Code Coverage |
||||||||||
Classes and Traits |
Functions and Methods |
Lines |
||||||||
| Total | |
0.00% |
0 / 1 |
|
0.00% |
0 / 30 |
CRAP | |
0.00% |
0 / 1471 |
| StudentsOverAllMarkReportService | |
0.00% |
0 / 1 |
|
0.00% |
0 / 30 |
50400.00 | |
0.00% |
0 / 1471 |
| __construct | |
0.00% |
0 / 1 |
2.00 | |
0.00% |
0 / 4 |
|||
| getMarkImportedBatches | |
0.00% |
0 / 1 |
42.00 | |
0.00% |
0 / 52 |
|||
| getStudentExamMarkDetails | |
0.00% |
0 / 1 |
1892.00 | |
0.00% |
0 / 229 |
|||
| getBatchDetailsByGroupId | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 18 |
|||
| getSupplyAttemptCount | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 26 |
|||
| getPublishedSupplyExamRegistrationByStudentId | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 32 |
|||
| insertStudentCourseConsolidatedMarkDetails | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 26 |
|||
| insertStudentSemesterConsolidatedMarkDetails | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 28 |
|||
| insertStudentSubjectConsolidatedMarkDetails | |
0.00% |
0 / 1 |
132.00 | |
0.00% |
0 / 55 |
|||
| importStudentMarkDetails | |
0.00% |
0 / 1 |
240.00 | |
0.00% |
0 / 58 |
|||
| importStudentMarkDetailsByStudentWise | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 14 |
|||
| updateGenerateStatus | |
0.00% |
0 / 1 |
2352.00 | |
0.00% |
0 / 158 |
|||
| getSavedGenerateStatus | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 17 |
|||
| insertStudentEditStatus | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 17 |
|||
| getStudentEditstatus | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 20 |
|||
| updateStudentSubjectActiveStatus | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 14 |
|||
| getBatchValidations | |
0.00% |
0 / 1 |
30.00 | |
0.00% |
0 / 109 |
|||
| deleteStudentEditstatus | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 20 |
|||
| getStudentFinalizedRevaluationMark | |
0.00% |
0 / 1 |
72.00 | |
0.00% |
0 / 97 |
|||
| getValuationMethodDetailsByRequest | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 23 |
|||
| calculateInstitutionAverage | |
0.00% |
0 / 1 |
30.00 | |
0.00% |
0 / 35 |
|||
| getBatchStudentsForImportMarks | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 31 |
|||
| getBatchAssignedExamRegistrationForImport | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 35 |
|||
| getStudentsForImportStudentWiseMark | |
0.00% |
0 / 1 |
30.00 | |
0.00% |
0 / 40 |
|||
| updateGenerateStatusByStudentWIse | |
0.00% |
0 / 1 |
182.00 | |
0.00% |
0 / 67 |
|||
| getPublishedRevaluationExamRegistrationByStudentId | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 31 |
|||
| getStudentRevaluationMark | |
0.00% |
0 / 1 |
56.00 | |
0.00% |
0 / 132 |
|||
| getSubjectCategoryByRequest | |
0.00% |
0 / 1 |
30.00 | |
0.00% |
0 / 24 |
|||
| getSubCourseRelationBystudent | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 40 |
|||
| getStudentLowPassGradeDetails | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 19 |
|||
| <?php | |
| namespace com\linways\ec\core\service; | |
| use com\linways\base\util\MakeSingletonTrait; | |
| use com\linways\base\util\SecurityUtils; | |
| use com\linways\ec\core\constant\StatusConstants; | |
| use com\linways\ec\core\exception\ExamControllerException; | |
| use com\linways\ec\core\mapper\StudentsOverAllMarkReportServiceMapper; | |
| use com\linways\ec\core\service\MarkMigrationGenerator\MarkMigrationGenerator; | |
| use com\linways\ec\core\request\GenerateConsolidatedMarkRequest; | |
| use com\linways\ec\core\dto\ConsolidatedMarkGenerateStatus; | |
| use com\linways\ec\core\constant\ExamRegistrationTypeConstants; | |
| use com\linways\core\ams\professional\logging\AMSLogger; | |
| use com\linways\ec\core\logging\Events; | |
| use com\linways\ec\core\logging\entities\Staff; | |
| use com\linways\ec\core\mapper\ExamRevaluationServiceMapper; | |
| use com\linways\ec\core\service\MarkMigrationGenerator\Template3MigrationdResultGenerator; | |
| use com\linways\ec\core\service\ExamRegistrationSubjectService; | |
| use com\linways\ec\core\service\ExamRegistrationService; | |
| use com\linways\ec\core\request\ConsolidatedOverAllMarkReportRequest; | |
| class StudentsOverAllMarkReportService extends BaseService | |
| { | |
| use MakeSingletonTrait; | |
| private function __construct() { | |
| $this->mapper = StudentsOverAllMarkReportServiceMapper::getInstance()->getMapper(); | |
| $this->mapper2 = ExamRevaluationServiceMapper::getInstance()->getMapper(); | |
| $this->logger = AMSLogger::getLogger('exam-controller-log'); | |
| } | |
| /** | |
| * get mark imported batch details | |
| * @param $searchRequest | |
| * @return $groups | |
| */ | |
| public function getMarkImportedBatches($searchRequest) { | |
| $searchRequest = $this->realEscapeObject($searchRequest); | |
| try{ | |
| $orderBy = "ORDER BY g.created_date DESC"; | |
| $whereQuery = ""; | |
| if(!empty($searchRequest->admissionYear)) { | |
| $whereQuery .= " AND g.properties ->> '$.startYear' = '$searchRequest->admissionYear'"; | |
| } | |
| if(!empty($searchRequest->groupId)) { | |
| $whereQuery .= " AND g.id = '$searchRequest->groupId'"; | |
| } | |
| if(!empty($searchRequest->courseTypeId)) { | |
| $whereQuery .= " AND p.course_type_id = '$searchRequest->courseTypeId'"; | |
| } | |
| $query = "SELECT DISTINCT | |
| g.id, | |
| g.name as groupName, | |
| d.deptName, | |
| d.departmentDesc as departmentDescription, | |
| d.deptID, | |
| deg.name as degreeName, | |
| ct.typeName as courseTypeName, | |
| ct.courseTypeID as courseTypeId, | |
| ecmg.generate_status->> '$.status' as generateStatus, | |
| ecmg.generate_status ->> '$.processedStudentsCount' as processedStudentsCount, | |
| ecmg.generate_status ->> '$.processedChucks' as processedChucks, | |
| ecmg.generate_status ->> '$.hasStartedInQueue' as hasStartedInQueue, | |
| ecmg.created_date as createdDate, | |
| ecmg.updated_date as updatedDate | |
| FROM | |
| `groups` g | |
| INNER JOIN department d ON | |
| d.deptID = g.properties ->> '$.departmentId' | |
| INNER JOIN program p ON | |
| p.id = g.properties ->> '$.programId' | |
| INNER JOIN degree deg ON | |
| deg.id = p.degree_id | |
| INNER JOIN course_type ct ON | |
| ct.courseTypeID = p.course_type_id | |
| LEFT JOIN ec_cosolidated_mark_generate ecmg ON | |
| ecmg.groups_id = g.id | |
| WHERE 1=1 "; | |
| $groups = $this->executeQueryForList($query.$whereQuery.$orderBy); | |
| } | |
| catch (\Exception $e) | |
| { | |
| throw new ExamControllerException($e->getCode(),$e->getMessage()); | |
| } | |
| foreach($groups as $group){ | |
| $group->isSelected = false; | |
| } | |
| return $groups; | |
| } | |
| /** | |
| * get exam mark details by filter | |
| * Not use group_id filter in this service | |
| * @param $searchRequest | |
| * @return $exams | |
| */ | |
| public function getStudentExamMarkDetails($searchRequest){ | |
| $searchRequest = $this->realEscapeObject($searchRequest); | |
| try{ | |
| $orderBy = " ORDER BY eer.properties->>'$.examYear', eer.properties->>'$.examMonth', eer.properties->>'$.examDate' ASC"; | |
| $whereQuery = ""; | |
| // if(!empty($searchRequest->groupId)) { | |
| // $groupIdString = is_array($searchRequest->groupId) ? "'" . implode("','",$searchRequest->groupId) . "'" : "'".$searchRequest->groupId."'"; | |
| // $whereQuery .= " AND g.id IN ( $groupIdString )"; | |
| // } | |
| if(!empty($searchRequest->academicTermId)) { | |
| $semesterIdString = is_array($searchRequest->academicTermId) ? "'" . implode("','",$searchRequest->academicTermId) . "'" : "'".$searchRequest->academicTermId."'"; | |
| $whereQuery .= " AND act.id IN ( $semesterIdString )"; | |
| } | |
| if(!empty($searchRequest->academicPaperSubjectId)) { | |
| $academicPaperSubjectIdString = is_array($searchRequest->academicPaperSubjectId) ? "'" . implode("','",$searchRequest->academicPaperSubjectId) . "'" : "'".$searchRequest->academicPaperSubjectId."'"; | |
| $whereQuery .= " AND aps.id IN ( $academicPaperSubjectIdString )"; | |
| } | |
| if(!empty($searchRequest->examRegistrationId)) { | |
| $examRegistrationIdString = is_array($searchRequest->examRegistrationId) ? "'" . implode("','",$searchRequest->examRegistrationId) . "'" : "'".$searchRequest->examRegistrationId."'"; | |
| $whereQuery .= " AND eer.id IN ( $examRegistrationIdString )"; | |
| } | |
| if(!empty($searchRequest->studentId)) { | |
| $studentIdString = is_array($searchRequest->studentId) ? "'" . implode("','",$searchRequest->studentId) . "'" : "'".$searchRequest->studentId."'"; | |
| $whereQuery .= " AND sa.studentID IN ( $studentIdString )"; | |
| } | |
| if(!empty($searchRequest->regNo)) { | |
| $regNoString = is_array($searchRequest->regNo) ? "'" . implode("','",$searchRequest->regNo) . "'" : "'".$searchRequest->regNo."'"; | |
| $whereQuery .= " AND sa.regNo IN ( $regNoString )"; | |
| } | |
| if(!empty($searchRequest->examRegistrationType)) { | |
| $whereQuery .= " AND eer.type = '$searchRequest->examRegistrationType'"; | |
| } | |
| if(!empty($searchRequest->subjectRegistrationType)) { | |
| if($searchRequest->subjectRegistrationType == ExamRegistrationTypeConstants::SUPPLEMENTARY && $searchRequest->considerSupplyImproveCombined){ | |
| $whereQuery .= " AND ( esar.properties ->> '$.registrationType' = '$searchRequest->subjectRegistrationType' OR (esar.properties ->> '$.registrationType' = '".ExamRegistrationTypeConstants::IMPROVEMENT."' AND eer.properties->>'$.examRegistrationType' = '".ExamRegistrationTypeConstants::SUPPLY_IMPROVEMENT."'))"; | |
| } | |
| else if($searchRequest->subjectRegistrationType == ExamRegistrationTypeConstants::IMPROVEMENT ){ | |
| $whereQuery .= " AND esar.properties ->> '$.registrationType' = '$searchRequest->subjectRegistrationType'"; | |
| $whereQuery .= " AND eer.properties->>'$.examRegistrationType' != '".ExamRegistrationTypeConstants::SUPPLY_IMPROVEMENT."'"; | |
| } | |
| else{ | |
| $whereQuery .= " AND esar.properties ->> '$.registrationType' = '$searchRequest->subjectRegistrationType'"; | |
| } | |
| } | |
| $studentJoinCondition = " 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 | |
| esar.properties ->> '$.registrationStatus' = 'REGISTERED' AND | |
| esar.properties ->> '$.feeStatus' = 'PAID'"; | |
| if( $searchRequest->considerFeStudents){ | |
| $studentJoinCondition = " 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 | |
| ((esar.properties ->> '$.registrationStatus' = 'REGISTERED' AND | |
| esar.properties ->> '$.feeStatus' = 'PAID') OR | |
| esar.properties->>'$.studentAttendanceStatus' = 'FE' AND | |
| esar.properties->>'$.registrationStatus' ='NOT_REGISTERED' )"; | |
| } | |
| if($searchRequest->yearUpperLimit && $searchRequest->monthUpperLimit && $searchRequest->studentSupplyRequest){ | |
| $whereQuery .= " AND UNIX_TIMESTAMP(CONCAT ( eer.properties ->> '$.examYear' ,'-', eer.properties ->> '$.examMonth','-01')) <= UNIX_TIMESTAMP('$searchRequest->yearUpperLimit-$searchRequest->monthUpperLimit-01') "; | |
| } | |
| else if($searchRequest->supplementaryYearUpperLimit && $searchRequest->supplementaryMonthUpperLimit && $searchRequest->studentSupplyRequest){ | |
| $whereQuery .= " AND UNIX_TIMESTAMP(CONCAT ( eer.properties ->> '$.examYear' ,'-', eer.properties ->> '$.examMonth','-01')) <= UNIX_TIMESTAMP('$searchRequest->supplementaryYearUpperLimit-$searchRequest->supplementaryMonthUpperLimit-01') "; | |
| } | |
| $markTable = "LEFT JOIN oe_student_total_mark ostm ON | |
| ostm.student_id = sa.studentID AND ostm.am_assessment_id = esar.am_assessment_id AND (ostm.valuation_type IS NULL OR ostm.valuation_type = '') AND valuation_count = 'FINALIZED' AND ( ostm.properties->>'$.isNotConfirm' !=1 OR ostm.properties->>'$.isNotConfirm' IS NULL )"; | |
| if ($searchRequest->examRegistrationType == ExamRegistrationTypeConstants::SUPPLEMENTARY ){ | |
| $markTable = "INNER JOIN oe_student_total_mark ostm ON | |
| ostm.student_id = sa.studentID AND ostm.am_assessment_id = esar.am_assessment_id AND (ostm.valuation_type IS NULL OR ostm.valuation_type = '') AND valuation_count = 'FINALIZED' AND ( ostm.properties->>'$.isNotConfirm' !=1 OR ostm.properties->>'$.isNotConfirm' IS NULL )"; | |
| } | |
| if($searchRequest->requestType == "GRACEMARK" || $searchRequest->requestType == "MODERATION" || $searchRequest->requestType == "GRADE_UPGRADATION" || $searchRequest->requestType == "INTERNAL_GRACE_MARK" || $searchRequest->requestType == "EXTERNAL_GRACE_MARK" || $searchRequest->requestType == "GRACE_MARK_HASH_NOTATION"|| $searchRequest->requestType == "INTERNAL_HASH_MARK_NOTATION"){ | |
| $markTable = "INNER JOIN oe_student_total_mark ostm ON | |
| ostm.student_id = sa.studentID AND ostm.am_assessment_id = esar.am_assessment_id AND ostm.valuation_type = '$searchRequest->requestType '"; | |
| } | |
| if ($searchRequest->orderBy == "HIGHEST") { | |
| $orderBy = " ORDER BY ostm.mark_obtained DESC, CAST( eer.properties ->> '$.examYear' AS UNSIGNED) DESC, CAST( eer.properties ->> '$.examMonth' AS UNSIGNED) DESC "; | |
| } else if ($searchRequest->orderBy == "LATEST") { | |
| $orderBy = " ORDER BY CAST( eer.properties ->> '$.examYear' AS UNSIGNED) DESC, CAST( eer.properties ->> '$.examMonth' AS UNSIGNED) DESC LIMIT 1"; | |
| } | |
| if ( $searchRequest->considerMinorSubject){ | |
| $whereQuery .= " AND eer.isMinorCourse = '1'"; | |
| } | |
| else if ( $searchRequest->considerHonorSubject){ | |
| $whereQuery .= " AND eer.isHonorCourse = '1'"; | |
| } | |
| else{ | |
| $whereQuery .= " AND (eer.isHonorCourse IS NULL OR eer.isHonorCourse != '1') AND (eer.isMinorCourse IS NULL OR eer.isMinorCourse != '1')"; | |
| } | |
| $query = "SELECT | |
| sa.studentID AS studentId, | |
| sa.studentName, | |
| sa.regNo, | |
| sa.rollNo, | |
| sa.admissionNo, | |
| sa.myImage, | |
| sa.studentGender, | |
| g.id AS groupId, | |
| g.name AS groupName, | |
| act.id AS academicTermId, | |
| act.name AS academicTermName, | |
| act.properties ->> '$.orderNo' AS academicTermOrderNo, | |
| dept.deptID, | |
| deg.name AS degreeName, | |
| deg.id AS degreeId, | |
| dept.deptName, | |
| ct.courseTypeID, | |
| ct.typeName AS courseTypeName, | |
| ct.course_Type AS courseType, | |
| g.properties ->> '$.startYear' AS batchStartYear, | |
| g.properties ->> '$.endYear' AS batchEndYear, | |
| g.properties ->> '$.finalTermId' AS finalTermId, | |
| eer.id AS examRegistrationId, | |
| eer.name AS examRegistartionName, | |
| eer.type AS examRegType, | |
| eer.properties ->> '$.examYear' AS examYear, | |
| eer.properties ->> '$.examMonth' AS examMonth, | |
| eer.properties ->> '$.description' AS examRegDesc, | |
| eer.properties ->> '$.publish' AS publishsubjectRegistrationType, | |
| s.code AS subjectCode, | |
| aps.properties ->> '$.syllabusName' AS syllabusName, | |
| s.id AS amsSubjectId, | |
| s.code AS subjectName, | |
| s.name AS subjectDesc, | |
| IF(aps.properties ->> '$.classType' = 'THEORY',1,0) AS isTheory, | |
| aps.properties ->> '$.classType' AS classType, | |
| aa.id AS assessmentId, | |
| aa.name AS assessmentName, | |
| aa.description AS assessmentDescription, | |
| eers.cm_academic_paper_subjects_id AS academicPaperSubjectId, | |
| eers.properties ->> '$.moderationMark' AS maxModerationMark, | |
| esar.properties ->> '$.registrationType' AS studentExamRegType, | |
| esar.properties ->> '$.studentAttendanceStatus' AS studentAttendanceStatus, | |
| esar.properties->>'$.moocCertificateStatus' AS moocVerificationStatus, | |
| aps.properties ->> '$.credit' AS subjectCredit, | |
| aps.properties ->> '$.isInternal' AS isInternal, | |
| aps.properties ->> '$.isExternal' AS isExternal, | |
| aps.properties ->> '$.excludeSubjectFromTotal' AS excludeSubjectFromTotal, | |
| aps.properties ->> '$.order' AS subjectOrder, | |
| aps.properties ->> '$.priority' AS subjectPriority, | |
| aps.properties ->> '$.externalMaxMark' AS externalMaxMark, | |
| aps.properties ->> '$.internalMaxMark' AS internalMaxMark, | |
| IF(ees.id,1,0) AS isExempted, | |
| eim.id AS internalMarkId, | |
| eim.internal_mark AS internalMark, | |
| eim.attendance_status AS internalAttendanceStatus, | |
| ostm.mark_obtained AS externalMark, | |
| ostm.oe_exams_id AS oeExamId, | |
| ostm.attendance_status AS externalAttendanceStatus, | |
| ostm.internal_mark AS improvedInternalMark, | |
| ostm.internal_attendance_status AS improvedInternalAttendanceStatus, | |
| ostm.internal_carried_status AS improvedInternalCarriedStatus, | |
| ostm.internal_carried_status AS improvedInternalCarriedStatus, | |
| ostm.exam_additional_marks->>'$.mark1' AS examAdditionalMark1, | |
| ostm.properties ->> '$.hasCondonation' AS hasCondonationGraceMark, | |
| eerb.properties ->> '$.attendanceClosingDate' AS attendanceClosingDate | |
| FROM | |
| `groups` g | |
| INNER JOIN ec_exam_registration_batch eerb ON | |
| eerb.groups_id = g.id | |
| INNER JOIN ec_exam_registration_subject eers ON | |
| eers.ec_exam_registration_batch_id = eerb.id | |
| INNER JOIN cm_academic_paper_subjects aps ON | |
| eers.cm_academic_paper_subjects_id = aps.id | |
| INNER JOIN v4_ams_subject s ON | |
| aps.ams_subject_id = s.id | |
| INNER JOIN ec_exam_registration eer ON | |
| eer.id = eerb.ec_exam_registration_id | |
| $studentJoinCondition | |
| INNER JOIN am_assessment aa ON | |
| aa.id = eers.am_assessment_id | |
| INNER JOIN studentaccount sa ON | |
| sa.studentID = esar.student_id | |
| INNER JOIN department dept ON | |
| dept.deptID = g.properties ->> '$.departmentId' | |
| INNER JOIN academic_term act ON | |
| act.id = eerb.properties ->> '$.academicTermId' | |
| INNER JOIN program p ON | |
| p.id = g.properties ->> '$.programId' | |
| INNER JOIN `student_program_account` spa ON | |
| spa.current_program_id = p.id AND | |
| spa.student_id = esar.student_id | |
| INNER JOIN degree deg ON | |
| deg.id = p.degree_id | |
| INNER JOIN `course_type` ct ON | |
| ct.courseTypeID = p.course_type_id | |
| LEFT JOIN exam_exempted_students ees ON | |
| ees.am_assessment_id = eers.am_assessment_id AND | |
| ees.studentaccount_id = sa.studentID | |
| LEFT JOIN ec_internal_marks eim ON | |
| eim.program_id = p.id AND eim.student_id = sa.studentID AND eim.academic_paper_subjects_id =aps.id | |
| $markTable | |
| WHERE 1=1 AND eerb.properties ->>'$.isRequiredForImport' = 1 AND eer.trashed IS NULL | |
| "; | |
| if( $searchRequest->skipMapper ){ | |
| $studentMarkDetails = $this->executeQueryForList($query.$whereQuery.$orderBy); | |
| } | |
| else{ | |
| $studentMarkDetails = $this->executeQueryForList($query.$whereQuery.$orderBy,$this->mapper[StudentsOverAllMarkReportServiceMapper::OVER_ALL_MARK_DETAILS]); | |
| } | |
| if ($searchRequest->orderBy == "HIGHEST") { | |
| if ( count($studentMarkDetails) > 1){ | |
| foreach($studentMarkDetails as $studentMarkDetail){ | |
| foreach($studentMarkDetail->studentMarks as $key => $studentMark){ | |
| $revalRequest = new \stdClass(); | |
| $revalRequest->studentId = $studentMark->studentId; | |
| $revalRequest->assessmentId = $studentMarkDetail->assessmentId; | |
| $revalRequest->orderByHighestMark = 1; | |
| $studentRevaluationDetails = StudentsOverAllMarkReportService::getInstance()->getStudentFinalizedRevaluationMark($revalRequest); | |
| if($studentRevaluationDetails){ | |
| $studentRevaluationDetail = current(current($studentRevaluationDetails)->students); | |
| if($studentRevaluationDetail->revaluationMark){ | |
| $studentMarkDetail->studentCombinedMark = $studentRevaluationDetail->revaluationMark; | |
| } | |
| else{ | |
| $studentMarkDetail->studentCombinedMark = $studentMark->externalMark; | |
| } | |
| } | |
| else{ | |
| $studentMarkDetail->studentCombinedMark = $studentMark->externalMark; | |
| } | |
| } | |
| } | |
| usort($studentMarkDetails, function($a, $b) { | |
| return $b->studentCombinedMark <=> $a->studentCombinedMark; | |
| }); | |
| $studentMarkDetails = array_slice($studentMarkDetails, 0, 1); | |
| } | |
| } | |
| } | |
| catch (\Exception $e) | |
| { | |
| return false; | |
| // throw new ExamControllerException($e->getCode(),$e->getMessage()); | |
| } | |
| return $studentMarkDetails; | |
| } | |
| /** | |
| * get batchDetails by groupId | |
| * @param $groupId | |
| * @return $batchDetails | |
| */ | |
| public function getBatchDetailsByGroupId($groupId){ | |
| $groupId = $this->realEscapeString($groupId); | |
| try{ | |
| $query = "SELECT | |
| b.batchID as batchId, | |
| b.batchName | |
| FROM | |
| batches b | |
| INNER JOIN `groups` g ON | |
| g.id = b.groups_id | |
| WHERE | |
| b.groups_id = '$groupId';"; | |
| $batchDetails = $this->executeQueryForObject($query); | |
| } | |
| catch (\Exception $e){ | |
| return false; | |
| // throw new ExamControllerException($e->getCode(),$e->getMessage()); | |
| } | |
| return $batchDetails; | |
| } | |
| /** | |
| * get supply attempted count | |
| * @param $studentId | |
| * @param $academicPaperSubjectId | |
| * @return $supplyCount | |
| */ | |
| public function getSupplyAttemptCount($studentId, $academicPaperSubjectId){ | |
| $studentId = $this->realEscapeString($studentId); | |
| $academicPaperSubjectId = $this->realEscapeString($academicPaperSubjectId); | |
| try{ | |
| $query = "SELECT | |
| COUNT(eer.id) AS supplyAttemptCount | |
| FROM | |
| ec_exam_registration_batch eerb | |
| INNER JOIN ec_exam_registration_subject eers ON | |
| eers.ec_exam_registration_batch_id = eerb.id | |
| INNER JOIN cm_academic_paper_subjects aps ON | |
| eers.cm_academic_paper_subjects_id = aps.id | |
| INNER JOIN 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 | |
| esar.properties ->> '$.registrationStatus' = 'REGISTERED' AND | |
| esar.properties ->> '$.feeStatus' = 'PAID' | |
| WHERE eer.`type` !='REGULAR' AND esar.student_id =$studentId and aps.id ='$academicPaperSubjectId'"; | |
| $supplyCount = $this->executeQueryForObject($query)->supplyAttemptCount; | |
| } | |
| catch (\Exception $e){ | |
| return false; | |
| // throw new ExamControllerException($e->getCode(),$e->getMessage()); | |
| } | |
| return $supplyCount; | |
| } | |
| /** | |
| * get published exam details | |
| * @param $request | |
| * @return $supplyExams | |
| */ | |
| public function getPublishedSupplyExamRegistrationByStudentId($request){ | |
| $request = $this->realEscapeObject($request); | |
| $whereQuery = ""; | |
| if($request->academicTermId){ | |
| $whereQuery = " AND eerb.properties->>'$.academicTermId' = $request->academicTermId"; | |
| } | |
| if($request->considerPublishedExams){ | |
| $whereQuery = " AND eerb.properties->>'$.publish' = 'true'"; | |
| } | |
| try{ | |
| $query = "SELECT | |
| DISTINCT eer.id, | |
| eerb.properties->>'$.academicTermId' AS academicTermId, | |
| eer.name as examRegistrationName | |
| 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 AND eer.type = esar.ec_exam_registration_type | |
| WHERE | |
| student_id = $request->studentId AND esar.ec_exam_registration_type = eer.type | |
| AND eer.`type` ='SUPPLEMENTARY' | |
| "; | |
| $supplyExams = $this->executeQueryForList($query.$whereQuery); | |
| } | |
| catch (\Exception $e){ | |
| return false; | |
| // throw new ExamControllerException($e->getCode(),$e->getMessage()); | |
| } | |
| return $supplyExams; | |
| } | |
| /** | |
| * insert student course colidated details | |
| * @param $consolidatedMarkDetails | |
| */ | |
| public function insertStudentCourseConsolidatedMarkDetails ( $consolidatedMarkDetails ) { | |
| $consolidatedMarkDetails = $this->realEscapeObject($consolidatedMarkDetails); | |
| $markDetailsJSON = json_encode($consolidatedMarkDetails->markDetails, JSON_PARTIAL_OUTPUT_ON_ERROR); | |
| try { | |
| $sql = "INSERT INTO ec_course_mark_details (groups_id,student_id,mark_details,total_supply_attempt_count,total_marks,percentage,grade,cgpa,class,failed_status,is_dirty,no_of_arrears,created_by) | |
| VALUES ('$consolidatedMarkDetails->groupId',$consolidatedMarkDetails->studentId,'$markDetailsJSON',$consolidatedMarkDetails->supplyAttemptCount,$consolidatedMarkDetails->totalMark,$consolidatedMarkDetails->percentage,'$consolidatedMarkDetails->grade',$consolidatedMarkDetails->cgpa,'$consolidatedMarkDetails->class','$consolidatedMarkDetails->status',0,'$consolidatedMarkDetails->noOfArrears',$consolidatedMarkDetails->staffId) | |
| ON DUPLICATE KEY UPDATE | |
| updated_by = VALUES(created_by), | |
| groups_id = VALUES(groups_id), | |
| mark_details = VALUES(mark_details), | |
| total_supply_attempt_count = VALUES(total_supply_attempt_count), | |
| no_of_arrears = VALUES(no_of_arrears), | |
| total_marks = VALUES(total_marks), | |
| percentage = VALUES(percentage), | |
| grade = VALUES(grade), | |
| cgpa = VALUES(cgpa), | |
| class = VALUES(class), | |
| failed_status = VALUES(failed_status)"; | |
| if(strPos($sql,'INF')){ | |
| error_log($sql); | |
| } | |
| $this->executeQuery($sql); | |
| } | |
| catch(\Exception $e) { | |
| return false; | |
| // throw new ExamControllerException($e->getCode(),$e->getMessage()); | |
| } | |
| } | |
| /** | |
| * insert student semester colidated details | |
| * @param $consolidatedMarkDetails | |
| */ | |
| public function insertStudentSemesterConsolidatedMarkDetails ( $consolidatedMarkDetails ) { | |
| $consolidatedMarkDetails = $this->realEscapeObject($consolidatedMarkDetails); | |
| $markDetailsJSON = json_encode($consolidatedMarkDetails->markDetails, JSON_PARTIAL_OUTPUT_ON_ERROR); | |
| $markHisoryJSON = json_encode($consolidatedMarkDetails->markHistory, JSON_PARTIAL_OUTPUT_ON_ERROR); | |
| $regularExamMarkDetailsJSON = json_encode($consolidatedMarkDetails->regularExamMarkDetails, JSON_PARTIAL_OUTPUT_ON_ERROR); | |
| $regularExamMarkDetailsJSON = $regularExamMarkDetailsJSON ?? "{}"; | |
| try { | |
| $sql = "INSERT INTO ec_semester_mark_details (groups_id,academic_term_id,student_id,mark_details,total_supply_attempt_count,total_mark,percentage,grade,class,sgpa,failed_status,is_dirty,mark_history,created_by) | |
| VALUES ('$consolidatedMarkDetails->groupId','$consolidatedMarkDetails->academicTermId',$consolidatedMarkDetails->studentId,'$markDetailsJSON',$consolidatedMarkDetails->supplyAttemptCount,$consolidatedMarkDetails->totalMarks,$consolidatedMarkDetails->percentage,'$consolidatedMarkDetails->grade','$consolidatedMarkDetails->class',$consolidatedMarkDetails->sgpa,'$consolidatedMarkDetails->status',0,'$markHisoryJSON',$consolidatedMarkDetails->staffId) | |
| ON DUPLICATE KEY UPDATE | |
| updated_by = VALUES(created_by), | |
| mark_details = VALUES(mark_details), | |
| total_supply_attempt_count = VALUES(total_supply_attempt_count), | |
| total_mark = VALUES(total_mark), | |
| percentage = VALUES(percentage), | |
| grade = VALUES(grade), | |
| sgpa = VALUES(sgpa), | |
| class = VALUES(class), | |
| mark_history = VALUES(mark_history), | |
| failed_status = VALUES(failed_status)"; | |
| if(strPos($sql,'INF')){ | |
| error_log($sql); | |
| } | |
| $this->executeQuery($sql); | |
| } | |
| catch(\Exception $e) { | |
| return false; | |
| // throw new ExamControllerException($e->getCode(),$e->getMessage()); | |
| } | |
| } | |
| /** | |
| * insert student subject colidated details | |
| * @param $consolidatedMarkDetails | |
| */ | |
| public function insertStudentSubjectConsolidatedMarkDetails ( $consolidatedMarkDetails ) { | |
| $markDetailsJSON = json_encode($consolidatedMarkDetails->markDetails, JSON_PARTIAL_OUTPUT_ON_ERROR); | |
| $markHistoryJSON = json_encode($consolidatedMarkDetails->markHistory, JSON_PARTIAL_OUTPUT_ON_ERROR); | |
| try { | |
| $sql = "INSERT INTO ec_consolidated_subject_mark_details (groups_id,cm_academic_paper_subjects_id,student_id,mark_details,mark_history,no_of_chances_taken,total_mark,percentage,grade,class,failed_status,is_dirty,created_by, is_active) | |
| VALUES ('$consolidatedMarkDetails->groupId','$consolidatedMarkDetails->id',$consolidatedMarkDetails->studentId,'$markDetailsJSON','$markHistoryJSON',$consolidatedMarkDetails->noOfChancesTaken,".($consolidatedMarkDetails->totalMarks ? $consolidatedMarkDetails->totalMarks : 0).",".($consolidatedMarkDetails->percentage ? $consolidatedMarkDetails->percentage : 0).",'$consolidatedMarkDetails->grade','$consolidatedMarkDetails->class','$consolidatedMarkDetails->status',0,'$consolidatedMarkDetails->staffId', 1) | |
| ON DUPLICATE KEY UPDATE | |
| updated_by = VALUES(created_by), | |
| mark_details = VALUES(mark_details), | |
| mark_history = VALUES(mark_history), | |
| no_of_chances_taken = VALUES(no_of_chances_taken), | |
| total_mark = VALUES(total_mark), | |
| percentage = VALUES(percentage), | |
| grade = VALUES(grade), | |
| class = VALUES(class), | |
| is_active = VALUES(is_active), | |
| failed_status = VALUES(failed_status)"; | |
| if(strPos($sql,'INF')){ | |
| error_log($sql); | |
| } | |
| $sql = str_replace('INF', '0', $sql); | |
| $sql = str_replace('NAN', '0', $sql); | |
| $this->executeQuery($sql); | |
| $examRegMarkDetailsValues = []; | |
| $storeExamTypes = ["REGULAR","SUPPLY","SUPPLEMENTARY","IMPROVEMENT","REVALUATION"]; | |
| foreach ($consolidatedMarkDetails->markHistory as $markHistoryObj) { | |
| if (!in_array($markHistoryObj->examMarkType,$storeExamTypes)) continue; | |
| $markHistoryObjJSON = json_encode($markHistoryObj, JSON_PARTIAL_OUTPUT_ON_ERROR); | |
| // $id = SecurityUtils::getRandomString(); | |
| $examRegMarkDetailsValues[] = "('$consolidatedMarkDetails->groupId','$markHistoryObj->examRegistrationId','$consolidatedMarkDetails->id',$consolidatedMarkDetails->studentId,'$markHistoryObjJSON',$consolidatedMarkDetails->noOfChancesTaken,".($markHistoryObj->totalMark ? $markHistoryObj->totalMark : 0).",".($markHistoryObj->percentage ? $markHistoryObj->percentage : 0).",'$markHistoryObj->grade','$markHistoryObj->class','$markHistoryObj->resultStatus',0,$consolidatedMarkDetails->staffId, 1)"; | |
| } | |
| $valuesJSON = implode(',',$examRegMarkDetailsValues); | |
| $sql = "INSERT INTO ec_subject_mark_details (groups_id,ec_exam_registration_id,cm_academic_paper_subjects_id,student_id,mark_details,no_of_chances_taken,total_mark,percentage,grade,class,failed_status,is_dirty,created_by,is_active) | |
| VALUES $valuesJSON | |
| ON DUPLICATE KEY UPDATE | |
| updated_by = VALUES(created_by), | |
| mark_details = VALUES(mark_details), | |
| no_of_chances_taken = VALUES(no_of_chances_taken), | |
| total_mark = VALUES(total_mark), | |
| percentage = VALUES(percentage), | |
| grade = VALUES(grade), | |
| class = VALUES(class), | |
| is_active = VALUES(is_active), | |
| failed_status = VALUES(failed_status)"; | |
| if (!empty($examRegMarkDetailsValues)) { | |
| if(strPos($sql,'INF')){ | |
| error_log($sql); | |
| } | |
| $sql = str_replace('INF', '0', $sql); | |
| $sql = str_replace('NAN', '0', $sql); | |
| $this->executeQuery($sql); | |
| } | |
| } | |
| catch(\Exception $e) { | |
| return false; | |
| // throw new ExamControllerException($e->getCode(),$e->getMessage()); | |
| } | |
| } | |
| /** | |
| * import student colidated mark details | |
| * adding students in queue | |
| * @param $searchRequest | |
| */ | |
| public function importStudentMarkDetails($searchRequest){ | |
| try{ | |
| $searchRequest = $this->realEscapeObject($searchRequest); | |
| $generateStatus = new ConsolidatedMarkGenerateStatus(); | |
| $currentSavedGenerateStatus = $this->getSavedGenerateStatus($searchRequest); | |
| $processedStudentCount = $currentSavedGenerateStatus->generateStatus->processedStudentsCount ? $currentSavedGenerateStatus->generateStatus->processedStudentsCount : 0; | |
| $processedChucks = $currentSavedGenerateStatus->generateStatus->processedChucks ? $currentSavedGenerateStatus->generateStatus->processedChucks : 0; | |
| $generateStatus->status = $currentSavedGenerateStatus->generateStatus->status; | |
| $generateStatus->hasStartedInQueue = 1; | |
| $generateStatus->processedStudentsCount = $processedStudentCount; | |
| $generateStatus->totalStudents = $searchRequest->totalStudents; | |
| $generateStatus->totalChunks = $searchRequest->totalChunks ; | |
| if ($searchRequest->totalStudents <= $processedStudentCount) { | |
| $generateStatus->status = StatusConstants::SUCCESS; | |
| } | |
| $this::getInstance()->updateGenerateStatus($searchRequest,$generateStatus); | |
| $currentProcessedStudent = 0; | |
| foreach ($searchRequest->studentIds as $studentId){ | |
| $searchRequest->studentId = $studentId; | |
| MarkMigrationGenerator::getInstance()->getConsolidatedMarkListResultData($searchRequest); | |
| $currentProcessedStudent++; | |
| } | |
| $currentSavedGenerateStatus = $this->getSavedGenerateStatus($searchRequest); | |
| $processedStudentCount = $currentSavedGenerateStatus->generateStatus->processedStudentsCount ? $currentSavedGenerateStatus->generateStatus->processedStudentsCount : 0; | |
| $processedChucks = $currentSavedGenerateStatus->generateStatus->processedChucks ? $currentSavedGenerateStatus->generateStatus->processedChucks : 0; | |
| $processCompletedStudents = $currentSavedGenerateStatus->generateStatus->processCompletedStudents ? $currentSavedGenerateStatus->generateStatus->processCompletedStudents : []; | |
| $generateStatus->status = $currentSavedGenerateStatus->generateStatus->status; | |
| $processedStudentCount = $processedStudentCount + $currentProcessedStudent; | |
| $processedChucks++; | |
| if ($searchRequest->totalStudents <= $processedStudentCount) { | |
| $generateStatus->status = StatusConstants::SUCCESS; | |
| $generateStatus->hasStartedInQueue = 0; | |
| } | |
| else{ | |
| if ($searchRequest->totalChunks == $processedChucks) { | |
| $generateStatus->status = $processedStudentCount && $generateStatus->status != StatusConstants::FAILED ? StatusConstants::PARTIALLY_COMPLETED : StatusConstants::FAILED; | |
| } | |
| else { | |
| $generateStatus->status = StatusConstants::PROCESSING; | |
| } | |
| } | |
| $generateStatus->processedStudentsCount = $processedStudentCount; | |
| $generateStatus->processCompletedStudents = array_merge($processCompletedStudents,$searchRequest->studentIds); | |
| $generateStatus->processedChucks = $processedChucks; | |
| $this::getInstance()->updateGenerateStatus($searchRequest,$generateStatus); | |
| } | |
| catch(\Exception $e) { | |
| error_log($e); | |
| $generateStatus = new ConsolidatedMarkGenerateStatus(); | |
| if($processedChucks > 0 && $currentSavedGenerateStatus->generateStatus->status != StatusConstants::FAILED){ | |
| $generateStatus->status = StatusConstants::PROCESSING; | |
| } | |
| else{ | |
| $generateStatus->status = StatusConstants::FAILED; | |
| } | |
| $generateStatus->processedChucks = $processedChucks +1; | |
| $this::getInstance()->updateGenerateStatus($searchRequest,$generateStatus); | |
| } | |
| } | |
| /** | |
| * import student colidated mark details by student wise | |
| * adding students in queue | |
| * @param $searchRequest | |
| */ | |
| public function importStudentMarkDetailsByStudentWise($searchRequest){ | |
| try{ | |
| $searchRequest = $this->realEscapeObject($searchRequest); | |
| $generateStatus = new ConsolidatedMarkGenerateStatus(); | |
| MarkMigrationGenerator::getInstance()->getConsolidatedMarkListResultData($searchRequest); | |
| $generateStatus->status = StatusConstants::SUCCESS; | |
| $this::getInstance()->updateGenerateStatusByStudentWIse($searchRequest,$generateStatus); | |
| } | |
| catch(\Exception $e) { | |
| error_log($e); | |
| $generateStatus = new ConsolidatedMarkGenerateStatus(); | |
| $generateStatus->status = StatusConstants::FAILED; | |
| $this::getInstance()->updateGenerateStatusByStudentWIse($searchRequest,$generateStatus); | |
| } | |
| } | |
| /** | |
| * updated generate status for mark import | |
| * @param $request | |
| * @param ConsolidatedMarkGenerateStatus $generateStatus | |
| */ | |
| public function updateGenerateStatus($request, ConsolidatedMarkGenerateStatus $generateStatus){ | |
| $request = $this->realEscapeObject($request); | |
| $generateStatus->processedStudentsCount = $generateStatus->processedStudentsCount !== null ? $this->realEscapeString($generateStatus->processedStudentsCount) : null; | |
| $generateStatus->processedChucks = $generateStatus->processedChucks !== null ? $this->realEscapeString($generateStatus->processedChucks) : null; | |
| $generateStatus->status = $generateStatus->status !== null ? $this->realEscapeString($generateStatus->status) : null; | |
| $generateStatus->isFailedStatus = $generateStatus->isFailedStatus !== null ? $this->realEscapeString($generateStatus->isFailedStatus) : null; | |
| $generateStatus->failedStatusReasons = $generateStatus->failedStatusReasons !== null ? $this->realEscapeArray($generateStatus->failedStatusReasons) : null; | |
| $generateStatus->isDirty = $generateStatus->isDirty !== null ? $this->realEscapeString($generateStatus->isDirty) : null; | |
| $generateStatus->dirtyReasons = $generateStatus->dirtyReasons !== null ? $this->realEscapeArray($generateStatus->dirtyReasons) : null; | |
| $generateStatus->processeFailedStudents = $generateStatus->processeFailedStudents !== null ? $this->realEscapeArray($generateStatus->processeFailedStudents) : null; | |
| $generateStatus->processCompletedStudents = $generateStatus->processCompletedStudents !== null ? $this->realEscapeArray($generateStatus->processCompletedStudents) : []; | |
| $updateSql = []; | |
| if (StatusConstants::FAILED == $generateStatus->status){ | |
| $generateStatus->isFailedStatus = true; | |
| } | |
| else if (StatusConstants::SUCCESS == $generateStatus->status){ | |
| $generateStatus->isFailedStatus = false; | |
| } | |
| else if (StatusConstants::PARTIALLY_COMPLETED == $generateStatus->status){ | |
| $generateStatus->isFailedStatus = true; | |
| } | |
| else if (StatusConstants::PROCESSING == $generateStatus->status){ | |
| $generateStatus->isFailedStatus = false; | |
| } | |
| else if ("NOT GENERATED" == $generateStatus->status){ | |
| $generateStatus->isFailedStatus = true; | |
| } | |
| if ($generateStatus->processedStudentsCount !== null && isset($generateStatus->processedStudentsCount)) { | |
| $updateSql[] = "generate_status = JSON_SET(generate_status,'$.processedStudentsCount',$generateStatus->processedStudentsCount)"; | |
| } | |
| else{ | |
| $generateStatus->processedStudentsCount = 0; | |
| } | |
| if ($generateStatus->processedChucks !== null && isset($generateStatus->processedChucks)) { | |
| $updateSql[] = "generate_status = JSON_SET(generate_status,'$.processedChucks',$generateStatus->processedChucks)"; | |
| } | |
| else{ | |
| $generateStatus->processedChucks = 0; | |
| } | |
| if ($generateStatus->status !== null && isset($generateStatus->status)) { | |
| $updateSql[] = "generate_status = JSON_SET(generate_status,'$.status','$generateStatus->status')"; | |
| } | |
| else{ | |
| $generateStatus->status = "NOT GENERATED"; | |
| } | |
| if ($generateStatus->hasStartedInQueue !== null && isset($generateStatus->hasStartedInQueue)) { | |
| $updateSql[] = "generate_status = JSON_SET(generate_status,'$.hasStartedInQueue','$generateStatus->hasStartedInQueue')"; | |
| } | |
| else{ | |
| $updateSql[] = "generate_status = JSON_SET(generate_status,'$.hasStartedInQueue','0')"; | |
| $generateStatus->hasStartedInQueue = 0; | |
| } | |
| if ($generateStatus->totalStudents !== null && isset($generateStatus->totalStudents)) { | |
| $updateSql[] = "generate_status = JSON_SET(generate_status,'$.totalStudents','$generateStatus->totalStudents')"; | |
| } | |
| if ($generateStatus->totalChunks !== null && isset($generateStatus->totalChunks)) { | |
| $updateSql[] = "generate_status = JSON_SET(generate_status,'$.totalChunks','$generateStatus->totalChunks')"; | |
| } | |
| if ($generateStatus->isFailedStatus !== null && isset($generateStatus->isFailedStatus)) { | |
| if ($generateStatus->isFailedStatus !== true) { | |
| $updateSql[] = "generate_status = JSON_SET(generate_status,'$.failedStatusReasons',JSON_ARRAY())"; | |
| $updateSql[] = "generate_status = JSON_SET(generate_status,'$.isFailedStatus',false)"; | |
| } | |
| else{ | |
| $updateSql[] = "generate_status = JSON_SET(generate_status,'$.isFailedStatus',true)"; | |
| } | |
| } | |
| else{ | |
| $generateStatus->isFailedStatus = false; | |
| } | |
| if($generateStatus->failedStatusReasons !== null && isset($generateStatus->failedStatusReasons)){ | |
| if (!empty($generateStatus->failedStatusReasons) && $generateStatus->isFailedStatus === true) { | |
| $failedStatusReasonJSON = json_encode($generateStatus->failedStatusReasons); | |
| $updateSql[] = "generate_status = JSON_MERGE_PRESERVE(generate_status,'{\"failedStatusReasons\": $failedStatusReasonJSON}')"; | |
| } | |
| else { | |
| $updateSql[] = "generate_status = JSON_SET(generate_status,'$.failedStatusReasons',JSON_ARRAY())"; | |
| } | |
| } | |
| else{ | |
| $generateStatus->failedStatusReasons = []; | |
| } | |
| if ($generateStatus->isDirty !== null && isset($generateStatus->isDirty)) { | |
| if ($generateStatus->isDirty !== true) { | |
| $updateSql[] = "generate_status = JSON_SET(generate_status,'$.dirtyReasons',JSON_ARRAY())"; | |
| $updateSql[] = "generate_status = JSON_SET(generate_status,'$.isDirty',false)"; | |
| } | |
| else{ | |
| $updateSql[] = "generate_status = JSON_SET(generate_status,'$.isDirty',true)"; | |
| } | |
| } | |
| else{ | |
| $generateStatus->isDirty = false; | |
| } | |
| if($generateStatus->dirtyReasons !== null && isset($generateStatus->dirtyReasons)){ | |
| if (empty($generateStatus->dirtyReasons)) { | |
| $updateSql[] = "generate_status = JSON_SET(generate_status,'$.dirtyReasons',JSON_ARRAY())"; | |
| } | |
| else { | |
| $dirtyReasonsJSON = json_encode($generateStatus->dirtyReasons); | |
| $updateSql[] = "generate_status = JSON_MERGE_PRESERVE(generate_status,'{\"dirtyReasons\": $dirtyReasonsJSON}')"; | |
| } | |
| } | |
| else{ | |
| $generateStatus->dirtyReasons = []; | |
| } | |
| if($generateStatus->processCompletedStudents !== null && isset($generateStatus->processCompletedStudents)){ | |
| $processCompletedStudentsJSON = json_encode($generateStatus->processCompletedStudents); | |
| if( $generateStatus->processCompletedStudents == 1){ | |
| $updateSql[] = "generate_status = JSON_SET(generate_status,'$.processCompletedStudents',JSON_ARRAY())"; | |
| } | |
| else{ | |
| $updateSql[] = "generate_status = JSON_MERGE_PRESERVE(generate_status,'{\"processCompletedStudents\": $processCompletedStudentsJSON}')"; | |
| } | |
| } | |
| if($generateStatus->processeFailedStudents !== null && isset($generateStatus->processeFailedStudents)){ | |
| if (empty($generateStatus->processeFailedStudents)) { | |
| $updateSql[] = "generate_status = JSON_SET(generate_status,'$.processeFailedStudents',JSON_ARRAY())"; | |
| } | |
| else { | |
| $dirtyReasonsJSON = json_encode($generateStatus->processeFailedStudents); | |
| $updateSql[] = "generate_status = JSON_MERGE_PRESERVE(generate_status,'{\"processeFailedStudents\": $dirtyReasonsJSON}')"; | |
| } | |
| } | |
| else{ | |
| $generateStatus->processeFailedStudents = []; | |
| } | |
| $id = SecurityUtils::getRandomString(); | |
| $generateStatusJSON = json_encode($generateStatus); | |
| if (!empty($updateSql)) { | |
| $updateSql[] = "groups_id = VALUES(groups_id)"; | |
| $updateSqlString = implode(",", $updateSql); | |
| $updateSqlString = "ON DUPLICATE KEY UPDATE | |
| $updateSqlString"; | |
| } | |
| try { | |
| $sql = "INSERT INTO | |
| ec_cosolidated_mark_generate (id, batch_id, groups_id, generate_status, created_by) | |
| VALUES ('$id','$request->batchId','$request->groupId','$generateStatusJSON',$request->staffId) | |
| $updateSqlString"; | |
| $this->executeQuery($sql); | |
| $currentStaffId = $request->staffId; | |
| AMSLogger::log_info($this->logger,Events::EC_SAVE_BATCHWISE_MARK_IMPORTED_STATUS,[ | |
| "staff" => new Staff(["id" => $currentStaffId]), | |
| "request" => $request, | |
| "status" => StatusConstants::SUCCESS | |
| ]); | |
| } | |
| catch(\Exception $e) { | |
| throw new ExamControllerException("STATUS_UPDATE_ERROR",$e->getMessage()); | |
| AMSLogger::log_error($this->logger,Events::EC_SAVE_BATCHWISE_MARK_IMPORTED_STATUS,[ | |
| "staff" => new Staff(["id" => $currentStaffId]), | |
| "request" => $request, | |
| "errorCode" => $e->getCode(), | |
| "errorMessage" => $e->getMessage(), | |
| "status" => StatusConstants::FAILED | |
| ]); | |
| } | |
| } | |
| /** | |
| * get generate status by groupId | |
| * @param $request | |
| * @return status | |
| */ | |
| public function getSavedGenerateStatus($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| try{ | |
| $sql = "SELECT | |
| id, | |
| groups_id AS groupId, | |
| generate_status AS generateStatus | |
| FROM | |
| ec_cosolidated_mark_generate | |
| WHERE groups_id = '$request->groupId'"; | |
| $status = $this->executeQueryForObject($sql); | |
| }catch (\Exception $e){ | |
| return false; | |
| // throw new ExamControllerException($e->getCode(),$e->getMessage()); | |
| } | |
| $status->generateStatus = json_decode($status->generateStatus); | |
| return $status; | |
| } | |
| /** | |
| * insert student course colidated details | |
| * @param $editHistoryLog | |
| */ | |
| public function insertStudentEditStatus ( $editHistoryLog ) { | |
| $editHistoryLog = $this->realEscapeObject($editHistoryLog); | |
| $insertionTableArray = []; | |
| foreach($editHistoryLog as $log){ | |
| $insertionTableArray[] = "('$log->groupId',$log->studentId,$log->isDirty,$log->staffId)"; | |
| } | |
| try { | |
| $sql = "INSERT INTO ec_cosolidated_mark_generate_student_edit_status (groups_id,student_id,is_dirty,created_by) | |
| VALUES " . implode ( ",", $insertionTableArray ) . " | |
| ON DUPLICATE KEY UPDATE | |
| updated_by = VALUES(created_by), | |
| is_dirty = VALUES(is_dirty)"; | |
| $this->executeQuery($sql); | |
| } | |
| catch(\Exception $e) { | |
| return false; | |
| // throw new ExamControllerException($e->getCode(),$e->getMessage()); | |
| } | |
| } | |
| /** | |
| * get student edit status by request | |
| * @param $request | |
| * @return status | |
| */ | |
| public function getStudentEditstatus($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $whereQuery = ""; | |
| if(!empty($request->groupId)) { | |
| $whereQuery .= " AND groups_id = '$request->groupId'"; | |
| } | |
| try{ | |
| $sql = "SELECT | |
| id, | |
| groups_id AS groupId, | |
| student_id AS studentId | |
| FROM | |
| ec_cosolidated_mark_generate_student_edit_status | |
| WHERE 1=1 "; | |
| $status = $this->executeQueryForObject($sql.$whereQuery); | |
| }catch (\Exception $e){ | |
| return false; | |
| // throw new ExamControllerException($e->getCode(),$e->getMessage()); | |
| } | |
| return $status; | |
| } | |
| /** | |
| * update subject active status by request | |
| * @param $request | |
| * @return status | |
| */ | |
| public function updateStudentSubjectActiveStatus($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| try{ | |
| $sql = "UPDATE ec_subject_mark_details SET is_active = 0 | |
| WHERE student_id = '$request->studentId' "; | |
| $this->executeQuery($sql); | |
| $sql1 = "UPDATE ec_consolidated_subject_mark_details SET is_active = 0 | |
| WHERE student_id = '$request->studentId' "; | |
| $this->executeQuery($sql1); | |
| }catch (\Exception $e){ | |
| return false; | |
| // throw new ExamControllerException($e->getCode(),$e->getMessage()); | |
| } | |
| return; | |
| } | |
| /** | |
| * get mark imported batch details | |
| * @param $searchRequest | |
| * @return $groups | |
| */ | |
| public function getBatchValidations($searchRequest) { | |
| $searchRequest = $this->realEscapeObject($searchRequest); | |
| try{ | |
| $orderBy = "ORDER BY g.created_date, act.id ASC"; | |
| $whereQuery = ""; | |
| if(!empty($searchRequest->groupIds)) { | |
| $groupIdString = is_array($searchRequest->groupIds) ? "'" . implode("','",$searchRequest->groupIds) . "'" : "'".$searchRequest->groupIds."'"; | |
| $whereQuery .= " AND g.id IN ( $groupIdString )"; | |
| } | |
| $limit = null; | |
| if( $searchRequest->entryCheck ){ | |
| $limit = " LIMIT 1"; | |
| } | |
| $query = " SELECT | |
| g.id AS groupId, | |
| g.name AS batchName, | |
| act.id AS academicTermId, | |
| act.name AS academicTermName, | |
| act.properties ->> '$.orderNo' AS academicTermOrderNo, | |
| dept.deptID, | |
| deg.name AS degreeName, | |
| deg.id AS degreeId, | |
| dept.deptName, | |
| ct.courseTypeID, | |
| ct.typeName AS courseTypeName, | |
| ct.course_Type AS courseType, | |
| g.properties ->> '$.startYear' AS batchStartYear, | |
| g.properties ->> '$.endYear' AS batchEndYear, | |
| g.properties ->> '$.finalTermId' AS finalTermId, | |
| eer.id AS examRegistrationId, | |
| eer.name AS examRegistartionName, | |
| eer.type AS examRegType, | |
| eer.properties ->> '$.examYear' AS examYear, | |
| eer.properties ->> '$.examMonth' AS examMonth, | |
| eer.properties ->> '$.description' AS examRegDesc, | |
| eer.properties ->> '$.publish' AS publishsubjectRegistrationType, | |
| s.code AS subjectCode, | |
| aps.properties ->> '$.syllabusName' AS syllabusName, | |
| s.name AS subjectName, | |
| IF(aps.properties ->> '$.classType' = 'THEORY',1,0) AS isTheory, | |
| eers.cm_academic_paper_subjects_id AS academicPaperSubjectId, | |
| aps.properties ->> '$.credit' AS subjectCredit, | |
| aps.properties ->> '$.isInternal' AS isInternal, | |
| aps.properties ->> '$.isExternal' AS isExternal, | |
| aps.properties ->> '$.excludeSubjectFromTotal' AS excludeSubjectFromTotal, | |
| aps.properties ->> '$.order' AS subjectOrder, | |
| aps.properties ->> '$.priority' AS subjectPriority, | |
| aps.properties ->> '$.externalMaxMark' AS externalMaxMark, | |
| aps.properties ->> '$.internalMaxMark' AS internalMaxMark, | |
| aps.properties ->> '$.classType' AS classType, | |
| cc.properties->>'$.gradeSchemeId' AS cgpaGradeSchemeId, | |
| csats.id AS syllabusTermId, | |
| -- vm_grade.properties->>'$.gradeSchemeId' AS semesterGradeSchemeId, | |
| aps.properties->>'$.gradeSchemeId' AS subjectGradeSchemeId | |
| -- vm.properties AS internalPercentage, | |
| -- vm1.properties AS externalPercentage, | |
| -- vm2.properties AS subjectPercentage | |
| FROM | |
| `groups` g | |
| INNER JOIN ec_exam_registration_batch eerb ON | |
| eerb.groups_id = g.id | |
| INNER JOIN ec_exam_registration_subject eers ON | |
| eers.ec_exam_registration_batch_id = eerb.id | |
| INNER JOIN cm_academic_paper_subjects aps ON | |
| eers.cm_academic_paper_subjects_id = aps.id | |
| INNER JOIN cm_academic_paper ap ON | |
| ap.id = aps.cm_academic_paper_id | |
| INNER JOIN cm_curriculum cc ON | |
| cc.id = g.properties->>'$.curriculumId' | |
| INNER JOIN cm_curriculum_syllabus_relation ccsr ON | |
| ccsr.cm_curriculum_id = cc.id | |
| INNER JOIN cm_syllabus_academic_term_settings csats ON | |
| csats.cm_syllabus_id = ccsr.cm_syllabus_id AND csats.id = ap.cm_syllabus_academic_term_settings_id | |
| INNER JOIN v4_ams_subject s ON | |
| aps.ams_subject_id = s.id | |
| INNER JOIN ec_exam_registration eer ON | |
| eer.id = eerb.ec_exam_registration_id | |
| INNER JOIN department dept ON | |
| dept.deptID = g.properties ->> '$.departmentId' | |
| INNER JOIN academic_term act ON | |
| act.id = eerb.properties ->> '$.academicTermId' AND act.id = csats.academic_term_id | |
| INNER JOIN program p ON | |
| p.id = g.properties ->> '$.programId' | |
| INNER JOIN degree deg ON | |
| deg.id = p.degree_id | |
| INNER JOIN `course_type` ct ON | |
| ct.courseTypeID = p.course_type_id | |
| -- LEFT JOIN valuation_method vm_grade ON | |
| -- CAST(vm_grade.identifying_context->>'$.syllabusAcademicTermSettingsId' AS CHAR) = csats.id AND vm_grade.type='SYLLABUS_ACADEMIC_TERM' | |
| -- LEFT JOIN valuation_method vm ON | |
| -- aps.id = vm.identifying_context->>'$.academicPaperSubjectId' | |
| -- AND vm.`type` = 'ACADEMIC_PAPER_SUBJECT' | |
| -- AND vm.identifying_context->>'$.passCriteriaType' = 'INTERNAL' | |
| -- LEFT JOIN valuation_method vm1 ON | |
| -- aps.id = vm1.identifying_context->>'$.academicPaperSubjectId' | |
| -- AND vm1.`type` = 'ACADEMIC_PAPER_SUBJECT' | |
| -- AND vm1.identifying_context->>'$.passCriteriaType' = 'EXTERNAL' | |
| -- LEFT JOIN valuation_method vm2 ON | |
| -- aps.id = vm2.identifying_context->>'$.academicPaperSubjectId' | |
| -- AND vm2.`type` = 'ACADEMIC_PAPER_SUBJECT' | |
| -- AND vm2.identifying_context->>'$.passCriteriaType' = 'AGGREGATE' | |
| WHERE 1=1 AND eer.trashed IS NULL AND eer.type = 'REGULAR'"; | |
| $subjectDetails = $this->executeQueryForList($query.$whereQuery.$orderBy.$limit,$this->mapper[StudentsOverAllMarkReportServiceMapper::BATCH_SUBJECTS]); | |
| } | |
| catch (\Exception $e) | |
| { | |
| throw new ExamControllerException($e->getCode(),$e->getMessage()); | |
| } | |
| return $subjectDetails; | |
| } | |
| /** | |
| *delete student edit status by request | |
| * @param $request | |
| * @return status | |
| */ | |
| public function deleteStudentEditstatus($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $whereQuery = ""; | |
| try{ | |
| if ( $request->isStudentWiseImport ){ | |
| $sql = "DELETE FROM | |
| ec_cosolidated_mark_generate_student_edit_status | |
| WHERE student_id = '$request->studentId' "; | |
| } | |
| else{ | |
| $sql = "DELETE FROM | |
| ec_cosolidated_mark_generate_student_edit_status | |
| WHERE groups_id = '$request->groupId' "; | |
| } | |
| $status = $this->executeQueryForObject($sql.$whereQuery); | |
| }catch (\Exception $e){ | |
| return false; | |
| // throw new ExamControllerException($e->getCode(),$e->getMessage()); | |
| } | |
| return $status; | |
| } | |
| /** | |
| * get Assigned Subject Students Details For Internal Mark Entry | |
| * @param $searchRequest | |
| * @return $subjectWiseInternalMarkDetails | |
| */ | |
| public function getStudentFinalizedRevaluationMark($searchRequest) { | |
| $searchRequest = $this->realEscapeObject($searchRequest); | |
| try | |
| { | |
| $orderBy = " ORDER BY s.name ASC , sa.regNo ASC"; | |
| $whereQuery = ""; | |
| if(!empty($searchRequest->revaluationId)) { | |
| $whereQuery .= " AND eer.id IN ( '$searchRequest->revaluationId' )"; | |
| } | |
| if(!empty($searchRequest->revaluationType)) { | |
| $whereQuery .= " AND esar.identifying_context->>'$.revaluationType' IN ( '$searchRequest->revaluationType' )"; | |
| } | |
| if(!empty($searchRequest->studentId)) { | |
| $whereQuery .= " AND esar.student_id IN ( '$searchRequest->studentId' )"; | |
| } | |
| if(!empty($searchRequest->assessmentId)) { | |
| $whereQuery .= " AND esar.am_assessment_id IN ( '$searchRequest->assessmentId' )"; | |
| } | |
| if(!empty($searchRequest->orderByHighestMark)){ | |
| $orderBy = " ORDER BY ostm.mark_obtained DESC"; | |
| } | |
| $query = "SELECT | |
| g.id AS groupId, | |
| g.name AS groupName, | |
| g.properties ->>'$.programId' AS programId, | |
| caps.id AS academicPaperSubjectId, | |
| caps.properties ->> '$.isInternal' as isInternal, | |
| caps.properties ->> '$.isExternal' as isExternal, | |
| caps.properties ->> '$.externalMaxMark' as externalMaxMark, | |
| caps.properties ->> '$.internalMaxMark' as internalMaxMark, | |
| eerbParent.properties ->> '$.academicTermId' AS academicTermId, | |
| eerbParent.ec_exam_registration_id AS parentExamRegistrationId, | |
| eer2.`type` AS parentExamRegistrationType, | |
| eer.id AS revaluationId, | |
| s.id AS subjectId, | |
| s.code AS subjectCode, | |
| s.name AS subjectName, | |
| spa.student_id AS studentId, | |
| sa.studentName AS studentName, | |
| spa.properties->>'$.rollNumber' AS studentRollNo, | |
| spa.properties->>'$.registerNumber' AS studentRegisterNo, | |
| esar.am_assessment_id AS assessmentId, | |
| oe.id AS oeExamId, | |
| eer.id AS revaluationId, | |
| esar.identifying_context->>'$.revaluationType' AS revaluationType, | |
| CONCAT( caps.id, CAST(esar.identifying_context->>'$.revaluationType'AS CHAR) ) as revalPaperSubjectId, | |
| ef.examFeesName AS revaluationTypeName, | |
| ostm.mark_obtained AS revaluationMark, | |
| eer.id AS revaluationId | |
| FROM | |
| ec_student_assessment_registration esar | |
| INNER JOIN ec_exam_registration eer ON | |
| eer.`type` = esar.ec_exam_registration_type AND eer.id = esar.identifying_context->>'$.examRegistrationId' | |
| INNER JOIN ec_exam_registration_batch eerb ON | |
| eerb.ec_exam_registration_id = eer.id | |
| INNER JOIN ec_exam_registration eer2 ON | |
| eer2.id = eer.properties->>'$.parentExamRegistrationId' AND | |
| eer2.trashed IS NULL | |
| INNER JOIN ec_exam_registration_batch eerbParent ON | |
| eerbParent.ec_exam_registration_id = eer2.id AND | |
| eerb.groups_id = eerbParent.groups_id | |
| INNER JOIN ec_exam_registration_subject eers2 ON | |
| eers2.ec_exam_registration_batch_id = eerbParent.id | |
| INNER JOIN cm_academic_paper_subjects caps ON | |
| caps.id = eers2.cm_academic_paper_subjects_id AND eers2.am_assessment_id = esar.am_assessment_id | |
| INNER JOIN oe_exams oe ON | |
| oe.assessment_id = esar.am_assessment_id AND oe.is_deleted = 0 | |
| INNER JOIN v4_ams_subject s ON | |
| s.id = caps.ams_subject_id | |
| INNER JOIN `groups` g ON | |
| g.id = eerb.groups_id | |
| AND g.`type` = 'BATCH' | |
| INNER JOIN department d ON | |
| d.deptID = g.properties ->> '$.departmentId' | |
| INNER JOIN studentaccount sa ON | |
| sa.studentID = esar.student_id | |
| INNER JOIN program p ON | |
| p.id = g.properties->>'$.programId' | |
| INNER JOIN student_program_account spa ON | |
| spa.current_program_id = p.id AND | |
| spa.student_id = esar.student_id | |
| INNER JOIN exam_feestype ef ON | |
| ef.examfeesID = esar.identifying_context->>'$.revaluationType' | |
| INNER JOIN oe_student_total_mark ostm ON | |
| ostm.student_id = sa.studentID AND ostm.am_assessment_id = esar.am_assessment_id AND ostm.valuation_type = 'REVALUATION' AND ostm.valuation_count = 'FINALIZED' | |
| WHERE eer.type = 'REVALUATION' AND esar.properties ->>'$.feeStatus' = 'PAID' AND esar.properties ->>'$.registrationStatus' = 'REGISTERED' AND eerb.properties ->>'$.isRequiredForImport' = 1 AND eer.trashed IS NULL "; | |
| if( $searchRequest->skipMapper ){ | |
| $subjectWiseMarkDetails = $this->executeQueryForList($query.$whereQuery.$orderBy); | |
| } | |
| else{ | |
| $subjectWiseMarkDetails = $this->executeQueryForList($query.$whereQuery.$orderBy, $this->mapper2[ExamRevaluationServiceMapper::GET_REVALUATION_STUDENT_SUBJETCS]); | |
| } | |
| } | |
| catch (\Exception $e){ | |
| return false; | |
| // throw new ExamControllerException($e->getCode(),$e->getMessage()); | |
| } | |
| return $subjectWiseMarkDetails; | |
| } | |
| /** | |
| * get valuation details by request | |
| * @param $request | |
| * @return status | |
| */ | |
| public function getValuationMethodDetailsByRequest($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $whereQuery = ""; | |
| if(!empty($request->syllabusTermId)) { | |
| $whereQuery .= " AND vm.identifying_context->>'$.syllabusAcademicTermSettingsId' = '$request->syllabusTermId' AND vm.type='SYLLABUS_ACADEMIC_TERM'"; | |
| } | |
| else if(!empty($request->academicPaperSubjectId)) { | |
| $whereQuery .= " AND vm.identifying_context->>'$.academicPaperSubjectId' = '$request->academicPaperSubjectId' AND vm.`type` = 'ACADEMIC_PAPER_SUBJECT'"; | |
| } | |
| try{ | |
| $sql = "SELECT | |
| id, | |
| identifying_context, | |
| properties | |
| FROM | |
| valuation_method vm | |
| WHERE 1=1 "; | |
| $status = $this->executeQueryForList($sql.$whereQuery); | |
| }catch (\Exception $e){ | |
| throw new ExamControllerException($e->getCode(),$e->getMessage()); | |
| } | |
| return $status; | |
| } | |
| /** | |
| * calculate institution average | |
| * adding students in queue | |
| * @param $searchRequest | |
| */ | |
| public function calculateInstitutionAverage($searchRequest){ | |
| try{ | |
| $searchRequest = $this->realEscapeObject($searchRequest); | |
| $taskQueRequest = new ConsolidatedOverAllMarkReportRequest(); | |
| $taskQueRequest->isConsolidatedRequest = false; | |
| $taskQueRequest->examRegistrationType = "REGULAR"; | |
| $taskQueRequest->isRoundOff = 1; | |
| $taskQueRequest->considerSupplyImproveCombined = false; | |
| $taskQueRequest->examRegistrationId = $searchRequest->examRegistrationId; | |
| $taskQueRequest->considerSupplementary = FALSE; | |
| $taskQueRequest->considerImprovement = FALSE; | |
| $taskQueRequest->considerRevaluation = TRUE; | |
| $taskQueRequest->considerGraceMark = TRUE; | |
| $taskQueRequest->fetchOnlyGpa = $searchRequest->fetchOnlyGpa ; | |
| $taskQueRequest->fetchOnlyMark = $searchRequest->fetchOnlyMark; | |
| $taskQueRequest->staffId = $searchRequest->staffId; | |
| $studentRegularExamDetails = $this->getStudentExamMarkDetails($taskQueRequest); | |
| if(empty($studentRegularExamDetails)){ | |
| throw new ExamControllerException(ExamControllerException::NO_DETAILS_FOUND,"No Details Found"); | |
| } | |
| $studentDetails = Template3MigrationdResultGenerator::getInstance()->getStudentsOverallMarkReport($taskQueRequest, $studentRegularExamDetails); | |
| foreach($studentDetails as $subjectId => $student){ | |
| $institutionalAvg = round(array_sum($student->students) / count($student->students), 2); | |
| $student->institutionalAvg = $institutionalAvg; | |
| } | |
| if( $studentDetails ){ | |
| ExamRegistrationSubjectService::getInstance()->updateInstitutionalAverageToExamRegSubjects($studentDetails); | |
| } | |
| $searchRequest->status = StatusConstants::SUCCESS; | |
| ExamRegistrationService::getInstance()->saveInstitutionalAverageGenerateStatus($searchRequest); | |
| } | |
| catch(\Exception $e) { | |
| $searchRequest->status = StatusConstants::FAILED; | |
| ExamRegistrationService::getInstance()->saveInstitutionalAverageGenerateStatus($searchRequest); | |
| } | |
| } | |
| public function getBatchStudentsForImportMarks($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $whereQuery = ""; | |
| $limitQuery = ""; | |
| $orderByQuery = ""; | |
| if(!empty($request->groupId)) { | |
| $whereQuery .= " AND bg.id='$request->groupId' "; | |
| } | |
| $orderByQuery = " ORDER BY spa.properties->>'$.registerNumber' "; | |
| $query = "SELECT | |
| st.studentID AS studentId, | |
| st.studentName, | |
| spa.properties->>'$.registerNumber' AS registerNo, | |
| spa.properties->>'$.rollNumber' AS rollNo | |
| 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 | |
| WHERE | |
| 1 = 1 "; | |
| 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; | |
| } | |
| /** | |
| * get batch assigned exam registration | |
| * @param $request | |
| * @return examRegistration | |
| */ | |
| public function getBatchAssignedExamRegistrationForImport($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $whereQuery = ""; | |
| $sortBy = " ORDER BY eer.properties ->> '$.examYear' DESC ,eer.properties ->> '$.examMonth' + 0 DESC "; | |
| $whereQuery = ""; | |
| $groupBy = " GROUP BY eer.id"; | |
| if(!empty($request->groupId)){ | |
| $whereQuery .= " AND eerb.groups_id = '$request->groupId' "; | |
| } | |
| if(!empty($request->studentId)){ | |
| $whereQuery .= " AND esar.student_id = '$request->studentId' "; | |
| } | |
| $query = "SELECT | |
| eer.id AS id, | |
| eer.name AS name, | |
| eerb.id AS batchRelationId, | |
| eerb.properties->>'$.isRequiredForImport' AS statusFlag, | |
| eer.type | |
| FROM | |
| ec_exam_registration eer | |
| INNER JOIN ec_exam_registration_batch eerb ON | |
| eerb.ec_exam_registration_id = eer.id | |
| 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 | |
| WHERE | |
| eer.trashed IS NULL AND eerb.properties->>'$.isRequiredForImport' = 1"; | |
| try { | |
| $examRegistration = $this->executeQueryForList($query.$whereQuery.$groupBy.$sortBy); | |
| } catch (\Exception $e) { | |
| throw new ExamControllerException(ExamControllerException::ERROR_FETCHING_EXAM_REGISTRATION,"Cannot fetch Exam Registration details! Please try again."); | |
| } | |
| return $examRegistration; | |
| } | |
| /** | |
| * get All Student details for sgpa direct entry | |
| * @param $request | |
| * @return studentDetails | |
| */ | |
| public function getStudentsForImportStudentWiseMark($request){ | |
| $request = $this->realEscapeObject($request); | |
| try{ | |
| $sortBy = " ORDER BY spa.properties->>'$.registerNumber' ASC "; | |
| $whereQuery = ""; | |
| if(!empty($request->groupId)){ | |
| $groupIdString = is_array($request->groupId) ? "'" . implode("','",$request->groupId) . "'" : "'".$request->groupId."'"; | |
| $whereQuery .= " AND bg.id IN ( $groupIdString )"; | |
| } | |
| if(!empty($request->studentId)) { | |
| $whereQuery .= " AND st.studentID = '$request->studentId'"; | |
| } | |
| $query = "SELECT | |
| st.studentID AS studentId, | |
| st.studentName AS name, | |
| spa.properties->>'$.registerNumber' AS registerNo, | |
| spa.properties->>'$.rollNumber' AS rollNumber, | |
| bg.id AS groupId, | |
| bg.name AS groupName, | |
| COALESCE(esmgs.generate_status->>'$.status', ecmg.generate_status->>'$.status') AS generateStatus, | |
| ecmgses.is_dirty AS isEdited | |
| 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 | |
| LEFT JOIN ec_student_mark_generate_status esmgs ON esmgs.student_id = st.studentID | |
| LEFT JOIN ec_cosolidated_mark_generate ecmg ON ecmg.groups_id = spa.current_batch_id | |
| LEFT JOIN ec_cosolidated_mark_generate_student_edit_status ecmgses ON ecmgses.student_id = st.studentID | |
| WHERE | |
| 1 = 1 "; | |
| $studentDetails = $this->executeQueryForList($query.$whereQuery.$sortBy); | |
| } | |
| catch(\Exception $e) { | |
| throw new ExamControllerException ($e->getCode(),$e->getMessage()); | |
| } | |
| return $studentDetails; | |
| } | |
| /** | |
| * updated generate status for mark import | |
| * @param $request | |
| * @param ConsolidatedMarkGenerateStatus $generateStatus | |
| */ | |
| public function updateGenerateStatusByStudentWIse($request, ConsolidatedMarkGenerateStatus $generateStatus){ | |
| $request = $this->realEscapeObject($request); | |
| $updateSql = []; | |
| if (StatusConstants::FAILED == $generateStatus->status){ | |
| $generateStatus->isFailedStatus = true; | |
| } | |
| else if (StatusConstants::SUCCESS == $generateStatus->status){ | |
| $generateStatus->isFailedStatus = false; | |
| } | |
| else if (StatusConstants::PARTIALLY_COMPLETED == $generateStatus->status){ | |
| $generateStatus->isFailedStatus = true; | |
| } | |
| else if (StatusConstants::PROCESSING == $generateStatus->status){ | |
| $generateStatus->isFailedStatus = false; | |
| } | |
| else if ("NOT GENERATED" == $generateStatus->status){ | |
| $generateStatus->isFailedStatus = true; | |
| } | |
| if ($generateStatus->status !== null && isset($generateStatus->status)) { | |
| $updateSql[] = "generate_status = JSON_SET(generate_status,'$.status','$generateStatus->status')"; | |
| } | |
| else{ | |
| $generateStatus->status = "NOT GENERATED"; | |
| } | |
| if ($generateStatus->isFailedStatus !== null && isset($generateStatus->isFailedStatus)) { | |
| if ($generateStatus->isFailedStatus !== true) { | |
| $updateSql[] = "generate_status = JSON_SET(generate_status,'$.failedStatusReasons',JSON_ARRAY())"; | |
| $updateSql[] = "generate_status = JSON_SET(generate_status,'$.isFailedStatus',false)"; | |
| } | |
| else{ | |
| $updateSql[] = "generate_status = JSON_SET(generate_status,'$.isFailedStatus',true)"; | |
| } | |
| } | |
| else{ | |
| $generateStatus->isFailedStatus = false; | |
| } | |
| $id = SecurityUtils::getRandomString(); | |
| $generateStatusJSON = json_encode($generateStatus); | |
| if (!empty($updateSql)) { | |
| $updateSql[] = "groups_id = VALUES(groups_id)"; | |
| $updateSqlString = implode(",", $updateSql); | |
| $updateSqlString = "ON DUPLICATE KEY UPDATE | |
| $updateSqlString"; | |
| } | |
| try { | |
| $sql = "INSERT INTO | |
| ec_student_mark_generate_status (id, student_id, groups_id, generate_status, created_by) | |
| VALUES ('$id','$request->studentId','$request->groupId','$generateStatusJSON',$request->staffId) | |
| $updateSqlString"; | |
| $this->executeQuery($sql); | |
| $currentStaffId = $request->staffId; | |
| AMSLogger::log_info($this->logger,Events::EC_SAVE_BATCHWISE_MARK_IMPORTED_STATUS_STUDENT_WISE,[ | |
| "staff" => new Staff(["id" => $currentStaffId]), | |
| "request" => $request, | |
| "status" => StatusConstants::SUCCESS | |
| ]); | |
| } | |
| catch(\Exception $e) { | |
| throw new ExamControllerException("STATUS_UPDATE_ERROR",$e->getMessage()); | |
| AMSLogger::log_error($this->logger,Events::EC_SAVE_BATCHWISE_MARK_IMPORTED_STATUS_STUDENT_WISE,[ | |
| "staff" => new Staff(["id" => $currentStaffId]), | |
| "request" => $request, | |
| "errorCode" => $e->getCode(), | |
| "errorMessage" => $e->getMessage(), | |
| "status" => StatusConstants::FAILED | |
| ]); | |
| } | |
| } | |
| /** | |
| * get published revaluationids | |
| * @param $request | |
| * @return $exams | |
| */ | |
| public function getPublishedRevaluationExamRegistrationByStudentId($request){ | |
| $request = $this->realEscapeObject($request); | |
| $whereQuery = ""; | |
| if($request->academicTermId){ | |
| $whereQuery = " AND eerb.properties->>'$.academicTermId' = $request->academicTermId"; | |
| } | |
| if($request->considerPublishedExams){ | |
| $whereQuery = " AND eerb.properties->>'$.publish' = 'true'"; | |
| } | |
| try{ | |
| $query = "SELECT | |
| DISTINCT eer.id, | |
| eerb.properties->>'$.academicTermId' AS academicTermId, | |
| eer.name as examRegistrationName | |
| FROM | |
| ec_student_assessment_registration esar | |
| INNER JOIN ec_exam_registration eer ON | |
| eer.`type` = esar.ec_exam_registration_type AND | |
| eer.id = esar.identifying_context->>'$.examRegistrationId' | |
| INNER JOIN ec_exam_registration_batch eerb ON | |
| eerb.ec_exam_registration_id = eer.id | |
| WHERE | |
| student_id = $request->studentId AND esar.ec_exam_registration_type = eer.type | |
| AND eer.`type` ='REVALUATION' | |
| "; | |
| $exams = $this->executeQueryForList($query.$whereQuery); | |
| } | |
| catch (\Exception $e){ | |
| return false; | |
| // throw new ExamControllerException($e->getCode(),$e->getMessage()); | |
| } | |
| return $exams; | |
| } | |
| /** | |
| * get Assigned Subject Students Details For Internal Mark Entry | |
| * @param $searchRequest | |
| * @return $subjectWiseInternalMarkDetails | |
| */ | |
| public function getStudentRevaluationMark($searchRequest) { | |
| $searchRequest = $this->realEscapeObject($searchRequest); | |
| try | |
| { | |
| $orderBy = " ORDER BY s.name ASC , sa.regNo ASC"; | |
| $whereQuery = ""; | |
| if(!empty($searchRequest->revaluationId)) { | |
| $whereQuery .= " AND eer.id IN ( '$searchRequest->revaluationId' )"; | |
| } | |
| if(!empty($searchRequest->revaluationType)) { | |
| $whereQuery .= " AND esar.identifying_context->>'$.revaluationType' IN ( '$searchRequest->revaluationType' )"; | |
| } | |
| if(!empty($searchRequest->studentId)) { | |
| $whereQuery .= " AND esar.student_id IN ( '$searchRequest->studentId' )"; | |
| } | |
| if(!empty($searchRequest->assessmentId)) { | |
| $whereQuery .= " AND esar.am_assessment_id IN ( '$searchRequest->assessmentId' )"; | |
| } | |
| if(!empty($searchRequest->orderByHighestMark)){ | |
| $orderBy = " ORDER BY ostm.mark_obtained DESC"; | |
| } | |
| $query = "SELECT | |
| sa.studentID AS studentId, | |
| sa.studentName, | |
| sa.regNo, | |
| sa.rollNo, | |
| sa.admissionNo, | |
| sa.myImage, | |
| sa.studentGender, | |
| g.id AS groupId, | |
| g.name AS groupName, | |
| act.id AS academicTermId, | |
| act.name AS academicTermName, | |
| act.properties ->> '$.orderNo' AS academicTermOrderNo, | |
| ct.courseTypeID, | |
| ct.typeName AS courseTypeName, | |
| ct.course_Type AS courseType, | |
| g.properties ->> '$.startYear' AS batchStartYear, | |
| g.properties ->> '$.endYear' AS batchEndYear, | |
| g.properties ->> '$.finalTermId' AS finalTermId, | |
| eer.id AS examRegistrationId, | |
| eer.name AS examRegistartionName, | |
| eer.type AS examRegType, | |
| eer2.properties ->> '$.examYear' AS examYear, | |
| eer2.properties ->> '$.examMonth' AS examMonth, | |
| eer.properties ->> '$.description' AS examRegDesc, | |
| eer.properties ->> '$.publish' AS publishsubjectRegistrationType, | |
| s.code AS subjectCode, | |
| caps.properties ->> '$.syllabusName' AS syllabusName, | |
| s.id AS amsSubjectId, | |
| s.code AS subjectName, | |
| s.name AS subjectDesc, | |
| IF(caps.properties ->> '$.classType' = 'THEORY',1,0) AS isTheory, | |
| aa.id AS assessmentId, | |
| aa.name AS assessmentName, | |
| aa.description AS assessmentDescription, | |
| eers2.cm_academic_paper_subjects_id AS academicPaperSubjectId, | |
| eim.id AS internalMarkId, | |
| eim.internal_mark AS internalMark, | |
| eim.attendance_status AS internalAttendanceStatus, | |
| esar.properties ->> '$.registrationType' AS studentExamRegType, | |
| esar.properties ->> '$.studentAttendanceStatus' AS studentAttendanceStatus, | |
| caps.properties ->> '$.credit' AS subjectCredit, | |
| caps.properties ->> '$.isInternal' AS isInternal, | |
| caps.properties ->> '$.isExternal' AS isExternal, | |
| caps.properties ->> '$.excludeSubjectFromTotal' AS excludeSubjectFromTotal, | |
| caps.properties ->> '$.order' AS subjectOrder, | |
| caps.properties ->> '$.priority' AS subjectPriority, | |
| caps.properties ->> '$.externalMaxMark' AS externalMaxMark, | |
| caps.properties ->> '$.internalMaxMark' AS internalMaxMark, | |
| ostm.mark_obtained AS externalMark, | |
| ostm.oe_exams_id AS oeExamId, | |
| ostm.attendance_status AS externalAttendanceStatus, | |
| ostm.internal_mark AS improvedInternalMark, | |
| ostm.internal_attendance_status AS improvedInternalAttendanceStatus, | |
| ostm.internal_carried_status AS improvedInternalCarriedStatus, | |
| ostm.internal_carried_status AS improvedInternalCarriedStatus, | |
| ostm.exam_additional_marks->>'$.mark1' AS examAdditionalMark1, | |
| ostm.properties ->> '$.hasCondonation' AS hasCondonationGraceMark | |
| FROM | |
| ec_student_assessment_registration esar | |
| INNER JOIN ec_exam_registration eer ON | |
| eer.`type` = esar.ec_exam_registration_type AND eer.id = esar.identifying_context->>'$.examRegistrationId' | |
| INNER JOIN ec_exam_registration_batch eerb ON | |
| eerb.ec_exam_registration_id = eer.id | |
| INNER JOIN ec_exam_registration eer2 ON | |
| eer2.id = eer.properties->>'$.parentExamRegistrationId' AND | |
| eer2.trashed IS NULL | |
| INNER JOIN ec_exam_registration_batch eerbParent ON | |
| eerbParent.ec_exam_registration_id = eer2.id AND | |
| eerb.groups_id = eerbParent.groups_id | |
| INNER JOIN ec_exam_registration_subject eers2 ON | |
| eers2.ec_exam_registration_batch_id = eerbParent.id | |
| INNER JOIN cm_academic_paper_subjects caps ON | |
| caps.id = eers2.cm_academic_paper_subjects_id AND eers2.am_assessment_id = esar.am_assessment_id | |
| INNER JOIN oe_exams oe ON | |
| oe.identifying_context ->> '$.assessmentId' = esar.am_assessment_id AND oe.is_deleted = 0 | |
| INNER JOIN v4_ams_subject s ON | |
| s.id = caps.ams_subject_id | |
| INNER JOIN `groups` g ON | |
| g.id = eerb.groups_id | |
| AND g.`type` = 'BATCH' | |
| INNER JOIN department d ON | |
| d.deptID = g.properties ->> '$.departmentId' | |
| INNER JOIN studentaccount sa ON | |
| sa.studentID = esar.student_id | |
| INNER JOIN program p ON | |
| p.id = g.properties->>'$.programId' | |
| INNER JOIN student_program_account spa ON | |
| spa.current_program_id = p.id AND | |
| spa.student_id = esar.student_id | |
| INNER JOIN am_assessment aa ON | |
| aa.id = esar.am_assessment_id | |
| INNER JOIN degree deg ON | |
| deg.id = p.degree_id | |
| INNER JOIN `course_type` ct ON | |
| ct.courseTypeID = p.course_type_id | |
| INNER JOIN academic_term act ON | |
| act.id = eerb.properties ->> '$.academicTermId' | |
| INNER JOIN exam_feestype ef ON | |
| ef.examfeesID = esar.identifying_context->>'$.revaluationType' | |
| INNER JOIN oe_student_total_mark ostm ON | |
| ostm.student_id = sa.studentID AND ostm.am_assessment_id = esar.am_assessment_id AND ostm.valuation_type = 'REVALUATION' AND ostm.valuation_count = 'FINALIZED' | |
| LEFT JOIN ec_internal_marks eim ON | |
| eim.program_id = p.id AND eim.student_id = sa.studentID AND eim.academic_paper_subjects_id =caps.id | |
| WHERE eer.type = 'REVALUATION' AND esar.properties ->>'$.feeStatus' = 'PAID' AND esar.properties ->>'$.registrationStatus' = 'REGISTERED' AND eerb.properties ->>'$.isRequiredForImport' = 1 AND eer.trashed IS NULL "; | |
| $studentMarkDetails = $this->executeQueryForList($query.$whereQuery.$orderBy,$this->mapper[StudentsOverAllMarkReportServiceMapper::OVER_ALL_MARK_DETAILS]); | |
| } | |
| catch (\Exception $e){ | |
| return false; | |
| // throw new ExamControllerException($e->getCode(),$e->getMessage()); | |
| } | |
| return $studentMarkDetails; | |
| } | |
| /** | |
| * Retrieves the subject types based on the search request. | |
| * | |
| * @param object $searchRequest The search request object containing the filters. | |
| * @return array|bool The list of subject types or false if an exception occurs. | |
| */ | |
| public function getSubjectCategoryByRequest($searchRequest) { | |
| $searchRequest = $this->realEscapeObject($searchRequest); | |
| $whereQuery = ""; | |
| if (!empty($searchRequest->studentId)) { | |
| $whereQuery .= " AND esar.student_id IN ('$searchRequest->studentId')"; | |
| } | |
| if (!empty($searchRequest->subjectCategory)) { | |
| $whereQuery .= " AND sc.subjectcatName = '$searchRequest->subjectCategory'"; | |
| } | |
| try { | |
| $query = "SELECT caps.id FROM cm_academic_paper_subjects caps | |
| 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 | |
| INNER JOIN subject_category sc ON sc.subjectcatID = caps.category_id | |
| WHERE 1=1 "; | |
| $subjectTypes = $this->executeQueryForList($query. $whereQuery); | |
| $subjectTypeList = []; | |
| foreach ($subjectTypes as $subjectType) { | |
| $subjectTypeList[$subjectType->id] = $subjectType->id; | |
| } | |
| } catch (\Exception $e) { | |
| return false; | |
| // throw new ExamControllerException($e->getCode(),$e->getMessage()); | |
| } | |
| return $subjectTypeList; | |
| } | |
| public function getSubCourseRelationBystudent($searchRequest) { | |
| $searchRequest = $this->realEscapeObject($searchRequest); | |
| $whereQuery = ""; | |
| if (!empty($searchRequest->studentId)) { | |
| $whereQuery .= " AND esar.student_id IN ('$searchRequest->studentId')"; | |
| } | |
| try { | |
| $query = "SELECT | |
| vsrm.parent_subject_id AS parentSubjectId, | |
| vsrm.child_subject_id AS childSubjectId, | |
| vas.code AS parentCode, | |
| vas.name AS parentName | |
| 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 | |
| INNER JOIN cm_academic_paper_subjects caps ON | |
| caps.id = eers.cm_academic_paper_subjects_id | |
| INNER JOIN v4_subject_relation_mapping vsrm ON | |
| vsrm.child_subject_id = caps.ams_subject_id | |
| INNER JOIN v4_ams_subject vas ON | |
| vas.id = vsrm.parent_subject_id | |
| WHERE | |
| eer.`type` = 'REGULAR'"; | |
| $subjectList = $this->executeQueryForList($query. $whereQuery); | |
| $subCourseSubjects = []; | |
| foreach ($subjectList as $subject) { | |
| $subCourseSubjects[$subject->childSubjectId]->childSubjectId = $subject->childSubjectId; | |
| $subCourseSubjects[$subject->childSubjectId]->parentSubjectId = $subject->parentSubjectId; | |
| $subCourseSubjects[$subject->childSubjectId]->parentCode = $subject->parentCode; | |
| $subCourseSubjects[$subject->childSubjectId]->parentName = $subject->parentName; | |
| } | |
| } catch (\Exception $e) { | |
| return false; | |
| // throw new ExamControllerException($e->getCode(),$e->getMessage()); | |
| } | |
| return $subCourseSubjects; | |
| } | |
| /** | |
| * Retrieves the details of students with low pass grades based on the provided search request. | |
| * | |
| * @param object $searchRequest The search request object containing the criteria for fetching student details. | |
| * - studentId: (optional) The ID of the student to filter the results. | |
| * | |
| * @return array|false An associative array containing student IDs and their respective subject details if successful, | |
| * or false if an exception occurs. | |
| * | |
| * @throws \Exception If an error occurs during the query execution. | |
| */ | |
| public function getStudentLowPassGradeDetails($searchRequest) { | |
| $searchRequest = $this->realEscapeObject($searchRequest); | |
| $whereQuery = ""; | |
| if (!empty($searchRequest->studentId)) { | |
| $whereQuery .= " AND student_id IN ('$searchRequest->studentId')"; | |
| } | |
| try { | |
| $query = " SELECT student_id as studentId, cm_academic_paper_subjects_id as paperSubjectId | |
| FROM ec_student_subject_term_submissions WHERE submission_status = 'APPROVED'"; | |
| $studentList = $this->executeQueryForList($query. $whereQuery); | |
| $lowPassList = []; | |
| foreach ($studentList as $subject) { | |
| $lowPassList[$subject->studentId]->studentId = $subject->studentId; | |
| $lowPassList[$subject->studentId]->subjects[$subject->paperSubjectId]->subjectId = $subject->paperSubjectId; | |
| } | |
| } catch (\Exception $e) { | |
| return false; | |
| // throw new ExamControllerException($e->getCode(),$e->getMessage()); | |
| } | |
| return $lowPassList; | |
| } | |
| } |