antissoft / lvqianmeiye_ERP

using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.Logging;
using SqlSugar;
using LqSalaryCalculationService.Models;
using Yitter.IdGenerator;

namespace LqSalaryCalculationService.Services
{
    /// <summary>
    /// 工资核算服务实现
    /// </summary>
    public class SalaryCalculationService : ISalaryCalculationService
    {
        private readonly SqlSugarClient _db;
        private readonly ILogger<SalaryCalculationService> _logger;
        private readonly IConfiguration _configuration;
        private readonly SalaryCalculationConfig _config;

        public SalaryCalculationService(
            SqlSugarClient db,
            ILogger<SalaryCalculationService> logger,
            IConfiguration configuration)
        {
            _db = db;
            _logger = logger;
            _configuration = configuration;
            _config = _configuration.GetSection("SalaryCalculation").Get<SalaryCalculationConfig>() ?? new SalaryCalculationConfig();
        }

        /// <summary>
        /// 初始化用户信息到工资表
        /// </summary>
        public async Task<UserInfoInitResult> InitializeUserInfoAsync(string calculationMonth)
        {
            try
            {
                _logger.LogInformation($"开始初始化用户信息，计算月份: {calculationMonth}");

                // 1. 从base_user表获取所有用户基础信息（包括没有门店信息的）
                var userInfoSql = @"
                    SELECT 
                        u.F_Id as UserId,
                        u.F_REALNAME as UserName,
                        u.F_GW as Position,
                        u.F_MDID as StoreId,
                        COALESCE(m.dm, '无门店') as StoreName
                    FROM BASE_USER u
                    LEFT JOIN lq_mdxx m ON u.F_MDID = m.F_Id
                    WHERE u.F_ENABLEDMARK = 1 
                        AND u.F_DELETEMARK IS NULL";

                var userInfos = await _db.Ado.SqlQueryAsync<UserInfoDto>(userInfoSql);
                _logger.LogInformation($"获取到 {userInfos.Count()} 名用户基础信息");

                // 2. 清理工资表数据
                _logger.LogInformation("开始清理工资表数据...");
                var clearSql = "DELETE FROM lq_gz";
                await _db.Ado.ExecuteCommandAsync(clearSql);
                _logger.LogInformation("工资表数据清理完成");

                // 3. 先插入所有用户到工资表
                var insertCount = 0;
                foreach (var userInfo in userInfos)
                {
                    try
                    {
                        // 插入到lq_gz工资表 - 先插入基本字段
                        var insertSql = @"
                            INSERT INTO lq_gz (F_Id, userid, xm, hsgw, md, jsjzd)
                            VALUES (@fid, @userid, @xm, @hsgw, @md, @jsjzd)
                            ON DUPLICATE KEY UPDATE
                                xm = VALUES(xm),
                                hsgw = VALUES(hsgw),
                                md = VALUES(md)";

                        await _db.Ado.ExecuteCommandAsync(insertSql, new
                        {
                            fid = Guid.NewGuid().ToString(),
                            userid = userInfo.UserId,
                            xm = userInfo.UserName,
                            hsgw = userInfo.Position,
                            md = userInfo.StoreName,
                            jsjzd = "" // 先插入空值
                        });

                        insertCount++;
                        _logger.LogDebug($"已插入用户: {userInfo.UserName} - {userInfo.StoreName}");
                    }
                    catch (Exception ex)
                    {
                        _logger.LogWarning(ex, $"插入用户 {userInfo.UserName} 时出错: {ex.Message}");
                    }
                }

                _logger.LogInformation($"第一阶段完成：已插入 {insertCount} 名用户到工资表");

                // 4. 更新门店信息（重新查询有门店信息的用户）
                var updateStoreCount = 0;
                var storeUpdateSql = @"
                    SELECT 
                        u.F_Id as UserId,
                        u.F_REALNAME as UserName,
                        m.dm as StoreName
                    FROM BASE_USER u
                    LEFT JOIN lq_mdxx m ON u.F_MDID = m.F_Id
                    WHERE u.F_ENABLEDMARK = 1 
                        AND u.F_DELETEMARK IS NULL
                        AND u.F_MDID IS NOT NULL
                        AND m.dm IS NOT NULL";

                var storeInfos = await _db.Ado.SqlQueryAsync<UserInfoDto>(storeUpdateSql);
                
                foreach (var storeInfo in storeInfos)
                {
                    try
                    {
                        var updateStoreSql = @"
                            UPDATE lq_gz 
                            SET md = @storeName 
                            WHERE userid = @userId";

                        await _db.Ado.ExecuteCommandAsync(updateStoreSql, new
                        {
                            storeName = storeInfo.StoreName,
                            userId = storeInfo.UserId
                        });

                        updateStoreCount++;
                        _logger.LogDebug($"已更新门店信息: {storeInfo.UserName} - {storeInfo.StoreName}");
                    }
                    catch (Exception ex)
                    {
                        _logger.LogWarning(ex, $"更新门店信息 {storeInfo.UserName} 时出错: {ex.Message}");
                    }
                }

                _logger.LogInformation($"第二阶段完成：已更新 {updateStoreCount} 名用户的门店信息");

                // 5. 更新金三角信息
                var updateTeamCount = 0;
                foreach (var userInfo in userInfos)
                {
                    try
                    {
                        // 通过用户ID在lq_jinsanjiao_user中获取对应月份的金三角ID
                        var monthFormat = calculationMonth.Replace("-", "");
                        var teamSql = @"
                            SELECT jsj_user.jsj_id, jsj.jsj as TeamName
                            FROM lq_jinsanjiao_user jsj_user
                            LEFT JOIN lq_ycsd_jsj jsj ON jsj_user.jsj_id = jsj.F_Id
                            WHERE jsj_user.user_id = @userId 
                                AND jsj_user.status = 'ACTIVE'
                                AND jsj_user.F_Month = @monthFormat
                            LIMIT 1";

                        var teamInfo = await _db.Ado.SqlQueryAsync<TeamInfoDto>(teamSql, new { userId = userInfo.UserId, monthFormat = monthFormat });
                        var teamName = teamInfo.FirstOrDefault()?.TeamName ?? "";

                        if (!string.IsNullOrEmpty(teamName))
                        {
                            var updateTeamSql = @"
                                UPDATE lq_gz 
                                SET jsjzd = @teamName 
                                WHERE userid = @userId";

                            await _db.Ado.ExecuteCommandAsync(updateTeamSql, new
                            {
                                teamName = teamName,
                                userId = userInfo.UserId
                            });

                            updateTeamCount++;
                            _logger.LogInformation($"已更新金三角信息: {userInfo.UserName} - {teamName}");
                        }
                        else
                        {
                            _logger.LogDebug($"用户 {userInfo.UserName} 没有找到金三角信息");
                        }
                    }
                    catch (Exception ex)
                    {
                        _logger.LogWarning(ex, $"更新金三角信息 {userInfo.UserName} 时出错: {ex.Message}");
                    }
                }

                _logger.LogInformation($"第三阶段完成：已更新 {updateTeamCount} 名用户的金三角信息");
                _logger.LogInformation($"用户信息初始化完成，共处理 {insertCount} 名用户");

                return new UserInfoInitResult
                {
                    Success = true,
                    Message = "用户信息初始化完成",
                    UserCount = insertCount,
                    CalculationMonth = calculationMonth
                };
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, $"初始化用户信息失败: {ex.Message}");
                return new UserInfoInitResult
                {
                    Success = false,
                    Message = $"初始化用户信息失败: {ex.Message}",
                    CalculationMonth = calculationMonth
                };
            }
        }

        /// <summary>
        /// 执行工资核算
        /// </summary>
        public async Task<SalaryCalculationResult> CalculateSalaryAsync(string calculationMonth)
        {
            try
            {
                _logger.LogInformation($"开始执行工资核算，计算月份: {calculationMonth}");

                // 1. 获取员工业绩数据
                var performances = await GetEmployeePerformanceAsync(calculationMonth);
                _logger.LogInformation($"获取到 {performances.Count} 条员工业绩数据");

                // 2. 计算每个员工的工资
                var salaries = new List<EmployeeSalary>();
                foreach (var performance in performances)
                {
                    EmployeeSalary salary;
                    switch (performance.PositionCategory)
                    {
                        case "健康师":
                            salary = await CalculateHealthWorkerSalaryAsync(performance);
                            break;
                        case "店长":
                        case "主任":
                        case "店助":
                            salary = await CalculateManagerSalaryAsync(performance);
                            break;
                        default:
                            salary = await CalculateDefaultSalaryAsync(performance);
                            break;
                    }
                    salaries.Add(salary);
                }

                // 3. 导出工资报表
                var outputPath = Path.Combine(_config.OutputPath, $"工资核算_{calculationMonth}.xlsx");
                var exportPath = await ExportSalaryReportAsync(salaries, outputPath);

                // 4. 发送邮件通知
                if (_config.EnableEmailNotification)
                {
                    await SendEmailNotificationAsync(new SalaryCalculationResult
                    {
                        Success = true,
                        Message = "工资核算完成",
                        Salaries = salaries,
                        OutputFilePath = exportPath,
                        CalculationMonth = calculationMonth
                    });
                }

                _logger.LogInformation($"工资核算完成，共计算 {salaries.Count} 名员工工资");

                return new SalaryCalculationResult
                {
                    Success = true,
                    Message = "工资核算完成",
                    Salaries = salaries,
                    OutputFilePath = exportPath,
                    CalculationMonth = calculationMonth
                };
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, $"工资核算失败: {ex.Message}");
                return new SalaryCalculationResult
                {
                    Success = false,
                    Message = $"工资核算失败: {ex.Message}",
                    CalculationMonth = calculationMonth
                };
            }
        }

        /// <summary>
        /// 获取员工业绩数据
        /// </summary>
        public async Task<List<EmployeePerformance>> GetEmployeePerformanceAsync(string calculationMonth)
        {
            var startDate = DateTime.Parse($"{calculationMonth}-01");
            var endDate = startDate.AddMonths(1).AddDays(-1);

            var sql = @"
                SELECT 
                    u.F_Id as EmployeeId,
                    u.F_REALNAME as EmployeeName,
                    u.F_MDID as StoreId,
                    m.dm as StoreName,
                    u.F_GW as Position,
                    u.F_GWFL as PositionCategory,
                    COALESCE(SUM(CAST(yj.ssyj as DECIMAL(18,2))), 0) as TotalPerformance,
                    COALESCE(SUM(CAST(xh.ssyj as DECIMAL(18,2))), 0) as ConsumptionPerformance,
                    COUNT(DISTINCT yj.xmbh) as ProjectCount,
                    COUNT(DISTINCT yj.khbh) as CustomerCount,
                    COALESCE(SUM(CAST(jsj.team_performance as DECIMAL(18,2))), 0) as TeamPerformance,
                    CASE WHEN m.kysj >= DATE_SUB(NOW(), INTERVAL 6 MONTH) THEN 1 ELSE 0 END as IsNewStore,
                    COALESCE(m.xsyj, 0) as StoreLifeLine,
                    COALESCE(jsj.team_id, '') as TeamId,
                    COALESCE(jsj.team_member_count, 1) as TeamMemberCount
                FROM BASE_USER u
                LEFT JOIN lq_mdxx m ON u.F_MDID = m.F_Id
                LEFT JOIN lq_yjmxb yj ON u.F_REALNAME = yj.jks 
                    AND DATE_FORMAT(yj.fssj, '%Y-%m') = @month
                LEFT JOIN lq_xhmxb xh ON u.F_REALNAME = xh.jks 
                    AND DATE_FORMAT(xh.fssj, '%Y-%m') = @month
                LEFT JOIN (
                    SELECT 
                        jsj.F_Id as team_id,
                        jsj.jsj,
                        COUNT(jsj_user.user_id) as team_member_count,
                        SUM(CAST(yj2.ssyj as DECIMAL(18,2))) as team_performance
                    FROM lq_ycsd_jsj jsj
                    LEFT JOIN lq_jinsanjiao_user jsj_user ON jsj.F_Id = jsj_user.jsj_id
                    LEFT JOIN lq_yjmxb yj2 ON jsj_user.user_name = yj2.jks 
                        AND DATE_FORMAT(yj2.fssj, '%Y-%m') = @month
                    WHERE jsj.yf = @month
                    GROUP BY jsj.F_Id, jsj.jsj
                ) jsj ON u.F_REALNAME = jsj.jsj
                WHERE u.F_ENABLEDMARK = 1
                    AND u.F_DELETEMARK IS NULL
                GROUP BY u.F_Id, u.F_REALNAME, u.F_MDID, m.dm, u.F_GW, u.F_GWFL, 
                         m.kysj, m.xsyj, jsj.team_id, jsj.team_member_count";

            var performances = await _db.Ado.SqlQueryAsync<EmployeePerformance>(sql, new { month = calculationMonth });
            return performances.ToList();
        }

        /// <summary>
        /// 计算健康师工资
        /// </summary>
        public Task<EmployeeSalary> CalculateHealthWorkerSalaryAsync(EmployeePerformance performance)
        {
            var salary = new EmployeeSalary
            {
                Id = Guid.NewGuid().ToString(),
                EmployeeId = performance.EmployeeId,
                EmployeeName = performance.EmployeeName,
                StoreId = performance.StoreId,
                StoreName = performance.StoreName,
                Position = performance.Position,
                PositionCategory = performance.PositionCategory,
                CalculationMonth = performance.StoreId, // 这里应该传计算月份
                TotalPerformance = performance.TotalPerformance,
                ConsumptionPerformance = performance.ConsumptionPerformance,
                ProjectCount = performance.ProjectCount,
                CustomerCount = performance.CustomerCount,
                TeamPerformance = performance.TeamPerformance,
                IsNewStore = performance.IsNewStore,
                StoreLifeLine = performance.StoreLifeLine,
                CreatedTime = DateTime.Now
            };

            // 根据健康师薪酬规则计算底薪
            if (performance.ConsumptionPerformance >= 40000 && performance.ProjectCount >= 156)
            {
                salary.BaseSalary = 2400; // 三星
            }
            else if (performance.ConsumptionPerformance >= 20000 && performance.ProjectCount >= 126)
            {
                salary.BaseSalary = 2200; // 二星
            }
            else if (performance.ConsumptionPerformance >= 10000 && performance.ProjectCount >= 96)
            {
                salary.BaseSalary = 2000; // 一星
            }
            else
            {
                salary.BaseSalary = 1800; // 0星
            }

            // 计算金三角提成
            if (performance.TeamMemberCount >= 1)
            {
                var commissionRate = CalculateTeamCommissionRate(performance.TeamPerformance, performance.TeamMemberCount);
                salary.TeamCommission = performance.TeamPerformance * commissionRate / 100;
            }

            // 计算个人提成（基础业绩提成）
            if (performance.TotalPerformance > 6000)
            {
                salary.CommissionAmount = performance.TotalPerformance * 0.95m * 0.03m; // 3%提成点
            }

            salary.GrossSalary = salary.BaseSalary + salary.CommissionAmount + salary.TeamCommission + salary.BonusAmount - salary.DeductionAmount;
            salary.NetSalary = salary.GrossSalary;

            return Task.FromResult(salary);
        }

        /// <summary>
        /// 计算金三角提成比例
        /// </summary>
        public decimal CalculateTeamCommissionRate(decimal teamPerformance, int memberCount)
        {
            switch (memberCount)
            {
                case 3: // 3人战队
                    if (teamPerformance >= 150000) return 7m;
                    if (teamPerformance >= 120000) return 6m;
                    if (teamPerformance >= 90000) return 5m;
                    if (teamPerformance >= 60000) return 4m;
                    if (teamPerformance >= 30000) return 3m;
                    break;
                case 2: // 2人战队
                    if (teamPerformance >= 80000) return 6m;
                    if (teamPerformance >= 60000) return 5m;
                    if (teamPerformance >= 40000) return 4m;
                    if (teamPerformance >= 20000) return 3m;
                    break;
                case 1: // 1人战队
                    if (teamPerformance >= 60000) return 6m;
                    if (teamPerformance >= 40000) return 5m;
                    if (teamPerformance >= 20000) return 4m;
                    if (teamPerformance >= 10000) return 3m;
                    break;
            }
            return 0m;
        }

        /// <summary>
        /// 计算门店管理员工资
        /// </summary>
        public Task<EmployeeSalary> CalculateManagerSalaryAsync(EmployeePerformance performance)
        {
            var salary = new EmployeeSalary
            {
                Id = Guid.NewGuid().ToString(),
                EmployeeId = performance.EmployeeId,
                EmployeeName = performance.EmployeeName,
                StoreId = performance.StoreId,
                StoreName = performance.StoreName,
                Position = performance.Position,
                PositionCategory = performance.PositionCategory,
                CalculationMonth = performance.StoreId, // 这里应该传计算月份
                TotalPerformance = performance.TotalPerformance,
                ConsumptionPerformance = performance.ConsumptionPerformance,
                ProjectCount = performance.ProjectCount,
                CustomerCount = performance.CustomerCount,
                TeamPerformance = performance.TeamPerformance,
                IsNewStore = performance.IsNewStore,
                StoreLifeLine = performance.StoreLifeLine,
                CreatedTime = DateTime.Now
            };

            // 根据职位设置底薪
            switch (performance.Position)
            {
                case "店长":
                    salary.BaseSalary = 4000;
                    break;
                case "主任":
                    salary.BaseSalary = 3500;
                    break;
                case "店助":
                    salary.BaseSalary = 3000;
                    break;
                default:
                    salary.BaseSalary = 3000;
                    break;
            }

            // 计算提成（基于毛利）
            var grossProfit = performance.TotalPerformance * 0.6m; // 假设毛利率60%
            var commissionRate = 0.03m; // 3%提成率
            salary.CommissionAmount = grossProfit * commissionRate;

            // 检查是否达标
            salary.IsTargetAchieved = performance.TotalPerformance >= performance.StoreLifeLine;

            salary.GrossSalary = salary.BaseSalary + salary.CommissionAmount + salary.BonusAmount - salary.DeductionAmount;
            salary.NetSalary = salary.GrossSalary;

            return Task.FromResult(salary);
        }

        /// <summary>
        /// 计算默认工资
        /// </summary>
        private Task<EmployeeSalary> CalculateDefaultSalaryAsync(EmployeePerformance performance)
        {
            var salary = new EmployeeSalary
            {
                Id = Guid.NewGuid().ToString(),
                EmployeeId = performance.EmployeeId,
                EmployeeName = performance.EmployeeName,
                StoreId = performance.StoreId,
                StoreName = performance.StoreName,
                Position = performance.Position,
                PositionCategory = performance.PositionCategory,
                CalculationMonth = performance.StoreId,
                TotalPerformance = performance.TotalPerformance,
                ConsumptionPerformance = performance.ConsumptionPerformance,
                ProjectCount = performance.ProjectCount,
                CustomerCount = performance.CustomerCount,
                TeamPerformance = performance.TeamPerformance,
                IsNewStore = performance.IsNewStore,
                StoreLifeLine = performance.StoreLifeLine,
                CreatedTime = DateTime.Now,
                BaseSalary = 2000, // 默认底薪
                GrossSalary = 2000,
                NetSalary = 2000
            };

            return Task.FromResult(salary);
        }

        /// <summary>
        /// 导出工资报表
        /// </summary>
        public async Task<string> ExportSalaryReportAsync(List<EmployeeSalary> salaries, string outputPath)
        {
            try
            {
                // 确保输出目录存在
                var directory = Path.GetDirectoryName(outputPath);
                if (!Directory.Exists(directory))
                {
                    Directory.CreateDirectory(directory!);
                }

                // 这里可以使用EPPlus或其他Excel库来生成Excel文件
                // 为了简化，这里生成CSV文件
                var csvPath = outputPath.Replace(".xlsx", ".csv");
                
                using var writer = new StreamWriter(csvPath);
                await writer.WriteLineAsync("员工姓名,门店名称,职位,底薪,总业绩,消耗业绩,提成金额,应发工资,实发工资,计算月份");
                
                foreach (var salary in salaries)
                {
                    await writer.WriteLineAsync($"{salary.EmployeeName},{salary.StoreName},{salary.Position}," +
                        $"{salary.BaseSalary},{salary.TotalPerformance},{salary.ConsumptionPerformance}," +
                        $"{salary.CommissionAmount},{salary.GrossSalary},{salary.NetSalary},{salary.CalculationMonth}");
                }

                _logger.LogInformation($"工资报表已导出到: {csvPath}");
                return csvPath;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, $"导出工资报表失败: {ex.Message}");
                throw;
            }
        }

        /// <summary>
        /// 发送邮件通知
        /// </summary>
        public Task<bool> SendEmailNotificationAsync(SalaryCalculationResult result)
        {
            try
            {
                // 这里可以实现邮件发送逻辑
                // 可以使用MailKit或其他邮件库
                _logger.LogInformation($"邮件通知功能待实现，核算结果: {result.Message}");
                return Task.FromResult(true);
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, $"发送邮件通知失败: {ex.Message}");
                return Task.FromResult(false);
            }
        }
    }

    /// <summary>
    /// 用户信息DTO
    /// </summary>
    public class UserInfoDto
    {
        public string UserId { get; set; } = string.Empty;
        public string UserName { get; set; } = string.Empty;
        public string Position { get; set; } = string.Empty;
        public string StoreId { get; set; } = string.Empty;
        public string StoreName { get; set; } = string.Empty;
    }

    /// <summary>
    /// 团队信息DTO
    /// </summary>
    public class TeamInfoDto
    {
        public string JsjId { get; set; } = string.Empty;
        public string TeamName { get; set; } = string.Empty;
    }
}