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

using AutoMapper;
using NPOI.POIFS.Crypt.Dsig;
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.ViewModels.Financial;
using OASystem.Domain.ViewModels.Groups;
using OASystem.Domain.ViewModels.QiYeWeChat;
using OASystem.Infrastructure.Tools;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace OASystem.Infrastructure.Repositories.Groups
{
    public class CustomersRepository : BaseRepository<Grp_Customers, Grp_Ommission>
    {
        private readonly IMapper _mapper;
        private readonly TeamRateRepository _teamRateRep;

        public CustomersRepository(SqlSugarClient sqlSugar, IMapper mapper, TeamRateRepository teamRateRep)
            : base(sqlSugar)
        {
            _mapper = mapper;
            _teamRateRep = teamRateRep;
        }
        /// <summary>
        /// 根据团组Id查询保险费用列表
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        public async Task<Result> CustomersByDiId(CustomersByDiIdDto dto)
        {
            Result result = new Result() { Code = -2, Msg = "未知错误" };
           
            string UserId = "";
            List<Grp_GroupsTaskAssignment> gtaUIdList = _sqlSugar.Queryable<Grp_GroupsTaskAssignment>().Where(a => a.DIId == dto.DiId && a.IsDel == 0 && a.CTId == 82).ToList();
            foreach (Grp_GroupsTaskAssignment gta in gtaUIdList)
                UserId += gta.UId + ",";

            if (!string.IsNullOrWhiteSpace(UserId))
            {
                UserId = UserId.Substring(0, UserId.Length - 1);
            }
            else
            {
                UserId = "0";
            }
            string sqlWhere = string.Format(@"Where h.DiId={0} and h.IsDel={1} And h.CreateUserId in ({2})", dto.DiId, 0, UserId);

            int startIndex = (dto.PageIndex - 1) * dto.PageSize + 1;
            int endIndex = startIndex + dto.PageSize - 1;
            if (dto.PortType == 1)
            {
                string sql = string.Format(@"select h.Id,h.ClientName,InsuranceCosts,Currency,s.Name as CurrencyStr,Attachment,c.OrbitalPrivateTransfer,
                                                    u.CnName,c.IsAuditGM,s1.Name As 'PayName', CASE c.IsPay  when 0 then '未付款' when 1 then '已付款' ELSE '未付款' END as 'IsPayStr'
                                                From Grp_Customers h 
                                                Join Grp_CreditCardPayment c on h.Id=c.CId and c.CTable=82 and c.isdel=0
                                                Left Join Sys_SetData s on  h.Currency=s.Id
                                                Left Join Sys_Users u on u.Id=h.CreateUserId 
                                                Left Join Sys_SetData s1 on c.PayDId = s1.Id {0}
                                                Order by c.IsAuditGM,c.PayPercentage,h.CreateTime desc", sqlWhere);
                List<CustomersView> Customers = _sqlSugar.SqlQueryable<CustomersView>(sql).ToList();

                var parseIntValue = 0;
                var CrmIds = (from item in Customers
                                where item.ClientName.Contains(",") || int.TryParse(item.ClientName, out parseIntValue)
                                select item.ClientName).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);
                    }
                }

                foreach (var item in Customers)
                {
                    if (item.IsAuditGM == 0) item.IsAuditGMStr = "未审核";
                    else if (item.IsAuditGM == 1) item.IsAuditGMStr = "已通过";
                    else if (item.IsAuditGM == 2) item.IsAuditGMStr = "未通过";
                    else if (item.IsAuditGM == 3) item.IsAuditGMStr = "自动审核";

                    if (item.OrbitalPrivateTransfer == 0) item.OrbitalPrivateTransferStr = "公转";
                    else if (item.OrbitalPrivateTransfer == 1) item.OrbitalPrivateTransferStr = "私转";

                    if (item.ClientName.Contains(","))
                    {
                        var splitArr = item.ClientName.Split(',');
                        var name = string.Empty;
                        foreach (var client in splitArr)
                        {
                            if (int.TryParse(client, out parseIntValue))
                            {
                                var clientObject = clietArr.Find(x => x.Id == parseIntValue);
                                name += (clientObject?.LastName + clientObject?.FirstName + " ");
                            }
                        }

                        if (!string.IsNullOrWhiteSpace(name))
                        {
                            item.ClientName = name;
                        }
                        continue;
                    }
                    if (int.TryParse(item.ClientName, out parseIntValue))
                    {
                        var name = string.Empty;
                        var clientObject = clietArr.Find(x => x.Id == parseIntValue);
                        EncryptionProcessor.DecryptProperties(clientObject);
                        name += (clientObject?.LastName + clientObject?.FirstName);

                        if (!string.IsNullOrWhiteSpace(name))
                        {
                            item.ClientName = name;
                        }
                        continue;
                    }
                }

                if (!string.IsNullOrWhiteSpace(dto.ClientName))
                {
                    Customers = Customers.Where(x => x.ClientName.Contains(dto.ClientName)).ToList();
                }

                return result = new Result() { Code = 0, Msg = "查询成功！", Data = Customers };
            }
            else if (dto.PortType == 2 || dto.PortType == 3)
            {
                string sql = string.Format(@"Select * From (	
                                            Select row_number() over (order by c.IsAuditGM,c.PayPercentage,h.CreateTime desc) as RowNumber,h.Id,h.ClientName,InsuranceCosts,
                                            Currency,s.Name as CurrencyStr,Attachment,c.OrbitalPrivateTransfer,u.CnName,c.IsAuditGM,s1.Name As 'PayName'
                                            From Grp_Customers h 
                                            Join Grp_CreditCardPayment c on h.Id=c.CId and c.CTable=82 and c.isdel=0
                                            Left Join Sys_SetData s on  h.Currency=s.Id  
                                            Left Join Sys_Users u on u.Id=h.CreateUserId  
                                            Left Join Sys_SetData s1 on c.PayDId = s1.Id {0}
                                            ) temp Where RowNumber Between {1} and {2}", sqlWhere, startIndex, endIndex);

                List<CustomersView> Customers = _sqlSugar.SqlQueryable<CustomersView>(sql).ToList();

                var parseIntValue = 0;
                var CrmIds = (from item in Customers
                                where item.ClientName.Contains(",") || int.TryParse(item.ClientName, out parseIntValue)
                                select item.ClientName).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);
                    }
                }

                foreach (var item in Customers)
                {
                    if (item.IsAuditGM == 0) item.IsAuditGMStr = "未审核";
                    else if (item.IsAuditGM == 1) item.IsAuditGMStr = "已通过";
                    else if (item.IsAuditGM == 2) item.IsAuditGMStr = "未通过";
                    else if (item.IsAuditGM == 3) item.IsAuditGMStr = "自动审核";

                    if (item.OrbitalPrivateTransfer == 0) item.OrbitalPrivateTransferStr = "公转";
                    else if (item.OrbitalPrivateTransfer == 1) item.OrbitalPrivateTransferStr = "私转";


                    if (item.ClientName.Contains(","))
                    {
                        var splitArr = item.ClientName.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))
                        {
                            item.ClientName = name.TrimEnd();
                        }

                        continue;
                    }

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

                        if (!string.IsNullOrWhiteSpace(name))
                        {
                            item.ClientName = name;
                        }
                        continue;
                    }

                }
                string CountSql = string.Format(@"Select COUNT(1) as Count From (	
                                                select h.Id,h.ClientName,InsuranceCosts,Currency,s.Name as CurrencyStr,Attachment,c.OrbitalPrivateTransfer,u.CnName,c.IsAuditGM
                                                From Grp_Customers h 
                                                Join Grp_CreditCardPayment c on h.Id=c.CId and c.CTable=82 and c.isdel=0
                                                left Join Sys_SetData s on  h.Currency=s.Id  
                                                left Join Sys_Users u on u.Id=h.CreateUserId   {0}
                                                ) temp", sqlWhere);
                DataCount dataCount = _sqlSugar.SqlQueryable<DataCount>(CountSql).First();

                int count = dataCount.Count;
                float totalPage = (float)count / dto.PageSize;//总页数
                if (totalPage == 0) totalPage = 1;
                else totalPage = (int)Math.Ceiling((double)totalPage);

                ListViewBase<CustomersView> rst = new ListViewBase<CustomersView>();
                rst.DataList = Customers;
                rst.DataCount = count;
                rst.CurrPageIndex = dto.PageIndex;
                rst.CurrPageSize = dto.PageSize;
                return result = new Result() { Code = 0, Msg = "查询成功！", Data = rst };

            }
            else
            {
                return result = new Result() { Code = -2, Msg = "请传入PortType参数,1 Web 2 Android 3 IOS" };
            }
           
        }

        public async Task<Result> CustomersById(CustomersByIdDto dto)
        {
            Result result = new Result() { Code = -2, Msg = "未知错误" };
            try
            {
                Grp_Customers grp_Customers = _sqlSugar.Queryable<Grp_Customers>().First(a => a.Id == dto.Id && a.IsDel == 0);
                CustomersByIdView customersById = _mapper.Map<CustomersByIdView>(grp_Customers);

                if (customersById != null)
                {
                    Grp_CreditCardPayment creditCardPayment = _sqlSugar.Queryable<Grp_CreditCardPayment>().First(a => a.CId == dto.Id && a.IsDel == 0 && a.CTable == 82);
                    CreditCardView creditCardView = _mapper.Map<CreditCardView>(creditCardPayment);
                    Grp_InsuranceCost Iid = _sqlSugar.Queryable<Grp_InsuranceCost>().First(a => a.IsDel == 0 && a.Id == customersById.Iid);
                    if (Iid != null)
                    {
                        customersById.IidStr = Iid.GName;

                        string CountSql = string.Format(@"select sum(InsuranceCosts) as CountCost from Grp_Customers where isdel=0 and Iid=" + customersById.Iid);
                        DataCountCost dataCount = _sqlSugar.SqlQueryable<DataCountCost>(CountSql).First();
                        customersById.InsuranceBalance = Math.Round(Iid.RechargeCost - dataCount.CountCost, 2);
                    }



                    Sys_SetData Currency = _sqlSugar.Queryable<Sys_SetData>().First(a => a.IsDel == 0 && a.Id == customersById.Currency);
                    if (Currency != null) customersById.CurrencyStr = Currency.Name;

                    Sys_SetData PayDId = _sqlSugar.Queryable<Sys_SetData>().First(a => a.IsDel == 0 && a.Id == creditCardView.PayDId);
                    if (PayDId != null) creditCardView.PayDIdStr = PayDId.Name;

                    Sys_SetData CTDId = _sqlSugar.Queryable<Sys_SetData>().First(a => a.IsDel == 0 && a.Id == creditCardView.CTDId);
                    if (CTDId != null) creditCardView.CTDIdStr = CTDId.Name;
                    creditCardView.OrbitalPrivateTransferStr = creditCardView.OrbitalPrivateTransfer == 0 ? "公转" : "私转";

                    Sys_SetData PaymentCurrency = _sqlSugar.Queryable<Sys_SetData>().First(a => a.IsDel == 0 && a.Id == creditCardView.PaymentCurrency);
                    if (PaymentCurrency != null) creditCardView.PaymentCurrencyStr = PaymentCurrency.Name;



                    var data = new
                    {
                        customers = customersById,
                        creditCard = creditCardView
                    };
                    return result = new Result() { Code = 0, Msg = "查询成功！", Data = data };
                }
                var dataN = new
                {
                    customers = new CustomersView(),
                    creditCard = new CreditCardView()
                };
                return result = new Result() { Code = 0, Msg = "暂无数据！", Data = dataN };

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

        public async Task<Result> CustomersInitialize(CustomersInitializeDto dto)
        {
            Result result = new Result() { Code = -2, Msg = "未知错误" };
            try
            {


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

                //卡类型
                List<Sys_SetData> BankCard = _sqlSugar.Queryable<Sys_SetData>().Where(a => a.STid == 15 && a.IsDel == 0).ToList();
                List<SetDataCurrencyInfoView> _BankCard = _mapper.Map<List<SetDataCurrencyInfoView>>(BankCard);

                //保险名称
                List<Grp_InsuranceCost> _InsuranceCosts = _sqlSugar.Queryable<Grp_InsuranceCost>().Where(a => a.IsDel == 0).ToList();
                List<InsuranceCostView> insuranceCostViews = new List<InsuranceCostView>();
                foreach (var item in _InsuranceCosts)
                {
                    InsuranceCostView costView = new InsuranceCostView();
                    costView.Id = item.Id;
                    costView.GName = item.GName;
                    costView.Balance = 0.00M;
                    string CountSql = string.Format(@"select sum(InsuranceCosts) as CountCost from Grp_Customers where isdel=0 and Iid=" + item.Id);
                    DataCountCost dataCount = _sqlSugar.SqlQueryable<DataCountCost>(CountSql).First();
                    costView.Balance = Math.Round(item.RechargeCost - dataCount.CountCost, 2);
                    insuranceCostViews.Add(costView);
                }

                //币种
                GeneralTeamRateInfoDto PostGroupTeamRatedto = new GeneralTeamRateInfoDto();
                PostGroupTeamRatedto.DiId = dto.DiId;
                PostGroupTeamRatedto.CTable = 82;
                PostGroupTeamRatedto.PortType = dto.PortType;

                var _teamRate = await _teamRateRep.PostGroupTeamRateItemByDiIdAndCTableId(dto.PortType, dto.DiId, 82);

                var data = new
                {
                    Payment = _Payment,
                    CurrencyList = _teamRate,
                    BankCard = _BankCard,
                    insuranceCost = insuranceCostViews,
                };

                return result = new Result() { Code = 0, Msg = "查询成功", Data = data };

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

        public async Task<Result> OpCustomers(OpCustomersDto dto)
        {
            Result result = new Result() { Code = -2, Msg = "未知错误" };

            BeginTran();
            int id = dto.Id;
            Grp_Customers cus = _mapper.Map<Grp_Customers>(dto);

            Grp_CreditCardPayment c = _mapper.Map<Grp_CreditCardPayment>(dto);
            c.Remark = dto.CRemark;
            c.PayPercentage = 100;
            c.CTable = 82;
            c.CId = id;
            c.IsAuditGM = 0;
            c.PayMoney = cus.InsuranceCosts;
            c.PaymentCurrency = cus.Currency;

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

            c.RMBPrice = cus.InsuranceCosts;
            c.DayRate = 1;
            Grp_TeamRate _TeamRate = _sqlSugar.Queryable<Grp_TeamRate>().First(a => a.DiId == dto.DiId && a.IsDel == 0 && a.CTable == 82);
            List<CurrencyInfo> currencyInfos = new List<CurrencyInfo>();
            if (_TeamRate != null)
            {
                Sys_SetData _SetData = _sqlSugar.Queryable<Sys_SetData>().First(a => a.IsDel == 0 && a.Id == cus.Currency);
                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)//添加
            {
                var customers = _sqlSugar.Queryable<Grp_Customers>()
                    .First(a => a.DiId == dto.DiId &&
                                a.IsDel == 0 &&
                                a.ClientName == dto.ClientName &&
                                a.InsuranceCosts == dto.InsuranceCosts &&
                                a.Currency == dto.Currency
                    );
                if (customers != null)
                {
                    return result = new Result() { Code = -1, Msg = "该笔费用已存在，请勿重复添加！" };
                }
                else
                {
                    id = await AddAsyncReturnId(cus);
                    if (id != 0)
                    {
                        c.CId = id;
                        int cId = await _sqlSugar.Insertable(c).ExecuteReturnIdentityAsync();
                        if (cId != 0)
                        {
                            var data = new { ccpId = cId, sign = 1, dataId = id };
                            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_Customers
                {
                    Iid = cus.Iid,
                    ClientName = cus.ClientName,
                    InsuranceCosts = cus.InsuranceCosts,
                    Currency = cus.Currency,
                    Attachment = cus.Attachment,
                    Remark = cus.Remark
                });
                if (res)
                {
                    int CTable = await _sqlSugar.Updateable<Grp_CreditCardPayment>()
                        .Where(a => a.CId == cus.Id && a.CTable == 82)
                        .SetColumns(a => new Grp_CreditCardPayment
                        {
                            PayDId = c.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,
                            IsAuditGM = c.IsAuditGM,
                        })
                        .ExecuteCommandAsync();
                    if (CTable == 0)
                    {
                        result = new Result() { Code = -1, Msg = "修改失败！" };
                        RollbackTran();
                    }
                    else
                    {
                        Grp_CreditCardPayment ccp = Query<Grp_CreditCardPayment>(a => a.CId == cus.Id && a.CTable == 82).First();
                        var data = new { ccpId = ccp.Id, sign = 2, dataId = dto.Id };
                        result = new Result() { Code = 0, Msg = "修改成功！", Data = data };
                    }
                }
                else
                {
                    RollbackTran();
                    result = new Result() { Code = -1, Msg = "修改失败，请稍后重试！" };
                }
            }
            CommitTran();

            return result;
        }
    }
}