VERSION 1.0 CLASS
BEGIN
  MultiUse = -1  'True
  Persistable = 0  'NotPersistable
  DataBindingBehavior = 0  'vbNone
  DataSourceBehavior  = 0  'vbNone
  MTSTransactionMode  = 0  'NotAnMTSObject
END
Attribute VB_Name = "clsSchedule"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = True
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = False
Dim mrs As New ADODB.Recordset
Dim msql As String
Sub AddSchedule(ID As String, cTime As String, cFaculty As String, cSubject As String, cRoom As String, cDay As String, cYearLevel As String, cSection As String)
On Error GoTo errhandler
If rs.State = adStateOpen Then rs.Close
msql = " Select * from ClassSchedule Where cTime like'" & cTime & "%' and cFaculty like'" & cFaculty & "%' and cSubjects like'" & cSubject & "%' and cRoom like '" & cRoom & "%' and cDay like'" & cDay & "%' and cYearLevel like'" & cYearLevel & "%' and cSection like'" & cSection & "%'"
rs.Open msql, conn
If rs.RecordCount >= 1 Then
MsgBox "Duplicate record.", vbCritical, ""
Exit Sub
End If
With rs
    .AddNew
    !ID = ID
    !cTime = cTime
    !cFaculty = cFaculty
    !cSubjects = cSubject
    !cRoom = cRoom
    !cDay = cDay
    !cYearLevel = cYearLevel
    !cSection = cSection
    .Update
errhandler:
MsgBox Err.Description, vbCritical, ""
End With
MsgBox "New entry successfully saved to the record.", vbInformation

End Sub
Sub UpdateSchedule(ID As String, cTime As String, cFaculty As String, cSubject As String, cRoom As String, cDay As String, cYearLevel As String, cSection As String)
If rs.State = adStateOpen Then rs.Close
msql = " Update ClassSchedule set id='" & ID & "',ctime='" & cTime & "',cfaculty='" & cFaculty & "',csubjects='" & cSubject & "',croom='" & cRoom & "',cday='" & cDay & "',cyearlevel='" & cYearLevel & "',csection='" & cSection & "' where id='" & ID & "'"
rs.Open msql, conn
MsgBox "Records updated.", vbInformation
End Sub
Function GetID() As String
If rs.State = adStateOpen Then rs.Close
msql = " SELECT Max(ClassSchedule.ID) AS MaxOfID" & _
" FROM ClassSchedule;"
rs.Open msql, conn
If IsNull(rs(0).Value) Then
GetID = Format(1, "00000")
Else
GetID = Format(rs(0).Value + 1, "00000")
End If

End Function
Public Sub DisplaySchedule(lstYL As ListView)
Dim lstItem As ListItem, a As Integer
If rs.State = adStateOpen Then rs.Close

    msql = " SELECT ClassSchedule.ID, ClassSchedule.cTime, ClassSchedule.cFaculty, ClassSchedule.cSubjects, ClassSchedule.cRoom, ClassSchedule.cDay, ClassSchedule.cYearLevel, ClassSchedule.cSection" & _
           " From ClassSchedule" & _
           " GROUP BY ClassSchedule.ID, ClassSchedule.cTime, ClassSchedule.cFaculty, ClassSchedule.cSubjects, ClassSchedule.cRoom, ClassSchedule.cDay, ClassSchedule.cYearLevel, ClassSchedule.cSection" & _
           " ORDER BY ClassSchedule.ID;"

    rs.Open msql, conn
   lstYL.ListItems.Clear
   Do While Not rs.EOF
    a = a + 1
        Set lstItem = lstYL.ListItems.Add(, , a)
            lstItem.SubItems(1) = rs(0).Value
            lstItem.SubItems(2) = rs(1).Value
            lstItem.SubItems(3) = rs(2).Value
            lstItem.SubItems(4) = rs(3).Value
            lstItem.SubItems(5) = rs(4).Value
            lstItem.SubItems(6) = rs(5).Value
            lstItem.SubItems(7) = rs(6).Value
            lstItem.SubItems(8) = rs(7).Value
            rs.MoveNext
            Loop
End Sub
'Public Sub DisplayFilterSchedule(lstYL As ListView, MyTime As String, MyFaculty As String, MyRoom As String, MyDay As String, MyYearLevel As String, MySection As String)
Public Sub DisplayFilterSchedule(lstYL As ListView, MyTime As String, MyRoom As String, MyDay As String)
'Public Sub DisplayFilterSchedule(lstYL As ListView, MyTime As String, Myday As String) 'original
'Public Sub DisplayFilterSchedule(lstYL As ListView, MyTime As String, Myday As String, MyYearLevel As String, MySection As String)

Dim lstItem As ListItem, a As Integer
If rs.State = adStateOpen Then rs.Close

    msql = " SELECT ClassSchedule.ID, ClassSchedule.cTime, ClassSchedule.cFaculty, ClassSchedule.cSubjects, ClassSchedule.cRoom, ClassSchedule.cDay, ClassSchedule.cYearLevel, ClassSchedule.cSection" & _
           " From ClassSchedule" & _
           " Where ClassSchedule.cTime like'" & MyTime & "%' and ClassSchedule.cRoom like'" & MyRoom & "%'  and ClassSchedule.cDay like'" & MyDay & "%'   " & _
           " GROUP BY ClassSchedule.ID, ClassSchedule.cTime, ClassSchedule.cFaculty, ClassSchedule.cSubjects, ClassSchedule.cRoom, ClassSchedule.cDay, ClassSchedule.cYearLevel, ClassSchedule.cSection" & _
           " ORDER BY ClassSchedule.ID;"
'and  ClassSchedule.cFaculty='" & MyFaculty & "' and ClassSchedule.cSubjects='" & MySubject & "' and ClassSchedule.cRoom='" & MyRoom & "' and ClassSchedule.cDay='" & MyDay & "' and ClassSchedule.cYearLevel='" & MyYearLevel & "' and ClassSchedule.cSection='" & MySection & "'" &
'and ClassSchedule.cSubjects like'" & MySubject & "%'
'and ClassSchedule.cRoom like'" & MyRoom & "%%%'
'and ClassSchedule.cYearLevel like'" & MyYearLevel & "%' and ClassSchedule.cSection like'" & MySection & "%'
'and ClassSchedule.cDay like'" & MyDay & "%'
', MySubject As String
        '   " Where ClassSchedule.cTime like'" & MyTime & "%' and  ClassSchedule.cRoom like'" & MyRoom & "%' and ClassSchedule.cDay like'" & MyDay & "%'" & _ '

    rs.Open msql, conn
   lstYL.ListItems.Clear
   Do While Not rs.EOF
    a = a + 1
        Set lstItem = lstYL.ListItems.Add(, , a)
            lstItem.SubItems(1) = rs(0).Value
            lstItem.SubItems(2) = rs(1).Value
            lstItem.SubItems(3) = rs(2).Value
            lstItem.SubItems(4) = rs(3).Value
            lstItem.SubItems(5) = rs(4).Value
            lstItem.SubItems(6) = rs(5).Value
            lstItem.SubItems(7) = rs(6).Value
            lstItem.SubItems(8) = rs(7).Value
            rs.MoveNext
            Loop
End Sub
Public Sub DeleteSchedule(ID As String)
If rs.State = adStateOpen Then rs.Close
msql = " Delete * From ClassSchedule Where ID='" & ID & "'"
rs.Open msql, conn
MsgBox "Record(s) Deleted", vbInformation, ""
End Sub



Function FacultyInUse(cTime As String, cFaculty As String) As Boolean
If rs.State = adStateOpen Then rs.Close
msql = " Select * from ClassSchedule Where cTime like'" & cTime & "%' and cFaculty like'" & cFaculty & "%'"
rs.Open msql, conn
If rs.RecordCount >= 1 Then
FacultyInUse = True
Else
FacultyInUse = False
End If
End Function
Public Sub PrintSchedule()
If rs.State = adStateOpen Then rs.Close
msql = " SELECT ClassSchedule.ID, ClassSchedule.cTime, ClassSchedule.cFaculty, ClassSchedule.cSubjects, ClassSchedule.cRoom, ClassSchedule.cDay, ClassSchedule.cYearLevel, ClassSchedule.cSection" & _
           " From ClassSchedule" & _
           " GROUP BY ClassSchedule.ID, ClassSchedule.cTime, ClassSchedule.cFaculty, ClassSchedule.cSubjects, ClassSchedule.cRoom, ClassSchedule.cDay, ClassSchedule.cYearLevel, ClassSchedule.cSection" & _
           " ORDER BY ClassSchedule.ID;"
rs.Open msql, conn
Set rptClassSchedule.DataSource = rs
    rptClassSchedule.Show 1
    Set rs = Nothing
End Sub
Public Sub PrintFacultySchedule(FacName As String)
If rs.State = adStateOpen Then rs.Close
msql = " SELECT ClassSchedule.ID, ClassSchedule.cTime, ClassSchedule.cFaculty, ClassSchedule.cSubjects, ClassSchedule.cRoom, ClassSchedule.cDay, ClassSchedule.cYearLevel, ClassSchedule.cSection" & _
           " From ClassSchedule" & _
           " Where ClassSchedule.cFaculty = '" & FacName & "'" & _
           " GROUP BY ClassSchedule.ID, ClassSchedule.cTime, ClassSchedule.cFaculty, ClassSchedule.cSubjects, ClassSchedule.cRoom, ClassSchedule.cDay, ClassSchedule.cYearLevel, ClassSchedule.cSection" & _
           " ORDER BY ClassSchedule.ID;"
rs.Open msql, conn
Set rptFacultySchedule.DataSource = rs
    rptFacultySchedule.Sections("Section4").Controls.Item("lblName").Caption = FacName
    rptFacultySchedule.Show 1
End Sub
Public Sub DisplayFacultySchedule(lstYL As ListView, FacName As String)
Dim lstItem As ListItem, a As Integer
If rs.State = adStateOpen Then rs.Close
If FacName <> "" Then
msql = " SELECT ClassSchedule.ID, ClassSchedule.cTime, ClassSchedule.cFaculty, ClassSchedule.cSubjects, ClassSchedule.cRoom, ClassSchedule.cDay, ClassSchedule.cYearLevel, ClassSchedule.cSection" & _
           " From ClassSchedule" & _
           " Where ClassSchedule.cFaculty like '" & FacName & "%%%'" & _
           " GROUP BY ClassSchedule.ID, ClassSchedule.cTime, ClassSchedule.cFaculty, ClassSchedule.cSubjects, ClassSchedule.cRoom, ClassSchedule.cDay, ClassSchedule.cYearLevel, ClassSchedule.cSection" & _
           " ORDER BY ClassSchedule.ID;"
Else
msql = " SELECT ClassSchedule.ID, ClassSchedule.cTime, ClassSchedule.cFaculty, ClassSchedule.cSubjects, ClassSchedule.cRoom, ClassSchedule.cDay, ClassSchedule.cYearLevel, ClassSchedule.cSection" & _
           " From ClassSchedule" & _
           " GROUP BY ClassSchedule.ID, ClassSchedule.cTime, ClassSchedule.cFaculty, ClassSchedule.cSubjects, ClassSchedule.cRoom, ClassSchedule.cDay, ClassSchedule.cYearLevel, ClassSchedule.cSection" & _
           " ORDER BY ClassSchedule.ID;"
End If
    rs.Open msql, conn
   lstYL.ListItems.Clear
   Do While Not rs.EOF
    a = a + 1
        Set lstItem = lstYL.ListItems.Add(, , a)
            lstItem.SubItems(1) = rs(0).Value
            lstItem.SubItems(2) = rs(1).Value
            lstItem.SubItems(3) = rs(2).Value
            lstItem.SubItems(4) = rs(3).Value
            lstItem.SubItems(5) = rs(4).Value
            lstItem.SubItems(6) = rs(5).Value
            lstItem.SubItems(7) = rs(6).Value
            lstItem.SubItems(8) = rs(7).Value
            rs.MoveNext
            Loop
End Sub
Public Sub DisplayRoomSchedule(lstYL As ListView, RoomNumber As String)
Dim lstItem As ListItem, a As Integer
If rs.State = adStateOpen Then rs.Close
msql = " SELECT ClassSchedule.ID, ClassSchedule.cTime, ClassSchedule.cFaculty, ClassSchedule.cSubjects, ClassSchedule.cRoom, ClassSchedule.cDay, ClassSchedule.cYearLevel, ClassSchedule.cSection" & _
           " From ClassSchedule" & _
           " Where ClassSchedule.cRoom = '" & RoomNumber & "'" & _
           " GROUP BY ClassSchedule.ID, ClassSchedule.cTime, ClassSchedule.cFaculty, ClassSchedule.cSubjects, ClassSchedule.cRoom, ClassSchedule.cDay, ClassSchedule.cYearLevel, ClassSchedule.cSection" & _
           " ORDER BY ClassSchedule.ID;"
rs.Open msql, conn
   lstYL.ListItems.Clear
   Do While Not rs.EOF
    a = a + 1
        Set lstItem = lstYL.ListItems.Add(, , a)
            lstItem.SubItems(1) = rs(0).Value
            lstItem.SubItems(2) = rs(1).Value
            lstItem.SubItems(3) = rs(2).Value
            lstItem.SubItems(4) = rs(3).Value
            lstItem.SubItems(5) = rs(4).Value
            lstItem.SubItems(6) = rs(5).Value
            lstItem.SubItems(7) = rs(6).Value
            lstItem.SubItems(8) = rs(7).Value
            rs.MoveNext
            Loop
End Sub
Public Sub PrintRoomSchedule(RoomNo As String)
If rs.State = adStateOpen Then rs.Close
msql = " SELECT ClassSchedule.ID, ClassSchedule.cTime, ClassSchedule.cFaculty, ClassSchedule.cSubjects, ClassSchedule.cRoom, ClassSchedule.cDay, ClassSchedule.cYearLevel, ClassSchedule.cSection" & _
           " From ClassSchedule" & _
           " Where ClassSchedule.cRoom = '" & RoomNo & "'" & _
           " GROUP BY ClassSchedule.ID, ClassSchedule.cTime, ClassSchedule.cFaculty, ClassSchedule.cSubjects, ClassSchedule.cRoom, ClassSchedule.cDay, ClassSchedule.cYearLevel, ClassSchedule.cSection" & _
           " ORDER BY ClassSchedule.ID;"
rs.Open msql, conn
Set rptRoomSchedule.DataSource = rs
    rptRoomSchedule.Sections("Section4").Controls.Item("lblName").Caption = RoomNo
    rptRoomSchedule.Show 1
End Sub
Public Sub PrintScheduleByDay(MyDay As String)
If rs.State = adStateOpen Then rs.Close
msql = " SELECT ClassSchedule.ID, ClassSchedule.cTime, ClassSchedule.cFaculty, ClassSchedule.cSubjects, ClassSchedule.cRoom, ClassSchedule.cDay, ClassSchedule.cYearLevel, ClassSchedule.cSection" & _
           " From ClassSchedule" & _
           " Where  ClassSchedule.cDay='" & MyDay & "'" & _
           " GROUP BY ClassSchedule.ID, ClassSchedule.cTime, ClassSchedule.cFaculty, ClassSchedule.cSubjects, ClassSchedule.cRoom, ClassSchedule.cDay, ClassSchedule.cYearLevel, ClassSchedule.cSection" & _
           " ORDER BY  ClassSchedule.cTime;"
rs.Open msql, conn
Set rptClassScheduleByDay.DataSource = rs
    rptClassScheduleByDay.Sections("Section4").Controls.Item("lblDay").Caption = MyDay
    rptClassScheduleByDay.Show 1
    Set rs = Nothing
End Sub
Public Sub PrintScheduleByYearAndSection(MyYear As String, MySection As String)
If rs.State = adStateOpen Then rs.Close
msql = " SELECT ClassSchedule.ID, ClassSchedule.cTime, ClassSchedule.cFaculty, ClassSchedule.cSubjects, ClassSchedule.cRoom, ClassSchedule.cDay, ClassSchedule.cYearLevel, ClassSchedule.cSection" & _
           " From ClassSchedule" & _
           " Where  ClassSchedule.cYearLevel='" & MyYear & "' and ClassSchedule.cSection='" & MySection & "' " & _
           " GROUP BY ClassSchedule.ID, ClassSchedule.cTime, ClassSchedule.cFaculty, ClassSchedule.cSubjects, ClassSchedule.cRoom, ClassSchedule.cDay, ClassSchedule.cYearLevel, ClassSchedule.cSection" & _
           " ORDER BY  ClassSchedule.cTime;"
rs.Open msql, conn
Set rptClassScheduleByYearAndSection.DataSource = rs
    rptClassScheduleByYearAndSection.Sections("Section4").Controls.Item("lblYear").Caption = MyYear
    rptClassScheduleByYearAndSection.Sections("Section4").Controls.Item("lblSection").Caption = MySection
    rptClassScheduleByYearAndSection.Show 1
    Set rs = Nothing
End Sub
Public Sub DisplayScheduleByDay(lstYL As ListView, MyDay As String)
Dim lstItem As ListItem, a As Integer
If rs.State = adStateOpen Then rs.Close
msql = " SELECT ClassSchedule.ID, ClassSchedule.cTime, ClassSchedule.cFaculty, ClassSchedule.cSubjects, ClassSchedule.cRoom, ClassSchedule.cDay, ClassSchedule.cYearLevel, ClassSchedule.cSection" & _
          " From ClassSchedule" & _
           " Where  ClassSchedule.cDay='" & MyDay & "'" & _
           " GROUP BY ClassSchedule.ID, ClassSchedule.cTime, ClassSchedule.cFaculty, ClassSchedule.cSubjects, ClassSchedule.cRoom, ClassSchedule.cDay, ClassSchedule.cYearLevel, ClassSchedule.cSection" & _
           " ORDER BY  ClassSchedule.cTime;"

rs.Open msql, conn
   lstYL.ListItems.Clear
   Do While Not rs.EOF
    a = a + 1
        Set lstItem = lstYL.ListItems.Add(, , a)
            lstItem.SubItems(1) = rs(0).Value
            lstItem.SubItems(2) = rs(1).Value
            lstItem.SubItems(3) = rs(2).Value
            lstItem.SubItems(4) = rs(3).Value
            lstItem.SubItems(5) = rs(4).Value
            lstItem.SubItems(6) = rs(5).Value
            lstItem.SubItems(7) = rs(6).Value
            lstItem.SubItems(8) = rs(7).Value
            rs.MoveNext
            Loop
End Sub
Public Sub DisplayScheduleByYearAndSection(lstYL As ListView, MyYear As String, MySection As String)
Dim lstItem As ListItem, a As Integer
If rs.State = adStateOpen Then rs.Close
msql = " SELECT ClassSchedule.ID, ClassSchedule.cTime, ClassSchedule.cFaculty, ClassSchedule.cSubjects, ClassSchedule.cRoom, ClassSchedule.cDay, ClassSchedule.cYearLevel, ClassSchedule.cSection" & _
           " From ClassSchedule" & _
           " Where  ClassSchedule.cYearLevel='" & MyYear & "' and ClassSchedule.cSection='" & MySection & "' " & _
           " GROUP BY ClassSchedule.ID, ClassSchedule.cTime, ClassSchedule.cFaculty, ClassSchedule.cSubjects, ClassSchedule.cRoom, ClassSchedule.cDay, ClassSchedule.cYearLevel, ClassSchedule.cSection" & _
           " ORDER BY  ClassSchedule.cTime;"

rs.Open msql, conn
   lstYL.ListItems.Clear
   Do While Not rs.EOF
    a = a + 1
        Set lstItem = lstYL.ListItems.Add(, , a)
            lstItem.SubItems(1) = rs(0).Value
            lstItem.SubItems(2) = rs(1).Value
            lstItem.SubItems(3) = rs(2).Value
            lstItem.SubItems(4) = rs(3).Value
            lstItem.SubItems(5) = rs(4).Value
            lstItem.SubItems(6) = rs(5).Value
            lstItem.SubItems(7) = rs(6).Value
            lstItem.SubItems(8) = rs(7).Value
            rs.MoveNext
            Loop
End Sub


