OA2023/OASystem/OASystem.Infrastructure/Repositories/Groups/DecreasePaymentsRepository.cs

using AutoMapper;
using NPOI.HPSF;
using NPOI.SS.Formula.Functions;
using OASystem.Domain;
using OASystem.Domain.AesEncryption;
using OASystem.Domain.Dtos.Groups;
using OASystem.Domain.Entities.Financial;
using OASystem.Domain.Entities.Groups;
using OASystem.Domain.Entities.Resource;
using OASystem.Domain.ViewModels.Financial;
using OASystem.Domain.ViewModels.Groups;
using OASystem.Domain.ViewModels.QiYeWeChat;
using OASystem.Domain.ViewModels.Resource;
using OASystem.Infrastructure.Repositories.System;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Intrinsics.Arm;
using System.Text;
using System.Threading.Tasks;

namespace OASystem.Infrastructure.Repositories.Groups
{
    public class DecreasePaymentsRepository : BaseRepository<Grp_DecreasePayments, Grp_DecreasePayments>
    {
        private readonly IMapper _mapper;
        private readonly TeamRateRepository _teamRateRep;
        private readonly SetDataRepository _setDataRepository;
        public DecreasePaymentsRepository(SqlSugarClient sqlSugar, IMapper mapper, TeamRateRepository teamRateRep, SetDataRepository setDataRepository )
            : base(sqlSugar)
        {
            this._mapper = mapper;
            _teamRateRep = teamRateRep;
            this._setDataRepository = setDataRepository;
        }
        /// <summary>
        /// 根据团组Id查询数据
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        public async Task<JsonView> DecreasePaymentsList(DecreasePaymentsListDto dto)
        {
            string sqlWhere = "";
            if (dto.IsPaySign != -1)
            {
                sqlWhere += string.Format(@" And ccp.IsPay = {0} ", dto.IsPaySign);
            }

            //雷怡 2024-5-8 15:14 新增View字段 CreateTime
            string sql = string.Format(@" Select gdp.Id,gdp.DiId,gdp.PriceName,gdp.FeeTotal,sd1.[Name] as Currency,gdp.FilePath,su.CnName as CreateUserName,
                                              ccp.IsAuditGM as isAudit,ccp.IsPay,gdp.CreateTime
                                              From Grp_DecreasePayments as gdp With(Nolock) Left Join Grp_CreditCardPayment as ccp With(Nolock) On gdp.Id = ccp.CId
                                              Left Join Sys_SetData as sd1 On gdp.Currency = sd1.Id
                                              Left Join Sys_Users as su On gdp.CreateUserId = su.Id
                                              Where gdp.DiId = {0} And ccp.CTable = 98 {2} And ccp.IsDel = 0 And gdp.IsDel = 0 And gdp.CreateUserId in ({1}) ", dto.DiId, dto.UserId, sqlWhere);
            List<DecreasePaymentsView> _DecreasePayments = await _sqlSugar.SqlQueryable<DecreasePaymentsView>(sql).ToListAsync();
            
            return new JsonView() { Code = 200, Msg = MsgTips.Succeed, Data = _DecreasePayments };
        }

        public async Task<JsonView> DecreasePaymentsSelect(DecreasePaymentsDto dto)
        {
            
            #region 团组下拉框

            List<Grp_GroupsTaskAssignment> grp_GroupsTaskAssignment = Query<Grp_GroupsTaskAssignment>(a => a.IsDel == 0 && a.UId == dto.UserId && a.CTId == dto.CTId).ToList();

            string DiId = "";

            List<DecreasePaymentGroupView> _Delegations = new List<DecreasePaymentGroupView>();
            if (grp_GroupsTaskAssignment.Count > 0)
            {


                foreach (var item in grp_GroupsTaskAssignment)
                {
                    DiId += item.DIId + ",";
                }
                if (DiId != "0")
                {
                    DiId = DiId.Substring(0, DiId.Length - 1);
                }

                string sql = string.Format($@"Select 
                                        (select ssd.name from Sys_SetData ssd  WHERE  ssd.id  = di.TeamLevSId) as TeamLevSId,
                                        di.Id,
										di.TeamName,
										di.ClientUnit,
										di.ClientName,
										di.TourCode,
										di.TeamDid,
										sd.[Name] As TeamTypeName,
										di.VisitCountry,
										di.VisitStartDate,
										di.VisitEndDate,
										di.VisitDays,
										di.VisitPNumber,
										di.CreateTime
										From Grp_DelegationInfo di With(NoLock)
										Left Join Sys_SetData sd On di.TeamDid = sd.Id
                                        Where di.Id in({DiId}) and di.IsDel=0 
										Order By di.VisitStartDate Desc");

                //DecreasePaymentGroupView
                _Delegations = _sqlSugar.SqlQueryable<DecreasePaymentGroupView>(sql).ToList();
            }
            #endregion

            #region 其他下拉框查询

            //支付方式
            List<Sys_SetData> Payment = _sqlSugar.Queryable<Sys_SetData>().Where(a => a.STid == 14 && a.IsDel == 0).ToList();
            List<SetDataInfoView> _Payment = _mapper.Map<List<SetDataInfoView>>(Payment);

            //供应商 supplier
            List<Sys_SetData> supplier = _sqlSugar.Queryable<Sys_SetData>().Where(a => a.STid == 21 && a.IsDel == 0).ToList();
            List<SetDataInfoView> _supplier = _mapper.Map<List<SetDataInfoView>>(supplier);
            if (_supplier.Count > 0)
            {
                var d1 = _supplier.Find(it => it.Name.Equals("策划 公司"));
                if (d1 != null) _supplier.Remove(d1);

                var d2 = _supplier.Find(it => it.Name.Equals("礼仪"));
                if (d2 != null) _supplier.Remove(d2);

                var d3 = _supplier.Find(it => it.Name.Equals("日常办公用品"));
                if (d3 != null) _supplier.Remove(d3);

                var d4 = _supplier.Find(it => it.Name.Equals("其他"));
                if (d4 != null)
                {
                    _supplier.Remove(d4);
                    _supplier.Add(d4);
                }

            }
            List<dynamic> _supplierArea = new List<dynamic>() {
                new { id=1,name = "国内" },
                new { id=2,name = "国外" },
            };

            #endregion

            var data = new
            {
                payment = _Payment,
                groupName = _Delegations,
                supplier = _supplier,
                supplierArea = _supplierArea
            };
            return  new JsonView() { Code = 200, Msg = MsgTips.Succeed, Data = data };

        }

        public async Task<JsonView> OpDecreasePayments(DecreasePaymentsOpDto dto)
        {
            BeginTran();
            int id = 0;
            var grp_Decrease = _mapper.Map<Grp_DecreasePayments>(dto);
            //处理费用总计
            if (grp_Decrease.FeeTotal == 0.00M)
            {
                grp_Decrease.FeeTotal = grp_Decrease.Price * grp_Decrease.Price;
            }

            List<TeamRateDescAddCurrencyIdView> teamRates = await _teamRateRep.PostGroupTeamRateItemByDiIdAndCTableId(1, dto.DiId, 98);
            if (dto.Status == 1)//添加
            {
                string selectSql = string.Format(@"select * from Grp_DecreasePayments where PriceName='{0}' and IsDel={1} and DiId={2}"
                                                , dto.PriceName, 0, dto.DiId);
                //var DecreasePayments = await _sqlSugar.SqlQueryable<Grp_DecreasePayments>(selectSql).FirstAsync();//查询是否存在
                //if (DecreasePayments != null)
                //{
                //    return new JsonView() { Code = 400, Msg = "该数据已存在，请勿重复添加！" };
                //}
                //else//不存在，可添加
                //{
                    id = await AddAsyncReturnId(grp_Decrease);
                    if (id < 1)
                    {
                        RollbackTran();
                        return new JsonView() { Code = 400, Msg = "添加失败！" };
                    }

                    //进行C表添加
                    Grp_CreditCardPayment C = new Grp_CreditCardPayment();
                    C.PayDId = dto.PayDId;
                    C.ConsumptionPatterns = "";
                    C.ConsumptionDate = "";
                    C.CTDId = 0;
                    C.BankNo = "";
                    C.CardholderName = "";
                    C.PayMoney = grp_Decrease.FeeTotal;
                    C.PaymentCurrency = grp_Decrease.Currency;
                    //当天汇率
                    //if (!string.IsNullOrEmpty(hfRate.Value))
                    //    C.DayRate = hfRate.Value;
                    //else
                    //C.DayRate = "";
                    C.CompanyBankNo = "";
                    C.OtherBankName = dto.OtherBankName;
                    C.OtherSideNo = dto.OtherSideNo;
                    C.OtherSideName = dto.OtherSideName;
                    C.Remark = "";
                    C.CreateUserId = grp_Decrease.CreateUserId;
                    C.MFOperator = 0;
                    C.MFOperatorDate = "";
                    C.IsAuditDM = 0;
                    C.AuditDMOperate = 0;
                    C.AuditDMDate = "";
                    C.IsAuditMF = 0;
                    C.AuditMFOperate = 0;
                    C.AuditMFDate = "";
                    C.IsAuditGM = 0;
                    C.AuditGMOperate = 21;
                    C.AuditGMDate = "";

                    //2025-04-07 第四次更改 PayDId == 72（刷卡） IsPay == 1
                    if (C.PayDId == 72) C.IsPay = 1;

                    C.DIId = grp_Decrease.DiId;
                    C.CId = id;
                    C.CTable = 98;
                    C.PayPercentage = 100;
                    C.PayThenMoney = 0;
                    C.PayPercentageOld = 0;
                    C.PayThenMoneyOld = 0;
                    C.UpdateDate = "";
                    C.Payee = dto.SupplierName;
                    C.OrbitalPrivateTransfer = dto.OrbitalPrivateTransfer;
                    C.ExceedBudget = 0;
                    //C.RMBPrice = 0.00f;

                    //设置该团组的汇率
                    var teamRate = teamRates.Where(it => it.CurrencyId == grp_Decrease.Currency).FirstOrDefault();
                    //Grp_TeamRate _TeamRate = _sqlSugar.Queryable<Grp_TeamRate>().First(a => a.DiId == grp_Decrease.DiId && a.IsDel == 0 && a.CTable == 98);
                    if (teamRate != null)
                    {
                        if (teamRate.CurrencyCode.Equals("CNY"))
                        {
                            C.DayRate = 1.0000M;
                            C.RMBPrice = C.PayMoney;
                        }
                        else
                        {
                            C.DayRate = teamRate.Rate;
                            C.RMBPrice = C.PayMoney * C.DayRate;
                        }
                    }
                    else
                    {
                        RollbackTran();
                        return new JsonView() { Code = 400, Msg = $"添加失败！团组汇率未设置{_sqlSugar.Queryable<Sys_SetData>().First(it => it.IsDel == 0 && it.Id == grp_Decrease.Currency)?.Name ?? ""}该币种汇率!" };
                    }
                    int cId = await _sqlSugar.Insertable(C).ExecuteReturnIdentityAsync();
                    if (cId > 1)
                    {

                        #region 策划部内容同步添加至资料库
                        //var userInfo = _sqlSugar.Queryable<Sys_Users>()
                        //    .LeftJoin<Sys_Department>((u,d)=> d.IsDel == 0 && u.DepId == d.Id )
                        //    .Where((u, d) => u.Id == dto.CreateUserId && d.DepName == "策划部" && u.IsDel == 0)
                        //    .First();

                        //if (userInfo != null)
                        //{
                        //    Res_MediaSuppliers mediaSuppliersData = new Res_MediaSuppliers
                        //    {
                        //        CreateTime = DateTime.Now,
                        //        CreateUserId = dto.CreateUserId,
                        //        UnitName = dto.SupplierName,
                        //        TypeId = dto.SupplierTypeId,
                        //        UnitAddress = dto.SupplierAddress,
                        //        Contact = dto.SupplierContact,
                        //        Tel = dto.SupplierContactNumber,
                        //        Email = dto.SupplierEmail,
                        //        Privince = dto.SupplierArea == 1 ? "国内" :"国外",
                        //    };

                        //    EncryptionProcessor.EncryptProperties(mediaSuppliersData);
                        //    _sqlSugar.Insertable(mediaSuppliersData).ExecuteCommand();
                        //}

                        #endregion

                        CommitTran();
                        var data = new { ccpId = cId, sign = 1,dataId = id };
                        return new JsonView() { Code = 200, Msg = "添加成功！", Data = data };
                    }

                    RollbackTran();
                    return new JsonView() { Code = 400, Msg = "添加失败！" };
               // }
            }
            else if (dto.Status == 2)
            {
                //已审核、已支付 不可修改
                var grp_CreditCardPayment = _sqlSugar.Queryable<Grp_CreditCardPayment>().First(a => a.CId == grp_Decrease.Id && a.CTable == 98 && a.CId == grp_Decrease.Id && a.IsDel == 0);

                if (grp_CreditCardPayment == null) return new JsonView() { Code = 400, Msg = MsgTips.Fail };

                var isAuditGM = grp_CreditCardPayment.IsAuditGM;
                if (isAuditGM == 1 || isAuditGM == 3) return new JsonView() { Code = 400, Msg = $"该费用已通过审核，不可编辑" };

                if (grp_CreditCardPayment.IsPay == 1) return new JsonView() { Code = 400, Msg = $"该费用已付款，不可编辑！" };

                bool res = await UpdateAsync(a => a.Id == grp_Decrease.Id, a => new Grp_DecreasePayments
                {
                    DiId = grp_Decrease.DiId,
                    SupplierArea = grp_Decrease.SupplierArea,
                    SupplierTypeId = grp_Decrease.SupplierTypeId,
                    SupplierName = grp_Decrease.SupplierName,
                    SupplierContact = grp_Decrease.SupplierContact,
                    SupplierContactNumber = grp_Decrease.SupplierContactNumber,
                    SupplierSocialAccount = grp_Decrease.SupplierSocialAccount,
                    SupplierEmail = grp_Decrease.SupplierEmail,
                    SupplierAddress = grp_Decrease.SupplierAddress,
                    PriceName = grp_Decrease.PriceName,
                    Price = grp_Decrease.Price,
                    Quantity = grp_Decrease.Quantity,
                    FeeTotal = grp_Decrease.FeeTotal,
                    Currency = grp_Decrease.Currency,
                    FilePath = grp_Decrease.FilePath,
                    OTAOrderNo = grp_Decrease.OTAOrderNo,
                    Remark = grp_Decrease.Remark,
                });
                if (res)
                {
                    //设置该团组的汇率
                    var teamRate = teamRates.Where(it => it.CurrencyId == grp_Decrease.Currency).FirstOrDefault();

                    if (teamRate != null)
                    {
                        if (teamRate.CurrencyCode.Equals("CNY"))
                        {
                            grp_CreditCardPayment.DayRate = 1.0000M;
                            grp_CreditCardPayment.RMBPrice = grp_CreditCardPayment.PayMoney;
                        }
                        else
                        {
                            grp_CreditCardPayment.DayRate = teamRate.Rate;
                            grp_CreditCardPayment.RMBPrice = grp_CreditCardPayment.PayMoney * grp_CreditCardPayment.DayRate;
                        }
                    }
                    else
                    {
                        RollbackTran();
                        return new JsonView() { Code = 400, Msg = "修改失败！" };
                    }

                    //2025-04-07 第四次更改 PayDId == 72（刷卡） IsPay == 1
                    int isPay = 0;
                    if (dto.PayDId == 72) isPay = 1;

                    int CTable = await _sqlSugar.Updateable<Grp_CreditCardPayment>()
                        .Where(a => a.Id == grp_CreditCardPayment.Id)
                        .SetColumns(a => new Grp_CreditCardPayment
                        {
                            OtherSideName = dto.OtherSideName,
                            OtherSideNo = dto.OtherSideNo,
                            OtherBankName = dto.OtherBankName,
                            PayDId = dto.PayDId,
                            IsPay = isPay,
                            PayMoney = grp_Decrease.FeeTotal,
                            PaymentCurrency = grp_Decrease.Currency,
                            Payee = dto.SupplierName,
                            OrbitalPrivateTransfer = dto.OrbitalPrivateTransfer,
                            DayRate = grp_CreditCardPayment.DayRate,
                            RMBPrice = grp_CreditCardPayment.RMBPrice,
                            //IsAuditGM = auto ? 3 : grp_CreditCardPayment.IsAuditGM
                        })
                        .ExecuteCommandAsync();
                    if (CTable > 0)
                    {
                        CommitTran();

                        var data = new { ccpId = grp_CreditCardPayment.Id, sign = 2, dataId = grp_Decrease.Id };

                        return new JsonView() { Code = 200, Msg = "修改成功！", Data = data };
                    }
                }
            }
            RollbackTran();
            return new JsonView() { Code = 400, Msg = MsgTips.Fail }; 
        }

        public async Task<Result> PostGroupNameAndEasy(DecreasePaymentsDto dto)
        {
            Result result = new Result() { Code = -2, Msg = "未知错误" };
            try
            {
                #region 团组下拉框


                List<Grp_GroupsTaskAssignment> grp_GroupsTaskAssignment = Query<Grp_GroupsTaskAssignment>(a => a.IsDel == 0 && a.UId == dto.UserId && a.CTId == dto.CTId).ToList();

                string DiId = "";
                foreach (var item in grp_GroupsTaskAssignment)
                {
                    DiId += item.DIId + ",";
                }
                if (!string.IsNullOrWhiteSpace(DiId))
                {
                    DiId = DiId.Substring(0, DiId.Length - 1);
                }
                else
                {
                    DiId = "0";
                }

                string sql = string.Format(@"select Id,TourCode,TeamName,ClientName,VisitStartDate,VisitEndDate,VisitCountry,VisitDays,VisitPNumber from Grp_DelegationInfo where Id in({0}) and IsDel={1}", DiId, 0);
                List<DelegationEasyView> grp_Delegations = _sqlSugar.SqlQueryable<DelegationEasyView>(sql).ToList();
                if (grp_Delegations.Count != 0)
                {
                    int count = grp_Delegations.Count;
                    float totalPage = (float)count / dto.PageSize;//总页数
                    if (totalPage == 0) totalPage = 1;
                    else totalPage = (int)Math.Ceiling((double)totalPage);

                    List<DelegationEasyView> delegationEasyViews = new List<DelegationEasyView>();
                    for (int i = 0; i < dto.PageSize; i++)
                    {
                        var RowIndex = i + (dto.PageIndex - 1) * dto.PageSize;
                        if (RowIndex < grp_Delegations.Count)
                        {
                            delegationEasyViews.Add(grp_Delegations[RowIndex]);
                        }
                        else
                        {
                            break;
                        }
                    }
                    ListViewBase<DelegationEasyView> rst = new ListViewBase<DelegationEasyView>();
                    rst.DataList = delegationEasyViews;
                    rst.DataCount = count;
                    rst.CurrPageIndex = dto.PageIndex;
                    rst.CurrPageSize = dto.PageSize;
                    return result = new Result() { Code = 0, Msg = "查询成功！", Data = rst };
                }
                else
                {
                    ListViewBase<DelegationEasyView> rst = new ListViewBase<DelegationEasyView>();
                    rst.DataList = new List<DelegationEasyView>();
                    return result = new Result() { Code = 0, Msg = "暂无数据！", Data = rst };
                }
                #endregion

            }
            catch (Exception ex)
            {
                return result = new Result() { Code = -2, Msg = "程序错误" };
                throw;
            }
        }

        /// <summary>
        /// 根据团组增减款项表Id查询数据
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        /// <exception cref="NotImplementedException"></exception>
        public async Task<JsonView> QueryDecreasePaymentsById(DecreasePaymentsByIdDto dto)
        {
            string sql = string.Format($@"Select 
											dp.Id,
											dp.DiId,
											dp.SupplierArea,
											dp.SupplierTypeId,
											dp.SupplierName,
											dp.SupplierContact,
											dp.SupplierContactNumber,
											dp.SupplierSocialAccount,
											dp.SupplierEmail,
											dp.SupplierAddress,
											dp.PriceName,
											dp.Price,
											dp.Quantity,
											dp.FeeTotal,
											dp.Currency,
											dp.FilePath,
                                            dp.OTAOrderNo,
											dp.Remark,
											ccp.PayDId,
											ccp.OrbitalPrivateTransfer,
											ccp.OtherBankName,
											ccp.OtherSideName,
											ccp.OtherSideNo,
                                            ccp.IsAuditGM
										From Grp_DecreasePayments dp With(NoLock)
										Left Join Grp_CreditCardPayment ccp With(NoLock) On dp.Id = ccp.CId And dp.DiId = ccp.DIId And ccp.CTable = 98
										Where dp.IsDel = 0 And dp.Id = {dto.Id}");

            var info = await _sqlSugar.SqlQueryable<DecreasePaymentsInfoView>(sql).FirstAsync();

            return new JsonView() { Code = 200, Msg = MsgTips.Succeed, Data = info };
        }

        /// <summary>
        /// Del
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        /// <exception cref="NotImplementedException"></exception>
        public async Task<Result>  _Del(int id, int userId)
        {
            Result result = new Result() { Code = -2, Msg = "删除失败！" };

            var ccpInfo =await _sqlSugar.Queryable<Grp_CreditCardPayment>()
                .FirstAsync(x => x.CId == id && x.CTable == 98 && x.IsDel == 0);
            if (ccpInfo == null) return new Result(-2, "数据不存在，不可删除！");

            var auditStatus = new List<int>() {
                    1, // 已通过
            };

            if (auditStatus.Any(x => x == ccpInfo.IsAuditGM)) return new Result(-2, "该费用已手动审核，不可删除！");

            if (ccpInfo.IsPay == 1) return new Result(-2, "该费用已付款，不可删除！");

            _sqlSugar.BeginTran();

            var del = await _sqlSugar.Updateable<Grp_DecreasePayments>()
                                     .SetColumns(it => new Grp_DecreasePayments()
                                     {
                                         IsDel = 1,
                                         DeleteUserId = userId,
                                         DeleteTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
                                     }).Where(it => it.Id == id)
                                     .ExecuteCommandAsync();
            if (del > 0)
            {
                var del1 = await _sqlSugar.Updateable<Grp_CreditCardPayment>()
                                          .SetColumns(a => new Grp_CreditCardPayment()
                                          {
                                              IsDel = 1,
                                              DeleteUserId = userId,
                                              DeleteTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
                                          })
                                          .Where(a => a.CId == id && a.CTable == 98)
                                          .ExecuteCommandAsync();
                if (del1 > 0)
                {
                    _sqlSugar.CommitTran();
                    result.Code = 0;
                    result.Msg = "删除成功!";
                    return result;
                }
            }
            _sqlSugar.RollbackTran();
            return result;
        }
    }
}