Imports DatabaseLibrary.DBLuokat
Imports OboutInc.SlideMenu

Public Class clsMenu
    Inherits clsPerus
    Private _obMenu As New SlideMenu
    Private _obIlmoitusMenu As New SlideMenu
    Private _iKieli As Integer = 0
    Private _strHakuSana As String = String.Empty
    Public Sub New()

    End Sub
    Public Property Kieli() As Integer
        Get
            Return _iKieli
        End Get
        Set(ByVal Value As Integer)
            _iKieli = Value
        End Set
    End Property
    Public Property HakuSana() As String
        Get
            Return _strHakuSana
        End Get
        Set(ByVal Value As String)
            _strHakuSana = Value
        End Set
    End Property
    Public Sub Hae()
        Dim obDS As New clsDB_OleDb
        Dim dtValikko As New DataTable
        Dim dtAlavalikko As New DataTable
        'Dim strSQL As String = ""
        Try
            '_obMenu = New SlideMenu
            '_obMenu.MenuItems.Clear()
            Me.PalautaMenu.MenuItems.Clear()
            Me.PalautaMenu.ID = "Glossary"
            Me.PalautaMenu.UrlTarget = "_sanat"
            Me.PalautaMenu.ScriptPath = "slidemenu/smscript"
            Me.PalautaMenu.StyleFolder = "slidemenu/styles/pro_9"
            Me.PalautaMenu.Speed = 5
            obDS.ConnectionString = Me.ConnectionString 'ConfigurationSettings.AppSettings("naku")
            obDS.OpenDBConnection()
            If Me.HakuSana = String.Empty Then
                Me.strSQL = "Select strgroup From glossaries where intlanguageid=" & Me.Kieli & " group by strgroup "
            Else
                Me.strSQL = "select strgroup from glossaries where (intlanguageid=" & Me.Kieli & ") and (strseach like '%" & Me.HakuSana.Trim & "%'" & _
                " or strexplanation like '%" & Me.HakuSana.Trim & "%') group by strgroup"
            End If
            dtValikko = obDS.GetDataSet(Me.strSQL).Tables(0)
            For i As Integer = 0 To dtValikko.Rows.Count - 1
                ' osm.AddParent(dtValikko.Rows(i)("strValikko"))
                Me.PalautaMenu.AddParent(Me.Kieli & "_" & dtValikko.Rows(i)(0), dtValikko.Rows(i)(0), "40002.aspx", "ob_post('on_button1_click', 'Hello there!')")
                Me.strSQL = "Select * from glossaries WHERE strgroup='" & dtValikko.Rows(i)(0) & "' and intlanguageid=" & _
                Me.Kieli & IIf(Me.HakuSana <> String.Empty, " and (strseach like '%" & Me.HakuSana.Trim & "%'" & _
                " or strexplanation like '%" & Me.HakuSana.Trim & "%')", String.Empty) & " order by strseach asc"
                dtAlavalikko = obDS.GetDataSet(Me.strSQL).Tables(0)
                For j As Integer = 0 To dtAlavalikko.Rows.Count - 1
                    'Response.Write("<A HREF ='" & dtAlavalikko.Rows(j)("stravattavasivu") & "' onMouseOver=window.status='Tornion Kokoomus';return true onMouseOut=window.status=' ';return true>")
                    ' osm.AddChild(dtAlavalikko.Rows(j)("strValikko"), dtAlavalikko.Rows(j)("strAvattavaSivu"), False, CType(dtAlavalikko.Rows(j)("intValikkoID") & "&ryhma=" & dtAlavalikko.Rows(j)("intryhma"), String))
                    ' Response.Write("</A>")
                    Me.PalautaMenu.AddChild(Me.Kieli & "_" & dtAlavalikko.Rows(j)("intglossaryid"), dtAlavalikko.Rows(j)("strseach"), "40002.aspx")
                Next
            Next

            obDS.CloseDBConnection()
            obDS.Dispose()
            obDS = Nothing
        Catch ex As Exception
            If Not IsNothing(obDS) Then
                obDS.CloseDBConnection()
                obDS.Dispose()
                obDS = Nothing
            End If
        End Try
    End Sub
    Public ReadOnly Property PalautaMenu() As SlideMenu
        Get
            Return _obMenu
        End Get
    End Property
    Public Sub HaeIlmoitukset()
        Dim obDS As New clsDB_OleDb
        Dim dtHaku As New DataTable
        Dim dtApu As New DataTable
        Dim strSQL As String = String.Empty
        Dim dtEraPaiva As Date
        Try
            Me.PalautaIlmoitukset.ID = "Naku"
            Me.PalautaIlmoitukset.UrlTarget = "_ilmoitusNaytto"
            Me.PalautaIlmoitukset.ScriptPath = "slidemenu/smscript"
            Me.PalautaIlmoitukset.StyleFolder = "slidemenu/styles/pro_9"
            Me.PalautaIlmoitukset.Speed = 5
            strSQL = "select intryhmaid, strnimi from ilmoitusryhmat order by intryhmaid "
            obDS.ConnectionString = ConfigurationSettings.AppSettings("naku")
            obDS.OpenDBConnection()
            dtHaku = obDS.GetDataSet(strSQL).Tables(0)
            For i As Integer = 0 To dtHaku.Rows.Count - 1
                Me.PalautaIlmoitukset.AddParent(dtHaku.Rows(i)(0), dtHaku.Rows(i)(1), "frmIlmoitusNaytto.aspx", "(ob_post('on_button1_click', '" & dtHaku.Rows(i)(0) & "')")
                strSQL = "select intilmoitustauluid,strotsikko,intvoimassaoloaika,dtlisayspaiva from ilmoitustaulu " & _
                " where intryhmaid=" & dtHaku.Rows(i)(0)
                dtApu = obDS.GetDataSet(strSQL).Tables(0)
                For j As Integer = 0 To dtApu.Rows.Count - 1
                    dtEraPaiva = DateAdd(DateInterval.Day, dtApu.Rows(j)("intvoimassaoloaika"), CType(dtApu.Rows(j)("dtlisayspaiva"), Date))
                    If dtEraPaiva >= Now.Date Then
                        Me.PalautaIlmoitukset.AddChild(dtHaku.Rows(i)(0) & "_" & dtApu.Rows(j)(0), dtApu.Rows(j)(1), "frmIlmoitusNaytto.aspx")

                    End If
                Next
            Next
            obDS.CloseDBConnection()
            obDS.Dispose()
            obDS = Nothing
        Catch ex As Exception
            If Not IsNothing(obDS) Then
                obDS.CloseDBConnection()
                obDS.Dispose()
                obDS = Nothing
            End If
        End Try
    End Sub
    Public ReadOnly Property PalautaIlmoitukset() As SlideMenu
        Get
            Return _obIlmoitusMenu
        End Get
    End Property
    Private Function Maara(ByVal sGroup As String)
        Dim obDS As New clsDB_OleDb
        Dim dtHaku As New DataTable
        'Dim strSQL As String = String.Empty
        Try
            Me.strSQL = "select count(intglossaryid) as lkm from glossaries " & _
            " where (strgroup='" & sGroup & "') "
            If Me.HakuSana <> String.Empty Then
                Me.strSQL += " and (strseach like '%" & Me.HakuSana.Trim & "%' or " & _
                "strexplanation like '%" & Me.HakuSana.Trim & "%')"
            End If

            obDS.ConnectionString = Me.ConnectionString  'ConfigurationSettings.AppSettings("naku")
            obDS.OpenDBConnection()
            dtHaku = obDS.GetDataSet(strSQL).Tables(0)
            obDS.CloseDBConnection()
            obDS.Dispose()
            obDS = Nothing
            Return dtHaku.Rows(0)(0)
        Catch ex As Exception
            If Not IsNothing(obDS) Then
                obDS.CloseDBConnection()
                obDS.Dispose()
                obDS = Nothing
            End If
            Return 0
        End Try
    End Function
End Class