Option Strict On
Option Explicit On 

Imports System.Data
Imports System.Data.SqlClient

Public Class PaymentManager
    Inherits System.Web.UI.Page
    Protected WithEvents Conn As New SqlConnection(ConfigurationSettings.AppSettings("sConnection"))
    Protected WithEvents Label1 As System.Web.UI.WebControls.Label
    Protected WithEvents Label2 As System.Web.UI.WebControls.Label
    Protected WithEvents Label3 As System.Web.UI.WebControls.Label
    Protected WithEvents Label4 As System.Web.UI.WebControls.Label
    Protected WithEvents GradeDDL1 As System.Web.UI.WebControls.ListBox
    Protected WithEvents FeeTB1 As System.Web.UI.WebControls.TextBox
    Protected WithEvents UpdateBtn1 As System.Web.UI.WebControls.Button
    Protected WithEvents DownloadBtn1 As System.Web.UI.WebControls.Button
    Protected WithEvents DownloadBtn2 As System.Web.UI.WebControls.Button
    Protected WithEvents DownloadBtn4 As System.Web.UI.WebControls.Button
    Protected WithEvents DownloadBtn3 As System.Web.UI.WebControls.Button
    Protected WithEvents SportDDL1 As System.Web.UI.WebControls.DropDownList
    Protected WithEvents EmailsLabel1 As System.Web.UI.HtmlControls.HtmlGenericControl

#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

    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        If Conn.State = ConnectionState.Closed Then
            Conn.ConnectionString = ConfigurationSettings.AppSettings("sConnection")
        End If

        Dim cmd As New SqlCommand

        '<Sohaib Akram - Dec 2, 2011 - Start>

        If Not Page.IsPostBack Then

            Dim DS As New DataSet
            Dim mySqlDataAdapter As New SqlDataAdapter

            With cmd
                .CommandText = "Select * from as_DropDownLists"
                .CommandType = CommandType.Text
                If Conn.State = ConnectionState.Closed Then Conn.Open()
                .Connection = Conn

            End With

            mySqlDataAdapter.SelectCommand = cmd
            mySqlDataAdapter.Fill(DS)

            If (DS.Tables(0).Rows.Count > 0) Then

                'Dim sport() As String
                'Dim grade() As String

                'sport = Split(CType(DS.Tables(0).Rows(0)(1), String), ",")
                'grade = Split(CType(DS.Tables(0).Rows(0)(3), String), ",")

                'For i As Integer = 0 To sport.Length - 1
                    'Dim li As New ListItem
                    'li.Value = sport(i)
                    'li.Text = sport(i)
                    'SportDDL1.Items.Add(li)
                'Next

                'Dim li1 As New ListItem
                'li1.Value = "all"
                'li1.Text = "All"
                'li1.Selected = True
                'GradeDDL1.Items.Add(li1)

                'For i As Integer = 0 To grade.Length - 1
                    'Dim li As New ListItem
                    'li.Value = grade(i)
                    'li.Text = grade(i)
                    'GradeDDL1.Items.Add(li)
                'Next

            End If

            'Sports 
            Dim DS1 As New DataSet
            Dim mySqlDataAdapter1 As New SqlDataAdapter

            With cmd
                .CommandText = "SELECT Sport FROM as_Sports ORDER BY Sport"
                .CommandType = CommandType.Text
                If Conn.State = ConnectionState.Closed Then Conn.Open()
                .Connection = Conn

            End With

            mySqlDataAdapter1.SelectCommand = cmd
            mySqlDataAdapter1.Fill(DS1)

            If (DS1.Tables(0).Rows.Count > 0) Then

                'sport = Split(CType(DS.Tables(0).Rows(0)(1), String), ",")
                For i As Integer = 0 To DS1.Tables(0).Rows.Count - 1
                    If Not (IsDBNull(DS1.Tables(0).Rows(i)(0))) Then
                        Dim li As New ListItem
                        li.Value = CType(DS1.Tables(0).Rows(i)(0), String)
                        li.Text = CType(DS1.Tables(0).Rows(i)(0), String)
                        SportDDL1.Items.Add(li)
                    End If
                Next

            End If

            'Grade 

            Dim li1 As New ListItem
            li1.Value = "all"
            li1.Text = "All"
            li1.Selected = True
            GradeDDL1.Items.Add(li1)

            Dim DS2 As New DataSet
            Dim mySqlDataAdapter2 As New SqlDataAdapter

            With cmd
                .CommandText = "SELECT Grade FROM as_Grade ORDER BY Grade"
                .CommandType = CommandType.Text
                If Conn.State = ConnectionState.Closed Then Conn.Open()
                .Connection = Conn

            End With

            mySqlDataAdapter2.SelectCommand = cmd
            mySqlDataAdapter2.Fill(DS2)

            If (DS2.Tables(0).Rows.Count > 0) Then

                For i As Integer = 0 To DS2.Tables(0).Rows.Count - 1
                    If Not (IsDBNull(DS2.Tables(0).Rows(i)(0))) Then

                        Dim li As New ListItem
                        li.Value = CType(DS2.Tables(0).Rows(i)(0), String)
                        li.Text = CType(DS2.Tables(0).Rows(i)(0), String)
                        GradeDDL1.Items.Add(li)

                    End If
                Next

            End If

        End If
        '<Sohaib Akram - Dec 2, 2011 - End>

        If Conn.State = ConnectionState.Open Then Conn.Close()

    End Sub

    Private Sub Page_Unload(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Unload
        If Conn.State = ConnectionState.Open Then Conn.Close()
    End Sub

    '<Sohaib Akram - Dec 2, 2011 - Start>

    Private Sub SetEmailTextBox(ByVal formType As String, ByVal sport As String, ByVal gradeLB As ListBox, ByVal txtBox As TextBox)

        If Conn.State = ConnectionState.Closed Then
            Conn.ConnectionString = ConfigurationSettings.AppSettings("sConnection")
        End If

        Dim grade As String = ""
        Dim flag As Integer = 0

        For i As Integer = 2 To gradeLB.Items.Count - 1
            If Not (gradeLB.Items(i).Text.Equals("Select Grade")) Then
                If (gradeLB.Items(i).Selected = True) Then
                    flag = flag + 1
                End If
            End If
        Next

        If gradeLB.Items(1).Selected = True Then
            flag = 0
            EmailsLabel1.InnerText = "Please select only one grade."
        Else
            If flag > 1 Then
                flag = 0
                EmailsLabel1.InnerText = "Please select only one grade."
            End If
        End If

        txtBox.Text = ""

        If flag = 1 Then
            EmailsLabel1.InnerText = ""
            If Not (gradeLB.Items(1).Selected = True) Then
                For i As Integer = 2 To gradeLB.Items.Count - 1
                    If Not (gradeLB.Items(i).Text.Equals("Select Grade")) Then
                        If (gradeLB.Items(i).Selected = True) Then
                            Dim cmd As New SqlCommand
                            With cmd
                                .CommandText = "Select Fee from as_FormFee where sport='" & sport & "' and grade='" & gradeLB.Items(i).Text & "'"
                                .CommandType = CommandType.Text
                                If Conn.State = ConnectionState.Closed Then Conn.Open()
                                .Connection = Conn
                                grade = CType(.ExecuteScalar(), String)
                            End With
                        End If
                    End If
                Next
            End If

            txtBox.Text = grade
        End If

        If Conn.State = ConnectionState.Open Then Conn.Close()

    End Sub


    Private Sub SportDDL1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SportDDL1.SelectedIndexChanged

        If GradeDDL1.SelectedIndex > 0 Then
            If Not SportDDL1.SelectedValue.Equals("Select Sport") Then
                Call SetEmailTextBox("Athletic", SportDDL1.SelectedValue, GradeDDL1, FeeTB1)
            Else
                FeeTB1.Text = ""
                EmailsLabel1.InnerText = "Please select sport."
            End If
        Else
            FeeTB1.Text = ""
            EmailsLabel1.InnerText = "Please select grade."
        End If

    End Sub


    Private Sub UpdateBtn1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles UpdateBtn1.Click

        If Conn.State = ConnectionState.Closed Then
            Conn.ConnectionString = ConfigurationSettings.AppSettings("sConnection")
        End If

        If Not SportDDL1.SelectedValue = "Select Sport" Then
            Dim grade As String = ""
            If Not (GradeDDL1.Items(1).Selected = True) Then
                For i As Integer = 0 To GradeDDL1.Items.Count - 1
                    If Not (GradeDDL1.Items(i).Text.Equals("Select Grade")) Then
                        EmailsLabel1.InnerText = ""
                        If (GradeDDL1.Items(i).Selected = True) Then

                            grade = GradeDDL1.Items(i).Text
                            Dim cmd As New SqlCommand
                            With cmd

                                Dim cmd2 As New SqlCommand
                                Dim value As Integer = 0

                                cmd2.CommandText = "select ID from as_FormFee where sport='" & SportDDL1.SelectedValue & "' and grade='" & grade & "'"
                                cmd2.CommandType = CommandType.Text
                                If Conn.State = ConnectionState.Closed Then Conn.Open()
                                cmd2.Connection = Conn
                                value = CType(cmd2.ExecuteScalar(), Integer)

                                If Not (value = 0) Then
                                    If (FeeTB1.Text.Equals("")) Then
                                        .CommandText = "delete from as_FormFee where sport='" & SportDDL1.SelectedValue & "' and grade='" & grade & "'"
                                    Else
                                        .CommandText = "update as_FormFee set Fee = '" & FeeTB1.Text & "' where sport='" & SportDDL1.SelectedValue & "' and grade='" & grade & "'"
                                    End If
                                Else
                                    .CommandText = "insert into as_FormFee values('" & SportDDL1.SelectedValue & "','" & grade & "','" & FeeTB1.Text.Trim().Replace("'", "''") & "')"
                                End If

                                .CommandType = CommandType.Text
                                If Conn.State = ConnectionState.Closed Then Conn.Open()
                                .Connection = Conn
                                .ExecuteNonQuery()

                            End With
                        End If
                    Else
                        EmailsLabel1.InnerText = "Please select grade first."
                    End If
                Next
            End If
        Else
            EmailsLabel1.InnerText = "Please select sport."
        End If
        If Conn.State = ConnectionState.Open Then Conn.Close()

    End Sub
End Class

'<Sohaib Akram - Dec 2, 2011 - End>