//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 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 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 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;
}