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 / 9
 CRAP
0.00% covered (danger)
0.00%
0 / 639
UniversityExamService
0.00% covered (danger)
0.00%
0 / 1
0.00% covered (danger)
0.00%
0 / 9
 1892.00
0.00% covered (danger)
0.00%
0 / 639
 __construct
0.00% covered (danger)
0.00%
0 / 1
 2.00
0.00% covered (danger)
0.00%
0 / 2
 __clone
0.00% covered (danger)
0.00%
0 / 1
 2.00
0.00% covered (danger)
0.00%
0 / 2
 getInstance
0.00% covered (danger)
0.00%
0 / 1
 6.00
0.00% covered (danger)
0.00%
0 / 5
 getGradePointsOfStudentsForAnExam
0.00% covered (danger)
0.00%
0 / 1
 110.00
0.00% covered (danger)
0.00%
0 / 131
 getUniversityExamListByBatchSemesterAndSubjectIds
0.00% covered (danger)
0.00%
0 / 1
 6.00
0.00% covered (danger)
0.00%
0 / 14
 getStudentEndSemesterExamMarks
0.00% covered (danger)
0.00%
0 / 1
 182.00
0.00% covered (danger)
0.00%
0 / 332
 getUniversityExamMarksForConsolidatedMarkReport
0.00% covered (danger)
0.00%
0 / 1
 42.00
0.00% covered (danger)
0.00%
0 / 46
 getMarksOfStudentsForAnExam
0.00% covered (danger)
0.00%
0 / 1
 42.00
0.00% covered (danger)
0.00%
0 / 96
 updateGradeOfStudentBasedonUniversityMark
0.00% covered (danger)
0.00%
0 / 1
 6.00
0.00% covered (danger)
0.00%
0 / 11
<?php
namespace com\linways\core\ams\professional\service;
use com\linways\core\ams\professional\service\ExamService;
use com\linways\core\ams\professional\dto\SettingsConstents;
use com\linways\core\ams\professional\service\CommonService;
use com\linways\core\ams\professional\constant\nba\NbaMethod;
use com\linways\core\ams\professional\service\CourseTypeService;
use com\linways\core\ams\professional\constant\SettingsConstants;
use com\linways\core\ams\professional\exception\ProfessionalException;
use com\linways\core\ams\professional\request\GetMarksStudentExamRequest;
use com\linways\core\ams\professional\request\GetGradePointOfStudentExamRequest;
use com\linways\core\ams\professional\constant\nba\UniversityStudentMarkCalculationMethod;
class UniversityExamService extends BaseService
{
    // /Condition 1 - Presence of a static member variable
    private static $_instance = null;
    
    // /Condition 2 - Locked down the constructor
    private function __construct()
    {
        // $this->mapper = UniversityExamServiceMapper::getInstance()->getMapper();
    }
    // Prevent any oustide instantiation of this class
    
    // /Condition 3 - Prevent any object or instance of that class to be cloned
    private function __clone()
    {
    }
    // Prevent any copy of this object
    
    // /Condition 4 - Have a single globally accessible static method
    public static function getInstance()
    {
        if (!is_object(self::$_instance)) // or if( is_null(self::$_instance) ) or if( self::$_instance == null )
        self::$_instance = new self();
        return self::$_instance;
    }
    /**
     * It gets all grade points of all the students who wrote an exam.
     *
     * @param [type] $examId
     * @return void
     */
    public function getGradePointsOfStudentsForAnExam(GetGradePointOfStudentExamRequest $request)
    {
        $request->examId = $this->realEscapeString($request->examId);
        $request->batchId = $this->realEscapeString($request->batchId);
        $request->semId = $this->realEscapeString($request->semId);
        $request->subjectId = $this->realEscapeString($request->subjectId);
        $request->studentId = $this->realEscapeString($request->studentId);
        $responseList = [];
        $sql = "";
        $conditionStatement = "";
        $grpStatement = " GROUP BY sta.studentID";
        $joins = "";
        if(!empty($request->studentId))
        {
            $conditionStatement .=  " AND us.studentID='$request->studentId'";
        }
        //-----------------Start Mark calculation method-----------------
        $markCalculationMethod = null;
        $markCalculationMethod = CommonService::getInstance()->getSettings(SettingsConstents::NBA, SettingsConstents::UNIVERSITY_EXAM_MARK_CALCULATION_METHOD);
        if(empty($markCalculationMethod)){
            $markCalculationMethod = UniversityStudentMarkCalculationMethod::AVERAGE;
        }
        if($markCalculationMethod == UniversityStudentMarkCalculationMethod::MINIMUM){
            $universityMarkSelectionStatement = "ug.percentFrom as averagePercent";
        }elseif($markCalculationMethod == UniversityStudentMarkCalculationMethod::MAXIMUM){
            $universityMarkSelectionStatement = "ug.percentTo as averagePercent";
        }
        elseif($markCalculationMethod == UniversityStudentMarkCalculationMethod::GRADEPOINT_PRODUCT_DIFFERENCE_BY_CONST){
            $universityMarkSelectionStatement = "((ug.gradePoint * MAX(ug1.gradePoint))- 3.75) as averagePercent";
            $joins .= "LEFT JOIN university_assignbatchcourse uabc ON uabc.batchID = ue.batchID LEFT JOIN university_gradepoints ug1 ON ug1.typeID = uabc.typeID ";
        }
        else{
            $universityMarkSelectionStatement = "(ug.percentFrom + ug.percentTo)/2 as averagePercent";
        }
        //-----------------End Mark calculation method-----------------
        $subbatches = BatchService::getInstance()->getSubbatchBySubject($request->subjectId, $request->semId, $request->batchId);
        $isCurrentSem = SemesterService::getInstance()->isCurrentSemester($request->batchId, $request->semId);
        if(!empty($subbatches))
        {
            if($isCurrentSem)
            {
                $sql = "SELECT sta.studentID AS studentId,
                    sta.studentName,
                    sta.admissionNo,
                    ue.examID AS examId,
                    ug.percentFrom,
                    ug.percentTo,
                    $universityMarkSelectionStatement,
                    ug.gradePoint,
                    ue.subjectID AS subjectId,
                    ue.batchID AS batchId,
                    ue.semID AS semId FROM sbs_relation sr inner join subbatch_sbs ssbs on sr.sbsID = ssbs.sbsID inner join subbatch_student ss on ss.subbatchID = ssbs.subbatchID inner join universityExams ue on ue.subjectID = sr.subjectID and ue.batchID = sr.batchID and sr.semID = ue.semID inner join studentaccount sta on sta.studentID = ss.studentID and sta.batchID = ue.batchID inner join  university_studentgrade us on us.studentID = sta.studentID and us.examID = ue.examID and us.semID = ue.semID INNER JOIN university_gradepoints ug ON ug.gradeID = us.gradeObtained $joins 
                    WHERE ue.examID = $request->examId $conditionStatement $grpStatement";
            }
            else 
            {
                $sql = "SELECT sta.studentID AS studentId,
                    sta.studentName,
                    sta.admissionNo,
                    ue.examID AS examId,
                    ug.percentFrom,
                    ug.percentTo,
                    $universityMarkSelectionStatement,
                    ug.gradePoint,
                    ue.subjectID AS subjectId,
                    ue.batchID AS batchId,
                    ue.semID AS semId FROM sbs_relation sr inner join subbatch_sbs ssbs on sr.sbsID = ssbs.sbsID inner join subbatch_student ss on ss.subbatchID = ssbs.subbatchID inner join universityExams ue on ue.subjectID = sr.subjectID and ue.batchID = sr.batchID and sr.semID = ue.semID inner join studentaccount sta on sta.studentID = ss.studentID inner join  university_studentgrade us on us.studentID = sta.studentID and us.examID = ue.examID and us.semID = ue.semID INNER JOIN university_gradepoints ug ON ug.gradeID = us.gradeObtained $joins WHERE ue.examID = $request->examId  and sta.studentID in (select studentID from studentaccount where batchID = $request->batchId union select studentID from failed_students where previousBatch = $request->batchId and failedInSemester > $request->semId) $conditionStatement $grpStatement;";
            }
        }
        else 
        {
            if($isCurrentSem)
            {
                $sql = "SELECT
                    sta.studentID AS studentId,
                    sta.studentName,
                    sta.admissionNo,
                    ue.examID AS examId,
                    ug.percentFrom,
                    ug.percentTo,
                    $universityMarkSelectionStatement,
                    ug.gradePoint,
                    ue.subjectID AS subjectId,
                    ue.batchID AS batchId,
                    ue.semID AS semId
                FROM
                    universityExams ue
                        INNER JOIN
                    university_studentgrade us ON us.examID = ue.examID
                        AND ue.subjectID = us.subjectID
                        AND ue.semID = us.semID
                        INNER JOIN
                    studentaccount sta ON sta.studentID = us.studentID and sta.batchID = ue.batchID
                        INNER JOIN
                    university_gradepoints ug ON ug.gradeID = us.gradeObtained 
                      $joins 
                WHERE
                    ue.examID = $request->examId $conditionStatement $grpStatement";
            }
            else
            {
                $sql = "SELECT
                    sta.studentID AS studentId,
                    sta.studentName,
                    sta.admissionNo,
                    ue.examID AS examId,
                    ug.percentFrom,
                    ug.percentTo,
                    $universityMarkSelectionStatement,
                    ug.gradePoint,
                    ue.subjectID AS subjectId,
                    ue.batchID AS batchId,
                    ue.semID AS semId
                FROM
                    universityExams ue
                        INNER JOIN
                    university_studentgrade us ON us.examID = ue.examID
                        AND ue.semID = us.semID
                        INNER JOIN
                    studentaccount sta ON sta.studentID = us.studentID
                        INNER JOIN
                    university_gradepoints ug ON ug.gradeID = us.gradeObtained 
                      $joins 
                WHERE
                    ue.examID = $request->examId and sta.studentID in (select studentID from studentaccount where batchID = $request->batchId union select studentID from failed_students where previousBatch = $request->batchId and failedInSemester > $request->semId) $conditionStatement $grpStatement";
            }
        }
        
        
        try {
            $responseList = $this->executeQueryForList($sql);
        } catch (\Exception $e) {
            throw new ProfessionalException($e->getCode(), $e->getMessage());
        }
        return $responseList;
    }
    public function getUniversityExamListByBatchSemesterAndSubjectIds($batchId, $semId, $subjectId)
    {
        $sql = "";
        $responseList = [];
        $batchId = $this->realEscapeString($batchId);
        $semId = $this->realEscapeString($semId);
        $subjectId = $this->realEscapeString($subjectId);
        $sql = "SELECT examID as id, examName as name , examTotalMarks as totalMarks, subjectID as subjectId, batchID as batchId, semID as semId FROM universityExams WHERE  batchID = '$batchId' AND semID = '$semId' AND subjectID = '$subjectId' ";
        try {
            $responseList = $this->executeQueryForList($sql);
        } catch (\Exception $e) {
            throw new ProfessionalException($e->getCode(), $e->getMessage());
        }
        return $responseList;
    }
    public function getStudentEndSemesterExamMarks($examId, $batchId, $semId, $subjectId)
    {
        $endSemesterExamMark = CommonService::getInstance()->getSettings(SettingsConstants::NBA, NbaMethod::OBE_END_SEMESTER_MARK_SELECTION);
        $examId = $this->realEscapeString($examId);
        $batchId = $this->realEscapeString($batchId);
        $semId = $this->realEscapeString($semId);
        $subjectId = $this->realEscapeString($subjectId);
        $subbatches = BatchService::getInstance()->getSubbatchBySubject($subjectId, $semId, $batchId);
        $isCurrentSem = SemesterService::getInstance()->isCurrentSemester($batchId, $semId);
        $courseType = CourseTypeService::getInstance()->getcourseTypeByBatchId($batchId);
        if($endSemesterExamMark==NbaMethod::OBE_END_SEMESTER_MARK_NOT_NORMALIZED){
            if($courseType->course_Type == 'UG'){
                if(!empty($subbatches))
                {
                    if($isCurrentSem)
                    {
                        $sql = "SELECT 
                        im.studentID as studentId,
                        im.batchID as batchId,
                        im.semID as semId,
                        im.subjectID as subjectId,
                        ee.mark as externalMark,
                        im.internalMarks as internalMark,
                        CASE
                            WHEN ee.mark is NULL AND gsm.marks is NULL THEN im.internalMarks
                            WHEN ee.mark is not NULL AND gsm.marks is not NULL THEN ee.mark+im.internalMarks+gsm.marks
                            WHEN ee.mark is NULL AND gsm.marks is not NULL THEN im.internalMarks+gsm.marks
                            WHEN ee.mark is not NULL AND gsm.marks is NULL THEN ee.mark+im.internalMarks
                        END AS totalMark
                    
                    FROM
                    sbs_relation sr inner join subbatch_sbs ssbs on sr.sbsID = ssbs.sbsID inner join subbatch_student ss on ss.subbatchID = ssbs.subbatchID  inner join studentaccount sta on sta.studentID = ss.studentID inner join
                        internal_marks im ON im.studentID = sta.studentID
                         left join 
                        exammarks_external ee ON (ee.studentID=im.studentID AND ee.examID = '$examId')
                        left join gracemarks_student_marks gsm ON gsm.examID = ee.examID and gsm.studentID = sta.studentID and gsm.semID = im.semID
                    WHERE
                    im.semID = '$semId' AND im.batchID = '$batchId'
                            AND im.subjectID = '$subjectId'
                            ";
                    }
                    else 
                    {
                        $sql = "SELECT 
                        im.studentID as studentId,
                        im.batchID as batchId,
                        im.semID as semId,
                        im.subjectID as subjectId,
                        ee.mark as externalMark,
                        im.internalMarks as internalMark,
                        CASE
                            WHEN ee.mark is NULL AND gsm.marks is NULL THEN im.internalMarks
                            WHEN ee.mark is not NULL AND gsm.marks is not NULL THEN ee.mark+im.internalMarks+gsm.marks
                            WHEN ee.mark is NULL AND gsm.marks is not NULL THEN im.internalMarks+gsm.marks
                            WHEN ee.mark is not NULL AND gsm.marks is NULL THEN ee.mark+im.internalMarks
                        END AS totalMark
                    
                    FROM
                    sbs_relation sr inner join subbatch_sbs ssbs on sr.sbsID = ssbs.sbsID inner join subbatch_student ss on ss.subbatchID = ssbs.subbatchID  inner join studentaccount sta on sta.studentID = ss.studentID inner join
                        internal_marks im ON im.studentID = sta.studentID
                         left join 
                        exammarks_external ee ON (ee.studentID=im.studentID AND ee.examID = '$examId')
                        left join gracemarks_student_marks gsm ON gsm.examID = ee.examID and gsm.studentID = sta.studentID and gsm.semID = im.semID
                    WHERE
                    im.semID = '$semId' AND im.batchID = '$batchId'
                            AND im.subjectID = '$subjectId'
                             AND sta.studentID IN (SELECT 
                            studentID
                        FROM
                            studentaccount
                        WHERE
                            batchID = '$batchId' UNION SELECT 
                            studentID
                        FROM
                            failed_students
                        WHERE
                            previousBatch = '$batchId'
                                AND failedInSemester > '$semId')";
                    }
                }
                else 
                {
                    if($isCurrentSem)
                    {
                        $sql = "SELECT 
                        im.studentID as studentId,
                        im.batchID as batchId,
                        im.semID as semId,
                        im.subjectID as subjectId,
                        ee.mark as externalMark,
                        im.internalMarks as internalMark,
                        CASE
                            WHEN ee.mark is NULL AND gsm.marks is NULL THEN im.internalMarks
                            WHEN ee.mark is not NULL AND gsm.marks is not NULL THEN ee.mark+im.internalMarks+gsm.marks
                            WHEN ee.mark is NULL AND gsm.marks is not NULL THEN im.internalMarks+gsm.marks
                            WHEN ee.mark is not NULL AND gsm.marks is NULL THEN ee.mark+im.internalMarks
                        END AS totalMark
                    
                    FROM
                        studentaccount sta
                         inner join     
                        internal_marks im ON sta.studentID=im.studentID
                         left join 
                        exammarks_external ee ON (ee.studentID=im.studentID AND ee.examID = '$examId')
                        left join gracemarks_student_marks gsm ON gsm.examID = ee.examID and gsm.studentID = sta.studentID and gsm.semID = im.semID
                    WHERE
                    im.semID = '$semId' AND im.batchID = '$batchId'
                            AND im.subjectID = '$subjectId'
                            ";
                    }
                    else
                    {
                        $sql = "SELECT 
                        im.studentID as studentId,
                        im.batchID as batchId,
                        im.semID as semId,
                        im.subjectID as subjectId,
                        ee.mark as externalMark,
                        im.internalMarks as internalMark,
                        CASE
                            WHEN ee.mark is NULL AND gsm.marks is NULL THEN im.internalMarks
                            WHEN ee.mark is not NULL AND gsm.marks is not NULL THEN ee.mark+im.internalMarks+gsm.marks
                            WHEN ee.mark is NULL AND gsm.marks is not NULL THEN im.internalMarks+gsm.marks
                            WHEN ee.mark is not NULL AND gsm.marks is NULL THEN ee.mark+im.internalMarks
                        END AS totalMark
                    
                    FROM
                        studentaccount sta
                         inner join     
                        internal_marks im ON sta.studentID=im.studentID
                         left join 
                        exammarks_external ee ON (ee.studentID=im.studentID AND ee.examID = '$examId')
                        left join gracemarks_student_marks gsm ON gsm.examID = ee.examID and gsm.studentID = sta.studentID and gsm.semID = im.semID
                    WHERE
                        im.semID = '$semId' AND im.batchID = '$batchId'
                            AND im.subjectID = '$subjectId'
                             AND sta.studentID IN (SELECT 
                            studentID
                        FROM
                            studentaccount
                        WHERE
                            batchID = '$batchId' UNION SELECT 
                            studentID
                        FROM
                            failed_students
                        WHERE
                            previousBatch = '$batchId'
                                AND failedInSemester > '$semId')";
                    }
                }
            }elseif($courseType->course_Type = "PG"){
                if(!empty($subbatches))
                {
                    if($isCurrentSem)
                    {
                        $sql = "SELECT 
                        im.studentID as studentId,
                        im.batchID as batchId,
                        im.semID as semId,
                        im.subjectID as subjectId,
                        ee.mark as externalMark,
                        im.internalMarks as internalMark,
                        CASE
                            WHEN ee.mark is NULL AND gsm.marks is NULL THEN im.internalMarks
                            WHEN ee.mark is not NULL AND gsm.marks is not NULL THEN ee.mark+im.internalMarks+gsm.marks
                            WHEN ee.mark is NULL AND gsm.marks is not NULL THEN im.internalMarks+gsm.marks
                            WHEN ee.mark is not NULL AND gsm.marks is NULL THEN ee.mark+im.internalMarks
                        END AS totalMark
                    
                    FROM
                    sbs_relation sr inner join subbatch_sbs ssbs on sr.sbsID = ssbs.sbsID inner join subbatch_student ss on ss.subbatchID = ssbs.subbatchID  inner join studentaccount sta on sta.studentID = ss.studentID inner join
                        internal_marks im ON im.studentID = sta.studentID
                        left join 
                        externalexammarks_finalized ee ON (ee.studentID=im.studentID AND ee.examID = '$examId')
                        left join gracemarks_student_marks gsm ON gsm.examID = ee.examID and gsm.studentID = sta.studentID and gsm.semID = im.semID
                    WHERE
                    im.semID = '$semId' AND im.batchID = '$batchId'
                            AND im.subjectID = '$subjectId'
                           ";
                    }
                    else 
                    {
                        $sql = "SELECT 
                        im.studentID as studentId,
                        im.batchID as batchId,
                        im.semID as semId,
                        im.subjectID as subjectId,
                        ee.mark as externalMark,
                        im.internalMarks as internalMark,
                        CASE
                            WHEN ee.mark is NULL AND gsm.marks is NULL THEN im.internalMarks
                            WHEN ee.mark is not NULL AND gsm.marks is not NULL THEN ee.mark+im.internalMarks+gsm.marks
                            WHEN ee.mark is NULL AND gsm.marks is not NULL THEN im.internalMarks+gsm.marks
                            WHEN ee.mark is not NULL AND gsm.marks is NULL THEN ee.mark+im.internalMarks
                        END AS totalMark
                    
                    FROM
                    sbs_relation sr inner join subbatch_sbs ssbs on sr.sbsID = ssbs.sbsID inner join subbatch_student ss on ss.subbatchID = ssbs.subbatchID  inner join studentaccount sta on sta.studentID = ss.studentID inner join
                        internal_marks im ON im.studentID = sta.studentID
                        left join 
                        externalexammarks_finalized ee ON (ee.studentID=im.studentID AND ee.examID = '$examId')
                        left join gracemarks_student_marks gsm ON gsm.examID = ee.examID and gsm.studentID = sta.studentID and gsm.semID = im.semID
                    WHERE
                    im.semID = '$semId' AND im.batchID = '$batchId'
                            AND im.subjectID = '$subjectId'
                             AND sta.studentID IN (SELECT 
                            studentID
                        FROM
                            studentaccount
                        WHERE
                            batchID = '$batchId' UNION SELECT 
                            studentID
                        FROM
                            failed_students
                        WHERE
                            previousBatch = '$batchId'
                                AND failedInSemester > '$semId')";
                    }
                }
                else 
                {
                    if($isCurrentSem)
                    {
                        $sql = "SELECT 
                        im.studentID as studentId,
                        im.batchID as batchId,
                        im.semID as semId,
                        im.subjectID as subjectId,
                        ee.mark as externalMark,
                        im.internalMarks as internalMark,
                        CASE
                            WHEN ee.mark is NULL AND gsm.marks is NULL THEN im.internalMarks
                            WHEN ee.mark is not NULL AND gsm.marks is not NULL THEN ee.mark+im.internalMarks+gsm.marks
                            WHEN ee.mark is NULL AND gsm.marks is not NULL THEN im.internalMarks+gsm.marks
                            WHEN ee.mark is not NULL AND gsm.marks is NULL THEN ee.mark+im.internalMarks
                        END AS totalMark
                    
                    FROM
                        studentaccount sta
                        inner join     
                        internal_marks im ON sta.studentID=im.studentID
                        left join 
                        externalexammarks_finalized ee ON (ee.studentID=im.studentID AND ee.examID = '$examId')
                        left join gracemarks_student_marks gsm ON gsm.examID = ee.examID and gsm.studentID = sta.studentID and gsm.semID = im.semID
                    WHERE
                    im.semID = '$semId' AND im.batchID = '$batchId'
                            AND im.subjectID = '$subjectId'
                            ";
                    }
                    else
                    {
                        $sql = "SELECT 
                        im.studentID as studentId,
                        im.batchID as batchId,
                        im.semID as semId,
                        im.subjectID as subjectId,
                        ee.mark as externalMark,
                        im.internalMarks as internalMark,
                        CASE
                            WHEN ee.mark is NULL AND gsm.marks is NULL THEN im.internalMarks
                            WHEN ee.mark is not NULL AND gsm.marks is not NULL THEN ee.mark+im.internalMarks+gsm.marks
                            WHEN ee.mark is NULL AND gsm.marks is not NULL THEN im.internalMarks+gsm.marks
                            WHEN ee.mark is not NULL AND gsm.marks is NULL THEN ee.mark+im.internalMarks
                        END AS totalMark
                    
                    FROM
                        studentaccount sta
                        inner join     
                        internal_marks im ON sta.studentID=im.studentID
                        left join 
                        externalexammarks_finalized ee ON (ee.studentID=im.studentID AND ee.examID = '$examId') 
                        left join gracemarks_student_marks gsm ON gsm.examID = ee.examID and gsm.studentID = sta.studentID and gsm.semID = im.semID
                    WHERE
                        im.semID = '$semId' AND im.batchID = '$batchId'
                            AND im.subjectID = '$subjectId' 
                            AND sta.studentID IN (SELECT 
                            studentID
                        FROM
                            studentaccount
                        WHERE
                            batchID = '$batchId' UNION SELECT 
                            studentID
                        FROM
                            failed_students
                        WHERE
                            previousBatch = '$batchId'
                                AND failedInSemester > '$semId')";
                    }
                }
            }
            try {
                $studentMarkList = $this->executeQueryForList($sql);
                if(empty($studentMarkList)){
                    $sql = "SELECT 
                        ee.studentID AS studentId,
                        $batchId AS batchId,
                        $semId AS semId,
                        $subjectId AS subjectId,
                        ee.mark AS totalMark
                    FROM
                        studentaccount sta
                            INNER JOIN
                        exammarks_external ee ON (ee.studentID = sta.studentID
                            AND ee.examID = '$examId')";
                    $studentMarkList = $this->executeQueryForList($sql);
                }
            } catch (\Exception $e) {
                throw new ProfessionalException($e->getCode(), $e->getMessage());
            }
        }else{
            
            $sql = "SELECT 
                marks.student_id AS studentId,
                marks.total_marks,
                marks.batch_id AS batchId,
                marks.sem_id AS semId,
                marks.subject_id AS subjectId,
                marks.grade,
                marks.grade_point  As totalMark,
                eg.percentTo,
                MAX(egg.gradePoint) As maxMark
            FROM
                ec_subject_consolidated_mark_details marks
                    INNER JOIN
                assignedSubjectGradeBatches asgb ON asgb.batches_id = marks.batch_id
                    INNER JOIN
                exam_gradepoints eg ON eg.letterGrade = marks.grade
                    AND eg.schemeID = asgb.exam_gradingscheme_id
                    INNER JOIN
                exam_gradepoints egg ON egg.schemeID = asgb.exam_gradingscheme_id
            WHERE
            marks.batch_id = '$batchId' AND marks.sem_id = '$semId'
                        AND marks.subject_id = '$subjectId'
            GROUP BY marks.student_id";       
            try {
                $studentMarkList = $this->executeQueryForList($sql);
            } catch (\Exception $e) {
                throw new ProfessionalException($e->getCode(), $e->getMessage());
            } 
        }
       
        
        return $studentMarkList;
    }
    
    public function getUniversityExamMarksForConsolidatedMarkReport($batchId,$semesterId,$subjectId,$studentId)
    {
        $batchId = $this->realEscapeString($batchId);
        $semesterId = $this->realEscapeString($semesterId);
        $subjectId = $this->realEscapeString($subjectId);
        $studentId = $this->realEscapeString($studentId);
        $response = new \StdClass;
        $query = "SELECT 
                    *
                FROM
                    universityExams ue
                        LEFT JOIN
                    university_examcredits uec ON uec.batchID = ue.batchID
                        AND uec.semID = ue.semID
                        AND uec.subjectID = ue.subjectID
                    WHERE ue.batchID='$batchId' AND ue.semID='$semesterId' AND ue.subjectId = '$subjectId';";
        try{
            $response->entryType = ExamService::getInstance()->getExamEntryType($batchId);
            $response->examDetails = $this->executeQueryForObject($query);
            if($response->entryType === "GRADE" && !empty($response->examDetails->examID))
            {
                $query = "SELECT
                            ue.examID AS examId,
                            ug.gradePoint
                        FROM
                            universityExams ue
                                INNER JOIN
                            university_studentgrade us ON us.examID = ue.examID
                                AND ue.subjectID = us.subjectID
                                AND ue.semID = us.semID
                                INNER JOIN
                            studentaccount sta ON sta.studentID = us.studentID
                                INNER JOIN
                            university_gradepoints ug ON ug.gradeID = us.gradeObtained
                        WHERE
                            ue.examID = ".$response->examDetails->examID." and sta.studentID ='$studentId' AND ue.semID='$semesterId' AND ue.batchID='$batchId' AND ue.subjectID='$subjectId' ;";
            } else if ($response->entryType === "MARK" && !empty($response->examDetails->examID)) {
                $query = "select um.subjectID,um.marksObtained,ue.examTotalMarks from universityExams ue
                INNER JOIN universityMarks um ON um.examID=ue.examID where um.studentID='$studentId' and ue.semID='$semesterId' and ue.subjectID='$subjectId' AND ue.examID='".$response->examDetails->examID."';";
            }
            $response->examMarks = $this->executeQueryForObject($query);
            return $response;
        }catch(\Exception $e)
        {
            throw new ProfessionalException($e->getCode(),$e->getMessage());
        }
        
    }
    /**
     * It gets all grade points of all the students who wrote an exam.
     *
     * @param [type] $examId
     * @return void
     */
    public function getMarksOfStudentsForAnExam(GetMarksStudentExamRequest $request)
    {
        $request->examId = $this->realEscapeString($request->examId);
        $request->batchId = $this->realEscapeString($request->batchId);
        $request->semId = $this->realEscapeString($request->semId);
        $request->subjectId = $this->realEscapeString($request->subjectId);
        $request->studentId = $this->realEscapeString($request->studentId);
        $responseList = [];
        $sql = "";
        $conditionStatement = "";
        if(!empty($request->studentId))
        {
            $conditionStatement .=  " AND um.studentID='$request->studentId'";
        }
        $subbatches = BatchService::getInstance()->getSubbatchBySubject($request->subjectId, $request->semId, $request->batchId);
        $isCurrentSem = SemesterService::getInstance()->isCurrentSemester($request->batchId, $request->semId);
        if(!empty($subbatches))
        {
            if($isCurrentSem)
            {
                $sql = "SELECT distinct sta.studentID AS studentId,
                    sta.studentName,
                    sta.admissionNo,
                    ue.examID AS examId,
                    um.marksObtained,
                    um.percentage,
                    um.internalMark,
                    ue.subjectID AS subjectId,
                    ue.batchID AS batchId,
                    ue.semID AS semId FROM sbs_relation sr inner join subbatch_sbs ssbs on sr.sbsID = ssbs.sbsID inner join subbatch_student ss on ss.subbatchID = ssbs.subbatchID inner join universityExams ue on ue.subjectID = sr.subjectID and ue.batchID = sr.batchID and sr.semID = ue.semID inner join studentaccount sta on sta.studentID = ss.studentID and sta.batchID = ue.batchID INNER JOIN universityMarks um ON ue.examID = um.examID WHERE ue.examID = $request->examId $conditionStatement";
            }
            else 
            {
                $sql = "SELECT distinct sta.studentID AS studentId,
                    sta.studentName,
                    sta.admissionNo,
                    ue.examID AS examId,
                    um.marksObtained,
                    um.percentage,
                    um.internalMark,
                    ue.subjectID AS subjectId,
                    ue.batchID AS batchId,
                    ue.semID AS semId FROM sbs_relation sr inner join subbatch_sbs ssbs on sr.sbsID = ssbs.sbsID inner join subbatch_student ss on ss.subbatchID = ssbs.subbatchID inner join universityExams ue on ue.subjectID = sr.subjectID and ue.batchID = sr.batchID and sr.semID = ue.semID inner join studentaccount sta on sta.studentID = ss.studentID INNER JOIN universityMarks um ON ue.examID = um.examID WHERE ue.examID = $request->examId  and sta.studentID in (select studentID from studentaccount where batchID = $request->batchId union select studentID from failed_students where previousBatch = $request->batchId and failedInSemester > $request->semId) $conditionStatement;";
            }
        }
        else 
        {
            if($isCurrentSem)
            {
                $sql = "SELECT
                    sta.studentID AS studentId,
                    sta.studentName,
                    sta.admissionNo,
                    ue.examID AS examId,
                    um.marksObtained,
                    um.percentage,
                    um.internalMark,
                    ue.subjectID AS subjectId,
                    ue.batchID AS batchId,
                    ue.semID AS semId
                FROM
                    universityExams ue
                    INNER JOIN universityMarks um ON ue.examID = um.examID
                        INNER JOIN
                    studentaccount sta ON sta.studentID = us.studentID and sta.batchID = ue.batchID
                WHERE
                    ue.examID = $request->examId $conditionStatement";
            }
            else
            {
                $sql = "SELECT
                    sta.studentID AS studentId,
                    sta.studentName,
                    sta.admissionNo,
                    ue.examID AS examId,
                    um.marksObtained,
                    um.percentage,
                    um.internalMark,
                    ue.subjectID AS subjectId,
                    ue.batchID AS batchId,
                    ue.semID AS semId
                FROM
                    universityExams ue
                    INNER JOIN universityMarks um ON ue.examID = um.examID
                        INNER JOIN
                    studentaccount sta ON sta.studentID = us.studentID
                WHERE
                    ue.examID = $request->examId and sta.studentID in (select studentID from studentaccount where batchID = $request->batchId union select studentID from failed_students where previousBatch = $request->batchId and failedInSemester > $request->semId) $conditionStatement";
            }
        }
        
        
        try {
            $responseList = $this->executeQueryForList($sql);
        } catch (\Exception $e) {
            throw new ProfessionalException($e->getCode(), $e->getMessage());
        }
        return $responseList;
    }
    /**
     * Update Grade of a student of a student based on University
     *
     * @param [type] $request
     * @return void
     */
    public function updateGradeOfStudentBasedonUniversityMark($request)
    {
        $deletionQuerySql = "delete us from university_studentgrade us inner join universityExams ue on us.examID = ue.examID and ue.subjectID = us.subjectID and ue.semID = us.semID inner join studentaccount sa on sa.batchID = ue.batchID where us.semID = '".$request->semId."' and sa.batchID = '".$request->batchId."' ;";
        $insertionQuerySql = "insert into university_studentgrade (examID, subjectID, gradeObtained, noOfChances, passType, semID, studentID)
        select um.examID,um.subjectID,ug.gradeID,um.chances,um.passType,um.semID,um.studentID from universityMarks um inner join universityExams ue on um.examID = ue.examID and ue.subjectID = um.subjectID and ue.semID = um.semID inner join university_assignbatchcourse ua on ua.batchID = ue.batchID inner join university_gradepoints ug on ug.typeID = ua.typeID and ((if(um.internalMark >0, um.internalMark, 0) + if(um.marksObtained >0, um.marksObtained, 0))/ (if(ue.maxInternal, ue.maxInternal, 0) + if(examTotalMarks, examTotalMarks, 0)))*100  between ug.percentFrom and ug.percentTo  where ue.batchID = '".$request->batchId."' and ue.semID = '".$request->semId."'; ";
        try {
            $this->executeQuery($deletionQuerySql);
            $this->executeQuery($insertionQuerySql);
        }catch (\Exception $e) {
            throw new ProfessionalException($e->getCode(), $e->getMessage());
        }
    }
}