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;
        }
        private ResponseFailureList GetFailuresForStop(int stopID)
        {
            ResponseFailureList theResponse = new ResponseFailureList();

            openDataConnection();

            SqlCommand cmdGet = new SqlCommand("GetFailuresForStop", theConnection);
            cmdGet.Parameters.AddWithValue("@stopID", stopID);
            cmdGet.CommandType = System.Data.CommandType.StoredProcedure;

            try
            {
                theReader = cmdGet.ExecuteReader();

                if (theReader.HasRows)
                {
                    theResponse.failures = 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.valueEntered = (int)theReader["ValueEntered"];
                        thisFailure.reason = new ReasonChildWithParent();
                        thisFailure.reason.childReasonCode = thisFailure.childReasonCode;
                        thisFailure.reason.childReasonName = theReader["ChildReasonName"].ToString();
                        thisFailure.reason.escalation = (bool)theReader["Escalation"];
                        thisFailure.reason.parentReason = new Reason();
                        thisFailure.reason.parentReason.reasonCode = thisFailure.parentReasonCode;
                        thisFailure.reason.parentReason.reasonName = theReader["ParentReasonName"].ToString();
                        thisFailure.reason.childReasonExplanation = theReader["ChildReasonExplanation"].ToString();

                        theResponse.failures.Add(thisFailure);
                    }

                    theResponse.statusCode = 0;
                    theResponse.statusDescription = "";
                }
                else
                {
                    theResponse.statusCode = 4;
                    theResponse.statusDescription = "No failures found";
                }
            }
            catch (Exception _exception)
            {
                theResponse.statusCode = 6;
                theResponse.statusDescription = _exception.Message;
            }

            theReader.Close();

            closeDataConnection();

            return theResponse;
        }