public ResponseRouteList GetAllRouteMappingsWithRange(string startingIndex, string endingIndex)
{
ResponseRouteList theResponse = new ResponseRouteList();
openDataConnection();
SqlCommand cmdAllRouteMaps = new SqlCommand("GetAllRouteMappingsWithStoreDetails", theConnection);
theReader = cmdAllRouteMaps.ExecuteReader();
int numRecords = 0;
if (theReader.HasRows)
{
List<Route> allRoutes = new List<Route>();
string currentRouteName = "";
Route currentRoute = null;
while (theReader.Read())
{
if (theReader["RouteName"].ToString().Equals(""))
{
continue;
}
if (!currentRouteName.Equals(theReader["RouteName"].ToString()))
{
currentRoute = new Route();
currentRoute.routeName = theReader["RouteName"].ToString();
currentRoute.routeID = (int)theReader["RouteID"];
currentRoute.routeStatus = (int)theReader["Status"];
currentRoute.stores = new List<Store>();
currentRoute.cdc = new CDC(theReader["CDCName"].ToString(), (int)theReader["CDCID"]);
currentRoute.cdcName = theReader["CDCName"].ToString();
currentRouteName = currentRoute.routeName;
allRoutes.Add(currentRoute);
numRecords++;
}
Store thisStore = new Store();
thisStore.storeID = (int)theReader["StoreID"];
thisStore.storeName = theReader["StoreName"].ToString();
thisStore.storeAddress = theReader["StoreAddress"].ToString();
thisStore.storeCity = theReader["StoreCity"].ToString();
thisStore.storeZip = theReader["StoreZip"].ToString();
thisStore.storeState = theReader["StoreState"].ToString();
thisStore.storePhone = theReader["StorePhone"].ToString();
thisStore.storeManagerName = theReader["StoreManagerName"].ToString();
thisStore.storeEmailAddress = theReader["StoreEmail"].ToString();
thisStore.storeNumber = theReader["StoreNumber"].ToString();
thisStore.storeOwnershipType = theReader["StoreOwnershipType"].ToString();
currentRoute.stores.Add(thisStore);
}
theResponse.numberOfRecords = numRecords;
List<Route> finalRoutes = new List<Route>();
int startIndex = Int32.Parse(startingIndex);
int endIndex = Int32.Parse(endingIndex);
endIndex = startIndex + endIndex;
if (startIndex <= 0)
{
startIndex = 1;
}
if (startIndex > 0 && endIndex >= startIndex)
{
if (endIndex > numRecords)
{
endIndex = numRecords;
}
for (int i = startIndex; i <= endIndex; i++)
{
finalRoutes.Add(allRoutes[i - 1]);
}
theResponse.routes = finalRoutes;
theResponse.statusCode = 0;
theResponse.statusDescription = "";
}
else
{
theResponse.statusCode = 6;
theResponse.statusDescription = "The starting or ending index did not fall within the data range";
}
}
else
{
theResponse.statusCode = 4;
theResponse.statusDescription = "There are no route-store mappings defined";
}
theReader.Close();
closeDataConnection();
return theResponse;
}
//public ResponseRouteList LinqGetAllRouteMappings(string providerId, int startIndex, int maxRows)
//{
// ResponseRouteList theResponse = new ResponseRouteList();
// DataClassesDataContext dc = new DataClassesDataContext();
// // Querying and creating a Route object using LINQ
// IQueryable<Route> RouteQuery;
// if (string.IsNullOrEmpty(providerId))
// {
// RouteQuery = (from route in dc.Routes
// join cdc in dc.CDCs on route.CDCID equals cdc.CDCID
// select new Route
// {
// routeID = route.RouteID,
// routeName = route.RouteName,
// routeStatus = (int)route.status,
// cdcName = cdc.CDCName
// }).Skip(startIndex).Take(maxRows);
// }
// else
// {
// RouteQuery = (from route in dc.Routes
// join cdc in dc.CDCs on route.CDCID equals cdc.CDCID
// where cdc.ProviderID == Convert.ToInt32(providerId)
// select new Route
// {
// routeID = route.RouteID,
// routeName = route.RouteName,
// routeStatus = (int)route.status,
// cdcName = cdc.CDCName
// }).Skip(startIndex).Take(maxRows);
// }
// IList<Route> allRoutes = new List<Route>();
// foreach (Route route in RouteQuery)
// {
// Route currentRoute = new Route
// {
// routeName = route.routeName,
// routeID = route.routeID,
// routeStatus = route.routeStatus,
// cdcName = route.cdcName
// };
// currentRoute.stores = new List<Store>();
// allRoutes.Add(currentRoute);
// // Querying and creating a Store object using LINQ
// IQueryable<Store> StoreQuery = from routeStore in dc.RouteStoreMaps
// join store in dc.Stores on routeStore.StoreID equals store.StoreID
// where (routeStore.RouteID == currentRoute.routeID) && (routeStore.State == true)
// select new Store
// {
// storeID = store.StoreID,
// storeName = store.StoreName,
// storeNumber = store.StoreNumber
// };
// foreach (Store store in StoreQuery)
// {
// Store thisStore = new Store
// {
// storeID = store.storeID,
// storeName = store.storeName,
// storeNumber = store.storeNumber
// };
// currentRoute.stores.Add(thisStore);
// }
// }
// theResponse.routes = (List<Route>)allRoutes;
// theResponse.statusCode = 0;
// theResponse.statusDescription = "";
// theResponse.numberOfRecords = (from route in dc.Routes
// join cdc in dc.CDCs on route.CDCID equals cdc.CDCID
// select route).Count();
// return theResponse;
//}
//public ResponseRouteList LinqGetAllFilteredRouteMappings(string filterText, int startIndex, int maxRows)
//{
// ResponseRouteList theResponse = new ResponseRouteList();
// DataClassesDataContext dc = new DataClassesDataContext();
// int rowCnt = 0;
// IList<Route> allRoutes = new List<Route>();
// IQueryable<Route> RouteQuery =
// (from route in dc.Routes
// join cdc in dc.CDCs on route.CDCID equals cdc.CDCID
// select new Route
// {
// routeID = route.RouteID,
// routeName = route.RouteName,
// routeStatus = (int)route.status,
// cdcName = cdc.CDCName
// }).Skip(startIndex);
// foreach (Route r in RouteQuery)
// {
// //Check if the filter text exist in route name or cdc name
// if (r.routeName.ToLower().Contains(filterText) || r.cdcName.ToLower().Contains(filterText))
// {
// rowCnt++;
// r.stores = new List<Store>();
// allRoutes.Add(r);
// // Querying and creating a Store object using LINQ
// IQueryable<Store> StoreQuery = from routeStore in dc.RouteStoreMaps
// join store in dc.Stores on routeStore.StoreID equals store.StoreID
// where (routeStore.RouteID == r.routeID) && (routeStore.State == true)
// select new Store
// {
// storeID = store.StoreID,
// storeName = store.StoreName,
// storeNumber = store.StoreNumber
// };
// foreach (Store store in StoreQuery)
// {
// Store thisStore = new Store
// {
// storeID = store.storeID,
// storeName = store.storeName,
// storeNumber = store.storeNumber
// };
// r.stores.Add(thisStore);
// }
// }
// else
// {
// //Check if the filter text exist in store number
// IQueryable<Store> StoreFilterQuery = from routeStore in dc.RouteStoreMaps
// join store in dc.Stores on routeStore.StoreID equals store.StoreID
// where (routeStore.RouteID == r.routeID) && (routeStore.State == true) && (store.StoreNumber.ToLower().Contains(filterText))
// select new Store
// {
// storeID = store.StoreID,
// storeName = store.StoreName,
// storeNumber = store.StoreNumber
// };
// if (StoreFilterQuery.Count() > 0) // if filter text exist in store number
// {
// rowCnt++;
// r.stores = new List<Store>();
// allRoutes.Add(r);
// IQueryable<Store> StoreQuery = from routeStore in dc.RouteStoreMaps
// join store in dc.Stores on routeStore.StoreID equals store.StoreID
// where (routeStore.RouteID == r.routeID) && (routeStore.State == true)
// select new Store
// {
// storeID = store.StoreID,
// storeName = store.StoreName,
// storeNumber = store.StoreNumber
// };
// foreach (Store store in StoreQuery)
// {
// Store thisStore = new Store
// {
// storeID = store.storeID,
// storeName = store.storeName,
// storeNumber = store.storeNumber
// };
// r.stores.Add(thisStore);
// }
// }
// }
// if (rowCnt == maxRows)
// break;
// }
// theResponse.routes = (List<Route>)allRoutes;
// return theResponse;
//}
public ResponseRouteList GetRouteDetail(string routeName)
{
ResponseRouteList theResponse = new ResponseRouteList();
openDataConnection();
SqlCommand cmdRouteMap = new SqlCommand("GetRouteMappingWithStoreDetail", theConnection);
cmdRouteMap.Parameters.AddWithValue("@routeName", routeName);
cmdRouteMap.CommandType = System.Data.CommandType.StoredProcedure;
theReader = cmdRouteMap.ExecuteReader();
if (theReader.HasRows)
{
bool firstRead = true;
List<Route> allRoutes = new List<Route>();
Route thisRoute = new Route();
thisRoute.routeName = routeName;
thisRoute.stores = new List<Store>();
allRoutes.Add(thisRoute);
while (theReader.Read())
{
if (firstRead)
{
thisRoute.routeID = (int)theReader["RouteID"];
thisRoute.cdc = new CDC(theReader["CDCName"].ToString(), (int)theReader["CDCID"]);
thisRoute.cdcName = theReader["CDCName"].ToString();
firstRead = false;
}
Store thisStore = new Store();
thisStore.storeID = (int)theReader["StoreID"];
thisStore.storeName = theReader["StoreName"].ToString();
thisStore.storeAddress = theReader["StoreAddress"].ToString();
thisStore.storeCity = theReader["StoreCity"].ToString();
thisStore.storeZip = theReader["StoreZip"].ToString();
thisStore.storeState = theReader["StoreState"].ToString();
thisStore.storePhone = theReader["StorePhone"].ToString();
thisStore.storeManagerName = theReader["StoreManagerName"].ToString();
thisStore.storeEmailAddress = theReader["StoreEmail"].ToString();
thisStore.storeNumber = theReader["StoreNumber"].ToString();
thisStore.storeOwnershipType = theReader["StoreOwnershipType"].ToString();
thisRoute.stores.Add(thisStore);
}
theResponse.routes = allRoutes;
theResponse.statusCode = 0;
theResponse.statusDescription = "";
}
else
{
theResponse.statusCode = 4;
theResponse.statusDescription = "There are no route-store mappings defined for " + routeName;
}
theReader.Close();
closeDataConnection();
return theResponse;
}
public ResponseRouteList GetAllRouteMappingsForProvider(string providerID)
{
ResponseRouteList theResponse = new ResponseRouteList();
openDataConnection();
SqlCommand cmdAllRouteMaps = new SqlCommand("GetAllRouteMappingsWithStoreDetailsForCDC", theConnection);
cmdAllRouteMaps.Parameters.AddWithValue(@"providerID", providerID);
cmdAllRouteMaps.CommandType = System.Data.CommandType.StoredProcedure;
theReader = cmdAllRouteMaps.ExecuteReader();
if (theReader.HasRows)
{
List<Route> allRoutes = new List<Route>();
string currentRouteName = "";
Route currentRoute = null;
while (theReader.Read())
{
if (theReader["RouteName"].ToString().Equals("") || theReader["status"].Equals("0"))
{
continue;
}
if (!currentRouteName.Equals(theReader["RouteName"].ToString()))
{
currentRoute = new Route();
currentRoute.routeName = theReader["RouteName"].ToString();
currentRoute.routeID = (int)theReader["RouteID"];
currentRoute.routeStatus = (int)theReader["Status"];
currentRoute.stores = new List<Store>();
currentRoute.cdc = new CDC(theReader["CDCName"].ToString(), (int)theReader["CDCID"]);
currentRoute.cdcName = theReader["CDCName"].ToString();
currentRouteName = currentRoute.routeName;
allRoutes.Add(currentRoute);
}
Store thisStore = new Store();
thisStore.storeID = (int)theReader["StoreID"];
thisStore.storeName = theReader["StoreName"].ToString();
thisStore.storeAddress = theReader["StoreAddress"].ToString();
thisStore.storeCity = theReader["StoreCity"].ToString();
thisStore.storeZip = theReader["StoreZip"].ToString();
thisStore.storeState = theReader["StoreState"].ToString();
thisStore.storePhone = theReader["StorePhone"].ToString();
thisStore.storeManagerName = theReader["StoreManagerName"].ToString();
thisStore.storeEmailAddress = theReader["StoreEmail"].ToString();
thisStore.storeNumber = theReader["StoreNumber"].ToString();
thisStore.storeOwnershipType = theReader["StoreOwnershipType"].ToString();
currentRoute.stores.Add(thisStore);
}
theResponse.routes = allRoutes;
theResponse.statusCode = 0;
theResponse.statusDescription = "";
theReader.Close();
}
else
{
theReader.Close();
theResponse.statusCode = 4;
theResponse.statusDescription = "There are no route-store mappings defined for Provider " + providerID + " (" + getProviderNameFromID(Int32.Parse(providerID)) + ")";
}
closeDataConnection();
return theResponse;
}
public ResponseRouteList GetAllRouteMappings()
{
ResponseRouteList theResponse = new ResponseRouteList();
openDataConnection();
SqlCommand cmdAllRouteMaps = new SqlCommand("GetAllRouteMappingsWithStoreDetails", theConnection);
theReader = cmdAllRouteMaps.ExecuteReader();
if (theReader.HasRows)
{
List<Route> allRoutes = new List<Route>();
string currentRouteName = "";
Route currentRoute = null;
while (theReader.Read())
{
if (theReader["RouteName"].ToString().Equals(""))
{
continue;
}
if (!currentRouteName.Equals(theReader["RouteName"].ToString()))
{
currentRoute = new Route();
currentRoute.routeName = theReader["RouteName"].ToString();
currentRoute.routeID = (int)theReader["RouteID"];
currentRoute.routeStatus = (int)theReader["Status"];
currentRoute.stores = new List<Store>();
currentRoute.cdc = new CDC(theReader["CDCName"].ToString(), (int)theReader["CDCID"]);
currentRoute.cdcName = theReader["CDCName"].ToString();
currentRouteName = currentRoute.routeName;
allRoutes.Add(currentRoute);
}
Store thisStore = new Store();
thisStore.storeID = (int)theReader["StoreID"];
thisStore.storeName = theReader["StoreName"].ToString();
thisStore.storeAddress = theReader["StoreAddress"].ToString();
thisStore.storeCity = theReader["StoreCity"].ToString();
thisStore.storeZip = theReader["StoreZip"].ToString();
thisStore.storeState = theReader["StoreState"].ToString();
thisStore.storePhone = theReader["StorePhone"].ToString();
thisStore.storeManagerName = theReader["StoreManagerName"].ToString();
thisStore.storeEmailAddress = theReader["StoreEmail"].ToString();
thisStore.storeNumber = theReader["StoreNumber"].ToString();
thisStore.storeOwnershipType = theReader["StoreOwnershipType"].ToString();
currentRoute.stores.Add(thisStore);
}
theResponse.routes = allRoutes;
theResponse.statusCode = 0;
theResponse.statusDescription = "";
}
else
{
theResponse.statusCode = 4;
theResponse.statusDescription = "There are no route-store mappings defined";
}
theReader.Close();
closeDataConnection();
return theResponse;
}
public ResponseRouteList DotNetGetAllRouteMappings(int startIndex, int maxRows, string providerId)
{
ResponseRouteList theResponse = new ResponseRouteList();
openDataConnection();
SqlCommand cmdAllRouteMaps = new SqlCommand("DotNetGetAllRouteMappingsWithStoreDetails", theConnection);
cmdAllRouteMaps.Parameters.AddWithValue(@"StartRowIndex", startIndex);
cmdAllRouteMaps.Parameters.AddWithValue(@"MaximumRows", maxRows);
cmdAllRouteMaps.Parameters.AddWithValue(@"ProviderId", providerId);
cmdAllRouteMaps.CommandType = System.Data.CommandType.StoredProcedure;
theReader = cmdAllRouteMaps.ExecuteReader();
if (theReader.HasRows)
{
List<Route> allRoutes = new List<Route>();
string currentRouteName = "";
Route currentRoute = null;
while (theReader.Read())
{
if (theReader["RouteName"].ToString().Equals(""))
{
continue;
}
if (!currentRouteName.Equals(theReader["RouteName"].ToString()))
{
currentRoute = new Route();
currentRoute.routeName = theReader["RouteName"].ToString();
currentRoute.routeID = (int)theReader["RouteID"];
currentRoute.routeStatus = (int)theReader["Status"];
currentRoute.stores = new List<Store>();
currentRoute.cdcName = theReader["CDCName"].ToString();
currentRouteName = currentRoute.routeName;
allRoutes.Add(currentRoute);
}
Store thisStore = new Store();
thisStore.storeName = theReader["StoreName"].ToString();
thisStore.storeNumber = theReader["StoreNumber"].ToString();
currentRoute.stores.Add(thisStore);
}
theResponse.routes = allRoutes;
theResponse.statusCode = 0;
theResponse.statusDescription = "";
}
else
{
theResponse.statusCode = 4;
theResponse.statusDescription = "There are no route-store mappings defined";
}
theReader.Close();
SqlCommand cmdCheckRecordCount;
if (string.IsNullOrEmpty(providerId))
cmdCheckRecordCount = new SqlCommand("SELECT COUNT(Distinct Route.RouteID) FROM Route JOIN RouteStoreMap ON " +
"Route.RouteID = RouteStoreMap.RouteID JOIN CDC ON Route.CDCID = CDC.CDCID And RouteStoreMap.State=1 And RouteName <> '' ", theConnection);
else
cmdCheckRecordCount = new SqlCommand("SELECT COUNT(Distinct Route.RouteID) FROM Route JOIN RouteStoreMap ON " +
"Route.RouteID = RouteStoreMap.RouteID JOIN CDC ON Route.CDCID = CDC.CDCID And CDC.CDCID = " + providerId + " And RouteStoreMap.State=1 And RouteName <> '' ", theConnection);
int RecordCount = (int)cmdCheckRecordCount.ExecuteScalar();
closeDataConnection();
theResponse.numberOfRecords = RecordCount;
return theResponse;
}
public Response CreateRoute(Route routeModel)
{
Response theResponse = new Response();
if (routeModel != null)
{
if (routeModel.routeName == null)
{
theResponse.statusDescription = "Route Name not supplied";
}
if (routeModel.cdc == null || routeModel.cdc.id == 0)
{
theResponse.statusDescription = "CDC information not supplied";
}
if (theResponse.statusDescription.Equals(""))
{
openDataConnection();
SqlCommand cmdCheck = new SqlCommand("RouteExists", theConnection);
cmdCheck.Parameters.AddWithValue("@routeName", routeModel.routeName);
cmdCheck.CommandType = System.Data.CommandType.StoredProcedure;
theReader = cmdCheck.ExecuteReader();
if (theReader.HasRows)
{
theReader.Close();
theResponse.statusCode = 3;
theResponse.statusDescription = "The route name " + routeModel.routeName + " already exists";
}
else
{
theReader.Close();
SqlCommand cmdCreate = new SqlCommand("CreateRoute", theConnection);
cmdCreate.Parameters.AddWithValue("@routeName", routeModel.routeName);
cmdCreate.Parameters.AddWithValue("@cdcID", routeModel.cdc.id);
cmdCreate.CommandType = System.Data.CommandType.StoredProcedure;
try
{
int numRowsAffected = cmdCreate.ExecuteNonQuery();
if (numRowsAffected > 0)
{
if (routeModel.stores != null)
{
int totalStoresGiven = routeModel.stores.Count;
int totalStoresAdded = 0;
for (int i = 0; i < totalStoresGiven; i++)
{
int thisStoreID = routeModel.stores[i].storeID;
SqlCommand cmdAddStoreToRoute = new SqlCommand("AddStoreToRoute", theConnection);
cmdAddStoreToRoute.Parameters.AddWithValue("@routeName", routeModel.routeName);
cmdAddStoreToRoute.Parameters.AddWithValue("@storeID", thisStoreID);
cmdAddStoreToRoute.CommandType = System.Data.CommandType.StoredProcedure;
int numRowsAffectedForAddStoreToRoute = cmdAddStoreToRoute.ExecuteNonQuery();
if (numRowsAffectedForAddStoreToRoute > 0)
{
totalStoresAdded++;
}
}
if (totalStoresAdded == totalStoresGiven)
{
theResponse.statusCode = 0;
theResponse.statusDescription = "";
}
else
{
theResponse.statusCode = 0;
theResponse.statusDescription = "Only " + totalStoresAdded + " out of " + totalStoresGiven + " were added to the route " + routeModel.routeName;
}
}
else
{
theResponse.statusCode = 0;
theResponse.statusDescription = "";
}
}
else
{
theResponse.statusCode = 6;
}
}
catch (Exception _exception)
{
theResponse.statusCode = 6;
theResponse.statusDescription = _exception.Message;
}
}
closeDataConnection();
}
else
{
theResponse.statusCode = 6;
}
}
else
{
theResponse.statusCode = 6;
theResponse.statusDescription = "Route Model was expected but not supplied";
}
return theResponse;
}
public Response UploadRoutesDotNet(string fileName, string username)
{
Response theResponse = new Response();
string cdcName = "";
string currentPath = HttpContext.Current.Server.MapPath("~");
long currentTime = DateTime.Now.ToFileTimeUtc();
//string fileName = "routes_" + currentTime;
string finalPath = currentPath + "\\uploads\\" + fileName;
//FileStream fileToUpload = new FileStream(finalPath, FileMode.Create);
//MultipartParser parser = new MultipartParser(fileStream);
//if (parser.Success)
//{
// fileToUpload.Write(parser.FileContents, 0, parser.FileContents.Length);
// fileToUpload.Close();
// fileToUpload.Dispose();
//}
//else
//{
// theResponse.statusCode = 6;
// theResponse.statusDescription = "Unable to parse input data";
// return theResponse;
//}
int recordsFound = 0;
int recordsAdded = 0;
int recordsUpdated = 0;
List<string> feedback = new List<string>();
//string connectionString = connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + finalPath + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + finalPath + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1;ReadOnly=False\"";
try
{
OleDbConnection con = new OleDbConnection(connectionString);
OleDbCommand cmd = new OleDbCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.Connection = con;
OleDbDataAdapter dAdapter = new OleDbDataAdapter(cmd);
DataTable dtExcelRecords = new DataTable();
con.Open();
DataTable dtExcelSheetName = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string getExcelSheetName = dtExcelSheetName.Rows[0]["Table_Name"].ToString();
cmd.CommandText = "SELECT * FROM [" + getExcelSheetName + "]";
OleDbDataReader oleReader;
oleReader = cmd.ExecuteReader();
if (oleReader.HasRows)
{
List<Route> routes = new List<Route>();
int rowCounter = 0;
while (oleReader.Read())
{
rowCounter += 1;
if (oleReader[0].ToString().Equals("Transaction Type") || oleReader[1].ToString().Equals("CDC"))
{
continue;
}
if (oleReader[0].ToString().Equals(""))
{
break;
}
if (cdcName == "")
cdcName = oleReader[1].ToString();
if (oleReader[0].ToString().ToUpper().Trim().Equals("ADD"))
{
recordsFound++;
if (!doesCDCExist(oleReader[1].ToString()))
{
feedback.Add("CDC " + oleReader[1].ToString() + " does not exist");
continue;
}
if (doesRouteExist(oleReader[2].ToString()))
{
feedback.Add("The route " + oleReader[2].ToString() + " already exists");
continue;
}
Route thisRoute = new Route();
thisRoute.cdc = new CDC();
thisRoute.cdc.name = oleReader[1].ToString();
thisRoute.cdc.id = getCDCIDForCDCName(oleReader[1].ToString());
thisRoute.routeName = oleReader[2].ToString();
int numberOfStopsForThisRoute = oleReader.FieldCount;
List<Store> stops = new List<Store>();
for (int i = 3; i < numberOfStopsForThisRoute; i++)
{
Store thisStore = new Store();
string thisStoreNumber = oleReader[i].ToString();
thisStore.storeID = getStoreIDForStoreNumber(thisStoreNumber);
if (thisStore.storeID > 0)
{
stops.Add(thisStore);
}
else
{
if (!thisStoreNumber.Equals("0") && !thisStoreNumber.Equals(""))
{
feedback.Add("The Store Number " + thisStoreNumber + " was not found in the database");
}
}
}
if (stops.Count > 0)
{
thisRoute.stores = stops;
feedback.Add("Route " + thisRoute.routeName + " has " + thisRoute.stores.Count + " stops.");
routes.Add(thisRoute);
}
else
{
feedback.Add("The Route " + thisRoute.routeName + " could not be added as none of the stores listed against this route are present in the database");
}
}
else if (oleReader[0].ToString().ToUpper().Trim().Equals("UPDATE"))
{
recordsFound++;
if (!doesRouteExist(oleReader[2].ToString()))
{
feedback.Add("The route " + oleReader[2].ToString() + " does not exist thus cannot be updated");
continue;
}
else
{
string thisRouteName = oleReader[2].ToString();
ResponseRouteList thisRouteDetail = GetRouteDetail(thisRouteName);
bool validRoute = false;
int numberOfStops = 0;
if (thisRouteDetail != null)
{
validRoute = true;
if (thisRouteDetail.routes != null)
{
numberOfStops = thisRouteDetail.routes[0].stores.Count;
}
}
if (validRoute)
{
openDataConnection();
SqlCommand cmdDisableMappingsForRoute = new SqlCommand("DisableCurrentMappingsForRouteName", theConnection);
cmdDisableMappingsForRoute.Parameters.AddWithValue("@routeName", thisRouteName);
cmdDisableMappingsForRoute.CommandType = System.Data.CommandType.StoredProcedure;
int numMappingsDisabled = cmdDisableMappingsForRoute.ExecuteNonQuery();
closeDataConnection();
if (numMappingsDisabled >= numberOfStops)
{
List<Store> newStops = new List<Store>();
int numberOfStopsForThisRoute = oleReader.FieldCount;
for (int i = 3; i < numberOfStopsForThisRoute; i++)
{
Store thisStore = new Store();
string thisStoreNumber = oleReader[i].ToString();
if (thisStoreNumber != null && !thisStoreNumber.Equals(""))
{
thisStore.storeNumber = thisStoreNumber;
thisStore.storeID = getStoreIDForStoreNumber(thisStoreNumber);
if (thisStore.storeID > 0)
{
newStops.Add(thisStore);
}
else
{
feedback.Add("The Store Number " + thisStoreNumber + " was not found in the database");
}
}
}
openDataConnection();
foreach (Store aStore in newStops)
{
SqlCommand cmdAddStoreToRoute = new SqlCommand("AddStoreToRoute", theConnection);
cmdAddStoreToRoute.Parameters.AddWithValue("@routeName", thisRouteName);
cmdAddStoreToRoute.Parameters.AddWithValue("@storeID", aStore.storeID);
cmdAddStoreToRoute.CommandType = System.Data.CommandType.StoredProcedure;
int numRowsAffectedForAddStoreToRoute = cmdAddStoreToRoute.ExecuteNonQuery();
feedback.Add("Added Store " + aStore.storeNumber + " to Route " + thisRouteName);
}
feedback.Add("Updated Route " + thisRouteName);
closeDataConnection();
recordsUpdated++;
}
else
{
feedback.Add("The route " + oleReader[2].ToString() + " had " + numberOfStops + " mappings but only " + numMappingsDisabled + " were disabled and this route cannot be updated");
continue;
}
}
else
{
feedback.Add("The route " + oleReader[2].ToString() + " does not seem to be valid and thus cannot be updated");
continue;
}
}
}
}
for (int i = 0, l = routes.Count; i < l; i++)
{
Response createResponse = CreateRoute(routes[i]);
if (createResponse.statusCode == 0)
{
recordsAdded++;
}
}
}
oleReader.Close();
}
catch (Exception _exception)
{
theResponse.statusCode = 6;
theResponse.statusDescription = _exception.Message + " Line Number: " + _exception.StackTrace;
return theResponse;
}
if (recordsFound > 0)
{
theResponse.statusCode = 0;
theResponse.statusDescription = "Found " + recordsFound + " route records in the file.<br />Added " + recordsAdded + " records to the database.<br />Updated " + recordsUpdated + " records in the database.";
}
else
{
theResponse.statusCode = 2;
theResponse.statusDescription = "No records found in the excel file";
}
if (feedback.Count > 0)
{
string emailString = "<ul>";
theResponse.statusDescription += "<br /><br /><p>Feedback:</p><ul>";
for (int i = 0, l = feedback.Count; i < l; i++)
{
theResponse.statusDescription += "<li>" + feedback[i] + "</li>";
emailString += "<li>" + feedback[i] + "</li>";
}
theResponse.statusDescription += "</ul>";
emailString += "</ul>";
SendEmailForUploadErrors("Routes", username, emailString, cdcName);
}
return theResponse;
}
