antissoft / lvqianmeiye_ERP

using Microsoft.AspNetCore.Authorization;
using Microsoft.AspNetCore.Mvc;
using NCC.Common.Filter;
using NCC.Common.Helper;
using NCC.Dependency;
using NCC.DynamicApiController;
using NCC.Extend.Entitys.Dto.LqTechTeacherSalary;
using NCC.Extend.Entitys.lq_hytk_kjbsyj;
using NCC.Extend.Entitys.lq_kd_kjbsyj;
using NCC.Extend.Entitys.lq_md_xdbhsj;
using NCC.Extend.Entitys.lq_mdxx;
using NCC.Extend.Entitys.lq_attendance_summary;
using NCC.Extend.Entitys.lq_tech_teacher_salary_statistics;
using NCC.Extend.Entitys.lq_xh_hyhk;
using NCC.Extend.Entitys.lq_xh_kjbsyj;
using NCC.Extend.Entitys.lq_person_times_record;
using NCC.System.Entitys.Permission;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Yitter.IdGenerator;

namespace NCC.Extend
{
    /// <summary>
    /// 科技老师薪酬服务
    /// </summary>
    [ApiDescriptionSettings(Tag = "科技老师薪酬服务", Name = "LqTechTeacherSalary", Order = 302)]
    [Route("api/Extend/[controller]")]
    public class LqTechTeacherSalaryService : IDynamicApiController, ITransient
    {
        private readonly ISqlSugarClient _db;

        /// <summary>
        /// 初始化一个<see cref="LqTechTeacherSalaryService"/>类型的新实例
        /// </summary>
        public LqTechTeacherSalaryService(ISqlSugarClient db)
        {
            _db = db;
        }

        /// <summary>
        /// 获取科技老师工资列表
        /// </summary>
        /// <param name="input">查询参数</param>
        /// <returns>科技老师工资分页列表</returns>
        [HttpGet("tech-teacher")]
        public async Task<dynamic> GetTechTeacherSalaryList([FromQuery] TechTeacherSalaryInput input)
        {
            var monthStr = $"{input.Year}{input.Month:D2}";

            // 1. 检查当月是否已生成工资数据
            var exists = await _db.Queryable<LqTechTeacherSalaryStatisticsEntity>()
                .AnyAsync(x => x.StatisticsMonth == monthStr);

            // 2. 如果没有数据，则进行计算
            if (!exists)
            {
                await CalculateTechTeacherSalary(input.Year, input.Month);
            }

            // 3. 查询数据
            var query = _db.Queryable<LqTechTeacherSalaryStatisticsEntity>()
                .Where(x => x.StatisticsMonth == monthStr);

            if (!string.IsNullOrEmpty(input.StoreId))
            {
                query = query.Where(x => x.StoreId == input.StoreId);
            }

            if (!string.IsNullOrEmpty(input.Keyword))
            {
                query = query.Where(x => x.EmployeeName.Contains(input.Keyword) || x.EmployeeAccount.Contains(input.Keyword));
            }

            var list = await query.Select(x => new TechTeacherSalaryOutput
            {
                Id = x.Id,
                StatisticsMonth = x.StatisticsMonth,
                StoreId = x.StoreId,
                StoreName = x.StoreName,
                Position = x.Position,
                EmployeeName = x.EmployeeName,
                EmployeeId = x.EmployeeId,
                EmployeeAccount = x.EmployeeAccount,
                OrderAchievement = x.OrderAchievement,
                ConsumeAchievement = x.ConsumeAchievement,
                RefundAchievement = x.RefundAchievement,
                TotalPerformance = x.TotalPerformance,
                ProjectCount = x.ProjectCount,
                BaseSalaryLevel = x.BaseSalaryLevel,
                BaseSalary = x.BaseSalary,
                PerformanceCommissionRate = x.PerformanceCommissionRate,
                PerformanceCommissionAmount = x.PerformanceCommissionAmount,
                ConsumeCommissionRate = x.ConsumeCommissionRate,
                ConsumeCommissionAmount = x.ConsumeCommissionAmount,
                HandworkFee = x.HandworkFee,
                WorkingDays = x.WorkingDays,
                LeaveDays = x.LeaveDays,
                TotalCommission = x.TotalCommission,
                TransportationAllowance = x.TransportationAllowance,
                LessRest = x.LessRest,
                FullAttendance = x.FullAttendance,
                CalculatedGrossSalary = x.CalculatedGrossSalary,
                GuaranteedSalary = x.GuaranteedSalary,
                GuaranteedLeaveDeduction = x.GuaranteedLeaveDeduction,
                GuaranteedBaseSalary = x.GuaranteedBaseSalary,
                GuaranteedSupplement = x.GuaranteedSupplement,
                FinalGrossSalary = x.FinalGrossSalary,
                MonthlyTrainingSubsidy = x.MonthlyTrainingSubsidy,
                MonthlyTransportSubsidy = x.MonthlyTransportSubsidy,
                LastMonthTrainingSubsidy = x.LastMonthTrainingSubsidy,
                LastMonthTransportSubsidy = x.LastMonthTransportSubsidy,
                TotalSubsidy = x.TotalSubsidy,
                MissingCard = x.MissingCard,
                LateArrival = x.LateArrival,
                LeaveDeduction = x.LeaveDeduction,
                SocialInsuranceDeduction = x.SocialInsuranceDeduction,
                RewardDeduction = x.RewardDeduction,
                AccommodationDeduction = x.AccommodationDeduction,
                StudyPeriodDeduction = x.StudyPeriodDeduction,
                WorkClothesDeduction = x.WorkClothesDeduction,
                TotalDeduction = x.TotalDeduction,
                Bonus = x.Bonus,
                ReturnPhoneDeposit = x.ReturnPhoneDeposit,
                ReturnAccommodationDeposit = x.ReturnAccommodationDeposit,
                ActualSalary = x.ActualSalary,
                MonthlyPaymentStatus = x.MonthlyPaymentStatus,
                PaidAmount = x.PaidAmount,
                PendingAmount = x.PendingAmount,
                LastMonthSupplement = x.LastMonthSupplement,
                MonthlyTotalPayment = x.MonthlyTotalPayment,
                IsLocked = x.IsLocked,
                IsTerminated = x.IsTerminated,
                UpdateTime = x.UpdateTime,
                StoreType = x.StoreType,
                StoreCategory = x.StoreCategory,
                IsNewStore = x.IsNewStore,
                NewStoreProtectionStage = x.NewStoreProtectionStage
            })
            .ToPagedListAsync(input.currentPage, input.pageSize);

            return PageResult<TechTeacherSalaryOutput>.SqlSugarPageResult(list);
        }

        /// <summary>
        /// 计算科技老师工资
        /// </summary>
        /// <param name="year">年份</param>
        /// <param name="month">月份</param>
        /// <returns></returns>
        [HttpPost("calculate/tech-teacher")]
        public async Task CalculateTechTeacherSalary(int year, int month)
        {
            var startDate = new DateTime(year, month, 1);
            var endDate = startDate.AddMonths(1).AddDays(-1);
            var monthStr = $"{year}{month:D2}";

            // 1. 获取基础数据

            // 1.1 获取科技老师员工列表（从BASE_USER表，岗位为"科技老师"）
            var techTeacherUserList = await _db.Queryable<UserEntity>()
                .Where(x => x.Gw == "科技老师" && x.DeleteMark == null && x.EnabledMark == 1)
                .Select(x => new { x.Id, x.RealName, x.Account, x.Mdid, x.IsOnJob })
                .ToListAsync();

            if (!techTeacherUserList.Any())
            {
                // 如果没有科技老师员工，直接返回
                return;
            }

            // 1.2 门店信息 (lq_mdxx)
            var storeList = await _db.Queryable<LqMdxxEntity>().ToListAsync();
            var storeDict = storeList.Where(x => !string.IsNullOrEmpty(x.Id)).ToDictionary(x => x.Id, x => x);

            // 1.3 门店新店保护信息 (lq_md_xdbhsj)
            var newStoreProtectionList = await _db.Queryable<LqMdXdbhsjEntity>()
                .Where(x => x.Sfqy == 1)
                .ToListAsync();

            var newStoreProtectionDict = newStoreProtectionList
                .Where(x => x.Bhkssj <= startDate && x.Bhjssj >= startDate)
                .GroupBy(x => x.Mdid)
                .ToDictionary(g => g.Key, g => g.First());

            // 1.4 开单业绩数据 (lq_kd_kjbsyj)
            var orderPerformanceList = await _db.Queryable<LqKdKjbsyjEntity>()
                .Where(x => x.Yjsj >= startDate && x.Yjsj <= endDate.AddDays(1) && x.IsEffective == 1)
                .ToListAsync();

            // 1.5 消耗业绩和项目数数据 (lq_xh_kjbsyj，关联lq_xh_hyhk获取时间)
            var consumePerformanceList = await _db.Queryable<LqXhKjbsyjEntity, LqXhHyhkEntity>(
                    (kjbsyj, hyhk) => kjbsyj.Glkdbh == hyhk.Id && hyhk.IsEffective == 1)
                .Where((kjbsyj, hyhk) => kjbsyj.IsEffective == 1
                    && hyhk.Hksj >= startDate && hyhk.Hksj <= endDate.AddDays(1))
                .Select((kjbsyj, hyhk) => new
                {
                    kjbsyj.Kjblszh,
                    kjbsyj.Kjblsyj,
                    kjbsyj.HdpxNumber,
                    kjbsyj.LaborCost,
                    hyhk.Md
                })
                .ToListAsync();

            // 1.6 退卡业绩数据 (lq_hytk_kjbsyj)
            var refundPerformanceList = await _db.Queryable<LqHytkKjbsyjEntity>()
                .Where(x => x.Tksj >= startDate && x.Tksj <= endDate.AddDays(1) && x.IsEffective == 1)
                .ToListAsync();

            // 1.7 考勤数据 (lq_attendance_summary)
            var attendanceList = await _db.Queryable<LqAttendanceSummaryEntity>()
                .Where(x => x.Year == year && x.Month == month && x.IsEffective == 1)
                .ToListAsync();
            var attendanceDict = attendanceList.ToDictionary(x => x.UserId, x => x);

            // 2. 按科技老师聚合数据
            var techTeacherStats = new Dictionary<string, LqTechTeacherSalaryStatisticsEntity>();

            foreach (var techTeacherUser in techTeacherUserList)
            {
                var teacherId = techTeacherUser.Id;
                var isTerminated = techTeacherUser.IsOnJob == 0;

                // 2.1 创建工资统计对象
                var salary = new LqTechTeacherSalaryStatisticsEntity
                {
                    Id = YitIdHelper.NextId().ToString(),
                    StatisticsMonth = monthStr,
                    EmployeeId = teacherId,
                    EmployeeName = techTeacherUser.RealName,
                    EmployeeAccount = techTeacherUser.Account ?? "",
                    Position = "科技部老师",
                    IsTerminated = isTerminated ? 1 : 0,
                    CreateTime = DateTime.Now,
                    UpdateTime = DateTime.Now,
                    IsLocked = 0
                };

                // 2.2 填充门店信息
                var storeId = techTeacherUser.Mdid;
                if (!string.IsNullOrEmpty(storeId) && storeDict.ContainsKey(storeId))
                {
                    var store = storeDict[storeId];
                    salary.StoreId = storeId;
                    salary.StoreName = store.Dm ?? "";
                    salary.StoreType = store.StoreType;
                    salary.StoreCategory = store.StoreCategory;
                }
                else
                {
                    // 如果用户没有门店，尝试从业绩数据中获取
                    var firstOrderStore = orderPerformanceList.FirstOrDefault(x => x.Kjbls == teacherId || x.Kjblszh == teacherId);
                    var firstConsumeStore = consumePerformanceList.FirstOrDefault(x => x.Kjblszh == teacherId);
                    var firstRefundStore = refundPerformanceList.FirstOrDefault(x => x.Kjbls == teacherId || x.Kjblszh == teacherId);

                    if (firstOrderStore != null && !string.IsNullOrEmpty(firstOrderStore.StoreId))
                    {
                        storeId = firstOrderStore.StoreId;
                    }
                    else if (firstConsumeStore != null && !string.IsNullOrEmpty(firstConsumeStore.Md))
                    {
                        storeId = firstConsumeStore.Md;
                    }
                    else if (firstRefundStore != null && !string.IsNullOrEmpty(firstRefundStore.StoreId))
                    {
                        storeId = firstRefundStore.StoreId;
                    }

                    if (!string.IsNullOrEmpty(storeId) && storeDict.ContainsKey(storeId))
                    {
                        var store = storeDict[storeId];
                        salary.StoreId = storeId;
                        salary.StoreName = store.Dm ?? "";
                        salary.StoreType = store.StoreType;
                        salary.StoreCategory = store.StoreCategory;
                    }
                }

                // 2.3 新店保护信息
                if (!string.IsNullOrEmpty(salary.StoreId) && newStoreProtectionDict.ContainsKey(salary.StoreId))
                {
                    var protection = newStoreProtectionDict[salary.StoreId];
                    salary.IsNewStore = "是";
                    salary.NewStoreProtectionStage = protection.Stage;
                }
                else
                {
                    salary.IsNewStore = "否";
                    salary.NewStoreProtectionStage = 0;
                }

                // 2.4 统计业绩数据
                // 开单业绩（注意：kjblsyj字段是string类型，需要转换）
                var orderPerformance = orderPerformanceList
                    .Where(x => (x.Kjbls == teacherId || x.Kjblszh == teacherId) && !string.IsNullOrEmpty(x.Kjblsyj))
                    .Sum(x => decimal.TryParse(x.Kjblsyj, out var val) ? val : 0m);
                salary.OrderAchievement = orderPerformance;

                // 消耗业绩和项目数
                var consumeData = consumePerformanceList
                    .Where(x => x.Kjblszh == teacherId)
                    .ToList();
                salary.ConsumeAchievement = consumeData.Sum(x => x.Kjblsyj ?? 0m);
                salary.ProjectCount = consumeData.Sum(x => x.HdpxNumber ?? 0m);
                salary.HandworkFee = consumeData.Sum(x => x.LaborCost ?? 0m);

                // 退卡业绩
                var refundPerformance = refundPerformanceList
                    .Where(x => (x.Kjbls == teacherId || x.Kjblszh == teacherId))
                    .Sum(x => x.Kjblsyj ?? 0m);
                salary.RefundAchievement = refundPerformance;

                // 总业绩 = 开单业绩 - 退卡业绩（退卡是扣除）
                salary.TotalPerformance = salary.OrderAchievement - salary.RefundAchievement;

                // 2.5 考勤数据
                var attendance = attendanceDict.ContainsKey(teacherId) ? attendanceDict[teacherId] : null;
                salary.WorkingDays = attendance?.WorkDays ?? 0;
                salary.LeaveDays = attendance?.LeaveDays ?? 0;

                // 3. 工资计算
                if (isTerminated)
                {
                    // 离职员工特殊处理
                    if (salary.TotalPerformance > 30000m)
                    {
                        // 总业绩 > 30,000元：只计算2%提成
                        salary.BaseSalary = 0;
                        salary.BaseSalaryLevel = 0;
                        salary.PerformanceCommissionRate = 2m;
                        salary.PerformanceCommissionAmount = salary.TotalPerformance * 0.02m;
                        salary.ConsumeCommissionRate = 0;
                        salary.ConsumeCommissionAmount = 0;
                        salary.TotalCommission = salary.PerformanceCommissionAmount;
                    }
                    else
                    {
                        // 总业绩 ≤ 30,000元：无任何工资
                        salary.BaseSalary = 0;
                        salary.BaseSalaryLevel = 0;
                        salary.PerformanceCommissionRate = 0;
                        salary.PerformanceCommissionAmount = 0;
                        salary.ConsumeCommissionRate = 0;
                        salary.ConsumeCommissionAmount = 0;
                        salary.TotalCommission = 0;
                    }
                }
                else
                {
                    // 在职员工正常计算

                    // 3.1 计算底薪（根据项目数和总业绩）
                    var baseSalaryResult = CalculateBaseSalary(salary.ProjectCount, salary.TotalPerformance);
                    salary.BaseSalary = baseSalaryResult.BaseSalary;
                    salary.BaseSalaryLevel = baseSalaryResult.Level;

                    // 3.2 计算业绩提成（分段累进）
                    var performanceCommissionResult = CalculatePerformanceCommission(salary.TotalPerformance);
                    salary.PerformanceCommissionRate = performanceCommissionResult.Rate;
                    salary.PerformanceCommissionAmount = performanceCommissionResult.Amount;

                    // 3.3 计算消耗提成（分段累进，可能为负数）
                    var consumeCommissionResult = CalculateConsumeCommission(salary.ConsumeAchievement);
                    salary.ConsumeCommissionRate = consumeCommissionResult.Rate;
                    salary.ConsumeCommissionAmount = consumeCommissionResult.Amount;

                    // 3.4 提成合计
                    salary.TotalCommission = salary.PerformanceCommissionAmount + salary.ConsumeCommissionAmount;
                }

                // 3.5 初始化其他字段（默认值为0）
                salary.TransportationAllowance = 0;
                salary.LessRest = 0;
                salary.FullAttendance = 0;
                salary.CalculatedGrossSalary = salary.BaseSalary + salary.TotalCommission + salary.HandworkFee;
                salary.GuaranteedSalary = 0;
                salary.GuaranteedLeaveDeduction = 0;
                salary.GuaranteedBaseSalary = 0;
                salary.GuaranteedSupplement = 0;
                salary.FinalGrossSalary = salary.CalculatedGrossSalary;
                salary.MonthlyTrainingSubsidy = 0;
                salary.MonthlyTransportSubsidy = 0;
                salary.LastMonthTrainingSubsidy = 0;
                salary.LastMonthTransportSubsidy = 0;
                salary.TotalSubsidy = 0;
                salary.MissingCard = 0;
                salary.LateArrival = 0;
                salary.LeaveDeduction = 0;
                salary.SocialInsuranceDeduction = 0;
                salary.RewardDeduction = 0;
                salary.AccommodationDeduction = 0;
                salary.StudyPeriodDeduction = 0;
                salary.WorkClothesDeduction = 0;
                salary.TotalDeduction = 0;
                salary.Bonus = 0;
                salary.ReturnPhoneDeposit = 0;
                salary.ReturnAccommodationDeposit = 0;
                salary.ActualSalary = salary.FinalGrossSalary - salary.TotalDeduction + salary.TotalSubsidy + salary.Bonus;
                salary.MonthlyPaymentStatus = "";
                salary.PaidAmount = 0;
                salary.PendingAmount = salary.ActualSalary;
                salary.LastMonthSupplement = 0;
                salary.MonthlyTotalPayment = 0;

                techTeacherStats[teacherId] = salary;
            }

            // 4. 保存数据
            if (techTeacherStats.Any())
            {
                // 先删除当月旧数据 (防止重复)
                await _db.Deleteable<LqTechTeacherSalaryStatisticsEntity>()
                    .Where(x => x.StatisticsMonth == monthStr)
                    .ExecuteCommandAsync();

                await _db.Insertable(techTeacherStats.Values.ToList()).ExecuteCommandAsync();
            }
        }

        /// <summary>
        /// 计算底薪（根据项目数和总业绩）
        /// </summary>
        /// <param name="projectCount">项目数</param>
        /// <param name="totalPerformance">总业绩</param>
        /// <returns>底薪金额和档位</returns>
        private (decimal BaseSalary, int Level) CalculateBaseSalary(decimal projectCount, decimal totalPerformance)
        {
            // 从高到低判断档位，同时满足项目数和总业绩两个条件
            // 第三档：≥ 110个 且 ≥ 100,000元 → 3,500元
            if (projectCount >= 110m && totalPerformance >= 100000m)
            {
                return (3500m, 3);
            }
            // 第二档：≥ 95个 且 ≥ 80,000元 → 3,000元
            else if (projectCount >= 95m && totalPerformance >= 80000m)
            {
                return (3000m, 2);
            }
            // 第一档：≥ 80个 且 ≥ 40,000元 → 2,500元
            else if (projectCount >= 80m && totalPerformance >= 40000m)
            {
                return (2500m, 1);
            }
            // 都不满足：默认第一档 2,500元
            else
            {
                return (2500m, 1);
            }
        }

        /// <summary>
        /// 计算业绩提成（阶梯式）
        /// </summary>
        /// <param name="totalPerformance">总业绩</param>
        /// <returns>提成比例和金额</returns>
        private (decimal Rate, decimal Amount) CalculatePerformanceCommission(decimal totalPerformance)
        {
            // 提成前提：业绩必须大于1万才能进行提成
            if (totalPerformance <= 10000m)
            {
                // ≤ 10,000元 → 0%（无提成）
                return (0m, 0m);
            }

            decimal rate;
            decimal amount;

            // 阶梯式提成计算（整个业绩按对应比例）
            if (totalPerformance > 150000m)
            {
                // > 15万 → 3%
                rate = 3m;
                amount = totalPerformance * 0.03m;
            }
            else if (totalPerformance > 70000m)
            {
                // > 7万 且 ≤ 15万 → 2.5%
                rate = 2.5m;
                amount = totalPerformance * 0.025m;
            }
            else
            {
                // > 1万 且 ≤ 7万 → 2%
                rate = 2m;
                amount = totalPerformance * 0.02m;
            }

            return (rate, amount);
        }

        /// <summary>
        /// 计算消耗提成（分段累进，可能为负数）
        /// </summary>
        /// <param name="consumeAchievement">消耗业绩</param>
        /// <returns>提成比例和金额（金额可能为负数，比例用于显示）</returns>
        private (decimal Rate, decimal Amount) CalculateConsumeCommission(decimal consumeAchievement)
        {
            if (consumeAchievement < 80000m)
            {
                // < 80,000元 → 扣除300元（负数）
                // 比例显示为0，金额为-300
                return (0m, -300m);
            }
            else if (consumeAchievement < 100000m)
            {
                // 80,000-100,000元 → 0.5%
                return (0.5m, consumeAchievement * 0.005m);
            }
            else if (consumeAchievement < 200000m)
            {
                // 100,000-200,000元 → 0.5%
                return (0.5m, consumeAchievement * 0.005m);
            }
            else
            {
                // > 200,000元 → 1%
                return (1m, consumeAchievement * 0.01m);
            }
        }

        /// <summary>
        /// 统计科技部老师某个月的数据
        /// </summary>
        /// <remarks>
        /// 实时统计科技部老师某个月的数据，包括：
        /// - 开单业绩：从 lq_kd_kjbsyj 表统计
        /// - 消耗业绩：从 lq_xh_kjbsyj 表统计（关联 lq_xh_hyhk 获取时间）
        /// - 退卡业绩：从 lq_hytk_kjbsyj 表统计
        /// - 手工费：从耗卡表（lq_xh_kjbsyj）的 F_LaborCost 字段统计
        /// - 人头：从 lq_person_times_record 表统计（按月份+客户去重）
        /// - 人次：从 lq_person_times_record 表统计（按日期+客户去重）
        /// 
        /// 示例请求：
        /// ```json
        /// {
        ///   "year": 2025,
        ///   "month": 11
        /// }
        /// ```
        /// </remarks>
        /// <param name="input">查询参数（年份、月份）</param>
        /// <returns>科技部老师统计数据列表</returns>
        [HttpGet("statistics")]
        public async Task<List<TechTeacherStatisticsOutput>> GetTechTeacherStatistics([FromQuery] TechTeacherStatisticsInput input)
        {
            // 1. 参数验证
            if (input.Year < 2020 || input.Year > 2100)
            {
                throw new Exception("年份范围不正确");
            }
            if (input.Month < 1 || input.Month > 12)
            {
                throw new Exception("月份范围不正确（1-12）");
            }

            // 2. 计算时间范围
            var startDate = new DateTime(input.Year, input.Month, 1);
            var endDate = startDate.AddMonths(1).AddDays(-1);
            var monthStr = $"{input.Year}{input.Month:D2}";

            // 3. 获取所有科技部老师（岗位为"科技老师"）
            var techTeacherList = await _db.Queryable<UserEntity>()
                .Where(x => x.Gw == "科技老师")
                .Select(x => new
                {
                    EmployeeId = x.Id,
                    EmployeeName = x.RealName,
                    EmployeeAccount = x.Account
                })
                .ToListAsync();

            if (techTeacherList == null || !techTeacherList.Any())
            {
                return new List<TechTeacherStatisticsOutput>();
            }

            var teacherIds = techTeacherList.Select(x => x.EmployeeId).ToList();
            var teacherAccounts = techTeacherList.Where(x => !string.IsNullOrEmpty(x.EmployeeAccount)).Select(x => x.EmployeeAccount).ToList();

            // 4. 使用聚合查询统计开单业绩和手工费（优化性能）
            // 注意：kjblsyj字段是varchar类型，需要转换
            var orderStatsList = await _db.Queryable<LqKdKjbsyjEntity>()
                .Where(x => x.Yjsj >= startDate && x.Yjsj <= endDate.AddDays(1) && x.IsEffective == 1)
                .Where(x => teacherIds.Contains(x.Kjbls) || teacherAccounts.Contains(x.Kjblszh))
                .ToListAsync();

            var orderStats = orderStatsList
                .Where(x => !string.IsNullOrEmpty(x.Kjbls))
                .GroupBy(x => x.Kjbls)
                .Select(g => new
                {
                    TeacherId = g.Key,
                    OrderAchievement = g.Sum(x => decimal.TryParse(x.Kjblsyj, out var val) ? val : 0m)
                })
                .ToList();

            // 5. 使用聚合查询统计消耗业绩和手工费（关联耗卡主表获取时间）
            var consumeStatsList = await _db.Queryable<LqXhKjbsyjEntity, LqXhHyhkEntity>(
                    (kjbsyj, hyhk) => kjbsyj.Glkdbh == hyhk.Id && hyhk.IsEffective == 1)
                .Where((kjbsyj, hyhk) => kjbsyj.IsEffective == 1
                    && hyhk.Hksj >= startDate && hyhk.Hksj <= endDate.AddDays(1))
                .Where((kjbsyj, hyhk) => teacherIds.Contains(kjbsyj.Kjbls) || teacherAccounts.Contains(kjbsyj.Kjblszh))
                .Select((kjbsyj, hyhk) => new
                {
                    TeacherId = kjbsyj.Kjbls,
                    ConsumeAchievement = kjbsyj.Kjblsyj,
                    LaborCost = kjbsyj.LaborCost
                })
                .ToListAsync();

            var consumeStats = consumeStatsList
                .Where(x => !string.IsNullOrEmpty(x.TeacherId))
                .GroupBy(x => x.TeacherId)
                .Select(g => new
                {
                    TeacherId = g.Key,
                    ConsumeAchievement = g.Sum(x => x.ConsumeAchievement ?? 0m),
                    LaborCost = g.Sum(x => x.LaborCost ?? 0m)
                })
                .ToList();

            // 6. 使用聚合查询统计退卡业绩和手工费
            var refundStatsList = await _db.Queryable<LqHytkKjbsyjEntity>()
                .Where(x => x.Tksj >= startDate && x.Tksj <= endDate.AddDays(1) && x.IsEffective == 1)
                .Where(x => teacherIds.Contains(x.Kjbls) || teacherAccounts.Contains(x.Kjblszh))
                .ToListAsync();

            var refundStats = refundStatsList
                .Where(x => !string.IsNullOrEmpty(x.Kjbls))
                .GroupBy(x => x.Kjbls)
                .Select(g => new
                {
                    TeacherId = g.Key,
                    RefundAchievement = g.Sum(x => x.Kjblsyj ?? 0m)
                })
                .ToList();

            // 7. 统计人头（按月份+客户去重）
            var personCountRecords = await _db.Queryable<LqPersonTimesRecordEntity>()
                .Where(x => x.PersonType == "科技老师"
                    && x.WorkMonth == monthStr
                    && x.IsEffective == 1
                    && teacherIds.Contains(x.PersonId))
                .ToListAsync();

            var personCountStats = personCountRecords
                .GroupBy(x => new { x.PersonId, x.MemberId })
                .Select(g => new { TeacherId = g.Key.PersonId })
                .GroupBy(x => x.TeacherId)
                .Select(g => new
                {
                    TeacherId = g.Key,
                    PersonCount = g.Count()
                })
                .ToList();

            // 8. 统计人次（按日期+客户去重，汇总数量）
            var personTimesRecords = await _db.Queryable<LqPersonTimesRecordEntity>()
                .Where(x => x.PersonType == "科技老师"
                    && x.WorkMonth == monthStr
                    && x.IsEffective == 1
                    && teacherIds.Contains(x.PersonId))
                .ToListAsync();

            // 先按日期+客户去重，取最大数量，然后按老师汇总
            var personTimesStats = personTimesRecords
                .GroupBy(x => new { x.PersonId, x.WorkDate, x.MemberId })
                .Select(g => new
                {
                    TeacherId = g.Key.PersonId,
                    Quantity = g.Max(x => x.Quantity ?? 0m) // 按日期+客户去重，取最大数量
                })
                .GroupBy(x => x.TeacherId)
                .Select(g => new
                {
                    TeacherId = g.Key,
                    PersonTimes = g.Sum(x => x.Quantity) // 汇总所有去重后的数量
                })
                .ToList();

            // 9. 构建结果字典（优化查找性能）
            var orderDict = orderStats.ToDictionary(x => x.TeacherId, x => x);
            var consumeDict = consumeStats.ToDictionary(x => x.TeacherId, x => x);
            var refundDict = refundStats.ToDictionary(x => x.TeacherId, x => x);
            var personCountDict = personCountStats.ToDictionary(x => x.TeacherId, x => x);
            var personTimesDict = personTimesStats.ToDictionary(x => x.TeacherId, x => x);

            // 10. 组装结果
            var result = new List<TechTeacherStatisticsOutput>();
            foreach (var teacher in techTeacherList)
            {
                var orderStat = orderDict.ContainsKey(teacher.EmployeeId) ? orderDict[teacher.EmployeeId] : null;
                var consumeStat = consumeDict.ContainsKey(teacher.EmployeeId) ? consumeDict[teacher.EmployeeId] : null;
                var refundStat = refundDict.ContainsKey(teacher.EmployeeId) ? refundDict[teacher.EmployeeId] : null;
                var personCountStat = personCountDict.ContainsKey(teacher.EmployeeId) ? personCountDict[teacher.EmployeeId] : null;
                var personTimesStat = personTimesDict.ContainsKey(teacher.EmployeeId) ? personTimesDict[teacher.EmployeeId] : null;

                result.Add(new TechTeacherStatisticsOutput
                {
                    EmployeeId = teacher.EmployeeId,
                    EmployeeName = teacher.EmployeeName,
                    OrderAchievement = orderStat?.OrderAchievement ?? 0m,
                    ConsumeAchievement = consumeStat?.ConsumeAchievement ?? 0m,
                    RefundAchievement = refundStat?.RefundAchievement ?? 0m,
                    PersonCount = personCountStat?.PersonCount ?? 0,
                    PersonTimes = personTimesStat?.PersonTimes ?? 0m,
                    LaborCost = consumeStat?.LaborCost ?? 0m // 手工费只统计耗卡中的手工费
                });
            }

            return result;
        }
    }
}