Code Coverage |
||||||||||
Classes and Traits |
Functions and Methods |
Lines |
||||||||
| Total | |
0.00% |
0 / 1 |
|
0.00% |
0 / 68 |
CRAP | |
0.00% |
0 / 1260 |
| SubjectPlanService | |
0.00% |
0 / 1 |
|
0.00% |
0 / 68 |
49506.00 | |
0.00% |
0 / 1260 |
| __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 |
|||
| getActualPlanReport | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 64 |
|||
| getSubjectActualPlanReport | |
0.00% |
0 / 1 |
42.00 | |
0.00% |
0 / 27 |
|||
| getPseudoSubjectActualPlanReport | |
0.00% |
0 / 1 |
42.00 | |
0.00% |
0 / 36 |
|||
| addSubjectPlanPopupPreference | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 30 |
|||
| updateSubjectPlanPopupPreference | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 19 |
|||
| getSubjectPlanPopupPreference | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 10 |
|||
| checkPopupHide | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 10 |
|||
| createModeOfInstruction | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 8 |
|||
| updateModeOfInstruction | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 8 |
|||
| getDefaultModeOfInstructionByBatch | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 9 |
|||
| getSubjectPlan | |
0.00% |
0 / 1 |
30.00 | |
0.00% |
0 / 22 |
|||
| subjectPlanApprovelStatusBySbsId | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 15 |
|||
| getWeeKWiseSubjectPlanDetails | |
0.00% |
0 / 1 |
42.00 | |
0.00% |
0 / 57 |
|||
| createActualplanCousreMat | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 20 |
|||
| getActualplanCousreMat | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 21 |
|||
| removeActualplanCousreMat | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 8 |
|||
| createProposedplanCousreMat | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 20 |
|||
| removeProposedplanCousreMat | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 8 |
|||
| getProposedplanCousreMat | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 21 |
|||
| getSubjectPlannerDetails | |
0.00% |
0 / 1 |
30.00 | |
0.00% |
0 / 47 |
|||
| getActualPlannerDetails | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 11 |
|||
| getProposedSubjectPlanDetailsBySbsId | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 11 |
|||
| getActualSubjectPlanDetailsBySbsId | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 16 |
|||
| addProposedSubjectPlanToCommunityBySbsId | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 17 |
|||
| getProposedSubjectPlanFromCommunityBySbsId | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 11 |
|||
| changeSharingProposedSubjectPlanInCommunityBySbsId | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 15 |
|||
| getProposedSubjectPlanFromCommunityBySubject | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 11 |
|||
| getProposedSubjectPlanByIds | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 10 |
|||
| addProposedSubjectPlan | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 32 |
|||
| addProposedPlanCopyInfo | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 13 |
|||
| getProposedPlanCopyInfoBySbsId | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 12 |
|||
| getProposedSubjectPlanById | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 11 |
|||
| getActualSubjectPlansCovered | |
0.00% |
0 / 1 |
42.00 | |
0.00% |
0 / 25 |
|||
| getProposedSubjectPlanBySbsID | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 10 |
|||
| deleteCourseMaterialFromActualPlan | |
0.00% |
0 / 1 |
30.00 | |
0.00% |
0 / 17 |
|||
| deleteCourseMaterialFromActualPlanByMaterialId | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 13 |
|||
| deleteCourseMaterialFromProposesPlan | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 17 |
|||
| deleteCourseMaterialFromProposesPlanMaterialId | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 13 |
|||
| getProposedPlanCourseMaterials | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 21 |
|||
| getActualPlanCourseMaterials | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 21 |
|||
| addNewActualPlanVideoMaterial | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 18 |
|||
| addNewProposedPlanVideoMaterial | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 18 |
|||
| getActualPlanVideoMaterials | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 16 |
|||
| getProposedPlanVideoMaterials | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 16 |
|||
| deleteActualPlan | |
0.00% |
0 / 1 |
42.00 | |
0.00% |
0 / 28 |
|||
| deleteAllActualPlanCourseMaterialsRelation | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 8 |
|||
| deleteAllActualPlanVideoMaterialsRelation | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 8 |
|||
| getAllActualPlanVideoMaterials | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 10 |
|||
| deleteProposedPlan | |
0.00% |
0 / 1 |
42.00 | |
0.00% |
0 / 29 |
|||
| deleteAllProposedPlanCourseMaterialsRelation | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 8 |
|||
| deleteAllProposedPlanVideoMaterialsRelation | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 8 |
|||
| removeProposedPlanFromActualPlan | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 9 |
|||
| getAllProposedPlanVideoMaterials | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 14 |
|||
| getAllProposedPlanCourseMaterialIds | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 13 |
|||
| getAllActualPlanCourseMaterialIds | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 9 |
|||
| getSubjectProposedPlanByRequest | |
0.00% |
0 / 1 |
132.00 | |
0.00% |
0 / 23 |
|||
| getSubjectPlansByRequest | |
0.00% |
0 / 1 |
182.00 | |
0.00% |
0 / 45 |
|||
| getSubjectPlanDurationBySbsIdAndDate | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 27 |
|||
| updateOnlineExamMappingProposedPlan | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 10 |
|||
| updateOnlineExamMappingProposedPlanPs | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 10 |
|||
| getOnlineExamMappingProposedPlan | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 11 |
|||
| getOnlineExamMappingProposedPlanPs | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 11 |
|||
| getSubjectPlanDetails | |
0.00% |
0 / 1 |
90.00 | |
0.00% |
0 / 74 |
|||
| getSubjectActualPlanReportMoreDetails | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 37 |
|||
| getProposedSubjectPlanFromCommunityByDiferentSubject | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 23 |
|||
| <?php | |
| namespace com\linways\core\ams\professional\service; | |
| use com\linways\core\ams\professional\exception\ProfessionalException; | |
| use com\linways\core\ams\professional\dto\SubjectPlan; | |
| use com\linways\core\ams\professional\mapper\SubjectPlanServiceMapper; | |
| use com\linways\core\ams\professional\dto\SubjectPlanPopupPreference; | |
| use com\linways\core\ams\professional\request\ActualPlanRequest; | |
| use com\linways\core\ams\professional\request\AddNewActualPlanMaterialRequest; | |
| use com\linways\core\ams\professional\request\AddNewProposedPlanMaterialRequest; | |
| use com\linways\core\ams\professional\request\DeleteActualPlanRequest; | |
| use com\linways\core\ams\professional\request\DeleteCourseMaterialRequest; | |
| use com\linways\core\ams\professional\request\DeleteProposedPlanRequest; | |
| use com\linways\core\ams\professional\request\GetPreSignedUrlRequest; | |
| use com\linways\core\ams\professional\response\ActualPlanResponse; | |
| use com\linways\core\ams\professional\dto\SubjectPlanPreference; | |
| use com\linways\core\ams\professional\request\SubjectPlanRequest; | |
| use stdClass; | |
| class SubjectPlanService extends BaseService | |
| { | |
| /** | |
| * Presence of a static member variable | |
| * | |
| * @var null | |
| */ | |
| private static $_instance = null; | |
| /** | |
| * @var array | |
| */ | |
| private $mapper = []; | |
| /** | |
| * Locked down the constructor | |
| * | |
| * SubjectPlanService constructor. | |
| */ | |
| private function __construct() | |
| { | |
| $this->mapper = SubjectPlanServiceMapper::getInstance()->getMapper(); | |
| } | |
| /** | |
| * Prevent any object or instance of that class to be cloned or Prevent any outside instantiation of this class | |
| */ | |
| private function __clone() | |
| { | |
| } | |
| /** | |
| * Have a single globally accessible static method | |
| * | |
| * @return SubjectPlanService|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 actual plan report | |
| * @return SubjectPlan $subjectPlans | |
| * @throws ProfessionalException | |
| */ | |
| public function getActualPlanReport($batchId, $semId) | |
| { | |
| $subjectPlans = []; | |
| $sql = "SELECT DISTINCT | |
| topicID, | |
| topicName, | |
| session, | |
| FROM_UNIXTIME(sbp.date, '%Y-%m-%d') AS subjectPlanDate, | |
| sbp.hour, | |
| staffName, | |
| subjectName, | |
| sbsr.sbsID, | |
| sub.subjectID, | |
| sbp.isCovered, | |
| (SELECT | |
| (CASE | |
| WHEN COUNT(DISTINCT a.studentID) > 0 THEN 1 | |
| ELSE 0 | |
| END) | |
| FROM | |
| attendance a | |
| WHERE | |
| a.attendanceDate = FROM_UNIXTIME(sbp.date, '%Y-%m-%d') | |
| AND a.hour = sbp.hour | |
| AND a.batchID = $batchId | |
| AND a.semId = $semId) AS isAttendanceMarked, | |
| (SELECT | |
| COUNT(DISTINCT a.studentID) | |
| FROM | |
| attendance a | |
| WHERE | |
| a.attendanceDate = FROM_UNIXTIME(sbp.date, '%Y-%m-%d') | |
| AND a.hour = sbp.hour | |
| AND a.batchID = $batchId | |
| AND a.semId = $semId | |
| AND a.isAbsent = 1) AS absenteesCount, | |
| (SELECT | |
| GROUP_CONCAT(DISTINCT sa.rollNo) | |
| FROM | |
| attendance a | |
| INNER JOIN | |
| studentaccount sa ON sa.studentID = a.studentID | |
| WHERE | |
| a.attendanceDate = FROM_UNIXTIME(sbp.date, '%Y-%m-%d') | |
| AND a.hour = sbp.hour | |
| AND a.batchID = $batchId | |
| AND a.semId = $semId | |
| AND a.isAbsent = 1) AS absentees | |
| FROM | |
| sbs_relation sbsr | |
| INNER JOIN | |
| subjectplan sbp ON sbp.sbsID = sbsr.sbsID | |
| INNER JOIN | |
| staffaccounts sa ON sbsr.staffID = sa.staffID | |
| INNER JOIN | |
| subjects sub ON sbsr.subjectID = sub.subjectID | |
| WHERE | |
| sbsr.batchID = $batchId AND sbsr.semID = $semId"; | |
| try { | |
| $subjectPlans = $this->executeQueryForList($sql, $this->mapper[SubjectPlanServiceMapper::GET_ACTUAL_PLAN_REPORT]); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $subjectPlans; | |
| } | |
| /** | |
| * get actual plan report by subject | |
| * @param ActualPlanRequest $actualPlanRequest | |
| * @return object|array|\com\linways\base\util\$objectList[] | |
| * @throws ProfessionalException | |
| */ | |
| public function getSubjectActualPlanReport($actualPlanRequest) | |
| { | |
| $actualPlans = new ActualPlanResponse(); | |
| $cond = ""; | |
| if ($actualPlanRequest != NULL) { | |
| $sql_count = "SELECT count(sp.date) as totalRecord FROM subjectplan as sp WHERE sbsID = $actualPlanRequest->sbsId "; | |
| $sql = "SELECT sp.date, sp.hour, sp.module, sp.topicName, sp.isCovered, sp.day, sp.session, spr.pedagogy, sp.modeOfeLearning, spr.mode, sp.mode as modeActual,spr.content,spr.units FROM subjectplan sp LEFT JOIN subjectplan_proposed spr ON sp.proposed_topicID = spr.topicID WHERE sp.sbsID = $actualPlanRequest->sbsId "; | |
| if ($actualPlanRequest->subbatchId != null) { | |
| $cond = "AND sp.subbatchID=$actualPlanRequest->subbatchId"; | |
| } | |
| $sql_count .= $cond; | |
| $sql .= $cond; | |
| if ($actualPlanRequest->sortOrder) { | |
| $sql .= " ORDER BY sp.date $actualPlanRequest->sortOrder, sp.hour, sp.session "; | |
| } | |
| try { | |
| $actualPlans->totalRecords = $this->executeQueryForObject($sql_count)->totalRecord; | |
| if ($actualPlanRequest->export) { | |
| $actualPlanRequest->endIndex = $studentDetails->totalRecords; | |
| } | |
| $sql .= $sql_cond . "LIMIT $actualPlanRequest->startIndex,$actualPlanRequest->endIndex"; | |
| $actualPlans->actualPlans = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| return $actualPlans; | |
| } | |
| /** | |
| * get actual plan report by pseudo subject | |
| * @param ActualPlanRequest $actualPlanRequest | |
| * @return object|array|\com\linways\base\util\$objectList[] | |
| * @throws ProfessionalException | |
| */ | |
| public function getPseudoSubjectActualPlanReport($actualPlanRequest) | |
| { | |
| $actualPlans = new ActualPlanResponse(); | |
| $cond = ""; | |
| if ($actualPlanRequest != NULL) { | |
| $sql_count = "SELECT count(pssp.date) as totalRecord FROM ps_subjectplan pssp WHERE pssp.pssId = $actualPlanRequest->pssId "; | |
| $sql = "SELECT pssp.date, pssp.hour, pssp.module, pssp.topicName, pssp.isCovered, pssp.mode, pssp.day, pssp.session, sppr.pedagogy, pssp.modeOfeLearning, pssp.mode as modeActual , GROUP_CONCAT(lr.path) AS url,GROUP_CONCAT(DISTINCT plr.path) AS proposedUrl | |
| FROM ps_subjectplan pssp | |
| LEFT JOIN subjectplan_ps_proposed sppr ON sppr.topicID = pssp.proposed_topicID | |
| LEFT JOIN ps_actual_plan_video_materials sapvm ON pssp.topicID = sapvm.ps_actual_plan_id | |
| LEFT JOIN course_video_materials cvm ON sapvm.video_repository_id = cvm.id | |
| LEFT JOIN ps_proposed_plan_video_materials spvm ON pssp.topicID = spvm.ps_proposed_plan_id | |
| LEFT JOIN course_video_materials pcvm ON spvm.video_repository_id = pcvm.id | |
| LEFT JOIN lin_resource lr ON lr.id = cvm.resource_id | |
| LEFT JOIN lin_resource plr ON plr.id = pcvm.resource_id | |
| WHERE pssp.pssId = $actualPlanRequest->pssId | |
| group by pssp.topicID "; | |
| if((int)$actualPlanRequest->isCovered){ | |
| $cond .= " AND pssp.isCovered = $actualPlanRequest->isCovered " ; | |
| } | |
| $sql_count .= $cond; | |
| $sql .= $cond; | |
| if ($actualPlanRequest->sortOrder) { | |
| $sql .= " ORDER BY pssp.date $actualPlanRequest->sortOrder, pssp.hour, pssp.session "; | |
| } | |
| try { | |
| $actualPlans->totalRecords = $this->executeQueryForObject($sql_count)->totalRecord; | |
| if(!$actualPlanRequest->notUseLimits){ | |
| $sql .= "LIMIT $actualPlanRequest->startIndex,$actualPlanRequest->endIndex"; | |
| } | |
| $actualPlans->actualPlans = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| return $actualPlans; | |
| } | |
| /** | |
| * add subject plan popup preference | |
| * @param SubjectPlanPopupPreference $subjectPlanPreference | |
| * @return \com\linways\base\dto\MySqlResult | |
| * @throws ProfessionalException | |
| */ | |
| public function addSubjectPlanPopupPreference($subjectPlanPreference) | |
| { | |
| $sql = "INSERT INTO | |
| subjectPlanPreference | |
| ( | |
| deptId, | |
| batchId, | |
| popupHide, | |
| buttonHide, | |
| createdBy, | |
| updatedBy, | |
| createdDate, | |
| updatedDate | |
| ) | |
| VALUES | |
| ( | |
| $subjectPlanPreference->deptId, | |
| $subjectPlanPreference->batchId, | |
| $subjectPlanPreference->popupHide, | |
| $subjectPlanPreference->buttonHide, | |
| $subjectPlanPreference->createdBy, | |
| $subjectPlanPreference->updatedBy, | |
| utc_timestamp(), | |
| utc_timestamp() | |
| )"; | |
| try { | |
| return $this->executeQuery($sql, TRUE); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * update subject plan popup preference | |
| * @param SubjectPlanPopupPreference $subjectPlanPreference | |
| * @throws ProfessionalException | |
| */ | |
| public function updateSubjectPlanPopupPreference($subjectPlanPreference) | |
| { | |
| $value = []; | |
| $sql = "UPDATE | |
| subjectPlanPreference | |
| SET "; | |
| if ($subjectPlanPreference->popupHide != NULL) { | |
| $value[] = "popupHide=$subjectPlanPreference->popupHide"; | |
| } | |
| if ($subjectPlanPreference->buttonHide != NULL) { | |
| $value[] = "buttonHide=$subjectPlanPreference->buttonHide"; | |
| } | |
| $sql .= implode(',', $value); | |
| $sql .= " WHERE id=$subjectPlanPreference->id"; | |
| try { | |
| return $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * get all subject plan preference by dept id | |
| * @param int $deptId | |
| * @return object|array|\com\linways\base\util\$objectList[] | |
| * @throws ProfessionalException | |
| */ | |
| public function getSubjectPlanPopupPreference($deptId) | |
| { | |
| $batchDetails = NULL; | |
| $sql = "SELECT bth.batchId, bth.batchName, spp.id, spp.popupHide, spp.buttonHide FROM batches bth LEFT JOIN subjectPlanPreference spp ON spp.batchId=bth.batchID AND spp.deptId=bth.deptID WHERE bth.deptID=$deptId AND bth.batchHide=0 ORDER BY bth.batchId"; | |
| try { | |
| $batchDetails = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $batchDetails; | |
| } | |
| /** | |
| * check popup preference using batch id | |
| * @param int $batchId | |
| * @return object|NULL|\com\linways\base\util\$objectList[] | |
| * @throws ProfessionalException | |
| */ | |
| public function checkPopupHide($batchId) | |
| { | |
| $popupPreference = NULL; | |
| $sql = "SELECT popupHide,buttonHide FROM subjectPlanPreference WHERE batchId=$batchId"; | |
| try { | |
| $popupPreference = $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $popupPreference; | |
| } | |
| /** | |
| * | |
| * @param SubjectPlanPreference $defaultModeOfInstruction | |
| * @return \com\linways\base\dto\MySqlResult | |
| * @throws ProfessionalException | |
| */ | |
| public function createModeOfInstruction($defaultModeOfInstruction) | |
| { | |
| $sql = "insert into default_mode_of_instruction (deptId,batchId,modeOfInstruction,createdBy,createdDate) values ($defaultModeOfInstruction->deptId,$defaultModeOfInstruction->batchId,'" . $defaultModeOfInstruction->modeOfInstruction . "',$defaultModeOfInstruction->createdBy, utc_timestamp())"; | |
| try { | |
| return $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * | |
| * @param SubjectPlanPreference $defaultModeOfInstruction | |
| * @return \com\linways\base\dto\MySqlResult | |
| * @throws ProfessionalException | |
| */ | |
| public function updateModeOfInstruction($defaultModeOfInstruction) | |
| { | |
| $sql = "update default_mode_of_instruction set modeOfInstruction = '" . $defaultModeOfInstruction->modeOfInstruction . "', updatedBy = $defaultModeOfInstruction->updatedBy, updatedDate = utc_timestamp() where deptId = $defaultModeOfInstruction->deptId and batchId = $defaultModeOfInstruction->batchId"; | |
| try { | |
| return $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * | |
| * @param int $deptId | |
| * @param int $batchId | |
| * @return string | |
| * @throws ProfessionalException | |
| */ | |
| public function getDefaultModeOfInstructionByBatch($deptId, $batchId) | |
| { | |
| $sql = "select modeOfInstruction from default_mode_of_instruction where deptId = $deptId and batchId= $batchId"; | |
| try { | |
| $modeOfInstruction = $this->executeQueryForObject($sql)->modeOfInstruction; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $modeOfInstruction; | |
| } | |
| /** | |
| * get actual and proposed plan by subject | |
| * @param SubjectPlanRequest $subjectPlanRequest | |
| * @return object|array|\com\linways\base\util\$objectList[] | |
| * @throws ProfessionalException | |
| */ | |
| public function getSubjectPlan($subjectPlanRequest) | |
| { | |
| $subjectPlanRequest = $this->realEscapeObject($subjectPlanRequest); | |
| $subjectPlanList = []; | |
| if ($subjectPlanRequest->subbatchId) { | |
| $cond = "subbatchID IN(0," . $subjectPlanRequest->subbatchId . ")"; | |
| } else { | |
| $cond = "subbatchID = 0"; | |
| } | |
| $sql = "SELECT DISTINCT sp.topicID, sp.topicName, sp.module, sub.isCovered, sub.hour as actualHour, sp.hour as proposedHour, FROM_UNIXTIME(sp.date, '%Y-%m-%d') as proposedDate, sp.session".($subjectPlanRequest->needActualPlanId?',sub.topicID as actual_plan_topic_id':',null as actual_plan_topic_id')." | |
| FROM subjectplan_proposed sp | |
| LEFT JOIN subjectplan sub ON sp.topicID = sub.proposed_topicID AND sub.sbsID = sp.sbsID AND sub.subbatchID = sp.subbatchID AND FROM_UNIXTIME(sub.date, '%Y-%m-%d') = '" . date('Y-m-d', strtotime($subjectPlanRequest->date)) . "' | |
| WHERE sp.sbsID = $subjectPlanRequest->sbsId AND sp.$cond AND sp.topicID NOT IN (SELECT proposed_topicID FROM subjectplan WHERE sbsID = $subjectPlanRequest->sbsId AND FROM_UNIXTIME(date, '%Y-%m-%d') != '" . date('Y-m-d', strtotime($subjectPlanRequest->date)) . "' AND $cond AND isCovered = 2) GROUP BY sp.topicID "; | |
| if ($subjectPlanRequest->sortBy) { | |
| $sql .= " ORDER BY $subjectPlanRequest->sortBy $subjectPlanRequest->sortOrder "; | |
| } | |
| try { | |
| $subjectPlanList = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $subjectPlanList; | |
| } | |
| /** | |
| * check subject paln for a subject is approved or not | |
| * @param int $sbsId | |
| * @return boolean | |
| * @throws ProfessionalException | |
| */ | |
| public function subjectPlanApprovelStatusBySbsId($sbsId) | |
| { | |
| $sbsId = $this->realEscapeString($sbsId); | |
| $approverId = 0; | |
| $isApproved = false; | |
| $sql = "SELECT approverID FROM subjects_approve_status WHERE sbsID='$sbsId'"; | |
| try { | |
| $approverId = $this->executeQueryForObject($sql); | |
| if ($approverId) { | |
| $isApproved = true; | |
| } | |
| return $isApproved; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * @param $sbsId | |
| * @return Object|null | |
| * @throws ProfessionalException | |
| */ | |
| public function getWeeKWiseSubjectPlanDetails($sbsId) | |
| { | |
| $sbsId = $this->realEscapeObject($sbsId); | |
| $subjectPlans = null; | |
| $sql = "SELECT DISTINCT sp.topicID, CONCAT(FROM_UNIXTIME(sp.date, '%Y'),WEEK(FROM_UNIXTIME(sp.date, '%Y-%m-%d'),1)) as yearWeek, | |
| FROM_UNIXTIME(sp.date, '%Y-%m-%d') as subjectPlanDate, sp.topicName, sp.topicDesc, sp.isImportant, sp.hour, sp.module, | |
| sp.pedagogy, subp.isCovered, sp.session, | |
| cm.id as material_id, cm.resource_id, cm.name as material_name, cm.description as material_description, | |
| cm1.id as proposed_material_id, cm1.resource_id as proposed_resource_id, cm1.name as proposed_material_name, | |
| cm1.description as proposed_material_description, | |
| cvm.id as actual_video_material_id,cvm.title as actual_video_material_name,lr.path as actual_video_material_url, | |
| cvm1.id as proposed_video_material_id,cvm1.title as proposed_video_material_name,lr1.path as proposed_video_material_url, | |
| temp.finalStatus | |
| FROM subjectplan_proposed sp | |
| LEFT JOIN subjectplan subp ON sp.topicID=subp.proposed_topicID | |
| LEFT JOIN courseMatActualPlanRelation map ON map.actualId=subp.topicID | |
| LEFT JOIN course_materials cm ON cm.id=map.cousreMatId AND cm.is_active=1 | |
| LEFT JOIN subject_actual_plan_video_materials sapvm ON sapvm.subject_actual_plan_id = subp.topicID | |
| LEFT JOIN course_video_materials cvm on sapvm.video_repository_id = cvm.id | |
| LEFT JOIN lin_resource lr ON lr.id = cvm.resource_id | |
| LEFT JOIN courseMatProposedPlanRelation mpp ON mpp.proposedId=sp.topicID | |
| LEFT JOIN course_materials cm1 ON cm1.id=mpp.cousreMatId AND cm1.is_active=1 | |
| LEFT JOIN subject_proposed_plan_video_materials sppvm ON sppvm.subject_proposed_plan_id = sp.topicID | |
| LEFT JOIN course_video_materials cvm1 on sppvm.video_repository_id = cvm1.id | |
| LEFT JOIN lin_resource lr1 ON lr1.id = cvm1.resource_id | |
| LEFT JOIN ( | |
| SELECT MAX(isCovered) as finalStatus,proposed_topicID | |
| FROM subjectplan WHERE sbsID = $sbsId | |
| ) AS temp ON temp.proposed_topicID=sp.topicID | |
| WHERE sp.sbsID=$sbsId | |
| ORDER BY sp.date, WEEK(FROM_UNIXTIME(sp.date, '%Y-%m-%d'),1), sp.hour desc"; | |
| try { | |
| $subjectPlans = $this->executeQueryForList($sql, $this->mapper[SubjectPlanServiceMapper::GET_WEEKWISE_SUBJECTPLAN_DETAILS]); | |
| foreach ($subjectPlans as $subjectPlan) { | |
| foreach ($subjectPlan->subjectPlan as $plan) { | |
| foreach ($plan->actualPlanCourseMaterials as $material) { | |
| $request = new GetPreSignedUrlRequest(); | |
| $request->resourceId = $material->resourceId; | |
| $request->secretKey = getenv("AWS_CLIENT_SECRET_KEY"); | |
| $request->accessKey = getenv("AWS_ACCESS_KEY"); | |
| $url = ResourceService::getInstance()->getPreSignedUrlByResourceId($request); | |
| $material->url = $url->url; | |
| } | |
| foreach ($plan->proposedPlanCourseMaterials as $material) { | |
| $request = new GetPreSignedUrlRequest(); | |
| $request->resourceId = $material->resourceId; | |
| $request->secretKey = getenv("AWS_CLIENT_SECRET_KEY"); | |
| $request->accessKey = getenv("AWS_ACCESS_KEY"); | |
| $url = ResourceService::getInstance()->getPreSignedUrlByResourceId($request); | |
| $material->url = $url->url; | |
| } | |
| } | |
| } | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $subjectPlans; | |
| } | |
| /** | |
| * create relation between course material and actual plan | |
| * @param object $matDetails | |
| * @param int $actualId | |
| * @return object|NULL|\com\linways\base\util\$objectList[] | |
| * @throws ProfessionalException | |
| */ | |
| public function createActualplanCousreMat($matDetails, $actualId) | |
| { | |
| if (count($matDetails->matIds)) { | |
| $sql = "INSERT INTO courseMatActualPlanRelation | |
| (actualId, | |
| cousreMatId, | |
| createdBy, | |
| createdDate, | |
| updatedBy, | |
| updatedDate) VALUES"; | |
| foreach ($matDetails->matIds as $matId) { | |
| $sql .= "($actualId,'$matId',$matDetails->createdBy,utc_timestamp(),$matDetails->updatedBy,utc_timestamp()),"; | |
| } | |
| $sql = rtrim($sql, ','); | |
| } | |
| try { | |
| return $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * for get material Details of actual plan | |
| * @param int $actualId | |
| * @return object|array|\com\linways\base\util\$objectList[] | |
| * @throws ProfessionalException | |
| */ | |
| public function getActualplanCousreMat($actualId) | |
| { | |
| $sql = "SELECT cr.cousreMatId, cm.name as materialName, cm.topic as materialTopic, | |
| cm.description as materialDesc,cm.resource_id as resourceId | |
| FROM courseMatActualPlanRelation cr | |
| INNER JOIN course_materials cm ON cm.id=cr.cousreMatId | |
| WHERE actualId=$actualId"; | |
| try { | |
| $materials = $this->executeQueryForList($sql); | |
| foreach ($materials as $material) { | |
| $request = new GetPreSignedUrlRequest(); | |
| $request->secretKey = getenv("AWS_CLIENT_SECRET_KEY"); | |
| $request->accessKey = getenv("AWS_ACCESS_KEY"); | |
| $request->resourceId = $material->resourceId; | |
| $url = ResourceService::getInstance()->getPreSignedUrlByResourceId($request); | |
| $material->materialDocPath = $url->url; | |
| } | |
| return $materials; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * remove actual plan course materials | |
| * @param int $id | |
| * @throws ProfessionalException | |
| */ | |
| public function removeActualplanCousreMat($id) | |
| { | |
| $sql = "DELETE FROM courseMatActualPlanRelation WHERE actualId=$id"; | |
| try { | |
| $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * create relation between course material and proposed plan | |
| * @param object $matDetails | |
| * @return object|NULL|\com\linways\base\util\$objectList[] | |
| * @throws ProfessionalException | |
| */ | |
| public function createProposedplanCousreMat($matDetails, $proposedId) | |
| { | |
| if (count($matDetails->matIds)) { | |
| $sql = "INSERT INTO courseMatProposedPlanRelation | |
| (proposedId, | |
| cousreMatId, | |
| createdBy, | |
| createdDate, | |
| updatedBy, | |
| updatedDate) VALUES"; | |
| foreach ($matDetails->matIds as $matId) { | |
| $sql .= "($proposedId,'$matId',$matDetails->createdBy,utc_timestamp(),$matDetails->updatedBy,utc_timestamp()),"; | |
| } | |
| $sql = rtrim($sql, ','); | |
| } | |
| try { | |
| return $this->executeQueryForObject($sql, true); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * remove relation between proposed plan and course material | |
| * @param int $id | |
| * @throws ProfessionalException | |
| */ | |
| public function removeProposedplanCousreMat($id) | |
| { | |
| $sql = "DELETE FROM courseMatProposedPlanRelation WHERE proposedId=$id"; | |
| try { | |
| $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * for get material Details of proposed plan | |
| * @param int $proposedId | |
| * @return object|array|\com\linways\base\util\$objectList[] | |
| * @throws ProfessionalException | |
| */ | |
| public function getProposedplanCousreMat($proposedId) | |
| { | |
| $sql = "SELECT cr.cousreMatId, cm.name as materialName, cm.topic asmaterialTopic, cm.description as materialDesc, | |
| cm.resource_id as resourceId | |
| FROM courseMatProposedPlanRelation cr | |
| INNER JOIN course_materials cm ON cm.id=cr.cousreMatId | |
| WHERE proposedId=$proposedId"; | |
| try { | |
| $materials = $this->executeQueryForList($sql); | |
| foreach ($materials as $material) { | |
| $request = new GetPreSignedUrlRequest(); | |
| $request->resourceId = $material->resourceId; | |
| $request->secretKey = getenv("AWS_CLIENT_SECRET_KEY"); | |
| $request->accessKey = getenv("AWS_ACCESS_KEY"); | |
| $url = ResourceService::getInstance()->getPreSignedUrlByResourceId($request); | |
| $material->materialDocPath = $url->url; | |
| } | |
| return $materials; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * Get subject planner hour details | |
| * @param int $sbsId | |
| * @param string $fromDate | |
| * @param string $toDate | |
| * @return unknown | |
| * @throws ProfessionalException | |
| */ | |
| public function getSubjectPlannerDetails($sbsId, $toDate, $fromDate = NULL) | |
| { | |
| $sbsId = $this->realEscapeString($sbsId); | |
| $fromDate = $this->realEscapeString($fromDate); | |
| $toDate = $this->realEscapeString($toDate); | |
| if ($toDate) { | |
| $date = date_create($toDate); | |
| $toDate = date_format($date, "Y-m-d"); | |
| unset($date); | |
| } | |
| if ($fromDate) { | |
| $date = date_create($fromDate); | |
| $fromDate = date_format($date, "Y-m-d"); | |
| } | |
| $response = new \StdClass(); | |
| if (empty($fromDate)) { | |
| $ProposedPlan = " <= UNIX_TIMESTAMP('$toDate')"; | |
| $HoursTaken = " <= ('$toDate')"; | |
| } else { | |
| $ProposedPlan = " BETWEEN UNIX_TIMESTAMP('$fromDate') AND UNIX_TIMESTAMP('$toDate')"; | |
| $HoursTaken = " BETWEEN ('$fromDate') AND ('$toDate')"; | |
| } | |
| $sql = "SELECT count(sp.hour) as expectedHrs, count(ac.hour) as hrsTakenAttn, count(sh.hour) as suspendedHrs , count(sp.hour) - count(ac.hour) - count(sh.hour) as pendingHrs, count(sp.hour) - count(ac.hour) as neededHrs FROM subjectplan_proposed sp inner join sbs_relation sr on sr.sbsID = sp.sbsID left join attendance_confirm ac on sp.sbsID = ac.sbsID and ac.attendanceDate = from_unixtime(sp.date) and sp.hour = ac.hour left join suspended_hours sh on sh.suspendedDate = from_unixtime(sp.date) and sh.hour = sp.hour and sh.batchID = sr.batchID and sh.semID = sr.semID where sp.sbsID = $sbsId and date $ProposedPlan order by date, sp.hour;"; | |
| $sqlForNoOfProposedPlans = "SELECT | |
| count(sp.topicID) as NoOfProposedPlans | |
| FROM | |
| subjectplan_proposed sp | |
| WHERE sp.sbsID = '$sbsId' AND sp.date$ProposedPlan"; | |
| $sqlForNoOfHoursTaken = " SELECT | |
| count(distinct attendanceDate,hour) as NoOfHoursTaken | |
| FROM | |
| attendance_confirm | |
| WHERE sbsID='$sbsId' AND attendanceDate $HoursTaken;"; | |
| try { | |
| $response->noOfProposedPlans = $this->executeQueryForObject($sqlForNoOfProposedPlans)->NoOfProposedPlans; | |
| $response->sqlForNoOfHoursTaken = $this->executeQueryForObject($sqlForNoOfHoursTaken)->NoOfHoursTaken; | |
| $response->NoofPeriodsPending = $response->noOfProposedPlans - $response->sqlForNoOfHoursTaken; | |
| $response->NoOfFullProposedPlans = SubjectPlanService::getInstance()->getProposedSubjectPlanBySbsID($sbsId); | |
| $response->NoofFullPeriodsPending = $response->NoOfFullProposedPlans - $response->sqlForNoOfHoursTaken; | |
| $proposedPlanDetails = $this->executeQueryForObject($sql); | |
| $response->suspendedHrs = $proposedPlanDetails->suspendedHrs; | |
| $response->pendingHrs = $proposedPlanDetails->pendingHrs; | |
| $response->neededHrs = $proposedPlanDetails->neededHrs; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $response; | |
| } | |
| /** | |
| * Get actual planner hour details | |
| * @param int $sbsId | |
| * @param string $fromDate | |
| * @param string $toDate | |
| * @return unknown | |
| * @throws ProfessionalException | |
| */ | |
| public function getActualPlannerDetails($sbsId, $fromDate, $toDate) | |
| { | |
| $sbsId = $this->realEscapeString($sbsId); | |
| $fromDate = $this->realEscapeString($fromDate); | |
| $toDate = $this->realEscapeString($toDate); | |
| $sql = "SELECT count(sp.hour) as expectedHrs, count(ac.hour) as hrsTakenAttn, count(sh.hour) as suspendedHrs , count(sp.hour) - count(ac.hour) - count(sh.hour) as pendingHrs, count(sp.hour) - count(ac.hour) as neededHrs FROM subjectplan sp inner join sbs_relation sr on sr.sbsID = sp.sbsID left join attendance_confirm ac on sp.sbsID = ac.sbsID and ac.attendanceDate = from_unixtime(sp.date) and sp.hour = ac.hour left join suspended_hours sh on sh.suspendedDate = from_unixtime(sp.date) and sh.hour = sp.hour and sh.batchID = sr.batchID and sh.semID = sr.semID where sp.sbsID = $sbsId and date between unix_timestamp('$fromDate') and unix_timestamp('$toDate') order by date, sp.hour;"; | |
| try { | |
| return $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * Get all proposed subject plans by sbsId | |
| * @param Integer $sbsId | |
| * @return Array $subjectplanProposed | |
| * @author Vishnu M | |
| */ | |
| public function getProposedSubjectPlanDetailsBySbsId($sbsId) | |
| { | |
| $sbsId = (int)$this->realEscapeString($sbsId); | |
| $subjectplanProposed = []; | |
| $sql = "SELECT topicID, date,FROM_UNIXTIME(date, '%Y-%m-%d') as planDate, sbsID, topicName, topicDesc, isCovered, isImportant, hour, module, pedagogy, subbatchID, mode, day, timeInMinutes, percentageCovered, session,content FROM subjectplan_proposed WHERE sbsID = '" . $sbsId . "' ORDER BY date ASC"; | |
| try { | |
| $subjectplanProposed = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $subjectplanProposed; | |
| } | |
| /** | |
| * Get all Actual subject plans by sbsId | |
| * @param Integer $sbsId | |
| * @return Array $subjectplanActual | |
| * @author Vishnu M | |
| */ | |
| public function getActualSubjectPlanDetailsBySbsId($sbsId, $isCovered = null) | |
| { | |
| $sbsId = (int)$this->realEscapeString($sbsId); | |
| $isCovered = $this->realEscapeString($isCovered); | |
| $subjectplanActual = []; | |
| $condition = null; | |
| if ($isCovered != NULL) { | |
| $condition = " AND isCovered = '$isCovered' "; | |
| } | |
| $sql = "SELECT topicID, date, sbsID, topicName, topicDesc, isCovered, isImportant, hour, module, subbatchID, mode, day, timeInMinutes, percentageCovered, proposed_topicID, material_id, session FROM subjectplan WHERE sbsID = '" . $sbsId . "' $condition ORDER BY date ASC"; | |
| try { | |
| $subjectplanActual = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $subjectplanActual; | |
| } | |
| /** | |
| * Add proposed subject plan to community by sbsId | |
| * @param Integer $sbsId | |
| * @param Integer $staffId | |
| * @return Integer $id | |
| * @author Vishnu M | |
| */ | |
| public function addProposedSubjectPlanToCommunityBySbsId($sbsId, $staffId, $other = false) | |
| { | |
| $sbsId = (int)$this->realEscapeString($sbsId); | |
| $id = null; | |
| if($other) | |
| { | |
| $sql = "INSERT INTO subjectplan_proposed_community (sbs_id, is_other, createdBy, createdDate, updatedBy, updatedDate) VALUES ('$sbsId',1 ,'$staffId', utc_timestamp(), '$staffId', utc_timestamp())"; | |
| } | |
| else | |
| { | |
| $sql = "INSERT INTO subjectplan_proposed_community (sbs_id, createdBy, createdDate, updatedBy, updatedDate) VALUES ('$sbsId', '$staffId', utc_timestamp(), '$staffId', utc_timestamp())"; | |
| } try { | |
| $id = $this->executeQuery($sql, true); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $id; | |
| } | |
| /** | |
| * Get proposed subject plan from community by sbsId | |
| * @param Integer $sbsId | |
| * @return Object $subjectplan_proposed | |
| * @author Vishnu M | |
| */ | |
| public function getProposedSubjectPlanFromCommunityBySbsId($sbsId) | |
| { | |
| $sbsId = (int)$this->realEscapeString($sbsId); | |
| $subjectplan_proposed = null; | |
| try { | |
| $sql = "SELECT id, sbs_id, is_shared FROM subjectplan_proposed_community WHERE sbs_id = '$sbsId' "; | |
| $subjectplan_proposed = $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $subjectplan_proposed; | |
| } | |
| /** | |
| * Soft Delete proposed subject plan from community by sbsId | |
| * @param Integer $sbsId | |
| * @return Boolean true | |
| * @author Vishnu M | |
| */ | |
| public function changeSharingProposedSubjectPlanInCommunityBySbsId($sbsId, $other = false) | |
| { | |
| $sbsId = (int)$this->realEscapeString($sbsId); | |
| $other = $other ? 1 : 0; | |
| try { | |
| $sql = "UPDATE subjectplan_proposed_community | |
| SET is_shared = IF ( is_shared = 1, 0 , 1), | |
| updatedDate = utc_timestamp(), | |
| is_other = $other | |
| WHERE sbs_id = '$sbsId'"; | |
| $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return true; | |
| } | |
| /** | |
| * Get proposed subject plan from community by sbsId | |
| * @param Integer $sbsId | |
| * @return Object $subjectplan_proposed | |
| * @author Vishnu M | |
| */ | |
| public function getProposedSubjectPlanFromCommunityBySubject($sbsId) | |
| { | |
| $sbsId = (int)$this->realEscapeString($sbsId); | |
| $subjectplan_community = null; | |
| try { | |
| $sql = "SELECT sa.staffName, b.batchName, count(spp.topicID) AS proposedplans, sbs.sbsID FROM staffaccounts sa INNER JOIN sbs_relation sbs ON (sa.staffID = sbs.staffID) INNER JOIN batches b ON (b.batchID = sbs.batchID) INNER JOIN subjectplan_proposed_community spc ON (spc.sbs_id = sbs.sbsID) LEFT JOIN subjectplan_proposed spp ON (spp.sbsID = sbs.sbsID) WHERE sbs.subjectID = (SELECT subjectID FROM sbs_relation WHERE sbsID = '$sbsId') AND sbs.sbsID != '$sbsId' AND spc.is_shared = 1 GROUP BY spp.sbsID;"; | |
| $subjectplan_community = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $subjectplan_community; | |
| } | |
| /** | |
| * Get proposed subject plan from an array of topic ids | |
| * @param Array $topicIdArr | |
| * @return Array $proposed_subjectplan | |
| * @author Vishnu M | |
| */ | |
| public function getProposedSubjectPlanByIds($topicIdArr) | |
| { | |
| $topicIdArr = $this->realEscapeArray($topicIdArr); | |
| $proposed_subjectplan = null; | |
| try { | |
| $sql = "SELECT topicID, date, sbsID, topicName, topicDesc, isCovered, isImportant, hour, module, pedagogy, subbatchID, mode, day, timeInMinutes, percentageCovered, session,content FROM subjectplan_proposed WHERE topicID IN (" . implode(",", $topicIdArr) . ")"; $proposed_subjectplan = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $proposed_subjectplan; | |
| } | |
| /** | |
| * Get proposed subject plan from an array of topic ids | |
| * @param ProposedPlan-Object $proposedPlan | |
| * @return Integer $topicId | |
| * @author Vishnu M | |
| */ | |
| public function addProposedSubjectPlan($proposedPlan) | |
| { | |
| $proposedPlan = $this->realEscapeObject($proposedPlan); | |
| $topicId = null; | |
| try { | |
| $sql = "INSERT INTO subjectplan_proposed (date, sbsID, topicName, topicDesc, isCovered, isImportant, hour, module, pedagogy, subbatchID, mode, day, timeInMinutes, percentageCovered, session, created_by, created_date, updated_by, updated_date,content) VALUES ( | |
| '" . $proposedPlan->subjectPlanDate . "', | |
| '" . $proposedPlan->sbsId . "', | |
| '" . $proposedPlan->topicName . "', | |
| '" . $proposedPlan->topicDesc . "', | |
| '" . $proposedPlan->isCovered . "', | |
| '" . $proposedPlan->isImportant . "', | |
| '" . $proposedPlan->hour . "', | |
| '" . $proposedPlan->module . "', | |
| '" . $proposedPlan->pedagogy . "', | |
| '" . $proposedPlan->subbatchID . "', | |
| '" . $proposedPlan->mode . "', | |
| '" . $proposedPlan->day . "', | |
| '" . $proposedPlan->timeInMinutes . "', | |
| '" . $proposedPlan->percentageCovered . "', | |
| '" . $proposedPlan->session . "', | |
| '" . $proposedPlan->created_by . "', | |
| utc_timestamp(), | |
| '" . $proposedPlan->created_by . "', | |
| utc_timestamp(), | |
| '". json_encode($proposedPlan->content) . "' | |
| )"; | |
| $topicId = $this->executeQuery($sql, true); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $topicId; | |
| } | |
| /** | |
| * Get proposed subject plan from an array of topic ids | |
| * @param Integer $proposedPlanId | |
| * @param Integer $proposedPlanFromId | |
| * @param Integer $fromSbsId | |
| * @param Integer $staffId | |
| * @return Boolean true | |
| * @author Vishnu M | |
| */ | |
| public function addProposedPlanCopyInfo($proposedPlanId, $proposedPlanFromId, $fromSbsId, $staffId) | |
| { | |
| $proposedPlanId = (int)$this->realEscapeString($proposedPlanId); | |
| $proposedPlanFromId = (int)$this->realEscapeString($proposedPlanFromId); | |
| $fromSbsId = (int)$this->realEscapeString($fromSbsId); | |
| $staffId = (int)$this->realEscapeString($staffId); | |
| try { | |
| $sql = "INSERT INTO subjectplan_proposed_copy_info (subjectplan_proposed_id, from_subjectplan_proposed_id, from_sbs_id, createdBy, createdDate, updatedBy, updatedDate) VALUES ('" . $proposedPlanId . "', '" . $proposedPlanFromId . "', '" . $fromSbsId . "', " . $staffId . ", utc_timestamp(), " . $staffId . ", utc_timestamp())"; | |
| $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return true; | |
| } | |
| /** | |
| * Get proposed subject plan copy info | |
| * @param Integer $sbsId | |
| * @return Array proposedPlanCopyInfo | |
| * @author Vishnu M | |
| */ | |
| public function getProposedPlanCopyInfoBySbsId($fromSbsId, $sbsId) | |
| { | |
| $fromSbsId = (int)$this->realEscapeString($fromSbsId); | |
| $sbsId = (int)$this->realEscapeString($sbsId); | |
| $proposedPlanCopyInfo = []; | |
| try { | |
| $sql = "SELECT spci.subjectplan_proposed_id, spci.from_subjectplan_proposed_id, spci.from_sbs_id FROM subjectplan_proposed_copy_info spci INNER JOIN subjectplan_proposed sp ON (sp.topicID = spci.subjectplan_proposed_id) WHERE spci.from_sbs_id = '" . $fromSbsId . "' AND sp.sbsID = '" . $sbsId . "'"; | |
| $proposedPlanCopyInfo = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $proposedPlanCopyInfo; | |
| } | |
| /** | |
| * Get proposed subject plan from an array of topic id | |
| * @param Integer $topicId | |
| * @return Object $proposed_subjectplan | |
| * @author Vishnu M | |
| */ | |
| public function getProposedSubjectPlanById($topicId) | |
| { | |
| $topicId = (int)$this->realEscapeString($topicId); | |
| $proposed_subjectplan = null; | |
| try { | |
| $sql = "SELECT topicID, date, sbsID, topicName, topicDesc, isCovered, isImportant, hour, module, pedagogy, subbatchID, mode, day, timeInMinutes, percentageCovered, session,content FROM subjectplan_proposed WHERE topicID = '$topicId'"; | |
| $proposed_subjectplan = $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $proposed_subjectplan; | |
| } | |
| /** | |
| * Get proposed subject plan from an array of topic id | |
| * @param Integer $sbsId | |
| * @param String $fromDate | |
| * @param String $toDate | |
| * @return Object $actualSubjectPlan | |
| * @throws ProfessionalException | |
| * @author Vishnu M | |
| */ | |
| public function getActualSubjectPlansCovered($sbsId, $fromDate = NULL, $toDate = NULL) | |
| { | |
| $sbsId = (int)$this->realEscapeString($sbsId); | |
| $fromDate = $this->realEscapeString($fromDate); | |
| $toDate = $this->realEscapeString($toDate); | |
| $condition = null; | |
| if ($fromDate && $toDate) { | |
| $fromDate = date("Y-m-d", strtotime($fromDate)); | |
| $toDate = date("Y-m-d", strtotime($toDate)); | |
| $condition = " AND date between unix_timestamp('$fromDate') and unix_timestamp('$toDate') "; | |
| } else if ($toDate) { | |
| $toDate = date("Y-m-d", strtotime($toDate)); | |
| $condition = " AND date <= unix_timestamp('$toDate') "; | |
| } else if ($fromDate) { | |
| $fromDate = date("Y-m-d", strtotime($fromDate)); | |
| $condition = " AND date >= unix_timestamp('$fromDate') "; | |
| } | |
| $actualSubjectPlan = null; | |
| try { | |
| $sql = "SELECT topicID, date, sbsID, topicName, topicDesc, isCovered, isImportant, hour, module, proposed_topicID, material_id, subbatchID, mode, day, timeInMinutes, percentageCovered, session FROM subjectplan WHERE isCovered = 2 AND sbsID = '$sbsId' $condition "; | |
| $actualSubjectPlan = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $actualSubjectPlan; | |
| } | |
| /** | |
| * To get count of proposed subjectplan | |
| * @param Integer $sbsId | |
| * @return Int NoOfproposedPlan | |
| * @author Nandu | |
| */ | |
| public function getProposedSubjectPlanBySbsID($sbsId) | |
| { | |
| $sbsID = (int)$this->realEscapeString($sbsID); | |
| try { | |
| $sql = "SELECT count(topicID) as NoOfproposedPlan FROM subjectplan_proposed sp WHERE sp.sbsID = '$sbsId';"; | |
| $NoOfproposedPlan = $this->executeQueryForObject($sql)->NoOfproposedPlan; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $NoOfproposedPlan; | |
| } | |
| /** | |
| * @param $subjectPlanId | |
| * @param $courseMaterialId | |
| * @throws ProfessionalException | |
| */ | |
| public function deleteCourseMaterialFromActualPlan($subjectPlanId, $courseMaterialId) | |
| { | |
| $subjectPlanId = $this->realEscapeString($subjectPlanId); | |
| $courseMaterialId = $this->realEscapeString($courseMaterialId); | |
| if (empty($subjectPlanId)) { | |
| throw new ProfessionalException(ProfessionalException::INVALID_ACTUAL_PLAN_ID, "Invalid actual plan details given"); | |
| } | |
| if (empty($courseMaterialId) && (int)$courseMaterialId) { | |
| throw new ProfessionalException(ProfessionalException::INVALID_DOCUMENT_ID, "Invalid course materials details given"); | |
| } | |
| $sql = "DELETE FROM courseMatActualPlanRelation | |
| WHERE actualId =$subjectPlanId AND cousreMatId= '$courseMaterialId'"; | |
| try { | |
| $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * @param $courseMaterialId | |
| * @throws ProfessionalException | |
| */ | |
| public function deleteCourseMaterialFromActualPlanByMaterialId($courseMaterialId) | |
| { | |
| $courseMaterialId = $this->realEscapeString($courseMaterialId); | |
| if (empty($courseMaterialId)) { | |
| throw new ProfessionalException(ProfessionalException::INVALID_DOCUMENT_ID, "Invalid course materials details given"); | |
| } | |
| $sql = "DELETE FROM courseMatActualPlanRelation | |
| WHERE cousreMatId= '$courseMaterialId'"; | |
| try { | |
| $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * @param $proposedId | |
| * @param $courseMaterialId | |
| * @throws ProfessionalException | |
| */ | |
| public function deleteCourseMaterialFromProposesPlan($proposedId, $courseMaterialId) | |
| { | |
| $proposedId = $this->realEscapeString($proposedId); | |
| $courseMaterialId = $this->realEscapeString($courseMaterialId); | |
| if (empty($proposedId)) { | |
| throw new ProfessionalException(ProfessionalException::INVALID_PROPOSED_PLAN_ID, "Invalid proposed plan details given"); | |
| } | |
| if (empty($courseMaterialId)) { | |
| throw new ProfessionalException(ProfessionalException::INVALID_DOCUMENT_ID, "Invalid course materials details given"); | |
| } | |
| $sql = "DELETE FROM courseMatProposedPlanRelation | |
| WHERE proposedId=$proposedId AND cousreMatId= '$courseMaterialId'"; | |
| try { | |
| $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * @param $courseMaterialId | |
| * @throws ProfessionalException | |
| */ | |
| public function deleteCourseMaterialFromProposesPlanMaterialId($courseMaterialId) | |
| { | |
| $courseMaterialId = $this->realEscapeString($courseMaterialId); | |
| if (empty($courseMaterialId)) { | |
| throw new ProfessionalException(ProfessionalException::INVALID_DOCUMENT_ID, "Invalid course materials details given"); | |
| } | |
| $sql = "DELETE FROM courseMatProposedPlanRelation | |
| WHERE cousreMatId= '$courseMaterialId'"; | |
| try { | |
| $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * @param $proposedId | |
| * @return array|object | |
| * @throws ProfessionalException | |
| */ | |
| public function getProposedPlanCourseMaterials($proposedId) | |
| { | |
| $courseMaterialDetails = []; | |
| $sql = "SELECT cm.id, cm.name, cm.topic,cm.description, cm.resource_id as resourceId | |
| FROM courseMatProposedPlanRelation cr | |
| INNER JOIN course_materials cm ON cm.id = cr.cousreMatId | |
| WHERE cr.proposedId = $proposedId"; | |
| try { | |
| $courseMaterialDetails = $this->executeQueryForList($sql); | |
| foreach ($courseMaterialDetails as $materialDetail) { | |
| $request = new GetPreSignedUrlRequest(); | |
| $request->resourceId = $materialDetail->resourceId; | |
| $request->secretKey = getenv("AWS_CLIENT_SECRET_KEY"); | |
| $request->accessKey = getenv("AWS_ACCESS_KEY"); | |
| $url = ResourceService::getInstance()->getPreSignedUrlByResourceId($request); | |
| $materialDetail->url = $url->url; | |
| } | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $courseMaterialDetails; | |
| } | |
| /** | |
| * Returns all course materials assigned to actual plans | |
| * | |
| * @param int $actualId | |
| * @return array|object $courseMatDetails | |
| * @throws ProfessionalException | |
| */ | |
| public function getActualPlanCourseMaterials($actualId) | |
| { | |
| $courseMaterialDetails = []; | |
| $sql = "SELECT cm.id, cm.name, cm.topic,cm.description, cm.resource_id as resourceId | |
| FROM courseMatActualPlanRelation cr | |
| INNER JOIN course_materials cm ON cm.id = cr.cousreMatId | |
| WHERE cr.actualId = $actualId"; | |
| try { | |
| $courseMaterialDetails = $this->executeQueryForList($sql); | |
| foreach ($courseMaterialDetails as $materialDetail) { | |
| $request = new GetPreSignedUrlRequest(); | |
| $request->resourceId = $materialDetail->resourceId; | |
| $request->secretKey = getenv("AWS_CLIENT_SECRET_KEY"); | |
| $request->accessKey = getenv("AWS_ACCESS_KEY"); | |
| $url = ResourceService::getInstance()->getPreSignedUrlByResourceId($request); | |
| $materialDetail->url = $url->url; | |
| } | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $courseMaterialDetails; | |
| } | |
| /** | |
| * @param AddNewActualPlanMaterialRequest $request | |
| * @return Object|integer | |
| * @throws ProfessionalException | |
| */ | |
| public function addNewActualPlanVideoMaterial(AddNewActualPlanMaterialRequest $request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| if (empty($request->materialId)) { | |
| throw new ProfessionalException(ProfessionalException::INVALID_BATCH_VIDEO_REPOSITORY_ID, "Invalid video repository details given"); | |
| } | |
| if (empty($request->actualPlanId)) { | |
| throw new ProfessionalException(ProfessionalException::INVALID_ACTUAL_PLAN_ID, "Invalid actual plan details given"); | |
| } | |
| $sql = "INSERT INTO subject_actual_plan_video_materials (subject_actual_plan_id, video_repository_id, | |
| created_by, created_date, updated_by, updated_date) | |
| VALUES ($request->actualPlanId,'$request->materialId',$request->createdBy,UTC_TIMESTAMP(), | |
| $request->updatedBy,UTC_TIMESTAMP())"; | |
| try { | |
| return $this->executeQueryForObject($sql, true); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * @param AddNewProposedPlanMaterialRequest $request | |
| * @return Object|integer | |
| * @throws ProfessionalException | |
| */ | |
| public function addNewProposedPlanVideoMaterial(AddNewProposedPlanMaterialRequest $request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| if (empty($request->materialId)) { | |
| throw new ProfessionalException(ProfessionalException::INVALID_BATCH_VIDEO_REPOSITORY_ID, "Invalid video repository details given"); | |
| } | |
| if (empty($request->proposedPlanId)) { | |
| throw new ProfessionalException(ProfessionalException::INVALID_PROPOSED_PLAN_ID, "Invalid proposed plan details given"); | |
| } | |
| $sql = "INSERT INTO subject_proposed_plan_video_materials (subject_proposed_plan_id, video_repository_id, | |
| created_by, created_date, updated_by, updated_date) | |
| VALUES ($request->proposedPlanId,'$request->materialId',$request->createdBy,UTC_TIMESTAMP(), | |
| $request->updatedBy,UTC_TIMESTAMP())"; | |
| try { | |
| return $this->executeQueryForObject($sql, true); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * @param $actualPlanId | |
| * @return Object|array | |
| * @throws ProfessionalException | |
| */ | |
| public function getActualPlanVideoMaterials($actualPlanId) | |
| { | |
| $actualPlanId = (int)$this->realEscapeString($actualPlanId); | |
| if (empty($actualPlanId)) { | |
| throw new ProfessionalException(ProfessionalException::INVALID_ACTUAL_PLAN_ID, "Invalid actual plan details given"); | |
| } | |
| $sql = "SELECT cvm.id,cvm.title as name,lr.path as url,cvm.description | |
| FROM subject_actual_plan_video_materials sapvm | |
| INNER JOIN course_video_materials cvm on sapvm.video_repository_id = cvm.id | |
| INNER JOIN lin_resource lr ON lr.id = cvm.resource_id | |
| WHERE subject_actual_plan_id = $actualPlanId"; | |
| try { | |
| return $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * @param $proposedPlanId | |
| * @return Object|arra | |
| * @throws ProfessionalException | |
| */ | |
| public function getProposedPlanVideoMaterials($proposedPlanId) | |
| { | |
| $proposedPlanId = (int)$this->realEscapeString($proposedPlanId); | |
| if (empty($proposedPlanId)) { | |
| throw new ProfessionalException(ProfessionalException::INVALID_PROPOSED_PLAN_ID, "Invalid proposed plan details given"); | |
| } | |
| $sql = "SELECT cvm.id,cvm.title as name,lr.path as url,cvm.description | |
| FROM subject_proposed_plan_video_materials sapvm | |
| INNER JOIN course_video_materials cvm on sapvm.video_repository_id = cvm.id | |
| INNER JOIN lin_resource lr ON lr.id = cvm.resource_id | |
| WHERE subject_proposed_plan_id = $proposedPlanId"; | |
| try { | |
| return $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * @param DeleteActualPlanRequest $request | |
| * @throws ProfessionalException | |
| */ | |
| public function deleteActualPlan(DeleteActualPlanRequest $request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| if (empty($request->actualPlanId)) { | |
| throw new ProfessionalException(ProfessionalException::INVALID_ACTUAL_PLAN_ID, "Invalid actual plan details given"); | |
| } | |
| try { | |
| if ($request->isDeleteCourseMaterials) { | |
| $courseMaterials = $this->getAllActualPlanCourseMaterialIds($request->actualPlanId); | |
| foreach ($courseMaterials as $material) { | |
| $deleteRequest = new DeleteCourseMaterialRequest(); | |
| $deleteRequest->documentId = $material->id; | |
| $deleteRequest->staffId = $request->staffId; | |
| CourseMaterialService::getInstance()->deleteCourseMaterial($deleteRequest); | |
| } | |
| $videoMaterials = $this->getAllActualPlanVideoMaterials($request->actualPlanId); | |
| foreach ($videoMaterials as $material) { | |
| VideoRepositoryService::getInstance()->deleteVideoMaterial($material->id); | |
| } | |
| } else { | |
| $this->deleteAllActualPlanCourseMaterialsRelation($request->actualPlanId); | |
| $this->deleteAllActualPlanVideoMaterialsRelation($request->actualPlanId); | |
| } | |
| $sql = "DELETE FROM subjectplan WHERE topicID = $request->actualPlanId"; | |
| $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * Deleting actual plan course material relation | |
| * | |
| * @param $actualPlanId | |
| * @throws ProfessionalException | |
| */ | |
| private function deleteAllActualPlanCourseMaterialsRelation($actualPlanId) | |
| { | |
| $sql = "DELETE FROM courseMatActualPlanRelation WHERE actualId = $actualPlanId"; | |
| try { | |
| $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * Deleting actual plan video material relation | |
| * | |
| * @param $actualPlanId | |
| * @throws ProfessionalException | |
| */ | |
| private function deleteAllActualPlanVideoMaterialsRelation($actualPlanId) | |
| { | |
| $sql = "DELETE FROM subject_actual_plan_video_materials WHERE subject_actual_plan_id = $actualPlanId"; | |
| try { | |
| $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * @param $actualPlanId | |
| * @return Object | |
| * @throws ProfessionalException | |
| */ | |
| private function getAllActualPlanVideoMaterials($actualPlanId) | |
| { | |
| $sql = "SELECT video_repository_id as id | |
| FROM subject_actual_plan_video_materials | |
| WHERE subject_actual_plan_id =$actualPlanId"; | |
| try { | |
| return $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * @param DeleteProposedPlanRequest $request | |
| * @throws ProfessionalException | |
| */ | |
| public function deleteProposedPlan(DeleteProposedPlanRequest $request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| if (empty($request->proposedPlanId)) { | |
| throw new ProfessionalException(ProfessionalException::INVALID_PROPOSED_PLAN_ID, "Invalid proposed plan details given"); | |
| } | |
| try { | |
| if ($request->isDeleteCourseMaterials) { | |
| $courseMaterials = $this->getAllProposedPlanCourseMaterialIds($request->proposedPlanId); | |
| foreach ($courseMaterials as $material) { | |
| $deleteRequest = new DeleteCourseMaterialRequest(); | |
| $deleteRequest->documentId = $material->id; | |
| $deleteRequest->staffId = $request->staffId; | |
| CourseMaterialService::getInstance()->deleteCourseMaterial($deleteRequest); | |
| } | |
| $videoMaterials = $this->getAllProposedPlanVideoMaterials($request->proposedPlanId); | |
| foreach ($videoMaterials as $material) { | |
| VideoRepositoryService::getInstance()->deleteVideoMaterial($material->id); | |
| } | |
| } else { | |
| $this->deleteAllProposedPlanCourseMaterialsRelation($request->proposedPlanId); | |
| $this->deleteAllProposedPlanVideoMaterialsRelation($request->proposedPlanId); | |
| } | |
| $this->removeProposedPlanFromActualPlan($request->proposedPlanId); | |
| $sql = "DELETE FROM subjectplan_proposed WHERE topicID = $request->proposedPlanId"; | |
| $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * Deleting all proposed plan course materials relation | |
| * | |
| * @param $proposedPlanId | |
| * @throws ProfessionalException | |
| */ | |
| private function deleteAllProposedPlanCourseMaterialsRelation($proposedPlanId) | |
| { | |
| $sql = "DELETE FROM courseMatProposedPlanRelation WHERE proposedId = $proposedPlanId"; | |
| try { | |
| $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * Deleting proposed plan video material relation | |
| * | |
| * @param $proposedPlanId | |
| * @throws ProfessionalException | |
| */ | |
| private function deleteAllProposedPlanVideoMaterialsRelation($proposedPlanId) | |
| { | |
| $sql = "DELETE FROM ps_proposed_plan_video_materials WHERE ps_proposed_plan_id = $proposedPlanId"; | |
| try { | |
| $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * @param $proposedPlanId | |
| * @throws ProfessionalException | |
| */ | |
| private function removeProposedPlanFromActualPlan($proposedPlanId) | |
| { | |
| $sql = "UPDATE subjectplan SET proposed_topicID = NULL | |
| WHERE proposed_topicID=$proposedPlanId"; | |
| try { | |
| $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * @param $proposedPlanId | |
| * @return Object | |
| * @throws ProfessionalException | |
| */ | |
| public function getAllProposedPlanVideoMaterials($proposedPlanId) | |
| { | |
| $proposedPlanId = (int)$this->realEscapeString($proposedPlanId); | |
| if (empty($proposedPlanId)) { | |
| throw new ProfessionalException(ProfessionalException::INVALID_PROPOSED_PLAN_ID, "Invalid proposed plan details given"); | |
| } | |
| $sql = "SELECT video_repository_id as id | |
| FROM subject_proposed_plan_video_materials | |
| WHERE subject_proposed_plan_id =$proposedPlanId"; | |
| try { | |
| return $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * @param $proposedPlanId | |
| * @return Object | |
| * @throws ProfessionalException | |
| */ | |
| public function getAllProposedPlanCourseMaterialIds($proposedPlanId) | |
| { | |
| $proposedPlanId = (int)$this->realEscapeString($proposedPlanId); | |
| if (empty($proposedPlanId)) { | |
| throw new ProfessionalException(ProfessionalException::INVALID_PROPOSED_PLAN_ID, "Invalid proposed plan details given"); | |
| } | |
| $sql = "SELECT cousreMatId as id FROM courseMatProposedPlanRelation | |
| WHERE proposedId =$proposedPlanId"; | |
| try { | |
| return $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * @param $actualPlanId | |
| * @return Object | |
| * @throws ProfessionalException | |
| */ | |
| private function getAllActualPlanCourseMaterialIds($actualPlanId) | |
| { | |
| $sql = "SELECT cousreMatId as id FROM courseMatActualPlanRelation | |
| WHERE actualId =$actualPlanId"; | |
| try { | |
| return $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| public function getSubjectProposedPlanByRequest($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $condition = $request->topicId ? " and topicID = " . (int)$request->topicId . " " : ""; | |
| $condition .= $request->topicIds ? " and topicID in (" . $request->topicIds . ") " : ""; | |
| if ($request->isPseudosubject) { | |
| $condition .= $request->pseudosubjectId ? " and pssId = $request->pseudosubjectId " : ""; | |
| $sql = "select topicID, date, pssId, topicName, topicDesc, isCovered, isImportant, hour, module, pedagogy, mode, day, session, approve,null as timeInMinutes,null as percentageCovered,oe_exam_id as onlineExamId from subjectplan_ps_proposed where 1=1 $condition;"; | |
| } else { | |
| $condition .= $request->sbsId ? " and sbs.sbsID = $request->sbsId " : ""; | |
| $condition .= $request->subjectId ? " and sbs.subjectID = '$request->subjectId' " : ""; | |
| $condition .= $request->batchId ? " and sbs.batchID = '$request->batchId' " : ""; | |
| $condition .= $request->semId ? " and sbs.semID = '$request->semId' " : ""; | |
| $condition .= $request->staffId ? " and sbs.staffId = $request->staffId " : ""; | |
| $sql = "select sp.topicID, sp.date, sp.sbsID, sp.topicName, sp.topicDesc, sp.isCovered, sp.isImportant, sp.hour, sp.module, sp.pedagogy, sp.subbatchID, sp.mode, sp.day, sp.timeInMinutes, sp.percentageCovered, sp.session, sp.approve, sp.timeInMinutes, sp.percentageCovered,oe_exam_id as onlineExamId,sp.content from subjectplan_proposed sp | |
| inner join sbs_relation sbs on sbs.sbsID = sp.sbsID | |
| where 1=1 $condition;"; | |
| } | |
| try { | |
| return $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| public function getSubjectPlansByRequest($request) | |
| { | |
| $where = []; | |
| if ($request->fromDate && $request->toDate) { | |
| $fromDate = date("Y-m-d", strtotime($request->fromDate)); | |
| $toDate = date("Y-m-d", strtotime($request->toDate)); | |
| $where[] = "((spp.date between unix_timestamp('$fromDate') and unix_timestamp('$toDate') ) OR (spp.date = 0))"; | |
| } else if ($request->toDate) { | |
| $toDate = date("Y-m-d", strtotime($request->toDate)); | |
| $where[] = "((spp.date <= unix_timestamp('$toDate') ) OR (spp.date = 0))"; | |
| } else if ($request->fromDate) { | |
| $fromDate = date("Y-m-d", strtotime($request->fromDate)); | |
| $where[] = "((spp.date >= unix_timestamp('$fromDate') ) OR (spp.date = 0))"; | |
| }else if ($request->date) { | |
| $date = date("Y-m-d", strtotime($request->date)); | |
| $where[] = "((spp.date = unix_timestamp('$date') ) OR (spp.date = 0))"; | |
| } | |
| $request->sbsId?$where[] = " sbs.sbsID = $request->sbsId " :""; | |
| $request->staffId?$where[] = " sbs.staffId = $request->staffId " :""; | |
| $request->hourId?$where[] = " sp.hour = $request->hourId " :""; | |
| $request->batchId?$where[] = " sbs.batchID = $request->batchId " :""; | |
| $request->semId?$where[] = " sbs.semID = $request->semId " :""; | |
| $sql = "SELECT FROM_UNIXTIME(spp.date, '%Y-%m-%d') as sspDate,FROM_UNIXTIME(sp.date, '%Y-%m-%d') as spDate,spp.topicID as proposedPlanTopicID,spp.topicName as proposedPlanTopicName,spp.session, | |
| case | |
| when spp.subbatchID = 0 then 'ALL' | |
| when spp.subbatchID <> 0 then su.subbatchName | |
| END as subBatch, | |
| spp.isImportant,spp.hour as proposedPlanHour,spp.module as proposedPlanModule,spp.approve, spp.content, sp.topicID, sp.topicName, sp.topicDesc, sp.isImportant, sp.isCovered, sp.hour, sp.module, d.deptName, sbs.sbsID, st.staffID, st.staffName, sub.subjectID, sub.subjectName, bat.batchID, bat.batchName, sem.semName,spp.verify,spp.oe_exam_id as onlineExamId,spp.mode as proposedPlanMode,spp.pedagogy as proposedPlanPedagogy | |
| FROM sbs_relation sbs | |
| INNER JOIN subjectplan_proposed spp ON spp.sbsID = sbs.sbsID | |
| INNER JOIN staffaccounts st ON st.staffID = sbs.staffID | |
| INNER JOIN subjects sub ON sub.subjectID = sbs.subjectID | |
| INNER JOIN batches bat ON bat.batchID = sbs.batchID AND bat.semID = sbs.semID | |
| INNER JOIN semesters sem ON sem.semID = bat.semID | |
| INNER JOIN department d ON d.deptID = bat.deptID | |
| LEFT JOIN subbatches su on su.subbatchID = spp.subbatchID and sbs.batchID = su.batchID | |
| LEFT JOIN subjectplan sp ON sbs.sbsID = sp.sbsID AND spp.topicID = sp.proposed_topicID | |
| ".($where?" WHERE ".implode(' AND ',$where):"")." | |
| ORDER BY sbs.sbsID;"; | |
| try { | |
| $result = $this->executeQueryForList($sql); | |
| return CommonService::getInstance()->htmlEntityDecodeArrayOfObjects($result); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * method to get subject plan duration by sbsId and dates | |
| * @param Int sbsID, fromdate.todate | |
| * @return Array | |
| * @throws ProfessionalException | |
| */ | |
| public function getSubjectPlanDurationBySbsIdAndDate($sbsID, $fromDate, $toDate){ | |
| $sql = "SELECT | |
| sp.topicName, | |
| bt.stratTime, | |
| bt.endTime, | |
| STR_TO_DATE(bt.stratTime, '%l:%i %p') as stratTime, | |
| STR_TO_DATE(bt.endTime, '%l:%i %p') as endTime, | |
| TIMEDIFF(cast(STR_TO_DATE(bt.endTime, '%l:%i %p') as TIME),cast(STR_TO_DATE(bt.stratTime, '%l:%i %p') as TIME)) AS duration, | |
| sp.hour, | |
| FROM_UNIXTIME(sp.date, '%Y-%m-%d') AS subjectPlanDate, | |
| FROM_UNIXTIME(sp.date, '%h:%i:%s') AS subjectPlanTime, | |
| sp.topicDesc | |
| FROM | |
| subjectplan sp | |
| LEFT JOIN | |
| batch_timetable bt ON sp.sbsID = bt.sbsID | |
| AND sp.hour = bt.hourID | |
| AND FROM_UNIXTIME(sp.date, '%Y-%m-%d') = bt.timetableDate | |
| WHERE | |
| sp.sbsID = '$sbsID' | |
| AND bt.timetableDate BETWEEN '$fromDate' AND '$toDate'"; | |
| try{ | |
| return $this->executeQueryForList($sql); | |
| } | |
| catch(\Exception $e){ | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * @param $proposedPlanId | |
| * @return Object | |
| * @throws ProfessionalException | |
| */ | |
| public function updateOnlineExamMappingProposedPlan($proposedPlanId,$onlineExamId) | |
| { | |
| $sql = "UPDATE `subjectplan_proposed` | |
| SET `oe_exam_id`= '$onlineExamId' | |
| WHERE `topicID`='$proposedPlanId' "; | |
| try { | |
| $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * @param $proposedPlanId | |
| * @return Object | |
| * @throws ProfessionalException | |
| */ | |
| public function updateOnlineExamMappingProposedPlanPs($proposedPlanId,$onlineExamId) | |
| { | |
| $sql = "UPDATE `subjectplan_ps_proposed` | |
| SET `oe_exam_id`= '$onlineExamId' | |
| WHERE `topicID`='$proposedPlanId' "; | |
| try { | |
| $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| public function getOnlineExamMappingProposedPlan($proposedPlanId) | |
| { | |
| $sql = "SELECT oe.id , oe.name | |
| from subjectplan_proposed sp | |
| inner join oe_exams oe on oe.id = sp.oe_exam_id | |
| where sp.topicID = $proposedPlanId "; | |
| try { | |
| return $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| public function getOnlineExamMappingProposedPlanPs($proposedPlanId) | |
| { | |
| $sql = "SELECT oe.id , oe.name | |
| from subjectplan_ps_proposed sp | |
| inner join oe_exams oe on oe.id = sp.oe_exam_id | |
| where sp.topicID = $proposedPlanId "; | |
| try { | |
| return $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| public function getSubjectPlanDetails($request) | |
| { | |
| $cond = ""; | |
| if($request->subjectType == "pseudoSubjects"){ | |
| if($request->sbsId){ | |
| $cond .=" AND sr.sbsID = $request->sbsId "; | |
| } | |
| if($request->mode){ | |
| $cond .=" AND sp.mode = '$request->mode' "; | |
| } | |
| if($request->pseudoSubjectId){ | |
| $cond .=" AND sp.pssId = '$request->pseudoSubjectId' "; | |
| } | |
| if($request->staffId){ | |
| $cond .=" AND sa.staffID = '$request->staffId' "; | |
| } | |
| $sql = "SELECT sr.sbsID,sp.topicID AS proposedPlanId,subp.topicID AS actualPlanId,FROM_UNIXTIME(sp.date, '%Y-%m-%d') AS subjectPlanDate, sp.hour, sp.module, sp.topicName, null as subjectType,sp.mode as mode_of_instruction, | |
| CASE | |
| WHEN subp.isCovered = '0' THEN 'Not covered' | |
| WHEN subp.isCovered = '1' THEN 'Partially covered' | |
| WHEN subp.isCovered = '2' THEN 'Fully covered' | |
| else null | |
| END AS covered,sa.staffName,sp.session, | |
| (select group_concat(sa.rollNo) | |
| from studentaccount sa left join attendance at on sa.studentID = at.studentID | |
| inner join sbs_relation Sbs on Sbs.sbsID = at.sbsID | |
| inner join staffaccounts Sa on Sa.staffID = Sbs.staffID | |
| where | |
| at.attendanceDate = subjectPlanDate and at.isAbsent = 1 | |
| and at.hour = (CASE | |
| WHEN sp.hour IS NOT NULL THEN sp.hour | |
| else sp.session | |
| END | |
| ) | |
| and Sa.staffID = $request->staffId | |
| ) as absentees | |
| FROM subjectplan_ps_proposed sp | |
| LEFT JOIN ps_subjectplan subp ON sp.topicID=subp.proposed_topicID | |
| inner join pseudosubjects_sbs psbs on psbs.pseudosubjectID = sp.pssId | |
| INNER JOIN sbs_relation sr ON sr.sbsID = psbs.sbsID | |
| INNER JOIN staffaccounts sa on sa.staffID = sr.staffID | |
| WHERE 1=1 $cond"; | |
| } | |
| else{ | |
| if($request->sbsId){ | |
| $cond .=" AND sr.sbsID = $request->sbsId "; | |
| } | |
| if($request->mode){ | |
| $cond .=" AND sp.mode = '$request->mode' "; | |
| } | |
| $sql = "SELECT sp.sbsID,sp.topicID AS proposedPlanId,subp.topicID AS actualPlanId,FROM_UNIXTIME(sp.date, '%Y-%m-%d') AS subjectPlanDate, sp.hour, sp.module, sp.topicName, null as subjectType,sp.mode as mode_of_instruction,sp.content as content, | |
| CASE | |
| WHEN subp.isCovered = '0' THEN 'Not covered' | |
| WHEN subp.isCovered = '1' THEN 'Partially covered' | |
| WHEN subp.isCovered = '2' THEN 'Fully covered' | |
| else null | |
| END AS covered, sa.staffName, | |
| (select group_concat(sa.rollNo) | |
| from studentaccount sa left join attendance at on sa.studentID = at.studentID | |
| where | |
| at.attendanceDate = subjectPlanDate and at.isAbsent = 1 | |
| and at.hour = sp.hour and at.sbsID = sp.sbsID | |
| ) as absentees | |
| FROM subjectplan_proposed sp | |
| INNER JOIN subjectplan subp ON sp.topicID = subp.proposed_topicID | |
| INNER JOIN sbs_relation sr ON sr.sbsID = subp.sbsID | |
| INNER JOIN staffaccounts sa on sa.staffID = sr.staffID | |
| WHERE 1=1 $cond"; | |
| } | |
| try { | |
| return $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| public function getSubjectActualPlanReportMoreDetails($request){ | |
| $sql= "SELECT sp.date, | |
| sp.hour, | |
| sp.module, | |
| sp.topicName, | |
| sp.isCovered, | |
| sp.mode, | |
| sp.day, | |
| sp.session, | |
| sppr.pedagogy, | |
| sp.modeOfeLearning, | |
| GROUP_CONCAT(distinct lr.path) as url, | |
| GROUP_CONCAT(distinct plr.path) as proposedUrl | |
| from subjectplan sp | |
| INNER JOIN sbs_relation sr on sp.sbsID = sr.sbsID | |
| LEFT JOIN subjectplan_proposed sppr ON sppr.topicID = sp.topicID | |
| LEFT JOIN subject_actual_plan_video_materials sapvm on sp.topicID = sapvm.subject_actual_plan_id | |
| LEFT JOIN course_video_materials cvm on sapvm.video_repository_id = cvm.id | |
| LEFT JOIN lin_resource lr ON lr.id = cvm.resource_id | |
| LEFT JOIN subjectplan_proposed spp ON spp.topicID=sp.proposed_topicID | |
| LEFT JOIN subject_proposed_plan_video_materials spvm ON spp.topicID=spvm.subject_proposed_plan_id | |
| LEFT JOIN course_video_materials pcvm on spvm.video_repository_id = pcvm.id | |
| LEFT JOIN lin_resource plr ON plr.id = pcvm.resource_id | |
| where sr.staffID = $request->staffId and sr.subjectID = $request->subjectId and sr.batchID = $request->batchId" ; | |
| if($request->subBatchId) | |
| { | |
| $sql .= " AND sp.subbatchID = $request->subBatchId "; | |
| } | |
| if($request->dateSortBy) | |
| { | |
| $sql .= " group by sp.topicID ORDER BY sp.date $request->dateSortBy , sp.hour, sp.session"; | |
| } | |
| try { | |
| return $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * Get proposed subject plan of all subjects of same staffs from community by sbsId | |
| * @param Integer $sbsId | |
| * @return Object $subjectplan_proposed | |
| * @author Ajay C | |
| */ | |
| public function getProposedSubjectPlanFromCommunityByDiferentSubject($sbsId) | |
| { | |
| $sbsId = (int)$this->realEscapeString($sbsId); | |
| $subjectplan_community = null; | |
| try { | |
| $sql = "SELECT sa.staffName, | |
| b.batchName, | |
| count(spp.topicID) AS proposedplans, | |
| sbs.sbsID | |
| FROM staffaccounts sa | |
| INNER JOIN sbs_relation sbs ON (sa.staffID = sbs.staffID) | |
| INNER JOIN batches b ON (b.batchID = sbs.batchID) | |
| INNER JOIN subjectplan_proposed_community spc ON (spc.sbs_id = sbs.sbsID) | |
| LEFT JOIN subjectplan_proposed spp ON (spp.sbsID = sbs.sbsID) | |
| WHERE sbs.subjectID IN (SELECT subjectID from sbs_relation sr where staffID in(SELECT staffID FROM sbs_relation WHERE sbsID = '$sbsId') and semID in (SELECT semID FROM sbs_relation WHERE sbsID = '$sbsId')) | |
| AND (spc.is_shared = 1 OR spc.is_other = 1) | |
| AND sbs.subjectID NOT IN (SELECT subjectID FROM sbs_relation WHERE sbsID = '$sbsId') | |
| GROUP BY spp.sbsID;"; | |
| $subjectplan_community = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $subjectplan_community; | |
| } | |
| } |