Option Strict On
Option Explicit On 

Imports System.Data
Imports System.Data.SqlClient

Public Class FormsManager
	Inherits System.Web.UI.Page
	Protected WithEvents lblTotalCensus As System.Web.UI.WebControls.Label
	Protected WithEvents Conn As New SqlConnection(ConfigurationSettings.AppSettings("sConnection"))
	Protected WithEvents lblTotalAlumnus As System.Web.UI.WebControls.Label
	Protected WithEvents lblTotalPicnic As System.Web.UI.WebControls.Label
    Protected WithEvents lblServey As System.Web.UI.WebControls.Label
    Protected WithEvents lblParentVolunteer As System.Web.UI.WebControls.Label
    Protected WithEvents coaches As System.Web.UI.WebControls.Label
    Protected WithEvents CYO As System.Web.UI.WebControls.Label
    Protected WithEvents SportsEval As System.Web.UI.WebControls.Label
    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 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 FormEmails1 As AllSaints.FormEmails
    Protected oES As Encryption64 = New Encryption64
    Protected WithEvents CoachLink As System.Web.UI.HtmlControls.HtmlAnchor
    Protected WithEvents CYOLink As System.Web.UI.HtmlControls.HtmlAnchor
    Protected WithEvents EvaluationLink As System.Web.UI.HtmlControls.HtmlAnchor
    Protected WithEvents Label4 As System.Web.UI.WebControls.Label
    Protected WithEvents FatherNameTB As System.Web.UI.WebControls.TextBox
    Protected WithEvents LandingPageTextTB As System.Web.UI.WebControls.TextBox
    Protected WithEvents LandingPageBtn As System.Web.UI.WebControls.Button
    Protected WithEvents Label5 As System.Web.UI.WebControls.Label
    Protected WithEvents ChurchFNameTB As System.Web.UI.WebControls.TextBox
    Protected WithEvents ChurchTextTB As System.Web.UI.WebControls.TextBox
    Protected WithEvents ChurchBtn As System.Web.UI.WebControls.Button
    Protected WithEvents Label6 As System.Web.UI.WebControls.Label
    Protected WithEvents SchoolPNameTB As System.Web.UI.WebControls.TextBox
    Protected WithEvents SchoolTextTB As System.Web.UI.WebControls.TextBox
    Protected WithEvents SchoolBtn As System.Web.UI.WebControls.Button
    Protected WithEvents PRegFormLink As System.Web.UI.HtmlControls.HtmlAnchor
    Protected WithEvents SportsPageLink As System.Web.UI.HtmlControls.HtmlAnchor
    Protected key As String = "!#$a54?3"

#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()
		With cmd
			.CommandText = "Select Count(*) from as_Census"
			.CommandType = CommandType.Text
			If Conn.State = ConnectionState.Closed Then Conn.Open()
			.Connection = Conn
			lblTotalCensus.Text = CType(.ExecuteScalar(), String)
		End With

		With cmd
			.CommandText = "Select Count(*) from as_AlumniForm"
			.CommandType = CommandType.Text
			If Conn.State = ConnectionState.Closed Then Conn.Open()
			.Connection = Conn
			lblTotalAlumnus.Text = CType(.ExecuteScalar(), String)
		End With

		With cmd
			.CommandText = "Select Count(*) from as_PicnicForm"
			.CommandType = CommandType.Text
			If Conn.State = ConnectionState.Closed Then Conn.Open()
			.Connection = Conn
			lblTotalPicnic.Text = CType(.ExecuteScalar(), String)
        End With

        With cmd
            .CommandText = "Select Count(*) from as_Technology_Servey"
            .CommandType = CommandType.Text
            If Conn.State = ConnectionState.Closed Then Conn.Open()
            .Connection = Conn
            lblServey.Text = CType(.ExecuteScalar(), String)
        End With

        With cmd
            .CommandText = "Select Count(*) from as_Parent_Volunteer"
            .CommandType = CommandType.Text
            If Conn.State = ConnectionState.Closed Then Conn.Open()
            .Connection = Conn
            lblParentVolunteer.Text = CType(.ExecuteScalar(), String)
        End With

        ' Coach Form
        With cmd
            .CommandText = "Select Count(*) from as_coachform"
            .CommandType = CommandType.Text
            If Conn.State = ConnectionState.Closed Then Conn.Open()
            .Connection = Conn
            coaches.Text = CType(.ExecuteScalar(), String)
        End With

        ' CYO Release Form
        With cmd
            .CommandText = "Select Count(*) from as_CYOLiabilityForm"
            .CommandType = CommandType.Text
            If Conn.State = ConnectionState.Closed Then Conn.Open()
            .Connection = Conn
            CYO.Text = CType(.ExecuteScalar(), String)
        End With

        ' Sports Evaluation Form
        With cmd
            .CommandText = "Select count( distinct formid) from as_SportsEvaluationForm"
            .CommandType = CommandType.Text
            If Conn.State = ConnectionState.Closed Then Conn.Open()
            .Connection = Conn
            SportsEval.Text = CType(.ExecuteScalar(), String)
        End With

        If Not IsPostBack Then

            ' Allsaints Landing Page - Father Name
            With cmd
                .CommandText = "SELECT HomepageFatherName FROM as_PageText"
                .CommandType = CommandType.Text
                If Conn.State = ConnectionState.Closed Then Conn.Open()
                .Connection = Conn
                FatherNameTB.Text = CType(.ExecuteScalar(), String)
            End With
            ' Allsaints Landing Page - Text
            With cmd
                .CommandText = "SELECT HomepageText FROM as_PageText"
                .CommandType = CommandType.Text
                If Conn.State = ConnectionState.Closed Then Conn.Open()
                .Connection = Conn
                LandingPageTextTB.Text = CType(.ExecuteScalar(), String)
            End With

            ' Allsaints Church Page - Father Name
            With cmd
                .CommandText = "SELECT ChurchFatherName FROM as_PageText"
                .CommandType = CommandType.Text
                If Conn.State = ConnectionState.Closed Then Conn.Open()
                .Connection = Conn
                ChurchFNameTB.Text = CType(.ExecuteScalar(), String)
            End With
            ' Allsaints Church Page - Text
            With cmd
                .CommandText = "SELECT ChurchText FROM as_PageText"
                .CommandType = CommandType.Text
                If Conn.State = ConnectionState.Closed Then Conn.Open()
                .Connection = Conn
                ChurchTextTB.Text = CType(.ExecuteScalar(), String)
            End With

            ' Allsaints School Page - Principal Name
            With cmd
                .CommandText = "SELECT SchoolPrincipal FROM as_PageText"
                .CommandType = CommandType.Text
                If Conn.State = ConnectionState.Closed Then Conn.Open()
                .Connection = Conn
                SchoolPNameTB.Text = CType(.ExecuteScalar(), String)
            End With
            ' Allsaints School Page - Text
            With cmd
                .CommandText = "SELECT SchoolText FROM as_PageText"
                .CommandType = CommandType.Text
                If Conn.State = ConnectionState.Closed Then Conn.Open()
                .Connection = Conn
                SchoolTextTB.Text = CType(.ExecuteScalar(), String)
            End With

        End If

        'Coaching Form
        CoachLink.HRef = "http://sms.allsaints.cc/Default.aspx?id=" & Server.UrlEncode("allsaintsAdmin") & "&form=coach"
        'CYO Form
        CYOLink.HRef = "http://sms.allsaints.cc/Default.aspx?id=" & Server.UrlEncode("allsaintsAdmin") & "&form=athletic"
        'Evaluation Form
        EvaluationLink.HRef = "http://sms.allsaints.cc/Default.aspx?id=" & Server.UrlEncode("allsaintsAdmin") & "&form=evaluation"
        'Pre-Registration Form
        PRegFormLink.HRef = "http://sms.allsaints.cc/Default.aspx?id=" & Server.UrlEncode("allsaintsAdmin") & "&form=pre-register"
        'Sport Management 
        SportsPageLink.HRef = "http://sms.allsaints.cc/Default.aspx?id=" & Server.UrlEncode("allsaintsAdmin") & "&form=sports"

        '<SAQIB ADDED LINES>
        If Conn.State = ConnectionState.Open Then Conn.Close()
        '<SAQIB ADDED LINES/>


	End Sub

    Private Sub Page_Unload(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Unload
        '<SAQIB ADDED LINES>
        If Conn.State = ConnectionState.Open Then Conn.Close()
        '<SAQIB ADDED LINES/>
    End Sub

    '<Sohaib Akram - Aug 13, 2011 - Start>
    

    Private Sub SetDownloadLinkGrade(ByVal formType As String, ByVal sport As String, ByVal grade As String, ByVal label As Label)

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

        Dim cmd As New SqlCommand
        With cmd
            If formType.Equals("Coaching") Then
                .CommandText = "Select count(ID) from as_CoachForm where sport='" & sport & "' and gradeddl='" & grade & "'"
            ElseIf formType.Equals("Athletic") Then
                .CommandText = "Select count(ID) from as_CYOLiabilityForm where sport='" & sport & "' and grade='" & grade & "'"
            End If

            .CommandType = CommandType.Text
            If Conn.State = ConnectionState.Closed Then Conn.Open()
            .Connection = Conn
            label.Text = CType(.ExecuteScalar(), String)
        End With

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

    End Sub

    Private Sub SetDownloadLinkSeason(ByVal formType As String, ByVal sport As String, ByVal season As String, ByVal label As Label)

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

        Dim cmd As New SqlCommand
        With cmd
            If formType.Equals("Coaching") Then
                .CommandText = "Select count(ID) from as_CoachForm where sport='" & sport & "' and seasonddl='" & season & "'"
            ElseIf formType.Equals("Athletic") Then
                .CommandText = "Select count(ID) from as_CYOLiabilityForm where sport='" & sport & "' and season='" & season & "'"
            End If

            .CommandType = CommandType.Text
            If Conn.State = ConnectionState.Closed Then Conn.Open()
            .Connection = Conn
            label.Text = CType(.ExecuteScalar(), String)
        End With

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

    End Sub

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

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

        Dim cmd As New SqlCommand
        With cmd
            .CommandText = "Select Emails from as_FormEmails where formtype='" & formType & "' and sport='" & sport & "' and grade='" & grade & "'"
            .CommandType = CommandType.Text
            If Conn.State = ConnectionState.Closed Then Conn.Open()
            .Connection = Conn
            txtBox.Text = CType(.ExecuteScalar(), String)
        End With

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

    End Sub

    Private Sub SetEmailTextBox2(ByVal formType As String, ByVal sport As String, ByVal season As String, ByVal txtBox As TextBox)

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

        Dim cmd As New SqlCommand
        With cmd
            .CommandText = "Select Emails from as_FormEmails where formtype='" & formType & "' and sport='" & sport & "' and season='" & season & "'"
            .CommandType = CommandType.Text
            If Conn.State = ConnectionState.Closed Then Conn.Open()
            .Connection = Conn
            txtBox.Text = CType(.ExecuteScalar(), String)
        End With

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

    End Sub


    Private Sub LandingPageBtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles LandingPageBtn.Click

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

        Dim cmd As New SqlCommand

        ' Allsaints Landing Page - Father Name
        With cmd
            .CommandText = "UPDATE as_PageText SET HomepageFatherName = '" & HttpUtility.HtmlEncode(FatherNameTB.Text.Trim().Replace("'", "''")) & "'"
            .CommandType = CommandType.Text
            If Conn.State = ConnectionState.Closed Then Conn.Open()
            .Connection = Conn
            .ExecuteNonQuery()
        End With

        ' Allsaints Landing Page - Text
        With cmd
            .CommandText = "UPDATE as_PageText SET HomepageText = '" & HttpUtility.HtmlEncode(LandingPageTextTB.Text.Trim().Replace("'", "''")) & "'"
            .CommandType = CommandType.Text
            If Conn.State = ConnectionState.Closed Then Conn.Open()
            .Connection = Conn
            .ExecuteNonQuery()
        End With

    End Sub

    Private Sub ChurchBtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ChurchBtn.Click

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

        Dim cmd As New SqlCommand

        ' Allsaints Church Page - Father Name
        With cmd
            .CommandText = "UPDATE as_PageText SET ChurchFatherName = '" & HttpUtility.HtmlEncode(ChurchFNameTB.Text.Trim().Replace("'", "''")) & "'"
            .CommandType = CommandType.Text
            If Conn.State = ConnectionState.Closed Then Conn.Open()
            .Connection = Conn
            .ExecuteNonQuery()
        End With

        ' Allsaints Church Page - Text
        With cmd
            .CommandText = "UPDATE as_PageText SET ChurchText = '" & HttpUtility.HtmlEncode(ChurchTextTB.Text.Trim().Replace("'", "''")) & "'"
            .CommandType = CommandType.Text
            If Conn.State = ConnectionState.Closed Then Conn.Open()
            .Connection = Conn
            .ExecuteNonQuery()
        End With

    End Sub

    Private Sub SchoolBtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SchoolBtn.Click

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

        Dim cmd As New SqlCommand

        ' Allsaints School Page - Principal
        With cmd
            .CommandText = "UPDATE as_PageText SET SchoolPrincipal = '" & HttpUtility.HtmlEncode(SchoolPNameTB.Text.Trim().Replace("'", "''")) & "'"
            .CommandType = CommandType.Text
            If Conn.State = ConnectionState.Closed Then Conn.Open()
            .Connection = Conn
            .ExecuteNonQuery()
        End With

        ' Allsaints School Page - Text
        With cmd
            .CommandText = "UPDATE as_PageText SET SchoolText = '" & HttpUtility.HtmlEncode(SchoolTextTB.Text.Trim().Replace("'", "''")) & "'"
            .CommandType = CommandType.Text
            If Conn.State = ConnectionState.Closed Then Conn.Open()
            .Connection = Conn
            .ExecuteNonQuery()
        End With

    End Sub
End Class
'<Sohaib Akram - Aug 16, 2011 - End>