Imports EDSL.Entities
Imports Microsoft.Practices.EnterpriseLibrary.Common
Imports Microsoft.Practices.EnterpriseLibrary.Data
Imports System.Configuration
Imports System.Data.Common
Public Class dbMainProduct
    Private Shared strMainConnString As String = "MainConnString"
    Public Function GetAll(ByVal strWHEREclause As String, ByVal strORDERBYclause As String, Optional ByVal intLimit As Integer = 0) As List(Of entMainProduct)
        Dim objProduct As New List(Of entMainProduct)

        Dim db As Database = DatabaseFactory.CreateDatabase(strMainConnString)
        Try
            Using dbCommand As DbCommand = db.GetStoredProcCommand("MainAdminProductGetAll")
                db.AddInParameter(dbCommand, "WHEREclause", DbType.String, strWHEREclause)
                db.AddInParameter(dbCommand, "ORDERBYclause", DbType.String, strORDERBYclause)
                Dim oRecordset As IDataReader = db.ExecuteReader(dbCommand)
                While oRecordset.Read()
                    Dim entProduct As New entMainProduct
                    With entProduct

                        .ProductID = Convert.ToInt32(oRecordset("ProductID"))
                        .ProductName = Convert.ToString(oRecordset("ProductName"))
                        .ProductDesc1 = Convert.ToString(oRecordset("ProductDesc1"))
                        .ProductDesc2 = Convert.ToString(oRecordset("ProductDesc2"))
                        .ProductDesc3 = Convert.ToString(oRecordset("ProductDesc3"))
                        .MainImage = Convert.ToString(oRecordset("MainImage"))
                        .MediumImage = Convert.ToString(oRecordset("MediumImage"))
                        .ThumbImage = Convert.ToString(oRecordset("ThumbImage"))
                        .ProductPrice = Convert.ToDecimal(oRecordset("ProductPrice"))
                        .ProductPriceText = Convert.ToString(oRecordset("ProductPriceText"))
                        .ShowProductPriceOnWebsite = Convert.ToInt32(oRecordset("ShowPrice"))
                        .Active = Convert.ToString(oRecordset("Active"))
                        .Deleted = Convert.ToString(oRecordset("Deleted"))
                        .CreatedDate = FormatDateTime(Convert.ToString(oRecordset("CreatedDate")), DateFormat.ShortDate)


                    End With
                    objProduct.Add(entProduct)

                End While
                oRecordset.Close()
            End Using
        Catch ex As Exception
            Throw ex
        End Try
        Return objProduct
    End Function

    Public Function GetAllByCategoryID(ByVal categoryID As Integer) As List(Of entMainProduct)
        Dim objProduct As New List(Of entMainProduct)

        Dim db As Database = DatabaseFactory.CreateDatabase(strMainConnString)
        Try
            Using dbCommand As DbCommand = db.GetStoredProcCommand("MainAdminProductGetByCategory")
                db.AddInParameter(dbCommand, "CategoryID", DbType.String, categoryID)
                Dim oRecordset As IDataReader = db.ExecuteReader(dbCommand)
                While oRecordset.Read()
                    Dim entProduct As New entMainProduct
                    With entProduct
                        .ProductID = Convert.ToInt32(oRecordset("ProductID"))
                        .ProductName = Convert.ToString(oRecordset("ProductName"))
                        .ProductDesc1 = Convert.ToString(oRecordset("ProductDesc1"))
                        .ProductDesc2 = Convert.ToString(oRecordset("ProductDesc2"))
                        .ProductDesc3 = Convert.ToString(oRecordset("ProductDesc3"))
                        .MainImage = Convert.ToString(oRecordset("MainImage"))
                        .MediumImage = Convert.ToString(oRecordset("MediumImage"))
                        .ThumbImage = Convert.ToString(oRecordset("ThumbImage"))
                        .ProductPrice = Convert.ToDecimal(oRecordset("ProductPrice"))
                        .ProductPriceText = Convert.ToString(oRecordset("ProductPriceText"))
                        .ShowProductPriceOnWebsite = Convert.ToInt32(oRecordset("ShowPrice"))
                        .Active = Convert.ToString(oRecordset("Active"))
                        .Deleted = Convert.ToString(oRecordset("Deleted"))
                        .CreatedDate = FormatDateTime(Convert.ToString(oRecordset("CreatedDate")), DateFormat.ShortDate)

                    End With
                    objProduct.Add(entProduct)

                End While
                oRecordset.Close()
            End Using
        Catch ex As Exception
            Throw ex
        End Try
        Return objProduct
    End Function

    Public Function GetItem(ByVal intProductID As Integer) As entMainProduct
        Dim objProduct As New entMainProduct
        Dim db As Database = DatabaseFactory.CreateDatabase(strMainConnString)

        Try
            Using dbCommand As DbCommand = db.GetStoredProcCommand("MainAdminProductGet")
                db.AddInParameter(dbCommand, "ProductID", DbType.Int16, intProductID)
                Dim oRecordset As IDataReader = db.ExecuteReader(dbCommand)
                While oRecordset.Read()
                    With objProduct

                        .ProductID = Convert.ToInt32(oRecordset("ProductID"))
                        .ProductName = Convert.ToString(oRecordset("ProductName"))
                        .ProductDesc1 = Convert.ToString(oRecordset("ProductDesc1"))
                        .ProductDesc2 = Convert.ToString(oRecordset("ProductDesc2"))
                        .ProductDesc3 = Convert.ToString(oRecordset("ProductDesc3"))
                        .MainImage = Convert.ToString(oRecordset("MainImage"))
                        .MediumImage = Convert.ToString(oRecordset("MediumImage"))
                        .ThumbImage = Convert.ToString(oRecordset("ThumbImage"))
                        .ProductPrice = Convert.ToDecimal(oRecordset("ProductPrice"))
                        .ProductPriceText = Convert.ToString(oRecordset("ProductPriceText"))
                        .ShowProductPriceOnWebsite = Convert.ToInt32(oRecordset("ShowPrice"))
                        .Active = Convert.ToString(oRecordset("Active"))
                        .Deleted = Convert.ToString(oRecordset("Deleted"))
                        .CreatedDate = FormatDateTime(Convert.ToString(oRecordset("CreatedDate")), DateFormat.ShortDate)
                    End With
                End While
                oRecordset.Close()
            End Using
            Return objProduct
        Catch ex As Exception
            Throw ex
            Return Nothing
        End Try
    End Function
    Public Function DeleteItem(ByVal intProductID As Integer, ByVal intCategoryID As Integer, ByVal intUserID As Integer) As Boolean
        Dim db As Database = DatabaseFactory.CreateDatabase(strMainConnString)
        Try
            Using dbCommand As DbCommand = db.GetStoredProcCommand("MainAdminProductDelete")
                db.AddInParameter(dbCommand, "ProductID", DbType.Int16, intProductID)
                db.AddInParameter(dbCommand, "CategoryID", DbType.Int16, intCategoryID)
                db.AddInParameter(dbCommand, "UserID", DbType.Int16, intUserID)
                db.ExecuteNonQuery(dbCommand)
            End Using
            Return True
        Catch ex As Exception
            Return False
        End Try
    End Function

    Public Function SaveItemPage1(ByVal intProductID As Integer, ByVal strProductName As String, ByVal productPrice As Decimal, ByVal strProductPriceText As String, ByVal showPrice As Integer, ByVal intActive As Integer, ByVal intUserID As Integer, ByVal strProductSummary As String, ByVal strProductDetail As String) As Integer

        Dim db As Database = DatabaseFactory.CreateDatabase(strMainConnString)
        Dim strSPName As String = ""
        Try
            If intProductID = 0 Then
                strSPName = "MainAdminProductINSERT1"
            Else
                strSPName = "MainAdminProductUPDATE1"
            End If
            Using dbCommand As DbCommand = db.GetStoredProcCommand(strSPName)
                If intProductID <> 0 Then
                    db.AddInParameter(dbCommand, "ProductID", DbType.Int16, intProductID)
                End If
                db.AddInParameter(dbCommand, "ProductName", DbType.String, strProductName)
                Dim productValue As Integer
                productValue = Convert.ToDecimal(productPrice)
                db.AddInParameter(dbCommand, "ProductPrice", DbType.Decimal, productValue)
                db.AddInParameter(dbCommand, "ProductPriceText", DbType.String, strProductPriceText)
                db.AddInParameter(dbCommand, "ShowPrice", DbType.Int32, showPrice)
                db.AddInParameter(dbCommand, "Active", DbType.Int16, intActive)
                db.AddInParameter(dbCommand, "UserID", DbType.Int16, intUserID)
                If intProductID = 0 Then
                    db.AddOutParameter(dbCommand, "NewID", DbType.Int16, 255)
                End If
                db.ExecuteNonQuery(dbCommand)

                If intProductID = 0 Then
                    Dim strNewID As String = db.GetParameterValue(dbCommand, "NewID")
                    SaveItemPage2(strNewID, strProductSummary, strProductDetail, "", intUserID)
                    'UpdateProductLocations(Convert.ToInt32(strNewID), locationList)
                    Return strNewID  '@@IDENTITY

                Else
                    'UpdateProductLocations(Convert.ToInt32(intProductID), locationList)
                    SaveItemPage2(Convert.ToInt32(intProductID), strProductSummary, strProductDetail, "", intUserID)
                    Return intProductID
                End If
            End Using
        Catch ex As Exception
            Return 0
        End Try
    End Function
  

    Public Function SaveItemPage2(ByVal intProductID As Integer, ByVal strProductDesc1 As String, ByVal strProductDesc2 As String, ByVal strProductDesc3 As String, ByVal intUserID As Integer) As Integer
        Dim db As Database = DatabaseFactory.CreateDatabase(strMainConnString)
        Dim strSPName As String = ""
        Try
            Using dbCommand As DbCommand = db.GetStoredProcCommand("MainAdminProductUPDATE2")
                db.AddInParameter(dbCommand, "ProductID", DbType.Int16, intProductID)
                db.AddInParameter(dbCommand, "ProductDesc1", DbType.String, strProductDesc1)
                db.AddInParameter(dbCommand, "ProductDesc2", DbType.String, strProductDesc2)
                db.AddInParameter(dbCommand, "ProductDesc3", DbType.String, strProductDesc3)
                db.AddInParameter(dbCommand, "UserID", DbType.Int16, intUserID)
                db.ExecuteNonQuery(dbCommand)
            End Using
            Return 1
        Catch ex As Exception
            Return 0
        End Try
    End Function
    Public Function SaveItemPage3(ByVal intProductID As Integer, ByVal strMainImage As String, ByVal strMediumImage As String, ByVal strThumbnail As String, ByVal intUserID As Integer) As Integer
        Dim db As Database = DatabaseFactory.CreateDatabase(strMainConnString)
        Dim strSPName As String = ""
        Try
            Using dbCommand As DbCommand = db.GetStoredProcCommand("MainAdminProductUPDATE3")
                db.AddInParameter(dbCommand, "ProductID", DbType.Int16, intProductID)
                db.AddInParameter(dbCommand, "MainImage", DbType.String, strMainImage)
                db.AddInParameter(dbCommand, "MediumImage", DbType.String, strMediumImage)
                db.AddInParameter(dbCommand, "ThumbImage", DbType.String, strThumbnail)
                db.AddInParameter(dbCommand, "UserID", DbType.Int16, intUserID)
                db.ExecuteNonQuery(dbCommand)
            End Using
            Return 1
        Catch ex As Exception
            Return 0
        End Try
    End Function

    Public Function GetItemShowOnWebsite(ByVal intProductID As Integer, ByVal intCategoryID As Integer) As entMainProduct
        Dim objProduct As New entMainProduct
        Dim db As Database = DatabaseFactory.CreateDatabase(strMainConnString)

        Try
            Using dbCommand As DbCommand = db.GetStoredProcCommand("MainProductGet")
                db.AddInParameter(dbCommand, "ProductID", DbType.Int16, intProductID)
                db.AddInParameter(dbCommand, "CategoryID", DbType.Int16, intCategoryID)
                Dim oRecordset As IDataReader = db.ExecuteReader(dbCommand)
                While oRecordset.Read()
                    With objProduct
                        .ProductID = Convert.ToInt32(oRecordset("ProductID"))
                        .ProductName = Convert.ToString(oRecordset("ProductName"))
                        .ProductDesc1 = Convert.ToString(oRecordset("ProductDesc1"))
                        .ProductDesc2 = Convert.ToString(oRecordset("ProductDesc2"))
                        .ProductDesc3 = Convert.ToString(oRecordset("ProductDesc3"))
                        .MainImage = Convert.ToString(oRecordset("MainImage"))
                        .MediumImage = Convert.ToString(oRecordset("MediumImage"))
                        .ThumbImage = Convert.ToString(oRecordset("ThumbImage"))
                        .Active = Convert.ToString(oRecordset("Active"))
                        .Deleted = Convert.ToString(oRecordset("Deleted"))
                        .CreatedDate = FormatDateTime(Convert.ToString(oRecordset("CreatedDate")), DateFormat.ShortDate)
                        .DropDownValue = Convert.ToString(oRecordset("DropDownValue"))
                        .CategoryTrail = Convert.ToString(oRecordset("ParentID")) & "," & Convert.ToString(oRecordset("ParentCategoryName"))
                    End With
                End While
                oRecordset.Close()
            End Using
            Return objProduct
        Catch ex As Exception
            Throw ex
            Return Nothing
        End Try
    End Function

    Public Function GetProductLocations(ByVal productID As Integer) As ArrayList

        Dim listOfLocations As New ArrayList
        Dim db As Database = DatabaseFactory.CreateDatabase(strMainConnString)

        Try
            Using dbCommand As DbCommand = db.GetStoredProcCommand("MainAdminGetProductLocationList")
                db.AddInParameter(dbCommand, "ProductID", DbType.Int16, productID)
                Dim oRecordset As IDataReader = db.ExecuteReader(dbCommand)
                While oRecordset.Read()
                    listOfLocations.Add(Convert.ToString(oRecordset("LocationID")))
                End While

                oRecordset.Close()
            End Using
            Return listOfLocations
        Catch ex As Exception
            Throw ex
            Return Nothing
        End Try

    End Function

    Private Function UpdateProductLocations(ByVal intProductID As Integer, ByVal locationList As ArrayList) As Boolean
        Dim db As Database = DatabaseFactory.CreateDatabase(strMainConnString)
        ' Delete All Locations First
        Try
            Using dbCommand As DbCommand = db.GetStoredProcCommand("MainProduct_LocationsDELETE")
                db.AddInParameter(dbCommand, "ProductID", DbType.Int32, intProductID)
                db.ExecuteNonQuery(dbCommand)
            End Using

            For Each item In locationList
                Try
                    Using dbCommand As DbCommand = db.GetStoredProcCommand("MainProduct_LocationsINSERT")
                        db.AddInParameter(dbCommand, "ProductID", DbType.Int32, intProductID)
                        db.AddInParameter(dbCommand, "LocationID", DbType.Int32, Convert.ToInt32(item.ToString()))
                        db.ExecuteNonQuery(dbCommand)
                    End Using

                Catch ex As Exception

                End Try
            Next
        Catch ex As Exception
            Return False
        End Try
    End Function
    Public Function DeleteAllRelatedNews(ByVal intID As Integer) As Boolean
        Dim db As Database = DatabaseFactory.CreateDatabase(strMainConnString)
        Try
            Using dbCommand As DbCommand = db.GetStoredProcCommand("MainAdminNews_ProductDelete")
                db.AddInParameter(dbCommand, "NewsID", DbType.Int16, intID)
                db.ExecuteNonQuery(dbCommand)
            End Using
            Return True

        Catch ex As Exception
            Return False
        End Try
    End Function
    Public Function DeleteAllRelatedCaseStudies(ByVal intID As Integer) As Boolean
        Dim db As Database = DatabaseFactory.CreateDatabase(strMainConnString)
        Try
            Using dbCommand As DbCommand = db.GetStoredProcCommand("MainAdminCaseStudy_ProductDelete")
                db.AddInParameter(dbCommand, "CaseStudyID", DbType.Int16, intID)
                db.ExecuteNonQuery(dbCommand)
            End Using
            Return True

        Catch ex As Exception
            Return False
        End Try
    End Function

    Public Function DeleteAllRelatedTestimonials(ByVal intID As Integer) As Boolean
        Dim db As Database = DatabaseFactory.CreateDatabase(strMainConnString)
        Try
            Using dbCommand As DbCommand = db.GetStoredProcCommand("MainAdminCaseStudy_ProductDelete")
                db.AddInParameter(dbCommand, "CaseStudyID", DbType.Int16, intID)
                db.ExecuteNonQuery(dbCommand)
            End Using
            Return True

        Catch ex As Exception
            Return False
        End Try
    End Function

    Public Function InsertAllRelatedNews(ByVal newsID As Integer, ByVal categoryID As Integer, ByVal productID As Integer) As Boolean
        Dim db As Database = DatabaseFactory.CreateDatabase(strMainConnString)
        Try
            Using dbCommand As DbCommand = db.GetStoredProcCommand("MainAdminNews_ProductINSERT")
                db.AddInParameter(dbCommand, "NewsID", DbType.Int16, newsID)
                db.AddInParameter(dbCommand, "ProductID", DbType.Int16, productID)
                db.AddInParameter(dbCommand, "CategoryID", DbType.Int16, categoryID)

                db.ExecuteNonQuery(dbCommand)
            End Using
            Return True

        Catch ex As Exception
            Return False
        End Try
    End Function
    Public Function InsertAllRelatedCaseStudies(ByVal caseStudyID As Integer, ByVal categoryID As Integer, ByVal productID As Integer) As Boolean
        Dim db As Database = DatabaseFactory.CreateDatabase(strMainConnString)
        Try
            Using dbCommand As DbCommand = db.GetStoredProcCommand("MainAdminCaseStudy_ProductINSERT")
                db.AddInParameter(dbCommand, "CaseStudyID", DbType.Int16, caseStudyID)
                db.AddInParameter(dbCommand, "ProductID", DbType.Int16, productID)
                db.AddInParameter(dbCommand, "CategoryID", DbType.Int16, categoryID)

                db.ExecuteNonQuery(dbCommand)
            End Using
            Return True

        Catch ex As Exception
            Return False
        End Try
    End Function

    Public Function InsertAllRelatedTestimonials(ByVal caseStudyID As Integer, ByVal categoryID As Integer, ByVal productID As Integer) As Boolean
        Dim db As Database = DatabaseFactory.CreateDatabase(strMainConnString)
        Try
            Using dbCommand As DbCommand = db.GetStoredProcCommand("MainAdminCaseStudy_ProductINSERT")
                db.AddInParameter(dbCommand, "CaseStudyID", DbType.Int16, caseStudyID)
                db.AddInParameter(dbCommand, "ProductID", DbType.Int16, productID)
                db.AddInParameter(dbCommand, "CategoryID", DbType.Int16, categoryID)

                db.ExecuteNonQuery(dbCommand)
            End Using
            Return True

        Catch ex As Exception
            Return False
        End Try
    End Function
    ''' <summary>
    ''' Get Selected Products with Related News for Checkbox admin
    ''' </summary>
    ''' <param name="newsID"></param>
    ''' <param name="CategoryID"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetRelatedNews(ByVal newsID As Integer, ByVal categoryID As Integer) As List(Of entMainProduct)
        Dim objProducts As New List(Of entMainProduct)
        Dim db As Database = DatabaseFactory.CreateDatabase(strMainConnString)
        Try
            Using dbCommand As DbCommand = db.GetStoredProcCommand("MainAdminNews_ProductGetSelected")
                db.AddInParameter(dbCommand, "NewsID", DbType.Int16, newsID)
                db.AddInParameter(dbCommand, "CategoryID", DbType.Int16, categoryID)

                Dim oRecordset As IDataReader = db.ExecuteReader(dbCommand)
                While oRecordset.Read()
                    Dim objProduct As New entMainProduct
                    With objProduct
                        .ProductID = Convert.ToInt32(oRecordset("ProductID"))
                    End With
                    objProducts.Add(objProduct)
                End While
                oRecordset.Close()
            End Using
            Return objProducts
        Catch ex As Exception
            Throw ex
            Return Nothing
        End Try
    End Function
    ''' <summary>
    ''' Get Selected Products with Related News for Checkbox admin
    ''' </summary>
    ''' <param name="caseStudyID"></param>
    ''' <param name="CategoryID"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetRelatedCaseStudy(ByVal caseStudyID As Integer, ByVal categoryID As Integer) As List(Of entMainProduct)
        Dim objProducts As New List(Of entMainProduct)
        Dim db As Database = DatabaseFactory.CreateDatabase(strMainConnString)
        Try
            Using dbCommand As DbCommand = db.GetStoredProcCommand("MainAdminCaseStudy_ProductGetSelected")
                db.AddInParameter(dbCommand, "CaseStudyID", DbType.Int16, caseStudyID)
                db.AddInParameter(dbCommand, "CategoryID", DbType.Int16, categoryID)

                Dim oRecordset As IDataReader = db.ExecuteReader(dbCommand)
                While oRecordset.Read()
                    Dim objProduct As New entMainProduct
                    With objProduct
                        .ProductID = Convert.ToInt32(oRecordset("ProductID"))
                    End With
                    objProducts.Add(objProduct)
                End While
                oRecordset.Close()
            End Using
            Return objProducts
        Catch ex As Exception
            Throw ex
            Return Nothing
        End Try
    End Function


    Public Function GetRelatedTestimonials(ByVal caseStudyID As Integer, ByVal categoryID As Integer) As List(Of entMainProduct)
        Dim objProducts As New List(Of entMainProduct)
        Dim db As Database = DatabaseFactory.CreateDatabase(strMainConnString)
        Try
            Using dbCommand As DbCommand = db.GetStoredProcCommand("MainAdminCaseStudy_ProductGetSelected")
                db.AddInParameter(dbCommand, "CaseStudyID", DbType.Int16, caseStudyID)
                db.AddInParameter(dbCommand, "CategoryID", DbType.Int16, categoryID)

                Dim oRecordset As IDataReader = db.ExecuteReader(dbCommand)
                While oRecordset.Read()
                    Dim objProduct As New entMainProduct
                    With objProduct
                        .ProductID = Convert.ToInt32(oRecordset("ProductID"))
                    End With
                    objProducts.Add(objProduct)
                End While
                oRecordset.Close()
            End Using
            Return objProducts
        Catch ex As Exception
            Throw ex
            Return Nothing
        End Try
    End Function
End Class
