Lookup

Imports System.Data
Imports System.Data.SqlClient
Imports Microsoft.VisualBasic

Public Class Lookups : Inherits SQLHelper

    Public Sub New()

    End Sub



    Public Sub GetCountryRegionDDL(ByVal ddlIn As DropDownList)
        Dim dt As DataTable = Me.ExecuteDataTableSP("sp_SelectCountryRegionsDDL")
        BindDDL(dt, ddlIn)
        ddlIn.Items.Add(New ListItem("Other", "0"))
    End Sub

    Public Sub GetCategoriesMetaDDL(ByVal ddlIn As DropDownList, Optional ByVal intCategoryID As Integer = 0)
        Dim params() As SqlParameter = {New SqlParameter("@CategoryID", SqlDbType.Int)}
        params(0).Value = intCategoryID
        Dim dt As DataTable = Me.ExecuteDataTableSP("sp_SelectCategoriesMetaDDL", params)
        BindDDL(dt, ddlIn)
    End Sub

    Public Sub GetStateProvinceByCountryRegionIDDDL(ByVal ddlIn As DropDownList, ByVal intCountryRegionID As Integer)
        Dim params() As SqlParameter = {New SqlParameter("@CountryRegionID", SqlDbType.Int)}
        params(0).Value = intCountryRegionID
        Dim dt As DataTable = Me.ExecuteDataTableSP("sp_SelectStateProvincesByCountryRegionIDDDL", params)
        BindDDL(dt, ddlIn)
    End Sub

    Public Sub GetCompaniesDDL(ByVal ddlIn As DropDownList)
        Dim dt As DataTable = Me.ExecuteDataTableSP("sp_SelectCompaniesDDL")
        BindDDL(dt, ddlIn)
    End Sub

    Public Sub GetCompaniesLB(ByVal lbIn As ListBox)
        Dim dt As DataTable = Me.ExecuteDataTableSP("sp_SelectCompaniesDDL")
        BindLB(dt, lbIn)
    End Sub

    Public Sub GetCategoriesDDL(ByVal ddlIn As DropDownList)
        Dim dt As DataTable = Me.ExecuteDataTableSP("sp_SelectCategoriesDDL")
        BindDDL(dt, ddlIn)
    End Sub

    Public Sub GetCategoriesLB(ByVal lbIn As ListBox)
        Dim dt As DataTable = Me.ExecuteDataTableSP("sp_SelectCategoriesDDL")
        BindLB(dt, lbIn)
    End Sub

    Public Sub GetColorsLB(ByVal lbIn As ListBox, ByVal intCardID As Integer)
        Dim params() As SqlParameter = {New SqlParameter("@CardID", SqlDbType.Int)}
        params(0).Value = intCardID
        Dim dt As DataTable = Me.ExecuteDataTableSP("sp_SelectColorsByCardIDDDL", params)
        BindLB(dt, lbIn)
    End Sub

    Public Sub GetFiltersLB(ByVal lbIn As ListBox)
        Dim dt As DataTable = Me.ExecuteDataTableSP("usp_SelectFiltersDDL")
        BindLB(dt, lbIn)
    End Sub

    Public Sub GetPriceGroupsDDL(ByVal ddlIn As DropDownList)
        Dim dt As DataTable = Me.ExecuteDataTableSP("sp_SelectPriceGroupsDDL")
        BindDDL(dt, ddlIn)
    End Sub

    Public Sub GetCardsByPriceGroupIDLB(ByVal lbIn As ListBox, ByVal intPriceGroupID As Integer)
        Dim params() As SqlParameter = {New SqlParameter("@PriceGroupID", SqlDbType.Int)}
        params(0).Value = intPriceGroupID
        Dim dt As DataTable = Me.ExecuteDataTableSP("sp_SelectCardsByPriceGroupIDDDL", params)
        BindLB(dt, lbIn)
    End Sub

    Public Sub GetColorsByCardIDDDL(ByVal ddlIn As DropDownList, ByVal intCardID As Integer)
        Dim params() As SqlParameter = {New SqlParameter("@CardID", SqlDbType.Int)}
        params(0).Value = intCardID
        Dim dt As DataTable = Me.ExecuteDataTableSP("sp_SelectColorsByCardIDDDL", params)
        BindDDL(dt, ddlIn, "-None/Blank-", "-1")
    End Sub

    Public Function GetColorsByCardID(ByVal intCardID As Integer) As DataTable
        Dim params() As SqlParameter = {New SqlParameter("@CardID", SqlDbType.Int)}
        params(0).Value = intCardID
        Dim dt As DataTable = Me.ExecuteDataTableSP("sp_SelectColorsByCardIDDDL", params)
        Return dt
    End Function

    Public Sub GetVersesByCompanyIDDDL(ByVal ddlIn As DropDownList, ByVal companyID As Integer)
        Dim params() As SqlParameter = {New SqlParameter("@CompanyID", SqlDbType.Int)}
        params(0).Value = companyID
        Dim dt As DataTable = Me.ExecuteDataTableSP("sp_SelectVersesByCompanyIDDDL", params)
        BindDDL(dt, ddlIn, "-Blank-", "-1")
        ddlIn.Items(0).Selected = True
    End Sub

    Public Sub GetVersesByCardIDDDL(ByVal ddlIn As DropDownList, ByVal intCardID As Integer)
        Dim params() As SqlParameter = {New SqlParameter("@CardID", SqlDbType.Int)}
        params(0).Value = intCardID
        Dim dt As DataTable = Me.ExecuteDataTableSP("sp_SelectVersesByCardIDDDL", params)
        BindDDL(dt, ddlIn, "-Blank-", "-1")
        ddlIn.Items(0).Selected = True
    End Sub

    Public Sub GetTypeStyleByCardIDDDL(ByVal ddlIn As DropDownList, ByVal intCardID As Integer)
        Dim params() As SqlParameter = {New SqlParameter("@CardID", SqlDbType.Int)}
        params(0).Value = intCardID
        Dim dt As DataTable = Me.ExecuteDataTableSP("sp_SelectTypeStyleByCardIDDDL", params)
        BindDDL(dt, ddlIn)
    End Sub

    Public Sub GetQuantityByCardIDDDL(ByVal ddlIn As DropDownList, ByVal intCardID As Integer)
        Dim params() As SqlParameter = {New SqlParameter("@CardID", SqlDbType.Int)}
        params(0).Value = intCardID
        Dim dt As DataTable = Me.ExecuteDataTableSP("sp_SelectQuantityByCardIDDDL", params)
        BindDDL(dt, ddlIn)
    End Sub

    Public Sub GetEnvelopesByCardIDDDL(ByVal ddlIn As DropDownList, ByVal intCardID As Integer)
        Dim params() As SqlParameter = {New SqlParameter("@CardID", SqlDbType.Int)}
        params(0).Value = intCardID
        Dim dt As DataTable = Me.ExecuteDataTableSP("sp_SelectEnvelopesByCardIDDDL", params)
        BindDDL(dt, ddlIn)
    End Sub

    Public Sub GetColorsDDL(ByVal ddlIn As DropDownList)
        Dim dt As DataTable = Me.ExecuteDataTableSP("sp_SelectColorsDDL")
        BindDDL(dt, ddlIn)
    End Sub

    Public Sub BindDDL(ByVal dt As DataTable, ByVal ddlIn As DropDownList, Optional ByVal strDefaultText As String = "-Select-", Optional ByVal strDefaultValue As String = "-1")
        ddlIn.DataSource = dt
        ddlIn.DataTextField = "DDLText"
        ddlIn.DataValueField = "DDLValue"
        ddlIn.DataBind()
        ddlIn.Items.Insert(0, New ListItem(strDefaultText, strDefaultValue))
    End Sub

    Public Sub BindCBL(ByVal dt As DataTable, ByVal cblIn As CheckBoxList)
        cblIn.DataSource = dt
        cblIn.DataTextField = "DDLText"
        cblIn.DataValueField = "DDLValue"
        cblIn.DataBind()
    End Sub

    Public Sub BindLB(ByVal dt As DataTable, ByVal lbIn As ListBox)
        lbIn.DataSource = dt
        lbIn.DataTextField = "DDLText"
        lbIn.DataValueField = "DDLValue"
        lbIn.DataBind()
    End Sub

End Class