Public Class frmenrollment

    Private Sub frmenrollment_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load


        sql = "SELECT * FROM ay"
        fillCombo(sql, cbosy, "ACADYR")

        sql = "SELECT  `LEVEL` FROM `level`"
        fillCombo(sql, cboLevel, "LEVEL")

        sql = "SELECT `IDNO`, CONCAT( `LNAME`,', ', `FNAME`,' ', `MNAME`) as 'FULL NAME', `SEX` , `AGE`, `HOME_ADD` as 'ADDRESS'  FROM `tblstudent`"
        fillListView(sql, lstViewStudent)
        cboLevel.Text = "Grade 7"
        cbostatus.Text = "New"
        cbosy.Text = "2013-2014"
        cboSection.Text = "A"
        clearGroupTxt(GroupBox1)
    End Sub

    Private Sub txtsearch_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtsearch.TextChanged

        sql = "SELECT `IDNO`, CONCAT( `LNAME`,', ', `FNAME`,' ', `MNAME`) as 'FULL NAME', `SEX`" _
        & ", `AGE`, `HOME_ADD` as 'ADDRESS'" _
        & " FROM `tblstudent` WHERE `IDNO` like '%" & txtsearch.Text & "%'" _
        & " OR `FNAME` like '%" & txtsearch.Text & "%' OR `LNAME` like '%" & txtsearch.Text & "%'"
        fillListView(sql, lstViewStudent)
    End Sub

    Private Sub lstViewStudent_DoubleClick(ByVal sender As Object, ByVal e As System.EventArgs) Handles lstViewStudent.DoubleClick
        sql = "SELECT `IDNO`, CONCAT( `LNAME`,', ', `FNAME`,' ', `MNAME`) as 'FULL NAME', `SEX`" _
               & ", `AGE`, `HOME_ADD` as 'ADDRESS'" _
               & " FROM `tblstudent` WHERE `IDNO` like '%" & lstViewStudent.SelectedItems.Item(0).Text & "%'"
        fillSpecifictxt(sql)
        Try
            txtFullname.Text = dt.Rows(0).Item("FULL NAME")
            txtAddress.Text = dt.Rows(0).Item("ADDRESS")

            id = lstViewStudent.SelectedItems.Item(0).Text
            'for the replica of LEVEL and SCHOOL YEAR 
            sql = " SELECT * FROM `tblstudupdatelevel` " _
            & "WHERE `IDNO` ='" & lstViewStudent.SelectedItems.Item(0).Text & "'"
            fillSpecifictxt(sql)
            If dt.Rows.Count > 0 Then
                cboLevel.Text = dt.Rows(0).Item("LEVEL")
                cbostatus.Text = "Old"
                cbosy.Text = dt.Rows(0).Item("AY")
                'If dt.Rows(0).Item("LEVEL") = "Grade 7" Then
                '    cboLevel.Text = "Grade 8"
                '    cbostatus.Text = "Old"
                'ElseIf dt.Rows(0).Item("LEVEL") = "Grade 8" Then
                '    cboLevel.Text = "3rd Year"
                '    cbostatus.Text = "Old"
                'ElseIf dt.Rows(0).Item("LEVEL") = "3rd Year" Then
                '    cboLevel.Text = "4th Year"
                '    cbostatus.Text = "Old"
                'End If
            Else
                cboLevel.Text = "Grade 7"
                cbostatus.Text = "New"
                cbosy.Text = "2013-2014"
            End If

        Catch ex As Exception
            MsgBox(ex.Message & " lstViewStudent_DoubleClick", MsgBoxStyle.Exclamation)
        End Try

    End Sub

    Private Sub btnnew_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnnew.Click
        Call frmenrollment_Load(sender, e)
    End Sub
    Public id As String
    Public subjId As String
    Private Sub btnsave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnsave.Click
        Try
            If txtFullname.Text = "" Then
                MsgBox("Fill the name of the students.", MsgBoxStyle.Exclamation)
            Else
                'for the replica of LEVEL and SCHOOL YEAR 
                sql = " SELECT * FROM `tblstudupdatelevel` WHERE `IDNO` ='" & id & "'"
                fillSpecifictxt(sql)
                Try
                    If dt.Rows.Count > 0 Then
                        sql = "UPDATE `tblstudupdatelevel` SET  " _
                         & "`LEVEL`='" & cboLevel.Text & "',SECTION = '" & cboSection.Text _
                         & "' ,`AY`= '" & cbosy.Text & "' WHERE `IDNO`='" & id & "'"
                        edit(sql)
                    Else
                        sql = "INSERT INTO `tblstudupdatelevel` (`IDNO`, `LEVEL`,SECTION, `AY`) " _
                                            & "VALUES ('" & id & "','" & cboLevel.Text & "','" _
                                            & cboSection.Text & "','" & cbosy.Text & "')"
                        create(sql)
                    End If
                Catch ex As Exception
                    MsgBox(ex.Message)
                End Try


                'for school year of student
                sql = "INSERT INTO `schoolyr` (`AY`, `YR`,SECTION, `IDNO`, `STATUS`) " _
                       & "VALUES ('" & cbosy.Text & "','" & cboLevel.Text & "','" _
                       & cboSection.Text & "','" & id & "','" & cbostatus.Text & "')"
                messageTrue = "Student has been enrolled."
                messageFalse = "Failed the student's to enrolled."
                CreateUpdateDeleteAllMessage(sql, messageTrue, messageFalse)

                sql = "SELECT * FROM `tblstudupdatelevel` l,`schoolyr`  sy, `subject` s " _
                & "WHERE  l.IDNO=sy.IDNO AND l.LEVEL = sy.YR AND sy.`YR` =s.`YR` AND sy.`IDNO` = '" & id & "'"
                fillSpecifictxt(sql)

                For Each r As DataRow In dt.Rows
                    sql = "INSERT INTO `grades` (`IDNO`, `SUBJ_ID`, `SYID` ) " _
                       & " VALUES ('" & id & "','" & r.Item("SUBJ_ID") & "','" & r.Item("SYID") & "')"
                    create(sql)
                Next

                reports("SELECT CONCAT(  `FNAME` ,  ' ',  `LNAME` ,  ' ',  `MNAME` ) AS  'NAME',  `LEVEL` ,  `SECTION` , l.`AY` ,  `SUBJ_CODE`,`SUBJ_DESCRIPTION` " _
                & " FROM  `tblstudent` s,  `tblstudupdatelevel` l,  `grades` g,  `subject` sub" _
                & " WHERE s.`IDNO` = l.`IDNO` " _
                & " AND l.`IDNO` = g.`IDNO` " _
                & " AND g.`SUBJ_ID` = sub.`SUBJ_ID` AND s.`IDNO` ='" & id & "'", "cashiercopy", frmHome.CrystalReportViewer1)
                frmHome.Focus()
                frmHome.ShowDialog()
                frmHome.Dispose()

                Call frmenrollment_Load(sender, e)

            End If
        Catch ex As Exception

        End Try
        

    End Sub
End Class