'****************************************************************************************************************
'* Id Application modified by:   Date(MM/dd/yyyy)   Description													*
'* -- ------------------------   --------           -------------------------------------------	    			*
'* 01 Raśl Callupe - PACNET      22/10/2006         Creation of the class
'* 02 Vanessa Ayala - PACNET     16/04/2006                                                                     *
'****************************************************************************************************************
Imports System
Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient
Imports Microsoft.ApplicationBlocks.Data
Imports System.Globalization

Public Class CC_Case

    Private myDTFI As New DateTimeFormatInfo
    Private strDateFormat As String = "dd/MM/yyyy"
    Private Const STATUS_CANCELLED As String = "CA"
    Private Const STATUS_CLOSED As String = "CL"

    Sub New()
        myDTFI.ShortDatePattern = strDateFormat
    End Sub

    Public Function GetApplicationTarget(ByVal strDVisit As String, ByVal strDAmdSch As String, _
                                            ByVal strGT As String, ByVal strGTSim As String) As String
        Dim strConnectionString As String = ConfigurationSettings.AppSettings("ConnectionString")
        Dim strSPName As String = "SP_CAMS_GET_APPLICATION_TARGET"
        Dim arrParams(4) As SqlParameter
        Dim strTarget As String = String.Empty

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

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

        arrParams(2) = New SqlParameter("@GrantType", SqlDbType.VarChar, 3)
        If strGT.Trim.Equals(String.Empty) Then
            arrParams(2).Value = DBNull.Value
        Else
            arrParams(2).Value = strGT
        End If

        arrParams(3) = New SqlParameter("@SGrantType", SqlDbType.VarChar, 3)
        If strGTSim.Trim.Equals(String.Empty) Then
            arrParams(3).Value = DBNull.Value
        Else
            arrParams(3).Value = strGTSim
        End If

        arrParams(4) = New SqlParameter("@Appl_Target", SqlDbType.DateTime)
        arrParams(4).Direction = ParameterDirection.Output

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

            If arrParams(4).Value Is DBNull.Value Then
                strTarget = String.Empty
            Else
                strTarget = Convert.ToDateTime(arrParams(4).Value).ToString(strDateFormat)
            End If

        Catch ex As Exception
            strTarget = String.Empty
        End Try

        Return strTarget

    End Function

    Public Function GetCompTarget(ByVal strDApproved As String, ByVal strGT As String, ByVal strGTSim As String) As String
        Dim strConnectionString As String = ConfigurationSettings.AppSettings("ConnectionString")
        Dim strSPName As String = "SP_CAMS_GET_COMP_TARGET"
        Dim arrParams(3) As SqlParameter
        Dim strTarget As String = String.Empty

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

        arrParams(1) = New SqlParameter("@GrantType", SqlDbType.VarChar, 3)
        If strGT.Trim.Equals(String.Empty) Then
            arrParams(1).Value = DBNull.Value
        Else
            arrParams(1).Value = strGT
        End If

        arrParams(2) = New SqlParameter("@SGrantType", SqlDbType.VarChar, 3)
        If strGTSim.Trim.Equals(String.Empty) Then
            arrParams(2).Value = DBNull.Value
        Else
            arrParams(2).Value = strGTSim
        End If

        arrParams(3) = New SqlParameter("@COMP_Target", SqlDbType.DateTime)
        arrParams(3).Direction = ParameterDirection.Output

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

            If arrParams(3).Value Is DBNull.Value Then
                strTarget = String.Empty
            Else
                strTarget = Convert.ToDateTime(arrParams(3).Value).ToString(strDateFormat)
            End If

        Catch ex As Exception
            strTarget = String.Empty
        End Try

        Return strTarget

    End Function

    Public Function GetSL21Target(ByVal strDApproved As String, ByVal strDRevSch As String) As String
        Dim strConnectionString As String = ConfigurationSettings.AppSettings("ConnectionString")
        Dim strSPName As String = "SP_CAMS_GET_SL21_TARGET"
        Dim arrParams(2) As SqlParameter
        Dim strTarget As String = String.Empty

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

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

        arrParams(2) = New SqlParameter("@SL21_TARGET", SqlDbType.DateTime)
        arrParams(2).Direction = ParameterDirection.Output

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

            If arrParams(2).Value Is DBNull.Value Then
                strTarget = String.Empty
            Else
                strTarget = Convert.ToDateTime(arrParams(2).Value).ToString(strDateFormat)
            End If

        Catch ex As Exception
            strTarget = String.Empty
        End Try

        Return strTarget

    End Function

    Public Function GetVisitTarget(ByVal strDContact As String, ByVal strDRefSch As String) As String
        Dim strConnectionString As String = ConfigurationSettings.AppSettings("ConnectionString")
        Dim strSPName As String = "SP_CAMS_GET_VISIT_TARGET"
        Dim arrParams(2) As SqlParameter
        Dim strTarget As String = String.Empty

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

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

        arrParams(2) = New SqlParameter("@VISIT_TARGET", SqlDbType.DateTime)
        arrParams(2).Direction = ParameterDirection.Output

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

            If arrParams(2).Value Is DBNull.Value Then
                strTarget = String.Empty
            Else
                strTarget = Convert.ToDateTime(arrParams(2).Value).ToString(strDateFormat)
            End If

        Catch ex As Exception
            strTarget = String.Empty
        End Try

        Return strTarget

    End Function

    Public Function CaseExists(ByVal strCaseID As String) As Boolean
        Dim strConnectionString As String = ConfigurationSettings.AppSettings("ConnectionString")
        Dim arrParams(0) As SqlParameter
        Dim strSPName As String = "SP_CAMS_GET_CASE_EXISTS"
        Dim nCount As Integer = 0

        arrParams(0) = New SqlParameter("@CASEID", SqlDbType.Char, 6)
        arrParams(0).Value = strCaseID

        Try
            nCount = SqlHelper.ExecuteScalar(strConnectionString, CommandType.StoredProcedure, strSPName, arrParams)
        Catch ex As Exception
            nCount = 0
        End Try

        If nCount > 0 Then
            Return True
        Else
            Return False
        End If

    End Function

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

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

        dsResult.Tables(0).TableName = "STATUS"
        dsResult.Tables(1).TableName = "REASONS"
        dsResult.Tables(2).TableName = "HA"
        dsResult.Tables(3).TableName = "GRANTTYPES"
        dsResult.Tables(4).TableName = "ARCHITECTS"
        dsResult.Tables(5).TableName = "CONTRACTORS"
        dsResult.Tables(6).TableName = "TA"
        dsResult.Tables(7).TableName = "GO"
        dsResult.Tables(8).TableName = "STAGES"
        dsResult.Tables(9).TableName = "QUARTERS"

        Return dsResult
    End Function

    Public Function GetCaseByID(ByVal strCaseID As String) As EC_Case
        Dim strConnectionString As String = ConfigurationSettings.AppSettings("ConnectionString")
        Dim strSPName As String = "SP_CAMS_GET_CASEBYID"
        Dim arrParams(0) As SqlParameter
        Dim dreader As SqlClient.SqlDataReader
        Dim oEC_Case As New EC_Case

        Try
            arrParams(0) = New SqlParameter("@CASEID", SqlDbType.Char, 6)
            arrParams(0).Value = strCaseID
            dreader = SqlHelper.ExecuteReader(strConnectionString, CommandType.StoredProcedure, strSPName, arrParams)

            With oEC_Case
                While dreader.Read()
                    .AddedBy = IIf(dreader("ADDEDBY") Is DBNull.Value, String.Empty, dreader("ADDEDBY"))

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

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

                    .AppClientPortion = IIf(dreader("APPCLIENTPORTION") Is DBNull.Value, String.Empty, dreader("APPCLIENTPORTION"))

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

                    .AppFoldInt = IIf(dreader("APPFOLDINT") Is DBNull.Value, String.Empty, dreader("APPFOLDINT"))
                    .ApplIDN = IIf(dreader("APPLIDN") Is DBNull.Value, String.Empty, dreader("APPLIDN"))
                    .AppMoney = IIf(dreader("APPMONEY") Is DBNull.Value, String.Empty, dreader("APPMONEY"))
                    .AppMoneyDesc = IIf(dreader("APPMONEYDESC") Is DBNull.Value, String.Empty, dreader("APPMONEYDESC"))

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

                    .AppSL20A = IIf(dreader("APPSL20A") Is DBNull.Value, String.Empty, dreader("APPSL20A"))
                    .AppSl20B = IIf(dreader("APPSL20B") Is DBNull.Value, String.Empty, dreader("APPSL20B"))
                    .AppSL20C = IIf(dreader("APPSL20C") Is DBNull.Value, String.Empty, dreader("APPSL20C"))

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

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

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

                    .ArchBControl = IIf(dreader("ARCHBCONTROL") Is DBNull.Value, String.Empty, dreader("ARCHBCONTROL"))

                    .GrantOfficeId = IIf(dreader("GRANTOFFICEID") Is DBNull.Value, String.Empty, dreader("GRANTOFFICEID"))

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

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

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

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

                    .CaseID = IIf(dreader("CASEID") Is DBNull.Value, String.Empty, dreader("CASEID"))
                    .CDLandCons = IIf(dreader("CDLANDCONS") Is DBNull.Value, String.Empty, dreader("CDLANDCONS"))

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

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

                    .CDMortCons = IIf(dreader("CDMORTCONS") Is DBNull.Value, String.Empty, dreader("CDMORTCONS"))

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

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

                    .CDProof = IIf(dreader("CDPROOF") Is DBNull.Value, String.Empty, dreader("CDPROOF"))

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

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

                    .ChangedBy = IIf(dreader("CHANGEDBY") Is DBNull.Value, String.Empty, dreader("CHANGEDBY"))
                    .CoEstimates = GetCoEstimatesByCase(.CaseID)
                    .Comments = IIf(dreader("COMMENTS") Is DBNull.Value, String.Empty, dreader("COMMENTS"))
                    .ContractorId = IIf(dreader("CONTRACTORID") Is DBNull.Value, String.Empty, dreader("CONTRACTORID"))
                    .GrantTypeId = IIf(dreader("GRANTTYPEID") Is DBNull.Value, String.Empty, dreader("GRANTTYPEID"))
                    .HA = IIf(dreader("HA") Is DBNull.Value, String.Empty, dreader("HA"))

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

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

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

                    .OGRefNum = IIf(dreader("OGREFNUM") Is DBNull.Value, String.Empty, dreader("OGREFNUM"))

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

                    .OGSGrantTypeId = IIf(dreader("OGSGRANTTYPEID") Is DBNull.Value, String.Empty, dreader("OGSGRANTTYPEID"))
                    .OGSimult = IIf(dreader("OGSIMULT") Is DBNull.Value, String.Empty, dreader("OGSIMULT"))
                    .OGSRefNum = IIf(dreader("OGSREFNUM") Is DBNull.Value, String.Empty, dreader("OGSREFNUM"))
                    .ProfessionalId = IIf(dreader("PROFESSIONALID") Is DBNull.Value, String.Empty, dreader("PROFESSIONALID"))
                    .ReasonId = IIf(dreader("REASONID") Is DBNull.Value, String.Empty, dreader("REASONID"))
                    .Reminder = IIf(dreader("REMINDER") Is DBNull.Value, String.Empty, dreader("REMINDER"))

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

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

                    .Stage = IIf(dreader("STAGE") Is DBNull.Value, String.Empty, dreader("STAGE"))
                    .Status = IIf(dreader("STATUS") Is DBNull.Value, String.Empty, dreader("STATUS"))
                    .TA = IIf(dreader("TA") Is DBNull.Value, String.Empty, dreader("TA"))
                    .WCBControl = IIf(dreader("WCBCONTROL") Is DBNull.Value, String.Empty, dreader("WCBCONTROL"))
                    .WCCommCard = IIf(dreader("WCCOMMCARD") Is DBNull.Value, String.Empty, dreader("WCCOMMCARD"))

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

                    .WCCompCard = IIf(dreader("WCCOMPCARD") Is DBNull.Value, String.Empty, dreader("WCCOMPCARD"))

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

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

                    .WCDescDoc = IIf(dreader("WCDESCDOC") Is DBNull.Value, String.Empty, dreader("WCDESCDOC"))
                    .WCFinalInv = IIf(dreader("WCFINALINV") Is DBNull.Value, String.Empty, dreader("WCFINALINV"))
                    .WCGrant = IIf(dreader("WCGRANT") Is DBNull.Value, String.Empty, dreader("WCGRANT"))
                    .WCHR7 = IIf(dreader("WCHR7") Is DBNull.Value, String.Empty, dreader("WCHR7"))
                    .WCNICEIC = IIf(dreader("WCNICEIC") Is DBNull.Value, String.Empty, dreader("WCNICEIC"))
                    .WCOtherDoc = IIf(dreader("WCOTHERDOC") Is DBNull.Value, String.Empty, dreader("WCOTHERDOC"))
                    .WCOverCost = IIf(dreader("WCOVERCOST") Is DBNull.Value, String.Empty, dreader("WCOVERCOST"))
                    .WCSGrant = IIf(dreader("WCSGRANT") Is DBNull.Value, String.Empty, dreader("WCSGRANT"))
                    .WCSnag = IIf(dreader("WCSNAG") Is DBNull.Value, String.Empty, dreader("WCSNAG"))
                    .WCSOverCost = IIf(dreader("WCSOVERCOST") Is DBNull.Value, String.Empty, dreader("WCSOVERCOST"))
                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_Case
    End Function

    Private Function GetCoEstimatesByCase(ByVal strCaseID As String) As EC_CoEstimates()
        Dim strConnectionString As String = ConfigurationSettings.AppSettings("ConnectionString")
        Dim strSPName As String = "SP_CAMS_GET_COESTIMATESBYCASE"
        Dim arrParams(0) As SqlParameter
        Dim dreader As SqlClient.SqlDataReader
        Dim oEC_CoEstimates() As EC_CoEstimates

        Try
            arrParams(0) = New SqlParameter("@CASEID", SqlDbType.Char, 6)
            arrParams(0).Value = strCaseID
            dreader = SqlHelper.ExecuteReader(strConnectionString, CommandType.StoredProcedure, strSPName, arrParams)

            Dim i As Integer = 0

            While dreader.Read()

                ReDim Preserve oEC_CoEstimates(i)

                oEC_CoEstimates(i) = New EC_CoEstimates

                With oEC_CoEstimates(i)
                    .CaseID = IIf(dreader("CASEID") Is DBNull.Value, String.Empty, dreader("CASEID"))
                    .ContractorID = IIf(dreader("CONTRACTORID") Is DBNull.Value, 0, dreader("CONTRACTORID"))
                    .Name = IIf(dreader("COMPANYNAME") Is DBNull.Value, String.Empty, dreader("COMPANYNAME"))

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

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

                    .NewEstimate = IIf(dreader("NEWESTIMATE") Is DBNull.Value, String.Empty, dreader("NEWESTIMATE"))
                End With

                i += 1

            End While

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

        End Try

        Return oEC_CoEstimates
    End Function

    Public Function GetRemindersByCase(ByVal strCaseID As String) As DataSet
        Dim strConnectionString As String = ConfigurationSettings.AppSettings("ConnectionString")
        Dim strSPName As String = "SP_CAMS_GET_REMINDERSBYCASE"
        Dim arrParams(0) As SqlParameter
        Dim dsResult As DataSet



        Try
            arrParams(0) = New SqlParameter("@CASEID", SqlDbType.Char, 6)
            arrParams(0).Value = strCaseID

            dsResult = SqlHelper.ExecuteDataset(strConnectionString, CommandType.StoredProcedure, strSPName, arrParams)
        Catch ex As Exception
            dsResult = Nothing

        End Try

        Return dsResult
    End Function

    Public Function GetCases(ByVal nPage As Integer, ByVal nRecordsPerPage As Integer, ByVal strIsFromMyCases As String, _
                            Optional ByVal strCaseID As String = "", Optional ByVal strRefNum As String = "", _
                            Optional ByVal strSimRefNum As String = "", Optional ByVal strGT As String = "", _
                            Optional ByVal strSimGT As String = "", Optional ByVal strSurname As String = "", _
                            Optional ByVal strAddress As String = "", Optional ByVal strPostcode As String = "", _
                            Optional ByVal strStatus As String = "", Optional ByVal strGO As String = "", _
                            Optional ByVal strStage As String = "", Optional ByVal strHA As String = "", _
                            Optional ByVal strTA As String = "", Optional ByVal strContractorID As String = "") As DataSet

        Dim strConnectionString As String = ConfigurationSettings.AppSettings("ConnectionString")
        Dim strSPName As String = "SP_CAMS_GET_CASES"
        Dim arrParams(16) 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("@CASEID", SqlDbType.VarChar, 6)
        arrParams(2).Value = strCaseID
        arrParams(3) = New SqlParameter("@OGREFNUM", SqlDbType.VarChar, 10)
        arrParams(3).Value = strRefNum
        arrParams(4) = New SqlParameter("@SIMREFNUM", SqlDbType.VarChar, 1)
        arrParams(4).Value = strSimRefNum
        arrParams(5) = New SqlParameter("@GRANTTYPEID", SqlDbType.VarChar, 3)
        arrParams(5).Value = strGT
        arrParams(6) = New SqlParameter("@SIMGRANTTYPE", SqlDbType.VarChar, 1)
        arrParams(6).Value = strSimGT
        arrParams(7) = New SqlParameter("@SURNAME", SqlDbType.VarChar, 100)
        arrParams(7).Value = strSurname
        arrParams(8) = New SqlParameter("@ADDRESS", SqlDbType.VarChar, 100)
        arrParams(8).Value = strAddress
        arrParams(9) = New SqlParameter("@POSTCODE", SqlDbType.VarChar, 7)
        arrParams(9).Value = strPostcode
        arrParams(10) = New SqlParameter("@STATUS", SqlDbType.VarChar, 2)
        arrParams(10).Value = strStatus
        arrParams(11) = New SqlParameter("@GO", SqlDbType.VarChar, 2)
        arrParams(11).Value = strGO
        arrParams(12) = New SqlParameter("@STAGE", SqlDbType.VarChar, 6)
        arrParams(12).Value = strStage

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

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

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

        arrParams(16) = New SqlParameter("@ISFROMMYCASES", SqlDbType.VarChar, 1)
        arrParams(16).Value = strIsFromMyCases

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

        Return dsResult
    End Function

    Public Function SaveCase(ByVal prmCase As EC_Case, ByVal strOrigin As String, Optional ByRef strErrorMsg As String = "") As Boolean
        Dim strConnectionString As String = ConfigurationSettings.AppSettings("ConnectionString")
        Dim strSPName As String = "SP_CAMS_SET_INSERT_CASE"
        Dim arrParams(68) As SqlParameter
        Dim bSuccess As Boolean = False

        With prmCase
            arrParams(0) = New SqlParameter("@APPAPROVAL", SqlDbType.DateTime)
            If .AppAproval.Trim.Equals(String.Empty) Then
                arrParams(0).Value = DBNull.Value
            Else
                arrParams(0).Value = DateTime.Parse(.AppAproval, myDTFI)
            End If

            arrParams(1) = New SqlParameter("@APPCLIENTPORTION", SqlDbType.Decimal)
            If .AppClientPortion.Trim.Equals(String.Empty) Then
                arrParams(1).Value = DBNull.Value
            Else
                arrParams(1).Value = .AppClientPortion
            End If

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

            arrParams(3) = New SqlParameter("@APPFOLDINT", SqlDbType.Decimal)
            If .AppFoldInt.Trim.Equals(String.Empty) Then
                arrParams(3).Value = DBNull.Value
            Else
                arrParams(3).Value = .AppFoldInt
            End If

            arrParams(4) = New SqlParameter("@APPLIDN", SqlDbType.Char, 9)
            arrParams(4).Value = .ApplIDN
            arrParams(5) = New SqlParameter("@APPMONEY", SqlDbType.Char, 1)
            arrParams(5).Value = .AppMoney
            arrParams(6) = New SqlParameter("@APPMONEYDESC", SqlDbType.VarChar, 70)
            arrParams(6).Value = .AppMoneyDesc

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

            arrParams(8) = New SqlParameter("@APPSL20A", SqlDbType.Char, 1)
            arrParams(8).Value = .AppSL20A
            arrParams(9) = New SqlParameter("@APPSL20B", SqlDbType.Char, 1)
            arrParams(9).Value = .AppSl20B
            arrParams(10) = New SqlParameter("@APPSL20C", SqlDbType.Char, 1)
            arrParams(10).Value = .AppSL20C

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

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

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

            arrParams(14) = New SqlParameter("@ARCHBCONTROL", SqlDbType.Char, 1)
            arrParams(14).Value = .ArchBControl

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

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

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

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

            arrParams(19) = New SqlParameter("@CDLandCons", SqlDbType.Char, 1)
            arrParams(19).Value = .CDLandCons

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

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

            arrParams(22) = New SqlParameter("@CDMORTCONS", SqlDbType.Char, 1)
            arrParams(22).Value = .CDMortCons

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

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

            arrParams(25) = New SqlParameter("@CDPROOF", SqlDbType.Char, 1)
            arrParams(25).Value = .CDProof

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

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

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

            arrParams(29) = New SqlParameter("@CONTRACTORID", SqlDbType.Int)
            If .ContractorId.Trim.Equals(String.Empty) Then
                arrParams(29).Value = DBNull.Value
            Else
                arrParams(29).Value = .ContractorId
            End If

            arrParams(30) = New SqlParameter("@GRANTTYPEID", SqlDbType.Char, 3)
            If .GrantTypeId.Trim.Equals(String.Empty) Then
                arrParams(30).Value = DBNull.Value
            Else
                arrParams(30).Value = .GrantTypeId
            End If

            arrParams(31) = New SqlParameter("@HA", SqlDbType.Int)
            If .HA.Trim.Equals(String.Empty) Then
                arrParams(31).Value = DBNull.Value
            Else
                arrParams(31).Value = .HA
            End If

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

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

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

            arrParams(35) = New SqlParameter("@OGREFNUM", SqlDbType.VarChar, 10)
            arrParams(35).Value = .OGRefNum

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

            arrParams(37) = New SqlParameter("@OGSGRANTTYPEID", SqlDbType.Char, 3)
            If .OGSGrantTypeId.Trim.Equals(String.Empty) Then
                arrParams(37).Value = DBNull.Value
            Else
                arrParams(37).Value = .OGSGrantTypeId
            End If

            arrParams(38) = New SqlParameter("@OGSIMULT", SqlDbType.Char, 1)
            arrParams(38).Value = .OGSimult
            arrParams(39) = New SqlParameter("@OGSREFNUM", SqlDbType.VarChar, 10)
            arrParams(39).Value = .OGSRefNum

            arrParams(40) = New SqlParameter("@PROFESSIONALID", SqlDbType.Int)
            If .ProfessionalId.Trim.Equals(String.Empty) Then
                arrParams(40).Value = DBNull.Value
            Else
                arrParams(40).Value = .ProfessionalId
            End If

            arrParams(41) = New SqlParameter("@REASONID", SqlDbType.Char, 2)
            If .ReasonId.Trim.Equals(String.Empty) Then
                arrParams(41).Value = DBNull.Value
            Else
                arrParams(41).Value = .ReasonId
            End If

            arrParams(42) = New SqlParameter("@REMINDER", SqlDbType.Char, 1)
            arrParams(42).Value = .Reminder

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

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

            arrParams(45) = New SqlParameter("@STATUS", SqlDbType.VarChar, 2)
            arrParams(45).Value = .Status

            arrParams(46) = New SqlParameter("@TA", SqlDbType.Int)
            If .TA.Trim.Equals(String.Empty) Then
                arrParams(46).Value = DBNull.Value
            Else
                arrParams(46).Value = .TA
            End If

            arrParams(47) = New SqlParameter("@WCBCONTROL", SqlDbType.Char, 1)
            arrParams(47).Value = .WCBControl
            arrParams(48) = New SqlParameter("@WCCOMMCARD", SqlDbType.Char, 1)
            arrParams(48).Value = .WCCommCard

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

            arrParams(50) = New SqlParameter("@WCCompCard", SqlDbType.Char, 1)
            arrParams(50).Value = .WCCompCard

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

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

            arrParams(53) = New SqlParameter("@WCDESCDOC", SqlDbType.VarChar, 100)
            arrParams(53).Value = .WCDescDoc
            arrParams(54) = New SqlParameter("@WCFINALINV", SqlDbType.Char, 1)
            arrParams(54).Value = .WCFinalInv

            arrParams(55) = New SqlParameter("@WCGRANT", SqlDbType.Decimal)
            If .WCGrant.Trim.Equals(String.Empty) Then
                arrParams(55).Value = DBNull.Value
            Else
                arrParams(55).Value = .WCGrant
            End If

            arrParams(56) = New SqlParameter("@WCHR7", SqlDbType.Char, 1)
            arrParams(56).Value = .WCHR7
            arrParams(57) = New SqlParameter("@WCNICEIC", SqlDbType.Char, 1)
            arrParams(57).Value = .WCNICEIC
            arrParams(58) = New SqlParameter("@WCOTHERDOC", SqlDbType.Char, 1)
            arrParams(58).Value = .WCOtherDoc

            arrParams(59) = New SqlParameter("@WCOVERCOST", SqlDbType.Decimal)
            If .WCOverCost.Trim.Equals(String.Empty) Then
                arrParams(59).Value = DBNull.Value
            Else
                arrParams(59).Value = .WCOverCost
            End If

            arrParams(60) = New SqlParameter("@WCSGRANT", SqlDbType.Decimal)
            If .WCSGrant.Trim.Equals(String.Empty) Then
                arrParams(60).Value = DBNull.Value
            Else
                arrParams(60).Value = .WCSGrant
            End If

            arrParams(61) = New SqlParameter("@WCSNAG", SqlDbType.Char, 1)
            arrParams(61).Value = .WCSnag

            arrParams(62) = New SqlParameter("@WCSOVERCOST", SqlDbType.Decimal)
            If .WCSOverCost.Trim.Equals(String.Empty) Then
                arrParams(62).Value = DBNull.Value
            Else
                arrParams(62).Value = .WCSOverCost
            End If

            arrParams(63) = New SqlParameter("@ADDEDBY", SqlDbType.VarChar, 15)
            arrParams(63).Value = .AddedBy
            arrParams(64) = New SqlParameter("@CHANGEDBY", SqlDbType.VarChar, 15)
            arrParams(64).Value = .ChangedBy
            arrParams(65) = New SqlParameter("@CASEID", SqlDbType.Char, 6)
            arrParams(65).Direction = ParameterDirection.Output
            arrParams(66) = New SqlParameter("@SOURCE", SqlDbType.Char, 1)
            arrParams(66).Value = strOrigin

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

            arrParams(68) = New SqlParameter("@GO", SqlDbType.Char, 2)
            If .GrantOfficeId.Trim.Equals(String.Empty) Then
                arrParams(68).Value = DBNull.Value
            Else
                arrParams(68).Value = .GrantOfficeId.Trim
            End If


        End With

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

        Try
            SqlHelper.ExecuteNonQuery(tran, CommandType.StoredProcedure, strSPName, arrParams)
            prmCase.CaseID = arrParams(65).Value

            'added by raul callupe 20/04/2006
            Update_Case_Stage(prmCase, strOrigin, tran)

            SaveCoEstimates(prmCase, tran)

            If prmCase.Reminder = "1" And Not (prmCase.Status = STATUS_CANCELLED Or prmCase.Status = STATUS_CLOSED) Then
                UpdateReminderStatus(prmCase, strOrigin, tran)
            End If
            'end added

            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

    Private Sub Update_Case_Stage(ByRef prmCase As EC_Case, ByVal strOrigin As String, ByVal sqlTran As SqlTransaction)
        Dim strSqlCommand As String = "SP_CAMS_SET_UPDATE_CASE_STAGE"
        Dim arrParams(11) As SqlParameter

        With prmCase
            arrParams(0) = New SqlParameter("@CASEID", SqlDbType.Char, 6)
            arrParams(0).Value = prmCase.CaseID

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

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

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

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

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

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

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

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

            arrParams(9) = New SqlParameter("@SOURCE", SqlDbType.Char, 1)
            arrParams(9).Value = strOrigin

            arrParams(10) = New SqlParameter("@CHANGEDBY", SqlDbType.VarChar, 15)
            arrParams(10).Value = prmCase.AddedBy

            arrParams(11) = New SqlParameter("@STAGE", SqlDbType.VarChar, 6)
            arrParams(11).Direction = ParameterDirection.Output

        End With

        Try
            SqlHelper.ExecuteNonQuery(sqlTran, CommandType.StoredProcedure, strSqlCommand, arrParams)
            prmCase.Stage = arrParams(11).Value
        Catch ex As Exception
            Throw ex
        End Try

    End Sub

    Private Sub UpdateReminderStatus(ByRef prmCase As EC_Case, ByVal strOrigin As String, ByVal sqlTran As SqlTransaction)
        Dim strSqlCommand As String = "SP_CAMS_SET_UPDATE_REMINDERS_STATUS"
        Dim arrParams(26) As SqlParameter

        With prmCase
            arrParams(0) = New SqlParameter("@CASEID", SqlDbType.Char, 6)
            arrParams(0).Value = prmCase.CaseID

            arrParams(1) = New SqlParameter("@STAGE", SqlDbType.VarChar, 6)
            arrParams(1).Value = .Stage

            arrParams(2) = New SqlParameter("@STATUS", SqlDbType.VarChar, 2)
            arrParams(2).Value = .Status

            arrParams(3) = New SqlParameter("@GRANTTYPEID", SqlDbType.Char, 3)
            If .GrantTypeId.Trim.Equals(String.Empty) Then
                arrParams(3).Value = DBNull.Value
            Else
                arrParams(3).Value = .GrantTypeId
            End If

            arrParams(4) = New SqlParameter("@OGSGRANTTYPEID", SqlDbType.Char, 3)
            If .OGSGrantTypeId.Trim.Equals(String.Empty) Then
                arrParams(4).Value = DBNull.Value
            Else
                arrParams(4).Value = .OGSGrantTypeId
            End If

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

            arrParams(25) = New SqlParameter("@SOURCE", SqlDbType.Char, 1)
            arrParams(25).Value = strOrigin

            arrParams(26) = New SqlParameter("@CHANGEDBY", SqlDbType.VarChar, 15)
            arrParams(26).Value = prmCase.AddedBy

        End With

        Try
            SqlHelper.ExecuteNonQuery(sqlTran, CommandType.StoredProcedure, strSqlCommand, arrParams)
        Catch ex As Exception
            Throw ex
        End Try
    End Sub

    Private Sub SaveCoEstimates(ByRef prmCase As EC_Case, ByVal sqlTran As SqlTransaction)
        Dim strSqlCommand As String = "SP_CAMS_SET_INSERT_CASE_COESTIMATE"
        Dim _CoEstimates() As EC_CoEstimates = prmCase.CoEstimates

        If Not _CoEstimates Is Nothing Then

            For i As Integer = 0 To _CoEstimates.Length - 1

                Dim arrParams(6) As SqlParameter

                With _CoEstimates(i)
                    arrParams(0) = New SqlParameter("@CASEID", SqlDbType.Char, 6)
                    arrParams(0).Value = prmCase.CaseID
                    arrParams(1) = New SqlParameter("@CONTRACTORID", SqlDbType.Int)
                    arrParams(1).Value = .ContractorID

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

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

                    arrParams(4) = New SqlParameter("@NEWESTIMATE", SqlDbType.Char, 1)
                    arrParams(4).Value = .NewEstimate
                    arrParams(5) = New SqlParameter("@CHANGEDBY", SqlDbType.VarChar, 15)
                    arrParams(5).Value = prmCase.ChangedBy
                    arrParams(6) = New SqlParameter("@ADDEDBY", SqlDbType.VarChar, 15)
                    arrParams(6).Value = prmCase.AddedBy
                End With

                Try
                    SqlHelper.ExecuteNonQuery(sqlTran, CommandType.StoredProcedure, strSqlCommand, arrParams)
                Catch ex As Exception
                    Throw ex
                End Try

            Next

        End If

    End Sub

    Public Function UpdateCase(ByVal prmCase As EC_Case, ByVal strOrigin As String, Optional ByRef strErrorMsg As String = "") As Boolean
        Dim strConnectionString As String = ConfigurationSettings.AppSettings("ConnectionString")
        Dim strSPName As String = "SP_CAMS_SET_UPDATE_CASE"
        Dim arrParams(67) As SqlParameter
        Dim bSuccess As Boolean = False

        With prmCase
            arrParams(0) = New SqlParameter("@APPAPROVAL", SqlDbType.DateTime)
            If .AppAproval.Trim.Equals(String.Empty) Then
                arrParams(0).Value = DBNull.Value
            Else
                arrParams(0).Value = DateTime.Parse(.AppAproval, myDTFI)
            End If

            arrParams(1) = New SqlParameter("@APPCLIENTPORTION", SqlDbType.Decimal)
            If .AppClientPortion.Trim.Equals(String.Empty) Then
                arrParams(1).Value = DBNull.Value
            Else
                arrParams(1).Value = .AppClientPortion
            End If

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

            arrParams(3) = New SqlParameter("@APPFOLDINT", SqlDbType.Decimal)
            If .AppFoldInt.Trim.Equals(String.Empty) Then
                arrParams(3).Value = DBNull.Value
            Else
                arrParams(3).Value = .AppFoldInt
            End If

            arrParams(4) = New SqlParameter("@APPLIDN", SqlDbType.Char, 9)
            arrParams(4).Value = .ApplIDN
            arrParams(5) = New SqlParameter("@APPMONEY", SqlDbType.Char, 1)
            arrParams(5).Value = .AppMoney
            arrParams(6) = New SqlParameter("@APPMONEYDESC", SqlDbType.VarChar, 70)
            arrParams(6).Value = .AppMoneyDesc

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

            arrParams(8) = New SqlParameter("@APPSL20A", SqlDbType.Char, 1)
            arrParams(8).Value = .AppSL20A
            arrParams(9) = New SqlParameter("@APPSL20B", SqlDbType.Char, 1)
            arrParams(9).Value = .AppSl20B
            arrParams(10) = New SqlParameter("@APPSL20C", SqlDbType.Char, 1)
            arrParams(10).Value = .AppSL20C

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

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

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

            arrParams(14) = New SqlParameter("@ARCHBCONTROL", SqlDbType.Char, 1)
            arrParams(14).Value = .ArchBControl

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

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

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

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

            arrParams(19) = New SqlParameter("@CDLandCons", SqlDbType.Char, 1)
            arrParams(19).Value = .CDLandCons

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

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

            arrParams(22) = New SqlParameter("@CDMORTCONS", SqlDbType.Char, 1)
            arrParams(22).Value = .CDMortCons

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

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

            arrParams(25) = New SqlParameter("@CDPROOF", SqlDbType.Char, 1)
            arrParams(25).Value = .CDProof

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

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

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

            arrParams(29) = New SqlParameter("@CONTRACTORID", SqlDbType.Int)
            If .ContractorId.Trim.Equals(String.Empty) Then
                arrParams(29).Value = DBNull.Value
            Else
                arrParams(29).Value = .ContractorId
            End If

            arrParams(30) = New SqlParameter("@GRANTTYPEID", SqlDbType.Char, 3)
            If .GrantTypeId.Trim.Equals(String.Empty) Then
                arrParams(30).Value = DBNull.Value
            Else
                arrParams(30).Value = .GrantTypeId
            End If

            arrParams(31) = New SqlParameter("@HA", SqlDbType.Int)
            If .HA.Trim.Equals(String.Empty) Then
                arrParams(31).Value = DBNull.Value
            Else
                arrParams(31).Value = .HA
            End If

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

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

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

            arrParams(35) = New SqlParameter("@OGREFNUM", SqlDbType.VarChar, 10)
            arrParams(35).Value = .OGRefNum

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

            arrParams(37) = New SqlParameter("@OGSGRANTTYPEID", SqlDbType.Char, 3)
            If .OGSGrantTypeId.Trim.Equals(String.Empty) Then
                arrParams(37).Value = DBNull.Value
            Else
                arrParams(37).Value = .OGSGrantTypeId
            End If

            arrParams(38) = New SqlParameter("@OGSIMULT", SqlDbType.Char, 1)
            arrParams(38).Value = .OGSimult
            arrParams(39) = New SqlParameter("@OGSREFNUM", SqlDbType.VarChar, 10)
            arrParams(39).Value = .OGSRefNum

            arrParams(40) = New SqlParameter("@PROFESSIONALID", SqlDbType.Int)
            If .ProfessionalId.Trim.Equals(String.Empty) Then
                arrParams(40).Value = DBNull.Value
            Else
                arrParams(40).Value = .ProfessionalId
            End If

            arrParams(41) = New SqlParameter("@REASONID", SqlDbType.Char, 2)
            If .ReasonId.Trim.Equals(String.Empty) Then
                arrParams(41).Value = DBNull.Value
            Else
                arrParams(41).Value = .ReasonId
            End If

            arrParams(42) = New SqlParameter("@REMINDER", SqlDbType.Char, 1)
            arrParams(42).Value = .Reminder

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

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

            arrParams(45) = New SqlParameter("@STATUS", SqlDbType.VarChar, 2)
            arrParams(45).Value = .Status

            arrParams(46) = New SqlParameter("@TA", SqlDbType.Int)
            If .TA.Trim.Equals(String.Empty) Then
                arrParams(46).Value = DBNull.Value
            Else
                arrParams(46).Value = .TA
            End If

            arrParams(47) = New SqlParameter("@WCBCONTROL", SqlDbType.Char, 1)
            arrParams(47).Value = .WCBControl
            arrParams(48) = New SqlParameter("@WCCOMMCARD", SqlDbType.Char, 1)
            arrParams(48).Value = .WCCommCard

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

            arrParams(50) = New SqlParameter("@WCCompCard", SqlDbType.Char, 1)
            arrParams(50).Value = .WCCompCard

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

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

            arrParams(53) = New SqlParameter("@WCDESCDOC", SqlDbType.VarChar, 100)
            arrParams(53).Value = .WCDescDoc
            arrParams(54) = New SqlParameter("@WCFINALINV", SqlDbType.Char, 1)
            arrParams(54).Value = .WCFinalInv

            arrParams(55) = New SqlParameter("@WCGRANT", SqlDbType.Decimal)
            If .WCGrant.Trim.Equals(String.Empty) Then
                arrParams(55).Value = DBNull.Value
            Else
                arrParams(55).Value = .WCGrant
            End If

            arrParams(56) = New SqlParameter("@WCHR7", SqlDbType.Char, 1)
            arrParams(56).Value = .WCHR7
            arrParams(57) = New SqlParameter("@WCNICEIC", SqlDbType.Char, 1)
            arrParams(57).Value = .WCNICEIC
            arrParams(58) = New SqlParameter("@WCOTHERDOC", SqlDbType.Char, 1)
            arrParams(58).Value = .WCOtherDoc

            arrParams(59) = New SqlParameter("@WCOVERCOST", SqlDbType.Decimal)
            If .WCOverCost.Trim.Equals(String.Empty) Then
                arrParams(59).Value = DBNull.Value
            Else
                arrParams(59).Value = .WCOverCost
            End If

            arrParams(60) = New SqlParameter("@WCSGRANT", SqlDbType.Decimal)
            If .WCSGrant.Trim.Equals(String.Empty) Then
                arrParams(60).Value = DBNull.Value
            Else
                arrParams(60).Value = .WCSGrant
            End If

            arrParams(61) = New SqlParameter("@WCSNAG", SqlDbType.Char, 1)
            arrParams(61).Value = .WCSnag

            arrParams(62) = New SqlParameter("@WCSOVERCOST", SqlDbType.Decimal)
            If .WCSOverCost.Trim.Equals(String.Empty) Then
                arrParams(62).Value = DBNull.Value
            Else
                arrParams(62).Value = .WCSOverCost
            End If

            arrParams(63) = New SqlParameter("@CASEID", SqlDbType.Char, 6)
            arrParams(63).Value = .CaseID
            arrParams(64) = New SqlParameter("@CHANGEDBY", SqlDbType.VarChar, 15)
            arrParams(64).Value = .ChangedBy
            arrParams(65) = New SqlParameter("@SOURCE", SqlDbType.Char, 1)
            arrParams(65).Value = strOrigin

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

            arrParams(67) = New SqlParameter("@GO", SqlDbType.Char, 2)
            If .GrantOfficeId.Trim.Equals(String.Empty) Then
                arrParams(67).Value = DBNull.Value
            Else
                arrParams(67).Value = .GrantOfficeId.Trim
            End If

        End With

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

        Try
            SqlHelper.ExecuteNonQuery(tran, CommandType.StoredProcedure, strSPName, arrParams)

            'added by raul callupe 20/04/2006
            Update_Case_Stage(prmCase, strOrigin, tran)

            DeleteCoEstimates(prmCase, tran)
            SaveCoEstimates(prmCase, tran)

            'If prmCase.Reminder = "1" And Not (prmCase.Status = STATUS_CANCELLED Or prmCase.Status = STATUS_CLOSED) Then
            If prmCase.Reminder = "1" And Not (prmCase.Status = STATUS_CANCELLED) Then
                UpdateReminderStatus(prmCase, strOrigin, tran)
            End If
            'end added

            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

    Private Sub DeleteCoEstimates(ByVal prmCase As EC_Case, ByVal sqlTran As SqlTransaction)
        Dim strSqlCommand As String = "SP_CAMS_SET_DELETE_CASE_COESTIMATES"
        Dim _CoEstiamtes() As EC_CoEstimates = prmCase.CoEstimates
        Dim arrParams(0) As SqlParameter

        arrParams(0) = New SqlParameter("@CASEID", SqlDbType.Char, 6)
        arrParams(0).Value = prmCase.CaseID

        Try
            SqlHelper.ExecuteNonQuery(sqlTran, CommandType.StoredProcedure, strSqlCommand, arrParams)
        Catch ex As Exception
            Throw ex
        End Try

    End Sub

    Public Function GetNumberCases(ByVal AccessGroup As String, ByVal Status As String, Optional ByVal UserId As Integer = 0) As Integer
        Dim strConnectionString As String = ConfigurationSettings.AppSettings("ConnectionString")
        Dim strSPName As String = "SP_CAMS_GET_NUMBER_CASES"
        Dim arrParams(3) As SqlParameter

        arrParams(0) = New SqlParameter("@UserId", SqlDbType.Int)
        arrParams(0).Value = UserId

        arrParams(1) = New SqlParameter("@AccessGroup", SqlDbType.Char, 2)
        arrParams(1).Value = AccessGroup

        arrParams(2) = New SqlParameter("@Status", SqlDbType.VarChar, 2)
        arrParams(2).Value = Status

        arrParams(3) = New SqlParameter("@Total", SqlDbType.Int)
        arrParams(3).Direction = ParameterDirection.Output

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

        If arrParams(3).Value Is System.DBNull.Value Then
            Return 0
        Else
            Return arrParams(3).Value
        End If


    End Function


    Public Function DeleteCase(ByVal prmCase As EC_Case, Optional ByRef strErrorMsg As String = "") As Boolean
        Dim strConnectionString As String = ConfigurationSettings.AppSettings("ConnectionString")
        Dim strSPName As String = "SP_CAMS_SET_DELETE_CASE"
        Dim arrParams(1) As SqlParameter
        Dim bSuccess As Boolean = False

        With prmCase
            arrParams(0) = New SqlParameter("@CASEID", SqlDbType.Char, 6)
            arrParams(0).Value = .CaseID
            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


    Public Function ImportCases(ByVal sCasesXML As String, ByVal AccessGroup As String, ByVal UserId As Integer, ByVal UserLogin As String, ByVal sFileName As String, ByVal nNumCases As Integer, ByVal nInvalidFormat As Integer, Optional ByRef strErrorMsg As String = "") As Boolean
        Dim strConnectionString As String = ConfigurationSettings.AppSettings("ConnectionString")
        Dim strSPName As String = "SP_CAMS_GET_CASES_TO_IMPORT"
        'Dim strSPName As String = "SP_CAMS_GET_CASES_TO_IMPORT_INITIAL_LOAD"
        Dim arrParams(7) As SqlParameter
        Dim bSuccess As Boolean = False

        arrParams(0) = New SqlParameter("@CasesXML", SqlDbType.Text)
        If sCasesXML.Equals(String.Empty) Then
            arrParams(0).Value = DBNull.Value
        Else
            arrParams(0).Value = sCasesXML
        End If

        arrParams(1) = New SqlParameter("@ACCESSGROUP", SqlDbType.Char)
        If AccessGroup.Equals(String.Empty) Then
            arrParams(1).Value = DBNull.Value
        Else
            arrParams(1).Value = AccessGroup
        End If

        arrParams(2) = New SqlParameter("@USERID", SqlDbType.Int)
        If UserId.Equals(String.Empty) Then
            arrParams(2).Value = DBNull.Value
        Else
            arrParams(2).Value = UserId
        End If

        arrParams(3) = New SqlParameter("@USERLOGIN", SqlDbType.VarChar)
        If UserLogin.Equals(String.Empty) Then
            arrParams(3).Value = DBNull.Value
        Else
            arrParams(3).Value = UserLogin
        End If

        arrParams(4) = New SqlParameter("@FILENAME", SqlDbType.VarChar)
        If sFileName.Equals(String.Empty) Then
            arrParams(4).Value = DBNull.Value
        Else
            arrParams(4).Value = sFileName
        End If

        arrParams(5) = New SqlParameter("@NUMCASES", SqlDbType.Int)
        If nNumCases.Equals(String.Empty) Then
            arrParams(5).Value = DBNull.Value
        Else
            arrParams(5).Value = nNumCases
        End If

        arrParams(6) = New SqlParameter("@NUMINVFORMAT", SqlDbType.Int)
        If nInvalidFormat.Equals(String.Empty) Then
            arrParams(6).Value = DBNull.Value
        Else
            arrParams(6).Value = nInvalidFormat
        End If

        arrParams(7) = New SqlParameter("@LOGIMPORTID", SqlDbType.Int)
        arrParams(7).Direction = ParameterDirection.Output

        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
            If Not arrParams(7).Value Is DBNull.Value Then
                strErrorMsg = arrParams(7).Value
            End If
        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 GetLogImport(ByVal nLogImportId As Integer) As String
        Dim strConnectionString As String = ConfigurationSettings.AppSettings("ConnectionString")
        Dim strSPName As String = "SP_CAMS_GET_LOG_IMPORT"
        Dim arrParams(1) As SqlParameter
        Dim strMessage As String = String.Empty

        arrParams(0) = New SqlParameter("@LOGIMPORTID", SqlDbType.Int)
        If nLogImportId.Equals(String.Empty) Then
            arrParams(0).Value = DBNull.Value
        Else
            arrParams(0).Value = nLogImportId
        End If

        arrParams(1) = New SqlParameter("@message", SqlDbType.VarChar, 8000)
        arrParams(1).Direction = ParameterDirection.Output

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

            If arrParams(1).Value Is System.DBNull.Value Then
                strMessage = String.Empty
            Else
                strMessage = arrParams(1).Value
            End If

        Catch ex As Exception
        End Try

        Return strMessage
    End Function


    Public Function GetSummaryImport(ByVal nLogImportId As Integer) As DataSet
        Dim strConnectionString As String = ConfigurationSettings.AppSettings("ConnectionString")
        Dim strSPName As String = "SP_CAMS_GET_TOTAL_IMPORT"
        Dim arrParams(0) As SqlParameter
        Dim dsResult As DataSet

        arrParams(0) = New SqlParameter("@LOGIMPORTID", SqlDbType.Int)
        arrParams(0).Value = nLogImportId

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

        Return dsResult

    End Function

    Public Function GetCasestoExport(ByVal DateIni As String, ByVal DateEnd As String, ByVal sStatus As String) As DataSet
        Dim strConnectionString As String = ConfigurationSettings.AppSettings("ConnectionString")
        Dim strSPName As String = "SP_CAMS_GET_CASES_TO_EXPORT"
        Dim arrParams(2) As SqlParameter
        Dim dsResult As DataSet

        arrParams(0) = New SqlParameter("@DATEINI", SqlDbType.DateTime)
        If DateIni Is String.Empty Then
            arrParams(0).Value = DBNull.Value
        Else
            arrParams(0).Value = DateTime.Parse(DateIni, myDTFI)
        End If

        arrParams(1) = New SqlParameter("@DATEEND", SqlDbType.DateTime)
        If DateEnd Is String.Empty Then
            arrParams(1).Value = DBNull.Value
        Else
            arrParams(1).Value = DateTime.Parse(DateEnd, myDTFI)
        End If

        arrParams(2) = New SqlParameter("@VSTATUS", SqlDbType.VarChar)
        If DateEnd Is String.Empty Then
            arrParams(2).Value = DBNull.Value
        Else
            arrParams(2).Value = sStatus
        End If

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

        Return dsResult

    End Function

    Public Function GetErrorImport(ByVal nLogImportId As Integer) As DataSet
        Dim strConnectionString As String = ConfigurationSettings.AppSettings("ConnectionString")
        Dim strSPName As String = "SP_CAMS_GET_ERRORS_IMPORT"
        Dim arrParams(0) As SqlParameter
        Dim dsResult As DataSet

        arrParams(0) = New SqlParameter("@LOGIMPORTID", SqlDbType.Int)
        arrParams(0).Value = nLogImportId

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

        Return dsResult

    End Function
    Public Function GetWorkOffline(ByVal nUserId As Integer, ByVal sAccessGroup As String) As DataSet
        Dim strConnectionString As String = ConfigurationSettings.AppSettings("ConnectionString")
        Dim strSPName As String = "SP_CAMS_GET_WORK_OFFLINE"
        Dim arrParams(1) As SqlParameter
        Dim dsResult As DataSet

        arrParams(0) = New SqlParameter("@USERID", SqlDbType.Int)
        arrParams(0).Value = nUserId

        arrParams(1) = New SqlParameter("@ACCESSGROUP", SqlDbType.Char, 2)
        arrParams(1).Value = sAccessGroup

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

    End Function


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

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

    End Function

    Public Function GetDetailErrorsImport(ByVal nLogImportId As Integer, ByVal nErrorId As Integer) As DataSet
        Dim strConnectionString As String = ConfigurationSettings.AppSettings("ConnectionString")
        Dim strSPName As String = "SP_CAMS_GET_DETAIL_ERRORS_IMPORT"
        Dim arrParams(1) As SqlParameter
        Dim dsResult As DataSet

        arrParams(0) = New SqlParameter("@LOGIMPORTID", SqlDbType.Int)
        arrParams(0).Value = nLogImportId

        arrParams(1) = New SqlParameter("@ERRORID", SqlDbType.Int)
        arrParams(1).Value = nErrorId

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

        Return dsResult

    End Function

    Public Function GetQuerytoExport(ByVal DateIni As String, ByVal DateEnd As String, ByVal sField As String) As DataSet
        Dim strConnectionString As String = ConfigurationSettings.AppSettings("ConnectionString")
        Dim strSPName As String = "SP_CAMS_GET_QUERY_TO_EXPORT"
        Dim arrParams(2) As SqlParameter
        Dim dsResult As DataSet

        arrParams(0) = New SqlParameter("@DATEINI", SqlDbType.DateTime)
        If DateIni Is String.Empty Then
            arrParams(0).Value = DBNull.Value
        Else
            arrParams(0).Value = DateTime.Parse(DateIni, myDTFI)
        End If

        arrParams(1) = New SqlParameter("@DATEEND", SqlDbType.DateTime)
        If DateEnd Is String.Empty Then
            arrParams(1).Value = DBNull.Value
        Else
            arrParams(1).Value = DateTime.Parse(DateEnd, myDTFI)
        End If

        arrParams(2) = New SqlParameter("@FIELD", SqlDbType.Char)
        arrParams(2).Value = sField


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

        Return dsResult

    End Function


End Class




Public Class EC_Case

    Private _strCaseId As String
    Private _strApplIDN As String = ""
    Private _strStage As String = ""
    Private _strStatus As String = ""
    Private _strReasonId As String = ""
    Private _strReminder As String = ""
    Private _strOGRefSche As String = ""
    Private _strOGCContact As String = ""
    Private _strOGCVisited As String = ""
    Private _strOGAmdSche As String = ""
    Private _strHA As String = ""
    Private _strGrantTypeId As String = ""
    Private _strOGRefNum As String = ""
    Private _strOGSimult As String = ""
    Private _strOGSGrantTypeId As String = ""
    Private _strOGSRefNum As String = ""
    Private _strProfessionalId As String = ""
    Private _strArchSPReq As String = ""
    Private _strArchSPRec As String = ""
    Private _strArchWDReq As String = ""
    Private _strArchWDRec As String = ""
    Private _strArchBControl As String = ""
    Private _strContractorId As String = ""
    Private _strCDProof As String = ""
    Private _strCDProofReq As String = ""
    Private _strCDProofRec As String = ""
    Private _strCDLandCons As String = ""
    Private _strCDLandReq As String = ""
    Private _strCDLandRec As String = ""
    Private _strCDMortCons As String = ""
    Private _strCDMortReq As String = ""
    Private _strCDMortRec As String = ""
    Private _strAppDate As String = ""
    Private _strAppAproval As String = ""
    Private _strAppRevSch As String = ""
    Private _strAppSL20A As String = ""
    Private _strAppSl20B As String = ""
    Private _strAppSL20C As String = ""
    Private _strAppSL20Req As String = ""
    Private _strAppSL20Rec As String = ""
    Private _strAppMoney As String = ""
    Private _strAppMoneyDesc As String = ""
    Private _strAppClientPortion As String = ""
    Private _strAppFoldInt As String = ""
    Private _strAppSL21 As String = ""
    Private _strTA As String = ""
    Private _strSL21ConfAssig As String = ""
    Private _strSL21OnSite As String = ""
    Private _strWCFinalInv As String = ""
    Private _strWCNICEIC As String = ""
    Private _strWCHR7 As String = ""
    Private _strWCOtherDoc As String = ""
    Private _strWCDescDoc As String = ""
    Private _strWCBControl As String = ""
    Private _strWCCommCard As String = ""
    Private _strWCCompCard As String = ""
    Private _strWCCOS As String = ""
    Private _strWCCOMP As String = ""
    Private _strWCSnag As String = ""
    Private _strWCOverCost As String = ""
    Private _strWCGrant As String = ""
    Private _strWCSOverCost As String = ""
    Private _strWCSGrant As String = ""
    Private _strWCDateRec As String = ""
    Private _strComments As String = ""
    Private _strCancdate As String = ""
    Private _strGrantOfficeId As String = ""
    Private _strChangedBy As String = ""
    Private _strAddedBy As String = ""
    Private _CoEstimates() As EC_CoEstimates

    Public Property CaseID() As String
        Get
            Return _strCaseId
        End Get
        Set(ByVal Value As String)
            _strCaseId = Value
        End Set
    End Property

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

    Public Property Stage() As String
        Get
            Return _strStage
        End Get
        Set(ByVal Value As String)
            _strStage = Value
        End Set
    End Property

    Public Property Status() As String
        Get
            Return _strStatus
        End Get
        Set(ByVal Value As String)
            _strStatus = Value
        End Set
    End Property

    Public Property ReasonId() As String
        Get
            Return _strReasonId
        End Get
        Set(ByVal Value As String)
            _strReasonId = Value
        End Set
    End Property

    Public Property Reminder() As String
        Get
            Return _strReminder
        End Get
        Set(ByVal Value As String)
            _strReminder = Value
        End Set
    End Property

    Public Property OGRefSche() As String
        Get
            Return _strOGRefSche
        End Get
        Set(ByVal Value As String)
            _strOGRefSche = Value
        End Set
    End Property

    Public Property OGCContact() As String
        Get
            Return _strOGCContact
        End Get
        Set(ByVal Value As String)
            _strOGCContact = Value
        End Set
    End Property

    Public Property OGCVisited() As String
        Get
            Return _strOGCVisited
        End Get
        Set(ByVal Value As String)
            _strOGCVisited = Value
        End Set
    End Property

    Public Property OGAmdSche() As String
        Get
            Return _strOGAmdSche
        End Get
        Set(ByVal Value As String)
            _strOGAmdSche = Value
        End Set
    End Property

    Public Property HA() As String
        Get
            Return _strHA
        End Get
        Set(ByVal Value As String)
            _strHA = Value
        End Set
    End Property

    Public Property GrantTypeId() As String
        Get
            Return _strGrantTypeId
        End Get
        Set(ByVal Value As String)
            _strGrantTypeId = Value
        End Set
    End Property

    Public Property OGRefNum() As String
        Get
            Return _strOGRefNum
        End Get
        Set(ByVal Value As String)
            _strOGRefNum = Value
        End Set
    End Property

    Public Property OGSimult() As String
        Get
            Return _strOGSimult
        End Get
        Set(ByVal Value As String)
            _strOGSimult = Value
        End Set
    End Property

    Public Property OGSGrantTypeId() As String
        Get
            Return _strOGSGrantTypeId
        End Get
        Set(ByVal Value As String)
            _strOGSGrantTypeId = Value
        End Set
    End Property

    Public Property OGSRefNum() As String
        Get
            Return _strOGSRefNum
        End Get
        Set(ByVal Value As String)
            _strOGSRefNum = 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 ArchSPReq() As String
        Get
            Return _strArchSPReq
        End Get
        Set(ByVal Value As String)
            _strArchSPReq = Value
        End Set
    End Property

    Public Property ArchSPRec() As String
        Get
            Return _strArchSPRec
        End Get
        Set(ByVal Value As String)
            _strArchSPRec = Value
        End Set
    End Property

    Public Property ArchWDReq() As String
        Get
            Return _strArchWDReq
        End Get
        Set(ByVal Value As String)
            _strArchWDReq = Value
        End Set
    End Property

    Public Property ArchWDRec() As String
        Get
            Return _strArchWDRec
        End Get
        Set(ByVal Value As String)
            _strArchWDRec = Value
        End Set
    End Property

    Public Property ArchBControl() As String
        Get
            Return _strArchBControl
        End Get
        Set(ByVal Value As String)
            _strArchBControl = Value
        End Set
    End Property

    Public Property ContractorId() As String
        Get
            Return _strContractorId
        End Get
        Set(ByVal Value As String)
            _strContractorId = Value
        End Set
    End Property

    Public Property CDProof() As String
        Get
            Return _strCDProof
        End Get
        Set(ByVal Value As String)
            _strCDProof = Value
        End Set
    End Property

    Public Property CDProofReq() As String
        Get
            Return _strCDProofReq
        End Get
        Set(ByVal Value As String)
            _strCDProofReq = Value
        End Set
    End Property

    Public Property CDProofRec() As String
        Get
            Return _strCDProofRec
        End Get
        Set(ByVal Value As String)
            _strCDProofRec = Value
        End Set
    End Property

    Public Property CDLandCons() As String
        Get
            Return _strCDLandCons
        End Get
        Set(ByVal Value As String)
            _strCDLandCons = Value
        End Set
    End Property

    Public Property CDLandReq() As String
        Get
            Return _strCDLandReq
        End Get
        Set(ByVal Value As String)
            _strCDLandReq = Value
        End Set
    End Property

    Public Property CDLandRec() As String
        Get
            Return _strCDLandRec
        End Get
        Set(ByVal Value As String)
            _strCDLandRec = Value
        End Set
    End Property

    Public Property CDMortCons() As String
        Get
            Return _strCDMortCons
        End Get
        Set(ByVal Value As String)
            _strCDMortCons = Value
        End Set
    End Property

    Public Property CDMortReq() As String
        Get
            Return _strCDMortReq
        End Get
        Set(ByVal Value As String)
            _strCDMortReq = Value
        End Set
    End Property

    Public Property CDMortRec() As String
        Get
            Return _strCDMortRec
        End Get
        Set(ByVal Value As String)
            _strCDMortRec = Value
        End Set
    End Property

    Public Property AppDate() As String
        Get
            Return _strAppDate
        End Get
        Set(ByVal Value As String)
            _strAppDate = Value
        End Set
    End Property

    Public Property AppAproval() As String
        Get
            Return _strAppAproval
        End Get
        Set(ByVal Value As String)
            _strAppAproval = Value
        End Set
    End Property

    Public Property AppRevSch() As String
        Get
            Return _strAppRevSch
        End Get
        Set(ByVal Value As String)
            _strAppRevSch = Value
        End Set
    End Property

    Public Property AppSL20A() As String
        Get
            Return _strAppSL20A
        End Get
        Set(ByVal Value As String)
            _strAppSL20A = Value
        End Set
    End Property

    Public Property AppSl20B() As String
        Get
            Return _strAppSl20B
        End Get
        Set(ByVal Value As String)
            _strAppSl20B = Value
        End Set
    End Property

    Public Property AppSL20C() As String
        Get
            Return _strAppSL20C
        End Get
        Set(ByVal Value As String)
            _strAppSL20C = Value
        End Set
    End Property

    Public Property AppSL20Req() As String
        Get
            Return _strAppSL20Req
        End Get
        Set(ByVal Value As String)
            _strAppSL20Req = Value
        End Set
    End Property

    Public Property AppSL20Rec()
        Get
            Return _strAppSL20Rec
        End Get
        Set(ByVal Value)
            _strAppSL20Rec = Value
        End Set
    End Property

    Public Property AppMoney() As String
        Get
            Return _strAppMoney
        End Get
        Set(ByVal Value As String)
            _strAppMoney = Value
        End Set
    End Property

    Public Property AppMoneyDesc() As String
        Get
            Return _strAppMoneyDesc
        End Get
        Set(ByVal Value As String)
            _strAppMoneyDesc = Value
        End Set
    End Property

    Public Property AppClientPortion() As String
        Get
            Return _strAppClientPortion
        End Get
        Set(ByVal Value As String)
            _strAppClientPortion = Value
        End Set
    End Property

    Public Property AppFoldInt() As String
        Get
            Return _strAppFoldInt
        End Get
        Set(ByVal Value As String)
            _strAppFoldInt = Value
        End Set
    End Property

    Public Property AppSL21() As String
        Get
            Return _strAppSL21
        End Get
        Set(ByVal Value As String)
            _strAppSL21 = Value
        End Set
    End Property

    Public Property TA() As String
        Get
            Return _strTA
        End Get
        Set(ByVal Value As String)
            _strTA = Value
        End Set
    End Property

    Public Property SL21ConfAssig() As String
        Get
            Return _strSL21ConfAssig
        End Get
        Set(ByVal Value As String)
            _strSL21ConfAssig = Value
        End Set
    End Property

    Public Property SL21OnSite() As String
        Get
            Return _strSL21OnSite
        End Get
        Set(ByVal Value As String)
            _strSL21OnSite = Value
        End Set
    End Property

    Public Property WCFinalInv() As String
        Get
            Return _strWCFinalInv
        End Get
        Set(ByVal Value As String)
            _strWCFinalInv = Value
        End Set
    End Property

    Public Property WCNICEIC() As String
        Get
            Return _strWCNICEIC
        End Get
        Set(ByVal Value As String)
            _strWCNICEIC = Value
        End Set
    End Property

    Public Property WCHR7() As String
        Get
            Return _strWCHR7
        End Get
        Set(ByVal Value As String)
            _strWCHR7 = Value
        End Set
    End Property

    Public Property WCOtherDoc() As String
        Get
            Return _strWCOtherDoc
        End Get
        Set(ByVal Value As String)
            _strWCOtherDoc = Value
        End Set
    End Property

    Public Property WCDescDoc() As String
        Get
            Return _strWCDescDoc
        End Get
        Set(ByVal Value As String)
            _strWCDescDoc = Value
        End Set
    End Property

    Public Property WCBControl() As String
        Get
            Return _strWCBControl
        End Get
        Set(ByVal Value As String)
            _strWCBControl = Value
        End Set
    End Property

    Public Property WCCommCard() As String
        Get
            Return _strWCCommCard
        End Get
        Set(ByVal Value As String)
            _strWCCommCard = Value
        End Set
    End Property

    Public Property WCCompCard() As String
        Get
            Return _strWCCompCard
        End Get
        Set(ByVal Value As String)
            _strWCCompCard = Value
        End Set
    End Property

    Public Property WCCOS() As String
        Get
            Return _strWCCOS
        End Get
        Set(ByVal Value As String)
            _strWCCOS = Value
        End Set
    End Property

    Public Property WCCOMP() As String
        Get
            Return _strWCCOMP
        End Get
        Set(ByVal Value As String)
            _strWCCOMP = Value
        End Set
    End Property

    Public Property WCSnag() As String
        Get
            Return _strWCSnag
        End Get
        Set(ByVal Value As String)
            _strWCSnag = Value
        End Set
    End Property

    Public Property WCOverCost() As String
        Get
            Return _strWCOverCost
        End Get
        Set(ByVal Value As String)
            _strWCOverCost = Value
        End Set
    End Property

    Public Property WCGrant() As String
        Get
            Return _strWCGrant
        End Get
        Set(ByVal Value As String)
            _strWCGrant = Value
        End Set
    End Property

    Public Property WCSOverCost() As String
        Get
            Return _strWCSOverCost
        End Get
        Set(ByVal Value As String)
            _strWCSOverCost = Value
        End Set
    End Property

    Public Property WCSGrant() As String
        Get
            Return _strWCSGrant
        End Get
        Set(ByVal Value As String)
            _strWCSGrant = Value
        End Set
    End Property

    Public Property WCDateRec() As String
        Get
            Return _strWCDateRec
        End Get
        Set(ByVal Value As String)
            _strWCDateRec = 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 ChangedBy() As String
        Get
            Return _strChangedBy
        End Get
        Set(ByVal Value As String)
            _strChangedBy = 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 CoEstimates() As EC_CoEstimates()
        Get
            Return _CoEstimates
        End Get
        Set(ByVal Value As EC_CoEstimates())
            _CoEstimates = Value
        End Set
    End Property

    Public Property Cancdate() As String
        Get
            Return _strCancdate
        End Get
        Set(ByVal Value As String)
            _strCancdate = Value
        End Set
    End Property

    Public Property GrantOfficeId() As String
        Get
            Return _strGrantOfficeId
        End Get
        Set(ByVal Value As String)
            _strGrantOfficeId = Value
        End Set
    End Property

End Class

Public Class EC_CoEstimates
    Private _strCaseId As String
    Private _nContractorId As Integer
    Private _strName As String
    Private _strDateReq As String
    Private _strDateRec As String
    Private _strNewEstimate As String

    Public Property CaseID() As String
        Get
            Return _strCaseId
        End Get
        Set(ByVal Value As String)
            _strCaseId = Value
        End Set
    End Property

    Public Property ContractorID() As Integer
        Get
            Return _nContractorId
        End Get
        Set(ByVal Value As Integer)
            _nContractorId = 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 DateReq() As String
        Get
            Return _strDateReq
        End Get
        Set(ByVal Value As String)
            _strDateReq = Value
        End Set
    End Property

    Public Property DateRec() As String
        Get
            Return _strDateRec
        End Get
        Set(ByVal Value As String)
            _strDateRec = Value
        End Set
    End Property

    Public Property NewEstimate() As String
        Get
            Return _strNewEstimate
        End Get
        Set(ByVal Value As String)
            _strNewEstimate = Value
        End Set
    End Property

End Class