using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using Neo.Legitimate.Common;
using Pilotfish.Afx.ComponentModel;
using Pilotfish.Afx.Security;
using Neo.Legitimate.Common.Helpers;
using System.Threading;
using System.Data.Entity;

namespace Neo.Legitimate.Data.Models
{
    public partial class LegitimateEntities
    {
        public LegitimateEntities(string connectionString)
            : base(connectionString)
        {
        }
    }

    public partial class LegitimateDatabase : Database<LegitimateEntities>
    {
        public LegitimateDatabase()
            : base(new LegitimateEntities())
        {

        }

        /// <summary>
        /// Constructor for use by Windows service
        /// </summary>
        /// <param name="connectionString"></param>
        public LegitimateDatabase(string connectionString)
            : base(new LegitimateEntities(connectionString))
        {

        }

        public void RouteStartVerificationWorkflow(long routeID)
        {
            Context.Pr_RouteStartVerificationWorkflow(routeID, null);
        }
                
        #region GetApplicationRequestDetail_ViewOnly
        public Vw_ApplicationRequestDetails GetApplicationRequestDetail_ViewOnly(long applicationRequestDeailID)
        {
            return Context.Vw_ApplicationRequestDetails.Where(a => a.ApplicationRequestDetailID == applicationRequestDeailID).FirstOrDefault();
        }
        #endregion

        #region GetLicenceIssueDetail_ViewOnly
        public Vw_LicenceIssueDetails GetLicenceIssueDetail_ViewOnly(long licenceIssueDetailID)
        {
            return Context.Vw_LicenceIssueDetails.Where(a => a.LicenceIssueDetailID == licenceIssueDetailID).FirstOrDefault();
        }
        #endregion

        #region GetLicenceUpliftmentExtensionDetail_ViewOnly
        public Vw_LicenceUpliftmentExtensionRequestDetails GetLicenceUpliftmentExtensionDetail_ViewOnly(long licenceUpliftmentExtensionRequestDetailID)
        {
            return Context.Vw_LicenceUpliftmentExtensionRequestDetails.Where(a => a.LicenceUpliftmentExtensionRequestDetailID == licenceUpliftmentExtensionRequestDetailID).FirstOrDefault();
        }
        #endregion
        
        #region GetApplicationRequestTypes
        public IEnumerable<ApplicationRequestType> GetApplicationRequestTypes(short workflowID)
        {
            var list = Context.ApplicationRequestTypes.Where(a => !a.IsCancelled.Value).ToList();
            
            foreach (var l in list)
            {
                l.ApplicationRequestTypeDocuments = Context.ApplicationRequestTypeDocuments.Include("DocumentTypes").Where(a => a.ApplicationRequestTypes.ApplicationRequestTypeID == l.ApplicationRequestTypeID && a.WorkflowID == workflowID).ToList();
            }

            return list;
        }
        #endregion

        #region GetApplicationRequestType
        public ApplicationRequestType GetApplicationRequestType(short applicationRequestTypeID, bool includeAll)
        {
            var requestType = Context.ApplicationRequestTypes.Where(a => a.ApplicationRequestTypeID == applicationRequestTypeID).FirstOrDefault();

            if (includeAll)
            {
                requestType.ApplicationRequestTypeDocuments = Context.ApplicationRequestTypeDocuments.Include("DocumentTypes").Where(a => a.ApplicationRequestTypes.ApplicationRequestTypeID == applicationRequestTypeID && !a.IsCancelled).ToList();
            }

            return requestType;
        }
        #endregion
                
        #region GetLicenceIssueDocuments
        public IEnumerable<SimpleDocument> GetLicenceIssueDocuments(long licenceIssueDetailID)
        {
            var licenceIssueDocuments = Context.LicenceIssueDocuments.Include("DocumentType").Where(a => a.LicenceIssueDetailID == licenceIssueDetailID).ToList();

            return (from document in licenceIssueDocuments
                    where document.DocumentID.HasValue
                    select new SimpleDocument(document.DocumentID.Value, document.DocumentType.Description, document.DocumentTypeID)).ToList();
        }
        #endregion

        #region GetInspectionDetail
        public LicenceIssueInspectionDetail GetInspectionDetail(long licenceIssueDetailID)
        {
            return Context.LicenceIssueInspectionDetails.Include("LicenceIssueDetail").Where(a => a.LicenceIssueDetailID == licenceIssueDetailID).FirstOrDefault();
        }
        #endregion

        #region GetInspectionOutcomes
        public IEnumerable<LicenceIssueInspectionOutcome> GetInspectionOutcomes()
        {
            return Context.LicenceIssueInspectionOutcomes.Where(a => !a.IsCancelled).ToList();
        }
        #endregion

        

        #region GetGazetteDetail
        public GazetteDetail GetGazetteDetail(long gazetteDetailID, long userID)
        {
            if (gazetteDetailID == -1)
            {
                var days = Convert.ToInt32(GetSettingValue("GazettePeriodDays"));

                return new GazetteDetail()
                {
                    GazetteDetailID = -1,
                    GazetteDetailStatusID = (short)GazetteDetailStatus.AwaitingGazetteFinalisation,
                    ProvinceID = (short)Common.Province.KwaZuluNatal,
                    GazettePrintDate = DateTime.Now,
                    GazetteNoticeExpiryDate = DateTime.Now.AddDays(days),
                    CreatedByID = userID,
                    CreatedDate = DateTime.Now,
                    UpdatedByID = userID,
                    UpdatedDate = DateTime.Now,
                    NumberOfApplications = 0
                };
            }

            var details = (from q in Context.GazetteDetails.Include("Status")
                           where q.GazetteDetailID == gazetteDetailID
                           select q).FirstOrDefault();

            if (details != null)
            {
                details.NumberOfApplications = GetGazetteApplications(gazetteDetailID).Count;
            }

            return details;
        }
        #endregion
        
        #region GetGazetteApplications
        public List<Vw_GazetteApplication> GetGazetteApplications(long gazetteDetailID)
        {
            return Context.Vw_GazetteApplications.Where(a => a.GazetteDetailID == gazetteDetailID).OrderBy(a => a.ApplicationNumber).ToList();
        }
        #endregion
        
        #region GetCurrentGazetteDetail
        public GazetteDetail GetCurrentGazetteDetail(long userID)
        {
            var detail = Context.GazetteDetails.Where(a => a.GazetteDetailStatusID == (short)GazetteDetailStatus.AwaitingGazetteFinalisation).FirstOrDefault();

            if (detail == null || detail.GazetteDetailID == -1)
            {
                detail = GetGazetteDetail(-1, userID);
            }
            else
            {
                detail.NumberOfApplications = GetGazetteApplications(detail.GazetteDetailID).Count;
            }

            return detail;
        }
        #endregion

        #region GetGazettesAtPrinter
        public IEnumerable<GazetteDetail> GetGazettesAtPrinter()
        {
            var list = Context.GazetteDetails.Where(a => a.GazetteDetailStatusID == (short)GazetteDetailStatus.AwaitingNoticePeriodExpiry).ToList();
            foreach (var item in list)
            {
                item.NumberOfApplications = GetGazetteApplications(item.GazetteDetailID).Count;
            }

            return list;
        }

        public IEnumerable<GazetteDetail> GetGazettesAwaitingPrinting()
        {
            var list = Context.GazetteDetails.Where(a => a.GazetteDetailStatusID == (short)GazetteDetailStatus.AwaitingGazettePrintDate).ToList();
            foreach (var item in list)
            {
                item.NumberOfApplications = GetGazetteApplications(item.GazetteDetailID).Count;
            }

            return list;
        }
        #endregion

        #region GetProvinces
        public IEnumerable<Province> GetProvinces()
        {
            return Context.Provinces.Where(a => !a.IsCancelled).OrderBy(a => a.Description).ToList();
        }
        #endregion

        #region GetRegions
        public IEnumerable<Region> GetRegions()
        {
            return Context.Regions.Where(a => !a.IsCancelled).OrderBy(a => a.Description).ToList();
        }       
        #endregion

        #region GetSourceApplications
        public IEnumerable<SourceApplication> GetSourceApplications()
        {
            return Context.SourceApplications.Where(a => !a.IsCancelled).OrderBy(a => a.Description).ToList();
        }
        #endregion

        #region GetPaymentTypes
        public IEnumerable<PaymentType> GetPaymentTypes()
        {
            return Context.PaymentTypes.Where(a => !a.IsCancelled).OrderBy(a => a.Description).ToList();
        }
        #endregion        

        #region GetVerifiedAssociations
        public IEnumerable<Vw_Associations> GetVerifiedAssociations()
        {
            return Context.Vw_Associations.ToList().OrderBy(a => a.AssociationName);
        }
        #endregion

        #region GetVerifiedPlaces
        public IEnumerable<Vw_Places> GetVerifiedPlaces()
        {
            return Context.Vw_Places.ToList().OrderBy(a => a.Description);
        }
        #endregion

        #region GetVerifiedPlaces
        public Vw_Places GetVerifiedPlace(long placeID)
        {
            return Context.Vw_Places.Where(a => a.PlaceID==placeID).FirstOrDefault();
        }
        #endregion

        #region GetPlaceTypes
        public List<PlaceType> GetPlaceTypes()
        {
            return Context.PlaceTypes.Where(a => !a.IsCancelled).OrderBy(a => a.Description).ToList();
        }
        #endregion

        #region GetVerifiedAssociation
        public Associations GetVerifiedAssociation(long associationID)
        {
            return Context.Associations.Include("Status").Include("Mode").Include("Type").Include("Region").Where(a => a.AssociationID == associationID).FirstOrDefault();
        }
        #endregion

        #region SearchVerifiedAssociations
        public IEnumerable<Pr_AssociationSearch_Result> SearchVerifiedAssociations(long? regionIDSearch, string associationNameSearch)
        {
            return Context.Pr_AssociationSearch(regionIDSearch, associationNameSearch);
        }
        #endregion

        #region GetVerifiedAssociationMembers
        public IEnumerable<VerifiedMember> GetVerifiedAssociationMembers(long associationID)
        {
            var allMembers = GetVerifiedMembers(true);
            var associationMembers = new List<VerifiedMember>();

            foreach (var member in allMembers)
            {
                if (member.Associations.Where(a => a.AssociationID == associationID).FirstOrDefault() != null)
                {
                    associationMembers.Add(member);
                }
            }

            return associationMembers;
        }
        #endregion

        #region GetVerifiedAssociationRoutes
        public List<Route> GetVerifiedAssociationRoutes(long associationID)
        {            
            return Context.Pr_AssociationRoutesGet(associationID).ToList();
        }
        #endregion

        #region GetVerifiedMembers
        public IEnumerable<VerifiedMember> GetVerifiedMembers(bool? includeAll = false)
        {
            if (includeAll.HasValue && includeAll.Value)
            {
                return Context.VerifiedMembers.Include("Associations").ToList();
            }

            return Context.VerifiedMembers.ToList();
        }
        #endregion

        #region SearchVerifiedRoutes
        public IEnumerable<Pr_MemberSearch_Result> SearchVerifiedMembers(string memberNameSearch, string memberIdSearch, string memberEmailSearch, long? assocationSearch)
        {
            return Context.Pr_MemberSearch(memberNameSearch, memberIdSearch, memberEmailSearch, assocationSearch);
        }
        #endregion

        #region GetVerifiedMember
        public VerifiedMember GetVerifiedMember(long memberID)
        {
            return Context.VerifiedMembers.Include("Status").Where(a => a.MemberID == memberID).FirstOrDefault();
        }

        public VerifiedMember GetVerifiedMember(string idNumber)
        {
            if (!string.IsNullOrEmpty(idNumber))
            {
                return Context.VerifiedMembers.Include("Status").Where(a => a.IDNumberOrBusinessRegistrationNo.ToLower() == idNumber.ToLower()).FirstOrDefault();
            }
            else
            {
                return null;
            }
        }
        #endregion
        
        #region GetVerifiedRoutes
        public IEnumerable<Route> GetVerifiedRoutes(bool includeDescriptions, bool? includeAll = false)
        {
            var list = new List<Route>();

            if (includeAll.HasValue && includeAll.Value)
            {
                list = Context.Routes.Include("Associations").Where(a => a.RouteTypeID != (short)RouteTypeEnum.TemporaryRoute && a.RouteStatusID != (short)RouteStatusEnum.Cancelled).ToList();
            }
            else
            {
                list = Context.Routes.Where(a => a.RouteTypeID != (short)RouteTypeEnum.TemporaryRoute && a.RouteStatusID != (short)RouteStatusEnum.Cancelled).ToList();
            }
            
            if (!includeDescriptions)
            {
                foreach (var i in list)
                {
                    i.Description = "";
                }
            }

            return list;
        }
        #endregion

        #region SearchVerifiedRoutes
        public IEnumerable<Pr_RouteSearch_Result> SearchVerifiedRoutes(long? originSearch, long? destinationSearch, long? associationSearch, string nationalRouteNumberSearch, string boardRouteNumberOriginSearch, bool? includeRoutesInVerification, long? routeStatusID, long? applicationRequestDetailID, long? applicationRequestTypeID)
        {
            return Context.Pr_RouteSearch(originSearch, destinationSearch, nationalRouteNumberSearch, boardRouteNumberOriginSearch, associationSearch, "", routeStatusID, includeRoutesInVerification, applicationRequestDetailID, applicationRequestTypeID);
        }
        #endregion

        #region SearchRoutes
        public IEnumerable<Pr_RouteSearch_Result> SearchRoutesByTextAndStatus(string description, int statusID, bool? includeRoutesInVerification)
        {
            return Context.Pr_RouteSearch(-1, -1, "", "", -1, description, statusID, includeRoutesInVerification, -1, -1);
        }
        #endregion

        #region GetVerifiedRoute
        public Route GetVerifiedRoute(long routeID)
        {
            return Context.Routes.Include("Status").Include("Origin").Include("Destination").FirstOrDefault(a => a.RouteID == routeID);
        }
        #endregion

        #region GetVerifiedRouteByNationalRouteNumber
        public Route GetVerifiedRouteByNationalRouteNumber(string nationalRouteNumber)
        {
            return Context.Routes.FirstOrDefault(a => a.NationalRouteNumber == nationalRouteNumber);
        }
        #endregion

        #region GetVerifiedRouteLicences
        public IEnumerable<Licence> GetVerifiedRouteLicences(long routeID)
        {
            var allLicences = GetVerifiedLicences(true);
            var routeLicences = new List<Licence>();

            foreach (var licence in allLicences)
            {
                if (licence.Routes.Where(a => a.RouteID == routeID).FirstOrDefault() != null)
                {
                    routeLicences.Add(licence);
                }
            }

            return routeLicences;
        }
        #endregion

        #region GetVerifiedRouteRouteNodes
        public IEnumerable<RouteNode> GetVerifiedRouteRouteNodes(long routeID)
        {
            return Context.RouteNodes.Include("Place").Where(a => a.RouteID == routeID).OrderBy(a => a.Sequence).ToList();
        }
        #endregion

        #region GetVerifiedLicenceStatusSummary
        public IEnumerable<Pr_LicenceStatusSummary_Result> GetVerifiedLicenceStatusSummary(long? regionID, long? associationID)
        {
            return Context.Pr_LicenceStatusSummary(regionID, associationID);
        }
        #endregion

        #region SearchVerifiedLicences
        public IEnumerable<Pr_LicenceSearch_Result> SearchVerifiedLicences(string operatingLicenceNumberSearch, long? memberIdSearch, long? licenceStatusIdSearch, string vechicleRegistrationNumberSearch, long? associationIdSearch, DateTime? issueDateStartDateSearch, DateTime? issueDateEndDateSearch, DateTime? expiryDateStartDateSearch, DateTime? expiryDateEndDateSearch)
        {
            return Context.Pr_LicenceSearch(operatingLicenceNumberSearch, memberIdSearch, licenceStatusIdSearch, vechicleRegistrationNumberSearch, associationIdSearch, issueDateStartDateSearch, issueDateEndDateSearch, expiryDateStartDateSearch, expiryDateEndDateSearch);
        }
        #endregion

        #region GetVerifiedLicences
        public IEnumerable<Licence> GetVerifiedLicences(bool? includeRoute = false)
        {
            if (includeRoute.HasValue && includeRoute.Value)
            {
                return Context.Licences.Include("Status").Include("Member").Include("Vehicle").Include("Routes").ToList();
            }

            return Context.Licences.Include("Status").Include("Member").Include("Vehicle").ToList();
        }

        public IEnumerable<Licence> GetVerifiedLicences(short status, bool? includeRoute = false)
        {
            if (includeRoute.HasValue && includeRoute.Value)
            {
                return Context.Licences.Include("Status").Include("Member").Include("Vehicle").Include("Routes").Where(a => a.LicenceStatusID == status).ToList();
            }

            return Context.Licences.Include("Status").Include("Member").Include("Vehicle").Where(a => a.LicenceStatusID == status).ToList();
        }
        #endregion

        #region GetLicencesOverdueUpliftment
        public List<Licence> GetLicencesOverdueUpliftment()
        {
            return Context.Pr_LicencesOverdueUpliftmentsGet().ToList();
        }
        #endregion

        #region GetLicencesUpcomingExpiries
        public List<Licence> GetLicencesUpcomingExpiries()
        {
            return Context.Pr_LicencesUpcomingExpiresGet().ToList();
        }
        #endregion

        #region GetLicencesExpiredInGracePeriod
        public List<Licence> GetLicencesExpiredInGracePeriod()
        {
            return Context.Pr_LicencesExpiredInGracePeriodGet().ToList();
        }
        #endregion

        #region GetVerifiedLicence
        public Licence GetVerifiedLicence(long licenceID)
        {
            return Context.Licences.Include("Status").Include("Member").Include("Mode").Include("LicenceServiceDetails").Include("LicenceServiceDetails.ServiceType").Include("LicenceExceptions").Include("Vehicle").Include("Vehicle.VehicleMake").Include("Vehicle.VehicleType").Where(a => a.LicenceID == licenceID).FirstOrDefault();
        }
        #endregion

        #region GetVerifiedLicenceServiceTypes
        public List<LicenceServiceDetail> GetVerifiedLicenceServiceDetails(long licenceID)
        {
            return Context.LicenceServiceDetails.Where(a => a.LicenceID == licenceID).ToList();
        }
        #endregion

        #region GetVerifiedLicenceRoutes
        public List<Pr_LicenceRoutesGet_Result> GetVerifiedLicenceRoutes(long licenceID)
        {
            return Context.Pr_LicenceRoutesGet(licenceID).ToList();
        }
        #endregion

        #region GetVerifiedOperatingLicence
        public Vw_OperatingLicenceDetails GetVerifiedOperatingLicence(long licenceID)
        {
            return Context.Vw_OperatingLicenceDetails.Where(a => a.LicenceID == licenceID).FirstOrDefault();
        }
        #endregion

        #region GetLicenceStatuses
        public IEnumerable<LicenceStatus> GetLicenceStatuses()
        {
            return Context.LicenceStatuses.Where(a => a.IsCancelled == false).OrderBy(a => a.Title).ToList();
        }
        #endregion

        #region GetLicencesByMember
        public IEnumerable<Vw_OperatingLicenceDetails> GetLicencesByMember(long MemberID)
        {
            var licences = Context.Vw_OperatingLicenceDetails.Where(a => a.MemberID == MemberID).OrderBy(a => a.OperatingLicenceNumber).ToList();
            return licences;
        }
        #endregion

        #region GetMemberApplications
        public IEnumerable<Vw_ApplicationRequestDetails> GetMemberApplications(VerifiedMember member)
        {
            return Context.Vw_ApplicationRequestDetails.Where(a => a.VerifiedMemberID == member.MemberID || a.IDNumberOrBusinessRegistrationNo == member.IDNumberOrBusinessRegistrationNo).OrderBy(a => a.ApplicationRequestDetailID).ToList();
        }
        #endregion

        #region GetMemberVehicles
        public IEnumerable<Vehicle> GetMemberVehicles(long memberID)
        {
            var vehicles = GetVerifiedVehicles(true);
            var memberVehicles = new List<Vehicle>();

            foreach (var vehicle in vehicles)
            {
                if (vehicle.Licences.Where(a => a.MemberID == memberID).FirstOrDefault() != null)
                {
                    memberVehicles.Add(vehicle);
                }
            }

            return memberVehicles;
        }
        #endregion

        #region GetVerifiedVehicles
        public IEnumerable<Vehicle> GetVerifiedVehicles(bool? includeAll = false)
        {
            if (includeAll.HasValue && includeAll.Value)
            {
                return Context.Vehicles.Include("VehicleMake").Include("Licences").ToList();
            }

            return Context.Vehicles.ToList();
        }
        #endregion

        #region GetVerifiedVehiclesView
        public IEnumerable<Vw_Vehicles> GetVerifiedVehiclesView()
        {
            return Context.Vw_Vehicles.ToList();
        }
        #endregion

        #region GetVerifiedVehiclesView (single)
        public Vw_Vehicles GetVerifiedVehiclesView(long vehicleID)
        {
            return Context.Vw_Vehicles.Where(a => a.VehicleID == vehicleID).FirstOrDefault();
        }
        #endregion

        #region GetVerifiedVehicle
        public Vehicle GetVerifiedVehicle(long verifiedVehicleID)
        {
            return Context.Vehicles.Where(a => a.VehicleID == verifiedVehicleID).FirstOrDefault();
        }
        #endregion        

        #region GazetteApplications
        public bool GazetteApplications(string applicationsToGazette, long userID, out string errorMessage)
        {
            var gazetteID = -1L;
            errorMessage = "";

            var db = Context.Database.Connection.CreateCommand();

            try
            {
                if (db.Connection.State != ConnectionState.Open)
                {
                    db.Connection.Open();
                }

                db.CommandText = "EXEC App.Pr_GazetteApplications @ApplicationsToGazette, @UserID";

                db.Parameters.Add(new SqlParameter { ParameterName = "ApplicationsToGazette", Value = applicationsToGazette });
                db.Parameters.Add(new SqlParameter { ParameterName = "UserID", Value = userID });

                var intFound = false;

                var reader = db.ExecuteReader();

                //find the first int in the result sets returned. This caters for multiple results sets which are returned in certain instances
                do
                {
                    while (reader.Read() && !intFound)
                    {
                        if (Int64.TryParse(reader[0].ToString(), out gazetteID))
                        {
                            intFound = true;
                        }
                    }
                } while (!intFound && reader.NextResult());
            }
            catch (Exception ex)
            {
                errorMessage = GetInnermostMessage(ex);                
            }
            finally
            {
                if (db.Connection.State == ConnectionState.Open)
                {
                    db.Connection.Close();
                }
            }

            return gazetteID > 0;
        }
        #endregion

        #region GazetteStatusUpdate
        public bool GazetteStatusUpdate(long gazetteDetailID, long userID, string comments, GazetteDetailStatus status, out string errorMessage)
        {
            errorMessage = "";

            try
            {
                Context.Pr_GazetteDetailStatusUpdate(userID, comments, gazetteDetailID, (short)status, true);
            }
            catch (Exception ex)
            {
                errorMessage = GetInnermostMessage(ex);                
            }

            return String.IsNullOrWhiteSpace(errorMessage);
        }
        #endregion
                                
        #region SaveGazetteDetail
        public GazetteDetail SaveGazetteDetail(GazetteDetail gazetteDetail, long userID, out string errorMessage)
        {
            var currentGazette = GetCurrentGazetteDetail(userID);

            errorMessage = "";

            if ((currentGazette.GazetteDetailID == -1 && gazetteDetail.GazetteDetailID == -1) || gazetteDetail.GazetteDetailID > 0)
            {
                gazetteDetail.UpdatedByID = userID;
                gazetteDetail.UpdatedDate = DateTime.Now;

                if (gazetteDetail.GazetteDetailID == -1)
                {
                    gazetteDetail.CreatedByID = userID;
                    gazetteDetail.CreatedDate = DateTime.Now;

                    gazetteDetail.GazetteNumber = Context.Pr_GazetteGetNumber().First();

                    Context.GazetteDetails.Add(gazetteDetail);
                }
                else
                {
                    Context.Entry(currentGazette).CurrentValues.SetValues(gazetteDetail);
                }

                try
                {
                    Context.SaveChanges();
                }
                catch (Exception ex)
                {
                    errorMessage = GetInnermostMessage(ex);                
                }

                currentGazette = gazetteDetail;
            }
            else
            {
                errorMessage = "Can not have more than 1 active Gazette at a time";
            }

            return currentGazette;
        }
        #endregion
        
        #region SaveInspectionDetail
        public bool SaveInspectionDetail(LicenceIssueInspectionDetail liid, long userID, out string errorMessage)
        {
            errorMessage = "";

            try
            {
                Context.Pr_LicenceIssueInspectionComplete(liid.LicenceIssueDetailID, liid.LicenceIssueInspectionOutcomeID, liid.InspectedByID, liid.DateInspected, liid.EngineNumber, liid.ChassisNumber, liid.InspectionSheetDocumentID, liid.FrontRegDocumentID, liid.ChassisDocumentID, liid.EngineDocumentID, liid.BackRegDocumentID, liid.Additional1DocumentID, liid.Additional2DocumentID, userID);
            }
            catch (Exception ex)
            {
                errorMessage = GetInnermostMessage(ex);                
            }

            return errorMessage.Length == 0;
        }
        #endregion

        #region UpdateOperatingLicence
        public Vw_OperatingLicenceDetails UpdateOperatingLicence(long licenceID, string documentNumber, bool isReprint, string reprintReason, long userID, out string errorMessage)
        {
            errorMessage = "";

            try
            {
                var ol = Context.Licences.Where(a => a.LicenceID == licenceID).FirstOrDefault();

                if (ol != null)
                {
                    var hasDuplicate = Context.Licences.Where(a => a.LicenceID != licenceID && a.CertificateNumber.ToLower() == documentNumber.ToLower()).FirstOrDefault();

                    if (hasDuplicate == null)
                    {
                        var exceptionOl = GetVerifiedOperatingLicence(licenceID);

                        if (exceptionOl != null && string.IsNullOrEmpty(exceptionOl.LicenceExceptions))
                        {
                            Context.Entry(ol).Entity.CertificateNumber = documentNumber;
                            Context.Entry(ol).State = System.Data.Entity.EntityState.Modified;
                            Context.SaveChanges();      
                  
                            Context.Pr_LicenceStatusUpdate(userID, "Licence printed with certificate number " + documentNumber, licenceID, 2 /*issued*/, false);
                        }

                        return GetVerifiedOperatingLicence(licenceID);
                    }
                    else
                    {
                        throw new Exception("The certificate number has already been used on licence " + hasDuplicate.OperatingLicenceNumber);
                    }
                }
                else
                {
                    throw new Exception("The given operating licence does not exist");
                }
            }
            catch (Exception ex)
            {
                errorMessage = GetInnermostMessage(ex);                
            }

            return null;
        }
        #endregion

        #region CompleteIssuing
        public bool CompleteIssuing(long licenceIssueDetailID, long userID, out string errorMessage)
        {
            errorMessage = "";

            try
            {
                Context.Pr_LicenceIssuingComplete(licenceIssueDetailID, userID);
            }
            catch (Exception ex)
            {
                errorMessage = GetInnermostMessage(ex);                
            }

            return errorMessage.Length == 0;
        }
        #endregion

        #region SearchApplicationRequestDetails
        /// <summary>
        /// Search the ApplicationRequestDetails table based on the specified criteria
        /// </summary>
        /// <param name="applicationRequestDetailID"></param>
        /// <param name="workflowInstanceStatusID">Limit the list to a particular workflow instance status</param>
        /// <param name="workflowTaskSteps">Comma delimited list of workflow task steps to return</param>
        /// <param name="includeActiveTasksOnly">Limits the result set to tasks assigned to the userID specified</param>
        /// <param name="userID">Used in conjunction with includeActiveTasksOnly</param>
        /// <returns></returns>
        public IEnumerable<Pr_ApplicationRequestDetailsSearch_Result> SearchApplicationRequestDetails(long applicationRequestDetailID, short workflowInstanceStatusID, string workflowTaskSteps, bool includeActiveTasksOnly, long userID)
        {
            return Context.Pr_ApplicationRequestDetailsSearch(applicationRequestDetailID, workflowInstanceStatusID, workflowTaskSteps, includeActiveTasksOnly, -1, false, userID).ToList();
        }
        #endregion

        #region SearchLicenceIssueDetails
        public List<Pr_LicenceIssueDetailsSearch_Result> SearchLicenceIssueDetails(long licenceIssueDetailID, short workflowInstanceStatusID, string workflowTaskSteps, bool includeActiveTasksOnly, long userID)
        {
            return Context.Pr_LicenceIssueDetailsSearch(licenceIssueDetailID, workflowInstanceStatusID, workflowTaskSteps, includeActiveTasksOnly, userID).ToList();
        }
        #endregion

        #region SendUpcomingSmsReminder
        public bool SendUpcomingSmsReminder(string licenceIdCsv, long userID, out string errorMessage)
        {
            errorMessage = "";

            try
            {
                Context.Pr_LicenceUpcomingExpiryCorrespondance(licenceIdCsv, userID);
            }
            catch (Exception ex)
            {
                errorMessage = GetInnermostMessage(ex);                
            }

            return errorMessage.Length == 0;
        }
        #endregion

        #region SendExpiredSmsReminder
        public bool SendExpiredSmsReminder(string licenceIdCsv, long userID, out string errorMessage)
        {
            errorMessage = "";

            try
            {
                Context.Pr_LicenceExpiredCorrespondance(licenceIdCsv, userID);
            }
            catch (Exception ex)
            {
                errorMessage = GetInnermostMessage(ex);                
            }

            return errorMessage.Length == 0;
        }
        #endregion

        #region ResetUserPassword
        /// <summary>
        /// Resets a use's password
        /// </summary>
        /// <param name="userName"></param>
        /// <param name="newPassword"></param>
        /// <returns></returns>
        public bool ResetUserPassword(long userID, string userName, string newPassword)
        {
            //todo move to membershipprovider
            var db = Context.Database.Connection.CreateCommand();

            try
            {
                if (db.Connection.State != ConnectionState.Open)
                {
                    db.Connection.Open();
                }

                db.CommandText = "EXEC [Security].[Pr_SaveUserPassword] @UpdatedByID, @UserID, @UserName, @AuthMode, @NewPassword, @IsPasswordReset";

                db.Parameters.Add(new SqlParameter { ParameterName = "UpdatedByID", Value = userID });
                db.Parameters.Add(new SqlParameter { ParameterName = "UserID", Value = userID });
                db.Parameters.Add(new SqlParameter { ParameterName = "UserName", Value = userName });
                db.Parameters.Add(new SqlParameter { ParameterName = "AuthMode", Value = "Forms" });
                db.Parameters.Add(new SqlParameter { ParameterName = "NewPassword", Value = newPassword });
                db.Parameters.Add(new SqlParameter { ParameterName = "IsPasswordReset", Value = 0 });
                db.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                return false;
            }
            finally
            {
                if (db.Connection.State == ConnectionState.Open)
                {
                    db.Connection.Close();
                }
            }

            return true;
        }
        #endregion

        #region GetPurificationExceptionsPending
        public IEnumerable<Vw_PurificationDetails> GetPurificationExceptionsPending(long AssociationID)
        {
            return Context.Pr_PurificationExceptionsPending(AssociationID).ToList();
        }
        #endregion

        #region GetPurificationPrechecksPending
        public IEnumerable<Vw_PurificationDetails> GetPurificationPrechecksPending(long AssociationID)
        {
            return Context.Pr_PurificationPrechecksPending(AssociationID).ToList();
        }
        #endregion

        #region GetPurificationWorkflowsInProgress
        public IEnumerable<Vw_PurificationDetails> GetPurificationWorkflowsInProgress(long AssociationID)
        {
            return Context.Pr_PurificationWorkflowsInProgress(AssociationID).ToList();
        }
        #endregion

        #region GetPurificationDetail
        public Vw_PurificationDetails GetPurificationDetail(long purificationDetailID)
        {
            return Context.Vw_PurificationDetails.Where(a => a.PurificationDetailID == purificationDetailID).FirstOrDefault();
        }
        #endregion

        #region RetryPurificationStart
        public void RetryPurificationStart(long purificationDetailID)
        {
            Context.Pr_PurificationDetailRetry(purificationDetailID);
        }
        #endregion

        #region PurificationStatusUpdate
        public bool PurificationStatusUpdate(long purificationDetailID, long userID, short purificationDetailStatus, string comments, out string errorMessage)
        {
            errorMessage = "";

            try
            {
                Context.Pr_PurificationDetailStatusUpdate(userID, comments, purificationDetailID, purificationDetailStatus, true);
            }
            catch (Exception ex)
            {
                errorMessage = GetInnermostMessage(ex);                
            }

            return String.IsNullOrWhiteSpace(errorMessage);
        }
        #endregion

        #region GetAssociationPurificationCounts
        public IEnumerable<Vw_AssociationsPurificationCount> GetAssociationPurificationCounts()
        {
            return Context.Vw_AssociationsPurificationCount.ToList();
        }
        #endregion

        #region DoAutoVerificationTask
        public int DoAutoVerificationTask(long userID, long taskID)
        {
            var result = 0;
            var tryActionCount = 0;

            TryAction:
            try
            {                
                result = Context.Database.SqlQuery<int>("EXEC App.Pr_ApplicationRequestAutoVerify @p0, @p1", userID, taskID).Single();
            }
            catch (Exception ex)
            {
                var message = GetInnermostMessage(ex);

                if (!String.IsNullOrEmpty(message) && message.ToLower().Contains("deadlock") && tryActionCount < 5)
                {
                    Random rnd = new Random();
                    Thread.Sleep(rnd.Next(2000, 4000));
                    tryActionCount++;
                    goto TryAction;
                }
                else {
                    throw ex;
                }
            }

             return result;
        }
        #endregion

        #region GetServiceTypes
        public IEnumerable<ServiceType> GetServiceTypes()
        {
            return (from it in Context.ServiceTypes
                    where !it.IsCancelled
                    select it);
        }
        #endregion

        #region GetRoutes
        public IEnumerable<Route> GetRoutes()
        {
            return (from it in Context.Routes select it);
        }
        #endregion

        #region GetModes
        public IEnumerable<Mode> GetModes()
        {
            return (from it in Context.Modes
                    where !it.IsCancelled
                    select it);
        }

        public Mode GetMode(short modeID)
        {
            return (from it in Context.Modes
                    where it.ModeID == modeID
                    select it).FirstOrDefault();
        }
        #endregion

        #region Write Audit Entry
        public void WriteAuditEntry(int userId, string source, string action, string description, bool isCritical, bool isException, string entity, long itemID)
        {
            Context.Pr_WriteEntry(userId, source, action, description, isCritical, isException, entity, itemID);
        }
        #endregion

        #region GetSettingValue
        public string GetSettingValue(string settingName)
        {
            return Context.Settings.Where(a => a.SettingName == settingName).FirstOrDefault().SettingValue;
        }
        #endregion

        #region UpdateAssociationOlas/NeoIDs
        public Boolean UpdateAssociationOlasNeo(long UpdatedByID ,long AssociationID ,long OlasID ,long NeoID ,bool IsTransactionRequired)
        {
            bool Success;

            try
            {
                Context.Pr_AssociationOlasNeoUpdate(UpdatedByID, AssociationID, OlasID, NeoID, IsTransactionRequired);
                Success = true;
            }
            catch(Exception e) {
                Success = false;
            }

            return Success;
        }
        #endregion

        #region GetNeoMembers
        public List<Pr_AssociationNeoMemberSearch_Result> GetNeoMembers(long neoAssociationID, long associationMemberDataID)
        {
            return Context.Pr_AssociationNeoMemberSearch(neoAssociationID, associationMemberDataID).ToList();
        } 
        #endregion

        #region GetNewRouteRequestWorkflows
        public IEnumerable<Vw_NewRouteRequestDetails> GetNewRouteRequestWorkflows(long verifiedRouteID)
        {
            return Context.Vw_NewRouteRequestDetails.Where(a => a.VerifiedRouteID == verifiedRouteID).ToList();
        }
        #endregion

        #region StartVerifiedRouteUpdateWorkflow
        public void StartVerifiedRouteUpdateWorkflow(long verifiedRouteID, long userID)
        {
            Context.Pr_RouteStartUpdateWorkflow(verifiedRouteID, userID);
        }
        #endregion

        #region BypassGazetteExpiry
        public Boolean BypassGazetteExpiry(long applicationRequestDetailID, string comments, long userID, out string errorMessage)
        {
            bool Success;
            errorMessage = "";

            try
            {
                Context.Pr_GazetteApplicationBypassExpiry(applicationRequestDetailID, comments, userID);
                Success = true;
            }
            catch (Exception ex)
            {
                Success = false;
                errorMessage = GetInnermostMessage(ex);                
            }

            return Success;
        }
        #endregion

        #region GetBypassedLicences
        public List<ApplicationAllowedExpiredLicence> GetBypassedLicences()
        {
            return Context.ApplicationAllowedExpiredLicences.Where(a => !a.IsUsed).OrderBy(a => a.OperatingLicenceNumber).ToList();
        }
        #endregion

        #region GetAllowedNewLicences
        public List<ApplicationAllowedNewLicence> GetAllowedNewLicences()
        {
            return Context.ApplicationAllowedNewLicences.Include("Association").OrderBy(a => a.Association.AssociationName).OrderBy(a => a.MemberName).ToList();
        }
        #endregion

        #region GetInnermostMessage
        public string GetInnermostMessage(Exception ex)
        {
            if (ex.InnerException == null)
            {
                return ex.Message;
            }
            else
            {
                return GetInnermostMessage(ex.InnerException);
            }
        } 
        #endregion
                        
        #region Purification_GetAppsWithMissingOrginatingLicences
        public List<Pr_Purification_GetAppsWithMissingOrginatingLicences_Result> Purification_GetAppsWithMissingOrginatingLicences()
        {
            return Context.Pr_Purification_GetAppsWithMissingOrginatingLicences().ToList();
        }
        #endregion

        #region Purification_UpdateAppsWithMissingOrginatingLicence
        public void Purification_UpdateAppsWithMissingOrginatingLicence(string applicationNumber, string originatingLicenceNumber, long userID)
        {
            Context.Pr_Purification_UpdateAppsWithMissingOrginatingLicence(applicationNumber, originatingLicenceNumber, userID);
        }
        #endregion

        #region Purification_UpdateBypassedExpiredLicence
        public void Purification_UpdateBypassedExpiredLicence(string operatingLicenceNumber, short issueNumber, string reason,string applicationNumber,long sourceApplicationID, long userID)
        {
            Context.Pr_Purification_UpdateBypassedExpiredLicence(operatingLicenceNumber, issueNumber, reason, applicationNumber, sourceApplicationID, userID);
        }
        #endregion

        #region Purification_InsertAllowedNewLicence
        public void Purification_InsertAllowedNewLicence(long applicationAllowedNewLicenceID, long associationID, string memberName, string idNumber, string registrationNumber, short applicationAllowedNewLicenceTypeID, string operatingLicenceNumber, string applicationReferenceNumber, long userID)
        {
            Context.Pr_Purification_InsertAllowedNewLicence(applicationAllowedNewLicenceID,memberName, idNumber, associationID, registrationNumber, applicationAllowedNewLicenceTypeID, operatingLicenceNumber, applicationReferenceNumber, userID);
        }
        #endregion

        #region GetVerifiedLicencesLinkedToActiveOLASGet
        public List<Pr_Purification_LicencesLinkedToActiveOLASGet_Result> GetVerifiedLicencesLinkedToActiveOLASGet()
        {
            return Context.Pr_Purification_LicencesLinkedToActiveOLASGet().ToList();
        }
        #endregion

        #region GetVerifiedLicenceRelatedIssues
        public List<Vw_OperatingLicenceDetails> GetVerifiedLicenceRelatedIssues(long licenceID)
        {
            return Context.Pr_LicenceSearchRelatedIssues(licenceID).ToList();
        }
        #endregion

        #region GetVerifiedLicenceRelatedApplications
        public List<Vw_ApplicationRequestDetails> GetVerifiedLicenceRelatedApplications(long licenceID)
        {
            return Context.Pr_LicenceSearchRelatedApplications(licenceID).ToList();
        }
        #endregion

        #region SearchVehicles
        public IEnumerable<Vehicle> SearchVehicles(string registrationNumber, string vinNumber)
        {
            return Context.Pr_VehicleSearch(registrationNumber, vinNumber);
        }
        #endregion

        #region SearchVehicleLinkedLicences
        public IEnumerable<Vw_OperatingLicenceDetails> SearchVehicleLinkedLicences(long vehicleID)
        {
            return Context.Pr_VehicleSearchLinkedLicences(vehicleID).ToList();
        }
        #endregion

        #region GetVerifiedLicenceIssueWorkflowDetails
        public Pr_LicenceIssueWorkflowGet_Result GetVerifiedLicenceIssueWorkflowDetails(long licenceID)
        {
            return Context.Pr_LicenceIssueWorkflowGet(licenceID).FirstOrDefault();
        }
        #endregion

        #region GetVerifiedLicenceApplicationWorkflowDetails
        public Pr_LicenceApplicationWorkflowGet_Result GetVerifiedLicenceApplicationWorkflowDetails(long licenceID)
        {
            return Context.Pr_LicenceApplicationWorkflowGet(licenceID).FirstOrDefault();
        }
        #endregion

        #region GetVerifiedLicenceWorkflowDocuments
        public List<Pr_LicenceWorkflowDocumentsGet_Result> GetVerifiedLicenceWorkflowDocuments(long licenceID)
        {
            return Context.Pr_LicenceWorkflowDocumentsGet(licenceID).ToList();
        }
        #endregion

        #region GetLicenceExceptionCounts
        public List<Pr_LicenceExceptionsCounts_Result> GetLicenceExceptionCounts()
        {
            return Context.Pr_LicenceExceptionsCounts().ToList();
        }
        #endregion

        #region GetLicencesWithException
        public List<Pr_LicencesWithExceptionGet_Result> GetLicencesWithException(string exception)
        {
            return Context.Pr_LicencesWithExceptionGet(exception).ToList();
        }
        #endregion

        #region GetVehicleTypes
        public List<VehicleType> GetVehicleTypes()
        {
            return Context.VehicleTypes.Where(a => !a.IsCancelled).OrderBy(a => a.Description).ToList();
        }
        #endregion

        #region GetVehicleCarryingCapacities
        public List<CarryingCapacity> GetVehicleCarryingCapacities()
        {
            return Context.CarryingCapacities.Where(a => !a.IsCancelled).OrderBy(a => a.Description).ToList();
        }
        #endregion

        #region GetVehicleMakes
        public List<VehicleMake> GetVehicleMakes()
        {
            return Context.VehicleMakes.Where(a => !a.IsCancelled).OrderBy(a => a.Description).ToList();
        }
        #endregion

        #region VerifiedVehicleUpdate
        public int VerifiedVehicleUpdate(Vehicle vehicle, bool forceUpdateIfExists, string reason, long userID)
        {
            var vehicleID = Context.Pr_VehicleUpsert(vehicle.VehicleID, vehicle.RegistrationNumber, vehicle.EngineNumber, vehicle.VIN, vehicle.SeatedCapacity, vehicle.StandingCapacity, vehicle.GrossMass, vehicle.VehicleMakeID, vehicle.VehicleTypeID, vehicle.CarryingCapacityID, vehicle.YearOfManufacture, vehicle.OlasID, vehicle.LtpsID, userID, reason, forceUpdateIfExists).FirstOrDefault().Value.ToInt32();

            return (vehicleID.HasValue) ? vehicleID.Value : -1;
        }
        #endregion

        #region VerifiedLicenceExceptionsUpdate
        public void VerifiedLicenceExceptionsUpdate(long licenceID)
        {
            Context.Pr_LicenceExceptionsUpdate(licenceID);
        }
        #endregion

        #region VerifiedMemberUpdate
        public void VerifiedMemberUpdate(VerifiedMember member, long userID)
        {
            VerifiedMemberUpdate(member.MemberID, member.SurnameOrCompanyName, member.FirstNames, member.PostalAddress, member.PostalAddressCode, member.PhysicalAddress, member.PhysicalAddressCode, member.TelPhone, member.TelCell, member.TelFax, member.Email, member.SARSIncomeTaxNumber, member.SARSReasonIfNoTaxNumber, member.TradeName,member.BusinessTypeID, userID);
        }

        public void VerifiedMemberUpdate(long? memberID, string surnameOrCompanyName, string firstnames, string postalAddress, string postallAddressCode, string physicalAddress, string physicalAddressCode, string telPhone, string telCell, string telFax, string email, string sARSIncomeTaxNumber, string sarsIncometaxReason, string tradeName,long? businessTypeID, long userID)
        {
            Context.Pr_MemberDetailsUpdate(memberID, surnameOrCompanyName, firstnames, postalAddress, postallAddressCode, physicalAddress, physicalAddressCode, telPhone, telCell, telFax, email, sARSIncomeTaxNumber, sarsIncometaxReason, tradeName,businessTypeID, userID);
        }
        #endregion

        #region RecalculateLicenceExceptions
        public void RecalculateLicenceExceptions(long licenceID)
        {
            Context.Pr_LicenceExceptionsUpdate(licenceID);
        }
        #endregion

        #region AddRoute
        public void AddRouteToVerifiedLicence(long licenceID, long routeid, long userID)
        {
            Context.Pr_LicenceRouteAdd(licenceID, routeid, userID);
        }
        #endregion

        #region RemoveRoute
        public void RemoveRouteFromVerifiedLicence(long licenceID, long routeid, long userID)
        {
            Context.Pr_LicenceRouteRemove(licenceID, routeid, userID);
        }
        #endregion

        #region GetApplicationsRequestStatusView
        public List<Pr_ApplicationRequestsStatusViewGet_Result> GetApplicationsRequestStatusView(long userID)
        {
            return Context.Pr_ApplicationRequestsStatusViewGet(userID).ToList();
        }
        #endregion

        #region GetLicenceIssueRequestStatusView
        public List<Pr_LicenceIssueRequestsStatusViewGet_Result> GetLicenceIssueRequestStatusView(long userID)
        {
            return Context.Pr_LicenceIssueRequestsStatusViewGet(userID).ToList();
        }
        #endregion

        #region GetNewRouteRequestStatusView
        public List<Pr_NewRouteStatusViewGet_Result> GetNewRouteStatusView(long userID)
        {
            return Context.Pr_NewRouteStatusViewGet(userID).ToList();
        }
        #endregion

        #region UpdateVerifiedPlace
        public void UpdateVerifiedPlace(long placeID, string description, short placeTypeID, long? olasID, long? LtpsID, long userID, string auditDescription, bool forceUpdateIfExists)
        {
            Context.Pr_PlaceUpsert(placeID, description, placeTypeID, olasID, LtpsID, userID, auditDescription, forceUpdateIfExists);
        }
        #endregion
        
        #region CheckIfvehicleIsInUse
        public void CheckIfvehicleIsInUse(string operatingLicenceNumber, string VIN)
        {
            Context.Pr_VehicleIsNotUsed(operatingLicenceNumber, VIN);
        }
        #endregion

        #region GetRouteAssociationSummary
        public List<Pr_RouteAssociationSummary_Result> GetRouteAssociationSummary(long routeID, long userID)
        {
            return Context.Pr_RouteAssociationSummary(routeID, userID).ToList();
        }
        #endregion

        #region GetApplicationVehicleDetailsInBulk
        public List<ApplicationVehicleDetailsInBulk> GetApplicationVehicleDetailsInBulk(long applicationRequestDetailID, long userID)
        {
            return Context.ApplicationVehicleDetailsInBulks.Where(a => a.ApplicationRequestDetailID == applicationRequestDetailID).ToList();
        }
        #endregion

        #region GetLicenceIssueDetailsReport
        public Pr_LicenceIssueDetaisReport_Result GetLicenceIssueDetailsReport(long licenceIssueDetailID, long userID)
        {
            return Context.Pr_LicenceIssueDetaisReport(licenceIssueDetailID, userID).FirstOrDefault();
        }
        #endregion

        #region DelinkVehicleButton
        public void DelinkVehicle(long licenceId, string reason, long userID)
        {
            Context.Pr_LicenceVehicleDelink(licenceId, reason, userID);
        }
        #endregion

        #region SendToPurificationButton
        public void SendToPurification(long routeId, long userID)
        {
            Context.Pr_RouteSendToPurification(routeId, userID);
        }
        #endregion

        #region SearchLicenceIssueTracking
        public List<Pr_LicenceIssueTrackingSearch_Result> SearchLicenceIssueTracking(long userID, long regionID)
        {
            return Context.Pr_LicenceIssueTrackingSearch(regionID, userID).ToList();
        }
        #endregion


        #region LicenceIssueTrackingFlag
        public bool LicenceIssueTrackingFlag(string licencesToBeFlaggedAsSent, int ThirtyLetterSent, int SixtyDaysLetterSent, int NinetyLetterSent, long userID, out string errorMessage)
        {
            var db = Context.Database.Connection.CreateCommand();
            var success = true;
            try
            {
                if (db.Connection.State != ConnectionState.Open)
                {
                    db.Connection.Open();
                }

                db.CommandText = "EXEC Verified.Pr_LicenceIssueTrackingFlag @licencesToBeFlaggedAsSent, @ThirtyLetterSent, @SixtyDaysLetterSent, @NinetyLetterSent, @UserID";

                db.Parameters.Add(new SqlParameter { ParameterName = "licencesToBeFlaggedAsSent", Value = licencesToBeFlaggedAsSent });
                db.Parameters.Add(new SqlParameter { ParameterName = "ThirtyLetterSent", Value = ThirtyLetterSent });
                db.Parameters.Add(new SqlParameter { ParameterName = "SixtyDaysLetterSent", Value = SixtyDaysLetterSent });
                db.Parameters.Add(new SqlParameter { ParameterName = "NinetyLetterSent", Value = NinetyLetterSent });
                db.Parameters.Add(new SqlParameter { ParameterName = "UserID", Value = userID });

                var reader = db.ExecuteReader();

                errorMessage = "";
            }
            catch (Exception ex)
            {
                errorMessage = GetInnermostMessage(ex);
                success = false;
            }
            finally
            {
                if (db.Connection.State == ConnectionState.Open)
                {
                    db.Connection.Close();
                }
            }

            return success;
        }
        #endregion

        #region LicenceExtendLetter
        public Boolean LicenceExtendLetter(long LicenceID, long userID)
        {
            bool Success;
            var errorMessage = "";

            try
            {
                Context.Pr_LicenceIssueTrackingExtend(LicenceID, userID);
                Success = true;
            }
            catch (Exception ex)
            {
                Success = false;
                errorMessage = GetInnermostMessage(ex);
            }

            return Success;
        }
        #endregion

        #region GetOutstandingExtensionTasks
        public List<Pr_ExtensionTasksGet_Result> GetOutstandingExtensionTasks(long userID)
        {
            return Context.Pr_ExtensionTasksGet(userID).ToList();
        }
        #endregion

        #region HandleVerificationErrors
        public void HandleVerificationErrors(long applicationRequestDetailID, long userID)
        {
            Context.Pr_ApplicationVerificationHandleError(applicationRequestDetailID, userID);
        }
        #endregion

        #region GetApplicationDocuments
        public IEnumerable<SimpleDocument> GetApplicationDocuments(long applicationRequestDetailID, bool includeAll = false)
        {
            var applicationDocuments = Context.ApplicationDocuments.Include("DocumentType").Where(a => a.ApplicationRequestDetailID == applicationRequestDetailID).ToList();

            return (from applicationDocument in applicationDocuments
                    where applicationDocument.DocumentID.HasValue || includeAll
                    select new SimpleDocument((applicationDocument.DocumentID.HasValue) ? applicationDocument.DocumentID.Value : -1, applicationDocument.DocumentType.Description, applicationDocument.DocumentTypeID)).ToList();
        }
        #endregion
        #region AddDocumentToApplication
        public void AddDocumentToApplication(long applicationrequest, long documentTypeID, long documentID, long userID)
        {
            Context.Pr_ApplicationAddDocument(applicationrequest, documentTypeID, documentID, userID);
        }
        #endregion
    }
}