using System.Data; using System.Data.Common; using System.Data.SqlClient; using Neo.Legitimate.Common; using Pilotfish.Afx.ComponentModel; using System.Linq; using System.Collections.Generic; using System.Collections.ObjectModel; using System; using System.Xml.Linq; using System.IO; using System.Diagnostics; using Neo.Legitimate.Data.Models; using Neo.Legitimate.Common.Helpers; using Neo.Legitimate.ApplicationRequest.Models; namespace Neo.Legitimate.ApplicationRequest.Models { public class ApplicationDatabase : Database { public ApplicationDatabase() : base(new ApplicationContext()) { } #region GetApplicationRequestDetails public Vw_ApplicationRequestDetails GetApplicationRequestDetails(long applicationRequestDetailId) { return (from q in Context.Vw_ApplicationRequestDetails where q.ApplicationRequestDetailID == applicationRequestDetailId select q).FirstOrDefault(); } #endregion #region GetApplicationRequest public ApplicationRequestDetail GetApplicationRequest(long ApplicationRequestDetailId, long userID) { if (ApplicationRequestDetailId == -1) { var ranks = new Collection(); ranks.Add(new ApplicationRankDetail { IsDefaultRank = true }); return new ApplicationRequestDetail() { ApplicationRequestDetailID = ApplicationRequestDetailId, ApplicationRequestTypeID = (short)RequestType.NewOperatingLicence, ProvinceID = (short)Common.Province.KwaZuluNatal, Applicant = new ApplicationApplicantDetail(), Mode = new ApplicationModeDetail(), Proxy = new ApplicationProxyDetail(), Routes = new Collection(), Services = new Collection(), Vehicle = new ApplicationVehicleDetail(), Documents = new Collection(), Contract = new ApplicationContractDetail(), Ranks = ranks, ExistingLicence = new ApplicationExistingLicence(), ExistingLicenceHolder = new ApplicationExistingLicenceHolder(), ExistingVehicle = new ApplicationExistingVehicleDetail(), Objections = new Collection(), ApplicationVehicleDetailsInBulk = new Collection(), CreatedByID = userID, CreatedDate = DateTime.Now, UpdatedByID = userID, UpdatedDate = DateTime.Now }; } var details = (from q in Context.ApplicationRequestDetails.Include("Applicant").Include("Mode").Include("Proxy").Include("Routes").Include("Services").Include("Vehicle").Include("ApplicationVehicleDetailsInBulk").Include("ExistingVehicle").Include("Documents").Include("Documents.DocumentType").Include("Contract").Include("ReceiptDetails").Include("Ranks").Include("ExistingLicenceHolder").Include("ExistingLicence").Include("Objections").Include("Objections.CreatedBy").Include("ApplicationRequestAdjudicationDecisions").Include("ApplicationRequestAdjudicationDecisions.AdjudicationDecision").Include("ApplicationRequestAdjudicationDecisions.AdjudicatedBy").Include("ApplicationRequestAdjudicationDecisions.LicenceDuration") where q.ApplicationRequestDetailID == ApplicationRequestDetailId select q).FirstOrDefault(); if (details.GazetteDetailID.HasValue) { details.GazetteDetail = (from q in Context.GazetteDetails where q.GazetteDetailID == details.GazetteDetailID.Value select q).FirstOrDefault(); } return details; } #endregion #region GetApplicationsRequestTypes public IEnumerable GetApplicationsRequestTypes() { return Context.ApplicationRequestTypes.Where(a => !a.IsCancelled.Value).ToList(); } #endregion #region GetApplicationRequestType public ApplicationRequestType GetApplicationRequestType(short applicationRequestTypeID, bool includeAll) { var requestType = Context.ApplicationRequestTypes.Where(a => a.ApplicationRequestTypeID == applicationRequestTypeID).FirstOrDefault(); if (includeAll) { requestType.ApplicationRequestTypeDocuments = Context.ApplicationRequestTypeDocuments.Include("DocumentType").Where(a => a.ApplicationRequestType.ApplicationRequestTypeID == applicationRequestTypeID && !a.IsCancelled).ToList(); } return requestType; } #endregion #region GetApplicationRequestObjections public IEnumerable GetApplicationRequestObjections(long applicationRequestDetailID) { return Context.ApplicationRequestObjections.Include("CreatedBy").Where(a => a.ApplicationRequestDetailID == applicationRequestDetailID).ToList(); } #endregion #region SaveApplicationRequestObjection public IEnumerable SaveApplicationRequestObjection(ApplicationRequestObjection objection, long userID) { Context.Pr_ApplicationRequestObjectionUpsert(objection.ApplicationRequestObjectionID, objection.ApplicationRequestDetailID, objection.Objector, objection.Objection, objection.ObjectionDate, objection.PostalAddress, objection.PostalAddressCode, objection.TelPhone, objection.TelCell, objection.TelFax, objection.Email, objection.ObjectionDocumentID, userID); return GetApplicationRequestObjections(objection.ApplicationRequestDetailID); } #endregion #region GetVerifiedRoute public Route GetVerifiedRoute(int routeId) { return Context.Routes.Where(a => a.RouteID == routeId).FirstOrDefault(); } #endregion #region AddPrecheckDetailToApplicationRequestDetail /// /// Prepopulate object with values from source system /// /// /// /// public ApplicationRequestDetail AddPrecheckDetailToApplicationRequestDetail(ApplicationRequestDetail value, long userID) { var legitimateDb = new LegitimateDatabase(); #region add relevant Precheck details var precheckDetail = Context.PrecheckDetails.Where(a => a.PrecheckDetailID == value.PrecheckDetailID).FirstOrDefault(); if (precheckDetail != null) { var precheckOlPermitNumber = precheckDetail.OlPermitNumber; //strip out the version number if found if (precheckOlPermitNumber.Contains("/")) { precheckOlPermitNumber = precheckOlPermitNumber.Substring(0, precheckOlPermitNumber.LastIndexOf("/")); } value.IsRenewal = precheckDetail.IsRenewal; value.IsLateRenewal = precheckDetail.IsLateRenewal; value.IsLtpsConversion = precheckDetail.IsLtpsConversion; #region New Operating Licence if (value.ApplicationRequestTypeID == (short)Common.ApplicationRequestType.NewOperatingLicence) { if (!String.IsNullOrWhiteSpace(precheckDetail.ApplicantName)) { var names = precheckDetail.ApplicantName.Split(' '); value.Applicant.FirstNames = names[0]; if (names.Length > 1) { value.Applicant.SurnameOrCompanyName = names[1]; } } if (!String.IsNullOrWhiteSpace(precheckDetail.IDNumber)) { var member = legitimateDb.Purification_FindMember(precheckDetail.IDNumber, userID); var verifiedMember = legitimateDb.GetVerifiedMember(precheckDetail.IDNumber); if (member != null) { #region Populate applicant details based on verified data value.Applicant.Email = member.Email; value.Applicant.FirstNames = member.FirstNames; value.Applicant.IDNumberOrBusinessRegistrationNo = member.IDNumber; value.Applicant.SARSIncomeTaxNumber = member.SARSIncomeTaxNumber; value.Applicant.SARSReasonIfNoTaxNumber = member.SARSReasonIfNoTaxNumber; value.Applicant.SurnameOrCompanyName = member.SurnameOrCompanyName; value.Applicant.TelCell = member.TelCell; value.Applicant.TelFax = member.TelFax; value.Applicant.TelPhone = member.TelPhone; value.Applicant.PhysicalAddress = member.PhysicalAddress; value.Applicant.PhysicalAddressCode = member.PhysicalAddressCode; value.Applicant.PostalAddress = member.PostalAddress; value.Applicant.PostalAddressCode = member.PostalAddressCode; value.Applicant.TradeName = member.TradeName; value.Applicant.BusinessTypeID = member.BusinessTypeID; if (verifiedMember != null) { value.Applicant.VerifiedMemberID = verifiedMember.MemberID; value.Applicant.IDTypeID = verifiedMember.IDTypeID; value.Applicant.IDTypeDescription = verifiedMember.IDTypeDescription; } #endregion } else { value.Applicant.IDNumberOrBusinessRegistrationNo = precheckDetail.IDNumber; } } if (!String.IsNullOrWhiteSpace(precheckDetail.ChassisNumber)) { if (value.Vehicle != null) { value.Vehicle.VIN = precheckDetail.ChassisNumber; } } if (!String.IsNullOrWhiteSpace(precheckDetail.VehicleRegistrationNumber)) { if (value.Vehicle != null) { value.Vehicle.VehicleRegistrationNumber = precheckDetail.VehicleRegistrationNumber; } } if (precheckDetail.ServiceTypeID.HasValue) { value.Services.Add(new ApplicationServiceDetail() { ServiceTypeID = precheckDetail.ServiceTypeID.Value, ServiceTypeDescription = "" }); } if (precheckDetail.AssociationID.HasValue) { var associationRoutes = legitimateDb.GetVerifiedAssociationRoutes(precheckDetail.AssociationID.Value); foreach (var route in associationRoutes) { var origin = (route.OriginPlaceID.HasValue) ? legitimateDb.GetVerifiedPlace(route.OriginPlaceID.Value).Description : ""; var destination = (route.DestinationPlaceID.HasValue) ? legitimateDb.GetVerifiedPlace(route.DestinationPlaceID.Value).Description : ""; var routeName = route.RouteName; if (!String.IsNullOrWhiteSpace(routeName)) { short appRouteStatusId = 2; //default to awaiting verification if (route.RouteStatusID == 1) //active { appRouteStatusId = 3; //verfied } value.Routes.Add(new ApplicationRouteDetail() { ApplicationRouteDetailStatusID = appRouteStatusId, BoardRouteNumber = route.BoardRouteNumber, NationalRouteNumber = route.NationalRouteNumber, RouteDescription = route.Description, RouteLength = route.RouteLength, VerifiedRouteID = route.RouteID, RouteDestination = destination, RouteOrigin = origin, RouteChangeTypeID = (short)RouteChangeTypeEnum.ExistingRouteOnLicence }); } } } #region create vehicle rows if bulk vehicle application value.IsBulkVehicleApplication = (precheckDetail != null && precheckDetail.NumberOfVehiclesRequired.HasValue && precheckDetail.NumberOfVehiclesRequired.Value > 1); value.ApplicationVehicleDetailsInBulk = new List(); if (value.IsBulkVehicleApplication) { for (var x = 1; x <= precheckDetail.NumberOfVehiclesRequired.Value; x++) { value.ApplicationVehicleDetailsInBulk.Add(new ApplicationVehicleDetailsInBulk() { VehicleNumber = x }); } } #endregion value.Applicant.VerifiedAssociationID = precheckDetail.AssociationID; value.ExistingLicence.OperatingLicenceNumber = precheckDetail.OlPermitNumber; } #endregion short precheckModeID = -1; var applicant = new XElement("applicant"); var applicantLicence = new XElement("licence"); var applicantVehicle = new XElement("vehicle"); var foundApplication = false; if (precheckDetail.ModeID.HasValue) { precheckModeID = precheckDetail.ModeID.Value; } Fn_GetSourceApplicationRecordsFromSourceSystem_Result sourceSystemData = null; if (!string.IsNullOrEmpty(precheckDetail.OlPermitNumber)) { sourceSystemData = GetSourceApplicationFromSourceSystem(precheckDetail.OlPermitNumber, precheckDetail.SourceApplicationID, precheckModeID, true, false); } if (sourceSystemData != null) { foundApplication = true; applicant = XElement.Parse(sourceSystemData.ApplicantXML); applicantLicence = XElement.Parse(sourceSystemData.LicenceXML); applicantVehicle = XElement.Parse(sourceSystemData.VehicleXML); } if (foundApplication) { if (value.SourceApplicationID == (short)Common.SourceApplication.Legitimate) { var licenceId = (long)applicantLicence.Element("licenceid"); var licence = legitimateDb.GetVerifiedLicence(licenceId); //if the source is a legitimate licence the data in the Db is used, as opposed to the xml var serviceDetails = legitimateDb.GetVerifiedLicenceServiceDetails(licenceId); var routes = legitimateDb.GetVerifiedLicenceRoutes(licenceId); #region Applicant Details if (ApplicationRequestHelper.IsTransferType(value.ApplicationRequestTypeID)) { if (licence.Member != null) { value.ExistingLicenceHolder.Email = licence.Member.Email; value.ExistingLicenceHolder.FirstNames = licence.Member.FirstNames; value.ExistingLicenceHolder.IDNumberOrBusinessRegistrationNo = licence.Member.IDNumberOrBusinessRegistrationNo; value.ExistingLicenceHolder.SARSIncomeTaxNumber = licence.Member.SARSIncomeTaxNumber; value.ExistingLicenceHolder.SurnameOrCompanyName = licence.Member.SurnameOrCompanyName; value.ExistingLicenceHolder.TelCell = licence.Member.TelCell; value.ExistingLicenceHolder.TelFax = licence.Member.TelFax; value.ExistingLicenceHolder.TelPhone = licence.Member.TelPhone; value.ExistingLicenceHolder.IDTypeID = licence.Member.IDTypeID; value.ExistingLicenceHolder.IDTypeDescription = licence.Member.IDTypeDescription; value.ExistingLicenceHolder.PhysicalAddress = licence.Member.PhysicalAddress; value.ExistingLicenceHolder.PhysicalAddressCode = licence.Member.PhysicalAddressCode; value.ExistingLicenceHolder.PostalAddress = licence.Member.PostalAddress; value.ExistingLicenceHolder.PostalAddressCode = licence.Member.PostalAddressCode; } //also set the new applicant's association to the original member's association value.Applicant.VerifiedAssociationID = precheckDetail.AssociationID; } else { if (licence.Member != null) { value.Applicant.Email = licence.Member.Email; value.Applicant.FirstNames = licence.Member.FirstNames; value.Applicant.IDNumberOrBusinessRegistrationNo = licence.Member.IDNumberOrBusinessRegistrationNo; value.Applicant.SARSIncomeTaxNumber = licence.Member.SARSIncomeTaxNumber; value.Applicant.SurnameOrCompanyName = licence.Member.SurnameOrCompanyName; value.Applicant.TelCell = licence.Member.TelCell; value.Applicant.TelFax = licence.Member.TelFax; value.Applicant.TelPhone = licence.Member.TelPhone; value.Applicant.IDTypeID = licence.Member.IDTypeID; value.Applicant.IDTypeDescription = licence.Member.IDTypeDescription; value.Applicant.PhysicalAddress = licence.Member.PhysicalAddress; value.Applicant.PhysicalAddressCode = licence.Member.PhysicalAddressCode; value.Applicant.PostalAddress = licence.Member.PostalAddress; value.Applicant.PostalAddressCode = licence.Member.PostalAddressCode; value.Applicant.VerifiedMemberID = licence.Member.MemberID; } value.Applicant.VerifiedAssociationID = precheckDetail.AssociationID; } #endregion #region licence detail value.ExistingLicence.OperatingLicenceNumber = licence.OperatingLicenceNumber; value.ExistingLicence.OperatingLicenceIssueDate = licence.DateOfIssue; value.ExistingLicence.OperatingLicenceIssuingBoard = "KZN Public Transport Licensing Board"; value.ExistingLicence.OperatingLicenceExpiryDate = licence.DateOfExpiry; #region vehicle if (!ApplicationRequestHelper.IsCovtype(value.ApplicationRequestTypeID) && value.ApplicationRequestTypeID != (short)Neo.Legitimate.Common.ApplicationRequestType.TemporaryReplacements) { var vehicle = new ApplicationVehicleDetail() { IsStillToBeAcquired = false, VehicleMakeID = (licence.Vehicle != null && licence.Vehicle.VehicleMakeID.HasValue) ? licence.Vehicle.VehicleMakeID : null, VehicleMakeDescription = (licence.Vehicle != null && licence.Vehicle.VehicleMake != null) ? licence.Vehicle.VehicleMake.Description : null, VehicleTypeID = (licence.Vehicle != null && licence.Vehicle.VehicleTypeID.HasValue) ? licence.Vehicle.VehicleTypeID : null, VIN = (licence.Vehicle != null) ? licence.Vehicle.VIN : null, EngineNumber = (licence.Vehicle != null) ? licence.Vehicle.EngineNumber : null, YearOfManufacture = (licence.Vehicle != null) ? licence.Vehicle.YearOfManufacture : null, VehicleRegistrationNumber = (licence.Vehicle != null) ? licence.Vehicle.RegistrationNumber : null, SeatedCapacity = (licence.Vehicle != null && licence.Vehicle.SeatedCapacity.HasValue) ? licence.Vehicle.SeatedCapacity.ToInt16() : null, StandingCapacity = (licence.Vehicle != null && licence.Vehicle.StandingCapacity.HasValue) ? licence.Vehicle.StandingCapacity.ToInt16() : null, GrossMass = (licence.Vehicle != null && licence.Vehicle.GrossMass.HasValue) ? licence.Vehicle.GrossMass.ToInt64() : null, VerifiedVehicleID = (licence.Vehicle != null) ? licence.Vehicle.VehicleID : (long?)null, CarryingCapacityID = (licence.Vehicle != null && licence.Vehicle.CarryingCapacityID.HasValue) ? licence.Vehicle.CarryingCapacityID : null }; value.Vehicle = vehicle; } else { var vehicle = new ApplicationExistingVehicleDetail() { IsStillToBeAcquired = false, VehicleMakeID = (licence.Vehicle != null && licence.Vehicle.VehicleMakeID.HasValue) ? licence.Vehicle.VehicleMakeID : null, VehicleMakeDescription = (licence.Vehicle != null && licence.Vehicle.VehicleMake != null) ? licence.Vehicle.VehicleMake.Description : null, VehicleTypeID = (licence.Vehicle != null && licence.Vehicle.VehicleTypeID.HasValue) ? licence.Vehicle.VehicleTypeID : null, VIN = (licence.Vehicle != null) ? licence.Vehicle.VIN : null, EngineNumber = (licence.Vehicle != null) ? licence.Vehicle.EngineNumber : null, YearOfManufacture = (licence.Vehicle != null) ? licence.Vehicle.YearOfManufacture : null, VehicleRegistrationNumber = (licence.Vehicle != null) ? licence.Vehicle.RegistrationNumber : null, SeatedCapacity = (licence.Vehicle != null && licence.Vehicle.SeatedCapacity.HasValue) ? licence.Vehicle.SeatedCapacity.ToInt16() : null, StandingCapacity = (licence.Vehicle != null && licence.Vehicle.StandingCapacity.HasValue) ? licence.Vehicle.StandingCapacity.ToInt16() : null, GrossMass = (licence.Vehicle != null && licence.Vehicle.GrossMass.HasValue) ? licence.Vehicle.GrossMass.ToInt64() : null, VerifiedVehicleID = (licence.Vehicle != null) ? licence.Vehicle.VehicleID : (long?)null, CarryingCapacityID = (licence.Vehicle != null && licence.Vehicle.CarryingCapacityID.HasValue) ? licence.Vehicle.CarryingCapacityID : null }; value.ExistingVehicle = vehicle; } #endregion #region service types foreach (var serviceType in serviceDetails) { //ensure only 'other' service type is kept if mode is other if (licence.ModeID == (short)Neo.Legitimate.Common.Mode.Other) { if (serviceType.ServiceTypeID == (short)Neo.Legitimate.Common.ServiceType.Other) { value.Services.Add(new ApplicationServiceDetail() { ServiceTypeID = serviceType.ServiceTypeID, ServiceTypeDescription = serviceType.ServiceTypeDescription }); } } else { value.Services.Add(new ApplicationServiceDetail() { ServiceTypeID = serviceType.ServiceTypeID, ServiceTypeDescription = serviceType.ServiceTypeDescription }); } } #endregion #region Mode var mode = legitimateDb.GetMode(licence.ModeID); if (mode != null) { value.Mode.ModeID = licence.ModeID; value.Mode.ModeDescription = mode.Description; } #endregion #region routes if (value.ApplicationRequestTypeID != (short)Neo.Legitimate.Common.ApplicationRequestType.TemporarySpecialLicence) { foreach (var route in routes) { var origin = (route.OriginPlaceID.HasValue) ? legitimateDb.GetVerifiedPlace(route.OriginPlaceID.Value).Description : ""; var destination = (route.DestinationPlaceID.HasValue) ? legitimateDb.GetVerifiedPlace(route.DestinationPlaceID.Value).Description : ""; var routeName = route.RouteName; if (!String.IsNullOrWhiteSpace(routeName)) { short appRouteStatusId = 2; //default to awaiting verification if (route.RouteStatusID == 1) //active { appRouteStatusId = 3; //verfied } value.Routes.Add(new ApplicationRouteDetail() { ApplicationRouteDetailStatusID = appRouteStatusId, BoardRouteNumber = route.BoardRouteNumber, NationalRouteNumber = route.NationalRouteNumber, RouteDescription = route.Description, RouteLength = route.RouteLength, VerifiedRouteID = route.RouteID, RouteDestination = destination, RouteOrigin = origin, RouteChangeTypeID = (short)RouteChangeTypeEnum.ExistingRouteOnLicence }); } } } #endregion #endregion } else { if (ApplicationRequestHelper.IsTransferType(value.ApplicationRequestTypeID)) { #region Existing Member details if (applicant != null) { value.ExistingLicenceHolder.Email = (string)applicant.Element("emailaddress"); value.ExistingLicenceHolder.FirstNames = (string)applicant.Element("firstname"); value.ExistingLicenceHolder.IDNumberOrBusinessRegistrationNo = (string)applicant.Element("idnumber"); value.ExistingLicenceHolder.SARSIncomeTaxNumber = (string)applicant.Element("incometaxnumber"); value.ExistingLicenceHolder.SurnameOrCompanyName = (string)applicant.Element("lastname"); value.ExistingLicenceHolder.TelCell = (string)applicant.Element("cellnumber"); value.ExistingLicenceHolder.TelFax = (string)applicant.Element("faxnumber"); value.ExistingLicenceHolder.TelPhone = (string)applicant.Element("telephonenumber"); if (applicant.Element("idtypeid") != null) { short idType = 0; if (!short.TryParse(applicant.Element("idtypeid").ToString(), out idType)) { idType = 1; } var idTypeRecord = Context.IDTypes.Where(a => a.OlasID == idType).FirstOrDefault(); if (idTypeRecord != null) { value.ExistingLicenceHolder.IDTypeID = idTypeRecord.IDTypeID; value.ExistingLicenceHolder.IDTypeDescription = idTypeRecord.Description; } else { value.ExistingLicenceHolder.IDTypeDescription = (string)applicant.Element("idtypedescription"); } } if (applicant.Element("physicaladdress") != null) { var physicalAddress = (string)applicant.Element("physicaladdress").Element("addressline1") + "\r\n" + (string)applicant.Element("physicaladdress").Element("addressline2") + "\r\n" + (string)applicant.Element("physicaladdress").Element("addressline3") + "\r\n" + (string)applicant.Element("physicaladdress").Element("province"); value.ExistingLicenceHolder.PhysicalAddress = (string.IsNullOrWhiteSpace(physicalAddress)) ? "" : physicalAddress; value.ExistingLicenceHolder.PhysicalAddressCode = (string)applicant.Element("physicaladdress").Element("pocode"); } if (applicant.Element("postaladdress") != null) { var postalAddress = (string)applicant.Element("postaladdress").Element("addressline1") + "\r\n" + (string)applicant.Element("postaladdress").Element("addressline2") + "\r\n" + (string)applicant.Element("postaladdress").Element("addressline3") + "\r\n" + (string)applicant.Element("postaladdress").Element("province"); value.ExistingLicenceHolder.PostalAddress = (string.IsNullOrWhiteSpace(postalAddress)) ? "" : postalAddress; value.ExistingLicenceHolder.PostalAddressCode = (string)applicant.Element("postaladdress").Element("pocode"); } } //also set the new applicant's association to the original member's association value.Applicant.VerifiedAssociationID = precheckDetail.AssociationID; #endregion } else { var idNumber = (string)applicant.Element("idnumber"); var verifiedmember = legitimateDb.GetVerifiedMember(idNumber); if (verifiedmember != null) { #region Populate applicant details based on verified data value.Applicant.Email = verifiedmember.Email; value.Applicant.FirstNames = verifiedmember.FirstNames; value.Applicant.IDNumberOrBusinessRegistrationNo = verifiedmember.IDNumberOrBusinessRegistrationNo; value.Applicant.SARSIncomeTaxNumber = verifiedmember.SARSIncomeTaxNumber; value.Applicant.SurnameOrCompanyName = verifiedmember.SurnameOrCompanyName; value.Applicant.TelCell = verifiedmember.TelCell; value.Applicant.TelFax = verifiedmember.TelFax; value.Applicant.TelPhone = verifiedmember.TelPhone; value.Applicant.IDTypeID = verifiedmember.IDTypeID; value.Applicant.IDTypeDescription = verifiedmember.IDTypeDescription; value.Applicant.PhysicalAddress = verifiedmember.PhysicalAddress; value.Applicant.PhysicalAddressCode = verifiedmember.PhysicalAddressCode; value.Applicant.PostalAddress = verifiedmember.PostalAddress; value.Applicant.PostalAddressCode = verifiedmember.PostalAddressCode; value.Applicant.VerifiedMemberID = verifiedmember.MemberID; value.Applicant.VerifiedAssociationID = precheckDetail.AssociationID; #endregion } else { #region Applicant Details if (applicant != null) { value.Applicant.Email = (string)applicant.Element("emailaddress"); value.Applicant.FirstNames = (string)applicant.Element("firstname"); value.Applicant.IDNumberOrBusinessRegistrationNo = (string)applicant.Element("idnumber"); value.Applicant.SARSIncomeTaxNumber = (string)applicant.Element("incometaxnumber"); value.Applicant.SurnameOrCompanyName = (string)applicant.Element("lastname"); value.Applicant.TelCell = (string)applicant.Element("cellnumber"); value.Applicant.TelFax = (string)applicant.Element("faxnumber"); value.Applicant.TelPhone = (string)applicant.Element("telephonenumber"); if (applicant.Element("idtypeid") != null) { short idType = 0; if (!short.TryParse(applicant.Element("idtypeid").ToString(), out idType)) { idType = 1; } var idTypeRecord = Context.IDTypes.Where(a => a.OlasID == idType).FirstOrDefault(); if (idTypeRecord != null) { value.Applicant.IDTypeID = idTypeRecord.IDTypeID; value.Applicant.IDTypeDescription = idTypeRecord.Description; } else { value.Applicant.IDTypeDescription = (string)applicant.Element("idtypedescription"); } } if (applicant.Element("businesstypeid") != null) { var businessType = (short)applicant.Element("businesstypeid"); var businessTypeRecord = Context.BusinessTypes.Where(a => a.OlasID == businessType).FirstOrDefault(); if (businessTypeRecord != null) { value.Applicant.BusinessTypeID = businessTypeRecord.BusinessTypeID; } } if (applicant.Element("physicaladdress") != null) { var physicalAddress = (string)applicant.Element("physicaladdress").Element("addressline1") + "\r\n" + (string)applicant.Element("physicaladdress").Element("addressline2") + "\r\n" + (string)applicant.Element("physicaladdress").Element("addressline3") + "\r\n" + (string)applicant.Element("physicaladdress").Element("province"); value.Applicant.PhysicalAddress = (string.IsNullOrWhiteSpace(physicalAddress)) ? "" : physicalAddress; value.Applicant.PhysicalAddressCode = (string)applicant.Element("physicaladdress").Element("pocode"); } if (applicant.Element("postaladdress") != null) { var postalAddress = (string)applicant.Element("postaladdress").Element("addressline1") + "\r\n" + (string)applicant.Element("postaladdress").Element("addressline2") + "\r\n" + (string)applicant.Element("postaladdress").Element("addressline3") + "\r\n" + (string)applicant.Element("postaladdress").Element("province"); value.Applicant.PostalAddress = (string.IsNullOrWhiteSpace(postalAddress)) ? "" : postalAddress; value.Applicant.PostalAddressCode = (string)applicant.Element("postaladdress").Element("pocode"); } } #region create verified member record from source data if (value.Applicant != null && !String.IsNullOrWhiteSpace(value.Applicant.IDNumberOrBusinessRegistrationNo)) { long? olasID = null; long? ltpsID = null; if (value.SourceApplicationID == (short)Common.SourceApplication.NTLIS) { olasID = (long)applicant.Element("personid"); } value.Applicant.VerifiedMemberID = Context.Pr_MemberUpsert(-1, null, null, value.Applicant.SurnameOrCompanyName, value.Applicant.FirstNames, value.Applicant.IDTypeID, value.Applicant.IDTypeDescription, value.Applicant.IDNumberOrBusinessRegistrationNo, value.Applicant.PostalAddress, value.Applicant.PostalAddressCode, value.Applicant.PhysicalAddress, value.Applicant.PhysicalAddressCode, value.Applicant.TelPhone, value.Applicant.TelCell, value.Applicant.TelFax, value.Applicant.Email, value.Applicant.SARSIncomeTaxNumber, value.Applicant.SARSReasonIfNoTaxNumber, value.Applicant.TradeName,value.Applicant.BusinessTypeID, olasID, ltpsID, userID, "Create record based on imported data", false).FirstOrDefault().Value.ToInt64(); } #endregion //also set the new applicant's association to the original member's association value.Applicant.VerifiedAssociationID = precheckDetail.AssociationID; #endregion } } #region licence detail value.ExistingLicence.OperatingLicenceNumber = (string)applicantLicence.Element("licencenumber"); value.ExistingLicence.OperatingLicenceIssueDate = (applicantLicence.Element("startdate") != null) ? applicantLicence.Element("startdate").Value.ToDateTime() : null; value.ExistingLicence.OperatingLicenceIssuingBoard = "KZN Public Transport Licensing Board"; if ((string)applicantLicence.Element("indefinite") != "1") { value.ExistingLicence.OperatingLicenceExpiryDate = (applicantLicence.Element("enddate") != null) ? applicantLicence.Element("enddate").Value.ToDateTime() : DateTime.Now; } else { value.ExistingLicence.OperatingLicenceExpiryDate = DateTime.Now; } #region vehicle if (!ApplicationRequestHelper.IsCovtype(value.ApplicationRequestTypeID) && value.ApplicationRequestTypeID != (short)Neo.Legitimate.Common.ApplicationRequestType.TemporaryReplacements) { var vehicle = new ApplicationVehicleDetail() { IsStillToBeAcquired = false, VehicleMakeDescription = (applicantVehicle != null && applicantVehicle.Element("vehiclemake") != null) ? (string)applicantVehicle.Element("vehiclemake") : "", VehicleRegistrationNumber = (applicantVehicle != null && applicantVehicle.Element("registrationnumber") != null) ? (string)applicantVehicle.Element("registrationnumber") : "", VIN = (applicantVehicle != null && applicantVehicle.Element("chassisnumber") != null) ? (string)applicantVehicle.Element("chassisnumber") : "", EngineNumber = (applicantVehicle != null && applicantVehicle.Element("enginenumber") != null) ? (string)applicantVehicle.Element("enginenumber") : "", SeatedCapacity = (applicantVehicle != null && applicantVehicle.Element("seatingcapacity") != null) ? applicantVehicle.Element("seatingcapacity").Value.ToInt16() : null, GrossMass = (applicantVehicle != null && applicantVehicle.Element("grossmass") != null) ? applicantVehicle.Element("grossmass").Value.ToInt64() : null, RoadworthyCertificateNumber = (applicantVehicle != null && applicantVehicle.Element("cofreferencenumber") != null) ? applicantVehicle.Element("cofreferencenumber").Value.ToString() : "", RoadworthyExpiryDate = (applicantVehicle != null && applicantVehicle.Element("cofexpiry") != null) ? applicantVehicle.Element("cofexpiry").Value.ToDateTime() : null }; value.Vehicle = vehicle; } else { var vehicle = new ApplicationExistingVehicleDetail() { IsStillToBeAcquired = false, VehicleMakeDescription = (applicantVehicle != null && applicantVehicle.Element("vehiclemake") != null) ? (string)applicantVehicle.Element("vehiclemake") : null, VehicleRegistrationNumber = (applicantVehicle != null && applicantVehicle.Element("registrationnumber") != null) ? (string)applicantVehicle.Element("registrationnumber") : null, VIN = (applicantVehicle != null && applicantVehicle.Element("chassisnumber") != null) ? (string)applicantVehicle.Element("chassisnumber") : null, EngineNumber = (applicantVehicle != null && applicantVehicle.Element("enginenumber") != null) ? (string)applicantVehicle.Element("enginenumber") : null, SeatedCapacity = (applicantVehicle != null && applicantVehicle.Element("seatingcapacity") != null) ? applicantVehicle.Element("seatingcapacity").Value.ToInt16() : null, GrossMass = (applicantVehicle != null && applicantVehicle.Element("grossmass") != null) ? applicantVehicle.Element("grossmass").Value.ToInt64() : null, RoadworthyCertificateNumber = (applicantVehicle != null && applicantVehicle.Element("cofreferencenumber") != null) ? applicantVehicle.Element("cofreferencenumber").Value.ToString() : "", RoadworthyExpiryDate = (applicantVehicle != null && applicantVehicle.Element("cofexpiry") != null) ? applicantVehicle.Element("cofexpiry").Value.ToDateTime() : null }; value.ExistingVehicle = vehicle; } #endregion #region service types //use the source application from precheck as nothing is received from ltps if (value.SourceApplicationID == (short)Common.SourceApplication.LTPS) { if (precheckDetail.ServiceTypeID.HasValue) { value.Services.Add(new ApplicationServiceDetail() { ServiceTypeID = precheckDetail.ServiceTypeID.Value, ServiceTypeDescription = "" }); } } foreach (var serviceType in applicantLicence.Descendants("servicetype")) { if (value.SourceApplicationID == (short)Common.SourceApplication.NTLIS) { var olasServiceTypeID = (serviceType.Element("licenceservicetypeid") != null) ? serviceType.Element("licenceservicetypeid").Value.ToInt32() : -1; short serviceTypeID; var serviceTypeDescription = ""; #region Calculate LG service type id from OLAS servie type switch (olasServiceTypeID) { case 4: /*Minibus taxi-type service*/ serviceTypeID = 2; serviceTypeDescription = "Unscheduled"; break; case 5: /*Charter service*/ serviceTypeID = 3; serviceTypeDescription = "Charter"; break; case 6: /*Metered taxi */ serviceTypeID = 2; serviceTypeDescription = "Unscheduled"; break; case 7: /*Staff service */ serviceTypeID = 5; serviceTypeDescription = "Staff"; break; case 8: /*Scheduled service */ serviceTypeID = 1; serviceTypeDescription = "Scheduled"; break; case 10: /*Unscheduled service */ serviceTypeID = 2; serviceTypeDescription = "Unscheduled"; break; case 13: /*Long distance service */ serviceTypeID = 2; serviceTypeDescription = "Unscheduled"; break; case 26: /*Tourist service*/ serviceTypeID = 4; serviceTypeDescription = "Tourist"; break; case 62: /*Educational Transport*/ serviceTypeID = 6; serviceTypeDescription = "Scholar"; break; case 63: /*Organised group*/ serviceTypeID = 1; serviceTypeDescription = "Scheduled"; break; default: serviceTypeID = 8; //Unmapped type, so set to Other. serviceTypeDescription = (string)serviceType.Element("servicetypedescription"); break; } #endregion #region ensure only 'other' service type is kept if mode is other if (precheckDetail.ModeID == (short)Neo.Legitimate.Common.Mode.Other) { if (serviceTypeID == (short)Neo.Legitimate.Common.ServiceType.Other) { value.Services.Add(new ApplicationServiceDetail() { ServiceTypeID = serviceTypeID, ServiceTypeDescription = serviceTypeDescription }); } } else { value.Services.Add(new ApplicationServiceDetail() { ServiceTypeID = serviceTypeID, ServiceTypeDescription = serviceTypeDescription }); } #endregion //do not copy routes for special licence as a new route will be used for this type if (value.ApplicationRequestTypeID != (short)Neo.Legitimate.Common.ApplicationRequestType.TemporarySpecialLicence) { #region Add routes from xml foreach (var route in serviceType.Descendants("route")) { var routeName = ((string)route.Element("routename")).Replace("&", ""); var routedescription = (route.Element("routedescription") != null) ? (string)route.Element("routedescription") : ""; if (!String.IsNullOrWhiteSpace(routeName) && !routedescription.ToLower().Replace(" ", "").StartsWith("conditions/restrictions") && !routedescription.ToLower().StartsWith("conditions:") && !routedescription.ToLower().Contains("OOOOOOO")) { var olasID = route.Element("routeid").Value.ToInt32(); var nationalRouteNumber = (string)route.Element("nationalroutenumber"); var boardroutenumber = (string)route.Element("boardroutenumber"); var routelengthkm = (route.Element("routelengthkm") != null) ? route.Element("routelengthkm").Value.ToInt32() : 0; var origin = ""; var destination = ""; var verifiedRouteIDActive = Context.Pr_RouteSearch(-1, -1, nationalRouteNumber, boardroutenumber, -1, null, 1, null, -1, -1).ToList(); if (verifiedRouteIDActive == null || verifiedRouteIDActive.Count() == 0) { var verifiedRouteID = Context.Pr_RouteUpsert(-1, 1, routeName, routedescription, null, null, nationalRouteNumber, boardroutenumber, routelengthkm, null, null, olasID, null, userID, "Create record based on imported data", false).FirstOrDefault().Value.ToInt32(); var verifiedRoute = this.GetVerifiedRoute(verifiedRouteID.Value); if (verifiedRoute.RouteStatusID != (short)RouteStatusEnum.Cancelled) { //create the required places var sequence = 1; foreach (var node in route.Descendants("routenode")) { var olasPlaceID = (node.Element("placeid") != null) ? node.Element("placeid").Value.ToInt32() : -1; var placeDescription = (string)node.Element("placedescription"); var placeTypeID = (node.Element("placetypeid") != null) ? node.Element("placetypeid").Value.ToInt16() : -1; //1 on 1 mapping between olas and legitimate var waypointTypeID = (node.Element("waypointtypeid") != null) ? node.Element("waypointtypeid").Value.ToInt16() : -1; //1 on 1 mapping between olas and legitimate var verifiedPlaceID = Context.Pr_PlaceUpsert(-1, placeDescription, placeTypeID, olasPlaceID, null, userID, "Create record based on imported data", false).FirstOrDefault().Value.ToInt32(); var verifiedRouteNodeID = Context.Pr_RouteNodeAdd(verifiedRouteID, verifiedPlaceID, waypointTypeID, null, sequence, userID); if (waypointTypeID == 1) { origin = placeDescription; } if (waypointTypeID == 4) { destination = placeDescription; } sequence++; } short appRouteStatusId = 2; //default to awaiting verification if (verifiedRoute.RouteStatusID == 1) //active { appRouteStatusId = 3; //verfied } value.Routes.Add(new ApplicationRouteDetail() { ApplicationRouteDetailStatusID = appRouteStatusId, BoardRouteNumber = boardroutenumber, NationalRouteNumber = nationalRouteNumber, RouteDescription = routedescription, RouteDestination = destination, RouteOrigin = origin, RouteLength = routelengthkm, VerifiedRouteID = verifiedRouteID, RouteChangeTypeID = (short)RouteChangeTypeEnum.ExistingRouteOnLicence }); } } else { var verifiedRouteDetail = verifiedRouteIDActive.FirstOrDefault(); value.Routes.Add(new ApplicationRouteDetail() { ApplicationRouteDetailStatusID = 3,//verified BoardRouteNumber = verifiedRouteDetail.BoardRouteNumber, NationalRouteNumber = verifiedRouteDetail.NationalRouteNumber, RouteDescription = verifiedRouteDetail.RouteDescription, RouteDestination = verifiedRouteDetail.RouteDestination, RouteOrigin = verifiedRouteDetail.RouteOrigin, RouteLength = verifiedRouteDetail.RouteLength, VerifiedRouteID = verifiedRouteDetail.RouteID, RouteChangeTypeID = (short)RouteChangeTypeEnum.ExistingRouteOnLicence }); } } } #endregion } } } #endregion #region add routes from association as we don't receive routes for ltps if (value.IsLtpsConversion.Value == true && value.Routes.Count()==0) { if (precheckDetail.AssociationID.HasValue) { var associationRoutes = legitimateDb.GetVerifiedAssociationRoutes(precheckDetail.AssociationID.Value); foreach (var route in associationRoutes) { var origin = (route.OriginPlaceID.HasValue) ? legitimateDb.GetVerifiedPlace(route.OriginPlaceID.Value).Description : ""; var destination = (route.DestinationPlaceID.HasValue) ? legitimateDb.GetVerifiedPlace(route.DestinationPlaceID.Value).Description : ""; var routeName = route.RouteName; if (!String.IsNullOrWhiteSpace(routeName)) { short appRouteStatusId = 2; //default to awaiting verification if (route.RouteStatusID == 1) //active { appRouteStatusId = 3; //verfied } value.Routes.Add(new ApplicationRouteDetail() { ApplicationRouteDetailStatusID = appRouteStatusId, BoardRouteNumber = route.BoardRouteNumber, NationalRouteNumber = route.NationalRouteNumber, RouteDescription = route.Description, RouteLength = route.RouteLength, VerifiedRouteID = route.RouteID, RouteDestination = destination, RouteOrigin = origin, RouteChangeTypeID = (short)RouteChangeTypeEnum.ExistingRouteOnLicence }); } } } } #endregion #endregion } } } #endregion #region default mode to precheck mode if not specified (for OLAS OLs and permits) if (!value.Mode.ModeID.HasValue) { if (precheckDetail.ModeID.HasValue) { var mode = legitimateDb.GetMode(precheckDetail.ModeID.Value); if (mode != null) { value.Mode.ModeID = mode.ModeID; value.Mode.ModeDescription = mode.Description; } } } #endregion #region default vehicle to existing vehicle for cov types if (ApplicationRequestHelper.IsCovtype(value.ApplicationRequestTypeID)) { value.Vehicle.IsStillToBeAcquired = false; value.ExistingVehicle.IsStillToBeAcquired = false; } #endregion return value; } #endregion #region LogPrintReceipt public bool LogPrintReceipt(long receiptDetailID, long userID, out string errorMessage) { errorMessage = ""; try { Context.Pr_LogPrintReceipt(receiptDetailID, userID); } catch (Exception ex) { errorMessage = ex.Message; } return String.IsNullOrWhiteSpace(errorMessage); } #endregion #region SearchApplicationRequestDetails public IEnumerable SearchApplicationRequestDetails(long applicationRequestDetailID, short workflowInstanceStatusID, bool includeActiveTasksOnly, long userID, string workflowSteps) { return Context.Pr_ApplicationRequestDetailsSearch(applicationRequestDetailID, workflowInstanceStatusID, workflowSteps, includeActiveTasksOnly, -1, false, userID).ToList(); } #endregion #region SearchNewRouteRequestDetails public IEnumerable SearchNewRouteRequestDetails(long newRouteRequestDetailID, short workflowInstanceStatusID, string description) { return Context.Pr_NewRouteRequestDetailsSearch(newRouteRequestDetailID, workflowInstanceStatusID, description).ToList(); } #endregion #region GetPrecheckDetail public PrecheckDetail GetPrecheckDetail(long precheckDetialID) { return Context.PrecheckDetails.Where(a => a.PrecheckDetailID == precheckDetialID).FirstOrDefault(); } #endregion #region CheckSecurity public void CheckSecurity(long applicationRequestDetailID, string action, long userID) { Context.Pr_ApplicationCheckSecurity(applicationRequestDetailID, action, userID); } #endregion #region GetSourceApplicationFromSourceSystem public Fn_GetSourceApplicationRecordsFromSourceSystem_Result GetSourceApplicationFromSourceSystem(string olPermitNumber, short sourceApplicationID, short modeID, bool includeActiveOnly, bool includeLastExpiredIfNoActiveFound) { return Context.Fn_GetSourceApplicationRecordsFromSourceSystem(olPermitNumber, sourceApplicationID, modeID, includeActiveOnly, includeLastExpiredIfNoActiveFound).FirstOrDefault(); } #endregion #region PerformPrecheck public void PerformPrecheck(long precheckDetailID, short applicationRequestTypeID, short sourceApplicationID, string olPermitNumber, string iDNumber, long? associationID, string vehicleRegistrationNumber, bool checkData, long userID, short? modeID, short? serviceTypeID, bool calledFromSave) { Context.Pr_PrecheckDetailsUpsert(precheckDetailID, applicationRequestTypeID, sourceApplicationID, null, olPermitNumber, iDNumber, null, vehicleRegistrationNumber, associationID, checkData, null, null, null, null, userID, modeID, serviceTypeID, calledFromSave); } #endregion #region Remove Route //public string RemoveRoute(long applicationLicenceID, long applicationLicenceRouteDetailID, long userID) //{ // Context.pr_appl //} #endregion } }