antissoft / lvqianmeiye_ERP

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Mvc;
using NCC.Common.Core.Manager;
using NCC.Dependency;
using NCC.DynamicApiController;
using NCC.Extend.Entitys.Dto.LqTkDashboard;
using NCC.Extend.Entitys.lq_event;
using NCC.Extend.Entitys.lq_eventuser;
using NCC.Extend.Entitys.lq_kd_kdjlb;
using NCC.Extend.Entitys.lq_kd_pxmx;
using NCC.Extend.Entitys.lq_mdxx;
using NCC.Extend.Entitys.lq_tkjlb;
using NCC.Extend.Entitys.lq_xh_hyhk;
using NCC.FriendlyException;
using NCC.System.Entitys.Permission;
using SqlSugar;

namespace NCC.Extend.LqTkDashboard
{
    /// <summary>
    /// 拓客驾驶舱服务
    /// </summary>
    [ApiDescriptionSettings(Tag = "绿纤拓客驾驶舱服务", Name = "LqTkDashboard", Order = 201)]
    [Route("api/Extend/[controller]")]
    public class LqTkDashboardService : IDynamicApiController, ITransient
    {
        private readonly ISqlSugarClient _db;
        private readonly IUserManager _userManager;

        /// <summary>
        /// 初始化一个<see cref="LqTkDashboardService"/>类型的新实例
        /// </summary>
        public LqTkDashboardService(IUserManager userManager, ISqlSugarClient db)
        {
            _userManager = userManager;
            _db = db;
        }

        #region 获取驾驶舱概览数据

        /// <summary>
        /// 获取驾驶舱概览数据
        /// </summary>
        /// <remarks>
        /// 获取拓客活动的整体统计数据，包括拓客人数、到店人数、开单人数、大单统计等核心指标
        /// 
        /// 示例请求：
        /// ```json
        /// {
        ///   "eventId": "活动ID",
        ///   "startTime": "2025-01-01",
        ///   "endTime": "2025-01-31"
        /// }
        /// ```
        /// 
        /// 参数说明：
        /// - eventId: 拓客活动ID（必填）
        /// - startTime: 开始时间（可选，如不填则使用活动开始时间）
        /// - endTime: 结束时间（可选，如不填则使用活动结束时间）
        /// </remarks>
        /// <param name="input">查询参数</param>
        /// <returns>概览统计数据</returns>
        /// <response code="200">成功返回概览数据</response>
        /// <response code="400">请求参数错误</response>
        /// <response code="500">服务器错误</response>
        [HttpPost("GetOverview")]
        public async Task<TkDashboardOverviewOutput> GetOverview([FromBody] TkDashboardQueryInput input)
        {
            DateTime? startTime = input.StartTime;
            DateTime? endTime = input.EndTime;

            if (!string.IsNullOrEmpty(input.EventId))
            {
                // 获取活动信息
                var eventInfo = await _db.Queryable<LqEventEntity>()
                    .Where(x => x.Id == input.EventId)
                    .FirstAsync();

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

                if (startTime == null) startTime = eventInfo.StartTime;
                if (endTime == null) endTime = eventInfo.EndTime;
            }

            if (!startTime.HasValue || !endTime.HasValue)
            {
                throw NCCException.Oh("必须指定时间范围或活动ID");
            }

            var eventFilter = string.IsNullOrEmpty(input.EventId) ? "1=1" : $"tk.F_EventId = '{input.EventId}'";

            // 1. 统计总拓客人数（按会员ID去重）
            var expansionCountSql = $@"
                SELECT COUNT(DISTINCT F_MemberId) as ExpansionCount
                FROM lq_tkjlb tk
                WHERE {eventFilter}
                    AND tk.F_ExpansionTime >= '{startTime.Value:yyyy-MM-dd HH:mm:ss}'
                    AND tk.F_ExpansionTime <= '{endTime.Value:yyyy-MM-dd HH:mm:ss}'";
            var expansionCountResult = await _db.Ado.SqlQuerySingleAsync<dynamic>(expansionCountSql);
            var totalExpansionCount = 0;
            if (expansionCountResult != null)
            {
                try { totalExpansionCount = Convert.ToInt32(expansionCountResult.ExpansionCount); } catch { }
            }

            // 2. 统计总到店人数（有耗卡记录的人数，按会员ID去重）
            var visitSql = $@"
                SELECT COUNT(DISTINCT tk.F_MemberId) as VisitCount
                FROM lq_tkjlb tk
                INNER JOIN lq_xh_hyhk xh ON tk.F_MemberId = xh.hy AND xh.F_IsEffective = 1
                WHERE {eventFilter}
                    AND tk.F_ExpansionTime >= '{startTime.Value:yyyy-MM-dd HH:mm:ss}'
                    AND tk.F_ExpansionTime <= '{endTime.Value:yyyy-MM-dd HH:mm:ss}'
                    AND xh.hksj >= '{startTime.Value:yyyy-MM-dd HH:mm:ss}'
                    AND xh.hksj <= '{endTime.Value:yyyy-MM-dd HH:mm:ss}'";
            var visitResult = await _db.Ado.SqlQuerySingleAsync<dynamic>(visitSql);
            var totalVisitCount = Convert.ToInt32(visitResult?.VisitCount ?? 0);

            // 3. 统计总开单人数（有开单记录且金额>0的人数，按会员ID去重）
            var billingCountSql = $@"
                SELECT COUNT(DISTINCT tk.F_MemberId) as BillingCount
                FROM lq_tkjlb tk
                INNER JOIN lq_kd_kdjlb kd ON tk.F_MemberId = kd.kdhy
                WHERE {eventFilter}
                    AND tk.F_ExpansionTime >= '{startTime.Value:yyyy-MM-dd HH:mm:ss}'
                    AND tk.F_ExpansionTime <= '{endTime.Value:yyyy-MM-dd HH:mm:ss}'
                    AND kd.F_IsEffective = 1
                    AND kd.sfyj > 0
                    AND kd.kdrq >= '{startTime.Value:yyyy-MM-dd HH:mm:ss}'
                    AND kd.kdrq <= '{endTime.Value:yyyy-MM-dd HH:mm:ss}'";
            var billingCountResult = await _db.Ado.SqlQuerySingleAsync<dynamic>(billingCountSql);
            var totalBillingCount = Convert.ToInt32(billingCountResult?.BillingCount ?? 0);

            // 4. 统计总开单金额
            var billingAmountSql = $@"
                SELECT COALESCE(SUM(kd.sfyj), 0) as BillingAmount
                FROM lq_tkjlb tk
                INNER JOIN lq_kd_kdjlb kd ON tk.F_MemberId = kd.kdhy
                WHERE {eventFilter}
                    AND tk.F_ExpansionTime >= '{startTime.Value:yyyy-MM-dd HH:mm:ss}'
                    AND tk.F_ExpansionTime <= '{endTime.Value:yyyy-MM-dd HH:mm:ss}'
                    AND kd.F_IsEffective = 1
                    AND kd.sfyj > 0
                    AND kd.kdrq >= '{startTime.Value:yyyy-MM-dd HH:mm:ss}'
                    AND kd.kdrq <= '{endTime.Value:yyyy-MM-dd HH:mm:ss}'";
            var billingAmountResult = await _db.Ado.SqlQuerySingleAsync<dynamic>(billingAmountSql);
            var totalBillingAmount = Convert.ToDecimal(billingAmountResult?.BillingAmount ?? 0);

            // 5. 统计大单数量（开单金额 > 10000）
            var bigOrderCountSql = $@"
                SELECT COUNT(*) as BigOrderCount
                FROM lq_tkjlb tk
                INNER JOIN lq_kd_kdjlb kd ON tk.F_MemberId = kd.kdhy
                WHERE {eventFilter}
                    AND tk.F_ExpansionTime >= '{startTime.Value:yyyy-MM-dd HH:mm:ss}'
                    AND tk.F_ExpansionTime <= '{endTime.Value:yyyy-MM-dd HH:mm:ss}'
                    AND kd.F_IsEffective = 1
                    AND kd.sfyj > 10000
                    AND kd.kdrq >= '{startTime.Value:yyyy-MM-dd HH:mm:ss}'
                    AND kd.kdrq <= '{endTime.Value:yyyy-MM-dd HH:mm:ss}'";
            var bigOrderCountResult = await _db.Ado.SqlQuerySingleAsync<dynamic>(bigOrderCountSql);
            var bigOrderCount = Convert.ToInt32(bigOrderCountResult?.BigOrderCount ?? 0);

            // 6. 统计大单金额
            var bigOrderAmountSql = $@"
                SELECT COALESCE(SUM(kd.sfyj), 0) as BigOrderAmount
                FROM lq_tkjlb tk
                INNER JOIN lq_kd_kdjlb kd ON tk.F_MemberId = kd.kdhy
                WHERE {eventFilter}
                    AND tk.F_ExpansionTime >= '{startTime.Value:yyyy-MM-dd HH:mm:ss}'
                    AND tk.F_ExpansionTime <= '{endTime.Value:yyyy-MM-dd HH:mm:ss}'
                    AND kd.F_IsEffective = 1
                    AND kd.sfyj > 10000
                    AND kd.kdrq >= '{startTime.Value:yyyy-MM-dd HH:mm:ss}'
                    AND kd.kdrq <= '{endTime.Value:yyyy-MM-dd HH:mm:ss}'";
            var bigOrderAmountResult = await _db.Ado.SqlQuerySingleAsync<dynamic>(bigOrderAmountSql);
            var bigOrderAmount = Convert.ToDecimal(bigOrderAmountResult?.BigOrderAmount ?? 0);

            // 7. 统计参与拓客人员数（按拓客人员ID去重）
            var participantCount = await _db.Queryable<LqTkjlbEntity>()
                .WhereIF(!string.IsNullOrEmpty(input.EventId), x => x.EventId == input.EventId)
                .Where(x => x.ExpansionTime >= startTime.Value && x.ExpansionTime <= endTime.Value)
                .GroupBy(x => x.ExpansionUserId)
                .Select(x => x.ExpansionUserId)
                .CountAsync();

            // 8. 统计参与门店数（按门店ID去重）
            var storeCount = await _db.Queryable<LqTkjlbEntity>()
                .WhereIF(!string.IsNullOrEmpty(input.EventId), x => x.EventId == input.EventId)
                .Where(x => x.ExpansionTime >= startTime.Value && x.ExpansionTime <= endTime.Value)
                .GroupBy(x => x.StoreId)
                .Select(x => x.StoreId)
                .CountAsync();

            // 计算各项比率
            var visitRate = totalExpansionCount > 0 ? Math.Round(totalVisitCount * 100m / totalExpansionCount, 2) : 0m;
            var billingRate = totalVisitCount > 0 ? Math.Round(totalBillingCount * 100m / totalVisitCount, 2) : 0m;
            var bigOrderRate = totalExpansionCount > 0 ? Math.Round(bigOrderCount * 100m / totalExpansionCount, 2) : 0m;
            var bigOrderAmountRate = totalBillingAmount > 0 ? Math.Round(bigOrderAmount * 100m / totalBillingAmount, 2) : 0m;
            var bigOrderAvgAmount = bigOrderCount > 0 ? Math.Round(bigOrderAmount / bigOrderCount, 2) : 0m;

            return new TkDashboardOverviewOutput
            {
                TotalExpansionCount = totalExpansionCount,
                TotalVisitCount = totalVisitCount,
                TotalBillingCount = totalBillingCount,
                TotalBillingAmount = totalBillingAmount,
                BigOrderCount = bigOrderCount,
                BigOrderAmount = bigOrderAmount,
                BigOrderAvgAmount = bigOrderAvgAmount,
                VisitRate = visitRate,
                BillingRate = billingRate,
                BigOrderRate = bigOrderRate,
                BigOrderAmountRate = bigOrderAmountRate,
                ParticipantCount = participantCount,
                StoreCount = storeCount
            };
        }

        #endregion

        #region 获取大单统计

        /// <summary>
        /// 获取大单统计
        /// </summary>
        /// <remarks>
        /// 获取拓客活动的大单统计数据，包括汇总数据、按门店统计、按员工统计和大单明细
        /// 
        /// 示例请求：
        /// ```json
        /// {
        ///   "eventId": "活动ID",
        ///   "startTime": "2025-01-01",
        ///   "endTime": "2025-01-31"
        /// }
        /// ```
        /// 
        /// 大单定义：开单金额（实付业绩）> 10000 元（不含等于）
        /// </remarks>
        /// <param name="input">查询参数</param>
        /// <returns>大单统计数据</returns>
        /// <response code="200">成功返回大单统计数据</response>
        /// <response code="400">请求参数错误</response>
        /// <response code="500">服务器错误</response>
        [HttpPost("GetBigOrderStatistics")]
        public async Task<BigOrderStatisticsOutput> GetBigOrderStatistics([FromBody] TkDashboardQueryInput input)
        {
            DateTime? startTime = input.StartTime;
            DateTime? endTime = input.EndTime;

            if (!string.IsNullOrEmpty(input.EventId))
            {
                var eventInfo = await _db.Queryable<LqEventEntity>()
                    .Where(x => x.Id == input.EventId)
                    .FirstAsync();

                if (eventInfo == null)
                {
                    throw NCCException.Oh("活动不存在");
                }
                if (startTime == null) startTime = eventInfo.StartTime;
                if (endTime == null) endTime = eventInfo.EndTime;
            }

            if (!startTime.HasValue || !endTime.HasValue)
            {
                throw NCCException.Oh("必须指定时间范围或活动ID");
            }

            var eventFilter = string.IsNullOrEmpty(input.EventId) ? "1=1" : $"tk.F_EventId = '{input.EventId}'";

            // 获取总拓客人数和总开单人数（用于计算转化率）
            var totalExpansionCount = await _db.Queryable<LqTkjlbEntity>()
                .WhereIF(!string.IsNullOrEmpty(input.EventId), x => x.EventId == input.EventId)
                .Where(x => x.ExpansionTime >= startTime.Value && x.ExpansionTime <= endTime.Value)
                .GroupBy(x => x.MemberId)
                .Select(x => x.MemberId)
                .CountAsync();

            var totalBillingCountSql = $@"
                SELECT COUNT(DISTINCT tk.F_MemberId) as BillingCount
                FROM lq_tkjlb tk
                INNER JOIN lq_kd_kdjlb kd ON tk.F_MemberId = kd.kdhy
                WHERE {eventFilter}
                    AND tk.F_ExpansionTime >= '{startTime.Value:yyyy-MM-dd HH:mm:ss}'
                    AND tk.F_ExpansionTime <= '{endTime.Value:yyyy-MM-dd HH:mm:ss}'
                    AND kd.F_IsEffective = 1
                    AND kd.sfyj > 0
                    AND kd.kdrq >= '{startTime.Value:yyyy-MM-dd HH:mm:ss}'
                    AND kd.kdrq <= '{endTime.Value:yyyy-MM-dd HH:mm:ss}'";
            var totalBillingCountResult = await _db.Ado.SqlQuerySingleAsync<dynamic>(totalBillingCountSql);
            var totalBillingCount = Convert.ToInt32(totalBillingCountResult?.BillingCount ?? 0);

            var totalBillingAmountSql = $@"
                SELECT COALESCE(SUM(kd.sfyj), 0) as BillingAmount
                FROM lq_tkjlb tk
                INNER JOIN lq_kd_kdjlb kd ON tk.F_MemberId = kd.kdhy
                WHERE {eventFilter}
                    AND tk.F_ExpansionTime >= '{startTime.Value:yyyy-MM-dd HH:mm:ss}'
                    AND tk.F_ExpansionTime <= '{endTime.Value:yyyy-MM-dd HH:mm:ss}'
                    AND kd.F_IsEffective = 1
                    AND kd.sfyj > 0
                    AND kd.kdrq >= '{startTime.Value:yyyy-MM-dd HH:mm:ss}'
                    AND kd.kdrq <= '{endTime.Value:yyyy-MM-dd HH:mm:ss}'";
            var totalBillingAmountResult = await _db.Ado.SqlQuerySingleAsync<dynamic>(totalBillingAmountSql);
            var totalBillingAmount = Convert.ToDecimal(totalBillingAmountResult?.BillingAmount ?? 0);

            var totalVisitCountSql = $@"
                SELECT COUNT(DISTINCT tk.F_MemberId) as VisitCount
                FROM lq_tkjlb tk
                INNER JOIN lq_xh_hyhk xh ON tk.F_MemberId = xh.hy AND xh.F_IsEffective = 1
                WHERE {eventFilter}
                    AND tk.F_ExpansionTime >= '{startTime.Value:yyyy-MM-dd HH:mm:ss}'
                    AND tk.F_ExpansionTime <= '{endTime.Value:yyyy-MM-dd HH:mm:ss}'
                    AND xh.hksj >= '{startTime.Value:yyyy-MM-dd HH:mm:ss}'
                    AND xh.hksj <= '{endTime.Value:yyyy-MM-dd HH:mm:ss}'";
            var totalVisitCountResult = await _db.Ado.SqlQuerySingleAsync<dynamic>(totalVisitCountSql);
            var totalVisitCount = Convert.ToInt32(totalVisitCountResult?.VisitCount ?? 0);

            // 获取大单汇总数据
            var bigOrderSql = $@"
                SELECT 
                    COUNT(*) as BigOrderCount,
                    COALESCE(SUM(kd.sfyj), 0) as BigOrderAmount
                FROM lq_tkjlb tk
                INNER JOIN lq_kd_kdjlb kd ON tk.F_MemberId = kd.kdhy
                WHERE {eventFilter}
                    AND tk.F_ExpansionTime >= '{startTime.Value:yyyy-MM-dd HH:mm:ss}'
                    AND tk.F_ExpansionTime <= '{endTime.Value:yyyy-MM-dd HH:mm:ss}'
                    AND kd.F_IsEffective = 1
                    AND kd.sfyj > 10000
                    AND kd.kdrq >= '{startTime.Value:yyyy-MM-dd HH:mm:ss}'
                    AND kd.kdrq <= '{endTime.Value:yyyy-MM-dd HH:mm:ss}'";

            var bigOrderResult = await _db.Ado.SqlQuerySingleAsync<dynamic>(bigOrderSql);
            var bigOrderCount = Convert.ToInt32(bigOrderResult?.BigOrderCount ?? 0);
            var bigOrderAmount = Convert.ToDecimal(bigOrderResult?.BigOrderAmount ?? 0);

            var bigOrderAvgAmount = bigOrderCount > 0 ? Math.Round(bigOrderAmount / bigOrderCount, 2) : 0m;
            var bigOrderRate = totalBillingCount > 0 ? Math.Round(bigOrderCount * 100m / totalBillingCount, 2) : 0m;
            var bigOrderAmountRate = totalBillingAmount > 0 ? Math.Round(bigOrderAmount * 100m / totalBillingAmount, 2) : 0m;
            var bigOrderConversionRate = totalExpansionCount > 0 ? Math.Round(bigOrderCount * 100m / totalExpansionCount, 2) : 0m;
            var bigOrderVisitConversionRate = totalVisitCount > 0 ? Math.Round(bigOrderCount * 100m / totalVisitCount, 2) : 0m;

            // 按门店统计大单
            var byStoreSql = $@"
                SELECT 
                    tk.F_StoreId as StoreId,
                    COALESCE(md.dm, '') as StoreName,
                    COUNT(*) as BigOrderCount,
                    COALESCE(SUM(kd.sfyj), 0) as BigOrderAmount,
                    COUNT(DISTINCT tk.F_MemberId) as TotalBillingCount
                FROM lq_tkjlb tk
                INNER JOIN lq_kd_kdjlb kd ON tk.F_MemberId = kd.kdhy
                LEFT JOIN lq_mdxx md ON tk.F_StoreId = md.F_Id
                WHERE {eventFilter}
                    AND tk.F_ExpansionTime >= '{startTime.Value:yyyy-MM-dd HH:mm:ss}'
                    AND tk.F_ExpansionTime <= '{endTime.Value:yyyy-MM-dd HH:mm:ss}'
                    AND kd.F_IsEffective = 1
                    AND kd.sfyj > 10000
                    AND kd.kdrq >= '{startTime.Value:yyyy-MM-dd HH:mm:ss}'
                    AND kd.kdrq <= '{endTime.Value:yyyy-MM-dd HH:mm:ss}'
                GROUP BY tk.F_StoreId, md.dm";

            var byStoreData = await _db.Ado.SqlQueryAsync<dynamic>(byStoreSql);
            var byStore = byStoreData.Select(x => new BigOrderByStoreOutput
            {
                StoreId = x.StoreId?.ToString() ?? "",
                StoreName = x.StoreName?.ToString() ?? "",
                BigOrderCount = Convert.ToInt32(x.BigOrderCount ?? 0),
                BigOrderAmount = Convert.ToDecimal(x.BigOrderAmount ?? 0),
                BigOrderRate = Convert.ToInt32(x.TotalBillingCount ?? 0) > 0
                    ? Math.Round(Convert.ToInt32(x.BigOrderCount ?? 0) * 100m / Convert.ToInt32(x.TotalBillingCount ?? 0), 2)
                    : 0m
            }).ToList();

            // 按员工统计大单
            var byEmployeeSql = $@"
                SELECT 
                    tk.F_ExpansionUserId as EmployeeId,
                    COALESCE(u.F_REALNAME, '') as EmployeeName,
                    COUNT(*) as BigOrderCount,
                    COALESCE(SUM(kd.sfyj), 0) as BigOrderAmount,
                    COUNT(DISTINCT tk.F_MemberId) as TotalExpansionCount
                FROM lq_tkjlb tk
                INNER JOIN lq_kd_kdjlb kd ON tk.F_MemberId = kd.kdhy
                LEFT JOIN BASE_USER u ON tk.F_ExpansionUserId = u.F_Id
                WHERE {eventFilter}
                    AND tk.F_ExpansionTime >= '{startTime.Value:yyyy-MM-dd HH:mm:ss}'
                    AND tk.F_ExpansionTime <= '{endTime.Value:yyyy-MM-dd HH:mm:ss}'
                    AND kd.F_IsEffective = 1
                    AND kd.sfyj > 10000
                    AND kd.kdrq >= '{startTime.Value:yyyy-MM-dd HH:mm:ss}'
                    AND kd.kdrq <= '{endTime.Value:yyyy-MM-dd HH:mm:ss}'
                GROUP BY tk.F_ExpansionUserId, u.F_REALNAME";

            var byEmployeeData = await _db.Ado.SqlQueryAsync<dynamic>(byEmployeeSql);
            var byEmployee = byEmployeeData.Select(x => new BigOrderByEmployeeOutput
            {
                EmployeeId = x.EmployeeId?.ToString() ?? "",
                EmployeeName = x.EmployeeName?.ToString() ?? "",
                BigOrderCount = Convert.ToInt32(x.BigOrderCount ?? 0),
                BigOrderAmount = Convert.ToDecimal(x.BigOrderAmount ?? 0),
                BigOrderRate = Convert.ToInt32(x.TotalExpansionCount ?? 0) > 0
                    ? Math.Round(Convert.ToInt32(x.BigOrderCount ?? 0) * 100m / Convert.ToInt32(x.TotalExpansionCount ?? 0), 2)
                    : 0m
            }).ToList();

            // 获取大单明细
            var detailsSql = $@"
                SELECT 
                    tk.F_CustomerName as CustomerName,
                    tk.F_CustomerPhone as CustomerPhone,
                    COALESCE(u.F_REALNAME, '') as ExpansionUserName,
                    tk.F_ExpansionTime as ExpansionTime,
                    kd.kdrq as BillingTime,
                    kd.sfyj as BillingAmount,
                    COALESCE(md.dm, '') as StoreName,
                    kd.F_Id as BillingId
                FROM lq_tkjlb tk
                INNER JOIN lq_kd_kdjlb kd ON tk.F_MemberId = kd.kdhy
                LEFT JOIN BASE_USER u ON tk.F_ExpansionUserId = u.F_Id
                LEFT JOIN lq_mdxx md ON tk.F_StoreId = md.F_Id
                WHERE {eventFilter}
                    AND tk.F_ExpansionTime >= '{startTime.Value:yyyy-MM-dd HH:mm:ss}'
                    AND tk.F_ExpansionTime <= '{endTime.Value:yyyy-MM-dd HH:mm:ss}'
                    AND kd.F_IsEffective = 1
                    AND kd.sfyj > 10000
                    AND kd.kdrq >= '{startTime.Value:yyyy-MM-dd HH:mm:ss}'
                    AND kd.kdrq <= '{endTime.Value:yyyy-MM-dd HH:mm:ss}'
                ORDER BY kd.kdrq DESC";

            var detailsData = await _db.Ado.SqlQueryAsync<dynamic>(detailsSql);

            // 获取每个开单记录的项目明细
            var details = new List<BigOrderDetailOutput>();
            foreach (var detailItem in detailsData)
            {
                var billingId = detailItem.BillingId?.ToString();
                var items = new List<string>();

                if (!string.IsNullOrEmpty(billingId))
                {
                    var itemsSql = $@"
                        SELECT pxmc
                        FROM lq_kd_pxmx
                        WHERE glkdbh = '{billingId}'";
                    var itemsData = await _db.Ado.SqlQueryAsync<dynamic>(itemsSql);
                    foreach (var itemRow in itemsData)
                    {
                        var pxmc = itemRow.pxmc?.ToString();
                        if (!string.IsNullOrEmpty(pxmc))
                        {
                            items.Add(pxmc);
                        }
                    }
                }

                var expansionTime = detailItem.ExpansionTime != null ? Convert.ToDateTime(detailItem.ExpansionTime) : (DateTime?)null;
                var billingTime = detailItem.BillingTime != null ? Convert.ToDateTime(detailItem.BillingTime) : (DateTime?)null;

                details.Add(new BigOrderDetailOutput
                {
                    CustomerName = detailItem.CustomerName?.ToString() ?? "",
                    CustomerPhone = detailItem.CustomerPhone?.ToString() ?? "",
                    ExpansionUserName = detailItem.ExpansionUserName?.ToString() ?? "",
                    ExpansionTime = expansionTime,
                    BillingTime = billingTime,
                    BillingAmount = Convert.ToDecimal(detailItem.BillingAmount ?? 0),
                    StoreName = detailItem.StoreName?.ToString() ?? "",
                    Items = items
                });
            }

            return new BigOrderStatisticsOutput
            {
                Summary = new BigOrderSummaryOutput
                {
                    BigOrderCount = bigOrderCount,
                    BigOrderAmount = bigOrderAmount,
                    BigOrderAvgAmount = bigOrderAvgAmount,
                    BigOrderRate = bigOrderRate,
                    BigOrderAmountRate = bigOrderAmountRate,
                    BigOrderConversionRate = bigOrderConversionRate,
                    BigOrderVisitConversionRate = bigOrderVisitConversionRate
                },
                ByStore = byStore,
                ByEmployee = byEmployee,
                Details = details
            };
        }

        #endregion

        #region 获取拓客人员参与统计

        /// <summary>
        /// 获取拓客人员参与统计
        /// </summary>
        /// <param name="input">查询参数</param>
        /// <returns>人员参与统计数据列表</returns>
        [HttpPost("GetEmployeeParticipationStatistics")]
        public async Task<List<EmployeeParticipationStatisticsOutput>> GetEmployeeParticipationStatistics([FromBody] TkDashboardQueryInput input)
        {
            DateTime? startTime = input.StartTime;
            DateTime? endTime = input.EndTime;
            // 默认不显示战队，除非明确知道是全员活动或者我们决定始终显示


            if (!string.IsNullOrEmpty(input.EventId))
            {
                var eventInfo = await _db.Queryable<LqEventEntity>()
                    .Where(x => x.Id == input.EventId)
                    .FirstAsync();

                if (eventInfo == null) throw NCCException.Oh("活动不存在");
                if (startTime == null) startTime = eventInfo.StartTime;
                if (endTime == null) endTime = eventInfo.EndTime;
            }

            if (!startTime.HasValue || !endTime.HasValue)
            {
                throw NCCException.Oh("必须指定时间范围或活动ID");
            }

            var eventFilter = string.IsNullOrEmpty(input.EventId) ? "1=1" : $"tk.F_EventId = '{input.EventId}'";
            var startTimeStr = startTime.Value.ToString("yyyy-MM-dd HH:mm:ss");
            var endTimeStr = endTime.Value.ToString("yyyy-MM-dd HH:mm:ss");

            // 优化：使用一次SQL查询获取所有数据，避免循环查询
            var sql = $@"
                SELECT 
                    emp.EmployeeId,
                    emp.EmployeeName,
                    emp.DepartmentName,
                    emp.Position,
                    emp.StoreId,
                    emp.StoreName,
                    emp.TeamName,
                    emp.ExpansionCount,
                    emp.ExpansionCardCount,
                    COALESCE(visit.VisitCount, 0) as VisitCount,
                    COALESCE(billing.BillingCount, 0) as BillingCount,
                    COALESCE(billing.BillingAmount, 0) as BillingAmount,
                    COALESCE(bigOrder.BigOrderCount, 0) as BigOrderCount,
                    COALESCE(bigOrder.BigOrderAmount, 0) as BigOrderAmount
                FROM (
                    -- 基础员工拓客数据（按员工统计，不按TeamName分组）
                    SELECT 
                        tk.F_ExpansionUserId as EmployeeId,
                        COALESCE(u.F_REALNAME, '') as EmployeeName,
                        COALESCE(org.F_FullName, '') as DepartmentName,
                        COALESCE(u.F_GW, '') as Position,
                        COALESCE(MAX(tk.F_StoreId), '') as StoreId,
                        COALESCE(MAX(md.dm), '') as StoreName,
                        COALESCE(MAX(tk.F_TeamName), '') as TeamName,
                        COUNT(DISTINCT tk.F_MemberId) as ExpansionCount,
                        COALESCE(SUM(CAST(tk.F_BuyNumber AS SIGNED)), 0) as ExpansionCardCount
                    FROM lq_tkjlb tk
                    LEFT JOIN BASE_USER u ON tk.F_ExpansionUserId = u.F_Id
                    LEFT JOIN base_organize org ON u.F_OrganizeId = org.F_Id
                    LEFT JOIN lq_mdxx md ON tk.F_StoreId = md.F_Id
                    WHERE {eventFilter}
                        AND tk.F_ExpansionTime >= '{startTimeStr}'
                        AND tk.F_ExpansionTime <= '{endTimeStr}'
                    GROUP BY tk.F_ExpansionUserId, u.F_REALNAME, org.F_FullName, u.F_GW
                ) emp
                LEFT JOIN (
                    -- 到店人数统计（一个人多次到店只算一次）
                    -- 先找出该员工拓客的所有会员（去重），然后统计这些会员中有到店记录的会员数
                    -- 确保到店时间在首次拓客时间之后
                    SELECT 
                        emp_members.EmployeeId,
                        COUNT(DISTINCT CASE WHEN xh.hy IS NOT NULL THEN emp_members.MemberId END) as VisitCount
                    FROM (
                        -- 先获取该员工拓客的所有会员（去重），并记录每个会员的首次拓客时间
                        SELECT 
                            tk.F_ExpansionUserId as EmployeeId,
                            tk.F_MemberId as MemberId,
                            MIN(tk.F_ExpansionTime) as FirstExpansionTime
                        FROM lq_tkjlb tk
                        WHERE {eventFilter}
                            AND tk.F_ExpansionTime >= '{startTimeStr}'
                            AND tk.F_ExpansionTime <= '{endTimeStr}'
                            AND tk.F_MemberId IS NOT NULL
                        GROUP BY tk.F_ExpansionUserId, tk.F_MemberId
                    ) emp_members
                    LEFT JOIN lq_xh_hyhk xh ON emp_members.MemberId = xh.hy 
                        AND xh.F_IsEffective = 1
                        AND xh.hksj >= emp_members.FirstExpansionTime
                        AND xh.hksj >= '{startTimeStr}'
                        AND xh.hksj <= '{endTimeStr}'
                    GROUP BY emp_members.EmployeeId
                ) visit ON emp.EmployeeId = visit.EmployeeId
                LEFT JOIN (
                    -- 开单人数和金额统计
                    SELECT 
                        tk.F_ExpansionUserId as EmployeeId,
                        COUNT(DISTINCT tk.F_MemberId) as BillingCount,
                        COALESCE(SUM(kd.sfyj), 0) as BillingAmount
                    FROM lq_tkjlb tk
                    INNER JOIN lq_kd_kdjlb kd ON tk.F_MemberId = kd.kdhy
                    WHERE {eventFilter}
                        AND tk.F_ExpansionTime >= '{startTimeStr}'
                        AND tk.F_ExpansionTime <= '{endTimeStr}'
                        AND kd.F_IsEffective = 1
                        AND kd.sfyj > 0
                        AND kd.kdrq >= '{startTimeStr}'
                        AND kd.kdrq <= '{endTimeStr}'
                    GROUP BY tk.F_ExpansionUserId
                ) billing ON emp.EmployeeId = billing.EmployeeId
                LEFT JOIN (
                    -- 大单数量和金额统计
                    SELECT 
                        tk.F_ExpansionUserId as EmployeeId,
                        COUNT(DISTINCT tk.F_MemberId) as BigOrderCount,
                        COALESCE(SUM(kd.sfyj), 0) as BigOrderAmount
                    FROM lq_tkjlb tk
                    INNER JOIN lq_kd_kdjlb kd ON tk.F_MemberId = kd.kdhy
                    WHERE {eventFilter}
                        AND tk.F_ExpansionTime >= '{startTimeStr}'
                        AND tk.F_ExpansionTime <= '{endTimeStr}'
                        AND kd.F_IsEffective = 1
                        AND kd.sfyj > 10000
                        AND kd.kdrq >= '{startTimeStr}'
                        AND kd.kdrq <= '{endTimeStr}'
                    GROUP BY tk.F_ExpansionUserId
                ) bigOrder ON emp.EmployeeId = bigOrder.EmployeeId
                ORDER BY emp.ExpansionCount DESC";

            var employeeData = await _db.Ado.SqlQueryAsync<dynamic>(sql);
            var result = new List<EmployeeParticipationStatisticsOutput>();

            foreach (var emp in employeeData)
            {
                var employeeId = emp.EmployeeId?.ToString() ?? "";
                int expansionCount = 0;
                try { expansionCount = Convert.ToInt32(emp.ExpansionCount); } catch { }

                int expansionCardCountValue = 0;
                try { expansionCardCountValue = Convert.ToInt32(emp.ExpansionCardCount); } catch { }

                int visitCount = 0;
                try { visitCount = Convert.ToInt32(emp.VisitCount ?? 0); } catch { }

                int billingCount = 0;
                decimal billingAmount = 0m;
                try { billingCount = Convert.ToInt32(emp.BillingCount ?? 0); } catch { }
                try { billingAmount = Convert.ToDecimal(emp.BillingAmount ?? 0); } catch { }

                int bigOrderCount = 0;
                decimal bigOrderAmount = 0m;
                try { bigOrderCount = Convert.ToInt32(emp.BigOrderCount ?? 0); } catch { }
                try { bigOrderAmount = Convert.ToDecimal(emp.BigOrderAmount ?? 0); } catch { }

                decimal visitRate = expansionCount > 0 ? Math.Round(visitCount * 100m / expansionCount, 2) : 0m;
                decimal billingConversionRate = expansionCount > 0 ? Math.Round(billingCount * 100m / expansionCount, 2) : 0m;

                result.Add(new EmployeeParticipationStatisticsOutput
                {
                    EmployeeId = employeeId,
                    EmployeeName = emp.EmployeeName?.ToString() ?? "",
                    DepartmentName = emp.DepartmentName?.ToString() ?? "",
                    Position = emp.Position?.ToString() ?? "",
                    StoreId = emp.StoreId?.ToString() ?? "",
                    StoreName = emp.StoreName?.ToString() ?? "",
                    TeamName = emp.TeamName?.ToString() ?? "",
                    ExpansionCount = expansionCount,
                    ExpansionCardCount = expansionCardCountValue,
                    VisitCount = visitCount,
                    VisitRate = visitRate,
                    BillingCount = billingCount,
                    BillingAmount = billingAmount,
                    BillingConversionRate = billingConversionRate,
                    BigOrderCount = bigOrderCount,
                    BigOrderAmount = bigOrderAmount
                });
            }

            return result;
        }

        #endregion

        #region 获取到店转化分析

        /// <summary>
        /// 获取到店转化分析
        /// </summary>
        /// <remarks>
        /// 获取到店转化分析数据，包括整体到店率、平均到店间隔、到店间隔分布等
        /// 
        /// 示例请求：
        /// ```json
        /// {
        ///   "eventId": "活动ID",
        ///   "startTime": "2025-01-01",
        ///   "endTime": "2025-01-31"
        /// }
        /// ```
        /// 
        /// 到店定义：有耗卡记录即视为到店
        /// </remarks>
        /// <param name="input">查询参数</param>
        /// <returns>到店转化分析数据</returns>
        /// <response code="200">成功返回到店转化分析数据</response>
        /// <response code="400">请求参数错误</response>
        /// <response code="500">服务器错误</response>
        [HttpPost("GetVisitConversionAnalysis")]
        public async Task<VisitConversionAnalysisOutput> GetVisitConversionAnalysis([FromBody] TkDashboardQueryInput input)
        {
            DateTime? startTime = input.StartTime;
            DateTime? endTime = input.EndTime;

            if (!string.IsNullOrEmpty(input.EventId))
            {
                var eventInfo = await _db.Queryable<LqEventEntity>()
                    .Where(x => x.Id == input.EventId)
                    .FirstAsync();

                if (eventInfo == null) throw NCCException.Oh("活动不存在");
                if (startTime == null) startTime = eventInfo.StartTime;
                if (endTime == null) endTime = eventInfo.EndTime;
            }

            if (!startTime.HasValue || !endTime.HasValue)
            {
                throw NCCException.Oh("必须指定时间范围或活动ID");
            }

            var eventFilter = string.IsNullOrEmpty(input.EventId) ? "1=1" : $"tk.F_EventId = '{input.EventId}'";

            // 获取拓客到首次耗卡的时间间隔数据
            var visitIntervalSql = $@"
                SELECT 
                    tk.F_MemberId as MemberId,
                    tk.F_ExpansionTime as ExpansionTime,
                    MIN(xh.hksj) as FirstVisitTime,
                    DATEDIFF(MIN(xh.hksj), tk.F_ExpansionTime) as IntervalDays
                FROM lq_tkjlb tk
                INNER JOIN lq_xh_hyhk xh ON tk.F_MemberId = xh.hy AND xh.F_IsEffective = 1
                WHERE {eventFilter}
                    AND tk.F_ExpansionTime >= '{startTime.Value:yyyy-MM-dd HH:mm:ss}'
                    AND tk.F_ExpansionTime <= '{endTime.Value:yyyy-MM-dd HH:mm:ss}'
                    AND xh.hksj >= '{startTime.Value:yyyy-MM-dd HH:mm:ss}'
                    AND xh.hksj <= '{endTime.Value:yyyy-MM-dd HH:mm:ss}'
                GROUP BY tk.F_MemberId, tk.F_ExpansionTime";

            var visitIntervalData = await _db.Ado.SqlQueryAsync<dynamic>(visitIntervalSql);

            var totalExpansionCount = await _db.Queryable<LqTkjlbEntity>()
                .WhereIF(!string.IsNullOrEmpty(input.EventId), x => x.EventId == input.EventId)
                .Where(x => x.ExpansionTime >= startTime.Value && x.ExpansionTime <= endTime.Value)
                .GroupBy(x => x.MemberId)
                .Select(x => x.MemberId)
                .CountAsync();

            var totalVisitCount = visitIntervalData.Count;
            var overallVisitRate = totalExpansionCount > 0 ? Math.Round(totalVisitCount * 100m / totalExpansionCount, 2) : 0m;

            // 计算平均到店间隔和间隔分布
            var intervals = new List<int>();
            foreach (var intervalItem in visitIntervalData)
            {
                if (intervalItem.IntervalDays != null)
                {
                    try
                    {
                        var interval = Convert.ToInt32(intervalItem.IntervalDays);
                        if (interval >= 0) // 过滤异常数据（拓客时间晚于耗卡时间）
                        {
                            intervals.Add(interval);
                        }
                    }
                    catch { }
                }
            }

            decimal averageVisitInterval = 0m;
            if (intervals.Any())
            {
                var sum = intervals.Sum();
                var count = intervals.Count;
                averageVisitInterval = Math.Round(sum / (decimal)count, 2);
            }

            var distribution = new VisitIntervalDistributionOutput
            {
                Within1Day = intervals.Count(x => x >= 0 && x <= 1),
                Within3Days = intervals.Count(x => x > 1 && x <= 3),
                Within7Days = intervals.Count(x => x > 3 && x <= 7),
                Within15Days = intervals.Count(x => x > 7 && x <= 15),
                Within30Days = intervals.Count(x => x > 15 && x <= 30),
                Over30Days = intervals.Count(x => x > 30)
            };

            // 按门店统计到店率
            var byStoreSql = $@"
                SELECT 
                    tk.F_StoreId as StoreId,
                    COALESCE(md.dm, '') as StoreName,
                    COUNT(DISTINCT tk.F_MemberId) as ExpansionCount,
                    COUNT(DISTINCT CASE WHEN xh.hy IS NOT NULL THEN tk.F_MemberId END) as VisitCount
                FROM lq_tkjlb tk
                LEFT JOIN lq_mdxx md ON tk.F_StoreId = md.F_Id
                LEFT JOIN lq_xh_hyhk xh ON tk.F_MemberId = xh.hy AND xh.F_IsEffective = 1 
                    AND xh.hksj >= '{startTime.Value:yyyy-MM-dd HH:mm:ss}' 
                    AND xh.hksj <= '{endTime.Value:yyyy-MM-dd HH:mm:ss}'
                WHERE {eventFilter}
                    AND tk.F_ExpansionTime >= '{startTime.Value:yyyy-MM-dd HH:mm:ss}'
                    AND tk.F_ExpansionTime <= '{endTime.Value:yyyy-MM-dd HH:mm:ss}'
                GROUP BY tk.F_StoreId, md.dm";

            var byStoreData = await _db.Ado.SqlQueryAsync<dynamic>(byStoreSql);
            var byStore = byStoreData.Select(x =>
            {
                int expansionCount = 0;
                int visitCount = 0;
                try { expansionCount = Convert.ToInt32(x.ExpansionCount ?? 0); } catch { }
                try { visitCount = Convert.ToInt32(x.VisitCount ?? 0); } catch { }

                return new VisitByStoreOutput
                {
                    StoreId = x.StoreId?.ToString() ?? "",
                    StoreName = x.StoreName?.ToString() ?? "",
                    ExpansionCount = expansionCount,
                    VisitCount = visitCount,
                    VisitRate = expansionCount > 0 ? Math.Round(visitCount * 100m / expansionCount, 2) : 0m,
                    AverageVisitInterval = 0 // 后续如果需要可补充逻辑
                };
            }).ToList();

            // 按员工统计到店率
            var byEmployeeSql = $@"
                SELECT 
                    tk.F_ExpansionUserId as EmployeeId,
                    COALESCE(u.F_REALNAME, '') as EmployeeName,
                    COUNT(DISTINCT tk.F_MemberId) as ExpansionCount,
                    COUNT(DISTINCT CASE WHEN xh.hy IS NOT NULL THEN tk.F_MemberId END) as VisitCount
                FROM lq_tkjlb tk
                LEFT JOIN BASE_USER u ON tk.F_ExpansionUserId = u.F_Id
                LEFT JOIN lq_xh_hyhk xh ON tk.F_MemberId = xh.hy AND xh.F_IsEffective = 1 
                    AND xh.hksj >= '{startTime.Value:yyyy-MM-dd HH:mm:ss}' 
                    AND xh.hksj <= '{endTime.Value:yyyy-MM-dd HH:mm:ss}'
                WHERE {eventFilter}
                    AND tk.F_ExpansionTime >= '{startTime.Value:yyyy-MM-dd HH:mm:ss}'
                    AND tk.F_ExpansionTime <= '{endTime.Value:yyyy-MM-dd HH:mm:ss}'
                GROUP BY tk.F_ExpansionUserId, u.F_REALNAME";

            var byEmployeeData = await _db.Ado.SqlQueryAsync<dynamic>(byEmployeeSql);
            var byEmployee = byEmployeeData.Select(x =>
            {
                int expansionCount = 0;
                int visitCount = 0;
                try { expansionCount = Convert.ToInt32(x.ExpansionCount ?? 0); } catch { }
                try { visitCount = Convert.ToInt32(x.VisitCount ?? 0); } catch { }

                return new VisitByEmployeeOutput
                {
                    EmployeeId = x.EmployeeId?.ToString() ?? "",
                    EmployeeName = x.EmployeeName?.ToString() ?? "",
                    ExpansionCount = expansionCount,
                    VisitCount = visitCount,
                    VisitRate = expansionCount > 0 ? Math.Round(visitCount * 100m / expansionCount, 2) : 0m,
                    AverageVisitInterval = 0 // 后续如果需要可补充逻辑
                };
            }).ToList();

            return new VisitConversionAnalysisOutput
            {
                TotalExpansionCount = totalExpansionCount,
                TotalVisitCount = totalVisitCount,
                OverallVisitRate = overallVisitRate,
                AverageVisitInterval = averageVisitInterval,
                VisitIntervalDistribution = distribution,
                ByStore = byStore,
                ByEmployee = byEmployee
            };
        }

        #endregion

        #region 获取漏斗统计数据

        /// <summary>
        /// 获取漏斗统计数据
        /// </summary>
        /// <param name="input">查询参数</param>
        /// <returns>漏斗统计数据</returns>
        [HttpPost("GetFunnelStatistics")]
        public async Task<dynamic> GetFunnelStatistics([FromBody] TkDashboardQueryInput input)
        {
            DateTime? startTime = input.StartTime;
            DateTime? endTime = input.EndTime;

            if (!string.IsNullOrEmpty(input.EventId))
            {
                var eventInfo = await _db.Queryable<LqEventEntity>()
                    .Where(x => x.Id == input.EventId)
                    .FirstAsync();

                if (eventInfo == null) throw NCCException.Oh("活动不存在");
                if (startTime == null) startTime = eventInfo.StartTime;
                if (endTime == null) endTime = eventInfo.EndTime;
            }

            if (!startTime.HasValue || !endTime.HasValue)
            {
                throw NCCException.Oh("必须指定时间范围或活动ID");
            }

            var eventFilter = string.IsNullOrEmpty(input.EventId) ? "1=1" : $"tk.F_EventId = '{input.EventId}'";

            // 1. 拓客人数
            var expansionCountSql = $@"
                SELECT COUNT(DISTINCT F_MemberId) as ExpansionCount
                FROM lq_tkjlb tk
                WHERE {eventFilter}
                    AND tk.F_ExpansionTime >= '{startTime.Value:yyyy-MM-dd HH:mm:ss}'
                    AND tk.F_ExpansionTime <= '{endTime.Value:yyyy-MM-dd HH:mm:ss}'";
            var expansionCountResult = await _db.Ado.SqlQuerySingleAsync<dynamic>(expansionCountSql);
            var totalExpansionCount = 0;
            if (expansionCountResult != null)
            {
                try { totalExpansionCount = Convert.ToInt32(expansionCountResult.ExpansionCount); } catch { }
            }

            // 2. 到店人数
            var visitSql = $@"
                SELECT COUNT(DISTINCT tk.F_MemberId) as VisitCount
                FROM lq_tkjlb tk
                INNER JOIN lq_xh_hyhk xh ON tk.F_MemberId = xh.hy AND xh.F_IsEffective = 1
                WHERE {eventFilter}
                    AND tk.F_ExpansionTime >= '{startTime.Value:yyyy-MM-dd HH:mm:ss}'
                    AND tk.F_ExpansionTime <= '{endTime.Value:yyyy-MM-dd HH:mm:ss}'
                    AND xh.hksj >= '{startTime.Value:yyyy-MM-dd HH:mm:ss}'
                    AND xh.hksj <= '{endTime.Value:yyyy-MM-dd HH:mm:ss}'";
            var visitResult = await _db.Ado.SqlQuerySingleAsync<dynamic>(visitSql);
            var totalVisitCount = 0;
            try { totalVisitCount = Convert.ToInt32(visitResult?.VisitCount ?? 0); } catch { }

            // 3. 开单人数
            var billingSql = $@"
                SELECT COUNT(DISTINCT tk.F_MemberId) as BillingCount
                FROM lq_tkjlb tk
                INNER JOIN lq_kd_kdjlb kd ON tk.F_MemberId = kd.kdhy
                WHERE {eventFilter}
                    AND tk.F_ExpansionTime >= '{startTime.Value:yyyy-MM-dd HH:mm:ss}'
                    AND tk.F_ExpansionTime <= '{endTime.Value:yyyy-MM-dd HH:mm:ss}'
                    AND kd.F_IsEffective = 1
                    AND kd.sfyj > 0
                    AND kd.kdrq >= '{startTime.Value:yyyy-MM-dd HH:mm:ss}'
                    AND kd.kdrq <= '{endTime.Value:yyyy-MM-dd HH:mm:ss}'";
            var billingResult = await _db.Ado.SqlQuerySingleAsync<dynamic>(billingSql);
            var totalBillingCount = 0;
            try { totalBillingCount = Convert.ToInt32(billingResult?.BillingCount ?? 0); } catch { }

            // 4. 大单人数
            var bigOrderSql = $@"
                SELECT COUNT(DISTINCT tk.F_MemberId) as BigOrderCount
                FROM lq_tkjlb tk
                INNER JOIN lq_kd_kdjlb kd ON tk.F_MemberId = kd.kdhy
                WHERE {eventFilter}
                    AND tk.F_ExpansionTime >= '{startTime.Value:yyyy-MM-dd HH:mm:ss}'
                    AND tk.F_ExpansionTime <= '{endTime.Value:yyyy-MM-dd HH:mm:ss}'
                    AND kd.F_IsEffective = 1
                    AND kd.sfyj > 10000
                    AND kd.kdrq >= '{startTime.Value:yyyy-MM-dd HH:mm:ss}'
                    AND kd.kdrq <= '{endTime.Value:yyyy-MM-dd HH:mm:ss}'";
            var bigOrderResult = await _db.Ado.SqlQuerySingleAsync<dynamic>(bigOrderSql);
            var totalBigOrderCount = 0;
            try { totalBigOrderCount = Convert.ToInt32(bigOrderResult?.BigOrderCount ?? 0); } catch { }

            var visitRate = totalExpansionCount > 0 ? Math.Round(totalVisitCount * 100m / totalExpansionCount, 2) : 0m;
            var billingRate = totalExpansionCount > 0 ? Math.Round(totalBillingCount * 100m / totalExpansionCount, 2) : 0m;
            var bigOrderRate = totalExpansionCount > 0 ? Math.Round(totalBigOrderCount * 100m / totalExpansionCount, 2) : 0m;

            return new
            {
                TotalExpansionCount = totalExpansionCount,
                TotalVisitCount = totalVisitCount,
                BillingCount = totalBillingCount,
                BigOrderCount = totalBigOrderCount,
                VisitRate = visitRate,
                BillingRate = billingRate,
                BigOrderRate = bigOrderRate
            };
        }

        #endregion

        #region 流失节点分析

        /// <summary>
        /// 获取流失节点分析数据
        /// </summary>
        /// <remarks>
        /// 分析拓客转化链路中各节点的流失情况，包括拓客未邀约、邀约未预约、预约未到店、到店未开单四个流失节点
        /// 
        /// 示例请求：
        /// ```json
        /// {
        ///   "eventId": "活动ID",
        ///   "startTime": "2025-01-01",
        ///   "endTime": "2025-01-31"
        /// }
        /// ```
        /// 
        /// 参数说明：
        /// - eventId: 拓客活动ID（必填）
        /// - startTime: 开始时间（可选，如不填则使用活动开始时间）
        /// - endTime: 结束时间（可选，如不填则使用活动结束时间）
        /// </remarks>
        /// <param name="input">查询参数</param>
        /// <returns>流失节点分析数据</returns>
        /// <response code="200">成功返回流失节点分析数据</response>
        /// <response code="400">请求参数错误</response>
        /// <response code="500">服务器错误</response>
        [HttpPost("GetLossNodeAnalysis")]
        public async Task<LossNodeAnalysisOutput> GetLossNodeAnalysis([FromBody] TkDashboardQueryInput input)
        {
            DateTime? startTime = input.StartTime;
            DateTime? endTime = input.EndTime;

            if (!string.IsNullOrEmpty(input.EventId))
            {
                // 获取活动信息
                var eventInfo = await _db.Queryable<LqEventEntity>()
                    .Where(x => x.Id == input.EventId)
                    .FirstAsync();

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

                if (startTime == null) startTime = eventInfo.StartTime;
                if (endTime == null) endTime = eventInfo.EndTime;
            }

            if (!startTime.HasValue || !endTime.HasValue)
            {
                throw NCCException.Oh("必须指定时间范围或活动ID");
            }

            var eventFilter = string.IsNullOrEmpty(input.EventId) ? "1=1" : $"tk.F_EventId = '{input.EventId}'";
            var startTimeStr = startTime.Value.ToString("yyyy-MM-dd HH:mm:ss");
            var endTimeStr = endTime.Value.ToString("yyyy-MM-dd HH:mm:ss");

            // 性能优化：使用子查询方式分别统计各节点，避免复杂的多表LEFT JOIN
            // 这样可以更好地利用索引，提高查询性能

            // 1. 统计拓客人数（基础数据）
            var expansionSql = $@"
                SELECT COUNT(DISTINCT F_MemberId) as ExpansionCount
                FROM lq_tkjlb tk
                WHERE {eventFilter}
                    AND tk.F_ExpansionTime >= '{startTimeStr}'
                    AND tk.F_ExpansionTime <= '{endTimeStr}'";
            var expansionResult = await _db.Ado.SqlQuerySingleAsync<dynamic>(expansionSql);
            var expansionCount = Convert.ToInt32(expansionResult?.ExpansionCount ?? 0);

            // 2. 统计邀约人数（通过拓客编号或会员ID关联）
            var inviteSql = $@"
                SELECT COUNT(DISTINCT tk.F_MemberId) as InviteCount
                FROM lq_tkjlb tk
                INNER JOIN lq_yaoyjl yy ON (yy.tkbh = tk.F_Id OR yy.yykh = tk.F_MemberId)
                WHERE {eventFilter}
                    AND tk.F_ExpansionTime >= '{startTimeStr}'
                    AND tk.F_ExpansionTime <= '{endTimeStr}'
                    AND yy.yysj >= '{startTimeStr}'
                    AND yy.yysj <= '{endTimeStr}'";
            var inviteResult = await _db.Ado.SqlQuerySingleAsync<dynamic>(inviteSql);
            var inviteCount = Convert.ToInt32(inviteResult?.InviteCount ?? 0);

            // 3. 统计预约人数（通过会员ID关联，包括通过邀约产生的预约和直接预约）
            var appointmentSql = $@"
                SELECT COUNT(DISTINCT tk.F_MemberId) as AppointmentCount
                FROM lq_tkjlb tk
                INNER JOIN lq_yyjl yyjl ON yyjl.gk = tk.F_MemberId
                    AND yyjl.yysj >= '{startTimeStr}'
                    AND yyjl.yysj <= '{endTimeStr}'
                WHERE {eventFilter}
                    AND tk.F_ExpansionTime >= '{startTimeStr}'
                    AND tk.F_ExpansionTime <= '{endTimeStr}'";
            var appointmentResult = await _db.Ado.SqlQuerySingleAsync<dynamic>(appointmentSql);
            var appointmentCount = Convert.ToInt32(appointmentResult?.AppointmentCount ?? 0);

            // 4. 统计到店人数（通过会员ID关联）
            var visitSql = $@"
                SELECT COUNT(DISTINCT tk.F_MemberId) as VisitCount
                FROM lq_tkjlb tk
                INNER JOIN lq_xh_hyhk hk ON hk.hyzh = tk.F_MemberId
                    AND hk.F_IsEffective = 1
                    AND hk.hksj >= '{startTimeStr}'
                    AND hk.hksj <= '{endTimeStr}'
                WHERE {eventFilter}
                    AND tk.F_ExpansionTime >= '{startTimeStr}'
                    AND tk.F_ExpansionTime <= '{endTimeStr}'";
            var visitResult = await _db.Ado.SqlQuerySingleAsync<dynamic>(visitSql);
            var visitCount = Convert.ToInt32(visitResult?.VisitCount ?? 0);

            // 5. 统计开单人数（通过会员ID关联，金额>0）
            var billingSql = $@"
                SELECT COUNT(DISTINCT tk.F_MemberId) as BillingCount
                FROM lq_tkjlb tk
                INNER JOIN lq_kd_kdjlb kd ON kd.kdhy = tk.F_MemberId
                    AND kd.F_IsEffective = 1
                    AND kd.sfyj > 0
                    AND kd.kdrq >= '{startTimeStr}'
                    AND kd.kdrq <= '{endTimeStr}'
                WHERE {eventFilter}
                    AND tk.F_ExpansionTime >= '{startTimeStr}'
                    AND tk.F_ExpansionTime <= '{endTimeStr}'";
            var billingResult = await _db.Ado.SqlQuerySingleAsync<dynamic>(billingSql);
            var billingCount = Convert.ToInt32(billingResult?.BillingCount ?? 0);

            // 6. 计算流失节点数据
            var loss1Count = expansionCount - inviteCount; // 拓客未邀约
            var loss2Count = inviteCount - appointmentCount; // 邀约未预约
            var loss3Count = appointmentCount - visitCount; // 预约未到店
            var loss4Count = visitCount - billingCount; // 到店未开单

            // 注意：预约未到店和到店未开单的计算需要更精确，因为预约和到店、到店和开单可能不是严格的包含关系
            // 需要计算交集来准确统计流失
            // 7. 精确计算预约且到店人数（预约和到店的交集）
            var appointmentVisitSql = $@"
                SELECT COUNT(DISTINCT tk.F_MemberId) as AppointmentVisitCount
                FROM lq_tkjlb tk
                INNER JOIN lq_yyjl yyjl ON yyjl.gk = tk.F_MemberId
                    AND yyjl.yysj >= '{startTimeStr}'
                    AND yyjl.yysj <= '{endTimeStr}'
                INNER JOIN lq_xh_hyhk hk ON hk.hyzh = tk.F_MemberId
                    AND hk.F_IsEffective = 1
                    AND hk.hksj >= '{startTimeStr}'
                    AND hk.hksj <= '{endTimeStr}'
                WHERE {eventFilter}
                    AND tk.F_ExpansionTime >= '{startTimeStr}'
                    AND tk.F_ExpansionTime <= '{endTimeStr}'";
            var appointmentVisitResult = await _db.Ado.SqlQuerySingleAsync<dynamic>(appointmentVisitSql);
            var appointmentVisitCount = Convert.ToInt32(appointmentVisitResult?.AppointmentVisitCount ?? 0);
            loss3Count = appointmentCount - appointmentVisitCount; // 预约未到店 = 预约人数 - 预约且到店人数

            // 8. 精确计算到店且开单人数
            var visitBillingSql = $@"
                SELECT COUNT(DISTINCT tk.F_MemberId) as VisitBillingCount
                FROM lq_tkjlb tk
                INNER JOIN lq_xh_hyhk hk ON hk.hyzh = tk.F_MemberId
                    AND hk.F_IsEffective = 1
                    AND hk.hksj >= '{startTimeStr}'
                    AND hk.hksj <= '{endTimeStr}'
                INNER JOIN lq_kd_kdjlb kd ON kd.kdhy = tk.F_MemberId
                    AND kd.F_IsEffective = 1
                    AND kd.sfyj > 0
                    AND kd.kdrq >= '{startTimeStr}'
                    AND kd.kdrq <= '{endTimeStr}'
                WHERE {eventFilter}
                    AND tk.F_ExpansionTime >= '{startTimeStr}'
                    AND tk.F_ExpansionTime <= '{endTimeStr}'";
            var visitBillingResult = await _db.Ado.SqlQuerySingleAsync<dynamic>(visitBillingSql);
            var visitBillingCount = Convert.ToInt32(visitBillingResult?.VisitBillingCount ?? 0);
            loss4Count = visitCount - visitBillingCount; // 到店未开单 = 到店人数 - 到店且开单人数

            // 确保流失数量不为负数
            loss1Count = Math.Max(0, loss1Count);
            loss2Count = Math.Max(0, loss2Count);
            loss3Count = Math.Max(0, loss3Count);
            loss4Count = Math.Max(0, loss4Count);

            // 计算流失率
            var loss1Rate = expansionCount > 0 ? Math.Round(loss1Count * 100m / expansionCount, 2) : 0m;
            var loss2Rate = inviteCount > 0 ? Math.Round(loss2Count * 100m / inviteCount, 2) : 0m;
            var loss3Rate = appointmentCount > 0 ? Math.Round(loss3Count * 100m / appointmentCount, 2) : 0m;
            var loss4Rate = visitCount > 0 ? Math.Round(loss4Count * 100m / visitCount, 2) : 0m;

            // 计算流失占比（占拓客人数的百分比）
            var loss1Percentage = expansionCount > 0 ? Math.Round(loss1Count * 100m / expansionCount, 2) : 0m;
            var loss2Percentage = expansionCount > 0 ? Math.Round(loss2Count * 100m / expansionCount, 2) : 0m;
            var loss3Percentage = expansionCount > 0 ? Math.Round(loss3Count * 100m / expansionCount, 2) : 0m;
            var loss4Percentage = expansionCount > 0 ? Math.Round(loss4Count * 100m / expansionCount, 2) : 0m;

            // 计算转化率
            var expansionToInviteRate = expansionCount > 0 ? Math.Round(inviteCount * 100m / expansionCount, 2) : 0m;
            var inviteToAppointmentRate = inviteCount > 0 ? Math.Round(appointmentCount * 100m / inviteCount, 2) : 0m;
            var appointmentToVisitRate = appointmentCount > 0 ? Math.Round(visitCount * 100m / appointmentCount, 2) : 0m;
            var visitToBillingRate = visitCount > 0 ? Math.Round(billingCount * 100m / visitCount, 2) : 0m;
            var overallVisitRate = expansionCount > 0 ? Math.Round(visitCount * 100m / expansionCount, 2) : 0m;
            var overallBillingRate = expansionCount > 0 ? Math.Round(billingCount * 100m / expansionCount, 2) : 0m;

            return new LossNodeAnalysisOutput
            {
                NodeCount = new NodeCountOutput
                {
                    ExpansionCount = expansionCount,
                    InviteCount = inviteCount,
                    AppointmentCount = appointmentCount,
                    VisitCount = visitCount,
                    BillingCount = billingCount
                },
                LossNodes = new List<LossNodeOutput>
                {
                    new LossNodeOutput
                    {
                        NodeIndex = 1,
                        NodeName = "拓客未邀约",
                        LossCount = loss1Count,
                        LossRate = loss1Rate,
                        LossPercentage = loss1Percentage
                    },
                    new LossNodeOutput
                    {
                        NodeIndex = 2,
                        NodeName = "邀约未预约",
                        LossCount = loss2Count,
                        LossRate = loss2Rate,
                        LossPercentage = loss2Percentage
                    },
                    new LossNodeOutput
                    {
                        NodeIndex = 3,
                        NodeName = "预约未到店",
                        LossCount = loss3Count,
                        LossRate = loss3Rate,
                        LossPercentage = loss3Percentage
                    },
                    new LossNodeOutput
                    {
                        NodeIndex = 4,
                        NodeName = "到店未开单",
                        LossCount = loss4Count,
                        LossRate = loss4Rate,
                        LossPercentage = loss4Percentage
                    }
                },
                ConversionRate = new ConversionRateOutput
                {
                    ExpansionToInviteRate = expansionToInviteRate,
                    InviteToAppointmentRate = inviteToAppointmentRate,
                    AppointmentToVisitRate = appointmentToVisitRate,
                    VisitToBillingRate = visitToBillingRate,
                    OverallVisitRate = overallVisitRate,
                    OverallBillingRate = overallBillingRate
                }
            };
        }

        #endregion
    }
}