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 CreateReasonChild(ReasonChildWithParent aChildReasonModel) { Response theResponse = new Response(); if (aChildReasonModel != null) { if (aChildReasonModel.childReasonName == null) { theResponse.statusDescription = "Child Reason Name was not supplied"; } if (aChildReasonModel.childReasonExplanation == null) { theResponse.statusDescription = "Child Reason Explanation was not supplied"; } if (aChildReasonModel.parentReason == null) { theResponse.statusDescription = "Child Reason's Parent Reason was not supplied"; } if (childReasonExists(aChildReasonModel.childReasonName)) { theResponse.statusCode = 3; theResponse.statusDescription = "Child Reason already exists"; return theResponse; } if (theResponse.statusDescription.Equals("")) { openDataConnection(); SqlCommand cmdCreateChildReason = new SqlCommand("CreateChildReason", theConnection); cmdCreateChildReason.Parameters.AddWithValue("@parentReasonID", aChildReasonModel.parentReason.reasonCode.ToString()); cmdCreateChildReason.Parameters.AddWithValue("@childReasonName", aChildReasonModel.childReasonName); cmdCreateChildReason.Parameters.AddWithValue("@childReasonExplanation", aChildReasonModel.childReasonExplanation); cmdCreateChildReason.Parameters.AddWithValue("@escalation", (object)aChildReasonModel.escalation); cmdCreateChildReason.Parameters.AddWithValue("@photoRequired", (object)aChildReasonModel.photoRequired); cmdCreateChildReason.Parameters.AddWithValue("@valueRequired", (object)aChildReasonModel.valueRequired); cmdCreateChildReason.Parameters.AddWithValue("@PODRequired", (object)aChildReasonModel.PODRequired); cmdCreateChildReason.Parameters.AddWithValue("@valueUnitPrice", aChildReasonModel.valueUnitPrice); cmdCreateChildReason.CommandType = System.Data.CommandType.StoredProcedure; try { int numRowsAffected = cmdCreateChildReason.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 Child Reason Model not received"; } return theResponse; }