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 OASystem.Infrastructure.Tools;
using System;
using System.Collections.Generic;
using System.Globalization;
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);
            }

            // 其他款项-查看团组全部费用UserIds
            var permUserIds = new List<int>() {
                400, // 李允
            };

            var typeIds = new List<int>() {
                302, // 成都-会务活动
                691, // 四川-会务活动
                762, // 四川-赛事项目收入
                1047,// 成都-赛事项目收入
                1539,// 纽茵-会务活动
                1548 // 重庆-会务活动
            };

            var isSpecialType = await _sqlSugar.Queryable<Grp_DelegationInfo>().AnyAsync(x => x.IsDel == 0 && x.Id == dto.DiId && typeIds.Contains(x.TeamDid));

            if (!isSpecialType || !permUserIds.Contains(dto.UserId))
            {
                sqlWhere += string.Format(@" AND gdp.CreateUserId IN({0})", dto.UserId);
            }

            //雷怡 2024-5-8 15:14 新增View字段 CreateTime
            string sql = string.Format(@"Select gdp.Id,gdp.DiId,
                                         CASE
                                            WHEN gdp.PaymentPercent <> 100
                                                THEN gdp.PriceName
                                                     + '（'
                                                     + gdp.PaymentType
                                                     + '）'
                                                     -- + ' '
                                                     -- + CAST(CAST(gdp.PaymentPercent AS INT) AS VARCHAR(10))
                                                     -- + '%)'
                                            ELSE gdp.PriceName
                                            END AS PriceName,
                                         -- gdp.FeeTotal, 
                                         gdp.PaymentType,
                                         gdp.PaymentPercent,
                                         gdp.ActualPaymentAmount 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 {1} And ccp.IsDel = 0 And gdp.IsDel = 0 order by gdp.CreateTime desc,gdp.Id desc",
                                              dto.DiId, sqlWhere);
            var _DecreasePayments = await _sqlSugar.SqlQueryable<DecreasePaymentsView>(sql).ToListAsync();

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

        /// <summary>
        /// 获取付款方式配置（STid = 138）
        /// </summary>
        public async Task<List<PaymentTypeView>> GetPaymentTypesAsync()
        {
            const int PaymentSettingStId = 138;

            var paymentTypes = await _sqlSugar
                .Queryable<Sys_SetData>()
                .Where(x => x.STid == PaymentSettingStId && x.IsDel == 0)
                .ToListAsync();

            return paymentTypes.Select(x =>
            {
                var y = new PaymentTypeView()
                {
                    PaymentType = x.Name,
                    PaymentPercent = x.Remark.SafeParseDecimal(),
                };

                return y;
            }).ToList();
        }

        public async Task<JsonView> DecreasePaymentsSelect(DecreasePaymentsDto dto)
        {
            #region 团组下拉框
            var isGm = AppSettingsHelper
                        .Get<int>("DecreasePaymentsSelectIsGm")
                        .Contains(dto.UserId);
            var _Delegations = new List<DecreasePaymentGroupView>();

            if (isGm && dto.CTId != 81)
            {
                isGm = false;
            }

            if (!isGm)
            {
                var grp_GroupsTaskAssignment = Query<Grp_GroupsTaskAssignment>(a => a.IsDel == 0 && a.UId == dto.UserId && a.CTId == dto.CTId).ToList();
                string DiId = "";
                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");
                    _Delegations = _sqlSugar.SqlQueryable<DecreasePaymentGroupView>(sql).ToList();
                }
            }
            else
            {
                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.IsDel=0 
										Order By di.VisitStartDate Desc");
                _Delegations = _sqlSugar.SqlQueryable<DecreasePaymentGroupView>(sql).ToList();
            }
            #endregion

            #region 其他下拉框查询

            var setdata = _sqlSugar.Queryable<Sys_SetData>().Where(a => a.IsDel == 0).ToList();

            // 支付方式
            var payment = setdata.Where(a => a.STid == 14).ToList();
            var _payment = _mapper.Map<List<SetDataInfoView>>(payment);

            // 供应商
            var supplier = setdata.Where(a => a.STid == 21).ToList();
            var _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);
                }

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

            // 付款方式
            var _paymentOptions =  await GetPaymentTypesAsync();

            #endregion

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

        public async Task<JsonView> OpDecreasePayments(DecreasePaymentsOpDto dto)
        {
            //  输入验证
            if (dto == null) return new JsonView { Code = 400, Msg = "参数不能为空" };

            BeginTran();
            int id = 0;
            var grp_Decrease = _mapper.Map<Grp_DecreasePayments>(dto);
            var recheck = true;

            //处理费用总计
            if (grp_Decrease.FeeTotal == 0.00M)
            {
                grp_Decrease.FeeTotal = grp_Decrease.Quantity * grp_Decrease.Price;
            }

            // 处理未选择付款类型
            if (string.IsNullOrEmpty(dto.PaymentType))
            {
                grp_Decrease.PaymentType = "全款";
                grp_Decrease.PaymentPercent = 100m;
            }

            // 处理此次 付款金额
            // grp_Decrease.ActualPaymentAmount = grp_Decrease.FeeTotal * grp_Decrease.PaymentPercent / 100m;

            // 获取汇率（复用查询）
            var teamRates = await _teamRateRep.PostGroupTeamRateItemByDiIdAndCTableId(1, dto.DiId, 98);
            var teamRate = teamRates.Where(it => it.CurrencyId == grp_Decrease.Currency).FirstOrDefault();
            if (teamRate == null)
            {
                var currencyName = await _sqlSugar.Queryable<Sys_SetData>()
                    .Where(it => it.IsDel == 0 && it.Id == grp_Decrease.Currency)
                    .Select(it => it.Name)
                    .FirstAsync() ?? "";
                return new JsonView { Code = 400, Msg = $"添加失败！团组汇率未设置{currencyName}该币种汇率!" };
            }

            if (dto.Status == 1)//添加
            {
                id = await AddAsyncReturnId(grp_Decrease);
                grp_Decrease.Id = id;

                #region C表赋值

                var ccp = new Grp_CreditCardPayment
                {
                    PayDId = dto.PayDId,
                    ConsumptionPatterns = "",
                    ConsumptionDate = "",
                    CTDId = 0,
                    BankNo = "",
                    CardholderName = "",
                    PayMoney = grp_Decrease.ActualPaymentAmount,
                    PaymentCurrency = grp_Decrease.Currency,
                    CompanyBankNo = "",
                    OtherBankName = dto.OtherBankName,
                    OtherSideNo = dto.OtherSideNo,
                    OtherSideName = dto.OtherSideName,
                    Remark = "",
                    CreateUserId = grp_Decrease.CreateUserId,
                    MFOperator = 0,
                    MFOperatorDate = "",
                    IsAuditDM = 0,
                    AuditDMOperate = 0,
                    AuditDMDate = "",
                    IsAuditMF = 0,
                    AuditMFOperate = 0,
                    AuditMFDate = "",
                    IsAuditGM = 0,
                    AuditGMOperate = 21,
                    AuditGMDate = "",
                    DIId = grp_Decrease.DiId,
                    CId = id,
                    CTable = 98,
                    PayPercentage = 100,
                    PayThenMoney = 0,
                    PayPercentageOld = 0,
                    PayThenMoneyOld = 0,
                    UpdateDate = "",
                    Payee = dto.SupplierName,
                    OrbitalPrivateTransfer = dto.OrbitalPrivateTransfer,
                    ExceedBudget = 0,
                };

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

                if (teamRate.CurrencyCode.Equals("CNY"))
                {
                    ccp.DayRate = 1.0000M;
                    ccp.RMBPrice = ccp.PayMoney;
                }
                else
                {
                    ccp.DayRate = teamRate.Rate;
                    ccp.RMBPrice = ccp.PayMoney * ccp.DayRate;
                }

                #endregion 

                int cId = await _sqlSugar.Insertable(ccp).ExecuteReturnIdentityAsync();
                if (cId > 1)
                {
                    #region 付款类型为预付款时，添加尾款信息

                    if (grp_Decrease.PaymentType.Contains("预付款"))
                    {

                        var balancePercent = 100m - grp_Decrease.PaymentPercent;
                        grp_Decrease.ParentId = id;
                        grp_Decrease.PaymentType = "尾款";
                        grp_Decrease.PaymentPercent = balancePercent;
                        grp_Decrease.ActualPaymentAmount = grp_Decrease.FeeTotal - grp_Decrease.ActualPaymentAmount;

                        var balanceId = await AddAsyncReturnId(grp_Decrease);
                        if (balanceId < 1)
                        {
                            RollbackTran();
                            return new JsonView() { Code = 200, Msg = $"{grp_Decrease.PriceName}尾款信息添加失败！" };
                        }

                        ccp.CId = balanceId;
                        ccp.PayMoney = grp_Decrease.ActualPaymentAmount;
                        if (teamRate.CurrencyCode.Equals("CNY"))
                        {
                            ccp.DayRate = 1.0000M;
                            ccp.RMBPrice = ccp.PayMoney;
                        }
                        else
                        {
                            ccp.DayRate = teamRate.Rate;
                            ccp.RMBPrice = ccp.PayMoney * ccp.DayRate;
                        }

                        var balanceCId = await _sqlSugar.Insertable(ccp).ExecuteReturnIdentityAsync();
                        if (balanceCId < 1)
                        {
                            RollbackTran();
                            return new JsonView() { Code = 200, Msg = $"{grp_Decrease.PriceName}尾款信息添加失败！" };
                        }
                    }

                    #endregion

                    #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

                    #region 世运会自动审核处理
                    if (dto.DiId == 2590)
                    {
                        bool isAuto = false;
                        var spPriceName = dto.PriceName.Split('-').ToArray();
                        var expression = Expressionable.Create<Grp_GamesBudgetMaster>()
                                                        .And(x => x.IsDel == 0);

                        if (spPriceName.Length >= 2)
                        {
                            expression.And(x => x.T0 == spPriceName[0])
                                      .And(x => x.CalculationContent == spPriceName[1]);

                            var setting = _sqlSugar.Queryable<Grp_GamesBudgetMaster>()
                                                    .First(expression.ToExpression());

                            if (setting != null)
                            {
                                var dbList = _sqlSugar.Queryable<Grp_DecreasePayments>()
                                            .LeftJoin<Grp_CreditCardPayment>((x, a) => x.Id == a.CId && a.IsDel == 0 && a.CTable == 98 && a.DIId == dto.DiId)
                                            .Where((x, a) => x.IsDel == 0 && x.PriceName.Contains(spPriceName[0]) && x.PriceName.Contains(spPriceName[1]) && x.Id != id)
                                            .Select((x, a) => new
                                            {
                                                x,
                                                a.RMBPrice,
                                            })
                                            .ToList();

                                //币种问题 (836 CNY)
                                if (dto.Currency != 836)
                                {
                                    var rate = teamRates.Where(it => it.CurrencyId == grp_Decrease.Currency).FirstOrDefault();
                                    if (rate != null)
                                    {
                                        //dto.Price *= rate.Rate;
                                        dto.FeeTotal *= rate.Rate;
                                    }
                                }

                                //现有金额
                                var sumPrice = dbList.Sum(x => x.RMBPrice);

                                //现有数量
                                //var sumCount = dbList.Sum(x => x.Quantity);

                                var availableAmount = setting.ItemTotal - sumPrice;
                                //var availableQuantity = setting.Quantity - sumCount;

                                if (availableAmount > 0)  // && availableQuantity > 0
                                {

                                    //单价计算
                                    //var settingPrice = setting.UnitPrice;
                                    //if (int.TryParse(setting.CycleUnit, out int cycleUnit))
                                    //{
                                    //    settingPrice *= cycleUnit;
                                    //}

                                    if (dto.FeeTotal <= availableAmount) //dto.Price <= settingPrice &&  && dto.Quantity <= setting.Quantity
                                    {
                                        isAuto = true;
                                    }

                                }
                            }
                        }

                        if (isAuto)
                        {
                            var execCount = _sqlSugar.Updateable<Grp_CreditCardPayment>()
                                .SetColumns(a => new Grp_CreditCardPayment
                                {
                                    IsAuditGM = 3, //自动审核通过
                                    AuditGMOperate = 4,
                                    AuditGMDate = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
                                })
                                .Where(a => a.CId == id && a.CTable == 98 && a.DIId == 2590)
                                .ExecuteCommand();
                            recheck = false;
                        }

                    }
                    #endregion

                    #region 会务成本自动审核处理

                    var auditRes = await AutoAuditConferenceCostAsync(grp_Decrease, teamRates);
                    recheck = auditRes.isRecheck;

                    #endregion

                    CommitTran();
                    var data = new { ccpId = cId, sign = 1, dataId = id, recheck };
                    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);

                // 修改 付款类型验证 尾款时不能修改为非尾款
                var editValid = _sqlSugar.Queryable<Grp_DecreasePayments>()
                    .Where(a => a.Id == grp_Decrease.Id && a.IsDel == 0)
                    .Select(a => new
                    {
                        a.ParentId,
                        a.PaymentType,
                        a.PaymentPercent,
                        a.Price,
                        a.Quantity,
                        a.PriceName,
                        a.FeeTotal,
                    })
                    .First();
                if (editValid.ParentId == grp_Decrease.Id && editValid.PaymentType.Contains("尾款"))
                {
                    // 原付款类型为尾款时，修改时必须包含尾款且付款比例、价格信息不变
                    if (!dto.PaymentType.Contains("尾款"))
                    {
                        return new JsonView() { Code = 400, Msg = $"尾款信息不可修改为非尾款选项！" };
                    }

                    if (editValid.PriceName != dto.PriceName)
                    {
                        return new JsonView() { Code = 400, Msg = $"尾款信息的费用名称不可修改，若要修改请前往预付款修改！" };
                    }

                    //if (editValid.PriceName != dto.PriceName || editValid.Price != dto.Price || editValid.Quantity != dto.Quantity || 
                    //    editValid.FeeTotal != dto.FeeTotal || editValid.PaymentPercent != dto.PaymentPercent)
                    //{
                    //    return new JsonView() { Code = 400, Msg = $"尾款信息的价格、数量、费用总计、付款比例不可修改，若要修改请前往预付款修改对应信息！" };
                    //}
                }

                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,
                    PaymentType = grp_Decrease.PaymentType,
                    PaymentPercent = grp_Decrease.PaymentPercent,
                    ActualPaymentAmount = grp_Decrease.ActualPaymentAmount,
                    Currency = grp_Decrease.Currency,
                    FilePath = grp_Decrease.FilePath,
                    OTAOrderNo = grp_Decrease.OTAOrderNo,
                    Remark = grp_Decrease.Remark,
                });
                if (res)
                {
                    if (teamRate != null)
                    {
                        if (teamRate.CurrencyCode.Equals("CNY"))
                        {
                            grp_CreditCardPayment.DayRate = 1.0000M;
                            grp_CreditCardPayment.RMBPrice = grp_Decrease.ActualPaymentAmount;
                        }
                        else
                        {
                            grp_CreditCardPayment.DayRate = teamRate.Rate;
                            grp_CreditCardPayment.RMBPrice = grp_Decrease.ActualPaymentAmount * grp_CreditCardPayment.DayRate;
                        }
                    }

                    //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.ActualPaymentAmount,
                            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)
                    {
                        #region 付款类型为预付款时，添加尾款信息

                        if (editValid.ParentId == grp_Decrease.Id && grp_Decrease.PaymentType.Contains("预付款"))
                        {
                            // 检查尾款信息是否存在，存在则更新，不存在则添加
                            var balanceInfo = await _sqlSugar.Queryable<Grp_DecreasePayments>()
                                .Where(x => x.DiId == grp_Decrease.DiId 
                                            && x.ParentId == grp_Decrease.Id
                                            && x.PriceName == grp_Decrease.PriceName 
                                            && x.PaymentType.Contains("尾款") 
                                            && x.IsDel == 0)
                                .FirstAsync();

                            var balancePercent = 100m - grp_Decrease.PaymentPercent;
                            var paymentTypeName = "尾款";
                            var paymentPercent = balancePercent;
                            var actualPaymentAmount = grp_Decrease.FeeTotal - grp_Decrease.ActualPaymentAmount;

                            if (balanceInfo != null) // 更新尾款信息
                            {
                                bool balanceRes = await UpdateAsync(a => a.Id == balanceInfo.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,
                                    PaymentType = paymentTypeName,
                                    PaymentPercent = paymentPercent,
                                    ActualPaymentAmount = actualPaymentAmount,
                                    Currency = grp_Decrease.Currency,
                                    FilePath = grp_Decrease.FilePath,
                                    OTAOrderNo = grp_Decrease.OTAOrderNo,
                                    Remark = grp_Decrease.Remark,
                                });

                                if (!balanceRes)
                                {
                                    RollbackTran();
                                    return new JsonView() { Code = 200, Msg = $"{grp_Decrease.PriceName}尾款信息更新失败！" };
                                }

                                var DayRate = 0.00m;
                                var RMBPrice = 0.00m;
                                if (teamRate != null)
                                {
                                    if (teamRate.CurrencyCode.Equals("CNY"))
                                    {
                                        DayRate = 1.0000M;
                                        RMBPrice = actualPaymentAmount;
                                    }
                                    else
                                    {
                                        DayRate = teamRate.Rate;
                                        RMBPrice = actualPaymentAmount * DayRate;
                                    }
                                }

                                int balanceCTable = await _sqlSugar.Updateable<Grp_CreditCardPayment>()
                                    .Where(a => a.DIId == balanceInfo.DiId && a.CTable == 98 && a.CId == balanceInfo.Id)
                                    .SetColumns(a => new Grp_CreditCardPayment
                                    {

                                        OtherSideName = dto.OtherSideName,
                                        OtherSideNo = dto.OtherSideNo,
                                        OtherBankName = dto.OtherBankName,
                                        PayDId = dto.PayDId,
                                        IsPay = isPay,
                                        PayMoney = actualPaymentAmount,
                                        PaymentCurrency = grp_Decrease.Currency,
                                        Payee = dto.SupplierName,
                                        OrbitalPrivateTransfer = dto.OrbitalPrivateTransfer,
                                        DayRate = DayRate,
                                        RMBPrice = RMBPrice,
                                    })
                                    .ExecuteCommandAsync();

                                if (balanceCTable < 1)
                                {
                                    RollbackTran();
                                    return new JsonView() { Code = 200, Msg = $"{grp_Decrease.PriceName}尾款信息更新失败！" };
                                }

                            }
                            else // 添加尾款信息
                            {
                                grp_Decrease.ParentId = grp_Decrease.Id;
                                grp_Decrease.PaymentType = paymentTypeName;
                                grp_Decrease.PaymentPercent = paymentPercent;
                                grp_Decrease.ActualPaymentAmount = actualPaymentAmount;

                                var balanceId = await AddAsyncReturnId(grp_Decrease);
                                if (balanceId < 1)
                                {
                                    RollbackTran();
                                    return new JsonView() { Code = 200, Msg = $"{grp_Decrease.PriceName}尾款信息添加失败！" };
                                }

                                grp_CreditCardPayment.CId = balanceId;
                                grp_CreditCardPayment.PayMoney = grp_Decrease.ActualPaymentAmount;
                                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;
                                }

                                var balanceCId = await _sqlSugar.Insertable(grp_CreditCardPayment).ExecuteReturnIdentityAsync();
                                if (balanceCId < 1)
                                {
                                    RollbackTran();
                                    return new JsonView() { Code = 200, Msg = $"{grp_Decrease.PriceName}尾款信息添加失败！" };
                                }
                            }
                        }

                        // 付款类型更改为全款时，删除同名称尾款信息
                        if (grp_Decrease.PaymentType.Contains("全款"))
                        {
                            var balanceInfo = await _sqlSugar.Queryable<Grp_DecreasePayments>()
                                .Where(x => x.DiId == grp_Decrease.DiId 
                                            && x.ParentId == grp_Decrease.Id
                                            && x.PriceName == grp_Decrease.PriceName 
                                            && x.PaymentType.Contains("尾款") 
                                            && x.IsDel == 0)
                                .FirstAsync();

                            if (balanceInfo != null) {

                                bool balanceRes = await UpdateAsync(a => a.Id == balanceInfo.Id,
                                     a => new Grp_DecreasePayments
                                     {
                                         IsDel = 1,
                                         DeleteTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"),
                                         DeleteUserId = dto.CreateUserId,
                                         Remark = $"{balanceInfo.Remark};由预付款更改为全款时，自动删除：{grp_Decrease.PriceName}尾款信息"
                                     });

                                int balanceCTable = await _sqlSugar.Updateable<Grp_CreditCardPayment>()
                                   .Where(a => a.DIId == balanceInfo.DiId && a.CTable == 98 && a.CId == balanceInfo.Id)
                                   .SetColumns(a => new Grp_CreditCardPayment
                                   {
                                       IsDel = 1,
                                       DeleteTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"),
                                       DeleteUserId = dto.CreateUserId,
                                       Remark = $"{balanceInfo.Remark};由预付款更改为全款时，自动删除：{grp_Decrease.PriceName}尾款信息"
                                   })
                                   .ExecuteCommandAsync();
                            }
                        }

                        #endregion

                        #region 世运会自动审核处理
                        if (dto.DiId == 2590)
                        {
                            bool isAuto = false;
                            string[] spPriceName = dto.PriceName.Split('-').ToArray();
                            var expression = Expressionable.Create<Grp_GamesBudgetMaster>()
                                                            .And(x => x.IsDel == 0);

                            if (spPriceName.Length >= 2)
                            {
                                expression.And(x => x.T0 == spPriceName[0])
                                          .And(x => x.CalculationContent == spPriceName[1]);

                                var setting = _sqlSugar.Queryable<Grp_GamesBudgetMaster>()
                                                        .First(expression.ToExpression());

                                if (setting != null)
                                {
                                    var dbList = _sqlSugar.Queryable<Grp_DecreasePayments>()
                                                .LeftJoin<Grp_CreditCardPayment>((x, a) => x.Id == a.CId && a.IsDel == 0 && a.CTable == 98 && a.DIId == dto.DiId)
                                                .Where((x, a) => x.IsDel == 0 && x.PriceName.Contains(spPriceName[0]) && x.PriceName.Contains(spPriceName[1]) && x.Id != dto.Id)
                                                .Select((x, a) => new
                                                {
                                                    x,
                                                    a.RMBPrice,
                                                })
                                                .ToList();

                                    //币种问题 (836 CNY)
                                    if (dto.Currency != 836)
                                    {
                                        var rate = teamRates.Where(it => it.CurrencyId == grp_Decrease.Currency).FirstOrDefault();
                                        if (rate != null)
                                        {
                                            //dto.Price *= rate.Rate;
                                            dto.FeeTotal *= rate.Rate;
                                        }
                                    }

                                    //现有金额
                                    var sumPrice = dbList.Sum(x => x.RMBPrice);

                                    //现有数量
                                    //var sumCount = dbList.Sum(x => x.Quantity);

                                    var availableAmount = setting.ItemTotal - sumPrice;
                                    //var availableQuantity = setting.Quantity - sumCount;

                                    if (availableAmount > 0)  // && availableQuantity > 0
                                    {

                                        //单价计算
                                        //var settingPrice = setting.UnitPrice;
                                        //if (int.TryParse(setting.CycleUnit, out int cycleUnit))
                                        //{
                                        //    settingPrice *= cycleUnit;
                                        //}

                                        if (dto.FeeTotal <= availableAmount) //dto.Price <= settingPrice &&  && dto.Quantity <= setting.Quantity
                                        {
                                            isAuto = true;
                                        }
                                    }
                                }
                            }

                            if (isAuto)
                            {
                                _sqlSugar.Updateable<Grp_CreditCardPayment>()
                                    .SetColumns(a => new Grp_CreditCardPayment
                                    {
                                        IsAuditGM = 3, //自动审核通过
                                        AuditGMOperate = 4,
                                        AuditGMDate = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
                                    })
                                    .Where(a => a.CId == dto.Id && a.CTable == 98 && a.DIId == 2590)
                                    .ExecuteCommand();
                                recheck = false;
                            }
                        }
                        #endregion

                        #region 会务成本自动审核处理

                        var auditRes = await AutoAuditConferenceCostAsync(grp_Decrease, teamRates);
                        recheck = auditRes.isRecheck;

                        #endregion

                        CommitTran();

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

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

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

        /// <summary>
        /// 会务成本自动审核
        /// </summary>
        /// <param name="paymentInfo"></param>
        /// <param name="teamRates"></param>
        /// <returns></returns>
        private async Task<(bool isAudit, bool isRecheck)> AutoAuditConferenceCostAsync(Grp_DecreasePayments paymentInfo, List<TeamRateDescAddCurrencyIdView> teamRates)
        {
            var hwids = _sqlSugar.Queryable<Sys_SetData>()
                           .Where(x => x.IsDel == 0 && x.STid == 10 && x.Name.Contains("会务活动"))
                           .Select(x => x.Id)
                           .ToList();

            var groupTypeIsTrue = _sqlSugar.Queryable<Grp_DelegationInfo>()
                .Where(x => x.Id == paymentInfo.DiId && x.IsDel == 0)
                .Any(x => hwids.Contains(x.TeamDid));

            if (groupTypeIsTrue)
            {
                var main = _sqlSugar.Queryable<Grp_ConferenceAffairsCost>().First(x => x.Diid == paymentInfo.DiId && x.IsDel == 0);

                if (main != null)
                {
                    var chiArr = _sqlSugar.Queryable<Grp_ConferenceAffairsCostChild>()
                        .Where(x => x.Diid == paymentInfo.DiId && x.ConferenceAffairsCostId == main.Id && x.IsDel == 0)
                        .ToList();
                    var find = chiArr.FirstOrDefault(x => x.PriceName == paymentInfo.PriceName && x.ReviewStatus == 1);
                    var isAuto = false;

                    //存在已审的会务成本
                    if (find != null)
                    {
                        //检索历史金额
                        var histyPrice = _sqlSugar.Queryable<Grp_DecreasePayments>()
                            .LeftJoin<Grp_CreditCardPayment>((x, a) => a.IsDel == 0 && a.CTable == 98 && a.CId == x.Id)
                            .Where((x, a) => x.IsDel == 0 
                                            && x.DiId == paymentInfo.DiId
                                            && x.ParentId == 0
                                            && x.PriceName == paymentInfo.PriceName 
                                            && !x.PaymentType.Contains("尾款") 
                                            && x.Id != paymentInfo.Id)
                            .Select((x, a) => new
                            {
                                x.FeeTotal,
                                x.Currency,
                                a.DayRate,
                                a.RMBPrice,
                                x.DiId,
                                x.PriceName,
                                x.Price,
                                x.Quantity,
                            })
                            .ToList();

                        //币种全转换为人民币
                        var rate = teamRates.Where(it => it.CurrencyId == paymentInfo.Currency).FirstOrDefault();
                        if (rate != null)
                        {
                            paymentInfo.FeeTotal *= rate.Rate;
                        }

                        var ConferenceAffairsCostFeeTotal = 0.00M;
                        ConferenceAffairsCostFeeTotal = find.Count * find.CostPrice * find.Rate;

                        var histyPriceValue = histyPrice.Sum(x => x.FeeTotal * x.DayRate);
                        if (ConferenceAffairsCostFeeTotal > histyPriceValue)
                        {
                            isAuto = ConferenceAffairsCostFeeTotal - histyPriceValue >= paymentInfo.FeeTotal;
                        }
                    }

                    if (isAuto)
                    {
                        var execCount = _sqlSugar.Updateable<Grp_CreditCardPayment>()
                        .SetColumns(a => new Grp_CreditCardPayment
                        {
                            IsAuditGM = 3, //自动审核通过
                            AuditGMOperate = 4,
                            AuditGMDate = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
                        })
                        .Where(a => a.CId == paymentInfo.Id && a.CTable == 98 && a.DIId == paymentInfo.DiId)
                        .ExecuteCommand();

                        // 处理尾款审核
                        var balanceAuditInfo = _sqlSugar.Queryable<Grp_DecreasePayments>()
                            .Where(x => x.DiId == paymentInfo.DiId 
                                        && x.IsDel == 0 
                                        && x.ParentId == paymentInfo.ParentId
                                        && x.PriceName == paymentInfo.PriceName 
                                        && x.PaymentType.Contains("尾款"))
                            .First();
                        if (balanceAuditInfo != null)
                        {
                            var balanceAudit = _sqlSugar.Updateable<Grp_CreditCardPayment>()
                                .SetColumns(a => new Grp_CreditCardPayment
                                {
                                    IsAuditGM = 3, //自动审核通过
                                    AuditGMOperate = 4,
                                    AuditGMDate = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
                                })
                                .Where(a => a.CId == balanceAuditInfo.Id && a.CTable == 98 && a.DIId == balanceAuditInfo.DiId)
                                .ExecuteCommand();
                        }
                        return (true, false);
                    }
                }
            }

            return (false,true);
        }

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


                var 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);
                var 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);

                    var 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 = "程序错误" };
            }
        }

        /// <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.PaymentType,
											dp.PaymentPercent,
											dp.ActualPaymentAmount,
											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;
        }
    }
}