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 / 12
 CRAP
0.00% covered (danger)
0.00%
0 / 991
GraceMarkService
0.00% covered (danger)
0.00%
0 / 1
0.00% covered (danger)
0.00%
0 / 12
 5852.00
0.00% covered (danger)
0.00%
0 / 991
 __construct
0.00% covered (danger)
0.00%
0 / 1
 2.00
0.00% covered (danger)
0.00%
0 / 3
 getAllSubjectsByAcademicTermId
0.00% covered (danger)
0.00%
0 / 1
 12.00
0.00% covered (danger)
0.00%
0 / 85
 getAllGraceMarkAppliedStudentList
0.00% covered (danger)
0.00%
0 / 1
 6.00
0.00% covered (danger)
0.00%
0 / 41
 getGraceMarkNewReport
0.00% covered (danger)
0.00%
0 / 1
 56.00
0.00% covered (danger)
0.00%
0 / 74
 getStudentMarkMarkDetails
0.00% covered (danger)
0.00%
0 / 1
 56.00
0.00% covered (danger)
0.00%
0 / 116
 getGraceMarkAppliedDetailsByStudentId
0.00% covered (danger)
0.00%
0 / 1
 6.00
0.00% covered (danger)
0.00%
0 / 18
 getStudentDetailsForGraceMarkNewMarkEntry
0.00% covered (danger)
0.00%
0 / 1
 6.00
0.00% covered (danger)
0.00%
0 / 35
 getGraceMarkPercent
0.00% covered (danger)
0.00%
0 / 1
 272.00
0.00% covered (danger)
0.00%
0 / 69
 getAllRegistredStudentMarkDetailsForGraceMarkStarCase
0.00% covered (danger)
0.00%
0 / 1
 182.00
0.00% covered (danger)
0.00%
0 / 195
 getAllRegistredStudentMarkDetailsForGraceMarkEventsHashCase
0.00% covered (danger)
0.00%
0 / 1
 72.00
0.00% covered (danger)
0.00%
0 / 77
 getAllRegistredStudentMarkDetailsForGraceMarkHashCase
0.00% covered (danger)
0.00%
0 / 1
 156.00
0.00% covered (danger)
0.00%
0 / 193
 getAllSpecialExamAssignedSubjectsByAcademicTermId
0.00% covered (danger)
0.00%
0 / 1
 12.00
0.00% covered (danger)
0.00%
0 / 85
<?php
namespace com\linways\ec\core\service;
use com\linways\ec\core\dto\FalseNumberSetting;
use com\linways\ec\core\dto\FalseNumberSettingExamLog;
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\GraceMarkServiceMapper;
use com\linways\core\ams\professional\service\CommonService;
use com\linways\core\ams\professional\constant\SettingsConstants;
use com\linways\base\util\TwigRenderer;
use com\linways\core\ams\professional\util\PdfUtil;
use Razorpay\Api\Order;
use com\linways\ec\core\service\GraceMarkApplicaionService;
use com\linways\ec\core\mapper\StudentMarkListServiceMapper;
class GraceMarkService extends BaseService
{
    use MakeSingletonTrait;
    private function __construct() {
        $this->mapper = GraceMarkServiceMapper::getInstance()->getMapper();
        $this->mapper2 = StudentMarkListServiceMapper::getInstance()->getMapper();
    }
    /**
     * get All Subjects By ExamRegistrationId And RegisterNo
     * @param $request
     * @param $studentRegisterNo
     * @return $subjects
     */
    public function getAllSubjectsByAcademicTermId($request){
        $request = $this->realEscapeObject($request);
        try{
            $whereCond = '';
            $orderBy = ' ORDER BY act.id, s.code ASC';
            if($request->considerOnlyExternalPapers){
                $whereCond = " AND aps.properties ->> '$.isExternal' = 1";
            }
            $query = "SELECT
                            DISTINCT  esar.am_assessment_id as id,
                            esar.am_assessment_id as assessmentId,
                            sa.studentID,
                            sa.studentName,
                            s.code as subjectCode,
                            s.name as subjectName,
                            IF(aps.properties ->> '$.classType' = 'THEORY',1,0) AS isTheory,
                            sa.regNo,
                            g.name as groupName,
                            g.id as groupId,
                            aps.id as academicPaperSubjectId,
                            im.internal_mark as internalMark,
                            ostm.mark_obtained  as obtainedExternal,
                            IF( IF(ostm.mark_obtained IS NULL, 0 , ostm.mark_obtained)>  IF(ostm2.mark_obtained IS NULL, 0 , ostm2.mark_obtained), ostm.mark_obtained, ostm2.mark_obtained) + IF(ostm4.mark_obtained IS NULL, 0 , ostm4.mark_obtained) as externalMark,
                            ostm.attendance_status as isAbsent,
                            ostm2.mark_obtained  as revaluationMark,
                            ostm3.mark_obtained  as graceMark,
                            ostm3.mark_obtained  as hasGraceMark,
                            ostm3.properties  as graceMarkproperties,
                            ostm3.properties ->> '$.graceMarkRemarks'  as graceMarkRemarks,
                            ostm4.mark_obtained  as moderationMark,
                            act.id AS academicTermId,
                            act.name AS academicTermName,
                            oe.id AS oeExamId,
                            aps.properties ->> '$.isInternal' as isInternal,
                            aps.properties ->> '$.isExternal' as isExternal,
                            IF((aps.properties ->> '$.externalMaxMark') AND (aps.properties ->> '$.isExternal'), aps.properties ->> '$.externalMaxMark', '-') as externalMaxMark,
                            IF((aps.properties ->> '$.internalMaxMark') AND (aps.properties ->> '$.isInternal'), aps.properties ->> '$.internalMaxMark', '-') as internalMaxMark,
                            IF((aps.properties ->> '$.externalMaxMark') AND (aps.properties ->> '$.isExternal'), aps.properties ->> '$.externalMaxMark', 0) + IF((aps.properties ->> '$.internalMaxMark') AND (aps.properties ->> '$.isInternal'), aps.properties ->> '$.internalMaxMark', 0) as totalMaxMark,
                            ostm.mark_obtained + im.internal_mark as totalObtainedMark
                        FROM
                            ec_student_assessment_registration esar
                        INNER JOIN studentaccount sa ON 
                            sa.studentID = esar.student_id
                        INNER JOIN ec_exam_registration_subject eers ON 
                            eers.am_assessment_id = esar.am_assessment_id
                        INNER JOIN  cm_academic_paper_subjects aps ON 
                            aps.id = eers.cm_academic_paper_subjects_id
                        INNER JOIN v4_ams_subject s ON 
                            s.id = aps.ams_subject_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 cm_academic_paper ap ON 
                            aps.cm_academic_paper_id = ap.id
                        INNER JOIN  academic_term act ON 
                            act.id = CAST(eerb.properties ->> '$.academicTermId'AS CHAR) 
                        INNER JOIN cm_syllabus_academic_term_settings str ON 
                            str.id = ap.cm_syllabus_academic_term_settings_id 
                        INNER JOIN oe_exams oe ON
                            oe.assessment_id = eers.am_assessment_id AND oe.is_deleted = 0
                        INNER JOIN `groups` g ON 
                            g.id = eerb.groups_id
                        LEFT JOIN ec_internal_marks im ON
                            im.groups_id = eerb.groups_id AND 
                            im.academic_paper_subjects_id = aps.id AND 
                            im.academic_term_id = str.academic_term_id AND im.student_id = sa.studentID
                        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'
                        LEFT JOIN oe_student_total_mark ostm2 ON 
                            ostm2.student_id = sa.studentID AND ostm2.am_assessment_id = esar.am_assessment_id AND ostm2.valuation_type = 'REVALUATION'
                        LEFT JOIN oe_student_total_mark ostm3 ON 
                            ostm3.student_id = sa.studentID AND ostm3.am_assessment_id = esar.am_assessment_id AND ostm3.valuation_type = 'GRACEMARK'
                        LEFT JOIN oe_student_total_mark ostm4 ON 
                            ostm4.student_id = sa.studentID AND ostm4.am_assessment_id = esar.am_assessment_id AND ostm4.valuation_type = 'MODERATION'
                        WHERE
                            sa.regNo = '$request->registerNo' AND eerb.properties ->> '$.academicTermId' IN ($request->academicTermId) AND eer.type='REGULAR' AND eer.trashed IS NULL
                       ";
                $subjects =  $this->executeQueryForList($query.$whereCond.$orderBy, $this->mapper[GraceMarkServiceMapper::GET_STUDENT_SUBJECT_DETAILS]);
        }
        catch (\Exception $e)
        {
            throw new ExamControllerException($e->getCode(),$e->getMessage());
        }
        return $subjects;
    }
         /**
     * Get grace mark distribution student details
     * @param $request
     */
    public function getAllGraceMarkAppliedStudentList($request) 
    {    
        $request = $this->realEscapeObject($request);
        $sql = "SELECT 
                    egmas.id,
                    egmas.applNo,
                    egmas.applied_events,
                    egmas.applied_date,
                    egmas.isConfirmed,
                    egmas.studentID,
                    spa.properties->>'$.registerNumber' AS regNo,
                    spa.properties->>'$.rollNumber' AS rollNo,
                    sa.studentName,
                    egmi.properties ->> '$.semesters' as semesters
                FROM 
                    ec_grace_mark_applied_students egmas  
                INNER JOIN 
                    ec_grace_mark_appln_initiate egmi ON
                    (egmas.gracemark_appln_id = egmi.id) 
                INNER JOIN
                    ec_grace_mark_appln_assigned_batches egmsb  ON 
                    (egmsb.gracemark_appln_id = egmi.id) 
                INNER JOIN studentaccount sa ON 
                    sa.studentID = egmas.studentID
                INNER JOIN 
                    `groups` g ON 
                    g.id = egmsb.groups_id
                INNER JOIN program p ON
                    p.id = g.properties ->> '$.programId'
                INNER JOIN student_program_account spa ON 
                    spa.current_program_id = p.id AND 
                    spa.current_batch_id = egmsb.groups_id AND 
                    spa.student_id = egmas.studentID  AND
                    spa.properties->>'$.academicStatus' IN ('ACTIVE','COMPLETED')
                WHERE 
                    egmi.id = '$request->applnId'";
        try {
            return $this->executeQueryForList($sql);
        } catch (\Exception $e) {
            throw new ExamControllerException($e->getCode(),$e->getMessage());
        }
        
    }
    /**
     * get Grace Mark New Report
     * @param $searchRequest 
     * @return $programResult 
     */
    public function getGraceMarkNewReport($searchRequest) {
        $searchRequest = $this->realEscapeObject($searchRequest);
        try{
            $request = new \stdClass;
            $additionalDetails = new \stdClass;
            $additionalDetails->collageName = $GLOBALS['COLLEGE_NAME'];
            $requestForGraceMarkReport = new \stdClass;
            $requestForGraceMarkReport->groupId = $searchRequest->groupId;
            $studentsDetails = $this->getStudentMarkMarkDetails($requestForGraceMarkReport);
            if(empty($studentsDetails)){
                throw new ExamControllerException(ExamControllerException::NO_REPORTS_DETAILS_FOUND,"No Details Found");
            }
            else{
                $categories = GraceMarkApplicaionService::getInstance()->getAllGraceMarkSubCategories($request);
                foreach($studentsDetails as $student){
                    $student->id = '1406';
                    $appliedDetails = $this->getGraceMarkAppliedDetailsByStudentId($student->id);
                    foreach($appliedDetails as $appliedDetail){
                        $semester = (array)json_decode($appliedDetail->semesters);
                        $percentageObj = $this->getGraceMarkPercent($appliedDetail, $categories);
                        foreach($student->academicTerms as $academicTerm){
                            if(in_array($academicTerm->id, $semester)){
                                $academicTerm->gracePercent = $percentageObj->totalPercent;
                                $academicTerm->catName = $percentageObj->categoryName;
                            }
                        }
                    }
                }
                $templateName = "grace_mark_new_report";
                $additionalDetails->startYear = reset($studentsDetails)->academicYear;
                $additionalDetails->courseTypeName = reset($studentsDetails)->courseTypeName;
                $additionalDetails->batchName = reset($studentsDetails)->batchName;
             
                $responseHtml = TwigRenderer::renderTemplateFileToHtml(realpath(DOCUMENT_ROOT."../examcontroller-api/src/com/linways/web/templates/GraceMark/GraceMarkReport/$templateName.twig"), [ 'students'=>$studentsDetails ,'additionalDetails'=>$additionalDetails]);
                $prtContent = NULL;
                $prtContent .= '<html><head>';
                $prtContent .= "<style>
                            *{
                                color:#000 !important;
                                background-color: #fff !important;
                            }
                            td, th{
                                border:1px solid #000 !important;
                            }
                            table{
                                border:1px solid #fff !important;
                            }
                            .print-border-0 td{
                                border:none !important;
                            }
                    </style>";
                $prtContent .= '</head><title>Grace Mark Report</title><body>';
                $prtContent .= $responseHtml;
                $prtContent .= '</body></html>';
                $options = array(
                    'page-width'     => "450mm",
                    'page-height'    => "310mm",
                    'dpi'            => 96,
                    'margin-top' => "10mm",
                    'margin-left' => "10mm",
                    'margin-right' => "10mm",
                    'margin-bottom' => "30mm",
                    'footer-spacing' => -10,
                    // 'binary' => "/usr/local/bin/wkhtmltopdf", // For Mac
                    'user-style-sheet' => realpath(DOCUMENT_ROOT . "/libcommon/bootstrap/css/bootstrap.min.css")
                );
            $programResult = new \stdClass;
            $programResult->dispalyHtmlData = $responseHtml;
            $programResult->printData = PdfUtil::renderPdf($prtContent, $options);
            return  $programResult;
            }
        }
        catch (\Exception $e)
        {
            throw new ExamControllerException($e->getCode(),$e->getMessage());
        }
    }
     /**
     * get All Registered Students Details
     * @param $searchRequest 
     */
    public function getStudentMarkMarkDetails($searchRequest) {
        $searchRequest = $this->realEscapeObject($searchRequest);
        try{
            $joinQuery = "";
            $subjectOrderBy = CommonService::getInstance()->getSettings(SettingsConstants::EXAM_CONTROLLER, SettingsConstants::SUBJECT_ORDER_TAKEN_BY);
            if($subjectOrderBy == "ORDER"){
                $orderBy = " ORDER BY spa.properties->>'$.registerNumber' ASC , CAST(aps.properties ->> '$.orderNo' AS UNSIGNED) ASC , CAST(act.properties ->> '$.orderNo' AS UNSIGNED) ASC";
            }
            else{
                $orderBy = " ORDER BY spa.properties->>'$.registerNumber' ASC , CAST(aps.properties ->> '$.priority' AS UNSIGNED) DESC , CAST(act.properties ->> '$.orderNo' AS UNSIGNED) ASC";
            }
            $whereQuery = "";
            if(!empty($searchRequest->studentId)) {
                $studentIdString = is_array($searchRequest->studentId) ? "'" . implode("','",$searchRequest->studentId) . "'" : "'".$searchRequest->studentId."'";
                $whereQuery .= " AND sa.studentID IN ( $studentIdString )";
            }
            if(!empty($searchRequest->groupId)) {
                $groupIdString = is_array($searchRequest->groupId) ? "'" . implode("','",$searchRequest->groupId) . "'" : "'".$searchRequest->groupId."'";
                $whereQuery .= " AND g.id IN ( $groupIdString )";
            }
            $query = "SELECT DISTINCT
                        sa.studentID AS id,
                        sa.studentID AS studentId,
                        sa.studentName,
                        spa.properties->>'$.registerNumber' AS regNo,
                        g.id AS groupId,
                        g.name AS groupName,
                        act.id AS academicTermId,
                        act.name AS academicTermName,
                        act.properties ->> '$.orderNo' AS academicTermOrderNo,
                        ct.courseTypeID,
                        ct.typeName AS courseTypeName,
                        g.properties ->> '$.startYear' AS academicYear,
                        g.properties ->> '$.finalTermId' AS finalTermId,
                        
                        esmdsem.mark_details ->>'$.sgpa' AS semesterSapaWithGraceMark,
                        esmdsem.mark_details ->>'$.sgpaWithoutGracemark' AS semesterSgpaWithOutGraceMark,
                        esmdsem.mark_details ->>'$.grade' AS semesterGradeWithGraceMark,
                        esmdsem.mark_details ->>'$.gradeWithOutGraceMark' AS semesterGradeWithOutGraceMark,
                        esmdsem.mark_details ->>'$.failedStatusWithOutGraceMark' AS semesterFailedStatusWithOutGraceMark,
                        esmdsem.failed_status AS semesterFailedStatusWithGraceMark,
                        esmdsem.mark_details ->>'$.totalMarks' AS semesterTotalMark,
                        s.name AS subjectName,
                        s.code AS subjectCode,
                        aps.id AS academicPaperSubjectId,
                        ap.properties ->> '$.order' AS subjectOrder,
                        eer.id AS examRegistrationId,
                        eer.name AS examRegistrationName,
                        esmdsubcon.mark_details ->>'$.credit' AS subjectCredit,
                        esmdsubcon.mark_details ->>'$.internalMark' AS subjectInternalMark,
                        esmdsub.mark_details ->>'$.attendanceStatus' AS subjectAttendanceStatus,
                        esmdsub.mark_details ->>'$.graceMark' AS graceMark,
                        esmdsub.mark_details ->>'$.externalMarkObtainedInExam' AS subjectExternalMarkWithOutGraceMark,
                        esmdsub.mark_details ->>'$.externalMarkObtainedInExamGrade' AS subjectExtrenalGradeWithOutGraceMark,
                        esmdsub.mark_details ->>'$.externalMarkObtainedInExamGradePoint' AS  subjectGradePointPointWithOutGraceMark,
                        esmdsub.mark_details ->>'$.externalMarkObtainedInExamCreditGradePoint' AS  subjectCreditGradePointPointWithOutGraceMark,
                        esmdsub.mark_details ->>'$.externalMarkObtainedInExamIsFailed' AS  subjectResultStatusWithOutGraceMark,
                        esmdsub.mark_details ->>'$.graceMarkCreditGradePoint' AS subjectCreditGradePointWithGraceMark,
                        esmdsub.mark_details ->>'$.graceMarkGradePoint' AS subjectGradePointPointWithGraceMark,
                        esmdsub.mark_details ->>'$.graceMarkGrade' AS subjectExtrenalGradeWithGraceMark,
                        esmdsub.mark_details ->>'$.graceMarkIsFailed' AS subjectResultStatusWithGraceMark
                    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 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 ec_student_assessment_registration esar ON
                        esar.am_assessment_id = eers.am_assessment_id AND 
                        esar.ec_exam_registration_type = eer.type AND
                        CAST(esar.properties ->> '$.registrationStatus' AS CHAR) = 'REGISTERED' AND 
                        CAST(esar.properties ->> '$.feeStatus' AS CHAR) = 'PAID'
                    INNER JOIN 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 studentaccount sa ON 
                        sa.studentID = esar.student_id
                    INNER JOIN oe_student_total_mark ostm ON
                        ostm.student_id = esar.student_id AND 
                        ostm.am_assessment_id = eers.am_assessment_id AND 
                        ostm.valuation_type = 'GRACEMARK'
                    INNER JOIN `course_type` ct ON
                        ct.courseTypeID = p.course_type_id
                    INNER JOIN  academic_term act ON 
                        act.id = CAST(eerb.properties ->> '$.academicTermId'AS CHAR) AND 
                        act.type = 'SEMESTER'
                    INNER JOIN ec_subject_mark_details esmdsub ON
                        esmdsub.groups_id = eerb.groups_id AND 
                        esmdsub.cm_academic_paper_subjects_id = eers.cm_academic_paper_subjects_id AND 
                        esmdsub.student_id = sa.studentID AND
                        esmdsub.ec_exam_registration_id = eer.id
                    INNER JOIN ec_consolidated_subject_mark_details esmdsubcon ON
                        esmdsubcon.groups_id = eerb.groups_id AND 
                        esmdsubcon.cm_academic_paper_subjects_id = eers.cm_academic_paper_subjects_id AND 
                        esmdsubcon.student_id = sa.studentID
                    INNER JOIN ec_semester_mark_details esmdsem ON
                        esmdsem.groups_id = eerb.groups_id AND 
                        esmdsem.academic_term_id = act.id AND 
                        esmdsem.student_id = sa.studentID 
                    WHERE 1=1 ";
            $studentMarkDetails = $this->executeQueryForList($query.$whereQuery.$orderBy, $this->mapper[GraceMarkServiceMapper::OVER_ALL_MARK_DETAILS]);
        }
        catch (\Exception $e)
        {
            throw new ExamControllerException($e->getCode(),$e->getMessage());
        }
        return $studentMarkDetails;
    }
    /**
     * get grace mark applied students
     * @return mixed
     * @throws ExamControllerException
     */
    public function getGraceMarkAppliedDetailsByStudentId($studentId) {    
        $studentId = $this->realEscapeString($studentId);
        $sql = "SELECT 
                    egmas.id, 
                    egmas.applNo, 
                    egmas.applied_events,
                    egmas.applied_date, 
                    egmas.isConfirmed,
                    egmi.properties->>'$.semesters' AS semesters
                FROM ec_grace_mark_applied_students egmas  
                INNER JOIN ec_grace_mark_appln_initiate egmi ON
                    (egmas.gracemark_appln_id = egmi.id) 
                WHERE egmas.studentID = $studentId AND egmas.isConfirmed = 1";
        
        try {
            return $this->executeQueryForList($sql);
        } catch (\Exception $e) {
            throw new ExamControllerException($e->getCode(),$e->getMessage());
        }
        
    }
             /**
     * Get grace mark distribution student details
     * @param $request
     */
    public function getStudentDetailsForGraceMarkNewMarkEntry($request) 
    {    
        $request = $this->realEscapeObject($request);
        $sql = "SELECT 
                    spa.properties->>'$.registerNumber' AS regNo,
                    spa.properties->>'$.rollNumber' AS rollNo,
                    spa.student_id as studentId,
                    egmi.properties ->> '$.semesters' as semesters,
                    egmas.id,
                    egmas.applNo,
                    egmas.applied_events,
                    egmas.applied_date
                FROM 
                    ec_grace_mark_appln_initiate egmi
                INNER JOIN
                    ec_grace_mark_appln_assigned_batches egmsb  ON 
                    (egmsb.gracemark_appln_id = egmi.id) 
                INNER JOIN 
                    `groups` g ON 
                    g.id = egmsb.groups_id
                INNER JOIN program p ON
                    p.id = g.properties ->> '$.programId'
                INNER JOIN student_program_account spa ON 
                    spa.current_program_id = p.id AND 
                    spa.current_batch_id = egmsb.groups_id AND 
                    spa.properties->>'$.academicStatus' IN ('ACTIVE','COMPLETED')
                LEFT JOIN ec_grace_mark_applied_students egmas ON 
                    egmas.studentID = spa.student_id
                WHERE 
                    egmi.id = '$request->applnId' AND  spa.properties->>'$.registerNumber' = '$request->registerNo'";
        try {
            return $this->executeQueryForObject($sql);
        } catch (\Exception $e) {
            throw new ExamControllerException($e->getCode(),$e->getMessage());
        }
        
    }
    public function getGraceMarkPercent($batch, $categoryList){
        $categories = [];
        $catSelected = [];
        foreach($categoryList as $category){
            $categories[$category->categoryId]->categoryId = $category->categoryId;
            $categories[$category->categoryId]->categoryName = $category->categoryName;
            $categories[$category->categoryId]->percentageLimit = $category->percentageLimit;
            foreach($category->levels as $level){
                $categories[$category->categoryId]->level[$level->levelId]->levelId = $level->levelId;
                $categories[$category->categoryId]->level[$level->levelId]->levelName = $level->levelName;
                $categories[$category->categoryId]->level[$level->levelId]->levelLimit = $level->levelLimit;
                $categories[$category->categoryId]->level[$category->levelId]->isSelected = 0;
                $positions = json_decode($positions->positions);
                $hasPosition = 0;
                if($positions){
                    $hasPosition = 1;
                }
                $categories[$category->categoryId]->level[$level->levelId]->hasPosition = $hasPosition;
                $categories[$category->categoryId]->level[$level->levelId]->positions = $positions;
            }
        }
        $categoryDetails = json_decode($batch->applied_events);
        foreach($categoryDetails as $catId  => $category){
            // $categories[$catId]->eventSelected = $category;
            foreach( $category as $key =>$catVal){
                $categories[$catId]->level[$catVal->levelId]->eventSelected[$key] = $catVal;
                $catSelected[$catId] = $categories[$catId]->categoryName;
            }
        }
        $totalPercent = 0;
        foreach( $categories as $cat){
            $categoryLimit = $cat->percentageLimit;
            $levelTotal = 0;
            foreach( $cat->level as $level){
                $levelLimit = $level->levelLimit;
                $positionTotal = 0;
                foreach( $level->eventSelected as $eventSelected){
                    if( $level->hasPosition){
                        $positionId = $eventSelected->positionId;
                        $percentageDetails = array_filter(array_map(function ($obj) use ($positionId) {
                            if($obj->id == $positionId){
                                return $obj->percentage;
                            }
                        }, $level->positions));
                        $percentage = current($percentageDetails);
                        $positionTotal += $percentage;
                    }
                    else{
                        $positionTotal += $levelLimit;
                    }
                }
                if($levelLimit){
                    $levelTotal += $positionTotal < $levelLimit? $positionTotal : $levelLimit;
                }
                else{
                    $levelTotal += $positionTotal;
                }
            }
            if($categoryLimit){
                $totalPercent += $levelTotal < $categoryLimit? $levelTotal : $categoryLimit;
            }
            else{
                $totalPercent += $levelTotal;
            }
        }
        $totalPercent =  $totalPercent < 25 ?  $totalPercent : 25;
        $categoryName = implode(', ', $catSelected);
        $response = new \stdClass();
        $response->totalPercent = $totalPercent;
        $response->categoryName = $categoryName;
        return $response;
    }
    /**
     * get All Registered Students Details
     * @param $searchRequest 
     */
    public function getAllRegistredStudentMarkDetailsForGraceMarkStarCase($searchRequest) {
        $searchRequest = $this->realEscapeObject($searchRequest);
        try
        {
            $orderBy = "ORDER BY spa.properties->>'$.registerNumber' ASC , CAST(cap.properties ->> '$.order' AS UNSIGNED) ASC , CAST(aps.properties ->> '$.order' AS UNSIGNED) ASC ";
            $whereQuery = "";
            if(!empty($searchRequest->groupId)) {
                $groupIdString = is_array($searchRequest->groupId) ? "'" . implode("','",$searchRequest->groupId) . "'" : "'".$searchRequest->groupId."'";
                $whereQuery .= " AND g.id IN ( $groupIdString )";
            }
            if(!empty($searchRequest->selectedSemesters)) {
                $semesterIdString = is_array($searchRequest->selectedSemesters) ? "'" . implode("','",$searchRequest->selectedSemesters) . "'" : "'".$searchRequest->selectedSemesters."'";
                $whereQuery .= " AND act.id IN ( $semesterIdString )";
            }
            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 )";
            }
            $innerJoin = "";
            $columns = "";
            if(!empty($searchRequest->considerExternalAppliedStudents)) {
                $innerJoin = "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 = 'EXTERNAL_GRACE_MARK'";
                $columns = " ostm.mark_obtained AS externalGraceMark, ";
            }
            if(!empty($searchRequest->considerInternalAppliedStudents)) {
                $innerJoin = "INNER JOIN oe_student_total_mark ostm2 ON 
                ostm2.student_id = sa.studentID AND ostm2.am_assessment_id = esar.am_assessment_id AND ostm2.valuation_type = 'INTERNAL_GRACE_MARK'";
                $columns = "ostm2.mark_obtained AS internalGraceMark,";
            }
            if(!empty($searchRequest->considerAssessmentAdditionalMark)) {
                $innerJoin = "INNER JOIN ec_student_assessment_additional_mark ostm2 ON 
                ostm2.student_id = sa.studentID AND ostm2.am_assessment_id = esar.am_assessment_id AND ostm2.type = 'STAR_HASH_COMBINED'";
                $columns = "ostm2.mark_obtained AS externalGraceMarkReserve,";
            }
            $query = "SELECT DISTINCT
                        sa.studentID AS id,
                        sa.studentID AS studentId,
                        sa.studentName,
                        spa.properties->>'$.rollNumber' AS rollNo,
                        spa.properties->>'$.registerNumber' AS regNo,
                        spa.properties->>'$.universityRegisterNumber' AS universityRegNo,
                        sa.admissionNo,
                        sa.myImage,
                        sa.studentGender,
                        g.id AS groupId,
                        g.name AS groupName,
                        g.properties ->> '$.optionName' AS batchOptionName,
                        act.id AS academicTermId,
                        act.name AS academicTermName,
                        act.properties ->>'$.orderNo' AS academicOrderNo,
                        dept.deptID,
                        deg.name AS degreeName,
                        deg.description AS degreeDescription,
                        deg.id AS degreeId,
                        dept.deptName,
                        ct.courseTypeID,
                        ct.typeName AS courseTypeName,
                        ct.course_Type AS courseType,
                        g.properties ->> '$.startYear' AS academicYear,
                        eer.type AS latestExamType,
                        eer.properties ->> '$.examYear' AS latestExamYear,
                        eer.properties ->> '$.examMonth' AS latestExamMonth,
                        cap.properties ->> '$.order' as subjectPriority,
                        esmdsem.mark_details ->>'$.sgpa' AS semesterSgpa,
                        esmdsem.mark_details ->>'$.percentage' AS semesterPercentage,
                        esmdsem.mark_details ->>'$.grade' AS semesterGrade,
                        esmdsem.mark_details ->>'$.markObtained' AS semesterMarkObtained,
                        esmdsem.mark_details ->>'$.class' AS semesterClass,
                        esmdsem.mark_details ->>'$.totalMarks' AS semesterTotalMarks,
                        esmdsem.mark_details ->>'$.credit' AS semesterCredit,
                        esmdsem.mark_details ->>'$.gradePoint' AS semesterGradePoint,
                        esmdsem.mark_details ->>'$.creditGradePoint' AS semesterCreditGradePoint,
                        esmdsem.mark_details ->>'$.totalEarnedCredits' AS totalEarnedCredits,
                        esmdsem.mark_details ->>'$.semesterCgpa' AS semesterCgpa,
                        esmdsem.mark_history AS semesterMarkHistory,
                        esmdsem.failed_status AS semesterFailedStatus,
                        str.name AS streamName,
                        str.properties->>'$.abbreviation' AS streamDesc,
                        s.code AS subjectCode,
                        aps.properties ->> '$.syllabusName' AS syllabusName,
                        s.name AS subjectName,
                        eers.cm_academic_paper_subjects_id AS academicPaperSubjectId,
                        eers.am_assessment_id AS assessmentId,
                        oe.id AS oeExamId,
                        esmdsub.mark_details ->>'$.markObtained' AS subjectMarkObtained,
                        esmdsub.mark_details ->>'$.isExternalFailed' AS isExternalFailed,
                        esmdsub.mark_details ->>'$.externalGrade' AS subjectExtrenalGrade,
                        esmdsub.mark_details ->>'$.externalGradePoint' AS subjectExternalGradePoint,
                        esmdsub.mark_details ->>'$.percentageObtainedExternal' AS percentageObtainedExternal,
                        esmdsub.mark_details ->>'$.percentageObtainedInternal' AS percentageObtainedInternal,
                        esmdsub.mark_details ->>'$.wgpa' AS subjectWgpa,
                        esmdsub.mark_details->>'$.attendanceStatus' AS subjectAttendanceStatus,
                        esmdsub.class AS subjectClass,
                        esmdsub.percentage AS subjectPercentage,
                        esmdsub.grade AS subjectGrade,
                        esmdsub.failed_status AS subjectFailedStatus,
                        esmdsub.mark_details ->>'$.externalMark' AS externalMark,
                        IF (esmdsubcon.mark_details ->>'$.graceMark' IS NULL,0,esmdsubcon.mark_details ->>'$.graceMark') AS graceMark,
                        esmdsub.mark_details ->>'$.gradePoint' AS subGradePoint,
                        esmdsub.mark_details ->>'$.gradePoint' AS subjectGradePoint,
                        esmdsub.mark_details ->>'$.creditGradePoint' AS subCreditGradePoint,
                        esmdsub.mark_details ->>'$.creditGradePoint' AS subjectCreditGradePoint,
                        esmdsub.mark_details ->>'$.internalAttendanceStatus' AS internalAttendanceStatus,
                        esmdsub.mark_details ->>'$.internalGrade' AS subjectInternalGrade,
                        esmdsub.mark_details ->>'$.internalGradePoint' AS subjectInternalGradePoint,
                        esmdsub.mark_details ->>'$.isInternalNull' AS isInternalNull,
                        esmdsub.mark_details ->>'$.internalMark' AS internalMark,
                        esmdsubcon.mark_details ->>'$.internalAttendance' AS subjectInternalAttendance,
                        esmdsubcon.mark_details ->>'$.credit' AS subjectCredit,
                        esmdsubcon.mark_details ->>'$.externalMaxMark' AS externalMaxMark,
                        esmdsubcon.mark_details ->>'$.internalMaxMark' AS internalMaxMark,
                        esmdsubcon.mark_details ->>'$.totalMarks' AS subjectTotalMarks,
                        esmdsubcon.mark_details ->>'$.isExternal' AS isExternal,
                        esmdsubcon.mark_details ->>'$.isInternal' AS isInternal,
                        esmdsubcon.mark_details ->>'$.isInternalFailed' AS isInternalFailed,
                        esmdsubcon.mark_details ->>'$.categoryCode' AS subjectCategoryCode,
                        esmdsubcon.mark_details ->>'$.internalPassPercentage' AS subjectInternalPassPercentage,
                        esmdsubcon.mark_details ->>'$.externalPassPercentage' AS subjectExternalPassPercentage,
                        esmdsubcon.mark_details ->>'$.aggregatePassPercentage' AS subjectAggregatePassPercentage,
                        esmdsubcon.mark_history AS subjectMarkHistory,
                        esmdsubcon.mark_details  subjectConsolidatedMarkDetails,
                       
                        ecmdcourse.mark_details ->>'$.cgpa' AS cgpa,
                        ecmdcourse.percentage AS overallPercentage,
                        ecmdcourse.mark_details ->>'$.grade' AS overallGrade,
                        ecmdcourse.mark_details ->>'$.class' AS overallClass,
                        ecmdcourse.mark_details ->>'$.markObtained' AS courseObtainedMark,
                        ecmdcourse.mark_details ->>'$.totalMarks' AS courseTotalMark,
                        ecmdcourse.mark_details ->>'$.creditxMark' AS courseCreditxMark,
                        ecmdcourse.mark_details ->>'$.creditGradePoint' AS courseCreditGradePoint,
                        ecmdcourse.mark_details ->>'$.credit' AS courseTotalCredit,
                        ecmdcourse.mark_details ->>'$.wgp' AS courseWgp,
                        ecmdcourse.mark_details ->>'$.wgpa' AS courseWgpa,
                        ecmdcourse.mark_details ->>'$.gradePoint' AS courseGradePoint,
                        $columns
                        ecmdcourse.failed_status AS courseFailedStatus
                        
                    FROM
                        `groups` g
                    INNER JOIN ec_exam_registration_batch eerb ON
                        eerb.groups_id = g.id
                    INNER JOIN ec_exam_registration_subject eers ON
                        eers.ec_exam_registration_batch_id = eerb.id
                    INNER JOIN  cm_academic_paper_subjects aps ON 
                        eers.cm_academic_paper_subjects_id = aps.id
                    INNER JOIN  cm_academic_paper cap ON 
                        cap.id = aps.cm_academic_paper_id
                    INNER JOIN  v4_ams_subject s ON 
                        aps.ams_subject_id = s.id
                    INNER JOIN program p ON
                        p.id = g.properties ->> '$.programId'
                    INNER JOIN ec_exam_registration eer ON
                        eer.id = eerb.ec_exam_registration_id
                    INNER JOIN ec_student_assessment_registration esar ON
                        esar.am_assessment_id = eers.am_assessment_id AND 
                        esar.ec_exam_registration_type = eer.type AND
                        CAST(esar.properties ->> '$.registrationStatus' AS CHAR) = 'REGISTERED' AND 
                        CAST(esar.properties ->> '$.feeStatus' AS CHAR) = 'PAID'
                    INNER JOIN student_program_account spa ON 
                        spa.current_program_id = p.id AND 
                        spa.student_id = esar.student_id 
                    INNER JOIN studentaccount sa ON 
                        sa.studentID = esar.student_id
                    INNER JOIN department dept ON
                        dept.deptID = g.properties ->> '$.departmentId'
                    INNER JOIN  academic_term act ON 
                        act.id = CAST(eerb.properties ->> '$.academicTermId'AS CHAR) 
                    INNER JOIN degree deg ON
                        deg.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
                    INNER JOIN ec_semester_mark_details esmdsem ON
                        esmdsem.groups_id = eerb.groups_id AND esmdsem.academic_term_id = act.id AND esmdsem.student_id = sa.studentID 
                    LEFT JOIN  ec_course_mark_details ecmdcourse ON
                        ecmdcourse.groups_id = eerb.groups_id AND ecmdcourse.student_id = sa.studentID 
                    INNER JOIN ec_subject_mark_details esmdsub ON
                        esmdsub.ec_exam_registration_id = eerb.ec_exam_registration_id AND esmdsub.groups_id = eerb.groups_id AND esmdsub.cm_academic_paper_subjects_id = eers.cm_academic_paper_subjects_id AND esmdsub.student_id = sa.studentID 
                    INNER JOIN ec_consolidated_subject_mark_details esmdsubcon ON
                        esmdsubcon.groups_id = eerb.groups_id AND esmdsubcon.cm_academic_paper_subjects_id = eers.cm_academic_paper_subjects_id AND esmdsubcon.student_id = sa.studentID 
                    INNER JOIN oe_exams oe ON
                        oe.assessment_id = esar.am_assessment_id AND oe.is_deleted = 0
                        $innerJoin
                    WHERE 1=1";
            $studentMarkDetails = $this->executeQueryForList($query.$whereQuery.$orderBy, $this->mapper2[StudentMarkListServiceMapper::OVER_ALL_MARK_DETAILS]);
        }
        catch (\Exception $e)
        {
            throw new ExamControllerException($e->getCode(),$e->getMessage());
        }
        return $studentMarkDetails;
    }
     /**
     * get All Registered Students Details
     * @param $searchRequest 
     * @return $studentDataDetails 
     */
    public function getAllRegistredStudentMarkDetailsForGraceMarkEventsHashCase($searchRequest) {
        $searchRequest = $this->realEscapeObject($searchRequest);
        try
        {
            $orderBy = "ORDER BY spa.properties->>'$.registerNumber' ASC";
            $whereQuery = "";
            if(!empty($searchRequest->courseTypeId)) {
                $whereQuery .= " AND p.course_type_id = '$searchRequest->courseTypeId'";
            }
            if(!empty($searchRequest->admissionYear)) {
                $whereQuery .= " AND g.properties ->> '$.startYear' = '$searchRequest->admissionYear'";
            }
            if(!empty($searchRequest->groupId)) {
                $groupIdString = is_array($searchRequest->groupId) ? "'" . implode("','",$searchRequest->groupId) . "'" : "'".$searchRequest->groupId."'";
                $whereQuery .= " AND g.id IN ( $groupIdString )";
            }
            if(!empty($searchRequest->examRegistrationId)) {
                $examRegistrationIdString = is_array($searchRequest->groupId) ? "'" . implode("','",$searchRequest->examRegistrationId) . "'" : "'".$searchRequest->examRegistrationId."'";
                $whereQuery .= " AND eer.id IN ( $examRegistrationIdString )";
            }
            $query = "SELECT DISTINCT
                            sa.studentID AS id,
                            sa.studentID AS studentId,
                            sa.studentName,
                            spa.properties->>'$.registerNumber' as regNo,
                            spa.properties->>'$.rollNumber' as rollNo,
                            g.id AS groupId,
                            g.name AS groupName,
                            act.id AS academicTermId,
                            act.name AS academicTerm,
                            d.deptName,
                            d.deptID,
                            eeram.properties->>'$.event' AS graceMarkEvent,
                            eserd.properties->>'$.isResultWithHeld' AS isResultWithHeld
                        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
                        INNER JOIN program p ON
                            p.id = g.properties ->> '$.programId'
                        INNER JOIN ec_student_assessment_registration esar ON
                            esar.am_assessment_id = eers.am_assessment_id AND 
                            CAST(esar.properties ->> '$.registrationStatus' AS CHAR) = 'REGISTERED' AND 
                            CAST(esar.properties ->> '$.feeStatus' AS CHAR) = 'PAID' AND
                            esar.ec_exam_registration_type = eer.type
                        INNER JOIN student_program_account spa ON 
                            spa.student_id  = esar.student_id  AND
                            spa.current_program_id = p.id 
                        INNER JOIN studentaccount sa ON 
                            sa.studentID = esar.student_id
                        INNER JOIN department d ON
                            d.deptID = g.properties ->> '$.departmentId'
                        INNER JOIN  academic_term act ON 
                            act.id = CAST(eerb.properties ->> '$.academicTermId'AS CHAR) 
                        INNER JOIN ec_student_exam_registration_details eserd ON
                            eserd.student_id = esar.student_id AND 
                            eserd.ec_exam_registration_id = eer.id 
                        LEFT JOIN ec_exam_registration_additional_mark eeram ON
                            eeram.student_id = esar.student_id AND 
                            eeram.ec_exam_registration_id = eer.id AND 
                            eeram.type='GRACE_MARK_HASH_NOTATION'
                        WHERE 1=1 ";
            $studentDataDetails = $this->executeQueryForList($query.$whereQuery.$orderBy);
        }
        catch (\Exception $e)
        {
            throw new ExamControllerException($e->getCode(),$e->getMessage());
        }
        return $studentDataDetails;
    }
        /**
     * get All Registered Students Details
     * @param $searchRequest 
     */
    public function getAllRegistredStudentMarkDetailsForGraceMarkHashCase($searchRequest) {
        $searchRequest = $this->realEscapeObject($searchRequest);
        try
        {
            $orderBy = "ORDER BY spa.properties->>'$.registerNumber' ASC , CAST(cap.properties ->> '$.order' AS UNSIGNED) ASC , CAST(aps.properties ->> '$.order' AS UNSIGNED) ASC ";
            $whereQuery = "";
            if(!empty($searchRequest->groupId)) {
                $groupIdString = is_array($searchRequest->groupId) ? "'" . implode("','",$searchRequest->groupId) . "'" : "'".$searchRequest->groupId."'";
                $whereQuery .= " AND g.id IN ( $groupIdString )";
            }
            if(!empty($searchRequest->selectedSemesters)) {
                $semesterIdString = is_array($searchRequest->selectedSemesters) ? "'" . implode("','",$searchRequest->selectedSemesters) . "'" : "'".$searchRequest->selectedSemesters."'";
                $whereQuery .= " AND act.id IN ( $semesterIdString )";
            }
            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 )";
            }
            $innerJoin = "";
            $columns = "";
            if(!empty($searchRequest->considerAppliedStudents)) {
                $innerJoin = "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 = 'GRACE_MARK_HASH_NOTATION'";
                $columns = " ostm.mark_obtained AS graceMark, ";
            }
            if(!empty($searchRequest->considerInternalAppliedStudents)) {
                $innerJoin = "INNER JOIN oe_student_total_mark ostmInt ON 
                ostmInt.student_id = sa.studentID AND ostmInt.am_assessment_id = esar.am_assessment_id AND ostmInt.valuation_type = 'INTERNAL_HASH_MARK_NOTATION'";
                $columns = " ostmInt.mark_obtained AS internalGraceMark, ";
            }
            $query = "SELECT DISTINCT
                        sa.studentID AS id,
                        sa.studentID AS studentId,
                        sa.studentName,
                        spa.properties->>'$.rollNumber' AS rollNo,
                        spa.properties->>'$.registerNumber' AS regNo,
                        spa.properties->>'$.universityRegisterNumber' AS universityRegNo,
                        sa.admissionNo,
                        sa.myImage,
                        sa.studentGender,
                        g.id AS groupId,
                        g.name AS groupName,
                        g.properties ->> '$.optionName' AS batchOptionName,
                        act.id AS academicTermId,
                        act.name AS academicTermName,
                        act.properties ->>'$.orderNo' AS academicOrderNo,
                        dept.deptID,
                        deg.name AS degreeName,
                        deg.description AS degreeDescription,
                        deg.id AS degreeId,
                        dept.deptName,
                        ct.courseTypeID,
                        ct.typeName AS courseTypeName,
                        ct.course_Type AS courseType,
                        g.properties ->> '$.startYear' AS academicYear,
                        eer.type AS latestExamType,
                        eer.properties ->> '$.examYear' AS latestExamYear,
                        eer.properties ->> '$.examMonth' AS latestExamMonth,
                        cap.properties ->> '$.order' as subjectPriority,
                        esmdsem.mark_details ->>'$.sgpa' AS semesterSgpa,
                        esmdsem.mark_details ->>'$.percentage' AS semesterPercentage,
                        esmdsem.mark_details ->>'$.grade' AS semesterGrade,
                        esmdsem.mark_details ->>'$.markObtained' AS semesterMarkObtained,
                        esmdsem.mark_details ->>'$.class' AS semesterClass,
                        esmdsem.mark_details ->>'$.totalMarks' AS semesterTotalMarks,
                        esmdsem.mark_details ->>'$.credit' AS semesterCredit,
                        esmdsem.mark_details ->>'$.gradePoint' AS semesterGradePoint,
                        esmdsem.mark_details ->>'$.creditGradePoint' AS semesterCreditGradePoint,
                        esmdsem.mark_details ->>'$.totalEarnedCredits' AS totalEarnedCredits,
                        esmdsem.mark_details ->>'$.semesterCgpa' AS semesterCgpa,
                        esmdsem.mark_history AS semesterMarkHistory,
                        esmdsem.failed_status AS semesterFailedStatus,
                        str.name AS streamName,
                        str.properties->>'$.abbreviation' AS streamDesc,
                        s.code AS subjectCode,
                        aps.properties ->> '$.syllabusName' AS syllabusName,
                        s.name AS subjectName,
                        eers.cm_academic_paper_subjects_id AS academicPaperSubjectId,
                        eers.am_assessment_id AS assessmentId,
                        oe.id AS oeExamId,
                        esmdsub.mark_details ->>'$.markObtained' AS subjectMarkObtained,
                        esmdsub.mark_details ->>'$.isExternalFailed' AS isExternalFailed,
                        esmdsub.mark_details ->>'$.externalGrade' AS subjectExtrenalGrade,
                        esmdsub.mark_details ->>'$.externalGradePoint' AS subjectExternalGradePoint,
                        esmdsub.mark_details ->>'$.percentageObtainedExternal' AS percentageObtainedExternal,
                        esmdsub.mark_details ->>'$.percentageObtainedInternal' AS percentageObtainedInternal,
                        esmdsub.mark_details ->>'$.wgpa' AS subjectWgpa,
                        esmdsub.mark_details->>'$.attendanceStatus' AS subjectAttendanceStatus,
                        esmdsub.class AS subjectClass,
                        esmdsub.percentage AS subjectPercentage,
                        esmdsub.grade AS subjectGrade,
                        esmdsub.failed_status AS subjectFailedStatus,
                        esmdsub.mark_details ->>'$.externalMark' AS externalMark,
                        IF (esmdsubcon.mark_details ->>'$.graceMark' IS NULL,0,esmdsubcon.mark_details ->>'$.graceMark') AS graceMark,
                        esmdsub.mark_details ->>'$.gradePoint' AS subGradePoint,
                        esmdsub.mark_details ->>'$.gradePoint' AS subjectGradePoint,
                        esmdsub.mark_details ->>'$.creditGradePoint' AS subCreditGradePoint,
                        esmdsub.mark_details ->>'$.creditGradePoint' AS subjectCreditGradePoint,
                        esmdsub.mark_details ->>'$.internalAttendanceStatus' AS internalAttendanceStatus,
                        esmdsub.mark_details ->>'$.internalGrade' AS subjectInternalGrade,
                        esmdsub.mark_details ->>'$.internalGradePoint' AS subjectInternalGradePoint,
                        esmdsub.mark_details ->>'$.isInternalNull' AS isInternalNull,
                        esmdsub.mark_details ->>'$.internalMark' AS internalMark,
                        esmdsubcon.mark_details ->>'$.internalAttendance' AS subjectInternalAttendance,
                        esmdsubcon.mark_details ->>'$.credit' AS subjectCredit,
                        esmdsubcon.mark_details ->>'$.externalMaxMark' AS externalMaxMark,
                        esmdsubcon.mark_details ->>'$.internalMaxMark' AS internalMaxMark,
                        esmdsubcon.mark_details ->>'$.totalMarks' AS subjectTotalMarks,
                        esmdsubcon.mark_details ->>'$.isExternal' AS isExternal,
                        esmdsubcon.mark_details ->>'$.isInternal' AS isInternal,
                        esmdsubcon.mark_details ->>'$.isInternalFailed' AS isInternalFailed,
                        esmdsubcon.mark_details ->>'$.categoryCode' AS subjectCategoryCode,
                        esmdsubcon.mark_details ->>'$.internalPassPercentage' AS subjectInternalPassPercentage,
                        esmdsubcon.mark_details ->>'$.externalPassPercentage' AS subjectExternalPassPercentage,
                        esmdsubcon.mark_details ->>'$.aggregatePassPercentage' AS subjectAggregatePassPercentage,
                        esmdsubcon.mark_history AS subjectMarkHistory,
                        esmdsubcon.mark_details  subjectConsolidatedMarkDetails,
                       
                        ecmdcourse.mark_details ->>'$.cgpa' AS cgpa,
                        ecmdcourse.percentage AS overallPercentage,
                        ecmdcourse.mark_details ->>'$.grade' AS overallGrade,
                        ecmdcourse.mark_details ->>'$.class' AS overallClass,
                        ecmdcourse.mark_details ->>'$.markObtained' AS courseObtainedMark,
                        ecmdcourse.mark_details ->>'$.totalMarks' AS courseTotalMark,
                        ecmdcourse.mark_details ->>'$.creditxMark' AS courseCreditxMark,
                        ecmdcourse.mark_details ->>'$.creditGradePoint' AS courseCreditGradePoint,
                        ecmdcourse.mark_details ->>'$.credit' AS courseTotalCredit,
                        ecmdcourse.mark_details ->>'$.wgp' AS courseWgp,
                        ecmdcourse.mark_details ->>'$.wgpa' AS courseWgpa,
                        ecmdcourse.mark_details ->>'$.gradePoint' AS courseGradePoint,
                        ostm2.mark_obtained AS externalGraceMarkReserve,
                        $columns
                        ecmdcourse.failed_status AS courseFailedStatus
                        
                    FROM
                        `groups` g
                    INNER JOIN ec_exam_registration_batch eerb ON
                        eerb.groups_id = g.id
                    INNER JOIN ec_exam_registration_subject eers ON
                        eers.ec_exam_registration_batch_id = eerb.id
                    INNER JOIN  cm_academic_paper_subjects aps ON 
                        eers.cm_academic_paper_subjects_id = aps.id
                    INNER JOIN  cm_academic_paper cap ON 
                        cap.id = aps.cm_academic_paper_id
                    INNER JOIN  v4_ams_subject s ON 
                        aps.ams_subject_id = s.id
                    INNER JOIN program p ON
                        p.id = g.properties ->> '$.programId'
                    INNER JOIN ec_exam_registration eer ON
                        eer.id = eerb.ec_exam_registration_id
                    INNER JOIN ec_student_assessment_registration esar ON
                        esar.am_assessment_id = eers.am_assessment_id AND 
                        esar.ec_exam_registration_type = eer.type AND
                        CAST(esar.properties ->> '$.registrationStatus' AS CHAR) = 'REGISTERED' AND 
                        CAST(esar.properties ->> '$.feeStatus' AS CHAR) = 'PAID'
                    INNER JOIN student_program_account spa ON 
                        spa.current_program_id = p.id AND 
                        spa.student_id = esar.student_id 
                    INNER JOIN studentaccount sa ON 
                        sa.studentID = esar.student_id
                    INNER JOIN department dept ON
                        dept.deptID = g.properties ->> '$.departmentId'
                    INNER JOIN  academic_term act ON 
                        act.id = CAST(eerb.properties ->> '$.academicTermId'AS CHAR) 
                    INNER JOIN degree deg ON
                        deg.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
                    INNER JOIN ec_semester_mark_details esmdsem ON
                        esmdsem.groups_id = eerb.groups_id AND esmdsem.academic_term_id = act.id AND esmdsem.student_id = sa.studentID 
                    LEFT JOIN  ec_course_mark_details ecmdcourse ON
                        ecmdcourse.groups_id = eerb.groups_id AND ecmdcourse.student_id = sa.studentID 
                    INNER JOIN ec_subject_mark_details esmdsub ON
                        esmdsub.ec_exam_registration_id = eerb.ec_exam_registration_id AND esmdsub.groups_id = eerb.groups_id AND esmdsub.cm_academic_paper_subjects_id = eers.cm_academic_paper_subjects_id AND esmdsub.student_id = sa.studentID 
                    INNER JOIN ec_consolidated_subject_mark_details esmdsubcon ON
                        esmdsubcon.groups_id = eerb.groups_id AND esmdsubcon.cm_academic_paper_subjects_id = eers.cm_academic_paper_subjects_id AND esmdsubcon.student_id = sa.studentID 
                    INNER JOIN oe_exams oe ON
                        oe.assessment_id = esar.am_assessment_id AND oe.is_deleted = 0
                    LEFT JOIN ec_student_assessment_additional_mark ostm2 ON 
                        ostm2.student_id = sa.studentID AND ostm2.am_assessment_id = esar.am_assessment_id AND ostm2.type = 'STAR_HASH_COMBINED'
                        $innerJoin
                    WHERE 1=1";
            $studentMarkDetails = $this->executeQueryForList($query.$whereQuery.$orderBy, $this->mapper2[StudentMarkListServiceMapper::OVER_ALL_MARK_DETAILS]);
        }
        catch (\Exception $e)
        {
            throw new ExamControllerException($e->getCode(),$e->getMessage());
        }
        return $studentMarkDetails;
    }
    /**
     * get All Subjects By Specal ExamRegistration 
     * @param $request
     * @param $studentRegisterNo
     * @return $subjects
     */
    public function getAllSpecialExamAssignedSubjectsByAcademicTermId($request){
        $request = $this->realEscapeObject($request);
        try{
            $whereCond = '';
            $orderBy = ' ORDER BY act.id, s.code ASC';
            if($request->considerOnlyExternalPapers){
                $whereCond = " AND aps.properties ->> '$.isExternal' = 1";
            }
            $query = "SELECT
                            DISTINCT  esar.am_assessment_id as id,
                            esar.am_assessment_id as assessmentId,
                            sa.studentID,
                            sa.studentName,
                            s.code as subjectCode,
                            s.name as subjectName,
                            IF(aps.properties ->> '$.classType' = 'THEORY',1,0) AS isTheory,
                            sa.regNo,
                            g.name as groupName,
                            g.id as groupId,
                            aps.id as academicPaperSubjectId,
                            im.internal_mark as internalMark,
                            ostm.mark_obtained  as obtainedExternal,
                            IF( IF(ostm.mark_obtained IS NULL, 0 , ostm.mark_obtained)>  IF(ostm2.mark_obtained IS NULL, 0 , ostm2.mark_obtained), ostm.mark_obtained, ostm2.mark_obtained) + IF(ostm4.mark_obtained IS NULL, 0 , ostm4.mark_obtained) as externalMark,
                            ostm.attendance_status as isAbsent,
                            ostm2.mark_obtained  as revaluationMark,
                            ostm3.mark_obtained  as graceMark,
                            ostm3.mark_obtained  as hasGraceMark,
                            ostm3.properties  as graceMarkproperties,
                            ostm3.properties ->> '$.graceMarkRemarks'  as graceMarkRemarks,
                            ostm4.mark_obtained  as moderationMark,
                            act.id AS academicTermId,
                            act.name AS academicTermName,
                            oe.id AS oeExamId,
                            aps.properties ->> '$.isInternal' as isInternal,
                            aps.properties ->> '$.isExternal' as isExternal,
                            IF((aps.properties ->> '$.externalMaxMark') AND (aps.properties ->> '$.isExternal'), aps.properties ->> '$.externalMaxMark', '-') as externalMaxMark,
                            IF((aps.properties ->> '$.internalMaxMark') AND (aps.properties ->> '$.isInternal'), aps.properties ->> '$.internalMaxMark', '-') as internalMaxMark,
                            IF((aps.properties ->> '$.externalMaxMark') AND (aps.properties ->> '$.isExternal'), aps.properties ->> '$.externalMaxMark', 0) + IF((aps.properties ->> '$.internalMaxMark') AND (aps.properties ->> '$.isInternal'), aps.properties ->> '$.internalMaxMark', 0) as totalMaxMark,
                            ostm.mark_obtained + im.internal_mark as totalObtainedMark
                        FROM
                            ec_student_assessment_registration esar
                        INNER JOIN studentaccount sa ON 
                            sa.studentID = esar.student_id
                        INNER JOIN ec_exam_registration_subject eers ON 
                            eers.am_assessment_id = esar.am_assessment_id
                        INNER JOIN  cm_academic_paper_subjects aps ON 
                            aps.id = eers.cm_academic_paper_subjects_id
                        INNER JOIN v4_ams_subject s ON 
                            s.id = aps.ams_subject_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 cm_academic_paper ap ON 
                            aps.cm_academic_paper_id = ap.id
                        INNER JOIN  academic_term act ON 
                            act.id = CAST(eerb.properties ->> '$.academicTermId'AS CHAR) AND act.type = 'SEMESTER'
                        INNER JOIN cm_syllabus_academic_term_settings str ON 
                            str.id = ap.cm_syllabus_academic_term_settings_id 
                        INNER JOIN oe_exams oe ON
                            oe.assessment_id = eers.am_assessment_id AND oe.is_deleted = 0
                        INNER JOIN `groups` g ON 
                            g.id = eerb.groups_id
                        LEFT JOIN ec_internal_marks im ON
                            im.groups_id = eerb.groups_id AND 
                            im.academic_paper_subjects_id = aps.id AND 
                            im.academic_term_id = str.academic_term_id AND im.student_id = sa.studentID
                        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'
                        LEFT JOIN oe_student_total_mark ostm2 ON 
                            ostm2.student_id = sa.studentID AND ostm2.am_assessment_id = esar.am_assessment_id AND ostm2.valuation_type = 'REVALUATION'
                        LEFT JOIN oe_student_total_mark ostm3 ON 
                            ostm3.student_id = sa.studentID AND ostm3.am_assessment_id = esar.am_assessment_id AND ostm3.valuation_type = 'GRACEMARK'
                        LEFT JOIN oe_student_total_mark ostm4 ON 
                            ostm4.student_id = sa.studentID AND ostm4.am_assessment_id = esar.am_assessment_id AND ostm4.valuation_type = 'MODERATION'
                        WHERE
                            sa.regNo = '$request->registerNo' AND eerb.properties ->> '$.academicTermId' IN ($request->academicTermId) AND eer.type='SUPPLEMENTARY' AND eer.properties->>'$.isSpecialExam' = '1' AND eer.trashed IS NULL
                       ";
                $subjects =  $this->executeQueryForList($query.$whereCond.$orderBy, $this->mapper[GraceMarkServiceMapper::GET_STUDENT_SUBJECT_DETAILS]);
        }
        catch (\Exception $e)
        {
            throw new ExamControllerException($e->getCode(),$e->getMessage());
        }
        return $subjects;
    }
}