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


using AutoMapper;
using NPOI.SS.Formula.Functions;
using OASystem.Domain;
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.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;

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


        /// <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
From Grp_VisaInfo as v With(Nolock) left Join Grp_CreditCardPayment as c With(Nolock) On v.Id = c.CId
 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();
                    }

                    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
                                            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 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)
        {
            Result result = new Result() { Code = -2, Msg = "未知错误" };
            try
            {
                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 };
            }
            catch (Exception ex)
            {
                return result = new Result() { Code = -2, Msg = "未知错误" };
            }
        }
        /// <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();
            try
            {
                int id = 0;
                Grp_VisaInfo grp_Visa1 = _mapper.Map<Grp_VisaInfo>(dto);
                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;
                if (c.PayDId == 72)
                {
                    c.IsPay = 1;
                }
                c.RMBPrice = c.PayMoney;
                c.DayRate = 1;
                Grp_TeamRate _TeamRate = _sqlSugar.Queryable<Grp_TeamRate>().First(a => a.DiId == dto.DiId && a.IsDel == 0 && a.CTable == 80);
                List<CurrencyInfo> 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;
                        }
                    }
                }

                if (dto.Status == 1)//添加
                {
                    Grp_VisaInfo grp_Visa = _sqlSugar.Queryable<Grp_VisaInfo>().First(a => a.IsDel == 0 && 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)
                            {
                                result = new Result() { Code = 0, Msg = "添加成功！" };

                            }
                            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,
                        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,
                            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,

                        }).ExecuteCommandAsync();

                        result = new Result() { Code = 0, Msg = "修改成功！" };
                    }
                    else
                    {
                        RollbackTran();
                        result = new Result() { Code = -1, Msg = "修改失败，请稍后重试！" };
                    }
                }
                CommitTran();
            }
            catch (Exception ex)
            {
                result = new Result() { Code = -2, Msg = ex.Message };
            }
            return result;
        }
    }
}