public Response ReportFieldReadinessWithInterval(string startDate, string endDate)
{
if (validateDate(startDate) && validateDate(endDate))
{
if (endDate.Length <= 10)
{
endDate += " 23:59:59";
}
Response theResponse = new Response();
openDataConnection();
/* DVP - RVP Data */
SqlCommand cmdDVPRVPDeliveriesWithoutIssues = new SqlCommand("ReportDVPRVPNumberOfDeliveriesWithoutIssuesWithInterval", theConnection);
cmdDVPRVPDeliveriesWithoutIssues.Parameters.AddWithValue("@dateStarted", startDate);
cmdDVPRVPDeliveriesWithoutIssues.Parameters.AddWithValue("@dateEnded", endDate);
cmdDVPRVPDeliveriesWithoutIssues.CommandType = System.Data.CommandType.StoredProcedure;
cmdDVPRVPDeliveriesWithoutIssues.CommandTimeout = 1200;
theReader = cmdDVPRVPDeliveriesWithoutIssues.ExecuteReader();
List<DVPRVPSummary> dvprvpData = new List<DVPRVPSummary>();
if (theReader.HasRows)
{
while (theReader.Read())
{
DVPRVPSummary thisRow = new DVPRVPSummary();
thisRow.dvpName = theReader["DVPOutlookName"].ToString();
thisRow.rvpName = theReader["RVPOutlookName"].ToString();
thisRow.deliveries = (int)theReader["NumberOfDeliveriesWithoutIssues"];
dvprvpData.Add(thisRow);
}
}
theReader.Close();
SqlCommand cmdDVPRVPDeliveriesWithIssues = new SqlCommand("ReportDVPRVPNumberOfDeliveriesWithIssuesWithInterval", theConnection);
cmdDVPRVPDeliveriesWithIssues.Parameters.AddWithValue("@dateStarted", startDate);
cmdDVPRVPDeliveriesWithIssues.Parameters.AddWithValue("@dateEnded", endDate);
cmdDVPRVPDeliveriesWithIssues.CommandType = System.Data.CommandType.StoredProcedure;
cmdDVPRVPDeliveriesWithIssues.CommandTimeout = 1200;
theReader = cmdDVPRVPDeliveriesWithIssues.ExecuteReader();
if (theReader.HasRows)
{
while (theReader.Read())
{
string thisRowsDVP = theReader["DVPOutlookName"].ToString();
string thisRowsRVP = theReader["RVPOutlookName"].ToString();
bool found = false;
for (int i = 0, l = dvprvpData.Count; i < l; i++)
{
DVPRVPSummary thisRowData = dvprvpData[i];
if (thisRowsDVP.Equals(thisRowData.dvpName) && thisRowsRVP.Equals(thisRowData.rvpName))
{
found = true;
int deliveriesWithIssues = (int)theReader["NumberOfDeliveriesWithIssues"];
thisRowData.deliveriesWithIssues = deliveriesWithIssues;
thisRowData.deliveries += thisRowData.deliveriesWithIssues;
}
}
if (!found)
{
DVPRVPSummary thisRow = new DVPRVPSummary();
thisRow.dvpName = thisRowsDVP;
thisRow.rvpName = thisRowsRVP;
thisRow.deliveriesWithIssues = (int)theReader["NumberOfDeliveriesWithIssues"];
thisRow.deliveries += thisRow.deliveriesWithIssues;
dvprvpData.Add(thisRow);
found = false;
}
}
}
theReader.Close();
SqlCommand cmdDVPRVPNumberOfIssues = new SqlCommand("ReportDVPRVPNumberOfIssuesWithInterval", theConnection);
cmdDVPRVPNumberOfIssues.Parameters.AddWithValue("@dateStarted", startDate);
cmdDVPRVPNumberOfIssues.Parameters.AddWithValue("@dateEnded", endDate);
cmdDVPRVPNumberOfIssues.CommandType = System.Data.CommandType.StoredProcedure;
cmdDVPRVPNumberOfIssues.CommandTimeout = 1200;
theReader = cmdDVPRVPNumberOfIssues.ExecuteReader();
if (theReader.HasRows)
{
while (theReader.Read())
{
string thisRowsDVP = theReader["DVPOutlookName"].ToString();
string thisRowsRVP = theReader["RVPOutlookName"].ToString();
bool found = false;
for (int i = 0, l = dvprvpData.Count; i < l; i++)
{
DVPRVPSummary thisRowData = dvprvpData[i];
if (thisRowsDVP.Equals(thisRowData.dvpName) && thisRowsRVP.Equals(thisRowData.rvpName))
{
found = true;
int numberOfIssues = (int)theReader["NumberOfIssues"];
thisRowData.totalReadinessIssues = numberOfIssues;
}
}
if (!found)
{
DVPRVPSummary thisRow = new DVPRVPSummary();
thisRow.dvpName = thisRowsDVP;
thisRow.rvpName = thisRowsRVP;
thisRow.totalReadinessIssues = (int)theReader["NumberOfIssues"];
dvprvpData.Add(thisRow);
found = false;
}
}
}
theReader.Close();
SqlCommand cmdDVPRVPUnitsLeftout = new SqlCommand("ReportDVPRVPUnitsLeftoutWithInterval", theConnection);
cmdDVPRVPUnitsLeftout.Parameters.AddWithValue("@dateStarted", startDate);
cmdDVPRVPUnitsLeftout.Parameters.AddWithValue("@dateEnded", endDate);
cmdDVPRVPUnitsLeftout.CommandType = System.Data.CommandType.StoredProcedure;
cmdDVPRVPUnitsLeftout.CommandTimeout = 1200;
theReader = cmdDVPRVPUnitsLeftout.ExecuteReader();
if (theReader.HasRows)
{
while (theReader.Read())
{
string thisRowsDVP = theReader["DVPOutlookName"].ToString();
string thisRowsRVP = theReader["RVPOutlookName"].ToString();
bool found = false;
for (int i = 0, l = dvprvpData.Count; i < l; i++)
{
DVPRVPSummary thisRowData = dvprvpData[i];
if (thisRowsDVP.Equals(thisRowData.dvpName) && thisRowsRVP.Equals(thisRowData.rvpName))
{
found = true;
int unitsLeftout = (int)theReader["UnitsLeftout"];
thisRowData.leftoutUnits = unitsLeftout;
}
}
if (!found)
{
DVPRVPSummary thisRow = new DVPRVPSummary();
thisRow.dvpName = thisRowsDVP;
thisRow.rvpName = thisRowsRVP;
thisRow.leftoutUnits = (int)theReader["UnitsLeftout"];
dvprvpData.Add(thisRow);
found = false;
}
}
}
theReader.Close();
SqlCommand cmdDVPRVPUnitsLeftoutCost = new SqlCommand("ReportDVPRVPUnitsLeftoutCostWithInterval", theConnection);
cmdDVPRVPUnitsLeftoutCost.Parameters.AddWithValue("@dateStarted", startDate);
cmdDVPRVPUnitsLeftoutCost.Parameters.AddWithValue("@dateEnded", endDate);
cmdDVPRVPUnitsLeftoutCost.CommandType = System.Data.CommandType.StoredProcedure;
cmdDVPRVPUnitsLeftoutCost.CommandTimeout = 1200;
theReader = cmdDVPRVPUnitsLeftoutCost.ExecuteReader();
if (theReader.HasRows)
{
while (theReader.Read())
{
string thisRowsDVP = theReader["DVPOutlookName"].ToString();
string thisRowsRVP = theReader["RVPOutlookName"].ToString();
bool found = false;
for (int i = 0, l = dvprvpData.Count; i < l; i++)
{
DVPRVPSummary thisRowData = dvprvpData[i];
if (thisRowsDVP.Equals(thisRowData.dvpName) && thisRowsRVP.Equals(thisRowData.rvpName))
{
found = true;
if (theReader["LeftoutCost"] != DBNull.Value)
{
double leftoutCost = (double)theReader["LeftoutCost"];
thisRowData.leftoutCOGS = leftoutCost;
}
}
}
if (!found)
{
DVPRVPSummary thisRow = new DVPRVPSummary();
thisRow.dvpName = thisRowsDVP;
thisRow.rvpName = thisRowsRVP;
thisRow.leftoutCOGS = (double)theReader["LeftoutCost"];
dvprvpData.Add(thisRow);
found = false;
}
}
}
theReader.Close();
SqlCommand cmdDVPRVPUnitsBackhauled = new SqlCommand("ReportDVPRVPUnitsBackhauledWithInterval", theConnection);
cmdDVPRVPUnitsBackhauled.Parameters.AddWithValue("@dateStarted", startDate);
cmdDVPRVPUnitsBackhauled.Parameters.AddWithValue("@dateEnded", endDate);
cmdDVPRVPUnitsBackhauled.CommandType = System.Data.CommandType.StoredProcedure;
cmdDVPRVPUnitsBackhauled.CommandTimeout = 1200;
theReader = cmdDVPRVPUnitsBackhauled.ExecuteReader();
if (theReader.HasRows)
{
while (theReader.Read())
{
string thisRowsDVP = theReader["DVPOutlookName"].ToString();
string thisRowsRVP = theReader["RVPOutlookName"].ToString();
bool found = false;
for (int i = 0, l = dvprvpData.Count; i < l; i++)
{
DVPRVPSummary thisRowData = dvprvpData[i];
if (thisRowsDVP.Equals(thisRowData.dvpName) && thisRowsRVP.Equals(thisRowData.rvpName))
{
found = true;
int unitsBackhauled = (int)theReader["UnitsBackhauled"];
thisRowData.dairyBackhaulUnits = unitsBackhauled;
}
}
if (!found)
{
DVPRVPSummary thisRow = new DVPRVPSummary();
thisRow.dvpName = thisRowsDVP;
thisRow.rvpName = thisRowsRVP;
thisRow.dairyBackhaulUnits = (int)theReader["UnitsBackhauled"];
dvprvpData.Add(thisRow);
found = false;
}
}
}
theReader.Close();
SqlCommand cmdDVPRVPUnitsBackhauledCost = new SqlCommand("ReportDVPRVPUnitsBackhauledCostWithInterval", theConnection);
cmdDVPRVPUnitsBackhauledCost.Parameters.AddWithValue("@dateStarted", startDate);
cmdDVPRVPUnitsBackhauledCost.Parameters.AddWithValue("@dateEnded", endDate);
cmdDVPRVPUnitsBackhauledCost.CommandType = System.Data.CommandType.StoredProcedure;
cmdDVPRVPUnitsBackhauledCost.CommandTimeout = 1200;
theReader = cmdDVPRVPUnitsBackhauledCost.ExecuteReader();
if (theReader.HasRows)
{
while (theReader.Read())
{
string thisRowsDVP = theReader["DVPOutlookName"].ToString();
string thisRowsRVP = theReader["RVPOutlookName"].ToString();
bool found = false;
for (int i = 0, l = dvprvpData.Count; i < l; i++)
{
DVPRVPSummary thisRowData = dvprvpData[i];
if (thisRowsDVP.Equals(thisRowData.dvpName) && thisRowsRVP.Equals(thisRowData.rvpName))
{
found = true;
if (theReader["BackhaulCost"] != DBNull.Value)
{
double backhaulCost = (double)theReader["BackhaulCost"];
thisRowData.dairyBackhaulCOGS = backhaulCost;
}
}
}
if (!found)
{
DVPRVPSummary thisRow = new DVPRVPSummary();
thisRow.dvpName = thisRowsDVP;
thisRow.rvpName = thisRowsRVP;
thisRow.dairyBackhaulCOGS = (double)theReader["BackhaulCost"];
dvprvpData.Add(thisRow);
found = false;
}
}
}
theReader.Close();
/* RD-DM Data */
SqlCommand cmdRDDMDeliveriesWithoutIssues = new SqlCommand("ReportRDDMNumberOfDeliveriesWithoutIssuesWithInterval", theConnection);
cmdRDDMDeliveriesWithoutIssues.Parameters.AddWithValue("@dateStarted", startDate);
cmdRDDMDeliveriesWithoutIssues.Parameters.AddWithValue("@dateEnded", endDate);
cmdRDDMDeliveriesWithoutIssues.CommandType = System.Data.CommandType.StoredProcedure;
cmdRDDMDeliveriesWithoutIssues.CommandTimeout = 1200;
theReader = cmdRDDMDeliveriesWithoutIssues.ExecuteReader();
List<RDDMSummary> rddmData = new List<RDDMSummary>();
if (theReader.HasRows)
{
while (theReader.Read())
{
RDDMSummary thisRow = new RDDMSummary();
thisRow.rdName = theReader["RDOutlookName"].ToString();
thisRow.dmName = theReader["DMOutlookName"].ToString();
thisRow.deliveries = (int)theReader["NumberOfDeliveriesWithoutIssues"];
rddmData.Add(thisRow);
}
}
theReader.Close();
SqlCommand cmdRDDMDeliveriesWithIssues = new SqlCommand("ReportRDDMNumberOfDeliveriesWithIssuesWithInterval", theConnection);
cmdRDDMDeliveriesWithIssues.Parameters.AddWithValue("@dateStarted", startDate);
cmdRDDMDeliveriesWithIssues.Parameters.AddWithValue("@dateEnded", endDate);
cmdRDDMDeliveriesWithIssues.CommandType = System.Data.CommandType.StoredProcedure;
cmdRDDMDeliveriesWithIssues.CommandTimeout = 1200;
theReader = cmdRDDMDeliveriesWithIssues.ExecuteReader();
if (theReader.HasRows)
{
while (theReader.Read())
{
string thisRowsRD = theReader["RDOutlookName"].ToString();
string thisRowsDM = theReader["DMOutlookName"].ToString();
bool found = false;
for (int i = 0, l = rddmData.Count; i < l; i++)
{
RDDMSummary thisRowData = rddmData[i];
if (thisRowsRD.Equals(thisRowData.rdName) && thisRowsDM.Equals(thisRowData.dmName))
{
found = true;
int deliveriesWithIssues = (int)theReader["NumberOfDeliveriesWithIssues"];
thisRowData.deliveriesWithIssues = deliveriesWithIssues;
thisRowData.deliveries += thisRowData.deliveriesWithIssues;
}
}
if (!found)
{
RDDMSummary thisRow = new RDDMSummary();
thisRow.rdName = theReader["RDOutlookName"].ToString();
thisRow.dmName = theReader["DMOutlookName"].ToString();
thisRow.deliveriesWithIssues = (int)theReader["NumberOfDeliveriesWithIssues"];
thisRow.deliveries += thisRow.deliveriesWithIssues;
rddmData.Add(thisRow);
found = false;
}
}
}
theReader.Close();
SqlCommand cmdRDDMNumberOfIssues = new SqlCommand("ReportRDDMNumberOfIssuesWithInterval", theConnection);
cmdRDDMNumberOfIssues.Parameters.AddWithValue("@dateStarted", startDate);
cmdRDDMNumberOfIssues.Parameters.AddWithValue("@dateEnded", endDate);
cmdRDDMNumberOfIssues.CommandType = System.Data.CommandType.StoredProcedure;
cmdRDDMNumberOfIssues.CommandTimeout = 1200;
theReader = cmdRDDMNumberOfIssues.ExecuteReader();
if (theReader.HasRows)
{
while (theReader.Read())
{
string thisRowsRD = theReader["RDOutlookName"].ToString();
string thisRowsDM = theReader["DMOutlookName"].ToString();
bool found = false;
for (int i = 0, l = rddmData.Count; i < l; i++)
{
RDDMSummary thisRowData = rddmData[i];
if (thisRowsRD.Equals(thisRowData.rdName) && thisRowsDM.Equals(thisRowData.dmName))
{
found = true;
int numberOfIssues = (int)theReader["NumberOfIssues"];
thisRowData.totalReadinessIssues = numberOfIssues;
}
}
if (!found)
{
RDDMSummary thisRow = new RDDMSummary();
thisRow.rdName = theReader["RDOutlookName"].ToString();
thisRow.dmName = theReader["DMOutlookName"].ToString();
thisRow.totalReadinessIssues = (int)theReader["NumberOfIssues"];
rddmData.Add(thisRow);
found = false;
}
}
}
theReader.Close();
SqlCommand cmdRDDMUnitsBackhauled = new SqlCommand("ReportRDDMUnitsBackhauledWithInterval", theConnection);
cmdRDDMUnitsBackhauled.Parameters.AddWithValue("@dateStarted", startDate);
cmdRDDMUnitsBackhauled.Parameters.AddWithValue("@dateEnded", endDate);
cmdRDDMUnitsBackhauled.CommandType = System.Data.CommandType.StoredProcedure;
cmdRDDMUnitsBackhauled.CommandTimeout = 1200;
theReader = cmdRDDMUnitsBackhauled.ExecuteReader();
if (theReader.HasRows)
{
while (theReader.Read())
{
string thisRowsRD = theReader["RDOutlookName"].ToString();
string thisRowsDM = theReader["DMOutlookName"].ToString();
bool found = false;
for (int i = 0, l = rddmData.Count; i < l; i++)
{
RDDMSummary thisRowData = rddmData[i];
if (thisRowsRD.Equals(thisRowData.rdName) && thisRowsDM.Equals(thisRowData.dmName))
{
found = true;
int unitsBackhauled = (int)theReader["UnitsBackhauled"];
thisRowData.dairyBackhaulUnits = unitsBackhauled;
}
}
if (!found)
{
RDDMSummary thisRow = new RDDMSummary();
thisRow.rdName = theReader["RDOutlookName"].ToString();
thisRow.dmName = theReader["DMOutlookName"].ToString();
thisRow.dairyBackhaulUnits = (int)theReader["UnitsBackhauled"];
rddmData.Add(thisRow);
found = false;
}
}
}
theReader.Close();
SqlCommand cmdRDDMUnitsBackhauledCost = new SqlCommand("ReportRDDMUnitsBackhauledCostWithInterval", theConnection);
cmdRDDMUnitsBackhauledCost.Parameters.AddWithValue("@dateStarted", startDate);
cmdRDDMUnitsBackhauledCost.Parameters.AddWithValue("@dateEnded", endDate);
cmdRDDMUnitsBackhauledCost.CommandType = System.Data.CommandType.StoredProcedure;
cmdRDDMUnitsBackhauledCost.CommandTimeout = 1200;
theReader = cmdRDDMUnitsBackhauledCost.ExecuteReader();
if (theReader.HasRows)
{
while (theReader.Read())
{
string thisRowsRD = theReader["RDOutlookName"].ToString();
string thisRowsDM = theReader["DMOutlookName"].ToString();
bool found = false;
for (int i = 0, l = rddmData.Count; i < l; i++)
{
RDDMSummary thisRowData = rddmData[i];
if (thisRowsRD.Equals(thisRowData.rdName) && thisRowsDM.Equals(thisRowData.dmName))
{
found = true;
if (theReader["BackhaulCost"] != DBNull.Value)
{
double backhaulCost = (double)theReader["BackhaulCost"];
thisRowData.dairyBackhaulCOGS = backhaulCost;
}
}
}
if (!found)
{
RDDMSummary thisRow = new RDDMSummary();
thisRow.rdName = theReader["RDOutlookName"].ToString();
thisRow.dmName = theReader["DMOutlookName"].ToString();
thisRow.dairyBackhaulCOGS = (double)theReader["BackhaulCost"];
rddmData.Add(thisRow);
found = false;
}
}
}
theReader.Close();
SqlCommand cmdRDDMNumberOfIssuesOne = new SqlCommand("ReportRDDMGroupOneIssuesWithInterval", theConnection);
cmdRDDMNumberOfIssuesOne.Parameters.AddWithValue("@dateStarted", startDate);
cmdRDDMNumberOfIssuesOne.Parameters.AddWithValue("@dateEnded", endDate);
cmdRDDMNumberOfIssuesOne.CommandType = System.Data.CommandType.StoredProcedure;
cmdRDDMNumberOfIssuesOne.CommandTimeout = 1200;
theReader = cmdRDDMNumberOfIssues.ExecuteReader();
if (theReader.HasRows)
{
while (theReader.Read())
{
string thisRowsRD = theReader["RDOutlookName"].ToString();
string thisRowsDM = theReader["DMOutlookName"].ToString();
bool found = false;
for (int i = 0, l = rddmData.Count; i < l; i++)
{
RDDMSummary thisRowData = rddmData[i];
if (thisRowsRD.Equals(thisRowData.rdName) && thisRowsDM.Equals(thisRowData.dmName))
{
found = true;
int numberOfIssues = (int)theReader["NumberOfIssues"];
thisRowData.totalSecurityFacilityIssues = numberOfIssues;
}
}
if (!found)
{
RDDMSummary thisRow = new RDDMSummary();
thisRow.rdName = theReader["RDOutlookName"].ToString();
thisRow.dmName = theReader["DMOutlookName"].ToString();
thisRow.totalSecurityFacilityIssues = (int)theReader["NumberOfIssues"];
rddmData.Add(thisRow);
found = false;
}
}
}
theReader.Close();
SqlCommand cmdRDDMNumberOfIssuesTwo = new SqlCommand("ReportRDDMGroupTwoIssuesWithInterval", theConnection);
cmdRDDMNumberOfIssuesTwo.Parameters.AddWithValue("@dateStarted", startDate);
cmdRDDMNumberOfIssuesTwo.Parameters.AddWithValue("@dateEnded", endDate);
cmdRDDMNumberOfIssuesTwo.CommandType = System.Data.CommandType.StoredProcedure;
cmdRDDMNumberOfIssuesTwo.CommandTimeout = 1200;
theReader = cmdRDDMNumberOfIssuesTwo.ExecuteReader();
if (theReader.HasRows)
{
while (theReader.Read())
{
string thisRowsRD = theReader["RDOutlookName"].ToString();
string thisRowsDM = theReader["DMOutlookName"].ToString();
bool found = false;
for (int i = 0, l = rddmData.Count; i < l; i++)
{
RDDMSummary thisRowData = rddmData[i];
if (thisRowsRD.Equals(thisRowData.rdName) && thisRowsDM.Equals(thisRowData.dmName))
{
found = true;
int numberOfIssues = (int)theReader["NumberOfIssues"];
thisRowData.totalCapacityIssues = numberOfIssues;
}
}
if (!found)
{
RDDMSummary thisRow = new RDDMSummary();
thisRow.rdName = theReader["RDOutlookName"].ToString();
thisRow.dmName = theReader["DMOutlookName"].ToString();
thisRow.totalCapacityIssues = (int)theReader["NumberOfIssues"];
rddmData.Add(thisRow);
found = false;
}
}
}
theReader.Close();
SqlCommand cmdRDDMNumberOfIssuesThree = new SqlCommand("ReportRDDMGroupThreeIssuesWithInterval", theConnection);
cmdRDDMNumberOfIssuesThree.Parameters.AddWithValue("@dateStarted", startDate);
cmdRDDMNumberOfIssuesThree.Parameters.AddWithValue("@dateEnded", endDate);
cmdRDDMNumberOfIssuesThree.CommandType = System.Data.CommandType.StoredProcedure;
cmdRDDMNumberOfIssuesThree.CommandTimeout = 1200;
theReader = cmdRDDMNumberOfIssuesThree.ExecuteReader();
if (theReader.HasRows)
{
while (theReader.Read())
{
string thisRowsRD = theReader["RDOutlookName"].ToString();
string thisRowsDM = theReader["DMOutlookName"].ToString();
bool found = false;
for (int i = 0, l = rddmData.Count; i < l; i++)
{
RDDMSummary thisRowData = rddmData[i];
if (thisRowsRD.Equals(thisRowData.rdName) && thisRowsDM.Equals(thisRowData.dmName))
{
found = true;
int numberOfIssues = (int)theReader["NumberOfIssues"];
thisRowData.totalProductivityIssues = numberOfIssues;
}
}
if (!found)
{
RDDMSummary thisRow = new RDDMSummary();
thisRow.rdName = theReader["RDOutlookName"].ToString();
thisRow.dmName = theReader["DMOutlookName"].ToString();
thisRow.totalProductivityIssues = (int)theReader["NumberOfIssues"];
rddmData.Add(thisRow);
found = false;
}
}
}
theReader.Close();
closeDataConnection();
try
{
string newFilePath = CopyReportTemplate("field");
/*string oleConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + newFilePath + ";Extended Properties=\"Excel 8.0;\"";
OleDbConnection oleConnection = new OleDbConnection(oleConnectionString);
oleConnection.Open();*/
openDataConnection();
SqlCommand cmdReport = new SqlCommand("ReportStoresNotReadyWithInterval", theConnection);
cmdReport.Parameters.AddWithValue("@dateStarted", startDate);
cmdReport.Parameters.AddWithValue("@dateEnded", endDate);
cmdReport.CommandType = System.Data.CommandType.StoredProcedure;
cmdReport.CommandTimeout = 1200;
theReader = cmdReport.ExecuteReader();
FileStream template = new FileStream(newFilePath, FileMode.Open, FileAccess.Read);
//FileStream fileRead = new FileStream(destFile, FileMode.Create, FileAccess.ReadWrite);
//IWorkbook theWorkbook = new XSSFWorkbook(fileRead);
//Create a stream of .xlsx file contained within my project using reflection
//EPPlusTest = Namespace/Project
//templates = folder
//VendorTemplate.xlsx = file
//ExcelPackage has a constructor that only requires a stream.
ExcelPackage pck = new OfficeOpenXml.ExcelPackage(template);
ExcelWorkbook workBook = pck.Workbook;
//fileRead.Close();
template.Close();
if (theReader.HasRows)
{
int rowIndex = 1;
//ISheet notReadySheet = theWorkbook.GetSheet("DetailedViewNotReady");
ExcelWorksheet notReadySheet = workBook.Worksheets["DetailedViewNotReady"];
//notReadySheet.ForceFormulaRecalculation = true;
string photoVal = "";
//IRow crow = null;
while (theReader.Read())
{
rowIndex++;
//crow = notReadySheet.CreateRow(rowIndex);
//DateTime todaysDateTime = DateTime.Today;
//DateTime thisEntrysDateTime = Convert.ToDateTime(theReader[0].ToString());
//TimeSpan timeDifference = todaysDateTime - thisEntrysDateTime;
//int differenceInDays = timeDifference.Days;
photoVal = "";
notReadySheet.Cells[rowIndex, 1].Value = theReader[0].ToString();
notReadySheet.Cells[rowIndex, 2].Value = theReader[1].ToString();
notReadySheet.Cells[rowIndex, 3].Value = theReader[2].ToString();
notReadySheet.Cells[rowIndex, 4].Value = theReader[3].ToString();
notReadySheet.Cells[rowIndex, 5].Value = theReader[4].ToString();
notReadySheet.Cells[rowIndex, 6].Value = theReader[5].ToString();
notReadySheet.Cells[rowIndex, 7].Value = theReader[6].ToString();
notReadySheet.Cells[rowIndex, 8].Value = theReader[7].ToString();
notReadySheet.Cells[rowIndex, 9].Value = theReader[8].ToString();
notReadySheet.Cells[rowIndex, 10].Value = theReader[9].ToString();
notReadySheet.Cells[rowIndex, 11].Value = theReader[10].ToString();
/* notReadySheet.Cells[rowIndex, 12].Value = theReader[11].ToString();
notReadySheet.Cells[rowIndex, 13].Value = getCellFriendlyText(theReader[12].ToString());
if (differenceInDays <= 21 && theReader[13].ToString() != null && !theReader[13].ToString().Equals(""))
{
photoVal += ",'" + baseURLForPhotoLink + theReader[13].ToString() + "'";
}
else
{
photoVal += ",'No Photos Available'";
}
notReadySheet.Cells[rowIndex, 14].Value = photoVal;
*/
notReadySheet.Cells[rowIndex, 12].Value = theReader[11].ToString();
notReadySheet.Cells[rowIndex, 13].Value = Math.Round(Convert.ToDecimal(theReader[14].ToString()), 2).ToString();
notReadySheet.Cells[rowIndex, 14].Value = getCellFriendlyText(theReader[12].ToString());
if (theReader[13].ToString() != null && !theReader[13].ToString().Equals(""))
{
photoVal += "" + baseURLForPhotoLink + theReader[13].ToString() + "";
}
else
{
photoVal += "No Photos Available";
}
notReadySheet.Cells[rowIndex, 15].Value = photoVal;
}
theResponse.statusCode = 0;
theResponse.statusDescription = extractFilename(newFilePath);
}
else
{
theResponse.statusCode = 1;
theResponse.statusDescription = "There is no data logged between the dates that were selected";
}
theReader.Close();
SqlCommand cmdReportNotReady = new SqlCommand("ReportStoresReadyWithInterval", theConnection);
cmdReportNotReady.Parameters.AddWithValue("@dateStarted", startDate);
cmdReportNotReady.Parameters.AddWithValue("@dateEnded", endDate);
cmdReportNotReady.CommandType = System.Data.CommandType.StoredProcedure;
cmdReportNotReady.CommandTimeout = 1200;
theReader = cmdReportNotReady.ExecuteReader();
if (theReader.HasRows)
{
int rowIndex = 1;
ExcelWorksheet readySheet = workBook.Worksheets["DetailedViewReady"];
while (theReader.Read())
{
rowIndex++;
readySheet.Cells[rowIndex, 1].Value = theReader[0].ToString();
readySheet.Cells[rowIndex, 2].Value = theReader[1].ToString();
readySheet.Cells[rowIndex, 3].Value = theReader[2].ToString();
readySheet.Cells[rowIndex, 4].Value = theReader[3].ToString();
readySheet.Cells[rowIndex, 5].Value = theReader[4].ToString();
}
theResponse.statusCode = 0;
theResponse.statusDescription = extractFilename(newFilePath);
}
else
{
theResponse.statusCode = 1;
theResponse.statusDescription = "There is no data logged between the dates that were selected";
}
theReader.Close();
/*if (theReader.HasRows)
{
while (theReader.Read())
{
DateTime todaysDateTime = DateTime.Today;
DateTime thisEntrysDateTime = Convert.ToDateTime(theReader[0].ToString());
TimeSpan timeDifference = todaysDateTime - thisEntrysDateTime;
int differenceInDays = timeDifference.Days;
string values = "'" + theReader[0] + "','" + theReader[1] + "','" + theReader[2] + "','" + theReader[3] + "','" + theReader[4] + "','" + theReader[5] + "','" + theReader[6] + "','" + theReader[7] + "','" + theReader[8] + "','" + theReader[9] + "','" + theReader[10] + "','" + theReader[11] + "','" + getCellFriendlyText(theReader[12].ToString()) + "'";
if (differenceInDays <= 21 && theReader[13].ToString() != null && !theReader[13].ToString().Equals(""))
{
values += ",'" + baseURLForPhotoLink + theReader[13].ToString() + "'";
}
else
{
values += ",'No Photos Available'";
}
OleDbCommand oleCommand = new OleDbCommand();
string sqlCommand = "INSERT INTO [DetailedViewNotReady$] (InStoreDate, StoreNumber, Provider, CDC, Route, DSVP, RVP, RD, DM, ReasonCode, Photos, UnitsBackhauled, Comments, PhotoLink) VALUES (" + values + ")";
oleCommand.CommandText = sqlCommand;
oleCommand.Connection = oleConnection;
oleCommand.ExecuteNonQuery();
}
theResponse.statusCode = 0;
theResponse.statusDescription = newFilePath;
}
else
{
theResponse.statusCode = 1;
theResponse.statusDescription = "There is no data logged between the dates that were selected";
}
theReader.Close();
SqlCommand cmdReportNotReady = new SqlCommand("ReportStoresReadyWithInterval", theConnection);
cmdReportNotReady.Parameters.AddWithValue("@dateStarted", startDate);
cmdReportNotReady.Parameters.AddWithValue("@dateEnded", endDate);
cmdReportNotReady.CommandType = System.Data.CommandType.StoredProcedure;
theReader = cmdReportNotReady.ExecuteReader();
if (theReader.HasRows)
{
while (theReader.Read())
{
/* DateTime todaysDateTime = DateTime.Today;
DateTime thisEntrysDateTime = Convert.ToDateTime(theReader[0].ToString());
TimeSpan timeDifference = todaysDateTime - thisEntrysDateTime;
int differenceInDays = timeDifference.Days;
string values = "'" + theReader[0] + "','" + theReader[1] + "','" + theReader[2] + "','" + theReader[3] + "','" + theReader[4] + "'";
OleDbCommand oleCommand = new OleDbCommand();
string sqlCommand = "INSERT INTO [DetailedViewReady$] (InStoreDate, StoreNumber, Provider, CDC, Route) VALUES (" + values + ")";
oleCommand.CommandText = sqlCommand;
oleCommand.Connection = oleConnection;
oleCommand.ExecuteNonQuery();
}
theResponse.statusCode = 0;
theResponse.statusDescription = newFilePath;
}
theReader.Close();
oleConnection.Close();*/
ExcelWorksheet sheetSummary = workBook.Worksheets["DVPRVPSummary"];
sheetSummary.Cells["A2:C2"].Merge = true;
sheetSummary.Cells[2, 1].Value = "All Data";
if (dvprvpData != null && dvprvpData.Count > 0)
{
int currentRowIndex = 3;
for (int i = 0, l = dvprvpData.Count; i < l; i++)
{
DVPRVPSummary thisRowsData = dvprvpData[i];
thisRowsData.percentageStoresReady = Math.Round((100 - ((double)thisRowsData.deliveriesWithIssues / (double)thisRowsData.deliveries) * 100.0), 2);
thisRowsData.leftoutCOGS = Math.Round(thisRowsData.leftoutCOGS, 2);
thisRowsData.dairyBackhaulCOGS = Math.Round(thisRowsData.dairyBackhaulCOGS, 2);
sheetSummary.Cells[currentRowIndex, 1].Value = thisRowsData.dvpName;
sheetSummary.Cells[currentRowIndex, 2].Value = thisRowsData.rvpName;
sheetSummary.Cells[currentRowIndex, 3].Value = thisRowsData.percentageStoresReady.ToString() + " %";
// sheetSummary.Cells[currentRowIndex, 4].Value = thisRowsData.leftoutUnits;
// sheetSummary.Cells[currentRowIndex, 5].Value = "$ " + thisRowsData.leftoutCOGS.ToString();
// sheetSummary.Cells[currentRowIndex, 6].Value = thisRowsData.dairyBackhaulUnits;
sheetSummary.Cells[currentRowIndex, 4].Value = "$ " + thisRowsData.dairyBackhaulCOGS.ToString();
sheetSummary.Cells[currentRowIndex, 5].Value = thisRowsData.deliveries;
sheetSummary.Cells[currentRowIndex, 6].Value = thisRowsData.deliveriesWithIssues;
sheetSummary.Cells[currentRowIndex, 7].Value = thisRowsData.totalReadinessIssues;
currentRowIndex++;
}
theResponse.statusCode = 0;
theResponse.statusDescription = extractFilename(newFilePath);
}
else
{
theResponse.statusCode = 1;
theResponse.statusDescription = "There is no data logged between the dates that were selected";
}
ExcelWorksheet sheetRDDMSummary = workBook.Worksheets["RDDMSummary"];
sheetRDDMSummary.Cells["A2:C2"].Merge = true;
sheetRDDMSummary.Cells[2, 1].Value = "All Data";
if (rddmData != null && rddmData.Count > 0)
{
int currentRowIndex = 3;
//IRow currentRow = null;
for (int i = 0, l = rddmData.Count; i < l; i++)
{
RDDMSummary thisRowsData = rddmData[i];
thisRowsData.percentageStoresReady = Math.Round((100 - ((double)thisRowsData.deliveriesWithIssues / (double)thisRowsData.deliveries) * 100.0), 2);
thisRowsData.dairyBackhaulCOGS = Math.Round(thisRowsData.dairyBackhaulCOGS, 2);
// currentRow = sheetRDDMSummary.CreateRow(currentRowIndex);
sheetRDDMSummary.Cells[currentRowIndex, 1].Value = thisRowsData.rdName;
sheetRDDMSummary.Cells[currentRowIndex, 2].Value = thisRowsData.dmName;
sheetRDDMSummary.Cells[currentRowIndex, 3].Value = thisRowsData.percentageStoresReady.ToString() + " %";
// sheetRDDMSummary.Cells[currentRowIndex, 4].Value = thisRowsData.dairyBackhaulUnits;
sheetRDDMSummary.Cells[currentRowIndex, 4].Value = "$ " + thisRowsData.dairyBackhaulCOGS.ToString();
sheetRDDMSummary.Cells[currentRowIndex, 5].Value = thisRowsData.deliveries;
sheetRDDMSummary.Cells[currentRowIndex, 6].Value = thisRowsData.deliveriesWithIssues;
sheetRDDMSummary.Cells[currentRowIndex, 7].Value = thisRowsData.totalReadinessIssues;
sheetRDDMSummary.Cells[currentRowIndex, 8].Value = thisRowsData.totalSecurityFacilityIssues;
sheetRDDMSummary.Cells[currentRowIndex, 9].Value = thisRowsData.totalCapacityIssues;
sheetRDDMSummary.Cells[currentRowIndex, 10].Value = thisRowsData.totalProductivityIssues;
currentRowIndex++;
}
theResponse.statusCode = 0;
theResponse.statusDescription = extractFilename(newFilePath);
}
else
{
theResponse.statusCode = 1;
theResponse.statusDescription = "There is no data logged between the dates that were selected";
}
FileStream fileSave = new FileStream(newFilePath, FileMode.Create);
//theWorkbook.Write(fileSave);
pck.SaveAs(fileSave);
fileSave.Close();
closeDataConnection();
}
catch (Exception _exception)
{
theResponse.statusCode = 6;
theResponse.statusDescription = _exception.Message + " " + _exception.StackTrace;
}
return theResponse;
}
else
{
return ReportFieldReadiness();
}
}
public Response DotNetReportFieldReadiness(string startDate, string endDate, string startHour, string endHour)
{
startDate = startDate + " " + startHour + ":00:00";
endDate = endDate + " " + endHour + ":00:00";
if (validateDate(startDate) && validateDate(endDate))
{
if (endDate.Length <= 10)
{
endDate += " 23:59:59";
}
Response theResponse = new Response();
openDataConnection();
/* DVP - RVP Data */
SqlCommand cmdDVPRVPDeliveriesWithoutIssues = new SqlCommand("ReportDVPRVPNumberOfDeliveriesWithoutIssuesWithInterval", theConnection);
cmdDVPRVPDeliveriesWithoutIssues.Parameters.AddWithValue("@dateStarted", startDate);
cmdDVPRVPDeliveriesWithoutIssues.Parameters.AddWithValue("@dateEnded", endDate);
cmdDVPRVPDeliveriesWithoutIssues.CommandType = System.Data.CommandType.StoredProcedure;
cmdDVPRVPDeliveriesWithoutIssues.CommandTimeout = 1200;
theReader = cmdDVPRVPDeliveriesWithoutIssues.ExecuteReader();
List<DVPRVPSummary> dvprvpData = new List<DVPRVPSummary>();
if (theReader.HasRows)
{
while (theReader.Read())
{
DVPRVPSummary thisRow = new DVPRVPSummary();
thisRow.dvpName = theReader["DVPOutlookName"].ToString();
thisRow.rvpName = theReader["RVPOutlookName"].ToString();
thisRow.deliveries = (int)theReader["NumberOfDeliveriesWithoutIssues"];
dvprvpData.Add(thisRow);
}
}
theReader.Close();
SqlCommand cmdDVPRVPDeliveriesWithIssues = new SqlCommand("ReportDVPRVPNumberOfDeliveriesWithIssuesWithInterval", theConnection);
cmdDVPRVPDeliveriesWithIssues.Parameters.AddWithValue("@dateStarted", startDate);
cmdDVPRVPDeliveriesWithIssues.Parameters.AddWithValue("@dateEnded", endDate);
cmdDVPRVPDeliveriesWithIssues.CommandType = System.Data.CommandType.StoredProcedure;
cmdDVPRVPDeliveriesWithIssues.CommandTimeout = 1200;
theReader = cmdDVPRVPDeliveriesWithIssues.ExecuteReader();
if (theReader.HasRows)
{
while (theReader.Read())
{
string thisRowsDVP = theReader["DVPOutlookName"].ToString();
string thisRowsRVP = theReader["RVPOutlookName"].ToString();
bool found = false;
for (int i = 0, l = dvprvpData.Count; i < l; i++)
{
DVPRVPSummary thisRowData = dvprvpData[i];
if (thisRowsDVP.Equals(thisRowData.dvpName) && thisRowsRVP.Equals(thisRowData.rvpName))
{
found = true;
int deliveriesWithIssues = (int)theReader["NumberOfDeliveriesWithIssues"];
thisRowData.deliveriesWithIssues = deliveriesWithIssues;
thisRowData.deliveries += thisRowData.deliveriesWithIssues;
}
}
if (!found)
{
DVPRVPSummary thisRow = new DVPRVPSummary();
thisRow.dvpName = thisRowsDVP;
thisRow.rvpName = thisRowsRVP;
thisRow.deliveriesWithIssues = (int)theReader["NumberOfDeliveriesWithIssues"];
thisRow.deliveries += thisRow.deliveriesWithIssues;
dvprvpData.Add(thisRow);
found = false;
}
}
}
theReader.Close();
SqlCommand cmdDVPRVPNumberOfIssues = new SqlCommand("ReportDVPRVPNumberOfIssuesWithInterval", theConnection);
cmdDVPRVPNumberOfIssues.Parameters.AddWithValue("@dateStarted", startDate);
cmdDVPRVPNumberOfIssues.Parameters.AddWithValue("@dateEnded", endDate);
cmdDVPRVPNumberOfIssues.CommandType = System.Data.CommandType.StoredProcedure;
cmdDVPRVPNumberOfIssues.CommandTimeout = 1200;
theReader = cmdDVPRVPNumberOfIssues.ExecuteReader();
if (theReader.HasRows)
{
while (theReader.Read())
{
string thisRowsDVP = theReader["DVPOutlookName"].ToString();
string thisRowsRVP = theReader["RVPOutlookName"].ToString();
bool found = false;
for (int i = 0, l = dvprvpData.Count; i < l; i++)
{
DVPRVPSummary thisRowData = dvprvpData[i];
if (thisRowsDVP.Equals(thisRowData.dvpName) && thisRowsRVP.Equals(thisRowData.rvpName))
{
found = true;
int numberOfIssues = (int)theReader["NumberOfIssues"];
thisRowData.totalReadinessIssues = numberOfIssues;
}
}
if (!found)
{
DVPRVPSummary thisRow = new DVPRVPSummary();
thisRow.dvpName = thisRowsDVP;
thisRow.rvpName = thisRowsRVP;
thisRow.totalReadinessIssues = (int)theReader["NumberOfIssues"];
dvprvpData.Add(thisRow);
found = false;
}
}
}
theReader.Close();
SqlCommand cmdDVPRVPUnitsLeftout = new SqlCommand("ReportDVPRVPUnitsLeftoutWithInterval", theConnection);
cmdDVPRVPUnitsLeftout.Parameters.AddWithValue("@dateStarted", startDate);
cmdDVPRVPUnitsLeftout.Parameters.AddWithValue("@dateEnded", endDate);
cmdDVPRVPUnitsLeftout.CommandType = System.Data.CommandType.StoredProcedure;
cmdDVPRVPUnitsLeftout.CommandTimeout = 1200;
theReader = cmdDVPRVPUnitsLeftout.ExecuteReader();
if (theReader.HasRows)
{
while (theReader.Read())
{
string thisRowsDVP = theReader["DVPOutlookName"].ToString();
string thisRowsRVP = theReader["RVPOutlookName"].ToString();
bool found = false;
for (int i = 0, l = dvprvpData.Count; i < l; i++)
{
DVPRVPSummary thisRowData = dvprvpData[i];
if (thisRowsDVP.Equals(thisRowData.dvpName) && thisRowsRVP.Equals(thisRowData.rvpName))
{
found = true;
int unitsLeftout = (int)theReader["UnitsLeftout"];
thisRowData.leftoutUnits = unitsLeftout;
}
}
if (!found)
{
DVPRVPSummary thisRow = new DVPRVPSummary();
thisRow.dvpName = thisRowsDVP;
thisRow.rvpName = thisRowsRVP;
thisRow.leftoutUnits = (int)theReader["UnitsLeftout"];
dvprvpData.Add(thisRow);
found = false;
}
}
}
theReader.Close();
SqlCommand cmdDVPRVPUnitsLeftoutCost = new SqlCommand("ReportDVPRVPUnitsLeftoutCostWithInterval", theConnection);
cmdDVPRVPUnitsLeftoutCost.Parameters.AddWithValue("@dateStarted", startDate);
cmdDVPRVPUnitsLeftoutCost.Parameters.AddWithValue("@dateEnded", endDate);
cmdDVPRVPUnitsLeftoutCost.CommandType = System.Data.CommandType.StoredProcedure;
cmdDVPRVPUnitsLeftoutCost.CommandTimeout = 1200;
theReader = cmdDVPRVPUnitsLeftoutCost.ExecuteReader();
if (theReader.HasRows)
{
while (theReader.Read())
{
string thisRowsDVP = theReader["DVPOutlookName"].ToString();
string thisRowsRVP = theReader["RVPOutlookName"].ToString();
bool found = false;
for (int i = 0, l = dvprvpData.Count; i < l; i++)
{
DVPRVPSummary thisRowData = dvprvpData[i];
if (thisRowsDVP.Equals(thisRowData.dvpName) && thisRowsRVP.Equals(thisRowData.rvpName))
{
found = true;
if (theReader["LeftoutCost"] != DBNull.Value)
{
double leftoutCost = (double)theReader["LeftoutCost"];
thisRowData.leftoutCOGS = leftoutCost;
}
}
}
if (!found)
{
DVPRVPSummary thisRow = new DVPRVPSummary();
thisRow.dvpName = thisRowsDVP;
thisRow.rvpName = thisRowsRVP;
thisRow.leftoutCOGS = (double)theReader["LeftoutCost"];
dvprvpData.Add(thisRow);
found = false;
}
}
}
theReader.Close();
SqlCommand cmdDVPRVPUnitsBackhauled = new SqlCommand("ReportDVPRVPUnitsBackhauledWithInterval", theConnection);
cmdDVPRVPUnitsBackhauled.Parameters.AddWithValue("@dateStarted", startDate);
cmdDVPRVPUnitsBackhauled.Parameters.AddWithValue("@dateEnded", endDate);
cmdDVPRVPUnitsBackhauled.CommandType = System.Data.CommandType.StoredProcedure;
cmdDVPRVPUnitsBackhauled.CommandTimeout = 1200;
theReader = cmdDVPRVPUnitsBackhauled.ExecuteReader();
if (theReader.HasRows)
{
while (theReader.Read())
{
string thisRowsDVP = theReader["DVPOutlookName"].ToString();
string thisRowsRVP = theReader["RVPOutlookName"].ToString();
bool found = false;
for (int i = 0, l = dvprvpData.Count; i < l; i++)
{
DVPRVPSummary thisRowData = dvprvpData[i];
if (thisRowsDVP.Equals(thisRowData.dvpName) && thisRowsRVP.Equals(thisRowData.rvpName))
{
found = true;
int unitsBackhauled = (int)theReader["UnitsBackhauled"];
thisRowData.dairyBackhaulUnits = unitsBackhauled;
}
}
if (!found)
{
DVPRVPSummary thisRow = new DVPRVPSummary();
thisRow.dvpName = thisRowsDVP;
thisRow.rvpName = thisRowsRVP;
thisRow.dairyBackhaulUnits = (int)theReader["UnitsBackhauled"];
dvprvpData.Add(thisRow);
found = false;
}
}
}
theReader.Close();
SqlCommand cmdDVPRVPUnitsBackhauledCost = new SqlCommand("ReportDVPRVPUnitsBackhauledCostWithInterval", theConnection);
cmdDVPRVPUnitsBackhauledCost.Parameters.AddWithValue("@dateStarted", startDate);
cmdDVPRVPUnitsBackhauledCost.Parameters.AddWithValue("@dateEnded", endDate);
cmdDVPRVPUnitsBackhauledCost.CommandType = System.Data.CommandType.StoredProcedure;
cmdDVPRVPUnitsBackhauledCost.CommandTimeout = 1200;
theReader = cmdDVPRVPUnitsBackhauledCost.ExecuteReader();
if (theReader.HasRows)
{
while (theReader.Read())
{
string thisRowsDVP = theReader["DVPOutlookName"].ToString();
string thisRowsRVP = theReader["RVPOutlookName"].ToString();
bool found = false;
for (int i = 0, l = dvprvpData.Count; i < l; i++)
{
DVPRVPSummary thisRowData = dvprvpData[i];
if (thisRowsDVP.Equals(thisRowData.dvpName) && thisRowsRVP.Equals(thisRowData.rvpName))
{
found = true;
if (theReader["BackhaulCost"] != DBNull.Value)
{
double backhaulCost = (double)theReader["BackhaulCost"];
thisRowData.dairyBackhaulCOGS = backhaulCost;
}
}
}
if (!found)
{
DVPRVPSummary thisRow = new DVPRVPSummary();
thisRow.dvpName = thisRowsDVP;
thisRow.rvpName = thisRowsRVP;
thisRow.dairyBackhaulCOGS = (double)theReader["BackhaulCost"];
dvprvpData.Add(thisRow);
found = false;
}
}
}
theReader.Close();
/* RD-DM Data */
SqlCommand cmdRDDMDeliveriesWithoutIssues = new SqlCommand("ReportRDDMNumberOfDeliveriesWithoutIssuesWithInterval", theConnection);
cmdRDDMDeliveriesWithoutIssues.Parameters.AddWithValue("@dateStarted", startDate);
cmdRDDMDeliveriesWithoutIssues.Parameters.AddWithValue("@dateEnded", endDate);
cmdRDDMDeliveriesWithoutIssues.CommandType = System.Data.CommandType.StoredProcedure;
cmdRDDMDeliveriesWithoutIssues.CommandTimeout = 1200;
theReader = cmdRDDMDeliveriesWithoutIssues.ExecuteReader();
List<RDDMSummary> rddmData = new List<RDDMSummary>();
if (theReader.HasRows)
{
while (theReader.Read())
{
RDDMSummary thisRow = new RDDMSummary();
thisRow.rdName = theReader["RDOutlookName"].ToString();
thisRow.dmName = theReader["DMOutlookName"].ToString();
thisRow.deliveries = (int)theReader["NumberOfDeliveriesWithoutIssues"];
rddmData.Add(thisRow);
}
}
theReader.Close();
SqlCommand cmdRDDMDeliveriesWithIssues = new SqlCommand("ReportRDDMNumberOfDeliveriesWithIssuesWithInterval", theConnection);
cmdRDDMDeliveriesWithIssues.Parameters.AddWithValue("@dateStarted", startDate);
cmdRDDMDeliveriesWithIssues.Parameters.AddWithValue("@dateEnded", endDate);
cmdRDDMDeliveriesWithIssues.CommandType = System.Data.CommandType.StoredProcedure;
cmdRDDMDeliveriesWithIssues.CommandTimeout = 1200;
theReader = cmdRDDMDeliveriesWithIssues.ExecuteReader();
if (theReader.HasRows)
{
while (theReader.Read())
{
string thisRowsRD = theReader["RDOutlookName"].ToString();
string thisRowsDM = theReader["DMOutlookName"].ToString();
bool found = false;
for (int i = 0, l = rddmData.Count; i < l; i++)
{
RDDMSummary thisRowData = rddmData[i];
if (thisRowsRD.Equals(thisRowData.rdName) && thisRowsDM.Equals(thisRowData.dmName))
{
found = true;
int deliveriesWithIssues = (int)theReader["NumberOfDeliveriesWithIssues"];
thisRowData.deliveriesWithIssues = deliveriesWithIssues;
thisRowData.deliveries += thisRowData.deliveriesWithIssues;
}
}
if (!found)
{
RDDMSummary thisRow = new RDDMSummary();
thisRow.rdName = theReader["RDOutlookName"].ToString();
thisRow.dmName = theReader["DMOutlookName"].ToString();
thisRow.deliveriesWithIssues = (int)theReader["NumberOfDeliveriesWithIssues"];
thisRow.deliveries += thisRow.deliveriesWithIssues;
rddmData.Add(thisRow);
found = false;
}
}
}
theReader.Close();
SqlCommand cmdRDDMNumberOfIssues = new SqlCommand("ReportRDDMNumberOfIssuesWithInterval", theConnection);
cmdRDDMNumberOfIssues.Parameters.AddWithValue("@dateStarted", startDate);
cmdRDDMNumberOfIssues.Parameters.AddWithValue("@dateEnded", endDate);
cmdRDDMNumberOfIssues.CommandType = System.Data.CommandType.StoredProcedure;
cmdRDDMNumberOfIssues.CommandTimeout = 1200;
theReader = cmdRDDMNumberOfIssues.ExecuteReader();
if (theReader.HasRows)
{
while (theReader.Read())
{
string thisRowsRD = theReader["RDOutlookName"].ToString();
string thisRowsDM = theReader["DMOutlookName"].ToString();
bool found = false;
for (int i = 0, l = rddmData.Count; i < l; i++)
{
RDDMSummary thisRowData = rddmData[i];
if (thisRowsRD.Equals(thisRowData.rdName) && thisRowsDM.Equals(thisRowData.dmName))
{
found = true;
int numberOfIssues = (int)theReader["NumberOfIssues"];
thisRowData.totalReadinessIssues = numberOfIssues;
}
}
if (!found)
{
RDDMSummary thisRow = new RDDMSummary();
thisRow.rdName = theReader["RDOutlookName"].ToString();
thisRow.dmName = theReader["DMOutlookName"].ToString();
thisRow.totalReadinessIssues = (int)theReader["NumberOfIssues"];
rddmData.Add(thisRow);
found = false;
}
}
}
theReader.Close();
SqlCommand cmdRDDMUnitsBackhauled = new SqlCommand("ReportRDDMUnitsBackhauledWithInterval", theConnection);
cmdRDDMUnitsBackhauled.Parameters.AddWithValue("@dateStarted", startDate);
cmdRDDMUnitsBackhauled.Parameters.AddWithValue("@dateEnded", endDate);
cmdRDDMUnitsBackhauled.CommandType = System.Data.CommandType.StoredProcedure;
cmdRDDMUnitsBackhauled.CommandTimeout = 1200;
theReader = cmdRDDMUnitsBackhauled.ExecuteReader();
if (theReader.HasRows)
{
while (theReader.Read())
{
string thisRowsRD = theReader["RDOutlookName"].ToString();
string thisRowsDM = theReader["DMOutlookName"].ToString();
bool found = false;
for (int i = 0, l = rddmData.Count; i < l; i++)
{
RDDMSummary thisRowData = rddmData[i];
if (thisRowsRD.Equals(thisRowData.rdName) && thisRowsDM.Equals(thisRowData.dmName))
{
found = true;
int unitsBackhauled = (int)theReader["UnitsBackhauled"];
thisRowData.dairyBackhaulUnits = unitsBackhauled;
}
}
if (!found)
{
RDDMSummary thisRow = new RDDMSummary();
thisRow.rdName = theReader["RDOutlookName"].ToString();
thisRow.dmName = theReader["DMOutlookName"].ToString();
thisRow.dairyBackhaulUnits = (int)theReader["UnitsBackhauled"];
rddmData.Add(thisRow);
found = false;
}
}
}
theReader.Close();
SqlCommand cmdRDDMUnitsBackhauledCost = new SqlCommand("ReportRDDMUnitsBackhauledCostWithInterval", theConnection);
cmdRDDMUnitsBackhauledCost.Parameters.AddWithValue("@dateStarted", startDate);
cmdRDDMUnitsBackhauledCost.Parameters.AddWithValue("@dateEnded", endDate);
cmdRDDMUnitsBackhauledCost.CommandType = System.Data.CommandType.StoredProcedure;
cmdRDDMUnitsBackhauledCost.CommandTimeout = 1200;
theReader = cmdRDDMUnitsBackhauledCost.ExecuteReader();
if (theReader.HasRows)
{
while (theReader.Read())
{
string thisRowsRD = theReader["RDOutlookName"].ToString();
string thisRowsDM = theReader["DMOutlookName"].ToString();
bool found = false;
for (int i = 0, l = rddmData.Count; i < l; i++)
{
RDDMSummary thisRowData = rddmData[i];
if (thisRowsRD.Equals(thisRowData.rdName) && thisRowsDM.Equals(thisRowData.dmName))
{
found = true;
if (theReader["BackhaulCost"] != DBNull.Value)
{
double backhaulCost = (double)theReader["BackhaulCost"];
thisRowData.dairyBackhaulCOGS = backhaulCost;
}
}
}
if (!found)
{
RDDMSummary thisRow = new RDDMSummary();
thisRow.rdName = theReader["RDOutlookName"].ToString();
thisRow.dmName = theReader["DMOutlookName"].ToString();
thisRow.dairyBackhaulCOGS = (double)theReader["BackhaulCost"];
rddmData.Add(thisRow);
found = false;
}
}
}
theReader.Close();
SqlCommand cmdRDDMNumberOfIssuesOne = new SqlCommand("ReportRDDMGroupOneIssuesWithInterval", theConnection);
cmdRDDMNumberOfIssuesOne.Parameters.AddWithValue("@dateStarted", startDate);
cmdRDDMNumberOfIssuesOne.Parameters.AddWithValue("@dateEnded", endDate);
cmdRDDMNumberOfIssuesOne.CommandType = System.Data.CommandType.StoredProcedure;
cmdRDDMNumberOfIssuesOne.CommandTimeout = 1200;
theReader = cmdRDDMNumberOfIssues.ExecuteReader();
if (theReader.HasRows)
{
while (theReader.Read())
{
string thisRowsRD = theReader["RDOutlookName"].ToString();
string thisRowsDM = theReader["DMOutlookName"].ToString();
bool found = false;
for (int i = 0, l = rddmData.Count; i < l; i++)
{
RDDMSummary thisRowData = rddmData[i];
if (thisRowsRD.Equals(thisRowData.rdName) && thisRowsDM.Equals(thisRowData.dmName))
{
found = true;
int numberOfIssues = (int)theReader["NumberOfIssues"];
thisRowData.totalSecurityFacilityIssues = numberOfIssues;
}
}
if (!found)
{
RDDMSummary thisRow = new RDDMSummary();
thisRow.rdName = theReader["RDOutlookName"].ToString();
thisRow.dmName = theReader["DMOutlookName"].ToString();
thisRow.totalSecurityFacilityIssues = (int)theReader["NumberOfIssues"];
rddmData.Add(thisRow);
found = false;
}
}
}
theReader.Close();
SqlCommand cmdRDDMNumberOfIssuesTwo = new SqlCommand("ReportRDDMGroupTwoIssuesWithInterval", theConnection);
cmdRDDMNumberOfIssuesTwo.Parameters.AddWithValue("@dateStarted", startDate);
cmdRDDMNumberOfIssuesTwo.Parameters.AddWithValue("@dateEnded", endDate);
cmdRDDMNumberOfIssuesTwo.CommandType = System.Data.CommandType.StoredProcedure;
cmdRDDMNumberOfIssuesTwo.CommandTimeout = 1200;
theReader = cmdRDDMNumberOfIssuesTwo.ExecuteReader();
if (theReader.HasRows)
{
while (theReader.Read())
{
string thisRowsRD = theReader["RDOutlookName"].ToString();
string thisRowsDM = theReader["DMOutlookName"].ToString();
bool found = false;
for (int i = 0, l = rddmData.Count; i < l; i++)
{
RDDMSummary thisRowData = rddmData[i];
if (thisRowsRD.Equals(thisRowData.rdName) && thisRowsDM.Equals(thisRowData.dmName))
{
found = true;
int numberOfIssues = (int)theReader["NumberOfIssues"];
thisRowData.totalCapacityIssues = numberOfIssues;
}
}
if (!found)
{
RDDMSummary thisRow = new RDDMSummary();
thisRow.rdName = theReader["RDOutlookName"].ToString();
thisRow.dmName = theReader["DMOutlookName"].ToString();
thisRow.totalCapacityIssues = (int)theReader["NumberOfIssues"];
rddmData.Add(thisRow);
found = false;
}
}
}
theReader.Close();
SqlCommand cmdRDDMNumberOfIssuesThree = new SqlCommand("ReportRDDMGroupThreeIssuesWithInterval", theConnection);
cmdRDDMNumberOfIssuesThree.Parameters.AddWithValue("@dateStarted", startDate);
cmdRDDMNumberOfIssuesThree.Parameters.AddWithValue("@dateEnded", endDate);
cmdRDDMNumberOfIssuesThree.CommandType = System.Data.CommandType.StoredProcedure;
cmdRDDMNumberOfIssuesThree.CommandTimeout = 1200;
theReader = cmdRDDMNumberOfIssuesThree.ExecuteReader();
if (theReader.HasRows)
{
while (theReader.Read())
{
string thisRowsRD = theReader["RDOutlookName"].ToString();
string thisRowsDM = theReader["DMOutlookName"].ToString();
bool found = false;
for (int i = 0, l = rddmData.Count; i < l; i++)
{
RDDMSummary thisRowData = rddmData[i];
if (thisRowsRD.Equals(thisRowData.rdName) && thisRowsDM.Equals(thisRowData.dmName))
{
found = true;
int numberOfIssues = (int)theReader["NumberOfIssues"];
thisRowData.totalProductivityIssues = numberOfIssues;
}
}
if (!found)
{
RDDMSummary thisRow = new RDDMSummary();
thisRow.rdName = theReader["RDOutlookName"].ToString();
thisRow.dmName = theReader["DMOutlookName"].ToString();
thisRow.totalProductivityIssues = (int)theReader["NumberOfIssues"];
rddmData.Add(thisRow);
found = false;
}
}
}
theReader.Close();
closeDataConnection();
try
{
string newFilePath = CopyReportTemplate("field");
openDataConnection();
SqlCommand cmdReport = new SqlCommand("ReportStoresNotReadyWithInterval", theConnection);
cmdReport.Parameters.AddWithValue("@dateStarted", startDate);
cmdReport.Parameters.AddWithValue("@dateEnded", endDate);
cmdReport.CommandType = System.Data.CommandType.StoredProcedure;
cmdReport.CommandTimeout = 1200;
theReader = cmdReport.ExecuteReader();
FileStream template = new FileStream(newFilePath, FileMode.Open, FileAccess.Read);
ExcelPackage pck = new OfficeOpenXml.ExcelPackage(template);
ExcelWorkbook workBook = pck.Workbook;
template.Close();
if (theReader.HasRows)
{
int rowIndex = 1;
ExcelWorksheet notReadySheet = workBook.Worksheets["DetailedViewNotReady"];
while (theReader.Read())
{
rowIndex++;
notReadySheet.Cells[rowIndex, 1].Value = theReader[0].ToString();
notReadySheet.Cells[rowIndex, 2].Value = theReader[1].ToString();
notReadySheet.Cells[rowIndex, 3].Value = theReader[2].ToString();
notReadySheet.Cells[rowIndex, 4].Value = theReader[3].ToString();
notReadySheet.Cells[rowIndex, 5].Value = theReader[4].ToString();
notReadySheet.Cells[rowIndex, 6].Value = theReader[5].ToString();
notReadySheet.Cells[rowIndex, 7].Value = theReader[6].ToString();
notReadySheet.Cells[rowIndex, 8].Value = theReader[7].ToString();
notReadySheet.Cells[rowIndex, 9].Value = theReader[8].ToString();
notReadySheet.Cells[rowIndex, 10].Value = theReader[9].ToString();
notReadySheet.Cells[rowIndex, 11].Value = theReader[10].ToString();
notReadySheet.Cells[rowIndex, 12].Value = theReader[11].ToString();
notReadySheet.Cells[rowIndex, 13].Value = Math.Round(Convert.ToDecimal(theReader[14].ToString()), 2).ToString();
notReadySheet.Cells[rowIndex, 14].Value = getCellFriendlyText(theReader[12].ToString());
if (String.IsNullOrEmpty(theReader[13].ToString()))
{
notReadySheet.Cells[rowIndex, 15].Value = "No Photos Available";
}
else
{
string[] photoIds = theReader[13].ToString().Split(',');
for (int j = 0; j < Int32.Parse(theReader[10].ToString()); j++)
notReadySheet.Cells[rowIndex, j + 15].Value = baseWebURL + "/photos/" + photoIds[j] + ".jpg";
}
notReadySheet.Cells.AutoFitColumns();
}
theResponse.statusCode = 0;
theResponse.statusDescription = extractFilename(newFilePath);
}
else
{
theResponse.statusCode = 1;
theResponse.statusDescription = "There is no data logged between the dates that were selected";
}
theReader.Close();
SqlCommand cmdReportNotReady = new SqlCommand("ReportStoresReadyWithInterval", theConnection);
cmdReportNotReady.Parameters.AddWithValue("@dateStarted", startDate);
cmdReportNotReady.Parameters.AddWithValue("@dateEnded", endDate);
cmdReportNotReady.CommandType = System.Data.CommandType.StoredProcedure;
cmdReportNotReady.CommandTimeout = 1200;
theReader = cmdReportNotReady.ExecuteReader();
if (theReader.HasRows)
{
int rowIndex = 1;
ExcelWorksheet readySheet = workBook.Worksheets["DetailedViewReady"];
while (theReader.Read())
{
rowIndex++;
readySheet.Cells[rowIndex, 1].Value = theReader[0].ToString();
readySheet.Cells[rowIndex, 2].Value = theReader[1].ToString();
readySheet.Cells[rowIndex, 3].Value = theReader[2].ToString();
readySheet.Cells[rowIndex, 4].Value = theReader[3].ToString();
readySheet.Cells[rowIndex, 5].Value = theReader[4].ToString();
}
theResponse.statusCode = 0;
theResponse.statusDescription = extractFilename(newFilePath);
}
else
{
theResponse.statusCode = 1;
theResponse.statusDescription = "There is no data logged between the dates that were selected";
}
theReader.Close();
ExcelWorksheet sheetSummary = workBook.Worksheets["DVPRVPSummary"];
sheetSummary.Cells["A2:C2"].Merge = true;
sheetSummary.Cells[2, 1].Value = "All Data";
if (dvprvpData != null && dvprvpData.Count > 0)
{
int currentRowIndex = 3;
for (int i = 0, l = dvprvpData.Count; i < l; i++)
{
DVPRVPSummary thisRowsData = dvprvpData[i];
thisRowsData.percentageStoresReady = Math.Round((100 - ((double)thisRowsData.deliveriesWithIssues / (double)thisRowsData.deliveries) * 100.0), 2);
thisRowsData.leftoutCOGS = Math.Round(thisRowsData.leftoutCOGS, 2);
thisRowsData.dairyBackhaulCOGS = Math.Round(thisRowsData.dairyBackhaulCOGS, 2);
sheetSummary.Cells[currentRowIndex, 1].Value = thisRowsData.dvpName;
sheetSummary.Cells[currentRowIndex, 2].Value = thisRowsData.rvpName;
sheetSummary.Cells[currentRowIndex, 3].Value = thisRowsData.percentageStoresReady.ToString() + " %";
sheetSummary.Cells[currentRowIndex, 4].Value = "$ " + thisRowsData.dairyBackhaulCOGS.ToString();
sheetSummary.Cells[currentRowIndex, 5].Value = thisRowsData.deliveries;
sheetSummary.Cells[currentRowIndex, 6].Value = thisRowsData.deliveriesWithIssues;
sheetSummary.Cells[currentRowIndex, 7].Value = thisRowsData.totalReadinessIssues;
currentRowIndex++;
}
theResponse.statusCode = 0;
theResponse.statusDescription = extractFilename(newFilePath);
}
else
{
theResponse.statusCode = 1;
theResponse.statusDescription = "There is no data logged between the dates that were selected";
}
ExcelWorksheet sheetRDDMSummary = workBook.Worksheets["RDDMSummary"];
sheetRDDMSummary.Cells["A2:C2"].Merge = true;
sheetRDDMSummary.Cells[2, 1].Value = "All Data";
if (rddmData != null && rddmData.Count > 0)
{
int currentRowIndex = 3;
for (int i = 0, l = rddmData.Count; i < l; i++)
{
RDDMSummary thisRowsData = rddmData[i];
thisRowsData.percentageStoresReady = Math.Round((100 - ((double)thisRowsData.deliveriesWithIssues / (double)thisRowsData.deliveries) * 100.0), 2);
thisRowsData.dairyBackhaulCOGS = Math.Round(thisRowsData.dairyBackhaulCOGS, 2);
sheetRDDMSummary.Cells[currentRowIndex, 1].Value = thisRowsData.rdName;
sheetRDDMSummary.Cells[currentRowIndex, 2].Value = thisRowsData.dmName;
sheetRDDMSummary.Cells[currentRowIndex, 3].Value = thisRowsData.percentageStoresReady.ToString() + " %";
sheetRDDMSummary.Cells[currentRowIndex, 4].Value = "$ " + thisRowsData.dairyBackhaulCOGS.ToString();
sheetRDDMSummary.Cells[currentRowIndex, 5].Value = thisRowsData.deliveries;
sheetRDDMSummary.Cells[currentRowIndex, 6].Value = thisRowsData.deliveriesWithIssues;
sheetRDDMSummary.Cells[currentRowIndex, 7].Value = thisRowsData.totalReadinessIssues;
sheetRDDMSummary.Cells[currentRowIndex, 8].Value = thisRowsData.totalSecurityFacilityIssues;
sheetRDDMSummary.Cells[currentRowIndex, 9].Value = thisRowsData.totalCapacityIssues;
sheetRDDMSummary.Cells[currentRowIndex, 10].Value = thisRowsData.totalProductivityIssues;
currentRowIndex++;
}
theResponse.statusCode = 0;
theResponse.statusDescription = extractFilename(newFilePath);
}
else
{
theResponse.statusCode = 1;
theResponse.statusDescription = "There is no data logged between the dates that were selected";
}
FileStream fileSave = new FileStream(newFilePath, FileMode.Create);
pck.SaveAs(fileSave);
fileSave.Close();
closeDataConnection();
}
catch (Exception _exception)
{
theResponse.statusCode = 6;
theResponse.statusDescription = _exception.Message + " " + _exception.StackTrace;
}
return theResponse;
}
else
{
//return ReportFieldReadiness();
return null;
}
}