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

using AutoMapper;
using MathNet.Numerics.Statistics.Mcmc;
using MySqlX.XDevAPI.Common;
using NPOI.SS.Formula.Functions;
using OASystem.Domain;
using OASystem.Domain.Dtos.Financial;
using OASystem.Domain.Dtos.Groups;
using OASystem.Domain.Dtos.Resource;
using OASystem.Domain.Entities.Financial;
using OASystem.Domain.Entities.Groups;
using OASystem.Domain.ViewModels.Financial;
using OASystem.Domain.ViewModels.Groups;
using OASystem.Infrastructure.Repositories.Resource;
using OASystem.Infrastructure.Tools;
using SqlSugar;
using StackExchange.Redis;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using static NPOI.HSSF.Util.HSSFColor;
using Result = OASystem.Domain.Result;

namespace OASystem.Infrastructure.Repositories.Groups
{
    public class HotelPriceRepository : BaseRepository<Grp_HotelReservations, Grp_HotelReservations>
    {
        private readonly IMapper _mapper;
        private readonly Result _result;
        private readonly TeamRateRepository _teamRateRep;
        private readonly TourClientListRepository _tourClientListRep;
        private readonly HotelDataRepository _hotelDataRep;
        public HotelPriceRepository(SqlSugarClient sqlSugar, IMapper mapper, TeamRateRepository teamRateRep, TourClientListRepository tourClientListRep, HotelDataRepository hotelDataRep) 
            :base(sqlSugar)
        {
            this._mapper = mapper;
            _result = new Result() { Code = -1, Msg = "操作失败!" };
            _teamRateRep = teamRateRep;
            _tourClientListRep = tourClientListRep;
            _hotelDataRep = hotelDataRep;
        }

        #region 保留

        /// <summary>
        /// 付款金额计算
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        public async Task<Result> HotelConversionAmounts(HotelReservationsCNYDto dto)
        {
            Result result = new Result() { Code = -2, Msg = "未知错误" };
            try
            {
                HotelReservationsView reservationsView=new HotelReservationsView();
                if ((dto.CardPriceCurrency == dto.GovernmentRentCurrency || dto.GovernmentRent == 0)
                 && (dto.CardPriceCurrency == dto.CityTaxCurrency || dto.CityTax== 0))
                {
                    reservationsView.CurrencyId = dto.CardPriceCurrency;
                    reservationsView.Price=Convert.ToDecimal((dto.CardPrice+dto.GovernmentRent+dto.CityTax).ToString("F2"));
                    reservationsView.CurrencyName = _sqlSugar.Queryable<Sys_SetData>().First(a => a.Id == dto.CardPriceCurrency).Name;
                    return result = new Result() { Code = 0, Msg = "查询成功", Data = reservationsView };
                }
                else
                {
                    Grp_TeamRate _TeamRate = _sqlSugar.Queryable<Grp_TeamRate>().First(a => a.DiId == dto.DiId && a.IsDel == 0 && a.CTable == 76);
                    List<CurrencyInfo> currencyInfos = new List<CurrencyInfo>();
                    decimal CardPrice = 0.00M;
                    decimal GovernmentRent = 0.00M;
                    decimal CityTax = 0.00M;
                    if (_TeamRate != null)
                    {
                        Sys_SetData _SetData = _sqlSugar.Queryable<Sys_SetData>().First(a => a.IsDel == 0 && a.Id == dto.CardPriceCurrency);
                        if (_SetData != null)
                        {
                            if (_SetData.Name=="CNY")
                            {
                                CardPrice = dto.CardPrice;
                            }
                            else
                            {
                                currencyInfos = CommonFun.GetCurrencyChinaToList(_TeamRate.Remark);
                                CurrencyInfo CurrencyRate = currencyInfos.FirstOrDefault(a => a.CurrencyCode == _SetData.Name);
                                if (CurrencyRate != null) CardPrice = dto.CardPrice * Convert.ToDecimal(CurrencyRate.Rate);
                                else return result = new Result() { Code = -1, Msg = "暂未设置团组汇率，请前往设置！", Data = reservationsView };
                            }
                        }
                        else return result = new Result() { Code = -1, Msg = "暂未设置团组汇率，请前往设置！", Data = reservationsView };

                        Sys_SetData _SetData1 = _sqlSugar.Queryable<Sys_SetData>().First(a => a.IsDel == 0 && a.Id == dto.GovernmentRentCurrency);
                        if (_SetData1 != null)
                        {
                            if (_SetData1.Name == "CNY")
                            {
                                GovernmentRent = dto.GovernmentRent;
                            }
                            else {
                                currencyInfos = CommonFun.GetCurrencyChinaToList(_TeamRate.Remark);
                                CurrencyInfo CurrencyRate = currencyInfos.FirstOrDefault(a => a.CurrencyCode == _SetData1.Name);
                                if (CurrencyRate != null) GovernmentRent = dto.GovernmentRent * Convert.ToDecimal(CurrencyRate.Rate);
                                else return result = new Result() { Code = -1, Msg = "暂未设置团组汇率，请前往设置！", Data = reservationsView };
                            }
                                
                        }
                        else return result = new Result() { Code = -1, Msg = "暂未设置团组汇率，请前往设置！", Data = reservationsView };

                        Sys_SetData _SetData2 = _sqlSugar.Queryable<Sys_SetData>().First(a => a.IsDel == 0 && a.Id == dto.CityTaxCurrency);
                        if (_SetData2 != null)
                        {
                            if (_SetData2.Name == "CNY")
                            {
                                CityTax = dto.CityTax;
                            }
                            else
                            {
                                currencyInfos = CommonFun.GetCurrencyChinaToList(_TeamRate.Remark);
                                CurrencyInfo CurrencyRate = currencyInfos.FirstOrDefault(a => a.CurrencyCode == _SetData2.Name);
                                if (CurrencyRate != null) CityTax = dto.CityTax * Convert.ToDecimal(CurrencyRate.Rate);
                                else return result = new Result() { Code = -1, Msg = "暂未设置团组汇率，请前往设置！", Data = reservationsView };
                            }
                        }
                        else return result = new Result() { Code = -1, Msg = "暂未设置团组汇率，请前往设置！", Data = reservationsView };
                       
                        reservationsView.CurrencyId = 836;
                        reservationsView.Price = Convert.ToDecimal((CardPrice + GovernmentRent + CityTax).ToString("F2"));
                        reservationsView.CurrencyName ="CNY";

                        return result = new Result() { Code = 0, Msg = "查询成功", Data = reservationsView };
                    }
                    else
                    {
                       return result = new Result() { Code = -1, Msg = "暂未设置团组汇率，请前往设置！", Data= reservationsView };
                    }
                }
            }
            catch (Exception ex)
            {
                return result = new Result() { Code = -2, Msg = "未知错误" };
                throw;
            }
        }

       
        /// <summary>
        /// 根据团组id查询酒店数据
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        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  
                                                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;
            }
        }
        
        /// <summary>
        /// 根据酒店费用Id查询详细数据
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        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;
                    if (!string.IsNullOrWhiteSpace(_hotelReservations.CheckInDate))
                    {
                        _hotelReservations.CheckInDate = Convert.ToDateTime(hotelReservationsById.CheckInDate).ToString("yyyy-MM-dd");
                    }
                    if (!string.IsNullOrWhiteSpace(_hotelReservations.CheckOutDate))
                    {
                        _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,c.Remark 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;
            }
        }
        /// <summary>
        /// 酒店页面下拉框等初始化
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        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<SetDataCurrencyInfoView> _BankCard = _mapper.Map<List<SetDataCurrencyInfoView>>(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);
               
                if (dto.PortType==2 || dto.PortType==3)
                {
                    GeneralTeamRateInfoDto PostGroupTeamRatedto = new GeneralTeamRateInfoDto();
                    PostGroupTeamRatedto.DiId= dto.DiId;
                    PostGroupTeamRatedto.CTable = 76;
                    PostGroupTeamRatedto.PortType= dto.PortType;
                    var _teamRateView = await _teamRateRep.PostGroupTeamRateItemByDiIdAndCTableId(dto.PortType, dto.DiId, 76);

                    var data = new
                    {
                        GuestType = _GuestType,
                        Payment = _Payment,
                        CurrencyList = _teamRateView,
                        BankCard = _BankCard,
                        BookingWebsite = _BookingWebsite,
                    };
                    return result = new Result() { Code = 0, Msg = "查询成功", Data = data };
                }
                else
                {
                    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;
            }
        }

        /// <summary>
        /// 酒店操作
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        /// <exception cref="NotImplementedException"></exception>
        public async Task<Result> OpHotelReservations(OpHotelReservationsData dto)
        {
            Result result = new Result() { Code = -2, Msg = "未知错误" };
            try
            {
                BeginTran();
                int id = dto.Id;
                Grp_HotelReservations hotelPrice = _mapper.Map<Grp_HotelReservations>(dto);
                hotelPrice.IsCardPrice = hotelPrice.CardPrice!=0 ? 1:0;
                hotelPrice.CboOne=hotelPrice.SingleRoomCount !=0 ? 1 : 0;
                hotelPrice.CboTwo = hotelPrice.DoubleRoomCount != 0 ? 1 : 0;
                hotelPrice.CboThree = hotelPrice.SuiteRoomCount != 0 ? 1 : 0;
                hotelPrice.CboFour = hotelPrice.SuiteRoomCount != 0 ? 1 : 0;

                Grp_CreditCardPayment c = _mapper.Map<Grp_CreditCardPayment>(dto);
                c.Remark = dto.CRemark;
                c.PayPercentage = 100;
                c.CTable = 76;
                c.CId = id;
                c.IsAuditGM = 0;
                if (c.PayDId == 72) c.IsPay = 1;
                else c.IsPay = 0;
                c.RMBPrice = c.PayMoney;
                c.DayRate = 1;
                Grp_TeamRate _TeamRate = _sqlSugar.Queryable<Grp_TeamRate>().First(a => a.DiId == dto.DiId && a.IsDel == 0 && a.CTable == 76);
                List<CurrencyInfo> currencyInfos = new List<CurrencyInfo>();
                if (_TeamRate != null)
                {
                    Sys_SetData _SetData = _sqlSugar.Queryable<Sys_SetData>().First(a => a.IsDel == 0 && a.Id == c.PaymentCurrency);
                    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_HotelReservations grp_Hotel = _sqlSugar.Queryable<Grp_HotelReservations>().First(a => a.IsDel == 0 && a.HotelName == dto.HotelName && a.GuestName == dto.GuestName &&
                    a.CheckInDate == dto.CheckInDate && a.CheckOutDate==dto.CheckOutDate && a.City==dto.City);
                    if (grp_Hotel != null)
                    {
                        return result = new Result() { Code = -1, Msg = "该笔费用已存在，请勿重复添加！" };
                    }
                    else
                    {
                        id = await AddAsyncReturnId(hotelPrice);
                        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_HotelReservations
                    {
                        GTId=hotelPrice.GTId,
                        CheckNumber=hotelPrice.CheckNumber,
                        ReservationsWebsite=hotelPrice.ReservationsWebsite,
                        ReservationsNo=hotelPrice.ReservationsNo,
                        DetermineNo=hotelPrice.DetermineNo,
                        City=hotelPrice.City,
                        HotelName=hotelPrice.HotelName,
                        HotelAddress=hotelPrice.HotelAddress,
                        HotelTel=hotelPrice.HotelTel,
                        GuestName=hotelPrice.GuestName,
                        CheckInDate=hotelPrice.CheckInDate,
                        BudgetCurrency=hotelPrice.BudgetCurrency,
                        CheckOutDate=hotelPrice.CheckOutDate,
                        SingleRoomCount=hotelPrice.SingleRoomCount,
                        SingleRoomPrice=hotelPrice.SingleRoomPrice,
                        DoubleRoomCount=hotelPrice.DoubleRoomCount,
                        DoubleRoomPrice=hotelPrice.DoubleRoomPrice,
                        SuiteRoomCount=hotelPrice.SuiteRoomCount,
                        SuiteRoomPrice=hotelPrice.SuiteRoomPrice,
                        OtherRoomCount=hotelPrice.OtherRoomCount,
                        OtherRoomPrice=hotelPrice.OtherRoomPrice,
                        RoomExplanation=hotelPrice.RoomExplanation,
                        Attachment=hotelPrice.Attachment,
                        CardPrice=hotelPrice.CardPrice,
                        CardPriceCurrency=hotelPrice.CardPriceCurrency,
                        IsCardPrice =hotelPrice.IsCardPrice,
                        PredictSingleRoom=hotelPrice.PredictSingleRoom,
                        PredictDoubleRoom=hotelPrice.PredictDoubleRoom,
                        PredictSuiteRoom=hotelPrice.PredictSuiteRoom,
                        PredictOtherRoom=hotelPrice.PredictOtherRoom,
                        GovernmentRent=hotelPrice.GovernmentRent,
                        GovernmentRentCurrency=hotelPrice.GovernmentRentCurrency,
                        CityTax=hotelPrice.CityTax,
                        CityTaxCurrency=hotelPrice.CityTaxCurrency,
                        CheckType=hotelPrice.CheckType,
                        CboOne=hotelPrice.SingleRoomCount,
                        CboTwo=hotelPrice.DoubleRoomCount,
                        CboThree=hotelPrice.SuiteRoomCount,
                        CboFour=hotelPrice.SuiteRoomCount,
                        CreateUserId=hotelPrice.CreateUserId,
                        Remark=hotelPrice.Remark
                    });
                    if (res)
                    {
                        int ispay = 0;
                        if (dto.PayDId == 72) ispay = 1;

                        int CTable = await _sqlSugar.Updateable<Grp_CreditCardPayment>().Where(a => a.CId == hotelPrice.Id && a.CTable == 76).SetColumns(a => new Grp_CreditCardPayment
                        {
                            PayDId = dto.PayDId,
                            IsPay=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,

                        }).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;
        }
       
        #endregion

        /// <summary>
        /// 酒店预定
        /// Items By DiId
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        public async Task<Result> _ItemsByDiId(int portType,int diId)
        {

            string sql = string.Format(@"Select row_number() over(order by hr.CreateTime Desc) as Row_Number,
										 hr.Id,hr.DiId,sd1.Name As GuestType,hr.ReservationsNo,hr.HotelName,hr.CheckInDate,hr.CheckOutDate,
										 ccp.PayMoney, ccp.PaymentCurrency,sd2.Name PayCurrency,hr.CreateUserId,u.CnName As CreateUserName,
										 hr.CreateTime,ccp.IsAuditGM,ccp.IsPay
										 From Grp_HotelReservations  hr
										 Inner Join Grp_CreditCardPayment ccp On hr.DiId = ccp.DIId And hr.Id = ccp.CId 
										 And ccp.CTable = 76
										 Left Join Sys_SetData sd1 On hr.GTId = sd1.Id
										 Left Join Sys_SetData sd2 On ccp.PaymentCurrency = sd2.Id
										 Left Join Sys_Users u On hr.CreateUserId = u.Id
										 Where hr.IsDel = 0 And ccp.IsDel = 0 And hr.DiId = {0} ", diId);

            if (portType == 1 || portType == 2 || portType == 3)
            {
                var hotelFeeData = await _sqlSugar.SqlQueryable<HotelReservationsItemsView>(sql).ToListAsync();

                _result.Code = 0;
                _result.Data = hotelFeeData;
                _result.Msg = "操作成功!";
            }
            else
            {
                _result.Msg = "请传入正确的PortType参数,1 Web 2 Android 3 IOS";
            }

            return _result;
        }

        /// <summary>
        /// 酒店预定
        /// basicsData Init
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        public async Task<Result> _BasicsDataInit(int portType, int diId)
        {
            List<Sys_SetData> _dataSouruce = await _sqlSugar.Queryable<Sys_SetData>().Where(a => a.IsDel == 0).ToListAsync();

            if (portType == 1 || portType == 2 || portType == 3)
            {
                //客人类型
                List<Sys_SetData> GuestType = _dataSouruce.Where(a => a.STid == 11).ToList();
                List<SetDataInfoView> _GuestType = _mapper.Map<List<SetDataInfoView>>(GuestType);

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

                //支付方式
                List<Sys_SetData> Payment = _dataSouruce.Where(a => a.STid == 14).ToList();
                List<SetDataInfoView> _Payment = _mapper.Map<List<SetDataInfoView>>(Payment);

                //卡类型
                List<Sys_SetData> BankCard = _dataSouruce.Where(a => a.STid == 15).ToList();
                List<SetDataCurrencyInfoView> _BankCard = _mapper.Map<List<SetDataCurrencyInfoView>>(BankCard);

                //房间入住人类型 
                List<Sys_SetData> CheckPerson = _dataSouruce.Where(a => a.STid == 71).ToList();
                List<SetDataInfoView> _CheckPerson = _mapper.Map<List<SetDataInfoView>>(CheckPerson);

                var _teamRateView = await _teamRateRep.PostGroupTeamRateItemByDiIdAndCTableId(portType, diId, 76);

                string _CheckVolumeNo = string.Empty;
                var checkVoumeNoData = _CreateCheckVolumeNo(diId);
                if (checkVoumeNoData.Result.Code == 0)
                {
                    _CheckVolumeNo = checkVoumeNoData.Result.Data;
                }

                //客户名单
                var guestNames = await _tourClientListRep._GuestNameItemByDiId(portType, diId);

                var data = new
                {
                    GuestType = _GuestType, //客人分类
                    Payment = _Payment,
                    CurrencyList = _teamRateView,
                    BankCard = _BankCard,
                    BookingWebsite = _BookingWebsite,
                    CheckPerson = _CheckPerson,
                    CheckVolumeNo = _CheckVolumeNo,
                    GuestName = guestNames
                };

                _result.Code = 0;
                _result.Data = data;
                _result.Msg = "操作成功!";

            }
            else
            {
                _result.Msg = "请传入正确的PortType参数,1 Web 2 Android 3 IOS!";

            }
            return _result;
        }

        /// <summary>
        /// 酒店预定
        /// 创建 入住卷号码
        /// </summary>
        /// <param name="DiId"></param>
        /// <returns></returns>
        public async Task<Result> _CreateCheckVolumeNo(int DiId)
        {
            var groupInfo = await _sqlSugar.Queryable<Grp_DelegationInfo>().Where(it => it.Id == DiId && it.IsDel == 0).FirstAsync();
            if (groupInfo != null)
            {
                if (!string.IsNullOrEmpty(groupInfo.VisitCountry))
                {
                    string no = string.Empty;

                    string[] countrys = new string[] { };
                    countrys = groupInfo.VisitCountry.Split('|');
                    foreach (string country in countrys)
                    {
                        //国家城市三字码 未挪数据 暂时用随机数代替


                    }
                    no = CommonFun.GetRandomLetter(countrys.Length).ToUpper();

                    no += CommonFun.GetRandomNumber(2);

                    _result.Code = 0;
                    _result.Data = no;
                    _result.Msg = "入住卷号码 获取成功!";
                }
            }

            return _result;
        }

        /// <summary>
        /// 酒店预定-
        /// details
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        public async Task<Result> _Details(int portType, int id)
        {

            if (id < 0 )
            {
                _result.Msg = string.Format(@"请输入正确的Id!");
                return _result;
            }

            string sql = string.Format(@"Select hr.Id,hr.DiId,hr.GTId,ccp.CTDId,hr.CheckNumber,hr.ReservationsWebsite,hr.ReservationsNo,
                                         hr.DetermineNo,hr.City,hr.HotelName,hr.HotelTel,hr.HotelAddress,hr.GuestName,hr.CheckInDate,hr.CheckOutDate,
                                         hr.CheckType,hr.RoomExplanation,hr.Remark As HotelRemark,hr.SingleRoomPrice,hr.SingleRoomCount,hr.DoubleRoomPrice,
                                         hr.DoubleRoomCount,hr.SuiteRoomPrice,hr.SuiteRoomCount,hr.OtherRoomPrice,hr.OtherRoomCount,hr.CardPrice,
                                         hr.Isoppay,hr.BreakfastPrice,hr.BreakfastCurrency,hr.GovernmentRent,hr.GovernmentRentCurrency,hr.CityTax,
                                         hr.CityTaxCurrency,ccp.PayDId,ccp.ConsumptionPatterns,ccp.ConsumptionDate,ccp.PayMoney,ccp.PaymentCurrency,
                                         ccp.BankNo,ccp.CardholderName,ccp.CompanyBankNo,ccp.OtherBankName,ccp.OtherSideNo,ccp.OtherSideName,ccp.Payee,
                                         ccp.OrbitalPrivateTransfer,ccp.Remark As CcpRemark
                                         From Grp_HotelReservations hr
                                         Inner Join Grp_CreditCardPayment ccp On hr.DiId = ccp.DIId And hr.Id = ccp.CId 
                                         And ccp.CTable = 76
                                         Where hr.IsDel = 0 And ccp.IsDel = 0 And  hr.Id = {0}", id);

            if (portType == 1 || portType == 2 || portType == 3)
            {
                var info = await _sqlSugar.SqlQueryable<HotelReservationsDetailsView>(sql).FirstAsync();
                if (info != null) _result.Msg = "操作成功!"; 
                else _result.Msg = "暂无数据"; 

                _result.Code = 0;
                _result.Data = info;
                return _result;
            }
            else
            {
                _result.Msg = "请传入正确的PortType参数,1 Web 2 Android 3 IOS";
                return _result;
            }
        }

        /// <summary>
        /// 酒店预定
        /// Add Or Edit
        /// </summary>
        /// <returns></returns>
        public async Task<Result> _AddOrEdit(HotelReservationsAddOrEditDto _dto)
        {
            _result.Code = -1;
            int portType = _dto.PortType;
            if (portType == 1 || portType == 2 || portType == 3)
            {

                Grp_HotelReservations _HotelReservations = new Grp_HotelReservations();
                Grp_CreditCardPayment _CreditCardPayment = new Grp_CreditCardPayment();

                #region 参数处理
                _HotelReservations = _mapper.Map<Grp_HotelReservations>(_dto);
                _CreditCardPayment = _mapper.Map<Grp_CreditCardPayment>(_dto);

                _HotelReservations.DiId = _dto.DiId;
                _HotelReservations.CardPriceCurrency = _dto.CTDId;
                _HotelReservations.CreateUserId = _dto.UserId;
                _HotelReservations.Remark = _dto.HotelRemark;

                if (portType == 2 || portType == 3)
                {
                    string checkNo = string.Empty;
                    Result checkNoRes = await _CreateCheckVolumeNo(_dto.DiId);
                    if (checkNoRes.Code == 0) 
                    {
                        checkNo = checkNoRes.Data;
                        _HotelReservations.CheckNumber = checkNo;
                    }
                }

                #region CCP 表参数
                if (_dto.PayDId == 72) //刷卡 默认已支付
                {
                    _CreditCardPayment.IsPay = 1;
                }else _CreditCardPayment.IsPay = 0;

                _CreditCardPayment.CreateUserId = _dto.UserId;
                _CreditCardPayment.DIId = _dto.DiId;
                _CreditCardPayment.CTable = 76;
                _CreditCardPayment.CTDId = _dto.CTDId;
                _CreditCardPayment.PayPercentage = 100.00M;
                _CreditCardPayment.PayThenMoney = _CreditCardPayment.PayMoney;

                string paymentCurrencyCode = string.Empty;
                List<Sys_SetData> currencySouruce = await _sqlSugar.Queryable<Sys_SetData>().Where(a => a.IsDel == 0 && a.STid == 66).ToListAsync();
                if (currencySouruce.Count > 0)
                {
                    Sys_SetData currency = new Sys_SetData();
                    currency = currencySouruce.Where(it => it.Id == _CreditCardPayment.PaymentCurrency).FirstOrDefault();
                    if (currency != null)
                    {
                        paymentCurrencyCode = currency.Name;
                    }
                }

                decimal currencyRate = 0.00M;
                if (_CreditCardPayment.PayMoney != 0)
                {
                    if(_CreditCardPayment.PaymentCurrency < 1)
                    {
                        _result.Msg = "请选择付款币种!";
                        return _result;
                    }

                    List<TeamRateModelView> teamRateModelViews = new List<TeamRateModelView>();
                    teamRateModelViews = await _teamRateRep.PostGroupRateInfoByDiId(_dto.DiId);
                    if (teamRateModelViews.Count < 0)
                    {
                        _result.Msg = "该团未设置酒店预订模块相关币种汇率!";
                        return _result;
                    }
                    TeamRateModelView teamRateModels_hotel = new TeamRateModelView();
                    teamRateModels_hotel = teamRateModelViews.Where(it => it.CTableId == 76).FirstOrDefault();
                    if (teamRateModels_hotel == null)
                    {
                        _result.Msg = "该团未设置酒店预订模块相关币种汇率!";
                        return _result;
                    }

                    List<TeamRateDescView> teamRateDescViews = new List<TeamRateDescView>();
                    teamRateDescViews = teamRateModels_hotel.TeamRates;
                    if (teamRateDescViews.Count < 0)
                    {
                        _result.Msg = "该团未设置酒店预订模块相关币种汇率!";
                        return _result;
                    }
                    TeamRateDescView teamRateDescView = new TeamRateDescView();
                    teamRateDescView = teamRateDescViews.Where(it => it.CurrencyCode == paymentCurrencyCode).FirstOrDefault();
                    if (teamRateDescView == null)
                    {
                        _result.Msg = "该团未设置酒店预订模块相关币种汇率!";
                        return _result;
                    }
                    currencyRate = teamRateDescView.Rate;
                }
                
                _CreditCardPayment.DayRate = currencyRate;
                _CreditCardPayment.RMBPrice = (_CreditCardPayment.DayRate * _CreditCardPayment.PayMoney).DecimalsKeepTwo();

                //if (_CreditCardPayment.PayDId == 72) //刷卡
                //{
                //    _CreditCardPayment.BankNo = "6222 **** **** 7990";
                //    _CreditCardPayment.CardholderName = "Zhang Hailin";
                //}

                _CreditCardPayment.Remark = _dto.CcpRemark;
                #endregion

                #endregion

                if (_dto.Id == 0) // Add
                {
                    _sqlSugar.BeginTran();

                    int hotelId = await _sqlSugar.Insertable<Grp_HotelReservations>(_HotelReservations).ExecuteReturnIdentityAsync();
                    if (hotelId < 0)
                    {
                        _result.Msg = "酒店预定信息添加失败!";
                        _sqlSugar.RollbackTran(); //回滚
                        return _result;
                    }
                    _CreditCardPayment.CId = hotelId;
                    _CreditCardPayment.CTable = 76; //酒店预定模块

                    int ccpId = await _sqlSugar.Insertable<Grp_CreditCardPayment>(_CreditCardPayment).ExecuteReturnIdentityAsync();
                    if (ccpId < 0)
                    {
                        _result.Msg = "付款信息添加失败!";
                        _sqlSugar.RollbackTran(); //回滚
                        return _result;
                    }

                    #region 酒店基础数据 添加

                    var hotelDataReq = new OperationHotelDto() {
                        Status = 1,
                        City = _dto.City,
                        Name = _dto.HotelName,
                        Tel = _dto.HotelTel,
                        Address = _dto.HotelAddress
                    };
                     await _hotelDataRep.OperationHotelData(hotelDataReq);

                    #endregion

                    _result.Msg = "操作成功!";
                    _result.Code = 0;
                    _sqlSugar.CommitTran(); // 提交
                }
                else if (_dto.Id > 0) //Edit
                {
                    _sqlSugar.BeginTran();

                    int hotelStatus = await _sqlSugar.Updateable<Grp_HotelReservations>(_HotelReservations)
                                                     .UpdateColumns(it => new
                                                     {
                                                         it.GTId,
                                                         it.CheckNumber,
                                                         it.ReservationsWebsite,
                                                         it.ReservationsNo,
                                                         it.DetermineNo,
                                                         it.City,
                                                         it.HotelName,
                                                         it.HotelTel,
                                                         it.HotelAddress,
                                                         it.GuestName,
                                                         it.CheckInDate,
                                                         it.CheckOutDate,
                                                         it.CheckType,
                                                         it.RoomExplanation,
                                                         it.SingleRoomPrice,
                                                         it.SingleRoomCount,
                                                         it.DoubleRoomPrice,
                                                         it.DoubleRoomCount,
                                                         it.SuiteRoomPrice,
                                                         it.SuiteRoomCount,
                                                         it.OtherRoomPrice, 
                                                         it.OtherRoomCount,
                                                         it.CardPrice,
                                                         it.CardPriceCurrency,
                                                         it.Isoppay,
                                                         it.BreakfastPrice,
                                                         it.BreakfastCurrency,
                                                         it.GovernmentRent,
                                                         it.GovernmentRentCurrency,
                                                         it.CityTax,
                                                         it.CityTaxCurrency,
                                                         it.Remark,
                                                     })
                                                     .WhereColumns(it => it.Id)
                                                     .ExecuteCommandAsync();

                    if (hotelStatus < 0)
                    {
                        _result.Msg = "酒店预定信息修改失败!";
                        _sqlSugar.RollbackTran(); //回滚
                        return _result;

                    }

                    var hotelInfo = await _sqlSugar.Queryable<Grp_CreditCardPayment>()
                                                   .Where(it => it.DIId == _dto.DiId && it.CId == _dto.Id && it.CTable == 76 && it.IsDel == 0)
                                                   .FirstAsync();
                    if (hotelInfo == null) //ccp表 Add
                    {

                    }
                    else //ccp表 Edit
                    {
                        _CreditCardPayment.Id = hotelInfo.Id;

                        int creditCardStatus = await _sqlSugar.Updateable<Grp_CreditCardPayment>(_CreditCardPayment)
                                                    .UpdateColumns(it => new
                                                    {
                                                       it.CTDId,
                                                       it.PayDId,
                                                       it.IsPay,
                                                       it.ConsumptionPatterns,
                                                       it.ConsumptionDate,
                                                       it.PayMoney,
                                                       it.PaymentCurrency,
                                                       it.PayThenMoney,
                                                       it.DayRate,
                                                       it.RMBPrice,
                                                       it.BankNo,
                                                       it.CardholderName,
                                                       it.CompanyBankNo,
                                                       it.OtherBankName,
                                                       it.OtherSideNo,
                                                       it.OtherSideName,
                                                       it.Payee,
                                                       it.OrbitalPrivateTransfer,
                                                       it.Remark,
                                                    })
                                                    .WhereColumns(it => it.Id)
                                                    .ExecuteCommandAsync();
                        if (creditCardStatus < 0)
                        {
                            _result.Msg = "付款信息表修改失败!";
                            _sqlSugar.RollbackTran(); //回滚
                            return _result;
                        }
                    }

                    _result.Msg = "操作成功!";
                    _result.Code = 0;
                    _sqlSugar.CommitTran(); // 提交
                }
                else if (_dto.Id < 0) //不正确的Id
                {
                    _result.Msg = "请输入正确的数据Id!";
                }
            }
            else
            {
                _result.Msg = "请传入正确的PortType参数,1 Web 2 Android 3 IOS";
            }

            return _result;
        }

        /// <summary>
        /// 酒店预定
        /// Del
        /// </summary>
        /// <returns></returns>
        public async Task<Result> _Del(int id,int userId)
        {
            Grp_HotelReservations _HotelReservations = new Grp_HotelReservations()
            {
                Id = id,
                IsDel = 1,
                DeleteUserId = userId,
                DeleteTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
            };
            int hrStatus = await _sqlSugar.Updateable<Grp_HotelReservations>(_HotelReservations)
                                        .UpdateColumns(it => new
                                        {
                                            it.IsDel,
                                            it.DeleteUserId,
                                            it.DeleteTime,
                                        })
                                        .WhereColumns(it => it.Id)
                                        .ExecuteCommandAsync();
            if (hrStatus < 0)
            {
                _result.Msg = "操作失败!";
                return _result;
            }

            _result.Msg = "操作成功!";
            _result.Code = 0;

            return _result;
        }
    }
}