Module ejbsqlstatements

    Dim cmd As New OleDb.OleDbCommand
    Dim da As New OleDb.OleDbDataAdapter


    Public Sub ejbfindthis(ByVal sql As String)
        Try
            con.Open()
            With cmd
                .Connection = con
                .CommandText = sql
            End With
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Exclamation)
        End Try
        con.Close()
        da.Dispose()

    End Sub

    Public Sub checkWinner()
        Dim table As New DataTable

        Try
            da.SelectCommand = cmd
            da.Fill(table)

            If table.Rows.Count > 0 Then

               


            
            Else

               
            End If

        Catch ex As Exception

        End Try

    End Sub

    Public Sub checkCandidate()
        Dim table As New DataTable

        Try
            da.SelectCommand = cmd
            da.Fill(table)

            If table.Rows.Count > 0 Then

                MsgBox("Candidate Already Exist")

            Else

                ejbinsert("INSERT INTO tblCandidate (cand_name, candidacy, gov_id, cand_score) " & _
                   " VALUES('" & frmCandidates.txtname.Text & "','" & frmCandidates.cbcandidacy.Text & "'," & frmCandidates.cbyrlevel.Text & "," & 0 & ")")

            End If

        Catch ex As Exception

        End Try

    End Sub

    Public Sub checkVoters()

        Dim table As New DataTable
        Try
            da.SelectCommand = cmd
            da.Fill(table)

            If table.Rows.Count > 0 Then

                MsgBox("Student ID Already Exist")

            Else
                Dim precentoNo As String

                precentoNo = " "

                If frmVoters.cbyearlevel.Text = 1 Then
                    precentoNo = "LAB1YEAR1"
                ElseIf frmVoters.cbyearlevel.Text = 2 Then
                    precentoNo = "LAB2YEAR2"
                ElseIf frmVoters.cbyearlevel.Text = 3 Then
                    precentoNo = "LAB3YEAR3"
                ElseIf frmVoters.cbyearlevel.Text = 4 Then
                    precentoNo = "LAB4YEAR4"
                End If

                ejbinsert("INSERT INTO tblVoters (StudentId, StudentName, YearLevel, precentoNo) " & _
                        " VALUES('" & frmVoters.txtstudentID.Text & "','" & frmVoters.txtstudname.Text & "'," & frmVoters.cbyearlevel.Text & ",'" & precentoNo & "')")


            End If

        Catch ex As Exception

        End Try


    End Sub


   

    Public Sub checkresultuser()

        Dim table As New DataTable

        Try
            da.SelectCommand = cmd
            da.Fill(table)

            If table.Rows.Count > 0 Then

                Dim res, name As String
                Dim u_id As Integer

                u_id = table.Rows(0).Item(0)
                res = table.Rows(0).Item(4)
                name = table.Rows(0).Item(1)

                ejbinsert2("insert into tbllogs(userid, res, uname, oras) values(" & u_id & ",'" & res & "','" & name & "','" & Date.Now & "')")

                If res = "Administrator" Then

                    MsgBox("Welcome you log on as " & res)


                    login.TextBox1.Clear()
                    login.TextBox2.Clear()

                    With Form1

                        .btnopen.Enabled = True
                        .btnview.Enabled = True
                        .btnvotersid.Enabled = True
                        .btnAddID.Enabled = True
                        .btnunfnshvoter.Enabled = True
                        .btnviewcand.Enabled = True
                        .btnreset.Enabled = True
                        .btnuseracc.Enabled = True

                        .btnloin.Text = "Log-Out"

                    End With



                ElseIf res = "Programmer" Then
                    MsgBox("Welcome you log on as " & res)

                    With Form1

                        .btnopen.Enabled = True
                        .btnview.Enabled = True
                        .btnvotersid.Enabled = True
                        .btnAddID.Enabled = True
                        .btnunfnshvoter.Enabled = True
                        .btnviewcand.Enabled = True
                        .btnuseracc.Enabled = True

                        .btnloin.Text = "Log-Out"

                    End With




                ElseIf res = "Comelec" Then

                    MessageBox.Show("You log-on as Board of Election Inspector.")

                    With Form1

                        .btnopen.Enabled = True
                        .btnview.Enabled = True
                        .btnvotersid.Enabled = True
                        .btnAddID.Enabled = True
                        .btnunfnshvoter.Enabled = True
                        .btnviewcand.Enabled = True
                        .btnreset.Enabled = True
                        .btnuseracc.Enabled = True

                        .btnloin.Text = "Log-Out"


                    End With



                End If


            Else
                MsgBox("Contact administrator to registered!")

            End If

            login.Close()
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Information)
        End Try
    End Sub





    Public Function checkresult() As Integer
        Dim publictable As New DataTable

        Try
            da.SelectCommand = cmd
            da.Fill(publictable)
            Return publictable.Rows.Count
            da.Dispose()

        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Information)
        End Try

    End Function


    Public Sub VoteScore()

        Dim table As New DataTable
        Dim candid, score As Integer

        Try
            da.SelectCommand = cmd
            da.Fill(table)

            If table.Rows.Count > 0 Then

                score = table.Rows(0).Item(5) + 1
                candid = table.Rows(0).Item(0)

                ejbupdateVote("UPDATE tblCandidate set cand_score =" & score & " where candidateID = " & candid & "")


            Else


            End If

        Catch ex As Exception

        End Try


    End Sub

    Public Sub checkRegistration()
        Dim table As New DataTable


        Try
            da.SelectCommand = cmd
            da.Fill(table)

            If table.Rows.Count > 0 Then



                Dim studentId As String
                Dim votemonitor As Integer

                studentId = table.Rows(0).Item(4)
                votemonitor = table.Rows(0).Item(5)


                If studentId = 1 And votemonitor = 0 Then

                    MsgBox("Start Voting Now")

                    ejbselect("select distinct [cand_name] from tblCandidate where candidacy = 'Governor' and gov_id = 1 and a_y ='" & frmAy.ComboBox1.Text & "'")
                    fillcombobox(frmRegistration.ComboBox1)

                    ejbselect("select distinct [cand_name] from tblCandidate where candidacy = 'Vice-Governor' and gov_id = 1 and a_y ='" & frmAy.ComboBox1.Text & "'")
                    fillcombobox(frmRegistration.ComboBox2)

                    With frmRegistration

                        .Panel1.Show()
                        .Panel2.Show()
                        .btnsubmit.Enabled = True
                        .btnregister.Enabled = False

                        .txtStudentId.Enabled = False

                    End With

                ElseIf studentId = 2 And votemonitor = 0 Then

                    MsgBox("Start Voting Now")

                    ejbselect("select distinct [cand_name] from tblCandidate where candidacy = 'Governor' and gov_id = 2 and a_y ='" & frmAy.ComboBox1.Text & "'")
                    fillcombobox(frmRegistration.ComboBox1)

                    ejbselect("select distinct [cand_name] from tblCandidate where candidacy = 'Vice-Governor' and gov_id = 2 and a_y ='" & frmAy.ComboBox1.Text & "'")
                    fillcombobox(frmRegistration.ComboBox2)

                    With frmRegistration

                        .Panel1.Show()
                        .Panel2.Show()
                        .btnsubmit.Enabled = True
                        .btnregister.Enabled = False

                        .txtStudentId.Enabled = False

                    End With

                ElseIf studentId = 3 And votemonitor = 0 Then

                    MsgBox("Start Voting Now")

                    ejbselect("select distinct [cand_name] from tblCandidate where candidacy = 'Governor' and gov_id = 3 and a_y ='" & frmAy.ComboBox1.Text & "'")
                    fillcombobox(frmRegistration.ComboBox1)

                    ejbselect("select distinct [cand_name] from tblCandidate where candidacy = 'Vice-Governor' and gov_id = 3 and a_y ='" & frmAy.ComboBox1.Text & "'")
                    fillcombobox(frmRegistration.ComboBox2)

                    With frmRegistration

                        .Panel1.Show()
                        .Panel2.Show()
                        .btnsubmit.Enabled = True
                        .btnregister.Enabled = False

                        .txtStudentId.Enabled = False

                    End With

                ElseIf studentId = 4 And votemonitor = 0 Then

                    MsgBox("Start Voting Now")

                    ejbselect("select distinct [cand_name] from tblCandidate where candidacy = 'Governor' and gov_id = 4 and a_y ='" & frmAy.ComboBox1.Text & "'")
                    fillcombobox(frmRegistration.ComboBox1)

                    ejbselect("select distinct [cand_name] from tblCandidate where candidacy = 'Vice-Governor' and gov_id = 4 and a_y ='" & frmAy.ComboBox1.Text & "'")
                    fillcombobox(frmRegistration.ComboBox2)

                    With frmRegistration

                        .Panel1.Show()
                        .Panel2.Show()
                        .btnsubmit.Enabled = True
                        .btnregister.Enabled = False

                        .txtStudentId.Enabled = False

                    End With


                Else
                    MsgBox("You Are Finished Voting")



                End If



            Else
                MsgBox("Please Register to the COMELEC")

                With frmRegistration


                    .txtStudentId.Clear()

                End With

            End If

        Catch ex As Exception

        End Try
    End Sub


End Module
