Code Coverage |
||||||||||
Classes and Traits |
Functions and Methods |
Lines |
||||||||
| Total | |
0.00% |
0 / 1 |
|
0.00% |
0 / 98 |
CRAP | |
0.00% |
0 / 2173 |
| PseudoSubjectService | |
0.00% |
0 / 1 |
|
0.00% |
0 / 98 |
134322.00 | |
0.00% |
0 / 2173 |
| __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 |
|||
| getbatchIDsOfStaff | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 12 |
|||
| getsubbatchIDsAndbatchIDs | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 10 |
|||
| getbatchListByPseudosubjectId | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 16 |
|||
| getGroupIdByBatchId | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 11 |
|||
| isAttendanceMarked | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 14 |
|||
| isAllowed | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 15 |
|||
| deleteAttendanceConfirmTemporary | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 14 |
|||
| deleteAttendanceTemporary | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 14 |
|||
| isAllowedToChange | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 15 |
|||
| isSuspendedHours | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 13 |
|||
| insertAttendanceConfirm | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 16 |
|||
| callProcedureCopyAttendance | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 17 |
|||
| isAttendanceMarkedDate | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 26 |
|||
| isHourAssigned | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 22 |
|||
| assignAttendanceToHour | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 16 |
|||
| confirmAttendanceMarked | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 19 |
|||
| isHoliday | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 25 |
|||
| createPseudoSubjectGroup | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 14 |
|||
| getAllSubjectGroup | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 10 |
|||
| updatePseudoSubjectGroup | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 16 |
|||
| getCountOfPseudoSubjectGroupSubjectsByGroupId | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 13 |
|||
| deletePseudoSubjectGroup | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 9 |
|||
| getAllPseudoSubjectType | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 8 |
|||
| createPseudoSubject | |
0.00% |
0 / 1 |
132.00 | |
0.00% |
0 / 50 |
|||
| getPseudoSubjectTypeById | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 9 |
|||
| getAllPseudoSubjects | |
0.00% |
0 / 1 |
342.00 | |
0.00% |
0 / 82 |
|||
| deletePseudoSubjectById | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 14 |
|||
| getPseudoSubjectsNotAssigned | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 23 |
|||
| checkGivenPseudoSubjectIsFromGivenCBSType | |
0.00% |
0 / 1 |
30.00 | |
0.00% |
0 / 19 |
|||
| updatePseudoSubject | |
0.00% |
0 / 1 |
156.00 | |
0.00% |
0 / 43 |
|||
| getSubjectOfPseudoSubject | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 8 |
|||
| updatePseudoSubjectSubject | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 17 |
|||
| addSubjectToPseudoSubjects | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 19 |
|||
| getPseudoSubjectById | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 14 |
|||
| getPseudoSubjectGroupIdByPseudoSubjectId | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 12 |
|||
| blockEditPreviousPseudoSubjectDetails | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 24 |
|||
| getPseudoSubjectsBysubjectId | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 30 |
|||
| getPseudoSubjectStudentsForCopyStudents | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 30 |
|||
| getPseudoSubjectStaffs | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 16 |
|||
| getAssignedStaffAndStudentDetails | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 45 |
|||
| getAssignedPseudoSubjectStaffDetails | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 35 |
|||
| assignSubjectToPseudoSubject | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 18 |
|||
| checkSubjectAlreadyAssignedToPseudoSubject | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 14 |
|||
| assignStudentToPseudoSubject | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 17 |
|||
| unAssignStudentFromThePseudoSubject | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 16 |
|||
| getAllUnAssignedStudentDetailsByBatchIds | |
0.00% |
0 / 1 |
30.00 | |
0.00% |
0 / 27 |
|||
| getAllUnAssignedSecondLanguageStudentByBatchIds | |
0.00% |
0 / 1 |
30.00 | |
0.00% |
0 / 31 |
|||
| checkPseudoSubjectIsSecondLanguage | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 15 |
|||
| checkStudentAlreadyAssigned | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 18 |
|||
| validatePseudoSubjectProperlyAssigned | |
0.00% |
0 / 1 |
182.00 | |
0.00% |
0 / 43 |
|||
| finalisePseudoSubject | |
0.00% |
0 / 1 |
90.00 | |
0.00% |
0 / 71 |
|||
| assignPseudoSubjectStudentsToSubBatch | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 14 |
|||
| removeUnAssignedPseudoSubjectStudentsFromSubBatch | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 14 |
|||
| getPseudoSubjectStudentBatches | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 16 |
|||
| markOrUnmarkPseudoSubjectFinalized | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 14 |
|||
| isPseudoSubjectIsFinalised | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 8 |
|||
| isDeletablePseudoSubject | |
0.00% |
0 / 1 |
42.00 | |
0.00% |
0 / 23 |
|||
| isSubBatchAssignedToPseudoSubject | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 14 |
|||
| getPseudoSubjectByHandlingDepartment | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 19 |
|||
| deletePseudoSubjectUnAssignedSubbatchByPsId | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 16 |
|||
| getPseudoSubjectDetails | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 35 |
|||
| getPseudoSubjectSbsDetailsByPseudoSubjectIdAndStaffId | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 10 |
|||
| getPseudoSubjectAndAssessmentDetailsForMarkEntry | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 45 |
|||
| getPseudoSubjectBySbsId | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 12 |
|||
| getStaffPseudoSubjectDetails | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 22 |
|||
| getPseudoSubjectDistinctBatchDetailsById | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 11 |
|||
| getPseudoSubjectId | |
0.00% |
0 / 1 |
42.00 | |
0.00% |
0 / 34 |
|||
| getFinalisedPseudoSubjectId | |
0.00% |
0 / 1 |
30.00 | |
0.00% |
0 / 38 |
|||
| getPseudoSubjectStaffSBSId | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 17 |
|||
| getSiblingPseudoSubjects | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 19 |
|||
| isPseudoSubjectIsFinalisedForAssignmentDisplay | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 8 |
|||
| getPseudoSubjectDetailsByRequest | |
0.00% |
0 / 1 |
306.00 | |
0.00% |
0 / 51 |
|||
| getAllAssignedSbsIdsByPseudosubjectsIds | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 16 |
|||
| getPseudoSubjectByRequest | |
0.00% |
0 / 1 |
72.00 | |
0.00% |
0 / 65 |
|||
| getDetailsOfPseudosubjectSbs | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 11 |
|||
| getPesudeosubjectIdsbyBatchSubjectStaff | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 13 |
|||
| getPseudoSubjectsByDeptIdandStaffId | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 18 |
|||
| deletePseudoSubjectId | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 18 |
|||
| assignStaffsToPseudoSubjects | |
0.00% |
0 / 1 |
90.00 | |
0.00% |
0 / 50 |
|||
| getAllStudentBatchesAssignToPseudoSubjects | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 26 |
|||
| getAllStaffsAssignedInPseudoSubject | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 28 |
|||
| getAllStudentsAssignToPseudoSubjects | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 18 |
|||
| getPseudosubjectSubBatchAndBatchDetails | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 18 |
|||
| getPseudoSubjectBatchList | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 10 |
|||
| easyAssignStaffsToPseudoSubjects | |
0.00% |
0 / 1 |
132.00 | |
0.00% |
0 / 58 |
|||
| isMentorMenteeByPsId | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 14 |
|||
| getPseudoSubjectsUnderSubjectBySubjectId | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 9 |
|||
| checkPresenceOfStudentInPseudoSubject | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 28 |
|||
| getbatchIdsBypseudoSubjectId | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 18 |
|||
| getAllPseudoSubjectsForQuickReports | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 37 |
|||
| getStudentDetailsFromPseudoSubjectsForCourseRegistration | |
0.00% |
0 / 1 |
42.00 | |
0.00% |
0 / 50 |
|||
| getCourseTypeNameAndSemNameForCourseRegistrationReport | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 23 |
|||
| getPsAttendanceDetailsForEdit | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 35 |
|||
| updatePsAttendanceDetailsFfromEdit | |
0.00% |
0 / 1 |
42.00 | |
0.00% |
0 / 34 |
|||
| getsemIDOfFirstBatchOfPsSubjects | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 21 |
|||
| <?php | |
| namespace com\linways\core\ams\professional\service; | |
| use com\linways\core\ams\professional\constant\PseudoSubjectType; | |
| use com\linways\core\ams\professional\constant\SBSAssigningConstants; | |
| use com\linways\core\ams\professional\constant\SettingsConstants; | |
| use com\linways\core\ams\professional\dto\Attendance; | |
| use com\linways\core\ams\professional\dto\PseudoSubject; | |
| use com\linways\core\ams\professional\exception\ProfessionalException; | |
| use com\linways\core\ams\professional\mapper\PseudoSubjectServiceMapper; | |
| use com\linways\core\ams\professional\request\AddSubjectToPseudoSubjectRequest; | |
| use com\linways\core\ams\professional\request\AssignedSubjectBatchRequest; | |
| use com\linways\core\ams\professional\request\AssignFacultyToSubjectRequest; | |
| use com\linways\core\ams\professional\request\AssignPseudoSubjectStudentsToSubBatchRequest; | |
| use com\linways\core\ams\professional\request\AssignStudentToPseudoSubjectRequest; | |
| use com\linways\core\ams\professional\request\AssignSubjectToPseudoSubjectRequest; | |
| use com\linways\core\ams\professional\request\CheckSbsAlreadyAssignedRequest; | |
| use com\linways\core\ams\professional\request\CreatePseudoSubjectGroupRequest; | |
| use com\linways\core\ams\professional\request\CreatePseudoSubjectRequest; | |
| use com\linways\core\ams\professional\request\CreateSubBatchRequest; | |
| use com\linways\core\ams\professional\request\CreateSubBatchSBSRequest; | |
| use com\linways\core\ams\professional\request\FinalisePseudoSubjectRequest; | |
| use com\linways\core\ams\professional\request\GetPseudoSubjectRequest; | |
| use com\linways\core\ams\professional\request\GetSubBatchRequest; | |
| use com\linways\core\ams\professional\request\MarkOrUnmarkIsFinalisedPseudoSubjectRequest; | |
| use com\linways\core\ams\professional\request\UnAssignStudentFromSubjectRequest; | |
| use com\linways\core\ams\professional\request\UpdatePseudoGroupRequest; | |
| use com\linways\core\ams\professional\request\UpdatePseudoSubjectRequest; | |
| use com\linways\core\ams\professional\request\AssignStaffToPseudoSubjectRequest; | |
| use com\linways\core\ams\professional\response\GetAllPseudoSubjectResponse; | |
| use com\linways\core\ams\professional\util\CommonUtil as CommonUtility; | |
| use CommonUtil; | |
| use stdClass; | |
| use TheSeer\Tokenizer\Exception; | |
| use com\linways\core\ams\professional\service\StaffService; | |
| class PseudoSubjectService extends BaseService | |
| { | |
| /** | |
| * Presence of a static member variable | |
| * | |
| * @var null | |
| */ | |
| private static $_instance = null; | |
| /** | |
| * Mapper variable | |
| * @var array | |
| */ | |
| private $mapper = []; | |
| /** | |
| * Initialise mapper, logger, hooks here | |
| * | |
| * PseudoSubjectService constructor. | |
| */ | |
| private function __construct() | |
| { | |
| $this->mapper = PseudoSubjectServiceMapper::getInstance()->getMapper(); | |
| } | |
| /** | |
| * Prevent any object or instance of that class to be cloned | |
| */ | |
| private function __clone() | |
| { | |
| } | |
| /** | |
| * Have a single globally accessible static method | |
| * | |
| * @return PseudoSubjectService|null | |
| */ | |
| 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; | |
| } | |
| /** | |
| * get batch id of staff | |
| * @param $staffID | |
| * @param $pssubID | |
| * @return object|array|\com\linways\base\util\$objectList[] | |
| * @throws ProfessionalException | |
| * @author Aswin | |
| */ | |
| public function getbatchIDsOfStaff($staffID, $pssubID) | |
| { | |
| $staffID = $this->realEscapeString($staffID); | |
| $pssubID = $this->realEscapeString($pssubID); | |
| $query = "SELECT t4.pseudosubjectID, t1.staffID, t4.subjectName, t2.batchID, t2.semID, t3.sbsID FROM sbs_relation t1, batches t2, pseudosubjects_sbs t3, pseudosubjects t4 WHERE t1.staffID = \"" . $staffID . "\" AND t1.batchID=t2.batchID AND t1.semID=t2.semID AND t1.sbsID=t3.sbsID AND t3.pseudosubjectID=t4.pseudosubjectID AND t4.pseudosubjectID = \"$pssubID\""; | |
| try { | |
| $response = $this->executeQueryForList($query); | |
| $response = CommonUtility::decodeHtmlSpecialChars($response); | |
| return $response; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * get subbatchID and batchID | |
| * @param unknown $pssubID | |
| * @return object|array|\com\linways\base\util\$objectList[] | |
| * @throws ProfessionalException | |
| * @author Aswin | |
| */ | |
| public function getsubbatchIDsAndbatchIDs($pssubID) | |
| { | |
| $pssubID = $this->realEscapeString($pssubID); | |
| $query = "SELECT subbatchID, batchID, semID FROM subbatches WHERE psID = '" . $pssubID . "'"; | |
| try { | |
| $response = $this->executeQueryForList($query); | |
| return $response; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * get batch id of staff | |
| * @param $staffID | |
| * @param $pssubID | |
| * @return object|array|\com\linways\base\util\$objectList[] | |
| * @throws ProfessionalException | |
| * @author Aswin | |
| */ | |
| public function getbatchListByPseudosubjectId($pssubID) | |
| { | |
| $result = []; | |
| $pssubID = $this->realEscapeString($pssubID); | |
| try { | |
| $queryToGetDeptList = "SELECT d.deptID,d.departmentDesc from pseudosubjects p inner join pseudosubjects_sbs psbs on p.pseudosubjectID = psbs.pseudosubjectID inner join sbs_relation sbs on psbs.sbsID = sbs.sbsID INNER JOIN batches b on sbs.batchID=b.batchID inner join department d on b.deptID = d.deptID where p.pseudosubjectID = \"$pssubID\" group by d.deptID"; | |
| $deptLists = $this->executeQueryForList($queryToGetDeptList); | |
| foreach ($deptLists as $deptList) { | |
| $query = "SELECT sbs.staffID,sbs.subjectID,d.deptID,sbs.semID,sbs.batchID,d.departmentDesc,b.batchName, nba_tree.co_root_node_id as coRootNodeId, nba_tree.po_root_node_id as poRootNodeId from pseudosubjects p inner join pseudosubjects_sbs psbs on p.pseudosubjectID = psbs.pseudosubjectID inner join sbs_relation sbs on psbs.sbsID = sbs.sbsID INNER JOIN batches b on sbs.batchID=b.batchID inner join department d on b.deptID = d.deptID inner join nba_po_attainment_tree nba_tree on d.deptID=nba_tree.deptID where p.pseudosubjectID = \"$pssubID\" and d.deptID=\"$deptList->deptID\" "; | |
| $response = $this->executeQueryForList($query); | |
| $result[$deptList->departmentDesc] = $response; | |
| } | |
| return $result; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * get batch id of staff | |
| * @param $staffID | |
| * @param $pssubID | |
| * @return object|array|\com\linways\base\util\$objectList[] | |
| * @throws ProfessionalException | |
| * @author Aswin | |
| */ | |
| public function getGroupIdByBatchId($batchId) | |
| { | |
| $result = []; | |
| $pssubID = $this->realEscapeString($batchId); | |
| $query = "select n.nba_po_group_id,nbapo.name from nba_po_group_to_batches_mapping n inner join nba_po_group nbapo on n.nba_po_group_id = nbapo.id where batch_id=\"$batchId\""; | |
| try { | |
| $groupID = $this->executeQueryForObject($query); | |
| return $groupID; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * @param $batchID | |
| * @param $subbatchID | |
| * @param $semID | |
| * @param $copyFrom | |
| * @param $date | |
| * @return Object | |
| * @throws ProfessionalException | |
| */ | |
| public function isAttendanceMarked($batchID, $subbatchID, $semID, $copyFrom, $date) | |
| { | |
| $batchID = $this->realEscapeString($batchID); | |
| $subbatchID = $this->realEscapeString($subbatchID); | |
| $semID = $this->realEscapeString($semID); | |
| $copyFrom = $this->realEscapeString($copyFrom); | |
| $date = $this->realEscapeString($date); | |
| $query = "select subbatchID from attendance_confirm where batchID=$batchID and (subbatchID=$subbatchID) and semID=$semID and hour=$copyFrom and attendanceDate=\"$date\""; | |
| try { | |
| $response = $this->executeQueryForList($query); | |
| return $response; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * @param $batchID | |
| * @param $semID | |
| * @param $day | |
| * @param $copyFrom | |
| * @param $sbsID | |
| * @param $date | |
| * @return Object | |
| * @throws ProfessionalException | |
| */ | |
| public function isAllowed($batchID, $semID, $day, $copyFrom, $sbsID, $date) | |
| { | |
| $batchID = $this->realEscapeString($batchID); | |
| $semID = $this->realEscapeString($semID); | |
| $day = $this->realEscapeString($day); | |
| $copyFrom = $this->realEscapeString($copyFrom); | |
| $sbsID = $this->realEscapeString($sbsID); | |
| $date = $this->realEscapeString($date); | |
| $query = "select sbsId from batch_timetable_date where batchId=$batchID and semId=$semID and dayId=$day and hourId = $copyFrom and (sbsId=$sbsID or is_allowed = 1) and date='$date'"; | |
| try { | |
| $response = $this->executeQueryForList($query); | |
| return $response; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * @param $batchID | |
| * @param $semID | |
| * @param $subbatchID | |
| * @param $copyFrom | |
| * @param $date | |
| * @return string | |
| * @throws ProfessionalException | |
| */ | |
| public function deleteAttendanceConfirmTemporary($batchID, $semID, $subbatchID, $copyFrom, $date) | |
| { | |
| $batchID = $this->realEscapeString($batchID); | |
| $semID = $this->realEscapeString($semID); | |
| $subbatchID = $this->realEscapeString($subbatchID); | |
| $copyFrom = $this->realEscapeString($copyFrom); | |
| $date = $this->realEscapeString($date); | |
| $query = "DELETE from attendance_confirm_temporary where batchID=\"" . $batchID . "\" and semID=\"" . $semID . "\" and subbatchID=\"" . $subbatchID . "\" and hour=\"" . $copyFrom . "\" and attendanceDate=\"" . $date . "\""; | |
| try { | |
| $response = $this->executeQuery($query); | |
| return "deleted"; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * @param $batchID | |
| * @param $semID | |
| * @param $copyFrom | |
| * @param $date | |
| * @param $subbatchID | |
| * @return string | |
| * @throws ProfessionalException | |
| */ | |
| public function deleteAttendanceTemporary($batchID, $semID, $copyFrom, $date, $subbatchID) | |
| { | |
| $batchID = $this->realEscapeString($batchID); | |
| $semID = $this->realEscapeString($semID); | |
| $copyFrom = $this->realEscapeString($copyFrom); | |
| $date = $this->realEscapeString($date); | |
| $subbatchID = $this->realEscapeString($subbatchID); | |
| $query = "DELETE from attendance_temporary where batchID=\"" . $batchID . "\" and semID=\"" . $semID . "\" and hour=\"" . $copyFrom . "\" and attendanceDate=\"" . $date . "\"and subbatchID = \"" . $subbatchID . "\""; | |
| try { | |
| $response = $this->executeQuery($query); | |
| return "deleted"; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * @param $staffID | |
| * @param $day | |
| * @param $copyFrom | |
| * @param $batchID | |
| * @param $semID | |
| * @param $date | |
| * @return Object | |
| * @throws ProfessionalException | |
| */ | |
| public function isAllowedToChange($staffID, $day, $copyFrom, $batchID, $semID, $date) | |
| { | |
| $staffID = $this->realEscapeString($staffID); | |
| $day = $this->realEscapeString($day); | |
| $copyFrom = $this->realEscapeString($copyFrom); | |
| $batchID = $this->realEscapeString($batchID); | |
| $semID = $this->realEscapeString($semID); | |
| $date = $this->realEscapeString($date); | |
| $query = "select t2.staffID from batch_timetable_date t1, sbs_relation t2 where (t1.sbsId=t2.sbsID and t2.staffID=$staffID and t1.dayId=$day and t1.hourId=$copyFrom and t1.batchId=$batchID and t1.semId=$semID) and t1.date='$date'"; | |
| try { | |
| $response = $this->executeQueryForList($query); | |
| return $response; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * @param $date | |
| * @param $copyFrom | |
| * @param $batchID | |
| * @param $semID | |
| * @return Object | |
| * @throws ProfessionalException | |
| */ | |
| public function isSuspendedHours($date, $copyFrom, $batchID, $semID) | |
| { | |
| $batchID = $this->realEscapeString($batchID); | |
| $copyFrom = $this->realEscapeString($copyFrom); | |
| $semID = $this->realEscapeString($semID); | |
| $date = $this->realEscapeString($date); | |
| $query = "select hour from suspended_hours where suspendedDate=\"" . $date . "\" and hour=\"" . $copyFrom . "\" and batchID=\"" . $batchID . "\" and semID=\"" . $semID . "\""; | |
| try { | |
| $response = $this->executeQueryForList($query); | |
| return $response; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| public function insertAttendanceConfirm($batchID, $subbatchID, $semID, $copyTo, $date, $sbsID) | |
| { | |
| $batchID = $this->realEscapeString($batchID); | |
| $subbatchID = $this->realEscapeString($subbatchID); | |
| $semID = $this->realEscapeString($semID); | |
| $copyTo = $this->realEscapeString($copyTo); | |
| $date = $this->realEscapeString($date); | |
| $sbsID = $this->realEscapeString($sbsID); | |
| $staffDetails = StaffService::getInstance()->getStaffDetailsBySbsID($sbsID); | |
| $query = "insert into attendance_confirm (batchID, subbatchID, semID, hour, attendanceDate, sbsID, staffID) values( $batchID,$subbatchID,$semID,$copyTo,\"$date\",$sbsID, $staffDetails->id)"; | |
| try { | |
| $response = $this->executeQueryForObject($query, TRUE); | |
| return "inserted"; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| public function callProcedureCopyAttendance($date, $copyFrom, $copyTo, $batchID, $semID, $sbsID, $subbatchID, $staffID) | |
| { | |
| $date = $this->realEscapeString($date); | |
| $copyFrom = $this->realEscapeString($copyFrom); | |
| $copyTo = $this->realEscapeString($copyTo); | |
| $batchID = $this->realEscapeString($batchID); | |
| $semID = $this->realEscapeString($semID); | |
| $sbsID = $this->realEscapeString($sbsID); | |
| $subbatchID = $this->realEscapeString($subbatchID); | |
| $staffID = $this->realEscapeString($staffID); | |
| $query = "call copyattendance_subbatch_insert(\"$date\",\"$copyFrom\", $copyTo,$batchID,\"$semID\",\"$sbsID\",\"$subbatchID\",\"$staffID\")"; | |
| try { | |
| $response = $this->executeQuery($query); | |
| return "copied"; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /*************************************************CopyAttendancePseudoSubjectServices*************************/ | |
| /** | |
| * is attendance marked | |
| * @param $pseudosubjectID | |
| * @param $date | |
| * @param $hour | |
| * @return objectlist | |
| * @throws ProfessionalException | |
| * @author Aswin | |
| */ | |
| public function isAttendanceMarkedDate($pseudosubjectID, $date, $hour) | |
| { | |
| $pseudosubjectID = $this->realEscapeString($pseudosubjectID); | |
| $date = $this->realEscapeString($date); | |
| $hour = $this->realEscapeString($hour); | |
| $query = "SELECT * | |
| FROM | |
| pseudosubjects_sbs pss | |
| INNER JOIN | |
| sbs_relation sr ON pss.sbsID = sr.sbsID | |
| INNER JOIN | |
| attendance_confirm ac ON sr.sbsID = ac.sbsID | |
| AND pss.sbsID = ac.sbsID | |
| AND sr.batchID = ac.batchID | |
| AND sr.semID = ac.semID | |
| WHERE | |
| pss.pseudosubjectID = $pseudosubjectID | |
| AND ac.attendanceDate = '$date' | |
| and ac.hour=$hour;"; | |
| try { | |
| $response = $this->executeQueryForList($query); | |
| $response = CommonUtility::decodeHtmlSpecialChars($response); | |
| return $response; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * is hour assigned | |
| * @param $pseudosubjectID | |
| * @param $date | |
| * @param $hour | |
| * @return object|array|\com\linways\base\util\$objectList[] | |
| * @throws ProfessionalException | |
| * @author Aswin | |
| */ | |
| public function isHourAssigned($pseudosubjectID, $date, $hour) | |
| { | |
| $pseudosubjectID = $this->realEscapeString($pseudosubjectID); | |
| $date = $this->realEscapeString($date); | |
| $hour = $this->realEscapeString($hour); | |
| $query = "SELECT * | |
| FROM | |
| pseudosubjects_sbs pss | |
| INNER JOIN | |
| sbs_relation sr ON pss.sbsID = sr.sbsID | |
| INNER JOIN | |
| batch_timetable btd ON sr.batchID = btd.batchID | |
| AND sr.semID = btd.semID | |
| WHERE | |
| pss.pseudosubjectID = $pseudosubjectID AND btd.timetableDate = '$date' AND btd.hourId = $hour AND (btd.is_allowed=0 OR btd.is_allowed=1)"; | |
| try { | |
| $response = $this->executeQueryForList($query); | |
| $response = CommonUtility::decodeHtmlSpecialChars($response); | |
| return $response; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * assign attendance to hour | |
| * @param $pseudosubjectID | |
| * @param $date | |
| * @param $fromHour | |
| * @param $toHour | |
| * @return string | |
| * @throws ProfessionalException | |
| * @author Aswin | |
| */ | |
| public function assignAttendanceToHour($pseudosubjectID, $date, $fromHour, $toHour, $markedStaff, $staffId) | |
| { | |
| $pseudosubjectID = $this->realEscapeString($pseudosubjectID); | |
| $date = $this->realEscapeString($date); | |
| $fromHour = $this->realEscapeString($fromHour); | |
| $toHour = $this->realEscapeString($toHour); | |
| $markedStaff = $this->realEscapeString($markedStaff); | |
| $staffId = $this->realEscapeString($staffId); | |
| $query = "INSERT INTO attendance (studentID, attendanceDate, hour, day, semID, batchID, isAbsent, staffID, sbsID, dlrId, subbatchId, createdBy, createdDate, updatedBy, updatedDate) | |
| select a.studentID,a.attendanceDate,'$toHour',a.day,a.semID,a.batchID,a.isAbsent,sr.staffID,ss.sbsID,a.dlrId, a.subbatchId, '$staffId', utc_timestamp(), '$staffId', utc_timestamp() from attendance a INNER JOIN subbatches sb ON a.subbatchID=sb.subbatchID INNER JOIN subbatch_sbs ss ON sb.subbatchID=ss.subbatchID INNER JOIN sbs_relation sr ON sr.sbsID=ss.sbsID WHERE sb.psID=$pseudosubjectID AND a.attendanceDate = '$date' and a.hour=$fromHour and sr.staffID=$markedStaff group by a.studentID"; | |
| try { | |
| $response = $this->executeQuery($query); | |
| return "inserted"; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| public function confirmAttendanceMarked($pseudosubjectID, $date, $fromHour, $toHour, $markedStaff, $staffId) | |
| { | |
| $pseudosubjectID = $this->realEscapeString($pseudosubjectID); | |
| $date = $this->realEscapeString($date); | |
| $fromHour = $this->realEscapeString($fromHour); | |
| $toHour = $this->realEscapeString($toHour); | |
| $staffId = $this->realEscapeString($staffId); | |
| $markedStaff = $this->realEscapeString($markedStaff); | |
| $centralized = 1; | |
| if ($markedStaff == $staffId) { | |
| $centralized = 0; | |
| } | |
| $query = "INSERT INTO attendance_confirm (batchID, subbatchID, semID, hour, attendanceDate, sbsID, csID, exam_id, confirmedDate, attendanceMethod, createdBy, createdDate, updatedBy, updatedDate, staffID) SELECT distinct ac.batchID,ac.subbatchID,ac.semID,$toHour,ac.attendanceDate,ss.sbsID ,0,0,'" . date('Y-m-d') . "', '" . ($centralized ? Attendance::PSEUDO_SUBJECT_CENTRALISED_COPY : Attendance::PSEUDO_SUBJECT_COPY) . "', '$staffId', utc_timestamp(), '$staffId', utc_timestamp(), $markedStaff from attendance_confirm ac INNER JOIN subbatches sb ON ac.subbatchID=sb.subbatchID INNER JOIN subbatch_sbs ss ON sb.subbatchID=ss.subbatchID INNER JOIN sbs_relation sr ON sr.sbsID=ss.sbsID WHERE sb.psID=$pseudosubjectID AND ac.attendanceDate = '$date' and ac.hour=$fromHour and sr.staffID=$markedStaff"; | |
| try { | |
| $response = $this->executeQuery($query); | |
| return "inserted"; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * check is holiday for a date | |
| * @param $pseudosubjectID | |
| * @param $date | |
| * @param $hour | |
| * @return Object | |
| * @throws ProfessionalException | |
| * @author Aswin | |
| */ | |
| public function isHoliday($pseudosubjectID, $date, $hour) | |
| { | |
| $pseudosubjectID = $this->realEscapeString($pseudosubjectID); | |
| $date = $this->realEscapeString($date); | |
| $hour = $this->realEscapeString($hour); | |
| $query = "select t1.eventID from lms_calender t1, batches t2 where ((t1.deptID=0 and t1.batchID=0) || (t1.deptID=t2.deptID and t1.batchID=0) || (t1.deptID=t2.deptID and t1.batchID=t2.batchID)) and t2.batchID in (SELECT | |
| btd.batchID | |
| FROM | |
| pseudosubjects_sbs pss | |
| INNER JOIN | |
| sbs_relation sr ON pss.sbsID = sr.sbsID | |
| INNER JOIN | |
| batch_timetable btd ON sr.sbsID = btd.sbsID | |
| AND sr.batchID = btd.batchID | |
| AND sr.semID = btd.semID | |
| WHERE | |
| pss.pseudosubjectID = $pseudosubjectID | |
| AND btd.timetableDate = '$date' | |
| AND btd.hourId = $hour) and flag in (1,3) and \"" . strtotime($date) . "\" between t1.timeStart and t1.timeEnd"; | |
| try { | |
| $response = $this->executeQueryForList($query); | |
| return $response; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * @param CreatePseudoSubjectGroupRequest $request | |
| * @return Object | |
| * @throws ProfessionalException | |
| */ | |
| public function createPseudoSubjectGroup(CreatePseudoSubjectGroupRequest $request) | |
| { | |
| $request = CommonUtility::stripHtmlTags($request); | |
| $request = $this->realEscapeObject($request); | |
| if (empty($request->name)) { | |
| throw new ProfessionalException(ProfessionalException::INVALID_PSEUDO_SUBJECT_GROUP_NAME, "Invalid pseudo subject group name"); | |
| } | |
| $sql = "INSERT INTO pseudo_subject_groups ( name, created_by, created_date) | |
| VALUES ('$request->name',$request->createdBy,UTC_TIMESTAMP())"; | |
| try { | |
| return $this->executeQueryForObject($sql, true); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * @return Object | |
| * @throws ProfessionalException | |
| */ | |
| public function getAllSubjectGroup() | |
| { | |
| $sql = "SELECT * FROM pseudo_subject_groups ORDER BY created_date DESC"; | |
| try { | |
| $response = $this->executeQueryForList($sql); | |
| $response = CommonUtility::decodeHtmlSpecialChars($response); | |
| return $response; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * @param UpdatePseudoGroupRequest $request | |
| * @throws ProfessionalException | |
| */ | |
| public function updatePseudoSubjectGroup(UpdatePseudoGroupRequest $request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| if (empty($request->id)) { | |
| throw new ProfessionalException(ProfessionalException::INVALID_REQUEST, "Invalid request sent"); | |
| } | |
| if (empty($request->name)) { | |
| throw new ProfessionalException(ProfessionalException::INVALID_PSEUDO_SUBJECT_GROUP_NAME, "Group name should not be empty"); | |
| } | |
| $sql = "UPDATE pseudo_subject_groups SET name ='$request->name',updated_by =$request->updatedBy, | |
| updated_date= UTC_TIMESTAMP() WHERE id = $request->id"; | |
| try { | |
| $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * @param $groupId | |
| * @return int | |
| * @throws ProfessionalException | |
| */ | |
| public function getCountOfPseudoSubjectGroupSubjectsByGroupId($groupId) | |
| { | |
| $groupId = $this->realEscapeString($groupId); | |
| $totalAssignedSubjects = 0; | |
| $sql = "SELECT count(ps.pseudosubjectID) as totalAssignedSubjects FROM pseudosubjects ps | |
| INNER JOIN pseudo_subject_groups psg ON ps.pseudo_subject_group_id = psg.id | |
| WHERE psg.id = $groupId"; | |
| try { | |
| $totalAssignedSubjects = $this->executeQueryForObject($sql)->totalAssignedSubjects; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $totalAssignedSubjects; | |
| } | |
| /** | |
| * @param $groupId | |
| * @throws ProfessionalException | |
| */ | |
| public function deletePseudoSubjectGroup($groupId) | |
| { | |
| $groupId = $this->realEscapeString($groupId); | |
| $sql = "DELETE FROM pseudo_subject_groups WHERE id = $groupId"; | |
| try { | |
| $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * @param int $isActive | |
| * @return Object | |
| * @throws ProfessionalException | |
| */ | |
| public function getAllPseudoSubjectType($isActive = 1) | |
| { | |
| $sql = "SELECT * FROM pseudo_subject_type WHERE is_active=$isActive"; | |
| try { | |
| return $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * @param CreatePseudoSubjectRequest $request | |
| * @return Object|null | |
| * @throws ProfessionalException | |
| */ | |
| public function createPseudoSubject(CreatePseudoSubjectRequest $request) | |
| { | |
| $request = CommonUtility::stripHtmlTags($request); | |
| $request = $this->realEscapeObject($request); | |
| $pseudoSubjectId = null; | |
| $isOpenCourse = 0; | |
| if ($request->isOpenCourse) { | |
| $isOpenCourse = 1; | |
| } | |
| try { | |
| if (!$request->isOpenCourse) { | |
| $pseudoSubjectType = $this->getPseudoSubjectTypeById($request->pseudoTypeId); | |
| if (empty($pseudoSubjectType)) { | |
| throw new ProfessionalException(ProfessionalException::PSEUDO_SUBJECT_TYPE_NOT_FOUND, "Pseudo subject type not found in the database"); | |
| } | |
| if ($pseudoSubjectType->code == PseudoSubjectType::OPEN_COURSE) { | |
| $isOpenCourse = 1; | |
| } | |
| } | |
| if (empty($request->pseudoGroupId)) { | |
| $request->pseudoGroupId = "NULL"; | |
| } | |
| if (empty($request->pseudoTypeId)) { | |
| $request->pseudoTypeId = "NULL"; | |
| }else{ | |
| $request->pseudoTypeId = "'".$request->pseudoTypeId."'"; | |
| } | |
| $column = ""; | |
| $columnValues = ""; | |
| if($request->clusterId){ | |
| $column = ",cluster_id,subject_group_ids"; | |
| $columnValues = ",'".$request->clusterId."',".($request->groupId?"'".$request->groupId."'":"null"); | |
| } | |
| $sql = "INSERT INTO pseudosubjects(subjectName, courseTypeID,hdl_deptID,isOpencourse,pseudo_subject_type_id, | |
| pseudo_subject_group_id".$column.") | |
| VALUES ('$request->name',$request->courseTypeId,$request->handlingDepartmentId,$isOpenCourse, | |
| $request->pseudoTypeId,$request->pseudoGroupId".$columnValues.")"; | |
| $pseudoSubjectId = $this->executeQueryForObject($sql, true); | |
| if (!empty($request->subjectId)) { | |
| $addSubjectRequest = new AddSubjectToPseudoSubjectRequest(); | |
| $addSubjectRequest->pseudoSubjectId = $pseudoSubjectId; | |
| $addSubjectRequest->subjectId = $request->subjectId; | |
| $addSubjectRequest->createdBy = $request->createdBy; | |
| $addSubjectRequest->updatedBy = $request->updatedBy; | |
| $this->addSubjectToPseudoSubjects($addSubjectRequest); | |
| } | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $pseudoSubjectId; | |
| } | |
| /** | |
| * @param $id | |
| * @return Object | |
| * @throws ProfessionalException | |
| */ | |
| public function getPseudoSubjectTypeById($id) | |
| { | |
| $id = $this->realEscapeString($id); | |
| $sql = "SELECT * FROM pseudo_subject_type WHERE id= $id"; | |
| try { | |
| return $this->executeQueryForObject($sql); | |
| } catch (Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * @param GetPseudoSubjectRequest $request | |
| * @return GetAllPseudoSubjectResponse | |
| * @throws ProfessionalException | |
| */ | |
| public function getAllPseudoSubjects(GetPseudoSubjectRequest $request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $response = new GetAllPseudoSubjectResponse(); | |
| $limitSql = ""; | |
| $conditionString = ""; | |
| if (!empty($request->getCurrentPseudoSubjectsOnly)) { | |
| $conditionString = " AND (psbs.pseudosubsbsID IS NULL OR sr.semID = b.semID)"; | |
| } elseif (!empty($request->getOldPseudoSubjectsOnly)) { | |
| $conditionString = " AND psbs.pseudosubsbsID IS NOT NULL AND sr.semID != b.semID"; | |
| } | |
| if (!empty($request->pseudoSubjectGroupIds) && count($request->pseudoSubjectGroupIds) > 0) { | |
| $conditionString .= " AND psg.id IN (" . implode(",", $request->pseudoSubjectGroupIds) . ") "; | |
| } | |
| if (!empty($request->pseudoSubjectTypeIds) && count($request->pseudoSubjectTypeIds) > 0) { | |
| $conditionString .= " AND pst.id IN (" . implode(",", $request->pseudoSubjectTypeIds) . ") "; | |
| } | |
| if (!empty($request->batchIds) && count($request->batchIds) > 0 && !empty($request->getOldPseudoSubjectsOnly)) { | |
| $conditionString .= " AND b.batchID IN (" . implode(",", $request->batchIds) . ")"; | |
| } | |
| if (!empty($request->courseTypeIds) && count($request->courseTypeIds) > 0) { | |
| $conditionString .= " AND ct.courseTypeID IN (" . implode(",", $request->courseTypeIds) . ")"; | |
| } | |
| if (!empty($request->handlingDepartmentIds) && count($request->handlingDepartmentIds) > 0) { | |
| $conditionString .= " AND d.deptID IN (" . implode(",", $request->handlingDepartmentIds) . ")"; | |
| } | |
| if (!empty($request->subjectName)) { | |
| $conditionString .= " AND ps.subjectName LIKE '%" . $request->subjectName . "%'"; | |
| } | |
| if ($request->startIndex !== "" && $request->endIndex !== "") { | |
| $limitSql = "INNER JOIN (SELECT DISTINCT ps.pseudosubjectID as id FROM pseudosubjects ps | |
| INNER JOIN course_type ct on ps.courseTypeID = ct.courseTypeID | |
| LEFT JOIN department d ON d.deptID = ps.hdl_deptID | |
| LEFT JOIN pseudo_subject_groups psg ON psg.id = ps.pseudo_subject_group_id | |
| LEFT JOIN pseudo_subject_type pst ON pst.id = ps.pseudo_subject_type_id | |
| LEFT JOIN subject_pseudo_subjects sps ON sps.pseudo_subject_id = ps.pseudosubjectID | |
| LEFT JOIN subjects s ON s.subjectID =sps.subject_id | |
| LEFT JOIN pseudosubjects_sbs psbs on ps.pseudosubjectID = psbs.pseudosubjectID | |
| LEFT JOIN sbs_relation sr on psbs.sbsID = sr.sbsID | |
| LEFT JOIN batches b on sr.batchID = b.batchID | |
| WHERE 1=1 $conditionString ORDER BY ps.subjectCreateDate DESC LIMIT $request->startIndex, $request->endIndex | |
| ) as ls ON ls.id =ps.pseudosubjectID "; | |
| } | |
| $sql = "SELECT ps.pseudosubjectID,ps.subjectName,ps.subjectCreateDate,ps.isOpencourse,ps.isFinalized, | |
| d.deptID,d.deptName,ct.courseTypeID,ct.typeName,pst.id as pseudoSubjectTypeId, | |
| pst.is_active as pseudoSubjectTypeActive,pst.name as pseudoSubjectTypeName, | |
| pst.code as pseudoSubjectTypeCode,psg.id as pseudoSubjectGroupId,psg.name as pseudoSubjectGroupName, | |
| s.subjectID as subject_id,s.subjectName as subject_name,s.syllabusName as syllabus_name, | |
| hd.deptID as subject_department_id, s.subjectDesc AS subjectDescription | |
| FROM pseudosubjects ps | |
| $limitSql | |
| INNER JOIN course_type ct on ps.courseTypeID = ct.courseTypeID | |
| LEFT JOIN department d ON d.deptID = ps.hdl_deptID | |
| LEFT JOIN pseudo_subject_groups psg ON psg.id = ps.pseudo_subject_group_id | |
| LEFT JOIN pseudo_subject_type pst ON pst.id = ps.pseudo_subject_type_id | |
| LEFT JOIN subject_pseudo_subjects sps ON sps.pseudo_subject_id = ps.pseudosubjectID | |
| LEFT JOIN subjects s ON s.subjectID =sps.subject_id | |
| LEFT JOIN department hd ON hd.deptID = s.hdl_deptID | |
| LEFT JOIN pseudosubjects_sbs psbs on ps.pseudosubjectID = psbs.pseudosubjectID | |
| LEFT JOIN sbs_relation sr on psbs.sbsID = sr.sbsID | |
| LEFT JOIN batches b on sr.batchID = b.batchID | |
| WHERE 1=1 $conditionString ORDER BY ps.subjectCreateDate DESC;"; | |
| $totalRecordSql = "SELECT COUNT(DISTINCT ps.pseudosubjectID )as totalRecords FROM pseudosubjects ps | |
| INNER JOIN course_type ct on ps.courseTypeID = ct.courseTypeID | |
| LEFT JOIN department d ON d.deptID = ps.hdl_deptID | |
| LEFT JOIN pseudo_subject_groups psg ON psg.id = ps.pseudo_subject_group_id | |
| LEFT JOIN pseudo_subject_type pst ON pst.id = ps.pseudo_subject_type_id | |
| LEFT JOIN subject_pseudo_subjects sps ON sps.pseudo_subject_id = ps.pseudosubjectID | |
| LEFT JOIN subjects s ON s.subjectID =sps.subject_id | |
| LEFT JOIN pseudosubjects_sbs psbs on ps.pseudosubjectID = psbs.pseudosubjectID | |
| LEFT JOIN sbs_relation sr on psbs.sbsID = sr.sbsID | |
| LEFT JOIN batches b on sr.batchID = b.batchID | |
| WHERE 1=1 $conditionString ORDER BY ps.subjectCreateDate DESC"; | |
| try { | |
| ini_set('memory_limit', '512M'); | |
| $response->totalRecords = $this->executeQueryForObject($totalRecordSql)->totalRecords; | |
| $response->pseudoSubjects = $this->executeQueryForList($sql, $this->mapper[PseudoSubjectServiceMapper::GET_ALL_PSEUDO_SUBJECTS]); | |
| $response = CommonUtility::decodeHtmlSpecialChars($response); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $response; | |
| } | |
| /** | |
| * @param $id | |
| * @throws ProfessionalException | |
| */ | |
| public function deletePseudoSubjectById($id) | |
| { | |
| $id = (int)$this->realEscapeString($id); | |
| if (empty($id)) { | |
| throw new ProfessionalException(ProfessionalException::INVALID_REQUEST, "Invalid request sent"); | |
| } | |
| $subjectSQL = "DELETE FROM subject_pseudo_subjects WHERE pseudo_subject_id =$id"; | |
| $sql = "DELETE FROM pseudosubjects WHERE pseudosubjectID=$id"; | |
| try { | |
| /** | |
| * If sub batch assigned, you cannot delete this pseudo subject | |
| */ | |
| // SubBatchService::getInstance()->updateSubBatchPseudoSubjectId($id); | |
| $this->executeQuery($subjectSQL); | |
| $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * @param $cbsTypeId | |
| * @param $showGroupedSubjects | |
| * @param $departmentId | |
| * @return Object | |
| * @throws ProfessionalException | |
| */ | |
| public function getPseudoSubjectsNotAssigned($cbsTypeId, $showGroupedSubjects, $departmentId) | |
| { | |
| $cbsTypeId = $this->realEscapeString($cbsTypeId); | |
| $showGroupedSubjects = $this->realEscapeString($showGroupedSubjects); | |
| $cbsType = ""; | |
| try { | |
| // if ($cbsTypeId) { | |
| // $cbsTypeSql = "SELECT code FROM pseudo_subject_type WHERE id = $cbsTypeId"; | |
| // $cbsType = $this->executeQueryForObject($cbsTypeSql)->code; | |
| // } | |
| $sql = "SELECT ps.pseudosubjectID as id,ps.subjectName as name,d.deptID as deptId,d.deptName,psg.id as groupId, | |
| psg.name as groupName | |
| FROM pseudosubjects ps | |
| LEFT JOIN pseudo_subject_type pst ON pst.id = ps.pseudo_subject_type_id | |
| LEFT JOIN department d ON ps.hdl_deptID = d.deptID | |
| LEFT JOIN pseudo_subject_groups psg ON psg.id = ps.pseudo_subject_group_id | |
| LEFT JOIN pseudosubjects_sbs psbs on ps.pseudosubjectID = psbs.pseudosubjectID | |
| WHERE psbs.pseudosubjectID IS NULL AND pst.id = $cbsTypeId"; | |
| if ($showGroupedSubjects) { | |
| $sql .= " AND psg.id IS NOT NULL"; | |
| } | |
| // switch ($cbsType) { | |
| // case PseudoSubjectType::OPEN_COURSE: | |
| // $sql .= " AND ps.hdl_deptID != $departmentId"; | |
| // break; | |
| // case PseudoSubjectType::FOUNDATION_COURSE: | |
| // case PseudoSubjectType::DEPARTMENT_ELECTIVE: | |
| // $sql .= " AND ps.hdl_deptID = $departmentId "; | |
| // break; | |
| // } | |
| $response = $this->executeQueryForList($sql); | |
| $response = CommonUtility::decodeHtmlSpecialChars($response); | |
| return $response; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * @param $pseudoSubjectId | |
| * @param $cbsTypeId | |
| * @return bool | |
| * @throws ProfessionalException | |
| */ | |
| public function checkGivenPseudoSubjectIsFromGivenCBSType($pseudoSubjectId, $cbsTypeId) | |
| { | |
| $pseudoSubjectId = $this->realEscapeString($pseudoSubjectId); | |
| $cbsTypeId = $this->realEscapeString($cbsTypeId); | |
| if (empty($pseudoSubjectId) && empty($cbsTypeId)) { | |
| throw new ProfessionalException(ProfessionalException::INVALID_REQUEST, "Invalid request sent"); | |
| } | |
| $sql = "SELECT pseudosubjectID FROM pseudosubjects | |
| WHERE pseudosubjectID =$pseudoSubjectId AND pseudo_subject_type_id=$cbsTypeId"; | |
| try { | |
| $pseudoSubjectId = $this->executeQueryForObject($sql)->pseudosubjectID; | |
| if (empty($pseudoSubjectId)) { | |
| return false; | |
| } else { | |
| return true; | |
| } | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * @param UpdatePseudoSubjectRequest $request | |
| * @throws ProfessionalException | |
| */ | |
| public function updatePseudoSubject(UpdatePseudoSubjectRequest $request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| if (empty($request->pseudoSubjectGroupId)) { | |
| $request->pseudoSubjectGroupId = "NULL"; | |
| } | |
| if (empty($request->id)) { | |
| throw new ProfessionalException(ProfessionalException::INVALID_PSEUDO_SUBJECT_ID, "Invalid request sent"); | |
| } | |
| if (empty($request->handlingDepartmentId)) { | |
| throw new ProfessionalException(ProfessionalException::INVALID_HANDLING_DEPARTMENT_ID, "Invalid handling department"); | |
| } | |
| if (empty($request->pseudoSubjectTypeId)) { | |
| throw new ProfessionalException(ProfessionalException::INVALID_PSEUDO_SUBJECT_TYPE_ID, "Invalid pseudo subject type"); | |
| } | |
| if (empty($request->name)) { | |
| throw new ProfessionalException(ProfessionalException::INVALID_PSEUDO_SUBJECT_NAME, "Invalid pseudo subject name"); | |
| } | |
| $sql = "UPDATE pseudosubjects SET subjectName='$request->name',hdl_deptID=$request->handlingDepartmentId, | |
| pseudo_subject_type_id=$request->pseudoSubjectTypeId,pseudo_subject_group_id=$request->pseudoSubjectGroupId, | |
| courseTypeID = $request->courseTypeId | |
| WHERE pseudosubjectID =$request->id"; | |
| try { | |
| $this->executeQueryForObject($sql, true); | |
| /** | |
| * Fetching previously assigned pseudo subject subject id | |
| */ | |
| $subjectId = $this->getSubjectOfPseudoSubject($request->id); | |
| /** | |
| * If previously assigned subject id is null or different from new one then we need to update | |
| * else we need to add subject to pseudo subject | |
| */ | |
| if (!empty($request->subjectId) && !empty($subjectId) && $subjectId !== $request->subjectId) { | |
| $addSubjectRequest = new AddSubjectToPseudoSubjectRequest(); | |
| $addSubjectRequest->pseudoSubjectId = $request->id; | |
| $addSubjectRequest->createdBy = $request->createdBy; | |
| $addSubjectRequest->updatedBy = $request->updatedBy; | |
| $addSubjectRequest->subjectId = $request->subjectId; | |
| $this->updatePseudoSubjectSubject($addSubjectRequest); | |
| } else if (empty($subjectId) && !empty($request->subjectId)) { | |
| $addSubjectRequest = new AddSubjectToPseudoSubjectRequest(); | |
| $addSubjectRequest->pseudoSubjectId = $request->id; | |
| $addSubjectRequest->subjectId = $request->subjectId; | |
| $addSubjectRequest->createdBy = $request->createdBy; | |
| $addSubjectRequest->updatedBy = $request->updatedBy; | |
| $this->addSubjectToPseudoSubjects($addSubjectRequest); | |
| } | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * @param $pseudoSubjectId | |
| * @return mixed|integer | |
| * @throws ProfessionalException | |
| */ | |
| public function getSubjectOfPseudoSubject($pseudoSubjectId) | |
| { | |
| $sql = "SELECT subject_id as subjectId FROM subject_pseudo_subjects WHERE pseudo_subject_id =$pseudoSubjectId"; | |
| try { | |
| return $this->executeQueryFOrObject($sql)->subjectId; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * @param AddSubjectToPseudoSubjectRequest $request | |
| * @throws ProfessionalException | |
| */ | |
| public function updatePseudoSubjectSubject(AddSubjectToPseudoSubjectRequest $request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| if (empty($request->subjectId)) { | |
| throw new ProfessionalException(ProfessionalException::INVALID_SUBJECT_ID, "Invalid subject mapped to the pseudo subjects"); | |
| } | |
| if (empty($request->pseudoSubjectId)) { | |
| throw new ProfessionalException(ProfessionalException::INVALID_PSEUDO_SUBJECT_ID, "Pseudo subject details not found"); | |
| } | |
| $sql = "UPDATE subject_pseudo_subjects | |
| SET subject_id = $request->subjectId,updated_by=$request->updatedBy,updated_date = UTC_TIMESTAMP() | |
| WHERE pseudo_subject_id = $request->pseudoSubjectId"; | |
| try { | |
| $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * @param AddSubjectToPseudoSubjectRequest $request | |
| * @throws ProfessionalException | |
| */ | |
| public function addSubjectToPseudoSubjects(AddSubjectToPseudoSubjectRequest $request) | |
| { | |
| $request = CommonUtility::stripHtmlTags($request); | |
| $request = $this->realEscapeObject($request); | |
| if (empty($request->subjectId)) { | |
| throw new ProfessionalException(ProfessionalException::INVALID_SUBJECT_ID, "Invalid subject mapped to the pseudo subjects"); | |
| } | |
| if (empty($request->pseudoSubjectId)) { | |
| throw new ProfessionalException(ProfessionalException::INVALID_PSEUDO_SUBJECT_ID, "Pseudo subject details not found"); | |
| } | |
| $sql = "INSERT INTO subject_pseudo_subjects (subject_id, pseudo_subject_id, created_by, created_date, | |
| updated_by, updated_date) | |
| VALUES ($request->subjectId,$request->pseudoSubjectId,$request->createdBy,UTC_TIMESTAMP(), | |
| $request->updatedBy,UTC_TIMESTAMP())"; | |
| try { | |
| $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * @param $pseudoSubjectId | |
| * @return Object | |
| * @throws ProfessionalException | |
| */ | |
| public function getPseudoSubjectById($pseudoSubjectId) | |
| { | |
| $pseudoSubjectId = $this->realEscapeString($pseudoSubjectId); | |
| $sql = "SELECT pseudosubjectID as id,subjectName as name,hdl_deptID as handlingDepartmentId, | |
| pseudo_subject_type_id as pseudoSubjectTypeId,courseTypeID as courseTypeId, | |
| pseudo_subject_group_id as pseudoSubjectGroupId | |
| FROM pseudosubjects WHERE pseudosubjectID = $pseudoSubjectId"; | |
| try { | |
| $response = $this->executeQueryForObject($sql); | |
| $response = CommonUtility::decodeHtmlSpecialChars($response); | |
| return $response; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * @param $pseudoSubjectId | |
| * @return mixed | |
| * @throws ProfessionalException | |
| */ | |
| public function getPseudoSubjectGroupIdByPseudoSubjectId($pseudoSubjectId) | |
| { | |
| $pseudoSubjectId = $this->realEscapeString($pseudoSubjectId); | |
| $sql = "SELECT pseudo_subject_group_id as groupId | |
| FROM pseudosubjects WHERE pseudosubjectID = $pseudoSubjectId"; | |
| try { | |
| $response = $this->executeQueryForObject($sql)->groupId; | |
| $response = CommonUtility::decodeHtmlSpecialChars($response); | |
| return $response; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * @param $pseudoSubjectId | |
| * @param $staffID | |
| * @return bool | |
| * @throws ProfessionalException | |
| */ | |
| public function blockEditPreviousPseudoSubjectDetails($pseudoSubjectId, $staffID) | |
| { | |
| $isEditable = true; | |
| $pseudoSubjectId = $this->realEscapeString($pseudoSubjectId); | |
| $staffID = $this->realEscapeString($staffID); | |
| $sql = "SELECT | |
| sr.sbsID,sr.subjectID,bat.batchID,sr.semID as oldSemester, bat.semID as currentSemester,psbs.pseudosubjectID | |
| FROM sbs_relation sr | |
| INNER JOIN batches bat ON sr.batchID = bat.batchID | |
| INNER JOIN pseudosubjects_sbs psbs ON psbs.sbsID = sr.sbsID | |
| WHERE sr.semId not IN (SELECT semID FROM batches WHERE batchID = bat.batchID) | |
| AND bat.batchHide=0 AND sr.csID = 0 and staffID = '" . $staffID . "' AND psbs.pseudosubjectID = '" . $pseudoSubjectId . "' AND sr.semID <> bat.semID;"; | |
| try { | |
| $pseudoSubjects = $this->executeQueryForObject($sql); | |
| if (!empty($pseudoSubjects)) { | |
| $isEditingEnabled = CommonService::getInstance()->getSettings(SettingsConstants::FACULTY_PREVIOUS_DETAILS_SETTINGS, SettingsConstants::IS_PREVIOUS_DETAILS_EDITABLE); | |
| if (!$isEditingEnabled) { | |
| $isEditable = false; | |
| } | |
| } | |
| return $isEditable; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * It returns all pseudo subjects for a pseudoSubjectId, | |
| * using pseudoSubjectId find subject Id and get all pseudo subjects of that subjectId | |
| * | |
| * @throws ProfessionalException | |
| */ | |
| public function getPseudoSubjectsBysubjectId($pseudoSubjectId) | |
| { | |
| try { | |
| $pseudoSubjectId = $this->realEscapeString($pseudoSubjectId); | |
| $sql = "SELECT | |
| DISTINCT subjectID | |
| from | |
| sbs_relation sr | |
| inner join | |
| pseudosubjects_sbs pss on pss.sbsID=sr.sbsID | |
| where | |
| pss.pseudosubjectID=$pseudoSubjectId"; | |
| $subjectId = $this->executeQueryForObject($sql)->subjectID; | |
| $sql = "SELECT | |
| distinct ps.pseudosubjectID, sr.subjectID, ps.subjectName, sr.semID | |
| FROM | |
| pseudosubjects ps | |
| INNER JOIN | |
| pseudosubjects_sbs pss ON pss.pseudosubjectID = ps.pseudosubjectID | |
| INNER JOIN | |
| sbs_relation sr ON pss.sbsID = sr.sbsID | |
| WHERE | |
| sr.subjectID = $subjectId | |
| AND sr.isPseudosubject = 1"; | |
| $pseudoSubjectList = $this->executeQueryForList($sql); | |
| $pseudoSubjectList = CommonUtility::decodeHtmlSpecialChars($pseudoSubjectList); | |
| return $pseudoSubjectList; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * @param $sourcePseudoSubjectId | |
| * @param $targetPseudoSubjectId | |
| * @return Object|array | |
| * @throws ProfessionalException | |
| */ | |
| public function getPseudoSubjectStudentsForCopyStudents($sourcePseudoSubjectId, $targetPseudoSubjectId) | |
| { | |
| $students = []; | |
| $sourcePseudoSubjectId = (int)$this->realEscapeString($sourcePseudoSubjectId); | |
| $targetPseudoSubjectId = (int)$this->realEscapeString($targetPseudoSubjectId); | |
| if (empty($sourcePseudoSubjectId)) { | |
| throw new ProfessionalException(ProfessionalException::INVALID_PSEUDO_SUBJECT_ID, "Source pseudo subject details not found"); | |
| } | |
| if (empty($targetPseudoSubjectId)) { | |
| throw new ProfessionalException(ProfessionalException::INVALID_PSEUDO_SUBJECT_ID, "Target pseudo subject details not found"); | |
| } | |
| $sql = "SELECT DISTINCT s.studentID AS student_id, s.regNo as register_number,s.studentName AS student_name, | |
| b.batchID as batch_id,b.batchName as batch_name, | |
| IF(temp.pseudoSubStudentId,1,0) as is_already_assigned | |
| FROM pseudosubjects_students ps | |
| INNER JOIN studentaccount s ON ps.studentID = s.studentID | |
| INNER JOIN batches b ON s.batchID = b.batchID AND b.batchHide=0 | |
| LEFT JOIN ( | |
| SELECT s.studentID,ps.pseudosubstudentID as pseudoSubStudentId | |
| FROM pseudosubjects_students ps | |
| INNER JOIN studentaccount s ON ps.studentID = s.studentID | |
| WHERE ps.pseudosubjectID =$targetPseudoSubjectId | |
| ) as temp ON temp.studentID = s.studentID | |
| WHERE ps.pseudosubjectID =$sourcePseudoSubjectId"; | |
| try { | |
| $students = $this->executeQueryForList($sql, $this->mapper[PseudoSubjectServiceMapper::GET_PSEUDO_SUBJECT_STUDENTS]); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $students; | |
| } | |
| /** | |
| * @param $pseudoSubjectId | |
| * @return array|Object | |
| * @throws ProfessionalException | |
| */ | |
| public function getPseudoSubjectStaffs($pseudoSubjectId) | |
| { | |
| $staffs = []; | |
| $sql = "SELECT DISTINCT sa.staffID as id,sa.staffName as name,sa.staffCode as code,d.deptID AS id, | |
| d.deptName as departmentName | |
| FROM pseudosubjects_sbs ps | |
| INNER JOIN sbs_relation sr on ps.sbsID = sr.sbsID | |
| INNER JOIN staffaccounts sa ON sa.staffID = sr.staffID | |
| INNER JOIN department d on sa.deptID = d.deptID | |
| WHERE ps.pseudosubjectID = $pseudoSubjectId"; | |
| try { | |
| $staffs = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $staffs; | |
| } | |
| /** | |
| * @param $pseudoSubjectId | |
| * @return Object|PseudoSubject | |
| * @throws ProfessionalException | |
| */ | |
| public function getAssignedStaffAndStudentDetails($pseudoSubjectId) | |
| { | |
| $pseudoSubjectId = $this->realEscapeString($pseudoSubjectId); | |
| if (empty($pseudoSubjectId)) { | |
| throw new ProfessionalException(ProfessionalException::INVALID_PSEUDO_SUBJECT_ID, "Pseudo subject not found"); | |
| } | |
| $sql = "SELECT sr.sbsID as sbs_id, | |
| ps.pseudosubjectID as pseudo_subject_id,ps.isFinalized as is_finalised, | |
| ps.subjectName as pseudo_subject_name, | |
| s.subjectID as subject_id,s.subjectName as subject_name,s.subjectDesc as subject_description, | |
| sa.studentID as student_id,sa.studentName as student_name,sa.regNo as student_register_number, | |
| sa.batchID as student_batch_id,sa.batchName as student_batch_name,sa.rollNo as roll_number, | |
| st.staffID as staff_id,st.staffName as staff_name,st.staffCode as staff_code, | |
| d.deptID as staff_department_id,d.deptName staff_department_name, | |
| hd.deptID as handling_department_id,hd.deptName as handling_department_name, | |
| b.batchID as staff_batch_id,b.batchName as staff_batch_name, | |
| ss.semID as staff_semester_id,ss.semName as staff_semester_name,1 as is_assigned,s.syllabusYear as subject_syllabusYear | |
| FROM pseudosubjects ps | |
| INNER JOIN department hd ON hd.deptID = ps.hdl_deptID | |
| LEFT JOIN subject_pseudo_subjects sps ON sps.pseudo_subject_id = ps.pseudosubjectID | |
| LEFT JOIN subjects s ON s.subjectID = sps.subject_id | |
| LEFT JOIN pseudosubjects_students p on ps.pseudosubjectID = p.pseudosubjectID | |
| LEFT JOIN pseudosubjects_sbs pss on ps.pseudosubjectID = pss.pseudosubjectID | |
| LEFT JOIN sbs_relation sr on pss.sbsID = sr.sbsID | |
| LEFT JOIN staffaccounts st ON st.staffID = sr.staffID | |
| LEFT JOIN batches b ON b.batchID =sr.batchID | |
| LEFT JOIN semesters ss ON ss.semID = sr.semID | |
| LEFT JOIN department d on st.deptID = d.deptID | |
| LEFT JOIN ( | |
| SELECT sa.studentID,sa.studentName,sa.regNo,b.batchID,b.batchName,rollNo | |
| FROM studentaccount sa | |
| INNER JOIN pseudosubjects_students ps ON sa.studentID = ps.studentID | |
| INNER JOIN batches b ON b.batchID = sa.batchID AND b.batchHide=0 | |
| WHERE ps.pseudosubjectID = $pseudoSubjectId | |
| ) sa ON sa.studentID = p.studentID | |
| WHERE ps.pseudosubjectID = $pseudoSubjectId ORDER BY sa.regNo,st.staffName"; | |
| try { | |
| ini_set('max_execution_time', '1800'); | |
| // after script execution, time resetted to default value | |
| ini_set('memory_limit', '1024M'); | |
| $response = $this->executeQueryForObject($sql, FALSE, $this->mapper[PseudoSubjectServiceMapper::GET_ALL_PSEUDO_SUBJECT_STUDENTS_STAFFS]); | |
| $response = CommonUtility::decodeHtmlSpecialChars($response); | |
| return $response; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * @param $pseudoSubjectId | |
| * @return Object|PseudoSubject | |
| * @throws ProfessionalException | |
| * @author jithinvijayan | |
| */ | |
| public function getAssignedPseudoSubjectStaffDetails($pseudoSubjectId) | |
| { | |
| $pseudoSubjectId = $this->realEscapeString($pseudoSubjectId); | |
| if (empty($pseudoSubjectId)) { | |
| throw new ProfessionalException(ProfessionalException::INVALID_PSEUDO_SUBJECT_ID, "Pseudo subject not found"); | |
| } | |
| $sql = "SELECT sr.sbsID as sbs_id, | |
| ps.pseudosubjectID as pseudo_subject_id,ps.isFinalized as is_finalised, | |
| ps.subjectName as pseudo_subject_name, | |
| s.subjectID as subject_id,s.subjectName as subject_name, | |
| st.staffID as staff_id,st.staffName as staff_name,st.staffCode as staff_code, | |
| d.deptID as staff_department_id,d.deptName staff_department_name, | |
| hd.deptID as handling_department_id,hd.deptName as handling_department_name, | |
| b.batchID as staff_batch_id,b.batchName as staff_batch_name, | |
| ss.semID as staff_semester_id,ss.semName as staff_semester_name,1 as is_assigned | |
| FROM pseudosubjects ps | |
| INNER JOIN department hd ON hd.deptID = ps.hdl_deptID | |
| LEFT JOIN subject_pseudo_subjects sps ON sps.pseudo_subject_id = ps.pseudosubjectID | |
| LEFT JOIN subjects s ON s.subjectID = sps.subject_id | |
| LEFT JOIN pseudosubjects_students p on ps.pseudosubjectID = p.pseudosubjectID | |
| LEFT JOIN pseudosubjects_sbs pss on ps.pseudosubjectID = pss.pseudosubjectID | |
| LEFT JOIN sbs_relation sr on pss.sbsID = sr.sbsID | |
| LEFT JOIN staffaccounts st ON st.staffID = sr.staffID | |
| LEFT JOIN batches b ON b.batchID =sr.batchID | |
| LEFT JOIN semesters ss ON ss.semID = sr.semID | |
| LEFT JOIN department d on st.deptID = d.deptID | |
| WHERE ps.pseudosubjectID = $pseudoSubjectId"; | |
| try { | |
| ini_set('memory_limit', '512M'); | |
| $response = $this->executeQueryForObject($sql, FALSE, $this->mapper[PseudoSubjectServiceMapper::GET_ALL_PSEUDO_SUBJECT_AND_STAFFS]); | |
| $response = CommonUtility::decodeHtmlSpecialChars($response); | |
| return $response; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * Assigning subject to the pseudo subject | |
| * | |
| * @param AssignSubjectToPseudoSubjectRequest $request | |
| * @throws ProfessionalException | |
| */ | |
| public function assignSubjectToPseudoSubject(AssignSubjectToPseudoSubjectRequest $request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| if (empty($request->pseudoSubjectId)) { | |
| throw new ProfessionalException(ProfessionalException::INVALID_PSEUDO_SUBJECT_ID, "Select a valid pseudo subject for this operation"); | |
| } | |
| if (empty($request->subjectId)) { | |
| throw new ProfessionalException(ProfessionalException::INVALID_SUBJECT_ID, "Select a valid subject fot this process"); | |
| } | |
| $sql = "INSERT INTO subject_pseudo_subjects (subject_id, pseudo_subject_id, created_by, created_date, | |
| updated_by, updated_date) | |
| VALUES ($request->subjectId,$request->pseudoSubjectId,$request->createdBy,UTC_TIMESTAMP(), | |
| $request->updatedBy,UTC_TIMESTAMP())"; | |
| try { | |
| $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * @param $pseudoSubjectId | |
| * @return Object | |
| * @throws ProfessionalException | |
| */ | |
| public function checkSubjectAlreadyAssignedToPseudoSubject($pseudoSubjectId) | |
| { | |
| $pseudoSubjectId = $this->realEscapeString($pseudoSubjectId); | |
| if (empty($pseudoSubjectId)) { | |
| throw new ProfessionalException(ProfessionalException::INVALID_PSEUDO_SUBJECT_ID, "Invalid pseudo subject details given"); | |
| } | |
| $sql = "SELECT COUNT(id) as totalAssignedSubjects | |
| FROM subject_pseudo_subjects | |
| WHERE pseudo_subject_id =$pseudoSubjectId"; | |
| try { | |
| return $this->executeQueryForObject($sql)->totalAssignedSubjects; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * @param AssignStudentToPseudoSubjectRequest $request | |
| * @throws ProfessionalException | |
| * @author: jithinvijayan | |
| */ | |
| public function assignStudentToPseudoSubject(AssignStudentToPseudoSubjectRequest $request) | |
| { | |
| try { | |
| if (empty($request->studentId)) { | |
| throw new ProfessionalException(ProfessionalException::INVALID_STUDENT_ID, "Student details not found"); | |
| } | |
| if (empty($request->pseudoSubjectId)) { | |
| throw new ProfessionalException(ProfessionalException::INVALID_PSEUDO_SUBJECT_ID, "Pseudo subject not found"); | |
| } | |
| $sql = "INSERT INTO pseudosubjects_students (pseudosubjectID, studentID, created_by, created_date, | |
| updated_by, updated_date) | |
| VALUES ($request->pseudoSubjectId,$request->studentId,$request->createdBy,UTC_TIMESTAMP(), | |
| $request->updatedBy,UTC_TIMESTAMP())"; | |
| $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * Remove pseudo student from the pseudo subject | |
| * | |
| * @param UnAssignStudentFromSubjectRequest $request | |
| * @throws ProfessionalException | |
| * @author: jithinvijayan | |
| */ | |
| public function unAssignStudentFromThePseudoSubject(UnAssignStudentFromSubjectRequest $request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| if (empty($request->pseudoSubjectId)) { | |
| throw new ProfessionalException(ProfessionalException::INVALID_PSEUDO_SUBJECT_ID, "Pseudo subject not found"); | |
| } | |
| if (empty($request->studentId)) { | |
| throw new ProfessionalException(ProfessionalException::INVALID_STUDENT_ID, "Student details not found"); | |
| } | |
| $sql = "DELETE FROM pseudosubjects_students | |
| WHERE studentID = $request->studentId AND pseudosubjectID= $request->pseudoSubjectId"; | |
| try { | |
| $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * Returns student details by batch ids | |
| * @param $batchIds | |
| * @param $pseudoSubjectId | |
| * @return Object|array | |
| * @throws ProfessionalException | |
| */ | |
| public function getAllUnAssignedStudentDetailsByBatchIds($batchIds, $pseudoSubjectId,$includeAdmissionBatchStudents=false) | |
| { | |
| $batchIds = $this->realEscapeArray($batchIds); | |
| $pseudoSubjectId = $this->realEscapeString($pseudoSubjectId); | |
| $condition = ""; | |
| if (empty($pseudoSubjectId)) { | |
| throw new ProfessionalException(ProfessionalException::INVALID_PSEUDO_SUBJECT_ID, "Invalid pseudo subject details given"); | |
| } | |
| if (count($batchIds) === 0) { | |
| throw new ProfessionalException(ProfessionalException::INVALID_BATCH_IDS, "Invalid batch details given"); | |
| } | |
| if(!$includeAdmissionBatchStudents){ | |
| $condition = " AND b.is_admission=0"; | |
| } | |
| $sql = "SELECT sa.studentID AS id, sa.studentName AS name, sa.regNo AS registerNumber,b.batchID AS batchId, | |
| b.batchName AS batchName,sa.rollNo as rollNumber | |
| FROM studentaccount sa | |
| INNER JOIN batches b ON b.batchID =sa.batchID | |
| LEFT JOIN pseudosubjects_students ps ON sa.studentID = ps.studentID AND ps.pseudosubjectID=$pseudoSubjectId | |
| WHERE b.batchID IN (" . implode(",", $batchIds) . ") AND ps.pseudosubstudentID IS NULL | |
| AND b.batchHide=0 $condition | |
| ORDER BY case when rollNo REGEXP '^-?[0-9]+$' =0 then rollNo end, case when rollNo REGEXP '^-?[0-9]+$' =1 then CAST(rollNo as decimal) end,sa.regNo,b.batchName"; | |
| try { | |
| return $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * @param $batchIds | |
| * @param $pseudoSubjectId | |
| * @return Object | |
| * @throws ProfessionalException | |
| * @author jithinvijayan | |
| */ | |
| public function getAllUnAssignedSecondLanguageStudentByBatchIds($batchIds, $pseudoSubjectId,$includeAdmissionBatchStudents=false) | |
| { | |
| $batchIds = $this->realEscapeArray($batchIds); | |
| $pseudoSubjectId = $this->realEscapeString($pseudoSubjectId); | |
| if (count($batchIds) === 0) { | |
| throw new ProfessionalException(ProfessionalException::INVALID_BATCH_IDS, "Invalid batch details given"); | |
| } | |
| if (empty($pseudoSubjectId)) { | |
| throw new ProfessionalException(ProfessionalException::INVALID_PSEUDO_SUBJECT_ID, "Invalid pseudo subject details given"); | |
| } | |
| if(!$includeAdmissionBatchStudents){ | |
| $condition = " AND b.is_admission=0 "; | |
| } | |
| $sql = "SELECT sa.studentID AS id, sa.studentName AS name, sa.regNo AS registerNumber,b.batchID AS batchId, | |
| b.batchName AS batchName,sa.rollNo as rollNumber | |
| FROM studentaccount sa | |
| INNER JOIN batches b ON b.batchID =sa.batchID | |
| INNER JOIN subjects s ON s.secondLangaugeId = sa.secondlangaugeID | |
| INNER JOIN subject_pseudo_subjects sps ON sps.subject_id= s.subjectID AND sps.pseudo_subject_id = $pseudoSubjectId | |
| INNER JOIN pseudosubjects ps ON ps.pseudosubjectID = sps.pseudo_subject_id | |
| LEFT JOIN pseudosubjects_students pss ON sa.studentID = pss.studentID AND ps.pseudosubjectID=pss.pseudosubjectID | |
| WHERE b.batchID IN (" . implode(",", $batchIds) . ") AND pss.pseudosubstudentID IS NULL | |
| AND b.batchHide=0 $condition AND ps.pseudosubjectID = $pseudoSubjectId | |
| ORDER BY case when rollNo REGEXP '^-?[0-9]+$' =0 then rollNo end, case when rollNo REGEXP '^-?[0-9]+$' =1 then CAST(rollNo as decimal) end,sa.regNo,b.batchName"; | |
| try { | |
| $response = $this->executeQueryForList($sql); | |
| $response = CommonUtility::decodeHtmlSpecialChars($response); | |
| return $response; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * Checking given pseudo subject is second language | |
| * | |
| * @param $pseudoSubjectId | |
| * @return mixed | |
| * @throws ProfessionalException | |
| * @author jithinvijayan | |
| */ | |
| public function checkPseudoSubjectIsSecondLanguage($pseudoSubjectId) | |
| { | |
| $pseudoSubjectId = $this->realEscapeString($pseudoSubjectId); | |
| if (empty($pseudoSubjectId)) { | |
| throw new ProfessionalException(ProfessionalException::INVALID_PSEUDO_SUBJECT_ID, "Invalid pseudo subject details given"); | |
| } | |
| $sql = "SELECT s.secondLangaugeId as seondLanguageId FROM pseudosubjects ps | |
| INNER JOIN subject_pseudo_subjects sps on ps.pseudosubjectID = sps.pseudo_subject_id | |
| INNER JOIN subjects s on sps.subject_id = s.subjectID | |
| WHERE ps.pseudosubjectID =$pseudoSubjectId"; | |
| try { | |
| return $this->executeQueryForObject($sql)->seondLanguageId; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * Checking student already assigned to this subject | |
| * | |
| * @param $studentId | |
| * @param $pseudoSubjectId | |
| * @return mixed|integer | |
| * @throws ProfessionalException | |
| * @author: jithinvijayan | |
| */ | |
| public function checkStudentAlreadyAssigned($pseudoSubjectId, $studentId) | |
| { | |
| $studentId = $this->realEscapeString($studentId); | |
| $pseudoSubjectId = $this->realEscapeString($pseudoSubjectId); | |
| if (empty($studentId)) { | |
| throw new ProfessionalException(ProfessionalException::INVALID_STUDENT_ID, "Student not found or invalid student details given."); | |
| } | |
| if (empty($pseudoSubjectId)) { | |
| throw new ProfessionalException(ProfessionalException::INVALID_PSEUDO_SUBJECT_ID, "Invalid pseudo subject details given"); | |
| } | |
| $sql = "SELECT pseudosubstudentID AS id | |
| FROM pseudosubjects_students | |
| WHERE studentID=$studentId AND pseudosubjectID=$pseudoSubjectId"; | |
| try { | |
| return $this->executeQueryForObject($sql)->id; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * @param $pseudoSubjectId | |
| * @throws ProfessionalException | |
| * @author: jithinvijayan | |
| */ | |
| private function validatePseudoSubjectProperlyAssigned($pseudoSubjectId) | |
| { | |
| $pseudoSubjectId = $this->realEscapeString($pseudoSubjectId); | |
| if (empty($pseudoSubjectId)) { | |
| throw new ProfessionalException(ProfessionalException::INVALID_PSEUDO_SUBJECT_ID, "Invalid pseudo subject details given"); | |
| } | |
| try { | |
| $studentBatches = $this->getPseudoSubjectStudentBatches($pseudoSubjectId,true); | |
| $staffBatches = SBSService::getInstance()->getPseudoSubjectStaffBatches($pseudoSubjectId); | |
| foreach ($staffBatches as $staffBatch) { | |
| $isFound = false; | |
| foreach ($studentBatches as $studentBatch) { | |
| $studentBatch->batchId = (int)$studentBatch->batchId; | |
| $staffBatch->batchId = (int)$staffBatch->batchId; | |
| if ($studentBatch->batchId === $staffBatch->batchId) { | |
| $isFound = true; | |
| break; | |
| } | |
| } | |
| if (!$isFound) { | |
| throw new ProfessionalException(ProfessionalException::NO_STUDENTS_ASSIGNED, "You are not assigned students from the batch `$staffBatch->batchName`. But you assigned staff from the same batch."); | |
| } | |
| } | |
| if (count($studentBatches) === 1 && count($staffBatches) === 1) { | |
| return; | |
| } | |
| foreach ($studentBatches as $studentBatch) { | |
| $isFound = false; | |
| foreach ($staffBatches as $staffBatch) { | |
| $studentBatch->batchId = (int)$studentBatch->batchId; | |
| $staffBatch->batchId = (int)$staffBatch->batchId; | |
| if ($studentBatch->batchId === $staffBatch->batchId) { | |
| $isFound = true; | |
| break; | |
| } | |
| } | |
| if (!$isFound) { | |
| throw new ProfessionalException(ProfessionalException::NO_STAFFS_ASSIGNED, "You are not assigned staffs from the batch `$studentBatch->batchName`. But you assigned students from the same batch."); | |
| } | |
| } | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * Finalising pseudo subject | |
| * | |
| * @param FinalisePseudoSubjectRequest $request | |
| * @throws ProfessionalException | |
| * @author jithinvijayan | |
| */ | |
| public function finalisePseudoSubject(FinalisePseudoSubjectRequest $request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| if (empty($request->pseudoSubjectId)) { | |
| throw new ProfessionalException(ProfessionalException::INVALID_PSEUDO_SUBJECT_ID, "Invalid pseudo subject details given"); | |
| } | |
| try { | |
| /** | |
| * STEP 1: | |
| * Validating staff and students properly assigned to the pseudo subject | |
| */ | |
| $this->validatePseudoSubjectProperlyAssigned($request->pseudoSubjectId); | |
| /** | |
| * STEP 1.1 | |
| * Checking pseudo subject already finalised | |
| */ | |
| if ($this->isPseudoSubjectIsFinalised($request->pseudoSubjectId)) { | |
| throw new ProfessionalException(ProfessionalException::PSEUDO_SUBJECT_ALREADY_FINALISED, "This pseudo subject already finalised"); | |
| } | |
| /** | |
| * STEP 2: | |
| * | |
| * Fetching pseudo subject details and assigned staffs and their batches | |
| */ | |
| $pseudoSubject = $this->getAssignedPseudoSubjectStaffDetails($request->pseudoSubjectId); | |
| $assignedBatches = []; | |
| /** | |
| * STEP 3: | |
| * Iterating pseudo subject staffs and creating sub-batch for staff batches | |
| */ | |
| foreach ($pseudoSubject->assignedStaffs as $staff) { | |
| $subBatchId = null; | |
| /** | |
| * STEP 3.1: | |
| * Checking sub-batch already created before | |
| */ | |
| $getSubBatchRequest = new GetSubBatchRequest(); | |
| $getSubBatchRequest->pseudoSubjectId = $request->pseudoSubjectId; | |
| $getSubBatchRequest->batchId = $staff->batchId; | |
| $getSubBatchRequest->semesterId = $staff->semesterId; | |
| $subBatch = SubBatchService::getInstance()->getSubBatch($getSubBatchRequest); | |
| $subBatchId = $subBatch->id; | |
| /** | |
| * STEP 3.2 | |
| * Checking sub-batch already created for this batch. | |
| * If already created then we need to assign sbs only | |
| */ | |
| if (!in_array($staff->batchId, $assignedBatches)) { | |
| /** | |
| * STEP 3.2.1: | |
| * Creating sub-batch if not created before | |
| */ | |
| if (empty($subBatch)) { | |
| $createRequest = new CreateSubBatchRequest(); | |
| $createRequest->semesterId = $staff->semesterId; | |
| $createRequest->batchId = $staff->batchId; | |
| $createRequest->pseudoSubjectId = $request->pseudoSubjectId; | |
| $createRequest->name = $staff->batchName . "-" . $pseudoSubject->subjectName; | |
| $createRequest->description = $staff->batchName . "-" . $pseudoSubject->subjectName; | |
| $createRequest->createdBy = $request->createdBy; | |
| $createRequest->updatedBy = $request->updatedBy; | |
| $subBatchId = SubBatchService::getInstance()->createSubBatch($createRequest); | |
| } | |
| /** | |
| * STEP 3.2.2 | |
| * Checking SBS assigned for sub-batch. | |
| * If not assigned, we need to assign sbs to the particular sub-batch | |
| */ | |
| $isSBSAssigned = SBSService::getInstance()->checkSBSAssignedForSubBatch($subBatchId, $staff->sbsId); | |
| if (empty($isSBSAssigned)) { | |
| $createSBSRequest = new CreateSubBatchSBSRequest(); | |
| $createSBSRequest->sbsId = $staff->sbsId; | |
| $createSBSRequest->subBatchId = $subBatchId; | |
| $createSBSRequest->updatedBy = $request->updatedBy; | |
| $createSBSRequest->createdBy = $request->createdBy; | |
| SBSService::getInstance()->createSubBatchSBS($createSBSRequest); | |
| } | |
| /** | |
| * STEP 3.2.3 | |
| * Assigning pseudo subject students to the sub-batches | |
| */ | |
| $assignStudentRequest = new AssignPseudoSubjectStudentsToSubBatchRequest(); | |
| $assignStudentRequest->pseudoSubjectId = $request->pseudoSubjectId; | |
| $assignStudentRequest->subBatchId = $subBatchId; | |
| $assignStudentRequest->createdBy = $request->createdBy; | |
| $assignStudentRequest->updatedBy = $request->updatedBy; | |
| $this->assignPseudoSubjectStudentsToSubBatch($assignStudentRequest); | |
| /** | |
| * STEP 3.2.4 | |
| * Remove un-assigned pseudo subject from sub-batches | |
| */ | |
| $this->removeUnAssignedPseudoSubjectStudentsFromSubBatch($request->pseudoSubjectId, $subBatchId); | |
| $assignedBatches[] = $staff->batchId; | |
| } else { | |
| /** | |
| * STEP 4 | |
| * Checking SBS already assigned for this sub batch. | |
| * If not, assigning sbs to the sub-batch | |
| */ | |
| $isSBSAssigned = SBSService::getInstance()->checkSBSAssignedForSubBatch($subBatchId, $staff->sbsId); | |
| if (empty($isSBSAssigned)) { | |
| $createSBSRequest = new CreateSubBatchSBSRequest(); | |
| $createSBSRequest->sbsId = $staff->sbsId; | |
| $createSBSRequest->subBatchId = $subBatchId; | |
| $createSBSRequest->updatedBy = $request->updatedBy; | |
| $createSBSRequest->createdBy = $request->createdBy; | |
| SBSService::getInstance()->createSubBatchSBS($createSBSRequest); | |
| } | |
| } | |
| } | |
| /** | |
| * Delete unwanted subbatches | |
| */ | |
| $this->deletePseudoSubjectUnAssignedSubbatchByPsId($request->pseudoSubjectId); | |
| /** | |
| * STEP 5 | |
| * Finalising pseudo subject | |
| */ | |
| $finaliseRequest = new MarkOrUnmarkIsFinalisedPseudoSubjectRequest(); | |
| $finaliseRequest->pseudoSubjectId = $request->pseudoSubjectId; | |
| $finaliseRequest->updatedBy = $request->updatedBy; | |
| $finaliseRequest->isFinalised = 1; | |
| $this->markOrUnmarkPseudoSubjectFinalized($finaliseRequest); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * Assigning pseudo subject students to the sub-batch by pseudo subject id and sub-batch id | |
| * | |
| * @param AssignPseudoSubjectStudentsToSubBatchRequest $request | |
| * @throws ProfessionalException | |
| * @author jithinvijayan | |
| */ | |
| private function assignPseudoSubjectStudentsToSubBatch(AssignPseudoSubjectStudentsToSubBatchRequest $request) | |
| { | |
| $sql = "INSERT IGNORE INTO subbatch_student (subbatchID, studentID, created_by, created_date, updated_by, updated_date) | |
| SELECT DISTINCT $request->subBatchId,ps.studentID,$request->createdBy,UTC_TIMESTAMP(),$request->updatedBy,UTC_TIMESTAMP() | |
| FROM pseudosubjects_students ps | |
| INNER JOIN studentaccount sa ON sa.studentID = ps.studentID | |
| INNER JOIN batches b ON b.batchID =sa.batchID AND b.batchHide=0 | |
| INNER JOIN subbatches sb ON sb.batchID = b.batchID AND sb.psID = ps.pseudosubjectID | |
| WHERE pseudosubjectID = $request->pseudoSubjectId AND sb.subbatchID = $request->subBatchId"; | |
| try { | |
| $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * Removing un-assigned pseudo subject students from the sub-batches | |
| * | |
| * @param $pseudoSubjectId | |
| * @param $subbatchId | |
| * @throws ProfessionalException | |
| * @author jithinvijayan | |
| */ | |
| private function removeUnAssignedPseudoSubjectStudentsFromSubBatch($pseudoSubjectId, $subbatchId) | |
| { | |
| if (empty($pseudoSubjectId)) { | |
| throw new ProfessionalException(ProfessionalException::INVALID_PSEUDO_SUBJECT_ID, "Invalid pseudo subject details given"); | |
| } | |
| $sql = "DELETE ss.* FROM subbatch_student ss | |
| INNER JOIN subbatches sb ON sb.subbatchID = ss.subbatchID | |
| LEFT JOIN pseudosubjects_students pss ON pss.pseudosubjectID = sb.psID AND ss.studentID = pss.studentID | |
| WHERE sb.psID = $pseudoSubjectId AND sb.subbatchID=$subbatchId AND pss.pseudosubstudentID IS NULL"; | |
| try { | |
| $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * Returns distinct student batches from pseudo subject students | |
| * | |
| * @param $pseudoSubjectId | |
| * @return Object|Object[] | |
| * @throws ProfessionalException | |
| * @author: jithinvijayan | |
| */ | |
| private function getPseudoSubjectStudentBatches($pseudoSubjectId,$includeAdmissionBatch=false) | |
| { | |
| $condition = ""; | |
| if(!$includeAdmissionBatch){ | |
| $condition = " AND b.is_admission=0 "; | |
| } | |
| $sql = "SELECT DISTINCT b.batchID as batchId,b.batchName FROM pseudosubjects ps | |
| INNER JOIN pseudosubjects_students pss on ps.pseudosubjectID = pss.pseudosubjectID | |
| INNER JOIN studentaccount s on pss.studentID = s.studentID | |
| INNER JOIN batches b on s.batchID = b.batchID AND b.batchHide=0 $condition | |
| WHERE ps.pseudosubjectID =$pseudoSubjectId"; | |
| try { | |
| return $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * set/reset finalise pseudo subject flag | |
| * | |
| * @param MarkOrUnmarkIsFinalisedPseudoSubjectRequest $request | |
| * @throws ProfessionalException | |
| * @author Jithin Vijayan | |
| */ | |
| public function markOrUnmarkPseudoSubjectFinalized(MarkOrUnmarkIsFinalisedPseudoSubjectRequest $request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| if (empty($request->pseudoSubjectId)) { | |
| throw new ProfessionalException(ProfessionalException::INVALID_PSEUDO_SUBJECT_ID, "Invalid pseudo subject details given"); | |
| } | |
| $sql = "UPDATE pseudosubjects | |
| SET isFinalized = $request->isFinalised,updated_by = $request->updatedBy,updated_date=UTC_TIMESTAMP() | |
| WHERE pseudosubjectID = $request->pseudoSubjectId"; | |
| try { | |
| $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * @param int $pseudoSubjectId | |
| * @return int | |
| * @throws ProfessionalException | |
| * @author jithinvijayan | |
| */ | |
| private function isPseudoSubjectIsFinalised($pseudoSubjectId) | |
| { | |
| $sql = "SELECT isFinalized FROM pseudosubjects WHERE pseudosubjectID =$pseudoSubjectId"; | |
| try { | |
| return (int)$this->executeQueryForObject($sql)->isFinalized; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * @param $pseudoSubjectId | |
| * @return bool | |
| * @throws ProfessionalException | |
| * @author jithinvijayan | |
| */ | |
| public function isDeletablePseudoSubject($pseudoSubjectId) | |
| { | |
| $isDeletable = false; | |
| $pseudoSubjectId = $this->realEscapeString($pseudoSubjectId); | |
| if (empty($pseudoSubjectId)) { | |
| throw new ProfessionalException(ProfessionalException::INVALID_PSEUDO_SUBJECT_ID, "Invalid pseudo subject details given"); | |
| } | |
| $sql = "SELECT COUNT(DISTINCT p.pseudosubsbsID) as totalStaffs,COUNT(DISTINCT s.pseudosubstudentID) as totalStudents | |
| FROM pseudosubjects ps | |
| LEFT JOIN pseudosubjects_sbs p on ps.pseudosubjectID = p.pseudosubjectID | |
| LEFT JOIN pseudosubjects_students s on ps.pseudosubjectID = s.pseudosubjectID | |
| WHERE ps.pseudosubjectID = $pseudoSubjectId GROUP BY ps.pseudosubjectID"; | |
| try { | |
| $counts = $this->executeQueryForObject($sql); | |
| if (empty($counts->totalStaffs) && empty($counts->totalStudents)) { | |
| $isDeletable = true; | |
| } else if (!$this->isSubBatchAssignedToPseudoSubject($pseudoSubjectId)) { | |
| $isDeletable = true; | |
| } | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $isDeletable; | |
| } | |
| /** | |
| * @param $pseudoSubjectId | |
| * @return int | |
| * @throws ProfessionalException | |
| * @author jithinvijayan | |
| */ | |
| public function isSubBatchAssignedToPseudoSubject($pseudoSubjectId) | |
| { | |
| $pseudoSubjectId = $this->realEscapeString($pseudoSubjectId); | |
| if (empty($pseudoSubjectId)) { | |
| throw new ProfessionalException(ProfessionalException::INVALID_PSEUDO_SUBJECT_ID, "Invalid pseudo subject details given"); | |
| } | |
| $sql = "SELECT COUNT(DISTINCT sb.subbatchID) as totalBatches FROM pseudosubjects ps | |
| LEFT JOIN subbatches sb ON sb.psID= ps.pseudosubjectID | |
| WHERE ps.pseudosubjectID = $pseudoSubjectId"; | |
| try { | |
| return (int)$this->executeQueryForObject($sql)->totalBatches; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * @param $departmentId | |
| * @param array $excludedPseudoSubjectIds | |
| * @return Object|array | |
| * @throws ProfessionalException | |
| * @author jithinvijayan | |
| */ | |
| public function getPseudoSubjectByHandlingDepartment($departmentId, $excludedPseudoSubjectIds = []) | |
| { | |
| if (empty($departmentId)) { | |
| throw new ProfessionalException(ProfessionalException::INVALID_DEPARTMENT_ID, "Invalid department details given"); | |
| } | |
| $condition = ""; | |
| if (count($excludedPseudoSubjectIds) !== 0) { | |
| $condition .= " AND pseudosubjectID NOT IN (" . implode(",", $excludedPseudoSubjectIds) . ") "; | |
| } | |
| $sql = "SELECT pseudosubjectID as id,subjectName as name | |
| FROM pseudosubjects | |
| WHERE hdl_deptID = $departmentId $condition ORDER BY subjectName"; | |
| try { | |
| $response = $this->executeQueryForList($sql); | |
| $response = CommonUtility::decodeHtmlSpecialChars($response); | |
| return $response; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| public function deletePseudoSubjectUnAssignedSubbatchByPsId($psId) | |
| { | |
| $sql_del_exm_mark = "DELETE sm.* from student_marks sm INNER JOIN exam ex ON ex.examID=sm.examID inner join subbatches sb ON ex.subbatchID=sb.subbatchID where sb.psID=$psId and sb.batchID not in (select sr.batchID from pseudosubjects_sbs ps inner join sbs_relation sr ON sr.sbsID=ps.sbsID where ps.pseudosubjectID=$psId)"; | |
| $sql_del_exm = "DELETE ex.* from exam ex inner join subbatches sb ON ex.subbatchID=sb.subbatchID where sb.psID=$psId and sb.batchID not in (select sr.batchID from pseudosubjects_sbs ps inner join sbs_relation sr ON sr.sbsID=ps.sbsID where ps.pseudosubjectID=$psId)"; | |
| $sql_del_student = "DELETE ss.* from subbatch_student ss inner join subbatches sb ON ss.subbatchID=sb.subbatchID where psID=$psId and batchID not in (select sr.batchID from pseudosubjects_sbs ps inner join sbs_relation sr ON sr.sbsID=ps.sbsID where ps.pseudosubjectID=$psId)"; | |
| $sql_del_staff = "DELETE ss.* from subbatch_sbs ss inner join subbatches sb ON ss.subbatchID=sb.subbatchID where psID=$psId and batchID not in (select sr.batchID from pseudosubjects_sbs ps inner join sbs_relation sr ON sr.sbsID=ps.sbsID where ps.pseudosubjectID=$psId)"; | |
| $sql_del_sub = "delete from subbatches where psID=$psId and batchID not in (select sr.batchID from pseudosubjects_sbs ps inner join sbs_relation sr ON sr.sbsID=ps.sbsID where ps.pseudosubjectID=$psId)"; | |
| try { | |
| $this->executeQuery($sql_del_exm_mark); | |
| $this->executeQuery($sql_del_exm); | |
| $this->executeQuery($sql_del_student); | |
| $this->executeQuery($sql_del_staff); | |
| $this->executeQuery($sql_del_sub); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| public function getPseudoSubjectDetails($pseudoSubjectId, $staffId) | |
| { | |
| $pseudoSubjectId = $this->realEscapeString($pseudoSubjectId); | |
| $staffId = $this->realEscapeString($staffId); | |
| $sql = null; | |
| $pseudoSubject = null; | |
| try { | |
| $sql = "SELECT | |
| psbs.pseudosubsbsID AS pseudoSubjectSbsId, | |
| sbs.sbsID AS sbsId, | |
| sbs.batchID AS batchId, | |
| sbs.semID AS semId, | |
| sbs.subjectID AS subjectId, | |
| sbs.staffID AS staffId, | |
| psbs.pseudosubjectID AS pseudoSubjectId, | |
| sb.subbatchID AS subbatchId, | |
| sb.subbatchName, | |
| ps.subjectName | |
| FROM | |
| pseudosubjects_sbs psbs | |
| INNER JOIN | |
| sbs_relation sbs ON (psbs.sbsID = sbs.sbsID) | |
| INNER JOIN | |
| subbatches sb ON sb.psID = psbs.pseudosubjectID | |
| AND sb.batchID = sbs.batchID | |
| INNER JOIN | |
| pseudosubjects ps ON ps.pseudosubjectID = psbs.pseudosubjectID | |
| WHERE | |
| psbs.pseudosubjectID = '$pseudoSubjectId' | |
| AND sbs.staffID ='$staffId'"; | |
| $pseudoSubject = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $pseudoSubject; | |
| } | |
| public function getPseudoSubjectSbsDetailsByPseudoSubjectIdAndStaffId($pseudoSubjectId, $staffId) | |
| { | |
| $pseudoSubjectId = $this->realEscapeString($pseudoSubjectId); | |
| $staffId = $this->realEscapeString($staffId); | |
| $sql = "SELECT distinct sbs.sbsID, dept.deptName,sa.staffID, sa.staffName, sbs.subjectID, sbs.semID, sbs.batchID, ss.subbatchID,psub.pseudosubjectID, psub.subjectName,sub.subjectName as subjectCode FROM pseudosubjects psub LEFT JOIN pseudosubjects_sbs psbs ON psbs.pseudosubjectID=psub.pseudosubjectID LEFT JOIN department dept ON dept.deptID=psub.hdl_deptID LEFT JOIN sbs_relation sbs ON sbs.sbsID=psbs.sbsID LEFT JOIN subjects sub ON sub.subjectID = sbs.subjectID LEFT JOIN subbatch_sbs ss ON ss.sbsID=psbs.sbsID LEFT JOIN staffaccounts sa ON sa.staffID=sbs.staffID WHERE psub.pseudosubjectID = $pseudoSubjectId AND sa.staffID = $staffId"; | |
| try { | |
| return $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| public function getPseudoSubjectAndAssessmentDetailsForMarkEntry($pseudoSubjectDetails) | |
| { | |
| $pseudoSubjectDetails = $this->realEscapeObject($pseudoSubjectDetails); | |
| $sql = null; | |
| $pseudoSubjectDetailsForMarkEntry = null; | |
| try { | |
| $sql = "SELECT | |
| sbs.batchID AS batchId, | |
| psbs.pseudosubsbsID AS pseudoSubjectSbsId, | |
| sbs.sbsID AS sbsId, | |
| sbs.batchID AS batchId, | |
| sbs.semID AS semId, | |
| sbs.subjectID AS subjectId, | |
| sbs.staffID AS staffId, | |
| psbs.pseudosubjectID AS pseudoSubjectId, | |
| sb.subbatchID AS subbatchId, | |
| sb.subbatchName, | |
| ba.assignmentID AS assignmentId, | |
| ast.id AS assessmentStructureId | |
| FROM | |
| pseudosubjects_sbs psbs | |
| INNER JOIN | |
| sbs_relation sbs ON (psbs.sbsID = sbs.sbsID AND sbs.staffID = '$pseudoSubjectDetails->staffId') | |
| INNER JOIN | |
| subbatches sb ON sb.psID = psbs.pseudosubjectID | |
| AND sb.batchID = sbs.batchID | |
| INNER JOIN | |
| batch_assignment ba ON (ba.batchID = sbs.batchID | |
| AND ba.semID = sbs.semID | |
| AND ba.subjectID = sbs.subjectID | |
| AND " . ($pseudoSubjectDetails->exAssiNu ? "externalAssiNu = $pseudoSubjectDetails->assessmentKey" : "assiNu = $pseudoSubjectDetails->assessmentKey") . " ) | |
| INNER JOIN | |
| assessment_structure ast ON (ast.batchID = sbs.batchID | |
| AND ast.semID = sbs.semID | |
| AND ast.subjectID = sbs.subjectID | |
| AND ast.subbatchID = sb.subbatchID | |
| AND ast.assessment_type = '$pseudoSubjectDetails->assessmentType' | |
| AND ast.assessment_key = $pseudoSubjectDetails->assessmentKey) | |
| WHERE | |
| psbs.pseudosubjectID = '$pseudoSubjectDetails->pseudoSubjectId'"; | |
| $pseudoSubjectDetailsForMarkEntry = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $pseudoSubjectDetailsForMarkEntry; | |
| } | |
| public function getPseudoSubjectBySbsId($sbsId) | |
| { | |
| $sbsId = $this->realEscapeString($sbsId); | |
| $sql = "select psbs.pseudosubjectID from sbs_relation sbs | |
| inner join pseudosubjects_sbs psbs on psbs.sbsID = sbs.sbsID | |
| inner join batches b on b.batchID = sbs.batchID and b.semID = sbs.semID | |
| where sbs.sbsID = $sbsId group by psbs.pseudosubjectID;"; | |
| try { | |
| return $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * @param $pseudoSubjectId | |
| * @return int | |
| * @throws ProfessionalException | |
| * @author jithinvijayan | |
| */ | |
| public function getStaffPseudoSubjectDetails($pseudoSubjectId, $staffId) | |
| { | |
| $pseudoSubjectId = $this->realEscapeString($pseudoSubjectId); | |
| $staffId = $this->realEscapeString($staffId); | |
| if (empty($pseudoSubjectId)) { | |
| throw new ProfessionalException(ProfessionalException::INVALID_PSEUDO_SUBJECT_ID, "Invalid pseudo subject details given"); | |
| } | |
| $sql = "SELECT sbs.batchID,sbs.semID,sbs.subjectID,sbs.staffID,ps.subjectName,b.batchDesc,b.batchName,sb.subbatchID,ps.pseudosubjectID,pst.code | |
| FROM pseudosubjects ps | |
| INNER JOIN pseudosubjects_sbs psbs ON psbs.pseudosubjectID = ps.pseudosubjectID | |
| INNER JOIN sbs_relation sbs ON sbs.sbsID = psbs.sbsID | |
| INNER JOIN subbatches sb ON sb.psID = ps.pseudosubjectID AND sbs.batchID = sb.batchID AND sbs.semID = sb.semID | |
| INNER JOIN batches b ON b.batchID = sbs.batchID | |
| INNER JOIN pseudo_subject_type pst ON pst.id = ps.pseudo_subject_type_id | |
| WHERE ps.pseudosubjectID = $pseudoSubjectId AND sbs.staffID = $staffId;"; | |
| try { | |
| $response = $this->executeQueryForList($sql); | |
| $response = CommonUtility::decodeHtmlSpecialChars($response); | |
| return $response; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| public function getPseudoSubjectDistinctBatchDetailsById($suSubId) | |
| { | |
| $pseudoSubjectDetails = NULL; | |
| $sql = "SELECT Distinct sr.batchID,ps.pseudosubjectID, ps.subjectName, dp.deptName, dp.departmentDesc, sr.subjectID, sr.semID, sm.semName, bt.batchName FROM pseudosubjects_sbs pss INNER JOIN pseudosubjects ps ON ps.pseudosubjectID=pss.pseudosubjectID INNER JOIN sbs_relation sr ON sr.sbsID=pss.sbsID INNER JOIN batches bt ON bt.batchID=sr.batchID INNER JOIN semesters sm ON sm.semID=sr.semID INNER JOIN department dp ON dp.deptID=bt.deptID WHERE ps.pseudosubjectID=$suSubId"; | |
| try { | |
| $pseudoSubjectDetails = $this->executeQueryForList($sql); | |
| $pseudoSubjectDetails = CommonUtility::decodeHtmlSpecialChars($pseudoSubjectDetails); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $pseudoSubjectDetails; | |
| } | |
| /** | |
| * @param $sbsId | |
| * @param $staffId | |
| * @return mixed | |
| * @throws ProfessionalException | |
| */ | |
| public function getPseudoSubjectId($sbsId, $staffId, $semId =null,$allPsSubjects = false) | |
| { | |
| $sbsId = (int)$this->realEscapeString($sbsId); | |
| $staffId = (int)$this->realEscapeString($staffId); | |
| if (empty($sbsId)) { | |
| throw new ProfessionalException(ProfessionalException::INVALID_SBS_ID, "Invalid request sent"); | |
| } | |
| if (empty($staffId)) { | |
| throw new ProfessionalException(ProfessionalException::INVALID_STAFF_ID, "Invalid request sent"); | |
| } | |
| if(empty($semId)) { | |
| $currentSemester = " AND b.semID = sbs.semID"; | |
| $semester = ""; | |
| } | |
| else { | |
| $currentSemester = ""; | |
| $semester = " AND sbs.semID =$semId"; | |
| } | |
| $sql = "SELECT DISTINCT psbs.pseudosubjectID AS id | |
| FROM pseudosubjects_sbs psbs | |
| INNER JOIN sbs_relation sbs ON psbs.sbsID = sbs.sbsID | |
| INNER JOIN batches b ON b.batchID = sbs.batchID $currentSemester | |
| WHERE sbs.sbsID = $sbsId AND sbs.staffID =$staffId "; | |
| $sql .= $semester; | |
| try { | |
| if($allPsSubjects){ //return all pseudosubjects Id in cases more than one | |
| return $this->executeQueryForList($sql); | |
| } | |
| else{ | |
| return $this->executeQueryForObject($sql)->id; | |
| } | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * Used to get the finalised pseudoSubjectId from the given request data with sbs | |
| * @param Object $request | |
| * @return String | |
| * @throws ProfessionalException5 | |
| */ | |
| public function getFinalisedPseudoSubjectId($request) | |
| { | |
| // Used for object destrucuring | |
| [ 'sbsId' => $sbsId, 'staffId' => $staffId, 'semId' => $semId ] = get_object_vars( $request ); | |
| $sbsId = (int)$this->realEscapeString($sbsId); | |
| $staffId = (int)$this->realEscapeString($staffId); | |
| if (empty($sbsId)) { | |
| throw new ProfessionalException(ProfessionalException::INVALID_SBS_ID, "Invalid request sent"); | |
| } | |
| if (empty($staffId)) { | |
| throw new ProfessionalException(ProfessionalException::INVALID_STAFF_ID, "Invalid request sent"); | |
| } | |
| try { | |
| if(empty($semId)) { | |
| $currentSemester = " AND b.semID = sbs.semID"; | |
| $semester = ""; | |
| } | |
| else { | |
| $currentSemester = ""; | |
| $semester = " AND sbs.semID =$semId"; | |
| } | |
| $sql = "SELECT | |
| DISTINCT psbs.pseudosubjectID AS id | |
| FROM | |
| pseudosubjects_sbs psbs | |
| INNER JOIN pseudosubjects p ON | |
| p.pseudosubjectID = psbs.pseudosubjectID | |
| INNER JOIN sbs_relation sbs ON | |
| psbs.sbsID = sbs.sbsID | |
| $currentSemester | |
| WHERE | |
| p.isFinalized = '1' | |
| AND sbs.sbsID = '$sbsId' | |
| AND sbs.staffID = '$staffId' | |
| $semester"; | |
| return $this->executeQueryForObject($sql)->id; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * @param $pseudoSubjectId | |
| * @param $staffId | |
| * @return mixed | |
| * @throws ProfessionalException | |
| */ | |
| public function getPseudoSubjectStaffSBSId($pseudoSubjectId, $staffId) | |
| { | |
| if (empty($pseudoSubjectId)) { | |
| throw new ProfessionalException(ProfessionalException::INVALID_PSEUDO_SUBJECT_ID, "Invalid pseudo subject details given"); | |
| } | |
| if (empty($staffId)) { | |
| throw new ProfessionalException(ProfessionalException::INVALID_STAFF_ID, "Invalid staff details given"); | |
| } | |
| $sql = "SELECT sr.sbsID as sbsId FROM pseudosubjects ps | |
| INNER JOIN pseudosubjects_sbs p on ps.pseudosubjectID = p.pseudosubjectID | |
| INNER JOIN sbs_relation sr on p.sbsID = sr.sbsID | |
| WHERE sr.staffID = $staffId AND ps.pseudosubjectID = $pseudoSubjectId"; | |
| try { | |
| return $this->executeQueryForObject($sql)->sbsId; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * @param $pseudoSubjectId | |
| * @return Object | |
| * @throws ProfessionalException | |
| */ | |
| public function getSiblingPseudoSubjects($pseudoSubjectId) | |
| { | |
| $pseudoSubjectId = (int)$this->realEscapeString($pseudoSubjectId); | |
| if (empty($pseudoSubjectId)) { | |
| throw new ProfessionalException(ProfessionalException::INVALID_PSEUDO_SUBJECT_ID, "Invalid pseudo subject details given"); | |
| } | |
| $sql = "SELECT DISTINCT ps.pseudosubjectID as id | |
| FROM pseudosubjects ps | |
| INNER JOIN pseudosubjects_sbs p on ps.pseudosubjectID = p.pseudosubjectID | |
| INNER JOIN sbs_relation sr on p.sbsID = sr.sbsID | |
| WHERE sr.subjectID IN ( | |
| SELECT subject_id FROM subject_pseudo_subjects sps | |
| WHERE sps.pseudo_subject_id = $pseudoSubjectId | |
| ) AND sr.isPseudosubject=1;"; | |
| try { | |
| return $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| public function isPseudoSubjectIsFinalisedForAssignmentDisplay($pseudoSubjectId) | |
| { | |
| $sql = "SELECT isFinalized FROM pseudosubjects WHERE pseudosubjectID =$pseudoSubjectId"; | |
| try { | |
| return (int)$this->executeQueryForObject($sql)->isFinalized; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * @param $request | |
| * @return unknown | |
| * @throws ProfessionalException | |
| */ | |
| public function getPseudoSubjectDetailsByRequest($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| if (empty((int)$request->pseudoSubjectId)) { | |
| throw new ProfessionalException(ProfessionalException::INVALID_PSEUDO_SUBJECT_ID, "Invalid pseudo subject details given"); | |
| } | |
| $select = ["ps.pseudosubjectID", "ps.subjectName"]; | |
| $where = ["ps.pseudosubjectID = " . $request->pseudoSubjectId]; | |
| $grouping = []; | |
| $joins = []; | |
| if ($request->basicDetails || $request->staffDetails || $request->needStudents || $request->assignments) { | |
| $select = array_merge($select, ["sbs.staffID", "sbs.batchID", "sbs.semID","bat.batchName","sem.semName as subjectSem","sub.subbatchID as subBatchId","sbs.sbsID as sbsId","sbs.subjectID"]); | |
| $joins = array_merge($joins, [ | |
| "inner join pseudosubjects_sbs psbs on psbs.pseudosubjectID = ps.pseudosubjectID", | |
| "inner join sbs_relation sbs on sbs.sbsID = psbs.sbsID", | |
| "inner join semesters sem on sem.semID = sbs.semID", | |
| "inner join subbatches sub on sub.psID = ps.pseudosubjectID and sub.batchID = sbs.batchID and sub.semID = sbs.semID", | |
| "inner join batches bat on bat.batchID = sbs.batchID" | |
| ]); | |
| $request->staffId ? $where[] = "sbs.staffID=" . $request->staffId : null; | |
| $request->batchId ? $where[] = "sbs.staffID=" . $request->batchId : null; | |
| $request->semId ? $where[] = "sbs.semID=" . $request->semId : null; | |
| } | |
| if ($request->staffDetails) { | |
| $select = array_merge($select, ["st.staffID", "st.staffName", "st.staffCode"]); | |
| $joins [] = "inner join staffaccounts st on st.staffID = sbs.staffID"; | |
| } | |
| if ($request->needStudents) { | |
| $select = array_merge($select, ["std.studentID as id", "std.studentName as name","std.rollNo","std.regNo","std.studentAccount","subst.subbatchID as student_subbatch","std.admissionNo as admissionNumber"]); | |
| $joins = array_merge($joins, [ | |
| "inner join pseudosubjects_students psstd on psstd.pseudosubjectID = psbs.pseudosubjectID", | |
| "inner join subbatch_student subst on subst.subbatchID = sub.subbatchID and psstd.studentID = subst.studentID", | |
| "inner join studentaccount std on std.studentID = psstd.studentID and std.batchID = sbs.batchID" | |
| ]); | |
| $grouping = ["std.studentID"]; | |
| } | |
| if ($request->assignments && $request->assignmentNo) { | |
| $request->assignmentNo ? $where[] = "ba.assiNu=" . $request->assignmentNo : null; | |
| $select = array_merge($select, ["ba.assignmentID", "ba.assiNu"]); | |
| $joins = array_merge($joins, [ | |
| "inner join batch_assignment ba on ba.psID = ps.pseudosubjectID and ba.staffID = sbs.staffID and ba.batchID = sbs.batchID and ba.semID = sbs.semID and ba.subbatchID = sub.subbatchID" | |
| ]); | |
| } | |
| $sql = "SELECT " . implode(', ', $select) . " from pseudosubjects ps " . implode(' ', $joins) . ($where ? " where " . implode(' and ', $where) : null) . ($grouping ? " group by " . implode(',', $grouping) : "") . ";"; | |
| try { | |
| $response = $this->executeQueryForList($sql); | |
| $response = CommonUtility::decodeHtmlSpecialChars($response); | |
| return $response; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * @param array $pseudosubjectsIds | |
| * @return array | |
| * @throws ProfessionalException | |
| */ | |
| public function getAllAssignedSbsIdsByPseudosubjectsIds($pseudoSubjectId){ | |
| try { | |
| if(!empty($pseudoSubjectId)){ | |
| $sql = "SELECT ps.pseudosubjectID, ps.subjectName, ps.courseTypeID, ps.subjectCreateDate, ps.isOpencourse, ps.hdl_deptID, ps.isFinalized, ps.pseudo_subject_type_id, ps.pseudo_subject_group_id, ps.created_by, ps.created_date, ps.updated_by, ps.updated_date, group_concat(DISTINCT psbs.pseudosubsbsID) AS sbsIds FROM pseudosubjects ps | |
| INNER JOIN pseudosubjects_sbs psbs ON psbs.pseudosubjectID = ps.pseudosubjectID | |
| WHERE ps.pseudosubjectID IN (".implode(',',$pseudoSubjectId).") | |
| GROUP BY ps.pseudosubjectID;"; | |
| $response = $this->executeQueryForList($sql); | |
| $response = CommonUtility::decodeHtmlSpecialChars($response); | |
| return $response; | |
| }else{ | |
| return []; | |
| } | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| public function getPseudoSubjectByRequest($request){ | |
| $where = []; | |
| $where [] = " ps.isFinalized = 1 "; | |
| $request->deptId?$where [] = " ps.hdl_deptID = $request->deptId ":''; | |
| $request->pseudoSubjectId?$where [] = " ps.pseudosubjectID = $request->pseudoSubjectId ":''; | |
| $request->staffId?$where [] = " sbs.staffID = $request->staffId ":''; | |
| $result = []; | |
| try { | |
| $sql = "SELECT ps.pseudosubjectID, | |
| ps.courseTypeID, | |
| ps.isFinalized, | |
| ps.isOpencourse, | |
| ps.hdl_deptID as deptID, | |
| pdept.deptName, | |
| ps.subjectName, | |
| pdept.deptName as handlingDepartmentName, | |
| pdept.departmentDesc as handlingDepartmentDesc, | |
| sbs.sbsID, | |
| sbs.staffID, | |
| sbs.batchID, | |
| sbs.subjectID, | |
| sbs.semID as subjectSemId, | |
| sem.semName as subjectSemName, | |
| bat.batchName, | |
| bat.batchDesc, | |
| bat.batchStartYear, | |
| bat.batchEndYear, | |
| bat.semID as currentSem, | |
| csem.semName as currentSemName, | |
| sub.subjectDesc, | |
| sub.subjectName as subjectCode, | |
| sub.isTheory, | |
| st.staffName, | |
| st.myImage, | |
| st.staffCode, | |
| st.staffID, | |
| concat('[',group_concat(distinct JSON_OBJECT('id',sbs.batchID,'name',bat.batchName,'sem',sem.semName)),']') as batchDetails, | |
| concat('[',group_concat(distinct JSON_OBJECT('id',sbs.staffId,'name',st.staffName)),']') as staffDetails, | |
| concat('[',group_concat(distinct JSON_OBJECT('sem',sem.semName,'id',sem.semID)),']') as semDetails | |
| from sbs_relation sbs | |
| inner join pseudosubjects_sbs psbs on sbs.sbsID = psbs.sbsID | |
| inner join pseudosubjects ps on psbs.pseudosubjectID = ps.pseudosubjectID | |
| inner join department pdept on pdept.deptID = ps.hdl_deptID | |
| inner join semesters sem on sem.semID = sbs.semID | |
| inner join batches bat on bat.batchID = sbs.batchID | |
| inner join semesters csem on csem.semID = bat.semID | |
| inner join subjects sub on sub.subjectID = sbs.subjectID | |
| inner join staffaccounts st on st.staffID = sbs.staffID | |
| ".($where?" WHERE ".implode(' AND ',$where):"")." | |
| group by ps.pseudosubjectID;"; | |
| $responses = $this->executeQueryForList($sql); | |
| foreach ($responses as $response) { | |
| if($response->staffID) | |
| { | |
| $profilePicture = StaffService::getInstance()->getStaffProfilePic($response->staffID); | |
| $imagePath = $profilePicture->docpath; | |
| $response->myImage = $imagePath; | |
| $result[] = $response; | |
| } | |
| } | |
| $result = CommonUtility::decodeHtmlSpecialChars($result); | |
| return $result; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| public function getDetailsOfPseudosubjectSbs($staffID, $pssubID) | |
| { | |
| $staffID = $this->realEscapeString($staffID); | |
| $pssubID = $this->realEscapeString($pssubID); | |
| $query = "SELECT t1.* FROM sbs_relation t1, batches t2, pseudosubjects_sbs t3, pseudosubjects t4 WHERE t1.staffID = \"" . $staffID . "\" AND t1.batchID=t2.batchID AND t1.semID=t2.semID AND t1.sbsID=t3.sbsID AND t3.pseudosubjectID=t4.pseudosubjectID AND t4.pseudosubjectID = \"$pssubID\""; | |
| try { | |
| $response = $this->executeQueryForObject($query); | |
| return $response; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * @param batchID,subjectID,staffID | |
| * @throws ProfessionalException | |
| */ | |
| public function getPesudeosubjectIdsbyBatchSubjectStaff($subjectID, $batchID, $staffID) | |
| { | |
| $staffID = $this->realEscapeString($staffID); | |
| $subjectID = $this->realEscapeString($subjectID); | |
| $batchID = $this->realEscapeString($batchID); | |
| $result=""; | |
| $sql = "SELECT psbs.pseudosubjectID from pseudosubjects_sbs psbs inner join sbs_relation sbs on sbs.sbsID = psbs.sbsID where sbs.subjectID='$subjectID' and sbs.batchID='$batchID' and sbs.staffID='$staffID'"; | |
| try { | |
| $result =$this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $result; | |
| } | |
| public function getPseudoSubjectsByDeptIdandStaffId($deptID = NULL, $staffID = NULL){ | |
| $deptID = $this->realEscapeString($deptID); | |
| $staffID = $this->realEscapeString($staffID); | |
| $cond = ""; | |
| if($deptID){ | |
| $cond .= " AND ps.hdl_deptID = '$deptID'"; | |
| } | |
| if($staffID){ | |
| $cond .= " AND sbs.staffID = '$staffID'"; | |
| } | |
| $cond .= " GROUP BY ps.pseudosubjectID"; | |
| $sql = "SELECT ps.pseudosubjectID,ps.subjectName,ps.hdl_deptID,ps.courseTypeID,ct.typeName,group_concat(bt.batchName) as batchNames,group_concat(bt.batchID) as batchIds,sbs.semID,sem.semName from pseudosubjects ps inner join pseudosubjects_sbs pssbs on ps.pseudosubjectID=pssbs.pseudosubjectID inner join course_type ct on ct.courseTypeID=ps.courseTypeID inner join sbs_relation sbs on sbs.sbsID=pssbs.sbsID inner join batches bt on bt.batchID = sbs.batchID and sbs.semID = bt.semID inner join semesters sem on sem.semID = sbs.semID WHERE sbs.isPseudoSubject = 1 AND ps.isFinalized = 1 $cond"; | |
| try { | |
| $subjects =$this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $subjects; | |
| } | |
| public function deletePseudoSubjectId($id){ | |
| $id = $this->realEscapeString($id); | |
| if (!$id) { | |
| throw new ProfessionalException(ProfessionalException::INVALID_PSEUDO_SUBJECT_ID, 'Invalid Pseudo-Subject'); | |
| } | |
| $deleteStudents = " DELETE FROM pseudosubjects_students WHERE pseudosubjectID = '$id' "; | |
| $deletePseudoSubjectSbs = " DELETE FROM pseudosubjects_sbs WHERE pseudosubjectID = '$id' "; | |
| try { | |
| $this->executeQuery($deleteStudents); | |
| $this->executeQuery($deletePseudoSubjectSbs); | |
| $subBatches = $this->getsubbatchIDsAndbatchIDs($id); | |
| foreach ($subBatches as $subBatch) { | |
| SubBatchService::getInstance()->deleteSubbatch($subBatch->subbatchID); | |
| } | |
| $this->deletePseudoSubjectById($id); | |
| } catch (\Throwable $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * @param Int $pseudoSubjectId | |
| * @param Array $students | |
| * @param Array $staffIds | |
| * @return Array | |
| * @throws ProfessionalException | |
| */ | |
| public function assignStaffsToPseudoSubjects($pseudoSubjectId, $students, $staffs, $userId) | |
| { | |
| $pseudoSubjectId = $this->realEscapeString($pseudoSubjectId); | |
| $students = $this->realEscapeArray($students); | |
| $staffs = $this->realEscapeArray($staffs); | |
| $subjectId = $this->getSubjectOfPseudoSubject($pseudoSubjectId); | |
| $allBatchesInPseudoSubject = $this->getAllStudentBatchesAssignToPseudoSubjects($pseudoSubjectId, $students); | |
| try { | |
| $allAssignedStaffs = []; | |
| foreach($staffs as $sta){ | |
| $staff = (object) $sta; | |
| if(!$staff->sbsId){ | |
| $request = new AssignFacultyToSubjectRequest(); | |
| $request->staffId = $staff->id; | |
| $request->subjectId = $subjectId; | |
| $request->assigningMethod = SBSAssigningConstants::ASSIGN_BY_FACULTY; | |
| foreach ($allBatchesInPseudoSubject as $batch) { | |
| $batchRequest = new AssignedSubjectBatchRequest(); | |
| $checkRequest = new CheckSbsAlreadyAssignedRequest(); | |
| $checkRequest->subjectId = $subjectId; | |
| $checkRequest->staffId = $staff->id; | |
| $checkRequest->batchId = $batch->batchId; | |
| $checkRequest->semesterId = $batch->semId; | |
| $isAssigned = SBSService::getInstance()->checkSBSAlreadyAssigned($checkRequest); | |
| if (!$isAssigned) { | |
| $batchRequest->semesterId = $batch->semId; | |
| $batchRequest->batchId = $batch->batchId; | |
| $request->batches[] = $batchRequest; | |
| } | |
| $notAssignedStaff = new \stdClass(); | |
| $notAssignedStaff->staffId = $staff->id; | |
| $notAssignedStaff->batchId = $batch->batchId; | |
| $notAssignedStaff->semId = $batch->semId; | |
| $notAssignedStaff->subjectId = $subjectId; | |
| $allAssignedStaffs[] = $notAssignedStaff; | |
| } | |
| $request->createdBy = $userId; | |
| $request->updatedBy = $userId; | |
| if(count($request->batches) && $request->batches) | |
| SBSService::getInstance()->assignFacultyToSubjectsAndBatches($request); | |
| } | |
| } | |
| $assignedStaffs = []; | |
| foreach($allAssignedStaffs as $sbs){ | |
| $assignedStaffs[] = SBSService::getInstance()->getSBSbyBatchSubjectSemAndStaffs($sbs->subjectId, $sbs->batchId, $sbs->semId, $sbs->staffId); | |
| } | |
| return $assignedStaffs; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * @param Int $pseudoSubjectId | |
| * @param Array $students | |
| * @return Array Batches | |
| * @throws ProfessionalException | |
| */ | |
| public function getAllStudentBatchesAssignToPseudoSubjects($pseudoSubjectId, $students) | |
| { | |
| $pseudoSubjectId = (int)$this->realEscapeString($pseudoSubjectId); | |
| $students = $this->realEscapeArray($students); | |
| $sql = | |
| "SELECT b.batchID AS batchId,b.batchName AS name, b.semID as semId | |
| FROM pseudosubjects_students pstd | |
| INNER JOIN studentaccount std ON std.studentID = pstd.studentID | |
| INNER JOIN batches b ON b.batchID = std.batchID | |
| WHERE pstd.pseudosubjectID = ".$pseudoSubjectId." AND b.batchName <> 'failed' | |
| GROUP BY b.batchID"; | |
| if(!$pseudoSubjectId) | |
| throw new ProfessionalException(ProfessionalException::INVALID_REQUEST, "Invalid request details given"); | |
| if(count($students)){ | |
| $sql .= "UNION | |
| SELECT b.batchID AS batchId,b.batchName AS name ,b.semID as semId | |
| FROM studentaccount std | |
| INNER JOIN batches b ON b.batchID = std.batchID | |
| WHERE std.studentID in ( ".implode(',',$students)." ) AND b.batchName <> 'failed' | |
| GROUP BY b.batchID;"; | |
| } | |
| try { | |
| return $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * @param Int $pseudoSubjectId | |
| * @return Array Roles | |
| * @throws ProfessionalException | |
| */ | |
| public function getAllStaffsAssignedInPseudoSubject($pseudoSubjectId) | |
| { | |
| $pseudoSubjectId = (int)$this->realEscapeString($pseudoSubjectId); | |
| if(!$pseudoSubjectId) | |
| throw new ProfessionalException(ProfessionalException::INVALID_REQUEST, "Invalid request details given"); | |
| $sql = | |
| "SELECT b.batchID, | |
| b.batchName, | |
| st.staffCode AS code, | |
| d.deptID AS departmentId, | |
| d.deptName AS departmentName, | |
| st.staffID AS id, | |
| true AS isAssigned, | |
| st.staffName AS name, | |
| sbs.sbsID AS sbsId, | |
| s.semName AS semesterName,s.semID FROM pseudosubjects_sbs psbs | |
| inner join sbs_relation sbs on sbs.sbsID = psbs.sbsID | |
| INNER JOIN batches b ON b.batchID = sbs.batchID | |
| INNER JOIN staffaccounts st ON st.staffID = sbs.staffID | |
| INNER JOIN department d ON d.deptID = st.deptID | |
| INNER JOIN semesters s ON s.semID = sbs.semID | |
| WHERE psbs.pseudosubjectID = $pseudoSubjectId ;"; | |
| try { | |
| $staffDetails = $this->executeQueryForList($sql); | |
| return empty($staffDetails)?array():$staffDetails; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * @param Int $pseudoSubjectId | |
| * @param Array $students | |
| * @return Array Batches | |
| * @throws ProfessionalException | |
| */ | |
| public function getAllStudentsAssignToPseudoSubjects($pseudoSubjectId) | |
| { | |
| $pseudoSubjectId = (int)$this->realEscapeString($pseudoSubjectId); | |
| $sql = | |
| "SELECT std.batchID as batchId,b.batchName,std.studentID as id,0 as isAssigned,std.studentName as name,std.regNo as registerNumber,std.rollNo as rollNumber, std.admissionNo as admissionNumber,std.studentAccount | |
| FROM pseudosubjects_students pstd | |
| INNER JOIN studentaccount std ON std.studentID = pstd.studentID | |
| INNER JOIN batches b ON b.batchID = std.batchID | |
| WHERE pstd.pseudosubjectID = ".$pseudoSubjectId." AND b.batchName <> 'failed' | |
| GROUP BY std.studentID "; | |
| if(!$pseudoSubjectId) | |
| throw new ProfessionalException(ProfessionalException::INVALID_REQUEST, "Invalid request details given"); | |
| try { | |
| $batchDetails = $this->executeQueryForList($sql); | |
| return empty($batchDetails) ? array() : $batchDetails; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * @param Int $pseudoSubjectId | |
| * @return Array | |
| * @throws ProfessionalException | |
| */ | |
| public function getPseudosubjectSubBatchAndBatchDetails($pseudoSubjectId,$staffId = "") | |
| { | |
| $pseudoSubjectId = (int)$this->realEscapeString($pseudoSubjectId); | |
| $sql = | |
| "SELECT sbs.batchId,sbs.semId,sbs.subjectId,sub.subbatchId,psbs.pseudoSubjectId,b.batchName,psbs.sbsId,sub.subbatchName FROM pseudosubjects_sbs psbs | |
| INNER JOIN subbatch_sbs ssbs ON ssbs.sbsID = psbs.sbsID | |
| INNER JOIN subbatches sub ON sub.psID = psbs.pseudosubjectID and ssbs.subbatchID = sub.subbatchID | |
| INNER JOIN sbs_relation sbs ON sbs.sbsID = psbs.sbsID | |
| INNER JOIN batches b ON b.batchID = sbs.batchID | |
| WHERE psbs.pseudosubjectID = '$pseudoSubjectId' ".((int)$staffId?'AND sbs.staffID = '.$staffId:'')." | |
| GROUP BY sbs.batchID; "; | |
| if(!$pseudoSubjectId) | |
| throw new ProfessionalException(ProfessionalException::INVALID_REQUEST, "Invalid request details given"); | |
| try { | |
| return $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| public function getPseudoSubjectBatchList($pseudoSubjectId){ | |
| $pseudoSubjectId = (int)$this->realEscapeString($pseudoSubjectId); | |
| $sql = "SELECT DISTINCT(sbs.sbsID), sbs.batchID, sbs.semID, sbs.subjectID, sb.psID, sb.subbatchID FROM subbatches sb INNER JOIN sbs_relation sbs ON (sb.batchID = sbs.batchID AND sb.semID = sbs.semID) INNER JOIN pseudosubjects_sbs psbs ON (sbs.sbsID = psbs.sbsID AND psbs.pseudosubjectID = sb.psID) WHERE sb.psID = '$pseudoSubjectId' ORDER BY sbs.batchID "; | |
| if(!$pseudoSubjectId) | |
| throw new ProfessionalException(ProfessionalException::INVALID_REQUEST, "Invalid request details given"); | |
| try { | |
| return $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| public function easyAssignStaffsToPseudoSubjects($pseudoSubjectId,$userId){ | |
| $allAssignRoles = PseudoSubjectService::getInstance()->getAllStaffsAssignedInPseudoSubject($pseudoSubjectId); | |
| $allAssignedStaffs = []; | |
| if(count($allAssignRoles)){ | |
| $subjectId = $this->getSubjectOfPseudoSubject($pseudoSubjectId); | |
| $allBatches = PseudoSubjectService::getInstance()->getAllStudentBatchesAssignToPseudoSubjects($pseudoSubjectId,array()); | |
| $newBatches = array_diff(array_map(function ($obj) {return $obj->batchId;}, $allBatches),array_map(function ($obj) {return $obj->batchID;}, $allAssignRoles)); | |
| $staffs = array_map(function ($obj) {return $obj->id;}, $allAssignRoles); | |
| if(count($newBatches)){ | |
| foreach ($staffs as $staffId) { | |
| $request = new AssignFacultyToSubjectRequest(); | |
| $request->staffId = $staffId; | |
| $request->subjectId = $subjectId; | |
| $request->assigningMethod = SBSAssigningConstants::ASSIGN_BY_FACULTY; | |
| foreach ($allBatches as $batch) { | |
| if(in_array($batch->batchId,$newBatches)){ | |
| $batchRequest = new AssignedSubjectBatchRequest(); | |
| $checkRequest = new CheckSbsAlreadyAssignedRequest(); | |
| $checkRequest->subjectId = $subjectId; | |
| $checkRequest->staffId = $staffId; | |
| $checkRequest->batchId = $batch->batchId; | |
| $checkRequest->semesterId = $batch->semId; | |
| $isAssigned = SBSService::getInstance()->checkSBSAlreadyAssigned($checkRequest); | |
| if (!$isAssigned) { | |
| $batchRequest->semesterId = $batch->semId; | |
| $batchRequest->batchId = $batch->batchId; | |
| $request->batches[] = $batchRequest; | |
| } | |
| $notAssignedStaff = new \stdClass(); | |
| $notAssignedStaff->staffId = $staffId; | |
| $notAssignedStaff->batchId = $batch->batchId; | |
| $notAssignedStaff->semId = $batch->semId; | |
| $notAssignedStaff->subjectId = $subjectId; | |
| $allAssignedStaffs[] = $notAssignedStaff; | |
| } | |
| } | |
| $request->createdBy = $userId; | |
| $request->updatedBy = $userId; | |
| if(count($request->batches) && $request->batches) | |
| SBSService::getInstance()->assignFacultyToSubjectsAndBatches($request); | |
| } | |
| } | |
| foreach ($allAssignedStaffs as $staff) { | |
| $checkRequest = new CheckSbsAlreadyAssignedRequest(); | |
| $checkRequest->subjectId = $staff->subjectId; | |
| $checkRequest->staffId = $staff->staffId; | |
| $checkRequest->batchId = $staff->batchId; | |
| $checkRequest->semesterId = $staff->semId; | |
| $sbsId = SBSService::getInstance()->checkSBSAlreadyAssigned($checkRequest); | |
| if (SBSService::getInstance()->checkStaffAlreadyAssigned($pseudoSubjectId, $sbsId)) { | |
| continue; | |
| } | |
| $request = new AssignStaffToPseudoSubjectRequest(); | |
| $request->sbsId = $sbsId; | |
| $request->pseudoSubjectId = $pseudoSubjectId; | |
| $request->createdBy = $userId; | |
| $request->updatedBy = $userId; | |
| SBSService::getInstance()->assignStaffToPseudoSubject($request); | |
| } | |
| } | |
| } | |
| public function isMentorMenteeByPsId($psId) | |
| { | |
| $psId = $this->realEscapeString($psId); | |
| $sql = "SELECT count(pst.code) as isMentor | |
| FROM pseudosubjects p | |
| INNER JOIN pseudo_subject_type pst | |
| ON pst.id = p.pseudo_subject_type_id | |
| WHERE p.pseudosubjectID ='$psId' and pst.code='MENTOR'"; | |
| try { | |
| $isMentor =boolval($this->executeQueryForObject($sql)->isMentor); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $isMentor; | |
| } | |
| public function getPseudoSubjectsUnderSubjectBySubjectId($subjectID) | |
| { | |
| $subjectID = $this->realEscapeString($subjectID); | |
| $sql = "SELECT pseudo_subject_id as psId from subject_pseudo_subjects sps where sps.subject_id =$subjectID"; | |
| try { | |
| return $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| public function checkPresenceOfStudentInPseudoSubject($psIds,$studentId) | |
| { | |
| $psIds = (Array)$this->realEscapeArray($psIds); | |
| $psIdString = ""; | |
| foreach($psIds as $id=>$ps) | |
| { | |
| $psIds[$id] = $ps->psId; | |
| // $psIdString .= $ps->psId; | |
| } | |
| $psIdString = implode($psIds,","); | |
| $studentId = $this->realEscapeString($studentId); | |
| $sql = "SELECT | |
| count(ps.studentID) as isPresent | |
| FROM | |
| pseudosubjects_students ps | |
| INNER JOIN pseudosubjects p ON | |
| p.pseudosubjectID = ps.pseudosubjectID | |
| INNER JOIN pseudo_subject_type pst ON | |
| p.pseudo_subject_type_id = pst.id | |
| WHERE | |
| ps.pseudosubjectID in ('$psIdString') | |
| and pst.code ='MENTOR' | |
| and ps.studentID = $studentId"; | |
| try { | |
| $isPresent =boolval($this->executeQueryForObject($sql)->isPresent); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $isPresent; | |
| } | |
| /** | |
| * get batches of students whom studying a pseudo subject | |
| * | |
| * | |
| * @param Int $psID | |
| * @return Array | |
| * @throws ProfessionalException | |
| **/ | |
| public function getbatchIdsBypseudoSubjectId($psID) | |
| { | |
| $sql = "SELECT | |
| b.batchID | |
| from | |
| pseudosubjects_sbs ps | |
| inner join sbs_relation sr on | |
| sr.sbsID = ps.sbsID | |
| INNER JOIN batches b on | |
| b.batchID = sr.batchID | |
| where | |
| ps.pseudosubjectID = '$psID'"; | |
| try { | |
| $batches = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $batches; | |
| } | |
| public function getAllPseudoSubjectsForQuickReports($courseTypeId, $semester) | |
| { | |
| try { | |
| $courseTypeId = $this->realEscapeArray($courseTypeId); | |
| $semester = $this->realEscapeArray($semester); | |
| $courseTypeIds = implode(",", $courseTypeId); | |
| $semesters = implode(",", $semester); | |
| $sql = "SELECT | |
| d.deptID , | |
| d.deptName , | |
| p.pseudosubjectID , | |
| p.subjectName , | |
| ps.sbsID , | |
| COUNT(DISTINCT pss.studentID) as studentCount | |
| from | |
| pseudosubjects p | |
| left join pseudosubjects_sbs ps on ps.pseudosubjectID = p.pseudosubjectID | |
| left join sbs_relation sr on sr.sbsID = ps.sbsID | |
| left join course_type ct on ct.courseTypeID = p.courseTypeID | |
| left join batches b on b.batchID = sr.batchID | |
| left join department d on d.deptID = p.hdl_deptID | |
| left join semesters s on s.semID = b.semID | |
| left join pseudosubjects_students pss on pss.pseudosubjectID = p.pseudosubjectID | |
| left join studentaccount s2 on s2.studentID = pss.studentID | |
| inner join batches b2 on b2.batchID = s2.batchID and b2.batchHide = 0 | |
| WHERE | |
| p.courseTypeID IN ($courseTypeIds) and (b.semID IN ($semesters) OR b.semID IS NULL) | |
| GROUP BY p.pseudosubjectID | |
| ORDER by | |
| d.deptID, | |
| p.pseudosubjectID DESC, | |
| b.batchID DESC"; | |
| return $this->executeQueryForList($sql); | |
| } | |
| catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| public function getStudentDetailsFromPseudoSubjectsForCourseRegistration($psID) | |
| { | |
| try{ | |
| $psID = $this->realEscapeString($psID); | |
| $final = []; | |
| $sql_get_student_details = "SELECT | |
| ps.pseudosubjectID, | |
| ps.studentID , | |
| s.batchID, | |
| b.batchName, | |
| cr.studentsConfirmed , | |
| s.studentName, | |
| s.regNo | |
| from | |
| pseudosubjects_students ps | |
| inner join studentaccount s on | |
| s.studentID = ps.studentID | |
| left join course_registration cr on cr.batchID = s.batchID | |
| inner join batches b on b.batchID = s.batchID | |
| WHERE | |
| ps.pseudosubjectID = '$psID'"; | |
| $studentDetails = $this->executeQueryForList($sql_get_student_details); | |
| ini_set('max_execution_time', '1800'); | |
| // after script execution, time resetted to default value | |
| ini_set('memory_limit', '1024M'); | |
| foreach ($studentDetails as $key => $student) { | |
| if($student->studentsConfirmed) | |
| { | |
| $allStudentsFromCourseRegistration = json_decode($student->studentsConfirmed)->students; | |
| foreach($allStudentsFromCourseRegistration as $studentCheck) | |
| { | |
| if($studentCheck->id == $student->studentID) | |
| { | |
| $stud = new stdClass(); | |
| $stud->id = $student->studentID; | |
| $stud->name = $student->studentName; | |
| $stud->regNo = $student->regNo; | |
| $stud->status = $studentCheck->attended; | |
| $stud->batchName = $student->batchName; | |
| $final[] = $stud; | |
| } | |
| } | |
| }else | |
| { | |
| $final[] = ""; | |
| } | |
| } | |
| }catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $final; | |
| } | |
| function getCourseTypeNameAndSemNameForCourseRegistrationReport($psID) | |
| { | |
| try{ | |
| $psID = $this->realEscapeString($psID); | |
| $sql = " SELECT ct.typeName , | |
| case | |
| WHEN s.semName IS NULL THEN '-' | |
| ELSE | |
| s.semName | |
| END AS semName, | |
| p.subjectName | |
| from pseudosubjects p | |
| left join course_type ct on ct.courseTypeID = p.courseTypeID | |
| left join pseudosubjects_sbs ps on ps.pseudosubjectID = p.pseudosubjectID | |
| left join sbs_relation sr on sr.sbsID = ps.sbsID | |
| left join semesters s on s.semID = sr.semID | |
| WHERE p.pseudosubjectID = '$psID'"; | |
| $ps = $this->executeProcForObject($sql); | |
| return $ps; | |
| } | |
| catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * get batches of students whom studying a pseudo subject | |
| * | |
| * | |
| * @param Int Id,Date,Hour | |
| * @return Array | |
| * @throws ProfessionalException | |
| **/ | |
| function getPsAttendanceDetailsForEdit($psID, $date, $hour) | |
| { | |
| $psID = $this->realEscapeString($psID); | |
| $date = $this->realEscapeString($date); | |
| $hour = $this->realEscapeString($hour); | |
| try{ | |
| $sql = "SELECT | |
| a.id , | |
| a.attendanceDate , | |
| a.`hour` , | |
| a.`day` , | |
| s.studentID , | |
| s.studentName , | |
| b.batchID, | |
| b.batchName , | |
| a.isAbsent , | |
| a.isBlocked | |
| from | |
| attendance a | |
| inner join studentaccount s on s.studentID = a.studentID | |
| inner join batches b on b.batchID = s.batchID | |
| WHERE | |
| a.attendanceDate = '$date' | |
| and a.`hour` = '$hour' | |
| and a.sbsID in (SELECT | |
| ps.sbsID | |
| from | |
| pseudosubjects_sbs ps | |
| where | |
| ps.pseudosubjectID = '$psID') | |
| order by b.batchID ,s.studentID"; | |
| return $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * get batches of students whom studying a pseudo subject | |
| * | |
| * | |
| * @param Array | |
| * @return Null | |
| * @throws ProfessionalException | |
| **/ | |
| function updatePsAttendanceDetailsFfromEdit($data) | |
| { | |
| $data = $this->realEscapeArray($data); | |
| $absentEntries = []; | |
| $presentEntries = []; | |
| try{ | |
| foreach ($data as $entry) { | |
| $entry = (object) $entry; | |
| $entry->isChecked = (bool) $entry->isChecked ; | |
| if(!$entry->isChecked) | |
| { | |
| $absentEntries[] = $entry->id; | |
| } | |
| else{ | |
| $presentEntries[] = $entry->id; | |
| } | |
| } | |
| $absentEntries = implode(",",$absentEntries); | |
| $presentEntries = implode(",",$presentEntries); | |
| if($absentEntries) | |
| { | |
| $sqlAbsent = "UPDATE attendance set isAbsent = 1 WHERE id IN ($absentEntries)"; | |
| $this->executeQuery($sqlAbsent); | |
| } | |
| if($presentEntries) | |
| { | |
| $sqlPresent = "UPDATE attendance set isAbsent = 0 WHERE id IN ($presentEntries)"; | |
| $this->executeQuery($sqlPresent); | |
| } | |
| return true; | |
| }catch (\Exception $e) { | |
| return false; | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| function getsemIDOfFirstBatchOfPsSubjects($staffID, $psID, $batchID) | |
| { | |
| $staffID = $this->realEscapeString($staffID); | |
| $psID = $this->realEscapeString($psID); | |
| $batchID = $this->realEscapeString($batchID); | |
| try{ | |
| $sql = "SELECT | |
| sr.semID | |
| from | |
| pseudosubjects_sbs ps | |
| inner join sbs_relation sr on | |
| sr.sbsID = ps.sbsID | |
| WHERE | |
| ps.pseudosubjectID = $psID | |
| and sr.staffID = $staffID and sr.batchID = $batchID | |
| LIMIT 1"; | |
| return $this->executeQueryForObject($sql); | |
| }catch (\Exception $e) { | |
| return false; | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| } |