public Response ActivateUser(string aUsername)
{
Response theResponse = new Response();
if (aUsername.Equals(""))
{
theResponse.statusCode = 1;
theResponse.statusDescription = "No username was provided";
return theResponse;
}
openDataConnection();
SqlCommand cmdActivate = new SqlCommand("ActivateUser", theConnection);
cmdActivate.Parameters.AddWithValue("@username", aUsername.ToString());
cmdActivate.CommandType = System.Data.CommandType.StoredProcedure;
int numRowsAffected = cmdActivate.ExecuteNonQuery();
if (numRowsAffected > 0)
{
theResponse.statusCode = 0;
}
else
{
theResponse.statusCode = 4;
theResponse.statusDescription = "The desired user could not be found";
}
closeDataConnection();
return theResponse;
}
public Response UpdateUser(StarbucksUser aUserModel)
{
Response theResponse = new Response();
if (aUserModel != null)
{
if (aUserModel.username == null)
{
theResponse.statusDescription = "Username was not supplied";
}
if (aUserModel.firstName == null)
{
theResponse.statusDescription = "First Name was not supplied";
}
if (aUserModel.lastName == null)
{
theResponse.statusDescription = "Last Name was not supplied";
}
if (theResponse.statusDescription.Equals(""))
{
openDataConnection();
SqlCommand cmdUpdateUser = new SqlCommand("UpdateUser", theConnection);
cmdUpdateUser.Parameters.AddWithValue("@username", aUserModel.username);
cmdUpdateUser.Parameters.AddWithValue("@firstName", aUserModel.firstName);
cmdUpdateUser.Parameters.AddWithValue("@lastName", aUserModel.lastName);
cmdUpdateUser.Parameters.AddWithValue("@phoneNumber", aUserModel.phoneNumber != null ? aUserModel.phoneNumber.ToString() : (object)DBNull.Value);
cmdUpdateUser.Parameters.AddWithValue("@emailAddress", aUserModel.emailAddress != null ? aUserModel.emailAddress.ToString() : (object)DBNull.Value);
cmdUpdateUser.CommandType = System.Data.CommandType.StoredProcedure;
int numRowsAffected = cmdUpdateUser.ExecuteNonQuery();
if (numRowsAffected > 0)
{
theResponse.statusCode = 0;
theResponse.statusDescription = "";
}
else
{
theResponse.statusCode = 6;
theResponse.statusDescription = "The user " + aUserModel.username + " could not be updated";
}
}
}
else
{
theResponse.statusCode = 6;
theResponse.statusDescription = "Expected User Model not received";
}
closeDataConnection();
return theResponse;
}
public Response AddCommentToStop(Comment aComment)
{
Response theResponse = new Response();
if (aComment != null)
{
if (aComment.comment == null || aComment.comment.Equals(""))
{
theResponse.statusDescription = "Comment is missing from model";
}
if (aComment.stopID == null || aComment.stopID <= 0)
{
theResponse.statusDescription = "StopID is missing from model";
}
if (theResponse.statusDescription.Equals(""))
{
string filteredComment = getFilteredText(aComment.comment);
openDataConnection();
SqlCommand cmdAddComment = new SqlCommand("AddCommentForStop", theConnection);
cmdAddComment.Parameters.AddWithValue("@stopID", aComment.stopID);
cmdAddComment.Parameters.AddWithValue("@comment", filteredComment);
cmdAddComment.CommandType = System.Data.CommandType.StoredProcedure;
int numRowsAffected = cmdAddComment.ExecuteNonQuery();
if (numRowsAffected > 0)
{
theResponse.statusCode = 0;
theResponse.statusDescription = "";
}
else
{
theResponse.statusCode = 6;
theResponse.statusDescription = "Could not add comment";
}
closeDataConnection();
}
else
{
theResponse.statusCode = 6;
}
}
else
{
theResponse.statusCode = 3;
theResponse.statusDescription = "Comment Model missing";
}
return theResponse;
}
public Response CommitCachedDataV5(TripForSync aTripModel)
{
Response theResponse = new Response();
int totalFailuresCommitted = 0;
int totalImagesCommitted = 0;
int totalDeliveryCodesCommitted = 0;
int totalCommentsCommitted = 0;
int totalFailureErrors = 0;
int totalImageErrors = 0;
int totalDeliveryCodeErrors = 0;
int totalCommentErrors = 0;
if (aTripModel != null)
{
if (aTripModel.id > 0)
{
openDataConnection();
SqlCommand cmdCheckTripID = new SqlCommand("SELECT TripID FROM Trip WHERE TripID = " + aTripModel.id, theConnection);
theReader = cmdCheckTripID.ExecuteReader();
if (theReader.HasRows)
{
theReader.Close();
if (aTripModel.stops != null && aTripModel.stops.Count > 0)
{
foreach (StopWithStoreAndFailure aStop in aTripModel.stops)
{
if (aStop.committed)
{
continue;
}
SqlCommand cmdCheckStopID = new SqlCommand("SELECT StopID FROM Stop WHERE StopID = " + aStop.id, theConnection);
openDataConnection();
theReader = cmdCheckStopID.ExecuteReader();
if (theReader.HasRows)
{
theReader.Close();
if (aStop.failure != null && aStop.failure.Count > 0)
{
foreach (Failure aFailure in aStop.failure)
{
if (aFailure.committed)
{
continue;
}
ResponseFailure addFailureResponse = AddFailure(aFailure);
if (addFailureResponse.statusCode == 0)
{
totalFailuresCommitted++;
}
else
{
totalFailureErrors++;
}
if (aFailure.photos != null && aFailure.photos.Count > 0)
{
foreach (Photo aPhoto in aFailure.photos)
{
Photo thisPhoto = new Photo();
thisPhoto.imageData = aPhoto.imageData;
thisPhoto.stopID = aStop.id;
thisPhoto.failureID = addFailureResponse.failure.failureID;
Response addPhotoResponse = AddPhotoToStop(thisPhoto);
if (addPhotoResponse.statusCode == 0)
{
totalImagesCommitted++;
}
else
{
totalImageErrors++;
}
}
}
//if (aFailure.deliveryCodes != null && aFailure.deliveryCodes.Count > 0)
//{
// foreach (Delivery aDelivery in aFailure.deliveryCodes)
// {
// Delivery thisDelivery = new Delivery();
// thisDelivery.deliveryCode = aDelivery.deliveryCode;
// thisDelivery.stopID = aStop.id;
// thisDelivery.failureID = addFailureResponse.failure.failureID;
// Response addDeliveryResponse = AddDelivery(thisDelivery);
// if (addDeliveryResponse.statusCode == 0)
// {
// totalDeliveryCodesCommitted++;
// }
// else
// {
// totalDeliveryCodeErrors++;
// }
// }
//}
}
}
if (aStop.completed)
{
Response addCompletedResponse = CompleteStop(aStop.id.ToString());
}
ConsolidateEmails(aStop.id.ToString());
}
else
{
theReader.Close();
theResponse.statusCode = 6;
theResponse.statusDescription = "The Stop ID " + aStop.id + " does not exist";
}
}
if (totalFailureErrors == 0 && totalImageErrors == 0 && totalDeliveryCodeErrors == 0 && totalCommentErrors == 0)
{
theResponse.statusCode = 0;
theResponse.statusDescription = "All data has been synchronized";
}
else
{
if (totalFailuresCommitted > 0 || totalImagesCommitted > 0 || totalDeliveryCodeErrors > 0 || totalCommentsCommitted > 0)
{
theResponse.statusCode = 6;
theResponse.statusDescription = "Some of the data has been synchronized. " + totalFailureErrors + " Failures, " + totalDeliveryCodeErrors + " DeliveryCodes, " + totalImageErrors + " Images and " + totalCommentErrors + " Comments could not be synchronized";
}
}
}
else
{
theResponse.statusCode = 6;
theResponse.statusDescription = "No stops were supplied with the trip model. No data was synchronized.";
}
}
else
{
theReader.Close();
theResponse.statusCode = 4;
theResponse.statusDescription = "Trip ID was invalid";
}
closeDataConnection();
}
}
else
{
theResponse.statusCode = 6;
theResponse.statusDescription = "Trip Model not supplied";
}
return theResponse;
}
public Response AddStoreToRoute(string routeName, string storeID)
{
Response theResponse = new Response();
if (routeName.Equals("") || storeID.Equals(""))
{
theResponse.statusCode = 1;
theResponse.statusDescription = "Missing required parameters";
return theResponse;
}
openDataConnection();
SqlCommand cmdCheckMap = new SqlCommand("CheckIfRouteMapExists", theConnection);
cmdCheckMap.Parameters.AddWithValue("@routeName", routeName);
cmdCheckMap.Parameters.AddWithValue("@storeID", storeID);
cmdCheckMap.CommandType = System.Data.CommandType.StoredProcedure;
theReader = cmdCheckMap.ExecuteReader();
if (theReader.HasRows)
{
theReader.Close();
theResponse.statusCode = 3;
theResponse.statusDescription = "The Mapping [Route " + routeName + " -> " + storeID + "] already exists";
}
else
{
theReader.Close();
SqlCommand cmdAddMap = new SqlCommand("AddStoreToRoute", theConnection);
cmdAddMap.Parameters.AddWithValue("@routeName", routeName);
cmdAddMap.Parameters.AddWithValue("@storeID", storeID);
cmdAddMap.CommandType = System.Data.CommandType.StoredProcedure;
try
{
int numRowsAffected = cmdAddMap.ExecuteNonQuery();
if (numRowsAffected > 0)
{
theResponse.statusCode = 0;
theResponse.statusDescription = "";
}
else
{
theResponse.statusCode = 6;
theResponse.statusDescription = "The Mapping [Route " + routeName + " -> " + storeID + "] could not be added";
}
}
catch (Exception theException)
{
theResponse.statusCode = 6;
theResponse.statusDescription = theException.Message;
}
}
closeDataConnection();
return theResponse;
}
public Response AddStopCompletedDate(string aStopID, string aStopCompletedDate)
{
Response theResponse = new Response();
if (aStopID != null && !aStopID.Equals(""))
{
SqlCommand cmdComplete = new SqlCommand("AddStopCompletedDate", theConnection);
cmdComplete.Transaction = theTrans;
cmdComplete.Parameters.AddWithValue("@stopID", aStopID);
cmdComplete.Parameters.AddWithValue("@stopCompletedDate", aStopCompletedDate);
cmdComplete.CommandType = System.Data.CommandType.StoredProcedure;
int numRowsAffected = 0;
numRowsAffected = cmdComplete.ExecuteNonQuery();
if (numRowsAffected > 0)
{
theResponse.statusCode = 0;
theResponse.statusDescription = "";
}
}
else
{
theResponse.statusCode = 2;
theResponse.statusDescription = "Stop ID is missing";
}
return theResponse;
}
public Response DotNetReportStoreReadinessForSSC(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();
/* Summary Data */
SqlCommand cmdDVPRVPDeliveriesWithoutIssues = new SqlCommand("ReportNumberOfDeliveriesWithoutIssuesWithIntervalForSSC", 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["ProviderName"].ToString();
thisRow.rvpName = theReader["CDCName"].ToString();
thisRow.deliveries = (int)theReader["NumberOfDeliveriesWithoutIssues"];
dvprvpData.Add(thisRow);
}
}
theReader.Close();
SqlCommand cmdDVPRVPDeliveriesWithIssues = new SqlCommand("ReportNumberOfDeliveriesWithIssuesWithIntervalForSSC", 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["ProviderName"].ToString();
string thisRowsRVP = theReader["CDCName"].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("ReportNumberOfIssuesWithIntervalForSSC", 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["ProviderName"].ToString();
string thisRowsRVP = theReader["CDCName"].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 cmdUnitsLeftout = new SqlCommand("ReportUnitsLeftoutWithIntervalForSSC", theConnection);
cmdUnitsLeftout.Parameters.AddWithValue("@dateStarted", startDate);
cmdUnitsLeftout.Parameters.AddWithValue("@dateEnded", endDate);
cmdUnitsLeftout.CommandType = System.Data.CommandType.StoredProcedure;
cmdUnitsLeftout.CommandTimeout = 1200;
theReader = cmdUnitsLeftout.ExecuteReader();
if (theReader.HasRows)
{
while (theReader.Read())
{
string thisRowsDVP = theReader["ProviderName"].ToString();
string thisRowsRVP = theReader["CDCName"].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 cmdDVPRVPUnitsBackhauled = new SqlCommand("ReportUnitsBackhauledWithIntervalForSSC", 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["ProviderName"].ToString();
string thisRowsRVP = theReader["CDCName"].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("ReportUnitsBackhauledCostWithIntervalForSSC", 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["ProviderName"].ToString();
string thisRowsRVP = theReader["CDCName"].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();
closeDataConnection();
try
{
string newFilePath = CopyReportTemplate("ssc");
string currentPath = HttpContext.Current.Server.MapPath(".");
long currentTime = DateTime.Now.ToFileTimeUtc();
string sourceFilename = "ssc.xlsx";
string targetFilename;
string targetPath;
string sourcePath = currentPath + "\\templates\\";
targetFilename = "report_ssc_" + currentTime + ".xlsx";
targetPath = currentPath + "\\downloads\\";
string sourceFile = System.IO.Path.Combine(sourcePath, sourceFilename);
string destFile = System.IO.Path.Combine(targetPath, targetFilename);
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;
string[] photoIds;
int colIndex;
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[9].ToString();
notReadySheet.Cells[rowIndex, 7].Value = theReader[10].ToString();
notReadySheet.Cells[rowIndex, 8].Value = theReader[11].ToString();
notReadySheet.Cells[rowIndex, 9].Value = Math.Round(Convert.ToDecimal(theReader[14].ToString()), 2).ToString();
notReadySheet.Cells[rowIndex, 10].Value = getCellFriendlyText(theReader[12].ToString());
if (String.IsNullOrEmpty(theReader[13].ToString()))
{
notReadySheet.Cells[rowIndex, 11].Value = "No Photos Available";
}
else
{
photoIds = theReader[13].ToString().Split(',');
for (colIndex = 0; colIndex < Int32.Parse(theReader[10].ToString()); colIndex++)
notReadySheet.Cells[rowIndex, colIndex + 11].Value = baseWebURL + "/photos/" + photoIds[colIndex] + ".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["Summary"];
sheetSummary.Cells["A2:C2"].Merge = true;
sheetSummary.Cells[2, 1].Value = startDate + " to " + endDate;
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);
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.deliveries;
sheetSummary.Cells[currentRowIndex, 5].Value = thisRowsData.deliveriesWithIssues;
sheetSummary.Cells[currentRowIndex, 6].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";
}
FileStream fileSave = new FileStream(newFilePath, FileMode.Create);
pck.SaveAs(fileSave);
fileSave.Close();
closeDataConnection();
}
catch (Exception _exception)
{
theResponse.statusCode = 6;
theResponse.statusDescription = _exception.Message;
}
return theResponse;
}
else
{
//return ReportStoreReadinessForSSC();
return null;
}
}
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;
}
}
public Response CompleteStopV7(string aStopID, string aStopCompletedDate)
{
Response theResponse = new Response();
aStopCompletedDate = aStopCompletedDate.Replace('S', ' ').Replace('C', ':').Replace('D', '.');
if (aStopID != null && !aStopID.Equals(""))
{
if (aStopCompletedDate != null && !aStopCompletedDate.Equals(""))
{
openDataConnection();
theTrans = theConnection.BeginTransaction();
Response addStopCompletedDateResponse = AddStopCompletedDate(aStopID, aStopCompletedDate);
Response addCompleteStopTransactionResponse = CompleteStopTransaction(aStopID);
if (addStopCompletedDateResponse.statusCode == 0 || addCompleteStopTransactionResponse.statusCode == 0)
{
theTrans.Commit();
theResponse.statusCode = 0;
theResponse.statusDescription = "Stop Completed";
}
else
{
theTrans.Rollback();
theResponse.statusCode = 6;
theResponse.statusDescription = "Unable to update data";
}
closeDataConnection();
}
else
{
theResponse.statusCode = 2;
theResponse.statusDescription = "Stop ID is missing";
}
}
else
{
theResponse.statusCode = 2;
theResponse.statusDescription = "Stop Completed Date is missing";
}
return theResponse;
}
public Response CompleteStopTransaction(string aStopID)
{
Response theResponse = new Response();
if (aStopID != null && !aStopID.Equals(""))
{
//openDataConnection();
SqlCommand cmdComplete = new SqlCommand("CompleteStop", theConnection);
cmdComplete.Transaction = theTrans;
cmdComplete.Parameters.AddWithValue("@stopID", aStopID);
cmdComplete.CommandType = System.Data.CommandType.StoredProcedure;
int numRowsAffected = 0;
// try
// {
numRowsAffected = cmdComplete.ExecuteNonQuery();
// }
// catch (Exception _exception)
// {
// theResponse.statusCode = 6;
// theResponse.statusDescription = _exception.Message;
// }
// string text = "";
// text += " No of Rows Affected(Transaction): " + numRowsAffected + "\n";
// text += " StatusCode: " + theResponse.statusCode + "\n";
// WriteToFile(text);
if (numRowsAffected > 0)
{
theResponse.statusCode = 0;
theResponse.statusDescription = "";
}
//closeDataConnection();
}
else
{
theResponse.statusCode = 2;
theResponse.statusDescription = "Stop ID is missing";
}
// WriteToFile(aStopID + "\n");
// WriteToFile(theResponse.statusCode.ToString()+ "\n");
return theResponse;
}
public Response UploadStoresDotNet(string fileName, string username)
{
int currentRowPointer = 0;
Response theResponse = new Response();
string currentPath = HttpContext.Current.Server.MapPath("~");
long currentTime = DateTime.Now.ToFileTimeUtc();
//string fileName = "stores_" + currentTime;
string finalPath = currentPath + "\\uploads\\" + fileName;
//FileStream fileToUpload = new FileStream(finalPath, FileMode.Create);
//MultipartParser parser = new MultipartParser(fileStream);
//if (parser.Success)
//{
// fileToUpload.Write(parser.FileContents, 0, parser.FileContents.Length);
// fileToUpload.Close();
// fileToUpload.Dispose();
//}
//else
//{
// theResponse.statusCode = 6;
// theResponse.statusDescription = "Unable to parse input data";
// return theResponse;
//}
int recordsFound = 0;
int recordsAdded = 0;
int recordsUpdated = 0;
List<string> feedback = new List<string>();
string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + finalPath + ";Extended Properties=\"Excel 8.0;HDR=Yes;ReadOnly=False\"";
//string connectionString = connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + finalPath + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
List<Op> opsToBeUpdated = new List<Op>();
try
{
OleDbConnection con = new OleDbConnection(connectionString);
OleDbCommand cmd = new OleDbCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.Connection = con;
OleDbDataAdapter dAdapter = new OleDbDataAdapter(cmd);
DataTable dtExcelRecords = new DataTable();
con.Open();
DataTable dtExcelSheetName = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string getExcelSheetName = dtExcelSheetName.Rows[0]["Table_Name"].ToString();
cmd.CommandText = "SELECT * FROM [" + getExcelSheetName + "]";
cmd.CommandTimeout = 10000;
OleDbDataReader oleReader;
oleReader = cmd.ExecuteReader();
if (oleReader.HasRows)
{
List<Store> stores = new List<Store>();
while (oleReader.Read())
{
currentRowPointer++;
if (oleReader[0].ToString().Equals("Store #"))
{
continue;
}
if (oleReader[0].ToString().Equals(""))
{
break;
}
string thisStoreNumber = oleReader[0].ToString();
if (doesStoreExist(thisStoreNumber))
{
Store thisStoreForUpdate = new Store();
thisStoreForUpdate.storeID = getStoreIDForStoreNumber(thisStoreNumber);
thisStoreForUpdate.storeNumber = oleReader[0].ToString();
thisStoreForUpdate.storeName = oleReader[1].ToString();
thisStoreForUpdate.storeAddress = oleReader[2].ToString();
thisStoreForUpdate.storeCity = oleReader[3].ToString();
thisStoreForUpdate.storeZip = oleReader[4].ToString();
thisStoreForUpdate.storeState = oleReader[5].ToString();
thisStoreForUpdate.storePhone = oleReader[6].ToString();
thisStoreForUpdate.storeManagerName = oleReader[7].ToString();
thisStoreForUpdate.storeEmailAddress = oleReader[8].ToString();
if (oleReader.FieldCount > 9)
{
thisStoreForUpdate.storeOwnershipType = oleReader[9].ToString();
}
if (!String.IsNullOrEmpty(oleReader[10].ToString()))
{
thisStoreForUpdate.PODRequired = Convert.ToBoolean(oleReader[10].ToString());
}
Response updateStoreResponse = UpdateStore(thisStoreForUpdate);
if (updateStoreResponse.statusCode == 0)
{
feedback.Add("The Store Number " + thisStoreNumber + " already exists in the database. The record was updated.");
recordsUpdated++;
}
else
{
feedback.Add("The Store Number " + thisStoreNumber + " already exists in the database. The record could not be updated.");
}
recordsFound++;
continue;
}
Store thisStore = new Store();
thisStore.storeNumber = oleReader[0].ToString();
thisStore.storeName = oleReader[1].ToString();
thisStore.storeAddress = oleReader[2].ToString();
thisStore.storeCity = oleReader[3].ToString();
thisStore.storeZip = oleReader[4].ToString();
thisStore.storeState = oleReader[5].ToString();
thisStore.storePhone = oleReader[6].ToString();
thisStore.storeManagerName = oleReader[7].ToString();
thisStore.storeEmailAddress = oleReader[8].ToString();
if (oleReader.FieldCount > 9)
{
thisStore.storeOwnershipType = oleReader[9].ToString();
}
if (!String.IsNullOrEmpty(oleReader[10].ToString()))
{
thisStore.PODRequired = Convert.ToBoolean(oleReader[10].ToString());
}
stores.Add(thisStore);
recordsFound++;
}
for (int i = 0, l = stores.Count; i < l; i++)
{
Response createResponse = CreateStore(stores[i]);
if (createResponse.statusCode == 0)
{
recordsAdded++;
}
}
}
oleReader.Close();
}
catch (Exception _exception)
{
theResponse.statusCode = 6;
theResponse.statusDescription = _exception.Message + " - " + _exception.StackTrace + " - Last Row Pointer was at " + currentRowPointer;
return theResponse;
}
if (recordsFound > 0)
{
theResponse.statusCode = 0;
theResponse.statusDescription = "Found " + recordsFound + " store records in the file. Added " + recordsAdded + " records to the database.Updated " + recordsUpdated + " records in the database.";
}
else
{
theResponse.statusCode = 2;
theResponse.statusDescription = "No records found in the excel file";
}
if (feedback.Count > 0)
{
/* string emailString = "";
theResponse.statusDescription += "Feedback:";
for (int i = 0, l = feedback.Count; i < l; i++)
{
theResponse.statusDescription += feedback[i];
emailString += feedback[i];
}
*/
string emailString = "<ul>";
theResponse.statusDescription += "<br /><br /><p>Feedback:</p><ul>";
for (int i = 0, l = feedback.Count; i < l; i++)
{
theResponse.statusDescription += "<li>" + feedback[i] + "</li>";
emailString += "<li>" + feedback[i] + "</li>";
}
theResponse.statusDescription += "</ul>";
emailString += "</ul>";
//theResponse.statusDescription;
//emailString ;
SendEmailForUploadErrors("Stores", username, emailString, "");
}
// theResponse = new Response();
// theResponse.statusCode = 0;
// theResponse.statusDescription = "Response added successfully";
theResponse.statusCode = 0;
//WebOperationContext.Current.OutgoingResponse.ContentType = "text/html";
return theResponse;
}
public Response UploadRoutesDotNet(string fileName, string username)
{
Response theResponse = new Response();
string cdcName = "";
string currentPath = HttpContext.Current.Server.MapPath("~");
long currentTime = DateTime.Now.ToFileTimeUtc();
//string fileName = "routes_" + currentTime;
string finalPath = currentPath + "\\uploads\\" + fileName;
//FileStream fileToUpload = new FileStream(finalPath, FileMode.Create);
//MultipartParser parser = new MultipartParser(fileStream);
//if (parser.Success)
//{
// fileToUpload.Write(parser.FileContents, 0, parser.FileContents.Length);
// fileToUpload.Close();
// fileToUpload.Dispose();
//}
//else
//{
// theResponse.statusCode = 6;
// theResponse.statusDescription = "Unable to parse input data";
// return theResponse;
//}
int recordsFound = 0;
int recordsAdded = 0;
int recordsUpdated = 0;
List<string> feedback = new List<string>();
//string connectionString = connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + finalPath + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + finalPath + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1;ReadOnly=False\"";
try
{
OleDbConnection con = new OleDbConnection(connectionString);
OleDbCommand cmd = new OleDbCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.Connection = con;
OleDbDataAdapter dAdapter = new OleDbDataAdapter(cmd);
DataTable dtExcelRecords = new DataTable();
con.Open();
DataTable dtExcelSheetName = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string getExcelSheetName = dtExcelSheetName.Rows[0]["Table_Name"].ToString();
cmd.CommandText = "SELECT * FROM [" + getExcelSheetName + "]";
OleDbDataReader oleReader;
oleReader = cmd.ExecuteReader();
if (oleReader.HasRows)
{
List<Route> routes = new List<Route>();
int rowCounter = 0;
while (oleReader.Read())
{
rowCounter += 1;
if (oleReader[0].ToString().Equals("Transaction Type") || oleReader[1].ToString().Equals("CDC"))
{
continue;
}
if (oleReader[0].ToString().Equals(""))
{
break;
}
if (cdcName == "")
cdcName = oleReader[1].ToString();
if (oleReader[0].ToString().ToUpper().Trim().Equals("ADD"))
{
recordsFound++;
if (!doesCDCExist(oleReader[1].ToString()))
{
feedback.Add("CDC " + oleReader[1].ToString() + " does not exist");
continue;
}
if (doesRouteExist(oleReader[2].ToString()))
{
feedback.Add("The route " + oleReader[2].ToString() + " already exists");
continue;
}
Route thisRoute = new Route();
thisRoute.cdc = new CDC();
thisRoute.cdc.name = oleReader[1].ToString();
thisRoute.cdc.id = getCDCIDForCDCName(oleReader[1].ToString());
thisRoute.routeName = oleReader[2].ToString();
int numberOfStopsForThisRoute = oleReader.FieldCount;
List<Store> stops = new List<Store>();
for (int i = 3; i < numberOfStopsForThisRoute; i++)
{
Store thisStore = new Store();
string thisStoreNumber = oleReader[i].ToString();
thisStore.storeID = getStoreIDForStoreNumber(thisStoreNumber);
if (thisStore.storeID > 0)
{
stops.Add(thisStore);
}
else
{
if (!thisStoreNumber.Equals("0") && !thisStoreNumber.Equals(""))
{
feedback.Add("The Store Number " + thisStoreNumber + " was not found in the database");
}
}
}
if (stops.Count > 0)
{
thisRoute.stores = stops;
feedback.Add("Route " + thisRoute.routeName + " has " + thisRoute.stores.Count + " stops.");
routes.Add(thisRoute);
}
else
{
feedback.Add("The Route " + thisRoute.routeName + " could not be added as none of the stores listed against this route are present in the database");
}
}
else if (oleReader[0].ToString().ToUpper().Trim().Equals("UPDATE"))
{
recordsFound++;
if (!doesRouteExist(oleReader[2].ToString()))
{
feedback.Add("The route " + oleReader[2].ToString() + " does not exist thus cannot be updated");
continue;
}
else
{
string thisRouteName = oleReader[2].ToString();
ResponseRouteList thisRouteDetail = GetRouteDetail(thisRouteName);
bool validRoute = false;
int numberOfStops = 0;
if (thisRouteDetail != null)
{
validRoute = true;
if (thisRouteDetail.routes != null)
{
numberOfStops = thisRouteDetail.routes[0].stores.Count;
}
}
if (validRoute)
{
openDataConnection();
SqlCommand cmdDisableMappingsForRoute = new SqlCommand("DisableCurrentMappingsForRouteName", theConnection);
cmdDisableMappingsForRoute.Parameters.AddWithValue("@routeName", thisRouteName);
cmdDisableMappingsForRoute.CommandType = System.Data.CommandType.StoredProcedure;
int numMappingsDisabled = cmdDisableMappingsForRoute.ExecuteNonQuery();
closeDataConnection();
if (numMappingsDisabled >= numberOfStops)
{
List<Store> newStops = new List<Store>();
int numberOfStopsForThisRoute = oleReader.FieldCount;
for (int i = 3; i < numberOfStopsForThisRoute; i++)
{
Store thisStore = new Store();
string thisStoreNumber = oleReader[i].ToString();
if (thisStoreNumber != null && !thisStoreNumber.Equals(""))
{
thisStore.storeNumber = thisStoreNumber;
thisStore.storeID = getStoreIDForStoreNumber(thisStoreNumber);
if (thisStore.storeID > 0)
{
newStops.Add(thisStore);
}
else
{
feedback.Add("The Store Number " + thisStoreNumber + " was not found in the database");
}
}
}
openDataConnection();
foreach (Store aStore in newStops)
{
SqlCommand cmdAddStoreToRoute = new SqlCommand("AddStoreToRoute", theConnection);
cmdAddStoreToRoute.Parameters.AddWithValue("@routeName", thisRouteName);
cmdAddStoreToRoute.Parameters.AddWithValue("@storeID", aStore.storeID);
cmdAddStoreToRoute.CommandType = System.Data.CommandType.StoredProcedure;
int numRowsAffectedForAddStoreToRoute = cmdAddStoreToRoute.ExecuteNonQuery();
feedback.Add("Added Store " + aStore.storeNumber + " to Route " + thisRouteName);
}
feedback.Add("Updated Route " + thisRouteName);
closeDataConnection();
recordsUpdated++;
}
else
{
feedback.Add("The route " + oleReader[2].ToString() + " had " + numberOfStops + " mappings but only " + numMappingsDisabled + " were disabled and this route cannot be updated");
continue;
}
}
else
{
feedback.Add("The route " + oleReader[2].ToString() + " does not seem to be valid and thus cannot be updated");
continue;
}
}
}
}
for (int i = 0, l = routes.Count; i < l; i++)
{
Response createResponse = CreateRoute(routes[i]);
if (createResponse.statusCode == 0)
{
recordsAdded++;
}
}
}
oleReader.Close();
}
catch (Exception _exception)
{
theResponse.statusCode = 6;
theResponse.statusDescription = _exception.Message + " Line Number: " + _exception.StackTrace;
return theResponse;
}
if (recordsFound > 0)
{
theResponse.statusCode = 0;
theResponse.statusDescription = "Found " + recordsFound + " route records in the file.<br />Added " + recordsAdded + " records to the database.<br />Updated " + recordsUpdated + " records in the database.";
}
else
{
theResponse.statusCode = 2;
theResponse.statusDescription = "No records found in the excel file";
}
if (feedback.Count > 0)
{
string emailString = "<ul>";
theResponse.statusDescription += "<br /><br /><p>Feedback:</p><ul>";
for (int i = 0, l = feedback.Count; i < l; i++)
{
theResponse.statusDescription += "<li>" + feedback[i] + "</li>";
emailString += "<li>" + feedback[i] + "</li>";
}
theResponse.statusDescription += "</ul>";
emailString += "</ul>";
SendEmailForUploadErrors("Routes", username, emailString, cdcName);
}
return theResponse;
}
public Response UploadOpsDotNet(string fileName, string username)
{
Response theResponse = new Response();
string currentPath = HttpContext.Current.Server.MapPath("~");
long currentTime = DateTime.Now.ToFileTimeUtc();
//string fileName = "ops_" + currentTime;
string finalPath = currentPath + "\\uploads\\" + fileName;
//FileStream fileToUpload = new FileStream(finalPath, FileMode.Create);
//MultipartParser parser = new MultipartParser(fileStream);
//if (parser.Success)
//{
// fileToUpload.Write(parser.FileContents, 0, parser.FileContents.Length);
// fileToUpload.Close();
// fileToUpload.Dispose();
//}
//else
//{
// theResponse.statusCode = 6;
// theResponse.statusDescription = "Unable to parse input data";
// return theResponse;
//}
int recordsFound = 0;
int recordsAdded = 0;
int recordsUpdated = 0;
List<string> feedback = new List<string>();
string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + finalPath + ";Extended Properties=\"Excel 8.0;HDR=Yes;Connect Timeout=12000; ReadOnly=False\"";
//string connectionString = connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + finalPath + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
List<Op> opsToBeUpdated = new List<Op>();
try
{
OleDbConnection con = new OleDbConnection(connectionString);
OleDbCommand cmd = new OleDbCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.Connection = con;
OleDbDataAdapter dAdapter = new OleDbDataAdapter(cmd);
DataTable dtExcelRecords = new DataTable();
con.Open();
DataTable dtExcelSheetName = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string getExcelSheetName = dtExcelSheetName.Rows[0]["Table_Name"].ToString();
cmd.CommandText = "SELECT * FROM [" + getExcelSheetName + "]";
cmd.CommandTimeout = 6000;
OleDbDataReader oleReader;
oleReader = cmd.ExecuteReader();
if (oleReader.HasRows)
{
List<Op> ops = new List<Op>();
while (oleReader.Read())
{
if (oleReader.FieldCount < 18)
{
continue;
}
if (oleReader[0].ToString().Equals("Transaction Type"))
{
continue;
}
if (oleReader[0].ToString().Equals(""))
{
break;
}
if (oleReader[0].ToString().ToUpper().Equals("ADD") || oleReader[0].ToString().ToUpper().Equals("UPDATE"))
{
recordsFound++;
string thisStoreNumber = oleReader[17].ToString();
if (!doesStoreExist(thisStoreNumber))
{
feedback.Add("The Store Number " + thisStoreNumber + " does not exist in the database");
continue;
}
int thisStoreID = getStoreIDForStoreNumber(thisStoreNumber);
Op thisOp = new Op();
thisOp.storeID = thisStoreID;
thisOp.division = oleReader[1].ToString();
thisOp.divisionName = oleReader[2].ToString();
thisOp.dvpOutlookname = oleReader[3].ToString();
thisOp.dvpEmailAddress = oleReader[4].ToString();
thisOp.region = oleReader[5].ToString();
thisOp.regionName = oleReader[6].ToString();
thisOp.rvpOutlookName = oleReader[7].ToString();
thisOp.rvpEmailAddress = oleReader[8].ToString();
thisOp.area = oleReader[9].ToString();
thisOp.areaName = oleReader[10].ToString();
thisOp.rdOutlookName = oleReader[11].ToString();
thisOp.rdEmailAddress = oleReader[12].ToString();
thisOp.district = oleReader[13].ToString();
thisOp.districtName = oleReader[14].ToString();
thisOp.dmOutlookName = oleReader[15].ToString();
thisOp.dmEmailAddress = oleReader[16].ToString();
if (oleReader[0].ToString().ToUpper().Equals("ADD") && !doesStoreExistInOps(thisOp.storeID.ToString()))
{
ops.Add(thisOp);
}
else if (oleReader[0].ToString().Trim().ToUpper().Equals("UPDATE"))
{
opsToBeUpdated.Add(thisOp);
}
}
}
for (int i = 0, l = ops.Count; i < l; i++)
{
Response opCreationResponse = AddOp(ops[i]);
if (opCreationResponse.statusCode == 0)
{
recordsAdded++;
}
}
for (int i = 0; i < opsToBeUpdated.Count; i++)
{
Response opUpdateResponse = UpdateOp(opsToBeUpdated[i]);
if (opUpdateResponse.statusCode == 0)
{
recordsUpdated++;
}
}
}
oleReader.Close();
}
catch (Exception _exception)
{
theResponse.statusCode = 6;
theResponse.statusDescription = _exception.Message;
return theResponse;
}
if (recordsFound > 0)
{
theResponse.statusCode = 0;
theResponse.statusDescription = "Found " + recordsFound + " Op hierarchy records in the file.<br />Added " + recordsAdded + " records to the database.<br />Updated " + recordsUpdated + " records in the database";
}
else
{
theResponse.statusCode = 2;
theResponse.statusDescription = "No records found in the excel file";
}
if (feedback.Count > 0)
{
string emailString = "<ul>";
theResponse.statusDescription += "<br /><br /><p>Feedback:</p><ul>";
for (int i = 0, l = feedback.Count; i < l; i++)
{
theResponse.statusDescription += "<li>" + feedback[i] + "</li>";
emailString += "<li>" + feedback[i] + "</li>";
}
theResponse.statusDescription += "</ul>";
emailString += "</ul>";
SendEmailForUploadErrors("Ops Hierarchy", username, emailString, "");
}
return theResponse;
}
public Response UpdateUserPassword(StarbucksUser aUserModel)
{
Response theResponse = new Response();
if (aUserModel != null)
{
if (aUserModel.username == null)
{
theResponse.statusDescription = "Username was not supplied";
}
if (aUserModel.password == null)
{
theResponse.statusDescription = "Password was not supplied";
}
if (theResponse.statusDescription.Equals(""))
{
openDataConnection();
SqlCommand cmdUpdateUser = new SqlCommand("UpdateUserPassword", theConnection);
cmdUpdateUser.Parameters.AddWithValue("@username", aUserModel.username);
cmdUpdateUser.Parameters.AddWithValue("@password", aUserModel.password);
cmdUpdateUser.CommandType = System.Data.CommandType.StoredProcedure;
int numRowsAffected = cmdUpdateUser.ExecuteNonQuery();
if (numRowsAffected > 0)
{
theResponse.statusCode = 0;
theResponse.statusDescription = "";
}
else
{
theResponse.statusCode = 6;
theResponse.statusDescription = "The password of the user " + aUserModel.username + " could not be updated";
}
}
}
else
{
theResponse.statusCode = 6;
theResponse.statusDescription = "Expected User Model not received";
}
closeDataConnection();
return theResponse;
}
public Response CommitIssuesV7(StopWithStoreAndFailure aStop)
{
//string text = "";
//text = " Online Start time :" + DateTime.Now + "\n";
//WriteToFile(text);
Response theResponse = new Response();
int totalFailureErrors = 0, totalImageErrors = 0, totalDeliveryErrors = 0;
try
{
openDataConnection();
theTrans = theConnection.BeginTransaction();
if (aStop.failure != null && aStop.failure.Count > 0)
{
foreach (Failure aFailure in aStop.failure)
{
SqlCommand cmdCheckUniqueID = new SqlCommand("SELECT UniqueID FROM Failure WHERE UniqueID = '" + aFailure.uniqueID + "'", theConnection);
cmdCheckUniqueID.Transaction = theTrans;
object uniqueId = cmdCheckUniqueID.ExecuteScalar();
if (uniqueId != null && !string.IsNullOrEmpty(uniqueId.ToString()))
{
continue;
}
ResponseFailure addFailureResponse = AddFailureTransaction(aFailure);
if (addFailureResponse.statusCode != 0)
{
totalFailureErrors++;
}
if (aFailure.photos != null && aFailure.photos.Count > 0)
{
foreach (Photo aPhoto in aFailure.photos)
{
Photo thisPhoto = new Photo();
thisPhoto.imageData = aPhoto.imageData;
thisPhoto.stopID = aFailure.stopID;
thisPhoto.failureID = addFailureResponse.failure.failureID;
Response addPhotoResponse = AddPhotoToStopTransaction(thisPhoto);
if (addPhotoResponse.statusCode != 0)
{
totalImageErrors++;
}
}
}
if (aFailure.deliveryCodes != null && aFailure.deliveryCodes.Count > 0)
{
foreach (Delivery aDelivery in aFailure.deliveryCodes)
{
Delivery thisDelivery = new Delivery();
thisDelivery.deliveryCode = aDelivery.deliveryCode;
thisDelivery.stopID = aStop.id;
thisDelivery.failureID = addFailureResponse.failure.failureID;
// thisDelivery.dateAdded = aDelivery.dateAdded;
Response addDeliveryResponse = AddDeliveryTransaction(thisDelivery);
if (addDeliveryResponse.statusCode != 0)
{
totalDeliveryErrors++;
}
}
}
}
}
Response addStopCompletedDateResponse = AddStopCompletedDate(aStop.id.ToString(), aStop.completedDate);
if (totalFailureErrors > 0 || totalImageErrors > 0 || totalDeliveryErrors > 0)
{
theTrans.Rollback();
theResponse.statusCode = 6;
theResponse.statusDescription = "Unable to update data";
}
else
{
theTrans.Commit();
theResponse.statusCode = 0;
theResponse.statusDescription = "All data has been synchronized";
ConsolidateEmails(aStop.id.ToString());
}
}
catch (Exception ex)
{
//text = "Exception :" + ex.Message + "\n";
//WriteToFile(text);
theTrans.Rollback();
theResponse.statusCode = 6;
theResponse.statusDescription = "Unable to update data";
}
finally
{
theTrans = null;
closeDataConnection();
}
//text = " End time :" + DateTime.Now + "\n";
//WriteToFile(text);
return theResponse;
}
public Response CreateUser(StarbucksUser aUserModel)
{
Response theResponse = new Response();
if (aUserModel != null)
{
if (aUserModel.username == null)
{
theResponse.statusDescription = "Username was not supplied";
}
if (aUserModel.password == null)
{
theResponse.statusDescription = "Password was not supplied";
}
if (aUserModel.firstName == null)
{
theResponse.statusDescription = "First Name was not supplied";
}
if (aUserModel.lastName == null)
{
theResponse.statusDescription = "Last Name was not supplied";
}
if (aUserModel.userType == 0)
{
theResponse.statusDescription = "User Type was not supplied";
}
if (theResponse.statusDescription.Equals(""))
{
openDataConnection();
SqlCommand cmdCheckUserExists = new SqlCommand("UserExists", theConnection);
cmdCheckUserExists.Parameters.AddWithValue("@username", aUserModel.username.ToString());
cmdCheckUserExists.CommandType = System.Data.CommandType.StoredProcedure;
theReader = cmdCheckUserExists.ExecuteReader();
if (theReader.HasRows)
{
theReader.Close();
theResponse.statusCode = 3;
theResponse.statusDescription = "Username already exists";
}
else
{
theReader.Close();
SqlCommand cmdCreateUser = new SqlCommand("CreateUser", theConnection);
cmdCreateUser.Parameters.AddWithValue("@username", aUserModel.username.ToString());
cmdCreateUser.Parameters.AddWithValue("@password", aUserModel.password.ToString());
cmdCreateUser.Parameters.AddWithValue("@firstName", aUserModel.firstName.ToString());
cmdCreateUser.Parameters.AddWithValue("@lastName", aUserModel.lastName.ToString());
cmdCreateUser.Parameters.AddWithValue("@userTypeID", aUserModel.userType.ToString());
cmdCreateUser.Parameters.AddWithValue("@phoneNumber", aUserModel.phoneNumber != null ? aUserModel.phoneNumber.ToString() : (object)DBNull.Value);
cmdCreateUser.Parameters.AddWithValue("@emailAddress", aUserModel.emailAddress != null ? aUserModel.emailAddress.ToString() : (object)DBNull.Value);
cmdCreateUser.CommandType = System.Data.CommandType.StoredProcedure;
int numRowsAffected = cmdCreateUser.ExecuteNonQuery();
if (numRowsAffected > 0)
{
if (aUserModel.associatedID != null && !aUserModel.associatedID.Equals(""))
{
SqlCommand cmdAssociate = new SqlCommand("AssociateUserToProvider", theConnection);
cmdAssociate.Parameters.AddWithValue("@username", aUserModel.username);
cmdAssociate.Parameters.AddWithValue("@providerID", Int32.Parse(aUserModel.associatedID));
cmdAssociate.CommandType = System.Data.CommandType.StoredProcedure;
int numRowsAffectedInAssociation = cmdAssociate.ExecuteNonQuery();
if (numRowsAffectedInAssociation > 0)
{
theResponse.statusCode = 0;
theResponse.statusDescription = "";
}
else
{
SqlCommand cmdDeleteUser = new SqlCommand("DeleteUser", theConnection);
cmdDeleteUser.Parameters.AddWithValue("@username", aUserModel.username);
cmdDeleteUser.CommandType = System.Data.CommandType.StoredProcedure;
int numRowsAffectedInDeletion = cmdDeleteUser.ExecuteNonQuery();
theResponse.statusCode = 3;
theResponse.statusDescription = "The user could not be created as it could not be associated to the supplied Provider ID or CDC ID";
}
}
else
{
theResponse.statusCode = 0;
theResponse.statusDescription = "";
}
}
}
}
}
else
{
theResponse.statusCode = 6;
theResponse.statusDescription = "Expected User Model not received";
}
return theResponse;
}
public Response DeleteAllIssues(string aStopID)
{
Response theResponse = new Response();
if (aStopID == null || aStopID.Equals(""))
{
theResponse.statusCode = 2;
theResponse.statusDescription = "Stop ID is missing";
}
else
{
openDataConnection();
SqlCommand cmdDelete = new SqlCommand("DeleteAllIssuesForStop", theConnection);
cmdDelete.Parameters.AddWithValue("@stopID", aStopID);
cmdDelete.CommandType = System.Data.CommandType.StoredProcedure;
int numRowsAffected = 0;
try
{
numRowsAffected = cmdDelete.ExecuteNonQuery();
if (numRowsAffected >= 0)
{
theResponse.statusCode = 0;
theResponse.statusDescription = "";
}
}
catch (Exception _exception)
{
theResponse.statusDescription = _exception.Message;
theResponse.statusCode = 6;
}
closeDataConnection();
}
return theResponse;
}
public Response ConsolidateEmails(string stopID)
{
Response theResponse = new Response();
try
{
ResponsePhoto photosResponse = GetPhotosForStop(stopID);
ResponseFailureList failuresResponse = GetFailuresForStop(Int32.Parse(stopID));
Store thisStore = GetStoreForStop(Int32.Parse(stopID));
CDC thisCDC = GetCDCForStop(Int32.Parse(stopID));
bool escalationRequired = false;
string escalationToName = "";
string escalationToEmail = "";
foreach (FailureWithReason aFailure in failuresResponse.failures)
{
if (aFailure.reason.escalation)
{
escalationRequired = true;
}
}
if (escalationRequired)
{
if (thisStore != null)
{
ResponseOpList opList = GetOpsForStoreID(thisStore.storeID.ToString());
if (opList.statusCode == 0)
{
if (opList.ops != null && opList.ops.Count > 0)
{
escalationToName = opList.ops[0].dmOutlookName;
escalationToEmail = opList.ops[0].dmEmailAddress;
}
}
}
}
List<string> listCC = new List<string>();
string storeEmailAddressTo = thisStore.storeEmailAddress;
storeEmailAddressTo = replaceCommasWithSemiColons(storeEmailAddressTo);
string[] storeEmailAddressesSeparatedBySemiColon = storeEmailAddressTo.Split(';');
if (storeEmailAddressesSeparatedBySemiColon.Count() > 1)
{
storeEmailAddressTo = storeEmailAddressesSeparatedBySemiColon[0];
for (int i = 1, l = storeEmailAddressesSeparatedBySemiColon.Count(); i < l; i++)
{
listCC.Add(storeEmailAddressesSeparatedBySemiColon[i]);
}
}
//listCC.Add("*****@*****.**");
//listCC.Add("*****@*****.**");
// SendEmail(thisStore.storeManagerName + " - Store Number " + thisStore.storeNumber, storeEmailAddressTo, listCC, failuresResponse.failures, photosResponse.photos);
SendEmail(thisStore.storeManagerName + " - Store Number " + thisStore.storeNumber, thisStore.storeNumber, thisCDC.name,
storeEmailAddressTo, listCC, failuresResponse.failures, photosResponse.photos);
if (escalationRequired && !escalationToEmail.Equals("") && !escalationToName.Equals(""))
{
// SendEmail(escalationToName, escalationToEmail, listCC, failuresResponse.failures, photosResponse.photos);
SendEmail(escalationToName, thisStore.storeNumber, thisCDC.name, escalationToEmail, listCC, failuresResponse.failures, photosResponse.photos);
}
theResponse.statusCode = 0;
theResponse.statusDescription = "Email(s) sent to " + thisStore.storeEmailAddress + (escalationRequired ? ". Emails were also sent to " + thisStore.storeEmailAddress + " for escalation." : "");
return theResponse;
}
catch (Exception e)
{
SendErrorEmail(e.Message + " / " + e.StackTrace);
return theResponse;
}
}
public Response AddOp(Op anOp)
{
Response theResponse = new Response();
if (anOp == null)
{
theResponse.statusCode = 2;
theResponse.statusDescription = "Expected Op Model is missing";
}
else
{
if (anOp.storeID == null || anOp.storeID == 0)
{
theResponse.statusDescription = "Store ID not provided";
}
if (anOp.area == null || anOp.area.Equals(""))
{
theResponse.statusDescription = "Area not provided";
}
if (anOp.division == null || anOp.division.Equals(""))
{
theResponse.statusDescription = "Division not provided";
}
if (anOp.region == null || anOp.region.Equals(""))
{
theResponse.statusDescription = "Region not provided";
}
if (anOp.district == null || anOp.district.Equals(""))
{
theResponse.statusDescription = "District not provided";
}
if (theResponse.statusDescription.Equals(""))
{
openDataConnection();
SqlCommand cmdAddOp = new SqlCommand("AddOpToHierarchy", theConnection);
cmdAddOp.Parameters.AddWithValue("@storeID", anOp.storeID.ToString());
cmdAddOp.Parameters.AddWithValue("@division", anOp.division);
cmdAddOp.Parameters.AddWithValue("@region", anOp.region);
cmdAddOp.Parameters.AddWithValue("@area", anOp.area);
cmdAddOp.Parameters.AddWithValue("@district", anOp.district);
cmdAddOp.Parameters.AddWithValue("@divisionName", anOp.divisionName == null ? DBNull.Value.ToString() : anOp.divisionName);
cmdAddOp.Parameters.AddWithValue("@dvpOutlookName", anOp.dvpOutlookname == null ? DBNull.Value.ToString() : anOp.dvpOutlookname);
cmdAddOp.Parameters.AddWithValue("@dvpEmailAddress", anOp.dvpEmailAddress == null ? DBNull.Value.ToString() : anOp.dvpEmailAddress);
cmdAddOp.Parameters.AddWithValue("@regionName", anOp.regionName == null ? DBNull.Value.ToString() : anOp.regionName);
cmdAddOp.Parameters.AddWithValue("@rvpOutlookName", anOp.rvpOutlookName == null ? DBNull.Value.ToString() : anOp.rvpOutlookName);
cmdAddOp.Parameters.AddWithValue("@rvpEmailAddress", anOp.rvpEmailAddress == null ? DBNull.Value.ToString() : anOp.rvpEmailAddress);
cmdAddOp.Parameters.AddWithValue("@areaName", anOp.areaName == null ? DBNull.Value.ToString() : anOp.areaName);
cmdAddOp.Parameters.AddWithValue("@rdOutlookName", anOp.rdOutlookName == null ? DBNull.Value.ToString() : anOp.rdOutlookName);
cmdAddOp.Parameters.AddWithValue("@rdEmailAddress", anOp.rdEmailAddress == null ? DBNull.Value.ToString() : anOp.rdEmailAddress);
cmdAddOp.Parameters.AddWithValue("@districtName", anOp.districtName == null ? DBNull.Value.ToString() : anOp.districtName);
cmdAddOp.Parameters.AddWithValue("@dmOutlookName", anOp.dmOutlookName == null ? DBNull.Value.ToString() : anOp.dmOutlookName);
cmdAddOp.Parameters.AddWithValue("@dmEmailAddress", anOp.dmEmailAddress == null ? DBNull.Value.ToString() : anOp.dmEmailAddress);
cmdAddOp.CommandType = System.Data.CommandType.StoredProcedure;
int numRowsAffected = 0;
try
{
numRowsAffected = cmdAddOp.ExecuteNonQuery();
}
catch (Exception _exception)
{
theResponse.statusCode = 6;
theResponse.statusDescription = _exception.Message;
}
if (numRowsAffected > 0)
{
theResponse.statusCode = 0;
theResponse.statusDescription = "";
}
closeDataConnection();
}
else
{
theResponse.statusCode = 2;
theResponse.statusDescription = "Op model missing";
}
}
return theResponse;
}
public Response CreateCDC(CDC aCDCModel)
{
Response theResponse = new Response();
if (aCDCModel != null)
{
if (aCDCModel.name == null || aCDCModel.name.Equals(""))
{
theResponse.statusDescription = "CDC Name was not provided";
}
if (aCDCModel.providerID == 0)
{
theResponse.statusDescription = "CDC Provider ID was not provided";
}
if (theResponse.statusDescription.Equals(""))
{
if (cdcExists(aCDCModel.name))
{
theResponse.statusCode = 3;
theResponse.statusDescription = "A CDC by the name of " + aCDCModel.name + " already exists";
return theResponse;
}
openDataConnection();
SqlCommand cmdCreate = null;
if (aCDCModel.address == null && aCDCModel.state == null && aCDCModel.zip == null && aCDCModel.phone == null && aCDCModel.email == null)
{
cmdCreate = new SqlCommand("CreateCDC", theConnection);
}
else
{
cmdCreate = new SqlCommand("CreateCDCWithDetail", theConnection);
cmdCreate.Parameters.AddWithValue("@cdcAddress", aCDCModel.address);
cmdCreate.Parameters.AddWithValue("@cdcState", aCDCModel.state);
cmdCreate.Parameters.AddWithValue("@cdcZip", aCDCModel.zip);
cmdCreate.Parameters.AddWithValue("@cdcPhone", aCDCModel.phone);
cmdCreate.Parameters.AddWithValue("@cdcEmailAddress", aCDCModel.email);
}
cmdCreate.Parameters.AddWithValue("@cdcName", aCDCModel.name);
cmdCreate.Parameters.AddWithValue("@providerID", aCDCModel.providerID);
cmdCreate.CommandType = System.Data.CommandType.StoredProcedure;
try
{
int numRowsAffected = cmdCreate.ExecuteNonQuery();
if (numRowsAffected > 0)
{
theResponse.statusCode = 0;
theResponse.statusDescription = "";
}
else
{
theResponse.statusCode = 6;
theResponse.statusDescription = "Specified CDC could not be added";
}
}
catch (Exception _exception)
{
theResponse.statusCode = 6;
theResponse.statusDescription = _exception.Message;
}
closeDataConnection();
}
else
{
theResponse.statusCode = 6;
}
}
else
{
theResponse.statusCode = 6;
theResponse.statusDescription = "Expected CDC Model not recieved";
}
return theResponse;
}
public Response AddPhotoToStopTransaction(Photo aPhoto)
{
Response theResponse = new Response();
if (aPhoto != null)
{
if (aPhoto.stopID <= 0)
{
theResponse.statusDescription = "Stop ID is missing";
}
if (aPhoto.imageData == null || aPhoto.imageData.Equals(""))
{
theResponse.statusDescription = "Image Data is missing";
}
if (theResponse.statusDescription.Equals(""))
{
//openDataConnection();
SqlCommand cmdAddPhoto = new SqlCommand("AddPhotoForStop", theConnection);
cmdAddPhoto.Transaction = theTrans;
cmdAddPhoto.Parameters.AddWithValue("@stopID", aPhoto.stopID);
cmdAddPhoto.Parameters.AddWithValue("@failureID", aPhoto.failureID);
//cmdAddPhoto.Parameters.AddWithValue("@failureID", null);
cmdAddPhoto.Parameters.AddWithValue("@photoData", aPhoto.imageData);
cmdAddPhoto.CommandType = System.Data.CommandType.StoredProcedure;
int numRowsAffected = cmdAddPhoto.ExecuteNonQuery();
if (numRowsAffected > 0)
{
theResponse.statusCode = 0;
theResponse.statusDescription = "";
}
else
{
theResponse.statusDescription = "Could not add photo to StopID " + aPhoto.stopID;
}
//closeDataConnection();
}
else
{
theResponse.statusCode = 6;
}
}
else
{
theResponse.statusCode = 4;
theResponse.statusDescription = "Photo Model missing";
}
return theResponse;
}
public Response CreateProvider(Provider aProviderModel)
{
Response theResponse = new Response();
if (aProviderModel == null)
{
theResponse.statusCode = 2;
theResponse.statusDescription = "Provider Model missing";
}
else
{
if (aProviderModel.providerName == null || aProviderModel.providerName.Equals(""))
{
theResponse.statusDescription = "Provider Name is missing";
}
if (theResponse.statusDescription.Equals(""))
{
bool doesProviderExist = providerExists(aProviderModel.providerName);
if (doesProviderExist)
{
theResponse.statusCode = 3;
theResponse.statusDescription = "The provider " + aProviderModel.providerName + " already exists";
return theResponse;
}
openDataConnection();
SqlCommand addProvider = new SqlCommand("CreateProvider", theConnection);
addProvider.Parameters.AddWithValue("@providerName", aProviderModel.providerName);
addProvider.CommandType = System.Data.CommandType.StoredProcedure;
int numRowsAffected = 0;
try
{
numRowsAffected = addProvider.ExecuteNonQuery();
}
catch (Exception _exception)
{
theResponse.statusCode = 6;
theResponse.statusDescription = _exception.Message;
}
if (numRowsAffected > 0)
{
theResponse.statusCode = 0;
theResponse.statusDescription = "";
}
else
{
theResponse.statusCode = 6;
theResponse.statusDescription = "The provider " + aProviderModel.providerName + " could not be created";
}
closeDataConnection();
}
else
{
theResponse.statusCode = 2;
theResponse.statusDescription = "Provider model missing";
}
}
return theResponse;
}
public Response CreateReason(Reason aReasonModel)
{
Response theResponse = new Response();
if (aReasonModel != null)
{
if (aReasonModel.reasonName == null)
{
theResponse.statusDescription = "Reason Name not supplied";
}
if (parentReasonExists(aReasonModel.reasonName))
{
theResponse.statusCode = 3;
theResponse.statusDescription = "Reason name already exists";
return theResponse;
}
if (theResponse.statusDescription.Equals(""))
{
openDataConnection();
SqlCommand cmdCreateReason = new SqlCommand("CreateParentReason", theConnection);
cmdCreateReason.Parameters.AddWithValue("@reasonName", aReasonModel.reasonName);
cmdCreateReason.CommandType = System.Data.CommandType.StoredProcedure;
try
{
int numRowsAffected = cmdCreateReason.ExecuteNonQuery();
if (numRowsAffected > 0)
{
theResponse.statusCode = 0;
theResponse.statusDescription = "";
}
else
{
theResponse.statusCode = 6;
theResponse.statusDescription = "The specified reason could not be added";
}
}
catch (Exception _exception)
{
theResponse.statusCode = 6;
theResponse.statusDescription = _exception.Message;
}
closeDataConnection();
}
else
{
theResponse.statusCode = 6;
}
}
else
{
theResponse.statusCode = 6;
theResponse.statusDescription = "Expected Reason Model not received";
}
return theResponse;
}
public Response CreateReasonChild(ReasonChildWithParent aChildReasonModel)
{
Response theResponse = new Response();
if (aChildReasonModel != null)
{
if (aChildReasonModel.childReasonName == null)
{
theResponse.statusDescription = "Child Reason Name was not supplied";
}
if (aChildReasonModel.childReasonExplanation == null)
{
theResponse.statusDescription = "Child Reason Explanation was not supplied";
}
if (aChildReasonModel.parentReason == null)
{
theResponse.statusDescription = "Child Reason's Parent Reason was not supplied";
}
if (childReasonExists(aChildReasonModel.childReasonName))
{
theResponse.statusCode = 3;
theResponse.statusDescription = "Child Reason already exists";
return theResponse;
}
if (theResponse.statusDescription.Equals(""))
{
openDataConnection();
SqlCommand cmdCreateChildReason = new SqlCommand("CreateChildReason", theConnection);
cmdCreateChildReason.Parameters.AddWithValue("@parentReasonID", aChildReasonModel.parentReason.reasonCode.ToString());
cmdCreateChildReason.Parameters.AddWithValue("@childReasonName", aChildReasonModel.childReasonName);
cmdCreateChildReason.Parameters.AddWithValue("@childReasonExplanation", aChildReasonModel.childReasonExplanation);
cmdCreateChildReason.Parameters.AddWithValue("@escalation", (object)aChildReasonModel.escalation);
cmdCreateChildReason.Parameters.AddWithValue("@photoRequired", (object)aChildReasonModel.photoRequired);
cmdCreateChildReason.Parameters.AddWithValue("@valueRequired", (object)aChildReasonModel.valueRequired);
cmdCreateChildReason.Parameters.AddWithValue("@PODRequired", (object)aChildReasonModel.PODRequired);
cmdCreateChildReason.Parameters.AddWithValue("@valueUnitPrice", aChildReasonModel.valueUnitPrice);
cmdCreateChildReason.CommandType = System.Data.CommandType.StoredProcedure;
try
{
int numRowsAffected = cmdCreateChildReason.ExecuteNonQuery();
if (numRowsAffected > 0)
{
theResponse.statusCode = 0;
theResponse.statusDescription = "";
}
else
{
theResponse.statusCode = 6;
}
}
catch (Exception _exception)
{
theResponse.statusCode = 6;
theResponse.statusDescription = _exception.Message;
}
closeDataConnection();
}
else
{
theResponse.statusCode = 6;
}
}
else
{
theResponse.statusCode = 6;
theResponse.statusDescription = "Expected Child Reason Model not received";
}
return theResponse;
}
public Response CloseTrip(string aTripID)
{
Response theResponse = new Response();
if (aTripID != null && !aTripID.Equals(""))
{
openDataConnection();
SqlCommand cmdCloseTrip = new SqlCommand("CloseTrip", theConnection);
cmdCloseTrip.Parameters.AddWithValue("@tripID", aTripID);
cmdCloseTrip.CommandType = System.Data.CommandType.StoredProcedure;
int numRowsAffected = 0;
try
{
numRowsAffected = cmdCloseTrip.ExecuteNonQuery();
}
catch (Exception _exception)
{
theResponse.statusCode = 6;
theResponse.statusDescription = _exception.Message;
}
if (numRowsAffected > 0)
{
theResponse.statusCode = 0;
theResponse.statusDescription = "";
try
{
SqlCommand cmdGetDetails = new SqlCommand("GetTripDetails", theConnection);
cmdGetDetails.Parameters.AddWithValue("@tripID", aTripID);
cmdGetDetails.CommandType = System.Data.CommandType.StoredProcedure;
theReader = cmdGetDetails.ExecuteReader();
if (theReader.HasRows)
{
while (theReader.Read())
{
string cdcName = theReader["CDCName"].ToString();
string cdcEmail = theReader["CDCEmailAddress"].ToString();
string routeName = theReader["RouteName"].ToString();
DateTime dateClosed = (DateTime)theReader["DateClosed"];
//SendEmailToCDC(cdcName, "*****@*****.**", routeName, dateClosed);
SendEmailToCDC(cdcName, cdcEmail, routeName, dateClosed);
}
}
theReader.Close();
}
catch (Exception _exceptionMail)
{
theResponse.statusDescription = _exceptionMail.Message + " / " + _exceptionMail.StackTrace;
}
}
closeDataConnection();
}
else
{
theResponse.statusCode = 4;
theResponse.statusDescription = "Missing Trip ID";
}
return theResponse;
}
public Response CreateRoute(Route routeModel)
{
Response theResponse = new Response();
if (routeModel != null)
{
if (routeModel.routeName == null)
{
theResponse.statusDescription = "Route Name not supplied";
}
if (routeModel.cdc == null || routeModel.cdc.id == 0)
{
theResponse.statusDescription = "CDC information not supplied";
}
if (theResponse.statusDescription.Equals(""))
{
openDataConnection();
SqlCommand cmdCheck = new SqlCommand("RouteExists", theConnection);
cmdCheck.Parameters.AddWithValue("@routeName", routeModel.routeName);
cmdCheck.CommandType = System.Data.CommandType.StoredProcedure;
theReader = cmdCheck.ExecuteReader();
if (theReader.HasRows)
{
theReader.Close();
theResponse.statusCode = 3;
theResponse.statusDescription = "The route name " + routeModel.routeName + " already exists";
}
else
{
theReader.Close();
SqlCommand cmdCreate = new SqlCommand("CreateRoute", theConnection);
cmdCreate.Parameters.AddWithValue("@routeName", routeModel.routeName);
cmdCreate.Parameters.AddWithValue("@cdcID", routeModel.cdc.id);
cmdCreate.CommandType = System.Data.CommandType.StoredProcedure;
try
{
int numRowsAffected = cmdCreate.ExecuteNonQuery();
if (numRowsAffected > 0)
{
if (routeModel.stores != null)
{
int totalStoresGiven = routeModel.stores.Count;
int totalStoresAdded = 0;
for (int i = 0; i < totalStoresGiven; i++)
{
int thisStoreID = routeModel.stores[i].storeID;
SqlCommand cmdAddStoreToRoute = new SqlCommand("AddStoreToRoute", theConnection);
cmdAddStoreToRoute.Parameters.AddWithValue("@routeName", routeModel.routeName);
cmdAddStoreToRoute.Parameters.AddWithValue("@storeID", thisStoreID);
cmdAddStoreToRoute.CommandType = System.Data.CommandType.StoredProcedure;
int numRowsAffectedForAddStoreToRoute = cmdAddStoreToRoute.ExecuteNonQuery();
if (numRowsAffectedForAddStoreToRoute > 0)
{
totalStoresAdded++;
}
}
if (totalStoresAdded == totalStoresGiven)
{
theResponse.statusCode = 0;
theResponse.statusDescription = "";
}
else
{
theResponse.statusCode = 0;
theResponse.statusDescription = "Only " + totalStoresAdded + " out of " + totalStoresGiven + " were added to the route " + routeModel.routeName;
}
}
else
{
theResponse.statusCode = 0;
theResponse.statusDescription = "";
}
}
else
{
theResponse.statusCode = 6;
}
}
catch (Exception _exception)
{
theResponse.statusCode = 6;
theResponse.statusDescription = _exception.Message;
}
}
closeDataConnection();
}
else
{
theResponse.statusCode = 6;
}
}
else
{
theResponse.statusCode = 6;
theResponse.statusDescription = "Route Model was expected but not supplied";
}
return theResponse;
}
public Response Logout(string aUsername, string aSessionID)
{
Response theResponse = new Response();
if (aUsername.Equals("") || aSessionID.Equals(""))
{
theResponse.statusCode = 1;
theResponse.statusDescription = "No credentials were provided";
return theResponse;
}
openDataConnection();
SqlCommand cmdCheckIfSessionIsOpen = new SqlCommand("IsSessionOpen", theConnection);
cmdCheckIfSessionIsOpen.Parameters.Add(new SqlParameter("@sessionID", aSessionID));
cmdCheckIfSessionIsOpen.Parameters.Add(new SqlParameter("@username", aUsername));
cmdCheckIfSessionIsOpen.CommandType = System.Data.CommandType.StoredProcedure;
theReader = cmdCheckIfSessionIsOpen.ExecuteReader();
if (!theReader.HasRows)
{
theReader.Close();
theResponse.statusCode = 6;
theResponse.statusDescription = "Session does not exist or is already closed";
}
else
{
theReader.Close();
SqlCommand cmdLogout = new SqlCommand("Logout", theConnection);
cmdLogout.Parameters.Add(new SqlParameter("@username", aUsername));
cmdLogout.Parameters.Add(new SqlParameter("@sessionID", aSessionID));
cmdLogout.CommandType = System.Data.CommandType.StoredProcedure;
int numRowsAffected = cmdLogout.ExecuteNonQuery();
if (numRowsAffected <= 0)
{
theResponse.statusCode = 4;
theResponse.statusDescription = "Session does not exist";
}
else
{
theResponse.statusCode = 0;
}
}
closeDataConnection();
return theResponse;
}
public Response CreateStore(Store aStoreModel)
{
Response theResponse = new Response();
if (aStoreModel != null)
{
if (aStoreModel.storeName == null)
{
theResponse.statusDescription = "Store Name was not supplied";
}
if (aStoreModel.storeAddress == null)
{
theResponse.statusDescription = "Store Address was not supplied";
}
if (aStoreModel.storeCity == null)
{
theResponse.statusDescription = "Store City was not supplied";
}
if (aStoreModel.storeZip == null)
{
theResponse.statusDescription = "Store Zip Code was not supplied";
}
else if (aStoreModel.storeZip.Count() > 10)
{
theResponse.statusDescription = "Store Zip Code is longer than 10 characters";
}
if (aStoreModel.storeState == null)
{
theResponse.statusDescription = "Store State was not supplied";
}
if (aStoreModel.storePhone == null)
{
theResponse.statusDescription = "Store Phone Number was not supplied";
}
if (aStoreModel.storeManagerName == null)
{
theResponse.statusDescription = "Store Manager's name was not supplied";
}
if (aStoreModel.storeEmailAddress == null)
{
theResponse.statusDescription = "Store's email address was not supplied";
}
if (doesStoreExist(aStoreModel.storeNumber))
{
theResponse.statusCode = 3;
theResponse.statusDescription = "Store Number already exists";
}
if (theResponse.statusDescription.Equals(""))
{
openDataConnection();
SqlCommand cmdCreateStore = new SqlCommand("CreateStore", theConnection);
cmdCreateStore.Parameters.AddWithValue("@storeName", aStoreModel.storeName);
cmdCreateStore.Parameters.AddWithValue("@storeAddress", aStoreModel.storeAddress);
cmdCreateStore.Parameters.AddWithValue("@storeCity", aStoreModel.storeCity);
cmdCreateStore.Parameters.AddWithValue("@storeZip", aStoreModel.storeZip);
cmdCreateStore.Parameters.AddWithValue("@storeState", aStoreModel.storeState);
cmdCreateStore.Parameters.AddWithValue("@storePhone", aStoreModel.storePhone);
cmdCreateStore.Parameters.AddWithValue("@storeEmail", aStoreModel.storeEmailAddress);
cmdCreateStore.Parameters.AddWithValue("@storeManager", aStoreModel.storeManagerName);
cmdCreateStore.Parameters.AddWithValue("@storeNumber", aStoreModel.storeNumber);
cmdCreateStore.Parameters.AddWithValue("@storeOwnershipType", aStoreModel.storeOwnershipType);
cmdCreateStore.Parameters.AddWithValue("@PODRequired", aStoreModel.PODRequired);
cmdCreateStore.CommandType = System.Data.CommandType.StoredProcedure;
int numRowsAffected = cmdCreateStore.ExecuteNonQuery();
if (numRowsAffected > 0)
{
theResponse.statusCode = 0;
theResponse.statusDescription = "";
}
else
{
theResponse.statusCode = 6;
theResponse.statusDescription = "Could not add this store";
}
}
}
else
{
theResponse.statusCode = 6;
theResponse.statusDescription = "Expected User Model not received";
}
return theResponse;
}
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 UpdateRouteStatusToDeactive(string routeId)
{
Response theResponse = new Response();
openDataConnection();
SqlCommand cmdAllRouteMaps = new SqlCommand("update route set status = @status where RouteID = @routeID", theConnection);
cmdAllRouteMaps.Parameters.AddWithValue(@"status", 0);
cmdAllRouteMaps.Parameters.AddWithValue(@"routeID", int.Parse(routeId));
cmdAllRouteMaps.CommandType = System.Data.CommandType.Text;
//cmdUpdate.CommandType = System.Data.CommandType.StoredProcedure;
try
{
int numRowsAffected = cmdAllRouteMaps.ExecuteNonQuery();
if (numRowsAffected > 0)
{
theResponse.statusCode = 0;
theResponse.statusDescription = "";
}
else
{
theResponse.statusCode = 6;
}
}
catch (Exception _exception)
{
theResponse.statusCode = 6;
theResponse.statusDescription = _exception.Message;
}
closeDataConnection();
return theResponse;
}