public void LoadMrsDgv(DateTime startDateDgv, DateTime endDateDgv) { ReservationDataService myResDataServ = new ReservationDataService(); DataTable storeData = myResDataServ.MsrDataSource(startDateDgv, endDateDgv); dgvMsr.DataSource = storeData; MonthlyReportData = storeData; StartDate = startDateDgv; EndDate = endDate; }
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); }
private void btnGenerateMsr_Click(object sender, EventArgs e) { try { MSRForm frm = new MSRForm(); ReservationDataService resData = new ReservationDataService(); frm.referencefrm1 = this; frm.dgvLoad(MonthlyReportData); frm.Show(); } catch (Exception ex) { MessageBox.Show(ex.ToString()); } }
public void LoadOccupancyRep(DateTime startDateDgv, DateTime endDateDgv) { try { ReportOcc crpt = new ReportOcc(); ReservationDataService myResDataServ = new ReservationDataService(); DataTable storeData = myResDataServ.Occupancy(startDateDgv, endDateDgv); //dgvMsr.DataSource = storeData; //MonthlyReportData = storeData; //StartDate = startDateDgv; //EndDate = endDate; crpt.Database.Tables["OccReport"].SetDataSource(storeData); crvOccReport.ReportSource = null; crvOccReport.ReportSource = crpt; } catch (Exception ex) { MessageBox.Show(ex.ToString()); } }
public void LoadReport(int resId) { try { rptStateAccount crpt = new rptStateAccount(); ReservationDataService myResDataServ = new ReservationDataService(); DataTable storeData = myResDataServ.FindGroupRes(resId); //dgvMsr.DataSource = storeData; //MonthlyReportData = storeData; //StartDate = startDateDgv; //EndDate = endDate; crpt.Database.Tables["StateAcc"].SetDataSource(storeData); crvStateAcc.ReportSource = null; crvStateAcc.ReportSource = crpt; } catch (Exception ex) { MessageBox.Show(ex.ToString()); } }
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); }