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; | |
} | |
} |