public DataTable ResDataTable(DateTime startOnDgv, DateTime endOnDgv)
{
//public List<ReservationInfo> FilterReservationByDgvDate(DateTime RangeFrom, DateTime RangeTill)
//SELECT* FROM Product_salesWHERE NOT(From_date > @RangeTill OR To_date < @RangeFrom)
//MessageBox.Show(start.ToString()+"my start date");
//MessageBox.Show(end.ToString()+"my end date");
ReservationDataService reservationDataService = new ReservationDataService();
List <ReservationInfo> myReservationList = reservationDataService.ShowAllRes(startOnDgv, endOnDgv);
ReservationInfo me = myReservationList[1];
//string query = "SELECT id, room_id, customer_id, description, startDate, endDate, totalPrice, lengthOfStay, rate FROM reservation WHERE NOT (startDate > @end OR endDate < @start)";
string query = "SELECT res.id, room.roomNumber, cust.name, res.description, res.startDate, res.endDate, res.totalPrice, res.lengthOfStay, res.rate " +
"FROM reservation res " +
"INNER JOIN room room " +
"ON res.room_id = room.id " +
"INNER JOIN customer cust " +
"ON res.customer_id = cust.id " +
"WHERE NOT (startDate > '2019-01-31 00:00:00' OR endDate < '2019-01-01 00:00:00')";
DataTable dt = new DataTable();
using (MySqlCommand cmd = new MySqlCommand(query, conn))
{
cmd.Parameters.AddWithValue("@end", endOnDgv);
cmd.Parameters.AddWithValue("@start", startOnDgv);
try
{
conn.Open();
using (MySqlDataAdapter sda = new MySqlDataAdapter(cmd))
{
sda.Fill(dt);
dt.Columns.Add("CalcDays", typeof(Int32));
dt.Columns.Add("TotalRevenue", typeof(Int32));
// dt.Columns.Add("TotalReserveDays", typeof(Int32));
//dt.Columns.Add("CalcDays", typeof(Int32));
for (int i = 0; i < dt.Rows.Count; i++)
{
List <string> computeValues = FilterResForMsr(myReservationList[i], startOnDgv, endOnDgv);
dt.Rows[i][9] = computeValues[0];
dt.Rows[i][10] = computeValues[1];
//dt.Rows[i][10] = 19;
//computeValues.Clear();
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString() + "Filter reservations failed");
return(null);
}
}
return(dt);
}
public DataTable Occupancy(DateTime startOnDgv, DateTime endOnDgv)
{
ReservationDataService reservationDataService = new ReservationDataService();
List <ReservationInfo> myReservationList = reservationDataService.ShowAllRes(startOnDgv, endOnDgv);
ReservationInfo me = myReservationList[1];
RoomDataService myRoomDataService = new RoomDataService();
int amtOfRooms = myRoomDataService.GetAmtRooms();
int days = DateTime.DaysInMonth(endOnDgv.Year, endOnDgv.Month);
int potentialNights = amtOfRooms * days;
//MessageBox.Show("My amount of rooms" + amtOfRooms.ToString());
// THe feilds we want a room.roomNumber, totalRoomNIghts of all, potentialNights
// totalRoom nights of all is equal to filter running total of calc days
// potentailnights is equal to daysInMonth * amtOfRooms
// amtOfrooms is to mysql count(*)
//
string query = "SELECT id, roomNumber from sad2_db.room";
DataTable dt = new DataTable();
using (MySqlCommand cmd = new MySqlCommand(query, conn))
{
cmd.Parameters.AddWithValue("@end", endOnDgv);
cmd.Parameters.AddWithValue("@start", startOnDgv);
try
{
conn.Open();
using (MySqlDataAdapter sda = new MySqlDataAdapter(cmd))
{
sda.Fill(dt);
dt.Columns.Add("TotRoomNights", typeof(Int32));
dt.Columns.Add("PotentNights", typeof(Int32));
dt.Columns.Add("OccPer", typeof(String));
int roomNightsMonth = 0;
//dt.Columns.Add("CalcDays", typeof(Int32));
int theHell = 0;
for (int i = 0; i < amtOfRooms; i++)
{
for (int x = 0; x < myReservationList.Count; x++)
{
dt.Columns["TotRoomNights"].DefaultValue = 0;
//dt.Columns["Occupancy"].
List <string> computeValues = FilterResForMsr(myReservationList[x], startOnDgv, endOnDgv);
if (Convert.ToInt32(computeValues[2]) == Convert.ToInt32(dt.Rows[i][0]))
{
dt.Columns["TotRoomNights"].DefaultValue = 0;
//Convert.ToInt32(dt.Rows[0]["field_name"]);
theHell += Convert.ToInt32(computeValues[0]);
dt.Rows[i]["TotRoomNights"] = theHell;
//dt.Rows[i]["TotRoomNights"] = Convert.ToInt32(computeValues[0]) + Convert.ToInt32(dt.Rows[i]["TotRoomNights"]);
}
}
theHell = 0;
dt.Rows[i]["PotentNights"] = potentialNights;
/*
* roomNightsMonth += Convert.ToInt32(computeValues[0]);
* MessageBox.Show(roomNightsMonth.ToString());
*/
}
foreach (DataRow items in dt.Rows)
{
if (items[2] == DBNull.Value)
{
items[2] = 0;
}
}
foreach (DataRow items in dt.Rows)
{
decimal roomNights = Math.Round(((Convert.ToDecimal(items["TotRoomNights"]) / potentialNights) * 100), 2);
string format = roomNights.ToString() + (" %");
//MessageBox.Show((Convert.ToDouble(items["TotRoomNights"]) / potentialNights).ToString());
items["OccPer"] = format;
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString() + "Filter reservations failed");
return(null);
}
}
return(dt);
}