// queries # of checkouts by item and location
// result table is either returned for display or exported as an Excel file
public List <ViewModels.QueryCheckoutsView> QueryCheckoutsByItem(DateTime startDate, DateTime endDate, string location, string action)
{
// get contents of tb_CSUCheckoutCheckin with date/loc parameters
var checkouts = db.tb_CSUCheckoutCheckin.Where(m => m.CheckoutDate >= startDate && m.CheckoutDate <= endDate);
List <ViewModels.QueryCheckoutsView> results = new List <ViewModels.QueryCheckoutsView>();
var numCheckedBackIn = 0;
foreach (var c in checkouts)
{
if (location == "All" || location == c.CheckoutLocationFK)
{
bool containsItemAlready = results.Any(item => item.ItemUpc == c.ItemUPCFK);
if (containsItemAlready)
{
foreach (var r in results)
{
if (r.ItemUpc == c.ItemUPCFK)
{
if (c.isLate == true)
{
r.NumLateCheckouts++;
}
if (c.CheckinDate != null)
{
numCheckedBackIn++;
var duration = (TimeSpan)(c.CheckinDate - c.CheckoutDate);
r.AvgCheckoutLength = ((r.AvgCheckoutLength * (numCheckedBackIn - 1)) + duration.TotalMinutes) / numCheckedBackIn;
}
r.NumCheckouts++;
}
}
}
else
{
ViewModels.QueryCheckoutsView entry = new ViewModels.QueryCheckoutsView();
entry.ItemUpc = c.ItemUPCFK;
entry.NumCheckouts = 1;
if (c.isLate == true)
{
entry.NumLateCheckouts = 1;
}
else
{
entry.NumLateCheckouts = 0;
}
if (c.CheckinDate != null)
{
numCheckedBackIn++;
TimeSpan duration = (TimeSpan)(c.CheckinDate - c.CheckoutDate);
entry.AvgCheckoutLength = duration.TotalMinutes;
}
results.Add(entry);
}
}
}
var sortedResults = results.OrderByDescending(a => a.NumCheckouts).ToList();
if (action == "excel")
{
List <ExportModels.ExportCheckoutsByItem> data = new List <ExportModels.ExportCheckoutsByItem>();
foreach (var item in sortedResults)
{
ExportModels.ExportCheckoutsByItem e = new ExportModels.ExportCheckoutsByItem();
e.ItemUpc = item.ItemUpc;
e.NumberOfCheckouts = Convert.ToInt32(item.NumCheckouts);
e.NumberOfLateCheckouts = Convert.ToInt32(item.NumLateCheckouts);
e.AverageLengthOfCheckout = Convert.ToDouble(item.AvgCheckoutLength);
data.Add(e);
}
var grid = new System.Web.UI.WebControls.GridView();
grid.DataSource = data;
grid.DataBind();
Response.ClearContent();
var fname = "CheckoutsByItem_" + location + ".xls";
Response.AddHeader("content-disposition", "attachment; filename=" + fname);
Response.ContentType = "application/vnd.ms-excel";
StringWriter sw = new StringWriter();
System.Web.UI.HtmlTextWriter htw = new System.Web.UI.HtmlTextWriter(sw);
grid.RenderControl(htw);
Response.Write(sw.ToString());
Response.End();
List <ViewModels.QueryCheckoutsView> emptyResults = new List <ViewModels.QueryCheckoutsView>();
return(emptyResults);
}
return(sortedResults);
}
// queries # of checkouts by hour and location
// result table is either returned for display or exported as an Excel file
public List <ViewModels.QueryCheckoutsView> QueryCheckoutsByHour(DateTime startDate, DateTime endDate, string location, string action)
{
// get contents of tb_CSUCheckoutCheckin with date/loc parameters
var checkouts = db.tb_CSUCheckoutCheckin.Where(m => m.CheckoutDate >= startDate && m.CheckoutDate <= endDate);
List <ViewModels.QueryCheckoutsView> results = new List <ViewModels.QueryCheckoutsView>();
foreach (var c in checkouts)
{
if (location == "All" || location == c.CheckoutLocationFK)
{
bool containsHourAlready = results.Any(item => item.CheckoutHour == Convert.ToDateTime(c.CheckoutDate).Hour);
if (containsHourAlready)
{
// increment hour tally
foreach (var r in results)
{
if (r.CheckoutHour == Convert.ToDateTime(c.CheckoutDate).Hour)
{
r.NumCheckouts++;
}
}
}
else
{
// add hour to list and set the tally to 1
ViewModels.QueryCheckoutsView entry = new ViewModels.QueryCheckoutsView();
entry.CheckoutHour = Convert.ToDateTime(c.CheckoutDate).Hour;
entry.NumCheckouts = 1;
results.Add(entry);
}
}
}
var sortedResults = results.OrderBy(a => a.CheckoutHour).ToList();
if (action == "excel")
{
List <ExportModels.ExportCheckoutsByHour> data = new List <ExportModels.ExportCheckoutsByHour>();
foreach (var item in sortedResults)
{
ExportModels.ExportCheckoutsByHour e = new ExportModels.ExportCheckoutsByHour();
e.CheckoutHour = item.CheckoutHour.ToString();
e.NumberOfCheckouts = Convert.ToInt32(item.NumCheckouts);
data.Add(e);
}
var grid = new System.Web.UI.WebControls.GridView();
grid.DataSource = data;
grid.DataBind();
Response.ClearContent();
var fname = "CheckoutsByHour_" + location + ".xls";
Response.AddHeader("content-disposition", "attachment; filename=" + fname);
Response.ContentType = "application/vnd.ms-excel";
StringWriter sw = new StringWriter();
System.Web.UI.HtmlTextWriter htw = new System.Web.UI.HtmlTextWriter(sw);
grid.RenderControl(htw);
Response.Write(sw.ToString());
Response.End();
List <ViewModels.QueryCheckoutsView> emptyResults = new List <ViewModels.QueryCheckoutsView>();
return(emptyResults);
}
return(sortedResults);
}
