public ResponseStoreList GetStoresForCDC(string aCDCID)
{
ResponseStoreList theResponse = new ResponseStoreList();
if (aCDCID == null || aCDCID.Equals(""))
{
theResponse.statusCode = 1;
theResponse.statusDescription = "CDC ID is missing";
}
else
{
openDataConnection();
SqlCommand cmdGet = new SqlCommand("GetStoresForCDC", theConnection);
cmdGet.Parameters.AddWithValue("@cdcID", aCDCID);
cmdGet.CommandType = System.Data.CommandType.StoredProcedure;
try
{
theReader = cmdGet.ExecuteReader();
if (theReader.HasRows)
{
theResponse.stores = new List<Store>();
while (theReader.Read())
{
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();
theResponse.stores.Add(thisStore);
}
theResponse.statusCode = 0;
theResponse.statusDescription = "";
}
else
{
theResponse.statusCode = 4;
theResponse.statusDescription = "There are no stores for CDC ID " + aCDCID;
}
theReader.Close();
}
catch (Exception _exception)
{
theResponse.statusCode = 6;
theResponse.statusDescription = _exception.Message;
}
closeDataConnection();
}
return theResponse;
}
public ResponseStoreList GetStoreDetail(string aStoreID)
{
ResponseStoreList theResponse = new ResponseStoreList();
if (aStoreID.Equals(""))
{
theResponse.statusCode = 1;
theResponse.statusDescription = "Store ID not provided";
return theResponse;
}
openDataConnection();
SqlCommand cmdUserDetail = new SqlCommand("SELECT * FROM Store WHERE StoreID = " + aStoreID + " OR StoreName = '" + aStoreID + "'", theConnection);
theReader = cmdUserDetail.ExecuteReader();
if (theReader.HasRows)
{
theResponse.stores = new List<Store>();
while (theReader.Read())
{
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();
theResponse.stores.Add(thisStore);
}
theResponse.statusCode = 0;
}
else
{
theResponse.statusCode = 4;
theResponse.statusDescription = "The store " + aStoreID + " could not be found";
}
theReader.Close();
closeDataConnection();
return theResponse;
}
public Store GetStoreForStop(int stopID)
{
Store thisStore = null;
openDataConnection();
SqlCommand getStore = new SqlCommand("GetStoreForStop", theConnection);
getStore.Parameters.AddWithValue("@stopID", stopID);
getStore.CommandType = System.Data.CommandType.StoredProcedure;
try
{
theReader = getStore.ExecuteReader();
if (theReader.HasRows)
{
while (theReader.Read())
{
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();
}
}
}
catch (Exception _exception)
{
}
closeDataConnection();
return thisStore;
}
public ResponseTrip GetOpenTripForRouteNameAndUserV7(string aRouteName, string aUsername, string aGmtOffset)
{
ResponseTrip theResponse = new ResponseTrip();
TimeSpan tSpan = TimeSpan.FromMinutes(Convert.ToDouble(aGmtOffset));
aGmtOffset = tSpan.TotalHours.ToString();
if (aRouteName != null && !aRouteName.Equals("") && aUsername != null && !aUsername.Equals(""))
{
openDataConnection();
SqlCommand cmdGetTrip = new SqlCommand("SELECT * FROM Trip WHERE RouteName = '" + aRouteName + "' AND Username = '******' AND Closed = 0", theConnection);
try
{
theReader = cmdGetTrip.ExecuteReader();
}
catch (Exception _exception)
{
theResponse.statusCode = 6;
theResponse.statusDescription = _exception.Message;
}
if (!theReader.HasRows)
{
theResponse.statusCode = 4;
theResponse.statusDescription = "No Open Trip for " + aRouteName + " exists for " + aUsername;
theReader.Close();
Response setupResponse = SetupTripV7(aRouteName, aUsername, aGmtOffset);
if (setupResponse.statusCode == 0)
{
theResponse = null;
return GetOpenTripForRouteNameAndUserV7(aRouteName, aUsername, aGmtOffset);
}
}
else
{
int tripID = 0;
theReader.Read();
tripID = (int)theReader["TripID"];
if (tripID > 0)
{
TripWithStops thisTrip = new TripWithStops();
thisTrip.id = tripID;
thisTrip.routeName = aRouteName;
thisTrip.username = aUsername;
thisTrip.closed = false;
if (theReader["GMTOffset"] != DBNull.Value)
{
thisTrip.GMTOffset = Convert.ToSingle(theReader["GMTOffset"]);
}
if (theReader["DateStarted"] != DBNull.Value)
{
thisTrip.dateStarted = (DateTime)theReader["DateStarted"];
DateTime epoch = new DateTime(1970, 1, 1, 0, 0, 0, 0);
TimeSpan span = (thisTrip.dateStarted - epoch);
double unixTime = span.TotalSeconds;
thisTrip.dateStartedEpoch = (int)unixTime;
}
if (theReader["DateClosed"] != DBNull.Value)
{
thisTrip.dateClosed = (DateTime)theReader["DateClosed"];
DateTime epoch = new DateTime(1970, 1, 1, 0, 0, 0, 0);
TimeSpan span = (thisTrip.dateClosed - epoch);
double unixTime = span.TotalSeconds;
thisTrip.dateClosedEpoch = (int)unixTime;
}
theReader.Close();
int routeID = 0;
SqlCommand cmdGetRouteId = new SqlCommand("SELECT RouteID FROM Route WHERE RouteName = '" + aRouteName + "'", theConnection);
theReader = cmdGetRouteId.ExecuteReader();
if (theReader.HasRows)
{
theReader.Read();
routeID = (int)theReader["RouteID"];
theReader.Close();
if (routeID > 0)
{
SqlCommand cmdRouteStoreMappings = new SqlCommand("SELECT * FROM Stop WHERE TripID = " + tripID.ToString(), theConnection);
theReader = cmdRouteStoreMappings.ExecuteReader();
if (theReader.HasRows)
{
List<StopWithStore> stops = new List<StopWithStore>();
while (theReader.Read())
{
StopWithStore thisStop = new StopWithStore();
thisStop.id = (int)theReader["StopID"];
thisStop.committed = true;
thisStop.tripID = (int)theReader["TripID"];
thisStop.mappingID = (int)theReader["MappingID"];
thisStop.completed = (bool)theReader["Completed"];
// thisStop.comment = theReader["Comment"].ToString();
if (theReader["DateAdded"] != DBNull.Value)
{
thisStop.dateAdded = (DateTime)theReader["DateAdded"];
DateTime epoch = new DateTime(1970, 1, 1, 0, 0, 0, 0);
TimeSpan span = (thisStop.dateAdded - epoch);
double unixTime = span.TotalSeconds;
thisStop.dateAddedEpoch = (int)unixTime;
}
if (theReader["DateUpdated"] != DBNull.Value)
{
thisStop.dateUpdated = (DateTime)theReader["DateUpdated"];
DateTime epoch = new DateTime(1970, 1, 1, 0, 0, 0, 0);
TimeSpan span = (thisStop.dateUpdated - epoch);
double unixTime = span.TotalSeconds;
thisStop.dateUpdatedEpoch = (int)unixTime;
}
stops.Add(thisStop);
}
theReader.Close();
for (int i = 0, l = stops.Count; i < l; i++)
{
StopWithStore thisStop = stops[i];
SqlCommand cmdGetStoreFromMappingID = new SqlCommand("GetStoreFromMappingID", theConnection);
cmdGetStoreFromMappingID.Parameters.AddWithValue("@mappingID", thisStop.mappingID);
cmdGetStoreFromMappingID.CommandType = System.Data.CommandType.StoredProcedure;
theReader = cmdGetStoreFromMappingID.ExecuteReader();
if (theReader.HasRows)
{
while (theReader.Read())
{
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();
if (theReader["PODRequired"] == DBNull.Value)
{
thisStore.PODRequired = false;
}
else
{
thisStore.PODRequired = (bool)theReader["PODRequired"];
}
thisStop.store = thisStore;
}
}
theReader.Close();
}
for (int i = 0, l = stops.Count; i < l; i++)
{
StopWithStore thisStop = stops[i];
SqlCommand cmdGetFailuresForStop = new SqlCommand("GetFailuresForStop", theConnection);
cmdGetFailuresForStop.Parameters.AddWithValue("@stopID", thisStop.id);
cmdGetFailuresForStop.CommandType = System.Data.CommandType.StoredProcedure;
theReader = cmdGetFailuresForStop.ExecuteReader();
if (theReader.HasRows)
{
thisStop.failure = new List<FailureWithReason>();
while (theReader.Read())
{
FailureWithReason thisFailure = new FailureWithReason();
thisFailure.failureID = (int)theReader["FailureID"];
thisFailure.stopID = (int)theReader["StopID"];
thisFailure.parentReasonCode = (int)theReader["ReasonID"];
thisFailure.childReasonCode = (int)theReader["ChildReasonID"];
thisFailure.emailSent = (bool)theReader["EmailSent"];
if (theReader["Comment"] != System.DBNull.Value)
thisFailure.comment = (string)theReader["Comment"];
else
thisFailure.comment = "";
thisStop.failure.Add(thisFailure);
}
}
theReader.Close();
}
for (int i = 0, l = stops.Count; i < l; i++)
{
StopWithStore thisStop = stops[i];
List<FailureWithReason> thisFailure = thisStop.failure;
if (thisFailure != null)
{
for (int j = 0, k = thisFailure.Count; j < k; j++)
{
SqlCommand cmdDetail = new SqlCommand("GetChildReasonDetail", theConnection);
cmdDetail.Parameters.AddWithValue("@childReasonCode", thisFailure[j].childReasonCode.ToString());
cmdDetail.CommandType = System.Data.CommandType.StoredProcedure;
theReader = cmdDetail.ExecuteReader();
if (theReader.HasRows)
{
while (theReader.Read())
{
ReasonChildWithParent theReason = new ReasonChildWithParent();
theReason.childReasonCode = thisFailure[j].childReasonCode;
theReason.childReasonExplanation = theReader["ChildReasonExplanation"].ToString();
theReason.childReasonName = theReader["ChildReasonName"].ToString();
theReason.escalation = (bool)theReader["Escalation"];
theReason.photoRequired = (bool)theReader["PhotoRequired"];
Reason theParentReason = new Reason();
theParentReason.reasonCode = (int)theReader["ReasonID"];
theParentReason.reasonName = theReader["ReasonName"].ToString();
theReason.parentReason = theParentReason;
thisFailure[j].reason = theReason;
}
}
theReader.Close();
}
}
}
thisTrip.stops = stops;
}
else
{
theReader.Close();
}
}
}
if (thisTrip.stops == null)
{
try
{
SqlCommand cmdResetTrip = new SqlCommand("DELETE FROM Trip WHERE TripID = " + thisTrip.id, theConnection);
int numRowsAffected = cmdResetTrip.ExecuteNonQuery();
if (numRowsAffected > 0)
{
theResponse = null;
return GetOpenTripForRouteNameAndUserV7(aRouteName, aUsername, aGmtOffset);
}
else
{
theResponse.statusCode = 6;
theResponse.statusDescription = "Invalid Trip Data. Please contact the service center.";
}
}
catch (Exception _exception)
{
theResponse.statusCode = 6;
theResponse.statusDescription = _exception.Message + " / " + _exception.StackTrace;
}
}
else
{
theResponse.trip = thisTrip;
theResponse.statusCode = 0;
theResponse.statusDescription = "";
}
}
}
closeDataConnection();
}
else
{
theResponse.statusCode = 4;
theResponse.statusDescription = "Route Name or Username not provided";
}
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 ResponseStoreList GetAllStores()
{
ResponseStoreList theResponse = new ResponseStoreList();
openDataConnection();
SqlCommand cmdGetAllUsers = new SqlCommand("SELECT * FROM Store", theConnection);
theReader = cmdGetAllUsers.ExecuteReader();
if (theReader.HasRows)
{
List<Store> listOfStores = new List<Store>();
int numRecords = 0;
while (theReader.Read())
{
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();
listOfStores.Add(thisStore);
numRecords++;
}
theResponse.stores = listOfStores;
theResponse.numberOfRecords = numRecords;
theResponse.statusCode = 0;
theResponse.statusDescription = "";
}
else
{
theResponse.statusCode = 4;
theResponse.statusDescription = "There are no stores in the database";
}
theReader.Close();
closeDataConnection();
return theResponse;
}
public ResponseStoreList GetAllStoresWithRange(string startingIndex, string endingIndex)
{
ResponseStoreList theResponse = new ResponseStoreList();
openDataConnection();
SqlCommand cmdGetAllUsers = new SqlCommand("SELECT * FROM Store", theConnection);
theReader = cmdGetAllUsers.ExecuteReader();
if (theReader.HasRows)
{
List<Store> listOfStores = new List<Store>();
int numRecords = 0;
while (theReader.Read())
{
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();
listOfStores.Add(thisStore);
numRecords++;
}
theResponse.stores = new List<Store>();
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++)
{
theResponse.stores.Add(listOfStores[i - 1]);
}
theResponse.numberOfRecords = numRecords;
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 stores in the database";
}
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 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 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 CreateStore(Store aStoreModel)
{
Response theResponse = new Response();
if (aStoreModel != null)
{
if (aStoreModel.storeName == null)
{
theResponse.statusDescription = "Store Name was not supplied";
}
if (aStoreModel.storeAddress == null)
{
theResponse.statusDescription = "Store Address was not supplied";
}
if (aStoreModel.storeCity == null)
{
theResponse.statusDescription = "Store City was not supplied";
}
if (aStoreModel.storeZip == null)
{
theResponse.statusDescription = "Store Zip Code was not supplied";
}
else if (aStoreModel.storeZip.Count() > 10)
{
theResponse.statusDescription = "Store Zip Code is longer than 10 characters";
}
if (aStoreModel.storeState == null)
{
theResponse.statusDescription = "Store State was not supplied";
}
if (aStoreModel.storePhone == null)
{
theResponse.statusDescription = "Store Phone Number was not supplied";
}
if (aStoreModel.storeManagerName == null)
{
theResponse.statusDescription = "Store Manager's name was not supplied";
}
if (aStoreModel.storeEmailAddress == null)
{
theResponse.statusDescription = "Store's email address was not supplied";
}
if (doesStoreExist(aStoreModel.storeNumber))
{
theResponse.statusCode = 3;
theResponse.statusDescription = "Store Number already exists";
}
if (theResponse.statusDescription.Equals(""))
{
openDataConnection();
SqlCommand cmdCreateStore = new SqlCommand("CreateStore", theConnection);
cmdCreateStore.Parameters.AddWithValue("@storeName", aStoreModel.storeName);
cmdCreateStore.Parameters.AddWithValue("@storeAddress", aStoreModel.storeAddress);
cmdCreateStore.Parameters.AddWithValue("@storeCity", aStoreModel.storeCity);
cmdCreateStore.Parameters.AddWithValue("@storeZip", aStoreModel.storeZip);
cmdCreateStore.Parameters.AddWithValue("@storeState", aStoreModel.storeState);
cmdCreateStore.Parameters.AddWithValue("@storePhone", aStoreModel.storePhone);
cmdCreateStore.Parameters.AddWithValue("@storeEmail", aStoreModel.storeEmailAddress);
cmdCreateStore.Parameters.AddWithValue("@storeManager", aStoreModel.storeManagerName);
cmdCreateStore.Parameters.AddWithValue("@storeNumber", aStoreModel.storeNumber);
cmdCreateStore.Parameters.AddWithValue("@storeOwnershipType", aStoreModel.storeOwnershipType);
cmdCreateStore.Parameters.AddWithValue("@PODRequired", aStoreModel.PODRequired);
cmdCreateStore.CommandType = System.Data.CommandType.StoredProcedure;
int numRowsAffected = cmdCreateStore.ExecuteNonQuery();
if (numRowsAffected > 0)
{
theResponse.statusCode = 0;
theResponse.statusDescription = "";
}
else
{
theResponse.statusCode = 6;
theResponse.statusDescription = "Could not add this store";
}
}
}
else
{
theResponse.statusCode = 6;
theResponse.statusDescription = "Expected User Model not received";
}
return theResponse;
}
public Response UploadStoresDotNet(string fileName, string username)
{
int currentRowPointer = 0;
Response theResponse = new Response();
string currentPath = HttpContext.Current.Server.MapPath("~");
long currentTime = DateTime.Now.ToFileTimeUtc();
//string fileName = "stores_" + currentTime;
string finalPath = currentPath + "\\uploads\\" + fileName;
//FileStream fileToUpload = new FileStream(finalPath, FileMode.Create);
//MultipartParser parser = new MultipartParser(fileStream);
//if (parser.Success)
//{
// fileToUpload.Write(parser.FileContents, 0, parser.FileContents.Length);
// fileToUpload.Close();
// fileToUpload.Dispose();
//}
//else
//{
// theResponse.statusCode = 6;
// theResponse.statusDescription = "Unable to parse input data";
// return theResponse;
//}
int recordsFound = 0;
int recordsAdded = 0;
int recordsUpdated = 0;
List<string> feedback = new List<string>();
string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + finalPath + ";Extended Properties=\"Excel 8.0;HDR=Yes;ReadOnly=False\"";
//string connectionString = connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + finalPath + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
List<Op> opsToBeUpdated = new List<Op>();
try
{
OleDbConnection con = new OleDbConnection(connectionString);
OleDbCommand cmd = new OleDbCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.Connection = con;
OleDbDataAdapter dAdapter = new OleDbDataAdapter(cmd);
DataTable dtExcelRecords = new DataTable();
con.Open();
DataTable dtExcelSheetName = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string getExcelSheetName = dtExcelSheetName.Rows[0]["Table_Name"].ToString();
cmd.CommandText = "SELECT * FROM [" + getExcelSheetName + "]";
cmd.CommandTimeout = 10000;
OleDbDataReader oleReader;
oleReader = cmd.ExecuteReader();
if (oleReader.HasRows)
{
List<Store> stores = new List<Store>();
while (oleReader.Read())
{
currentRowPointer++;
if (oleReader[0].ToString().Equals("Store #"))
{
continue;
}
if (oleReader[0].ToString().Equals(""))
{
break;
}
string thisStoreNumber = oleReader[0].ToString();
if (doesStoreExist(thisStoreNumber))
{
Store thisStoreForUpdate = new Store();
thisStoreForUpdate.storeID = getStoreIDForStoreNumber(thisStoreNumber);
thisStoreForUpdate.storeNumber = oleReader[0].ToString();
thisStoreForUpdate.storeName = oleReader[1].ToString();
thisStoreForUpdate.storeAddress = oleReader[2].ToString();
thisStoreForUpdate.storeCity = oleReader[3].ToString();
thisStoreForUpdate.storeZip = oleReader[4].ToString();
thisStoreForUpdate.storeState = oleReader[5].ToString();
thisStoreForUpdate.storePhone = oleReader[6].ToString();
thisStoreForUpdate.storeManagerName = oleReader[7].ToString();
thisStoreForUpdate.storeEmailAddress = oleReader[8].ToString();
if (oleReader.FieldCount > 9)
{
thisStoreForUpdate.storeOwnershipType = oleReader[9].ToString();
}
if (!String.IsNullOrEmpty(oleReader[10].ToString()))
{
thisStoreForUpdate.PODRequired = Convert.ToBoolean(oleReader[10].ToString());
}
Response updateStoreResponse = UpdateStore(thisStoreForUpdate);
if (updateStoreResponse.statusCode == 0)
{
feedback.Add("The Store Number " + thisStoreNumber + " already exists in the database. The record was updated.");
recordsUpdated++;
}
else
{
feedback.Add("The Store Number " + thisStoreNumber + " already exists in the database. The record could not be updated.");
}
recordsFound++;
continue;
}
Store thisStore = new Store();
thisStore.storeNumber = oleReader[0].ToString();
thisStore.storeName = oleReader[1].ToString();
thisStore.storeAddress = oleReader[2].ToString();
thisStore.storeCity = oleReader[3].ToString();
thisStore.storeZip = oleReader[4].ToString();
thisStore.storeState = oleReader[5].ToString();
thisStore.storePhone = oleReader[6].ToString();
thisStore.storeManagerName = oleReader[7].ToString();
thisStore.storeEmailAddress = oleReader[8].ToString();
if (oleReader.FieldCount > 9)
{
thisStore.storeOwnershipType = oleReader[9].ToString();
}
if (!String.IsNullOrEmpty(oleReader[10].ToString()))
{
thisStore.PODRequired = Convert.ToBoolean(oleReader[10].ToString());
}
stores.Add(thisStore);
recordsFound++;
}
for (int i = 0, l = stores.Count; i < l; i++)
{
Response createResponse = CreateStore(stores[i]);
if (createResponse.statusCode == 0)
{
recordsAdded++;
}
}
}
oleReader.Close();
}
catch (Exception _exception)
{
theResponse.statusCode = 6;
theResponse.statusDescription = _exception.Message + " - " + _exception.StackTrace + " - Last Row Pointer was at " + currentRowPointer;
return theResponse;
}
if (recordsFound > 0)
{
theResponse.statusCode = 0;
theResponse.statusDescription = "Found " + recordsFound + " store records in the file. Added " + recordsAdded + " records to the database.Updated " + recordsUpdated + " records in the database.";
}
else
{
theResponse.statusCode = 2;
theResponse.statusDescription = "No records found in the excel file";
}
if (feedback.Count > 0)
{
/* string emailString = "";
theResponse.statusDescription += "Feedback:";
for (int i = 0, l = feedback.Count; i < l; i++)
{
theResponse.statusDescription += feedback[i];
emailString += feedback[i];
}
*/
string emailString = "<ul>";
theResponse.statusDescription += "<br /><br /><p>Feedback:</p><ul>";
for (int i = 0, l = feedback.Count; i < l; i++)
{
theResponse.statusDescription += "<li>" + feedback[i] + "</li>";
emailString += "<li>" + feedback[i] + "</li>";
}
theResponse.statusDescription += "</ul>";
emailString += "</ul>";
//theResponse.statusDescription;
//emailString ;
SendEmailForUploadErrors("Stores", username, emailString, "");
}
// theResponse = new Response();
// theResponse.statusCode = 0;
// theResponse.statusDescription = "Response added successfully";
theResponse.statusCode = 0;
//WebOperationContext.Current.OutgoingResponse.ContentType = "text/html";
return theResponse;
}
public Response UploadRoutesDotNet(string fileName, string username)
{
Response theResponse = new Response();
string cdcName = "";
string currentPath = HttpContext.Current.Server.MapPath("~");
long currentTime = DateTime.Now.ToFileTimeUtc();
//string fileName = "routes_" + currentTime;
string finalPath = currentPath + "\\uploads\\" + fileName;
//FileStream fileToUpload = new FileStream(finalPath, FileMode.Create);
//MultipartParser parser = new MultipartParser(fileStream);
//if (parser.Success)
//{
// fileToUpload.Write(parser.FileContents, 0, parser.FileContents.Length);
// fileToUpload.Close();
// fileToUpload.Dispose();
//}
//else
//{
// theResponse.statusCode = 6;
// theResponse.statusDescription = "Unable to parse input data";
// return theResponse;
//}
int recordsFound = 0;
int recordsAdded = 0;
int recordsUpdated = 0;
List<string> feedback = new List<string>();
//string connectionString = connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + finalPath + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + finalPath + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1;ReadOnly=False\"";
try
{
OleDbConnection con = new OleDbConnection(connectionString);
OleDbCommand cmd = new OleDbCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.Connection = con;
OleDbDataAdapter dAdapter = new OleDbDataAdapter(cmd);
DataTable dtExcelRecords = new DataTable();
con.Open();
DataTable dtExcelSheetName = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string getExcelSheetName = dtExcelSheetName.Rows[0]["Table_Name"].ToString();
cmd.CommandText = "SELECT * FROM [" + getExcelSheetName + "]";
OleDbDataReader oleReader;
oleReader = cmd.ExecuteReader();
if (oleReader.HasRows)
{
List<Route> routes = new List<Route>();
int rowCounter = 0;
while (oleReader.Read())
{
rowCounter += 1;
if (oleReader[0].ToString().Equals("Transaction Type") || oleReader[1].ToString().Equals("CDC"))
{
continue;
}
if (oleReader[0].ToString().Equals(""))
{
break;
}
if (cdcName == "")
cdcName = oleReader[1].ToString();
if (oleReader[0].ToString().ToUpper().Trim().Equals("ADD"))
{
recordsFound++;
if (!doesCDCExist(oleReader[1].ToString()))
{
feedback.Add("CDC " + oleReader[1].ToString() + " does not exist");
continue;
}
if (doesRouteExist(oleReader[2].ToString()))
{
feedback.Add("The route " + oleReader[2].ToString() + " already exists");
continue;
}
Route thisRoute = new Route();
thisRoute.cdc = new CDC();
thisRoute.cdc.name = oleReader[1].ToString();
thisRoute.cdc.id = getCDCIDForCDCName(oleReader[1].ToString());
thisRoute.routeName = oleReader[2].ToString();
int numberOfStopsForThisRoute = oleReader.FieldCount;
List<Store> stops = new List<Store>();
for (int i = 3; i < numberOfStopsForThisRoute; i++)
{
Store thisStore = new Store();
string thisStoreNumber = oleReader[i].ToString();
thisStore.storeID = getStoreIDForStoreNumber(thisStoreNumber);
if (thisStore.storeID > 0)
{
stops.Add(thisStore);
}
else
{
if (!thisStoreNumber.Equals("0") && !thisStoreNumber.Equals(""))
{
feedback.Add("The Store Number " + thisStoreNumber + " was not found in the database");
}
}
}
if (stops.Count > 0)
{
thisRoute.stores = stops;
feedback.Add("Route " + thisRoute.routeName + " has " + thisRoute.stores.Count + " stops.");
routes.Add(thisRoute);
}
else
{
feedback.Add("The Route " + thisRoute.routeName + " could not be added as none of the stores listed against this route are present in the database");
}
}
else if (oleReader[0].ToString().ToUpper().Trim().Equals("UPDATE"))
{
recordsFound++;
if (!doesRouteExist(oleReader[2].ToString()))
{
feedback.Add("The route " + oleReader[2].ToString() + " does not exist thus cannot be updated");
continue;
}
else
{
string thisRouteName = oleReader[2].ToString();
ResponseRouteList thisRouteDetail = GetRouteDetail(thisRouteName);
bool validRoute = false;
int numberOfStops = 0;
if (thisRouteDetail != null)
{
validRoute = true;
if (thisRouteDetail.routes != null)
{
numberOfStops = thisRouteDetail.routes[0].stores.Count;
}
}
if (validRoute)
{
openDataConnection();
SqlCommand cmdDisableMappingsForRoute = new SqlCommand("DisableCurrentMappingsForRouteName", theConnection);
cmdDisableMappingsForRoute.Parameters.AddWithValue("@routeName", thisRouteName);
cmdDisableMappingsForRoute.CommandType = System.Data.CommandType.StoredProcedure;
int numMappingsDisabled = cmdDisableMappingsForRoute.ExecuteNonQuery();
closeDataConnection();
if (numMappingsDisabled >= numberOfStops)
{
List<Store> newStops = new List<Store>();
int numberOfStopsForThisRoute = oleReader.FieldCount;
for (int i = 3; i < numberOfStopsForThisRoute; i++)
{
Store thisStore = new Store();
string thisStoreNumber = oleReader[i].ToString();
if (thisStoreNumber != null && !thisStoreNumber.Equals(""))
{
thisStore.storeNumber = thisStoreNumber;
thisStore.storeID = getStoreIDForStoreNumber(thisStoreNumber);
if (thisStore.storeID > 0)
{
newStops.Add(thisStore);
}
else
{
feedback.Add("The Store Number " + thisStoreNumber + " was not found in the database");
}
}
}
openDataConnection();
foreach (Store aStore in newStops)
{
SqlCommand cmdAddStoreToRoute = new SqlCommand("AddStoreToRoute", theConnection);
cmdAddStoreToRoute.Parameters.AddWithValue("@routeName", thisRouteName);
cmdAddStoreToRoute.Parameters.AddWithValue("@storeID", aStore.storeID);
cmdAddStoreToRoute.CommandType = System.Data.CommandType.StoredProcedure;
int numRowsAffectedForAddStoreToRoute = cmdAddStoreToRoute.ExecuteNonQuery();
feedback.Add("Added Store " + aStore.storeNumber + " to Route " + thisRouteName);
}
feedback.Add("Updated Route " + thisRouteName);
closeDataConnection();
recordsUpdated++;
}
else
{
feedback.Add("The route " + oleReader[2].ToString() + " had " + numberOfStops + " mappings but only " + numMappingsDisabled + " were disabled and this route cannot be updated");
continue;
}
}
else
{
feedback.Add("The route " + oleReader[2].ToString() + " does not seem to be valid and thus cannot be updated");
continue;
}
}
}
}
for (int i = 0, l = routes.Count; i < l; i++)
{
Response createResponse = CreateRoute(routes[i]);
if (createResponse.statusCode == 0)
{
recordsAdded++;
}
}
}
oleReader.Close();
}
catch (Exception _exception)
{
theResponse.statusCode = 6;
theResponse.statusDescription = _exception.Message + " Line Number: " + _exception.StackTrace;
return theResponse;
}
if (recordsFound > 0)
{
theResponse.statusCode = 0;
theResponse.statusDescription = "Found " + recordsFound + " route records in the file.<br />Added " + recordsAdded + " records to the database.<br />Updated " + recordsUpdated + " records in the database.";
}
else
{
theResponse.statusCode = 2;
theResponse.statusDescription = "No records found in the excel file";
}
if (feedback.Count > 0)
{
string emailString = "<ul>";
theResponse.statusDescription += "<br /><br /><p>Feedback:</p><ul>";
for (int i = 0, l = feedback.Count; i < l; i++)
{
theResponse.statusDescription += "<li>" + feedback[i] + "</li>";
emailString += "<li>" + feedback[i] + "</li>";
}
theResponse.statusDescription += "</ul>";
emailString += "</ul>";
SendEmailForUploadErrors("Routes", username, emailString, cdcName);
}
return theResponse;
}