netcore/src/Modularity/Extend/NCC.Extend/LqXmzlService.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.LqXmzl;
using Mapster;
using Microsoft.AspNetCore.Authorization;
using Microsoft.AspNetCore.Mvc;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using NCC.Extend.Entitys.lq_xmzl;
using NCC.Extend.Entitys.Dto.LqXmzl;
using NCC.Extend.Entitys.lq_kd_pxmx;
using NCC.Extend.Entitys.lq_xh_pxmx;
using NCC.Extend.Entitys.lq_hytk_mx;
using NCC.Extend.Entitys.lq_kd_kdjlb;
using NCC.Extend.Entitys.lq_xh_hyhk;
using NCC.Extend.Entitys.lq_hytk_hytk;
using Yitter.IdGenerator;
using NCC.Common.Helper;
using NCC.JsonSerialization;
using NCC.Common.Model.NPOI;
using NCC.Common.Configuration;
using NCC.DataEncryption;
using NCC.ClayObject;
using NCC.Extend.Entitys.Enum;
namespace NCC.Extend.LqXmzl
{
    /// <summary>
    /// 项目资料服务
    /// </summary>
    [ApiDescriptionSettings(Tag = "绿纤品项资料服务", Name = "LqXmzl", Order = 200)]
    [Route("api/Extend/[controller]")]
    public class LqXmzlService : ILqXmzlService, IDynamicApiController, ITransient
    {
        private readonly ISqlSugarRepository<LqXmzlEntity> _lqXmzlRepository;
        private readonly SqlSugarScope _db;
        private readonly IUserManager _userManager;
        /// <summary>
        /// 初始化一个<see cref="LqXmzlService"/>类型的新实例
        /// </summary>
        public LqXmzlService(ISqlSugarRepository<LqXmzlEntity> lqXmzlRepository, IUserManager userManager)
        {
            _lqXmzlRepository = lqXmzlRepository;
            _db = _lqXmzlRepository.Context;
            _userManager = userManager;
        }
        #region 获取品项资料
        /// <summary>
        /// 获取项目资料
        /// </summary>
        /// <param name="id">参数</param>
        /// <returns></returns>
        [HttpGet("{id}")]
        public async Task<dynamic> GetInfo(string id)
        {
            var entity = await _db.Queryable<LqXmzlEntity>().FirstAsync(p => p.Id == id);
            var output = entity.Adapt<LqXmzlInfoOutput>();
            return output;
        }
        #endregion
        #region 获取品项资料列表
        /// <summary>
        /// 获取品项资料列表
        /// </summary>
        /// <param name="input">请求参数</param>
        /// <returns></returns>
        [HttpGet("")]
        public async Task<dynamic> GetList([FromQuery] LqXmzlListQueryInput input)
        {
            var sidx = input.sidx == null ? "id" : input.sidx;
            var data = await _db.Queryable<LqXmzlEntity>()
                .WhereIF(!string.IsNullOrEmpty(input.id), p => p.Id.Contains(input.id))
                .WhereIF(!string.IsNullOrEmpty(input.xmbh), p => p.Xmbh.Contains(input.xmbh))
                .WhereIF(!string.IsNullOrEmpty(input.xmmc), p => p.Xmmc.Contains(input.xmmc))
                .WhereIF(!string.IsNullOrEmpty(input.bzjg), p => p.Bzjg.Equals(input.bzjg))
                .WhereIF(!string.IsNullOrEmpty(input.xmsc), p => p.Xmsc.Equals(input.xmsc))
                .WhereIF(!string.IsNullOrEmpty(input.jcfwtc), p => p.Jcfwtc.Equals(input.jcfwtc))
                .WhereIF(!string.IsNullOrEmpty(input.fl1), p => p.Fl1.Contains(input.fl1))
                .WhereIF(!string.IsNullOrEmpty(input.fl2), p => p.Fl2.Contains(input.fl2))
                .WhereIF(!string.IsNullOrEmpty(input.fl3), p => p.Fl3.Contains(input.fl3))
                .WhereIF(!string.IsNullOrEmpty(input.fl4), p => p.Fl4.Contains(input.fl4))
                .WhereIF(!string.IsNullOrEmpty(input.fl), p => p.Fl.Contains(input.fl))
                .WhereIF(!string.IsNullOrEmpty(input.qt1), p => p.Qt1.Contains(input.qt1))
                .WhereIF(!string.IsNullOrEmpty(input.qt2), p => p.Qt2.Contains(input.qt2))
                .WhereIF(!string.IsNullOrEmpty(input.sgf), p => p.Sgf.Equals(input.sgf))
                .WhereIF(!string.IsNullOrEmpty(input.beautyType), p => p.BeautyType.Contains(input.beautyType))
                .WhereIF(input.isEffective != 0, p => p.IsEffective.Equals(input.isEffective))
                .WhereIF(!string.IsNullOrEmpty(input.sourceType), p => p.SourceType.Contains(input.sourceType))
                .Select(it => new LqXmzlListOutput
                {
                    id = it.Id,
                    xmbh = it.Xmbh,
                    xmmc = it.Xmmc,
                    bzjg = it.Bzjg,
                    xmsc = it.Xmsc,
                    jcfwtc = it.Jcfwtc,
                    fl1 = it.Fl1,
                    fl2 = it.Fl2,
                    fl3 = it.Fl3,
                    fl4 = it.Fl4,
                    fl = it.Fl,
                    qt1 = it.Qt1,
                    qt2 = it.Qt2,
                    sgf = it.Sgf,
                    beautyType = it.BeautyType,
                    sourceType = it.SourceType,
                    isEffective = it.IsEffective,
                    medicalBeautyLaborCost = it.MedicalBeautyLaborCost,
                    techBeautyLaborCost = it.TechBeautyLaborCost,
                    lifeBeautyLaborCost = it.LifeBeautyLaborCost,
                    healthCoachLaborCost = it.HealthCoachLaborCost,
                    isAllowAccompanied = it.IsAllowAccompanied,
                }).MergeTable().OrderBy(sidx + " " + input.sort).ToPagedListAsync(input.currentPage, input.pageSize);
            return PageResult<LqXmzlListOutput>.SqlSugarPageResult(data);
        }
        #endregion
        #region 新建品项资料
        /// <summary>
        /// 新建品项资料
        /// </summary>
        /// <param name="input">参数</param>
        /// <returns></returns>
        [HttpPost("")]
        public async Task Create([FromBody] LqXmzlCrInput input)
        {
            var userInfo = await _userManager.GetUserInfo();
            var entity = input.Adapt<LqXmzlEntity>();
            entity.Id = YitIdHelper.NextId().ToString();
            var isOk = await _db.Insertable(entity).IgnoreColumns(ignoreNullColumn: true).ExecuteCommandAsync();
            if (!(isOk > 0)) throw NCCException.Oh(ErrorCode.COM1000);
        }
        #endregion
        #region 获取品项资料无分页列表
        /// <summary>
        /// 获取品项资料无分页列表
        /// </summary>
        /// <param name="input">请求参数</param>
        /// <returns></returns>
        [NonAction]
        public async Task<dynamic> GetNoPagingList([FromQuery] LqXmzlListQueryInput input)
        {
            var sidx = input.sidx == null ? "id" : input.sidx;
            var data = await _db.Queryable<LqXmzlEntity>()
                .WhereIF(!string.IsNullOrEmpty(input.id), p => p.Id.Contains(input.id))
                .WhereIF(!string.IsNullOrEmpty(input.xmbh), p => p.Xmbh.Contains(input.xmbh))
                .WhereIF(!string.IsNullOrEmpty(input.xmmc), p => p.Xmmc.Contains(input.xmmc))
                .WhereIF(!string.IsNullOrEmpty(input.bzjg), p => p.Bzjg.Equals(input.bzjg))
                .WhereIF(!string.IsNullOrEmpty(input.xmsc), p => p.Xmsc.Equals(input.xmsc))
                .WhereIF(!string.IsNullOrEmpty(input.jcfwtc), p => p.Jcfwtc.Equals(input.jcfwtc))
                .WhereIF(!string.IsNullOrEmpty(input.fl1), p => p.Fl1.Contains(input.fl1))
                .WhereIF(!string.IsNullOrEmpty(input.fl2), p => p.Fl2.Contains(input.fl2))
                .WhereIF(!string.IsNullOrEmpty(input.fl3), p => p.Fl3.Contains(input.fl3))
                .WhereIF(!string.IsNullOrEmpty(input.fl4), p => p.Fl4.Contains(input.fl4))
                .WhereIF(!string.IsNullOrEmpty(input.fl), p => p.Fl.Contains(input.fl))
                .WhereIF(!string.IsNullOrEmpty(input.qt1), p => p.Qt1.Contains(input.qt1))
                .WhereIF(!string.IsNullOrEmpty(input.qt2), p => p.Qt2.Contains(input.qt2))
                .WhereIF(!string.IsNullOrEmpty(input.sgf), p => p.Sgf.Equals(input.sgf))
                .Select(it => new LqXmzlListOutput
                {
                    id = it.Id,
                    xmbh = it.Xmbh,
                    xmmc = it.Xmmc,
                    bzjg = it.Bzjg,
                    xmsc = it.Xmsc,
                    jcfwtc = it.Jcfwtc,
                    fl1 = it.Fl1,
                    fl2 = it.Fl2,
                    fl3 = it.Fl3,
                    fl4 = it.Fl4,
                    fl = it.Fl,
                    qt1 = it.Qt1,
                    qt2 = it.Qt2,
                    sgf = it.Sgf,
                }).MergeTable().OrderBy(sidx + " " + input.sort).ToListAsync();
            return data;
        }
        #endregion
        #region 导出品项资料
        /// <summary>
        /// 导出品项资料
        /// </summary>
        /// <param name="input">请求参数</param>
        /// <returns></returns>
        [HttpGet("Actions/Export")]
        public async Task<dynamic> Export([FromQuery] LqXmzlListQueryInput input)
        {
            var userInfo = await _userManager.GetUserInfo();
            var exportData = new List<LqXmzlListOutput>();
            if (input.dataType == 0)
            {
                var data = Clay.Object(await this.GetList(input));
                exportData = data.Solidify<PageResult<LqXmzlListOutput>>().list;
            }
            else
            {
                exportData = await this.GetNoPagingList(input);
            }
            List<ParamsModel> paramList = "[{\"value\":\"主键\",\"field\":\"id\"},{\"value\":\"项目编号\",\"field\":\"xmbh\"},{\"value\":\"项目名称\",\"field\":\"xmmc\"},{\"value\":\"标准价格\",\"field\":\"bzjg\"},{\"value\":\"时长（分）\",\"field\":\"xmsc\"},{\"value\":\"基础服务提成\",\"field\":\"jcfwtc\"},{\"value\":\"分类1汇总表\",\"field\":\"fl1\"},{\"value\":\"分类2汇总表\",\"field\":\"fl2\"},{\"value\":\"分类3工资用\",\"field\":\"fl3\"},{\"value\":\"分类4品项用\",\"field\":\"fl4\"},{\"value\":\"统计类别\",\"field\":\"tjlb\"},{\"value\":\"折扣类别\",\"field\":\"zklb\"},{\"value\":\"分类\",\"field\":\"fl\"},{\"value\":\"其它1\",\"field\":\"qt1\"},{\"value\":\"其它2\",\"field\":\"qt2\"},{\"value\":\"溯源金额\",\"field\":\"syje\"},{\"value\":\"Cell金额\",\"field\":\"cellje\"},{\"value\":\"手工费\",\"field\":\"sgf\"},]".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 = input.selectKey.Split(',').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<LqXmzlListOutput>.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;
        }
        #endregion
        #region 批量删除品项资料
        /// <summary>
        /// 批量删除品项资料
        /// </summary>
        /// <param name="ids">主键数组</param>
        /// <returns></returns>
        [HttpPost("batchRemove")]
        public async Task BatchRemove([FromBody] List<string> ids)
        {
            throw NCCException.Oh("不允许删除项目资料");
            var entitys = await _db.Queryable<LqXmzlEntity>().In(it => it.Id, ids).ToListAsync();
            if (entitys.Count > 0)
            {
                try
                {
                    //开启事务
                    _db.BeginTran();
                    //批量删除项目资料
                    await _db.Deleteable<LqXmzlEntity>().In(d => d.Id, ids).ExecuteCommandAsync();
                    //关闭事务
                    _db.CommitTran();
                }
                catch (Exception)
                {
                    //回滚事务
                    _db.RollbackTran();
                    throw NCCException.Oh(ErrorCode.COM1002);
                }
            }
        }
        #endregion
        #region 更新品项资料
        /// <summary>
        /// 更新品项资料
        /// </summary>
        /// <param name="id">主键</param>
        /// <param name="input">参数</param>
        /// <returns></returns>
        [HttpPut("{id}")]
        public async Task Update(string id, [FromBody] LqXmzlUpInput input)
        {
            var entity = input.Adapt<LqXmzlEntity>();
            var isOk = await _db.Updateable(entity).IgnoreColumns(ignoreAllNullColumns: true).ExecuteCommandAsync();
            if (!(isOk > 0)) throw NCCException.Oh(ErrorCode.COM1001);
        }
        #endregion
        #region 设置品项作废
        /// <summary>
        /// 设置品项作废
        /// </summary>
        /// <param name="id">主键</param>
        /// <returns></returns>
        [HttpPut("SetInvalid/{id}")]
        public async Task SetInvalid(string id)
        {
            var entity = await _db.Queryable<LqXmzlEntity>().FirstAsync(p => p.Id == id);
            if (entity == null) throw NCCException.Oh("品项不存在");
            entity.IsEffective = StatusEnum.无效.GetHashCode();
            var isOk = await _db.Updateable(entity).IgnoreColumns(ignoreAllNullColumns: true).ExecuteCommandAsync();
            if (!(isOk > 0)) throw NCCException.Oh(ErrorCode.COM1001);
        }
        #endregion
        #region 删除品项资料
        /// <summary>
        /// 删除品项资料
        /// </summary>
        /// <param name="id">主键</param>
        /// <returns></returns>
        [HttpDelete("{id}")]
        public async Task Delete(string id)
        {
            throw NCCException.Oh("不允许删除项目资料");
        }
        #endregion
        #region 获取字段去重数据
        /// <summary>
        /// 获取指定字段的去重数据
        /// </summary>
        /// <param name="fieldName">字段名称，支持：fl1、fl2、fl3、fl4、fl、qt1、beautyType、sourceType</param>
        /// <returns>去重后的字段数据</returns>
        [HttpGet("GetDistinctFieldData")]
        public async Task<dynamic> GetDistinctFieldData([FromQuery] string fieldName)
        {
            try
            {
                // 验证字段名称
                var validFields = new[] { "fl1", "fl2", "fl3", "fl4", "fl", "qt1", "qt2", "beautyType", "sourceType" };
                if (!validFields.Contains(fieldName))
                {
                    throw NCCException.Oh($"无效的字段名称: {fieldName}。支持的字段: {string.Join(", ", validFields)}");
                }
                List<string> distinctValues = new List<string>();
                switch (fieldName)
                {
                    case "fl1":
                        distinctValues = await _db.Queryable<LqXmzlEntity>()
                            .Where(p => !string.IsNullOrEmpty(p.Fl1))
                            .Select(p => p.Fl1)
                            .Distinct()
                            .ToListAsync();
                        break;
                    case "fl2":
                        distinctValues = await _db.Queryable<LqXmzlEntity>()
                            .Where(p => !string.IsNullOrEmpty(p.Fl2))
                            .Select(p => p.Fl2)
                            .Distinct()
                            .ToListAsync();
                        break;
                    case "fl3":
                        distinctValues = await _db.Queryable<LqXmzlEntity>()
                            .Where(p => !string.IsNullOrEmpty(p.Fl3))
                            .Select(p => p.Fl3)
                            .Distinct()
                            .ToListAsync();
                        break;
                    case "fl4":
                        distinctValues = await _db.Queryable<LqXmzlEntity>()
                            .Where(p => !string.IsNullOrEmpty(p.Fl4))
                            .Select(p => p.Fl4)
                            .Distinct()
                            .ToListAsync();
                        break;
                    case "fl":
                        distinctValues = await _db.Queryable<LqXmzlEntity>()
                            .Where(p => !string.IsNullOrEmpty(p.Fl))
                            .Select(p => p.Fl)
                            .Distinct()
                            .ToListAsync();
                        break;
                    case "qt1":
                        distinctValues = await _db.Queryable<LqXmzlEntity>()
                            .Where(p => !string.IsNullOrEmpty(p.Qt1))
                            .Select(p => p.Qt1)
                            .Distinct()
                            .ToListAsync();
                        break;
                    case "qt2":
                        distinctValues = await _db.Queryable<LqXmzlEntity>()
                            .Where(p => !string.IsNullOrEmpty(p.Qt2))
                            .Select(p => p.Qt2)
                            .Distinct()
                            .ToListAsync();
                        break;
                    case "beautyType":
                        distinctValues = await _db.Queryable<LqXmzlEntity>()
                            .Where(p => !string.IsNullOrEmpty(p.BeautyType))
                            .Select(p => p.BeautyType)
                            .Distinct()
                            .ToListAsync();
                        break;
                    case "sourceType":
                        distinctValues = await _db.Queryable<LqXmzlEntity>()
                            .Where(p => !string.IsNullOrEmpty(p.SourceType))
                            .Select(p => p.SourceType)
                            .Distinct()
                            .ToListAsync();
                        break;
                }
                return new
                {
                    fieldName = fieldName,
                    values = distinctValues.OrderBy(x => x).ToList()
                };
            }
            catch (Exception ex)
            {
                throw NCCException.Oh($"获取字段去重数据失败：{ex.Message}");
            }
        }
        #endregion
        #region 品项维度统计
        /// <summary>
        /// 品项维度统计
        /// </summary>
        /// <remarks>
        /// 按品项维度统计开卡、消耗、退卡等数据
        /// 包括业绩、人数、占比、复购率等指标
        /// 
        /// 示例请求：
        /// ```json
        /// {
        ///   "startTime": "2024-01-01",
        ///   "endTime": "2024-12-31",
        ///   "storeId": "store001",
        ///   "category": "美容",
        ///   "itemId": "item001"
        /// }
        /// ```
        /// 
        /// 参数说明：
        /// - startTime: 开始时间（可选）
        /// - endTime: 结束时间（可选）
        /// - storeId: 门店ID（可选）
        /// - category: 品项分类（可选）
        /// - itemId: 品项ID（可选，单个品项统计）
        /// </remarks>
        /// <param name="input">统计输入参数</param>
        /// <returns>品项维度统计数据</returns>
        /// <response code="200">统计成功</response>
        /// <response code="400">参数错误</response>
        /// <response code="500">服务器错误</response>
        [HttpPost("GetItemStatistics")]
        public async Task<dynamic> GetItemStatistics([FromBody] LqXmzlStatisticsInput input)
        {
            try
            {
                // 第一步：获取品项基础信息
                var itemsQuery = _db.Queryable<LqXmzlEntity>()
                    .Where(x => x.IsEffective == 1);
                if (!string.IsNullOrEmpty(input.ItemId))
                {
                    itemsQuery = itemsQuery.Where(x => x.Id == input.ItemId);
                }
                if (!string.IsNullOrEmpty(input.Category))
                {
                    itemsQuery = itemsQuery.Where(x => x.Fl1 == input.Category || x.Fl2 == input.Category || x.Fl == input.Category);
                }
                var items = await itemsQuery.ToListAsync();
                if (!items.Any())
                {
                    return new
                    {
                        success = true,
                        data = new List<LqXmzlStatisticsOutput>(),
                        message = "未找到符合条件的品项"
                    };
                }
                var itemIds = items.Select(x => x.Id).ToList();
                // 第二步：开卡数据统计
                var billingStats = await GetBillingStatistics(itemIds, input);
                // 第三步：消耗数据统计
                var consumeStats = await GetConsumeStatistics(itemIds, input);
                // 第四步：退卡数据统计
                var refundStats = await GetRefundStatistics(itemIds, input);
                // 第五步：计算总数据用于占比计算
                var totalBillingAmount = billingStats.Sum(x => x.BillingAmount);
                var totalConsumeAmount = consumeStats.Sum(x => x.ConsumeAmount);
                var totalBuyers = billingStats.Sum(x => x.TotalBuyers);
                // 第六步：合并数据并计算占比
                var result = new List<LqXmzlStatisticsOutput>();
                foreach (var item in items)
                {
                    var billingData = billingStats.FirstOrDefault(x => x.ItemId == item.Id);
                    var consumeData = consumeStats.FirstOrDefault(x => x.ItemId == item.Id);
                    var refundData = refundStats.FirstOrDefault(x => x.ItemId == item.Id);
                    var output = new LqXmzlStatisticsOutput
                    {
                        ItemId = item.Id,
                        ItemName = item.Xmmc,
                        ItemNumber = item.Xmbh,
                        BillingAmount = billingData?.BillingAmount ?? 0,
                        BillingAmountRatio = totalBillingAmount > 0 ? (billingData?.BillingAmount ?? 0) / totalBillingAmount : 0,
                        TotalBuyers = billingData?.TotalBuyers ?? 0,
                        ItemRatio = totalBuyers > 0 ? (billingData?.TotalBuyers ?? 0) / (decimal)totalBuyers : 0,
                        RepeatBuyers = billingData?.RepeatBuyers ?? 0,
                        RepeatBuyRate = (billingData?.TotalBuyers ?? 0) > 0 ? (billingData?.RepeatBuyers ?? 0) / (decimal)(billingData?.TotalBuyers ?? 1) : 0,
                        ConsumeAmount = consumeData?.ConsumeAmount ?? 0,
                        ConsumeAmountRatio = totalConsumeAmount > 0 ? (consumeData?.ConsumeAmount ?? 0) / totalConsumeAmount : 0,
                        ConsumePurchaseCount = consumeData?.ConsumePurchaseCount ?? 0,
                        ConsumeGiftCount = consumeData?.ConsumeGiftCount ?? 0,
                        ConsumeExperienceCount = consumeData?.ConsumeExperienceCount ?? 0,
                        RefundAmount = refundData?.RefundAmount ?? 0,
                        RefundCount = refundData?.RefundCount ?? 0
                    };
                    result.Add(output);
                }
                return new
                {
                    success = true,
                    data = result,
                    message = "品项维度统计成功"
                };
            }
            catch (Exception ex)
            {
                throw NCCException.Oh($"品项维度统计失败：{ex.Message}");
            }
        }
        /// <summary>
        /// 获取开卡统计数据
        /// </summary>
        private async Task<List<ItemStatisticsData>> GetBillingStatistics(List<string> itemIds, LqXmzlStatisticsInput input)
        {
            // 使用JOIN关联开单记录表，以便使用开单时间进行过滤
            var query = _db.Queryable<LqKdPxmxEntity, LqKdKdjlbEntity>((px, kd) => new JoinQueryInfos(
                JoinType.Inner, px.Glkdbh == kd.Id))
                .Where((px, kd) => itemIds.Contains(px.Px) && px.IsEffective == 1 && kd.IsEffective == 1);
            // 时间过滤（使用开单时间）
            if (input.StartTime.HasValue)
            {
                query = query.Where((px, kd) => kd.Kdrq >= input.StartTime.Value);
            }
            if (input.EndTime.HasValue)
            {
                query = query.Where((px, kd) => kd.Kdrq <= input.EndTime.Value);
            }
            // 门店过滤
            if (!string.IsNullOrEmpty(input.StoreId))
            {
                query = query.Where((px, kd) => kd.Djmd == input.StoreId);
            }
            var result = await query
                .GroupBy((px, kd) => px.Px)
                .Select((px, kd) => new ItemStatisticsData
                {
                    ItemId = px.Px,
                    BillingAmount = SqlFunc.AggregateSum(px.Pxjg * px.ProjectNumber),
                    TotalBuyers = SqlFunc.AggregateCount(px.MemberId),
                    RepeatBuyers = 0 // 复购人数需要单独计算
                })
                .ToListAsync();
            // 单独计算复购人数
            foreach (var item in result)
            {
                var memberCountQuery = _db.Queryable<LqKdPxmxEntity, LqKdKdjlbEntity>((px, kd) => new JoinQueryInfos(
                    JoinType.Inner, px.Glkdbh == kd.Id))
                    .Where((px, kd) => px.Px == item.ItemId && px.IsEffective == 1 && kd.IsEffective == 1);
                if (input.StartTime.HasValue)
                {
                    memberCountQuery = memberCountQuery.Where((px, kd) => kd.Kdrq >= input.StartTime.Value);
                }
                if (input.EndTime.HasValue)
                {
                    memberCountQuery = memberCountQuery.Where((px, kd) => kd.Kdrq <= input.EndTime.Value);
                }
                if (!string.IsNullOrEmpty(input.StoreId))
                {
                    memberCountQuery = memberCountQuery.Where((px, kd) => kd.Djmd == input.StoreId);
                }
                var memberStats = await memberCountQuery
                    .GroupBy((px, kd) => px.MemberId)
                    .Having((px, kd) => SqlFunc.AggregateCount(px.MemberId) > 1)
                    .Select((px, kd) => SqlFunc.AggregateCount(px.MemberId))
                    .ToListAsync();
                item.RepeatBuyers = memberStats.Count;
            }
            return result;
        }
        /// <summary>
        /// 获取消耗统计数据
        /// </summary>
        private async Task<List<ItemConsumeStatisticsData>> GetConsumeStatistics(List<string> itemIds, LqXmzlStatisticsInput input)
        {
            var query = _db.Queryable<LqXhPxmxEntity>()
                .Where(x => itemIds.Contains(x.Px));
            // 时间过滤
            if (input.StartTime.HasValue)
            {
                query = query.Where(x => x.CreateTIme >= input.StartTime.Value);
            }
            if (input.EndTime.HasValue)
            {
                query = query.Where(x => x.CreateTIme <= input.EndTime.Value);
            }
            // 门店过滤（通过耗卡记录关联）
            if (!string.IsNullOrEmpty(input.StoreId))
            {
                // 使用子查询过滤门店 - 在SQL层面过滤，避免别名问题
                query = query.Where(x => SqlFunc.Subqueryable<LqXhHyhkEntity>()
                    .Where(xh => xh.Id == x.ConsumeInfoId && xh.Md == input.StoreId && xh.IsEffective == 1)
                    .Any());
            }
            var result = await query
                .GroupBy(x => x.Px)
                .Select(x => new ItemConsumeStatisticsData
                {
                    ItemId = x.Px,
                    ConsumeAmount = SqlFunc.AggregateSum(x.Pxjg * x.ProjectNumber),
                    ConsumePurchaseCount = SqlFunc.AggregateSum(SqlFunc.IIF(x.SourceType == "购买", x.ProjectNumber, 0)),
                    ConsumeGiftCount = SqlFunc.AggregateSum(SqlFunc.IIF(x.SourceType == "赠送", x.ProjectNumber, 0)),
                    ConsumeExperienceCount = SqlFunc.AggregateSum(SqlFunc.IIF(x.SourceType == "体验", x.ProjectNumber, 0))
                })
                .ToListAsync();
            return result;
        }
        /// <summary>
        /// 获取退卡统计数据
        /// </summary>
        private async Task<List<ItemRefundStatisticsData>> GetRefundStatistics(List<string> itemIds, LqXmzlStatisticsInput input)
        {
            var query = _db.Queryable<LqHytkMxEntity>()
                .Where(x => itemIds.Contains(x.Px));
            // 时间过滤
            if (input.StartTime.HasValue)
            {
                query = query.Where(x => x.Tksj >= input.StartTime.Value);
            }
            if (input.EndTime.HasValue)
            {
                query = query.Where(x => x.Tksj <= input.EndTime.Value);
            }
            // 门店过滤（通过退卡记录关联）
            if (!string.IsNullOrEmpty(input.StoreId))
            {
                // 使用子查询过滤门店 - 在SQL层面过滤，避免别名问题
                query = query.Where(x => SqlFunc.Subqueryable<LqHytkHytkEntity>()
                    .Where(hytk => hytk.Id == x.RefundInfoId && hytk.Md == input.StoreId && hytk.IsEffective == 1)
                    .Any());
            }
            var result = await query
                .GroupBy(x => x.Px)
                .Select(x => new ItemRefundStatisticsData
                {
                    ItemId = x.Px,
                    RefundAmount = SqlFunc.AggregateSum(x.Tkje ?? 0),
                    RefundCount = SqlFunc.AggregateCount(x.Id)
                })
                .ToListAsync();
            return result;
        }
        #endregion
        #region 获取品项门店统计
        /// <summary>
        /// 获取品项门店统计
        /// </summary>
        /// <remarks>
        /// 根据品项ID、开始时间和结束时间，统计每个门店对于该品项的开单数、项目数、实付金额、退款金额
        /// 
        /// 示例请求：
        /// ```json
        /// {
        ///   "itemId": "17",
        ///   "startTime": "2025-11-01",
        ///   "endTime": "2025-11-30"
        /// }
        /// ```
        /// 
        /// 参数说明：
        /// - itemId: 品项ID（必填）
        /// - startTime: 开始时间（必填）
        /// - endTime: 结束时间（必填）
        /// 
        /// 返回说明：
        /// - StoreId: 门店ID
        /// - StoreName: 门店名称
        /// - BillingCount: 开单数（去重后的开单编号数量）
        /// - ProjectCount: 项目数（项目次数总和）
        /// - ActualAmount: 实付金额（实付金额总和）
        /// - RefundAmount: 退款金额（退款金额总和）
        /// </remarks>
        /// <param name="input">查询参数</param>
        /// <returns>品项门店统计数据列表</returns>
        /// <response code="200">成功返回品项门店统计数据列表</response>
        /// <response code="400">参数无效</response>
        /// <response code="500">服务器内部错误</response>
        [HttpPost("get-item-store-statistics")]
        public async Task<List<ItemStoreStatisticsOutput>> GetItemStoreStatistics(ItemStoreStatisticsInput input)
        {
            if (string.IsNullOrWhiteSpace(input.ItemId))
            {
                throw NCCException.Oh("品项ID不能为空");
            }
            // 查询开单统计数据（按门店分组）
            var billingSql = $@"
                SELECT 
                    store.F_Id as StoreId,
                    store.dm as StoreName,
                    COUNT(DISTINCT billing.F_Id) as BillingCount,
                    COALESCE(SUM(pxmx.F_ProjectNumber), 0) as ProjectCount,
                    COALESCE(SUM(pxmx.F_ActualPrice), 0) as ActualAmount
                FROM lq_kd_pxmx pxmx
                INNER JOIN lq_kd_kdjlb billing ON pxmx.glkdbh = billing.F_Id
                INNER JOIN lq_mdxx store ON billing.djmd = store.F_Id
                WHERE pxmx.px = '{input.ItemId}'
                    AND pxmx.F_IsEffective = 1
                    AND billing.F_IsEffective = 1
                    AND billing.kdrq >= '{input.StartTime:yyyy-MM-dd} 00:00:00'
                    AND billing.kdrq < '{input.EndTime.AddDays(1):yyyy-MM-dd} 00:00:00'
                GROUP BY store.F_Id, store.dm";
            var billingData = await _db.Ado.SqlQueryAsync<dynamic>(billingSql);
            // 查询退款统计数据（按门店分组）
            var refundSql = $@"
                SELECT 
                    store.F_Id as StoreId,
                    store.dm as StoreName,
                    COALESCE(SUM(refund_mx.tkje), 0) as RefundAmount
                FROM lq_hytk_mx refund_mx
                INNER JOIN lq_hytk_hytk refund ON refund_mx.F_RefundInfoId = refund.F_Id
                INNER JOIN lq_mdxx store ON refund.md = store.F_Id
                WHERE refund_mx.px = '{input.ItemId}'
                    AND refund_mx.F_IsEffective = 1
                    AND refund.F_IsEffective = 1
                    AND refund.tksj >= '{input.StartTime:yyyy-MM-dd} 00:00:00'
                    AND refund.tksj < '{input.EndTime.AddDays(1):yyyy-MM-dd} 00:00:00'
                GROUP BY store.F_Id, store.dm";
            var refundData = await _db.Ado.SqlQueryAsync<dynamic>(refundSql);
            // 合并数据
            var resultDict = new Dictionary<string, ItemStoreStatisticsOutput>();
            // 处理开单数据
            foreach (var item in billingData ?? Enumerable.Empty<dynamic>())
            {
                var storeId = item.StoreId.ToString();
                resultDict[storeId] = new ItemStoreStatisticsOutput
                {
                    StoreId = storeId,
                    StoreName = item.StoreName.ToString(),
                    BillingCount = Convert.ToInt32(item.BillingCount),
                    ProjectCount = Convert.ToDecimal(item.ProjectCount),
                    ActualAmount = Convert.ToDecimal(item.ActualAmount),
                    RefundAmount = 0
                };
            }
            // 处理退款数据
            foreach (var item in refundData ?? Enumerable.Empty<dynamic>())
            {
                var storeId = item.StoreId.ToString();
                if (resultDict.ContainsKey(storeId))
                {
                    resultDict[storeId].RefundAmount = Convert.ToDecimal(item.RefundAmount);
                }
                else
                {
                    // 如果门店只有退款没有开单，也添加到结果中
                    resultDict[storeId] = new ItemStoreStatisticsOutput
                    {
                        StoreId = storeId,
                        StoreName = item.StoreName.ToString(),
                        BillingCount = 0,
                        ProjectCount = 0,
                        ActualAmount = 0,
                        RefundAmount = Convert.ToDecimal(item.RefundAmount)
                    };
                }
            }
            // 按实付金额降序排列
            return resultDict.Values.OrderByDescending(x => x.ActualAmount).ToList();
        }
        #endregion
    }
    /// <summary>
    /// 品项统计数据（内部类）
    /// </summary>
    public class ItemStatisticsData
    {
        public string ItemId { get; set; }
        public decimal BillingAmount { get; set; }
        public int TotalBuyers { get; set; }
        public int RepeatBuyers { get; set; }
    }
    /// <summary>
    /// 品项消耗统计数据（内部类）
    /// </summary>
    public class ItemConsumeStatisticsData
    {
        public string ItemId { get; set; }
        public decimal ConsumeAmount { get; set; }
        public decimal ConsumePurchaseCount { get; set; }
        public decimal ConsumeGiftCount { get; set; }
        public decimal ConsumeExperienceCount { get; set; }
    }
    /// <summary>
    /// 品项退卡统计数据（内部类）
    /// </summary>
    public class ItemRefundStatisticsData
    {
        public string ItemId { get; set; }
        public decimal RefundAmount { get; set; }
        public int RefundCount { get; set; }
    }
}