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 countrys = await _sqlSugar.Queryable<Grp_NationalTravelFee>()
.Where(x => x.IsDel == 0 )
.Select(x => new {
x.Id,
x.Country,
x.City
})
.ToListAsync();
var data = new
{
GuestType = _GuestType, //客人分类
Payment = _Payment,
CurrencyList = _teamRateView,
BankCard = _BankCard,
BookingWebsite = _BookingWebsite,
CheckPerson = _CheckPerson,
CheckVolumeNo = _CheckVolumeNo,
GuestName = guestNames,
Countrys = countrys
};
_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.AreaId,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.HrId = _HotelReservations.Id;
//2024-12-05 10:18 更改代付逻辑：是否由地接或其他人代付: 是 ： ispay = 0 否 ： ispay = 1
if (it.PayDId == 72 || it.IsOppay == 0) it.IsPay = 1;
else it.IsPay = 0;
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.PayMoney = _dto.CardPrice;
_CreditCardPayment.PaymentCurrency = _dto.CardPriceCurrency;
decimal ccpRate = 1.0000M;
if (_dto.CardPriceCurrency != 836) ccpRate = _dto.Contents.Find(x => x.Currency == _dto.CardPriceCurrency)?.Rate ?? 1.0000M;
_CreditCardPayment.DayRate = ccpRate;
_CreditCardPayment.PayPercentage = 100.00M;
_CreditCardPayment.PayThenMoney = _CreditCardPayment.PayMoney;
_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.AreaId,
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(_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 };
}
}
}