Imports System.Data.SqlClient

Namespace Bid


    Public Class ItemDetails
        Public ItemNo As Int32
        Public ItemName As String
        Public ItemDesc As String
        Public ItemAsk As Double
        Public ItemNotify As Double
        Public ItemSellerID As Int32
        Public ItemListingDate As DateTime
        Public ItemExpDate As DateTime
        Public ItemStatus As Char
    End Class

    Public Class Item

        Public Function AddItem(ByVal ItemName As String, _
                                    ByVal ItemDesc As String, _
                                    ByVal ItemAsk As Double, _
                                    ByVal ItemNotify As Double, _
                                    ByVal ItemSellerID As Int32, _
                                    ByVal ItemExpDate As DateTime) As String

            ' Create Instance of Connection and Command Object
            Dim myConnection As New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
            Dim myCommand As New SqlCommand("sp_item_isp", myConnection)

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

            ' Add Parameters to SPROC
            Dim prmItemName As New SqlParameter("@name", SqlDbType.VarChar, 500)
            prmItemName.Value = ItemName
            myCommand.Parameters.Add(prmItemName)

            Dim prmItemDesc As New SqlParameter("@desc", SqlDbType.VarChar, 1000)
            prmItemDesc.Value = ItemDesc
            myCommand.Parameters.Add(prmItemDesc)

            Dim prmItemAsk As New SqlParameter("@ask", SqlDbType.Money)
            prmItemAsk.Value = ItemAsk
            myCommand.Parameters.Add(prmItemAsk)

            Dim prmItemNotify As New SqlParameter("@notify", SqlDbType.Money)
            prmItemNotify.Value = ItemNotify
            myCommand.Parameters.Add(prmItemNotify)

            Dim prmPersonID As New SqlParameter("@personid", SqlDbType.BigInt)
            prmPersonID.Value = ItemSellerID
            myCommand.Parameters.Add(prmPersonID)

            Dim prmItemExpDate As New SqlParameter("@exp", SqlDbType.DateTime)
            prmItemExpDate.Value = ItemExpDate
            myCommand.Parameters.Add(prmItemExpDate)

            Try
                myConnection.Open()
                myCommand.ExecuteNonQuery()
                myConnection.Close()

                Return "1"
            Catch SQLexc As SqlException
                Return SQLexc.ToString()
            End Try

        End Function

        Public Function ViewItems(ByVal intSellerID As Int32) As SqlDataReader
            ' Create Instance of Connection and Command Object
            Dim myConnection As New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
            Dim myCommand As New SqlCommand("sp_item_sel", myConnection)

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

            Dim prmSellerID As New SqlParameter("@sellerid", SqlDbType.BigInt)
            prmSellerID.Value = intSellerID
            myCommand.Parameters.Add(prmSellerID)

            myConnection.Open()
            Dim Result As SqlDataReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection)

            Return Result
        End Function

        Public Function UpdateItem(ByVal strItemID As String, _
                                   ByVal strItemName As String, _
                                   ByVal strItemDesc As String, _
                                   ByVal strAskPrice As String, _
                                   ByVal strNotifyPrice As String) As String

            ' Create Instance of Connection and Command Object
            Dim myConnection As New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
            Dim myCommand As New SqlCommand("sp_item_usp", myConnection)

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

            ' Add Parameters to SPROC
            Dim prmItemID As New SqlParameter("@itemid", SqlDbType.BigInt)
            prmItemID.Value = CInt(strItemID)
            myCommand.Parameters.Add(prmItemID)

            Dim prmItemName As New SqlParameter("@itemname", SqlDbType.VarChar, 500)
            prmItemName.Value = strItemName
            myCommand.Parameters.Add(prmItemName)

            Dim prmItemDesc As New SqlParameter("@desc", SqlDbType.VarChar, 1000)
            prmItemDesc.Value = strItemDesc
            myCommand.Parameters.Add(prmItemDesc)

            Dim prmAsk As New SqlParameter("@ask", SqlDbType.Money)
            prmAsk.Value = CDbl(strAskPrice)
            myCommand.Parameters.Add(prmAsk)

            Dim prmNotify As New SqlParameter("@notify", SqlDbType.Money)
            prmNotify.Value = CDbl(strNotifyPrice)
            myCommand.Parameters.Add(prmNotify)

            Try
                myConnection.Open()
                myCommand.ExecuteNonQuery()
                myConnection.Close()

                Return "1"
            Catch SQLexc As SqlException
                Return SQLexc.ToString()
            End Try

        End Function

        Public Function ViewItemsForSale() As SqlDataReader
            ' Create Instance of Connection and Command Object
            Dim myConnection As New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
            Dim myCommand As New SqlCommand("sp_items_for_sale", myConnection)

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

            myConnection.Open()
            Dim Result As SqlDataReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection)

            Return Result
        End Function

        Public Function GetBidDetails(ByVal intItemID As Int32) As SqlDataReader
            ' Create Instance of Connection and Command Object
            Dim myConnection As New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
            Dim myCommand As New SqlCommand("sp_Get_Bid_Details", myConnection)

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

            Dim prmItemID As New SqlParameter("@itemid", SqlDbType.BigInt)
            prmItemID.Value = intItemID
            myCommand.Parameters.Add(prmItemID)

            myConnection.Open()
            Dim Result As SqlDataReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection)

            Return Result
        End Function

        Public Function GetHighestBid(ByVal intItemID As Int32) As String
            ' Create Instance of Connection and Command Object
            Dim myConnection As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
            Dim myCommand As SqlCommand = New SqlCommand("sp_get_highest_bid", myConnection)

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

            ' Add Parameters to SPROC
            Dim prmItemID As SqlParameter = New SqlParameter("@itemid", SqlDbType.BigInt)
            prmItemID.Value = CStr(intItemID)
            myCommand.Parameters.Add(prmItemID)

            Dim prmHighBid As SqlParameter = New SqlParameter("@highbid", SqlDbType.Money)
            prmHighBid.Direction = ParameterDirection.Output
            myCommand.Parameters.Add(prmHighBid)

            myConnection.Open()
            myCommand.ExecuteNonQuery()
            myConnection.Close()

            If IsDBNull(prmHighBid.Value) Then
                Return "0"
            Else
                Return prmHighBid.Value
            End If

        End Function

        Public Function AddBid(ByVal ItemID As Int32, _
                                ByVal BidderID As Int32, _
                                ByVal BidAmount As Double) As String
            ' Create Instance of Connection and Command Object
            Dim myConnection As New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
            Dim myCommand As New SqlCommand("sp_bid_isp", myConnection)

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

            ' Add Parameters to SPROC
            Dim prmItemID As New SqlParameter("@itemid", SqlDbType.BigInt)
            prmItemID.Value = ItemID
            myCommand.Parameters.Add(prmItemID)

            Dim prmBidderID As New SqlParameter("@bidderid", SqlDbType.BigInt)
            prmBidderID.Value = BidderID
            myCommand.Parameters.Add(prmBidderID)

            Dim prmBidAmount As New SqlParameter("@bidamount", SqlDbType.Money)
            prmBidAmount.Value = BidAmount
            myCommand.Parameters.Add(prmBidAmount)

            Dim prmStatus As SqlParameter = New SqlParameter("@status", SqlDbType.Char, 1)
            prmStatus.Direction = ParameterDirection.Output
            myCommand.Parameters.Add(prmStatus)

            myConnection.Open()
            myCommand.ExecuteNonQuery()
            myConnection.Close()

            Return prmStatus.Value
        End Function

        Public Function AddSale(ByVal ItemID As Int32, _
                                ByVal BidID As Int32) As String
            ' Create Instance of Connection and Command Object
            Dim myConnection As New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
            Dim myCommand As New SqlCommand("sp_sale_isp", myConnection)

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

            ' Add Parameters to SPROC
            Dim prmItemID As New SqlParameter("@itemid", SqlDbType.BigInt)
            prmItemID.Value = ItemID
            myCommand.Parameters.Add(prmItemID)

            Dim prmBidID As New SqlParameter("@bidid", SqlDbType.BigInt)
            prmBidID.Value = BidID
            myCommand.Parameters.Add(prmBidID)

            Try
                myConnection.Open()
                myCommand.ExecuteNonQuery()
                myConnection.Close()

                Return "1"
            Catch SQLexc As SqlException
                Return SQLexc.ToString()
            End Try
        End Function

        Public Function GetMyWinningBids(ByVal intPersonID As Int32) As SqlDataReader
            ' Create Instance of Connection and Command Object
            Dim myConnection As New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
            Dim myCommand As New SqlCommand("sp_my_winning_bids", myConnection)

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

            Dim prmPersonID As New SqlParameter("@personid", SqlDbType.BigInt)
            prmPersonID.Value = intPersonID
            myCommand.Parameters.Add(prmPersonID)

            myConnection.Open()
            Dim Result As SqlDataReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection)

            Return Result
        End Function

        Public Function CompleteSale(ByVal ItemID As Int32, ByVal WinningBid As Double) As String
            ' Create Instance of Connection and Command Object
            Dim myConnection As New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
            Dim myCommand As New SqlCommand("sp_sale_complete", myConnection)

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

            ' Add Parameters to SPROC
            Dim prmItemID As New SqlParameter("@itemid", SqlDbType.BigInt)
            prmItemID.Value = ItemID
            myCommand.Parameters.Add(prmItemID)

            Dim prmWinningBidAmount As New SqlParameter("@bidamount", SqlDbType.Money)
            prmWinningBidAmount.Value = WinningBid
            myCommand.Parameters.Add(prmWinningBidAmount)

            Try
                myConnection.Open()
                myCommand.ExecuteNonQuery()
                myConnection.Close()

                Return "1"
            Catch SQLexc As SqlException
                Return SQLexc.ToString()
            End Try
        End Function

        Public Function DeleteItem(ByVal ItemID As Int32) As String

            ' Create Instance of Connection and Command Object
            Dim myConnection As New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
            Dim myCommand As New SqlCommand("sp_item_dsp", myConnection)

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

            ' Add Parameters to SPROC
            Dim prmItemID As New SqlParameter("@itemid", SqlDbType.BigInt)
            prmItemID.Value = ItemID
            myCommand.Parameters.Add(prmItemID)

            Try
                myConnection.Open()
                myCommand.ExecuteNonQuery()
                myConnection.Close()

                Return "1"
            Catch SQLexc As SqlException
                Return SQLexc.ToString()
            End Try

        End Function

    End Class

End Namespace