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

using AutoMapper;
using OASystem.Domain;
using OASystem.Domain.Dtos.Groups;
using OASystem.Domain.Entities.Groups;
using OASystem.Domain.ViewModels.Groups;
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 HotelPriceRepository : BaseRepository<Grp_HotelReservations, Grp_HotelReservations>
    {
        private readonly IMapper _mapper;
        public HotelPriceRepository(SqlSugarClient sqlSugar, IMapper mapper) :
            base(sqlSugar)
        {
            this._mapper = mapper;
        }

        public async Task<Result> HotelReservationsByDiId(HotelReservationsByDiIdDto 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 and s.IsDel=0
                                                left Join Sys_SetData s1 on h.GTId=s1.Id and s1.IsDel=0
                                                left Join Sys_Users u on u.Id=h.CreateUserId and u.isdel=0 {0}", 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 = "自动审核";

                    }
                    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 h.Id 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 and s.IsDel=0
                                                left Join Sys_SetData s1 on h.GTId=s1.Id and s1.IsDel=0
                                                left Join Sys_Users u on u.Id=h.CreateUserId and u.isdel=0  {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 = "自动审核";

                    }
                    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 and s.IsDel=0
                                                        left Join Sys_SetData s1 on h.GTId=s1.Id and s1.IsDel=0
                                                        left Join Sys_Users u on u.Id=h.CreateUserId and u.isdel=0  {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;
            }
        }

        public async Task<Result> HotelReservationsById(HotelReservationsByIdDto dto)
        {
            Result result = new Result() { Code = -2, Msg = "未知错误" };
            try
            {
                Grp_HotelReservations hotelReservationsById = _sqlSugar.Queryable<Grp_HotelReservations>().First(a => a.IsDel == 0 && a.Id == dto.Id);
                HotelReservationsByIdView _hotelReservations = _mapper.Map<HotelReservationsByIdView>(hotelReservationsById);
                if (_hotelReservations!=null)
                {
                    
                    Sys_SetData GTId = _sqlSugar.Queryable<Sys_SetData>().First(a => a.IsDel == 0 && a.Id == hotelReservationsById.GTId);
                    if (GTId != null) _hotelReservations.GtIdStr = GTId.Name;
                    
                    _hotelReservations.CheckInDate=Convert.ToDateTime(hotelReservationsById.CheckInDate).ToString("yyyy-MM-dd");
                    _hotelReservations.CheckOutDate = Convert.ToDateTime(hotelReservationsById.CheckOutDate).ToString("yyyy-MM-dd");

                    Sys_SetData GovernmentRentCurrencyStr = _sqlSugar.Queryable<Sys_SetData>().First(a => a.IsDel == 0 && a.Id == hotelReservationsById.GovernmentRentCurrency);
                    if (GovernmentRentCurrencyStr != null) _hotelReservations.GovernmentRentCurrencyStr = GovernmentRentCurrencyStr.Name;

                    Sys_SetData CityTaxCurrencyStr = _sqlSugar.Queryable<Sys_SetData>().First(a => a.IsDel == 0 && a.Id == hotelReservationsById.CityTaxCurrency);
                    if (CityTaxCurrencyStr != null) _hotelReservations.CityTaxCurrencyStr = CityTaxCurrencyStr.Name;

                    if (_hotelReservations.CheckType == "") _hotelReservations.CheckTypeStr = "客人房";
                    else if (_hotelReservations.CheckType == "D") _hotelReservations.CheckTypeStr = "司机房";
                    else if (_hotelReservations.CheckType == "G") _hotelReservations.CheckTypeStr = "导游房";
                    else if (_hotelReservations.CheckType == "D&G") _hotelReservations.CheckTypeStr = "司机导游房";



                }
               
                string Sql = string.Format(@"select c.PayDId, s.Name asPayDIdStr,c.ConsumptionPatterns,c.ConsumptionDate,c.CTDId,
                                            s1.Name as CTDIdStr,c.BankNo,c.CardholderName,c.PayMoney,c.PaymentCurrency,s2.Name
                                            as PaymentCurrencyStr,c.DayRate,c.CompanyBankNo,c.OtherBankName,c.OtherSideNo,c.OtherSideName,
                                            c.IsAuditGM,c.Payee,c.RMBPrice,c.OrbitalPrivateTransfer from Grp_CreditCardPayment c
                                            left join Sys_SetData s on c.PayDId=s.Id
                                            left join Sys_SetData s1 on c.CTDId=s1.Id
                                            left join Sys_SetData s2 on c.PaymentCurrency=s2.Id
                                            where c.CId ={0} and c.IsDel = 0 and c.CTable = 76",dto.Id);
                Grp_CreditCardView _CreditCardPayment = _sqlSugar.SqlQueryable<Grp_CreditCardView>(Sql).First();
                if (_CreditCardPayment!=null)
                {
                    if(!string.IsNullOrWhiteSpace(_CreditCardPayment.ConsumptionDate)) _CreditCardPayment.ConsumptionDate=Convert.ToDateTime(_CreditCardPayment.ConsumptionDate).ToString("yyyy-MM-dd");
                }
                var data = new
                {
                    hotelReservations = _hotelReservations,
                    creditCardPayment = _CreditCardPayment,
                };
                return result = new Result() { Code = 0, Msg = "查询成功",Data= data };
            }
            catch (Exception ex)
            {
                return result = new Result() { Code = -2, Msg = "未知错误" };
                throw;
            }
        }

        public async Task<Result> HotelReservationsInitialize(HotelReservationsDto dto)
        {
            Result result = new Result() { Code = -2, Msg = "未知错误" };
            try
            {
                List<Grp_GroupsTaskAssignment> grp_GroupsTaskAssignment = Query<Grp_GroupsTaskAssignment>(a => a.IsDel == 0 && a.UId == dto.UserId && a.CTId == 76).ToList();

                string DiId = "0";
                foreach (var item in grp_GroupsTaskAssignment)
                {
                    DiId += item.DIId + ",";
                }
                if (DiId != "0")
                {
                    DiId = DiId.Substring(0, DiId.Length - 1);
                }
                string sql = string.Format(@"select * from Grp_DelegationInfo where Id in({0}) and IsDel={1}", DiId, 0);
                //团组下拉框
                List<Grp_DelegationInfo> Delegations = _sqlSugar.SqlQueryable<Grp_DelegationInfo>(sql).ToList();
                List<ShareGroupInfoIIView> grp_Delegations = _mapper.Map<List<ShareGroupInfoIIView>>(Delegations);
                for (int i = 0;i< grp_Delegations.Count; i++)
                {
                    grp_Delegations[i].VisitDate = Delegations[i].VisitStartDate.ToString("yyyy-MM-dd")+"至"+ Delegations[i].VisitEndDate.ToString("yyyy-MM-dd");
                }
                //客人类型
                List<Sys_SetData> GuestType = _sqlSugar.Queryable<Sys_SetData>().Where(a=>a.IsDel==0 && a.STid==11).ToList();
                List<SetDataInfoView> _GuestType = _mapper.Map<List<SetDataInfoView>>(GuestType);

                //支付方式
                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> CurrencyList = _sqlSugar.Queryable<Sys_SetData>().Where(a => a.STid == 66 && a.IsDel == 0).ToList();
                List<SetDataInfoView> _CurrencyList = _mapper.Map<List<SetDataInfoView>>(CurrencyList);

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

                //预订网站
                List<Sys_SetData> BookingWebsite = _sqlSugar.Queryable<Sys_SetData>().Where(a => a.STid == 12 && a.IsDel == 0).ToList();
                List<SetDataInfoView> _BookingWebsite = _mapper.Map<List<SetDataInfoView>>(BookingWebsite);

                var data = new
                {
                    Delegations = grp_Delegations,
                    GuestType = _GuestType,
                    Payment = _Payment,
                    CurrencyList = _CurrencyList,
                    BankCard = _BankCard,
                    BookingWebsite = _BookingWebsite,
                };
                return result = new Result() { Code = 0, Msg = "查询成功",Data=data };
            }
            catch (Exception ex)
            {
               return result = new Result() { Code = -2, Msg = "未知错误" };
                throw;
            }
        }

        public Task<Result> OpHotelReservations(OpHotelReservationsData dto)
        {
            throw new NotImplementedException();
        }
    }
}