using AutoMapper;
using MathNet.Numerics.Statistics.Mcmc;
using MySqlX.XDevAPI.Common;
using OASystem.Domain;
using OASystem.Domain.Dtos.Financial;
using OASystem.Domain.Dtos.Groups;
using OASystem.Domain.Entities.Financial;
using OASystem.Domain.Entities.Groups;
using OASystem.Domain.ViewModels.Financial;
using OASystem.Domain.ViewModels.Groups;
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 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;
public HotelPriceRepository(SqlSugarClient sqlSugar, IMapper mapper, TeamRateRepository teamRateRep, TourClientListRepository tourClientListRep)
:base(sqlSugar)
{
this._mapper = mapper;
_result = new Result() { Code = -1, Msg = "操作失败!" };
_teamRateRep = teamRateRep;
_tourClientListRep = tourClientListRep;
}
#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;
}
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 CTable = await _sqlSugar.Updateable<Grp_CreditCardPayment>().Where(a => a.CId == hotelPrice.Id && a.CTable == 76).SetColumns(a => new Grp_CreditCardPayment
{
PayDId = dto.PayDId,
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
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)
{
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 表参数
_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 <= 0)
{
_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;
}
_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.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;
}
}
}