using System.Data;
using Neo.Afx.ComponentModel;
using System.Linq;
using System.Collections.Generic;
using Neo.LegitimateLicences.Common;
using System;
using Neo.LegitimateLicences.Data.Models;

namespace Neo.LegitimateLicences.ApplicationRequest.Models.Database
{
    public class ApplicationDatabase : Database<ApplicationContext>
    {
        #region ApplicationDatabase
        public ApplicationDatabase()
            : base(new ApplicationContext())
        {

        }
        #endregion

        #region GetApplicationRequestTypeDocuments
        public List<Pr_GetApplicationRequestTypeDocuments_Result> GetApplicationRequestTypeDocuments(long applicationPrecheckDetailID, short? applicationRequestTypeID, long workflowID, long? memberID, long userID)
        {
            return Context.Pr_GetApplicationRequestTypeDocuments(applicationPrecheckDetailID, applicationRequestTypeID, workflowID, memberID, userID).ToList();
        }
        #endregion

        #region GetOperatingLicenceTransferSummary
        public List<Pr_ApplicationCreateGetTransferSummary_Result> GetOperatingLicenceTransferSummary(string tranferToIdNumber, string transferOperatingLicenceNumbers, long userID)
        {
            return Context.Pr_ApplicationCreateGetTransferSummary(tranferToIdNumber, transferOperatingLicenceNumbers, userID).ToList();
        } 
        #endregion

        #region GetApplicantApplicationsInProgress
        public List<Pr_ApplicationCreateGetApplicationsInProgress_Result> GetApplicantApplicationsInProgress(string idNumber, long userID)
        {
            return Context.Pr_ApplicationCreateGetApplicationsInProgress(idNumber, userID).ToList();
        } 
        #endregion

        #region CreateApplication
        public Vw_ApplicationDetails_Light CreateApplication(long applicationPrecheckDetailID, long userID)
        {
            return Context.Pr_ApplicationCreate(applicationPrecheckDetailID, userID).FirstOrDefault();
        }
        #endregion

        #region TAT_CreateApplication
        public Vw_ApplicationDetails_Light TAT_CreateApplication(long applicationPrecheckDetailID, long userID)
        {
            return Context.Pr_TAT_ApplicationCreate(applicationPrecheckDetailID, userID).FirstOrDefault();
        }
        #endregion

        #region GetApplicationForm
        public ApplicationDetail GetApplicationForm(long applicationDetailId, long userID)
        {
            var details = (from q in Context.ApplicationDetails
                            .Include("Applicant")
                            .Include("Proxy")
                            .Include("Contract")
                            .Include("PreviousLicenceHolder")
                            .Include("NPTR")
                            .Include("ApplicationTATDetail")
                            .Include("ApplicationTATDetail.AdjudicationDecision")
                           where q.ApplicationDetailID == applicationDetailId
                           select q).FirstOrDefault();

            if (details.ApplicationTATDetail == null)
            {
                details.ApplicationTATDetail = new ApplicationTATDetail();
            }

            return details;
        }
        #endregion

        #region GetApplicationDetais
        public ApplicationDetail GetApplicationDetais(long applicationDetailId, long userID)
        {
            var details = (from q in Context.ApplicationDetails                           
                           where q.ApplicationDetailID == applicationDetailId
                           select q).FirstOrDefault();
            return details;
        }
        #endregion

        #region GetApplicationTATDetails
        public ApplicationDetail GetApplicationTATDetails(long applicationDetailId, long userID)
        {
            var details = (from q in Context.ApplicationDetails                            
                            .Include("ApplicationTATDetail")
                            .Include("ApplicationTATDetail.AdjudicationDecision")
                           where q.ApplicationDetailID == applicationDetailId
                           select q).FirstOrDefault();

            if (details.ApplicationTATDetail == null)
            {
                details.ApplicationTATDetail = new ApplicationTATDetail();
            }
            else
            {
                details.Adjudications = (from q in Context.ApplicationAdjudications
                               where q.ApplicationDetailID == applicationDetailId
                               && q.AdjudicationDetailID == details.AdjudicationDetailID
                               && q.IsActiveAdjudication
                               select q).ToList();
            }

            return details;
        }
        #endregion

        #region GetApplicationApplicant
        public ApplicationApplicantDetail GetApplicationApplicant(long applicationDetailId, long userID)
        {
            var details = (from q in Context.ApplicationApplicantDetails                           
                           where q.ApplicationDetailID == applicationDetailId
                           select q).FirstOrDefault();

            return details;
        }
        #endregion

        #region GetApplicationCreateMemberLicences
        public List<Pr_ApplicationCreateGetMemberLicences_Result> GetApplicationCreateMemberLicences(string idNumber, long userID)
        {            
            return Context.Pr_ApplicationCreateGetMemberLicences(idNumber, userID).ToList();
        }
        #endregion

        #region GetApplicationCreateMemberAccreditations
        public List<Pr_ApplicationCreateGetMemberAccreditation_Result> GetApplicationCreateMemberAccreditations(long memberID, long userID)
        {            
            return Context.Pr_ApplicationCreateGetMemberAccreditation(memberID, userID).ToList();
        }
        #endregion

        #region GetApplicationCreateMemberAllowedNewSummary
        public List<Pr_ApplicationCreateGetMemberAllowedNewSummary_Result> GetApplicationCreateMemberAllowedNewSummary(string idNumberOrBusinessRegistrationNumber, long userID)
        {            
            return Context.Pr_ApplicationCreateGetMemberAllowedNewSummary(idNumberOrBusinessRegistrationNumber, userID).ToList();
        }
        #endregion

        #region GetApplicationLicences
        public ApplicationDetail GetApplicationLicences(long applicationDetailId)
        {
            var details = (from q in Context.ApplicationDetails
                            .Include("Applicant")
                            .Include("Licences")                 
                            .Include("Licences.Routes")
                            .Include("Licences.Services")
                            .Include("Licences.Services.ServiceType")
                            .Include("Licences.CurrentVehicle")
                            .Include("Licences.NewVehicle")
                            .Include("Licences.PreviousVehicle")
                           where q.ApplicationDetailID == applicationDetailId
                           select q).FirstOrDefault();

            if(details == null)
            {
                details = new ApplicationDetail();
            }

            return details;
        }
        #endregion

        #region GetAdjudicationApplicationLicences
        public ApplicationDetail GetAdjudicationApplicationLicences(long applicationDetailId, long userID)
        {
            var details = (from q in Context.ApplicationDetails
                            .Include("Applicant")
                            .Include("Proxy")
                            .Include("PreviousLicenceHolder")
                            .Include("NPTR")
                            .Include("Objections")
                            .Include("Referrals")
                            .Include("Referrals.ReferralType")
                            .Include("Receipts")
                            .Include("Receipts.PaymentType")
                            .Include("InspectionDetails")
                            .Include("Adjudications")
                            .Include("Adjudications.Decisions")
                            .Include("Licences")
                            .Include("Licences.Routes")
                            .Include("Licences.Services")
                            .Include("Licences.Services.ServiceType")
                            .Include("Licences.CurrentVehicle")
                            .Include("Licences.NewVehicle")
                            .Include("Licences.PreviousVehicle")
                            .Include("ApplicationTATDetail")
                            .Include("ApplicationTATDetail.AdjudicationDecision")
                           where q.ApplicationDetailID == applicationDetailId
                           select q).FirstOrDefault();
            
            #region Cleanup
            if (details.Objections == null)
            {
                details.Objections = new List<ApplicationObjection>();
            }

            if (details.NPTR == null)
            {
                details.NPTR = new ApplicationNPTRDetail();
            }

            if (details.Referrals == null)
            {
                details.Referrals = new List<ApplicationReferral>();
            }

            if (details.Receipts == null)
            {
                details.Receipts = new List<ReceiptDetail>();
            }

            if (details.InspectionDetails == null)
            {
                details.InspectionDetails = new ApplicationInspectionDetail();
            }

            if (details.Adjudications == null)
            {
                details.Adjudications = new List<ApplicationAdjudication>();
            }
            foreach(var adjudication in details.Adjudications)
            {
                if(adjudication.Decisions == null)
                {
                    adjudication.Decisions = new List<ApplicationAdjudicationDecision>();
                }
            }

            if (details.ApplicationTATDetail == null)
            {
                details.ApplicationTATDetail = new ApplicationTATDetail();
                details.ApplicationTATDetail.AdjudicationDecision = new AdjudicationDecision();
            }
            if(details.ApplicationTATDetail.AdjudicationDecision == null)
            {
                details.ApplicationTATDetail.AdjudicationDecision = new AdjudicationDecision();
            }
            #endregion

            return details;
        }
        #endregion

        #region GetPaymentDetails
        public List<ReceiptDetail> GetPaymentDetails(long applicationDetailId, long userID)
        {
            var details = (from q in Context.ReceiptDetails
                            .Include("PaymentType")
                           where q.ApplicationDetailID == applicationDetailId
                           select q).ToList();

            if (details == null)
            {
                details = new List<ReceiptDetail>();
            }

            return details;
        }
        #endregion

        #region GetApplicationLicencesView
        public Vw_ApplicationLicences GetApplicationLicencesView(long ApplicationLicenceID, long userID)
        {
            var details = (from q in Context.Vw_ApplicationLicences
                           where q.ApplicationLicenceID == ApplicationLicenceID
                           select q).FirstOrDefault();

            if (details == null)
            {
                details = new Vw_ApplicationLicences();
            }

            return details;
        }
        #endregion 

        #region GetApplicationLicenceVehicleDetailsView
        public Vw_ApplicationLicenceVehicleDetails GetApplicationLicenceVehicleDetailsView(long ApplicationLicenceID, long userID)
        {
            var details = (from q in Context.Vw_ApplicationLicenceVehicleDetails
                           where q.ApplicationLicenceID == ApplicationLicenceID
                           select q).FirstOrDefault();

            if (details == null)
            {
                details = new Vw_ApplicationLicenceVehicleDetails();
            }

            return details;
        }
        #endregion 

        #region GetApplicationInspectionDetails
        public ApplicationDetail GetApplicationInspectionDetails(long applicationDetailId, long userID)
        {
            var details = (from q in Context.ApplicationDetails
                            .Include("InspectionDetails")                            
                           where q.ApplicationDetailID == applicationDetailId
                           select q).FirstOrDefault();

            if (details.InspectionDetails == null)
            {
                details.InspectionDetails = new ApplicationInspectionDetail();
            }

            return details;
        }
        #endregion

        #region GetApplicationReferrals
        public ApplicationDetail GetApplicationReferrals(long applicationDetailId, long userID)
        {
            var details = (from q in Context.ApplicationDetails
                            .Include("Referrals")
                            .Include("Referrals.ReferralType")                            
                           where q.ApplicationDetailID == applicationDetailId
                           select q).FirstOrDefault();
            if(details.Referrals == null)
            {
                details.Referrals = new List<ApplicationReferral>();
            }
            return details;
        }
        #endregion

        #region GetApplicationObjections
        public ApplicationDetail GetApplicationObjections(long applicationDetailId, long userID)
        {
            var details = (from q in Context.ApplicationDetails
                            .Include("Objections")
                            .Include("Objections.ApplicationLicence")
                           where q.ApplicationDetailID == applicationDetailId
                           select q).FirstOrDefault();

            if (details.Objections == null)
            {
                details.Objections = new List<ApplicationObjection>();
            }
            else
            {
                details.Objections = details.Objections.Where(a => !a.IsCancelled).ToList();
            }            

            return details;
        }
        #endregion

        #region GetLicence
        public ApplicationLicence GetLicence(long applicationLicenceID, long userID)
        {
            var details = (from q in Context.ApplicationLicences
                           where q.ApplicationLicenceID == applicationLicenceID
                           select q).FirstOrDefault();

            if (details == null)
            {
                details = new ApplicationLicence();
            }

            return details;
        }
        #endregion

        #region GetLicenceVehicle
        public ApplicationLicenceVehicleDetail GetLicenceVehicle(long applicationLicenceID, long userID)
        {
            var details = (from q in Context.ApplicationLicenceVehicleDetails
                           where q.ApplicationLicenceID == applicationLicenceID
                           select q).FirstOrDefault();

            if (details == null)
            {
                details = new ApplicationLicenceVehicleDetail();
            }

            return details;
        }
        #endregion

        #region GetLicencePreviousVehicle
        public ApplicationLicencePreviousVehicleDetail GetLicencePreviousVehicle(long applicationLicenceID, long userID)
        {
            var details = (from q in Context.ApplicationLicencePreviousVehicleDetails
                           where q.ApplicationLicenceID == applicationLicenceID
                           select q).FirstOrDefault();

            if (details == null)
            {
                details = new ApplicationLicencePreviousVehicleDetail();
            }

            return details;
        }
        #endregion

        #region GetLicenceServiceDetails
        public ApplicationLicence GetLicenceServiceDetails(long applicationLicenceID, long userID)
        {
            var details = (from q in Context.ApplicationLicences
                           .Include("Services")
                           .Include("Services.ServiceType")
                           where q.ApplicationLicenceID == applicationLicenceID
                           select q).FirstOrDefault();

            details.RemovedServices = new List<ApplicationLicenceServiceDetail>();
            
            return details;
        }
        #endregion

        #region GetApplicationView
        public Vw_ApplicationDetails_Light GetApplicationView(long applicationDetailId, long userID)
        {
            var details = (from q in Context.Vw_ApplicationDetails_Light
                           where q.ApplicationDetailID == applicationDetailId
                           select q).FirstOrDefault();

            return details;
        }
        #endregion

        #region AddApplicationLicenceRoute_Existing
        public ApplicationLicenceRouteDetail AddApplicationLicenceRoute_Existing(long applicationLicenceID, long routeID, short serviceTypeID, long userID)
        {
            return Context.Pr_ApplicationLicenceRoute_AddExisting(applicationLicenceID, routeID, serviceTypeID, userID).FirstOrDefault();
        }
        #endregion

        #region GetApplicationWorkflowStatusSummary
        public List<Pr_Dashboard_ApplicationWorkflowStatusGet_Result> GetApplicationWorkflowStatusSummary(long applicationDetailId, long userID)
        {            
            return Context.Pr_Dashboard_ApplicationWorkflowStatusGet(applicationDetailId, userID).ToList();
        }
        #endregion

        #region GetApplicationAdjudicationDetails
        public ApplicationDetail GetApplicationAdjudicationDetails(long applicationDetailId, long userID)
        {
            var details = (from q in Context.ApplicationDetails
                            .Include("Adjudications")
                            .Include("Adjudications.AdjudicationDetail")           
                            .Include("Adjudications.AdjudicationDetail.AdjudicationStatus")
                            .Include("Adjudications.Decisions")                            
                            .Include("Adjudications.Decisions.Decision")
                            .Include("Adjudications.Decisions.AdjudicatedBy")
                            .Include("Adjudications.Decisions.LicenceDuration")                       
                           where q.ApplicationDetailID == applicationDetailId
                           select q).FirstOrDefault();

            if (details == null)
            {
                details = new ApplicationDetail();
                details.Adjudications = new List<ApplicationAdjudication>();
            }

            return details;
        }
        #endregion

        #region GetApplicationLicenceAdjudicationDetails
        public List<ApplicationAdjudication> GetApplicationLicenceAdjudicationDetails(long applicationDetailId, long applicationLicenceID, long userID)
        {
            var details = (from q in Context.ApplicationAdjudications
                            .Include("AdjudicationDetail")
                            .Include("AdjudicationDetail.AdjudicationStatus")
                            .Include("Decisions")
                            .Include("Decisions.Decision")
                            .Include("Decisions.AdjudicatedBy")
                            .Include("Decisions.LicenceDuration")
                           where q.ApplicationDetailID == applicationDetailId
                           && q.ApplicationLicenceID == applicationLicenceID
                           select q).ToList();

            if (details == null)
            {
                details = new List<ApplicationAdjudication>();                
            }
            else
            {
                foreach(var applicationAdjudication in details)
                {

                    if(applicationAdjudication.AdjudicationDetail == null)
                    {
                        applicationAdjudication.AdjudicationDetail = new AdjudicationDetail();
                    }

                    if (applicationAdjudication.Decisions == null)
                    {
                        applicationAdjudication.Decisions = new List<ApplicationAdjudicationDecision>();
                    }
                    else
                    {
                        foreach(var decision in applicationAdjudication.Decisions)
                        {
                            if (decision.Decision == null)
                            {
                                decision.Decision = new AdjudicationDecision();
                            }

                            if (decision.AdjudicatedBy == null)
                            {
                                decision.AdjudicatedBy = new User();
                            }

                            if (decision.LicenceDuration == null)
                            {
                                decision.LicenceDuration = new LicenceDuration();
                            }
                        }
                    }
                }
            }

            return details;
        }

        public List<ApplicationAdjudication> GetApplicationLicenceAdjudicationDetails(long applicationDetailId, long userID)
        {
            var details = (from q in Context.ApplicationAdjudications
                            .Include("AdjudicationDetail")
                            .Include("AdjudicationDetail.AdjudicationStatus")
                            .Include("Decisions")
                            .Include("Decisions.Decision")
                            .Include("Decisions.AdjudicatedBy")
                            .Include("Decisions.LicenceDuration")
                           where q.ApplicationDetailID == applicationDetailId
                           && q.ApplicationLicenceID == null
                           select q).ToList();

            if (details == null)
            {
                details = new List<ApplicationAdjudication>();
            }
            else
            {
                foreach (var applicationAdjudication in details)
                {

                    if (applicationAdjudication.AdjudicationDetail == null)
                    {
                        applicationAdjudication.AdjudicationDetail = new AdjudicationDetail();
                    }

                    if (applicationAdjudication.Decisions == null)
                    {
                        applicationAdjudication.Decisions = new List<ApplicationAdjudicationDecision>();
                    }
                    else
                    {
                        foreach (var decision in applicationAdjudication.Decisions)
                        {
                            if (decision.Decision == null)
                            {
                                decision.Decision = new AdjudicationDecision();
                            }

                            if (decision.AdjudicatedBy == null)
                            {
                                decision.AdjudicatedBy = new User();
                            }

                            if (decision.LicenceDuration == null)
                            {
                                decision.LicenceDuration = new LicenceDuration();
                            }
                        }
                    }
                }
            }

            return details;
        }
        #endregion 

        #region GetApplicationReceipts
        public List<Pr_ApplicationReceiptsGet_Result> GetApplicationReceipts(long applicationDetailId, long userID)
        {
            var result = Context.Pr_ApplicationReceiptsGet(applicationDetailId, userID).ToList();
            if (result == null)
            {
                result = new List<Pr_ApplicationReceiptsGet_Result>();
            }
            return result;
        }
        #endregion

        #region GetApplicationReceipt
        public Pr_ApplicationReceiptsGet_Result GetApplicationReceipt(long applicationDetailId, long receiptDetailID, long userID)
        {            
            return GetApplicationReceipts(applicationDetailId, userID).Where(a => a.ReceiptDetailID == receiptDetailID).FirstOrDefault();
        }
        #endregion

        #region GetApplicationRequesTypes
        public List<ApplicationRequestType> GetApplicationRequesTypes()
        {            
            return Context.ApplicationRequestTypes.Where(a => a.IsCancelled != true).ToList();
        }
        #endregion
        
        #region GetApplicationLicenceRouteDetail
        public ApplicationLicenceRouteDetail GetApplicationLicenceRouteDetail(long applicationLicenceRouteDetailID, long applicationLicenceID, short serviceTypeID, long userID)
        {            
            var details = (from q in Context.ApplicationLicenceRouteDetails
                           .Include("ApplicationLicenceRouteDetailPlaces")
                           where q.ApplicationLicenceRouteDetailID == applicationLicenceRouteDetailID
                           select q).FirstOrDefault();

            #region Cleanup
            if(details == null || details.ApplicationLicenceRouteDetailID <= 0)
            {
                details = new ApplicationLicenceRouteDetail();
                details.ApplicationLicenceRouteDetailPlaces = new List<ApplicationLicenceRouteDetailPlace>();
                details.ApplicationLicenceID = applicationLicenceID;
                details.ApplicationRouteDetailStatusID = (short)ApplicationRouteDetailStatusEnum.New;
                details.RouteOrigin = "";
                details.RouteDestination = "";
                details.RouteDescription = "";
                details.IsOriginSameAsPhysicalAddress = true;
                details.IsDestinationArea = true;
                details.RouteChangeTypeID = (short)RouteChangeTypeEnum.AddBrandNewRouteToLicence;
                details.ServiceTypeID = serviceTypeID;
            }            
            else
            {
                if (details.ApplicationLicenceRouteDetailPlaces == null)
                {
                    details.ApplicationLicenceRouteDetailPlaces = new List<ApplicationLicenceRouteDetailPlace>();
                }
            }

            details.ExistingApplicationLicenceRouteDetailPlaces = details.ApplicationLicenceRouteDetailPlaces.ToList();
            #endregion

            return details;
        }
        #endregion

        #region GetApplicationLicenceRouteDetail
        public List<ApplicationLicenceRouteDetail> GetApplicationLicenceRouteDetails(long applicationLicenceID, long userID)
        {
            var applicationLicenceRouteDetail = (from q in Context.ApplicationLicenceRouteDetails
                           .Include("ApplicationLicenceRouteDetailPlaces")
                           where q.ApplicationLicenceID == applicationLicenceID
                           select q).ToList();

            #region Cleanup
            if (applicationLicenceRouteDetail == null)
            {
                applicationLicenceRouteDetail = new List<ApplicationLicenceRouteDetail>();

            }
            #endregion

            return applicationLicenceRouteDetail;
        }
        #endregion

        #region GetApplicationLicencesRouteDetails
        public List<ApplicationLicenceRouteDetail> GetApplicationLicencesRouteDetails(long applicationDetailId, long userID)
        {
            List <ApplicationLicenceRouteDetail> applicationLicenceRouteDetail = new List<ApplicationLicenceRouteDetail>();

            var application = (from q in Context.ApplicationDetails
                           .Include("Licences")                            
                            .Include("Licences.Routes")
                            .Include("Licences.Routes.ApplicationLicenceRouteDetailPlaces")
                               where q.ApplicationDetailID == applicationDetailId
                                                 select q).FirstOrDefault();

            #region Populate applicationLicenceRouteDetail
            if (application != null)
            {
                foreach(var licence in application.Licences)
                {
                    foreach(var route in licence.Routes)
                    {
                        applicationLicenceRouteDetail.Add(route);
                    }
                }
            }
            #endregion

            return applicationLicenceRouteDetail;
        }
        #endregion

        #region GetApplicationLicenceRouteDetails
        public string CalculateApplicationLicenceRouteDetails(long applicationLicenceRouteDetailID, long userID)
        {
            return Context.Pr_ApplicationLicenceRouteDescriptionCalculate(applicationLicenceRouteDetailID, userID).FirstOrDefault();
        }
        #endregion

        #region CalculateApplicationLicenceRadiusRouteDetails
        public string CalculateApplicationLicenceRadiusRouteDetails(long applicationLicenceRouteDetailID, long userID)
        {
            return Context.Pr_ApplicationLicenceRadiusRouteDescriptionCalculate(applicationLicenceRouteDetailID, userID).FirstOrDefault();
        }
        #endregion

        #region GetFailedApplicationDocumentVerifications
        public List<Pr_ApplicationFailedDocumentVerificationsGet_Result> GetFailedApplicationDocumentVerifications(long applicationDetailID, long userID)
        {
            var returnValue = Context.Pr_ApplicationFailedDocumentVerificationsGet(applicationDetailID, userID).ToList();

            if (returnValue == null)
            {
                returnValue = new List<Pr_ApplicationFailedDocumentVerificationsGet_Result>();
            }

            return returnValue;
        }
        #endregion

        #region GetApplicationReferral
        public ApplicationReferral GetApplicationReferral(long applicationReferralDetailID, long applicationDetailID, long userID)
        {
            var applicationReferral = (from q in Context.ApplicationReferrals
                                                 where q.ApplicationReferralDetailID == applicationReferralDetailID
                                                 && q.ApplicationDetailID == applicationDetailID
                                                 && q.IsCancelled == false
                                       select q).FirstOrDefault();

            if (applicationReferral == null)
            {
                applicationReferral = new ApplicationReferral();
                applicationReferral.ApplicationDetailID = applicationDetailID;
            }

            return applicationReferral;
        }

        public ReferralType GetReferralType(short referralTypeID)
        {
            var referralType = (from q in Context.ReferralTypes
                                       where q.ReferralTypeID == referralTypeID
                                       && q.IsCancelled == false
                                       select q).FirstOrDefault();            

            return referralType;
        }
        #endregion

        #region GetApplicationObjectionl
        public ApplicationObjection GetApplicationObjection(long applicationObjectionID, long applicationDetailID, long userID)
        {
            var applicationObjection = (from q in Context.ApplicationObjections
                                       where q.ApplicationObjectionID == applicationObjectionID
                                       && q.ApplicationDetailID == applicationDetailID
                                       && q.IsCancelled == false
                                       select q).FirstOrDefault();

            if (applicationObjection == null)
            {
                applicationObjection = new ApplicationObjection();
                applicationObjection.ApplicationDetailID = applicationDetailID;
                applicationObjection.ObjectionDate = DateTime.Now;
            }

            return applicationObjection;
        }        
        #endregion

        #region Remove Licence Route
        public string RemoveLicenceRoute(long applicationLicenceID, long applicationLicenceRouteDetailID, long userID)
        {            
            return Context.Pr_ApplicationLicenceRoute_RemoveRoute(applicationLicenceID, applicationLicenceRouteDetailID, userID).FirstOrDefault();
        }
        #endregion

        #region CreateApplicationPrecheck
        public List<Pr_ApplicationPrecheckCreate_Result> CreateApplicationPrecheck(short applicationRequestTypeID, short sourceApplicationID, string applicantName, string applicantCell, short idTypeID, string idOrBusinessRegistrationNumber, string originatingLicenceNumbers, string originatingAccreditationNumber, short? vehicleTypeID, string serviceTypeIDs, short? numberOfLicencesRequired, short? applicationProvinceID, short? applicationRegionID, long? applicationAssociationID, string applicationAllowedNewLicenceIDs, string selectedRouteIDs, long? parentApplicationDetailID, long? parentApplicationLicenceID, long userID)
        {
            return Context.Pr_ApplicationPrecheckCreate(applicationRequestTypeID, sourceApplicationID, applicantName, applicantCell, idTypeID, idOrBusinessRegistrationNumber, originatingLicenceNumbers, originatingAccreditationNumber, vehicleTypeID, serviceTypeIDs, numberOfLicencesRequired, applicationProvinceID, applicationRegionID, applicationAssociationID, applicationAllowedNewLicenceIDs, selectedRouteIDs, parentApplicationDetailID, parentApplicationLicenceID, userID).ToList();
        }
        #endregion

        #region UpdateApplicationLicenceDetails
        public void UpdateApplicationLicenceDetails(long applicationLicenceID, DateTime? specialLicenceDateFrom, DateTime? specialLicenceDateTo, string specialLicenceDetails, string reasonForAmendment, long userID)
        {            
            Context.Pr_ApplicationLicenceDetailUpdate(applicationLicenceID, specialLicenceDateFrom, specialLicenceDateTo, specialLicenceDetails, reasonForAmendment, userID);
        }
        #endregion

        #region UpdateApplicationLicenceDetails
        public void UpdateApplicationLicenceRouteDetails(long applicationLicenceID, long applicationLicenceRouteDetailID, string routeOrigin, string routeDestination, string routeDescription, long userID)
        {
            Context.Pr_ApplicationLicenceRouteDetailUpdate(applicationLicenceID, applicationLicenceRouteDetailID, routeOrigin, routeDestination, routeDescription, userID);
        }
        #endregion

        #region SearchMemberAssociation
        public List<Pr_MemberAssociationSearch_Result> SearchMemberAssociation(long memberID, long userID)
        {
            return Context.Pr_MemberAssociationSearch(memberID, userID).ToList();
        }
        #endregion

        #region GetApplicationTags
        public List<Vw_ApplicationTags> GetApplicationTags(long applicationDetailID, long userID)
        {
            return Context.Vw_ApplicationTags.Where(a => a.ApplicationDetailID == applicationDetailID).ToList();
        }
        #endregion

        #region ApplicationTagCreate
        public List<Vw_ApplicationTags> ApplicationTagCreate(long applicationDetailID, short tagID, bool isUserTag, bool allowDuplicate, long userID)
        {
            Context.Pr_ApplicationTagCreate(applicationDetailID, tagID, isUserTag, allowDuplicate, userID);

            return Context.Vw_ApplicationTags.Where(a => a.ApplicationDetailID == applicationDetailID).ToList();
        }
        #endregion

        #region ApplicationTagRemove
        public List<Vw_ApplicationTags> ApplicationTagRemove(long applicationDetailID, short tagID, long userID)
        {
            Context.Pr_ApplicationTagRemove(applicationDetailID, tagID, userID);

            return Context.Vw_ApplicationTags.Where(a => a.ApplicationDetailID == applicationDetailID).ToList();
        }
        #endregion

        #region ApplicationTagClear
        public void ApplicationTagClear(long applicationTagID, long applicationDetailID, bool isTagCleared, string comments, long userID)
        {
            Context.Pr_ApplicationTagClear(applicationTagID, applicationDetailID, isTagCleared, comments, userID);           
        }
        #endregion

        #region ReceiptApplicationPaymentTypeUpdate
        public void ReceiptApplicationPaymentTypeUpdate(long applicationDetailID, long receiptDetailID, short paymentTypeID, long userID)
        {
            Context.Pr_ReceiptApplicationPaymentTypeUpdate(applicationDetailID, receiptDetailID, paymentTypeID, userID);
        }
        #endregion

        #region GetApplicantUnassignedReceipt
        public List<ReceiptDetail> GetApplicantUnassignedReceipt(long applicationDetailID, long userID)
        {
            return Context.Pr_ApplicantUnassignedReceiptGet(applicationDetailID, userID).ToList();
        }
        #endregion

        #region AssignReceiptToApplication
        public void AssignReceiptToApplication(long receiptDetailID, long applicationDetailID, long userID)
        {
            Context.Pr_ReceiptAddToApplication(receiptDetailID, applicationDetailID, userID);
        }
        #endregion

        #region TATPrecheckAdjudicationSearch
        public List<Pr_TAT_AdjudicationPrecheckSearch_Result> TATPrecheckAdjudicationSearch(string applicantIDNumber, string applicantName, string olPermitNumber, string applicationLicenceReferenceNumber, long userID)
        {
            return Context.Pr_TAT_AdjudicationPrecheckSearch(applicantIDNumber, applicantName, olPermitNumber, applicationLicenceReferenceNumber, userID).ToList();
        }
        #endregion
        
        #region ApplicationCreateGetAssociationRoutes
        public List<Pr_ApplicationCreateGetAssociationRoutes_Result> ApplicationCreateGetAssociationRoutes(long associationID, long userID)
        {
            return Context.Pr_ApplicationCreateGetAssociationRoutes(associationID, userID).ToList();
        }
        #endregion

        #region GazetteApplicationsGet
        public List<Pr_ApplicationGazetteGet_Result> GazetteApplicationsGet(long applicationDetailID, long userID)
        {
            return Context.Pr_ApplicationGazetteGet(applicationDetailID, userID).ToList();
        }
        #endregion

        #region GetApplicationRequestNewRouteRequests
        public List<Pr_ApplicationRequestNewRouteRequestsGet_Result> GetApplicationRequestNewRouteRequests(long applicationDetailId, long userID)
        {
            return Context.Pr_ApplicationRequestNewRouteRequestsGet(applicationDetailId, userID).ToList();
        }
        #endregion
    }
}