OA2023/OASystem/OASystem.Api/Controllers/PersonnelModuleController.cs

using Aspose.Cells;
using OASystem.API.OAMethodLib;
using OASystem.API.OAMethodLib.QiYeWeChatAPI;
using OASystem.Domain.Dtos.PersonnelModule;
using OASystem.Domain.Entities.PersonnelModule;
using OASystem.Domain.ViewModels.PersonnelModule;
using OASystem.Domain.ViewModels.QiYeWeChat;
using OASystem.Infrastructure.Repositories.PersonnelModule;
using System.Data;
using System.Diagnostics;
using System.Globalization;
using System.IO;

namespace OASystem.API.Controllers
{
    /// <summary>
    /// 人事模块
    /// </summary>
    [Route("api/[controller]/[action]")]
    public class PersonnelModuleController : ControllerBase
    {
        private Result _result;
        private readonly IMapper _mapper;
        private readonly decimal _chengDuMinimumWage = 2100.00M;
        private readonly IQiYeWeChatApiService _qiYeWeChatApiService;
        private readonly WageSheetRepository _wageSheetRep;
        private readonly UsersRepository _usersRep;


        /// <summary>
        /// 初始化
        /// </summary>
        /// <param name="qiYeWeChatApiService"></param>
        /// <param name="wageSheetRep"></param>
        /// <param name="usersRep"></param>
        /// <param name="mapper"></param>
        public PersonnelModuleController(IQiYeWeChatApiService qiYeWeChatApiService,WageSheetRepository wageSheetRep, UsersRepository usersRep, IMapper mapper)
        {
            _mapper = mapper;
            _usersRep = usersRep;
            _qiYeWeChatApiService = qiYeWeChatApiService;
            _wageSheetRep = wageSheetRep;
            _result = new Result();
        }

        #region 工资表单


        /// <summary>
        /// 工资 月列表
        /// </summary>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> GetWageSheetMonth()
        {
            string sql = string.Format("Select * From  Pm_WageIssueWorkingDay Where IsDel = 0 Order By YearMonth Desc");
            var data = await _wageSheetRep._sqlSugar.SqlQueryable<WageSheetMonthView>(sql).ToListAsync();

            return Ok(JsonView(true, "查询成功!", data));
        }

        /// <summary>
        /// 工资 工作日信息
        /// </summary>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> GetWageSheetMonthWorkdays(string startDt, string endDt )
        {

            //参数处理
            string dtFormat = "yyyy-MM-dd";
            DateTime startDt1, endDt1;
            bool startDtIsValid = DateTime.TryParseExact(startDt, dtFormat, CultureInfo.InvariantCulture, DateTimeStyles.None, out startDt1);
            bool endDtIsValid = DateTime.TryParseExact(endDt, dtFormat, CultureInfo.InvariantCulture, DateTimeStyles.None, out endDt1);

            if (!startDtIsValid) return Ok(JsonView(false, "开始日期格式错误!正确时间格式：yyyy-MM-dd  "));
            if (!endDtIsValid) return Ok(JsonView(false, "结束格式错误!正确时间格式：yyyy-MM-dd  "));



            string sql = string.Format(@"Select  * From Sys_Calendar
										 Where Isdel = 0 And Dt between  '{0}' And  '{1}'", startDt, endDt);
            var data = await _wageSheetRep._sqlSugar.SqlQueryable<CalendarInfoView>(sql).ToListAsync();

            return Ok(JsonView(true, "查询成功!", data));

        }

        /// <summary>
        /// 工资 工作日信息
        /// </summary>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> GetWageSheetMonthWorkdaysAddOrEdit(WageSheetMonthWorkdaysAddOrEditDto dto)
        {

            //参数处理
            string yearFormat = "yyyy-MM";
            string dtFormat = "yyyy-MM-dd";
            DateTime yearDt, startDt1, endDt1;
            bool yearDtIsValid = DateTime.TryParseExact(dto.YearMonth, yearFormat, CultureInfo.InvariantCulture, DateTimeStyles.None, out yearDt);
            bool startDtIsValid = DateTime.TryParseExact(dto.StartDate, dtFormat, CultureInfo.InvariantCulture, DateTimeStyles.None, out startDt1);
            bool endDtIsValid = DateTime.TryParseExact(dto.EndDate, dtFormat, CultureInfo.InvariantCulture, DateTimeStyles.None, out endDt1);


            if (!yearDtIsValid) return Ok(JsonView(false, "年月日期格式错误!正确时间格式：yyyy-MM  "));
            if (!startDtIsValid) return Ok(JsonView(false, "开始日期格式错误!正确时间格式：yyyy-MM-dd  "));
            if (!endDtIsValid) return Ok(JsonView(false, "结束格式错误!正确时间格式：yyyy-MM-dd  "));

            

            int res = -1;

            #region 处理数据
            Pm_WageIssueWorkingDay pm_WageIssueWorkingDay = new Pm_WageIssueWorkingDay();
            pm_WageIssueWorkingDay = _mapper.Map<Pm_WageIssueWorkingDay>(dto);
            List<Sys_Calendar> sys_Calendars = new List<Sys_Calendar>();
            sys_Calendars = _mapper.Map<List<Sys_Calendar>>(dto.CalendarInfos);
            pm_WageIssueWorkingDay.Workdays = sys_Calendars.Where(it => it.IsWorkDays == true).ToList().Count();
            foreach (var item in sys_Calendars)
            {
                item.Remark = pm_WageIssueWorkingDay.Remark;
                item.CreateUserId = pm_WageIssueWorkingDay.CreateUserId;
                item.CreateTime = pm_WageIssueWorkingDay.CreateTime;
            }

            #endregion

            var _sqlSugar = _wageSheetRep._sqlSugar;
            if (dto.Status == 1)
            {
                string sql = string.Format("Select * From  Pm_WageIssueWorkingDay Where IsDel = 0 and YearMonth='{0}'",dto.YearMonth);

                var data = await _sqlSugar.SqlQueryable<WageSheetMonthView>(sql).FirstAsync();
                if (data != null)
                {
                    return Ok(JsonView(false, dto.YearMonth + "日期数据已存在,请前往修改!"));
                }

                _sqlSugar.BeginTran();
                int add1 = await _sqlSugar.Insertable< Pm_WageIssueWorkingDay>(pm_WageIssueWorkingDay).ExecuteCommandAsync();
                if (add1 > 0) res = 0;
                int add2 = await _sqlSugar.Insertable<Sys_Calendar>(sys_Calendars).ExecuteCommandAsync();
                if (add2 > 0) res = 0;
                _sqlSugar.CommitTran();
            }
            else if (dto.Status == 1)
            {
                _sqlSugar.BeginTran();


                int upd1 = await _sqlSugar.Updateable< Pm_WageIssueWorkingDay >(pm_WageIssueWorkingDay)
                    .UpdateColumns(it => new { it.YearMonth,it.StartDate,it.EndDate,it.Workdays,it.DeleteTime })
                    .ExecuteCommandAsync();
                if (upd1 > 0) res = 0;
                int upd2 = await _sqlSugar.Updateable<Sys_Calendar>(sys_Calendars)
                    .UpdateColumns(it => new { it.Dt, it.IsWorkDays, it.IsHoliDay, it.HoliDayName })
                    .ExecuteCommandAsync();
                if (upd2 > 0) res = 0;

                _sqlSugar.CommitTran();
            }
            else return Ok(JsonView(false, "Status 请传入正确的状态码 1 添加 2 修改"));

            if (res == 0) return Ok(JsonView(true, "操作成功!"));


            return Ok(JsonView(false, "操作失败!"));
        }


        /// <summary>
        /// 工资表单 基础数据源
        /// </summary>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> GetWageSheetBasicsDataSource()
        {
            string companySql = string.Format("Select * From Sys_Company Where IsDel = 0");
            var compnayData = await _wageSheetRep._sqlSugar.SqlQueryable<CompanyNameView>(companySql).ToListAsync();

            string depSql = string.Format("Select * From Sys_Department Where IsDel = 0");
            var depData = await _wageSheetRep._sqlSugar.SqlQueryable<Domain.ViewModels.System.DepartmentView>(depSql).ToListAsync();

            //获取OA系统内所有用户
            var nameData = await _usersRep.GetUserNameList(1);

            if (nameData.Code != 0)
            {
                return Ok(JsonView(false, nameData.Msg));
            }

            var data = new {
                compnayData = compnayData,
                depData = depData,
                userNames = nameData.Data
        };


            return Ok(JsonView(true, "查询成功!", data));
        }

        /// <summary>
        /// 获取工资发放月份
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> GetWageYaerMonths(WageYearDto dto)
        {
            string sql = string.Format(@"Select * From  Pm_WageIssueWorkingDay 
                                         Where Isdel = 0 And YearMonth Like '%{0}%'
                                         Order By YearMonth Asc", dto.Year);

            var data  = await _wageSheetRep._sqlSugar.SqlQueryable<WageYearMonthView>(sql).ToListAsync();
            
           
            return Ok(JsonView(true,"操作成功!", data));
        }

        /// <summary>
        /// 获取工资表单
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> GetWageSheetList(WageSheetListDto dto)
        {
            //参数处理
            string ymFormat = "yyyy-MM";
            DateTime yearMonthDt;
            bool yearMonthDttIsValid = DateTime.TryParseExact(dto.YearMonth, ymFormat, CultureInfo.InvariantCulture, DateTimeStyles.None, out yearMonthDt);
            if (!yearMonthDttIsValid)
            {
                _result.Msg = "年月格式错误!正确时间格式：yyyy-MM  ";
                return Ok(JsonView(false, _result.Msg));
            }

            //获取月工资数据
            string yearMonth = yearMonthDt.ToString("yyyy-MM");

            if (dto.PortType == 1)
            {
                _result = await _wageSheetRep.Get_WageSheet_ListByYearMonthAsync(yearMonth);
                if (_result.Code != 0)
                {
                    return Ok(JsonView(false, _result.Msg));
                }
            }
            else if (dto.PortType == 2)
            { }
            else if (dto.PortType == 3)
            { }
            else
            {
                return Ok(JsonView(false, "请选择正确的端口参数"));
            }

            return Ok(JsonView(true, _result.Msg, _result.Data));
        }

        /// <summary>
        /// 获取工资 详情
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> GetWageSheetById(WageSheetInfoDto dto)
        {
            if (dto.PortType == 1)
            {
                _result = await _wageSheetRep.Get_WageSheet_InfoByIdAsync(dto.Id);
                if (_result.Code != 0)
                {
                    return Ok(JsonView(false, _result.Msg));
                }
            }
            else if (dto.PortType == 2)
            { }
            else if (dto.PortType == 3)
            { }
            else
            {
                return Ok(JsonView(false, "请选择正确的端口参数"));
            }

            return Ok(JsonView(true, _result.Msg, _result.Data));
        }

        /// <summary>
        /// 人事模块 工资表单 删除
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> PostWageSheetDel(WageDelDto dto)
        {
            try
            {
                _result = await _wageSheetRep.Post_WageSheet_DelAsync(dto);
                if (_result.Code != 0)
                {
                    return Ok(JsonView(false, _result.Msg));

                }
            }
            catch (Exception ex)
            {

                return Ok(JsonView(false, ex.Message));
            }

            return Ok(JsonView(true, _result.Msg, _result.Data));
        }

        /// <summary>
        /// 人事模块 工资表单 添加 Or 修改
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> PostWageSheetAddOrEdit(WageAddOrEditDto dto)
        {
            try
            {
                Pm_WageSheet pm_WageSheet = new Pm_WageSheet();
                pm_WageSheet = _mapper.Map<Pm_WageSheet>(dto);
                pm_WageSheet.LastUpdateUserId = dto.CreateUserId;

                #region 计算工资

                //月工资
                decimal salary = pm_WageSheet.Basic + pm_WageSheet.Floats + pm_WageSheet.PostAllowance + pm_WageSheet.InformationSecurityFee + pm_WageSheet.OtherSubsidies;
                //扣款合计
                decimal totalDeduction = pm_WageSheet.SickLeave + pm_WageSheet.SomethingFalse + pm_WageSheet.LateTo + pm_WageSheet.LeaveEarly + pm_WageSheet.Absenteeism + pm_WageSheet.NotPunch +
                                         pm_WageSheet.ReservedFunds + pm_WageSheet.WithholdingInsurance + pm_WageSheet.OtherDeductions + pm_WageSheet.OtherDeductions;

                //实发合计 不含个税
                if (pm_WageSheet.RegularDays >= pm_WageSheet.WorkDays )
                {
                    pm_WageSheet.RegularDays = pm_WageSheet.WorkDays;
                    salary = salary + pm_WageSheet.Mealsupplement;
                }
                else
                {
                    if (dto.UserId == 21) //21==张海麟
                    {
                        salary = salary + pm_WageSheet.Mealsupplement;
                    }
                    else
                    {
                        salary = salary = (PayrollComputation.ConvertToDecimal(salary / pm_WageSheet.WorkDays) * pm_WageSheet.RegularDays) + pm_WageSheet.Mealsupplement;
                    }    
                }

                
                decimal actualTotal = salary - totalDeduction;
                pm_WageSheet.Should = salary;
                pm_WageSheet.TotalDeductions = totalDeduction;
                pm_WageSheet.TotalRealHair = actualTotal - pm_WageSheet.WithholdingTax;
                pm_WageSheet.AfterTax = actualTotal - pm_WageSheet.WithholdingTax;

                #endregion


                _result = await _wageSheetRep.Post_WageSheet_AddOrEditAsync(dto, pm_WageSheet);
                if (_result.Code != 0)
                {
                    return Ok(JsonView(false, _result.Msg));

                }
            }
            catch (Exception ex)
            {

                return Ok(JsonView(false, ex.Message));
            }
                
            return Ok(JsonView(true, _result.Msg, _result.Data));
        }

        /// <summary>
        /// 计算工资
        /// </summary>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> SalaryCalculatorAsync(SalaryCalculatorDto dto)
        {

            Result result = new Result();
            Stopwatch sw = new Stopwatch();
            sw.Start();
            //参数处理
            string ymFormat = "yyyy-MM";
            string dtFormat = "yyyy-MM-dd";
            DateTime yearMonthDt,startDt,endDt;
            bool yearMonthDtIsValid = DateTime.TryParseExact(dto.yearMonth, ymFormat, CultureInfo.InvariantCulture, DateTimeStyles.None, out yearMonthDt);
            bool startDtIsValid = DateTime.TryParseExact(dto.startDt, dtFormat, CultureInfo.InvariantCulture, DateTimeStyles.None, out startDt);
            bool endDtIsValid = DateTime.TryParseExact(dto.endDt, dtFormat, CultureInfo.InvariantCulture, DateTimeStyles.None, out endDt);

            if (!yearMonthDtIsValid) return Ok(JsonView(false, "年月格式错误!正确时间格式：yyyy-MM  "));
            if (!startDtIsValid) return Ok(JsonView(false, "开始日期格式错误!正确时间格式：yyyy-MM-dd  "));
            if (!endDtIsValid) return Ok(JsonView(false, "结束格式错误!正确时间格式：yyyy-MM-dd  "));

            string thisYearMonth = dto.yearMonth;
            string preYearMonth = yearMonthDt.AddMonths(-1).ToString("yyyy-MM");



            //计算本月工资起止时间 比如是2月的1号-28号，那就是2月1号的零点到3月1号的零点 
            DateTime thisStartDt = startDt;
            DateTime thisEndDt = endDt; //

            //本月工资是否有数据 有数据则不计算
            result = await _wageSheetRep.Get_WageSheet_ListByYearMonthAsync(thisYearMonth);
            if (result.Code == 0 )
            {
                return Ok(JsonView(false, thisYearMonth + " 工资数据已存在,若无人员工资请手动添加!"));
            }

            //获取上个月工资信息
            List<Pm_WageSheet> preWageSheetItems = await _wageSheetRep._sqlSugar.Queryable<Pm_WageSheet>().Where(it => it.IsDel == 0 && it.YearMonth == preYearMonth).ToListAsync();
            preWageSheetItems = preWageSheetItems.OrderBy(it => it.UserId).ToList();
            if (preWageSheetItems.Count <= 0) 
            {
                return Ok(JsonView(false, thisYearMonth + " 上月工资数据不存在,请手动添加!"));
            }
            //处理上个月同月同人 多条数据
            List<Pm_WageSheet> preWageSheetItems1 = new List<Pm_WageSheet>();
            preWageSheetItems1 = preWageSheetItems.GroupBy(it => new { it.YearMonth,it.UserId })
                                                  .Select(it => it.FirstOrDefault(item => item.Basic !=0))
                                                  .ToList();

             //获取OA系统内所有用户
             var nameData = await _usersRep.GetUserNameList(1);
            List<UserNameView>? userNames = nameData.Data;

            List<Pm_WageSheet> wageSheets = new List<Pm_WageSheet>();

            _result = await PayrollComputation.SalaryCalculatorAsync(preWageSheetItems1, userNames,dto.UserId, thisYearMonth, thisStartDt,thisEndDt);

            #region 批量添加

            if (_result.Code != 0)
            {
                return Ok(JsonView(false, _result.Msg));
            }



            wageSheets = _result.Data;

            var add = await _wageSheetRep._sqlSugar.Insertable(wageSheets).ExecuteCommandAsync();
            if (add <= 0)
            {
                return Ok(JsonView(false, "操作失败!"));
            }

            #endregion

            #region 处理返回数据
            //List <WageSheetItemInfoView> wageSheetItems = new List<WageSheetItemInfoView>();
            //wageSheetItems = _mapper.Map<List<WageSheetItemInfoView>>(wageSheets);
            //wageSheetItems = wageSheetItems.Select(it => 
            //        {
            //            UserNameView? uName1 = new UserNameView();
            //            UserNameView? uName2 = new UserNameView();
            //            uName1 = userNames.Where(it1 => it.UserId == it1.Id).FirstOrDefault();
            //            if (uName1 != null)  it.Name = uName1.CnName;

            //            uName2 = userNames.Where(it1 => it.LastUpdateUserId == it1.Id).FirstOrDefault();
            //            if (uName2 != null) it.LastUpdateUserName = uName2.CnName;

            //            return it; }
            //        ).ToList();
            #endregion

            sw.Stop();
            return Ok(JsonView(true, "操作成功! 耗时：" + (sw.ElapsedMilliseconds/1000) + "s"));
        }


        /// <summary>
        /// 计算工资 By YearMonth And UserId
        /// </summary>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> SalaryCalculatorSingleAsync(SalaryCalculatorSingleDto dto)
        {
            Result result = new Result();
            Stopwatch sw = new Stopwatch();
            sw.Start();

            //参数处理
            string ymFormat = "yyyy-MM";
            string ymdFormat = "yyyy-MM-dd";
            DateTime yearMonthDt,startDt,endDt;
            bool yearMonthDtIsValid = DateTime.TryParseExact(dto.YearMonth, ymFormat, CultureInfo.InvariantCulture, DateTimeStyles.None, out yearMonthDt);
            bool startDtIsValid = DateTime.TryParseExact(dto.StartDate, ymdFormat, CultureInfo.InvariantCulture, DateTimeStyles.None, out startDt);
            bool endDtIsValid = DateTime.TryParseExact(dto.EndDate, ymdFormat, CultureInfo.InvariantCulture, DateTimeStyles.None, out endDt);

            if (!yearMonthDtIsValid) return Ok(JsonView(false, "年月格式错误!正确时间格式：yyyy-MM  "));

            if (!startDtIsValid) return Ok(JsonView(false, "开始时间格式错误!正确时间格式：yyyy-MM-dd  "));

            if (!yearMonthDtIsValid) return Ok(JsonView(false, "结束时间格式错误!正确时间格式：yyyy-MM-dd  "));

            List<Pm_WageSheet> wageSheets = new List<Pm_WageSheet>();
            Pm_WageSheet wageSheet = _mapper.Map<Pm_WageSheet>(dto);
            wageSheets.Add(wageSheet);
            //获取OA系统内所有用户
            var nameData = await _usersRep.GetUserNameList(1);
            List<UserNameView>? userNames = nameData.Data;

            _result = await PayrollComputation.SalaryCalculatorAsync(wageSheets, userNames, dto.UserId, dto.YearMonth, startDt, endDt);

            if (_result.Code != 0)
            {
                return Ok(JsonView(false, _result.Msg));
            }

            List<Pm_WageSheet> wageSheets1 = new List<Pm_WageSheet>();
            wageSheets1 = _result.Data;

            #region 处理返回数据

            List<WageSheetInfoView> wageSheetItems = new List<WageSheetInfoView>();
            wageSheetItems = _mapper.Map<List<WageSheetInfoView>>(wageSheets1);
            wageSheetItems = wageSheetItems.Select(it =>
                {
                    UserNameView? uName1 = new UserNameView();
                    UserNameView? uName2 = new UserNameView();
                    uName1 = userNames.Where(it1 => it.UserId == it1.Id).FirstOrDefault();
                    if (uName1 != null) it.Name = uName1.CnName;

                    uName2 = userNames.Where(it1 => it.LastUpdateUserId == it1.Id).FirstOrDefault();
                    if (uName2 != null) it.LastUpdateUserName = uName2.CnName;

                    return it;
                }
            ).ToList();
                #endregion

            sw.Stop();
            return Ok(JsonView(true, "操作成功!耗时：" + (sw.ElapsedMilliseconds / 1000) + "s", wageSheetItems[0]));
           
            
        }

        /// <summary>
        /// 导出工资单
        /// </summary>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> ExportWageCard(string yearMonth)
        {
            Result result = new Result();
            Stopwatch sw = new Stopwatch();
            sw.Start();

            //参数处理
            string ymFormat = "yyyy-MM";
            DateTime yearMonthDt;
            bool yearMonthDtIsValid = DateTime.TryParseExact(yearMonth, ymFormat, CultureInfo.InvariantCulture, DateTimeStyles.None, out yearMonthDt);

            if (!yearMonthDtIsValid) return Ok(JsonView(false, "年月格式错误!正确时间格式：yyyy-MM  "));
            //公司部门
            string sql = string.Format(@"Select row_number() over(order by pm_ws.Id) as Row_Number,
                                         	sc.Id as CompanyId,sc.CompanyName,sd.Id as DepId,sd.DepName, 
                                         	sys_u1.CnName Name,sys_u2.CnName LastUpdateUserName,pm_ws.* 
                                         From Pm_WageSheet pm_ws
                                         Left Join Sys_Users sys_u1 On pm_ws.UserId = sys_u1.Id
                                         Left Join Sys_Users sys_u2 On pm_ws.LastUpdateUserId = sys_u2.Id
                                         Left Join Sys_Company sc On  sys_u1.companyId = sc.Id
                                         Left Join Sys_Department sd On sys_u1.DepId = sd.Id
                                         Where pm_ws.IsDel = 0 And pm_ws.YearMonth = '{0}'", yearMonth);
            var wageSheetList = await _wageSheetRep._sqlSugar.SqlQueryable<ExportWageSheetItemView>(sql).ToListAsync();

            if (wageSheetList.Count <= 0)
            {
                return Ok(JsonView(false, yearMonth + "暂无工资数据!"));
            }

            decimal SumPrice = 0.00M;
            foreach (var item in wageSheetList)
            {
                SumPrice += item.AfterTax;
            }

            WorkbookDesigner designer = new WorkbookDesigner();

            designer.Workbook = new Workbook(AppSettingsHelper.Get("ExcelBasePath") + "Template/工资详细清单.xlsx");
            designer.Workbook.Worksheets[0].Name = yearMonth +" 工资单";
            designer.SetDataSource("WageSheet", wageSheetList);
            designer.SetDataSource("YearMonth", yearMonth);
            designer.SetDataSource("StartEndDt", wageSheetList[0].StartDate +" - "+ wageSheetList[0].EndDate);
            designer.SetDataSource("WorkDays", wageSheetList[0].WorkDays);
            designer.SetDataSource("SumPrice", SumPrice);
            designer.SetDataSource("WageSheetTitle", "工资单");//
            designer.Process();

            string fileName = "WageCard/" + yearMonth + "_工资单_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx";
            string path = AppSettingsHelper.Get("ExcelBasePath");
            designer.Workbook.Save(path + fileName);
            designer = null;

            string excelPath = AppSettingsHelper.Get("ExcelFtpPath") + fileName;
            string url = AppSettingsHelper.Get("ExcelBaseUrl");
            string fileUrl = url + excelPath;

            sw.Stop();
            return Ok(JsonView(true, "操作成功!耗时：" + (sw.ElapsedMilliseconds / 1000) + "s", new { FileUrl = fileUrl }));
        }

        /// <summary>
        /// 下载个税模板
        /// </summary>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> WageSheetTaxTemplate()
        {
            string serverUrl = AppSettingsHelper.Get("WageSheetExcelBaseUrl");
            return Ok(JsonView(true, "操作成功!",new { FileUrl = serverUrl + "Office/WageSheetFile/个税导入模板.xlsx" }));
        }

        /// <summary>
        /// 上传个税
        /// </summary>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> UploadTax(IFormFile file)
        {
            try
            {
                var yearMonth = Request.Headers["YearMonth"].ToString();
                //string yearMonth = "2023-10";
                string ymFormat = "yyyy-MM";
                DateTime yearMonthDt;
                bool yearMonthDtIsValid = DateTime.TryParseExact(yearMonth, ymFormat, CultureInfo.InvariantCulture, DateTimeStyles.None, out yearMonthDt);
                if (!yearMonthDtIsValid) return Ok(JsonView(false, "年月格式错误!正确时间格式：yyyy-MM  "));
                if (file != null)
                {
                    var fileDir = AppSettingsHelper.Get("WageSheetExcelFptPath");
                    //文件名称
                    string projectFileName = file.FileName;

                    //上传的文件的路径
                    string filePath = "";


                    if (!Directory.Exists(fileDir))
                    {
                        Directory.CreateDirectory(fileDir);
                    }

                    //上传的文件的路径
                    filePath = fileDir + $@"\{projectFileName}";

                    if (System.IO.File.Exists(filePath))
                    {
                        //删除文件
                        System.IO.File.Delete(filePath);
                    }

                    using (FileStream fs = System.IO.File.Create(filePath))
                    {
                        file.CopyTo(fs);
                        fs.Flush();
                    }

                    if (System.IO.File.Exists(filePath))
                    {
                        Workbook book = new Workbook(filePath);
                        DataSet dataSet = new DataSet();
                        if (book.Worksheets.Count > 0)
                        {
                            var sheet = book.Worksheets[0];

                            if (sheet != null)
                            {
                                // sheets 中的数据必须存在
                                if (sheet.Cells.MaxDataRow != -1 && sheet.Cells.MaxDataColumn != -1)
                                {
                                    // 方法 ExportDataTable 的参数说明
                                    //  要导出的第一个单元格的行号。
                                    //  要导出的第一个单元格的列号。
                                    //  要导入的行数。
                                    //  要导入的列数。
                                    //  指示第一行的数据是否导出到DataTable的列名。
                                    DataTable dataTable = sheet.Cells.ExportDataTable(0, 0, sheet.Cells.MaxDataRow + 1, sheet.Cells.MaxDataColumn + 1, true);
                                    dataSet.Tables.Add(dataTable);

                                    DataTable taxData = dataSet.Tables[0];

                                    //公司部门
                                    string sql = string.Format(@"Select row_number() over(order by pm_ws.Id) as Row_Number,
                                         	sc.Id as CompanyId,sc.CompanyName,sd.Id as DepId,sd.DepName, 
                                         	sys_u1.CnName Name,sys_u2.CnName LastUpdateUserName,pm_ws.* 
                                         From Pm_WageSheet pm_ws
                                         Left Join Sys_Users sys_u1 On pm_ws.UserId = sys_u1.Id
                                         Left Join Sys_Users sys_u2 On pm_ws.LastUpdateUserId = sys_u2.Id
                                         Left Join Sys_Company sc On  sys_u1.companyId = sc.Id
                                         Left Join Sys_Department sd On sys_u1.DepId = sd.Id
                                         Where pm_ws.IsDel = 0 And pm_ws.YearMonth = '{0}'
                                         Order By UserId Asc ", yearMonth);
                                    var wageSheetList = await _wageSheetRep._sqlSugar.SqlQueryable<WageSheetInfoView>(sql).ToListAsync();

                                    if (wageSheetList.Count <= 0)
                                    {
                                        return Ok(JsonView(false, yearMonth + "工资数据不存在,请先添加工资数据!"));
                                    }

                                    for (int i = 0; i < taxData.Rows.Count; i++)
                                    {
                                        string name = taxData.Rows[i][0].ToString().Trim();

                                        List<WageSheetInfoView> wageSheets = new List<WageSheetInfoView>();
                                        wageSheets = wageSheetList.Where(it => it.Name.Equals(name)).ToList();
                                        if (wageSheets.Count > 0)
                                        {
                                            wageSheetList.Where(it => it.Name.Equals(name))
                                            .Select(it =>
                                             {
                                                 //修改 绩效不等于0.00M的数据
                                                 decimal oldTax = it.WithholdingTax;
                                                 decimal newTax = Convert.ToDecimal(taxData.Rows[i][1].ToString());
                                                 it.WithholdingTax = newTax;
                                                 it.TotalRealHair = it.Should - it.TotalDeductions - newTax;
                                                 return it;
                                             })
                                            .ToList();
                                        }
                                    }
                                    List<Pm_WageSheet> wageSheets1 = new List<Pm_WageSheet>();
                                    wageSheets1 = _mapper.Map<List<Pm_WageSheet>>(wageSheetList);
                                    var updateStatus = _wageSheetRep._sqlSugar
                                        .Updateable(wageSheets1)
                                        .UpdateColumns(it => new { it.WithholdingTax,it.TotalRealHair})
                                        .ExecuteCommand();

                                    if (updateStatus<0)
                                    {
                                        return Ok(JsonView(false, "操作失败！"));
                                    }

                                    if (System.IO.File.Exists(filePath))
                                    {
                                        //删除文件
                                        System.IO.File.Delete(filePath);
                                    }
                                    return Ok(JsonView(true, "操作成功!"));

                                }
                            }
                            else
                            {
                                return Ok(JsonView(false, "工作薄没有数据！"));
                            }


                        }
                    }

                    return Ok(JsonView(true, "上传成功！", projectFileName));
                }
                else
                {
                    return Ok(JsonView(false, "上传失败！"));
                }
            }
            catch (Exception ex)
            {
                return Ok(JsonView(false, "程序错误！"));
                throw;
            }
            return Ok(JsonView(true, "操作成功!"));
        }

        #endregion

    }
}