mports System.Data
Imports System.Data.SqlClient

Partial Class News_Listing
    Inherits System.Web.UI.UserControl

    Dim cmd As SqlCommand
    Public CurrentPageNumber As Integer = 1
    Dim totalpages As Double = 1
    Dim totalRecords As Integer = 0

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Dim ID As New Sitecore.Xml.Xsl.XslHelper
        Dim name As String
        name = ID.qs("Category")


        If Page.IsPostBack = False Then
            ' MsgBox("is true")


            If name <> "" Then
                Dim Category As String = name
                Category = Replace(Category, "-", " ")

                ' DDL_Year.DataSource = loaditems("Year")

                If Request.QueryString("Year") <> "" Then
                    DDL_Year.Text = Request.QueryString("Year")
                End If
                ' DDL_Year.DataBind()

                If Request.QueryString("Month") <> "" Then
                    DDL_Month.Text = Request.QueryString("Month")
                End If

                Dim dt As DataTable
                Dim totalRecords As Integer

                If Request.QueryString("Month") = "" Then
                    dt = BindGvData(CurrentPageNumber, 6, GetCategoryName(Category), 0, 0, totalRecords)
                Else
                    dt = BindGvData(CurrentPageNumber, 6, GetCategoryName(Category), CInt(DDL_Year.SelectedValue), DDL_Month.SelectedValue, totalRecords)
                End If

                Dim i As Integer = totalRecords
                GVListing.DataSource = dt
                GVListing.DataBind()

                Dim totalpages As Double
                totalpages = totalRecords / 6
                totalpages = System.Math.Ceiling(totalpages)
                totalpages = Double.Parse(totalpages)
                Total.Text = totalpages.ToString
                Current.Text = CurrentPageNumber.ToString

                If CInt(Total.Text) >= 1 Then
                    FirstPage.Enabled = True
                    PreviousPage.Enabled = True
                    NextPage.Enabled = True
                    LastPage.Enabled = True

                    If Current.Text <= 1 Then
                        FirstPage.Enabled = False
                        PreviousPage.Enabled = False
                    End If

                ElseIf Total.Text <= 1 Then
                    FirstPage.Enabled = False
                    PreviousPage.Enabled = False
                    NextPage.Enabled = False
                    LastPage.Enabled = True

                End If
                NewsArchivePanel.Visible = True ' make visible if query string contains category

            Else
                NewsArchivePanel.Visible = False
            End If
        Else
            'Dim oldurl As String = Request.ServerVariables("URL") & "?" & Request.ServerVariables("QUERY_STRING")
            'Dim ID As New Sitecore.Xml.Xsl.XslHelper
            ' Dim curl As Uri = Request.Url

            Dim iMonth As String = Request.QueryString("Month") ' ID.qs("Month")


        End If

    End Sub
Public Function BindGvData(ByVal iPage As Integer, ByVal iRecordsPerPage As Integer, ByVal iCategoryName As String, ByVal Year As Integer, ByVal Month As Integer, ByRef TotalRecords As Integer) As DataTable
        iCategoryName = GetCategoryName(iCategoryName)

        Dim con As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
        cmd = New SqlCommand("testNews.dbo.usp_NewsArchivePaging", con)
        cmd.CommandType = CommandType.StoredProcedure

        cmd.Parameters.Add(New SqlParameter("@iPage", SqlDbType.SmallInt)).Value = iPage
        cmd.Parameters.Add(New SqlParameter("@iRecsPerPage", SqlDbType.TinyInt)).Value = iRecordsPerPage 'Set the GVPaging size here
        cmd.Parameters.Add(New SqlParameter("@iCategoryName", SqlDbType.VarChar)).Value = iCategoryName
        cmd.Parameters.Add(New SqlParameter("@iYear", SqlDbType.Int)).Value = Year
        cmd.Parameters.Add(New SqlParameter("@iMonth", SqlDbType.Int)).Value = Month

        cmd.Parameters.Add(New SqlParameter("@iNoRecordsReturned", SqlDbType.Int)).Direction = ParameterDirection.Output
        cmd.Parameters.Add(New SqlParameter("@iMoreRecords", SqlDbType.Int)).Direction = ParameterDirection.Output
        cmd.Parameters.Add(New SqlParameter("@iCountAllNewsInCategory", SqlDbType.Int)).Direction = ParameterDirection.Output

        con.Open()

        Dim dr As SqlDataReader = cmd.ExecuteReader()
        Dim dt As New DataTable()
        dt.Load(dr)
        dr.Close()
        con.Close()

        TotalRecords = CType(cmd.Parameters("@iCountAllNewsInCategory").Value, Integer)
        cmd.Parameters.Clear()
        Return dt

    End Function
Public Sub bind()
        '  Dim totalRecords As Integer '= 0

        Dim al As DataTable
        Dim ID As New Sitecore.Xml.Xsl.XslHelper
        Dim name As String = ID.qs("Category")
        Dim Category As String = name

        If Request.QueryString("Month") = "" Then

            Category = Replace(Category, "-", " ")
            al = BindGvData(CurrentPageNumber, 6, GetCategoryName(Category), DDL_Year.SelectedValue, DDL_Month.SelectedValue, totalRecords)

        Else
            al = BindGvData(CurrentPageNumber, 6, GetCategoryName(Category), DDL_Year.SelectedValue, 0, totalRecords)


        End If


        Dim i As Integer = totalRecords

        GVListing.DataSource = al
        DataBind()
        Dim totalpages As Double
        If Page.IsPostBack Then
            totalpages = totalRecords / 6
            totalpages = System.Math.Ceiling(totalpages)
        Else
            totalpages = Double.Parse(totalpages)
        End If
        Total.Text = totalpages.ToString
        Current.Text = CurrentPageNumber.ToString
        ' i took the ShowCategories() from here
    End Sub

    Public Sub UpdateURL()

        Dim ID As New Sitecore.Xml.Xsl.XslHelper
        Dim name As String = ID.qs("Category")
        Dim Category As String = name

        Dim oldurl As String = Request.ServerVariables("URL") & "?" & Request.ServerVariables("QUERY_STRING")
        'Dim s(30) As String
        's = oldurl.Split("/")
        Dim Year As String = DDL_Year.SelectedItem.Text
        Dim Month As String = DDL_Month.SelectedItem.Text
        'Dim newurl As String = "http://localhost" & oldurl & "&Month=" + DDL_Month.SelectedItem.Text & "&Year=" & DDL_Year.SelectedItem.Text
        Dim newurl As String = "http://localhost:3920/test_NewsArchive/default.aspx?Category=" & name & "&Year=" & Year & "&Month=" & Month

        Response.Redirect(newurl)
    End Sub



    Protected Sub Bt_Go_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Bt_Go.Click
        ' bind()
        UpdateURL()
    End Sub
End Class