using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using Neo.LegitimateLicences.Common;
using Neo.Afx.ComponentModel;
using Neo.Afx.Security;
using Neo.LegitimateLicences.Common.Helpers;
using System.Threading;
using System.Data.Entity;
using System.Data.Entity.Core.Objects;

namespace Neo.LegitimateLicences.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 List<Pr_AdvancedSearch_Result> Search(string searchTerm, long userID)
        {
            return Context.Pr_AdvancedSearch(searchTerm, userID).ToList();
        }

        public void RouteStartVerificationWorkflow(long routeID)
        {
            Context.Pr_RouteStartVerificationWorkflow(routeID, null);
        }
                
        #region GetApplicationDetail_ViewOnly
        public Vw_ApplicationDetails GetApplicationDetail_ViewOnly(long applicationRequestDeailID)
        {
            return Context.Vw_ApplicationDetails.Where(a => a.ApplicationDetailID == applicationRequestDeailID).FirstOrDefault();
        }

        public Vw_ApplicationDetails GetApplicationLicenceDetail_ViewOnly(long licenceIssueDetailID)
        {
            var licenseIssueDetails = (from q in Context.LicenceIssueDetails
                                       where q.LicenceIssueDetailID == licenceIssueDetailID
                                       select q).FirstOrDefault();

            return Context.Vw_ApplicationDetails.Where(a => a.ApplicationDetailID == licenseIssueDetails.ApplicationDetailID).FirstOrDefault();
        }

        public Vw_ApplicationDetails GetApplicationDetail_ViewOnly(string applicationNumber)
        {
            return Context.Vw_ApplicationDetails.Where(a => a.ApplicationNumber == applicationNumber).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 GetApplicationTypes
        ////public List<ApplicationType> GetApplicationTypes(short workflowID)
        ////{
        ////    var list = Context.ApplicationTypes.Where(a => !a.IsCancelled.Value).ToList();
            
        ////    foreach (var l in list)
        ////    {
        ////        l.ApplicationTypeDocuments = Context.ApplicationTypeDocuments.Include("DocumentTypes").Where(a => a.ApplicationTypes.ApplicationTypeID == l.ApplicationTypeID && a.WorkflowID == workflowID).ToList();
        ////    }

        ////    return list;
        ////}
        ////#endregion

        ////#region GetApplicationType
        ////public ApplicationType GetApplicationType(short applicationRequestTypeID, bool includeAll)
        ////{
        ////    var requestType = Context.ApplicationTypes.Where(a => a.ApplicationTypeID == applicationRequestTypeID).FirstOrDefault();

        ////    if (includeAll)
        ////    {
        ////        requestType.ApplicationTypeDocuments = Context.ApplicationTypeDocuments.Include("DocumentTypes").Where(a => a.ApplicationTypes.ApplicationTypeID == 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)GazetteDetailStatusEnum.AwaitingGazetteFinalisation,
                    ProvinceID = (short)Common.ProvinceEnum.KwaZuluNatal,
                    GazettePrintDate = DateTime.Now,
                    GazetteNoticeExpiryDate = DateTime.Now.AddDays(days),
                    CreatedByID = userID,
                    CreatedDate = DateTime.Now,
                    UpdatedByID = userID,
                    UpdatedDate = DateTime.Now,                   
                    LinkedApplications = new List<Vw_GazetteApplication>()
                };
            }

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

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

            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(short? gazetteTypeID, short divisionTypeID, short provinceID, long userID)
        {
            var detail = Context.GazetteDetails.Where(a => a.GazetteDetailStatusID == (short)GazetteDetailStatusEnum.AwaitingGazetteFinalisation && a.GazetteTypeID == gazetteTypeID && a.DivisionTypeID == divisionTypeID && a.ProvinceID == provinceID).FirstOrDefault();

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

            return detail;
        }
        #endregion

        #region GetGazettesAtPrinter
        public List<GazetteDetail> GetGazetteArchive(int provinceId)
        {            
            return Context.GazetteDetails.Include("GazetteType").Where(a => a.GazetteDetailStatusID == (short)GazetteDetailStatusEnum.Finalised && a.ProvinceID == provinceId).ToList();
        }

        public List<GazetteDetail> GetGazettesAtPrinter(int provinceId)
        {            
            return Context.GazetteDetails.Include("GazetteType").Where(a => a.GazetteDetailStatusID == (short)GazetteDetailStatusEnum.AwaitingNoticePeriodExpiry && a.ProvinceID == provinceId).ToList().OrderByDescending(a =>a.GazetteNoticeExpiryDate).ToList();
        }

        public List<GazetteDetail> GetGazettesAwaitingPrinting(int provinceId)
        {                        
            return Context.GazetteDetails.Include("GazetteType").Where(a => a.GazetteDetailStatusID == (short)GazetteDetailStatusEnum.AwaitingGazettePrintDate && a.ProvinceID == provinceId).ToList();
        }
        #endregion

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

        #region GetDivisionTypes
        public List<DivisionType> GetDivisionTypes()
        {
            return Context.DivisionTypes.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, long userID)
        {
            return Context.Pr_AssociationSearch(regionIDSearch, associationNameSearch, userID);
        }
        #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, long userID)
        {            
            return Context.Pr_AssociationRoutesGet(associationID, userID).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, long userID)
        {
            return Context.Pr_MemberSearch(memberNameSearch, memberIdSearch, memberEmailSearch, assocationSearch, userID);
        }
        #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 List<Pr_RouteSearch_Result> SearchRoutesByTextAndStatus(string description, int statusID, bool? includeRoutesInVerification, long? associationID)
        {
            return Context.Pr_RouteSearch(-1, -1, "", "", associationID, description, statusID, includeRoutesInVerification, -1, -1).ToList();
        }
        #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 routeLicences = new List<Licence>();

            var licenceRouteIds = Context.LicenceRoutes.AsNoTracking().Where(x => x.RouteID == routeID).ToList().Select(x => x.LicenceID);

            var licences = Context.Licences.AsNoTracking().Include(x => x.Status).Include(x => x.Member).Where(x => licenceRouteIds.Contains(x.LicenceID)).ToList();

            foreach (Licence licence in licences)
                licence.LicenceRoutes = null;

            routeLicences.AddRange(licences);

            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, long userID)
        {
            return Context.Pr_LicenceStatusSummary(regionID, associationID, userID);
        }
        #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, long userID)
        {
            return Context.Pr_LicenceSearch(operatingLicenceNumberSearch, memberIdSearch, licenceStatusIdSearch, vechicleRegistrationNumberSearch, associationIdSearch, issueDateStartDateSearch, issueDateEndDateSearch, expiryDateStartDateSearch, expiryDateEndDateSearch, userID);
        }
        #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(long userID)
        {
            return Context.Pr_LicencesOverdueUpliftmentsGet(userID).ToList();
        }
        #endregion

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

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

        #region GetVerifiedLicence
        public Licence GetVerifiedLicence(long licenceID)
        {
            return Context.Licences.Include("Status").Include("Member").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, long userID)
        {
            return Context.Pr_LicenceRoutesGet(licenceID, userID).ToList();
        }
        #endregion

        #region GetVerifiedLicenceByMember
        public List<Licence> GetVerifiedLicenceByMember(long MemberID)
        {
            var result = Context.Licences.Where( a => a.MemberID == MemberID);

            return result.ToList();
        }
        #endregion

        #region GetVerifiedOperatingLicence
        public Vw_OperatingLicenceDetails GetVerifiedOperatingLicence(long licenceID, string certificateNumber = null)
        {
            var licence = Context.Vw_OperatingLicenceDetails.Where(a => a.LicenceID == licenceID).FirstOrDefault();
            
            // If a certificate number is provided and the licence exists, override the certificate number
            // This ensures the certificate number is available on the first print
            if (licence != null && !string.IsNullOrEmpty(certificateNumber))
            {
                licence.CertificateNumber = certificateNumber;
            }
            
            return licence;
        }
        #endregion

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

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

        #region GetAccreditationsByMember
        public List<Accreditation> GetAccreditationsByMember(long MemberID)
        {
            var accreditations = Context.Accreditations
                .Include("AccreditationStatu")
                .Where(a => a.MemberID == MemberID).OrderBy(a => a.AccreditationNumber).ToList();
            return accreditations;
        }
        #endregion

        #region GetLicencesByAccreditation
        public List<Licence> GetLicencesByAccreditation(long AccreditationID)
        {
            var licences = Context.Licences.Include("Status").Include("Member").Include("LicenceServiceDetails").Include("LicenceServiceDetails.ServiceType").Include("LicenceExceptions").Include("Vehicle").Include("Vehicle.VehicleMake").Include("Vehicle.VehicleType")
                .Where(a => a.AccreditationID == AccreditationID).OrderBy(a => a.OperatingLicenceNumber).ToList();
            return licences;
        }
        #endregion

        #region GetMemberApplications
        public List<Vw_ApplicationDetails> GetMemberApplications(long memberID)
        {
            return Context.Vw_ApplicationDetails.Where(a => a.VerifiedMemberID == memberID).OrderBy(a => a.ApplicationDetailID).ToList();
        }
        #endregion

        #region GetMemberVehicles
        public List<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, GazetteDetailStatusEnum 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 SetGazetteNoticeExpiryForPublish
        /// <summary>
        /// Sets GazetteNoticeExpiryDate (and GazettePrintDate if null) after status moves to AwaitingNoticePeriodExpiry.
        /// 14 days only for NPTR division when gazette contains e-hailing (60, 61, 62); PRE and all other cases use 21 (GazettePeriodDays).
        /// Run DB script for GazettePeriodDaysEhailing before deploy.
        /// </summary>
        public void SetGazetteNoticeExpiryForPublish(long gazetteDetailID)
        {
            var gazette = Context.GazetteDetails.Where(g => g.GazetteDetailID == gazetteDetailID).FirstOrDefault();
            if (gazette == null) return;

            var applications = GetGazetteApplications(gazetteDetailID);
            var hasEhailing = applications.Any(a => a.ApplicationRequestTypeID == (short)ApplicationRequestTypeEnum.NPTR_EhailingProviderRegistration
                || a.ApplicationRequestTypeID == (short)ApplicationRequestTypeEnum.NPTR_EhailingProviderRegistrationRenewal
                || a.ApplicationRequestTypeID == (short)ApplicationRequestTypeEnum.NPTR_EhailingProviderRegistrationAmendConditions);

            // 14-day notice only for NPTR division with e-hailing; PRE and others always use default 21 days
            var isNptrWithEhailing = gazette.DivisionTypeID == (short)DivisionTypeEnum.NPTR && hasEhailing;
            int days;
            if (isNptrWithEhailing)
            {
                try
                {
                    var val = GetSettingValue("GazettePeriodDaysEhailing");
                    days = (val != null && int.TryParse(val, out var d)) ? d : 14;
                }
                catch
                {
                    days = 14;
                }
            }
            else
            {
                days = Convert.ToInt32(GetSettingValue("GazettePeriodDays"));
            }

            var printDate = gazette.GazettePrintDate ?? DateTime.Now;
            if (!gazette.GazettePrintDate.HasValue)
                gazette.GazettePrintDate = printDate;
            gazette.GazetteNoticeExpiryDate = printDate.AddDays(days);
            Context.SaveChanges();
        }
        #endregion
                                
        #region SaveGazetteDetail
        public GazetteDetail SaveGazetteDetail(GazetteDetail gazetteDetail, long userID, out string errorMessage)
        {
            var database = new LegitimateDatabase();
            var officeLocation = database.GetOfficeLocations(userID).FirstOrDefault();
            var divisionTypeID = database.GetOfficeLocation(officeLocation.OfficeLocationID).DivisionTypeID;
            var provinceID = database.GetOfficeLocation(officeLocation.OfficeLocationID).ProvinceID;
            var currentGazette = GetCurrentGazetteDetail(gazetteDetail.GazetteTypeID, divisionTypeID, provinceID, 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(gazetteDetail.GazetteTypeID, gazetteDetail.DivisionTypeID, gazetteDetail.ProvinceID).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)
                    {
                        // First print: DateOfIssue is stamped at this moment so the printed
                        // licence shows the actual print date. DateOfExpiry must already be set
                        // (clerk picks it on the Licence Issuing Details screen) — if not,
                        // block the print rather than silently fall back to a default.
                        var isFirstPrint = !ol.DateOfIssue.HasValue;
                        if (isFirstPrint && !ol.DateOfExpiry.HasValue)
                        {
                            throw new Exception("Please capture a Date of Expiry on the Licence Issuing Details screen before printing.");
                        }

                        var exceptionOl = GetVerifiedOperatingLicence(licenceID);

                        if (exceptionOl != null && string.IsNullOrEmpty(exceptionOl.LicenceExceptions))
                        {
                            Context.Entry(ol).Entity.CertificateNumber = documentNumber;
                            if (isFirstPrint)
                            {
                                Context.Entry(ol).Entity.DateOfIssue = DateTime.Now;
                            }
                            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 StampDateOfIssueForFirstPrint
        // Stamps Verified.Licences.DateOfIssue = GETDATE() when a licence is being
        // printed for the first time. Called from StartPrintout (BEFORE the printout
        // data is fetched), so the printed paper renders the new DateOfIssue.
        // Idempotent — the SQL guard `WHERE DateOfIssue IS NULL` makes retries safe.
        // Blocks the print when DateOfExpiry hasn't been captured by the clerk.
        public bool StampDateOfIssueForFirstPrint(long licenceID, out string errorMessage)
        {
            errorMessage = "";
            try
            {
                var ol = Context.Licences.Where(a => a.LicenceID == licenceID).FirstOrDefault();
                if (ol == null)
                {
                    errorMessage = "The given operating licence does not exist";
                    return false;
                }

                // Reprint / second-print path: DateOfIssue already set — no-op.
                if (ol.DateOfIssue.HasValue)
                {
                    return true;
                }

                if (!ol.DateOfExpiry.HasValue)
                {
                    errorMessage = "Please capture a Date of Expiry on the Licence Issuing Details screen before printing.";
                    return false;
                }

                Context.Entry(ol).Entity.DateOfIssue = DateTime.Now;
                Context.Entry(ol).State = System.Data.Entity.EntityState.Modified;
                Context.SaveChanges();
                return true;
            }
            catch (Exception ex)
            {
                errorMessage = GetInnermostMessage(ex);
                return false;
            }
        }
        #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 SearchApplicationDetails
        /// <summary>
        /// Search the ApplicationDetails table based on the specified criteria
        /// </summary>
        public List<Vw_ApplicationDetails_Light> SearchApplicationDetails(string applicationRequestTypes, bool includeWorkflowsAwaitingInfoOnly, bool includeActiveWorkflowsOnly, bool includeActiveTasksOnly, short? applicationStatusID, short? applicationSubStatusID, string steps, long userID)
        {
            return Context.Pr_ApplicationSearch(applicationRequestTypes, includeWorkflowsAwaitingInfoOnly, includeActiveWorkflowsOnly, includeActiveTasksOnly, applicationStatusID, applicationSubStatusID, steps, userID).OrderByDescending(a=>a.CreatedDate).ToList();
        }

        //public IEnumerable<Pr_ApplicationDetailsSearch_Result> SearchApplicationDetails(long applicationRequestDetailID, short workflowInstanceStatusID, string workflowTaskSteps, bool includeActiveTasksOnly, long userID)
        //{
        //    return Context.Pr_ApplicationDetailsSearch(applicationRequestDetailID, workflowInstanceStatusID, workflowTaskSteps, includeActiveTasksOnly, -1, false, userID).ToList();
        //}
        #endregion

        #region SearchApplicationDetails
        /// <summary>
        /// Search the ApplicationDetails table based on the specified criteria
        /// </summary>
        public List<Pr_ApplicationAnalyticsSearch_Result> SearchApplicationAnalyticsDetails(DateTime? fromDate, DateTime? toDate, string applicationRequestTypes, string createdBy, long? regionID, long userID)
        {
            return Context.Pr_ApplicationAnalyticsSearch(fromDate, toDate, applicationRequestTypes, createdBy, regionID, userID).ToList();
        }
        #endregion

        #region UpdateAccreditation
        public Accreditation UpdateAccreditation(long accreditationID, string documentNumber, bool isReprint, string reprintReason, long userID, out string errorMessage)
        {
            errorMessage = "";

            try
            {
                var acc = Context.Accreditations.Where(a => a.AccreditationID == accreditationID).FirstOrDefault();

                if (acc != null)
                {
                    var hasDuplicate = Context.Accreditations.Where(a => a.AccreditationID != accreditationID && a.CertificateNumber.ToLower() == documentNumber.ToLower()).FirstOrDefault();

                    if (hasDuplicate == null)
                    {
                        //var exceptionOl = GetVerifiedOperatingLicence(accreditationID);

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

                        return GetVerifiedAccreditation(accreditationID);
                    }
                    else
                    {
                        throw new Exception("The certificate number has already been used on licence " + hasDuplicate.AccreditationNumber);
                    }
                }                
            }
            catch (Exception ex)
            {
                errorMessage = GetInnermostMessage(ex);                
            }

            return null;
        }
        #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
            {
                return false;
            }
            finally
            {
                if (db.Connection.State == ConnectionState.Open)
                {
                    db.Connection.Close();
                }
            }

            return true;
        }
        #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_ApplicationAutoVerify @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
            {
                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 GetApplicationAllowedNewLicences
        public List<Pr_GetApplicationAllowedNewLicences_Result> GetApplicationAllowedNewLicences(long userID)
        {
            return Context.Pr_GetApplicationAllowedNewLicences(userID).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(long userID)
        {
            return Context.Pr_Purification_GetAppsWithMissingOrginatingLicences(userID).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(long userID)
        {
            return Context.Pr_Purification_LicencesLinkedToActiveOLASGet(userID).ToList();
        }
        #endregion

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

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

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

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

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

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

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

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

        #region GetLicencesWithException
        public List<Pr_LicencesWithExceptionGet_Result> GetLicencesWithException(string exception, long userID)
        {
            return Context.Pr_LicencesWithExceptionGet(exception, userID).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 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.VehicleModelID, vehicle.VehicleTypeID, vehicle.CarryingCapacityID, vehicle.YearOfManufacture, vehicle.Tare, vehicle.CurrentOdometerReading, 
                    vehicle.RoadworthyCertificateDocID, vehicle.ProfessionalDriversPermitDocID, vehicle.RankPermitDocID, vehicle.CertificateOfRegistrationDocID, vehicle.CertifiedServiceRecordsDocID,
                    vehicle.CertifiedPublicPassangerLiabilityDocID, 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.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 tradeName,long? businessTypeID, long userID)
        {
            Context.Pr_MemberDetailsUpdate(memberID, surnameOrCompanyName, firstnames, postalAddress, postallAddressCode, physicalAddress, physicalAddressCode, telPhone, telCell, telFax, email, sARSIncomeTaxNumber, 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, short serviceTypeID, long userID)
        {
            Context.Pr_LicenceRouteAdd(licenceID, routeid, serviceTypeID, userID);
        }
        #endregion

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

        #region GetApplicationsRequestStatusView
        public List<Pr_ApplicationsStatusViewGet_Result> GetApplicationsRequestStatusView(long userID)
        {
            return Context.Pr_ApplicationsStatusViewGet(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, long userID)
        {
            Context.Pr_VehicleIsNotUsed(operatingLicenceNumber, VIN, userID);
        }
        #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.ApplicationDetailID == 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)
        {
            return Context.Pr_LicenceIssueTrackingSearch(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 AddDocumentToApplication
        public void AddDocumentToApplication(long applicationrequest, long documentTypeID, long documentID, long userID)
        {
            Context.Pr_ApplicationAddDocument(applicationrequest, documentTypeID, documentID, userID);
        }
        #endregion

        #region GetDocumentTypes
        public List<DocumentTypes> GetDocumentTypes()
        {
            var documentTypes = (from q in Context.DocumentTypes
                           .Include("DocumentTypeQuestions")
                                 where !q.IsCancelled
                                 && q.DocumentTypeID > 999
                                 select q).OrderBy(o => o.Description).ToList();
            return documentTypes;
        }
        #endregion

        #region GetDocumentType
        public DocumentTypes GetDocumentType(long DocumentTypeID)
        {
            var documentType = (from q in Context.DocumentTypes
                           .Include("DocumentTypeQuestions")
                                 where q.DocumentTypeID == DocumentTypeID
                                where !q.IsCancelled
                                select q).FirstOrDefault();
            if (documentType == null)
            {
                documentType = new DocumentTypes();
            }
            else
            {
                if (documentType.DocumentTypeQuestions != null)
                {
                    documentType.DocumentTypeQuestions = documentType.DocumentTypeQuestions.Where(a => !a.IsCancelled).OrderBy(o => o.OrderBy).ToList();
                }
            }

            return documentType;
        }
        #endregion

        #region UpsertDocumentType
        public long UpsertDocumentType(DocumentTypes documentType, long userID)
        {
            return Context.Pr_DocumentTypeUpsert(documentType.DocumentTypeID, documentType.Description, documentType.IsDocumentTitleEditable, documentType.IsExpiryDateRequired, documentType.IsUniqueEntityDocument, userID).FirstOrDefault().Value;
        }
        #endregion

        #region UpsertDocumentTypeQuestion
        public void UpsertDocumentTypeQuestion(DocumentTypeQuestion documentTypeQuestion, long userID)
        {
            Context.Pr_DocumentTypeQuestionUpsert(documentTypeQuestion.DocumentTypeQuestionID, documentTypeQuestion.DocumentTypeID, documentTypeQuestion.CaptureFieldID, documentTypeQuestion.Question, documentTypeQuestion.OrderBy, userID);
        }
        #endregion

        #region CancelDocumentTypeQuestions
        public void CancelDocumentTypeQuestions(string documentTypeQuestionIDs, long documentTypeID, long userID)
        {
            Context.Pr_DocumentTypeQuestionCancel(documentTypeQuestionIDs, documentTypeID, userID);
        }
        #endregion

        #region CancelDocumentType
        public void CancelDocumentType(long documentTypeID, long userID)
        {
            Context.Pr_DocumentTypeCancel(documentTypeID, userID);
        }
        #endregion        

        #region GetAppDocumentsAwaitingVerification
        public List<Pr_AppDocumentsAwaitingVerificationGet_Result> GetAppDocumentsAwaitingVerification(long entityID, long itemID)
        {
            return Context.Pr_AppDocumentsAwaitingVerificationGet(entityID, itemID).ToList();
        }
        #endregion


        #region GetApplicationLicenceTemporaryReplacement
        public Pr_ApplicationLicenceTemporaryReplacementGet_Result GetApplicationLicenceTemporaryReplacement(long applicationLicenceID, long userID)
        {
            return Context.Pr_ApplicationLicenceTemporaryReplacementGet(applicationLicenceID, userID).FirstOrDefault();
        }
        #endregion

        #region GetOfficeLocations
        public List<Vw_OfficeLocation> GetOfficeLocations()
        {
            var officeLocations = (from q in Context.Vw_OfficeLocations
                           //.Include("OfficeLocationSetupDocumentTemplates")                                                            
                                 select q).OrderBy(o => o.LocationName).ToList();
            return officeLocations;
        }
        #endregion

        #region GetOfficeLocations
        public Vw_OfficeLocationSetup GetOfficeLocation(long officeLocationID)
        {
            var officeLocationSetups = (from q in Context.Vw_OfficeLocationSetup
                                   where q.OfficeLocationID == officeLocationID
                                   select q).OrderBy(o => o.LocationName).FirstOrDefault();
            return officeLocationSetups;
        }
        #endregion

        #region GetOfficeLocationSetupDocumentTemplate
        public OfficeLocationSetupDocumentTemplate GetOfficeLocationSetupDocumentTemplate(long officeLocationID)
        {
            var officeLocationSetupDocumentTemplates = (from q in Context.OfficeLocationSetupDocumentTemplates
                                        where q.OfficeLocationID == officeLocationID
                                        select q).FirstOrDefault();

            if (officeLocationSetupDocumentTemplates == null)
            {
                officeLocationSetupDocumentTemplates = new OfficeLocationSetupDocumentTemplate();
            }
            return officeLocationSetupDocumentTemplates;
        }
        #endregion

        #region UpsertOfficeLocationSetupDocumentTemplate
        public void UpsertOfficeLocationSetupDocumentTemplate(long officeLocationID, OfficeLocationSetupDocumentTemplate officeDoc, long userID)
        {
            Context.Pr_OfficeLocationSetupDocumentTemplateUpsert(officeLocationID
                , officeDoc.PreAdjudicationLetterDocumentID
                , officeDoc.PreAdjudicationObjectionLetterDocumentID
                , officeDoc.PostAdjudicationLetterApprovedDocumentID
                , officeDoc.PostAdjudicationLetterPostponedDocumentID
                , officeDoc.PostAdjudicationLetterRefusedDocumentID
                , officeDoc.PostAdjudicationLetterCancelledDocumentID
                , officeDoc.PostAdjudicationLetterWithdrawnDocumentID
                , officeDoc.PostAdjudicationLetterStruckOffDocumentID
                , officeDoc.LicenceAnnexure_AdjudicationPanelDocumentID             
                , officeDoc.TATConfirmationReceipt_DocumentID
                , userID);
        }
        #endregion

        #region UpsertOfficeLocationSetupDocumentTemplate
        public void UpsertOfficeLocationSetup(long officeLocationID, OfficeLocationSetup officeLocationSetup, long userID)
        {
           Context.Pr_OfficeLocationSetupUpsert(officeLocationID
                , officeLocationSetup.LocationName
                , officeLocationSetup.LocationAddress
                , officeLocationSetup.DivisionTypeID
                , officeLocationSetup.ProvinceID
                , officeLocationSetup.RegionID
                , officeLocationSetup.LicencingBoardID
                , officeLocationSetup.PaymentClerkUserID
                , officeLocationSetup.AssessorUserID
                , officeLocationSetup.SeniorManagerApproverUserID
                , officeLocationSetup.InspectionCapturerUserID
                , officeLocationSetup.ReferralCapturerUserID
                , officeLocationSetup.HandlesGazetting
                , officeLocationSetup.GazetteNumberPrefix
                , officeLocationSetup.AdjudicationOfficerUserID
                , officeLocationSetup.AdjudicationSecrataryUserID
                , officeLocationSetup.AdjudicationAgendaEmails
                , officeLocationSetup.AdjudicationHearingQuorumCount
                , officeLocationSetup.AdjudicationMeetingQuorumCount
                , officeLocationSetup.MaxAdjudicationAutoApplicationCount
                , officeLocationSetup.BusinessRuleBypassApproverUserID
                , officeLocationSetup.AdjudicationContactName
                , officeLocationSetup.AdjudicationContactTelPhone
                , officeLocationSetup.AdjudicationContactEmail
                ,officeLocationSetup.RouteCapturerUserID
                ,officeLocationSetup.RouteApproverUserID
                , userID);
        }
        #endregion


        #region GetSystemSetting
        public string GetSystemSetting(string settingName)
        {
            return Context.Pr_GetSystemSettings(settingName).FirstOrDefault();
        }
        #endregion

        #region SearchAppTasks
        public List<Pr_TaskSearch_Result> SearchAppTasks(long assignedToUserID, long userID)
        {
            return Context.Pr_TaskSearch(assignedToUserID, userID).ToList();
        }
        #endregion

        #region GetInprogressBusinessRuleBypassRequest
        public List<Pr_BusinessRuleBypassRequestInprogressGet_Result> GetInprogressBusinessRuleBypassRequest(long userID)
        {
            return Context.Pr_BusinessRuleBypassRequestInprogressGet(userID).ToList();
        }
        #endregion


        #region GetSystemAdministratorDashboard
        public List<Pr_Dashboard_SystemAdministrator_Result> GetSystemAdministratorDashboard(long userID)
        {
            return Context.Pr_Dashboard_SystemAdministrator(userID).ToList();
        }
        #endregion

        #region GetSystemAdministratorDashboard_FailedTasks
        public List<Pr_Dashboard_SystemAdministrator_FailedTasks_Result> GetSystemAdministratorDashboard_FailedTasks(long userID)
        {
            return Context.Pr_Dashboard_SystemAdministrator_FailedTasks(userID).ToList();
        }
        #endregion

        #region Handle Failed Tasks
        #region RetryFailedTasks
        public void RetryFailedTasks(long instanceTaskID, long userID)
        {
            Context.Pr_Dashboard_SystemAdministrator_RetryFailedTasks(instanceTaskID, userID);
        }
        #endregion

        #region RequestCorrection
        public void RequestCorrection(long instanceTaskID, long userID)
        {
            Context.Pr_Dashboard_SystemAdministrator_RequestCorrection(instanceTaskID, userID);
        }
        #endregion
        #endregion

    }
}