OA2021/DAL/HotelReservationsService.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Models;
using System.Data;
using System.Data.SqlClient;
using System.Data.Common;

namespace DAL
{
    /// <summary>
    /// 酒店预订数据访问类
    /// </summary>
    public class HotelReservationsService
    {
        /// <summary>
        /// 查询所有
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="param">可变参数数组</param>
        /// <returns>返回集合</returns>
        List<HotelReservations> excuteSql(string sql, params SqlParameter[] param)
        {
            return ServiceBase<HotelReservations>.excuteSql(new HotelReservations(), "HotelReservations", sql, CommandType.Text, param);
        }


        /// <summary>
        /// 获取单个对象
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="param">可变参数数组</param>
        /// <returns>返回空或者单个对象</returns>
        HotelReservations excuteType(string sql, params SqlParameter[] param)
        {
            //查询结果放入对象集合
            List<HotelReservations> hrList = excuteSql(sql, param);
            //判断集合是否为空
            if (hrList == null || hrList.Count == 0)
                //返回null
                return null;
            //返回单个对象
            return hrList[0];
        }


        /// <summary>
        /// 根据编号查询对象信息
        /// </summary>
        /// <param name="id">对象编号</param>
        /// <returns>返回空或者单个对象信息</returns>
        public HotelReservations GetHotelReservationsByID(int id)
        {
            //调用获取单个对象的方法
            return excuteType("select *,datediff(DD,checkindate,checkoutdate) as dayDiff from HotelReservations where Id = @id and isdel=0", new SqlParameter("@id", id));
        }

        /// <summary>
        /// 获得签证酒店信息       
        /// </summary>
        /// <param name="diid">团组编号</param>
        /// <param name="city">出访国家下的 城市</param>
        /// <returns></returns>
        public HotelReservations GetHotelReservationsByDiidAndCity(int diid,string city)
        {
            //调用获取单个对象的方法
            return excuteType("select * from HotelReservations where isdel=0 and Diid="+diid+" and  City='"+city+"'");
        }
        /// <summary>
        /// 根据编号查询对象天数
        /// </summary>
        /// <param name="id">对象编号</param>
        /// <returns>返回空或者单个对象信息</returns>
        public object GetHotelReservationsDayDiffByID(int id)
        {
            //调用获取单个对象的方法
            return SqlHelper.ExecuteScalar("select datediff(DD,checkindate,checkoutdate) as dayDiff from HotelReservations where Id = @id and isdel=0", CommandType.Text, new SqlParameter("@id", id));
        }

        /// <summary>
        /// 根据编号查询对象信息
        /// </summary>
        /// <param name="id">对象编号</param>
        /// <returns>返回空或者对象信息</returns>
        public List<HotelReservations> GetHotelReservationsByGuestName(string guestName)
        {
            //调用获取单个对象的方法
            return excuteSql("select * from HotelReservations where GuestName like '%"+ guestName + "%' and IsDel = 0");
        }

        /// <summary>
        /// 根据编号查询对象信息
        /// </summary>
        /// <param name="id">对象编号</param>
        /// <returns>返回空或者对象信息</returns>
        public List<HotelReservations> GetHotelReservationsByDIId(int DIId, string arrayUsersId)
        {
            //调用获取单个对象的方法
            return excuteSql("select * from HotelReservations where DIId = @DIId and IsDel = 0 and Operator in (" + arrayUsersId + ") order by CheckInDate asc", new SqlParameter("@DIId", DIId));
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="DIId"></param>
        /// <returns></returns>
        public List<HotelReservations> GetByDIId(int DIId)
        {
            //调用获取单个对象的方法
            return excuteSql("select * from HotelReservations where DIId = @DIId and IsDel = 0 and Operator!=0  order by CheckInDate asc", new SqlParameter("@DIId", DIId));
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="DIId"></param>
        /// <returns></returns>
        public List<HotelReservations> GetByDIIdUId(int DIId,int UId)
        {
            //调用获取单个对象的方法
            return excuteSql("select * from HotelReservations where DIId = @DIId and IsDel = 0 and Operator="+UId+"  order by CheckInDate asc", new SqlParameter("@DIId", DIId));
        }
        /// <summary>
        /// 根据编号查询对象信息
        /// </summary>
        /// <param name="id">对象编号</param>
        /// <returns>返回空或者对象信息</returns>
        public HotelReservations GetHotelReservationsByDIIdEntity(int DIId)
        {
            //调用获取单个对象的方法
            return excuteType("select * from HotelReservations where DIId = @DIId and IsDel = 0", new SqlParameter("@DIId", DIId));
        }

        /// <summary>
        /// 新增
        /// </summary>
        /// <param name="sdt">对象</param>
        public bool AddHotelReservations(HotelReservations hr, out int id)
        {
            string sql = "insert into HotelReservations values(@DIId,@GTId,@CheckNumber, @ReservationsWebsite,@ReservationsNo, @DetermineNo,@City," +
                " @HotelName, @HotelAddress,@HotelTel,@HotelFax, @GuestName,@CheckInDate,@CheckOutDate,@Budget,@BudgetCurrency,@SingleRoomCount," +
                "@SingleRoomPrice, @DoubleRoomCount,@DoubleRoomPrice, @SuiteRoomCount, @SuiteRoomPrice,@OtherRoomCount, @OtherRoomPrice, @Commission," +
                "@CommissionCurrency,@CommissionMark,@RoomExplanation, @Attachment,@Remark,@CardPrice,@IsCardPrice,@Operator,@OperatorDate,@IsDel," +
                "@breakfastPrice,@isoppay,@cboOne,@cboTwo,@cboThree,@cboFour,@PredictSingleRoom,@PredictDoubleRoom,@PredictSuiteRoom,@PredictOtherRoom," +
                "@CityTax,@GovernmentRent,@CheckType);SELECT @@IDENTITY";

            SqlParameter[] parameter = new SqlParameter[]{
                new SqlParameter("@DIId",hr.DIId),
                new SqlParameter("@GTId",hr.GTId),
                new SqlParameter("@CheckNumber",hr.CheckNumber),
                new SqlParameter("@ReservationsWebsite",hr.ReservationsWebsite),
                new SqlParameter("@ReservationsNo",hr.ReservationsNo),
                new SqlParameter("@DetermineNo",hr.DetermineNo),
                new SqlParameter("@City",hr.City),
                new SqlParameter("@HotelName",hr.HotelName),
                new SqlParameter("@HotelAddress",hr.HotelAddress),
                new SqlParameter("@HotelTel",hr.HotelTel),
                new SqlParameter("@HotelFax",hr.HotelFax),
                new SqlParameter("@GuestName",hr.GuestName),
                new SqlParameter("@CheckInDate",hr.CheckInDate),
                new SqlParameter("@CheckOutDate",hr.CheckOutDate),
                new SqlParameter("@Budget",hr.Budget),
                new SqlParameter("@BudgetCurrency",hr.BudgetCurrency),
                new SqlParameter("@SingleRoomCount",hr.SingleRoomCount),
                new SqlParameter("@SingleRoomPrice",hr.SingleRoomPrice),
                new SqlParameter("@DoubleRoomCount",hr.DoubleRoomCount),
                new SqlParameter("@DoubleRoomPrice",hr.DoubleRoomPrice),
                new SqlParameter("@SuiteRoomCount",hr.SuiteRoomCount),
                new SqlParameter("@SuiteRoomPrice",hr.SuiteRoomPrice),
                new SqlParameter("@OtherRoomCount",hr.OtherRoomCount),
                new SqlParameter("@OtherRoomPrice",hr.OtherRoomPrice),
                new SqlParameter("@Commission",hr.Commission),
                new SqlParameter("@CommissionCurrency",hr.CommissionCurrency),
                new SqlParameter("@CommissionMark",hr.CommissionMark),
                new SqlParameter("@RoomExplanation",hr.RoomExplanation),
                new SqlParameter("@Attachment",hr.Attachment),
                new SqlParameter("@Remark",hr.Remark),
                new SqlParameter("@CardPrice",hr.CardPrice),
                new SqlParameter("@IsCardPrice",hr.IsCardPrice),
                new SqlParameter("@Operator",hr.Operators),
                new SqlParameter("@OperatorDate",hr.OperatorsDate),
                new SqlParameter("@IsDel",hr.IsDel),
                new SqlParameter("@breakfastPrice",hr.BreakfastPrice),
                new SqlParameter("@isoppay",hr.Isoppay),
                //-------
                new SqlParameter("@cboOne",hr.cboOne),
                new SqlParameter("@cboTwo",hr.CboTwo),
                new SqlParameter("@cboThree",hr.CboThree),
                new SqlParameter("@cboFour",hr.CboFour),

                new SqlParameter("@PredictSingleRoom",hr.PredictSingleRoom ),
                new SqlParameter("@PredictDoubleRoom",hr.PredictDoubleRoom ),
                new SqlParameter("@PredictSuiteRoom",hr.PredictSuiteRoom ),
                new SqlParameter("@PredictOtherRoom",hr.PredictOtherRoom ),
                new SqlParameter("@CityTax",hr.CityTax),
                new SqlParameter("@GovernmentRent",hr.GovernmentRent),
                new SqlParameter("@CheckType",hr.CheckType)
            };

            int obj = Convert.ToInt32(SqlHelper.ExecuteScalar(sql, CommandType.Text, parameter));

            if (obj > 0)
            {
                id = obj;
                return true;
            }

            id = 0;
            return false;
        }

        /// <summary>
        /// 编辑
        /// </summary>
        /// <param name="sdt"></param>
        /// <returns></returns>
        public bool EditHotelReservations(HotelReservations hr)
        {
            string sql = "update HotelReservations set DIId = @DIId,GTId = @GTId,CheckNumber = @CheckNumber,ReservationsWebsite = @ReservationsWebsite,ReservationsNo" +
                " = @ReservationsNo,DetermineNo = @DetermineNo ,City = @City,HotelName = @HotelName,HotelAddress = @HotelAddress,HotelTel = @HotelTel,HotelFax = @HotelFax," +
                "GuestName = @GuestName,CheckInDate = @CheckInDate ,CheckOutDate = @CheckOutDate,Budget = @Budget,BudgetCurrency =@BudgetCurrency,SingleRoomCount = @SingleRoomCount," +
                "SingleRoomPrice = @SingleRoomPrice,DoubleRoomCount = @DoubleRoomCount,DoubleRoomPrice = @DoubleRoomPrice,SuiteRoomCount = @SuiteRoomCount," +
                "SuiteRoomPrice = @SuiteRoomPrice ,OtherRoomCount = @OtherRoomCount,OtherRoomPrice = @OtherRoomPrice,Commission = @Commission,CommissionCurrency = @CommissionCurrency," +
                "CommissionMark = @CommissionMark,RoomExplanation = @RoomExplanation,Attachment = @Attachment,Remark = @Remark,CardPrice=@CardPrice,Operator = @Operator," +
                "OperatorDate = @OperatorDate,breakfastPrice = @breakfastPrice,isoppay = @isoppay,cboOne=@cboOne,cboTwo=@cboTwo,cboThree=@cboThree,cboFour=@cboFour," +
                "PredictSingleRoom=@PredictSingleRoom,PredictDoubleRoom=@PredictDoubleRoom,PredictSuiteRoom=@PredictSuiteRoom,PredictOtherRoom=@PredictOtherRoom," +
                "CityTax=@CityTax,GovernmentRent=@GovernmentRent,CheckType=@CheckType where Id = @Id;";

            SqlParameter[] parameter = new SqlParameter[]{
                new SqlParameter("@DIId",hr.DIId),
                new SqlParameter("@GTId",hr.GTId),
                new SqlParameter("@CheckNumber",hr.CheckNumber),
                new SqlParameter("@ReservationsWebsite",hr.ReservationsWebsite),
                new SqlParameter("@ReservationsNo",hr.ReservationsNo),
                new SqlParameter("@DetermineNo",hr.DetermineNo),
                new SqlParameter("@City",hr.City),
                new SqlParameter("@HotelName",hr.HotelName),
                new SqlParameter("@HotelAddress",hr.HotelAddress),
                new SqlParameter("@HotelTel",hr.HotelTel),
                new SqlParameter("@HotelFax",hr.HotelFax),
                new SqlParameter("@GuestName",hr.GuestName),
                new SqlParameter("@CheckInDate",hr.CheckInDate),
                new SqlParameter("@CheckOutDate",hr.CheckOutDate),
                new SqlParameter("@Budget",hr.Budget),
                new SqlParameter("@BudgetCurrency",hr.BudgetCurrency),
                new SqlParameter("@SingleRoomCount",hr.SingleRoomCount),
                new SqlParameter("@SingleRoomPrice",hr.SingleRoomPrice),
                new SqlParameter("@DoubleRoomCount",hr.DoubleRoomCount),
                new SqlParameter("@DoubleRoomPrice",hr.DoubleRoomPrice),
                new SqlParameter("@SuiteRoomCount",hr.SuiteRoomCount),
                new SqlParameter("@SuiteRoomPrice",hr.SuiteRoomPrice),
                new SqlParameter("@OtherRoomCount",hr.OtherRoomCount),
                new SqlParameter("@OtherRoomPrice",hr.OtherRoomPrice),
                new SqlParameter("@Commission",hr.Commission),
                new SqlParameter("@CommissionCurrency",hr.CommissionCurrency),
                new SqlParameter("@CommissionMark",hr.CommissionMark),
                new SqlParameter("@RoomExplanation",hr.RoomExplanation),
                new SqlParameter("@Attachment",hr.Attachment),
                new SqlParameter("@Remark",hr.Remark),
                new SqlParameter("@CardPrice",hr.CardPrice),
                new SqlParameter("@Operator",hr.Operators),
                new SqlParameter("@OperatorDate",hr.OperatorsDate),
                new SqlParameter("@breakfastPrice",hr.BreakfastPrice),
                new SqlParameter("@isoppay",hr.Isoppay),
                //-------
                new SqlParameter("@cboOne",hr.CboOne),
                new SqlParameter("@cboTwo",hr.CboTwo),
                new SqlParameter("@cboThree",hr.CboThree),
                new SqlParameter("@cboFour",hr.CboFour),
                new SqlParameter("@Id",hr.Id),
                new SqlParameter("@PredictSingleRoom",hr.PredictSingleRoom ),
                new SqlParameter("@PredictDoubleRoom",hr.PredictDoubleRoom ),
                new SqlParameter("@PredictSuiteRoom",hr.PredictSuiteRoom ),
                new SqlParameter("@PredictOtherRoom",hr.PredictOtherRoom ),
                new SqlParameter("@CityTax",hr.CityTax),
                new SqlParameter("@GovernmentRent",hr.GovernmentRent),
                new SqlParameter("@CheckType",hr.CheckType)

            };

            if (SqlHelper.ExecuteNonQuery(sql, CommandType.Text, parameter) > 0)
                return true;
            return false;
        }

        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool DelHotelReservations(int id)
        {
            if (SqlHelper.ExecuteNonQuery("update HotelReservations set IsDel = 1 where Id = @Id", CommandType.Text, new SqlParameter("@Id", id)) > 0)
                return true;
            return false;
        }
        /// <summary>
        /// LiuChengYi 2014/4/16
        /// 用于出访国家的数据统计
        /// </summary>
        /// <returns></returns>
        public DataTable ReportHotelReservationsInfo()
        {
            return SqlHelper.TransferProcedure("HotelReservationsList", CommandType.StoredProcedure);
        }

        /// <summary>
        /// LiuChengYi 2014/05/09
        /// 用作微信平台上对入住酒店的查询
        /// </summary>
        /// <param name="tourCode"></param>
        /// <returns></returns>
        public DataTable QueryCheckHotel(string tourCode)
        {
            SqlParameter[] pars = 
              {
                   new SqlParameter("@tourCode",tourCode)
              };
            return SqlHelper.TransferProcedure("QueryCheckHotel_Weixin", CommandType.StoredProcedure, pars);
        }
        /// <summary>
        /// LiuChengYi 2014/4/16
        /// 用于出访国家的数据统计
        /// </summary>
        /// <returns></returns>
        public DataTable ReportHotelReservationsInfo(string year)
        {
            SqlParameter[] pars = 
            {
               new SqlParameter("@year",year)
            };
            return SqlHelper.TransferProcedure("HotelReservationsList", CommandType.StoredProcedure, pars);
        }
        
        
        /// <summary>
        ///雷怡 2021-08-16 14:23
        ///确认信用卡是否刷卡成功 标识
        /// </summary>
        /// <param name="IsCardPrice"></param>
        /// <param name="Id"></param>
        /// <returns></returns>
        public bool GetIsCardPrice(int IsCardPrice,int Id) 
        {
            string sql = " update HotelReservations set  IsCardPrice=@IsCardPrice where Id=@Id";

            SqlParameter[] parameter = new SqlParameter[]{
                new SqlParameter("@IsCardPrice",IsCardPrice),
                new SqlParameter("@Id",Id)
            };

            if (SqlHelper.ExecuteNonQuery(sql, CommandType.Text, parameter) > 0)
                return true;
            return false;

        }


      
    }
}