public Response ReportFieldReadinessWithInterval(string startDate, string endDate)
        {
            if (validateDate(startDate) && validateDate(endDate))
            {
                if (endDate.Length <= 10)
                {
                    endDate += " 23:59:59";
                }

                Response theResponse = new Response();

                openDataConnection();

                /* DVP - RVP Data */

                SqlCommand cmdDVPRVPDeliveriesWithoutIssues = new SqlCommand("ReportDVPRVPNumberOfDeliveriesWithoutIssuesWithInterval", theConnection);
                cmdDVPRVPDeliveriesWithoutIssues.Parameters.AddWithValue("@dateStarted", startDate);
                cmdDVPRVPDeliveriesWithoutIssues.Parameters.AddWithValue("@dateEnded", endDate);
                cmdDVPRVPDeliveriesWithoutIssues.CommandType = System.Data.CommandType.StoredProcedure;
                cmdDVPRVPDeliveriesWithoutIssues.CommandTimeout = 1200;

                theReader = cmdDVPRVPDeliveriesWithoutIssues.ExecuteReader();

                List<DVPRVPSummary> dvprvpData = new List<DVPRVPSummary>();

                if (theReader.HasRows)
                {
                    while (theReader.Read())
                    {
                        DVPRVPSummary thisRow = new DVPRVPSummary();

                        thisRow.dvpName = theReader["DVPOutlookName"].ToString();
                        thisRow.rvpName = theReader["RVPOutlookName"].ToString();
                        thisRow.deliveries = (int)theReader["NumberOfDeliveriesWithoutIssues"];

                        dvprvpData.Add(thisRow);
                    }
                }

                theReader.Close();

                SqlCommand cmdDVPRVPDeliveriesWithIssues = new SqlCommand("ReportDVPRVPNumberOfDeliveriesWithIssuesWithInterval", theConnection);
                cmdDVPRVPDeliveriesWithIssues.Parameters.AddWithValue("@dateStarted", startDate);
                cmdDVPRVPDeliveriesWithIssues.Parameters.AddWithValue("@dateEnded", endDate);
                cmdDVPRVPDeliveriesWithIssues.CommandType = System.Data.CommandType.StoredProcedure;
                cmdDVPRVPDeliveriesWithIssues.CommandTimeout = 1200;

                theReader = cmdDVPRVPDeliveriesWithIssues.ExecuteReader();

                if (theReader.HasRows)
                {
                    while (theReader.Read())
                    {
                        string thisRowsDVP = theReader["DVPOutlookName"].ToString();
                        string thisRowsRVP = theReader["RVPOutlookName"].ToString();

                        bool found = false;

                        for (int i = 0, l = dvprvpData.Count; i < l; i++)
                        {
                            DVPRVPSummary thisRowData = dvprvpData[i];

                            if (thisRowsDVP.Equals(thisRowData.dvpName) && thisRowsRVP.Equals(thisRowData.rvpName))
                            {
                                found = true;

                                int deliveriesWithIssues = (int)theReader["NumberOfDeliveriesWithIssues"];

                                thisRowData.deliveriesWithIssues = deliveriesWithIssues;
                                thisRowData.deliveries += thisRowData.deliveriesWithIssues;
                            }
                        }

                        if (!found)
                        {
                            DVPRVPSummary thisRow = new DVPRVPSummary();

                            thisRow.dvpName = thisRowsDVP;
                            thisRow.rvpName = thisRowsRVP;
                            thisRow.deliveriesWithIssues = (int)theReader["NumberOfDeliveriesWithIssues"];
                            thisRow.deliveries += thisRow.deliveriesWithIssues;

                            dvprvpData.Add(thisRow);

                            found = false;
                        }
                    }
                }

                theReader.Close();

                SqlCommand cmdDVPRVPNumberOfIssues = new SqlCommand("ReportDVPRVPNumberOfIssuesWithInterval", theConnection);
                cmdDVPRVPNumberOfIssues.Parameters.AddWithValue("@dateStarted", startDate);
                cmdDVPRVPNumberOfIssues.Parameters.AddWithValue("@dateEnded", endDate);
                cmdDVPRVPNumberOfIssues.CommandType = System.Data.CommandType.StoredProcedure;
                cmdDVPRVPNumberOfIssues.CommandTimeout = 1200;

                theReader = cmdDVPRVPNumberOfIssues.ExecuteReader();

                if (theReader.HasRows)
                {
                    while (theReader.Read())
                    {
                        string thisRowsDVP = theReader["DVPOutlookName"].ToString();
                        string thisRowsRVP = theReader["RVPOutlookName"].ToString();

                        bool found = false;

                        for (int i = 0, l = dvprvpData.Count; i < l; i++)
                        {
                            DVPRVPSummary thisRowData = dvprvpData[i];

                            if (thisRowsDVP.Equals(thisRowData.dvpName) && thisRowsRVP.Equals(thisRowData.rvpName))
                            {
                                found = true;

                                int numberOfIssues = (int)theReader["NumberOfIssues"];

                                thisRowData.totalReadinessIssues = numberOfIssues;
                            }
                        }

                        if (!found)
                        {
                            DVPRVPSummary thisRow = new DVPRVPSummary();

                            thisRow.dvpName = thisRowsDVP;
                            thisRow.rvpName = thisRowsRVP;
                            thisRow.totalReadinessIssues = (int)theReader["NumberOfIssues"];

                            dvprvpData.Add(thisRow);

                            found = false;
                        }
                    }
                }

                theReader.Close();

                SqlCommand cmdDVPRVPUnitsLeftout = new SqlCommand("ReportDVPRVPUnitsLeftoutWithInterval", theConnection);
                cmdDVPRVPUnitsLeftout.Parameters.AddWithValue("@dateStarted", startDate);
                cmdDVPRVPUnitsLeftout.Parameters.AddWithValue("@dateEnded", endDate);
                cmdDVPRVPUnitsLeftout.CommandType = System.Data.CommandType.StoredProcedure;
                cmdDVPRVPUnitsLeftout.CommandTimeout = 1200;

                theReader = cmdDVPRVPUnitsLeftout.ExecuteReader();

                if (theReader.HasRows)
                {
                    while (theReader.Read())
                    {
                        string thisRowsDVP = theReader["DVPOutlookName"].ToString();
                        string thisRowsRVP = theReader["RVPOutlookName"].ToString();

                        bool found = false;

                        for (int i = 0, l = dvprvpData.Count; i < l; i++)
                        {
                            DVPRVPSummary thisRowData = dvprvpData[i];

                            if (thisRowsDVP.Equals(thisRowData.dvpName) && thisRowsRVP.Equals(thisRowData.rvpName))
                            {
                                found = true;

                                int unitsLeftout = (int)theReader["UnitsLeftout"];

                                thisRowData.leftoutUnits = unitsLeftout;
                            }
                        }

                        if (!found)
                        {
                            DVPRVPSummary thisRow = new DVPRVPSummary();

                            thisRow.dvpName = thisRowsDVP;
                            thisRow.rvpName = thisRowsRVP;
                            thisRow.leftoutUnits = (int)theReader["UnitsLeftout"];

                            dvprvpData.Add(thisRow);

                            found = false;
                        }
                    }
                }

                theReader.Close();

                SqlCommand cmdDVPRVPUnitsLeftoutCost = new SqlCommand("ReportDVPRVPUnitsLeftoutCostWithInterval", theConnection);
                cmdDVPRVPUnitsLeftoutCost.Parameters.AddWithValue("@dateStarted", startDate);
                cmdDVPRVPUnitsLeftoutCost.Parameters.AddWithValue("@dateEnded", endDate);
                cmdDVPRVPUnitsLeftoutCost.CommandType = System.Data.CommandType.StoredProcedure;
                cmdDVPRVPUnitsLeftoutCost.CommandTimeout = 1200;

                theReader = cmdDVPRVPUnitsLeftoutCost.ExecuteReader();

                if (theReader.HasRows)
                {
                    while (theReader.Read())
                    {
                        string thisRowsDVP = theReader["DVPOutlookName"].ToString();
                        string thisRowsRVP = theReader["RVPOutlookName"].ToString();

                        bool found = false;

                        for (int i = 0, l = dvprvpData.Count; i < l; i++)
                        {
                            DVPRVPSummary thisRowData = dvprvpData[i];

                            if (thisRowsDVP.Equals(thisRowData.dvpName) && thisRowsRVP.Equals(thisRowData.rvpName))
                            {
                                found = true;

                                if (theReader["LeftoutCost"] != DBNull.Value)
                                {
                                    double leftoutCost = (double)theReader["LeftoutCost"];

                                    thisRowData.leftoutCOGS = leftoutCost;
                                }
                            }
                        }

                        if (!found)
                        {
                            DVPRVPSummary thisRow = new DVPRVPSummary();

                            thisRow.dvpName = thisRowsDVP;
                            thisRow.rvpName = thisRowsRVP;
                            thisRow.leftoutCOGS = (double)theReader["LeftoutCost"];

                            dvprvpData.Add(thisRow);

                            found = false;
                        }
                    }
                }

                theReader.Close();

                SqlCommand cmdDVPRVPUnitsBackhauled = new SqlCommand("ReportDVPRVPUnitsBackhauledWithInterval", theConnection);
                cmdDVPRVPUnitsBackhauled.Parameters.AddWithValue("@dateStarted", startDate);
                cmdDVPRVPUnitsBackhauled.Parameters.AddWithValue("@dateEnded", endDate);
                cmdDVPRVPUnitsBackhauled.CommandType = System.Data.CommandType.StoredProcedure;
                cmdDVPRVPUnitsBackhauled.CommandTimeout = 1200;

                theReader = cmdDVPRVPUnitsBackhauled.ExecuteReader();

                if (theReader.HasRows)
                {
                    while (theReader.Read())
                    {
                        string thisRowsDVP = theReader["DVPOutlookName"].ToString();
                        string thisRowsRVP = theReader["RVPOutlookName"].ToString();

                        bool found = false;

                        for (int i = 0, l = dvprvpData.Count; i < l; i++)
                        {
                            DVPRVPSummary thisRowData = dvprvpData[i];

                            if (thisRowsDVP.Equals(thisRowData.dvpName) && thisRowsRVP.Equals(thisRowData.rvpName))
                            {
                                found = true;

                                int unitsBackhauled = (int)theReader["UnitsBackhauled"];

                                thisRowData.dairyBackhaulUnits = unitsBackhauled;
                            }
                        }

                        if (!found)
                        {
                            DVPRVPSummary thisRow = new DVPRVPSummary();

                            thisRow.dvpName = thisRowsDVP;
                            thisRow.rvpName = thisRowsRVP;
                            thisRow.dairyBackhaulUnits = (int)theReader["UnitsBackhauled"];

                            dvprvpData.Add(thisRow);

                            found = false;
                        }
                    }
                }

                theReader.Close();

                SqlCommand cmdDVPRVPUnitsBackhauledCost = new SqlCommand("ReportDVPRVPUnitsBackhauledCostWithInterval", theConnection);
                cmdDVPRVPUnitsBackhauledCost.Parameters.AddWithValue("@dateStarted", startDate);
                cmdDVPRVPUnitsBackhauledCost.Parameters.AddWithValue("@dateEnded", endDate);
                cmdDVPRVPUnitsBackhauledCost.CommandType = System.Data.CommandType.StoredProcedure;
                cmdDVPRVPUnitsBackhauledCost.CommandTimeout = 1200;

                theReader = cmdDVPRVPUnitsBackhauledCost.ExecuteReader();

                if (theReader.HasRows)
                {
                    while (theReader.Read())
                    {
                        string thisRowsDVP = theReader["DVPOutlookName"].ToString();
                        string thisRowsRVP = theReader["RVPOutlookName"].ToString();

                        bool found = false;

                        for (int i = 0, l = dvprvpData.Count; i < l; i++)
                        {
                            DVPRVPSummary thisRowData = dvprvpData[i];

                            if (thisRowsDVP.Equals(thisRowData.dvpName) && thisRowsRVP.Equals(thisRowData.rvpName))
                            {
                                found = true;

                                if (theReader["BackhaulCost"] != DBNull.Value)
                                {
                                    double backhaulCost = (double)theReader["BackhaulCost"];

                                    thisRowData.dairyBackhaulCOGS = backhaulCost;
                                }
                            }
                        }

                        if (!found)
                        {
                            DVPRVPSummary thisRow = new DVPRVPSummary();

                            thisRow.dvpName = thisRowsDVP;
                            thisRow.rvpName = thisRowsRVP;
                            thisRow.dairyBackhaulCOGS = (double)theReader["BackhaulCost"];

                            dvprvpData.Add(thisRow);

                            found = false;
                        }
                    }
                }

                theReader.Close();

                /* RD-DM Data */

                SqlCommand cmdRDDMDeliveriesWithoutIssues = new SqlCommand("ReportRDDMNumberOfDeliveriesWithoutIssuesWithInterval", theConnection);
                cmdRDDMDeliveriesWithoutIssues.Parameters.AddWithValue("@dateStarted", startDate);
                cmdRDDMDeliveriesWithoutIssues.Parameters.AddWithValue("@dateEnded", endDate);
                cmdRDDMDeliveriesWithoutIssues.CommandType = System.Data.CommandType.StoredProcedure;
                cmdRDDMDeliveriesWithoutIssues.CommandTimeout = 1200;

                theReader = cmdRDDMDeliveriesWithoutIssues.ExecuteReader();

                List<RDDMSummary> rddmData = new List<RDDMSummary>();

                if (theReader.HasRows)
                {
                    while (theReader.Read())
                    {
                        RDDMSummary thisRow = new RDDMSummary();

                        thisRow.rdName = theReader["RDOutlookName"].ToString();
                        thisRow.dmName = theReader["DMOutlookName"].ToString();
                        thisRow.deliveries = (int)theReader["NumberOfDeliveriesWithoutIssues"];

                        rddmData.Add(thisRow);
                    }
                }

                theReader.Close();

                SqlCommand cmdRDDMDeliveriesWithIssues = new SqlCommand("ReportRDDMNumberOfDeliveriesWithIssuesWithInterval", theConnection);
                cmdRDDMDeliveriesWithIssues.Parameters.AddWithValue("@dateStarted", startDate);
                cmdRDDMDeliveriesWithIssues.Parameters.AddWithValue("@dateEnded", endDate);
                cmdRDDMDeliveriesWithIssues.CommandType = System.Data.CommandType.StoredProcedure;
                cmdRDDMDeliveriesWithIssues.CommandTimeout = 1200;

                theReader = cmdRDDMDeliveriesWithIssues.ExecuteReader();

                if (theReader.HasRows)
                {
                    while (theReader.Read())
                    {
                        string thisRowsRD = theReader["RDOutlookName"].ToString();
                        string thisRowsDM = theReader["DMOutlookName"].ToString();

                        bool found = false;

                        for (int i = 0, l = rddmData.Count; i < l; i++)
                        {
                            RDDMSummary thisRowData = rddmData[i];

                            if (thisRowsRD.Equals(thisRowData.rdName) && thisRowsDM.Equals(thisRowData.dmName))
                            {
                                found = true;

                                int deliveriesWithIssues = (int)theReader["NumberOfDeliveriesWithIssues"];

                                thisRowData.deliveriesWithIssues = deliveriesWithIssues;
                                thisRowData.deliveries += thisRowData.deliveriesWithIssues;
                            }
                        }

                        if (!found)
                        {
                            RDDMSummary thisRow = new RDDMSummary();

                            thisRow.rdName = theReader["RDOutlookName"].ToString();
                            thisRow.dmName = theReader["DMOutlookName"].ToString();
                            thisRow.deliveriesWithIssues = (int)theReader["NumberOfDeliveriesWithIssues"];
                            thisRow.deliveries += thisRow.deliveriesWithIssues;

                            rddmData.Add(thisRow);

                            found = false;
                        }
                    }
                }

                theReader.Close();

                SqlCommand cmdRDDMNumberOfIssues = new SqlCommand("ReportRDDMNumberOfIssuesWithInterval", theConnection);
                cmdRDDMNumberOfIssues.Parameters.AddWithValue("@dateStarted", startDate);
                cmdRDDMNumberOfIssues.Parameters.AddWithValue("@dateEnded", endDate);
                cmdRDDMNumberOfIssues.CommandType = System.Data.CommandType.StoredProcedure;
                cmdRDDMNumberOfIssues.CommandTimeout = 1200;

                theReader = cmdRDDMNumberOfIssues.ExecuteReader();

                if (theReader.HasRows)
                {
                    while (theReader.Read())
                    {
                        string thisRowsRD = theReader["RDOutlookName"].ToString();
                        string thisRowsDM = theReader["DMOutlookName"].ToString();

                        bool found = false;

                        for (int i = 0, l = rddmData.Count; i < l; i++)
                        {
                            RDDMSummary thisRowData = rddmData[i];

                            if (thisRowsRD.Equals(thisRowData.rdName) && thisRowsDM.Equals(thisRowData.dmName))
                            {
                                found = true;

                                int numberOfIssues = (int)theReader["NumberOfIssues"];

                                thisRowData.totalReadinessIssues = numberOfIssues;
                            }
                        }

                        if (!found)
                        {
                            RDDMSummary thisRow = new RDDMSummary();

                            thisRow.rdName = theReader["RDOutlookName"].ToString();
                            thisRow.dmName = theReader["DMOutlookName"].ToString();
                            thisRow.totalReadinessIssues = (int)theReader["NumberOfIssues"];

                            rddmData.Add(thisRow);

                            found = false;
                        }
                    }
                }

                theReader.Close();

                SqlCommand cmdRDDMUnitsBackhauled = new SqlCommand("ReportRDDMUnitsBackhauledWithInterval", theConnection);
                cmdRDDMUnitsBackhauled.Parameters.AddWithValue("@dateStarted", startDate);
                cmdRDDMUnitsBackhauled.Parameters.AddWithValue("@dateEnded", endDate);
                cmdRDDMUnitsBackhauled.CommandType = System.Data.CommandType.StoredProcedure;
                cmdRDDMUnitsBackhauled.CommandTimeout = 1200;

                theReader = cmdRDDMUnitsBackhauled.ExecuteReader();

                if (theReader.HasRows)
                {
                    while (theReader.Read())
                    {
                        string thisRowsRD = theReader["RDOutlookName"].ToString();
                        string thisRowsDM = theReader["DMOutlookName"].ToString();

                        bool found = false;

                        for (int i = 0, l = rddmData.Count; i < l; i++)
                        {
                            RDDMSummary thisRowData = rddmData[i];

                            if (thisRowsRD.Equals(thisRowData.rdName) && thisRowsDM.Equals(thisRowData.dmName))
                            {
                                found = true;

                                int unitsBackhauled = (int)theReader["UnitsBackhauled"];

                                thisRowData.dairyBackhaulUnits = unitsBackhauled;
                            }
                        }

                        if (!found)
                        {
                            RDDMSummary thisRow = new RDDMSummary();

                            thisRow.rdName = theReader["RDOutlookName"].ToString();
                            thisRow.dmName = theReader["DMOutlookName"].ToString();
                            thisRow.dairyBackhaulUnits = (int)theReader["UnitsBackhauled"];

                            rddmData.Add(thisRow);

                            found = false;
                        }
                    }
                }

                theReader.Close();

                SqlCommand cmdRDDMUnitsBackhauledCost = new SqlCommand("ReportRDDMUnitsBackhauledCostWithInterval", theConnection);
                cmdRDDMUnitsBackhauledCost.Parameters.AddWithValue("@dateStarted", startDate);
                cmdRDDMUnitsBackhauledCost.Parameters.AddWithValue("@dateEnded", endDate);
                cmdRDDMUnitsBackhauledCost.CommandType = System.Data.CommandType.StoredProcedure;
                cmdRDDMUnitsBackhauledCost.CommandTimeout = 1200;

                theReader = cmdRDDMUnitsBackhauledCost.ExecuteReader();

                if (theReader.HasRows)
                {
                    while (theReader.Read())
                    {
                        string thisRowsRD = theReader["RDOutlookName"].ToString();
                        string thisRowsDM = theReader["DMOutlookName"].ToString();

                        bool found = false;

                        for (int i = 0, l = rddmData.Count; i < l; i++)
                        {
                            RDDMSummary thisRowData = rddmData[i];

                            if (thisRowsRD.Equals(thisRowData.rdName) && thisRowsDM.Equals(thisRowData.dmName))
                            {
                                found = true;

                                if (theReader["BackhaulCost"] != DBNull.Value)
                                {
                                    double backhaulCost = (double)theReader["BackhaulCost"];

                                    thisRowData.dairyBackhaulCOGS = backhaulCost;
                                }
                            }
                        }

                        if (!found)
                        {
                            RDDMSummary thisRow = new RDDMSummary();

                            thisRow.rdName = theReader["RDOutlookName"].ToString();
                            thisRow.dmName = theReader["DMOutlookName"].ToString();
                            thisRow.dairyBackhaulCOGS = (double)theReader["BackhaulCost"];

                            rddmData.Add(thisRow);

                            found = false;
                        }
                    }
                }

                theReader.Close();

                SqlCommand cmdRDDMNumberOfIssuesOne = new SqlCommand("ReportRDDMGroupOneIssuesWithInterval", theConnection);
                cmdRDDMNumberOfIssuesOne.Parameters.AddWithValue("@dateStarted", startDate);
                cmdRDDMNumberOfIssuesOne.Parameters.AddWithValue("@dateEnded", endDate);
                cmdRDDMNumberOfIssuesOne.CommandType = System.Data.CommandType.StoredProcedure;
                cmdRDDMNumberOfIssuesOne.CommandTimeout = 1200;

                theReader = cmdRDDMNumberOfIssues.ExecuteReader();

                if (theReader.HasRows)
                {
                    while (theReader.Read())
                    {
                        string thisRowsRD = theReader["RDOutlookName"].ToString();
                        string thisRowsDM = theReader["DMOutlookName"].ToString();

                        bool found = false;

                        for (int i = 0, l = rddmData.Count; i < l; i++)
                        {
                            RDDMSummary thisRowData = rddmData[i];

                            if (thisRowsRD.Equals(thisRowData.rdName) && thisRowsDM.Equals(thisRowData.dmName))
                            {
                                found = true;

                                int numberOfIssues = (int)theReader["NumberOfIssues"];

                                thisRowData.totalSecurityFacilityIssues = numberOfIssues;
                            }
                        }

                        if (!found)
                        {
                            RDDMSummary thisRow = new RDDMSummary();

                            thisRow.rdName = theReader["RDOutlookName"].ToString();
                            thisRow.dmName = theReader["DMOutlookName"].ToString();
                            thisRow.totalSecurityFacilityIssues = (int)theReader["NumberOfIssues"];

                            rddmData.Add(thisRow);

                            found = false;
                        }
                    }
                }

                theReader.Close();

                SqlCommand cmdRDDMNumberOfIssuesTwo = new SqlCommand("ReportRDDMGroupTwoIssuesWithInterval", theConnection);
                cmdRDDMNumberOfIssuesTwo.Parameters.AddWithValue("@dateStarted", startDate);
                cmdRDDMNumberOfIssuesTwo.Parameters.AddWithValue("@dateEnded", endDate);
                cmdRDDMNumberOfIssuesTwo.CommandType = System.Data.CommandType.StoredProcedure;
                cmdRDDMNumberOfIssuesTwo.CommandTimeout = 1200;

                theReader = cmdRDDMNumberOfIssuesTwo.ExecuteReader();

                if (theReader.HasRows)
                {
                    while (theReader.Read())
                    {
                        string thisRowsRD = theReader["RDOutlookName"].ToString();
                        string thisRowsDM = theReader["DMOutlookName"].ToString();

                        bool found = false;

                        for (int i = 0, l = rddmData.Count; i < l; i++)
                        {
                            RDDMSummary thisRowData = rddmData[i];

                            if (thisRowsRD.Equals(thisRowData.rdName) && thisRowsDM.Equals(thisRowData.dmName))
                            {
                                found = true;

                                int numberOfIssues = (int)theReader["NumberOfIssues"];

                                thisRowData.totalCapacityIssues = numberOfIssues;
                            }
                        }

                        if (!found)
                        {
                            RDDMSummary thisRow = new RDDMSummary();

                            thisRow.rdName = theReader["RDOutlookName"].ToString();
                            thisRow.dmName = theReader["DMOutlookName"].ToString();
                            thisRow.totalCapacityIssues = (int)theReader["NumberOfIssues"];

                            rddmData.Add(thisRow);

                            found = false;
                        }
                    }
                }

                theReader.Close();

                SqlCommand cmdRDDMNumberOfIssuesThree = new SqlCommand("ReportRDDMGroupThreeIssuesWithInterval", theConnection);
                cmdRDDMNumberOfIssuesThree.Parameters.AddWithValue("@dateStarted", startDate);
                cmdRDDMNumberOfIssuesThree.Parameters.AddWithValue("@dateEnded", endDate);
                cmdRDDMNumberOfIssuesThree.CommandType = System.Data.CommandType.StoredProcedure;
                cmdRDDMNumberOfIssuesThree.CommandTimeout = 1200;

                theReader = cmdRDDMNumberOfIssuesThree.ExecuteReader();

                if (theReader.HasRows)
                {
                    while (theReader.Read())
                    {
                        string thisRowsRD = theReader["RDOutlookName"].ToString();
                        string thisRowsDM = theReader["DMOutlookName"].ToString();

                        bool found = false;

                        for (int i = 0, l = rddmData.Count; i < l; i++)
                        {
                            RDDMSummary thisRowData = rddmData[i];

                            if (thisRowsRD.Equals(thisRowData.rdName) && thisRowsDM.Equals(thisRowData.dmName))
                            {
                                found = true;

                                int numberOfIssues = (int)theReader["NumberOfIssues"];

                                thisRowData.totalProductivityIssues = numberOfIssues;
                            }
                        }

                        if (!found)
                        {
                            RDDMSummary thisRow = new RDDMSummary();

                            thisRow.rdName = theReader["RDOutlookName"].ToString();
                            thisRow.dmName = theReader["DMOutlookName"].ToString();
                            thisRow.totalProductivityIssues = (int)theReader["NumberOfIssues"];

                            rddmData.Add(thisRow);

                            found = false;
                        }
                    }
                }

                theReader.Close();

                closeDataConnection();

                try
                {
                    string newFilePath = CopyReportTemplate("field");

                    /*string oleConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + newFilePath + ";Extended Properties=\"Excel 8.0;\"";
                    OleDbConnection oleConnection = new OleDbConnection(oleConnectionString);
                    oleConnection.Open();*/

                    openDataConnection();
                    SqlCommand cmdReport = new SqlCommand("ReportStoresNotReadyWithInterval", theConnection);
                    cmdReport.Parameters.AddWithValue("@dateStarted", startDate);
                    cmdReport.Parameters.AddWithValue("@dateEnded", endDate);
                    cmdReport.CommandType = System.Data.CommandType.StoredProcedure;
                    cmdReport.CommandTimeout = 1200;

                    theReader = cmdReport.ExecuteReader();

                    FileStream template = new FileStream(newFilePath, FileMode.Open, FileAccess.Read);
                    //FileStream fileRead = new FileStream(destFile, FileMode.Create, FileAccess.ReadWrite);
                    //IWorkbook theWorkbook = new XSSFWorkbook(fileRead);
                    //Create a stream of .xlsx file contained within my project using reflection

                    //EPPlusTest = Namespace/Project
                    //templates = folder
                    //VendorTemplate.xlsx = file

                    //ExcelPackage has a constructor that only requires a stream.
                    ExcelPackage pck = new OfficeOpenXml.ExcelPackage(template);
                    ExcelWorkbook workBook = pck.Workbook;
                    //fileRead.Close();
                    template.Close();

                    if (theReader.HasRows)
                    {
                        int rowIndex = 1;
                        //ISheet notReadySheet = theWorkbook.GetSheet("DetailedViewNotReady");
                        ExcelWorksheet notReadySheet = workBook.Worksheets["DetailedViewNotReady"];
                        //notReadySheet.ForceFormulaRecalculation = true;
                        string photoVal = "";
                        //IRow crow = null;

                        while (theReader.Read())
                        {
                            rowIndex++;
                            //crow = notReadySheet.CreateRow(rowIndex);
                            //DateTime todaysDateTime = DateTime.Today;
                            //DateTime thisEntrysDateTime = Convert.ToDateTime(theReader[0].ToString());
                            //TimeSpan timeDifference = todaysDateTime - thisEntrysDateTime;
                            //int differenceInDays = timeDifference.Days;
                            photoVal = "";
                            notReadySheet.Cells[rowIndex, 1].Value = theReader[0].ToString();
                            notReadySheet.Cells[rowIndex, 2].Value = theReader[1].ToString();
                            notReadySheet.Cells[rowIndex, 3].Value = theReader[2].ToString();
                            notReadySheet.Cells[rowIndex, 4].Value = theReader[3].ToString();
                            notReadySheet.Cells[rowIndex, 5].Value = theReader[4].ToString();
                            notReadySheet.Cells[rowIndex, 6].Value = theReader[5].ToString();
                            notReadySheet.Cells[rowIndex, 7].Value = theReader[6].ToString();
                            notReadySheet.Cells[rowIndex, 8].Value = theReader[7].ToString();
                            notReadySheet.Cells[rowIndex, 9].Value = theReader[8].ToString();
                            notReadySheet.Cells[rowIndex, 10].Value = theReader[9].ToString();
                            notReadySheet.Cells[rowIndex, 11].Value = theReader[10].ToString();
                            /*           notReadySheet.Cells[rowIndex, 12].Value = theReader[11].ToString();
                                       notReadySheet.Cells[rowIndex, 13].Value = getCellFriendlyText(theReader[12].ToString());
                                       if (differenceInDays <= 21 && theReader[13].ToString() != null && !theReader[13].ToString().Equals(""))
                                       {
                                           photoVal += ",'" + baseURLForPhotoLink + theReader[13].ToString() + "'";
                                       }
                                       else
                                       {
                                           photoVal += ",'No Photos Available'";
                                       }
                                       notReadySheet.Cells[rowIndex, 14].Value = photoVal;
                   */

                            notReadySheet.Cells[rowIndex, 12].Value = theReader[11].ToString();
                            notReadySheet.Cells[rowIndex, 13].Value = Math.Round(Convert.ToDecimal(theReader[14].ToString()), 2).ToString();
                            notReadySheet.Cells[rowIndex, 14].Value = getCellFriendlyText(theReader[12].ToString());
                            if (theReader[13].ToString() != null && !theReader[13].ToString().Equals(""))
                            {
                                photoVal += "" + baseURLForPhotoLink + theReader[13].ToString() + "";
                            }
                            else
                            {
                                photoVal += "No Photos Available";
                            }
                            notReadySheet.Cells[rowIndex, 15].Value = photoVal;

                        }
                        theResponse.statusCode = 0;
                        theResponse.statusDescription = extractFilename(newFilePath);
                    }
                    else
                    {
                        theResponse.statusCode = 1;
                        theResponse.statusDescription = "There is no data logged between the dates that were selected";
                    }
                    theReader.Close();

                    SqlCommand cmdReportNotReady = new SqlCommand("ReportStoresReadyWithInterval", theConnection);
                    cmdReportNotReady.Parameters.AddWithValue("@dateStarted", startDate);
                    cmdReportNotReady.Parameters.AddWithValue("@dateEnded", endDate);
                    cmdReportNotReady.CommandType = System.Data.CommandType.StoredProcedure;
                    cmdReportNotReady.CommandTimeout = 1200;

                    theReader = cmdReportNotReady.ExecuteReader();
                    if (theReader.HasRows)
                    {
                        int rowIndex = 1;
                        ExcelWorksheet readySheet = workBook.Worksheets["DetailedViewReady"];

                        while (theReader.Read())
                        {
                            rowIndex++;

                            readySheet.Cells[rowIndex, 1].Value = theReader[0].ToString();
                            readySheet.Cells[rowIndex, 2].Value = theReader[1].ToString();
                            readySheet.Cells[rowIndex, 3].Value = theReader[2].ToString();
                            readySheet.Cells[rowIndex, 4].Value = theReader[3].ToString();
                            readySheet.Cells[rowIndex, 5].Value = theReader[4].ToString();

                        }
                        theResponse.statusCode = 0;
                        theResponse.statusDescription = extractFilename(newFilePath);
                    }
                    else
                    {
                        theResponse.statusCode = 1;
                        theResponse.statusDescription = "There is no data logged between the dates that were selected";
                    }
                    theReader.Close();

                    /*if (theReader.HasRows)
                    {
                        while (theReader.Read())
                        {
                            DateTime todaysDateTime = DateTime.Today;
                            DateTime thisEntrysDateTime = Convert.ToDateTime(theReader[0].ToString());
                            TimeSpan timeDifference = todaysDateTime - thisEntrysDateTime;
                            int differenceInDays = timeDifference.Days;

                            string values = "'" + theReader[0] + "','" + theReader[1] + "','" + theReader[2] + "','" + theReader[3] + "','" + theReader[4] + "','" + theReader[5] + "','" + theReader[6] + "','" + theReader[7] + "','" + theReader[8] + "','" + theReader[9] + "','" + theReader[10] + "','" + theReader[11] + "','" + getCellFriendlyText(theReader[12].ToString()) + "'";

                            if (differenceInDays <= 21 && theReader[13].ToString() != null && !theReader[13].ToString().Equals(""))
                            {
                                values += ",'" + baseURLForPhotoLink + theReader[13].ToString() + "'";
                            }
                            else
                            {
                                values += ",'No Photos Available'";
                            }

                            OleDbCommand oleCommand = new OleDbCommand();
                            string sqlCommand = "INSERT INTO [DetailedViewNotReady$] (InStoreDate, StoreNumber, Provider, CDC, Route, DSVP, RVP, RD, DM, ReasonCode, Photos, UnitsBackhauled, Comments, PhotoLink) VALUES (" + values + ")";
                            oleCommand.CommandText = sqlCommand;
                            oleCommand.Connection = oleConnection;
                            oleCommand.ExecuteNonQuery();
                        }

                        theResponse.statusCode = 0;
                        theResponse.statusDescription = newFilePath;
                    }
                    else
                    {
                        theResponse.statusCode = 1;
                        theResponse.statusDescription = "There is no data logged between the dates that were selected";
                    }

                    theReader.Close();

                    SqlCommand cmdReportNotReady = new SqlCommand("ReportStoresReadyWithInterval", theConnection);
                    cmdReportNotReady.Parameters.AddWithValue("@dateStarted", startDate);
                    cmdReportNotReady.Parameters.AddWithValue("@dateEnded", endDate);
                    cmdReportNotReady.CommandType = System.Data.CommandType.StoredProcedure;

                    theReader = cmdReportNotReady.ExecuteReader();

                    if (theReader.HasRows)
                    {
                        while (theReader.Read())
                        {
                           /* DateTime todaysDateTime = DateTime.Today;
                            DateTime thisEntrysDateTime = Convert.ToDateTime(theReader[0].ToString());
                            TimeSpan timeDifference = todaysDateTime - thisEntrysDateTime;
                            int differenceInDays = timeDifference.Days;

                            string values = "'" + theReader[0] + "','" + theReader[1] + "','" + theReader[2] + "','" + theReader[3] + "','" + theReader[4] + "'";

                            OleDbCommand oleCommand = new OleDbCommand();
                            string sqlCommand = "INSERT INTO 	[DetailedViewReady$] (InStoreDate, StoreNumber, Provider, CDC, Route) VALUES (" + values + ")";
                            oleCommand.CommandText = sqlCommand;
                            oleCommand.Connection = oleConnection;
                            oleCommand.ExecuteNonQuery();
                        }

                        theResponse.statusCode = 0;
                        theResponse.statusDescription = newFilePath;
                    }

                    theReader.Close();

                    oleConnection.Close();*/

                    ExcelWorksheet sheetSummary = workBook.Worksheets["DVPRVPSummary"];

                    sheetSummary.Cells["A2:C2"].Merge = true;
                    sheetSummary.Cells[2, 1].Value = "All Data";

                    if (dvprvpData != null && dvprvpData.Count > 0)
                    {
                        int currentRowIndex = 3;

                        for (int i = 0, l = dvprvpData.Count; i < l; i++)
                        {
                            DVPRVPSummary thisRowsData = dvprvpData[i];

                            thisRowsData.percentageStoresReady = Math.Round((100 - ((double)thisRowsData.deliveriesWithIssues / (double)thisRowsData.deliveries) * 100.0), 2);
                            thisRowsData.leftoutCOGS = Math.Round(thisRowsData.leftoutCOGS, 2);
                            thisRowsData.dairyBackhaulCOGS = Math.Round(thisRowsData.dairyBackhaulCOGS, 2);

                            sheetSummary.Cells[currentRowIndex, 1].Value = thisRowsData.dvpName;
                            sheetSummary.Cells[currentRowIndex, 2].Value = thisRowsData.rvpName;
                            sheetSummary.Cells[currentRowIndex, 3].Value = thisRowsData.percentageStoresReady.ToString() + " %";
                            //         sheetSummary.Cells[currentRowIndex, 4].Value = thisRowsData.leftoutUnits;
                            //         sheetSummary.Cells[currentRowIndex, 5].Value = "$ " + thisRowsData.leftoutCOGS.ToString();
                            //         sheetSummary.Cells[currentRowIndex, 6].Value = thisRowsData.dairyBackhaulUnits;
                            sheetSummary.Cells[currentRowIndex, 4].Value = "$ " + thisRowsData.dairyBackhaulCOGS.ToString();
                            sheetSummary.Cells[currentRowIndex, 5].Value = thisRowsData.deliveries;
                            sheetSummary.Cells[currentRowIndex, 6].Value = thisRowsData.deliveriesWithIssues;
                            sheetSummary.Cells[currentRowIndex, 7].Value = thisRowsData.totalReadinessIssues;

                            currentRowIndex++;
                        }

                        theResponse.statusCode = 0;
                        theResponse.statusDescription = extractFilename(newFilePath);
                    }
                    else
                    {
                        theResponse.statusCode = 1;
                        theResponse.statusDescription = "There is no data logged between the dates that were selected";
                    }

                    ExcelWorksheet sheetRDDMSummary = workBook.Worksheets["RDDMSummary"];

                    sheetRDDMSummary.Cells["A2:C2"].Merge = true;
                    sheetRDDMSummary.Cells[2, 1].Value = "All Data";

                    if (rddmData != null && rddmData.Count > 0)
                    {
                        int currentRowIndex = 3;

                        //IRow currentRow = null;

                        for (int i = 0, l = rddmData.Count; i < l; i++)
                        {
                            RDDMSummary thisRowsData = rddmData[i];

                            thisRowsData.percentageStoresReady = Math.Round((100 - ((double)thisRowsData.deliveriesWithIssues / (double)thisRowsData.deliveries) * 100.0), 2);

                            thisRowsData.dairyBackhaulCOGS = Math.Round(thisRowsData.dairyBackhaulCOGS, 2);
                            // currentRow = sheetRDDMSummary.CreateRow(currentRowIndex);

                            sheetRDDMSummary.Cells[currentRowIndex, 1].Value = thisRowsData.rdName;
                            sheetRDDMSummary.Cells[currentRowIndex, 2].Value = thisRowsData.dmName;
                            sheetRDDMSummary.Cells[currentRowIndex, 3].Value = thisRowsData.percentageStoresReady.ToString() + " %";
                            //        sheetRDDMSummary.Cells[currentRowIndex, 4].Value = thisRowsData.dairyBackhaulUnits;
                            sheetRDDMSummary.Cells[currentRowIndex, 4].Value = "$ " + thisRowsData.dairyBackhaulCOGS.ToString();
                            sheetRDDMSummary.Cells[currentRowIndex, 5].Value = thisRowsData.deliveries;
                            sheetRDDMSummary.Cells[currentRowIndex, 6].Value = thisRowsData.deliveriesWithIssues;
                            sheetRDDMSummary.Cells[currentRowIndex, 7].Value = thisRowsData.totalReadinessIssues;
                            sheetRDDMSummary.Cells[currentRowIndex, 8].Value = thisRowsData.totalSecurityFacilityIssues;
                            sheetRDDMSummary.Cells[currentRowIndex, 9].Value = thisRowsData.totalCapacityIssues;
                            sheetRDDMSummary.Cells[currentRowIndex, 10].Value = thisRowsData.totalProductivityIssues;

                            currentRowIndex++;
                        }

                        theResponse.statusCode = 0;
                        theResponse.statusDescription = extractFilename(newFilePath);
                    }
                    else
                    {
                        theResponse.statusCode = 1;
                        theResponse.statusDescription = "There is no data logged between the dates that were selected";
                    }

                    FileStream fileSave = new FileStream(newFilePath, FileMode.Create);
                    //theWorkbook.Write(fileSave);
                    pck.SaveAs(fileSave);
                    fileSave.Close();

                    closeDataConnection();
                }
                catch (Exception _exception)
                {
                    theResponse.statusCode = 6;
                    theResponse.statusDescription = _exception.Message + " " + _exception.StackTrace;
                }

                return theResponse;
            }
            else
            {
                return ReportFieldReadiness();
            }
        }
        public Response DotNetReportFieldReadiness(string startDate, string endDate, string startHour, string endHour)
        {
            startDate = startDate + " " + startHour + ":00:00";
            endDate = endDate + " " + endHour + ":00:00";

            if (validateDate(startDate) && validateDate(endDate))
            {
                if (endDate.Length <= 10)
                {
                    endDate += " 23:59:59";
                }

                Response theResponse = new Response();

                openDataConnection();

                /* DVP - RVP Data */

                SqlCommand cmdDVPRVPDeliveriesWithoutIssues = new SqlCommand("ReportDVPRVPNumberOfDeliveriesWithoutIssuesWithInterval", theConnection);
                cmdDVPRVPDeliveriesWithoutIssues.Parameters.AddWithValue("@dateStarted", startDate);
                cmdDVPRVPDeliveriesWithoutIssues.Parameters.AddWithValue("@dateEnded", endDate);
                cmdDVPRVPDeliveriesWithoutIssues.CommandType = System.Data.CommandType.StoredProcedure;
                cmdDVPRVPDeliveriesWithoutIssues.CommandTimeout = 1200;

                theReader = cmdDVPRVPDeliveriesWithoutIssues.ExecuteReader();

                List<DVPRVPSummary> dvprvpData = new List<DVPRVPSummary>();

                if (theReader.HasRows)
                {
                    while (theReader.Read())
                    {
                        DVPRVPSummary thisRow = new DVPRVPSummary();

                        thisRow.dvpName = theReader["DVPOutlookName"].ToString();
                        thisRow.rvpName = theReader["RVPOutlookName"].ToString();
                        thisRow.deliveries = (int)theReader["NumberOfDeliveriesWithoutIssues"];

                        dvprvpData.Add(thisRow);
                    }
                }

                theReader.Close();

                SqlCommand cmdDVPRVPDeliveriesWithIssues = new SqlCommand("ReportDVPRVPNumberOfDeliveriesWithIssuesWithInterval", theConnection);
                cmdDVPRVPDeliveriesWithIssues.Parameters.AddWithValue("@dateStarted", startDate);
                cmdDVPRVPDeliveriesWithIssues.Parameters.AddWithValue("@dateEnded", endDate);
                cmdDVPRVPDeliveriesWithIssues.CommandType = System.Data.CommandType.StoredProcedure;
                cmdDVPRVPDeliveriesWithIssues.CommandTimeout = 1200;

                theReader = cmdDVPRVPDeliveriesWithIssues.ExecuteReader();

                if (theReader.HasRows)
                {
                    while (theReader.Read())
                    {
                        string thisRowsDVP = theReader["DVPOutlookName"].ToString();
                        string thisRowsRVP = theReader["RVPOutlookName"].ToString();

                        bool found = false;

                        for (int i = 0, l = dvprvpData.Count; i < l; i++)
                        {
                            DVPRVPSummary thisRowData = dvprvpData[i];

                            if (thisRowsDVP.Equals(thisRowData.dvpName) && thisRowsRVP.Equals(thisRowData.rvpName))
                            {
                                found = true;

                                int deliveriesWithIssues = (int)theReader["NumberOfDeliveriesWithIssues"];

                                thisRowData.deliveriesWithIssues = deliveriesWithIssues;
                                thisRowData.deliveries += thisRowData.deliveriesWithIssues;
                            }
                        }

                        if (!found)
                        {
                            DVPRVPSummary thisRow = new DVPRVPSummary();

                            thisRow.dvpName = thisRowsDVP;
                            thisRow.rvpName = thisRowsRVP;
                            thisRow.deliveriesWithIssues = (int)theReader["NumberOfDeliveriesWithIssues"];
                            thisRow.deliveries += thisRow.deliveriesWithIssues;

                            dvprvpData.Add(thisRow);

                            found = false;
                        }
                    }
                }

                theReader.Close();

                SqlCommand cmdDVPRVPNumberOfIssues = new SqlCommand("ReportDVPRVPNumberOfIssuesWithInterval", theConnection);
                cmdDVPRVPNumberOfIssues.Parameters.AddWithValue("@dateStarted", startDate);
                cmdDVPRVPNumberOfIssues.Parameters.AddWithValue("@dateEnded", endDate);
                cmdDVPRVPNumberOfIssues.CommandType = System.Data.CommandType.StoredProcedure;
                cmdDVPRVPNumberOfIssues.CommandTimeout = 1200;

                theReader = cmdDVPRVPNumberOfIssues.ExecuteReader();

                if (theReader.HasRows)
                {
                    while (theReader.Read())
                    {
                        string thisRowsDVP = theReader["DVPOutlookName"].ToString();
                        string thisRowsRVP = theReader["RVPOutlookName"].ToString();

                        bool found = false;

                        for (int i = 0, l = dvprvpData.Count; i < l; i++)
                        {
                            DVPRVPSummary thisRowData = dvprvpData[i];

                            if (thisRowsDVP.Equals(thisRowData.dvpName) && thisRowsRVP.Equals(thisRowData.rvpName))
                            {
                                found = true;

                                int numberOfIssues = (int)theReader["NumberOfIssues"];

                                thisRowData.totalReadinessIssues = numberOfIssues;
                            }
                        }

                        if (!found)
                        {
                            DVPRVPSummary thisRow = new DVPRVPSummary();

                            thisRow.dvpName = thisRowsDVP;
                            thisRow.rvpName = thisRowsRVP;
                            thisRow.totalReadinessIssues = (int)theReader["NumberOfIssues"];

                            dvprvpData.Add(thisRow);

                            found = false;
                        }
                    }
                }

                theReader.Close();

                SqlCommand cmdDVPRVPUnitsLeftout = new SqlCommand("ReportDVPRVPUnitsLeftoutWithInterval", theConnection);
                cmdDVPRVPUnitsLeftout.Parameters.AddWithValue("@dateStarted", startDate);
                cmdDVPRVPUnitsLeftout.Parameters.AddWithValue("@dateEnded", endDate);
                cmdDVPRVPUnitsLeftout.CommandType = System.Data.CommandType.StoredProcedure;
                cmdDVPRVPUnitsLeftout.CommandTimeout = 1200;

                theReader = cmdDVPRVPUnitsLeftout.ExecuteReader();

                if (theReader.HasRows)
                {
                    while (theReader.Read())
                    {
                        string thisRowsDVP = theReader["DVPOutlookName"].ToString();
                        string thisRowsRVP = theReader["RVPOutlookName"].ToString();

                        bool found = false;

                        for (int i = 0, l = dvprvpData.Count; i < l; i++)
                        {
                            DVPRVPSummary thisRowData = dvprvpData[i];

                            if (thisRowsDVP.Equals(thisRowData.dvpName) && thisRowsRVP.Equals(thisRowData.rvpName))
                            {
                                found = true;

                                int unitsLeftout = (int)theReader["UnitsLeftout"];

                                thisRowData.leftoutUnits = unitsLeftout;
                            }
                        }

                        if (!found)
                        {
                            DVPRVPSummary thisRow = new DVPRVPSummary();

                            thisRow.dvpName = thisRowsDVP;
                            thisRow.rvpName = thisRowsRVP;
                            thisRow.leftoutUnits = (int)theReader["UnitsLeftout"];

                            dvprvpData.Add(thisRow);

                            found = false;
                        }
                    }
                }

                theReader.Close();

                SqlCommand cmdDVPRVPUnitsLeftoutCost = new SqlCommand("ReportDVPRVPUnitsLeftoutCostWithInterval", theConnection);
                cmdDVPRVPUnitsLeftoutCost.Parameters.AddWithValue("@dateStarted", startDate);
                cmdDVPRVPUnitsLeftoutCost.Parameters.AddWithValue("@dateEnded", endDate);
                cmdDVPRVPUnitsLeftoutCost.CommandType = System.Data.CommandType.StoredProcedure;
                cmdDVPRVPUnitsLeftoutCost.CommandTimeout = 1200;

                theReader = cmdDVPRVPUnitsLeftoutCost.ExecuteReader();

                if (theReader.HasRows)
                {
                    while (theReader.Read())
                    {
                        string thisRowsDVP = theReader["DVPOutlookName"].ToString();
                        string thisRowsRVP = theReader["RVPOutlookName"].ToString();

                        bool found = false;

                        for (int i = 0, l = dvprvpData.Count; i < l; i++)
                        {
                            DVPRVPSummary thisRowData = dvprvpData[i];

                            if (thisRowsDVP.Equals(thisRowData.dvpName) && thisRowsRVP.Equals(thisRowData.rvpName))
                            {
                                found = true;

                                if (theReader["LeftoutCost"] != DBNull.Value)
                                {
                                    double leftoutCost = (double)theReader["LeftoutCost"];

                                    thisRowData.leftoutCOGS = leftoutCost;
                                }
                            }
                        }

                        if (!found)
                        {
                            DVPRVPSummary thisRow = new DVPRVPSummary();

                            thisRow.dvpName = thisRowsDVP;
                            thisRow.rvpName = thisRowsRVP;
                            thisRow.leftoutCOGS = (double)theReader["LeftoutCost"];

                            dvprvpData.Add(thisRow);

                            found = false;
                        }
                    }
                }

                theReader.Close();

                SqlCommand cmdDVPRVPUnitsBackhauled = new SqlCommand("ReportDVPRVPUnitsBackhauledWithInterval", theConnection);
                cmdDVPRVPUnitsBackhauled.Parameters.AddWithValue("@dateStarted", startDate);
                cmdDVPRVPUnitsBackhauled.Parameters.AddWithValue("@dateEnded", endDate);
                cmdDVPRVPUnitsBackhauled.CommandType = System.Data.CommandType.StoredProcedure;
                cmdDVPRVPUnitsBackhauled.CommandTimeout = 1200;

                theReader = cmdDVPRVPUnitsBackhauled.ExecuteReader();

                if (theReader.HasRows)
                {
                    while (theReader.Read())
                    {
                        string thisRowsDVP = theReader["DVPOutlookName"].ToString();
                        string thisRowsRVP = theReader["RVPOutlookName"].ToString();

                        bool found = false;

                        for (int i = 0, l = dvprvpData.Count; i < l; i++)
                        {
                            DVPRVPSummary thisRowData = dvprvpData[i];

                            if (thisRowsDVP.Equals(thisRowData.dvpName) && thisRowsRVP.Equals(thisRowData.rvpName))
                            {
                                found = true;

                                int unitsBackhauled = (int)theReader["UnitsBackhauled"];

                                thisRowData.dairyBackhaulUnits = unitsBackhauled;
                            }
                        }

                        if (!found)
                        {
                            DVPRVPSummary thisRow = new DVPRVPSummary();

                            thisRow.dvpName = thisRowsDVP;
                            thisRow.rvpName = thisRowsRVP;
                            thisRow.dairyBackhaulUnits = (int)theReader["UnitsBackhauled"];

                            dvprvpData.Add(thisRow);

                            found = false;
                        }
                    }
                }

                theReader.Close();

                SqlCommand cmdDVPRVPUnitsBackhauledCost = new SqlCommand("ReportDVPRVPUnitsBackhauledCostWithInterval", theConnection);
                cmdDVPRVPUnitsBackhauledCost.Parameters.AddWithValue("@dateStarted", startDate);
                cmdDVPRVPUnitsBackhauledCost.Parameters.AddWithValue("@dateEnded", endDate);
                cmdDVPRVPUnitsBackhauledCost.CommandType = System.Data.CommandType.StoredProcedure;
                cmdDVPRVPUnitsBackhauledCost.CommandTimeout = 1200;

                theReader = cmdDVPRVPUnitsBackhauledCost.ExecuteReader();

                if (theReader.HasRows)
                {
                    while (theReader.Read())
                    {
                        string thisRowsDVP = theReader["DVPOutlookName"].ToString();
                        string thisRowsRVP = theReader["RVPOutlookName"].ToString();

                        bool found = false;

                        for (int i = 0, l = dvprvpData.Count; i < l; i++)
                        {
                            DVPRVPSummary thisRowData = dvprvpData[i];

                            if (thisRowsDVP.Equals(thisRowData.dvpName) && thisRowsRVP.Equals(thisRowData.rvpName))
                            {
                                found = true;

                                if (theReader["BackhaulCost"] != DBNull.Value)
                                {
                                    double backhaulCost = (double)theReader["BackhaulCost"];

                                    thisRowData.dairyBackhaulCOGS = backhaulCost;
                                }
                            }
                        }

                        if (!found)
                        {
                            DVPRVPSummary thisRow = new DVPRVPSummary();

                            thisRow.dvpName = thisRowsDVP;
                            thisRow.rvpName = thisRowsRVP;
                            thisRow.dairyBackhaulCOGS = (double)theReader["BackhaulCost"];

                            dvprvpData.Add(thisRow);

                            found = false;
                        }
                    }
                }

                theReader.Close();

                /* RD-DM Data */

                SqlCommand cmdRDDMDeliveriesWithoutIssues = new SqlCommand("ReportRDDMNumberOfDeliveriesWithoutIssuesWithInterval", theConnection);
                cmdRDDMDeliveriesWithoutIssues.Parameters.AddWithValue("@dateStarted", startDate);
                cmdRDDMDeliveriesWithoutIssues.Parameters.AddWithValue("@dateEnded", endDate);
                cmdRDDMDeliveriesWithoutIssues.CommandType = System.Data.CommandType.StoredProcedure;
                cmdRDDMDeliveriesWithoutIssues.CommandTimeout = 1200;

                theReader = cmdRDDMDeliveriesWithoutIssues.ExecuteReader();

                List<RDDMSummary> rddmData = new List<RDDMSummary>();

                if (theReader.HasRows)
                {
                    while (theReader.Read())
                    {
                        RDDMSummary thisRow = new RDDMSummary();

                        thisRow.rdName = theReader["RDOutlookName"].ToString();
                        thisRow.dmName = theReader["DMOutlookName"].ToString();
                        thisRow.deliveries = (int)theReader["NumberOfDeliveriesWithoutIssues"];

                        rddmData.Add(thisRow);
                    }
                }

                theReader.Close();

                SqlCommand cmdRDDMDeliveriesWithIssues = new SqlCommand("ReportRDDMNumberOfDeliveriesWithIssuesWithInterval", theConnection);
                cmdRDDMDeliveriesWithIssues.Parameters.AddWithValue("@dateStarted", startDate);
                cmdRDDMDeliveriesWithIssues.Parameters.AddWithValue("@dateEnded", endDate);
                cmdRDDMDeliveriesWithIssues.CommandType = System.Data.CommandType.StoredProcedure;
                cmdRDDMDeliveriesWithIssues.CommandTimeout = 1200;

                theReader = cmdRDDMDeliveriesWithIssues.ExecuteReader();

                if (theReader.HasRows)
                {
                    while (theReader.Read())
                    {
                        string thisRowsRD = theReader["RDOutlookName"].ToString();
                        string thisRowsDM = theReader["DMOutlookName"].ToString();

                        bool found = false;

                        for (int i = 0, l = rddmData.Count; i < l; i++)
                        {
                            RDDMSummary thisRowData = rddmData[i];

                            if (thisRowsRD.Equals(thisRowData.rdName) && thisRowsDM.Equals(thisRowData.dmName))
                            {
                                found = true;

                                int deliveriesWithIssues = (int)theReader["NumberOfDeliveriesWithIssues"];

                                thisRowData.deliveriesWithIssues = deliveriesWithIssues;
                                thisRowData.deliveries += thisRowData.deliveriesWithIssues;
                            }
                        }

                        if (!found)
                        {
                            RDDMSummary thisRow = new RDDMSummary();

                            thisRow.rdName = theReader["RDOutlookName"].ToString();
                            thisRow.dmName = theReader["DMOutlookName"].ToString();
                            thisRow.deliveriesWithIssues = (int)theReader["NumberOfDeliveriesWithIssues"];
                            thisRow.deliveries += thisRow.deliveriesWithIssues;

                            rddmData.Add(thisRow);

                            found = false;
                        }
                    }
                }

                theReader.Close();

                SqlCommand cmdRDDMNumberOfIssues = new SqlCommand("ReportRDDMNumberOfIssuesWithInterval", theConnection);
                cmdRDDMNumberOfIssues.Parameters.AddWithValue("@dateStarted", startDate);
                cmdRDDMNumberOfIssues.Parameters.AddWithValue("@dateEnded", endDate);
                cmdRDDMNumberOfIssues.CommandType = System.Data.CommandType.StoredProcedure;
                cmdRDDMNumberOfIssues.CommandTimeout = 1200;

                theReader = cmdRDDMNumberOfIssues.ExecuteReader();

                if (theReader.HasRows)
                {
                    while (theReader.Read())
                    {
                        string thisRowsRD = theReader["RDOutlookName"].ToString();
                        string thisRowsDM = theReader["DMOutlookName"].ToString();

                        bool found = false;

                        for (int i = 0, l = rddmData.Count; i < l; i++)
                        {
                            RDDMSummary thisRowData = rddmData[i];

                            if (thisRowsRD.Equals(thisRowData.rdName) && thisRowsDM.Equals(thisRowData.dmName))
                            {
                                found = true;

                                int numberOfIssues = (int)theReader["NumberOfIssues"];

                                thisRowData.totalReadinessIssues = numberOfIssues;
                            }
                        }

                        if (!found)
                        {
                            RDDMSummary thisRow = new RDDMSummary();

                            thisRow.rdName = theReader["RDOutlookName"].ToString();
                            thisRow.dmName = theReader["DMOutlookName"].ToString();
                            thisRow.totalReadinessIssues = (int)theReader["NumberOfIssues"];

                            rddmData.Add(thisRow);

                            found = false;
                        }
                    }
                }

                theReader.Close();

                SqlCommand cmdRDDMUnitsBackhauled = new SqlCommand("ReportRDDMUnitsBackhauledWithInterval", theConnection);
                cmdRDDMUnitsBackhauled.Parameters.AddWithValue("@dateStarted", startDate);
                cmdRDDMUnitsBackhauled.Parameters.AddWithValue("@dateEnded", endDate);
                cmdRDDMUnitsBackhauled.CommandType = System.Data.CommandType.StoredProcedure;
                cmdRDDMUnitsBackhauled.CommandTimeout = 1200;

                theReader = cmdRDDMUnitsBackhauled.ExecuteReader();

                if (theReader.HasRows)
                {
                    while (theReader.Read())
                    {
                        string thisRowsRD = theReader["RDOutlookName"].ToString();
                        string thisRowsDM = theReader["DMOutlookName"].ToString();

                        bool found = false;

                        for (int i = 0, l = rddmData.Count; i < l; i++)
                        {
                            RDDMSummary thisRowData = rddmData[i];

                            if (thisRowsRD.Equals(thisRowData.rdName) && thisRowsDM.Equals(thisRowData.dmName))
                            {
                                found = true;

                                int unitsBackhauled = (int)theReader["UnitsBackhauled"];

                                thisRowData.dairyBackhaulUnits = unitsBackhauled;
                            }
                        }

                        if (!found)
                        {
                            RDDMSummary thisRow = new RDDMSummary();

                            thisRow.rdName = theReader["RDOutlookName"].ToString();
                            thisRow.dmName = theReader["DMOutlookName"].ToString();
                            thisRow.dairyBackhaulUnits = (int)theReader["UnitsBackhauled"];

                            rddmData.Add(thisRow);

                            found = false;
                        }
                    }
                }

                theReader.Close();

                SqlCommand cmdRDDMUnitsBackhauledCost = new SqlCommand("ReportRDDMUnitsBackhauledCostWithInterval", theConnection);
                cmdRDDMUnitsBackhauledCost.Parameters.AddWithValue("@dateStarted", startDate);
                cmdRDDMUnitsBackhauledCost.Parameters.AddWithValue("@dateEnded", endDate);
                cmdRDDMUnitsBackhauledCost.CommandType = System.Data.CommandType.StoredProcedure;
                cmdRDDMUnitsBackhauledCost.CommandTimeout = 1200;

                theReader = cmdRDDMUnitsBackhauledCost.ExecuteReader();

                if (theReader.HasRows)
                {
                    while (theReader.Read())
                    {
                        string thisRowsRD = theReader["RDOutlookName"].ToString();
                        string thisRowsDM = theReader["DMOutlookName"].ToString();

                        bool found = false;

                        for (int i = 0, l = rddmData.Count; i < l; i++)
                        {
                            RDDMSummary thisRowData = rddmData[i];

                            if (thisRowsRD.Equals(thisRowData.rdName) && thisRowsDM.Equals(thisRowData.dmName))
                            {
                                found = true;

                                if (theReader["BackhaulCost"] != DBNull.Value)
                                {
                                    double backhaulCost = (double)theReader["BackhaulCost"];

                                    thisRowData.dairyBackhaulCOGS = backhaulCost;
                                }
                            }
                        }

                        if (!found)
                        {
                            RDDMSummary thisRow = new RDDMSummary();

                            thisRow.rdName = theReader["RDOutlookName"].ToString();
                            thisRow.dmName = theReader["DMOutlookName"].ToString();
                            thisRow.dairyBackhaulCOGS = (double)theReader["BackhaulCost"];

                            rddmData.Add(thisRow);

                            found = false;
                        }
                    }
                }

                theReader.Close();

                SqlCommand cmdRDDMNumberOfIssuesOne = new SqlCommand("ReportRDDMGroupOneIssuesWithInterval", theConnection);
                cmdRDDMNumberOfIssuesOne.Parameters.AddWithValue("@dateStarted", startDate);
                cmdRDDMNumberOfIssuesOne.Parameters.AddWithValue("@dateEnded", endDate);
                cmdRDDMNumberOfIssuesOne.CommandType = System.Data.CommandType.StoredProcedure;
                cmdRDDMNumberOfIssuesOne.CommandTimeout = 1200;

                theReader = cmdRDDMNumberOfIssues.ExecuteReader();

                if (theReader.HasRows)
                {
                    while (theReader.Read())
                    {
                        string thisRowsRD = theReader["RDOutlookName"].ToString();
                        string thisRowsDM = theReader["DMOutlookName"].ToString();

                        bool found = false;

                        for (int i = 0, l = rddmData.Count; i < l; i++)
                        {
                            RDDMSummary thisRowData = rddmData[i];

                            if (thisRowsRD.Equals(thisRowData.rdName) && thisRowsDM.Equals(thisRowData.dmName))
                            {
                                found = true;

                                int numberOfIssues = (int)theReader["NumberOfIssues"];

                                thisRowData.totalSecurityFacilityIssues = numberOfIssues;
                            }
                        }

                        if (!found)
                        {
                            RDDMSummary thisRow = new RDDMSummary();

                            thisRow.rdName = theReader["RDOutlookName"].ToString();
                            thisRow.dmName = theReader["DMOutlookName"].ToString();
                            thisRow.totalSecurityFacilityIssues = (int)theReader["NumberOfIssues"];

                            rddmData.Add(thisRow);

                            found = false;
                        }
                    }
                }

                theReader.Close();

                SqlCommand cmdRDDMNumberOfIssuesTwo = new SqlCommand("ReportRDDMGroupTwoIssuesWithInterval", theConnection);
                cmdRDDMNumberOfIssuesTwo.Parameters.AddWithValue("@dateStarted", startDate);
                cmdRDDMNumberOfIssuesTwo.Parameters.AddWithValue("@dateEnded", endDate);
                cmdRDDMNumberOfIssuesTwo.CommandType = System.Data.CommandType.StoredProcedure;
                cmdRDDMNumberOfIssuesTwo.CommandTimeout = 1200;

                theReader = cmdRDDMNumberOfIssuesTwo.ExecuteReader();

                if (theReader.HasRows)
                {
                    while (theReader.Read())
                    {
                        string thisRowsRD = theReader["RDOutlookName"].ToString();
                        string thisRowsDM = theReader["DMOutlookName"].ToString();

                        bool found = false;

                        for (int i = 0, l = rddmData.Count; i < l; i++)
                        {
                            RDDMSummary thisRowData = rddmData[i];

                            if (thisRowsRD.Equals(thisRowData.rdName) && thisRowsDM.Equals(thisRowData.dmName))
                            {
                                found = true;

                                int numberOfIssues = (int)theReader["NumberOfIssues"];

                                thisRowData.totalCapacityIssues = numberOfIssues;
                            }
                        }

                        if (!found)
                        {
                            RDDMSummary thisRow = new RDDMSummary();

                            thisRow.rdName = theReader["RDOutlookName"].ToString();
                            thisRow.dmName = theReader["DMOutlookName"].ToString();
                            thisRow.totalCapacityIssues = (int)theReader["NumberOfIssues"];

                            rddmData.Add(thisRow);

                            found = false;
                        }
                    }
                }

                theReader.Close();

                SqlCommand cmdRDDMNumberOfIssuesThree = new SqlCommand("ReportRDDMGroupThreeIssuesWithInterval", theConnection);
                cmdRDDMNumberOfIssuesThree.Parameters.AddWithValue("@dateStarted", startDate);
                cmdRDDMNumberOfIssuesThree.Parameters.AddWithValue("@dateEnded", endDate);
                cmdRDDMNumberOfIssuesThree.CommandType = System.Data.CommandType.StoredProcedure;
                cmdRDDMNumberOfIssuesThree.CommandTimeout = 1200;

                theReader = cmdRDDMNumberOfIssuesThree.ExecuteReader();

                if (theReader.HasRows)
                {
                    while (theReader.Read())
                    {
                        string thisRowsRD = theReader["RDOutlookName"].ToString();
                        string thisRowsDM = theReader["DMOutlookName"].ToString();

                        bool found = false;

                        for (int i = 0, l = rddmData.Count; i < l; i++)
                        {
                            RDDMSummary thisRowData = rddmData[i];

                            if (thisRowsRD.Equals(thisRowData.rdName) && thisRowsDM.Equals(thisRowData.dmName))
                            {
                                found = true;

                                int numberOfIssues = (int)theReader["NumberOfIssues"];

                                thisRowData.totalProductivityIssues = numberOfIssues;
                            }
                        }

                        if (!found)
                        {
                            RDDMSummary thisRow = new RDDMSummary();

                            thisRow.rdName = theReader["RDOutlookName"].ToString();
                            thisRow.dmName = theReader["DMOutlookName"].ToString();
                            thisRow.totalProductivityIssues = (int)theReader["NumberOfIssues"];

                            rddmData.Add(thisRow);

                            found = false;
                        }
                    }
                }

                theReader.Close();

                closeDataConnection();

                try
                {
                    string newFilePath = CopyReportTemplate("field");

                    openDataConnection();
                    SqlCommand cmdReport = new SqlCommand("ReportStoresNotReadyWithInterval", theConnection);
                    cmdReport.Parameters.AddWithValue("@dateStarted", startDate);
                    cmdReport.Parameters.AddWithValue("@dateEnded", endDate);
                    cmdReport.CommandType = System.Data.CommandType.StoredProcedure;
                    cmdReport.CommandTimeout = 1200;

                    theReader = cmdReport.ExecuteReader();

                    FileStream template = new FileStream(newFilePath, FileMode.Open, FileAccess.Read);
                    ExcelPackage pck = new OfficeOpenXml.ExcelPackage(template);
                    ExcelWorkbook workBook = pck.Workbook;

                    template.Close();

                    if (theReader.HasRows)
                    {
                        int rowIndex = 1;

                        ExcelWorksheet notReadySheet = workBook.Worksheets["DetailedViewNotReady"];

                        while (theReader.Read())
                        {
                            rowIndex++;

                            notReadySheet.Cells[rowIndex, 1].Value = theReader[0].ToString();
                            notReadySheet.Cells[rowIndex, 2].Value = theReader[1].ToString();
                            notReadySheet.Cells[rowIndex, 3].Value = theReader[2].ToString();
                            notReadySheet.Cells[rowIndex, 4].Value = theReader[3].ToString();
                            notReadySheet.Cells[rowIndex, 5].Value = theReader[4].ToString();
                            notReadySheet.Cells[rowIndex, 6].Value = theReader[5].ToString();
                            notReadySheet.Cells[rowIndex, 7].Value = theReader[6].ToString();
                            notReadySheet.Cells[rowIndex, 8].Value = theReader[7].ToString();
                            notReadySheet.Cells[rowIndex, 9].Value = theReader[8].ToString();
                            notReadySheet.Cells[rowIndex, 10].Value = theReader[9].ToString();
                            notReadySheet.Cells[rowIndex, 11].Value = theReader[10].ToString();
                            notReadySheet.Cells[rowIndex, 12].Value = theReader[11].ToString();
                            notReadySheet.Cells[rowIndex, 13].Value = Math.Round(Convert.ToDecimal(theReader[14].ToString()), 2).ToString();
                            notReadySheet.Cells[rowIndex, 14].Value = getCellFriendlyText(theReader[12].ToString());
                            if (String.IsNullOrEmpty(theReader[13].ToString()))
                            {
                                notReadySheet.Cells[rowIndex, 15].Value = "No Photos Available";
                            }
                            else
                            {
                                string[] photoIds = theReader[13].ToString().Split(',');
                                for (int j = 0; j < Int32.Parse(theReader[10].ToString()); j++)
                                    notReadySheet.Cells[rowIndex, j + 15].Value = baseWebURL + "/photos/" + photoIds[j] + ".jpg";
                            }
                            notReadySheet.Cells.AutoFitColumns();

                        }
                        theResponse.statusCode = 0;
                        theResponse.statusDescription = extractFilename(newFilePath);
                    }
                    else
                    {
                        theResponse.statusCode = 1;
                        theResponse.statusDescription = "There is no data logged between the dates that were selected";
                    }
                    theReader.Close();

                    SqlCommand cmdReportNotReady = new SqlCommand("ReportStoresReadyWithInterval", theConnection);
                    cmdReportNotReady.Parameters.AddWithValue("@dateStarted", startDate);
                    cmdReportNotReady.Parameters.AddWithValue("@dateEnded", endDate);
                    cmdReportNotReady.CommandType = System.Data.CommandType.StoredProcedure;
                    cmdReportNotReady.CommandTimeout = 1200;

                    theReader = cmdReportNotReady.ExecuteReader();
                    if (theReader.HasRows)
                    {
                        int rowIndex = 1;
                        ExcelWorksheet readySheet = workBook.Worksheets["DetailedViewReady"];

                        while (theReader.Read())
                        {
                            rowIndex++;

                            readySheet.Cells[rowIndex, 1].Value = theReader[0].ToString();
                            readySheet.Cells[rowIndex, 2].Value = theReader[1].ToString();
                            readySheet.Cells[rowIndex, 3].Value = theReader[2].ToString();
                            readySheet.Cells[rowIndex, 4].Value = theReader[3].ToString();
                            readySheet.Cells[rowIndex, 5].Value = theReader[4].ToString();

                        }
                        theResponse.statusCode = 0;
                        theResponse.statusDescription = extractFilename(newFilePath);
                    }
                    else
                    {
                        theResponse.statusCode = 1;
                        theResponse.statusDescription = "There is no data logged between the dates that were selected";
                    }
                    theReader.Close();

                    ExcelWorksheet sheetSummary = workBook.Worksheets["DVPRVPSummary"];

                    sheetSummary.Cells["A2:C2"].Merge = true;
                    sheetSummary.Cells[2, 1].Value = "All Data";

                    if (dvprvpData != null && dvprvpData.Count > 0)
                    {
                        int currentRowIndex = 3;

                        for (int i = 0, l = dvprvpData.Count; i < l; i++)
                        {
                            DVPRVPSummary thisRowsData = dvprvpData[i];

                            thisRowsData.percentageStoresReady = Math.Round((100 - ((double)thisRowsData.deliveriesWithIssues / (double)thisRowsData.deliveries) * 100.0), 2);
                            thisRowsData.leftoutCOGS = Math.Round(thisRowsData.leftoutCOGS, 2);
                            thisRowsData.dairyBackhaulCOGS = Math.Round(thisRowsData.dairyBackhaulCOGS, 2);

                            sheetSummary.Cells[currentRowIndex, 1].Value = thisRowsData.dvpName;
                            sheetSummary.Cells[currentRowIndex, 2].Value = thisRowsData.rvpName;
                            sheetSummary.Cells[currentRowIndex, 3].Value = thisRowsData.percentageStoresReady.ToString() + " %";
                            sheetSummary.Cells[currentRowIndex, 4].Value = "$ " + thisRowsData.dairyBackhaulCOGS.ToString();
                            sheetSummary.Cells[currentRowIndex, 5].Value = thisRowsData.deliveries;
                            sheetSummary.Cells[currentRowIndex, 6].Value = thisRowsData.deliveriesWithIssues;
                            sheetSummary.Cells[currentRowIndex, 7].Value = thisRowsData.totalReadinessIssues;

                            currentRowIndex++;
                        }

                        theResponse.statusCode = 0;
                        theResponse.statusDescription = extractFilename(newFilePath);
                    }
                    else
                    {
                        theResponse.statusCode = 1;
                        theResponse.statusDescription = "There is no data logged between the dates that were selected";
                    }

                    ExcelWorksheet sheetRDDMSummary = workBook.Worksheets["RDDMSummary"];

                    sheetRDDMSummary.Cells["A2:C2"].Merge = true;
                    sheetRDDMSummary.Cells[2, 1].Value = "All Data";

                    if (rddmData != null && rddmData.Count > 0)
                    {
                        int currentRowIndex = 3;

                        for (int i = 0, l = rddmData.Count; i < l; i++)
                        {
                            RDDMSummary thisRowsData = rddmData[i];

                            thisRowsData.percentageStoresReady = Math.Round((100 - ((double)thisRowsData.deliveriesWithIssues / (double)thisRowsData.deliveries) * 100.0), 2);

                            thisRowsData.dairyBackhaulCOGS = Math.Round(thisRowsData.dairyBackhaulCOGS, 2);

                            sheetRDDMSummary.Cells[currentRowIndex, 1].Value = thisRowsData.rdName;
                            sheetRDDMSummary.Cells[currentRowIndex, 2].Value = thisRowsData.dmName;
                            sheetRDDMSummary.Cells[currentRowIndex, 3].Value = thisRowsData.percentageStoresReady.ToString() + " %";
                            sheetRDDMSummary.Cells[currentRowIndex, 4].Value = "$ " + thisRowsData.dairyBackhaulCOGS.ToString();
                            sheetRDDMSummary.Cells[currentRowIndex, 5].Value = thisRowsData.deliveries;
                            sheetRDDMSummary.Cells[currentRowIndex, 6].Value = thisRowsData.deliveriesWithIssues;
                            sheetRDDMSummary.Cells[currentRowIndex, 7].Value = thisRowsData.totalReadinessIssues;
                            sheetRDDMSummary.Cells[currentRowIndex, 8].Value = thisRowsData.totalSecurityFacilityIssues;
                            sheetRDDMSummary.Cells[currentRowIndex, 9].Value = thisRowsData.totalCapacityIssues;
                            sheetRDDMSummary.Cells[currentRowIndex, 10].Value = thisRowsData.totalProductivityIssues;

                            currentRowIndex++;
                        }

                        theResponse.statusCode = 0;
                        theResponse.statusDescription = extractFilename(newFilePath);
                    }
                    else
                    {
                        theResponse.statusCode = 1;
                        theResponse.statusDescription = "There is no data logged between the dates that were selected";
                    }

                    FileStream fileSave = new FileStream(newFilePath, FileMode.Create);

                    pck.SaveAs(fileSave);
                    fileSave.Close();

                    closeDataConnection();
                }
                catch (Exception _exception)
                {
                    theResponse.statusCode = 6;
                    theResponse.statusDescription = _exception.Message + " " + _exception.StackTrace;
                }

                return theResponse;
            }
            else
            {
                //return ReportFieldReadiness();
                return null;
            }
        }
        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 ReportStoreReadinessForSSCWithInterval(string startDate, string endDate)
        {
            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);

                /* 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[9].ToString();
                        notReadySheet.Cells[rowIndex, 7].Value = theReader[10].ToString();
                        /*         notReadySheet.Cells[rowIndex, 8].Value = theReader[11].ToString();
                                 notReadySheet.Cells[rowIndex, 9].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, 10].Value = photoVal;
                */
                        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 (theReader[13].ToString() != null && !theReader[13].ToString().Equals(""))
                        {
                            photoVal += "" + baseURLForPhotoLink + theReader[13].ToString() + "";
                        }
                        else
                        {
                            photoVal += "No Photos Available";
                        }
                        notReadySheet.Cells[rowIndex, 11].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] + "'";

                         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["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.leftoutUnits;
                        //               sheetSummary.Cells[currentRowIndex, 5].Value = thisRowsData.dairyBackhaulUnits;
                        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);
                //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 ReportStoreReadinessForSSC();
            }
        }