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

using AutoMapper;
using OASystem.Domain;
using OASystem.Domain.Dtos.Groups;
using OASystem.Domain.Entities.Groups;
using OASystem.Domain.ViewModels.Groups;
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;
        public CustomersRepository(SqlSugarClient sqlSugar, IMapper mapper) : base(sqlSugar)
        {
            _mapper = mapper;
        }
        /// <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.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();
                    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 = "私转";

                    }
                    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();
                    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 = "私转";
                    }
                    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);
                Grp_CreditCardPayment creditCardPayment = _sqlSugar.Queryable<Grp_CreditCardPayment>().First(a => a.CId == dto.Id && a.IsDel == 0 && a.CTable==82);
                customersById = _mapper.Map<CustomersByIdView>(creditCardPayment);
                if (customersById != null)
                {
                    Sys_SetData Iid = _sqlSugar.Queryable<Sys_SetData>().First(a => a.IsDel == 0 && a.Id == customersById.Iid);
                    if (Iid != null) customersById.IidStr = Iid.Name;

                    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 == customersById.PayDId);
                    if (PayDId != null) customersById.PayDIdStr = PayDId.Name;

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

                    customersById.OrbitalPrivateTransferStr=customersById.OrbitalPrivateTransfer== 0 ? "公转" : "私转";
                    customersById.CRemark = creditCardPayment.Remark;
                }
                return result = new Result() { Code = 0, Msg = "查询成功！",Data= customersById };
            }
            catch (Exception ex)
            {
                return result = new Result() { Code = -2, Msg = "未知错误" };
                throw;
            }
        }
    }
}