antissoft / lvqianmeiye_ERP

using Microsoft.AspNetCore.Mvc;
using NCC.Common.Filter;
using NCC.Dependency;
using NCC.DynamicApiController;
using NCC.Extend.Entitys.Dto.LqShareStatisticsTechDept;
using NCC.Extend.Entitys.lq_share_statistics_tech_dept;
using NCC.Extend.Entitys.lq_md_target;
using NCC.Extend.Entitys.lq_mdxx;
using NCC.Extend.Entitys.lq_kd_pxmx;
using NCC.Extend.Entitys.lq_kd_kdjlb;
using NCC.Extend.Entitys.lq_hytk_jksyj;
using NCC.Extend.Entitys.lq_xh_jksyj;
using NCC.Extend.Entitys.lq_xh_kjbsyj;
using NCC.Extend.Entitys.lq_tech_teacher_salary_statistics;
using NCC.Extend.Entitys.lq_tech_general_manager_salary_statistics;
using NCC.Extend.Entitys.lq_md_general_manager_lifeline;
using NCC.System.Entitys.Permission;
using NCC.Extend.Entitys;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Yitter.IdGenerator;

namespace NCC.Extend
{
    /// <summary>
    /// 科技部股份统计服务
    /// </summary>
    [ApiDescriptionSettings(Tag = "科技部股份统计服务", Name = "LqShareStatisticsTechDept", Order = 401)]
    [Route("api/Extend/[controller]")]
    public class LqShareStatisticsTechDeptService : IDynamicApiController, ITransient
    {
        private readonly ISqlSugarClient _db;

        public LqShareStatisticsTechDeptService(ISqlSugarClient db)
        {
            _db = db;
        }

        /// <summary>
        /// 生成科技部股份统计数据
        /// </summary>
        /// <param name="input">生成参数</param>
        /// <returns>生成结果</returns>
        [HttpPost("generate")]
        public async Task<dynamic> GenerateStatistics([FromBody] ShareStatisticsTechDeptGenerateInput input)
        {
            if (string.IsNullOrEmpty(input.StatisticsMonth) || input.StatisticsMonth.Length != 6)
            {
                return new { code = 500, msg = "统计月份格式错误,应为 YYYYMM" };
            }

            var year = int.Parse(input.StatisticsMonth.Substring(0, 4));
            var month = int.Parse(input.StatisticsMonth.Substring(4, 2));
            var startDate = new DateTime(year, month, 1);
            var endDate = startDate.AddMonths(1); // 下个月的第一天，用于 < 比较

            // 确定要生成的部门列表
            var departments = new List<string>();
            if (!string.IsNullOrEmpty(input.DepartmentName))
            {
                departments.Add(input.DepartmentName);
            }
            else
            {
                departments.Add("科技一部");
                departments.Add("科技二部");
            }

            var generatedCount = 0;
            var updatedCount = 0;

            foreach (var deptName in departments)
            {
                // 检查是否已存在
                var existing = await _db.Queryable<LqShareStatisticsTechDeptEntity>()
                    .FirstAsync(x => x.DepartmentName == deptName && x.StatisticsMonth == input.StatisticsMonth);

                var entity = existing ?? new LqShareStatisticsTechDeptEntity
                {
                    Id = YitIdHelper.NextId().ToString(),
                    DepartmentName = deptName,
                    StatisticsMonth = input.StatisticsMonth,
                    IsEffective = 1,
                    CreateTime = DateTime.Now,
                    CreateUser = "System"
                };

                // 计算各项数据
                await CalculateIncome(entity, deptName, startDate, endDate, input.StatisticsMonth);
                await CalculateCost(entity, deptName, startDate, endDate, input.StatisticsMonth);
                CalculateProfit(entity);

                entity.UpdateTime = DateTime.Now;
                entity.UpdateUser = "System";

                if (existing == null)
                {
                    await _db.Insertable(entity).ExecuteCommandAsync();
                    generatedCount++;
                }
                else
                {
                    await _db.Updateable(entity).ExecuteCommandAsync();
                    updatedCount++;
                }
            }

            return new
            {
                code = 200,
                msg = "生成成功",
                data = new
                {
                    generatedCount,
                    updatedCount,
                    totalCount = departments.Count
                }
            };
        }

        /// <summary>
        /// 查询科技部股份统计列表
        /// </summary>
        /// <param name="input">查询参数</param>
        /// <returns>统计列表</returns>
        [HttpGet("list")]
        public async Task<dynamic> GetList([FromQuery] ShareStatisticsTechDeptQueryInput input)
        {
            var query = _db.Queryable<LqShareStatisticsTechDeptEntity>()
                .Where(x => x.IsEffective == 1);

            if (!string.IsNullOrEmpty(input.StatisticsMonth))
            {
                query = query.Where(x => x.StatisticsMonth == input.StatisticsMonth);
            }

            if (!string.IsNullOrEmpty(input.DepartmentName))
            {
                query = query.Where(x => x.DepartmentName == input.DepartmentName);
            }

            var list = await query.OrderBy(x => x.StatisticsMonth, OrderByType.Desc)
                .OrderBy(x => x.DepartmentName)
                .Select(x => new ShareStatisticsTechDeptOutput
                {
                    Id = x.Id,
                    DepartmentName = x.DepartmentName,
                    StatisticsMonth = x.StatisticsMonth,
                    Income = x.Income,
                    CostReimbursement = x.CostReimbursement,
                    CostTeacherBase = x.CostTeacherBase,
                    CostTeacherManual = x.CostTeacherManual,
                    CostTeacherBillingComm = x.CostTeacherBillingComm,
                    CostTeacherConsumeComm = x.CostTeacherConsumeComm,
                    CostTeacherExpertComm = x.CostTeacherExpertComm,
                    CostTeacherOvertime = x.CostTeacherOvertime,
                    CostGMBase = x.CostGMBase,
                    CostGMComm = x.CostGMComm,
                    RewardTechDept = x.RewardTechDept,
                    CostOther1 = x.CostOther1,
                    CostOther2 = x.CostOther2,
                    Profit = x.Profit,
                    CreateTime = x.CreateTime,
                    UpdateTime = x.UpdateTime
                })
                .ToListAsync();

            return new { code = 200, msg = "查询成功", data = list };
        }

        #region 私有计算方法

        /// <summary>
        /// 计算收入部分
        /// </summary>
        private async Task CalculateIncome(LqShareStatisticsTechDeptEntity entity, string deptName, DateTime startDate, DateTime endDate, string statisticsMonth)
        {
            // 1. 通过组织名称找到组织ID
            var organize = await _db.Queryable<OrganizeEntity>()
                .Where(x => x.FullName == deptName && x.DeleteMark == null && x.EnabledMark == 1)
                .FirstAsync();

            if (organize == null)
            {
                entity.Income = 0;
                return;
            }

            // 2. 找到该科技部当月管辖的门店（lq_mdxx.kjb 已废弃，从 lq_md_target 按月份取）
            var stores = await _db.Queryable<LqMdTargetEntity>()
                .Where(x => x.Month == statisticsMonth && x.TechDepartment == organize.Id)
                .Select(x => x.StoreId)
                .Distinct()
                .ToListAsync();

            if (stores.Count == 0)
            {
                entity.Income = 0;
                return;
            }

            // 3. 统计这些门店的科美项目开单实付业绩
            // 需要关联 lq_kd_kdjlb 来获取门店信息
            decimal kemeiIncome = 0;
            if (stores.Count > 0)
            {
                var storeIdsStr = string.Join("','", stores);
                var kemeiIncomeSql = $@"
                    SELECT COALESCE(SUM(px.F_TotalPrice), 0) as Total
                    FROM lq_kd_pxmx px
                    INNER JOIN lq_kd_kdjlb kd ON px.glkdbh = kd.F_Id
                    WHERE kd.djmd IN ('{storeIdsStr}')
                        AND px.yjsj >= '{startDate:yyyy-MM-dd} 00:00:00'
                        AND px.yjsj < '{endDate:yyyy-MM-dd} 00:00:00'
                        AND px.F_IsEffective = 1
                        AND px.F_ItemCategory = '科美'";
                var kemeiIncomeResult = await _db.Ado.SqlQueryAsync<dynamic>(kemeiIncomeSql);
                if (kemeiIncomeResult != null && kemeiIncomeResult.Any())
                {
                    var first = kemeiIncomeResult.FirstOrDefault();
                    if (first != null)
                    {
                        kemeiIncome = Convert.ToDecimal(first.Total ?? 0);
                    }
                }
            }

            // 4. 减去对应的科美项目实退金额
            decimal kemeiRefund = 0;
            if (stores.Count > 0)
            {
                var storeIdsStr = string.Join("','", stores);
                var kemeiRefundSql = $@"
                    SELECT COALESCE(SUM(jksyj), 0) as Total
                    FROM lq_hytk_jksyj
                    WHERE F_StoreId IN ('{storeIdsStr}')
                        AND tksj >= '{startDate:yyyy-MM-dd} 00:00:00'
                        AND tksj < '{endDate:yyyy-MM-dd} 00:00:00'
                        AND F_IsEffective = 1
                        AND F_ItemCategory = '科美'";
                var kemeiRefundResult = await _db.Ado.SqlQueryAsync<dynamic>(kemeiRefundSql);
                if (kemeiRefundResult != null && kemeiRefundResult.Any())
                {
                    var first = kemeiRefundResult.FirstOrDefault();
                    if (first != null)
                    {
                        kemeiRefund = Convert.ToDecimal(first.Total ?? 0);
                    }
                }
            }

            // 5. 结果 * 30%
            entity.Income = (kemeiIncome - kemeiRefund) * 0.3m;
        }

        /// <summary>
        /// 计算成本部分
        /// </summary>
        private async Task CalculateCost(LqShareStatisticsTechDeptEntity entity, string deptName, DateTime startDate, DateTime endDate, string statisticsMonth)
        {
            // 1. 通过组织名称找到组织ID
            var organize = await _db.Queryable<OrganizeEntity>()
                .Where(x => x.FullName == deptName && x.DeleteMark == null && x.EnabledMark == 1)
                .FirstAsync();

            if (organize == null)
            {
                // 如果找不到组织，所有成本为0
                entity.CostReimbursement = 0;
                entity.CostTeacherBase = 0;
                entity.CostTeacherManual = 0;
                entity.CostTeacherBillingComm = 0;
                entity.CostTeacherConsumeComm = 0;
                entity.CostGMBase = 0;
                entity.CostGMComm = 0;
                entity.CostTeacherExpertComm = 0;
                entity.CostTeacherOvertime = 0;
                entity.RewardTechDept = 0;
                entity.CostOther1 = 0;
                entity.CostOther2 = 0;
                return;
            }

            // 2. 找到该科技部当月管辖的门店（lq_mdxx.kjb 已废弃，从 lq_md_target 按月份取）
            var stores = await _db.Queryable<LqMdTargetEntity>()
                .Where(x => x.Month == statisticsMonth && x.TechDepartment == organize.Id)
                .Select(x => x.StoreId)
                .Distinct()
                .ToListAsync();

            // 1. 成本-报销：筛选一级分类为"科技部费用"的申请，根据申请门店的归属区分一部/二部
            var reimbursementAmount = await _db.Queryable<LqReimbursementApplicationEntity, LqPurchaseRecordsEntity, LqReimbursementCategoryEntity>(
                    (app, purchase, category) => app.PurchaseRecordsId == purchase.Id && purchase.ReimbursementCategoryId == category.Id)
                .Where((app, purchase, category) =>
                    category.Level1Name == "科技部费用"
                    && app.ApprovalStatus == "已通过"
                    && stores.Contains(app.ApplicationStoreId)
                    && app.ApplicationTime >= startDate
                    && app.ApplicationTime <= endDate.AddDays(1))
                .SumAsync((app, purchase, category) => purchase.Amount);

            entity.CostReimbursement = reimbursementAmount;

            // 2. 成本-人工-科技部老师底薪：从科技部老师工资统计表统计
            // 需要筛选该科技部管理的门店的老师
            var teacherBaseSalary = await _db.Queryable<LqTechTeacherSalaryStatisticsEntity>()
                .Where(x => x.StatisticsMonth == statisticsMonth
                    && stores.Contains(x.StoreId))
                .SumAsync(x => x.BaseSalary);

            entity.CostTeacherBase = teacherBaseSalary;

            // 3. 成本-人工-科技部手工费：从消耗表中统计科技部老师的手工费
            // 数据来源：lq_xh_kjbsyj（科技部老师业绩表）
            // 条件：ItemCategory = 科美，根据门店归属区分一部/二部
            var teacherManualFee = await _db.Queryable<LqXhKjbsyjEntity>()
                .Where(x => x.ItemCategory == "科美"
                    && stores.Contains(x.StoreId)
                    && x.Yjsj >= startDate
                    && x.Yjsj < endDate
                    && x.IsEffective == 1)
                .SumAsync(x => x.LaborCost ?? 0);

            entity.CostTeacherManual = teacherManualFee;

            // 4. 成本-人工-科技部开单提成：从科技部老师工资统计表统计业绩提成金额
            var teacherBillingComm = await _db.Queryable<LqTechTeacherSalaryStatisticsEntity>()
                .Where(x => x.StatisticsMonth == statisticsMonth
                    && stores.Contains(x.StoreId))
                .SumAsync(x => x.PerformanceCommissionAmount);

            entity.CostTeacherBillingComm = teacherBillingComm;

            // 5. 成本-人工-科技部消耗提成：从科技部老师工资统计表统计消耗提成金额
            var teacherConsumeComm = await _db.Queryable<LqTechTeacherSalaryStatisticsEntity>()
                .Where(x => x.StatisticsMonth == statisticsMonth
                    && stores.Contains(x.StoreId))
                .SumAsync(x => x.ConsumeCommissionAmount);

            entity.CostTeacherConsumeComm = teacherConsumeComm;

            // 6. 成本-人工-科技部总经理底薪和提成
            // 直接根据工资统计表中的 F_Position 字段区分科技一部/二部，不需要通过用户表过滤
            // 科技部总经理底薪
            var gmBaseSalary = await _db.Queryable<LqTechGeneralManagerSalaryStatisticsEntity>()
                .Where(x => x.StatisticsMonth == statisticsMonth
                    && x.Position == deptName)
                .SumAsync(x => x.BaseSalary);

            entity.CostGMBase = gmBaseSalary;

            // 科技部总经理提成
            var gmComm = await _db.Queryable<LqTechGeneralManagerSalaryStatisticsEntity>()
                .Where(x => x.StatisticsMonth == statisticsMonth
                    && x.Position == deptName)
                .SumAsync(x => x.TotalCommission);

            entity.CostGMComm = gmComm;

            // 保留字段
            entity.CostTeacherExpertComm = 0;
            entity.CostTeacherOvertime = 0;
            entity.RewardTechDept = 0;
            entity.CostOther1 = 0;
            entity.CostOther2 = 0;
        }

        /// <summary>
        /// 计算利润
        /// </summary>
        private void CalculateProfit(LqShareStatisticsTechDeptEntity entity)
        {
            // 科技部利润 = 收入 - 成本报销 - 成本人工(底薪 + 手工 + 开单提成 + 消耗提成 + 专家提成 + 加班 + 总经理底薪 + 总经理提成) - 其他
            var totalCost = entity.CostReimbursement              // 成本报销
                + entity.CostTeacherBase                          // 成本人工-底薪
                + entity.CostTeacherManual                        // 成本人工-手工
                + entity.CostTeacherBillingComm                    // 成本人工-开单提成
                + entity.CostTeacherConsumeComm                    // 成本人工-消耗提成
                + entity.CostTeacherExpertComm                     // 成本人工-专家提成
                + entity.CostTeacherOvertime                       // 成本人工-加班
                + entity.CostGMBase                                // 成本人工-总经理底薪
                + entity.CostGMComm                                // 成本人工-总经理提成
                + entity.CostOther1                                // 其他1
                + entity.CostOther2;                               // 其他2
            // 注意：RewardTechDept（奖励-科技部）是保留字段，不计入成本

            entity.Profit = entity.Income - totalCost;
        }

        #endregion
    }
}