protected void lstView_ItemDataBound(object sender, ListViewItemEventArgs e)
{
HotelRoomQuantity hh = (HotelRoomQuantity)e.Item.DataItem;
Button btnSelect = (Button)e.Item.FindControl("btnSelect");
btnSelect.CommandArgument = hh.H_name + "-" + hh.R_type;
}
public List <HotelRoomQuantity> getSpecialRequirements()
{
List <HotelRoomQuantity> hotels = new List <HotelRoomQuantity>();
try
{
sqlStr = "select * from special_requirements";
cmd = new MySqlCommand(sqlStr, conn);
rdr = cmd.ExecuteReader();
HotelRoomQuantity hrq;
while (rdr.Read())
{
hrq = new HotelRoomQuantity();
hrq.H_name = rdr["res_id"].ToString();
hrq.R_type = rdr["description"].ToString();
hrq.Quantity = Convert.ToInt32(rdr["price"]);
hotels.Add(hrq);
}
rdr.Close();
}
catch (Exception ex)
{
throw ex;
}
return(hotels);
}
public List <HotelRoomQuantity> descReservationTable()
{
List <HotelRoomQuantity> hotels = new List <HotelRoomQuantity>();
try
{
sqlStr = "desc reservation";
cmd = new MySqlCommand(sqlStr, conn);
rdr = cmd.ExecuteReader();
HotelRoomQuantity hrq;
while (rdr.Read())
{
hrq = new HotelRoomQuantity();
hrq.H_name = rdr["field"].ToString();
hrq.R_type = rdr["type"].ToString();
hrq.Quantity = 0;
hotels.Add(hrq);
}
rdr.Close();
}
catch (Exception ex)
{
throw ex;
}
return(hotels);
}
/*
* res_id / int(5) / 0
* c_id / int(4) / 0
* h_name / varchar(30) / 0
* r_type / varchar(20) / 0
* start_date / date / 0
* end_date / date / 0
* quantity / int(1) / 0
*/
public List <HotelRoomQuantity> getHotelCapacityByDes(String desti)
{
List <HotelRoomQuantity> hotels = new List <HotelRoomQuantity>();
try
{
sqlStr = "select cap.h_name, cap.r_type, count(r_id) as r_cap "
+ "from room cap "
+ "join hotel on hotel.h_name = cap.H_NAME "
+ "where city = '" + desti + "' "
+ "group by cap.H_NAME, cap.r_type "
+ "order by cap.h_name";
cmd = new MySqlCommand(sqlStr, conn);
rdr = cmd.ExecuteReader();
HotelRoomQuantity hrq;
while (rdr.Read())
{
hrq = new HotelRoomQuantity();
hrq.H_name = rdr["h_name"].ToString();
hrq.R_type = rdr["r_type"].ToString();
hrq.Quantity = Convert.ToInt32(rdr["r_cap"]);
hotels.Add(hrq);
}
rdr.Close();
}
catch (Exception ex)
{
throw ex;
}
return(hotels);
}
public HotelRoomQuantity getSelectdHotelRoom(string selectedItem)
{
HotelRoomQuantity hrq = new HotelRoomQuantity();
try
{
sqlStr = "select cap.h_name, cap.r_type, count(r_id) as r_cap "
+ "from room cap "
+ "join hotel on hotel.h_name = cap.H_NAME "
+ "where concat_ws('-',cap.h_name,cap.r_type) = '" + selectedItem + "' "
+ "group by cap.H_NAME, cap.r_type "
+ "order by cap.h_name";
cmd = new MySqlCommand(sqlStr, conn);
rdr = cmd.ExecuteReader();
while (rdr.Read())
{
hrq.H_name = rdr["h_name"].ToString();
hrq.R_type = rdr["r_type"].ToString();
hrq.Quantity = Convert.ToInt32(rdr["r_cap"]);
}
rdr.Close();
}
catch (Exception ex)
{
throw ex;
}
return(hrq);
}
public List <HotelRoomQuantity> getReserved(String starting, String ending, String desti) //String desti, , DateTime ending
{
List <HotelRoomQuantity> hotels = new List <HotelRoomQuantity>();
try
{
sqlStr = "select h_name, r_type, sum(quantity) as r_booked from reservation "
+ "where h_name in (select h_name from hotel where city = '" + desti + "') and "
+ "((start_date>= str_to_date('" + starting + "','MM/DD/YY') and end_date < str_to_date('" + ending + "','MM/DD/YY')) "
+ "or (end_date > str_to_date('" + starting + "','MM/DD/YY') and end_date <= str_to_date('" + ending + "','MM/DD/YY')) "
+ "or (start_date<= str_to_date('" + starting + "','MM/DD/YY') and end_date >= str_to_date('" + ending + "','MM/DD/YY')) "
+ "or (start_date>= str_to_date('" + starting + "','MM/DD/YY') and end_date <= str_to_date('" + ending + "','MM/DD/YY'))) "
+ "group by h_name, r_type";
cmd = new MySqlCommand(sqlStr, conn);
rdr = cmd.ExecuteReader();
HotelRoomQuantity hrq;
while (rdr.Read())
{
hrq = new HotelRoomQuantity();
hrq.H_name = rdr["h_name"].ToString();
hrq.R_type = rdr["r_type"].ToString();
hrq.Quantity = Convert.ToInt32(rdr["r_booked"]);
hotels.Add(hrq);
}
rdr.Close();
}
catch (Exception ex)
{
throw ex;
}
return(hotels);
}
protected void Page_Load(object sender, EventArgs e)
{
HotelRoomQuantity hotel_room = (HotelRoomQuantity)HttpContext.Current.Session["hotel_room"];
lblSelectedRoom.Text = hotel_room.H_name + " / " + hotel_room.R_type;
dbmJ = DBMgr_Jeff.getInstance();
user_id = (int)Session["user_id"];
}
public static List <HotelRoomQuantity> cloneHotels(List <HotelRoomQuantity> h)
{
foreach (HotelRoomQuantity hrq in h)
{
HotelRoomQuantity temp = new HotelRoomQuantity();
temp.H_name = String.Copy(hrq.H_name);
temp.R_type = String.Copy(hrq.R_type);
temp.Quantity = hrq.Quantity;
//temp.TotalRooms = hrq.TotalRooms;
//temp.AvailableRooms = hrq.AvailableRooms;
hotels.Add(temp);
}
return(hotels);
}
protected void lstView_ItemCommand(object sender, ListViewCommandEventArgs e)
{
string lstView_val = (string)e.CommandArgument;
HotelRoomQuantity hotelRoom = dbm.getSelectdHotelRoom(lstView_val);
Session["hotel_room"] = hotelRoom;
if (Session["user_id"] == null)
{
Response.Redirect("Login.aspx");
}
else
{
Response.Redirect("Reserve.aspx");
}
}
protected void btnReserve_Click(object sender, EventArgs e)
{
DBMgr dbm = new DBMgr();
HotelRoomQuantity hotel_room = (HotelRoomQuantity)Session["hotel_room"];
String hotel = hotel_room.H_name;
String r_type = hotel_room.R_type;
int quantity = Convert.ToInt32(ddlNoOfRooms.SelectedValue);
Search search = (Search)Session["search"];
String starting = search.StartingDate;
String ending = search.EndingDate;
dbm.insertReservation(user_id, hotel, r_type, starting, ending, quantity);
AddCookie();
Response.Redirect("UserAccount.aspx");
}
public List <HotelRoomQuantity> getReservedOnSingleNight(String starting, String desti) //String desti, , DateTime end_date
{
List <HotelRoomQuantity> hotels = new List <HotelRoomQuantity>();
try
{
//2016/4/23
/*
* sqlStr = "select h_name, r_type, sum(quantity) as r_booked from reservation "
+ "where h_name in (select h_name from hotel where city = '" + desti + "') and "
+ "(start_date<= str_to_date('" + starting + "','MM/DD/YY') and end_date > str_to_date('" + starting + "','MM/DD/YY')) "
+ "group by h_name, r_type";
*/
//2016/4/23 is turned into "4/23/2016"
//'%m/%d/%Y', y has to be captilized
sqlStr = "select h_name, r_type, sum(quantity) as r_booked from reservation "
+ "where h_name in (select h_name from hotel where city = '" + desti + "') and "
+ "(start_date<= str_to_date('" + starting + "','%m/%d/%Y') and end_date > str_to_date('" + starting + "','%m/%d/%Y')) "
+ "group by h_name, r_type";
cmd = new MySqlCommand(sqlStr, conn);
rdr = cmd.ExecuteReader();
HotelRoomQuantity hrq;
while (rdr.Read())
{
hrq = new HotelRoomQuantity();
hrq.H_name = rdr["h_name"].ToString();
hrq.R_type = rdr["r_type"].ToString();
hrq.Quantity = Convert.ToInt32(rdr["r_booked"]);
hotels.Add(hrq);
}
rdr.Close();
}
catch (Exception ex)
{
throw ex;
}
return(hotels);
}
public void add(HotelRoomQuantity hrq)
{
lst.Add(hrq);
}