Code Coverage |
||||||||||
Classes and Traits |
Functions and Methods |
Lines |
||||||||
Total | |
0.00% |
0 / 1 |
|
0.00% |
0 / 20 |
CRAP | |
0.00% |
0 / 942 |
RankListReportService | |
0.00% |
0 / 1 |
|
0.00% |
0 / 20 |
18090.00 | |
0.00% |
0 / 942 |
__construct | |
0.00% |
0 / 1 |
2.00 | |
0.00% |
0 / 3 |
|||
getAllRankListReports | |
0.00% |
0 / 1 |
72.00 | |
0.00% |
0 / 41 |
|||
saveRankListReport | |
0.00% |
0 / 1 |
56.00 | |
0.00% |
0 / 35 |
|||
insertRankListReport | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 11 |
|||
updateRankListReport | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 16 |
|||
getRankListSubjectMappingDetails | |
0.00% |
0 / 1 |
30.00 | |
0.00% |
0 / 16 |
|||
getSubCurriculumSubjectMapingDetails | |
0.00% |
0 / 1 |
110.00 | |
0.00% |
0 / 74 |
|||
saveRankListSubjectMappingDetails | |
0.00% |
0 / 1 |
72.00 | |
0.00% |
0 / 34 |
|||
insertRankListSyllabusSubjectMapping | |
0.00% |
0 / 1 |
30.00 | |
0.00% |
0 / 42 |
|||
deleteRankListSyllabusSubjectMapping | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 15 |
|||
deleteRankListReport | |
0.00% |
0 / 1 |
30.00 | |
0.00% |
0 / 35 |
|||
getSubjectToppersList | |
0.00% |
0 / 1 |
156.00 | |
0.00% |
0 / 108 |
|||
getTopRankReport | |
0.00% |
0 / 1 |
182.00 | |
0.00% |
0 / 103 |
|||
getConsolidatedStudentMarkDetailsForRankListReport | |
0.00% |
0 / 1 |
240.00 | |
0.00% |
0 / 119 |
|||
getAllRankListMappingDetails | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 23 |
|||
getMeritCertificate | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 56 |
|||
getSubCurriculumByDepartmentAndRankReport | |
0.00% |
0 / 1 |
72.00 | |
0.00% |
0 / 36 |
|||
saveRankListSyllabusSubjectRelation | |
0.00% |
0 / 1 |
30.00 | |
0.00% |
0 / 45 |
|||
getSubjectToppersStudentList | |
0.00% |
0 / 1 |
210.00 | |
0.00% |
0 / 116 |
|||
deleteRankListSubjectGroupRelation | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 14 |
<?php | |
namespace com\linways\ec\core\service; | |
use com\linways\base\util\MakeSingletonTrait; | |
use com\linways\ec\core\exception\ExamControllerException; | |
use com\linways\ec\core\mapper\RankListReportServiceMapper; | |
use com\linways\core\ams\professional\logging\AMSLogger; | |
use com\linways\ec\core\constant\StatusConstants; | |
use com\linways\ec\core\logging\Events; | |
use com\linways\ec\core\logging\entities\Staff; | |
use com\linways\base\util\TwigRenderer; | |
use com\linways\core\ams\autonomous\service\SemesterService; | |
use com\linways\core\ams\professional\util\PdfUtil; | |
use com\linways\ec\core\service\CommonExamService; | |
use com\linways\core\ams\professional\util\CommonUtil; | |
use com\linways\core\ams\professional\request\examcontroller\RankReportRequest; | |
use com\linways\core\ams\professional\service\examcontroller\RankReportService; | |
class RankListReportService extends BaseService{ | |
use MakeSingletonTrait; | |
private function __construct() { | |
$this->logger = AMSLogger::getLogger('exam-controller-log'); | |
$this->mapper = RankListReportServiceMapper::getInstance()->getMapper(); | |
} | |
/** | |
* get All Rank List Reports | |
* @param $searchRequest | |
* @return $response | |
* @author Krishnajith | |
*/ | |
public function getAllRankListReports($searchRequest) { | |
$searchRequest = $this->realEscapeObject($searchRequest); | |
try{ | |
$whereQuery = null; | |
$orderBy = " ORDER BY rr.year DESC "; | |
$whereQuery = ""; | |
if(!empty($searchRequest->id)) { | |
$idString = is_array($searchRequest->id) ? "'" . implode("','",$searchRequest->id) . "'" : "'".$searchRequest->id."'"; | |
$whereQuery .= " AND rr.id IN ( $idString )"; | |
} | |
if(!empty($searchRequest->courseTypeId)) { | |
$courseTypeIdString = is_array($searchRequest->courseTypeId) ? "'" . implode("','",$searchRequest->courseTypeId) . "'" : "'".$searchRequest->courseTypeId."'"; | |
$whereQuery .= " AND rr.course_type_id IN ( $courseTypeIdString )"; | |
} | |
if(!empty($searchRequest->startYear)) { | |
$startYearString = is_array($searchRequest->startYear) ? "'" . implode("','",$searchRequest->startYear) . "'" : "'".$searchRequest->startYear."'"; | |
$whereQuery .= " AND rr.year IN ( $startYearString )"; | |
} | |
$query = "SELECT | |
DISTINCT | |
rr.id as id, | |
rr.name as name, | |
rr.course_type_id as courseTypeId, | |
ct.typeName as courseTypeName, | |
rr.year as batchStartYear | |
FROM | |
`rank_report` rr | |
INNER JOIN course_type ct ON | |
ct.courseTypeID = rr.course_type_id | |
WHERE 1=1 "; | |
$rankListReports = $this->executeQueryForList($query.$whereQuery.$orderBy, $this->mapper[RankListReportServiceMapper::GET_ALL_REPORTS]); | |
AMSLogger::log_info($this->logger,Events::GET_ALL_RANK_LIST_REPORTS,[ | |
"staff" => new Staff(["id" => $GLOBALS['userId']]), | |
"request" => $searchRequest, | |
"status" => StatusConstants::SUCCESS | |
]); | |
return $rankListReports; | |
} | |
catch (\Exception $e){ | |
throw new ExamControllerException($e->getCode(),$e->getMessage()); | |
} | |
} | |
/** | |
* Save Rank List Report | |
* @param $rankListReport | |
* @return $id | |
*/ | |
public function saveRankListReport ($rankListReport) | |
{ | |
$rankListReport = $this->realEscapeObject($rankListReport); | |
$rankListReport->createdBy = $GLOBALS['userId']; | |
$rankListReport->updatedBy = $GLOBALS['userId']; | |
$staffId = $GLOBALS['userId']; | |
try{ | |
if(!empty($rankListReport->id)){ | |
$rankListReport->id = $this->updateRankListReport($rankListReport); | |
} | |
else{ | |
$rankListReport->id = $this->insertRankListReport($rankListReport); | |
} | |
AMSLogger::log_info($this->logger,Events::SAVE_RANK_LIST_REPORT,[ | |
"staff" => new Staff(["id" => $staffId]), | |
"request" => $rankListReport, | |
"status" => StatusConstants::SUCCESS | |
]); | |
}catch(\Exception $e) { | |
AMSLogger::log_error($this->logger,Events::SAVE_RANK_LIST_REPORT, [ | |
"staff" => new Staff(["id" => $staffId]), | |
"request" => $rankListReport, | |
"errorCode" => $e->getCode(), | |
"errorMessage" => $e->getMessage(), | |
"status" => StatusConstants::FAILED | |
]); | |
if($e->getCode() !== ExamControllerException::INVALID_PARAMETERS && $e->getCode() !== ExamControllerException::INVALID_PARAMETERS && $e->getCode() !== "DUPLICATE_ENTRY") { | |
throw new ExamControllerException($e->getCode(),"Failed to save rank list report ! Please try again"); | |
} else if ($e->getCode() === ExamControllerException::DUPLICATE_ENTRY) { | |
throw new ExamControllerException (ExamControllerException::DUPLICATE_ENTRY,"Already created by using course type and batch year !"); | |
} else { | |
throw new ExamControllerException ($e->getCode(),$e->getMessage()); | |
} | |
} | |
return $rankListReport->id ; | |
} | |
/** | |
* Insert rank List Report | |
* @param $rankListReport | |
* @return $id | |
*/ | |
private function insertRankListReport($rankListReport){ | |
$query = "INSERT INTO rank_report | |
(`name`,`course_type_id`,`year`,`created_by`) | |
VALUES | |
('$rankListReport->name','$rankListReport->courseTypeId','$rankListReport->startYear','$rankListReport->createdBy')"; | |
try { | |
$rankListReport->id = $this->executeQuery($query)->id; | |
return $rankListReport->id; | |
} catch (\Exception $e) { | |
throw new ExamControllerException($e->getCode(),$e->getMessage()); | |
} | |
} | |
/** | |
* Update rank List Report | |
* @param $rankListReport | |
* @return String $rankListReport->id | |
*/ | |
private function updateRankListReport($rankListReport){ | |
$query = "UPDATE | |
rank_report | |
SET | |
`name` = '$rankListReport->name', | |
course_type_id = '$rankListReport->courseTypeId', | |
`year` = '$rankListReport->startYear', | |
updated_by = '$rankListReport->updatedBy' | |
WHERE | |
id = '$rankListReport->id'"; | |
try { | |
$this->executeQuery($query); | |
return $rankListReport->id; | |
} catch (\Exception $e) { | |
throw new ExamControllerException($e->getCode(),$e->getMessage()); | |
} | |
} | |
/** | |
* get Rank List Subject Mapping Details | |
* @param $searchRequest | |
* @return $response | |
*/ | |
public function getRankListSubjectMappingDetails($searchRequest) { | |
$searchRequest = $this->realEscapeObject($searchRequest); | |
$programResult = new \stdClass(); | |
try{ | |
$subCurriculumDetails = reset($this->getSubCurriculumSubjectMapingDetails($searchRequest)); | |
foreach($subCurriculumDetails->academicTerms as $academicTerm){ | |
foreach($academicTerm->subjects as $subject){ | |
$subject->isSelected = !empty($subject->subjectMappingId) ? 1 : 0; | |
} | |
} | |
$programResult->subCurriculum = $subCurriculumDetails; | |
} | |
catch (\Exception $e){ | |
throw new ExamControllerException($e->getCode(),$e->getMessage()); | |
} | |
return $programResult; | |
} | |
/** | |
* Fetch Sub curriculum Subject Mapping details | |
* @param Object $studentId | |
* @return Array $subCurriculums | |
* @author Krishnajith V | |
*/ | |
public function getSubCurriculumSubjectMapingDetails($searchRequest){ | |
$searchRequest = $this->realEscapeObject($searchRequest); | |
try{ | |
$joinQuery = ""; | |
$whereQuery = ""; | |
if(!empty($searchRequest->groupId)) { | |
$groupIdString = is_array($searchRequest->groupId) ? "'" . implode("','",$searchRequest->groupId) . "'" : "'".$searchRequest->groupId."'"; | |
$whereQuery .= " AND bg.id IN ( $groupIdString )"; | |
} | |
if(!empty($searchRequest->curriculumId)) { | |
$curriculumIdString = is_array($searchRequest->curriculumId) ? "'" . implode("','",$searchRequest->curriculumId) . "'" : "'".$searchRequest->curriculumId."'"; | |
$whereQuery .= " AND cc.id IN ( $curriculumIdString )"; | |
} | |
if(!empty($searchRequest->syllabusId)) { | |
$syllabusIdStrng = is_array($searchRequest->syllabusId) ? "'" . implode("','",$searchRequest->syllabusId) . "'" : "'".$searchRequest->syllabusId."'"; | |
$whereQuery .= " AND cs.id IN ( $syllabusIdStrng )"; | |
} | |
if(!empty($searchRequest->rankListReportId)) { | |
$rankListReportIdStrng = is_array($searchRequest->rankListReportId) ? "'" . implode("','",$searchRequest->rankListReportId) . "'" : "'".$searchRequest->rankListReportId."'"; | |
$joinQuery .= " INNER JOIN rank_report rr ON rr.id IN ( $rankListReportIdStrng ) "; | |
// $whereQuery .= " AND rr.id IN ( $rankListReportIdStrng )"; | |
} | |
else{ | |
$joinQuery .= " INNER JOIN rank_report rr "; | |
} | |
$query = "SELECT DISTINCT | |
cc.id as curriculumId, | |
cc.name as curriculumName, | |
rr.id as rankListReportId, | |
rr.name as rankListReportName, | |
cc.description as curriculumDesc, | |
cc.program_id, | |
cs.id as syllabusId, | |
cs.name as syllabusName, | |
cs.description as syllabusDesc, | |
cs.`type` as syllabusType, | |
cs.department_id as departmentId, | |
d.deptName, | |
cs.properties as syllabusProperties, | |
cs.trashed as syllabusTrashed, | |
csats.academic_term_id as termId, | |
at2.name as termName, | |
at2.properties AS academic_term_properties, | |
at2.type AS academic_term_type, | |
cap.id as academicPaperId, | |
cap.name as academicPaperName, | |
caps.id as academicPaperSubjectId, | |
vas.id as subjectId, | |
vas.code as subjectCode, | |
vas.name as subjectName, | |
erlrssm.id as subjectMappingId, | |
erlrssm.properties->>'$.groupNo' as groupNo | |
FROM cm_curriculum cc | |
INNER JOIN program p ON p.id = cc.program_id | |
INNER JOIN `groups` bg ON bg.cm_curriculum_id = cc.id | |
INNER JOIN cm_curriculum_syllabus_relation ccsr on ccsr.cm_curriculum_id = cc.id | |
INNER JOIN cm_syllabus cs on cs.id = ccsr.cm_syllabus_id | |
INNER JOIN department d on d.deptID = cs.department_id | |
INNER JOIN cm_syllabus_academic_term_settings csats on csats.cm_syllabus_id = ccsr.cm_syllabus_id | |
INNER JOIN academic_term at2 on at2.id = csats.academic_term_id | |
INNER JOIN cm_academic_paper cap on cap.cm_syllabus_academic_term_settings_id = csats.id | |
INNER JOIN cm_academic_paper_subjects caps ON caps.cm_academic_paper_id = cap.id | |
INNER JOIN v4_ams_subject vas on vas.id = caps.ams_subject_id | |
$joinQuery | |
LEFT JOIN ec_rank_list_report_syllabus_subject_mapping erlrssm ON | |
erlrssm.cm_syllabus_id = cs.id AND | |
erlrssm.cm_academic_paper_subjects_id = caps.id AND | |
erlrssm.rank_report_id = rr.id | |
WHERE 1 =1 "; | |
$subCurriculums = $this->executeQueryForList($query.$whereQuery.$orderBy,$this->mapper[RankListReportServiceMapper::SUB_CURRICULUM_SUBJECT_DETAILS]); | |
} | |
catch (\Exception $e){ | |
throw new ExamControllerException($e->getCode(),$e->getMessage()); | |
} | |
return $subCurriculums; | |
} | |
/** | |
* Save Rank List Subject Syllabus Mapping | |
* @param $rankListReport | |
* @return $id | |
*/ | |
public function saveRankListSubjectMappingDetails ($subCurriculum){ | |
$subCurriculum = $this->realEscapeObject($subCurriculum); | |
try { | |
$ranklistSubjectMappingArray = []; | |
$deletedRanklistSubjectMappingIds = []; | |
foreach($subCurriculum->academicTerms as $academicTerm){ | |
foreach($academicTerm->subjects as $subject){ | |
if($subject->isSelected == '1'){ | |
$ranklistSubjectMapping = new \stdClass(); | |
$ranklistSubjectMapping->syllabusId = $subCurriculum->id; | |
$ranklistSubjectMapping->rankReportId = $subCurriculum->rankListReportId; | |
$ranklistSubjectMapping->academicPaperSubjectId = $subject->academicPaperSubjectId; | |
$ranklistSubjectMappingProperties = new \stdClass(); | |
$ranklistSubjectMappingProperties->groupNo = $subject->groupNo; | |
$ranklistSubjectMapping->properties = $ranklistSubjectMappingProperties; | |
$ranklistSubjectMapping->createdBy = $GLOBALS['userId']; | |
$ranklistSubjectMappingArray[] = $ranklistSubjectMapping; | |
} | |
else{ | |
if($subject->subjectMappingId){ | |
$deletedRanklistSubjectMappingIds[] = $subject->subjectMappingId; | |
} | |
} | |
} | |
} | |
if(!empty($ranklistSubjectMappingArray)){ | |
$this->insertRankListSyllabusSubjectMapping($ranklistSubjectMappingArray); | |
} | |
if(!empty($deletedRanklistSubjectMappingIds)){ | |
$this->deleteRankListSyllabusSubjectMapping($deletedRanklistSubjectMappingIds); | |
} | |
}catch (\Exception $e) { | |
throw new ExamControllerException($e->getCode(),$e->getMessage()); | |
} | |
} | |
/** | |
* Insert rank List Syllabus Subject Mapping | |
* @param $ranklistSubjectMappingArray | |
* @return Boolean true | |
* @throws ExamControllerException | |
* @author Krishnajith V | |
*/ | |
public function insertRankListSyllabusSubjectMapping ($ranklistSubjectMappingArray) { | |
$ranklistSubjectMappingArray = $this->realEscapeArray($ranklistSubjectMappingArray); | |
$values = []; | |
if ( empty ( $ranklistSubjectMappingArray ) ) { | |
throw new ExamControllerException (ExamControllerException::EMPTY_PARAMETERS, "Empty Parameters"); | |
} | |
foreach ( $ranklistSubjectMappingArray as $ranklistSubjectMapping ) { | |
$ranklistSubjectMapping->properties = $ranklistSubjectMapping->properties ? json_encode($ranklistSubjectMapping->properties) : "{}"; | |
$values[] = "( | |
'$ranklistSubjectMapping->rankReportId', | |
'$ranklistSubjectMapping->syllabusId', | |
'$ranklistSubjectMapping->academicPaperSubjectId', | |
'$ranklistSubjectMapping->properties' | |
)"; | |
$staffId = $ranklistSubjectMapping->createdBy; | |
} | |
try { | |
$sql = "INSERT INTO `ec_rank_list_report_syllabus_subject_mapping` ( | |
`rank_report_id`, | |
`cm_syllabus_id`, | |
`cm_academic_paper_subjects_id`, | |
`properties` | |
) VALUES " . implode(', ', $values). " ON DUPLICATE KEY UPDATE | |
`properties` = VALUES(properties), | |
`updated_by` = VALUES(created_by)"; | |
$this->executeQuery($sql); | |
AMSLogger::log_info($this->logger,Events::SAVE_RANK_LIST_REPORT_MAPPING, [ | |
"staff" => new Staff(["id" => $staffId]), | |
"request" => $ranklistSubjectMappingArray, | |
"status" => StatusConstants::SUCCESS | |
]); | |
} catch (\Exception $e) { | |
AMSLogger::log_error($this->logger,Events::SAVE_RANK_LIST_REPORT_MAPPING, [ | |
"staff" => new Staff(["id" => $staffId]), | |
"request" => $ranklistSubjectMappingArray, | |
"errorCode" => $e->getCode(), | |
"errorMessage" => $e->getMessage(), | |
"status" => StatusConstants::FAILED | |
]); | |
throw new ExamControllerException ($e->getCode(), $e->getMessage()); | |
} | |
return true; | |
} | |
/** | |
* delete RankList Syllabus Subject Mapping | |
* @param Array $ids | |
* @return NULL | |
*/ | |
public function deleteRankListSyllabusSubjectMapping($ids){ | |
$ids = $this->realEscapeArray($ids); | |
if(empty($ids)) { | |
throw new ExamControllerException(ExamControllerException::EMPTY_PARAMETERS,"Error Delete Mapping Details! Please try again"); | |
} | |
$id = implode(",",$ids); | |
$query = "DELETE FROM | |
ec_rank_list_report_syllabus_subject_mapping | |
WHERE | |
id IN ($id) "; | |
try { | |
$this->executeQuery($query); | |
} catch (\Exception $e) { | |
throw new ExamControllerException(ExamControllerException::EMPTY_PARAMETERS,"Error Delete Mapping Details! Please try again"); | |
} | |
} | |
/** | |
* delete RankList Syllabus Subject Mapping | |
* @param Array $ids | |
* @return NULL | |
*/ | |
public function deleteRankListReport($id){ | |
$id = $this->realEscapeString($id); | |
$staffId = $GLOBALS['userId']; | |
$requestForRankListReport = new \stdClass(); | |
$requestForRankListReport->id = $id; | |
$rankListReport = reset($this->getAllRankListReports($requestForRankListReport)); | |
if(empty($id) || empty($rankListReport)) { | |
throw new ExamControllerException(ExamControllerException::EMPTY_PARAMETERS,"Error Delete Rank List Report"); | |
} | |
$query = "DELETE FROM | |
rank_report | |
WHERE | |
id = '$id' "; | |
try { | |
$this->executeQuery($query); | |
AMSLogger::log_info($this->logger,Events::DELETE_RANK_LIST_REPORT, [ | |
"staff" => new Staff(["id" => $staffId]), | |
"request" => $rankListReport, | |
"status" => StatusConstants::SUCCESS | |
]); | |
} catch (\Exception $e) { | |
if($e->getCode() =='CANNOT_DELETE_OR_UPDATE_ROW_FOREIGN_KEY_FAILED'){ | |
throw new ExamControllerException(ExamControllerException::EMPTY_PARAMETERS,"Error delete! subjects mapped in this rank report"); | |
} | |
else{ | |
throw new ExamControllerException(ExamControllerException::EMPTY_PARAMETERS,"Error delete rank report! Please try again"); | |
} | |
AMSLogger::log_error($this->logger,Events::DELETE_RANK_LIST_REPORT, [ | |
"staff" => new Staff(["id" => $staffId]), | |
"request" => $rankListReport, | |
"errorCode" => $e->getCode(), | |
"errorMessage" => $e->getMessage(), | |
"status" => StatusConstants::FAILED | |
]); | |
} | |
} | |
/** | |
* get Subject Toppers List | |
* @param $searchRequest | |
* @return $programResult | |
*/ | |
public function getSubjectToppersList($searchRequest) { | |
$searchRequest = $this->realEscapeObject($searchRequest); | |
try{ | |
$programResult = new \stdClass; | |
$additionalDetails = new \stdClass; | |
$additionalDetails->collegeData = CommonExamService::getInstance()->getCollegeDetails(); | |
$requestForExamRegistration = new \stdClass; | |
$requestForExamRegistration->examRegistrationId = $searchRequest->examRegistrationId; | |
$requestForExamRegistration->limitCondition = 1; | |
$examRegistration = reset(ExamRegistrationService::getInstance()->searchDetailedExamRegistrationDetails($requestForExamRegistration)); | |
$requestForExamRegistration->academicTermId = $searchRequest->academicTermId; | |
$semester = reset(CommonExamService::getInstance()->getAcademicTermsDetails($requestForExamRegistration)); | |
$batchStartYear = $examRegistration->properties->examYear - floor ($semester->orderNo / 2 ); | |
$additionalDetails->examMonth = $examRegistration->properties->examMonthName; | |
$additionalDetails->examYear = $examRegistration->properties->examYear; | |
$semesterName = CommonExamService::getInstance()->getDifferentSemesterName($semester->orderNo, true); | |
$additionalDetails->semInRomanLetter = $semesterName->romanLetter; | |
$rankReportRequest = new RankReportRequest(); | |
$rankReportRequest->courseTypeId = $examRegistration->groups[0]->courseTypeID;; | |
$rankReportRequest->year = $batchStartYear; | |
$rankReport = RankReportService::getInstance()->searchRankReport ( $rankReportRequest ); | |
if ( empty ( $rankReport ) ) { | |
throw new ExamControllerException(ExamControllerException::NO_REPORTS_DETAILS_FOUND,"No rank report created"); | |
} else { | |
$rankReport = $rankReport[0]; | |
} | |
$rankListReportRequest = new \stdClass; | |
$rankListReportRequest->examRegistrationId = $searchRequest->examRegistrationId; | |
$rankListReportRequest->rankListReportId = $rankReport->id; | |
$rankListReportRequest->academicTermId = $searchRequest->academicTermId; | |
$studentSubject = $this->getSubjectToppersStudentList($rankListReportRequest); | |
$syllabusDetails = []; | |
if(!empty($studentSubject)){ | |
foreach($studentSubject as $subject){ | |
$syllabusDetails[$subject->subjectGroupId]->id = $subject->subjectGroupId; | |
$syllabusDetails[$subject->subjectGroupId]->rankListDepartmentName = $subject->subjectGroupName; | |
$syllabusDetails[$subject->subjectGroupId]->rankListStudentCount = $subject->rankListStudentCount; | |
$syllabusDetails[$subject->subjectGroupId]->students[$subject->studentId]->totalObtainedMark += (int)$subject->subjectMarkObtained; | |
$syllabusDetails[$subject->subjectGroupId]->students[$subject->studentId]->subjectTotalMarks += (int)$subject->subjectTotalMarks; | |
$syllabusDetails[$subject->subjectGroupId]->students[$subject->studentId]->regNo = $subject->regNo; | |
$syllabusDetails[$subject->subjectGroupId]->students[$subject->studentId]->name = $subject->studentName; | |
$syllabusDetails[$subject->subjectGroupId]->students[$subject->studentId]->groupName = $subject->groupName; | |
$syllabusDetails[$subject->subjectGroupId]->students[$subject->studentId]->programName = $subject->programName; | |
if($subject->subjectFailedStatus == 'FAILED'){ | |
$syllabusDetails[$subject->subjectGroupId]->students[$subject->studentId]->failedStatus = "FAILED"; | |
} | |
} | |
foreach($syllabusDetails as $syllabus){ | |
$count = 0; | |
$syllabus->totalStudentCount = count($syllabus->students); | |
$syllabus->passedStudents = array_filter($syllabus->students,function($student){ | |
return $student->failedStatus != 'FAILED'; | |
}); | |
uasort( $syllabus->passedStudents, function ( $a, $b ) { | |
return $a->totalObtainedMark < $b->totalObtainedMark; | |
}); | |
$studentCount = (int)$syllabus->rankListStudentCount; | |
$lastMark = 0; | |
$totalSelectedStudents = 0; | |
foreach ($syllabus->passedStudents as $key=> $student ) { | |
$student->percentage = ($student->totalObtainedMark /$student->subjectTotalMarks) * 100; | |
$student->percentage = round($student->percentage,2); | |
$totalSelectedStudents++; | |
if ($lastMark == $student->totalObtainedMark) { | |
$lastMark = $student->totalObtainedMark; | |
} | |
else if ( $count < $studentCount ) { | |
$lastMark = $student->totalObtainedMark; | |
$count++; | |
} | |
else if ($lastMark != $student->totalObtainedMark) { | |
$syllabus->passedStudents = array_slice($syllabus->passedStudents, 0, $totalSelectedStudents-1); | |
break; | |
} | |
} | |
$syllabus->students = []; | |
} | |
} | |
if(empty($syllabusDetails)){ | |
throw new ExamControllerException(ExamControllerException::NO_REPORTS_DETAILS_FOUND,"No Details Found"); | |
} | |
else{ | |
$templateName = "subjectToppersList"; | |
$responseHtml = TwigRenderer::renderTemplateFileToHtml(realpath(DOCUMENT_ROOT."../examcontroller-api/src/com/linways/web/templates/rankListReport/$templateName.twig"), [ 'syllabusDetails'=>$syllabusDetails ,'additionalDetails'=>$additionalDetails]); | |
$prtContent = NULL; | |
$prtContent .= '<html><head>'; | |
$prtContent .= "<style> | |
</style>"; | |
$prtContent .= '</head><title>Subject Toppers List</title><body>'; | |
$prtContent .= $responseHtml; | |
$prtContent .= '</body></html>'; | |
$options = array( | |
'page-width' => "210mm", | |
'page-height' => "297mm", | |
'dpi' => 96, | |
'margin-bottom' => "5mm", | |
'margin-top' => "5mm", | |
'margin-left' => "5mm", | |
'margin-right' => "5mm", | |
// 'binary' => "/usr/local/bin/wkhtmltopdf", // For Mac | |
'user-style-sheet' => realpath(DOCUMENT_ROOT . "/libcommon/bootstrap/css/bootstrap.min.css") | |
); | |
$programResult->dispalyHtmlData = $responseHtml; | |
$programResult->printData = PdfUtil::renderPdf($prtContent, $options); | |
$programResult->syllabusDetails = array_values($syllabusDetails); | |
} | |
} | |
catch (\Exception $e){ | |
throw new ExamControllerException($e->getCode(),$e->getMessage()); | |
} | |
return $programResult; | |
} | |
/** | |
* get Top Rank Report | |
* @param $searchRequest | |
* @return $programResult | |
*/ | |
public function getTopRankReport($searchRequest) { | |
$searchRequest = $this->realEscapeObject($searchRequest); | |
try{ | |
$programResult = new \stdClass; | |
$additionalDetails = new \stdClass; | |
$additionalDetails->collegeData = CommonExamService::getInstance()->getCollegeDetails(); | |
$requestForRankReport = new \stdClass; | |
$requestForRankReport->id = $searchRequest->rankListReportId; | |
$rankReport = reset($this->getAllRankListReports($requestForRankReport)); | |
$additionalDetails->courseTypeName = $rankReport->courseTypeName; | |
$additionalDetails->rankReportName = $rankReport->name; | |
$requestForRankReportMapping = new \stdClass; | |
$requestForRankReportMapping->rankReportId = $searchRequest->rankListReportId; | |
$rankReportMapping = $this->getAllRankListMappingDetails($requestForRankReportMapping); | |
$syllabusIds = array_unique(array_column($rankReportMapping, 'syllabusId')); | |
$syllabusDetails = []; | |
foreach($syllabusIds as $syllabusId){ | |
$rankListReportRequest = new \stdClass; | |
$rankListReportRequest->rankListReportId = $searchRequest->rankListReportId; | |
$rankListReportRequest->examRegistrationType = 'REGULAR'; | |
$rankListReportRequest->fetchAllSemesterDetailsByGroupNo = true; | |
$rankListReportRequest->syllabusId = $syllabusId; | |
$syllabus = reset($this->getConsolidatedStudentMarkDetailsForRankListReport($rankListReportRequest)); | |
if(!empty($syllabus)){ | |
$count = 0; | |
$syllabus->academicTerm = []; | |
$syllabus->totalStudentCount = count($syllabus->students); | |
foreach($syllabus->students as $student){ | |
$student->failedStatus = "PASSED"; | |
$student->totalObtainedMark = 0; | |
$student->totalMaxMark = 0; | |
foreach($student->academicTerms as $academicTerm){ | |
foreach($academicTerm->groups as $group){ | |
$syllabus->academicTerms[$academicTerm->id.$group->id]->id = $academicTerm->id; | |
$syllabus->academicTerms[$academicTerm->id.$group->id]->orderNo = $academicTerm->orderNo; | |
$syllabus->academicTerms[$academicTerm->id.$group->id]->groupNo = $group->groupNo; | |
$syllabus->academicTerms[$academicTerm->id.$group->id]->name = $academicTerm->name; | |
$group->totalObtainedMark = 0; | |
foreach($group->subjects as $subject){ | |
$group->totalObtainedMark = $group->totalObtainedMark + (int)$subject->subjectMarkObtained; | |
$student->totalObtainedMark = $student->totalObtainedMark + (int)$subject->subjectMarkObtained; | |
$student->totalMaxMark = $student->totalMaxMark + (int)$subject->subjectTotalMarks; | |
if($subject->failedStatus == 'FAILED'){ | |
$student->failedStatus = "FAILED"; | |
} | |
} | |
} | |
} | |
} | |
$syllabus->passedStudents = array_filter($syllabus->students,function($student){ | |
return $student->failedStatus != 'FAILED'; | |
}); | |
uasort( $syllabus->academicTerms, function ( $a, $b ) { | |
return $a->orderNo > $b->orderNo; | |
}); | |
uasort( $syllabus->passedStudents, function ( $a, $b ) { | |
return $a->totalObtainedMark < $b->totalObtainedMark; | |
}); | |
$studentCount = (int)$syllabus->rankListStudentCount; | |
foreach ($syllabus->passedStudents as $student ) { | |
$student->percentage = ($student->totalObtainedMark /$student->totalMaxMark) * 100; | |
$student->percentage = round($student->percentage,2); | |
$student->percentage = sprintf("%.02f", $student->percentage); | |
$count++; | |
// $student->class = PatternCourseService::getInstance()->getPatternCourseCodeByStudentId ($studentId, "abbr"); | |
if ( $count <= $studentCount ) { | |
$lastMark = $student->totalObtainedMark; | |
} | |
else if ($lastMark != $student->totalObtainedMark) { | |
// $studentDetail->totalStudentCount = count($studentDetail->student); | |
$syllabus->passedStudents = array_slice($syllabus->passedStudents, 0, $count-1); | |
break; | |
} | |
} | |
$syllabusDetails[] = $syllabus; | |
} | |
} | |
if(empty($syllabusDetails)){ | |
throw new ExamControllerException(ExamControllerException::NO_REPORTS_DETAILS_FOUND,"No Details Found"); | |
} | |
else{ | |
$templateName = "TopRankReportList"; | |
$responseHtml = TwigRenderer::renderTemplateFileToHtml(realpath(DOCUMENT_ROOT."../examcontroller-api/src/com/linways/web/templates/rankListReport/$templateName.twig"), [ 'syllabusDetails'=>$syllabusDetails ,'additionalDetails'=>$additionalDetails]); | |
$prtContent = NULL; | |
$prtContent .= '<html><head>'; | |
$prtContent .= "<style> | |
</style>"; | |
$prtContent .= '</head><title>Top Rank Report</title><body>'; | |
$prtContent .= $responseHtml; | |
$prtContent .= '</body></html>'; | |
$options = array( | |
'page-width' => "210mm", | |
'page-height' => "297mm", | |
'dpi' => 96, | |
'margin-bottom' => "5mm", | |
'margin-top' => "5mm", | |
'margin-left' => "5mm", | |
'margin-right' => "5mm", | |
// 'binary' => "/usr/local/bin/wkhtmltopdf", // For Mac | |
'user-style-sheet' => realpath(DOCUMENT_ROOT . "/libcommon/bootstrap/css/bootstrap.min.css") | |
); | |
$programResult->dispalyHtmlData = $responseHtml; | |
$programResult->printData = PdfUtil::renderPdf($prtContent, $options); | |
} | |
} | |
catch (\Exception $e){ | |
throw new ExamControllerException($e->getCode(),$e->getMessage()); | |
} | |
return $programResult; | |
} | |
/** | |
* get All Registered Students Details For Rank List Report | |
* @param $searchRequest | |
*/ | |
public function getConsolidatedStudentMarkDetailsForRankListReport($searchRequest) { | |
$searchRequest = $this->realEscapeObject($searchRequest); | |
try{ | |
if($searchRequest->fetchAllSemesterDetailsByGroupNo == true){ | |
$mapper = $this->mapper[RankListReportServiceMapper::GET_SEMESTER_RANK_REPORT]; | |
} | |
else{ | |
$mapper = $this->mapper[RankListReportServiceMapper::GET_SUBJECT_RANK_REPORT]; | |
} | |
$orderBy = "ORDER BY spa.properties->>'$.registerNumber' ASC , CAST(cap.properties ->> '$.order' AS UNSIGNED) ASC , CAST(aps.properties ->> '$.order' AS UNSIGNED) ASC "; | |
$whereQuery = ""; | |
if(!empty($searchRequest->examRegistrationId)) { | |
$examRegistrationIdString = is_array($searchRequest->examRegistrationId) ? "'" . implode("','",$searchRequest->examRegistrationId) . "'" : "'".$searchRequest->examRegistrationId."'"; | |
$whereQuery .= " AND eer.id IN ( $examRegistrationIdString )"; | |
} | |
if(!empty($searchRequest->examRegistrationType)) { | |
$examRegistrationTypeString = is_array($searchRequest->examRegistrationType) ? "'" . implode("','",$searchRequest->examRegistrationType) . "'" : "'".$searchRequest->examRegistrationType."'"; | |
$whereQuery .= " AND eer.type IN ( $examRegistrationTypeString )"; | |
} | |
if(!empty($searchRequest->studentId)) { | |
$studentIdString = is_array($searchRequest->studentId) ? "'" . implode("','",$searchRequest->studentId) . "'" : "'".$searchRequest->studentId."'"; | |
$whereQuery .= " AND sa.studentID IN ( $studentIdString )"; | |
} | |
if(!empty($searchRequest->academicTermId)) { | |
$academicTermIdString = is_array($searchRequest->academicTermId) ? "'" . implode("','",$searchRequest->academicTermId) . "'" : "'".$searchRequest->academicTermId."'"; | |
$whereQuery .= " AND act.id IN ( $academicTermIdString )"; | |
} | |
if(!empty($searchRequest->rankListReportId)) { | |
$rankListReportIdString = is_array($searchRequest->rankListReportId) ? "'" . implode("','",$searchRequest->rankListReportId) . "'" : "'".$searchRequest->rankListReportId."'"; | |
$whereQuery .= " AND erlrssm.rank_report_id IN ( $rankListReportIdString )"; | |
} | |
if(!empty($searchRequest->syllabusId)) { | |
$syllabusIdString = is_array($searchRequest->syllabusId) ? "'" . implode("','",$searchRequest->syllabusId) . "'" : "'".$searchRequest->syllabusId."'"; | |
$whereQuery .= " AND cs.id IN ( $syllabusIdString )"; | |
} | |
$query = "SELECT DISTINCT | |
sa.studentID AS id, | |
sa.studentID AS studentId, | |
sa.studentName, | |
spa.properties->>'$.registerNumber' AS regNo, | |
g.id AS groupId, | |
g.name AS groupName, | |
act.id AS academicTermId, | |
act.name AS academicTermName, | |
act.properties ->>'$.orderNo' AS academicOrderNo, | |
g.properties ->> '$.startYear' AS academicYear, | |
eer.type AS latestExamType, | |
eer.properties ->> '$.examYear' AS latestExamYear, | |
eer.properties ->> '$.examMonth' AS latestExamMonth, | |
cs.id as syllabusId, | |
cs.name as syllabusName, | |
cs.description as syllabusDesc, | |
cs.`type` as syllabusType, | |
cs.properties as syllabusProperties, | |
cs.properties ->>'$.rankListDepartmentName' AS rankListDepartmentName, | |
cs.properties ->>'$.rankListStudentCount' AS rankListStudentCount, | |
cs.properties ->>'$.rankListStudentCount' AS rankListIsLanguage, | |
s.code AS subjectCode, | |
s.name AS subjectName, | |
s.description AS subjectDesc, | |
erlrssm.properties->>'$.groupNo' as groupNo, | |
eers.cm_academic_paper_subjects_id AS academicPaperSubjectId, | |
esmdsub.mark_details ->>'$.markObtained' AS subjectMarkObtained, | |
esmdsub.mark_details->>'$.attendanceStatus' AS subjectAttendanceStatus, | |
esmdsub.failed_status AS subjectFailedStatus, | |
esmdsubcon.mark_details ->>'$.totalMarks' AS subjectTotalMarks, | |
eer.id as examRegId, | |
eer.type as examType | |
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 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 cm_syllabus_academic_term_settings csats ON | |
csats.id = cap.cm_syllabus_academic_term_settings_id | |
INNER JOIN cm_syllabus cs ON | |
cs.id = csats.cm_syllabus_id | |
INNER JOIN ec_rank_list_report_syllabus_subject_mapping erlrssm ON | |
erlrssm.cm_syllabus_id = cs.id AND | |
erlrssm.cm_academic_paper_subjects_id = aps.id | |
INNER JOIN v4_ams_subject s ON | |
aps.ams_subject_id = s.id | |
INNER JOIN program p ON | |
p.id = g.properties ->> '$.programId' | |
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' AND | |
CAST(esar.properties ->> '$.feeStatus' AS CHAR) = 'PAID' ) OR | |
CAST(esar.properties->>'$.studentAttendanceStatus' AS CHAR) = 'FE' AND | |
CAST(esar.properties->>'$.registrationStatus' AS CHAR) ='NOT_REGISTERED') | |
INNER JOIN ec_student_exam_registration_details eserd ON | |
eserd.student_id = esar.student_id AND | |
eserd.ec_exam_registration_id = eer.id | |
INNER JOIN student_program_account spa ON | |
spa.current_program_id = p.id AND | |
spa.student_id = esar.student_id | |
INNER JOIN studentaccount sa ON | |
sa.studentID = esar.student_id | |
INNER JOIN academic_term act ON | |
act.id = CAST(eerb.properties ->> '$.academicTermId'AS CHAR) | |
INNER JOIN ec_subject_mark_details esmdsub ON | |
esmdsub.ec_exam_registration_id = eerb.ec_exam_registration_id AND esmdsub.groups_id = eerb.groups_id AND esmdsub.cm_academic_paper_subjects_id = eers.cm_academic_paper_subjects_id AND esmdsub.student_id = sa.studentID | |
INNER JOIN ec_consolidated_subject_mark_details esmdsubcon ON | |
esmdsubcon.groups_id = eerb.groups_id AND esmdsubcon.cm_academic_paper_subjects_id = eers.cm_academic_paper_subjects_id AND esmdsubcon.student_id = sa.studentID | |
WHERE 1=1 AND cs.properties ->>'$.rankListDepartmentName' IS NOT NULL AND erlrssm.properties->>'$.groupNo' IS NOT NULL "; | |
$studentMarkDetails = $this->executeQueryForList($query.$whereQuery.$orderBy,$mapper); | |
} | |
catch (\Exception $e){ | |
throw new ExamControllerException($e->getCode(),$e->getMessage()); | |
} | |
return $studentMarkDetails; | |
} | |
/** | |
* get All Rank List Reports Mapping Details | |
* @param $searchRequest | |
* @return $response | |
* @author Krishnajith | |
*/ | |
public function getAllRankListMappingDetails($searchRequest) { | |
$searchRequest = $this->realEscapeObject($searchRequest); | |
try{ | |
$whereQuery = null; | |
$orderBy = ""; | |
$whereQuery = ""; | |
if(!empty($searchRequest->rankReportId)) { | |
$rankReportIdString = is_array($searchRequest->rankReportId) ? "'" . implode("','",$searchRequest->rankReportId) . "'" : "'".$searchRequest->rankReportId."'"; | |
$whereQuery .= " AND erlrssm.rank_report_id IN ( $rankReportIdString )"; | |
} | |
$query = "SELECT DISTINCT id, | |
cm_syllabus_id AS syllabusId, | |
cm_academic_paper_subjects_id AS academicPaperSubjectsId, | |
rank_report_id as rankReportId | |
FROM | |
`ec_rank_list_report_syllabus_subject_mapping` erlrssm | |
WHERE 1=1 "; | |
$rankListReportMappingDetails = $this->executeQueryForList($query.$whereQuery.$orderBy); | |
return $rankListReportMappingDetails; | |
} | |
catch (\Exception $e){ | |
throw new ExamControllerException($e->getCode(),$e->getMessage()); | |
} | |
} | |
/** | |
* get Subject Toppers List | |
* @param $searchRequest | |
* @return $programResult | |
*/ | |
public function getMeritCertificate($searchRequest) { | |
$searchRequest = $this->realEscapeObject($searchRequest); | |
try{ | |
$programResult = new \stdClass; | |
$certificateDetails = new \stdClass; | |
$requestForExamRegistration = new \stdClass; | |
$requestForExamRegistration->examRegistrationId = $searchRequest->examRegistrationId; | |
$examRegistration = reset(ExamRegistrationService::getInstance()->searchDetailedExamRegistrationDetails($requestForExamRegistration)); | |
if(!empty($examRegistration)){ | |
$certificateDetails->examMonth = $examRegistration->examMonthName; | |
$certificateDetails->examYear = $examRegistration->examYear; | |
$certificateDetails->courseTypeName = $examRegistration->groups[0]->courseTypeName; | |
$semesterNames = CommonExamService::getInstance()->getDifferentSemesterName($examRegistration->groups[0]->academicTermName); | |
$certificateDetails->semInRomanLetter = $semesterNames->romanLetter; | |
$certificateDetails->regNo = $searchRequest->regNo; | |
$certificateDetails->studentName = $searchRequest->studentName; | |
$certificateDetails->groupName = $searchRequest->programName; | |
$certificateDetails->rank = $searchRequest->rank; | |
$certificateDetails->rankInWords = CommonUtil::ordinalNumberInWords($searchRequest->rank); | |
$certificateDetails->rankListDepartmentName = $searchRequest->rankListDepartmentName; | |
$certificateDetails->totalStudentCount = $searchRequest->totalStudentCount; | |
$collegeData = CommonExamService::getInstance()->getCollegeDetails(); | |
$certificateDetails->coeSign = $collegeData->coeSign; | |
$certificateDetails->principalSign = $collegeData->principalSign; | |
} | |
if(empty($certificateDetails)){ | |
throw new ExamControllerException(ExamControllerException::NO_REPORTS_DETAILS_FOUND,"No Details Found"); | |
} | |
else{ | |
$templateName = "meritCertificate_template1"; | |
$responseHtml = TwigRenderer::renderTemplateFileToHtml(realpath(DOCUMENT_ROOT."../examcontroller-api/src/com/linways/web/templates/rankListReport/$templateName.twig"), [ 'certificateDetails'=>$certificateDetails]); | |
$prtContent = NULL; | |
$prtContent .= '<html><head>'; | |
$prtContent .= "<style> | |
</style>"; | |
$prtContent .= '</head><title>Merit Certificate</title><body>'; | |
$prtContent .= $responseHtml; | |
$prtContent .= '</body></html>'; | |
$options = array( | |
'page-width' => "210mm", | |
'page-height' => "297mm", | |
'dpi' => 96, | |
'margin-bottom' => "5mm", | |
'margin-top' => "5mm", | |
'margin-left' => "5mm", | |
'margin-right' => "5mm", | |
// 'binary' => "/usr/local/bin/wkhtmltopdf", // For Mac | |
'user-style-sheet' => realpath(DOCUMENT_ROOT . "/libcommon/bootstrap/css/bootstrap.min.css") | |
); | |
$programResult->dispalyHtmlData = $responseHtml; | |
$programResult->printData = PdfUtil::renderPdf($prtContent, $options); | |
} | |
} | |
catch (\Exception $e){ | |
throw new ExamControllerException($e->getCode(),$e->getMessage()); | |
} | |
return $programResult; | |
} | |
/** | |
* get Subject Toppers List | |
* @param $searchRequest | |
* @return $programResult | |
*/ | |
public function getSubCurriculumByDepartmentAndRankReport($searchRequest) { | |
$searchRequest = $this->realEscapeObject($searchRequest); | |
try{ | |
$whereQuery = ""; | |
$joinCondition = "erlps.cm_syllabus_id = cs.id"; | |
if(!empty($searchRequest->rankListReportId)) { | |
$rankReportIdString = is_array($searchRequest->rankListReportId) ? "'" . implode("','",$searchRequest->rankListReportId) . "'" : "'".$searchRequest->rankListReportId."'"; | |
$joinCondition .= " AND erlps.rank_report_id IN ( $rankReportIdString )"; | |
} | |
if(!empty($searchRequest->departmentId)) { | |
$departmentIdString = is_array($searchRequest->departmentId) ? "'" . implode("','",$searchRequest->departmentId) . "'" : "'".$searchRequest->departmentId."'"; | |
$whereQuery .= " AND cs.department_id IN ( $departmentIdString )"; | |
} | |
if(!empty($searchRequest->batchStartYear)) { | |
$whereQuery .= " AND g.properties ->> '$.startYear' = '$searchRequest->batchStartYear'"; | |
} | |
if(!empty($searchRequest->courseTypeId)) { | |
$whereQuery .= " AND p.course_type_id = '$searchRequest->courseTypeId'"; | |
} | |
$groupBy = "GROUP BY cs.id"; | |
$query = "SELECT cs.id, cs.name, erlps.rank_report_subject_group_id as subjectGroupId, erlps.id as relationId FROM cm_syllabus cs | |
INNER JOIN cm_syllabus_academic_term_settings csats ON csats.cm_syllabus_id = cs.id | |
INNER JOIN cm_academic_paper cap ON cap.cm_syllabus_academic_term_settings_id = csats.id | |
INNER JOIN cm_academic_paper_subjects caps ON caps.cm_academic_paper_id = cap.id | |
INNER JOIN ec_exam_registration_subject eers ON eers.cm_academic_paper_subjects_id = caps.id | |
INNER JOIN ec_exam_registration_batch eerb ON eerb.id = eers.ec_exam_registration_batch_id | |
INNER JOIN `groups` g ON g.id = eerb.groups_id | |
INNER JOIN program p ON p.id = g.properties ->> '$.programId' | |
LEFT JOIN ec_rank_list_report_syllabus_subject_group_relation erlps ON $joinCondition | |
WHERE 1 = 1 "; | |
$rankListReportMappingDetails = $this->executeQueryForList($query.$whereQuery.$groupBy); | |
return $rankListReportMappingDetails; | |
} | |
catch (\Exception $e){ | |
throw new ExamControllerException($e->getCode(),$e->getMessage()); | |
} | |
} | |
/** | |
* Insert rank List Syllabus Subject Mapping | |
* @param $ranklistSubjectMappingArray | |
* @return Boolean true | |
* @throws ExamControllerException | |
* @author Krishnajith V | |
*/ | |
public function saveRankListSyllabusSubjectRelation ($ranklistSubjectMappingArray) { | |
$ranklistSubjectMappingArray = $this->realEscapeArray($ranklistSubjectMappingArray); | |
$values = []; | |
if ( empty ( $ranklistSubjectMappingArray ) ) { | |
throw new ExamControllerException (ExamControllerException::EMPTY_PARAMETERS, "Empty Parameters"); | |
} | |
foreach ( $ranklistSubjectMappingArray as $ranklistSubjectMapping ) { | |
$ranklistSubjectMapping->properties = $ranklistSubjectMapping->properties ? json_encode($ranklistSubjectMapping->properties) : "{}"; | |
$values[] = "( | |
'$ranklistSubjectMapping->rankReportId', | |
'$ranklistSubjectMapping->syllabusId', | |
'$ranklistSubjectMapping->rankReportSubjectGroupId', | |
'$ranklistSubjectMapping->properties', | |
'$ranklistSubjectMapping->createdBy' | |
)"; | |
$staffId = $ranklistSubjectMapping->createdBy; | |
} | |
try { | |
$sql = "INSERT INTO `ec_rank_list_report_syllabus_subject_group_relation` ( | |
`rank_report_id`, | |
`cm_syllabus_id`, | |
`rank_report_subject_group_id`, | |
`properties`, | |
`created_by` | |
) VALUES " . implode(', ', $values). " ON DUPLICATE KEY UPDATE | |
`rank_report_subject_group_id` = VALUES(rank_report_subject_group_id), | |
`properties` = VALUES(properties), | |
`updated_by` = VALUES(created_by)"; | |
$this->executeQuery($sql); | |
AMSLogger::log_info($this->logger,Events::SAVE_RANK_LIST_REPORT_MAPPING, [ | |
"staff" => new Staff(["id" => $staffId]), | |
"request" => $ranklistSubjectMappingArray, | |
"status" => StatusConstants::SUCCESS | |
]); | |
} catch (\Exception $e) { | |
AMSLogger::log_error($this->logger,Events::SAVE_RANK_LIST_REPORT_MAPPING, [ | |
"staff" => new Staff(["id" => $staffId]), | |
"request" => $ranklistSubjectMappingArray, | |
"errorCode" => $e->getCode(), | |
"errorMessage" => $e->getMessage(), | |
"status" => StatusConstants::FAILED | |
]); | |
throw new ExamControllerException ($e->getCode(), $e->getMessage()); | |
} | |
return true; | |
} | |
/** | |
* get All Registered Students Details For Rank List Report | |
* @param $searchRequest | |
*/ | |
public function getSubjectToppersStudentList($searchRequest) { | |
$searchRequest = $this->realEscapeObject($searchRequest); | |
try{ | |
$orderBy = "ORDER BY spa.properties->>'$.registerNumber' ASC , CAST(cap.properties ->> '$.order' AS UNSIGNED) ASC , CAST(aps.properties ->> '$.order' AS UNSIGNED) ASC "; | |
$whereQuery = ""; | |
if(!empty($searchRequest->examRegistrationId)) { | |
$examRegistrationIdString = is_array($searchRequest->examRegistrationId) ? "'" . implode("','",$searchRequest->examRegistrationId) . "'" : "'".$searchRequest->examRegistrationId."'"; | |
$whereQuery .= " AND eer.id IN ( $examRegistrationIdString )"; | |
} | |
if(!empty($searchRequest->examRegistrationType)) { | |
$examRegistrationTypeString = is_array($searchRequest->examRegistrationType) ? "'" . implode("','",$searchRequest->examRegistrationType) . "'" : "'".$searchRequest->examRegistrationType."'"; | |
$whereQuery .= " AND eer.type IN ( $examRegistrationTypeString )"; | |
} | |
if(!empty($searchRequest->studentId)) { | |
$studentIdString = is_array($searchRequest->studentId) ? "'" . implode("','",$searchRequest->studentId) . "'" : "'".$searchRequest->studentId."'"; | |
$whereQuery .= " AND sa.studentID IN ( $studentIdString )"; | |
} | |
if(!empty($searchRequest->academicTermId)) { | |
$academicTermIdString = is_array($searchRequest->academicTermId) ? "'" . implode("','",$searchRequest->academicTermId) . "'" : "'".$searchRequest->academicTermId."'"; | |
$whereQuery .= " AND act.id IN ( $academicTermIdString )"; | |
} | |
if(!empty($searchRequest->rankListReportId)) { | |
$rankListReportIdString = is_array($searchRequest->rankListReportId) ? "'" . implode("','",$searchRequest->rankListReportId) . "'" : "'".$searchRequest->rankListReportId."'"; | |
$whereQuery .= " AND erlrssm.rank_report_id IN ( $rankListReportIdString )"; | |
} | |
if(!empty($searchRequest->syllabusId)) { | |
$syllabusIdString = is_array($searchRequest->syllabusId) ? "'" . implode("','",$searchRequest->syllabusId) . "'" : "'".$searchRequest->syllabusId."'"; | |
$whereQuery .= " AND cs.id IN ( $syllabusIdString )"; | |
} | |
$query = "SELECT DISTINCT | |
sa.studentID AS id, | |
sa.studentID AS studentId, | |
sa.studentName, | |
spa.properties->>'$.registerNumber' AS regNo, | |
g.id AS groupId, | |
g.name AS groupName, | |
p.name AS programName, | |
act.id AS academicTermId, | |
act.name AS academicTermName, | |
act.properties ->>'$.orderNo' AS academicOrderNo, | |
g.properties ->> '$.startYear' AS academicYear, | |
eer.type AS latestExamType, | |
eer.properties ->> '$.examYear' AS latestExamYear, | |
eer.properties ->> '$.examMonth' AS latestExamMonth, | |
cs.id as syllabusId, | |
cs.name as syllabusName, | |
cs.description as syllabusDesc, | |
cs.`type` as syllabusType, | |
cs.properties as syllabusProperties, | |
cs.properties ->>'$.rankListDepartmentName' AS rankListDepartmentName, | |
cs.properties ->>'$.rankListStudentCount' AS rankListStudentCount, | |
cs.properties ->>'$.rankListStudentCount' AS rankListIsLanguage, | |
s.code AS subjectCode, | |
s.name AS subjectName, | |
s.description AS subjectDesc, | |
eers.cm_academic_paper_subjects_id AS academicPaperSubjectId, | |
esmdsub.mark_details ->>'$.markObtained' AS subjectMarkObtained, | |
esmdsub.mark_details->>'$.attendanceStatus' AS subjectAttendanceStatus, | |
esmdsub.failed_status AS subjectFailedStatus, | |
esmdsubcon.mark_details ->>'$.totalMarks' AS subjectTotalMarks, | |
eer.id as examRegId, | |
eer.type as examType, | |
ersg.id AS subjectGroupId, | |
ersg.name AS subjectGroupName, | |
ersg.student_count AS rankListStudentCount | |
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 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 cm_syllabus_academic_term_settings csats ON | |
csats.id = cap.cm_syllabus_academic_term_settings_id | |
INNER JOIN cm_syllabus cs ON | |
cs.id = csats.cm_syllabus_id | |
INNER JOIN ec_rank_list_report_syllabus_subject_group_relation erlrssm ON | |
erlrssm.cm_syllabus_id = cs.id | |
INNER JOIN rank_report_subject_group ersg ON | |
ersg.id = erlrssm.rank_report_subject_group_id | |
INNER JOIN v4_ams_subject s ON | |
aps.ams_subject_id = s.id | |
INNER JOIN program p ON | |
p.id = g.properties ->> '$.programId' | |
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' AND | |
CAST(esar.properties ->> '$.feeStatus' AS CHAR) = 'PAID' ) OR | |
CAST(esar.properties->>'$.studentAttendanceStatus' AS CHAR) = 'FE' AND | |
CAST(esar.properties->>'$.registrationStatus' AS CHAR) ='NOT_REGISTERED') | |
INNER JOIN ec_student_exam_registration_details eserd ON | |
eserd.student_id = esar.student_id AND | |
eserd.ec_exam_registration_id = eer.id | |
INNER JOIN student_program_account spa ON | |
spa.student_id = esar.student_id | |
INNER JOIN studentaccount sa ON | |
sa.studentID = esar.student_id | |
INNER JOIN ec_subject_mark_details esmdsub ON | |
esmdsub.ec_exam_registration_id = eerb.ec_exam_registration_id AND esmdsub.groups_id = eerb.groups_id AND esmdsub.cm_academic_paper_subjects_id = eers.cm_academic_paper_subjects_id AND esmdsub.student_id = sa.studentID | |
INNER JOIN ec_consolidated_subject_mark_details esmdsubcon ON | |
esmdsubcon.groups_id = eerb.groups_id AND esmdsubcon.cm_academic_paper_subjects_id = eers.cm_academic_paper_subjects_id AND esmdsubcon.student_id = sa.studentID | |
INNER JOIN academic_term act ON | |
act.id = eerb.properties ->> '$.academicTermId' | |
WHERE 1=1 "; | |
$studentMarkDetails = $this->executeQueryForList($query.$whereQuery.$orderBy); | |
} | |
catch (\Exception $e){ | |
throw new ExamControllerException($e->getCode(),$e->getMessage()); | |
} | |
return $studentMarkDetails; | |
} | |
/** | |
* delete RankList Syllabus Subject Mapping | |
* @param Array $ids | |
* @return NULL | |
*/ | |
public function deleteRankListSubjectGroupRelation($id){ | |
$id = $this->realEscapeArray($id); | |
if(empty($id)) { | |
throw new ExamControllerException(ExamControllerException::EMPTY_PARAMETERS,"Error Delete Mapping Details! Please try again"); | |
} | |
$query = "DELETE FROM | |
ec_rank_list_report_syllabus_subject_group_relation | |
WHERE | |
id IN ($id) "; | |
try { | |
$this->executeQuery($query); | |
} catch (\Exception $e) { | |
throw new ExamControllerException(ExamControllerException::EMPTY_PARAMETERS,"Error Delete Mapping Details! Please try again"); | |
} | |
} | |
} |