Imports System
Imports System.Data
Imports System.Data.SQL

Namespace Conference

    '*******************************************************
    '
    ' ProductDetails Class
    '
    ' A simple data Class that encapsulates details about
    ' a particular product inside the Conference Product 
    ' database.
    '
    '*******************************************************

    Public Class ProductDetails 
    
        Public ModelNumber As String
        Public ModelName As String
        Public ProductImage As String
        Public UnitCost As Double
        Public Description As String
        
    End Class

    '*******************************************************
    '
    ' ProductsDB Class
    '
    ' Business/Data Logic Class that encapsulates all data
    ' logic necessary to query products within
    ' the Conference Products database.
    ' 
    '*******************************************************

    Public Class ProductsDB 

        '*******************************************************
        '
        ' ProductsDB.GetProductCategories() Method
        '
        ' The GetProductCategories method returns a bindable
        ' DataSet containing all product categories (and their
        ' ProductIDs) within the Conference Products database.
        '
        ' Other relevant sources:  
        '     ProductCategoryList Stored Procedure
        '
        '*******************************************************

        Public Function GetProductCategories() As DataSet

            ' Create Instance of Connection and Command Object
            Dim myConnection As SQLConnection = new SQLConnection(ConferenceDB.ConnectionString)
            Dim myCommand As SQLDataSetCommand = new SQLDataSetCommand("ProductCategoryList", myConnection)

            ' Mark the Command as a SPROC
            myCommand.SelectCommand.CommandType = CommandType.StoredProcedure

            ' Create and Fill the DataSet
            Dim myDataSet As DataSet = new DataSet()
            myCommand.FillDataSet(myDataSet, "CategoryList")

            ' Return the DataSet
            return myDataSet
        
        End Function
       
        '*******************************************************
        '
        ' ProductsDB.GetProducts() Method
        '
        ' The GetProducts method returns a bindable
        ' DataSet containing all products within a specified
        ' product category.
        '
        ' Other relevant sources:  
        '     ProductsByCategory Stored Procedure
        '
        '*******************************************************       
       

        ' TO DO: add a GetProducts function



        '*******************************************************
        '
        ' ProductsDB.GetSessions() Method
        '
        ' The GetSessions method returns a bindable
        ' DataSet containing all sessions within a specified
        ' product id.
        '
        ' Other relevant sources:  
        '     ProductsByCategory Stored Procedure
        '
        '*******************************************************       
       
        Public Function GetSessions(productID As Integer) As DataSet

            ' Create Instance of Connection and Command Object
            Dim myConnection As SQLConnection = new SQLConnection(ConferenceDB.ConnectionString)
            Dim myCommand As SQLDataSetCommand = new SQLDataSetCommand("SessionsByProduct", myConnection)

            ' Mark the Command as a SPROC
            myCommand.SelectCommand.CommandType = CommandType.StoredProcedure

            ' Add Parameters to SPROC
            Dim parameterProductID As SQLParameter= new SQLParameter("@ProductID", SQLDataType.Int, 4)
            parameterProductID.Value = productID
            myCommand.SelectCommand.Parameters.Add(parameterProductID)

            ' Create and Fill the DataSet
            Dim myDataSet As DataSet = new DataSet()
            myCommand.FillDataSet(myDataSet, "Sessions")

            ' Return the DataSet
            return myDataSet
            
        End Function


        '*******************************************************
        '
        ' ProductsDB.GetProductDetails() Method
        '
        ' The GetProductDetails method returns a ProductDetails
        ' struct containing specific details about a specified
        ' product within the Conference Products Database.
        '
        ' Other relevant sources:  
        '     ProductDetail Stored Procedure
        '        
        '*******************************************************    

        Public Function GetProductDetails(productID As Integer) As ProductDetails

            ' Create Instance of Connection and Command Object
            Dim myConnection As SQLConnection = new SQLConnection(ConferenceDB.ConnectionString)
            Dim myCommand As SQLCommand = new SQLCommand("ProductDetail", myConnection)

            ' Mark the Command as a SPROC
            myCommand.CommandType = CommandType.StoredProcedure

            ' Add Parameters to SPROC
            Dim parameterProductID As SQLParameter = new SQLParameter("@ProductID", SQLDataType.Int, 4)
            parameterProductID.Value = productID
            myCommand.Parameters.Add(parameterProductID)

            Dim parameterUnitCost As SQLParameter = new SQLParameter("@UnitCost", SQLDataType.Float, 8)
            parameterUnitCost.Direction = ParameterDirection.Output
            myCommand.Parameters.Add(parameterUnitCost)

            Dim parameterModelNumber As SQLParameter = new SQLParameter("@ModelNumber", SQLDataType.NChar, 50)
            parameterModelNumber.Direction = ParameterDirection.Output
            myCommand.Parameters.Add(parameterModelNumber)

            Dim parameterModelName As SQLParameter = new SQLParameter("@ModelName", SQLDataType.NChar, 50)
            parameterModelName.Direction = ParameterDirection.Output
            myCommand.Parameters.Add(parameterModelName)

            Dim parameterProductImage As SQLParameter = new SQLParameter("@ProductImage", SQLDataType.NChar, 50)
            parameterProductImage.Direction = ParameterDirection.Output
            myCommand.Parameters.Add(parameterProductImage)

            Dim parameterDescription As SQLParameter = new SQLParameter("@Description", SQLDataType.NChar, 8000)
            parameterDescription.Direction = ParameterDirection.Output
            myCommand.Parameters.Add(parameterDescription)

            Try 
                ' Open the connection and execute the Command
                myConnection.Open()
                myCommand.Execute()
            Catch e As Exception
                ' An error occurred, pass the exception up
                throw e
            Finally 
                ' Close the Connection
                If myConnection.State = DBObjectState.Open then
                    myConnection.Close()
                End If
            End Try
           
            ' Create and Populate ProductDetails Struct Imports 
            ' Output Params from the SPROC
            Dim myProductDetails As ProductDetails = new ProductDetails()

            myProductDetails.ModelNumber = CStr(parameterModelNumber.Value)
            myProductDetails.ModelName = CStr(parameterModelName.Value)
            myProductDetails.ProductImage = CStr(parameterProductImage.Value)
            myProductDetails.UnitCost = CDbl(parameterUnitCost.Value)
            myProductDetails.Description = CStr(parameterDescription.Value)      
            
            return myProductDetails
        
        End Function

        '*******************************************************
        '
        ' ProductsDB.GetProductsAlsoPurchased() Method
        '
        ' The GetProductsAlsoPurchased method returns a bindable
        ' DataSet containing a list of other products also
        ' purchased with a specified product.
        '
        ' Other relevant sources:  
        '     CustomerAlsoBought Stored Procedure
        '
        '******************************************************* 

        Public Function GetProductsAlsoPurchased(productID As Integer) As DataSet

            ' Create Instance of Connection and Command Object
            Dim myConnection As SQLConnection = new SQLConnection(ConferenceDB.ConnectionString)
            Dim myCommand As SQLDataSetCommand = new SQLDataSetCommand("CustomerAlsoBought", myConnection)

            ' Mark the Command as a SPROC
            myCommand.SelectCommand.CommandType = CommandType.StoredProcedure

            ' Add Parameters to SPROC
            Dim parameterProductID As SQLParameter = new SQLParameter("@ProductID", SQLDataType.Int, 4)
            parameterProductID.Value = productID
            myCommand.SelectCommand.Parameters.Add(parameterProductID)

            ' Create and Fill the DataSet
            Dim myDataSet As DataSet = new DataSet()
            myCommand.FillDataSet(myDataSet, "Products")

            ' Return the DataSet
            return myDataSet
        
        End Function

        '*******************************************************
        '
        ' ProductsDB.GetMostPopularProductsOfWeek() Method
        '
        ' The GetMostPopularProductsOfWeek method returns a bindable
        ' DataSet containing a list of the most popular products
        ' of the week within the Conference Product database.
        '
        ' Other relevant sources:  
        '     ProductsMostPopular Stored Procedure
        '
        '******************************************************* 

        Public Function GetMostPopularProductsOfWeek() As DataSet

            ' Create Instance of Connection and Command Object
            Dim myConnection As SQLConnection = new SQLConnection(ConferenceDB.ConnectionString)
            Dim myCommand As SQLDataSetCommand = new SQLDataSetCommand("ProductsMostPopular", myConnection)

            ' Mark the Command as a SPROC
            myCommand.SelectCommand.CommandType = CommandType.StoredProcedure

            ' Create and Fill the DataSet
            Dim myDataSet As DataSet = new DataSet()
            myCommand.FillDataSet(myDataSet, "CategoryList")

            ' Return the DataSet
            return myDataSet
        
        End Function
        
        '*******************************************************
        '
        ' ProductsDB.SearchProductDescriptions() Method
        '
        ' The SearchProductDescriptions method returns a bindable
        ' DataSet containing a list of all products whose name
        ' and/or description contains the specified search string.
        '
        ' Other relevant sources:  
        '     ProductSearch Stored Procedure
        '
        '*******************************************************         
        
        Public Function SearchProductDescriptions(searchString As String) As DataSet

            ' Create Instance of Connection and Command Object
            Dim myConnection As SQLConnection = new SQLConnection(ConferenceDB.ConnectionString)
            Dim myCommand As SQLDataSetCommand = new SQLDataSetCommand("ProductSearch", myConnection)

            ' Mark the Command as a SPROC
            myCommand.SelectCommand.CommandType = CommandType.StoredProcedure

            ' Add Parameters to SPROC
            Dim parameterSearchString As SQLParameter = new SQLParameter("@Search", SQLDataType.NChar, 255)
            parameterSearchString.Value = searchString
            myCommand.SelectCommand.Parameters.Add(parameterSearchString)

            ' Create and Fill the DataSet
            Dim myDataSet As DataSet = new DataSet()
            myCommand.FillDataSet(myDataSet, "Products")

            ' Return the DataSet
            return myDataSet
            
        End Function
        
    End Class

End Namespace