public Response CommitCachedDataV5(TripForSync aTripModel)
        {
            Response theResponse = new Response();

            int totalFailuresCommitted = 0;
            int totalImagesCommitted = 0;
            int totalDeliveryCodesCommitted = 0;
            int totalCommentsCommitted = 0;

            int totalFailureErrors = 0;
            int totalImageErrors = 0;
            int totalDeliveryCodeErrors = 0;
            int totalCommentErrors = 0;

            if (aTripModel != null)
            {
                if (aTripModel.id > 0)
                {
                    openDataConnection();

                    SqlCommand cmdCheckTripID = new SqlCommand("SELECT TripID FROM Trip WHERE TripID = " + aTripModel.id, theConnection);

                    theReader = cmdCheckTripID.ExecuteReader();

                    if (theReader.HasRows)
                    {
                        theReader.Close();

                        if (aTripModel.stops != null && aTripModel.stops.Count > 0)
                        {
                            foreach (StopWithStoreAndFailure aStop in aTripModel.stops)
                            {
                                if (aStop.committed)
                                {
                                    continue;
                                }

                                SqlCommand cmdCheckStopID = new SqlCommand("SELECT StopID FROM Stop WHERE StopID = " + aStop.id, theConnection);
                                openDataConnection();
                                theReader = cmdCheckStopID.ExecuteReader();

                                if (theReader.HasRows)
                                {
                                    theReader.Close();

                                    if (aStop.failure != null && aStop.failure.Count > 0)
                                    {
                                        foreach (Failure aFailure in aStop.failure)
                                        {
                                            if (aFailure.committed)
                                            {
                                                continue;
                                            }

                                            ResponseFailure addFailureResponse = AddFailure(aFailure);

                                            if (addFailureResponse.statusCode == 0)
                                            {
                                                totalFailuresCommitted++;
                                            }
                                            else
                                            {
                                                totalFailureErrors++;
                                            }

                                            if (aFailure.photos != null && aFailure.photos.Count > 0)
                                            {
                                                foreach (Photo aPhoto in aFailure.photos)
                                                {
                                                    Photo thisPhoto = new Photo();
                                                    thisPhoto.imageData = aPhoto.imageData;
                                                    thisPhoto.stopID = aStop.id;
                                                    thisPhoto.failureID = addFailureResponse.failure.failureID;

                                                    Response addPhotoResponse = AddPhotoToStop(thisPhoto);

                                                    if (addPhotoResponse.statusCode == 0)
                                                    {
                                                        totalImagesCommitted++;
                                                    }
                                                    else
                                                    {
                                                        totalImageErrors++;
                                                    }
                                                }
                                            }
                                            //if (aFailure.deliveryCodes != null && aFailure.deliveryCodes.Count > 0)
                                            //{
                                            //    foreach (Delivery aDelivery in aFailure.deliveryCodes)
                                            //    {
                                            //        Delivery thisDelivery = new Delivery();
                                            //        thisDelivery.deliveryCode = aDelivery.deliveryCode;
                                            //        thisDelivery.stopID = aStop.id;
                                            //        thisDelivery.failureID = addFailureResponse.failure.failureID;

                                            //        Response addDeliveryResponse = AddDelivery(thisDelivery);

                                            //        if (addDeliveryResponse.statusCode == 0)
                                            //        {
                                            //            totalDeliveryCodesCommitted++;
                                            //        }
                                            //        else
                                            //        {
                                            //            totalDeliveryCodeErrors++;
                                            //        }
                                            //    }
                                            //}
                                        }
                                    }

                                    if (aStop.completed)
                                    {
                                        Response addCompletedResponse = CompleteStop(aStop.id.ToString());
                                    }
                                    ConsolidateEmails(aStop.id.ToString());
                                }
                                else
                                {
                                    theReader.Close();

                                    theResponse.statusCode = 6;
                                    theResponse.statusDescription = "The Stop ID " + aStop.id + " does not exist";
                                }
                            }

                            if (totalFailureErrors == 0 && totalImageErrors == 0 && totalDeliveryCodeErrors == 0 && totalCommentErrors == 0)
                            {
                                theResponse.statusCode = 0;
                                theResponse.statusDescription = "All data has been synchronized";
                            }
                            else
                            {
                                if (totalFailuresCommitted > 0 || totalImagesCommitted > 0 || totalDeliveryCodeErrors > 0 || totalCommentsCommitted > 0)
                                {
                                    theResponse.statusCode = 6;
                                    theResponse.statusDescription = "Some of the data has been synchronized. " + totalFailureErrors + " Failures, " + totalDeliveryCodeErrors + " DeliveryCodes, " + totalImageErrors + " Images and " + totalCommentErrors + " Comments could not be synchronized";
                                }
                            }
                        }
                        else
                        {
                            theResponse.statusCode = 6;
                            theResponse.statusDescription = "No stops were supplied with the trip model. No data was synchronized.";
                        }
                    }
                    else
                    {
                        theReader.Close();

                        theResponse.statusCode = 4;
                        theResponse.statusDescription = "Trip ID was invalid";
                    }

                    closeDataConnection();
                }
            }
            else
            {
                theResponse.statusCode = 6;
                theResponse.statusDescription = "Trip Model not supplied";
            }

            return theResponse;
        }
        public ResponsePhoto GetPhotosForStop(string aStopID)
        {
            ResponsePhoto theResponse = new ResponsePhoto();

            if (aStopID == null || aStopID.Equals(""))
            {
                theResponse.statusCode = 3;
                theResponse.statusDescription = "StopID missing";
            }
            else
            {
                openDataConnection();

                SqlCommand cmdGetPhotos = new SqlCommand("GetImagesForStop", theConnection);
                cmdGetPhotos.Parameters.AddWithValue("@stopID", aStopID);
                cmdGetPhotos.CommandType = System.Data.CommandType.StoredProcedure;

                theReader = cmdGetPhotos.ExecuteReader();

                if (!theReader.HasRows)
                {
                    theResponse.statusCode = 4;
                    theResponse.statusDescription = "There are no photos uploaded for this stop, yet";
                }
                else
                {
                    List<Photo> photos = new List<Photo>();

                    while (theReader.Read())
                    {
                        Photo thisPhoto = new Photo();

                        thisPhoto.stopID = Int32.Parse(aStopID);
                        thisPhoto.imageData = theReader["Photo"].ToString();
                        thisPhoto.photoID = (int)theReader["PhotoID"];

                        if (theReader["FailureID"] != DBNull.Value)
                            thisPhoto.failureID = (int)theReader["FailureID"];
                        else
                            thisPhoto.failureID = 0;

                        photos.Add(thisPhoto);
                    }

                    theResponse.photos = photos;
                    theResponse.statusCode = 0;
                    theResponse.statusDescription = "";
                }

                closeDataConnection();
            }

            return theResponse;
        }
        public Response AddPhotoToStopTransaction(Photo aPhoto)
        {
            Response theResponse = new Response();

            if (aPhoto != null)
            {
                if (aPhoto.stopID <= 0)
                {
                    theResponse.statusDescription = "Stop ID is missing";
                }
                if (aPhoto.imageData == null || aPhoto.imageData.Equals(""))
                {
                    theResponse.statusDescription = "Image Data is missing";
                }

                if (theResponse.statusDescription.Equals(""))
                {
                    //openDataConnection();

                    SqlCommand cmdAddPhoto = new SqlCommand("AddPhotoForStop", theConnection);
                    cmdAddPhoto.Transaction = theTrans;
                    cmdAddPhoto.Parameters.AddWithValue("@stopID", aPhoto.stopID);
                    cmdAddPhoto.Parameters.AddWithValue("@failureID", aPhoto.failureID);
                    //cmdAddPhoto.Parameters.AddWithValue("@failureID", null);
                    cmdAddPhoto.Parameters.AddWithValue("@photoData", aPhoto.imageData);
                    cmdAddPhoto.CommandType = System.Data.CommandType.StoredProcedure;

                    int numRowsAffected = cmdAddPhoto.ExecuteNonQuery();

                    if (numRowsAffected > 0)
                    {
                        theResponse.statusCode = 0;
                        theResponse.statusDescription = "";
                    }
                    else
                    {
                        theResponse.statusDescription = "Could not add photo to StopID " + aPhoto.stopID;
                    }

                    //closeDataConnection();
                }
                else
                {
                    theResponse.statusCode = 6;
                }
            }
            else
            {
                theResponse.statusCode = 4;
                theResponse.statusDescription = "Photo Model missing";
            }

            return theResponse;
        }
        public Response CommitIssuesV7(StopWithStoreAndFailure aStop)
        {
            //string text = "";
            //text = " Online Start time :" + DateTime.Now + "\n";
            //WriteToFile(text);

            Response theResponse = new Response();
            int totalFailureErrors = 0, totalImageErrors = 0, totalDeliveryErrors = 0;

            try
            {
                openDataConnection();
                theTrans = theConnection.BeginTransaction();

                if (aStop.failure != null && aStop.failure.Count > 0)
                {
                    foreach (Failure aFailure in aStop.failure)
                    {
                        SqlCommand cmdCheckUniqueID = new SqlCommand("SELECT UniqueID FROM Failure WHERE UniqueID = '" + aFailure.uniqueID + "'", theConnection);
                        cmdCheckUniqueID.Transaction = theTrans;

                        object uniqueId = cmdCheckUniqueID.ExecuteScalar();

                        if (uniqueId != null && !string.IsNullOrEmpty(uniqueId.ToString()))
                        {
                            continue;
                        }
                        ResponseFailure addFailureResponse = AddFailureTransaction(aFailure);

                        if (addFailureResponse.statusCode != 0)
                        {
                            totalFailureErrors++;
                        }

                        if (aFailure.photos != null && aFailure.photos.Count > 0)
                        {
                            foreach (Photo aPhoto in aFailure.photos)
                            {
                                Photo thisPhoto = new Photo();
                                thisPhoto.imageData = aPhoto.imageData;
                                thisPhoto.stopID = aFailure.stopID;
                                thisPhoto.failureID = addFailureResponse.failure.failureID;

                                Response addPhotoResponse = AddPhotoToStopTransaction(thisPhoto);

                                if (addPhotoResponse.statusCode != 0)
                                {
                                    totalImageErrors++;
                                }

                            }
                        }
                        if (aFailure.deliveryCodes != null && aFailure.deliveryCodes.Count > 0)
                        {
                            foreach (Delivery aDelivery in aFailure.deliveryCodes)
                            {
                                Delivery thisDelivery = new Delivery();
                                thisDelivery.deliveryCode = aDelivery.deliveryCode;
                                thisDelivery.stopID = aStop.id;
                                thisDelivery.failureID = addFailureResponse.failure.failureID;
                                //    thisDelivery.dateAdded = aDelivery.dateAdded;

                                Response addDeliveryResponse = AddDeliveryTransaction(thisDelivery);

                                if (addDeliveryResponse.statusCode != 0)
                                {
                                    totalDeliveryErrors++;
                                }

                            }
                        }
                    }
                }

                Response addStopCompletedDateResponse = AddStopCompletedDate(aStop.id.ToString(), aStop.completedDate);

                if (totalFailureErrors > 0 || totalImageErrors > 0 || totalDeliveryErrors > 0)
                {
                    theTrans.Rollback();
                    theResponse.statusCode = 6;
                    theResponse.statusDescription = "Unable to update data";
                }
                else
                {
                    theTrans.Commit();
                    theResponse.statusCode = 0;
                    theResponse.statusDescription = "All data has been synchronized";
                    ConsolidateEmails(aStop.id.ToString());
                }
            }
            catch (Exception ex)
            {
                //text = "Exception :" + ex.Message + "\n";
                //WriteToFile(text);

                theTrans.Rollback();
                theResponse.statusCode = 6;
                theResponse.statusDescription = "Unable to update data";
            }
            finally
            {
                theTrans = null;
                closeDataConnection();

            }
            //text = " End time   :" + DateTime.Now + "\n";
            //WriteToFile(text);

            return theResponse;
        }
        public Response CommitIssues(StopWithStoreAndFailure aStop)
        {
            Response theResponse = new Response();
            int totalFailureErrors = 0, totalImageErrors = 0;

            try
            {
                openDataConnection();
                theTrans = theConnection.BeginTransaction();

                if (aStop.failure != null && aStop.failure.Count > 0)
                {
                    foreach (Failure aFailure in aStop.failure)
                    {
                        SqlCommand cmdCheckUniqueID = new SqlCommand("SELECT UniqueID FROM Failure WHERE UniqueID = '" + aFailure.uniqueID + "'", theConnection);
                        cmdCheckUniqueID.Transaction = theTrans;

                        object uniqueId = cmdCheckUniqueID.ExecuteScalar();

                        if (uniqueId != null && !string.IsNullOrEmpty(uniqueId.ToString()))
                        {
                            continue;
                        }
                        ResponseFailure addFailureResponse = AddFailureTransaction(aFailure);

                        if (addFailureResponse.statusCode != 0)
                        {
                            totalFailureErrors++;
                        }

                        if (aFailure.photos != null && aFailure.photos.Count > 0)
                        {
                            foreach (Photo aPhoto in aFailure.photos)
                            {
                                Photo thisPhoto = new Photo();
                                thisPhoto.imageData = aPhoto.imageData;
                                thisPhoto.stopID = aFailure.stopID;
                                thisPhoto.failureID = addFailureResponse.failure.failureID;

                                Response addPhotoResponse = AddPhotoToStopTransaction(thisPhoto);

                                if (addPhotoResponse.statusCode != 0)
                                {
                                    totalImageErrors++;
                                }

                            }
                        }
                    }
                }

                if (totalFailureErrors > 0 || totalImageErrors > 0)
                {
                    theTrans.Rollback();
                    theResponse.statusCode = 6;
                    theResponse.statusDescription = "Unable to update data";
                }
                else
                {
                    theTrans.Commit();
                    theResponse.statusCode = 0;
                    theResponse.statusDescription = "All data has been synchronized";
                    ConsolidateEmails(aStop.id.ToString());
                }
            }
            catch (Exception ex)
            {
                theTrans.Rollback();
                theResponse.statusCode = 6;
                theResponse.statusDescription = "Unable to update data";
            }
            finally
            {
                theTrans = null;
                closeDataConnection();

            }
            return theResponse;
        }
        public Response CommitCachedDataV7(TripForSync aTripModel)
        {
            //string text = "";
            //text = " Offline Start time :" + DateTime.Now + "\n";
            //WriteToFile(text);

            Response theResponse = new Response();
            List<int> lstStopIds = new List<int>();

            int totalFailuresCommitted = 0;
            int totalImagesCommitted = 0;
            int totalDeliveryCodesCommitted = 0;
            int totalCommentsCommitted = 0;

            int totalFailureErrors = 0;
            int totalImageErrors = 0;
            int totalDeliveryCodeErrors = 0;
            int totalCommentErrors = 0;

            try
            {
                openDataConnection();
                theTrans = theConnection.BeginTransaction();

                if (aTripModel != null)
                {
                    if (aTripModel.id > 0)
                    {
                        SqlCommand cmdCheckTripID = new SqlCommand("SELECT TripID FROM Trip WHERE TripID = " + aTripModel.id, theConnection);
                        cmdCheckTripID.Transaction = theTrans;

                        theReader = cmdCheckTripID.ExecuteReader();

                        if (theReader.HasRows)
                        {
                            theReader.Close();

                            if (aTripModel.stops != null && aTripModel.stops.Count > 0)
                            {
                                foreach (StopWithStoreAndFailure aStop in aTripModel.stops)
                                {
                                    if (aStop.committed)
                                    {
                                        continue;
                                    }

                                    SqlCommand cmdCheckStopID = new SqlCommand("SELECT StopID FROM Stop WHERE StopID = " + aStop.id, theConnection);
                                    cmdCheckStopID.Transaction = theTrans;
                                    theReader = cmdCheckStopID.ExecuteReader();

                                    if (theReader.HasRows)
                                    {
                                        theReader.Close();

                                        if (aStop.failure != null && aStop.failure.Count > 0)
                                        {
                                            foreach (Failure aFailure in aStop.failure)
                                            {
                                                SqlCommand cmdCheckUniqueID = new SqlCommand("SELECT UniqueID FROM Failure WHERE UniqueID = '" + aFailure.uniqueID + "'", theConnection);
                                                cmdCheckUniqueID.Transaction = theTrans;

                                                object uniqueId = cmdCheckUniqueID.ExecuteScalar();

                                                if (uniqueId != null && !string.IsNullOrEmpty(uniqueId.ToString()))
                                                {
                                                    continue;
                                                }

                                                ResponseFailure addFailureResponse = AddFailureTransaction(aFailure);

                                                if (addFailureResponse.statusCode == 0)
                                                {
                                                    totalFailuresCommitted++;
                                                }
                                                else
                                                {
                                                    totalFailureErrors++;
                                                }

                                                if (aFailure.photos != null && aFailure.photos.Count > 0)
                                                {
                                                    foreach (Photo aPhoto in aFailure.photos)
                                                    {
                                                        Photo thisPhoto = new Photo();
                                                        thisPhoto.imageData = aPhoto.imageData;
                                                        thisPhoto.stopID = aStop.id;
                                                        thisPhoto.failureID = addFailureResponse.failure.failureID;

                                                        Response addPhotoResponse = AddPhotoToStopTransaction(thisPhoto);

                                                        if (addPhotoResponse.statusCode == 0)
                                                        {
                                                            totalImagesCommitted++;
                                                        }
                                                        else
                                                        {
                                                            totalImageErrors++;
                                                        }
                                                    }
                                                }
                                                if (aFailure.deliveryCodes != null && aFailure.deliveryCodes.Count > 0)
                                                {
                                                    foreach (Delivery aDelivery in aFailure.deliveryCodes)
                                                    {
                                                        if (aDelivery.deliveryCode == 0)
                                                        {
                                                            continue;
                                                        }

                                                        Delivery thisDelivery = new Delivery();
                                                        thisDelivery.deliveryCode = aDelivery.deliveryCode;
                                                        thisDelivery.stopID = aStop.id;
                                                        thisDelivery.failureID = addFailureResponse.failure.failureID;
                                                        //thisDelivery.dateAdded = aDelivery.dateAdded;

                                                        Response addDeliveryResponse = AddDeliveryTransaction(thisDelivery);

                                                        if (addDeliveryResponse.statusCode == 0)
                                                        {
                                                            totalDeliveryCodesCommitted++;
                                                        }
                                                        else
                                                        {
                                                            totalDeliveryCodeErrors++;
                                                        }
                                                    }
                                                }
                                            }
                                        }

                                        Response addStopCompletedDateResponse = AddStopCompletedDate(aStop.id.ToString(), aStop.completedDate);

                                        if (aStop.completed)
                                        {
                                            Response addCompletedResponse = CompleteStopTransaction(aStop.id.ToString());
                                        }
                                        lstStopIds.Add(aStop.id);
                                        //ConsolidateEmails(aStop.id.ToString());
                                    }
                                    else
                                    {
                                        theReader.Close();

                                        theResponse.statusCode = 6;
                                        theResponse.statusDescription = "The Stop ID " + aStop.id + " does not exist";
                                    }
                                }

                                if (totalFailureErrors == 0 && totalImageErrors == 0 && totalDeliveryCodeErrors == 0 && totalCommentErrors == 0)
                                {
                                    theTrans.Commit();
                                    theResponse.statusCode = 0;
                                    theResponse.statusDescription = "All data has been synchronized";
                                    //for(int i = 0; i<lstStopIds.Count;i++)
                                    foreach (int id in lstStopIds)
                                        ConsolidateEmails(id.ToString());
                                    //ConsolidateEmails(aStop.id.ToString());
                                }
                                else
                                {
                                    if (totalFailuresCommitted > 0 || totalImagesCommitted > 0 || totalDeliveryCodeErrors > 0 || totalCommentsCommitted > 0)
                                    {
                                        theTrans.Commit();
                                        theResponse.statusCode = 6;
                                        theResponse.statusDescription = "Some of the data has been synchronized. " + totalFailureErrors + " Failures, " + totalDeliveryCodeErrors + " DeliveryCodes, " + totalImageErrors + " Images and " + totalCommentErrors + " Comments could not be synchronized";
                                    }
                                }
                            }
                            else
                            {
                                theResponse.statusCode = 6;
                                theResponse.statusDescription = "No stops were supplied with the trip model. No data was synchronized.";
                            }
                        }
                        else
                        {
                            theReader.Close();

                            theResponse.statusCode = 4;
                            theResponse.statusDescription = "Trip ID was invalid";
                        }

                    }
                }
                else
                {
                    theResponse.statusCode = 6;
                    theResponse.statusDescription = "Trip Model not supplied";
                }
            }
            catch (Exception ex)
            {
                //text = "Exception :" + ex.Message + "\n";
                //WriteToFile(text);

                theTrans.Rollback();
                theResponse.statusCode = 6;
                theResponse.statusDescription = "Unable to update data";
            }
            finally
            {
                theTrans = null;
                closeDataConnection();
            }

            //text = " End time   :" + DateTime.Now + "\n";
            //WriteToFile(text);

            return theResponse;
        }