Imports Microsoft.Practices.EnterpriseLibrary.Common
Imports Microsoft.Practices.EnterpriseLibrary.Data
Imports System.Configuration
Imports System.Data.Common
Imports System.Web
Imports System.Text.StringBuilder

Public Class dbMainSelectorBox
    Private intWidth As Integer = 200
    Private Shared strMainConnString As String = "MainConnString"
    Public Function GetChoices(ByVal AllOptionsTableName As String, ByVal TableNameIDFieldName As String, ByVal FieldNameOfDisplayText As String, Optional ByVal strWHEREClause As String = "") As System.Web.UI.WebControls.ListItemCollection
        'Dim objMainSelector As new list(Of entmainSelectorBox)
        Dim db As Database = DatabaseFactory.CreateDatabase(strMainConnString)
        Dim objListCollection As New System.Web.UI.WebControls.ListItemCollection

        Try
            Using dbCommand As DbCommand = db.GetStoredProcCommand("MainAdminGetChoices")
                db.AddInParameter(dbCommand, "AllOptionsTableName", DbType.String, AllOptionsTableName)
                db.AddInParameter(dbCommand, "TableNameIDFieldName", DbType.String, TableNameIDFieldName)
                db.AddInParameter(dbCommand, "FieldNameOfDisplayText", DbType.String, FieldNameOfDisplayText)
                db.AddInParameter(dbCommand, "WHEREClause", DbType.String, strWHEREClause)
                Dim oRecordset As IDataReader = db.ExecuteReader(dbCommand)
                While oRecordset.Read()
                    Dim selItem As New System.Web.UI.WebControls.ListItem
                    ' Use positional as the ID and displaytext field could be named anything
                    selItem.Value = Convert.ToString(oRecordset(0))
                    selItem.Text = Convert.ToString(oRecordset(1))
                    objListCollection.Add(selItem)
                End While
                oRecordset.Close()
            End Using
            Return objListCollection
        Catch ex As Exception
            Throw ex
            Return Nothing
        End Try
    End Function

    Public Function GetChoicesAssignCategories(ByVal strWHEREclause As String, ByVal strORDERBYclause As String) As System.Web.UI.WebControls.ListItemCollection
        'Dim objMainSelector As new list(Of entmainSelectorBox)
        Dim db As Database = DatabaseFactory.CreateDatabase(strMainConnString)
        Dim objListCollection As New System.Web.UI.WebControls.ListItemCollection

        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()
                    Dim selItem As New System.Web.UI.WebControls.ListItem
                    ' Use positional as the ID and displaytext field could be named anything
                    selItem.Value = Convert.ToString(oRecordset(0) & "," & oRecordset(3))
                    selItem.Text = Convert.ToString(oRecordset(1))
                    selItem.Attributes.CssStyle.Add("font-style", "italic")
                    selItem.Attributes.CssStyle.Add("font-weight", "bold")
                 
                    objListCollection.Add(selItem)
                    ' Get the sub categories to belong in the list
                    Dim objSubcategories As New dbMainCategory
                    Dim lstOfSubcategories As New List(Of Entities.entmainCategory)
                    lstOfSubcategories = objSubcategories.GetAll(" AND ParentCategoryID = " & Convert.ToInt32(oRecordset(0)), " AND ( CategoryID <> 4 AND CategoryID <> 5)  ORDER BY CategoryName ASC")
                    For Each subCategory In lstOfSubcategories
                        Dim selItemSubCategory As New System.Web.UI.WebControls.ListItem
                        selItemSubCategory.Value = Convert.ToString(subCategory.CategoryID & "," & subCategory.ParentCategoryID)
                        selItemSubCategory.Text = Convert.ToString(subCategory.CategoryName)
                        selItemSubCategory.Attributes.CssStyle.Add("padding-left", "10px")
                        objListCollection.Add(selItemSubCategory)

                    Next

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

    Public Function GetSelected(ByVal LinkTableName As String, ByVal LinkTableNameIDFieldName As String, ByVal IDFieldToCompareAgainstChoicesSelectBox As String, _
                                ByVal FieldToCompareToTheID As String, _
                                ByVal intID As Integer, _
                                ByRef objChoices As System.Web.UI.WebControls.ListItemCollection, _
                                ByVal AllOptionsTableName As String, ByVal TableNameIDFieldName As String, ByVal FieldNameOfDisplayText As String) As System.Web.UI.WebControls.ListItemCollection

        Dim db As Database = DatabaseFactory.CreateDatabase(strMainConnString)
        Dim objListCollection As New System.Web.UI.WebControls.ListItemCollection

        Try
            Using dbCommand As DbCommand = db.GetStoredProcCommand("MainAdminGetSelected")
                db.AddInParameter(dbCommand, "LinkTableName", DbType.String, LinkTableName)
                db.AddInParameter(dbCommand, "LinkTableNameIDFieldName", DbType.String, LinkTableNameIDFieldName)
                db.AddInParameter(dbCommand, "FieldToCompareToTheID", DbType.String, FieldToCompareToTheID)
                db.AddInParameter(dbCommand, "IDFieldToCompareAgainstChoicesSelectBox", DbType.String, IDFieldToCompareAgainstChoicesSelectBox)
                db.AddInParameter(dbCommand, "TheID", DbType.Int16, intID)
                'Same as Choice box so can put an INNER JOIN in so I can order by the name
                db.AddInParameter(dbCommand, "AllOptionsTableName", DbType.String, AllOptionsTableName)
                db.AddInParameter(dbCommand, "TableNameIDFieldName", DbType.String, TableNameIDFieldName)
                db.AddInParameter(dbCommand, "FieldNameOfDisplayText", DbType.String, FieldNameOfDisplayText)
                Dim oRecordset As IDataReader = db.ExecuteReader(dbCommand)

                While oRecordset.Read()

                    If Not objChoices.FindByValue(Convert.ToString(oRecordset(1)).Trim) Is Nothing Then

                        Dim selItem As New System.Web.UI.WebControls.ListItem
                        selItem.Text = objChoices.FindByValue(Convert.ToString(oRecordset(1)).Trim).Text
                        selItem.Value = objChoices.FindByValue(Convert.ToString(oRecordset(1)).Trim).Value
                        objListCollection.Add(selItem)
                        ' If the item's in the selected option list then remove it from the available choices
                        objChoices.Remove(selItem)
                    End If

                End While
                oRecordset.Close()
            End Using
            Return objListCollection
        Catch ex As Exception
            Throw ex
            Return Nothing
        End Try
    End Function
    Public Function GetChoicesCategories() As System.Web.UI.WebControls.ListItemCollection
        Dim objListCollection As New System.Web.UI.WebControls.ListItemCollection
        Try
            Call GetRows(0, 0, objListCollection)
            Return objListCollection
        Catch ex As Exception
            Throw ex
            Return Nothing
        End Try
    End Function
    Private Sub GetRows(ByVal intParentCategoryID As Integer, ByVal intDepth As Integer, ByRef objListCollection As System.Web.UI.WebControls.ListItemCollection)
        Dim db As Database = DatabaseFactory.CreateDatabase(strMainConnString)
        Try
            Using dbCommand As DbCommand = db.GetStoredProcCommand("MainAdminGetChoicesCategories")
                db.AddInParameter(dbCommand, "ParentCategoryID", DbType.String, intParentCategoryID)
                Dim oRecordset As IDataReader = db.ExecuteReader(dbCommand)
                While oRecordset.Read()
                    Dim selItem As New System.Web.UI.WebControls.ListItem

                    selItem.Value = Convert.ToString(oRecordset("CategoryID"))
                    selItem.Text = AddInset(intDepth) & Convert.ToString(oRecordset(1))
                    objListCollection.Add(selItem)
                    Call GetRows(oRecordset("CategoryID"), intDepth + 1, objListCollection)
                End While
                oRecordset.Close()
                Dim y As String = ""
                For i = 1 To 255
                    y += Chr(i)
                Next
            End Using
        Catch ex As Exception
            Throw ex
        End Try
    End Sub
    Private Function AddInset(ByVal intWidth As Integer) As String
        Dim strInset As String = ""
        ' Chr(187) is the double >> symbol
        For intCount = 1 To intWidth
            strInset += Chr(187)
        Next
        strInset += " "
        Return strInset
    End Function

    Public Function GetSelectedCategories(ByVal strTableName As String, ByVal strFieldname As String, ByVal strFieldnameLookingAt As String, ByVal intID As Integer, ByRef objChoices As System.Web.UI.WebControls.ListItemCollection) As System.Web.UI.WebControls.ListItemCollection
        Dim db As Database = DatabaseFactory.CreateDatabase(strMainConnString)
        Dim objListCollection As New System.Web.UI.WebControls.ListItemCollection

        Try
            Using dbCommand As DbCommand = db.GetStoredProcCommand("MainAdminGetSelected")
                db.AddInParameter(dbCommand, "TableName", DbType.String, strTableName)
                db.AddInParameter(dbCommand, "FieldName", DbType.String, strFieldname)
                db.AddInParameter(dbCommand, "FieldNameLookingAt", DbType.String, strFieldnameLookingAt)
                db.AddInParameter(dbCommand, "TheID", DbType.Int16, intID)
                Dim oRecordset As IDataReader = db.ExecuteReader(dbCommand)
                While oRecordset.Read()
                    If Not objChoices.FindByValue(Convert.ToString(oRecordset(2))) Is Nothing Then
                        Dim selItem As New System.Web.UI.WebControls.ListItem
                        selItem.Text = objChoices.FindByValue(Convert.ToString(oRecordset(2))).Text
                        selItem.Value = objChoices.FindByValue(Convert.ToString(oRecordset(2))).Value
                        objListCollection.Add(selItem)
                    End If
                End While
                oRecordset.Close()
            End Using
            Return objListCollection
        Catch ex As Exception
            Throw ex
            Return Nothing
        End Try
    End Function
    Public Property Width() As Integer
        Get
            Return intWidth
        End Get
        Set(ByVal value As Integer)
            intWidth = value
        End Set
    End Property
End Class