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);
}
