antissoft / lvqianmeiye_ERP

using Microsoft.AspNetCore.Authorization;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using NCC.Common.Enum;
using NCC.Common.Filter;
using NCC.Common.Helper;
using NCC.Dependency;
using NCC.DynamicApiController;
using NCC.Extend.Entitys.Dto.LqSalaryExtraCalculation;
using Yitter.IdGenerator;
using NCC.Extend.Entitys.lq_salary_extra_calculation;
using NCC.Extend.Entitys.lq_md_xdbhsj;
using NCC.System.Entitys.Permission;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Threading.Tasks;
using NCC.Common.Core.Manager;
using NCC.FriendlyException;

namespace NCC.Extend
{
    /// <summary>
    /// 健康师工资额外计算服务
    /// </summary>
    [ApiDescriptionSettings(Tag = "健康师工资额外计算", Name = "LqSalaryExtraCalculation", Order = 301)]
    [Route("api/Extend/[controller]")]
    public class LqSalaryExtraCalculationService : IDynamicApiController, ITransient
    {
        private readonly ISqlSugarClient _db;
        private readonly IUserManager _userManager;

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

        /// <summary>
        /// 获取健康师工资额外计算列表
        /// </summary>
        /// <param name="input">查询参数</param>
        /// <returns>健康师工资额外计算分页列表</returns>
        [HttpGet]
        public async Task<PageResult<SalaryExtraCalculationOutput>> GetList([FromQuery] SalaryExtraCalculationInput input)
        {
            var query = _db.Queryable<LqSalaryExtraCalculationEntity>()
                .LeftJoin<UserEntity>((ec, u) => ec.EmployeeId == u.Id);

            // 年份筛选
            if (input.Year.HasValue)
            {
                query = query.Where((ec, u) => ec.Year == input.Year.Value);
            }

            // 月份筛选
            if (input.Month.HasValue)
            {
                query = query.Where((ec, u) => ec.Month == input.Month.Value);
            }

            // 健康师ID筛选
            if (!string.IsNullOrEmpty(input.EmployeeId))
            {
                query = query.Where((ec, u) => ec.EmployeeId == input.EmployeeId);
            }

            // 关键词搜索（姓名或电话）
            if (!string.IsNullOrEmpty(input.Keyword))
            {
                query = query.Where((ec, u) => u.RealName.Contains(input.Keyword) || u.MobilePhone.Contains(input.Keyword));
            }

            var list = await query.Select((ec, u) => new SalaryExtraCalculationOutput
            {
                Id = ec.Id,
                EmployeeId = ec.EmployeeId,
                EmployeeName = u.RealName,
                EmployeePhone = u.MobilePhone,
                Year = ec.Year,
                Month = ec.Month,
                BaseRewardPerformance = ec.BaseRewardPerformance,
                CooperationRewardPerformance = ec.CooperationRewardPerformance,
                NewCustomerPerformance = ec.NewCustomerPerformance,
                NewCustomerConversionRate = ec.NewCustomerConversionRate,
                UpgradePerformance = ec.UpgradePerformance,
                UpgradeConversionRate = ec.UpgradeConversionRate,
                UpgradeCustomerCount = ec.UpgradeCustomerCount,
                OtherPerformanceAdd = ec.OtherPerformanceAdd,
                OtherPerformanceSubtract = ec.OtherPerformanceSubtract
            })
                .ToPagedListAsync(input.currentPage, input.pageSize);

            return PageResult<SalaryExtraCalculationOutput>.SqlSugarPageResult(list);
        }

        /// <summary>
        /// 从Excel导入健康师工资额外计算数据
        /// </summary>
        /// <param name="file">Excel文件</param>
        /// <returns>导入结果</returns>
        [HttpPost("ImportFromExcel")]
        public async Task<dynamic> ImportFromExcel(IFormFile file)
        {
            try
            {
                if (file == null || file.Length == 0)
                {
                    throw NCCException.Oh("请选择要上传的Excel文件");
                }

                // 检查文件格式
                var allowedExtensions = new[] { ".xlsx", ".xls" };
                var fileExtension = Path.GetExtension(file.FileName).ToLowerInvariant();
                if (!allowedExtensions.Contains(fileExtension))
                {
                    throw NCCException.Oh("只支持.xlsx和.xls格式的Excel文件");
                }

                var importData = new List<SalaryExtraCalculationImportInput>();

                // 保存临时文件
                var tempFilePath = Path.Combine(Path.GetTempPath(), Guid.NewGuid().ToString() + Path.GetExtension(file.FileName));
                try
                {
                    using (var stream = new FileStream(tempFilePath, FileMode.Create))
                    {
                        await file.CopyToAsync(stream);
                    }

                    // 使用ExcelImportHelper读取Excel文件
                    // 参数说明：0表示第一个工作表，0表示第一行是标题行
                    var dataTable = ExcelImportHelper.ToDataTable(tempFilePath, 0, 0);

                    if (dataTable.Rows.Count == 0)
                    {
                        throw NCCException.Oh("Excel文件中没有数据行");
                    }

                    // Excel字段顺序：id, 健康师姓名, 健康师电话, 年份, 月份, 基础奖励业绩, 合作奖励业绩, 新客业绩, 新客成交率, 升单业绩, 升单成交率, 升单人头数, 其他业绩加, 其他业绩减
                    // 从第1行开始读取数据（跳过标题行）
                    for (int i = 1; i < dataTable.Rows.Count; i++)
                    {
                        try
                        {
                            var row = dataTable.Rows[i];
                            var id = row[0]?.ToString()?.Trim();
                            var employeeName = row[1]?.ToString()?.Trim();
                            var employeePhone = row[2]?.ToString()?.Trim();
                            var yearText = row[3]?.ToString()?.Trim();
                            var monthText = row[4]?.ToString()?.Trim();
                            var baseRewardPerformanceText = row[5]?.ToString()?.Trim();
                            var cooperationRewardPerformanceText = row[6]?.ToString()?.Trim();
                            var newCustomerPerformanceText = row[7]?.ToString()?.Trim();
                            var newCustomerConversionRateText = row[8]?.ToString()?.Trim();
                            var upgradePerformanceText = row[9]?.ToString()?.Trim();
                            var upgradeConversionRateText = row[10]?.ToString()?.Trim();
                            var upgradeCustomerCountText = row[11]?.ToString()?.Trim();
                            var otherPerformanceAddText = row[12]?.ToString()?.Trim();
                            var otherPerformanceSubtractText = row[13]?.ToString()?.Trim();

                            // 跳过空行
                            if (string.IsNullOrEmpty(employeeName) && string.IsNullOrEmpty(employeePhone))
                            {
                                continue;
                            }

                            // 验证必填字段
                            if (string.IsNullOrEmpty(employeeName))
                            {
                                throw new Exception($"第{i + 1}行：健康师姓名不能为空");
                            }
                            if (string.IsNullOrEmpty(employeePhone))
                            {
                                throw new Exception($"第{i + 1}行：健康师电话不能为空");
                            }

                            // 解析年份
                            if (!int.TryParse(yearText, out int year))
                            {
                                throw new Exception($"第{i + 1}行：年份格式错误");
                            }

                            // 解析月份
                            if (!int.TryParse(monthText, out int month))
                            {
                                throw new Exception($"第{i + 1}行：月份格式错误");
                            }

                            // 验证月份范围
                            if (month < 1 || month > 12)
                            {
                                throw new Exception($"第{i + 1}行：月份必须在1-12之间");
                            }

                            // 解析数值字段（允许为空，默认为0）
                            decimal.TryParse(baseRewardPerformanceText, out decimal baseRewardPerformance);
                            decimal.TryParse(cooperationRewardPerformanceText, out decimal cooperationRewardPerformance);
                            decimal.TryParse(newCustomerPerformanceText, out decimal newCustomerPerformance);
                            decimal.TryParse(newCustomerConversionRateText, out decimal newCustomerConversionRate);
                            decimal.TryParse(upgradePerformanceText, out decimal upgradePerformance);
                            decimal.TryParse(upgradeConversionRateText, out decimal upgradeConversionRate);
                            decimal.TryParse(upgradeCustomerCountText, out decimal upgradeCustomerCount);
                            decimal.TryParse(otherPerformanceAddText, out decimal otherPerformanceAdd);
                            decimal.TryParse(otherPerformanceSubtractText, out decimal otherPerformanceSubtract);

                            var item = new SalaryExtraCalculationImportInput
                            {
                                Id = id,
                                EmployeeName = employeeName,
                                EmployeePhone = employeePhone,
                                Year = year,
                                Month = month,
                                BaseRewardPerformance = baseRewardPerformance,
                                CooperationRewardPerformance = cooperationRewardPerformance,
                                NewCustomerPerformance = newCustomerPerformance,
                                NewCustomerConversionRate = newCustomerConversionRate,
                                UpgradePerformance = upgradePerformance,
                                UpgradeConversionRate = upgradeConversionRate,
                                UpgradeCustomerCount = upgradeCustomerCount,
                                OtherPerformanceAdd = otherPerformanceAdd,
                                OtherPerformanceSubtract = otherPerformanceSubtract
                            };

                            importData.Add(item);
                        }
                        catch (Exception ex)
                        {
                            throw new Exception($"第{i + 1}行数据解析失败: {ex.Message}");
                        }
                    }
                }
                finally
                {
                    // 清理临时文件
                    if (File.Exists(tempFilePath))
                    {
                        File.Delete(tempFilePath);
                    }
                }

                if (!importData.Any())
                {
                    throw NCCException.Oh("Excel文件中没有有效的数据行");
                }

                // 处理导入数据
                return await ProcessImportData(importData);
            }
            catch (Exception ex)
            {
                throw NCCException.Oh($"上传Excel文件导入健康师工资额外计算数据失败: {ex.Message}");
            }
        }

        #region 处理导入数据
        /// <summary>
        /// 处理导入数据
        /// </summary>
        /// <param name="importData">导入数据列表</param>
        /// <returns>导入结果</returns>
        private async Task<dynamic> ProcessImportData(List<SalaryExtraCalculationImportInput> importData)
        {
            var successCount = 0;
            var failCount = 0;
            var errorMessages = new List<string>();
            var entitiesToInsert = new List<LqSalaryExtraCalculationEntity>();
            var entitiesToUpdate = new List<LqSalaryExtraCalculationEntity>();

            foreach (var item in importData)
            {
                try
                {
                    // 1. 根据健康师姓名和电话查找用户ID
                    var user = await _db.Queryable<UserEntity>()
                        .Where(u => u.RealName == item.EmployeeName && u.MobilePhone == item.EmployeePhone)
                        .FirstAsync();

                    if (user == null)
                    {
                        errorMessages.Add($"健康师 {item.EmployeeName}({item.EmployeePhone}) 不存在");
                        failCount++;
                        continue;
                    }

                    // 2. 检查是否已存在相同记录（根据健康师ID、年份、月份）
                    LqSalaryExtraCalculationEntity existingRecord = null;

                    // 如果提供了ID，先尝试根据ID查找
                    if (!string.IsNullOrEmpty(item.Id))
                    {
                        existingRecord = await _db.Queryable<LqSalaryExtraCalculationEntity>()
                            .Where(x => x.Id == item.Id)
                            .FirstAsync();
                    }

                    // 如果没有找到，则根据健康师ID、年份、月份查找
                    if (existingRecord == null)
                    {
                        existingRecord = await _db.Queryable<LqSalaryExtraCalculationEntity>()
                            .Where(x => x.EmployeeId == user.Id && x.Year == item.Year && x.Month == item.Month)
                            .FirstAsync();
                    }

                    if (existingRecord != null)
                    {
                        // 更新现有记录
                        existingRecord.BaseRewardPerformance = item.BaseRewardPerformance;
                        existingRecord.CooperationRewardPerformance = item.CooperationRewardPerformance;
                        existingRecord.NewCustomerPerformance = item.NewCustomerPerformance;
                        existingRecord.NewCustomerConversionRate = item.NewCustomerConversionRate;
                        existingRecord.UpgradePerformance = item.UpgradePerformance;
                        existingRecord.UpgradeConversionRate = item.UpgradeConversionRate;
                        existingRecord.UpgradeCustomerCount = item.UpgradeCustomerCount;
                        existingRecord.OtherPerformanceAdd = item.OtherPerformanceAdd;
                        existingRecord.OtherPerformanceSubtract = item.OtherPerformanceSubtract;
                        entitiesToUpdate.Add(existingRecord);
                    }
                    else
                    {
                        // 创建新记录
                        var entity = new LqSalaryExtraCalculationEntity
                        {
                            Id = YitIdHelper.NextId().ToString(),
                            EmployeeId = user.Id,
                            Year = item.Year,
                            Month = item.Month,
                            BaseRewardPerformance = item.BaseRewardPerformance,
                            CooperationRewardPerformance = item.CooperationRewardPerformance,
                            NewCustomerPerformance = item.NewCustomerPerformance,
                            NewCustomerConversionRate = item.NewCustomerConversionRate,
                            UpgradePerformance = item.UpgradePerformance,
                            UpgradeConversionRate = item.UpgradeConversionRate,
                            UpgradeCustomerCount = item.UpgradeCustomerCount,
                            OtherPerformanceAdd = item.OtherPerformanceAdd,
                            OtherPerformanceSubtract = item.OtherPerformanceSubtract
                        };
                        entitiesToInsert.Add(entity);
                    }

                    successCount++;
                }
                catch (Exception ex)
                {
                    errorMessages.Add($"健康师 {item.EmployeeName}({item.EmployeePhone}) 处理失败: {ex.Message}");
                    failCount++;
                }
            }

            // 批量插入新记录
            if (entitiesToInsert.Any())
            {
                await _db.Insertable(entitiesToInsert).ExecuteCommandAsync();
            }

            // 批量更新现有记录
            if (entitiesToUpdate.Any())
            {
                await _db.Updateable(entitiesToUpdate).ExecuteCommandAsync();
            }

            var result = new
            {
                successCount,
                failCount,
                totalCount = importData.Count,
                errorMessages = errorMessages.Take(50).ToList() // 最多返回50条错误信息
            };

            if (failCount > 0)
            {
                throw NCCException.Oh($"导入完成，成功：{successCount}条，失败：{failCount}条。{string.Join("; ", errorMessages.Take(10))}");
            }

            return result;
        }
        #endregion

        /// <summary>
        /// 为新店健康师生成模拟数据
        /// </summary>
        /// <param name="year">年份</param>
        /// <param name="month">月份</param>
        /// <returns>生成结果</returns>
        [HttpPost("GenerateMockData")]
        public async Task<dynamic> GenerateMockData(int year, int month)
        {
            try
            {
                // 验证月份范围
                if (month < 1 || month > 12)
                {
                    throw NCCException.Oh("月份必须在1-12之间");
                }

                var startDate = new DateTime(year, month, 1);
                var endDate = startDate.AddMonths(1).AddDays(-1);

                // 1. 查询新店保护信息
                var newStoreProtectionList = await _db.Queryable<LqMdXdbhsjEntity>()
                    .Where(x => x.Sfqy == 1)
                    .ToListAsync();

                // 筛选出在统计月份处于保护期内的门店
                var newStoreIds = newStoreProtectionList
                    .Where(x => x.Bhkssj <= endDate && x.Bhjssj >= startDate)
                    .Select(x => x.Mdid)
                    .Distinct()
                    .ToList();

                if (!newStoreIds.Any())
                {
                    throw NCCException.Oh("当前月份没有新店");
                }

                // 2. 查询新店下的健康师
                // 健康师的岗位字段是F_GW，值为"健康师"
                var healthCoaches = await _db.Queryable<UserEntity>()
                    .Where(u => newStoreIds.Contains(u.Mdid) && u.Gw == "健康师" && u.EnabledMark == 1 && (u.DeleteMark == null || u.DeleteMark == 0))
                    .Select(u => new { u.Id, u.RealName, u.Mdid })
                    .ToListAsync();

                if (!healthCoaches.Any())
                {
                    throw NCCException.Oh("新店下没有健康师");
                }

                // 3. 检查是否已存在数据
                var existingEmployeeIds = await _db.Queryable<LqSalaryExtraCalculationEntity>()
                    .Where(x => x.Year == year && x.Month == month)
                    .Select(x => x.EmployeeId)
                    .ToListAsync();

                var employeesToProcess = healthCoaches
                    .Where(hc => !existingEmployeeIds.Contains(hc.Id))
                    .ToList();

                if (!employeesToProcess.Any())
                {
                    throw NCCException.Oh("所有新店健康师已存在该月份的数据");
                }

                // 4. 生成模拟数据
                var random = new Random();
                var entitiesToInsert = new List<LqSalaryExtraCalculationEntity>();
                var successCount = 0;

                foreach (var employee in employeesToProcess)
                {
                    try
                    {
                        // 生成金额
                        // 基础业绩奖励和合作业绩奖励：0-20000之间
                        var baseRewardPerformance = (decimal)(random.NextDouble() * 20000);
                        var cooperationRewardPerformance = (decimal)(random.NextDouble() * 20000);
                        // 其他金额字段：10000-30000之间
                        var newCustomerPerformance = (decimal)(random.NextDouble() * 20000 + 10000);
                        var upgradePerformance = (decimal)(random.NextDouble() * 20000 + 10000);
                        var otherPerformanceAdd = (decimal)(random.NextDouble() * 20000 + 10000);
                        var otherPerformanceSubtract = (decimal)(random.NextDouble() * 20000 + 10000);

                        // 生成转化率（0-60%之间，转换为0-0.6）
                        var newCustomerConversionRate = (decimal)(random.NextDouble() * 0.6);
                        var upgradeConversionRate = (decimal)(random.NextDouble() * 0.6);

                        // 生成升单人头数（0-10之间）
                        var upgradeCustomerCount = (decimal)random.Next(0, 11);

                        var entity = new LqSalaryExtraCalculationEntity
                        {
                            Id = YitIdHelper.NextId().ToString(),
                            EmployeeId = employee.Id,
                            Year = year,
                            Month = month,
                            BaseRewardPerformance = Math.Round(baseRewardPerformance, 2),
                            CooperationRewardPerformance = Math.Round(cooperationRewardPerformance, 2),
                            NewCustomerPerformance = Math.Round(newCustomerPerformance, 2),
                            NewCustomerConversionRate = Math.Round(newCustomerConversionRate, 4),
                            UpgradePerformance = Math.Round(upgradePerformance, 2),
                            UpgradeConversionRate = Math.Round(upgradeConversionRate, 4),
                            UpgradeCustomerCount = upgradeCustomerCount,
                            OtherPerformanceAdd = Math.Round(otherPerformanceAdd, 2),
                            OtherPerformanceSubtract = Math.Round(otherPerformanceSubtract, 2)
                        };

                        entitiesToInsert.Add(entity);
                        successCount++;
                    }
                    catch
                    {
                        // 记录错误但继续处理其他健康师
                        continue;
                    }
                }

                // 5. 批量插入数据
                if (entitiesToInsert.Any())
                {
                    await _db.Insertable(entitiesToInsert).ExecuteCommandAsync();
                }

                return new
                {
                    successCount,
                    totalCount = employeesToProcess.Count,
                    message = $"成功为新店健康师生成 {successCount} 条模拟数据"
                };
            }
            catch (Exception ex)
            {
                throw NCCException.Oh($"生成模拟数据失败: {ex.Message}");
            }
        }
    }
}