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 Response CreateReason(Reason aReasonModel)
{
Response theResponse = new Response();
if (aReasonModel != null)
{
if (aReasonModel.reasonName == null)
{
theResponse.statusDescription = "Reason Name not supplied";
}
if (parentReasonExists(aReasonModel.reasonName))
{
theResponse.statusCode = 3;
theResponse.statusDescription = "Reason name already exists";
return theResponse;
}
if (theResponse.statusDescription.Equals(""))
{
openDataConnection();
SqlCommand cmdCreateReason = new SqlCommand("CreateParentReason", theConnection);
cmdCreateReason.Parameters.AddWithValue("@reasonName", aReasonModel.reasonName);
cmdCreateReason.CommandType = System.Data.CommandType.StoredProcedure;
try
{
int numRowsAffected = cmdCreateReason.ExecuteNonQuery();
if (numRowsAffected > 0)
{
theResponse.statusCode = 0;
theResponse.statusDescription = "";
}
else
{
theResponse.statusCode = 6;
theResponse.statusDescription = "The specified reason could not be added";
}
}
catch (Exception _exception)
{
theResponse.statusCode = 6;
theResponse.statusDescription = _exception.Message;
}
closeDataConnection();
}
else
{
theResponse.statusCode = 6;
}
}
else
{
theResponse.statusCode = 6;
theResponse.statusDescription = "Expected Reason Model not received";
}
return theResponse;
}
public ResponseReasonList GetAllParentReasons()
{
ResponseReasonList theResponse = new ResponseReasonList();
openDataConnection();
SqlCommand cmdGet = new SqlCommand("GetAllParentReasons", theConnection);
cmdGet.CommandType = System.Data.CommandType.StoredProcedure;
theReader = cmdGet.ExecuteReader();
if (theReader.HasRows)
{
List<Reason> allReasons = new List<Reason>();
while (theReader.Read())
{
Reason thisReason = new Reason();
thisReason.reasonCode = (int)theReader["ReasonID"];
thisReason.reasonName = theReader["ReasonName"].ToString();
allReasons.Add(thisReason);
}
theResponse.reasons = allReasons;
theResponse.statusCode = 0;
theResponse.statusDescription = "";
}
else
{
theResponse.statusCode = 4;
theResponse.statusDescription = "There are no reasons defined";
}
theReader.Close();
closeDataConnection();
return theResponse;
}
public Response UpdateReason(Reason aReasonModel)
{
Response theResponse = new Response();
if (aReasonModel != null)
{
if (aReasonModel.reasonName == null)
{
theResponse.statusDescription = "Updated Reason Name not supplied";
}
if (aReasonModel.reasonCode < 1)
{
theResponse.statusDescription = "Reason Code ID not supplied";
}
if (theResponse.statusDescription.Equals(""))
{
openDataConnection();
SqlCommand cmdUpdate = new SqlCommand("UpdateParentReason", theConnection);
cmdUpdate.Parameters.AddWithValue("@reasonID", aReasonModel.reasonCode);
cmdUpdate.Parameters.AddWithValue("@reasonName", aReasonModel.reasonName);
cmdUpdate.CommandType = System.Data.CommandType.StoredProcedure;
try
{
int numRowsAffected = cmdUpdate.ExecuteNonQuery();
if (numRowsAffected > 0)
{
theResponse.statusCode = 0;
theResponse.statusDescription = "";
}
else
{
theResponse.statusCode = 6;
}
}
catch (Exception _exception)
{
theResponse.statusCode = 6;
theResponse.statusDescription = _exception.Message;
}
closeDataConnection();
}
else
{
theResponse.statusCode = 6;
}
}
else
{
theResponse.statusCode = 6;
theResponse.statusDescription = "Expected Reason Model not received";
}
return theResponse;
}
