Imports MySql.Data.MySqlClient
Imports CrystalDecisions.CrystalReports.Engine
Imports CrystalDecisions.Windows.Forms
Imports CrystalDecisions.Shared

Module crud
    Dim dbReader As MySqlDataReader
    Public DbDataReader As MySqlDataReader
    Public Function myInsert(ByVal sql As String) As Boolean
        If ConnectionState.Open Then
            con.Close()
        End If
        Try
            con.Open()
            With cmd
                .Connection = con
                .CommandText = sql

                result = cmd.ExecuteNonQuery
                If result = 0 Then
                    Return False
                Else
                    Return True
                End If
            End With
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Information)
        End Try
        con.Close()
        Return True
    End Function
    Public Function myDelete(ByVal sql As String) As Boolean
        If ConnectionState.Open Then
            con.Close()
        End If
        Try
            con.Open()
            With cmd
                .Connection = con
                .CommandText = sql

                result = cmd.ExecuteNonQuery
                If result = 0 Then
                    Return False
                Else
                    Return True
                End If
            End With
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Information)
        End Try
        con.Close()
        Return True
    End Function
    Public Function myUpdate(ByVal sql As String) As Boolean
        If ConnectionState.Open Then
            con.Close()
        End If
        Try
            con.Open()
            With cmd
                .Connection = con
                .CommandText = sql

                result = cmd.ExecuteNonQuery
                If result = 0 Then
                    Return False
                Else
                    Return True
                End If
            End With
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Information)
        End Try
        con.Close()
        Return True
    End Function
    Public Sub mySelectQuery(ByVal sql As String)
        If ConnectionState.Open Then
            con.Close()
        End If
        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 Checkresult(ByVal CaseResult As String)

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

            If table.Rows.Count >= 0 Then

                Select Case CaseResult
                    Case "add"
                        MessageBox.Show("Your Data Has Been added")
                        frmaddstudent.TextBox1.Text = ""
                        frmaddstudent.TextBox2.Text = ""
                        frmaddstudent.TextBox3.Text = ""
                        frmaddstudent.TextBox4.Text = ""

                        frmteacher.TextBox1.Text = ""
                        frmteacher.TextBox2.Text = ""
                       

                        frmaccount.TextBox1.Text = ""
                        frmaccount.TextBox2.Text = ""
                        frmaccount.ComboBox1.Text = ""

                        tblschedule.txtsub.Text = ""
                        '  tblschedule.txtdesc.Text = ""
                        tblschedule.txtsem.Text = ""
                        tblschedule.txtsy.Text = ""
                        tblschedule.txtsched.Text = ""
                        tblschedule.txtunit.Text = ""

                    Case "Update"
                        MessageBox.Show("Your Data Has Been Updated")
                        frmaddstudent.TextBox1.Text = ""
                        frmaddstudent.TextBox2.Text = ""
                        frmaddstudent.TextBox3.Text = ""
                        frmaddstudent.TextBox4.Text = ""


                        frmteacher.TextBox1.Text = ""
                        frmteacher.TextBox2.Text = ""
                       

                    Case "DELETE"

                        frmteacher.TextBox1.Text = ""
                        frmteacher.TextBox2.Text = ""
                     

                        frmaddstudent.TextBox1.Text = ""
                        frmaddstudent.TextBox2.Text = ""
                        frmaddstudent.TextBox3.Text = ""
                        frmaddstudent.TextBox4.Text = ""

                        tblschedule.txtsched.Text = ""
                        tblschedule.txtsem.Text = ""
                        tblschedule.txtsub.Text = ""
                        tblschedule.txtsy.Text = ""
                        tblschedule.txtunit.Text = ""

                    Case "login"

                        MessageBox.Show("You are now login")
                        '  frmstudentlogin.Close()
                        ' frmeval.Show()
                        studid = table.Rows(0).Item("IDNO")
                        studgrade = table.Rows(0).Item("GRADE")

                        Debug.WriteLine("studid =======================>" & studid)

                        MainForm.Label1.Text = ("Logged in as Student")
                        frmstudentlogin.TextBox1.Text = Nothing
                        frmstudentlogin.Hide()
                        frmeval.Show()

                    Case "adminLogin"
                        MessageBox.Show("You are now login")
                        frmadminlogin.Close()


                        '        frmadminlogin.TextBox1.Text = Nothing
                        '       frmadminlogin.TextBox2.Text = Nothing


                        Username = table.Rows(0).Item("username")
                        UserId = table.Rows(0).Item("id")
                        userType = table.Rows(0).Item("user_type")
                        MainForm.Label1.Text = "Logged in as " & userType

                        With frmadminlogin
                            If .TextBox1.Text = table.Rows(0).Item("username") And .TextBox2.Text = table.Rows(0).Item("password") Then

                                If table.Rows(0).Item("user_type") = "Administrator" Then
                                    MainForm.logad.Visible = False
                                    MainForm.tbi.Visible = False
                                    MainForm.logout.Visible = True
                                    MainForm.tsbtneval.Enabled = True
                                    MainForm.tsbtnSched.Enabled = True
                                    MainForm.tsbtnManage.Enabled = True
                                End If

                            End If
                        End With

                End Select

            Else

                Select Case CaseResult
                    Case "Login"
                        MessageBox.Show("Invalid ID")
                    Case "add"
                        MessageBox.Show("Your Data Has not Been added")
                    Case "Update"
                        MessageBox.Show("Your Data Has not Been Updated")

                End Select
            End If

        Catch ex As Exception

        End Try

    End Sub
    Public Sub LoadData(ByVal obj As Object, ByVal caseswitch As String)
        Dim Counter = 1
        Dim MaxPoints = 100

        If ConnectionState.Open Then
            con.Close()
        End If
        Try

            con.Open()
            dbReader = cmd.ExecuteReader()
            obj.rows.clear()

            Do While dbReader.Read = True
                Select Case caseswitch
                    Case "addstudent"
                        obj.Rows.Add(dbReader(0), dbReader(1), dbReader(2), dbReader(3), dbReader(4))
                    Case "addteacher"
                        obj.Rows.Add(dbReader(0), dbReader(1), dbReader(2), )
                    Case "addaccount"
                        obj.Rows.Add(dbReader(0), dbReader(1), dbReader(2), dbReader(3))
                    Case "addsched"
                        obj.Rows.Add(dbReader(0), dbReader(1), dbReader(2), dbReader(3), dbReader(4), dbReader(5))
                    Case "Sched"
                        obj.Rows.Add(dbReader(0), dbReader(1), dbReader(2), dbReader(3), dbReader(4), dbReader(5), dbReader(6), dbReader(7), dbReader(8), dbReader(9))
                    Case "quest"
                        obj.Rows.Add(dbReader(0), dbReader(1))
                    Case "result"
                        obj.Rows.Add(dbReader(0), dbReader(1))
                End Select
            Loop

        Catch ex As Exception
        End Try
        con.Close()
    End Sub
    Public Sub fillcombo(ByVal combo As ComboBox, ByVal member As String, ByVal idparam As String)
        Dim Publictable As New DataTable
        Try

            da.SelectCommand = cmd
            da.Fill(Publictable)
            With combo
                .datasource = Publictable
                .displaymember = member
                .ValueMember = idparam
                .SelectedIndex = -1
                .AutoCompleteSource = AutoCompleteSource.ListItems
                .AutoCompleteMode = AutoCompleteMode.Suggest
            End With
            da.Dispose()

        Catch ex As Exception

        End Try

    End Sub

    Public Sub PrintReport(ByVal query As String, ByVal FilePath As String, ByVal ReportViewer As CrystalReportViewer)
        Dim Report As New ReportDocument

        Dim DtResult = New DataTable

        Try
            With cmd
                .Connection = con
                .CommandText = query
            End With

            da.SelectCommand = cmd
            da.Fill(DtResult)
        Catch ex As Exception
            MessageBox.Show(ex.Message, "", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
        End Try

        Report.Load(Application.StartupPath & FilePath)
        Report.SetDataSource(DtResult)
        ReportViewer.ReportSource = Report
        ReportViewer.Refresh()
    End Sub

    Public Sub myreporting(ByVal rptsql As String, ByVal rptname As String)
        dt = New DataTable
        Try
            connect.Open()

            cmd.Connection = connect()
            cmd.CommandText = rptsql
            da.SelectCommand = cmd
            da.Fill(dt)

            Dim reportpath As String = Application.StartupPath & "\Reports\" & rptname & ".rpt"

            If Not IO.File.Exists(reportpath) Then
                MsgBox("Unable to locate file")

            End If

            Dim reportdoc As New CrystalDecisions.CrystalReports.Engine.ReportDocument

            reportdoc.Load(reportpath)
            reportdoc.SetDataSource(dt)

            'With frmprint
            '    .CrystalReportViewer1.ReportSource = reportdoc
            '    .Show()

            'End With

        Catch ex As Exception

        End Try
    End Sub
End Module



