Imports EDSL.Entities
Imports Microsoft.Practices.EnterpriseLibrary.Common
Imports Microsoft.Practices.EnterpriseLibrary.Data
Imports System.Configuration
Imports System.Data.Common
Public Class dbMainCategory
    Private Shared strMainConnString As String = "MainConnString"
    Public Function GetTheParentsID(ByVal intParentCategoryID As Integer) As Integer
        Dim objCategory As New entmainCategory
        Dim db As Database = DatabaseFactory.CreateDatabase(strMainConnString)
        Try
            Using dbCommand As DbCommand = db.GetStoredProcCommand("MainAdminCategoryGet")
                db.AddInParameter(dbCommand, "CategoryID", DbType.Int16, intParentCategoryID)
                Dim oRecordset As IDataReader = db.ExecuteReader(dbCommand)
                While oRecordset.Read()
                    Return Convert.ToInt32(oRecordset("ParentCategoryID"))
                End While
                oRecordset.Close()
            End Using
        Catch ex As Exception
            Throw ex
            Return Nothing
        End Try
    End Function
    Public Function GetItem(ByVal intCategoryID As Integer) As entmainCategory
        Dim objCategory As New entmainCategory
        Dim db As Database = DatabaseFactory.CreateDatabase(strMainConnString)
        Try
            Using dbCommand As DbCommand = db.GetStoredProcCommand("MainAdminCategoryGet")
                db.AddInParameter(dbCommand, "CategoryID", DbType.Int16, intCategoryID)
                Dim oRecordset As IDataReader = db.ExecuteReader(dbCommand)
                While oRecordset.Read()
                    With objCategory
                        .CategoryID = Convert.ToInt32(oRecordset("CategoryID"))
                        .CategoryName = Convert.ToString(oRecordset("CategoryName"))
                        .ParentCategoryID = Convert.ToInt32(oRecordset("ParentCategoryID"))
                        .CountOfSubs = Convert.ToInt32(oRecordset("CountOfSubs"))
                        .CategorySummary = Convert.ToString(oRecordset("CategorySummary"))
                        .BulletList = Convert.ToString(oRecordset("BulletList"))
                        .ThumbImage = Convert.ToString(oRecordset("ThumbImage"))
                        .Active = Convert.ToString(oRecordset("Active"))
                        .MetaDescription = Convert.ToString(oRecordset("MetaDescription"))
                        .MetaTitleBar = Convert.ToString(oRecordset("MetaTitleBar"))
                        .CategoryStyleID = Convert.ToInt32(oRecordset("CategoryStyleID"))
                        .DropDownValue = Convert.ToString(oRecordset("DropDownValue"))
                    End With
                End While
                oRecordset.Close()
            End Using
            Return objCategory
        Catch ex As Exception
            Throw ex
            Return Nothing
        End Try
    End Function
    Public Function GetSubCategoryItem(ByVal intCategoryID As Integer) As entmainCategory
        Dim objCategory As New entmainCategory
        Dim db As Database = DatabaseFactory.CreateDatabase(strMainConnString)
        Try
            Using dbCommand As DbCommand = db.GetStoredProcCommand("MainAdminSubCategoryGet")
                db.AddInParameter(dbCommand, "CategoryID", DbType.Int16, intCategoryID)
                Dim oRecordset As IDataReader = db.ExecuteReader(dbCommand)
                While oRecordset.Read()
                    With objCategory
                        .CategoryID = Convert.ToInt32(oRecordset("CategoryID"))
                        .CategoryName = Convert.ToString(oRecordset("CategoryName"))
                        .ParentCategoryID = Convert.ToInt32(oRecordset("ParentCategoryID"))
                        .CountOfSubs = Convert.ToInt32(oRecordset("CountOfSubs"))
                        .CategorySummary = Convert.ToString(oRecordset("CategorySummary"))
                        .BulletList = Convert.ToString(oRecordset("BulletList"))
                        .ThumbImage = Convert.ToString(oRecordset("ThumbImage"))
                        .Active = Convert.ToString(oRecordset("Active"))
                        .MetaDescription = Convert.ToString(oRecordset("MetaDescription"))
                        .MetaTitleBar = Convert.ToString(oRecordset("MetaTitleBar"))
                        .CategoryStyleID = Convert.ToInt32(oRecordset("CategoryStyleID"))
                        .DropDownValue = Convert.ToString(oRecordset("DropDownValue"))
                    End With
                End While
                oRecordset.Close()
            End Using
            Return objCategory
        Catch ex As Exception
            Throw ex
            Return Nothing
        End Try
    End Function

    Public Function GetAll(ByVal strWHEREclause As String, ByVal strORDERBYclause As String) As List(Of entmainCategory)
        Dim objPage As New List(Of entmainCategory)
        Dim db As Database = DatabaseFactory.CreateDatabase(strMainConnString)
        Try
            Using dbCommand As DbCommand = db.GetStoredProcCommand("MainAdminCategoryGetAll")
                db.AddInParameter(dbCommand, "WHEREclause", DbType.String, strWHEREclause)
                db.AddInParameter(dbCommand, "ORDERBYclause", DbType.String, strORDERBYclause)
                Dim oRecordset As IDataReader = db.ExecuteReader(dbCommand)
                While oRecordset.Read()
                    objPage.Add(New entmainCategory() With { _
                    .CategoryID = Convert.ToInt32(oRecordset("CategoryID")), _
                    .CategoryName = Convert.ToString(oRecordset("CategoryName")), _
                    .CategoryAdminTitle = Convert.ToString(oRecordset("CategoryPageTitle")), _
                    .ParentCategoryID = Convert.ToInt32(oRecordset("ParentCategoryID")), _
                    .CountOfSubs = Convert.ToInt32(oRecordset("CountOfSubs")), _
                    .CategorySummary = Convert.ToString(oRecordset("CategorySummary")), _
                    .BulletList = Convert.ToString(oRecordset("BulletList")), _
                    .ThumbImage = Convert.ToString(oRecordset("ThumbImage")), _
                    .Active = Convert.ToString(oRecordset("Active")), _
                    .MetaDescription = Convert.ToString(oRecordset("MetaDescription")), _
                    .MetaTitleBar = Convert.ToString(oRecordset("MetaTitleBar")), _
                    .CategoryStyleID = Convert.ToInt32(oRecordset("CategoryStyleID")) _
                    })
                End While
                oRecordset.Close()
            End Using
        Catch ex As Exception
            Throw ex
        End Try
        Return objPage
    End Function
    Public Function DeleteItem(ByVal intID As Integer, ByVal intParentCategoryID As Integer, ByVal intUserID As Integer) As Boolean
        Dim db As Database = DatabaseFactory.CreateDatabase(strMainConnString)
        Try
            Using dbCommand As DbCommand = db.GetStoredProcCommand("MainAdminCategoryDelete")
                db.AddInParameter(dbCommand, "CategoryID", DbType.Int16, intID)
                db.AddInParameter(dbCommand, "UserID", DbType.Int16, intUserID)
                db.ExecuteNonQuery(dbCommand)
            End Using

            If UpdateCountOfSubs(intParentCategoryID) = True Then
                Return True
            Else
                Return False
            End If
        Catch ex As Exception
            Return False
        End Try
    End Function
    Public Function SaveItem(ByVal intCategoryID As Integer, ByVal strCategoryName As String, ByVal intParentCategoryID As Integer, ByVal strCategorySummary As String, ByVal strThumbImage As String, ByVal intActive As Integer, ByVal intUserID As Integer, ByVal strMetaTitleBar As String, ByVal strMetaDescription As String, ByVal intCategoryStyleID As Integer, ByVal strBulletList As String) As Integer
        Dim db As Database = DatabaseFactory.CreateDatabase(strMainConnString)
        Dim strSPName As String = ""
        Try
            If intCategoryID = 0 Then
                strSPName = "MainAdminCategoryINSERT"
            Else
                strSPName = "MainAdminCategoryUPDATE"
            End If
            Using dbCommand As DbCommand = db.GetStoredProcCommand(strSPName)
                If intCategoryID <> 0 Then
                    db.AddInParameter(dbCommand, "CategoryID", DbType.Int16, intCategoryID)
                End If
                db.AddInParameter(dbCommand, "CategoryName", DbType.String, strCategoryName)
                db.AddInParameter(dbCommand, "ParentCategoryID", DbType.Int16, intParentCategoryID)
                db.AddInParameter(dbCommand, "CategorySummary", DbType.String, strCategorySummary)
                db.AddInParameter(dbCommand, "BulletList", DbType.String, strBulletList)
                db.AddInParameter(dbCommand, "ThumbImage", DbType.String, strThumbImage)
                db.AddInParameter(dbCommand, "Active", DbType.Int16, intActive)
                db.AddInParameter(dbCommand, "UserID", DbType.Int16, intUserID)
                db.AddInParameter(dbCommand, "MetaTitleBar", DbType.String, strMetaTitleBar)
                db.AddInParameter(dbCommand, "MetaDescription", DbType.String, strMetaDescription)
                db.AddInParameter(dbCommand, "CategoryStyleID", DbType.Int32, intCategoryStyleID)
                db.ExecuteNonQuery(dbCommand)
            End Using

            If UpdateCountOfSubs(intParentCategoryID) = True Then
                Return 0
            Else
                Return 2
            End If
        Catch ex As Exception
            Return 1
        End Try
    End Function

    Private Function UpdateCountOfSubs(ByVal intParentCategoryID As Integer) As Boolean
        Dim db As Database = DatabaseFactory.CreateDatabase(strMainConnString)
        Try
            ' Update CountOfSubs for the ParentCategoryID
            Using dbCommand As DbCommand = db.GetStoredProcCommand("MainAdminCategoryUPDATECountOfSubs")
                db.AddInParameter(dbCommand, "ParentCategoryID", DbType.Int16, intParentCategoryID)
                db.ExecuteNonQuery(dbCommand)
            End Using
            Return True
        Catch ex As Exception
            Return False
        End Try
    End Function

    Public Function GetITServiceItem(ByVal intCategoryID As Integer) As entmainCategory
        Dim objCategory As New entmainCategory
        Dim db As Database = DatabaseFactory.CreateDatabase(strMainConnString)
        Try
            Using dbCommand As DbCommand = db.GetStoredProcCommand("projGetITServiceDetail")
                db.AddInParameter(dbCommand, "ParentCategoryID", DbType.Int16, intCategoryID)
                Dim oRecordset As IDataReader = db.ExecuteReader(dbCommand)
                While oRecordset.Read()
                    With objCategory
                        .CategoryID = Convert.ToInt32(oRecordset("CategoryID"))
                        .CategoryName = Convert.ToString(oRecordset("CategoryName"))
                        .ParentCategoryID = Convert.ToInt32(oRecordset("ParentCategoryID"))
                        .CountOfSubs = Convert.ToInt32(oRecordset("CountOfSubs"))
                        .CategorySummary = Convert.ToString(oRecordset("CategorySummary"))
                        .ThumbImage = Convert.ToString(oRecordset("ThumbImage"))
                        .Active = Convert.ToString(oRecordset("Active"))
                        .MetaDescription = Convert.ToString(oRecordset("MetaDescription"))
                        .MetaTitleBar = Convert.ToString(oRecordset("MetaTitleBar"))
                        .CategoryStyleID = Convert.ToInt32(oRecordset("CategoryStyleID"))
                        .DropDownValue = Convert.ToString(oRecordset("DropDownValue"))
                    End With
                End While
                oRecordset.Close()
            End Using
            Return objCategory
        Catch ex As Exception
            Throw ex
            Return Nothing
        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_CategoryDelete")
                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_CategoryDelete")
                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_CategoryDelete")
                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 parentID As Integer) As Boolean
        Dim db As Database = DatabaseFactory.CreateDatabase(strMainConnString)
        Try
            Using dbCommand As DbCommand = db.GetStoredProcCommand("MainAdminNews_CategoryINSERT")
                db.AddInParameter(dbCommand, "NewsID", DbType.Int16, newsID)
                db.AddInParameter(dbCommand, "CategoryID", DbType.Int16, categoryID)
                db.AddInParameter(dbCommand, "ParentCategoryID", DbType.Int16, parentID)
                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 parentID As Integer) As Boolean
        Dim db As Database = DatabaseFactory.CreateDatabase(strMainConnString)
        Try
            Using dbCommand As DbCommand = db.GetStoredProcCommand("MainAdminCaseStudy_CategoryINSERT")
                db.AddInParameter(dbCommand, "CaseStudyID", DbType.Int16, caseStudyID)
                db.AddInParameter(dbCommand, "CategoryID", DbType.Int16, categoryID)
                db.AddInParameter(dbCommand, "ParentCategoryID", DbType.Int16, parentID)
                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 parentID As Integer) As Boolean
        Dim db As Database = DatabaseFactory.CreateDatabase(strMainConnString)
        Try
            Using dbCommand As DbCommand = db.GetStoredProcCommand("MainAdminCaseStudy_CategoryINSERT")
                db.AddInParameter(dbCommand, "CaseStudyID", DbType.Int16, caseStudyID)
                db.AddInParameter(dbCommand, "CategoryID", DbType.Int16, categoryID)
                db.AddInParameter(dbCommand, "ParentCategoryID", DbType.Int16, parentID)
                db.ExecuteNonQuery(dbCommand)
            End Using
            Return True

        Catch ex As Exception
            Return False
        End Try
    End Function

    ''' <summary>
    ''' Get Selected Categories with Related News for Checkbox admin
    ''' </summary>
    ''' <param name="newsID"></param>
    ''' <param name="parentCategoryID"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetRelatedNews(ByVal newsID As Integer, ByVal parentCategoryID As Integer) As List(Of entmainCategory)
        Dim objCategories As New List(Of entmainCategory)
        Dim db As Database = DatabaseFactory.CreateDatabase(strMainConnString)
        Try
            Using dbCommand As DbCommand = db.GetStoredProcCommand("MainAdminNews_CategoryGetSelected")
                db.AddInParameter(dbCommand, "ParentCategoryID", DbType.Int16, parentCategoryID)
                db.AddInParameter(dbCommand, "NewsID", DbType.Int16, newsID)
                Dim oRecordset As IDataReader = db.ExecuteReader(dbCommand)
                While oRecordset.Read()
                    Dim objCat As New entmainCategory
                    With objCat
                        .CategoryID = Convert.ToInt32(oRecordset("CategoryID"))
                    End With
                    objCategories.Add(objCat)
                End While
                oRecordset.Close()
            End Using
            Return objCategories
        Catch ex As Exception
            Throw ex
            Return Nothing
        End Try
    End Function
    ''' <summary>
    ''' Get Selected Categories with Related CaseStudies for Checkbox admin
    ''' </summary>
    ''' <param name="caseStudyID"></param>
    ''' <param name="parentCategoryID"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetRelatedCaseStudies(ByVal caseStudyID As Integer, ByVal parentCategoryID As Integer) As List(Of entmainCategory)
        Dim objCategories As New List(Of entmainCategory)
        Dim db As Database = DatabaseFactory.CreateDatabase(strMainConnString)
        Try
            Using dbCommand As DbCommand = db.GetStoredProcCommand("MainAdminCaseStudy_CategoryGetSelected")
                db.AddInParameter(dbCommand, "ParentCategoryID", DbType.Int16, parentCategoryID)
                db.AddInParameter(dbCommand, "CaseStudyID", DbType.Int16, caseStudyID)
                Dim oRecordset As IDataReader = db.ExecuteReader(dbCommand)
                While oRecordset.Read()
                    Dim objCat As New entmainCategory
                    With objCat
                        .CategoryID = Convert.ToInt32(oRecordset("CategoryID"))
                    End With
                    objCategories.Add(objCat)
                End While
                oRecordset.Close()
            End Using
            Return objCategories
        Catch ex As Exception
            Throw ex
            Return Nothing
        End Try
    End Function
    Public Function GetRelatedTestimonials(ByVal caseStudyID As Integer, ByVal parentCategoryID As Integer) As List(Of entmainCategory)
        Dim objCategories As New List(Of entmainCategory)
        Dim db As Database = DatabaseFactory.CreateDatabase(strMainConnString)
        Try
            Using dbCommand As DbCommand = db.GetStoredProcCommand("MainAdminCaseStudy_CategoryGetSelected")
                db.AddInParameter(dbCommand, "ParentCategoryID", DbType.Int16, parentCategoryID)
                db.AddInParameter(dbCommand, "CaseStudyID", DbType.Int16, caseStudyID)
                Dim oRecordset As IDataReader = db.ExecuteReader(dbCommand)
                While oRecordset.Read()
                    Dim objCat As New entmainCategory
                    With objCat
                        .CategoryID = Convert.ToInt32(oRecordset("CategoryID"))
                    End With
                    objCategories.Add(objCat)
                End While
                oRecordset.Close()
            End Using
            Return objCategories
        Catch ex As Exception
            Throw ex
            Return Nothing
        End Try
    End Function


    ''' <summary>
    ''' Get a list of all categories, pages and products 
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetListForEnquiryForm() As List(Of entMainDropDown)
        Dim objCategories As New List(Of entmainCategory)
        Dim objList As New List(Of entMainDropDown)

        objCategories = GetAll(" AND ParentCategoryID = 0 AND Active = 1 ", " Order By CategoryName ASC")

        Try
            For Each cat In objCategories
                With cat
                    'add a parent catgory to the list
                    Dim newItem As New entMainDropDown
                    newItem.DropDownValue = .CategoryID & "|0|0"
                    newItem.TextDisplay = .CategoryName
                    objList.Add(newItem)

                    'Get the sub categories belong to the parent and add to list
                    Dim objSubCategories As New List(Of entmainCategory)
                    objSubCategories = GetAll(" AND ParentCategoryID = " & .CategoryID & " AND Active = 1  ", " Order By CategoryName ASC")

                    For Each subCat In objSubCategories

                        Dim newSubItem As New entMainDropDown
                        newSubItem.DropDownValue = subCat.CategoryID & "|" & subCat.ParentCategoryID & "|0"
                        newSubItem.TextDisplay = " -- " & subCat.CategoryName
                        objList.Add(newSubItem)


                        'Get the products belonging to each sub category and add to the list
                        Dim objProducts As New dbMainProduct
                        Dim lstProducts As New List(Of entMainProduct)
                        lstProducts = objProducts.GetAllByCategoryID(subCat.CategoryID)
                        For Each product In lstProducts
                            Dim newProductItem As New entMainDropDown
                            newProductItem.DropDownValue = subCat.CategoryID & "|" & subCat.ParentCategoryID & "|" & product.ProductID
                            newProductItem.TextDisplay = "  ---- " & product.ProductName
                            objList.Add(newProductItem)

                        Next
                    Next

                End With

            Next

            Return objList
        Catch ex As Exception
            Throw ex
            Return Nothing
        End Try
    End Function
End Class
