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 / 71
CRAP
0.00% covered (danger)
0.00%
0 / 2657
ExamRegistrationService
0.00% covered (danger)
0.00%
0 / 1
0.00% covered (danger)
0.00%
0 / 71
177662.00
0.00% covered (danger)
0.00%
0 / 2656
 __construct
0.00% covered (danger)
0.00%
0 / 1
2.00
0.00% covered (danger)
0.00%
0 / 3
 saveExamRegistration
0.00% covered (danger)
0.00%
0 / 1
56.00
0.00% covered (danger)
0.00%
0 / 38
 saveSupplementaryExamRegistration
0.00% covered (danger)
0.00%
0 / 1
2.00
0.00% covered (danger)
0.00%
0 / 5
 saveRegularExamRegistration
0.00% covered (danger)
0.00%
0 / 1
2.00
0.00% covered (danger)
0.00%
0 / 5
 saveRevaluationExamRegistration
0.00% covered (danger)
0.00%
0 / 1
2.00
0.00% covered (danger)
0.00%
0 / 5
 saveImprovementExamRegistration
0.00% covered (danger)
0.00%
0 / 1
2.00
0.00% covered (danger)
0.00%
0 / 5
 validateSaveExamRegistration
0.00% covered (danger)
0.00%
0 / 1
12.00
0.00% covered (danger)
0.00%
0 / 6
 insertExamRegistration
0.00% covered (danger)
0.00%
0 / 1
20.00
0.00% covered (danger)
0.00%
0 / 16
 updateExamRegistration
0.00% covered (danger)
0.00%
0 / 1
30.00
0.00% covered (danger)
0.00%
0 / 23
 deleteExamRegistration
0.00% covered (danger)
0.00%
0 / 1
20.00
0.00% covered (danger)
0.00%
0 / 33
 searchExamRegistration
0.00% covered (danger)
0.00%
0 / 1
462.00
0.00% covered (danger)
0.00%
0 / 89
 publishExamRegistration
0.00% covered (danger)
0.00%
0 / 1
20.00
0.00% covered (danger)
0.00%
0 / 20
 searchStudentRegularExamSubjectDetails
0.00% covered (danger)
0.00%
0 / 1
72.00
0.00% covered (danger)
0.00%
0 / 53
 searchExamRegistrationByOtherDetails
0.00% covered (danger)
0.00%
0 / 1
420.00
0.00% covered (danger)
0.00%
0 / 105
 getAllAssignedExamRegistrationByCurrentStaff
0.00% covered (danger)
0.00%
0 / 1
12.00
0.00% covered (danger)
0.00%
0 / 28
 getAllAssignedExamRegistrationValuationCountByCurrentStaff
0.00% covered (danger)
0.00%
0 / 1
272.00
0.00% covered (danger)
0.00%
0 / 57
 getExamDateOfExamRegistration
0.00% covered (danger)
0.00%
0 / 1
6.00
0.00% covered (danger)
0.00%
0 / 21
 getExamTimeOfExamRegistration
0.00% covered (danger)
0.00%
0 / 1
12.00
0.00% covered (danger)
0.00%
0 / 26
 getAcademicTermsByExamRegistration
0.00% covered (danger)
0.00%
0 / 1
72.00
0.00% covered (danger)
0.00%
0 / 38
 getGrdeSchemeByExamRegistration
0.00% covered (danger)
0.00%
0 / 1
20.00
0.00% covered (danger)
0.00%
0 / 26
 getAllSpecialExamRegistration
0.00% covered (danger)
0.00%
0 / 1
12.00
0.00% covered (danger)
0.00%
0 / 30
 getAllExamRegistredStudentDetails
0.00% covered (danger)
0.00%
0 / 1
90.00
0.00% covered (danger)
0.00%
0 / 67
 saveBatchGroupForExamRevaluation
0.00% covered (danger)
0.00%
0 / 1
12.00
0.00% covered (danger)
0.00%
0 / 19
 insertBatchGroupForRevaluation
0.00% covered (danger)
0.00%
0 / 1
6.00
0.00% covered (danger)
0.00%
0 / 14
 updateBatchGroupForRevaluation
0.00% covered (danger)
0.00%
0 / 1
6.00
0.00% covered (danger)
0.00%
0 / 22
 deleteBatchGroupForRevaluation
0.00% covered (danger)
0.00%
0 / 1
12.00
0.00% covered (danger)
0.00%
0 / 14
 searchStudentExamRevaluation
0.00% covered (danger)
0.00%
0 / 1
182.00
0.00% covered (danger)
0.00%
0 / 113
 getRevaluationTypes
0.00% covered (danger)
0.00%
0 / 1
6.00
0.00% covered (danger)
0.00%
0 / 15
 searchDetailedExamRegistrationDetails
0.00% covered (danger)
0.00%
0 / 1
506.00
0.00% covered (danger)
0.00%
0 / 147
 getMonthName
0.00% covered (danger)
0.00%
0 / 1
12.00
0.00% covered (danger)
0.00%
0 / 16
 getAcademicTermIdByExamRegistrationId
0.00% covered (danger)
0.00%
0 / 1
12.00
0.00% covered (danger)
0.00%
0 / 24
 getAllStudentDetailsForSpecialExam
0.00% covered (danger)
0.00%
0 / 1
56.00
0.00% covered (danger)
0.00%
0 / 77
 getAllStudentDetailsListForSpecialExamRegistration
0.00% covered (danger)
0.00%
0 / 1
30.00
0.00% covered (danger)
0.00%
0 / 64
 getAllDegreesByExamRegistration
0.00% covered (danger)
0.00%
0 / 1
20.00
0.00% covered (danger)
0.00%
0 / 28
 getAllAssessmentDatesByExamRegistration
0.00% covered (danger)
0.00%
0 / 1
30.00
0.00% covered (danger)
0.00%
0 / 29
 getCurrentExamRevaluationDetails
0.00% covered (danger)
0.00%
0 / 1
42.00
0.00% covered (danger)
0.00%
0 / 47
 saveExamRegistrationFeeTemplate
0.00% covered (danger)
0.00%
0 / 1
42.00
0.00% covered (danger)
0.00%
0 / 46
 createExamRegistrationFeeTemplate
0.00% covered (danger)
0.00%
0 / 1
6.00
0.00% covered (danger)
0.00%
0 / 14
 updateExamRegistrationFeeTemplate
0.00% covered (danger)
0.00%
0 / 1
6.00
0.00% covered (danger)
0.00%
0 / 14
 getExamRegistrationType
0.00% covered (danger)
0.00%
0 / 1
90.00
0.00% covered (danger)
0.00%
0 / 32
 searchExamRegistrationForFilter
0.00% covered (danger)
0.00%
0 / 1
110.00
0.00% covered (danger)
0.00%
0 / 56
 searchAllExamRegistration
0.00% covered (danger)
0.00%
0 / 1
272.00
0.00% covered (danger)
0.00%
0 / 65
 getStudentExamAssignedDetails
0.00% covered (danger)
0.00%
0 / 1
6.00
0.00% covered (danger)
0.00%
0 / 22
 getRevaluationStudentAssignedDetails
0.00% covered (danger)
0.00%
0 / 1
30.00
0.00% covered (danger)
0.00%
0 / 37
 getAllExamRevaluation
0.00% covered (danger)
0.00%
0 / 1
306.00
0.00% covered (danger)
0.00%
0 / 80
 getExamRegistrationOfABatchByRequest
0.00% covered (danger)
0.00%
0 / 1
42.00
0.00% covered (danger)
0.00%
0 / 33
 getExamRegistrationStudentListByExamRegId
0.00% covered (danger)
0.00%
0 / 1
6.00
0.00% covered (danger)
0.00%
0 / 32
 getAllAssignedExamRevaluationByCurrentStaff
0.00% covered (danger)
0.00%
0 / 1
56.00
0.00% covered (danger)
0.00%
0 / 51
 getAllAssignedExamRevaluationValuationCountByCurrentStaff
0.00% covered (danger)
0.00%
0 / 1
72.00
0.00% covered (danger)
0.00%
0 / 41
 publishExamTimeTableInStudentSide
0.00% covered (danger)
0.00%
0 / 1
20.00
0.00% covered (danger)
0.00%
0 / 32
 getExamRegistrationCount
0.00% covered (danger)
0.00%
0 / 1
156.00
0.00% covered (danger)
0.00%
0 / 46
 supplyExamMonthAndYearSemesterWiseByStudent
0.00% covered (danger)
0.00%
0 / 1
72.00
0.00% covered (danger)
0.00%
0 / 50
 getExamMonthYearsOfBatchGroup
0.00% covered (danger)
0.00%
0 / 1
30.00
0.00% covered (danger)
0.00%
0 / 26
 saveInstitutionalAverageGenerateStatus
0.00% covered (danger)
0.00%
0 / 1
12.00
0.00% covered (danger)
0.00%
0 / 31
 getStaffHandlingDepartmentExamRegistrations
0.00% covered (danger)
0.00%
0 / 1
72.00
0.00% covered (danger)
0.00%
0 / 55
 saveRevaluationMemoDetails
0.00% covered (danger)
0.00%
0 / 1
6.00
0.00% covered (danger)
0.00%
0 / 14
 getExamRegistrationStartYears
0.00% covered (danger)
0.00%
0 / 1
20.00
0.00% covered (danger)
0.00%
0 / 29
 getRecentPublishedExamRegistration
0.00% covered (danger)
0.00%
0 / 1
6.00
0.00% covered (danger)
0.00%
0 / 21
 getBatchAssignedExamRegistration
0.00% covered (danger)
0.00%
0 / 1
20.00
0.00% covered (danger)
0.00%
0 / 30
 getSupplyPublishDetailsByBatch
0.00% covered (danger)
0.00%
0 / 1
90.00
0.00% covered (danger)
0.00%
0 / 48
 getAllExamRegistrationForVivaValuationStaff
0.00% covered (danger)
0.00%
0 / 1
6.00
0.00% covered (danger)
0.00%
0 / 32
 getMinorHonorMoocAppliedStudents
0.00% covered (danger)
0.00%
0 / 1
20.00
0.00% covered (danger)
0.00%
0 / 26
 getCourseTypeOfExamRegistration
0.00% covered (danger)
0.00%
0 / 1
12.00
0.00% covered (danger)
0.00%
0 / 30
 getMoocAppliedStudentDetails
0.00% covered (danger)
0.00%
0 / 1
72.00
0.00% covered (danger)
0.00%
0 / 61
 getCurrentExamRegistrationDetailsForPaymentModule
0.00% covered (danger)
0.00%
0 / 1
6.00
0.00% covered (danger)
0.00%
0 / 15
 searchDetailedExamRevaluationDetails
0.00% covered (danger)
0.00%
0 / 1
240.00
0.00% covered (danger)
0.00%
0 / 130
 getStudentCourseTypeByExamRegistration
0.00% covered (danger)
0.00%
0 / 1
20.00
0.00% covered (danger)
0.00%
0 / 27
 getPaymentGateWayModulesForCourseTypes
0.00% covered (danger)
0.00%
0 / 1
56.00
0.00% covered (danger)
0.00%
0 / 16
 getRegularExamRegistrationIdBySupplyExamRegistrationId
0.00% covered (danger)
0.00%
0 / 1
20.00
0.00% covered (danger)
0.00%
0 / 27
 updateFalseNumberQueueUpdateStatus
0.00% covered (danger)
0.00%
0 / 1
12.00
0.00% covered (danger)
0.00%
0 / 20
 searchAllProgramsByExamRegistration
0.00% covered (danger)
0.00%
0 / 1
56.00
0.00% covered (danger)
0.00%
0 / 39
<?php
namespace com\linways\ec\core\service;
use com\linways\ec\core\dto\ExamRegistration;
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\ExamRegistrationServiceMapper;
use com\linways\core\ams\professional\logging\AMSLogger;
use com\linways\ec\core\logging\Events;
use com\linways\ec\core\logging\entities\Staff;
use com\linways\ec\core\request\SearchExamRegistrationRequest;
use com\linways\ec\core\constant\ExamRegistrationTypeConstants;
use com\linways\ec\core\request\SearchStudentExamRegistrationRequest;
use com\linways\ec\core\service\ExamRegistrationSubjectService;
use com\linways\base\util\TwigRenderer;
use com\linways\core\ams\professional\util\PdfUtil;
use com\linways\ec\core\dto\ExamRegistrationFeeTemplate;
use com\linways\core\ams\professional\constant\academic\GroupTypeConstant as ProfessionalGroupTypeConstant;
use com\linways\ec\core\service\CommonExamService;
use com\linways\core\ams\professional\constant\SettingsConstants;
use com\linways\core\ams\professional\service\CommonService;
use com\linways\ec\core\request\SearchRuleRequest;
use com\linways\ec\core\service\RuleService;
use com\linways\ec\core\service\ExamValuationService;
use com\linways\core\ams\professional\service\CourseTypeService;
use com\linways\ec\core\service\ExamFeeTypeService;
use stdClass;
use function PHPSTORM_META\type;
class ExamRegistrationService extends BaseService
{
    use MakeSingletonTrait;
    private function __construct() {
        $this->logger = AMSLogger::getLogger('exam-controller-log');
        $this->mapper = ExamRegistrationServiceMapper::getInstance()->getMapper();
    }
    /**
     * Save ExamRegistration
     * @param ExamRegistration $examRegistration
     * @return $id
     */
    public function saveExamRegistration (ExamRegistration $examRegistration)
    {
        $examRegistration = $this->realEscapeObject($examRegistration);
        $examRegistration->createdBy = $GLOBALS['userId'] ?? $examRegistration->createdBy;
        $examRegistration->updatedBy = $GLOBALS['userId'] ?? $examRegistration->updatedBy;
        $staffId = $GLOBALS['userId'];
        try{
            $this->validateSaveExamRegistration($examRegistration);
            if(!empty($examRegistration->id))
            {
                $examRegistration->id = $this->updateExamRegistration($examRegistration);
            }
            else
            {
                $examRegistration->id = $this->insertExamRegistration($examRegistration);
            }
            AMSLogger::log_info($this->logger,Events::EC_SAVE_EXAM_REGISTRATION,[
                "staff" => new Staff(["id" => $staffId]),
                    "request" => $examRegistration,
                    "status" => StatusConstants::SUCCESS
            ]);
            
        }catch(\Exception $e) {
            AMSLogger::log_error($this->logger,Events::EC_SAVE_EXAM_REGISTRATION, [
                "staff" => new Staff(["id" => $staffId]),
                "request" => $examRegistration,
                "errorCode" => $e->getCode(),
                "errorMessage" => $e->getMessage(),
                "status" => StatusConstants::FAILED
            ]);
            if($e->getCode() !== ExamControllerException::INVALID_PARAMETERS_EXAM_REGISTRATION && $e->getCode() !== ExamControllerException::EMPTY_PARAMETERS_EXAM_REGISTRATION && $e->getCode() !== "DUPLICATE_ENTRY") {
                throw new ExamControllerException($e->getCode(),"Failed to save Exam Registration! Please try again");
            } else if ($e->getCode() === ExamControllerException::DUPLICATE_ENTRY) {
                throw new ExamControllerException (ExamControllerException::DUPLICATE_ENTRY_EXAM_REGISTRATION,"Cannot create Exam Registration.This Exam is already registered!");
            } else {
                throw new ExamControllerException ($e->getCode(),$e->getMessage());
            }
        }
        return $examRegistration->id ;
        
    }
    /**
     * Save Supplementary ExamRegistration
     * @param ExamRegistration $examRegistration
     * @return $id
     */
    public function saveSupplementaryExamRegistration (ExamRegistration $examRegistration)
    {
        $examRegistration = $this->realEscapeObject($examRegistration);
        $examRegistration->type = ExamRegistrationTypeConstants::SUPPLEMENTARY;
        $this->saveExamRegistration($examRegistration);
    }
     /**
     * Save Regular Exam Registration
     * @param ExamRegistration $examRegistration
     * @return $id
     */
    public function saveRegularExamRegistration (ExamRegistration $examRegistration)
    {
        $examRegistration = $this->realEscapeObject($examRegistration);
        $examRegistration->type = ExamRegistrationTypeConstants::REGULAR;
        $this->saveExamRegistration($examRegistration); 
    }
     /**
     * Save Revaluation Exam Registration
     * @param ExamRegistration $examRegistration
     * @return $id
     */
    public function saveRevaluationExamRegistration (ExamRegistration $examRegistration)
    {
        $examRegistration = $this->realEscapeObject($examRegistration);
        $examRegistration->type = ExamRegistrationTypeConstants::REVALUATION;
        $this->saveExamRegistration($examRegistration);
    }
    /**
     * Save Improvement Exam Registration
     * @param ExamRegistration $examRegistration
     * @return $id
     */
    public function saveImprovementExamRegistration (ExamRegistration $examRegistration)
    {
        $examRegistration = $this->realEscapeObject($examRegistration);
        $examRegistration->type = ExamRegistrationTypeConstants::IMPROVEMENT;
        $this->saveExamRegistration($examRegistration);
    }
    /**
     * Validate examRegistration Request Before Saving
     * @param ExamRegistration $examRegistration
     * @return NULL
     */
    private function validateSaveExamRegistration(ExamRegistration $examRegistration)
    {
        if(empty($examRegistration->name))
            throw new ExamControllerException(ExamControllerException::EMPTY_PARAMETERS_EXAM_REGISTRATION," Exam Registration name is  empty! Please choose name for Exam Registration");
        if(empty($examRegistration->type))
            throw new ExamControllerException(ExamControllerException::EMPTY_PARAMETERS_EXAM_REGISTRATION," Exam Registration type is  empty! Please choose any type for Exam Registration");
    }
   
    
    /**
     * Insert ExamRegistration
     * @param ExamRegistration $examRegistration
     * @return  $id
     */
    private function insertExamRegistration(ExamRegistration $examRegistration)
    {
        $properties = !empty($examRegistration->properties) ? "'" . json_encode($examRegistration->properties) . "'" : "NULL";
        $identifyingContext = !empty($examRegistration->identifyingContext) ? "'" . json_encode($examRegistration->identifyingContext) . "'" : "NULL";
        
        $id = SecurityUtils::getRandomString();
        $query = "INSERT INTO ec_exam_registration
                  (id,identifying_context,name,type,properties,created_by,updated_by)
                  VALUES
                  ('$id',$identifyingContext,'$examRegistration->name','$examRegistration->type',$properties,'$examRegistration->createdBy','$examRegistration->updatedBy')";
        
        try {
           $this->executeQuery($query);
           $examRegistration->id = $id;
           return $examRegistration->id;
        } catch (\Exception $e) {
             throw new ExamControllerException($e->getCode(),$e->getMessage());
        }
    }
    /**
     * Update ExamRegistration
     * @param ExamRegistration $examRegistration
     * @return String $examRegistration->id
     */
    private function updateExamRegistration(ExamRegistration $examRegistration)
    {
        $properties = !empty($examRegistration->properties) ? "'".json_encode($examRegistration->properties)."'" : "NULL";
        $feeProperties = !empty($examRegistration->feesProperties) ? "'".json_encode($examRegistration->feesProperties)."'" : "NULL";
        $identifyingContext = !empty($examRegistration->identifyingContext) ? "'" . json_encode($examRegistration->identifyingContext) . "'" : "NULL";
        $query = "UPDATE
                    ec_exam_registration
                SET
                    identifying_context = $identifyingContext,
                    name = '$examRegistration->name',
                    type = '$examRegistration->type',
                    properties = $properties,
                    fees_properties = $feeProperties,
                    updated_by = '$examRegistration->updatedBy'
                WHERE
                    id = '$examRegistration->id'";
        try 
        {
            $this->executeQuery($query);
            return $examRegistration->id;
        } catch (\Exception $e) {
            throw new ExamControllerException($e->getCode(),$e->getMessage());
        }
    }
    /**
     * Delete examRegistration (Soft Delete)
     * @param String $id
     * @return NULL
     */
    public function deleteExamRegistration($id)
    {
        $id = $this->realEscapeString($id);
        $staffId = $GLOBALS['userId'];
        $searchRequest = new SearchExamRegistrationRequest();
        $searchRequest->id = $id;
        $examRegistration = reset($this->searchExamRegistration($searchRequest));
        if(empty($id) && empty($examRegistration))
            throw new ExamControllerException(ExamControllerException::EMPTY_PARAMETERS_EXAM_REGISTRATION,"Exam Registration is invalid! Please enter a valid Exam Registration");
        $query = "UPDATE
                    ec_exam_registration
                SET
                    trashed = UTC_TIMESTAMP(),
                    updated_by = '$staffId'
                WHERE
                    id = '$id'";
        try {
            $this->executeQuery($query);
            AMSLogger::log_info($this->logger,Events::EC_DELETE_EXAM_REGISTRATION,[
                "staff" => new Staff(["id" => $staffId]),
                    "request" => $examRegistration,
                    "status" => StatusConstants::SUCCESS
            ]);
        } catch (\Exception $e) {
            AMSLogger::log_error($this->logger,Events::EC_DELETE_EXAM_REGISTRATION,[
                "staff" => new Staff(["id" => $staffId]),
                "request" => $examRegistration,
                "errorCode" => $e->getCode(),
                "errorMessage" => $e->getMessage(),
                "status" => StatusConstants::FAILED
            ]);
            throw new ExamControllerException(ExamControllerException::ERROR_DELETING_EXAM_REGISTRATION,"Error deleting Exam Registration! Please try again");
        }
    }
    /**
     * Search examRegistration
     * @param SearchExamRegistrationRequest $request
     * @return examRegistration
     */
    public function searchExamRegistration(SearchExamRegistrationRequest $request)
    {
        $request = $this->realEscapeObject($request);
        $whereQuery = "";
        $limitQuery = "";
        $joinQueary = "";
        $sortBy = " ORDER BY eer.created_date DESC";
        if(!empty($request->id)){
            $whereQuery .= " AND eer.id='$request->id";
        }
        if(!empty($request->name)){
            $whereQuery .= " AND eer.name LIKE '%$request->name%'";
        }
        if(!empty($request->type)) {
            $examTypeString= "'" . implode("', '", $request->type) . "'" ;
            $whereQuery .= " AND eer.type  IN ($examTypeString)";
        }
        if(!empty($request->parentExamRegistrationId)) {
            $whereQuery .= " AND CAST(eer.properties->>'$.parentExamRegistrationId' AS CHAR) = '$request->parentExamRegistrationId'";
        }
        
        if($request->trashed === StatusConstants::ACTIVE) {
            $whereQuery .= " AND eer.trashed IS NULL ";
        }
        if($request->trashed === StatusConstants::TRASHED) {
            $whereQuery .= " AND eer.trashed IS NOT NULL ";
        }
        if($request->startIndex !== "" && $request->endIndex !== ""){
            $limitQuery .= " LIMIT $request->startIndex,$request->endIndex";
        }
        if(!empty($request->courseTypeId) || !empty($request->startYear) || !empty($request->currentTermId)) {
            $joinQueary = " INNER JOIN ec_exam_registration_batch eerb
                    ON eerb.ec_exam_registration_id =  eer.id
                INNER JOIN `groups` g
                    ON g.id =  eerb.groups_id
                INNER JOIN program p 
                    ON p.id  = g.properties->>'$.programId'";   
            if(!empty($request->courseTypeId)) {
                $whereQuery .= " AND p.course_type_id = '$request->courseTypeId'";
            }
            if(!empty($request->startYear)) {
                $whereQuery .= " AND g.properties ->> '$.startYear' = '$request->startYear'";
            }
            if(!empty($request->currentTermId)) {
                $whereQuery .= " AND CAST(eerb.properties ->> '$.academicTermId'AS CHAR) = '$request->currentTermId";
            }
        }
        if(($request->isPublishedOnlyRegistration)) {
            $whereQuery .= " AND eer.properties->>'$.publish' = '1' ";
        }
        else if(($request->isNotPublishedOnlyRegistration)) {
            $whereQuery .= " AND eer.properties->>'$.publish' = '0' ";
        }
        if(!empty($request->ids)){
            $idString = is_array($request->ids) ? "'" . implode("','",$request->ids) . "'" : "'".$request->ids."'";
            $whereQuery .= " AND eer.id IN ( $idString )";
        }
        if($request->resultPublishedRegistrationOnly) {
            $joinQueary = " INNER JOIN ec_exam_registration_batch eerb
                    ON eerb.ec_exam_registration_id =  eer.id ";   
            $whereQuery .= " AND  eerb.properties ->>'$.isResultPublished' = 1 ";
        }
        
        $query = "SELECT
            eer.id,
            eer.identifying_context,        
            eer.name,
            eer.type,
            eer.properties,
            eer.fees_properties,
            eer.trashed,
            eer.created_by,
            eer.created_date,
            eer.updated_by,
            eer.updated_date,
            eerp.id as parentExamRegistrationId,
            eerp.name as parentExamRegistrationName,
            eerp.type as parentExamRegistrationType
        FROM
            ec_exam_registration eer
        LEFT JOIN ec_exam_registration eerp ON 
            eerp.id = CAST(eer.properties->>'$.parentExamRegistrationId' AS CHAR)
            $joinQueary
        WHERE
            1 = 1";
        try {
            $examRegistration = $this->executeQueryForList($query.$whereQuery.$sortBy.$limitQuery, $this->mapper[ExamRegistrationServiceMapper::SEARCH_EXAM_REGISTRATION]);
        } catch (\Exception $e) {
            throw new ExamControllerException(ExamControllerException::ERROR_FETCHING_EXAM_REGISTRATION,"Cannot fetch Exam Registration details! Please try again.");
        }
        return $examRegistration;
    }
    /**
     * Publish examRegistration 
     * @param String $id
     * @param Boolean $publish
     * @return NULL
     */
    public function publishExamRegistration($id, $publish)
    {
        $id = $this->realEscapeString($id);
        $publish = $this->realEscapeString($publish);
        $updatedBy = $GLOBALS['userId'];
        if(empty($id))
            throw new ExamControllerException(ExamControllerException::EMPTY_PARAMETERS_EXAM_REGISTRATION,"Exam Registration is invalid! Please enter a valid Exam Registration");
        $publish = $publish ? 1 : 0;
        $query = "UPDATE
                    ec_exam_registration
                SET
                    properties = JSON_SET(properties, '$.publish', $publish),
                    updated_by = '$updatedBy'
                WHERE
                    id = '$id'";
        try {
            $this->executeQuery($query);
        } catch (\Exception $e) {
            throw new ExamControllerException(ExamControllerException::ERROR_UPDATE_PUBLISH_STATUS_EXAM_REGISTRATION,"Error deleting Exam Registration! Please try again");
        }
    }
    /**
     * Search Student registration Details
     * @param $request
     * @return $subjects
     */
    private function searchStudentRegularExamSubjectDetails($request)
    {
        $request = $this->realEscapeObject($request);
        $whereQuery = "";
        $limitQuery = "";
        if(!empty($request->id)) {
            $whereQuery .= " AND ecscmd.id = '$request->id'";
        }
        if(!empty($request->studentId)) {
            $whereQuery .= " AND ecscmd.student_id = '$request->studentId'";
        }
        if(!empty($request->groupId)) {
            $whereQuery .= " AND ecscmd.groups_id = '$request->groupId'";
        }
        if(!empty($request->paperSubjectId)) {
            $paperSubjectIdString = is_array($request->paperSubjectId) ? "'" . implode("','",$request->paperSubjectId) . "'" : '$request->paperSubjectId';
            $whereQuery .= " ecscmd.paper_subject_id IN($paperSubjectIdString)";
        }
        // if($request->startIndex !== "" && $request->endIndex !== "")
        // {
        //     $limitQuery .= " LIMIT $request->startIndex,$request->endIndex";
        // }
        $query = "SELECT
        ecscmd.id,
        ecscmd.student_id,
        ecscmd.mark_details,
        ecscmd.mark_history,
        ecscmd.no_of_chances_taken,
        ecscmd.total_marks,
        ecscmd.percentage,
        ecscmd.grade,
        ecscmd.grade_point,
        ecscmd.credit_grade_point,
        ecscmd.class,
        ecscmd.status,
        ecscmd.is_dirty,
        ecscmd.created_by,
        ecscmd.created_date,
        ecscmd.updated_by,
        ecscmd.updated_date,
        ecscmd.paper_subject_id,
        ecscmd.groups_id
    FROM
        ec_subject_consolidated_mark_details ecscmd
    WHERE
        1 = 1
        ";
        try {
            $subjectDetails = $this->executeQueryForList($query.$whereQuery.$limitQuery,$this->mapper[ExamRegistrationServiceMapper::SEARCH_STUDENT_SUBJECTS_MARK_DETAILS]);
        } catch (\Exception $e) {
            throw new ExamControllerException(ExamControllerException::ERROR_FETCHING_STUDENT_REGISTRATION,"Cannot fetch studentRegistration details! Please try again");
        }
        $subjects = [];
        foreach ($subjectDetails as $subject) {
            $subjects[$subject->paperSubjectId] = $subject;
        }
        return $subjects;
    }
     /**
     * Search examRegistration
     * @param  $request
     * @return examRegistration
     */
    public function searchExamRegistrationByOtherDetails( $request)
    {
        $request = $this->realEscapeObject($request);
        if($request->odrerBy == "ASSENDING_EXAM_MONTH_AND_YEAR"){
            $orderBy = " ORDER BY eer.properties ->> '$.examYear' ASC ,eer.properties ->> '$.examMonth' + 0 ASC ";
        }
        else{
            $orderBy = " ORDER BY eer.properties ->> '$.examYear' DESC ,eer.properties ->> '$.examMonth' + 0 DESC ";
        }
        $whereQuery = "";
        $limitQuery = "";
        $joinQueary = "";
        if(!empty($request->id)){
            $whereQuery .= " AND eer.id='$request->id";
        }
        if(!empty($request->name)){
            $whereQuery .= " AND eer.name LIKE '%$request->name%'";
        }
        if(!empty($request->examYear)){
            $whereQuery .= " AND eer.properties ->> '$.examYear' = '$request->examYear'";
        }
        if(!empty($request->courseTypeId)){
            $whereQuery .= " AND p.course_type_id = '$request->courseTypeId";
        }
        if(!empty($request->groupId)){
            $groupIdString = is_array($request->groupId) ? "'" . implode("','",$request->groupId) . "'" : "'".$request->groupId."'";
            $whereQuery .= " AND g.id IN ( $groupIdString )";
        }
        if(!empty($request->academicTermId)){
            $whereQuery .= " AND CAST(eerb.properties ->> '$.academicTermId'AS CHAR) = '$request->academicTermId'";
        }
        if(!empty($request->batchStartYear)){
            $whereQuery .= " AND g.properties ->> '$.startYear' = '$request->batchStartYear'";
        }
        if(!empty($request->type)) {
            $examTypeString = is_array($request->type) ? "'" . implode("','",$request->type) . "'" : "'".$request->type."'";
            $whereQuery .= " AND eer.type IN ( $examTypeString )";
        }
        if($request->isSpecialExamRegistration){
            $whereQuery .= " AND eer.properties->>'$.isSpecialExam' = true ";
        }
        if($request->isMinorOrHonorExamRegistration){
            $whereQuery .= " AND (eer.properties ->> '$.isHonorCourse' = '1' OR eer.properties ->> '$.isMinorCourse' = '1')";
        }
        if($request->isAvoidMinorHonorExamRegistration){
            $whereQuery .= "  AND  ( (eer.properties ->> '$.isHonorCourse' IS NULL OR eer.properties ->> '$.isHonorCourse' != '1') AND (eer.properties ->> '$.isMinorCourse' IS NULL OR eer.properties ->> '$.isMinorCourse' != '1')) ";
        }
        // else{
        //     $whereQuery .= " AND  ( (eer.properties ->> '$.isHonorCourse' IS NULL OR eer.properties ->> '$.isHonorCourse' != '1') AND (eer.properties ->> '$.isMinorCourse' IS NULL OR eer.properties ->> '$.isMinorCourse' != '1'))";
        // }
        if(!empty($request->registerNo)) {
            $joinQueary = " INNER JOIN ec_exam_registration_subject eers ON
                                eers.ec_exam_registration_batch_id = eerb.id
                            INNER JOIN ec_student_assessment_registration esar ON
                                esar.am_assessment_id = eers.am_assessment_id AND esar.ec_exam_registration_type = eer.type
                            INNER JOIN studentaccount sa ON 
                                sa.studentID = esar.student_id 
                            INNER JOIN student_program_account spa ON 
                                spa.student_id  = esar.student_id  AND
                                spa.current_program_id = p.id ";
            $whereQuery .= " AND spa.properties->>'$.registerNumber' LIKE '%$request->registerNo%'";
        }
        if(!empty($request->rollNo)) {
            $joinQueary = " INNER JOIN ec_exam_registration_subject eers ON
                                eers.ec_exam_registration_batch_id = eerb.id
                            INNER JOIN ec_student_assessment_registration esar ON
                                esar.am_assessment_id = eers.am_assessment_id AND esar.ec_exam_registration_type = eer.type
                            INNER JOIN studentaccount sa ON 
                                sa.studentID = esar.student_id 
                            INNER JOIN student_program_account spa ON 
                                spa.student_id  = esar.student_id  AND
                                spa.current_program_id = p.id ";
            $whereQuery .= " AND spa.properties->>'$.rollNumber' LIKE '%$request->rollNo%'";
        }
        if(!empty($request->typeForRevaluation)) {
            $joinQueary .= " INNER JOIN  ec_exam_registration eerp ON
            eerp.id = TRIM(BOTH "."'\"'"." FROM eer.parent_registration_id)";
            $whereQuery .= " AND eerp.type LIKE '%$request->typeForRevaluation%'";
        }
        if(!empty($request->examDate)) {
            $joinQueary .= " INNER JOIN ec_exam_registration_subject eers ON
                                eers.ec_exam_registration_batch_id = eerb.id
                            INNER JOIN am_assessment aa ON
                                aa.id = eers.am_assessment_id";
            $whereQuery .= " AND aa.properties_value ->>'$.assessmentDate' = '$request->examDate'";
        }
        
        $query = "SELECT
            DISTINCT(eer.id),
            eer.name,
            eer.properties,
            eer.type
        FROM
            ec_exam_registration eer
        INNER JOIN ec_exam_registration_batch eerb
            ON eerb.ec_exam_registration_id =  eer.id
        INNER JOIN `groups` g
            ON g.id =  eerb.groups_id
        INNER JOIN program p 
            ON p.id  = g.properties->>'$.programId'
        $joinQueary 
        WHERE
            eer.trashed IS NULL";
        try {
            $examRegistration = $this->executeQueryForList($query.$whereQuery.$orderBy.$limitQuery, $this->mapper[ExamRegistrationServiceMapper::SEARCH_EXAM_REGISTRATION]);
        } catch (\Exception $e) {
            throw new ExamControllerException(ExamControllerException::ERROR_FETCHING_EXAM_REGISTRATION,"Cannot fetch Exam Registration details! Please try again.");
        }
        return $examRegistration;
    }
     /**
     * Search examRegistration
     * @param  $request
     * @return examRegistration
     */
    public function getAllAssignedExamRegistrationByCurrentStaff( $request)
    {
        $request = $this->realEscapeObject($request);
        $currentUerId = $GLOBALS['userId'];
        $whereQuery = "";
        $limitQuery = "";
        if(!empty($request->type)) 
        {
            $whereQuery .= " AND eer.type LIKE '%$request->type%'";
        }
        $query = "SELECT
            DISTINCT(eer.id),
            eer.name
        FROM
            ec_exam_registration_subject eers
        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.trashed IS NULL
        WHERE
            JSON_CONTAINS(JSON_EXTRACT(eers.valuation_details ,'$.valuationStaffs'),JSON_OBJECT('addiitonalExamniners', '$currentUerId')) $whereQuery OR
            JSON_CONTAINS(JSON_EXTRACT(eers.valuation_details ,'$.valuationStaffs'),JSON_OBJECT('chiefExaminers', '$currentUerId')) $whereQuery";
        try {
            $examRegistration = $this->executeQueryForList($query.$limitQuery, $this->mapper[ExamRegistrationServiceMapper::SEARCH_EXAM_REGISTRATION]);
        } catch (\Exception $e) {
            throw new ExamControllerException(ExamControllerException::ERROR_FETCHING_EXAM_REGISTRATION,"Cannot fetch Exam Registration details! Please try again.");
        }
        return $examRegistration;
    }
     /**
     * Search examRegistration Valuation Count
     * @param  $request
     * @return examRegistration
     */
    public function getAllAssignedExamRegistrationValuationCountByCurrentStaff($request){
        $valuationCountObjectArray = [];
        $valuationCountArray = [];
        $currentUerId = $GLOBALS['userId'];
        $searchRuleRequest = new SearchRuleRequest;
        $searchRuleRequest->name = "EXAM_VALUATION_PROCEDURE_RULE";
        $examValuationProcedureRule = reset(RuleService::getInstance()->searchRule($searchRuleRequest))->rule;
        $considerAdditionalAndChiefValuationForUg = $examValuationProcedureRule->considerAdditionalAndChiefValuationForUg ? true : false;            
        $valuationCountNames = $examValuationProcedureRule->valuationCountNames ? json_decode($examValuationProcedureRule->valuationCountNames):"";
        $valuationCountNamesArray = [];
        foreach ($valuationCountNames as $valuationCountName) {
            $valuationCountNamesArray[$valuationCountName->id] = $valuationCountName;
        }
        $subjects =  ExamRegistrationSubjectService::getInstance()->getAllCurretStaffSubjectsValuationDetailsByExamRegistrationId($request);
        foreach($subjects as $subject)
        {
            $subject->valuationDetails = json_decode($subject->valuationDetails);
            foreach($subject->valuationDetails->valuationStaffs as $valuationStaff)
            {
                foreach($valuationStaff->addiitonalExamniners as $staff)
                {
                    if($currentUerId == $staff)
                    {
                        $valuationCountArray[$valuationStaff->count] = $valuationStaff->count;
                    }
                }
                foreach($valuationStaff->chiefExaminers as $staff)
                {
                    if($currentUerId == $staff)
                    {
                        $valuationCountArray[$valuationStaff->count] = $valuationStaff->count;
                    }
                }
            }
        }
        sort($valuationCountArray);
        foreach($valuationCountArray as $valuationCount)
        {
            $valuaionCount = new \stdClass;
            $valuaionCount->id= $valuationCount;
            $valuaionCount->text= $valuationCountNamesArray[$valuationCount]->name ? $valuationCountNamesArray[$valuationCount]->name : $valuationCount;
            if ( $considerAdditionalAndChiefValuationForUg ){
                $courseTypeId = ExamValuationService::getInstance()->getCourseTypeIdByExamRegistrationIdAndPaperSubjectId($request->examRegistrationId,reset($subjects)->academicPaperSubjectId);
                $courseType = CourseTypeService::getInstance()->getCourseTypesById ($courseTypeId);
                if(!$courseType->extValRequired){
                    if ( $valuationCount == 1 ){
                        $valuaionCount->text= "Additional Examination";
                        $valuaionCount->name= "Additional Examination";
                    } else if ( $valuationCount == 2 ){
                        $valuaionCount->text= "Chief Examination";
                        $valuaionCount->name= "Chief Examination";
                    }
                }
            }
            $valuationCountObjectArray[]= $valuaionCount;
        }
        return $valuationCountObjectArray;
    }
    
     /**
     * exam date
     * @param  $request
     * @return examRegistration
     */
    public function getExamDateOfExamRegistration($examRegistrationId)
    {
        $examRegistrationId = $this->realEscapeString($examRegistrationId);
        $whereQuery = "";
        $limitQuery = "";
        $query = "SELECT DISTINCT
            aa.properties_value->>'$.assessmentDate' AS examDate
        FROM
            ec_exam_registration_batch eerb
        INNER JOIN ec_exam_registration_subject eers ON
            eers.ec_exam_registration_batch_id = eerb.id
        INNER JOIN am_assessment aa ON
            aa.id = eers.am_assessment_id
        WHERE
            eerb.ec_exam_registration_id = '$examRegistrationId' ORDER BY aa.properties_value->>'$.assessmentDate' ASC ";
        try {
            $examDates = $this->executeQueryForList($query);
        } catch (\Exception $e) {
            throw new ExamControllerException(ExamControllerException::ERROR_FETCHING_EXAM_REGISTRATION,"Cannot fetch Exam Registration details! Please try again.");
        }
        return $examDates;
    }
     /**
     * exam times
     * @param  $request
     * @return examRegistration
     */
    public function getExamTimeOfExamRegistration($examRegistrationId, $examDate=null)
    {
        $examRegistrationId = $this->realEscapeString($examRegistrationId);
        $examDate = $this->realEscapeString($examDate);
        $whereQuery = "";
        $limitQuery = "";
        if (!empty($examDate)) {
            $whereQuery .= " AND aa.properties_value->>'$.assessmentDate'='$examDate";
        }
        $query = "SELECT DISTINCT
            CONCAT(TIME_FORMAT(aa.properties_value->>'$.startTime', '%l:%i %p'),' - ',TIME_FORMAT(aa.properties_value->>'$.endTime', '%l:%i %p')) AS examTime
        FROM
            ec_exam_registration_batch eerb
        INNER JOIN ec_exam_registration_subject eers ON
            eers.ec_exam_registration_batch_id = eerb.id
        INNER JOIN am_assessment aa ON
            aa.id = eers.am_assessment_id
        WHERE
            eerb.ec_exam_registration_id = '$examRegistrationId'
            $whereQuery";
        try {
            $examTimes = $this->executeQueryForList($query);
        } catch (\Exception $e) {
            throw new ExamControllerException(ExamControllerException::ERROR_FETCHING_EXAM_REGISTRATION,"Cannot fetch Exam Registration details! Please try again.");
        }
        return $examTimes;
    }
      /**
     * get academic terms by exam registration Id
     * @param  $request
     * @return academicTerms
     */
    public function getAcademicTermsByExamRegistration($request)
    {
        $whereQuery = "";
        $orderBy = " ORDER BY acte.name ASC";
        $request = $this->realEscapeObject($request);
        if(!empty($request->examRegistrationId)){
            $examRegistrationIdString = is_array($request->examRegistrationId) ? "'" . implode("','",$request->examRegistrationId) . "'" : "'".$request->examRegistrationId."'";
            $whereQuery .= " AND  eerb.ec_exam_registration_id IN ( $examRegistrationIdString )";
        }
        if(!empty($request->groupId)){
            $groupIdString = is_array($request->groupId) ? "'" . implode("','",$request->groupId) . "'" : "'".$request->groupId."'";
            $whereQuery .= " AND  eerb.groups_id IN ( $groupIdString )";
        }
        if(!empty($request->degreeId)){
            $degreeIdString = is_array($request->degreeId) ? "'" . implode("','",$request->degreeId) . "'" : "'".$request->degreeId."'";
            $whereQuery .= " AND  deg.id IN ( $degreeIdString )";
        }
        
        $query = "SELECT DISTINCT
                    acte.id,
                    acte.name
                FROM
                    ec_exam_registration_batch eerb
                INNER JOIN academic_term acte ON
                    acte.id = CAST(eerb.properties ->> '$.academicTermId'AS CHAR) 
                INNER JOIN `groups` g ON
                    g.id =  eerb.groups_id
                INNER JOIN program p ON
                    p.id =  CAST(g.properties ->> '$.programId'AS CHAR) 
                INNER JOIN degree deg ON
                    deg.id = p.degree_id
                WHERE
                    1 = 1 ";
        try {
            $academicTerms = $this->executeQueryForList($query.$whereQuery.$orderBy);
        } catch (\Exception $e) {
            throw new ExamControllerException(ExamControllerException::ERROR_FETCHING_EXAM_REGISTRATION_ACADEMIC_TERM,"Cannot fetch Semester Details ! Please try again.");
        }
        return $academicTerms;
    }
      /**
     * get Grade Scheme by exam registration Id
     * @param  $request
     * @return gradeSchemes
     */
    public function getGrdeSchemeByExamRegistration($request)
    {
        $whereQuery = "";
        if(!empty($request->examRegistrationId)){
            $examRegistrationIdString = is_array($request->examRegistrationId) ? "'" . implode("','",$request->examRegistrationId) . "'" : "'".$request->examRegistrationId."'";
            $whereQuery .= " AND  eerb.ec_exam_registration_id IN ( $examRegistrationIdString )";
        }
        $request = $this->realEscapeObject($request);
        $query = "SELECT
                    DISTINCT  g.id,
                    g.name,
                    g.properties ->> '$.class' as class,
                    g.range_from,
                    g.range_to,
                    g.properties,
                    gs.type
                FROM
                    grade_scheme gs
                INNER JOIN grade g ON 
                    g.grade_scheme_id = gs.id";
        
        try {
            $gradeSchemes = $this->executeQueryForList($query);
        } catch (\Exception $e) {
            throw new ExamControllerException(ExamControllerException::ERROR_FETCHING_EXAM_REGISTRATION_GRADE_SCHEMES,"Cannot fetch Grade Details ! Please try again.");
        }
        return $gradeSchemes;
    }
     /**
     * Search All Special Exam Registrations
     * @param $request
     * @return $examRegistrations
     */
    public function getAllSpecialExamRegistration($request)
    {
        $request = $this->realEscapeObject($request);
        $whereQuery = "";
        $limitQuery = "";
        if(!empty($request->specialExamType)) {
            $whereQuery .= " AND  CAST(eer.properties->>'$.criteriaDuringSpecialExam' AS CHAR)= '$request->specialExamType'";
        }
        $query = "SELECT
                    eer.id,
                    eer.identifying_context,
                    eer.name,
                    eer.properties->>'$.discription' AS `description`,
                    eer.type,
                    eer.properties,
                    eer.trashed,
                    eer.created_by,
                    eer.created_date,
                    eer.updated_by,
                    eer.updated_date
                FROM
                    ec_exam_registration eer
                WHERE
                    eer.trashed IS NULL AND eer.properties->>'$.isSpecialExam' = true";
        try {
            $examRegistrations = $this->executeQueryForList($query.$whereQuery.$limitQuery,$this->mapper[ExamRegistrationServiceMapper::SEARCH_EXAM_REGISTRATION]);
        } catch (\Exception $e) {
            throw new ExamControllerException(ExamControllerException::ERROR_FETCHING_EXAM_REGISTRATION,"Cannot fetch Exam Registration details! Please try again.");
        }
        return $examRegistrations;
    }
      /**
     * get All Students For Subject Wise SUPPLEMENTARY Status
     * @param $searchRequest 
     * @return $studentDetails 
     */
    public function getAllExamRegistredStudentDetails($searchRequest) {
        $searchRequest = $this->realEscapeObject($searchRequest);
        try
        {
            $whereQuery = "";
            if(!empty($searchRequest->courseTypeId)) {
                $whereQuery .= " AND g.properties ->> '$.courseTypeId' = '$searchRequest->courseTypeId'";
            }
            if(!empty($searchRequest->admissionYear)) {
                $whereQuery .= " AND g.properties ->> '$.startYear' = '$searchRequest->admissionYear'";
            }
            if(!empty($searchRequest->academicTermId)) {
                $whereQuery .= " AND CAST(eerb.properties ->> '$.academicTermId' AS CHAR) = '$searchRequest->academicTermId'";
            }
            if(!empty($searchRequest->examRegistrationId)) {
                $whereQuery .= " AND eerb.ec_exam_registration_id = '$searchRequest->examRegistrationId'";
            }
            if(!empty($searchRequest->academicPaperSubjectIds)) {
                $academicPaperSubjectIdsString= "'" . implode("', '", $searchRequest->academicPaperSubjectIds) . "'" ;
                $whereQuery .= "AND eers.cm_academic_paper_subjects_id  IN ($academicPaperSubjectIdsString)";
            }
            if(!empty($searchRequest->feeStatus)) {
                $whereQuery .= " AND esar.properties ->> '$.feeStatus' = '$searchRequest->feeStatus'";
            }
            $orderBy = $searchRequest->orderBy? "ORDER BY sa.studentID ASC" : "";
            $query = "SELECT DISTINCT
                        sa.studentID as studentId,
                        sa.studentName,
                        sa.regNo,
                        sa.studentEmail,
                        sa.studentPhone,
                        eer.name as specialExamRegistrationName,
                        g.name as groupName,
                        act.name as academicTerm,
                        eers.am_assessment_id as assessmentId,
                        eerb.ec_exam_registration_id as examRegistrationId,
                        eers.cm_academic_paper_subjects_id as academicPaperSubjectId,
                        s.code as subjectCode,
                        s.name as subjectName,
                        eer.name as examRegistrationName,
                        esar.properties ->> '$.registeredDate' as studentExamRegistrationDate
                    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 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'
                    INNER JOIN studentaccount sa ON 
                        sa.studentID = esar.student_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  academic_term act ON 
                        act.id = CAST(eerb.properties ->> '$.academicTermId' AS CHAR)
                    WHERE 1=1 ";
            $studentDetails = $this->executeQueryForList($query.$whereQuery.$orderBy);
        }
        catch (\Exception $e)
        {
            throw new ExamControllerException($e->getCode(),$e->getMessage());
        }
        return $studentDetails;
    }
    /**
     * save Batch Group For Exam Revaluation
     * @param $batchGroupForRevaluation
     * @return $id
     */
    public function saveBatchGroupForExamRevaluation ($batchGroupForRevaluation)
    {
        $batchGroupForRevaluation = $this->realEscapeObject($batchGroupForRevaluation);
        $batchGroupForRevaluation->startDate = date("Y-m-d", strtotime($batchGroupForRevaluation->startDate));
        $batchGroupForRevaluation->endDate = date("Y-m-d", strtotime($batchGroupForRevaluation->endDate));
        $batchGroupForRevaluation->verificationDate = date("Y-m-d", strtotime($batchGroupForRevaluation->verificationDate));
        try{
            if(!empty($batchGroupForRevaluation->id))
            {
                $batchGroupForRevaluation->id = $this->updateBatchGroupForRevaluation($batchGroupForRevaluation);
            }
            else
            {
                $batchGroupForRevaluation->id = $this->insertBatchGroupForRevaluation($batchGroupForRevaluation);
            }
        }catch(\Exception $e) {
            throw new ExamControllerException ($e->getCode(),$e->getMessage());
        }
        return $batchGroupForRevaluation->id ;
        
    }
      /**
     * Insert Batch Group For Revaluation
     * @param $batchGroupForRevaluation
     * @return  $id
     */
    private function insertBatchGroupForRevaluation($batchGroupForRevaluation){
        $groupIdsObj = new \stdClass();
        $groupIdsObj->groupIds = $batchGroupForRevaluation->groupIds;
        $groupIds = json_encode($groupIdsObj);
        $query = "INSERT INTO exam_revaluation_batch_groups
                  (ec_exam_revaluation_id,group_ids,startDate,endDate,subjectLimit,verificationDate)
                  VALUES
                  ('$batchGroupForRevaluation->examRevaluationId','$groupIds','$batchGroupForRevaluation->startDate','$batchGroupForRevaluation->endDate','$batchGroupForRevaluation->subjectLimit','$batchGroupForRevaluation->verificationDate')";
        try {
            $batchGroupForRevaluation->id = $this->executeQuery($query,true)->id;
           return $batchGroupForRevaluation->id;
        } catch (\Exception $e) {
             throw new ExamControllerException($e->getCode(),$e->getMessage());
        }
    }
    /**
     * update Batch Group For Revaluation
     * @param  $batchGroupForRevaluation
     * @return String $batchGroupForRevaluation->id
     */
    private function updateBatchGroupForRevaluation($batchGroupForRevaluation){
        $groupIdsObj = new \stdClass();
        $groupIdsObj->groupIds = $batchGroupForRevaluation->groupIds;
        $groupIds = json_encode($groupIdsObj);
        $query = "UPDATE
                    exam_revaluation_batch_groups
                SET
                    ec_exam_revaluation_id = '$batchGroupForRevaluation->examRevaluationId',
                    group_ids = '$groupIds',
                    startDate = '$batchGroupForRevaluation->startDate',
                    endDate = '$batchGroupForRevaluation->endDate',
                    subjectLimit = '$batchGroupForRevaluation->subjectLimit',
                    verificationDate = '$batchGroupForRevaluation->verificationDate'
                WHERE
                    id = '$batchGroupForRevaluation->id'";
        try 
        {
            $this->executeQuery($query);
            return $batchGroupForRevaluation->id;
        } catch (\Exception $e) {
            throw new ExamControllerException($e->getCode(),$e->getMessage());
        }
    }
    /**
     * Delete Batch Group For Revaluation
     * @param String $id
     * @return NULL
     */
    public function deleteBatchGroupForRevaluation($id)
    {
        $id = $this->realEscapeString($id);
        if(empty($id))
            throw new ExamControllerException(ExamControllerException::EMPTY_PARAMETERS,"Revaluation Batch Group is invalid!");
        $query = "DELETE FROM
                    exam_revaluation_batch_groups
                WHERE
                    id = '$id'";
        try {
            $this->executeQuery($query);
        } catch (\Exception $e) {
            throw new ExamControllerException(ExamControllerException::ERROR_DELETING_REVALUATION_BATCH_GROUP,"Error deleting Revaluation Batch! Please try again");
        }
    }
     /**
     * Search stduent Exam Registration
     * @param SearchStudentExamRegistrationRequest $request
     * @return examRevaluations
     */
    public function searchStudentExamRevaluation(SearchStudentExamRegistrationRequest $request){
        $request = $this->realEscapeObject($request);
        $whereQuery = "";
        $limitQuery = "";
        $joinQuery = "";
        $selectColumns = "";
        if(!empty($request->studentId)){
            $whereQuery .= " AND spa.student_id = '$request->studentId";
        }
        else {
            throw new ExamControllerException(ExamControllerException::EMPTY_PARAMETERS,"Cannot fetch Exam Registration of unknown student! Please try again.");
        }
        if(!empty($request->examRegistrationId)){
            $whereQuery .= " AND eer.id  = '$request->examRegistrationId'";
            // $joinQuery .= "INNER JOIN fm_fee_head ffhc ON
            //     ffhc.id = eerb.fees_properties->>'$.commonFeeHeadId'
            // INNER JOIN fm_fee_subhead ffsc ON
            //     ffsc.fm_fee_head_id = ffhc.id
            // INNER JOIN fm_fee_head ffhr ON
            //     ffhr.id = eerb.fees_properties->>'$.revaluationFeeHeadId'
            // INNER JOIN fm_fee_subhead ffsr ON
            //     ffsr.fm_fee_head_id = ffhr.id";
            // $selectColumns = ",ffsc.id as commonFeeId,
            //     ffsc.name as commonFeeName,
            //     ffsr.id as revaluationFeeId,
            //     ffsr.name as revaluationFeeName";
        }
        if($request->startIndex !== "" && $request->endIndex !== ""){
            $limitQuery .= " LIMIT $request->startIndex,$request->endIndex";
        }
        $query = "SELECT
            eer.id,
            eer.identifying_context,
            eer.name,
            eer.type,
            eer.properties,
            eer.trashed,
            eer.created_by,
            eer.created_date,
            eer.updated_by,
            eer.updated_date,
            eerp.id AS parentExamRegistrationId,
            eerp.name AS parentExamRegistrationName,
            eerp.type AS parentExamRegistrationType,
            eerb.groups_id,
            g.name AS groupName,
            eerb.fees_properties AS batchFeeProperties,
            caps.id AS paperSubjectId,
            eers.fees_properties AS subjectFeeProperties,
            eers.revaluation_properties,
            eers.am_assessment_id,
            s.code as subjectName,
            aps.properties ->> '$.syllabusName' AS syllabusName,
            s.name as subjectDesc,
            esar.id AS studentAssessmentRegistrationId,
            esar.properties->>'$.registrationStatus' AS registrationStatus,
            erbg.id AS revaluationGroupId,
            erbg.startDate,
            erbg.endDate,
            erbg.subjectLimit,
            erbg.verificationDate
            $selectColumns
        FROM
            ec_exam_registration eer
        INNER JOIN  ec_exam_registration eerp ON
            eerp.id = eer.properties->>'$.parentExamRegstrationId'
        INNER JOIN ec_exam_registration_batch eerb ON
            eerb.ec_exam_registration_id = eer.id
        INNER JOIN `groups` g ON
            g.id = eerb.groups_id
            AND g.`type` = 'BATCH'
        INNER JOIN group_members gm ON
            gm.groups_id = g.id
        INNER JOIN student_program_account spa ON 
            spa.id  = gm.members->>'$.studentId'
        INNER JOIN exam_revaluation_batch_groups erbg ON 
            erbg.ec_exam_revaluation_id = eer.id AND JSON_SEARCH(erbg.group_ids ->> '$.groupIds','one',g.id)
        INNER JOIN ec_exam_registration_subject eers ON
            eers.ec_exam_registration_batch_id = eerb.id 
        INNER JOIN cm_academic_paper_subjects caps ON
            caps.id = eers.cm_academic_paper_subjects_id
        INNER JOIN v4_ams_subject s ON
            s.id = caps.ams_subject_id
        LEFT JOIN ec_student_assessment_registration esar ON
            esar.student_id = spa.student_id AND esar.am_assessment_id = eers.am_assessment_id AND esar.ec_exam_registration_type = eer.type
            $joinQuery
        WHERE
            eer.trashed IS NULL
            AND eer.type = 'REVALUATION' ";
        try {
            $examRevaluations = $this->executeQueryForList($query.$whereQuery.$limitQuery, $this->mapper[ExamRegistrationServiceMapper::SEARCH_STUDENT_EXAM_REVALUATION]);
        } catch (\Exception $e) {
            throw new ExamControllerException(ExamControllerException::ERROR_FETCHING_EXAM_REGISTRATION,"Cannot fetch Exam Registration of the student.");
        }
        if (empty($examRevaluations)) {
            throw new ExamControllerException(ExamControllerException::EMPTY_PARAMETERS,"Exam Registration not declared, Please try again.");
        }
        foreach ($examRevaluations as $examRevaluation) {            
            $examRevaluation->isBlocked = false;
            $examRevaluation->blockReasons = "";
            $examRevaluation->notification = "Nill";
            $examRevaluation->paymentStatus = "Not Paid";
            $examRevaluation->status = "Not Registered";
            $examRevaluation->revaluationSubjectLimit = (int)$examRevaluation->revaluationSubjectLimit;
            if(strtotime($examRevaluation->revaluationStartDate) > strtotime(date("Y-m-d"))) {
                // $examRevaluation->isBlocked = true;
                $examRevaluation->blockReasons = "Registration not started";
            }
            elseif (strtotime($examRevaluation->revaluationEndDate) < strtotime(date("Y-m-d"))) {
                $examRevaluation->isBlocked = true;
                $examRevaluation->blockReasons = "Registration closed";
            }
            if(!empty($examRevaluation->registrationStatus)){
                $examRevaluation->status = $examRevaluation->registrationStatus;
            }
            foreach($examRevaluation->subjects as $subject){
                $subject->isSelected = false;
                usort($subject->revaluationProperties, function($a, $b) {return strcmp($a->order, $b->order);});
                foreach ($subject->revaluationProperties as $revaluaionTypes) { 
                    $revaluationObj = new \stdClass;
                    $revaluationObj->id = $revaluaionTypes->name;
                    $revaluationObj->name = $revaluaionTypes->name;
                    $subject->revaluationTypes[] = $revaluationObj;
                }
            }
            
        }
        return $examRevaluations;
    }
     /**
     * Search stduent Exam Registration
     * @return examRevaluationTpes
     */
    public function getRevaluationTypes(){
        try {
            $query = "SELECT
                        id,
                        name,
                        properties
                    FROM
                    ec_revaluation_fee_types ";
            $revaluationTypes = $this->executeQueryForList($query);
        }
        catch (\Exception $e)
        {
            throw new ExamControllerException($e->getCode(),$e->getMessage());
        }
        return $revaluationTypes;
        
    }
     /**
     * Search examRegistration
     * @param  $request
     * @return examRegistration
     */
    public function searchDetailedExamRegistrationDetails( $request)
    {
        $request = $this->realEscapeObject($request);
        if(is_array($request->assessmentId)){
            foreach($request->assessmentId as $key => $assessmentId){
                $request->assessmentId[$key] = stripslashes($assessmentId);
            }
        }
        $whereQuery = "";
        $limitQuery = "";
        $joinQueary = "";
        $orderQuery = "";
        if(!empty($request->examRegistrationId)){
            $examRegistrationIdString = is_array($request->examRegistrationId) ? "'" . implode("','",$request->examRegistrationId) . "'" : "'".$request->examRegistrationId."'";
            $whereQuery .= " AND eer.id IN ( $examRegistrationIdString ) ";
        }
        if(!empty($request->name)){
            $whereQuery .= " AND eer.name LIKE '%$request->name%'";
        }
        if(!empty($request->courseTypeId)){
            $whereQuery .= " AND p.course_type_id = '$request->courseTypeId'";
        }
        if(!empty($request->groupId)) {
            $groupIdString = is_array($request->groupId) ? "'" . implode("','",$request->groupId) . "'" : "'".$request->groupId."'";
            $whereQuery .= " AND g.id IN ( $groupIdString )";
        }
        if(!empty($request->academicPaperSubjectId)) {
            $academicPaperSubjectIdString = is_array($request->academicPaperSubjectId) ? "'" . implode("','",$request->academicPaperSubjectId) . "'" : "'".$request->academicPaperSubjectId."'";
            $whereQuery .= " AND aps.id IN ( $academicPaperSubjectIdString )";
        }
        if(!empty($request->assessmentId)) {
            $assessmentIdString = is_array($request->assessmentId) ? "'" . implode("','",$request->assessmentId) . "'" : "'".$request->assessmentId."'";
            $whereQuery .= " AND eers.am_assessment_id IN ( $assessmentIdString )";
        }
        if(!empty($request->examRegistrationBatchId)) {
            $examRegistrationBatchIdString = is_array($request->examRegistrationBatchId) ? "'" . implode("','",$request->examRegistrationBatchId) . "'" : "'".$request->examRegistrationBatchId."'";
            $whereQuery .= " AND eerb.id IN ( $examRegistrationBatchIdString )";
        }
        if(!empty($request->academicTermId)){
            $whereQuery .= " AND CAST(eerb.properties ->> '$.academicTermId'AS CHAR) = '$request->academicTermId'";
        }
        if(!empty($request->batchStartYear)){
            $whereQuery .= " AND g.properties ->> '$.startYear' = '$request->batchStartYear'";
        }
        if(!empty($request->type)) {
            foreach($request->type as $type){
                $whereQuery .= " AND eer.type LIKE '%$type%'";
            }
        }
        if(!empty($request->limitCondition)) {
            $limitQuery = " LIMIT 1";
        }
        $joinQuery = " LEFT JOIN stream str ON
             JSON_SEARCH( p.stream_id, 'one', str.id) IS NOT NULL";
        $columns = " group_concat(str.name SEPARATOR ' and ') as streamName, ";
        if(!empty($request->removeStream)) {
            $joinQuery = "";
            $columns = "";
        }
        $groupByQuerry = " GROUP BY eer.id,p.id,g.id,aps.id";
        $orderQuery = " ORDER BY CAST(cap.properties ->> '$.order' AS UNSIGNED) ASC";
        $query = "SELECT
            DISTINCT(eer.id),
            eer.identifying_context,        
            eer.name,
            eer.type,
            eer.properties,
            eer.properties ->> '$.examMonth' AS examMonth,
            MONTHNAME(STR_TO_DATE(eer.properties ->> '$.examMonth', '%m')) AS examMonthName,
            eer.properties ->> '$.examYear' AS examYear,
            eer.fees_properties,
            eer.trashed,
            eer.created_by,
            eer.created_date,
            eer.updated_by,
            eer.updated_date,
            eerb.groups_id,
            eerb.properties AS examBatchProperties,
            g.name AS groupName,
            g.type AS groupType,
            act.id as academicTermId,
            act.name as academicTermName,
            act.properties ->>'$.year' AS academicTermYear,
            act.properties ->>'$.orderNo' AS academicOrderNo,
            dept.deptID,
            deg.name as degreeName,
            deg.description as degreeDescription,
            $columns
            p.name as programName,
            p.properties ->>'$.subsidiaryCourseName' AS subsidiaryCourseName,
            dept.deptName,
            dept.departmentDesc as departmentDesc,
            dept.departmentSpecialization AS departmentSpecialization,
            ct.courseTypeID,
            ct.course_Type as courseTypeName,
            g.properties ->> '$.startYear' AS batchStartYear,
            g.identifying_context AS groupIdentifyingContext,
            g.properties AS groupProperties,
            eers.cm_academic_paper_subjects_id as academicPaperSubjectId,
            eers.am_assessment_id as assessmentId,
            s.code as subjectCode,
            aps.properties ->> '$.syllabusName' AS syllabusName,
            s.name as subjectName,
            aps.properties ->>'$.isInternal' AS isInternal,
            aps.properties ->>'$.isExternal' AS isExternal,
            aps.properties ->>'$.externalMaxMark' AS externalMaxMark,
            aps.properties ->>'$.internalMaxMark' AS internalMaxMark,
            aps.properties ->>'$.credit' AS subjectCredit,
            aps.properties ->>'$.subjectTypeId' AS subjectTypeId,
            aps.properties ->>'$.courseCode' AS courseCode,
            IFNULL(aps.properties ->>'$.externalMaxMark', 0) + IFNULL(aps.properties ->>'$.internalMaxMark', 0) AS totalSubjectMark,
            cap.properties ->> '$.order' as subjectPriority,
            aps.properties ->> '$.excludeSubjectFromTotal' AS excludeSubjectFromTotal
        FROM
            ec_exam_registration eer
        INNER JOIN ec_exam_registration_batch eerb
            ON eerb.ec_exam_registration_id =  eer.id
        INNER JOIN ec_exam_registration_subject eers ON
            eers.ec_exam_registration_batch_id = eerb.id
        INNER JOIN  cm_academic_paper_subjects 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 `groups` g
            ON g.id =  eerb.groups_id
        INNER JOIN program p ON
            p.id = CAST(g.properties ->> '$.programId' AS CHAR)
        INNER JOIN degree deg ON
            deg.id = p.degree_id
        INNER JOIN `academic_term` act ON
            act.id = CAST(eerb.properties ->> '$.academicTermId'AS CHAR)
        INNER JOIN `department` dept ON
            dept.deptID = CAST(g.properties ->> '$.departmentId' AS CHAR)
        INNER JOIN `course_type` ct ON
            ct.courseTypeID = p.course_type_id
        $joinQuery
        WHERE
            eer.trashed IS NULL
            ";
        try {
            $examRegistrations = $this->executeQueryForList($query.$whereQuery.$groupByQuerry.$orderQuery.$limitQuery, $this->mapper[ExamRegistrationServiceMapper::SEARCH_DETAILED_EXAM_REGISTRATION_DETAILS]);
        } catch (\Exception $e) {
            throw new ExamControllerException(ExamControllerException::ERROR_FETCHING_EXAM_REGISTRATION,"Cannot fetch Exam Registration details! Please try again.");
        }
        return $examRegistrations;
    }
     /**
     * get Month Name By month Num
     * @param  $monthNo
     * @return monthData
     */
    public function getMonthName($monthNo){
        if($monthNo){
            $monthData = new \stdClass;
            $query = "SELECT
                MONTHNAME(STR_TO_DATE($monthNo , '%m')) AS examMonthName";
            try {
                $monthData = $this->executeQueryForObject($query);
            }
            catch (\Exception $e) {
                throw new ExamControllerException(ExamControllerException::INVALID_PARAMETERS,"Invailed Parameter.");
            }
            return $monthData->examMonthName;
        }
        else{
            return null;
        }        
    }
      /**
     * getAcademicTermIdByExamRegistrationId
     * @param  $request
     * @return examRegistration
     */
    public function getAcademicTermIdByExamRegistrationId( $request)
    {
        $request = $this->realEscapeObject($request);
        $whereQuery = "";
        if(!empty($request->examRegistrationId)){
            $whereQuery .= " AND eer.id='$request->examRegistrationId";
        }
        
        $query = "SELECT
            DISTINCT
            act.id as academicTermId
        FROM
            ec_exam_registration eer
        RIGHT JOIN ec_exam_registration_batch eerb
            ON eerb.ec_exam_registration_id =  eer.id
        LEFT JOIN `academic_term` act ON
            act.id = CAST(eerb.properties ->> '$.academicTermId'AS CHAR)
        WHERE
            eer.trashed IS NULL";
        try {
            $academicTerm = $this->executeQueryForObject($query.$whereQuery);
        } catch (\Exception $e) {
            throw new ExamControllerException(ExamControllerException::ERROR_FETCHING_EXAM_REGISTRATION,"Cannot fetch Exam Registration details! Please try again.");
        }
        return $academicTerm->academicTermId;
    }
     /**
     * print All absent Students For Special Exam
     * @param $searchRequest 
     * @return $response 
     */
    public function getAllStudentDetailsForSpecialExam($searchRequest) {
        $searchRequest = $this->realEscapeObject($searchRequest);
        try{
            $response = new \stdClass;
            $request = new \stdClass;
            $additionalDetails = new \stdClass;
            $absentStudentDetails = [];
            $absentStudentDetailsArray = [];
            $request->groupId = $searchRequest->groupId;
            $request->academicTermId = $searchRequest->academicTermId;
            $request->departmentId = $searchRequest->departmentId;
            $request->specialExamRegstrationId = $searchRequest->specialExamRegstrationId;
            $studentsDetails = $this->getAllStudentDetailsListForSpecialExamRegistration($request);
            if(empty($studentsDetails)){
                throw new ExamControllerException(ExamControllerException::NO_STUDENTS_IN_THIS_EXAM_REGISTRATION,"No Students In This Special Exam Registration");
            }
            else{
                foreach($studentsDetails as $student){
                    $additionalDetails->specialExamRegistrationName = $student->specialExamRegistrationName;
                    $absentStudentDetails[$student->studentId]->studentId = $student->studentId;
                    $absentStudentDetails[$student->studentId]->groupName = $student->groupName;
                    $absentStudentDetails[$student->studentId]->academicTerm = $student->academicTerm;
                    $absentStudentDetails[$student->studentId]->studentName = $student->studentName;
                    $absentStudentDetails[$student->studentId]->regNo = $student->regNo;
                    $absentStudentDetails[$student->studentId]->regularExamRegistrationName = $student->regularExamRegistrationName;
                    $absentStudentDetails[$student->studentId]->availableSubjects[$student->academicPaperSubjectId]->academicPaperSubjectId = $student->academicPaperSubjectId;
                    $absentStudentDetails[$student->studentId]->availableSubjects[$student->academicPaperSubjectId]->subjectCode = $student->subjectCode;
                    $absentStudentDetails[$student->studentId]->availableSubjects[$student->academicPaperSubjectId]->subjectName = $student->subjectName;
                    $absentStudentDetails[$student->studentId]->availableSubjects[$student->academicPaperSubjectId]->specialExamAssessmentId = $student->specialExamAssessmentId;
                    $absentStudentDetails[$student->studentId]->availableSubjects[$student->academicPaperSubjectId]->specialExamRegistrationId = $student->specialExamRegistrationId;
                    $absentStudentDetails[$student->studentId]->availableSubjects[$student->academicPaperSubjectId]->academicTerm = $student->academicTerm;
                }
                foreach($absentStudentDetails as $absentStudent){
                    $absentStudent->subjects=[];
                    foreach($absentStudent->availableSubjects as $subject){
                        $absentStudent->subjects[] = $subject;
                    }
                    $absentStudentDetailsArray[]=$absentStudent;
                }
            }
            if(empty($absentStudentDetailsArray)){
                throw new ExamControllerException(ExamControllerException::NO_STUDENTS_IN_THIS_EXAM_REGISTRATION,"No Students In This Special Exam Registration");
            }
            else
            {
                $templateName = "PrintSpecialExamReport";
                $responseHtml = TwigRenderer::renderTemplateFileToHtml(realpath(DOCUMENT_ROOT."../examcontroller-api/src/com/linways/web/templates/SupplementaryExamReports/$templateName.twig"), [ 'students'=>$absentStudentDetailsArray ,'additionalDetails'=>$additionalDetails]);
                $prtContent = NULL;
                $prtContent .= '<html><head>';
                $prtContent .= "<style>
                    h6 {font-size: 26px;} .text-center { text-align: center;} .align-middle {vertical-align: middle;};
                    </style>";
                $prtContent .= '</head><title>Special Exam Students Report</title><body>';
                $prtContent .= $responseHtml;
                $prtContent .= '</body></html>';
                $totalWidth = 210;
                $totalHeight = 297;
                $options = array(
                    'page-width'     => $totalHeight."mm",
                    'page-height'    => $totalWidth."mm",
                    'dpi'            => 96,
                    'margin-top' => "9mm",
                    'margin-left' => "1mm",
                    'margin-right' => "1mm",
                    'margin-bottom' => "9mm",
                    // '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());
        }
        return $response;
    }
     /**
     * get All Students For Special Exam,
     * @param $searchRequest 
     * @return $response 
     */
    public function getAllStudentDetailsListForSpecialExamRegistration($searchRequest) {
        $searchRequest = $this->realEscapeObject($searchRequest);
        try
        {
            $whereQuery = "";
            if(!empty($searchRequest->groupId)){
                $whereQuery .= " AND eerbse.groups_id = '$searchRequest->groupId";
            }
            if(!empty($searchRequest->academicTermId)){
                $whereQuery .= " AND CAST(eerbse.properties ->> '$.academicTermId'AS CHAR)  = '$searchRequest->academicTermId";
            }
            if(!empty($searchRequest->departmentId)){
                $whereQuery .= " AND g.properties ->> '$.departmentId' = '$searchRequest->departmentId";
            }
            $query = "SELECT DISTINCT
                            sa.studentID as studentId,
                            sa.studentName,
                            sa.regNo,
                            eerse.name as specialExamRegistrationName,
                            g.name as groupName,
                            act.name as academicTerm,
                            eersse.am_assessment_id as specialExamAssessmentId,
                            eerbse.ec_exam_registration_id as specialExamRegistrationId,
                            eersse.cm_academic_paper_subjects_id as academicPaperSubjectId,
                            s.code as subjectCode,
                            s.name as subjectName,
                            eer.name as regularExamRegistrationName
                        FROM
                            ec_exam_registration_batch eerbse
                        INNER JOIN ec_exam_registration_subject eersse ON
                            eersse.ec_exam_registration_batch_id = eerbse.id
                        INNER JOIN ec_exam_registration eerse ON
                            eerse.id = eerbse.ec_exam_registration_id
                        INNER JOIN ec_student_assessment_registration esarse ON
                            esarse.am_assessment_id = eersse.am_assessment_id
                        INNER JOIN studentaccount sa ON 
                            sa.studentID = esarse.student_id
                        INNER JOIN  cm_academic_paper_subjects aps ON 
                            eersse.cm_academic_paper_subjects_id = aps.id
                        INNER JOIN v4_ams_subject s ON 
                            aps.ams_subject_id = s.id
                        INNER JOIN  `groups` g ON 
                            g.id = eerbse.groups_id
                        INNER JOIN  ec_exam_registration_retest_mapping eerrm ON 
                            eerrm.retest_exam_registration_id = eerbse.ec_exam_registration_id
                        INNER JOIN  ec_exam_registration_batch eerbreg ON 
                            eerbreg.ec_exam_registration_id = eerrm.ec_exam_registration_id AND eerbreg.groups_id = eerbse.groups_id
                        INNER JOIN ec_exam_registration_subject eersreg ON
                            eersreg.ec_exam_registration_batch_id = eerbreg.id AND eersreg.cm_academic_paper_subjects_id = eersse.cm_academic_paper_subjects_id
                        INNER JOIN  academic_term act ON 
                            act.id = CAST(eerbreg.properties ->> '$.academicTermId'AS CHAR)
                        INNER JOIN ec_student_assessment_registration esarreg ON
                            esarreg.am_assessment_id = eersreg.am_assessment_id AND esarreg.student_id = esarse.student_id
                        INNER JOIN  ec_exam_registration eer ON 
                            eer.id = eerbreg.ec_exam_registration_id
                        WHERE
                            eerbse.ec_exam_registration_id = '$searchRequest->specialExamRegstrationId'  ";
            $specialExamStudentsDetails = $this->executeQueryForList($query.$whereQuery);
        }
        catch (\Exception $e)
        {
            throw new ExamControllerException($e->getCode(),$e->getMessage());
        }
        return $specialExamStudentsDetails;
    }
     /**
     * get degree by exam registration Id
     * @param  $request
     * @return academicTerms
     */
    public function getAllDegreesByExamRegistration($request)
    {
        $whereQuery = "";
        $orderBy = " ORDER BY deg.name ASC";
        $searchRequest = $this->realEscapeObject($request);
        if(!empty($searchRequest->examRegistrationId)){
            $examRegistrationIdString = is_array($searchRequest->examRegistrationId) ? "'" . implode("','",$searchRequest->examRegistrationId) . "'" : "'".$searchRequest->examRegistrationId."'";
            $whereQuery .= " AND  eerb.ec_exam_registration_id IN ( $examRegistrationIdString )";
        }
        
        $query = "SELECT DISTINCT
                    deg.id,
                    deg.name
                FROM
                    ec_exam_registration_batch eerb
                INNER JOIN `groups` g ON
                    g.id =  eerb.groups_id
                INNER JOIN program p ON
                    p.id =  CAST(g.properties ->> '$.programId'AS CHAR) 
                INNER JOIN degree deg ON
                    deg.id = p.degree_id
                WHERE
                  1=1 ";
        try {
            $degree = $this->executeQueryForList($query.$whereQuery.$orderBy);
        } catch (\Exception $e) {
            throw new ExamControllerException(ExamControllerException::ERROR_FETCHING_EXAM_REGISTRATION_ACADEMIC_TERM,"Cannot fetch Semester Details ! Please try again.");
        }
        return $degree;
    }
     /**
     * get All Assessment Dates By ExamRegistration
     * @param  $request
     * @return assessmentDates
     */
    public function getAllAssessmentDatesByExamRegistration($request)
    {
        $whereQuery = "";
        $orderBy = " ORDER BY aa.properties_value ->>'$.assessmentDate' DESC";
        $searchRequest = $this->realEscapeObject($request);
        if(!empty($searchRequest->examRegistrationId)){
            $examRegistrationIdString = is_array($searchRequest->examRegistrationId) ? "'" . implode("','",$searchRequest->examRegistrationId) . "'" : "'".$searchRequest->examRegistrationId."'";
            $whereQuery .= " AND  eerb.ec_exam_registration_id IN ( $examRegistrationIdString )";
        }
        
        $query = "SELECT DISTINCT
                    aa.properties_value ->>'$.assessmentDate' AS id,
                    aa.properties_value ->>'$.assessmentDate' AS name
                FROM
                    ec_exam_registration_batch eerb
                INNER JOIN ec_exam_registration_subject eers ON
                    eers.ec_exam_registration_batch_id = eerb.id
                INNER JOIN am_assessment aa ON
                    aa.id = eers.am_assessment_id
                WHERE
                  1=1 AND aa.properties_value ->>'$.assessmentDate' IS NOT NULL";
        try {
            $assessmentDates = $this->executeQueryForList($query.$whereQuery.$orderBy);
        } catch (\Exception $e) {
            throw new ExamControllerException(ExamControllerException::ERROR_FETCHING_EXAM_DATES,"Cannot fetch Exam Dates ! Please try again.");
        }
        foreach($assessmentDates as $assessmentDate){
            $assessmentDate->name = date("d-M-Y", strtotime($assessmentDate->name));
        }
        return $assessmentDates;
    }
    /**
     * get Current Exam Revaluation Details
     * @param $searchRequest 
     * @return $response 
     */
    public function getCurrentExamRevaluationDetails($request){
        $searchRequest = $this->realEscapeObject($request);
        $whereQuery = "";
        
        if(!empty($searchRequest->id)){
            $examRegistrationIdString = is_array($searchRequest->id) ? "'" . implode("','",$searchRequest->id) . "'" : "'".$searchRequest->id."'";
            $whereQuery .= " AND  eer.id IN ( $examRegistrationIdString )";
        }
        $query = "SELECT
                    eer.id,
                    eer.identifying_context,        
                    eer.name,
                    eer.type,
                    eer.properties,
                    eer.fees_properties,
                    eer.trashed,
                    eer.created_by,
                    eer.created_date,
                    eer.updated_by,
                    eer.updated_date,
                    eerp.id as parentExamRegstrationId,
                    eerp.name as parentExamRegistrationName,
                    eerp.type as parentExamRegistrationType,
                    erpr.id as parentRevaluationEntryId,
                    erpr.parent_registration_id as parentRevaluationId
                FROM
                    ec_exam_registration eer
                LEFT JOIN ec_exam_registration eerp ON 
                    eerp.id = CAST(eer.properties->>'$.parentExamRegistrationId' AS CHAR)
                LEFT JOIN ec_revaluation_parent_registration erpr ON 
                    erpr.ec_exam_registration_id = eer.id
                WHERE
                    1 = 1 AND eer.type = 'REVALUATION' ";
        try {
            $currentExamRevaluation = $this->executeQueryForList($query.$whereQuery,$this->mapper[ExamRegistrationServiceMapper::SEARCH_CURRENT_EXAM_REVALUATION]);
        } catch (\Exception $e) {
            throw new ExamControllerException(ExamControllerException::ERROR_FETCHING_EXAM_DATES,"Cannot fetch Exam Dates ! Please try again.");
        }
        if(!empty($currentExamRevaluation)){
            array_walk($currentExamRevaluation,function($revalObj,$key){
                if($revalObj->parentRevaluationId){
                    $revalObj->parentRevaluationId = explode(',',$revalObj->parentRevaluationId);
                    $parentRevaluationName = ExamRevaluationParentMappingService::getInstance()->getRevaluationParentName($revalObj->parentRevaluationId);
                    $revalObj->parentRevaluationName = implode(', ', array_column($parentRevaluationName, 'name'));
                }
            });
            return reset($currentExamRevaluation);
        }
        return $currentExamRevaluation;
    }
        /**
     * Save ExamRegistration Fee Template
     * @param $feeTemplate
     * @return $id
     */
    public function saveExamRegistrationFeeTemplate ($feeTemplate)
    {
        $feeTemplate = $this->realEscapeObject($feeTemplate);
        try{
            if($feeTemplate->existingTemplateId){
                $this->updateExamRegistrationFeeTemplate($feeTemplate);
                $templateId = $feeTemplate->existingTemplateId;
            }
            else{
                $templateId = $this->createExamRegistrationFeeTemplate($feeTemplate);
            }
            foreach($feeTemplate->subTemplate as $feeSubTemplate){
                $examRegistrationFeeTemplate = new ExamRegistrationFeeTemplate();
                $examRegistrationFeeTemplate->feeProperties = json_encode($feeSubTemplate->fees);
                $examRegistrationFeeTemplate->fineProperties = json_encode($feeSubTemplate->fines);
                $examRegistrationFeeTemplate->operationProperties = $feeSubTemplate->feeOperation ? json_encode($feeSubTemplate->feeOperation) : "{}";
                if($feeSubTemplate->templateId){
                    $examRegistrationFeeTemplate->id = $feeSubTemplate->templateId;
                    $query = "UPDATE ec_examregistration_fee_templates SET fee_properties = '$examRegistrationFeeTemplate->feeProperties',  fine_properties = '$examRegistrationFeeTemplate->fineProperties', operation_properties = '$examRegistrationFeeTemplate->operationProperties' WHERE id = '$examRegistrationFeeTemplate->id'";
                }
                else{
                    $staffId = $GLOBALS['userId'];
                    $id = SecurityUtils::getRandomString();
                    $query = "INSERT INTO ec_examregistration_fee_templates
                    (ec_examregistration_fee_templates_mapping_id,fee_properties,fine_properties,operation_properties,created_by)
                    VALUES
                    ('$templateId','$examRegistrationFeeTemplate->feeProperties','$examRegistrationFeeTemplate->fineProperties','$examRegistrationFeeTemplate->operationProperties', '$staffId')";
                }
                $this->executeQuery($query);
                AMSLogger::log_info($this->logger,Events::EC_SAVE_EXAM_REGISTRATION_FEE_TEMPLATE,[
                    "staff" => new Staff(["id" => $staffId]),
                        "request" => $examRegistrationFeeTemplate,
                        "status" => StatusConstants::SUCCESS
                ]);
            }
        }catch(\Exception $e) {
            AMSLogger::log_error($this->logger,Events::EC_SAVE_EXAM_REGISTRATION, [
                "staff" => new Staff(["id" => $staffId]),
                "request" => $examRegistrationFeeTemplate,
                "errorCode" => $e->getCode(),
                "errorMessage" => $e->getMessage(),
                "status" => StatusConstants::FAILED
            ]);
            throw new ExamControllerException ($e->getCode(),$e->getMessage());
        }
        return $templateId;
    }
    /**
     * create ExamRegistration Fee Template
     * @param $feeTemplate
     * @return $id
     */
    public function createExamRegistrationFeeTemplate ($feeTemplate)
    {
        $feeTemplate = $this->realEscapeObject($feeTemplate);
        try{
            $id = SecurityUtils::getRandomString();
            $query = "INSERT INTO ec_examregistration_fee_templates_mapping
                (id,template_name,ec_exam_registration_id)
                VALUES
                ('$id','$feeTemplate->templateName','$feeTemplate->examRegistrationId')";
        
        $this->executeQuery($query);
        }catch(\Exception $e) {
            throw new ExamControllerException ($e->getCode(),$e->getMessage());
        }
        return $id ;
    }
    /**
     * create ExamRegistration Fee Template
     * @param $feeTemplate
     * @return $id
     */
    public function updateExamRegistrationFeeTemplate ($feeTemplate){
        $feeTemplate = $this->realEscapeObject($feeTemplate);
        try{
            $query = "UPDATE
                            ec_examregistration_fee_templates_mapping
                        SET
                            template_name = '$feeTemplate->templateName'
                        WHERE
                            id = '$feeTemplate->existingTemplateId' AND ec_exam_registration_id = '$feeTemplate->examRegistrationId";
        $this->executeQuery($query);
        }catch(\Exception $e) {
            throw new ExamControllerException ($e->getCode(),$e->getMessage());
        }
        return $feeTemplate->existingTemplateId;
    }
    /**
     * get exam registration types by type
     * @param $examRegistrationId 
     * @return $examRegistartionType 
     */
    public function getExamRegistrationType ($examRegistrationId)
    {
        $examRegistrationId = $this->realEscapeString($examRegistrationId);
        try{
            $query = "SELECT 
                        eert.ec_examregistration_type AS typeName,
                        eer.properties ->> '$.examRegistrationType' AS examRegistrationType,
                        eer.type AS type
                    FROM 
                        ec_exam_registration eer 
                    INNER JOIN ec_exam_registration_types eert ON 
                        eer.`type` = eert.parent_type 
                    WHERE 
                        eer.id ='$examRegistrationId'";
        
        $examRegistartionType = $this->executeQueryForList($query);
        foreach($examRegistartionType as $key => $examRegType){
            if($examRegType->type ==  ExamRegistrationTypeConstants::SUPPLEMENTARY){
                if($examRegType->examRegistrationType ==   ExamRegistrationTypeConstants::SUPPLY_IMPROVEMENT){
                    break;
                }
                else if($examRegType->examRegistrationType ==  ExamRegistrationTypeConstants::SUPPLEMENTARY && $examRegType->typeName !=  ExamRegistrationTypeConstants::SUPPLEMENTARY){
                    unset($examRegistartionType[$key]);
                }
                else if($examRegType->examRegistrationType ==  ExamRegistrationTypeConstants::IMPROVEMENT && $examRegType->typeName !=  ExamRegistrationTypeConstants::IMPROVEMENT){
                    unset($examRegistartionType[$key]);
                }
            }
        }
        }catch(\Exception $e) {
            throw new ExamControllerException ($e->getCode(),$e->getMessage());
        }
        return $examRegistartionType ;
    }
    /**
     * search Exam Registration For Filter
     * @param SearchExamRegistrationRequest $request
     * @return examRegistration
     */
    public function searchExamRegistrationForFilter( SearchExamRegistrationRequest $request)
    {
        $request = $this->realEscapeObject($request);
        $where = [];
        $where [] = " g.type = '".ProfessionalGroupTypeConstant::BATCH."'";
        $where [] = " eer.trashed IS NULL";
        !empty($request->id)?$where [] = " eer.id = '$request->id":null;
        !empty($request->courseTypeId)?$where [] = " p.course_type_id = '$request->courseTypeId":null;
        !empty($request->startYear)?$where [] = " g.properties ->> '$.startYear' = $request->startYear ":null;
        !empty($request->departmentId)?$where [] = " g.properties ->> '$.departmentId' = $request->departmentId ":null;
        !empty($request->currentTermId)?$where [] = " CAST(eerb.properties ->> '$.academicTermId'AS CHAR) = $request->currentTermId ":null;
        !empty($request->type)?$where [] = " eer.type LIKE '%$request->type%'":null;
        $groupByQuerry = " GROUP BY eer.id,p.id,g.id,aps.id";
        $query = "SELECT
                DISTINCT(eer.id),eer.identifying_context,eer.name,eer.type,eer.properties,g.properties->'$.startYear' as startYear,
                dept.deptID as deptId,dept.deptName as deptName,act.id as academicTermId,act.name as academicTermName,
                p.id as programId,p.name as programName,eerb.groups_id as groupId,g.name as groupName,deg.id as degreeId,deg.name as degreeName,
                ct.courseTypeID as courseTypeId,ct.course_Type as courseTypeName,eers.cm_academic_paper_subjects_id as academicPaperSubjectId,
                s.name as subjectName,s.code as subjectCode,
                JSON_OBJECT('id', eer.properties ->> '$.examYear','name', eer.properties ->> '$.examYear') AS examYear,
                JSON_OBJECT('id',eer.type,'name', eer.type) AS examType
            FROM
                ec_exam_registration eer
            INNER JOIN ec_exam_registration_batch eerb ON eerb.ec_exam_registration_id =  eer.id
            INNER JOIN `groups` g ON g.id = eerb.groups_id
            INNER JOIN program p ON p.id = CAST(g.properties ->> '$.programId' AS CHAR)
            INNER JOIN degree deg ON deg.id = p.degree_id
            LEFT JOIN stream str ON JSON_SEARCH( p.stream_id, 'one', str.id) IS NOT NULL
            INNER JOIN `academic_term` act ON act.id = CAST(eerb.properties ->> '$.academicTermId'AS CHAR)
            INNER JOIN `department` dept ON dept.deptID = CAST(g.properties ->> '$.departmentId' AS CHAR)
            INNER JOIN `course_type` ct ON ct.courseTypeID = p.course_type_id 
            LEFT JOIN ec_exam_registration_subject eers ON eers.ec_exam_registration_batch_id = eerb.id
            LEFT JOIN  cm_academic_paper_subjects aps ON  eers.cm_academic_paper_subjects_id = aps.id
            LEFT JOIN  v4_ams_subject s ON  aps.ams_subject_id = s.id".(count($where) ? ' WHERE '.implode(' AND ',$where) : "").";";
        $recordCount = "SELECT count(distinct eer.id) as `examRegCount` 
                        FROM
                            ec_exam_registration eer
                        INNER JOIN ec_exam_registration_batch eerb ON eerb.ec_exam_registration_id =  eer.id
                        INNER JOIN `groups` g ON g.id = eerb.groups_id
                        INNER JOIN program p ON p.id = CAST(g.properties ->> '$.programId' AS CHAR)
                        INNER JOIN degree deg ON deg.id = p.degree_id
                        LEFT JOIN stream str ON JSON_SEARCH( p.stream_id, 'one', str.id) IS NOT NULL
                        INNER JOIN `academic_term` act ON act.id = CAST(eerb.properties ->> '$.academicTermId'AS CHAR)
                        INNER JOIN `department` dept ON dept.deptID = CAST(g.properties ->> '$.departmentId' AS CHAR)
                        INNER JOIN `course_type` ct ON ct.courseTypeID = p.course_type_id 
                        LEFT JOIN ec_exam_registration_subject eers ON eers.ec_exam_registration_batch_id = eerb.id
                        LEFT JOIN  cm_academic_paper_subjects aps ON  eers.cm_academic_paper_subjects_id = aps.id
                        LEFT JOIN  v4_ams_subject s ON  aps.ams_subject_id = s.id".(count($where) ? ' WHERE '.implode(' AND ',$where) : "").$groupByQuerry.";";
        try {
            $result = new stdClass();
            $result->records = $this->executeQueryForList($query, $this->mapper[ExamRegistrationServiceMapper::SEARCH_EXAM_REGISTRATION_FILTER]);
            $result->totalRecords = $this->executeQueryForObject($recordCount)->examRegCount;
        } catch (\Exception $e) {
            throw new ExamControllerException(ExamControllerException::ERROR_FETCHING_EXAM_REGISTRATION,"Cannot fetch Exam Registration details! Please try again.");
        }
        return $result;
    }
     /**
     * search All Exam Registration
     * @param SearchExamRegistrationRequest $request
     * @return examRegistration
     */
    public function searchAllExamRegistration( SearchExamRegistrationRequest $request)
    {
        $request = $this->realEscapeObject($request);
        $where = [];
        $where [] = " g.type = '".ProfessionalGroupTypeConstant::BATCH."'";
        $where [] = " eer.trashed IS NULL";
        $joinQuery = [];
        $limitQuery = "";
        $sortBy = " ORDER BY eer.created_date DESC";
        if(!empty($request->courseTypeId)){
            $where [] = " p.course_type_id = '$request->courseTypeId"; 
            $joinQuery [] = " INNER JOIN program p ON p.id =  CAST(g.properties ->> '$.programId'AS CHAR) ";
        }
        if(!empty($request->isSpecialExam == '1')){
            $where [] = " eer.properties->>'$.isSpecialExam' = true "; 
        }
        else{
            $where [] = " (eer.properties->>'$.isSpecialExam' != true OR eer.properties->>'$.isSpecialExam' IS NULL ) "; 
        }
        if(!empty($request->departmentId)){
            $where [] = " g.properties ->> '$.departmentId' = $request->departmentId ";
        }
        if(($request->isControllerSideRevaluationOnly)){
            $where [] = " (eer.properties ->> '$.isStaffSideMarkEntry' != '1' OR eer.properties ->> '$.isStaffSideMarkEntry' IS NULL ) ";
        }
        if(($request->isStaffSideRevaluationOnly)){
            $where [] = " eer.properties ->> '$.isStaffSideMarkEntry' = '1'  ";
        }
        if(!empty($request->currentTermId)){
            $where [] = "  CAST(eerb.properties ->> '$.academicTermId'AS CHAR) = $request->currentTermId";
        }
        !empty($request->type)?$where [] = " eer.type LIKE '%$request->type%'": $where [] = " eer.type IN ('REGULAR','SUPPLEMENTARY')";
        !empty($request->startYear)?$where [] = " g.properties ->> '$.startYear' = $request->startYear ":null;
        if($request->startIndex !== "" && $request->endIndex !== ""){
            $limitQuery .= " LIMIT $request->startIndex,$request->endIndex";
        }
        $query = "SELECT
                DISTINCT(eer.id) as id,
                eer.identifying_context,
                eer.name,
                eer.type,
                IF((eer.properties->>'$.isHonorCourse') = '1', '1', '0') AS isHonorCourse,
                IF((eer.properties->>'$.isMinorCourse') = '1', '1', '0') AS isMinorCourse,
                eer.properties
            FROM
                ec_exam_registration eer
            INNER JOIN ec_exam_registration_batch eerb ON eerb.ec_exam_registration_id =  eer.id
            INNER JOIN `groups` g ON g.id = eerb.groups_id
            ".(count($joinQuery) ? implode('',$joinQuery) : "")."
            ".(count($where) ? ' WHERE '.implode(' AND ',$where) : "");
        $recordCount = "SELECT count(distinct eer.id) as `examRegCount` 
                    FROM
                        ec_exam_registration eer
                    INNER JOIN ec_exam_registration_batch eerb ON eerb.ec_exam_registration_id =  eer.id
                    INNER JOIN `groups` g ON g.id = eerb.groups_id
                    ".(count($joinQuery) ? implode('',$joinQuery) : "")."
                    ".(count($where) ? ' WHERE '.implode(' AND ',$where) : "").";";
        try {
            $result = new stdClass();
            $result->records = $this->executeQueryForList($query.$sortBy.$limitQuery);
            $result->totalRecords = $this->executeQueryForObject($recordCount)->examRegCount;
        } catch (\Exception $e) {
            throw new ExamControllerException(ExamControllerException::ERROR_FETCHING_EXAM_REGISTRATION,"Cannot fetch Exam Registration details! Please try again.");
        }
        return $result;
    }
    /**
     * Get Exam Registered Student Details
     * @params examRegistrationId
     * @return studentList
     */
    public function getStudentExamAssignedDetails($request){
        $request = $this->realEscapeObject($request);
        $limitQuerry = "Limit 1";
        $whereQuery = '';
        $query = " SELECT 
            esar.student_id as studentId,
            esar.am_assessment_id as assessmentId
        FROM
            ec_exam_registration_batch eerb
        INNER JOIN ec_exam_registration_subject eers ON
            eers.ec_exam_registration_batch_id = eerb.id 
        INNER JOIN ec_student_assessment_registration esar ON
            esar.am_assessment_id = eers.am_assessment_id AND esar.properties ->> '$.registrationStatus' IN ('APPLIED','REGISTERED')
        INNER JOIN  ec_exam_registration eer ON
            eer.id = eerb.ec_exam_registration_id AND eer.type = esar.ec_exam_registration_type
        WHERE eerb .ec_exam_registration_id = '$request->examRegistrationId'";
        try {
            $studentList = $this->executeQueryForList($query. $whereQuery.$limitQuerry);
        } catch (\Exception $e) {
            throw new ExamControllerException($e->getCode(),$e->getMessage());
        }
        return $studentList;
    }
    /**
     * Get Exam Registered Student Details
     * @params examRegistrationId
     * @return studentList
     */
    public function getRevaluationStudentAssignedDetails($request){
        $request = $this->realEscapeObject($request);
        $limitQuerry = "Limit 1";
        $whereQuery = '';
        if($request->groupIds){
            $whereQuery .= " AND eerb.groups_id IN ('$request->groupIds')";
        }
        if(!empty($request->groupId)) {
            $groupIdString = is_array($request->groupId) ? "'" . implode("','",$request->groupId) . "'" : "'".$request->groupId."'";
            $whereQuery .= " AND eerb.groups_id IN ( $groupIdString )";
        }
        $query = " SELECT
            caps.id AS academicPaperSubjectId,
            esar.student_id AS studentId
        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 
        INNER JOIN cm_academic_paper_subjects caps ON
            caps.id = eers2.cm_academic_paper_subjects_id AND eers2.am_assessment_id = esar.am_assessment_id 
        WHERE eer.type = 'REVALUATION' AND eer.trashed IS NULL AND eer.id = '$request->examRegistrationId'";
        try {
            $studentList = $this->executeQueryForList($query. $whereQuery.$limitQuerry);
        } catch (\Exception $e) {
            throw new ExamControllerException($e->getCode(),$e->getMessage());
        }
        return $studentList;
    }
    /**
     * Search examRegistration
     * @param SearchExamRegistrationRequest $request
     * @return examRegistration
     */
    public function getAllExamRevaluation(SearchExamRegistrationRequest $request)
    {
        $request = $this->realEscapeObject($request);
        $whereQuery = "";
        $limitQuery = "";
        $sortBy = " ORDER BY eer.created_date DESC";
        if(!empty($request->id)){
            $whereQuery .= " AND eer.id='$request->id";
        }
        if(!empty($request->name)){
            $whereQuery .= " AND eer.name LIKE '%$request->name%'";
        }
        if(!empty($request->parentExamRegistrationId)) {
            $whereQuery .= " AND CAST(eer.properties->>'$.parentExamRegistrationId' AS CHAR) = '$request->parentExamRegistrationId'";
        }
        if($request->trashed === StatusConstants::ACTIVE) {
            $whereQuery .= " AND eer.trashed IS NULL ";
        }
        if($request->trashed === StatusConstants::TRASHED) {
            $whereQuery .= " AND eer.trashed IS NOT NULL ";
        }
        if($request->startIndex !== "" && $request->endIndex !== ""){
            $limitQuery .= " LIMIT $request->startIndex,$request->endIndex";
        }
        if(!empty($request->courseTypeId)){
            $whereQuery .= " AND p.course_type_id = '$request->courseTypeId"; 
            $joinQuery [] = " INNER JOIN program p ON p.id =  CAST(g.properties ->> '$.programId'AS CHAR) ";
        }
        if(!empty($request->departmentId)){
            $whereQuery .= " AND g.properties ->> '$.departmentId' = $request->departmentId ";
        }
        if(!empty($request->currentTermId)){
            $whereQuery .= " AND CAST(eerb.properties ->> '$.academicTermId'AS CHAR) = $request->currentTermId";
        }
        if(!empty($request->batchStartYear)){
            $whereQuery .= " AND g.properties ->> '$.startYear' = '$request->batchStartYear'";
        }
        if(!empty($request->parentExamType)){
            $whereQuery .= " AND eerp.type = '$request->parentExamType'";
        }
        if(!empty($request->groupId)){
            $groupIdString = is_array($request->groupId) ? "'" . implode("','",$request->groupId) . "'" : "'".$request->groupId."'";
            $whereQuery .= " AND eerb.groups_id IN ( $groupIdString )";
        }
        $query = "SELECT
            eer.id,
            eer.identifying_context,        
            eer.name,
            eer.type,
            eer.properties,
            eer.trashed,
            eer.created_by,
            eer.created_date,
            eer.updated_by,
            eer.updated_date,
            eerp.id as parentExamRegistrationId,
            eerp.name as parentExamRegistrationName,
            eerp.type as parentExamRegistrationType,
            eerb.id as examRegistrationBatchId,
            eerb.properties as examRegistrationBatchProperties,
            g.id as groupId,
            g.name as groupName,
            act.id as academicTermId,
            act.name as academicTermName
        FROM
            ec_exam_registration eer
        INNER JOIN ec_exam_registration eerp ON 
            eerp.id = CAST(eer.properties->>'$.parentExamRegistrationId' AS CHAR)
        INNER JOIN ec_exam_registration_batch eerb ON eerb.ec_exam_registration_id =  eer.id
            INNER JOIN `groups` g ON g.id = eerb.groups_id
        INNER JOIN `academic_term` act ON act.id = CAST(eerb.properties ->> '$.academicTermId'AS CHAR)
        ".(count($joinQuery) ? implode('',$joinQuery) : "")."
        WHERE
            1 = 1 AND eer.type = 'REVALUATION'";
        try {
            $examRevaluations = $this->executeQueryForList($query.$whereQuery.$sortBy.$limitQuery, $this->mapper[ExamRegistrationServiceMapper::SEARCH_EXAM_REVALUATION]);
        } catch (\Exception $e) {
            throw new ExamControllerException(ExamControllerException::ERROR_FETCHING_EXAM_REGISTRATION,"Cannot fetch Exam Registration details! Please try again.");
        }
        return $examRevaluations;
    }
    /**
     * Get Supply Registrations By Sem and batch
     * @param $request, $batchId
     * @return Object|null
     * @throws ExamControllerException
     */
    public function getExamRegistrationOfABatchByRequest (  $request ) {
        $request = $this->realEscapeObject($request);
        $whereQuery = "";
        $limitCond = "";
        if(!empty($request->type)){
            $whereQuery .= " AND  eer.`type` ='$request->type";
        }
        if(!empty($request->groupId)){
            $whereQuery .= " AND  eerb.groups_id ='$request->groupId";
        }
        if(!empty($request->termId)){
            $whereQuery .= " AND  eerb.properties->>'$.academicTermId' ='$request->termId";
        }
        if(!empty($request->limit)){
            $limitCond .= " LIMIT $request->limit";
        }
        
        try {
            $sql = "SELECT 
                        eer.id AS examRegistrationId, eer.name  
                    FROM 
                        ec_exam_registration eer 
                    INNER JOIN ec_exam_registration_batch eerb ON
                        eerb.ec_exam_registration_id = eer.id
                    WHERE 
                        eer.trashed IS NULL $whereQuery
                    ORDER BY 
                        IF(CAST(eer.properties->>'$.examYear' AS SIGNED) = 0, 99999, CAST(eer.properties->>'$.examYear' AS SIGNED)) ,IF(CAST(eer.properties->>'$.examMonth' AS SIGNED) = 0, 99999, CAST(eer.properties->>'$.examMonth' AS SIGNED))";
            
            $supplyRegs = $this->executeQueryForList($sql.$limitCond);
        }
        catch(\Exception $e) {
            throw new ExamControllerException($e->getCode(),$e->getMessage());
        }
        return $supplyRegs;
    }
    /**
     * Search examRegistration
     * @param $request
     * @return studentList
     */
    public function getExamRegistrationStudentListByExamRegId($request)
    {
        $request = $this->realEscapeObject($request);
        $query = "SELECT 
                    DISTINCT sa.studentID as studentId, sa.studentName as name, sa.studentEmail as emailId, sa.studentPhone as mobileNo, act.properties ->> '$.orderNo' as termOrderNo, act.id as academicTermId, act.name as academicTermName, g.id as groupId, g.name as groupName, eer.properties ->> '$.examDate' as examDate, eer.properties ->> '$.registrationStartDate' as registrationStartDate
                FROM 
                    ec_exam_registration eer 
                INNER JOIN ec_exam_registration_batch eerb ON 
                    eer.id = eerb.ec_exam_registration_id 
                INNER JOIN academic_term act ON 
                    act.id = CAST(eerb.properties ->> '$.academicTermId'AS CHAR)
                INNER JOIN `groups` g ON
                    g.id = eerb.groups_id
                    AND g.`type` = 'BATCH'
                INNER JOIN group_members gm ON
                    gm.groups_id = g.id
                INNER JOIN student_program_account spa ON 
                    spa.id  = gm.members->>'$.studentId'
                INNER JOIN student_program_batch_log spbl ON
                    spbl.batch_group_id = g.id AND
                    spbl.term_id = CAST(eerb.properties ->> '$.academicTermId'AS CHAR) AND
                    spbl.program_student_id = spa.id AND spbl.properties->>'$.academicStatus' IN ('ACTIVE','COMPLETED')
                INNER JOIN studentaccount sa ON
                    sa.studentID = spa.student_id
                WHERE 
                    eer.id = '$request->examRegistrationId'";
        try {
            $examRegistration = $this->executeQueryForList($query);
        } catch (\Exception $e) {
            throw new ExamControllerException(ExamControllerException::ERROR_FETCHING_EXAM_REGISTRATION,"Cannot fetch Exam Registration details! Please try again.");
        }
        return $examRegistration;
    }
     /**
     * Search examRevaluation By staff 
     * @param  $request
     * @return examRegistration
     */
    public function getAllAssignedExamRevaluationByCurrentStaff( $request)
    {
        $request = $this->realEscapeObject($request);
        $currentUerId = $GLOBALS['userId'];
        $whereQuery = "";
        $limitQuery = "";
        if(!empty($request->type)) 
        {
            $whereQuery .= " AND eer.type LIKE 'REVALUATION'";
        }
        if(($request->isStaffSideRevaluationOnly)){
           $whereQuery .= " AND eer.properties ->> '$.isStaffSideMarkEntry' = '1'  ";
        }
        $query = "SELECT
                    DISTINCT(eer.id),
                    eer.name,
                    eer.properties as properties
                FROM ec_exam_registration_batch eerb
                INNER JOIN ec_exam_registration eer ON
                    eer.id = eerb.ec_exam_registration_id AND 
                    eer.trashed IS NULL
                INNER JOIN ec_exam_registration eerp ON
                    eerp.id = eer.properties->>'$.parentExamRegistrationId' AND 
                    eerp.trashed IS NULL
                INNER JOIN ec_exam_registration_batch eerbParent ON
                    eerbParent.ec_exam_registration_id = eerp.id  AND 
                    eerbParent.groups_id = eerb.groups_id
                INNER JOIN ec_exam_registration_subject eers ON     
                    eers.ec_exam_registration_batch_id = eerbParent.id 
                WHERE
                    JSON_CONTAINS(JSON_EXTRACT(eers.valuation_details ,'$.reValuationStaffs'),JSON_OBJECT('addiitonalExamniners', '$currentUerId')) $whereQuery OR
                    JSON_CONTAINS(JSON_EXTRACT(eers.valuation_details ,'$.reValuationStaffs'),JSON_OBJECT('chiefExaminer', '$currentUerId')) $whereQuery";
        try {
            $examRegistration = $this->executeQueryForList($query.$limitQuery, $this->mapper[ExamRegistrationServiceMapper::SEARCH_EXAM_REGISTRATION]);
            $searchFeeReq = new \stdClass;
            $searchFeeReq->examType = 'REVALUATION';
            $searchFeeReq->isMarkEntryNeeded = true;
            $revaluationFeeTypes = ExamFeeTypeService::getInstance()->getFeeTypes($searchFeeReq);
            $revaluationFeeTypeIds = array_column($revaluationFeeTypes, 'id');
            foreach($examRegistration as $key => $examReg){
                if(!empty($examReg->properties->revaluationFeeTypeIds)){
                    if(empty(array_intersect($examReg->properties->revaluationFeeTypeIds, $revaluationFeeTypeIds))){
                        unset($examRegistration[$key]);
                    }
                }
            }
            $examRegistration = array_values($examRegistration);
        } catch (\Exception $e) {
            throw new ExamControllerException(ExamControllerException::ERROR_FETCHING_EXAM_REGISTRATION,"Cannot fetch Exam Registration details! Please try again.");
        }
        return $examRegistration;
    }
     /**
     * Search examRegistration Valuation Count
     * @param  $request
     * @return examRegistration
     */
    public function getAllAssignedExamRevaluationValuationCountByCurrentStaff($request){
        $subjecs = [];
        $valuationCountObjectArray = [];
        $valuationCountArray = [];
        $revaluationTypeIdArray = [];
        $response = new \stdClass;
        $revaluationTypeArray = [];
        $currentUerId = $GLOBALS['userId'];
        $subjects =  ExamRegistrationSubjectService::getInstance()->getAllCurretStaffSubjectsValuationDetailsByExamRevaluationId($request);
        foreach($subjects as $subject){
            $subject->valuationDetails = json_decode($subject->valuationDetails);
            foreach($subject->valuationDetails->reValuationStaffs as $valuationStaff){
                foreach($valuationStaff->addiitonalExamniners as $staff){
                    if($currentUerId == $staff){
                        $valuationCountArray[$valuationStaff->count] = $valuationStaff->count;
                        $revaluationTypeIdArray[$valuationStaff->revaluationType] = $valuationStaff->revaluationType;
                    }
                }
            }
        }
        $feeRequest = new \stdClass;
        $feeRequest->examType = 'REVALUATION';
        $allCommonFees = CommonExamService::getInstance()->getAllFeesTypes($feeRequest);
        foreach($allCommonFees as $fee){
            if(in_array($fee->id,$revaluationTypeIdArray)){
                $revaluationType = new \stdClass;
                $revaluationType->id= $fee->id;
                $revaluationType->text= $fee->name;
                $revaluationTypeArray[]= $revaluationType;
            }
        }
        sort($valuationCountArray);
        foreach($valuationCountArray as $valuationCount){
            $valuaionCount = new \stdClass;
            $valuaionCount->id= $valuationCount;
            $valuaionCount->text= $valuationCount;
            $valuationCountObjectArray[]= $valuaionCount;
        }
        $response->valuaionCount= $valuationCountObjectArray;
        $response->revaluationType= $revaluationTypeArray;
        return $response;
    }
    /**
     * Publish exam time table in student side 
     * @param $request
     * @return NULL
     */
    public function publishExamTimeTableInStudentSide($request)
    {
        $request = $this->realEscapeObject($request);
        $updatedBy = $GLOBALS['userId'];
        $staffId = $GLOBALS['userId'];
        if(empty($request->examRegistrationId))
            throw new ExamControllerException(ExamControllerException::EMPTY_PARAMETERS_EXAM_REGISTRATION,"Exam Registration is invalid! Please enter a valid Exam Registration");
        $publish = $request->publish ? 1 : 0;
        $query = "UPDATE
                    ec_exam_registration
                SET
                    properties = JSON_SET(properties, '$.publishExamTimeTableStudent', $publish),
                    updated_by = '$updatedBy'
                WHERE
                    id = '$request->examRegistrationId'";
        try {
            $this->executeQuery($query);
            AMSLogger::log_info($this->logger,Events::EC_UPDATE_EXAM_REGISTRATION,[
                "staff" => new Staff(["id" => $staffId]),
                "request" => $request,
                "status" => StatusConstants::SUCCESS
            ]);
        } catch (\Exception $e) {
            AMSLogger::log_error($this->logger,Events::EC_UPDATE_EXAM_REGISTRATION, [
                "staff" => new Staff(["id" => $staffId]),
                "request" => $request,
                "errorCode" => $e->getCode(),
                "errorMessage" => $e->getMessage(),
                "status" => StatusConstants::FAILED
            ]);
            throw new ExamControllerException(ExamControllerException::ERROR_UPDATE_PUBLISH_STATUS_EXAM_REGISTRATION,"Error deleting Exam Registration! Please try again");
        }
    }
     /**
     * Get all exam registation count
     * Features : Dashboard
     */
    public function getExamRegistrationCount($request)
    {
        $request = $this->realEscapeObject($request);
        try {
            $sql = "SELECT 
                        eer.id,
                        eer.name, 
                        eer.properties, 
                        eeft.id as feeTemplateId,
                        eeft.fine_properties as fineProperties
                    FROM
                        ec_exam_registration eer 
                    INNER JOIN ec_examregistration_fee_templates_mapping eeftm  ON 
                        eeftm.ec_exam_registration_id = eer.id 
                    INNER JOIN ec_examregistration_fee_templates eeft ON 
                        eeft.ec_examregistration_fee_templates_mapping_id = eeftm.id 
                    WHERE `type` = '$request->examType' AND trashed IS NULL ";
            $examRegistrationList =  $this->executeQueryForList($sql, $this->mapper[ExamRegistrationServiceMapper::LIST_EXAM_REGISTRATION]);
            $examRegistrationDetails =  new \stdClass;
            $examRegistrationDetails->totalCount =  count($examRegistrationList);
            $today = date("Y-m-d H:i");
            foreach ($examRegistrationList as $examRegistrationDetail ){
                $endDate = "";
                foreach ($examRegistrationDetail->templates as $feeTemplate ){
                    foreach ($feeTemplate->fineProperties as $fineProperties ){
                        foreach ($fineProperties->fineType as $fineType ){
                            if($endDate && (strtotime($fineType->endDate) > strtotime($endDate))){
                                $endDate = $fineType->endDate;
                            }
                            else if(empty( $endDate )){
                                $endDate = $fineType->endDate;
                            }
                        }
                    }
                }
                $examRegistrationDetail->endDate =  $endDate;
            }
            foreach ($examRegistrationList as $examRegistrationDetail ){
                if((strtotime($today) <= strtotime($examRegistrationDetail->endDate)) && (strtotime($today) >= strtotime($examRegistrationDetail->properties->registrationStartDate))){
                    $examRegistrationDetails->onGoingCount++;
                }
            }
        } catch (\Exception $e) {
            throw new ExamControllerException($e->getCode(),$e->getMessage());
        }
        return $examRegistrationDetails;
    }
     /**
     * list of supplementary exam's month and year semsterwise by student 
     * @param  $request
     * @return examRegistration
     */
    public function supplyExamMonthAndYearSemesterWiseByStudent( $request){
        $request = $this->realEscapeObject($request);
        $whereQuery = "";
        $orderBy = " ORDER BY eer.properties ->> '$.examYear' ASC ,eer.properties ->> '$.examMonth' + 0 ASC ";
        if(empty($request->groupId)) {
            throw new ExamControllerException(ExamControllerException::EMPTY_PARAMETERS_EXAM_REGISTRATION, " Empty Parameter ! Batch group  id is empty ");
        }
        if(!empty($request->groupId)) {
            $groupIdString = is_array($request->groupId) ? "'" . implode("','",$request->groupId) . "'" : "'".$request->groupId."'";
            $whereQuery .= " AND eerb.groups_id IN ( $groupIdString )";
        }
        if(!empty($request->studentId)) {
            $studentIdString = is_array($request->studentId) ? "'" . implode("','",$request->studentId) . "'" : "'".$request->studentId."'";
            $whereQuery .= " AND esar.student_id IN ( $studentIdString )";
        }
        $query = "SELECT
            DISTINCT(eer.id),
            eer.name AS name,
            eer.type AS type,
            eer.properties ->> '$.examMonth' AS examMonth,
            MONTHNAME(STR_TO_DATE(eer.properties ->> '$.examMonth', '%m')) AS examMonthName,
            eer.properties ->> '$.examYear' AS examYear,
            act.id as academicTermId,
            act.name as academicTermName
        FROM
            ec_exam_registration eer
        INNER JOIN ec_exam_registration_batch eerb
            ON eerb.ec_exam_registration_id =  eer.id
        INNER JOIN ec_exam_registration_subject eers ON
            eers.ec_exam_registration_batch_id = eerb.id
        INNER JOIN ec_student_assessment_registration esar ON
            esar.am_assessment_id = eers.am_assessment_id AND  
            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  academic_term act ON 
            act.id = CAST(eerb.properties ->> '$.academicTermId'AS CHAR) AND 
            act.type = 'SEMESTER'
        WHERE
            eer.trashed IS NULL AND eer.type = 'SUPPLEMENTARY' ";
        try {
            $examDetails = $this->executeQueryForList($query.$whereQuery.$orderBy);
            $semesterWiseDetails = [];
            foreach ($examDetails as $examDetail) {
                $semesterWiseDetails[$examDetail->academicTermId]->exams[] = $examDetail;
            }
        } catch (\Exception $e) {
            throw new ExamControllerException(ExamControllerException::ERROR_FETCHING_EXAM_REGISTRATION,"Cannot fetch Exam Registration details! Please try again.");
        }
        return $semesterWiseDetails;
    }
     /**
     * list of  exam's month and year By batch
     */
    public function getExamMonthYearsOfBatchGroup($request){
        $request = $this->realEscapeObject($request);
        $whereQuery = "";
        $orderBy = " ORDER BY eer.properties ->> '$.examYear' ASC ,eer.properties ->> '$.examMonth' + 0 ASC ";
        if(empty($request->groupId)) {
            throw new ExamControllerException(ExamControllerException::EMPTY_PARAMETERS_EXAM_REGISTRATION, " Empty Parameter ! Batch group  id is empty ");
        }
        if(!empty($request->groupId)) {
            $groupIdString = is_array($request->groupId) ? "'" . implode("','",$request->groupId) . "'" : "'".$request->groupId."'";
            $whereQuery .= " AND eerb.groups_id IN ( $groupIdString )";
        }
        $query = "SELECT DISTINCT
                DATE_FORMAT(CONCAT(eer.properties ->> '$.examYear','-',eer.properties ->> '$.examMonth','-','1'),'%Y-%m') AS id,
                DATE_FORMAT(CONCAT(eer.properties ->> '$.examYear','-',eer.properties ->> '$.examMonth','-','1'),'%Y %b') name
            FROM
                ec_exam_registration eer
            INNER JOIN ec_exam_registration_batch eerb
                ON eerb.ec_exam_registration_id =  eer.id
            WHERE
                eer.trashed IS NULL AND eer.properties ->> '$.examYear' IS NOT NULL";
        try {
            $examMonthYears  = $this->executeQueryForList($query.$whereQuery.$orderBy);
        } catch (\Exception $e) {
            throw new ProfessionalException($e->getCode(), $e->getMessage());
        }
        return $examMonthYears;
    }
     /**
     * Save Institutional Average Generate Status
     * @param $request
     * @return NULL
     */
    public function saveInstitutionalAverageGenerateStatus($request)
    {
        $request = $this->realEscapeObject($request);
        $updatedBy = $request->$staffId?? $GLOBALS['userId'];
        $staffId = $request->$staffId?? $GLOBALS['userId'];
        if(empty($request->examRegistrationId))
            throw new ExamControllerException(ExamControllerException::EMPTY_PARAMETERS_EXAM_REGISTRATION,"Exam Registration is invalid! Please enter a valid Exam Registration");
        $query = "UPDATE
                    ec_exam_registration
                SET
                    properties = JSON_SET(properties, '$.institutionalAverageGenerateStatus','$request->status'),
                    updated_by = '$updatedBy'
                WHERE
                    id = '$request->examRegistrationId'";
        try {
            $this->executeQuery($query);
            $this->logger->info(Events::EC_UPDATE_EXAM_REGISTRATION,[
                "staff" => new Staff(["id" => $staffId]),
                "request" => $request,
                "status" => StatusConstants::SUCCESS
            ]);
        } catch (\Exception $e) {
            $this->logger->error(Events::EC_UPDATE_EXAM_REGISTRATION, [
                "staff" => new Staff(["id" => $staffId]),
                "request" => $request,
                "errorCode" => $e->getCode(),
                "errorMessage" => $e->getMessage(),
                "status" => StatusConstants::FAILED
            ]);
            throw new ExamControllerException(ExamControllerException::ERROR_UPDATE_PUBLISH_STATUS_EXAM_REGISTRATION,"Error deleting Exam Registration! Please try again");
        }
    }
     /**
     * get exam registration by current staff
     * @param $request
     * @return examRegistrations
     */
    public function getStaffHandlingDepartmentExamRegistrations($request){
        $request = $this->realEscapeObject($request);
        $whereQuery = "";
        $limitQuery = "";
        $examRegistrations = [];
        $joinQuery = "";
        $sortBy = " ORDER BY eer.created_date DESC";
        if(!empty($request->examType)) {
            $whereQuery .= " AND eer.type LIKE '%$request->examType%'";
        }
        if(!empty($request->groupId)) {
            $whereQuery .= " AND g.id = '$request->groupId'";
        }
        if($request->haveNotDepartmentRestriction) {
            if(!empty($request->departmentId)){
                $whereQuery .= " AND pdr.department_id  = '$request->departmentId'";
            }
        }
        else if(!$request->isTutorWiseResultSheet){
            $joinQuery .= "INNER JOIN staffaccounts s ON s.deptID = pdr.department_id ";
            $whereQuery .= " AND s.staffID = '$request->staffId'";
        }
        $query = "SELECT
            eer.id AS id,
            eer.name AS name,
            eer.type AS type,
            eer.properties as examRegistrationProperties,
            eerb.properties as examRegistrationBatchProperties,
            g.id as groupId,
            g.name as groupName
        FROM `groups` g 
        INNER JOIN program_department_relation pdr ON pdr.program_id = g.program_id 
        INNER JOIN ec_exam_registration_batch eerb ON eerb.groups_id = g.id
        INNER JOIN ec_exam_registration eer ON eer.id = eerb.ec_exam_registration_id
        $joinQuery
        WHERE eer.trashed IS NULL";
        try {
            $examRegistrationsArray = $this->executeQueryForList($query.$whereQuery.$sortBy.$limitQuery);
        } catch (\Exception $e) {
            throw new ExamControllerException(ExamControllerException::ERROR_FETCHING_EXAM_REGISTRATION,"Cannot fetch Exam Registration details! Please try again.");
        }
        foreach($examRegistrationsArray as $examRegistration){
            $examRegistrations[$examRegistration->id]->id = $examRegistration->id;
            $examRegistrations[$examRegistration->id]->name = $examRegistration->name;
            $examRegistrations[$examRegistration->id]->type = $examRegistration->type;
            $examRegistrations[$examRegistration->id]->properties = json_decode($examRegistration->examRegistrationProperties);
            $examRegistrations[$examRegistration->id]->batchProperties = json_decode($examRegistration->examRegistrationBatchProperties);
            $examRegistrations[$examRegistration->id]->groups[$examRegistration->groupId]->id = $examRegistration->groupId;
            $examRegistrations[$examRegistration->id]->groups[$examRegistration->groupId]->name = $examRegistration->groupName;
            $examRegistrations[$examRegistration->id]->groups[$examRegistration->groupId]->text = $examRegistration->groupName;
        }
        $examRegistrations = array_values($examRegistrations);
        array_walk($examRegistrations, function($examRegistration){
            $examRegistration->groups = array_values($examRegistration->groups);
        });
        return $examRegistrations;
    }
    /**
     * Save exam revaluation Memo details
     * @param $request
     */
    public function saveRevaluationMemoDetails($request){
        $columnVal = "";
        $query = "UPDATE
                    ec_exam_registration
                SET
                    properties = JSON_SET(properties, '$.memoNumber','$request->memoNumber'),
                    properties = JSON_SET(properties, '$.memoDate','$request->memoDate')
                WHERE
                    id IN ('$request->examRegistrationId') AND `type` = 'REVALUATION'";
        try {
            $this->executeQuery($query);
        } catch (\Exception $e) {
            throw new ExamControllerException($e->getCode(), $e->getMessage());
        }
    }
     /**
     * Search Batch Start Year by exam Registration
     * @param  $request
     * @return startYears
     */
     public function getExamRegistrationStartYears( $request){
        $request = $this->realEscapeObject($request);
        $orderBy = " ORDER BY CAST(g.properties ->> '$.startYear' AS UNSIGNED) ASC";
         $whereQuery = "";
         if(!empty($request->courseTypeId)){
             $whereQuery .= " AND p.course_type_id = '$request->courseTypeId";
         }
         if(!empty($request->examRegistrationId)){
             $whereQuery .= " AND eer.id = '$request->examRegistrationId'";
         }
         $query = "SELECT
             DISTINCT g.properties ->> '$.startYear' as id,
             g.properties ->> '$.startYear' as name
         FROM
             ec_exam_registration eer
         INNER JOIN ec_exam_registration_batch eerb
             ON eerb.ec_exam_registration_id =  eer.id
         INNER JOIN `groups` g
             ON g.id =  eerb.groups_id
         INNER JOIN program p 
             ON p.id  = g.properties->>'$.programId'
         WHERE
             eer.trashed IS NULL";
         try {
             $startYears = $this->executeQueryForList($query.$whereQuery.$orderBy);
         } catch (\Exception $e) {
             throw new ExamControllerException(ExamControllerException::ERROR_FETCHING_EXAM_REGISTRATION,"Cannot fetch Exam Registration Start year details! Please try again.");
         }
         return $startYears;
     }
    /**
     * get recent published exam registration
     * @param $request
     * @return examRegistration
     */
    public function getRecentPublishedExamRegistration($request)
    {
        $request = $this->realEscapeObject($request);
        $whereQuery = "";
        $sortBy = " ORDER BY eer.properties ->> '$.examYear' DESC ,eer.properties ->> '$.examMonth' + 0 DESC ";
        $query = "SELECT 
                    eer.id AS examRegistrationId 
                FROM 
                    ec_exam_registration eer 
                INNER JOIN ec_exam_registration_batch eerb ON 
                    eerb.ec_exam_registration_id = eer.id 
                WHERE 
                    eer.`type` = 'REGULAR' AND 
                    trashed IS NULL AND 
                    eerb.properties ->>'$.isResultPublished' = 1";
        try {
            $examRegistration = $this->executeQueryForObject($query.$whereQuery.$sortBy);
        } catch (\Exception $e) {
            throw new ExamControllerException(ExamControllerException::ERROR_FETCHING_EXAM_REGISTRATION,"Cannot fetch Exam Registration details! Please try again.");
        }
        return $examRegistration;
    }
     /**
     * get batch assigned exam registration
     * @param $request
     * @return examRegistration
     */
    public function getBatchAssignedExamRegistration($request)
    {
        $request = $this->realEscapeObject($request);
        $whereQuery = "";
        $sortBy = " ORDER BY eer.properties ->> '$.examYear' ASC ,eer.properties ->> '$.examMonth' + 0 ASC ";
        $whereQuery = "";
        if(!empty($request->groupId)){
            $whereQuery .= " AND eerb.groups_id = '$request->groupId";
        }
        if(!empty($request->examRegType)){
            $whereQuery .= " AND eer.`type`  = '$request->examRegType'";
        }
        $query = "SELECT 
                    eer.id AS id,
                    eer.name AS name,
                    eerb.id AS batchRelationId,
                    eerb.properties->>'$.isRequiredForImport' AS statusFlag, 
                    eer.`type` 
                FROM 
                    ec_exam_registration eer 
                INNER JOIN ec_exam_registration_batch eerb ON 
                    eerb.ec_exam_registration_id = eer.id 
                WHERE 
                    trashed IS NULL ";
        try {
            $examRegistration = $this->executeQueryForList($query.$whereQuery.$sortBy);
        } catch (\Exception $e) {
            throw new ExamControllerException(ExamControllerException::ERROR_FETCHING_EXAM_REGISTRATION,"Cannot fetch Exam Registration details! Please try again.");
        }
        return $examRegistration;
    }
      /**
     * list of supplementary publising details
     * @param  $request
     * @return examRegistration
     */
    public function getSupplyPublishDetailsByBatch( $request){
        $request = $this->realEscapeObject($request);
        $whereQuery = "";
        if(empty($request->groupId)) {
            throw new ExamControllerException(ExamControllerException::EMPTY_PARAMETERS_EXAM_REGISTRATION, " Empty Parameter ! Batch group  id is empty ");
        }
        if(!empty($request->groupId)) {
            $groupIdString = is_array($request->groupId) ? "'" . implode("','",$request->groupId) . "'" : "'".$request->groupId."'";
            $whereQuery .= " AND eerb.groups_id IN ( $groupIdString )";
        }
        if(!empty($request->studentId)) {
            $studentIdString = is_array($request->studentId) ? "'" . implode("','",$request->studentId) . "'" : "'".$request->studentId."'";
            $whereQuery .= " AND esar.student_id IN ( $studentIdString )";
        }
        if (!empty($request->publishStartDate) &&!empty($request->publishEndDate) ) {
            $request->publishStartDate = date('Y-m-d', strtotime($request->publishStartDate));
            $request->publishStartDate = date('Y-m-d', strtotime($request->publishStartDate));
            $sql .= " AND eerb.properties->> '$.publishingStartDate' BETWEEN '$request->publishStartDate' AND '$request->publishEndDate'";
        }
        $query = "SELECT
            DISTINCT(eer.id),
            eer.name AS name,
            eer.type AS type,
            eerb.properties->> '$.publishingStartDate' as publishingStartDate,
            eerb.properties->> '$.publishingEndDate' as publishingEndDate,
            act.id as academicTermId,
            act.name as academicTermName
        FROM
            ec_exam_registration eer
        INNER JOIN ec_exam_registration_batch eerb
            ON eerb.ec_exam_registration_id =  eer.id
        INNER JOIN ec_exam_registration_subject eers ON
            eers.ec_exam_registration_batch_id = eerb.id
        INNER JOIN ec_student_assessment_registration esar ON
            esar.am_assessment_id = eers.am_assessment_id AND  
            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  academic_term act ON 
            act.id = CAST(eerb.properties ->> '$.academicTermId'AS CHAR) 
        WHERE
            eer.trashed IS NULL AND eer.type = 'SUPPLEMENTARY' AND eerb.properties->> '$.isResultPublished' = 1 ";
        try {
            $examDetails = $this->executeQueryForList($query.$whereQuery.$orderBy);
          
        } catch (\Exception $e) {
            throw new ExamControllerException(ExamControllerException::ERROR_FETCHING_EXAM_REGISTRATION,"Cannot fetch Exam Registration details! Please try again.");
        }
        return $examDetails;
    }
     /**
     * Search examRegistration
     * @param  $request
     * @return examRegistrations
     */
    public function getAllExamRegistrationForVivaValuationStaff( $request){
        $request = $this->realEscapeObject($request);
        $currentUerId = $GLOBALS['userId'];
        $whereQuery = "";
        $limitQuery = "";
        $query = "SELECT
            DISTINCT(eer.id),
            eer.name
        FROM
            cluster_groups_relations cgr 
        INNER JOIN cluster_members cm ON 
            cm.cluster_id = cgr.cluster_id 
        INNER JOIN `groups` sg ON 
            sg.id = cgr.groups_id 
        INNER JOIN groups_relations gr ON 
            gr.child_groups_id = sg.id
        INNER JOIN `groups` g ON 
            g.id = gr.parent_groups_id AND g.type = 'BATCH'
        INNER JOIN    ec_exam_registration_subject eers ON
            eers.cm_academic_paper_subjects_id = sg.paperSubjectId
        INNER JOIN ec_exam_registration_batch eerb ON
            eerb.id = eers.ec_exam_registration_batch_id AND eerb.groups_id = g.id 
        INNER JOIN ec_exam_registration eer ON
            eer.id = eerb.ec_exam_registration_id AND eer.trashed IS NULL
        WHERE
            cm.staff_id = '$currentUerId";
        try {
            $examRegistrations = $this->executeQueryForList($query.$limitQuery, $this->mapper[ExamRegistrationServiceMapper::SEARCH_EXAM_REGISTRATION]);
        } catch (\Exception $e) {
            throw new ExamControllerException(ExamControllerException::ERROR_FETCHING_EXAM_REGISTRATION,"Cannot fetch Exam Registration details! Please try again.");
        }
        return $examRegistrations;
    }
    /**
     * get Minor Honor Mooc Applied Students
     * @param $searchRequest 
     * @return $response 
     */
    public function getMinorHonorMoocAppliedStudents($searchRequest) {
        $searchRequest = $this->realEscapeObject($searchRequest);
        $response = new \stdClass;
        try{
            $studentsDetails = [];
            $request = new \stdClass;
            $request->groupId = $searchRequest->groupId;
            $request->examRegistrationId = $searchRequest->examRegistrationId;
            $studentsDetails = $this->getMoocAppliedStudentDetails($request);
            foreach($studentsDetails as $student){
                $requestForCertificate = new \stdClass();
                $requestForCertificate->groupId = $student->groupId;
                $requestForCertificate->studentId = $student->studentId;
                $requestForCertificate->assessmentId = $student->assessmentId;
                $requestForCertificate->type = 'MOOC_COURSE_CERTIFICATES';
                $student->uploadedCertificates = CertificateUploadService::getInstance()->getCertificateUpload($requestForCertificate);
                if(empty($student->uploadedCertificates)){
                    $student->verificationStatus = "NO_DOCUMENTS";
                }
            }
        }
         catch (\Exception $e){
            throw new ExamControllerException($e->getCode(),$e->getMessage());
        }
        $response->students = $studentsDetails;
        return $response;
    }
    /**
     * Search Course Type of exam registration
     * @param  $request
     * @return examRegistration
     */
    public function getCourseTypeOfExamRegistration( $request) {
        $request = $this->realEscapeObject($request);
        $whereQuery = "";
        if(!empty($request->examRegistrationId)){
            $whereQuery .= " AND eer.id='$request->examRegistrationId";
        }
       
        $query = "SELECT
            DISTINCT
            ct.courseTypeID,
            ct.course_Type as courseTypeName
        FROM
            ec_exam_registration eer
        INNER JOIN ec_exam_registration_batch eerb
            ON eerb.ec_exam_registration_id =  eer.id
        INNER JOIN ec_exam_registration_subject eers ON
            eers.ec_exam_registration_batch_id = eerb.id
        INNER JOIN `groups` g
            ON g.id =  eerb.groups_id
        INNER JOIN program p ON
            p.id = CAST(g.properties ->> '$.programId' AS CHAR)
        INNER JOIN `course_type` ct ON
            ct.courseTypeID = p.course_type_id
        WHERE
            eer.trashed IS NULL ";
        try {
            $courseType = $this->executeQueryForObject($query.$whereQuery);
        } catch (\Exception $e) {
            throw new ExamControllerException(ExamControllerException::ERROR_FETCHING_EXAM_REGISTRATION,"Cannot fetch Exam Registration details! Please try again.");
        }
        return $courseType;
    }
     /**
     * get Mooc Applied Student Details
     * @param  $request
     * @return examRegistration
     */
    public function getMoocAppliedStudentDetails( $request){
        $request = $this->realEscapeObject($request);
        $whereQuery = "";
        $orderBy = " ORDER BY spa.properties->>'$.registerNumber' ASC";
        if(empty($request->groupId) || empty($request->examRegistrationId)) {
            throw new ExamControllerException(ExamControllerException::EMPTY_PARAMETERS_EXAM_REGISTRATION, " Empty Parameter ! Batch group  id is empty ");
        }
        if(!empty($request->groupId)) {
            $groupIdString = is_array($request->groupId) ? "'" . implode("','",$request->groupId) . "'" : "'".$request->groupId."'";
            $whereQuery .= " AND eerb.groups_id IN ( $groupIdString )";
        }
        if(!empty($request->examRegistrationId)) {
            $examRegistrationIdString = is_array($request->examRegistrationId) ? "'" . implode("','",$request->examRegistrationId) . "'" : "'".$request->examRegistrationId."'";
            $whereQuery .= " AND eer.id IN ( $examRegistrationIdString )";
        }
        $query = "SELECT
            sa.studentID AS studentId,
            sa.myImage AS studentImage,
            sa.studentName,
            spa.properties->>'$.registerNumber' AS regNo,
            eer.id AS examRegistrationId,
            eer.name AS ExamRegistrationName,
            g.id as groupId,
            g.name as groupName,
            esar.am_assessment_id as assessmentId,
            s.code AS subjectCode,
            s.name AS subjectName,
            esar.properties->>'$.moocCertificateStatus' AS verificationStatus
        FROM
            ec_exam_registration eer
        INNER JOIN ec_exam_registration_batch eerb
            ON eerb.ec_exam_registration_id =  eer.id
        INNER JOIN ec_exam_registration_subject eers ON
            eers.ec_exam_registration_batch_id = eerb.id
        INNER JOIN ec_student_assessment_registration esar ON
            esar.am_assessment_id = eers.am_assessment_id AND  
            CAST(esar.properties ->> '$.registrationStatus' AS CHAR) = 'REGISTERED'
        INNER JOIN `groups` g ON
            g.id = eerb.groups_id
        INNER JOIN studentaccount sa ON 
            sa.studentID = esar.student_id
        INNER JOIN program p ON 
            p.id = g.properties->>'$.programId'
        INNER JOIN student_program_account spa ON 
            spa.student_id  = esar.student_id  AND
            spa.current_program_id = p.id 
        INNER JOIN ec_student_exam_registration_details eserd ON 
            eserd.ec_exam_registration_id = eer.id AND
            eserd.student_id = esar.student_id
        INNER JOIN  cm_academic_paper_subjects aps ON 
            aps.id = eers.cm_academic_paper_subjects_id
        INNER JOIN  v4_ams_subject s ON 
            aps.ams_subject_id = s.id
        WHERE
            eer.trashed IS NULL AND esar.properties->>'$.syllabusSubType' = 'MOOC' ";
        try {
            $studentDetails = $this->executeQueryForList($query.$whereQuery.$orderBy);
          
        } catch (\Exception $e) {
            throw new ExamControllerException(ExamControllerException::ERROR_FETCHING_EXAM_REGISTRATION,"Cannot fetch Exam Registration details! Please try again.");
        }
        return $studentDetails;
    }
    /**
     * Get current Exam Registration Details For Payment module 
     * @param  $examRegistrationId
     * @return examRegistrationDetails
     */
     public function getCurrentExamRegistrationDetailsForPaymentModule( $examRegistrationId){
        $examRegistrationId = $this->realEscapeString($examRegistrationId);
         $query = "SELECT
                    eer.type as examRegType,
                    IF((eer.properties->>'$.isSpecialExam') = true, 1, 0) AS isSpecialExam
         FROM
             ec_exam_registration eer
         WHERE
             eer.trashed IS NULL AND eer.id = '$examRegistrationId'";
         try {
             $examRegistrationDetails = $this->executeQueryForObject($query);
         } catch (\Exception $e) {
             throw new ExamControllerException(ExamControllerException::ERROR_FETCHING_EXAM_REGISTRATION,"Cannot fetch Exam Registration Start year details! Please try again.");
         }
         return $examRegistrationDetails;
     }
      /**
     * Search Exam  Revaluation
     * @param  $request
     * @return $examRegistrations
     */
    public function searchDetailedExamRevaluationDetails( $request)
    {
        $request = $this->realEscapeObject($request);
        $whereQuery = "";
        $limitQuery = "";
        $joinQueary = "";
        $orderQuery = "";
        if(!empty($request->examRegistrationId)){
            $whereQuery .= " AND eer.id='$request->examRegistrationId";
        }
        if(!empty($request->name)){
            $whereQuery .= " AND eer.name LIKE '%$request->name%'";
        }
        if(!empty($request->courseTypeId)){
            $whereQuery .= " AND p.course_type_id = '$request->courseTypeId'";
        }
        if(!empty($request->groupId)) {
            $groupIdString = is_array($request->groupId) ? "'" . implode("','",$request->groupId) . "'" : "'".$request->groupId."'";
            $whereQuery .= " AND g.id IN ( $groupIdString )";
        }
        if(!empty($request->academicPaperSubjectId)) {
            $academicPaperSubjectIdString = is_array($request->academicPaperSubjectId) ? "'" . implode("','",$request->academicPaperSubjectId) . "'" : "'".$request->academicPaperSubjectId."'";
            $whereQuery .= " AND aps.id IN ( $academicPaperSubjectIdString )";
        }
        if(!empty($request->examRegistrationBatchId)) {
            $examRegistrationBatchIdString = is_array($request->examRegistrationBatchId) ? "'" . implode("','",$request->examRegistrationBatchId) . "'" : "'".$request->examRegistrationBatchId."'";
            $whereQuery .= " AND eerb.id IN ( $examRegistrationBatchIdString )";
        }
        if(!empty($request->academicTermId)){
            $whereQuery .= " AND CAST(eerb.properties ->> '$.academicTermId'AS CHAR) = '$request->academicTermId'";
        }
        if(!empty($request->batchStartYear)){
            $whereQuery .= " AND g.properties ->> '$.startYear' = '$request->batchStartYear'";
        }
        if(!empty($request->type)) {
            foreach($request->type as $type){
                $whereQuery .= " AND eer.type LIKE '%$type%'";
            }
        }
        $groupByQuerry = " GROUP BY eer.id,p.id,g.id,aps.id";
        $orderQuery = " ORDER BY CAST(cap.properties ->> '$.order' AS UNSIGNED) ASC";
        $query = "SELECT
            DISTINCT(eer.id),
            eer.identifying_context,        
            eer.name,
            eer.type,
            eer.properties,
            eerp.id AS parentExamRegistrationId,
            eerp.properties ->> '$.examMonth' AS examMonth,
            MONTHNAME(STR_TO_DATE(eerp.properties ->> '$.examMonth', '%m')) AS examMonthName,
            eerp.properties ->> '$.examYear' AS examYear,
            eer.fees_properties,
            eer.trashed,
            eer.created_by,
            eer.created_date,
            eer.updated_by,
            eer.updated_date,
            eerb.groups_id,
            eerb.properties AS examBatchProperties,
            g.name AS groupName,
            g.type AS groupType,
            act.id as academicTermId,
            act.name as academicTermName,
            act.properties ->>'$.year' AS academicTermYear,
            act.properties ->>'$.orderNo' AS academicOrderNo,
            dept.deptID,
            deg.name as degreeName,
            deg.description as degreeDescription,
            group_concat(str.name SEPARATOR ' and ') as streamName,
            p.name as programName,
            dept.deptName,
            dept.departmentDesc as departmentDesc,
            ct.courseTypeID,
            ct.course_Type as courseTypeName,
            g.properties ->> '$.startYear' AS batchStartYear,
            g.identifying_context AS groupIdentifyingContext,
            g.properties AS groupProperties,
            eers.cm_academic_paper_subjects_id as academicPaperSubjectId,
            eers.am_assessment_id as assessmentId,
            s.code as subjectCode,
            aps.properties ->> '$.syllabusName' AS syllabusName,
            s.name as subjectName,
            aps.properties ->>'$.isInternal' AS isInternal,
            aps.properties ->>'$.isExternal' AS isExternal,
            aps.properties ->>'$.externalMaxMark' AS externalMaxMark,
            aps.properties ->>'$.internalMaxMark' AS internalMaxMark,
            aps.properties ->>'$.credit' AS subjectCredit,
            aps.properties ->>'$.subjectTypeId' AS subjectTypeId,
            aps.properties ->>'$.courseCode' AS courseCode,
            aps.properties ->>'$.externalMaxMark' + aps.properties ->>'$.internalMaxMark' AS totalSubjectMark,
            cap.properties ->> '$.order' as subjectPriority
        FROM
            ec_exam_registration eer
        INNER JOIN ec_exam_registration eerp ON 
            eer.properties->>'$.parentExamRegistrationId' = eerp.id
        INNER JOIN ec_exam_registration_batch eerb
            ON eerb.ec_exam_registration_id =  eer.id
        INNER JOIN ec_exam_registration_batch eerbp
            ON eerbp.ec_exam_registration_id =  eerp.id AND
                eerb.groups_id = eerbp.groups_id
        INNER JOIN ec_exam_registration_subject eers ON
            eers.ec_exam_registration_batch_id = eerbp.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 `groups` g
            ON g.id =  eerb.groups_id
        INNER JOIN program p ON
            p.id = CAST(g.properties ->> '$.programId' AS CHAR)
        INNER JOIN degree deg ON
            deg.id = p.degree_id
        INNER JOIN `academic_term` act ON
            act.id = CAST(eerb.properties ->> '$.academicTermId'AS CHAR)
        INNER JOIN `department` dept ON
            dept.deptID = CAST(g.properties ->> '$.departmentId' AS CHAR)
        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
            eer.trashed IS NULL";
        try {
            $examRegistrations = $this->executeQueryForList($query.$whereQuery.$groupByQuerry.$orderQuery.$limitQuery, $this->mapper[ExamRegistrationServiceMapper::SEARCH_DETAILED_EXAM_REGISTRATION_DETAILS]);
        } catch (\Exception $e) {
            throw new ExamControllerException(ExamControllerException::ERROR_FETCHING_EXAM_REGISTRATION,"Cannot fetch Exam Registration details! Please try again.");
        }
        return $examRegistrations;
    }
    /**
     * Get course type by exam registeration id and student id
     * @param  $studentId
     * @param  $examRegistrationId
     * @return courseTypeDetails
     */
     public function getStudentCourseTypeByExamRegistration($studentId,$examRegistrationId){
        $studentId = $this->realEscapeString($studentId);
        $examRegistrationId = $this->realEscapeString($examRegistrationId);
        if(!$studentId || !$examRegistrationId){
            throw new ExamControllerException(ExamControllerException::EMPTY_PARAMETERS," Exam Registration id and studentId are mandatory!");
        }
        $query = "SELECT
                        p.course_type_id as courseTypeId,ct.course_Type as courseType
                    FROM
                        ec_exam_registration_batch eerb
                    INNER JOIN  `groups` g ON 
                            g.id = eerb.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
                    INNER JOIN student_program_batch_log spbl ON
                        spbl.batch_group_id = g.id AND
                        spbl.program_student_id = spa.id AND spbl.properties->>'$.academicStatus' IN ('ACTIVE','COMPLETED')
                    INNER JOIN course_type ct ON ct.courseTypeID = p.course_type_id
                    WHERE
                        eerb.ec_exam_registration_id='$examRegistrationId' AND spa.student_id ='$studentId'  GROUP BY ct.courseTypeID";
         try {
             return $this->executeQueryForObject($query);
         } catch (\Exception $e) {
             throw new ExamControllerException(ExamControllerException::ERROR_FETCHING_COURSE_TYPE,"Cannot fetch course type details! Please try again.");
         }
     }
         /**
     * Get custom payment gateway module name by exam regiteration id and studentid
     * @param  $studentId
     * @param  $examRegistrationId
     * @return paymentGatewayModuleDetails
     */
    public function getPaymentGateWayModulesForCourseTypes($studentId,$examRegistrationId){
        $studentId = $this->realEscapeString($studentId);
        $examRegistrationId = $this->realEscapeString($examRegistrationId);
        $paymentGatewayModuleDetails = null;
         try {
            $customModules = json_decode(CommonService::getInstance()->getSettings(SettingsConstants::EXAM_CONTROLLER,SettingsConstants::PAYMENT_GATEWAY_MODULE_FOR_COURSETYPES));
            if($customModules->enableFeature){
                if($studentId && $examRegistrationId){
                    $courseType = $this->getStudentCourseTypeByExamRegistration($studentId,$examRegistrationId)->courseType;
                    $paymentGatewayModuleDetails = ($courseType && $customModules->$courseType) ? $customModules->$courseType : null;
                }
            }
            return $paymentGatewayModuleDetails;
         } catch (\Exception $e) {
             throw new ExamControllerException(ExamControllerException::ERROR_FETCHING_CUSTOM_PAYMENT_MODULE,"Cannot fetch payment module details! Please try again.");
         }
     }
      /**
     * get Regular Exam Registration Id by Supply Exam Registration Id
     * @param  $request
     * @return regularExamRegistration->id
     */
    public function getRegularExamRegistrationIdBySupplyExamRegistrationId( $request)
    {
        $request = $this->realEscapeObject($request);
        $whereQuery = "";
        if(!empty($request->examRegistrationId)){
            $whereQuery .= " AND eerb.ec_exam_registration_id = '$request->examRegistrationId";
        }
        if(!empty($request->groupId)){
            $whereQuery .= " AND eerb.groups_id = '$request->groupId";
        }
        $query = "SELECT DISTINCT 
                    eer.id
                FROM
                    ec_exam_registration_batch eerb
                INNER JOIN ec_exam_registration_batch eerbReg ON
                    eerbReg.groups_id = eerb.groups_id AND eerbReg.academicTermId = eerb.academicTermId
                INNER JOIN ec_exam_registration eer ON
                    eer.id = eerbReg.ec_exam_registration_id AND 
                    eer.`type` = 'REGULAR'
                WHERE
                    eer.trashed IS NULL AND ( (eer.properties ->> '$.isHonorCourse' IS NULL OR eer.properties ->> '$.isHonorCourse' != '1') AND (eer.properties ->> '$.isMinorCourse' IS NULL OR eer.properties ->> '$.isMinorCourse' != '1'))";
        try {
            $regularExamRegistration = $this->executeQueryForObject($query.$whereQuery);
        } catch (\Exception $e) {
            throw new ExamControllerException(ExamControllerException::ERROR_FETCHING_EXAM_REGISTRATION,"Cannot fetch Exam Registration details! Please try again.");
        }
        return $regularExamRegistration->id;
    }
     /**
     * Update False Number Queue Update Status
     * @param String $id
     * @param Boolean $publish
     * @return NULL
     */
    public function updateFalseNumberQueueUpdateStatus($id, $status,$message = "No message"){
        $id = $this->realEscapeString($id);
        $status = $this->realEscapeString($status);
        $message = $this->realEscapeString($message);
        $updatedBy = $GLOBALS['userId'];
        if(empty($id))
            throw new ExamControllerException(ExamControllerException::EMPTY_PARAMETERS_EXAM_REGISTRATION,"Exam Registration is invalid! Please enter a valid Exam Registration");
        $query = "UPDATE
                    ec_exam_registration
                SET
                    properties = JSON_SET(properties, '$.falseNoQueueStatus', '$status'),
                    properties = JSON_SET(properties, '$.falseNoQueueMessage', '$message'),
                    updated_by = '$updatedBy'
                WHERE
                    id = '$id'";
        try {
            $this->executeQuery($query);
        } catch (\Exception $e) {
            throw new ExamControllerException(ExamControllerException::EMPTY_PARAMETERS_EXAM_REGISTRATION,"Error update status! Please try again");
        }
    }
    /**
     * Search All Programs by Exam Registration
     * @param  $request
     * @return examRegistration
     */
    public function searchAllProgramsByExamRegistration( $request){
        $request = $this->realEscapeObject($request);
        $whereQuery = "";
        $limitQuery = "";
        $orderQuery = "";
        if(!empty($request->examRegistrationId)){
            $examRegistrationIdString = is_array($request->examRegistrationId) ? "'" . implode("','",$request->examRegistrationId) . "'" : "'".$request->examRegistrationId."'";
            $whereQuery .= " AND eer.id IN ( $examRegistrationIdString ) ";
        }
        if(!empty($request->courseTypeId)){
            $whereQuery .= " AND p.course_type_id = '$request->courseTypeId'";
        }
        if(!empty($request->groupId)) {
            $groupIdString = is_array($request->groupId) ? "'" . implode("','",$request->groupId) . "'" : "'".$request->groupId."'";
            $whereQuery .= " AND g.id IN ( $groupIdString )";
        }
        $orderQuery = " ORDER BY p.name ASC";
        $query = "SELECT
            DISTINCT(p.id) as programId,
            p.id as id,
            p.name as name,
            p.name as text
        FROM
            ec_exam_registration eer
        INNER JOIN ec_exam_registration_batch eerb
            ON eerb.ec_exam_registration_id =  eer.id
        INNER JOIN `groups` g
            ON g.id =  eerb.groups_id
        INNER JOIN program p ON
            p.id = CAST(g.properties ->> '$.programId' AS CHAR)
        INNER JOIN `course_type` ct ON
            ct.courseTypeID = p.course_type_id
        WHERE eer.trashed IS NULL ";
        try {
            $programs = $this->executeQueryForList($query.$whereQuery.$orderQuery);
        } catch (\Exception $e) {
            throw new ExamControllerException(ExamControllerException::ERROR_FETCHING_PROGRAMS,"Cannot fetch programs! Please try again.");
        }
        return $programs;
    }
}