antissoft / lvqianmeiye_ERP

using Microsoft.AspNetCore.Authorization;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Logging;
using NCC.Common.Filter;
using NCC.Common.Helper;
using NCC.Dependency;
using NCC.DynamicApiController;
using NCC.Extend.Entitys.Dto.LqSalary;
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_kd_kdjlb;
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.FriendlyException;
using NCC.System.Entitys.Permission;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
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;
        private readonly ILogger<LqTechTeacherSalaryService> _logger;

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

        /// <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}";

            // 查询数据
            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,
                EmployeeConfirmStatus = x.EmployeeConfirmStatus,
                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>
        /// 通过月份和员工ID查询工资（仅查询已锁定且未确认的工资）
        /// </summary>
        /// <remarks>
        /// **重要**：此接口只能查询已锁定（IsLocked=1）且未确认（EmployeeConfirmStatus=0或null）的工资记录
        /// **注意**：已确认的工资记录无法通过此接口查询
        /// </remarks>
        [HttpGet("query-by-employee")]
        public async Task<TechTeacherSalaryOutput> GetSalaryByEmployee([FromQuery] SalaryQueryByEmployeeInput input)
        {
            if (input.Year <= 0 || input.Month <= 0 || input.Month > 12)
                throw NCCException.Oh("年份和月份参数不正确");
            if (string.IsNullOrWhiteSpace(input.EmployeeId))
                throw NCCException.Oh("员工ID不能为空");
            var monthStr = $"{input.Year}{input.Month:D2}";
            var salary = await _db.Queryable<LqTechTeacherSalaryStatisticsEntity>()
                .Where(x => x.StatisticsMonth == monthStr && x.EmployeeId == input.EmployeeId && x.IsLocked == 1 && x.EmployeeConfirmStatus != 1)
                .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,
                    EmployeeConfirmStatus = x.EmployeeConfirmStatus,
                    IsTerminated = x.IsTerminated,
                    UpdateTime = x.UpdateTime,
                    StoreType = x.StoreType,
                    StoreCategory = x.StoreCategory,
                    IsNewStore = x.IsNewStore,
                    NewStoreProtectionStage = x.NewStoreProtectionStage
                })
                .FirstAsync();
            if (salary == null)
                throw NCCException.Oh($"未找到员工{input.EmployeeId}在{input.Year}年{input.Month}月的工资记录");
            return salary;
        }

        /// <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 = 0.02m; // 保存为小数形式，0.02表示2%
                        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
                {
                    // 在职员工正常计算

                    // 判断是否为T区员工（员工姓名包含"T区"）
                    bool isTZoneEmployee = !string.IsNullOrEmpty(salary.EmployeeName) && salary.EmployeeName.Contains("T区");

                    if (isTZoneEmployee)
                    {
                        // T区员工：按照开单减去退款之后的业绩统一按照2%提成
                        // 总业绩 = 开单业绩 - 退卡业绩（已在前面计算）
                        salary.PerformanceCommissionRate = 0.02m; // 保存为小数形式，0.02表示2%
                        salary.PerformanceCommissionAmount = salary.TotalPerformance * 0.02m;
                        salary.ConsumeCommissionRate = 0;
                        salary.ConsumeCommissionAmount = 0;
                        salary.TotalCommission = salary.PerformanceCommissionAmount;
                        // T区员工也计算底薪（根据项目数和总业绩）
                        var baseSalaryResult = CalculateBaseSalary(salary.ProjectCount, salary.TotalPerformance);
                        salary.BaseSalary = baseSalaryResult.BaseSalary;
                        salary.BaseSalaryLevel = baseSalaryResult.Level;
                    }
                    else
                    {
                        // 非T区员工正常计算

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

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

                        // 3.3 计算消耗提成（新规则：10万门槛，阶梯式）
                        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())
            {
                // 4.1 先删除计算月的未锁定且未确认的工资记录
                var deletedCount = await _db.Deleteable<LqTechTeacherSalaryStatisticsEntity>()
                    .Where(x => x.StatisticsMonth == monthStr 
                        && x.IsLocked == 0 
                        && x.EmployeeConfirmStatus == 0)
                    .ExecuteCommandAsync();
                
                if (deletedCount > 0)
                {
                    _logger.LogInformation($"计算工资前删除了 {deletedCount} 条未锁定且未确认的记录（月份：{monthStr}）");
                }

                // 4.2 查询已存在的记录（只查询已锁定或已确认的记录）
                var existingRecords = await _db.Queryable<LqTechTeacherSalaryStatisticsEntity>()
                    .Where(x => x.StatisticsMonth == monthStr 
                        && (x.IsLocked == 1 || x.EmployeeConfirmStatus == 1))
                    .ToListAsync();

                var existingDict = existingRecords
                    .Where(x => !string.IsNullOrEmpty(x.EmployeeId))
                    .GroupBy(x => x.EmployeeId)
                    .ToDictionary(g => g.Key, g => g.First());

                // 分离需要插入的新记录和需要更新的记录
                var recordsToInsert = new List<LqTechTeacherSalaryStatisticsEntity>();
                var recordsToUpdate = new List<LqTechTeacherSalaryStatisticsEntity>();
                var updatedCount = 0;
                var skippedCount = 0;

                foreach (var salary in techTeacherStats.Values)
                {
                    if (existingDict.ContainsKey(salary.EmployeeId))
                    {
                        // 检查记录是否已锁定或已确认
                        var existing = existingDict[salary.EmployeeId];
                        
                        // 如果已锁定或已确认，跳过不更新（保留所有原有数据，包括扣款项目）
                        if (existing.IsLocked == 1 || existing.EmployeeConfirmStatus == 1)
                        {
                            skippedCount++;
                            continue; // 跳过，不进行任何更新
                        }
                        
                        // 未锁定且未确认的记录，可以做更新操作
                        salary.Id = existing.Id;
                        salary.EmployeeConfirmStatus = existing.EmployeeConfirmStatus; // 应该是0
                        salary.EmployeeConfirmTime = existing.EmployeeConfirmTime;
                        salary.EmployeeConfirmRemark = existing.EmployeeConfirmRemark;
                        salary.IsLocked = existing.IsLocked; // 保留锁定状态（应该是0）
                        salary.CreateTime = existing.CreateTime;
                        salary.CreateUser = existing.CreateUser;
                        recordsToUpdate.Add(salary);
                        updatedCount++;
                    }
                    else
                    {
                        // 不存在的记录，做插入操作
                        salary.Id = YitIdHelper.NextId().ToString();
                        salary.EmployeeConfirmStatus = 0;
                        salary.IsLocked = 0;
                        salary.CreateTime = DateTime.Now;
                        salary.CreateUser = ""; // 新记录，创建人为空或系统
                        recordsToInsert.Add(salary);
                    }
                }

                // 批量插入新记录
                if (recordsToInsert.Any())
                {
                    await _db.Insertable(recordsToInsert).ExecuteCommandAsync();
                    _logger.LogInformation($"插入了 {recordsToInsert.Count} 条新的工资记录（月份：{monthStr}）");
                }

                // 批量更新现有记录
                if (recordsToUpdate.Any())
                {
                    await _db.Updateable(recordsToUpdate).ExecuteCommandAsync();
                    _logger.LogInformation($"更新了 {recordsToUpdate.Count} 条未锁定且未确认的工资记录（月份：{monthStr}）");
                }

                if (skippedCount > 0)
                {
                    _logger.LogInformation($"跳过了 {skippedCount} 条已锁定或已确认的工资记录，保留原有数据（月份：{monthStr}）");
                }
            }
        }

        /// <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>
        /// <remarks>
        /// 提成规则（分段累进式）：
        /// 1. 前提条件：整月业绩必须大于等于3万才能进行提成（门槛从1万提高到3万）
        /// 2. 如果有提成资格后，分段计算：
        ///    - 0-7万部分：2%（整个0-7万部分都按2%计算）
        ///    - 7万-15万部分：2.5%
        ///    - 15万以上部分：3%
        /// 
        /// 计算公式（分段累进）：
        /// - 如果业绩 < 3万：提成 = 0（无提成资格）
        /// - 如果 3万 ≤ 业绩 ≤ 7万：提成 = 业绩 × 2%
        /// - 如果 7万 < 业绩 ≤ 15万：提成 = 7万 × 2% + (业绩 - 7万) × 2.5%
        /// - 如果业绩 > 15万：提成 = 7万 × 2% + (15万 - 7万) × 2.5% + (业绩 - 15万) × 3%
        /// 
        /// 示例：
        /// - 总业绩 = 25,000元 → 提成 = 0（无提成资格，未达到3万门槛）
        /// - 总业绩 = 50,000元 → 提成 = 50,000 × 2% = 1,000元
        /// - 总业绩 = 100,000元 → 提成 = 70,000 × 2% + (100,000 - 70,000) × 2.5% = 1,400 + 750 = 2,150元
        /// - 总业绩 = 200,000元 → 提成 = 70,000 × 2% + (150,000 - 70,000) × 2.5% + (200,000 - 150,000) × 3% = 1,400 + 2,000 + 1,500 = 4,900元
        /// </remarks>
        private (decimal Rate, decimal Amount) CalculatePerformanceCommission(decimal totalPerformance)
        {
            // 提成前提：整月业绩必须大于等于3万才能进行提成
            if (totalPerformance < 30000m)
            {
                // < 30,000元 → 0%（无提成资格）
                return (0m, 0m);
            }

            decimal totalCommission = 0m;

            // 分段累进式提成计算（已通过提成资格检查）
            if (totalPerformance > 150000m)
            {
                // 业绩 > 15万：分段计算
                // 0-7万部分：2%
                decimal part1 = 70000m * 0.02m; // 7万 × 2% = 1,400元
                // 7万-15万部分：2.5%
                decimal part2 = (150000m - 70000m) * 0.025m; // 8万 × 2.5% = 2,000元
                // 15万以上部分：3%
                decimal part3 = (totalPerformance - 150000m) * 0.03m;
                totalCommission = part1 + part2 + part3;
            }
            else if (totalPerformance > 70000m)
            {
                // 业绩 > 7万 且 ≤ 15万：分段计算
                // 0-7万部分：2%
                decimal part1 = 70000m * 0.02m; // 7万 × 2% = 1,400元
                // 7万以上部分：2.5%
                decimal part2 = (totalPerformance - 70000m) * 0.025m;
                totalCommission = part1 + part2;
            }
            else
            {
                // 业绩 ≥ 3万 且 ≤ 7万：整个业绩按2%计算
                totalCommission = totalPerformance * 0.02m;
            }

            // 计算平均提成比例（保存为小数形式，如0.02表示2%，前端会乘以100显示）
            decimal averageRate = totalCommission > 0 && totalPerformance > 0 ? (totalCommission / totalPerformance) : 0m;

            return (averageRate, totalCommission);
        }

        /// <summary>
        /// 计算消耗提成（阶梯式，可能为负数）
        /// </summary>
        /// <param name="consumeAchievement">消耗业绩</param>
        /// <returns>提成比例和金额（金额可能为负数，比例用于显示）</returns>
        /// <remarks>
        /// 消耗提成规则：
        /// 1. 未完成10万底标：负激励300元（扣除300元）
        /// 2. 达到10万条件后，按照阶梯式提成：
        ///    - 1-20万部分：0.5%
        ///    - 超过20万部分：1%
        /// 
        /// 计算公式（阶梯式）：
        /// - 如果消耗业绩 < 10万：提成 = -300元（扣除300元）
        /// - 如果消耗业绩 ≥ 10万 且 ≤ 20万：提成 = 消耗业绩 × 0.5%
        /// - 如果消耗业绩 > 20万：提成 = 20万 × 0.5% + (消耗业绩 - 20万) × 1%
        /// 
        /// 示例：
        /// - 消耗业绩 = 50,000元 → 提成 = -300元（未完成10万底标）
        /// - 消耗业绩 = 100,000元 → 提成 = 100,000 × 0.5% = 500元
        /// - 消耗业绩 = 150,000元 → 提成 = 150,000 × 0.5% = 750元
        /// - 消耗业绩 = 250,000元 → 提成 = 200,000 × 0.5% + (250,000 - 200,000) × 1% = 1,000 + 500 = 1,500元
        /// </remarks>
        private (decimal Rate, decimal Amount) CalculateConsumeCommission(decimal consumeAchievement)
        {
            if (consumeAchievement < 100000m)
            {
                // < 100,000元（未完成10万底标）→ 扣除300元（负数）
                // 比例显示为0，金额为-300
                return (0m, -300m);
            }
            else if (consumeAchievement <= 200000m)
            {
                // ≥ 100,000元 且 ≤ 200,000元 → 1-20万部分按0.5%
                return (0.005m, consumeAchievement * 0.005m); // 保存为小数形式，0.005表示0.5%
            }
            else
            {
                // > 200,000元 → 阶梯式：1-20万部分0.5%，超过20万部分1%
                decimal part1 = 200000m * 0.005m; // 20万 × 0.5% = 1,000元
                decimal part2 = (consumeAchievement - 200000m) * 0.01m; // 超过20万部分 × 1%
                decimal totalCommission = part1 + part2;
                // 计算平均比例（保存为小数形式，前端会乘以100显示）
                decimal averageRate = totalCommission > 0 && consumeAchievement > 0 ? (totalCommission / consumeAchievement) : 0m;
                return (averageRate, totalCommission);
            }
        }

        /// <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 techOrganizeList = await _db.Queryable<OrganizeEntity>()
                .Where(x => x.FullName != null && (x.FullName.Contains("科技一部") || x.FullName.Contains("科技二部"))
                    && x.DeleteMark == null && x.EnabledMark == 1)
                .Select(x => new { x.Id, x.FullName })
                .ToListAsync();

            var techOrganizeIds = techOrganizeList?.Select(x => x.Id).ToList() ?? new List<string>();
            var techOrganizeDict = techOrganizeList?.ToDictionary(x => x.Id, x => x.FullName) ?? new Dictionary<string, string>();

            // 4. 获取所有科技部老师（岗位为"科技老师"），并关联部门信息
            // 先筛选科技部组织，只查询属于科技一部或科技二部的老师
            var techTeacherQuery = _db.Queryable<UserEntity>()
                .LeftJoin<OrganizeEntity>((user, org) => user.OrganizeId == org.Id)
                .Where((user, org) => user.Gw == "科技老师"
                    && user.DeleteMark == null && user.EnabledMark == 1
                    && (techOrganizeIds.Count == 0 || techOrganizeIds.Contains(user.OrganizeId)));

            // 如果指定了部门ID，则进行筛选
            if (!string.IsNullOrEmpty(input.DepartmentId))
            {
                techTeacherQuery = techTeacherQuery.Where((user, org) => user.OrganizeId == input.DepartmentId);
            }

            var techTeacherList = await techTeacherQuery
                .Select((user, org) => new
                {
                    EmployeeId = user.Id,
                    EmployeeName = user.RealName,
                    EmployeeAccount = user.Account,
                    OrganizeId = user.OrganizeId,
                    DepartmentName = org.FullName ?? ""
                })
                .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();

            // 5. 使用聚合查询统计开单业绩和手工费（优化性能）
            // 注意：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();

            // 6. 使用聚合查询统计消耗业绩和手工费（关联耗卡主表获取时间）
            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();

            // 7. 使用聚合查询统计退卡业绩和手工费
            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();

            // 8. 统计人头（按月份+客户去重）
            // 注意：数据库中可能存在"科技老师"和"科技部老师"两种PersonType值，需要同时查询
            var personCountRecords = await _db.Queryable<LqPersonTimesRecordEntity>()
                .Where(x => (x.PersonType == "科技老师" || x.PersonType == "科技部老师")
                    && x.WorkMonth == monthStr
                    && x.IsEffective == 1
                    && teacherIds.Contains(x.PersonId)
                    && !string.IsNullOrEmpty(x.PersonId)
                    && !string.IsNullOrEmpty(x.MemberId))
                .Select(x => new
                {
                    PersonId = x.PersonId,
                    MemberId = x.MemberId
                })
                .ToListAsync();

            // 按PersonId和MemberId去重，然后按PersonId分组统计不同MemberId的数量
            var personCountDict = personCountRecords
                .GroupBy(x => new { x.PersonId, x.MemberId })
                .Select(g => new { TeacherId = g.Key.PersonId, MemberId = g.Key.MemberId })
                .GroupBy(x => x.TeacherId)
                .ToDictionary(
                    g => g.Key ?? "",
                    g => g.Select(x => x.MemberId).Distinct().Count());

            // 9. 统计人次（按日期+客户去重，汇总数量）
            // 注意：数据库中可能存在"科技老师"和"科技部老师"两种PersonType值，需要同时查询
            var personTimesRecords = await _db.Queryable<LqPersonTimesRecordEntity>()
                .Where(x => (x.PersonType == "科技老师" || 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();

            // 10. 统计开单人头（同一天去重）
            // 从开单科技老师业绩表关联开单主表，按科技老师ID + 开单日期 + 客户ID去重统计
            var orderPersonCountList = await _db.Queryable<LqKdKjbsyjEntity, LqKdKdjlbEntity>(
                    (kjbsyj, kdjlb) => kjbsyj.Glkdbh == kdjlb.Id && kdjlb.IsEffective == 1)
                .Where((kjbsyj, kdjlb) => kjbsyj.IsEffective == 1
                    && kdjlb.Kdrq >= startDate && kdjlb.Kdrq <= endDate.AddDays(1))
                .Where((kjbsyj, kdjlb) => teacherIds.Contains(kjbsyj.Kjbls) || teacherAccounts.Contains(kjbsyj.Kjblszh))
                .Select((kjbsyj, kdjlb) => new
                {
                    TeacherId = kjbsyj.Kjbls,
                    OrderDate = kdjlb.Kdrq,
                    MemberId = kdjlb.Kdhy
                })
                .ToListAsync();

            // 按科技老师ID + 开单日期 + 客户ID去重统计开单人头
            var orderPersonCountDict = orderPersonCountList
                .Where(x => !string.IsNullOrEmpty(x.TeacherId) && !string.IsNullOrEmpty(x.MemberId) && x.OrderDate.HasValue)
                .GroupBy(x => new { x.TeacherId, OrderDate = x.OrderDate.Value.Date, x.MemberId })
                .Select(g => new { TeacherId = g.Key.TeacherId, MemberId = g.Key.MemberId })
                .GroupBy(x => x.TeacherId)
                .ToDictionary(
                    g => g.Key ?? "",
                    g => g.Select(x => x.MemberId).Distinct().Count());

            // 11. 构建结果字典（优化查找性能）
            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);
            // personCountDict 已经在上面构建了
            var personTimesDict = personTimesStats.ToDictionary(x => x.TeacherId, x => x);

            // 12. 组装结果
            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 personCount = personCountDict.ContainsKey(teacher.EmployeeId) ? personCountDict[teacher.EmployeeId] : 0;
                var personTimesStat = personTimesDict.ContainsKey(teacher.EmployeeId) ? personTimesDict[teacher.EmployeeId] : null;
                var orderPersonCount = orderPersonCountDict.ContainsKey(teacher.EmployeeId) ? orderPersonCountDict[teacher.EmployeeId] : 0;

                // 计算消耗开单转化率（开单人头/消耗人头）
                var consumeOrderConversionRate = personCount > 0 ? (decimal)orderPersonCount / personCount : 0m;

                result.Add(new TechTeacherStatisticsOutput
                {
                    EmployeeId = teacher.EmployeeId,
                    EmployeeName = teacher.EmployeeName,
                    OrderAchievement = orderStat?.OrderAchievement ?? 0m,
                    ConsumeAchievement = consumeStat?.ConsumeAchievement ?? 0m,
                    RefundAchievement = refundStat?.RefundAchievement ?? 0m,
                    PersonCount = personCount,
                    PersonTimes = personTimesStat?.PersonTimes ?? 0m,
                    LaborCost = consumeStat?.LaborCost ?? 0m, // 手工费只统计耗卡中的手工费
                    DepartmentId = teacher.OrganizeId,
                    DepartmentName = !string.IsNullOrEmpty(teacher.DepartmentName)
                        ? teacher.DepartmentName
                        : (techOrganizeDict.ContainsKey(teacher.OrganizeId) ? techOrganizeDict[teacher.OrganizeId] : ""),
                    OrderPersonCount = orderPersonCount,
                    ConsumeOrderConversionRate = consumeOrderConversionRate
                });
            }

            return result;
        }

        #region 员工工资确认

        /// <summary>
        /// 员工确认工资条
        /// </summary>
        /// <remarks>
        /// 员工确认自己的工资条，确认后工资数据不可再修改
        /// 
        /// 示例请求：
        /// <code>
        /// {
        ///   "id": "工资记录ID",
        ///   "employeeId": "员工ID",
        ///   "remark": "确认备注（可选）"
        /// }
        /// </code>
        /// 
        /// 参数说明：
        /// - id: 工资记录ID（必填）
        /// - employeeId: 员工ID（必填）
        /// - remark: 确认备注（可选）
        /// 
        /// 注意事项：
        /// - 只能确认自己的工资条
        /// - 只能确认已锁定的工资条（IsLocked = 1）
        /// - 已确认的工资条不能重复确认
        /// </remarks>
        /// <param name="input">确认参数</param>
        /// <returns>操作结果</returns>
        /// <response code="200">确认成功</response>
        /// <response code="400">参数错误或记录不存在</response>
        [HttpPost("confirm")]
        public async Task<string> ConfirmSalary([FromBody] SalaryConfirmInput input)
        {
            try
            {
                if (string.IsNullOrWhiteSpace(input.Id))
                {
                    throw NCCException.Oh("工资记录ID不能为空");
                }

                if (string.IsNullOrWhiteSpace(input.EmployeeId))
                {
                    throw NCCException.Oh("员工ID不能为空");
                }

                // 查询工资记录
                var salary = await _db.Queryable<LqTechTeacherSalaryStatisticsEntity>()
                    .Where(s => s.Id == input.Id && s.EmployeeId == input.EmployeeId)
                    .FirstAsync();

                if (salary == null)
                {
                    throw NCCException.Oh("工资记录不存在或不属于该员工");
                }

                // 检查是否已确认
                if (salary.EmployeeConfirmStatus == 1)
                {
                    throw NCCException.Oh("该工资条已确认，不能重复确认");
                }

                // 检查是否已锁定（员工只能确认已锁定的工资条）
                if (salary.IsLocked != 1)
                {
                    throw NCCException.Oh("该工资条尚未锁定，请等待管理员锁定后再确认");
                }

                // 更新确认状态
                salary.EmployeeConfirmStatus = 1;
                salary.EmployeeConfirmTime = DateTime.Now;
                salary.EmployeeConfirmRemark = input.Remark;
                // 注意：IsLocked 保持为 1（因为本来就是管理员锁定的）
                salary.UpdateTime = DateTime.Now;

                await _db.Updateable(salary).ExecuteCommandAsync();

                return "确认成功";
            }
            catch (Exception ex)
            {
                throw NCCException.Oh($"确认工资条失败: {ex.Message}");
            }
        }

        #endregion

        #region 工资锁定/解锁

        /// <summary>
        /// 批量锁定/解锁工资条
        /// </summary>
        [HttpPost("lock")]
        public async Task<string> LockSalary([FromBody] SalaryLockInput input)
        {
            try
            {
                if (input == null || input.Ids == null || !input.Ids.Any())
                    throw NCCException.Oh("工资记录ID列表不能为空");
                var salaries = await _db.Queryable<LqTechTeacherSalaryStatisticsEntity>()
                    .Where(s => input.Ids.Contains(s.Id))
                    .ToListAsync();
                if (!salaries.Any())
                    throw NCCException.Oh("未找到指定的工资记录");
                var lockedCount = 0;
                var unlockedCount = 0;
                var skippedCount = 0;
                foreach (var salary in salaries)
                {
                    if (salary.EmployeeConfirmStatus == 1 && !input.IsLocked)
                    {
                        skippedCount++;
                        continue;
                    }
                    salary.IsLocked = input.IsLocked ? 1 : 0;
                    salary.UpdateTime = DateTime.Now;
                    if (input.IsLocked) lockedCount++; else unlockedCount++;
                }
                await _db.Updateable(salaries).ExecuteCommandAsync();
                var action = input.IsLocked ? "锁定" : "解锁";
                var count = input.IsLocked ? lockedCount : unlockedCount;
                var message = $"{action}成功：{count}条";
                if (skippedCount > 0)
                    message += $"，跳过{skippedCount}条（已确认的记录不能解锁）";
                return message;
            }
            catch (Exception ex)
            {
                throw NCCException.Oh($"锁定/解锁工资条失败: {ex.Message}");
            }
        }

        /// <summary>
        /// 批量锁定当月所有工资
        /// </summary>
        /// <remarks>
        /// 批量锁定指定月份的所有科技部老师工资记录
        /// </remarks>
        /// <param name="input">批量锁定输入参数</param>
        /// <returns>锁定结果</returns>
        [HttpPost("lock-by-month")]
        public async Task<dynamic> LockSalaryByMonth([FromBody] SalaryLockByMonthInput input)
        {
            try
            {
                if (input == null)
                    throw NCCException.Oh("参数不能为空");

                if (input.Year <= 0 || input.Month <= 0 || input.Month > 12)
                    throw NCCException.Oh("年份和月份参数不正确");

                var monthStr = $"{input.Year}{input.Month:D2}";

                var salaries = await _db.Queryable<LqTechTeacherSalaryStatisticsEntity>()
                    .Where(s => s.StatisticsMonth == monthStr)
                    .ToListAsync();

                if (!salaries.Any())
                    throw NCCException.Oh($"未找到{input.Year}年{input.Month}月的工资记录");

                var lockedCount = 0;
                var unlockedCount = 0;
                var skippedCount = 0;
                var alreadyLockedCount = 0;

                foreach (var salary in salaries)
                {
                    if (salary.EmployeeConfirmStatus == 1 && !input.IsLocked)
                    {
                        skippedCount++;
                        continue;
                    }

                    if (salary.IsLocked == 1 && input.IsLocked)
                    {
                        alreadyLockedCount++;
                        continue;
                    }

                    if (salary.IsLocked == 0 && !input.IsLocked)
                    {
                        alreadyLockedCount++;
                        continue;
                    }

                    salary.IsLocked = input.IsLocked ? 1 : 0;
                    salary.UpdateTime = DateTime.Now;

                    if (input.IsLocked)
                        lockedCount++;
                    else
                        unlockedCount++;
                }

                if (lockedCount > 0 || unlockedCount > 0)
                {
                    var salariesToUpdate = salaries.Where(s =>
                        (input.IsLocked && s.IsLocked == 0) ||
                        (!input.IsLocked && s.IsLocked == 1 && s.EmployeeConfirmStatus != 1)
                    ).ToList();

                    if (salariesToUpdate.Any())
                    {
                        await _db.Updateable(salariesToUpdate)
                            .UpdateColumns(s => new { s.IsLocked, s.UpdateTime })
                            .ExecuteCommandAsync();
                    }
                }

                var action = input.IsLocked ? "锁定" : "解锁";
                var count = input.IsLocked ? lockedCount : unlockedCount;
                var message = $"{action}成功：{count}条";

                if (alreadyLockedCount > 0)
                    message += $"，跳过{alreadyLockedCount}条（已是{action}状态）";

                if (skippedCount > 0)
                    message += $"，跳过{skippedCount}条（已确认的记录不能解锁）";

                return new
                {
                    success = true,
                    message = message,
                    total = salaries.Count,
                    locked = lockedCount,
                    unlocked = unlockedCount,
                    skipped = skippedCount,
                    alreadyLocked = alreadyLockedCount
                };
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "批量锁定当月工资失败");
                var action = input?.IsLocked == true ? "锁定" : "解锁";
                throw NCCException.Oh($"批量{action}当月工资失败: {ex.Message}");
            }
        }

        #endregion

        #region 导入工资

        /// <summary>
        /// 从Excel导入科技部老师工资数据
        /// </summary>
        /// <remarks>
        /// 从Excel文件导入科技部老师工资数据，Excel第一列必须是ID（主键）
        /// 
        /// 导入规则：
        /// 1. Excel第一列是ID（主键），如果为空则自动生成新ID
        /// 2. 如果ID在数据库中存在，则更新记录（需检查是否已锁定或已确认）
        /// 3. 如果ID在数据库中不存在，则新增记录
        /// 4. 已锁定（IsLocked=1）或已确认（EmployeeConfirmStatus=1）的记录不能导入覆盖
        /// 
        /// Excel字段顺序（第一列为ID）：
        /// ID, 门店名称, 员工姓名, 岗位, 开单业绩, ...（共54列）
        /// </remarks>
        /// <param name="file">Excel文件</param>
        /// <returns>导入结果</returns>
        /// <response code="200">导入成功</response>
        /// <response code="400">文件格式错误或数据验证失败</response>
        [HttpPost("import")]
        public async Task<dynamic> ImportSalaryFromExcel(IFormFile file)
        {
            try
            {
                if (file == null || file.Length == 0)
                {
                    throw NCCException.Oh("请选择要上传的Excel文件");
                }

                // 检查文件格式
                var allowedExtensions = new[] { ".xlsx", ".xls" };
                var fileExtension = Path.GetExtension(file.FileName).ToLowerInvariant();
                if (!allowedExtensions.Contains(fileExtension))
                {
                    throw NCCException.Oh("只支持.xlsx和.xls格式的Excel文件");
                }

                var recordsToInsert = new List<LqTechTeacherSalaryStatisticsEntity>();
                var recordsToUpdate = new List<LqTechTeacherSalaryStatisticsEntity>();
                var errorMessages = new List<string>();
                var successCount = 0;
                var failCount = 0;
                var skippedCount = 0;

                // 保存临时文件
                var tempFilePath = Path.Combine(Path.GetTempPath(), Guid.NewGuid().ToString() + Path.GetExtension(file.FileName));
                try
                {
                    using (var stream = new FileStream(tempFilePath, FileMode.Create))
                    {
                        await file.CopyToAsync(stream);
                    }

                    // 使用ExcelImportHelper读取Excel文件
                    var dataTable = ExcelImportHelper.ToDataTable(tempFilePath, 0, 0);

                    if (dataTable.Rows.Count == 0)
                    {
                        throw NCCException.Oh("Excel文件中没有数据行");
                    }

                    // ExcelImportHelper.ToDataTable(filePath, 0, 0)会将第一行作为标题行，数据从第二行开始
                    // 所以DataTable.Rows[0]是Excel的第一条数据行，应该从i=0开始循环
                    for (int i = 0; i < dataTable.Rows.Count; i++)
                    {
                        try
                        {
                            var row = dataTable.Rows[i];

                            // 安全获取列值
                            Func<int, string> GetColumnValue = (colIndex) =>
                            {
                                if (colIndex < row.ItemArray.Length)
                                {
                                    return row[colIndex]?.ToString()?.Trim() ?? "";
                                }
                                return "";
                            };

                            // 第一列是ID
                            var id = GetColumnValue(0);

                            // 判断Excel格式：如果第一列是"门店名称"等中文，说明是旧格式（没有ID列）
                            var firstColumnValue = GetColumnValue(0);
                            bool isOldFormat = false;
                            if (!string.IsNullOrWhiteSpace(firstColumnValue) &&
                                (firstColumnValue == "门店名称" || firstColumnValue.Contains("门店") || (!string.IsNullOrWhiteSpace(firstColumnValue) && !long.TryParse(firstColumnValue, out _) && firstColumnValue.Length > 20)))
                            {
                                isOldFormat = true;
                                id = "";
                            }

                            // 根据Excel格式确定字段索引
                            int storeNameIndex = isOldFormat ? 0 : 1;
                            int employeeNameIndex = isOldFormat ? 1 : 2;

                            // 跳过空行
                            var employeeName = GetColumnValue(employeeNameIndex);
                            var storeName = GetColumnValue(storeNameIndex);

                            if (string.IsNullOrWhiteSpace(id) && string.IsNullOrWhiteSpace(employeeName))
                            {
                                continue;
                            }

                            // 如果ID为空，尝试根据员工姓名和门店名称匹配现有记录的ID
                            if (string.IsNullOrWhiteSpace(id) && !string.IsNullOrWhiteSpace(employeeName))
                            {
                                var matchedRecord = await _db.Queryable<LqTechTeacherSalaryStatisticsEntity>()
                                    .Where(x => x.EmployeeName == employeeName)
                                    .WhereIF(!string.IsNullOrWhiteSpace(storeName), x => x.StoreName == storeName)
                                    .OrderBy(x => x.CreateTime, OrderByType.Desc)
                                    .FirstAsync();

                                if (matchedRecord != null)
                                {
                                    id = matchedRecord.Id;
                                }
                            }

                            // 验证必填字段
                            if (string.IsNullOrWhiteSpace(employeeName))
                            {
                                errorMessages.Add($"第{i + 1}行：员工姓名不能为空");
                                failCount++;
                                continue;
                            }

                            // 辅助方法：清理数值字符串
                            Func<string, decimal> ParseDecimal = (str) =>
                            {
                                if (string.IsNullOrWhiteSpace(str))
                                    return 0;
                                var cleaned = str.Trim()
                                    .Replace(",", "")
                                    .Replace("，", "")
                                    .Replace("￥", "")
                                    .Replace("$", "")
                                    .Replace("元", "")
                                    .Replace("%", "")
                                    .Replace(" ", "");
                                if (decimal.TryParse(cleaned, out decimal result))
                                    return result;
                                return 0;
                            };

                            Func<string, int> ParseInt = (str) =>
                            {
                                if (string.IsNullOrWhiteSpace(str))
                                    return 0;
                                var cleaned = str.Trim().Replace(",", "").Replace("，", "").Replace(" ", "");
                                if (int.TryParse(cleaned, out int result))
                                    return result;
                                return 0;
                            };

                            // 如果Excel中有ID，查找现有记录
                            LqTechTeacherSalaryStatisticsEntity existing = null;
                            if (!string.IsNullOrWhiteSpace(id))
                            {
                                existing = await _db.Queryable<LqTechTeacherSalaryStatisticsEntity>()
                                    .Where(x => x.Id == id)
                                    .FirstAsync();

                                if (existing != null)
                                {
                                    // 检查是否已锁定或已确认
                                    if (existing.IsLocked == 1)
                                    {
                                        errorMessages.Add($"第{i + 1}行：员工 {existing.EmployeeName} (ID: {id}) 的工资已锁定，不能导入覆盖");
                                        skippedCount++;
                                        failCount++;
                                        continue;
                                    }

                                    if (existing.EmployeeConfirmStatus == 1)
                                    {
                                        errorMessages.Add($"第{i + 1}行：员工 {existing.EmployeeName} (ID: {id}) 的工资已确认，不能导入覆盖");
                                        skippedCount++;
                                        failCount++;
                                        continue;
                                    }
                                }
                            }

                            // 创建或更新实体
                            LqTechTeacherSalaryStatisticsEntity entity;
                            if (existing != null)
                            {
                                entity = existing;
                                entity.EmployeeConfirmStatus = 0;
                                entity.EmployeeConfirmTime = null;
                                entity.EmployeeConfirmRemark = null;
                            }
                            else
                            {
                                entity = new LqTechTeacherSalaryStatisticsEntity
                                {
                                    Id = string.IsNullOrWhiteSpace(id) ? YitIdHelper.NextId().ToString() : id,
                                    EmployeeConfirmStatus = 0,
                                    IsLocked = 0,
                                    CreateTime = DateTime.Now,
                                    UpdateTime = DateTime.Now
                                };
                            }

                            // 根据Excel格式计算字段索引偏移量
                            int offset = isOldFormat ? 0 : 1;

                            // 映射Excel字段到实体属性（根据Excel列顺序，第一列是ID，所以业务字段从索引1开始）
                            // Excel列顺序：ID, 门店名称, 员工姓名, 岗位, 开单业绩, 消耗业绩, 退卡业绩, 总业绩, 项目数, 底薪档位, 底薪, ...
                            entity.StoreName = storeName;
                            entity.EmployeeName = employeeName;
                            entity.Position = GetColumnValue(2 + offset);
                            entity.OrderAchievement = ParseDecimal(GetColumnValue(3 + offset));
                            entity.ConsumeAchievement = ParseDecimal(GetColumnValue(4 + offset));
                            entity.RefundAchievement = ParseDecimal(GetColumnValue(5 + offset));
                            entity.TotalPerformance = ParseDecimal(GetColumnValue(6 + offset));
                            entity.ProjectCount = ParseDecimal(GetColumnValue(7 + offset));
                            entity.BaseSalaryLevel = ParseInt(GetColumnValue(8 + offset));
                            entity.BaseSalary = ParseDecimal(GetColumnValue(9 + offset));
                            entity.PerformanceCommissionRate = ParseDecimal(GetColumnValue(10 + offset));
                            entity.PerformanceCommissionAmount = ParseDecimal(GetColumnValue(11 + offset));
                            entity.ConsumeCommissionRate = ParseDecimal(GetColumnValue(12 + offset));
                            entity.ConsumeCommissionAmount = ParseDecimal(GetColumnValue(13 + offset));
                            entity.HandworkFee = ParseDecimal(GetColumnValue(14 + offset));
                            entity.TotalCommission = ParseDecimal(GetColumnValue(15 + offset));
                            entity.WorkingDays = ParseDecimal(GetColumnValue(16 + offset));
                            entity.LeaveDays = ParseDecimal(GetColumnValue(17 + offset));
                            entity.TransportationAllowance = ParseDecimal(GetColumnValue(18 + offset));
                            entity.LessRest = ParseDecimal(GetColumnValue(19 + offset));
                            entity.FullAttendance = ParseDecimal(GetColumnValue(20 + offset));
                            entity.MonthlyTrainingSubsidy = ParseDecimal(GetColumnValue(21 + offset));
                            entity.MonthlyTransportSubsidy = ParseDecimal(GetColumnValue(22 + offset));
                            entity.LastMonthTrainingSubsidy = ParseDecimal(GetColumnValue(23 + offset));
                            entity.LastMonthTransportSubsidy = ParseDecimal(GetColumnValue(24 + offset));
                            entity.TotalSubsidy = ParseDecimal(GetColumnValue(25 + offset));
                            entity.CalculatedGrossSalary = ParseDecimal(GetColumnValue(26 + offset));
                            entity.GuaranteedSalary = ParseDecimal(GetColumnValue(27 + offset));
                            entity.GuaranteedLeaveDeduction = ParseDecimal(GetColumnValue(28 + offset));
                            entity.GuaranteedBaseSalary = ParseDecimal(GetColumnValue(29 + offset));
                            entity.GuaranteedSupplement = ParseDecimal(GetColumnValue(30 + offset));
                            entity.FinalGrossSalary = ParseDecimal(GetColumnValue(31 + offset));
                            entity.MissingCard = ParseDecimal(GetColumnValue(32 + offset));
                            entity.LateArrival = ParseDecimal(GetColumnValue(33 + offset));
                            entity.LeaveDeduction = ParseDecimal(GetColumnValue(34 + offset));
                            entity.SocialInsuranceDeduction = ParseDecimal(GetColumnValue(35 + offset));
                            entity.RewardDeduction = ParseDecimal(GetColumnValue(36 + offset));
                            entity.AccommodationDeduction = ParseDecimal(GetColumnValue(37 + offset));
                            entity.StudyPeriodDeduction = ParseDecimal(GetColumnValue(38 + offset));
                            entity.WorkClothesDeduction = ParseDecimal(GetColumnValue(39 + offset));
                            entity.TotalDeduction = ParseDecimal(GetColumnValue(40 + offset));
                            entity.Bonus = ParseDecimal(GetColumnValue(41 + offset));
                            entity.ReturnPhoneDeposit = ParseDecimal(GetColumnValue(42 + offset));
                            entity.ReturnAccommodationDeposit = ParseDecimal(GetColumnValue(43 + offset));
                            entity.LastMonthSupplement = ParseDecimal(GetColumnValue(44 + offset));
                            entity.ActualSalary = ParseDecimal(GetColumnValue(45 + offset));
                            entity.MonthlyPaymentStatus = GetColumnValue(46 + offset);
                            entity.PaidAmount = ParseDecimal(GetColumnValue(47 + offset));
                            entity.PendingAmount = ParseDecimal(GetColumnValue(48 + offset));
                            entity.MonthlyTotalPayment = ParseDecimal(GetColumnValue(49 + offset));

                            // 处理"是否新店"字段
                            var isNewStoreStr = GetColumnValue(50 + offset);
                            entity.IsNewStore = (isNewStoreStr == "是" || isNewStoreStr == "1" || isNewStoreStr.ToLower() == "true") ? "是" : "否";

                            // 处理"新店保护阶段"字段
                            var newStoreProtectionStageStr = GetColumnValue(51 + offset);
                            if (int.TryParse(newStoreProtectionStageStr, out int protectionStage))
                            {
                                entity.NewStoreProtectionStage = protectionStage;
                            }

                            // 处理"离职状态"字段（第54列，索引53+offset）
                            var terminatedStatus = GetColumnValue(53 + offset);
                            entity.IsTerminated = (terminatedStatus == "离职" || terminatedStatus == "1") ? 1 : 0;

                            // 处理Excel中没有的字段
                            if (existing != null)
                            {
                                entity.StoreId = existing.StoreId;
                                entity.EmployeeId = existing.EmployeeId;
                                entity.StatisticsMonth = existing.StatisticsMonth;
                            }
                            else
                            {
                                // 新增记录：尝试根据员工姓名和门店名称查找EmployeeId和StoreId
                                if (!string.IsNullOrWhiteSpace(employeeName))
                                {
                                    var user = await _db.Queryable<UserEntity>()
                                        .Where(u => u.RealName == employeeName)
                                        .FirstAsync();

                                    if (user != null)
                                    {
                                        entity.EmployeeId = user.Id;
                                        entity.EmployeeAccount = user.Account;

                                        if (!string.IsNullOrEmpty(user.Mdid) && string.IsNullOrWhiteSpace(storeName))
                                        {
                                            entity.StoreId = user.Mdid;
                                        }
                                    }

                                    if (!string.IsNullOrWhiteSpace(storeName) && string.IsNullOrEmpty(entity.StoreId))
                                    {
                                        var store = await _db.Queryable<LqMdxxEntity>()
                                            .Where(s => s.Dm == storeName)
                                            .FirstAsync();

                                        if (store != null)
                                        {
                                            entity.StoreId = store.Id;
                                        }
                                    }
                                }
                            }

                            entity.UpdateTime = DateTime.Now;

                            if (existing != null)
                            {
                                recordsToUpdate.Add(entity);
                            }
                            else
                            {
                                recordsToInsert.Add(entity);
                            }

                            successCount++;
                        }
                        catch (Exception ex)
                        {
                            errorMessages.Add($"第{i + 1}行数据处理失败: {ex.Message}");
                            failCount++;
                        }
                    }
                }
                finally
                {
                    // 清理临时文件
                    if (File.Exists(tempFilePath))
                    {
                        File.Delete(tempFilePath);
                    }
                }

                // 批量插入新记录
                if (recordsToInsert.Any())
                {
                    await _db.Insertable(recordsToInsert).ExecuteCommandAsync();
                }

                // 批量更新现有记录
                if (recordsToUpdate.Any())
                {
                    // 使用IgnoreColumns排除CreateTime和CreateUser，确保其他所有字段都被更新
                    await _db.Updateable(recordsToUpdate)
                        .IgnoreColumns(x => x.CreateTime)
                        .IgnoreColumns(x => x.CreateUser)
                        .ExecuteCommandAsync();
                }

                return new
                {
                    success = true,
                    message = $"导入完成：成功 {successCount} 条，失败 {failCount} 条，跳过 {skippedCount} 条（已锁定或已确认）",
                    successCount = successCount,
                    failCount = failCount,
                    skippedCount = skippedCount,
                    errors = errorMessages
                };
            }
            catch (Exception ex)
            {
                throw NCCException.Oh($"导入科技部老师工资数据失败: {ex.Message}");
            }
        }

        #endregion
    }
}