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

using AutoMapper;
using NPOI.POIFS.Crypt.Dsig;
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.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 HotelPriceRepository _hotel;
        public CustomersRepository(SqlSugarClient sqlSugar, IMapper mapper, HotelPriceRepository hotel) : base(sqlSugar)
        {
            _mapper = mapper;
            _hotel= hotel;
        }
        /// <summary>
        /// 根据团组Id查询保险费用列表
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        public async Task<Result> CustomersByDiId(CustomersByDiIdDto dto)
        {
            Result result = new Result() { Code = -2, Msg = "未知错误" };
            try
            {
                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.Empty;
                if (!string.IsNullOrWhiteSpace(dto.ClientName))
                {
                    sqlWhere = string.Format(@"Where h.DiId={0} and h.IsDel={1} And h.CreateUserId in ({2}) and h.ClientName like '%{3}%'", dto.DiId, 0, UserId,dto.ClientName);
                }
                else
                {
                    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
                                                    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} 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();

                    sql = string.Format("SELECT * FROM Crm_DeleClient cdc where id in ({0}) and IsDel  = 0",string.Join(',', CrmIds));
                    List<Crm_DeleClient> clietArr = _sqlSugar.SqlQueryable<Crm_DeleClient>(sql).ToList();

                    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);
                            name += (clientObject?.LastName + clientObject?.FirstName);

                            if (!string.IsNullOrWhiteSpace(name))
                            {
                                item.ClientName = name;
                            }
                            continue;
                        }
                    }
                    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
                                                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 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();

                    sql = string.Format("SELECT * FROM Crm_DeleClient cdc where id in ({0}) and IsDel  = 0", string.Join(',', CrmIds));
                    List<Crm_DeleClient> clietArr = _sqlSugar.SqlQueryable<Crm_DeleClient>(sql).ToList();

                    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);
                            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" };
                }
            }
            catch (Exception ex)
            {
                return result = new Result() { Code = -2, Msg = "未知错误" };
                throw;
            }
        }

        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;
                Result teamRateDescAddCurrencyIdViews = _hotel.PostGroupTeamRateByDiIdAndCTableId(PostGroupTeamRatedto);
                List<TeamRateDescAddCurrencyIdView> Currency = new List<TeamRateDescAddCurrencyIdView>();
                if (teamRateDescAddCurrencyIdViews.Code == 0)
                {
                    Currency = teamRateDescAddCurrencyIdViews.Data;
                }

                var data = new
                {
                    Payment = _Payment,
                    CurrencyList = Currency,
                    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 = "未知错误" };
            try
            {
                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;
                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)//添加
                {
                    Grp_Customers customers = _sqlSugar.Queryable<Grp_Customers>().First(a => a.IsDel == 0 && a.ClientName == dto.ClientName && a.InsuranceCosts == dto.InsuranceCosts);
                    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)
                            {
                                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_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 = 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();
                        if (CTable == 0)
                        {
                            result = new Result() { Code = -1, Msg = "修改失败！" };
                            RollbackTran();
                        }
                        else
                        {
                            result = new Result() { Code = 0, Msg = "修改成功！" };
                        }

                    }
                    else
                    {
                        RollbackTran();
                        result = new Result() { Code = -1, Msg = "修改失败，请稍后重试！" };
                    }
                }
                CommitTran();
            }
            catch (Exception ex)
            {
                return result = new Result() { Code = -2, Msg = "未知错误" };
                throw;
            }
            return result;
        }
    }
}