using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Logging;
using NCC.Dependency;
using NCC.DynamicApiController;
using NCC.FriendlyException;
using NCC.Extend.Entitys.Dto.LqTechDepartmentDashboard;
using NCC.Extend.Entitys.lq_kd_jksyj;
using NCC.Extend.Entitys.lq_hytk_jksyj;
using NCC.Extend.Entitys.lq_md_target;
using NCC.Extend.Entitys.lq_xh_hyhk;
using NCC.Extend.Entitys.lq_xh_pxmx;
using NCC.Extend.Entitys.lq_kd_kdjlb;
using NCC.Extend.Entitys.lq_kd_pxmx;
using NCC.Extend.Entitys.lq_mdxx;
using NCC.Extend.Entitys.lq_tech_teacher_salary_statistics;
using NCC.Extend.Entitys.lq_tech_general_manager_salary_statistics;
using NCC.Extend.Entitys.lq_xh_kjbsyj;
using NCC.Extend.Entitys.lq_xmzl;
using NCC.Extend.Entitys.lq_hytk_hytk;
using NCC.Extend.Entitys.lq_kd_kjbsyj;
using NCC.Extend.Entitys.lq_hytk_kjbsyj;
using NCC.Extend.Entitys.lq_kd_deductinfo;
using NCC.Extend.Entitys.lq_khxx;
using NCC.Extend.Entitys;
using NCC.Common.Filter;
using NCC.System.Entitys.Permission;
using SqlSugar;

namespace NCC.Extend
{
    /// <summary>
    /// 科技部驾驶舱服务
    /// </summary>
    [ApiDescriptionSettings(Tag = "科技部驾驶舱服务", Name = "LqTechDepartmentDashboard", Order = 202)]
    [Route("api/Extend/[controller]")]
    public class LqTechDepartmentDashboardService : IDynamicApiController, ITransient
    {
        private readonly ISqlSugarClient _db;
        private readonly ILogger<LqTechDepartmentDashboardService> _logger;

        /// <summary>
        /// 初始化科技部驾驶舱服务
        /// </summary>
        public LqTechDepartmentDashboardService(ISqlSugarClient db, ILogger<LqTechDepartmentDashboardService> logger)
        {
            _db = db;
            _logger = logger;
        }

        // 辅助方法：获取门店列表
        private async Task<List<string>> GetStoreIdsAsync(string techDepartmentId, List<string> storeIds, string statisticsMonth)
        {
            if (!string.IsNullOrWhiteSpace(techDepartmentId))
            {
                return await _db.Queryable<LqMdTargetEntity>()
                    .Where(x => x.TechDepartment == techDepartmentId && x.Month == statisticsMonth)
                    .Select(x => x.StoreId)
                    .Distinct()
                    .ToListAsync();
            }
            return storeIds ?? new List<string>();
        }

        // 辅助方法：获取门店列表（重载）
        private async Task<List<string>> GetStoreIdsAsync(TechDepartmentDashboardStatisticsInput input)
        {
            return await GetStoreIdsAsync(input.TechDepartmentId, input.StoreIds, input.StatisticsMonth);
        }

        // 辅助方法：获取指定月份的门店列表
        private async Task<List<string>> GetStoreIdsForMonthAsync(string techDepartmentId, List<string> storeIds, string month)
        {
            if (!string.IsNullOrWhiteSpace(techDepartmentId))
            {
                return await _db.Queryable<LqMdTargetEntity>()
                    .Where(x => x.TechDepartment == techDepartmentId && x.Month == month)
                    .Select(x => x.StoreId)
                    .Distinct()
                    .ToListAsync();
            }
            return storeIds ?? new List<string>();
        }

        // 辅助方法：计算单月业绩指标
        private async Task<(decimal traceabilityAmount, decimal cellAmount, decimal totalKemeiIncome)> CalculateMonthlyPerformance(List<string> storeIds, string month)
        {
            if (!storeIds.Any()) return (0, 0, 0);

            var year = int.Parse(month.Substring(0, 4));
            var monthNum = int.Parse(month.Substring(4, 2));
            var startDate = new DateTime(year, monthNum, 1);
            var endDate = startDate.AddMonths(1).AddDays(-1);
            var endDateTime = month == DateTime.Now.ToString("yyyyMM")
                ? DateTime.Now
                : endDate.Date.AddHours(23).AddMinutes(59).AddSeconds(59);

            var storeIdsStr = string.Join("','", storeIds);

            // 溯源金额
            var billingTraceabilitySql = $@"
                SELECT COALESCE(SUM(CAST(jksyj AS DECIMAL(18,2))), 0) as Amount
                FROM lq_kd_jksyj
                WHERE F_IsEffective = 1
                    AND F_StoreId IN ('{storeIdsStr}')
                    AND (F_BeautyType = '溯源系统' OR F_BeautyType = '溯源')
                    AND yjsj >= '{startDate:yyyy-MM-dd HH:mm:ss}'
                    AND yjsj <= '{endDateTime:yyyy-MM-dd HH:mm:ss}'";
            var billingTraceabilityResult = await _db.Ado.SqlQueryAsync<dynamic>(billingTraceabilitySql);
            var billingTraceabilityAmount = billingTraceabilityResult?.FirstOrDefault() != null
                ? Convert.ToDecimal(billingTraceabilityResult.FirstOrDefault().Amount ?? 0)
                : 0m;

            var refundTraceabilityAmount = await _db.Queryable<LqHytkJksyjEntity>()
                .Where(x => x.IsEffective == 1)
                .Where(x => storeIds.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)
                .SumAsync(x => (decimal?)(x.Jksyj ?? 0)) ?? 0m;

            var traceabilityAmount = billingTraceabilityAmount - refundTraceabilityAmount;

            // Cell金额
            var billingCellSql = $@"
                SELECT COALESCE(SUM(CAST(jksyj AS DECIMAL(18,2))), 0) as Amount
                FROM lq_kd_jksyj
                WHERE F_IsEffective = 1
                    AND F_StoreId IN ('{storeIdsStr}')
                    AND (F_BeautyType = 'cell' OR F_BeautyType = 'Cell')
                    AND yjsj >= '{startDate:yyyy-MM-dd HH:mm:ss}'
                    AND yjsj <= '{endDateTime:yyyy-MM-dd HH:mm:ss}'";
            var billingCellResult = await _db.Ado.SqlQueryAsync<dynamic>(billingCellSql);
            var billingCellAmount = billingCellResult?.FirstOrDefault() != null
                ? Convert.ToDecimal(billingCellResult.FirstOrDefault().Amount ?? 0)
                : 0m;

            var refundCellAmount = await _db.Queryable<LqHytkJksyjEntity>()
                .Where(x => x.IsEffective == 1)
                .Where(x => storeIds.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)
                .SumAsync(x => (decimal?)(x.Jksyj ?? 0)) ?? 0m;

            var cellAmount = billingCellAmount - refundCellAmount;
            var totalKemeiIncome = traceabilityAmount + cellAmount;

            return (traceabilityAmount, cellAmount, totalKemeiIncome);
        }

        /// <summary>
        /// 获取科技部驾驶舱统计数据
        /// </summary>
        /// <remarks>
        /// 获取指定科技部在指定月份的核心指标：溯源金额、Cell金额、科美总收入、管理的门店数、活跃门店数
        /// 
        /// 示例请求：
        /// ```json
        /// {
        ///   "techDepartmentId": "1649328471923847169",
        ///   "statisticsMonth": "202512"
        /// }
        /// ```
        /// 
        /// 或者：
        /// ```json
        /// {
        ///   "storeIds": ["1649328471923847169", "1649328471923847170"],
        ///   "statisticsMonth": "202512"
        /// }
        /// ```
        /// 
        /// 参数说明：
        /// - techDepartmentId: 科技部ID（BASE_ORGANIZE表的组织ID），与storeIds两者必填其一
        /// - storeIds: 门店ID列表，与techDepartmentId两者必填其一
        /// - statisticsMonth: 统计月份，格式为YYYYMM（必填）
        /// 
        /// 返回数据说明：
        /// - TraceabilityAmount: 溯源金额（开单溯源金额 - 退卡溯源金额）
        /// - CellAmount: Cell金额（开单Cell金额 - 退卡Cell金额）
        /// - TotalKemeiIncome: 科美总收入（溯源金额 + Cell金额）
        /// - ManagedStoreCount: 管理的门店数
        /// - ActiveStoreCount: 活跃门店数（有科美开单或消耗的门店数量）
        /// </remarks>
        /// <param name="input">查询参数</param>
        /// <returns>科技部驾驶舱统计数据</returns>
        /// <response code="200">成功返回统计数据</response>
        /// <response code="400">参数错误</response>
        /// <response code="500">服务器错误</response>
        [HttpPost("GetStatistics")]
        public async Task<TechDepartmentDashboardStatisticsOutput> GetStatistics([FromBody] TechDepartmentDashboardStatisticsInput input)
        {
            try
            {
                if (input == null)
                {
                    throw NCCException.Oh("请求参数不能为空");
                }

                if (string.IsNullOrWhiteSpace(input.StatisticsMonth) || input.StatisticsMonth.Length != 6)
                {
                    throw NCCException.Oh("统计月份格式错误，必须为YYYYMM格式");
                }

                // 参数验证：科技部ID和门店ID列表两者必填其一
                if (string.IsNullOrWhiteSpace(input.TechDepartmentId) &&
                    (input.StoreIds == null || !input.StoreIds.Any()))
                {
                    throw NCCException.Oh("科技部ID和门店ID列表不能同时为空，必须传入其中一个");
                }

                if (!string.IsNullOrWhiteSpace(input.TechDepartmentId) &&
                    input.StoreIds != null && input.StoreIds.Any())
                {
                    throw NCCException.Oh("科技部ID和门店ID列表不能同时传入，请只传入其中一个");
                }

                _logger.LogInformation("开始查询科技部驾驶舱统计数据，科技部ID：{TechDepartmentId}，门店ID列表：{StoreIds}，统计月份：{StatisticsMonth}",
                    input.TechDepartmentId, input.StoreIds != null ? string.Join(",", input.StoreIds) : "", input.StatisticsMonth);

                // 解析月份获取时间范围
                var year = int.Parse(input.StatisticsMonth.Substring(0, 4));
                var month = int.Parse(input.StatisticsMonth.Substring(4, 2));
                var startDate = new DateTime(year, month, 1);
                var endDate = startDate.AddMonths(1).AddDays(-1);
                var endDateTime = input.StatisticsMonth == DateTime.Now.ToString("yyyyMM")
                    ? DateTime.Now
                    : endDate.Date.AddHours(23).AddMinutes(59).AddSeconds(59);

                // 获取门店列表
                List<string> storeIds;
                if (!string.IsNullOrWhiteSpace(input.TechDepartmentId))
                {
                    // 方式1：从lq_md_target表查询该月份、该科技部归属的门店列表
                    storeIds = await _db.Queryable<LqMdTargetEntity>()
                        .Where(x => x.TechDepartment == input.TechDepartmentId && x.Month == input.StatisticsMonth)
                        .Select(x => x.StoreId)
                        .Distinct()
                        .ToListAsync();

                    if (!storeIds.Any())
                    {
                        _logger.LogWarning("科技部ID：{TechDepartmentId} 在 {StatisticsMonth} 月份没有管理的门店", input.TechDepartmentId, input.StatisticsMonth);
                        // 返回空数据
                        return new TechDepartmentDashboardStatisticsOutput();
                    }
                }
                else
                {
                    // 方式2：直接使用传入的门店ID列表
                    storeIds = input.StoreIds;
                }

                // 1. 统计开单溯源金额（使用SQL查询，因为jksyj字段是字符串类型）
                var billingTraceabilitySql = $@"
                    SELECT 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("','", storeIds)}')
                        AND (F_BeautyType = '溯源系统' OR F_BeautyType = '溯源')
                        AND yjsj >= '{startDate:yyyy-MM-dd HH:mm:ss}'
                        AND yjsj <= '{endDateTime:yyyy-MM-dd HH:mm:ss}'";
                var billingTraceabilityResult = await _db.Ado.SqlQueryAsync<dynamic>(billingTraceabilitySql);
                var billingTraceabilityAmount = billingTraceabilityResult?.FirstOrDefault() != null
                    ? Convert.ToDecimal(billingTraceabilityResult.FirstOrDefault().Amount ?? 0)
                    : 0m;

                // 2. 统计退卡溯源金额
                var refundTraceabilityAmount = await _db.Queryable<LqHytkJksyjEntity>()
                    .Where(x => x.IsEffective == 1)
                    .Where(x => storeIds.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)
                    .SumAsync(x => (decimal?)(x.Jksyj ?? 0)) ?? 0m;

                // 3. 计算净溯源金额
                var traceabilityAmount = billingTraceabilityAmount - refundTraceabilityAmount;

                // 4. 统计开单Cell金额（使用SQL查询，因为jksyj字段是字符串类型）
                var billingCellSql = $@"
                    SELECT 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("','", storeIds)}')
                        AND (F_BeautyType = 'cell' OR F_BeautyType = 'Cell')
                        AND yjsj >= '{startDate:yyyy-MM-dd HH:mm:ss}'
                        AND yjsj <= '{endDateTime:yyyy-MM-dd HH:mm:ss}'";
                var billingCellResult = await _db.Ado.SqlQueryAsync<dynamic>(billingCellSql);
                var billingCellAmount = billingCellResult?.FirstOrDefault() != null
                    ? Convert.ToDecimal(billingCellResult.FirstOrDefault().Amount ?? 0)
                    : 0m;

                // 5. 统计退卡Cell金额
                var refundCellAmount = await _db.Queryable<LqHytkJksyjEntity>()
                    .Where(x => x.IsEffective == 1)
                    .Where(x => storeIds.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)
                    .SumAsync(x => (decimal?)(x.Jksyj ?? 0)) ?? 0m;

                // 6. 计算净Cell金额
                var cellAmount = billingCellAmount - refundCellAmount;

                // 7. 计算科美总收入
                var totalKemeiIncome = traceabilityAmount + cellAmount;

                // 8. 管理的门店数
                var managedStoreCount = storeIds.Count;

                // 9. 统计活跃门店数（有科美开单或消耗的门店数量）
                // 先获取有科美开单的门店
                var storesWithBilling = await _db.Queryable<LqKdJksyjEntity>()
                    .Where(x => x.IsEffective == 1)
                    .Where(x => storeIds.Contains(x.StoreId))
                    .Where(x => (x.BeautyType == "溯源系统" || x.BeautyType == "溯源" || x.BeautyType == "cell" || x.BeautyType == "Cell"))
                    .Where(x => x.Yjsj.HasValue && x.Yjsj.Value >= startDate && x.Yjsj.Value <= endDateTime)
                    .Select(x => x.StoreId)
                    .Distinct()
                    .ToListAsync();

                // 获取有科美消耗的门店（通过关联lq_xh_pxmx表，筛选科美类型）
                var storesWithConsumeSql = $@"
                    SELECT DISTINCT xh.Md as StoreId
                    FROM lq_xh_hyhk xh
                    INNER JOIN lq_xh_pxmx px ON px.F_ConsumeInfoId = xh.F_Id
                    INNER JOIN lq_xmzl xm ON px.px = xm.F_Id
                    WHERE xh.F_IsEffective = 1
                        AND px.F_IsEffective = 1
                        AND xh.Md IN ('{string.Join("','", storeIds)}')
                        AND xm.qt2 = '科美'
                        AND xh.Hksj >= '{startDate:yyyy-MM-dd HH:mm:ss}'
                        AND xh.Hksj <= '{endDateTime:yyyy-MM-dd HH:mm:ss}'";

                var storesWithConsumeData = await _db.Ado.SqlQueryAsync<dynamic>(storesWithConsumeSql);
                var storesWithConsume = new List<string>();
                if (storesWithConsumeData != null)
                {
                    foreach (var row in storesWithConsumeData)
                    {
                        var storeId = row?.StoreId?.ToString();
                        if (!string.IsNullOrEmpty(storeId) && !storesWithConsume.Contains(storeId))
                        {
                            storesWithConsume.Add(storeId);
                        }
                    }
                }

                // 合并并去重
                var allActiveStoreIds = storesWithBilling.Union(storesWithConsume).Distinct().ToList();
                var activeStoreCount = allActiveStoreIds.Count;

                // 10. 统计消耗金额（科技部老师消耗业绩）
                var consumeAmount = 0m;
                if (storeIds.Any())
                {
                    var storeIdsStr = string.Join("','", storeIds);
                    var consumeSql = $@"
                        SELECT COALESCE(SUM(kjbsyj.kjblsyj), 0) as ConsumeAmount
                        FROM lq_xh_kjbsyj kjbsyj
                        WHERE kjbsyj.F_IsEffective = 1
                            AND kjbsyj.F_ItemCategory = '科美'
                            AND kjbsyj.F_StoreId IN ('{storeIdsStr}')
                            AND kjbsyj.yjsj >= '{startDate:yyyy-MM-dd HH:mm:ss}'
                            AND kjbsyj.yjsj <= '{endDateTime:yyyy-MM-dd HH:mm:ss}'";
                    var consumeResult = await _db.Ado.SqlQueryAsync<dynamic>(consumeSql);
                    consumeAmount = consumeResult?.FirstOrDefault() != null
                        ? Convert.ToDecimal(consumeResult.FirstOrDefault().ConsumeAmount ?? 0)
                        : 0m;
                }

                // 11. 统计手工费（科技部老师手工费）
                var handworkFee = 0m;
                if (storeIds.Any())
                {
                    var storeIdsStr = string.Join("','", storeIds);
                    var handworkFeeSql = $@"
                        SELECT COALESCE(SUM(kjbsyj.F_LaborCost), 0) as HandworkFee
                        FROM lq_xh_kjbsyj kjbsyj
                        WHERE kjbsyj.F_IsEffective = 1
                            AND kjbsyj.F_ItemCategory = '科美'
                            AND kjbsyj.F_StoreId IN ('{storeIdsStr}')
                            AND kjbsyj.yjsj >= '{startDate:yyyy-MM-dd HH:mm:ss}'
                            AND kjbsyj.yjsj <= '{endDateTime:yyyy-MM-dd HH:mm:ss}'";
                    var handworkFeeResult = await _db.Ado.SqlQueryAsync<dynamic>(handworkFeeSql);
                    handworkFee = handworkFeeResult?.FirstOrDefault() != null
                        ? Convert.ToDecimal(handworkFeeResult.FirstOrDefault().HandworkFee ?? 0)
                        : 0m;
                }

                // 12. 统计开单金额（科美开单实付金额）
                var billingAmount = 0m;
                if (storeIds.Any())
                {
                    var storeIdsStr = string.Join("','", storeIds);
                    var billingAmountSql = $@"
                        SELECT COALESCE(SUM(billing.sfyj), 0) as BillingAmount
                        FROM lq_kd_kdjlb billing
                        INNER JOIN lq_kd_kjbsyj kjbsyj ON billing.F_Id = kjbsyj.glkdbh
                        WHERE billing.F_IsEffective = 1
                            AND kjbsyj.F_IsEffective = 1
                            AND kjbsyj.F_ItemCategory = '科美'
                            AND billing.djmd IN ('{storeIdsStr}')
                            AND billing.kdrq >= '{startDate:yyyy-MM-dd HH:mm:ss}'
                            AND billing.kdrq <= '{endDateTime:yyyy-MM-dd HH:mm:ss}'";
                    var billingAmountResult = await _db.Ado.SqlQueryAsync<dynamic>(billingAmountSql);
                    billingAmount = billingAmountResult?.FirstOrDefault() != null
                        ? Convert.ToDecimal(billingAmountResult.FirstOrDefault().BillingAmount ?? 0)
                        : 0m;
                }

                // 13. 统计退款金额（科美退卡金额）
                // 先找出包含科美品项的退卡记录ID，然后统计这些退卡记录的总金额（避免重复计算）
                var refundAmount = 0m;
                if (storeIds.Any())
                {
                    var storeIdsStr = string.Join("','", storeIds);
                    var refundAmountSql = $@"
                        SELECT COALESCE(SUM(COALESCE(refund.F_ActualRefundAmount, refund.tkje, 0)), 0) as RefundAmount
                        FROM lq_hytk_hytk refund
                        WHERE refund.F_IsEffective = 1
                            AND refund.md IN ('{storeIdsStr}')
                            AND refund.tksj >= '{startDate:yyyy-MM-dd HH:mm:ss}'
                            AND refund.tksj <= '{endDateTime:yyyy-MM-dd HH:mm:ss}'
                            AND EXISTS (
                                SELECT 1 FROM lq_hytk_kjbsyj kjbsyj
                                WHERE kjbsyj.gltkbh = refund.F_Id
                                    AND kjbsyj.F_IsEffective = 1
                                    AND kjbsyj.F_ItemCategory = '科美'
                            )";
                    var refundAmountResult = await _db.Ado.SqlQueryAsync<dynamic>(refundAmountSql);
                    refundAmount = refundAmountResult?.FirstOrDefault() != null
                        ? Convert.ToDecimal(refundAmountResult.FirstOrDefault().RefundAmount ?? 0)
                        : 0m;
                }

                // 14. 统计储扣金额（科美开单的储扣金额）
                var deductAmount = 0m;
                if (storeIds.Any())
                {
                    var storeIdsStr = string.Join("','", storeIds);
                    var deductAmountSql = $@"
                        SELECT COALESCE(SUM(deduct.F_Amount), 0) as DeductAmount
                        FROM lq_kd_deductinfo deduct
                        INNER JOIN lq_kd_kdjlb billing ON deduct.F_BillingId = billing.F_Id
                        LEFT JOIN lq_xmzl item ON deduct.F_ItemId = item.F_Id AND item.F_IsEffective = 1
                        WHERE deduct.F_IsEffective = 1
                            AND billing.F_IsEffective = 1
                            AND COALESCE(item.qt2, '其他') = '科美'
                            AND billing.djmd IN ('{storeIdsStr}')
                            AND COALESCE(deduct.F_BillingTime, billing.kdrq) >= '{startDate:yyyy-MM-dd HH:mm:ss}'
                            AND COALESCE(deduct.F_BillingTime, billing.kdrq) <= '{endDateTime:yyyy-MM-dd HH:mm:ss}'";
                    var deductAmountResult = await _db.Ado.SqlQueryAsync<dynamic>(deductAmountSql);
                    deductAmount = deductAmountResult?.FirstOrDefault() != null
                        ? Convert.ToDecimal(deductAmountResult.FirstOrDefault().DeductAmount ?? 0)
                        : 0m;
                }

                var result = new TechDepartmentDashboardStatisticsOutput
                {
                    TraceabilityAmount = traceabilityAmount,
                    CellAmount = cellAmount,
                    TotalKemeiIncome = totalKemeiIncome,
                    ManagedStoreCount = managedStoreCount,
                    ActiveStoreCount = activeStoreCount,
                    BillingTraceabilityAmount = billingTraceabilityAmount,
                    RefundTraceabilityAmount = refundTraceabilityAmount,
                    BillingCellAmount = billingCellAmount,
                    RefundCellAmount = refundCellAmount,
                    BillingAmount = billingAmount,
                    DeductAmount = deductAmount,
                    RefundAmount = refundAmount,
                    ConsumeAmount = consumeAmount,
                    HandworkFee = handworkFee
                };

                _logger.LogInformation($"科技部驾驶舱统计数据查询完成，统计月份：{input.StatisticsMonth}，管理的门店数：{managedStoreCount}，活跃门店数：{activeStoreCount}，溯源金额：{traceabilityAmount:F2}，Cell金额：{cellAmount:F2}，科美总收入：{totalKemeiIncome:F2}，开单金额：{billingAmount:F2}，储扣金额：{deductAmount:F2}，退款金额：{refundAmount:F2}，消耗金额：{consumeAmount:F2}，手工费：{handworkFee:F2}");

                return result;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "查询科技部驾驶舱统计数据失败，科技部ID：{TechDepartmentId}，统计月份：{StatisticsMonth}", input?.TechDepartmentId, input?.StatisticsMonth);
                throw NCCException.Oh($"查询科技部驾驶舱统计数据失败：{ex.Message}");
            }
        }

        /// <summary>
        /// 获取科技部驾驶舱股份统计数据
        /// </summary>
        /// <remarks>
        /// 获取指定科技部在指定月份的股份相关指标：收入、成本（报销、人工各项明细）、利润
        /// 
        /// 示例请求：
        /// ```json
        /// {
        ///   "techDepartmentId": "1649328471923847169",
        ///   "statisticsMonth": "202512"
        /// }
        /// ```
        /// 
        /// 参数说明：
        /// - techDepartmentId: 科技部ID（BASE_ORGANIZE表的组织ID），与storeIds两者必填其一
        /// - storeIds: 门店ID列表，与techDepartmentId两者必填其一
        /// - statisticsMonth: 统计月份，格式为YYYYMM（必填）
        /// 
        /// 返回数据说明：
        /// - Income: 收入（科美开单30%，需减去退款）
        /// - CostReimbursement: 成本-报销
        /// - CostTeacherBase: 成本-人工-老师底薪
        /// - CostTeacherManual: 成本-人工-老师手工费
        /// - CostTeacherBillingComm: 成本-人工-老师开单提成
        /// - CostTeacherConsumeComm: 成本-人工-老师消耗提成
        /// - CostGMBase: 成本-人工-总经理底薪
        /// - CostGMComm: 成本-人工-总经理提成
        /// - Profit: 利润（收入 - 所有成本）
        /// </remarks>
        /// <param name="input">查询参数</param>
        /// <returns>科技部驾驶舱股份统计数据</returns>
        /// <response code="200">成功返回统计数据</response>
        /// <response code="400">参数错误</response>
        /// <response code="500">服务器错误</response>
        [HttpPost("GetShareStatistics")]
        public async Task<TechDepartmentDashboardShareStatisticsOutput> GetShareStatistics([FromBody] TechDepartmentDashboardStatisticsInput input)
        {
            try
            {
                if (input == null)
                {
                    throw NCCException.Oh("请求参数不能为空");
                }

                if (string.IsNullOrWhiteSpace(input.StatisticsMonth) || input.StatisticsMonth.Length != 6)
                {
                    throw NCCException.Oh("统计月份格式错误，必须为YYYYMM格式");
                }

                if (string.IsNullOrWhiteSpace(input.TechDepartmentId) &&
                    (input.StoreIds == null || !input.StoreIds.Any()))
                {
                    throw NCCException.Oh("科技部ID和门店ID列表不能同时为空，必须传入其中一个");
                }

                _logger.LogInformation("开始查询科技部驾驶舱股份统计数据，科技部ID：{TechDepartmentId}，门店ID列表：{StoreIds}，统计月份：{StatisticsMonth}",
                    input.TechDepartmentId, input.StoreIds != null ? string.Join(",", input.StoreIds) : "", input.StatisticsMonth);

                // 解析月份获取时间范围
                var year = int.Parse(input.StatisticsMonth.Substring(0, 4));
                var month = int.Parse(input.StatisticsMonth.Substring(4, 2));
                var startDate = new DateTime(year, month, 1);
                var endDate = startDate.AddMonths(1).AddDays(-1);
                var endDateTime = input.StatisticsMonth == DateTime.Now.ToString("yyyyMM")
                    ? DateTime.Now
                    : endDate.Date.AddHours(23).AddMinutes(59).AddSeconds(59);

                // 获取门店列表
                List<string> storeIds;
                string deptName = null;

                if (!string.IsNullOrWhiteSpace(input.TechDepartmentId))
                {
                    // 从lq_md_target表查询该月份、该科技部归属的门店列表
                    storeIds = await _db.Queryable<LqMdTargetEntity>()
                        .Where(x => x.TechDepartment == input.TechDepartmentId && x.Month == input.StatisticsMonth)
                        .Select(x => x.StoreId)
                        .Distinct()
                        .ToListAsync();

                    if (!storeIds.Any())
                    {
                        _logger.LogWarning("科技部ID：{TechDepartmentId} 在 {StatisticsMonth} 月份没有管理的门店", input.TechDepartmentId, input.StatisticsMonth);
                        return new TechDepartmentDashboardShareStatisticsOutput();
                    }

                    // 获取组织名称（用于查询总经理工资）
                    var organize = await _db.Queryable<OrganizeEntity>()
                        .Where(x => x.Id == input.TechDepartmentId && x.DeleteMark == null && x.EnabledMark == 1)
                        .Select(x => x.FullName)
                        .FirstAsync();
                    deptName = organize;
                }
                else
                {
                    storeIds = input.StoreIds;
                }

                // 1. 计算收入：科美开单30%（需减去退款）
                decimal kemeiIncome = 0;
                if (storeIds.Any())
                {
                    var storeIdsStr = string.Join("','", storeIds);
                    var kemeiIncomeSql = $@"
                        SELECT COALESCE(SUM(px.F_TotalPrice), 0) as Total
                        FROM lq_kd_pxmx px
                        INNER JOIN lq_kd_kdjlb kd ON px.glkdbh = kd.F_Id
                        WHERE kd.djmd IN ('{storeIdsStr}')
                            AND px.yjsj >= '{startDate:yyyy-MM-dd} 00:00:00'
                            AND px.yjsj < '{endDate:yyyy-MM-dd} 23:59:59'
                            AND px.F_IsEffective = 1
                            AND px.F_ItemCategory = '科美'";
                    var kemeiIncomeResult = await _db.Ado.SqlQueryAsync<dynamic>(kemeiIncomeSql);
                    if (kemeiIncomeResult != null && kemeiIncomeResult.Any())
                    {
                        var first = kemeiIncomeResult.FirstOrDefault();
                        if (first != null)
                        {
                            kemeiIncome = Convert.ToDecimal(first.Total ?? 0);
                        }
                    }
                }

                // 减去对应的科美项目实退金额
                decimal kemeiRefund = 0;
                if (storeIds.Any())
                {
                    var storeIdsStr = string.Join("','", storeIds);
                    var kemeiRefundSql = $@"
                        SELECT COALESCE(SUM(jksyj), 0) as Total
                        FROM lq_hytk_jksyj
                        WHERE F_StoreId IN ('{storeIdsStr}')
                            AND tksj >= '{startDate:yyyy-MM-dd} 00:00:00'
                            AND tksj < '{endDate:yyyy-MM-dd} 23:59:59'
                            AND F_IsEffective = 1
                            AND F_ItemCategory = '科美'";
                    var kemeiRefundResult = await _db.Ado.SqlQueryAsync<dynamic>(kemeiRefundSql);
                    if (kemeiRefundResult != null && kemeiRefundResult.Any())
                    {
                        var first = kemeiRefundResult.FirstOrDefault();
                        if (first != null)
                        {
                            kemeiRefund = Convert.ToDecimal(first.Total ?? 0);
                        }
                    }
                }

                // 收入 = (科美开单 - 科美退卡) * 30%
                var income = (kemeiIncome - kemeiRefund) * 0.3m;

                // 2. 成本-报销：筛选一级分类为"科技部费用"的申请
                var reimbursementAmount = 0m;
                if (storeIds.Any())
                {
                    reimbursementAmount = await _db.Queryable<LqReimbursementApplicationEntity, LqPurchaseRecordsEntity, LqReimbursementCategoryEntity>(
                            (app, purchase, category) => app.PurchaseRecordsId == purchase.Id && purchase.ReimbursementCategoryId == category.Id)
                        .Where((app, purchase, category) =>
                            category.Level1Name == "科技部费用"
                            && app.ApprovalStatus == "已通过"
                            && storeIds.Contains(app.ApplicationStoreId)
                            && app.ApplicationTime >= startDate
                            && app.ApplicationTime <= endDateTime)
                        .SumAsync((app, purchase, category) => purchase.Amount);
                }

                // 3. 成本-人工-科技部老师底薪
                var teacherBaseSalary = await _db.Queryable<LqTechTeacherSalaryStatisticsEntity>()
                    .Where(x => x.StatisticsMonth == input.StatisticsMonth
                        && storeIds.Contains(x.StoreId))
                    .SumAsync(x => x.BaseSalary);

                // 4. 成本-人工-科技部手工费
                var teacherManualFee = await _db.Queryable<LqXhKjbsyjEntity>()
                    .Where(x => x.ItemCategory == "科美"
                        && storeIds.Contains(x.StoreId)
                        && x.Yjsj >= startDate
                        && x.Yjsj <= endDateTime
                        && x.IsEffective == 1)
                    .SumAsync(x => x.LaborCost ?? 0);

                // 5. 成本-人工-科技部开单提成
                var teacherBillingComm = await _db.Queryable<LqTechTeacherSalaryStatisticsEntity>()
                    .Where(x => x.StatisticsMonth == input.StatisticsMonth
                        && storeIds.Contains(x.StoreId))
                    .SumAsync(x => x.PerformanceCommissionAmount);

                // 6. 成本-人工-科技部消耗提成
                var teacherConsumeComm = await _db.Queryable<LqTechTeacherSalaryStatisticsEntity>()
                    .Where(x => x.StatisticsMonth == input.StatisticsMonth
                        && storeIds.Contains(x.StoreId))
                    .SumAsync(x => x.ConsumeCommissionAmount);

                // 7. 成本-人工-科技部总经理底薪和提成（需要组织名称）
                var gmBaseSalary = 0m;
                var gmComm = 0m;
                if (!string.IsNullOrWhiteSpace(deptName))
                {
                    gmBaseSalary = await _db.Queryable<LqTechGeneralManagerSalaryStatisticsEntity>()
                        .Where(x => x.StatisticsMonth == input.StatisticsMonth
                            && x.Position == deptName)
                        .SumAsync(x => x.BaseSalary);

                    gmComm = await _db.Queryable<LqTechGeneralManagerSalaryStatisticsEntity>()
                        .Where(x => x.StatisticsMonth == input.StatisticsMonth
                            && x.Position == deptName)
                        .SumAsync(x => x.TotalCommission);
                }

                // 8. 计算利润
                var totalCost = reimbursementAmount + teacherBaseSalary + teacherManualFee +
                               teacherBillingComm + teacherConsumeComm + gmBaseSalary + gmComm;
                var profit = income - totalCost;

                var result = new TechDepartmentDashboardShareStatisticsOutput
                {
                    Income = income,
                    CostReimbursement = reimbursementAmount,
                    CostTeacherBase = teacherBaseSalary,
                    CostTeacherManual = teacherManualFee,
                    CostTeacherBillingComm = teacherBillingComm,
                    CostTeacherConsumeComm = teacherConsumeComm,
                    CostGMBase = gmBaseSalary,
                    CostGMComm = gmComm,
                    Profit = profit
                };

                _logger.LogInformation($"科技部驾驶舱股份统计数据查询完成，统计月份：{input.StatisticsMonth}，收入：{income:F2}，成本：{totalCost:F2}，利润：{profit:F2}");

                return result;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "查询科技部驾驶舱股份统计数据失败，科技部ID：{TechDepartmentId}，统计月份：{StatisticsMonth}", input?.TechDepartmentId, input?.StatisticsMonth);
                throw NCCException.Oh($"查询科技部驾驶舱股份统计数据失败：{ex.Message}");
            }
        }

        /// <summary>
        /// 获取科技部驾驶舱业绩趋势数据
        /// </summary>
        /// <remarks>
        /// 获取指定科技部在指定时间范围内的业绩趋势：溯源金额趋势、Cell金额趋势、科美总收入趋势
        /// </remarks>
        /// <param name="input">查询参数</param>
        /// <returns>业绩趋势数据</returns>
        [HttpPost("GetPerformanceTrend")]
        public async Task<TechDepartmentDashboardPerformanceTrendOutput> GetPerformanceTrend([FromBody] TechDepartmentDashboardPerformanceTrendInput input)
        {
            try
            {
                if (input == null) throw NCCException.Oh("请求参数不能为空");
                if (string.IsNullOrWhiteSpace(input.StatisticsMonth) || input.StatisticsMonth.Length != 6)
                    throw NCCException.Oh("统计月份格式错误，必须为YYYYMM格式");
                if (string.IsNullOrWhiteSpace(input.TechDepartmentId) && (input.StoreIds == null || !input.StoreIds.Any()))
                    throw NCCException.Oh("科技部ID和门店ID列表不能同时为空，必须传入其中一个");

                var monthCount = input.MonthCount;
                if (monthCount != 3 && monthCount != 6 && monthCount != 12)
                    monthCount = 12;

                var baseMonth = DateTime.ParseExact(input.StatisticsMonth, "yyyyMM", null);
                var result = new TechDepartmentDashboardPerformanceTrendOutput();

                decimal cumulativeTraceability = 0m;
                decimal cumulativeCell = 0m;
                decimal cumulativeTotal = 0m;

                for (int i = monthCount - 1; i >= 0; i--)
                {
                    var trendMonth = baseMonth.AddMonths(-i);
                    var trendMonthStr = trendMonth.ToString("yyyyMM");

                    List<string> storeIds;
                    if (!string.IsNullOrWhiteSpace(input.TechDepartmentId))
                    {
                        storeIds = await GetStoreIdsForMonthAsync(input.TechDepartmentId, null, trendMonthStr);
                    }
                    else
                    {
                        storeIds = input.StoreIds;
                    }

                    var (traceabilityAmount, cellAmount, totalKemeiIncome) = await CalculateMonthlyPerformance(storeIds, trendMonthStr);

                    cumulativeTraceability += traceabilityAmount;
                    cumulativeCell += cellAmount;
                    cumulativeTotal += totalKemeiIncome;

                    result.TrendData.Add(new PerformanceTrendPoint
                    {
                        Month = trendMonthStr,
                        TraceabilityAmount = traceabilityAmount,
                        CellAmount = cellAmount,
                        TotalKemeiIncome = totalKemeiIncome,
                        CumulativeTraceabilityAmount = cumulativeTraceability,
                        CumulativeCellAmount = cumulativeCell,
                        CumulativeTotalKemeiIncome = cumulativeTotal
                    });
                }

                return result;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "查询科技部驾驶舱业绩趋势数据失败");
                throw NCCException.Oh($"查询失败：{ex.Message}");
            }
        }

        /// <summary>
        /// 获取科技部驾驶舱股份趋势数据
        /// </summary>
        /// <remarks>
        /// 获取指定科技部在指定时间范围内的股份趋势：收入趋势、成本趋势、利润趋势
        /// </remarks>
        /// <param name="input">查询参数</param>
        /// <returns>股份趋势数据</returns>
        [HttpPost("GetShareTrend")]
        public async Task<TechDepartmentDashboardShareTrendOutput> GetShareTrend([FromBody] TechDepartmentDashboardShareTrendInput input)
        {
            try
            {
                if (input == null) throw NCCException.Oh("请求参数不能为空");
                if (string.IsNullOrWhiteSpace(input.StatisticsMonth) || input.StatisticsMonth.Length != 6)
                    throw NCCException.Oh("统计月份格式错误，必须为YYYYMM格式");
                if (string.IsNullOrWhiteSpace(input.TechDepartmentId) && (input.StoreIds == null || !input.StoreIds.Any()))
                    throw NCCException.Oh("科技部ID和门店ID列表不能同时为空，必须传入其中一个");

                var monthCount = input.MonthCount;
                if (monthCount != 3 && monthCount != 6 && monthCount != 12)
                    monthCount = 12;

                var baseMonth = DateTime.ParseExact(input.StatisticsMonth, "yyyyMM", null);
                var result = new TechDepartmentDashboardShareTrendOutput();

                string deptName = null;
                if (!string.IsNullOrWhiteSpace(input.TechDepartmentId))
                {
                    var organize = await _db.Queryable<OrganizeEntity>()
                        .Where(x => x.Id == input.TechDepartmentId && x.DeleteMark == null && x.EnabledMark == 1)
                        .Select(x => x.FullName)
                        .FirstAsync();
                    deptName = organize;
                }

                decimal cumulativeIncome = 0m;
                decimal cumulativeCost = 0m;
                decimal cumulativeProfit = 0m;

                for (int i = monthCount - 1; i >= 0; i--)
                {
                    var trendMonth = baseMonth.AddMonths(-i);
                    var trendMonthStr = trendMonth.ToString("yyyyMM");

                    var year = trendMonth.Year;
                    var month = trendMonth.Month;
                    var startDate = new DateTime(year, month, 1);
                    var endDate = startDate.AddMonths(1).AddDays(-1);
                    var endDateTime = trendMonthStr == DateTime.Now.ToString("yyyyMM")
                        ? DateTime.Now
                        : endDate.Date.AddHours(23).AddMinutes(59).AddSeconds(59);

                    List<string> storeIds;
                    if (!string.IsNullOrWhiteSpace(input.TechDepartmentId))
                    {
                        storeIds = await GetStoreIdsForMonthAsync(input.TechDepartmentId, null, trendMonthStr);
                    }
                    else
                    {
                        storeIds = input.StoreIds;
                    }

                    if (!storeIds.Any())
                    {
                        result.TrendData.Add(new ShareTrendPoint { Month = trendMonthStr });
                        continue;
                    }

                    // 计算收入
                    decimal kemeiIncome = 0;
                    decimal kemeiRefund = 0;
                    if (storeIds.Any())
                    {
                        var storeIdsStr = string.Join("','", storeIds);
                        var kemeiIncomeSql = $@"
                            SELECT COALESCE(SUM(px.F_TotalPrice), 0) as Total
                            FROM lq_kd_pxmx px
                            INNER JOIN lq_kd_kdjlb kd ON px.glkdbh = kd.F_Id
                            WHERE kd.djmd IN ('{storeIdsStr}')
                                AND px.yjsj >= '{startDate:yyyy-MM-dd} 00:00:00'
                                AND px.yjsj < '{endDate:yyyy-MM-dd} 23:59:59'
                                AND px.F_IsEffective = 1
                                AND px.F_ItemCategory = '科美'";
                        var kemeiIncomeResult = await _db.Ado.SqlQueryAsync<dynamic>(kemeiIncomeSql);
                        if (kemeiIncomeResult?.Any() == true)
                            kemeiIncome = Convert.ToDecimal(kemeiIncomeResult.FirstOrDefault().Total ?? 0);

                        var kemeiRefundSql = $@"
                            SELECT COALESCE(SUM(jksyj), 0) as Total
                            FROM lq_hytk_jksyj
                            WHERE F_StoreId IN ('{storeIdsStr}')
                                AND tksj >= '{startDate:yyyy-MM-dd} 00:00:00'
                                AND tksj < '{endDate:yyyy-MM-dd} 23:59:59'
                                AND F_IsEffective = 1
                                AND F_ItemCategory = '科美'";
                        var kemeiRefundResult = await _db.Ado.SqlQueryAsync<dynamic>(kemeiRefundSql);
                        if (kemeiRefundResult?.Any() == true)
                            kemeiRefund = Convert.ToDecimal(kemeiRefundResult.FirstOrDefault().Total ?? 0);
                    }

                    var income = (kemeiIncome - kemeiRefund) * 0.3m;

                    // 计算成本
                    var reimbursementAmount = await _db.Queryable<LqReimbursementApplicationEntity, LqPurchaseRecordsEntity, LqReimbursementCategoryEntity>(
                            (app, purchase, category) => app.PurchaseRecordsId == purchase.Id && purchase.ReimbursementCategoryId == category.Id)
                        .Where((app, purchase, category) =>
                            category.Level1Name == "科技部费用"
                            && app.ApprovalStatus == "已通过"
                            && storeIds.Contains(app.ApplicationStoreId)
                            && app.ApplicationTime >= startDate
                            && app.ApplicationTime <= endDateTime)
                        .SumAsync((app, purchase, category) => purchase.Amount);

                    var teacherBaseSalary = await _db.Queryable<LqTechTeacherSalaryStatisticsEntity>()
                        .Where(x => x.StatisticsMonth == trendMonthStr && storeIds.Contains(x.StoreId))
                        .SumAsync(x => x.BaseSalary);

                    var teacherManualFee = await _db.Queryable<LqXhKjbsyjEntity>()
                        .Where(x => x.ItemCategory == "科美" && storeIds.Contains(x.StoreId) && x.Yjsj >= startDate && x.Yjsj <= endDateTime && x.IsEffective == 1)
                        .SumAsync(x => x.LaborCost ?? 0);

                    var teacherBillingComm = await _db.Queryable<LqTechTeacherSalaryStatisticsEntity>()
                        .Where(x => x.StatisticsMonth == trendMonthStr && storeIds.Contains(x.StoreId))
                        .SumAsync(x => x.PerformanceCommissionAmount);

                    var teacherConsumeComm = await _db.Queryable<LqTechTeacherSalaryStatisticsEntity>()
                        .Where(x => x.StatisticsMonth == trendMonthStr && storeIds.Contains(x.StoreId))
                        .SumAsync(x => x.ConsumeCommissionAmount);

                    var gmBaseSalary = 0m;
                    var gmComm = 0m;
                    if (!string.IsNullOrWhiteSpace(deptName))
                    {
                        gmBaseSalary = await _db.Queryable<LqTechGeneralManagerSalaryStatisticsEntity>()
                            .Where(x => x.StatisticsMonth == trendMonthStr && x.Position == deptName)
                            .SumAsync(x => x.BaseSalary);
                        gmComm = await _db.Queryable<LqTechGeneralManagerSalaryStatisticsEntity>()
                            .Where(x => x.StatisticsMonth == trendMonthStr && x.Position == deptName)
                            .SumAsync(x => x.TotalCommission);
                    }

                    var totalCost = reimbursementAmount + teacherBaseSalary + teacherManualFee + teacherBillingComm + teacherConsumeComm + gmBaseSalary + gmComm;
                    var profit = income - totalCost;

                    cumulativeIncome += income;
                    cumulativeCost += totalCost;
                    cumulativeProfit += profit;

                    result.TrendData.Add(new ShareTrendPoint
                    {
                        Month = trendMonthStr,
                        Income = income,
                        CostReimbursement = reimbursementAmount,
                        CostTeacherBase = teacherBaseSalary,
                        CostTeacherManual = teacherManualFee,
                        CostTeacherBillingComm = teacherBillingComm,
                        CostTeacherConsumeComm = teacherConsumeComm,
                        CostGMBase = gmBaseSalary,
                        CostGMComm = gmComm,
                        Profit = profit,
                        CumulativeIncome = cumulativeIncome,
                        CumulativeCost = cumulativeCost,
                        CumulativeProfit = cumulativeProfit,
                        ProfitRate = income > 0 ? (profit / income) * 100 : 0
                    });
                }

                return result;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "查询科技部驾驶舱股份趋势数据失败");
                throw NCCException.Oh($"查询失败：{ex.Message}");
            }
        }

        /// <summary>
        /// 获取科技部驾驶舱门店业绩排行
        /// </summary>
        /// <remarks>
        /// 获取指定科技部在指定月份的门店业绩排行：门店溯源金额排行、门店Cell金额排行、门店科美总收入排行
        /// </remarks>
        /// <param name="input">查询参数</param>
        /// <returns>门店业绩排行数据</returns>
        [HttpPost("GetStoreRanking")]
        public async Task<TechDepartmentDashboardStoreRankingOutput> GetStoreRanking([FromBody] TechDepartmentDashboardStatisticsInput input)
        {
            try
            {
                if (input == null) throw NCCException.Oh("请求参数不能为空");
                if (string.IsNullOrWhiteSpace(input.StatisticsMonth) || input.StatisticsMonth.Length != 6)
                    throw NCCException.Oh("统计月份格式错误，必须为YYYYMM格式");
                if (string.IsNullOrWhiteSpace(input.TechDepartmentId) && (input.StoreIds == null || !input.StoreIds.Any()))
                    throw NCCException.Oh("科技部ID和门店ID列表不能同时为空，必须传入其中一个");

                var year = int.Parse(input.StatisticsMonth.Substring(0, 4));
                var month = int.Parse(input.StatisticsMonth.Substring(4, 2));
                var startDate = new DateTime(year, month, 1);
                var endDate = startDate.AddMonths(1).AddDays(-1);
                var endDateTime = input.StatisticsMonth == DateTime.Now.ToString("yyyyMM")
                    ? DateTime.Now
                    : endDate.Date.AddHours(23).AddMinutes(59).AddSeconds(59);

                var storeIds = await GetStoreIdsAsync(input);
                if (!storeIds.Any())
                {
                    return new TechDepartmentDashboardStoreRankingOutput();
                }

                var storeIdsStr = string.Join("','", storeIds);

                // 统计各门店的业绩
                var storePerformanceSql = $@"
                    SELECT * FROM (
                        SELECT 
                            md.F_Id as StoreId,
                            md.dm as StoreName,
                            COALESCE(SUM(CASE WHEN jksyj.F_BeautyType IN ('溯源系统', '溯源') THEN CAST(jksyj.jksyj AS DECIMAL(18,2)) ELSE 0 END), 0) 
                                - COALESCE(refund_trace.RefundAmount, 0) as TraceabilityAmount,
                            COALESCE(SUM(CASE WHEN jksyj.F_BeautyType IN ('cell', 'Cell') THEN CAST(jksyj.jksyj AS DECIMAL(18,2)) ELSE 0 END), 0)
                                - COALESCE(refund_cell.RefundAmount, 0) as CellAmount
                        FROM lq_mdxx md
                        LEFT JOIN lq_kd_jksyj jksyj ON md.F_Id = jksyj.F_StoreId 
                            AND jksyj.F_IsEffective = 1
                            AND jksyj.yjsj >= '{startDate:yyyy-MM-dd HH:mm:ss}'
                            AND jksyj.yjsj <= '{endDateTime:yyyy-MM-dd HH:mm:ss}'
                        LEFT JOIN (
                            SELECT F_StoreId, COALESCE(SUM(CAST(jksyj AS DECIMAL(18,2))), 0) as RefundAmount
                            FROM lq_hytk_jksyj
                            WHERE F_IsEffective = 1
                                AND F_BeautyType IN ('溯源系统', '溯源')
                                AND tksj >= '{startDate:yyyy-MM-dd} 00:00:00'
                                AND tksj <= '{endDate:yyyy-MM-dd} 23:59:59'
                            GROUP BY F_StoreId
                        ) refund_trace ON md.F_Id = refund_trace.F_StoreId
                        LEFT JOIN (
                            SELECT F_StoreId, COALESCE(SUM(CAST(jksyj AS DECIMAL(18,2))), 0) as RefundAmount
                            FROM lq_hytk_jksyj
                            WHERE F_IsEffective = 1
                                AND F_BeautyType IN ('cell', 'Cell')
                                AND tksj >= '{startDate:yyyy-MM-dd} 00:00:00'
                                AND tksj <= '{endDate:yyyy-MM-dd} 23:59:59'
                            GROUP BY F_StoreId
                        ) refund_cell ON md.F_Id = refund_cell.F_StoreId
                        WHERE md.F_Id IN ('{storeIdsStr}')
                        GROUP BY md.F_Id, md.dm, refund_trace.RefundAmount, refund_cell.RefundAmount
                    ) t
                    WHERE TraceabilityAmount > 0 OR CellAmount > 0
                    ORDER BY (TraceabilityAmount + CellAmount) DESC";

                var storePerformanceData = await _db.Ado.SqlQueryAsync<dynamic>(storePerformanceSql);
                var storePerformanceList = storePerformanceData?.ToList() ?? new List<dynamic>();

                decimal totalKemeiIncome = storePerformanceList.Sum(x =>
                {
                    decimal trace = 0;
                    decimal cell = 0;
                    trace = Convert.ToDecimal(x.TraceabilityAmount);
                    cell = Convert.ToDecimal(x.CellAmount);

                    return trace + cell;
                });
                var result = new TechDepartmentDashboardStoreRankingOutput();
                int rankingIndex = 1;
                foreach (var item in storePerformanceList)
                {
                    var rankingItem = new StoreRankingItem
                    {
                        Ranking = rankingIndex,
                        StoreId = item.StoreId?.ToString() ?? "",
                        StoreName = item.StoreName?.ToString() ?? "未知门店",
                        TraceabilityAmount = item.TraceabilityAmount,
                        CellAmount = item.CellAmount,
                        TotalKemeiIncome = Convert.ToDecimal(item.TraceabilityAmount ?? 0) + Convert.ToDecimal(item.CellAmount ?? 0),
                        Percentage = totalKemeiIncome > 0 ? ((Convert.ToDecimal(item.TraceabilityAmount ?? 0) + Convert.ToDecimal(item.CellAmount ?? 0)) / totalKemeiIncome) * 100 : 0
                    };
                    result.RankingData.Add(rankingItem);
                    rankingIndex++;
                }

                return result;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "查询科技部驾驶舱门店业绩排行失败");
                throw NCCException.Oh($"查询失败：{ex.Message}");
            }
        }

        /// <summary>
        /// 获取科技部驾驶舱门店业绩分布
        /// </summary>
        /// <remarks>
        /// 获取指定科技部在指定月份的门店业绩分布数据（用于饼图展示）
        /// </remarks>
        /// <param name="input">查询参数</param>
        /// <returns>门店业绩分布数据</returns>
        [HttpPost("GetStoreDistribution")]
        public async Task<TechDepartmentDashboardStoreDistributionOutput> GetStoreDistribution([FromBody] TechDepartmentDashboardStatisticsInput input)
        {
            try
            {
                if (input == null) throw NCCException.Oh("请求参数不能为空");
                if (string.IsNullOrWhiteSpace(input.StatisticsMonth) || input.StatisticsMonth.Length != 6)
                    throw NCCException.Oh("统计月份格式错误，必须为YYYYMM格式");
                if (string.IsNullOrWhiteSpace(input.TechDepartmentId) && (input.StoreIds == null || !input.StoreIds.Any()))
                    throw NCCException.Oh("科技部ID和门店ID列表不能同时为空，必须传入其中一个");

                var year = int.Parse(input.StatisticsMonth.Substring(0, 4));
                var month = int.Parse(input.StatisticsMonth.Substring(4, 2));
                var startDate = new DateTime(year, month, 1);
                var endDate = startDate.AddMonths(1).AddDays(-1);
                var endDateTime = input.StatisticsMonth == DateTime.Now.ToString("yyyyMM")
                    ? DateTime.Now
                    : endDate.Date.AddHours(23).AddMinutes(59).AddSeconds(59);

                var storeIds = await GetStoreIdsAsync(input);
                if (!storeIds.Any())
                {
                    return new TechDepartmentDashboardStoreDistributionOutput();
                }

                var storeIdsStr = string.Join("','", storeIds);

                // 统计各门店的业绩（与GetStoreRanking逻辑相同）
                var storePerformanceSql = $@"
                    SELECT 
                        md.F_Id as StoreId,
                        md.dm as StoreName,
                        COALESCE(SUM(CASE WHEN jksyj.F_BeautyType IN ('溯源系统', '溯源') THEN CAST(jksyj.jksyj AS DECIMAL(18,2)) ELSE 0 END), 0) 
                            - COALESCE(refund_trace.RefundAmount, 0) as TraceabilityAmount,
                        COALESCE(SUM(CASE WHEN jksyj.F_BeautyType IN ('cell', 'Cell') THEN CAST(jksyj.jksyj AS DECIMAL(18,2)) ELSE 0 END), 0)
                            - COALESCE(refund_cell.RefundAmount, 0) as CellAmount
                    FROM lq_mdxx md
                    LEFT JOIN lq_kd_jksyj jksyj ON md.F_Id = jksyj.F_StoreId 
                        AND jksyj.F_IsEffective = 1
                        AND jksyj.yjsj >= '{startDate:yyyy-MM-dd HH:mm:ss}'
                        AND jksyj.yjsj <= '{endDateTime:yyyy-MM-dd HH:mm:ss}'
                    LEFT JOIN (
                        SELECT F_StoreId, COALESCE(SUM(CAST(jksyj AS DECIMAL(18,2))), 0) as RefundAmount
                        FROM lq_hytk_jksyj
                        WHERE F_IsEffective = 1
                            AND F_BeautyType IN ('溯源系统', '溯源')
                            AND tksj >= '{startDate:yyyy-MM-dd}'
                            AND tksj <= '{endDate:yyyy-MM-dd}'
                        GROUP BY F_StoreId
                    ) refund_trace ON md.F_Id = refund_trace.F_StoreId
                    LEFT JOIN (
                        SELECT F_StoreId, COALESCE(SUM(CAST(jksyj AS DECIMAL(18,2))), 0) as RefundAmount
                        FROM lq_hytk_jksyj
                        WHERE F_IsEffective = 1
                            AND F_BeautyType IN ('cell', 'Cell')
                            AND tksj >= '{startDate:yyyy-MM-dd}'
                            AND tksj <= '{endDate:yyyy-MM-dd}'
                        GROUP BY F_StoreId
                    ) refund_cell ON md.F_Id = refund_cell.F_StoreId
                    WHERE md.F_Id IN ('{storeIdsStr}')
                    GROUP BY md.F_Id, md.dm, refund_trace.RefundAmount, refund_cell.RefundAmount";

                var storePerformanceData = await _db.Ado.SqlQueryAsync<dynamic>(storePerformanceSql);
                var storePerformanceList = storePerformanceData?.ToList() ?? new List<dynamic>();

                decimal totalKemeiIncome = storePerformanceList.Sum(x =>
                {
                    decimal trace = 0;
                    decimal cell = 0;
                    trace = Convert.ToDecimal(x.TraceabilityAmount);
                    cell = Convert.ToDecimal(x.CellAmount);

                    return trace + cell;
                });

                var result = new TechDepartmentDashboardStoreDistributionOutput();

                foreach (var item in storePerformanceList)
                {
                    result.DistributionData.Add(new StoreDistributionItem
                    {
                        StoreId = item.StoreId?.ToString() ?? "",
                        StoreName = item.StoreName?.ToString() ?? "未知门店",
                        TraceabilityAmount = item.TraceabilityAmount,
                        CellAmount = item.CellAmount,
                        TotalKemeiIncome = Convert.ToDecimal(item.TraceabilityAmount ?? 0) + Convert.ToDecimal(item.CellAmount ?? 0),
                        Percentage = totalKemeiIncome > 0 ? ((Convert.ToDecimal(item.TraceabilityAmount ?? 0) + Convert.ToDecimal(item.CellAmount ?? 0)) / totalKemeiIncome) * 100 : 0
                    });
                }

                return result;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "查询科技部驾驶舱门店业绩分布失败");
                throw NCCException.Oh($"查询失败：{ex.Message}");
            }
        }

        /// <summary>
        /// 获取科技部驾驶舱老师业绩排行
        /// </summary>
        /// <remarks>
        /// 获取指定科技部在指定月份的科技部老师业绩排行
        /// </remarks>
        /// <param name="input">查询参数</param>
        /// <returns>老师业绩排行数据</returns>
        [HttpPost("GetTeacherRanking")]
        public async Task<TechDepartmentDashboardTeacherRankingOutput> GetTeacherRanking([FromBody] TechDepartmentDashboardStatisticsInput input)
        {
            try
            {
                if (input == null) throw NCCException.Oh("请求参数不能为空");
                if (string.IsNullOrWhiteSpace(input.StatisticsMonth) || input.StatisticsMonth.Length != 6)
                    throw NCCException.Oh("统计月份格式错误，必须为YYYYMM格式");
                if (string.IsNullOrWhiteSpace(input.TechDepartmentId) && (input.StoreIds == null || !input.StoreIds.Any()))
                    throw NCCException.Oh("科技部ID和门店ID列表不能同时为空，必须传入其中一个");

                var storeIds = await GetStoreIdsAsync(input);
                if (!storeIds.Any())
                {
                    return new TechDepartmentDashboardTeacherRankingOutput();
                }

                // 从科技部老师工资统计表获取数据
                var teacherStats = await _db.Queryable<LqTechTeacherSalaryStatisticsEntity>()
                    .Where(x => x.StatisticsMonth == input.StatisticsMonth && storeIds.Contains(x.StoreId))
                    .OrderBy(x => x.TotalPerformance, OrderByType.Desc)
                    .ToListAsync();

                var result = new TechDepartmentDashboardTeacherRankingOutput();
                int ranking = 1;

                foreach (var teacher in teacherStats)
                {
                    result.RankingData.Add(new TeacherRankingItem
                    {
                        Ranking = ranking++,
                        EmployeeId = teacher.EmployeeId ?? "",
                        EmployeeName = teacher.EmployeeName ?? "",
                        EmployeeAccount = teacher.EmployeeAccount ?? "",
                        StoreId = teacher.StoreId ?? "",
                        StoreName = teacher.StoreName ?? "",
                        OrderAchievement = teacher.OrderAchievement,
                        ConsumeAchievement = teacher.ConsumeAchievement,
                        RefundAchievement = teacher.RefundAchievement,
                        TotalPerformance = teacher.TotalPerformance,
                        ProjectCount = teacher.ProjectCount,
                        BaseSalary = teacher.BaseSalary,
                        PerformanceCommissionAmount = teacher.PerformanceCommissionAmount,
                        ConsumeCommissionAmount = teacher.ConsumeCommissionAmount,
                        FinalGrossSalary = teacher.FinalGrossSalary
                    });
                }

                return result;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "查询科技部驾驶舱老师业绩排行失败");
                throw NCCException.Oh($"查询失败：{ex.Message}");
            }
        }

        /// <summary>
        /// 获取科技部驾驶舱运营统计数据
        /// </summary>
        /// <remarks>
        /// 获取指定科技部在指定月份的运营分析：开单分析、消耗分析、退卡分析
        /// </remarks>
        /// <param name="input">查询参数</param>
        /// <returns>运营统计数据</returns>
        [HttpPost("GetOperationStatistics")]
        public async Task<TechDepartmentDashboardOperationStatisticsOutput> GetOperationStatistics([FromBody] TechDepartmentDashboardStatisticsInput input)
        {
            try
            {
                if (input == null) throw NCCException.Oh("请求参数不能为空");
                if (string.IsNullOrWhiteSpace(input.StatisticsMonth) || input.StatisticsMonth.Length != 6)
                    throw NCCException.Oh("统计月份格式错误，必须为YYYYMM格式");
                if (string.IsNullOrWhiteSpace(input.TechDepartmentId) && (input.StoreIds == null || !input.StoreIds.Any()))
                    throw NCCException.Oh("科技部ID和门店ID列表不能同时为空，必须传入其中一个");

                var year = int.Parse(input.StatisticsMonth.Substring(0, 4));
                var month = int.Parse(input.StatisticsMonth.Substring(4, 2));
                var startDate = new DateTime(year, month, 1);
                var endDate = startDate.AddMonths(1).AddDays(-1);
                var endDateTime = input.StatisticsMonth == DateTime.Now.ToString("yyyyMM")
                    ? DateTime.Now
                    : endDate.Date.AddHours(23).AddMinutes(59).AddSeconds(59);

                var storeIds = await GetStoreIdsAsync(input);
                if (!storeIds.Any())
                {
                    return new TechDepartmentDashboardOperationStatisticsOutput();
                }

                var storeIdsStr = string.Join("','", storeIds);

                // 开单分析：开单次数、平均开单金额
                var billingStatsSql = $@"
                    SELECT 
                        COUNT(DISTINCT kd.F_Id) as BillingCount,
                        COALESCE(SUM(CASE WHEN jksyj.F_BeautyType IN ('溯源系统', '溯源', 'cell', 'Cell') THEN CAST(jksyj.jksyj AS DECIMAL(18,2)) ELSE 0 END), 0) as BillingAmount
                    FROM lq_kd_kdjlb kd
                    INNER JOIN lq_kd_jksyj jksyj ON kd.F_Id = jksyj.Glkdbh 
                        AND jksyj.F_IsEffective = 1
                        AND jksyj.F_BeautyType IN ('溯源系统', '溯源', 'cell', 'Cell')
                        AND jksyj.yjsj >= '{startDate:yyyy-MM-dd HH:mm:ss}'
                        AND jksyj.yjsj <= '{endDateTime:yyyy-MM-dd HH:mm:ss}'
                    WHERE kd.F_IsEffective = 1
                        AND kd.djmd IN ('{storeIdsStr}')
                        AND kd.kdrq >= '{startDate:yyyy-MM-dd HH:mm:ss}'
                        AND kd.kdrq <= '{endDateTime:yyyy-MM-dd HH:mm:ss}'";

                var billingStatsResult = await _db.Ado.SqlQueryAsync<dynamic>(billingStatsSql);
                var billingCount = billingStatsResult?.FirstOrDefault() != null
                    ? Convert.ToInt32(billingStatsResult.FirstOrDefault().BillingCount ?? 0)
                    : 0;
                var billingAmount = billingStatsResult?.FirstOrDefault() != null
                    ? Convert.ToDecimal(billingStatsResult.FirstOrDefault().BillingAmount ?? 0)
                    : 0m;

                // 消耗分析：消耗次数、消耗金额
                var consumeStatsSql = $@"
                    SELECT 
                        COUNT(DISTINCT xh.F_Id) as ConsumeCount,
                        COALESCE(SUM(CASE WHEN xmzl.qt2 = '科美' THEN CAST(COALESCE(xhpx.F_TotalPrice, xhpx.pxjg * xhpx.F_ProjectNumber, 0) AS DECIMAL(18,2)) ELSE 0 END), 0) as ConsumeAmount
                    FROM lq_xh_hyhk xh
                    INNER JOIN lq_xh_pxmx xhpx ON xh.F_Id = xhpx.F_ConsumeInfoId
                    INNER JOIN lq_xmzl xmzl ON xhpx.px = xmzl.F_Id
                    WHERE xh.F_IsEffective = 1
                        AND xhpx.F_IsEffective = 1
                        AND xh.Md IN ('{storeIdsStr}')
                        AND xmzl.qt2 = '科美'
                        AND xh.Hksj >= '{startDate:yyyy-MM-dd HH:mm:ss}'
                        AND xh.Hksj <= '{endDateTime:yyyy-MM-dd HH:mm:ss}'";

                var consumeStatsResult = await _db.Ado.SqlQueryAsync<dynamic>(consumeStatsSql);
                var consumeCount = consumeStatsResult?.FirstOrDefault() != null
                    ? Convert.ToInt32(consumeStatsResult.FirstOrDefault().ConsumeCount ?? 0)
                    : 0;
                var consumeAmount = consumeStatsResult?.FirstOrDefault() != null
                    ? Convert.ToDecimal(consumeStatsResult.FirstOrDefault().ConsumeAmount ?? 0)
                    : 0m;

                // 退卡分析：退卡次数、退卡金额
                var refundStatsSql = $@"
                    SELECT 
                        COUNT(DISTINCT hytk.F_Id) as RefundCount,
                        COALESCE(SUM(CASE WHEN jksyj.F_ItemCategory = '科美' THEN CAST(jksyj.jksyj AS DECIMAL(18,2)) ELSE 0 END), 0) as RefundAmount
                    FROM lq_hytk_hytk hytk
                    INNER JOIN lq_hytk_jksyj jksyj ON hytk.F_Id = jksyj.Gltkbh
                        AND jksyj.F_IsEffective = 1
                        AND jksyj.F_ItemCategory = '科美'
                        AND jksyj.tksj >= '{startDate:yyyy-MM-dd} 00:00:00'
                        AND jksyj.tksj <= '{endDate:yyyy-MM-dd} 23:59:59'
                    WHERE hytk.F_IsEffective = 1
                        AND hytk.md IN ('{storeIdsStr}')
                        AND hytk.tksj >= '{startDate:yyyy-MM-dd} 00:00:00'
                        AND hytk.tksj <= '{endDate:yyyy-MM-dd} 23:59:59'";

                var refundStatsResult = await _db.Ado.SqlQueryAsync<dynamic>(refundStatsSql);
                var refundCount = refundStatsResult?.FirstOrDefault() != null
                    ? Convert.ToInt32(refundStatsResult.FirstOrDefault().RefundCount ?? 0)
                    : 0;
                var refundAmount = refundStatsResult?.FirstOrDefault() != null
                    ? Convert.ToDecimal(refundStatsResult.FirstOrDefault().RefundAmount ?? 0)
                    : 0m;

                // 计算消耗率和退卡率
                var consumeRate = billingAmount > 0 ? (consumeAmount / billingAmount) * 100 : 0;
                var refundRate = billingAmount > 0 ? (refundAmount / billingAmount) * 100 : 0;

                var result = new TechDepartmentDashboardOperationStatisticsOutput
                {
                    BillingAnalysis = new BillingAnalysis
                    {
                        BillingCount = billingCount,
                        AverageBillingAmount = billingCount > 0 ? billingAmount / billingCount : 0
                    },
                    ConsumeAnalysis = new ConsumeAnalysis
                    {
                        ConsumeCount = consumeCount,
                        ConsumeAmount = consumeAmount,
                        ConsumeRate = consumeRate
                    },
                    RefundAnalysis = new RefundAnalysis
                    {
                        RefundCount = refundCount,
                        RefundAmount = refundAmount,
                        RefundRate = refundRate
                    }
                };

                return result;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "查询科技部驾驶舱运营统计数据失败");
                throw NCCException.Oh($"查询失败：{ex.Message}");
            }
        }

        /// <summary>
        /// 获取科技部驾驶舱对比分析数据
        /// </summary>
        /// <remarks>
        /// 获取指定科技部在指定月份的时间对比分析：环比对比（与上月对比）、同比对比（与去年同月对比）
        /// </remarks>
        /// <param name="input">查询参数</param>
        /// <returns>对比分析数据</returns>
        [HttpPost("GetComparisonAnalysis")]
        public async Task<TechDepartmentDashboardComparisonAnalysisOutput> GetComparisonAnalysis([FromBody] TechDepartmentDashboardStatisticsInput input)
        {
            try
            {
                if (input == null) throw NCCException.Oh("请求参数不能为空");
                if (string.IsNullOrWhiteSpace(input.StatisticsMonth) || input.StatisticsMonth.Length != 6)
                    throw NCCException.Oh("统计月份格式错误，必须为YYYYMM格式");
                if (string.IsNullOrWhiteSpace(input.TechDepartmentId) && (input.StoreIds == null || !input.StoreIds.Any()))
                    throw NCCException.Oh("科技部ID和门店ID列表不能同时为空，必须传入其中一个");

                var currentMonth = DateTime.ParseExact(input.StatisticsMonth, "yyyyMM", null);
                var lastMonth = currentMonth.AddMonths(-1);
                var lastMonthStr = lastMonth.ToString("yyyyMM");
                var lastYearMonth = currentMonth.AddYears(-1);
                var lastYearMonthStr = lastYearMonth.ToString("yyyyMM");

                // 计算当月业绩
                List<string> currentStoreIds = await GetStoreIdsAsync(input);
                var (currentTraceability, currentCell, currentTotal) = await CalculateMonthlyPerformance(currentStoreIds, input.StatisticsMonth);

                // 计算上月业绩（环比）
                List<string> lastMonthStoreIds;
                if (!string.IsNullOrWhiteSpace(input.TechDepartmentId))
                {
                    lastMonthStoreIds = await GetStoreIdsForMonthAsync(input.TechDepartmentId, null, lastMonthStr);
                }
                else
                {
                    lastMonthStoreIds = input.StoreIds;
                }
                var (lastTraceability, lastCell, lastTotal) = await CalculateMonthlyPerformance(lastMonthStoreIds, lastMonthStr);

                // 计算去年同月业绩（同比）
                List<string> lastYearStoreIds;
                if (!string.IsNullOrWhiteSpace(input.TechDepartmentId))
                {
                    lastYearStoreIds = await GetStoreIdsForMonthAsync(input.TechDepartmentId, null, lastYearMonthStr);
                }
                else
                {
                    lastYearStoreIds = input.StoreIds;
                }
                var (lastYearTraceability, lastYearCell, lastYearTotal) = await CalculateMonthlyPerformance(lastYearStoreIds, lastYearMonthStr);

                // 计算环比变化率
                var momTraceabilityRate = lastTraceability > 0 ? ((currentTraceability - lastTraceability) / lastTraceability) * 100 : 0;
                var momCellRate = lastCell > 0 ? ((currentCell - lastCell) / lastCell) * 100 : 0;
                var momTotalRate = lastTotal > 0 ? ((currentTotal - lastTotal) / lastTotal) * 100 : 0;

                // 计算同比变化率
                var yoyTraceabilityRate = lastYearTraceability > 0 ? ((currentTraceability - lastYearTraceability) / lastYearTraceability) * 100 : 0;
                var yoyCellRate = lastYearCell > 0 ? ((currentCell - lastYearCell) / lastYearCell) * 100 : 0;
                var yoyTotalRate = lastYearTotal > 0 ? ((currentTotal - lastYearTotal) / lastYearTotal) * 100 : 0;

                var result = new TechDepartmentDashboardComparisonAnalysisOutput
                {
                    TimeComparison = new TimeComparison
                    {
                        MonthOverMonth = new ComparisonData
                        {
                            TraceabilityAmountChangeRate = momTraceabilityRate,
                            CellAmountChangeRate = momCellRate,
                            TotalKemeiIncomeChangeRate = momTotalRate
                        },
                        YearOverYear = new ComparisonData
                        {
                            TraceabilityAmountChangeRate = yoyTraceabilityRate,
                            CellAmountChangeRate = yoyCellRate,
                            TotalKemeiIncomeChangeRate = yoyTotalRate
                        }
                    }
                };

                return result;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "查询科技部驾驶舱对比分析数据失败");
                throw NCCException.Oh($"查询失败：{ex.Message}");
            }
        }

        /// <summary>
        /// 获取科技部驾驶舱门店明细列表
        /// </summary>
        /// <remarks>
        /// 获取指定科技部在指定月份的门店明细列表（支持分页、排序、筛选）
        /// </remarks>
        /// <param name="input">查询参数</param>
        /// <returns>门店明细列表</returns>
        [HttpPost("GetStoreDetailList")]
        public async Task<dynamic> GetStoreDetailList(
    [FromBody] TechDepartmentDashboardStoreDetailListInput input)
        {
            try
            {
                if (input == null) throw NCCException.Oh("请求参数不能为空");
                if (string.IsNullOrWhiteSpace(input.StatisticsMonth) || input.StatisticsMonth.Length != 6)
                    throw NCCException.Oh("统计月份格式错误，必须为YYYYMM格式");

                var year = int.Parse(input.StatisticsMonth.Substring(0, 4));
                var month = int.Parse(input.StatisticsMonth.Substring(4, 2));
                var startDate = new DateTime(year, month, 1);
                var endDate = startDate.AddMonths(1).AddDays(-1);
                var endDateTime = input.StatisticsMonth == DateTime.Now.ToString("yyyyMM")
                    ? DateTime.Now
                    : startDate.AddMonths(1).AddSeconds(-1);

                var storeIds = await GetStoreIdsAsync(
                    input.TechDepartmentId, input.StoreIds, input.StatisticsMonth);

                if (!storeIds.Any())
                {
                    return PageResult<TechDepartmentDashboardStoreDetailListOutput>
                        .SqlSugarPageResult(new SqlSugarPagedList<TechDepartmentDashboardStoreDetailListOutput>
                        {
                            list = new List<TechDepartmentDashboardStoreDetailListOutput>(),
                            pagination = new SqlSugar.PagedModel
                            {
                                PageIndex = input.currentPage,
                                PageSize = input.pageSize,
                                Total = 0
                            }
                        });
                }

                var pageIndex = input.currentPage <= 0 ? 1 : input.currentPage;
                var pageSize = input.pageSize <= 0 ? 20 : input.pageSize;

                /* ==============================
                 * ① 只查【门店分页】
                 * ============================== */
                var storeQuery = _db.Queryable<LqMdxxEntity>()
                    .Where(md => storeIds.Contains(md.Id));

                if (!string.IsNullOrWhiteSpace(input.StoreName))
                {
                    storeQuery = storeQuery.Where(md => md.Dm.Contains(input.StoreName));
                }

                var total = await storeQuery.CountAsync();

                var pageStores = await storeQuery
                    .OrderBy(md => md.Dm)
                    .ToPageListAsync(pageIndex, pageSize);

                var pageStoreIds = pageStores.Select(x => x.Id).ToList();

                if (!pageStoreIds.Any())
                    return PageResult<TechDepartmentDashboardStoreDetailListOutput>.SqlSugarPageResult(new SqlSugarPagedList<TechDepartmentDashboardStoreDetailListOutput>
                    {
                        list = new List<TechDepartmentDashboardStoreDetailListOutput>(),
                        pagination = new SqlSugar.PagedModel { PageIndex = pageIndex, PageSize = pageSize, Total = 0 }
                    });

                /* ==============================
                 * ② 只对【当前页门店】做统计 - 开单数据
                 * ============================== */
                var storeIdsStr = string.Join(",", pageStoreIds.Select(x => $"'{x}'"));
                var billingSql = $@"
            SELECT 
                jksyj.F_StoreId AS StoreId,
                SUM(CASE WHEN jksyj.F_BeautyType IN ('溯源系统','溯源')
                    THEN CAST(jksyj.jksyj AS DECIMAL(18,2)) ELSE 0 END) AS BillingTraceabilityAmount,
                SUM(CASE WHEN jksyj.F_BeautyType IN ('cell','Cell')
                    THEN CAST(jksyj.jksyj AS DECIMAL(18,2)) ELSE 0 END) AS BillingCellAmount,
                COUNT(DISTINCT jksyj.Glkdbh) AS BillingCount
            FROM lq_kd_jksyj jksyj
            WHERE jksyj.F_IsEffective = 1
              AND jksyj.F_StoreId IN ({storeIdsStr})
              AND jksyj.yjsj >= '{startDate:yyyy-MM-dd HH:mm:ss}'
              AND jksyj.yjsj <= '{endDateTime:yyyy-MM-dd HH:mm:ss}'
            GROUP BY jksyj.F_StoreId";

                var billingStatList = await _db.Ado.SqlQueryAsync<StoreStatDto>(billingSql);
                var billingStatDict = billingStatList.ToDictionary(x => x.StoreId);

                /* ==============================
                 * ③ 查询退卡数据
                 * ============================== */
                var refundSql = $@"
            SELECT 
                jksyj.F_StoreId AS StoreId,
                SUM(CASE WHEN jksyj.F_BeautyType IN ('溯源系统','溯源')
                    THEN CAST(jksyj.jksyj AS DECIMAL(18,2)) ELSE 0 END) AS RefundTraceabilityAmount,
                SUM(CASE WHEN jksyj.F_BeautyType IN ('cell','Cell')
                    THEN CAST(jksyj.jksyj AS DECIMAL(18,2)) ELSE 0 END) AS RefundCellAmount
            FROM lq_hytk_jksyj jksyj
            WHERE jksyj.F_IsEffective = 1
              AND jksyj.F_StoreId IN ({storeIdsStr})
              AND jksyj.tksj >= '{startDate:yyyy-MM-dd 00:00:00}'
              AND jksyj.tksj <= '{endDate:yyyy-MM-dd 23:59:59}'
            GROUP BY jksyj.F_StoreId";

                var refundStatList = await _db.Ado.SqlQueryAsync<StoreRefundStatDto>(refundSql);
                var refundStatDict = refundStatList.ToDictionary(x => x.StoreId);

                /* ==============================
                 * ④ 查询消耗次数和退卡次数
                 * ============================== */
                var consumeSql = $@"
            SELECT 
                hyhk.md AS StoreId,
                COUNT(DISTINCT hyhk.F_Id) AS ConsumeCount
            FROM lq_xh_hyhk hyhk
            WHERE hyhk.F_IsEffective = 1
              AND hyhk.md IN ({storeIdsStr})
              AND hyhk.Hksj >= '{startDate:yyyy-MM-dd HH:mm:ss}'
              AND hyhk.Hksj <= '{endDateTime:yyyy-MM-dd HH:mm:ss}'
            GROUP BY hyhk.md";

                var consumeStatList = await _db.Ado.SqlQueryAsync<StoreConsumeRefundCountDto>(consumeSql);
                var consumeStatDict = consumeStatList.ToDictionary(x => x.StoreId);

                var refundCountSql = $@"
            SELECT 
                hytk.md AS StoreId,
                COUNT(DISTINCT hytk.F_Id) AS RefundCount
            FROM lq_hytk_hytk hytk
            WHERE hytk.F_IsEffective = 1
              AND hytk.md IN ({storeIdsStr})
              AND hytk.tksj >= '{startDate:yyyy-MM-dd 00:00:00}'
              AND hytk.tksj <= '{endDate:yyyy-MM-dd 23:59:59}'
            GROUP BY hytk.md";

                var refundCountStatList = await _db.Ado.SqlQueryAsync<StoreConsumeRefundCountDto>(refundCountSql);
                var refundCountStatDict = refundCountStatList.ToDictionary(x => x.StoreId);

                /* ==============================
                 * ⑤ 内存合并 + 计算
                 * ============================== */
                var resultList = pageStores.Select(md =>
                {
                    billingStatDict.TryGetValue(md.Id, out var billingStat);
                    refundStatDict.TryGetValue(md.Id, out var refundStat);
                    consumeStatDict.TryGetValue(md.Id, out var consumeStat);
                    refundCountStatDict.TryGetValue(md.Id, out var refundCountStat);

                    var billingTrace = billingStat?.BillingTraceabilityAmount ?? 0m;
                    var refundTrace = refundStat?.RefundTraceabilityAmount ?? 0m;
                    var trace = billingTrace - refundTrace;

                    var billingCell = billingStat?.BillingCellAmount ?? 0m;
                    var refundCell = refundStat?.RefundCellAmount ?? 0m;
                    var cell = billingCell - refundCell;

                    return new TechDepartmentDashboardStoreDetailListOutput
                    {
                        StoreId = md.Id,
                        StoreName = md.Dm,
                        StoreType = md.StoreType,
                        StoreCategory = md.StoreCategory,
                        Month = input.StatisticsMonth,
                        BillingTraceabilityAmount = billingTrace,
                        RefundTraceabilityAmount = refundTrace,
                        TraceabilityAmount = trace,
                        BillingCellAmount = billingCell,
                        RefundCellAmount = refundCell,
                        CellAmount = cell,
                        TotalKemeiIncome = trace + cell,
                        BillingCount = billingStat?.BillingCount ?? 0,
                        ConsumeCount = consumeStat?.ConsumeCount ?? 0,
                        RefundCount = refundCountStat?.RefundCount ?? 0
                    };
                }).ToList();

                return PageResult<TechDepartmentDashboardStoreDetailListOutput>
                    .SqlSugarPageResult(new SqlSugarPagedList<TechDepartmentDashboardStoreDetailListOutput>
                    {
                        list = resultList,
                        pagination = new SqlSugar.PagedModel
                        {
                            PageIndex = pageIndex,
                            PageSize = pageSize,
                            Total = total
                        }
                    });
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "查询科技部驾驶舱门店明细列表失败");
                throw NCCException.Oh($"查询失败：{ex.Message}");
            }
        }

        /// <summary>
        /// 获取科技部驾驶舱老师明细列表
        /// </summary>
        /// <remarks>
        /// 获取指定科技部在指定月份的科技部老师明细列表（支持分页、排序、筛选）
        /// </remarks>
        /// <param name="input">查询参数</param>
        /// <returns>老师明细列表</returns>
        [HttpPost("GetTeacherDetailList")]
        public async Task<dynamic> GetTeacherDetailList([FromBody] TechDepartmentDashboardTeacherDetailListInput input)
        {
            try
            {
                if (input == null) throw NCCException.Oh("请求参数不能为空");
                if (string.IsNullOrWhiteSpace(input.StatisticsMonth) || input.StatisticsMonth.Length != 6)
                    throw NCCException.Oh("统计月份格式错误，必须为YYYYMM格式");
                if (string.IsNullOrWhiteSpace(input.TechDepartmentId) && (input.StoreIds == null || !input.StoreIds.Any()))
                    throw NCCException.Oh("科技部ID和门店ID列表不能同时为空，必须传入其中一个");

                var storeIds = await GetStoreIdsAsync(input.TechDepartmentId, input.StoreIds, input.StatisticsMonth);
                if (!storeIds.Any())
                {
                    var emptyPagination = new SqlSugarPagedList<TechDepartmentDashboardTeacherDetailListOutput>
                    {
                        list = new List<TechDepartmentDashboardTeacherDetailListOutput>(),
                        pagination = new SqlSugar.PagedModel { PageIndex = input.currentPage, PageSize = input.pageSize, Total = 0 }
                    };
                    return PageResult<TechDepartmentDashboardTeacherDetailListOutput>.SqlSugarPageResult(emptyPagination);
                }

                var query = _db.Queryable<LqTechTeacherSalaryStatisticsEntity>()
                    .Where(x => x.StatisticsMonth == input.StatisticsMonth && storeIds.Contains(x.StoreId))
                    .WhereIF(!string.IsNullOrWhiteSpace(input.TeacherName), x => x.EmployeeName.Contains(input.TeacherName))
                    .WhereIF(!string.IsNullOrWhiteSpace(input.StoreId), x => x.StoreId == input.StoreId)
                    .OrderBy(x => x.TotalPerformance, OrderByType.Desc);

                var result = await query.ToPagedListAsync(input.currentPage, input.pageSize);
                var outputList = result.list.Select(x => new TechDepartmentDashboardTeacherDetailListOutput
                {
                    EmployeeId = x.EmployeeId ?? "",
                    EmployeeName = x.EmployeeName ?? "",
                    EmployeeAccount = x.EmployeeAccount ?? "",
                    StoreId = x.StoreId ?? "",
                    StoreName = x.StoreName ?? "",
                    OrderAchievement = x.OrderAchievement,
                    ConsumeAchievement = x.ConsumeAchievement,
                    RefundAchievement = x.RefundAchievement,
                    TotalPerformance = x.TotalPerformance,
                    ProjectCount = x.ProjectCount,
                    BaseSalary = x.BaseSalary,
                    PerformanceCommissionAmount = x.PerformanceCommissionAmount,
                    ConsumeCommissionAmount = x.ConsumeCommissionAmount,
                    HandworkFee = x.HandworkFee,
                    FinalGrossSalary = x.FinalGrossSalary
                }).ToList();

                return PageResult<TechDepartmentDashboardTeacherDetailListOutput>.SqlSugarPageResult(
                    new SqlSugarPagedList<TechDepartmentDashboardTeacherDetailListOutput>
                    {
                        list = outputList,
                        pagination = result.pagination
                    });
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "查询科技部驾驶舱老师明细列表失败");
                throw NCCException.Oh($"查询失败：{ex.Message}");
            }
        }

        /// <summary>
        /// 获取科技部驾驶舱开单明细列表
        /// </summary>
        /// <remarks>
        /// 获取指定科技部在指定月份的科美开单明细列表（支持分页、排序、筛选）
        /// </remarks>
        /// <param name="input">查询参数</param>
        /// <returns>开单明细列表</returns>
        [HttpPost("GetBillingDetailList")]
        public async Task<dynamic> GetBillingDetailList([FromBody] TechDepartmentDashboardBillingDetailListInput input)
        {
            try
            {
                if (input == null) throw NCCException.Oh("请求参数不能为空");
                if (string.IsNullOrWhiteSpace(input.StatisticsMonth) || input.StatisticsMonth.Length != 6)
                    throw NCCException.Oh("统计月份格式错误，必须为YYYYMM格式");
                if (string.IsNullOrWhiteSpace(input.TechDepartmentId) && (input.StoreIds == null || !input.StoreIds.Any()))
                    throw NCCException.Oh("科技部ID和门店ID列表不能同时为空，必须传入其中一个");

                var year = int.Parse(input.StatisticsMonth.Substring(0, 4));
                var month = int.Parse(input.StatisticsMonth.Substring(4, 2));
                var startDate = new DateTime(year, month, 1);
                var endDate = startDate.AddMonths(1).AddDays(-1);
                var endDateTime = input.StatisticsMonth == DateTime.Now.ToString("yyyyMM")
                    ? DateTime.Now
                    : endDate.Date.AddHours(23).AddMinutes(59).AddSeconds(59);

                var storeIds = await GetStoreIdsAsync(input.TechDepartmentId, input.StoreIds, input.StatisticsMonth);
                if (!storeIds.Any())
                {
                    var emptyPagination = new SqlSugarPagedList<TechDepartmentDashboardBillingDetailListOutput>
                    {
                        list = new List<TechDepartmentDashboardBillingDetailListOutput>(),
                        pagination = new SqlSugar.PagedModel { PageIndex = input.currentPage, PageSize = input.pageSize, Total = 0 }
                    };
                    return PageResult<TechDepartmentDashboardBillingDetailListOutput>.SqlSugarPageResult(emptyPagination);
                }

                var query = _db.Queryable<LqKdJksyjEntity, LqKdKdjlbEntity, LqKhxxEntity, LqMdxxEntity>(
                        (jksyj, kd, kh, md) => jksyj.Glkdbh == kd.Id && kd.Kdhy == kh.Id && jksyj.StoreId == md.Id)
                    .Where((jksyj, kd, kh, md) => jksyj.IsEffective == 1 && kd.IsEffective == 1)
                    .Where((jksyj, kd, kh, md) => storeIds.Contains(jksyj.StoreId))
                    .Where((jksyj, kd, kh, md) => (jksyj.BeautyType == "溯源系统" || jksyj.BeautyType == "溯源" || jksyj.BeautyType == "cell" || jksyj.BeautyType == "Cell"))
                    .Where((jksyj, kd, kh, md) => jksyj.Yjsj >= startDate && jksyj.Yjsj <= endDateTime)
                    .WhereIF(!string.IsNullOrWhiteSpace(input.StoreId), (jksyj, kd, kh, md) => jksyj.StoreId == input.StoreId)
                    .WhereIF(!string.IsNullOrWhiteSpace(input.MemberName), (jksyj, kd, kh, md) => kh.Khmc.Contains(input.MemberName))
                    .WhereIF(!string.IsNullOrWhiteSpace(input.BeautyType), (jksyj, kd, kh, md) => jksyj.BeautyType.Contains(input.BeautyType))
                    .OrderBy((jksyj, kd, kh, md) => jksyj.Yjsj, OrderByType.Desc);

                var result = await query.Select((jksyj, kd, kh, md) => new TechDepartmentDashboardBillingDetailListOutput
                {
                    BillingId = kd.Id ?? "",
                    StoreId = jksyj.StoreId ?? "",
                    StoreName = md.Dm ?? "",
                    MemberId = kd.Kdhy ?? "",
                    MemberName = kh.Khmc ?? "",
                    BeautyType = jksyj.BeautyType ?? "",
                    BillingAmount = SqlFunc.ToDecimal(jksyj.Jksyj),
                    ActualAmount = SqlFunc.ToDecimal(jksyj.Jksyj),
                    BillingDate = jksyj.Yjsj,
                    HealthCoachName = "",
                    TechTeacherName = ""
                }).ToPagedListAsync(input.currentPage, input.pageSize);

                return PageResult<TechDepartmentDashboardBillingDetailListOutput>.SqlSugarPageResult(result);
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "查询科技部驾驶舱开单明细列表失败");
                throw NCCException.Oh($"查询失败：{ex.Message}");
            }
        }

        /// <summary>
        /// 获取科技部驾驶舱消耗明细列表
        /// </summary>
        /// <remarks>
        /// 获取指定科技部在指定月份的科美消耗明细列表（支持分页、排序、筛选）
        /// </remarks>
        /// <param name="input">查询参数</param>
        /// <returns>消耗明细列表</returns>
        [HttpPost("GetConsumeDetailList")]
        public async Task<dynamic> GetConsumeDetailList([FromBody] TechDepartmentDashboardConsumeDetailListInput input)
        {
            try
            {
                if (input == null) throw NCCException.Oh("请求参数不能为空");
                if (string.IsNullOrWhiteSpace(input.StatisticsMonth) || input.StatisticsMonth.Length != 6)
                    throw NCCException.Oh("统计月份格式错误，必须为YYYYMM格式");
                if (string.IsNullOrWhiteSpace(input.TechDepartmentId) && (input.StoreIds == null || !input.StoreIds.Any()))
                    throw NCCException.Oh("科技部ID和门店ID列表不能同时为空，必须传入其中一个");

                var year = int.Parse(input.StatisticsMonth.Substring(0, 4));
                var month = int.Parse(input.StatisticsMonth.Substring(4, 2));
                var startDate = new DateTime(year, month, 1);
                var endDate = startDate.AddMonths(1).AddDays(-1);
                var endDateTime = input.StatisticsMonth == DateTime.Now.ToString("yyyyMM")
                    ? DateTime.Now
                    : endDate.Date.AddHours(23).AddMinutes(59).AddSeconds(59);

                var storeIds = await GetStoreIdsAsync(input.TechDepartmentId, input.StoreIds, input.StatisticsMonth);
                if (!storeIds.Any())
                {
                    var emptyPagination = new SqlSugarPagedList<TechDepartmentDashboardConsumeDetailListOutput>
                    {
                        list = new List<TechDepartmentDashboardConsumeDetailListOutput>(),
                        pagination = new SqlSugar.PagedModel { PageIndex = input.currentPage, PageSize = input.pageSize, Total = 0 }
                    };
                    return PageResult<TechDepartmentDashboardConsumeDetailListOutput>.SqlSugarPageResult(emptyPagination);
                }

                var query = _db.Queryable<LqXhHyhkEntity, LqXhPxmxEntity, LqXmzlEntity, LqKhxxEntity, LqMdxxEntity>(
                        (xh, pxmx, xm, kh, md) => xh.Id == pxmx.ConsumeInfoId && pxmx.Px == xm.Id && xh.Hy == kh.Id && xh.Md == md.Id)
                    .Where((xh, pxmx, xm, kh, md) => xh.IsEffective == 1 && pxmx.IsEffective == 1 && xm.Qt2 == "科美")
                    .Where((xh, pxmx, xm, kh, md) => storeIds.Contains(xh.Md))
                    .Where((xh, pxmx, xm, kh, md) => xh.Hksj >= startDate && xh.Hksj <= endDateTime)
                    .WhereIF(!string.IsNullOrWhiteSpace(input.StoreId), (xh, pxmx, xm, kh, md) => xh.Md == input.StoreId)
                    .WhereIF(!string.IsNullOrWhiteSpace(input.MemberName), (xh, pxmx, xm, kh, md) => kh.Khmc.Contains(input.MemberName))
                    .OrderBy((xh, pxmx, xm, kh, md) => xh.Hksj, OrderByType.Desc);

                var result = await query.Select((xh, pxmx, xm, kh, md) => new TechDepartmentDashboardConsumeDetailListOutput
                {
                    ConsumeId = xh.Id ?? "",
                    StoreId = xh.Md ?? "",
                    StoreName = md.Dm ?? "",
                    MemberId = xh.Hy ?? "",
                    MemberName = kh.Khmc ?? "",
                    ItemId = xm.Id ?? "",
                    ItemName = xm.Xmmc ?? "",
                    BeautyType = xm.Qt2 ?? "",
                    ConsumeAmount = pxmx.TotalPrice > 0 ? pxmx.TotalPrice : (pxmx.Pxjg * pxmx.ProjectNumber),
                    ConsumeCount = pxmx.ProjectNumber,
                    ConsumeDate = xh.Hksj,
                    HealthCoachName = "",
                    TechTeacherName = ""
                }).ToPagedListAsync(input.currentPage, input.pageSize);

                return PageResult<TechDepartmentDashboardConsumeDetailListOutput>.SqlSugarPageResult(result);
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "查询科技部驾驶舱消耗明细列表失败");
                throw NCCException.Oh($"查询失败：{ex.Message}");
            }
        }
    }
    public class StoreBaseDto
    {
        public string StoreId { get; set; }
        public string StoreName { get; set; }
        public int? StoreType { get; set; }
        public int? StoreCategory { get; set; }
    }

    public class StoreStatDto
    {
        public string StoreId { get; set; }
        public decimal BillingTraceabilityAmount { get; set; }
        public decimal BillingCellAmount { get; set; }
        public int BillingCount { get; set; }
    }

    public class StoreRefundStatDto
    {
        public string StoreId { get; set; }
        public decimal RefundTraceabilityAmount { get; set; }
        public decimal RefundCellAmount { get; set; }
    }

    public class StoreConsumeRefundCountDto
    {
        public string StoreId { get; set; }
        public int ConsumeCount { get; set; }
        public int RefundCount { get; set; }
    }
}