Code Coverage
 
Classes and Traits
Functions and Methods
Lines
Total
0.00% covered (danger)
0.00%
0 / 1
0.00% covered (danger)
0.00%
0 / 10
 CRAP
0.00% covered (danger)
0.00%
0 / 866
MarkReportService
0.00% covered (danger)
0.00%
0 / 1
0.00% covered (danger)
0.00%
0 / 10
 14280.00
0.00% covered (danger)
0.00%
0 / 866
 __construct
0.00% covered (danger)
0.00%
0 / 1
 2.00
0.00% covered (danger)
0.00%
0 / 2
 getStudentRegularExamMarkDetails
0.00% covered (danger)
0.00%
0 / 1
 420.00
0.00% covered (danger)
0.00%
0 / 177
 getOverallMarkDetails
0.00% covered (danger)
0.00%
0 / 1
 552.00
0.00% covered (danger)
0.00%
0 / 166
 getStudentMarkHistory
0.00% covered (danger)
0.00%
0 / 1
 812.00
0.00% covered (danger)
0.00%
0 / 236
 getAllStudentPassStatus
0.00% covered (danger)
0.00%
0 / 1
 30.00
0.00% covered (danger)
0.00%
0 / 41
 getGraduationRateDetails
0.00% covered (danger)
0.00%
0 / 1
 110.00
0.00% covered (danger)
0.00%
0 / 57
 getCourseWiseSuccessRateForDashBoard
0.00% covered (danger)
0.00%
0 / 1
 72.00
0.00% covered (danger)
0.00%
0 / 46
 getRegisteredStudentDetailsByExamRegistrationId
0.00% covered (danger)
0.00%
0 / 1
 72.00
0.00% covered (danger)
0.00%
0 / 42
 getRevaluationAppliedDetailsByExamRegistrationId
0.00% covered (danger)
0.00%
0 / 1
 72.00
0.00% covered (danger)
0.00%
0 / 48
 getRevaluationMarkEnteredDetailsByExamRegistrationId
0.00% covered (danger)
0.00%
0 / 1
 72.00
0.00% covered (danger)
0.00%
0 / 51
<?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\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\core\ams\professional\util\CommonUtil;
use com\linways\ec\core\mapper\DashBoardServiceMapper;
class MarkReportService extends BaseService
{
    use MakeSingletonTrait;
    private function __construct() {
        $this->mapper = DashBoardServiceMapper::getInstance()->getMapper();
    }
     /**
     * Get regular exam mark details of a student by register number
     */
    public function getStudentRegularExamMarkDetails( ConsolidatedMarkReportRequest $request )
    {
        $request = $this->realEscapeObject($request);
        $regularExamMarkDetails = [];
        $condition = null;
        $failedBatchId = BatchService::getInstance()->getFailedBatchId();
        try {
            if ($request->regNo) {
                $condition .= " AND sa.regNo = '" . $request->regNo . "' ";
            }
            if ($request->studentId) {
                $condition .= " AND sa.studentID IN (" . $request->studentId . ") ";
            }
            if ($request->batchId) {
                if($request->serviceAction == 'REGULAR_MARKLIST'){
                    $condition .= " AND e.batchID IN (" . $request->batchId . ") ";
                }else if($request->serviceAction == 'CONSOLIDATED_MARKLIST'){
                    $condition .= " AND e.batchID IN (" . $request->batchId . ") ";
                    $condition .= " AND sa.batchID IN (" . $request->batchId . ", $failedBatchId) ";
                }else{
                    $condition .= " AND sa.batchID IN (" . $request->batchId . ") ";
                }
            }
            if ($request->semId) {
                $semIdString = is_array($request->semId) ? implode(',',$request->semId) : $request->semId;
                $condition .= " AND sem.semID IN (" . $semIdString . ") ";
            }
            if ($request->examRegId) {
                $condition .= " AND er.examregID IN (" . $request->examRegId . ") ";
            }
            if ($request->coursePatternId) {
                $condition .= " AND b.patternID IN (" . $request->coursePatternId . ") ";
            }
            if ($request->subjectIds) {
                $subjectIdString = is_array($request->subjectIds) ? implode(',',$request->subjectIds) : $request->subjectIds;
                $condition .= " AND erss.subjectID IN ($subjectIdString) ";
            }
            if ($request->gender) {
                $condition .= " AND sa.studentGender LIKE '$request->gender' ";
            }
            if ($request->seatReservationId) {
                // reservID in studentaccount Table
                $seatReservationIdString = is_array($request->seatReservationId) ? implode(',',$request->seatReservationId) : $request->seatReservationId;
                $condition .= " AND sa.reservID IN ($seatReservationIdString) ";
            }
            if ($request->religionId) {
                $religionIdString = is_array($request->religionId) ? implode(',',$request->religionId) : $request->religionId;
                $condition .= " AND sa.religion IN ($religionIdString) ";
            }
            if ($request->campusTypeId) {
                $campusTypeIdString = is_array($request->campusTypeId) ? implode(',',$request->campusTypeId) : $request->campusTypeId;
                $condition .= " AND sa.campus_type_id IN ($campusTypeIdString) ";
            }
            $courseTypeUG = CourseTypeConstants::UG;
            $courseTypeUGPRO = CourseTypeConstants::UG_PRO;
            $courseTypeBPED = CourseTypeConstants::BPED;
            $sql = "SELECT DISTINCT
                        sa.studentID,
                        sa.studentName,
                        sa.regNo,
                        sa.rollNo,
                        sa.myImage,
                        sa.admissionNo,
                        sa.studentGender,
                        sa.batchID AS currentBatchId,
                        ea.isAbsent,
                        im.internalMarkID,
                        im.internalMarks AS internalMark,
                        ims.maxInternalMarks AS internalMax,
                        ims.markType AS internalMarkType,
                        e.examTotalMarks AS externalMax,
                        e.examName,
                        e.examID,
                        b.batchID,
                        b.batchName,
                        b.deptID,
                        b.totalSemester,
                        b.final_semester,
                        b.batchStartYear,
                        b.batchEndYear,
                        b.courseTypeID,
                        b.patternID,
                        sem.semID,
                        sem.semName,
                        sem.orderNo,
                        s.subjectID,
                        s.subjectName,
                        s.subjectDesc,
                        s.syllabusName, 
                        s.isTheory,
                        esc.subjectOrder,
                        s.subjectPriority,
                        IF(ct.course_Type IN ('$courseTypeUG','$courseTypeUGPRO','$courseTypeBPED'), ee.id, eef.examfinalizeID) AS markId,
                        IF(ct.course_Type IN ('$courseTypeUG','$courseTypeUGPRO','$courseTypeBPED'), ee.mark, eef.mark) AS externalMark,
                        esc.credit,
                        esc.isInternal,
                        esc.isExternal,
                        esc.excludeSubjectFromTotal,
                        esc.subjectType,
                        sc.subjectcatName,
                        sc.subjectcatCode,
                        sc.subjectcatID,
                        sc.code AS subjectCategoryCode,
                        et.typeID,
                        et.typeName,
                        et.isInternal AS isInternalExam,
                        et.isSupply,
                        er.examregID,
                        er.examregName,
                        er.examMonth,
                        er.examYear,
                        IF (ees.id, 1, 0) AS isExempted,
                        im.isAbsent AS isInternalAbsent
                    FROM
                        exam_subjectcredit esc
                            INNER JOIN
                        subjects s ON esc.subjectID = s.subjectID
                            INNER JOIN
                        semesters sem ON sem.semID = esc.semID
                            INNER JOIN
                        exam e ON e.subjectID = s.subjectID
                            AND e.batchID = esc.batchID
                            AND e.semID = esc.semID
                            INNER JOIN
                        exam_type et ON et.typeID = e.examTypeID
                            INNER JOIN
                        batches b ON b.batchID = e.batchID
                            INNER JOIN
                        exam_registration er ON er.examregID = e.examregID
                            INNER JOIN
                        exam_reg_studentsubject erss ON erss.subjectID = e.subjectID
                            AND erss.examregID = e.examregID
                            INNER JOIN
                        exam_reg_studentchallan ersc ON erss.examregID = ersc.examregID
                            AND erss.studentID = ersc.studentID
                            INNER JOIN
                        studentaccount sa ON sa.studentID = erss.studentID
                            AND sa.studentID = ersc.studentID
                            INNER JOIN 
                        course_type ct ON ct.courseTypeID = b.courseTypeID
                            LEFT JOIN
                        subject_category sc ON sc.subjectcatID = s.subjectcatID
                            LEFT JOIN
                        exammarks_external ee ON ee.examID = e.examID
                            AND sa.studentID = ee.studentID
                            LEFT JOIN
                        externalexammarks_finalized eef ON eef.examID = e.examID
                            AND sa.studentID = eef.studentID
                            LEFT JOIN
                        exam_attendance ea ON ea.examID = e.examID
                            AND ea.studentID = sa.studentID
                            LEFT JOIN
                        internal_marks im ON im.subjectID = e.subjectID
                            AND im.batchID = e.batchID
                            AND im.semID = e.semID
                            AND im.studentID = sa.studentID
                            LEFT JOIN
                        internal_marks_settings ims ON ims.subjectID = e.subjectID
                            AND ims.batchID = e.batchID
                            AND ims.semID = e.semID
                            LEFT JOIN 
                        exam_exempted_students ees ON ees.exam_id = e.examID
                            AND ees.studentaccount_id = sa.studentID
                            LEFT JOIN 
                        failed_students fs ON fs.studentID = sa.studentID
                            AND FIND_IN_SET(e.semID, fs.hisSemestersInThisbatch)
                    WHERE
                        e.examregID IS NOT NULL
                            AND ersc.paid = 1
                            AND e.batchID = IF (fs.previousBatch, fs.previousBatch, sa.batchID)
                            -- AND e.batchID IN (fs.previousBatch, sa.batchID)
                            $condition
                    ORDER BY esc.subjectOrder ASC, sa.regNo ASC, sem.semID ASC,s.subjectPriority ASC, fs.failedInSemester ASC";
            $regularExamMarkDetails = $this->executeQueryForList($sql, $this->mapper [ConsolidatedMarkReportServiceMapper::GET_REGULAR_EXAM_MARK_DETAILS]);
        } catch (\Exception $e) {
            throw new ECCoreException($e->getCode(), $e->getMessage());
        }
        return $regularExamMarkDetails;
    }
    /**
     * 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 = $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 = ConsolidatedMarkReportService::getInstance()->getStudentRegularExamMarkDetails($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;
    }
    /**
     * @param $request
     */
    public function getStudentMarkHistory($request)
    {
        $request = $this->realEscapeObject($request);
        $consolidatedRequest = new \stdClass();
        $consolidatedRequest->studentId = $request->studentId;
        $consolidatedRequest->groupId = $request->groupId;
        
        try {
            $whereQuery = "";
            // 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) ";
            }
            $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.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,
                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,
                eer.name as examRegName
            FROM
            ec_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 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 eer 
                ON eer.id = ecsmd.ec_exam_registration_id
            LEFT JOIN subject_category sc ON
                sc.subjectcatID = 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 AND eer.trashed IS NULL 
            $whereQuery";
            $subjectDetails = $this->executeQueryForList($query);
            $subjectsMarkDetails = [];
            $subjectMarkType = ['revaluation','moderation','grace'];
            foreach($subjectDetails as $subject){
                $subject->markDetails = json_decode($subject->markDetails);
                $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->markDetails->priority;
                if(empty($subjectsMarkDetails[$subject->paperSubjetId])){
                    $subjectsMarkDetails[$subject->paperSubjetId] = $subject;
                }
                $examMarkType = $subject->markDetails->examMarkType;
                $subject->markDetails->externalMark = $subject->markDetails->externalMarkObtainedInExam;
                $subject->grade = $subject->markDetails->externalMarkObtainedInExamGrade;
                $subject->markDetails->gradePoint = $subject->markDetails->externalMarkObtainedInExamGradePoint;
                $subject->failedStatus = $subject->markDetails->externalMarkObtainedInExamIsFailed ? "FAILED" : "PASSED";
                
                if($examMarkType == "SUPPLY"){
                    $examMarkType = $subject->examRegName;
                }
                $subject->markDetails->markObtained = $subject->markDetails->externalMark + $subject->markDetails->internalMark;
                $subjectsMarkDetails[$subject->paperSubjetId]->markHistory[ $examMarkType] = $subject;
                $subjectsMarkDetails[$subject->paperSubjetId]->markCount++;
                $subjectMark = unserialize(serialize($subject));
                foreach($subjectMarkType as $markType){
                    $markObtained = $markType."Mark";
                    if($subjectMark->markDetails->$markObtained){
                        if($markType == "revaluation"){
                            $examMarkType = "REVALUATION";
                        }
                        else if($markType == "grace"){
                            $examMarkType = "GRACEMARK";
                        }
                        else if($markType == "moderation"){
                            $examMarkType = "MODERATION";
                        }
                        $subjectMark->markDetails->externalMark = round($subjectMark->markDetails->$markObtained);
                        if($markType == "grace"){
                            $markType = "graceMark";
                        }
                        $gradeObtained = $markType."Grade";
                        $gradePointObtained = $markType."GradePoint";
                        $creditPointObtained = $markType."CreditGradePoint";
                        $isFailedForSubject = $markType."IsFailed";
                        $subjectMark->grade = $subjectMark->markDetails->$gradeObtained;
                        $subjectMark->markDetails->gradePoint = $subjectMark->markDetails->$gradePointObtained;
                        $subjectMark->failedStatus = $subjectMark->markDetails->$isFailedForSubject ? "FAILED" : "PASSED";
                        $subjectMark->markDetails->markObtained = $subjectMark->markDetails->$markObtained + $subjectMark->markDetails->internalMark;
                        if($markType == "graceMark" || $markType == "moderation"){
                            $subjectMark->markDetails->markObtained += $subject->markDetails->externalMarkObtainedInExam;
                            $subjectMark->markDetails->externalMark += $subject->markDetails->externalMarkObtainedInExam;
                            $subjectMark->markDetails->externalMark .= "(".round($subjectMark->markDetails->$markObtained).")";
                        }
                        if($subject->markDetails->examMarkType == "SUPPLY"){
                            $examMarkType .= " - ".$subject->examRegName;
                        }
                        $subjectsMarkDetails[$subjectMark->paperSubjetId]->markHistory[ $examMarkType] = $subjectMark;
                        $subjectsMarkDetails[$subjectMark->paperSubjetId]->markCount++;
                    }
                }
                
            }
            
            
            $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) ";
            }
            $query = "SELECT
                esmd.groups_id AS groupsId,
                esmd.student_id AS studentId,
                esmd.academic_term_id AS termId,
                esmd.mark_details AS markDetails,
                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->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.class AS class,
                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 ->> '$.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,
                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->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;
                $programs[$course->programId]->students[$course->studentId]->roundOff = '2';
            });
        } catch (\Exception $e) {
            throw new ECCoreException(ECCoreException::ERROR_FETCHING,"Cannot fetch details! Please try again.");
        }
        return $programs;
    } 
      /**
     * Get all students pass status
     * Features : Dashboard
     */
    public function getAllStudentPassStatus($request)
    {
        $request = $this->realEscapeObject($request);
        $condition = "";
        foreach($request->filters as $filters){
            $filters = (object) $filters;
            if($filters->key == "years"){
                $condition .= " AND  g.properties->>'$.startYear' $filters->operation (".implode(',',$filters->value).")";
            }
            if($filters->key == "courseTypeId"){
                $condition .= " AND  p.course_type_id $filters->operation (".implode(',',$filters->value).")";
            }
        }
        try {
            $sql = "SELECT 
                        ecmd.student_id, 
                        ecmd.failed_status, 
                        g.properties as groupProperties,             
                        g.id as groupId,             
                        g.name as groupName,             
                        ct.courseTypeID,
                        ct.typeName as courseTypeName,
                        dept.deptID,
                        dept.deptName 
                    FROM
                        ec_course_mark_details ecmd 
                    INNER JOIN `groups` g ON 
                        g.id = ecmd.groups_id   
                    INNER JOIN program p ON 
                        p.id = g.properties->>'$.programId' 
                    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)
                    WHERE
                        1 = 1";
            $studentList =  $this->executeQueryForList($sql. $condition);
        } catch (\Exception $e) {
            throw new ECCoreException($e->getCode(),$e->getMessage());
        }
        return $studentList;
    }
      /**
     * Get all students pass status
     * Features : Dashboard
     */
    public function getGraduationRateDetails($request)
    {
        $request = $this->realEscapeObject($request);
        $condition = "";
        foreach($request->filters as $filters){
            $filters = (object) $filters;
            if($filters->key == "admissionYears"){
                $condition .= " AND  g.properties->>'$.startYear' $filters->operation (".implode(',',$filters->value).")";
            }
            if($filters->key == "courseTypeId"){
                $condition .= " AND  p.course_type_id $filters->operation (".implode(',',$filters->value).")";
            }
            if($filters->key == "deptId"){
                $condition .= " AND  dept.deptID $filters->operation (".implode(',',$filters->value).")";
            }
            if($filters->key == "passingYears"){
                $condition .= " AND  ecmd.mark_details->>'$.passingYear' $filters->operation (".implode(',',$filters->value).")";
            }
        }
        if( $request->considerPassedStudents ){
            $condition .= " AND  ecmd.failed_status = 'PASSED' ";
        }
        if( $request->requiredPassingYear ){
            $condition .= " AND  ecmd.mark_details->>'$.passingYear' IS NOT NULL";
        }
        if( $request->graduationBatchStartYear ){
            $condition .= " AND  g.properties->>'$.startYear' >= $request->graduationBatchStartYear ";
        }
        try {
            $sql = "SELECT 
                        ecmd.student_id, 
                        ecmd.failed_status, 
                        g.properties as groupProperties,             
                        ct.courseTypeID,
                        ct.typeName as courseTypeName,
                        dept.deptID,
                        ecmd.mark_details->>'$.passingYear' AS passingYear,
                        ecmd.mark_details->>'$.passingMonth' AS passingMonth,
                        dept.deptName 
                    FROM
                        ec_course_mark_details ecmd 
                    INNER JOIN `groups` g ON 
                        g.id = ecmd.groups_id   
                    INNER JOIN program p ON 
                        p.id = g.properties->>'$.programId' 
                    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)
                    WHERE
                        1 = 1 AND 
                        ecmd.mark_details->>'$.hasCompletedAcademicYear' = 1 ";
            $studentList =  $this->executeQueryForList($sql. $condition);
        } catch (\Exception $e) {
            throw new ECCoreException($e->getCode(),$e->getMessage());
        }
        return $studentList;
    }
    /**
     * Get all students pass status
     * Features : Dashboard
     */
    public function getCourseWiseSuccessRateForDashBoard($request)
    {
        $request = $this->realEscapeObject($request);
        $condition = "";
        foreach($request->filters as $filters){
            $filters = (object) $filters;
            if($filters->key == "groupId"){
                $groupIdString = is_array($filters->value) ? implode("','",$filters->value) : $filters->value;
                $condition .= " AND  g.id $filters->operation  ('$groupIdString')";
            }
        }
        if( $request->examRegistrationId ){
            $examRegistrationIdString = is_array($request->examRegistrationId) ? implode("','",$request->examRegistrationId) : $request->examRegistrationId;
            $condition .= " AND  eer.id IN ('$examRegistrationIdString') ";
        }
        if( $request->resultPublishedRegistrationOnly ){
            $condition .= " AND  eerb.properties ->>'$.isResultPublished' = 1 ";
        }
        try {
            $sql = "SELECT 
                esmd.student_id AS studentId, 
                esmd.mark_history AS markHistory,           
                g.id as groupId,             
                g.name as groupName,
                esmd2.cm_academic_paper_subjects_id AS academicPaperSubjectId,
                esmd2.attendance_status AS attendanceStatus
            FROM
                ec_exam_registration eer 
            INNER JOIN ec_exam_registration_batch eerb ON
                eerb.ec_exam_registration_id = eer.id 
            INNER JOIN ec_semester_mark_details esmd ON
                esmd.groups_id = eerb.groups_id
                AND esmd.academic_term_id = eerb.properties->>'$.academicTermId'
            INNER JOIN ec_subject_mark_details esmd2 ON
                esmd2.ec_exam_registration_id = eer.id 
                AND esmd2.student_id = esmd.student_id 
                AND esmd2.groups_id = eerb.groups_id
            INNER JOIN `groups` g ON 
                g.id = eerb.groups_id
            WHERE
                1 = 1 ";
            $studentList =  $this->executeQueryForList($sql. $condition, $this->mapper[DashBoardServiceMapper::GET_COURSE_WISE_STUDENT_LIST]);
        } catch (\Exception $e) {
            throw new ECCoreException($e->getCode(),$e->getMessage());
        }
        return $studentList;
    }
    /**
     * Get all exam registered student
     * Features : Dashboard
     */
    public function getRegisteredStudentDetailsByExamRegistrationId($request)
    {
        $request = $this->realEscapeObject($request);
        $condition = "";
        foreach($request->filters as $filters){
            $filters = (object) $filters;
            if($filters->key == "groupId"){
                $groupIdString = is_array($filters->value) ? implode("','",$filters->value) : $filters->value;
                $condition .= " AND  g.id $filters->operation  ('$groupIdString')";
            }
        }
        if( $request->examRegistrationId ){
            $examRegistrationIdString = is_array($request->examRegistrationId) ? implode("','",$request->examRegistrationId) : $request->examRegistrationId;
            $condition .= " AND  eer.id IN ('$examRegistrationIdString') ";
        }
        if( $request->resultPublishedRegistrationOnly ){
            $condition .= " AND  eerb.properties ->>'$.isResultPublished' = 1 ";
        }
        $groupBy = " Group By esar.student_id ";
        try {
            $sql = "SELECT 
                esar.student_id, 
                eerb.groups_id AS groupId,
                g.name as groupName           
            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 
            INNER JOIN `groups` g ON 
                g.id = eerb.groups_id
            WHERE CAST(esar.properties ->> '$.registrationStatus' AS CHAR) = 'REGISTERED' AND 
                CAST(esar.properties ->> '$.feeStatus' AS CHAR) = 'PAID' ";
            $studentList =  $this->executeQueryForList($sql. $condition. $groupBy );
        } catch (\Exception $e) {
            throw new ECCoreException($e->getCode(),$e->getMessage());
        }
        return $studentList;
    }
    /**
     * Get all revaluation applied student
     * Features : Dashboard
     */
    public function getRevaluationAppliedDetailsByExamRegistrationId($request)
    {
        $request = $this->realEscapeObject($request);
        $condition = "";
        foreach($request->filters as $filters){
            $filters = (object) $filters;
            if($filters->key == "groupId"){
                $groupIdString = is_array($filters->value) ? implode("','",$filters->value) : $filters->value;
                $condition .= " AND  g.id $filters->operation  ('$groupIdString')";
            }
        }
        if( $request->examRegistrationId ){
            $examRegistrationIdString = is_array($request->examRegistrationId) ? implode("','",$request->examRegistrationId) : $request->examRegistrationId;
            $condition .= " AND  eer2.id IN ('$examRegistrationIdString') ";
        }
        if( $request->resultPublishedRegistrationOnly ){
            $condition .= " AND  eerbParent.properties ->>'$.isResultPublished' = 1 ";
        }
        $groupBy = " Group By esar.student_id ";
        try {
            $sql = "SELECT 
            esar.student_id,
            eerb.groups_id AS groupId,
            g.name as groupName
        FROM
            ec_student_assessment_registration esar
        INNER JOIN ec_exam_registration eer ON
            eer.`type` = esar.ec_exam_registration_type AND eer.id = CAST(esar.identifying_context->>'$.examRegistrationId' AS CHAR)
        INNER JOIN ec_exam_registration_batch eerb ON
            eerb.ec_exam_registration_id = eer.id 
        INNER JOIN ec_exam_registration eer2 ON
            eer2.id = eer.properties->>'$.parentExamRegistrationId' AND 
            eer2.trashed IS NULL
        INNER JOIN ec_exam_registration_batch eerbParent ON
            eerbParent.ec_exam_registration_id = eer2.id  AND 
            eerb.groups_id = eerbParent.groups_id
        INNER JOIN ec_exam_registration_subject eers2 ON     
            eers2.ec_exam_registration_batch_id = eerbParent.id AND 
            eers2.am_assessment_id = esar.am_assessment_id 
        INNER JOIN `groups` g ON 
            g.id = eerb.groups_id
        WHERE CAST(esar.properties ->> '$.registrationStatus' AS CHAR) = 'REGISTERED' AND 
              CAST(esar.properties ->> '$.feeStatus' AS CHAR) = 'PAID' ";
            $studentList =  $this->executeQueryForList($sql. $condition. $groupBy );
        } catch (\Exception $e) {
            throw new ECCoreException($e->getCode(),$e->getMessage());
        }
        return $studentList;
    }
    /**
     * Get Revaluation Mark Entered Students
     * Features : Dashboard
     */
    public function getRevaluationMarkEnteredDetailsByExamRegistrationId($request)
    {
        $request = $this->realEscapeObject($request);
        $condition = "";
        foreach($request->filters as $filters){
            $filters = (object) $filters;
            if($filters->key == "groupId"){
                $groupIdString = is_array($filters->value) ? implode("','",$filters->value) : $filters->value;
                $condition .= " AND  g.id $filters->operation  ('$groupIdString')";
            }
        }
        if( $request->examRegistrationId ){
            $examRegistrationIdString = is_array($request->examRegistrationId) ? implode("','",$request->examRegistrationId) : $request->examRegistrationId;
            $condition .= " AND  eer2.id IN ('$examRegistrationIdString') ";
        }
        if( $request->resultPublishedRegistrationOnly ){
            $condition .= " AND  eerbParent.properties ->>'$.isResultPublished' = 1 ";
        }
        $groupBy = " Group By esar.student_id ";
        try {
            $sql = "SELECT 
            esar.student_id,
            eerb.groups_id AS groupId,
            g.name as groupName,             
            ostm.id AS markEntryId
        FROM
            ec_student_assessment_registration esar
        INNER JOIN ec_exam_registration eer ON
            eer.`type` = esar.ec_exam_registration_type AND eer.id = CAST(esar.identifying_context->>'$.examRegistrationId' AS CHAR)
        INNER JOIN ec_exam_registration_batch eerb ON
            eerb.ec_exam_registration_id = eer.id 
        INNER JOIN ec_exam_registration eer2 ON
            eer2.id = eer.properties->>'$.parentExamRegistrationId' AND 
            eer2.trashed IS NULL
        INNER JOIN ec_exam_registration_batch eerbParent ON
            eerbParent.ec_exam_registration_id = eer2.id  AND 
            eerb.groups_id = eerbParent.groups_id
        INNER JOIN ec_exam_registration_subject eers2 ON     
            eers2.ec_exam_registration_batch_id = eerbParent.id AND 
            eers2.am_assessment_id = esar.am_assessment_id 
        INNER JOIN `groups` g ON 
            g.id = eerb.groups_id
        INNER JOIN oe_student_total_mark ostm ON 
            ostm.student_id =  esar.student_id AND ostm.am_assessment_id = esar.am_assessment_id AND ostm.valuation_type = 'REVALUATION' AND ostm.valuation_count = 'FINALIZED'
        WHERE CAST(esar.properties ->> '$.registrationStatus' AS CHAR) = 'REGISTERED' AND 
              CAST(esar.properties ->> '$.feeStatus' AS CHAR) = 'PAID' ";
            $studentList =  $this->executeQueryForList($sql. $condition. $groupBy );
        } catch (\Exception $e) {
            throw new ECCoreException($e->getCode(),$e->getMessage());
        }
        return $studentList;
    }
}