Code Coverage |
||||||||||
Classes and Traits |
Functions and Methods |
Lines |
||||||||
| Total | |
0.00% |
0 / 1 |
|
0.00% |
0 / 71 |
CRAP | |
0.00% |
0 / 2657 |
| ExamRegistrationService | |
0.00% |
0 / 1 |
|
0.00% |
0 / 71 |
177662.00 | |
0.00% |
0 / 2656 |
| __construct | |
0.00% |
0 / 1 |
2.00 | |
0.00% |
0 / 3 |
|||
| saveExamRegistration | |
0.00% |
0 / 1 |
56.00 | |
0.00% |
0 / 38 |
|||
| saveSupplementaryExamRegistration | |
0.00% |
0 / 1 |
2.00 | |
0.00% |
0 / 5 |
|||
| saveRegularExamRegistration | |
0.00% |
0 / 1 |
2.00 | |
0.00% |
0 / 5 |
|||
| saveRevaluationExamRegistration | |
0.00% |
0 / 1 |
2.00 | |
0.00% |
0 / 5 |
|||
| saveImprovementExamRegistration | |
0.00% |
0 / 1 |
2.00 | |
0.00% |
0 / 5 |
|||
| validateSaveExamRegistration | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 6 |
|||
| insertExamRegistration | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 16 |
|||
| updateExamRegistration | |
0.00% |
0 / 1 |
30.00 | |
0.00% |
0 / 23 |
|||
| deleteExamRegistration | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 33 |
|||
| searchExamRegistration | |
0.00% |
0 / 1 |
462.00 | |
0.00% |
0 / 89 |
|||
| publishExamRegistration | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 20 |
|||
| searchStudentRegularExamSubjectDetails | |
0.00% |
0 / 1 |
72.00 | |
0.00% |
0 / 53 |
|||
| searchExamRegistrationByOtherDetails | |
0.00% |
0 / 1 |
420.00 | |
0.00% |
0 / 105 |
|||
| getAllAssignedExamRegistrationByCurrentStaff | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 28 |
|||
| getAllAssignedExamRegistrationValuationCountByCurrentStaff | |
0.00% |
0 / 1 |
272.00 | |
0.00% |
0 / 57 |
|||
| getExamDateOfExamRegistration | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 21 |
|||
| getExamTimeOfExamRegistration | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 26 |
|||
| getAcademicTermsByExamRegistration | |
0.00% |
0 / 1 |
72.00 | |
0.00% |
0 / 38 |
|||
| getGrdeSchemeByExamRegistration | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 26 |
|||
| getAllSpecialExamRegistration | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 30 |
|||
| getAllExamRegistredStudentDetails | |
0.00% |
0 / 1 |
90.00 | |
0.00% |
0 / 67 |
|||
| saveBatchGroupForExamRevaluation | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 19 |
|||
| insertBatchGroupForRevaluation | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 14 |
|||
| updateBatchGroupForRevaluation | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 22 |
|||
| deleteBatchGroupForRevaluation | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 14 |
|||
| searchStudentExamRevaluation | |
0.00% |
0 / 1 |
182.00 | |
0.00% |
0 / 113 |
|||
| getRevaluationTypes | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 15 |
|||
| searchDetailedExamRegistrationDetails | |
0.00% |
0 / 1 |
506.00 | |
0.00% |
0 / 147 |
|||
| getMonthName | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 16 |
|||
| getAcademicTermIdByExamRegistrationId | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 24 |
|||
| getAllStudentDetailsForSpecialExam | |
0.00% |
0 / 1 |
56.00 | |
0.00% |
0 / 77 |
|||
| getAllStudentDetailsListForSpecialExamRegistration | |
0.00% |
0 / 1 |
30.00 | |
0.00% |
0 / 64 |
|||
| getAllDegreesByExamRegistration | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 28 |
|||
| getAllAssessmentDatesByExamRegistration | |
0.00% |
0 / 1 |
30.00 | |
0.00% |
0 / 29 |
|||
| getCurrentExamRevaluationDetails | |
0.00% |
0 / 1 |
42.00 | |
0.00% |
0 / 47 |
|||
| saveExamRegistrationFeeTemplate | |
0.00% |
0 / 1 |
42.00 | |
0.00% |
0 / 46 |
|||
| createExamRegistrationFeeTemplate | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 14 |
|||
| updateExamRegistrationFeeTemplate | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 14 |
|||
| getExamRegistrationType | |
0.00% |
0 / 1 |
90.00 | |
0.00% |
0 / 32 |
|||
| searchExamRegistrationForFilter | |
0.00% |
0 / 1 |
110.00 | |
0.00% |
0 / 56 |
|||
| searchAllExamRegistration | |
0.00% |
0 / 1 |
272.00 | |
0.00% |
0 / 65 |
|||
| getStudentExamAssignedDetails | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 22 |
|||
| getRevaluationStudentAssignedDetails | |
0.00% |
0 / 1 |
30.00 | |
0.00% |
0 / 37 |
|||
| getAllExamRevaluation | |
0.00% |
0 / 1 |
306.00 | |
0.00% |
0 / 80 |
|||
| getExamRegistrationOfABatchByRequest | |
0.00% |
0 / 1 |
42.00 | |
0.00% |
0 / 33 |
|||
| getExamRegistrationStudentListByExamRegId | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 32 |
|||
| getAllAssignedExamRevaluationByCurrentStaff | |
0.00% |
0 / 1 |
56.00 | |
0.00% |
0 / 51 |
|||
| getAllAssignedExamRevaluationValuationCountByCurrentStaff | |
0.00% |
0 / 1 |
72.00 | |
0.00% |
0 / 41 |
|||
| publishExamTimeTableInStudentSide | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 32 |
|||
| getExamRegistrationCount | |
0.00% |
0 / 1 |
156.00 | |
0.00% |
0 / 46 |
|||
| supplyExamMonthAndYearSemesterWiseByStudent | |
0.00% |
0 / 1 |
72.00 | |
0.00% |
0 / 50 |
|||
| getExamMonthYearsOfBatchGroup | |
0.00% |
0 / 1 |
30.00 | |
0.00% |
0 / 26 |
|||
| saveInstitutionalAverageGenerateStatus | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 31 |
|||
| getStaffHandlingDepartmentExamRegistrations | |
0.00% |
0 / 1 |
72.00 | |
0.00% |
0 / 55 |
|||
| saveRevaluationMemoDetails | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 14 |
|||
| getExamRegistrationStartYears | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 29 |
|||
| getRecentPublishedExamRegistration | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 21 |
|||
| getBatchAssignedExamRegistration | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 30 |
|||
| getSupplyPublishDetailsByBatch | |
0.00% |
0 / 1 |
90.00 | |
0.00% |
0 / 48 |
|||
| getAllExamRegistrationForVivaValuationStaff | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 32 |
|||
| getMinorHonorMoocAppliedStudents | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 26 |
|||
| getCourseTypeOfExamRegistration | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 30 |
|||
| getMoocAppliedStudentDetails | |
0.00% |
0 / 1 |
72.00 | |
0.00% |
0 / 61 |
|||
| getCurrentExamRegistrationDetailsForPaymentModule | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 15 |
|||
| searchDetailedExamRevaluationDetails | |
0.00% |
0 / 1 |
240.00 | |
0.00% |
0 / 130 |
|||
| getStudentCourseTypeByExamRegistration | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 27 |
|||
| getPaymentGateWayModulesForCourseTypes | |
0.00% |
0 / 1 |
56.00 | |
0.00% |
0 / 16 |
|||
| getRegularExamRegistrationIdBySupplyExamRegistrationId | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 27 |
|||
| updateFalseNumberQueueUpdateStatus | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 20 |
|||
| searchAllProgramsByExamRegistration | |
0.00% |
0 / 1 |
56.00 | |
0.00% |
0 / 39 |
|||
| <?php | |
| namespace com\linways\ec\core\service; | |
| use com\linways\ec\core\dto\ExamRegistration; | |
| use com\linways\base\util\MakeSingletonTrait; | |
| use com\linways\base\util\SecurityUtils; | |
| use com\linways\ec\core\constant\StatusConstants; | |
| use com\linways\ec\core\exception\ExamControllerException; | |
| use com\linways\ec\core\mapper\ExamRegistrationServiceMapper; | |
| use com\linways\core\ams\professional\logging\AMSLogger; | |
| use com\linways\ec\core\logging\Events; | |
| use com\linways\ec\core\logging\entities\Staff; | |
| use com\linways\ec\core\request\SearchExamRegistrationRequest; | |
| use com\linways\ec\core\constant\ExamRegistrationTypeConstants; | |
| use com\linways\ec\core\request\SearchStudentExamRegistrationRequest; | |
| use com\linways\ec\core\service\ExamRegistrationSubjectService; | |
| use com\linways\base\util\TwigRenderer; | |
| use com\linways\core\ams\professional\util\PdfUtil; | |
| use com\linways\ec\core\dto\ExamRegistrationFeeTemplate; | |
| use com\linways\core\ams\professional\constant\academic\GroupTypeConstant as ProfessionalGroupTypeConstant; | |
| use com\linways\ec\core\service\CommonExamService; | |
| use com\linways\core\ams\professional\constant\SettingsConstants; | |
| use com\linways\core\ams\professional\service\CommonService; | |
| use com\linways\ec\core\request\SearchRuleRequest; | |
| use com\linways\ec\core\service\RuleService; | |
| use com\linways\ec\core\service\ExamValuationService; | |
| use com\linways\core\ams\professional\service\CourseTypeService; | |
| use com\linways\ec\core\service\ExamFeeTypeService; | |
| use stdClass; | |
| use function PHPSTORM_META\type; | |
| class ExamRegistrationService extends BaseService | |
| { | |
| use MakeSingletonTrait; | |
| private function __construct() { | |
| $this->logger = AMSLogger::getLogger('exam-controller-log'); | |
| $this->mapper = ExamRegistrationServiceMapper::getInstance()->getMapper(); | |
| } | |
| /** | |
| * Save ExamRegistration | |
| * @param ExamRegistration $examRegistration | |
| * @return $id | |
| */ | |
| public function saveExamRegistration (ExamRegistration $examRegistration) | |
| { | |
| $examRegistration = $this->realEscapeObject($examRegistration); | |
| $examRegistration->createdBy = $GLOBALS['userId'] ?? $examRegistration->createdBy; | |
| $examRegistration->updatedBy = $GLOBALS['userId'] ?? $examRegistration->updatedBy; | |
| $staffId = $GLOBALS['userId']; | |
| try{ | |
| $this->validateSaveExamRegistration($examRegistration); | |
| if(!empty($examRegistration->id)) | |
| { | |
| $examRegistration->id = $this->updateExamRegistration($examRegistration); | |
| } | |
| else | |
| { | |
| $examRegistration->id = $this->insertExamRegistration($examRegistration); | |
| } | |
| AMSLogger::log_info($this->logger,Events::EC_SAVE_EXAM_REGISTRATION,[ | |
| "staff" => new Staff(["id" => $staffId]), | |
| "request" => $examRegistration, | |
| "status" => StatusConstants::SUCCESS | |
| ]); | |
| }catch(\Exception $e) { | |
| AMSLogger::log_error($this->logger,Events::EC_SAVE_EXAM_REGISTRATION, [ | |
| "staff" => new Staff(["id" => $staffId]), | |
| "request" => $examRegistration, | |
| "errorCode" => $e->getCode(), | |
| "errorMessage" => $e->getMessage(), | |
| "status" => StatusConstants::FAILED | |
| ]); | |
| if($e->getCode() !== ExamControllerException::INVALID_PARAMETERS_EXAM_REGISTRATION && $e->getCode() !== ExamControllerException::EMPTY_PARAMETERS_EXAM_REGISTRATION && $e->getCode() !== "DUPLICATE_ENTRY") { | |
| throw new ExamControllerException($e->getCode(),"Failed to save Exam Registration! Please try again"); | |
| } else if ($e->getCode() === ExamControllerException::DUPLICATE_ENTRY) { | |
| throw new ExamControllerException (ExamControllerException::DUPLICATE_ENTRY_EXAM_REGISTRATION,"Cannot create Exam Registration.This Exam is already registered!"); | |
| } else { | |
| throw new ExamControllerException ($e->getCode(),$e->getMessage()); | |
| } | |
| } | |
| return $examRegistration->id ; | |
| } | |
| /** | |
| * Save Supplementary ExamRegistration | |
| * @param ExamRegistration $examRegistration | |
| * @return $id | |
| */ | |
| public function saveSupplementaryExamRegistration (ExamRegistration $examRegistration) | |
| { | |
| $examRegistration = $this->realEscapeObject($examRegistration); | |
| $examRegistration->type = ExamRegistrationTypeConstants::SUPPLEMENTARY; | |
| $this->saveExamRegistration($examRegistration); | |
| } | |
| /** | |
| * Save Regular Exam Registration | |
| * @param ExamRegistration $examRegistration | |
| * @return $id | |
| */ | |
| public function saveRegularExamRegistration (ExamRegistration $examRegistration) | |
| { | |
| $examRegistration = $this->realEscapeObject($examRegistration); | |
| $examRegistration->type = ExamRegistrationTypeConstants::REGULAR; | |
| $this->saveExamRegistration($examRegistration); | |
| } | |
| /** | |
| * Save Revaluation Exam Registration | |
| * @param ExamRegistration $examRegistration | |
| * @return $id | |
| */ | |
| public function saveRevaluationExamRegistration (ExamRegistration $examRegistration) | |
| { | |
| $examRegistration = $this->realEscapeObject($examRegistration); | |
| $examRegistration->type = ExamRegistrationTypeConstants::REVALUATION; | |
| $this->saveExamRegistration($examRegistration); | |
| } | |
| /** | |
| * Save Improvement Exam Registration | |
| * @param ExamRegistration $examRegistration | |
| * @return $id | |
| */ | |
| public function saveImprovementExamRegistration (ExamRegistration $examRegistration) | |
| { | |
| $examRegistration = $this->realEscapeObject($examRegistration); | |
| $examRegistration->type = ExamRegistrationTypeConstants::IMPROVEMENT; | |
| $this->saveExamRegistration($examRegistration); | |
| } | |
| /** | |
| * Validate examRegistration Request Before Saving | |
| * @param ExamRegistration $examRegistration | |
| * @return NULL | |
| */ | |
| private function validateSaveExamRegistration(ExamRegistration $examRegistration) | |
| { | |
| if(empty($examRegistration->name)) | |
| throw new ExamControllerException(ExamControllerException::EMPTY_PARAMETERS_EXAM_REGISTRATION," Exam Registration name is empty! Please choose name for Exam Registration"); | |
| if(empty($examRegistration->type)) | |
| throw new ExamControllerException(ExamControllerException::EMPTY_PARAMETERS_EXAM_REGISTRATION," Exam Registration type is empty! Please choose any type for Exam Registration"); | |
| } | |
| /** | |
| * Insert ExamRegistration | |
| * @param ExamRegistration $examRegistration | |
| * @return $id | |
| */ | |
| private function insertExamRegistration(ExamRegistration $examRegistration) | |
| { | |
| $properties = !empty($examRegistration->properties) ? "'" . json_encode($examRegistration->properties) . "'" : "NULL"; | |
| $identifyingContext = !empty($examRegistration->identifyingContext) ? "'" . json_encode($examRegistration->identifyingContext) . "'" : "NULL"; | |
| $id = SecurityUtils::getRandomString(); | |
| $query = "INSERT INTO ec_exam_registration | |
| (id,identifying_context,name,type,properties,created_by,updated_by) | |
| VALUES | |
| ('$id',$identifyingContext,'$examRegistration->name','$examRegistration->type',$properties,'$examRegistration->createdBy','$examRegistration->updatedBy')"; | |
| try { | |
| $this->executeQuery($query); | |
| $examRegistration->id = $id; | |
| return $examRegistration->id; | |
| } catch (\Exception $e) { | |
| throw new ExamControllerException($e->getCode(),$e->getMessage()); | |
| } | |
| } | |
| /** | |
| * Update ExamRegistration | |
| * @param ExamRegistration $examRegistration | |
| * @return String $examRegistration->id | |
| */ | |
| private function updateExamRegistration(ExamRegistration $examRegistration) | |
| { | |
| $properties = !empty($examRegistration->properties) ? "'".json_encode($examRegistration->properties)."'" : "NULL"; | |
| $feeProperties = !empty($examRegistration->feesProperties) ? "'".json_encode($examRegistration->feesProperties)."'" : "NULL"; | |
| $identifyingContext = !empty($examRegistration->identifyingContext) ? "'" . json_encode($examRegistration->identifyingContext) . "'" : "NULL"; | |
| $query = "UPDATE | |
| ec_exam_registration | |
| SET | |
| identifying_context = $identifyingContext, | |
| name = '$examRegistration->name', | |
| type = '$examRegistration->type', | |
| properties = $properties, | |
| fees_properties = $feeProperties, | |
| updated_by = '$examRegistration->updatedBy' | |
| WHERE | |
| id = '$examRegistration->id'"; | |
| try | |
| { | |
| $this->executeQuery($query); | |
| return $examRegistration->id; | |
| } catch (\Exception $e) { | |
| throw new ExamControllerException($e->getCode(),$e->getMessage()); | |
| } | |
| } | |
| /** | |
| * Delete examRegistration (Soft Delete) | |
| * @param String $id | |
| * @return NULL | |
| */ | |
| public function deleteExamRegistration($id) | |
| { | |
| $id = $this->realEscapeString($id); | |
| $staffId = $GLOBALS['userId']; | |
| $searchRequest = new SearchExamRegistrationRequest(); | |
| $searchRequest->id = $id; | |
| $examRegistration = reset($this->searchExamRegistration($searchRequest)); | |
| if(empty($id) && empty($examRegistration)) | |
| throw new ExamControllerException(ExamControllerException::EMPTY_PARAMETERS_EXAM_REGISTRATION,"Exam Registration is invalid! Please enter a valid Exam Registration"); | |
| $query = "UPDATE | |
| ec_exam_registration | |
| SET | |
| trashed = UTC_TIMESTAMP(), | |
| updated_by = '$staffId' | |
| WHERE | |
| id = '$id'"; | |
| try { | |
| $this->executeQuery($query); | |
| AMSLogger::log_info($this->logger,Events::EC_DELETE_EXAM_REGISTRATION,[ | |
| "staff" => new Staff(["id" => $staffId]), | |
| "request" => $examRegistration, | |
| "status" => StatusConstants::SUCCESS | |
| ]); | |
| } catch (\Exception $e) { | |
| AMSLogger::log_error($this->logger,Events::EC_DELETE_EXAM_REGISTRATION,[ | |
| "staff" => new Staff(["id" => $staffId]), | |
| "request" => $examRegistration, | |
| "errorCode" => $e->getCode(), | |
| "errorMessage" => $e->getMessage(), | |
| "status" => StatusConstants::FAILED | |
| ]); | |
| throw new ExamControllerException(ExamControllerException::ERROR_DELETING_EXAM_REGISTRATION,"Error deleting Exam Registration! Please try again"); | |
| } | |
| } | |
| /** | |
| * Search examRegistration | |
| * @param SearchExamRegistrationRequest $request | |
| * @return examRegistration | |
| */ | |
| public function searchExamRegistration(SearchExamRegistrationRequest $request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $whereQuery = ""; | |
| $limitQuery = ""; | |
| $joinQueary = ""; | |
| $sortBy = " ORDER BY eer.created_date DESC"; | |
| if(!empty($request->id)){ | |
| $whereQuery .= " AND eer.id='$request->id' "; | |
| } | |
| if(!empty($request->name)){ | |
| $whereQuery .= " AND eer.name LIKE '%$request->name%'"; | |
| } | |
| if(!empty($request->type)) { | |
| $examTypeString= "'" . implode("', '", $request->type) . "'" ; | |
| $whereQuery .= " AND eer.type IN ($examTypeString)"; | |
| } | |
| if(!empty($request->parentExamRegistrationId)) { | |
| $whereQuery .= " AND CAST(eer.properties->>'$.parentExamRegistrationId' AS CHAR) = '$request->parentExamRegistrationId'"; | |
| } | |
| if($request->trashed === StatusConstants::ACTIVE) { | |
| $whereQuery .= " AND eer.trashed IS NULL "; | |
| } | |
| if($request->trashed === StatusConstants::TRASHED) { | |
| $whereQuery .= " AND eer.trashed IS NOT NULL "; | |
| } | |
| if($request->startIndex !== "" && $request->endIndex !== ""){ | |
| $limitQuery .= " LIMIT $request->startIndex,$request->endIndex"; | |
| } | |
| if(!empty($request->courseTypeId) || !empty($request->startYear) || !empty($request->currentTermId)) { | |
| $joinQueary = " INNER JOIN ec_exam_registration_batch eerb | |
| ON eerb.ec_exam_registration_id = eer.id | |
| INNER JOIN `groups` g | |
| ON g.id = eerb.groups_id | |
| INNER JOIN program p | |
| ON p.id = g.properties->>'$.programId'"; | |
| if(!empty($request->courseTypeId)) { | |
| $whereQuery .= " AND p.course_type_id = '$request->courseTypeId'"; | |
| } | |
| if(!empty($request->startYear)) { | |
| $whereQuery .= " AND g.properties ->> '$.startYear' = '$request->startYear'"; | |
| } | |
| if(!empty($request->currentTermId)) { | |
| $whereQuery .= " AND CAST(eerb.properties ->> '$.academicTermId'AS CHAR) = '$request->currentTermId' "; | |
| } | |
| } | |
| if(($request->isPublishedOnlyRegistration)) { | |
| $whereQuery .= " AND eer.properties->>'$.publish' = '1' "; | |
| } | |
| else if(($request->isNotPublishedOnlyRegistration)) { | |
| $whereQuery .= " AND eer.properties->>'$.publish' = '0' "; | |
| } | |
| if(!empty($request->ids)){ | |
| $idString = is_array($request->ids) ? "'" . implode("','",$request->ids) . "'" : "'".$request->ids."'"; | |
| $whereQuery .= " AND eer.id IN ( $idString )"; | |
| } | |
| if($request->resultPublishedRegistrationOnly) { | |
| $joinQueary = " INNER JOIN ec_exam_registration_batch eerb | |
| ON eerb.ec_exam_registration_id = eer.id "; | |
| $whereQuery .= " AND eerb.properties ->>'$.isResultPublished' = 1 "; | |
| } | |
| $query = "SELECT | |
| eer.id, | |
| eer.identifying_context, | |
| eer.name, | |
| eer.type, | |
| eer.properties, | |
| eer.fees_properties, | |
| eer.trashed, | |
| eer.created_by, | |
| eer.created_date, | |
| eer.updated_by, | |
| eer.updated_date, | |
| eerp.id as parentExamRegistrationId, | |
| eerp.name as parentExamRegistrationName, | |
| eerp.type as parentExamRegistrationType | |
| FROM | |
| ec_exam_registration eer | |
| LEFT JOIN ec_exam_registration eerp ON | |
| eerp.id = CAST(eer.properties->>'$.parentExamRegistrationId' AS CHAR) | |
| $joinQueary | |
| WHERE | |
| 1 = 1"; | |
| try { | |
| $examRegistration = $this->executeQueryForList($query.$whereQuery.$sortBy.$limitQuery, $this->mapper[ExamRegistrationServiceMapper::SEARCH_EXAM_REGISTRATION]); | |
| } catch (\Exception $e) { | |
| throw new ExamControllerException(ExamControllerException::ERROR_FETCHING_EXAM_REGISTRATION,"Cannot fetch Exam Registration details! Please try again."); | |
| } | |
| return $examRegistration; | |
| } | |
| /** | |
| * Publish examRegistration | |
| * @param String $id | |
| * @param Boolean $publish | |
| * @return NULL | |
| */ | |
| public function publishExamRegistration($id, $publish) | |
| { | |
| $id = $this->realEscapeString($id); | |
| $publish = $this->realEscapeString($publish); | |
| $updatedBy = $GLOBALS['userId']; | |
| if(empty($id)) | |
| throw new ExamControllerException(ExamControllerException::EMPTY_PARAMETERS_EXAM_REGISTRATION,"Exam Registration is invalid! Please enter a valid Exam Registration"); | |
| $publish = $publish ? 1 : 0; | |
| $query = "UPDATE | |
| ec_exam_registration | |
| SET | |
| properties = JSON_SET(properties, '$.publish', $publish), | |
| updated_by = '$updatedBy' | |
| WHERE | |
| id = '$id'"; | |
| try { | |
| $this->executeQuery($query); | |
| } catch (\Exception $e) { | |
| throw new ExamControllerException(ExamControllerException::ERROR_UPDATE_PUBLISH_STATUS_EXAM_REGISTRATION,"Error deleting Exam Registration! Please try again"); | |
| } | |
| } | |
| /** | |
| * Search Student registration Details | |
| * @param $request | |
| * @return $subjects | |
| */ | |
| private function searchStudentRegularExamSubjectDetails($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $whereQuery = ""; | |
| $limitQuery = ""; | |
| if(!empty($request->id)) { | |
| $whereQuery .= " AND ecscmd.id = '$request->id'"; | |
| } | |
| if(!empty($request->studentId)) { | |
| $whereQuery .= " AND ecscmd.student_id = '$request->studentId'"; | |
| } | |
| if(!empty($request->groupId)) { | |
| $whereQuery .= " AND ecscmd.groups_id = '$request->groupId'"; | |
| } | |
| if(!empty($request->paperSubjectId)) { | |
| $paperSubjectIdString = is_array($request->paperSubjectId) ? "'" . implode("','",$request->paperSubjectId) . "'" : '$request->paperSubjectId'; | |
| $whereQuery .= " ecscmd.paper_subject_id IN($paperSubjectIdString)"; | |
| } | |
| // if($request->startIndex !== "" && $request->endIndex !== "") | |
| // { | |
| // $limitQuery .= " LIMIT $request->startIndex,$request->endIndex"; | |
| // } | |
| $query = "SELECT | |
| ecscmd.id, | |
| ecscmd.student_id, | |
| ecscmd.mark_details, | |
| ecscmd.mark_history, | |
| ecscmd.no_of_chances_taken, | |
| ecscmd.total_marks, | |
| ecscmd.percentage, | |
| ecscmd.grade, | |
| ecscmd.grade_point, | |
| ecscmd.credit_grade_point, | |
| ecscmd.class, | |
| ecscmd.status, | |
| ecscmd.is_dirty, | |
| ecscmd.created_by, | |
| ecscmd.created_date, | |
| ecscmd.updated_by, | |
| ecscmd.updated_date, | |
| ecscmd.paper_subject_id, | |
| ecscmd.groups_id | |
| FROM | |
| ec_subject_consolidated_mark_details ecscmd | |
| WHERE | |
| 1 = 1 | |
| "; | |
| try { | |
| $subjectDetails = $this->executeQueryForList($query.$whereQuery.$limitQuery,$this->mapper[ExamRegistrationServiceMapper::SEARCH_STUDENT_SUBJECTS_MARK_DETAILS]); | |
| } catch (\Exception $e) { | |
| throw new ExamControllerException(ExamControllerException::ERROR_FETCHING_STUDENT_REGISTRATION,"Cannot fetch studentRegistration details! Please try again"); | |
| } | |
| $subjects = []; | |
| foreach ($subjectDetails as $subject) { | |
| $subjects[$subject->paperSubjectId] = $subject; | |
| } | |
| return $subjects; | |
| } | |
| /** | |
| * Search examRegistration | |
| * @param $request | |
| * @return examRegistration | |
| */ | |
| public function searchExamRegistrationByOtherDetails( $request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| if($request->odrerBy == "ASSENDING_EXAM_MONTH_AND_YEAR"){ | |
| $orderBy = " ORDER BY eer.properties ->> '$.examYear' ASC ,eer.properties ->> '$.examMonth' + 0 ASC "; | |
| } | |
| else{ | |
| $orderBy = " ORDER BY eer.properties ->> '$.examYear' DESC ,eer.properties ->> '$.examMonth' + 0 DESC "; | |
| } | |
| $whereQuery = ""; | |
| $limitQuery = ""; | |
| $joinQueary = ""; | |
| if(!empty($request->id)){ | |
| $whereQuery .= " AND eer.id='$request->id' "; | |
| } | |
| if(!empty($request->name)){ | |
| $whereQuery .= " AND eer.name LIKE '%$request->name%'"; | |
| } | |
| if(!empty($request->examYear)){ | |
| $whereQuery .= " AND eer.properties ->> '$.examYear' = '$request->examYear'"; | |
| } | |
| if(!empty($request->courseTypeId)){ | |
| $whereQuery .= " AND p.course_type_id = '$request->courseTypeId' "; | |
| } | |
| if(!empty($request->groupId)){ | |
| $groupIdString = is_array($request->groupId) ? "'" . implode("','",$request->groupId) . "'" : "'".$request->groupId."'"; | |
| $whereQuery .= " AND g.id IN ( $groupIdString )"; | |
| } | |
| if(!empty($request->academicTermId)){ | |
| $whereQuery .= " AND CAST(eerb.properties ->> '$.academicTermId'AS CHAR) = '$request->academicTermId'"; | |
| } | |
| if(!empty($request->batchStartYear)){ | |
| $whereQuery .= " AND g.properties ->> '$.startYear' = '$request->batchStartYear'"; | |
| } | |
| if(!empty($request->type)) { | |
| $examTypeString = is_array($request->type) ? "'" . implode("','",$request->type) . "'" : "'".$request->type."'"; | |
| $whereQuery .= " AND eer.type IN ( $examTypeString )"; | |
| } | |
| if($request->isSpecialExamRegistration){ | |
| $whereQuery .= " AND eer.properties->>'$.isSpecialExam' = true "; | |
| } | |
| if($request->isMinorOrHonorExamRegistration){ | |
| $whereQuery .= " AND (eer.properties ->> '$.isHonorCourse' = '1' OR eer.properties ->> '$.isMinorCourse' = '1')"; | |
| } | |
| if($request->isAvoidMinorHonorExamRegistration){ | |
| $whereQuery .= " AND ( (eer.properties ->> '$.isHonorCourse' IS NULL OR eer.properties ->> '$.isHonorCourse' != '1') AND (eer.properties ->> '$.isMinorCourse' IS NULL OR eer.properties ->> '$.isMinorCourse' != '1')) "; | |
| } | |
| // else{ | |
| // $whereQuery .= " AND ( (eer.properties ->> '$.isHonorCourse' IS NULL OR eer.properties ->> '$.isHonorCourse' != '1') AND (eer.properties ->> '$.isMinorCourse' IS NULL OR eer.properties ->> '$.isMinorCourse' != '1'))"; | |
| // } | |
| if(!empty($request->registerNo)) { | |
| $joinQueary = " INNER JOIN ec_exam_registration_subject eers ON | |
| eers.ec_exam_registration_batch_id = eerb.id | |
| INNER JOIN ec_student_assessment_registration esar ON | |
| esar.am_assessment_id = eers.am_assessment_id AND esar.ec_exam_registration_type = eer.type | |
| INNER JOIN studentaccount sa ON | |
| sa.studentID = esar.student_id | |
| INNER JOIN student_program_account spa ON | |
| spa.student_id = esar.student_id AND | |
| spa.current_program_id = p.id "; | |
| $whereQuery .= " AND spa.properties->>'$.registerNumber' LIKE '%$request->registerNo%'"; | |
| } | |
| if(!empty($request->rollNo)) { | |
| $joinQueary = " INNER JOIN ec_exam_registration_subject eers ON | |
| eers.ec_exam_registration_batch_id = eerb.id | |
| INNER JOIN ec_student_assessment_registration esar ON | |
| esar.am_assessment_id = eers.am_assessment_id AND esar.ec_exam_registration_type = eer.type | |
| INNER JOIN studentaccount sa ON | |
| sa.studentID = esar.student_id | |
| INNER JOIN student_program_account spa ON | |
| spa.student_id = esar.student_id AND | |
| spa.current_program_id = p.id "; | |
| $whereQuery .= " AND spa.properties->>'$.rollNumber' LIKE '%$request->rollNo%'"; | |
| } | |
| if(!empty($request->typeForRevaluation)) { | |
| $joinQueary .= " INNER JOIN ec_exam_registration eerp ON | |
| eerp.id = TRIM(BOTH "."'\"'"." FROM eer.parent_registration_id)"; | |
| $whereQuery .= " AND eerp.type LIKE '%$request->typeForRevaluation%'"; | |
| } | |
| if(!empty($request->examDate)) { | |
| $joinQueary .= " INNER JOIN ec_exam_registration_subject eers ON | |
| eers.ec_exam_registration_batch_id = eerb.id | |
| INNER JOIN am_assessment aa ON | |
| aa.id = eers.am_assessment_id"; | |
| $whereQuery .= " AND aa.properties_value ->>'$.assessmentDate' = '$request->examDate'"; | |
| } | |
| $query = "SELECT | |
| DISTINCT(eer.id), | |
| eer.name, | |
| eer.properties, | |
| eer.type | |
| FROM | |
| ec_exam_registration eer | |
| INNER JOIN ec_exam_registration_batch eerb | |
| ON eerb.ec_exam_registration_id = eer.id | |
| INNER JOIN `groups` g | |
| ON g.id = eerb.groups_id | |
| INNER JOIN program p | |
| ON p.id = g.properties->>'$.programId' | |
| $joinQueary | |
| WHERE | |
| eer.trashed IS NULL"; | |
| try { | |
| $examRegistration = $this->executeQueryForList($query.$whereQuery.$orderBy.$limitQuery, $this->mapper[ExamRegistrationServiceMapper::SEARCH_EXAM_REGISTRATION]); | |
| } catch (\Exception $e) { | |
| throw new ExamControllerException(ExamControllerException::ERROR_FETCHING_EXAM_REGISTRATION,"Cannot fetch Exam Registration details! Please try again."); | |
| } | |
| return $examRegistration; | |
| } | |
| /** | |
| * Search examRegistration | |
| * @param $request | |
| * @return examRegistration | |
| */ | |
| public function getAllAssignedExamRegistrationByCurrentStaff( $request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $currentUerId = $GLOBALS['userId']; | |
| $whereQuery = ""; | |
| $limitQuery = ""; | |
| if(!empty($request->type)) | |
| { | |
| $whereQuery .= " AND eer.type LIKE '%$request->type%'"; | |
| } | |
| $query = "SELECT | |
| DISTINCT(eer.id), | |
| eer.name | |
| FROM | |
| ec_exam_registration_subject eers | |
| INNER JOIN ec_exam_registration_batch eerb ON | |
| eerb.id = eers.ec_exam_registration_batch_id | |
| INNER JOIN ec_exam_registration eer ON | |
| eer.id = eerb.ec_exam_registration_id AND eer.trashed IS NULL | |
| WHERE | |
| JSON_CONTAINS(JSON_EXTRACT(eers.valuation_details ,'$.valuationStaffs'),JSON_OBJECT('addiitonalExamniners', '$currentUerId')) $whereQuery OR | |
| JSON_CONTAINS(JSON_EXTRACT(eers.valuation_details ,'$.valuationStaffs'),JSON_OBJECT('chiefExaminers', '$currentUerId')) $whereQuery"; | |
| try { | |
| $examRegistration = $this->executeQueryForList($query.$limitQuery, $this->mapper[ExamRegistrationServiceMapper::SEARCH_EXAM_REGISTRATION]); | |
| } catch (\Exception $e) { | |
| throw new ExamControllerException(ExamControllerException::ERROR_FETCHING_EXAM_REGISTRATION,"Cannot fetch Exam Registration details! Please try again."); | |
| } | |
| return $examRegistration; | |
| } | |
| /** | |
| * Search examRegistration Valuation Count | |
| * @param $request | |
| * @return examRegistration | |
| */ | |
| public function getAllAssignedExamRegistrationValuationCountByCurrentStaff($request){ | |
| $valuationCountObjectArray = []; | |
| $valuationCountArray = []; | |
| $currentUerId = $GLOBALS['userId']; | |
| $searchRuleRequest = new SearchRuleRequest; | |
| $searchRuleRequest->name = "EXAM_VALUATION_PROCEDURE_RULE"; | |
| $examValuationProcedureRule = reset(RuleService::getInstance()->searchRule($searchRuleRequest))->rule; | |
| $considerAdditionalAndChiefValuationForUg = $examValuationProcedureRule->considerAdditionalAndChiefValuationForUg ? true : false; | |
| $valuationCountNames = $examValuationProcedureRule->valuationCountNames ? json_decode($examValuationProcedureRule->valuationCountNames):""; | |
| $valuationCountNamesArray = []; | |
| foreach ($valuationCountNames as $valuationCountName) { | |
| $valuationCountNamesArray[$valuationCountName->id] = $valuationCountName; | |
| } | |
| $subjects = ExamRegistrationSubjectService::getInstance()->getAllCurretStaffSubjectsValuationDetailsByExamRegistrationId($request); | |
| foreach($subjects as $subject) | |
| { | |
| $subject->valuationDetails = json_decode($subject->valuationDetails); | |
| foreach($subject->valuationDetails->valuationStaffs as $valuationStaff) | |
| { | |
| foreach($valuationStaff->addiitonalExamniners as $staff) | |
| { | |
| if($currentUerId == $staff) | |
| { | |
| $valuationCountArray[$valuationStaff->count] = $valuationStaff->count; | |
| } | |
| } | |
| foreach($valuationStaff->chiefExaminers as $staff) | |
| { | |
| if($currentUerId == $staff) | |
| { | |
| $valuationCountArray[$valuationStaff->count] = $valuationStaff->count; | |
| } | |
| } | |
| } | |
| } | |
| sort($valuationCountArray); | |
| foreach($valuationCountArray as $valuationCount) | |
| { | |
| $valuaionCount = new \stdClass; | |
| $valuaionCount->id= $valuationCount; | |
| $valuaionCount->text= $valuationCountNamesArray[$valuationCount]->name ? $valuationCountNamesArray[$valuationCount]->name : $valuationCount; | |
| if ( $considerAdditionalAndChiefValuationForUg ){ | |
| $courseTypeId = ExamValuationService::getInstance()->getCourseTypeIdByExamRegistrationIdAndPaperSubjectId($request->examRegistrationId,reset($subjects)->academicPaperSubjectId); | |
| $courseType = CourseTypeService::getInstance()->getCourseTypesById ($courseTypeId); | |
| if(!$courseType->extValRequired){ | |
| if ( $valuationCount == 1 ){ | |
| $valuaionCount->text= "Additional Examination"; | |
| $valuaionCount->name= "Additional Examination"; | |
| } else if ( $valuationCount == 2 ){ | |
| $valuaionCount->text= "Chief Examination"; | |
| $valuaionCount->name= "Chief Examination"; | |
| } | |
| } | |
| } | |
| $valuationCountObjectArray[]= $valuaionCount; | |
| } | |
| return $valuationCountObjectArray; | |
| } | |
| /** | |
| * exam date | |
| * @param $request | |
| * @return examRegistration | |
| */ | |
| public function getExamDateOfExamRegistration($examRegistrationId) | |
| { | |
| $examRegistrationId = $this->realEscapeString($examRegistrationId); | |
| $whereQuery = ""; | |
| $limitQuery = ""; | |
| $query = "SELECT DISTINCT | |
| aa.properties_value->>'$.assessmentDate' AS examDate | |
| FROM | |
| ec_exam_registration_batch eerb | |
| INNER JOIN ec_exam_registration_subject eers ON | |
| eers.ec_exam_registration_batch_id = eerb.id | |
| INNER JOIN am_assessment aa ON | |
| aa.id = eers.am_assessment_id | |
| WHERE | |
| eerb.ec_exam_registration_id = '$examRegistrationId' ORDER BY aa.properties_value->>'$.assessmentDate' ASC "; | |
| try { | |
| $examDates = $this->executeQueryForList($query); | |
| } catch (\Exception $e) { | |
| throw new ExamControllerException(ExamControllerException::ERROR_FETCHING_EXAM_REGISTRATION,"Cannot fetch Exam Registration details! Please try again."); | |
| } | |
| return $examDates; | |
| } | |
| /** | |
| * exam times | |
| * @param $request | |
| * @return examRegistration | |
| */ | |
| public function getExamTimeOfExamRegistration($examRegistrationId, $examDate=null) | |
| { | |
| $examRegistrationId = $this->realEscapeString($examRegistrationId); | |
| $examDate = $this->realEscapeString($examDate); | |
| $whereQuery = ""; | |
| $limitQuery = ""; | |
| if (!empty($examDate)) { | |
| $whereQuery .= " AND aa.properties_value->>'$.assessmentDate'='$examDate' "; | |
| } | |
| $query = "SELECT DISTINCT | |
| CONCAT(TIME_FORMAT(aa.properties_value->>'$.startTime', '%l:%i %p'),' - ',TIME_FORMAT(aa.properties_value->>'$.endTime', '%l:%i %p')) AS examTime | |
| FROM | |
| ec_exam_registration_batch eerb | |
| INNER JOIN ec_exam_registration_subject eers ON | |
| eers.ec_exam_registration_batch_id = eerb.id | |
| INNER JOIN am_assessment aa ON | |
| aa.id = eers.am_assessment_id | |
| WHERE | |
| eerb.ec_exam_registration_id = '$examRegistrationId' | |
| $whereQuery"; | |
| try { | |
| $examTimes = $this->executeQueryForList($query); | |
| } catch (\Exception $e) { | |
| throw new ExamControllerException(ExamControllerException::ERROR_FETCHING_EXAM_REGISTRATION,"Cannot fetch Exam Registration details! Please try again."); | |
| } | |
| return $examTimes; | |
| } | |
| /** | |
| * get academic terms by exam registration Id | |
| * @param $request | |
| * @return academicTerms | |
| */ | |
| public function getAcademicTermsByExamRegistration($request) | |
| { | |
| $whereQuery = ""; | |
| $orderBy = " ORDER BY acte.name ASC"; | |
| $request = $this->realEscapeObject($request); | |
| if(!empty($request->examRegistrationId)){ | |
| $examRegistrationIdString = is_array($request->examRegistrationId) ? "'" . implode("','",$request->examRegistrationId) . "'" : "'".$request->examRegistrationId."'"; | |
| $whereQuery .= " AND eerb.ec_exam_registration_id IN ( $examRegistrationIdString )"; | |
| } | |
| if(!empty($request->groupId)){ | |
| $groupIdString = is_array($request->groupId) ? "'" . implode("','",$request->groupId) . "'" : "'".$request->groupId."'"; | |
| $whereQuery .= " AND eerb.groups_id IN ( $groupIdString )"; | |
| } | |
| if(!empty($request->degreeId)){ | |
| $degreeIdString = is_array($request->degreeId) ? "'" . implode("','",$request->degreeId) . "'" : "'".$request->degreeId."'"; | |
| $whereQuery .= " AND deg.id IN ( $degreeIdString )"; | |
| } | |
| $query = "SELECT DISTINCT | |
| acte.id, | |
| acte.name | |
| FROM | |
| ec_exam_registration_batch eerb | |
| INNER JOIN academic_term acte ON | |
| acte.id = CAST(eerb.properties ->> '$.academicTermId'AS CHAR) | |
| INNER JOIN `groups` g ON | |
| g.id = eerb.groups_id | |
| INNER JOIN program p ON | |
| p.id = CAST(g.properties ->> '$.programId'AS CHAR) | |
| INNER JOIN degree deg ON | |
| deg.id = p.degree_id | |
| WHERE | |
| 1 = 1 "; | |
| try { | |
| $academicTerms = $this->executeQueryForList($query.$whereQuery.$orderBy); | |
| } catch (\Exception $e) { | |
| throw new ExamControllerException(ExamControllerException::ERROR_FETCHING_EXAM_REGISTRATION_ACADEMIC_TERM,"Cannot fetch Semester Details ! Please try again."); | |
| } | |
| return $academicTerms; | |
| } | |
| /** | |
| * get Grade Scheme by exam registration Id | |
| * @param $request | |
| * @return gradeSchemes | |
| */ | |
| public function getGrdeSchemeByExamRegistration($request) | |
| { | |
| $whereQuery = ""; | |
| if(!empty($request->examRegistrationId)){ | |
| $examRegistrationIdString = is_array($request->examRegistrationId) ? "'" . implode("','",$request->examRegistrationId) . "'" : "'".$request->examRegistrationId."'"; | |
| $whereQuery .= " AND eerb.ec_exam_registration_id IN ( $examRegistrationIdString )"; | |
| } | |
| $request = $this->realEscapeObject($request); | |
| $query = "SELECT | |
| DISTINCT g.id, | |
| g.name, | |
| g.properties ->> '$.class' as class, | |
| g.range_from, | |
| g.range_to, | |
| g.properties, | |
| gs.type | |
| FROM | |
| grade_scheme gs | |
| INNER JOIN grade g ON | |
| g.grade_scheme_id = gs.id"; | |
| try { | |
| $gradeSchemes = $this->executeQueryForList($query); | |
| } catch (\Exception $e) { | |
| throw new ExamControllerException(ExamControllerException::ERROR_FETCHING_EXAM_REGISTRATION_GRADE_SCHEMES,"Cannot fetch Grade Details ! Please try again."); | |
| } | |
| return $gradeSchemes; | |
| } | |
| /** | |
| * Search All Special Exam Registrations | |
| * @param $request | |
| * @return $examRegistrations | |
| */ | |
| public function getAllSpecialExamRegistration($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $whereQuery = ""; | |
| $limitQuery = ""; | |
| if(!empty($request->specialExamType)) { | |
| $whereQuery .= " AND CAST(eer.properties->>'$.criteriaDuringSpecialExam' AS CHAR)= '$request->specialExamType'"; | |
| } | |
| $query = "SELECT | |
| eer.id, | |
| eer.identifying_context, | |
| eer.name, | |
| eer.properties->>'$.discription' AS `description`, | |
| eer.type, | |
| eer.properties, | |
| eer.trashed, | |
| eer.created_by, | |
| eer.created_date, | |
| eer.updated_by, | |
| eer.updated_date | |
| FROM | |
| ec_exam_registration eer | |
| WHERE | |
| eer.trashed IS NULL AND eer.properties->>'$.isSpecialExam' = true"; | |
| try { | |
| $examRegistrations = $this->executeQueryForList($query.$whereQuery.$limitQuery,$this->mapper[ExamRegistrationServiceMapper::SEARCH_EXAM_REGISTRATION]); | |
| } catch (\Exception $e) { | |
| throw new ExamControllerException(ExamControllerException::ERROR_FETCHING_EXAM_REGISTRATION,"Cannot fetch Exam Registration details! Please try again."); | |
| } | |
| return $examRegistrations; | |
| } | |
| /** | |
| * get All Students For Subject Wise SUPPLEMENTARY Status | |
| * @param $searchRequest | |
| * @return $studentDetails | |
| */ | |
| public function getAllExamRegistredStudentDetails($searchRequest) { | |
| $searchRequest = $this->realEscapeObject($searchRequest); | |
| try | |
| { | |
| $whereQuery = ""; | |
| if(!empty($searchRequest->courseTypeId)) { | |
| $whereQuery .= " AND g.properties ->> '$.courseTypeId' = '$searchRequest->courseTypeId'"; | |
| } | |
| if(!empty($searchRequest->admissionYear)) { | |
| $whereQuery .= " AND g.properties ->> '$.startYear' = '$searchRequest->admissionYear'"; | |
| } | |
| if(!empty($searchRequest->academicTermId)) { | |
| $whereQuery .= " AND CAST(eerb.properties ->> '$.academicTermId' AS CHAR) = '$searchRequest->academicTermId'"; | |
| } | |
| if(!empty($searchRequest->examRegistrationId)) { | |
| $whereQuery .= " AND eerb.ec_exam_registration_id = '$searchRequest->examRegistrationId'"; | |
| } | |
| if(!empty($searchRequest->academicPaperSubjectIds)) { | |
| $academicPaperSubjectIdsString= "'" . implode("', '", $searchRequest->academicPaperSubjectIds) . "'" ; | |
| $whereQuery .= "AND eers.cm_academic_paper_subjects_id IN ($academicPaperSubjectIdsString)"; | |
| } | |
| if(!empty($searchRequest->feeStatus)) { | |
| $whereQuery .= " AND esar.properties ->> '$.feeStatus' = '$searchRequest->feeStatus'"; | |
| } | |
| $orderBy = $searchRequest->orderBy? "ORDER BY sa.studentID ASC" : ""; | |
| $query = "SELECT DISTINCT | |
| sa.studentID as studentId, | |
| sa.studentName, | |
| sa.regNo, | |
| sa.studentEmail, | |
| sa.studentPhone, | |
| eer.name as specialExamRegistrationName, | |
| g.name as groupName, | |
| act.name as academicTerm, | |
| eers.am_assessment_id as assessmentId, | |
| eerb.ec_exam_registration_id as examRegistrationId, | |
| eers.cm_academic_paper_subjects_id as academicPaperSubjectId, | |
| s.code as subjectCode, | |
| s.name as subjectName, | |
| eer.name as examRegistrationName, | |
| esar.properties ->> '$.registeredDate' as studentExamRegistrationDate | |
| FROM | |
| `groups` g | |
| INNER JOIN ec_exam_registration_batch eerb ON | |
| eerb.groups_id = g.id | |
| INNER JOIN ec_exam_registration_subject eers ON | |
| eers.ec_exam_registration_batch_id = eerb.id | |
| INNER JOIN ec_exam_registration eer ON | |
| eer.id = eerb.ec_exam_registration_id | |
| INNER JOIN ec_student_assessment_registration esar ON | |
| esar.am_assessment_id = eers.am_assessment_id AND esar.ec_exam_registration_type = eer.type AND CAST(esar.properties ->> '$.registrationStatus' AS CHAR) = 'REGISTERED' | |
| INNER JOIN studentaccount sa ON | |
| sa.studentID = esar.student_id | |
| INNER JOIN cm_academic_paper_subjects aps ON | |
| eers.cm_academic_paper_subjects_id = aps.id | |
| INNER JOIN v4_ams_subject s ON | |
| aps.ams_subject_id = s.id | |
| INNER JOIN academic_term act ON | |
| act.id = CAST(eerb.properties ->> '$.academicTermId' AS CHAR) | |
| WHERE 1=1 "; | |
| $studentDetails = $this->executeQueryForList($query.$whereQuery.$orderBy); | |
| } | |
| catch (\Exception $e) | |
| { | |
| throw new ExamControllerException($e->getCode(),$e->getMessage()); | |
| } | |
| return $studentDetails; | |
| } | |
| /** | |
| * save Batch Group For Exam Revaluation | |
| * @param $batchGroupForRevaluation | |
| * @return $id | |
| */ | |
| public function saveBatchGroupForExamRevaluation ($batchGroupForRevaluation) | |
| { | |
| $batchGroupForRevaluation = $this->realEscapeObject($batchGroupForRevaluation); | |
| $batchGroupForRevaluation->startDate = date("Y-m-d", strtotime($batchGroupForRevaluation->startDate)); | |
| $batchGroupForRevaluation->endDate = date("Y-m-d", strtotime($batchGroupForRevaluation->endDate)); | |
| $batchGroupForRevaluation->verificationDate = date("Y-m-d", strtotime($batchGroupForRevaluation->verificationDate)); | |
| try{ | |
| if(!empty($batchGroupForRevaluation->id)) | |
| { | |
| $batchGroupForRevaluation->id = $this->updateBatchGroupForRevaluation($batchGroupForRevaluation); | |
| } | |
| else | |
| { | |
| $batchGroupForRevaluation->id = $this->insertBatchGroupForRevaluation($batchGroupForRevaluation); | |
| } | |
| }catch(\Exception $e) { | |
| throw new ExamControllerException ($e->getCode(),$e->getMessage()); | |
| } | |
| return $batchGroupForRevaluation->id ; | |
| } | |
| /** | |
| * Insert Batch Group For Revaluation | |
| * @param $batchGroupForRevaluation | |
| * @return $id | |
| */ | |
| private function insertBatchGroupForRevaluation($batchGroupForRevaluation){ | |
| $groupIdsObj = new \stdClass(); | |
| $groupIdsObj->groupIds = $batchGroupForRevaluation->groupIds; | |
| $groupIds = json_encode($groupIdsObj); | |
| $query = "INSERT INTO exam_revaluation_batch_groups | |
| (ec_exam_revaluation_id,group_ids,startDate,endDate,subjectLimit,verificationDate) | |
| VALUES | |
| ('$batchGroupForRevaluation->examRevaluationId','$groupIds','$batchGroupForRevaluation->startDate','$batchGroupForRevaluation->endDate','$batchGroupForRevaluation->subjectLimit','$batchGroupForRevaluation->verificationDate')"; | |
| try { | |
| $batchGroupForRevaluation->id = $this->executeQuery($query,true)->id; | |
| return $batchGroupForRevaluation->id; | |
| } catch (\Exception $e) { | |
| throw new ExamControllerException($e->getCode(),$e->getMessage()); | |
| } | |
| } | |
| /** | |
| * update Batch Group For Revaluation | |
| * @param $batchGroupForRevaluation | |
| * @return String $batchGroupForRevaluation->id | |
| */ | |
| private function updateBatchGroupForRevaluation($batchGroupForRevaluation){ | |
| $groupIdsObj = new \stdClass(); | |
| $groupIdsObj->groupIds = $batchGroupForRevaluation->groupIds; | |
| $groupIds = json_encode($groupIdsObj); | |
| $query = "UPDATE | |
| exam_revaluation_batch_groups | |
| SET | |
| ec_exam_revaluation_id = '$batchGroupForRevaluation->examRevaluationId', | |
| group_ids = '$groupIds', | |
| startDate = '$batchGroupForRevaluation->startDate', | |
| endDate = '$batchGroupForRevaluation->endDate', | |
| subjectLimit = '$batchGroupForRevaluation->subjectLimit', | |
| verificationDate = '$batchGroupForRevaluation->verificationDate' | |
| WHERE | |
| id = '$batchGroupForRevaluation->id'"; | |
| try | |
| { | |
| $this->executeQuery($query); | |
| return $batchGroupForRevaluation->id; | |
| } catch (\Exception $e) { | |
| throw new ExamControllerException($e->getCode(),$e->getMessage()); | |
| } | |
| } | |
| /** | |
| * Delete Batch Group For Revaluation | |
| * @param String $id | |
| * @return NULL | |
| */ | |
| public function deleteBatchGroupForRevaluation($id) | |
| { | |
| $id = $this->realEscapeString($id); | |
| if(empty($id)) | |
| throw new ExamControllerException(ExamControllerException::EMPTY_PARAMETERS,"Revaluation Batch Group is invalid!"); | |
| $query = "DELETE FROM | |
| exam_revaluation_batch_groups | |
| WHERE | |
| id = '$id'"; | |
| try { | |
| $this->executeQuery($query); | |
| } catch (\Exception $e) { | |
| throw new ExamControllerException(ExamControllerException::ERROR_DELETING_REVALUATION_BATCH_GROUP,"Error deleting Revaluation Batch! Please try again"); | |
| } | |
| } | |
| /** | |
| * Search stduent Exam Registration | |
| * @param SearchStudentExamRegistrationRequest $request | |
| * @return examRevaluations | |
| */ | |
| public function searchStudentExamRevaluation(SearchStudentExamRegistrationRequest $request){ | |
| $request = $this->realEscapeObject($request); | |
| $whereQuery = ""; | |
| $limitQuery = ""; | |
| $joinQuery = ""; | |
| $selectColumns = ""; | |
| if(!empty($request->studentId)){ | |
| $whereQuery .= " AND spa.student_id = '$request->studentId' "; | |
| } | |
| else { | |
| throw new ExamControllerException(ExamControllerException::EMPTY_PARAMETERS,"Cannot fetch Exam Registration of unknown student! Please try again."); | |
| } | |
| if(!empty($request->examRegistrationId)){ | |
| $whereQuery .= " AND eer.id = '$request->examRegistrationId'"; | |
| // $joinQuery .= "INNER JOIN fm_fee_head ffhc ON | |
| // ffhc.id = eerb.fees_properties->>'$.commonFeeHeadId' | |
| // INNER JOIN fm_fee_subhead ffsc ON | |
| // ffsc.fm_fee_head_id = ffhc.id | |
| // INNER JOIN fm_fee_head ffhr ON | |
| // ffhr.id = eerb.fees_properties->>'$.revaluationFeeHeadId' | |
| // INNER JOIN fm_fee_subhead ffsr ON | |
| // ffsr.fm_fee_head_id = ffhr.id"; | |
| // $selectColumns = ",ffsc.id as commonFeeId, | |
| // ffsc.name as commonFeeName, | |
| // ffsr.id as revaluationFeeId, | |
| // ffsr.name as revaluationFeeName"; | |
| } | |
| if($request->startIndex !== "" && $request->endIndex !== ""){ | |
| $limitQuery .= " LIMIT $request->startIndex,$request->endIndex"; | |
| } | |
| $query = "SELECT | |
| eer.id, | |
| eer.identifying_context, | |
| eer.name, | |
| eer.type, | |
| eer.properties, | |
| eer.trashed, | |
| eer.created_by, | |
| eer.created_date, | |
| eer.updated_by, | |
| eer.updated_date, | |
| eerp.id AS parentExamRegistrationId, | |
| eerp.name AS parentExamRegistrationName, | |
| eerp.type AS parentExamRegistrationType, | |
| eerb.groups_id, | |
| g.name AS groupName, | |
| eerb.fees_properties AS batchFeeProperties, | |
| caps.id AS paperSubjectId, | |
| eers.fees_properties AS subjectFeeProperties, | |
| eers.revaluation_properties, | |
| eers.am_assessment_id, | |
| s.code as subjectName, | |
| aps.properties ->> '$.syllabusName' AS syllabusName, | |
| s.name as subjectDesc, | |
| esar.id AS studentAssessmentRegistrationId, | |
| esar.properties->>'$.registrationStatus' AS registrationStatus, | |
| erbg.id AS revaluationGroupId, | |
| erbg.startDate, | |
| erbg.endDate, | |
| erbg.subjectLimit, | |
| erbg.verificationDate | |
| $selectColumns | |
| FROM | |
| ec_exam_registration eer | |
| INNER JOIN ec_exam_registration eerp ON | |
| eerp.id = eer.properties->>'$.parentExamRegstrationId' | |
| INNER JOIN ec_exam_registration_batch eerb ON | |
| eerb.ec_exam_registration_id = eer.id | |
| INNER JOIN `groups` g ON | |
| g.id = eerb.groups_id | |
| AND g.`type` = 'BATCH' | |
| INNER JOIN group_members gm ON | |
| gm.groups_id = g.id | |
| INNER JOIN student_program_account spa ON | |
| spa.id = gm.members->>'$.studentId' | |
| INNER JOIN exam_revaluation_batch_groups erbg ON | |
| erbg.ec_exam_revaluation_id = eer.id AND JSON_SEARCH(erbg.group_ids ->> '$.groupIds','one',g.id) | |
| INNER JOIN ec_exam_registration_subject eers ON | |
| eers.ec_exam_registration_batch_id = eerb.id | |
| INNER JOIN cm_academic_paper_subjects caps ON | |
| caps.id = eers.cm_academic_paper_subjects_id | |
| INNER JOIN v4_ams_subject s ON | |
| s.id = caps.ams_subject_id | |
| LEFT JOIN ec_student_assessment_registration esar ON | |
| esar.student_id = spa.student_id AND esar.am_assessment_id = eers.am_assessment_id AND esar.ec_exam_registration_type = eer.type | |
| $joinQuery | |
| WHERE | |
| eer.trashed IS NULL | |
| AND eer.type = 'REVALUATION' "; | |
| try { | |
| $examRevaluations = $this->executeQueryForList($query.$whereQuery.$limitQuery, $this->mapper[ExamRegistrationServiceMapper::SEARCH_STUDENT_EXAM_REVALUATION]); | |
| } catch (\Exception $e) { | |
| throw new ExamControllerException(ExamControllerException::ERROR_FETCHING_EXAM_REGISTRATION,"Cannot fetch Exam Registration of the student."); | |
| } | |
| if (empty($examRevaluations)) { | |
| throw new ExamControllerException(ExamControllerException::EMPTY_PARAMETERS,"Exam Registration not declared, Please try again."); | |
| } | |
| foreach ($examRevaluations as $examRevaluation) { | |
| $examRevaluation->isBlocked = false; | |
| $examRevaluation->blockReasons = ""; | |
| $examRevaluation->notification = "Nill"; | |
| $examRevaluation->paymentStatus = "Not Paid"; | |
| $examRevaluation->status = "Not Registered"; | |
| $examRevaluation->revaluationSubjectLimit = (int)$examRevaluation->revaluationSubjectLimit; | |
| if(strtotime($examRevaluation->revaluationStartDate) > strtotime(date("Y-m-d"))) { | |
| // $examRevaluation->isBlocked = true; | |
| $examRevaluation->blockReasons = "Registration not started"; | |
| } | |
| elseif (strtotime($examRevaluation->revaluationEndDate) < strtotime(date("Y-m-d"))) { | |
| $examRevaluation->isBlocked = true; | |
| $examRevaluation->blockReasons = "Registration closed"; | |
| } | |
| if(!empty($examRevaluation->registrationStatus)){ | |
| $examRevaluation->status = $examRevaluation->registrationStatus; | |
| } | |
| foreach($examRevaluation->subjects as $subject){ | |
| $subject->isSelected = false; | |
| usort($subject->revaluationProperties, function($a, $b) {return strcmp($a->order, $b->order);}); | |
| foreach ($subject->revaluationProperties as $revaluaionTypes) { | |
| $revaluationObj = new \stdClass; | |
| $revaluationObj->id = $revaluaionTypes->name; | |
| $revaluationObj->name = $revaluaionTypes->name; | |
| $subject->revaluationTypes[] = $revaluationObj; | |
| } | |
| } | |
| } | |
| return $examRevaluations; | |
| } | |
| /** | |
| * Search stduent Exam Registration | |
| * @return examRevaluationTpes | |
| */ | |
| public function getRevaluationTypes(){ | |
| try { | |
| $query = "SELECT | |
| id, | |
| name, | |
| properties | |
| FROM | |
| ec_revaluation_fee_types "; | |
| $revaluationTypes = $this->executeQueryForList($query); | |
| } | |
| catch (\Exception $e) | |
| { | |
| throw new ExamControllerException($e->getCode(),$e->getMessage()); | |
| } | |
| return $revaluationTypes; | |
| } | |
| /** | |
| * Search examRegistration | |
| * @param $request | |
| * @return examRegistration | |
| */ | |
| public function searchDetailedExamRegistrationDetails( $request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| if(is_array($request->assessmentId)){ | |
| foreach($request->assessmentId as $key => $assessmentId){ | |
| $request->assessmentId[$key] = stripslashes($assessmentId); | |
| } | |
| } | |
| $whereQuery = ""; | |
| $limitQuery = ""; | |
| $joinQueary = ""; | |
| $orderQuery = ""; | |
| if(!empty($request->examRegistrationId)){ | |
| $examRegistrationIdString = is_array($request->examRegistrationId) ? "'" . implode("','",$request->examRegistrationId) . "'" : "'".$request->examRegistrationId."'"; | |
| $whereQuery .= " AND eer.id IN ( $examRegistrationIdString ) "; | |
| } | |
| if(!empty($request->name)){ | |
| $whereQuery .= " AND eer.name LIKE '%$request->name%'"; | |
| } | |
| if(!empty($request->courseTypeId)){ | |
| $whereQuery .= " AND p.course_type_id = '$request->courseTypeId'"; | |
| } | |
| if(!empty($request->groupId)) { | |
| $groupIdString = is_array($request->groupId) ? "'" . implode("','",$request->groupId) . "'" : "'".$request->groupId."'"; | |
| $whereQuery .= " AND g.id IN ( $groupIdString )"; | |
| } | |
| if(!empty($request->academicPaperSubjectId)) { | |
| $academicPaperSubjectIdString = is_array($request->academicPaperSubjectId) ? "'" . implode("','",$request->academicPaperSubjectId) . "'" : "'".$request->academicPaperSubjectId."'"; | |
| $whereQuery .= " AND aps.id IN ( $academicPaperSubjectIdString )"; | |
| } | |
| if(!empty($request->assessmentId)) { | |
| $assessmentIdString = is_array($request->assessmentId) ? "'" . implode("','",$request->assessmentId) . "'" : "'".$request->assessmentId."'"; | |
| $whereQuery .= " AND eers.am_assessment_id IN ( $assessmentIdString )"; | |
| } | |
| if(!empty($request->examRegistrationBatchId)) { | |
| $examRegistrationBatchIdString = is_array($request->examRegistrationBatchId) ? "'" . implode("','",$request->examRegistrationBatchId) . "'" : "'".$request->examRegistrationBatchId."'"; | |
| $whereQuery .= " AND eerb.id IN ( $examRegistrationBatchIdString )"; | |
| } | |
| if(!empty($request->academicTermId)){ | |
| $whereQuery .= " AND CAST(eerb.properties ->> '$.academicTermId'AS CHAR) = '$request->academicTermId'"; | |
| } | |
| if(!empty($request->batchStartYear)){ | |
| $whereQuery .= " AND g.properties ->> '$.startYear' = '$request->batchStartYear'"; | |
| } | |
| if(!empty($request->type)) { | |
| foreach($request->type as $type){ | |
| $whereQuery .= " AND eer.type LIKE '%$type%'"; | |
| } | |
| } | |
| if(!empty($request->limitCondition)) { | |
| $limitQuery = " LIMIT 1"; | |
| } | |
| $joinQuery = " LEFT JOIN stream str ON | |
| JSON_SEARCH( p.stream_id, 'one', str.id) IS NOT NULL"; | |
| $columns = " group_concat(str.name SEPARATOR ' and ') as streamName, "; | |
| if(!empty($request->removeStream)) { | |
| $joinQuery = ""; | |
| $columns = ""; | |
| } | |
| $groupByQuerry = " GROUP BY eer.id,p.id,g.id,aps.id"; | |
| $orderQuery = " ORDER BY CAST(cap.properties ->> '$.order' AS UNSIGNED) ASC"; | |
| $query = "SELECT | |
| DISTINCT(eer.id), | |
| eer.identifying_context, | |
| eer.name, | |
| eer.type, | |
| eer.properties, | |
| eer.properties ->> '$.examMonth' AS examMonth, | |
| MONTHNAME(STR_TO_DATE(eer.properties ->> '$.examMonth', '%m')) AS examMonthName, | |
| eer.properties ->> '$.examYear' AS examYear, | |
| eer.fees_properties, | |
| eer.trashed, | |
| eer.created_by, | |
| eer.created_date, | |
| eer.updated_by, | |
| eer.updated_date, | |
| eerb.groups_id, | |
| eerb.properties AS examBatchProperties, | |
| g.name AS groupName, | |
| g.type AS groupType, | |
| act.id as academicTermId, | |
| act.name as academicTermName, | |
| act.properties ->>'$.year' AS academicTermYear, | |
| act.properties ->>'$.orderNo' AS academicOrderNo, | |
| dept.deptID, | |
| deg.name as degreeName, | |
| deg.description as degreeDescription, | |
| $columns | |
| p.name as programName, | |
| p.properties ->>'$.subsidiaryCourseName' AS subsidiaryCourseName, | |
| dept.deptName, | |
| dept.departmentDesc as departmentDesc, | |
| dept.departmentSpecialization AS departmentSpecialization, | |
| ct.courseTypeID, | |
| ct.course_Type as courseTypeName, | |
| g.properties ->> '$.startYear' AS batchStartYear, | |
| g.identifying_context AS groupIdentifyingContext, | |
| g.properties AS groupProperties, | |
| eers.cm_academic_paper_subjects_id as academicPaperSubjectId, | |
| eers.am_assessment_id as assessmentId, | |
| s.code as subjectCode, | |
| aps.properties ->> '$.syllabusName' AS syllabusName, | |
| s.name as subjectName, | |
| aps.properties ->>'$.isInternal' AS isInternal, | |
| aps.properties ->>'$.isExternal' AS isExternal, | |
| aps.properties ->>'$.externalMaxMark' AS externalMaxMark, | |
| aps.properties ->>'$.internalMaxMark' AS internalMaxMark, | |
| aps.properties ->>'$.credit' AS subjectCredit, | |
| aps.properties ->>'$.subjectTypeId' AS subjectTypeId, | |
| aps.properties ->>'$.courseCode' AS courseCode, | |
| IFNULL(aps.properties ->>'$.externalMaxMark', 0) + IFNULL(aps.properties ->>'$.internalMaxMark', 0) AS totalSubjectMark, | |
| cap.properties ->> '$.order' as subjectPriority, | |
| aps.properties ->> '$.excludeSubjectFromTotal' AS excludeSubjectFromTotal | |
| FROM | |
| ec_exam_registration eer | |
| INNER JOIN ec_exam_registration_batch eerb | |
| ON eerb.ec_exam_registration_id = eer.id | |
| INNER JOIN ec_exam_registration_subject eers ON | |
| eers.ec_exam_registration_batch_id = eerb.id | |
| INNER JOIN cm_academic_paper_subjects aps ON | |
| eers.cm_academic_paper_subjects_id = aps.id | |
| INNER JOIN cm_academic_paper cap ON | |
| cap.id = aps.cm_academic_paper_id | |
| INNER JOIN v4_ams_subject s ON | |
| aps.ams_subject_id = s.id | |
| INNER JOIN `groups` g | |
| ON g.id = eerb.groups_id | |
| INNER JOIN program p ON | |
| p.id = CAST(g.properties ->> '$.programId' AS CHAR) | |
| INNER JOIN degree deg ON | |
| deg.id = p.degree_id | |
| INNER JOIN `academic_term` act ON | |
| act.id = CAST(eerb.properties ->> '$.academicTermId'AS CHAR) | |
| INNER JOIN `department` dept ON | |
| dept.deptID = CAST(g.properties ->> '$.departmentId' AS CHAR) | |
| INNER JOIN `course_type` ct ON | |
| ct.courseTypeID = p.course_type_id | |
| $joinQuery | |
| WHERE | |
| eer.trashed IS NULL | |
| "; | |
| try { | |
| $examRegistrations = $this->executeQueryForList($query.$whereQuery.$groupByQuerry.$orderQuery.$limitQuery, $this->mapper[ExamRegistrationServiceMapper::SEARCH_DETAILED_EXAM_REGISTRATION_DETAILS]); | |
| } catch (\Exception $e) { | |
| throw new ExamControllerException(ExamControllerException::ERROR_FETCHING_EXAM_REGISTRATION,"Cannot fetch Exam Registration details! Please try again."); | |
| } | |
| return $examRegistrations; | |
| } | |
| /** | |
| * get Month Name By month Num | |
| * @param $monthNo | |
| * @return monthData | |
| */ | |
| public function getMonthName($monthNo){ | |
| if($monthNo){ | |
| $monthData = new \stdClass; | |
| $query = "SELECT | |
| MONTHNAME(STR_TO_DATE($monthNo , '%m')) AS examMonthName"; | |
| try { | |
| $monthData = $this->executeQueryForObject($query); | |
| } | |
| catch (\Exception $e) { | |
| throw new ExamControllerException(ExamControllerException::INVALID_PARAMETERS,"Invailed Parameter."); | |
| } | |
| return $monthData->examMonthName; | |
| } | |
| else{ | |
| return null; | |
| } | |
| } | |
| /** | |
| * getAcademicTermIdByExamRegistrationId | |
| * @param $request | |
| * @return examRegistration | |
| */ | |
| public function getAcademicTermIdByExamRegistrationId( $request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $whereQuery = ""; | |
| if(!empty($request->examRegistrationId)){ | |
| $whereQuery .= " AND eer.id='$request->examRegistrationId' "; | |
| } | |
| $query = "SELECT | |
| DISTINCT | |
| act.id as academicTermId | |
| FROM | |
| ec_exam_registration eer | |
| RIGHT JOIN ec_exam_registration_batch eerb | |
| ON eerb.ec_exam_registration_id = eer.id | |
| LEFT JOIN `academic_term` act ON | |
| act.id = CAST(eerb.properties ->> '$.academicTermId'AS CHAR) | |
| WHERE | |
| eer.trashed IS NULL"; | |
| try { | |
| $academicTerm = $this->executeQueryForObject($query.$whereQuery); | |
| } catch (\Exception $e) { | |
| throw new ExamControllerException(ExamControllerException::ERROR_FETCHING_EXAM_REGISTRATION,"Cannot fetch Exam Registration details! Please try again."); | |
| } | |
| return $academicTerm->academicTermId; | |
| } | |
| /** | |
| * print All absent Students For Special Exam | |
| * @param $searchRequest | |
| * @return $response | |
| */ | |
| public function getAllStudentDetailsForSpecialExam($searchRequest) { | |
| $searchRequest = $this->realEscapeObject($searchRequest); | |
| try{ | |
| $response = new \stdClass; | |
| $request = new \stdClass; | |
| $additionalDetails = new \stdClass; | |
| $absentStudentDetails = []; | |
| $absentStudentDetailsArray = []; | |
| $request->groupId = $searchRequest->groupId; | |
| $request->academicTermId = $searchRequest->academicTermId; | |
| $request->departmentId = $searchRequest->departmentId; | |
| $request->specialExamRegstrationId = $searchRequest->specialExamRegstrationId; | |
| $studentsDetails = $this->getAllStudentDetailsListForSpecialExamRegistration($request); | |
| if(empty($studentsDetails)){ | |
| throw new ExamControllerException(ExamControllerException::NO_STUDENTS_IN_THIS_EXAM_REGISTRATION,"No Students In This Special Exam Registration"); | |
| } | |
| else{ | |
| foreach($studentsDetails as $student){ | |
| $additionalDetails->specialExamRegistrationName = $student->specialExamRegistrationName; | |
| $absentStudentDetails[$student->studentId]->studentId = $student->studentId; | |
| $absentStudentDetails[$student->studentId]->groupName = $student->groupName; | |
| $absentStudentDetails[$student->studentId]->academicTerm = $student->academicTerm; | |
| $absentStudentDetails[$student->studentId]->studentName = $student->studentName; | |
| $absentStudentDetails[$student->studentId]->regNo = $student->regNo; | |
| $absentStudentDetails[$student->studentId]->regularExamRegistrationName = $student->regularExamRegistrationName; | |
| $absentStudentDetails[$student->studentId]->availableSubjects[$student->academicPaperSubjectId]->academicPaperSubjectId = $student->academicPaperSubjectId; | |
| $absentStudentDetails[$student->studentId]->availableSubjects[$student->academicPaperSubjectId]->subjectCode = $student->subjectCode; | |
| $absentStudentDetails[$student->studentId]->availableSubjects[$student->academicPaperSubjectId]->subjectName = $student->subjectName; | |
| $absentStudentDetails[$student->studentId]->availableSubjects[$student->academicPaperSubjectId]->specialExamAssessmentId = $student->specialExamAssessmentId; | |
| $absentStudentDetails[$student->studentId]->availableSubjects[$student->academicPaperSubjectId]->specialExamRegistrationId = $student->specialExamRegistrationId; | |
| $absentStudentDetails[$student->studentId]->availableSubjects[$student->academicPaperSubjectId]->academicTerm = $student->academicTerm; | |
| } | |
| foreach($absentStudentDetails as $absentStudent){ | |
| $absentStudent->subjects=[]; | |
| foreach($absentStudent->availableSubjects as $subject){ | |
| $absentStudent->subjects[] = $subject; | |
| } | |
| $absentStudentDetailsArray[]=$absentStudent; | |
| } | |
| } | |
| if(empty($absentStudentDetailsArray)){ | |
| throw new ExamControllerException(ExamControllerException::NO_STUDENTS_IN_THIS_EXAM_REGISTRATION,"No Students In This Special Exam Registration"); | |
| } | |
| else | |
| { | |
| $templateName = "PrintSpecialExamReport"; | |
| $responseHtml = TwigRenderer::renderTemplateFileToHtml(realpath(DOCUMENT_ROOT."../examcontroller-api/src/com/linways/web/templates/SupplementaryExamReports/$templateName.twig"), [ 'students'=>$absentStudentDetailsArray ,'additionalDetails'=>$additionalDetails]); | |
| $prtContent = NULL; | |
| $prtContent .= '<html><head>'; | |
| $prtContent .= "<style> | |
| h6 {font-size: 26px;} .text-center { text-align: center;} .align-middle {vertical-align: middle;}; | |
| </style>"; | |
| $prtContent .= '</head><title>Special Exam Students Report</title><body>'; | |
| $prtContent .= $responseHtml; | |
| $prtContent .= '</body></html>'; | |
| $totalWidth = 210; | |
| $totalHeight = 297; | |
| $options = array( | |
| 'page-width' => $totalHeight."mm", | |
| 'page-height' => $totalWidth."mm", | |
| 'dpi' => 96, | |
| 'margin-top' => "9mm", | |
| 'margin-left' => "1mm", | |
| 'margin-right' => "1mm", | |
| 'margin-bottom' => "9mm", | |
| // 'binary' => "/usr/local/bin/wkhtmltopdf", // For Mac | |
| 'user-style-sheet' => realpath(DOCUMENT_ROOT . "/libcommon/bootstrap/css/bootstrap.min.css") | |
| ); | |
| $programResult = new \stdClass; | |
| $programResult->dispalyHtmlData = $responseHtml; | |
| $programResult->printData = PdfUtil::renderPdf($prtContent, $options); | |
| return $programResult; | |
| } | |
| } | |
| catch (\Exception $e){ | |
| throw new ExamControllerException($e->getCode(),$e->getMessage()); | |
| } | |
| return $response; | |
| } | |
| /** | |
| * get All Students For Special Exam, | |
| * @param $searchRequest | |
| * @return $response | |
| */ | |
| public function getAllStudentDetailsListForSpecialExamRegistration($searchRequest) { | |
| $searchRequest = $this->realEscapeObject($searchRequest); | |
| try | |
| { | |
| $whereQuery = ""; | |
| if(!empty($searchRequest->groupId)){ | |
| $whereQuery .= " AND eerbse.groups_id = '$searchRequest->groupId' "; | |
| } | |
| if(!empty($searchRequest->academicTermId)){ | |
| $whereQuery .= " AND CAST(eerbse.properties ->> '$.academicTermId'AS CHAR) = '$searchRequest->academicTermId' "; | |
| } | |
| if(!empty($searchRequest->departmentId)){ | |
| $whereQuery .= " AND g.properties ->> '$.departmentId' = '$searchRequest->departmentId' "; | |
| } | |
| $query = "SELECT DISTINCT | |
| sa.studentID as studentId, | |
| sa.studentName, | |
| sa.regNo, | |
| eerse.name as specialExamRegistrationName, | |
| g.name as groupName, | |
| act.name as academicTerm, | |
| eersse.am_assessment_id as specialExamAssessmentId, | |
| eerbse.ec_exam_registration_id as specialExamRegistrationId, | |
| eersse.cm_academic_paper_subjects_id as academicPaperSubjectId, | |
| s.code as subjectCode, | |
| s.name as subjectName, | |
| eer.name as regularExamRegistrationName | |
| FROM | |
| ec_exam_registration_batch eerbse | |
| INNER JOIN ec_exam_registration_subject eersse ON | |
| eersse.ec_exam_registration_batch_id = eerbse.id | |
| INNER JOIN ec_exam_registration eerse ON | |
| eerse.id = eerbse.ec_exam_registration_id | |
| INNER JOIN ec_student_assessment_registration esarse ON | |
| esarse.am_assessment_id = eersse.am_assessment_id | |
| INNER JOIN studentaccount sa ON | |
| sa.studentID = esarse.student_id | |
| INNER JOIN cm_academic_paper_subjects aps ON | |
| eersse.cm_academic_paper_subjects_id = aps.id | |
| INNER JOIN v4_ams_subject s ON | |
| aps.ams_subject_id = s.id | |
| INNER JOIN `groups` g ON | |
| g.id = eerbse.groups_id | |
| INNER JOIN ec_exam_registration_retest_mapping eerrm ON | |
| eerrm.retest_exam_registration_id = eerbse.ec_exam_registration_id | |
| INNER JOIN ec_exam_registration_batch eerbreg ON | |
| eerbreg.ec_exam_registration_id = eerrm.ec_exam_registration_id AND eerbreg.groups_id = eerbse.groups_id | |
| INNER JOIN ec_exam_registration_subject eersreg ON | |
| eersreg.ec_exam_registration_batch_id = eerbreg.id AND eersreg.cm_academic_paper_subjects_id = eersse.cm_academic_paper_subjects_id | |
| INNER JOIN academic_term act ON | |
| act.id = CAST(eerbreg.properties ->> '$.academicTermId'AS CHAR) | |
| INNER JOIN ec_student_assessment_registration esarreg ON | |
| esarreg.am_assessment_id = eersreg.am_assessment_id AND esarreg.student_id = esarse.student_id | |
| INNER JOIN ec_exam_registration eer ON | |
| eer.id = eerbreg.ec_exam_registration_id | |
| WHERE | |
| eerbse.ec_exam_registration_id = '$searchRequest->specialExamRegstrationId' "; | |
| $specialExamStudentsDetails = $this->executeQueryForList($query.$whereQuery); | |
| } | |
| catch (\Exception $e) | |
| { | |
| throw new ExamControllerException($e->getCode(),$e->getMessage()); | |
| } | |
| return $specialExamStudentsDetails; | |
| } | |
| /** | |
| * get degree by exam registration Id | |
| * @param $request | |
| * @return academicTerms | |
| */ | |
| public function getAllDegreesByExamRegistration($request) | |
| { | |
| $whereQuery = ""; | |
| $orderBy = " ORDER BY deg.name ASC"; | |
| $searchRequest = $this->realEscapeObject($request); | |
| if(!empty($searchRequest->examRegistrationId)){ | |
| $examRegistrationIdString = is_array($searchRequest->examRegistrationId) ? "'" . implode("','",$searchRequest->examRegistrationId) . "'" : "'".$searchRequest->examRegistrationId."'"; | |
| $whereQuery .= " AND eerb.ec_exam_registration_id IN ( $examRegistrationIdString )"; | |
| } | |
| $query = "SELECT DISTINCT | |
| deg.id, | |
| deg.name | |
| FROM | |
| ec_exam_registration_batch eerb | |
| INNER JOIN `groups` g ON | |
| g.id = eerb.groups_id | |
| INNER JOIN program p ON | |
| p.id = CAST(g.properties ->> '$.programId'AS CHAR) | |
| INNER JOIN degree deg ON | |
| deg.id = p.degree_id | |
| WHERE | |
| 1=1 "; | |
| try { | |
| $degree = $this->executeQueryForList($query.$whereQuery.$orderBy); | |
| } catch (\Exception $e) { | |
| throw new ExamControllerException(ExamControllerException::ERROR_FETCHING_EXAM_REGISTRATION_ACADEMIC_TERM,"Cannot fetch Semester Details ! Please try again."); | |
| } | |
| return $degree; | |
| } | |
| /** | |
| * get All Assessment Dates By ExamRegistration | |
| * @param $request | |
| * @return assessmentDates | |
| */ | |
| public function getAllAssessmentDatesByExamRegistration($request) | |
| { | |
| $whereQuery = ""; | |
| $orderBy = " ORDER BY aa.properties_value ->>'$.assessmentDate' DESC"; | |
| $searchRequest = $this->realEscapeObject($request); | |
| if(!empty($searchRequest->examRegistrationId)){ | |
| $examRegistrationIdString = is_array($searchRequest->examRegistrationId) ? "'" . implode("','",$searchRequest->examRegistrationId) . "'" : "'".$searchRequest->examRegistrationId."'"; | |
| $whereQuery .= " AND eerb.ec_exam_registration_id IN ( $examRegistrationIdString )"; | |
| } | |
| $query = "SELECT DISTINCT | |
| aa.properties_value ->>'$.assessmentDate' AS id, | |
| aa.properties_value ->>'$.assessmentDate' AS name | |
| FROM | |
| ec_exam_registration_batch eerb | |
| INNER JOIN ec_exam_registration_subject eers ON | |
| eers.ec_exam_registration_batch_id = eerb.id | |
| INNER JOIN am_assessment aa ON | |
| aa.id = eers.am_assessment_id | |
| WHERE | |
| 1=1 AND aa.properties_value ->>'$.assessmentDate' IS NOT NULL"; | |
| try { | |
| $assessmentDates = $this->executeQueryForList($query.$whereQuery.$orderBy); | |
| } catch (\Exception $e) { | |
| throw new ExamControllerException(ExamControllerException::ERROR_FETCHING_EXAM_DATES,"Cannot fetch Exam Dates ! Please try again."); | |
| } | |
| foreach($assessmentDates as $assessmentDate){ | |
| $assessmentDate->name = date("d-M-Y", strtotime($assessmentDate->name)); | |
| } | |
| return $assessmentDates; | |
| } | |
| /** | |
| * get Current Exam Revaluation Details | |
| * @param $searchRequest | |
| * @return $response | |
| */ | |
| public function getCurrentExamRevaluationDetails($request){ | |
| $searchRequest = $this->realEscapeObject($request); | |
| $whereQuery = ""; | |
| if(!empty($searchRequest->id)){ | |
| $examRegistrationIdString = is_array($searchRequest->id) ? "'" . implode("','",$searchRequest->id) . "'" : "'".$searchRequest->id."'"; | |
| $whereQuery .= " AND eer.id IN ( $examRegistrationIdString )"; | |
| } | |
| $query = "SELECT | |
| eer.id, | |
| eer.identifying_context, | |
| eer.name, | |
| eer.type, | |
| eer.properties, | |
| eer.fees_properties, | |
| eer.trashed, | |
| eer.created_by, | |
| eer.created_date, | |
| eer.updated_by, | |
| eer.updated_date, | |
| eerp.id as parentExamRegstrationId, | |
| eerp.name as parentExamRegistrationName, | |
| eerp.type as parentExamRegistrationType, | |
| erpr.id as parentRevaluationEntryId, | |
| erpr.parent_registration_id as parentRevaluationId | |
| FROM | |
| ec_exam_registration eer | |
| LEFT JOIN ec_exam_registration eerp ON | |
| eerp.id = CAST(eer.properties->>'$.parentExamRegistrationId' AS CHAR) | |
| LEFT JOIN ec_revaluation_parent_registration erpr ON | |
| erpr.ec_exam_registration_id = eer.id | |
| WHERE | |
| 1 = 1 AND eer.type = 'REVALUATION' "; | |
| try { | |
| $currentExamRevaluation = $this->executeQueryForList($query.$whereQuery,$this->mapper[ExamRegistrationServiceMapper::SEARCH_CURRENT_EXAM_REVALUATION]); | |
| } catch (\Exception $e) { | |
| throw new ExamControllerException(ExamControllerException::ERROR_FETCHING_EXAM_DATES,"Cannot fetch Exam Dates ! Please try again."); | |
| } | |
| if(!empty($currentExamRevaluation)){ | |
| array_walk($currentExamRevaluation,function($revalObj,$key){ | |
| if($revalObj->parentRevaluationId){ | |
| $revalObj->parentRevaluationId = explode(',',$revalObj->parentRevaluationId); | |
| $parentRevaluationName = ExamRevaluationParentMappingService::getInstance()->getRevaluationParentName($revalObj->parentRevaluationId); | |
| $revalObj->parentRevaluationName = implode(', ', array_column($parentRevaluationName, 'name')); | |
| } | |
| }); | |
| return reset($currentExamRevaluation); | |
| } | |
| return $currentExamRevaluation; | |
| } | |
| /** | |
| * Save ExamRegistration Fee Template | |
| * @param $feeTemplate | |
| * @return $id | |
| */ | |
| public function saveExamRegistrationFeeTemplate ($feeTemplate) | |
| { | |
| $feeTemplate = $this->realEscapeObject($feeTemplate); | |
| try{ | |
| if($feeTemplate->existingTemplateId){ | |
| $this->updateExamRegistrationFeeTemplate($feeTemplate); | |
| $templateId = $feeTemplate->existingTemplateId; | |
| } | |
| else{ | |
| $templateId = $this->createExamRegistrationFeeTemplate($feeTemplate); | |
| } | |
| foreach($feeTemplate->subTemplate as $feeSubTemplate){ | |
| $examRegistrationFeeTemplate = new ExamRegistrationFeeTemplate(); | |
| $examRegistrationFeeTemplate->feeProperties = json_encode($feeSubTemplate->fees); | |
| $examRegistrationFeeTemplate->fineProperties = json_encode($feeSubTemplate->fines); | |
| $examRegistrationFeeTemplate->operationProperties = $feeSubTemplate->feeOperation ? json_encode($feeSubTemplate->feeOperation) : "{}"; | |
| if($feeSubTemplate->templateId){ | |
| $examRegistrationFeeTemplate->id = $feeSubTemplate->templateId; | |
| $query = "UPDATE ec_examregistration_fee_templates SET fee_properties = '$examRegistrationFeeTemplate->feeProperties', fine_properties = '$examRegistrationFeeTemplate->fineProperties', operation_properties = '$examRegistrationFeeTemplate->operationProperties' WHERE id = '$examRegistrationFeeTemplate->id'"; | |
| } | |
| else{ | |
| $staffId = $GLOBALS['userId']; | |
| $id = SecurityUtils::getRandomString(); | |
| $query = "INSERT INTO ec_examregistration_fee_templates | |
| (ec_examregistration_fee_templates_mapping_id,fee_properties,fine_properties,operation_properties,created_by) | |
| VALUES | |
| ('$templateId','$examRegistrationFeeTemplate->feeProperties','$examRegistrationFeeTemplate->fineProperties','$examRegistrationFeeTemplate->operationProperties', '$staffId')"; | |
| } | |
| $this->executeQuery($query); | |
| AMSLogger::log_info($this->logger,Events::EC_SAVE_EXAM_REGISTRATION_FEE_TEMPLATE,[ | |
| "staff" => new Staff(["id" => $staffId]), | |
| "request" => $examRegistrationFeeTemplate, | |
| "status" => StatusConstants::SUCCESS | |
| ]); | |
| } | |
| }catch(\Exception $e) { | |
| AMSLogger::log_error($this->logger,Events::EC_SAVE_EXAM_REGISTRATION, [ | |
| "staff" => new Staff(["id" => $staffId]), | |
| "request" => $examRegistrationFeeTemplate, | |
| "errorCode" => $e->getCode(), | |
| "errorMessage" => $e->getMessage(), | |
| "status" => StatusConstants::FAILED | |
| ]); | |
| throw new ExamControllerException ($e->getCode(),$e->getMessage()); | |
| } | |
| return $templateId; | |
| } | |
| /** | |
| * create ExamRegistration Fee Template | |
| * @param $feeTemplate | |
| * @return $id | |
| */ | |
| public function createExamRegistrationFeeTemplate ($feeTemplate) | |
| { | |
| $feeTemplate = $this->realEscapeObject($feeTemplate); | |
| try{ | |
| $id = SecurityUtils::getRandomString(); | |
| $query = "INSERT INTO ec_examregistration_fee_templates_mapping | |
| (id,template_name,ec_exam_registration_id) | |
| VALUES | |
| ('$id','$feeTemplate->templateName','$feeTemplate->examRegistrationId')"; | |
| $this->executeQuery($query); | |
| }catch(\Exception $e) { | |
| throw new ExamControllerException ($e->getCode(),$e->getMessage()); | |
| } | |
| return $id ; | |
| } | |
| /** | |
| * create ExamRegistration Fee Template | |
| * @param $feeTemplate | |
| * @return $id | |
| */ | |
| public function updateExamRegistrationFeeTemplate ($feeTemplate){ | |
| $feeTemplate = $this->realEscapeObject($feeTemplate); | |
| try{ | |
| $query = "UPDATE | |
| ec_examregistration_fee_templates_mapping | |
| SET | |
| template_name = '$feeTemplate->templateName' | |
| WHERE | |
| id = '$feeTemplate->existingTemplateId' AND ec_exam_registration_id = '$feeTemplate->examRegistrationId' "; | |
| $this->executeQuery($query); | |
| }catch(\Exception $e) { | |
| throw new ExamControllerException ($e->getCode(),$e->getMessage()); | |
| } | |
| return $feeTemplate->existingTemplateId; | |
| } | |
| /** | |
| * get exam registration types by type | |
| * @param $examRegistrationId | |
| * @return $examRegistartionType | |
| */ | |
| public function getExamRegistrationType ($examRegistrationId) | |
| { | |
| $examRegistrationId = $this->realEscapeString($examRegistrationId); | |
| try{ | |
| $query = "SELECT | |
| eert.ec_examregistration_type AS typeName, | |
| eer.properties ->> '$.examRegistrationType' AS examRegistrationType, | |
| eer.type AS type | |
| FROM | |
| ec_exam_registration eer | |
| INNER JOIN ec_exam_registration_types eert ON | |
| eer.`type` = eert.parent_type | |
| WHERE | |
| eer.id ='$examRegistrationId'"; | |
| $examRegistartionType = $this->executeQueryForList($query); | |
| foreach($examRegistartionType as $key => $examRegType){ | |
| if($examRegType->type == ExamRegistrationTypeConstants::SUPPLEMENTARY){ | |
| if($examRegType->examRegistrationType == ExamRegistrationTypeConstants::SUPPLY_IMPROVEMENT){ | |
| break; | |
| } | |
| else if($examRegType->examRegistrationType == ExamRegistrationTypeConstants::SUPPLEMENTARY && $examRegType->typeName != ExamRegistrationTypeConstants::SUPPLEMENTARY){ | |
| unset($examRegistartionType[$key]); | |
| } | |
| else if($examRegType->examRegistrationType == ExamRegistrationTypeConstants::IMPROVEMENT && $examRegType->typeName != ExamRegistrationTypeConstants::IMPROVEMENT){ | |
| unset($examRegistartionType[$key]); | |
| } | |
| } | |
| } | |
| }catch(\Exception $e) { | |
| throw new ExamControllerException ($e->getCode(),$e->getMessage()); | |
| } | |
| return $examRegistartionType ; | |
| } | |
| /** | |
| * search Exam Registration For Filter | |
| * @param SearchExamRegistrationRequest $request | |
| * @return examRegistration | |
| */ | |
| public function searchExamRegistrationForFilter( SearchExamRegistrationRequest $request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $where = []; | |
| $where [] = " g.type = '".ProfessionalGroupTypeConstant::BATCH."'"; | |
| $where [] = " eer.trashed IS NULL"; | |
| !empty($request->id)?$where [] = " eer.id = '$request->id' ":null; | |
| !empty($request->courseTypeId)?$where [] = " p.course_type_id = '$request->courseTypeId' ":null; | |
| !empty($request->startYear)?$where [] = " g.properties ->> '$.startYear' = $request->startYear ":null; | |
| !empty($request->departmentId)?$where [] = " g.properties ->> '$.departmentId' = $request->departmentId ":null; | |
| !empty($request->currentTermId)?$where [] = " CAST(eerb.properties ->> '$.academicTermId'AS CHAR) = $request->currentTermId ":null; | |
| !empty($request->type)?$where [] = " eer.type LIKE '%$request->type%'":null; | |
| $groupByQuerry = " GROUP BY eer.id,p.id,g.id,aps.id"; | |
| $query = "SELECT | |
| DISTINCT(eer.id),eer.identifying_context,eer.name,eer.type,eer.properties,g.properties->'$.startYear' as startYear, | |
| dept.deptID as deptId,dept.deptName as deptName,act.id as academicTermId,act.name as academicTermName, | |
| p.id as programId,p.name as programName,eerb.groups_id as groupId,g.name as groupName,deg.id as degreeId,deg.name as degreeName, | |
| ct.courseTypeID as courseTypeId,ct.course_Type as courseTypeName,eers.cm_academic_paper_subjects_id as academicPaperSubjectId, | |
| s.name as subjectName,s.code as subjectCode, | |
| JSON_OBJECT('id', eer.properties ->> '$.examYear','name', eer.properties ->> '$.examYear') AS examYear, | |
| JSON_OBJECT('id',eer.type,'name', eer.type) AS examType | |
| FROM | |
| ec_exam_registration eer | |
| INNER JOIN ec_exam_registration_batch eerb ON eerb.ec_exam_registration_id = eer.id | |
| INNER JOIN `groups` g ON g.id = eerb.groups_id | |
| INNER JOIN program p ON p.id = CAST(g.properties ->> '$.programId' AS CHAR) | |
| INNER JOIN degree deg ON deg.id = p.degree_id | |
| LEFT JOIN stream str ON JSON_SEARCH( p.stream_id, 'one', str.id) IS NOT NULL | |
| INNER JOIN `academic_term` act ON act.id = CAST(eerb.properties ->> '$.academicTermId'AS CHAR) | |
| INNER JOIN `department` dept ON dept.deptID = CAST(g.properties ->> '$.departmentId' AS CHAR) | |
| INNER JOIN `course_type` ct ON ct.courseTypeID = p.course_type_id | |
| LEFT JOIN ec_exam_registration_subject eers ON eers.ec_exam_registration_batch_id = eerb.id | |
| LEFT JOIN cm_academic_paper_subjects aps ON eers.cm_academic_paper_subjects_id = aps.id | |
| LEFT JOIN v4_ams_subject s ON aps.ams_subject_id = s.id".(count($where) ? ' WHERE '.implode(' AND ',$where) : "").";"; | |
| $recordCount = "SELECT count(distinct eer.id) as `examRegCount` | |
| FROM | |
| ec_exam_registration eer | |
| INNER JOIN ec_exam_registration_batch eerb ON eerb.ec_exam_registration_id = eer.id | |
| INNER JOIN `groups` g ON g.id = eerb.groups_id | |
| INNER JOIN program p ON p.id = CAST(g.properties ->> '$.programId' AS CHAR) | |
| INNER JOIN degree deg ON deg.id = p.degree_id | |
| LEFT JOIN stream str ON JSON_SEARCH( p.stream_id, 'one', str.id) IS NOT NULL | |
| INNER JOIN `academic_term` act ON act.id = CAST(eerb.properties ->> '$.academicTermId'AS CHAR) | |
| INNER JOIN `department` dept ON dept.deptID = CAST(g.properties ->> '$.departmentId' AS CHAR) | |
| INNER JOIN `course_type` ct ON ct.courseTypeID = p.course_type_id | |
| LEFT JOIN ec_exam_registration_subject eers ON eers.ec_exam_registration_batch_id = eerb.id | |
| LEFT JOIN cm_academic_paper_subjects aps ON eers.cm_academic_paper_subjects_id = aps.id | |
| LEFT JOIN v4_ams_subject s ON aps.ams_subject_id = s.id".(count($where) ? ' WHERE '.implode(' AND ',$where) : "").$groupByQuerry.";"; | |
| try { | |
| $result = new stdClass(); | |
| $result->records = $this->executeQueryForList($query, $this->mapper[ExamRegistrationServiceMapper::SEARCH_EXAM_REGISTRATION_FILTER]); | |
| $result->totalRecords = $this->executeQueryForObject($recordCount)->examRegCount; | |
| } catch (\Exception $e) { | |
| throw new ExamControllerException(ExamControllerException::ERROR_FETCHING_EXAM_REGISTRATION,"Cannot fetch Exam Registration details! Please try again."); | |
| } | |
| return $result; | |
| } | |
| /** | |
| * search All Exam Registration | |
| * @param SearchExamRegistrationRequest $request | |
| * @return examRegistration | |
| */ | |
| public function searchAllExamRegistration( SearchExamRegistrationRequest $request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $where = []; | |
| $where [] = " g.type = '".ProfessionalGroupTypeConstant::BATCH."'"; | |
| $where [] = " eer.trashed IS NULL"; | |
| $joinQuery = []; | |
| $limitQuery = ""; | |
| $sortBy = " ORDER BY eer.created_date DESC"; | |
| if(!empty($request->courseTypeId)){ | |
| $where [] = " p.course_type_id = '$request->courseTypeId' "; | |
| $joinQuery [] = " INNER JOIN program p ON p.id = CAST(g.properties ->> '$.programId'AS CHAR) "; | |
| } | |
| if(!empty($request->isSpecialExam == '1')){ | |
| $where [] = " eer.properties->>'$.isSpecialExam' = true "; | |
| } | |
| else{ | |
| $where [] = " (eer.properties->>'$.isSpecialExam' != true OR eer.properties->>'$.isSpecialExam' IS NULL ) "; | |
| } | |
| if(!empty($request->departmentId)){ | |
| $where [] = " g.properties ->> '$.departmentId' = $request->departmentId "; | |
| } | |
| if(($request->isControllerSideRevaluationOnly)){ | |
| $where [] = " (eer.properties ->> '$.isStaffSideMarkEntry' != '1' OR eer.properties ->> '$.isStaffSideMarkEntry' IS NULL ) "; | |
| } | |
| if(($request->isStaffSideRevaluationOnly)){ | |
| $where [] = " eer.properties ->> '$.isStaffSideMarkEntry' = '1' "; | |
| } | |
| if(!empty($request->currentTermId)){ | |
| $where [] = " CAST(eerb.properties ->> '$.academicTermId'AS CHAR) = $request->currentTermId"; | |
| } | |
| !empty($request->type)?$where [] = " eer.type LIKE '%$request->type%'": $where [] = " eer.type IN ('REGULAR','SUPPLEMENTARY')"; | |
| !empty($request->startYear)?$where [] = " g.properties ->> '$.startYear' = $request->startYear ":null; | |
| if($request->startIndex !== "" && $request->endIndex !== ""){ | |
| $limitQuery .= " LIMIT $request->startIndex,$request->endIndex"; | |
| } | |
| $query = "SELECT | |
| DISTINCT(eer.id) as id, | |
| eer.identifying_context, | |
| eer.name, | |
| eer.type, | |
| IF((eer.properties->>'$.isHonorCourse') = '1', '1', '0') AS isHonorCourse, | |
| IF((eer.properties->>'$.isMinorCourse') = '1', '1', '0') AS isMinorCourse, | |
| eer.properties | |
| FROM | |
| ec_exam_registration eer | |
| INNER JOIN ec_exam_registration_batch eerb ON eerb.ec_exam_registration_id = eer.id | |
| INNER JOIN `groups` g ON g.id = eerb.groups_id | |
| ".(count($joinQuery) ? implode('',$joinQuery) : "")." | |
| ".(count($where) ? ' WHERE '.implode(' AND ',$where) : ""); | |
| $recordCount = "SELECT count(distinct eer.id) as `examRegCount` | |
| FROM | |
| ec_exam_registration eer | |
| INNER JOIN ec_exam_registration_batch eerb ON eerb.ec_exam_registration_id = eer.id | |
| INNER JOIN `groups` g ON g.id = eerb.groups_id | |
| ".(count($joinQuery) ? implode('',$joinQuery) : "")." | |
| ".(count($where) ? ' WHERE '.implode(' AND ',$where) : "").";"; | |
| try { | |
| $result = new stdClass(); | |
| $result->records = $this->executeQueryForList($query.$sortBy.$limitQuery); | |
| $result->totalRecords = $this->executeQueryForObject($recordCount)->examRegCount; | |
| } catch (\Exception $e) { | |
| throw new ExamControllerException(ExamControllerException::ERROR_FETCHING_EXAM_REGISTRATION,"Cannot fetch Exam Registration details! Please try again."); | |
| } | |
| return $result; | |
| } | |
| /** | |
| * Get Exam Registered Student Details | |
| * @params examRegistrationId | |
| * @return studentList | |
| */ | |
| public function getStudentExamAssignedDetails($request){ | |
| $request = $this->realEscapeObject($request); | |
| $limitQuerry = "Limit 1"; | |
| $whereQuery = ''; | |
| $query = " SELECT | |
| esar.student_id as studentId, | |
| esar.am_assessment_id as assessmentId | |
| FROM | |
| ec_exam_registration_batch eerb | |
| INNER JOIN ec_exam_registration_subject eers ON | |
| eers.ec_exam_registration_batch_id = eerb.id | |
| INNER JOIN ec_student_assessment_registration esar ON | |
| esar.am_assessment_id = eers.am_assessment_id AND esar.properties ->> '$.registrationStatus' IN ('APPLIED','REGISTERED') | |
| INNER JOIN ec_exam_registration eer ON | |
| eer.id = eerb.ec_exam_registration_id AND eer.type = esar.ec_exam_registration_type | |
| WHERE eerb .ec_exam_registration_id = '$request->examRegistrationId'"; | |
| try { | |
| $studentList = $this->executeQueryForList($query. $whereQuery.$limitQuerry); | |
| } catch (\Exception $e) { | |
| throw new ExamControllerException($e->getCode(),$e->getMessage()); | |
| } | |
| return $studentList; | |
| } | |
| /** | |
| * Get Exam Registered Student Details | |
| * @params examRegistrationId | |
| * @return studentList | |
| */ | |
| public function getRevaluationStudentAssignedDetails($request){ | |
| $request = $this->realEscapeObject($request); | |
| $limitQuerry = "Limit 1"; | |
| $whereQuery = ''; | |
| if($request->groupIds){ | |
| $whereQuery .= " AND eerb.groups_id IN ('$request->groupIds')"; | |
| } | |
| if(!empty($request->groupId)) { | |
| $groupIdString = is_array($request->groupId) ? "'" . implode("','",$request->groupId) . "'" : "'".$request->groupId."'"; | |
| $whereQuery .= " AND eerb.groups_id IN ( $groupIdString )"; | |
| } | |
| $query = " SELECT | |
| caps.id AS academicPaperSubjectId, | |
| esar.student_id AS studentId | |
| FROM | |
| ec_student_assessment_registration esar | |
| INNER JOIN ec_exam_registration eer ON | |
| eer.`type` = esar.ec_exam_registration_type AND eer.id = CAST(esar.identifying_context->>'$.examRegistrationId' AS CHAR) | |
| INNER JOIN ec_exam_registration_batch eerb ON | |
| eerb.ec_exam_registration_id = eer.id | |
| INNER JOIN ec_exam_registration eer2 ON | |
| eer2.id = eer.properties->>'$.parentExamRegistrationId' AND | |
| eer2.trashed IS NULL | |
| INNER JOIN ec_exam_registration_batch eerbParent ON | |
| eerbParent.ec_exam_registration_id = eer2.id AND | |
| eerb.groups_id = eerbParent.groups_id | |
| INNER JOIN ec_exam_registration_subject eers2 ON | |
| eers2.ec_exam_registration_batch_id = eerbParent.id | |
| INNER JOIN cm_academic_paper_subjects caps ON | |
| caps.id = eers2.cm_academic_paper_subjects_id AND eers2.am_assessment_id = esar.am_assessment_id | |
| WHERE eer.type = 'REVALUATION' AND eer.trashed IS NULL AND eer.id = '$request->examRegistrationId'"; | |
| try { | |
| $studentList = $this->executeQueryForList($query. $whereQuery.$limitQuerry); | |
| } catch (\Exception $e) { | |
| throw new ExamControllerException($e->getCode(),$e->getMessage()); | |
| } | |
| return $studentList; | |
| } | |
| /** | |
| * Search examRegistration | |
| * @param SearchExamRegistrationRequest $request | |
| * @return examRegistration | |
| */ | |
| public function getAllExamRevaluation(SearchExamRegistrationRequest $request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $whereQuery = ""; | |
| $limitQuery = ""; | |
| $sortBy = " ORDER BY eer.created_date DESC"; | |
| if(!empty($request->id)){ | |
| $whereQuery .= " AND eer.id='$request->id' "; | |
| } | |
| if(!empty($request->name)){ | |
| $whereQuery .= " AND eer.name LIKE '%$request->name%'"; | |
| } | |
| if(!empty($request->parentExamRegistrationId)) { | |
| $whereQuery .= " AND CAST(eer.properties->>'$.parentExamRegistrationId' AS CHAR) = '$request->parentExamRegistrationId'"; | |
| } | |
| if($request->trashed === StatusConstants::ACTIVE) { | |
| $whereQuery .= " AND eer.trashed IS NULL "; | |
| } | |
| if($request->trashed === StatusConstants::TRASHED) { | |
| $whereQuery .= " AND eer.trashed IS NOT NULL "; | |
| } | |
| if($request->startIndex !== "" && $request->endIndex !== ""){ | |
| $limitQuery .= " LIMIT $request->startIndex,$request->endIndex"; | |
| } | |
| if(!empty($request->courseTypeId)){ | |
| $whereQuery .= " AND p.course_type_id = '$request->courseTypeId' "; | |
| $joinQuery [] = " INNER JOIN program p ON p.id = CAST(g.properties ->> '$.programId'AS CHAR) "; | |
| } | |
| if(!empty($request->departmentId)){ | |
| $whereQuery .= " AND g.properties ->> '$.departmentId' = $request->departmentId "; | |
| } | |
| if(!empty($request->currentTermId)){ | |
| $whereQuery .= " AND CAST(eerb.properties ->> '$.academicTermId'AS CHAR) = $request->currentTermId"; | |
| } | |
| if(!empty($request->batchStartYear)){ | |
| $whereQuery .= " AND g.properties ->> '$.startYear' = '$request->batchStartYear'"; | |
| } | |
| if(!empty($request->parentExamType)){ | |
| $whereQuery .= " AND eerp.type = '$request->parentExamType'"; | |
| } | |
| if(!empty($request->groupId)){ | |
| $groupIdString = is_array($request->groupId) ? "'" . implode("','",$request->groupId) . "'" : "'".$request->groupId."'"; | |
| $whereQuery .= " AND eerb.groups_id IN ( $groupIdString )"; | |
| } | |
| $query = "SELECT | |
| eer.id, | |
| eer.identifying_context, | |
| eer.name, | |
| eer.type, | |
| eer.properties, | |
| eer.trashed, | |
| eer.created_by, | |
| eer.created_date, | |
| eer.updated_by, | |
| eer.updated_date, | |
| eerp.id as parentExamRegistrationId, | |
| eerp.name as parentExamRegistrationName, | |
| eerp.type as parentExamRegistrationType, | |
| eerb.id as examRegistrationBatchId, | |
| eerb.properties as examRegistrationBatchProperties, | |
| g.id as groupId, | |
| g.name as groupName, | |
| act.id as academicTermId, | |
| act.name as academicTermName | |
| FROM | |
| ec_exam_registration eer | |
| INNER JOIN ec_exam_registration eerp ON | |
| eerp.id = CAST(eer.properties->>'$.parentExamRegistrationId' AS CHAR) | |
| INNER JOIN ec_exam_registration_batch eerb ON eerb.ec_exam_registration_id = eer.id | |
| INNER JOIN `groups` g ON g.id = eerb.groups_id | |
| INNER JOIN `academic_term` act ON act.id = CAST(eerb.properties ->> '$.academicTermId'AS CHAR) | |
| ".(count($joinQuery) ? implode('',$joinQuery) : "")." | |
| WHERE | |
| 1 = 1 AND eer.type = 'REVALUATION'"; | |
| try { | |
| $examRevaluations = $this->executeQueryForList($query.$whereQuery.$sortBy.$limitQuery, $this->mapper[ExamRegistrationServiceMapper::SEARCH_EXAM_REVALUATION]); | |
| } catch (\Exception $e) { | |
| throw new ExamControllerException(ExamControllerException::ERROR_FETCHING_EXAM_REGISTRATION,"Cannot fetch Exam Registration details! Please try again."); | |
| } | |
| return $examRevaluations; | |
| } | |
| /** | |
| * Get Supply Registrations By Sem and batch | |
| * @param $request, $batchId | |
| * @return Object|null | |
| * @throws ExamControllerException | |
| */ | |
| public function getExamRegistrationOfABatchByRequest ( $request ) { | |
| $request = $this->realEscapeObject($request); | |
| $whereQuery = ""; | |
| $limitCond = ""; | |
| if(!empty($request->type)){ | |
| $whereQuery .= " AND eer.`type` ='$request->type' "; | |
| } | |
| if(!empty($request->groupId)){ | |
| $whereQuery .= " AND eerb.groups_id ='$request->groupId' "; | |
| } | |
| if(!empty($request->termId)){ | |
| $whereQuery .= " AND eerb.properties->>'$.academicTermId' ='$request->termId' "; | |
| } | |
| if(!empty($request->limit)){ | |
| $limitCond .= " LIMIT $request->limit"; | |
| } | |
| try { | |
| $sql = "SELECT | |
| eer.id AS examRegistrationId, eer.name | |
| FROM | |
| ec_exam_registration eer | |
| INNER JOIN ec_exam_registration_batch eerb ON | |
| eerb.ec_exam_registration_id = eer.id | |
| WHERE | |
| eer.trashed IS NULL $whereQuery | |
| ORDER BY | |
| IF(CAST(eer.properties->>'$.examYear' AS SIGNED) = 0, 99999, CAST(eer.properties->>'$.examYear' AS SIGNED)) ,IF(CAST(eer.properties->>'$.examMonth' AS SIGNED) = 0, 99999, CAST(eer.properties->>'$.examMonth' AS SIGNED))"; | |
| $supplyRegs = $this->executeQueryForList($sql.$limitCond); | |
| } | |
| catch(\Exception $e) { | |
| throw new ExamControllerException($e->getCode(),$e->getMessage()); | |
| } | |
| return $supplyRegs; | |
| } | |
| /** | |
| * Search examRegistration | |
| * @param $request | |
| * @return studentList | |
| */ | |
| public function getExamRegistrationStudentListByExamRegId($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $query = "SELECT | |
| DISTINCT sa.studentID as studentId, sa.studentName as name, sa.studentEmail as emailId, sa.studentPhone as mobileNo, act.properties ->> '$.orderNo' as termOrderNo, act.id as academicTermId, act.name as academicTermName, g.id as groupId, g.name as groupName, eer.properties ->> '$.examDate' as examDate, eer.properties ->> '$.registrationStartDate' as registrationStartDate | |
| FROM | |
| ec_exam_registration eer | |
| INNER JOIN ec_exam_registration_batch eerb ON | |
| eer.id = eerb.ec_exam_registration_id | |
| INNER JOIN academic_term act ON | |
| act.id = CAST(eerb.properties ->> '$.academicTermId'AS CHAR) | |
| INNER JOIN `groups` g ON | |
| g.id = eerb.groups_id | |
| AND g.`type` = 'BATCH' | |
| INNER JOIN group_members gm ON | |
| gm.groups_id = g.id | |
| INNER JOIN student_program_account spa ON | |
| spa.id = gm.members->>'$.studentId' | |
| INNER JOIN student_program_batch_log spbl ON | |
| spbl.batch_group_id = g.id AND | |
| spbl.term_id = CAST(eerb.properties ->> '$.academicTermId'AS CHAR) AND | |
| spbl.program_student_id = spa.id AND spbl.properties->>'$.academicStatus' IN ('ACTIVE','COMPLETED') | |
| INNER JOIN studentaccount sa ON | |
| sa.studentID = spa.student_id | |
| WHERE | |
| eer.id = '$request->examRegistrationId'"; | |
| try { | |
| $examRegistration = $this->executeQueryForList($query); | |
| } catch (\Exception $e) { | |
| throw new ExamControllerException(ExamControllerException::ERROR_FETCHING_EXAM_REGISTRATION,"Cannot fetch Exam Registration details! Please try again."); | |
| } | |
| return $examRegistration; | |
| } | |
| /** | |
| * Search examRevaluation By staff | |
| * @param $request | |
| * @return examRegistration | |
| */ | |
| public function getAllAssignedExamRevaluationByCurrentStaff( $request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $currentUerId = $GLOBALS['userId']; | |
| $whereQuery = ""; | |
| $limitQuery = ""; | |
| if(!empty($request->type)) | |
| { | |
| $whereQuery .= " AND eer.type LIKE 'REVALUATION'"; | |
| } | |
| if(($request->isStaffSideRevaluationOnly)){ | |
| $whereQuery .= " AND eer.properties ->> '$.isStaffSideMarkEntry' = '1' "; | |
| } | |
| $query = "SELECT | |
| DISTINCT(eer.id), | |
| eer.name, | |
| eer.properties as properties | |
| FROM ec_exam_registration_batch eerb | |
| INNER JOIN ec_exam_registration eer ON | |
| eer.id = eerb.ec_exam_registration_id AND | |
| eer.trashed IS NULL | |
| INNER JOIN ec_exam_registration eerp ON | |
| eerp.id = eer.properties->>'$.parentExamRegistrationId' AND | |
| eerp.trashed IS NULL | |
| INNER JOIN ec_exam_registration_batch eerbParent ON | |
| eerbParent.ec_exam_registration_id = eerp.id AND | |
| eerbParent.groups_id = eerb.groups_id | |
| INNER JOIN ec_exam_registration_subject eers ON | |
| eers.ec_exam_registration_batch_id = eerbParent.id | |
| WHERE | |
| JSON_CONTAINS(JSON_EXTRACT(eers.valuation_details ,'$.reValuationStaffs'),JSON_OBJECT('addiitonalExamniners', '$currentUerId')) $whereQuery OR | |
| JSON_CONTAINS(JSON_EXTRACT(eers.valuation_details ,'$.reValuationStaffs'),JSON_OBJECT('chiefExaminer', '$currentUerId')) $whereQuery"; | |
| try { | |
| $examRegistration = $this->executeQueryForList($query.$limitQuery, $this->mapper[ExamRegistrationServiceMapper::SEARCH_EXAM_REGISTRATION]); | |
| $searchFeeReq = new \stdClass; | |
| $searchFeeReq->examType = 'REVALUATION'; | |
| $searchFeeReq->isMarkEntryNeeded = true; | |
| $revaluationFeeTypes = ExamFeeTypeService::getInstance()->getFeeTypes($searchFeeReq); | |
| $revaluationFeeTypeIds = array_column($revaluationFeeTypes, 'id'); | |
| foreach($examRegistration as $key => $examReg){ | |
| if(!empty($examReg->properties->revaluationFeeTypeIds)){ | |
| if(empty(array_intersect($examReg->properties->revaluationFeeTypeIds, $revaluationFeeTypeIds))){ | |
| unset($examRegistration[$key]); | |
| } | |
| } | |
| } | |
| $examRegistration = array_values($examRegistration); | |
| } catch (\Exception $e) { | |
| throw new ExamControllerException(ExamControllerException::ERROR_FETCHING_EXAM_REGISTRATION,"Cannot fetch Exam Registration details! Please try again."); | |
| } | |
| return $examRegistration; | |
| } | |
| /** | |
| * Search examRegistration Valuation Count | |
| * @param $request | |
| * @return examRegistration | |
| */ | |
| public function getAllAssignedExamRevaluationValuationCountByCurrentStaff($request){ | |
| $subjecs = []; | |
| $valuationCountObjectArray = []; | |
| $valuationCountArray = []; | |
| $revaluationTypeIdArray = []; | |
| $response = new \stdClass; | |
| $revaluationTypeArray = []; | |
| $currentUerId = $GLOBALS['userId']; | |
| $subjects = ExamRegistrationSubjectService::getInstance()->getAllCurretStaffSubjectsValuationDetailsByExamRevaluationId($request); | |
| foreach($subjects as $subject){ | |
| $subject->valuationDetails = json_decode($subject->valuationDetails); | |
| foreach($subject->valuationDetails->reValuationStaffs as $valuationStaff){ | |
| foreach($valuationStaff->addiitonalExamniners as $staff){ | |
| if($currentUerId == $staff){ | |
| $valuationCountArray[$valuationStaff->count] = $valuationStaff->count; | |
| $revaluationTypeIdArray[$valuationStaff->revaluationType] = $valuationStaff->revaluationType; | |
| } | |
| } | |
| } | |
| } | |
| $feeRequest = new \stdClass; | |
| $feeRequest->examType = 'REVALUATION'; | |
| $allCommonFees = CommonExamService::getInstance()->getAllFeesTypes($feeRequest); | |
| foreach($allCommonFees as $fee){ | |
| if(in_array($fee->id,$revaluationTypeIdArray)){ | |
| $revaluationType = new \stdClass; | |
| $revaluationType->id= $fee->id; | |
| $revaluationType->text= $fee->name; | |
| $revaluationTypeArray[]= $revaluationType; | |
| } | |
| } | |
| sort($valuationCountArray); | |
| foreach($valuationCountArray as $valuationCount){ | |
| $valuaionCount = new \stdClass; | |
| $valuaionCount->id= $valuationCount; | |
| $valuaionCount->text= $valuationCount; | |
| $valuationCountObjectArray[]= $valuaionCount; | |
| } | |
| $response->valuaionCount= $valuationCountObjectArray; | |
| $response->revaluationType= $revaluationTypeArray; | |
| return $response; | |
| } | |
| /** | |
| * Publish exam time table in student side | |
| * @param $request | |
| * @return NULL | |
| */ | |
| public function publishExamTimeTableInStudentSide($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $updatedBy = $GLOBALS['userId']; | |
| $staffId = $GLOBALS['userId']; | |
| if(empty($request->examRegistrationId)) | |
| throw new ExamControllerException(ExamControllerException::EMPTY_PARAMETERS_EXAM_REGISTRATION,"Exam Registration is invalid! Please enter a valid Exam Registration"); | |
| $publish = $request->publish ? 1 : 0; | |
| $query = "UPDATE | |
| ec_exam_registration | |
| SET | |
| properties = JSON_SET(properties, '$.publishExamTimeTableStudent', $publish), | |
| updated_by = '$updatedBy' | |
| WHERE | |
| id = '$request->examRegistrationId'"; | |
| try { | |
| $this->executeQuery($query); | |
| AMSLogger::log_info($this->logger,Events::EC_UPDATE_EXAM_REGISTRATION,[ | |
| "staff" => new Staff(["id" => $staffId]), | |
| "request" => $request, | |
| "status" => StatusConstants::SUCCESS | |
| ]); | |
| } catch (\Exception $e) { | |
| AMSLogger::log_error($this->logger,Events::EC_UPDATE_EXAM_REGISTRATION, [ | |
| "staff" => new Staff(["id" => $staffId]), | |
| "request" => $request, | |
| "errorCode" => $e->getCode(), | |
| "errorMessage" => $e->getMessage(), | |
| "status" => StatusConstants::FAILED | |
| ]); | |
| throw new ExamControllerException(ExamControllerException::ERROR_UPDATE_PUBLISH_STATUS_EXAM_REGISTRATION,"Error deleting Exam Registration! Please try again"); | |
| } | |
| } | |
| /** | |
| * Get all exam registation count | |
| * Features : Dashboard | |
| */ | |
| public function getExamRegistrationCount($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| try { | |
| $sql = "SELECT | |
| eer.id, | |
| eer.name, | |
| eer.properties, | |
| eeft.id as feeTemplateId, | |
| eeft.fine_properties as fineProperties | |
| FROM | |
| ec_exam_registration eer | |
| INNER JOIN ec_examregistration_fee_templates_mapping eeftm ON | |
| eeftm.ec_exam_registration_id = eer.id | |
| INNER JOIN ec_examregistration_fee_templates eeft ON | |
| eeft.ec_examregistration_fee_templates_mapping_id = eeftm.id | |
| WHERE `type` = '$request->examType' AND trashed IS NULL "; | |
| $examRegistrationList = $this->executeQueryForList($sql, $this->mapper[ExamRegistrationServiceMapper::LIST_EXAM_REGISTRATION]); | |
| $examRegistrationDetails = new \stdClass; | |
| $examRegistrationDetails->totalCount = count($examRegistrationList); | |
| $today = date("Y-m-d H:i"); | |
| foreach ($examRegistrationList as $examRegistrationDetail ){ | |
| $endDate = ""; | |
| foreach ($examRegistrationDetail->templates as $feeTemplate ){ | |
| foreach ($feeTemplate->fineProperties as $fineProperties ){ | |
| foreach ($fineProperties->fineType as $fineType ){ | |
| if($endDate && (strtotime($fineType->endDate) > strtotime($endDate))){ | |
| $endDate = $fineType->endDate; | |
| } | |
| else if(empty( $endDate )){ | |
| $endDate = $fineType->endDate; | |
| } | |
| } | |
| } | |
| } | |
| $examRegistrationDetail->endDate = $endDate; | |
| } | |
| foreach ($examRegistrationList as $examRegistrationDetail ){ | |
| if((strtotime($today) <= strtotime($examRegistrationDetail->endDate)) && (strtotime($today) >= strtotime($examRegistrationDetail->properties->registrationStartDate))){ | |
| $examRegistrationDetails->onGoingCount++; | |
| } | |
| } | |
| } catch (\Exception $e) { | |
| throw new ExamControllerException($e->getCode(),$e->getMessage()); | |
| } | |
| return $examRegistrationDetails; | |
| } | |
| /** | |
| * list of supplementary exam's month and year semsterwise by student | |
| * @param $request | |
| * @return examRegistration | |
| */ | |
| public function supplyExamMonthAndYearSemesterWiseByStudent( $request){ | |
| $request = $this->realEscapeObject($request); | |
| $whereQuery = ""; | |
| $orderBy = " ORDER BY eer.properties ->> '$.examYear' ASC ,eer.properties ->> '$.examMonth' + 0 ASC "; | |
| if(empty($request->groupId)) { | |
| throw new ExamControllerException(ExamControllerException::EMPTY_PARAMETERS_EXAM_REGISTRATION, " Empty Parameter ! Batch group id is empty "); | |
| } | |
| if(!empty($request->groupId)) { | |
| $groupIdString = is_array($request->groupId) ? "'" . implode("','",$request->groupId) . "'" : "'".$request->groupId."'"; | |
| $whereQuery .= " AND eerb.groups_id IN ( $groupIdString )"; | |
| } | |
| if(!empty($request->studentId)) { | |
| $studentIdString = is_array($request->studentId) ? "'" . implode("','",$request->studentId) . "'" : "'".$request->studentId."'"; | |
| $whereQuery .= " AND esar.student_id IN ( $studentIdString )"; | |
| } | |
| $query = "SELECT | |
| DISTINCT(eer.id), | |
| eer.name AS name, | |
| eer.type AS type, | |
| eer.properties ->> '$.examMonth' AS examMonth, | |
| MONTHNAME(STR_TO_DATE(eer.properties ->> '$.examMonth', '%m')) AS examMonthName, | |
| eer.properties ->> '$.examYear' AS examYear, | |
| act.id as academicTermId, | |
| act.name as academicTermName | |
| FROM | |
| ec_exam_registration eer | |
| INNER JOIN ec_exam_registration_batch eerb | |
| ON eerb.ec_exam_registration_id = eer.id | |
| INNER JOIN ec_exam_registration_subject eers ON | |
| eers.ec_exam_registration_batch_id = eerb.id | |
| INNER JOIN ec_student_assessment_registration esar ON | |
| esar.am_assessment_id = eers.am_assessment_id AND | |
| CAST(esar.properties ->> '$.registrationStatus' AS CHAR) = 'REGISTERED' AND | |
| CAST(esar.properties ->> '$.feeStatus' AS CHAR) = 'PAID' AND | |
| esar.ec_exam_registration_type = eer.type | |
| INNER JOIN academic_term act ON | |
| act.id = CAST(eerb.properties ->> '$.academicTermId'AS CHAR) AND | |
| act.type = 'SEMESTER' | |
| WHERE | |
| eer.trashed IS NULL AND eer.type = 'SUPPLEMENTARY' "; | |
| try { | |
| $examDetails = $this->executeQueryForList($query.$whereQuery.$orderBy); | |
| $semesterWiseDetails = []; | |
| foreach ($examDetails as $examDetail) { | |
| $semesterWiseDetails[$examDetail->academicTermId]->exams[] = $examDetail; | |
| } | |
| } catch (\Exception $e) { | |
| throw new ExamControllerException(ExamControllerException::ERROR_FETCHING_EXAM_REGISTRATION,"Cannot fetch Exam Registration details! Please try again."); | |
| } | |
| return $semesterWiseDetails; | |
| } | |
| /** | |
| * list of exam's month and year By batch | |
| */ | |
| public function getExamMonthYearsOfBatchGroup($request){ | |
| $request = $this->realEscapeObject($request); | |
| $whereQuery = ""; | |
| $orderBy = " ORDER BY eer.properties ->> '$.examYear' ASC ,eer.properties ->> '$.examMonth' + 0 ASC "; | |
| if(empty($request->groupId)) { | |
| throw new ExamControllerException(ExamControllerException::EMPTY_PARAMETERS_EXAM_REGISTRATION, " Empty Parameter ! Batch group id is empty "); | |
| } | |
| if(!empty($request->groupId)) { | |
| $groupIdString = is_array($request->groupId) ? "'" . implode("','",$request->groupId) . "'" : "'".$request->groupId."'"; | |
| $whereQuery .= " AND eerb.groups_id IN ( $groupIdString )"; | |
| } | |
| $query = "SELECT DISTINCT | |
| DATE_FORMAT(CONCAT(eer.properties ->> '$.examYear','-',eer.properties ->> '$.examMonth','-','1'),'%Y-%m') AS id, | |
| DATE_FORMAT(CONCAT(eer.properties ->> '$.examYear','-',eer.properties ->> '$.examMonth','-','1'),'%Y %b') name | |
| FROM | |
| ec_exam_registration eer | |
| INNER JOIN ec_exam_registration_batch eerb | |
| ON eerb.ec_exam_registration_id = eer.id | |
| WHERE | |
| eer.trashed IS NULL AND eer.properties ->> '$.examYear' IS NOT NULL"; | |
| try { | |
| $examMonthYears = $this->executeQueryForList($query.$whereQuery.$orderBy); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $examMonthYears; | |
| } | |
| /** | |
| * Save Institutional Average Generate Status | |
| * @param $request | |
| * @return NULL | |
| */ | |
| public function saveInstitutionalAverageGenerateStatus($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $updatedBy = $request->$staffId?? $GLOBALS['userId']; | |
| $staffId = $request->$staffId?? $GLOBALS['userId']; | |
| if(empty($request->examRegistrationId)) | |
| throw new ExamControllerException(ExamControllerException::EMPTY_PARAMETERS_EXAM_REGISTRATION,"Exam Registration is invalid! Please enter a valid Exam Registration"); | |
| $query = "UPDATE | |
| ec_exam_registration | |
| SET | |
| properties = JSON_SET(properties, '$.institutionalAverageGenerateStatus','$request->status'), | |
| updated_by = '$updatedBy' | |
| WHERE | |
| id = '$request->examRegistrationId'"; | |
| try { | |
| $this->executeQuery($query); | |
| $this->logger->info(Events::EC_UPDATE_EXAM_REGISTRATION,[ | |
| "staff" => new Staff(["id" => $staffId]), | |
| "request" => $request, | |
| "status" => StatusConstants::SUCCESS | |
| ]); | |
| } catch (\Exception $e) { | |
| $this->logger->error(Events::EC_UPDATE_EXAM_REGISTRATION, [ | |
| "staff" => new Staff(["id" => $staffId]), | |
| "request" => $request, | |
| "errorCode" => $e->getCode(), | |
| "errorMessage" => $e->getMessage(), | |
| "status" => StatusConstants::FAILED | |
| ]); | |
| throw new ExamControllerException(ExamControllerException::ERROR_UPDATE_PUBLISH_STATUS_EXAM_REGISTRATION,"Error deleting Exam Registration! Please try again"); | |
| } | |
| } | |
| /** | |
| * get exam registration by current staff | |
| * @param $request | |
| * @return examRegistrations | |
| */ | |
| public function getStaffHandlingDepartmentExamRegistrations($request){ | |
| $request = $this->realEscapeObject($request); | |
| $whereQuery = ""; | |
| $limitQuery = ""; | |
| $examRegistrations = []; | |
| $joinQuery = ""; | |
| $sortBy = " ORDER BY eer.created_date DESC"; | |
| if(!empty($request->examType)) { | |
| $whereQuery .= " AND eer.type LIKE '%$request->examType%'"; | |
| } | |
| if(!empty($request->groupId)) { | |
| $whereQuery .= " AND g.id = '$request->groupId'"; | |
| } | |
| if($request->haveNotDepartmentRestriction) { | |
| if(!empty($request->departmentId)){ | |
| $whereQuery .= " AND pdr.department_id = '$request->departmentId'"; | |
| } | |
| } | |
| else if(!$request->isTutorWiseResultSheet){ | |
| $joinQuery .= "INNER JOIN staffaccounts s ON s.deptID = pdr.department_id "; | |
| $whereQuery .= " AND s.staffID = '$request->staffId'"; | |
| } | |
| $query = "SELECT | |
| eer.id AS id, | |
| eer.name AS name, | |
| eer.type AS type, | |
| eer.properties as examRegistrationProperties, | |
| eerb.properties as examRegistrationBatchProperties, | |
| g.id as groupId, | |
| g.name as groupName | |
| FROM `groups` g | |
| INNER JOIN program_department_relation pdr ON pdr.program_id = g.program_id | |
| INNER JOIN ec_exam_registration_batch eerb ON eerb.groups_id = g.id | |
| INNER JOIN ec_exam_registration eer ON eer.id = eerb.ec_exam_registration_id | |
| $joinQuery | |
| WHERE eer.trashed IS NULL"; | |
| try { | |
| $examRegistrationsArray = $this->executeQueryForList($query.$whereQuery.$sortBy.$limitQuery); | |
| } catch (\Exception $e) { | |
| throw new ExamControllerException(ExamControllerException::ERROR_FETCHING_EXAM_REGISTRATION,"Cannot fetch Exam Registration details! Please try again."); | |
| } | |
| foreach($examRegistrationsArray as $examRegistration){ | |
| $examRegistrations[$examRegistration->id]->id = $examRegistration->id; | |
| $examRegistrations[$examRegistration->id]->name = $examRegistration->name; | |
| $examRegistrations[$examRegistration->id]->type = $examRegistration->type; | |
| $examRegistrations[$examRegistration->id]->properties = json_decode($examRegistration->examRegistrationProperties); | |
| $examRegistrations[$examRegistration->id]->batchProperties = json_decode($examRegistration->examRegistrationBatchProperties); | |
| $examRegistrations[$examRegistration->id]->groups[$examRegistration->groupId]->id = $examRegistration->groupId; | |
| $examRegistrations[$examRegistration->id]->groups[$examRegistration->groupId]->name = $examRegistration->groupName; | |
| $examRegistrations[$examRegistration->id]->groups[$examRegistration->groupId]->text = $examRegistration->groupName; | |
| } | |
| $examRegistrations = array_values($examRegistrations); | |
| array_walk($examRegistrations, function($examRegistration){ | |
| $examRegistration->groups = array_values($examRegistration->groups); | |
| }); | |
| return $examRegistrations; | |
| } | |
| /** | |
| * Save exam revaluation Memo details | |
| * @param $request | |
| */ | |
| public function saveRevaluationMemoDetails($request){ | |
| $columnVal = ""; | |
| $query = "UPDATE | |
| ec_exam_registration | |
| SET | |
| properties = JSON_SET(properties, '$.memoNumber','$request->memoNumber'), | |
| properties = JSON_SET(properties, '$.memoDate','$request->memoDate') | |
| WHERE | |
| id IN ('$request->examRegistrationId') AND `type` = 'REVALUATION'"; | |
| try { | |
| $this->executeQuery($query); | |
| } catch (\Exception $e) { | |
| throw new ExamControllerException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * Search Batch Start Year by exam Registration | |
| * @param $request | |
| * @return startYears | |
| */ | |
| public function getExamRegistrationStartYears( $request){ | |
| $request = $this->realEscapeObject($request); | |
| $orderBy = " ORDER BY CAST(g.properties ->> '$.startYear' AS UNSIGNED) ASC"; | |
| $whereQuery = ""; | |
| if(!empty($request->courseTypeId)){ | |
| $whereQuery .= " AND p.course_type_id = '$request->courseTypeId' "; | |
| } | |
| if(!empty($request->examRegistrationId)){ | |
| $whereQuery .= " AND eer.id = '$request->examRegistrationId'"; | |
| } | |
| $query = "SELECT | |
| DISTINCT g.properties ->> '$.startYear' as id, | |
| g.properties ->> '$.startYear' as name | |
| FROM | |
| ec_exam_registration eer | |
| INNER JOIN ec_exam_registration_batch eerb | |
| ON eerb.ec_exam_registration_id = eer.id | |
| INNER JOIN `groups` g | |
| ON g.id = eerb.groups_id | |
| INNER JOIN program p | |
| ON p.id = g.properties->>'$.programId' | |
| WHERE | |
| eer.trashed IS NULL"; | |
| try { | |
| $startYears = $this->executeQueryForList($query.$whereQuery.$orderBy); | |
| } catch (\Exception $e) { | |
| throw new ExamControllerException(ExamControllerException::ERROR_FETCHING_EXAM_REGISTRATION,"Cannot fetch Exam Registration Start year details! Please try again."); | |
| } | |
| return $startYears; | |
| } | |
| /** | |
| * get recent published exam registration | |
| * @param $request | |
| * @return examRegistration | |
| */ | |
| public function getRecentPublishedExamRegistration($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $whereQuery = ""; | |
| $sortBy = " ORDER BY eer.properties ->> '$.examYear' DESC ,eer.properties ->> '$.examMonth' + 0 DESC "; | |
| $query = "SELECT | |
| eer.id AS examRegistrationId | |
| FROM | |
| ec_exam_registration eer | |
| INNER JOIN ec_exam_registration_batch eerb ON | |
| eerb.ec_exam_registration_id = eer.id | |
| WHERE | |
| eer.`type` = 'REGULAR' AND | |
| trashed IS NULL AND | |
| eerb.properties ->>'$.isResultPublished' = 1"; | |
| try { | |
| $examRegistration = $this->executeQueryForObject($query.$whereQuery.$sortBy); | |
| } catch (\Exception $e) { | |
| throw new ExamControllerException(ExamControllerException::ERROR_FETCHING_EXAM_REGISTRATION,"Cannot fetch Exam Registration details! Please try again."); | |
| } | |
| return $examRegistration; | |
| } | |
| /** | |
| * get batch assigned exam registration | |
| * @param $request | |
| * @return examRegistration | |
| */ | |
| public function getBatchAssignedExamRegistration($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $whereQuery = ""; | |
| $sortBy = " ORDER BY eer.properties ->> '$.examYear' ASC ,eer.properties ->> '$.examMonth' + 0 ASC "; | |
| $whereQuery = ""; | |
| if(!empty($request->groupId)){ | |
| $whereQuery .= " AND eerb.groups_id = '$request->groupId' "; | |
| } | |
| if(!empty($request->examRegType)){ | |
| $whereQuery .= " AND eer.`type` = '$request->examRegType'"; | |
| } | |
| $query = "SELECT | |
| eer.id AS id, | |
| eer.name AS name, | |
| eerb.id AS batchRelationId, | |
| eerb.properties->>'$.isRequiredForImport' AS statusFlag, | |
| eer.`type` | |
| FROM | |
| ec_exam_registration eer | |
| INNER JOIN ec_exam_registration_batch eerb ON | |
| eerb.ec_exam_registration_id = eer.id | |
| WHERE | |
| trashed IS NULL "; | |
| try { | |
| $examRegistration = $this->executeQueryForList($query.$whereQuery.$sortBy); | |
| } catch (\Exception $e) { | |
| throw new ExamControllerException(ExamControllerException::ERROR_FETCHING_EXAM_REGISTRATION,"Cannot fetch Exam Registration details! Please try again."); | |
| } | |
| return $examRegistration; | |
| } | |
| /** | |
| * list of supplementary publising details | |
| * @param $request | |
| * @return examRegistration | |
| */ | |
| public function getSupplyPublishDetailsByBatch( $request){ | |
| $request = $this->realEscapeObject($request); | |
| $whereQuery = ""; | |
| if(empty($request->groupId)) { | |
| throw new ExamControllerException(ExamControllerException::EMPTY_PARAMETERS_EXAM_REGISTRATION, " Empty Parameter ! Batch group id is empty "); | |
| } | |
| if(!empty($request->groupId)) { | |
| $groupIdString = is_array($request->groupId) ? "'" . implode("','",$request->groupId) . "'" : "'".$request->groupId."'"; | |
| $whereQuery .= " AND eerb.groups_id IN ( $groupIdString )"; | |
| } | |
| if(!empty($request->studentId)) { | |
| $studentIdString = is_array($request->studentId) ? "'" . implode("','",$request->studentId) . "'" : "'".$request->studentId."'"; | |
| $whereQuery .= " AND esar.student_id IN ( $studentIdString )"; | |
| } | |
| if (!empty($request->publishStartDate) &&!empty($request->publishEndDate) ) { | |
| $request->publishStartDate = date('Y-m-d', strtotime($request->publishStartDate)); | |
| $request->publishStartDate = date('Y-m-d', strtotime($request->publishStartDate)); | |
| $sql .= " AND eerb.properties->> '$.publishingStartDate' BETWEEN '$request->publishStartDate' AND '$request->publishEndDate'"; | |
| } | |
| $query = "SELECT | |
| DISTINCT(eer.id), | |
| eer.name AS name, | |
| eer.type AS type, | |
| eerb.properties->> '$.publishingStartDate' as publishingStartDate, | |
| eerb.properties->> '$.publishingEndDate' as publishingEndDate, | |
| act.id as academicTermId, | |
| act.name as academicTermName | |
| FROM | |
| ec_exam_registration eer | |
| INNER JOIN ec_exam_registration_batch eerb | |
| ON eerb.ec_exam_registration_id = eer.id | |
| INNER JOIN ec_exam_registration_subject eers ON | |
| eers.ec_exam_registration_batch_id = eerb.id | |
| INNER JOIN ec_student_assessment_registration esar ON | |
| esar.am_assessment_id = eers.am_assessment_id AND | |
| CAST(esar.properties ->> '$.registrationStatus' AS CHAR) = 'REGISTERED' AND | |
| CAST(esar.properties ->> '$.feeStatus' AS CHAR) = 'PAID' AND | |
| esar.ec_exam_registration_type = eer.type | |
| INNER JOIN academic_term act ON | |
| act.id = CAST(eerb.properties ->> '$.academicTermId'AS CHAR) | |
| WHERE | |
| eer.trashed IS NULL AND eer.type = 'SUPPLEMENTARY' AND eerb.properties->> '$.isResultPublished' = 1 "; | |
| try { | |
| $examDetails = $this->executeQueryForList($query.$whereQuery.$orderBy); | |
| } catch (\Exception $e) { | |
| throw new ExamControllerException(ExamControllerException::ERROR_FETCHING_EXAM_REGISTRATION,"Cannot fetch Exam Registration details! Please try again."); | |
| } | |
| return $examDetails; | |
| } | |
| /** | |
| * Search examRegistration | |
| * @param $request | |
| * @return examRegistrations | |
| */ | |
| public function getAllExamRegistrationForVivaValuationStaff( $request){ | |
| $request = $this->realEscapeObject($request); | |
| $currentUerId = $GLOBALS['userId']; | |
| $whereQuery = ""; | |
| $limitQuery = ""; | |
| $query = "SELECT | |
| DISTINCT(eer.id), | |
| eer.name | |
| FROM | |
| cluster_groups_relations cgr | |
| INNER JOIN cluster_members cm ON | |
| cm.cluster_id = cgr.cluster_id | |
| INNER JOIN `groups` sg ON | |
| sg.id = cgr.groups_id | |
| INNER JOIN groups_relations gr ON | |
| gr.child_groups_id = sg.id | |
| INNER JOIN `groups` g ON | |
| g.id = gr.parent_groups_id AND g.type = 'BATCH' | |
| INNER JOIN ec_exam_registration_subject eers ON | |
| eers.cm_academic_paper_subjects_id = sg.paperSubjectId | |
| INNER JOIN ec_exam_registration_batch eerb ON | |
| eerb.id = eers.ec_exam_registration_batch_id AND eerb.groups_id = g.id | |
| INNER JOIN ec_exam_registration eer ON | |
| eer.id = eerb.ec_exam_registration_id AND eer.trashed IS NULL | |
| WHERE | |
| cm.staff_id = '$currentUerId' "; | |
| try { | |
| $examRegistrations = $this->executeQueryForList($query.$limitQuery, $this->mapper[ExamRegistrationServiceMapper::SEARCH_EXAM_REGISTRATION]); | |
| } catch (\Exception $e) { | |
| throw new ExamControllerException(ExamControllerException::ERROR_FETCHING_EXAM_REGISTRATION,"Cannot fetch Exam Registration details! Please try again."); | |
| } | |
| return $examRegistrations; | |
| } | |
| /** | |
| * get Minor Honor Mooc Applied Students | |
| * @param $searchRequest | |
| * @return $response | |
| */ | |
| public function getMinorHonorMoocAppliedStudents($searchRequest) { | |
| $searchRequest = $this->realEscapeObject($searchRequest); | |
| $response = new \stdClass; | |
| try{ | |
| $studentsDetails = []; | |
| $request = new \stdClass; | |
| $request->groupId = $searchRequest->groupId; | |
| $request->examRegistrationId = $searchRequest->examRegistrationId; | |
| $studentsDetails = $this->getMoocAppliedStudentDetails($request); | |
| foreach($studentsDetails as $student){ | |
| $requestForCertificate = new \stdClass(); | |
| $requestForCertificate->groupId = $student->groupId; | |
| $requestForCertificate->studentId = $student->studentId; | |
| $requestForCertificate->assessmentId = $student->assessmentId; | |
| $requestForCertificate->type = 'MOOC_COURSE_CERTIFICATES'; | |
| $student->uploadedCertificates = CertificateUploadService::getInstance()->getCertificateUpload($requestForCertificate); | |
| if(empty($student->uploadedCertificates)){ | |
| $student->verificationStatus = "NO_DOCUMENTS"; | |
| } | |
| } | |
| } | |
| catch (\Exception $e){ | |
| throw new ExamControllerException($e->getCode(),$e->getMessage()); | |
| } | |
| $response->students = $studentsDetails; | |
| return $response; | |
| } | |
| /** | |
| * Search Course Type of exam registration | |
| * @param $request | |
| * @return examRegistration | |
| */ | |
| public function getCourseTypeOfExamRegistration( $request) { | |
| $request = $this->realEscapeObject($request); | |
| $whereQuery = ""; | |
| if(!empty($request->examRegistrationId)){ | |
| $whereQuery .= " AND eer.id='$request->examRegistrationId' "; | |
| } | |
| $query = "SELECT | |
| DISTINCT | |
| ct.courseTypeID, | |
| ct.course_Type as courseTypeName | |
| FROM | |
| ec_exam_registration eer | |
| INNER JOIN ec_exam_registration_batch eerb | |
| ON eerb.ec_exam_registration_id = eer.id | |
| INNER JOIN ec_exam_registration_subject eers ON | |
| eers.ec_exam_registration_batch_id = eerb.id | |
| INNER JOIN `groups` g | |
| ON g.id = eerb.groups_id | |
| INNER JOIN program p ON | |
| p.id = CAST(g.properties ->> '$.programId' AS CHAR) | |
| INNER JOIN `course_type` ct ON | |
| ct.courseTypeID = p.course_type_id | |
| WHERE | |
| eer.trashed IS NULL "; | |
| try { | |
| $courseType = $this->executeQueryForObject($query.$whereQuery); | |
| } catch (\Exception $e) { | |
| throw new ExamControllerException(ExamControllerException::ERROR_FETCHING_EXAM_REGISTRATION,"Cannot fetch Exam Registration details! Please try again."); | |
| } | |
| return $courseType; | |
| } | |
| /** | |
| * get Mooc Applied Student Details | |
| * @param $request | |
| * @return examRegistration | |
| */ | |
| public function getMoocAppliedStudentDetails( $request){ | |
| $request = $this->realEscapeObject($request); | |
| $whereQuery = ""; | |
| $orderBy = " ORDER BY spa.properties->>'$.registerNumber' ASC"; | |
| if(empty($request->groupId) || empty($request->examRegistrationId)) { | |
| throw new ExamControllerException(ExamControllerException::EMPTY_PARAMETERS_EXAM_REGISTRATION, " Empty Parameter ! Batch group id is empty "); | |
| } | |
| if(!empty($request->groupId)) { | |
| $groupIdString = is_array($request->groupId) ? "'" . implode("','",$request->groupId) . "'" : "'".$request->groupId."'"; | |
| $whereQuery .= " AND eerb.groups_id IN ( $groupIdString )"; | |
| } | |
| if(!empty($request->examRegistrationId)) { | |
| $examRegistrationIdString = is_array($request->examRegistrationId) ? "'" . implode("','",$request->examRegistrationId) . "'" : "'".$request->examRegistrationId."'"; | |
| $whereQuery .= " AND eer.id IN ( $examRegistrationIdString )"; | |
| } | |
| $query = "SELECT | |
| sa.studentID AS studentId, | |
| sa.myImage AS studentImage, | |
| sa.studentName, | |
| spa.properties->>'$.registerNumber' AS regNo, | |
| eer.id AS examRegistrationId, | |
| eer.name AS ExamRegistrationName, | |
| g.id as groupId, | |
| g.name as groupName, | |
| esar.am_assessment_id as assessmentId, | |
| s.code AS subjectCode, | |
| s.name AS subjectName, | |
| esar.properties->>'$.moocCertificateStatus' AS verificationStatus | |
| FROM | |
| ec_exam_registration eer | |
| INNER JOIN ec_exam_registration_batch eerb | |
| ON eerb.ec_exam_registration_id = eer.id | |
| INNER JOIN ec_exam_registration_subject eers ON | |
| eers.ec_exam_registration_batch_id = eerb.id | |
| INNER JOIN ec_student_assessment_registration esar ON | |
| esar.am_assessment_id = eers.am_assessment_id AND | |
| CAST(esar.properties ->> '$.registrationStatus' AS CHAR) = 'REGISTERED' | |
| INNER JOIN `groups` g ON | |
| g.id = eerb.groups_id | |
| INNER JOIN studentaccount sa ON | |
| sa.studentID = esar.student_id | |
| INNER JOIN program p ON | |
| p.id = g.properties->>'$.programId' | |
| INNER JOIN student_program_account spa ON | |
| spa.student_id = esar.student_id AND | |
| spa.current_program_id = p.id | |
| INNER JOIN ec_student_exam_registration_details eserd ON | |
| eserd.ec_exam_registration_id = eer.id AND | |
| eserd.student_id = esar.student_id | |
| INNER JOIN cm_academic_paper_subjects aps ON | |
| aps.id = eers.cm_academic_paper_subjects_id | |
| INNER JOIN v4_ams_subject s ON | |
| aps.ams_subject_id = s.id | |
| WHERE | |
| eer.trashed IS NULL AND esar.properties->>'$.syllabusSubType' = 'MOOC' "; | |
| try { | |
| $studentDetails = $this->executeQueryForList($query.$whereQuery.$orderBy); | |
| } catch (\Exception $e) { | |
| throw new ExamControllerException(ExamControllerException::ERROR_FETCHING_EXAM_REGISTRATION,"Cannot fetch Exam Registration details! Please try again."); | |
| } | |
| return $studentDetails; | |
| } | |
| /** | |
| * Get current Exam Registration Details For Payment module | |
| * @param $examRegistrationId | |
| * @return examRegistrationDetails | |
| */ | |
| public function getCurrentExamRegistrationDetailsForPaymentModule( $examRegistrationId){ | |
| $examRegistrationId = $this->realEscapeString($examRegistrationId); | |
| $query = "SELECT | |
| eer.type as examRegType, | |
| IF((eer.properties->>'$.isSpecialExam') = true, 1, 0) AS isSpecialExam | |
| FROM | |
| ec_exam_registration eer | |
| WHERE | |
| eer.trashed IS NULL AND eer.id = '$examRegistrationId'"; | |
| try { | |
| $examRegistrationDetails = $this->executeQueryForObject($query); | |
| } catch (\Exception $e) { | |
| throw new ExamControllerException(ExamControllerException::ERROR_FETCHING_EXAM_REGISTRATION,"Cannot fetch Exam Registration Start year details! Please try again."); | |
| } | |
| return $examRegistrationDetails; | |
| } | |
| /** | |
| * Search Exam Revaluation | |
| * @param $request | |
| * @return $examRegistrations | |
| */ | |
| public function searchDetailedExamRevaluationDetails( $request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $whereQuery = ""; | |
| $limitQuery = ""; | |
| $joinQueary = ""; | |
| $orderQuery = ""; | |
| if(!empty($request->examRegistrationId)){ | |
| $whereQuery .= " AND eer.id='$request->examRegistrationId' "; | |
| } | |
| if(!empty($request->name)){ | |
| $whereQuery .= " AND eer.name LIKE '%$request->name%'"; | |
| } | |
| if(!empty($request->courseTypeId)){ | |
| $whereQuery .= " AND p.course_type_id = '$request->courseTypeId'"; | |
| } | |
| if(!empty($request->groupId)) { | |
| $groupIdString = is_array($request->groupId) ? "'" . implode("','",$request->groupId) . "'" : "'".$request->groupId."'"; | |
| $whereQuery .= " AND g.id IN ( $groupIdString )"; | |
| } | |
| if(!empty($request->academicPaperSubjectId)) { | |
| $academicPaperSubjectIdString = is_array($request->academicPaperSubjectId) ? "'" . implode("','",$request->academicPaperSubjectId) . "'" : "'".$request->academicPaperSubjectId."'"; | |
| $whereQuery .= " AND aps.id IN ( $academicPaperSubjectIdString )"; | |
| } | |
| if(!empty($request->examRegistrationBatchId)) { | |
| $examRegistrationBatchIdString = is_array($request->examRegistrationBatchId) ? "'" . implode("','",$request->examRegistrationBatchId) . "'" : "'".$request->examRegistrationBatchId."'"; | |
| $whereQuery .= " AND eerb.id IN ( $examRegistrationBatchIdString )"; | |
| } | |
| if(!empty($request->academicTermId)){ | |
| $whereQuery .= " AND CAST(eerb.properties ->> '$.academicTermId'AS CHAR) = '$request->academicTermId'"; | |
| } | |
| if(!empty($request->batchStartYear)){ | |
| $whereQuery .= " AND g.properties ->> '$.startYear' = '$request->batchStartYear'"; | |
| } | |
| if(!empty($request->type)) { | |
| foreach($request->type as $type){ | |
| $whereQuery .= " AND eer.type LIKE '%$type%'"; | |
| } | |
| } | |
| $groupByQuerry = " GROUP BY eer.id,p.id,g.id,aps.id"; | |
| $orderQuery = " ORDER BY CAST(cap.properties ->> '$.order' AS UNSIGNED) ASC"; | |
| $query = "SELECT | |
| DISTINCT(eer.id), | |
| eer.identifying_context, | |
| eer.name, | |
| eer.type, | |
| eer.properties, | |
| eerp.id AS parentExamRegistrationId, | |
| eerp.properties ->> '$.examMonth' AS examMonth, | |
| MONTHNAME(STR_TO_DATE(eerp.properties ->> '$.examMonth', '%m')) AS examMonthName, | |
| eerp.properties ->> '$.examYear' AS examYear, | |
| eer.fees_properties, | |
| eer.trashed, | |
| eer.created_by, | |
| eer.created_date, | |
| eer.updated_by, | |
| eer.updated_date, | |
| eerb.groups_id, | |
| eerb.properties AS examBatchProperties, | |
| g.name AS groupName, | |
| g.type AS groupType, | |
| act.id as academicTermId, | |
| act.name as academicTermName, | |
| act.properties ->>'$.year' AS academicTermYear, | |
| act.properties ->>'$.orderNo' AS academicOrderNo, | |
| dept.deptID, | |
| deg.name as degreeName, | |
| deg.description as degreeDescription, | |
| group_concat(str.name SEPARATOR ' and ') as streamName, | |
| p.name as programName, | |
| dept.deptName, | |
| dept.departmentDesc as departmentDesc, | |
| ct.courseTypeID, | |
| ct.course_Type as courseTypeName, | |
| g.properties ->> '$.startYear' AS batchStartYear, | |
| g.identifying_context AS groupIdentifyingContext, | |
| g.properties AS groupProperties, | |
| eers.cm_academic_paper_subjects_id as academicPaperSubjectId, | |
| eers.am_assessment_id as assessmentId, | |
| s.code as subjectCode, | |
| aps.properties ->> '$.syllabusName' AS syllabusName, | |
| s.name as subjectName, | |
| aps.properties ->>'$.isInternal' AS isInternal, | |
| aps.properties ->>'$.isExternal' AS isExternal, | |
| aps.properties ->>'$.externalMaxMark' AS externalMaxMark, | |
| aps.properties ->>'$.internalMaxMark' AS internalMaxMark, | |
| aps.properties ->>'$.credit' AS subjectCredit, | |
| aps.properties ->>'$.subjectTypeId' AS subjectTypeId, | |
| aps.properties ->>'$.courseCode' AS courseCode, | |
| aps.properties ->>'$.externalMaxMark' + aps.properties ->>'$.internalMaxMark' AS totalSubjectMark, | |
| cap.properties ->> '$.order' as subjectPriority | |
| FROM | |
| ec_exam_registration eer | |
| INNER JOIN ec_exam_registration eerp ON | |
| eer.properties->>'$.parentExamRegistrationId' = eerp.id | |
| INNER JOIN ec_exam_registration_batch eerb | |
| ON eerb.ec_exam_registration_id = eer.id | |
| INNER JOIN ec_exam_registration_batch eerbp | |
| ON eerbp.ec_exam_registration_id = eerp.id AND | |
| eerb.groups_id = eerbp.groups_id | |
| INNER JOIN ec_exam_registration_subject eers ON | |
| eers.ec_exam_registration_batch_id = eerbp.id | |
| INNER JOIN cm_academic_paper_subjects aps ON | |
| eers.cm_academic_paper_subjects_id = aps.id | |
| INNER JOIN cm_academic_paper cap ON | |
| cap.id = aps.cm_academic_paper_id | |
| INNER JOIN v4_ams_subject s ON | |
| aps.ams_subject_id = s.id | |
| INNER JOIN `groups` g | |
| ON g.id = eerb.groups_id | |
| INNER JOIN program p ON | |
| p.id = CAST(g.properties ->> '$.programId' AS CHAR) | |
| INNER JOIN degree deg ON | |
| deg.id = p.degree_id | |
| INNER JOIN `academic_term` act ON | |
| act.id = CAST(eerb.properties ->> '$.academicTermId'AS CHAR) | |
| INNER JOIN `department` dept ON | |
| dept.deptID = CAST(g.properties ->> '$.departmentId' AS CHAR) | |
| INNER JOIN `course_type` ct ON | |
| ct.courseTypeID = p.course_type_id | |
| LEFT JOIN stream str ON | |
| JSON_SEARCH( p.stream_id, 'one', str.id) IS NOT NULL | |
| WHERE | |
| eer.trashed IS NULL"; | |
| try { | |
| $examRegistrations = $this->executeQueryForList($query.$whereQuery.$groupByQuerry.$orderQuery.$limitQuery, $this->mapper[ExamRegistrationServiceMapper::SEARCH_DETAILED_EXAM_REGISTRATION_DETAILS]); | |
| } catch (\Exception $e) { | |
| throw new ExamControllerException(ExamControllerException::ERROR_FETCHING_EXAM_REGISTRATION,"Cannot fetch Exam Registration details! Please try again."); | |
| } | |
| return $examRegistrations; | |
| } | |
| /** | |
| * Get course type by exam registeration id and student id | |
| * @param $studentId | |
| * @param $examRegistrationId | |
| * @return courseTypeDetails | |
| */ | |
| public function getStudentCourseTypeByExamRegistration($studentId,$examRegistrationId){ | |
| $studentId = $this->realEscapeString($studentId); | |
| $examRegistrationId = $this->realEscapeString($examRegistrationId); | |
| if(!$studentId || !$examRegistrationId){ | |
| throw new ExamControllerException(ExamControllerException::EMPTY_PARAMETERS," Exam Registration id and studentId are mandatory!"); | |
| } | |
| $query = "SELECT | |
| p.course_type_id as courseTypeId,ct.course_Type as courseType | |
| FROM | |
| ec_exam_registration_batch eerb | |
| INNER JOIN `groups` g ON | |
| g.id = eerb.groups_id | |
| INNER JOIN program p ON | |
| p.id = g.properties->>'$.programId' | |
| INNER JOIN student_program_account spa ON | |
| spa.current_program_id = p.id | |
| INNER JOIN student_program_batch_log spbl ON | |
| spbl.batch_group_id = g.id AND | |
| spbl.program_student_id = spa.id AND spbl.properties->>'$.academicStatus' IN ('ACTIVE','COMPLETED') | |
| INNER JOIN course_type ct ON ct.courseTypeID = p.course_type_id | |
| WHERE | |
| eerb.ec_exam_registration_id='$examRegistrationId' AND spa.student_id ='$studentId' GROUP BY ct.courseTypeID"; | |
| try { | |
| return $this->executeQueryForObject($query); | |
| } catch (\Exception $e) { | |
| throw new ExamControllerException(ExamControllerException::ERROR_FETCHING_COURSE_TYPE,"Cannot fetch course type details! Please try again."); | |
| } | |
| } | |
| /** | |
| * Get custom payment gateway module name by exam regiteration id and studentid | |
| * @param $studentId | |
| * @param $examRegistrationId | |
| * @return paymentGatewayModuleDetails | |
| */ | |
| public function getPaymentGateWayModulesForCourseTypes($studentId,$examRegistrationId){ | |
| $studentId = $this->realEscapeString($studentId); | |
| $examRegistrationId = $this->realEscapeString($examRegistrationId); | |
| $paymentGatewayModuleDetails = null; | |
| try { | |
| $customModules = json_decode(CommonService::getInstance()->getSettings(SettingsConstants::EXAM_CONTROLLER,SettingsConstants::PAYMENT_GATEWAY_MODULE_FOR_COURSETYPES)); | |
| if($customModules->enableFeature){ | |
| if($studentId && $examRegistrationId){ | |
| $courseType = $this->getStudentCourseTypeByExamRegistration($studentId,$examRegistrationId)->courseType; | |
| $paymentGatewayModuleDetails = ($courseType && $customModules->$courseType) ? $customModules->$courseType : null; | |
| } | |
| } | |
| return $paymentGatewayModuleDetails; | |
| } catch (\Exception $e) { | |
| throw new ExamControllerException(ExamControllerException::ERROR_FETCHING_CUSTOM_PAYMENT_MODULE,"Cannot fetch payment module details! Please try again."); | |
| } | |
| } | |
| /** | |
| * get Regular Exam Registration Id by Supply Exam Registration Id | |
| * @param $request | |
| * @return regularExamRegistration->id | |
| */ | |
| public function getRegularExamRegistrationIdBySupplyExamRegistrationId( $request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $whereQuery = ""; | |
| if(!empty($request->examRegistrationId)){ | |
| $whereQuery .= " AND eerb.ec_exam_registration_id = '$request->examRegistrationId' "; | |
| } | |
| if(!empty($request->groupId)){ | |
| $whereQuery .= " AND eerb.groups_id = '$request->groupId' "; | |
| } | |
| $query = "SELECT DISTINCT | |
| eer.id | |
| FROM | |
| ec_exam_registration_batch eerb | |
| INNER JOIN ec_exam_registration_batch eerbReg ON | |
| eerbReg.groups_id = eerb.groups_id AND eerbReg.academicTermId = eerb.academicTermId | |
| INNER JOIN ec_exam_registration eer ON | |
| eer.id = eerbReg.ec_exam_registration_id AND | |
| eer.`type` = 'REGULAR' | |
| WHERE | |
| eer.trashed IS NULL AND ( (eer.properties ->> '$.isHonorCourse' IS NULL OR eer.properties ->> '$.isHonorCourse' != '1') AND (eer.properties ->> '$.isMinorCourse' IS NULL OR eer.properties ->> '$.isMinorCourse' != '1'))"; | |
| try { | |
| $regularExamRegistration = $this->executeQueryForObject($query.$whereQuery); | |
| } catch (\Exception $e) { | |
| throw new ExamControllerException(ExamControllerException::ERROR_FETCHING_EXAM_REGISTRATION,"Cannot fetch Exam Registration details! Please try again."); | |
| } | |
| return $regularExamRegistration->id; | |
| } | |
| /** | |
| * Update False Number Queue Update Status | |
| * @param String $id | |
| * @param Boolean $publish | |
| * @return NULL | |
| */ | |
| public function updateFalseNumberQueueUpdateStatus($id, $status,$message = "No message"){ | |
| $id = $this->realEscapeString($id); | |
| $status = $this->realEscapeString($status); | |
| $message = $this->realEscapeString($message); | |
| $updatedBy = $GLOBALS['userId']; | |
| if(empty($id)) | |
| throw new ExamControllerException(ExamControllerException::EMPTY_PARAMETERS_EXAM_REGISTRATION,"Exam Registration is invalid! Please enter a valid Exam Registration"); | |
| $query = "UPDATE | |
| ec_exam_registration | |
| SET | |
| properties = JSON_SET(properties, '$.falseNoQueueStatus', '$status'), | |
| properties = JSON_SET(properties, '$.falseNoQueueMessage', '$message'), | |
| updated_by = '$updatedBy' | |
| WHERE | |
| id = '$id'"; | |
| try { | |
| $this->executeQuery($query); | |
| } catch (\Exception $e) { | |
| throw new ExamControllerException(ExamControllerException::EMPTY_PARAMETERS_EXAM_REGISTRATION,"Error update status! Please try again"); | |
| } | |
| } | |
| /** | |
| * Search All Programs by Exam Registration | |
| * @param $request | |
| * @return examRegistration | |
| */ | |
| public function searchAllProgramsByExamRegistration( $request){ | |
| $request = $this->realEscapeObject($request); | |
| $whereQuery = ""; | |
| $limitQuery = ""; | |
| $orderQuery = ""; | |
| if(!empty($request->examRegistrationId)){ | |
| $examRegistrationIdString = is_array($request->examRegistrationId) ? "'" . implode("','",$request->examRegistrationId) . "'" : "'".$request->examRegistrationId."'"; | |
| $whereQuery .= " AND eer.id IN ( $examRegistrationIdString ) "; | |
| } | |
| if(!empty($request->courseTypeId)){ | |
| $whereQuery .= " AND p.course_type_id = '$request->courseTypeId'"; | |
| } | |
| if(!empty($request->groupId)) { | |
| $groupIdString = is_array($request->groupId) ? "'" . implode("','",$request->groupId) . "'" : "'".$request->groupId."'"; | |
| $whereQuery .= " AND g.id IN ( $groupIdString )"; | |
| } | |
| $orderQuery = " ORDER BY p.name ASC"; | |
| $query = "SELECT | |
| DISTINCT(p.id) as programId, | |
| p.id as id, | |
| p.name as name, | |
| p.name as text | |
| FROM | |
| ec_exam_registration eer | |
| INNER JOIN ec_exam_registration_batch eerb | |
| ON eerb.ec_exam_registration_id = eer.id | |
| INNER JOIN `groups` g | |
| ON g.id = eerb.groups_id | |
| INNER JOIN program p ON | |
| p.id = CAST(g.properties ->> '$.programId' AS CHAR) | |
| INNER JOIN `course_type` ct ON | |
| ct.courseTypeID = p.course_type_id | |
| WHERE eer.trashed IS NULL "; | |
| try { | |
| $programs = $this->executeQueryForList($query.$whereQuery.$orderQuery); | |
| } catch (\Exception $e) { | |
| throw new ExamControllerException(ExamControllerException::ERROR_FETCHING_PROGRAMS,"Cannot fetch programs! Please try again."); | |
| } | |
| return $programs; | |
| } | |
| } |