netcore/src/Modularity/Extend/NCC.Extend/LqCooperationCostService.cs

using NCC.Common.Core.Manager;
using NCC.Common.Enum;
using NCC.Common.Extension;
using NCC.Common.Filter;
using NCC.Dependency;
using NCC.DynamicApiController;
using NCC.FriendlyException;
using NCC.Extend.Interfaces.LqCooperationCost;
using Mapster;
using Microsoft.AspNetCore.Mvc;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using NCC.Extend.Entitys.lq_cooperation_cost;
using NCC.Extend.Entitys.Dto.LqCooperationCost;
using NCC.Extend.Entitys.lq_mdxx;
using Yitter.IdGenerator;
using NCC.Common.Helper;
using NCC.Common.Model.NPOI;
using NCC.Common.Configuration;
using NCC.DataEncryption;
using NCC.ClayObject;
using NCC.Common.Const;
using NCC.Extend.Entitys.Enum;
using Microsoft.AspNetCore.Http;
using System.Globalization;
using System.IO;
using System.Data;
namespace NCC.Extend.LqCooperationCost
{
    /// <summary>
    /// 合作成本表服务
    /// </summary>
    [ApiDescriptionSettings(Tag = "Extend", Name = "LqCooperationCost", Order = 200)]
    [Route("api/Extend/[controller]")]
    public class LqCooperationCostService : ILqCooperationCostService, IDynamicApiController, ITransient
    {
        private readonly ISqlSugarRepository<LqCooperationCostEntity> _repository;
        private readonly SqlSugarScope _db;
        private readonly IUserManager _userManager;
        /// <summary>
        /// 初始化一个<see cref="LqCooperationCostService"/>类型的新实例
        /// </summary>
        public LqCooperationCostService(
            ISqlSugarRepository<LqCooperationCostEntity> repository,
            IUserManager userManager)
        {
            _repository = repository;
            _db = _repository.Context;
            _userManager = userManager;
        }
        /// <summary>
        /// 获取合作成本表详情
        /// </summary>
        /// <param name="id">主键ID</param>
        /// <returns></returns>
        [HttpGet("{id}")]
        public async Task<dynamic> GetInfo(string id)
        {
            var entity = await _db.Queryable<LqCooperationCostEntity>()
                .Where(x => x.Id == id && x.IsEffective == StatusEnum.有效.GetHashCode())
                .FirstAsync();
            _ = entity ?? throw NCCException.Oh(ErrorCode.COM1005);
            var output = entity.Adapt<LqCooperationCostInfoOutput>();
            return output;
        }
        /// <summary>
        /// 获取合作成本表列表
        /// </summary>
        /// <param name="input">请求参数</param>
        /// <returns></returns>
        [HttpGet("")]
        public async Task<dynamic> GetList([FromQuery] LqCooperationCostListQueryInput input)
        {
            var sidx = input.sidx ?? "CreateTime";
            var sortType = input.sort?.ToLower() == "asc" ? OrderByType.Asc : OrderByType.Desc;
            var query = _db.Queryable<LqCooperationCostEntity>()
                .Where(x => x.IsEffective == StatusEnum.有效.GetHashCode())
                .WhereIF(!string.IsNullOrEmpty(input.storeId), x => x.StoreId == input.storeId)
                .WhereIF(!string.IsNullOrEmpty(input.storeName), x => x.StoreName.Contains(input.storeName))
                .WhereIF(input.year.HasValue, x => x.Year == input.year.Value)
                .WhereIF(!string.IsNullOrEmpty(input.month), x => x.Month == input.month);
            // 处理排序
            switch (sidx.ToLower())
            {
                case "id":
                    query = query.OrderBy(x => x.Id, sortType);
                    break;
                case "createtime":
                    query = query.OrderBy(x => x.CreateTime, sortType);
                    break;
                case "storename":
                    query = query.OrderBy(x => x.StoreName, sortType);
                    break;
                case "year":
                    query = query.OrderBy(x => x.Year, sortType);
                    break;
                case "month":
                    query = query.OrderBy(x => x.Month, sortType);
                    break;
                default:
                    query = query.OrderBy(x => x.CreateTime, OrderByType.Desc);
                    break;
            }
            var total = await query.CountAsync();
            var list = await query.ToPageListAsync(input.currentPage, input.pageSize);
            var result = list.Select(x => new LqCooperationCostListOutput
            {
                id = x.Id,
                storeId = x.StoreId,
                storeName = x.StoreName,
                year = x.Year,
                month = x.Month,
                totalAmount = x.TotalAmount,
                remarks = x.Remarks,
                costType = x.CostType,
                createUser = x.CreateUser,
                createTime = x.CreateTime,
                updateUser = x.UpdateUser,
                updateTime = x.UpdateTime
            }).ToList();
            return PageResult<LqCooperationCostListOutput>.SqlSugarPageResult(
                new SqlSugarPagedList<LqCooperationCostListOutput>
                {
                    list = result,
                    pagination = new PagedModel { PageIndex = input.currentPage, PageSize = input.pageSize, Total = total }
                });
        }
        /// <summary>
        /// 获取合作成本表无分页列表
        /// </summary>
        /// <param name="input">请求参数</param>
        /// <returns></returns>
        [HttpGet("Actions/GetNoPagingList")]
        public async Task<List<LqCooperationCostListOutput>> GetNoPagingList([FromQuery] LqCooperationCostListQueryInput input)
        {
            var sidx = input.sidx ?? "CreateTime";
            var sortType = input.sort?.ToLower() == "asc" ? OrderByType.Asc : OrderByType.Desc;
            var query = _db.Queryable<LqCooperationCostEntity>()
                .Where(x => x.IsEffective == StatusEnum.有效.GetHashCode())
                .WhereIF(!string.IsNullOrEmpty(input.storeId), x => x.StoreId == input.storeId)
                .WhereIF(!string.IsNullOrEmpty(input.storeName), x => x.StoreName.Contains(input.storeName))
                .WhereIF(input.year.HasValue, x => x.Year == input.year.Value)
                .WhereIF(!string.IsNullOrEmpty(input.month), x => x.Month == input.month);
            // 处理排序
            switch (sidx.ToLower())
            {
                case "id":
                    query = query.OrderBy(x => x.Id, sortType);
                    break;
                case "createtime":
                    query = query.OrderBy(x => x.CreateTime, sortType);
                    break;
                case "storename":
                    query = query.OrderBy(x => x.StoreName, sortType);
                    break;
                case "year":
                    query = query.OrderBy(x => x.Year, sortType);
                    break;
                case "month":
                    query = query.OrderBy(x => x.Month, sortType);
                    break;
                default:
                    query = query.OrderBy(x => x.CreateTime, OrderByType.Desc);
                    break;
            }
            var list = await query
                .Select(x => new LqCooperationCostListOutput
                {
                    id = x.Id,
                    storeId = x.StoreId,
                    storeName = x.StoreName,
                    year = x.Year,
                    month = x.Month,
                    totalAmount = x.TotalAmount,
                    remarks = x.Remarks,
                    costType = x.CostType,
                    createUser = x.CreateUser,
                    createTime = x.CreateTime,
                    updateUser = x.UpdateUser,
                    updateTime = x.UpdateTime
                })
                .ToListAsync();
            return list;
        }
        /// <summary>
        /// 创建合作成本表
        /// </summary>
        /// <param name="input">参数</param>
        /// <returns></returns>
        [HttpPost("")]
        public async Task Create([FromBody] LqCooperationCostCrInput input)
        {
            var userInfo = await _userManager.GetUserInfo();
            var entity = input.Adapt<LqCooperationCostEntity>();
            entity.Id = YitIdHelper.NextId().ToString();
            entity.IsEffective = StatusEnum.有效.GetHashCode();
            entity.CreateUser = _userManager.UserId;
            entity.CreateTime = DateTime.Now;
            // 如果未提供门店名称，根据门店ID查询
            if (string.IsNullOrEmpty(entity.StoreName) && !string.IsNullOrEmpty(entity.StoreId))
            {
                var store = await _db.Queryable<LqMdxxEntity>()
                    .Where(x => x.Id == entity.StoreId)
                    .Select(x => x.Dm)
                    .FirstAsync();
                entity.StoreName = store;
            }
            var isOk = await _db.Insertable(entity).ExecuteCommandAsync();
            if (!(isOk > 0)) throw NCCException.Oh(ErrorCode.COM1000);
        }
        /// <summary>
        /// 更新合作成本表
        /// </summary>
        /// <param name="id">主键</param>
        /// <param name="input">参数</param>
        /// <returns></returns>
        [HttpPut("{id}")]
        public async Task Update(string id, [FromBody] LqCooperationCostUpInput input)
        {
            var entity = await _db.Queryable<LqCooperationCostEntity>()
                .Where(x => x.Id == id && x.IsEffective == StatusEnum.有效.GetHashCode())
                .FirstAsync();
            _ = entity ?? throw NCCException.Oh(ErrorCode.COM1005);
            entity.StoreId = input.storeId;
            entity.StoreName = input.storeName;
            entity.Year = input.year;
            entity.Month = input.month;
            entity.TotalAmount = input.totalAmount;
            entity.Remarks = input.remarks;
            entity.CostType = input.costType;
            entity.UpdateUser = _userManager.UserId;
            entity.UpdateTime = DateTime.Now;
            // 如果未提供门店名称，根据门店ID查询
            if (string.IsNullOrEmpty(entity.StoreName) && !string.IsNullOrEmpty(entity.StoreId))
            {
                var store = await _db.Queryable<LqMdxxEntity>()
                    .Where(x => x.Id == entity.StoreId)
                    .Select(x => x.Dm)
                    .FirstAsync();
                entity.StoreName = store;
            }
            var isOk = await _db.Updateable(entity).ExecuteCommandAsync();
            if (!(isOk > 0)) throw NCCException.Oh(ErrorCode.COM1001);
        }
        /// <summary>
        /// 删除合作成本表
        /// </summary>
        /// <param name="id">主键</param>
        /// <returns></returns>
        [HttpDelete("{id}")]
        public async Task Delete(string id)
        {
            var entity = await _db.Queryable<LqCooperationCostEntity>()
                .Where(x => x.Id == id && x.IsEffective == StatusEnum.有效.GetHashCode())
                .FirstAsync();
            _ = entity ?? throw NCCException.Oh(ErrorCode.COM1005);
            // 逻辑删除
            entity.IsEffective = StatusEnum.无效.GetHashCode();
            entity.UpdateUser = _userManager.UserId;
            entity.UpdateTime = DateTime.Now;
            var isOk = await _db.Updateable(entity).ExecuteCommandAsync();
            if (!(isOk > 0)) throw NCCException.Oh(ErrorCode.COM1002);
        }
        /// <summary>
        /// 导出合作成本表
        /// </summary>
        /// <remarks>
        /// 未传入任何筛选参数时，默认导出全部数据；传入 storeId、storeName、year、month 时按条件筛选导出。
        /// </remarks>
        /// <param name="input">请求参数</param>
        /// <returns></returns>
        [HttpGet("Actions/Export")]
        public async Task<dynamic> Export([FromQuery] LqCooperationCostListQueryInput input)
        {
            var userInfo = await _userManager.GetUserInfo();
            // 导出时统一使用无分页列表：未传参数则导出全部，有参数则按条件筛选
            var exportData = await this.GetNoPagingList(input);
            List<ParamsModel> paramList = "[{\"value\":\"门店名称\",\"field\":\"storeName\"},{\"value\":\"年份\",\"field\":\"year\"},{\"value\":\"月份\",\"field\":\"month\"},{\"value\":\"合计金额\",\"field\":\"totalAmount\"},{\"value\":\"成本类型\",\"field\":\"costType\"},{\"value\":\"备注说明\",\"field\":\"remarks\"},{\"value\":\"创建人\",\"field\":\"createUser\"},{\"value\":\"创建时间\",\"field\":\"createTime\"},]".ToList<ParamsModel>();
            ExcelConfig excelconfig = new ExcelConfig();
            excelconfig.FileName = "合作成本表.xls";
            excelconfig.HeadFont = "微软雅黑";
            excelconfig.HeadPoint = 10;
            excelconfig.IsAllSizeColumn = true;
            excelconfig.ColumnModel = new List<ExcelColumnModel>();
            List<string> selectKeyList = !string.IsNullOrEmpty(input.selectKey) ? input.selectKey.Split(',').ToList() : paramList.Select(p => p.field).ToList();
            foreach (var item in selectKeyList)
            {
                var isExist = paramList.Find(p => p.field == item);
                if (isExist != null)
                {
                    excelconfig.ColumnModel.Add(new ExcelColumnModel() { Column = isExist.field, ExcelColumn = isExist.value });
                }
            }
            var addPath = FileVariable.TemporaryFilePath + excelconfig.FileName;
            ExcelExportHelper<LqCooperationCostListOutput>.Export(exportData, excelconfig, addPath);
            var fileName = _userManager.UserId + "|" + addPath + "|xls";
            var output = new
            {
                name = excelconfig.FileName,
                url = "/api/File/Download?encryption=" + DESCEncryption.Encrypt(fileName, "NCC")
            };
            return output;
        }
        /// <summary>
        /// 下载导入模板
        /// </summary>
        /// <remarks>
        /// 下载合作成本表导入模板Excel文件
        /// 
        /// Excel格式：
        /// 第一行为标题行：门店名称、年份、月份、合计金额、成本类型、备注说明
        /// </remarks>
        /// <returns>模板文件下载信息</returns>
        [HttpGet("Actions/TemplateDownload")]
        public dynamic TemplateDownload()
        {
            try
            {
                // 创建模板数据（只有标题行）
                var templateData = new List<Dictionary<string, object>>
                {
                    new Dictionary<string, object>
                    {
                        { "storeName", "" },
                        { "year", "" },
                        { "month", "" },
                        { "totalAmount", "" },
                        { "costType", "" },
                        { "remarks", "" }
                    }
                };
                ExcelConfig excelconfig = new ExcelConfig();
                excelconfig.FileName = "合作成本表模板.xlsx";
                excelconfig.HeadFont = "微软雅黑";
                excelconfig.HeadPoint = 10;
                excelconfig.IsAllSizeColumn = true;
                excelconfig.ColumnModel = new List<ExcelColumnModel>
                {
                    new ExcelColumnModel { Column = "storeName", ExcelColumn = "门店名称" },
                    new ExcelColumnModel { Column = "year", ExcelColumn = "年份" },
                    new ExcelColumnModel { Column = "month", ExcelColumn = "月份" },
                    new ExcelColumnModel { Column = "totalAmount", ExcelColumn = "合计金额" },
                    new ExcelColumnModel { Column = "costType", ExcelColumn = "成本类型" },
                    new ExcelColumnModel { Column = "remarks", ExcelColumn = "备注说明" }
                };
                var addPath = FileVariable.TemporaryFilePath + excelconfig.FileName;
                var columnList = new List<string> { "门店名称", "年份", "月份", "合计金额", "成本类型", "备注说明" };
                ExcelExportHelper<Dictionary<string, object>>.Export(templateData, excelconfig, addPath, columnList);
                var fileName = _userManager.UserId + "|" + addPath + "|xlsx";
                return new
                {
                    name = excelconfig.FileName,
                    url = "/api/File/Download?encryption=" + DESCEncryption.Encrypt(fileName, "NCC")
                };
            }
            catch (Exception ex)
            {
                throw NCCException.Oh($"生成模板失败：{ex.Message}");
            }
        }
        /// <summary>
        /// 导入合作成本数据
        /// </summary>
        /// <remarks>
        /// 从Excel文件导入合作成本数据
        /// 
        /// Excel格式要求：
        /// 第一行为标题行：门店名称、年份、月份、合计金额、成本类型、备注说明
        /// 从第二行开始为数据行
        /// 
        /// 合计金额支持正数和负数，负数可用于冲减、退款等场景；
        /// 支持格式：-100、-100.50、会计格式(100)等
        /// 
        /// 注意：导入时通过门店名称查找门店ID，不需要填写门店ID
        /// 
        /// 示例请求：
        /// POST /api/Extend/LqCooperationCost/Actions/Import
        /// Content-Type: multipart/form-data
        /// </remarks>
        /// <param name="file">Excel文件</param>
        /// <param name="clearBeforeImport">是否需要清理导入月份数据（默认：true，清理）</param>
        /// <returns>导入结果</returns>
        /// <response code="200">导入成功</response>
        /// <response code="400">文件格式错误或数据验证失败</response>
        [HttpPost("Actions/Import")]
        public async Task<dynamic> Import(IFormFile file, bool clearBeforeImport = true)
        {
            try
            {
                if (file == null || file.Length == 0)
                {
                    throw NCCException.Oh("请选择要上传的Excel文件");
                }
                // 检查文件格式
                var allowedExtensions = new[] { ".xlsx", ".xls" };
                var fileExtension = Path.GetExtension(file.FileName).ToLowerInvariant();
                if (!allowedExtensions.Contains(fileExtension))
                {
                    throw NCCException.Oh("只支持.xlsx和.xls格式的Excel文件");
                }
                var successCount = 0;
                var failCount = 0;
                var failMessages = new List<string>();
                var importData = new List<(string StoreId, int Year, string Month, decimal TotalAmount, string CostType, string Remarks)>();
                // 保存临时文件
                var tempFilePath = Path.Combine(Path.GetTempPath(), Guid.NewGuid().ToString() + Path.GetExtension(file.FileName));
                try
                {
                    using (var stream = new FileStream(tempFilePath, FileMode.Create))
                    {
                        await file.CopyToAsync(stream);
                    }
                    // 使用ExcelImportHelper读取Excel文件（第一行为标题行）
                    var dataTable = ExcelImportHelper.ToDataTable(tempFilePath, 0, 0);
                    if (dataTable.Rows.Count == 0)
                    {
                        throw NCCException.Oh("Excel文件中没有数据行（至少需要标题行和一行数据）");
                    }
                    // ExcelImportHelper 已跳过标题行，从第0行开始即为第一行数据
                    for (int i = 0; i < dataTable.Rows.Count; i++)
                    {
                        try
                        {
                            var row = dataTable.Rows[i];
                            // Excel列顺序：门店名称、年份、月份、合计金额、成本类型、备注说明
                            var storeName = row[0]?.ToString()?.Trim();
                            var yearText = row[1]?.ToString()?.Trim();
                            var monthText = row[2]?.ToString()?.Trim();
                            var totalAmountText = row[3]?.ToString()?.Trim();
                            var costType = row[4]?.ToString()?.Trim();
                            var remarks = row[5]?.ToString()?.Trim();
                            // 跳过空行
                            if (string.IsNullOrEmpty(storeName))
                            {
                                continue;
                            }
                            // 验证必填字段：门店名称
                            if (string.IsNullOrEmpty(storeName))
                            {
                                failMessages.Add($"第{i + 1}行：门店名称不能为空");
                                failCount++;
                                continue;
                            }
                            // 根据门店名称查找门店ID
                            var store = await _db.Queryable<LqMdxxEntity>()
                                .Where(x => x.Dm == storeName)
                                .FirstAsync();
                            if (store == null)
                            {
                                failMessages.Add($"第{i + 1}行：找不到门店名称'{storeName}'对应的门店");
                                failCount++;
                                continue;
                            }
                            var storeId = store.Id;
                            // 验证年份
                            if (string.IsNullOrEmpty(yearText) || !int.TryParse(yearText, out int year))
                            {
                                failMessages.Add($"第{i + 1}行：年份格式错误（应为数字）");
                                failCount++;
                                continue;
                            }
                            // 验证月份
                            if (string.IsNullOrEmpty(monthText) || monthText.Length != 6)
                            {
                                failMessages.Add($"第{i + 1}行：月份格式错误（应为YYYYMM格式，如：202501）");
                                failCount++;
                                continue;
                            }
                            // 验证合计金额（支持正数和负数，如冲减、退款等场景）
                            if (string.IsNullOrEmpty(totalAmountText))
                            {
                                failMessages.Add($"第{i + 1}行：合计金额不能为空");
                                failCount++;
                                continue;
                            }
                            if (!TryParseDecimalAllowNegative(totalAmountText, out decimal totalAmount))
                            {
                                failMessages.Add($"第{i + 1}行：合计金额格式错误（应为数字，支持负数如-100或会计格式(100)）");
                                failCount++;
                                continue;
                            }
                            // 注意：一个门店在同一个月份可以有多笔成本记录（包括相同成本类型、相同金额）
                            // 因为业务上可能需要记录多笔成本，比如多次合作项目、多次设备维护等
                            // 统计时会按门店汇总所有记录的金额，所以不需要做重复检查
                            // 如果用户需要避免重复导入，可以使用"清理导入月份数据"功能
                            // 添加到导入数据列表
                            importData.Add((storeId, year, monthText, totalAmount, costType, remarks));
                            successCount++;
                        }
                        catch (Exception ex)
                        {
                            failMessages.Add($"第{i + 1}行：{ex.Message}");
                            failCount++;
                        }
                    }
                }
                finally
                {
                    // 清理临时文件
                    if (File.Exists(tempFilePath))
                    {
                        File.Delete(tempFilePath);
                    }
                }
                // 如果需要清理导入月份数据，先删除导入数据中所有涉及的年份+月份组合的数据
                if (clearBeforeImport && importData.Any())
                {
                    // 获取导入数据中所有唯一的年份+月份组合
                    var yearMonthPairs = importData
                        .Select(x => new { x.Year, x.Month })
                        .Distinct()
                        .ToList();
                    foreach (var pair in yearMonthPairs)
                    {
                        await _db.Deleteable<LqCooperationCostEntity>()
                            .Where(x => x.Year == pair.Year && x.Month == pair.Month && x.IsEffective == StatusEnum.有效.GetHashCode())
                            .ExecuteCommandAsync();
                    }
                }
                // 批量插入导入数据
                if (importData.Any())
                {
                    // 批量获取门店信息
                    var storeIds = importData.Select(x => x.StoreId).Distinct().ToList();
                    var stores = await _db.Queryable<LqMdxxEntity>()
                        .Where(x => storeIds.Contains(x.Id))
                        .Select(x => new { x.Id, x.Dm })
                        .ToListAsync();
                    var storeDict = stores.ToDictionary(x => x.Id, x => x.Dm);
                    var entities = importData.Select(x => new LqCooperationCostEntity
                    {
                        Id = YitIdHelper.NextId().ToString(),
                        StoreId = x.StoreId,
                        StoreName = storeDict.ContainsKey(x.StoreId) ? storeDict[x.StoreId] : "",
                        Year = x.Year,
                        Month = x.Month,
                        TotalAmount = x.TotalAmount,
                        CostType = x.CostType,
                        Remarks = x.Remarks,
                        IsEffective = StatusEnum.有效.GetHashCode(),
                        CreateUser = _userManager.UserId,
                        CreateTime = DateTime.Now
                    }).ToList();
                    await _db.Insertable(entities).ExecuteCommandAsync();
                }
                // 构建返回结果，包含成功导入的记录的成本类型信息
                var successRecords = importData.Select(x => new
                {
                    storeId = x.StoreId,
                    year = x.Year,
                    month = x.Month,
                    totalAmount = x.TotalAmount,
                    costType = x.CostType,
                    remarks = x.Remarks
                }).ToList();
                return new
                {
                    success = true,
                    message = $"导入完成：成功{successCount}条，失败{failCount}条",
                    successCount = successCount,
                    failCount = failCount,
                    failMessages = failMessages,
                    successRecords = successRecords
                };
            }
            catch (Exception ex)
            {
                throw NCCException.Oh($"导入失败：{ex.Message}");
            }
        }
        /// <summary>
        /// 解析合计金额字符串，支持正数和负数
        /// </summary>
        /// <param name="text">待解析的字符串</param>
        /// <param name="result">解析结果</param>
        /// <returns>是否解析成功</returns>
        /// <remarks>
        /// 支持格式：-100、-100.50、会计格式(100)或（100）、千分位1,234.56等
        /// </remarks>
        private static bool TryParseDecimalAllowNegative(string text, out decimal result)
        {
            result = 0;
            if (string.IsNullOrWhiteSpace(text)) return false;
            var cleaned = text.Trim()
                .Replace(",", "")
                .Replace("，", "")
                .Replace("￥", "")
                .Replace("$", "")
                .Replace("元", "")
                .Replace(" ", "");
            // 会计格式：(100) 或 （100）表示负数
            if (cleaned.StartsWith("(") && cleaned.EndsWith(")"))
            {
                cleaned = "-" + cleaned.Substring(1, cleaned.Length - 2).Trim();
            }
            else if (cleaned.StartsWith("（") && cleaned.EndsWith("）"))
            {
                cleaned = "-" + cleaned.Substring(1, cleaned.Length - 2).Trim();
            }
            return decimal.TryParse(cleaned, NumberStyles.Number, CultureInfo.InvariantCulture, out result);
        }
    }
}