Code Coverage |
||||||||||
Classes and Traits |
Functions and Methods |
Lines |
||||||||
| Total | |
0.00% |
0 / 1 |
|
0.00% |
0 / 41 |
CRAP | |
0.00% |
0 / 922 |
| SBSService | |
0.00% |
0 / 1 |
|
0.00% |
0 / 41 |
22350.00 | |
0.00% |
0 / 922 |
| __construct | |
0.00% |
0 / 1 |
2.00 | |
0.00% |
0 / 4 |
|||
| __clone | |
0.00% |
0 / 1 |
2.00 | |
0.00% |
0 / 2 |
|||
| getInstance | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 5 |
|||
| getAllSBSAssignedToFacultyByFacultyId | |
0.00% |
0 / 1 |
42.00 | |
0.00% |
0 / 70 |
|||
| getFacultiesNotAssignedToSubject | |
0.00% |
0 / 1 |
30.00 | |
0.00% |
0 / 25 |
|||
| getSubjectsThatNotAssignedToFaculty | |
0.00% |
0 / 1 |
42.00 | |
0.00% |
0 / 33 |
|||
| getAllFacultyAssignedToSubject | |
0.00% |
0 / 1 |
30.00 | |
0.00% |
0 / 66 |
|||
| getBatchesAssignedToSubjectByStaffId | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 21 |
|||
| assignFacultyToSubjectsAndBatches | |
0.00% |
0 / 1 |
72.00 | |
0.00% |
0 / 66 |
|||
| removeIncompleteSBSAssign | |
0.00% |
0 / 1 |
30.00 | |
0.00% |
0 / 20 |
|||
| checkSBSAlreadyAssigned | |
0.00% |
0 / 1 |
42.00 | |
0.00% |
0 / 23 |
|||
| assignSubjectsToStaffTemporary | |
0.00% |
0 / 1 |
110.00 | |
0.00% |
0 / 39 |
|||
| getAllStaffsAssignedToSubject | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 20 |
|||
| assignStaffToPseudoSubject | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 18 |
|||
| markSBSAsPseudoSBS | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 10 |
|||
| checkStaffAlreadyAssigned | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 14 |
|||
| unAssignStaffFromThePseudoSubject | |
0.00% |
0 / 1 |
30.00 | |
0.00% |
0 / 27 |
|||
| getCountOfSBSAssignedForOtherPseudoSubjects | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 11 |
|||
| unmarkSBSAsPseudoSBS | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 10 |
|||
| deleteSubBatchSBS | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 10 |
|||
| getPseudoSubjectStaffBatches | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 16 |
|||
| checkSBSAssignedForSubBatch | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 16 |
|||
| createSubBatchSBS | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 17 |
|||
| getStaffTeachingBatches | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 19 |
|||
| getStaffTeachingSemesters | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 20 |
|||
| getStaffTeachingSubjects | |
0.00% |
0 / 1 |
42.00 | |
0.00% |
0 / 25 |
|||
| getSBSDetailsBySBSId | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 13 |
|||
| getSubjectsByBatchAndSem | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 15 |
|||
| getAllSbsIdsByStudentId | |
0.00% |
0 / 1 |
30.00 | |
0.00% |
0 / 32 |
|||
| getStaffByCourseTypeAndAdmissionYearAndSem | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 21 |
|||
| getStaffByBatchAndSem | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 18 |
|||
| createSmsLogSessionalMarkSettings | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 11 |
|||
| getSmsLogSessionalMarkSettings | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 22 |
|||
| getSubbatchIdOrPseudoSubjectIdBySbsId | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 29 |
|||
| getSBSbyBatchSubjectSem | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 14 |
|||
| getSubjectBySBS | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 15 |
|||
| getStaffSubjectsByCourseTypeAndAdmissionYearAndSem | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 24 |
|||
| getAllStaffWithASubject | |
0.00% |
0 / 1 |
30.00 | |
0.00% |
0 / 18 |
|||
| getSBSbyBatchSubjectSemAndStaffs | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 29 |
|||
| getAllSBS | |
0.00% |
0 / 1 |
90.00 | |
0.00% |
0 / 31 |
|||
| getStudentsOfSBSFromSubjectAndStaff | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 23 |
|||
| <?php | |
| /** | |
| * User: jithinvijayan | |
| * Date: 29/10/19 | |
| * Time: 10:43 AM | |
| */ | |
| namespace com\linways\core\ams\professional\service; | |
| use com\linways\core\ams\professional\constant\SBSAssigningConstants; | |
| use com\linways\core\ams\professional\constant\StatusConstants; | |
| use com\linways\core\ams\professional\constant\SubjectConstants; | |
| use com\linways\core\ams\professional\constant\UserType; | |
| use com\linways\core\ams\professional\dto\Batch; | |
| use com\linways\core\ams\professional\dto\PseudoSubjectStaff; | |
| use com\linways\core\ams\professional\dto\Staff; | |
| use com\linways\core\ams\professional\dto\Subject; | |
| use com\linways\core\ams\professional\exception\ProfessionalException; | |
| use com\linways\core\ams\professional\logging\AMSLogger; | |
| use com\linways\core\ams\professional\logging\Events; | |
| use com\linways\core\ams\professional\mapper\SBSServiceMapper; | |
| use com\linways\core\ams\professional\mapper\SubjectServiceMapper; | |
| use com\linways\core\ams\professional\request\AssignFacultyToSubjectRequest; | |
| use com\linways\core\ams\professional\request\AssignSBSTemporaryRequest; | |
| use com\linways\core\ams\professional\request\AssignStaffToPseudoSubjectRequest; | |
| use com\linways\core\ams\professional\request\AssignSubjectToDepartmentsRequest; | |
| use com\linways\core\ams\professional\request\AssignSubjectToSemestersRequest; | |
| use com\linways\core\ams\professional\request\CheckSbsAlreadyAssignedRequest; | |
| use com\linways\core\ams\professional\request\CreateSubBatchSBSRequest; | |
| use com\linways\core\ams\professional\request\GetAllStaffsAssignedToSubjectRequest; | |
| use com\linways\core\ams\professional\request\GetStaffTeachingSubjectsRequest; | |
| use com\linways\core\ams\professional\request\GetSubjectsThatNotAssignedToFacultyRequest; | |
| use com\linways\core\ams\professional\request\MarkOrUnmarkIsFinalisedPseudoSubjectRequest; | |
| use com\linways\core\ams\professional\request\RemoveIncompleteSBSRequest; | |
| use com\linways\core\ams\professional\request\UnAssignStaffFromPseudoSubjectRequest; | |
| use phpDocumentor\Reflection\Types\This; | |
| class SBSService extends BaseService | |
| { | |
| /** | |
| * Presence of a static member variable | |
| * | |
| * @var null | |
| */ | |
| private static $_instance = null; | |
| /** | |
| * @var null | |
| */ | |
| private $logger = null; | |
| /** | |
| * Mapper variable | |
| * @var array | |
| */ | |
| private $mapper = []; | |
| /** | |
| * Initialise mapper, logger, hooks here | |
| * | |
| * ReportGenderService constructor. | |
| */ | |
| private function __construct() | |
| { | |
| /** | |
| * Initialising mapper | |
| */ | |
| $this->mapper = SBSServiceMapper::getInstance()->getMapper(); | |
| /** | |
| * Initialising ams logger | |
| * logging to elastic search | |
| */ | |
| $this->logger = AMSLogger::getLogger(); | |
| } | |
| /** | |
| * Prevent any object or instance of that class to be cloned | |
| */ | |
| private function __clone() | |
| { | |
| } | |
| /** | |
| * Have a single globally accessible static method | |
| * | |
| * @return SBSService|null | |
| */ | |
| public static function getInstance() | |
| { | |
| if (!is_object(self::$_instance)) | |
| self::$_instance = new self (); | |
| return self::$_instance; | |
| } | |
| /** | |
| * Returns all subjects and batches assigned to faculty. Temporary assigned subjects also | |
| * | |
| * @param $facultyId | |
| * @param string $assigningMethod | |
| * @return Object|Staff[] | |
| * @throws ProfessionalException | |
| * @author jithinvijayan | |
| */ | |
| public function getAllSBSAssignedToFacultyByFacultyId($facultyId, $assigningMethod) | |
| { | |
| $facultyId = $this->realEscapeString($facultyId); | |
| if (empty($facultyId)) { | |
| throw new ProfessionalException(ProfessionalException::INVALID_STAFF_ID, "Staff not found"); | |
| } | |
| if (empty($assigningMethod)) { | |
| throw new ProfessionalException(ProfessionalException::INVALID_STAFF_ID, "Assigning method not found"); | |
| } | |
| $joinCondition = ""; | |
| if (!empty($assigningMethod)) { | |
| $joinCondition = " AND assigned_method='$assigningMethod' "; | |
| } | |
| $excludedSubject = SubjectConstants::TUTOR_SUBJECT; | |
| /** | |
| * Also fetching incomplete assigning details from the temporary table | |
| */ | |
| $sql = "(SELECT isr.id as sbs_id, sa.staffID AS staff_id,sa.staffName AS staff_name, | |
| su.subjectID AS subject_id, su.subjectName AS subject_name,su.subjectDesc AS subject_description, | |
| su.syllabusName as syllabus_name,su.syllabusYear as syllabus_year, | |
| hd.deptID AS handling_department_id,hd.deptName AS handling_department_name, | |
| hd.departmentDesc AS handling_department_description, | |
| b.batchID AS batch_id, b.batchName AS batch_name, | |
| cs.semID as current_semester_id,cs.semName as current_semester_name, | |
| d.deptID AS batch_department_id,d.deptName AS batch_department_name, | |
| s.semID AS semester_id,s.semName AS semester_name, | |
| sd.deptName as staff_department_name,sr.sbsID as sbs_id,isr.updated_date as updated_date, | |
| 1 as is_newly_assigned,0 as is_updated | |
| FROM staffaccounts sa | |
| INNER JOIN incomplete_sbs_relation isr on sa.staffID = isr.staff_id $joinCondition | |
| LEFT JOIN department sd ON sd.deptID = sa.deptID | |
| LEFT JOIN sbs_relation sr ON sr.staffID = sa.staffID AND isr.subject_id= sr.subjectID | |
| LEFT JOIN subjects su ON su.subjectID = isr.subject_id | |
| LEFT JOIN department hd ON hd.deptID = su.hdl_deptID | |
| LEFT JOIN batches b ON isr.batch_id = b.batchID | |
| LEFT JOIN semesters cs ON cs.semID =b.semID | |
| LEFT JOIN department d ON b.deptID = d.deptID | |
| LEFT JOIN semesters s ON isr.semester_id = s.semID | |
| WHERE sa.staffID = $facultyId AND sr.sbsID IS NULL ORDER BY isr.updated_date DESC) | |
| UNION | |
| (SELECT sr.sbsID as sbs_id, sa.staffID AS staff_id,sa.staffName AS staff_name, | |
| su.subjectID AS subject_id, su.subjectName AS subject_name,su.subjectDesc AS subject_description, | |
| su.syllabusName as syllabus_name,su.syllabusYear as syllabus_year, | |
| hd.deptID AS handling_department_id,hd.deptName AS handling_department_name, | |
| hd.departmentDesc AS handling_department_description, | |
| b.batchID AS batch_id, b.batchName AS batch_name, | |
| cs.semID as current_semester_id,cs.semName as current_semester_name, | |
| d.deptID AS batch_department_id,d.deptName AS batch_department_name, | |
| s.semID AS semester_id,s.semName AS semester_name, | |
| sd.deptName as staff_department_name,sr.sbsID as sbs_id,sr.updatedDate as updated_date, | |
| 0 as is_newly_assigned,0 as is_updated | |
| FROM staffaccounts sa | |
| LEFT JOIN department sd ON sd.deptID = sa.deptID | |
| LEFT JOIN sbs_relation sr ON sr.staffID = sa.staffID | |
| LEFT JOIN subjects su ON su.subjectID = sr.subjectID | |
| LEFT JOIN department hd ON hd.deptID = su.hdl_deptID | |
| LEFT JOIN batches b ON sr.batchID = b.batchID | |
| LEFT JOIN semesters cs ON cs.semID =b.semID | |
| LEFT JOIN department d ON b.deptID = d.deptID | |
| LEFT JOIN semesters s ON sr.semID = s.semID | |
| WHERE sa.staffID = $facultyId AND su.subjectName!='$excludedSubject' ORDER BY sr.updatedDate DESC)"; | |
| try { | |
| $returnData = $this->executeQueryForObject($sql, false, $this->mapper[SBSServiceMapper::GET_SBS_ASSIGNED_TO_FACULTY]); | |
| if(empty($returnData)){ | |
| $sql = "SELECT st.staffID AS staff_id,st.staffName AS staff_name,d.deptName AS staff_department_name,1 AS is_newly_assigned FROM staffaccounts st | |
| INNER JOIN department d ON d.deptID = st.deptID | |
| WHERE st.staffID = $facultyId;"; | |
| $returnData = $this->executeQueryForObject($sql, false, $this->mapper[SBSServiceMapper::GET_SBS_ASSIGNED_TO_FACULTY]); | |
| } | |
| return $returnData; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * @param integer $subjectId | |
| * @param array $departmentIds | |
| * @return array|Object | |
| * @throws ProfessionalException | |
| * @author jithinvijayan | |
| */ | |
| function getFacultiesNotAssignedToSubject($subjectId, $departmentIds) | |
| { | |
| $staffList = []; | |
| $subjectId = $this->realEscapeString($subjectId); | |
| $departmentIds = $this->realEscapeArray($departmentIds); | |
| if (empty($subjectId)) { | |
| throw new ProfessionalException(ProfessionalException::INVALID_STAFF_ID, "Invalid subject details or subject not found"); | |
| } | |
| if (empty($departmentIds) || count($departmentIds) === 0) { | |
| throw new ProfessionalException(ProfessionalException::INVALID_DEPARTMENT_IDS, "Invalid departments details"); | |
| } | |
| $sql = "SELECT DISTINCT sa.staffID as id,sa.staffName as name,sa.staffCode as code, | |
| d.deptID as departmentId,d.deptName as departmentName,d.departmentDesc as departmentDescription | |
| FROM staffaccounts sa | |
| LEFT JOIN department d ON sa.deptID = d.deptID | |
| LEFT JOIN sbs_relation sr ON sr.staffID =sa.staffID AND sr.subjectID =$subjectId | |
| LEFT JOIN incomplete_sbs_relation isr ON isr.staff_id =sa.staffID AND isr.subject_id = $subjectId | |
| WHERE sr.sbsID IS NULL AND isr.id IS NULL AND d.deptID IN (" . implode(",", $departmentIds) . ") | |
| AND sa.isResigned = 0"; | |
| try { | |
| $staffList = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $staffList; | |
| } | |
| /** | |
| * Returns subjects from the departments that are not assigned to a particular faculty | |
| * @param GetSubjectsThatNotAssignedToFacultyRequest $request | |
| * @return array|object | |
| * @throws ProfessionalException | |
| * @author jithinvijayan | |
| */ | |
| public function getSubjectsThatNotAssignedToFaculty(GetSubjectsThatNotAssignedToFacultyRequest $request) | |
| { | |
| $subjects = []; | |
| $request = $this->realEscapeObject($request); | |
| if (empty($request->staffId)) { | |
| throw new ProfessionalException(ProfessionalException::INVALID_STAFF_ID, "Staff details not found"); | |
| } | |
| $excludedSubject = SubjectConstants::TUTOR_SUBJECT; | |
| $sql = "SELECT DISTINCT s.subjectID as id,s.syllabusName as name,s.syllabusYear, s.subjectDesc as description, | |
| d.deptID as department_id,d.deptID as handling_department_id,d.deptName as department_name,d.deptName as handling_department_name ,d.departmentDesc as department_description, | |
| s.syllabusName | |
| FROM subjects s | |
| LEFT JOIN subject_sem_relation ssr ON s.subjectID = ssr.subjectID | |
| LEFT JOIN department d ON s.hdl_deptID = d.deptID | |
| LEFT JOIN sbs_relation sr ON s.subjectID = sr.subjectID AND sr.staffID =$request->staffId | |
| LEFT JOIN incomplete_sbs_relation isr on s.subjectID = isr.subject_id AND isr.staff_id = $request->staffId | |
| WHERE sr.sbsID IS NULL AND isr.id IS NULL AND s.subjectName !='$excludedSubject'"; | |
| if (!empty($request->departmentIds)) { | |
| $sql .= " AND s.hdl_deptID IN (" . implode(",", $request->departmentIds) . ") "; | |
| } | |
| if (!empty($request->syllabusYears)) { | |
| $sql .= " AND s.syllabusYear IN (" . implode(',', $request->syllabusYears) . ") "; | |
| } | |
| if (!empty($request->semesters)) { | |
| $sql .= " AND ssr.semID IN (" . implode(",", $request->semesters) . ") "; | |
| } | |
| $sql .= " ORDER BY s.syllabusYear DESC, s.subjectName ASC "; | |
| try { | |
| $subjects = $this->executeQueryForList($sql, $this->mapper[SBSServiceMapper::GET_UNASSIGNED_SUBJECTS]); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $subjects; | |
| } | |
| /** | |
| * Returns all faculties and batches assigned to a subject by subject id.Temporary assigned faculties also | |
| * | |
| * @param $subjectId | |
| * @param $assigningMethod | |
| * @return Object|Subject[] | |
| * @throws ProfessionalException | |
| * @author jithinvijayan | |
| */ | |
| public function getAllFacultyAssignedToSubject($subjectId, $assigningMethod) | |
| { | |
| $subjectId = $this->realEscapeString($subjectId); | |
| $assigningMethod = $this->realEscapeString($assigningMethod); | |
| if (empty($subjectId)) { | |
| throw new ProfessionalException(ProfessionalException::INVALID_SUBJECT_ID, "Subject details not found"); | |
| } | |
| if (empty($assigningMethod)) { | |
| throw new ProfessionalException(ProfessionalException::INVALID_STAFF_ID, "Assigning method not found"); | |
| } | |
| $joinCondition = ""; | |
| if (!empty($assigningMethod)) { | |
| $joinCondition = " AND assigned_method='$assigningMethod' "; | |
| } | |
| $excludedSubject = SubjectConstants::TUTOR_SUBJECT; | |
| $sql = "(SELECT isr.id as sbs_id, sa.staffID AS staff_id,sa.staffName AS staff_name, | |
| sa.staffCode, | |
| su.subjectID AS subject_id, su.subjectName AS subject_name,su.subjectDesc AS subject_description, | |
| su.syllabusName as syllabus_name,su.syllabusYear as syllabus_year, | |
| hd.deptID AS handling_department_id,hd.deptName AS handling_department_name, | |
| hd.departmentDesc AS handling_department_description, | |
| b.batchID AS batch_id, b.batchName AS batch_name, | |
| cs.semID as current_semester_id,cs.semName as current_semester_name, | |
| d.deptID AS batch_department_id,d.deptName AS batch_department_name, | |
| s.semID AS semester_id,s.semName AS semester_name, | |
| sd.deptName as staff_department_name,sr.sbsID as sbs_id,isr.updated_date as updated_date, | |
| 1 as is_newly_assigned,0 as is_updated | |
| FROM subjects su | |
| INNER JOIN incomplete_sbs_relation isr on su.subjectID = isr.subject_id $joinCondition | |
| LEFT JOIN sbs_relation sr ON isr.subject_id= sr.subjectID AND sr.staffID = isr.staff_id | |
| LEFT JOIN staffaccounts sa ON sa.staffID = isr.staff_id | |
| LEFT JOIN department sd ON sd.deptID = sa.deptID | |
| LEFT JOIN department hd ON hd.deptID = su.hdl_deptID | |
| LEFT JOIN batches b ON isr.batch_id = b.batchID | |
| LEFT JOIN semesters cs ON cs.semID =b.semID | |
| LEFT JOIN department d ON b.deptID = d.deptID | |
| LEFT JOIN semesters s ON isr.semester_id = s.semID | |
| WHERE su.subjectID = $subjectId AND sr.sbsID IS NULL ORDER BY isr.updated_date DESC) | |
| UNION | |
| (SELECT sr.sbsID as sbs_id, sa.staffID AS staff_id,sa.staffName AS staff_name, | |
| sa.staffCode, | |
| su.subjectID AS subject_id, su.subjectName AS subject_name,su.subjectDesc AS subject_description, | |
| su.syllabusName as syllabus_name,su.syllabusYear as syllabus_year, | |
| hd.deptID AS handling_department_id,hd.deptName AS handling_department_name, | |
| hd.departmentDesc AS handling_department_description, | |
| b.batchID AS batch_id, b.batchName AS batch_name, | |
| cs.semID as current_semester_id,cs.semName as current_semester_name, | |
| d.deptID AS batch_department_id,d.deptName AS batch_department_name, | |
| s.semID AS semester_id,s.semName AS semester_name, | |
| sd.deptName as staff_department_name,sr.sbsID as sbs_id,sr.updatedDate as updated_date, | |
| 0 as is_newly_assigned,0 as is_updated | |
| FROM subjects su | |
| LEFT JOIN sbs_relation sr ON sr.subjectID = su.subjectID | |
| LEFT JOIN staffaccounts sa ON sa.staffID = sr.staffID | |
| LEFT JOIN department sd ON sd.deptID = sa.deptID | |
| LEFT JOIN department hd ON hd.deptID = su.hdl_deptID | |
| LEFT JOIN batches b ON sr.batchID = b.batchID | |
| LEFT JOIN semesters cs ON cs.semID =b.semID | |
| LEFT JOIN department d ON b.deptID = d.deptID | |
| LEFT JOIN semesters s ON sr.semID = s.semID | |
| WHERE su.subjectID = $subjectId AND su.subjectName!='$excludedSubject' ORDER BY sr.updatedDate DESC)"; | |
| try { | |
| return $this->executeQueryForObject($sql, false, $this->mapper[SBSServiceMapper::GET_FACULTIES_ASSIGNED_TO_SUBJECT]); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * @param $staffId | |
| * @param $subjectId | |
| * @return Object|Batch[] | |
| * @throws ProfessionalException | |
| * @author jithinvijayan | |
| */ | |
| public function getBatchesAssignedToSubjectByStaffId($staffId, $subjectId) | |
| { | |
| $sql = "SELECT sr.sbsID as sbs_id, sa.staffID AS staff_id,sa.staffName AS staff_name, | |
| b.batchID AS batch_id, b.batchName AS batch_name, | |
| cs.semID as current_semester_id,cs.semName as current_semester_name, | |
| d.deptID AS batch_department_id,d.deptName AS batch_department_name, | |
| sem.semID AS semester_id,sem.semName AS semester_name, | |
| sr.sbsID as sbs_id | |
| FROM sbs_relation sr | |
| INNER JOIN subjects s ON s.subjectID=sr.subjectID | |
| INNER JOIN staffaccounts sa ON sa.staffID = sr.staffID | |
| LEFT JOIN semesters sem ON sr.semID = sem.semID | |
| LEFT JOIN batches b ON b.batchID = sr.batchID | |
| LEFT JOIN department d ON b.deptID = d.deptID | |
| LEFT JOIN semesters cs ON cs.semID =b.semID | |
| WHERE sr.staffID = $staffId AND sr.subjectID = $subjectId"; | |
| try { | |
| return $this->executeQueryForList($sql, $this->mapper[SBSServiceMapper::GET_BATCHES_ASSIGNED_TO_SUBJECT_BY_FACULTY]); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * Assigning faculties to subjects and batches | |
| * | |
| * @param AssignFacultyToSubjectRequest $request | |
| * @throws ProfessionalException | |
| * @author jithinvijayan | |
| */ | |
| public function assignFacultyToSubjectsAndBatches(AssignFacultyToSubjectRequest $request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $departments = []; | |
| $semesters = []; | |
| try { | |
| if (empty($request->staffId)) { | |
| throw new ProfessionalException(ProfessionalException::INVALID_STAFF_ID, "Staff not found"); | |
| } | |
| if (empty($request->subjectId)) { | |
| throw new ProfessionalException(ProfessionalException::INVALID_SUBJECT_ID, "No subject selected to assign batches and faculty"); | |
| } | |
| if (empty($request->batches)) { | |
| throw new ProfessionalException(ProfessionalException::INVALID_BATCH_IDS, "No batches assigned to the given subject"); | |
| } | |
| /** | |
| * Assigning batches to the selected subjects and faculty | |
| */ | |
| $sql = "INSERT INTO sbs_relation ( staffID, batchID, subjectID, semID, isPseudosubject, createdBy, | |
| createdDate, updatedBy, updatedDate) | |
| VALUES "; | |
| foreach ($request->batches as $batch) { | |
| if (empty($batch->batchId)) { | |
| throw new ProfessionalException(ProfessionalException::INVALID_BATCH_ID, "Batch not assigned properly"); | |
| } | |
| if (empty($batch->semesterId)) { | |
| throw new ProfessionalException(ProfessionalException::INVALID_SEMESTER_ID, "Semester not assigned properly"); | |
| } | |
| $sql .= "($request->staffId,$batch->batchId,$request->subjectId,$batch->semesterId, | |
| $request->isPseudoSubject,$request->createdBy,UTC_TIMESTAMP(),$request->updatedBy,UTC_TIMESTAMP()),"; | |
| $departments[] = BatchService::getInstance()->getDepartmentByBatchId($batch->batchId); | |
| $semesters[] = $batch->semesterId; | |
| } | |
| $sql = rtrim($sql, ","); | |
| $this->executeQuery($sql); | |
| /** | |
| * Assign department to subjects if doesn't exists | |
| */ | |
| $assignDepartmentRequest = new AssignSubjectToDepartmentsRequest(); | |
| $assignDepartmentRequest->departmentIds = $departments; | |
| $assignDepartmentRequest->subjectId = $request->subjectId; | |
| $assignDepartmentRequest->updatedBy = $request->updatedBy; | |
| $assignDepartmentRequest->createdBy = $request->createdBy; | |
| SubjectDepartmentService::getInstance()->assignSubjectToDepartments($assignDepartmentRequest); | |
| /** | |
| * Assign semesters to subjects if doesn't exists | |
| */ | |
| $assignSemesterRequest = new AssignSubjectToSemestersRequest(); | |
| $assignSemesterRequest->subjectId = $request->subjectId; | |
| $assignSemesterRequest->semesterIds = $semesters; | |
| $assignSemesterRequest->updatedBy = $request->updatedBy; | |
| $assignSemesterRequest->createdBy = $request->createdBy; | |
| SubjectSemesterService::getInstance()->assignSubjectToSemesters($assignSemesterRequest); | |
| /** | |
| * Removing incomplete sbs assigning from the temporary table. | |
| */ | |
| $removeRequest = new RemoveIncompleteSBSRequest(); | |
| $removeRequest->assigningMethod = $request->assigningMethod; | |
| $removeRequest->subjectIds[] = $request->subjectId; | |
| $removeRequest->staffId = $request->staffId; | |
| $this->removeIncompleteSBSAssign($removeRequest); | |
| /** | |
| * Logging the success event | |
| */ | |
| AMSLogger::log_info($this->logger,Events::SBS_ASSIGNING, [ | |
| "userId" => $request->updatedBy, | |
| "userType" => UserType::ADMIN, | |
| "status" => StatusConstants::SUCCESS, | |
| "request" => $request | |
| ]); | |
| } catch (\Exception $e) { | |
| /** | |
| * Logging the error events | |
| */ | |
| AMSLogger::log_error($this->logger,Events::SBS_ASSIGNING, [ | |
| "userId" => $request->createdBy, | |
| "userType" => UserType::ADMIN, | |
| "request" => $request, | |
| "status" => StatusConstants::FAILED, | |
| "error" => $e->getCode(), | |
| "message" => $e->getMessage() | |
| ]); | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * Removing incomplete sbs assigning from the table | |
| * | |
| * @param RemoveIncompleteSBSRequest $request | |
| * @throws ProfessionalException | |
| * @author jithinvijayan | |
| */ | |
| private function removeIncompleteSBSAssign(RemoveIncompleteSBSRequest $request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| if (empty($request->staffId)) { | |
| throw new ProfessionalException(ProfessionalException::INVALID_STAFF_ID, "Invalid staff details given"); | |
| } | |
| if (empty($request->subjectIds)) { | |
| throw new ProfessionalException(ProfessionalException::NO_SUBJECTS_ASSIGNED, "Assigned subjects details not found"); | |
| } | |
| if (empty($request->assigningMethod)) { | |
| throw new ProfessionalException(ProfessionalException::INVALID_SBS_ASSIGNING_METHOD, "SBS Assigning method not defined"); | |
| } | |
| $sql = "DELETE FROM incomplete_sbs_relation | |
| WHERE assigned_method='$request->assigningMethod' AND staff_id = $request->staffId | |
| AND subject_id IN (" . implode(",", $request->subjectIds) . ")"; | |
| try { | |
| $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * @param CheckSbsAlreadyAssignedRequest $request | |
| * @return mixed | |
| * @throws ProfessionalException | |
| * @author jithinvijayan | |
| */ | |
| public function checkSBSAlreadyAssigned(CheckSbsAlreadyAssignedRequest $request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| if (empty($request->subjectId)) { | |
| throw new ProfessionalException(ProfessionalException::INVALID_SUBJECT_ID, "Subject details not found"); | |
| } | |
| if (empty($request->staffId)) { | |
| throw new ProfessionalException(ProfessionalException::INVALID_STAFF_ID, "Staff details not found"); | |
| } | |
| if (empty($request->semesterId)) { | |
| throw new ProfessionalException(ProfessionalException::INVALID_SEMESTER_ID, "Semester details not found"); | |
| } | |
| if (empty($request->batchId)) { | |
| throw new ProfessionalException(ProfessionalException::INVALID_BATCH_ID, "Batch details not found"); | |
| } | |
| $sql = "SELECT sbsID FROM sbs_relation | |
| WHERE semID = $request->semesterId AND batchID = $request->batchId AND subjectID=$request->subjectId | |
| AND staffID = $request->staffId"; | |
| try { | |
| return $this->executeQueryForObject($sql)->sbsID; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * @param $request | |
| * @throws ProfessionalException | |
| * @author jithinvijayan | |
| */ | |
| public function assignSubjectsToStaffTemporary(AssignSBSTemporaryRequest $request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| if (empty($request->assigningMethod)) { | |
| throw new ProfessionalException(ProfessionalException::INVALID_SBS_ASSIGNING_METHOD, "Invalid sbs assigning method"); | |
| } | |
| /** | |
| * Assign by staff method | |
| */ | |
| if ($request->assigningMethod === SBSAssigningConstants::ASSIGN_BY_FACULTY) { | |
| if (empty($request->facultyId)) { | |
| throw new ProfessionalException(ProfessionalException::INVALID_STAFF_ID, "Staff details not found"); | |
| } | |
| if (empty($request->assignedSubjects)) { | |
| throw new ProfessionalException(ProfessionalException::NO_SUBJECTS_ASSIGNED, "No subjects assigned to the selected faculty"); | |
| } | |
| $sql = "INSERT INTO incomplete_sbs_relation (staff_id, subject_id, assigned_method, | |
| created_by, created_date, updated_by, updated_date) VALUES "; | |
| foreach ($request->assignedSubjects as $subject) { | |
| $sql .= "( $request->facultyId,$subject,'$request->assigningMethod',$request->createdBy,UTC_TIMESTAMP(), | |
| $request->updatedBy,UTC_TIMESTAMP() ),"; | |
| } | |
| } else { | |
| /** | |
| * Assign by subject method | |
| */ | |
| if (empty($request->subjectId)) { | |
| throw new ProfessionalException(ProfessionalException::INVALID_SUBJECT_ID, "Subject details not found"); | |
| } | |
| if (empty($request->assignedStaffs)) { | |
| throw new ProfessionalException(ProfessionalException::NO_STAFFS_ASSIGNED, "Select at-least one staff to proceed"); | |
| } | |
| $sql = "INSERT INTO incomplete_sbs_relation (staff_id, subject_id, assigned_method, | |
| created_by, created_date, updated_by, updated_date) VALUES "; | |
| foreach ($request->assignedStaffs as $staff) { | |
| $sql .= "( $staff,$request->subjectId,'$request->assigningMethod',$request->createdBy,UTC_TIMESTAMP(), | |
| $request->updatedBy,UTC_TIMESTAMP() ),"; | |
| } | |
| } | |
| $sql = rtrim($sql, ","); | |
| try { | |
| $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * @param GetAllStaffsAssignedToSubjectRequest $request | |
| * @return Object|PseudoSubjectStaff[] | |
| * @throws ProfessionalException | |
| * @author Jithin Vijayan | |
| */ | |
| public function getAllStaffsAssignedToSubject(GetAllStaffsAssignedToSubjectRequest $request) | |
| { | |
| $sql = "SELECT sr.sbsID AS sbs_id, sa.staffID AS id,sa.staffName AS name,sa.staffCode AS code,d.deptID AS department_id, | |
| d.deptName AS department_name,b.batchID AS batch_id,b.batchName AS batch_name,s.semName AS semester_name | |
| FROM staffaccounts sa | |
| INNER JOIN department d ON sa.deptID = d.deptID | |
| INNER JOIN sbs_relation sr ON sa.staffID = sr.staffID | |
| INNER JOIN batches b ON b.batchID = sr.batchID | |
| INNER JOIN semesters s ON sr.semID = s.semID | |
| INNER JOIN subject_pseudo_subjects sps ON sps.subject_id = sr.subjectID | |
| INNER JOIN pseudosubjects p ON p.pseudosubjectID = sps.pseudo_subject_id | |
| LEFT JOIN pseudosubjects_sbs ps ON sr.sbsID = ps.sbsID AND ps.pseudosubjectID = p.pseudosubjectID | |
| WHERE p.pseudosubjectID = $request->pseudoSubjectId AND ps.pseudosubsbsID IS NULL | |
| AND sr.batchID IN (" . implode(",", $request->batchIds) . ") | |
| AND sr.semID IN (" . implode(",", $request->semesterIds) . ")"; | |
| try { | |
| return $this->executeQueryForList($sql, $this->mapper[SBSServiceMapper::GET_ALL_ASSIGNED_STAFF_TO_SUBJECT]); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * Assigning staff to pseudo subject | |
| * | |
| * @param AssignStaffToPseudoSubjectRequest $request | |
| * @throws ProfessionalException | |
| * @author Jithin Vijayan | |
| */ | |
| public function assignStaffToPseudoSubject(AssignStaffToPseudoSubjectRequest $request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| if (empty($request->pseudoSubjectId)) { | |
| throw new ProfessionalException(ProfessionalException::INVALID_PSEUDO_SUBJECT_ID, "Invalid pseudo subject details given"); | |
| } | |
| if (empty($request->sbsId)) { | |
| throw new ProfessionalException(ProfessionalException::NO_STAFFS_ASSIGNED, "No staffs assigned to the subject"); | |
| } | |
| $sql = "INSERT INTO pseudosubjects_sbs (pseudosubjectID, sbsID, created_by, created_date, updated_by, updated_date) | |
| VALUES ($request->pseudoSubjectId,$request->sbsId,$request->createdBy,UTC_TIMESTAMP(),$request->updatedBy, | |
| UTC_TIMESTAMP()) "; | |
| try { | |
| $this->executeQuery($sql); | |
| $this->markSBSAsPseudoSBS($request->sbsId, $request->updatedBy); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * Marking sbs as pseudo subject sbs | |
| * | |
| * @param $sbsId | |
| * @param $updatedBy | |
| * @throws ProfessionalException | |
| * @author Jithin Vijayan | |
| */ | |
| private function markSBSAsPseudoSBS($sbsId, $updatedBy) | |
| { | |
| $sql = "UPDATE sbs_relation | |
| SET isPseudosubject=1,updatedBy=$updatedBy,updatedDate =UTC_TIMESTAMP() | |
| WHERE sbsID=$sbsId"; | |
| try { | |
| $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * checking sbs already assigned to pseudo subject | |
| * | |
| * @param $pseudoSubjectId | |
| * @param $sbsId | |
| * @return Object | |
| * @throws ProfessionalException | |
| * @author Jithin Vijayan | |
| */ | |
| public function checkStaffAlreadyAssigned($pseudoSubjectId, $sbsId) | |
| { | |
| if (empty($pseudoSubjectId)) { | |
| throw new ProfessionalException(ProfessionalException::INVALID_PSEUDO_SUBJECT_ID, "Invalid pseudo subject details given"); | |
| } | |
| if (empty($sbsId)) { | |
| throw new ProfessionalException(ProfessionalException::NO_STAFFS_ASSIGNED, "No staffs assigned to the subject"); | |
| } | |
| $sql = "SELECT pseudosubsbsID as id FROM pseudosubjects_sbs WHERE sbsID =$sbsId AND pseudosubjectID = $pseudoSubjectId"; | |
| try { | |
| return $this->executeQueryForObject($sql)->id; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * Un-assigning staff from the pseudo subject | |
| * | |
| * @param UnAssignStaffFromPseudoSubjectRequest $request | |
| * @throws ProfessionalException | |
| * @author Jithin Vijayan | |
| */ | |
| public function unAssignStaffFromThePseudoSubject(UnAssignStaffFromPseudoSubjectRequest $request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| if (empty($request->pseudoSubjectId)) { | |
| throw new ProfessionalException(ProfessionalException::INVALID_PSEUDO_SUBJECT_ID, "Invalid pseudo subject details given"); | |
| } | |
| if (empty($request->sbsId)) { | |
| throw new ProfessionalException(ProfessionalException::NO_STAFFS_ASSIGNED, "No matching staff assignment found"); | |
| } | |
| $sql = "DELETE FROM pseudosubjects_sbs WHERE sbsID =$request->sbsId AND pseudosubjectID = $request->pseudoSubjectId"; | |
| try { | |
| $this->beginTransaction(); | |
| $this->executeQuery($sql); | |
| /** | |
| * Un marking subject as not pseudo subject if no other pseudo subjects mapped to the sbs | |
| */ | |
| if (!$this->getCountOfSBSAssignedForOtherPseudoSubjects($request->sbsId, $request->pseudoSubjectId)) { | |
| $this->unmarkSBSAsPseudoSBS($request->sbsId, $request->updatedBy); | |
| } | |
| /** | |
| * Deleting sub batch sbs | |
| */ | |
| $this->deleteSubBatchSBS($request->pseudoSubjectId, $request->sbsId); | |
| $finaliseRequest = new MarkOrUnmarkIsFinalisedPseudoSubjectRequest(); | |
| $finaliseRequest->pseudoSubjectId = $request->pseudoSubjectId; | |
| $finaliseRequest->updatedBy = $request->updatedBy; | |
| $finaliseRequest->isFinalised = 0; | |
| /** | |
| * reset the finalised flag | |
| */ | |
| PseudoSubjectService::getInstance()->markOrUnmarkPseudoSubjectFinalized($finaliseRequest); | |
| $this->commit(); | |
| } catch (\Exception $e) { | |
| $this->rollBack(); | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * @param $sbsId | |
| * @param $pseudoSubjectId | |
| * @return mixed|integer | |
| * @throws ProfessionalException | |
| */ | |
| public function getCountOfSBSAssignedForOtherPseudoSubjects($sbsId, $pseudoSubjectId) | |
| { | |
| $sql = "SELECT COUNT(DISTINCT ps.pseudosubsbsID) as totalRecords | |
| FROM pseudosubjects_sbs ps | |
| INNER JOIN sbs_relation sr on ps.sbsID = sr.sbsID | |
| WHERE pseudosubjectID !=$pseudoSubjectId AND ps.sbsID = $sbsId"; | |
| try { | |
| return $this->executeQueryForObject($sql)->totalRecords; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * Marking sbs as not pseudo subject sbs | |
| * | |
| * @param $sbsId | |
| * @param $updatedBy | |
| * @throws ProfessionalException | |
| * @author Jithin Vijayan | |
| */ | |
| private function unmarkSBSAsPseudoSBS($sbsId, $updatedBy) | |
| { | |
| $sql = "UPDATE sbs_relation | |
| SET isPseudosubject=0,updatedBy=$updatedBy,updatedDate =UTC_TIMESTAMP() | |
| WHERE sbsID=$sbsId"; | |
| try { | |
| $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * Deleting subbatch sbs for the pseudo subject | |
| * | |
| * @param $pseudoSubjectId | |
| * @param $sbsId | |
| * @throws ProfessionalException | |
| * @author Jithin Vijayan | |
| */ | |
| private function deleteSubBatchSBS($pseudoSubjectId, $sbsId) | |
| { | |
| $sql = "DELETE ss.* FROM subbatch_sbs ss | |
| INNER JOIN subbatches s on ss.subbatchID = s.subbatchID | |
| WHERE s.psID =$pseudoSubjectId AND ss.sbsID =$sbsId"; | |
| try { | |
| $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * Return distinct batches of assigned staffs in a pseudo subjects | |
| * | |
| * @param $pseudoSubjectId | |
| * @return Object|Object[] | |
| * @throws ProfessionalException | |
| * @author: jithinvijayan | |
| */ | |
| public function getPseudoSubjectStaffBatches($pseudoSubjectId) | |
| { | |
| $pseudoSubjectId = $this->realEscapeString($pseudoSubjectId); | |
| if (empty($pseudoSubjectId)) { | |
| throw new ProfessionalException(ProfessionalException::INVALID_PSEUDO_SUBJECT_ID, "Invalid pseudo subject details given"); | |
| } | |
| $sql = "SELECT DISTINCT b.batchID as batchId,b.batchName FROM pseudosubjects ps | |
| INNER JOIN pseudosubjects_sbs pss on ps.pseudosubjectID = pss.pseudosubjectID | |
| INNER JOIN sbs_relation sr on pss.sbsID = sr.sbsID | |
| INNER JOIN batches b on sr.batchID = b.batchID | |
| WHERE ps.pseudosubjectID = $pseudoSubjectId"; | |
| try { | |
| return $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * @param $sbsId | |
| * @param $subBatchId | |
| * @return mixed | |
| * @throws ProfessionalException | |
| * @author jithinvijayan | |
| */ | |
| public function checkSBSAssignedForSubBatch($subBatchId, $sbsId) | |
| { | |
| if (empty($subBatchId)) { | |
| throw new ProfessionalException(ProfessionalException::INVALID_REQUEST, "Invalid sub-batch details given"); | |
| } | |
| if (empty($sbsId)) { | |
| throw new ProfessionalException(ProfessionalException::INVALID_SBS_ID, "Invalid sbs details given"); | |
| } | |
| $sql = "SELECT subbatchID as id FROM subbatch_sbs ssbs | |
| INNER JOIN sbs_relation sr ON sr.sbsID = ssbs.sbsID | |
| WHERE sr.sbsID = " . $sbsId . " AND subbatchID = " . $subBatchId; | |
| try { | |
| return (int)$this->executeQueryForObject($sql)->id; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * @param CreateSubBatchSBSRequest $request | |
| * @return Object | |
| * @throws ProfessionalException | |
| * @author jithinvijayan | |
| */ | |
| public function createSubBatchSBS(CreateSubBatchSBSRequest $request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| if (empty($request->subBatchId)) { | |
| throw new ProfessionalException(ProfessionalException::INVALID_SUB_BATCH_ID, "Invalid sub-batch details given"); | |
| } | |
| if (empty($request->sbsId)) { | |
| throw new ProfessionalException(ProfessionalException::INVALID_SBS_ID, "Invalid subject-batch-staff relation given"); | |
| } | |
| $sql = "INSERT INTO subbatch_sbs(subbatchID,sbsID,created_by,created_date,updated_by,updated_date) | |
| VALUES (" . $request->subBatchId . ", " . $request->sbsId . ",$request->createdBy,UTC_TIMESTAMP(), | |
| $request->updatedBy,UTC_TIMESTAMP())"; | |
| try { | |
| return $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * @param $staffId | |
| * @param null|integer $excludedBatchId | |
| * @return Object|array | |
| * @throws ProfessionalException | |
| */ | |
| public function getStaffTeachingBatches($staffId, $excludedBatchId = null) | |
| { | |
| $staffId = (int)$this->realEscapeString($staffId); | |
| if (empty($staffId)) { | |
| throw new ProfessionalException(ProfessionalException::INVALID_STAFF_ID, "Invalid staff details given"); | |
| } | |
| $sql = "SELECT DISTINCT b.batchID as id,b.batchName as name | |
| FROM sbs_relation sr | |
| INNER JOIN batches b ON b.batchID = sr.batchID | |
| WHERE sr.staffID =$staffId "; | |
| if (!empty($excludedBatchId)) { | |
| $sql .= " AND sr.batchID!=$excludedBatchId "; | |
| } | |
| $sql .= " ORDER BY b.batchStartYear"; | |
| try { | |
| return $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * @param $staffId | |
| * @param null $batchId | |
| * @return Object | |
| * @throws ProfessionalException | |
| */ | |
| public function getStaffTeachingSemesters($staffId, $batchId = null) | |
| { | |
| $staffId = (int)$this->realEscapeString($staffId); | |
| $batchId = (int)$this->realEscapeString($batchId); | |
| if (empty($staffId)) { | |
| throw new ProfessionalException(ProfessionalException::INVALID_STAFF_ID, "Invalid staff details given"); | |
| } | |
| $sql = "SELECT DISTINCT s.semID as id,s.semName as name | |
| FROM sbs_relation sr | |
| INNER JOIN semesters s on s.semID = sr.semID | |
| WHERE sr.staffID =$staffId "; | |
| if (!empty($batchId)) { | |
| $sql .= " AND sr.batchID =$batchId"; | |
| } | |
| $sql .= " ORDER BY s.orderNo"; | |
| try { | |
| return $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * @param $staffId | |
| * @param null $batchId | |
| * @param null $semesterId | |
| * @return Object|array | |
| * @throws ProfessionalException | |
| */ | |
| public function getStaffTeachingSubjects(GetStaffTeachingSubjectsRequest $request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| if (empty($request->staffId)) { | |
| throw new ProfessionalException(ProfessionalException::INVALID_STAFF_ID, "Invalid staff details given"); | |
| } | |
| $sql = "SELECT DISTINCT s.subjectID as id,s.subjectName as name | |
| FROM sbs_relation sr | |
| INNER JOIN subjects s on sr.subjectID = s.subjectID | |
| WHERE sr.staffID =$request->staffId "; | |
| if (!empty($request->batchId)) { | |
| $sql .= " AND sr.batchID =$request->batchId "; | |
| } | |
| if (!empty($request->semesterId)) { | |
| $sql .= " AND sr.semID =$request->semesterId "; | |
| } | |
| if (!empty($request->excludedSubjectId)) { | |
| $sql .= " AND sr.subjectID !=$request->excludedSubjectId "; | |
| } | |
| $sql .= " ORDER BY s.subjectName "; | |
| try { | |
| return $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * @param $sbsId | |
| * @return Object | |
| * @throws ProfessionalException | |
| */ | |
| public function getSBSDetailsBySBSId($sbsId) | |
| { | |
| $sbsId = (int)$this->realEscapeString($sbsId); | |
| if (empty($sbsId)) { | |
| throw new ProfessionalException(ProfessionalException::INVALID_SBS_ID, "Invalid sbs details given"); | |
| } | |
| $sql = "SELECT batchID as batchId,subjectID as subjectId,semID as semesterId, staffID as staffId | |
| FROM sbs_relation WHERE sbsID = $sbsId"; | |
| try { | |
| return $this->executeQueryForobject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * @param unknown $courseTypeId | |
| * @param unknown $admsnStartYear | |
| * @param unknown $semId | |
| * @return object|array|\com\linways\base\util\$objectList[] | |
| * @throws ProfessionalException | |
| */ | |
| public function getSubjectsByBatchAndSem($batchId, $semId) | |
| { | |
| $batchId = $this->realEscapeString($batchId); | |
| $semId = $this->realEscapeString($semId); | |
| $batchSubjects = []; | |
| $sql = "SELECT subjectID | |
| from sbs_relation sr | |
| where sr.batchID='$batchId' | |
| AND sr.semID='$semId'"; | |
| try { | |
| $batchSubjects = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException ($e->getCode(), $e->getMessage()); | |
| } | |
| return $batchSubjects; | |
| } | |
| /** | |
| * Get all current sbsID details by studentId | |
| * @param $request | |
| * @return Array | |
| * @throw ProfessionalException | |
| */ | |
| public function getAllSbsIdsByStudentId($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $condition = null; | |
| $condition .= $request->studentId?" AND st.studentID = ".$request->studentId:null; | |
| $condition .= $request->sbsId?" AND sbs.sbsID IN (".implode(',',$request->sbsId).") ":null; | |
| if(!$condition) | |
| { | |
| throw new ProfessionalException(ProfessionalException::INVALID_REQUEST, "Invalid request details given"); | |
| } | |
| try { | |
| $sql = "SELECT sbs.sbsID,st.studentID,st.studentName,sbs.batchID,bat.deptID,sbs.semID,sta.staffID,sta.staffName,sub.subjectID, | |
| CASE | |
| WHEN pss.pseudosubjectID THEN pss.subjectName ELSE concat('(',sub.subjectName,') - ',sub.subjectDesc) | |
| END AS subjectName | |
| ,sb.subbatchID,pss.pseudosubjectID FROM sbs_relation sbs | |
| INNER JOIN batches bat ON bat.batchID = sbs.batchID AND sbs.semID = bat.semID | |
| INNER JOIN studentaccount st ON st.batchID = bat.batchID | |
| INNER JOIN staffaccounts sta ON sta.staffID = sbs.staffID | |
| INNER JOIN subjects sub ON sub.subjectID = sbs.subjectID | |
| LEFT JOIN subbatch_sbs ssbs ON sbs.sbsID = ssbs.sbsID | |
| LEFT JOIN subbatches sb ON sb.subbatchID = ssbs.subbatchID AND sb.batchID = sbs.batchID AND sb.semID = sbs.semID | |
| LEFT JOIN subbatch_student sst ON sst.subbatchID = sb.subbatchID AND sst.studentID = st.studentID | |
| LEFT JOIN pseudosubjects pss ON pss.pseudosubjectID = sb.psID AND pss.isFinalized = 1 | |
| WHERE ((ssbs.subbatchID IS NULL AND sst.subbatchID IS NULL) | |
| OR (ssbs.subbatchID IS NOT NULL AND sst.subbatchID IS NOT NULL AND (sb.psID = 0 or sb.psID is null)) | |
| OR (ssbs.subbatchID IS NOT NULL AND sst.subbatchID IS NOT NULL AND pss.pseudosubjectID IS NOT NULL) | |
| ) $condition ;"; | |
| return $this->executeQueryForList($sql); | |
| } catch (\Throwable $th) { | |
| throw new ProfessionalException ($th->getCode(), $th->getMessage()); | |
| } | |
| } | |
| /** | |
| * @param unknown $courseTypeId | |
| * @param unknown $admsnStartYear | |
| * @param unknown $semId | |
| * @return object|array|\com\linways\base\util\$objectList[] | |
| * @throws ProfessionalException | |
| */ | |
| public function getStaffByCourseTypeAndAdmissionYearAndSem($courseTypeId, $admissionYear,$semId) | |
| { | |
| $courseTypeId = $this->realEscapeString($courseTypeId); | |
| $admissionYear = $this->realEscapeString($admissionYear); | |
| $semId = $this->realEscapeString($semId); | |
| $staffAccounts = []; | |
| $sql = "SELECT distinct(sbs.staffID),sa.staffName,sa.staffPhone from sbs_relation sbs | |
| inner join batches b | |
| on b.batchID=sbs.batchID | |
| inner join staffaccounts sa | |
| on sa.staffID=sbs.staffID | |
| where b.courseTypeID='$courseTypeId' | |
| AND b.batchStartYear='$admissionYear' | |
| and sbs.semID='$semId' | |
| order by sbs.staffID"; | |
| try { | |
| $staffAccounts = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException ($e->getCode(), $e->getMessage()); | |
| } | |
| return $staffAccounts; | |
| } | |
| /** | |
| * @param unknown $courseTypeId | |
| * @param unknown $admsnStartYear | |
| * @param unknown $semId | |
| * @return object|array|\com\linways\base\util\$objectList[] | |
| * @throws ProfessionalException | |
| */ | |
| public function getStaffByBatchAndSem($batchId,$semId) | |
| { | |
| $batchId = $this->realEscapeString($batchId); | |
| $semId = $this->realEscapeString($semId); | |
| $staffAccounts = []; | |
| $sql = "SELECT distinct(sbs.staffID),sa.staffName,sa.staffPhone | |
| from sbs_relation sbs | |
| inner join staffaccounts sa | |
| on sa.staffID=sbs.staffID | |
| where sbs.batchID='$batchId' | |
| and sbs.semID='$semId' | |
| order by sbs.staffID"; | |
| try { | |
| $staffAccounts = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException ($e->getCode(), $e->getMessage()); | |
| } | |
| return $staffAccounts; | |
| } | |
| /** | |
| * @param unknown $sqlvaluestring | |
| * @throws ProfessionalException | |
| */ | |
| public function createSmsLogSessionalMarkSettings($sqlvaluestring) | |
| { | |
| // $semId = $this->realEscapeString($semId); | |
| // $examTypeId = $this->realEscapeString($examTypeId); | |
| // $lastDate = $this->realEscapeString($lastDate); | |
| $sql = "INSERT into sessional_mark_settings_smslog (staffId,semId,examTypeId,lastDate) | |
| values $sqlvaluestring"; | |
| try { | |
| $this->executeQueryForObject($sql,true); | |
| $result=true; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException ($e->getCode(), $e->getMessage()); | |
| } | |
| return $result; | |
| } | |
| /** | |
| * @param unknown $staffId | |
| * @param unknown $examTypeId | |
| * @param unknown $semId | |
| * @param unknown $lastDate | |
| * @return object|array|\com\linways\base\util\$objectList[] | |
| * @throws ProfessionalException | |
| */ | |
| public function getSmsLogSessionalMarkSettings($staffId,$semId,$examTypeId,$lastDate) | |
| { | |
| $staffId = $this->realEscapeString($staffId); | |
| $semId = $this->realEscapeString($semId); | |
| $examTypeId = $this->realEscapeString($examTypeId); | |
| $lastDate = $this->realEscapeString($lastDate); | |
| $result=null; | |
| $sql = "SELECT sms.id, | |
| sms.staffId, | |
| sms.semId, | |
| sms.examTypeId, | |
| sms.lastDate from sessional_mark_settings_smslog sms | |
| where sms.staffId='$staffId' | |
| and sms.semId='$semId' | |
| and sms.examTypeId='$examTypeId' | |
| and sms.lastDate='$lastDate'"; | |
| try { | |
| $result= $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException ($e->getCode(), $e->getMessage()); | |
| } | |
| return $result; | |
| } | |
| public function getSubbatchIdOrPseudoSubjectIdBySbsId($sbsId) | |
| { | |
| $sbsId = $this->realEscapeString($sbsId); | |
| $sql = "select sbs.sbsID,sbs.batchID,sbs.semID,sbs.subjectID,sbs.staffID, | |
| case | |
| when psbs.sbsID is not null then 'pseudoSubjects' | |
| when ssbs.sbsID is not null then 'subBatch' | |
| else 'allBatch' | |
| End as subjectType, | |
| case | |
| when psbs.sbsID is not null then psbs.pseudosubjectID | |
| when ssbs.sbsID is not null then group_concat(ssbs.subbatchID) | |
| else null | |
| End as ids, | |
| case | |
| when sbs.semID = bat.semID is not null then 'CurrentBatch' | |
| else 'previousBatch' | |
| End as type | |
| from sbs_relation sbs | |
| inner join batches bat on bat.batchID = sbs.batchID | |
| left join subbatch_sbs ssbs on ssbs.sbsID = sbs.sbsID | |
| left join pseudosubjects_sbs psbs on psbs.sbsID = sbs.sbsID | |
| where sbs.sbsID = $sbsId | |
| group by sbs.sbsID;"; | |
| try { | |
| return $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException ($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * @param batchID,subjectID,semID | |
| * @throws ProfessionalException | |
| */ | |
| public function getSBSbyBatchSubjectSem($subjectId, $batchId, $semId) | |
| { | |
| $semId = $this->realEscapeString($semId); | |
| $subjectId = $this->realEscapeString($subjectId); | |
| $batchId = $this->realEscapeString($batchId); | |
| $staffId = $this->realEscapeString($staffId); | |
| $result=""; | |
| $sql = "SELECT sbsID,staffID from sbs_relation where subjectID='$subjectId' and batchID IN ($batchId) and semID='$semId'"; | |
| try { | |
| $result =$this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $result; | |
| } | |
| /** | |
| * @param batchID,subjectID,semID | |
| * @throws ProfessionalException | |
| */ | |
| public function getSubjectBySBS($staffId, $batchId, $semId) | |
| { | |
| $semId = $this->realEscapeString($semId); | |
| $staffId = $this->realEscapeString($staffId); | |
| $batchId = $this->realEscapeString($batchId); | |
| $result = ""; | |
| $sql = "SELECT sbs.subjectID,s.subjectDesc,s.subjectName from sbs_relation sbs | |
| INNER JOIN subjects s ON s.subjectID = sbs.subjectID | |
| where sbs.staffID='$staffId' and sbs.batchID='$batchId' and sbs.semID='$semId'"; | |
| try { | |
| $result = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $result; | |
| } | |
| /** | |
| * @param $courseTypeId | |
| * @param $admsnStartYear | |
| * @param $semId | |
| * @param $staffId | |
| * @return object|array|\com\linways\base\util\$objectList[] | |
| * @throws ProfessionalException | |
| */ | |
| public function getStaffSubjectsByCourseTypeAndAdmissionYearAndSem($courseTypeId, $admissionYear, $semId, $staffId) | |
| { | |
| $courseTypeId = $this->realEscapeString($courseTypeId); | |
| $admissionYear = $this->realEscapeString($admissionYear); | |
| $semId = $this->realEscapeString($semId); | |
| $staffId = $this->realEscapeString($staffId); | |
| $staffSubjects = []; | |
| $sql = "SELECT distinct sbs.subjectID,s.subjectDesc,s.subjectName from sbs_relation sbs | |
| inner join batches b | |
| on b.batchID=sbs.batchID | |
| inner join staffaccounts sa | |
| on sa.staffID=sbs.staffID | |
| inner join subjects s | |
| on s.subjectID = sbs.subjectID | |
| where b.courseTypeID='$courseTypeId' | |
| AND b.batchStartYear='$admissionYear' | |
| and sbs.semID='$semId' | |
| and sbs.staffID='$staffId'"; | |
| try { | |
| $staffSubjects = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $staffSubjects; | |
| } | |
| /** | |
| * | |
| * @param Int $subjectId | |
| * @param Array $departmentId | |
| * @throws ProfessionalException | |
| */ | |
| public function getAllStaffWithASubject($subjectId,$departmentId) | |
| { | |
| $subjectId = $this->realEscapeString($subjectId); | |
| $departmentId = $this->realEscapeArray($departmentId); | |
| $result = ""; | |
| $sql = "SELECT st.staffID AS id,st.staffName AS name, st.staffCode AS code,st.deptID AS departmentId,d.deptName as departmentName | |
| FROM staffaccounts st | |
| INNER JOIN department d ON d.deptID = st.deptID | |
| LEFT JOIN sbs_relation sbs ON sbs.staffID = st.staffID ".($subjectId ? " AND sbs.subjectID = '$subjectId' ":"")." | |
| WHERE st.isResigned = 0 ".(count($departmentId) && $departmentId ? " AND st.deptID in (".implode(',',$departmentId).") ":"")." | |
| GROUP BY st.staffID | |
| ORDER BY sbs.sbsID DESC;"; | |
| try { | |
| return $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $result; | |
| } | |
| public function getSBSbyBatchSubjectSemAndStaffs($subjectId, $batchId, $semId, $staffId) | |
| { | |
| $semId = $this->realEscapeString($semId); | |
| $subjectId = $this->realEscapeString($subjectId); | |
| $batchId = $this->realEscapeString($batchId); | |
| $staffId = $this->realEscapeString($staffId); | |
| $result=""; | |
| $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 FROM sbs_relation sbs | |
| 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 sbs.subjectID='$subjectId' AND sbs.batchID='$batchId' AND sbs.semID='$semId' AND sbs.staffID='$staffId'; "; | |
| try { | |
| $result =$this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $result; | |
| } | |
| public function getAllSBS($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $where = []; | |
| $request->staffId?$where[] = " sbs.staffID = '$request->staffId' ":""; | |
| $request->batchId?$where[] = " sbs.batchID = '$request->batchId' ":""; | |
| $request->sbsIds?$where[] = " sbs.sbsID in (".implode(',',$request->sbsIds).") ":""; | |
| $request->sbsId?$where[] = " sbs.sbsID = '$request->sbsId' ":""; | |
| $request->semId?$where[] = " sbs.semID = '$request->semId' ":" sbs.semID = b.semID "; | |
| $request->subjectId?$where[] = " sbs.subjectID = '$request->subjectId' ":""; | |
| $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 FROM sbs_relation sbs | |
| 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 ? " WHERE " . implode(' AND ', $where) : "") . "; "; | |
| try { | |
| return $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| public function getStudentsOfSBSFromSubjectAndStaff($subjectID) | |
| { | |
| $subjectID = $this->realEscapeString($subjectID); | |
| $sql = "SELECT | |
| b.batchID , | |
| b.batchName, | |
| GROUP_CONCAT(s.studentID) as studentIds, | |
| sr.semID, | |
| s2.staffName | |
| from | |
| sbs_relation sr | |
| inner join batches b on | |
| b.batchID = sr.batchID | |
| inner join studentaccount s on s.batchID = b.batchID | |
| inner join staffaccounts s2 on s2.staffID = sr.staffID | |
| WHERE | |
| sr.subjectID = '$subjectID' | |
| group by b.batchID"; | |
| try { | |
| return $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| } |