using Microsoft.AspNetCore.Authorization;
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.LqTechGeneralManagerSalary;
using NCC.Extend.Entitys.lq_attendance_summary;
using NCC.Extend.Entitys.lq_hytk_hytk;
using NCC.Extend.Entitys.lq_hytk_jksyj;
using NCC.Extend.Entitys.lq_hytk_mx;
using NCC.Extend.Entitys.lq_kd_jksyj;
using NCC.Extend.Entitys.lq_kd_kdjlb;
using NCC.Extend.Entitys.lq_kd_pxmx;
using NCC.Extend.Entitys.lq_md_general_manager_lifeline;
using NCC.Extend.Entitys.lq_md_target;
using NCC.Extend.Entitys.lq_mdxx;
using NCC.Extend.Entitys.lq_tech_general_manager_salary_statistics;
using NCC.Extend.Entitys.lq_xmzl;
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;
using Newtonsoft.Json;
using Microsoft.AspNetCore.Http;

namespace NCC.Extend
{
    /// <summary>
    /// 科技部总经理薪酬服务
    /// </summary>
    [ApiDescriptionSettings(Tag = "科技部总经理薪酬服务", Name = "LqTechGeneralManagerSalary", Order = 305)]
    [Route("api/Extend/[controller]")]
    public class LqTechGeneralManagerSalaryService : IDynamicApiController, ITransient
    {
        private readonly ISqlSugarClient _db;
        private readonly ILogger<LqTechGeneralManagerSalaryService> _logger;

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

        /// <summary>
        /// 获取科技部总经理工资列表
        /// </summary>
        /// <param name="input">查询参数</param>
        /// <returns>科技部总经理工资分页列表</returns>
        [HttpGet("tech-general-manager")]
        public async Task<dynamic> GetTechGeneralManagerSalaryList([FromQuery] TechGeneralManagerSalaryInput input)
        {
            var monthStr = $"{input.Year}{input.Month:D2}";

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

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

            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 TechGeneralManagerSalaryOutput
            {
                Id = x.Id,
                StatisticsMonth = x.StatisticsMonth,
                Position = x.Position,
                EmployeeName = x.EmployeeName,
                EmployeeId = x.EmployeeId,
                EmployeeAccount = x.EmployeeAccount,
                IsTerminated = x.IsTerminated,
                StoreDetail = x.StoreDetail,
                TraceabilityAmount = x.TraceabilityAmount,
                CellAmount = x.CellAmount,
                BaseSalary = x.BaseSalary,
                TraceabilityCommissionRate = x.TraceabilityCommissionRate,
                TraceabilityCommissionAmount = x.TraceabilityCommissionAmount,
                CellCommissionRate = x.CellCommissionRate,
                CellCommissionAmount = x.CellCommissionAmount,
                TotalCommission = x.TotalCommission,
                WorkingDays = x.WorkingDays,
                LeaveDays = x.LeaveDays,
                CalculatedGrossSalary = x.CalculatedGrossSalary,
                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,
                UpdateTime = x.UpdateTime
            })
            .ToPagedListAsync(input.currentPage, input.pageSize);

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

        /// <summary>
        /// 通过月份和员工ID查询工资（仅查询已锁定且未确认的工资）
        /// </summary>
        /// <remarks>
        /// **重要**：此接口只能查询已锁定（IsLocked=1）且未确认（EmployeeConfirmStatus=0或null）的工资记录
        /// **注意**：已确认的工资记录无法通过此接口查询
        /// </remarks>
        [HttpGet("query-by-employee")]
        public async Task<TechGeneralManagerSalaryOutput> 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<LqTechGeneralManagerSalaryStatisticsEntity>()
                .Where(x => x.StatisticsMonth == monthStr && x.EmployeeId == input.EmployeeId && x.IsLocked == 1 && x.EmployeeConfirmStatus != 1)
                .Select(x => new TechGeneralManagerSalaryOutput
                {
                    Id = x.Id,
                    StatisticsMonth = x.StatisticsMonth,
                    Position = x.Position,
                    EmployeeName = x.EmployeeName,
                    EmployeeId = x.EmployeeId,
                    EmployeeAccount = x.EmployeeAccount,
                    IsTerminated = x.IsTerminated,
                    StoreDetail = x.StoreDetail,
                    TraceabilityAmount = x.TraceabilityAmount,
                    CellAmount = x.CellAmount,
                    BaseSalary = x.BaseSalary,
                    TraceabilityCommissionRate = x.TraceabilityCommissionRate,
                    TraceabilityCommissionAmount = x.TraceabilityCommissionAmount,
                    CellCommissionRate = x.CellCommissionRate,
                    CellCommissionAmount = x.CellCommissionAmount,
                    TotalCommission = x.TotalCommission,
                    WorkingDays = x.WorkingDays,
                    LeaveDays = x.LeaveDays,
                    CalculatedGrossSalary = x.CalculatedGrossSalary,
                    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,
                    UpdateTime = x.UpdateTime
                })
                .FirstAsync();
            if (salary == null)
                throw NCCException.Oh($"未找到员工{input.EmployeeId}在{input.Year}年{input.Month}月的工资记录");
            return salary;
        }

        /// <summary>
        /// 计算科技部总经理工资
        /// </summary>
        /// <remarks>
        /// 计算科技部总经理的工资，包括底薪、溯源金额提成、Cell金额提成等。
        /// 
        /// 计算规则：
        /// - 底薪：固定4000元
        /// - 溯源金额提成：根据管理的所有门店的溯源金额总和分段累进计算
        /// - Cell金额提成：根据管理的所有门店的Cell金额总和分段累进计算
        /// 
        /// 数据统计说明：
        /// - 时间范围：严格按照当月范围（startDate 到 endDate 23:59:59），不包含下个月的数据
        /// - 数据查询：使用原生SQL在数据库层面转换和求和，确保与科技部驾驶舱接口的计算方式一致
        /// - 门店范围：通过门店的kjb字段确定科技部总经理管理的门店
        /// 
        /// 溯源金额和Cell金额的计算方式：
        /// - 开单金额：从 lq_kd_jksyj 表统计（使用原生SQL CAST转换）
        /// - 退卡金额：从 lq_hytk_jksyj 表统计（使用SqlSugar聚合查询）
        /// - 净金额 = 开单金额 - 退卡金额
        /// </remarks>
        /// <param name="year">年份</param>
        /// <param name="month">月份</param>
        /// <returns></returns>
        [HttpPost("calculate/tech-general-manager")]
        public async Task CalculateTechGeneralManagerSalary(int year, int month)
        {
            var startDate = new DateTime(year, month, 1);
            var endDate = startDate.AddMonths(1).AddDays(-1);
            var monthStr = $"{year}{month:D2}";
            // 使用与科技部驾驶舱接口相同的时间范围处理方式
            var endDateTime = monthStr == DateTime.Now.ToString("yyyyMM")
                ? DateTime.Now
                : endDate.Date.AddHours(23).AddMinutes(59).AddSeconds(59);

            _logger.LogInformation($"[科技部总经理工资计算] 开始计算，月份: {monthStr}, 时间范围: {startDate.ToString("yyyy-MM-dd HH:mm:ss")} 到 {endDateTime.ToString("yyyy-MM-dd HH:mm:ss")}");

            // 1. 获取基础数据

            // 1.1 先从BASE_ORGANIZE表查找组织名称包含"科技一部"或"科技二部"的组织
            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();

            if (!techOrganizeList.Any())
            {
                // 如果没有找到科技部组织，直接返回
                return;
            }

            var techOrganizeIds = techOrganizeList.Select(x => x.Id).ToList();
            var techOrganizeDict = techOrganizeList.ToDictionary(x => x.Id, x => x.FullName);

            // 1.2 从BASE_USER表查询岗位为"总经理"或"科技部总经理"且组织ID在科技一部或科技二部的员工
            var techGeneralManagerUserList = await _db.Queryable<UserEntity>()
                .Where(x => (x.Gw == "总经理" || x.Gw == "科技部总经理")
                    && techOrganizeIds.Contains(x.OrganizeId)
                    && x.DeleteMark == null && x.EnabledMark == 1)
                .Select(x => new { x.Id, x.RealName, x.Account, x.Gw, x.OrganizeId, x.IsOnJob })
                .ToListAsync();

            if (!techGeneralManagerUserList.Any())
            {
                // 如果没有科技部总经理员工，记录日志并返回
                _logger.LogWarning($"[科技部总经理工资计算] 未找到科技部总经理员工，科技部组织ID: {string.Join(",", techOrganizeIds)}");
                return;
            }

            // 1.3 获取科技部总经理归属信息（从lq_md_general_manager_lifeline表）
            // lq_mdxx.kjb 已废弃，按统计月份从 lq_md_target 取科技一部/二部管理的门店，再关联 lifeline
            var techManagedStoreIdsForMonth = await _db.Queryable<LqMdTargetEntity>()
                .Where(x => x.Month == monthStr && techOrganizeIds.Contains(x.TechDepartment))
                .Select(x => x.StoreId)
                .Distinct()
                .ToListAsync();
            var lifelineList = await _db.Queryable<LqMdGeneralManagerLifelineEntity>()
                .Where(x => x.Month == monthStr && techManagedStoreIdsForMonth.Contains(x.StoreId))
                .ToListAsync();

            // 1.4 获取科技一部和科技二部当月管理的门店（从 lq_md_target）
            var techManagedStoreIds = techManagedStoreIdsForMonth;

            // 1.5 按科技部总经理ID分组，获取每个科技部总经理当月管理的门店（从 lq_md_target）
            var managerStoreDict = new Dictionary<string, List<string>>();
            foreach (var managerUser in techGeneralManagerUserList)
            {
                var managerId = managerUser.Id;
                var managerOrganizeId = managerUser.OrganizeId;

                var managedStores = await _db.Queryable<LqMdTargetEntity>()
                    .Where(x => x.Month == monthStr && x.TechDepartment == managerOrganizeId)
                    .Select(x => x.StoreId)
                    .Distinct()
                    .ToListAsync();

                managerStoreDict[managerId] = managedStores;
            }

            // 1.4 门店信息 (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.6 考勤数据 (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);

            // 1.7 获取所有管理的门店ID列表（用于后续查询，如果没有管理的门店，则为空列表）
            var allManagedStoreIds = managerStoreDict.Values.SelectMany(x => x).Distinct().ToList();

            // 1.8 按科技部总经理和门店分组统计（用于生成门店明细JSON和汇总数据）
            var storeDetailDict = new Dictionary<string, Dictionary<string, StoreDetailItem>>();

            // 按门店统计溯源和Cell金额（如果有管理的门店）
            // 使用与科技部驾驶舱接口完全相同的查询方式：批量查询所有门店，然后按门店分组
            if (allManagedStoreIds.Any())
            {
                // 时间格式化字符串（使用与科技部驾驶舱接口相同的格式化方式）
                var startDateStr = startDate.ToString("yyyy-MM-dd HH:mm:ss");
                var endDateTimeStr = endDateTime.ToString("yyyy-MM-dd HH:mm:ss");

                // 批量查询所有门店的开单溯源金额（与科技部驾驶舱接口保持一致）
                var allStoreTraceabilityBillingSql = $@"
                    SELECT F_StoreId, COALESCE(SUM(CAST(jksyj AS DECIMAL(18,2))), 0) as Amount
                    FROM lq_kd_jksyj
                    WHERE F_IsEffective = 1
                        AND F_StoreId IN ('{string.Join("','", allManagedStoreIds)}')
                        AND (F_BeautyType = '溯源系统' OR F_BeautyType = '溯源')
                        AND yjsj >= '{startDateStr}'
                        AND yjsj <= '{endDateTimeStr}'
                    GROUP BY F_StoreId";
                var allStoreTraceabilityBillingResult = await _db.Ado.SqlQueryAsync<dynamic>(allStoreTraceabilityBillingSql);
                var storeTraceabilityBillingDict = new Dictionary<string, decimal>();
                if (allStoreTraceabilityBillingResult != null)
                {
                    foreach (var item in allStoreTraceabilityBillingResult)
                    {
                        var storeId = item.F_StoreId?.ToString() ?? "";
                        var amount = Convert.ToDecimal(item.Amount ?? 0);
                        storeTraceabilityBillingDict[storeId] = amount;
                    }
                }

                // 批量查询所有门店的开单Cell金额（与科技部驾驶舱接口保持一致）
                var allStoreCellBillingSql = $@"
                    SELECT F_StoreId, COALESCE(SUM(CAST(jksyj AS DECIMAL(18,2))), 0) as Amount
                    FROM lq_kd_jksyj
                    WHERE F_IsEffective = 1
                        AND F_StoreId IN ('{string.Join("','", allManagedStoreIds)}')
                        AND (F_BeautyType = 'cell' OR F_BeautyType = 'Cell')
                        AND yjsj >= '{startDateStr}'
                        AND yjsj <= '{endDateTimeStr}'
                    GROUP BY F_StoreId";
                var allStoreCellBillingResult = await _db.Ado.SqlQueryAsync<dynamic>(allStoreCellBillingSql);
                var storeCellBillingDict = new Dictionary<string, decimal>();
                if (allStoreCellBillingResult != null)
                {
                    foreach (var item in allStoreCellBillingResult)
                    {
                        var storeId = item.F_StoreId?.ToString() ?? "";
                        var amount = Convert.ToDecimal(item.Amount ?? 0);
                        storeCellBillingDict[storeId] = amount;
                    }
                }

                // 批量查询所有门店的退卡溯源金额
                var allStoreTraceabilityRefundList = await _db.Queryable<LqHytkJksyjEntity>()
                    .Where(x => x.IsEffective == 1)
                    .Where(x => allManagedStoreIds.Contains(x.StoreId))
                    .Where(x => (x.BeautyType == "溯源系统" || x.BeautyType == "溯源"))
                    .Where(x => x.Tksj.HasValue && x.Tksj.Value.Date >= startDate.Date && x.Tksj.Value.Date <= endDate.Date)
                    .GroupBy(x => x.StoreId)
                    .Select(x => new { StoreId = x.StoreId, Amount = SqlFunc.AggregateSum(x.Jksyj) })
                    .ToListAsync();
                var storeTraceabilityRefundDict = new Dictionary<string, decimal>();
                if (allStoreTraceabilityRefundList != null)
                {
                    foreach (var item in allStoreTraceabilityRefundList)
                    {
                        var storeId = item.StoreId ?? "";
                        var amount = Convert.ToDecimal(item.Amount ?? 0);
                        storeTraceabilityRefundDict[storeId] = amount;
                    }
                }

                // 批量查询所有门店的退卡Cell金额
                var allStoreCellRefundList = await _db.Queryable<LqHytkJksyjEntity>()
                    .Where(x => x.IsEffective == 1)
                    .Where(x => allManagedStoreIds.Contains(x.StoreId))
                    .Where(x => (x.BeautyType == "cell" || x.BeautyType == "Cell"))
                    .Where(x => x.Tksj.HasValue && x.Tksj.Value.Date >= startDate.Date && x.Tksj.Value.Date <= endDate.Date)
                    .GroupBy(x => x.StoreId)
                    .Select(x => new { StoreId = x.StoreId, Amount = SqlFunc.AggregateSum(x.Jksyj) })
                    .ToListAsync();
                var storeCellRefundDict = new Dictionary<string, decimal>();
                if (allStoreCellRefundList != null)
                {
                    foreach (var item in allStoreCellRefundList)
                    {
                        var storeId = item.StoreId ?? "";
                        var amount = Convert.ToDecimal(item.Amount ?? 0);
                        storeCellRefundDict[storeId] = amount;
                    }
                }

                // 批量查询当月门店科技部归属（lq_md_target），避免循环内查询
                var storeTechDeptList = await _db.Queryable<LqMdTargetEntity>()
                    .Where(x => x.Month == monthStr && allManagedStoreIds.Contains(x.StoreId))
                    .Select(x => new { x.StoreId, x.TechDepartment })
                    .ToListAsync();
                var storeTechDeptDict = storeTechDeptList
                    .GroupBy(x => x.StoreId)
                    .ToDictionary(g => g.Key, g => g.First().TechDepartment);

                // 遍历所有门店，构建门店明细
                foreach (var storeId in allManagedStoreIds)
                {
                    var storeTraceabilityBilling = storeTraceabilityBillingDict.ContainsKey(storeId) ? storeTraceabilityBillingDict[storeId] : 0m;
                    var storeTraceabilityRefund = storeTraceabilityRefundDict.ContainsKey(storeId) ? storeTraceabilityRefundDict[storeId] : 0m;
                    var storeCellBilling = storeCellBillingDict.ContainsKey(storeId) ? storeCellBillingDict[storeId] : 0m;
                    var storeCellRefund = storeCellRefundDict.ContainsKey(storeId) ? storeCellRefundDict[storeId] : 0m;

                    // 调试日志：记录关键门店的计算结果
                    if (storeId == "1649328471923847173" || storeId == "1649328471923847175" || storeId == "1649328471923847187")
                    {
                        _logger.LogInformation($"[科技部总经理工资计算] 门店ID: {storeId}, 开单Cell金额: {storeCellBilling}, 退卡Cell金额: {storeCellRefund}, Cell金额: {storeCellBilling - storeCellRefund}");
                    }

                    // 获取该门店属于哪些科技部总经理（按当月 lq_md_target 的科技部归属）
                    var store = storeDict.ContainsKey(storeId) ? storeDict[storeId] : null;
                    var managersOfStore = new List<string>();
                    var storeTechDept = storeTechDeptDict.ContainsKey(storeId) ? storeTechDeptDict[storeId] : null;
                    if (!string.IsNullOrEmpty(storeTechDept))
                    {
                        var managers = techGeneralManagerUserList
                            .Where(x => x.OrganizeId == storeTechDept)
                            .Select(x => x.Id)
                            .ToList();
                        managersOfStore.AddRange(managers);
                    }

                    foreach (var managerId in managersOfStore)
                    {
                        if (!storeDetailDict.ContainsKey(managerId))
                        {
                            storeDetailDict[managerId] = new Dictionary<string, StoreDetailItem>();
                        }

                        var storeName = storeDict.ContainsKey(storeId) ? storeDict[storeId].Dm ?? "" : "";
                        storeDetailDict[managerId][storeId] = new StoreDetailItem
                        {
                            StoreId = storeId,
                            StoreName = storeName,
                            TraceabilityBillingAmount = storeTraceabilityBilling,
                            TraceabilityRefundAmount = storeTraceabilityRefund,
                            TraceabilityAmount = storeTraceabilityBilling - storeTraceabilityRefund,
                            CellBillingAmount = storeCellBilling,
                            CellRefundAmount = storeCellRefund,
                            CellAmount = storeCellBilling - storeCellRefund
                        };
                    }
                }
            }

            // 2. 按科技部总经理聚合数据
            var managerStats = new Dictionary<string, LqTechGeneralManagerSalaryStatisticsEntity>();

            foreach (var managerUser in techGeneralManagerUserList)
            {
                var managerId = managerUser.Id;

                // 获取该科技部总经理管理的门店列表
                var managedStores = managerStoreDict.ContainsKey(managerId) ? managerStoreDict[managerId] : new List<string>();

                // 2.1 创建工资统计对象
                // 岗位使用组织名称（科技一部/科技二部）
                var position = techOrganizeDict.ContainsKey(managerUser.OrganizeId)
                    ? techOrganizeDict[managerUser.OrganizeId]
                    : "";

                var salary = new LqTechGeneralManagerSalaryStatisticsEntity
                {
                    Id = YitIdHelper.NextId().ToString(),
                    StatisticsMonth = monthStr,
                    EmployeeId = managerId,
                    Position = position,
                    EmployeeName = managerUser.RealName ?? "",
                    EmployeeAccount = managerUser.Account ?? "",
                    IsTerminated = managerUser.IsOnJob == 0 ? 1 : 0,
                    CreateTime = DateTime.Now,
                    UpdateTime = DateTime.Now,
                    IsLocked = 0
                };

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

                // 2.3 计算底薪（固定4000元）
                salary.BaseSalary = 4000m;

                // 2.4 统计该科技部总经理管理的所有门店的溯源金额和Cell金额总和
                decimal totalTraceabilityAmount = 0m;
                decimal totalCellAmount = 0m;
                var storeDetails = new List<StoreDetailItem>();

                if (managedStores.Any() && storeDetailDict.ContainsKey(managerId))
                {
                    foreach (var storeId in managedStores)
                    {
                        if (storeDetailDict[managerId].ContainsKey(storeId))
                        {
                            var storeDetail = storeDetailDict[managerId][storeId];
                            totalTraceabilityAmount += storeDetail.TraceabilityAmount;
                            totalCellAmount += storeDetail.CellAmount;
                            storeDetails.Add(storeDetail);
                        }
                    }
                }

                salary.TraceabilityAmount = totalTraceabilityAmount;
                salary.CellAmount = totalCellAmount;

                _logger.LogInformation($"[科技部总经理工资计算] 员工: {salary.EmployeeName}, 溯源金额: {totalTraceabilityAmount}, Cell金额: {totalCellAmount}, 门店数: {storeDetails.Count}");

                // 2.5 保存门店明细（JSON格式）
                salary.StoreDetail = JsonConvert.SerializeObject(storeDetails);

                // 2.6 计算溯源金额提成（分段累进）
                var traceabilityCommission = CalculateTraceabilityCommission(totalTraceabilityAmount);
                salary.TraceabilityCommissionAmount = traceabilityCommission.Amount;
                salary.TraceabilityCommissionRate = traceabilityCommission.Rate;

                // 2.7 计算Cell金额提成（分段累进）
                var cellCommission = CalculateCellCommission(totalCellAmount);
                salary.CellCommissionAmount = cellCommission.Amount;
                salary.CellCommissionRate = cellCommission.Rate;

                // 2.8 提成合计
                salary.TotalCommission = salary.TraceabilityCommissionAmount + salary.CellCommissionAmount;

                // 2.9 计算应发工资
                salary.CalculatedGrossSalary = salary.BaseSalary + salary.TotalCommission;
                salary.FinalGrossSalary = salary.CalculatedGrossSalary;

                // 2.10 初始化其他字段（默认值为0）
                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;

                managerStats[managerId] = salary;
            }

            _logger.LogInformation($"[科技部总经理工资计算] 共计算了 {managerStats.Count} 个科技部总经理的工资数据");

            // 3. 保存数据
            if (managerStats.Any())
            {
                // 3.1 先删除计算月的未锁定且未确认的工资记录
                var deletedCount = await _db.Deleteable<LqTechGeneralManagerSalaryStatisticsEntity>()
                    .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<LqTechGeneralManagerSalaryStatisticsEntity>()
                    .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<LqTechGeneralManagerSalaryStatisticsEntity>();
                var recordsToUpdate = new List<LqTechGeneralManagerSalaryStatisticsEntity>();
                var updatedCount = 0;
                
                var skippedCount = 0;
                foreach (var salary in managerStats.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;
                        salary.UpdateTime = DateTime.Now; // 强制更新UpdateTime
                        _logger.LogInformation($"[科技部总经理工资计算] 准备更新，员工: {salary.EmployeeName}, 旧Cell金额: {existing.CellAmount}, 新Cell金额: {salary.CellAmount}");
                        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())
                {
                    // 使用IgnoreColumns排除CreateTime和CreateUser，确保其他所有字段都被更新
                    await _db.Updateable(recordsToUpdate)
                        .IgnoreColumns(x => x.CreateTime)
                        .IgnoreColumns(x => x.CreateUser)
                        .ExecuteCommandAsync();
                    _logger.LogInformation($"更新了 {recordsToUpdate.Count} 条未锁定且未确认的工资记录（月份：{monthStr}）");
                }

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

        /// <summary>
        /// 计算溯源金额提成（分段累进）
        /// </summary>
        /// <param name="traceabilityAmount">溯源金额</param>
        /// <returns>提成金额和平均比例</returns>
        private (decimal Amount, decimal? Rate) CalculateTraceabilityCommission(decimal traceabilityAmount)
        {
            if (traceabilityAmount <= 0)
            {
                return (0m, null);
            }

            decimal commissionAmount = 0m;
            decimal? averageRate = null;

            if (traceabilityAmount < 200000m)
            {
                // < 200,000元：1%
                commissionAmount = traceabilityAmount * 0.01m;
                averageRate = 1.00m;
            }
            else if (traceabilityAmount < 300000m)
            {
                // 200,000-300,000元：1.5%
                commissionAmount = 200000m * 0.01m + (traceabilityAmount - 200000m) * 0.015m;
                averageRate = (commissionAmount / traceabilityAmount) * 100m;
            }
            else if (traceabilityAmount < 500000m)
            {
                // 300,000-500,000元：2%
                commissionAmount = 200000m * 0.01m + 100000m * 0.015m + (traceabilityAmount - 300000m) * 0.02m;
                averageRate = (commissionAmount / traceabilityAmount) * 100m;
            }
            else
            {
                // ≥ 500,000元：2.5%
                commissionAmount = 200000m * 0.01m + 100000m * 0.015m + 200000m * 0.02m + (traceabilityAmount - 500000m) * 0.025m;
                averageRate = (commissionAmount / traceabilityAmount) * 100m;
            }

            return (commissionAmount, averageRate);
        }

        /// <summary>
        /// 计算Cell金额提成（分段累进）
        /// </summary>
        /// <param name="cellAmount">Cell金额</param>
        /// <returns>提成金额和平均比例</returns>
        private (decimal Amount, decimal? Rate) CalculateCellCommission(decimal cellAmount)
        {
            if (cellAmount <= 0)
            {
                return (0m, null);
            }

            if (cellAmount < 50000m)
            {
                // < 50,000元：无提成
                return (0m, null);
            }

            decimal commissionAmount = 0m;
            decimal? averageRate = null;

            if (cellAmount < 400000m)
            {
                // 50,000-400,000元：1%
                commissionAmount = (cellAmount - 50000m) * 0.01m;
                averageRate = (commissionAmount / cellAmount) * 100m;
            }
            else
            {
                // ≥ 400,000元：1.5%
                commissionAmount = 350000m * 0.01m + (cellAmount - 400000m) * 0.015m;
                averageRate = (commissionAmount / cellAmount) * 100m;
            }

            return (commissionAmount, averageRate);
        }

        /// <summary>
        /// 门店明细项（用于JSON序列化）
        /// </summary>
        private class StoreDetailItem
        {
            public string StoreId { get; set; }
            public string StoreName { get; set; }
            public decimal TraceabilityBillingAmount { get; set; }
            public decimal TraceabilityRefundAmount { get; set; }
            public decimal TraceabilityAmount { get; set; }
            public decimal CellBillingAmount { get; set; }
            public decimal CellRefundAmount { get; set; }
            public decimal CellAmount { get; set; }
        }

        #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) || string.IsNullOrWhiteSpace(input.EmployeeId))
                    throw NCCException.Oh("工资记录ID和员工ID不能为空");
                var salary = await _db.Queryable<LqTechGeneralManagerSalaryStatisticsEntity>()
                    .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<LqTechGeneralManagerSalaryStatisticsEntity>()
                    .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<LqTechGeneralManagerSalaryStatisticsEntity>()
                    .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<LqTechGeneralManagerSalaryStatisticsEntity>();
                var recordsToUpdate = new List<LqTechGeneralManagerSalaryStatisticsEntity>();
                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 employeeNameIndex = isOldFormat ? 0 : 1;
                            int offset = isOldFormat ? 0 : 1;

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

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

                            if (string.IsNullOrWhiteSpace(id) && !string.IsNullOrWhiteSpace(employeeName))
                            {
                                var matchedRecord = await _db.Queryable<LqTechGeneralManagerSalaryStatisticsEntity>()
                                    .Where(x => x.EmployeeName == employeeName)
                                    .OrderBy(x => x.CreateTime, OrderByType.Desc)
                                    .FirstAsync();
                                if (matchedRecord != null) id = matchedRecord.Id;
                            }

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

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

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

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

                            // Excel字段映射（科技部总经理工资41列：员工姓名,员工账号,核算岗位,统计月份,是否离职,溯源金额,Cell金额,底薪,溯源金额提成比例,溯源金额提成金额,Cell金额提成比例,Cell金额提成金额,提成合计...）
                            entity.EmployeeName = employeeName;
                            entity.EmployeeAccount = GetColumnValue(1 + offset);
                            entity.Position = GetColumnValue(2 + offset);
                            entity.StatisticsMonth = GetColumnValue(3 + offset);
                            entity.IsTerminated = GetColumnValue(4 + offset) == "离职" || GetColumnValue(4 + offset) == "1" ? 1 : 0;
                            entity.TraceabilityAmount = ParseDecimal(GetColumnValue(5 + offset));
                            entity.CellAmount = ParseDecimal(GetColumnValue(6 + offset));
                            entity.BaseSalary = ParseDecimal(GetColumnValue(7 + offset));
                            entity.TraceabilityCommissionRate = ParseDecimal(GetColumnValue(8 + offset));
                            entity.TraceabilityCommissionAmount = ParseDecimal(GetColumnValue(9 + offset));
                            entity.CellCommissionRate = ParseDecimal(GetColumnValue(10 + offset));
                            entity.CellCommissionAmount = ParseDecimal(GetColumnValue(11 + offset));
                            entity.TotalCommission = ParseDecimal(GetColumnValue(12 + offset));
                            entity.WorkingDays = ParseDecimal(GetColumnValue(13 + offset));
                            entity.LeaveDays = ParseDecimal(GetColumnValue(14 + offset));
                            entity.CalculatedGrossSalary = ParseDecimal(GetColumnValue(15 + offset));
                            entity.FinalGrossSalary = ParseDecimal(GetColumnValue(16 + offset));
                            entity.MonthlyTrainingSubsidy = ParseDecimal(GetColumnValue(17 + offset));
                            entity.MonthlyTransportSubsidy = ParseDecimal(GetColumnValue(18 + offset));
                            entity.LastMonthTrainingSubsidy = ParseDecimal(GetColumnValue(19 + offset));
                            entity.LastMonthTransportSubsidy = ParseDecimal(GetColumnValue(20 + offset));
                            entity.TotalSubsidy = ParseDecimal(GetColumnValue(21 + offset));
                            entity.MissingCard = ParseDecimal(GetColumnValue(22 + offset));
                            entity.LateArrival = ParseDecimal(GetColumnValue(23 + offset));
                            entity.LeaveDeduction = ParseDecimal(GetColumnValue(24 + offset));
                            entity.SocialInsuranceDeduction = ParseDecimal(GetColumnValue(25 + offset));
                            entity.RewardDeduction = ParseDecimal(GetColumnValue(26 + offset));
                            entity.AccommodationDeduction = ParseDecimal(GetColumnValue(27 + offset));
                            entity.StudyPeriodDeduction = ParseDecimal(GetColumnValue(28 + offset));
                            entity.WorkClothesDeduction = ParseDecimal(GetColumnValue(29 + offset));
                            entity.TotalDeduction = ParseDecimal(GetColumnValue(30 + offset));
                            entity.Bonus = ParseDecimal(GetColumnValue(31 + offset));
                            entity.ReturnPhoneDeposit = ParseDecimal(GetColumnValue(32 + offset));
                            entity.ReturnAccommodationDeposit = ParseDecimal(GetColumnValue(33 + offset));
                            entity.LastMonthSupplement = ParseDecimal(GetColumnValue(34 + offset));
                            entity.ActualSalary = ParseDecimal(GetColumnValue(35 + offset));
                            entity.MonthlyPaymentStatus = GetColumnValue(36 + offset);
                            entity.PaidAmount = ParseDecimal(GetColumnValue(37 + offset));
                            entity.PendingAmount = ParseDecimal(GetColumnValue(38 + offset));
                            entity.MonthlyTotalPayment = ParseDecimal(GetColumnValue(39 + offset));
                            var isLockedStr = GetColumnValue(40 + offset);
                            entity.IsLocked = isLockedStr == "已锁定" || isLockedStr == "1" || isLockedStr == "锁定" ? 1 : 0;

                            if (existing != null)
                            {
                                entity.EmployeeId = existing.EmployeeId;
                                entity.StoreDetail = existing.StoreDetail;
                            }
                            else
                            {
                                if (!string.IsNullOrWhiteSpace(employeeName))
                                {
                                    var user = await _db.Queryable<UserEntity>()
                                        .Where(u => u.RealName == employeeName).FirstAsync();
                                    if (user != null) entity.EmployeeId = user.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,
                    failCount,
                    skippedCount,
                    errors = errorMessages
                };
            }
            catch (Exception ex)
            {
                throw NCCException.Oh($"导入科技部总经理工资数据失败: {ex.Message}");
            }
        }

        #endregion
    }
}