private AccomodationOrderModel GetKingAvailableRoom(DateTime checkin, DateTime checkout, DataTable roomStatus, AccomodationOrderModel order) { TimeSpan ts = checkout - checkin; int staydays = ts.Days; var result = from myRom in roomStatus.AsEnumerable() where myRom.Field <int>("RoomType") == 0 && myRom.Field <bool>("Available") == true group myRom by myRom.Field <System.DateTime>("StatusDate") into g select new { aa = g.Count(), g.Key }; if (result.Count() >= staydays) { // List<AvailableRoom> ars = result.ToList(); //var a = result.ToList(); //var min = result.ToList(); List <int> KingRoomCount = new List <int>(); foreach (var item in result) { KingRoomCount.Add(item.aa); } AccomodationOrderListModel orderListKing = new AccomodationOrderListModel(); orderListKing.RoomType = GlobalVariable.RoomType.King; orderListKing.AvailableRoomNumber = KingRoomCount.Min(); order.AccomodationOrderList.Add(orderListKing); } return(order); }
public AccomodationOrderModel CalcRoomRate(AccomodationOrderModel order) { List <AccomodationDiscountPolicy> allDiscountRate = this.GetAllDiscountRate(); List <AccomodationPolicy> roomPolicy = this.GetRoomPolicy(Convert.ToDateTime(order.CheckInDate), Convert.ToDateTime(order.CheckOutDate), allDiscountRate); DateTime startDate = Convert.ToDateTime(order.CheckInDate); DateTime endDate = Convert.ToDateTime(order.CheckOutDate); // TimeSpan ts = startDate.Subtract(endDate).Duration(); TimeSpan ts = endDate - startDate; int totalstaydays = ts.Days; //calc basic Rate for (DateTime dt = startDate; dt < endDate; dt = dt.AddDays(1)) { int weekDays = (int)dt.DayOfWeek; // string weekDays = dt.ToString(); roomPolicy = this.CalcBasicRate(roomPolicy, weekDays.ToString(), GlobalVariable.RoomType.King); roomPolicy = this.CalcBasicRate(roomPolicy, weekDays.ToString(), GlobalVariable.RoomType.Queen); } //calc discount Rate roomPolicy = this.CalcDiscountRate(roomPolicy, totalstaydays); //set order data: price/totprice/discount: for (int i = 0; i < order.AccomodationOrderList.Count; i++) { order.AccomodationOrderList[i] = this.CalcFinalRate(order.AccomodationOrderList[i], roomPolicy); } return(order); }
public AccomodationOrderModel SaveOrderResult(AccomodationOrderModel order) { List <string> sqls = new List <string>(); List <SqlParameter[]> cmdParms = new List <SqlParameter[]>(); StringBuilder str; SqlParameter[] paras; #region Order str = new StringBuilder(); str.AppendLine("update [Order] set"); str.AppendLine(" OrderStatus=@OrderStatus "); str.AppendLine(" ,OrderDesc=@OrderDesc "); str.AppendLine(" ,UpdateTime = getdate()"); str.AppendLine(" where orderID = @OrderID "); paras = new SqlParameter[3]; paras[0] = new SqlParameter("@OrderStatus", order.ResultStatus); paras[1] = new SqlParameter("@OrderDesc", Common.VariableConvert.ConvertStringToDBValue(order.ResultMsg)); paras[2] = new SqlParameter("@OrderID", order.OrderID); sqls.Add(str.ToString()); cmdParms.Add(paras); #endregion #region PaymentTransaction if (order.Response != null) { str = new StringBuilder(); str.AppendLine("update PaymentTransaction set"); str.AppendLine(" ResponseID=@ResponseID "); str.AppendLine(" ,ResponseStatus=@ResponseStatus "); str.AppendLine(" ,ResponseMsg=@ResponseMsg "); str.AppendLine(" ,UpdateTime = getdate()"); str.AppendLine(" where RequestID = @RequestID "); paras = new SqlParameter[4]; paras[0] = new SqlParameter("@ResponseID", Common.VariableConvert.ConvertStringToDBValue(order.Response.ResponseID)); paras[1] = new SqlParameter("@ResponseStatus", order.Response.ResultCode); paras[2] = new SqlParameter("@ResponseMsg", Common.VariableConvert.ConvertStringToDBValue(order.Response.ResultMessage)); paras[3] = new SqlParameter("@RequestID", order.RequestID); sqls.Add(str.ToString()); cmdParms.Add(paras); } #endregion SqlAccess mySqlAccess = new SqlAccess(); mySqlAccess.ExecuteNonQuerys(sqls, cmdParms); return(order); }
public AccomodationOrderModel UpdateRoomStatus(AccomodationOrderModel order) { StringBuilder select = new StringBuilder(); select.AppendLine("SELECT "); select.AppendLine(" RoomStatusID, StatusDate, RoomType, Available"); select.AppendLine(" FROM RoomStatus WITH(NOLOCK)"); select.AppendLine(" WHERE 1=1"); select.AppendLine(" and StatusDate >= @CheckinDate"); select.AppendLine(" and StatusDate < @CheckoutDate"); select.AppendLine(" order by StatusDate asc"); SqlParameter[] paras = new SqlParameter[2]; paras[0] = new SqlParameter("@CheckinDate", Convert.ToDateTime(order.CheckInDate)); paras[1] = new SqlParameter("@CheckoutDate", Convert.ToDateTime(order.CheckOutDate)); SqlAccess mySqlAccess = new SqlAccess(); DataSet ds = mySqlAccess.ExecuteAdapter(select.ToString(), paras); List <string> sqlUpdates = new List <string>(); List <SqlParameter[]> cmdParms = new List <SqlParameter[]>(); foreach (AccomodationOrderListModel orderList in order.AccomodationOrderList) { if (orderList.OrderRoomNumber > 0) { for (DateTime dt = Convert.ToDateTime(order.CheckInDate); dt < Convert.ToDateTime(order.CheckOutDate); dt = dt.AddDays(1)) { string sql = string.Empty; SqlParameter[] param = new SqlParameter[2]; if (orderList.RoomType == GlobalVariable.RoomType.King) { this.GetUpdateSql(dt, GlobalVariable.RoomType.King, ref sql, ref param); sqlUpdates.Add(sql); cmdParms.Add(param); } else if (orderList.RoomType == GlobalVariable.RoomType.Queen) { this.GetUpdateRoomStatusForQueen(orderList.RoomType, dt, ds, orderList.OrderRoomNumber, ref sqlUpdates, ref cmdParms); } } } } mySqlAccess.ExecuteNonQuerys(sqlUpdates, cmdParms); return(order); }
/// <summary> /// online /// </summary> /// <param name="model"></param> public AccomodationOrderModel GetAvailableRoom(AccomodationOrderModel model) { StringBuilder sql = new StringBuilder(); sql.AppendLine("SELECT m.RoomType, MIN(m.Availablecount) as Availablecount"); sql.AppendLine(" from ("); sql.AppendLine(" select t.StatusDate, t.RoomType, COUNT(t.Availablecount) as Availablecount"); sql.AppendLine(" from ("); sql.AppendLine(" select StatusDate, RoomType = case RoomType when 0 then 0 when 1 then 1 when 2 then 1 end, COUNT(1) as Availablecount"); sql.AppendLine(" from RoomStatus"); sql.AppendLine(" where StatusDate >= '2015-01-01' and StatusDate <= '2015-01-02' and Available=0"); sql.AppendLine(" group by StatusDate, RoomType"); sql.AppendLine(" ) as t"); sql.AppendLine(" group by t.StatusDate, t.RoomType"); sql.AppendLine(" ) m"); sql.AppendLine(" where m.Availablecount>0 "); sql.AppendLine(" group by m.RoomType"); SqlParameter[] paras = new SqlParameter[2]; paras[0] = new SqlParameter("@CheckinDate", model.CheckInDate); paras[1] = new SqlParameter("@CheckoutDate", model.CheckOutDate); SqlAccess mySqlAccess = new SqlAccess(); DataSet ds = mySqlAccess.ExecuteAdapter(sql.ToString(), paras); model.AccomodationOrderList = new List <AccomodationOrderListModel>(); if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0) { foreach (DataRow rw in ds.Tables[0].Rows) { AccomodationOrderListModel order = new AccomodationOrderListModel(); order.RoomType = Convert.ToInt32(rw["RoomType"]); order.AvailableRoomNumber = Convert.ToInt32(rw["Availablecount"]); model.AccomodationOrderList.Add(order); } } return(model); }
private AccomodationOrderModel GetQueenAvailableRoom(DateTime checkin, DateTime checkout, DataTable roomStatus, AccomodationOrderModel order) { int minRoomCount; List <int> RoomCount1 = new List <int>(); int[] RoomCount = new int[5]; int i = 0; for (DateTime dt = checkin; dt < checkout; dt = dt.AddDays(1)) { var result = from myRom in roomStatus.AsEnumerable() where (myRom.Field <int>("RoomType") == GlobalVariable.RoomType.Queen) && //|| myRom.Field<int>("RoomType") == GlobalVariable.RoomType.Princess) myRom.Field <bool>("Available") == true && myRom.Field <DateTime>("StatusDate") == dt select myRom; if (result.Count() == 0) { //there is one day without any rooms return(order); } else { //one day just left one room RoomCount1.Add(result.Count()); // RoomCount[i] = result.Count(); // i++; } } var min = RoomCount1.Min(); minRoomCount = min; AccomodationOrderListModel orderQueen = new AccomodationOrderListModel(); orderQueen.RoomType = GlobalVariable.RoomType.Queen; orderQueen.AvailableRoomNumber = minRoomCount; order.AccomodationOrderList.Add(orderQueen); return(order); }
public AccomodationOrderModel GetAvailableRoom(AccomodationOrderModel order) { StringBuilder sql = new StringBuilder(); sql.AppendLine("select StatusDate, RoomType, Available "); sql.AppendLine(" from RoomStatus"); sql.AppendLine(" where StatusDate >= @CheckinDate and StatusDate < @CheckoutDate"); SqlParameter[] paras = new SqlParameter[2]; paras[0] = new SqlParameter("@CheckinDate", order.CheckInDate); paras[1] = new SqlParameter("@CheckoutDate", order.CheckOutDate); SqlAccess mySqlAccess = new SqlAccess(); DataSet ds = mySqlAccess.ExecuteAdapter(sql.ToString(), paras); order.AccomodationOrderList = new List <AccomodationOrderListModel>(); if (ds != null && ds.Tables.Count > 0) { DataTable dt = ds.Tables[0]; if (dt.Rows.Count > 0) { order = this.GetKingAvailableRoom(Convert.ToDateTime(order.CheckInDate), Convert.ToDateTime(order.CheckOutDate), dt, order);// order = this.GetQueenAvailableRoom(Convert.ToDateTime(order.CheckInDate), Convert.ToDateTime(order.CheckOutDate), dt, order); } else { AccomodationOrderListModel orderListKing = new AccomodationOrderListModel(); orderListKing.RoomType = GlobalVariable.RoomType.King; orderListKing.AvailableRoomNumber = 1; order.AccomodationOrderList.Add(orderListKing); AccomodationOrderListModel orderListQueen = new AccomodationOrderListModel(); orderListQueen.RoomType = GlobalVariable.RoomType.Queen; orderListQueen.AvailableRoomNumber = 2; order.AccomodationOrderList.Add(orderListQueen); } } return(order); }
public AccomodationOrderModel SaveOrder(AccomodationOrderModel order) { List <string> sqls = new List <string>(); List <SqlParameter[]> cmdParms = new List <SqlParameter[]>(); JsonSerializer json = new JsonSerializer(); if (order.OrderID == 0) { int orderID = this.GetNewOrderID(); order.OrderID = orderID; } StringBuilder str; SqlParameter[] paras; #region Customer str = new StringBuilder(); str.AppendLine("Insert into Customer"); str.AppendLine(" (OrderID, Name, Phone, Email, Address, State, PostCode, CreateTime, UpdateTime) "); str.AppendLine(" Values (@OrderID, @Name, @Phone, @Email, @Address, @State, @PostCode, Getdate(), getdate());"); paras = new SqlParameter[7]; paras[0] = new SqlParameter("@OrderID", order.OrderID); paras[1] = new SqlParameter("@Name", order.Contact.Name); paras[2] = new SqlParameter("@Phone", order.Contact.Phone); paras[3] = new SqlParameter("@Email", order.Contact.Email); paras[4] = new SqlParameter("@Address", order.Contact.Address); paras[5] = new SqlParameter("@State", order.Contact.State); paras[6] = new SqlParameter("@PostCode", order.Contact.PostCode); sqls.Add(str.ToString()); cmdParms.Add(paras); #endregion #region AccomodationOrderDetail order.TotalPrice = 0; double originalPrice = 0; double discount = 0; foreach (AccomodationOrderListModel detail in order.AccomodationOrderList) { if (detail.OrderRoomNumber > 0) { detail.SubTotalPrice = (detail.Price - detail.DiscountPrice) * detail.OrderRoomNumber; order.TotalPrice = order.TotalPrice + detail.SubTotalPrice; originalPrice = originalPrice + detail.Price * detail.OrderRoomNumber; discount = discount + detail.DiscountPrice * detail.OrderRoomNumber; str = new StringBuilder(); str.AppendLine("Insert into RoomOrderDetail"); str.AppendLine(" (OrderID, RoomType, CheckinDate, CheckoutDate, Price, Discount, Quantity, TotalPrice, AdultCount, KidsCount, AdditionalDetail, CreateTime, UpdateTime) "); str.AppendLine(" Values (@OrderID, @RoomType, @CheckinDate, @CheckoutDate, @Price, @Discount, @Quantity, @TotalPrice, @AdultCount, @KidsCount, @AdditionalDetail, getdate(), getdate());"); paras = new SqlParameter[11]; paras[0] = new SqlParameter("@OrderID", order.OrderID); paras[1] = new SqlParameter("@RoomType", detail.RoomType); paras[2] = new SqlParameter("@CheckinDate", Convert.ToDateTime(order.CheckInDate)); paras[3] = new SqlParameter("@CheckoutDate", Convert.ToDateTime(order.CheckOutDate)); paras[4] = new SqlParameter("@Price", detail.Price); paras[5] = new SqlParameter("@Discount", detail.DiscountPrice); paras[6] = new SqlParameter("@Quantity", detail.OrderRoomNumber); paras[7] = new SqlParameter("@TotalPrice", detail.SubTotalPrice); paras[8] = new SqlParameter("@AdultCount", Convert.ToInt32(order.AccomodationOrderDetail.AdultCount)); paras[9] = new SqlParameter("@KidsCount", Convert.ToInt32(order.AccomodationOrderDetail.KidsCount)); paras[10] = new SqlParameter("@AdditionalDetail", Common.VariableConvert.ConvertStringToDBValue(order.AccomodationOrderDetail.AdditionalDetail)); sqls.Add(str.ToString()); cmdParms.Add(paras); } } #endregion #region Order str = new StringBuilder(); str.AppendLine("Insert into [Order]"); str.AppendLine(" (OrderID, OrderType, OrderNo, ProcessType, OrderDate, OriginalPrice, Discount, Shipping, TotalPrice, Tax, OrderStatus, CreateTime, UpdateTime) "); str.AppendLine(" Values (@OrderID, @OrderType, @OrderNo, @ProcessType, @OrderDate, @OriginalPrice, @Discount, 0, @TotalPrice, @Tax, @OrderStatus, Getdate(), getdate());"); paras = new SqlParameter[10]; paras[0] = new SqlParameter("@OrderID", order.OrderID); int orderType = (int)GlobalVariable.OrderType.Room; paras[1] = new SqlParameter("@OrderType", orderType); order.OrderNo = "R" + order.OrderID.ToString().PadLeft(9, '0'); paras[2] = new SqlParameter("@OrderNo", order.OrderNo); paras[3] = new SqlParameter("@ProcessType", order.ProcessType); order.OrderDate = DateTime.Now; paras[4] = new SqlParameter("@OrderDate", order.OrderDate); paras[5] = new SqlParameter("@OriginalPrice", originalPrice); paras[6] = new SqlParameter("@Discount", discount); paras[7] = new SqlParameter("@TotalPrice", order.TotalPrice); double tax = order.TotalPrice * Convert.ToDouble(ConfigurationManager.AppSettings["TaxRate"].ToString()); paras[8] = new SqlParameter("@Tax", tax); if (order.ProcessType == (int)GlobalVariable.ProcessType.Online) { int status = (int)GlobalVariable.OrderStatus.Init; paras[9] = new SqlParameter("@OrderStatus", status); } else if (order.ProcessType == (int)GlobalVariable.ProcessType.Offline) { int status = (int)GlobalVariable.OrderStatus.Success; paras[9] = new SqlParameter("@OrderStatus", status); } sqls.Add(str.ToString()); cmdParms.Add(paras); #endregion #region Transaction if (order.ProcessType == (int)GlobalVariable.ProcessType.Online) { string requestID = PayflowUtility.RequestId; order.RequestID = requestID; str = new StringBuilder(); str.AppendLine("Insert into [PaymentTransaction]"); str.AppendLine(" (RequestID, OrderID, CreateTime, UpdateTime) "); str.AppendLine(" Values (@RequestID, @OrderID, Getdate(), getdate());"); paras = new SqlParameter[2]; paras[0] = new SqlParameter("@RequestID", requestID); paras[1] = new SqlParameter("@OrderID", order.OrderID); sqls.Add(str.ToString()); cmdParms.Add(paras); } #endregion SqlAccess mySqlAccess = new SqlAccess(); mySqlAccess.ExecuteNonQuerys(sqls, cmdParms); return(order); }