using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using NCC.Common.Filter;
using NCC.Common.Helper;
using NCC.Dependency;
using NCC.DynamicApiController;
using NCC.Extend.Entitys.Dto.LqAnnualSummary;
using NCC.Extend.Entitys.lq_annual_summary;
using NCC.Extend.Entitys.lq_mdxx;
using NCC.System.Entitys.Permission;
using Mapster;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using Yitter.IdGenerator;

namespace NCC.Extend
{
    /// <summary>
    /// 年度汇总表服务
    /// </summary>
    [ApiDescriptionSettings(Tag = "年度经营数据汇总", Name = "LqAnnualSummary", Order = 500)]
    [Route("api/Extend/[controller]")]
    public class LqAnnualSummaryService : IDynamicApiController, ITransient
    {
        private readonly ISqlSugarClient _db;

        /// <summary>
        /// 初始化一个<see cref="LqAnnualSummaryService"/>类型的新实例
        /// </summary>
        /// <param name="db">数据库客户端</param>
        public LqAnnualSummaryService(ISqlSugarClient db)
        {
            _db = db;
        }

        #region 基础 CRUD

        /// <summary>
        /// 分页查询
        /// </summary>
        [HttpGet("list")]
        public async Task<dynamic> GetList([FromQuery] AnnualSummaryQueryInput input)
        {
            var query = _db.Queryable<LqAnnualSummaryEntity>()
                .Where(x => x.IsEffective == 1);

            if (!string.IsNullOrEmpty(input.StoreName))
            {
                query = query.Where(x => x.StoreName.Contains(input.StoreName));
            }
            if (!string.IsNullOrEmpty(input.StoreId))
            {
                query = query.Where(x => x.StoreId == input.StoreId);
            }
            if (input.Year.HasValue)
            {
                query = query.Where(x => x.Year == input.Year.Value);
            }
            if (input.Month.HasValue)
            {
                query = query.Where(x => x.Month == input.Month.Value);
            }

            var list = await query.OrderBy(x => x.Year, OrderByType.Desc)
                .OrderBy(x => x.Month, OrderByType.Desc)
                .OrderBy(x => x.StoreId)
                .ToPagedListAsync(input.currentPage, input.pageSize);

            // 获取事业部名称映射
            var buNameMap = await GetBusinessUnitNameMapAsync();

            // 获取所有门店信息，用于获取正确的事业部信息
            var storeIds = list.list.Select(x => x.StoreId).Distinct().ToList();
            var stores = await _db.Queryable<LqMdxxEntity>()
                .Where(x => storeIds.Contains(x.Id))
                .Select(x => new { x.Id, x.Syb })
                .ToListAsync();
            var storeDict = stores.ToDictionary(x => x.Id, x => x);

            var result = new
            {
                pagination = list.pagination,
                list = list.list.Select(x =>
                {
                    // 确定正确的事业部ID
                    string correctBuId = null;
                    string correctBuName = null;

                    // 1. 如果BusinessUnitId不为空，检查是否是科技部
                    if (!string.IsNullOrEmpty(x.BusinessUnitId))
                    {
                        var buName = GetBusinessUnitDisplayName(x.BusinessUnitId, buNameMap);
                        // 如果名称包含"科技"，说明是科技部，需要从门店表获取事业部
                        if (buName.Contains("科技"))
                        {
                            // 从门店表获取事业部ID
                            if (storeDict.ContainsKey(x.StoreId))
                            {
                                correctBuId = storeDict[x.StoreId].Syb;
                                if (!string.IsNullOrEmpty(correctBuId))
                                {
                                    correctBuName = GetBusinessUnitDisplayName(correctBuId, buNameMap);
                                }
                            }
                        }
                        else
                        {
                            // 不是科技部，使用原有的BusinessUnitId
                            correctBuId = x.BusinessUnitId;
                            correctBuName = buName;
                        }
                    }
                    else if (!string.IsNullOrEmpty(x.BusinessUnitName))
                    {
                        // 2. 如果BusinessUnitId为空，但BusinessUnitName不为空
                        var buName = GetBusinessUnitDisplayName(x.BusinessUnitName, buNameMap);
                        if (buName.Contains("科技"))
                        {
                            // 从门店表获取事业部ID
                            if (storeDict.ContainsKey(x.StoreId))
                            {
                                correctBuId = storeDict[x.StoreId].Syb;
                                if (!string.IsNullOrEmpty(correctBuId))
                                {
                                    correctBuName = GetBusinessUnitDisplayName(correctBuId, buNameMap);
                                }
                            }
                        }
                        else
                        {
                            // 尝试从BusinessUnitName中解析ID
                            if (buNameMap.ContainsKey(x.BusinessUnitName))
                            {
                                correctBuName = buNameMap[x.BusinessUnitName];
                            }
                            else
                            {
                                correctBuName = buName;
                            }
                        }
                    }
                    else
                    {
                        // 3. 如果都为空，从门店表获取事业部
                        if (storeDict.ContainsKey(x.StoreId))
                        {
                            correctBuId = storeDict[x.StoreId].Syb;
                            if (!string.IsNullOrEmpty(correctBuId))
                            {
                                correctBuName = GetBusinessUnitDisplayName(correctBuId, buNameMap);
                            }
                        }
                    }

                    // 如果还是没有找到，返回"未知"
                    if (string.IsNullOrEmpty(correctBuName))
                    {
                        correctBuName = "未知";
                    }

                    return new
                    {
                        id = x.Id,
                        storeId = x.StoreId,
                        storeName = x.StoreName,
                        businessUnitId = correctBuId,
                        businessUnitName = correctBuName,
                        year = x.Year,
                        month = x.Month,
                        totalPerformance = x.TotalPerformance,
                        totalConsume = x.TotalConsume,
                        headCount = x.HeadCount,
                        personTime = x.PersonTime,
                        projectCount = x.ProjectCount
                    };
                }).ToList()
            };

            return result;
        }

        /// <summary>
        /// 保存（新增或更新）
        /// </summary>
        [HttpPost("save")]
        public async Task Save([FromBody] AnnualSummaryInput input)
        {
            if (string.IsNullOrEmpty(input.Id))
            {
                // Uniqueness check
                var exist = await _db.Queryable<LqAnnualSummaryEntity>()
                    .AnyAsync(x => x.StoreId == input.StoreId && x.Year == input.Year && x.Month == input.Month && x.IsEffective == 1);
                if (exist) throw new Exception($"该门店 {input.Year}年{input.Month}月 数据已存在");

                var entity = input.Adapt<LqAnnualSummaryEntity>();
                entity.Id = YitIdHelper.NextId().ToString();
                entity.CreateTime = DateTime.Now;
                entity.IsEffective = 1;
                await _db.Insertable(entity).ExecuteCommandAsync();
            }
            else
            {
                var entity = await _db.Queryable<LqAnnualSummaryEntity>().FirstAsync(x => x.Id == input.Id);
                if (entity == null) throw new Exception("数据不存在");

                // Update fields
                entity.TotalPerformance = input.TotalPerformance;
                entity.TotalConsume = input.TotalConsume;
                entity.HeadCount = input.HeadCount;
                entity.PersonTime = input.PersonTime;
                entity.ProjectCount = input.ProjectCount;

                entity.UpdateTime = DateTime.Now;
                await _db.Updateable(entity).ExecuteCommandAsync();
            }
        }

        /// <summary>
        /// 删除
        /// </summary>
        [HttpPost("delete")]
        public async Task Delete([FromBody] List<string> ids)
        {
            await _db.Updateable<LqAnnualSummaryEntity>()
                .SetColumns(x => x.IsEffective == 0)
                .Where(x => ids.Contains(x.Id))
                .ExecuteCommandAsync();
        }

        #endregion

        #region Excel 导入导出

        /// <summary>
        /// 导入数据
        /// </summary>
        [HttpPost("import")]
        public async Task Import(IFormFile file)
        {
            if (file == null || file.Length == 0) throw new Exception("请上传文件");

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

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

                // 使用NPOI直接读取Excel文件，支持读取公式的计算值
                var list = new List<AnnualSummaryImportDto>();
                var errorMessages = new List<string>();

                using (var fileStream = new FileStream(tempFilePath, FileMode.Open, FileAccess.Read))
                {
                    IWorkbook workbook = null;
                    if (fileExtension == ".xlsx")
                    {
                        workbook = new XSSFWorkbook(fileStream);
                    }
                    else
                    {
                        workbook = new HSSFWorkbook(fileStream);
                    }

                    ISheet sheet = workbook.GetSheetAt(0); // 第一个工作表
                    if (sheet == null || sheet.LastRowNum < 1)
                    {
                        throw new Exception("Excel文件中没有数据行（至少需要标题行和一行数据）");
                    }

                    // 读取标题行，确定列索引
                    var headerRow = sheet.GetRow(0);
                    if (headerRow == null)
                    {
                        throw new Exception("Excel文件第一行（标题行）为空");
                    }

                    // 根据列名查找列索引（支持多种可能的列名）
                    int GetColumnIndex(string[] possibleNames)
                    {
                        for (int col = 0; col < headerRow.LastCellNum; col++)
                        {
                            var cell = headerRow.GetCell(col);
                            if (cell == null) continue;
                            var columnName = GetCellValue(cell)?.Trim() ?? "";
                            foreach (var name in possibleNames)
                            {
                                if (columnName == name || columnName.Contains(name) || name.Contains(columnName))
                                {
                                    return col;
                                }
                            }
                        }
                        return -1;
                    }

                    // 查找各列的索引（根据实际Excel格式）
                    var businessUnitColIndex = GetColumnIndex(new[] { "事业部", "归属事业部", "BusinessUnit" });
                    var storeNameColIndex = GetColumnIndex(new[] { "门店", "门店名称", "店名", "店铺名称" });
                    var yearColIndex = GetColumnIndex(new[] { "年份", "年", "Year" });
                    var monthColIndex = GetColumnIndex(new[] { "月份", "月", "Month" });
                    var perfColIndex = GetColumnIndex(new[] { "总业绩", "业绩", "总营业额", "TotalPerformance" });
                    var consumeColIndex = GetColumnIndex(new[] { "总消耗", "消耗", "TotalConsume" });
                    var headColIndex = GetColumnIndex(new[] { "人头数", "客头数", "HeadCount" });
                    var personColIndex = GetColumnIndex(new[] { "人次数", "客次数", "PersonTime" });
                    var projectColIndex = GetColumnIndex(new[] { "项目数", "总项目数", "ProjectCount" });

                    // 验证必填列
                    if (storeNameColIndex == -1)
                    {
                        throw new Exception("Excel文件中未找到门店名称列，请确保第一行包含'门店名称'列");
                    }
                    if (yearColIndex == -1)
                    {
                        throw new Exception("Excel文件中未找到年份列，请确保第一行包含'年份'列");
                    }
                    if (monthColIndex == -1)
                    {
                        throw new Exception("Excel文件中未找到月份列，请确保第一行包含'月份'列");
                    }

                    // 从第1行开始读取数据（跳过标题行，索引0是标题行）
                    for (int i = 1; i <= sheet.LastRowNum; i++)
                    {
                        var row = sheet.GetRow(i);
                        if (row == null) continue;

                        try
                        {
                            // 读取门店名称，支持公式计算值
                            var storeNameCell = row.GetCell(storeNameColIndex);
                            if (storeNameCell == null) continue;

                            string storeName = GetCellValue(storeNameCell);
                            if (string.IsNullOrWhiteSpace(storeName)) continue; // 跳过空行

                            // 检测是否为公式文本（如果GetCellValue返回的是公式文本，说明计算失败）
                            if (storeName.StartsWith("_xlfn.") || storeName.StartsWith("=") ||
                                storeName.Contains("XLOOKUP") || storeName.Contains("VLOOKUP"))
                            {
                                errorMessages.Add($"第{i + 1}行：门店名称包含无法计算的公式（{storeName}），已跳过。请确保门店名称列是实际值而不是公式。");
                                continue;
                            }

                            // 读取事业部名称（从Excel中读取）
                            string businessUnitName = null;
                            if (businessUnitColIndex >= 0)
                            {
                                var businessUnitCell = row.GetCell(businessUnitColIndex);
                                if (businessUnitCell != null)
                                {
                                    businessUnitName = GetCellValue(businessUnitCell);
                                    if (!string.IsNullOrWhiteSpace(businessUnitName))
                                    {
                                        businessUnitName = businessUnitName.Trim();
                                    }
                                }
                            }

                            // 读取其他字段
                            var yearCell = row.GetCell(yearColIndex);
                            var monthCell = row.GetCell(monthColIndex);
                            var perfCell = perfColIndex >= 0 ? row.GetCell(perfColIndex) : null;
                            var consumeCell = consumeColIndex >= 0 ? row.GetCell(consumeColIndex) : null;
                            var headCell = headColIndex >= 0 ? row.GetCell(headColIndex) : null;
                            var personCell = personColIndex >= 0 ? row.GetCell(personColIndex) : null;
                            var projectCell = projectColIndex >= 0 ? row.GetCell(projectColIndex) : null;

                            // 解析年份（支持"2024年"格式）
                            int year = 0;
                            var yearStr = GetCellValue(yearCell);
                            if (!string.IsNullOrWhiteSpace(yearStr))
                            {
                                // 提取数字部分（如"2024年" -> "2024"）
                                var yearMatch = Regex.Match(yearStr, @"(\d{4})");
                                if (yearMatch.Success)
                                {
                                    int.TryParse(yearMatch.Groups[1].Value, out year);
                                }
                                else
                                {
                                    int.TryParse(yearStr, out year);
                                }
                            }

                            // 解析月份（支持"1月"、"01月"格式）
                            int month = 0;
                            var monthStr = GetCellValue(monthCell);
                            if (!string.IsNullOrWhiteSpace(monthStr))
                            {
                                // 提取数字部分（如"1月" -> "1"）
                                var monthMatch = Regex.Match(monthStr, @"(\d{1,2})");
                                if (monthMatch.Success)
                                {
                                    int.TryParse(monthMatch.Groups[1].Value, out month);
                                }
                                else
                                {
                                    int.TryParse(monthStr, out month);
                                }
                            }

                            var item = new AnnualSummaryImportDto
                            {
                                StoreName = storeName.Trim(),
                                BusinessUnitName = businessUnitName,
                                Year = year,
                                Month = month,
                                TotalPerformance = perfCell != null && decimal.TryParse(GetCellValue(perfCell), out var perf) ? perf : 0m,
                                TotalConsume = consumeCell != null && decimal.TryParse(GetCellValue(consumeCell), out var consume) ? consume : 0m,
                                HeadCount = headCell != null && decimal.TryParse(GetCellValue(headCell), out var head) ? head : 0m,
                                PersonTime = personCell != null && decimal.TryParse(GetCellValue(personCell), out var person) ? person : 0m,
                                ProjectCount = projectCell != null && decimal.TryParse(GetCellValue(projectCell), out var project) ? project : 0m
                            };
                            list.Add(item);
                        }
                        catch (Exception ex)
                        {
                            errorMessages.Add($"第{i + 1}行：读取数据时出错 - {ex.Message}");
                            continue;
                        }
                    }

                    workbook.Close();
                }

                if (!list.Any())
                {
                    if (errorMessages.Any())
                    {
                        throw new Exception($"导入失败：没有有效数据。\n{string.Join("\n", errorMessages)}");
                    }
                    throw new Exception("导入失败：Excel文件中没有有效数据行");
                }

                // 预加载所有门店信息，用于匹配 StoreId 和 BusinessUnit
                var allStores = await _db.Queryable<LqMdxxEntity>().ToListAsync();

                // 预加载所有组织机构信息，用于匹配事业部
                var allOrgs = await _db.Queryable<OrganizeEntity>()
                    .Where(x => x.DeleteMark == null)
                    .Select(x => new { x.Id, x.FullName })
                    .ToListAsync();

                var entities = new List<LqAnnualSummaryEntity>();
                var successCount = 0;
                var failCount = 0;

                foreach (var item in list)
                {
                    if (string.IsNullOrEmpty(item.StoreName))
                    {
                        failCount++;
                        continue;
                    }

                    try
                    {
                        // 增强匹配逻辑：支持模糊匹配和简称
                        // Excel中的门店名称可能是简化名称（如"紫荆"），需要匹配完整名称（如"绿纤紫荆店"）
                        var store = allStores.FirstOrDefault(x => x.Dm == item.StoreName);
                        if (store == null)
                        {
                            // 精确匹配（包含关系）
                            store = allStores.FirstOrDefault(x => x.Dm.Contains(item.StoreName));
                        }
                        if (store == null)
                        {
                            // 去除 绿纤 和 店 之后再比
                            var cleanExcelName = item.StoreName.Replace("绿纤", "").Replace("店", "").Trim();
                            if (!string.IsNullOrEmpty(cleanExcelName))
                            {
                                store = allStores.FirstOrDefault(x =>
                                    x.Dm.Replace("绿纤", "").Replace("店", "").Trim() == cleanExcelName);
                            }
                        }
                        if (store == null)
                        {
                            // 反向匹配：Excel名称可能是门店名称的一部分（如"紫荆"匹配"绿纤紫荆店"）
                            store = allStores.FirstOrDefault(x =>
                                x.Dm.Replace("绿纤", "").Replace("店", "").Trim().Contains(item.StoreName.Trim()));
                        }
                        if (store == null)
                        {
                            // 最后尝试：Excel名称包含门店名称的一部分（如"静居寺"匹配"绿纤静居寺店"）
                            store = allStores.FirstOrDefault(x =>
                                item.StoreName.Trim().Contains(x.Dm.Replace("绿纤", "").Replace("店", "").Trim()) ||
                                x.Dm.Replace("绿纤", "").Replace("店", "").Trim().Contains(item.StoreName.Trim()));
                        }

                        if (store == null)
                        {
                            failCount++;
                            errorMessages.Add($"门店【{item.StoreName}】未找到匹配的门店。请确保 Excel 中的门店名称在系统中存在（系统示例：{allStores.FirstOrDefault()?.Dm ?? "无"}）");
                            continue;
                        }

                        var entity = new LqAnnualSummaryEntity
                        {
                            StoreId = store.Id,
                            StoreName = store.Dm,
                            Year = item.Year,
                            Month = item.Month,
                            TotalPerformance = item.TotalPerformance,
                            TotalConsume = item.TotalConsume,
                            HeadCount = item.HeadCount,
                            PersonTime = item.PersonTime,
                            ProjectCount = item.ProjectCount,
                            IsEffective = 1,
                            CreateTime = DateTime.Now,
                            CreateUser = "Import"
                        };

                        // 处理事业部信息
                        // 1. 优先从Excel中读取的事业部名称匹配组织表（只匹配事业部，不匹配科技部）
                        if (!string.IsNullOrEmpty(item.BusinessUnitName))
                        {
                            // 先尝试精确匹配
                            var org = allOrgs.FirstOrDefault(x => x.FullName == item.BusinessUnitName);

                            // 如果精确匹配失败，尝试模糊匹配，但只匹配包含"事业"的组织（排除科技部）
                            if (org == null)
                            {
                                org = allOrgs.FirstOrDefault(x =>
                                    x.FullName.Contains("事业") && // 确保是事业部
                                    (x.FullName.Contains(item.BusinessUnitName) ||
                                     item.BusinessUnitName.Contains(x.FullName)));
                            }

                            if (org != null)
                            {
                                // 验证匹配到的组织确实是事业部（不是科技部）
                                if (org.FullName.Contains("事业") && !org.FullName.Contains("科技"))
                                {
                                    entity.BusinessUnitId = org.Id;
                                    entity.BusinessUnitName = org.FullName;
                                }
                                else
                                {
                                    // 如果匹配到的是科技部，记录错误，从门店表获取事业部
                                    errorMessages.Add($"门店【{item.StoreName}】的事业部【{item.BusinessUnitName}】匹配到了科技部，已从门店表获取正确的事业部信息。");
                                    // 从门店表获取事业部
                                    if (!string.IsNullOrEmpty(store.Syb))
                                    {
                                        // 设置事业部ID
                                        entity.BusinessUnitId = store.Syb;

                                        var sybOrg = allOrgs.FirstOrDefault(x => x.Id == store.Syb);
                                        if (sybOrg != null)
                                        {
                                            // 设置事业部名称
                                            entity.BusinessUnitName = sybOrg.FullName;
                                        }
                                        else
                                        {
                                            // 如果找不到组织，记录警告
                                            errorMessages.Add($"门店【{item.StoreName}】的事业部ID【{store.Syb}】未找到对应的组织名称。");
                                            entity.BusinessUnitName = null;
                                        }
                                    }
                                    else
                                    {
                                        // 如果门店表中也没有事业部信息
                                        entity.BusinessUnitId = null;
                                        entity.BusinessUnitName = null;
                                    }
                                }
                            }
                            else
                            {
                                // 如果找不到匹配的组织，从门店表获取事业部
                                errorMessages.Add($"门店【{item.StoreName}】的事业部【{item.BusinessUnitName}】未找到匹配的组织，已从门店表获取事业部信息。");
                                if (!string.IsNullOrEmpty(store.Syb))
                                {
                                    // 设置事业部ID
                                    entity.BusinessUnitId = store.Syb;

                                    var sybOrg = allOrgs.FirstOrDefault(x => x.Id == store.Syb);
                                    if (sybOrg != null)
                                    {
                                        // 设置事业部名称
                                        entity.BusinessUnitName = sybOrg.FullName;
                                    }
                                    else
                                    {
                                        // 如果找不到组织，记录警告
                                        errorMessages.Add($"门店【{item.StoreName}】的事业部ID【{store.Syb}】未找到对应的组织名称。");
                                        entity.BusinessUnitName = null;
                                    }
                                }
                                else
                                {
                                    // 如果门店表中也没有事业部信息
                                    entity.BusinessUnitId = null;
                                    entity.BusinessUnitName = null;
                                }
                            }
                        }
                        else
                        {
                            // 2. 如果Excel中没有事业部信息，从门店信息中获取 Syb (事业部)
                            // 注意：只使用Syb，不使用Kjb（科技部）
                            string buId = store.Syb;

                            if (!string.IsNullOrEmpty(buId))
                            {
                                // 设置事业部ID
                                entity.BusinessUnitId = buId;

                                // 从组织表获取事业部名称
                                var org = allOrgs.FirstOrDefault(x => x.Id == buId);
                                if (org != null)
                                {
                                    // 设置事业部名称
                                    entity.BusinessUnitName = org.FullName;
                                }
                                else
                                {
                                    // 如果找不到组织，记录警告，但不设置名称（保持为空）
                                    errorMessages.Add($"门店【{item.StoreName}】的事业部ID【{buId}】未找到对应的组织名称。");
                                    entity.BusinessUnitName = null; // 明确设置为null，而不是ID
                                }
                            }
                            else
                            {
                                // 如果门店表中也没有事业部信息，记录警告
                                errorMessages.Add($"门店【{item.StoreName}】没有事业部信息，请检查门店配置。");
                                // 明确设置为null
                                entity.BusinessUnitId = null;
                                entity.BusinessUnitName = null;
                            }
                        }

                        entities.Add(entity);
                    }
                    catch (Exception ex)
                    {
                        failCount++;
                        errorMessages.Add($"处理门店【{item.StoreName}】时出错：{ex.Message}");
                        continue;
                    }
                }

                // 批量处理：覆盖逻辑
                // 先删除已存在的 (StoreId + Year + Month)
                foreach (var group in entities.GroupBy(x => new { x.StoreId, x.Year, x.Month }))
                {
                    // 删除旧数据
                    await _db.Deleteable<LqAnnualSummaryEntity>()
                        .Where(x => x.StoreId == group.Key.StoreId && x.Year == group.Key.Year && x.Month == group.Key.Month)
                        .ExecuteCommandAsync();

                    // 插入新数据 (取Excel中最后一条，防止Excel自身重复)
                    var toInsert = group.Last();
                    toInsert.Id = YitIdHelper.NextId().ToString();
                    toInsert.CreateTime = DateTime.Now;
                    toInsert.CreateUser = "Import";
                    await _db.Insertable(toInsert).ExecuteCommandAsync();
                    successCount++;
                }

                // 返回导入结果
                var resultMessage = $"导入完成：成功 {successCount} 条";
                if (failCount > 0 || errorMessages.Any())
                {
                    resultMessage += $"，失败 {failCount} 条";
                    if (errorMessages.Any())
                    {
                        resultMessage += $"\n错误详情：\n{string.Join("\n", errorMessages)}";
                    }
                }

                // 如果有错误但部分成功，返回警告信息（通过异常返回，但包含成功信息）
                if (successCount > 0 && (failCount > 0 || errorMessages.Any()))
                {
                    // 部分成功，抛出包含成功和失败信息的异常
                    throw new Exception(resultMessage);
                }
                else if (successCount == 0)
                {
                    // 全部失败，抛出异常
                    throw new Exception(resultMessage);
                }
            }
            finally
            {
                // 删除临时文件
                if (File.Exists(tempFilePath))
                {
                    File.Delete(tempFilePath);
                }
            }
        }

        #endregion

        #region 统计报表

        /// <summary>
        /// 4.1 全年门店业绩表
        /// </summary>
        [HttpPost("GetTotalPerformanceStat")]
        public async Task<dynamic> GetTotalPerformanceStat([FromBody] AnnualSummaryQueryInput input)
        {
            return await GetMonthlyStat(input, x => x.TotalPerformance);
        }

        /// <summary>
        /// 4.2 全年门店消耗表
        /// </summary>
        [HttpPost("GetTotalConsumeStat")]
        public async Task<dynamic> GetTotalConsumeStat([FromBody] AnnualSummaryQueryInput input)
        {
            return await GetMonthlyStat(input, x => x.TotalConsume);
        }

        /// <summary>
        /// 4.3 年度门店人头表
        /// </summary>
        [HttpPost("GetHeadCountStat")]
        public async Task<dynamic> GetHeadCountStat([FromBody] AnnualSummaryQueryInput input)
        {
            return await GetMonthlyStat(input, x => x.HeadCount);
        }

        /// <summary>
        /// 4.4 年度门店项目数表
        /// </summary>
        [HttpPost("GetProjectCountStat")]
        public async Task<dynamic> GetProjectCountStat([FromBody] AnnualSummaryQueryInput input)
        {
            return await GetMonthlyStat(input, x => x.ProjectCount);
        }

        /// <summary>
        /// 4.5 年度门店人次表
        /// </summary>
        [HttpGet("GetPersonTimeStat")]
        public async Task<dynamic> GetPersonTimeStat([FromQuery] AnnualSummaryQueryInput input)
        {
            return await GetMonthlyStat(input, x => x.PersonTime);
        }

        /// <summary>
        /// 通用月度趋势统计
        /// </summary>
        [HttpGet("GetMonthlyTrend")]
        public async Task<dynamic> GetMonthlyTrend([FromQuery] AnnualSummaryQueryInput input, [FromQuery] string type)
        {
            // 确保 type 参数正确绑定
            if (string.IsNullOrEmpty(type))
            {
                type = "totalperformance"; // 默认值
            }

            // 根据 type 参数选择正确的字段选择器
            Func<LqAnnualSummaryEntity, decimal> fieldSelector;
            switch (type.ToLower())
            {
                case "totalperformance":
                    fieldSelector = x => x.TotalPerformance;
                    break;
                case "totalconsume":
                    fieldSelector = x => x.TotalConsume;
                    break;
                case "headcount":
                    fieldSelector = x => x.HeadCount;
                    break;
                case "persontime":
                    fieldSelector = x => x.PersonTime;
                    break;
                case "projectcount":
                    fieldSelector = x => x.ProjectCount;
                    break;
                default:
                    fieldSelector = x => x.TotalPerformance;
                    break;
            }

            return await GetMonthlyStat(input, fieldSelector);
        }

        /// <summary>
        /// 4.6 门店五项指标统计图
        /// </summary>
        [HttpPost("GetStoreIndicatorsStat")]
        public async Task<dynamic> GetStoreIndicatorsStat([FromBody] AnnualSummaryQueryInput input)
        {
            return await GetIndicatorStat(input, x => x.TotalPerformance);
        }

        /// <summary>
        /// 获取门店指标详情
        /// </summary>
        /// <param name="input">查询参数</param>
        /// <param name="type">指标类型</param>
        /// <returns>指标统计输出</returns>
        [HttpGet("GetStoreIndicatorDetail")]
        public async Task<dynamic> GetStoreIndicatorDetail([FromQuery] AnnualSummaryQueryInput input, string type)
        {
            switch (type?.ToLower())
            {
                case "totalperformance": return await GetIndicatorStat(input, x => x.TotalPerformance);
                case "totalconsume": return await GetIndicatorStat(input, x => x.TotalConsume);
                case "headcount": return await GetIndicatorStat(input, x => x.HeadCount);
                case "persontime": return await GetIndicatorStat(input, x => x.PersonTime);
                case "projectcount": return await GetIndicatorStat(input, x => x.ProjectCount);
                default: return await GetIndicatorStat(input, x => x.TotalPerformance);
            }
        }

        /// <summary>
        /// 4.7 事业部五项指标总计图
        /// </summary>
        [HttpGet("GetBusinessUnitIndicatorsStat")]
        public async Task<dynamic> GetBusinessUnitIndicatorsStat([FromQuery] AnnualSummaryQueryInput input, string type)
        {
            // 类似 4.6，但是按 BusinessUnitName 分组
            switch (type?.ToLower())
            {
                case "totalperformance": return await GetBuIndicatorStat(input, x => x.TotalPerformance);
                case "totalconsume": return await GetBuIndicatorStat(input, x => x.TotalConsume);
                case "headcount": return await GetBuIndicatorStat(input, x => x.HeadCount);
                case "persontime": return await GetBuIndicatorStat(input, x => x.PersonTime);
                case "projectcount": return await GetBuIndicatorStat(input, x => x.ProjectCount);
                default: return await GetBuIndicatorStat(input, x => x.TotalPerformance);
            }
        }

        /// <summary>
        /// 4.8 事业部内部汇总 (宽表)
        /// </summary>
        [HttpPost("GetBusinessUnitSummaryStat")]
        public async Task<dynamic> GetBusinessUnitSummaryStat([FromBody] AnnualSummaryQueryInput input)
        {
            int year = input.Year ?? DateTime.Now.Year;

            // 获取事业部名称映射
            var buNameMap = await GetBusinessUnitNameMapAsync();

            var currentData = await _db.Queryable<LqAnnualSummaryEntity>()
                .Where(x => x.IsEffective == 1 && x.Year == year)
                .WhereIF(!string.IsNullOrEmpty(input.StoreName), x => x.StoreName.Contains(input.StoreName))
                .ToListAsync();

            var lastYearData = await _db.Queryable<LqAnnualSummaryEntity>()
                .Where(x => x.IsEffective == 1 && x.Year == year - 1)
                .WhereIF(!string.IsNullOrEmpty(input.StoreName), x => x.StoreName.Contains(input.StoreName))
                .ToListAsync();

            // 聚合数据：按 StoreId 分组，解决同一门店对应多个事业部的问题
            // 先获取所有涉及的门店，按 StoreId 分组，优先选择有 BusinessUnitId 且数据更多的记录
            var allStoreGroups = currentData.Select(x => new { x.StoreId, x.StoreName, x.BusinessUnitName, x.BusinessUnitId })
                .Union(lastYearData.Select(x => new { x.StoreId, x.StoreName, x.BusinessUnitName, x.BusinessUnitId }))
                .GroupBy(x => x.StoreId)
                .Select(g =>
                {
                    // 优先选择有 BusinessUnitId 的记录，如果都有或都没有，选择第一条
                    var preferred = g.OrderByDescending(x => !string.IsNullOrEmpty(x.BusinessUnitId))
                        .ThenByDescending(x => x.BusinessUnitName)
                        .First();
                    return new { preferred.StoreId, preferred.StoreName, preferred.BusinessUnitName, preferred.BusinessUnitId };
                })
                .OrderBy(x => x.BusinessUnitName)
                .ThenBy(x => x.StoreName)
                .ToList();

            var allStores = allStoreGroups;

            // 批量获取门店开业时间
            var storeIds = allStores.Select(x => x.StoreId).Distinct().ToList();
            var storeOpenTimes = await GetStoreOpenTimesAsync(storeIds);

            var output = new BusinessUnitSummaryOutput();

            foreach (var store in allStores)
            {
                // Current Year Sums
                var curr = currentData.Where(x => x.StoreId == store.StoreId).ToList();
                var last = lastYearData.Where(x => x.StoreId == store.StoreId).ToList();

                // 优先使用BusinessUnitId，如果没有则使用BusinessUnitName
                var buId = store.BusinessUnitId ?? store.BusinessUnitName;
                var buDisplayName = GetBusinessUnitDisplayName(buId, buNameMap);

                // 获取门店开业时间
                var openTime = storeOpenTimes.ContainsKey(store.StoreId) ? storeOpenTimes[store.StoreId] : null;

                var row = new BusinessUnitSummaryRow
                {
                    BusinessUnitName = buDisplayName,
                    StoreName = store.StoreName,

                    CurrentYearPerformance = curr.Sum(x => x.TotalPerformance),
                    LastYearPerformance = last.Sum(x => x.TotalPerformance),

                    CurrentYearConsume = curr.Sum(x => x.TotalConsume),
                    LastYearConsume = last.Sum(x => x.TotalConsume),

                    CurrentYearHeadCount = curr.Sum(x => x.HeadCount),
                    LastYearHeadCount = last.Sum(x => x.HeadCount),

                    CurrentYearPersonTime = curr.Sum(x => x.PersonTime),
                    LastYearPersonTime = last.Sum(x => x.PersonTime),

                    CurrentYearProjectCount = curr.Sum(x => x.ProjectCount),
                    LastYearProjectCount = last.Sum(x => x.ProjectCount),
                };

                // 使用新的增长率计算方法（基于月均，考虑实际营业月份）
                // 对于单个门店，需要考虑开业时间
                row.PerformanceGrowthRate = CalculateGrowthRateByAverage(curr, last, openTime, year, x => x.TotalPerformance);
                row.ConsumeGrowthRate = CalculateGrowthRateByAverage(curr, last, openTime, year, x => x.TotalConsume);
                row.HeadCountGrowthRate = CalculateGrowthRateByAverage(curr, last, openTime, year, x => x.HeadCount);
                row.PersonTimeGrowthRate = CalculateGrowthRateByAverage(curr, last, openTime, year, x => x.PersonTime);
                row.ProjectCountGrowthRate = CalculateGrowthRateByAverage(curr, last, openTime, year, x => x.ProjectCount);

                output.Rows.Add(row);
            }

            return new
            {
                list = output.Rows.Select(x => new
                {
                    businessUnitName = x.BusinessUnitName,
                    storeName = x.StoreName,
                    currentPerformance = x.CurrentYearPerformance,
                    lastPerformance = x.LastYearPerformance,
                    performanceGrowthRate = x.PerformanceGrowthRate,
                    currentConsume = x.CurrentYearConsume,
                    lastConsume = x.LastYearConsume,
                    consumeGrowthRate = x.ConsumeGrowthRate,
                    currentHeadCount = x.CurrentYearHeadCount,
                    lastHeadCount = x.LastYearHeadCount,
                    headCountGrowthRate = x.HeadCountGrowthRate,
                    currentPersonTime = x.CurrentYearPersonTime,
                    lastPersonTime = x.LastYearPersonTime,
                    personTimeGrowthRate = x.PersonTimeGrowthRate,
                    currentProjectCount = x.CurrentYearProjectCount,
                    lastProjectCount = x.LastYearProjectCount,
                    projectCountGrowthRate = x.ProjectCountGrowthRate
                }).ToList()
            };
        }

        #endregion

        #region 私有辅助方法

        private async Task<dynamic> GetMonthlyStat(AnnualSummaryQueryInput input, Func<LqAnnualSummaryEntity, decimal> fieldSelector)
        {
            int year = input.Year ?? DateTime.Now.Year;

            // 获取事业部名称映射
            var buNameMap = await GetBusinessUnitNameMapAsync();

            // 获取本年度数据
            var currentYearData = await _db.Queryable<LqAnnualSummaryEntity>()
                .Where(x => x.IsEffective == 1 && x.Year == year)
                .WhereIF(!string.IsNullOrEmpty(input.StoreName), x => x.StoreName.Contains(input.StoreName))
                .ToListAsync();

            // 获取上年度数据 (用于计算同比/增长率)
            var lastYearData = await _db.Queryable<LqAnnualSummaryEntity>()
                .Where(x => x.IsEffective == 1 && x.Year == year - 1)
                .WhereIF(!string.IsNullOrEmpty(input.StoreName), x => x.StoreName.Contains(input.StoreName))
                .ToListAsync();

            // 整理所有门店 (包括今年有数据和去年有数据的)
            // 按门店ID去重，确保每个门店只出现一次
            // 如果同一门店有多个事业部，取第一个（通常应该只有一个）
            var allStores = currentYearData
                .GroupBy(x => x.StoreId)
                .Select(g => new
                {
                    StoreId = g.Key,
                    StoreName = g.First().StoreName,
                    BusinessUnitName = g.First().BusinessUnitName,
                    BusinessUnitId = g.First().BusinessUnitId
                })
                .OrderBy(x => x.BusinessUnitName)
                .ThenBy(x => x.StoreName)
                .ToList();

            // 批量获取门店开业时间
            var storeIds = allStores.Select(x => x.StoreId).Distinct().ToList();
            var storeOpenTimes = await GetStoreOpenTimesAsync(storeIds);

            var output = new AnnualMonthlyStatOutput();

            foreach (var store in allStores)
            {
                // 优先使用BusinessUnitId，如果没有则使用BusinessUnitName
                var buId = store.BusinessUnitId ?? store.BusinessUnitName;
                var buDisplayName = GetBusinessUnitDisplayName(buId, buNameMap);

                var row = new MonthlyDataRow
                {
                    BusinessUnitName = buDisplayName,
                    StoreName = store.StoreName
                };

                var storeCurrData = currentYearData.Where(x => x.StoreId == store.StoreId).ToList();
                var storeLastData = lastYearData.Where(x => x.StoreId == store.StoreId).ToList();

                // 获取门店开业时间
                var openTime = storeOpenTimes.ContainsKey(store.StoreId) ? storeOpenTimes[store.StoreId] : null;

                // Fill months (Current Year) - 优化：避免重复调用 FirstOrDefault
                for (int month = 1; month <= 12; month++)
                {
                    var currMonthData = storeCurrData.FirstOrDefault(x => x.Month == month);
                    var lastMonthData = storeLastData.FirstOrDefault(x => x.Month == month);

                    switch (month)
                    {
                        case 1: row.Month1 = currMonthData != null ? fieldSelector(currMonthData) : 0; row.LastMonth1 = lastMonthData != null ? fieldSelector(lastMonthData) : 0; break;
                        case 2: row.Month2 = currMonthData != null ? fieldSelector(currMonthData) : 0; row.LastMonth2 = lastMonthData != null ? fieldSelector(lastMonthData) : 0; break;
                        case 3: row.Month3 = currMonthData != null ? fieldSelector(currMonthData) : 0; row.LastMonth3 = lastMonthData != null ? fieldSelector(lastMonthData) : 0; break;
                        case 4: row.Month4 = currMonthData != null ? fieldSelector(currMonthData) : 0; row.LastMonth4 = lastMonthData != null ? fieldSelector(lastMonthData) : 0; break;
                        case 5: row.Month5 = currMonthData != null ? fieldSelector(currMonthData) : 0; row.LastMonth5 = lastMonthData != null ? fieldSelector(lastMonthData) : 0; break;
                        case 6: row.Month6 = currMonthData != null ? fieldSelector(currMonthData) : 0; row.LastMonth6 = lastMonthData != null ? fieldSelector(lastMonthData) : 0; break;
                        case 7: row.Month7 = currMonthData != null ? fieldSelector(currMonthData) : 0; row.LastMonth7 = lastMonthData != null ? fieldSelector(lastMonthData) : 0; break;
                        case 8: row.Month8 = currMonthData != null ? fieldSelector(currMonthData) : 0; row.LastMonth8 = lastMonthData != null ? fieldSelector(lastMonthData) : 0; break;
                        case 9: row.Month9 = currMonthData != null ? fieldSelector(currMonthData) : 0; row.LastMonth9 = lastMonthData != null ? fieldSelector(lastMonthData) : 0; break;
                        case 10: row.Month10 = currMonthData != null ? fieldSelector(currMonthData) : 0; row.LastMonth10 = lastMonthData != null ? fieldSelector(lastMonthData) : 0; break;
                        case 11: row.Month11 = currMonthData != null ? fieldSelector(currMonthData) : 0; row.LastMonth11 = lastMonthData != null ? fieldSelector(lastMonthData) : 0; break;
                        case 12: row.Month12 = currMonthData != null ? fieldSelector(currMonthData) : 0; row.LastMonth12 = lastMonthData != null ? fieldSelector(lastMonthData) : 0; break;
                    }
                }

                row.TotalCurrentYear = storeCurrData.Sum(x => fieldSelector(x));
                // 使用新的月均计算方法（考虑开业时间）
                row.AvgCurrentYear = CalculateMonthlyAverage(storeCurrData, openTime, year, fieldSelector);

                row.TotalLastYear = storeLastData.Sum(x => fieldSelector(x));
                // 使用新的月均计算方法（考虑开业时间）
                row.AvgLastYear = CalculateMonthlyAverage(storeLastData, openTime, year - 1, fieldSelector);

                // 使用新的增长率计算方法（基于月均）
                row.GrowthRate = CalculateGrowthRateByAverage(storeCurrData, storeLastData, openTime, year, fieldSelector);

                output.Rows.Add(row);
            }

            return new
            {
                monthColumns = output.MonthColumns.Select(c => c.Substring(0, 1).ToLower() + c.Substring(1)).ToList(),
                rows = output.Rows.Select(x => new
                {
                    businessUnitName = x.BusinessUnitName,
                    storeName = x.StoreName,
                    month1 = x.Month1,
                    month2 = x.Month2,
                    month3 = x.Month3,
                    month4 = x.Month4,
                    month5 = x.Month5,
                    month6 = x.Month6,
                    month7 = x.Month7,
                    month8 = x.Month8,
                    month9 = x.Month9,
                    month10 = x.Month10,
                    month11 = x.Month11,
                    month12 = x.Month12,
                    lastMonth1 = x.LastMonth1,
                    lastMonth2 = x.LastMonth2,
                    lastMonth3 = x.LastMonth3,
                    lastMonth4 = x.LastMonth4,
                    lastMonth5 = x.LastMonth5,
                    lastMonth6 = x.LastMonth6,
                    lastMonth7 = x.LastMonth7,
                    lastMonth8 = x.LastMonth8,
                    lastMonth9 = x.LastMonth9,
                    lastMonth10 = x.LastMonth10,
                    lastMonth11 = x.LastMonth11,
                    lastMonth12 = x.LastMonth12,
                    totalCurrentYear = x.TotalCurrentYear,
                    avgCurrentYear = x.AvgCurrentYear,
                    totalLastYear = x.TotalLastYear,
                    avgLastYear = x.AvgLastYear,
                    growthRate = x.GrowthRate
                }).ToList()
            };
        }

        private async Task<dynamic> GetIndicatorStat(AnnualSummaryQueryInput input, Func<LqAnnualSummaryEntity, decimal> fieldSelector)
        {
            int year = input.Year ?? DateTime.Now.Year;

            // 获取事业部名称映射
            var buNameMap = await GetBusinessUnitNameMapAsync();

            var currentData = await _db.Queryable<LqAnnualSummaryEntity>()
                .Where(x => x.IsEffective == 1 && x.Year == year)
                .WhereIF(!string.IsNullOrEmpty(input.StoreName), x => x.StoreName.Contains(input.StoreName))
                .ToListAsync();

            var lastData = await _db.Queryable<LqAnnualSummaryEntity>()
                .Where(x => x.IsEffective == 1 && x.Year == year - 1)
                .WhereIF(!string.IsNullOrEmpty(input.StoreName), x => x.StoreName.Contains(input.StoreName))
                .ToListAsync();

            // 按门店ID去重，确保每个门店只出现一次
            // 如果同一门店有多个事业部，取第一个（通常应该只有一个）
            var allStores = currentData
                .GroupBy(x => x.StoreId)
                .Select(g => new
                {
                    StoreId = g.Key,
                    StoreName = g.First().StoreName,
                    BusinessUnitName = g.First().BusinessUnitName,
                    BusinessUnitId = g.First().BusinessUnitId
                })
                .OrderBy(x => x.BusinessUnitName)
                .ThenBy(x => x.StoreName)
                .ToList();

            // 批量获取门店开业时间
            var storeIds = allStores.Select(x => x.StoreId).Distinct().ToList();
            var storeOpenTimes = await GetStoreOpenTimesAsync(storeIds);

            var output = new IndicatorStatOutput();
            foreach (var store in allStores)
            {
                // 按门店ID汇总所有月份的数据
                var storeCurrData = currentData.Where(x => x.StoreId == store.StoreId).ToList();
                var storeLastData = lastData.Where(x => x.StoreId == store.StoreId).ToList();

                var curVal = storeCurrData.Sum(x => fieldSelector(x));
                var lastVal = storeLastData.Sum(x => fieldSelector(x));

                // 优先使用BusinessUnitId，如果没有则使用BusinessUnitName
                var buId = store.BusinessUnitId ?? store.BusinessUnitName;
                var buDisplayName = GetBusinessUnitDisplayName(buId, buNameMap);

                // 获取门店开业时间
                var openTime = storeOpenTimes.ContainsKey(store.StoreId) ? storeOpenTimes[store.StoreId] : null;

                // 使用新的增长率计算方法（基于月均）
                var growthRate = CalculateGrowthRateByAverage(storeCurrData, storeLastData, openTime, year, fieldSelector);

                output.Rows.Add(new IndicatorDataRow
                {
                    BusinessUnitName = buDisplayName,
                    StoreName = store.StoreName,
                    CurrentYearValue = curVal,
                    LastYearValue = lastVal,
                    GrowthRate = growthRate
                });
            }
            return new
            {
                rows = output.Rows.Select(x => new
                {
                    businessUnitName = x.BusinessUnitName,
                    storeName = x.StoreName,
                    currentYearValue = x.CurrentYearValue,
                    lastYearValue = x.LastYearValue,
                    growthRate = x.GrowthRate
                }).ToList()
            };
        }

        private async Task<dynamic> GetBuIndicatorStat(AnnualSummaryQueryInput input, Func<LqAnnualSummaryEntity, decimal> fieldSelector)
        {
            int year = input.Year ?? DateTime.Now.Year;

            // 获取所有事业部（事业一部到事业六部）
            var allBusinessUnits = await _db.Queryable<OrganizeEntity>()
                .Where(x => x.Category == "department" && (x.DeleteMark == null || x.DeleteMark != 1))
                .Where(x => x.FullName.Contains("事业") && x.FullName != "事业部")
                .OrderBy(x => x.FullName)
                .ToListAsync();

            // 获取本年度数据
            var currentData = await _db.Queryable<LqAnnualSummaryEntity>()
                .Where(x => x.IsEffective == 1 && x.Year == year)
                .WhereIF(!string.IsNullOrEmpty(input.StoreName), x => x.StoreName.Contains(input.StoreName))
                .ToListAsync();

            // 获取上年度数据
            var lastData = await _db.Queryable<LqAnnualSummaryEntity>()
                .Where(x => x.IsEffective == 1 && x.Year == year - 1)
                .WhereIF(!string.IsNullOrEmpty(input.StoreName), x => x.StoreName.Contains(input.StoreName))
                .ToListAsync();

            // 创建事业部ID到名称的映射
            var buIdToNameMap = allBusinessUnits.ToDictionary(x => x.Id, x => x.FullName);

            // 获取年度汇总数据中有数据的事业部ID（从BusinessUnitId或BusinessUnitName中获取）
            var dataBuIds = currentData
                .Where(x => !string.IsNullOrEmpty(x.BusinessUnitId))
                .Select(x => x.BusinessUnitId)
                .Distinct()
                .ToList();

            // 如果BusinessUnitId为空，尝试从BusinessUnitName中解析（可能是ID）
            var dataBuNames = currentData
                .Where(x => string.IsNullOrEmpty(x.BusinessUnitId) && !string.IsNullOrEmpty(x.BusinessUnitName))
                .Select(x => x.BusinessUnitName)
                .Distinct()
                .ToList();

            // 合并所有有数据的事业部ID
            var allDataBuIds = dataBuIds.Union(dataBuNames).Distinct().ToList();

            var output = new IndicatorStatOutput();

            // 遍历所有事业部（确保显示所有6个事业部）
            foreach (var bu in allBusinessUnits)
            {
                // 查找该事业部的数据（通过BusinessUnitId或BusinessUnitName匹配）
                var buCurrData = currentData
                    .Where(x => (x.BusinessUnitId == bu.Id || x.BusinessUnitName == bu.Id || x.BusinessUnitName == bu.FullName))
                    .ToList();

                var buLastData = lastData
                    .Where(x => (x.BusinessUnitId == bu.Id || x.BusinessUnitName == bu.Id || x.BusinessUnitName == bu.FullName))
                    .ToList();

                var curVal = buCurrData.Sum(x => fieldSelector(x));
                var lastVal = buLastData.Sum(x => fieldSelector(x));

                // 对于事业部汇总，使用汇总数据的增长率计算方法（基于实际有数据的月份）
                var growthRate = CalculateAggregateGrowthRateByAverage(buCurrData, buLastData, year, fieldSelector);

                output.Rows.Add(new IndicatorDataRow
                {
                    BusinessUnitName = bu.FullName, // 使用事业部名称而不是ID
                    StoreName = "汇总",
                    CurrentYearValue = curVal,
                    LastYearValue = lastVal,
                    GrowthRate = growthRate
                });
            }

            return new
            {
                rows = output.Rows.Select(x => new
                {
                    businessUnitName = x.BusinessUnitName,
                    storeName = x.StoreName,
                    currentYearValue = x.CurrentYearValue,
                    lastYearValue = x.LastYearValue,
                    growthRate = x.GrowthRate
                }).ToList()
            };
        }

        private string CalculateGrowthRate(decimal current, decimal last)
        {
            if (last == 0) return current > 0 ? "100%" : "0%";
            var rate = (current - last) / last;
            return (rate * 100).ToString("F2") + "%";
        }

        /// <summary>
        /// 批量获取门店开业时间
        /// </summary>
        private async Task<Dictionary<string, DateTime?>> GetStoreOpenTimesAsync(List<string> storeIds)
        {
            if (storeIds == null || !storeIds.Any())
                return new Dictionary<string, DateTime?>();

            var stores = await _db.Queryable<LqMdxxEntity>()
                .Where(x => storeIds.Contains(x.Id))
                .Select(x => new { x.Id, x.Kysj })
                .ToListAsync();

            return stores.ToDictionary(x => x.Id, x => x.Kysj);
        }

        /// <summary>
        /// 计算月均（考虑开业时间和实际有数据的月份）
        /// </summary>
        private decimal CalculateMonthlyAverage(
            List<LqAnnualSummaryEntity> data,
            DateTime? openTime,
            int year,
            Func<LqAnnualSummaryEntity, decimal> fieldSelector)
        {
            if (data == null || !data.Any()) return 0;

            // 获取实际有数据的月份（排除值为0或null的数据）
            var validData = data.Where(x => fieldSelector(x) > 0).ToList();
            if (!validData.Any()) return 0;

            // 如果有开业时间且是开业当年，排除开业当月
            if (openTime != null && openTime.Value.Year == year)
            {
                int openMonth = openTime.Value.Month;
                validData = validData.Where(x => x.Month > openMonth).ToList();
                if (!validData.Any()) return 0;
            }

            // 统计实际有数据的月份数（去重）
            int actualMonthCount = validData.Select(x => x.Month).Distinct().Count();
            if (actualMonthCount == 0) return 0;

            decimal total = validData.Sum(x => fieldSelector(x));
            return total / actualMonthCount;
        }

        /// <summary>
        /// 计算汇总数据的月均（用于事业部汇总，考虑所有门店的实际营业月份）
        /// </summary>
        private decimal CalculateAggregateMonthlyAverage(
            List<LqAnnualSummaryEntity> data,
            Func<LqAnnualSummaryEntity, decimal> fieldSelector)
        {
            if (data == null || !data.Any()) return 0;

            // 获取实际有数据的月份（排除值为0或null的数据）
            var validData = data.Where(x => fieldSelector(x) > 0).ToList();
            if (!validData.Any()) return 0;

            // 统计实际有数据的月份数（去重，因为可能有多个门店在同一个月有数据）
            int actualMonthCount = validData.Select(x => x.Month).Distinct().Count();
            if (actualMonthCount == 0) return 0;

            decimal total = validData.Sum(x => fieldSelector(x));
            return total / actualMonthCount;
        }

        /// <summary>
        /// 计算增长率（基于月均，考虑开业时间）
        /// </summary>
        private string CalculateGrowthRateByAverage(
            List<LqAnnualSummaryEntity> currentData,
            List<LqAnnualSummaryEntity> lastData,
            DateTime? openTime,
            int currentYear,
            Func<LqAnnualSummaryEntity, decimal> fieldSelector)
        {
            // 25年新开门店，增长率 = 0
            if (openTime != null && openTime.Value.Year == 2025)
            {
                return "0%";
            }

            // 计算本年月均
            decimal currentAvg = CalculateMonthlyAverage(currentData, openTime, currentYear, fieldSelector);

            // 计算上年月均
            decimal lastAvg = CalculateMonthlyAverage(lastData, openTime, currentYear - 1, fieldSelector);

            if (lastAvg == 0) return currentAvg > 0 ? "100%" : "0%";

            var rate = (currentAvg - lastAvg) / lastAvg;
            return (rate * 100).ToString("F2") + "%";
        }

        /// <summary>
        /// 计算汇总数据的增长率（基于月均，用于事业部汇总）
        /// </summary>
        private string CalculateAggregateGrowthRateByAverage(
            List<LqAnnualSummaryEntity> currentData,
            List<LqAnnualSummaryEntity> lastData,
            int currentYear,
            Func<LqAnnualSummaryEntity, decimal> fieldSelector)
        {
            // 计算本年月均（基于实际有数据的月份）
            decimal currentAvg = CalculateAggregateMonthlyAverage(currentData, fieldSelector);

            // 计算上年月均（基于实际有数据的月份）
            decimal lastAvg = CalculateAggregateMonthlyAverage(lastData, fieldSelector);

            if (lastAvg == 0) return currentAvg > 0 ? "100%" : "0%";

            var rate = (currentAvg - lastAvg) / lastAvg;
            return (rate * 100).ToString("F2") + "%";
        }

        /// <summary>
        /// 获取事业部ID到名称的映射
        /// </summary>
        private async Task<Dictionary<string, string>> GetBusinessUnitNameMapAsync()
        {
            var orgs = await _db.Queryable<OrganizeEntity>()
                .Where(x => x.Category == "department" && (x.DeleteMark == null || x.DeleteMark != 1))
                .Select(x => new { x.Id, x.FullName })
                .ToListAsync();

            var map = new Dictionary<string, string>();
            foreach (var org in orgs)
            {
                map[org.Id] = org.FullName;
                // 如果名称本身也是ID格式，也添加映射
                if (!map.ContainsKey(org.FullName))
                {
                    map[org.FullName] = org.FullName;
                }
            }
            return map;
        }

        /// <summary>
        /// 将事业部ID或名称转换为显示名称
        /// </summary>
        private string GetBusinessUnitDisplayName(string buIdOrName, Dictionary<string, string> buNameMap)
        {
            if (string.IsNullOrEmpty(buIdOrName))
                return "未知";

            // 如果映射中存在，返回名称
            if (buNameMap.ContainsKey(buIdOrName))
                return buNameMap[buIdOrName];

            // 如果本身就是名称（包含"事业"或"科技"等），直接返回
            if (buIdOrName.Contains("事业") || buIdOrName.Contains("科技") || buIdOrName.Contains("教育") || buIdOrName.Contains("大项目"))
                return buIdOrName;

            // 否则返回原值（可能是ID）
            return buIdOrName;
        }

        /// <summary>
        /// 获取Excel单元格的值（支持公式计算值）
        /// </summary>
        /// <param name="cell">单元格</param>
        /// <returns>单元格的值（字符串形式）</returns>
        private string GetCellValue(ICell cell)
        {
            if (cell == null) return string.Empty;

            try
            {
                // 如果是公式类型，先尝试获取计算后的值
                if (cell.CellType == CellType.Formula)
                {
                    try
                    {
                        var formulaEvaluator = cell.Sheet.Workbook.GetCreationHelper().CreateFormulaEvaluator();
                        var cellValue = formulaEvaluator.Evaluate(cell);

                        // 根据计算结果类型返回相应的值
                        switch (cellValue.CellType)
                        {
                            case CellType.String:
                                return cellValue.StringValue?.Trim() ?? string.Empty;
                            case CellType.Numeric:
                                if (DateUtil.IsCellDateFormatted(cell))
                                {
                                    return cellValue.NumberValue.ToString("yyyy/MM/dd");
                                }
                                var numValue = cellValue.NumberValue;
                                if (numValue == Math.Floor(numValue))
                                {
                                    return ((long)numValue).ToString();
                                }
                                return numValue.ToString();
                            case CellType.Boolean:
                                return cellValue.BooleanValue.ToString();
                            case CellType.Error:
                                // 公式计算错误，返回空字符串
                                return string.Empty;
                            default:
                                return string.Empty;
                        }
                    }
                    catch
                    {
                        // 如果公式计算失败，尝试读取公式文本
                        // 如果包含公式特征，返回空字符串（会被后续逻辑跳过）
                        var formulaText = cell.CellFormula;
                        if (!string.IsNullOrEmpty(formulaText) &&
                            (formulaText.Contains("XLOOKUP") || formulaText.Contains("VLOOKUP") ||
                             formulaText.Contains("_xlfn.") || formulaText.StartsWith("=")))
                        {
                            return string.Empty;
                        }
                        return string.Empty;
                    }
                }

                // 非公式类型，直接读取值
                switch (cell.CellType)
                {
                    case CellType.String:
                        return cell.StringCellValue?.Trim() ?? string.Empty;

                    case CellType.Numeric:
                        if (DateUtil.IsCellDateFormatted(cell))
                        {
                            return cell.DateCellValue.ToString("yyyy/MM/dd");
                        }
                        // 处理数字，避免科学计数法
                        var numericValue = cell.NumericCellValue;
                        if (numericValue == Math.Floor(numericValue))
                        {
                            return ((long)numericValue).ToString();
                        }
                        return numericValue.ToString();

                    case CellType.Boolean:
                        return cell.BooleanCellValue.ToString();

                    case CellType.Blank:
                        return string.Empty;

                    default:
                        return cell.ToString()?.Trim() ?? string.Empty;
                }
            }
            catch
            {
                return string.Empty;
            }
        }

        #endregion
    }
}