antissoft / lvqianmeiye_ERP

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Mapster;
using Microsoft.AspNetCore.Mvc;
using NCC.Common.Core.Manager;
using NCC.Common.Filter;
using NCC.Dependency;
using NCC.DynamicApiController;
using NCC.Extend.Entitys.Dto.LqPackageInfo;
using NCC.Extend.Entitys.Dto.LqXmzl;
using NCC.Extend.Entitys.Enum;
using NCC.Extend.Entitys.lq_package_info;
using NCC.Extend.Entitys.lq_package_item_detail;
using NCC.Extend.Entitys.lq_xmzl;
using NCC.Extend.Interfaces.LqPackageInfo;
using NCC.FriendlyException;
using SqlSugar;
using Yitter.IdGenerator;

namespace NCC.Extend.LqPackageInfo
{
    /// <summary>
    /// 营销活动服务
    /// </summary>
    [ApiDescriptionSettings(Tag = "绿纤营销活动服务", Name = "LqPackageInfo", Order = 200)]
    [Route("api/Extend/[controller]")]
    public class LqPackageInfoService : ILqPackageInfoService, IDynamicApiController, ITransient
    {
        private readonly ISqlSugarRepository<LqPackageInfoEntity> _packageInfoRepository;
        private readonly SqlSugarScope _db;
        private readonly IUserManager _userManager;

        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="packageInfoRepository">营销活动仓储</param>
        /// <param name="userManager">用户管理器</param>
        public LqPackageInfoService(ISqlSugarRepository<LqPackageInfoEntity> packageInfoRepository, IUserManager userManager)
        {
            _packageInfoRepository = packageInfoRepository;
            _db = packageInfoRepository.Context;
            _userManager = userManager;
        }

        #region 添加营销活动
        /// <summary>
        /// 添加营销活动
        /// </summary>
        /// <param name="input">营销活动创建输入</param>
        /// <returns>营销活动ID</returns>
        /// <remarks>
        /// 创建新的营销活动，包含活动基本信息和品项明细
        /// 
        /// 示例请求：
        /// ```json
        /// {
        ///   "activityName": "春季护肤优惠活动",
        ///   "activityDesc": "春季护肤特惠活动",
        ///   "startTime": "2025-03-01T00:00:00",
        ///   "endTime": "2025-03-31T23:59:59",
        ///   "minItemQuantity": 2,
        ///   "activityRules": "活动规则说明",
        ///   "activityImages": "[\"image1.jpg\"]",
        ///   "sortOrder": 1,
        ///   "activityItems": [
        ///     {
        ///       "itemId": "ITEM001",
        ///       "itemName": "面部清洁",
        ///       "itemCategory": "基础护理",
        ///       "itemRemark": "推荐品项"
        ///     }
        ///   ]
        /// }
        /// ```
        /// 
        /// 参数说明：
        /// - activityName: 营销活动名称
        /// - activityDesc: 营销活动描述
        /// - startTime: 活动开始时间
        /// - endTime: 活动结束时间
        /// - minItemQuantity: 至少购买品项数量
        /// - activityRules: 活动规则说明
        /// - activityImages: 活动图片(JSON格式)
        /// - sortOrder: 排序
        /// - activityItems: 营销活动品项明细列表
        /// </remarks>
        /// <response code="200">成功创建营销活动，返回营销活动ID</response>
        /// <response code="400">请求参数错误</response>
        /// <response code="500">服务器内部错误</response>
        [HttpPost("CreatePackageInfoAsync")]
        public async Task<string> CreatePackageInfoAsync(LqPackageInfoCrInput input)
        {
            try
            {
                // 验证活动时间
                if (input.StartTime >= input.EndTime)
                {
                    throw NCCException.Oh("活动开始时间必须小于结束时间");
                }

                // 验证品项明细
                if (input.ActivityItems == null || !input.ActivityItems.Any())
                {
                    throw NCCException.Oh("营销活动必须包含至少一个品项");
                }

                // 验证至少购买品项数量
                if (input.ActivityItems.Count < input.MinItemQuantity)
                {
                    throw NCCException.Oh($"品项数量不能少于至少购买品项数量({input.MinItemQuantity})");
                }

                // 开始事务
                _db.BeginTran();

                try
                {
                    // 创建营销活动实体
                    var packageInfo = input.Adapt<LqPackageInfoEntity>();
                    packageInfo.Id = YitIdHelper.NextId().ToString();
                    packageInfo.CreateTime = DateTime.Now;
                    packageInfo.UpdateTime = DateTime.Now;
                    packageInfo.CreateUser = _userManager.UserId;
                    packageInfo.UpdateUser = _userManager.UserId;
                    packageInfo.IsEffective = StatusEnum.有效.GetHashCode();
                    // 保存营销活动
                    await _db.Insertable(packageInfo).ExecuteCommandAsync();
                    // 创建营销活动品项明细
                    var packageItemDetails = input.ActivityItems.Select(item =>
                    {
                        var packageItemDetail = item.Adapt<LqPackageItemDetailEntity>();
                        packageItemDetail.Id = YitIdHelper.NextId().ToString();
                        packageItemDetail.ActivityId = packageInfo.Id;
                        packageItemDetail.CreateTime = DateTime.Now;
                        packageItemDetail.UpdateTime = DateTime.Now;
                        packageItemDetail.IsEffective = StatusEnum.有效.GetHashCode();
                        return packageItemDetail;
                    }).ToList();
                    // 批量保存营销活动品项明细
                    if (packageItemDetails.Any())
                    {
                        await _db.Insertable(packageItemDetails).ExecuteCommandAsync();
                    }
                    // 提交事务
                    _db.CommitTran();

                    return packageInfo.Id;
                }
                catch
                {
                    // 回滚事务
                    _db.RollbackTran();
                    throw;
                }
            }
            catch (Exception ex)
            {
                throw NCCException.Oh($"添加营销活动失败: {ex.Message}");
            }
        }
        #endregion

        #region 获取营销活动列表
        /// <summary>
        /// 获取营销活动列表
        /// </summary>
        /// <param name="input">营销活动列表查询输入</param>
        /// <returns>营销活动列表</returns>
        /// <remarks>
        /// 获取营销活动列表，支持按活动名称、时间范围等条件筛选
        /// 
        /// 查询参数说明：
        /// - activityName: 活动名称（模糊查询）
        /// - activityDesc: 活动描述（模糊查询）
        /// - startTimeStart: 活动开始时间范围开始
        /// - startTimeEnd: 活动开始时间范围结束
        /// - endTimeStart: 活动结束时间范围开始
        /// - endTimeEnd: 活动结束时间范围结束
        /// - minItemQuantity: 至少购买品项数量
        /// - createTimeStart: 创建时间范围开始
        /// - createTimeEnd: 创建时间范围结束
        /// </remarks>
        /// <response code="200">成功获取营销活动列表</response>
        /// <response code="400">请求参数错误</response>
        /// <response code="500">服务器内部错误</response>
        [HttpGet("GetPackageInfoListAsync")]
        public async Task<dynamic> GetPackageInfoListAsync([FromQuery] LqPackageInfoListQueryInput input)
        {
            var sidx = input.sidx == null ? "CreateTime" : input.sidx;
            var data = await _db.Queryable<LqPackageInfoEntity>()
                .WhereIF(!string.IsNullOrEmpty(input.ActivityName), w => w.ActivityName.Contains(input.ActivityName))
                .WhereIF(!string.IsNullOrEmpty(input.ActivityDesc), w => w.ActivityDesc.Contains(input.ActivityDesc))
                .WhereIF(input.StartTimeStart.HasValue, w => w.StartTime >= input.StartTimeStart.Value)
                .WhereIF(input.StartTimeEnd.HasValue, w => w.StartTime <= input.StartTimeEnd.Value)
                .WhereIF(input.EndTimeStart.HasValue, w => w.EndTime >= input.EndTimeStart.Value)
                .WhereIF(input.EndTimeEnd.HasValue, w => w.EndTime <= input.EndTimeEnd.Value)
                .WhereIF(input.MinItemQuantity.HasValue, w => w.MinItemQuantity == input.MinItemQuantity.Value)
                .WhereIF(input.CreateTimeStart.HasValue, w => w.CreateTime >= input.CreateTimeStart.Value)
                .WhereIF(input.CreateTimeEnd.HasValue, w => w.CreateTime <= input.CreateTimeEnd.Value)
                .WhereIF(input.IsEffective.HasValue, w => w.IsEffective == input.IsEffective.Value)
                .Select(it => new LqPackageInfoListOutput
                {
                    id = it.Id,
                    activityName = it.ActivityName,
                    activityDesc = it.ActivityDesc,
                    startTime = it.StartTime,
                    endTime = it.EndTime,
                    minItemQuantity = it.MinItemQuantity,
                    activityRules = it.ActivityRules,
                    sortOrder = it.SortOrder,
                    createTime = it.CreateTime,
                    isEffective = it.IsEffective,
                    createUser = it.CreateUser,
                    updateUser = it.UpdateUser,
                    updateTime = it.UpdateTime,
                    activityImages = it.ActivityImages,
                }).MergeTable().OrderBy(sidx + " " + input.sort).ToPagedListAsync(input.currentPage, input.pageSize);
            return PageResult<LqPackageInfoListOutput>.SqlSugarPageResult(data);
        }
        #endregion

        #region 获取营销活动详情
        /// <summary>
        /// 获取营销活动详情
        /// </summary>
        /// <param name="id">营销活动ID</param>
        /// <returns>营销活动详情（包含品项信息）</returns>
        /// <remarks>
        /// 获取营销活动详情，包括活动基本信息和对应的品项明细列表
        /// 
        /// 返回数据说明：
        /// - 活动基本信息：活动名称、描述、时间、规则等
        /// - 品项明细列表：该活动包含的所有品项信息
        /// </remarks>
        /// <response code="200">成功获取营销活动详情</response>
        /// <response code="404">营销活动不存在</response>
        /// <response code="500">服务器内部错误</response>
        [HttpGet("GetPackageInfoDetailAsync")]
        public async Task<dynamic> GetPackageInfoDetailAsync(string id)
        {
            try
            {
                // 获取营销活动基本信息
                var packageInfo = await _db.Queryable<LqPackageInfoEntity>().Where(w => w.Id == id).FirstAsync();
                if (packageInfo == null)
                {
                    throw NCCException.Oh("营销活动不存在");
                }
                // 获取品项明细信息
                var activityItems = await _db.Queryable<LqPackageItemDetailEntity>()
                    .Where(w => w.ActivityId == id && w.IsEffective == StatusEnum.有效.GetHashCode())
                    .OrderBy(w => w.CreateTime)
                    .Select(it => new
                    {
                        id = it.Id,
                        activityId = it.ActivityId,
                        itemId = it.ItemId,
                        itemName = it.ItemName,
                        itemCategory = it.ItemCategory,
                        itemRemark = it.ItemRemark,
                        createTime = it.CreateTime
                    })
                    .ToListAsync();
                // 构建返回结果
                var result = new
                {
                    // 活动基本信息
                    id = packageInfo.Id,
                    activityName = packageInfo.ActivityName,
                    activityDesc = packageInfo.ActivityDesc,
                    startTime = packageInfo.StartTime,
                    endTime = packageInfo.EndTime,
                    minItemQuantity = packageInfo.MinItemQuantity,
                    activityRules = packageInfo.ActivityRules,
                    activityImages = packageInfo.ActivityImages,
                    sortOrder = packageInfo.SortOrder,
                    createTime = packageInfo.CreateTime,
                    updateTime = packageInfo.UpdateTime,
                    createUser = packageInfo.CreateUser,
                    updateUser = packageInfo.UpdateUser,
                    isEffective = packageInfo.IsEffective,

                    // 品项明细列表
                    activityItems = activityItems
                };

                return result;
            }
            catch (Exception ex)
            {
                throw NCCException.Oh($"获取营销活动详情失败：{ex.Message}");
            }
        }
        #endregion

        #region 更新营销活动
        /// <summary>
        /// 更新营销活动
        /// </summary>
        /// <param name="input">营销活动更新输入</param>
        /// <returns>营销活动ID</returns>
        /// <remarks>
        /// 更新营销活动信息，包括活动基本信息和品项明细
        /// 
        /// 示例请求：
        /// ```json
        /// {
        ///   "id": "123456789",
        ///   "activityName": "春季护肤优惠活动",
        ///   "activityDesc": "春季护肤特惠活动",
        ///   "startTime": "2025-03-01T00:00:00",
        ///   "endTime": "2025-03-31T23:59:59",
        ///   "minItemQuantity": 2,
        ///   "activityRules": "活动规则说明",
        ///   "activityImages": "[\"image1.jpg\"]",
        ///   "sortOrder": 1,
        ///   "activityItems": [
        ///     {
        ///       "itemId": "ITEM001",
        ///       "itemName": "面部清洁",
        ///       "itemCategory": "基础护理",
        ///       "itemRemark": "推荐品项"
        ///     }
        ///   ]
        /// }
        /// ```
        /// 
        /// 参数说明：
        /// - id: 营销活动ID（必填）
        /// - activityName: 营销活动名称
        /// - activityDesc: 营销活动描述
        /// - startTime: 活动开始时间
        /// - endTime: 活动结束时间
        /// - minItemQuantity: 至少购买品项数量
        /// - activityRules: 活动规则说明
        /// - activityImages: 活动图片(JSON格式)
        /// - sortOrder: 排序
        /// - activityItems: 营销活动品项明细列表
        /// </remarks>
        /// <response code="200">成功更新营销活动，返回营销活动ID</response>
        /// <response code="400">请求参数错误</response>
        /// <response code="404">营销活动不存在</response>
        /// <response code="500">服务器内部错误</response>
        [HttpPut("UpdatePackageInfoAsync")]
        public async Task<string> UpdatePackageInfoAsync(LqPackageInfoUpInput input)
        {
            try
            {
                // 验证活动时间
                if (input.StartTime >= input.EndTime)
                {
                    throw NCCException.Oh("活动开始时间必须小于结束时间");
                }

                // 验证品项明细
                if (input.ActivityItems == null || !input.ActivityItems.Any())
                {
                    throw NCCException.Oh("营销活动必须包含至少一个品项");
                }

                // 验证至少购买品项数量
                if (input.ActivityItems.Count < input.MinItemQuantity)
                {
                    throw NCCException.Oh($"品项数量不能少于至少购买品项数量({input.MinItemQuantity})");
                }

                // 检查营销活动是否存在
                var existingActivity = await _db.Queryable<LqPackageInfoEntity>()
                    .Where(w => w.Id == input.Id)
                    .FirstAsync();

                if (existingActivity == null)
                {
                    throw NCCException.Oh("营销活动不存在");
                }

                // 开始事务
                _db.BeginTran();

                try
                {
                    // 更新营销活动基本信息
                    var updateResult = await _db.Updateable<LqPackageInfoEntity>()
                        .SetColumns(it => new LqPackageInfoEntity
                        {
                            ActivityName = input.ActivityName,
                            ActivityDesc = input.ActivityDesc,
                            StartTime = input.StartTime,
                            EndTime = input.EndTime,
                            MinItemQuantity = input.MinItemQuantity,
                            ActivityRules = input.ActivityRules,
                            ActivityImages = input.ActivityImages,
                            SortOrder = input.SortOrder,
                            UpdateTime = DateTime.Now,
                            UpdateUser = _userManager.UserId
                        })
                        .Where(w => w.Id == input.Id)
                        .ExecuteCommandAsync();

                    if (updateResult <= 0)
                    {
                        throw NCCException.Oh("更新营销活动失败");
                    }

                    // 删除原有的品项明细
                    await _db.Deleteable<LqPackageItemDetailEntity>()
                        .Where(w => w.ActivityId == input.Id)
                        .ExecuteCommandAsync();

                    // 创建新的品项明细
                    var packageItemDetails = input.ActivityItems.Select(item =>
                    {
                        var packageItemDetail = item.Adapt<LqPackageItemDetailEntity>();
                        packageItemDetail.Id = YitIdHelper.NextId().ToString();
                        packageItemDetail.ActivityId = input.Id;
                        packageItemDetail.CreateTime = DateTime.Now;
                        packageItemDetail.UpdateTime = DateTime.Now;
                        packageItemDetail.IsEffective = 1;
                        return packageItemDetail;
                    }).ToList();

                    // 批量保存新的品项明细
                    if (packageItemDetails.Any())
                    {
                        await _db.Insertable(packageItemDetails).ExecuteCommandAsync();
                    }

                    // 提交事务
                    _db.CommitTran();

                    return input.Id;
                }
                catch
                {
                    // 回滚事务
                    _db.RollbackTran();
                    throw;
                }
            }
            catch (Exception ex)
            {
                throw NCCException.Oh($"更新营销活动失败: {ex.Message}");
            }
        }
        #endregion

        #region 标记删除营销活动
        /// <summary>
        /// 标记删除营销活动
        /// </summary>
        /// <param name="id">营销活动ID</param>
        /// <returns>营销活动ID</returns>
        [HttpDelete("MarkDeletePackageInfoAsync")]
        public async Task<string> MarkDeletePackageInfoAsync(string id)
        {
            var entity = await _db.Queryable<LqPackageInfoEntity>().Where(w => w.Id == id).FirstAsync();
            if (entity == null)
            {
                throw NCCException.Oh("营销活动不存在");
            }
            entity.IsEffective = StatusEnum.无效.GetHashCode();
            await _db.Updateable(entity).ExecuteCommandAsync();
            await _db.Updateable<LqPackageItemDetailEntity>().SetColumns(it => new LqPackageItemDetailEntity { IsEffective = StatusEnum.无效.GetHashCode() }).Where(w => w.ActivityId == id).ExecuteCommandAsync();
            return id;
        }
        #endregion

        #region 根据营销活动ID获取品项详情
        /// <summary>
        /// 根据营销活动ID获取品项详情
        /// </summary>
        /// <param name="activityId">营销活动ID</param>
        /// <returns>品项详情列表</returns>
        /// <remarks>
        /// 通过营销活动ID获取对应的品项ID，然后查询品项表获取完整的品项信息
        /// 
        /// 查询流程：
        /// 1. 根据营销活动ID查询营销活动品项明细表，获取品项ID列表
        /// 2. 根据品项ID列表查询项目资料表，获取完整的品项信息
        /// 3. 返回品项详情列表
        /// </remarks>
        /// <response code="200">成功获取品项详情</response>
        /// <response code="404">营销活动不存在或没有品项</response>
        /// <response code="500">服务器内部错误</response>
        [HttpGet("GetPackageItemDetailByActivityIdAsync/{activityId}")]
        public async Task<dynamic> GetPackageItemDetailByActivityIdAsync(string activityId)
        {
            try
            {
                // 验证参数
                if (string.IsNullOrEmpty(activityId))
                {
                    throw NCCException.Oh("营销活动ID不能为空");
                }
                // 检查营销活动是否存在
                var activityExists = await _db.Queryable<LqPackageInfoEntity>().Where(w => w.Id == activityId && w.IsEffective == StatusEnum.有效.GetHashCode()).AnyAsync();
                if (!activityExists)
                {
                    throw NCCException.Oh("营销活动不存在或已失效");
                }
                // 获取营销活动下的品项ID列表
                var itemIds = await _db.Queryable<LqPackageItemDetailEntity>().Where(w => w.ActivityId == activityId && w.IsEffective == StatusEnum.有效.GetHashCode()).Select(w => w.ItemId).ToListAsync();
                if (!itemIds.Any())
                {
                    throw NCCException.Oh("该营销活动暂无品项");
                }
                // 根据品项ID查询项目资料表
                var itemDetails = await _db.Queryable<LqXmzlEntity>()
                    .Where(w => itemIds.Contains(w.Id) && w.IsEffective == StatusEnum.有效.GetHashCode())
                    .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,
                        isEffective = it.IsEffective,
                        sourceType = it.SourceType
                    }).MergeTable().ToPagedListAsync(1, 50);
                return PageResult<LqXmzlListOutput>.SqlSugarPageResult(itemDetails);
            }
            catch (Exception ex)
            {
                throw NCCException.Oh($"获取品项详情失败: {ex.Message}");
            }
        }
        #endregion

        #region 获取当前时间有效的活动
        /// <summary>
        /// 获取当前时间有效的活动
        /// </summary>
        /// <returns>当前时间有效的活动</returns>
        [HttpGet("GetCurrentTimeEffectiveActivityAsync")]
        public async Task<dynamic> GetCurrentTimeEffectiveActivityAsync()
        {
            var data = await _db.Queryable<LqPackageInfoEntity>().Where(w => w.StartTime <= DateTime.Now && w.EndTime >= DateTime.Now && w.IsEffective == StatusEnum.有效.GetHashCode()).ToListAsync();
            var output = data.Adapt<List<LqPackageInfoListOutput>>();
            return output;
        }
        #endregion

        #region 营销活动统计
        /// <summary>
        /// 获取营销活动统计数据
        /// </summary>
        /// <remarks>
        /// 统计指定营销活动的开单、退卡相关数据
        /// 包括：开单数量、开单金额、退卡数量、退卡金额、净开单数量、净开单金额、退卡率
        /// 
        /// 示例请求：
        /// ```json
        /// {
        ///   "activityId": "营销活动ID",
        ///   "startTime": "2025-10-01",
        ///   "endTime": "2025-10-31",
        ///   "storeIds": ["门店ID1", "门店ID2"]
        /// }
        /// ```
        /// 
        /// 参数说明：
        /// - activityId: 营销活动ID（必填）
        /// - startTime: 开始时间（可选，默认为活动开始时间）
        /// - endTime: 结束时间（可选，默认为活动结束时间）
        /// - storeIds: 门店ID列表（可选）
        /// 
        /// 返回字段说明：
        /// - ActivityId: 营销活动ID
        /// - ActivityName: 营销活动名称
        /// - BillingCount: 开单数量
        /// - BillingAmount: 开单金额
        /// - RefundCount: 退卡数量
        /// - RefundAmount: 退卡金额
        /// - NetBillingCount: 净开单数量（开单数量 - 退卡数量）
        /// - NetBillingAmount: 净开单金额（开单金额 - 退卡金额）
        /// - RefundRate: 退卡率（退卡数量 / 开单数量）
        /// - DebtAmount: 欠款金额（所有开单的欠款总和）
        /// </remarks>
        /// <param name="input">查询参数</param>
        /// <returns>营销活动统计数据</returns>
        /// <response code="200">成功返回统计数据</response>
        /// <response code="400">参数错误</response>
        /// <response code="500">服务器错误</response>
        [HttpPost("get-activity-statistics")]
        public async Task<object> GetActivityStatistics(ActivityStatisticsInput input)
        {
            try
            {
                // 1. 获取营销活动信息
                var activity = await _db.Queryable<LqPackageInfoEntity>()
                    .Where(x => x.Id == input.ActivityId && x.IsEffective == StatusEnum.有效.GetHashCode())
                    .FirstAsync();

                if (activity == null)
                {
                    throw NCCException.Oh("营销活动不存在或已失效");
                }

                // 2. 设置时间范围（如果未提供，使用活动时间范围）
                var startTime = input.StartTime ?? activity.StartTime;
                var endTime = input.EndTime ?? activity.EndTime;

                // 3. 获取营销活动关联的品项ID列表
                var itemIds = await _db.Queryable<LqPackageItemDetailEntity>()
                    .Where(x => x.ActivityId == input.ActivityId && x.IsEffective == StatusEnum.有效.GetHashCode())
                    .Select(x => x.ItemId)
                    .ToListAsync();

                if (!itemIds.Any())
                {
                    return new ActivityStatisticsOutput
                    {
                        ActivityId = input.ActivityId,
                        ActivityName = activity.ActivityName,
                        BillingCount = 0,
                        BillingAmount = 0,
                        RefundCount = 0,
                        RefundAmount = 0,
                        NetBillingCount = 0,
                        NetBillingAmount = 0,
                        RefundRate = 0,
                        DebtAmount = 0
                    };
                }

                // 4. 构建品项ID过滤条件
                var itemIdsStr = string.Join("','", itemIds);
                var itemIdsFilter = $"AND px.px IN ('{itemIdsStr}')";
                var itemIdsFilterRefund = $"AND hytkmx.px IN ('{itemIdsStr}')";

                // 5. 构建门店过滤条件
                string storeFilter = "";
                string storeFilterRefund = "";

                if (input.StoreIds != null && input.StoreIds.Any())
                {
                    var storeIdsStr = string.Join("','", input.StoreIds);
                    storeFilter = $"AND kd.djmd IN ('{storeIdsStr}')";
                    storeFilterRefund = $"AND hytk.md IN ('{storeIdsStr}')";
                }

                // 6. 开单统计
                var billingSql = $@"
                    SELECT 
                        COUNT(DISTINCT kd.F_Id) as billing_count,
                        SUM(CAST(px.F_ActualPrice AS DECIMAL(18,2))) as billing_amount,
                        SUM(CAST(kd.qk AS DECIMAL(18,2))) as debt_amount
                    FROM lq_kd_pxmx px
                    LEFT JOIN lq_kd_kdjlb kd ON px.glkdbh = kd.F_Id
                    WHERE px.F_IsEffective = 1 
                        {itemIdsFilter}
                        AND px.yjsj >= '{startTime:yyyy-MM-dd HH:mm:ss}' 
                        AND px.yjsj <= '{endTime:yyyy-MM-dd HH:mm:ss}'
                        {storeFilter}";

                var billingData = await _db.Ado.SqlQueryAsync<dynamic>(billingSql);
                var billingCount = Convert.ToInt32(billingData.FirstOrDefault()?.billing_count ?? 0);
                var billingAmount = Convert.ToDecimal(billingData.FirstOrDefault()?.billing_amount ?? 0);
                var debtAmount = Convert.ToDecimal(billingData.FirstOrDefault()?.debt_amount ?? 0);

                // 7. 退卡统计
                var refundSql = $@"
                    SELECT 
                        COUNT(DISTINCT hytk.F_Id) as refund_count,
                        SUM(CAST(hytkmx.tkje AS DECIMAL(18,2))) as refund_amount
                    FROM lq_hytk_mx hytkmx
                    LEFT JOIN lq_hytk_hytk hytk ON hytkmx.F_RefundInfoId = hytk.F_Id
                    WHERE hytkmx.F_IsEffective = 1 
                        {itemIdsFilterRefund}
                        AND hytkmx.tksj >= '{startTime:yyyy-MM-dd HH:mm:ss}' 
                        AND hytkmx.tksj <= '{endTime:yyyy-MM-dd HH:mm:ss}'
                        {storeFilterRefund}";

                var refundData = await _db.Ado.SqlQueryAsync<dynamic>(refundSql);
                var refundCount = Convert.ToInt32(refundData.FirstOrDefault()?.refund_count ?? 0);
                var refundAmount = Convert.ToDecimal(refundData.FirstOrDefault()?.refund_amount ?? 0);

                // 8. 计算净值和退卡率
                var netBillingCount = billingCount - refundCount;
                var netBillingAmount = billingAmount - refundAmount;
                var refundRate = billingCount > 0 ? Math.Round((decimal)refundCount / billingCount * 100, 2) : 0;

                // 9. 返回统计结果
                return new ActivityStatisticsOutput
                {
                    ActivityId = input.ActivityId,
                    ActivityName = activity.ActivityName,
                    BillingCount = billingCount,
                    BillingAmount = billingAmount,
                    RefundCount = refundCount,
                    RefundAmount = refundAmount,
                    NetBillingCount = netBillingCount,
                    NetBillingAmount = netBillingAmount,
                    RefundRate = refundRate,
                    DebtAmount = debtAmount
                };
            }
            catch (Exception ex)
            {
                throw NCCException.Oh($"获取营销活动统计数据失败: {ex.Message}");
            }
        }
        #endregion

    }
}