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


using AutoMapper;
using NPOI.SS.Formula.Functions;
using OASystem.Domain;
using OASystem.Domain.AesEncryption;
using OASystem.Domain.Dtos.Financial;
using OASystem.Domain.Dtos.Groups;
using OASystem.Domain.Entities.Customer;
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.Infrastructure.Repositories.System;
using OASystem.Infrastructure.Tools;
using Org.BouncyCastle.Asn1.Cms;
using System;
using System.Collections.Generic;
using System.Drawing;
using System.Linq;
using System.Security.Cryptography;
using System.Text;
using System.Threading.Tasks;

namespace OASystem.Infrastructure.Repositories.Groups
{
    public class VisaPriceRepository : BaseRepository<Grp_VisaInfo, VisaPriceDto>
    {
        private readonly IMapper _mapper;

        private readonly DelegationInfoRepository _groupRep;

        public VisaPriceRepository(SqlSugarClient sqlSugar, IMapper mapper, DelegationInfoRepository groupRep)
            : base(sqlSugar)
        {
            _mapper = mapper;
            _groupRep = groupRep;
        }


        /// <summary>
        /// 根据diid查询签证费用列表
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        public async Task<Result> PostVisaByDiId(VisaPriceDto dto)
        {
            Result result = new Result() { Code = -2, Msg = "未知错误" };
            if (dto.PortType == 0 || string.IsNullOrWhiteSpace(dto.PortType.ToString()))
            {
                return result = new Result() { Code = -1, Msg = "请传入PortType参数，请求端口分类 1 Web 2 Android 3 IOS" };
            }
            try
            {
                #region SQL条件拼接

                string sqlWhere = string.Empty;
                if (!string.IsNullOrWhiteSpace(dto.VisaClient))
                {
                    sqlWhere += string.Format(@" And v.VisaClient like '%{0}%'", dto.VisaClient);
                }

                sqlWhere += string.Format(@"And v.DIId={0} and v.isdel={1}", dto.DiID, 0);
                string UserId = "";
                List<Grp_GroupsTaskAssignment> gtaUIdList = _sqlSugar.Queryable<Grp_GroupsTaskAssignment>().Where(a => a.DIId == dto.DiID && a.IsDel == 0 && a.CTId == 80).ToList();
                foreach (Grp_GroupsTaskAssignment gta in gtaUIdList)
                    UserId += gta.UId + ",";

                if (!string.IsNullOrWhiteSpace(UserId))
                {
                    UserId = UserId.Substring(0, UserId.Length - 1);
                }
                else
                {
                    UserId = "0";
                }
                sqlWhere += string.Format(@" And v.CreateUserId in ({0})", UserId);

                if (dto.IsPaySign != -1)
                {
                    sqlWhere += string.Format(@" And c.IsPay = {0} ", dto.IsPaySign);
                }

                if (!string.IsNullOrEmpty(sqlWhere.Trim()))
                {
                    Regex r = new Regex("And");
                    sqlWhere = r.Replace(sqlWhere, "Where", 1);
                }

                #endregion
                int startIndex = (dto.PageIndex - 1) * dto.PageSize + 1;
                int endIndex = startIndex + dto.PageSize - 1;
                if (dto.PortType == 1)
                {
                    //string sql = string.Format(@"select *,(select IsAuditGM from Grp_CreditCardPayment where CTable=80 and CId=v.Id and IsDel=0) IsAuditGM,
                    //                            (select Name from Sys_SetData where isdel=0 and v.VisaCurrency=Id) VisaCurrencyStr from
                    //                            Grp_VisaInfo v {0} order by CreateTime desc",sqlWhere);

                    string sql = string.Format(@"  Select v.*, c.IsAuditGM,c.IsPay,sd.Name As 'PayName' From Grp_VisaInfo as v With(Nolock) 
left Join Grp_CreditCardPayment as c With(Nolock) On v.Id = c.CId
Left Join Sys_SetData as sd With(Nolock) On c.PayDId = sd.Id 
{0}
 And c.CTable = 80 And c.IsDel = 0   ", sqlWhere);

                    List<VisaInfoView> infoViews = _sqlSugar.SqlQueryable<VisaInfoView>(sql).ToList();
                    var parseIntValue = 0;
                    var CrmIds = (from item in infoViews
                                  where item.VisaClient.Contains(",") || int.TryParse(item.VisaClient, out parseIntValue)
                                  select item.VisaClient).ToList();
                    var strCrmIds = string.Join(',', CrmIds);
                    List<Crm_DeleClient> clietArr = new List<Crm_DeleClient>();
                    if (!string.IsNullOrWhiteSpace(strCrmIds))
                    {
                        sql = string.Format("SELECT * FROM Crm_DeleClient cdc where id in ({0}) and IsDel  = 0", strCrmIds);
                        clietArr = _sqlSugar.SqlQueryable<Crm_DeleClient>(sql).ToList();
                        foreach (var item in clietArr)
                        {
                            EncryptionProcessor.DecryptProperties(item);
                        }
                    }

                    Dictionary<int, string> dic_Currency = new Dictionary<int, string>();

                    foreach (VisaInfoView view in infoViews)
                    {
                        if (dic_Currency.ContainsKey(view.VisaCurrency))
                        {
                            view.VisaCurrencyStr = dic_Currency[view.VisaCurrency];
                        }
                        else
                        {
                            List<Sys_SetData> temp_sdList = Query<Sys_SetData>(s => s.Id == view.VisaCurrency).ToList();
                            if (temp_sdList.Count > 0)
                            {
                                view.VisaCurrencyStr = temp_sdList[0].Name;
                                dic_Currency.Add(view.VisaCurrency, temp_sdList[0].Name);

                            }
                            else
                            {
                                view.VisaCurrencyStr = "未知";
                                dic_Currency.Add(view.VisaCurrency, "未知");
                            }

                        }

                        if (view.IsAuditGM == 0) view.IsAuditGMStr = "未审核";
                        else if (view.IsAuditGM == 1) view.IsAuditGMStr = "已通过";
                        else if (view.IsAuditGM == 2) view.IsAuditGMStr = "未通过";
                        else if (view.IsAuditGM == 3) view.IsAuditGMStr = "自动审核";
                        {
                            if (view.VisaClient.Contains(","))
                            {
                                var splitArr = view.VisaClient.Split(',');
                                var name = string.Empty;
                                var i = 0;
                                while (i < splitArr.Count())
                                {
                                    if (int.TryParse(splitArr[i], out parseIntValue))
                                    {
                                        var clientObject = clietArr.Find(x => x.Id == parseIntValue);
                                        name += (clientObject?.LastName + clientObject?.FirstName);
                                    }

                                    i++;

                                    if (i < splitArr.Count())
                                    {
                                        name += " ";
                                    }
                                }

                                if (!string.IsNullOrWhiteSpace(name))
                                {
                                    view.VisaClient = name.TrimEnd();
                                }

                                continue;
                            }
                        }

                        if (int.TryParse(view.VisaClient, out parseIntValue))
                        {
                            var name = string.Empty;
                            var clientObject = clietArr.Find(x => x.Id == parseIntValue);
                            name += (clientObject?.LastName + clientObject?.FirstName);

                            if (!string.IsNullOrWhiteSpace(name))
                            {
                                view.VisaClient = name;
                            }
                            continue;
                        }

                    }
                    return result = new Result() { Code = 0, Msg = "查询成功！", Data = infoViews };
                }
                else if (dto.PortType == 2 || dto.PortType == 3)
                {
                    string sql = string.Format(@"Select * From (	
                                            Select row_number() over (order by v.Id Desc) as RowNumber,v.*,c.IsAuditGM,s.Name as VisaCurrencyStr,s1.Name As 'PayName'
                                            From Grp_VisaInfo v
                                            Left Join Grp_CreditCardPayment c On v.Id = c.CId and CTable=80 and c.isdel=0
                                            Left Join Sys_SetData s On v.VisaCurrency = s.Id 
                                            Left Join Sys_SetData s1 On c.PayDId = s1.Id {0}
                                            ) temp Where RowNumber Between {1} and {2}", sqlWhere, startIndex, endIndex);

                    List<VisaInfoView> infoViews = _sqlSugar.SqlQueryable<VisaInfoView>(sql).ToList();
                    var parseIntValue = 0;
                    var CrmIds = (from item in infoViews
                                  where item.VisaClient.Contains(",") || int.TryParse(item.VisaClient, out parseIntValue)
                                  select item.VisaClient).ToList();
                    var strCrmIds = string.Join(',', CrmIds);
                    List<Crm_DeleClient> clietArr = new List<Crm_DeleClient>();
                    if (!string.IsNullOrWhiteSpace(strCrmIds))
                    {
                        sql = string.Format("SELECT * FROM Crm_DeleClient cdc where id in ({0}) and IsDel  = 0", strCrmIds);
                        clietArr = _sqlSugar.SqlQueryable<Crm_DeleClient>(sql).ToList();
                    }
                    foreach (VisaInfoView view in infoViews)
                    {
                        if (view.IsAuditGM == 0) view.IsAuditGMStr = "未审核";
                        else if (view.IsAuditGM == 1) view.IsAuditGMStr = "已通过";
                        else if (view.IsAuditGM == 2) view.IsAuditGMStr = "未通过";
                        else if (view.IsAuditGM == 3) view.IsAuditGMStr = "自动审核";
                        {
                            if (view.VisaClient.Contains(","))
                            {
                                var splitArr = view.VisaClient.Split(',');
                                var name = string.Empty;
                                var i = 0;
                                while (i < splitArr.Count())
                                {
                                    if (int.TryParse(splitArr[i], out parseIntValue))
                                    {
                                        var clientObject = clietArr.Find(x => x.Id == parseIntValue);
                                        name += (clientObject?.LastName + clientObject?.FirstName);
                                    }

                                    i++;

                                    if (i < splitArr.Count())
                                    {
                                        name += "[*TuT*]";
                                    }
                                }

                                if (!string.IsNullOrWhiteSpace(name))
                                {
                                    view.VisaClient = name.TrimEnd();
                                }

                                continue;
                            }
                        }

                        if (int.TryParse(view.VisaClient, out parseIntValue))
                        {
                            var name = string.Empty;
                            var clientObject = clietArr.Find(x => x.Id == parseIntValue);
                            name += (clientObject?.LastName + clientObject?.FirstName);

                            if (!string.IsNullOrWhiteSpace(name))
                            {
                                view.VisaClient = name;
                            }
                            continue;
                        }

                    }
                    string CountSql = string.Format(@"Select COUNT(1) as Count From (	
                                            Select v.*,c.IsAuditGM,s.Name as VisaCurrencyStr
                                            From Grp_VisaInfo v
                                            left Join Grp_CreditCardPayment c On v.Id = c.CId and CTable=80  and c.isdel=0
                                            Left Join Sys_SetData s On v.VisaCurrency = s.Id {0}
                                            ) temp", sqlWhere);
                    DataCount dataCount = _sqlSugar.SqlQueryable<DataCount>(CountSql).First();
                    if (dataCount != null)
                    {
                        int count = dataCount.Count;
                        float totalPage = (float)count / dto.PageSize;//总页数
                        if (totalPage == 0) totalPage = 1;
                        else totalPage = (int)Math.Ceiling((double)totalPage);


                        ListViewBase<VisaInfoView> rst = new ListViewBase<VisaInfoView>();
                        rst.DataList = infoViews;
                        rst.DataCount = count;
                        rst.CurrPageIndex = dto.PageIndex;
                        rst.CurrPageSize = dto.PageSize;
                        return result = new Result() { Code = 0, Msg = "查询成功！", Data = rst };
                    }
                }
            }
            catch (Exception ex)
            {
                return result = new Result() { Code = -2, Msg = "未知错误" };
                throw;
            }
            return result;
        }
        /// <summary>
        /// 根据签证费用Id查询单条数据及c表数据
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        public async Task<Result> PostVisaById(PostVisaByIdDto dto)
        {
            var result = new Result() { Code = -2, Msg = "未知错误" };

            Grp_VisaInfo _VisaInfo = _sqlSugar.Queryable<Grp_VisaInfo>().First(a => a.Id == dto.Id && a.IsDel == 0);
            Grp_CreditCardPayment _CreditCardPayment = _sqlSugar.Queryable<Grp_CreditCardPayment>().First(a => a.CId == dto.Id && a.IsDel == 0 && a.CTable == 80);
            var data = new
            {
                VisaInfo = _VisaInfo,
                CreditCardPayment = _CreditCardPayment
            };
            return result = new Result() { Code = 0, Msg = "暂无数据！", Data = data };

        }
        /// <summary>
        /// 签证费用录入操作（Status:1.新增,2.修改）
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        public async Task<Result> OpVisaPrice(OpVisaPriceDto dto)
        {
            Result result = new Result() { Code = -2, Msg = "未知错误" };
            BeginTran();
            int id = 0;
            Grp_VisaInfo grp_Visa1 = _mapper.Map<Grp_VisaInfo>(dto);
            grp_Visa1.Area = dto.Area;
            Grp_CreditCardPayment c = _mapper.Map<Grp_CreditCardPayment>(dto);
            c.PayMoney = dto.VisaPrice;
            c.PaymentCurrency = dto.VisaCurrency;
            c.Remark = dto.CRemark;
            c.PayPercentage = 100;
            c.CTable = 80;
            c.CId = dto.Id;
            c.IsAuditGM = 0;

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

            c.RMBPrice = c.PayMoney;
            c.DayRate = 1.0000M;

            // 按汇率计算人名币费用
            var _TeamRate = _sqlSugar.Queryable<Grp_TeamRate>().First(a => a.DiId == dto.DiId && a.IsDel == 0 && a.CTable == 80);
            var currencyInfos = new List<CurrencyInfo>();
            if (_TeamRate != null)
            {
                Sys_SetData _SetData = _sqlSugar.Queryable<Sys_SetData>().First(a => a.IsDel == 0 && a.Id == c.PaymentCurrency);
                if (_SetData != null)
                {
                    currencyInfos = CommonFun.GetCurrencyChinaToList(_TeamRate.Remark);
                    CurrencyInfo CurrencyRate = currencyInfos.FirstOrDefault(a => a.CurrencyCode == _SetData.Name);
                    if (CurrencyRate != null)
                    {
                        c.RMBPrice = c.PayMoney * Convert.ToDecimal(CurrencyRate.Rate);
                        c.DayRate = CurrencyRate.Rate;
                    }
                }
            }

            //todo：费用标准需更改为 Res_VisaFeeStandardDetails
            #region 自动审核处理 
            var isAutoAudit = false;
            if (dto.IsThird == 0)
            {
                var area = dto.Area;
                var feeType = 0; // 0 因公 1 因私
                if (area.Contains("(因公)"))
                {
                    feeType = 0;
                    area = area.Replace("(因公)", "");
                }
                else if (area.Contains("(因私)"))
                {
                    feeType = 1;
                    area = area.Replace("(因私)", "");
                }

                var visaFeeInfo = _sqlSugar.Queryable<Res_VisaFeeStandard>()
                    .Where(x => x.IsDel == 0 && x.FeeType == feeType && x.Country.Equals(area))
                    .First();
                if (visaFeeInfo != null) 
                {
                    //因公默认四川省
                    int provinceId = 122;
                    var groupNames = await _groupRep.EnterExitCostGroupNameInfoAsync(dto.DiId);
                    if (groupNames != null)
                    {
                        provinceId = groupNames.ProvinceId;
                    }

                    var visaDetails = _sqlSugar.Queryable<Res_VisaFeeStandardDetails>()
                        .Where(x => x.IsDel == 0 && x.ParentId == visaFeeInfo.Id && x.ProvinceId == provinceId)
                        .First();

                    decimal visaFeePrice = 0.00M;

                    if (dto.AgencyFeeType == 1)
                    {
                        if (!visaDetails.IsVisaExemptionSmall) visaFeePrice += visaDetails.SmallVisaPrice;
                        visaFeePrice += visaDetails.SmallAgencyFee;
                    }
                    else if (dto.AgencyFeeType == 2)
                    {
                        if (!visaDetails.IsVisaExemptionLarge) visaFeePrice += visaDetails.LargeVisaPrice;
                        visaFeePrice += visaDetails.LargeAgencyFee;

                    }

                    var visaFeeTotalPrice = visaFeePrice * dto.VisaNumber;
                    if (c.RMBPrice <= visaFeeTotalPrice)
                    {
                        isAutoAudit = true;
                        c.IsAuditGM = 3;
                        c.AuditGMOperate = 4; //系统管理员
                        c.AuditGMDate = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
                    }
                }
            }

            #endregion

            if (dto.Status == 1)//添加
            {
                var grp_Visa = _sqlSugar.Queryable<Grp_VisaInfo>()
                    .First(a => a.IsDel == 0 &&
                                a.DIId == dto.DiId &&
                                a.VisaDescription.Equals(dto.VisaDescription) &&
                                a.VisaCurrency == dto.VisaCurrency &&
                                a.VisaPrice == dto.VisaPrice &&
                                a.VisaClient == dto.VisaClient
                    );
                if (grp_Visa != null)
                {
                    return result = new Result() { Code = -1, Msg = "该笔费用已存在，请勿重复添加！" };
                }
                else
                {
                    id = await AddAsyncReturnId(grp_Visa1);
                    if (id != 0)
                    {
                        c.CId = id;
                        int cId = await _sqlSugar.Insertable(c).ExecuteReturnIdentityAsync();
                        if (cId != 0)
                        {
                            var data = new { ccpId = cId, sign = 1 };
                            result = new Result() { Code = 0, Msg = "添加成功！", Data = data };

                        }
                        else
                        {
                            RollbackTran();
                            result = new Result() { Code = -1, Msg = "添加失败！" };
                        }
                    }
                    else
                    {
                        RollbackTran();
                        result = new Result() { Code = -1, Msg = "添加失败，请稍后重试！" };
                    }
                }
            }
            else if (dto.Status == 2)//修改
            {
                bool res = await UpdateAsync(a => a.Id == dto.Id, a => new Grp_VisaInfo
                {
                    VisaClient = grp_Visa1.VisaClient,
                    Area = grp_Visa1.Area,
                    AgencyFeeType = grp_Visa1.AgencyFeeType,
                    VisaPrice = grp_Visa1.VisaPrice,
                    VisaCurrency = grp_Visa1.VisaCurrency,
                    IsThird = grp_Visa1.IsThird,
                    PassengerType = grp_Visa1.PassengerType,
                    VisaNumber = grp_Visa1.VisaNumber,
                    VisaFreeNumber = grp_Visa1.VisaFreeNumber,
                    Remark = dto.Remark,
                    VisaDescription = dto.VisaDescription
                });
                if (res)
                {
                    int CTable = await _sqlSugar.Updateable<Grp_CreditCardPayment>()
                        .Where(a => a.CId == grp_Visa1.Id && a.CTable == 80)
                        .SetColumns(a => new Grp_CreditCardPayment
                        {
                            PayDId = dto.PayDId,
                            IsPay = c.IsPay,
                            PayMoney = c.PayMoney,
                            PaymentCurrency = c.PaymentCurrency,
                            Payee = c.Payee,
                            OrbitalPrivateTransfer = c.OrbitalPrivateTransfer,
                            DayRate = c.DayRate,
                            RMBPrice = c.RMBPrice,
                            ConsumptionPatterns = c.ConsumptionPatterns,
                            ConsumptionDate = c.ConsumptionDate,
                            CTDId = c.CTDId,
                            CompanyBankNo = c.CompanyBankNo,
                            OtherBankName = c.OtherBankName,
                            OtherSideNo = c.OtherSideNo,
                            OtherSideName = c.OtherSideName,
                            BankNo = c.BankNo,
                            CardholderName = c.CardholderName,
                            Remark = c.Remark,
                        })
                        .SetColumnsIF(isAutoAudit, x => x.IsAuditGM == 3)
                        .SetColumnsIF(isAutoAudit, x => x.AuditGMOperate == 4)
                        .SetColumnsIF(isAutoAudit, x => x.AuditGMDate == DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"))
                        .ExecuteCommandAsync();

                    var ccp = Query<Grp_CreditCardPayment>(a => a.CId == grp_Visa1.Id && a.CTable == 80).First();

                    var data = new { ccpId = ccp.Id, sign = 2 };
                    result = new Result() { Code = 0, Msg = "修改成功！", Data = data };
                }
                else
                {
                    RollbackTran();
                    result = new Result() { Code = -1, Msg = "修改失败，请稍后重试！" };
                }
            }
            CommitTran();

            return result;
        }
    }
}