OA2023/OASystem/OASystem.Infrastructure/Repositories/PersonnelModule/WageSheetRepository.cs

using AutoMapper;
using OASystem.Domain.Dtos.PersonnelModule;
using OASystem.Domain.Entities.PersonnelModule;
using OASystem.Domain.ViewModels.PersonnelModule;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using SqlSugar;
using Result = OASystem.Domain.Result;
using Newtonsoft.Json;

namespace OASystem.Infrastructure.Repositories.PersonnelModule
{
    /// <summary>
    /// 人事模块 工资表 仓库
    /// </summary>
    public class WageSheetRepository : BaseRepository<Pm_WageSheet, WageSheetView>
    {
        private readonly IMapper _mapper;
        private Result _result;

        public WageSheetRepository(SqlSugarClient sqlSugar, IMapper mapper)
            : base(sqlSugar)
        {
            _mapper = mapper;
            _result = new Result();
        }

        /// <summary>
        /// 查询工资 List
        /// </summary>
        /// <returns></returns>
        public async Task<Result> Get_WageSheet_ListByYearMonthAsync(string yearMonth )
        {
            if (string.IsNullOrEmpty(yearMonth))
            {
                _result.Msg = "参数为空";
                return _result;
            }

            //不含公司部门
            // string sql = string.Format(@"Select sys_u1.CnName Name,sys_u2.CnName LastUpdateUserName,pm_ws.LastUpdateDt, pm_ws.Id,pm_ws.YearMonth,pm_ws.StartDate,
            //pm_ws.EndDate,pm_ws.WorkDays,pm_ws.RegularDays,pm_ws.UserId,pm_ws.Should,pm_ws.TotalDeductions,
            //pm_ws.Ex_ItemsRemark,TotalRealHair,pm_ws.WithholdingTax,pm_ws.AfterTax
            //                              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
            //                              Where pm_ws.IsDel = 0 And pm_ws.YearMonth =  '{0}'", yearMonth);

            //公司部门
            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 _sqlSugar.SqlQueryable<WageSheetItemInfoView>(sql).ToListAsync();

            if (wageSheetList.Count <= 0 )
            {
                _result.Msg = "暂无数据!";
                return _result;
            }

            _result.Code = 0;
            _result.Msg = "查询成功!";
            _result.Data = wageSheetList;

            return _result;
        }

        /// <summary>
        /// 查询工资 Info
        /// </summary>
        /// <returns></returns>
        public async Task<Result> Get_WageSheet_InfoByIdAsync(int id)
        {
            string sql = string.Format(@"Select 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
                                         Where pm_ws.IsDel = 0 And pm_ws.Id = {0}", id);
            var wageSheetInfo = await _sqlSugar.SqlQueryable<WageSheetInfoView>(sql).FirstAsync();

            if (wageSheetInfo != null)
            {
                _result.Code = 0;
                _result.Msg = "查询成功!";
                _result.Data = wageSheetInfo;
            }
            
            return _result;
        }

        /// <summary>
        /// 修改
        /// </summary>
        /// <returns></returns>
        public async Task<Result> Post_WageSheet_AddOrEditAsync(WageAddOrEditDto dto)
        {
            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;

            //实发合计 不含个税
            salary = salary + pm_WageSheet.Mealsupplement;
            decimal actualTotal = salary - totalDeduction;
            pm_WageSheet.Should = salary ;
            pm_WageSheet.TotalDeductions = totalDeduction;
            pm_WageSheet.TotalRealHair = actualTotal;
            pm_WageSheet.AfterTax = actualTotal - pm_WageSheet.WithholdingTax;

            #endregion


            if (dto.Status == 1) //添加
            {
                //var select = await _sqlSugar.Queryable<Pm_WageSheet>().
                //    Where(it => it.IsDel == 0 && it.UserId == pm_WageSheet.UserId && it.YearMonth == pm_WageSheet.YearMonth).
                //    FirstAsync();
                //if (select != null) 
                //{
                //    _result.Msg = "该用户 "+ pm_WageSheet.YearMonth + " 工资信息已存在，请前往修改!";
                //    return _result;
                //}

                int add = await _sqlSugar.Insertable(pm_WageSheet).ExecuteReturnIdentityAsync();
                if (add <= 0)
                {
                    _result.Msg = "操作失败!";
                    return _result;
                }
            }
            else if(dto.Status == 2) //修改
            {
                //修改时 更改其他 费用基数时 假勤 费用也将更改 
                if (string.IsNullOrEmpty(dto.Ex_ItemsRemark))
                {
                    List<Ex_Items> ex_Items = new List<Ex_Items>();

                }

                int update = await _sqlSugar.Updateable(pm_WageSheet)
                                            .IgnoreColumns(it =>  new { it.CreateUserId, it.CreateTime,it.DeleteUserId,it.DeleteTime })
                                            .WhereColumns(it => new { it.Id })
                                            .ExecuteCommandAsync();
                if (update <= 0)
                {
                    _result.Msg = "操作失败!";
                    return _result;
                }
            }
            else{
                _result.Msg = "请输入操作状态!1 添加 2 修改";
            }

            _result.Code = 0;
            _result.Msg = "操作成功!";

            return _result;
        }

        /// <summary>
        /// 删除
        /// </summary>
        /// <returns></returns>
        public async Task<Result> Post_WageSheet_DelAsync(WageDelDto dto)
        {
            Pm_WageSheet pm_WageSheet = new Pm_WageSheet()
            {
                Id = dto.Id,
                DeleteUserId = dto.UserId,
                DeleteTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"),
                LastUpdateUserId = dto.UserId,
                IsDel = 1
            };
            int update = await _sqlSugar.Updateable(pm_WageSheet)
                                             .UpdateColumns(it => new { it.DeleteUserId, it.DeleteTime, it.IsDel, it.LastUpdateUserId, it.LastUpdateDt })
                                             .WhereColumns(it => new { it.Id })
                                             .ExecuteCommandAsync();
            if (update <= 0)
            {
                _result.Msg = "操作失败!";
                return _result;
            }

            _result.Code = 0;
            _result.Msg = "操作成功!";

            return _result;
        }
    }
}