Code Coverage |
||||||||||
Classes and Traits |
Functions and Methods |
Lines |
||||||||
| Total | |
0.00% |
0 / 1 |
|
0.00% |
0 / 103 |
CRAP | |
0.00% |
0 / 3516 |
| ExamRevaluationService | |
0.00% |
0 / 1 |
|
0.00% |
0 / 103 |
327756.00 | |
0.00% |
0 / 3516 |
| __construct | n/a |
0 / 0 |
1 | n/a |
0 / 0 |
|||||
| __clone | n/a |
0 / 0 |
1 | n/a |
0 / 0 |
|||||
| getInstance | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 5 |
|||
| getExamRevaluationStudentSubjects | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 13 |
|||
| getExamRevaluations | |
0.00% |
0 / 1 |
42.00 | |
0.00% |
0 / 52 |
|||
| getExamRevaluationBatches | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 37 |
|||
| getExamRevaluationStudents | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 32 |
|||
| getRevaluationStudentDetails | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 59 |
|||
| addExamRevaluationNotification | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 21 |
|||
| getExamRevaluationNotification | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 11 |
|||
| saveExamRevaluation | |
0.00% |
0 / 1 |
132.00 | |
0.00% |
0 / 50 |
|||
| addExamRevaluationBatches | |
0.00% |
0 / 1 |
272.00 | |
0.00% |
0 / 86 |
|||
| getExamRevaluationBatchGroups | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 24 |
|||
| getBatchesInBatchGroups | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 24 |
|||
| getExamRevaluationFees | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 20 |
|||
| getSelectedPaymentMethods | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 16 |
|||
| saveAssignedExamRevaluationFees | |
0.00% |
0 / 1 |
30.00 | |
0.00% |
0 / 26 |
|||
| changeExamPaymentMethod | |
0.00% |
0 / 1 |
42.00 | |
0.00% |
0 / 40 |
|||
| getExamRevaluationReport | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 73 |
|||
| getRevaluationTypes | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 25 |
|||
| deleteRevaluation | |
0.00% |
0 / 1 |
156.00 | |
0.00% |
0 / 70 |
|||
| deleteBatchGroupAndFee | |
0.00% |
0 / 1 |
182.00 | |
0.00% |
0 / 76 |
|||
| getStudentAppliedStatusByRequest | |
0.00% |
0 / 1 |
110.00 | |
0.00% |
0 / 63 |
|||
| getRevaluationTypesByRequest | |
0.00% |
0 / 1 |
30.00 | |
0.00% |
0 / 35 |
|||
| getRevaluationExamSubjects | |
0.00% |
0 / 1 |
30.00 | |
0.00% |
0 / 30 |
|||
| getRevaluationExamDetails | |
0.00% |
0 / 1 |
42.00 | |
0.00% |
0 / 36 |
|||
| getExamDetailsByExamRegId | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 19 |
|||
| getRevaluationStudentsBySubject | |
0.00% |
0 / 1 |
72.00 | |
0.00% |
0 / 81 |
|||
| saveExamRevaluationMarks | |
0.00% |
0 / 1 |
380.00 | |
0.00% |
0 / 86 |
|||
| finalizeExamRevaluationMarks | |
0.00% |
0 / 1 |
156.00 | |
0.00% |
0 / 58 |
|||
| getFinalizedExamRevaluationMarks | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 22 |
|||
| getRevaluationDetailsById | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 13 |
|||
| getRevaluationExamReport | |
0.00% |
0 / 1 |
110.00 | |
0.00% |
0 / 105 |
|||
| getRevaluationTypesByIds | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 14 |
|||
| getExamRegistrationDetailsByRevaluationId | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 19 |
|||
| getRevaluationSubjects | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 25 |
|||
| getRevaluationByExamRegistration | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 18 |
|||
| uploadRevaluationMarksFromExcel | |
0.00% |
0 / 1 |
42.00 | |
0.00% |
0 / 34 |
|||
| checkRevaluationByStudent | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 11 |
|||
| checkRevaluationExamByStudent | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 13 |
|||
| checkRevaluationExamMarkByStudent | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 17 |
|||
| checkRevaluationExamMarkNonFinalByStudent | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 14 |
|||
| updateRevaluationExamMarkByStudent | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 16 |
|||
| updateRevaluationExamMarkNonFinalByStudent | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 16 |
|||
| saveExamRevaluationValMarks | |
0.00% |
0 / 1 |
182.00 | |
0.00% |
0 / 46 |
|||
| finalizeExamRevaluationValTwoMarks | |
0.00% |
0 / 1 |
30.00 | |
0.00% |
0 / 24 |
|||
| checkFinalizeExamRevaluationValTwoMarks | |
0.00% |
0 / 1 |
30.00 | |
0.00% |
0 / 25 |
|||
| saveThirdValRevalStudents | |
0.00% |
0 / 1 |
72.00 | |
0.00% |
0 / 35 |
|||
| getExamRevaluationReportBySubject | |
0.00% |
0 / 1 |
72.00 | |
0.00% |
0 / 102 |
|||
| deleteExamRevaluationMarkFinalizedState | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 12 |
|||
| getStudentExamRevaluationDetails | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 30 |
|||
| getRevaluationRegisteredStudentsById | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 15 |
|||
| getExamRevaluationsByRequest | |
0.00% |
0 / 1 |
72.00 | |
0.00% |
0 / 63 |
|||
| getRevaluationExamSubjectsWithValuationDateByRequest | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 34 |
|||
| assignAllRevaluationDatesSubjectWise | |
0.00% |
0 / 1 |
72.00 | |
0.00% |
0 / 33 |
|||
| getStudentsForExamRevaluationBySubject | |
0.00% |
0 / 1 |
30.00 | |
0.00% |
0 / 32 |
|||
| getRevaluationFinalizedMarksByExam | |
0.00% |
0 / 1 |
42.00 | |
0.00% |
0 / 23 |
|||
| saveFinalizedExamRevaluationMarks | |
0.00% |
0 / 1 |
210.00 | |
0.00% |
0 / 57 |
|||
| getRevaluationFinalizedMarksByExamFromLog | |
0.00% |
0 / 1 |
30.00 | |
0.00% |
0 / 24 |
|||
| getRevaluationAppliedStatusByRequest | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 15 |
|||
| saveExamRevaluationMark | |
0.00% |
0 / 1 |
42.00 | |
0.00% |
0 / 38 |
|||
| getRevaluationExamStudentBySubjects | |
0.00% |
0 / 1 |
30.00 | |
0.00% |
0 / 30 |
|||
| getAllRevaluationTypes | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 13 |
|||
| getRevaluationExamBatchesWithValuationDateByRequest | |
0.00% |
0 / 1 |
110.00 | |
0.00% |
0 / 58 |
|||
| assignRevaluationDatesBatchWise | |
0.00% |
0 / 1 |
56.00 | |
0.00% |
0 / 33 |
|||
| getExamRevaluationStaffs | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 16 |
|||
| assignExamRevaluationFaculty | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 20 |
|||
| getRevaluationExamValuationStudentsByRequest | |
0.00% |
0 / 1 |
210.00 | |
0.00% |
0 / 79 |
|||
| assignStudentsToExamRevaluationStaffs | |
0.00% |
0 / 1 |
72.00 | |
0.00% |
0 / 35 |
|||
| getExamRevaluationStudentAssignedStaffByExam | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 22 |
|||
| getExamRevaluationsAssignedForValuationByStaff | |
0.00% |
0 / 1 |
42.00 | |
0.00% |
0 / 45 |
|||
| getExamRevaluationsAssignedForValuationByStaffForReviewerEnabled | |
0.00% |
0 / 1 |
30.00 | |
0.00% |
0 / 40 |
|||
| getBatchExamRevaluationDates | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 18 |
|||
| getStudentsForExamRevaluationByStaff | |
0.00% |
0 / 1 |
42.00 | |
0.00% |
0 / 42 |
|||
| getRevaluationsAssignedForStaff | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 12 |
|||
| getExamRevaluationStudentsMarkDetailsBySubject | |
0.00% |
0 / 1 |
42.00 | |
0.00% |
0 / 63 |
|||
| finalizeStudentRevaluationExamMark | |
0.00% |
0 / 1 |
72.00 | |
0.00% |
0 / 35 |
|||
| saveStudentsForThirdValuation | |
0.00% |
0 / 1 |
42.00 | |
0.00% |
0 / 21 |
|||
| deleteStudentRevaluationFinalizedMark | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 12 |
|||
| getStudentAppliedRevaluations | |
0.00% |
0 / 1 |
90.00 | |
0.00% |
0 / 47 |
|||
| getExamRevaluationsPublishStatusByRequest | |
0.00% |
0 / 1 |
72.00 | |
0.00% |
0 / 53 |
|||
| getRevaluationTypeDetailsById | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 13 |
|||
| setStudentRevaluationSubjectRemarks | |
0.00% |
0 / 1 |
42.00 | |
0.00% |
0 / 14 |
|||
| getRevaluationStudentsWithSubjectDetailsByRequest | |
0.00% |
0 / 1 |
110.00 | |
0.00% |
0 / 44 |
|||
| getStudentThirdRevaluationDetails | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 13 |
|||
| getRevaluationAppliedStudentsWithSubjectByRequest | |
0.00% |
0 / 1 |
42.00 | |
0.00% |
0 / 36 |
|||
| getRevaluationPaymentDetails | |
0.00% |
0 / 1 |
240.00 | |
0.00% |
0 / 63 |
|||
| getExamRevalReceiptDetails | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 18 |
|||
| getRevaluationStudentSubjects | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 34 |
|||
| getRevaluationBatches | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 14 |
|||
| publishRevaluationBatchWise | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 23 |
|||
| getStaffAssignedStudentDetails | |
0.00% |
0 / 1 |
56.00 | |
0.00% |
0 / 28 |
|||
| getStaffAssignedRevaluationStudentDetails | |
0.00% |
0 / 1 |
42.00 | |
0.00% |
0 / 25 |
|||
| assignSameStudentsToRevaluationStudentsDigitalValuation | |
0.00% |
0 / 1 |
42.00 | |
0.00% |
0 / 39 |
|||
| getRevaluationAssignedStaffDetails | |
0.00% |
0 / 1 |
30.00 | |
0.00% |
0 / 15 |
|||
| assignExamRevaluationFacultyByRequest | |
0.00% |
0 / 1 |
30.00 | |
0.00% |
0 / 24 |
|||
| getRevaluationExamReportDigital | |
0.00% |
0 / 1 |
110.00 | |
0.00% |
0 / 94 |
|||
| copyToScrutinyStudentPreviousDigitalValuationMarks | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 25 |
|||
| getRevaluationTypeIdByOeRequest | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 19 |
|||
| checkIsRevaluationPublishedDateRange | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 23 |
|||
| getStudentRevaluationSubjectRemarks | |
0.00% |
0 / 1 |
42.00 | |
0.00% |
0 / 14 |
|||
| getDigitalValuationAssignedStaffs | |
0.00% |
0 / 1 |
30.00 | |
0.00% |
0 / 22 |
|||
| getDigitalValuationStaffAssignedStudents | |
0.00% |
0 / 1 |
42.00 | |
0.00% |
0 / 39 |
|||
| getMarkConfirmedStaffStudentsCountForSubjectValuation | |
0.00% |
0 / 1 |
56.00 | |
0.00% |
0 / 54 |
|||
| getRevaluationBatchesByStudentRegistered | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 16 |
|||
| <?php | |
| namespace com\linways\core\ams\professional\service; | |
| use com\linways\core\ams\professional\constant\examcontroller\CourseTypeConstants; | |
| use com\linways\core\ams\professional\dto\ExamType; | |
| use com\linways\core\ams\professional\exception\ProfessionalException; | |
| use com\linways\core\ams\professional\dto\SettingsConstents; | |
| use stdClass; | |
| class ExamRevaluationService extends BaseService | |
| { | |
| // private $batchService = BatchService::getInstance(); | |
| // /Condition 1 - Presence of a static member variable | |
| private static $_instance = null; | |
| private $mapper = []; | |
| // /Condition 2 - Locked down the constructor | |
| private function __construct() {} | |
| // 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; | |
| } | |
| /** | |
| * Get exam supplementary details by examSupplementaryId | |
| * @param Integer $examSupplementaryId | |
| * @return Array $examSupplementary | |
| * @throws ProfessionalException | |
| * @author Vishnu M | |
| */ | |
| public function getExamRevaluationStudentSubjects ( $revalId, $studentId ) { | |
| $revalId = $this->realEscapeString($revalId); | |
| $studentId = $this->realEscapeString($studentId); | |
| $sql = null; | |
| $revalSubjects = null; | |
| $sql = "SELECT s.subjectName, s.subjectDesc, erf.id AS feeId, erf.exam_fees_name as examFeeName, erss.examID FROM subjects s INNER JOIN exam e ON (s.subjectID = e.subjectID) INNER JOIN exam_revaluation_student_subjects erss ON ( erss.examID = e.examID ) INNER JOIN exam_revaluation_fees erf ON (erf.exam_revaluation_id = erss.exam_revaluation_id AND erss.exam_revaluation_fees_id = erf.id) WHERE erss.exam_revaluation_id = '$revalId' AND erss.studentID = '$studentId' ORDER BY erf.id ASC"; | |
| try { | |
| $revalSubjects = $this->executeQueryForList($sql); | |
| } | |
| catch(\Exception $e) { | |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); | |
| } | |
| return $revalSubjects; | |
| } | |
| /** | |
| * @param $request | |
| * @return Object|null | |
| * @author Vishnu M | |
| */ | |
| public function getExamRevaluations($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $conditions = null; | |
| $examRegDesc = null; | |
| $examregDetilsCondition = null; | |
| if ( $request->examType === ExamType::REGULAR ) { | |
| $conditions .= " AND ex.exam_registration_id IS NOT NULL AND ex.exam_supplementary_id IS NULL"; | |
| $examRegDesc = ", er.examregName AS examRegDesc "; | |
| $examregDetailsCondition = "INNER JOIN exam_registration er ON er.examregID = ex.exam_registration_id"; | |
| } | |
| else if ( $request->examType === ExamType::SUPPLY ) { | |
| $conditions .= " AND ex.exam_supplementary_id IS NOT NULL AND ex.exam_registration_id IS NULL"; | |
| $examRegDesc = ", es.supplyDesc AS examRegDesc "; | |
| $examregDetailsCondition = " INNER JOIN exam_supplementary es ON es.id = ex.exam_supplementary_id "; | |
| } | |
| if ( $request->revaluationId ) { | |
| $conditions .= " AND id = $request->revaluationId "; | |
| } | |
| if ($request->revaluationType) { | |
| $conditions .= " AND JSON_CONTAINS(ex.revaluationType, '{\"revaluationType\":\"$request->revaluationType\"}')"; | |
| } | |
| $sql = null; | |
| $revaluations = null; | |
| try { | |
| $sql = "SELECT | |
| ex.id, | |
| ex.exam_registration_id AS examRegId, | |
| ex.revalDesc AS name, | |
| ex.startDate, | |
| ex.endDate, | |
| ex.percentage, | |
| ex.margin, | |
| ex.memoNum, | |
| ex.memoDate, | |
| ex.finalizedFlag AS isFinalized, | |
| ex.subjectLimit, | |
| ex.exam_supplementary_id AS supplyRegId, | |
| ex.published AS isPublished, | |
| ex.fromDate, | |
| ex.toDate, | |
| ex.revaluationType | |
| $examRegDesc | |
| FROM | |
| exam_revaluation ex | |
| $examregDetailsCondition | |
| WHERE ex.id IS NOT NULL $conditions ORDER BY ex.id DESC"; | |
| $revaluations = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException ($e->getCode(), $e->getMessage()); | |
| } | |
| return $revaluations; | |
| } | |
| /** | |
| * Get all revaluation batches by revaluation id | |
| * @param $revaluationId | |
| * @return ObjectList | |
| * @author Vishnu M | |
| */ | |
| public function getExamRevaluationBatches($revaluationId) | |
| { | |
| $revaluationId = $this->realEscapeString($revaluationId); | |
| $sql = null; | |
| $batches = []; | |
| try { | |
| $sql = "SELECT | |
| erbg.id, | |
| erbg.exam_revaluation_id, | |
| erbg.batchID, | |
| b.batchName, | |
| s.semName, | |
| d.deptName, | |
| d.departmentDesc, | |
| cp.patternName, | |
| cp.patternDesc, | |
| erbg.startDate, | |
| erbg.endDate, | |
| erbg.subjectLimit, | |
| erbg.verificationDate | |
| FROM | |
| exam_revaluation_batch_groups erbg | |
| INNER JOIN | |
| batches b ON ( b.batchID = erbg.batchID ) | |
| INNER JOIN | |
| semesters s ON (s.semID = b.semID) | |
| INNER JOIN | |
| department d ON (d.deptID = b.deptID) | |
| INNER JOIN | |
| course_pattern cp ON (cp.patternID = b.patternID) | |
| WHERE | |
| exam_revaluation_id IN ( $revaluationId ) ORDER BY id DESC"; | |
| $batches = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException ($e->getCode(), $e->getMessage()); | |
| } | |
| return $batches; | |
| } | |
| /** | |
| * @param $request | |
| * @return array|Object | |
| * @author Vishnu M | |
| */ | |
| public function getExamRevaluationStudents($request) { | |
| $request = $this->realEscapeObject($request); | |
| $sql = null; | |
| $result = []; | |
| $conditions = null; | |
| if ( $request->batchId ) { | |
| $conditions .= " AND sa.batchID = $request->batchId "; | |
| } | |
| if ( $request->isFinalised ) { | |
| $conditions .= " AND rmf.approveMark = 1 "; | |
| } | |
| try { | |
| $sql = "SELECT | |
| sa.studentID as studentId, | |
| sa.regNo, | |
| sa.studentName | |
| FROM | |
| exam_revaluation_batch_groups erbg | |
| INNER JOIN | |
| exam_revaluation_student_details ersd ON (ersd.exam_revaluation_id = erbg.exam_revaluation_id) | |
| INNER JOIN | |
| studentaccount sa ON (sa.studentID = ersd.studentID | |
| AND sa.batchID = erbg.batchID) | |
| LEFT JOIN | |
| revaluation_marks_finalized rmf ON (rmf.studentID = ersd.studentID) | |
| WHERE | |
| erbg.exam_revaluation_id = '$request->revaluationId' AND ersd.paid = 1 $conditions "; | |
| $result = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException ($e->getCode(), $e->getMessage()); | |
| } | |
| return $result; | |
| } | |
| /** | |
| * Get all revaluation applied student detials | |
| * @param $request | |
| * @return Object|null | |
| * @author Vishnu M | |
| */ | |
| public function getRevaluationStudentDetails($request) { | |
| $request = $this->realEscapeObject($request); | |
| $sql = null; | |
| $revaluationStudentDetails = null; | |
| try { | |
| $courseTypeUG = CourseTypeConstants::UG; | |
| $sql = "SELECT | |
| sa.studentName, | |
| sa.regNo, | |
| sa.studentEmail, | |
| erf.exam_fees_name AS revaluationType, | |
| s.subjectName, | |
| s.subjectDesc, | |
| easvs.packetNo, | |
| im.internalMarks, | |
| IF(ct.course_Type = '$courseTypeUG', ee.mark, eef.mark) AS externalMarks, | |
| sta.staffName AS valuatedStaff | |
| FROM | |
| exam_revaluation_student_details ersd | |
| INNER JOIN | |
| studentaccount sa ON (sa.studentID = ersd.studentID) | |
| INNER JOIN | |
| exam_revaluation_student_subjects erss ON (erss.studentID = ersd.studentID | |
| AND ersd.exam_revaluation_id = erss.exam_revaluation_id) | |
| INNER JOIN | |
| exam e ON (e.examID = erss.examID) | |
| INNER JOIN | |
| subjects s ON (e.subjectID = s.subjectID) | |
| INNER JOIN | |
| exam_revaluation_fees erf ON (erss.exam_revaluation_fees_id = erf.id | |
| AND ersd.exam_revaluation_id = erf.exam_revaluation_id) | |
| INNER JOIN | |
| exam_answerSheetGroup_student_valuated_staff easvs ON (easvs.studentID = ersd.studentID | |
| AND easvs.examID = erss.examID) | |
| INNER JOIN | |
| staffaccounts sta ON (easvs.valuatedStaffId = sta.staffID) | |
| INNER JOIN | |
| batches b ON (b.batchID = e.batchID) | |
| INNER JOIN | |
| course_type ct ON ct.courseTypeID = b.courseTypeID | |
| LEFT JOIN | |
| internal_marks im ON (im.studentID = ersd.studentID | |
| AND im.subjectID = e.subjectID | |
| AND im.batchID = e.batchID | |
| AND im.semID = e.semID) | |
| LEFT JOIN | |
| exammarks_external ee ON (ee.examID = erss.examID | |
| AND ee.studentID = ersd.studentID) | |
| LEFT JOIN | |
| externalexammarks_finalized eef ON (eef.examID = erss.examID | |
| AND eef.studentID = ersd.studentID) | |
| WHERE | |
| ersd.paid = 1 AND ersd.approved = 1 | |
| AND ersd.exam_revaluation_id = $request->revaluationId "; | |
| $revaluationStudentDetails = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException ($e->getCode(), $e->getMessage()); | |
| } | |
| return $revaluationStudentDetails; | |
| } | |
| /** | |
| * Add Revaluation Notification | |
| * @param $request | |
| * @return $result | |
| * @throws ProfessionalException | |
| */ | |
| public function addExamRevaluationNotification ( $request) { | |
| $request = $this->realEscapeObject($request); | |
| $revaluationDetails = json_encode($request->revaluationDetails); | |
| $created_by = $_SESSION['adminID']; | |
| $created_date = date("Y-m-d"); | |
| $sql = null; | |
| $sql = "SELECT id from exam_revaluation_notification WHERE examRegistrationID = '$request->examRegId'"; | |
| $revaluation = $this->executeQueryForObject($sql); | |
| if ( empty($revaluation)){ | |
| $sql = "INSERT INTO exam_revaluation_notification (examRegistration, examRegistrationID, instruction, value, created_by, created_date) VALUES ('$request->examType', $request->examRegId, '$request->notificationContent','$revaluationDetails', $created_by,' $created_date')"; | |
| } | |
| else{ | |
| $sql = "UPDATE exam_revaluation_notification SET examRegistration = '$request->examType', instruction = '$request->notificationContent', value = '$revaluationDetails' WHERE examRegistrationID = $request->examRegId "; | |
| } | |
| try { | |
| $result = $this->executeQuery($sql); | |
| } | |
| catch(\Exception $e) { | |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); | |
| } | |
| return $result; | |
| } | |
| /** | |
| * Get Revaluation Notification | |
| * @param $examRegId | |
| * @return $result | |
| * @throws ProfessionalException | |
| */ | |
| public function getExamRevaluationNotification ( $examRegId) { | |
| $examRegId = $this->realEscapeObject($examRegId); | |
| $sql = null; | |
| $sql = "SELECT instruction, value from exam_revaluation_notification WHERE examRegistrationID = $examRegId"; | |
| try { | |
| $result = $this->executeQueryForObject($sql); | |
| } | |
| catch(\Exception $e) { | |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); | |
| } | |
| return $result; | |
| } | |
| /** | |
| * save Revaluation | |
| * @param $request | |
| * @return $result | |
| * @throws ProfessionalException | |
| */ | |
| public function saveExamRevaluation($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $examType = $request->examType; | |
| $examRegId = $request->examRegId; | |
| $revaluationDesc = $request->revaluationDesc; | |
| $percentage = $request->percentage; | |
| $margin = $request->margin; | |
| $isExistSql=""; | |
| $isExist=null; | |
| $updateQuery=""; | |
| $insertQuery=""; | |
| $revaluationProperties = $request->revaluationProperties ? json_encode($request->revaluationProperties) : "{}"; | |
| if((int)$request->revaluationTypeOrder){ | |
| $revalTypeName = ""; | |
| switch($request->revaluationTypeOrder){ | |
| case 1:$revalTypeName = "SCRUTINY"; | |
| break; | |
| case 2:$revalTypeName = "REVALUATION"; | |
| break; | |
| case 3:$revalTypeName = "REVIEW"; | |
| break; | |
| } | |
| $revaluationType = new stdClass; | |
| $revaluationType->order = "$request->revaluationTypeOrder"; | |
| $revaluationType->revaluationType = $revalTypeName; | |
| $revaluationProperties = json_encode($revaluationType); | |
| } | |
| if ($examType == ExamType::REGULAR) { | |
| // $isExistSql = "SELECT exam_registration_id,id FROM exam_revaluation WHERE exam_registration_id = '$examRegId'"; | |
| // $updateQuery = "UPDATE exam_revaluation SET revalDesc = '$revaluationDesc', percentage = '$percentage', margin = '$margin' | |
| // WHERE exam_registration_id = '$examRegId'"; | |
| $insertQuery ="INSERT into exam_revaluation (exam_registration_id,revalDesc,percentage,margin,revaluationType) | |
| values ('$examRegId','$revaluationDesc','$percentage','$margin','$revaluationProperties')"; | |
| } | |
| else if($examType == ExamType::SUPPLY) { | |
| // $isExistSql = "SELECT exam_registration_id,id FROM exam_revaluation WHERE exam_supplementary_id = '$examRegId'"; | |
| // $updateQuery = "UPDATE exam_revaluation SET revalDesc = '$revaluationDesc', percentage = '$percentage', margin = '$margin' | |
| // WHERE exam_supplementary_id = '$examRegId'"; | |
| $insertQuery = "INSERT into exam_revaluation (exam_supplementary_id,revalDesc,percentage,margin,revaluationType) | |
| values ('$examRegId','$revaluationDesc','$percentage','$margin','$revaluationProperties')"; | |
| } | |
| // try { | |
| // $isExist = $this->executeQueryForObject($isExistSql); | |
| // } catch (\Exception $e) { | |
| // throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| // } | |
| //insert or update into exam revaluation table | |
| if($isExist){ | |
| try { | |
| // $result = $this->executeQueryForObject($updateQuery); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| else{ | |
| try { | |
| $result = $this->executeQueryForObject($insertQuery); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| return $result; | |
| } | |
| /** | |
| * save Revaluation | |
| * @param $request | |
| * @return $result | |
| * @throws ProfessionalException | |
| */ | |
| public function addExamRevaluationBatches($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $batches = $request->batches; | |
| $revaluationId = $request->revaluationId; | |
| $startDate = $request->startDate; | |
| $endDate = $request->endDate; | |
| $oldStartDate = $request->oldStartDate; | |
| $oldEndDate = $request->oldEndDate; | |
| $verificationDate = $request->verificationDate; | |
| $subjectLimit = $request->subjectLimit; | |
| $insertQuery=""; | |
| $batchesString=""; | |
| //delete the entry of unselected batches if edit | |
| if ($request->isEdit == 1) { | |
| foreach ($batches as $batch){ | |
| $batch = (object) $batch; | |
| if($batchesString){ | |
| $batchesString = $batchesString .",$batch->id"; | |
| }else{ | |
| $batchesString = $batch->id; | |
| } | |
| } | |
| $deleteSql = "DELETE from exam_revaluation_batch_groups | |
| where exam_revaluation_id='$revaluationId' | |
| and startDate ='$oldStartDate' | |
| and endDate = '$oldEndDate' | |
| and batchID NOT IN($batchesString)"; | |
| if ($batchesString) { | |
| try { | |
| $result = $this->executeQueryForObject($deleteSql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| } | |
| if($request->isEdit==1){ | |
| foreach ($batches as $batch) { | |
| $batch= (object) $batch; | |
| //check entry exist for the batch in that revaluation id | |
| $isExist = "select batchID from exam_revaluation_batch_groups | |
| where batchID='$batch->id' | |
| AND exam_revaluation_id='$revaluationId'"; | |
| try { | |
| $resultExist = $this->executeQueryForObject($isExist); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| //update if already exist | |
| if($resultExist){ | |
| $updateQuery = "UPDATE exam_revaluation_batch_groups set startDate='$startDate' , | |
| endDate='$endDate' , | |
| verificationDate='$verificationDate' , | |
| subjectLimit ='$subjectLimit' | |
| WHERE batchID='$batch->id' | |
| AND exam_revaluation_id='$revaluationId'"; | |
| try { | |
| $result = $this->executeQueryForObject($updateQuery); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| //insert if not | |
| else{ | |
| if ($insertQuery) { | |
| $insertQuery = $insertQuery . ",('$revaluationId','$batch->id','$startDate','$endDate',$subjectLimit,'$verificationDate')"; | |
| } else { | |
| $insertQuery = "('$revaluationId','$batch->id','$startDate','$endDate',$subjectLimit,'$verificationDate')"; | |
| } | |
| } | |
| } | |
| }else{ | |
| foreach ($batches as $batch) { | |
| $batch = (object) $batch; | |
| if ($insertQuery) { | |
| $insertQuery = $insertQuery . ",('$revaluationId','$batch->id','$startDate','$endDate','$subjectLimit','$verificationDate')"; | |
| } else { | |
| $insertQuery = "('$revaluationId','$batch->id','$startDate','$endDate',$subjectLimit,'$verificationDate')"; | |
| } | |
| } | |
| } | |
| $sql = "INSERT into exam_revaluation_batch_groups (exam_revaluation_id,batchID,startDate,endDate,subjectLimit,verificationDate) | |
| values $insertQuery"; | |
| if ($insertQuery) { | |
| try { | |
| $result = $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| } | |
| /** | |
| * Get all revaluation batch groups revaluation id | |
| * @param $revaluationId | |
| * @return ObjectList | |
| * @author sibin | |
| */ | |
| public function getExamRevaluationBatchGroups($revaluationId) | |
| { | |
| $revaluationId = $this->realEscapeString($revaluationId); | |
| $sql = null; | |
| $batches = []; | |
| try { | |
| $sql = "SELECT | |
| distinct | |
| erbg.exam_revaluation_id, | |
| erbg.startDate, | |
| erbg.endDate, | |
| erbg.verificationDate, | |
| erbg.subjectLimit, | |
| erbg.startDate as oldStartDate, | |
| erbg.endDate as oldEndDate | |
| FROM | |
| exam_revaluation_batch_groups erbg | |
| WHERE | |
| erbg.exam_revaluation_id IN ( $revaluationId ) ORDER BY id DESC"; | |
| $batches = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $batches; | |
| } | |
| /** | |
| * Get all revaluation batch groups revaluation id | |
| * @param $revaluationId | |
| * @return ObjectList | |
| * @author sibin | |
| */ | |
| public function getBatchesInBatchGroups($group) | |
| { | |
| $group = $this->realEscapeObject($group); | |
| $sql = null; | |
| $batches = []; | |
| try { | |
| $sql = "SELECT | |
| erbg.batchID as id, | |
| b.batchName as name, | |
| b.batchDesc as description | |
| FROM | |
| exam_revaluation_batch_groups erbg | |
| INNER JOIN batches b | |
| ON b.batchID = erbg.batchID | |
| WHERE | |
| erbg.exam_revaluation_id IN ( $group->exam_revaluation_id ) | |
| and erbg.startDate ='$group->startDate' | |
| and erbg.endDate ='$group->endDate' | |
| ORDER BY id DESC"; | |
| $batches = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $batches; | |
| } | |
| /** | |
| * Get getExamRevaluationFees by revaluation id | |
| * @param $revaluationId | |
| * @return ObjectList | |
| * @author sibin | |
| */ | |
| public function getExamRevaluationFees($revaluationId) | |
| { | |
| $revaluationId = $this->realEscapeObject($revaluationId); | |
| $sql = null; | |
| $revaluationFees=""; | |
| try { | |
| $sql = "SELECT id, | |
| exam_fees_name, | |
| exam_fees_amount, | |
| exam_revaluation_id, | |
| isCommon, | |
| need_markentry | |
| from exam_revaluation_fees | |
| where exam_revaluation_id = '$revaluationId' | |
| order by id asc"; | |
| $revaluationFees = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $revaluationFees; | |
| } | |
| /** | |
| * Get getExamRevaluationFees by revaluation id | |
| * @param $revaluationId | |
| * @return ObjectList | |
| * @author sibin | |
| */ | |
| public function getSelectedPaymentMethods($revaluationId,$examTypeFlag) | |
| { | |
| $revaluationId = $this->realEscapeString($revaluationId); | |
| $examTypeFlag = $this->realEscapeString($examTypeFlag); | |
| $sql = null; | |
| $selectedPaymentMethods = ""; | |
| try { | |
| $sql = "SELECT exam_paymentmethod_id | |
| from exam_paymentmethod_settings | |
| where exam_registration_type_id ='$revaluationId' | |
| and exam_registration_type ='$examTypeFlag'"; | |
| $selectedPaymentMethods = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $selectedPaymentMethods; | |
| } | |
| /** | |
| * Get getExamRevaluationFees by revaluation id | |
| * @param $revaluationId | |
| * @return ObjectList | |
| * @author sibin | |
| */ | |
| public function saveAssignedExamRevaluationFees($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $sql = null; | |
| $saveAssignFees = ""; | |
| if($request->isUpdateFee == 1){ | |
| $sql = "UPDATE exam_revaluation_fees | |
| set exam_fees_name='$request->feeName' , | |
| exam_fees_amount='$request->feesAmount' , | |
| isCommon='$request->isCommon' , | |
| need_markentry ='$request->needMarkEntry' | |
| where id='$request->feeId' | |
| and exam_revaluation_id='$request->revaluationId'"; | |
| } | |
| else if($request->isUpdateFee == 0){ | |
| $staffTypeValue = !empty($request->staffType) ? "'$request->staffType'" : 'NULL'; | |
| $sql = "INSERT into exam_revaluation_fees | |
| (exam_fees_name, exam_fees_amount, exam_revaluation_id, isCommon, need_markentry,markEntryType) | |
| values ('$request->feeName', '$request->feesAmount', '$request->revaluationId', '$request->isCommon', '$request->needMarkEntry',$staffTypeValue)"; | |
| } | |
| try { | |
| $saveAssignFees = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $saveAssignFees; | |
| } | |
| /** | |
| * Get getExamRevaluationFees by revaluation id | |
| * @param $revaluationId | |
| * @return ObjectList | |
| * @author sibin | |
| */ | |
| public function changeExamPaymentMethod($paymentMethod) | |
| { | |
| $paymentMethod = (object) $this->realEscapeObject($paymentMethod); | |
| $sql = null; | |
| $selectedPaymentMethods = ""; | |
| $status = new stdClass; | |
| if($paymentMethod->isSelected ==1){ | |
| $sql = "INSERT into exam_paymentmethod_settings | |
| (exam_paymentmethod_id,exam_registration_type,exam_registration_type_id) | |
| values ('$paymentMethod->id','$paymentMethod->examTypeFlag','$paymentMethod->revaluationId') "; | |
| $status->process = "assign"; | |
| }else if($paymentMethod->isSelected == 0){ | |
| $existMethodsSql = "SELECT exam_paymentmethod_id | |
| from exam_paymentmethod_settings | |
| where exam_registration_type_id ='$paymentMethod->revaluationId' | |
| and exam_registration_type ='$paymentMethod->examTypeFlag'"; | |
| try { | |
| $existMethods = $this->executeQueryForList($existMethodsSql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| $count = count($existMethods); | |
| if ($count < 2) { | |
| return $status->status=0; | |
| } | |
| else { | |
| $sql = "DELETE from exam_paymentmethod_settings | |
| where exam_paymentmethod_id ='$paymentMethod->id' | |
| and exam_registration_type_id = '$paymentMethod->revaluationId' | |
| and exam_registration_type='$paymentMethod->examTypeFlag'"; | |
| $status->process="unAssign"; | |
| } | |
| } | |
| try { | |
| $selectedPaymentMethods = $this->executeQueryForObject($sql); | |
| $status->status=1; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $status; | |
| } | |
| /** | |
| * Get Revaluation Report | |
| * @param $revaluationId | |
| * @return $report | |
| * @throws ProfessionalException | |
| */ | |
| public function getExamRevaluationReport($request){ | |
| $request = $this->realEscapeObject($request); | |
| $revaluationTypeCondition=""; | |
| $revaluationId=""; | |
| $revaluationId=$request->revaluationId; | |
| $courseType=$request->courseType; | |
| $revaluationTypeId= (int)$request->revaluationTypeId; | |
| if($revaluationTypeId){ | |
| $revaluationTypeCondition = "and erss.exam_revaluation_fees_id = '$revaluationTypeId'"; | |
| } | |
| $condition =""; | |
| if($courseType == 'UG'){ | |
| $condition ="exammarks_external em ON (em.examID = erss.examID | |
| AND em.studentID = ersd.studentID)"; | |
| } | |
| else{ | |
| $condition ="externalexammarks_finalized em ON (em.examID = erss.examID | |
| AND em.studentID = ersd.studentID)"; | |
| } | |
| $sql = null; | |
| $revaluationStudentDetails = null; | |
| $sql="SELECT | |
| sa.studentName AS 'StudentName', | |
| sa.regNo AS 'RegisterNumber', | |
| sa.studentEmail AS 'StudentEmail', | |
| erf.exam_fees_name AS 'RevaluationType', | |
| s.subjectName AS 'SubjectCode', | |
| s.subjectDesc AS 'SubjectName', | |
| easvs.packetNo AS 'PacketNo', | |
| im.internalMarks AS 'InternalMarks', | |
| em.mark AS 'ExternalMarks', | |
| sta.staffName AS 'ValuatedStaff', | |
| b.batchName AS 'batchName' | |
| FROM | |
| exam_revaluation_student_details ersd | |
| INNER JOIN | |
| studentaccount sa ON (sa.studentID = ersd.studentID) | |
| INNER JOIN | |
| batches b ON (b.batchID = sa.batchID) | |
| INNER JOIN | |
| exam_revaluation_student_subjects erss ON (erss.studentID = ersd.studentID | |
| AND ersd.exam_revaluation_id = erss.exam_revaluation_id) | |
| INNER JOIN | |
| exam e ON (e.examID = erss.examID) | |
| INNER JOIN | |
| subjects s ON (e.subjectID = s.subjectID) | |
| INNER JOIN | |
| exam_revaluation_fees erf ON (erss.exam_revaluation_fees_id = erf.id | |
| AND ersd.exam_revaluation_id = erf.exam_revaluation_id) | |
| LEFT JOIN | |
| exam_answerSheetGroup_student_valuated_staff easvs ON (easvs.studentID = ersd.studentID | |
| AND easvs.examID = erss.examID) | |
| LEFT JOIN | |
| staffaccounts sta ON (easvs.valuatedStaffId = sta.staffID) | |
| LEFT JOIN | |
| internal_marks im ON (im.studentID = ersd.studentID | |
| AND im.subjectID = e.subjectID | |
| AND im.batchID = e.batchID | |
| AND im.semID = e.semID) | |
| LEFT JOIN | |
| $condition | |
| WHERE | |
| ersd.paid = 1 AND ersd.approved = 1 | |
| AND ersd.exam_revaluation_id = '$revaluationId' | |
| $revaluationTypeCondition | |
| ORDER BY sa.studentID"; | |
| try{ | |
| $revaluationStudentDetails = $this->executeQueryForList($sql); | |
| } | |
| catch(\Exception $e){ | |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); | |
| } | |
| return $revaluationStudentDetails; | |
| } | |
| /** | |
| * get revaluation fee types | |
| * @param revaluationId | |
| * @return List | |
| */ | |
| public function getRevaluationTypes($revaluationId){ | |
| $revaluationId = $this->realEscapeString($revaluationId); | |
| $sql=""; | |
| if($revaluationId){ | |
| $sql = "SELECT distinct erss.exam_revaluation_fees_id as id, | |
| erf.exam_fees_name as name | |
| FROM exam_revaluation_student_details ersd | |
| INNER JOIN exam_revaluation_student_subjects erss | |
| ON (erss.studentID = ersd.studentID | |
| AND ersd.exam_revaluation_id = erss.exam_revaluation_id) | |
| INNER JOIN exam_revaluation_fees erf | |
| ON (erss.exam_revaluation_fees_id = erf.id | |
| AND ersd.exam_revaluation_id = erf.exam_revaluation_id) | |
| WHERE ersd.paid = 1 | |
| AND ersd.approved = 1 | |
| AND ersd.exam_revaluation_id = '$revaluationId' | |
| order by erss.exam_revaluation_fees_id"; | |
| try{ | |
| $revaluationTypes = $this->executeQueryForList($sql); | |
| } | |
| catch(\Exception $e){ | |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); | |
| } | |
| return $revaluationTypes; | |
| } | |
| } | |
| /** | |
| * get revaluation fee types | |
| * @param revaluationId | |
| * @return List | |
| */ | |
| public function deleteRevaluation($revaluationId) | |
| { | |
| $revaluationId = $this->realEscapeString($revaluationId); | |
| $revaluation=""; | |
| $examTypeFlag = "revaluation"; | |
| $studentApplied=""; | |
| $sql = ""; | |
| $status = new stdClass; | |
| if ($revaluationId) { | |
| //check students applied or not | |
| $sql = "SELECT distinct exam_revaluation_id | |
| from exam_revaluation_student_details | |
| where exam_revaluation_id='$revaluationId'"; | |
| try { | |
| $revaluation = $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| if($revaluation){ | |
| $status->studentApplied = 1; | |
| return $status; | |
| }else{ | |
| $status->studentApplied = 0; | |
| } | |
| //check batch group assigned | |
| $sql = "SELECT distinct exam_revaluation_id | |
| from exam_revaluation_batch_groups | |
| where exam_revaluation_id='$revaluationId'"; | |
| try { | |
| $revaluationInBatchGroup = $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| if ($revaluationInBatchGroup) { | |
| $status->hasBatchGroup = 1; | |
| return $status; | |
| } else { | |
| $status->hasBatchGroup = 0; | |
| } | |
| //check fees defined | |
| $sql = "SELECT distinct exam_revaluation_id | |
| from exam_revaluation_fees | |
| where exam_revaluation_id='$revaluationId'"; | |
| try { | |
| $revaluationInFees = $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| if ($revaluationInFees) { | |
| $status->hasFeesDefined = 1; | |
| return $status; | |
| } else { | |
| $status->hasFeesDefined = 0; | |
| } | |
| if($status->studentApplied == 0 && $status->hasBatchGroup == 0 && $status->hasFeesDefined == 0){ | |
| //delete revaluation | |
| $sql = "DELETE from exam_revaluation | |
| where id='$revaluationId'"; | |
| $deletePaymentMethod = "DELETE from exam_paymentmethod_settings | |
| where exam_registration_type ='$examTypeFlag' | |
| and exam_registration_type_id='$revaluationId'"; | |
| try { | |
| $result = $this->executeQueryForObject($sql); | |
| $result = $this->executeQueryForObject($deletePaymentMethod); | |
| $status->deleteStatus=1; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| }else{ | |
| $status->deleteStatus = 0; | |
| return $status; | |
| } | |
| return $status; | |
| } | |
| } | |
| /** | |
| * delete revaluation batchgroup and fee types | |
| * @param request | |
| * @return status | |
| */ | |
| public function deleteBatchGroupAndFee($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $deleteType = $request->deleteType; | |
| $batchGroup = $request->batchGroup; | |
| $batchString=""; | |
| $fee = $request->feeType; | |
| $studentApplied = ""; | |
| $sql = ""; | |
| if($deleteType == "fee"){ | |
| $revaluationId = $fee->exam_revaluation_id; | |
| } | |
| elseif($deleteType == "batchGroup"){ | |
| $revaluationId = $batchGroup->exam_revaluation_id; | |
| //start reavalution students exist for batch group | |
| $getBatchesSql = "SELECT batchID from exam_revaluation_batch_groups | |
| where exam_revaluation_id='$batchGroup->exam_revaluation_id' | |
| and startDate ='$batchGroup->oldStartDate' | |
| and endDate = '$batchGroup->oldEndDate'"; | |
| try { | |
| $batches = $this->executeQueryForList($getBatchesSql); | |
| foreach ($batches as $batch) { | |
| if ($batchString) { | |
| $batchString = $batchString . ",$batch->batchID"; | |
| } else { | |
| $batchString = $batch->batchID; | |
| } | |
| } | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| $sql = "SELECT ersd.id,ersd.exam_revaluation_id,ersd.studentID,sa.batchID from exam_revaluation_student_details ersd | |
| INNER JOIN studentaccount sa | |
| on sa.studentID = ersd.studentID | |
| where ersd.exam_revaluation_id='$revaluationId' | |
| and sa.batchID in ($batchString)"; | |
| } | |
| $status = new stdClass; | |
| if($deleteType == "fee"){ | |
| // check students applied or not | |
| $sql = "SELECT distinct exam_revaluation_id | |
| from exam_revaluation_student_details | |
| where exam_revaluation_id='$revaluationId'"; | |
| } | |
| try { | |
| $revaluation = $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| if ($revaluation) { | |
| $status->studentApplied = 1; | |
| return $status; | |
| } else { | |
| $status->studentApplied = 0; | |
| } | |
| if ($status->studentApplied == 0) { | |
| //delete batchGroup | |
| if($deleteType=="fee"){ | |
| $sql = "DELETE from exam_revaluation_fees | |
| where exam_revaluation_id='$fee->exam_revaluation_id' | |
| and id='$fee->id'"; | |
| } | |
| elseif($deleteType == "batchGroup"){ | |
| $sql = "DELETE from exam_revaluation_batch_groups | |
| where exam_revaluation_id='$revaluationId' | |
| and startDate='$batchGroup->startDate' | |
| and endDate='$batchGroup->endDate'"; | |
| } | |
| try { | |
| $deleteStatus = $this->executeQueryForObject($sql); | |
| $status->deleteStatus = 1; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } else { | |
| $status->deleteStatus = 0; | |
| return $status; | |
| } | |
| return $status; | |
| } | |
| /** | |
| * get student applied status | |
| * @param request | |
| * @return status | |
| */ | |
| public function getStudentAppliedStatusByRequest($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $studentApplied = ""; | |
| $sql = ""; | |
| $status = new stdClass; | |
| if ($request->type == "assignFee") { | |
| //check students applied or not | |
| $sql = "SELECT distinct exam_revaluation_id | |
| from exam_revaluation_student_details | |
| where exam_revaluation_id='$request->revaluationId'"; | |
| try { | |
| $revaluation = $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| if ($revaluation) { | |
| $status->studentApplied = 1; | |
| return $status; | |
| } else { | |
| $status->studentApplied = 0; | |
| } | |
| return $status; | |
| } | |
| //for batch group | |
| else if($request->type == "batchGroup"){ | |
| $revaluation=""; | |
| $batchString = ""; | |
| $batches=""; | |
| $batchGroup= $request->group; | |
| $revaluationId = $batchGroup->exam_revaluation_id; | |
| //start reavalution students exist for batch group | |
| $getBatchesSql = "SELECT batchID from exam_revaluation_batch_groups | |
| where exam_revaluation_id='$batchGroup->exam_revaluation_id' | |
| and startDate ='$batchGroup->oldStartDate' | |
| and endDate = '$batchGroup->oldEndDate'"; | |
| try { | |
| $batches = $this->executeQueryForList($getBatchesSql); | |
| foreach ($batches as $batch) { | |
| if ($batchString) { | |
| $batchString = $batchString . ",$batch->batchID"; | |
| } else { | |
| $batchString = $batch->batchID; | |
| } | |
| } | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| $sql = "SELECT ersd.id,ersd.exam_revaluation_id,ersd.studentID,sa.batchID from exam_revaluation_student_details ersd | |
| INNER JOIN studentaccount sa | |
| on sa.studentID = ersd.studentID | |
| where ersd.exam_revaluation_id='$revaluationId' | |
| and sa.batchID in ($batchString)"; | |
| try { | |
| $revaluation = $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| if($revaluation){ | |
| $status->studentApplied = 1; | |
| } | |
| else{ | |
| $status->studentApplied = 0; | |
| } | |
| return $status; | |
| } | |
| } | |
| /** | |
| * get revaluation fee types by request | |
| * @param request | |
| * @return List | |
| */ | |
| public function getRevaluationTypesByRequest($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $markEntryNeeded=""; | |
| $typeIdCondition=""; | |
| if($request->markEntryStatus){ | |
| $markEntryNeeded ="AND erf.need_markentry = '$request->markEntryStatus'"; | |
| } | |
| if($request->typeId){ | |
| $typeIdCondition = "AND erf.id = '$request->typeId'"; | |
| } | |
| $sql = ""; | |
| if ($request->revaluationId) { | |
| $sql = "SELECT distinct erss.exam_revaluation_fees_id as id, | |
| erf.exam_fees_name as name | |
| FROM exam_revaluation_student_details ersd | |
| INNER JOIN exam_revaluation_student_subjects erss | |
| ON (erss.studentID = ersd.studentID | |
| AND ersd.exam_revaluation_id = erss.exam_revaluation_id) | |
| INNER JOIN exam_revaluation_fees erf | |
| ON (erss.exam_revaluation_fees_id = erf.id | |
| AND ersd.exam_revaluation_id = erf.exam_revaluation_id) | |
| WHERE ersd.paid = 1 | |
| AND ersd.approved = 1 | |
| AND ersd.exam_revaluation_id = '$request->revaluationId' | |
| $markEntryNeeded | |
| $typeIdCondition | |
| order by erss.exam_revaluation_fees_id"; | |
| try { | |
| $revaluationTypes = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $revaluationTypes; | |
| } | |
| } | |
| /** | |
| * get revaluation fee types by request | |
| * @param request | |
| * @return List | |
| */ | |
| public function getRevaluationExamSubjects($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $sql = $condition = ""; | |
| if($request->revaluationTypeId){ | |
| $condition .=" AND erss.exam_revaluation_fees_id ='$request->revaluationTypeId' "; | |
| } | |
| if ($request->subjectId) { | |
| $condition .= " AND e.subjectID IN ($request->subjectId) "; | |
| } | |
| if ($request->revaluationId) { | |
| $sql = "SELECT distinct (erss.examID), | |
| s.subjectID, | |
| s.subjectName,s.subjectDesc,e.examregID,e.supply_examreg_id, | |
| s.subjectID as id,CONCAT(s.subjectName,' [',s.subjectDesc,']') as name | |
| from exam_revaluation_student_subjects erss | |
| inner join exam e | |
| on e.examID = erss.examID | |
| inner join subjects s | |
| on s.subjectID = e.subjectID | |
| where erss.exam_revaluation_id='$request->revaluationId' | |
| $condition | |
| group by(s.subjectID)"; | |
| try { | |
| $revaluationExamSubjects = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $revaluationExamSubjects; | |
| } | |
| } | |
| /** | |
| * get revaluation fee types by request | |
| * @param request | |
| * @return List | |
| */ | |
| public function getRevaluationExamDetails($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $sql = ""; | |
| $exam_registration_type=""; | |
| if ($request->revaluationId) { | |
| $sql = "SELECT id,revalDesc,exam_registration_id as regular_examRegId,exam_supplementary_id as supply_examRegId,revaluationType | |
| from exam_revaluation | |
| where id='$request->revaluationId'"; | |
| try { | |
| $revaluation = $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| if($revaluation->regular_examRegId){ | |
| $detailsSql = "SELECT examregID as id,examregName as examregName,0 as isSupply,examMonth,examYear | |
| from exam_registration | |
| where examregID='$revaluation->regular_examRegId'"; | |
| $exam_registration_type = "REGULAR"; | |
| } | |
| else if($revaluation->supply_examRegId){ | |
| $detailsSql = "SELECT id as id,supplyDesc as examregName,1 as isSupply,examMonth,examYear | |
| from exam_supplementary | |
| where id='$revaluation->supply_examRegId'"; | |
| $exam_registration_type="SUPPLY"; | |
| } | |
| try { | |
| $revaluationDetails = $this->executeQueryForObject($detailsSql); | |
| $revaluationDetails->exam_registration_type= $exam_registration_type; | |
| $revaluationDetails->revaluationName = $revaluation->revalDesc; | |
| $revaluationDetails->revaluationType = $revaluation->revaluationType; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $revaluationDetails; | |
| } | |
| } | |
| /** | |
| * get exams by request | |
| * @param request | |
| * @return List | |
| */ | |
| public function getExamDetailsByExamRegId($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $sql = ""; | |
| $exam_registration_type = ""; | |
| if ($request->examregID) { | |
| $condition = "AND examregID='$request->examregID'"; | |
| } else if ($request->supply_examreg_id) { | |
| $condition = "AND supply_examreg_id='$request->supply_examreg_id'"; | |
| } | |
| $sql = "SELECT examID from exam | |
| where subjectID ='$request->subjectId' | |
| $condition"; | |
| try { | |
| $exams = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $exams; | |
| } | |
| /** | |
| * get exams by request | |
| * @param request | |
| * @return List | |
| */ | |
| public function getRevaluationStudentsBySubject($request) | |
| { | |
| $examIdString=""; | |
| $thirdValStudentsCondition = ""; | |
| $request = $this->realEscapeObject($request); | |
| $exams= $request->exams; | |
| foreach($exams as $exam){ | |
| if($examIdString){ | |
| $examIdString = $examIdString .",$exam->examID"; | |
| }else{ | |
| $examIdString = $exam->examID; | |
| } | |
| } | |
| if ($request->courseType == CourseTypeConstants::UG || $request->courseType == CourseTypeConstants::BPED|| $request->courseType == CourseTypeConstants::UG_PRO) { | |
| $markCondition = "exammarks_external em ON (em.examID = erss.examID | |
| AND em.studentID = erss.studentID)"; | |
| }else { | |
| $markCondition = "externalexammarks_finalized em ON (em.examID = erss.examID | |
| AND em.studentID = erss.studentID)"; | |
| } | |
| if($request->valCount == 3){ | |
| $thirdValStudentsCondition = " INNER JOIN externalexam_thirdvalstudents eth ON eth.studentID = erss.studentID AND eth.examID = erss.examID AND eth.revaluationFlag=1 "; | |
| } | |
| $sql = ""; | |
| $sql = "SELECT distinct (erss.studentID), | |
| erss.id, | |
| sa.studentID, | |
| sa.regNo, | |
| sa.studentName, | |
| erss.examID, | |
| em.mark AS 'oldMark', | |
| erm.mark AS 'savedMark', | |
| emf.mark AS 'finalizedMark', | |
| efn.false_number AS 'falseNumber', | |
| e.examTotalMarks, | |
| im.internalMarks, | |
| ims.maxInternalMarks, | |
| em.mark AS 'mark1', | |
| erm.mark AS 'mark2', | |
| ermt.mark AS 'mark3' | |
| from exam_revaluation_student_subjects erss | |
| inner join studentaccount sa | |
| on sa.studentID=erss.studentID | |
| inner join exam_revaluation_student_details ersd | |
| on ersd.exam_revaluation_id =erss.exam_revaluation_id | |
| and ersd.studentID = erss.studentID | |
| left join $markCondition | |
| left join exam_revaluation_marks erm | |
| on erm.exam_revaluation_id = erss.exam_revaluation_id | |
| and erm.studentID = erss.studentID | |
| and erm.examID = erss.examID | |
| left join revaluation_marks_finalized emf | |
| on emf.exam_revaluation_id = erss.exam_revaluation_id | |
| and emf.studentID = erss.studentID | |
| and emf.examID = erss.examID | |
| left join examcontroller_false_number efn | |
| on efn.studentID = sa.studentID | |
| and efn.examID = erss.examID | |
| INNER JOIN exam e ON e.examID= erss.examID | |
| LEFT JOIN internal_marks im | |
| on im.studentID = sa.studentID | |
| and im.subjectID = e.subjectID | |
| and im.batchID = e.batchId | |
| and im.semID = e.semID | |
| LEFT JOIN internal_marks_settings ims | |
| on ims.subjectID = e.subjectID | |
| and ims.batchID = e.batchId | |
| and ims.semID = e.semID | |
| LEFT JOIN exam_revaluation_marks_thirdval ermt ON ermt.studentID = erss.studentID AND ermt.examID = erss.examID AND ermt.exam_revaluation_id = erss.exam_revaluation_id | |
| $thirdValStudentsCondition | |
| where ersd.paid=1 | |
| and erss.exam_revaluation_id='$request->revaluationId' | |
| and erss.exam_revaluation_fees_id='$request->revaluationTypeId' | |
| and erss.examID in($examIdString) | |
| order by $request->orderBy"; | |
| try { | |
| $studentList = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $studentList; | |
| } | |
| /** | |
| * get exams by request | |
| * @param request | |
| * @return List | |
| */ | |
| public function saveExamRevaluationMarks($request) | |
| { | |
| $result = new stdClass; | |
| $result->madeChanges = 0; | |
| $request = $this->realEscapeObject($request); | |
| $studentList = $request->studentList; | |
| $valueString=""; | |
| $staffType = $request->staffType ? $request->staffType : "EXAM CONTROLLER"; | |
| $staffSql = "SELECT staffID from external_examiners limit 1"; | |
| try { | |
| $staffDetails = $this->executeQueryForList($staffSql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| $staffId = $staffDetails[0]->staffID; | |
| foreach($studentList as $student){ | |
| $student = (object) $student; | |
| //check isExist and if yes delete marks | |
| if(!$student->savedMark || $student->savedMark == "null"){ | |
| $isExistDeleteSql = "select mark from exam_revaluation_marks | |
| where studentID='$student->studentID' | |
| and exam_revaluation_id='$request->revaluationId' | |
| and examID='$student->examID'"; | |
| try { | |
| $isExistDelete = $this->executeQueryForList($isExistDeleteSql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| if ($isExistDelete) { | |
| $deleteSql = "DELETE from exam_revaluation_marks | |
| where studentID='$student->studentID' | |
| and exam_revaluation_id='$request->revaluationId' | |
| and examID='$student->examID'"; | |
| try { | |
| $this->executeQueryForList($deleteSql); | |
| $result->madeChanges = 1; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| } | |
| //end delete | |
| //check isExist and if yes update marks | |
| else if(($student->finalizedMark=="null" || !$student->finalizedMark ) && $student->savedMark && $student->savedMark!="null"){ | |
| $isExistSql = "select mark from exam_revaluation_marks | |
| where studentID='$student->studentID' | |
| and exam_revaluation_id='$request->revaluationId' | |
| and examID='$student->examID'"; | |
| try { | |
| $isExist = $this->executeQueryForList($isExistSql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| if($isExist){ | |
| $updateSql = "UPDATE exam_revaluation_marks | |
| set mark='$student->savedMark', | |
| staffID='$staffId' | |
| where studentID='$student->studentID' | |
| and exam_revaluation_id='$request->revaluationId' | |
| and examID='$student->examID'"; | |
| try { | |
| $this->executeQueryForList($updateSql); | |
| $result->madeChanges = 1; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| //end | |
| else { | |
| if ($valueString) { | |
| $valueString = $valueString . ",('$student->studentID' , '$request->revaluationId' , '$student->examID', '$staffId', '$student->savedMark', '$staffType')"; | |
| } else { | |
| $valueString = "('$student->studentID' , '$request->revaluationId' , '$student->examID', '$staffId', '$student->savedMark', '$staffType')"; | |
| } | |
| } | |
| } | |
| } | |
| $sql = ""; | |
| $exam_registration_type = ""; | |
| $sql = "INSERT into exam_revaluation_marks(studentID,exam_revaluation_id,examID,staffID,mark,staffType) | |
| values $valueString"; | |
| //insert mark as new | |
| if($valueString){ | |
| try { | |
| $this->executeQueryForList($sql); | |
| $result->madeChanges = 1; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| return $result; | |
| } | |
| /** | |
| * get exams by request | |
| * @param request | |
| * @return List | |
| */ | |
| public function finalizeExamRevaluationMarks($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $studentList = $request->studentList; | |
| $valueString=""; | |
| $isExistSql=""; | |
| $result = new stdClass; | |
| $result->isFinalized = 0; | |
| foreach($studentList as $student){ | |
| $student = (object) $student; | |
| //check if already exist | |
| if ($student->savedMark && $student->savedMark != "null") { | |
| $isExistSql = "select mark from revaluation_marks_finalized | |
| where studentID='$student->studentID' | |
| and exam_revaluation_id='$request->revaluationId' | |
| and examID='$student->examID'"; | |
| try { | |
| $isExist = $this->executeQueryForList($isExistSql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| if($isExist){ | |
| if($request->finalizeAgain && $student->finalizedMark){ | |
| $sqlUpdate = "UPDATE revaluation_marks_finalized set mark = '$student->finalizedMark' WHERE examID= $student->examID AND exam_revaluation_id = $request->revaluationId AND studentID = $student->studentID"; | |
| $this->executeQuery($sqlUpdate); | |
| $updateSql2 = "UPDATE exam_revaluation_marks set mark='$student->finalizedMark' where studentID='$student->studentID' and exam_revaluation_id='$request->revaluationId' and examID='$student->examID'"; | |
| $this->executeQuery($updateSql2); | |
| if($student->finalizedMark != $student->savedMark){ | |
| $result->isFinalized=1; | |
| } | |
| } | |
| $sql1 = "UPDATE exam_revaluation set finalizedFlag = 1 WHERE id= $request->revaluationId"; | |
| $this->executeQuery($sql1); | |
| continue; | |
| } | |
| else{ | |
| //insert query | |
| if ($valueString) { | |
| $valueString = $valueString . ",('$student->examID','$student->studentID' , '$student->savedMark' , '$request->staffId' ,'$request->revaluationId',1)"; | |
| } else { | |
| $valueString = "('$student->examID','$student->studentID' , '$student->savedMark' , '$request->staffId' ,'$request->revaluationId',1)"; | |
| } | |
| } | |
| } | |
| } | |
| $sql = ""; | |
| $exam_registration_type = ""; | |
| $sql = "INSERT into revaluation_marks_finalized(examID,studentID,mark,staffID,exam_revaluation_id,approveMark) | |
| values $valueString"; | |
| $sql1 = "UPDATE exam_revaluation set finalizedFlag = 1 WHERE id= $request->revaluationId"; | |
| //insert mark as new | |
| if($valueString){ | |
| try { | |
| $this->executeQueryForList($sql); | |
| $this->executeQuery($sql1); | |
| $result->isFinalized=1; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| return $result; | |
| } | |
| /** | |
| * get FinalizedExamRevaluationMarks by request | |
| * @param request | |
| * @return List | |
| */ | |
| public function getFinalizedExamRevaluationMarks($request) | |
| { | |
| $examIdString = ""; | |
| $request = $this->realEscapeObject($request); | |
| $exams = $request->exams; | |
| foreach ($exams as $exam) { | |
| if ($examIdString) { | |
| $examIdString = $examIdString . ",$exam->examID"; | |
| } else { | |
| $examIdString = $exam->examID; | |
| } | |
| } | |
| $sql = ""; | |
| $sql = "SELECT id,studentID,examID,mark from revaluation_marks_finalized erf | |
| where erf.exam_revaluation_id='$request->revaluationId' | |
| and erf.examID in($examIdString)"; | |
| try { | |
| $studentMarks = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $studentMarks; | |
| } | |
| /** | |
| * get revaluation Details | |
| * @param revaluationId | |
| * @return List | |
| */ | |
| public function getRevaluationDetailsById($revaluationId){ | |
| $revaluationId = $this->realEscapeString($revaluationId); | |
| $sql=""; | |
| if($revaluationId){ | |
| $sql = "SELECT * from exam_revaluation where id = $revaluationId"; | |
| try{ | |
| $revaluationDetails = $this->executeQueryForList($sql); | |
| } | |
| catch(\Exception $e){ | |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); | |
| } | |
| return $revaluationDetails[0]; | |
| } | |
| } | |
| /** | |
| * Get Revaluation Report | |
| * @param $revaluationId | |
| * @return $report | |
| * @throws ProfessionalException | |
| */ | |
| public function getRevaluationExamReport($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $revaluationTypeCondition = ""; | |
| $revaluationId = ""; | |
| $subjectBatchCondition=""; | |
| $revaluationId = $request->revaluationId; | |
| $courseType = $request->courseType; | |
| $revaluationTypeIds = is_array($request->revaluationTypeId) ? implode(', ', $request->revaluationTypeId): $request->revaluationTypeId; | |
| if ($request->revaluationTypeId) { | |
| $revaluationTypeCondition = "and erss.exam_revaluation_fees_id IN ($revaluationTypeIds)"; | |
| } | |
| $condition = ""; | |
| if ($courseType == CourseTypeConstants::UG || $courseType == CourseTypeConstants::BPED) { | |
| $condition = "exammarks_external em ON (em.examID = erss.examID | |
| AND em.studentID = ersd.studentID)"; | |
| } else { | |
| $condition = "externalexammarks_finalized em ON (em.examID = erss.examID | |
| AND em.studentID = ersd.studentID)"; | |
| } | |
| if($request->subjectId){ | |
| $subjectIds = implode(",", $request->subjectId); | |
| $subjectBatchCondition .= "AND s.subjectID IN ($subjectIds)"; | |
| } | |
| if ($request->batchId) { | |
| $batchIds = implode(",", $request->batchId); | |
| $subjectBatchCondition .= " AND sa.batchID IN ($batchIds)"; | |
| } | |
| if ($request->studentId) { | |
| $subjectBatchCondition .= " AND ersd.studentID IN ($request->studentId)"; | |
| } | |
| if($request->groupByStudents){ | |
| $groupBy = " GROUP BY sa.studentID"; | |
| } | |
| $sql = null; | |
| $revaluationStudentDetails = null; | |
| $sql = "SELECT | |
| distinct (ersd.studentID), | |
| sa.studentName AS 'StudentName', | |
| sa.regNo AS 'RegisterNumber', | |
| sa.studentEmail AS 'StudentEmail', | |
| sa.studentPhone, | |
| erf.id AS 'revaluationTypeId', | |
| erf.exam_fees_name AS 'RevaluationType', | |
| s.subjectID AS 'subjectId', | |
| s.subjectName AS 'SubjectCode', | |
| s.subjectDesc AS 'SubjectName', | |
| easvs.packetNo AS 'PacketNo', | |
| im.internalMarks AS 'InternalMarks', | |
| em.mark AS 'ExternalMarks', | |
| sta.staffName AS 'ValuatedStaff', | |
| b.batchName AS 'batchName', | |
| efn.false_number AS 'falseNumber', | |
| e.examID, | |
| e.examTotalMarks as externalMaxMark, | |
| ims.maxInternalMarks as internalMaxMark, | |
| erm.mark as mark1, | |
| ermt.mark as mark2 | |
| FROM | |
| exam_revaluation_student_details ersd | |
| INNER JOIN | |
| studentaccount sa ON (sa.studentID = ersd.studentID) | |
| INNER JOIN | |
| batches b ON (b.batchID = sa.batchID) | |
| INNER JOIN | |
| exam_revaluation_student_subjects erss ON (erss.studentID = ersd.studentID | |
| AND ersd.exam_revaluation_id = erss.exam_revaluation_id) | |
| INNER JOIN | |
| exam e ON (e.examID = erss.examID) | |
| INNER JOIN | |
| subjects s ON (e.subjectID = s.subjectID) | |
| INNER JOIN | |
| exam_revaluation_fees erf ON (erss.exam_revaluation_fees_id = erf.id | |
| AND ersd.exam_revaluation_id = erf.exam_revaluation_id) | |
| LEFT JOIN | |
| exam_answerSheetGroup_student_valuated_staff easvs ON (easvs.studentID = ersd.studentID | |
| AND easvs.examID = erss.examID) | |
| LEFT JOIN | |
| staffaccounts sta ON (easvs.valuatedStaffId = sta.staffID) | |
| LEFT JOIN | |
| internal_marks im ON (im.studentID = ersd.studentID | |
| AND im.subjectID = e.subjectID | |
| AND im.batchID = e.batchID | |
| AND im.semID = e.semID) | |
| LEFT JOIN | |
| examcontroller_false_number efn ON (efn.studentID = ersd.studentID | |
| AND efn.examID = erss.examID) | |
| LEFT JOIN internal_marks_settings ims ON ims.batchID = e.batchID AND ims.semID = e.semID AND ims.subjectID = e.subjectID | |
| LEFT JOIN exam_revaluation_marks erm ON erm.exam_revaluation_id = ersd.exam_revaluation_id AND erm.examID = erss.examID AND erm.studentID = ersd.studentID | |
| LEFT JOIN exam_revaluation_marks_thirdval ermt ON erm.exam_revaluation_id = ersd.exam_revaluation_id AND ermt.examID = erss.examID AND ermt.studentID = ersd.studentID | |
| LEFT JOIN | |
| $condition | |
| WHERE | |
| ersd.paid = 1 | |
| AND ersd.exam_revaluation_id = '$revaluationId' | |
| $revaluationTypeCondition | |
| $subjectBatchCondition | |
| $groupBy | |
| ORDER BY sa.regNo,s.subjectDesc,erf.exam_fees_name"; | |
| try { | |
| $revaluationStudentDetails = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $revaluationStudentDetails; | |
| } | |
| /** | |
| * get revaluationTypes by id | |
| * @param $revaluationTypeIds | |
| * @return List | |
| */ | |
| public function getRevaluationTypesByIds($revaluationTypeIds) | |
| { | |
| $revaluationTypeIds = $this->realEscapeArray($revaluationTypeIds); | |
| $revaluationTypeIds = implode(', ', $revaluationTypeIds); | |
| $sql = ""; | |
| if ($revaluationTypeIds) { | |
| $sql = "SELECT id,exam_fees_name AS 'name' from exam_revaluation_fees where id IN($revaluationTypeIds)"; | |
| try { | |
| $revaluationTypes = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $revaluationTypes; | |
| } | |
| } | |
| /** | |
| * get ExamRegistrationDetailsByRevaluationId | |
| * @param $revaluationId | |
| * @return Object | |
| */ | |
| public function getExamRegistrationDetailsByRevaluationId($revaluationId) | |
| { | |
| $revaluationId = $this->realEscapeString($revaluationId); | |
| $sql = ""; | |
| if ($revaluationId) { | |
| $sql = "SELECT exr.id,exr.exam_registration_id as 'examRegId',exr.exam_supplementary_id as 'supplyExamRegId',exr.revalDesc, | |
| er.examregName as regularExamRegName,er.examYear as regularExamYear , | |
| es.supplyDesc as supplyExamRegName,es.examYear as supplyExamYear,es.examMonth as supplyExamMonth,er.examMonth as regularExamMonth | |
| from exam_revaluation exr | |
| LEFT JOIN exam_registration er ON er.examregID = exr.exam_registration_id | |
| LEFT JOIN exam_supplementary es ON es.id = exr.exam_supplementary_id | |
| where exr.id IN($revaluationId)"; | |
| try { | |
| $revaluationExamReg = $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $revaluationExamReg; | |
| } | |
| } | |
| /** | |
| * get revalution subjects | |
| * @param $revaluationId | |
| * @param $subjectId | |
| * @return Object | |
| */ | |
| public function getRevaluationSubjects($request) | |
| { | |
| $revaluationId = $this->realEscapeString($request->revaluationId); | |
| $batchId = $this->realEscapeArray($request->batchId); | |
| $subjectList =""; | |
| $sql = ""; | |
| $conditions = ""; | |
| if (!empty($batchId)) { | |
| $batchIds = implode(",", $batchId); | |
| $conditions .=" and sa.batchID IN ($batchIds)"; | |
| } | |
| if($request->filters->groupBySubject){ | |
| $conditions .= " GROUP BY s.subjectID"; | |
| } | |
| $sql = "SELECT distinct (erss.examID),erss.exam_revaluation_id,s.subjectID as 'id',CONCAT(s.subjectName,' [ ',s.subjectDesc,' ]') as 'name',s.subjectName,s.subjectDesc from exam_revaluation_student_subjects erss | |
| INNER JOIN studentaccount sa ON sa.studentID = erss.studentID | |
| INNER JOIN exam e ON e.examID = erss.examID | |
| INNER JOIN subjects s ON s.subjectID = e.subjectID | |
| WHERE erss.exam_revaluation_id = '$revaluationId' $conditions"; | |
| try { | |
| $subjectList = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $subjectList; | |
| } | |
| /** | |
| * get revalutions | |
| * @param $examType | |
| * @param $examRegId | |
| * @return Object | |
| */ | |
| public function getRevaluationByExamRegistration($request) | |
| { | |
| $request= $this->realEscapeObject($request); | |
| if($request->examType === ExamType::REGULAR){ | |
| $condition = "exam_registration_id = $request->examRegId"; | |
| }else if ($request->examType === ExamType::SUPPLY){ | |
| $condition = "exam_supplementary_id = $request->examRegId"; | |
| } | |
| $sql = ""; | |
| $revaluations=""; | |
| $sql = "SELECT id,revalDesc as 'name' from exam_revaluation | |
| WHERE $condition "; | |
| try { | |
| $revaluations = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $revaluations; | |
| } | |
| /** | |
| * upload revaluation marks from excel | |
| * @param $insertValues | |
| */ | |
| public function uploadRevaluationMarksFromExcel($insertStudentDetailValues, $insertStudentSubjectValues, $insertStudentMarkValues, $insertStudentMarkValuesReval){ | |
| $insertStudentDetailValues = implode(",", $insertStudentDetailValues); | |
| $insertStudentSubjectValues = implode(",", $insertStudentSubjectValues); | |
| $insertStudentMarkValues = implode(",", $insertStudentMarkValues); | |
| $insertStudentMarkValuesReval = implode(",", $insertStudentMarkValuesReval); | |
| $result= new stdClass; | |
| $insertStudentDetailSql = "INSERT into exam_revaluation_student_details (exam_revaluation_id,studentID,paid,approved) | |
| values $insertStudentDetailValues"; | |
| $insertStudentSubjectSql = "INSERT into exam_revaluation_student_subjects(studentID,examID,exam_revaluation_id) values $insertStudentSubjectValues"; | |
| $insertStudentMarkSql = "INSERT into revaluation_marks_finalized(examID,studentID,mark,staffID,adminID,exam_revaluation_id,approveMark) | |
| values $insertStudentMarkValues"; | |
| $insertStudentMarkSqlNonFinal = "INSERT into exam_revaluation_marks(studentID,exam_revaluation_id,examID,staffID,mark) | |
| values $insertStudentMarkValuesReval"; | |
| try { | |
| if($insertStudentDetailValues){ | |
| $this->executeQueryForObject($insertStudentDetailSql); | |
| $result->result1 = true; | |
| } | |
| if($insertStudentSubjectValues){ | |
| $this->executeQueryForObject($insertStudentSubjectSql); | |
| $result->result2 = true; | |
| } | |
| if($insertStudentMarkValues){ | |
| $this->executeQueryForObject($insertStudentMarkSql); | |
| $result->result3 = true; | |
| } | |
| if ($insertStudentMarkValuesReval) { | |
| $this->executeQueryForObject($insertStudentMarkSqlNonFinal); | |
| $result->result4 = true; | |
| } | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $result; | |
| } | |
| /** | |
| * @param $studentId,revaluation Id | |
| */ | |
| public function checkRevaluationByStudent($request){ | |
| $request = $this->realEscapeObject($request); | |
| $revaluation = ""; | |
| $sql = "SELECT exam_revaluation_id from exam_revaluation_student_details | |
| WHERE exam_revaluation_id ='$request->revaluationId' AND studentID = '$request->studentId'"; | |
| try { | |
| $revaluation = $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $revaluation; | |
| } | |
| /** | |
| * @param $studentId,revaluation Id,ExamId | |
| */ | |
| public function checkRevaluationExamByStudent($request){ | |
| $request = $this->realEscapeObject($request); | |
| $revaluationExam = ""; | |
| $sql = "SELECT id from exam_revaluation_student_subjects | |
| WHERE exam_revaluation_id ='$request->revaluationId' | |
| AND studentID = '$request->studentId' | |
| AND examID = '$request->examId'" ; | |
| try { | |
| $revaluationExam = $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $revaluationExam; | |
| } | |
| /** | |
| * @param $studentId,revaluation Id,ExamId | |
| */ | |
| public function checkRevaluationExamMarkByStudent($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $condition = ""; | |
| if($request->revaluationId){ | |
| $condition .=" AND exam_revaluation_id ='$request->revaluationId'"; | |
| } | |
| $revaluationExamMark = ""; | |
| $sql = "SELECT mark from revaluation_marks_finalized | |
| WHERE studentID = '$request->studentId' | |
| AND examID = '$request->examId' $condition"; | |
| try { | |
| $revaluationExamMark = $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $revaluationExamMark; | |
| } | |
| /** | |
| * @param $studentId,revaluation Id,ExamId | |
| */ | |
| public function checkRevaluationExamMarkNonFinalByStudent($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $revaluationExamMark = ""; | |
| $sql = "SELECT mark from exam_revaluation_marks | |
| WHERE exam_revaluation_id ='$request->revaluationId' | |
| AND studentID = '$request->studentId' | |
| AND examID = '$request->examId'"; | |
| try { | |
| $revaluationExamMark = $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $revaluationExamMark; | |
| } | |
| /** | |
| * @param $studentId,revaluation Id,ExamId | |
| */ | |
| public function updateRevaluationExamMarkByStudent($request,$exam) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $exam = $this->realEscapeObject($exam); | |
| $result = ""; | |
| $sql = "UPDATE revaluation_marks_finalized set mark = '$exam->revaluationMark' | |
| WHERE exam_revaluation_id ='$request->revaluationId' | |
| AND studentID = '$request->studentId' | |
| AND examID = '$request->examId'"; | |
| try { | |
| $this->executeQueryForObject($sql); | |
| $result = true; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $result; | |
| } | |
| /** | |
| * @param $studentId,revaluation Id,ExamId | |
| */ | |
| public function updateRevaluationExamMarkNonFinalByStudent($request, $exam) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $exam = $this->realEscapeObject($exam); | |
| $result = ""; | |
| $sql = "UPDATE exam_revaluation_marks set mark = '$exam->revaluationMark' | |
| WHERE exam_revaluation_id ='$request->revaluationId' | |
| AND studentID = '$request->studentId' | |
| AND examID = '$request->examId'"; | |
| try { | |
| $this->executeQueryForObject($sql); | |
| $result = true; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $result; | |
| } | |
| /** | |
| * save revaluation valuation 2 marks by request | |
| * @param request | |
| * @return List | |
| */ | |
| public function saveExamRevaluationValMarks($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $studentList = $request->studentList; | |
| $valueString = ""; | |
| $staffType = $request->staffType ? $request->staffType : "EXAM CONTROLLER"; | |
| $staffSql = "SELECT staffID from external_examiners limit 1 "; | |
| try { | |
| $staffDetails = $this->executeQueryForList($staffSql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| $staffId = $staffDetails[0]->staffID; | |
| $valueString=[]; | |
| $sql=""; | |
| foreach ($studentList as $student) { | |
| $student = (object) $student; | |
| if($request->valCount == 3 || $request->isThirdVal){ | |
| if (is_numeric($student->mark3)){ | |
| $valueString[] = "('$student->studentID' , '$request->revaluationId' , '$student->examID', '$staffId', '$student->mark3')"; | |
| } | |
| }else{ | |
| if(is_numeric($student->mark2)){ | |
| $valueString[] = "('$student->studentID' , '$request->revaluationId' , '$student->examID', '$staffId', '$student->mark2', '$staffType')"; | |
| } | |
| } | |
| } | |
| $sql = ""; | |
| if(!empty($valueString)){ | |
| $valueString = implode(",", $valueString); | |
| if ($request->valCount == 3 || $request->isThirdVal) { | |
| $sql = "INSERT into exam_revaluation_marks_thirdval(studentID,exam_revaluation_id,examID,staffID,mark) | |
| values $valueString ON DUPLICATE KEY UPDATE mark = VALUES(mark)"; | |
| } else { | |
| $sql = "INSERT into exam_revaluation_marks(studentID,exam_revaluation_id,examID,staffID,mark,staffType) | |
| values $valueString ON DUPLICATE KEY UPDATE mark = VALUES(mark)"; | |
| } | |
| } | |
| //insert mark as new | |
| if ($sql) { | |
| try { | |
| $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| return true; | |
| } | |
| /** | |
| * save revaluation valuation 2 marks by request | |
| * @param request | |
| * @return List | |
| */ | |
| public function finalizeExamRevaluationValTwoMarks($request) | |
| { | |
| $sql = ""; | |
| $request = $this->realEscapeObject($request); | |
| $valueString = []; | |
| foreach ($request->examIds as $examId) { | |
| $valueString []= "('$examId' , $request->staffId)"; | |
| } | |
| if (!empty($valueString)) { | |
| try { | |
| $valueString = implode(",", $valueString); | |
| if($request->valCount == 3){ | |
| $sql = "INSERT into exam_revaluation_marks_submitted_thirdval(examID,staffID) | |
| values $valueString"; | |
| }else{ | |
| $sql = "INSERT into exam_revaluation_marks_submitted(examID,staffID) | |
| values $valueString"; | |
| } | |
| $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| return true; | |
| } | |
| /** | |
| * checkrevaluation valuation 2 marks by request | |
| * @param request | |
| * @return List | |
| */ | |
| public function checkFinalizeExamRevaluationValTwoMarks($request) | |
| { | |
| $sql = ""; | |
| $request = $this->realEscapeObject($request); | |
| $result = new StdClass; | |
| $result->finalizedVal2 = 0; | |
| $result->finalizedVal3 = 0; | |
| if (!empty($request->examIds)) { | |
| try { | |
| $examIds = implode(",", $request->examIds); | |
| $sqlThirdVal = "SELECT distinct examID,staffID FROM exam_revaluation_marks_submitted_thirdval WHERE examID IN($examIds)"; | |
| $sql = "SELECT distinct examID,staffID FROM exam_revaluation_marks_submitted WHERE examID IN($examIds)"; | |
| $examsSubmittedSecondVal = $this->executeQueryForList($sql); | |
| $examsSubmittedThirdVal = $this->executeQueryForList($sqlThirdVal); | |
| if(!empty($examsSubmittedSecondVal)){ | |
| $result->finalizedVal2 = 1; | |
| } | |
| if (!empty($examsSubmittedThirdVal)) { | |
| $result->finalizedVal3 = 1; | |
| } | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| return $result; | |
| } | |
| /** | |
| * @param $request | |
| * save to third valuation students for revaluation | |
| */ | |
| public function saveThirdValRevalStudents($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $result = null; | |
| $studentList = $request->studentList; | |
| $valueString = []; | |
| foreach ($studentList as $student) { | |
| $student = (object) $student; | |
| if($student->eligibleFor3rdVal == 1 && $request->valCount==2 || $request->isThirdVal){ | |
| $valueString []= "('$student->examID','$student->studentID' , '1')"; | |
| }else{ | |
| $finalizeMarksValueString[] = "('$student->examID','$student->studentID' , '$student->finalizedMark',$request->staffId,$request->revaluationId,'1')"; | |
| } | |
| } | |
| try { | |
| if(!empty($valueString)){ | |
| $valueString = implode(",", $valueString); | |
| $sql = "INSERT INTO externalexam_thirdvalstudents (examID, studentID,revaluationFlag) | |
| VALUES $valueString | |
| ON DUPLICATE KEY UPDATE | |
| studentID = VALUES(studentID),examID = VALUES(examID)"; | |
| $this->executeQueryForObject($sql); | |
| } | |
| if(!empty($finalizeMarksValueString)){ | |
| $finalizeMarksValueString = implode(",", $finalizeMarksValueString); | |
| $insertMarksSql = "INSERT INTO revaluation_marks_finalized (examID, studentID,mark,staffID,exam_revaluation_id,approveMark) | |
| VALUES $finalizeMarksValueString | |
| ON DUPLICATE KEY UPDATE | |
| mark = VALUES(mark)"; | |
| $this->executeQueryForObject($insertMarksSql); | |
| } | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return true; | |
| } | |
| /** | |
| * Get Exam Revaluation Report | |
| * @param $revaluationId,subjectIds | |
| * @return $report | |
| * @throws ProfessionalException | |
| */ | |
| public function getExamRevaluationReportBySubject($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $revaluationTypeCondition = ""; | |
| $revaluationId = ""; | |
| $subjectBatchCondition = ""; | |
| $studentCondition =""; | |
| $revaluationId = $request->revaluationId; | |
| $courseType = $request->courseType; | |
| //$revaluationTypeId= (int)$request->revaluationTypeId; | |
| $revaluationTypeIds = implode(', ', $request->revaluationTypeId); | |
| if ($request->revaluationTypeId) { | |
| $revaluationTypeCondition = "and erss.exam_revaluation_fees_id IN ($revaluationTypeIds)"; | |
| } | |
| $condition = ""; | |
| if ($courseType == CourseTypeConstants::UG || $courseType == CourseTypeConstants::UG_PRO) { | |
| $condition = "exammarks_external em ON (em.examID = erss.examID | |
| AND em.studentID = ersd.studentID)"; | |
| } else { | |
| $condition = "externalexammarks_finalized em ON (em.examID = erss.examID | |
| AND em.studentID = ersd.studentID)"; | |
| } | |
| if ($request->subjectId) { | |
| $subjectIds = implode(",", $request->subjectId); | |
| $subjectBatchCondition .= "AND s.subjectID IN ($subjectIds)"; | |
| } | |
| if ($request->batchId) { | |
| $batchIds = implode(",", $request->batchId); | |
| $subjectBatchCondition .= " AND sa.batchID IN ($batchIds)"; | |
| } | |
| if($request->studentId){ | |
| $studentCondition = "AND sa.studentID IN ($request->studentId)"; | |
| } | |
| $sql = null; | |
| $revaluationStudentDetails = null; | |
| $sql = "SELECT | |
| distinct (ersd.studentID), | |
| sa.studentName AS 'StudentName', | |
| sa.regNo AS 'RegisterNumber', | |
| s.subjectID AS 'subjectId', | |
| s.subjectName AS 'SubjectCode', | |
| s.subjectDesc AS 'SubjectName', | |
| b.batchID as 'batchId', | |
| b.batchName AS 'batchName', | |
| efn.false_number AS 'falseNumber', | |
| em.mark AS 'mark1', | |
| erm.mark as 'mark2', | |
| ermt.mark as 'mark3', | |
| rmf.mark as 'finalizedMark', | |
| e.examTotalMarks, | |
| im.internalMarks, | |
| ims.maxInternalMarks, | |
| e.examID, | |
| erss.exam_revaluation_fees_id as revaluationTypeId | |
| FROM | |
| exam_revaluation_student_details ersd | |
| INNER JOIN | |
| studentaccount sa ON (sa.studentID = ersd.studentID) | |
| INNER JOIN | |
| batches b ON (b.batchID = sa.batchID) | |
| INNER JOIN | |
| exam_revaluation_student_subjects erss ON (erss.studentID = ersd.studentID | |
| AND ersd.exam_revaluation_id = erss.exam_revaluation_id) | |
| INNER JOIN | |
| exam e ON (e.examID = erss.examID) | |
| INNER JOIN | |
| internal_marks im ON( im.studentID = sa.studentID | |
| AND im.subjectID = e.subjectID | |
| AND im.batchID = e.batchId | |
| AND im.semID = e.semID) | |
| INNER JOIN | |
| internal_marks_settings ims ON(ims.subjectID = e.subjectID | |
| AND ims.batchID = e.batchId | |
| AND ims.semID = e.semID) | |
| INNER JOIN | |
| subjects s ON (e.subjectID = s.subjectID) | |
| INNER JOIN | |
| exam_revaluation_fees erf ON (erss.exam_revaluation_fees_id = erf.id | |
| AND ersd.exam_revaluation_id = erf.exam_revaluation_id) | |
| LEFT JOIN | |
| examcontroller_false_number efn ON (efn.studentID = ersd.studentID | |
| AND efn.examID = erss.examID) | |
| LEFT JOIN | |
| $condition | |
| LEFT JOIN | |
| exam_revaluation_marks erm ON erm.examID = erss.examID AND erm.studentID = ersd.studentID AND erm.exam_revaluation_id = ersd.exam_revaluation_id | |
| LEFT JOIN | |
| exam_revaluation_marks_thirdval ermt ON ermt.examID = erss.examID AND ermt.studentID = ersd.studentID AND ermt.exam_revaluation_id = ersd.exam_revaluation_id | |
| LEFT JOIN | |
| revaluation_marks_finalized rmf ON rmf.examID = erss.examID AND rmf.studentID = ersd.studentID AND rmf.exam_revaluation_id = ersd.exam_revaluation_id AND rmf.approveMark =1 | |
| WHERE | |
| ersd.paid = 1 | |
| AND ersd.exam_revaluation_id = '$revaluationId' | |
| $revaluationTypeCondition | |
| $subjectBatchCondition | |
| $studentCondition | |
| ORDER BY efn.false_number,sa.studentID,s.subjectDesc,erf.exam_fees_name"; | |
| try { | |
| $revaluationStudentDetails = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $revaluationStudentDetails; | |
| } | |
| /** | |
| * delete finalized revaluation marks | |
| * @param request | |
| */ | |
| public function deleteExamRevaluationMarkFinalizedState($request, $revaluationId) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| try { | |
| $sqlUpdate = "DELETE FROM revaluation_marks_finalized WHERE examID= $request->examID AND exam_revaluation_id = '$revaluationId' AND studentID = $request->studentID"; | |
| $this->executeQuery($sqlUpdate); | |
| $updateSql2 = "UPDATE exam_revaluation_marks set mark='$request->finalizedMark' where studentID='$request->studentID' and exam_revaluation_id='$revaluationId' and examID='$request->examID'"; | |
| $this->executeQuery($updateSql2); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return true; | |
| } | |
| /** | |
| * Get student exam revaluation details | |
| * @param $revaluationId,studentId | |
| * @throws ProfessionalException | |
| */ | |
| public function getStudentExamRevaluationDetails($studentId, $revaluationId) | |
| { | |
| $studentId = $this->realEscapeString($studentId); | |
| $revaluationId = $this->realEscapeString($revaluationId); | |
| $sql = "SELECT | |
| sa.studentName ,sa.regNo ,er.exam_registration_id ,er.exam_supplementary_id ,er.revalDesc ,s.subjectName ,s.subjectDesc ,rmf.mark, erss.examID,erbg.resultFromDate as fromDate,s.subjectID as subjectId | |
| FROM | |
| exam_revaluation_student_subjects erss | |
| INNER JOIN exam_revaluation_student_details ersd ON | |
| (erss.exam_revaluation_id = ersd.exam_revaluation_id and erss .studentID = ersd.studentID) | |
| INNER JOIN exam_revaluation er ON | |
| (er.id = erss.exam_revaluation_id) | |
| INNER JOIN studentaccount sa ON | |
| (sa.studentID = erss.studentID) | |
| INNER JOIN exam ex ON | |
| (ex.examID = erss.examID) | |
| INNER JOIN subjects s ON | |
| (ex.subjectID = s.subjectID) | |
| INNER JOIN exam_revaluation_batch_groups erbg ON erbg.exam_revaluation_id = ersd.exam_revaluation_id AND erbg.batchID = ex.batchID | |
| LEFT JOIN revaluation_marks_finalized rmf ON | |
| (rmf.examID = erss.examID and rmf.studentID = erss.studentID and rmf.exam_revaluation_id = erss.exam_revaluation_id and rmf.approveMark =1) | |
| WHERE | |
| ersd.paid =1 and erss.studentID = $studentId and er.id = $revaluationId | |
| ORDER BY s.subjectDesc;"; | |
| try { | |
| $revaluationStudentDetails = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $revaluationStudentDetails; | |
| } | |
| /** | |
| * Get revaluation registered student details | |
| * @param $revaluationId | |
| * @throws ProfessionalException | |
| */ | |
| public function getRevaluationRegisteredStudentsById($revaluationId) | |
| { | |
| $revaluationId = $this->realEscapeString($revaluationId); | |
| $sql = "SELECT | |
| id, studentID | |
| FROM exam_revaluation_student_details | |
| WHERE | |
| exam_revaluation_id =$revaluationId AND paid =1 | |
| ORDER BY id ASC;"; | |
| try { | |
| $revaluationStudentDetails = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $revaluationStudentDetails; | |
| } | |
| /** | |
| * @param $request | |
| * @return Object|null | |
| * @author Sibin | |
| */ | |
| public function getExamRevaluationsByRequest($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $conditions = null; | |
| $examRegDesc = null; | |
| $examregDetailsCondition = $staffAssignedSubjectsTable = ""; | |
| if ($request->examType === ExamType::REGULAR) { | |
| $conditions .= " AND ex.exam_registration_id IS NOT NULL AND ex.exam_supplementary_id IS NULL"; | |
| $examRegDesc = ", er.examregName AS examRegDesc "; | |
| $examregDetailsCondition = "INNER JOIN exam_registration er ON er.examregID = ex.exam_registration_id"; | |
| } else if ($request->examType === ExamType::SUPPLY) { | |
| $conditions .= " AND ex.exam_supplementary_id IS NOT NULL AND ex.exam_registration_id IS NULL"; | |
| $examRegDesc = ", es.supplyDesc AS examRegDesc "; | |
| $examregDetailsCondition = " INNER JOIN exam_supplementary es ON es.id = ex.exam_supplementary_id "; | |
| } | |
| if ($request->revaluationId) { | |
| $conditions .= " AND id = $request->revaluationId "; | |
| } | |
| if ($request->markEntryType) { | |
| $conditions .= " AND erf.markEntryType = '$request->markEntryType' "; | |
| } | |
| if ($request->needMarkEntry) { | |
| $conditions .= " AND erf.need_markentry = '1' "; | |
| } | |
| $conditions .= " GROUP BY ex.id "; | |
| if ($request->staffId) { | |
| $staffAssignedSubjectsTable = " INNER JOIN examRevaluationFacultyPackets evf ON evf.revaluationId = erss.exam_revaluation_id AND evf.revaluationTypeId = erss.exam_revaluation_fees_id | |
| AND evf.evaluator = '$request->staffId' "; | |
| } | |
| $sql = null; | |
| $revaluations = null; | |
| try { | |
| $sql = "SELECT | |
| ex.id, | |
| ex.exam_registration_id AS examRegId, | |
| ex.revalDesc AS name, | |
| ex.startDate, | |
| ex.endDate, | |
| ex.percentage, | |
| ex.margin, | |
| ex.memoNum, | |
| ex.memoDate, | |
| ex.finalizedFlag AS isFinalized, | |
| ex.subjectLimit, | |
| ex.exam_supplementary_id AS supplyRegId, | |
| ex.published AS isPublished, | |
| ex.fromDate, | |
| ex.toDate | |
| $examRegDesc | |
| FROM | |
| exam_revaluation ex | |
| INNER JOIN exam_revaluation_student_subjects erss | |
| ON erss.exam_revaluation_id = ex.id | |
| INNER JOIN exam_revaluation_fees erf | |
| ON erf.id = erss.exam_revaluation_fees_id | |
| $examregDetailsCondition | |
| $staffAssignedSubjectsTable | |
| WHERE ex.id IS NOT NULL $conditions ORDER BY ex.id DESC"; | |
| $revaluations = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $revaluations; | |
| } | |
| /** | |
| * get revaluation subjects with valuation dates | |
| * @param request | |
| * @return List | |
| */ | |
| public function getRevaluationExamSubjectsWithValuationDateByRequest($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $sql = $staffAssignedSubjectsTable = ""; | |
| if($request->staffId){ | |
| $staffAssignedSubjectsTable = " INNER JOIN examRevaluationFacultyPackets evf ON evf.revaluationId = erss.exam_revaluation_id AND evf.revaluationTypeId = erss.exam_revaluation_fees_id | |
| AND evf.subjectId = e.subjectID AND evf.evaluator = '$request->staffId' "; | |
| } | |
| if ($request->revaluationId) { | |
| $sql = "SELECT distinct (erss.examID), | |
| s.subjectID as subjectId, | |
| s.subjectName,s.subjectDesc,e.examregID,e.supply_examreg_id, | |
| rds.firstval_Datestart as firstStartDate, | |
| rds.firstval_Dateend as firstEndDate, | |
| rds.secondval_Datestart as secondStartDate, | |
| rds.secondval_Dateend as secondEndDate | |
| from exam_revaluation_student_subjects erss | |
| inner join exam e | |
| on e.examID = erss.examID | |
| inner join subjects s | |
| on s.subjectID = e.subjectID | |
| LEFT JOIN revaluationDatesSubjectWise rds | |
| ON rds.revaluationId = erss.exam_revaluation_id AND rds.revaluationTypeId = erss.exam_revaluation_fees_id AND rds.subjectId = s.subjectID | |
| $staffAssignedSubjectsTable | |
| where erss.exam_revaluation_id='$request->revaluationId' | |
| and erss.exam_revaluation_fees_id='$request->revaluationTypeId' | |
| group by(s.subjectID)"; | |
| try { | |
| $revaluationExamSubjects = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $revaluationExamSubjects; | |
| } | |
| } | |
| public function assignAllRevaluationDatesSubjectWise($request) | |
| { | |
| $subjectIds = $this->realEscapeArray($request->subjectArray); | |
| $request->examRegId = $this->realEscapeString($request->examRegId); | |
| $dates = (object) $this->realEscapeObject($request->dates); | |
| $insertSql = []; | |
| $sql = null; | |
| $result = null; | |
| try { | |
| if (!empty($subjectIds) && $request->revaluationId && $request->revaluationTypeId) { | |
| foreach ($subjectIds as $subjectId) { | |
| if($subjectId){ | |
| $insertSql[] = "(\"$request->revaluationId\",\"$request->revaluationTypeId\", \"$subjectId\", \"$dates->firstStartDate\", \"$dates->firstEndDate\", \"$dates->secondStartDate\", \"$dates->secondEndDate\",\"$request->adminId\")"; | |
| } | |
| } | |
| if($insertSql){ | |
| $insertSql = implode(",", $insertSql); | |
| $sql = "INSERT into revaluationDatesSubjectWise(revaluationId,revaluationTypeId,subjectId, firstval_Datestart, firstval_Dateend, secondval_Datestart, secondval_Dateend,created_by) values " . $insertSql . " | |
| ON DUPLICATE KEY UPDATE | |
| firstval_Datestart = VALUES(firstval_Datestart), | |
| firstval_Dateend = VALUES(firstval_Dateend), | |
| secondval_Datestart = VALUES(secondval_Datestart), | |
| secondval_Dateend = VALUES(secondval_Dateend), | |
| updated_by = VALUES(created_by), | |
| updated_date = VALUES(updated_date)"; | |
| $this->executeQueryForObject($sql); | |
| $result = true; | |
| } | |
| } | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $result; | |
| } | |
| /** | |
| * get Students In ExamRevaluation By Subject | |
| * @param $request | |
| * @author sibin | |
| */ | |
| public function getStudentsForExamRevaluationBySubject($request) | |
| { | |
| $studentList = []; | |
| $packetCondition = ""; | |
| $request = $this->realEscapeObject($request); | |
| $filterPacket = $request->viewOnly ? "" : " AND evpr.packetNo ='$request->packetNo' "; | |
| $filterPacketJoin = $request->viewOnly ? " INNER JOIN " : " LEFT JOIN "; | |
| $packetOrder = "evpr.packetNo ASC,"; | |
| if ($request->packetNo) { | |
| $packetOrder = ""; | |
| $packetCondition = " AND erss.studentID NOT IN(SELECT pr.studentId from examRevaluationStudentPacketsRelation pr | |
| INNER JOIN exam ex ON ex.examID = pr.examId | |
| WHERE pr.revaluationId='$request->revaluationId' AND pr.revaluationTypeId='$request->revaluationTypeId' and ex.subjectID='$request->subjectId' and pr.packetNo NOT IN('$request->packetNo') and pr.packetNo IS NOT NULL)"; | |
| } | |
| $sql = "SELECT erss.studentID,efn.false_number as falseNumber,efn.alpha_numeric_code as alphaNumericCode | |
| ,e.examID,efna.false_number as falseNumberInput,efna.false_number as hasFalseNumberAssigned,evpr.packetNo, null as falseNumberMismatch | |
| FROM exam_revaluation_student_details ersd | |
| INNER JOIN exam_revaluation_student_subjects erss ON ersd.studentID = erss.studentID AND ersd.exam_revaluation_id = erss.exam_revaluation_id | |
| INNER JOIN exam e ON erss.examID = e.examID | |
| INNER JOIN examcontroller_false_number efn ON efn.studentID = erss.studentID AND efn.examID = e.examID | |
| $filterPacketJoin examRevaluationStudentPacketsRelation evpr ON evpr.studentId = erss.studentID AND evpr.examId = e.examID AND evpr.revaluationId = erss.exam_revaluation_id AND evpr.revaluationTypeId = erss.exam_revaluation_fees_id $filterPacket | |
| LEFT JOIN examcontroller_false_number efna ON efna.examID = e.examID AND efna.studentID = evpr.studentId $filterPacket | |
| WHERE erss.exam_revaluation_id = '$request->revaluationId' AND erss.exam_revaluation_fees_id = '$request->revaluationTypeId' AND e.subjectID = '$request->subjectId' | |
| $packetCondition | |
| AND ersd.paid = 1 AND ersd.paid = 1 group by erss.studentID | |
| ORDER BY $packetOrder efna.false_number DESC,efn.false_number DESC"; | |
| try { | |
| $studentList = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $studentList; | |
| } | |
| /** | |
| * Get revaluation finalize marks for student exam | |
| * @param $request | |
| * @throws ProfessionalException | |
| */ | |
| public function getRevaluationFinalizedMarksByExam($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $condition=""; | |
| if($request->excludeRevaluationType && $request->revaluationTypeId){ | |
| $condition .= " AND erss.exam_revaluation_fees_id NOT IN($request->revaluationTypeId)"; | |
| } | |
| if ($request->considerRevaluationId && $request->revaluationId) { | |
| $condition .= " AND rmf.exam_revaluation_id IN($request->revaluationId) "; | |
| } | |
| $revaluationFinalized=null; | |
| $sql = "SELECT rmf.examID,rmf.studentID as studentId,rmf.mark,staffID as staffId,rmf.exam_revaluation_id as revaluationId ,erss.exam_revaluation_fees_id as revaluationTypeId,er.revaluationType | |
| FROM revaluation_marks_finalized rmf | |
| INNER JOIN exam_revaluation_student_subjects erss ON erss.exam_revaluation_id = rmf.exam_revaluation_id AND erss.examID = rmf.examID AND erss.studentID = rmf.studentID | |
| INNER JOIN exam_revaluation er ON er.id = rmf.exam_revaluation_id | |
| WHERE rmf.studentID IN($request->studentID) AND rmf.examID IN($request->examID) | |
| $condition"; | |
| try { | |
| $revaluationFinalized = $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $revaluationFinalized; | |
| } | |
| /** | |
| * save finalized revaluation marks | |
| * @param request | |
| * @return List | |
| */ | |
| public function saveFinalizedExamRevaluationMarks($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $studentList = $request->studentList; | |
| $valueString = ""; | |
| $logValuesString = ""; | |
| $isExistSql = ""; | |
| $result = new stdClass; | |
| $result->isFinalized = 0; | |
| $valueStringArray = []; | |
| $logValuesArray = []; | |
| foreach ($studentList as $student) { | |
| $student = (object) $student; | |
| //check if already exist | |
| if ($student->savedMark && $student->savedMark != "null") { | |
| $isExistSql = "SELECT mark from revaluation_marks_finalized | |
| where studentID='$student->studentID' | |
| and exam_revaluation_id='$request->revaluationId' | |
| and examID='$student->examID'"; | |
| try { | |
| $isExist = $this->executeQueryForList($isExistSql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| if (!$isExist) { | |
| $currentFinalizedMarks = ExamRevaluationService::getInstance()->getRevaluationFinalizedMarksByExam($student); | |
| $student->savedMark = round($student->savedMark,2); | |
| $student->oldMark = round($student->oldMark,2); | |
| $currentFinalizedMarks->mark = round($currentFinalizedMarks->mark,2); | |
| if(($student->savedMark > $currentFinalizedMarks->mark || !$currentFinalizedMarks->mark) && $student->savedMark > $student->oldMark){ | |
| //insert query | |
| $valueStringArray[] = "('$student->examID','$student->studentID' , '$student->savedMark' , '$request->staffId' ,'$request->revaluationId',1)"; | |
| if ($currentFinalizedMarks->mark && $student->savedMark > $currentFinalizedMarks->mark) { | |
| //delete current finalized entry and create log | |
| $logValuesArray[] = "('$currentFinalizedMarks->revaluationId','$currentFinalizedMarks->revaluationTypeId','$currentFinalizedMarks->examID','$currentFinalizedMarks->studentId' , '$currentFinalizedMarks->mark' ,'$currentFinalizedMarks->staffId' ,'$request->staffId')"; | |
| $deleteSql = "DELETE FROM revaluation_marks_finalized WHERE exam_revaluation_id = '$currentFinalizedMarks->revaluationId' AND examID = '$currentFinalizedMarks->examID' AND studentID = '$currentFinalizedMarks->studentId'"; | |
| $this->executeQueryForObject($deleteSql); | |
| } | |
| } | |
| } | |
| } | |
| } | |
| if(!empty($valueStringArray)){ | |
| try { | |
| //insert new finalized marks | |
| $valueString = implode(",", $valueStringArray); | |
| $sql = "INSERT into revaluation_marks_finalized(examID,studentID,mark,staffID,exam_revaluation_id,approveMark) | |
| values $valueString"; | |
| $this->executeQueryForList($sql); | |
| if (!empty($logValuesArray)){ | |
| $logValuesString = implode(",", $logValuesArray); | |
| $logSql = "INSERT into examRevaluationMarkEntryLog(revaluationId,revaluationTypeId,examId,studentId,mark,staffId,created_by) | |
| values $logValuesString"; | |
| $this->executeQueryForList($logSql); | |
| } | |
| $result->isFinalized = 1; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| return $result; | |
| } | |
| /** | |
| * Get revaluation finalize marks from log for student exam | |
| * @param $request | |
| * @throws ProfessionalException | |
| */ | |
| public function getRevaluationFinalizedMarksByExamFromLog($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $revaluationFinalized = null; | |
| $condition=""; | |
| if ($request->considerRevaluationPublishedOnly) { | |
| $condition .= " AND er.published = 1 AND TIMESTAMP(er.fromDate,'00:00:00') < NOW() "; | |
| } | |
| if($request->revaluationId && $request->considerSpecifiedRevaluation){ | |
| $condition .= " AND rmf.revaluationId IN ($request->revaluationId) "; | |
| } | |
| $sql = "SELECT rmf.examId,rmf.studentId,rmf.mark,rmf.staffId,rmf.revaluationId ,erss.exam_revaluation_fees_id as revaluationTypeId,er.revaluationType | |
| FROM examRevaluationMarkEntryLog rmf | |
| INNER JOIN exam_revaluation_student_subjects erss ON erss.exam_revaluation_id = rmf.revaluationId AND erss.examID = rmf.examId AND erss.studentID = rmf.studentId | |
| INNER JOIN exam_revaluation er ON er.id = rmf.revaluationId | |
| WHERE rmf.studentId IN($request->studentID) AND rmf.examId IN($request->examID) | |
| $condition | |
| ORDER BY created_date DESC LIMIT 1"; | |
| try { | |
| $revaluationFinalized = $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $revaluationFinalized; | |
| } | |
| /** | |
| * Get revaluation student applied status by revaluation type | |
| * @param $request | |
| * @throws ProfessionalException | |
| */ | |
| public function getRevaluationAppliedStatusByRequest($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $examRegField = $request->isSupply ? "exam_supplementary_id" : "exam_registration_id"; | |
| $revaluation = null; | |
| $sql = "SELECT erss.exam_revaluation_id as revaluationId FROM exam_revaluation_student_details erss | |
| INNER JOIN exam_revaluation er ON er.id = erss.exam_revaluation_id | |
| WHERE erss.studentID IN ($request->studentId) AND erss.paid=1 AND erss.approved =1 AND er.$examRegField IN ($request->examRegId) | |
| AND JSON_CONTAINS(er.revaluationType, '{\"revaluationType\":\"$request->revalTypeToCheck\"}')"; | |
| try { | |
| $revaluation = $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $revaluation; | |
| } | |
| /** | |
| * @param $request | |
| * @throws ProfessionalException | |
| *update exam external marks by student | |
| */ | |
| public function saveExamRevaluationMark($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $studentList = $request->studentList; | |
| $sql = $sqlFinalize = ""; | |
| try { | |
| $staffSql = "SELECT staffID from external_examiners limit 1"; | |
| $staffDetails = $this->executeQueryForList($staffSql); | |
| $staffId = current($staffDetails)->staffID; | |
| foreach($studentList as $student){ | |
| $valueStringArray[] = "('$student->studentId','$request->revaluationId','$student->examId','$staffId','$student->mark','$request->staffType')"; | |
| if($student->finalize){ | |
| $valueStringFinalizedArray[] = "('$student->examId','$student->studentId','$student->mark','$request->staffId','$request->revaluationId',1)"; | |
| } | |
| } | |
| if(!empty($valueStringArray)){ | |
| $valueStringArray = implode(",", $valueStringArray); | |
| $sql = "INSERT INTO exam_revaluation_marks (studentID,exam_revaluation_id,examID,staffID,mark,staffType) | |
| VALUES $valueStringArray | |
| ON DUPLICATE KEY UPDATE | |
| mark = VALUES(mark), | |
| staffID = VALUES(staffID), | |
| staffType = VALUES(staffType)"; | |
| $this->executeQuery($sql); | |
| } | |
| if(!empty($valueStringFinalizedArray)){ | |
| $valueStringFinalizedArray = implode(",", $valueStringFinalizedArray); | |
| $sqlFinalize = "INSERT INTO revaluation_marks_finalized(examID,studentID,mark,adminID,exam_revaluation_id,approveMark) | |
| VALUES $valueStringFinalizedArray | |
| ON DUPLICATE KEY UPDATE | |
| mark = VALUES(mark), | |
| adminID = VALUES(adminID)"; | |
| $this->executeQuery($sqlFinalize); | |
| } | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return true; | |
| } | |
| /** | |
| * get revaluation students by request | |
| * @param request | |
| * @return List | |
| */ | |
| public function getRevaluationExamStudentBySubjects($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $sql = $condition = ""; | |
| if ($request->revaluationTypeId) { | |
| $condition .= " AND erss.exam_revaluation_fees_id ='$request->revaluationTypeId' "; | |
| } | |
| if ($request->subjectId) { | |
| $condition .= " AND e.subjectID IN ($request->subjectId) "; | |
| } | |
| if ($request->revaluationId) { | |
| $sql = "SELECT erss.examID, | |
| s.subjectID, | |
| s.subjectName,s.subjectDesc,e.examregID,e.supply_examreg_id,erss.studentID | |
| from exam_revaluation_student_subjects erss | |
| INNER JOIN exam e | |
| on e.examID = erss.examID | |
| INNER JOIN subjects s | |
| on s.subjectID = e.subjectID | |
| INNER JOIN exam_revaluation_student_details ersd ON ersd.exam_revaluation_id = erss.exam_revaluation_id AND ersd.studentID = erss.studentID | |
| where ersd.paid =1 AND erss.exam_revaluation_id='$request->revaluationId' | |
| $condition | |
| group by(erss.studentID)"; | |
| try { | |
| $revaluationExamStudents = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $revaluationExamStudents; | |
| } | |
| } | |
| /** | |
| * get revaluation types | |
| * @return List | |
| */ | |
| public function getAllRevaluationTypes() { | |
| $revaluationTypes = []; | |
| $sql = "SELECT revaluationType FROM exam_revaluation "; | |
| try { | |
| $revaluations = $this->executeQueryForList($sql); | |
| foreach ($revaluations as $revaluation){ | |
| $revType = json_decode($revaluation->revaluationType); | |
| $revaluationTypes[$revType->revaluationType] = $revType->revaluationType; | |
| } | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $revaluationTypes; | |
| } | |
| /** | |
| * get revaluation batches with valuation dates | |
| * @param request | |
| * @return List | |
| */ | |
| public function getRevaluationExamBatchesWithValuationDateByRequest($request) | |
| { | |
| $digitalValuationProperties = CommonService::getInstance()->getSettings(SettingsConstents::EXAM_CONTROLLER, SettingsConstents::DIGITAL_VALUATION_PROPERTIES); | |
| $digitalValuationProperties = $digitalValuationProperties ? current(json_decode($digitalValuationProperties)->revaluation) : ""; | |
| $request = $this->realEscapeObject($request); | |
| $sql = $condition = ""; | |
| $thirdValStudentsCondition = ""; | |
| $revaluationExamBatches = []; | |
| $groupByCondition = " group by(e.batchID)"; | |
| if($request->examWise){ | |
| $groupByCondition = " group by(e.examID) " ; | |
| } | |
| else if ($request->subjectWise) { | |
| $groupByCondition = " group by(e.subjectID) "; | |
| } | |
| if ($request->subjectId) { | |
| $condition .= " AND e.subjectID = '$request->subjectId' "; | |
| } | |
| if ($request->revaluationTypeId) { | |
| $condition .= " AND erss.exam_revaluation_fees_id IN($request->revaluationTypeId)"; | |
| } | |
| if($digitalValuationProperties->showOnlyThirdValStudentSubjects && $request->valuationCount == 2){ | |
| $thirdValStudentsCondition = " INNER JOIN externalexam_thirdvalstudents eth ON eth.examID = erss.examID AND eth.studentID = erss.studentID AND eth.revaluationFlag=1"; | |
| } | |
| if ($request->revaluationId) { | |
| $sql = "SELECT | |
| b.batchID as batchId, | |
| b.batchName, | |
| s.subjectID as subjectId,s.subjectName,s.subjectDesc, | |
| e.examregID,e.supply_examreg_id, | |
| rds.firstval_Datestart as firstStartDate, | |
| rds.firstval_Dateend as firstEndDate, | |
| rds.secondval_Datestart as secondStartDate, | |
| rds.secondval_Dateend as secondEndDate, | |
| count(DISTINCT(erss.studentId)) as studentCount, | |
| e.examID as examId | |
| from exam_revaluation_student_subjects erss | |
| inner join exam_revaluation_student_details ersd | |
| ON ersd.exam_revaluation_id = erss.exam_revaluation_id AND ersd.studentID = erss.studentID | |
| inner join exam e | |
| on e.examID = erss.examID | |
| inner join batches b | |
| on b.batchID = e.batchID | |
| inner join subjects s | |
| on s.subjectID = e.subjectID | |
| $thirdValStudentsCondition | |
| LEFT JOIN valuationDatesRevaluation rds | |
| ON rds.revaluationId = erss.exam_revaluation_id AND rds.batchId = e.batchID | |
| where erss.exam_revaluation_id='$request->revaluationId' AND ersd.paid = 1 | |
| $condition | |
| $groupByCondition"; | |
| try { | |
| $revaluationExamBatches = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $revaluationExamBatches; | |
| } | |
| } | |
| public function assignRevaluationDatesBatchWise($request) | |
| { | |
| $batchIds = $this->realEscapeArray($request->batchArray); | |
| $request->examRegId = $this->realEscapeString($request->examRegId); | |
| $dates = (object) $this->realEscapeObject($request->dates); | |
| $insertSql = []; | |
| $sql = null; | |
| $result = null; | |
| try { | |
| if (!empty($batchIds) && $request->revaluationId) { | |
| foreach ($batchIds as $batchId) { | |
| if ($batchId) { | |
| $insertSql[] = "(\"$request->revaluationId\", \"$batchId\", \"$dates->firstStartDate\", \"$dates->firstEndDate\", \"$dates->secondStartDate\", \"$dates->secondEndDate\",\"$request->adminId\")"; | |
| } | |
| } | |
| if ($insertSql) { | |
| $insertSql = implode(",", $insertSql); | |
| $sql = "INSERT into valuationDatesRevaluation(revaluationId,batchId,firstval_Datestart, firstval_Dateend, secondval_Datestart, secondval_Dateend,created_by) values " . $insertSql . " | |
| ON DUPLICATE KEY UPDATE | |
| firstval_Datestart = VALUES(firstval_Datestart), | |
| firstval_Dateend = VALUES(firstval_Dateend), | |
| secondval_Datestart = VALUES(secondval_Datestart), | |
| secondval_Dateend = VALUES(secondval_Dateend), | |
| updated_by = VALUES(created_by), | |
| updated_date = VALUES(updated_date)"; | |
| $this->executeQueryForObject($sql); | |
| $result = true; | |
| } | |
| } | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $result; | |
| } | |
| /** | |
| * @param $examId | |
| * @throws ProfessionalException | |
| * @author Sibin | |
| */ | |
| public function getExamRevaluationStaffs($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $staffIds = null; | |
| $sql = null; | |
| $condition = ""; | |
| if ($request->revaluationTypeId) { | |
| $condition .= " AND revaluationTypeId IN ($request->revaluationTypeId) "; | |
| } | |
| try { | |
| $sql = "SELECT examId,staffIds from revaluationExamValuationStaffs where revaluationId = '$request->revaluationId' AND examId='$request->examId' and valuationCount='$request->valuationCount' $condition"; | |
| $staffIds = $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $staffIds; | |
| } | |
| /** | |
| * assign revaluation staff by examId | |
| * | |
| * @param int $examId | |
| * @return object|NULL|$objectList[] | |
| * @throws ProfessionalException | |
| */ | |
| public function assignExamRevaluationFaculty($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $staffIds = ""; | |
| $result = null; | |
| try { | |
| $staffIds = implode(",", $request->staffIds); | |
| if ($request->valuationCountSelected) { | |
| $deleteSql = "DELETE from revaluationExamValuationStaffs where revaluationId = '$request->revaluationId' AND examId = '$request->examId' AND valuationCount='$request->valuationCountSelected'"; | |
| $this->executeQueryForObject($deleteSql); | |
| $sql = "INSERT into revaluationExamValuationStaffs(revaluationId,examId,staffIds,valuationCount) values('$request->revaluationId','$request->examId', '$staffIds','$request->valuationCountSelected')"; | |
| } | |
| if ($request->staffIds) { | |
| $this->executeQueryForObject($sql); | |
| } | |
| $result = true; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $result; | |
| } | |
| /** | |
| * get revaluation students | |
| * @param request | |
| * @return List | |
| */ | |
| public function getRevaluationExamValuationStudentsByRequest($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $sql = $staffAssignedSubjectsTable = $condition = $thirdValStudentsCondition = $thirdValStudentStaffCondition = ""; | |
| $revaluationExamStudents = []; | |
| $groupByCondition = $request->studentWise ? " group by(erss.studentID) " : " group by(e.batchID)"; | |
| $staffAssignedCol = ""; | |
| $revalTypeCondition = $request->revaluationTypeId ? " AND revaluationTypeId IN ($request->revaluationTypeId) " : ""; | |
| if ($request->staffId && $request->examId && $request->valuationCount) { | |
| $joinCondition = "LEFT JOIN examRevaluationStaffAssignedStudents eras ON eras.studentId = erss.studentID AND eras.examId = e.examID AND eras.revaluationId = erss.exam_revaluation_id AND eras.staffId = '$request->staffId' and valuationCount='$request->valuationCount'"; | |
| $staffAssignedCol = " , eras.staffId as staffAssigned"; | |
| if ($request->revaluationTypeId) { | |
| $joinCondition .= " AND eras.revaluationTypeId IN ($request->revaluationTypeId) "; | |
| } | |
| } | |
| if($request->examId){ | |
| $condition .=" AND e.examID IN($request->examId)"; | |
| $condition .=" AND erss.studentID NOT IN (select studentId from examRevaluationStaffAssignedStudents where revaluationId ='$request->revaluationId' and examId IN ($request->examId) and valuationCount='$request->valuationCount' $revalTypeCondition and staffId NOT IN('$request->staffId'))"; | |
| } | |
| if ($request->valuationCount == 2) { | |
| $thirdValStudentsCondition = " INNER JOIN externalexam_thirdvalstudents eth ON eth.examID = erss.examID AND eth.studentID = erss.studentID AND eth.revaluationFlag=1"; | |
| $thirdValStudentStaffCondition = " AND erss.studentID NOT IN (select studentId from examRevaluationStaffAssignedStudents where revaluationId='$request->revaluationId' and examId='$request->examId' | |
| and valuationCount NOT IN ($request->valuationCount) $revalTypeCondition and staffId IN('$request->staffId')) "; | |
| } | |
| if ($request->subjectId) { | |
| $condition .= " AND e.subjectID = '$request->subjectId'"; | |
| } | |
| if ($request->revaluationTypeId) { | |
| $condition .= " AND erss.exam_revaluation_fees_id IN ($request->revaluationTypeId) "; | |
| } | |
| $falseNoCondition = ""; | |
| $falseNoField = ""; | |
| if ($request->getFalseNoStudentsOnly) { | |
| $falseNoCondition = " INNER JOIN examcontroller_false_number efn ON efn.examID = e.examID AND efn.studentID = sa.studentID "; | |
| $falseNoField = " , efn.false_number as falseNumber "; | |
| } | |
| if ($request->revaluationId) { | |
| $sql = "SELECT | |
| erss.studentID as studentId, | |
| sa.regNo,sa.studentName, | |
| b.batchID as batchId, | |
| b.batchName, | |
| s.subjectID as subjectId,s.subjectName,s.subjectDesc, | |
| e.examregID,e.supply_examreg_id, | |
| rds.firstval_Datestart as firstStartDate, | |
| rds.firstval_Dateend as firstEndDate, | |
| rds.secondval_Datestart as secondStartDate, | |
| rds.secondval_Dateend as secondEndDate, | |
| count(erss.studentId) as studentCount, | |
| e.examID as examId | |
| $falseNoField | |
| $staffAssignedCol | |
| from exam_revaluation_student_subjects erss | |
| inner join exam_revaluation_student_details ersd | |
| ON ersd.exam_revaluation_id = erss.exam_revaluation_id AND ersd.studentID = erss.studentID | |
| inner join exam e | |
| on e.examID = erss.examID | |
| inner join batches b | |
| on b.batchID = e.batchID | |
| inner join subjects s | |
| on s.subjectID = e.subjectID | |
| inner join studentaccount sa | |
| on sa.studentId = erss.studentID | |
| $joinCondition | |
| $thirdValStudentsCondition | |
| $falseNoCondition | |
| LEFT JOIN valuationDatesRevaluation rds | |
| ON rds.revaluationId = erss.exam_revaluation_id AND rds.batchId = e.batchID | |
| where erss.exam_revaluation_id='$request->revaluationId' AND ersd.paid = 1 | |
| $condition | |
| $thirdValStudentStaffCondition | |
| $groupByCondition"; | |
| try { | |
| $revaluationExamStudents = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $revaluationExamStudents; | |
| } | |
| } | |
| /** | |
| * assign staff to revaluation staff | |
| * | |
| * @param int $examId | |
| * @return object|NULL|$objectList[] | |
| * @throws ProfessionalException | |
| */ | |
| public function assignStudentsToExamRevaluationStaffs($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $insertValues = []; | |
| $insertValuesString = ""; | |
| $uncheckedStudentsString = ""; | |
| $insertSql = ""; | |
| $deleteSql = ""; | |
| $adminId = $_SESSION['adminID']; | |
| $revaluationTypeId = $request->revaluationTypeId ? $request->revaluationTypeId : 'NULL'; | |
| $revalTypeCondition = $request->revaluationTypeId ? " AND revaluationTypeId IN ($request->revaluationTypeId) " : ""; | |
| try { | |
| if (!empty($request->selectedStudents)) { | |
| foreach ($request->selectedStudents as $selectedStudent) { | |
| $selectedStudent = (object)$selectedStudent; | |
| if ($request->assignSubjectwise) { | |
| $insertValues[] = "('$selectedStudent->studentId','$request->revaluationId',$revaluationTypeId,'$selectedStudent->examId','$request->staffId','$request->valuationCount','$adminId')"; | |
| }else{ | |
| $insertValues[] = "('$selectedStudent->studentId','$request->revaluationId',$revaluationTypeId,'$request->examId','$request->staffId','$request->valuationCount','$adminId')"; | |
| } | |
| } | |
| $insertValuesString = implode(",", $insertValues); | |
| $insertSql = "INSERT INTO examRevaluationStaffAssignedStudents(studentId,revaluationId,revaluationTypeId,examId,staffId,valuationCount,created_by) VALUES $insertValuesString | |
| ON DUPLICATE KEY UPDATE staffId = VALUES(staffId)"; | |
| $this->executeQueryForObject($insertSql); | |
| } | |
| if (!empty($request->uncheckedStudents)) { | |
| $uncheckedStudentsString = implode(",", array_column($request->uncheckedStudents, 'studentId')); | |
| $deleteSql = "DELETE from examRevaluationStaffAssignedStudents where revaluationId='$request->revaluationId' AND examId IN($request->examId) AND valuationCount = '$request->valuationCount' $revalTypeCondition AND studentId IN($uncheckedStudentsString)"; | |
| $this->executeQueryForObject($deleteSql); | |
| } | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return true; | |
| } | |
| /** | |
| * get getStudentAssignedStaffByExam | |
| * | |
| * @param int $request | |
| * @return object|NULL|$objectList[] | |
| * @throws ProfessionalException | |
| */ | |
| public function getExamRevaluationStudentAssignedStaffByExam($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $joinTable = $condition = $staffIds = ""; | |
| if ($request->batchId) { | |
| $joinTable .= " INNER JOIN studentaccount sa ON sa.studentID = evs.studentId "; | |
| $condition .= " AND sa.batchID IN ($request->batchId) "; | |
| } | |
| if($request->revaluationTypeId){ | |
| $condition .= " AND evs.revaluationTypeId IN ($request->revaluationTypeId) "; | |
| } | |
| try { | |
| $sql = "SELECT distinct evs.staffId FROM examRevaluationStaffAssignedStudents evs | |
| $joinTable | |
| WHERE evs.revaluationId IN ($request->revaluationId) AND evs.examId IN ($request->examId) | |
| AND evs.valuationCount IN ($request->valuationCountSelected) | |
| $condition"; | |
| $staffIds = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $staffIds; | |
| } | |
| /** | |
| * get Exams Assigned For Re-Valuation By staff | |
| * | |
| * @param int $request | |
| * @return object|NULL|$objectList[] | |
| * @throws ProfessionalException | |
| */ | |
| public function getExamRevaluationsAssignedForValuationByStaff($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $valuationCountCondition = ""; | |
| $condition = $groupBy = ""; | |
| if ($request->valuationCount) { | |
| $tableName = "revaluationExamValuationStaffs evs"; | |
| $valuationCountCondition = " AND evs.valuationCount='$request->valuationCount'"; | |
| } | |
| if ($request->revaluationId) { | |
| $condition .= " AND evs.revaluationId IN ('$request->revaluationId')"; | |
| } | |
| if ($request->revaluationTypeId) { | |
| $condition .= " AND evs.revaluationTypeId IN ($request->revaluationTypeId) "; | |
| } | |
| if ($request->groupBySubject) { | |
| $groupBy = "group by e.subjectID"; | |
| } | |
| $exams = null; | |
| try { | |
| $sql = "SELECT | |
| e.batchID as 'batchId', | |
| b.batchName, | |
| b.batchDesc, | |
| e.semID as 'semId', | |
| e.examID AS examId, | |
| e.examName AS examName, | |
| e.subjectID as 'subjectId', | |
| s.subjectName, | |
| s.subjectDesc, | |
| evs.staffIDs, | |
| e.examregID as examRegId | |
| FROM | |
| exam e | |
| INNER JOIN batches b ON b.batchID = e.batchID | |
| INNER JOIN subjects s ON s.subjectID = e.subjectID | |
| INNER JOIN $tableName ON evs.examId = e.examID AND FIND_IN_SET('$request->staffId',evs.staffIDs) | |
| WHERE | |
| 1 = 1 | |
| $condition $valuationCountCondition $groupBy"; | |
| $exams = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $exams; | |
| } | |
| /** | |
| * get Exams Assigned For Re-Valuation By staff =>reviewer enabled | |
| * | |
| * @param int $request | |
| * @return object|NULL|$objectList[] | |
| * @throws ProfessionalException | |
| */ | |
| public function getExamRevaluationsAssignedForValuationByStaffForReviewerEnabled($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $exams = null; | |
| try { | |
| $examRevalStudentList = $this->getStaffAssignedRevaluationStudentDetails($request); | |
| $assignedExamIds = array_unique(array_column($examRevalStudentList, 'examId')); | |
| if(!empty($assignedExamIds)){ | |
| $request->examId = implode(",", $assignedExamIds); | |
| $condition = $groupBy = ""; | |
| if ($request->groupBySubject) { | |
| $groupBy = "group by e.subjectID"; | |
| } | |
| if ($request->examId) { | |
| $condition .= " AND e.examID IN ($request->examId)"; | |
| } | |
| $sql = "SELECT | |
| e.batchID as 'batchId', | |
| b.batchName, | |
| b.batchDesc, | |
| e.semID as 'semId', | |
| e.examID AS examId, | |
| e.examName AS examName, | |
| e.subjectID as 'subjectId', | |
| s.subjectName, | |
| s.subjectDesc, | |
| e.examregID as examRegId | |
| FROM | |
| exam e | |
| INNER JOIN batches b ON b.batchID = e.batchID | |
| INNER JOIN subjects s ON s.subjectID = e.subjectID | |
| WHERE | |
| 1 = 1 | |
| $condition $groupBy"; | |
| $exams = $this->executeQueryForList($sql); | |
| } | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $exams; | |
| } | |
| /** | |
| * get Batch Exam Re-Valuation Dates | |
| * | |
| * @param int $batchId,revaluationId,valuationCount | |
| * @return object|NULL|$objectList[] | |
| * @throws ProfessionalException | |
| */ | |
| public function getBatchExamRevaluationDates($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $valuationDates = null; | |
| if ($request->valuationCount == 1) { | |
| $dateColumns = "firstval_Datestart as valStartDate,firstval_Dateend as valEndDate"; | |
| } else if ($request->valuationCount == 2) { | |
| $dateColumns = "secondval_Datestart as valStartDate,secondval_Dateend as valEndDate"; | |
| } | |
| try { | |
| $sql = "SELECT $dateColumns | |
| from valuationDatesRevaluation | |
| WHERE revaluationId='$request->revaluationId' AND batchId='$request->batchId'"; | |
| $valuationDates = $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $valuationDates; | |
| } | |
| /** | |
| * get Students For Exam Re-Valuation By Staff | |
| * | |
| * @param int $examId | |
| * @return object|NULL|$objectList[] | |
| * @throws ProfessionalException | |
| */ | |
| public function getStudentsForExamRevaluationByStaff($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $getFalseNumber = ""; | |
| $falseNoField = ""; | |
| $condition=""; | |
| if ($request->showStudentsByFalseNumber) { | |
| if($request->getRegularExamFalseNumber){ | |
| $getFalseNumber = "INNER JOIN examcontroller_false_number efn ON efn.studentID = esas.studentID AND efn.examID = '$request->regularExamId'"; | |
| }else{ | |
| $getFalseNumber = "INNER JOIN examcontroller_false_number efn ON efn.studentID = esas.studentID AND efn.examID = '$request->examId'"; | |
| } | |
| $falseNoField = " , efn.false_number AS falseNumber"; | |
| } | |
| if ($request->examId) { | |
| $examIdValue = " , $request->examId as examId "; | |
| } | |
| if($request->revaluationTypeId) { | |
| $condition .= " AND esas.revaluationTypeId IN ($request->revaluationTypeId) "; | |
| } | |
| $studentList = []; | |
| try { | |
| $sql = "SELECT esas.studentId, | |
| sa.regNo,sa.studentName, | |
| esas.revaluationId, | |
| esas.staffId, | |
| esas.valuationCount | |
| $falseNoField | |
| $examIdValue | |
| FROM examRevaluationStaffAssignedStudents esas | |
| INNER JOIN studentaccount sa ON sa.studentID = esas.studentID | |
| $getFalseNumber | |
| WHERE esas.revaluationId='$request->revaluationId' | |
| and esas.examId='$request->examId' | |
| and esas.staffId='$request->staffId' | |
| and esas.valuationCount='$request->valuationCount' | |
| $condition"; | |
| $studentList = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $studentList; | |
| } | |
| /** | |
| * get Exam Revaluations Assigned For staff | |
| * | |
| * @param int $request | |
| * @return object|NULL|$objectList[] | |
| * @throws ProfessionalException | |
| */ | |
| public function getRevaluationsAssignedForStaff($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $exams = null; | |
| try { | |
| $sql = "SELECT evs.revaluationId as revaluationId | |
| FROM revaluationExamValuationStaffs evs WHERE FIND_IN_SET('$request->staffId',evs.staffIds)"; | |
| $exams = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $exams; | |
| } | |
| /** | |
| * @author Sibin | |
| * get oe exam reval reg students by examregid,revaluation id and subjectid | |
| */ | |
| public function getExamRevaluationStudentsMarkDetailsBySubject($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| if($request->courseType == CourseTypeConstants::UG) { | |
| $externalMarkCondition = " exammarks_external em ON (em.examID = e.examID AND em.studentID = sa.studentID) "; | |
| } else { | |
| $externalMarkCondition = " externalexammarks_finalized em ON (em.examID = e.examID AND em.studentID = sa.studentID) "; | |
| } | |
| $condition = ""; | |
| if ($request->studentId && $request->getSingleStudent) { | |
| $condition = " AND sa.studentID IN ($request->studentId) "; | |
| } | |
| if ($request->examType == ExamType::SUPPLY) { | |
| $sql = "SELECT erss.studentID,sa.regNo,sa.studentName,e.examID,e.batchID,e.semID,e.subjectID,e.examTotalMarks, | |
| oe.id as oeExamId, | |
| em.mark as mark1,1 as mark1Confirm,oec1.exam_mark as mark2,oec1.is_confirmed as mark2Confirm,oec2.exam_mark as mark3,oec2.is_confirmed as mark3Confirm,oec3.exam_mark as mark4,oec3.is_confirmed as mark4Confirm | |
| ,rmf.mark as revaluationFinalizedMark | |
| ,IF(oec1.valuation_count,1,0) as valuation1,IF(oec2.valuation_count,1,0) as valuation2,IF(oec3.valuation_count,1,0) as valuation3 | |
| FROM exam ex | |
| INNER JOIN exam_supplementary_student_subjects erss ON erss.examID = ex.examID | |
| INNER JOIN exam_supplementary_student_details ers ON ers.exam_supplementary_id = erss.exam_supplementary_id AND ers.studentID = erss.studentID | |
| INNER JOIN studentaccount sa ON sa.studentID = erss.studentID | |
| INNER JOIN exam e ON e.supply_examreg_id = erss.exam_supplementary_id AND e.batchID = ex.batchID AND e.subjectID = ex.subjectID | |
| INNER JOIN oe_exams oe ON JSON_UNQUOTE(JSON_EXTRACT(oe.identifying_context, '$.examId')) = e.examID | |
| INNER JOIN $externalMarkCondition | |
| INNER JOIN exam_revaluation_student_details ersd ON ersd.studentID = sa.studentID | |
| INNER JOIN exam_revaluation_student_subjects ervss ON ervss.studentID = sa.studentID AND ervss.examID = e.examID AND ervss.exam_revaluation_id = ersd.exam_revaluation_id | |
| 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 AND oec1.revaluation_id = '$request->revaluationId' AND oec1.scrutiny_id IS NULL | |
| 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 AND oec2.revaluation_id = '$request->revaluationId' AND oec2.scrutiny_id IS NULL | |
| 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 = 1 AND oec3.revaluation_id = '$request->revaluationId' AND oec3.scrutiny_id = 1 | |
| LEFT JOIN revaluation_marks_finalized rmf ON rmf.examID = e.examID AND rmf.studentID =sa.studentID AND rmf.exam_revaluation_id = ersd.exam_revaluation_id | |
| WHERE erss.exam_supplementary_id IN ($request->examRegId) AND ex.subjectID IN ($request->subjectId) AND ex.examregID IS NOT NULL AND ers.paid=1 AND ersd.exam_revaluation_id = '$request->revaluationId' AND ersd.paid = 1 | |
| $condition | |
| group by sa.studentID order by sa.regNo"; | |
| } else { | |
| $sql = "SELECT distinct erss.studentID,sa.regNo,sa.studentName,e.examID,e.batchID,e.semID,e.subjectID,e.examTotalMarks, | |
| oe.id as oeExamId, | |
| em.mark as mark1,1 as mark1Confirm,oec1.exam_mark as mark2,oec1.is_confirmed as mark2Confirm,oec2.exam_mark as mark3,oec2.is_confirmed as mark3Confirm,oec3.exam_mark as mark4,oec3.is_confirmed as mark4Confirm | |
| ,rmf.mark as revaluationFinalizedMark | |
| ,IF(oec1.valuation_count,1,0) as valuation1,IF(oec2.valuation_count,1,0) as valuation2,IF(oec3.valuation_count,1,0) as valuation3 | |
| 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 | |
| INNER JOIN $externalMarkCondition | |
| INNER JOIN exam_revaluation_student_details ersd ON ersd.studentID = sa.studentID | |
| INNER JOIN exam_revaluation_student_subjects ers ON ers.studentID = sa.studentID AND ers.examID = e.examID AND ers.exam_revaluation_id = ersd.exam_revaluation_id | |
| 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 AND oec1.revaluation_id = '$request->revaluationId' AND oec1.scrutiny_id IS NULL | |
| 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 AND oec2.revaluation_id = '$request->revaluationId' AND oec2.scrutiny_id IS NULL | |
| 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 = 1 AND oec3.revaluation_id = '$request->revaluationId' AND oec3.scrutiny_id = 1 | |
| LEFT JOIN revaluation_marks_finalized rmf ON rmf.examID = e.examID AND rmf.studentID =sa.studentID AND rmf.exam_revaluation_id = ersd.exam_revaluation_id | |
| WHERE erss.examregID = '$request->examRegId' AND erss.subjectID = '$request->subjectId' AND ersc.paid=1 AND ersd.exam_revaluation_id = '$request->revaluationId' AND ersd.paid = 1 | |
| $condition | |
| 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; | |
| } | |
| public function finalizeStudentRevaluationExamMark($students) | |
| { | |
| $students = $this->realEscapeArray($students); | |
| $eefValues = $eeValues = []; | |
| if (empty($students)) { | |
| throw new ProfessionalException(ProfessionalException::INVALID_PARAMETER, "Students can not be null"); | |
| } | |
| $staffSql = "SELECT staffID from external_examiners limit 1"; | |
| $staffDetails = $this->executeQueryForList($staffSql); | |
| $staffId = current($staffDetails)->staffID; | |
| $staffType = 'EXAM_CONTROLLER'; | |
| 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->adminId','$student->revaluationId',1)"; | |
| $eeValues[] = "('$student->studentId','$student->revaluationId','$student->examId','$staffId','$student->externalMark','$staffType')"; | |
| } | |
| try { | |
| if(!empty($eefValues)){ | |
| $eefValuesString = implode(", ", $eefValues); | |
| $eeValuesString = implode(", ", $eeValues); | |
| $sqlF = "INSERT INTO revaluation_marks_finalized(examID,studentID,mark,adminID,exam_revaluation_id,approveMark) VALUES | |
| $eefValuesString ON DUPLICATE KEY UPDATE mark = VALUES(mark),adminID = VALUES(adminID)"; | |
| $this->executeQuery($sqlF); | |
| $sql = "INSERT INTO exam_revaluation_marks(studentID,exam_revaluation_id,examID,staffID,mark,staffType) VALUES | |
| $eeValuesString ON DUPLICATE KEY UPDATE mark = VALUES(mark)"; | |
| if ($staffId) { | |
| $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',1)"; | |
| } | |
| try { | |
| $valuesString = implode(", ", $values); | |
| $sql = "INSERT INTO externalexam_thirdvalstudents (examID, studentID,revaluationFlag) VALUES $valuesString ON DUPLICATE KEY UPDATE revaluationFlag = VALUES(revaluationFlag)"; | |
| $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return; | |
| } | |
| /** | |
| * delete from revaluation finalized marks | |
| */ | |
| public function deleteStudentRevaluationFinalizedMark($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $result = null; | |
| $sql = "DELETE FROM revaluation_marks_finalized WHERE examID = '$request->examID' AND studentID='$request->studentID' AND exam_revaluation_id = '$request->revaluationId'"; | |
| try { | |
| $this->executeQueryForObject($sql); | |
| $result = true; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $result; | |
| } | |
| /** get student revaluations | |
| * @return List | |
| */ | |
| public function getStudentAppliedRevaluations($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $sql = $condition = $groupBy = ""; | |
| $revaluations=[]; | |
| $regularExamJoin = ""; | |
| $groupBy = "GROUP BY er.id"; | |
| if($request->revaluationId){ | |
| $condition .=" AND er.id ='$request->revaluationId'"; | |
| $groupBy ="GROUP BY e.subjectID"; | |
| } | |
| $revaluationTypeIds = is_array($request->revaluationTypeId) ? implode(', ', $request->revaluationTypeId) : $request->revaluationTypeId; | |
| if ($request->revaluationTypeId) { | |
| $condition .= " AND erss.exam_revaluation_fees_id IN ($revaluationTypeIds)"; | |
| } | |
| if ($request->revalType) { | |
| $condition .= " AND JSON_CONTAINS(revaluationType, '{\"revaluationType\":\"$request->revalType\"}')"; | |
| } | |
| if(!$request->includeUnPublished){ | |
| $condition .= " AND er.published = 1"; | |
| } | |
| if($request->getRegularExamFalseNumber){ | |
| $regularExamJoin = " INNER JOIN exam erg ON erg.semID = e.semID AND erg.subjectID = e.subjectID AND erg.batchID = e.batchID AND erg.examregID IS NOT NULL"; | |
| $falseNoJoin = "LEFT JOIN examcontroller_false_number efn ON efn.studentID = ersd.studentID AND efn.examID = erg.examID"; | |
| }else{ | |
| $falseNoJoin = "LEFT JOIN examcontroller_false_number efn ON efn.studentID = ersd.studentID AND efn.examID = erss.examID"; | |
| } | |
| if($request->subjectId){ | |
| $condition .= " AND s.subjectID IN ($request->subjectId)"; | |
| } | |
| try { | |
| $sql = "SELECT er.id,er.revalDesc as name,er.revaluationType,er.exam_registration_id as examRegId,er.exam_supplementary_id as supplyRegId,e.subjectID as subjectId,e.semID as semId,s.subjectName,s.subjectDesc,erss.examID as examId,efn.false_number as falseNumber,erss.properties FROM exam_revaluation_student_details ersd | |
| INNER JOIN exam_revaluation_student_subjects erss ON erss.exam_revaluation_id = ersd.exam_revaluation_id AND erss.studentID = ersd.studentID | |
| INNER JOIN exam_revaluation er ON er.id = ersd.exam_revaluation_id | |
| INNER JOIN exam_revaluation_fees erf ON (erss.exam_revaluation_fees_id = erf.id AND ersd.exam_revaluation_id = erf.exam_revaluation_id) | |
| INNER JOIN exam e ON e.examID = erss.examID | |
| $regularExamJoin | |
| INNER JOIN subjects s ON s.subjectID = e.subjectID | |
| $falseNoJoin | |
| WHERE ersd.paid =1 AND ersd.studentID = '$request->studentId' | |
| $condition | |
| $groupBy ORDER BY er.id,e.subjectID"; | |
| $revaluations = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $revaluations; | |
| } | |
| /** | |
| * @param $request | |
| * @return Object|null | |
| * @author Sibin | |
| */ | |
| public function getExamRevaluationsPublishStatusByRequest($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $conditions = null; | |
| if ($request->examType === ExamType::REGULAR) { | |
| $conditions .= " AND ex.exam_registration_id IS NOT NULL AND ex.exam_supplementary_id IS NULL"; | |
| } else if ($request->examType === ExamType::SUPPLY) { | |
| $conditions .= " AND ex.exam_supplementary_id IS NOT NULL AND ex.exam_registration_id IS NULL"; | |
| } | |
| if ($request->revaluationId) { | |
| $conditions .= " AND ex.id = $request->revaluationId "; | |
| } | |
| if ($request->markEntryType) { | |
| $conditions .= " AND erf.markEntryType = '$request->markEntryType' "; | |
| } | |
| if ($request->courseTypeId) { | |
| $conditions .= " AND b.courseTypeID = $request->courseTypeId "; | |
| } | |
| if($request->revalType){ | |
| $conditions .=" AND JSON_CONTAINS(revaluationType, '{\"revaluationType\":\"$request->revalType\"}')"; | |
| } | |
| $conditions .= " GROUP BY ex.id "; | |
| $sql = null; | |
| $revaluations = null; | |
| try { | |
| $sql = "SELECT | |
| ex.id, | |
| ex.exam_registration_id AS examRegId, | |
| ex.revalDesc AS name, | |
| ex.startDate, | |
| ex.endDate, | |
| ex.percentage, | |
| ex.margin, | |
| ex.memoNum, | |
| ex.memoDate, | |
| ex.subjectLimit, | |
| ex.exam_supplementary_id AS supplyRegId, | |
| ex.fromDate, | |
| ex.toDate, | |
| ex.revalDesc,ex.finalizedFlag,ex.published,ex.exam_registration_id,ex.exam_supplementary_id, | |
| IF(ex.exam_registration_id,er.examregName,es.supplyDesc) AS examRegDesc | |
| FROM | |
| exam_revaluation ex | |
| LEFT JOIN exam_registration er ON er.examregID = ex.exam_registration_id | |
| LEFT JOIN exam_supplementary es ON es.id = ex.exam_supplementary_id | |
| INNER JOIN exam_revaluation_batch_groups erb ON erb.exam_revaluation_id = ex.id | |
| INNER JOIN batches b ON b.batchID = erb.batchID | |
| WHERE ex.id IS NOT NULL $conditions ORDER BY ex.id DESC"; | |
| $revaluations = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $revaluations; | |
| } | |
| /** | |
| * get revaluation type details | |
| * @param revaluationTypeId | |
| * @return List | |
| */ | |
| public function getRevaluationTypeDetailsById($revaluationTypeId){ | |
| $revaluationTypeId = $this->realEscapeString($revaluationTypeId); | |
| $sql=""; | |
| if($revaluationTypeId){ | |
| $sql = "SELECT exam_revaluation_id, markEntryType,need_markentry from exam_revaluation_fees where id = $revaluationTypeId"; | |
| try{ | |
| $revaluationDetails = $this->executeQueryForObject($sql); | |
| } | |
| catch(\Exception $e){ | |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); | |
| } | |
| return $revaluationDetails; | |
| } | |
| } | |
| /** | |
| * set student revaluation subject remarks | |
| * @param revaluationTypeId | |
| * @return Boolean | |
| */ | |
| public function setStudentRevaluationSubjectRemarks($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $sql = ""; | |
| if ($request->studentId && $request->examId && $request->revaluationId && $request->revaluationTypeId) { | |
| $sql = "UPDATE exam_revaluation_student_subjects set properties = JSON_SET(IFNULL(properties, '{}'), '$.valuationRemarks','$request->remarks') | |
| WHERE studentID IN ($request->studentId) AND examID IN ($request->examId) AND exam_revaluation_id IN ($request->revaluationId) AND exam_revaluation_fees_id IN ($request->revaluationTypeId)"; | |
| try { | |
| $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return true; | |
| } | |
| } | |
| /** | |
| * get revaluation students with subject details | |
| * @return List | |
| */ | |
| public function getRevaluationStudentsWithSubjectDetailsByRequest($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $studentList = []; | |
| $condition = $sql = ""; | |
| if ($request->name) { | |
| $condition .= " AND t2.studentName like '" . $request->name . "%' "; | |
| } | |
| if ($request->deptId) { | |
| $condition .= " AND t2.deptID = " . $request->deptId . " "; | |
| if ($request->batchId) { | |
| $condition .= " AND t2.batchID = " . $request->batchId . " "; | |
| if ($request->semId) { | |
| $condition .= " AND t3.semID = " . $request->semId . " "; | |
| } | |
| } | |
| } | |
| if ($request->sel == 1 | |
| ) { | |
| $condition .= " AND t1.paid = 0 "; | |
| } else if ($request->sel == 2) { | |
| $condition .= " AND t1.paid = 1 "; | |
| } elseif ($request->sel == 3) { | |
| $condition .= " AND t1.approved = 1 "; | |
| } elseif ($request->sel == 4) { | |
| $condition .= " AND t1.paid = 1 AND t1.approved = 0 "; | |
| } | |
| $sql = "SELECT DISTINCT t1.studentID, t2.regNo, t2.studentName, t1.appliedDate, t1.challanNo, t1.paid, t1.fee_paid_date,t1.revaluation_total_fees,t5.examName, | |
| t6.false_number,t1.approved,t4.exam_revaluation_fees_id AS revalFeesId, s.subjectName, t5.examID, t5.subjectID, t5.semID, t3.batchID,s.subjectDesc,t3.batchName | |
| FROM exam_revaluation_student_details t1 | |
| INNER JOIN studentaccount t2 ON t1.studentID = t2.studentID | |
| INNER JOIN batches t3 ON t2.batchID = t3.batchID | |
| INNER JOIN exam_revaluation_student_subjects t4 ON t4.studentID = t2.studentID AND t1.exam_revaluation_id = t4.exam_revaluation_id | |
| INNER JOIN exam t5 ON t4.examID = t5.examID | |
| INNER JOIN subjects s ON t5.subjectID = s.subjectID | |
| LEFT JOIN examcontroller_false_number t6 ON t2.studentID = t6.studentID AND t5.examID = t6.examID | |
| WHERE t1.exam_revaluation_id = " . $request->revalId . " " . $condition . " | |
| ORDER BY t2.regNo"; | |
| try { | |
| $studentList = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $studentList; | |
| } | |
| /** get student third val revaluation details | |
| * @return Object | |
| */ | |
| public function getStudentThirdRevaluationDetails($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $sql = $thirdValDetails = ""; | |
| try { | |
| $sql = "SELECT eth.thirdvalstudentID as id,emth.mark FROM externalexam_thirdvalstudents eth | |
| LEFT JOIN exam_revaluation_marks_thirdval emth ON emth.examID = eth.examID AND emth.studentID = eth.studentID | |
| WHERE eth.revaluationFlag = 1 AND eth.studentID IN ($request->studentID) AND eth.examID IN ($request->examID)"; | |
| $thirdValDetails = $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $thirdValDetails; | |
| } | |
| /** | |
| * get revaluation applied students with subject details | |
| * @return List | |
| */ | |
| public function getRevaluationAppliedStudentsWithSubjectByRequest($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $studentList = []; | |
| $condition = $sql = ""; | |
| if ($request->batchId) { | |
| $condition .= " AND t2.batchID IN ($request->batchId)"; | |
| } | |
| if ($request->semId) { | |
| $condition .= " AND t3.semID IN ($request->semId)"; | |
| } | |
| if ($request->studentId) { | |
| $condition .= " AND t1.studentID IN ($request->studentId) "; | |
| } | |
| if ($request->paid) { | |
| $condition .= " AND t1.paid = 1 "; | |
| } | |
| $sql = "SELECT DISTINCT t1.studentID, t2.regNo, t2.studentName, t1.appliedDate, t1.challanNo, t1.paid, t1.fee_paid_date,t1.revaluation_total_fees,t5.examName, | |
| t6.false_number,t1.approved,t4.exam_revaluation_fees_id AS revalFeesId, s.subjectName, t5.examID, t5.subjectID, t5.semID, t3.batchID,s.subjectDesc,t3.batchName , | |
| t5.examDate,t2.studentAddress,t2.studentPhone,t1.payment_method,eop.txnID,eop.transactionDate,t5.examCode | |
| FROM exam_revaluation_student_details t1 | |
| INNER JOIN studentaccount t2 ON t1.studentID = t2.studentID | |
| INNER JOIN batches t3 ON t2.batchID = t3.batchID | |
| INNER JOIN exam_revaluation_student_subjects t4 ON t4.studentID = t2.studentID AND t1.exam_revaluation_id = t4.exam_revaluation_id | |
| INNER JOIN exam t5 ON t4.examID = t5.examID | |
| INNER JOIN subjects s ON t5.subjectID = s.subjectID | |
| LEFT JOIN examcontroller_false_number t6 ON t2.studentID = t6.studentID AND t5.examID = t6.examID | |
| LEFT JOIN exam_online_payment eop ON eop.studentID = t1.studentID AND eop.exam_registration_type_id = t1.exam_revaluation_id AND eop.exam_registration_type ='revaluation' AND status='success' | |
| WHERE t1.exam_revaluation_id = " . $request->revaluationId . " " . $condition . " | |
| ORDER BY t2.regNo"; | |
| try { | |
| $studentList = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $studentList; | |
| } | |
| /** | |
| * @param $request | |
| * @return array|Object | |
| * @throws ProfessionalException | |
| */ | |
| public function getRevaluationPaymentDetails($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $filterValues = (object) $request->reportFilterValues; | |
| $condition = ""; | |
| if ($filterValues->paymentMethod) { | |
| if ($filterValues->paymentMethod == 1) { | |
| $condition .= " and ers.payment_method LIKE 'online'"; | |
| } else { | |
| $condition .= " and (ers.payment_method NOT LIKE 'online' or ers.payment_method is null)"; | |
| } | |
| } | |
| if ($request->examRegId) { | |
| $condition .= " AND ers.exam_revaluation_id = '$request->examRegId' "; | |
| } | |
| if ($filterValues->campusType) { | |
| $condition .= " and ba.campus_typeID = $filterValues->campusType"; | |
| } | |
| if ($filterValues->admissionYear) { | |
| $condition .= " and ba.batchStartYear = $filterValues->admissionYear"; | |
| } | |
| if ($filterValues->batch) { | |
| $condition .= " and ba.batchID = $filterValues->batch"; | |
| } | |
| if ($filterValues->department) { | |
| $condition .= " and ba.deptID = $filterValues->department"; | |
| } | |
| if ($filterValues->startDate) { | |
| $condition .= " and IF (ers.fee_paid_date, DATE_FORMAT(ers.fee_paid_date,'%Y-%m-%d'), DATE_FORMAT(ers.appliedDate,'%Y-%m-%d')) >= '$filterValues->startDate'"; | |
| } | |
| if ($filterValues->endDate) { | |
| $condition .= " and IF (ers.fee_paid_date, DATE_FORMAT(ers.fee_paid_date,'%Y-%m-%d'), DATE_FORMAT(ers.appliedDate,'%Y-%m-%d')) <= '$filterValues->endDate'"; | |
| } | |
| if ($filterValues->regNo) { | |
| $condition .= " and sa.regNo = '$filterValues->regNo'"; | |
| } | |
| if ($filterValues->studentName) { | |
| $condition .= " and sa.studentName = '$filterValues->studentName'"; | |
| } | |
| if ($filterValues->admNo) { | |
| $condition .= " and sa.admissionNo = '$filterValues->admNo'"; | |
| } | |
| if ($filterValues->rollNo) { | |
| $condition .= " and sa.rollNo = '$filterValues->rollNo'"; | |
| } | |
| $sql = null; | |
| try { | |
| $sql = "SELECT sa.regNo, sa.studentName, ba.batchName, ers.revaluation_total_fees as examtotalFees, ers.fee_paid_date as dateOfPay,IF ( ers.fee_paid_date, DATE_FORMAT(ers.fee_paid_date,'%Y-%m-%d'),DATE_FORMAT(ers.appliedDate,'%Y-%m-%d')) as dateOfPayFormatted, | |
| ers.payment_method | |
| from | |
| exam_revaluation_student_details ers | |
| inner join | |
| studentaccount sa | |
| on (ers.studentID = sa.studentID ) | |
| inner join batches ba | |
| on(sa.batchID = ba.batchID ) | |
| where | |
| ers.paid=1 $condition ORDER BY IF (ers.fee_paid_date, DATE_FORMAT(ers.fee_paid_date,'%Y-%m-%d'), DATE_FORMAT(ers.appliedDate,'%Y-%m-%d')) ASC,sa.regNo"; | |
| $studentsList = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $studentsList; | |
| } | |
| /** | |
| * @param $studentId | |
| * @param $examRevalId | |
| * @throws ProfessionalException | |
| * @author Sibin | |
| */ | |
| public function getExamRevalReceiptDetails($studentId, $revalId) | |
| { | |
| $studentId = $this->realEscapeString($studentId); | |
| $revalId = $this->realEscapeString($revalId); | |
| $receiptDetails = null; | |
| $sql = null; | |
| try { | |
| $sql = "SELECT sa.regNo,sa.rollNo,sa.studentName,ers.challanNo,ers.revaluation_total_fees as examtotalFees,ers.payment_method as paymentMethod,ers.paid,ers.fee_paid_date as dateofPay,er.revalDesc as examregName,b.batchName, DATE_FORMAT(sa.studentBirthday, '%d-%m-%Y') AS studentBirthday,ers.appliedDate as dateofRegistration | |
| from exam_revaluation_student_details ers | |
| INNER JOIN studentaccount sa ON sa.studentID = ers.studentID | |
| INNER JOIN batches b on b.batchID = sa.batchID | |
| LEFT JOIN exam_revaluation er ON er.id = ers.exam_revaluation_id | |
| where ers.exam_revaluation_id='$revalId' and ers.studentID='$studentId'"; | |
| $receiptDetails = $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $receiptDetails; | |
| } | |
| /* | |
| * get revaluationTypes by id | |
| * @param $revaluationTypeIds | |
| * @return List | |
| */ | |
| public function getRevaluationStudentSubjects($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $sql = ""; | |
| $sql = "SELECT | |
| ersd.studentID, | |
| sa.studentName, | |
| sa.regNo, | |
| s.subjectID AS 'subjectId', | |
| s.subjectName, | |
| s.subjectDesc | |
| FROM | |
| exam_revaluation_student_details ersd | |
| INNER JOIN | |
| exam_revaluation_student_subjects erss ON (erss.studentID = ersd.studentID | |
| AND ersd.exam_revaluation_id = erss.exam_revaluation_id) | |
| INNER JOIN | |
| exam e ON (e.examID = erss.examID) | |
| INNER JOIN | |
| subjects s ON (e.subjectID = s.subjectID) | |
| INNER JOIN | |
| studentaccount sa ON (sa.studentID = erss.studentID) | |
| INNER JOIN | |
| batches b ON (b.batchID = sa.batchID) | |
| WHERE | |
| ersd.paid = 1 | |
| AND ersd.exam_revaluation_id = '$request->revaluationId' | |
| ORDER BY sa.regNo;"; | |
| try { | |
| $revaluations = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $revaluations; | |
| } | |
| /** | |
| * @param $request | |
| * @throws ProfessionalException | |
| * @author Sibin | |
| */ | |
| public function getRevaluationBatches($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $batches = null; | |
| $sql = null; | |
| try { | |
| $sql = "SELECT erbg.batchID as batchId,b.batchName,erbg.isResultPublished as published,erbg.resultFromDate as fromDate,erbg.resultToDate as toDate from exam_revaluation_batch_groups erbg | |
| INNER JOIN batches b ON b.batchID = erbg.batchID | |
| WHERE exam_revaluation_id='$request->revaluationId'"; | |
| $batches = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $batches; | |
| } | |
| /** | |
| * Assign same staff - student - valuation count combo in revaluation digital valuation | |
| * @param $request | |
| * @throws ProfessionalException | |
| * @author Sibin | |
| */ | |
| public function publishRevaluationBatchWise($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $result = null; | |
| $sql = null; | |
| $condition = ""; | |
| $request->published = (int)$request->published; | |
| if ($request->batchId) { | |
| $condition .= " AND batchID IN ($request->batchId)"; | |
| } | |
| try { | |
| if ($request->published) { | |
| $sql = "UPDATE exam_revaluation_batch_groups set isResultPublished = '$request->published',resultFromDate='$request->fromDate',resultToDate='$request->toDate' | |
| WHERE exam_revaluation_id='$request->revaluationId' $condition"; | |
| } else { | |
| $sql = "UPDATE exam_revaluation_batch_groups set isResultPublished = '$request->published' | |
| WHERE exam_revaluation_id='$request->revaluationId' $condition"; | |
| } | |
| $result = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $result; | |
| } | |
| /** | |
| * @param $studentId | |
| * @param $examRevalId | |
| * @throws ProfessionalException | |
| * @author Sibin | |
| */ | |
| public function getStaffAssignedStudentDetails($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $students = []; | |
| $sql = null; | |
| $condition= ""; | |
| if($request->examType){ | |
| $condition .=" AND examType = '$request->examType'"; | |
| } | |
| if($request->examRegId){ | |
| $condition.=" AND examRegId IN ($request->examRegId)"; | |
| } | |
| if($request->staffId){ | |
| $condition .=" AND staffId IN ($request->staffId)"; | |
| } | |
| if($request->subjectId){ | |
| $condition .=" AND subjectId IN($request->subjectId)"; | |
| } | |
| if($request->valuationCount){ | |
| $condition .=" AND valuationCount IN ($request->valuationCount)"; | |
| } | |
| try { | |
| $sql = "SELECT studentId from examValuationStaffAssignedStudents WHERE 1=1 $condition"; | |
| $students = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $students; | |
| } | |
| /** | |
| * @param $studentId | |
| * @param $examRevalId | |
| * @throws ProfessionalException | |
| * @author Sibin | |
| */ | |
| public function getStaffAssignedRevaluationStudentDetails($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $students = []; | |
| $sql = null; | |
| $condition = ""; | |
| if ($request->staffId) { | |
| $condition .= " AND staffId IN ($request->staffId)"; | |
| } | |
| if ($request->examId) { | |
| $condition .= " AND examId IN($request->examId)"; | |
| } | |
| if ($request->valuationCount) { | |
| $condition .= " AND valuationCount IN ($request->valuationCount)"; | |
| } | |
| if ($request->revaluationId) { | |
| $condition .= " AND revaluationId IN ($request->revaluationId)"; | |
| } | |
| try { | |
| $sql = "SELECT studentId,revaluationId,examId from examRevaluationStaffAssignedStudents WHERE 1=1 $condition"; | |
| $students = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $students; | |
| } | |
| /** | |
| * Assign same staff - student - valuation count combo in revaluation digital valuation | |
| * @param $request | |
| * @throws ProfessionalException | |
| * @author Sibin | |
| */ | |
| public function assignSameStudentsToRevaluationStudentsDigitalValuation($request){ | |
| $request = $this->realEscapeObject($request); | |
| $result = null; | |
| $getStudentsSql = "SELECT | |
| erss.studentID as studentId, | |
| erss.exam_revaluation_id as revaluationId, | |
| e.examID as examId, | |
| evsas.staffId, | |
| evsas.valuationCount,$request->adminId as createdBy | |
| from exam_revaluation_student_subjects erss | |
| inner join exam_revaluation_student_details ersd | |
| ON ersd.exam_revaluation_id = erss.exam_revaluation_id AND ersd.studentID = erss.studentID | |
| inner join exam e | |
| on e.examID = erss.examID | |
| inner join batches b | |
| on b.batchID = e.batchID | |
| inner join subjects s | |
| on s.subjectID = e.subjectID | |
| inner join studentaccount sa | |
| on sa.studentId = erss.studentID | |
| LEFT JOIN valuationDatesRevaluation rds | |
| ON rds.revaluationId = erss.exam_revaluation_id AND rds.batchId = e.batchID | |
| INNER JOIN examValuationStaffAssignedStudents evsas ON evsas.studentId = erss.studentID AND evsas.subjectId = e.subjectID | |
| AND evsas.examRegId = IF(e.examregID,e.examregID,e.supply_examreg_id) AND evsas.examType = IF(e.examregID,'REGULAR','SUPPLY') | |
| where erss.exam_revaluation_id='$request->revaluationId' AND ersd.paid = 1 AND e.subjectID = '$request->subjectId'"; | |
| $sql = "INSERT INTO examRevaluationStaffAssignedStudents (studentId,revaluationId,examId, staffId, valuationCount,created_by) ($getStudentsSql) | |
| ON DUPLICATE KEY UPDATE staffId = evsas.staffId"; | |
| try{ | |
| if($request->adminId && $request->revaluationId && $request->subjectId){ | |
| $studentList = $this->executeQueryForList($getStudentsSql); | |
| if(!empty($studentList)){ | |
| $this->executeQueryForList($sql); | |
| $result = $studentList; | |
| } | |
| } | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $result; | |
| } | |
| /** | |
| * get staff details assigned to a digital valuation revaluation subject | |
| * @param $request | |
| * @throws ProfessionalException | |
| * @author Sibin | |
| */ | |
| public function getRevaluationAssignedStaffDetails($request){ | |
| $staffs = []; | |
| $request = $this->realEscapeObject($request); | |
| $sql = "SELECT s.staffID,s.staffName ,s.staffPhone from examRevaluationStaffAssignedStudents ersas | |
| INNER JOIN staffaccounts s ON s.staffID = ersas.staffId | |
| INNER JOIN exam e ON e.examID = ersas.examId | |
| WHERE ersas.revaluationId IN($request->revaluationId) AND e.subjectID IN($request->subjectId) AND ersas.valuationCount IN($request->valuationCount) GROUP BY s.staffID;"; | |
| try{ | |
| if($request->revaluationId && $request->subjectId && $request->valuationCount){ | |
| $staffs = $this->executeQueryForList($sql); | |
| } | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $staffs; | |
| } | |
| /** | |
| * assign revaluation staff by examId | |
| * | |
| * @param int $examId | |
| * @return object|NULL|$objectList[] | |
| * @throws ProfessionalException | |
| */ | |
| public function assignExamRevaluationFacultyByRequest($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $staffIds = ""; | |
| $result = null; | |
| $condition = ""; | |
| if ($request->revaluationTypeId) { | |
| $condition .= " AND revaluationTypeId IN ($request->revaluationTypeId) "; | |
| } | |
| try { | |
| $staffIds = implode(",", $request->staffIds); | |
| if ($request->valuationCountSelected) { | |
| $deleteSql = "DELETE from revaluationExamValuationStaffs where revaluationId = '$request->revaluationId' AND examId = '$request->examId' AND valuationCount='$request->valuationCountSelected' $condition"; | |
| $this->executeQueryForObject($deleteSql); | |
| $sql = "INSERT into revaluationExamValuationStaffs(revaluationId,revaluationTypeId,examId,staffIds,valuationCount) values('$request->revaluationId','$request->revaluationTypeId','$request->examId', '$staffIds','$request->valuationCountSelected')"; | |
| } | |
| if ($request->staffIds) { | |
| $this->executeQueryForObject($sql); | |
| } | |
| $result = true; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $result; | |
| } | |
| /** | |
| * Get Revaluation Report | |
| * @param $revaluationId | |
| * @return $report | |
| * @throws ProfessionalException | |
| */ | |
| public function getRevaluationExamReportDigital($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $revaluationTypeCondition = ""; | |
| $revaluationId = ""; | |
| $subjectBatchCondition = ""; | |
| $revaluationId = $request->revaluationId; | |
| $courseType = $request->courseType; | |
| if(is_array($request->revaluationTypeId)){ | |
| $revaluationTypeIds = implode(', ', $request->revaluationTypeId); | |
| }else{ | |
| $revaluationTypeIds = $request->revaluationTypeId; | |
| } | |
| if ($request->revaluationTypeId) { | |
| $revaluationTypeCondition = "and erss.exam_revaluation_fees_id IN ($revaluationTypeIds)"; | |
| } | |
| $joinMarksTable = ""; | |
| if ($courseType == CourseTypeConstants::UG || $courseType == CourseTypeConstants::BPED) { | |
| $joinMarksTable = " LEFT JOIN exammarks_external em ON (em.examID = erss.examID | |
| AND em.studentID = ersd.studentID)"; | |
| } else { | |
| $joinMarksTable = " LEFT JOIN externalexammarks_finalized em ON (em.examID = erss.examID | |
| AND em.studentID = ersd.studentID)"; | |
| } | |
| if ($request->subjectId) { | |
| $subjectIds = implode(",", $request->subjectId); | |
| $subjectBatchCondition .= "AND s.subjectID IN ($subjectIds)"; | |
| } | |
| if ($request->batchId) { | |
| $batchIds = implode(",", $request->batchId); | |
| $subjectBatchCondition .= " AND sa.batchID IN ($batchIds)"; | |
| } | |
| if ($request->studentId) { | |
| $subjectBatchCondition .= " AND ersd.studentID IN ($request->studentId)"; | |
| } | |
| $regularExamJoin = ""; | |
| if ($request->getRegularExamFalseNumber) { | |
| $regularExamJoin = " INNER JOIN exam erg ON erg.semID = e.semID AND erg.subjectID = e.subjectID AND erg.batchID = e.batchID AND erg.examregID IS NOT NULL"; | |
| $falseNoJoin = "LEFT JOIN examcontroller_false_number efn ON efn.studentID = ersd.studentID AND efn.examID = erg.examID"; | |
| } else { | |
| $falseNoJoin = "LEFT JOIN examcontroller_false_number efn ON efn.studentID = ersd.studentID AND efn.examID = erss.examID"; | |
| } | |
| $sql = null; | |
| $revaluationStudentDetails = null; | |
| $sql = "SELECT distinct (ersd.studentID) as studentId, | |
| sa.studentName AS 'studentName', | |
| sa.regNo, | |
| s.subjectID as subjectId, | |
| s.subjectName AS 'subjectCode', | |
| s.subjectDesc AS 'subjectName', | |
| ersas.valuationCount, | |
| sta.staffName AS 'valuatedStaff', | |
| efn.false_number AS 'falseNumber', | |
| em.mark AS 'externalMark', | |
| erm.mark as revaluationMark | |
| FROM | |
| exam_revaluation_student_details ersd | |
| INNER JOIN | |
| studentaccount sa ON (sa.studentID = ersd.studentID) | |
| INNER JOIN | |
| batches b ON (b.batchID = sa.batchID) | |
| INNER JOIN | |
| exam_revaluation_student_subjects erss ON (erss.studentID = ersd.studentID | |
| AND ersd.exam_revaluation_id = erss.exam_revaluation_id) | |
| INNER JOIN | |
| exam e ON (e.examID = erss.examID) | |
| INNER JOIN | |
| subjects s ON (e.subjectID = s.subjectID) | |
| INNER JOIN | |
| exam_revaluation_fees erf ON (erss.exam_revaluation_fees_id = erf.id | |
| AND ersd.exam_revaluation_id = erf.exam_revaluation_id) | |
| INNER JOIN | |
| examRevaluationStaffAssignedStudents ersas ON (ersas.studentID = ersd.studentID | |
| AND ersas.examID = erss.examID) | |
| INNER JOIN | |
| staffaccounts sta ON (ersas.staffId = sta.staffID) | |
| $regularExamJoin | |
| -- LEFT JOIN | |
| -- examcontroller_false_number efn ON (efn.studentID = ersd.studentID AND efn.examID = erss.examID) | |
| $falseNoJoin | |
| LEFT JOIN revaluation_marks_finalized erm ON erm.exam_revaluation_id = ersd.exam_revaluation_id AND erm.examID = erss.examID AND erm.studentID = ersd.studentID | |
| $joinMarksTable | |
| WHERE | |
| ersd.paid = 1 | |
| AND ersd.exam_revaluation_id = '$revaluationId' | |
| $revaluationTypeCondition | |
| $subjectBatchCondition | |
| ORDER BY sa.regNo,s.subjectDesc,erf.exam_fees_name"; | |
| try { | |
| $revaluationStudentDetails = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $revaluationStudentDetails; | |
| } | |
| /** | |
| * copy student questionwise digital valuation marks from 1 valuation to scritiny | |
| * @param $request | |
| */ | |
| public function copyToScrutinyStudentPreviousDigitalValuationMarks($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $sql = "UPDATE | |
| oe_exam_user_mark | |
| SET | |
| valuation_marks = JSON_SET(valuation_marks,'$.\"scrutiny_$request->valuationTo\"', | |
| CASE | |
| WHEN valuation_marks->'$.\"$request->valuationFrom\"' IS NOT NULL | |
| THEN JSON_OBJECT( | |
| \"mark\",valuation_marks->'$.\"$request->valuationFrom\".mark', | |
| \"staffId\",valuation_marks->'$.\"$request->valuationFrom\".staffId', | |
| \"scrutinyId\",\"$request->valuationTo\") | |
| ELSE JSON_OBJECT( | |
| \"mark\",\"\", | |
| \"staffId\",\"$request->staffId\", | |
| \"scrutinyId\",\"$request->valuationTo\" | |
| ) | |
| END) WHERE oe_exams_id = '$request->oeExamId' AND user_id = '$request->user_id' AND user_type = '$request->userType'"; | |
| try { | |
| $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getMessage(), $e->getCode()); | |
| } | |
| return true; | |
| } | |
| /** | |
| * get reval type details assigned to a digital valuation revaluation | |
| * @param $request | |
| * @throws ProfessionalException | |
| * @author Sibin | |
| */ | |
| public function getRevaluationTypeIdByOeRequest($request) | |
| { | |
| $revalType = new stdClass; | |
| $request = $this->realEscapeObject($request); | |
| $condition = ""; | |
| if($request->markEntryType){ | |
| $condition .= " AND erf.markEntryType ='$request->markEntryType'"; | |
| } | |
| $sql = "SELECT erss.exam_revaluation_fees_id as revaluationTypeId FROM exam_revaluation_student_subjects erss | |
| INNER JOIN exam_revaluation_fees erf ON erf.id = erss.exam_revaluation_fees_id | |
| WHERE erss.exam_revaluation_id IN($request->revalId) $condition GROUP BY erss.exam_revaluation_fees_id"; | |
| try { | |
| if ($request->revalId) { | |
| $revalType = $this->executeQueryForObject($sql); | |
| } | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $revalType; | |
| } | |
| /** | |
| * get is published revaluation | |
| * @param $request | |
| * @throws ProfessionalException | |
| * @author Sibin | |
| */ | |
| public function checkIsRevaluationPublishedDateRange($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $sql = "SELECT DISTINCT | |
| er.id, | |
| er.revalDesc, | |
| er.exam_registration_id, | |
| er.exam_supplementary_id, | |
| erbg.resultFromDate, | |
| erbg.resultToDate | |
| FROM | |
| exam_revaluation er | |
| JOIN | |
| exam_revaluation_batch_groups erbg ON erbg.exam_revaluation_id = er.id | |
| WHERE erbg.batchID = '$request->batchId' AND erbg.isResultPublished = 1 AND '" . date('Y-m-d') . "' BETWEEN erbg.resultFromDate AND erbg.resultToDate AND er.id = '$request->revaluationId'"; | |
| try { | |
| if ($sql) { | |
| $reval = $this->executeQueryForObject($sql); | |
| } | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $reval; | |
| } | |
| /** | |
| * get student revaluation subject remarks | |
| * @param revaluationTypeId | |
| * @return Boolean | |
| */ | |
| public function getStudentRevaluationSubjectRemarks($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $sql = ""; | |
| if ($request->studentId && $request->examId && $request->revaluationId && $request->revaluationTypeId) { | |
| $sql = "UPDATE exam_revaluation_student_subjects set properties = JSON_SET(IFNULL(properties, '{}'), '$.valuationRemarks','$request->remarks') | |
| WHERE studentID IN ($request->studentId) AND examID IN ($request->examId) AND exam_revaluation_id IN ($request->revaluationId) AND exam_revaluation_fees_id IN ($request->revaluationTypeId)"; | |
| $sql = "SELECT properties->>'$.valuationRemarks' AS valuationRemarks FROM exam_revaluation_student_subjects WHERE studentID IN ($request->studentId) AND examID IN ($request->examId) AND exam_revaluation_id IN ($request->revaluationId) AND exam_revaluation_fees_id IN ($request->revaluationTypeId)"; | |
| try { | |
| return $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| } | |
| /** | |
| * get staff By request | |
| * | |
| * @return object|NULL|$objectList[] | |
| * @throws ProfessionalException | |
| */ | |
| public function getDigitalValuationAssignedStaffs($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $sql = ""; | |
| $condition = ""; | |
| if ($request->revaluationId) { | |
| $condition .= " AND esas.revaluationId IN ($request->revaluationId)"; | |
| } | |
| if ($request->revaluationTypeId) { | |
| $condition .= " AND esas.revaluationTypeId IN ($request->revaluationTypeId)"; | |
| } | |
| if ($request->valuationCount) { | |
| $condition .= " AND esas.valuationCount IN ($request->valuationCount)"; | |
| } | |
| try { | |
| $sql = "SELECT esas.staffId as id,sa.staffName as name from examRevaluationStaffAssignedStudents esas | |
| INNER JOIN staffaccounts sa ON sa.staffID = esas.staffId | |
| WHERE 1=1 $condition group by esas.staffId order by sa.staffID"; | |
| return $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * get staff students By request | |
| * | |
| * @return object|NULL|$objectList[] | |
| * @throws ProfessionalException | |
| */ | |
| public function getDigitalValuationStaffAssignedStudents($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $sql = ""; | |
| $condition = ""; | |
| if ($request->revaluationId) { | |
| $condition .= " AND evss.revaluationId IN ($request->revaluationId)"; | |
| } | |
| if ($request->revaluationTypeId) { | |
| $condition .= " AND evss.revaluationTypeId IN ($request->revaluationTypeId)"; | |
| } | |
| if ($request->valuationCount) { | |
| $condition .= " AND evss.valuationCount IN ($request->valuationCount)"; | |
| } | |
| if ($request->staffId) { | |
| $condition .= " AND evss.staffId IN ($request->staffId)"; | |
| } | |
| try { | |
| $sql = "SELECT | |
| count(distinct(evss.studentId)) as assignedStudentCount, | |
| evss.valuationCount, | |
| evss.staffId, | |
| sa.staffName, | |
| e.subjectId | |
| FROM examRevaluationStaffAssignedStudents evss | |
| INNER JOIN exam e ON e.examID = evss.examId | |
| INNER JOIN staffaccounts sa ON | |
| sa.staffID = evss.staffId | |
| WHERE 1=1 $condition GROUP BY | |
| e.subjectId, | |
| evss.valuationCount, | |
| evss.staffId | |
| ORDER BY | |
| evss.valuationCount, | |
| evss.staffId"; | |
| return $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * get staff students confirmed By request | |
| * | |
| * @return object|NULL|$objectList[] | |
| * @throws ProfessionalException | |
| */ | |
| public function getMarkConfirmedStaffStudentsCountForSubjectValuation($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $sql = ""; | |
| $condition = ""; | |
| if ($request->revaluationId) { | |
| $condition .= " AND evss.revaluationId IN ($request->revaluationId)"; | |
| } | |
| if ($request->revaluationTypeId) { | |
| $condition .= " AND evss.revaluationTypeId IN ($request->revaluationTypeId)"; | |
| } | |
| if ($request->valuationCount) { | |
| $condition .= " AND evss.valuationCount IN ($request->valuationCount)"; | |
| } | |
| if ($request->staffId) { | |
| $condition .= " AND oec.created_by IN ($request->staffId)"; | |
| } | |
| if(!$request->getValuationStarted){ | |
| $condition .= " AND oec.is_confirmed = 1"; | |
| } | |
| try { | |
| $sql = "SELECT | |
| count(distinct oec.oe_users_id) as confirmedStudentCount, | |
| oec.valuation_count as valuationCount, | |
| oec.created_by as staffId, | |
| sf.staffName, | |
| oec.review_id, | |
| e.subjectID as subjectId | |
| FROM | |
| examRevaluationStaffAssignedStudents evss | |
| INNER JOIN studentaccount sa ON | |
| sa.studentID = evss.studentId | |
| INNER JOIN exam e ON | |
| e.examID = evss.examId | |
| INNER JOIN oe_exams oe ON | |
| JSON_UNQUOTE(JSON_EXTRACT(oe.identifying_context, '$.examId')) = e.examID | |
| INNER JOIN oe_exam_marks_confirm oec ON | |
| oec.oe_exams_id = oe.id | |
| AND oec.oe_users_id = evss.studentID | |
| AND oec.revaluation_id = evss.revaluationId | |
| AND oec.valuation_count = evss.valuationCount | |
| INNER JOIN staffaccounts sf ON | |
| sf.staffID = oec.created_by | |
| WHERE 1=1 $condition GROUP BY | |
| e.subjectID, | |
| oec.valuation_count, | |
| oec.created_by | |
| ORDER BY | |
| oec.valuation_count, | |
| oec.created_by"; | |
| return $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** get reval batche by reg students | |
| * @param $request | |
| * @throws ProfessionalException | |
| * @author Sibin | |
| */ | |
| public function getRevaluationBatchesByStudentRegistered($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $batches = null; | |
| $sql = null; | |
| try { | |
| $sql = "SELECT erbg.batchID as batchId,b.batchName,erbg.isResultPublished as published,erbg.resultFromDate as fromDate,erbg.resultToDate as toDate from exam_revaluation_batch_groups erbg | |
| INNER JOIN batches b ON b.batchID = erbg.batchID | |
| INNER JOIN exam_revaluation_student_details ersd ON ersd.exam_revaluation_id = erbg.exam_revaluation_id | |
| INNER JOIN studentaccount sa ON sa.studentID = ersd.studentID AND sa.batchID = erbg.batchID | |
| WHERE erbg.exam_revaluation_id='$request->revaluationId' GROUP BY erbg.batchID"; | |
| $batches = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $batches; | |
| } | |
| } | |