Code Coverage |
||||||||||
Classes and Traits |
Functions and Methods |
Lines |
||||||||
| Total | |
0.00% |
0 / 1 |
|
0.00% |
0 / 79 |
CRAP | |
0.00% |
0 / 1674 |
| MarkService | |
0.00% |
0 / 1 |
|
0.00% |
0 / 79 |
98910.00 | |
0.00% |
0 / 1674 |
| __construct | |
0.00% |
0 / 1 |
2.00 | |
0.00% |
0 / 3 |
|||
| __clone | |
0.00% |
0 / 1 |
2.00 | |
0.00% |
0 / 1 |
|||
| getInstance | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 5 |
|||
| getBackpaperCount | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 12 |
|||
| getBacklogCount | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 11 |
|||
| getStudentCgpa | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 28 |
|||
| createStudentUniversityGrade | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 16 |
|||
| updateStudentUniversityGrade | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 14 |
|||
| removeStudentUniversityGrade | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 8 |
|||
| getTotalCreditsBySem | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 19 |
|||
| createStudentSgpaCgpa | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 14 |
|||
| updateStudentSgpaCgpa | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 14 |
|||
| getStudentSgpa | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 19 |
|||
| isStudentUniversityGradeEntered | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 15 |
|||
| getStudentExamMarkDetailsByExamId | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 11 |
|||
| updateStudentExamMark | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 9 |
|||
| createStudentExamMark | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 9 |
|||
| isNormalisedMarksApproved | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 11 |
|||
| isNormalisedMarksPublished | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 11 |
|||
| changeNormalisedMarksApprovalStatus | |
0.00% |
0 / 1 |
420.00 | |
0.00% |
0 / 58 |
|||
| getStudentSubjectMarksCgpa | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 13 |
|||
| getStudentMarksCgpa | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 15 |
|||
| getStudentUniversityExamTotalMarks | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 11 |
|||
| getStudentSgpaCpga | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 11 |
|||
| checkIfFailedSubjCreditConsidered | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 12 |
|||
| getMarkListRegularDiplomaIndividual | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 46 |
|||
| getCourseGrade | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 19 |
|||
| getStudentGraceMarkPG | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 14 |
|||
| getStudentGraceMark | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 14 |
|||
| getClassAvgByExamId | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 18 |
|||
| getTotalMarksOfAStudentByExamType | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 12 |
|||
| getStudentInternalAddedMark | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 18 |
|||
| hasInternalMarkFinalised | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 24 |
|||
| getStudentNormalizedMark | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 19 |
|||
| getStudentExamMark | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 17 |
|||
| updateStudentExamMarks | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 18 |
|||
| insertStudentExamMark | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 20 |
|||
| logStudentMarks | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 10 |
|||
| insertStaffValuatedAnswerSheetCount | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 11 |
|||
| updateStaffValuatedAnswerSheetCount | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 12 |
|||
| setConfirmStaffValuatedAnswerSheetCount | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 16 |
|||
| getStaffValuatedAnswerSheetCount | |
0.00% |
0 / 1 |
30.00 | |
0.00% |
0 / 31 |
|||
| saveStudentExamMarkAndLog | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 19 |
|||
| getStudentExamMarkEditLog | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 16 |
|||
| getSubjectsWithStudentMark | |
0.00% |
0 / 1 |
380.00 | |
0.00% |
0 / 230 |
|||
| saveUgStudentExternalExamMark | |
0.00% |
0 / 1 |
132.00 | |
0.00% |
0 / 30 |
|||
| savePgStudentExternalExamMark | |
0.00% |
0 / 1 |
210.00 | |
0.00% |
0 / 42 |
|||
| saveStudentFinilizedExternalExamMark | |
0.00% |
0 / 1 |
56.00 | |
0.00% |
0 / 21 |
|||
| saveStudentsForThirdValuation | |
0.00% |
0 / 1 |
42.00 | |
0.00% |
0 / 21 |
|||
| getFormulaByExamRegId | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 28 |
|||
| getAllRuleConstants | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 15 |
|||
| deleteThirdValStudents | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 20 |
|||
| deleteStudentFinalizedMark | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 12 |
|||
| getStudentsExternalMarksByExam | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 20 |
|||
| getStudentRvStatus | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 11 |
|||
| checkStudentInNormaliseMark | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 19 |
|||
| savePgStudentExternalExamMarkAndAttendance | |
0.00% |
0 / 1 |
306.00 | |
0.00% |
0 / 56 |
|||
| getStaffIDNormaliseMarkBySubjectIdAndBatchId | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 18 |
|||
| saveUgStudentsExternalMark | |
0.00% |
0 / 1 |
90.00 | |
0.00% |
0 / 25 |
|||
| saveStudentInternalMarkLog | |
0.00% |
0 / 1 |
42.00 | |
0.00% |
0 / 38 |
|||
| getStudentInternalMarkEditLog | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 18 |
|||
| getOeExamMarkConfirmedStudentByExam | |
0.00% |
0 / 1 |
30.00 | |
0.00% |
0 / 35 |
|||
| removeConfirmedStatusOfOeExamMarkConfirmedStudent | |
0.00% |
0 / 1 |
42.00 | |
0.00% |
0 / 28 |
|||
| getExamMarkFinalizedOeStudentsMarkDetailsBySubject | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 32 |
|||
| getThirdValuationStudentCount | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 16 |
|||
| getNormaliseMarks | |
0.00% |
0 / 1 |
72.00 | |
0.00% |
0 / 16 |
|||
| getNormaliseMarksRules | |
0.00% |
0 / 1 |
42.00 | |
0.00% |
0 / 14 |
|||
| deleteStudentExamMarkAndUpdateAttendance | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 19 |
|||
| getStudentRvStatusPg | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 11 |
|||
| resetStudentExamMarkAndUpdateAttendance | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 19 |
|||
| saveStudentExamMarkLog | |
0.00% |
0 / 1 |
42.00 | |
0.00% |
0 / 23 |
|||
| prepareLog | |
0.00% |
0 / 1 |
30.00 | |
0.00% |
0 / 16 |
|||
| getAuditCoursePassDetails | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 10 |
|||
| deleteAuditCoursePassDetails | |
0.00% |
0 / 1 |
30.00 | |
0.00% |
0 / 11 |
|||
| saveAuditCoursePassDetails | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 9 |
|||
| getEligibleExternalMarksForConversion | |
0.00% |
0 / 1 |
42.00 | |
0.00% |
0 / 28 |
|||
| convertExternalMarks | |
0.00% |
0 / 1 |
42.00 | |
0.00% |
0 / 30 |
|||
| checkExternalMarksSavedStatus | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 13 |
|||
| saveStudentSgpaCgpa | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 16 |
|||
| <?php | |
| namespace com\linways\core\ams\professional\service; | |
| use com\linways\base\util\SecurityUtils; | |
| use com\linways\core\ams\professional\dto\StudentExamMark; | |
| use com\linways\core\ams\professional\mapper\MarkServiceMapper; | |
| use com\linways\core\ams\professional\exception\ProfessionalException; | |
| use com\linways\core\ams\professional\request\CreateStudentMarkLogRequest; | |
| use com\linways\core\ams\professional\dto\examcontroller\exam\StudentMarkEditLog; | |
| use com\linways\core\ams\professional\dto\ExamType; | |
| class MarkService extends BaseService | |
| { | |
| // /Condition 1 - Presence of a static member variable | |
| private static $_instance = null; | |
| private $mapper = []; | |
| // /Condition 2 - Locked down the constructor | |
| private function __construct() | |
| { | |
| $this->mapper = MarkServiceMapper::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; | |
| } | |
| /** | |
| * To get count of backpapers of a student | |
| * @param int $studentId | |
| * @param int $courseTypeId | |
| * @throws ProfessionalException | |
| * @return unknown | |
| */ | |
| public function getBackpaperCount($studentId, $courseTypeId) | |
| { | |
| $studentId = $this->realEscapeString($studentId); | |
| $courseTypeId = $this->realEscapeString($courseTypeId); | |
| $sql = "select sum(CASE WHEN (gradeObtained in (select gradeID from university_gradepoints where gradePoint = 0 and typeID = $courseTypeId)) THEN 1 else 0 END) AS backpapers | |
| from university_studentgrade where studentID = $studentId"; | |
| try { | |
| $backpapers = $this->executeQueryForObject($sql)->backpapers; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); | |
| } | |
| return $backpapers; | |
| } | |
| /** | |
| * To get count of backhistories of a student | |
| * @param int $studentId | |
| * @param int $courseTypeId | |
| * @throws ProfessionalException | |
| * @return unknown | |
| */ | |
| public function getBacklogCount($studentId, $courseTypeId) | |
| { | |
| $studentId = $this->realEscapeString($studentId); | |
| $courseTypeId = $this->realEscapeString($courseTypeId); | |
| $sql = "select sum(case when (((noOfChances) > 1) || ((noOfChances = 1) && (gradeObtained in (select gradeID from university_gradepoints where gradePoint = 0 and typeID = $courseTypeId) ))) then 1 else 0 end) as backhistory from university_studentgrade where studentID = $studentId"; | |
| try { | |
| $backhistory = $this->executeQueryForObject($sql)->backhistory; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); | |
| } | |
| return $backhistory; | |
| } | |
| /** | |
| * To get the cgpa of a student | |
| * @param unknown $studentId | |
| * @param unknown $batchId | |
| * @throws ProfessionalException | |
| * @return number | |
| */ | |
| public function getStudentCgpa($studentId, $batchId) | |
| { | |
| $studentId = $this->realEscapeString($studentId); | |
| $batchId = $this->realEscapeString($batchId); | |
| $sql_cg = "select sum(credit*gradePoint) as sum from university_studentgrade us left join university_gradepoints ug on ug.gradeID = us.gradeObtained left join university_examcredits uc on uc.subjectID = us.subjectID where studentID = $studentId and batchID = $batchId"; | |
| if($this->checkIfFailedSubjCreditConsidered($batchId)) | |
| { | |
| $sql_credit = "select sum(credit) as sum from university_studentgrade us left join university_examcredits ue on ue.subjectID= us.subjectID where batchID = $batchId and studentID=$studentId"; | |
| } | |
| else | |
| { | |
| $sql_credit = "select sum(case when ugp.gradePoint !=0 then credit else 0 end) as sum from university_studentgrade us left join university_examcredits ue on ue.subjectID= us.subjectID left join university_gradepoints ugp on ugp.gradeID = us.gradeObtained where batchID = $batchId and studentID=$studentId"; | |
| } | |
| try { | |
| $cgs = $this->executeQueryForObject($sql_cg)->sum; | |
| $credit = $this->executeQueryForObject($sql_credit)->sum; | |
| if($credit != 0) | |
| { | |
| $cgpa = $cgs/$credit; | |
| } | |
| else | |
| { | |
| $cgpa = 0; | |
| } | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); | |
| } | |
| return $cgpa; | |
| } | |
| /** | |
| * Insert student university exam details | |
| * @param StudentUniversityExamGradewise $studentGrade | |
| * @throws ProfessionalException | |
| * @return \com\linways\base\connection\Object | |
| */ | |
| public function createStudentUniversityGrade($studentGrade) | |
| { | |
| $universityGrade = $studentGrade->universityExamGrade[0]; | |
| $studentId = $this->realEscapeString($studentGrade->studentId); | |
| $examId = $this->realEscapeString($universityGrade->examId); | |
| $subjectId = $this->realEscapeString($universityGrade->subjectId); | |
| $grades = $this->realEscapeString($universityGrade->gradeObtained); | |
| $noOfChances = $this->realEscapeString($universityGrade->noOfChances); | |
| $passType = $this->realEscapeString($universityGrade->passType); | |
| $semId = $this->realEscapeString($universityGrade->semId); | |
| $sql = "insert into university_studentgrade (examID, subjectID, gradeObtained, noOfChances, passType, semID, studentID) values ($examId,$subjectId, $grades, $noOfChances, '".$passType."', $semId, $studentId)"; | |
| try { | |
| return $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); | |
| } | |
| } | |
| /** | |
| * Update student university exam details | |
| * @param unknown $studentGrade | |
| * @throws ProfessionalException | |
| * @return \com\linways\base\connection\Object | |
| */ | |
| public function updateStudentUniversityGrade($studentGrade) | |
| { | |
| $universityGrade = $studentGrade->universityExamGrade[0]; | |
| $examId = $this->realEscapeString($universityGrade->examId); | |
| $grades = $this->realEscapeString($universityGrade->gradeObtained); | |
| $noOfChances = $this->realEscapeString($universityGrade->noOfChances); | |
| $passType = $this->realEscapeString($universityGrade->passType); | |
| $studentId = $this->realEscapeString($studentGrade->studentId); | |
| $sql = "update university_studentgrade set gradeObtained = $grades, noOfChances = $noOfChances, passType = '".$passType."' where examID = $examId and studentID = $studentId"; | |
| try { | |
| return $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); | |
| } | |
| } | |
| /** | |
| * remove student university mark by examId and studentId | |
| * @param int $examId | |
| * @param int $studentId | |
| * @throws ProfessionalException | |
| * @return null | |
| */ | |
| public function removeStudentUniversityGrade($examId, $studentId) | |
| { | |
| $sql="DELETE FROM university_studentgrade WHERE examID=$examId and studentID=$studentId"; | |
| try { | |
| return $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); | |
| } | |
| } | |
| /** | |
| * Get total credits of subjects by sem | |
| * @param int $batchId | |
| * @param int $semId | |
| * @throws ProfessionalException | |
| * @return unknown | |
| */ | |
| public function getTotalCreditsBySem($batchId, $semId, $studentId) | |
| { | |
| $semId = $this->realEscapeString($semId); | |
| $batchId = $this->realEscapeString($batchId); | |
| $studentId = $this->realEscapeString($studentId); | |
| if($this->checkIfFailedSubjCreditConsidered($batchId)) | |
| { | |
| $sql = "select sum(credit) as sum from university_studentgrade us left join university_examcredits ue on ue.subjectID= us.subjectID where batchID = $batchId and studentID=$studentId and us.semID = $semId"; | |
| } | |
| else | |
| { | |
| $sql = "select sum(case when ugp.gradePoint !=0 then credit else 0 end) as sum from university_studentgrade us left join university_examcredits ue on ue.subjectID= us.subjectID left join university_gradepoints ugp on ugp.gradeID = us.gradeObtained where batchID = $batchId and studentID=$studentId and us.semID = $semId"; | |
| } | |
| try { | |
| $total_credits = $this->executeQueryForObject($sql)->sum; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); | |
| } | |
| return $total_credits; | |
| } | |
| /** | |
| * create student sgpa_cgpa | |
| * @param int $semId | |
| * @param int $studentId | |
| * @param int $sgpa | |
| * @param int $cgpa | |
| * @param int $total_credit | |
| * @throws ProfessionalException | |
| * @return \com\linways\base\connection\Object | |
| */ | |
| public function createStudentSgpaCgpa($semId, $studentId, $sgpa, $cgpa, $total_credit, $total_marks = 0, $failed_subjects = 0) | |
| { | |
| $semId = $this->realEscapeString($semId); | |
| $studentId = $this->realEscapeString($studentId); | |
| $sgpa = $this->realEscapeString($sgpa); | |
| $cgpa = $this->realEscapeString($cgpa); | |
| $total_credit = $this->realEscapeString($total_credit); | |
| $failed_subjects = $this->realEscapeString($failed_subjects); | |
| $sql = "insert into sgpa_cgpa (semID, studentID, sgpa, cgpa, total_credit, total_marks, failed_subjects) values ($semId, $studentId, $sgpa, $cgpa, '$total_credit', $total_marks, $failed_subjects)"; | |
| try { | |
| return $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); | |
| } | |
| } | |
| /** | |
| * update student sgpa_cgpa | |
| * @param int $semId | |
| * @param int $studentId | |
| * @param int $sgpa | |
| * @param int $cgpa | |
| * @param int $total_credit | |
| * @throws ProfessionalException | |
| * @return \com\linways\base\connection\Object | |
| */ | |
| public function updateStudentSgpaCgpa($semId, $studentId, $sgpa, $cgpa, $total_credit, $total_marks = 0, $failed_subjects = 0) | |
| { | |
| $semId = $this->realEscapeString($semId); | |
| $studentId = $this->realEscapeString($studentId); | |
| $sgpa = $this->realEscapeString($sgpa); | |
| $cgpa = $this->realEscapeString($cgpa); | |
| $total_credit = $this->realEscapeString($total_credit); | |
| $failed_subjects = $this->realEscapeString($failed_subjects); | |
| $sql = "update sgpa_cgpa set sgpa = $sgpa, cgpa = $cgpa, total_credit = '$total_credit',failed_subjects = $failed_subjects,total_marks = $total_marks where semID = $semId and studentID = $studentId"; | |
| try { | |
| return $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); | |
| } | |
| } | |
| /** | |
| * Get student sgpa | |
| * @param int $batchId | |
| * @param int $semId | |
| * @param int $studentId | |
| * @throws ProfessionalException | |
| * @return int | |
| */ | |
| public function getStudentSgpa($batchId, $semId, $studentId) | |
| { | |
| $semId = $this->realEscapeString($semId); | |
| $studentId = $this->realEscapeString($studentId); | |
| $batchId = $this->realEscapeString($batchId); | |
| if($this->checkIfFailedSubjCreditConsidered($batchId)) | |
| { | |
| $sql = "select sum(credit * gradePoint)/sum(credit) as sgpa from university_studentgrade us left join university_examcredits ue on ue.subjectID= us.subjectID left join university_gradepoints ug on ug.gradeID = us.gradeObtained where batchID = $batchId and studentID=$studentId and us.semID = $semId"; | |
| } | |
| else | |
| { | |
| $sql = "select sum(credit * gradePoint)/sum(case when ug.gradePoint !=0 then credit else 0 end) as sgpa from university_studentgrade us left join university_examcredits ue on ue.subjectID= us.subjectID left join university_gradepoints ug on ug.gradeID = us.gradeObtained where batchID = $batchId and studentID=$studentId and us.semID = $semId"; | |
| } | |
| try { | |
| $sgpa = $this->executeQueryForObject($sql)->sgpa; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); | |
| } | |
| return $sgpa; | |
| } | |
| /** | |
| * | |
| * @param int examId | |
| * @param int $studentId | |
| * @throws ProfessionalException | |
| * @return boolean | |
| */ | |
| public function isStudentUniversityGradeEntered($examId, $studentId) | |
| { | |
| $semId = $this->realEscapeString($semId); | |
| $studentId = $this->realEscapeString($studentId); | |
| $sql = "select examID from university_studentgrade where studentID = $studentId and examID = $examId"; | |
| try { | |
| $examId = $this->executeQueryForObject($sql)->examID; | |
| if($examId) | |
| { | |
| return true; | |
| } | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); | |
| } | |
| return false; | |
| } | |
| /** | |
| * Get exam mark of a student by examId | |
| * @param int $studentId | |
| * @param int $examId | |
| * @throws ProfessionalException | |
| * @return object|NULL|\com\linways\base\util\$objectList[] | |
| */ | |
| public function getStudentExamMarkDetailsByExamId($studentId,$examId) | |
| { | |
| $studentId = $this->realEscapeString($studentId); | |
| $examId = $this->realEscapeString($examId); | |
| $sql = "select examID, marksObtained, percentage, examTypeID from student_marks where studentID = $studentId and examID = $examId"; | |
| try { | |
| $examMark = $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); | |
| } | |
| return $examMark; | |
| } | |
| /** | |
| * update student exam mark | |
| * @param StudentExamMark $studentExamMark | |
| * @throws ProfessionalException | |
| * @return \com\linways\base\dto\MySqlResult | |
| */ | |
| public function updateStudentExamMark($studentExamMark) | |
| { | |
| $studentExamMark = $this->realEscapeObject($studentExamMark); | |
| $sql = "update student_marks set marksObtained = $studentExamMark->marksObtained, percentage = $studentExamMark->percentage where studentID = $studentExamMark->studentId and examID = $studentExamMark->examId"; | |
| try { | |
| return $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); | |
| } | |
| } | |
| /** | |
| * create student exam mark | |
| * @param StudentExamMark $studentExamMark | |
| * @throws ProfessionalException | |
| * @return \com\linways\base\dto\MySqlResult | |
| */ | |
| public function createStudentExamMark($studentExamMark) | |
| { | |
| $studentExamMark = $this->realEscapeObject($studentExamMark); | |
| $sql = "insert into student_marks (batchID, studentID, examID, marksObtained, subjectID, staffID, percentage, semID, examTypeID) values ($studentExamMark->batchId, $studentExamMark->studentId, $studentExamMark->examId, $studentExamMark->marksObtained, $studentExamMark->subjectId, $studentExamMark->staffId, '$studentExamMark->percentage', $studentExamMark->semId, $studentExamMark->examTypeId)"; | |
| try { | |
| return $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); | |
| } | |
| } | |
| /** | |
| * Check if normalised marks is approved or not. | |
| * @param int $batchId | |
| * @param int $semId | |
| * @param int $subjectId | |
| * @return boolean | |
| */ | |
| public function isNormalisedMarksApproved($batchId, $semId, $subjectId) | |
| { | |
| $batchId = $this->realEscapeString($batchId); | |
| $semId = $this->realEscapeString($semId); | |
| $subjectId = $this->realEscapeString($subjectId); | |
| $sql = "SELECT isAproved FROM aprove_normalise_mark WHERE batchID = $batchId AND semID = $semId AND subjectID = $subjectId"; | |
| try { | |
| return $this->executeQueryForObject($sql)->isAproved; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); | |
| } | |
| } | |
| /** | |
| * Check if normalised marks is published or not. | |
| * @param int $batchId | |
| * @param int $semId | |
| * @param int $subjectId | |
| * @throws ProfessionalException | |
| * @return \com\linways\base\dto\MySqlResult | |
| */ | |
| public function isNormalisedMarksPublished($batchId, $semId, $subjectId) | |
| { | |
| $batchId = $this->realEscapeString($batchId); | |
| $semId = $this->realEscapeString($semId); | |
| $subjectId = $this->realEscapeString($subjectId); | |
| $sql = "SELECT isPublished FROM aprove_normalise_mark WHERE batchID = $batchId AND semID = $semId AND subjectID = $subjectId"; | |
| try { | |
| return $this->executeQueryForObject($sql)->isPublished; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); | |
| } | |
| } | |
| public function changeNormalisedMarksApprovalStatus($batchId, $semId, $subjectId, $staffId, $isApproved = NULL, $isPublished = NULL) | |
| { | |
| $batchId = $this->realEscapeString($batchId); | |
| $semId = $this->realEscapeString($semId); | |
| $subjectId = $this->realEscapeString($subjectId); | |
| $staffId = $this->realEscapeString($staffId); | |
| $isApproved = $this->realEscapeString($isApproved); | |
| $isPublished = $this->realEscapeString($isPublished); | |
| $isApprovedCond = NULL; | |
| $isPublishedCond = NULL; | |
| $sqlCheck = "SELECT isAproved FROM aprove_normalise_mark WHERE batchID = $batchId AND semID = $semId AND subjectID = $subjectId"; | |
| try { | |
| //If already a record exists | |
| $currentStatus = $this->executeQueryForObject($sqlCheck)->isAproved; | |
| if($currentStatus != NULL) | |
| { | |
| if($isApproved != NULL) | |
| { | |
| $isApprovedCond = " isAproved = $isApproved"; | |
| } | |
| if($isPublished != NULL && $currentStatus == 1) | |
| { | |
| if($isApproved != NULL) | |
| { | |
| $isApprovedCond .= ", "; | |
| } | |
| $isPublishedCond = " isPublished = $isPublished"; | |
| } | |
| $sql = "UPDATE aprove_normalise_mark SET $isApprovedCond $isPublishedCond , updatedDate = utc_timestamp(), updatedBy=$staffId WHERE batchID = $batchId AND semID = $semId AND subjectID = $subjectId"; | |
| if($isApprovedCond != NULL || $isPublishedCond != NULL || $isApproved == 1) | |
| { | |
| $this->executeQuery($sql); | |
| } | |
| } | |
| else if($isApproved == 1) | |
| { | |
| $sql="select p.pseudosubjectID as pseudosubjectID from pseudosubjects_sbs p inner join sbs_relation s on s.sbsID = p.sbsID where batchID='$batchId' and semID='$semId' and s.subjectID='$subjectId';"; | |
| $pseudosubjects=$this->executeQueryForList($sql); | |
| if(!empty($pseudosubjects)){ | |
| foreach($pseudosubjects as $pseudosubject){ | |
| $pseudosubjectID=!empty($pseudosubject->pseudosubjectID)?$pseudosubject->pseudosubjectID:"NULL"; | |
| $sql = "INSERT INTO aprove_normalise_mark(batchID, semID, isAproved, subjectID, staffID, createdBy, createdDate, updatedBy, updatedDate, pseudosubjectID) VALUES('$batchId', '$semId', '$isApproved', '$subjectId', '$staffId', '$staffId', utc_timestamp(), $staffId, utc_timestamp(), $pseudosubjectID)"; | |
| if($isApprovedCond != NULL || $isPublishedCond != NULL || $isApproved == 1) | |
| { | |
| $this->executeQuery($sql); | |
| } | |
| } | |
| } | |
| else{ | |
| $sql = "INSERT INTO aprove_normalise_mark(batchID, semID, isAproved, subjectID, staffID, createdBy, createdDate, updatedBy, updatedDate, pseudosubjectID) VALUES('$batchId', '$semId', '$isApproved', '$subjectId', '$staffId', '$staffId', utc_timestamp(), $staffId, utc_timestamp(), NULL)"; | |
| if($isApprovedCond != NULL || $isPublishedCond != NULL || $isApproved == 1) | |
| { | |
| $this->executeQuery($sql); | |
| } | |
| } | |
| } | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); | |
| } | |
| } | |
| /** | |
| * Get student subject cgpa in marks scheme | |
| * @param int $batchId | |
| * @param int $semId | |
| * @param int $totalPercentage | |
| * @param int $subjectId | |
| * @throws ProfessionalException | |
| * @return unknown | |
| */ | |
| public function getStudentSubjectMarksCgpa($batchId, $semId, $totalPercentage, $subjectId) | |
| { | |
| $batchId = $this->realEscapeString($batchId); | |
| $semId = $this->realEscapeString($semId); | |
| $totalPercentage = $this->realEscapeString($totalPercentage); | |
| $subjectId = $this->realEscapeString($subjectId); | |
| $sql = "SELECT credit * (SELECT ug.gradePoint FROM university_gradepoints ug INNER JOIN university_assignbatchcourse uab on uab.typeID = ug.typeID WHERE ug.percentFrom <= $totalPercentage AND ug.percentTo >= $totalPercentage AND uab.batchID=$batchId) as subjectCredit FROM university_examcredits WHERE batchID = $batchId AND semID = $semId AND subjectID = $subjectId"; | |
| try { | |
| $cgpa = $this->executeQueryForObject($sql)->subjectCredit; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); | |
| } | |
| return $cgpa; | |
| } | |
| /** | |
| * Get student cgpa by markScheme | |
| * @param int $batchId | |
| * @param int $semId | |
| * @param int $studentId | |
| * @throws ProfessionalException | |
| */ | |
| public function getStudentMarksCgpa($batchId, $semId, $studentId) | |
| { | |
| $batchId = $this->realEscapeString($batchId); | |
| $semId = $this->realEscapeString($semId); | |
| $studentId = $this->realEscapeString($studentId); | |
| $sql = "select sum(credit) as creditSum from university_examcredits where batchId = $batchId and semID = $semId"; | |
| $sql_credit = "select sum(cg)as totalCredit from universityMarks where studentId = $studentId and semID = $semId"; | |
| try { | |
| $creditSum = $this->executeQueryForObject($sql)->creditSum; | |
| $totalCredit = $this->executeQueryForObject($sql)->totalCredit; | |
| $cgpa = $totalCredit == 0 ? 0 : ($creditSum/$totalCredit); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); | |
| } | |
| return $cgpa; | |
| } | |
| /** | |
| * Get student total marks | |
| * @param int $studentId | |
| * @param int $semId | |
| * @throws ProfessionalException | |
| * @return unknown | |
| */ | |
| public function getStudentUniversityExamTotalMarks($studentId, $semId) | |
| { | |
| $semId = $this->realEscapeString($semId); | |
| $studentId = $this->realEscapeString($studentId); | |
| $sql = "select sum(if(internalMark, internalMark, 0)) + sum(if(marksObtained, marksObtained, 0)) as totalMarks from universityMarks where studentID = $studentId and semID = $semId"; | |
| try { | |
| $totalMarks = $this->executeQueryForObject($sql)->totalMarks; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); | |
| } | |
| return $totalMarks; | |
| } | |
| /** | |
| * Get student sgps_cgpa | |
| * @param int $studentId | |
| * @param int $semId | |
| * @throws ProfessionalException | |
| * @return unknown | |
| */ | |
| public function getStudentSgpaCpga($studentId, $semId) | |
| { | |
| $semId = $this->realEscapeString($semId); | |
| $studentId = $this->realEscapeString($studentId); | |
| $sql = "select failed_subjects, total_marks, sgpa, cgpa, total_credit from sgpa_cgpa where studentID = $studentId and semID = $semId"; | |
| try { | |
| $sgpa_cgpa = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); | |
| } | |
| return $sgpa_cgpa; | |
| } | |
| /** | |
| * Check if failed subject credit should be considered | |
| * when calculating sgpa and cgpa by batchId | |
| * returns true if failed subj considered | |
| * @param int $batchId | |
| * @throws ProfessionalException | |
| * @return boolean | |
| */ | |
| public function checkIfFailedSubjCreditConsidered($batchId) | |
| { | |
| $batchId = $this->realEscapeString($batchId); | |
| $sql = "select considerFailedSubjectCredit from university uni inner join batches ba on ba.universityId = uni.id where batchID = $batchId and considerFailedSubjectCredit = 0"; | |
| try { | |
| $considerFailedSubjectCredit = $this->executeQueryForObject($sql); | |
| if($considerFailedSubjectCredit) | |
| return false; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); | |
| } | |
| return true; | |
| } | |
| /** | |
| * | |
| * @param int $examRegId | |
| * @param int $batchId | |
| * @param int $semId | |
| * @param int $studentId | |
| * @throws ProfessionalException | |
| * @return array StudentExamSubjectMark | |
| * | |
| */ | |
| public function getMarkListRegularDiplomaIndividual($examRegId, $batchId, $semId, $studentId) | |
| { | |
| $studentMarkList = []; | |
| $sql = "SELECT DISTINCT e.subjectID as subjectId, | |
| s.subjectName, | |
| s.subjectDesc as subjectDescription, | |
| e.examID as examId, | |
| im.internalMarks, | |
| ee.mark as externalMarks, | |
| ( (case when im.internalMarks is null then 0 else im.internalMarks end) + (case when ee.mark is null then 0 else ee.mark end)) as totalMarks, | |
| ((case when imss.maxInternalMarks is null then 0 else imss.maxInternalMarks end) + e.examTotalMarks) as examTotalMaxMarks , | |
| round( | |
| ((( (case when im.internalMarks is null then 0 else im.internalMarks end) + (case when ee.mark is null then 0 else ee.mark end)) / ((case when imss.maxInternalMarks is null then 0 else imss.maxInternalMarks end) + e.examTotalMarks)) * 100),2) as percentage, | |
| sa.studentID as studentId, | |
| sa.studentName, | |
| e.examregID as examRegId, | |
| ea.isAbsent, | |
| mgsd.id as subjectGroupId, | |
| mgsd.subjectTitle as subjectGroupName, | |
| mgsd.subjectCode as subjectGroupCode, | |
| (case when mgs.id is null then 0 else 1 end) as isGroupedSubject, | |
| (case when mgs.id is null then 'THEORY' else (case when mgs.isPractical = 1 then 'LAB' else 'THEORY' end) end) as subjectType | |
| from exam e | |
| INNER JOIN subjects s on s.subjectID = e.subjectID | |
| INNER JOIN exam_reg_studentsubject erss on erss.examregID = e.examregID and erss.subjectID = e.subjectID | |
| LEFT JOIN exam_reg_studentchallan ersc on ersc.examregID = e.examregID | |
| LEFT JOIN internal_marks_submitted ims on ims.batchID = e.batchID and ims.semID = e.semID and | |
| ims.subjectID = e.subjectID | |
| LEFT JOIN internal_marks im on im.batchID = ims.batchID and im.semID = ims.semID and | |
| im.subjectID = ims.subjectID and im.studentID = ersc.studentID | |
| LEFT JOIN internal_marks_settings imss on imss.batchID = e.batchID and imss.semID = e.semID and | |
| imss.subjectID = e.subjectID | |
| LEFT JOIN exammarks_external ee on e.examID = ee.examID and ersc.studentID = ee.studentID | |
| LEFT JOIN studentaccount sa on ersc.studentID = sa.studentID | |
| LEFT JOIN exam_attendance ea on ea.examID = e.examID and ea.studentID = sa.studentID | |
| LEFT JOIN marklist_group_subjects mgs on mgs.subjects_id = s.subjectID | |
| LEFT JOIN marklist_group_subjects_details mgsd on mgsd.id = mgs.marklist_group_subjects_details_id | |
| WHERE e.examregID = $examRegId and e.batchID = $batchId and e.semID = $semId and ersc.paid = 1 | |
| AND sa.studentId = $studentId order by s.subjectName; | |
| "; | |
| try { | |
| $studentMarkList = $this->executeQueryForList($sql, $this->mapper[MarkServiceMapper::GET_MARK_LIST_REGULAR_DIPLOMA]); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| // $studentExamMark = MarkListUtil::splitSubjectMarksByGroup($studentMarkList); | |
| return $studentMarkList; | |
| } | |
| /** | |
| * Method for getting course grade of a courseType | |
| * | |
| * @param int $percentage | |
| * @param int $courseTypeId | |
| * @return CourseGrade $courseGrade | |
| */ | |
| public function getCourseGrade($percentage, $courseTypeId) { | |
| $courseGrade= null; | |
| $sql = "SELECT eg.gradePointID as gradePointId, | |
| eg.schemeID as schemeId, | |
| eg.percentFrom, | |
| eg.percentTo, | |
| eg.lettergrade as letterGrade, | |
| eg.gradePoint, | |
| eg.failstatus as failStatus, | |
| egs.courseTypeID as courseTypeId FROM exam_gradepoints | |
| eg INNER JOIN exam_gradingscheme egs on egs.schemeID = eg.schemeID | |
| WHERE egs.courseTypeID = $courseTypeId | |
| AND (eg.percentFrom <= $percentage and eg.percentTo >= $percentage);" ; | |
| try { | |
| $courseGrade = $this->executeQueryForObject ( $sql ); | |
| } catch ( \Exception $e ) { | |
| throw new ProfessionalException( $e->getCode (), $e->getMessage () ); | |
| } | |
| return $courseGrade; | |
| } | |
| /** | |
| * Get student grace mark for PG | |
| * @param int $examId | |
| * @param int $studentId | |
| * @param int $subjectId | |
| * @throws ProfessionalException | |
| * @return unknown | |
| */ | |
| public function getStudentGraceMarkPG($examId,$studentId,$subjectId) | |
| { | |
| $sql = ''; | |
| $examId = $this->realEscapeObject($examId); | |
| $studentId = $this->realEscapeObject($studentId); | |
| $subjectId = $this->realEscapeObject($subjectId); | |
| $studentGraceMark = null; | |
| try{ | |
| $sql = "SELECT gsm.adminUpdatedMarks AS correctMark FROM gracemarks_student_marks gsm WHERE gsm.studentID = $studentId AND examID = $examId AND subjectID = $subjectId AND gsm.approve = 1 AND gsm.fianlize = 1"; | |
| $studentGraceMark = $this->executeQueryForObject($sql)->correctMark; | |
| }catch (\Exception $e){ | |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); | |
| } | |
| return $studentGraceMark; | |
| } | |
| /** | |
| * Get student grace mark | |
| * @param int $examId | |
| * @param int $studentId | |
| * @param int $subjectId | |
| * @throws ProfessionalException | |
| * @return unknown | |
| */ | |
| public function getStudentGraceMark($examId,$studentId,$subjectId) | |
| { | |
| $sql = ''; | |
| $examId = $this->realEscapeObject($examId); | |
| $studentId = $this->realEscapeObject($studentId); | |
| $subjectId = $this->realEscapeObject($subjectId); | |
| $studentGraceMark = null; | |
| try{ | |
| $sql = "SELECT gsm.marks AS studentGraceMark FROM gracemarks_student_marks gsm WHERE gsm.studentID = $studentId AND examID = $examId AND subjectID = $subjectId AND gsm.approve = 1 AND gsm.fianlize = 1"; | |
| $studentGraceMark = $this->executeQueryForObject($sql)->studentGraceMark; | |
| }catch (\Exception $e){ | |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); | |
| } | |
| return $studentGraceMark; | |
| } | |
| /** | |
| * Get class average by examId | |
| * @param int $examId | |
| * @throws ProfessionalException | |
| * @return unknown | |
| */ | |
| public function getClassAvgByExamId($examId, $considerAbsentees =false) | |
| { | |
| $examId = $this->realEscapeObject($examId); | |
| $considerAbsentees = $this->realEscapeString($considerAbsentees); | |
| if($considerAbsentees) | |
| { | |
| $sqlCond = " sum(if(sm.marksObtained != '-0.001' AND sm.marksObtained !='-1',sm.marksObtained,0))/ count(if(sm.marksObtained != '-.001' ,sm.studentId,0)) "; | |
| } | |
| else | |
| { | |
| $sqlCond = " sum(if(sm.marksObtained != '-0.001' AND sm.marksObtained !='-1',sm.marksObtained,0))/ count(if(sm.marksObtained != '-.001' AND sm.marksObtained !='-1',sm.studentId,0)) "; | |
| } | |
| $sql = "select ".$sqlCond." as totalObtained from student_marks sm inner join exam ex on sm.examID = ex.examID inner join studentaccount sa on sa.studentID = sm.studentID and sa.batchID = sm.batchID where ex.examID = ".$examId.""; | |
| try { | |
| return $this->executeQueryForObject($sql)->totalObtained; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * Get total marks of a student by exam type | |
| * @param int $examTypeId | |
| * @param int $batchId | |
| * @param int $semId | |
| * @param int $studentId | |
| * @throws ProfessionalException | |
| * @return unknown | |
| */ | |
| public function getTotalMarksOfAStudentByExamType($examTypeId, $batchId, $semId, $studentId) | |
| { | |
| $examTypeId = $this->realEscapeString($examTypeId); | |
| $batchId = $this->realEscapeString($batchId); | |
| $semId = $this->realEscapeString($semId); | |
| $studentId = $this->realEscapeString($studentId); | |
| $sql = "select sum(if(sm.marksObtained != '-0.001' AND sm.marksObtained !='-1',sm.marksObtained,0)) as total from student_marks sm where examTypeID = ".$examTypeId." and studentID = ".$studentId." and batchID = ".$batchId." and semID = ".$semId.""; | |
| try { | |
| return $this->executeQueryForObject($sql)->total; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * To get student's internal added mark | |
| * @param int $studentID | |
| * @param int $staffID | |
| * @param int $batchID | |
| * @param int $subjectID | |
| */ | |
| public function getStudentInternalAddedMark($studentID,$staffID,$batchID,$subjectID) | |
| { | |
| $studentID = $this->realEscapeString($studentID); | |
| $staffID = $this->realEscapeString($staffID); | |
| $batchID = $this->realEscapeString($batchID); | |
| $subjectID = $this->realEscapeString($subjectID); | |
| $sql="SELECT markID, marksObtained, normalisedMark, percentage FROM normalise_marks WHERE batchID = '$batchID' AND studentID = '$studentID' AND subjectID = '$subjectID' AND staffID = '$staffID'"; | |
| try | |
| { | |
| $result = $this->executeQueryForObject($sql); | |
| $result->grace = $result->normalisedMark-$result->marksObtained; | |
| $result->total = $result->normalisedMark; | |
| return $result; | |
| } | |
| catch(\Exception $e) | |
| { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * To check wether batch internal mark finalised or not | |
| * @param int $batchID | |
| * @param int $subjectID | |
| * @param int $semID | |
| * @param int $subbatchID | |
| */ | |
| public function hasInternalMarkFinalised($batchID,$subjectID,$semID,$subbatchID) | |
| { | |
| if($subbatchID) | |
| { | |
| $sql = "SELECT n.normalisedMark ,n.studentID FROM normalise_marks n | |
| INNER JOIN subbatch_student s ON n.studentID = s.studentID | |
| WHERE n.batchID='$batchID' AND n.subjectID='$subjectID' AND n.semID='$semID' AND subbatchID='$subbatchID' GROUP BY n.studentID LIMIT 1;"; | |
| } | |
| else | |
| { | |
| $sql="SELECT normalisedMark FROM normalise_marks WHERE batchID='$batchID' AND subjectID='$subjectID' AND semID='$semID';"; | |
| } | |
| try | |
| { | |
| $result = $this->executeQueryForObject($sql); | |
| if ($result) | |
| return true; | |
| else | |
| return false; | |
| } | |
| catch(\Exception $e) | |
| { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * To get student's normalized mark | |
| * @param int $studentId | |
| * @param int $batchId | |
| * @param int $semId | |
| * @param int $subjectId | |
| */ | |
| public function getStudentNormalizedMark($studentId, $batchId, $semId, $subjectId) { | |
| $studentId = $this->realEscapeString($studentId); | |
| $batchId = $this->realEscapeString($batchId); | |
| $semId = $this->realEscapeString($semId); | |
| $subjectId = $this->realEscapeString($subjectId); | |
| $result = NULL; | |
| $sql = "SELECT markID, subjectID, batchID, semID, studentID, marksObtained, normalisedMark, percentage FROM normalise_marks WHERE batchID = '$batchId' AND studentID = '$studentId' AND subjectID IN ($subjectId) AND semID = '$semId' "; | |
| try { | |
| $results = $this->executeQueryForList($sql); | |
| foreach ($results as $result ) { | |
| $result->grace = $result->normalisedMark - $result->marksObtained; | |
| $result->total = $result->normalisedMark; | |
| } | |
| return $results; | |
| } | |
| catch(\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $result; | |
| } | |
| /** | |
| * create student exam mark | |
| * @param StudentExamMark $studentExamMark | |
| * @throws ProfessionalException | |
| * @return \com\linways\base\dto\MySqlResult | |
| */ | |
| public function getStudentExamMark($studentExamMark) | |
| { | |
| $studentExamMark = $this->realEscapeObject($studentExamMark); | |
| $sql = "SELECT markID, marksObtained | |
| FROM student_marks | |
| WHERE | |
| batchID = $studentExamMark->batchId AND | |
| studentID = $studentExamMark->studentId AND | |
| examID = $studentExamMark->examId AND | |
| subjectID = $studentExamMark->subjectId AND | |
| semID = $studentExamMark->semId AND | |
| examTypeID = $studentExamMark->examTypeId"; | |
| try { | |
| return $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); | |
| } | |
| } | |
| /** | |
| * update student exam marks for subject in exam of semester of batch | |
| * @param StudentExamMark $studentExamMark | |
| * @throws ProfessionalException | |
| * @return \com\linways\base\dto\MySqlResult | |
| */ | |
| public function updateStudentExamMarks($studentExamMark) | |
| { | |
| $studentExamMark = $this->realEscapeObject($studentExamMark); | |
| $sql = "UPDATE student_marks | |
| SET marksObtained = $studentExamMark->marksObtained, | |
| percentage = $studentExamMark->percentage | |
| WHERE | |
| batchID = $studentExamMark->batchId AND | |
| studentID = $studentExamMark->studentId AND | |
| examID = $studentExamMark->examId AND | |
| subjectID = $studentExamMark->subjectId AND | |
| semID = $studentExamMark->semId AND | |
| examTypeID = $studentExamMark->examTypeId"; | |
| try { | |
| return $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); | |
| } | |
| } | |
| /** | |
| * create student exam mark | |
| * @param StudentExamMark $studentExamMarkList | |
| * @throws ProfessionalException | |
| * @return \com\linways\base\dto\MySqlResult | |
| */ | |
| public function insertStudentExamMark($studentExamMarkList) | |
| { | |
| $studentExamMarkList = $this->realEscapeObject($studentExamMarkList); | |
| $valueStringList = NULL; | |
| $valueString = ""; | |
| foreach ($studentExamMarkList as $studentExamMark ) { | |
| $valueStringList[] = "($studentExamMark->batchId, $studentExamMark->studentId, $studentExamMark->examId, $studentExamMark->marksObtained, $studentExamMark->subjectId, $studentExamMark->staffId, '$studentExamMark->percentage', $studentExamMark->semId, $studentExamMark->examTypeId)"; | |
| } | |
| $valueString = implode(',',$valueStringList); | |
| $sql = "INSERT INTO | |
| student_marks (batchID, studentID, examID, marksObtained, subjectID, staffID, percentage, semID, examTypeID) | |
| VALUES | |
| $valueString | |
| ON DUPLICATE KEY UPDATE | |
| marksObtained = VALUES(marksObtained), percentage = VALUES(percentage)"; | |
| try { | |
| return $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); | |
| } | |
| } | |
| /** | |
| * Log student mark entry | |
| * Ignore if duplicate entry for student mark. | |
| */ | |
| public function logStudentMarks(CreateStudentMarkLogRequest $request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $query = "INSERT IGNORE INTO student_marks_log (studentID,examID,marksObtained,staffID,created_date) VALUES ('$request->studentId','$request->examId','$request->marksObtained','$request->createdBy',UTC_TIMESTAMP())"; | |
| try{ | |
| $this->executeQuery($query); | |
| }catch(\Exception $e) | |
| { | |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); | |
| } | |
| } | |
| /** | |
| * Enter staff valuated answer sheet count | |
| * | |
| */ | |
| public function insertStaffValuatedAnswerSheetCount($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $query = "INSERT INTO staff_exam_valuation_answer_sheet_count (assignstaff_exam_groupname_id, exam_id, staffaccounts_id, valuated_answer_sheet_count, created_by) VALUES (".($request->groupId ? $request->groupId : 'NULL').",".($request->examId ? $request->examId : 'NULL').",$request->staffId, $request->valuatedAnswerSheetCount, $request->createdBy)"; | |
| try{ | |
| $id = $this->executeQuery($query,true); | |
| }catch(\Exception $e) | |
| { | |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); | |
| } | |
| return $id; | |
| } | |
| /** | |
| * Enter staff valuated answer sheet count | |
| * | |
| */ | |
| public function updateStaffValuatedAnswerSheetCount($id, $valuatedAnswerSheetCount,$staffId) | |
| { | |
| $id = $this->realEscapeString($id); | |
| $valuatedAnswerSheetCount = $this->realEscapeString($valuatedAnswerSheetCount); | |
| $query = "UPDATE staff_exam_valuation_answer_sheet_count SET valuated_answer_sheet_count = $valuatedAnswerSheetCount,updated_by = '$staffId' WHERE id = $id"; | |
| try{ | |
| $this->executeQuery($query); | |
| }catch(\Exception $e) | |
| { | |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); | |
| } | |
| return; | |
| } | |
| /** | |
| * update is_confirm flag | |
| * | |
| */ | |
| public function setConfirmStaffValuatedAnswerSheetCount($id, $isConfirm = false,$staffId) | |
| { | |
| $id = $this->realEscapeString($id); | |
| $isConfirm = $this->realEscapeString($isConfirm); | |
| $isConfirmValue = 0; | |
| if ($isConfirm) { | |
| $isConfirmValue = 1; | |
| } | |
| $query = "UPDATE staff_exam_valuation_answer_sheet_count SET is_confirmed = '$isConfirmValue',updated_by = '$staffId' WHERE id = $id"; | |
| try{ | |
| $this->executeQuery($query); | |
| }catch(\Exception $e) | |
| { | |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); | |
| } | |
| return; | |
| } | |
| /** | |
| * get staff valuated answer sheet count | |
| * | |
| */ | |
| public function getStaffValuatedAnswerSheetCount($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $condition = ""; | |
| try{ | |
| if ($request->groupId) { | |
| $condition .= " AND assignstaff_exam_groupname_id = $request->groupId"; | |
| } | |
| else if ($request->examId) { | |
| $condition .= " AND exam_id = $request->examId"; | |
| } | |
| else{ | |
| throw new ProfessionalException(ProfessionalException::INVALID_REQUEST,'Either Group or Exam required'); | |
| } | |
| if (!$request->staffId) { | |
| throw new ProfessionalException(ProfessionalException::INVALID_REQUEST,'Staff can not be null'); | |
| } | |
| $query = "SELECT | |
| id, | |
| valuated_answer_sheet_count AS answerSheetCount | |
| FROM | |
| staff_exam_valuation_answer_sheet_count | |
| WHERE | |
| staffaccounts_id = '$request->staffId' | |
| $condition"; | |
| $valuatedAnswerSheetCountObj = $this->executeQueryForObject($query); | |
| }catch(\Exception $e) | |
| { | |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); | |
| } | |
| return $valuatedAnswerSheetCountObj; | |
| } | |
| /** | |
| * Update or add new exam mark | |
| * | |
| */ | |
| public function saveStudentExamMarkAndLog($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| try { | |
| $sql = "INSERT INTO externalexammarks_finalized (examID, studentID, mark) VALUES ('$request->examId','$request->studentId',$request->mark) ON DUPLICATE KEY UPDATE mark = VALUES(mark)"; | |
| $this->executeQuery($sql); | |
| $sql = "INSERT INTO exammarks_external (examID, studentID, mark, adminID) VALUES ('$request->examId','$request->studentId',$request->mark,$request->staffId) ON DUPLICATE KEY UPDATE mark = VALUES(mark)"; | |
| $this->executeQuery($sql); | |
| $sql_absent = "INSERT INTO exam_attendance (examID, studentID, isAbsent) VALUES ('$request->examId','$request->studentId',0) ON DUPLICATE KEY UPDATE isAbsent = VALUES(isAbsent)"; | |
| $this->executeQuery($sql_absent); | |
| $id = SecurityUtils::getRandomString(); | |
| $log = json_encode($request->log); | |
| $sql = "INSERT INTO ec_student_mark_edit_log (id,exam_id,student_id,log,created_by,created_date) | |
| VALUES ('$id',$request->examId,'$request->studentId','[$log]','$request->staffId',now()) ON DUPLICATE KEY UPDATE `log` = JSON_MERGE(`log`,'$log'), updated_by = VALUES(created_by)"; | |
| $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); | |
| } | |
| return true; | |
| } | |
| /** | |
| * Update or add new exam mark | |
| * | |
| */ | |
| public function getStudentExamMarkEditLog($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| try { | |
| $sql = "SELECT | |
| `log` | |
| FROM | |
| ec_student_mark_edit_log | |
| WHERE | |
| student_id = '$request->studentId' | |
| AND exam_id = '$request->examId'"; | |
| $log = $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); | |
| } | |
| return $log; | |
| } | |
| /** | |
| * fetch subject with student mark | |
| * | |
| */ | |
| public function getSubjectsWithStudentMark($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $condition = ""; | |
| if (empty($request->semId)) { | |
| throw new ProfessionalException(ProfessionalException::INVALID_PARAMETER,"Semester can not be null"); | |
| } | |
| else{ | |
| $semIdString = is_array($request->semId) ? implode(",",$request->semId) : $request->semId; | |
| $condition .= " AND e.semID IN ($semIdString) "; | |
| } | |
| if (empty($request->examRegId)) { | |
| throw new ProfessionalException(ProfessionalException::INVALID_PARAMETER,"Exam registration can not be null"); | |
| } | |
| else{ | |
| $examRegIdString = is_array($request->examRegId) ? implode(",",$request->examRegId) : $request->examRegId; | |
| if ($request->isSupply) { | |
| $condition .= " AND e.supply_examreg_id IN ($examRegIdString) "; | |
| } | |
| else{ | |
| $condition .= " AND e.examregID IN ($examRegIdString) "; | |
| } | |
| } | |
| if (!isset($request->isUg)) { | |
| throw new ProfessionalException(ProfessionalException::INVALID_PARAMETER,"Unable to conform batch course type"); | |
| } | |
| else{ | |
| $courseType = $request->isUg ? 'UG' : 'PG'; | |
| } | |
| if (empty($request->staffId)) { | |
| throw new ProfessionalException(ProfessionalException::INVALID_PARAMETER,"Staff can not be null"); | |
| } | |
| else{ | |
| $condition .= $request->isSupply ? ($request->staffSubjects ? "AND FIND_IN_SET ('$request->staffId',evs.staffIDs)" : "") : " AND FIND_IN_SET ('$request->staffId',evs.staffIDs) "; | |
| } | |
| if (!empty($request->batchId)) { | |
| $batchIdString = is_array($request->batchId) ? implode(",",$request->batchId) : $request->batchId; | |
| $condition .= " AND e.batchID IN ($batchIdString) "; | |
| } | |
| if (!empty($request->subjectId)) { | |
| $subjectIdString = is_array($request->subjectId) ? implode(",",$request->subjectId) : $request->subjectId; | |
| $condition .= " AND s.subjectID IN ($subjectIdString) "; | |
| } | |
| $staffSubjectsCondition=""; | |
| if($request->staffSubjects){ | |
| $staffSubjectsCondition = " INNER JOIN exam_valuation_staffs evs ON evs.examID = e.examID "; | |
| } | |
| try { | |
| $sqlRegular = "SELECT | |
| ersf.batchID AS batchId, | |
| s.subjectID AS subjectId, | |
| s.subjectName, | |
| s.syllabusName, | |
| s.subjectDesc, | |
| e.examID AS examId, | |
| e.examTotalMarks, | |
| sa.studentID AS studentId, | |
| sa.regNo, | |
| sa.studentName, | |
| sa.admissionNo, | |
| IF(vd.firstval_Dateend = '', null, vd.firstval_Dateend) AS firstValidationEndDate, | |
| IF(vd.firstval_Datestart = '', null, vd.firstval_Datestart) AS firstValidationStartDate, | |
| IF(vd.secondval_Dateend = '', null, vd.secondval_Dateend) AS secondValidationEndDate, | |
| IF(vd.secondval_Datestart = '', null, vd.secondval_Datestart) AS secondValidationStartDate, | |
| ecfn.false_number AS falseNumber, | |
| eem.mark, | |
| eem.valuationCount, | |
| ee.mark AS externalMark, | |
| eef.mark AS externalMarkFinilized, | |
| eets.thirdvalstudentID AS eligible3rdVal, | |
| ea.isAbsent, | |
| s.isTheory | |
| FROM | |
| exam_registration_subject_fees ersf | |
| INNER JOIN subjects s ON | |
| s.subjectID = ersf.subjectID | |
| INNER JOIN exam e ON | |
| e.batchID = ersf.batchID | |
| AND e.semID = ersf.semID | |
| AND e.subjectID = ersf.subjectID | |
| AND e.subjectID = s.subjectID | |
| AND e.examregID = ersf.examregID | |
| INNER JOIN exam_reg_studentsubject erss ON | |
| erss.examregID = ersf.examregID | |
| AND erss.examregID = e.examregID | |
| AND erss.subjectID = e.subjectID | |
| AND erss.subjectID = s.subjectID | |
| AND erss.subjectID = ersf.subjectID | |
| INNER JOIN studentaccount sa ON | |
| sa.studentID = erss.studentID | |
| AND sa.batchID = ersf.batchID | |
| AND sa.batchID = e.batchID | |
| INNER JOIN exam_valuation_staffs evs ON | |
| evs.examID = e.examID | |
| LEFT JOIN valuationdates vd ON | |
| vd.batchID = e.batchID | |
| AND vd.batchID = ersf.batchID | |
| AND vd.batchID = sa.batchID | |
| AND vd.examregID = erss.examregID | |
| AND vd.examregID = ersf.examregID | |
| AND vd.examregID = e.examregID | |
| LEFT JOIN examcontroller_false_number ecfn ON | |
| ecfn.examregID = erss.examregID | |
| AND ecfn.examregID = ersf.examregID | |
| AND ecfn.examregID = e.examregID | |
| AND ecfn.examID = e.examID | |
| AND ecfn.examID = evs.examID | |
| AND ecfn.studentID = erss.studentID | |
| AND ecfn.studentID = sa.studentID | |
| LEFT JOIN exammarks_external ee ON | |
| ee.examID = e.examID | |
| AND ee.examID = evs.examID | |
| AND ee.studentID = erss.studentID | |
| AND ee.studentID = sa.studentID | |
| LEFT JOIN externalexammarks_finalized eef ON | |
| eef.examID = e.examID | |
| AND eef.examID = evs.examID | |
| AND eef.studentID = erss.studentID | |
| AND eef.studentID = sa.studentID | |
| LEFT JOIN external_exammarks eem ON | |
| eem.examID = e.examID | |
| AND eem.examID = evs.examID | |
| AND eem.studentID = erss.studentID | |
| AND eem.studentID = sa.studentID | |
| LEFT JOIN externalexam_thirdvalstudents eets ON | |
| eets.examID = e.examID | |
| AND eets.examID = evs.examID | |
| AND eets.studentID = erss.studentID | |
| AND eets.studentID = sa.studentID | |
| LEFT JOIN exam_valuation_closed evc ON | |
| evc.examID = e.examID | |
| AND evc.examID = evs.examID | |
| LEFT JOIN exam_attendance ea ON | |
| ea.examID = e.examID | |
| AND ea.studentID = sa.studentID | |
| WHERE | |
| ersf.subjectID = ersf.subjectID | |
| $condition"; | |
| $sqlSupply = "SELECT | |
| e.batchID AS batchId, | |
| s.subjectID AS subjectId, | |
| s.subjectName, | |
| s.syllabusName, | |
| s.subjectDesc, | |
| e.examID AS examId, | |
| e.examTotalMarks, | |
| sa.studentID AS studentId, | |
| sa.regNo, | |
| sa.studentName, | |
| sa.admissionNo, | |
| IF(es.valuation_endDate = '', null, es.valuation_endDate) AS firstValidationEndDate, | |
| IF(es.valuation_startDate = '', null, es.valuation_startDate) AS firstValidationStartDate, | |
| IF(esv.valuation_endDate = '', null, esv.valuation_endDate) AS secondValidationEndDate, | |
| IF(esv.valuation_startDate = '', null, esv.valuation_startDate) AS secondValidationStartDate, | |
| ecfn.false_number AS falseNumber, | |
| eem.mark, | |
| eem.valuationCount, | |
| ee.mark AS externalMark, | |
| eef.mark AS externalMarkFinilized, | |
| eets.thirdvalstudentID AS eligible3rdVal, | |
| ea.isAbsent, | |
| s.isTheory | |
| FROM | |
| exam_supplementary_student_subjects esss | |
| INNER JOIN exam re ON | |
| re.examID = esss.examID | |
| INNER JOIN exam e ON | |
| e.batchID = re.batchID | |
| AND e.subjectID = re.subjectID | |
| AND e.semID = re.semID | |
| AND e.supply_examreg_id = esss.exam_supplementary_id | |
| INNER JOIN subjects s ON | |
| s.subjectID = e.subjectID | |
| AND s.subjectID = re.subjectID | |
| INNER JOIN studentaccount sa ON | |
| sa.studentID = esss.studentID | |
| AND sa.batchID = re.batchID | |
| AND sa.batchID = e.batchID | |
| INNER JOIN exam_supplementary es ON | |
| es.id = esss.exam_supplementary_id | |
| $staffSubjectsCondition | |
| LEFT JOIN exam_supplementary_valdates esv ON | |
| esv.exam_supplementary_id = esss.exam_supplementary_id | |
| AND esv.valuationCount = 2 | |
| LEFT JOIN examcontroller_false_number ecfn ON | |
| ecfn.exam_supplementary_id = esss.exam_supplementary_id | |
| AND ecfn.exam_supplementary_id = e.supply_examreg_id | |
| AND ecfn.examID = e.examID | |
| -- AND ecfn.examID = evs.examID | |
| AND ecfn.studentID = esss.studentID | |
| AND ecfn.studentID = sa.studentID | |
| LEFT JOIN exammarks_external ee ON | |
| ee.examID = e.examID | |
| -- AND ee.examID = evs.examID | |
| AND ee.studentID = esss.studentID | |
| AND ee.studentID = sa.studentID | |
| LEFT JOIN externalexammarks_finalized eef ON | |
| eef.examID = e.examID | |
| -- AND eef.examID = evs.examID | |
| AND eef.studentID = esss.studentID | |
| AND eef.studentID = sa.studentID | |
| LEFT JOIN external_exammarks eem ON | |
| eem.examID = e.examID | |
| -- AND eem.examID = evs.examID | |
| AND eem.studentID = esss.studentID | |
| AND eem.studentID = sa.studentID | |
| LEFT JOIN externalexam_thirdvalstudents eets ON | |
| eets.examID = e.examID | |
| -- AND eets.examID = evs.examID | |
| AND eets.studentID = esss.studentID | |
| AND eets.studentID = sa.studentID | |
| LEFT JOIN exam_valuation_closed evc ON | |
| evc.examID = e.examID | |
| -- AND evc.examID = evs.examID | |
| LEFT JOIN exam_attendance ea ON | |
| ea.examID = e.examID | |
| AND ea.studentID = sa.studentID | |
| WHERE e.subjectID = e.subjectID | |
| $condition"; | |
| if (!isset($request->isSupply)) { | |
| throw new ProfessionalException(ProfessionalException::INVALID_PARAMETER,"Unable to conform Exam type"); | |
| } | |
| else{ | |
| $sql = $request->isSupply ? $sqlSupply : $sqlRegular; | |
| } | |
| $students = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); | |
| } | |
| return $students; | |
| } | |
| /** | |
| * Update or add new exam mark | |
| * | |
| */ | |
| public function saveUgStudentExternalExamMark($students,$staffId) | |
| { | |
| $students = $this->realEscapeArray($students); | |
| $staffId = $this->realEscapeString($staffId); | |
| $eefValues = []; | |
| $eeValues = []; | |
| if (empty($students)) { | |
| throw new ProfessionalException(ProfessionalException::INVALID_PARAMETER,"Students can not be null"); | |
| } | |
| if (empty($staffId)) { | |
| throw new ProfessionalException(ProfessionalException::INVALID_PARAMETER,"Staff can not be null"); | |
| } | |
| foreach ($students as $student) { | |
| if (empty($student->examId) || empty($student->studentId)) { | |
| throw new ProfessionalException(ProfessionalException::INVALID_PARAMETER,"Some students have no valid data"); | |
| } | |
| $eefValues[] = "('$student->examId','$student->studentId',".($student->externalMark ? $student->externalMark : 0). "," . ($student->isRevalued ? 1 : 0) . ")"; | |
| $eeValues[] = "('$student->examId','$student->studentId',".($student->externalMark ? $student->externalMark : 0).",$staffId,".($student->isRevalued ? 1: 0).")"; | |
| } | |
| try { | |
| $eefValuesString = implode(", ",$eefValues); | |
| $sql = "INSERT INTO externalexammarks_finalized (examID, studentID, mark,isRevalued) VALUES $eefValuesString ON DUPLICATE KEY UPDATE mark = VALUES(mark), isRevalued = VALUES(isRevalued),updated_by = $staffId"; | |
| $this->executeQuery($sql); | |
| $eeValuesString = implode(", ",$eeValues); | |
| $sql = "INSERT INTO exammarks_external (examID, studentID, mark, staffID, isRevalued) VALUES $eeValuesString ON DUPLICATE KEY UPDATE mark = VALUES(mark), isRevalued = VALUES(isRevalued),updated_by = $staffId"; | |
| $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); | |
| } | |
| return; | |
| } | |
| public function savePgStudentExternalExamMark($students,$request) | |
| { | |
| $students = $this->realEscapeArray($students); | |
| $request = $this->realEscapeObject($request); | |
| $eemValues = []; | |
| $eeValues = []; | |
| $markEnteringPersonColumn = ""; | |
| if (empty($students)) { | |
| throw new ProfessionalException(ProfessionalException::INVALID_PARAMETER,"Students can not be null"); | |
| } | |
| if (empty($request->staffId) && empty($request->adminId)) { | |
| throw new ProfessionalException(ProfessionalException::INVALID_PARAMETER,"Staff can not be null"); | |
| } | |
| else{ | |
| $markEnteringPersonColumn = empty($request->staffId) ? "adminID" : "staffID"; | |
| $markEnteringPersonId = empty($request->staffId) ? $request->adminId : $request->staffId; | |
| } | |
| if (empty($request->valuationCount)) { | |
| throw new ProfessionalException(ProfessionalException::INVALID_PARAMETER,"Valuation count can not be null"); | |
| } | |
| foreach ($students as $student) { | |
| if (empty($student->examId) || empty($student->studentId)) { | |
| throw new ProfessionalException(ProfessionalException::INVALID_PARAMETER,"Some students have no valid data"); | |
| } | |
| $eemValues[] = "('$student->examId','$student->studentId',".($student->mark ? $student->mark : 0).",$request->valuationCount)"; | |
| $eeValues[] = "('$student->examId','$student->studentId',".($student->mark ? $student->mark : 0).",$markEnteringPersonId)"; | |
| } | |
| try { | |
| if ($request->valuationCount == 1) { | |
| $eeValuesString = implode(", ",$eeValues); | |
| $sql = "INSERT INTO exammarks_external (examID, studentID, mark, $markEnteringPersonColumn) VALUES $eeValuesString ON DUPLICATE KEY UPDATE mark = VALUES(mark)"; | |
| $this->executeQuery($sql); | |
| } | |
| else { | |
| $eemValuesString = implode(", ",$eemValues); | |
| $sql = "INSERT INTO external_exammarks (examID, studentID, mark, valuationCount) VALUES $eemValuesString ON DUPLICATE KEY UPDATE mark = VALUES(mark)"; | |
| $this->executeQuery($sql); | |
| } | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); | |
| } | |
| return; | |
| } | |
| public function saveStudentFinilizedExternalExamMark($students) | |
| { | |
| $students = $this->realEscapeArray($students); | |
| $eefValues = []; | |
| if (empty($students)) { | |
| throw new ProfessionalException(ProfessionalException::INVALID_PARAMETER,"Students can not be null"); | |
| } | |
| foreach ($students as $student) { | |
| if (empty($student->examId) || empty($student->studentId)) { | |
| throw new ProfessionalException(ProfessionalException::INVALID_PARAMETER,"Some students have no valid data"); | |
| } | |
| $eefValues[] = "('$student->examId','$student->studentId',".($student->externalMark ? $student->externalMark : 0).")"; | |
| } | |
| try { | |
| $eefValuesString = implode(", ",$eefValues); | |
| $sql = "INSERT INTO externalexammarks_finalized (examID, studentID, mark) VALUES $eefValuesString ON DUPLICATE KEY UPDATE mark = VALUES(mark)"; | |
| $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); | |
| } | |
| return; | |
| } | |
| public function saveStudentsForThirdValuation($students) | |
| { | |
| $students = $this->realEscapeArray($students); | |
| $values = []; | |
| if (empty($students)) { | |
| throw new ProfessionalException(ProfessionalException::INVALID_PARAMETER,"Students can not be null"); | |
| } | |
| foreach ($students as $student) { | |
| if (empty($student->examId) || empty($student->studentId)) { | |
| throw new ProfessionalException(ProfessionalException::INVALID_PARAMETER,"Some students have no valid data"); | |
| } | |
| $values[] = "('$student->examId','$student->studentId')"; | |
| } | |
| try { | |
| $valuesString = implode(", ",$values); | |
| $sql = "INSERT INTO externalexam_thirdvalstudents (examID, studentID) VALUES $valuesString ON DUPLICATE KEY UPDATE examID = VALUES(examID)"; | |
| $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); | |
| } | |
| return; | |
| } | |
| public function getFormulaByExamRegId($examRegId, $isSupply=false) | |
| { | |
| $examRegId = $this->realEscapeString($examRegId); | |
| $isSupply = $this->realEscapeString($isSupply); | |
| $examRegColumnName = "exam_registration_id"; | |
| if ($isSupply) { | |
| $examRegColumnName = "exam_supplementary_id"; | |
| } | |
| try{ | |
| if (empty($examRegId)) { | |
| throw new ProfessionalException(ProfessionalException::INVALID_PARAMETER,"Invalid Exam Registraion"); | |
| } | |
| $query = "SELECT | |
| evrf.id, | |
| evrf.formulaName, | |
| evrf.formula | |
| FROM | |
| exam_valuation_rule_relation evrr | |
| INNER JOIN exam_valuation_rule_formula evrf ON | |
| evrf.id = evrr.exam_valuation_rule_formula_id | |
| WHERE | |
| ".$examRegColumnName." = '$examRegId'"; | |
| $formula = $this->executeQueryForObject($query); | |
| }catch(\Exception $e) | |
| { | |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); | |
| } | |
| return $formula; | |
| } | |
| public function getAllRuleConstants() | |
| { | |
| try{ | |
| $query = "SELECT | |
| id, | |
| constantName, | |
| constantValue | |
| FROM | |
| exam_valuation_rule_constant"; | |
| $constants = $this->executeQueryForList($query); | |
| }catch(\Exception $e) | |
| { | |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); | |
| } | |
| return $constants; | |
| } | |
| /** | |
| * @param $studentId,examId | |
| * delete from third valuation students | |
| */ | |
| public function deleteThirdValStudents($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $result = null; | |
| $condition = ""; | |
| if ($request->isRevaluation) { | |
| $condition .= " AND revaluationFlag = 1"; | |
| } | |
| $sql = "DELETE FROM externalexam_thirdvalstudents WHERE examID = '$request->examID' AND studentID='$request->studentID' $condition"; | |
| $deleteMarkSql = "DELETE FROM external_exammarks WHERE examID = '$request->examID' AND studentID='$request->studentID' AND valuationCount='3'"; | |
| try { | |
| $this->executeQueryForObject($sql); | |
| if (!$request->isRevaluation){ | |
| $this->executeQueryForObject($deleteMarkSql); | |
| } | |
| $result = true; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $result; | |
| } | |
| /** | |
| * @param $courseTypeId | |
| * delete from finalized marks | |
| */ | |
| public function deleteStudentFinalizedMark($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $result = null; | |
| $sql = "DELETE FROM externalexammarks_finalized WHERE examID = '$request->examID' AND studentID='$request->studentID'"; | |
| try { | |
| $this->executeQueryForObject($sql); | |
| $result = true; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $result; | |
| } | |
| /** | |
| * @param $examId,valuation count | |
| * get Students External Marks By Exam | |
| */ | |
| public function getStudentsExternalMarksByExam($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $valuationCountCondition = ""; | |
| $sql = ""; | |
| $studentsMarks = null; | |
| if ($request->valuationCount == 2 || $request->valuationCount == 3) { | |
| $tableName = "external_exammarks"; | |
| $valuationCountCondition = "AND valuationCount = $request->valuationCount"; | |
| } | |
| else{ | |
| $tableName = "exammarks_external"; | |
| } | |
| $sql = "SELECT examID,studentID,mark from $tableName WHERE examID IN($request->examIds) $valuationCountCondition"; | |
| try { | |
| $studentsMarks = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $studentsMarks; | |
| } | |
| /** | |
| * @param $examId,studentId | |
| * get Students External Marks By Exam | |
| */ | |
| public function getStudentRvStatus($examId, $studentId) | |
| { | |
| $examId = $this->realEscapeString($examId); | |
| $studentId = $this->realEscapeString($studentId); | |
| $sql = "SELECT isRevalued from exammarks_external WHERE examID =$examId and studentID =$studentId"; | |
| try { | |
| $resposnse = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $resposnse[0]->isRevalued; | |
| } | |
| /** | |
| * Method to check student exist in normalise mark | |
| * @param Integer | |
| * @return Boolean | |
| * @throws ProfessionalException | |
| */ | |
| public function checkStudentInNormaliseMark($studentID,$subjectID,$batchID,$semID) | |
| { | |
| $sql = "SELECT | |
| markID | |
| FROM | |
| normalise_marks | |
| WHERE | |
| studentID = '$studentID' AND batchID = '$batchID' | |
| AND subjectID = '$subjectID' | |
| AND semID = '$semID'"; | |
| try { | |
| $result = $this->executeQueryForList($sql); | |
| if($result){ | |
| return true; | |
| } | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return false; | |
| } | |
| public function savePgStudentExternalExamMarkAndAttendance($students,$request) | |
| { | |
| $students = $this->realEscapeArray($students); | |
| $request = $this->realEscapeObject($request); | |
| $eemValues = []; | |
| $eeValues = []; | |
| $markEnteringPersonColumn = ""; | |
| if (empty($students)) { | |
| throw new ProfessionalException(ProfessionalException::INVALID_PARAMETER,"Students can not be null"); | |
| } | |
| if (empty($request->staffId) && empty($request->adminId)) { | |
| throw new ProfessionalException(ProfessionalException::INVALID_PARAMETER,"Staff can not be null"); | |
| } | |
| else{ | |
| $markEnteringPersonColumn = empty($request->staffId) ? "adminID" : "staffID"; | |
| $markEnteringPersonId = empty($request->staffId) ? $request->adminId : $request->staffId; | |
| } | |
| if (empty($request->valuationCount)) { | |
| throw new ProfessionalException(ProfessionalException::INVALID_PARAMETER,"Valuation count can not be null"); | |
| } | |
| foreach ($students as $student) { | |
| if (empty($student->examId) || empty($student->studentId)) { | |
| throw new ProfessionalException(ProfessionalException::INVALID_PARAMETER,"Some students have no valid data"); | |
| } | |
| if($student->mark == "AB"){ | |
| $isAbsent = 1; | |
| }elseif($student->mark == "MAL"){ | |
| $isAbsent = 2; | |
| } | |
| else{ | |
| $isAbsent = 0; | |
| } | |
| $eemValues[] = "('$student->examId','$student->studentId',".($student->mark ? $student->mark : 0).",$request->valuationCount)"; | |
| if(!$isAbsent){ | |
| $eeValues[] = "('$student->examId','$student->studentId',".($student->mark ? $student->mark : 0).",$markEnteringPersonId)"; | |
| } | |
| $exAtt[] = "('$student->examId','$student->studentId','$isAbsent')"; | |
| } | |
| try { | |
| if ($request->valuationCount == 1) { | |
| $eeValuesString = implode(", ",$eeValues); | |
| $sql = "INSERT INTO exammarks_external (examID, studentID, mark, $markEnteringPersonColumn) VALUES $eeValuesString ON DUPLICATE KEY UPDATE mark = VALUES(mark)"; | |
| $exAttString = implode(", ",$exAtt); | |
| $sql2 = "INSERT INTO exam_attendance (examID, studentID, isAbsent) VALUES $exAttString ON DUPLICATE KEY UPDATE isAbsent = VALUES(isAbsent)"; | |
| $this->executeQuery($sql); | |
| $this->executeQuery($sql2); | |
| } | |
| else { | |
| $eemValuesString = implode(", ",$eemValues); | |
| $sql = "INSERT INTO external_exammarks (examID, studentID, mark, valuationCount) VALUES $eemValuesString ON DUPLICATE KEY UPDATE mark = VALUES(mark)"; | |
| $this->executeQuery($sql); | |
| } | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); | |
| } | |
| return; | |
| } | |
| /** | |
| * method to get staffID of normalise_mark | |
| * @param Int batchID, subjectID | |
| * @return StaffID | |
| * @throws ProfessionalException | |
| * | |
| */ | |
| public function getStaffIDNormaliseMarkBySubjectIdAndBatchId($subjectID,$batchID) | |
| { | |
| $batchID = $this->realEscapeString($batchID); | |
| $subjectID = $this->realEscapeString($subjectID); | |
| try { | |
| $sql = "SELECT | |
| staffID | |
| FROM | |
| normalise_marks | |
| WHERE | |
| subjectID = '$subjectID' AND batchID = '$batchID' | |
| GROUP BY staffID"; | |
| $staffID = $this->executeQueryForObject($sql)->staffID; | |
| return $staffID; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return false; | |
| } | |
| /** | |
| * Update or add new exam mark for ug students | |
| * | |
| */ | |
| public function saveUgStudentsExternalMark($students, $staffId) | |
| { | |
| $students = $this->realEscapeArray($students); | |
| $staffId = $this->realEscapeString($staffId); | |
| $eeValues = []; | |
| if (empty($students)) { | |
| throw new ProfessionalException(ProfessionalException::INVALID_PARAMETER, "Students can not be null"); | |
| } | |
| if (empty($staffId)) { | |
| throw new ProfessionalException(ProfessionalException::INVALID_PARAMETER, "Staff can not be null"); | |
| } | |
| foreach ($students as $student) { | |
| if (empty($student->examId) || empty($student->studentId)) { | |
| throw new ProfessionalException(ProfessionalException::INVALID_PARAMETER, "Some students have no valid data"); | |
| } | |
| $eeValues[] = "('$student->examId','$student->studentId'," . ($student->externalMark ? $student->externalMark : 0) . ",$staffId," . ($student->isRevalued ? 1 : 0) . ")"; | |
| } | |
| try { | |
| $eeValuesString = implode(", ", $eeValues); | |
| $sql = "INSERT INTO exammarks_external (examID, studentID, mark, staffID, isRevalued) VALUES $eeValuesString ON DUPLICATE KEY UPDATE mark = VALUES(mark), isRevalued = VALUES(isRevalued)"; | |
| $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return; | |
| } | |
| /** | |
| * Update or add new internal mark log | |
| * | |
| */ | |
| public function saveStudentInternalMarkLog($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $studentList = $request->studentList; | |
| $insertValues = []; | |
| date_default_timezone_set('Asia/Kolkata'); | |
| $t=time(); | |
| try { | |
| foreach ($studentList as $student) { | |
| $student = (object) $student; | |
| if($student->internalMarks != $student->oldInternalMarks || $student->remarks != $student->oldRemarks){ | |
| $log = new StudentMarkEditLog(); | |
| $log->oldMark = $student->oldInternalMarks; | |
| $log->newMark = $student->internalMarks; | |
| $log->remarks = $student->remarks; | |
| $log->markType = "INTERNAL_MARK"; | |
| $log->actionTakenTime = date("d-m-Y h:i A",$t); | |
| $log->updatedStaffId = $_SESSION['adminID']; | |
| $log->updatedStaffName = $request->staffName; | |
| $log->updatedStaffType = "EXAM_CONTROLLER"; | |
| $log->menuName = $request->menu; | |
| $log = json_encode($log); | |
| $id = SecurityUtils::getRandomString(); | |
| $insertValues = "('$id',$request->subjectId,$request->batchId,$request->semId,'$student->studentID','[$log]','$request->staffId',now())"; | |
| if (!empty($insertValues)) { | |
| // $insertValues = implode(",", $insertValues); | |
| $sql = "INSERT INTO im_student_mark_edit_log (id,subjectId,batchId,semId,student_id,log,created_by,created_date) | |
| VALUES $insertValues ON DUPLICATE KEY UPDATE `log` = JSON_MERGE(`log`,'$log'), updated_by = VALUES(created_by)"; | |
| $this->executeQuery($sql); | |
| } | |
| unset($log); | |
| unset($insertValues); | |
| unset($id); | |
| } | |
| } | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return true; | |
| } | |
| /** | |
| * get Student InternalMark EditLog | |
| * | |
| */ | |
| public function getStudentInternalMarkEditLog($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| try { | |
| $sql = "SELECT | |
| `log` | |
| FROM | |
| im_student_mark_edit_log | |
| WHERE | |
| student_id = '$request->studentID' | |
| AND subjectId = '$request->subjectID' | |
| AND batchId = '$request->batchID' | |
| AND semId = '$request->semId'"; | |
| $log = $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $log; | |
| } | |
| /** | |
| * get Oe Exam Mark Confirmed Students | |
| * author sibin | |
| */ | |
| public function getOeExamMarkConfirmedStudentByExam($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $condition = ""; | |
| if($request->studentId){ | |
| $condition .= " AND oec.oe_users_id IN ($request->studentId)"; | |
| } | |
| if ($request->reviewId) { | |
| $condition .= " AND oec.review_id IN ($request->reviewId)"; | |
| }else{ | |
| $condition .= " AND oec.review_id IS NULL"; | |
| } | |
| if(!$request->revaluationId){ | |
| $condition .=" AND oec.revaluation_id IS NULL"; | |
| } | |
| $studentList = []; | |
| try { | |
| $sql = "SELECT | |
| oe.id as oeExamId,oec.oe_users_id as studentId,efn.false_number as falseNumber,oec.is_confirmed as isConfirmed,oec.valuation_count as valuationCount,oec.review_id as reviewId | |
| FROM | |
| oe_exams oe | |
| INNER JOIN oe_exam_marks_confirm oec | |
| ON oec.oe_exams_id = oe.id | |
| INNER JOIN examcontroller_false_number efn | |
| ON efn.examID = $request->examId AND efn.studentID = oec.oe_users_id | |
| WHERE | |
| JSON_CONTAINS(oe.identifying_context, | |
| '{\"examId\": \"$request->examId\"}') | |
| AND oec.is_confirmed = 1 AND oec.valuation_count = '$request->valuationCount' $condition | |
| GROUP BY oec.oe_users_id ORDER BY efn.false_number"; | |
| $studentList = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $studentList; | |
| } | |
| /** | |
| * remove Oe Exam Mark Confirmed Student state | |
| * author sibin | |
| */ | |
| public function removeConfirmedStatusOfOeExamMarkConfirmedStudent($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $condition =""; | |
| if($request->valuationType){ | |
| if($request->valuationType == "valuer"){ | |
| $condition .=" AND review_id IS NULL"; | |
| }else if ($request->valuationType == "reviewer"){ | |
| $condition .=" AND review_id = 1"; | |
| } | |
| } | |
| if(!$request->revaluationId){ | |
| $condition .=" AND revaluation_id IS NULL"; | |
| } | |
| try { | |
| $sql = "UPDATE | |
| oe_exam_marks_confirm | |
| SET | |
| is_confirmed = '0' | |
| WHERE | |
| oe_exams_id = '$request->oeExamId' | |
| AND oe_users_id = '$request->studentId' | |
| AND valuation_count = '$request->valuationCount' $condition"; | |
| $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return true; | |
| } | |
| /** | |
| * @author Sibin | |
| * get oe exam marks students mark finalized by examregid and subjectid | |
| */ | |
| public function getExamMarkFinalizedOeStudentsMarkDetailsBySubject($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $externalMarksTableJoin = "LEFT"; | |
| $externalMarksTable = "exammarks_external"; | |
| if($request->isPg){ | |
| $externalMarksTable = "externalexammarks_finalized"; | |
| } | |
| if($request->showFinalizedMarksOnly){ | |
| $externalMarksTableJoin = "INNER"; | |
| } | |
| $sql = "SELECT distinct erss.studentID,sa.regNo,sa.studentName,e.examID,e.batchID,e.semID,e.subjectID,e.examTotalMarks, | |
| oe.id as oeExamId,oec1.exam_mark as mark1,oec1.is_confirmed as mark1Confirm,oec2.exam_mark as mark2,oec2.is_confirmed as mark2Confirm | |
| ,oec3.exam_mark as mark3,oec3.is_confirmed as mark3Confirm,ee.mark as markFinalized | |
| FROM exam_reg_studentsubject erss | |
| INNER JOIN exam_reg_studentchallan ersc ON ersc.studentID = erss.studentID AND ersc.examregID = erss.examregID | |
| INNER JOIN studentaccount sa ON sa.studentID = erss.studentID | |
| INNER JOIN exam_registration_batches erb ON erb.examregID = erss.examregID AND erb.batchID = sa.batchID | |
| INNER JOIN exam e ON e.examregID = erss.examregID AND e.subjectID = erss.subjectID AND e.batchID = sa.batchID AND e.semID = erb.semID | |
| INNER JOIN oe_exams oe ON JSON_UNQUOTE(JSON_EXTRACT(oe.identifying_context, '$.examId')) = e.examID | |
| LEFT JOIN oe_exam_marks_confirm oec1 ON oec1.oe_exams_id = oe.id AND oec1.oe_users_id = erss.studentID AND oec1.valuation_count = 1 | |
| LEFT JOIN oe_exam_marks_confirm oec2 ON oec2.oe_exams_id = oe.id AND oec2.oe_users_id = erss.studentID AND oec2.valuation_count = 2 | |
| LEFT JOIN oe_exam_marks_confirm oec3 ON oec3.oe_exams_id = oe.id AND oec3.oe_users_id = erss.studentID AND oec3.valuation_count = 3 | |
| $externalMarksTableJoin JOIN $externalMarksTable ee ON ee.examID = e.examID AND ee.studentID = erss.studentID | |
| WHERE erss.examregID = '$request->examRegId' AND erss.subjectID = '$request->subjectId' AND e.examID = '$request->examId' AND ersc.paid=1 | |
| group by sa.studentID order by sa.regNo"; | |
| try { | |
| $subjectStudents = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $subjectStudents; | |
| } | |
| /** | |
| * @param $subjectId,examId | |
| * get third valuation students count | |
| */ | |
| public function getThirdValuationStudentCount($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $examRegField = ($request->examType == ExamType::SUPPLY) ? "supply_examreg_id" : "examregID"; | |
| $condition=""; | |
| if($request->isRevaluation){ | |
| $condition .=" AND etv.revaluationFlag = 1"; | |
| } | |
| $result = null; | |
| $sql = " SELECT COUNT(thirdvalstudentID) as studentCount from externalexam_thirdvalstudents etv INNER JOIN exam ex ON(etv.examID = ex.examID ) WHERE ex.$examRegField = '$request->examRegId' and ex.subjectID = '$request->subjectId' | |
| $condition"; | |
| try { | |
| return $this->executeQueryForObject($sql)->studentCount; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * To get student's internal added mark | |
| * @param int $studentID | |
| * @param int $staffID | |
| * @param int $batchID | |
| * @param int $subjectID | |
| */ | |
| public function getNormaliseMarks($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $where = []; | |
| $request->batchId?$where[] = "batchID = '".$request->batchId."'":null; | |
| $request->studentId?$where[] = "studentID = '".$request->studentId."'":null; | |
| $request->semId?$where[] = "semID = '".$request->semId."'":null; | |
| $request->subejctId?$where[] = "subejctID = '".$request->subejctId."'":null; | |
| $request->staffId?$where[] = "staffID = '".$request->staffId."'":null; | |
| $sql="SELECT markID, marksObtained, normalisedMark, percentage FROM normalise_marks | |
| " . ($where ? " WHERE " . implode(' AND ', $where) : "") . " "; | |
| try{ | |
| return $this->executeQueryForList($sql); | |
| }catch(\Exception $e){ | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * To get student's internal mark rules | |
| * @param int $studentID | |
| * @param int $staffID | |
| * @param int $batchID | |
| * @param int $subjectID | |
| */ | |
| public function getNormaliseMarksRules($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $where = []; | |
| $request->batchId?$where[] = "batch_id = '".$request->batchId."'":null; | |
| $request->semId?$where[] = "sem_id = '".$request->semId."'":null; | |
| $request->subejctId?$where[] = "subject_id = '".$request->subejctId."'":null; | |
| $sql="SELECT id,isAttendance,roundOffAttnPercent from normalization_rule3_subject | |
| " . ($where ? " WHERE " . implode(' AND ', $where) : "") . " "; | |
| try{ | |
| return $this->executeQueryForList($sql); | |
| }catch(\Exception $e){ | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * delete marks and update exam attendance on ab,mal | |
| * | |
| */ | |
| public function deleteStudentExamMarkAndUpdateAttendance($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| try { | |
| $sql = "DELETE FROM externalexammarks_finalized WHERE examID = '$request->examId' AND studentID = '$request->studentId'"; | |
| $this->executeQuery($sql); | |
| $sql = "DELETE FROM exammarks_external WHERE examID = '$request->examId' AND studentID = '$request->studentId'"; | |
| $this->executeQuery($sql); | |
| $sql_absent = "INSERT INTO exam_attendance (examID, studentID, isAbsent) VALUES ('$request->examId','$request->studentId',1) ON DUPLICATE KEY UPDATE isAbsent = VALUES(isAbsent)"; | |
| $this->executeQuery($sql_absent); | |
| $id = SecurityUtils::getRandomString(); | |
| $log = json_encode($request->log); | |
| $sql = "INSERT INTO ec_student_mark_edit_log (id,exam_id,student_id,log,created_by,created_date) | |
| VALUES ('$id',$request->examId,'$request->studentId','[$log]','$request->staffId',now()) ON DUPLICATE KEY UPDATE `log` = JSON_MERGE(`log`,'$log'), updated_by = VALUES(created_by)"; | |
| $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return true; | |
| } | |
| /** | |
| * @param $examId,studentId | |
| * get pg Students External Marks By Exam | |
| */ | |
| public function getStudentRvStatusPg($examId, $studentId) | |
| { | |
| $examId = $this->realEscapeString($examId); | |
| $studentId = $this->realEscapeString($studentId); | |
| $sql = "SELECT isRevalued from externalexammarks_finalized WHERE examID =$examId and studentID =$studentId"; | |
| try { | |
| $resposnse = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $resposnse[0]->isRevalued; | |
| } | |
| /** | |
| * delete marks and update exam attendance on ab,mal | |
| * | |
| */ | |
| public function resetStudentExamMarkAndUpdateAttendance($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| try { | |
| $sql = "UPDATE externalexammarks_finalized set mark = 0 WHERE examID = '$request->examId' AND studentID = '$request->studentId'"; | |
| $this->executeQuery($sql); | |
| $sql = "UPDATE exammarks_external set mark = 0 WHERE examID = '$request->examId' AND studentID = '$request->studentId'"; | |
| $this->executeQuery($sql); | |
| $sql_absent = "INSERT INTO exam_attendance (examID, studentID, isAbsent) VALUES ('$request->examId','$request->studentId',1) ON DUPLICATE KEY UPDATE isAbsent = VALUES(isAbsent)"; | |
| $this->executeQuery($sql_absent); | |
| $id = SecurityUtils::getRandomString(); | |
| $log = json_encode($request->log); | |
| $sql = "INSERT INTO ec_student_mark_edit_log (id,exam_id,student_id,log,created_by,created_date) | |
| VALUES ('$id',$request->examId,'$request->studentId','[$log]','$request->staffId',now()) ON DUPLICATE KEY UPDATE `log` = JSON_MERGE(`log`,'$log'), updated_by = VALUES(created_by)"; | |
| $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return true; | |
| } | |
| /** | |
| * Update or add new/old exam mark log | |
| * | |
| */ | |
| public function saveStudentExamMarkLog($request) | |
| { | |
| if (is_array($request)) { | |
| $request = (object)$request; | |
| } | |
| $request = $this->realEscapeObject($request); | |
| if (!$request->log) { | |
| $request->log = $this->prepareLog($request); | |
| if(!$request->log){ | |
| return false; | |
| } | |
| } | |
| $request->createdBy = $request->updatedStaffId ? $request->updatedStaffId : $_SESSION['adminID']; | |
| try { | |
| $id = SecurityUtils::getRandomString(); | |
| $log = json_encode($request->log); | |
| $sql = "INSERT INTO ec_student_mark_edit_log (id,exam_id,student_id,log,created_by,created_date) | |
| VALUES ('$id',$request->examId,'$request->studentId','[$log]','$request->createdBy',now()) ON DUPLICATE KEY UPDATE `log` = JSON_MERGE(`log`,'$log'), updated_by = VALUES(created_by)"; | |
| $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return true; | |
| } | |
| public function prepareLog($request) | |
| { | |
| $log = new StudentMarkEditLog(); | |
| $log->oldMark = $request->oldMark; | |
| $log->newMark = $request->mark; | |
| if($log->newMark != $log->oldMark){ | |
| $log->remarks = $request->remarks ? $request->remarks : "DIRECT_MARK_ENTRY"; | |
| $log->markType = $request->markType ? $request->markType : "EXTERNAL_MARK"; | |
| $log->actionTakenTime = date("d-m-Y h:i A"); | |
| $log->updatedStaffId = $request->updatedStaffId ? $request->updatedStaffId : $_SESSION['adminID']; | |
| $log->updatedStaffName = $request->updatingStaffName; | |
| $log->updatedStaffType = "EXAM_CONTROLLER"; | |
| return $log; | |
| }else{ | |
| return false; | |
| } | |
| } | |
| //get audit course pass details | |
| public function getAuditCoursePassDetails($request){ | |
| $request = $this->realEscapeObject($request); | |
| try { | |
| $sql = "SELECT aud.studentID,aud.semID,aud.subjectID,aud.flag,sa.batchID as batchId FROM audit_course_student_pass_details aud | |
| INNER JOIN studentaccount sa ON sa.studentID = aud.studentID | |
| where aud.semID=$request->semId and aud.subjectID =$request->subjectId AND sa.batchID = $request->batchId"; | |
| return $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| //delete audit course pass details | |
| public function deleteAuditCoursePassDetails($request){ | |
| $request = $this->realEscapeObject($request); | |
| try { | |
| if($request->semId && $request->subjectId && $request->studentIds){ | |
| $sql = "DELETE FROM audit_course_student_pass_details | |
| WHERE semID=$request->semId and subjectID =$request->subjectId AND studentID IN ($request->studentIds)"; | |
| return $this->executeQueryForObject($sql); | |
| } | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| //save audit course pass details | |
| public function saveAuditCoursePassDetails($entries){ | |
| $entries = $this->realEscapeArray($entries); | |
| $entriesStr = implode(",",$entries); | |
| try { | |
| $sql = "INSERT INTO audit_course_student_pass_details (studentID,semID,subjectID,flag) VALUES $entriesStr"; | |
| return $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| //get eligible marks for external mark conversion | |
| public function getEligibleExternalMarksForConversion($request){ | |
| $request = $this->realEscapeObject($request); | |
| $subjectIds = implode(',' ,$request->subjectIds); | |
| try { | |
| if($request->isPg){ | |
| $tableName = "externalexammarks_finalized"; | |
| }else{ | |
| $tableName = "exammarks_external"; | |
| } | |
| if($request->examType == "REGULAR"){ | |
| $examRegField = "e.examregID"; | |
| }else{ | |
| $examRegField = "e.supply_examreg_id"; | |
| } | |
| if($request->examRegId && $subjectIds ){ | |
| $sql = "SELECT ee.examID,ee.studentID FROM $tableName ee | |
| JOIN exam e ON ee.examID = e.examID | |
| WHERE $examRegField = '$request->examRegId' | |
| AND e.semID IN ('$request->semId') | |
| AND e.subjectID IN ($subjectIds) | |
| AND COALESCE(ee.isRevalued, 0) = 0 | |
| AND COALESCE(e.examTotalMarks, 0) != 0 | |
| AND COALESCE(e.valuationMaxMark, 0) != 0"; | |
| return $this->executeQueryForList($sql); | |
| } | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| //convert external mark entry | |
| public function convertExternalMarks($request){ | |
| $request = $this->realEscapeObject($request); | |
| $subjectIds = implode(',' ,$request->subjectIds); | |
| try { | |
| if($request->isPg){ | |
| $tableName = "externalexammarks_finalized"; | |
| }else{ | |
| $tableName = "exammarks_external"; | |
| } | |
| if($request->examType == "REGULAR"){ | |
| $examRegField = "e.examregID"; | |
| }else{ | |
| $examRegField = "e.supply_examreg_id"; | |
| } | |
| if($request->examRegId && $subjectIds ){ | |
| $sql = "UPDATE $tableName ee | |
| JOIN exam e ON ee.examID = e.examID | |
| SET ee.mark = ROUND(CAST(ee.mark AS DECIMAL(5,2)) * CAST(e.examTotalMarks AS DECIMAL(5,2)) / CAST(e.valuationMaxMark AS DECIMAL(5,2))),ee.isRevalued =1 | |
| WHERE $examRegField = '$request->examRegId' | |
| AND e.semID IN ('$request->semId') | |
| AND e.subjectID IN ($subjectIds) | |
| AND COALESCE(ee.isRevalued, 0) = 0 | |
| AND COALESCE(e.examTotalMarks, 0) != 0 | |
| AND COALESCE(e.valuationMaxMark, 0) != 0"; | |
| $result = $this->executeQueryForObject($sql,true); | |
| return $result; | |
| } | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| //check external mark entry | |
| public function checkExternalMarksSavedStatus($request){ | |
| $request = $this->realEscapeObject($request); | |
| try { | |
| if($request->isPg){ | |
| $tableName = "externalexammarks_finalized"; | |
| }else{ | |
| $tableName = "exammarks_external"; | |
| } | |
| $sql = "SELECT mark from $tableName where examID IN ($request->examIds)"; | |
| return $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| //save student sgpa cgpa | |
| public function saveStudentSgpaCgpa($request){ | |
| //$request = $this->realEscapeObject($request); | |
| try { | |
| if(!empty($request->sgpaInsertValues) && !empty($request->cgpaInsertValues)){ | |
| //sgpa | |
| $sgpaStr = implode(",",$request->sgpaInsertValues); | |
| $sql = "INSERT INTO student_semwise_sgpa_regular (studentID,courseTypeID,batchID,semID,sgpa,lettergrade) VALUES $sgpaStr ON DUPLICATE KEY UPDATE sgpa = VALUES(sgpa),lettergrade = VALUES(lettergrade)"; | |
| $this->executeQuery($sql); | |
| //cgpa | |
| $cgpaStr = implode(",",$request->cgpaInsertValues); | |
| $sql = "INSERT INTO student_final_cgpa_credits_regular (studentID,courseTypeID,batchID,semID,cgpa,overallgrade) VALUES $cgpaStr ON DUPLICATE KEY UPDATE cgpa = VALUES(cgpa),overallgrade = VALUES(overallgrade)"; | |
| $this->executeQuery($sql); | |
| return true; | |
| }else{ | |
| return false; | |
| } | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| } |