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

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

Public Class CC_Contractor

    Sub New()

    End Sub

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

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

        dsResult.Tables(0).TableName = "LOCATIONS"
        dsResult.Tables(1).TableName = "WARRANTY"

        Return dsResult
    End Function

    Public Function GetContractors(ByVal nPage As Integer, ByVal nRecordsPerPage As Integer) As DataSet
        Dim strConnectionString As String = ConfigurationSettings.AppSettings("ConnectionString")
        Dim strSPName As String = "SP_CAMS_GET_CONTRACTORS"
        Dim arrParams(1) 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

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

        Return dsResult
    End Function

    Public Function GetContractorByID(ByVal nContractorID As Integer) As EC_Contractor
        Dim strConnectionString As String = ConfigurationSettings.AppSettings("ConnectionString")
        Dim strSPName As String = "SP_CAMS_GET_CONTRACTORBYID"
        Dim arrParams(0) As SqlParameter
        Dim dreader As SqlClient.SqlDataReader
        Dim oEC_Contractor As New EC_Contractor

        Try
            arrParams(0) = New SqlParameter("@CONTRACTORID", SqlDbType.Int)
            arrParams(0).Value = nContractorID
            dreader = SqlHelper.ExecuteReader(strConnectionString, CommandType.StoredProcedure, strSPName, arrParams)

            With oEC_Contractor
                While dreader.Read()
                    .ContractorId = IIf(dreader("CONTRACTORID") Is DBNull.Value, 0, dreader("CONTRACTORID"))
                    .CompanyName = IIf(dreader("COMPANYNAME") Is DBNull.Value, String.Empty, dreader("COMPANYNAME"))
                    .ContactName = IIf(dreader("CONTACTNAME") Is DBNull.Value, String.Empty, dreader("CONTACTNAME"))
                    .Email = IIf(dreader("EMAIL") Is DBNull.Value, String.Empty, dreader("EMAIL"))
                    .Fax = IIf(dreader("FAX") Is DBNull.Value, String.Empty, dreader("FAX"))
                    .Location = IIf(dreader("LOCATION") Is DBNull.Value, String.Empty, dreader("LOCATION"))
                    .Mobile = IIf(dreader("MOBILE") Is DBNull.Value, String.Empty, dreader("MOBILE"))
                    .Postcode = IIf(dreader("POSTCODE") Is DBNull.Value, String.Empty, dreader("POSTCODE"))
                    .Telephone = IIf(dreader("TELEPHONE") Is DBNull.Value, String.Empty, dreader("TELEPHONE"))
                    .ValueWork = IIf(dreader("VALUEWORK") Is DBNull.Value, String.Empty, dreader("VALUEWORK"))
                    .VATReg = IIf(dreader("VATREG") Is DBNull.Value, String.Empty, dreader("VATREG"))
                    .WarrantyID = IIf(dreader("WARRANTYID") Is DBNull.Value, String.Empty, dreader("WARRANTYID"))
                    .Address = IIf(dreader("ADDRESS") Is DBNull.Value, String.Empty, dreader("ADDRESS"))
                    .VATNumber = IIf(dreader("VATNUMBER") Is DBNull.Value, String.Empty, dreader("VATNUMBER"))
                    .AddedBy = IIf(dreader("ADDEDBY") Is DBNull.Value, String.Empty, dreader("ADDEDBY"))
                    .ChangedBy = IIf(dreader("CHANGEDBY") Is DBNull.Value, String.Empty, dreader("CHANGEDBY"))
                End While

            End With

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

        End Try

        Return oEC_Contractor
    End Function

    Public Function SaveContractor(ByVal prmContractor As EC_Contractor, Optional ByRef strErrorMsg As String = "") As Boolean
        Dim strConnectionString As String = ConfigurationSettings.AppSettings("ConnectionString")
        Dim strSPName As String = "SP_CAMS_SET_INSERT_CONTRACTOR"
        Dim arrParams(14) As SqlParameter
        Dim bSuccess As Boolean = False

        With prmContractor
            arrParams(0) = New SqlParameter("@COMPANYNAME", SqlDbType.VarChar, 100)
            arrParams(0).Value = .CompanyName
            arrParams(1) = New SqlParameter("@ADDRESS", SqlDbType.VarChar, 100)
            arrParams(1).Value = .Address
            arrParams(2) = New SqlParameter("@TELEPHONE", SqlDbType.VarChar, 12)
            arrParams(2).Value = .Telephone
            arrParams(3) = New SqlParameter("@FAX", SqlDbType.VarChar, 11)
            arrParams(3).Value = .Fax
            arrParams(4) = New SqlParameter("@EMAIL", SqlDbType.VarChar, 50)
            arrParams(4).Value = .Email
            arrParams(5) = New SqlParameter("@MOBILE", SqlDbType.VarChar, 12)
            arrParams(5).Value = .Mobile
            arrParams(6) = New SqlParameter("@LOCATION", SqlDbType.Char, 2)
            arrParams(6).Value = .Location
            arrParams(7) = New SqlParameter("@VATREG", SqlDbType.Char, 1)
            arrParams(7).Value = .VATReg
            arrParams(8) = New SqlParameter("@WARRANTYID", SqlDbType.Char, 2)
            arrParams(8).Value = .WarrantyID
            arrParams(9) = New SqlParameter("@VATNUMBER", SqlDbType.Char, 9)
            arrParams(9).Value = .VATNumber
            arrParams(10) = New SqlParameter("@CONTACTNAME", SqlDbType.VarChar, 60)
            arrParams(10).Value = .ContactName
            arrParams(11) = New SqlParameter("@VALUEWORK", SqlDbType.Decimal)

            If .ValueWork.Equals(String.Empty) Then
                arrParams(11).Value = DBNull.Value
            Else
                arrParams(11).Value = Convert.ToDecimal(.ValueWork)
            End If

            arrParams(12) = New SqlParameter("@POSTCODE", SqlDbType.VarChar, 7)
            arrParams(12).Value = .Postcode
            arrParams(13) = New SqlParameter("@ADDEDBY", SqlDbType.VarChar, 15)
            arrParams(13).Value = .AddedBy
            arrParams(14) = New SqlParameter("@CHANGEDBY", SqlDbType.VarChar, 15)
            arrParams(14).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 UpdateContractor(ByVal prmContractor As EC_Contractor, Optional ByRef strErrorMsg As String = "") As Boolean
        Dim strConnectionString As String = ConfigurationSettings.AppSettings("ConnectionString")
        Dim strSPName As String = "SP_CAMS_SET_UPDATE_CONTRACTOR"
        Dim arrParams(14) As SqlParameter
        Dim bSuccess As Boolean = False

        With prmContractor
            arrParams(0) = New SqlParameter("@CONTRACTORID", SqlDbType.VarChar, 100)
            arrParams(0).Value = .ContractorId
            arrParams(1) = New SqlParameter("@COMPANYNAME", SqlDbType.VarChar, 100)
            arrParams(1).Value = .CompanyName
            arrParams(2) = New SqlParameter("@ADDRESS", SqlDbType.VarChar, 100)
            arrParams(2).Value = .Address
            arrParams(3) = New SqlParameter("@TELEPHONE", SqlDbType.VarChar, 12)
            arrParams(3).Value = .Telephone
            arrParams(4) = New SqlParameter("@FAX", SqlDbType.VarChar, 11)
            arrParams(4).Value = .Fax
            arrParams(5) = New SqlParameter("@EMAIL", SqlDbType.VarChar, 50)
            arrParams(5).Value = .Email
            arrParams(6) = New SqlParameter("@MOBILE", SqlDbType.VarChar, 12)
            arrParams(6).Value = .Mobile
            arrParams(7) = New SqlParameter("@LOCATION", SqlDbType.Char, 2)
            arrParams(7).Value = .Location
            arrParams(8) = New SqlParameter("@VATREG", SqlDbType.Char, 1)
            arrParams(8).Value = .VATReg
            arrParams(9) = New SqlParameter("@WARRANTYID", SqlDbType.Char, 2)
            arrParams(9).Value = .WarrantyID
            arrParams(10) = New SqlParameter("@VATNUMBER", SqlDbType.Char, 9)
            arrParams(10).Value = .VATNumber
            arrParams(11) = New SqlParameter("@CONTACTNAME", SqlDbType.VarChar, 60)
            arrParams(11).Value = .ContactName
            arrParams(12) = New SqlParameter("@VALUEWORK", SqlDbType.Decimal)
            If .ValueWork.Equals(String.Empty) Then
                arrParams(12).Value = DBNull.Value
            Else
                arrParams(12).Value = Convert.ToDecimal(.ValueWork)
            End If
            arrParams(13) = New SqlParameter("@POSTCODE", SqlDbType.VarChar, 7)
            arrParams(13).Value = .Postcode
            arrParams(14) = New SqlParameter("@CHANGEDBY", SqlDbType.VarChar, 15)
            arrParams(14).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 DeleteContractor(ByVal prmContractor As EC_Contractor, Optional ByRef strErrorMsg As String = "") As Boolean
        Dim strConnectionString As String = ConfigurationSettings.AppSettings("ConnectionString")
        Dim strSPName As String = "SP_CAMS_SET_DELETE_CONTRACTOR"
        Dim arrParams(1) As SqlParameter
        Dim bSuccess As Boolean = False

        With prmContractor
            arrParams(0) = New SqlParameter("@CONTRACTORID", SqlDbType.Int)
            arrParams(0).Value = .ContractorId
            arrParams(1) = New SqlParameter("@CHANGEDBY", SqlDbType.VarChar, 15)
            arrParams(1).Value = .ChangedBy
        End With

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

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

        Return bSuccess
    End Function

End Class

Public Class EC_Contractor
    Private _nContractorId As Integer
    Private _strCompanyName As String = ""
    Private _strAddress As String = ""
    Private _strTelephone As String = ""
    Private _strFax As String = ""
    Private _strEmail As String = ""
    Private _strMobile As String = ""
    Private _strLocation As String = ""
    Private _strVatReg As String = ""
    Private _strWarrantyId As String = ""
    Private _strVatNumber As String = ""
    Private _strContactName As String = ""
    Private _strValueWork As String = ""
    Private _strPostcode As String = ""
    Private _strAddedBy As String = ""
    Private _strChangedBy As String = ""

    Public Property ContractorId() As Integer
        Get
            Return _nContractorId
        End Get
        Set(ByVal Value As Integer)
            _nContractorId = Value
        End Set
    End Property

    Public Property CompanyName() As String
        Get
            Return _strCompanyName
        End Get
        Set(ByVal Value As String)
            _strCompanyName = Value
        End Set
    End Property

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

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

    Public Property Fax() As String
        Get
            Return _strFax
        End Get
        Set(ByVal Value As String)
            _strFax = Value
        End Set
    End Property

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

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

    Public Property Location() As String
        Get
            Return _strLocation
        End Get
        Set(ByVal Value As String)
            _strLocation = Value
        End Set
    End Property

    Public Property VATReg() As String
        Get
            Return _strVatReg
        End Get
        Set(ByVal Value As String)
            _strVatReg = Value
        End Set
    End Property

    Public Property WarrantyID() As String
        Get
            Return _strWarrantyId
        End Get
        Set(ByVal Value As String)
            _strWarrantyId = Value
        End Set
    End Property

    Public Property VATNumber() As String
        Get
            Return _strVatNumber
        End Get
        Set(ByVal Value As String)
            _strVatNumber = Value
        End Set
    End Property

    Public Property ContactName() As String
        Get
            Return _strContactName
        End Get
        Set(ByVal Value As String)
            _strContactName = Value
        End Set
    End Property

    Public Property ValueWork() As String
        Get
            Return _strValueWork
        End Get
        Set(ByVal Value As String)
            _strValueWork = Value
        End Set
    End Property

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

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

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

End Class