phantomzz71
Shounen Jump Specialist
2
MONTHS
2 2 MONTHS OF SERVICE
LEVEL 1
300 XP
In Class Scheduling System/ Module you have to consider the following constraint:
1. Academic Year and Term
2. Class Section
3. Class Room and Building
4. Faculty
5. Subjects
6. Curriculum
7. Class Schedule (Subject Offering) - Class Schedule is set for maximum of 5 Schedule per Subject
USING MySQL as the Primary Database......
==================STORED PROCEDURE ==============================
1. Academic Year and Term
2. Class Section
3. Class Room and Building
4. Faculty
5. Subjects
6. Curriculum
7. Class Schedule (Subject Offering) - Class Schedule is set for maximum of 5 Schedule per Subject
USING MySQL as the Primary Database......
- CREATE
TABLE
`tblayterm`
(
- `TermID`
INT
(
10
)
UNSIGNED
NOT
NULL
,
- `SchoolYear`
VARCHAR
(
10
)
DEFAULT
NULL
,
- `Locked`
tinyint(
1
)
DEFAULT
'0'
,
- `SchoolTerm`
VARCHAR
(
45
)
DEFAULT
NULL
,
- `StartofSY`
datetime DEFAULT
NULL
,
- `EndofSY`
datetime DEFAULT
NULL
,
- `ExpireReg`
tinyint(
3
)
UNSIGNED
DEFAULT
NULL
,
- `ExpireDays`
INT
(
10
)
UNSIGNED
DEFAULT
NULL
,
- `LastModified`
VARCHAR
(
45
)
DEFAULT
NULL
,
- `LastModifiedDate`
datetime DEFAULT
NULL
,
- `NumWeeks`
INT
(
11
)
DEFAULT
NULL
,
- `Hidden`
INT
(
11
)
DEFAULT
NULL
,
- PRIMARY
KEY
(
`TermID`
)
USING
BTREE
- )
ENGINE=
InnoDB DEFAULT
CHARSET=
utf8;
- CREATE
TABLE
`tblsection`
(
- `SectionID`
VARCHAR
(
20
)
NOT
NULL
,
- `SectionTitle`
VARCHAR
(
255
)
DEFAULT
NULL
,
- `YearLevelID`
INT
(
11
)
DEFAULT
'0'
,
- `TermID`
INT
(
10
)
UNSIGNED
DEFAULT
NULL
,
- `CampusID`
VARCHAR
(
45
)
DEFAULT
NULL
,
- `CollegeID`
VARCHAR
(
45
)
DEFAULT
NULL
,
- `CurriculumID`
VARCHAR
(
45
)
DEFAULT
NULL
,
- `ProgramID`
VARCHAR
(
45
)
DEFAULT
NULL
,
- `AdviserID`
VARCHAR
(
45
)
DEFAULT
NULL
,
- `IsBlock`
tinyint(
3
)
UNSIGNED
DEFAULT
NULL
,
- `RoomID`
VARCHAR
(
45
)
DEFAULT
NULL
,
- `Limit`
INT
(
10
)
UNSIGNED
DEFAULT
NULL
,
- `IsEvening`
tinyint(
3
)
UNSIGNED
DEFAULT
NULL
,
- `IsDissolved`
tinyint(
3
)
UNSIGNED
DEFAULT
NULL
,
- `CreationDate`
datetime DEFAULT
NULL
,
- `CreatedBy`
VARCHAR
(
45
)
DEFAULT
NULL
,
- `ModifiedBy`
VARCHAR
(
45
)
DEFAULT
NULL
,
- `ModifiedOn`
datetime DEFAULT
NULL
,
- PRIMARY
KEY
(
`SectionID`
)
,
- KEY
`YearLevelID`
(
`YearLevelID`
)
,
- KEY
`TermID`
(
`TermID`
)
USING
BTREE,
- KEY
`CampusID`
(
`CampusID`
)
USING
BTREE,
- KEY
`CollegeID`
(
`CollegeID`
)
USING
BTREE,
- KEY
`ProgramID`
(
`ProgramID`
)
USING
BTREE,
- CONSTRAINT
`FKTermID`
FOREIGN
KEY
(
`TermID`
)
REFERENCES
`tblayterm`
(
`TermID`
)
ON
DELETE
CASCADE ON
UPDATE
CASCADE
- )
ENGINE=
InnoDB DEFAULT
CHARSET=
utf8;
- CREATE
TABLE
`tblbuilding`
(
- `BldgID`
INT
(
11
)
NOT
NULL
,
- `CampusID`
INT
(
11
)
DEFAULT
NULL
,
- `BldgName`
VARCHAR
(
100
)
CHARACTER
SET
latin1 DEFAULT
NULL
,
- `BldgOtherName`
VARCHAR
(
100
)
CHARACTER
SET
latin1 DEFAULT
NULL
,
- `Acronym`
VARCHAR
(
45
)
CHARACTER
SET
latin1 DEFAULT
NULL
,
- `FloorsCount`
INT
(
11
)
DEFAULT
NULL
,
- `BldgPic`
longblob,
- `IsLANReady`
tinyint(
3
)
UNSIGNED
DEFAULT
NULL
,
- `Elevator`
tinyint(
3
)
UNSIGNED
DEFAULT
NULL
,
- `Escalator`
tinyint(
3
)
UNSIGNED
DEFAULT
NULL
,
- PRIMARY
KEY
(
`BldgID`
)
,
- KEY
`Index_2`
(
`CampusID`
)
,
- CONSTRAINT
`FK_CampusID`
FOREIGN
KEY
(
`CampusID`
)
REFERENCES
`tblcampus`
(
`CampusID`
)
ON
DELETE
CASCADE ON
UPDATE
CASCADE
- )
ENGINE=
InnoDB DEFAULT
CHARSET=
utf8;
- CREATE
TABLE
`tblroom`
(
- `RoomID`
VARCHAR
(
20
)
NOT
NULL
,
- `BldgID`
INT
(
11
)
DEFAULT
NULL
,
- `Floor`
INT
(
11
)
DEFAULT
NULL
,
- `Room`
VARCHAR
(
100
)
DEFAULT
NULL
,
- `Capacity`
INT
(
11
)
DEFAULT
NULL
,
- `RoomNo`
VARCHAR
(
20
)
DEFAULT
NULL
,
- `RoomTypeID`
INT
(
11
)
DEFAULT
NULL
,
- `IsAirConditioned`
tinyint(
3
)
UNSIGNED
DEFAULT
NULL
,
- `IsUsable`
tinyint(
3
)
UNSIGNED
DEFAULT
NULL
,
- `IsLANMember`
tinyint(
3
)
UNSIGNED
DEFAULT
NULL
,
- `AllowNightClass`
tinyint(
3
)
UNSIGNED
DEFAULT
NULL
,
- `Shared`
tinyint(
3
)
UNSIGNED
DEFAULT
NULL
,
- PRIMARY
KEY
(
`RoomID`
)
,
- KEY
`RoomTypeID`
(
`RoomTypeID`
)
,
- CONSTRAINT
`FK_RoomTypeID`
FOREIGN
KEY
(
`RoomTypeID`
)
REFERENCES
`tblroomtypes`
(
`RoomTypeID`
)
ON
DELETE
CASCADE ON
UPDATE
CASCADE
- )
ENGINE=
InnoDB DEFAULT
CHARSET=
utf8;
- CREATE
TABLE
`tblteacher`
(
- `TeacherID`
VARCHAR
(
10
)
NOT
NULL
,
- `EmployeeID`
VARCHAR
(
45
)
NOT
NULL
,
- `DeptID`
VARCHAR
(
45
)
NOT
NULL
,
- `CampusID`
VARCHAR
(
45
)
NOT
NULL
,
- `CollegeID`
VARCHAR
(
45
)
NOT
NULL
,
- `RankID`
VARCHAR
(
45
)
NOT
NULL
,
- `IsRegularFaculty`
tinyint(
3
)
UNSIGNED
NOT
NULL
DEFAULT
'1'
,
- `IsFullTime`
tinyint(
3
)
UNSIGNED
NOT
NULL
,
- `PRC_LicenseID`
VARCHAR
(
45
)
NOT
NULL
,
- `DegreeDiscipline`
VARCHAR
(
45
)
NOT
NULL
,
- `TeachLoadLevel`
INT
(
10
)
UNSIGNED
NOT
NULL
,
- PRIMARY
KEY
(
`TeacherID`
)
,
- KEY
`TeacherID`
(
`TeacherID`
)
- )
ENGINE=
InnoDB DEFAULT
CHARSET=
utf8;
- CREATE
TABLE
`tblcurriculum`
(
- `CurriculumID`
VARCHAR
(
45
)
NOT
NULL
,
- `CurriculumCode`
VARCHAR
(
45
)
NOT
NULL
,
- `Description`
VARCHAR
(
200
)
DEFAULT
NULL
,
- `Notes`
text,
- `CreatedBy`
VARCHAR
(
45
)
DEFAULT
NULL
,
- `CreationDate`
datetime DEFAULT
NULL
,
- `ModifiedBy`
VARCHAR
(
45
)
DEFAULT
NULL
,
- `ModifiedDate`
datetime DEFAULT
NULL
,
- `IsLocked`
tinyint(
3
)
UNSIGNED
DEFAULT
'0'
,
- `DateLocked`
datetime DEFAULT
NULL
,
- `ProgramID`
VARCHAR
(
45
)
DEFAULT
NULL
,
- `MajorID`
VARCHAR
(
45
)
DEFAULT
NULL
,
- `CampusID`
INT
(
11
)
DEFAULT
NULL
,
- PRIMARY
KEY
(
`CurriculumID`
)
,
- KEY
`Index_3`
(
`ProgramID`
)
USING
BTREE,
- KEY
`Index_1`
(
`CampusID`
)
USING
BTREE,
- KEY
`Index_2`
(
`MajorID`
)
USING
BTREE,
- CONSTRAINT
`FKCampusID`
FOREIGN
KEY
(
`CampusID`
)
REFERENCES
`tblcampus`
(
`CampusID`
)
ON
DELETE
CASCADE ON
UPDATE
CASCADE,
- CONSTRAINT
`FKProgramID`
FOREIGN
KEY
(
`ProgramID`
)
REFERENCES
`tblprograms`
(
`ProgID`
)
ON
DELETE
CASCADE ON
UPDATE
CASCADE
- )
ENGINE=
InnoDB DEFAULT
CHARSET=
utf8;
- CREATE
TABLE
`tblcurriculumdetails`
(
- `IndexID`
INT
(
10
)
UNSIGNED
NOT
NULL
AUTO_INCREMENT
,
- `CurriculumID`
VARCHAR
(
45
)
NOT
NULL
,
- `YearTermID`
INT
(
10
)
UNSIGNED
NOT
NULL
,
- `SubjectID`
VARCHAR
(
45
)
NOT
NULL
,
- `YearStandingID`
INT
(
10
)
UNSIGNED
DEFAULT
NULL
,
- `EquivalentSubjectID`
VARCHAR
(
45
)
CHARACTER
SET
latin1 NOT
NULL
,
- PRIMARY
KEY
(
`IndexID`
)
USING
BTREE,
- KEY
`CurriculumID`
(
`CurriculumID`
)
USING
BTREE,
- KEY
`SubjectID`
(
`SubjectID`
)
USING
BTREE,
- KEY
`YearTermID`
(
`YearTermID`
)
USING
BTREE,
- CONSTRAINT
`FK_CurriculumID`
FOREIGN
KEY
(
`CurriculumID`
)
REFERENCES
`tblcurriculum`
(
`CurriculumID`
)
ON
DELETE
CASCADE ON
UPDATE
CASCADE,
- CONSTRAINT
`FK_YearTermID`
FOREIGN
KEY
(
`YearTermID`
)
REFERENCES
`tblyearlevelterm`
(
`ID`
)
ON
DELETE
CASCADE ON
UPDATE
CASCADE
- )
ENGINE=
InnoDB AUTO_INCREMENT
=
184
DEFAULT
CHARSET=
utf8;
- CREATE
TABLE
`tblsection`
(
- `SectionID`
VARCHAR
(
20
)
NOT
NULL
,
- `SectionTitle`
VARCHAR
(
255
)
DEFAULT
NULL
,
- `YearLevelID`
INT
(
11
)
DEFAULT
'0'
,
- `TermID`
INT
(
10
)
UNSIGNED
DEFAULT
NULL
,
- `CampusID`
VARCHAR
(
45
)
DEFAULT
NULL
,
- `CollegeID`
VARCHAR
(
45
)
DEFAULT
NULL
,
- `CurriculumID`
VARCHAR
(
45
)
DEFAULT
NULL
,
- `ProgramID`
VARCHAR
(
45
)
DEFAULT
NULL
,
- `AdviserID`
VARCHAR
(
45
)
DEFAULT
NULL
,
- `IsBlock`
tinyint(
3
)
UNSIGNED
DEFAULT
NULL
,
- `RoomID`
VARCHAR
(
45
)
DEFAULT
NULL
,
- `Limit`
INT
(
10
)
UNSIGNED
DEFAULT
NULL
,
- `IsEvening`
tinyint(
3
)
UNSIGNED
DEFAULT
NULL
,
- `IsDissolved`
tinyint(
3
)
UNSIGNED
DEFAULT
NULL
,
- `CreationDate`
datetime DEFAULT
NULL
,
- `CreatedBy`
VARCHAR
(
45
)
DEFAULT
NULL
,
- `ModifiedBy`
VARCHAR
(
45
)
DEFAULT
NULL
,
- `ModifiedOn`
datetime DEFAULT
NULL
,
- PRIMARY
KEY
(
`SectionID`
)
,
- KEY
`YearLevelID`
(
`YearLevelID`
)
,
- KEY
`TermID`
(
`TermID`
)
USING
BTREE,
- KEY
`CampusID`
(
`CampusID`
)
USING
BTREE,
- KEY
`CollegeID`
(
`CollegeID`
)
USING
BTREE,
- KEY
`ProgramID`
(
`ProgramID`
)
USING
BTREE,
- CONSTRAINT
`FKTermID`
FOREIGN
KEY
(
`TermID`
)
REFERENCES
`tblayterm`
(
`TermID`
)
ON
DELETE
CASCADE ON
UPDATE
CASCADE
- )
ENGINE=
InnoDB DEFAULT
CHARSET=
utf8;
- CREATE
TABLE
`tblclassschedule`
(
- `SubjectOfferingID`
VARCHAR
(
31
)
NOT
NULL
,
- `TermID`
INT
(
20
)
NOT
NULL
,
- `SubjectID`
VARCHAR
(
10
)
DEFAULT
NULL
,
- `SectionID`
VARCHAR
(
20
)
DEFAULT
NULL
,
- `IsSpecialClasses`
tinyint(
1
)
DEFAULT
NULL
,
- `SchedTimeStart`
INT
(
10
)
UNSIGNED
DEFAULT
NULL
,
- `SchedTimeEnd`
INT
(
10
)
UNSIGNED
DEFAULT
NULL
,
- `TeacherID`
VARCHAR
(
10
)
DEFAULT
NULL
,
- `RoomID`
VARCHAR
(
50
)
DEFAULT
NULL
,
- `Days`
VARCHAR
(
20
)
DEFAULT
NULL
,
- `EventID1`
INT
(
10
)
UNSIGNED
DEFAULT
NULL
,
- `Sched1`
VARCHAR
(
45
)
DEFAULT
NULL
,
- `SchedTimeStart2`
INT
(
10
)
UNSIGNED
DEFAULT
NULL
,
- `SchedTimeEnd2`
INT
(
10
)
UNSIGNED
DEFAULT
NULL
,
- `TeacherID2`
VARCHAR
(
45
)
DEFAULT
NULL
,
- `RoomID2`
VARCHAR
(
45
)
DEFAULT
NULL
,
- `Days2`
VARCHAR
(
45
)
DEFAULT
NULL
,
- `EventID2`
INT
(
10
)
UNSIGNED
DEFAULT
NULL
,
- `Sched2`
VARCHAR
(
45
)
DEFAULT
NULL
,
- `SchedTimeStart3`
INT
(
10
)
UNSIGNED
DEFAULT
NULL
,
- `SchedTimeEnd3`
INT
(
10
)
UNSIGNED
DEFAULT
NULL
,
- `TeacherID3`
VARCHAR
(
45
)
DEFAULT
NULL
,
- `RoomID3`
VARCHAR
(
45
)
DEFAULT
NULL
,
- `Days3`
VARCHAR
(
45
)
DEFAULT
NULL
,
- `EventID3`
INT
(
10
)
UNSIGNED
DEFAULT
NULL
,
- `Sched3`
VARCHAR
(
45
)
DEFAULT
NULL
,
- `SchedTimeStart4`
INT
(
10
)
UNSIGNED
DEFAULT
NULL
,
- `SchedTimeEnd4`
INT
(
10
)
UNSIGNED
DEFAULT
NULL
,
- `TeacherID4`
VARCHAR
(
45
)
DEFAULT
NULL
,
- `RoomID4`
VARCHAR
(
45
)
DEFAULT
NULL
,
- `Days4`
VARCHAR
(
45
)
DEFAULT
NULL
,
- `EventID4`
INT
(
10
)
UNSIGNED
DEFAULT
NULL
,
- `Sched4`
VARCHAR
(
45
)
DEFAULT
NULL
,
- `SchedTimeStart5`
INT
(
10
)
UNSIGNED
DEFAULT
NULL
,
- `SchedTimeEnd5`
INT
(
10
)
UNSIGNED
DEFAULT
NULL
,
- `TeacherID5`
VARCHAR
(
45
)
DEFAULT
NULL
,
- `RoomID5`
VARCHAR
(
45
)
DEFAULT
NULL
,
- `Days5`
VARCHAR
(
45
)
DEFAULT
NULL
,
- `EventID5`
INT
(
10
)
UNSIGNED
DEFAULT
NULL
,
- `Sched5`
VARCHAR
(
45
)
DEFAULT
NULL
,
- `OverRideConflict`
tinyint(
4
)
DEFAULT
NULL
,
- `IsDissolved`
tinyint(
4
)
DEFAULT
NULL
,
- `PostedBy`
VARCHAR
(
45
)
DEFAULT
NULL
,
- `DatePosted`
VARCHAR
(
45
)
DEFAULT
NULL
,
- `RoomPostedBy`
VARCHAR
(
45
)
DEFAULT
NULL
,
- `RoomDatePosted`
VARCHAR
(
20
)
DEFAULT
NULL
,
- `FacultyDatePosted`
VARCHAR
(
45
)
DEFAULT
NULL
,
- `FacultyPostedBy`
VARCHAR
(
45
)
DEFAULT
NULL
,
- `CreationDate`
VARCHAR
(
20
)
DEFAULT
NULL
,
- `CreatedBy`
VARCHAR
(
70
)
DEFAULT
NULL
,
- `ModifiedDate`
VARCHAR
(
45
)
DEFAULT
NULL
,
- `ModifiedBy`
VARCHAR
(
70
)
DEFAULT
NULL
,
- PRIMARY
KEY
(
`SubjectOfferingID`
)
,
- KEY
`SubjectID`
(
`SubjectID`
)
,
- KEY
`SectionID`
(
`SectionID`
)
USING
BTREE,
- KEY
`TermID`
(
`TermID`
)
USING
BTREE,
- CONSTRAINT
`FKSectionID`
FOREIGN
KEY
(
`SectionID`
)
REFERENCES
`tblsection`
(
`SectionID`
)
ON
DELETE
CASCADE ON
UPDATE
CASCADE,
- CONSTRAINT
`FK_SubjectID`
FOREIGN
KEY
(
`SubjectID`
)
REFERENCES
`tblsubject`
(
`SubjectID`
)
ON
DELETE
CASCADE ON
UPDATE
CASCADE
- )
ENGINE=
InnoDB DEFAULT
CHARSET=
utf8;
==================STORED PROCEDURE ==============================
- CREATE
DEFINER =
'root'
@'localhost'
PROCEDURE
`GetClassScheduleConflicts`
(
- IN
TermID INTEGER
,
- IN
SectionID VARCHAR
(
45
)
,
- IN
sDay VARCHAR
(
10
)
,
- IN
TimeStart VARCHAR
(
10
)
,
- IN
TimeEnd VARCHAR
(
10
)
- )
- NOT
DETERMINISTIC
- CONTAINS SQL
- SQL
SECURITY DEFINER
- COMMENT ''
- BEGIN
- SELECT
CS.
SubjectOfferingID,
CS.
TermID,
CS.
SubjectID,
S.
SubjectCode,
S.
SubjectTitle,
S.
CreditUnits,
- CS.
SectionID,
CSec.
SectionName,
- CS.
Sched1,
fnRoomName2(
RoomID)
AS
Room_1,
- CS.
Sched2,
fnRoomName2(
RoomID2)
AS
Room_2,
- CS.
Sched3,
fnRoomName2(
RoomID3)
AS
Room_3,
- CS.
Sched4,
fnRoomName2(
RoomID4)
AS
Room_4,
- CS.
Sched5,
fnRoomName2(
RoomID4)
AS
Room_5,
- CS.
Days1,
CS.
Days2,
CS.
Days3,
CS.
Days4,
CS.
Days5,
- fnCollegeCode(
CSec.
CollegeID)
AS
CollegeCode,
- fnProgramCode(
CSec.
ProgramID)
AS
ProgramCode
- FROM
tblClassSchedule AS
CS LEFT
JOIN
- tblSubject AS
S ON
S.
SubjectID =
CS.
SubjectID LEFT
JOIN
- tblSection AS
CSec ON
CSec.
SectionID =
CS.
SectionID
- WHERE
(
CS.
TermID =
TermID)
AND
(
CS.
SectionID =
SectionID)
- AND
(
- (
(
CS.
Days1 LIKE
sDay)
AND
(
(
CS.
SchedTimeStart BETWEEN
TimeStart AND
TimeEnd)
OR
(
CS.
SchedTimeEnd BETWEEN
TimeStart AND
TimeEnd)
OR
(
(
CS.
SchedTimeEnd >
TimeStart AND
CS.
SchedTimeEnd
- AND
(
(
CS.
SchedTimeEnd >
TimeStart AND
CS.
SchedTimeEnd
- )
- OR
- (
(
CS.
Days2 LIKE
sDay)
AND
(
(
CS.
SchedTimeStart2 BETWEEN
TimeStart AND
TimeEnd)
OR
(
CS.
SchedTimeEnd2 BETWEEN
TimeStart AND
TimeEnd)
OR
(
(
CS.
Time2_End >
TimeStart AND
CS.
SchedTimeEnd2
- AND
(
(
CS.
SchedTimeEnd2 >
TimeStart AND
CS.
Time2_End
- )
- OR
- (
(
CS.
Days3 LIKE
sDay)
AND
(
(
CS.
SchedTimeStart3 BETWEEN
TimeStart AND
TimeEnd)
OR
(
CS.
SchedTimeEnd3 BETWEEN
TimeStart AND
TimeEnd)
OR
(
(
CS.
Time3_End >
TimeStart AND
CS.
SchedTimeEnd3
- AND
(
(
CS.
SchedTimeEnd3 >
TimeStart AND
CS.
SchedTimeEnd3
- )
- OR
- (
(
CS.
Days4 LIKE
sDay)
AND
(
(
CS.
SchedTimeStart4 BETWEEN
TimeStart AND
TimeEnd)
OR
(
CS.
SchedTimeEnd4 BETWEEN
TimeStart AND
TimeEnd)
OR
(
(
CS.
Time4_End >
TimeStart AND
CS.
SchedTimeEnd4
- AND
(
(
CS.
SchedTimeEnd4 >
TimeStart AND
CS.
SchedTimeEnd4
- )
- OR
- (
(
CS.
Days5 LIKE
sDay)
AND
(
(
CS.
SchedTimeStart5 BETWEEN
TimeStart AND
TimeEnd)
OR
(
CS.
SchedTimeEnd5 BETWEEN
TimeStart AND
TimeEnd)
OR
(
(
CS.
Time5_End >
@TimeStart AND
CS.
SchedTimeEnd5
- AND
(
(
CS.
SchedTimeEnd5 >
TimeStart AND
CS.
SchedTimeEnd5
- )
- )
;
- END
;
- CREATE
DEFINER =
'root'
@'localhost'
PROCEDURE
`GetFacultyScheduleConflicts`
(
- TermID INT
,
- FacultyID VARCHAR
(
15
)
,
- sDay VARCHAR
(
10
)
,
- TimeStart VARCHAR
(
10
)
,
- TimeEnd VARCHAR
(
10
)
- )
- NOT
DETERMINISTIC
- CONTAINS SQL
- SQL
SECURITY DEFINER
- COMMENT ''
- BEGIN
- SELECT
SubjectOfferingID,
CS.
TermID,
CS.
SubjectID,
S.
SubjectCode,
S.
SubjectTitle,
S.
CreditUnits,
CSec.
SectionTitle,
- CS.
Sched1,
fnRoomName2(
CS.
RoomID)
AS
Room_1,
- CS.
Sched2,
fnRoomName2(
CS.
RoomID2)
AS
Room_2,
- CS.
Sched3,
fnRoomName2(
CS.
RoomID3)
AS
Room_3,
- CS.
Sched4,
fnRoomName2(
CS.
RoomID4)
AS
Room_4,
- CS.
Sched5,
fnRoomName2(
CS.
RoomID5)
AS
Room_5,
- fnProgramCode(
CSec.
ProgramID)
AS
ProgramCode
- FROM
tblClassSchedule AS
CS LEFT
JOIN
- tblSubject AS
S ON
S.
SubjectID =
CS.
SubjectID LEFT
JOIN
- tblSection AS
CSec ON
CSec.
SectionID =
CS.
SectionID
- WHERE
(
CS.
TermID =
TermID)
AND
- (
CS.
TeacherID =
FacultyID)
AND
- (
CS.
Days LIKE
sDay)
AND
- (
(
SchedTimeStart BETWEEN
TimeStart AND
TimeEnd)
OR
(
SchedTimeEnd BETWEEN
TimeStart AND
TimeEnd)
- OR
(
(
SchedTimeEnd >
TimeStart AND
SchedTimeEnd
- AND
(
(
SchedTimeEnd >
TimeStart AND
SchedTimeEnd
- /*----------------------------------*/
- UNION
-- SCHEDULE.1 TO SCHEDULE.2
- /*----------------------------------*/
- SELECT
SubjectOfferingID,
CS.
TermID,
CS.
SubjectID,
S.
SubjectCode,
S.
SubjectTitle,
S.
CreditUnits,
CSec.
SectionTitle,
- CS.
Sched1,
fnRoomName2(
CS.
RoomID)
AS
Room_1,
- CS.
Sched2,
fnRoomName2(
CS.
RoomID2)
AS
Room_2,
- CS.
Sched3,
fnRoomName2(
CS.
RoomID3)
AS
Room_3,
- CS.
Sched4,
fnRoomName2(
CS.
RoomID4)
AS
Room_4,
- CS.
Sched5,
fnRoomName2(
CS.
RoomID5)
AS
Room_5,
- fnProgramCode(
CSec.
ProgramID)
AS
ProgramCode
- FROM
tblClassSchedule AS
CS LEFT
JOIN
- tblSubject AS
S ON
S.
SubjectID =
CS.
SubjectID LEFT
JOIN
- tblSection AS
CSec ON
CSec.
SectionID =
CS.
SectionID
- WHERE
(
CS.
TermID =
TermID)
AND
- (
CS.
TeacherID2 =
FacultyID)
AND
- (
Days2 LIKE
sDay)
AND
- (
(
SchedTimeStart2 BETWEEN
TimeStart AND
TimeEnd)
OR
(
SchedTimeEnd2 BETWEEN
TimeStart AND
TimeEnd)
- OR
(
(
SchedTimeEnd2 >
TimeStart AND
SchedTimeEnd2
- AND
(
(
SchedTimeEnd2 >
TimeStart AND
SchedTimeEnd2
- /*----------------------------------*/
- UNION
-- SCHEDULE.2 TO SCHEDULE.3
- /*----------------------------------*/
- SELECT
SubjectOfferingID,
CS.
TermID,
CS.
SubjectID,
S.
SubjectCode,
S.
SubjectTitle,
S.
CreditUnits,
CSec.
SectionTitle,
- CS.
Sched1,
fnRoomName2(
CS.
RoomID)
AS
Room_1,
- CS.
Sched2,
fnRoomName2(
CS.
RoomID2)
AS
Room_2,
- CS.
Sched3,
fnRoomName2(
CS.
RoomID3)
AS
Room_3,
- CS.
Sched4,
fnRoomName2(
CS.
RoomID4)
AS
Room_4,
- CS.
Sched5,
fnRoomName2(
CS.
RoomID5)
AS
Room_5,
- fnProgramCode(
CSec.
ProgramID)
AS
ProgramCode
- FROM
tblClassSchedule AS
CS LEFT
JOIN
- tblSubject AS
S ON
S.
SubjectID =
CS.
SubjectID LEFT
JOIN
- tblSection AS
CSec ON
CSec.
SectionID =
CS.
SectionID
- WHERE
(
CS.
TermID =
TermID)
AND
- (
CS.
TeacherID3 =
FacultyID)
AND
- (
Days3 LIKE
sDay)
AND
- (
(
SchedTimeStart3 BETWEEN
TimeStart AND
TimeEnd)
OR
(
SchedTimeEnd3 BETWEEN
TimeStart AND
TimeEnd)
- OR
(
(
SchedTimeEnd3 >
TimeStart AND
SchedTimeEnd3
- AND
(
(
SchedTimeEnd3 >
TimeStart AND
SchedTimeEnd3
- /*----------------------------------*/
- UNION
-- SCHEDULE.3 TO SCHEDULE.4
- /*----------------------------------*/
- SELECT
SubjectOfferingID,
CS.
TermID,
CS.
SubjectID,
S.
SubjectCode,
S.
SubjectTitle,
S.
CreditUnits,
CSec.
SectionTitle,
- CS.
Sched1,
fnRoomName2(
CS.
RoomID)
AS
Room_1,
- CS.
Sched2,
fnRoomName2(
CS.
RoomID2)
AS
Room_2,
- CS.
Sched3,
fnRoomName2(
CS.
RoomID3)
AS
Room_3,
- CS.
Sched4,
fnRoomName2(
CS.
RoomID4)
AS
Room_4,
- CS.
Sched5,
fnRoomName2(
CS.
RoomID5)
AS
Room_5,
- fnProgramCode(
CSec.
ProgramID)
AS
ProgramCode
- FROM
tblClassSchedule AS
CS LEFT
JOIN
- tblSubject AS
S ON
S.
SubjectID =
CS.
SubjectID LEFT
JOIN
- tblSection AS
CSec ON
CSec.
SectionID =
CS.
SectionID
- WHERE
(
CS.
TermID =
TermID)
AND
- (
CS.
TeacherID4 =
FacultyID)
AND
- (
Days4 LIKE
sDay)
AND
- (
(
SchedTimeStart4 BETWEEN
TimeStart AND
TimeEnd)
OR
(
SchedTimeEnd4 BETWEEN
TimeStart AND
TimeEnd)
- OR
(
(
SchedTimeEnd4 >
TimeStart AND
SchedTimeEnd4
- AND
(
(
SchedTimeEnd4 >
TimeStart AND
SchedTimeEnd4
- /*----------------------------------*/
- UNION
-- SCHEDULE.4 TO SCHEDULE.5
- /*----------------------------------*/
- SELECT
SubjectOfferingID,
CS.
TermID,
CS.
SubjectID,
S.
SubjectCode,
S.
SubjectTitle,
S.
CreditUnits,
CSec.
SectionTitle,
- CS.
Sched1,
fnRoomName2(
CS.
RoomID)
AS
Room_1,
- CS.
Sched2,
fnRoomName2(
CS.
RoomID2)
AS
Room_2,
- CS.
Sched3,
fnRoomName2(
CS.
RoomID3)
AS
Room_3,
- CS.
Sched4,
fnRoomName2(
CS.
RoomID4)
AS
Room_4,
- CS.
Sched5,
fnRoomName2(
CS.
RoomID5)
AS
Room_5,
- fnProgramCode(
CSec.
ProgramID)
AS
ProgramCode
- FROM
tblClassSchedule AS
CS LEFT
JOIN
- tblSubject AS
S ON
S.
SubjectID =
CS.
SubjectID LEFT
JOIN
- tblSection AS
CSec ON
CSec.
SectionID =
CS.
SectionID
- WHERE
(
CS.
TermID =
TermID)
AND
- (
CS.
TeacherID5 =
FacultyID)
AND
- (
Days5 LIKE
sDay)
AND
- (
(
SchedTimeStart5 BETWEEN
TimeStart AND
TimeEnd)
OR
(
SchedTimeEnd5 BETWEEN
TimeStart AND
TimeEnd)
- OR
(
(
SchedTimeEnd5 >
TimeStart AND
SchedTimeEnd5
- AND
(
(
SchedTimeEnd5 >
TimeStart AND
SchedTimeEnd5
- END
;
- CREATE
DEFINER =
'root'
@'localhost'
PROCEDURE
`GetRoomScheduleConflicts`
(
- TermID INT
,
- RoomID VARCHAR
(
15
)
,
- sDay VARCHAR
(
10
)
,
- TimeStart VARCHAR
(
10
)
,
- TimeEnd VARCHAR
(
10
)
- )
- DETERMINISTIC
- CONTAINS SQL
- SQL
SECURITY DEFINER
- COMMENT ''
- BEGIN
- SELECT
SubjectOfferingID,
CS.
TermID,
CS.
SubjectID,
S.
SubjectCode,
S.
SubjectTitle,
S.
CreditUnits,
CSec.
SectionTitle,
- CS.
Sched1,
fnRoomName2(
CS.
RoomID)
AS
Room_1,
- CS.
Sched2,
fnRoomName2(
CS.
RoomID2)
AS
Room_2,
- CS.
Sched3,
fnRoomName2(
CS.
RoomID3)
AS
Room_3,
- CS.
Sched4,
fnRoomName2(
CS.
RoomID4)
AS
Room_4,
- CS.
Sched5,
fnRoomName2(
CS.
RoomID5)
AS
Room_5,
- fnProgramCode(
CSec.
ProgramID)
AS
ProgramCode
- FROM
tblClassSchedule AS
CS LEFT
JOIN
- tblSubject AS
S ON
S.
SubjectID =
CS.
SubjectID LEFT
JOIN
- tblSection AS
CSec ON
CSec.
SectionID =
CS.
SectionID
- WHERE
(
CS.
TermID =
TermID)
AND
- (
CS.
RoomID =
RoomID)
AND
- (
CS.
Days LIKE
sDay)
AND
- (
(
SchedTimeStart BETWEEN
TimeStart AND
TimeEnd)
OR
(
SchedTimeEnd BETWEEN
TimeStart AND
TimeEnd)
- OR
(
(
SchedTimeEnd >
TimeStart AND
SchedTimeEnd
- AND
(
(
SchedTimeEnd >
TimeStart AND
SchedTimeEnd
- /*----------------------------------*/
- UNION
-- SCHEDULE.1 TO SCHEDULE.2
- /*----------------------------------*/
- SELECT
SubjectOfferingID,
CS.
TermID,
CS.
SubjectID,
S.
SubjectCode,
S.
SubjectTitle,
S.
CreditUnits,
CSec.
SectionTitle,
- CS.
Sched1,
fnRoomName2(
CS.
RoomID)
AS
Room_1,
- CS.
Sched2,
fnRoomName2(
CS.
RoomID2)
AS
Room_2,
- CS.
Sched3,
fnRoomName2(
CS.
RoomID3)
AS
Room_3,
- CS.
Sched4,
fnRoomName2(
CS.
RoomID4)
AS
Room_4,
- CS.
Sched5,
fnRoomName2(
CS.
RoomID5)
AS
Room_5,
- fnProgramCode(
CSec.
ProgramID)
AS
ProgramCode
- FROM
tblClassSchedule AS
CS LEFT
JOIN
- tblSubject AS
S ON
S.
SubjectID =
CS.
SubjectID LEFT
JOIN
- tblSection AS
CSec ON
CSec.
SectionID =
CS.
SectionID
- WHERE
(
CS.
TermID =
TermID)
AND
- (
CS.
RoomID2 =
RoomID)
AND
- (
Days2 LIKE
sDay)
AND
- (
(
SchedTimeStart2 BETWEEN
TimeStart AND
TimeEnd)
OR
(
SchedTimeEnd2 BETWEEN
TimeStart AND
TimeEnd)
- OR
(
(
SchedTimeEnd2 >
TimeStart AND
SchedTimeEnd2
- AND
(
(
SchedTimeEnd2 >
TimeStart AND
SchedTimeEnd2
- /*----------------------------------*/
- UNION
-- SCHEDULE.2 TO SCHEDULE.3
- /*----------------------------------*/
- SELECT
SubjectOfferingID,
CS.
TermID,
CS.
SubjectID,
S.
SubjectCode,
S.
SubjectTitle,
S.
CreditUnits,
CSec.
SectionTitle,
- CS.
Sched1,
fnRoomName2(
CS.
RoomID)
AS
Room_1,
- CS.
Sched2,
fnRoomName2(
CS.
RoomID2)
AS
Room_2,
- CS.
Sched3,
fnRoomName2(
CS.
RoomID3)
AS
Room_3,
- CS.
Sched4,
fnRoomName2(
CS.
RoomID4)
AS
Room_4,
- CS.
Sched5,
fnRoomName2(
CS.
RoomID5)
AS
Room_5,
- fnProgramCode(
CSec.
ProgramID)
AS
ProgramCode
- FROM
tblClassSchedule AS
CS LEFT
JOIN
- tblSubject AS
S ON
S.
SubjectID =
CS.
SubjectID LEFT
JOIN
- tblSection AS
CSec ON
CSec.
SectionID =
CS.
SectionID
- WHERE
(
CS.
TermID =
TermID)
AND
- (
CS.
RoomID3 =
RoomID)
AND
- (
Days3 LIKE
sDay)
AND
- (
(
SchedTimeStart3 BETWEEN
TimeStart AND
TimeEnd)
OR
(
SchedTimeEnd3 BETWEEN
TimeStart AND
TimeEnd)
- OR
(
(
SchedTimeEnd3 >
TimeStart AND
SchedTimeEnd3
- AND
(
(
SchedTimeEnd3 >
TimeStart AND
SchedTimeEnd3
- /*----------------------------------*/
- UNION
-- SCHEDULE.3 TO SCHEDULE.4
- /*----------------------------------*/
- SELECT
SubjectOfferingID,
CS.
TermID,
CS.
SubjectID,
S.
SubjectCode,
S.
SubjectTitle,
S.
CreditUnits,
CSec.
SectionTitle,
- CS.
Sched1,
fnRoomName2(
CS.
RoomID)
AS
Room_1,
- CS.
Sched2,
fnRoomName2(
CS.
RoomID2)
AS
Room_2,
- CS.
Sched3,
fnRoomName2(
CS.
RoomID3)
AS
Room_3,
- CS.
Sched4,
fnRoomName2(
CS.
RoomID4)
AS
Room_4,
- CS.
Sched5,
fnRoomName2(
CS.
RoomID5)
AS
Room_5,
- fnProgramCode(
CSec.
ProgramID)
AS
ProgramCode
- FROM
tblClassSchedule AS
CS LEFT
JOIN
- tblSubject AS
S ON
S.
SubjectID =
CS.
SubjectID LEFT
JOIN
- tblSection AS
CSec ON
CSec.
SectionID =
CS.
SectionID
- WHERE
(
CS.
TermID =
TermID)
AND
- (
CS.
RoomID4 =
RoomID)
AND
- (
Days4 LIKE
sDay)
AND
- (
(
SchedTimeStart4 BETWEEN
TimeStart AND
TimeEnd)
OR
(
SchedTimeEnd4 BETWEEN
TimeStart AND
TimeEnd)
- OR
(
(
SchedTimeEnd4 >
TimeStart AND
SchedTimeEnd4
- AND
(
(
SchedTimeEnd4 >
TimeStart AND
SchedTimeEnd4
- /*----------------------------------*/
- UNION
-- SCHEDULE.4 TO SCHEDULE.5
- /*----------------------------------*/
- SELECT
SubjectOfferingID,
CS.
TermID,
CS.
SubjectID,
S.
SubjectCode,
S.
SubjectTitle,
S.
CreditUnits,
CSec.
SectionTitle,
- CS.
Sched1,
fnRoomName2(
CS.
RoomID)
AS
Room_1,
- CS.
Sched2,
fnRoomName2(
CS.
RoomID2)
AS
Room_2,
- CS.
Sched3,
fnRoomName2(
CS.
RoomID3)
AS
Room_3,
- CS.
Sched4,
fnRoomName2(
CS.
RoomID4)
AS
Room_4,
- CS.
Sched5,
fnRoomName2(
CS.
RoomID5)
AS
Room_5,
- fnProgramCode(
CSec.
ProgramID)
AS
ProgramCode
- FROM
tblClassSchedule AS
CS LEFT
JOIN
- tblSubject AS
S ON
S.
SubjectID =
CS.
SubjectID LEFT
JOIN
- tblSection AS
CSec ON
CSec.
SectionID =
CS.
SectionID
- WHERE
(
CS.
TermID =
TermID)
AND
- (
CS.
RoomID5 =
RoomID)
AND
- (
Days5 LIKE
sDay)
AND
- (
(
SchedTimeStart5 BETWEEN
TimeStart AND
TimeEnd)
OR
(
SchedTimeEnd5 BETWEEN
TimeStart AND
TimeEnd)
- OR
(
(
SchedTimeEnd5 >
TimeStart AND
SchedTimeEnd5
- AND
(
(
SchedTimeEnd5 >
TimeStart AND
SchedTimeEnd5
- END
;
- /*=================== STORED FUNCTION ===========================*/
- CREATE
DEFINER =
'root'
@'localhost'
FUNCTION
`fnProgramName`
(
ProgID INT
)
- RETURNS
VARCHAR
(
100
)
CHARSET utf8
- DETERMINISTIC
- CONTAINS SQL
- SQL
SECURITY DEFINER
- COMMENT ''
- BEGIN
- DECLARE
ProgramName VARCHAR
(
100
)
;
- SELECT
Programs.
ProgName INTO
ProgramName FROM
tblPrograms AS
Programs WHERE
Programs.
ProgID =
ProgID
- LIMIT
1
;
- RETURN
ifnull(
ProgramName,
''
)
;
- END
;
- CREATE
DEFINER =
'root'
@'localhost'
FUNCTION
`fnRoomName2`
(
- sRoomID VARCHAR
(
45
)
- )
- RETURNS
VARCHAR
(
60
)
CHARSET utf8
- DETERMINISTIC
- CONTAINS SQL
- SQL
SECURITY DEFINER
- COMMENT ''
- BEGIN
- DECLARE
RoomName VARCHAR
(
60
)
;
- SELECT
CONCAT(
fnBuildingAcronym(
R.
BldgID)
,
'-'
,
R.
Room)
INTO
RoomName FROM
tblRoom AS
R
- WHERE
R.
RoomID =
sRoomID LIMIT
1
;
- RETURN
RoomName;
- END
;