using AutoMapper; using OASystem.Domain; using OASystem.Domain.Dtos.Groups; using OASystem.Domain.Entities.Groups; using OASystem.Domain.ViewModels.Groups; using SqlSugar; using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; namespace OASystem.Infrastructure.Repositories.Groups { public class HotelPriceRepository : BaseRepository { private readonly IMapper _mapper; public HotelPriceRepository(SqlSugarClient sqlSugar, IMapper mapper) : base(sqlSugar) { this._mapper = mapper; } public async Task HotelReservationsByDiId(HotelReservationsByDiIdDto dto) { Result result = new Result() { Code = -2, Msg = "未知错误" }; try { string UserId = ""; List gtaUIdList = _sqlSugar.Queryable().Where(a => a.DIId == dto.DiId && a.IsDel == 0 && a.CTId == 76).ToList(); foreach (Grp_GroupsTaskAssignment gta in gtaUIdList) UserId += gta.UId + ","; if (!string.IsNullOrWhiteSpace(UserId)) { UserId = UserId.Substring(0, UserId.Length - 1); } else { UserId = "0"; } string sqlWhere = string.Format(@"Where h.DiId={0} and h.IsDel={1} And h.CreateUserId in ({2})", dto.DiId, 0,UserId); int startIndex = (dto.PageIndex - 1) * dto.PageSize + 1; int endIndex = startIndex + dto.PageSize - 1; if (dto.PortType == 1) { string sql = string.Format(@"select h.Id,s1.Name as GuestType,h.ReservationsNo,h.HotelName,h.CheckInDate, h.CheckOutDate,c.PayMoney,s.Name as PaymentCurrency,u.CnName as CreateUserName,c.IsAuditGM,h.CreateTime,h.Attachment From Grp_HotelReservations h Join Grp_CreditCardPayment c on h.Id=c.CId and c.CTable=76 and c.IsDel=0 left Join Sys_SetData s on c.PaymentCurrency=s.Id and s.IsDel=0 left Join Sys_SetData s1 on h.GTId=s1.Id and s1.IsDel=0 left Join Sys_Users u on u.Id=h.CreateUserId and u.isdel=0 {0}", sqlWhere); List hotelDataList = _sqlSugar.SqlQueryable(sql).ToList(); foreach (var item in hotelDataList) { if (item.IsAuditGM == 0) item.IsAuditGMStr = "未审核"; else if (item.IsAuditGM == 1) item.IsAuditGMStr = "已通过"; else if (item.IsAuditGM == 2) item.IsAuditGMStr = "未通过"; else if (item.IsAuditGM == 3) item.IsAuditGMStr = "自动审核"; } return result = new Result() { Code = 0, Msg = "查询成功!", Data = hotelDataList }; } else if (dto.PortType == 2 || dto.PortType == 3) { string sql = string.Format(@"Select * From ( Select row_number() over (order by h.Id Desc) as RowNumber,h.Id,s1.Name as GuestType,h.ReservationsNo,h.HotelName,h.CheckInDate, h.CheckOutDate,c.PayMoney,s.Name as PaymentCurrency,u.CnName as CreateUserName,c.IsAuditGM,h.CreateTime,h.Attachment From Grp_HotelReservations h Join Grp_CreditCardPayment c on h.Id=c.CId and c.CTable=76 and c.IsDel=0 left Join Sys_SetData s on c.PaymentCurrency=s.Id and s.IsDel=0 left Join Sys_SetData s1 on h.GTId=s1.Id and s1.IsDel=0 left Join Sys_Users u on u.Id=h.CreateUserId and u.isdel=0 {0} ) temp Where RowNumber Between {1} and {2}", sqlWhere, startIndex, endIndex); List hotelDataList = _sqlSugar.SqlQueryable(sql).ToList(); foreach (var item in hotelDataList) { if (item.IsAuditGM == 0) item.IsAuditGMStr = "未审核"; else if (item.IsAuditGM == 1) item.IsAuditGMStr = "已通过"; else if (item.IsAuditGM == 2) item.IsAuditGMStr = "未通过"; else if (item.IsAuditGM == 3) item.IsAuditGMStr = "自动审核"; } string CountSql = string.Format(@"Select COUNT(1) as Count From ( Select h.Id,s1.Name as GuestType,h.ReservationsNo,h.HotelName,h.CheckInDate, h.CheckOutDate,c.PayMoney,s.Name as PaymentCurrency,u.CnName as CreateUserName,c.IsAuditGM,h.CreateTime,h.Attachment From Grp_HotelReservations h Join Grp_CreditCardPayment c on h.Id=c.CId and c.CTable=76 and c.IsDel=0 left Join Sys_SetData s on c.PaymentCurrency=s.Id and s.IsDel=0 left Join Sys_SetData s1 on h.GTId=s1.Id and s1.IsDel=0 left Join Sys_Users u on u.Id=h.CreateUserId and u.isdel=0 {0} ) temp", sqlWhere); DataCount dataCount = _sqlSugar.SqlQueryable(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 rst = new ListViewBase(); rst.DataList = hotelDataList; rst.DataCount = count; rst.CurrPageIndex = dto.PageIndex; rst.CurrPageSize = dto.PageSize; return result = new Result() { Code = 0, Msg = "查询成功!", Data = rst }; } else { return result = new Result() { Code = -2, Msg = "请传入PortType参数,1 Web 2 Android 3 IOS" }; } } catch (Exception ex) { return result = new Result() { Code = -2, Msg = "未知错误" }; throw; } } public async Task HotelReservationsById(HotelReservationsByIdDto dto) { Result result = new Result() { Code = -2, Msg = "未知错误" }; try { Grp_HotelReservations hotelReservationsById = _sqlSugar.Queryable().First(a => a.IsDel == 0 && a.Id == dto.Id); HotelReservationsByIdView _hotelReservations = _mapper.Map(hotelReservationsById); Grp_CreditCardPayment _CreditCardPayment = _sqlSugar.Queryable().First(a => a.CId == dto.Id && a.IsDel == 0 && a.CTable == 76); var data = new { hotelReservations = _hotelReservations, creditCardPayment = _CreditCardPayment, }; return result = new Result() { Code = 0, Msg = "查询成功",Data= data }; } catch (Exception ex) { return result = new Result() { Code = -2, Msg = "未知错误" }; throw; } } public async Task HotelReservationsInitialize(HotelReservationsDto dto) { Result result = new Result() { Code = -2, Msg = "未知错误" }; try { List grp_GroupsTaskAssignment = Query(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 Delegations = _sqlSugar.SqlQueryable(sql).ToList(); List grp_Delegations = _mapper.Map>(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 GuestType = _sqlSugar.Queryable().Where(a=>a.IsDel==0 && a.STid==11).ToList(); List _GuestType = _mapper.Map>(GuestType); //支付方式 List Payment = _sqlSugar.Queryable().Where(a => a.STid == 14 && a.IsDel == 0).ToList(); List _Payment = _mapper.Map>(Payment); //币种 List CurrencyList = _sqlSugar.Queryable().Where(a => a.STid == 66 && a.IsDel == 0).ToList(); List _CurrencyList = _mapper.Map>(CurrencyList); //卡类型 List BankCard = _sqlSugar.Queryable().Where(a => a.STid == 15 && a.IsDel == 0).ToList(); List _BankCard = _mapper.Map>(BankCard); //预订网站 List BookingWebsite = _sqlSugar.Queryable().Where(a => a.STid == 12 && a.IsDel == 0).ToList(); List _BookingWebsite = _mapper.Map>(BookingWebsite); var data = new { Delegations = grp_Delegations, GuestType = _GuestType, Payment = _Payment, CurrencyList = _CurrencyList, BankCard = _BankCard, BookingWebsite = _BookingWebsite, }; return result = new Result() { Code = 0, Msg = "查询成功",Data=data }; } catch (Exception ex) { return result = new Result() { Code = -2, Msg = "未知错误" }; throw; } } public Task OpHotelReservations(OpHotelReservationsData dto) { throw new NotImplementedException(); } } }