OA2023/OASystem/OASystem.Infrastructure/Repositories/Financial/PaymentRefundAndOtherMoneyRepository.cs

using AutoMapper;
using Newtonsoft.Json;
using NPOI.SS.Formula.Functions;
using OASystem.Domain;
using OASystem.Domain.Dtos;
using OASystem.Domain.Dtos.Financial;
using OASystem.Domain.Dtos.UserDto;
using OASystem.Domain.Entities.Financial;
using OASystem.Domain.Entities.Groups;
using OASystem.Domain.ViewModels.Financial;
using OASystem.Infrastructure.Repositories.Groups;
using OASystem.Infrastructure.Repositories.System;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Security.Cryptography;
using System.Text;
using System.Threading.Tasks;

namespace OASystem.Infrastructure.Repositories.Financial
{
    /// <summary>
    /// 财务 - 收款退还与其他款项
    /// </summary>
    public class PaymentRefundAndOtherMoneyRepository:BaseRepository<Fin_PaymentRefundAndOtherMoney, Fin_PaymentRefundAndOtherMoneyView>
    {
        private readonly IMapper _mapper;
        private readonly Result _result;
        private readonly SetDataRepository _setDataRep;
        private readonly TeamRateRepository _teamRateRep;

        /// <summary>
        /// 初始化
        /// </summary>
        /// <param name="sqlSugar"></param>
        /// <param name="mapper"></param>
        public PaymentRefundAndOtherMoneyRepository(SqlSugarClient sqlSugar, IMapper mapper, SetDataRepository setDataRep, TeamRateRepository teamRateRep)
            : base(sqlSugar)
        {
            _mapper = mapper;
            _result = new Result() { Code = -2 };
            _setDataRep = setDataRep;
            _teamRateRep = teamRateRep;
        }

        /// <summary>
        /// 根据团组ID 查询
        /// </summary>
        /// <param name="diId"></param>
        /// <returns></returns>
        public async Task<Result> _ItemByDiId(int diId)
        {
            string sql = string.Format(@"Select prom.Id,prom.PriceName,prom.Price,sd.Name As CurrencyCode,
					                     prom.Remark,u.CnName As CreateUserName,prom.CreateTime,ccp.IsAuditGM,
										 u1.CnName As AuditGMName,ccp.AuditGMDate
				                         From Fin_PaymentRefundAndOtherMoney prom
					                     Left Join Grp_CreditCardPayment ccp On ccp.CTable = 285 And 
										           ccp.CId = prom.Id And ccp.DIId = prom.DiId
				                         Left Join Sys_SetData sd On prom.CurrencyId = sd.Id
				                         Left Join Sys_Users u On u.Id = prom.CreateUserId 
				                         Left Join Sys_Users u1 On u1.Id = ccp.AuditGMOperate 
				                         Where prom.IsDel = 0 And ccp.IsDel = 0 And prom.DiId = {0}", diId);

            var data = await _sqlSugar.SqlQueryable<Fin_PaymentRefundAndOtherMoneyItemView>(sql).ToListAsync();

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

            return _result;

        }

        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="diId"></param>
        /// <returns></returns>
        public async Task<Result> _Del(PaymentRefundAndOtherMoneyDelDto dto)
        {
            Fin_PaymentRefundAndOtherMoney _PaymentRefundAndOtherMoney = new Fin_PaymentRefundAndOtherMoney() { 
                Id = dto.Id,
                DeleteUserId = dto.UserId,
                DeleteTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"),
                IsDel = 1
            };

            _sqlSugar.BeginTran();
            var prom_del = await _sqlSugar.Updateable( _PaymentRefundAndOtherMoney )
                                     .UpdateColumns(it => new { it.DeleteUserId,it.DeleteTime,it.IsDel })
                                     .WhereColumns(it => new { it.Id})
                                     .ExecuteCommandAsync();


            Fin_PaymentRefundAndOtherMoney _PaymentRefundAndOtherMoneyInfo = new Fin_PaymentRefundAndOtherMoney();
            _PaymentRefundAndOtherMoneyInfo = await _sqlSugar.Queryable<Fin_PaymentRefundAndOtherMoney>().Where(it => it.Id == dto.Id).FirstAsync();

            if (_PaymentRefundAndOtherMoneyInfo != null)
            {
                Grp_CreditCardPayment _CreditCardPayment = new Grp_CreditCardPayment() {
                    CId = _PaymentRefundAndOtherMoneyInfo.Id,
                    DIId = _PaymentRefundAndOtherMoneyInfo.DiId,
                    DeleteUserId = dto.UserId,
                    DeleteTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"),
                    IsDel = 1
                };


                var ccp_del = await _sqlSugar.Updateable(_CreditCardPayment)
                                     .UpdateColumns(it => new { it.DeleteUserId, it.DeleteTime, it.IsDel })
                                     .WhereColumns(it => new { it.CId,it.DIId })
                                     .ExecuteCommandAsync();
            }

            if (prom_del > 0 )
            {
                _result.Code = 0;
                _result.Msg = "操作成功!";
            }
            else
            {
                _result.Msg = "操作失败!";
            }

            _sqlSugar.CommitTran();
            return _result;
        }
        
        /// <summary>
        /// 详情 数据源
        /// </summary>
        /// <param name="diId"></param>
        /// <returns></returns>
        public async Task<Result> _InfoDataSource(PortDtoBase dto)
        {
            if (dto.PortType == 1 || dto.PortType == 2 || dto.PortType == 3)  //1 Web 2 Android 3 Ios
            {

                var  setDatas = _sqlSugar.Queryable<Sys_SetData>().Where(it => it.IsDel == 0).ToList();
                dynamic _currencyData = null, _payTypeData = null;
                _currencyData = setDatas.Where(it => it.STid == 66).Select(it => new { it.Id, it.Name, it.Remark }); //币种类型
                _payTypeData = setDatas.Where(it => it.STid == 14).Select(it => new { it.Id, it.Name, it.Remark }); //支付类型

                List<dynamic> _priceTypeDatas = new List<dynamic>();
                _priceTypeDatas.Add(new { Id = 0, Name = "其他", Remark = "" });
                _priceTypeDatas.Add(new { Id = 1, Name = "退多付款", Remark = "" });

                List<dynamic> _orbitalPrivateTransferDatas = new List<dynamic>();
                _orbitalPrivateTransferDatas.Add(new { Id = 0, Name = "公转", Remark = "" });
                _orbitalPrivateTransferDatas.Add(new { Id = 1, Name = "私转", Remark = "" });

                dynamic _InfoDataSource = new
                {
                    CurrencyDatas = _currencyData,
                    PayTypeDatas = _payTypeData,
                    OrbitalPrivateTransferDatas = _orbitalPrivateTransferDatas,
                    PriceTypeDatas = _priceTypeDatas
                };

                _result.Data = _InfoDataSource;
                _result.Code = 0;
                _result.Msg = "查询成功!";
            }
            else
            {
                _result.Msg = "请输入正确的端口号! 1 Web 2 Android 3 Ios;";
            }

            return _result;
        }

        /// <summary>
        /// 详情
        /// </summary>
        /// <param name="diId"></param>
        /// <returns></returns>
        public async Task<JsonView> _Info(PaymentRefundAndOtherMoneyInfoDto dto)
        {
            var _view = new JsonView() { Code = StatusCodes.Status204NoContent };
           
            if (dto.PortType == 1 || dto.PortType == 2 || dto.PortType == 3)  //1 Web 2 Android 3 Ios
            {
                var data = await _sqlSugar.Queryable<Fin_PaymentRefundAndOtherMoney>()
                                          .LeftJoin<Grp_CreditCardPayment>((prom, ccp) => prom.Id == ccp.CId && ccp.CTable == 285 && ccp.IsDel == 0)
                                          .Where((prom, ccp) => prom.IsDel == 0 && ccp.DIId == prom.DiId && prom.Id == dto.Id)
                                          .Select((prom, ccp) => new Fin_PaymentRefundAndOtherMoneyInfoView()
                                          {
                                              Id = prom.Id,
                                              DiId = prom.DiId,
                                              CcpId = ccp.Id,
                                              PriceName = prom.PriceName,
                                              Price = prom.Price,
                                              CurrencyId = prom.CurrencyId,
                                              Payee = ccp.Payee,
                                              PayDId = ccp.PayDId,
                                              OrbitalPrivateTransfer = ccp.OrbitalPrivateTransfer,
                                              ConsumptionPatterns = ccp.ConsumptionPatterns,
                                              PayType = prom.PayType,
                                              Remark = prom.Remark,
                                              OtherBankName = ccp.OtherBankName,
                                              OtherSideNo = ccp.OtherSideNo,
                                          })
                                          .FirstAsync();

                if (data != null)
                {
                    _view.Data = data;
                    _view.Code = 200;
                    _view.Msg = "查询成功!";
                }
                else _result.Msg = "查询失败!";

            }
            else _view.Msg = "请输入正确的端口号! 1 Web 2 Android 3 Ios;";
            return _view;
        }

        /// <summary>
        /// 操作(Add Or Edit)
        /// </summary>
        /// <param name="diId"></param>
        /// <returns></returns>
        public async Task<JsonView> _AddOrEdit(PaymentRefundAndOtherMoneyAddOrEditDto dto)
        {
            var _view = new JsonView() { Code = StatusCodes.Status204NoContent };
            if (dto.PortType == 1 || dto.PortType == 2 || dto.PortType == 3)  //1 Web 2 Android 3 Ios
            {
                #region 参数处理
                Fin_PaymentRefundAndOtherMoney _PaymentRefundAndOtherMoney = new Fin_PaymentRefundAndOtherMoney() { 
                    //Id = dto.Id,
                    DiId = dto.DiId,
                    PriceName = dto.PriceName.Trim(),
                    Price = dto.Price,
                    CurrencyId = dto.CurrencyId,
                    PayType = dto.PayType,
                    //PriceType = dto.PriceType,
                    CreateUserId = dto.UserId,
                    Remark = dto.Remark 
                };

                //处理团组汇率
                decimal dayRate = 0.00M;
                decimal CNY_Price = 0.00M;
                decimal payThenMoney = 0.00M;
                #region 收款退还 团组汇率 验证 暂时注释
                if (dto.CurrencyId == 836) //人民币币种Id
                {
                    dayRate = 1.0000M;
                    CNY_Price = dto.Price;
                    payThenMoney = dto.Price;
                }
                //else //其他币种Id
                //{
                //    List<TeamRateModelView> teamReteDatas = await _teamRateRep.PostGroupRateInfoByDiId(dto.DiId);
                //    if (teamReteDatas.Count <= 0)
                //    {
                //        _result.Msg = "该团未设置团组汇率,请先设置!";
                //        return _result;
                //    }

                //    var teamReteData1 = teamReteDatas.Where(it => it.CTableId == 98).FirstOrDefault();
                //    if (teamReteData1 == null)
                //    {
                //        _result.Msg = "该团下的“其他款项”未设置团组汇率,请先设置!";
                //        return _result;
                //    }

                //    var teamReteData2 = teamReteData1.TeamRates.Where(it => it.CurrencyCode == dto.CurrencyCode).FirstOrDefault();
                //    if (teamReteData2 == null)
                //    {
                //        _result.Msg = $"该团下的“其他款项”币种 “{dto.CurrencyCode}”未设置团组汇率,请先设置!";
                //        return _result;
                //    }
                //    dayRate = teamReteData2.Rate;
                //    CNY_Price = dayRate * dayRate;
                //    payThenMoney = dayRate * dayRate;
                //}

                #endregion

                int ispay = 0;
                if (dto.PayDId == 72) ispay = 1;

                Grp_CreditCardPayment _CreditCardPayment = new Grp_CreditCardPayment() {
                    //Id = dto.CcpId,
                    //CId = dto.CcpId,
                    DIId = dto.DiId,
                    CTable = 285,// 285 
                    PayDId = dto.PayDId,
                    ConsumptionPatterns = dto.ConsumptionPatterns,
                    ConsumptionDate = string.Empty,
                    CTDId = 0,
                    BankNo = string.Empty,
                    CardholderName = string.Empty,
                    PayMoney = dto.Price,
                    PaymentCurrency = dto.CurrencyId,
                    DayRate = dayRate,
                    CompanyBankNo = string.Empty,
                    OtherBankName = dto.OtherBankName,
                    OtherSideNo = dto.OtherSideNo,
                    OtherSideName = string.Empty,
                    MFOperator = 0,
                    MFOperatorDate = string.Empty,
                    IsAuditDM = 0,
                    AuditDMOperate = 0,
                    AuditDMDate = string.Empty,
                    IsAuditMF = 0,
                    AuditMFOperate = 0,
                    AuditMFDate = string.Empty,
                    IsAuditGM = 0,
                    AuditGMOperate = 0,
                    AuditGMDate = string.Empty,
                    IsPay = ispay,
                    PayPercentage = 100.00M,
                    PayThenMoney = payThenMoney,
                    PayPercentageOld = 0.00M,
                    PayThenMoneyOld = 0.00M,
                    UpdateDate = string.Empty,
                    Payee = dto.Payee,
                    RMBPrice = CNY_Price,
                    OrbitalPrivateTransfer = dto.OrbitalPrivateTransfer,
                    ExceedBudget = 0.00M,
                    CreateUserId = dto.UserId,
                    DeleteUserId = null,
                    DeleteTime = string.Empty,
                    Remark = string.Empty,
                    IsDel = 0
                };

                #endregion

                if (dto.Status == 1) //添加
                {
                    #region 添加数据验证

                    string sql = string.Format(@"
SELECT
  prom.Id,
  ccp.Id CcpId,
  prom.DiId,
  prom.PriceName,
  prom.Price,
  prom.CurrencyId,
  ccp.Payee,
  ccp.PayDId,
  ccp.OrbitalPrivateTransfer,
  ccp.ConsumptionPatterns,
  prom.PayType,
  prom.Remark
FROM
  Fin_PaymentRefundAndOtherMoney prom
  LEFT JOIN Grp_CreditCardPayment ccp ON ccp.CId = prom.id
  AND ccp.CTable = 285
WHERE
  prom.IsDel = 0
  AND ccp.IsDel = 0
  AND ccp.DIId = prom.DiId
  AND ccp.DIId = {0}
  AND prom.PriceName = '{1}'", _PaymentRefundAndOtherMoney.DiId,_PaymentRefundAndOtherMoney.PriceName);
                    //var data = await _sqlSugar.SqlQueryable<Fin_PaymentRefundAndOtherMoneyInfoView>(sql).FirstAsync();

                    //if (data != null)
                    //{
                    //    _view.Msg = "费用名称已存在,请勿重新添加!";
                    //    return _view;
                    //}
                   
                    #endregion

                    _sqlSugar.BeginTran();

                    var addReturnId = await _sqlSugar.Insertable(_PaymentRefundAndOtherMoney).ExecuteReturnIdentityAsync();

                    if (addReturnId <= 0)
                    {
                        _view.Msg = "操作失败!收款退还与其他款项添加失败!";
                        _sqlSugar.RollbackTran();
                        return _view;
                    }
                    _CreditCardPayment.CId = addReturnId;
                    var addStatus = await _sqlSugar.Insertable(_CreditCardPayment).ExecuteReturnIdentityAsync();
                    if (addStatus <= 0)
                    {
                        _view.Msg = "操作失败!付款类型添加失败!";
                        _sqlSugar.RollbackTran();
                        return _view;
                    }

                    _sqlSugar.CommitTran();
                    _view.Msg = "操作成功!";
                    _view.Code = 200;
                }
                else if (dto.Status == 2) //修改
                {
                    _PaymentRefundAndOtherMoney.Id = dto.Id;
                    _CreditCardPayment.Id = dto.CcpId;
                    _CreditCardPayment.CId = dto.Id;

                    _sqlSugar.BeginTran();

                    var prom_update = await _sqlSugar.Updateable(_PaymentRefundAndOtherMoney)
                                                     .IgnoreColumns(it => new { it.CreateUserId, it.CreateTime, it.DeleteUserId, it.DeleteTime, it.IsDel })
                                                     .WhereColumns(it => new { it.Id })
                                                     .ExecuteCommandAsync();

                    if (prom_update <= 0)
                    {
                        _view.Msg = "操作失败!收款退还与其他款项修改失败!";
                        _sqlSugar.RollbackTran();
                        return _view;
                    }

                    var ccp_update = await _sqlSugar.Updateable(_CreditCardPayment)
                                                   .UpdateColumns(it => new
                                                   {
                                                       it.PayDId,
                                                       it.IsPay,
                                                       it.ConsumptionPatterns,
                                                       it.PayMoney,
                                                       it.PaymentCurrency,
                                                       it.DayRate,
                                                       it.PayThenMoney,
                                                       it.Payee,
                                                       it.OrbitalPrivateTransfer,
                                                       it.OtherSideNo,
                                                       it.OtherBankName
                                                       
                                                   })
                                                   .WhereColumns(it => new { it.Id })
                                                   .ExecuteCommandAsync();
                    if (ccp_update <= 0)
                    {
                        _view.Msg = "操作失败!付款信息修改失败!";
                        _sqlSugar.RollbackTran();
                        return _view;
                    }

                    _sqlSugar.CommitTran();
                    _view.Msg = "操作成功!";
                    _view.Code = 200;
                }
                else _view.Msg = "请输入正确的操作状态! 1 添加 2 修改;";
            }
            else _view.Msg = "请输入正确的端口号! 1 Web 2 Android 3 Ios;";

            return _view;
        }
    }
}