using System; using System.Collections.Generic; using System.Data; using System.Data.SqlClient; using System.Linq; using System.Text; using System.Web; using System.Xml; namespace Neo.Legitimate.LTPSWebService.Data { internal class LTPSMember : LTPSEntity { private const string DuplicateVehicleError = "Vehicle is already registered in RAS or to an operating licence."; private const int MemberStatusID = 7; private const string MemberStatusReason = "KZN LTPS Migration"; public const int EntityTypeID = 4; public const int VehicleStatusID = 1; private const int DefaultIDType = 1; private const int DefaultMemberTypeID = 5; private const int DefaultSeatingCapacity = 15; private const int DefaultVehicleTypeID = 9; private const int DefaultVehicleMakeID = 4; private XmlElement _vehicle; private int AssociationID { get { return LTPSDataBroker.ParseInt(base.Root.ParentNode.Attributes["associationID"].Value, -1); } } private int ProvinceID { get { return LTPSDataBroker.ParseInt(base.Root.ParentNode.Attributes["provinceID"].Value, 4); } } public int VehicleID { get { return LTPSDataBroker.ParseInt(base.GetAttribute(this._vehicle, "vehicleID"), -1); } set { base.SetAttribute(this._vehicle, "vehicleID", value.ToString()); } } public VehicleService[] VehicleServices { get { int i = this._vehicle.ChildNodes.Count; VehicleService[] services = new VehicleService[i]; for (int j = 0; j < i; j++) { XmlElement serviceType = this._vehicle.ChildNodes[j] as XmlElement; StringBuilder routesCsv = new StringBuilder(); int k = serviceType.ChildNodes.Count; for (int l = 0; l < k; l++) { XmlElement route = serviceType.ChildNodes[l] as XmlElement; if (routesCsv.Length > 0) { routesCsv.Append(','); } routesCsv.Append(base.GetAttribute(route, "routeID")); } services[j].ServiceTypeID = LTPSDataBroker.ParseInt(base.GetAttribute(serviceType, "serviceTypeID"), -1); services[j].ServiceConditions = base.GetAttribute(serviceType, "serviceConditions"); services[j].ServiceRoutes = routesCsv.ToString(); } return services; } } public int VehicleSeatingCapacity { get { return LTPSDataBroker.ParseInt(base.GetAttribute(this._vehicle, "seatingCapacity"), 15); } } public string VehicleRegNumber { get { return base.GetAttribute(this._vehicle, "regNumber"); } } public string VehicleChassisNumber { get { return base.GetAttribute(this._vehicle, "chassisNumber"); } } public int VehicleCount { get { return base.Root.ChildNodes.Count; } } public LTPSMember(XmlElement root) : base(root) { this._vehicle = (root.ChildNodes[0] as XmlElement); } protected override bool Exists() { bool exists = false; StringBuilder sql = new StringBuilder(); using (SqlConnection connection = new SqlConnection(base.ConnectionString)) { using (SqlCommand command = connection.CreateCommand()) { connection.Open(); sql.Append(" SELECT P.pkiPersonId, P.sFirstName, P.sLastName, P.sIDNum, P.sBusRegistrationNum, P.fkiIDType, P.sTradeName, P.sTelephoneNum, P.sFaxNum, P.sCellNum, P.sEMailAddress, P.fkiAddressDomicilliumId,"); sql.Append(" PH.pkiAddressId physicalAddressID, PH.sAddressLine1 physicalAddressLine1, PH.sAddressLine2 physicalAddressLine2, PH.sAddressLine3 physicalAddressLine3, PH.sPOCode physicalAddressCode,"); sql.Append(" PO.pkiAddressId postalAddressID, PO.sAddressLine1 postalAddressLine1, PO.sAddressLine2 postalAddressLine2, PO.sAddressLine3 postalAddressLine3, PO.sPOCode postalAddressCode,"); sql.Append(" M.pkiMemberId, M.fkiMemberTypeId, M.fkiMemberStatusId, M.sTaxNum, M.dtMemberApplicationDate, M.sMemberRegistrationNum, AM.fkiAssocId AS AssocId"); sql.Append(" FROM tblPerson P INNER JOIN "); sql.Append(" tblAddress PH ON PH.pkiAddressId = P.fkiPhysicalAddressId INNER JOIN "); sql.Append(" tblAddress PO ON PO.pkiAddressId = P.fkiPOAddressId LEFT OUTER JOIN "); sql.Append(" tblMember M ON P.pkiPersonId = M.fkiPersonId LEFT OUTER JOIN "); sql.Append(" tlkAssociationMember AM ON M.pkiMemberID = AM.fkiMemberId"); sql.Append(" WHERE P.sIDNum = @IDNumber AND P.fkiIDType = @IDType AND M.bActive = 1 AND P.bActive = 1"); sql.Append(" ORDER BY P.pkiPersonId DESC, M.pkiMemberID DESC, AM.fkiAssocId DESC"); command.CommandType = CommandType.Text; command.CommandText = sql.ToString(); command.Parameters.Clear(); command.Parameters.AddWithValue("@IDNumber", LTPSDataBroker.ParseDbString(base["idNumber"])); command.Parameters.AddWithValue("@IDType", LTPSDataBroker.ParseInt(base["idType"], 1)); using (SqlDataReader reader = command.ExecuteReader()) { int LTPSsociationID = this.AssociationID; while (reader.Read()) { if (!exists) { exists = true; base["personID"] = LTPSDataBroker.ToString(reader["pkiPersonId"]); base["idType"] = LTPSDataBroker.ToString(reader["fkiIDType"]); base["idNumber"] = LTPSDataBroker.ToString(reader["sIDNum"]); base["firstName"] = LTPSDataBroker.ToString(reader["sFirstName"]); base["lastName"] = LTPSDataBroker.ToString(reader["sLastName"]); base["initials"] = LTPSDataBroker.ParseInitials(base["firstName"], base["lastName"]); base["tradeName"] = LTPSDataBroker.ToString(reader["sTradeName"]); base["telNumber"] = LTPSDataBroker.ToString(reader["sTelephoneNum"]); base["faxNumber"] = LTPSDataBroker.ToString(reader["sFaxNum"]); base["cellNumber"] = LTPSDataBroker.ToString(reader["sCellNum"]); base["email"] = LTPSDataBroker.ToString(reader["sEMailAddress"]); base["physicalAddressID"] = LTPSDataBroker.ToString(reader["physicalAddressID"]); base["physicalAddressLine1"] = LTPSDataBroker.ToString(reader["physicalAddressLine1"]); base["physicalAddressLine2"] = LTPSDataBroker.ToString(reader["physicalAddressLine2"]); base["physicalAddressLine3"] = LTPSDataBroker.ToString(reader["physicalAddressLine3"]); base["physicalAddressCode"] = LTPSDataBroker.ToString(reader["physicalAddressCode"]); base["postalAddressID"] = LTPSDataBroker.ToString(reader["postalAddressID"]); base["postalAddressLine1"] = LTPSDataBroker.ToString(reader["postalAddressLine1"]); base["postalAddressLine2"] = LTPSDataBroker.ToString(reader["postalAddressLine2"]); base["postalAddressLine3"] = LTPSDataBroker.ToString(reader["postalAddressLine3"]); base["postalAddressCode"] = LTPSDataBroker.ToString(reader["postalAddressCode"]); base["postalAddressIsDomicilium"] = LTPSDataBroker.ToString(base.PostalAddressIsDomicilium(reader["fkiAddressDomicilliumId"], reader["physicalAddressID"], reader["postalAddressID"])); } if (LTPSsociationID == -1) { break; } object value = reader["AssocId"]; int associationID = LTPSDataBroker.IsNull(value) ? -1 : ((int)value); if (associationID == LTPSsociationID) { base["associationID"] = associationID.ToString(); base["memberID"] = LTPSDataBroker.ToString(reader["pkiMemberId"]); base["memberStatusID"] = LTPSDataBroker.ToString(reader["fkiMemberStatusId"]); base["memberTypeID"] = LTPSDataBroker.ToString(reader["fkiMemberTypeId"]); base["taxNumber"] = LTPSDataBroker.ToString(reader["sTaxNum"]); base["applicationDate"] = LTPSDataBroker.ToDateString(reader["dtMemberApplicationDate"]); base["memberRegNumber"] = LTPSDataBroker.ToString(reader["sMemberRegistrationNum"]); break; } } } string regNumber = base.GetAttribute(this._vehicle, "regNumber"); string chassisNumber = base.GetAttribute(this._vehicle, "chassisNumber"); string reason; if (this.VehicleLinkedToLicence(command, regNumber, chassisNumber, out reason)) { base.SetStatus(this._vehicle, 2, reason); } else { bool vehicleExists = false; sql.Length = 0; sql.Append(" SELECT P.pkiPersonID, P.sIDNum, M.pkiMemberID, AM.fkiAssocId, V.*, "); sql.Append(" CASE WHEN PM.pkiPermitId IS NOT NULL then 'Permit' WHEN L.pkiLicenceId IS NOT NULL then 'Licence' ELSE 'Unlinked' END as 'LicType' "); sql.Append(" FROM tblVehicle V INNER JOIN "); sql.Append(" tlkHumanEntity HE ON V.fkiRASOwnerEntityId = HE.pkiEntityId AND V.bActive = 1 LEFT OUTER JOIN "); sql.Append("\ttblMember M ON M.pkiMemberID = HE.mfkiRealEntityId LEFT OUTER JOIN "); sql.Append(" tblPerson P ON M.fkipersonID = P.pkiPersonID LEFT OUTER JOIN "); sql.Append(" tlkAssociationMember AM ON M.pkiMemberID = AM.fkiMemberId LEFT OUTER JOIN "); sql.Append(" tblPermit PM ON PM.pkiPermitID = V.fkiPermitID AND PM.fkiPermitStatusId NOT IN (8, 10, 11, 13, 14, 15, 17, 18) LEFT OUTER JOIN "); sql.Append(" tblLicence L ON L.pkiLicenceID = V.fkiLicenceID\tAND L.fkiLicenceStatusId NOT IN (5, 7, 9, 10, 11, 13, 14) "); sql.Append(" WHERE (V.sRegistrationNum = @RegNumber OR V.sChassisNum = @ChassisNumber) "); command.CommandType = CommandType.Text; command.CommandText = sql.ToString(); command.Parameters.Clear(); command.Parameters.AddWithValue("@RegNumber", regNumber); command.Parameters.AddWithValue("@ChassisNumber", chassisNumber); using (SqlDataReader reader2 = command.ExecuteReader()) { if (reader2.Read()) { base.SetAttribute(this._vehicle, "vehicleID", LTPSDataBroker.ToString(reader2["pkiVehicleId"])); base.SetAttribute(this._vehicle, "seatingCapacity", LTPSDataBroker.ToString(reader2["iSeatingCapacity"])); base.SetAttribute(this._vehicle, "engineNumber", LTPSDataBroker.ToString(reader2["sEngineNum"])); base.SetAttribute(this._vehicle, "grossMass", LTPSDataBroker.ToString(reader2["dGrossMass"])); base.SetAttribute(this._vehicle, "vehicleTypeID", LTPSDataBroker.ToString(reader2["fkiVehicleTypeId"])); base.SetAttribute(this._vehicle, "vehicleMakeID", LTPSDataBroker.ToString(reader2["fkiVehicleMakeId"])); base.SetAttribute(this._vehicle, "vehicleMakeName", LTPSDataBroker.ToString(reader2["sMake"])); base.SetAttribute(this._vehicle, "licenceType", LTPSDataBroker.ToString(reader2["LicType"])); base.SetAttribute(this._vehicle, "personID", LTPSDataBroker.ToString(reader2["pkiPersonID"])); base.SetAttribute(this._vehicle, "personIDNumber", LTPSDataBroker.ToString(reader2["sIDNum"])); base.SetAttribute(this._vehicle, "memberID", LTPSDataBroker.ToString(reader2["pkiMemberID"])); base.SetAttribute(this._vehicle, "associationID", LTPSDataBroker.ToString(reader2["fkiAssocId"])); vehicleExists = true; } } if (vehicleExists) { int vehiclePersonID = LTPSDataBroker.ParseInt(base.GetAttribute(this._vehicle, "personID"), -1); if (vehiclePersonID == -1) { base.SetStatus(this._vehicle, 2, "Vehicle registered to unknown person."); } else { int personID = LTPSDataBroker.ParseInt(base["personID"], -1); if (personID == -1) { base.SetStatus(this._vehicle, 2, "Vehicle registered to different person: " + base.GetAttribute(this._vehicle, "personIDNumber")); } else { if (personID == vehiclePersonID) { base.SetStatus(this._vehicle, 1, VerificationStatus.Exists.ToString()); } else { base.SetStatus(this._vehicle, 2, "Vehicle registered to different person: " + base.GetAttribute(this._vehicle, "personIDNumber")); } } } } else { base.SetStatus(this._vehicle, 0, VerificationStatus.New.ToString()); } } } } return exists; } protected override void Read() { } public override void Create(SqlCommand command) { int physicalAddressID; int postalAddressID; int domicilliumAddressID; this.SaveAddress(command, out physicalAddressID, out postalAddressID, out domicilliumAddressID); command.CommandType = CommandType.StoredProcedure; command.CommandText = "spCOMMONPersonI"; command.Parameters.Clear(); command.Parameters.AddWithValue("@sIDNum", LTPSDataBroker.ParseDbString(base["idNumber"])); command.Parameters.AddWithValue("@fkiIDType", LTPSDataBroker.ParseInt(base["idType"], 1)); command.Parameters.AddWithValue("@sFirstName", LTPSDataBroker.ParseDbString(base["firstName"])); command.Parameters.AddWithValue("@sLastName", LTPSDataBroker.ParseDbString(base["lastName"])); command.Parameters.AddWithValue("@sInitials", LTPSDataBroker.ParseDbString(base["initials"])); command.Parameters.AddWithValue("@sTradeName", LTPSDataBroker.ParseDbString(base["tradeName"])); command.Parameters.AddWithValue("@fkiPOAddressId", postalAddressID); command.Parameters.AddWithValue("@fkiPhysicalAddressId", physicalAddressID); command.Parameters.AddWithValue("@fkiAddressDomicilliumId", domicilliumAddressID); command.Parameters.AddWithValue("@sTelephoneNum", LTPSDataBroker.ParseDbString(base["telNumber"])); command.Parameters.AddWithValue("@sFaxNum", LTPSDataBroker.ParseDbString(base["faxNumber"])); command.Parameters.AddWithValue("@sCellNum", LTPSDataBroker.ParseDbString(base["cellNumber"])); command.Parameters.AddWithValue("@sEMailAddress", LTPSDataBroker.ParseDbString(base["email"])); command.Parameters.AddWithValue("@sAuditInfo", base.UserAudit); int personID = Convert.ToInt32(command.ExecuteScalar()); int memberID = this.CreateMember(command, personID); string memberRegNumber = this.RegisterMember(command, memberID); base["personID"] = LTPSDataBroker.ToString(personID); base["memberID"] = LTPSDataBroker.ToString(memberID); base["memberRegNumber"] = memberRegNumber; this.SaveVehicle(command); this.Uplift(command); base.UpdateStatus(0, MigrationStatus.Created.ToString()); } public override void Update(SqlCommand command) { int physicalAddressID; int postalAddressID; int domicilliumAddressID; this.SaveAddress(command, out physicalAddressID, out postalAddressID, out domicilliumAddressID); int personID = LTPSDataBroker.ParseInt(base["personID"], -1); command.CommandType = CommandType.StoredProcedure; command.CommandText = "spCOMMONPersonU"; command.Parameters.Clear(); command.Parameters.AddWithValue("@pkiPersonId", personID); command.Parameters.AddWithValue("@sIDNum", LTPSDataBroker.ParseDbString(base["idNumber"])); command.Parameters.AddWithValue("@fkiIDType", LTPSDataBroker.ParseInt(base["idType"], 1)); command.Parameters.AddWithValue("@sFirstName", LTPSDataBroker.ParseDbString(base["firstName"])); command.Parameters.AddWithValue("@sLastName", LTPSDataBroker.ParseDbString(base["lastName"])); command.Parameters.AddWithValue("@sInitials", LTPSDataBroker.ParseDbString(base["initials"])); command.Parameters.AddWithValue("@sTradeName", LTPSDataBroker.ParseDbString(base["tradeName"])); command.Parameters.AddWithValue("@fkiPOAddressId", postalAddressID); command.Parameters.AddWithValue("@fkiPhysicalAddressId", physicalAddressID); command.Parameters.AddWithValue("@fkiAddressDomicilliumId", domicilliumAddressID); command.Parameters.AddWithValue("@sTelephoneNum", LTPSDataBroker.ParseDbString(base["telNumber"])); command.Parameters.AddWithValue("@sFaxNum", LTPSDataBroker.ParseDbString(base["faxNumber"])); command.Parameters.AddWithValue("@sCellNum", LTPSDataBroker.ParseDbString(base["cellNumber"])); command.Parameters.AddWithValue("@sEMailAddress", LTPSDataBroker.ParseDbString(base["email"])); command.Parameters.AddWithValue("@sAuditInfo", base.UserAudit); command.ExecuteNonQuery(); int memberID = LTPSDataBroker.ParseInt(base["memberID"], -1); if (memberID == -1) { memberID = this.CreateMember(command, personID); base["memberID"] = LTPSDataBroker.ToString(memberID); base["memberRegNumber"] = this.RegisterMember(command, memberID); } else { command.CommandType = CommandType.StoredProcedure; command.CommandText = "spMemberDetailU"; command.Parameters.Clear(); command.Parameters.AddWithValue("@pkiMemberId", memberID); command.Parameters.AddWithValue("@fkiMemberTypeId", LTPSDataBroker.ParseInt(base["memberTypeID"], 5)); command.Parameters.AddWithValue("@fkiPersonId", personID); command.Parameters.AddWithValue("@dtMemberApplicationDate", LTPSDataBroker.ParseDbDate(base["applicationDate"])); command.Parameters.AddWithValue("@sTaxNum", LTPSDataBroker.ParseDbString(base["taxNumber"])); command.Parameters.AddWithValue("@sAuditInfo", base.UserAudit); command.ExecuteNonQuery(); } this.SaveVehicle(command); this.Uplift(command); base.UpdateStatus(1, MigrationStatus.Updated.ToString()); } private void Uplift(SqlCommand command) { command.CommandType = CommandType.StoredProcedure; command.CommandText = "spMemberStatusU"; command.Parameters.Clear(); command.Parameters.AddWithValue("@pkiMemberId", LTPSDataBroker.ParseInt(base["memberID"], -1)); command.Parameters.AddWithValue("@fkiMemberStatusId", 7); command.Parameters.AddWithValue("@sMemberStatusOverrideReason", "Western Cape LTPS Migration"); command.Parameters.AddWithValue("@sAuditInfo", base.UserAudit); command.ExecuteNonQuery(); } private void SaveVehicle(SqlCommand command) { try { bool successful = false; VerificationStatus verStatus = (VerificationStatus)base.GetStatus(this._vehicle); if (verStatus != VerificationStatus.Error) { int memberID = LTPSDataBroker.ParseInt(base["memberID"], -1); int vehicleMemberID = LTPSDataBroker.ParseInt(base.GetAttribute(this._vehicle, "memberID"), -1); string regNumber = base.GetAttribute(this._vehicle, "regNumber"); if (memberID != vehicleMemberID) { this.MoveVehicle(command, regNumber, vehicleMemberID, memberID); } Action action = (verStatus == VerificationStatus.New) ? Action.Add : Action.Update; int vehicleID = LTPSDataBroker.ParseInt(base.GetAttribute(this._vehicle, "vehicleID"), -1); command.CommandType = CommandType.StoredProcedure; command.CommandText = "spMemberVehicleUpdate"; command.Parameters.Clear(); command.Parameters.AddWithValue("@iMemberId", LTPSDataBroker.ParseInt(base["memberID"], -1)); command.Parameters.AddWithValue("@iVehicleId", vehicleID); command.Parameters.AddWithValue("@sRegistrationNum", regNumber); command.Parameters.AddWithValue("@sChassisNum", LTPSDataBroker.ParseDbString(base.GetAttribute(this._vehicle, "chassisNumber"))); command.Parameters.AddWithValue("@iSeatingCapacity", LTPSDataBroker.ParseInt(base.GetAttribute(this._vehicle, "seatingCapacity"), 15)); command.Parameters.AddWithValue("@iActionId", (int)action); command.Parameters.AddWithValue("@sAuditInfo", base.UserAudit); successful = (Convert.ToInt32(command.ExecuteScalar()) > 0); if (successful) { if (action == Action.Add) { command.CommandType = CommandType.Text; command.CommandText = "SELECT MAX(pkiVehicleID) FROM tblVehicle"; command.Parameters.Clear(); vehicleID = Convert.ToInt32(command.ExecuteScalar()); base.SetAttribute(this._vehicle, "vehicleID", LTPSDataBroker.ToString(vehicleID)); } command.CommandType = CommandType.StoredProcedure; command.CommandText = "spLTPSVehicleNATISSubU"; command.Parameters.Clear(); command.Parameters.AddWithValue("@iVehicleId", vehicleID); command.Parameters.AddWithValue("@lVehicleType", LTPSDataBroker.ParseInt(base.GetAttribute(this._vehicle, "vehicleTypeID"), 9)); command.Parameters.AddWithValue("@lVehicleMake", LTPSDataBroker.ParseInt(base.GetAttribute(this._vehicle, "vehicleMakeID"), 4)); command.Parameters.AddWithValue("@sEngineNumber", base.GetAttribute(this._vehicle, "engineNumber")); command.Parameters.AddWithValue("@sGrossMass", LTPSDataBroker.ParseInt(base.GetAttribute(this._vehicle, "grossMass"), 0)); command.Parameters.AddWithValue("@sVMake", base.GetAttribute(this._vehicle, "vehicleMakeName")); command.Parameters.AddWithValue("@sAuditInfo", base.UserAudit); command.ExecuteNonQuery(); } } if (!successful) { base.SetStatus(this._vehicle, 2, "Vehicle is already registered in RAS or to an operating licence."); throw new Exception("Vehicle is already registered in RAS or to an operating licence."); } MigrationStatus migStatus = (verStatus == VerificationStatus.New) ? MigrationStatus.Created : MigrationStatus.Updated; base.SetStatus(this._vehicle, (int)migStatus, migStatus.ToString()); } catch (Exception ex) { base.SetStatus(this._vehicle, 2, LTPSDataBroker.ToString(ex)); throw ex; } } public int GetEntityID(SqlCommand command) { command.CommandType = CommandType.StoredProcedure; command.CommandText = "spReturnMemberEntity"; command.Parameters.Clear(); command.Parameters.AddWithValue("@MemberId", LTPSDataBroker.ParseInt(base["memberID"], -1)); return Convert.ToInt32(command.ExecuteScalar()); } private int CreateMember(SqlCommand command, int personID) { command.CommandType = CommandType.StoredProcedure; command.CommandText = "spMemberDetailI"; command.Parameters.Clear(); command.Parameters.AddWithValue("@fkiMemberTypeId", LTPSDataBroker.ParseInt(base["memberTypeID"], 5)); command.Parameters.AddWithValue("@fkiPersonId", personID); command.Parameters.AddWithValue("@dtMemberApplicationDate", LTPSDataBroker.ParseDbDate(base["applicationDate"])); command.Parameters.AddWithValue("@sTaxNum", LTPSDataBroker.ParseDbString(base["taxNumber"])); command.Parameters.AddWithValue("@sAuditInfo", base.UserAudit); int memberID = Convert.ToInt32(command.ExecuteScalar()); command.CommandType = CommandType.StoredProcedure; command.CommandText = "spMemberEntityI"; command.Parameters.Clear(); command.Parameters.AddWithValue("@MemberId", memberID); command.ExecuteNonQuery(); return memberID; } private string RegisterMember(SqlCommand command, int memberID) { command.CommandType = CommandType.StoredProcedure; command.CommandText = "spAssocMemberI"; command.Parameters.Clear(); command.Parameters.AddWithValue("@fkiAssocId", this.AssociationID); command.Parameters.AddWithValue("@fkiMemberId", memberID); command.ExecuteNonQuery(); command.CommandType = CommandType.Text; command.CommandText = "SELECT sMemberRegistrationNum FROM tblMember Where pkiMemberID = @fkiMemberId"; command.Parameters.Clear(); command.Parameters.AddWithValue("@fkiMemberId", memberID); return LTPSDataBroker.ToString(command.ExecuteScalar()); } private void SaveAddress(SqlCommand command, out int physicalAddressID, out int postalAddressID, out int domicilliumAddressID) { int provinceID = this.ProvinceID; physicalAddressID = LTPSDataBroker.ParseInt(base["physicalAddressID"], -1); postalAddressID = LTPSDataBroker.ParseInt(base["postalAddressID"], -1); physicalAddressID = base.SaveAddress(command, physicalAddressID, provinceID, base["physicalAddressLine1"], base["physicalAddressLine2"], base["physicalAddressLine3"], base["physicalAddressCode"], false); postalAddressID = base.SaveAddress(command, postalAddressID, provinceID, base["postalAddressLine1"], base["postalAddressLine2"], base["postalAddressLine3"], base["postalAddressCode"], true); domicilliumAddressID = (LTPSDataBroker.ParseBool(base["postalAddressIsDomicilium"], false) ? postalAddressID : physicalAddressID); base["physicalAddressID"] = LTPSDataBroker.ToString(physicalAddressID); base["postalAddressID"] = LTPSDataBroker.ToString(postalAddressID); } private bool VehicleLinkedToLicence(SqlCommand command, string regNumber, string chassisNumber, out string reason) { bool linked = false; reason = string.Empty; StringBuilder sql = new StringBuilder(); sql.Append("SELECT "); sql.Append(" dbo.fnLTPSReturnApplicantName(HE.pkiEntityId, HE.fkiEntityTypeId) as 'sApplicantName', "); sql.Append(" ISNULL(P.sNewPermitNum,'') + ISNULL(L.sOLNum,'') as 'sPOLNum' "); sql.Append(" FROM tblVehicle V LEFT OUTER JOIN "); sql.Append(" tblPermit P ON \tV.pkiVehicleId = P.fkiVehicleId AND P.bActive = 1 AND P.fkiPermitStatusId IN (1, 2, 3, 9, 12, 16) LEFT OUTER JOIN "); sql.Append(" tluPermitStatus PS ON P.fkiPermitStatusId = PS.pkiPermitStatusId LEFT OUTER JOIN "); sql.Append(" tblLicence L ON V.pkiVehicleId = L.fkiVehicleId AND L.bActive = 1 AND L.fkiLicenceStatusId IN (1, 2, 3, 6) LEFT OUTER JOIN "); sql.Append(" tluLicenceStatus LS ON L.fkiLicenceStatusId = LS.pkiLicenceStatusId INNER JOIN "); sql.Append(" tblApplication A ON (P.fkiApplicationId = A.pkiApplicationId OR L.fkiApplicationId = A.pkiApplicationId) INNER JOIN "); sql.Append(" tluApplicationStatus APS ON APS.pkiApplicationStatusId = A.fkiApplicationStatusId INNER JOIN "); sql.Append(" tluApplicationType APT ON APT.pkiApplicationTypeId = A.fkiApplicationTypeId INNER JOIN "); sql.Append(" tluApplicationTypeGroup ATG ON ATG.pkiApplicationTypeGroupId = APT.fkiApplicationTypeGroupId INNER JOIN "); sql.Append(" tlkHumanEntity HE ON HE.pkiEntityId = A.fkiApplicantEntityId "); sql.Append(" WHERE (V.sRegistrationNum = @RegNumber OR V.sChassisNum = @ChassisNumber) AND V.bActive = 1 "); command.CommandType = CommandType.Text; command.CommandText = sql.ToString(); command.Parameters.Clear(); command.Parameters.AddWithValue("@RegNumber", regNumber); command.Parameters.AddWithValue("@ChassisNumber", chassisNumber); using (SqlDataReader reader = command.ExecuteReader()) { if (reader.Read()) { linked = true; reason = string.Format("Vehicle linked to licence '{0}' for applicant '{1}'.", reader["sPOLNum"], reader["sApplicantName"]); } } return linked; } private void MoveVehicle(SqlCommand command, string regNumber, int oldMemberID, int newMemberID) { StringBuilder sql = new StringBuilder(); sql.Append("UPDATE tblVehicle SET fkiRASOwnerEntityId = (SELECT pkiEntityId FROM tlkHumanEntity WHERE mfkiRealEntityId = @NewMemberID) "); sql.Append("FROM tblVehicle INNER JOIN "); sql.Append(" tlkHumanEntity ON tblVehicle.fkiRASOwnerEntityId = tlkHumanEntity.pkiEntityId AND tblVehicle.bActive = 1 "); sql.Append("WHERE mfkiRealEntityId = @OldMemberID "); sql.Append("AND sREgistrationNum = @RegNumber "); command.CommandType = CommandType.Text; command.CommandText = sql.ToString(); command.Parameters.Clear(); command.Parameters.AddWithValue("@RegNumber", regNumber); command.Parameters.AddWithValue("@OldMemberID", oldMemberID); command.Parameters.AddWithValue("@NewMemberID", newMemberID); command.ExecuteNonQuery(); } } }