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.LqAssistantSalary;
using NCC.Extend.Entitys.Dto.LqSalary;
using NCC.Extend.Entitys.lq_assistant_salary_statistics;
using NCC.Extend.Entitys.lq_attendance_summary;
using NCC.Extend.Entitys.lq_hytk_hytk;
using NCC.Extend.Entitys.lq_kd_kdjlb;
using NCC.Extend.Entitys.lq_md_target;
using NCC.Extend.Entitys.lq_md_xdbhsj;
using NCC.Extend.Entitys.lq_mdxx;
using NCC.Extend.Entitys.lq_xh_hyhk;
using NCC.Extend.Entitys.lq_xh_jksyj;
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 = "LqAssistantSalary", Order = 301)]
    [Route("api/Extend/[controller]")]
    public class LqAssistantSalaryService : IDynamicApiController, ITransient
    {
        private readonly ISqlSugarClient _db;
        private readonly ILogger<LqAssistantSalaryService> _logger;

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

        /// <summary>
        /// 获取店助工资列表
        /// </summary>
        /// <param name="input">查询参数</param>
        /// <returns>店助工资分页列表</returns>
        [HttpGet("assistant")]
        public async Task<dynamic> GetAssistantSalaryList([FromQuery] AssistantSalaryInput input)
        {
            var monthStr = $"{input.Year}{input.Month:D2}";

            // 查询数据
            var query = _db.Queryable<LqAssistantSalaryStatisticsEntity>()
                .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.EmployeeId.Contains(input.Keyword));
            }

            var list = await query.Select(x => new AssistantSalaryOutput
            {
                Id = x.Id,
                StoreName = x.StoreName,
                EmployeeName = x.EmployeeName,
                Position = x.Position,
                StoreTotalPerformance = x.StoreTotalPerformance,
                StoreBillingPerformance = x.StoreBillingPerformance,
                StoreRefundPerformance = x.StoreRefundPerformance,
                StoreLifeline = x.StoreLifeline,
                PerformanceCompletionRate = x.PerformanceCompletionRate,
                CommissionRate = x.CommissionRate,
                CommissionAmount = x.CommissionAmount,
                HeadCount = x.HeadCount,
                Stage1TargetHeadCount = x.Stage1TargetHeadCount,
                Stage2TargetHeadCount = x.Stage2TargetHeadCount,
                ReachedStage1 = x.ReachedStage1,
                ReachedStage2 = x.ReachedStage2,
                StageRewardAmount = x.StageRewardAmount,
                Stage1Reward = x.Stage1Reward,
                Stage2Reward = x.Stage2Reward,
                BaseSalary = x.BaseSalary,
                PhoneManagementFee = x.PhoneManagementFee,
                WorkingDays = x.WorkingDays,
                LeaveDays = x.LeaveDays,
                GrossSalary = x.GrossSalary,
                ActualSalary = x.ActualSalary,
                TotalDeduction = x.TotalDeduction,
                TotalSubsidy = x.TotalSubsidy,
                Bonus = x.Bonus,
                ReturnPhoneDeposit = x.ReturnPhoneDeposit,
                ReturnAccommodationDeposit = x.ReturnAccommodationDeposit,
                MonthlyPaymentStatus = x.MonthlyPaymentStatus,
                PaidAmount = x.PaidAmount,
                PendingAmount = x.PendingAmount,
                LastMonthSupplement = x.LastMonthSupplement,
                MonthlyTotalPayment = x.MonthlyTotalPayment,
                IsLocked = x.IsLocked,
                EmployeeConfirmStatus = x.EmployeeConfirmStatus,
                UpdateTime = x.UpdateTime,
                StoreType = x.StoreType,
                StoreCategory = x.StoreCategory,
                IsNewStore = x.IsNewStore,
                NewStoreProtectionStage = x.NewStoreProtectionStage
            })
            .ToPagedListAsync(input.currentPage, input.pageSize);

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

        /// <summary>
        /// 通过月份和员工ID查询工资（仅查询已锁定且未确认的工资）
        /// </summary>
        /// <remarks>
        /// **重要**：此接口只能查询已锁定（IsLocked=1）且未确认（EmployeeConfirmStatus=0或null）的工资记录
        /// **注意**：已确认的工资记录无法通过此接口查询
        /// </remarks>
        [HttpGet("query-by-employee")]
        public async Task<AssistantSalaryOutput> 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<LqAssistantSalaryStatisticsEntity>()
                .Where(x => x.StatisticsMonth == monthStr && x.EmployeeId == input.EmployeeId && x.IsLocked == 1 && x.EmployeeConfirmStatus != 1)
                .Select(x => new AssistantSalaryOutput
                {
                    Id = x.Id,
                    StoreName = x.StoreName,
                    EmployeeName = x.EmployeeName,
                    Position = x.Position,
                    StoreTotalPerformance = x.StoreTotalPerformance,
                    StoreBillingPerformance = x.StoreBillingPerformance,
                    StoreRefundPerformance = x.StoreRefundPerformance,
                    StoreLifeline = x.StoreLifeline,
                    PerformanceCompletionRate = x.PerformanceCompletionRate,
                    CommissionRate = x.CommissionRate,
                    CommissionAmount = x.CommissionAmount,
                    HeadCount = x.HeadCount,
                    Stage1TargetHeadCount = x.Stage1TargetHeadCount,
                    Stage2TargetHeadCount = x.Stage2TargetHeadCount,
                    ReachedStage1 = x.ReachedStage1,
                    ReachedStage2 = x.ReachedStage2,
                    StageRewardAmount = x.StageRewardAmount,
                    Stage1Reward = x.Stage1Reward,
                    Stage2Reward = x.Stage2Reward,
                    BaseSalary = x.BaseSalary,
                    PhoneManagementFee = x.PhoneManagementFee,
                    WorkingDays = x.WorkingDays,
                    LeaveDays = x.LeaveDays,
                    GrossSalary = x.GrossSalary,
                    ActualSalary = x.ActualSalary,
                    TotalDeduction = x.TotalDeduction,
                    TotalSubsidy = x.TotalSubsidy,
                    Bonus = x.Bonus,
                    ReturnPhoneDeposit = x.ReturnPhoneDeposit,
                    ReturnAccommodationDeposit = x.ReturnAccommodationDeposit,
                    MonthlyPaymentStatus = x.MonthlyPaymentStatus,
                    PaidAmount = x.PaidAmount,
                    PendingAmount = x.PendingAmount,
                    LastMonthSupplement = x.LastMonthSupplement,
                    MonthlyTotalPayment = x.MonthlyTotalPayment,
                    IsLocked = x.IsLocked,
                    EmployeeConfirmStatus = x.EmployeeConfirmStatus,
                    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/assistant")]
        public async Task CalculateAssistantSalary(int year, int month)
        {
            var startDate = new DateTime(year, month, 1);
            var endDate = startDate.AddMonths(1).AddDays(-1);
            var monthStr = $"{year}{month:D2}";
            var daysInMonth = DateTime.DaysInMonth(year, month); // 当月天数

            // 1. 获取基础数据

            // 1.1 获取店助员工列表（从BASE_USER表，岗位为"店助"或"店助主任"）
            var assistantUserList = await _db.Queryable<UserEntity>()
                .Where(x => (x.Gw == "店助" || x.Gw == "店助主任") && x.DeleteMark == null && x.EnabledMark == 1)
                .Select(x => new { x.Id, x.RealName, x.Mdid, x.Gw })
                .ToListAsync();

            if (!assistantUserList.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.4 门店目标信息 (lq_md_target) - 包含门店生命线和阶段目标
            var storeTargets = await _db.Queryable<LqMdTargetEntity>()
                .Where(x => x.Month == monthStr)
                .ToListAsync();
            var storeTargetDict = storeTargets.Where(x => !string.IsNullOrEmpty(x.StoreId))
                .ToDictionary(x => x.StoreId, x => x);

            // 1.5 门店新店保护信息 (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.6 门店总业绩计算 (开单实付 - 退卡金额)
            // 开单实付
            var storeBillingList = await _db.Queryable<LqKdKdjlbEntity>()
                .Where(x => x.Kdrq >= startDate && x.Kdrq <= endDate.AddDays(1) && x.IsEffective == 1)
                .Select(x => new { x.Djmd, x.Sfyj })
                .ToListAsync();
            var storeBillingDict = storeBillingList
                .Where(x => !string.IsNullOrEmpty(x.Djmd))
                .GroupBy(x => x.Djmd)
                .ToDictionary(g => g.Key, g => g.Sum(x => x.Sfyj));

            // 退卡金额（使用F_ActualRefundAmount，如果没有则使用tkje）
            var storeRefundList = await _db.Queryable<LqHytkHytkEntity>()
                .Where(x => x.Tksj >= startDate && x.Tksj <= endDate.AddDays(1) && x.IsEffective == 1)
                .Select(x => new { x.Md, x.ActualRefundAmount, x.Tkje })
                .ToListAsync();
            var storeRefundDict = storeRefundList
                .Where(x => !string.IsNullOrEmpty(x.Md))
                .GroupBy(x => x.Md)
                .ToDictionary(g => g.Key, g => g.Sum(x => x.ActualRefundAmount ?? x.Tkje ?? 0));

            // 1.7 进店消耗人数统计（有消费金额的，按门店按月去重客户数）
            // 使用SQL查询优化性能
            var headcountSql = $@"
                SELECT 
                    hyhk.md as StoreId,
                    COUNT(DISTINCT hyhk.hy) as HeadCount
                FROM lq_xh_hyhk hyhk
                WHERE hyhk.F_IsEffective = 1
                    AND DATE_FORMAT(hyhk.hksj, '%Y%m') = @monthStr
                    AND EXISTS (
                        SELECT 1 
                        FROM lq_xh_jksyj jksyj 
                        WHERE jksyj.glkdbh = hyhk.F_Id 
                            AND jksyj.F_IsEffective = 1 
                            AND jksyj.jksyj > 0
                    )
                GROUP BY hyhk.md";

            var headcountData = await _db.Ado.SqlQueryAsync<dynamic>(headcountSql, new { monthStr });
            var headcountDict = headcountData
                .Where(x => x.StoreId != null)
                .ToDictionary(x => x.StoreId.ToString(), x => Convert.ToInt32(x.HeadCount));

            // 1.8 考勤数据 (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 assistantSalaryList = new List<LqAssistantSalaryStatisticsEntity>();

            foreach (var assistantUser in assistantUserList)
            {
                var salary = new LqAssistantSalaryStatisticsEntity
                {
                    Id = YitIdHelper.NextId().ToString(),
                    EmployeeId = assistantUser.Id,
                    EmployeeName = assistantUser.RealName,
                    StatisticsMonth = monthStr,
                    Position = assistantUser.Gw ?? "店助", // 使用Gw字段，如果为空则默认为"店助"
                    CreateTime = DateTime.Now,
                    UpdateTime = DateTime.Now,
                    IsLocked = 0,
                    MonthlyPaymentStatus = "未发放"
                };

                // 2.1 填充门店信息
                string storeId = assistantUser.Mdid;
                if (string.IsNullOrEmpty(storeId))
                {
                    // 如果用户没有门店ID，跳过
                    continue;
                }

                salary.StoreId = storeId;

                if (storeDict.ContainsKey(storeId))
                {
                    var store = storeDict[storeId];
                    salary.StoreName = store.Dm;
                    salary.StoreType = store.StoreType;
                    salary.StoreCategory = store.StoreCategory;

                    // 数据校验：门店分类必须设置
                    if (!salary.StoreCategory.HasValue)
                    {
                        throw new Exception($"门店【{store.Dm}】的门店分类未设置，无法计算店助工资");
                    }
                }

                // 2.2 填充新店保护信息
                if (newStoreProtectionDict.ContainsKey(storeId))
                {
                    var protection = newStoreProtectionDict[storeId];
                    salary.IsNewStore = "是";
                    salary.NewStoreProtectionStage = protection.Stage;
                }
                else
                {
                    salary.IsNewStore = "否";
                    salary.NewStoreProtectionStage = 0;
                }

                // 2.3 获取门店目标信息（门店生命线和阶段目标）
                // 如果没有设置门店目标，则相关字段设为0（适用于新店未开张等情况）
                if (!storeTargetDict.ContainsKey(storeId))
                {
                    // 门店目标未设置时，所有目标相关字段设为0
                    salary.StoreLifeline = 0;
                    salary.Stage1TargetHeadCount = 0;
                    salary.Stage2TargetHeadCount = 0;
                }
                else
                {
                    var storeTarget = storeTargetDict[storeId];
                    salary.StoreLifeline = storeTarget.StoreLifeline;

                    // 阶段目标设置（如果未设置，则奖励金额为0）
                    salary.Stage1TargetHeadCount = storeTarget.AssistantHeadcountTargetStage1 > 0
                        ? (int)storeTarget.AssistantHeadcountTargetStage1
                        : 0;
                    salary.Stage2TargetHeadCount = storeTarget.AssistantHeadcountTargetStage2 > 0
                        ? (int)storeTarget.AssistantHeadcountTargetStage2
                        : 0;
                }

                // 2.4 计算门店业绩
                decimal billing = storeBillingDict.ContainsKey(storeId) ? storeBillingDict[storeId] : 0;
                decimal refund = storeRefundDict.ContainsKey(storeId) ? storeRefundDict[storeId] : 0;
                salary.StoreBillingPerformance = billing;
                salary.StoreRefundPerformance = refund;
                salary.StoreTotalPerformance = billing - refund;

                // 计算业绩完成率
                if (salary.StoreLifeline > 0)
                {
                    salary.PerformanceCompletionRate = salary.StoreTotalPerformance / salary.StoreLifeline;
                }
                else
                {
                    salary.PerformanceCompletionRate = 0;
                }

                // 2.5 计算提成比例（固定比例，不随在店天数变化）
                // 判断岗位类型：店助 或 店助主任
                bool isDirector = salary.Position == "店助主任";

                decimal commissionRate = 0;

                // 如果门店生命线未设置（<=0），则没有提成
                if (salary.StoreLifeline <= 0)
                {
                    commissionRate = 0;
                }
                else
                {
                    // 计算业绩完成率
                    decimal performanceRatio = salary.StoreTotalPerformance / salary.StoreLifeline;

                    // 根据岗位类型确定提成比例
                    // 店助和店助主任使用相同的分段提成规则，但100%以上部分比例不同
                    decimal totalCommission;
                    if (isDirector)
                    {
                        // 店助主任提成规则（分段式）：
                        // 1. 前提条件：门店业绩必须达到门店生命线的70%，否则无提成
                        // 2. 70% ≤ 业绩 < 100%：整个业绩按0.4%
                        // 3. 业绩 ≥ 100%：分段式
                        //    - 0-100%部分（整个生命线）：0.4%
                        //    - 100%以上部分：1.6%（与店助的0.6%不同）
                        totalCommission = CalculateDirectorCommission(salary.StoreTotalPerformance, salary.StoreLifeline);
                    }
                    else
                    {
                        // 店助提成规则（分段式）：
                        // 1. 前提条件：门店业绩必须达到门店生命线的70%，否则无提成
                        // 2. 70% ≤ 业绩 < 100%：整个业绩按0.4%
                        // 3. 业绩 ≥ 100%：分段式
                        //    - 0-100%部分（整个生命线）：0.4%
                        //    - 100%以上部分：0.6%
                        totalCommission = CalculateAssistantCommission(salary.StoreTotalPerformance, salary.StoreLifeline);
                    }

                    // 计算平均提成比例（用于显示）
                    if (salary.StoreTotalPerformance > 0)
                    {
                        commissionRate = totalCommission / salary.StoreTotalPerformance;
                    }
                    else
                    {
                        commissionRate = 0;
                    }
                }

                salary.CommissionRate = commissionRate;

                // 2.6 统计进店消耗人数
                salary.HeadCount = headcountDict.ContainsKey(storeId) ? headcountDict[storeId] : 0;

                // 2.7 计算门店总阶段奖励（先计算门店级别的奖励）
                decimal storeTotalStageReward = 0;
                bool reachedStage1 = false;
                bool reachedStage2 = false;

                // 如果阶段目标未设置（为0），则没有奖励考核，奖励金额为0
                if (salary.Stage1TargetHeadCount <= 0 && salary.Stage2TargetHeadCount <= 0)
                {
                    // 阶段目标未设置，没有奖励考核
                    salary.ReachedStage1 = "否";
                    salary.ReachedStage2 = "否";
                    storeTotalStageReward = 0m;
                }
                else
                {
                    // 阶段目标已设置，进行奖励考核
                    reachedStage1 = salary.Stage1TargetHeadCount > 0 && salary.HeadCount >= salary.Stage1TargetHeadCount;
                    reachedStage2 = salary.Stage2TargetHeadCount > 0 && salary.HeadCount >= salary.Stage2TargetHeadCount;

                    salary.ReachedStage1 = reachedStage1 ? "是" : "否";
                    salary.ReachedStage2 = reachedStage2 ? "是" : "否";

                    // 阶段奖励计算规则：
                    // - 如果达到第二阶段，获得400元（第一阶段200 + 第二阶段200）
                    // - 如果只达到第一阶段，获得200元
                    // - 如果都没达到，获得0元
                    if (reachedStage2)
                    {
                        storeTotalStageReward = 400m;
                    }
                    else if (reachedStage1)
                    {
                        storeTotalStageReward = 200m;
                    }
                    else
                    {
                        storeTotalStageReward = 0m;
                    }
                }

                // 2.8 考勤数据
                int workingDays = 0;
                if (attendanceDict.ContainsKey(assistantUser.Id))
                {
                    var attendance = attendanceDict[assistantUser.Id];
                    workingDays = (int)attendance.WorkDays;
                    salary.WorkingDays = workingDays;
                    salary.LeaveDays = (int)attendance.LeaveDays;
                }
                else
                {
                    salary.WorkingDays = 0;
                    salary.LeaveDays = 0;
                }

                // 2.9 计算底薪（店助和店助主任底薪规则相同，都根据门店分类确定，按在店天数比例计算）
                decimal baseSalaryFull = CalculateBaseSalary(salary.StoreCategory.Value);
                if (daysInMonth > 0 && workingDays > 0)
                {
                    salary.BaseSalary = baseSalaryFull / daysInMonth * workingDays;
                }
                else
                {
                    salary.BaseSalary = 0;
                }

                // 2.10 计算手机管理费（按在店天数比例计算）
                decimal phoneManagementFeeFull = 150m;
                if (daysInMonth > 0 && workingDays > 0)
                {
                    salary.PhoneManagementFee = phoneManagementFeeFull / daysInMonth * workingDays;
                }
                else
                {
                    salary.PhoneManagementFee = 0;
                }

                // 2.11 按在店天数比例计算店助的提成和奖励
                // 逻辑：提成金额 = 门店总提成（阶梯计算） / 当月天数 × 在店天数
                //       阶段奖励 = 门店总奖励 / 当月天数 × 在店天数
                if (daysInMonth > 0 && workingDays > 0)
                {
                    // 先计算门店总提成（阶梯计算）- 根据岗位类型使用不同规则
                    decimal storeTotalCommission;
                    if (isDirector)
                    {
                        storeTotalCommission = CalculateDirectorCommission(salary.StoreTotalPerformance, salary.StoreLifeline);
                    }
                    else
                    {
                        storeTotalCommission = CalculateAssistantCommission(salary.StoreTotalPerformance, salary.StoreLifeline);
                    }

                    // 按比例计算提成：门店总提成 / 当月天数 × 在店天数
                    salary.CommissionAmount = storeTotalCommission / daysInMonth * workingDays;

                    // 按比例计算奖励
                    salary.StageRewardAmount = storeTotalStageReward / daysInMonth * workingDays;

                    // 计算阶段奖励的明细（按比例分配）
                    if (reachedStage2)
                    {
                        // 达到第二阶段：第一阶段200 + 第二阶段200
                        salary.Stage1Reward = 200m / daysInMonth * workingDays;
                        salary.Stage2Reward = 200m / daysInMonth * workingDays;
                    }
                    else if (reachedStage1)
                    {
                        // 只达到第一阶段：第一阶段200
                        salary.Stage1Reward = 200m / daysInMonth * workingDays;
                        salary.Stage2Reward = 0m;
                    }
                    else
                    {
                        // 都没达到
                        salary.Stage1Reward = 0m;
                        salary.Stage2Reward = 0m;
                    }
                }
                else
                {
                    // 如果当月天数为0或在店天数为0，则提成和奖励为0
                    salary.CommissionAmount = 0;
                    salary.StageRewardAmount = 0;
                    salary.Stage1Reward = 0;
                    salary.Stage2Reward = 0;
                }

                // 2.12 计算应发工资
                salary.GrossSalary = salary.BaseSalary + salary.CommissionAmount + salary.StageRewardAmount + salary.PhoneManagementFee;

                // 2.13 初始化扣款、补贴、奖金字段（默认值为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.MonthlyTrainingSubsidy = 0;
                salary.MonthlyTransportSubsidy = 0;
                salary.LastMonthTrainingSubsidy = 0;
                salary.LastMonthTransportSubsidy = 0;
                salary.TotalSubsidy = 0;

                salary.Bonus = 0;
                salary.ReturnPhoneDeposit = 0;
                salary.ReturnAccommodationDeposit = 0;

                // 2.14 计算实发工资
                salary.ActualSalary = salary.GrossSalary - salary.TotalDeduction + salary.TotalSubsidy + salary.Bonus;

                // 2.15 初始化支付相关字段
                salary.PaidAmount = 0;
                salary.PendingAmount = salary.ActualSalary;
                salary.LastMonthSupplement = 0;
                salary.MonthlyTotalPayment = 0;

                assistantSalaryList.Add(salary);
            }

            // 3. 保存数据
            if (assistantSalaryList.Any())
            {
                // 3.1 先删除计算月的未锁定且未确认的工资记录
                var deletedCount = await _db.Deleteable<LqAssistantSalaryStatisticsEntity>()
                    .Where(x => x.StatisticsMonth == monthStr 
                        && x.IsLocked == 0 
                        && x.EmployeeConfirmStatus == 0)
                    .ExecuteCommandAsync();
                
                if (deletedCount > 0)
                {
                    _logger.LogInformation($"计算工资前删除了 {deletedCount} 条未锁定且未确认的记录（月份：{monthStr}）");
                }

                // 3.2 查询已存在的记录（只查询已锁定或已确认的记录）
                var existingRecords = await _db.Queryable<LqAssistantSalaryStatisticsEntity>()
                    .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<LqAssistantSalaryStatisticsEntity>();
                var recordsToUpdate = new List<LqAssistantSalaryStatisticsEntity>();
                var updatedCount = 0;
                var skippedCount = 0;

                foreach (var salary in assistantSalaryList)
                {
                    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="storePerformance">门店业绩</param>
        /// <param name="storeLifeline">门店生命线</param>
        /// <returns>提成金额</returns>
        /// <remarks>
        /// 提成规则：
        /// 1. 前提条件：门店业绩必须达到门店生命线的70%，否则无提成
        /// 2. 0-100%部分：整个0-100%部分（整个生命线）按0.4%计算
        /// 3. 100%以上部分：按0.6%计算
        /// 4. 分段计算：不同区间按不同比例分别计算后累加
        /// 
        /// 计算公式：
        /// - 如果业绩 < 70%：提成 = 0
        /// - 如果 70% ≤ 业绩 < 100%：提成 = 业绩 × 0.4%
        /// - 如果业绩 ≥ 100%：提成 = 生命线 × 0.4% + (业绩 - 生命线) × 0.6%
        /// </remarks>
        private decimal CalculateAssistantCommission(decimal storePerformance, decimal storeLifeline)
        {
            if (storeLifeline <= 0)
            {
                return 0;
            }

            decimal ratio = storePerformance / storeLifeline;

            // 前提条件：必须达到70%才有提成
            if (ratio < 0.7m)
            {
                // 门店业绩 < 门店生命线 × 70% → 0%（无提成）
                return 0;
            }
            else if (ratio < 1.0m)
            {
                // 门店生命线 × 70% ≤ 门店业绩 < 门店生命线 × 100% → 整个业绩按0.4%计算
                return storePerformance * 0.004m;
            }
            else
            {
                // 门店业绩 ≥ 门店生命线 × 100% → 分段计算
                // 0-100%部分（整个生命线）：按0.4%计算
                // 100%以上部分：按0.6%计算
                decimal commissionBelow100 = storeLifeline * 0.004m;  // 0-100%部分（整个生命线）按0.4%
                decimal commissionAbove100 = (storePerformance - storeLifeline) * 0.006m;  // 100%以上部分按0.6%

                return commissionBelow100 + commissionAbove100;
            }
        }

        /// <summary>
        /// 计算店助主任提成（分段提成模式）
        /// </summary>
        /// <param name="storePerformance">门店业绩</param>
        /// <param name="storeLifeline">门店生命线</param>
        /// <returns>提成金额</returns>
        /// <remarks>
        /// 店助主任提成规则（分段式，与店助相同，但100%以上部分比例不同）：
        /// 1. 前提条件：门店业绩必须达到门店生命线的70%，否则无提成
        /// 2. 70% ≤ 业绩 < 100%：整个业绩按0.4%计算
        /// 3. 业绩 ≥ 100%：分段式提成
        ///    - 0-100%部分（整个生命线）：按0.4%计算
        ///    - 100%以上部分：按1.6%计算（与店助的0.6%不同）
        /// 
        /// 计算公式：
        /// - 如果业绩 < 70%：提成 = 0
        /// - 如果 70% ≤ 业绩 < 100%：提成 = 业绩 × 0.4%
        /// - 如果业绩 ≥ 100%：提成 = 生命线 × 0.4% + (业绩 - 生命线) × 1.6%
        /// </remarks>
        private decimal CalculateDirectorCommission(decimal storePerformance, decimal storeLifeline)
        {
            if (storeLifeline <= 0)
            {
                return 0;
            }

            decimal ratio = storePerformance / storeLifeline;

            // 前提条件：必须达到70%才有提成
            if (ratio < 0.7m)
            {
                // 门店业绩 < 门店生命线 × 70% → 0%（无提成）
                return 0;
            }
            else if (ratio < 1.0m)
            {
                // 门店生命线 × 70% ≤ 门店业绩 < 门店生命线 × 100% → 整个业绩按0.4%计算
                return storePerformance * 0.004m;
            }
            else
            {
                // 门店业绩 ≥ 门店生命线 × 100% → 分段式提成
                // 0-100%部分（整个生命线）：按0.4%计算
                // 100%以上部分：按1.6%计算（店助主任与店助的区别）
                decimal commissionBelow100 = storeLifeline * 0.004m;  // 0-100%部分（整个生命线）按0.4%
                decimal commissionAbove100 = (storePerformance - storeLifeline) * 0.016m;  // 100%以上部分按1.6%

                return commissionBelow100 + commissionAbove100;
            }
        }

        /// <summary>
        /// 计算底薪（店助）
        /// </summary>
        /// <param name="storeCategory">门店分类（1=A类，2=B类，3=C类）</param>
        /// <returns>底薪金额</returns>
        private decimal CalculateBaseSalary(int storeCategory)
        {
            return storeCategory switch
            {
                1 => 3000m, // A类门店
                2 => 3100m, // B类门店
                3 => 3200m, // C类门店
                _ => throw new Exception($"门店分类值无效：{storeCategory}，有效值为1（A类）、2（B类）、3（C类）")
            };
        }

        #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<LqAssistantSalaryStatisticsEntity>()
                    .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;
                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<LqAssistantSalaryStatisticsEntity>()
                    .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>
        /// <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<LqAssistantSalaryStatisticsEntity>()
                    .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>
        /// <param name="file">Excel文件</param>
        /// <returns>导入结果</returns>
        [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<LqAssistantSalaryStatisticsEntity>();
                var recordsToUpdate = new List<LqAssistantSalaryStatisticsEntity>();
                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);
                    }

                    var dataTable = ExcelImportHelper.ToDataTable(tempFilePath, 0, 0);
                    if (dataTable.Rows.Count == 0)
                        throw NCCException.Oh("Excel文件中没有数据行");

                    Func<string, decimal> ParseDecimal = (str) =>
                    {
                        if (string.IsNullOrWhiteSpace(str)) return 0;
                        var cleaned = str.Trim().Replace(",", "").Replace("，", "").Replace("￥", "").Replace("$", "").Replace("元", "").Replace("%", "").Replace(" ", "");
                        return decimal.TryParse(cleaned, out decimal result) ? result : 0;
                    };

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

                    // 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) => colIndex < row.ItemArray.Length && row[colIndex] != null ? row[colIndex].ToString().Trim() : "";

                            var firstColumnValue = GetColumnValue(0);
                            bool isOldFormat = !string.IsNullOrWhiteSpace(firstColumnValue) && (firstColumnValue == "门店名称" || (!long.TryParse(firstColumnValue, out _) && firstColumnValue.Length > 20));

                            int storeNameIndex = isOldFormat ? 0 : 1;
                            int employeeNameIndex = isOldFormat ? 1 : 2;
                            int offset = isOldFormat ? 0 : 1;

                            var id = isOldFormat ? "" : GetColumnValue(0);
                            var employeeName = GetColumnValue(employeeNameIndex);
                            var storeName = GetColumnValue(storeNameIndex);

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

                            if (string.IsNullOrWhiteSpace(id) && !string.IsNullOrWhiteSpace(employeeName))
                            {
                                var matchedRecord = await _db.Queryable<LqAssistantSalaryStatisticsEntity>()
                                    .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;
                            }

                            LqAssistantSalaryStatisticsEntity existing = null;
                            if (!string.IsNullOrWhiteSpace(id))
                            {
                                existing = await _db.Queryable<LqAssistantSalaryStatisticsEntity>()
                                    .Where(x => x.Id == id).FirstAsync();

                                if (existing != null && (existing.IsLocked == 1 || existing.EmployeeConfirmStatus == 1))
                                {
                                    skippedCount++;
                                    failCount++;
                                    continue;
                                }
                            }

                            var entity = existing ?? new LqAssistantSalaryStatisticsEntity
                            {
                                Id = string.IsNullOrWhiteSpace(id) ? YitIdHelper.NextId().ToString() : id,
                                EmployeeConfirmStatus = 0,
                                IsLocked = 0,
                                CreateTime = DateTime.Now,
                                CreateUser = ""
                            };

                            entity.StoreName = storeName;
                            entity.EmployeeName = employeeName;
                            entity.Position = GetColumnValue(2 + offset);
                            entity.StoreTotalPerformance = ParseDecimal(GetColumnValue(3 + offset));
                            entity.StoreBillingPerformance = ParseDecimal(GetColumnValue(4 + offset));
                            entity.StoreRefundPerformance = ParseDecimal(GetColumnValue(5 + offset));
                            entity.StoreLifeline = ParseDecimal(GetColumnValue(6 + offset));
                            entity.PerformanceCompletionRate = ParseDecimal(GetColumnValue(7 + offset));
                            entity.CommissionRate = ParseDecimal(GetColumnValue(8 + offset));
                            entity.CommissionAmount = ParseDecimal(GetColumnValue(9 + offset));
                            entity.HeadCount = ParseInt(GetColumnValue(10 + offset));
                            entity.Stage1TargetHeadCount = ParseInt(GetColumnValue(11 + offset));
                            entity.Stage2TargetHeadCount = ParseInt(GetColumnValue(12 + offset));
                            entity.ReachedStage1 = GetColumnValue(13 + offset);
                            entity.ReachedStage2 = GetColumnValue(14 + offset);
                            entity.StageRewardAmount = ParseDecimal(GetColumnValue(15 + offset));
                            entity.Stage1Reward = ParseDecimal(GetColumnValue(16 + offset));
                            entity.Stage2Reward = ParseDecimal(GetColumnValue(17 + offset));
                            entity.BaseSalary = ParseDecimal(GetColumnValue(18 + offset));
                            entity.PhoneManagementFee = ParseDecimal(GetColumnValue(19 + offset));
                            entity.WorkingDays = ParseInt(GetColumnValue(20 + offset));
                            entity.LeaveDays = ParseInt(GetColumnValue(21 + offset));
                            entity.GrossSalary = ParseDecimal(GetColumnValue(22 + offset));
                            entity.ActualSalary = ParseDecimal(GetColumnValue(23 + offset));
                            entity.TotalDeduction = ParseDecimal(GetColumnValue(24 + offset));
                            entity.TotalSubsidy = ParseDecimal(GetColumnValue(25 + offset));
                            entity.Bonus = ParseDecimal(GetColumnValue(26 + offset));
                            entity.ReturnPhoneDeposit = ParseDecimal(GetColumnValue(27 + offset));
                            entity.ReturnAccommodationDeposit = ParseDecimal(GetColumnValue(28 + offset));
                            entity.LastMonthSupplement = ParseDecimal(GetColumnValue(29 + offset));
                            entity.MonthlyPaymentStatus = GetColumnValue(30 + offset);
                            entity.PaidAmount = ParseDecimal(GetColumnValue(31 + offset));
                            entity.PendingAmount = ParseDecimal(GetColumnValue(32 + offset));
                            entity.MonthlyTotalPayment = ParseDecimal(GetColumnValue(33 + offset));
                            entity.IsNewStore = GetColumnValue(34 + offset) == "是" ? "是" : "否";
                            entity.NewStoreProtectionStage = ParseInt(GetColumnValue(35 + offset));

                            if (existing != null)
                            {
                                entity.StoreId = existing.StoreId;
                                entity.EmployeeId = existing.EmployeeId;
                                entity.StatisticsMonth = existing.StatisticsMonth;
                            }
                            else
                            {
                                // 对于新记录，尝试通过员工姓名和门店名称匹配已有记录以获取统计月份
                                LqAssistantSalaryStatisticsEntity matchedRecord = null;
                                if (!string.IsNullOrWhiteSpace(employeeName))
                                {
                                    var user = await _db.Queryable<UserEntity>()
                                        .Where(u => u.RealName == employeeName).FirstAsync();
                                    if (user != null)
                                    {
                                        entity.EmployeeId = user.Id;
                                        if (!string.IsNullOrEmpty(user.Mdid) && string.IsNullOrWhiteSpace(storeName))
                                            entity.StoreId = user.Mdid;

                                        // 尝试通过员工ID匹配已有记录获取统计月份
                                        matchedRecord = await _db.Queryable<LqAssistantSalaryStatisticsEntity>()
                                            .Where(x => x.EmployeeId == user.Id)
                                            .WhereIF(!string.IsNullOrWhiteSpace(storeName), x => x.StoreName == storeName)
                                            .OrderBy(x => x.CreateTime, OrderByType.Desc)
                                            .FirstAsync();
                                    }

                                    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;
                                            // 如果没有匹配到记录，再通过门店ID尝试
                                            if (matchedRecord == null)
                                            {
                                                matchedRecord = await _db.Queryable<LqAssistantSalaryStatisticsEntity>()
                                                    .Where(x => x.StoreId == store.Id)
                                                    .WhereIF(!string.IsNullOrWhiteSpace(employeeName), x => x.EmployeeName == employeeName)
                                                    .OrderBy(x => x.CreateTime, OrderByType.Desc)
                                                    .FirstAsync();
                                            }
                                        }
                                    }
                                }

                                // 如果有匹配的记录，使用其统计月份；否则使用当前年月作为默认值
                                if (matchedRecord != null && !string.IsNullOrWhiteSpace(matchedRecord.StatisticsMonth))
                                {
                                    entity.StatisticsMonth = matchedRecord.StatisticsMonth;
                                }
                                else
                                {
                                    // 如果没有匹配记录，使用当前年月（YYYYMM格式）
                                    var now = DateTime.Now;
                                    entity.StatisticsMonth = $"{now.Year}{now.Month:D2}";
                                }
                            }

                            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,
                    failCount,
                    skippedCount,
                    errors = errorMessages
                };
            }
            catch (Exception ex)
            {
                throw NCCException.Oh($"导入店助工资数据失败: {ex.Message}");
            }
        }

        #endregion
    }
}