'##############################################################################

' Author: Kevin Koch

' Description:  This class represents the data tier middle layer class for the

'               CUSTOMER table. It contains all data access methods and static

'               constants representing the stored procedures, field names

'               and Sql parameters required by this entity

'##############################################################################

Public Class CustomerDb

    Private Const CLASS_NAME As String = "CustomerDb"

    '#########################################################################################################################

    ' DB Configuration Properties

    '#########################################################################################################################

    Friend Shared WSB2B_CREATE_CUSTOMER As String = "WSB2B_CREATE_CUSTOMER"

    Friend Shared WSB2B_GET_CUSTOMER As String = "WSB2B_GET_CUSTOMER"

    Friend Shared WSB2B_GET_CUSTOMER_BY_ID As String = "WSB2B_GET_CUSTOMER_BY_ID"

    Friend Shared PARAM_CUSTOMER_ID_NAME As String = "@paramCustomerId"

    Friend Shared PARAM_CUSTOMER_ID_TYPE As SqlDbType = SqlDbType.Int

    Friend Shared PARAM_CUSTOMER_ID_SIZE As Integer = 4

    Friend Shared PARAM_EMAIL_NAME As String = "@paramEmail"

    Friend Shared PARAM_EMAIL_TYPE As SqlDbType = SqlDbType.NVarChar

    Friend Shared PARAM_EMAIL_SIZE As Integer = 50

    Friend Shared PARAM_PASSWORD_NAME As String = "@paramPassword"

    Friend Shared PARAM_PASSWORD_TYPE As SqlDbType = SqlDbType.NVarChar

    Friend Shared PARAM_PASSWORD_SIZE As Integer = 12

    Friend Shared PARAM_FIRST_NAME_NAME As String = "@paramFirstName"

    Friend Shared PARAM_FIRST_NAME_TYPE As SqlDbType = SqlDbType.NVarChar

    Friend Shared PARAM_FIRST_NAME_SIZE As Integer = 25

    Friend Shared PARAM_LAST_NAME_NAME As String = "@paramLastName"

    Friend Shared PARAM_LAST_NAME_TYPE As SqlDbType = SqlDbType.NVarChar

    Friend Shared PARAM_LAST_NAME_SIZE As Integer = 25

    ' Table field names, used for dataset references

    Public Shared FIELD_CUSTOMER_ID As String = "CUSTOMER_ID"

    Public Shared FIELD_EMAIL As String = "EMAIL"

    Public Shared FIELD_PASSWORD As String = "PASSWORD"

    Public Shared FIELD_FIRST_NAME As String = "FIRST_NAME"

    Public Shared FIELD_LAST_NAME As String = "LAST_NAME"

    Friend Shared CUSTOMER_TABLE_NAME As String = "CUSTOMER"

    '#########################################################################################################################

    ' Creates a new customer

    '#########################################################################################################################

    Public Sub CreateCustomer(ByVal Email As String, ByVal Password As String, ByVal FirstName As String, ByVal LastName As String)

        Const METHOD_NAME As String = "CreateCustomer"

        Dim DbObj As DbAccess = New DbAccess()

        Try

            Dim params As New Collection()

            params.Add(DbObj.MakeParam(Me.PARAM_EMAIL_NAME, Me.PARAM_EMAIL_TYPE, Me.PARAM_EMAIL_SIZE, Email))

            params.Add(DbObj.MakeParam(Me.PARAM_PASSWORD_NAME, Me.PARAM_PASSWORD_TYPE, Me.PARAM_PASSWORD_SIZE, Password))

            params.Add(DbObj.MakeParam(Me.PARAM_FIRST_NAME_NAME, Me.PARAM_FIRST_NAME_TYPE, Me.PARAM_FIRST_NAME_SIZE, FirstName))

            params.Add(DbObj.MakeParam(Me.PARAM_LAST_NAME_NAME, Me.PARAM_LAST_NAME_TYPE, Me.PARAM_LAST_NAME_SIZE, LastName))

            DbObj.ExecuteProc(Me.WSB2B_CREATE_CUSTOMER, params)

        Catch ex As Exception

            Log.WriteLogEntry(ex, Me.CLASS_NAME, METHOD_NAME)

            Throw New DbTierException(ex.Message, ex)

        Finally

            DbObj.Close()

            DbObj = Nothing

        End Try

    End Sub

    '#########################################################################################################################

    ' Validates a customer's email and password. Return the Pk to their customer record

    '#########################################################################################################################

    Public Function CustomerExists(ByVal Email As String, ByVal Password As String) As DataSet

        Const METHOD_NAME As String = "CustomerExists"

        Dim DbObj As DbAccess = New DbAccess()

        Try

            Dim params As New Collection()

            params.Add(DbObj.MakeParam(Me.PARAM_EMAIL_NAME, Me.PARAM_EMAIL_TYPE, Me.PARAM_EMAIL_SIZE, Email))

            params.Add(DbObj.MakeParam(Me.PARAM_PASSWORD_NAME, Me.PARAM_PASSWORD_TYPE, Me.PARAM_PASSWORD_SIZE, Password))

            Dim CustDs As DataSet = DbObj.ExecuteDataset(Me.WSB2B_GET_CUSTOMER, params, Me.CUSTOMER_TABLE_NAME)

            If CustDs.Tables(0).Rows.Count > 0 Then

                Return CustDs

            Else

                Return Nothing

            End If

        Catch ex As Exception

            Log.WriteLogEntry(ex, Me.CLASS_NAME, METHOD_NAME)

            Throw New DbTierException(ex.Message, ex)

       Finally

            DbObj.Close()

            DbObj = Nothing

        End Try

    End Function

    '#########################################################################################################################

    ' Returns the customer by the specified Id

    '#########################################################################################################################

    Public Function GetCustomerById(ByVal CustomerId As Int32) As DataSet

        Const METHOD_NAME As String = "GetCustomerById"

        Dim DbObj As DbAccess = New DbAccess()

        Try

            Dim params As New Collection()

            params.Add(DbObj.MakeParam(Me.PARAM_CUSTOMER_ID_NAME, Me.PARAM_CUSTOMER_ID_TYPE, Me.PARAM_CUSTOMER_ID_SIZE, CustomerId))

            Return DbObj.ExecuteDataset(Me.WSB2B_GET_CUSTOMER_BY_ID, params, Me.CUSTOMER_TABLE_NAME)

        Catch ex As Exception

            Log.WriteLogEntry(ex, Me.CLASS_NAME, METHOD_NAME)

            Throw New DbTierException(ex.Message, ex)

       Finally

            DbObj.Close()

            DbObj = Nothing

        End Try

    End Function

End Class

'##############################################################################

' Author: Kevin Koch

' Description:  This class represents the data tier middle layer class for the

'               ORDER table. It contains all data access methods and static

'               constants representing the stored procedures, field names

'               and Sql parameters required by this entity

'##############################################################################

Public Class OrderDb

    Private Const CLASS_NAME As String = "OrderDb"

    '#########################################################################################################################

    ' DB Configuration Properties

    '#########################################################################################################################

    Friend Shared WSB2B_GET_ORDERS_BY_CUSTOMER As String = "WSB2B_GET_ORDERS_BY_CUSTOMER"

    Friend Shared WSB2B_CREATE_ORDER As String = "WSB2B_CREATE_ORDER"

    Friend Shared WSB2B_DELETE_ORDER As String = "WSB2B_DELETE_ORDER"

    Friend Shared WSB2B_CONFIRM_ORDER As String = "WSB2B_CONFIRM_ORDER"

    Friend Shared PARAM_ORDER_ID_NAME As String = "@paramOrderId"

    Friend Shared PARAM_ORDER_ID_TYPE As SqlDbType = SqlDbType.Int

    Friend Shared PARAM_ORDER_ID_SIZE As Integer = 4

    Friend Shared PARAM_ORDER_STATUS_NAME As String = "@paramOrderStatus"

    Friend Shared PARAM_ORDER_STATUS_TYPE As SqlDbType = SqlDbType.NVarChar

    Friend Shared PARAM_ORDER_STATUS_SIZE As Integer = 20

    ' Table field names, used for dataset references

    Public Shared FIELD_ORDER_ID As String = "ORDER_ID"

    Public Shared FIELD_ORDER_STATUS As String = "ORDER_STATUS"

    Public Shared FIELD_CREATION_DATE As String = "CREATION_DATE"

    Friend Shared ORDER_TABLE_NAME As String = "ORDER"

    '#########################################################################################################################

    ' Possible order statuses, defined as static constants

    '#########################################################################################################################

    Public Shared ORDER_STATUS_PENDING As String = "Pending"

    Public Shared ORDER_STATUS_COMPLETE As String = "Complete"

    '#########################################################################################################################

    ' Searches the orders table for a particular customers orders

    '#########################################################################################################################

    Public Function GetOrdersByCustomer(ByVal CustomerId As Int32, ByVal OrderStatus As String) As DataSet

        Const METHOD_NAME As String = "GetOrdersByCustomer"

        Dim DbObj As DbAccess = New DbAccess()

        Try

            Dim params As Collection = New Collection()

            params.Add(DbObj.MakeParam(CustomerDb.PARAM_CUSTOMER_ID_NAME, CustomerDb.PARAM_CUSTOMER_ID_TYPE, CustomerDb.PARAM_CUSTOMER_ID_SIZE, CustomerId))

            params.Add(DbObj.MakeParam(Me.PARAM_ORDER_STATUS_NAME, Me.PARAM_ORDER_STATUS_TYPE, Me.PARAM_ORDER_STATUS_SIZE, OrderStatus))

            Return DbObj.ExecuteDataset(Me.WSB2B_GET_ORDERS_BY_CUSTOMER, params, Me.ORDER_TABLE_NAME)

        Catch ex As Exception

            Log.WriteLogEntry(ex, Me.CLASS_NAME, METHOD_NAME)

            Throw New DbTierException(ex.Message, ex)

        Finally

            DbObj.Close()

            DbObj = Nothing

        End Try

    End Function

    '#########################################################################################################################

    ' Creates a new pending order

    '#########################################################################################################################

    Public Function CreateOrder(ByVal CustomerId As Int32) As Int32

        Const METHOD_NAME As String = "CreateOrder"

        Dim DbObj As DbAccess = New DbAccess()

        Try

            Dim params As Collection = New Collection()

            params.Add(DbObj.MakeParam(Me.PARAM_ORDER_STATUS_NAME, Me.PARAM_ORDER_STATUS_TYPE, Me.PARAM_ORDER_STATUS_SIZE, Me.ORDER_STATUS_PENDING))

            params.Add(DbObj.MakeParam(CustomerDb.PARAM_CUSTOMER_ID_NAME, CustomerDb.PARAM_CUSTOMER_ID_TYPE, CustomerDb.PARAM_CUSTOMER_ID_SIZE, CustomerId))

            ' Create the main order entry

           Dim OrderDs As DataSet = DbObj.ExecuteDataset(Me.WSB2B_CREATE_ORDER, params, Me.ORDER_TABLE_NAME)

            Dim OrderId As Int32 = Convert.ToInt32(OrderDs.Tables(0).Rows(0).Item(Me.FIELD_ORDER_ID))

            If Not Utils.IsValidPk(OrderId) Then

                Throw New Exception("Invalid OrderId returned from stored procedure")

            End If

            Return OrderId

        Catch ex As Exception

            Log.WriteLogEntry(ex, Me.CLASS_NAME, METHOD_NAME)

            Throw New DbTierException(ex.Message, ex)

        Finally

            DbObj.Close()

            DbObj = Nothing

        End Try

    End Function

    '#########################################################################################################################

    ' Deletes the OrderId specified

    '#########################################################################################################################

    Public Sub DeleteOrder(ByVal OrderId As Int32)

        Const METHOD_NAME As String = "DeleteOrder"

        Dim DbObj As DbAccess = New DbAccess()

        Try

            Dim params As Collection = New Collection()

            params.Add(DbObj.MakeParam(Me.PARAM_ORDER_ID_NAME, Me.PARAM_ORDER_ID_TYPE, Me.PARAM_ORDER_ID_SIZE, OrderId))

           DbObj.ExecuteProc(Me.WSB2B_DELETE_ORDER, params)

        Catch ex As Exception

            Log.WriteLogEntry(ex, Me.CLASS_NAME, METHOD_NAME)

            Throw New DbTierException(ex.Message, ex)

        Finally

            DbObj.Close()

           DbObj = Nothing

        End Try

    End Sub

    '#########################################################################################################################

    ' Confirms a pending order by updating its status to Complete

    '#########################################################################################################################

    Public Function ConfirmOrder(ByVal OrderId As Int32) As DataSet

        Const METHOD_NAME As String = "ConfirmOrder"

        Dim DbObj As DbAccess = New DbAccess()

        Try

            Dim params As Collection = New Collection()

            params.Add(DbObj.MakeParam(Me.PARAM_ORDER_ID_NAME, Me.PARAM_ORDER_ID_TYPE, Me.PARAM_ORDER_ID_SIZE, OrderId))

            params.Add(DbObj.MakeParam(Me.PARAM_ORDER_STATUS_NAME, Me.PARAM_ORDER_STATUS_TYPE, Me.PARAM_ORDER_STATUS_SIZE, Me.ORDER_STATUS_COMPLETE))

            Return DbObj.ExecuteDataset(Me.WSB2B_CONFIRM_ORDER, params, Me.ORDER_TABLE_NAME)

        Catch ex As Exception

            Log.WriteLogEntry(ex, Me.CLASS_NAME, METHOD_NAME)

            Throw New DbTierException(ex.Message, ex)

        Finally

            DbObj.Close()

            DbObj = Nothing

        End Try

    End Function

End Class