Imports System.Data.OleDb
Imports System.Text
Imports System.Math
Imports System.Data
Partial Class Information
    Inherits System.Web.UI.Page
    Protected WithEvents tddisp1 As New System.Web.UI.HtmlControls.HtmlTableCell
    Protected WithEvents tdHeading1 As New System.Web.UI.HtmlControls.HtmlTableCell
    '  Dim con As New SqlConnection(constr)
    Protected WithEvents lnkCategory1 As New System.Web.UI.WebControls.HyperLink

    Protected WithEvents tdInformation1 As New System.Web.UI.HtmlControls.HtmlTableCell
    Protected WithEvents lnkTitle1 As New System.Web.UI.WebControls.HyperLink

    Public Shared intCatId As String
    Public Shared MyTitle As String
    Protected WithEvents litMetadata1 As New System.Web.UI.WebControls.Literal
    Protected WithEvents litDescription1 As New System.Web.UI.WebControls.Literal
    Dim metadata As New System.Collections.Specialized.HybridDictionary()
#Region " Web Form Designer Generated Code "

    'This call is required by the Web Form Designer.
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()

    End Sub

    Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
        'CODEGEN: This method call is required by the Web Form Designer
        'Do not modify it using the code editor.
        InitializeComponent()
    End Sub

#End Region
    Protected Sub HomeBottom1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles HomeBottom1.Load

    End Sub

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If Not IsNothing(Request.QueryString("id")) Then
            intCatId = Request.QueryString("id")
            SelectMainCategory(intCatId)
            LodData(intCatId)
        End If
    End Sub

    Private Sub SelectMainCategory(ByVal CatId)
        Dim PrmPathExcelFile As String = Server.MapPath("./Category/categorymaster.xls")
        Dim strTableName = "Categorymaster"
        Dim MyConnection As OleDbConnection

        MyConnection = New System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0; " & _
                       "data source='" & PrmPathExcelFile & " '; " & "Extended Properties=""Excel 8.0;HDR=YES;FMT=Delimited;IMEX=1""")
        '  MyConnection.Open()
        '  Dim strSQLXL As String = "select catName,isnull(catDescription,' ') from [" & strTableName & "$] where Cat_Id = " & CatId
        Dim strSQLXL As String = "select * from [" & strTableName & "$] where Cat_Id = '" & CatId & "'"

        'strSQLXL = "select * from [" & PrmWorksheet & "$]"

        Dim MyCommand As New OleDbDataAdapter(strSQLXL, MyConnection)
        Dim DtSet As New System.Data.DataSet()
        MyCommand.Fill(DtSet)

        Dim dr As DataRow
        For Each dr In DtSet.Tables(0).Rows
            Trace.Warn(dr("catName"))
            lnkCategory.Text = dr("catName")
            '= dr("catName")

            MyTitle = dr("Heading")
            lnkTitle.Text = IIf(IsDBNull(dr("catName")), " ", dr("catName"))
            lnkTitle.NavigateUrl = "Information.aspx?Id=" & CatId
            lnkCategory.NavigateUrl = "./search.aspx?catid=" & CatId
            tdInformation.InnerHtml = IIf(IsDBNull(dr("catDescription")), " ", dr("catDescription"))
            '  Trace.Warn("Des->" & dr("catDescription"))
            Dim strKeys As String = IIf(IsDBNull(dr("Keywords")), "", dr("Keywords"))
            Dim strDescription As String = IIf(IsDBNull(dr("catDescription")), "", dr("catDescription"))
            litMetadata.Text = "<META NAME='KEYWORDS' CONTENT='" & strKeys & "'>"
            litDescription.Text = "<META NAME='Description' CONTENT='" & strDescription & "'>"
        Next

        '''''Dim strsql As String = "select catName,isnull(Description,' ') as Description from categorymaster  where Cat_Id=" & CatId
        '''''If con.State = ConnectionState.Open Then
        '''''    con.Close()
        '''''End If
        '''''con.Open()
        '''''Dim cmd As New SqlCommand(strsql, con)
        '''''Dim mReader As SqlDataReader = cmd.ExecuteReader
        '''''While mReader.Read
        '''''    lnkCategory1.Text = mReader("catName")
        '''''    lnkCategory11.NavigateUrl = "../search/categorysearch.aspx?Id=" & CatId
        '''''    tdInformation11.InnerHtml = mReader("Description")
        '''''    Trace.Warn("Des->" & mReader("Description"))
        '''''End While
        '''''mReader.Close()

    End Sub
    Private Sub LodData(ByVal CatId As String)
        ''''''Dim strsql As String = "select * from categorymaster  where parent_id=" & CatId
        ''''''If con.State = ConnectionState.Open Then
        ''''''    con.Close()
        ''''''End If
        ''''''con.Open()
        ''''''Dim cmd As New SqlCommand(strsql, con)
        ''''''Dim ada As New SqlDataAdapter(cmd)
        ''''''Dim dst As New DataSet()
        ''''''ada.Fill(dst, "Cat")
        ''''''Dim strbld As New StringBuilder()
        ''''''strbld.Append("<table id='Table2' cellspacing='1' cellPadding='1' width='100%' border='0' class='LinkBtn'>")
        ''''''Dim dr As DataRow
        ''''''For Each dr In dst.Tables(0).Rows
        ''''''    Dim i As Integer = Math.Round(dst.Tables(0).Rows.Count / 2)
        ''''''    '    strbld.Append("<tr>")
        ''''''    'Dim j As Integer
        ''''''    'While j < i
        ''''''    strbld.Append("<TR bgcolor='white'>")
        ''''''    strbld.Append("<TD><img src='../Images/left_icon1.jpg'><font class='LinkBtn'><b>")
        ''''''    strbld.Append(dr("CatName"))
        ''''''    strbld.Append("</b></font><br>")
        ''''''    strbld.Append("&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;")
        ''''''    strbld.Append(dr("Description"))
        ''''''    Dim strsql1 As String = "select * from categorymaster  where parent_id=" & dr("cat_id")
        ''''''    'If con.State = ConnectionState.Open Then
        ''''''    '    con.Close()
        ''''''    'End If
        ''''''    'con.Open()
        ''''''    Dim cmd1 As New SqlCommand(strsql1, con)
        ''''''    Dim ada1 As New SqlDataAdapter(cmd1)
        ''''''    Dim dst1 As New DataSet()
        ''''''    ada1.Fill(dst1, "Cat")
        ''''''    Dim dr1 As DataRow
        ''''''    strbld.Append("<table cellspacing='1' cellPadding='1' width='100%' border='0' class='LinkBtn'>")
        ''''''    For Each dr1 In dst1.Tables(0).Rows

        ''''''        '      While (i < 5)
        ''''''        '  strbld.Append("<tr>")
        ''''''        strbld.Append("<td width ='5%'>")
        ''''''        strbld.Append("</td>")
        ''''''        strbld.Append("<td width='95%'>")
        ''''''        strbld.Append("<li><!--<img src='../Images/left_icon1.jpg'>-->" & dr1("CatName"))
        ''''''        strbld.Append("</td>")
        ''''''        '  i = i + 1
        ''''''        '   End While

        ''''''        strbld.Append("</tr>")
        ''''''    Next
        ''''''    strbld.Append("</table>")
        ''''''    strbld.Append("<hr>")
        ''''''    strbld.Append("</TD>")

        ''''''    strbld.Append("</TR>")

        ''''''Next

        ''''''strbld.Append("</table>")
        ''''''tddisp1.InnerHtml = strbld.ToString()
        Dim PrmPathExcelFile As String = Server.MapPath("./Category/categorymaster.xls")
        Dim strTableName = "Categorymaster"
        Dim MyConnection As OleDbConnection

        MyConnection = New System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0; " & _
                                        "data source='" & PrmPathExcelFile & " '; " & "Extended Properties=""Excel 8.0;HDR=YES;FMT=Delimited;IMEX=1""")
        '  MyConnection.Open()

        Dim strSQLXL As String = "select * from [" & strTableName & "$] where parent_id ='" & CatId & "'"
        'strSQLXL = "select * from [" & PrmWorksheet & "$]"

        Dim MyCommand As New OleDbDataAdapter(strSQLXL, MyConnection)
        Dim dst As New System.Data.DataSet()
        MyCommand.Fill(dst)
        Dim strbld As New StringBuilder()
        strbld.Append("<table id='Table2' cellspacing='0' cellPadding='0' width='100%' border='0' class='LinkBtn'>")
        Dim dr As DataRow
        For Each dr In dst.Tables(0).Rows
            Dim i As Integer = Math.Round(dst.Tables(0).Rows.Count / 2)
            '    strbld.Append("<tr>")
            'Dim j As Integer
            'While j < i
            strbld.Append("<TR bgcolor='white'>")
            strbld.Append("<TD><!--<img src='../Images/left_icon1.jpg'>--><font class='LinkBtn1'><b>")
            strbld.Append("<a class='LinkBtn1' href=./search.aspx?catid=" & intCatId)
            'strbld.Append("&SId=" & dr("Cat_Id"))
            strbld.Append(">")
            strbld.Append(dr("CatName"))
            strbld.Append("</a>")
            strbld.Append("</b></font>")
            strbld.Append("&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;")
            strbld.Append("<p align=justify>")

            strbld.Append(IIf(IsDBNull(dr("catDescription")), " ", dr("catDescription")))
            strbld.Append("</p>")
            Dim strsql1 As String = "select * from [" & strTableName & "$] where parent_id = '" & dr("cat_id") & "'"
            'If con.State = ConnectionState.Open Then
            '    con.Close()
            'End If
            'con.Open()
            Dim cmd1 As New OleDbCommand(strsql1, MyConnection)
            Dim ada1 As New OleDbDataAdapter(cmd1)
            Dim dst1 As New DataSet()
            ada1.Fill(dst1, "Cat")
            Dim dr1 As DataRow
            strbld.Append("<table cellspacing='1' cellPadding='1' width='100%' border='0' class='LinkBtn1'>")
            For Each dr1 In dst1.Tables(0).Rows

                '      While (i < 5)
                '  strbld.Append("<tr>")
                strbld.Append("<td width ='5%'>")
                strbld.Append("</td>")
                strbld.Append("<td width='95%'>")
                strbld.Append("<li>")
                '  strbld.Append("<a href=../search/categorysearch.aspx?Id=" & intCatId)
                ' strbld.Append("&SId=" & dr("Cat_Id"))
                '' strbld.Append("&SSId=" & dr1("Cat_Id"))
                'strbld.Append(">")
                strbld.Append("<!--<img src='../Images/left_icon1.jpg'>-->" & dr1("CatName"))
                ' strbld.Append("<br>")
                'strbld.Append("</a")

                strbld.Append("</td>")
                '  i = i + 1
                '   End While

                strbld.Append("</tr>")
            Next
            strbld.Append("</table>")
            strbld.Append("<hr>")
            strbld.Append("</TD>")

            strbld.Append("</TR>")

        Next

        strbld.Append("</table>")
        tdDisp.InnerHtml = strbld.ToString()
    End Sub
End Class