Code Coverage |
||||||||||
Classes and Traits |
Functions and Methods |
Lines |
||||||||
| Total | |
0.00% |
0 / 1 |
|
0.00% |
0 / 95 |
CRAP | |
0.00% |
0 / 1574 |
| TimetableService | |
0.00% |
0 / 1 |
|
0.00% |
0 / 95 |
127806.00 | |
0.00% |
0 / 1574 |
| __construct | |
0.00% |
0 / 1 |
2.00 | |
0.00% |
0 / 3 |
|||
| __clone | |
0.00% |
0 / 1 |
2.00 | |
0.00% |
0 / 2 |
|||
| getInstance | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 5 |
|||
| copyTimetable | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 16 |
|||
| deleteTimetable | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 15 |
|||
| getTimeTableAssignedBathes | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 23 |
|||
| getTimetableHours | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 11 |
|||
| getTimetableDayNames | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 11 |
|||
| getDayNameByOrder | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 11 |
|||
| getTimetableDayOrders | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 11 |
|||
| addBatchMasterTimetable | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 10 |
|||
| getBatchMasterTimetableId | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 11 |
|||
| addBatchMasterTimetableHour | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 10 |
|||
| updateStartAndEndTimeForAnHourInMasterTimetable | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 11 |
|||
| updateStartTimeAndEndTimeOfAnHourInMasterTimetableWithBatchAndSemIds | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 10 |
|||
| deleteBatchMasterTimetableHourById | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 10 |
|||
| getDayName | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 13 |
|||
| updateMasterTimetableDayName | |
0.00% |
0 / 1 |
42.00 | |
0.00% |
0 / 23 |
|||
| getAssignedBatchMasterTimetableSbsIDs | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 20 |
|||
| getStartTimeAndEndTimeOfAnHourInMasterTimetable | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 14 |
|||
| addMasterDayOrder | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 11 |
|||
| deleteMasterDayOrderBycoursetypeId | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 10 |
|||
| getMasterDayOrderName | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 19 |
|||
| getMasterDayOrderId | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 12 |
|||
| getMasterDayOrder | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 11 |
|||
| updateMasterDayOrder | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 17 |
|||
| getDaysBetweenDates | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 15 |
|||
| copyMasterTimetable | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 23 |
|||
| getdayOrderNameByTimetableDate | |
0.00% |
0 / 1 |
30.00 | |
0.00% |
0 / 24 |
|||
| getDayOrderByDate | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 10 |
|||
| getTimetableEndDate | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 10 |
|||
| addCourseFileTimetableDayOrder | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 12 |
|||
| checkDayOrderCourseFileTimetable | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 18 |
|||
| updateCourseFileTimetableDayOrder | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 8 |
|||
| getCourseFileTimetableDayOrder | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 8 |
|||
| getCourseFileTimetableDayOrderByPsId | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 8 |
|||
| getCourseFileTimetableDayOrderByDayId | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 8 |
|||
| getCourseFileTimetableDayOrderByDayIdpsId | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 8 |
|||
| deleteCourseFileTimetableDayOrder | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 8 |
|||
| checkHourAllowed | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 13 |
|||
| checkHourIsSuspended | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 13 |
|||
| updateHourTime | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 9 |
|||
| getHourSuspendedStaffDetails | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 11 |
|||
| getAssignedHourDetails | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 18 |
|||
| getTotalHour | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 13 |
|||
| getHourAssignedStaffs | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 11 |
|||
| getHourDetails | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 13 |
|||
| deleteBatchTimetableBySbsId | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 9 |
|||
| getMyTimeTableByStaffId | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 11 |
|||
| getBatchTimeTableByBatchAndSem | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 11 |
|||
| checkStaffTimetableExists | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 13 |
|||
| getTotalWorkingDays | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 20 |
|||
| checkIfTimetableCanBeCopiedAndReturnErrors | |
0.00% |
0 / 1 |
2.00 | |
0.00% |
0 / 7 |
|||
| getDepartmentWiseTimeTable | |
0.00% |
0 / 1 |
272.00 | |
0.00% |
0 / 54 |
|||
| getBatchWiseTimetableBetweenToDates | |
0.00% |
0 / 1 |
30.00 | |
0.00% |
0 / 34 |
|||
| getHolidays | |
0.00% |
0 / 1 |
240.00 | |
0.00% |
0 / 39 |
|||
| getSuspendedHoursBetweenDates | |
0.00% |
0 / 1 |
110.00 | |
0.00% |
0 / 26 |
|||
| getFacultyWiseTimeTable | |
0.00% |
0 / 1 |
552.00 | |
0.00% |
0 / 70 |
|||
| getDistinctBatchIdsFromTimeTable | |
0.00% |
0 / 1 |
156.00 | |
0.00% |
0 / 35 |
|||
| getStaffTimetableTimeBetweenDates | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 13 |
|||
| getStaffTimetableInDateRange | |
0.00% |
0 / 1 |
110.00 | |
0.00% |
0 / 34 |
|||
| getBatchTimetableTimeBetweenDates | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 13 |
|||
| getBatchTimetableInDateRange | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 20 |
|||
| getBatchTimetableInDateRangeWithoutTimeTableMethod | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 19 |
|||
| isTimetableExchangeEnabled | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 11 |
|||
| getHourwiseTimeRangeOfTimetable | |
0.00% |
0 / 1 |
42.00 | |
0.00% |
0 / 29 |
|||
| isNewTimetableTobeLocked | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 10 |
|||
| updateIsAllowed | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 9 |
|||
| updateIsAllowedForAllHours | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 9 |
|||
| swapTimetable | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 17 |
|||
| getTimeTableDetailsOfBatchByDate | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 9 |
|||
| updateTimetableMeetingId | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 16 |
|||
| getHourDetailsByMeetingId | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 8 |
|||
| getStaffTimetableInDateRangeByCourseTypeId | |
0.00% |
0 / 1 |
110.00 | |
0.00% |
0 / 34 |
|||
| getAllTimetableHours | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 10 |
|||
| getBatchTimetableCount | |
0.00% |
0 / 1 |
56.00 | |
0.00% |
0 / 31 |
|||
| getTimetableDetails | |
0.00% |
0 / 1 |
110.00 | |
0.00% |
0 / 52 |
|||
| getBatchTimetableByRequest | |
0.00% |
0 / 1 |
156.00 | |
0.00% |
0 / 67 |
|||
| deleteBatchTimetableById | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 16 |
|||
| getBatchTimetableInDateRangeBySbsID | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 23 |
|||
| getCourseFileTimeTable | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 10 |
|||
| getCourseFileTimeTableDetails | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 12 |
|||
| getBatchTimetableWeeks | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 11 |
|||
| getPseudoSubjectTimetableWeeks | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 11 |
|||
| getCourseFilePseudoSubjectTimeTableDetails | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 16 |
|||
| getdayOrderNameByTimetableDateForPseudoSubject | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 20 |
|||
| getCourseFilePsTimeTable | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 10 |
|||
| getCourseFileConstants | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 10 |
|||
| getCourseDiaryConstants | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 10 |
|||
| updateCourseFileConstants | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 9 |
|||
| updateCourseDiaryConstants | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 9 |
|||
| getCourseFileConstantsForRender | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 8 |
|||
| getCourseDiaryConstantsForRender | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 8 |
|||
| getTimetableDetailsByRequest | |
0.00% |
0 / 1 |
306.00 | |
0.00% |
0 / 43 |
|||
| getTimeTableAssignedToStaffWithStaffDetails | |
0.00% |
0 / 1 |
132.00 | |
0.00% |
0 / 27 |
|||
| <?php | |
| namespace com\linways\core\ams\professional\service; | |
| use com\linways\core\ams\professional\constant\Calendar; | |
| use com\linways\core\ams\professional\constant\courseFileAndDiary\CourseFileAndDiary; | |
| use com\linways\core\ams\professional\constant\SettingsConstants; | |
| use com\linways\core\ams\professional\dto\Batch; | |
| use com\linways\core\ams\professional\dto\Timetable; | |
| use com\linways\core\ams\professional\dto\CopyTimetable; | |
| use com\linways\core\ams\professional\dto\BatchTimetable; | |
| use com\linways\core\ams\professional\dto\MasterDayOrder; | |
| use com\linways\core\ams\professional\request\FacultyTimeTableResponse; | |
| use com\linways\core\ams\professional\request\GetDepartmentWiseTimeTableRequest; | |
| use com\linways\core\ams\professional\request\GetFacultyWiseTimeTableRequest; | |
| use com\linways\core\ams\professional\request\GetHolidaysRequest; | |
| use com\linways\core\ams\professional\request\GetSuspendedHoursRequest; | |
| use com\linways\core\ams\professional\request\GetTimeTableBetweenTwoDatesRequest; | |
| use com\linways\core\ams\professional\service\BatchService; | |
| use com\linways\core\ams\professional\dto\SettingsConstents; | |
| use com\linways\core\ams\professional\service\CalendarService; | |
| use com\linways\core\ams\professional\dto\BatchMasterTimetable; | |
| use com\linways\core\ams\professional\service\AttendanceService; | |
| use com\linways\core\ams\professional\dto\BatchMasterTimetableHour; | |
| use com\linways\core\ams\professional\mapper\TimetableServiceMapper; | |
| use com\linways\core\ams\professional\exception\ProfessionalException; | |
| use com\linways\core\ams\professional\dto\BatchTimetableCoursefileDayOrder; | |
| use com\linways\core\ams\professional\util\CommonUtil; | |
| use TheSeer\Tokenizer\Exception; | |
| use stdClass; | |
| class TimetableService extends BaseService | |
| { | |
| // /Condition 1 - Presence of a static member variable | |
| private static $_instance = null; | |
| private $mapper = []; | |
| // /Condition 2 - Locked down the constructor | |
| private function __construct() | |
| { | |
| $this->mapper = TimetableServiceMapper::getInstance()->getMapper(); | |
| } | |
| // Prevent any oustide instantiation of this class | |
| // /Condition 3 - Prevent any object or instance of that class to be cloned | |
| private function __clone() | |
| { | |
| } | |
| // Prevent any copy of this object | |
| // /Condition 4 - Have a single globally accessible static method | |
| public static function getInstance() | |
| { | |
| if (!is_object(self::$_instance)) // or if( is_null(self::$_instance) ) or if( self::$_instance == null ) | |
| self::$_instance = new self(); | |
| return self::$_instance; | |
| } | |
| /** | |
| * Copy timetable for given department and batch | |
| * @param CopyTimetable $copyTimetable | |
| * @param int $deptId | |
| * @param int $batchId | |
| */ | |
| public function copyTimetable($copyTimetable, $deptId, $batchId) | |
| { | |
| //Deleting existing timetable entry if any. | |
| $this->deleteTimetable($copyTimetable->toDate, $deptId, $batchId); | |
| if ($deptId != 0) { | |
| if ($batchId != 0) { | |
| $sqlCond = "AND batchID = " . $this->realEscapeString($batchId) . ""; | |
| } else { | |
| $sqlCond = "AND batchID IN(" . BatchService::getInstance()->getBatchIDsByDeptID($deptId) . ")"; | |
| } | |
| } | |
| $sql = "insert into batch_timetable (batchID, subjectID, dayID, hourID, semID, sbsID, is_allowed, subbatchID, stratTime, endTime, timetableDate) SELECT batchID, subjectID, WEEKDAY('" . $this->realEscapeString($copyTimetable->toDate) . "')+1, hourID, semID, sbsID, is_allowed, subbatchID, stratTime, endTime, '" . $this->realEscapeString($copyTimetable->toDate) . "' from batch_timetable where timetableDate = '" . $this->realEscapeString($copyTimetable->fromDate) . "' $sqlCond"; | |
| try { | |
| return $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| public function deleteTimetable($date, $deptId, $batchId) | |
| { | |
| if ($deptId != 0) { | |
| if ($batchId != 0) { | |
| $sqlCond = "AND batchID = " . $this->realEscapeString($batchId) . ""; | |
| } else { | |
| $sqlCond = "AND batchID IN(" . BatchService::getInstance()->getBatchIDsByDeptID($deptId) . ")"; | |
| } | |
| } | |
| $sql = "DELETE FROM batch_timetable WHERE timetableDate = '" . $this->realEscapeString($date) . "' $sqlCond"; | |
| try { | |
| return $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * get timetable assigned batch details | |
| * @param string $timeTableDate | |
| * @param int $deptId | |
| * @return array|Object|object[] | |
| * @throws ProfessionalException | |
| */ | |
| public function getTimeTableAssignedBathes($timeTableDate, $deptId = 0) | |
| { | |
| $timeTableDate = $this->realEscapeString($timeTableDate); | |
| $deptId = $this->realEscapeString($deptId); | |
| $batchDetails = []; | |
| $sqlEodExBatch = "SELECT batchIDs FROM eod_settings"; | |
| $eodExcludedBatches = $this->executeQueryForObject($sqlEodExBatch)->batchIDs; | |
| $sql = "SELECT DISTINCT(t1.batchID), t2.batchName, t2.semID, t2.deptID | |
| FROM batch_timetable t1, batches t2 | |
| WHERE t1.batchID = t2.batchID | |
| AND t1.timetableDate ='" . date('Y-m-d', strtotime($timeTableDate)) . "' "; | |
| if ($deptId) { | |
| $sql .= "AND t2.deptID=" . $deptId; | |
| } | |
| if ($eodExcludedBatches) { | |
| $sql .= " AND t1.batchID NOT IN(" . $eodExcludedBatches . ")"; | |
| } | |
| try { | |
| $batchDetails = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $batchDetails; | |
| } | |
| /** | |
| * Get timetable hours | |
| * @param int $noOfHours | |
| * @return object|array|$objectList[] | |
| * @throws ProfessionalException | |
| */ | |
| public function getTimetableHours($noOfHours) | |
| { | |
| $noOfHours = $this->realEscapeString($noOfHours); | |
| $hours = []; | |
| $sql = "SELECT * FROM timetable_hour hour WHERE hour.order<=$noOfHours ORDER BY hour.order ASC"; | |
| try { | |
| $hours = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $hours; | |
| } | |
| /** | |
| * Get timetable dayNames corresponding to no. of days | |
| * @return object|array|$objectList[] | |
| * @throws ProfessionalException | |
| */ | |
| public function getTimetableDayNames() | |
| { | |
| $dayNames = []; | |
| //Get no. of timetable days. | |
| $timetableDaysNo = CommonService::getInstance()->getSettings(SettingsConstents::DAY_ORDER_TIMETABLE, SettingsConstents::NO_OF_DAY_ORDER); | |
| $sql = "SELECT * FROM timetable_dayName dayName WHERE dayName.order<=$timetableDaysNo ORDER BY dayName.order ASC"; | |
| try { | |
| $dayNames = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $dayNames; | |
| } | |
| /** | |
| * Get dayname corresponding to order | |
| * @param int $order | |
| * @return string $dayName | |
| * @throws ProfessionalException | |
| */ | |
| public function getDayNameByOrder($order) | |
| { | |
| $dayName = ""; | |
| $order = $this->realEscapeString($order); | |
| $sql = "SELECT dayName.name FROM timetable_dayName dayName WHERE dayName.order=$order"; | |
| try { | |
| $dayName = $this->executeQueryForObject($sql)->name; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $dayName; | |
| } | |
| /** | |
| * Get timetable dayorders corresponding to no. of days | |
| * @return object|array|$objectList[] | |
| * @throws ProfessionalException | |
| */ | |
| public function getTimetableDayOrders() | |
| { | |
| $dayOrders = []; | |
| //Get no. of timetable days. | |
| $timetableDaysNo = CommonService::getInstance()->getSettings(SettingsConstents::DAY_ORDER_TIMETABLE, SettingsConstents::NO_OF_DAY_ORDER); | |
| $sql = "SELECT * FROM timetable_dayOrder day WHERE day.order<=$timetableDaysNo ORDER BY day.order ASC"; | |
| try { | |
| $dayOrders = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $dayOrders; | |
| } | |
| /** | |
| * Add batch master timetable | |
| * @param BatchMasterTimetable $batchMasterTimetable | |
| * @return object|NULL|$objectList[]|NULL | |
| * @throws ProfessionalException | |
| */ | |
| public function addBatchMasterTimetable($batchMasterTimetable) | |
| { | |
| $batchMasterTimetable = $this->realEscapeObject($batchMasterTimetable); | |
| $sql = "INSERT INTO batchMasterTimetable(batchId,semId,dayOrder,dayName,createdBy,createdDate,updatedBy,updatedDate) VALUES($batchMasterTimetable->batchId, $batchMasterTimetable->semId, $batchMasterTimetable->dayOrder,'$batchMasterTimetable->dayName', $batchMasterTimetable->createdBy, utc_timestamp(), $batchMasterTimetable->updatedBy, utc_timestamp())"; | |
| try { | |
| return $this->executeQueryForObject($sql, TRUE); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException ($e->getCode(), $e->getMessage()); | |
| } | |
| return null; | |
| } | |
| /** | |
| * Get batch master timetable id | |
| * @param BatchMasterTimetable $batchMasterTimetable | |
| * @return int batchMasterTimetableId | |
| * @throws ProfessionalException | |
| */ | |
| public function getBatchMasterTimetableId($batchMasterTimetable) | |
| { | |
| $batchMasterTimetable = $this->realEscapeObject($batchMasterTimetable); | |
| $sql = "SELECT id FROM batchMasterTimetable WHERE batchId = $batchMasterTimetable->batchId | |
| AND semId = $batchMasterTimetable->semId AND dayOrder = $batchMasterTimetable->dayOrder"; | |
| try { | |
| return $this->executeQueryForObject($sql)->id; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException ($e->getCode(), $e->getMessage()); | |
| } | |
| return null; | |
| } | |
| /** | |
| * Add batch master timetable hour. | |
| * @param BatchMasterTimetableHour $batchMasterTimetableHour | |
| * @return Object|null $batchMasterTimetableHourId | |
| * @throws ProfessionalException | |
| */ | |
| public function addBatchMasterTimetableHour($batchMasterTimetableHour) | |
| { | |
| $batchMasterTimetableHour = $this->realEscapeObject($batchMasterTimetableHour); | |
| $sql = "INSERT INTO batchMasterTimetableHour(batchMasterTimetableId, hourId, sbsId, subjectId, subbatchID, startTime, endTime, createdBy, createdDate, updatedBy, updatedDate) VALUES($batchMasterTimetableHour->batchMasterTimetableId, $batchMasterTimetableHour->hourId, $batchMasterTimetableHour->sbsId, $batchMasterTimetableHour->subjectId, $batchMasterTimetableHour->subbatchID, '$batchMasterTimetableHour->startTime', '$batchMasterTimetableHour->endTime', $batchMasterTimetableHour->createdBy, utc_timestamp(), $batchMasterTimetableHour->updatedBy, utc_timestamp())"; | |
| try { | |
| return $this->executeQueryForObject($sql, TRUE); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException ($e->getCode(), $e->getMessage()); | |
| } | |
| return null; | |
| } | |
| /** | |
| * Update all start time and end time of an hour | |
| * | |
| * @param BatchMasterTimetableHour $updateRequest | |
| * @return bool | |
| * @throws ProfessionalException | |
| */ | |
| public function updateStartAndEndTimeForAnHourInMasterTimetable($updateRequest) | |
| { | |
| $updateRequest = $this->realEscapeObject($updateRequest); | |
| $sql = ""; | |
| $sql = "UPDATE `batchMasterTimetableHour` SET `startTime`='$updateRequest->startTime', `endTime`='$updateRequest->endTime', updatedBy = $updateRequest->updatedBy, updatedDate=utc_timestamp() WHERE batchMasterTimetableId = $updateRequest->batchMasterTimetableId AND hourId=$updateRequest->hourId"; | |
| try { | |
| $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException ($e->getCode(), $e->getMessage()); | |
| } | |
| return true; | |
| } | |
| /** | |
| * Undocumented function | |
| * | |
| * @param [type] $request | |
| * @return bool | |
| * @throws ProfessionalException | |
| */ | |
| public function updateStartTimeAndEndTimeOfAnHourInMasterTimetableWithBatchAndSemIds($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $sql = "UPDATE batchMasterTimetable master inner join batchMasterTimetableHour hour ON (master.id = hour.batchMasterTimetableId AND master.dayOrder=$request->dayOrder AND master.batchId=$request->batchId AND master.semId=$request->semId AND hour.hourId=$request->hourId) SET hour.startTime='$request->startTime', hour.endTime='$request->endTime'"; | |
| try { | |
| $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return true; | |
| } | |
| /** | |
| * Delete master timetable hour by id. | |
| * @param BatchMasterTimetableHour $batchMasterTimetableHour | |
| * @return boolean | |
| * @throws ProfessionalException | |
| */ | |
| public function deleteBatchMasterTimetableHourById($batchMasterTimetableHour) | |
| { | |
| $batchMasterTimetableHour = $this->realEscapeObject($batchMasterTimetableHour); | |
| $sql = "DELETE FROM batchMasterTimetableHour WHERE batchMasterTimetableId = $batchMasterTimetableHour->batchMasterTimetableId AND hourId = $batchMasterTimetableHour->hourId AND sbsId = $batchMasterTimetableHour->sbsId"; | |
| try { | |
| $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException ($e->getCode(), $e->getMessage()); | |
| } | |
| return true; | |
| } | |
| /** | |
| * Get dayname mapped to a dayOrder for a batch | |
| * @param int $dayOrder | |
| * @param int $batchId | |
| * @param int $semId | |
| * @return string $dayName | |
| * @throws ProfessionalException | |
| */ | |
| public function getDayName($dayOrder, $batchId, $semId) | |
| { | |
| $dayOrder = $this->realEscapeString($dayOrder); | |
| $batchId = $this->realEscapeString($batchId); | |
| $semId = $this->realEscapeString($semId); | |
| $dayName = ""; | |
| $sql = "SELECT days.name FROM batchMasterTimetable bmaster INNER JOIN timetable_dayName days ON bmaster.dayName = days.order WHERE bmaster.dayOrder=$dayOrder AND bmaster.batchId=$batchId AND bmaster.semId=$semId"; | |
| try { | |
| $dayName = $this->executeQueryForObject($sql)->name; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $dayName; | |
| } | |
| /** | |
| * @param $masterDayOrder | |
| * @return bool | |
| * @throws ProfessionalException | |
| */ | |
| public function updateMasterTimetableDayName($masterDayOrder) | |
| { | |
| $masterDayOrder = $this->realEscapeObject($masterDayOrder); | |
| $sqlCondition = ''; | |
| if ($masterDayOrder->courseTypeId && $masterDayOrder->batchstartYear) { | |
| $batches = BatchService::getInstance()->getBatchesByCourseTypeId($masterDayOrder->courseTypeId, $masterDayOrder->batchstartYear); | |
| $conditions = []; | |
| foreach ($batches as $batch) { | |
| $conditions[] = "(batchId = " . $batch->id . " AND semId = " . $batch->currentSemId . ")"; | |
| } | |
| if (!empty ($conditions)) { | |
| $sqlCondition = implode(' OR ', $conditions); | |
| $sql = "UPDATE batchMasterTimetable SET dayName = " . $masterDayOrder->dayNameId . " WHERE dayOrder = " . $masterDayOrder->dayOrder . " AND (" . $sqlCondition . ")"; | |
| } | |
| } else { | |
| $sql = "UPDATE batchMasterTimetable SET dayName = " . $masterDayOrder->dayNameId . " WHERE dayOrder = " . $masterDayOrder->dayOrder . ""; | |
| } | |
| try { | |
| $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return true; | |
| } | |
| /** | |
| * Get assigned hours in master timetable. | |
| * @param int $batchId | |
| * @param int $semId | |
| * @param int $dayOrder | |
| * @param int $hourId | |
| * @return array | |
| * @throws ProfessionalException | |
| */ | |
| public function getAssignedBatchMasterTimetableSbsIDs($batchId, $semId, $dayOrder, $hourId) | |
| { | |
| $batchId = $this->realEscapeString($batchId); | |
| $semId = $this->realEscapeString($semId); | |
| $dayOrder = $this->realEscapeString($dayOrder); | |
| $hourId = $this->realEscapeString($hourId); | |
| $sbsIds = []; | |
| $sbsString = ""; | |
| $sql = "SELECT GROUP_CONCAT(hour.sbsId) as sbsIds FROM batchMasterTimetable master inner join batchMasterTimetableHour hour ON master.id = hour.batchMasterTimetableId WHERE master.dayOrder=$dayOrder AND master.batchId=$batchId AND master.semId=$semId AND hour.hourId=$hourId"; | |
| try { | |
| $sbsString = $this->executeQueryForObject($sql)->sbsIds; | |
| if ($sbsString != NULL) { | |
| $sbsIds = explode(",", $sbsString); | |
| } else { | |
| return NULL; | |
| } | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $sbsIds; | |
| } | |
| /** | |
| * Undocumented function | |
| * | |
| * @param int $batchId | |
| * @param int $semId | |
| * @param int $dayOrder | |
| * @param int $hourId | |
| * @return Object | |
| * @throws ProfessionalException | |
| */ | |
| public function getStartTimeAndEndTimeOfAnHourInMasterTimetable($batchId, $semId, $dayOrder, $hourId) | |
| { | |
| $batchId = $this->realEscapeString($batchId); | |
| $semId = $this->realEscapeString($semId); | |
| $dayOrder = $this->realEscapeString($dayOrder); | |
| $hourId = $this->realEscapeString($hourId); | |
| $startAndEndTime = ""; | |
| $sql = "SELECT hour.startTime, hour.endTime FROM batchMasterTimetable master inner join batchMasterTimetableHour hour ON master.id = hour.batchMasterTimetableId WHERE master.dayOrder=$dayOrder AND master.batchId=$batchId AND master.semId=$semId AND hour.hourId=$hourId LIMIT 1"; | |
| try { | |
| $startAndEndTime = $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $startAndEndTime; | |
| } | |
| /** | |
| * Add masterDayOrder | |
| * @param MasterDayOrder $masterDayOrder | |
| * @return Object|int | |
| * @throws ProfessionalException | |
| */ | |
| public function addMasterDayOrder($masterDayOrder) | |
| { | |
| $masterDayOrder = $this->realEscapeObject($masterDayOrder); | |
| $masterDayOrder->courseTypeId = $masterDayOrder->courseTypeId ? $masterDayOrder->courseTypeId : 'NULL'; | |
| $masterDayOrder->batchStartYear = $masterDayOrder->batchStartYear ? $masterDayOrder->batchStartYear : 'NULL'; | |
| $sql = "INSERT INTO masterDayOrder (dayOrder, dayNameId, batchCourseTypeId, courseTypeId, batchStartYear, createdBy, createdDate, updatedBy, updatedDate) VALUES(" . $masterDayOrder->dayOrder . ", " . $masterDayOrder->dayNameId . ", " . $masterDayOrder->batchCoursetypeId . ", " . $masterDayOrder->courseTypeId . ", " . $masterDayOrder->batchStartYear . ", " . $masterDayOrder->createdBy . ", utc_timestamp(), " . $masterDayOrder->updatedBy . ", utc_timestamp())"; | |
| try { | |
| return $this->executeQueryForObject($sql, TRUE); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException ($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * Delete master day order by batchCourseTypeId | |
| * @param int $batchCourseTypeId | |
| * @return bool | |
| * @throws ProfessionalException | |
| */ | |
| public function deleteMasterDayOrderBycoursetypeId($batchCourseTypeId) | |
| { | |
| $batchCourseTypeId = $this->realEscapeString($batchCourseTypeId); | |
| $sql = "DELETE FROM masterDayOrder WHERE batchCourseTypeId=$batchCourseTypeId"; | |
| try { | |
| $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException ($e->getCode(), $e->getMessage()); | |
| } | |
| return true; | |
| } | |
| /** | |
| * Get day order name by daynameId , $id (id can be batchCourseTypeId or courseTypeId) | |
| * @param int $dayNameId | |
| * @param $id | |
| * @param null $batchStartYear | |
| * @return int dayOrder | |
| * @throws ProfessionalException | |
| */ | |
| public function getMasterDayOrderName($dayNameId, $id, $batchStartYear = null) | |
| { | |
| $dayOrder = NULL; | |
| $batchStartYear = $this->realEscapeString($batchStartYear); | |
| $dayNameId = $this->realEscapeString($dayNameId); | |
| $id = $this->realEscapeString($id); | |
| if ($batchStartYear) { | |
| $courseTypeId = $id; | |
| $sql = "SELECT dayOrder.name FROM masterDayOrder master INNER JOIN timetable_dayOrder dayOrder ON master.dayOrder = dayOrder.order WHERE master.courseTypeId = " . $courseTypeId . " AND master.batchStartYear = " . $batchStartYear . " AND master.dayNameId = " . $dayNameId . ""; | |
| } else { | |
| $batchCourseTypeId = $id; | |
| $sql = "SELECT dayOrder.name FROM masterDayOrder master INNER JOIN timetable_dayOrder dayOrder ON master.dayOrder = dayOrder.order WHERE master.batchCourseTypeId = " . $batchCourseTypeId . " AND master.dayNameId = " . $dayNameId . ""; | |
| } | |
| try { | |
| $dayOrder = $this->executeQueryForObject($sql)->name; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException ($e->getCode(), $e->getMessage()); | |
| } | |
| return $dayOrder; | |
| } | |
| /** | |
| * Get day orderId by batchcoursetypeId and daynameId | |
| * @param int $batchCourseTypeId | |
| * @param int $dayNameId | |
| * @return int dayOrder | |
| * @throws ProfessionalException | |
| */ | |
| public function getMasterDayOrderId($batchCourseTypeId, $dayNameId) | |
| { | |
| $batchCourseTypeId = $this->realEscapeString($batchCourseTypeId); | |
| $dayNameId = $this->realEscapeString($dayNameId); | |
| $dayOrder = NULL; | |
| $sql = "SELECT dayOrder FROM masterDayOrder WHERE batchCourseTypeId=$batchCourseTypeId AND dayNameId = $dayNameId"; | |
| try { | |
| $dayOrder = $this->executeQueryForObject($sql)->dayOrder; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException ($e->getCode(), $e->getMessage()); | |
| } | |
| return $dayOrder; | |
| } | |
| /** | |
| * Get day orderId by masterDayOrder | |
| * @param int $masterDayOrder | |
| * @return Object|null dayOrder | |
| * @throws ProfessionalException | |
| */ | |
| public function getMasterDayOrder($masterDayOrder) | |
| { | |
| $masterDayOrder = $this->realEscapeObject($masterDayOrder); | |
| $dayOrder = NULL; | |
| $sql = "SELECT dayOrder FROM masterDayOrder WHERE courseTypeId = " . $masterDayOrder->courseTypeId . " AND batchStartYear = " . $masterDayOrder->batchStartYear . " AND dayNameId = " . $masterDayOrder->dayNameId . ""; | |
| try { | |
| $dayOrder = $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException ($e->getCode(), $e->getMessage()); | |
| } | |
| return $dayOrder; | |
| } | |
| /** | |
| * Update dayOrder | |
| * @param MasterDayOrder $masterDayOrder | |
| * @return boolean | |
| * @throws ProfessionalException | |
| */ | |
| public function updateMasterDayOrder($masterDayOrder) | |
| { | |
| $masterDayOrder = $this->realEscapeObject($masterDayOrder); | |
| $condition = ''; | |
| if ($masterDayOrder->courseTypeId && $masterDayOrder->batchStartYear) { | |
| $condition .= " AND courseTypeId = " . $masterDayOrder->courseTypeId; | |
| $condition .= " AND batchStartYear = " . $masterDayOrder->batchStartYear; | |
| } else { | |
| $condition .= " AND batchCourseTypeId = " . $masterDayOrder->batchCoursetypeId; | |
| } | |
| $sql = "UPDATE masterDayOrder SET dayOrder = " . $masterDayOrder->dayOrder . ", updatedBy = " . $masterDayOrder->updatedBy . ", updatedDate = utc_timestamp() WHERE dayNameId = " . $masterDayOrder->dayNameId . " " . $condition . ""; | |
| try { | |
| $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException ($e->getCode(), $e->getMessage()); | |
| } | |
| return true; | |
| } | |
| /** | |
| * Get dates between dates | |
| * @param string $fromDate | |
| * @param string $toDate | |
| * @return array | |
| */ | |
| public function getDaysBetweenDates($fromDate, $toDate) | |
| { | |
| $fromDate = $this->realEscapeString($fromDate); | |
| $toDate = $this->realEscapeString($toDate); | |
| $dateArray = []; | |
| $iDateFrom = mktime(1, 0, 0, substr($fromDate, 5, 2), substr($fromDate, 8, 2), substr($fromDate, 0, 4)); | |
| $iDateTo = mktime(1, 0, 0, substr($toDate, 5, 2), substr($toDate, 8, 2), substr($toDate, 0, 4)); | |
| if ($iDateTo >= $iDateFrom) { | |
| array_push($dateArray, date('Y-m-d', $iDateFrom)); // first entry | |
| while ($iDateFrom < $iDateTo) { | |
| $iDateFrom += 86400; // add 24 hours | |
| array_push($dateArray, date('Y-m-d', $iDateFrom)); | |
| } | |
| } | |
| return $dateArray; | |
| } | |
| /** | |
| * Copy master timetable for given department and batch | |
| * @param int $timetableDate | |
| * @param int $dayOrderId | |
| * @param int $deptId | |
| * @param int $batchId | |
| * @param $semId | |
| * @return boolean | |
| * @throws ProfessionalException | |
| */ | |
| public function copyMasterTimetable($timetableDate, $dayOrderId, $deptId, $batchId, $semId) | |
| { | |
| $timetableDate = $this->realEscapeString($timetableDate); | |
| $dayOrderId = $this->realEscapeString($dayOrderId); | |
| $deptId = $this->realEscapeString($deptId); | |
| $batchId = $this->realEscapeString($batchId); | |
| $semId = $this->realEscapeString($semId); | |
| $isLocked = 0; | |
| //Deleting existing timetable entry if any. | |
| $this->deleteTimetable($timetableDate, $deptId, $batchId); | |
| $isLocked = $this->isNewTimetableTobeLocked(); | |
| $sql = "INSERT INTO batch_timetable (batchID, subjectID, dayID, hourID, semID, sbsID, subbatchID, stratTime, | |
| endTime, timetableDate, dayOrderId,is_allowed) | |
| SELECT bmt.batchId, bmth.subjectId, WEEKDAY('" . $timetableDate . "')+1, bmth.hourId, | |
| bmt.semId, bmth.sbsId,bmth.subbatchID, bmth.startTime, bmth.endTime, '" . $timetableDate . "', | |
| '" . $dayOrderId . "'," . $isLocked . " | |
| FROM batchMasterTimetable bmt | |
| INNER JOIN batchMasterTimetableHour bmth ON bmt.id = bmth.batchMasterTimetableId | |
| WHERE bmt.dayOrder=$dayOrderId AND bmt.batchId=$batchId AND bmt.semId = $semId"; | |
| try { | |
| return $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * Get day Order Name of a date by batchId | |
| * @param string $timetableDate | |
| * @param int $batchId | |
| * @return int dayOrder | |
| * @throws ProfessionalException | |
| */ | |
| public function getdayOrderNameByTimetableDate($timetableDate, $batchId = null, $semId = null) | |
| { | |
| $timetableDate = $this->realEscapeString($timetableDate); | |
| $timetableDate = date('Y-m-d', strtotime($timetableDate)); | |
| $batchId = $this->realEscapeString($batchId); | |
| $semId = $this->realEscapeString($semId); | |
| $dayOrder = NULL; | |
| //Check whether this day is common holiday | |
| if (!CalendarService::getInstance()->isCollegeHoliday($timetableDate)) { | |
| $sql = "SELECT distinct dayOrder.name FROM batch_timetable bt INNER JOIN timetable_dayOrder dayOrder ON bt.dayOrderId = dayOrder.order WHERE bt.timetableDate='$timetableDate'"; | |
| if ($batchId) { | |
| $sql .= " AND bt.batchID=$batchId"; | |
| } | |
| if ($semId) { | |
| $sql .= " AND bt.semID=$semId"; | |
| } | |
| try { | |
| $dayOrder = $this->executeQueryForObject($sql)->name; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException ($e->getCode(), $e->getMessage()); | |
| } | |
| } else { | |
| return null; | |
| } | |
| return $dayOrder; | |
| } | |
| /** | |
| * Get day order by timetable date | |
| * @param string $date | |
| * @return int $dayOrder | |
| * @throws ProfessionalException | |
| */ | |
| public function getDayOrderByDate($date) | |
| { | |
| $date = $this->realEscapeString($date); | |
| $sql = "SELECT DISTINCT(dayOrderId) as dayOrder FROM batch_timetable WHERE timetableDate='$date' AND dayOrderId>0"; | |
| try { | |
| $dayOrder = $this->executeQueryForObject($sql)->dayOrder; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException ($e->getCode(), $e->getMessage()); | |
| } | |
| return $dayOrder; | |
| } | |
| /** | |
| * get largest date in batch timetable | |
| * @return string date | |
| * @throws ProfessionalException | |
| */ | |
| public function getTimetableEndDate() | |
| { | |
| $endDate = ""; | |
| $sql = "SELECT MAX(timetableDate) as endDate from batch_timetable "; | |
| try { | |
| $endDate = $this->executeQueryForObject($sql)->endDate; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException ($e->getCode(), $e->getMessage()); | |
| } | |
| return $endDate; | |
| } | |
| /** | |
| * add course file batch timetable day order | |
| * @param BatchTimetableCoursefileDayOrder $batchTimetableCoursefileDayOrder | |
| * @return object|NULL|$objectList[] | |
| * @throws ProfessionalException | |
| */ | |
| public function addCourseFileTimetableDayOrder($batchTimetableCoursefileDayOrder) | |
| { | |
| $batchTimetableCoursefileDayOrder = $this->realEscapeObject($batchTimetableCoursefileDayOrder); | |
| $psId = $batchTimetableCoursefileDayOrder->psId; | |
| $psId = $psId ? $psId : 0; | |
| $sql = "INSERT INTO batch_timetable_coursefile_dayOrder (batchId, sbsId, dayId, semId, dayOrderId, psId, createdBy, createdDate, updatedBy, updatedDate) VALUES ($batchTimetableCoursefileDayOrder->batchId, $batchTimetableCoursefileDayOrder->sbsId, $batchTimetableCoursefileDayOrder->dayId, $batchTimetableCoursefileDayOrder->semId, $batchTimetableCoursefileDayOrder->dayOrderId, $psId, $batchTimetableCoursefileDayOrder->createdBy, utc_timestamp(), $batchTimetableCoursefileDayOrder->updatedBy, utc_timestamp()); | |
| "; | |
| try { | |
| return $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException ($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * check whether course file batch timetable day order exists or not | |
| * @param BatchTimetableCoursefileDayOrder $batchTimetableCoursefileDayOrder | |
| * @return boolean | |
| * @throws ProfessionalException | |
| */ | |
| public function checkDayOrderCourseFileTimetable($batchTimetableCoursefileDayOrder) | |
| { | |
| $isDayOrder = false; | |
| $id = 0; | |
| if (!$batchTimetableCoursefileDayOrder->psId) { | |
| $sql = "SELECT id FROM batch_timetable_coursefile_dayOrder WHERE batchId=$batchTimetableCoursefileDayOrder->batchId AND sbsId=$batchTimetableCoursefileDayOrder->sbsId AND dayId=$batchTimetableCoursefileDayOrder->dayId AND semId=$batchTimetableCoursefileDayOrder->semId AND dayOrderId=$batchTimetableCoursefileDayOrder->dayOrderId"; | |
| } else { | |
| $sql = "SELECT id FROM batch_timetable_coursefile_dayOrder WHERE psId=$batchTimetableCoursefileDayOrder->psId AND dayId=$batchTimetableCoursefileDayOrder->dayId AND dayOrderId=$batchTimetableCoursefileDayOrder->dayOrderId"; | |
| } | |
| try { | |
| $id = $this->executeQueryForObject($sql)->id; | |
| if ($id) { | |
| $isDayOrder = true; | |
| } | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException ($e->getCode(), $e->getMessage()); | |
| } | |
| return $isDayOrder; | |
| } | |
| /** | |
| * update course file batch timetable day order | |
| * @param BatchTimetableCoursefileDayOrder $batchTimetableCoursefileDayOrder | |
| * @return object|NULL|$objectList[] | |
| * @throws ProfessionalException | |
| */ | |
| public function updateCourseFileTimetableDayOrder($batchTimetableCoursefileDayOrder) | |
| { | |
| $sql = "UPDATE batch_timetable_coursefile_dayOrder SET dayOrderId=$batchTimetableCoursefileDayOrder->dayOrderId, updatedBy=$batchTimetableCoursefileDayOrder->updatedBy, updatedDate=utc_timestamp() WHERE id=$batchTimetableCoursefileDayOrder->id"; | |
| try { | |
| return $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * get coursefile day order by batchId, semId, sbsId | |
| * @param int $batchId | |
| * @param int $semId | |
| * @param int $sbsId | |
| * @return object|array|$objectList[] | |
| * @throws ProfessionalException | |
| */ | |
| public function getCourseFileTimetableDayOrder($batchId, $semId, $sbsId) | |
| { | |
| $sql = "SELECT * FROM batch_timetable_coursefile_dayOrder WHERE batchId=$batchId AND sbsId=$sbsId AND semId=$semId"; | |
| try { | |
| return $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * get coursefile day order by psId | |
| * @param int $psId | |
| * @return object|array|$objectList[] | |
| * @throws ProfessionalException | |
| */ | |
| public function getCourseFileTimetableDayOrderByPsId($psId) | |
| { | |
| $sql = "SELECT * FROM batch_timetable_coursefile_dayOrder WHERE psId=$psId"; | |
| try { | |
| return $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * get coursefile day order by batchId, semId, sbsId | |
| * @param int $batchId | |
| * @param int $semId | |
| * @param int $sbsId | |
| * @return object|array|$objectList[] | |
| * @throws ProfessionalException | |
| */ | |
| public function getCourseFileTimetableDayOrderByDayId($batchId, $semId, $sbsId, $dayId) | |
| { | |
| $sql = "SELECT btdo.*, tdo.name as dayOrderName FROM batch_timetable_coursefile_dayOrder btdo INNER JOIN timetable_dayOrder tdo ON btdo.dayOrderId=tdo.id WHERE batchId=$batchId AND sbsId=$sbsId AND semId=$semId AND dayId=$dayId"; | |
| try { | |
| return $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * get coursefile day order by psId | |
| * @param int $psId | |
| * @param int $dayId | |
| * @return object|array|$objectList[] | |
| * @throws ProfessionalException | |
| */ | |
| public function getCourseFileTimetableDayOrderByDayIdpsId($psId, $dayId) | |
| { | |
| $sql = "SELECT btdo.*, tdo.name as dayOrderName FROM batch_timetable_coursefile_dayOrder btdo INNER JOIN timetable_dayOrder tdo ON btdo.dayOrderId=tdo.id WHERE psId=$psId AND dayId=$dayId"; | |
| try { | |
| return $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * delete coursefile timetable day order | |
| * @param int $id | |
| * @return object|NULL|$objectList[] | |
| * @throws ProfessionalException | |
| */ | |
| public function deleteCourseFileTimetableDayOrder($id) | |
| { | |
| $sql = "DELETE FROM batch_timetable_coursefile_dayOrder WHERE id=$id"; | |
| try { | |
| return $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * check hour is allowed to staff or not | |
| * @param Timetable $timetable | |
| * @return boolean | |
| * @throws ProfessionalException | |
| */ | |
| public function checkHourAllowed($timetable) | |
| { | |
| $flag = FALSE; | |
| $sql = "SELECT t2.staffID FROM batch_timetable t1, sbs_relation t2 WHERE t1.timetableDate = '" . $timetable->timetableDate . "' AND t1.dayID='" . $timetable->dayID . "' AND t1.hourID='" . $timetable->hourID . "' AND t1.batchID='" . $timetable->batchID . "' AND t1.semID='" . $timetable->semID . "' AND t1.sbsID=t2.sbsID AND (t1.sbsID='" . $timetable->sbsID . "' OR t1.is_allowed=1) AND (t2.staffID='" . $timetable->staffID . "' OR t1.is_allowed=1)"; | |
| try { | |
| $staffId = $this->executeQueryForObject($sql)->staffID; | |
| if ($staffId) { | |
| $flag = TRUE; | |
| } | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $flag; | |
| } | |
| /** | |
| * check hour is suspended | |
| * @param Timetable $timetable | |
| * @return boolean | |
| * @throws ProfessionalException | |
| */ | |
| public function checkHourIsSuspended($timetable) | |
| { | |
| $isSuspended = FALSE; | |
| $sql = "SELECT hour FROM suspended_hours WHERE suspendedDate='" . $timetable->timetableDate . "' AND hour='" . $timetable->hourID . "' AND batchID='" . $timetable->batchID . "' AND semID='" . $timetable->semID . "'"; | |
| try { | |
| $hour = $this->executeQueryForObject($sql)->hour; | |
| if ($hour) { | |
| $isSuspended = TRUE; | |
| } | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $isSuspended; | |
| } | |
| /** | |
| * update hour time | |
| * @param BatchTimetable $batchTimetable | |
| * @return object|NULL|$objectList[] | |
| * @throws ProfessionalException | |
| */ | |
| public function updateHourTime($batchTimetable) | |
| { | |
| $this->realEscapeObject($batchTimetable); | |
| $sql = "UPDATE batch_timetable SET stratTime = '" . $batchTimetable->stratTime . "', endTime = '" . $batchTimetable->endTime . "' WHERE batchID= $batchTimetable->batchId AND subbatchID=$batchTimetable->subbatchId AND semID=$batchTimetable->semId AND hourID=$batchTimetable->hourId AND timetableDate = '" . date('Y-m-d', strtotime($batchTimetable->timetableDate)) . "'"; | |
| try { | |
| return $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * get hour suspended staff details | |
| * @param Timetable $timetable | |
| * @return object|NULL|$objectList[] | |
| * @throws ProfessionalException | |
| */ | |
| public function getHourSuspendedStaffDetails($timetable) | |
| { | |
| $staffDetails = NULL; | |
| $sql = "SELECT sa.staffName, sa.isHOD, sa.isPrincipal FROM suspended_hours sh | |
| INNER JOIN staffaccounts sa ON sa.staffID = sh.whom_suspended WHERE sh.suspendedDate='" . $timetable->timetableDate . "' AND sh.hour='" . $timetable->hourID . "' AND sh.batchID='" . $timetable->batchID . "' AND sh.semID='" . $timetable->semID . "'"; | |
| try { | |
| $staffDetails = $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $staffDetails; | |
| } | |
| /** | |
| * get assigned hour details | |
| * @param Timetable $timetable | |
| * @param bool $subjectFlag | |
| * @return object|array|$objectList[] | |
| * @throws ProfessionalException | |
| */ | |
| public function getAssignedHourDetails($timetable, $subjectFlag = TRUE) | |
| { | |
| $subjectDetails = null; | |
| $sql = "SELECT t1.sbsID, t2.subjectName, t3.staffCode, t2.subjectID, t2.subjectDesc, t3.staffName, t5.batchName, t1.subbatchID, t1.is_allowed, 0, 0, t1.stratTime, t1.endTime, t3.staffID, t1.hourID from batch_timetable t1, subjects t2, staffaccounts t3, sbs_relation t4, batches t5 where t1.sbsID=t4.sbsID and t4.subjectID=t2.subjectID and t4.staffID=t3.staffID and t1.batchID=t5.batchID and (t1.batchID='$timetable->batchID' and t1.semID='$timetable->semID' and t1.timetableDate='" . date('Y-m-d', strtotime($timetable->timetableDate)) . "'"; | |
| if ($timetable->hourID) { | |
| $sql .= " AND t1.hourID='$timetable->hourID'"; | |
| } | |
| if ($subjectFlag) { | |
| $sql .= " AND t1.sbsID='$timetable->sbsID' )"; | |
| } else { | |
| $sql .= " AND t1.is_allowed=1 )"; | |
| } | |
| try { | |
| $subjectDetails = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $subjectDetails; | |
| } | |
| /** | |
| * Calculating total hours assigned to this particular faculty | |
| * @param Timetable $timetable | |
| * @return int | |
| * @throws ProfessionalException | |
| */ | |
| public function getTotalHour($timetable) | |
| { | |
| $timetable = $this->realEscapeObject($timetable); | |
| $totalHour = 0; | |
| $deptID = BatchService::getInstance()->getBatchDetailsById($timetable->batchID)->deptId; | |
| $holidays = CalendarService::getInstance()->getHolidays($deptID, $timetable->batchID, $timetable->timetableDate); | |
| $sql = "select count(hour) as totalHour FROM (SELECT count(hourID) as hour FROM batch_timetable WHERE batchID=\"" . $timetable->batchID . "\" AND semID=\"" . $timetable->semID . "\" AND sbsID=\"" . $timetable->sbsID . "\" AND timetableDate <= \"" . $timetable->timetableDate . "\" AND timetableDate NOT IN ('" . implode("','", $holidays) . "') group by hourID, timetableDate) as hourDetails"; | |
| try { | |
| $totalHour = $this->executeQueryForObject($sql)->totalHour; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $totalHour; | |
| } | |
| /** | |
| * get hour assigned staff details | |
| * @param Timetable $timetable | |
| * @return object|array|$objectList[] | |
| * @throws ProfessionalException | |
| */ | |
| public function getHourAssignedStaffs($timetable) | |
| { | |
| $timetable = $this->realEscapeObject($timetable); | |
| $staffDetails = []; | |
| $sql = "select distinct sa.staffID, sa.staffName, sub.subjectName, bt.is_allowed, sa.staffCode, sub.subjectID, sr.sbsID from batch_timetable bt INNER JOIN sbs_relation sr ON sr.sbsID=bt.sbsID AND sr.batchID=bt.batchID AND sr.semID=bt.semID INNER JOIN staffaccounts sa ON sa.staffID=sr.staffID INNER JOIN subjects sub ON sub.subjectID=sr.subjectID WHERE bt.timetableDate='" . date('Y-m-d', strtotime($timetable->timetableDate)) . "' AND bt.hourID=$timetable->hourID AND bt.batchID=$timetable->batchID AND bt.semID=$timetable->semID"; | |
| try { | |
| $staffDetails = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $staffDetails; | |
| } | |
| /** | |
| * update hour time | |
| * @param BatchTimetable $batchTimetable | |
| * @return object|NULL|$objectList[] | |
| * @throws ProfessionalException | |
| */ | |
| public function getHourDetails($batchTimetable) | |
| { | |
| $batchTimetable = $this->realEscapeObject($batchTimetable); | |
| $sql = "SELECT * FROM batch_timetable WHERE batchID= $batchTimetable->batchId AND semID=$batchTimetable->semId AND hourID=$batchTimetable->hourId AND timetableDate = '" . date('Y-m-d', strtotime($batchTimetable->timetableDate)) . "'"; | |
| if($batchTimetable->sbsId) | |
| { | |
| $sql .=" AND sbsID='$batchTimetable->sbsId'"; | |
| } | |
| try { | |
| return $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * delete batch timetable entry by sbsID | |
| * @param int $sbsId | |
| * @return Object | |
| * @throws ProfessionalException | |
| */ | |
| public function deleteBatchTimetableBySbsId($sbsId) | |
| { | |
| $sbsId = $this->realEscapeString($sbsId); | |
| $sql = "DELETE FROM batch_timetable WHERE sbsID = $sbsId"; | |
| try { | |
| return $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * @param $staffId | |
| * @param $fromDate | |
| * @param $toDate | |
| * @return Object | |
| * @throws ProfessionalException | |
| */ | |
| public function getMyTimeTableByStaffId($staffId, $fromDate, $toDate) | |
| { | |
| $staffId = $this->realEscapeString($staffId); | |
| $fromDate = $this->realEscapeString($fromDate); | |
| $toDate = $this->realEscapeString($toDate); | |
| $sql = "SELECT distinct sa.staffID, bt.sbsID, sub.subjectName, sa.staffCode, sr.subjectID, sub.subjectDesc, sa.staffName, bth.batchName, bt.subbatchID, bt.is_allowed, bt.batchID, bt.semID, bt.stratTime, bt.endTime,bt.timetableDate,bt.hourID , if(ps.sbsID,'1','0') as isPSubject FROM batch_timetable bt INNER JOIN sbs_relation sr ON bt.sbsID = sr.sbsID INNER JOIN batches bth ON sr.batchID=bth.batchID AND sr.semID=bth.semID INNER JOIN subjects sub ON sub.subjectID = sr.subjectID INNER JOIN staffaccounts sa ON sa.staffID = sr.staffID LEFT JOIN pseudosubjects_sbs ps ON ps.sbsID = sr.sbsID WHERE bt.timetableDate BETWEEN '" . date('Y-m-d', strtotime($fromDate)) . "' AND '" . date('Y-m-d', strtotime($toDate)) . "' AND sr.staffID=$staffId ORDER BY bt.timetableDate, bt.hourID, bt.stratTime ASC"; | |
| try { | |
| return $this->executeQueryForObject($sql, FALSE, $this->mapper[TimetableServiceMapper::GET_MY_TIMETABLE_BY_STAFF_ID]); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * @param unknown $batchID | |
| * @param unknown $semID | |
| * @return object|array|$objectList[] | |
| * @throws | |
| * @author sujesh | |
| */ | |
| function getBatchTimeTableByBatchAndSem($batchID, $semID) | |
| { | |
| $batchID = $this->realEscapeString($batchID); | |
| $semID = $this->realEscapeString($semID); | |
| $sql = "SELECT * from batch_timetable where batchID=$batchID and semID=$semID"; | |
| try { | |
| $batchTimeTableList = $this->executeQueryForList($sql); | |
| } catch (\Exception $ex) { | |
| throw new ProfessionalException($ex->getCode(), $ex->getMessage()); | |
| } | |
| return $batchTimeTableList; | |
| } | |
| /** | |
| * @param $dayOrder | |
| * @param $hourId | |
| * @param $sbsId | |
| * @return array|Object | |
| * @throws ProfessionalException | |
| */ | |
| public function checkStaffTimetableExists($dayOrder, $hourId, $sbsId) | |
| { | |
| $dayOrder = $this->realEscapeString($dayOrder); | |
| $hourId = $this->realEscapeString($hourId); | |
| $sbsId = $this->realEscapeString($sbsId); | |
| $batchTimeTableList = []; | |
| $sql = "SELECT bmth.id FROM batchMasterTimetableHour bmth INNER JOIN batchMasterTimetable bmt ON (bmt.id = bmth.batchMasterTimetableId) WHERE bmth.sbsId IN (SELECT sbsID from sbs_relation WHERE staffID = (select staffID from sbs_relation where sbsID = " . $sbsId . ")) AND bmth.hourId = " . $hourId . " AND bmt.dayOrder = " . $dayOrder; | |
| try { | |
| $batchTimeTableList = $this->executeQueryForList($sql); | |
| } catch (\Exception $ex) { | |
| throw new ProfessionalException($ex->getCode(), $ex->getMessage()); | |
| } | |
| return $batchTimeTableList; | |
| } | |
| /** | |
| * Get total working days between a date range | |
| * @param String $fromDate | |
| * @param String $toDate | |
| * @return int | |
| * @throws ProfessionalException | |
| */ | |
| public function getTotalWorkingDays($fromDate, $toDate) | |
| { | |
| $fromDate = $this->realEscapeString($fromDate); | |
| $toDate = $this->realEscapeString($toDate); | |
| $sql = "SELECT | |
| COUNT(DISTINCT bt.timetableDate) as totalWorkingDay | |
| FROM | |
| batch_timetable bt | |
| LEFT JOIN lms_calender lc ON bt.timetableDate BETWEEN FROM_UNIXTIME(lc.timeStart) AND FROM_UNIXTIME(lc.timeEnd) | |
| AND lc.deptID = 0 | |
| AND lc.batchID = 0 | |
| AND lc.flag IN (1 , 3) | |
| WHERE | |
| bt.timetableDate BETWEEN '" . $fromDate . "' AND '" . $toDate . "' | |
| AND eventID IS NULL"; | |
| try { | |
| return $this->executeQueryForObject($sql)->totalWorkingDay; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * Undocumented function | |
| * | |
| * @param [type] $timetableDetails | |
| * @return \StdClass | |
| * @throws ProfessionalException | |
| */ | |
| public function checkIfTimetableCanBeCopiedAndReturnErrors($timetableDetails) | |
| { | |
| $timetableDetails = $this->realEscapeObject($timetableDetails); | |
| $response = new \StdClass(); | |
| $response->holidayList = CalendarService::getInstance()->getHolidaysBetweenADateRange($timetableDetails); | |
| $response->attendanceList = AttendanceService::getInstance()->getAttendanceConfirmedDetailsForAGivenDateRange($timetableDetails->batchId, $timetableDetails->semId, $timetableDetails->copyToStartDate, $timetableDetails->copyToEndDate); | |
| return $response; | |
| } | |
| /** | |
| * @param GetDepartmentWiseTimeTableRequest $request | |
| * @return array|Object | |
| * @throws ProfessionalException | |
| */ | |
| public function getDepartmentWiseTimeTable(GetDepartmentWiseTimeTableRequest $request) | |
| { | |
| $response = []; | |
| $request = $this->realEscapeObject($request); | |
| $timeTableCondition = ""; | |
| if (!is_array($request->batchIds)) { | |
| throw new ProfessionalException("INVALID_BATCH_DETAILS_GIVEN", "Invalid batch details given"); | |
| } | |
| if (!is_array($request->departmentIds)) { | |
| throw new ProfessionalException("INVALID_DEPARTMENT_DETAILS_GIVEN", "Invalid department details given"); | |
| } | |
| if (!is_array($request->semesterIds)) { | |
| throw new ProfessionalException("INVALID_SEMESTER_DETAILS_GIVEN", "Invalid semester details given"); | |
| } | |
| if (!is_array($request->dayIds)) { | |
| throw new ProfessionalException("INVALID_DAY_DETAILS_GIVEN", "Invalid day details given"); | |
| } | |
| if (!empty($request->fromDate) && !empty($request->toDate)) { | |
| $timeTableCondition .= " AND bt.timetableDate | |
| BETWEEN '" . date('Y-m-d', strtotime($request->fromDate)) . "' | |
| AND '" . date('Y-m-d', strtotime($request->toDate)) . "'"; | |
| } else { | |
| throw new ProfessionalException(ProfessionalException::INVALID_DATE_RANGE, "Invalid from and to date given"); | |
| } | |
| if (!empty($request->semesterIds) && count($request->semesterIds) > 0) { | |
| $timeTableCondition .= " AND bt.semID IN (" . implode(",", $request->semesterIds) . ")"; | |
| } | |
| if (!empty($request->dayIds) && count($request->dayIds) > 0) { | |
| $timeTableCondition .= " AND bt.dayID IN (" . implode(",", $request->dayIds) . ")"; | |
| } | |
| $sql = "SELECT sa.staffID,sa.staffName,b.batchName,b.batchID,bt.hourID,bt.dayID,bt.timetableDate,bt.timetableID, | |
| unix_timestamp(timetableDate) as dateId,s.subjectID,s.subjectName,s.subjectDesc,th.id as hourID, | |
| th.name as hourName,d.deptID,d.deptName,d.departmentDesc | |
| FROM batches b | |
| INNER JOIN department d ON b.deptID = d.deptID | |
| LEFT JOIN batch_timetable bt ON b.batchID=bt.batchID " . $timeTableCondition . " | |
| LEFT JOIN sbs_relation sr ON sr.sbsID = bt.sbsID | |
| LEFT JOIN staffaccounts sa ON sa.staffID = sr.staffID | |
| LEFT JOIN subjects s ON s.subjectID = bt.subjectID OR s.subjectID =sr.subjectID | |
| LEFT JOIN timetable_hour th ON th.id = bt.hourID | |
| WHERE 1=1 "; | |
| if (!empty($request->batchIds) && count($request->batchIds) > 0) { | |
| $sql .= " AND b.batchID IN (" . implode(",", $request->batchIds) . ")"; | |
| } | |
| if (!empty($request->departmentIds) && count($request->departmentIds) > 0) { | |
| $sql .= " AND b.deptID IN (" . implode(",", $request->departmentIds) . ")"; | |
| } | |
| $sql .= " ORDER BY bt.timetableDate,th.id "; | |
| try { | |
| $response = $this->executeQueryForList($sql, $this->mapper[TimetableServiceMapper::GET_DEPARTMENT_WISE_TIMETABLE]); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $response; | |
| } | |
| /** | |
| * @param GetTimeTableBetweenTwoDatesRequest $request | |
| * @return Object|Batch[] | |
| * @throws ProfessionalException | |
| */ | |
| public function getBatchWiseTimetableBetweenToDates(GetTimeTableBetweenTwoDatesRequest $request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $timeTableCondition = ""; | |
| if (!empty($request->fromDate) && !empty($request->toDate)) { | |
| $timeTableCondition .= " AND bt.timetableDate | |
| BETWEEN '" . date('Y-m-d', strtotime($request->fromDate)) . "' | |
| AND '" . date('Y-m-d', strtotime($request->toDate)) . "'"; | |
| } else { | |
| throw new ProfessionalException(ProfessionalException::INVALID_DATE_RANGE, "Invalid from and to date given"); | |
| } | |
| $sql = "SELECT sa.staffID,sa.staffName,bt.hourID,bt.dayID,bt.timetableDate,bt.timetableID,bt.sbsID, | |
| bt.batchID as batchId,bt.semID as semesterId, | |
| unix_timestamp(timetableDate) as dateId,s.subjectID,th.id as hourID,th.name as hourName, sb.subbatchID, | |
| sb.batchID as subBatchId,sb.semID as subBatchSemester | |
| FROM batches b | |
| LEFT JOIN batch_timetable bt ON b.batchID=bt.batchID $timeTableCondition | |
| LEFT JOIN sbs_relation sr ON sr.sbsID = bt.sbsID | |
| LEFT JOIN staffaccounts sa ON sa.staffID = sr.staffID | |
| LEFT JOIN subjects s ON s.subjectID = bt.subjectID OR s.subjectID =sr.subjectID | |
| LEFT JOIN timetable_hour th ON th.id = bt.hourID | |
| LEFT JOIN subbatch_sbs ss ON ss.sbsID= sr.sbsID | |
| LEFT JOIN subbatches sb ON sb.subbatchID =ss.subbatchID | |
| WHERE 1=1 "; | |
| if (!empty($request->batchId)) { | |
| $sql .= " AND b.batchID =$request->batchId"; | |
| } | |
| $sql .= " ORDER BY bt.timetableDate,th.id "; | |
| try { | |
| $response = $this->executeQueryForList($sql, $this->mapper[TimetableServiceMapper::GET_BATCH_WISE_TIMETABLE_BETWEEN_DATES]); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $response; | |
| } | |
| /** | |
| * Returns holidays between given date for departments and batches | |
| * @param GetHolidaysRequest $request | |
| * @return Object | |
| * @throws ProfessionalException | |
| */ | |
| public function getHolidays(GetHolidaysRequest $request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $request->fromDate = date('Y-m-d 00:00:00', strtotime($request->fromDate)); | |
| $request->toDate = date('Y-m-d 23:59:59', strtotime($request->toDate)); | |
| try { | |
| if (empty($request->fromDate) || empty($request->toDate)) { | |
| throw new ProfessionalException(ProfessionalException::INVALID_DATE_RANGE, "Invalid date range given"); | |
| } | |
| if (!is_array($request->batchIds)) { | |
| throw new ProfessionalException("INVALID_BATCH_DETAILS_GIVEN", "Invalid batch details given"); | |
| } | |
| if (!is_array($request->departmentIds)) { | |
| throw new ProfessionalException("INVALID_DEPARTMENT_DETAILS_GIVEN", "Invalid department details given"); | |
| } | |
| $sql = "SELECT eventID as event_id,eventTitle as event_title, | |
| timeStart as event_time_start,timeEnd as event_time_end,deptID as event_department_id, | |
| batchID event_batch_id | |
| FROM lms_calender | |
| WHERE ((deptID=0 AND batchID=0) "; | |
| if (count($request->departmentIds) != 0) { | |
| $sql .= " OR (deptID IN (" . implode(",", $request->departmentIds) . ") AND batchID=0) "; | |
| } | |
| if (count($request->departmentIds) != 0 && count($request->batchIds) > 0) { | |
| $sql .= " OR (deptID IN (" . implode(",", $request->departmentIds) . ") AND | |
| batchID IN (" . implode(",", $request->batchIds) . ") ) "; | |
| } | |
| $sql .= " ) "; | |
| if (!empty($request->fromDate) && !empty($request->toDate)) { | |
| $sql .= " AND timeStart BETWEEN UNIX_TIMESTAMP('" . $request->fromDate . "') AND UNIX_TIMESTAMP('" . $request->toDate . "') | |
| AND timeEnd BETWEEN UNIX_TIMESTAMP('" . $request->fromDate . "') AND UNIX_TIMESTAMP('" . $request->toDate . "')"; | |
| } else if ((empty($request->fromDate) && !empty($request->toDate)) || (!empty($request->fromDate) || empty($request->toDate))) { | |
| throw new ProfessionalException(ProfessionalException::INVALID_DATE_RANGE, "Select a valid date range"); | |
| } | |
| $sql .= " AND flag IN (" . Calendar::SPECIAL_HOLIDAY . "," . Calendar::HOLIDAY . ")"; | |
| return $this->executeQueryForList($sql, $this->mapper[TimetableServiceMapper::GET_HOLIDAYS]); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * @param GetSuspendedHoursRequest $request | |
| * @return Object | |
| * @throws ProfessionalException | |
| */ | |
| public function getSuspendedHoursBetweenDates(GetSuspendedHoursRequest $request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $request->fromDate = date('Y-m-d', strtotime($request->fromDate)); | |
| $request->toDate = date('Y-m-d', strtotime($request->toDate)); | |
| $sql = "SELECT sh.batchID as batchId,sh.semID as semesterId,sh.suspendedDate,sh.whom_suspended as whomSuspended, | |
| sh.hour as suspendedHour | |
| FROM suspended_hours sh | |
| INNER JOIN batches b On b.batchID=sh.batchID AND sh.semID=b.semID | |
| WHERE 1=1 "; | |
| if (count($request->batchIds) > 0) { | |
| $sql .= " AND sh.batchID IN (" . implode(",", $request->batchIds) . ") "; | |
| } | |
| if (count($request->semesterIds) > 0) { | |
| $sql .= " AND sh.semID IN (" . implode(",", $request->semesterIds) . ") "; | |
| } | |
| if (!empty($request->fromDate) && !empty($request->toDate)) { | |
| $sql .= " AND sh.suspendedDate BETWEEN '" . $request->fromDate . "' AND '" . $request->toDate . "' "; | |
| } else if ((!empty($request->fromDate) && empty($request->toDate)) || (empty($request->fromDate) && !empty($request->toDate))) { | |
| throw new ProfessionalException(ProfessionalException::INVALID_DATE_RANGE, "Invalid date range"); | |
| } | |
| try { | |
| return $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * @param GetFacultyWiseTimeTableRequest $request | |
| * @return FacultyTimeTableResponse | |
| * @throws ProfessionalException | |
| */ | |
| public function getFacultyWiseTimeTable(GetFacultyWiseTimeTableRequest $request) | |
| { | |
| $dateCondition = ""; | |
| $limitCondition = ""; | |
| $staffCondition = ""; | |
| $limitQuery = ""; | |
| $departmentCondition = ""; | |
| $totDeptCondition = ""; | |
| $response = new FacultyTimeTableResponse(); | |
| $request = $this->realEscapeObject($request); | |
| if (!is_array($request->staffIds)) { | |
| throw new ProfessionalException("INVALID_FACULTY_DETAILS_GIVEN", "Invalid faculty details given"); | |
| } | |
| if (!is_array($request->departmentIds)) { | |
| throw new ProfessionalException("INVALID_DEPARTMENT_DETAILS_GIVEN", "Invalid department details given"); | |
| } | |
| if (!is_array($request->semesterIds)) { | |
| throw new ProfessionalException("INVALID_SEMESTER_DETAILS_GIVEN", "Invalid semester details given"); | |
| } | |
| if (!is_array($request->dayIds)) { | |
| throw new ProfessionalException("INVALID_DAY_DETAILS_GIVEN", "Invalid day details given"); | |
| } | |
| /* | |
| * Adding pagination | |
| */ | |
| if ($request->isPaginationRequired && $request->startIndex !== "" && $request->endIndex !== "") { | |
| $limitCondition = "LIMIT $request->startIndex, $request->endIndex"; | |
| } | |
| if (!empty($request->staffIds) && count($request->staffIds) > 0) { | |
| $staffCondition = " AND staffID IN (" . implode(",", $request->staffIds) . ")"; | |
| } | |
| /* | |
| * Filtering with department ids | |
| */ | |
| if (!empty($request->departmentIds) && count($request->departmentIds) > 0) { | |
| $departmentCondition = " AND deptID IN (" . implode(",", $request->departmentIds) . ")"; | |
| } | |
| if (count($request->departmentIds) > 0 || count($request->staffIds) > 0 || $request->isPaginationRequired) { | |
| $limitQuery = "INNER JOIN ( SELECT staffID FROM staffaccounts | |
| WHERE 1=1 " . $staffCondition . $departmentCondition . " ORDER BY staffName " . $limitCondition . " ) | |
| AS distinctStaff ON distinctStaff.staffID = sa.staffID"; | |
| } | |
| /* | |
| * Filtering with timetable dates | |
| */ | |
| if (!empty($request->fromDate) && !empty($request->toDate)) { | |
| $dateCondition = " AND bt.timetableDate | |
| BETWEEN '" . date('Y-m-d', strtotime($request->fromDate)) . "' | |
| AND '" . date('Y-m-d', strtotime($request->toDate)) . "' "; | |
| } else { | |
| throw new ProfessionalException(ProfessionalException::INVALID_DATE_RANGE, "Invalid from and to date given"); | |
| } | |
| $sql = "SELECT sa.staffID,sa.staffName,sa.staffCode,b.batchName,b.batchID,bt.hourID,bt.dayID,bt.timetableDate,bt.timetableID, | |
| unix_timestamp(timetableDate) as dateId,s.subjectID,s.subjectName,s.subjectDesc,th.id as hourID, | |
| th.name as hourName,d.deptID,d.deptName,d.departmentDesc | |
| FROM staffaccounts sa " . $limitQuery . " | |
| INNER JOIN department d ON sa.deptID = d.deptID | |
| LEFT JOIN sbs_relation sr ON sr.staffID = sa.staffID | |
| LEFT JOIN batches b ON b.batchID=sr.batchID | |
| LEFT JOIN subjects s ON s.subjectID =sr.subjectID | |
| LEFT JOIN batch_timetable bt ON bt.sbsID=sr.sbsID " . $dateCondition . " | |
| LEFT JOIN timetable_hour th ON th.id = bt.hourID WHERE 1=1 "; | |
| if (!empty($request->semesterIds) && count($request->semesterIds) > 0) { | |
| $sql .= " AND b.semID IN (" . implode(",", $request->semesterIds) . ")"; | |
| } | |
| if (!empty($request->dayIds) && count($request->dayIds) > 0) { | |
| $sql .= " AND bt.dayID IN (" . implode(",", $request->dayIds) . ")"; | |
| } | |
| $sql .= "ORDER BY sa.staffName "; | |
| /* | |
| * Calculating total records | |
| */ | |
| try { | |
| if ($request->isPaginationRequired) { | |
| $totalCountSql = "SELECT count(staffID) AS totalRedcords FROM staffaccounts WHERE 1=1 " . $totDeptCondition . " " . $staffCondition; | |
| $response->totalRecords = $this->executeQueryForObject($totalCountSql)->totalRedcords; | |
| } | |
| $response->facultyTimeTable = $this->executeQueryForList($sql, $this->mapper[TimetableServiceMapper::GET_FACULTY_WISE_TIMETABLE]); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $response; | |
| } | |
| /** | |
| * @param GetFacultyWiseTimeTableRequest $request | |
| * @return Object | |
| * @throws ProfessionalException | |
| */ | |
| public function getDistinctBatchIdsFromTimeTable(GetFacultyWiseTimeTableRequest $request) | |
| { | |
| $sql = "SELECT DISTINCT b.batchID | |
| FROM batch_timetable bt | |
| INNER JOIN sbs_relation sr ON sr.sbsID = bt.sbsID | |
| INNER JOIN staffaccounts sa ON sa.staffID = sr.staffID | |
| INNER JOIN batches b ON b.batchID=sr.batchID | |
| INNER JOIN department d ON b.deptID = d.deptID | |
| WHERE 1=1 "; | |
| if (!empty($request->batchIds) && count($request->batchIds) > 0) { | |
| $sql .= " AND bt.batchID IN (" . implode(",", $request->batchIds) . ")"; | |
| } | |
| if (!empty($request->semesterIds) && count($request->semesterIds) > 0) { | |
| $sql .= " AND bt.semID IN (" . implode(",", $request->semesterIds) . ")"; | |
| } | |
| if (!empty($request->departmentIds) && count($request->departmentIds) > 0) { | |
| $sql .= " AND b.deptID IN (" . implode(",", $request->departmentIds) . ")"; | |
| } | |
| if (!empty($request->fromDate) && !empty($request->toDate)) { | |
| $sql .= " AND bt.timetableDate | |
| BETWEEN '" . date('Y-m-d', strtotime($request->fromDate)) . "' | |
| AND '" . date('Y-m-d', strtotime($request->toDate)) . "'"; | |
| } else { | |
| throw new ProfessionalException(ProfessionalException::INVALID_DATE_RANGE, "Invalid from and to date given"); | |
| } | |
| if (!empty($request->dayIds) && count($request->dayIds) > 0) { | |
| $sql .= " AND bt.dayID IN (" . implode(",", $request->dayIds) . ")"; | |
| } | |
| $sql .= "ORDER BY bt.timetableDate"; | |
| try { | |
| $response = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $response; | |
| } | |
| /** | |
| * Gat all the time ranges in all the batches where this staff is teaching | |
| * @param Integer $staffId | |
| * @param String $fromDate | |
| * @param String $toDate | |
| * @return ObjectList $timeRanges | |
| * @throws ProfessionalException | |
| * @author Vishnu M | |
| */ | |
| public function getStaffTimetableTimeBetweenDates($staffId, $fromDate, $toDate) | |
| { | |
| $staffId = $this->realEscapeString($staffId); | |
| $fromDate = $this->realEscapeString($fromDate); | |
| $toDate = $this->realEscapeString($toDate); | |
| $timeRanges = []; | |
| $sql = "SELECT DISTINCT bt.stratTime, bt.endTime, ct.typeName as courseType, ct.courseTypeID as courseTypeID FROM batch_timetable bt INNER JOIN sbs_relation sbs1 ON (bt.sbsID = sbs1.sbsID AND bt.batchID = sbs1.batchID AND bt.semID = sbs1.semID) INNER JOIN batches b ON (b.batchID = sbs1.batchID AND b.semID = sbs1.semID) INNER JOIN sbs_relation sbs2 ON (sbs2.batchID = b.batchID AND sbs2.semID = b.semID) left join course_type ct ON ct.courseTypeID = b.courseTypeID WHERE sbs2.staffID = '$staffId' AND timetableDate BETWEEN '$fromDate' AND '$toDate' UNION SELECT distinct bt.stratTime, bt.endTime, ct.typeName as courseType, ct.courseTypeID as courseTypeID FROM batch_timetable bt INNER JOIN attendance_swapping_details asd ON asd.date=bt.timetableDate AND asd.hour=bt.hourID AND asd.sbs_id=bt.sbsID INNER JOIN attendance_confirming_staff acs ON acs.attendance_swapping_details_id=asd.id INNER JOIN sbs_relation sr ON bt.sbsID = sr.sbsID INNER JOIN batches bth ON sr.batchID=bth.batchID AND sr.semID=bth.semID INNER JOIN subjects sub ON sub.subjectID = sr.subjectID INNER JOIN staffaccounts sa ON sa.staffID = sr.staffID left join course_type ct ON ct.courseTypeID = bth.courseTypeID LEFT JOIN pseudosubjects_sbs ps ON ps.sbsID = sr.sbsID left join pseudosubjects psub on psub.pseudosubjectID = ps.pseudosubjectID WHERE bt.timetableDate BETWEEN '$fromDate' AND '$toDate' AND acs.staff_id = '$staffId' ORDER BY stratTime ASC"; | |
| try { | |
| $timeRanges = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $timeRanges; | |
| } | |
| public function getStaffTimetableInDateRange($staffId, $fromDate, $toDate) | |
| { | |
| $staffId = $this->realEscapeString($staffId); | |
| $fromDate = $this->realEscapeString($fromDate); | |
| $toDate = $this->realEscapeString($toDate); | |
| try { | |
| $durations =json_decode(CommonService::getInstance()->getSettings(SettingsConstants::ONLINE_CLASS,SettingsConstants::ONLINE_CLASS_LIST_DURATIONS)); | |
| } catch (\Throwable $th) { | |
| $durations->upComingMeetingShowDurationInMinutes = 5; | |
| $durations->onGoingMeetingShowDurationInMinutes = 40; | |
| } | |
| $staffTimetable = []; | |
| $sql = "SELECT distinct sr.staffID, bt.sbsID, sub.subjectName, sa.staffCode, sr.subjectID, sub.subjectDesc, sa.staffName, bth.batchName, bt.subbatchID, bt.is_allowed, bt.batchID, bt.semID, bt.stratTime, bt.endTime, bt.timetableDate,bt.hourID , if(ps.sbsID,'1','0') as isPSubject, bt.dayID, ps.pseudosubjectID, psub.subjectName as psName, bt.course_timetable_id, asd.id as requestId, null as confirmingStaffId, asd.request_completed, asd.rule, bt.oicMeetingId,IF(now() BETWEEN om.meeting_date+INTERVAL -".$durations->upComingMeetingShowDurationInMinutes." MINUTE AND om.meeting_date +INTERVAL +".$durations->onGoingMeetingShowDurationInMinutes." MINUTE,1,0) AS liveNow, om.meetings_details, ou.user_details as userDetails,ou.oic_app_id as appId, IF(now() < om.meeting_date+INTERVAL -".$durations->upComingMeetingShowDurationInMinutes." MINUTE,1,0) AS upComing, ct.courseTypeID FROM batch_timetable bt INNER JOIN sbs_relation sr ON bt.sbsID = sr.sbsID AND bt.batchID=sr.batchID INNER JOIN batches bth ON sr.batchID=bth.batchID AND sr.semID=bth.semID INNER JOIN subjects sub ON sub.subjectID = sr.subjectID INNER JOIN course_type ct ON ct.courseTypeID = bth.courseTypeID INNER JOIN staffaccounts sa ON sa.staffID = sr.staffID LEFT JOIN pseudosubjects_sbs ps ON ps.sbsID = sr.sbsID left join pseudosubjects psub on psub.pseudosubjectID = ps.pseudosubjectID LEFT JOIN attendance_swapping_details asd ON asd.date=bt.timetableDate AND asd.hour=bt.hourID AND asd.sbs_id=bt.sbsID LEFT JOIN oic_meetings om ON om.id=bt.oicMeetingId LEFT JOIN oic_users ou ON om.oic_users_id = ou.id WHERE bt.timetableDate BETWEEN '$fromDate' AND '$toDate' AND sr.staffID = '$staffId' UNION SELECT distinct sr.staffID, bt.sbsID, sub.subjectName, sa.staffCode, sr.subjectID, sub.subjectDesc, sa.staffName, bth.batchName, bt.subbatchID, bt.is_allowed, bt.batchID, bt.semID, bt.stratTime, bt.endTime, bt.timetableDate,bt.hourID , if(ps.sbsID,'1','0') as isPSubject, bt.dayID, ps.pseudosubjectID, psub.subjectName as psName, bt.course_timetable_id, asd.id as requestId, acs.staff_id as confirmingStaffId, asd.request_completed, asd.rule, bt.oicMeetingId, IF(now() BETWEEN om.meeting_date+INTERVAL -".$durations->upComingMeetingShowDurationInMinutes." MINUTE AND om.meeting_date +INTERVAL +".$durations->onGoingMeetingShowDurationInMinutes." MINUTE,1,0) AS liveNow, om.meetings_details, ou.user_details as userDetails, ou.oic_app_id as appId, IF(now() < om.meeting_date+INTERVAL -".$durations->upComingMeetingShowDurationInMinutes." MINUTE,1,0) AS upComing, ct.courseTypeID FROM batch_timetable bt INNER JOIN attendance_swapping_details asd ON asd.date=bt.timetableDate AND asd.hour=bt.hourID AND asd.sbs_id=bt.sbsID INNER JOIN attendance_confirming_staff acs ON acs.attendance_swapping_details_id=asd.id INNER JOIN sbs_relation sr ON bt.sbsID = sr.sbsID AND bt.batchID=sr.batchID INNER JOIN batches bth ON sr.batchID=bth.batchID AND sr.semID=bth.semID INNER JOIN subjects sub ON sub.subjectID = sr.subjectID INNER JOIN course_type ct ON ct.courseTypeID = bth.courseTypeID INNER JOIN staffaccounts sa ON sa.staffID = sr.staffID LEFT JOIN pseudosubjects_sbs ps ON ps.sbsID = sr.sbsID left join pseudosubjects psub on psub.pseudosubjectID = ps.pseudosubjectID LEFT JOIN oic_meetings om ON om.id=bt.oicMeetingId LEFT JOIN oic_users ou ON om.oic_users_id = ou.id WHERE bt.timetableDate BETWEEN '$fromDate' AND '$toDate' AND acs.staff_id = '$staffId' ORDER BY timetableDate ASC, stratTime ASC, subjectID ASC, sbsID ASC"; | |
| try { | |
| $staffTimetable = $this->executeQueryForList($sql); | |
| foreach($staffTimetable as $timetable) | |
| { | |
| $timetable->liveNow=(int)$timetable->liveNow; | |
| if($timetable->meetings_details) | |
| { | |
| $timetable->meetings_details = json_decode($timetable->meetings_details); | |
| $timetable->userDetails = json_decode($timetable->userDetails); | |
| } | |
| if($timetable->meetingDate){ | |
| $meetingDate = strtotime($timetable->meetingDate); | |
| $timetable->upComing = strtotime(date("Y-m-d H:i:s")) < strtotime("-".$durations->upComingMeetingShowDurationInMinutes." minutes",$meetingDate)?"1":"0"; | |
| $timetable->liveNow = strtotime("-".$durations->upComingMeetingShowDurationInMinutes." minutes",$meetingDate) < strtotime(date("Y-m-d H:i:s")) && strtotime(date("Y-m-d H:i:s")) < strtotime("+".$durations->onGoingMeetingShowDurationInMinutes." minutes",$meetingDate)?"1":"0"; | |
| $timetable->expiredMeeting = strtotime("+".$durations->onGoingMeetingShowDurationInMinutes." minutes",$meetingDate) < strtotime(date("Y-m-d H:i:s"))?"1":"0"; | |
| } | |
| } | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $staffTimetable; | |
| } | |
| /** | |
| * Gat all the time ranges in batch | |
| * @param Integer $staffId | |
| * @param String $fromDate | |
| * @param String $toDate | |
| * @return ObjectList $timeRanges | |
| * @throws ProfessionalException | |
| * @author Sanoop | |
| */ | |
| public function getBatchTimetableTimeBetweenDates($batchId, $fromDate, $toDate) | |
| { | |
| $batchId = $this->realEscapeString($batchId); | |
| $fromDate = $this->realEscapeString($fromDate); | |
| $toDate = $this->realEscapeString($toDate); | |
| $timeRanges = []; | |
| $sql = "SELECT DISTINCT bt.stratTime, bt.endTime, ct.typeName as courseType FROM batch_timetable bt INNER JOIN sbs_relation sbs1 ON bt.sbsID = sbs1.sbsID AND bt.batchID = sbs1.batchID AND bt.semID = sbs1.semID INNER JOIN batches b ON b.batchID = sbs1.batchID AND b.semID = sbs1.semID LEFT JOIN course_type ct ON ct.courseTypeID = b.courseTypeID WHERE sbs1.batchID = '$batchId' AND bt.timetableDate BETWEEN '$fromDate' AND '$toDate' ORDER BY bt.stratTime ASC"; | |
| try { | |
| $timeRanges = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $timeRanges; | |
| } | |
| public function getBatchTimetableInDateRange($batchId, $fromDate, $toDate,$subjectId = null,$subbatchIds = null) | |
| { | |
| $batchId = $this->realEscapeString($batchId); | |
| $fromDate = $this->realEscapeString($fromDate); | |
| $toDate = $this->realEscapeString($toDate); | |
| $cond =""; | |
| if($subjectId){ | |
| $cond .=" AND bt.subjectID = '$subjectId' "; | |
| } | |
| if($subbatchIds){ | |
| $cond .=" AND bt.subbatchID IN($subbatchIds) "; | |
| } | |
| $staffTimetable = []; | |
| $sql = "SELECT distinct sr.staffID, bt.sbsID, sub.subjectName, sa.staffCode, sr.subjectID, sub.subjectDesc, sa.staffName, bth.batchName, bt.subbatchID, bt.is_allowed, bt.batchID, bt.semID, bt.stratTime, bt.endTime, bt.timetableDate,bt.hourID , if(ps.sbsID,'1','0') as isPSubject, bt.dayID, ps.pseudosubjectID, psub.subjectName as psName, bt.course_timetable_id, asd.id as requestId, null as confirmingStaffId, asd.request_completed, asd.rule FROM batch_timetable bt INNER JOIN sbs_relation sr ON bt.sbsID = sr.sbsID INNER JOIN batches bth ON sr.batchID=bth.batchID AND sr.semID=bth.semID INNER JOIN subjects sub ON sub.subjectID = sr.subjectID INNER JOIN staffaccounts sa ON sa.staffID = sr.staffID LEFT JOIN pseudosubjects_sbs ps ON ps.sbsID = sr.sbsID left join pseudosubjects psub on psub.pseudosubjectID = ps.pseudosubjectID LEFT JOIN attendance_swapping_details asd ON asd.date=bt.timetableDate AND asd.hour=bt.hourID AND (asd.sbs_id=bt.sbsID OR ps.pseudosubjectID=asd.ps_id) WHERE bt.timetableDate BETWEEN '$fromDate' AND '$toDate' AND sr.batchID = '$batchId' $cond ORDER BY bt.timetableDate ASC, bt.stratTime ASC, bt.subjectID ASC, bt.sbsID ASC"; | |
| try { | |
| $staffTimetable = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $staffTimetable; | |
| } | |
| public function getBatchTimetableInDateRangeWithoutTimeTableMethod($batchId, $fromDate, $toDate,$semId) | |
| { | |
| $batchId = $this->realEscapeString($batchId); | |
| $fromDate = $this->realEscapeString($fromDate); | |
| $toDate = $this->realEscapeString($toDate); | |
| $semId = $this->realEscapeString($semId); | |
| $staffTimetable = []; | |
| $sql = "select DISTINCT(attendanceDate) | |
| from attendance_confirm | |
| where semID=\"$semId\" | |
| and batchID=\"$batchId\" | |
| and attendanceDate BETWEEN '$fromDate' | |
| AND '$toDate' ORDER BY attendanceDate "; | |
| try { | |
| $staffTimetable = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $staffTimetable; | |
| } | |
| public function isTimetableExchangeEnabled($userType) | |
| { | |
| $userType = $this->realEscapeString($userType); | |
| $enabled = 0; | |
| $sql = "SELECT enabled FROM timetable_exchange WHERE userType = '$userType' "; | |
| try { | |
| $enabled = $this->executeQueryForObject($sql)->enabled; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $enabled; | |
| } | |
| /** | |
| * Get time range of hours of a batch on corresponding date, if date is null get latest time range | |
| * @param Integer $batchID | |
| * @return ObjectList $timeRanges | |
| * @throws ProfessionalException | |
| * @author Anoop | |
| */ | |
| public function getHourwiseTimeRangeOfTimetable($batchID, $date = NULL, $subBatchId = NULL, $semID = NULL) | |
| { | |
| $batchID = $this->realEscapeString($batchID); | |
| $date = $this->realEscapeString($date); | |
| $timeRanges = []; | |
| $sqlDate = ""; | |
| $sqlSubBatch = ""; | |
| $sqlOrderBy = " ORDER BY bt.timetableDate DESC, bt.hourId ASC"; | |
| try { | |
| if(empty((int)$semID)) | |
| { | |
| $semID = BatchService::getInstance()->getBatchDetails($batchID)->currentSemId; | |
| } | |
| // $sql = "SELECT DISTINCT bt.hourID, bt.stratTime AS startTime, bt.endTime FROM batch_timetable bt WHERE bt.batchID = $batchID AND bt.semID = $semID GROUP BY bt.hourId ORDER BY bt.timetableDate DESC, bt.hourId ASC"; | |
| if ($date) { | |
| // $sql = "SELECT DISTINCT bt.hourID, bt.stratTime AS startTime, bt.endTime FROM batch_timetable bt WHERE bt.batchID=$batchID AND bt.semID = $semID AND bt.timetableDate = '$date' GROUP BY bt.hourId ORDER BY bt.hourId"; | |
| $sqlDate = " AND bt.timetableDate = '$date'"; | |
| $sqlOrderBy = " ORDER BY bt.hourId"; | |
| } | |
| if ($subBatchId != NULL) { | |
| $sqlSubBatch = " AND bt.subbatchID = $subBatchId"; | |
| } | |
| $sql = "SELECT DISTINCT bt.hourID, bt.stratTime AS startTime, bt.endTime FROM batch_timetable bt WHERE bt.batchID = $batchID AND bt.semID = $semID" . $sqlDate . $sqlSubBatch . " GROUP BY bt.hourId" . $sqlOrderBy; | |
| $timeRanges = $this->executeQueryForList($sql); | |
| foreach ($timeRanges as $timeRange) { | |
| $hourTimeRange [$timeRange->hourID] = $timeRange; | |
| } | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $hourTimeRange; | |
| } | |
| /** | |
| * Get whether new timetable to be assigned is to be locked or not. | |
| * @return int | |
| * @throws ProfessionalException | |
| * @author Kelvin | |
| */ | |
| public function isNewTimetableTobeLocked() | |
| { | |
| $isLock = 0; | |
| $sql = "SELECT disabled FROM timetable_settings"; | |
| try { | |
| $isLock = (int)$this->executeQueryForObject($sql)->disabled; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $isLock; | |
| } | |
| /** | |
| * update timetable hour is allowed ie, lock or unlock hour | |
| * @param Timetable $timetable | |
| * @throws ProfessionalException | |
| */ | |
| public function updateIsAllowed(Timetable $timetable) | |
| { | |
| $timetable = $this->realEscapeObject($timetable); | |
| $sql ="UPDATE batch_timetable SET is_allowed = ".$timetable->is_allowed." WHERE batchID = ".$timetable->batchID." AND subbatchID = ".$timetable->subbatchID." AND semID = ".$timetable->semID." AND hourID = ".$timetable->hourID." AND dayID = ".$timetable->dayID." AND timetableDate = \"".$timetable->timetableDate."\" AND sbsID = ".$timetable->sbsID; | |
| try { | |
| $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * update timetable is allowed for all hours | |
| * @param Timetable $timetable | |
| * @throws ProfessionalException | |
| */ | |
| public function updateIsAllowedForAllHours(Timetable $timetable) | |
| { | |
| $timetable = $this->realEscapeObject($timetable); | |
| $sql ="UPDATE batch_timetable SET is_allowed = ".$timetable->is_allowed." WHERE batchID = ".$timetable->batchID." AND subbatchID = ".$timetable->subbatchID." AND semID = ".$timetable->semID." AND timetableDate BETWEEN \"".$timetable->fromDate."\" AND \"".$timetable->toDate."\""; | |
| try { | |
| $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * swapp timetable to other faculties | |
| * @param Timetable $timetable | |
| * @param array $staffList | |
| * @throws ProfessionalException | |
| */ | |
| public function swapTimetable(Timetable $timetable, $staffList) | |
| { | |
| $sql_insert ="INSERT INTO batch_timetable (batchID, subjectID, dayID, hourID, semID, sbsID, is_allowed, stratTime, endTime, subbatchID, timetableDate, course_timetable_id ) VALUES "; | |
| $value=[]; | |
| foreach ($staffList AS $staff) | |
| { | |
| $staff = (object)$staff; | |
| $value[] = "(".$timetable->batchID.", $staff->subjectID, ".$timetable->dayID.", ".$timetable->hourID.", ".$timetable->semID.", ".$staff->sbsID.", 0, \"".$timetable->stratTime."\", \"".$timetable->endTime."\", ".$timetable->subbatchID.", \"".$timetable->timetableDate."\", \"".$timetable->course_timetable_id."\")"; | |
| } | |
| $sql_insert .=implode(', ', $value); | |
| $sql_del = "DELETE FROM batch_timetable WHERE batchID = ".$timetable->batchID." AND subbatchID = ".$timetable->subbatchID." AND semID = ".$timetable->semID." AND hourID = ".$timetable->hourID." AND dayID = ".$timetable->dayID." AND timetableDate = \"".$timetable->timetableDate."\" AND sbsID = ".$timetable->sbsID.""; | |
| try { | |
| $this->executeQuery($sql_insert); | |
| $this->executeQuery($sql_del); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * timetable details of batch by date | |
| * @param Timetable $timetable | |
| * @throws ProfessionalException | |
| * @return ObjectList $timeTableDetails | |
| */ | |
| public function getTimeTableDetailsOfBatchByDate(Timetable $timetable){ | |
| $timetable = $this->realEscapeObject($timetable); | |
| $sql = "SELECT DISTINCT s.staffName, s.staffCode, s.staffID ,b.hourID, sbs.subjectID,sub.subjectName,sub.subjectDesc FROM batch_timetable b,staffaccounts s INNER JOIN sbs_relation sbs ON sbs.staffID = s.staffID INNER JOIN subjects sub ON sub.subjectID = sbs.subjectID WHERE b.dayID=$timetable->dayID AND b.batchID = $timetable->batchID AND b.semID = $timetable->semID AND b.sbsID=sbs.sbsID AND b.timetableDate='$timetable->timetableDate'"; | |
| try { | |
| $timeTableDetails = $this->executeQueryForList($sql, $this->mapper[TimetableServiceMapper::GET_DAY_WISE_TIMETABLE_OF_BATCH]); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $timeTableDetails; | |
| } | |
| /** | |
| * update hour time | |
| * @param BatchTimetable $batchTimetable | |
| * @return object|NULL|$objectList[] | |
| * @throws ProfessionalException | |
| */ | |
| public function updateTimetableMeetingId($batchTimetable) | |
| { | |
| $this->realEscapeObject($batchTimetable); | |
| if($batchTimetable->pseudoSubjectId) | |
| { | |
| $sql = "UPDATE batch_timetable bt inner join pseudosubjects_sbs ps ON ps.sbsID=bt.sbsID SET bt.oicMeetingId='$batchTimetable->oicMeetingId' WHERE ps.pseudosubjectID='$batchTimetable->pseudoSubjectId' AND bt.timetableDate='" . date('Y-m-d', strtotime($batchTimetable->timetableDate)) . "' AND bt.hourID=$batchTimetable->hourId"; | |
| } | |
| else | |
| { | |
| $sql = "UPDATE batch_timetable SET oicMeetingId = '" . $batchTimetable->oicMeetingId . "' WHERE batchID= '$batchTimetable->batchId' AND subbatchID='$batchTimetable->subbatchId' AND semID='$batchTimetable->semId' AND hourID='$batchTimetable->hourId' AND sbsID='$batchTimetable->sbsID' AND timetableDate = '" . date('Y-m-d', strtotime($batchTimetable->timetableDate)) . "'"; | |
| } | |
| try { | |
| return $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * meeting scheduled hour details | |
| * @param $meetingId | |
| * @return array $timeTableDetails | |
| * @throws ProfessionalException | |
| */ | |
| public function getHourDetailsByMeetingId($meetingId){ | |
| $meetingId = $this->realEscapeObject($meetingId); | |
| $sql = "SELECT * FROM batch_timetable WHERE oicMeetingId = '$meetingId'"; | |
| try { | |
| return $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * get staff timetable in date range based on subject course Type Id | |
| * @param $staffId, $fromDate, $toDate, $CourseTypeID | |
| * @return array $staffTimetable | |
| * @throws ProfessionalException | |
| */ | |
| public function getStaffTimetableInDateRangeByCourseTypeId($staffId, $fromDate, $toDate, $CourseTypeID) | |
| { | |
| $staffId = $this->realEscapeString($staffId); | |
| $fromDate = $this->realEscapeString($fromDate); | |
| $toDate = $this->realEscapeString($toDate); | |
| try { | |
| $durations =json_decode(CommonService::getInstance()->getSettings(SettingsConstants::ONLINE_CLASS,SettingsConstants::ONLINE_CLASS_LIST_DURATIONS)); | |
| } catch (\Throwable $th) { | |
| $durations->upComingMeetingShowDurationInMinutes = 5; | |
| $durations->onGoingMeetingShowDurationInMinutes = 40; | |
| } | |
| $staffTimetable = []; | |
| $sql = "SELECT distinct sr.staffID, bt.sbsID, sub.subjectName, sa.staffCode, sr.subjectID, sub.subjectDesc, sa.staffName, bth.batchName, bt.subbatchID, bt.is_allowed, bt.batchID, bt.semID, bt.stratTime, bt.endTime, bt.timetableDate,bt.hourID , if(ps.sbsID,'1','0') as isPSubject, bt.dayID, ps.pseudosubjectID, psub.subjectName as psName, bt.course_timetable_id, asd.id as requestId, null as confirmingStaffId, asd.request_completed, asd.rule, bt.oicMeetingId,IF(now() BETWEEN om.meeting_date+INTERVAL -".$durations->upComingMeetingShowDurationInMinutes." MINUTE AND om.meeting_date +INTERVAL +".$durations->onGoingMeetingShowDurationInMinutes." MINUTE,1,0) AS liveNow, om.meetings_details, ou.user_details as userDetails,ou.oic_app_id as appId, IF(now() < om.meeting_date+INTERVAL -".$durations->upComingMeetingShowDurationInMinutes." MINUTE,1,0) AS upComing, ct.courseTypeID FROM batch_timetable bt INNER JOIN sbs_relation sr ON bt.sbsID = sr.sbsID AND bt.batchID=sr.batchID INNER JOIN batches bth ON sr.batchID=bth.batchID AND sr.semID=bth.semID INNER JOIN subjects sub ON sub.subjectID = sr.subjectID INNER JOIN course_type ct ON ct.courseTypeID = bth.courseTypeID INNER JOIN staffaccounts sa ON sa.staffID = sr.staffID LEFT JOIN pseudosubjects_sbs ps ON ps.sbsID = sr.sbsID left join pseudosubjects psub on psub.pseudosubjectID = ps.pseudosubjectID LEFT JOIN attendance_swapping_details asd ON asd.date=bt.timetableDate AND asd.hour=bt.hourID AND asd.sbs_id=bt.sbsID LEFT JOIN oic_meetings om ON om.id=bt.oicMeetingId LEFT JOIN oic_users ou ON om.oic_users_id = ou.id WHERE bt.timetableDate BETWEEN '$fromDate' AND '$toDate' AND sr.staffID = '$staffId' AND ct.courseTypeID = $CourseTypeID UNION SELECT distinct sr.staffID, bt.sbsID, sub.subjectName, sa.staffCode, sr.subjectID, sub.subjectDesc, sa.staffName, bth.batchName, bt.subbatchID, bt.is_allowed, bt.batchID, bt.semID, bt.stratTime, bt.endTime, bt.timetableDate,bt.hourID , if(ps.sbsID,'1','0') as isPSubject, bt.dayID, ps.pseudosubjectID, psub.subjectName as psName, bt.course_timetable_id, asd.id as requestId, acs.staff_id as confirmingStaffId, asd.request_completed, asd.rule, bt.oicMeetingId, IF(now() BETWEEN om.meeting_date+INTERVAL -".$durations->upComingMeetingShowDurationInMinutes." MINUTE AND om.meeting_date +INTERVAL +".$durations->onGoingMeetingShowDurationInMinutes." MINUTE,1,0) AS liveNow, om.meetings_details, ou.user_details as userDetails, ou.oic_app_id as appId, IF(now() < om.meeting_date+INTERVAL -".$durations->upComingMeetingShowDurationInMinutes." MINUTE,1,0) AS upComing, ct.courseTypeID FROM batch_timetable bt INNER JOIN attendance_swapping_details asd ON asd.date=bt.timetableDate AND asd.hour=bt.hourID AND asd.sbs_id=bt.sbsID INNER JOIN attendance_confirming_staff acs ON acs.attendance_swapping_details_id=asd.id INNER JOIN sbs_relation sr ON bt.sbsID = sr.sbsID AND bt.batchID=sr.batchID INNER JOIN batches bth ON sr.batchID=bth.batchID AND sr.semID=bth.semID INNER JOIN subjects sub ON sub.subjectID = sr.subjectID INNER JOIN course_type ct ON ct.courseTypeID = bth.courseTypeID INNER JOIN staffaccounts sa ON sa.staffID = sr.staffID LEFT JOIN pseudosubjects_sbs ps ON ps.sbsID = sr.sbsID left join pseudosubjects psub on psub.pseudosubjectID = ps.pseudosubjectID LEFT JOIN oic_meetings om ON om.id=bt.oicMeetingId LEFT JOIN oic_users ou ON om.oic_users_id = ou.id WHERE bt.timetableDate BETWEEN '$fromDate' AND '$toDate' AND acs.staff_id = '$staffId' AND ct.courseTypeID = $CourseTypeID ORDER BY timetableDate ASC, stratTime ASC, subjectID ASC, sbsID ASC"; | |
| try { | |
| $staffTimetable = $this->executeQueryForList($sql); | |
| foreach($staffTimetable as $timetable) | |
| { | |
| $timetable->liveNow=(int)$timetable->liveNow; | |
| if($timetable->meetings_details) | |
| { | |
| $timetable->meetings_details = json_decode($timetable->meetings_details); | |
| $timetable->userDetails = json_decode($timetable->userDetails); | |
| } | |
| if($timetable->meetingDate){ | |
| $meetingDate = strtotime($timetable->meetingDate); | |
| $timetable->upComing = strtotime(date("Y-m-d H:i:s")) < strtotime("-".$durations->upComingMeetingShowDurationInMinutes." minutes",$meetingDate)?"1":"0"; | |
| $timetable->liveNow = strtotime("-".$durations->upComingMeetingShowDurationInMinutes." minutes",$meetingDate) < strtotime(date("Y-m-d H:i:s")) && strtotime(date("Y-m-d H:i:s")) < strtotime("+".$durations->onGoingMeetingShowDurationInMinutes." minutes",$meetingDate)?"1":"0"; | |
| $timetable->expiredMeeting = strtotime("+".$durations->onGoingMeetingShowDurationInMinutes." minutes",$meetingDate) < strtotime(date("Y-m-d H:i:s"))?"1":"0"; | |
| } | |
| } | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $staffTimetable; | |
| } | |
| /** | |
| * Get All timetable hours | |
| * @return object|array|$objectList[] | |
| * @throws ProfessionalException | |
| */ | |
| public function getAllTimetableHours() | |
| { | |
| $hours = []; | |
| $sql = "SELECT id,name,order,createdBy,createdDate FROM timetable_hour "; | |
| try { | |
| $hours = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $hours; | |
| } | |
| /** | |
| * Get count of batch timetable y request | |
| * @return object|array|$objectList[] | |
| * @throws ProfessionalException | |
| */ | |
| public function getBatchTimetableCount($request) | |
| { | |
| $cond = ''; | |
| if($request->batchID){ | |
| $cond .="AND batchID = '$request->batchID' "; | |
| } | |
| if($request->sbsID){ | |
| $cond .="AND sbsID = '$request->sbsID' "; | |
| } | |
| if($request->semID){ | |
| $cond .="AND semID = '$request->semID' "; | |
| } | |
| if($request->subjectID){ | |
| $cond .="AND subjectID = '$request->subjectID' "; | |
| } | |
| try { | |
| $sql = "SELECT | |
| COUNT(*) AS total | |
| FROM | |
| batch_timetable | |
| WHERE | |
| timetableDate BETWEEN '$request->fromDate' AND '$request->toDate' "; | |
| if($cond != ''){ | |
| $sql .=$cond; | |
| } | |
| $count = $this->executeQueryForObject($sql); | |
| return $count; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return false; | |
| } | |
| /** | |
| * Get timetable details | |
| * @param Object $request | |
| * @return object|array|$objectList[] | |
| * @throws ProfessionalException | |
| */ | |
| public function getTimetableDetails($request) | |
| { | |
| $cond = ''; | |
| if($request->courseTypeID){ | |
| $cond .="AND bth.courseTypeID = '$request->courseTypeID' "; | |
| } | |
| if($request->deptID){ | |
| $cond .="AND bth.deptID = '$request->deptID' "; | |
| } | |
| if($request->batchID){ | |
| $cond .="AND bt.batchID = '$request->batchID' "; | |
| } | |
| if($request->sbsID){ | |
| $cond .="AND bt.sbsID = '$request->sbsID' "; | |
| } | |
| if($request->semID){ | |
| $cond .="AND bt.semID = '$request->semID' "; | |
| } | |
| if($request->subjectID){ | |
| $cond .="AND bt.subjectID = '$request->subjectID' "; | |
| } | |
| try { | |
| $sql = "SELECT | |
| distinct(bth.batchID),bth.batchName | |
| FROM | |
| batch_timetable bt inner join batches bth on bt.batchID = bth.batchID | |
| WHERE | |
| bt.timetableDate BETWEEN '$request->fromDate' AND '$request->toDate' "; | |
| if($cond != ''){ | |
| $sql .=$cond; | |
| } | |
| $batchList = $this->executeQueryForList($sql); | |
| foreach($batchList as $batch){ | |
| $request->batchID = $batch->batchID; | |
| $count = TimetableService::getInstance()->getBatchTimetableCount($request); | |
| $batch->count = $count->total; | |
| } | |
| $result = new stdClass(); | |
| $result->batchList = $batchList; | |
| $result->courseTypeID = $request->courseTypeID; | |
| $result->deptID = $request->deptID; | |
| $result->batchID = $request->batchID; | |
| $result->sbsID = $request->sbsID; | |
| $result->semID = $request->semID; | |
| $result->subjectID = $request->subjectID; | |
| $result->fromDate = $request->fromDate; | |
| $result->toDate = $request->toDate; | |
| return $result; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return false; | |
| } | |
| /** | |
| * Get timetable details | |
| * @param Object | |
| * @return object|array|$objectList[] | |
| * @throws ProfessionalException | |
| */ | |
| public function getBatchTimetableByRequest($request) | |
| { | |
| $cond = ''; | |
| if($request->courseTypeID){ | |
| $cond .="AND bth.courseTypeID = '$request->courseTypeID' "; | |
| } | |
| if($request->deptID){ | |
| $cond .="AND bth.deptID = '$request->deptID' "; | |
| } | |
| if($request->batchID){ | |
| $cond .="AND bt.batchID = '$request->batchID' "; | |
| } | |
| if($request->sbsID){ | |
| $cond .="AND bt.sbsID = '$request->sbsID' "; | |
| } | |
| if($request->semID){ | |
| $cond .="AND bt.semID = '$request->semID' "; | |
| } | |
| if($request->subjectID){ | |
| $cond .="AND bt.subjectID = '$request->subjectID' "; | |
| } | |
| if($request->timetableID){ | |
| $cond .="AND bt.timetableID = '$request->timetableID' "; | |
| } | |
| try { | |
| $sql = "SELECT | |
| timetableID AS id, | |
| bth.batchName, | |
| bt.batchID, | |
| bt.subjectID, | |
| bt.hourID, | |
| bt.semID, | |
| bt.sbsID, | |
| bt.timetableDate, | |
| bt.dayID, | |
| tday.name as dayName, | |
| sub.subjectName, | |
| sa.staffName, | |
| sem.semName | |
| FROM | |
| batch_timetable bt inner join batches bth on bt.batchID = bth.batchID | |
| inner join subjects sub on sub.subjectID = bt.subjectID | |
| inner join sbs_relation sbs on sbs.sbsID = bt.sbsID | |
| inner join staffaccounts sa on sa.staffID = sbs.staffID | |
| inner join timetable_dayName tday on tday.order = bt.dayID | |
| inner join semesters sem on sem.semID = bt.semID | |
| WHERE | |
| bt.timetableDate BETWEEN '$request->fromDate' AND '$request->toDate' | |
| "; | |
| if($cond != ''){ | |
| $sql .=$cond; | |
| } | |
| $sql .="ORDER BY bt.timetableDate ASC, bt.hourID"; | |
| if($request->currentPage){ | |
| $recordsPerPage = 20; | |
| $clickedPage = $request->currentPage; | |
| $startFrom = ($clickedPage -1) * $recordsPerPage; | |
| $sql .=" limit ".$startFrom." , ".$recordsPerPage; | |
| } | |
| $result = $this->executeQueryForList($sql); | |
| if($result){ | |
| return $result; | |
| } | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return false; | |
| } | |
| /** | |
| * Delete batch Timetable by ID | |
| * DON'T USE THIS METHOD IF ATTENDANCE IS MARKED | |
| * @param Int $timetableID | |
| * @return Boolean | |
| * @throws ProfessionalException | |
| */ | |
| public function deleteBatchTimetableById($timetableID) | |
| { | |
| try { | |
| $sql = "DELETE | |
| FROM | |
| batch_timetable | |
| WHERE | |
| timetableID = '$timetableID' | |
| "; | |
| if($this->executeQuery($sql)){ | |
| return true; | |
| } | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return false; | |
| } | |
| /* | |
| * Get batch timietable between date range | |
| * @param Int $sbsID $fromdate $todate | |
| * @return object|array|$objectList[] | |
| * @throws ProfessionalException | |
| */ | |
| public function getBatchTimetableInDateRangeBySbsID($sbsID,$fromDate,$toDate) | |
| { | |
| $sql = "SELECT | |
| timetableID, | |
| batchID, | |
| subjectID, | |
| dayID, | |
| hourID, | |
| semID, | |
| sbsID, | |
| timetableDate | |
| FROM | |
| batch_timetable | |
| WHERE | |
| timetableDate BETWEEN '$fromDate' AND '$toDate' | |
| AND sbsID = '$sbsID' | |
| "; | |
| try { | |
| $result = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $result; | |
| } | |
| /* | |
| * Get all Course File TimeTable | |
| * @return ARRAY $batch_exam_types | |
| */ | |
| public function getCourseFileTimeTable($sbsId) | |
| { | |
| $sbsId = $this->realEscapeString($sbsId); | |
| try { | |
| $sql = "SELECT dayID, hourID,mode FROM batch_timetable_coursefile WHERE sbsID = '$sbsId';"; | |
| $timetable = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $timetable; | |
| } | |
| /* | |
| * Get all Course File TimeTable | |
| * @return ARRAY $batch_exam_types | |
| */ | |
| public function getCourseFileTimeTableDetails($sbsId,$startDate,$endDate) | |
| { | |
| $sbsId = $this->realEscapeString($sbsId); | |
| $startDate = $this->realEscapeString($startDate); | |
| $endDate = $this->realEscapeString($endDate); | |
| try { | |
| $sql = "SELECT t1.dayID, t1.hourID, t1.timetableDate, t2.mode FROM batch_timetable t1 LEFT JOIN subjectplan t2 ON (t1.timetableDate = FROM_UNIXTIME(t2.date, '%Y-%m-%d') AND t1.sbsID = t2.sbsID AND t1.hourID = t2.hour) WHERE t1.sbsID = '".$sbsId."' AND t1.timetableDate BETWEEN '".$startDate."' AND '".$endDate."' AND t2.mode is not null"; | |
| $timetable = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $timetable; | |
| } | |
| public function getBatchTimetableWeeks($batchId,$semId,$sbsId){ | |
| $sbsId = $this->realEscapeString($sbsId); | |
| $batchId = $this->realEscapeString($batchId); | |
| $semId = $this->realEscapeString($semId); | |
| try { | |
| $sql = "SELECT SUBDATE(MIN(timetableDate), WEEKDAY(MIN(timetableDate))) AS week_startDate, DATE_ADD(SUBDATE(MIN(timetableDate), WEEKDAY(MIN(timetableDate))), INTERVAL 5 DAY) AS week_endDate FROM batch_timetable WHERE batchID = " . $batchId . " AND semID = " . $semId . " AND sbsID = " . $sbsId . ""; | |
| $timetableWeeks = $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $timetableWeeks; | |
| } | |
| public function getPseudoSubjectTimetableWeeks($pseudoSubjectId){ | |
| $pseudoSubjectId = $this->realEscapeString($pseudoSubjectId); | |
| try { | |
| $sql = "SELECT SUBDATE(MIN(timetableDate), WEEKDAY(MIN(timetableDate))) AS week_startDate, DATE_ADD(SUBDATE(MIN(timetableDate), WEEKDAY(MIN(timetableDate))), INTERVAL 5 DAY) AS week_endDate FROM batch_timetable bt | |
| inner join pseudosubjects_sbs psbs on psbs.sbsID = bt.sbsID | |
| WHERE psbs.pseudosubjectID = '".$pseudoSubjectId."';"; | |
| $timetableWeeks = $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $timetableWeeks; | |
| } | |
| /* | |
| * Get all Course File TimeTable | |
| * @return ARRAY $batch_exam_types | |
| */ | |
| public function getCourseFilePseudoSubjectTimeTableDetails($pseudoSubjectId,$startDate,$endDate) | |
| { | |
| $pseudoSubjectId = $this->realEscapeString($pseudoSubjectId); | |
| $startDate = $this->realEscapeString($startDate); | |
| $endDate = $this->realEscapeString($endDate); | |
| try { | |
| $sql = "SELECT t1.dayID, t1.hourID, t1.timetableDate, t2.mode | |
| FROM batch_timetable t1 | |
| INNER JOIN pseudosubjects_sbs t3 on t3.sbsID = t1.sbsID | |
| INNER JOIN ps_subjectplan t2 ON (t1.timetableDate = FROM_UNIXTIME(t2.date, '%Y-%m-%d') AND t1.hourID = t2.hour AND t2.pssId = t3.pseudosubjectID) | |
| WHERE t3.pseudosubjectID = '$pseudoSubjectId' AND t1.timetableDate BETWEEN '".$startDate."' AND '".$endDate."' AND t2.mode IS NOT NULL;"; | |
| $timetable = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $timetable; | |
| } | |
| /** | |
| * Get day Order Name of a date by batchId | |
| * @param string $timetableDate | |
| * @param int $batchId | |
| * @return int dayOrder | |
| * @throws ProfessionalException | |
| */ | |
| public function getdayOrderNameByTimetableDateForPseudoSubject($timetableDate, $pseudoSubjectId) | |
| { | |
| $pseudoSubjectId = $this->realEscapeString($pseudoSubjectId); | |
| $timetableDate = date('Y-m-d', strtotime($timetableDate)); | |
| $dayOrder = NULL; | |
| //Check whether this day is common holiday | |
| if (!CalendarService::getInstance()->isCollegeHoliday($timetableDate)) { | |
| $sql = "SELECT distinct dayOrder.name | |
| FROM batch_timetable bt | |
| INNER JOIN pseudosubjects_sbs psbs ON psbs.sbsID = bt.sbsID | |
| INNER JOIN timetable_dayOrder dayOrder ON bt.dayOrderId = dayOrder.order | |
| WHERE bt.timetableDate = '$timetableDate' and psbs.pseudosubjectID = '$pseudoSubjectId'"; | |
| try { | |
| $dayOrder = $this->executeQueryForObject($sql)->name; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException ($e->getCode(), $e->getMessage()); | |
| } | |
| } else { | |
| return null; | |
| } | |
| return $dayOrder; | |
| } | |
| /* | |
| * Get all Course File TimeTable | |
| * @return ARRAY $batch_exam_types | |
| */ | |
| public function getCourseFilePsTimeTable($pseudoSubjectId) | |
| { | |
| $pseudoSubjectId = $this->realEscapeString($pseudoSubjectId); | |
| try { | |
| $sql = "SELECT dayID, hourID,mode FROM batch_timetable_coursefile WHERE psId = '$pseudoSubjectId';"; | |
| $timetable = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $timetable; | |
| } | |
| public function getCourseFileConstants() | |
| { | |
| try { | |
| $sql = "SELECT id, constant,label | |
| from course_settings_staff_order | |
| where is_hidden = '0' AND type = '".CourseFileAndDiary::COURSE_FILE."';"; | |
| return $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| public function getCourseDiaryConstants() | |
| { | |
| try { | |
| $sql = "SELECT id, constant,label | |
| from course_file_and_diary_constants | |
| where is_hidden = '0' AND type = '".CourseFileAndDiary::COURSE_DIARY."' "; | |
| return $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| public function updateCourseFileConstants($request) | |
| { | |
| try { | |
| $sql = "UPDATE course_file_and_diary_constants | |
| SET label = '$request->customLabel' WHERE id= $request->constantId "; | |
| $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| public function updateCourseDiaryConstants($request) | |
| { | |
| try { | |
| $sql = "UPDATE course_file_and_diary_constants | |
| SET label = '$request->customLabel' WHERE id= $request->constantId "; | |
| $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| public function getCourseFileConstantsForRender() | |
| { | |
| try { | |
| $sql = "SELECT constant as const,label as name from course_file_and_diary_constants where is_hidden = '0' AND type = '".CourseFileAndDiary::COURSE_FILE."' "; | |
| return $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| public function getCourseDiaryConstantsForRender() | |
| { | |
| try { | |
| $sql = "SELECT constant as const,label as name from course_file_and_diary_constants where is_hidden = '0' AND type = '".CourseFileAndDiary::COURSE_DIARY."' "; | |
| return $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * Get timetable details | |
| * @param Object | |
| * @return object|array|$objectList[] | |
| * @throws ProfessionalException | |
| */ | |
| public function getTimetableDetailsByRequest($request) | |
| { | |
| $where = []; | |
| $request->courseTypeId ? $where [] = "b.courseTypeID = '$request->courseTypeId' " : null ; | |
| $request->deptId ? $where [] = "b.deptID = '$request->deptId' " : null ; | |
| $request->batchId ? $where [] = "bt.batchID = '$request->batchId' " : null ; | |
| $request->sbsId ? $where [] = "bt.sbsID = '$request->sbsId' " : null ; | |
| $request->semId ? $where [] = "bt.semID = '$request->semId' " : null ; | |
| $request->subjectId ? $where [] = "bt.subjectID = '$request->subjectID' " : null ; | |
| $request->timetableId ? $where [] = "bt.timetableID = '$request->timetableID' " : null ; | |
| $request->fromDate && !$request->toDate ? $where [] = "bt.timetableDate = '$request->fromDate'" : null ; | |
| !$request->fromDate && $request->toDate ? $where [] = "bt.timetableDate = '$request->toDate'" : null ; | |
| $request->fromDate && $request->toDate ? $where [] = "bt.timetableDate BETWEEN '$request->fromDate' AND '$request->toDate'" : null ; | |
| try { | |
| $sql = | |
| "SELECT | |
| b.batchId,b.deptId,bt.timetableDate AS date,JSON_ARRAYAGG(json_object( | |
| 'id',bt.timetableID, | |
| 'hourId',bt.hourID, | |
| 'start',bt.stratTime, | |
| 'end',bt.endTime, | |
| 'staff',st.staffName, | |
| 'staffCode',st.staffCode, | |
| 'subject',s.subjectDesc, | |
| 'subjectCode',s.subjectName | |
| )) AS time_table | |
| FROM batch_timetable bt | |
| INNER JOIN sbs_relation sbs ON sbs.sbsID = bt.sbsID | |
| INNER JOIN staffaccounts st ON st.staffID = sbs.staffID | |
| INNER JOIN subjects s ON s.subjectID = sbs.subjectID | |
| INNER JOIN batches b ON b.batchID = sbs.batchID | |
| ".($where?" WHERE ".implode(' AND ',$where):"")." | |
| GROUP BY bt.timetableDate | |
| ORDER BY bt.timetableDate ASC, bt.hourID;"; | |
| $this->executeQuery("SET SESSION group_concat_max_len = 1000000;"); | |
| $timetable = $this->executeQueryForList($sql); | |
| foreach ($timetable as $key => $data) { | |
| $data->time_table = json_decode($data->time_table); | |
| } | |
| return $timetable; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * get Assigned hour for a staff in batch within date range | |
| * @author Ajay C | |
| * @return List | |
| * @param batchId,staffId,fromDate,toDate | |
| */ | |
| public function getTimeTableAssignedToStaffWithStaffDetails($batchId = null,$staffId = null,$fromDate = null,$toDate = null) | |
| { | |
| try | |
| { | |
| $batchId = $this->realEscapeString($batchId); | |
| $staffId = $this->realEscapeString($staffId); | |
| $fromDate = $fromDate ? implode("-",array_reverse(explode("-", $fromDate))):""; | |
| $toDate = $toDate ? implode("-",array_reverse(explode("-", $toDate))):""; | |
| $staffId?$where[] = " s.staffID = $staffId ":""; | |
| $batchId?$where[] = " bt.batchID = $batchId ":""; | |
| $fromDate && $toDate?$where[] = " bt.timetableDate between '$fromDate' and '$toDate' ":""; | |
| $fromDate && $toDate?$where[] = " bt.timetableDate NOT IN (SELECT FROM_UNIXTIME(timeStart, '%Y-%m-%d')from lms_calender lc where timeStart BETWEEN UNIX_TIMESTAMP('$fromDate') and UNIX_TIMESTAMP('$toDate') and flag =3)":""; | |
| $cond = ($where?" WHERE ".implode(' AND ',$where):""); | |
| $sql = "SELECT | |
| bt.* | |
| from | |
| batch_timetable bt | |
| inner join sbs_relation sr on | |
| sr.sbsID = bt.sbsID | |
| inner join staffaccounts s on | |
| s.staffID = sr.staffID | |
| $cond"; | |
| return $this->executeQueryForList($sql); | |
| } | |
| catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| } |