Imports System.Data.OleDb

Public Class login
    Inherits System.Web.UI.Page
    Dim OleDBConn As OleDbConnection
    Dim OleDBCommand As OleDbCommand
    Dim OleDBDataReader As OleDbDataReader
    Dim OleDBQuery 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()



            OleDBConn = New OleDbConnection(Session("Connection"))
            If Not Me.IsPostBack Then
                Using cmd As New OleDbCommand("SELECT MUD_NAME, MUD_ID FROM [Governorate] ORDER BY [MUD_NAME]")
                    cmd.CommandType = CommandType.Text
                    cmd.Connection = OleDBConn
                    OleDBConn.Open()
                    CobMudCode.DataSource = cmd.ExecuteReader()
                    CobMudCode.DataTextField = "MUD_NAME"
                    CobMudCode.DataValueField = "MUD_ID"
                    CobMudCode.DataBind()
                    OleDBConn.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

        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
        OleDBConn = New OleDbConnection(Session("Connection"))
        OleDBConn.Open()
        OleDBQuery = "SELECT UserName, Password, StudentName, StudentCode FROM StudentID WHERE (UserName ='" & TxtUserNameLogin.Text.ToString & "' and Password='" & TxtPasswordLogin.Text.ToString & "')"
        OleDBCommand = New OleDbCommand()
        OleDBCommand.Connection = OleDBConn
        OleDBCommand.CommandText = OleDBQuery
        'Dim result As Integer = OleDBCommand.ExecuteNonQuery()
        OleDBDataReader = OleDBCommand.ExecuteReader()

        If OleDBDataReader.HasRows Then
            OleDBDataReader.Read()
            Session("StudentName") = OleDBDataReader("StudentName")
            Session("UserName") = OleDBDataReader("UserName")
            Session("Password") = OleDBDataReader("Password")
            Session("StudentCode") = OleDBDataReader("StudentCode")
        Else
            LblLoginError.Text = " اسم المستخدم أو كلمة السر خطأ، أعد المحاوله"
            TxtUserNameLogin.Text = ""
            TxtPasswordLogin.Text = ""
            LblLoginError.Visible = True
            Exit Sub
        End If
        OleDBDataReader.Close()
        OleDBDataReader = Nothing
        OleDBCommand.Cancel()
        OleDBCommand.Dispose()
        OleDBConn.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
        OleDBConn = New OleDbConnection(Session("Connection"))
        OleDBConn.Open()
        OleDBQuery = "SELECT UserName, Password, StudentName FROM StudentID WHERE (UserName ='" & TxtUsername.Text.ToString & "' and Password='" & TxtPassword.Text.ToString & "')"
        OleDBCommand = New OleDbCommand()
        OleDBCommand.Connection = OleDBConn
        OleDBCommand.CommandText = OleDBQuery
        OleDBDataReader = OleDBCommand.ExecuteReader()
        If OleDBDataReader.HasRows Then
            OleDBDataReader.Read()

            LblUser.Text = " تم تسجيل اسم المستخدم هذا من قبل، أدخل أسم مستخدم أخر"
            TxtUsername.Text = ""
            TxtPassword.Text = ""
            LblUser.Visible = True
            OleDBDataReader.Close()
            OleDBDataReader = Nothing
        Else
            OleDBDataReader.Close()
            OleDBDataReader = 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
            OleDBQuery = "INSERT INTO [StudentID]"
            OleDBQuery = OleDBQuery & "([StudentName]"
            OleDBQuery = OleDBQuery & ",[MudName]"
            OleDBQuery = OleDBQuery & ",[SchoolName]"
            OleDBQuery = OleDBQuery & ",[IdaraName]"
            OleDBQuery = OleDBQuery & ",[SpecialEdu]"
            'OleDBQuery = OleDBQuery & ",[ArabicDegree]"
            'OleDBQuery = OleDBQuery & ",[MathDegree]"
            'OleDBQuery = OleDBQuery & ",[SciDegree]"
            'OleDBQuery = OleDBQuery & ",[EngDegree]"
            'OleDBQuery = OleDBQuery & ",[SocialDegree]"
            'OleDBQuery = OleDBQuery & ",[DiffArabic]"
            'OleDBQuery = OleDBQuery & ",[DiffMath]"
            'OleDBQuery = OleDBQuery & ",[DiffSci]"
            'OleDBQuery = OleDBQuery & ",[WorkLifeArabic]"
            'OleDBQuery = OleDBQuery & ",[WorkLifeMath]"
            'OleDBQuery = OleDBQuery & ",[WorkLifeSci]"
            OleDBQuery = OleDBQuery & ",[UserName]"
            OleDBQuery = OleDBQuery & ",[Password]"
            OleDBQuery = OleDBQuery & ",[RePassword]"
            OleDBQuery = OleDBQuery & ",[Email])"
            OleDBQuery = OleDBQuery & " VALUES ("
            OleDBQuery = OleDBQuery & "'" & TxtName.Text.ToString & "',"
            OleDBQuery = OleDBQuery & "'" & CobMudCode.SelectedItem.Text.ToString & "',"
            OleDBQuery = OleDBQuery & "'" & CobSchoolCode.SelectedItem.Text.ToString & "',"
            OleDBQuery = OleDBQuery & "'" & CobIdaraCode.SelectedItem.Text.ToString & "',"
            OleDBQuery = OleDBQuery & IntSpecialEdu & ","
            'OleDBQuery = OleDBQuery & Session("ArabicDegree") & ","
            'OleDBQuery = OleDBQuery & Session("MathDegree") & ","
            'OleDBQuery = OleDBQuery & Session("SciDegree") & ","
            'OleDBQuery = OleDBQuery & Session("EngDegree") & ","
            'OleDBQuery = OleDBQuery & Session("SocialDegree") & ","
            'OleDBQuery = OleDBQuery & Session("DiffArabic") & ","
            'OleDBQuery = OleDBQuery & Session("DiffMath") & ","
            'OleDBQuery = OleDBQuery & Session("DiffSci") & ","
            'OleDBQuery = OleDBQuery & Session("WorkLifeArabic") & ","
            'OleDBQuery = OleDBQuery & Session("WorkLifeMath") & ","
            'OleDBQuery = OleDBQuery & Session("WorkLifeSci") & ","
            OleDBQuery = OleDBQuery & "'" & TxtUsername.Text.ToString & "',"
            OleDBQuery = OleDBQuery & "'" & TxtPassword.Text.ToString & "',"
            OleDBQuery = OleDBQuery & "'" & TxtRe_EnterPassword.Text.ToString & "',"
            OleDBQuery = OleDBQuery & "'" & TxtEMail.Text.ToString & "')"
            Session("OleDBQuery") = OleDBQuery
            OleDBCommand.CommandText = OleDBQuery
            Dim result As Integer = OleDBCommand.ExecuteNonQuery()
            OleDBQuery = "SELECT UserName, Password, StudentName, StudentCode FROM StudentID WHERE (UserName ='" & TxtUsername.Text.ToString & "' and Password='" & TxtPassword.Text.ToString & "')"
            OleDBCommand = New OleDbCommand()
            OleDBCommand.Connection = OleDBConn
            OleDBCommand.CommandText = OleDBQuery
            'Dim result As Integer = OleDBCommand.ExecuteNonQuery()
            OleDBDataReader = OleDBCommand.ExecuteReader()

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

        OleDBCommand.Cancel()
        OleDBCommand.Dispose()
        OleDBConn.Close()
    End Sub

    Protected Sub CobMudCode_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs) Handles CobMudCode.SelectedIndexChanged
        Dim OleDBquery As String = "SELECT * FROM [Edara] WHERE ([MUD_ID] = " & CobMudCode.SelectedValue & ")"
        Using cmd As New OleDbCommand(OleDBquery)
            cmd.CommandType = CommandType.Text
            cmd.Connection = OleDBConn
            OleDBConn.Open()
            CobIdaraCode.DataSource = cmd.ExecuteReader()
            CobIdaraCode.DataTextField = "IDARA_NAME"
            CobIdaraCode.DataValueField = "IDARA_ID"
            CobIdaraCode.DataBind()
            OleDBConn.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 OleDBquery 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 OleDbCommand(OleDBquery)
            cmd.CommandType = CommandType.Text
            cmd.Connection = OleDBConn
            OleDBConn.Open()
            CobSchoolCode.DataSource = cmd.ExecuteReader()
            CobSchoolCode.DataTextField = "BCH_DESC"
            CobSchoolCode.DataValueField = "BCH_CODE"
            CobSchoolCode.DataBind()
            OleDBConn.Close()
            CobSchoolCode.Items.Insert(0, New ListItem("أختر المدرسة", "0"))
            CobSchoolCode.SelectedIndex = 0

        End Using
    End Sub
End Class