Code Coverage |
||||||||||
Classes and Traits |
Functions and Methods |
Lines |
||||||||
| Total | |
0.00% |
0 / 1 |
|
0.00% |
0 / 28 |
CRAP | |
0.00% |
0 / 1739 |
| MarksCardService | |
0.00% |
0 / 1 |
|
0.00% |
0 / 28 |
50850.00 | |
0.00% |
0 / 1739 |
| __construct | |
0.00% |
0 / 1 |
2.00 | |
0.00% |
0 / 2 |
|||
| getBatchesForConsoliidatedMarksCard | |
0.00% |
0 / 1 |
90.00 | |
0.00% |
0 / 63 |
|||
| getBatchStudentsForConsoliidatedMarksCard | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 53 |
|||
| getMarkCardTemplate | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 12 |
|||
| getFinalConsolidatedMarksCard | |
0.00% |
0 / 1 |
2.00 | |
0.00% |
0 / 11 |
|||
| getConsoliidatedMarksCardData | |
0.00% |
0 / 1 |
702.00 | |
0.00% |
0 / 256 |
|||
| getOverallMarkDetails | |
0.00% |
0 / 1 |
552.00 | |
0.00% |
0 / 166 |
|||
| generateQRcodeByStudentId | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 43 |
|||
| getMarkListSubjectCategories | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 23 |
|||
| getDisplayFilterSettings | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 41 |
|||
| checkWhetherPropertyEditableForTheAcademicPaper | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 30 |
|||
| getProvisionalMarksCard | |
0.00% |
0 / 1 |
2.00 | |
0.00% |
0 / 11 |
|||
| getGroupSubjectCategoryDetails | |
0.00% |
0 / 1 |
56.00 | |
0.00% |
0 / 53 |
|||
| getBatchSubjectsInstitutionalAverge | |
0.00% |
0 / 1 |
56.00 | |
0.00% |
0 / 35 |
|||
| getStudentSemesterMarkDetails | |
0.00% |
0 / 1 |
42.00 | |
0.00% |
0 / 73 |
|||
| getConsoliidatedStudentData | |
0.00% |
0 / 1 |
2550.00 | |
0.00% |
0 / 291 |
|||
| blockBatchForDepromotion | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 26 |
|||
| getStudentCurrentFeSubjects | |
0.00% |
0 / 1 |
30.00 | |
0.00% |
0 / 51 |
|||
| getStudentRegisteredMoocCount | |
0.00% |
0 / 1 |
56.00 | |
0.00% |
0 / 41 |
|||
| getStudentExamResultByCluster | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 23 |
|||
| getStudentSubjectWiseResultByCluster | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 70 |
|||
| getStudentExamResultForProgressReport | |
0.00% |
0 / 1 |
552.00 | |
0.00% |
0 / 97 |
|||
| getStudentSemesterWiseResult | |
0.00% |
0 / 1 |
56.00 | |
0.00% |
0 / 74 |
|||
| getStudentRegularFeSubjects | |
0.00% |
0 / 1 |
30.00 | |
0.00% |
0 / 36 |
|||
| getMinorSyllabusDetailsByStudent | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 22 |
|||
| getBatchWiseCurrentFeStatus | |
0.00% |
0 / 1 |
56.00 | |
0.00% |
0 / 63 |
|||
| getStudentRegisteredMoocSubject | |
0.00% |
0 / 1 |
30.00 | |
0.00% |
0 / 33 |
|||
| getBatchStudentsForCsvWithSemester | |
0.00% |
0 / 1 |
30.00 | |
0.00% |
0 / 40 |
|||
| <?php | |
| namespace com\linways\ec\core\service; | |
| use com\linways\base\util\SecurityUtils; | |
| use com\linways\base\util\MakeSingletonTrait; | |
| use com\linways\ec\core\exception\ECCoreException; | |
| use com\linways\ec\core\exception\ExamControllerException; | |
| use com\linways\ec\core\request\MarkReportRequest; | |
| use com\linways\ec\core\dto\exam\AcademicTermMark; | |
| use com\linways\ec\core\constant\StatusConstants; | |
| use com\linways\ec\core\dto\exam\StudentDetails; | |
| use com\linways\ec\core\dto\StudentMarkDetails; | |
| use com\linways\ec\core\dto\exam\RegularExam; | |
| use com\linways\ec\core\dto\exam\ExamSubject; | |
| use com\linways\ec\core\dto\exam\MarkHistory; | |
| use com\linways\core\ams\professional\request\examcontroller\ConsolidatedMarkReportRequest; | |
| use com\linways\core\ams\professional\service\SubjectService as ProfessionalSubjectService; | |
| use com\linways\core\ams\professional\service\examcontroller\migration\finalMarkList\ScekConsolidatedMarkService; | |
| use com\linways\core\ams\professional\service\examcontroller\migration\finalMarkList\ConsolidatedMarkReportService; | |
| use com\linways\ec\core\service\MarkReportService; | |
| use com\linways\ec\core\service\FinalConsolidatedMarksCardGenerator; | |
| use com\linways\ec\core\service\ProvisionalMarksCardGenerator; | |
| use com\linways\core\ams\professional\util\CommonUtil; | |
| use com\linways\ec\core\mapper\MarksCardServiceMapper; | |
| use com\linways\ec\core\service\StudentMarkListService; | |
| use com\linways\ec\core\service\RegularConsolidatedMarkListGenerator\RegularConsolidatedMarkListGenerator; | |
| use com\linways\ec\core\request\SearchRuleRequest; | |
| use com\linways\ec\core\service\RuleService; | |
| use com\linways\ec\core\service\StudentExamRegistrationService; | |
| class MarksCardService extends BaseService | |
| { | |
| use MakeSingletonTrait; | |
| private function __construct() { | |
| $this->mapper = MarksCardServiceMapper::getInstance()->getMapper(); | |
| } | |
| /** | |
| * @param $request | |
| * @return Group | |
| */ | |
| public function getBatchesForConsoliidatedMarksCard($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $whereQuery = ""; | |
| $limitQuery = ""; | |
| if(!empty($request->batchStartYear)) { | |
| $whereQuery .= " AND CAST(g.properties->>'$.startYear' AS CHAR) = '$request->batchStartYear' "; | |
| } | |
| if(!empty($request->departmentId)) { | |
| $whereQuery .= " AND dpt.deptID = $request->departmentId "; | |
| } | |
| if(!empty($request->courseTypeId)) { | |
| $whereQuery .= " AND p.course_type_id = '$request->courseTypeId' "; | |
| } | |
| if(!empty($request->id)) { | |
| $whereQuery .= " AND g.id='$request->id' "; | |
| } | |
| $query = "SELECT | |
| g.id AS groupId, | |
| g.name AS groupName, | |
| g.properties AS groupProperties, | |
| dpt.deptName AS departmentName, | |
| dpt.deptID AS departmentId, | |
| dpt.departmentDesc AS departmentDescription, | |
| d.id AS degreeId, | |
| d.name AS degreeName, | |
| d.description AS degreeDescription, | |
| atm.id AS academicTermId, | |
| atm.name AS academicTermName, | |
| ebad.properties->>'$.isPublishFCMC' AS isResultPublished, | |
| ebad.properties->>'$.startDateFCMC' AS publishingStartDate, | |
| ebad.properties->>'$.endDateFCMC' AS publishingEndDate, | |
| ebad.properties AS additionalProperties | |
| FROM | |
| `groups` g | |
| INNER JOIN department dpt ON | |
| dpt.deptID = g.properties->>'$.departmentId' | |
| INNER JOIN program p ON | |
| p.id = CAST(g.properties->>'$.programId' AS CHAR) | |
| INNER JOIN `degree` d ON | |
| d.id = p.degree_id | |
| INNER JOIN academic_term atm ON | |
| atm.id = CAST(g.properties->>'$.currentTermId' AS CHAR) | |
| LEFT JOIN ec_batch_additional_details ebad ON | |
| ebad.groups_id = g.id | |
| WHERE | |
| g.`type` = 'BATCH' | |
| AND g.trashed IS NULL "; | |
| try { | |
| $groups = $this->executeQueryForList($query.$whereQuery); | |
| } catch (\Exception $e) { | |
| throw new ECCoreException(ECCoreException::ERROR_FETCHING,"Cannot fetch group details! Please try again."); | |
| } | |
| if(empty($groups)) | |
| { | |
| throw new ECCoreException(ECCoreException::EMPTY_SEARCH_ITEMS,"No Batch Groups Found"); | |
| } | |
| foreach ($groups as $group) { | |
| $group->properties = json_decode($group->properties); | |
| $group->additionalProperties = json_decode($group->additionalProperties); | |
| $group->isResultPublished = $group->isResultPublished == '1' ? true : false; | |
| } | |
| return $groups; | |
| } | |
| /** | |
| * @param $request | |
| */ | |
| public function getBatchStudentsForConsoliidatedMarksCard($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $whereQuery = ""; | |
| $limitQuery = ""; | |
| $orderByQuery = ""; | |
| if(!empty($request->groupId)) { | |
| $whereQuery .= " AND bg.id='$request->groupId' "; | |
| } | |
| $orderByQuery = " ORDER BY spa.properties->>'$.registerNumber' ASC"; | |
| $query = "SELECT | |
| st.studentID AS studentId, | |
| st.studentName, | |
| spa.properties->>'$.registerNumber' AS registerNo, | |
| spa.properties->>'$.rollNumber' AS rollNo, | |
| bg.properties->>'$.startYear' as startYear, | |
| bg.properties->>'$.currentTermId' as currentTermId, | |
| bg.properties->>'$.finalTermId' as finalTermId, | |
| currentTerm.properties->>'$.orderNo' AS currentTermOrder, | |
| finalTerm.properties->>'$.orderNo' AS finalTermOrder, | |
| p.id as programId, | |
| esad.properties->>'$.withHeldStatus' as withHeldStatus, | |
| p.course_type_id as courseTypeId, | |
| ecmd.failed_status | |
| FROM | |
| `groups` bg | |
| INNER JOIN group_members gm on | |
| gm.groups_id = bg.id | |
| INNER JOIN student_program_account spa on | |
| spa.id = gm.student_id AND spa.current_batch_id = bg.id | |
| INNER JOIN studentaccount st on | |
| st.studentID = spa.student_id | |
| INNER JOIN academic_term finalTerm on | |
| finalTerm.id = bg.properties->>'$.finalTermId' | |
| INNER JOIN academic_term currentTerm on | |
| currentTerm.id = bg.properties->>'$.currentTermId' | |
| INNER JOIN program p ON | |
| p.id = CAST(bg.properties->>'$.programId' AS CHAR) | |
| LEFT JOIN ec_course_mark_details ecmd ON | |
| ecmd.groups_id = bg.id | |
| AND ecmd.student_id = st.studentID | |
| LEFT JOIN ec_student_additional_details esad ON | |
| esad.student_id = st.studentID AND | |
| esad.program_id = p.id AND | |
| esad.type = 'FINAL_MARK_CARD' | |
| WHERE | |
| gm.academic_status in ('ACTIVE','COMPLETED') "; | |
| try { | |
| $students = $this->executeQueryForList($query.$whereQuery.$orderByQuery); | |
| } catch (\Exception $e) { | |
| throw new ECCoreException(ECCoreException::ERROR_FETCHING,"Cannot fetch group students! Please try again."); | |
| } | |
| return $students; | |
| } | |
| public function getMarkCardTemplate($type) | |
| { | |
| $sql = null; | |
| $type = $this->realEscapeString($type); | |
| $markListTemplate = null; | |
| $sql = "SELECT templateName AS templateName FROM universityMarkListTemplates WHERE examType = '$type' AND isActive = 1"; | |
| try { | |
| $markListTemplate = $this->executeQueryForObject($sql)->templateName; | |
| } catch (\Exception $e) { | |
| throw new ECCoreException($e->getCode(), $e->getMessage()); | |
| } | |
| return $markListTemplate; | |
| } | |
| /** | |
| * @param $request | |
| */ | |
| public function getFinalConsolidatedMarksCard($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $template = $this->getMarkCardTemplate("EC_FINAL_CONSOLIDATED_MARK_LIST"); | |
| $consolidatedRequest = new MarkReportRequest(); | |
| $consolidatedRequest->studentId = $request->studentId; | |
| $consolidatedRequest->groupId = $request->groupId; | |
| $consolidatedRequest->template = $template; | |
| $consolidatedRequest->filterSettings = $request->filterSettings; | |
| $marksCardData = FinalConsolidatedMarksCardGenerator::getInstance()->getFinalConsolidatedMarksCard($consolidatedRequest); | |
| return $marksCardData; | |
| } | |
| /** | |
| * @param $request | |
| */ | |
| public function getConsoliidatedMarksCardData($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $consolidatedRequest->studentId = $request->studentId; | |
| $consolidatedRequest->groupId = $request->groupId; | |
| try { | |
| $whereQuery = ""; | |
| $selectQuery = ""; | |
| $joinQuery = ""; | |
| // if(!empty($request->groupId)) { | |
| // $groupIdString = is_array($request->groupId) ? implode("','",$request->groupId) : $request->groupId; | |
| // $whereQuery .= " AND g.id IN ('$groupIdString') "; | |
| // } | |
| if(!empty($request->studentId)) { | |
| $studentIdString = is_array($request->studentId) ? implode(",",$request->studentId) : $request->studentId; | |
| $whereQuery .= " AND ecsmd.student_id IN ($studentIdString) "; | |
| } | |
| if(!empty($request->excludeMinorHonor)) { | |
| $whereQuery .= " AND ( cs.type != 'HONOURS') AND ( cs.type != 'MINOR') "; | |
| } | |
| else if(!empty($request->consoiderMinorSubjects)) { | |
| $whereQuery .= " AND cs.type = 'MINOR' "; | |
| } | |
| else if(!empty($request->consoiderHonoursSubjects)) { | |
| $whereQuery .= " AND cs.type = 'HONOURS' "; | |
| } | |
| else if(!empty($request->consoiderBothMinorHonoursSubjects)) { | |
| $whereQuery .= " AND cs.type IN ('HONOURS','MINOR')"; | |
| } | |
| if(!empty($request->consoiderMinorSubjects) || !empty($request->consoiderHonoursSubjects) || !empty($request->consoiderBothMinorHonoursSubjects)){ | |
| $selectQuery = " ,IF(esar.properties->>'$.syllabusSubType' = 'MOOC',1,0) AS isMoocSubject, | |
| esar.properties->>'$.moocCertificateStatus' AS moocVerificationStatus"; | |
| $joinQuery = " INNER JOIN ec_exam_registration_subject eers ON | |
| eers.cm_academic_paper_subjects_id = caps.id | |
| INNER JOIN ec_student_assessment_registration esar ON | |
| esar.am_assessment_id = eers.am_assessment_id AND | |
| esar.student_id = ecsmd.student_id"; | |
| } | |
| elseif (!empty($request->considerRegularSubjectsExcludingMinorHonours)) { | |
| $joinQuery = " INNER JOIN ec_exam_registration_subject eers ON | |
| eers.cm_academic_paper_subjects_id = caps.id | |
| INNER JOIN ec_student_assessment_registration esar ON | |
| esar.am_assessment_id = eers.am_assessment_id AND | |
| esar.student_id = ecsmd.student_id | |
| 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 esar.ec_exam_registration_type = eer.type | |
| AND eer.type = 'REGULAR'"; | |
| $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->academicTermId)) { | |
| $academicTermIdString = is_array($request->academicTermId) ? implode(",",$request->academicTermId) : $request->academicTermId; | |
| $whereQuery .= " AND atm.id IN ($academicTermIdString) "; | |
| } | |
| $query = "SELECT | |
| ecsmd.student_id AS studentId, | |
| ecsmd.groups_id AS groupsId, | |
| atm.id AS termId, | |
| ecsmd.cm_academic_paper_subjects_id AS paperSubjetId, | |
| ecsmd.mark_details AS markDetails, | |
| ecsmd.mark_history AS markHistory, | |
| ecsmd.no_of_chances_taken AS noOfChancesTaken, | |
| ecsmd.total_mark AS totalMarkObtained, | |
| ecsmd.class, | |
| ecsmd.grade, | |
| ecsmd.failed_status AS failedStatus, | |
| s.code AS code, | |
| s.name AS name, | |
| caps.properties ->> '$.syllabusName' AS syllabusName, | |
| COALESCE(cap.properties ->> '$.order', 0) * 100 + COALESCE(caps.properties ->> '$.order', 0) AS priority, | |
| IF(caps.properties ->> '$.classType' = 'THEORY',1,0) AS isTheory, | |
| sc.subjectcatID AS categoryId, | |
| sc.subjectcatName AS categoryName, | |
| sc.subjectcatPriority AS categoryPriority, | |
| sc.subjectcatCode AS subjectCategoryCode, | |
| sc.parentID AS categoryParentId, | |
| sc.use_bring_value AS useBringValue, | |
| cc.categoryCode, | |
| cs.name as academicSyllabusName, | |
| cs.description as syllabusDescription, | |
| cs.type as syllabusType, | |
| cst.name as subjectTypeName, | |
| cst.code as subjectTypeCode, | |
| caps.properties ->> '$.excludeSubjectFromTotal' AS excludeSubjectFromTotal, | |
| emcs.name AS displayCategoryName, | |
| cc.categoryCode | |
| $selectQuery | |
| FROM | |
| ec_consolidated_subject_mark_details ecsmd | |
| INNER JOIN student_program_account spa | |
| ON spa.student_id = ecsmd.student_id | |
| INNER JOIN `groups` g ON | |
| g.id = ecsmd.groups_id | |
| INNER JOIN cm_academic_paper_subjects caps ON | |
| caps.id = ecsmd.cm_academic_paper_subjects_id | |
| INNER JOIN cm_academic_paper cap ON | |
| cap.id = caps.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 academic_term atm ON | |
| atm.id = csats.academic_term_id | |
| INNER JOIN v4_ams_subject s ON | |
| s.id = caps.ams_subject_id | |
| $joinQuery | |
| LEFT JOIN subject_category sc ON | |
| sc.subjectcatID = caps.properties->>'$.categoryId' | |
| LEFT JOIN ec_marklist_categorize_subjects emcs ON emcs.groups_id = g.id AND emcs.subject_category_id = sc.subjectcatID | |
| LEFT JOIN cm_subject_types cst ON | |
| cst.id = caps.properties->>'$.subjectTypeId' | |
| LEFT JOIN categoryCode cc ON | |
| cc.subject_category_id = caps.properties->>'$.subjectTypeId' | |
| AND cc.subject_category_id = sc.subjectcatID | |
| AND cc.course_type_id = CAST(g.properties->>'$.courseTypeId' AS CHAR) | |
| WHERE | |
| 1 = 1 AND ecsmd.is_active = 1 | |
| $whereQuery ORDER BY COALESCE(cap.properties ->> '$.order', 0) * 100 + COALESCE(caps.properties ->> '$.order', 0) ASC "; | |
| $subjectsMarkDetails = $this->executeQueryForList($query); | |
| array_walk($subjectsMarkDetails,function($subject,$key){ | |
| $subject->markDetails = json_decode($subject->markDetails); | |
| $subject->markHistory = json_decode($subject->markHistory); | |
| $subject->examMonthYear = $subject->markDetails->latestExamMonth."/".$subject->markDetails->latestExamYear; | |
| $subject->creditPoint = round($subject->markDetails->credit * $subject->markDetails->gradePoint, 2); | |
| $subject->RSI = substr($subject->markDetails->latestExamType,0,1); | |
| $subject->priority = $subject->priority; | |
| }); | |
| $whereQuery = ""; | |
| // if(!empty($request->groupId)) { | |
| // $groupIdString = is_array($request->groupId) ? implode("','",$request->groupId) : $request->groupId; | |
| // $whereQuery .= " AND esmd.groups_id IN ('$groupIdString') "; | |
| // } | |
| if(!empty($request->studentId)) { | |
| $studentIdString = is_array($request->studentId) ? implode(",",$request->studentId) : $request->studentId; | |
| $whereQuery .= " AND esmd.student_id IN ($studentIdString) "; | |
| } | |
| if(!empty($request->academicTermId)) { | |
| $academicTermIdString = is_array($request->academicTermId) ? implode(",",$request->academicTermId) : $request->academicTermId; | |
| $whereQuery .= " AND atm.id IN ($academicTermIdString) "; | |
| } | |
| $query = "SELECT | |
| esmd.groups_id AS groupsId, | |
| esmd.student_id AS studentId, | |
| esmd.academic_term_id AS termId, | |
| esmd.mark_details AS markDetails, | |
| esmd.mark_history AS markHistory, | |
| esmd.total_supply_attempt_count AS supplyAttemptCount, | |
| esmd.total_mark AS totalMarkObtained, | |
| esmd.sgpa, | |
| esmd.grade, | |
| esmd.failed_status AS failedStatus, | |
| atm.properties->>'$.orderNo' AS termOrder, | |
| atm.name AS termName | |
| FROM | |
| ec_semester_mark_details esmd | |
| INNER JOIN academic_term atm ON | |
| atm.id = esmd.academic_term_id | |
| WHERE | |
| 1 = 1 | |
| $whereQuery ORDER BY atm.properties ->> '$.orderNo' ASC"; | |
| $termMarkDetails = $this->executeQueryForList($query); | |
| array_walk($termMarkDetails,function($term,$key)use($subjectsMarkDetails){ | |
| $term->markDetails = json_decode($term->markDetails); | |
| $term->markHistory = json_decode($term->markHistory); | |
| $term->subjects = array_filter( $subjectsMarkDetails, function($subject)use($term){ | |
| return ($term->groupsId == $subject->groupsId && $term->termId == $subject->termId && $term->studentId == $subject->studentId) ? true : false; | |
| }); | |
| uasort($term->subjects, function($a, $b) { | |
| return ($a->priority > $b->priority); | |
| }); | |
| $term->romanNumber = CommonUtil::convertNumberToRoman($term->termOrder); | |
| $term->creditPoint = array_sum(array_column($term->subjects,'creditPoint')); | |
| }); | |
| $whereQuery = ""; | |
| if(!empty($request->groupId)) { | |
| $groupIdString = is_array($request->groupId) ? implode("','",$request->groupId) : $request->groupId; | |
| $whereQuery .= " AND ecmd.groups_id IN ('$groupIdString') "; | |
| } | |
| if(!empty($request->studentId)) { | |
| $studentIdString = is_array($request->studentId) ? implode(",",$request->studentId) : $request->studentId; | |
| $whereQuery .= " AND ecmd.student_id IN ($studentIdString) "; | |
| } | |
| $query = "SELECT | |
| ecmd.groups_id AS groupsId, | |
| g.properties->>'$.programId' AS programId, | |
| ecmd.student_id AS studentId, | |
| ecmd.mark_details AS markDetails, | |
| ecmd.no_of_arrears AS arrears, | |
| ecmd.total_supply_attempt_count AS supplyAttemptCount, | |
| ecmd.percentage AS cgpaPercentage, | |
| ecmd.cgpa, | |
| ecmd.grade, | |
| ecmd.failed_status AS failedStatus, | |
| sa.studentName AS name, | |
| sa.studentPhone AS mobile, | |
| sa.studentFather AS fatherName, | |
| sa.abcId AS abcAccountId, | |
| sa.myImage, | |
| sa.studentGender AS gender, | |
| sa.studentBirthday AS dob, | |
| efn.folio_number as folioNumber, | |
| g.name AS batchName, | |
| g.properties ->> '$.description' as batchDescription, | |
| g.properties ->> '$.optionName' as batchOptionName, | |
| g.properties ->> '$.startYear' as admissionYear, | |
| spa.properties->>'$.registerNumber' AS regNo, | |
| spa.properties->>'$.rollNumber' AS rollNo, | |
| dept.deptName as deptName, | |
| dept.departmentDesc as departmentDesc, | |
| gm.properties, | |
| g.properties, | |
| p.name AS programName, | |
| d.name AS degreeName, | |
| GROUP_CONCAT(str.name) AS streamName, | |
| GROUP_CONCAT(str.properties->>'$.abbreviation') AS streamDesc, | |
| GROUP_CONCAT(str.properties->>'$.code') AS streamDescCode, | |
| ct.course_type AS courseType, | |
| ct.typeDesc AS courseTypeDesc, | |
| ct.typeName AS courseTypeName, | |
| ct.courseTypeID AS courseTypeId | |
| FROM | |
| ec_course_mark_details ecmd | |
| INNER JOIN `groups` g ON | |
| g.id = ecmd.groups_id | |
| INNER JOIN studentaccount sa ON | |
| sa.studentID = ecmd.student_id | |
| INNER JOIN student_program_account spa ON | |
| spa.student_id = sa.studentID | |
| INNER JOIN group_members gm ON | |
| gm.groups_id = g.id | |
| AND CAST(gm.members->>'$.studentId' AS CHAR) = spa.id | |
| INNER JOIN program p ON | |
| p.id = CAST(g.properties->>'$.programId' AS CHAR) | |
| INNER JOIN `degree` d ON | |
| d.id = p.degree_id | |
| INNER JOIN course_type ct ON | |
| ct.courseTypeID = p.course_type_id | |
| INNER JOIN `department` dept ON | |
| dept.deptID = CAST(g.properties ->> '$.departmentId' AS CHAR) | |
| LEFT JOIN stream str ON | |
| JSON_SEARCH( p.stream_id, 'one', str.id) IS NOT NULL | |
| LEFT JOIN ec_folio_number efn ON efn.student_id = ecmd.student_id AND efn.groups_id = ecmd.groups_id | |
| WHERE | |
| 1 = 1 | |
| $whereQuery | |
| GROUP BY p.id, sa.studentID"; | |
| $courseMarkDetails = $this->executeQueryForList($query); | |
| $programs = []; | |
| array_walk($courseMarkDetails,function($course,$key)use(&$programs,$termMarkDetails){ | |
| $course->markDetails = json_decode($course->markDetails); | |
| $course->cgpaInWords = CommonUtil::convertNumberToWords($course->cgpa); | |
| $course->academicTerms = array_filter( $termMarkDetails, function($term)use($course){ | |
| return ($term->studentId == $course->studentId) ? true : false; | |
| }); | |
| $course->academicTerms = call_user_func_array('array_merge', array_map( | |
| function ($key, $value) {return array($key => $value);}, | |
| array_column($course->academicTerms, "termId"), | |
| $course->academicTerms) | |
| ); | |
| uasort($course->academicTerms, function($a, $b) { | |
| return ($a->termOrder > $b->termOrder); | |
| }); | |
| $course->qrCodeLink = $this->generateQRcodeByStudentId($course->studentId,$course->groupId); | |
| $course->examMonthYear = date('F Y',strtotime($course->markDetails->latestExamYear."-".$course->markDetails->latestExamMonth."-01")); | |
| $course->creditPoint = array_sum(array_column($course->academicTerms,'creditPoint')); | |
| $programs[$course->programId]->students[$course->studentId] = $course; | |
| }); | |
| } catch (\Exception $e) { | |
| throw new ECCoreException(ECCoreException::ERROR_FETCHING,"Cannot fetch details! Please try again."); | |
| } | |
| return $programs; | |
| } | |
| /** | |
| * Consolidated Mark details | |
| * @param $request | |
| * @return Array $students | |
| */ | |
| public function getOverallMarkDetails(ConsolidatedMarkReportRequest $request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $collegeCodes = ["STTHOMAS","VIMALA"]; | |
| $COLLEGE_CODE = $request->COLLEGE_CODE; | |
| $COLLEGE_CODE = "STTHOMAS"; | |
| $students = []; | |
| if (!empty($request->examRegistrationId)) { | |
| $examRegObj = ConsolidatedMarkReportService::getInstance()->getExamRegistrationFromExamReg($request->examRegistrationId); | |
| if ($examRegObj->type == "REGULAR") { | |
| $request->examRegId = $examRegObj->examRegId; | |
| } | |
| else { | |
| $request->supplyRegId = $examRegObj->examRegId; | |
| } | |
| $request->considerSupplementary = false; | |
| } | |
| if (!empty($request->academicTermId)) { | |
| $request->semId = ConsolidatedMarkReportService::getInstance()->getSemIdFromAcademicTerm($request->academicTermId); | |
| } | |
| if (!empty($request->groupId)) { | |
| $request->batchId = implode(",",ConsolidatedMarkReportService::getInstance()->getBatchIdFromGroupId($request->groupId)); | |
| } | |
| if (!empty($request->academicPaperSubjectId)) { | |
| $request->subjectIds = ConsolidatedMarkReportService::getInstance()->getSubjectIdFromPaperSubject($request->academicPaperSubjectId); | |
| } | |
| if (!empty($request->degreeId)) { | |
| $request->batchId = implode(",",ConsolidatedMarkReportService::getInstance()->getBatchIdFromDegreeId($request->degreeId)); | |
| } | |
| // if (!empty($request->academicPaperSubjectId)) { | |
| // $request->examId = ConsolidatedMarkReportService::getInstance()->getExamDetails($request->assessmentId); | |
| // } | |
| try { | |
| $examDetails = $this->getStudentAssessmentMarkDetails($request); | |
| if ( !empty ( $examDetails ) ) { | |
| if ( $COLLEGE_CODE == "SCEK" ){ | |
| $studentDetails = ScekConsolidatedMarkService::getInstance()->getStudentsOverallMarkReport($request, $examDetails); | |
| } | |
| else{ | |
| $studentDetails = ConsolidatedMarkReportService::getInstance()->getStudentsOverallMarkReport($request, $examDetails); | |
| } | |
| foreach ($studentDetails as $studentId => $student) { | |
| $studentMarkDetails = new StudentMarkDetails(); | |
| $studentMarkDetails->id = $student->studentId; | |
| $studentMarkDetails->latestExamYear = $student->lastExamYear; | |
| $studentMarkDetails->latestExamMonth = $student->lastExamMonth; | |
| $studentMarkDetails->latestExamType = $student->lastExamReg; | |
| $studentMarkDetails->arrearCount = $student->totalArrears; | |
| $studentMarkDetails->supplyAttemptCount = $student->supplyAttemptCount; | |
| $studentMarkDetails->credit = $student->credit; | |
| $studentMarkDetails->creditGradePoint = $student->creditGradePoint; | |
| $studentMarkDetails->markObtained = $student->totalMarkObtained; | |
| $studentMarkDetails->totalMarks = $student->totalMark; | |
| $studentMarkDetails->isFailed = $student->isFailed; | |
| $studentMarkDetails->cgpa = $student->cgpa; | |
| $studentMarkDetails->gradePoint = $student->gradePoint; | |
| $studentMarkDetails->grade = $student->grade; | |
| $studentMarkDetails->class = $student->class; | |
| $studentMarkDetails->studentDetails = null; | |
| $studentMarkDetails->academicTerms = []; | |
| $studentDetails = new StudentDetails(); | |
| $studentDetails->id = $student->studentId; | |
| $studentDetails->name = $student->name; | |
| $studentDetails->academicYear = $student->batchStartYear; | |
| $studentDetails->registerNo = $student->regNo; | |
| $studentDetails->admissionNo = $student->admissionNo; | |
| $studentDetails->gender = $student->gender; | |
| $studentDetails->batchId = ConsolidatedMarkReportService::getInstance()->getGroupDetailsByBatch($student->batchId)->id; | |
| $studentDetails->batchName = $student->batchName; | |
| $studentDetails->courseTypeId = $student->courseTypeId; | |
| $studentDetails->courseType = $student->isPG ? "PG" : "UG"; | |
| $studentMarkDetails->studentDetails = $studentDetails; | |
| foreach ($student->semMarks as $semId => $semMarks) { | |
| $academicTermMark = new AcademicTermMark(); | |
| $academicTermMark->id = ConsolidatedMarkReportService::getInstance()->getAcademicTermDetails($semId)->id;; | |
| $academicTermMark->name = $semMarks->semName; | |
| $academicTermMark->order = ConsolidatedMarkReportService::getInstance()->getAcademicTermDetails($semId)->properties->order; | |
| $academicTermMark->latestExamYear = $semMarks->lastExamYear; | |
| $academicTermMark->latestExamMonth = $semMarks->lastExamMonth; | |
| $academicTermMark->latestExamType = $semMarks->lastExamReg; | |
| $academicTermMark->arrearCount = $semMarks->arrears; | |
| $academicTermMark->supplyAttemptCount = $semMarks->supplyAttemptCount; | |
| $academicTermMark->credit = $semMarks->credit; | |
| $academicTermMark->creditGradePoint = $semMarks->creditGradePoint; | |
| $academicTermMark->markObtained = $semMarks->totalMarks; | |
| $academicTermMark->totalMarks = $semMarks->examTotalMarks; | |
| $academicTermMark->isFailed = $semMarks->isFailed; | |
| $academicTermMark->sgpa = $semMarks->sgpa; | |
| $academicTermMark->gradePoint = $semMarks->gradePoint; | |
| $academicTermMark->grade = $semMarks->grade; | |
| $academicTermMark->class = $semMarks->class; | |
| $academicTermMark->registrationId = ConsolidatedMarkReportService::getInstance()->getExamRegistrationDetails($semMarks->regularExamRegId,"REGULAR")->id; | |
| $academicTermMark->subjects = []; | |
| foreach ($semMarks->subject as $subjectId => $subject) { | |
| $examSubject = new ExamSubject(); | |
| $examSubject->id = ConsolidatedMarkReportService::getInstance()->getExamPaperSubjectubjectDetails($subject->examAttendedBatchId,$semId,$subjectId)->id; | |
| $examSubject->name = $subject->subjectDesc; | |
| $examSubject->code = $subject->subjectName; | |
| $examSubject->syllabusCode = $subject->syllabusCode; | |
| $examSubject->isInternal = $subject->isInternal; | |
| $examSubject->internalMark = $subject->internalMark; | |
| $examSubject->internalMaxMark = $subject->internalMaxMark; | |
| $examSubject->isInternalFailed = $subject->isInternalFailed; | |
| $examSubject->isInternalAbsent = $subject->isInternalAbsent; | |
| $examSubject->attendance = $subject->isAbsent ? "ABSENT" : $subject->isMal ? "MAL" : "PRESENT"; | |
| $examSubject->isExternal = $subject->isInternalFailed; | |
| $examSubject->externalMark = $subject->isInternalAbsent; | |
| $examSubject->externalMaxMark = $subject->externalMaxMark; | |
| $examSubject->isExternalFailed = $subject->isExternalFailed; | |
| $examSubject->markObtained = $subject->totalMarkObtained; | |
| $examSubject->totalMarks = $subject->totalMark; | |
| $examSubject->markNeededToPass = $subject->markNeededToPass; | |
| $examSubject->isFailed = $subject->isFailed; | |
| $examSubject->registrationId = $academicTermMark->registrationId; | |
| $examSubject->isTheory = $subject->isTheory; | |
| $examSubject->examYear = $subject->examYear; | |
| $examSubject->examMonth = $subject->examMonth; | |
| $examSubject->grade = $subject->grade; | |
| $examSubject->class = $subject->className; | |
| $examSubject->creditxMark = $subject->creditxMark; | |
| $examSubject->markHistory = []; | |
| foreach ($subject->markHistory as $markHistoryType => $markHistory) { | |
| if($markHistoryType == "SUPPLY") { | |
| foreach ($markHistory->supplyMarks as $supplyMarksHistory){ | |
| $markHistoryObj = new MarkHistory(); | |
| $markHistoryObj->examMarkType = $markHistoryType; | |
| $markHistoryObj->examRegId = $supplyMarksHistory->id; | |
| $markHistoryObj->examRegistrationId = ConsolidatedMarkReportService::getInstance()->getExamRegistrationDetails($supplyMarksHistory->id,"SUPPLEMENTARY")->id; | |
| $markHistoryObj->examId = $supplyMarksHistory->examId; | |
| $markHistoryObj->assessmentId = ConsolidatedMarkReportService::getInstance()->getExamAssessmentDetails($supplyMarksHistory->examId)->id; | |
| $markHistoryObj->mark = $supplyMarksHistory->mark; | |
| $markHistoryObj->examMonth = $supplyMarksHistory->examMonth; | |
| $markHistoryObj->examYear = $supplyMarksHistory->examYear; | |
| $markHistoryObj->attendanceStatus = $supplyMarksHistory->isAbsent ? "ABSENT" : "PRESENT"; | |
| $markHistoryObj->resultStatus = $supplyMarksHistory->isFailed ? "FAILED" : "PASSED"; | |
| $markHistoryObj->examName = $supplyMarksHistory->supplyName; | |
| $examSubject->markHistory[] = $markHistoryObj; | |
| } | |
| } | |
| else{ | |
| $markHistoryObj = new MarkHistory(); | |
| $markHistoryObj->examMarkType = $markHistoryType; | |
| $markHistoryObj->examRegId = $markHistory->examRegId; | |
| $markHistoryObj->examRegistrationId = ConsolidatedMarkReportService::getInstance()->getExamRegistrationDetails($markHistory->examRegId,$markHistoryType)->id; | |
| $markHistoryObj->examId = $markHistory->examId; | |
| $markHistoryObj->assessmentId = ConsolidatedMarkReportService::getInstance()->getExamAssessmentDetails($markHistory->examId)->id; | |
| $markHistoryObj->mark = $markHistory->mark; | |
| $markHistoryObj->examMonth = $markHistory->month; | |
| $markHistoryObj->examYear = $markHistory->year; | |
| $markHistoryObj->attendanceStatus = $markHistory->isAbsent ? "ABSENT" : "PRESENT"; | |
| $markHistoryObj->resultStatus = $markHistory->isFailed ? "FAILED" : "PASSED"; | |
| $markHistoryObj->examName = $markHistory->name; | |
| $examSubject->markHistory[] = $markHistoryObj; | |
| } | |
| } | |
| $academicTermMark->subjects[$examSubject->id] = $examSubject; | |
| } | |
| $studentMarkDetails->academicTerms[$academicTermMark->id] = $academicTermMark; | |
| } | |
| $students[$studentMarkDetails->id] = $studentMarkDetails; | |
| } | |
| } | |
| else { | |
| throw new ECCoreException (ECCoreException::DATA_NOT_FOUND, "Exam data not for the request"); | |
| } | |
| } catch (\Exception $e) { | |
| throw new ECCoreException ($e->getCode(), $e->getMessage()); | |
| } | |
| return $students; | |
| } | |
| /** | |
| * Generate QR code | |
| * @param studentId $studentId | |
| * @return string QRcodeImageURL | |
| * @throws ECCoreException | |
| */ | |
| public function generateQRcodeByStudentId($studentId,$groupId) | |
| { | |
| $studentId = $this->realEscapeString($studentId); | |
| $groupId = $this->realEscapeString($groupId); | |
| // $QUICK is the secret key defined in the conf.php | |
| global $QUICK; | |
| try { | |
| $studentDetailsSQL = "SELECT | |
| cc.code, | |
| sa.studentName, | |
| sa.studentBirthday | |
| FROM | |
| coursetype_code cc | |
| INNER JOIN `groups` g ON | |
| g.id = cc.groups_id | |
| AND cc.courseTypeID = CAST(g.properties->>'$.courseTypeId' AS CHAR) | |
| 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 studentaccount sa ON | |
| sa.studentID = CAST(spa.student_id AS CHAR) | |
| WHERE | |
| sa.studentID = '$studentId' AND g.id='$groupId'"; | |
| $studentDetails = $this->executeQueryForObject($studentDetailsSQL); | |
| $batchCode = $studentDetails->code; | |
| $DOB = explode("-", $studentDetails->studentBirthday); | |
| $date = strtotime($studentDetails->studentBirthday); | |
| $DOBMonth = date("m",$date); | |
| $DOBDay = date("d",$date); | |
| $year = date("Y", $date); | |
| $smallYear = date("y", $date); | |
| $studentDOB = trim($smallYear . $DOBMonth . $DOBDay); | |
| $studentNameInQR = $studentDetails->studentName; | |
| $studentNameInQR = str_replace(" ", "", trim($studentNameInQR)); | |
| $studentNameInQR = str_replace(".", "", $studentNameInQR); | |
| $combineString = $studentNameInQR . $studentDOB . $batchCode; | |
| $ciphertext = hash_hmac('sha256', $combineString, $QUICK); | |
| $ciphertext_base64 = base64_encode($ciphertext); | |
| $allString = $combineString . "|" . $ciphertext_base64; | |
| return "https://chart.googleapis.com/chart?chs=100x100&cht=qr&chl=$allString&choe=UTF-8"; | |
| } catch (\Exception $e) { | |
| throw new ECCoreException (ECCoreException::DATA_NOT_FOUND, "QR Code can't be generated"); | |
| } | |
| } | |
| public function getMarkListSubjectCategories($groupId) | |
| { | |
| $groupId = $this->realEscapeString($groupId); | |
| try { | |
| $sql = "SELECT | |
| emcs.name AS displayName, | |
| emcs.properties->>'$.isSecondLanguage' AS secondLangFlag, | |
| emcs.properties->>'$.isOpenCourse' AS openCourseFlag, | |
| emcs.properties->>'$.priority' AS priority, | |
| emcs.subject_category_id AS subjectCategoryId, | |
| sc.code, | |
| sc.subjectcatName AS subjectCatName | |
| FROM | |
| ec_marklist_categorize_subjects emcs | |
| INNER JOIN subject_category sc ON | |
| sc.subjectcatID = emcs.subject_category_id | |
| WHERE | |
| groups_id ='$groupId'"; | |
| $markListSubjectCategories = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ECCoreException (ECCoreException::DATA_NOT_FOUND, "QR Code can't be generated"); | |
| } | |
| return $markListSubjectCategories; | |
| } | |
| /** | |
| *get Display Filter Settings | |
| * @return string settings | |
| * @throws ECCoreException | |
| */ | |
| public function getDisplayFilterSettings(){ | |
| try { | |
| $searchRuleRequest = new SearchRuleRequest; | |
| $searchRuleRequest->name = "FINAL_CONSOLIDATED_MARKS_CARD_ADVANCED_FILTERS"; | |
| $marksCardFilterSettings = reset(RuleService::getInstance()->searchRule($searchRuleRequest))->rule; | |
| $response = new \stdClass; | |
| $filterSettings = new \stdClass; | |
| $filterSettings->isMarkListDate = true; | |
| $filterSettings->markListDate = date("d-m-Y"); | |
| $filterSettings->isDisplayStudentPhoto = true; | |
| $filterSettings->displayStudentPhoto = true; | |
| $filterSettings->isDisplayHeading = true; | |
| $filterSettings->displayHeading = true; | |
| $filterSettings->isDisplaySignatures = true; | |
| $filterSettings->displaySignatures = true; | |
| $filterSettings->isDisplayFooterDetails = true; | |
| $filterSettings->displayFooterDetails = true; | |
| $filterSettings->isDisplayAffiliationDetails = true; | |
| $filterSettings->displayAffiliationDetails = true; | |
| $filterSettings->isDisplayExamControllerLabel = true; | |
| $filterSettings->displayExamControllerLabel = true; | |
| $filterSettings->isDisplayAssistantExamControllerLabel = true; | |
| $filterSettings->displayAssistantExamControllerLabel = true; | |
| $filterSettings->isDisplayPrincipalLabel = true; | |
| $filterSettings->displayPrincipalLabel = true; | |
| $filterSettings->isChangeHeadingAsOfficialTranscript = true; | |
| $filterSettings->changeHeadingAsOfficialTranscript = true; | |
| $filterSettings->isDisplayDisclaimer = true; | |
| $filterSettings->displayDisclaimer = true; | |
| if ( $marksCardFilterSettings->isShowMinorMarksCard ){ | |
| $filterSettings->isShowMinorMarksCard = true; | |
| } | |
| if ( $marksCardFilterSettings->isShowHonoursMarksCard ){ | |
| $filterSettings->isShowHonoursMarksCard = true; | |
| } | |
| $response->displayFilterSetting = $filterSettings; | |
| return $response; | |
| } | |
| catch (\Exception $e) { | |
| throw new ECCoreException (ECCoreException::DATA_NOT_FOUND, "QR Code can't be generated"); | |
| } | |
| } | |
| public function checkWhetherPropertyEditableForTheAcademicPaper($request) | |
| { | |
| $searchRuleRequest = new SearchRuleRequest; | |
| $searchRuleRequest->name = "ACADEMIC_PAPER_PROPERTIES_SETTINGS"; | |
| $examAcademicPaperSettings = reset(RuleService::getInstance()->searchRule($searchRuleRequest))->rule; | |
| // this condition for avoid checking is editable academic paper properties (return always false) | |
| if($examAcademicPaperSettings->isAvoidCheckingForIsEditableInAcademicPaperProperties == '1'){ | |
| return false; | |
| } | |
| $request = $this->realEscapeObject($request); | |
| $sql = "SELECT | |
| DISTINCT eers.cm_academic_paper_subjects_id | |
| FROM | |
| oe_student_total_mark ostm | |
| INNER JOIN ec_exam_registration_subject eers ON | |
| eers.am_assessment_id = ostm.am_assessment_id | |
| INNER JOIN cm_academic_paper_subjects caps ON | |
| caps.id = eers.cm_academic_paper_subjects_id | |
| WHERE | |
| caps.cm_academic_paper_id ='$request->academicPaperId'"; | |
| try { | |
| $studentSubjects = $this->executeQueryForList($sql); | |
| if(empty($studentSubjects)){ | |
| return false; | |
| } | |
| else{ | |
| return true; | |
| } | |
| } catch (\Exception $e) { | |
| throw new ECCoreException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * @param $request | |
| */ | |
| public function getProvisionalMarksCard($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $template = $this->getMarkCardTemplate("EC_PROVISIONAL_MARK_CARD"); | |
| $consolidatedRequest = new MarkReportRequest(); | |
| $consolidatedRequest->studentId = $request->studentId; | |
| $consolidatedRequest->groupId = $request->groupId; | |
| $consolidatedRequest->template = $template; | |
| $consolidatedRequest->filterSettings = $request->filterSettings; | |
| $marksCardData = ProvisionalMarksCardGenerator::getInstance()->getFinalConsolidatedMarksCard($consolidatedRequest); | |
| return $marksCardData; | |
| } | |
| /** | |
| * get batch wise subject category | |
| * @param $request | |
| */ | |
| public function getGroupSubjectCategoryDetails($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $whereQuery = ""; | |
| if(!empty($request->groupId)) { | |
| $groupIdString = is_array($request->groupId) ? implode("','",$request->groupId) : $request->groupId; | |
| $whereQuery .= " AND eerb.groups_id IN ('$groupIdString') "; | |
| } | |
| if(!empty($request->academicTermId)) { | |
| $academicTermIdString = is_array($request->academicTermId) ? implode(",",$request->academicTermId) : $request->academicTermId; | |
| $whereQuery .= " AND eerb.properties->>'$.academicTermId' IN ($academicTermIdString) "; | |
| } | |
| try{ | |
| $sql = "SELECT | |
| eerb.groups_id as groupId, | |
| eerb.properties->>'$.academicTermId' as academicTermId, | |
| caps.id AS academicPaperId, | |
| emcs.subject_category_id AS subjectcatId, | |
| emcs.properties->>'$.subjectCatGroupId' AS subjectCatGroupId, | |
| scg.name AS subCatGroupName, | |
| sc.code, | |
| sc.subjectcatName, | |
| sc.subjectcatCode, | |
| emcs.properties->>'$.priority' AS subjectcatPriority, | |
| sc.use_bring_value AS useBringValue, | |
| sc.parentID AS parentId, | |
| sc.canShow | |
| 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 caps ON | |
| caps.id = eers.cm_academic_paper_subjects_id | |
| INNER JOIN ec_marklist_categorize_subjects emcs ON | |
| emcs.subject_category_id = caps.properties->>'$.categoryId' | |
| AND emcs.groups_id = eerb.groups_id | |
| INNER JOIN subject_category sc ON | |
| sc.subjectcatID = emcs.subject_category_id | |
| LEFT JOIN ec_subject_category_group scg ON | |
| scg.id = emcs.properties->>'$.subjectCatGroupId' | |
| WHERE | |
| eer.`type` ='REGULAR'"; | |
| $subjectCategory = $this->executeQueryForList($sql.$whereQuery); | |
| }catch (\Exception $e){ | |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); | |
| } | |
| $categoryDetails = []; | |
| foreach ($subjectCategory as $subjectCat) { | |
| $categoryDetails[$subjectCat->academicPaperId] = $subjectCat; | |
| } | |
| return $categoryDetails; | |
| } | |
| /** | |
| * get batch wise subject category | |
| * @param $request | |
| */ | |
| public function getBatchSubjectsInstitutionalAverge($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $whereQuery = ""; | |
| if(!empty($request->groupId)) { | |
| $groupIdString = is_array($request->groupId) ? implode("','",$request->groupId) : $request->groupId; | |
| $whereQuery .= " AND eerb.groups_id IN ('$groupIdString') "; | |
| } | |
| if(!empty($request->academicTermId)) { | |
| $academicTermIdString = is_array($request->academicTermId) ? implode("','",$request->academicTermId) : $request->academicTermId; | |
| $whereQuery .= " AND eerb.properties->>'$.academicTermId' IN ('$academicTermIdString') "; | |
| } | |
| try{ | |
| $sql = "SELECT | |
| eerb.groups_id as groupId, | |
| eerb.properties->>'$.academicTermId' as academicTermId, | |
| eers.cm_academic_paper_subjects_id AS academicPaperId, | |
| eers.properties->>'$.institutionalAverage' AS institutionalAverage | |
| 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 | |
| WHERE | |
| eer.`type` ='REGULAR'"; | |
| $subjectCategory = $this->executeQueryForList($sql.$whereQuery); | |
| }catch (\Exception $e){ | |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); | |
| } | |
| $categoryDetails = []; | |
| foreach ($subjectCategory as $subjectCat) { | |
| $categoryDetails[$subjectCat->academicPaperId] = $subjectCat; | |
| } | |
| return $categoryDetails; | |
| } | |
| /** | |
| * get student semester mark details | |
| * @param $searchRequest | |
| * @return $semesterDetails | |
| */ | |
| public function getStudentSemesterMarkDetails($searchRequest) { | |
| $searchRequest = $this->realEscapeObject($searchRequest); | |
| try{ | |
| $whereQuery = ""; | |
| $orderBy = " ORDER BY CAST(act.properties ->> '$.orderNo' AS UNSIGNED) ASC"; | |
| if(!empty($searchRequest->groupId)) { | |
| $whereQuery .= " AND g.id = '$searchRequest->groupId'"; | |
| } | |
| if(!empty($searchRequest->studentId)) { | |
| $whereQuery .= " AND esar.student_id = '$searchRequest->studentId'"; | |
| } | |
| if(!empty($searchRequest->academicTermId)) { | |
| $whereQuery .= " AND eerb.academicTermId = '$searchRequest->academicTermId'"; | |
| } | |
| $query = "SELECT DISTINCT | |
| act.id as id, | |
| act.name as name, | |
| act.properties ->> '$.orderNo' as orderNo, | |
| esmd.mark_details as markDetails, | |
| esmd.mark_history AS markHistory, | |
| esmd.sgpa as sgpa, | |
| esmd.grade as grade, | |
| esmd.failed_status as failedStatus, | |
| eers.cm_academic_paper_subjects_id as academicPaperSubjectId, | |
| esmdsubcon.failed_status as sujectFailedStatus, | |
| esmdsubcon.mark_history as subjectMarkHistory, | |
| esmdsubcon.mark_details as subjectMarkDetails | |
| FROM | |
| `groups` g | |
| INNER JOIN program p | |
| ON p.id = g.properties->>'$.programId' | |
| 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 | |
| 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 AND | |
| CAST(esar.properties ->> '$.registrationStatus' AS CHAR) = 'REGISTERED' AND | |
| CAST(esar.properties ->> '$.feeStatus' AS CHAR) = 'PAID' | |
| INNER JOIN academic_term act ON | |
| act.id = CAST(eerb.properties ->> '$.academicTermId'AS CHAR) | |
| INNER JOIN ec_semester_mark_details esmd ON | |
| esmd.groups_id = eerb.groups_id AND esmd.academic_term_id = act.id AND esmd.student_id = esar.student_id | |
| 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 = esar.student_id | |
| WHERE 1=1 "; | |
| $semesterDetails = $this->executeQueryForList($query.$whereQuery.$orderBy); | |
| $semesters = []; | |
| foreach($semesterDetails as $semester){ | |
| $semester->markDetails = json_decode($semester->markDetails); | |
| $semester->subjectMarkDetails = json_decode($semester->subjectMarkDetails); | |
| $semesters[$semester->id]->id = $semester->id; | |
| $semesters[$semester->id]->name = $semester->name; | |
| $semesters[$semester->id]->orderNo = $semester->orderNo; | |
| $semesters[$semester->id]->sgpa = $semester->sgpa; | |
| $semesters[$semester->id]->markDetails = $semester->markDetails; | |
| $semesters[$semester->id]->markHistory = json_decode($semester->markHistory); | |
| $semesters[$semester->id]->subjects[$semester->academicPaperSubjectId]->id = $semester->academicPaperSubjectId; | |
| $semesters[$semester->id]->subjects[$semester->academicPaperSubjectId]->failedStatus = $semester->sujectFailedStatus; | |
| $semesters[$semester->id]->subjects[$semester->academicPaperSubjectId]->markDetails = $semester->subjectMarkDetails; | |
| $semesters[$semester->id]->subjects[$semester->academicPaperSubjectId]->subjectMarkHistory = json_decode($semester->subjectMarkHistory); | |
| } | |
| $semesters = array_values($semesters); | |
| array_walk($semesters, function($semester){ | |
| $semester->subjects = array_values($semester->subjects); | |
| }); | |
| } | |
| catch (\Exception $e){ | |
| throw new ExamControllerException($e->getCode(),$e->getMessage()); | |
| } | |
| return $semesters; | |
| } | |
| /** | |
| * get Consoliidated Student Data For academics Sem term registration | |
| * @param $request | |
| */ | |
| public function getConsoliidatedStudentData($request){ | |
| $request = $this->realEscapeObject($request); | |
| try { | |
| // to get student exam registration and block status | |
| $studentExamRegistrationReq = new \stdClass; | |
| $studentExamRegistrationReq->studentId = $request->studentId; | |
| $studentExamRegistrationReq->groupId = $request->groupId; | |
| $studentExamRegistrations = reset(StudentExamRegistrationService::getInstance()->getStudentExamRegistrationDetails($studentExamRegistrationReq)); | |
| $notPublishedExamRegIds = []; | |
| foreach($studentExamRegistrations->exams as $key => $examRegistration){ | |
| // this case to check these cases | |
| // 1. result is published | |
| // 2.student wise with held status | |
| // 3.result blocking | |
| if( !($examRegistration->batchProperties->isResultPublished ) || | |
| (($examRegistration->batchProperties->isResultPublished) && (strtotime($examRegistration->batchProperties->publishingStartDate) > strtotime(date("Y-m-d H:i")))) || | |
| ( $examRegistration->isResultBlocked) || | |
| ($examRegistration->isResultWithHeld)) { | |
| $notPublishedExamRegIds[] = $examRegistration->examRegistrationId; | |
| } | |
| } | |
| $consolidatedStudentRequest = new \stdClass; | |
| $consolidatedStudentRequest->studentId = $request->studentId; | |
| $consolidatedStudentRequest->groupId = $request->groupId; | |
| if(empty( $consolidatedStudentRequest->studentId)){ | |
| throw new ECCoreException(ECCoreException::EMPTY_PARAMETERS,"Invaild Request."); | |
| } | |
| $whereQuery = ""; | |
| if(!empty($consolidatedStudentRequest->groupId)) { | |
| $groupIdString = is_array($consolidatedStudentRequest->groupId) ? implode("','",$consolidatedStudentRequest->groupId) : $consolidatedStudentRequest->groupId; | |
| $whereQuery .= " AND g.id IN ('$groupIdString') "; | |
| } | |
| if(!empty($consolidatedStudentRequest->studentId)) { | |
| $studentIdString = is_array($consolidatedStudentRequest->studentId) ? implode(",",$consolidatedStudentRequest->studentId) : $consolidatedStudentRequest->studentId; | |
| $whereQuery .= " AND ecsmd.student_id IN ($studentIdString) "; | |
| } | |
| $query = "SELECT DISTINCT | |
| ecsmd.student_id AS studentId, | |
| ecsmd.groups_id AS groupsId, | |
| atm.id AS termId, | |
| ecsmd.cm_academic_paper_subjects_id AS paperSubjetId, | |
| ecsmd.mark_details AS markDetails, | |
| ecsmd.no_of_chances_taken AS noOfChancesTaken, | |
| ecsmd.total_mark AS totalMarkObtained, | |
| ecsmd.class, | |
| ecsmd.grade, | |
| ecsmd.failed_status AS failedStatus, | |
| ecsmd.mark_history AS markHistory, | |
| s.id as subjectId, | |
| s.code AS code, | |
| s.name AS name, | |
| IF(caps.properties ->> '$.classType' = 'THEORY',1,0) AS isTheory, | |
| IF(esar.properties->>'$.syllabusSubType' = 'MOOC',1,0) AS isMoocSubject, | |
| esar.properties->>'$.studentAttendanceStatus' AS studentAttendanceStatus, | |
| sc.subjectcatID AS categoryId, | |
| sc.subjectcatName AS categoryName, | |
| sc.subjectcatPriority AS categoryPriority, | |
| sc.subjectcatCode AS subjectCategoryCode, | |
| sc.parentID AS categoryParentId, | |
| sc.use_bring_value AS useBringValue, | |
| cc.categoryCode, | |
| cs.`type` as syllabusType, | |
| cs.id as syllabusId, | |
| cclo.name as slot | |
| FROM | |
| ec_consolidated_subject_mark_details ecsmd | |
| INNER JOIN student_program_account spa | |
| ON spa.student_id = ecsmd.student_id | |
| INNER JOIN `groups` g ON | |
| g.id = ecsmd.groups_id | |
| INNER JOIN cm_academic_paper_subjects caps ON | |
| caps.id = ecsmd.cm_academic_paper_subjects_id | |
| INNER JOIN cm_academic_paper cap ON | |
| cap.id = caps.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 academic_term atm ON | |
| atm.id = csats.academic_term_id | |
| INNER JOIN v4_ams_subject s ON | |
| s.id = caps.ams_subject_id | |
| INNER JOIN ec_exam_registration_subject eers ON | |
| eers.cm_academic_paper_subjects_id = caps.id | |
| INNER JOIN ec_student_assessment_registration esar ON | |
| esar.student_id = ecsmd.student_id AND | |
| esar.am_assessment_id = eers.am_assessment_id AND | |
| esar.ec_exam_registration_type = 'REGULAR' | |
| LEFT JOIN subject_category sc ON | |
| sc.subjectcatID = caps.properties->>'$.categoryId' | |
| LEFT JOIN categoryCode cc ON | |
| cc.subject_category_id = caps.properties->>'$.subjectTypeId' | |
| AND cc.subject_category_id = sc.subjectcatID | |
| AND cc.course_type_id = CAST(g.properties->>'$.courseTypeId' AS CHAR) | |
| LEFT JOIN cm_common_list_object cclo ON | |
| cap.slot_id = cclo.id AND cclo.type = 'SLOT' | |
| WHERE | |
| 1 = 1 AND ecsmd.is_active = 1 | |
| $whereQuery ORDER BY cclo.name ASC "; | |
| $subjectsMarkDetails = $this->executeQueryForList($query); | |
| array_walk($subjectsMarkDetails,function($subject,$key) use($request){ | |
| $subject->markDetails = json_decode($subject->markDetails); | |
| $subject->markHistory = json_decode($subject->markHistory); | |
| // $subject->markHistory = ""; | |
| }); | |
| // this case to handle publish exam registration cases | |
| foreach($subjectsMarkDetails as $subjectKey => $subject){ | |
| $subject->credit = $subject->markDetails->credit; | |
| $subject->excludeSubjectFromTotal = $subject->markDetails->excludeSubjectFromTotal; | |
| $subject->markHistory = array_filter($subject->markHistory,function($value) use($notPublishedExamRegIds) { | |
| return !(in_array($value->examRegistrationId,$notPublishedExamRegIds)); | |
| }); | |
| foreach($subject->markHistory as $markHistory){ | |
| if($markHistory->hasRevaluationMark && in_array($markHistory->revaluationId,$notPublishedExamRegIds)){ | |
| $markHistory->failedStatus = $markHistory->withoutRevaluationIsFailed == 1 ? "FAILED" : "PASSED"; | |
| $markHistory->isFailed = $markHistory->failedStatus; | |
| $markHistory->grade = $markHistory->withoutRevaluationGrade; | |
| } | |
| } | |
| $supplyHistory = array_search("SUPPLY", array_column( $subject->markHistory, "examMarkType")); | |
| if($supplyHistory || $supplyHistory === 0){ | |
| $subject->supplyAttempted = 1; | |
| } | |
| $regularHistory = array_filter($subject->markHistory,function($value){ | |
| return $value->examMarkType == "REGULAR"; | |
| }); | |
| if(empty($subject->markHistory) || empty($regularHistory)){ | |
| unset($subjectsMarkDetails[$subjectKey]); | |
| } | |
| usort($subject->markHistory, function($a, $b) { | |
| return ($a->examYear."-".date("m", mktime(0, 0, 0, (int)$a->examMonth, 10))."-".$a->examMarkType) < ($b->examYear."-".date("m", mktime(0, 0, 0, (int)$b->examMonth, 10))."-".$b->examMarkType); | |
| }); | |
| $subject->currentMarkHistory = reset($subject->markHistory); | |
| if($subject->currentMarkHistory->attendanceStatus == 'ABSENT'){ | |
| foreach ($subject->markHistory AS $subjectHistory){ | |
| if(($subjectHistory->attendanceStatus == 'PRESENT' && $subjectHistory->studentAttendanceStatus != 'FE') || ($subjectHistory->examMarkType == 'REGULAR' && $subjectHistory->studentAttendanceStatus != 'FE')){ | |
| $subject->currentMarkHistory = $subjectHistory; | |
| break; | |
| } | |
| } | |
| } | |
| $subject->markDetails = $subject->currentMarkHistory; | |
| $subject->failedStatus = $subject->markDetails->resultStatus; | |
| $subject->grade = $subject->markDetails->grade; | |
| $subject->examMonth = $subject->markDetails->examMonth; | |
| $subject->examYear = $subject->markDetails->examYear; | |
| $subject->monthName = date("F", mktime(0, 0, 0, (int)$subject->examMonth, 10)); | |
| $subject->syllabusType = $subject->syllabusType; | |
| $subject->syllabusId = $subject->syllabusId; | |
| $subject->markDetails->credit = $subject->credit; | |
| $subject->earnedCredit = $subject->failedStatus == 'FAILED' ? 0 : (int)$subject->markDetails->credit; | |
| if ( $subject->markDetails->studentAttendanceStatus == 'FE' && $request->considerFeStudentGradeChange == 1){ | |
| $subject->grade = 'FE'; | |
| $subject->examMonth = ''; | |
| $subject->examYear = ''; | |
| $subject->monthName = ''; | |
| $subject->credit = ''; | |
| } | |
| // if subject exclude from total then the grade become P or F | |
| if( $subject->excludeSubjectFromTotal == 1){ | |
| $subject->grade = $subject->failedStatus == 'PASSED' ? 'P' : 'F'; | |
| } | |
| } | |
| $whereQuery = ""; | |
| if(!empty($consolidatedStudentRequest->groupId)) { | |
| $groupIdString = is_array($consolidatedStudentRequest->groupId) ? implode("','",$consolidatedStudentRequest->groupId) : $consolidatedStudentRequest->groupId; | |
| $whereQuery .= " AND esmd.groups_id IN ('$groupIdString') "; | |
| } | |
| if(!empty($consolidatedStudentRequest->studentId)) { | |
| $studentIdString = is_array($consolidatedStudentRequest->studentId) ? implode(",",$consolidatedStudentRequest->studentId) : $consolidatedStudentRequest->studentId; | |
| $whereQuery .= " AND esmd.student_id IN ($studentIdString) "; | |
| } | |
| $query = "SELECT | |
| esmd.groups_id AS groupsId, | |
| esmd.student_id AS studentId, | |
| esmd.academic_term_id AS termId, | |
| esmd.mark_details AS markDetails, | |
| esmd.mark_history AS markHistory, | |
| esmd.total_supply_attempt_count AS supplyAttemptCount, | |
| esmd.total_mark AS totalMarkObtained, | |
| esmd.sgpa, | |
| esmd.grade, | |
| esmd.failed_status AS failedStatus, | |
| atm.properties->>'$.orderNo' AS termOrder, | |
| atm.name AS termName | |
| FROM | |
| ec_semester_mark_details esmd | |
| INNER JOIN academic_term atm ON | |
| atm.id = esmd.academic_term_id | |
| WHERE | |
| 1 = 1 | |
| $whereQuery"; | |
| $termMarkDetails = $this->executeQueryForList($query); | |
| array_walk($termMarkDetails,function($term,$key)use($subjectsMarkDetails){ | |
| $term->markDetails = json_decode($term->markDetails); | |
| $term->markHistory = json_decode($term->markHistory); | |
| $term->subjects = array_filter( $subjectsMarkDetails, function($subject)use($term){ | |
| return ($term->groupsId == $subject->groupsId && $term->termId == $subject->termId && $term->studentId == $subject->studentId) ? true : false; | |
| }); | |
| // uasort($term->subjects, function($a, $b) { | |
| // return ($a->slot < $b->slot); | |
| // }); | |
| $term->earnedCredit = array_sum(array_column($term->subjects,'earnedCredit')); | |
| }); | |
| // this case to handle publish exam registration | |
| foreach($termMarkDetails as $termKey => $term){ | |
| $term->markHistory = array_filter($term->markHistory,function($value) use($notPublishedExamRegIds) { | |
| return !(in_array($value->examRegistrationId,$notPublishedExamRegIds)); | |
| }); | |
| foreach($term->markHistory as $markHistory){ | |
| if( in_array($markHistory->revaluationId,$notPublishedExamRegIds)){ | |
| $markHistory->failedStatus = $markHistory->withoutRevaluationFailedStatus; | |
| $markHistory->sgpa = $markHistory->withoutRevaluationsgpa; | |
| } | |
| } | |
| $regularTermHistory = array_filter($term->markHistory,function($value){ | |
| return $value->historyType == "REGULAR"; | |
| }); | |
| if(empty($term->markHistory) || empty($regularTermHistory)){ | |
| unset($termMarkDetails[$termKey]); | |
| } | |
| usort($term->markHistory, function($a, $b) { | |
| return ($a->examYear."-".date("m", mktime(0, 0, 0, (int)$a->examMonth, 10))."-".$a->historyType) < ($b->examYear."-".date("m", mktime(0, 0, 0, (int)$b->examMonth, 10))."-".$b->historyType); | |
| }); | |
| $term->currentMarkHistory = reset($term->markHistory); | |
| $term->markDetails = $term->currentMarkHistory; | |
| $term->failedStatus = $term->markDetails->failedStatus; | |
| $term->sgpa = $term->markDetails->sgpa; | |
| } | |
| $whereQuery = ""; | |
| if(!empty($consolidatedStudentRequest->groupId)) { | |
| $groupIdString = is_array($consolidatedStudentRequest->groupId) ? implode("','",$consolidatedStudentRequest->groupId) : $consolidatedStudentRequest->groupId; | |
| $whereQuery .= " AND ecmd.groups_id IN ('$groupIdString') "; | |
| } | |
| if(!empty($consolidatedStudentRequest->studentId)) { | |
| $studentIdString = is_array($consolidatedStudentRequest->studentId) ? implode(",",$consolidatedStudentRequest->studentId) : $consolidatedStudentRequest->studentId; | |
| $whereQuery .= " AND ecmd.student_id IN ($studentIdString) "; | |
| } | |
| $query = "SELECT | |
| ecmd.groups_id AS groupsId, | |
| g.properties->>'$.programId' AS programId, | |
| ecmd.student_id AS studentId, | |
| ecmd.mark_details AS markDetails, | |
| ecmd.no_of_arrears AS arrears, | |
| ecmd.total_supply_attempt_count AS supplyAttemptCount, | |
| ecmd.percentage AS cgpaPercentage, | |
| ecmd.cgpa, | |
| ecmd.grade, | |
| ecmd.failed_status AS failedStatus, | |
| sa.studentName AS name, | |
| sa.myImage, | |
| sa.studentGender AS gender, | |
| sa.studentBirthday AS dob, | |
| g.name AS batchName, | |
| g.properties ->> '$.description' as batchDescription, | |
| g.properties ->> '$.optionName' as batchOptionName, | |
| g.properties ->> '$.startYear' as admissionYear, | |
| spa.properties->>'$.registerNumber' AS regNo, | |
| spa.properties->>'$.rollNumber' AS rollNo, | |
| gm.properties, | |
| g.properties, | |
| p.name AS programName, | |
| d.name AS degreeName, | |
| GROUP_CONCAT(str.name) AS streamName, | |
| GROUP_CONCAT(str.properties->>'$.abbreviation') AS streamDesc, | |
| ct.course_type AS courseType, | |
| ct.courseTypeID AS courseTypeId | |
| FROM | |
| ec_course_mark_details ecmd | |
| INNER JOIN `groups` g ON | |
| g.id = ecmd.groups_id | |
| INNER JOIN studentaccount sa ON | |
| sa.studentID = ecmd.student_id | |
| INNER JOIN student_program_account spa ON | |
| spa.student_id = sa.studentID | |
| INNER JOIN group_members gm ON | |
| gm.groups_id = g.id | |
| AND CAST(gm.members->>'$.studentId' AS CHAR) = spa.id | |
| INNER JOIN program p ON | |
| p.id = CAST(g.properties->>'$.programId' AS CHAR) | |
| INNER JOIN `degree` d ON | |
| d.id = p.degree_id | |
| 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 | |
| 1 = 1 | |
| $whereQuery | |
| GROUP BY p.id, sa.studentID"; | |
| $courseMarkDetails = $this->executeQueryForList($query); | |
| $programs = []; | |
| array_walk($courseMarkDetails,function($course,$key)use(&$programs,$termMarkDetails){ | |
| $course->markDetails = json_decode($course->markDetails); | |
| $course->cgpaInWords = CommonUtil::convertNumberToWords($course->cgpa); | |
| $course->academicTerms = array_filter( $termMarkDetails, function($term)use($course){ | |
| return ($term->studentId == $course->studentId) ? true : false; | |
| }); | |
| $course->academicTerms = call_user_func_array('array_merge', array_map( | |
| function ($key, $value) {return array($key => $value);}, | |
| array_column($course->academicTerms, "termId"), | |
| $course->academicTerms) | |
| ); | |
| uasort($course->academicTerms, function($a, $b) { | |
| return ($a->termOrder > $b->termOrder); | |
| }); | |
| $course->academicTerms = array_values($course->academicTerms); | |
| $course->earnedCredit = array_sum(array_column($course->academicTerms,'earnedCredit')); | |
| $course->examYear =$course->markDetails->latestExamYear; | |
| $course->examMonth =$course->markDetails->latestExamMonth; | |
| $programs[$course->programId]->students[$course->studentId] = $course; | |
| }); | |
| } catch (\Exception $e) { | |
| throw new ECCoreException(ECCoreException::ERROR_FETCHING,"Cannot fetch details! Please try again."); | |
| } | |
| return $programs; | |
| } | |
| /** | |
| * block batch for depromotion | |
| * @param $request | |
| */ | |
| public function blockBatchForDepromotion($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $sql = "SELECT | |
| eer.id | |
| FROM | |
| ec_exam_registration_batch eerb | |
| INNER JOIN `groups` g ON | |
| g.id = eerb.groups_id AND | |
| g.academic_term_id = eerb.academicTermId | |
| INNER JOIN ec_exam_registration eer ON | |
| eer.id = eerb.ec_exam_registration_id | |
| WHERE | |
| eer.trashed IS NULL AND | |
| g.id = '$request->groupId'"; | |
| try { | |
| $exambatches = $this->executeQueryForList($sql); | |
| if(empty($exambatches)){ | |
| return false; | |
| } | |
| else{ | |
| return true; | |
| } | |
| } catch (\Exception $e) { | |
| throw new ECCoreException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * Get current fe student details | |
| * @param $request | |
| * @return $feStudentsList | |
| */ | |
| public function getStudentCurrentFeSubjects($request){ | |
| $request = $this->realEscapeObject($request); | |
| $feStudentsList = []; | |
| $whereQueryRegular = ""; | |
| $whereQuerySupply = ""; | |
| if(!empty($request->studentProgramAccountId)) { | |
| $studentProgramAccountIdString = is_array($request->studentProgramAccountId) ? implode(",",$request->studentProgramAccountId) : $request->studentProgramAccountId; | |
| $whereQueryRegular .= " AND spa.id IN ($studentProgramAccountIdString) "; | |
| $whereQuerySupply .= " AND spa2.id IN ($studentProgramAccountIdString) "; | |
| } | |
| $query = "SELECT eers.cm_academic_paper_subjects_id , eerb.academicTermId, spa.id | |
| FROM | |
| ec_student_assessment_registration esar | |
| INNER JOIN student_program_account spa ON | |
| spa.student_id = esar.student_id | |
| INNER JOIN ec_exam_registration_subject eers ON | |
| eers.am_assessment_id = esar.am_assessment_id | |
| 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 | |
| WHERE | |
| esar.properties->>'$.studentAttendanceStatus' = 'FE' AND | |
| esar.properties->>'$.registrationStatus' ='NOT_REGISTERED' | |
| $whereQueryRegular AND | |
| eer.trashed IS NULL | |
| AND (eers.cm_academic_paper_subjects_id, eerb.academicTermId, spa.id) NOT IN | |
| (SELECT eers2.cm_academic_paper_subjects_id, eerb2.academicTermId, spa2.id FROM ec_student_assessment_registration esar2 | |
| INNER JOIN ec_exam_registration_subject eers2 ON eers2.am_assessment_id = esar2.am_assessment_id | |
| INNER JOIN ec_exam_registration_batch eerb2 ON eerb2.id = eers2.ec_exam_registration_batch_id | |
| INNER JOIN ec_exam_registration eer2 ON eer2.id = eerb2.ec_exam_registration_id | |
| INNER JOIN student_program_account spa2 ON | |
| spa2.student_id = esar2.student_id | |
| WHERE | |
| eer2.`type` = 'SUPPLEMENTARY' AND | |
| esar2.properties->>'$.registrationStatus' ='REGISTERED' | |
| $whereQuerySupply AND | |
| eer2.trashed IS NULL) | |
| GROUP BY eers.cm_academic_paper_subjects_id, spa.id"; | |
| try { | |
| $feStudents = $this->executeQueryForList($query); | |
| } catch (\Exception $e) { | |
| throw new ECCoreException(ECCoreException::ERROR_FETCHING,"Cannot fetch mooc count details! Please try again."); | |
| } | |
| foreach($feStudents as $student ){ | |
| $feStudentsList[$student->studentId]->studentProgramAccountId = $student->id; | |
| $feStudentObj = new \stdClass; | |
| $feStudentObj->paperSubjectId = $student->cm_academic_paper_subjects_id; | |
| $feStudentObj->termId = $student->academicTermId; | |
| $feStudentsList[$student->studentId]->failedSubjects[] = $feStudentObj; | |
| } | |
| $feStudentsList = array_values($feStudentsList); | |
| return $feStudentsList; | |
| } | |
| /** | |
| * Get Student Registered Mooc Count | |
| * @param $request | |
| * @return Group | |
| */ | |
| public function getStudentRegisteredMoocCount($request){ | |
| $request = $this->realEscapeObject($request); | |
| $moocCountResponse = []; | |
| $whereQuery = ""; | |
| if(!empty($request->studentId)) { | |
| $studentIdString = is_array($request->studentId) ? implode(",",$request->studentId) : $request->studentId; | |
| $whereQuery .= " AND esar.student_id IN ($studentIdString) "; | |
| } | |
| $query = "SELECT | |
| esar.student_id as studentId, | |
| eer.properties ->> '$.isMinorCourse' AS minorMoocCount, | |
| eer.properties ->> '$.isHonorCourse' AS honourMoocCount, | |
| eers.cm_academic_paper_subjects_id AS paperSubjectId | |
| FROM ec_student_assessment_registration esar | |
| INNER JOIN ec_exam_registration_subject eers ON | |
| eers.am_assessment_id = esar.am_assessment_id | |
| 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 | |
| WHERE esar.properties->>'$.syllabusSubType' = 'MOOC' AND esar.properties->>'$.registrationStatus' = 'REGISTERED' | |
| $whereQuery | |
| GROUP BY esar.student_id, eers.cm_academic_paper_subjects_id "; | |
| try { | |
| $studentMoocCounts = $this->executeQueryForList($query); | |
| } catch (\Exception $e) { | |
| throw new ECCoreException(ECCoreException::ERROR_FETCHING,"Cannot fetch mooc count details! Please try again."); | |
| } | |
| foreach($studentMoocCounts as $student ){ | |
| $moocCountResponse[$student->studentId]->studentId = $student->studentId; | |
| if( $student->minorMoocCount == 1 ){ | |
| $moocCountResponse[$student->studentId]->moocCount["MINOR"] += 1; | |
| $moocCountResponse[$student->studentId]->moocRegisteredPaperSubjectIds["MINOR"][] = $student->paperSubjectId; | |
| } | |
| if( $student->honourMoocCount == 1 ){ | |
| $moocCountResponse[$student->studentId]->moocCount["HONOURS"] += 1; | |
| $moocCountResponse[$student->studentId]->moocRegisteredPaperSubjectIds["HONOURS"][] = $student->paperSubjectId; | |
| } | |
| } | |
| $moocCountResponse = array_values($moocCountResponse); | |
| return $moocCountResponse; | |
| } | |
| /** | |
| * @param $request | |
| */ | |
| public function getStudentExamResultByCluster($request){ | |
| $request = $this->realEscapeObject($request); | |
| $studentListResponse = new \stdClass; | |
| $studentExamResultReq = new \stdClass; | |
| $studentExamResultReq->clusterId = $request->clusterIds; | |
| $studentExamResultReq->staffId = $request->staffId; | |
| $studentsList = $this->getStudentSubjectWiseResultByCluster($studentExamResultReq); | |
| $studentBatchList = []; | |
| $examType = 'EC_REGULAR_CONSOLIDATED'; | |
| $markListTemplate = StudentMarkListService::getInstance()->getUniversityMarkListTemplate($examType); | |
| switch ($markListTemplate) { | |
| case 'Template_6': | |
| // SAINTGITS Colleges | |
| $studentBatchList = RegularConsolidatedMarkListGenerator::getInstance()->processStudentSubjectDataForCourseFile($studentsList); | |
| break; | |
| case 'Template_1': | |
| // Default template | |
| $studentBatchList = RegularConsolidatedMarkListGenerator::getInstance()->processStudentSubjectDataForCourseFile($studentsList); | |
| break; | |
| default: | |
| $studentBatchList = []; | |
| break; | |
| } | |
| $studentListResponse->studentExamResultList = $studentBatchList; | |
| return $studentListResponse; | |
| } | |
| /** | |
| * get All Registered Students Details | |
| * @param $searchRequest | |
| */ | |
| public function getStudentSubjectWiseResultByCluster($searchRequest) { | |
| $searchRequest = $this->realEscapeObject($searchRequest); | |
| try{ | |
| $orderBy = "ORDER BY spa.properties->>'$.registerNumber' ASC "; | |
| $whereQuery = ""; | |
| if(!empty($searchRequest->clusterId)) { | |
| $clusterIdString = is_array($searchRequest->clusterId) ? "'" . implode("','",$searchRequest->clusterId) . "'" : "'".$searchRequest->clusterId."'"; | |
| $whereQuery .= " AND c.id IN ( $clusterIdString )"; | |
| } | |
| $query = "SELECT DISTINCT | |
| sa.studentID AS id, | |
| sa.studentID AS studentId, | |
| sa.studentName, | |
| spa.properties->>'$.rollNumber' AS rollNo, | |
| spa.properties->>'$.registerNumber' AS regNo, | |
| ct.course_Type as courseTypeName, | |
| g.id AS groupId, | |
| g.name AS groupName, | |
| s.code AS subjectCode, | |
| s.name AS subjectName, | |
| s.description AS subjectDesc, | |
| eers.cm_academic_paper_subjects_id AS academicPaperSubjectId, | |
| aps.properties ->> '$.syllabusName' AS syllabusName, | |
| aps.properties ->> '$.classType' AS classType, | |
| aps.properties ->>'$.courseCode' AS courseCode, | |
| esmdsubcon.mark_history AS subjectMarkHistory, | |
| esmdsubcon.mark_details AS subjectMarkDetails, | |
| eer.id AS examRegistrationid, | |
| eer.name AS examRegistrationName, | |
| eer.type AS examType, | |
| esbrm.ec_block_student_reason_id as blockStudentReasonId, | |
| ebsr.name as blockReasonName, | |
| cpsa.staffName as contactPersonName, | |
| ebsr.properties ->> '$.description' AS contactPersonDecription, | |
| eserd.properties->>'$.isResultWithHeld' AS isResultWithHeld, | |
| eerb.properties AS examRegistrationBatchProperties | |
| FROM `cluster` c | |
| INNER JOIN `cluster_groups_relations` cg ON cg.cluster_id = c.id | |
| INNER JOIN `groups` sg ON sg.type = 'SUBJECT' AND sg.id = cg.groups_id | |
| INNER JOIN `cm_academic_paper_subjects` aps ON sg.paperSubjectId = aps.id | |
| INNER JOIN `v4_ams_subject` s ON s.id = aps.ams_subject_id | |
| INNER JOIN `group_members` gm ON gm.groups_id = sg.id and gm.trashed is null | |
| INNER JOIN `student_program_account` spa ON spa.id = gm.student_id | |
| INNER JOIN `groups_relations` gr ON gr.child_groups_id = cg.groups_id | |
| INNER JOIN `groups` g ON gr.parent_groups_id = g.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 | |
| INNER JOIN `studentaccount` sa ON sa.studentID = spa.student_id | |
| INNER JOIN ec_exam_registration_subject eers ON eers.cm_academic_paper_subjects_id = aps.id | |
| 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 | |
| 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 = sa.studentID AND eserd.ec_exam_registration_id = eer.id | |
| 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 | |
| LEFT JOIN ec_student_block_reason_mapping esbrm ON esbrm.student_id = sa.studentID AND esbrm.exam_registration_id = eer.id AND esbrm.blocking_type = 'RESULT_BLOCKING' | |
| LEFT JOIN ec_block_student_reason ebsr ON ebsr.id = esbrm.ec_block_student_reason_id AND ebsr.type = 'SEMESTER_WISE' | |
| LEFT JOIN staffaccounts cpsa ON cpsa.staffID = ebsr.contact_person_id | |
| WHERE 1=1 "; | |
| $studentMarkDetails = $this->executeQueryForList($query.$whereQuery.$orderBy, $this->mapper[MarksCardServiceMapper::STUDENT_CLUSTER_MARK_DETAILS]); | |
| } | |
| catch (\Exception $e) | |
| { | |
| throw new ExamControllerException($e->getCode(),$e->getMessage()); | |
| } | |
| return $studentMarkDetails; | |
| } | |
| /** | |
| * get Student Exam Result For Progress Report | |
| * @param $request | |
| */ | |
| public function getStudentExamResultForProgressReport($request){ | |
| $request = $this->realEscapeObject($request); | |
| $studentExamResultResponse = new \stdClass; | |
| $getAcademicTerms = new \stdClass; | |
| $getAcademicTerms->groupId = $request->groupId; | |
| $semesters = CommonExamService::getInstance()->getAllAcademicTermsDetailsByBatch($getAcademicTerms); | |
| $studentExamResultReq = new \stdClass; | |
| $studentExamResultReq->studentId = $request->studentId; | |
| $studentExamResultReq->groupId = $request->groupId; | |
| $student = reset($this->getStudentSemesterWiseResult($studentExamResultReq)); | |
| if(empty($student)){ | |
| $student = new \stdClass; | |
| $student->academicTerms = []; | |
| } | |
| $student->regularNotPublishedExamRegIds = []; | |
| foreach($student->examRegistrations as $key => $examRegistration){ | |
| if( !($examRegistration->batchProperties->isResultPublished ) || (($examRegistration->batchProperties->isResultPublished) && (strtotime($examRegistration->batchProperties->publishingStartDate) > strtotime(date("Y-m-d H:i"))))) { | |
| if($examRegistration->examType == 'REGULAR'){ | |
| $student->regularNotPublishedExamRegIds[] = $examRegistration->id; | |
| } | |
| unset($student->examRegistrations[$key]); | |
| } | |
| else if(($examRegistration->examType) != 'REGULAR'){ | |
| if(!empty($examRegistration->blockReasons) || $examRegistration->isResultWithHeld == '1'){ | |
| unset($student->examRegistrations[$key]); | |
| } | |
| } | |
| else{ | |
| if($examRegistration->isResultWithHeld == '1'){ | |
| $student->withHeldExamRegIds[] = $examRegistration->id; | |
| unset($student->examRegistrations[$key]); | |
| } | |
| } | |
| } | |
| $student->examRegistrationIds = array_column($student->examRegistrations,'id'); | |
| foreach($student->academicTerms as $academicKey => $academicTerm){ | |
| foreach($academicTerm->subjects as $subject){ | |
| usort($subject->markHistory, function($a, $b) { | |
| return ($a->examYear."-".date("m", mktime(0, 0, 0, (int)$a->examMonth, 10))) < ($b->examYear."-".date("m", mktime(0, 0, 0, (int)$b->examMonth, 10))); | |
| }); | |
| foreach($subject->markHistory as $subjectMarkHistory){ | |
| if(in_array($subjectMarkHistory->examRegistrationId,$student->examRegistrationIds)){ | |
| $subject->subjectFailedStatus = $subjectMarkHistory->resultStatus; | |
| $subject->creditGradePoint = $subjectMarkHistory->creditGradePoint; | |
| break; | |
| } | |
| } | |
| } | |
| $academicTerm->totalArrearsNo = count(array_filter($academicTerm->subjects,function($value){ | |
| return $value->subjectFailedStatus == "FAILED"; | |
| })); | |
| $passedSubjects = []; | |
| $passedSubjects = array_filter($academicTerm->subjects,function($value){ | |
| return $value->subjectFailedStatus == 'PASSED'; | |
| }); | |
| $academicTerm->totalCreditGradePoint = array_sum(array_column($passedSubjects,'creditGradePoint')); | |
| $academicTerm->totalCredit = array_sum(array_column($academicTerm->subjects,'subjectCredit')); | |
| usort($academicTerm->markHistory, function($a, $b) { | |
| return ($a->examYear."-".date("m", mktime(0, 0, 0, (int)$a->examMonth, 10))) < ($b->examYear."-".date("m", mktime(0, 0, 0, (int)$b->examMonth, 10))); | |
| }); | |
| foreach($academicTerm->markHistory as $semesterMarkHistory){ | |
| if(in_array($semesterMarkHistory->examRegistrationId,$student->examRegistrationIds)){ | |
| $academicTerm->sgpa = $semesterMarkHistory->failedStatus == 'FAILED' ? '-' : sprintf('%0.2f', round($semesterMarkHistory->sgpa, 2)); | |
| $academicTerm->failedStatus = $semesterMarkHistory->failedStatus; | |
| break; | |
| } | |
| else if(in_array($semesterMarkHistory->examRegistrationId,$student->withHeldExamRegIds)){ | |
| $academicTerm->sgpa = '-'; | |
| $academicTerm->failedStatus = 'FAILED'; | |
| $academicTerm->isWithHeld = true; | |
| $academicTerm->totalArrearsNo = '-'; | |
| break; | |
| } | |
| else if(in_array($semesterMarkHistory->examRegistrationId,$student->regularNotPublishedExamRegIds)){ | |
| unset($student->academicTerms[$academicKey]); | |
| break; | |
| } | |
| } | |
| } | |
| $student->totalCredit = array_sum(array_column($student->academicTerms,'totalCredit')); | |
| $student->totalCreditGradePoint = array_sum(array_column($student->academicTerms,'totalCreditGradePoint')); | |
| $failedSemesters = array_filter($student->academicTerms,function($value){ | |
| return $value->failedStatus == 'FAILED'; | |
| }); | |
| if(empty($failedSemesters)){ | |
| $student->cgpa = $student->totalCreditGradePoint / $student->totalCredit; | |
| $student->cgpa = sprintf('%0.2f', round($student->cgpa, 2)); | |
| $student->failedStatus = "PASSED"; | |
| } | |
| else{ | |
| $student->failedStatus = "FAILED"; | |
| } | |
| $academicTermIds = array_column($student->academicTerms, 'id'); | |
| foreach( $semesters as $semester){ | |
| if(!in_array($semester->id,$academicTermIds)){ | |
| $student->academicTerms[] = $semester; | |
| } | |
| } | |
| usort($student->academicTerms, function($a, $b) { | |
| return $a->OrderNo > $b->OrderNo; | |
| }); | |
| $studentExamResultResponse->student = $student; | |
| return $studentExamResultResponse; | |
| } | |
| /** | |
| * get All Students (Semester Wise)Details | |
| * @param $searchRequest | |
| * @return $studentDetails | |
| */ | |
| public function getStudentSemesterWiseResult($searchRequest) { | |
| $searchRequest = $this->realEscapeObject($searchRequest); | |
| try{ | |
| $whereQuery = ""; | |
| if(!empty($searchRequest->groupId)) { | |
| $groupIdString = is_array($searchRequest->groupId) ? "'" . implode("','",$searchRequest->groupId) . "'" : "'".$searchRequest->groupId."'"; | |
| $whereQuery .= " AND g.id IN ( $groupIdString )"; | |
| } | |
| if(!empty($searchRequest->studentId)) { | |
| $studentIdString = is_array($searchRequest->studentId) ? "'" . implode("','",$searchRequest->studentId) . "'" : "'".$searchRequest->studentId."'"; | |
| $whereQuery .= " AND esar.student_id IN ( $studentIdString )"; | |
| } | |
| if(!empty($searchRequest->considerFailedSubjectOnly)) { | |
| $whereQuery .= " AND esmdsubcon.failed_status = 'FAILED'"; | |
| } | |
| $query = "SELECT DISTINCT | |
| 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 termOrderNo, | |
| eers.cm_academic_paper_subjects_id as academicPaperSubjectId, | |
| s.code as subjectCode, | |
| s.name as subjectName, | |
| esmdsubcon.mark_details ->>'$.credit' AS subjectCredit, | |
| esmdsubcon.mark_details AS subjectMarkDetails, | |
| esmdsubcon.mark_history as subjectMarkHistory, | |
| esmd.mark_details as semesterMarkDetails, | |
| esmd.mark_history as semesterMarkHistory, | |
| eer.id as examRegId, | |
| eerb.properties as examBatchProperties, | |
| eserd.properties->>'$.isResultWithHeld' AS isResultWithHeld, | |
| eer.type as examType | |
| FROM | |
| `groups` g | |
| INNER JOIN program p | |
| ON p.id = g.properties->>'$.programId' | |
| 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 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 | |
| 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 studentaccount sa ON | |
| sa.studentID = esar.student_id | |
| INNER JOIN ec_student_exam_registration_details eserd ON eserd.student_id = sa.studentID AND eserd.ec_exam_registration_id = eer.id | |
| INNER JOIN student_program_account spa ON | |
| spa.student_id = esar.student_id AND | |
| spa.current_program_id = p.id | |
| INNER JOIN academic_term act ON | |
| act.id = CAST(eerb.properties ->> '$.academicTermId'AS CHAR) | |
| INNER JOIN ec_semester_mark_details esmd ON | |
| esmd.groups_id = eerb.groups_id AND esmd.academic_term_id = act.id AND esmd.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 ( (eer.properties ->> '$.isHonorCourse' IS NULL OR eer.properties ->> '$.isHonorCourse' != '1') AND (eer.properties ->> '$.isMinorCourse' IS NULL OR eer.properties ->> '$.isMinorCourse' != '1'))"; | |
| $studentDetails = $this->executeQueryForList($query.$whereQuery.$orderBy, $this->mapper[MarksCardServiceMapper::SEMESTER_WISE_MARK_DETAILS]); | |
| } | |
| catch (\Exception $e){ | |
| throw new ExamControllerException($e->getCode(),$e->getMessage()); | |
| } | |
| return $studentDetails; | |
| } | |
| /** | |
| * Get student regular fe student details | |
| * @param $request | |
| * @return $feStudentsList | |
| */ | |
| public function getStudentRegularFeSubjects($request){ | |
| $request = $this->realEscapeObject($request); | |
| $feStudentsList = []; | |
| $whereQueryRegular = ""; | |
| if(!empty($request->studentProgramAccountId)) { | |
| $studentProgramAccountIdString = is_array($request->studentProgramAccountId) ? implode(",",$request->studentProgramAccountId) : $request->studentProgramAccountId; | |
| $whereQueryRegular .= " AND spa.id IN ($studentProgramAccountIdString) "; | |
| } | |
| $query = "SELECT eers.cm_academic_paper_subjects_id , eerb.academicTermId, spa.id | |
| FROM | |
| ec_student_assessment_registration esar | |
| INNER JOIN student_program_account spa ON | |
| spa.student_id = esar.student_id | |
| INNER JOIN ec_exam_registration_subject eers ON | |
| eers.am_assessment_id = esar.am_assessment_id | |
| 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 | |
| WHERE | |
| esar.properties->>'$.studentAttendanceStatus' = 'FE' AND | |
| esar.properties->>'$.registrationStatus' ='NOT_REGISTERED' | |
| $whereQueryRegular AND | |
| eer.trashed IS NULL"; | |
| try { | |
| $feStudents = $this->executeQueryForList($query); | |
| } catch (\Exception $e) { | |
| throw new ECCoreException(ECCoreException::ERROR_FETCHING,"Cannot fetch mooc count details! Please try again."); | |
| } | |
| foreach($feStudents as $student ){ | |
| $feStudentsList[$student->studentId]->studentProgramAccountId = $student->id; | |
| $feStudentObj = new \stdClass; | |
| $feStudentObj->paperSubjectId = $student->cm_academic_paper_subjects_id; | |
| $feStudentObj->termId = $student->academicTermId; | |
| $feStudentsList[$student->studentId]->failedSubjects[] = $feStudentObj; | |
| } | |
| $feStudentsList = array_values($feStudentsList); | |
| return $feStudentsList; | |
| } | |
| /** | |
| * Get Minor Syllabus Details By Student | |
| * @param $request | |
| * @return $minorSyllabusDetails | |
| */ | |
| public function getMinorSyllabusDetailsByStudent($request){ | |
| $request = $this->realEscapeObject($request); | |
| // if(!empty($request->academicPaperSubjectId)){ | |
| // $academicPaperSubjectIdString = is_array($request->academicPaperSubjectId) ? "'" . implode("','",$request->academicPaperSubjectId) . "'" : "'".$request->academicPaperSubjectId."'"; | |
| // $whereQuery .= " AND csra.academic_paper_subject_id IN ( $academicPaperSubjectIdString )"; | |
| // } | |
| // if(!empty($request->studentId)){ | |
| // $studentIdString = is_array($request->studentId) ? "'" . implode("','",$request->studentId) . "'" : "'".$request->studentId."'"; | |
| // $whereQuery .= " AND csra.student_id IN ( $studentIdString )"; | |
| // } | |
| $studentIdString = is_array($request->studentId) ? "'" . implode("','",$request->studentId) . "'" : "'".$request->studentId."'"; | |
| $query = "SELECT cs.name as syllabusName , cs.description as syllabusDescription | |
| FROM | |
| v4_cbe_student_registered_application csra | |
| INNER JOIN cm_academic_paper_subjects aps ON | |
| aps.id = csra.academic_paper_subject_id | |
| INNER JOIN cm_academic_paper ap ON | |
| aps.cm_academic_paper_id = ap.id | |
| INNER JOIN cm_syllabus_academic_term_settings csats ON | |
| csats.id = ap.cm_syllabus_academic_term_settings_id | |
| INNER JOIN cm_syllabus cs ON | |
| cs.id = csats.cm_syllabus_id | |
| WHERE | |
| cs.type = 'MINOR' AND csra.student_id IN ( $studentIdString )"; | |
| try { | |
| $minorSyllabusDetails = $this->executeQueryForObject($query); | |
| } catch (\Exception $e) { | |
| throw new ECCoreException(ECCoreException::ERROR_FETCHING,"Cannot fetch mooc count details! Please try again."); | |
| } | |
| return $minorSyllabusDetails; | |
| } | |
| /** | |
| * Get current fe student details | |
| * @param $request | |
| * @return $feStudentsList | |
| */ | |
| public function getBatchWiseCurrentFeStatus($request){ | |
| $request = $this->realEscapeObject($request); | |
| $feStudentsList = []; | |
| $whereQueryRegular = ""; | |
| $whereQuerySupply = ""; | |
| if(!empty($request->batchGroups)) { | |
| $groupArrayRegular = []; | |
| $groupArraySupply = []; | |
| foreach($request->batchGroups as $groupId){ | |
| $groupId = (object)$groupId; | |
| $termIdString = is_array($groupId->termIds) ? implode(",",$groupId->termIds) : $groupId->termIds; | |
| $groupArrayRegular[] = " ( spa.current_batch_id = '$groupId->batchId' AND eerb.academicTermId IN ( $termIdString ))"; | |
| $groupArraySupply[] = " ( spa2.current_batch_id = '$groupId->batchId' AND eerb2.academicTermId IN ( $termIdString ))"; | |
| } | |
| $whereQueryRegular .= " AND (". implode(" OR ",$groupArrayRegular).") "; | |
| $whereQuerySupply .= " AND (". implode(" OR ",$groupArraySupply).") "; | |
| } | |
| $query = "SELECT eers.cm_academic_paper_subjects_id , eerb.academicTermId, spa.id, spa.current_batch_id | |
| FROM | |
| ec_student_assessment_registration esar | |
| INNER JOIN student_program_account spa ON | |
| spa.student_id = esar.student_id | |
| INNER JOIN ec_exam_registration_subject eers ON | |
| eers.am_assessment_id = esar.am_assessment_id | |
| 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 | |
| WHERE | |
| esar.properties->>'$.studentAttendanceStatus' = 'FE' AND | |
| esar.properties->>'$.registrationStatus' ='NOT_REGISTERED' | |
| $whereQueryRegular AND | |
| eer.trashed IS NULL | |
| AND (eers.cm_academic_paper_subjects_id, eerb.academicTermId, spa.id) NOT IN | |
| (SELECT eers2.cm_academic_paper_subjects_id, eerb2.academicTermId, spa2.id FROM ec_student_assessment_registration esar2 | |
| INNER JOIN ec_exam_registration_subject eers2 ON eers2.am_assessment_id = esar2.am_assessment_id | |
| INNER JOIN ec_exam_registration_batch eerb2 ON eerb2.id = eers2.ec_exam_registration_batch_id | |
| INNER JOIN ec_exam_registration eer2 ON eer2.id = eerb2.ec_exam_registration_id | |
| INNER JOIN student_program_account spa2 ON spa2.student_id = esar2.student_id | |
| INNER JOIN ec_exam_registration_subject eers3 ON eers3.cm_academic_paper_subjects_id = eers2.cm_academic_paper_subjects_id | |
| INNER JOIN ec_exam_registration_batch eerb3 ON eerb3.id = eers3.ec_exam_registration_batch_id AND eerb3.groups_id = eerb2.groups_id | |
| INNER JOIN ec_exam_registration eer3 ON eer3.id = eerb3.ec_exam_registration_id AND eer3.`type` = 'REGULAR' | |
| INNER JOIN ec_student_assessment_registration esar3 ON esar3.am_assessment_id = eers3.am_assessment_id AND esar3.student_id = esar2.student_id AND esar3.properties->>'$.studentAttendanceStatus' = 'FE' AND | |
| esar3.properties->>'$.registrationStatus' ='NOT_REGISTERED' | |
| WHERE | |
| eer2.`type` = 'SUPPLEMENTARY' AND | |
| esar2.properties->>'$.registrationStatus' ='REGISTERED' | |
| $whereQuerySupply AND | |
| eer2.trashed IS NULL) | |
| GROUP BY eers.cm_academic_paper_subjects_id, spa.id"; | |
| try { | |
| $feStudents = $this->executeQueryForList($query); | |
| } catch (\Exception $e) { | |
| throw new ECCoreException(ECCoreException::ERROR_FETCHING,"Cannot fetch fe details! Please try again."); | |
| } | |
| $studentList = []; | |
| foreach($feStudents as $student ){ | |
| $feStudentsList[$student->current_batch_id]->batchId = $student->current_batch_id; | |
| if ( !in_array( $student->id, $studentList )){ | |
| $feStudentsList[$student->current_batch_id]->studentProgramAccountIds[] = $student->id; | |
| } | |
| $studentList[$student->id] = $student->id; | |
| } | |
| $feStudentsList = array_values($feStudentsList); | |
| return $feStudentsList; | |
| } | |
| /** | |
| * get Student Registered Mooc Subject | |
| * @param $request | |
| * @return Group | |
| */ | |
| public function getStudentRegisteredMoocSubject($request){ | |
| $request = $this->realEscapeObject($request); | |
| $whereQuery = ""; | |
| if(!empty($request->studentId)) { | |
| $studentIdString = is_array($request->studentId) ? implode(",",$request->studentId) : $request->studentId; | |
| $whereQuery .= " AND esar.student_id IN ($studentIdString) "; | |
| } | |
| $query = "SELECT | |
| esar.student_id as studentId, | |
| eers.cm_academic_paper_subjects_id as paperSubjectId, | |
| esar.properties->>'$.MOOC_SUBJECT_CODE' as moocSubjectCode, | |
| esar.properties->>'$.MOOC_SUBJECT_NAME' as moocSubjectName, | |
| esar.properties->>'$.MOOC_SUBJECT_MONTH_YEAR' as moocSubjectMonthYear, | |
| esar.properties->>'$.syllabusSubType' as syllabusSubType, | |
| esar.properties->>'$.moocCertificateStatus' as moocCertificateStatus | |
| FROM | |
| ec_student_assessment_registration esar | |
| INNER JOIN ec_exam_registration_subject eers ON | |
| eers.am_assessment_id = esar.am_assessment_id | |
| WHERE | |
| esar.properties->>'$.syllabusSubType' = 'MOOC' | |
| AND esar.properties->>'$.registrationStatus' = 'REGISTERED'"; | |
| try { | |
| $studentSubjects = $this->executeQueryForList($query.$whereQuery); | |
| } catch (\Exception $e) { | |
| throw new ECCoreException(ECCoreException::ERROR_FETCHING,"Cannot fetch fe details! Please try again."); | |
| } | |
| $studentList = []; | |
| foreach($studentSubjects as $student ){ | |
| $studentList[$student->studentId]->studentId = $student->studentId; | |
| $studentList[$student->studentId]->subjects[$student->paperSubjectId] = $student; | |
| } | |
| return $studentList; | |
| } | |
| /** | |
| * @param $request | |
| */ | |
| public function getBatchStudentsForCsvWithSemester($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $whereQuery = ""; | |
| $limitQuery = ""; | |
| $orderByQuery = ""; | |
| $groupByQuery = " GROUP BY spa.student_id "; | |
| if(!empty($request->groupId)) { | |
| $whereQuery .= " AND bg.id='$request->groupId' "; | |
| } | |
| if(!empty($request->termId)) { | |
| $termIdString = is_array($request->termId) ? implode("','",$request->termId) : $request->termId; | |
| $whereQuery .= " AND eerb.academicTermId IN ('$termIdString') "; | |
| } | |
| $orderByQuery = " ORDER BY spa.properties->>'$.registerNumber' ASC"; | |
| $query = "SELECT | |
| s.studentID AS studentId, | |
| s.studentName, | |
| spa.properties->>'$.registerNumber' AS registerNo, | |
| spa.properties->>'$.rollNumber' AS rollNo, | |
| bg.properties->>'$.startYear' as startYear, | |
| bg.properties->>'$.currentTermId' as currentTermId, | |
| bg.properties->>'$.finalTermId' as finalTermId | |
| 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 esar.ec_exam_registration_type = eer.type | |
| INNER JOIN cm_academic_paper_subjects caps On caps.id = eers.cm_academic_paper_subjects_id | |
| INNER JOIN v4_ams_subject vas On vas.id = caps.ams_subject_id | |
| INNER JOIN studentaccount s ON s.studentID = esar.student_id | |
| INNER JOIN student_program_account spa ON spa.student_id = esar.student_id | |
| INNER JOIN `groups` bg ON bg.id = eerb.groups_id | |
| WHERE | |
| eer.type = 'REGULAR' AND esar.properties->>'$.registrationStatus' = 'REGISTERED' "; | |
| try { | |
| $students = $this->executeQueryForList($query.$whereQuery.$groupByQuery.$orderByQuery); | |
| } catch (\Exception $e) { | |
| throw new ECCoreException(ECCoreException::ERROR_FETCHING,"Cannot fetch group students! Please try again."); | |
| } | |
| return $students; | |
| } | |
| } |