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 Org.BouncyCastle.Asn1.Ocsp;
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 JsonView _view;
        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 = MsgTips.Fail };
            _view = new JsonView() { Code = 400, Msg = MsgTips.Fail };
            _teamRateRep = teamRateRep;
            _tourClientListRep = tourClientListRep;
            _hotelDataRep = hotelDataRep;
        }

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

            string sql = string.Format(@"Select row_number() over(order by hr.CheckInDate asc) 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();

                _view.Code = 200;
                _view.Data = hotelFeeData;
                _view.Msg = MsgTips.Succeed;
            }
            else
            {
                _view.Msg = MsgTips.Port;
            }

            return _view;
        }

        /// <summary>
        /// 酒店预定
        /// basicsData Init
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        public async Task<JsonView> _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
                };

                _view.Code = 200;
                _view.Data = data;
                _view.Msg = MsgTips.Succeed;

            }
            else _view.Msg = MsgTips.Port;

            return _view;
        }

        /// <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<JsonView> _Details(int portType, int id)
        {
            if (id < 0 )
            {
                _view.Msg = string.Format(@"请输入正确的Id!");
                return _view;
            }

            string sql = string.Format(@"Select hr.Id,hr.DiId,hr.GTId,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.CardPriceCurrency,
                                         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();
                string contentSql = string.Format($@"Select 
											            Id,
											            DiId,
											            HrId,
											            PriceType,
											            Price,
											            Currency,
											            Rate,
											            IsOppay,
											            PayDId,
											            ConsumptionPatterns,
											            ConsumptionDate,
											            CTDId,
											            BankNo,
											            CardholderName,
											            CompanyBankNo,
											            OtherBankName,
											            OtherSideNo,
											            OtherSideName,
											            IsPay,
											            Payee,
											            OrbitalPrivateTransfer
										            From Grp_HotelReservationsContent With(NoLock)
										            Where IsDel = 0 And HrId = {info.Id}");
                info.Contents = await _sqlSugar.SqlQueryable<HotelReservationsContnetDetailsView>(contentSql).ToListAsync();

                _view.Code = 200;
                _view.Data = info;
                _view.Msg = MsgTips.Succeed;
            }
            else _view.Msg = MsgTips.Port;
            return _view;
        }

        /// <summary>
        /// 酒店预定
        /// Add Or Edit
        /// </summary>
        /// <returns></returns>
        public async Task<JsonView> _AddOrEdit(HotelReservationsAddOrEditDto _dto)
        {
            var _view = new JsonView() { Code = 400, Msg = MsgTips.Fail };
            int portType = _dto.PortType;
            if (portType == 1 || portType == 2 || portType == 3)
            {
                Grp_HotelReservations _HotelReservations = new Grp_HotelReservations();
                List<Grp_HotelReservationsContent> _HotelReservationsContents = new List<Grp_HotelReservationsContent>();

                Grp_CreditCardPayment _CreditCardPayment = new Grp_CreditCardPayment();

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

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

                #region 子表币种汇率处理
                //List<Sys_SetData> currData = await _sqlSugar.Queryable<Sys_SetData>().Where(a => a.IsDel == 0 && a.STid == 66).ToListAsync();

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

                //List<TeamRateDescView> teamRateDescViews = new List<TeamRateDescView>();
                //teamRateDescViews = teamRateModels_hotel.TeamRates;
                //if (teamRateDescViews.Count < 0)
                //{
                //    _view.Msg = "该团未设置酒店预订模块相关币种汇率!";
                //    return _view;
                //}

                #endregion

                _HotelReservationsContents.ForEach(it => {
                    it.CreateUserId = _dto.UserId;
                    it.DiId = _dto.DiId;
                    it.IsPay = 0;
                    it.HrId = _HotelReservations.Id;

                    if (!string.IsNullOrEmpty(it.ConsumptionDate))
                    {
                        if (DateTime.TryParse(it.ConsumptionDate, out DateTime dt))
                        { 
                            it.ConsumptionDate = dt.ToString("yyyy-MM-dd");
                        }
                    }


                    //var currCode = currData.Find(x => x.Id == it.Currency)?.Name;
                    //decimal? currRate = teamRateDescViews.Find(x => x.CurrencyCode == currCode)?.Rate;
                    //it.Rate = currRate ?? 0.00M;
                });
                _HotelReservationsContents = _HotelReservationsContents.OrderBy(x => x.PriceType).ToList();


                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 表参数

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

                _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)
                    {
                        _sqlSugar.RollbackTran(); //回滚
                        _view.Msg = $"酒店预定信息添加失败!";
                        return _view;
                    }

                    _HotelReservationsContents.ForEach(it => {
                        it.HrId = hotelId;
                    });

                    int hotelSubId = await _sqlSugar.Insertable<Grp_HotelReservationsContent>(_HotelReservationsContents).ExecuteReturnIdentityAsync();
                    if (hotelSubId < 0) 
                    {
                        _sqlSugar.RollbackTran(); //回滚
                        _view.Msg = "酒店预定详细信息添加失败";
                        return _view;
                    }


                    _CreditCardPayment.CId = hotelId;
                    _CreditCardPayment.CTable = 76; //酒店预定模块

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

                    #region 酒店基础数据 添加

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

                    #endregion

                    _view.Msg = "操作成功!";
                    _view.Code = 200;
                    _view.Data = new { ccpId = ccpId, sign = 1, hotelId = hotelId };
                    _sqlSugar.CommitTran(); // 提交

                    return _view;
                }
                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.Remark,
                                                     })
                                                     .WhereColumns(it => it.Id)
                                                     .ExecuteCommandAsync();

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

                    var hotelSubStatus = false;
                    var addHotelSubData = _HotelReservationsContents.Where(it => it.Id == 0).ToList();
                    var updateHotelSubData = _HotelReservationsContents.Where(it => it.Id != 0).ToList();

                    if (addHotelSubData.Count > 0)
                    {
                        int hotelSubAdd = await _sqlSugar.Insertable<Grp_HotelReservationsContent>(addHotelSubData).ExecuteCommandAsync();
                        if (hotelSubAdd > 0) hotelSubStatus = true;

                    }
                    if (updateHotelSubData.Count > 0)
                    {
                        int hotelSubUpdate = await _sqlSugar.Updateable<Grp_HotelReservationsContent>(updateHotelSubData)
                                                         .IgnoreColumns(it => new {
                                                             it.DiId,
                                                             it.HrId,
                                                             it.CreateUserId,
                                                             it.CreateTime,
                                                             it.DeleteUserId,
                                                             it.DeleteTime
                                                         })
                                                         .WhereColumns(it => it.Id)
                                                         .ExecuteCommandAsync();
                        if (hotelSubUpdate > 0) hotelSubStatus = true;
                    }
                    
                    if (!hotelSubStatus)
                    {
                        _view.Msg = "酒店预定子表信息修改失败!";
                        _sqlSugar.RollbackTran(); //回滚
                        return _view;
                    }

                    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)
                        {
                            _view.Msg = "付款信息表修改失败!";
                            _sqlSugar.RollbackTran(); //回滚
                            return _view;
                        }
                    }

                    _view.Msg = "操作成功!";
                    _view.Code = 200;
                    _view.Data = new { ccpId = _CreditCardPayment.Id, sign = 2 ,hotelId = _HotelReservations.Id };
                    _sqlSugar.CommitTran(); // 提交
                    return _view;
                }
                else if (_dto.Id < 0) _view.Msg = MsgTips.Id;

            }
            else _view.Msg = MsgTips.Port;

            return _view;
        }

        /// <summary>
        /// 酒店预定
        /// Del
        /// </summary>
        /// <returns></returns>
        public async Task<JsonView> _Del(int id,int userId)
        {
            _sqlSugar.BeginTran();
            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)
            {
                _sqlSugar.RollbackTran();
                return new JsonView() { Code = 400,Msg = MsgTips.Fail };
            }

            int hrSubStatus = await _sqlSugar.Updateable<Grp_HotelReservationsContent>()
                                             .SetColumns(it => it.IsDel == 1)
                                             .SetColumns(it => it.DeleteUserId == userId)
                                             .SetColumns(it => it.DeleteTime == DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"))
                                             .Where(it => it.HrId == id).ExecuteCommandAsync();

            var ccpInfp = new Grp_CreditCardPayment() {
                CId = id,
                CTable = 76,
                IsDel = 1,
                DeleteUserId = userId,
                DeleteTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
            };
            int hrCCPStatus = await _sqlSugar.Updateable<Grp_CreditCardPayment>(ccpInfp)
                                        .UpdateColumns(it => new
                                        {
                                            it.IsDel,
                                            it.DeleteUserId,
                                            it.DeleteTime,
                                        })
                                        .WhereColumns(it => new { it.CTable, it.CId })
                                        .ExecuteCommandAsync();

            _sqlSugar.CommitTran();
            return new JsonView() { Code = 200, Msg = MsgTips.Succeed };
        }
    }
}