'****************************************************************************************************************
'* Id Application modified by:   Date(MM/dd/yyyy)   Description													*
'* -- ------------------------   --------           -------------------------------------------	    			*
'* 01 Raśl Callupe - PACNET      22/10/2006         Creation of the class                                       *
'****************************************************************************************************************

Imports System
Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient
Imports Microsoft.ApplicationBlocks.Data
Imports System.Globalization

Public Class CC_Client

    Private myDTFI As New DateTimeFormatInfo
    Private strDateFormat As String = "dd/MM/yyyy"

    Sub New()
        myDTFI.ShortDatePattern = strDateFormat
    End Sub

    Public Function GetDataForControls() As DataSet
        Dim strConnectionString As String = ConfigurationSettings.AppSettings("ConnectionString")
        Dim strSPName As String = "SP_CAMS_GET_CLIENT_CONTROLSDATA"
        Dim dsResult As DataSet

        dsResult = SqlHelper.ExecuteDataset(strConnectionString, CommandType.StoredProcedure, strSPName)

        dsResult.Tables(0).TableName = "ETHNICITY"
        dsResult.Tables(1).TableName = "RELIGION"
        dsResult.Tables(2).TableName = "PROPERTYTYPE"
        dsResult.Tables(3).TableName = "TENURE"
        dsResult.Tables(4).TableName = "TITLE"
        dsResult.Tables(5).TableName = "MARITALSTATUS"
        dsResult.Tables(6).TableName = "OT"

        Return dsResult
    End Function

    Public Function GetClients(ByVal nPage As Integer, ByVal nRecordsPerPage As Integer, _
                                Optional ByVal strSurname As String = "", Optional ByVal strName As String = "", _
                                Optional ByVal strApplidn As String = "", Optional ByVal strAddress As String = "", _
                                Optional ByVal strPostcode As String = "", Optional ByVal strGender As String = "", _
                                Optional ByVal strReligion As String = "", Optional ByVal strOYThan As String = "", _
                                Optional ByVal strAge As String = "", Optional ByVal strIniVisited As String = "", _
                                Optional ByVal strFinalVisited As String = "", Optional ByVal strSavingMLThan As String = "", _
                                Optional ByVal strSavingAmount As String = "", Optional ByVal strWithpen As String = "", _
                                Optional ByVal strWithOccPen As String = "", Optional ByVal strWithIncSup As String = "", _
                                Optional ByVal strWithPenCredit As String = "", Optional ByVal strWithOthInc As String = "") As DataSet

        Dim strConnectionString As String = ConfigurationSettings.AppSettings("ConnectionString")
        Dim strSPName As String = "SP_CAMS_GET_CLIENTS"
        Dim arrParams(19) As SqlParameter
        Dim dsResult As DataSet

        arrParams(0) = New SqlParameter("@PAGE", SqlDbType.Int)
        arrParams(0).Value = nPage
        arrParams(1) = New SqlParameter("@RECORDSPERPAGE", SqlDbType.Int)
        arrParams(1).Value = nRecordsPerPage
        arrParams(2) = New SqlParameter("@SURNAME", SqlDbType.VarChar, 100)
        arrParams(2).Value = strSurname
        arrParams(3) = New SqlParameter("@NAME", SqlDbType.VarChar, 50)
        arrParams(3).Value = strName
        arrParams(4) = New SqlParameter("@APPLIDN", SqlDbType.VarChar, 9)
        arrParams(4).Value = strApplidn
        arrParams(5) = New SqlParameter("@ADDRESS", SqlDbType.VarChar, 100)
        arrParams(5).Value = strAddress
        arrParams(6) = New SqlParameter("@POSTCODE", SqlDbType.VarChar, 7)
        arrParams(6).Value = strPostcode
        arrParams(7) = New SqlParameter("@GENDER", SqlDbType.VarChar, 1)
        arrParams(7).Value = strGender
        arrParams(8) = New SqlParameter("@RELIGION", SqlDbType.VarChar, 3)
        arrParams(8).Value = strReligion
        arrParams(9) = New SqlParameter("@OYTHAN", SqlDbType.VarChar, 1)
        arrParams(9).Value = strOYThan

        arrParams(10) = New SqlParameter("@AGE", SqlDbType.Int)
        If strAge.Trim.Equals(String.Empty) Then
            arrParams(10).Value = 0
        Else
            arrParams(10).Value = Integer.Parse(strAge)
        End If

        arrParams(11) = New SqlParameter("@DINIVISITED", SqlDbType.DateTime)
        If strIniVisited.Trim.Equals(String.Empty) Then
            arrParams(11).Value = DBNull.Value
        Else
            arrParams(11).Value = DateTime.Parse(strIniVisited, myDTFI)
        End If

        arrParams(12) = New SqlParameter("@DFINALVISITED", SqlDbType.DateTime)
        If strFinalVisited.Trim.Equals(String.Empty) Then
            arrParams(12).Value = DBNull.Value
        Else
            arrParams(12).Value = DateTime.Parse(strFinalVisited, myDTFI)
        End If

        arrParams(13) = New SqlParameter("@SAVINGMLTHAN", SqlDbType.VarChar, 1)
        arrParams(13).Value = strSavingMLThan

        arrParams(14) = New SqlParameter("@SAVINGAMOUNT", SqlDbType.Decimal)
        If strSavingAmount.Trim.Equals(String.Empty) Then
            arrParams(14).Value = 0
        Else
            arrParams(14).Value = Decimal.Parse(strSavingAmount)
        End If

        arrParams(15) = New SqlParameter("@WITHPEN", SqlDbType.VarChar, 1)
        arrParams(15).Value = strWithpen
        arrParams(16) = New SqlParameter("@WITHOCCPEN", SqlDbType.VarChar, 1)
        arrParams(16).Value = strWithOccPen
        arrParams(17) = New SqlParameter("@WITHINCSUP", SqlDbType.VarChar, 1)
        arrParams(17).Value = strWithIncSup
        arrParams(18) = New SqlParameter("@WITHPENCREDIT", SqlDbType.VarChar, 1)
        arrParams(18).Value = strWithPenCredit
        arrParams(19) = New SqlParameter("@WITHOTHINC", SqlDbType.VarChar, 1)
        arrParams(19).Value = strWithOthInc

        dsResult = SqlHelper.ExecuteDataset(strConnectionString, CommandType.StoredProcedure, strSPName, arrParams)

        Return dsResult
    End Function

    Public Function GetGeneratedClientID() As String
        Dim strConnectionString As String = ConfigurationSettings.AppSettings("ConnectionString")
        Dim strSPName As String = "SP_CAMS_GET_APPLICANTIDNUMBER"
        Dim arrParams(0) As SqlParameter

        arrParams(0) = New SqlParameter("@NEWID", SqlDbType.Char, 9)
        arrParams(0).Direction = ParameterDirection.Output

        SqlHelper.ExecuteNonQuery(strConnectionString, CommandType.StoredProcedure, strSPName, arrParams)

        Return arrParams(0).Value

    End Function

    Public Function DeleteGeneratedClientID(ByVal prmClientID As String) As Boolean
        Dim strConnectionString As String = ConfigurationSettings.AppSettings("ConnectionString")
        Dim strSPName As String = "SP_CAMS_SET_DELETE_APPLICANTIDNUMBER"
        Dim arrParams(0) As SqlParameter
        Dim bSuccess As Boolean = False

        arrParams(0) = New SqlParameter("@CLIENTID", SqlDbType.VarChar, 9)
        arrParams(0).Value = prmClientID

        Dim cnn As New SqlConnection(strConnectionString)
        cnn.Open()
        Dim tran As SqlTransaction = cnn.BeginTransaction()

        Try
            SqlHelper.ExecuteNonQuery(tran, CommandType.StoredProcedure, strSPName, arrParams)
            tran.Commit()
            bSuccess = True
        Catch ex As Exception
            tran.Rollback()
            bSuccess = False
        Finally
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
        End Try

        Return bSuccess
    End Function

    Public Function GetClientByID(ByVal strClientID As String) As EC_Client
        Dim strConnectionString As String = ConfigurationSettings.AppSettings("ConnectionString")
        Dim strSPName As String = "SP_CAMS_GET_CLIENTBYID"
        Dim arrParams(0) As SqlParameter
        Dim dreader As SqlClient.SqlDataReader
        Dim oEC_Client As New EC_Client

        Try
            arrParams(0) = New SqlParameter("@APPLIDN", SqlDbType.Char, 9)
            arrParams(0).Value = strClientID
            dreader = SqlHelper.ExecuteReader(strConnectionString, CommandType.StoredProcedure, strSPName, arrParams)

            With oEC_Client
                While dreader.Read()
                    .ApplIDN = IIf(dreader("APPLIDN") Is DBNull.Value, String.Empty, dreader("APPLIDN"))
                    .Address = IIf(dreader("ADDRESS") Is DBNull.Value, String.Empty, dreader("ADDRESS"))
                    .AgeProperty = IIf(dreader("AGEPROPERTY") Is DBNull.Value, String.Empty, dreader("AGEPROPERTY"))

                    If dreader("APPLDOB") Is DBNull.Value Then
                        .ApplDOB = String.Empty
                    Else
                        .ApplDOB = Convert.ToDateTime(dreader("APPLDOB")).ToString(strDateFormat)
                    End If

                    '.ApplDOB = IIf(dreader("APPLDOB") Is DBNull.Value, String.Empty, Convert.ToDateTime(dreader("APPLDOB")).ToString(strDateFormat))
                    .Comments = IIf(dreader("COMMENTS") Is DBNull.Value, String.Empty, dreader("COMMENTS"))
                    .ContactName1 = IIf(dreader("CONTACTNAME1") Is DBNull.Value, String.Empty, dreader("CONTACTNAME1"))
                    .ContactName2 = IIf(dreader("CONTACTNAME2") Is DBNull.Value, String.Empty, dreader("CONTACTNAME2"))
                    .ContactName3 = IIf(dreader("CONTACTNAME3") Is DBNull.Value, String.Empty, dreader("CONTACTNAME3"))
                    .DeedOrganized = IIf(dreader("DEEDORGANIZED") Is DBNull.Value, String.Empty, dreader("DEEDORGANIZED"))
                    .Email = IIf(dreader("EMAIL") Is DBNull.Value, String.Empty, dreader("EMAIL"))
                    .Ethnicity = IIf(dreader("ETHNICITY") Is DBNull.Value, String.Empty, dreader("ETHNICITY"))
                    .Gender = IIf(dreader("GENDER") Is DBNull.Value, String.Empty, dreader("GENDER"))
                    .IncomeSupport = IIf(dreader("INCOMESUPPORT") Is DBNull.Value, String.Empty, dreader("INCOMESUPPORT"))
                    .LandConsent = IIf(dreader("LANDCONSENT") Is DBNull.Value, String.Empty, dreader("LANDCONSENT"))
                    .LoftInsulation = IIf(dreader("LOFTINSULATION") Is DBNull.Value, String.Empty, dreader("LOFTINSULATION"))
                    .MaritalStatus = IIf(dreader("MARITALSTATUS") Is DBNull.Value, String.Empty, dreader("MARITALSTATUS"))
                    .Mobile = IIf(dreader("MOBILE") Is DBNull.Value, String.Empty, dreader("MOBILE"))
                    .MortConsent = IIf(dreader("MORTCONSENT") Is DBNull.Value, String.Empty, dreader("MORTCONSENT"))
                    .Mortgage = IIf(dreader("MORTGAGE") Is DBNull.Value, String.Empty, dreader("MORTGAGE"))
                    .Name = IIf(dreader("NAME") Is DBNull.Value, String.Empty, dreader("NAME"))
                    .NameDeed = IIf(dreader("NAMEDEED") Is DBNull.Value, String.Empty, dreader("NAMEDEED"))
                    .OccupPension = IIf(dreader("OCCUPPENSION") Is DBNull.Value, String.Empty, dreader("OCCUPPENSION"))
                    .OtherIncome = IIf(dreader("OTHERINCOME") Is DBNull.Value, String.Empty, dreader("OTHERINCOME"))
                    .OtherOccupants = IIf(dreader("OTHEROCCUPANTS") Is DBNull.Value, String.Empty, dreader("OTHEROCCUPANTS"))

                    If dreader("PARTDOB") Is DBNull.Value Then
                        .PartDOB = String.Empty
                    Else
                        .PartDOB = Convert.ToDateTime(dreader("PARTDOB")).ToString(strDateFormat)
                    End If
                    '.PartDOB = IIf(dreader("PARTDOB") Is DBNull.Value, String.Empty, Convert.ToDateTime(dreader("PARTDOB")).ToString(strDateFormat))

                    .PartIDN = IIf(dreader("PARTIDN") Is DBNull.Value, String.Empty, dreader("PARTIDN"))
                    .Pension = IIf(dreader("PENSION") Is DBNull.Value, String.Empty, dreader("PENSION"))
                    .PensionCredit = IIf(dreader("PENSIONCREDIT") Is DBNull.Value, String.Empty, dreader("PENSIONCREDIT"))
                    .Postcode = IIf(dreader("POSTCODE") Is DBNull.Value, String.Empty, dreader("POSTCODE"))
                    .ProfessionalID = IIf(dreader("PROFESSIONALID") Is DBNull.Value, String.Empty, dreader("PROFESSIONALID"))
                    .PropertyType = IIf(dreader("PROPERTYTYPE") Is DBNull.Value, String.Empty, dreader("PROPERTYTYPE"))
                    .ProveTitle = IIf(dreader("PROVETITLE") Is DBNull.Value, String.Empty, dreader("PROVETITLE"))
                    .Religion = IIf(dreader("RELIGION") Is DBNull.Value, String.Empty, dreader("RELIGION"))
                    .SavingAmount = IIf(dreader("SAVINGAMOUNT") Is DBNull.Value, String.Empty, dreader("SAVINGAMOUNT"))
                    .Savings = IIf(dreader("SAVINGS") Is DBNull.Value, String.Empty, dreader("SAVINGS"))
                    .Surname = IIf(dreader("SURNAME") Is DBNull.Value, String.Empty, dreader("SURNAME"))
                    .Telephone = IIf(dreader("TELEPHONE") Is DBNull.Value, String.Empty, dreader("TELEPHONE"))
                    .TenancyAgree = IIf(dreader("TENANCYAGREE") Is DBNull.Value, String.Empty, dreader("TENANCYAGREE"))
                    .Tenure = IIf(dreader("TENURE") Is DBNull.Value, String.Empty, dreader("TENURE"))
                    .Title = IIf(dreader("TITLE") Is DBNull.Value, String.Empty, dreader("TITLE"))
                    .WhereHeld = IIf(dreader("WHEREHELD") Is DBNull.Value, String.Empty, dreader("WHEREHELD"))
                    .AddedBy = IIf(dreader("ADDEDBY") Is DBNull.Value, String.Empty, dreader("ADDEDBY"))
                    .ChangedBy = IIf(dreader("CHANGEDBY") Is DBNull.Value, String.Empty, dreader("CHANGEDBY"))
                End While

            End With

        Catch ex As Exception
            Throw ex
        Finally
            If Not dreader Is Nothing Then
                dreader.Close()
            End If

        End Try

        Return oEC_Client
    End Function

    Public Function SaveClient(ByVal prmClient As EC_Client, Optional ByRef strErrorMsg As String = "") As Boolean
        Dim strConnectionString As String = ConfigurationSettings.AppSettings("ConnectionString")
        Dim strSPName As String = "SP_CAMS_SET_INSERT_CLIENT"
        Dim arrParams(42) As SqlParameter
        Dim bSuccess As Boolean = False

        With prmClient
            arrParams(0) = New SqlParameter("@APPLIDN", SqlDbType.Char, 9)
            arrParams(0).Value = .ApplIDN
            arrParams(1) = New SqlParameter("@ADDRESS", SqlDbType.VarChar, 100)
            arrParams(1).Value = .Address

            arrParams(2) = New SqlParameter("@AGEPROPERTY", SqlDbType.Int)
            If .AgeProperty.Equals(String.Empty) Then
                arrParams(2).Value = DBNull.Value
            Else
                arrParams(2).Value = Convert.ToInt32(.AgeProperty)
            End If

            arrParams(3) = New SqlParameter("@APPLDOB", SqlDbType.DateTime)
            If .ApplDOB.Trim.Equals(String.Empty) Then
                arrParams(3).Value = DBNull.Value
            Else
                arrParams(3).Value = DateTime.Parse(.ApplDOB, myDTFI)
            End If

            arrParams(4) = New SqlParameter("@COMMENTS", SqlDbType.Text)
            If .Comments.Trim.Equals(String.Empty) Then
                arrParams(4).Value = DBNull.Value
            Else
                arrParams(4).Value = .Comments
            End If

            arrParams(5) = New SqlParameter("@CONTACTNAME1", SqlDbType.VarChar, 60)
            arrParams(5).Value = .ContactName1
            arrParams(6) = New SqlParameter("@CONTACTNAME2", SqlDbType.VarChar, 60)
            arrParams(6).Value = .ContactName2
            arrParams(7) = New SqlParameter("@CONTACTNAME3", SqlDbType.VarChar, 60)
            arrParams(7).Value = .ContactName3
            arrParams(8) = New SqlParameter("@DEEDORGANIZED", SqlDbType.Char, 1)
            arrParams(8).Value = .DeedOrganized
            arrParams(9) = New SqlParameter("@EMAIL", SqlDbType.VarChar, 50)
            arrParams(9).Value = .Email
            arrParams(10) = New SqlParameter("@ETHNICITY", SqlDbType.Char, 2)
            arrParams(10).Value = .Ethnicity
            arrParams(11) = New SqlParameter("@GENDER", SqlDbType.Char, 1)
            arrParams(11).Value = .Gender
            arrParams(12) = New SqlParameter("@INCOMESUPPORT", SqlDbType.Char, 1)
            arrParams(12).Value = .IncomeSupport
            arrParams(13) = New SqlParameter("@LANDCONSENT", SqlDbType.Char, 1)
            arrParams(13).Value = .LandConsent
            arrParams(14) = New SqlParameter("@LOFTINSULATION", SqlDbType.Char, 1)
            arrParams(14).Value = .LoftInsulation
            arrParams(15) = New SqlParameter("@MARITALSTATUS", SqlDbType.Char, 2)
            arrParams(15).Value = .MaritalStatus
            arrParams(16) = New SqlParameter("@MOBILE", SqlDbType.VarChar, 12)
            arrParams(16).Value = .Mobile
            arrParams(17) = New SqlParameter("@MORTCONSENT", SqlDbType.Char, 1)
            arrParams(17).Value = .MortConsent
            arrParams(18) = New SqlParameter("@MORTGAGE", SqlDbType.Char, 1)
            arrParams(18).Value = .Mortgage
            arrParams(19) = New SqlParameter("@NAME", SqlDbType.VarChar, 50)
            arrParams(19).Value = .Name
            arrParams(20) = New SqlParameter("@NAMEDEED", SqlDbType.VarChar, 80)
            arrParams(20).Value = .NameDeed
            arrParams(21) = New SqlParameter("@OCCUPPENSION", SqlDbType.Char, 1)
            arrParams(21).Value = .OccupPension
            arrParams(22) = New SqlParameter("@OTHERINCOME", SqlDbType.Char, 1)
            arrParams(22).Value = .OtherIncome
            arrParams(23) = New SqlParameter("@OTHEROCCUPANTS", SqlDbType.Char, 1)
            arrParams(23).Value = .OtherOccupants

            arrParams(24) = New SqlParameter("@PARTDOB", SqlDbType.DateTime)
            If .PartDOB.Trim.Equals(String.Empty) Then
                arrParams(24).Value = DBNull.Value
            Else
                arrParams(24).Value = DateTime.Parse(.PartDOB, myDTFI)
            End If

            arrParams(25) = New SqlParameter("@PARTIDN", SqlDbType.Char, 9)
            arrParams(25).Value = .PartIDN
            arrParams(26) = New SqlParameter("@PENSION", SqlDbType.Char, 1)
            arrParams(26).Value = .Pension
            arrParams(27) = New SqlParameter("@PENSIONCREDIT", SqlDbType.Char, 1)
            arrParams(27).Value = .PensionCredit
            arrParams(28) = New SqlParameter("@POSTCODE", SqlDbType.Char, 7)
            arrParams(28).Value = .Postcode

            arrParams(29) = New SqlParameter("@PROFESSIONALID", SqlDbType.Int)
            If .ProfessionalID.Equals(String.Empty) Then
                arrParams(29).Value = DBNull.Value
            Else
                arrParams(29).Value = Convert.ToInt32(.ProfessionalID)
            End If

            arrParams(30) = New SqlParameter("@PROPERTYTYPE", SqlDbType.Char, 2)
            arrParams(30).Value = .PropertyType
            arrParams(31) = New SqlParameter("@PROVETITLE", SqlDbType.Char, 1)
            arrParams(31).Value = .ProveTitle
            arrParams(32) = New SqlParameter("@RELIGION", SqlDbType.Char, 2)
            arrParams(32).Value = .Religion

            arrParams(33) = New SqlParameter("@SAVINGAMOUNT", SqlDbType.Decimal)
            If .SavingAmount.Equals(String.Empty) Then
                arrParams(33).Value = DBNull.Value
            Else
                arrParams(33).Value = Convert.ToDecimal(.SavingAmount)
            End If

            arrParams(34) = New SqlParameter("@SAVINGS", SqlDbType.Char, 1)
            arrParams(34).Value = .Savings
            arrParams(35) = New SqlParameter("@SURNAME", SqlDbType.VarChar, 100)
            arrParams(35).Value = .Surname
            arrParams(36) = New SqlParameter("@TELEPHONE", SqlDbType.VarChar, 12)
            arrParams(36).Value = .Telephone
            arrParams(37) = New SqlParameter("@TENANCYAGREE", SqlDbType.Char, 1)
            arrParams(37).Value = .TenancyAgree
            arrParams(38) = New SqlParameter("@TENURE", SqlDbType.Char, 2)
            arrParams(38).Value = .Tenure
            arrParams(39) = New SqlParameter("@TITLE", SqlDbType.Char, 2)
            arrParams(39).Value = .Title
            arrParams(40) = New SqlParameter("@WHEREHELD", SqlDbType.VarChar, 100)
            arrParams(40).Value = .WhereHeld
            arrParams(41) = New SqlParameter("@ADDEDBY", SqlDbType.VarChar, 15)
            arrParams(41).Value = .AddedBy
            arrParams(42) = New SqlParameter("@CHANGEDBY", SqlDbType.VarChar, 15)
            arrParams(42).Value = .ChangedBy
        End With

        Dim cnn As New SqlConnection(strConnectionString)
        cnn.Open()
        Dim tran As SqlTransaction = cnn.BeginTransaction()

        Try
            SqlHelper.ExecuteNonQuery(tran, CommandType.StoredProcedure, strSPName, arrParams)
            tran.Commit()
            bSuccess = True
        Catch ex As Exception
            tran.Rollback()
            bSuccess = False
            strErrorMsg = ex.Message
        Finally
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
        End Try

        Return bSuccess
    End Function

    Public Function UpdateClient(ByVal prmOldAppIDN As String, ByVal prmClient As EC_Client, Optional ByRef strErrorMsg As String = "") As Boolean
        Dim strConnectionString As String = ConfigurationSettings.AppSettings("ConnectionString")
        Dim strSPName As String = "SP_CAMS_SET_UPDATE_CLIENT"
        Dim arrParams(43) As SqlParameter
        Dim bSuccess As Boolean = False

        With prmClient
            arrParams(0) = New SqlParameter("@OLDAPPLIDN", SqlDbType.Char, 9)
            arrParams(0).Value = prmOldAppIDN
            arrParams(1) = New SqlParameter("@APPLIDN", SqlDbType.Char, 9)
            arrParams(1).Value = .ApplIDN
            arrParams(2) = New SqlParameter("@ADDRESS", SqlDbType.VarChar, 100)
            arrParams(2).Value = .Address

            arrParams(3) = New SqlParameter("@AGEPROPERTY", SqlDbType.Int)
            If .AgeProperty.Equals(String.Empty) Then
                arrParams(3).Value = DBNull.Value
            Else
                arrParams(3).Value = Convert.ToInt32(.AgeProperty)
            End If

            arrParams(4) = New SqlParameter("@APPLDOB", SqlDbType.DateTime)
            If .ApplDOB.Trim.Equals(String.Empty) Then
                arrParams(4).Value = DBNull.Value
            Else
                arrParams(4).Value = DateTime.Parse(.ApplDOB, myDTFI)
            End If

            arrParams(5) = New SqlParameter("@COMMENTS", SqlDbType.Text)
            If .Comments.Trim.Equals(String.Empty) Then
                arrParams(5).Value = DBNull.Value
            Else
                arrParams(5).Value = .Comments
            End If

            arrParams(6) = New SqlParameter("@CONTACTNAME1", SqlDbType.VarChar, 60)
            arrParams(6).Value = .ContactName1
            arrParams(7) = New SqlParameter("@CONTACTNAME2", SqlDbType.VarChar, 60)
            arrParams(7).Value = .ContactName2
            arrParams(8) = New SqlParameter("@CONTACTNAME3", SqlDbType.VarChar, 60)
            arrParams(8).Value = .ContactName3
            arrParams(9) = New SqlParameter("@DEEDORGANIZED", SqlDbType.Char, 1)
            arrParams(9).Value = .DeedOrganized
            arrParams(10) = New SqlParameter("@EMAIL", SqlDbType.VarChar, 50)
            arrParams(10).Value = .Email
            arrParams(11) = New SqlParameter("@ETHNICITY", SqlDbType.Char, 2)
            arrParams(11).Value = .Ethnicity
            arrParams(12) = New SqlParameter("@GENDER", SqlDbType.Char, 1)
            arrParams(12).Value = .Gender
            arrParams(13) = New SqlParameter("@INCOMESUPPORT", SqlDbType.Char, 1)
            arrParams(13).Value = .IncomeSupport
            arrParams(14) = New SqlParameter("@LANDCONSENT", SqlDbType.Char, 1)
            arrParams(14).Value = .LandConsent
            arrParams(15) = New SqlParameter("@LOFTINSULATION", SqlDbType.Char, 1)
            arrParams(15).Value = .LoftInsulation
            arrParams(16) = New SqlParameter("@MARITALSTATUS", SqlDbType.Char, 2)
            arrParams(16).Value = .MaritalStatus
            arrParams(17) = New SqlParameter("@MOBILE", SqlDbType.VarChar, 12)
            arrParams(17).Value = .Mobile
            arrParams(18) = New SqlParameter("@MORTCONSENT", SqlDbType.Char, 1)
            arrParams(18).Value = .MortConsent
            arrParams(19) = New SqlParameter("@MORTGAGE", SqlDbType.Char, 1)
            arrParams(19).Value = .Mortgage
            arrParams(20) = New SqlParameter("@NAME", SqlDbType.VarChar, 50)
            arrParams(20).Value = .Name
            arrParams(21) = New SqlParameter("@NAMEDEED", SqlDbType.VarChar, 80)
            arrParams(21).Value = .NameDeed
            arrParams(22) = New SqlParameter("@OCCUPPENSION", SqlDbType.Char, 1)
            arrParams(22).Value = .OccupPension
            arrParams(23) = New SqlParameter("@OTHERINCOME", SqlDbType.Char, 1)
            arrParams(23).Value = .OtherIncome
            arrParams(24) = New SqlParameter("@OTHEROCCUPANTS", SqlDbType.Char, 1)
            arrParams(24).Value = .OtherOccupants

            arrParams(25) = New SqlParameter("@PARTDOB", SqlDbType.DateTime)
            If .PartDOB.Trim.Equals(String.Empty) Then
                arrParams(25).Value = DBNull.Value
            Else
                arrParams(25).Value = DateTime.Parse(.PartDOB, myDTFI)
            End If

            arrParams(26) = New SqlParameter("@PARTIDN", SqlDbType.Char, 9)
            arrParams(26).Value = .PartIDN
            arrParams(27) = New SqlParameter("@PENSION", SqlDbType.Char, 1)
            arrParams(27).Value = .Pension
            arrParams(28) = New SqlParameter("@PENSIONCREDIT", SqlDbType.Char, 1)
            arrParams(28).Value = .PensionCredit
            arrParams(29) = New SqlParameter("@POSTCODE", SqlDbType.Char, 7)
            arrParams(29).Value = .Postcode

            arrParams(30) = New SqlParameter("@PROFESSIONALID", SqlDbType.Int)
            If .ProfessionalID.Equals(String.Empty) Then
                arrParams(30).Value = DBNull.Value
            Else
                arrParams(30).Value = Convert.ToInt32(.ProfessionalID)
            End If

            arrParams(31) = New SqlParameter("@PROPERTYTYPE", SqlDbType.Char, 2)
            arrParams(31).Value = .PropertyType
            arrParams(32) = New SqlParameter("@PROVETITLE", SqlDbType.Char, 1)
            arrParams(32).Value = .ProveTitle
            arrParams(33) = New SqlParameter("@RELIGION", SqlDbType.Char, 2)
            arrParams(33).Value = .Religion

            arrParams(34) = New SqlParameter("@SAVINGAMOUNT", SqlDbType.Decimal)
            If .SavingAmount.Equals(String.Empty) Then
                arrParams(34).Value = DBNull.Value
            Else
                arrParams(34).Value = Convert.ToDecimal(.SavingAmount)
            End If

            arrParams(35) = New SqlParameter("@SAVINGS", SqlDbType.Char, 1)
            arrParams(35).Value = .Savings
            arrParams(36) = New SqlParameter("@SURNAME", SqlDbType.VarChar, 100)
            arrParams(36).Value = .Surname
            arrParams(37) = New SqlParameter("@TELEPHONE", SqlDbType.VarChar, 12)
            arrParams(37).Value = .Telephone
            arrParams(38) = New SqlParameter("@TENANCYAGREE", SqlDbType.Char, 1)
            arrParams(38).Value = .TenancyAgree
            arrParams(39) = New SqlParameter("@TENURE", SqlDbType.Char, 2)
            arrParams(39).Value = .Tenure
            arrParams(40) = New SqlParameter("@TITLE", SqlDbType.Char, 2)
            arrParams(40).Value = .Title
            arrParams(41) = New SqlParameter("@WHEREHELD", SqlDbType.VarChar, 100)
            arrParams(41).Value = .WhereHeld
            arrParams(42) = New SqlParameter("@CHANGEDBY", SqlDbType.VarChar, 15)
            arrParams(42).Value = .ChangedBy
            arrParams(43) = New SqlParameter("@ADDEDBY", SqlDbType.VarChar, 15)
            arrParams(43).Value = .AddedBy
        End With

        Dim cnn As New SqlConnection(strConnectionString)
        cnn.Open()
        Dim tran As SqlTransaction = cnn.BeginTransaction()

        Try
            SqlHelper.ExecuteNonQuery(tran, CommandType.StoredProcedure, strSPName, arrParams)
            tran.Commit()
            bSuccess = True
        Catch ex As Exception
            tran.Rollback()
            bSuccess = False
            strErrorMsg = ex.Message
        Finally
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
        End Try

        Return bSuccess
    End Function

    Public Function DeleteClient(ByVal prmClient As EC_Client, Optional ByRef strErrorMsg As String = "") As Boolean
        Dim strConnectionString As String = ConfigurationSettings.AppSettings("ConnectionString")
        Dim strSPName As String = "SP_CAMS_SET_DELETE_CLIENT"
        Dim arrParams(1) As SqlParameter
        Dim bSuccess As Boolean = False

        With prmClient
            arrParams(0) = New SqlParameter("@APPLIDN", SqlDbType.Char, 9)
            arrParams(0).Value = .ApplIDN
            arrParams(1) = New SqlParameter("@CHANGEDBY", SqlDbType.VarChar, 15)
            arrParams(1).Value = .ChangedBy
        End With

        Dim cnn As New SqlConnection(strConnectionString)
        cnn.Open()
        Dim tran As SqlTransaction = cnn.BeginTransaction()

        Try
            SqlHelper.ExecuteNonQuery(tran, CommandType.StoredProcedure, strSPName, arrParams)
            tran.Commit()
            bSuccess = True
        Catch ex As Exception
            tran.Rollback()
            bSuccess = False
            strErrorMsg = ex.Message
        Finally
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
        End Try

        Return bSuccess
    End Function

End Class

Public Class EC_Client
    Private _strApplIDN As String = ""
    Private _strTitle As String = ""
    Private _strSurname As String = ""
    Private _strName As String = ""
    Private _strMaritalStatus As String = ""
    Private _strTelephone As String = ""
    Private _strMobile As String = ""
    Private _strAddress As String = ""
    Private _strPostcode As String = ""
    Private _strEmail As String = ""
    Private _strEthnicity As String = ""
    Private _strReligion As String = ""
    Private _strPartDOB As String = ""
    Private _strPension As String = ""
    Private _strOccupPension As String = ""
    Private _strPensionCredit As String = ""
    Private _strIncomeSupport As String = ""
    Private _strOtherIncome As String = ""
    Private _strSavings As String = ""
    Private _strOtherOccupants As String = ""
    Private _strAgeProperty As String = ""
    Private _strPropertyType As String = ""
    Private _strTenure As String = ""
    Private _strMortgage As String = ""
    Private _strLoftInsulation As String = ""
    Private _strDeedOrganized As String = ""
    Private _strNameDeed As String = ""
    Private _strWhereHeld As String = ""
    Private _strProveTitle As String = ""
    Private _strMortConsent As String = ""
    Private _strLandConsent As String = ""
    Private _strTenancyAgree As String = ""
    Private _strContactName1 As String = ""
    Private _strContactName2 As String = ""
    Private _strContactName3 As String = ""
    Private _strGender As String = ""
    Private _strProfessionalId As String = ""
    Private _strSavingAmount As String = ""
    Private _strComments As String = ""
    Private _strPartIDN As String = ""
    Private _strApplDOB As String = ""
    Private _strAddedBy As String = ""
    Private _strChangedBy As String = ""

    Public Property ApplIDN() As String
        Get
            Return _strApplIDN
        End Get
        Set(ByVal Value As String)
            _strApplIDN = Value
        End Set
    End Property

    Public Property Title() As String
        Get
            Return _strTitle
        End Get
        Set(ByVal Value As String)
            _strTitle = Value
        End Set
    End Property

    Public Property Surname() As String
        Get
            Return _strSurname
        End Get
        Set(ByVal Value As String)
            _strSurname = Value
        End Set
    End Property

    Public Property Name() As String
        Get
            Return _strName
        End Get
        Set(ByVal Value As String)
            _strName = Value
        End Set
    End Property

    Public Property MaritalStatus() As String
        Get
            Return _strMaritalStatus
        End Get
        Set(ByVal Value As String)
            _strMaritalStatus = Value
        End Set
    End Property

    Public Property Telephone() As String
        Get
            Return _strTelephone
        End Get
        Set(ByVal Value As String)
            _strTelephone = Value
        End Set
    End Property

    Public Property Mobile() As String
        Get
            Return _strMobile
        End Get
        Set(ByVal Value As String)
            _strMobile = Value
        End Set
    End Property

    Public Property Address() As String
        Get
            Return _strAddress
        End Get
        Set(ByVal Value As String)
            _strAddress = Value
        End Set
    End Property

    Public Property Postcode() As String
        Get
            Return _strPostcode
        End Get
        Set(ByVal Value As String)
            _strPostcode = Value
        End Set
    End Property

    Public Property Email() As String
        Get
            Return _strEmail
        End Get
        Set(ByVal Value As String)
            _strEmail = Value
        End Set
    End Property

    Public Property Ethnicity() As String
        Get
            Return _strEthnicity
        End Get
        Set(ByVal Value As String)
            _strEthnicity = Value
        End Set
    End Property

    Public Property Religion() As String
        Get
            Return _strReligion
        End Get
        Set(ByVal Value As String)
            _strReligion = Value
        End Set
    End Property

    Public Property PartDOB() As String
        Get
            Return _strPartDOB
        End Get
        Set(ByVal Value As String)
            _strPartDOB = Value
        End Set
    End Property

    Public Property Pension() As String
        Get
            Return _strPension
        End Get
        Set(ByVal Value As String)
            _strPension = Value
        End Set
    End Property

    Public Property OccupPension() As String
        Get
            Return _strOccupPension
        End Get
        Set(ByVal Value As String)
            _strOccupPension = Value
        End Set
    End Property

    Public Property PensionCredit() As String
        Get
            Return _strPensionCredit
        End Get
        Set(ByVal Value As String)
            _strPensionCredit = Value
        End Set
    End Property

    Public Property IncomeSupport() As String
        Get
            Return _strIncomeSupport
        End Get
        Set(ByVal Value As String)
            _strIncomeSupport = Value
        End Set
    End Property

    Public Property OtherIncome() As String
        Get
            Return _strOtherIncome
        End Get
        Set(ByVal Value As String)
            _strOtherIncome = Value
        End Set
    End Property

    Public Property Savings() As String
        Get
            Return _strSavings
        End Get
        Set(ByVal Value As String)
            _strSavings = Value
        End Set
    End Property

    Public Property OtherOccupants() As String
        Get
            Return _strOtherOccupants
        End Get
        Set(ByVal Value As String)
            _strOtherOccupants = Value
        End Set
    End Property

    Public Property AgeProperty() As String
        Get
            Return _strAgeProperty
        End Get
        Set(ByVal Value As String)
            _strAgeProperty = Value
        End Set
    End Property

    Public Property PropertyType() As String
        Get
            Return _strPropertyType
        End Get
        Set(ByVal Value As String)
            _strPropertyType = Value
        End Set
    End Property

    Public Property Tenure() As String
        Get
            Return _strTenure
        End Get
        Set(ByVal Value As String)
            _strTenure = Value
        End Set
    End Property

    Public Property Mortgage() As String
        Get
            Return _strMortgage
        End Get
        Set(ByVal Value As String)
            _strMortgage = Value
        End Set
    End Property

    Public Property LoftInsulation() As String
        Get
            Return _strLoftInsulation
        End Get
        Set(ByVal Value As String)
            _strLoftInsulation = Value
        End Set
    End Property

    Public Property DeedOrganized() As String
        Get
            Return _strDeedOrganized
        End Get
        Set(ByVal Value As String)
            _strDeedOrganized = Value
        End Set
    End Property

    Public Property NameDeed() As String
        Get
            Return _strNameDeed
        End Get
        Set(ByVal Value As String)
            _strNameDeed = Value
        End Set
    End Property

    Public Property WhereHeld() As String
        Get
            Return _strWhereHeld
        End Get
        Set(ByVal Value As String)
            _strWhereHeld = Value
        End Set
    End Property

    Public Property ProveTitle() As String
        Get
            Return _strProveTitle
        End Get
        Set(ByVal Value As String)
            _strProveTitle = Value
        End Set
    End Property

    Public Property MortConsent() As String
        Get
            Return _strMortConsent
        End Get
        Set(ByVal Value As String)
            _strMortConsent = Value
        End Set
    End Property

    Public Property LandConsent() As String
        Get
            Return _strLandConsent
        End Get
        Set(ByVal Value As String)
            _strLandConsent = Value
        End Set
    End Property

    Public Property TenancyAgree() As String
        Get
            Return _strTenancyAgree
        End Get
        Set(ByVal Value As String)
            _strTenancyAgree = Value
        End Set
    End Property

    Public Property ContactName1() As String
        Get
            Return _strContactName1
        End Get
        Set(ByVal Value As String)
            _strContactName1 = Value
        End Set
    End Property

    Public Property ContactName2() As String
        Get
            Return _strContactName2
        End Get
        Set(ByVal Value As String)
            _strContactName2 = Value
        End Set
    End Property

    Public Property ContactName3() As String
        Get
            Return _strContactName3
        End Get
        Set(ByVal Value As String)
            _strContactName3 = Value
        End Set
    End Property

    Public Property Gender() As String
        Get
            Return _strGender
        End Get
        Set(ByVal Value As String)
            _strGender = Value
        End Set
    End Property

    Public Property ProfessionalID() As String
        Get
            Return _strProfessionalId
        End Get
        Set(ByVal Value As String)
            _strProfessionalId = Value
        End Set
    End Property

    Public Property SavingAmount() As String
        Get
            Return _strSavingAmount
        End Get
        Set(ByVal Value As String)
            _strSavingAmount = Value
        End Set
    End Property

    Public Property Comments() As String
        Get
            Return _strComments
        End Get
        Set(ByVal Value As String)
            _strComments = Value
        End Set
    End Property

    Public Property PartIDN() As String
        Get
            Return _strPartIDN
        End Get
        Set(ByVal Value As String)
            _strPartIDN = Value
        End Set
    End Property

    Public Property ApplDOB() As String
        Get
            Return _strApplDOB
        End Get
        Set(ByVal Value As String)
            _strApplDOB = Value
        End Set
    End Property

    Public Property AddedBy() As String
        Get
            Return _strAddedBy
        End Get
        Set(ByVal Value As String)
            _strAddedBy = Value
        End Set
    End Property

    Public Property ChangedBy() As String
        Get
            Return _strChangedBy
        End Get
        Set(ByVal Value As String)
            _strChangedBy = Value
        End Set
    End Property

End Class