Imports System.Data.SqlClient
Public Class login
    Inherits System.Web.UI.Page
    Dim SqlConn As SqlConnection
    Dim SqlCommand As SqlCommand
    Dim SqlDataReader As SqlDataReader
    Dim SqlQuery As String
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Try
            Response.Cache.SetExpires(DateTime.UtcNow.AddMinutes(-1))
            Response.Cache.SetCacheability(HttpCacheability.NoCache)
            Response.Cache.SetNoStore()



            SqlConn = New SqlConnection(Session("Connection"))
            If Not Me.IsPostBack Then
                Using cmd As New SqlCommand("SELECT MUD_NAME, MUD_ID FROM [Governorate] ORDER BY [MUD_NAME]")
                    cmd.CommandType = CommandType.Text
                    cmd.Connection = SqlConn
                    SqlConn.Open()
                    CobMudCode.DataSource = cmd.ExecuteReader()
                    CobMudCode.DataTextField = "MUD_NAME"
                    CobMudCode.DataValueField = "MUD_ID"
                    CobMudCode.DataBind()
                    SqlConn.Close()
                End Using

                CobMudCode.Items.Insert(0, New ListItem("أختر المحافظة", "0"))
                CobMudCode.SelectedIndex = 0
                CobIdaraCode.Items.Insert(0, New ListItem("أختر الاداره", "0"))
                CobIdaraCode.SelectedIndex = 0
                CobSchoolCode.Items.Insert(0, New ListItem("أختر المدرسة", "0"))
                CobSchoolCode.SelectedIndex = 0

            End If
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

    End Sub

    Protected Sub BtnLogin_Click(ByVal sender As Object, ByVal e As EventArgs) Handles BtnLogin.Click

        If TxtUserNameLogin.Text = "" And TxtPasswordLogin.Text = "" Then
            LblLoginError.Text = "أدخل اسم المستخدم و كلمة السر"
            LblLoginError.Visible = True
            Exit Sub
        ElseIf TxtUserNameLogin.Text = "" Then
            LblLoginError.Text = "أدخل اسم المستخدم"
            LblLoginError.Visible = True
            Exit Sub
        ElseIf TxtPasswordLogin.Text = "" Then
            LblLoginError.Text = "أدخل كلمة السر"
            LblLoginError.Visible = True
            Exit Sub
        Else
            LblLoginError.Text = Nothing
            LblLoginError.Visible = False
        End If
        SqlConn = New SqlConnection(Session("Connection"))
        SqlConn.Open()
        SqlQuery = "SELECT UserName, Password, StudentName, StudentCode FROM StudentID WHERE (UserName ='" & TxtUserNameLogin.Text.ToString & "' and Password='" & TxtPasswordLogin.Text.ToString & "')"
        SqlCommand = New SqlCommand()
        SqlCommand.Connection = SqlConn
        SqlCommand.CommandText = SqlQuery
        'Dim result As Integer = SqlCommand.ExecuteNonQuery()
        SqlDataReader = SqlCommand.ExecuteReader()

        If SqlDataReader.HasRows Then
            SqlDataReader.Read()
            Session("StudentName") = SqlDataReader("StudentName")
            Session("UserName") = SqlDataReader("UserName")
            Session("Password") = SqlDataReader("Password")
            Session("StudentCode") = SqlDataReader("StudentCode")
        Else
            LblLoginError.Text = " اسم المستخدم أو كلمة السر خطأ، أعد المحاوله"
            TxtUserNameLogin.Text = ""
            TxtPasswordLogin.Text = ""
            LblLoginError.Visible = True
            Exit Sub
        End If
        SqlDataReader.Close()
        SqlDataReader = Nothing
        SqlCommand.Cancel()
        SqlCommand.Dispose()
        SqlConn.Close()
        Response.Redirect("mainpage.aspx")
    End Sub

    Private Sub TxtUserNameLogin_Init(ByVal sender As Object, ByVal e As System.EventArgs) Handles TxtUserNameLogin.Init
        If TxtUserNameLogin.Text <> "" Then
            LblLoginError.Text = Nothing
            LblLoginError.Visible = False
        End If
    End Sub

    Protected Sub TxtUserNameLogin_TextChanged(ByVal sender As Object, ByVal e As EventArgs) Handles TxtUserNameLogin.TextChanged
        If TxtUserNameLogin.Text <> "" Then
            LblLoginError.Text = Nothing
            LblLoginError.Visible = False
        End If
    End Sub

    Private Sub TxtPasswordLogin_TextChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles TxtPasswordLogin.TextChanged
        If TxtPasswordLogin.Text <> "" Then
            LblLoginError.Text = Nothing
            LblLoginError.Visible = False
        End If
    End Sub

    Private Sub Submit2_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Submit2.Click
        SqlConn = New SqlConnection(Session("Connection"))
        SqlConn.Open()
        SqlQuery = "SELECT UserName, Password, StudentName FROM StudentID WHERE (UserName ='" & TxtUsername.Text.ToString & "' and Password='" & TxtPassword.Text.ToString & "')"
        SqlCommand = New SqlCommand()
        SqlCommand.Connection = SqlConn
        SqlCommand.CommandText = SqlQuery
        SqlDataReader = SqlCommand.ExecuteReader()
        If SqlDataReader.HasRows Then
            SqlDataReader.Read()

            LblUser.Text = " تم تسجيل اسم المستخدم هذا من قبل، أدخل أسم مستخدم أخر"
            TxtUsername.Text = ""
            TxtPassword.Text = ""
            LblUser.Visible = True
            SqlDataReader.Close()
            SqlDataReader = Nothing
        Else
            SqlDataReader.Close()
            SqlDataReader = Nothing
            'Session("MudCode") = CobMudCode.SelectedItem.Value
            'Session("MudName") = CobMudCode.SelectedItem.Text.ToString
            'Session("IdaraCode") = CobIdaraCode.SelectedItem.Value
            'Session("IdaraName") = CobIdaraCode.SelectedItem.Text.ToString
            'Session("SchoolCode") = CobSchoolCode.SelectedItem.Value
            'Session("SchoolName") = CobSchoolCode.SelectedItem.Text.ToString
            ''Session("StudentCode") = ""
            'Session("StudentName") = TxtName.Text.ToString
            Dim IntSpecialEdu As Integer = RbtSpecialEdu.SelectedValue
            'MsgBox(Session("SpecialEdu"))
            'Session("ArabicDegree") = TxtArabicDegree.Text
            'Session("MathDegree") = TxtMathDegree.Text
            'Session("SciDegree") = TxtSciDegree.Text
            'Session("EngDegree") = TxtEngDegree.Text
            'Session("SocialDegree") = TxtSciDegree.Text
            'Session("DiffMath") = TxtDiffMath.Text
            'Session("DiffSci") = TxtDiffSci.Text
            'Session("DiffArabic") = TxtDiffArabic.Text
            'Session("WorkLifeMath") = TxtWorkLifeMath.Text
            'Session("WorkLifeSci") = TxtWorkLifeSci.Text
            'Session("WorkLifeArabic") = TxtWorkLifeArabic.Text
            'Session("UserName") = TxtUserName.Text.ToString
            'Session("Password") = TxtPassword.Text.ToString
            'Session("Email") = TxtEMail.Text.ToString
            SqlQuery = "INSERT INTO [StudentID]"
            SqlQuery = SqlQuery & "([StudentName]"
            SqlQuery = SqlQuery & ",[MudName]"
            SqlQuery = SqlQuery & ",[SchoolName]"
            SqlQuery = SqlQuery & ",[IdaraName]"
            SqlQuery = SqlQuery & ",[SpecialEdu]"
            'SqlQuery = SqlQuery & ",[ArabicDegree]"
            'SqlQuery = SqlQuery & ",[MathDegree]"
            'SqlQuery = SqlQuery & ",[SciDegree]"
            'SqlQuery = SqlQuery & ",[EngDegree]"
            'SqlQuery = SqlQuery & ",[SocialDegree]"
            'SqlQuery = SqlQuery & ",[DiffArabic]"
            'SqlQuery = SqlQuery & ",[DiffMath]"
            'SqlQuery = SqlQuery & ",[DiffSci]"
            'SqlQuery = SqlQuery & ",[WorkLifeArabic]"
            'SqlQuery = SqlQuery & ",[WorkLifeMath]"
            'SqlQuery = SqlQuery & ",[WorkLifeSci]"
            SqlQuery = SqlQuery & ",[UserName]"
            SqlQuery = SqlQuery & ",[Password]"
            SqlQuery = SqlQuery & ",[RePassword]"
            SqlQuery = SqlQuery & ",[Email])"
            SqlQuery = SqlQuery & " VALUES ("
            SqlQuery = SqlQuery & "'" & TxtName.Text.ToString & "',"
            SqlQuery = SqlQuery & "'" & CobMudCode.SelectedItem.Text.ToString & "',"
            SqlQuery = SqlQuery & "'" & CobSchoolCode.SelectedItem.Text.ToString & "',"
            SqlQuery = SqlQuery & "'" & CobIdaraCode.SelectedItem.Text.ToString & "',"
            SqlQuery = SqlQuery & IntSpecialEdu & ","
            'SqlQuery = SqlQuery & Session("ArabicDegree") & ","
            'SqlQuery = SqlQuery & Session("MathDegree") & ","
            'SqlQuery = SqlQuery & Session("SciDegree") & ","
            'SqlQuery = SqlQuery & Session("EngDegree") & ","
            'SqlQuery = SqlQuery & Session("SocialDegree") & ","
            'SqlQuery = SqlQuery & Session("DiffArabic") & ","
            'SqlQuery = SqlQuery & Session("DiffMath") & ","
            'SqlQuery = SqlQuery & Session("DiffSci") & ","
            'SqlQuery = SqlQuery & Session("WorkLifeArabic") & ","
            'SqlQuery = SqlQuery & Session("WorkLifeMath") & ","
            'SqlQuery = SqlQuery & Session("WorkLifeSci") & ","
            SqlQuery = SqlQuery & "'" & TxtUsername.Text.ToString & "',"
            SqlQuery = SqlQuery & "'" & TxtPassword.Text.ToString & "',"
            SqlQuery = SqlQuery & "'" & TxtRe_EnterPassword.Text.ToString & "',"
            SqlQuery = SqlQuery & "'" & TxtEMail.Text.ToString & "')"
            Session("SqlQuery") = SqlQuery
            SqlCommand.CommandText = SqlQuery
            Dim result As Integer = SqlCommand.ExecuteNonQuery()
            SqlQuery = "SELECT UserName, Password, StudentName, StudentCode FROM StudentID WHERE (UserName ='" & TxtUsername.Text.ToString & "' and Password='" & TxtPassword.Text.ToString & "')"
            SqlCommand = New SqlCommand()
            SqlCommand.Connection = SqlConn
            SqlCommand.CommandText = SqlQuery
            'Dim result As Integer = SqlCommand.ExecuteNonQuery()
            SqlDataReader = SqlCommand.ExecuteReader()

            If SqlDataReader.HasRows Then
                SqlDataReader.Read()
                Session("StudentName") = SqlDataReader("StudentName")
                Session("UserName") = SqlDataReader("UserName")
                Session("Password") = SqlDataReader("Password")
                Session("StudentCode") = SqlDataReader("StudentCode")
                Response.Redirect("mainpage.aspx")
            End If
        End If

        SqlCommand.Cancel()
        SqlCommand.Dispose()
        SqlConn.Close()
    End Sub

    Protected Sub CobMudCode_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs) Handles CobMudCode.SelectedIndexChanged
        Dim sqlquery As String = "SELECT * FROM [Edara] WHERE ([MUD_ID] = " & CobMudCode.SelectedValue & ")"
        Using cmd As New SqlCommand(sqlquery)
            cmd.CommandType = CommandType.Text
            cmd.Connection = SqlConn
            SqlConn.Open()
            CobIdaraCode.DataSource = cmd.ExecuteReader()
            CobIdaraCode.DataTextField = "IDARA_NAME"
            CobIdaraCode.DataValueField = "IDARA_ID"
            CobIdaraCode.DataBind()
            SqlConn.Close()
            CobIdaraCode.Items.Insert(0, New ListItem("أختر الاداره", "0"))
            CobIdaraCode.SelectedIndex = 0

        End Using
    End Sub

    Protected Sub CobIdaraCode_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs) Handles CobIdaraCode.SelectedIndexChanged
        Dim sqlquery As String = "SELECT BCH_DESC, BCH_CODE FROM schools WHERE (MUD_ID =" & CobMudCode.SelectedValue & ") AND (IDARA_ID = " & CobIdaraCode.SelectedValue & ")  AND (STAGE_ID = 20)"
        Using cmd As New SqlCommand(sqlquery)
            cmd.CommandType = CommandType.Text
            cmd.Connection = SqlConn
            SqlConn.Open()
            CobSchoolCode.DataSource = cmd.ExecuteReader()
            CobSchoolCode.DataTextField = "BCH_DESC"
            CobSchoolCode.DataValueField = "BCH_CODE"
            CobSchoolCode.DataBind()
            SqlConn.Close()
            CobSchoolCode.Items.Insert(0, New ListItem("أختر المدرسة", "0"))
            CobSchoolCode.SelectedIndex = 0

        End Using
    End Sub
End Class