I worked on a project, where we maintain Guest Arrival/Departure information and their counts. My client wants to see the head counts for preparing food for the guests, Morning/Noon/Night. They will maintain the below counts:
- OB – Opening Balance Counts
- IN – IN Head Counts
- OUT – Out Head Counts
- CB – Closing Balance Counts
Data we retrieved from SQL Server. We were going to convert the below rows into Quick Summary View
Guest Counting View for 15 days
Code for generating the above view
public static string GetGuestCountDetail(DateTime StartDate, DateTime EndDate)
{
string sException = string.Empty;
Database db;
StringBuilder sbQuickSummary = new StringBuilder();
DateTime dtStartDate = StartDate;
try
{
db = Entity.DBConnection.dbCon;
DbCommand cmd = db.GetStoredProcCommand(constants.StoredProcedures.USP_SELECT_GUESTCOUNTINGDETAIL);
db.AddInParameter(cmd, "@DateFrom", DbType.String, StartDate.ToString("dd/MM/yyyy"));
db.AddInParameter(cmd, "@DateTo", DbType.String, EndDate.ToString("dd/MM/yyyy"));
DataSet dsList = db.ExecuteDataSet(cmd);
int DaysCount = (EndDate.Date - StartDate.Date).Days + 1;
if (dsList.Tables.Count > 0 && dsList.Tables[0].Rows.Count > 0)
{
var qData = (from t in dsList.Tables[0].AsEnumerable()
select new
{
GuestCountingDetailID = t.Field<int>("PK_GuestCountingDetailID"),
GuestScheduleID = t.Field<int>("FK_GuestScheduleID"),
ProcessDate = t.Field<DateTime>("ProcessDate"),
ShiftID = t.Field<byte>("ShiftID"),
EntryTypeID = t.Field<byte>("EntryTypeID"),
AdultCount = t.Field<int>("AdultCount"),
ChildCount = t.Field<int>("ChildCount"),
PersonTypeID = t.Field<byte>("PersonTypeID"),
ScheduleNo = t.Field<string>("ScheduleNo"),
ScheduleTypeID = t.Field<byte>("ScheduleTypeID")
});
string sTemp = string.Empty;
DataTable dtGuestCounting = new DataTable();
DataRow drow = null;
dtGuestCounting.Columns.Add("PrepareDate", typeof(DateTime));
dtGuestCounting.Columns.Add("OpeningBalance", typeof(int));
dtGuestCounting.Columns.Add("ShiftID", typeof(Int16));
dtGuestCounting.Columns.Add("ClosingBalance", typeof(int));
//Table Header
sbQuickSummary.Append("<div class='table-responsive'>");
sbQuickSummary.Append("<table id='tblGuestCounting' class='table no-margin table-hover'>");
sbQuickSummary.Append("<thead><tr class='bg-aqua-active color-palette'>");
sbQuickSummary.Append("<th colspan='2' style='text-align:center;'>Schedule</th>");
sbQuickSummary.Append("<th colspan='4' style='text-align:center;'>Morning</th><th colspan='3' style='text-align:center;'>Noon</th><th colspan='3' style='text-align:center;'>Night</th>");
sbQuickSummary.Append("</tr><tr class='bg-info disabled color-palette'>");
sbQuickSummary.Append("<th>Date</th><th>Day</th>");
sbQuickSummary.Append("<th>OB</th><th>IN</th><th>OUT</th><th>CB</th>"); //Morning
sbQuickSummary.Append("<th>IN</th><th>OUT</th><th>CB</th>"); //Noon
sbQuickSummary.Append("<th>IN</th><th>OUT</th><th>CB</th>"); //Night
sbQuickSummary.Append("</tr></thead>");
sbQuickSummary.Append("<tbody>");
DateTime tDate = StartDate;
int ClosingBalance = 0, OnceCount = 0, INCount = 0, OUTCount = 0, ACount = 0, CCount = 0;
ClosingBalance = GetClosingBalance(tDate);
for (int DayCount = 1; DayCount <= DaysCount; DayCount++)
{
sbQuickSummary.Append("<tr>");
sbQuickSummary.Append("<td>" + tDate.ToString("dd/MM/yyyy") + "</td>");
sbQuickSummary.Append("<td>" + tDate.ToString("ddd") + "</td>");
//ACount = 0; CCount = 0;
for (int ShiftID = 1; ShiftID <= 3; ShiftID++)
{
drow = dtGuestCounting.NewRow();
var qShiftTemp = (from t in qData where t.ShiftID == ShiftID && t.ProcessDate.Date == tDate.Date select t);
if (qShiftTemp.Count() > 0)
{
var qShiftData = (from t in qShiftTemp
group t by new { t.ShiftID, t.EntryTypeID } into grp
select new
{
grp.Key.EntryTypeID,
grp.Key.ShiftID,
AdultCount = grp.Sum(o => o.AdultCount),
ChildCount = grp.Sum(o => o.ChildCount)
});
if (ShiftID == 1) sbQuickSummary.Append("<td>" + ClosingBalance.ToString() + "</td>");
drow["OpeningBalance"] = ClosingBalance;
drow["PrepareDate"] = tDate;
drow["ShiftID"] = ShiftID;
//INCount
INCount = 0;
var qShiftINCount = (from t in qShiftData where t.EntryTypeID == 1 select t).FirstOrDefault();
if (qShiftINCount != null)
{
INCount = qShiftINCount.AdultCount + qShiftINCount.ChildCount;
ACount = ACount + qShiftINCount.AdultCount;
CCount = CCount + qShiftINCount.ChildCount;
}
else
INCount = 0;
//Once Count
OnceCount = 0;
var qShiftOnceCount = (from t in qShiftData where t.EntryTypeID == 3 select t).FirstOrDefault();
if (qShiftOnceCount != null)
{
OnceCount = qShiftOnceCount.AdultCount + qShiftOnceCount.ChildCount;
ACount = ACount + qShiftOnceCount.AdultCount;
CCount = CCount + qShiftOnceCount.ChildCount;
}
else
OnceCount = 0;
INCount = INCount + OnceCount;
if (OnceCount > 0)
{
sTemp = "IN : " + (INCount - OnceCount).ToString() + " ONCE : " + OnceCount.ToString();
sbQuickSummary.Append("<td title='" + sTemp + "'><span class='badge bg-green'>" + INCount.ToString() + "</span></td>");
sTemp = string.Empty;
}
else
sbQuickSummary.Append("<td>" + INCount.ToString() + "</td>");
//OUTCount
OUTCount = 0;
var qShiftOUTCount = (from t in qShiftData where t.EntryTypeID == 2 select t).FirstOrDefault();
if (qShiftOUTCount != null)
{
OUTCount = qShiftOUTCount.AdultCount + qShiftOUTCount.ChildCount;
ACount = ACount - qShiftOUTCount.AdultCount;
CCount = CCount - qShiftOUTCount.ChildCount;
}
else
OUTCount = 0;
sbQuickSummary.Append("<td>" + OUTCount.ToString() + "</td>");
ClosingBalance = ClosingBalance + INCount - OUTCount;
var qPersonData = (from t in qShiftTemp
group t by new { t.PersonTypeID } into grp
select new
{
grp.Key.PersonTypeID,
AdultCount = grp.Sum(o => o.AdultCount),
ChildCount = grp.Sum(o => o.ChildCount)
});
sbQuickSummary.Append("<td title='" + sTemp + "'><span class='badge bg-maroon'>" + ClosingBalance.ToString() + "</span></td>");
ClosingBalance = ClosingBalance - OnceCount;
drow["ClosingBalance"] = ClosingBalance;
if (qShiftOnceCount != null)
{
ACount = ACount - qShiftOnceCount.AdultCount;
CCount = CCount - qShiftOnceCount.ChildCount;
}
}
else
{
drow["OpeningBalance"] = ClosingBalance;
drow["PrepareDate"] = tDate;
drow["ShiftID"] = ShiftID;
drow["ClosingBalance"] = ClosingBalance;
if (ShiftID == 1) sbQuickSummary.Append("<td>" + ClosingBalance.ToString() + "</td>");
sbQuickSummary.Append("<td>0</td>");
sbQuickSummary.Append("<td>0</td>");
sbQuickSummary.Append("<td title='" + sTemp + "'><span class='badge bg-maroon'>" + ClosingBalance.ToString() + "</span></td>");
}
dtGuestCounting.Rows.Add(drow);
}
sbQuickSummary.Append("</tr>");
tDate = tDate.AddDays(1);
}
sbQuickSummary.Append("</tbody></table></div>");
SaveGuestCounting(dtGuestCounting);
}
}
catch (Exception ex)
{
sException = "DAL.GuestCountingDetail GetGuestCountDetail | " + ex.ToString();
Log.Write(sException);
throw;
}
return sbQuickSummary.ToString();
}
