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

        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 == 76).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,s1.Name as GuestType,h.ReservationsNo,h.HotelName,h.CheckInDate,
                                                h.CheckOutDate,c.PayMoney,s.Name as PaymentCurrency,u.CnName as CreateUserName,c.IsAuditGM,h.CreateTime,h.Attachment
                                                From Grp_HotelReservations h 
                                                Join Grp_CreditCardPayment c on h.Id=c.CId and c.CTable=76 and c.isdel=0
                                                left Join Sys_SetData s on  c.PaymentCurrency=s.Id  
                                                left Join Sys_SetData s1 on h.GTId=s1.Id 
                                                left Join Sys_Users u on u.Id=h.CreateUserId {0} order by c.IsAuditGM,c.PayPercentage desc", sqlWhere);
                    List<HotelReservationsByDiIdView> hotelDataList = _sqlSugar.SqlQueryable<HotelReservationsByDiIdView>(sql).ToList();
                    foreach (var item in hotelDataList)
                    {
                        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 (!string.IsNullOrWhiteSpace(item.CheckInDate))
                        {
                            item.CheckInDate = DateTime.Parse(item.CheckInDate).ToString("yyyy-MM-dd");
                        }
                        if (!string.IsNullOrWhiteSpace(item.CheckOutDate))
                        {
                            item.CheckOutDate = DateTime.Parse(item.CheckOutDate).ToString("yyyy-MM-dd");
                        }



                    }
                    return result = new Result() { Code = 0, Msg = "查询成功！", Data = hotelDataList };
                }
                else if (dto.PortType == 2 || dto.PortType == 3)
                {
                    string sql = string.Format(@"Select * From (	
                                                Select row_number() over (order by c.IsAuditGM,c.PayPercentage desc) as RowNumber,h.Id,s1.Name as GuestType,h.ReservationsNo,h.HotelName,h.CheckInDate,
                                                h.CheckOutDate,c.PayMoney,s.Name as PaymentCurrency,u.CnName as CreateUserName,c.IsAuditGM,h.CreateTime,h.Attachment
                                                 From Grp_HotelReservations h 
                                                Join Grp_CreditCardPayment c on h.Id=c.CId and c.CTable=76 and c.isdel=0
                                                left Join Sys_SetData s on  c.PaymentCurrency=s.Id 
                                                left Join Sys_SetData s1 on h.GTId=s1.Id 
                                                left Join Sys_Users u on u.Id=h.CreateUserId   {0}
                                                ) temp Where RowNumber Between {1} and {2}", sqlWhere, startIndex, endIndex);

                    List<HotelReservationsByDiIdView> hotelDataList = _sqlSugar.SqlQueryable<HotelReservationsByDiIdView>(sql).ToList();
                    foreach (var item in hotelDataList)
                    {
                        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 (!string.IsNullOrWhiteSpace(item.CheckInDate))
                        {
                            item.CheckInDate = DateTime.Parse(item.CheckInDate).ToString("yyyy-MM-dd");
                        }
                        if (!string.IsNullOrWhiteSpace(item.CheckOutDate))
                        {
                            item.CheckOutDate = DateTime.Parse(item.CheckOutDate).ToString("yyyy-MM-dd");
                        }
                    }
                    string CountSql = string.Format(@"Select COUNT(1) as Count From (	
                                                        Select h.Id,s1.Name as GuestType,h.ReservationsNo,h.HotelName,h.CheckInDate,
                                                        h.CheckOutDate,c.PayMoney,s.Name as PaymentCurrency,u.CnName as CreateUserName,c.IsAuditGM,h.CreateTime,h.Attachment
                                                        From Grp_HotelReservations h 
                                                        Join Grp_CreditCardPayment c on h.Id=c.CId and c.CTable=76 and c.isdel=0
                                                        left Join Sys_SetData s on  c.PaymentCurrency=s.Id 
                                                        left Join Sys_SetData s1 on h.GTId=s1.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<HotelReservationsByDiIdView> rst = new ListViewBase<HotelReservationsByDiIdView>();
                    rst.DataList = hotelDataList;
                    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;
            }
        }
    }
}