public VersaTransEmployeeRepository()
{
_cache = new Dictionary <int, VersaTransEmployee>();
_vehicleRepo = new VersaTransVehicleRepository();
_certificationRepo = new VersaTransCertificationRepository();
using (SqlConnection connection = new SqlConnection(Settings.DBConnectionString_Versatrans))
{
SqlCommand sqlCommand = new SqlCommand
{
Connection = connection,
CommandType = CommandType.Text,
CommandText = SQLQuery
};
sqlCommand.Connection.Open();
SqlDataReader dbDataReader = sqlCommand.ExecuteReader();
if (dbDataReader.HasRows)
{
while (dbDataReader.Read())
{
VersaTransEmployee employee = dataReaderToEmployee(dbDataReader);
if (employee != null)
{
_cache.Add(employee.RecordID, employee);
}
}
}
sqlCommand.Connection.Close();
}
}
protected void Page_Load(object sender, EventArgs e)
{
VersaTransEmployeeRepository vtemployeeRepo = new VersaTransEmployeeRepository();
List <VersaTransEmployee> employees = vtemployeeRepo.GetAllActive();
Dictionary <VersatransCertification, VersaTransEmployee> allBusInspections = new Dictionary <VersatransCertification, VersaTransEmployee>();
foreach (VersaTransEmployee employee in employees)
{
// If the employee doesn't have any vehicles associated with them, then the certification is meaningless
if (employee.Vehicles.Count > 0)
{
foreach (VersatransCertification cert in employee.Certifications)
{
if (cert.CertificationType == "bus inspection")
{
allBusInspections.Add(cert, employee);
}
}
}
}
Dictionary <string, List <VersatransCertification> > certificationsByMonth = new Dictionary <string, List <VersatransCertification> >();
foreach (VersatransCertification cert in allBusInspections.Keys.OrderBy(x => x.Expires))
{
string dateKey = cert.Expires.Year + "-" + Helpers.GetMonthName(cert.Expires.Month);
if (!certificationsByMonth.ContainsKey(dateKey))
{
certificationsByMonth.Add(dateKey, new List <VersatransCertification>());
}
certificationsByMonth[dateKey].Add(cert);
}
Response.Write("<div style=\"font-family: Arial\">");
// Display on page
foreach (string dateKey in certificationsByMonth.Keys)
{
Response.Write("<h1 style=\"margin-bottom: 2; padding-bottom: 2;\">" + dateKey + "</h1>");
Response.Write("<table border=1 style=\"width: 600px;\"><tr><td><b>Vehicle</b></td><td><b>Driver</b></td><td><b>Expires</b></td><td><b>Last Complete</b></td></tr>");
foreach (VersatransCertification cert in certificationsByMonth[dateKey])
{
if (allBusInspections.ContainsKey(cert))
{
VersaTransEmployee driver = allBusInspections[cert];
foreach (VersaTransVehicle vehicle in driver.Vehicles)
{
Response.Write("<tr><td>" + vehicle.VehicleNumber + "</td><td>" + driver.DisplayName + "</td><td>" + cert.Expires.ToShortDateString() + "</td><td>" + cert.Completed.ToShortDateString() + "</td></tr>");
}
}
}
Response.Write("</table><br>");
// Vehicle
// Driver
// Expires
// Completed
}
Response.Write("</div>");
}
protected void Page_Load(object sender, EventArgs e)
{
VersaTransEmployeeRepository vtemployeeRepo = new VersaTransEmployeeRepository();
List <VersaTransEmployee> employees = vtemployeeRepo.GetAllActive();
Dictionary <VersatransCertification, VersaTransEmployee> allBusInspections = new Dictionary <VersatransCertification, VersaTransEmployee>();
foreach (VersaTransEmployee employee in employees)
{
// If the employee doesn't have any vehicles associated with them, then the certification is meaningless
if (employee.Vehicles.Count > 0)
{
foreach (VersatransCertification cert in employee.Certifications)
{
if (cert.CertificationType == "bus inspection")
{
allBusInspections.Add(cert, employee);
}
}
}
}
// Find all certifications expiring on or before the LAST DAY of the current month
Dictionary <VersatransCertification, VersaTransEmployee> inspectionsDueThisMonth = new Dictionary <VersatransCertification, VersaTransEmployee>();
Dictionary <VersatransCertification, VersaTransEmployee> inspectionsDueNextMonth = new Dictionary <VersatransCertification, VersaTransEmployee>();
Dictionary <VersatransCertification, VersaTransEmployee> overdueInspections = new Dictionary <VersatransCertification, VersaTransEmployee>();
DateTime startOfThisMonth = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1);
DateTime endOfThisMonth = new DateTime(startOfThisMonth.Year, DateTime.Now.Month, DateTime.DaysInMonth(DateTime.Now.Year, DateTime.Now.Month));
DateTime startOfNextMonth = endOfThisMonth.AddDays(1);
DateTime endOfNextMonth = new DateTime(startOfNextMonth.Year, startOfNextMonth.Month, DateTime.DaysInMonth(startOfNextMonth.Year, startOfNextMonth.Month));
foreach (VersatransCertification cert in allBusInspections.Keys)
{
// Overdue
if (
(cert.Expires <= endOfThisMonth.AddMonths(-1))
)
{
overdueInspections.Add(cert, allBusInspections[cert]);
}
// Current month
if (
(cert.Expires >= startOfThisMonth) &&
(cert.Expires <= endOfThisMonth)
)
{
inspectionsDueThisMonth.Add(cert, allBusInspections[cert]);
}
// Next month
if (
(cert.Expires >= startOfNextMonth) &&
(cert.Expires <= endOfNextMonth)
)
{
inspectionsDueNextMonth.Add(cert, allBusInspections[cert]);
}
}
// Now go through and remove any inspections that have no valid vehicles associated with them
foreach (KeyValuePair <VersatransCertification, VersaTransEmployee> kvp in inspectionsDueThisMonth.Where(i => i.Value.Vehicles.Count <= 0))
{
inspectionsDueThisMonth.Remove(kvp.Key);
}
foreach (KeyValuePair <VersatransCertification, VersaTransEmployee> kvp in inspectionsDueNextMonth.Where(i => i.Value.Vehicles.Count <= 0))
{
inspectionsDueThisMonth.Remove(kvp.Key);
}
foreach (KeyValuePair <VersatransCertification, VersaTransEmployee> kvp in overdueInspections.Where(i => i.Value.Vehicles.Count <= 0))
{
inspectionsDueThisMonth.Remove(kvp.Key);
}
Response.Clear();
Response.ContentEncoding = Encoding.UTF8;
Response.ContentType = "application/json; charset=utf-8";
Response.Write("{\n");
Response.Write("\"TotalOverdue\" : " + overdueInspections.Count + ",\n");
Response.Write("\"Overdue\": [\n");
int displaycount = 0;
foreach (VersatransCertification cert in overdueInspections.Keys)
{
VersaTransEmployee driver = overdueInspections[cert];
foreach (VersaTransVehicle vehicle in driver.Vehicles)
{
Response.Write("\n{");
Response.Write("\"Vehicle\" : \"" + vehicle.VehicleNumber + "\",");
Response.Write("\"Driver\" : \"" + driver.DisplayName + "\",");
Response.Write("\"Expires\" : \"" + cert.Expires.ToShortDateString() + "\",");
Response.Write("\"Completed\" : \"" + cert.Completed.ToShortDateString() + "\"");
Response.Write("}");
if (!(displaycount + 1 >= overdueInspections.Count))
{
Response.Write(",");
}
displaycount++;
}
}
Response.Write("],\n");
Response.Write("\"ThisMonthName\": \"" + Helpers.GetMonthName(startOfThisMonth.Month) + "\",\n");
Response.Write("\"TotalThisMonth\" : " + inspectionsDueThisMonth.Count + ",\n");
Response.Write("\"ThisMonth\": [\n");
displaycount = 0;
foreach (VersatransCertification cert in inspectionsDueThisMonth.Keys)
{
VersaTransEmployee driver = inspectionsDueThisMonth[cert];
foreach (VersaTransVehicle vehicle in driver.Vehicles)
{
Response.Write("\n{");
Response.Write("\"Vehicle\" : \"" + vehicle.VehicleNumber + "\",");
Response.Write("\"Driver\" : \"" + driver.DisplayName + "\",");
Response.Write("\"Expires\" : \"" + cert.Expires.ToShortDateString() + "\",");
Response.Write("\"Completed\" : \"" + cert.Completed.ToShortDateString() + "\"");
Response.Write("}");
if (!(displaycount + 1 >= inspectionsDueThisMonth.Count))
{
Response.Write(",");
}
displaycount++;
}
}
Response.Write("],\n");
Response.Write("\"NextMonthName\": \"" + Helpers.GetMonthName(startOfNextMonth.Month) + "\",\n");
Response.Write("\"TotalNextMonth\" : " + inspectionsDueNextMonth.Count + ",\n");
Response.Write("\"NextMonth\": [\n");
displaycount = 0;
foreach (VersatransCertification cert in inspectionsDueNextMonth.Keys)
{
VersaTransEmployee driver = inspectionsDueNextMonth[cert];
foreach (VersaTransVehicle vehicle in driver.Vehicles)
{
Response.Write("\n{");
Response.Write("\"Vehicle\" : \"" + vehicle.VehicleNumber + "\",");
Response.Write("\"Driver\" : \"" + driver.DisplayName + "\",");
Response.Write("\"Expires\" : \"" + cert.Expires.ToShortDateString() + "\",");
Response.Write("\"Completed\" : \"" + cert.Completed.ToShortDateString() + "\"");
Response.Write("}");
if (!(displaycount + 1 >= inspectionsDueNextMonth.Count))
{
Response.Write(",");
}
displaycount++;
}
}
Response.Write("]\n");
Response.Write("}\n");
Response.End();
}