| <?php |
| namespace com\linways\core\ams\professional\service\examcontroller\examvaluationrule; |
| |
| use com\linways\core\ams\professional\service\BaseService; |
| use com\linways\core\ams\professional\constant\ExamType; |
| use com\linways\core\ams\professional\service\ExamService; |
| use com\linways\core\ams\professional\service\CourseTypeService; |
| use com\linways\core\ams\professional\exception\ProfessionalException; |
| use com\linways\core\ams\professional\mapper\ExamValuationRuleServiceMapper; |
| use com\linways\core\ams\professional\service\examcontroller\falsenumber\FalseNumberService; |
| use com\linways\core\ams\professional\constant\examcontroller\CourseTypeConstants; |
| use com\linways\core\ams\professional\dto\SettingsConstents; |
| use com\linways\core\ams\professional\service\CommonService; |
| use Aws\S3\S3Client; |
| use Aws\Credentials\Credentials; |
| use stdClass; |
| use com\linways\core\ams\professional\service\MarkService; |
| use com\linways\core\ams\professional\service\StaffService; |
| use com\linways\core\ams\professional\service\examcontroller\ExamRegistrationService; |
| |
| class ExamValuationRuleService extends BaseService |
| { |
| private static $_instance = null; |
| private $mapper = []; |
| |
| |
| private function __construct() { |
| $this->mapper = ExamValuationRuleServiceMapper::getInstance()->getMapper(); |
| } |
| |
| |
| |
| private function __clone() { |
| } |
| |
| |
| |
| public static function getInstance() { |
| if (! is_object ( self::$_instance )) |
| self::$_instance = new self (); |
| return self::$_instance; |
| } |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| public function getExamRegistrationDetails ( $examRegistrationDetailsRequest ) { |
| $sql = null; |
| $examRegistrationDetailsRequest = $this->realEscapeObject ( $examRegistrationDetailsRequest ); |
| $examRegistrationDetails = []; |
| $conditions = NULL; |
| $courseTypeCondition = ""; |
| |
| if($examRegistrationDetailsRequest->examYear) |
| { |
| if($examRegistrationDetailsRequest->examType == ExamType::REGULAR){ |
| $examYearCondition = " AND er.examYear = $examRegistrationDetailsRequest->examYear"; |
| }elseif($examRegistrationDetailsRequest->examType == ExamType::SUPPLY){ |
| $examYearCondition = " AND es.examYear = $examRegistrationDetailsRequest->examYear"; |
| }else{ |
| $examYearCondition = ""; |
| } |
| } |
| if($examRegistrationDetailsRequest->courseTypeId) |
| { |
| $courseTypeCondition = " AND b.courseTypeID = $examRegistrationDetailsRequest->courseTypeId "; |
| } |
| try { |
| |
| if($examRegistrationDetailsRequest->examType == ExamType::REGULAR){ |
| if ( $examRegistrationDetailsRequest->semId ) { |
| $conditions .= " AND erb.semID = $examRegistrationDetailsRequest->semId "; |
| } |
| $sql = "SELECT DISTINCT er.examregID AS examRegId,er.examregName AS examRegName,'REGULAR' AS examType FROM exam_registration er INNER JOIN exam_registration_batches erb ON er.examregID = erb.examregID INNER JOIN batches b ON erb.batchID = b.batchID WHERE er.examregID = er.examregID $courseTypeCondition $examYearCondition $conditions ORDER BY er.examYear DESC"; |
| |
| } |
| else if($examRegistrationDetailsRequest->examType == ExamType::SUPPLY) { |
| if ( $examRegistrationDetailsRequest->semId ) { |
| $conditions .= " AND es.semID = $examRegistrationDetailsRequest->semId "; |
| } |
| $sql = "SELECT DISTINCT es.id AS examRegId,es.supplyDesc AS examRegName,'SUPPLY' AS examType FROM exam_supplementary es LEFT JOIN supply_improve_batches sib ON es.id = sib.exam_supplementary_id INNER JOIN batches b ON sib.batchID = b.batchID WHERE es.id = es.id $courseTypeCondition $examYearCondition $conditions ORDER BY es.examYear DESC"; |
| |
| } |
| else if($examRegistrationDetailsRequest->examType == ExamType::BOTH){ |
| $sql = "(SELECT DISTINCT er.examregID AS examRegId,er.examregName AS examRegName,'REGULAR' AS examType FROM exam_registration er INNER JOIN exam_registration_batches erb ON er.examregID = erb.examregID INNER JOIN batches b ON erb.batchID = b.batchID WHERE erb.semID = $examRegistrationDetailsRequest->semId AND b.courseTypeID = $examRegistrationDetailsRequest->courseTypeId ORDER BY er.examYear DESC) UNION (SELECT DISTINCT es.id AS examRegId,es.supplyDesc AS examRegName,'SUPPLY' AS examType FROM exam_supplementary es INNER JOIN supply_improve_batches sib ON es.id = sib.exam_supplementary_id INNER JOIN batches b ON sib.batchID = b.batchID WHERE es.semID = $examRegistrationDetailsRequest->semId AND b.courseTypeID = $examRegistrationDetailsRequest->courseTypeId ORDER BY es.examYear DESC)"; |
| } |
| |
| $examRegistrationDetails = $this->executeQueryForList($sql); |
| |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); |
| } |
| |
| return $examRegistrationDetails; |
| } |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| public function getRegisteredExamSubjects ( $examRegistrationSubjectRequest ) { |
| $sql = null; |
| $examRegistrationSubjectRequest = $this->realEscapeObject ( $examRegistrationSubjectRequest ); |
| $subjectList = []; |
| $regularExamRegIdsArr = []; |
| $supplyExamRegIdsArr = []; |
| $condition = ""; |
| |
| foreach ($examRegistrationSubjectRequest->examRegistrationIds as $examRegIds) { |
| $examregId = $examRegIds["examregId"]; |
| $examType = $examRegIds["examType"]; |
| |
| if ($examType == ExamType::REGULAR ) { |
| $regularExamRegIdsArr[$examregId] = $examregId; |
| } else if ($examType == ExamType::SUPPLY ) { |
| $supplyExamRegIdsArr[$examregId] = $examregId; |
| } |
| } |
| if ($examRegistrationSubjectRequest->subjectCategoryIdList) { |
| $condition .= " AND s.subjectcatID IN (" . (implode(',', $examRegistrationSubjectRequest->subjectCategoryIdList) ? implode(',', $examRegistrationSubjectRequest->subjectCategoryIdList) : $examRegistrationSubjectRequest->subjectCategoryIdList) . ") "; |
| } |
| |
| if ( !empty ( $examRegistrationSubjectRequest->batchIds ) ) { |
| $batchIdStr = implode ( ',', $examRegistrationSubjectRequest->batchIds ); |
| if ( $batchIdStr ) { |
| $condition .= " AND e.batchID IN ( $batchIdStr ) "; |
| } |
| } |
| |
| $sqlArr = []; |
| if ( count ( $regularExamRegIdsArr ) ) { |
| $regularExamRegIdsStr = implode("," , $regularExamRegIdsArr); |
| |
| |
| $sqlArr[] = "SELECT s.subjectID, s.subjectName, s.subjectDesc, s.isTheory, e.examregID as examRegId, e.semID, 'REGULAR' as examType, COUNT(erss.studentID) AS examRegStudentCount, s.syllabusName FROM exam e INNER JOIN subjects s ON (s.subjectID = e.subjectID) INNER JOIN exam_reg_studentsubject erss ON (e.examregID = erss.examregID AND e.subjectID = erss.subjectID) INNER JOIN exam_reg_studentchallan ersc ON (ersc.studentID = erss.studentID AND ersc.examregID = erss.examregID AND ersc.paid = 1) INNER JOIN studentaccount sa on (sa.studentID = erss.studentID AND e.batchID = sa.batchID) WHERE e.semID = '$examRegistrationSubjectRequest->semId' AND e.examregID in ( $regularExamRegIdsStr ) AND s.courseTypeID = '$examRegistrationSubjectRequest->courseTypeId' $condition GROUP BY s.subjectID ORDER BY e.examDate ASC, s.subjectName ASC"; |
| } |
| |
| if ( count ( $supplyExamRegIdsArr ) ) { |
| $supplyExamRegIdsStr = implode("," , $supplyExamRegIdsArr); |
| |
| |
| $sqlArr[] = "SELECT DISTINCT s.subjectID , s.subjectName , s.subjectDesc, s.isTheory, e.supply_examreg_id as examRegId, e.semID, 'SUPPLY' as examType, COUNT(DISTINCT esss.studentID) as examRegStudentCount, s.syllabusName FROM subjects s INNER JOIN exam e ON e.subjectID = s.subjectID INNER JOIN exam e2 ON (e2.subjectID = e.subjectID) INNER JOIN exam_supplementary_student_subjects esss ON (esss.exam_supplementary_id = e.supply_examreg_id AND esss.examID = e2.examID) INNER JOIN exam_supplementary_student_details essd ON (essd.studentID = esss.studentID AND essd.exam_supplementary_id = e.supply_examreg_id AND essd.paid = 1 AND essd.approved = 1 ) LEFT JOIN subject_sem_relation ssr ON ( s.subjectID = ssr.subjectID AND ssr.semID = e.semID) WHERE e.semID = IF (ssr.semID, ssr.semID, s.semID) AND e.semID = '$examRegistrationSubjectRequest->semId' AND e.supply_examreg_id in ( $supplyExamRegIdsStr ) AND s.courseTypeID = '$examRegistrationSubjectRequest->courseTypeId' $condition GROUP BY s.subjectID ORDER BY e.examDate ASC, s.subjectName ASC"; |
| } |
| try { |
| $sql = implode ( " UNION " , $sqlArr ); |
| $subjectList = $this->executeQueryForList($sql); |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); |
| } |
| return $subjectList; |
| } |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| public function getRegisteredExamAnswerSheetGroups ( $request ) { |
| $sql = null; |
| $request = $this->realEscapeObject ( $request ); |
| $registeredExamAnswerSheetGroupDetails = []; |
| try { |
| if($request->examType == ExamType::REGULAR){ |
| $sql = "SELECT answersheet_groupNo AS groupNo FROM exam_examReg_subject_answerSheet_group WHERE examRegId = $request->examRegId AND subjectId = $request->subjectId AND semId = $request->semId"; |
| }elseif($request->examType == ExamType::SUPPLY){ |
| $sql = "SELECT answersheet_groupNo AS groupNo FROM exam_examReg_subject_answerSheet_group WHERE supplyExamRegId = $request->examRegId AND subjectId = $request->subjectId AND semId = $request->semId"; |
| } |
| |
| $registeredExamAnswerSheetGroupDetails = $this->executeQueryForList($sql); |
| |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); |
| } |
| |
| return $registeredExamAnswerSheetGroupDetails; |
| } |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| public function getRegisteredExamValuationStaffs ( $request ) { |
| $sql = null; |
| $request = $this->realEscapeObject ( $request ); |
| $registeredExamValuationStaffDetails = []; |
| try { |
| |
| if($request->examType == ExamType::REGULAR){ |
| $sql = "SELECT staffId AS valuationStaffId FROM exam_examReg_subject_valuation_staff WHERE examRegId = $request->examRegId AND subjectId = $request->subjectId AND semId = $request->semId"; |
| }elseif($request->examType == ExamType::SUPPLY){ |
| $sql = "SELECT staffId AS valuationStaffId FROM exam_examReg_subject_valuation_staff WHERE supplyExamRegId = $request->examRegId AND subjectId = $request->subjectId AND semId = $request->semId"; |
| } |
| |
| $registeredExamValuationStaffDetails = $this->executeQueryForList($sql); |
| |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); |
| } |
| |
| return $registeredExamValuationStaffDetails; |
| } |
| |
| public function getSubjectAnswersheetGroup ($examRegistrationSubjectRequest) { |
| $sql = null; |
| $packetSubjectRelation = null; |
| $examRegistrationSubjectRequest = $this->realEscapeObject ( $examRegistrationSubjectRequest ); |
| if ( !empty ( $examRegistrationSubjectRequest->examRegistrationIds ) ) { |
| $regIds = implode(",", $examRegistrationSubjectRequest->examRegistrationIds); |
| } |
| else { |
| $regIds = $examRegistrationSubjectRequest->examRegId; |
| } |
| |
| if ( $examRegistrationSubjectRequest->examType == ExamType::REGULAR ) { |
| $condition = " AND esag.examRegId IN ($regIds) "; |
| } |
| else if ( $examRegistrationSubjectRequest->examType == ExamType::SUPPLY ) { |
| $condition = " AND esag.supplyExamRegId IN ($regIds) "; |
| } |
| if($examRegistrationSubjectRequest->packetNo){ |
| $condition = " AND $examRegistrationSubjectRequest->packetNo BETWEEN esag.packet_start_no and esag.packet_end_no "; |
| } |
| |
| $sql = "SELECT esag.id, esag.examType, esag.examRegId, esag.supplyExamRegId, esag.semId, esag.subjectId, s.subjectName, s.subjectDesc, esagc.registeredStudentCount, esagc.studentsPerAnswerSheetGroup, esagc.studentsPerAnswerSheetGroupCount, esag.packet_start_no, esag.packet_end_no ,s.syllabusName FROM exam_examReg_subject_answerSheet_group esag INNER JOIN exam_examReg_subject_answerSheet_group_config esagc ON (esag.examReg_subject_answerSheet_group_config_id = esagc.id) INNER JOIN subjects s ON (s.subjectID = esag.subjectId) WHERE esag.semId = '$examRegistrationSubjectRequest->semId' $condition "; |
| try { |
| if ( $examRegistrationSubjectRequest->subjectId ) { |
| $sql .= " AND esag.subjectId = '$examRegistrationSubjectRequest->subjectId' "; |
| } |
| $sql .= " ORDER BY esag.packet_start_no ASC "; |
| $packetSubjectRelation = $this->executeQueryForList($sql); |
| |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); |
| } |
| return $packetSubjectRelation; |
| } |
| |
| public function saveExamValuationPacketSubjectRelation($packetSubjectRelationArr) { |
| $packetSubjectRelationArr = $this->realEscapeArray($packetSubjectRelationArr); |
| $values = $details = []; |
| |
| $sql = "INSERT INTO exam_examReg_subject_answerSheet_group_config (examType, examRegId, supplyExamRegId, semId, subjectId, registeredStudentCount, studentsPerAnswerSheetGroup, studentsPerAnswerSheetGroupCount, createdBy, createdDate, updatedBy, updatedDate) VALUES "; |
| foreach ( $packetSubjectRelationArr as $packetSubjectRelation ) { |
| $examRegId = $supplyExamRegId = 'NULL'; |
| $examType = $packetSubjectRelation->examType; |
| if ( $examType === ExamType::REGULAR ) { |
| $examRegId = $packetSubjectRelation->examRegId; |
| } |
| else if ( $examType === ExamType::SUPPLY ) { |
| $supplyExamRegId = $packetSubjectRelation->examRegId; |
| } |
| $values[] = "( |
| '".$examType."', |
| ".$examRegId.", |
| ".$supplyExamRegId.", |
| ".$packetSubjectRelation->semId.", |
| ".$packetSubjectRelation->subjectId.", |
| ".$packetSubjectRelation->regStudentCount.", |
| ".$packetSubjectRelation->studentPerPacket.", |
| ".$packetSubjectRelation->packetCount.", |
| ".$packetSubjectRelation->createdBy.", |
| utc_timestamp(), |
| ".$packetSubjectRelation->createdBy.", |
| utc_timestamp() |
| )"; |
| |
| $details[$packetSubjectRelation->examRegId]->examType = $examType; |
| $details[$packetSubjectRelation->examRegId]->semId = $packetSubjectRelation->semId; |
| } |
| try { |
| $sql = $sql . implode ( ",", $values ); |
| $this->executeQuery($sql); |
| |
| foreach ( $details as $examRegId => $detail ) { |
| $examType = $detail->examType; |
| $condition = null; |
| if ( $examType == ExamType::REGULAR ) { |
| $condition = " AND examRegId = '$examRegId' "; |
| } |
| else if ( $examType == ExamType::SUPPLY ) { |
| $condition = " AND supplyExamRegId = '$examRegId' "; |
| } |
| $packetNo = $this->getNextPacketNumber ( $examType, $examRegId ); |
| $sql = "INSERT INTO exam_examReg_subject_answerSheet_group (examType, examRegId, supplyExamRegId, semId, subjectId, packet_start_no, packet_end_no, examReg_subject_answerSheet_group_config_id) SELECT examType, examRegId, supplyExamRegId, semId, subjectId, @packetNo := @packetNo + 1 packetStartNo, @packetNo := (@packetNo + studentsPerAnswerSheetGroupCount - 1) packetEndNo, id FROM (SELECT @packetNo:= $packetNo - 1) a, exam_examReg_subject_answerSheet_group_config WHERE examType = '$examType' $condition AND semId = $detail->semId AND id NOT IN (SELECT examReg_subject_answerSheet_group_config_id FROM exam_examReg_subject_answerSheet_group)"; |
| $this->executeQuery($sql); |
| } |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); |
| } |
| } |
| |
| public function saveExamValuationSubjectStaffRelation ( $subjectStaffRelationArr ) { |
| $subjectStaffRelationArr = $this->realEscapeArray($subjectStaffRelationArr); |
| $values = []; |
| foreach ( $subjectStaffRelationArr as $subjectStaffRelation ) { |
| $staffsToBeDeleted = []; |
| $staffsToBeAdded = []; |
| $existingValuationStaffArr = []; |
| |
| $existingValuationStaff = $this->getFacultiesAssignedForSubjectValuation ( $subjectStaffRelation ); |
| foreach ( $existingValuationStaff as $staff ) { |
| $existingValuationStaffArr[] = $staff->staffID; |
| } |
| $staffsToBeDeleted = array_diff ( $existingValuationStaffArr, $subjectStaffRelation->staff ); |
| $staffsToBeAdded = array_diff ( $subjectStaffRelation->staff, $existingValuationStaffArr ); |
| |
| if ( !empty ( $staffsToBeDeleted ) ) { |
| $subjectStaffRelation->staff = $staffsToBeDeleted; |
| $this->deleteExamValuationSubjectStaffRelation ( $subjectStaffRelation ); |
| } |
| |
| if ( !empty ( $staffsToBeAdded ) ) { |
| $examRegId = $supplyExamRegId = 'NULL'; |
| if ( $subjectStaffRelation->examType === ExamType::REGULAR ) { |
| $examRegId = $subjectStaffRelation->examRegId; |
| } |
| else if ( $subjectStaffRelation->examType === ExamType::SUPPLY ) { |
| $supplyExamRegId = $subjectStaffRelation->examRegId; |
| } |
| foreach ( $staffsToBeAdded as $staffId ) { |
| $values[] = " ( |
| '".$subjectStaffRelation->examType."', |
| ".$examRegId.", |
| ".$supplyExamRegId.", |
| ".$subjectStaffRelation->semId.", |
| ".$subjectStaffRelation->subjectId.", |
| ".$staffId.", |
| ".$subjectStaffRelation->createdBy.", |
| utc_timestamp(), |
| ".$subjectStaffRelation->createdBy.", |
| utc_timestamp() |
| )"; |
| } |
| } |
| } |
| try { |
| if ( !empty ( $values ) ) { |
| $sql = "INSERT INTO exam_examReg_subject_valuation_staff (examType, examRegId, supplyExamRegId, semId, subjectId, staffId, createdBy, createdDate, updatedBy, updatedDate) VALUES " . implode ( ",", $values ) . " ON DUPLICATE KEY UPDATE staffId = VALUES (staffId), updatedDate = VALUES (updatedDate)"; |
| $this->executeQuery($sql); |
| } |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); |
| } |
| } |
| |
| public function deleteExamValuationSubjectStaffRelation ( $subjectStaffRelation ) { |
| $subjectStaffRelation = $this->realEscapeObject($subjectStaffRelation); |
| $condition = null; |
| if ( $subjectStaffRelation->examType == ExamType::REGULAR ) { |
| $condition .= " AND examRegId = '$subjectStaffRelation->examRegId' "; |
| } |
| else if ( $subjectStaffRelation->examType == ExamType::SUPPLY ) { |
| $condition .= " AND supplyExamRegId = '$subjectStaffRelation->examRegId' "; |
| } |
| $condition .= " AND staffId IN (".implode(',', $subjectStaffRelation->staff ).") "; |
| $sql = "DELETE FROM exam_examReg_subject_valuation_staff WHERE semId = '$subjectStaffRelation->semId' AND subjectId = '$subjectStaffRelation->subjectId' $condition "; |
| try { |
| $this->executeQuery($sql); |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); |
| } |
| } |
| |
| public function getNextPacketNumber ( $examType, $examRegId ) { |
| |
| $packetNo = NULL; |
| if ( $examType === ExamType::REGULAR ) { |
| $sql = "SELECT examMonth, examYear FROM exam_registration WHERE examregID = '$examRegId' "; |
| } |
| else if ( $examType === ExamType::SUPPLY ) { |
| $sql = "SELECT examMonth, examYear FROM exam_supplementary WHERE id = '$examRegId' "; |
| } |
| $examMonthYear = $this->executeQueryForObject($sql); |
| |
| $sql = "SELECT IF(esag.packet_end_no, MAX(esag.packet_end_no) + 1, 1) AS packetNo FROM exam_examReg_subject_answerSheet_group esag WHERE esag.examRegId IN ( SELECT er.examregID FROM exam_registration er WHERE er.examMonth = $examMonthYear->examMonth AND er.examYear = $examMonthYear->examYear ) OR esag.supplyExamRegId IN ( SELECT es.id FROM exam_supplementary es WHERE es.examMonth = $examMonthYear->examMonth AND es.examYear = $examMonthYear->examYear)"; |
| |
| try { |
| $packetNo = $this->executeQueryForObject($sql)->packetNo; |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); |
| } |
| return $packetNo; |
| } |
| |
| |
| |
| |
| |
| |
| |
| |
| public function getFacultiesAssignedForSubjectValuation ( $examRegistrationSubjectRequest ) { |
| $condition = null; |
| $staffDetails = null; |
| if ( $examRegistrationSubjectRequest->examType === ExamType::REGULAR ) { |
| $condition = " AND esvs.examRegId = '$examRegistrationSubjectRequest->examRegId' "; |
| } |
| else if ( $examRegistrationSubjectRequest->examType === ExamType::SUPPLY ) { |
| $condition = " AND esvs.supplyExamRegId = '$examRegistrationSubjectRequest->examRegId' "; |
| } |
| |
| $sql = "SELECT sa.staffID, sa.staffName, esvs.id FROM staffaccounts sa INNER JOIN exam_examReg_subject_valuation_staff esvs ON (sa.staffID = esvs.staffId) WHERE |
| esvs.semId = '$examRegistrationSubjectRequest->semId' AND |
| esvs.subjectId = '$examRegistrationSubjectRequest->subjectId' $condition "; |
| try { |
| $staffDetails = $this->executeQueryForList($sql); |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); |
| } |
| return $staffDetails; |
| } |
| |
| public function getFacultiesAssignedForValuation ( $examRegistrationSubjectRequest ) { |
| $condition = null; |
| $staffDetails = null; |
| $regIds = implode(",", $examRegistrationSubjectRequest->examRegistrationIds); |
| if ( $examRegistrationSubjectRequest->examType === ExamType::REGULAR ) { |
| $condition = " AND esvs.examRegId IN ($regIds) "; |
| } |
| else if ( $examRegistrationSubjectRequest->examType === ExamType::SUPPLY ) { |
| $condition = " AND esvs.supplyExamRegId IN ($regIds) "; |
| } |
| |
| $sql = "SELECT esvs.subjectId, esvs.examRegId, esvs.supplyExamRegId, sa.deptID, sa.staffID, sa.staffName FROM staffaccounts sa INNER JOIN exam_examReg_subject_valuation_staff esvs ON (sa.staffID = esvs.staffId) WHERE esvs.semId = '$examRegistrationSubjectRequest->semId' $condition "; |
| try { |
| $staffDetails = $this->executeQueryForList($sql); |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); |
| } |
| return $staffDetails; |
| } |
| |
| |
| |
| |
| |
| |
| |
| public function getExamValuationStudentmarks ( $examValuationStudentMark ) { |
| $examValuationStudentMark = $this->realEscapeObject ( $examValuationStudentMark ); |
| $condition = null; |
| $markDetails = []; |
| if ( $examValuationStudentMark->examType === ExamType::REGULAR ) { |
| $condition = " AND asvs.examRegId = '$examValuationStudentMark->examRegId' "; |
| } |
| else if ( $examValuationStudentMark->examType === ExamType::SUPPLY ) { |
| $condition = " AND asvs.supplyExamRegId = '$examValuationStudentMark->examRegId' "; |
| } |
| $sql = "SELECT asvs.id, asvs.examType, asvs.examRegId, asvs.supplyExamRegId, asvs.examId, asvs.studentId, sa.regNo, ee.mark, asvs.valuatedStaffId, asvs.examReg_subject_answerSheet_group_id FROM exam_answerSheetGroup_student_valuated_staff asvs INNER JOIN exammarks_external ee ON (ee.id = asvs.exammarks_external_id AND asvs.examId = ee.examID AND ee.studentID = asvs.studentId) INNER JOIN studentaccount sa ON (asvs.studentID = sa.studentID) WHERE asvs.packetNo = '$examValuationStudentMark->packetNo' $condition "; |
| try { |
| $markDetails = $this->executeQueryForList($sql); |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); |
| } |
| return $markDetails; |
| } |
| |
| |
| |
| |
| |
| |
| |
| public function saveExamValuationStudentmarks ( $examValuation ) { |
| $examValuation = $this->realEscapeObject ( $examValuation ); |
| $conditions = $markValues = []; |
| $delete_externalexammarks_finalized = $delete_externalexam_thirdvalstudents = []; |
| |
| |
| if ( !empty ($examValuation) ) { |
| try { |
| $examRegId = $supplyExamRegId = 'NULL'; |
| if ( $examValuation->examType === ExamType::REGULAR ) { |
| $examRegId = $examValuation->examRegId; |
| } |
| else if ( $examValuation->examType === ExamType::SUPPLY ) { |
| $supplyExamRegId = $examValuation->examRegId; |
| } |
| $studentMarks = []; |
| $external_exammarks = []; |
| foreach ($examValuation->markDetails as $details) { |
| |
| |
| |
| |
| |
| $conditions[] = " (ee.examID = ".$details->examId." AND ee.studentID = ".$details->studentId.") "; |
| $studentMarks[$details->studentId][$details->examId] = $details->mark; |
| $studentExams[$details->studentId] = $details->examId; |
| |
| $markValues[] = "( ".$details->examId.", ".$details->studentId.", ".$details->mark.", ".$examValuation->adminID." )"; |
| |
| $externalexammarks_finalized_pg_practical[] = "( ".$details->examId.", ".$details->studentId.", ".$details->mark." )"; |
| |
| if ( $examValuation->valuationNo == 2 || $examValuation->valuationNo == 3 ) { |
| $external_exammarks[] = "( ".$details->examId.", ".$details->studentId.", ".$details->mark.", ".$examValuation->valuationNo." )"; |
| } |
| } |
| |
| if ( $examValuation->valuationNo == 2 ) { |
| $markDiff = ExamService::getInstance()->getExternalValuationMarkDiff($examValuation->courseTypeId); |
| if ( count ( $conditions ) ) { |
| $studentExternalMarks = ExamService::getInstance()->getStudentsExternalMarksByStudentExamDetail( $conditions ); |
| if ( !empty ( $studentExternalMarks ) ) { |
| foreach ($studentExternalMarks as $markDetail ) { |
| if ( $markDetail->valuationCount == 1 ) { |
| $studentSecondValMarks = $studentMarks[$markDetail->studentID][$markDetail->examID]; |
| $studentMarkDiff = abs ($markDetail->studentExternalMark - $studentSecondValMarks); |
| |
| if ( $studentMarkDiff >= $markDiff ) { |
| $externalexam_thirdvalstudents[] = "( ".$markDetail->examID.", ".$markDetail->studentID." )"; |
| |
| $delete_externalexammarks_finalized[] = "( examID = '$markDetail->examID' AND studentID = '$markDetail->studentID' ) "; |
| } |
| else { |
| $studentMark = ( $markDetail->studentExternalMark + $studentSecondValMarks ) / 2; |
| $externalexammarks_finalized[] = "( ".$markDetail->examID.", ".$markDetail->studentID.", ".$studentMark." )"; |
| |
| $delete_externalexam_thirdvalstudents[] = "( examID = '$markDetail->examID' AND studentID = '$markDetail->studentID' ) "; |
| } |
| } |
| } |
| } |
| } |
| } |
| else if ( $examValuation->valuationNo == 3 ) { |
| if ( count ( $conditions ) ) { |
| $studentExternalMarks = ExamService::getInstance()->getStudentsExternalMarksByStudentExamDetail( $conditions ); |
| if ( !empty ( $studentExternalMarks ) ) { |
| foreach ($studentExternalMarks as $markDetail ) { |
| $studentMarks[$markDetail->studentID][] = $markDetail->studentExternalMark; |
| } |
| foreach ( $studentMarks as $studentID => $studentMark ) { |
| $examID = $studentExams[$studentID]; |
| |
| |
| |
| sort($studentMark); |
| $diff_01 = abs ( $studentMark[0] - $studentMark[1] ); |
| $diff_12 = abs ( $studentMark[1] - $studentMark[2] ); |
| if ( $diff_01 < $diff_12 ) { |
| $nearestTwo = array_slice($studentMark, 0, 2); |
| } else { |
| $nearestTwo = array_slice($studentMark, 1, 2); |
| } |
| |
| $avgMark = array_sum($nearestTwo) / 2; |
| $externalexammarks_finalized[] = "( ".$examID.", ".$studentID.", ".$avgMark." )"; |
| } |
| } |
| } |
| } |
| |
| if ( count ( $markValues ) ) { |
| |
| |
| |
| |
| if ( $examValuation->valuationNo == 1 ) { |
| $sql_mark = "INSERT INTO exammarks_external (examID, studentID, mark, adminID) VALUES " . implode ( ',', $markValues ) . " ON DUPLICATE KEY UPDATE mark = VALUES(mark)"; |
| $this->executeQuery($sql_mark); |
| if ( count($conditions)) { |
| if ( $examValuation->facultyId == null ) { |
| $examValuation->facultyId = 'NULL'; |
| } |
| else { |
| $examValuation->facultyId = "'".$examValuation->facultyId."'"; |
| } |
| $sql_val = "INSERT INTO exam_answerSheetGroup_student_valuated_staff (examType, examRegId, supplyExamRegId, examId, studentId, valuatedStaffId, examReg_subject_answerSheet_group_id, packetNo, exammarks_external_id, mark_inserted_staffId, valuationType, createdBy, createdDate, updatedBy, updatedDate ) SELECT '".$examValuation->examType."', ".$examRegId.", ".$supplyExamRegId.", ee.examID, ee.studentID, ".$examValuation->facultyId.", ".$examValuation->esagId.", ".$examValuation->packetNo.", ee.id, ".$examValuation->adminID.", '".$examValuation->valuationType."', 1, utc_timestamp(), 1, utc_timestamp() FROM exammarks_external ee WHERE " . implode ( ' OR ', $conditions ) . " ON DUPLICATE KEY UPDATE updatedDate = VALUES(updatedDate) ,valuatedStaffId = VALUES(valuatedStaffId)"; |
| $this->executeQuery($sql_val); |
| } |
| |
| |
| |
| |
| |
| $subject = $this->checkForTheoryPracticalSubject( $examValuation->subjectId, $examValuation->courseTypeId); |
| if (($subject->isTheory == 0 && $subject->isPG == 1) || $subject->courseType == "PGD") { |
| $sql_mark = "INSERT INTO externalexammarks_finalized (examID, studentID, mark) VALUES " . implode ( ',', $externalexammarks_finalized_pg_practical ) . " ON DUPLICATE KEY UPDATE mark = VALUES(mark)"; |
| $this->executeQuery($sql_mark); |
| } |
| } |
| else if ( $examValuation->valuationNo == 2 || $examValuation->valuationNo == 3 ) { |
| if ( !empty ( $external_exammarks ) ) { |
| $sql_mark = "INSERT INTO external_exammarks (examID, studentID, mark, valuationCount) VALUES " . implode ( ',', $external_exammarks ) . " ON DUPLICATE KEY UPDATE mark = VALUES(mark) "; |
| $this->executeQuery($sql_mark); |
| } |
| if ( !empty ( $externalexam_thirdvalstudents ) && $examValuation->valuationNo == 2 ) { |
| $sql_mark = "INSERT INTO externalexam_thirdvalstudents (examID, studentID) VALUES " . implode ( ',', $externalexam_thirdvalstudents ) . " ON DUPLICATE KEY UPDATE examID = VALUES(examID) " ; |
| $this->executeQuery($sql_mark); |
| |
| $sql_del = "DELETE FROM externalexammarks_finalized WHERE " . implode(" OR ", $delete_externalexammarks_finalized); |
| $this->executeQuery($sql_del); |
| } |
| if ( !empty ( $externalexammarks_finalized ) ) { |
| $sql_mark = "INSERT INTO externalexammarks_finalized (examID, studentID, mark) VALUES " . implode ( ',', $externalexammarks_finalized ) . " ON DUPLICATE KEY UPDATE mark = VALUES(mark)"; |
| $this->executeQuery($sql_mark); |
| if ( $examValuation->valuationNo == 2 ) { |
| $sql_del = "DELETE FROM externalexam_thirdvalstudents WHERE " . implode(" OR ", $delete_externalexam_thirdvalstudents); |
| $this->executeQuery($sql_del); |
| } |
| } |
| if ( count($conditions)) { |
| if ( $examValuation->facultyId == null ) { |
| $examValuation->facultyId = 'NULL'; |
| } |
| else { |
| $examValuation->facultyId = "'".$examValuation->facultyId."'"; |
| } |
| $sql_val = "INSERT INTO exam_answerSheetGroup_student_valuated_staff (examType, examRegId, supplyExamRegId, examId, studentId, valuatedStaffId, examReg_subject_answerSheet_group_id, packetNo, external_exammarks_id, mark_inserted_staffId, valuationType, createdBy, createdDate, updatedBy, updatedDate ) SELECT '".$examValuation->examType."', ".$examRegId.", ".$supplyExamRegId.", ee.examID, ee.studentID, ".$examValuation->facultyId.", ".$examValuation->esagId.", ".$examValuation->packetNo.", ee.exstdntmarkID, ".$examValuation->adminID.", '".$examValuation->valuationType."', 1, utc_timestamp(), 1, utc_timestamp() FROM external_exammarks ee WHERE " . implode ( ' OR ', $conditions ) . " AND valuationCount = '$examValuation->valuationNo' ON DUPLICATE KEY UPDATE updatedDate = VALUES(updatedDate) ,valuatedStaffId = VALUES(valuatedStaffId)"; |
| $this->executeQuery($sql_val); |
| } |
| } |
| } |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); |
| } |
| } |
| } |
| |
| |
| |
| |
| |
| |
| |
| |
| public function getExamRegisteredStudentForSubject ( $examRegistrationSubjectRequest ) { |
| $sql = null; |
| $examRegistrationSubjectRequest = $this->realEscapeObject ( $examRegistrationSubjectRequest ); |
| $studentDetails = []; |
| try { |
| if ($examRegistrationSubjectRequest->examType == ExamType::REGULAR ) { |
| $sql = "SELECT sa.studentID, sa.regNo, sa.batchID, e.examID, e.examTotalMarks,e.valuationMaxMark FROM exam_reg_studentsubject ers INNER JOIN studentaccount sa ON (ers.studentID = sa.studentID) INNER JOIN exam e ON (e.subjectID = ers.subjectID AND sa.batchID = e.batchID AND e.examregID = ers.examRegId) INNER JOIN exam_reg_studentchallan ersc ON (ersc.studentID = ers.studentID AND ersc.examregID = ers.examregID AND ersc.paid = 1) WHERE e.semID = '$examRegistrationSubjectRequest->semId' AND ers.examRegId = '$examRegistrationSubjectRequest->examRegId' AND ers.subjectID = '$examRegistrationSubjectRequest->subjectId' "; |
| } |
| else if ($examRegistrationSubjectRequest->examType == ExamType::SUPPLY ) { |
| $sql = "SELECT sa.studentID, sa.regNo, sa.batchID, e.examID, e.examTotalMarks,e.valuationMaxMark FROM exam e INNER JOIN exam e1 ON (e.subjectID = e1.subjectID AND e.semID = e1.semID AND e.batchID = e1.batchID AND e1.supply_examreg_id IS NULL ) INNER JOIN exam_supplementary_student_subjects esss ON (esss.examID = e1.examID AND esss.exam_supplementary_id = e.supply_examreg_id) INNER JOIN studentaccount sa ON (sa.studentID = esss.studentID AND sa.batchID = e.batchID) WHERE e.supply_examreg_id = '$examRegistrationSubjectRequest->examRegId' AND e.subjectID = '$examRegistrationSubjectRequest->subjectId' "; |
| } |
| $studentDetails = $this->executeQueryForList($sql); |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); |
| } |
| return $studentDetails; |
| } |
| |
| |
| |
| |
| |
| |
| |
| |
| public function getExternalExamValuationStudentmarks ( $examValuationStudent ) { |
| $examValuationStudent = $this->realEscapeObject ( $examValuationStudent ); |
| $condition = null; |
| $studentMarks = null; |
| if ( $examValuationStudent->examType === ExamType::REGULAR ) { |
| $condition = " AND esvs.examRegId = '$examValuationStudent->examRegId' "; |
| } |
| else if ( $examValuationStudent->examType === ExamType::SUPPLY ) { |
| $condition = " AND esvs.supplyExamRegId = '$examValuationStudent->examRegId' "; |
| } |
| try { |
| $sql = "SELECT DISTINCT esvs.studentId, esvs.examId, eme.mark AS firstValMark, eem.valuationCount, eem.mark, IF(ets.examID, 1, 0) AS thirdValReq FROM exam_answerSheetGroup_student_valuated_staff esvs INNER JOIN exam_examReg_subject_answerSheet_group esag ON (esvs.examReg_subject_answerSheet_group_id = esag.id ) INNER JOIN studentaccount sa ON (sa.studentID = esvs.studentId) LEFT JOIN exammarks_external eme ON (eme.id = esvs.exammarks_external_id AND eme.examID = esvs.examId AND eme.studentID = esvs.studentId) LEFT JOIN external_exammarks eem ON (eem.exstdntmarkID = esvs.external_exammarks_id AND eem.examID = esvs.examId AND eem.studentID = esvs.studentId) LEFT JOIN externalexam_thirdvalstudents ets ON (ets.studentID = esvs.studentId AND ets.examID = esvs.examId) WHERE esvs.packetNo = '$examValuationStudent->packetNo' AND esag.semId = '$examValuationStudent->semId' AND esag.subjectId = '$examValuationStudent->subjectId' AND sa.regNo = '$examValuationStudent->regNo' $condition ORDER BY eem.valuationCount ASC"; |
| $studentMarks = $this->executeQueryForList($sql); |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); |
| } |
| return $studentMarks; |
| } |
| |
| |
| |
| |
| |
| |
| |
| |
| public function getExamValuationReport( $examValuationRequest ) { |
| $examValuationRequest = $this->realEscapeObject ( $examValuationRequest ); |
| $sql = null; |
| $condition = null; |
| $examValuation = null; |
| |
| if ( $examValuationRequest->examType === ExamType::REGULAR ) { |
| $condition = " AND eesag.examRegId = '$examValuationRequest->examRegId' "; |
| } |
| else if ( $examValuationRequest->examType === ExamType::SUPPLY ) { |
| $condition = " AND eesag.supplyExamRegId = '$examValuationRequest->examRegId' "; |
| } |
| try { |
| $sql = "SELECT s.subjectName, s.subjectDesc, '$examValuationRequest->packetNo' AS packetNo, sem.semName, IF(eesag.examRegId, er.examregName, es.supplyDesc) AS examRegName FROM exam_examReg_subject_answerSheet_group eesag INNER JOIN subjects s ON (s.subjectID = eesag.subjectId) INNER JOIN semesters sem ON (sem.semID = eesag.semId) LEFT JOIN exam_registration er ON (er.examregID = eesag.examRegId) LEFT JOIN exam_supplementary es ON (es.id = eesag.supplyExamRegId) WHERE eesag.subjectId = '$examValuationRequest->subjectId' AND eesag.semId = '$examValuationRequest->semId' $condition "; |
| $examValuation = $this->executeQueryForObject($sql); |
| $examValuation->markDetails = $this->getExamValuationReportStudentmarks ($examValuationRequest); |
| |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); |
| } |
| return $examValuation; |
| } |
| |
| public function getExamValuationHeaderDetails($examRegistrationSubjectRequest) { |
| $examRegistrationSubjectRequest = $this->realEscapeObject ( $examRegistrationSubjectRequest ); |
| $sql = null; |
| $condition = null; |
| $examValuationHeader = null; |
| if ( !empty ( $examRegistrationSubjectRequest->examRegistrationIds ) ) { |
| $regIds = implode(",", $examRegistrationSubjectRequest->examRegistrationIds); |
| } |
| if ( $examRegistrationSubjectRequest->examType === ExamType::REGULAR ) { |
| $condition = " AND eesag.examRegId IN ($regIds) "; |
| } |
| else if ( $examRegistrationSubjectRequest->examType === ExamType::SUPPLY ) { |
| $condition = " AND eesag.supplyExamRegId IN ($regIds) "; |
| } |
| try { |
| $sql = "SELECT sem.semName, GROUP_CONCAT( DISTINCT IF(eesag.examRegId, er.examregName, es.supplyDesc)) AS examRegName FROM exam_examReg_subject_answerSheet_group eesag INNER JOIN semesters sem ON (sem.semID = eesag.semId) LEFT JOIN exam_registration er ON (er.examregID = eesag.examRegId) LEFT JOIN exam_supplementary es ON (es.id = eesag.supplyExamRegId) WHERE eesag.semId = '$examRegistrationSubjectRequest->semId' $condition "; |
| $examValuationHeader = $this->executeQueryForObject($sql); |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); |
| } |
| return $examValuationHeader; |
| } |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| public function checkForTheoryPracticalSubject( $subjectId, $courseTypeId = NULL ) { |
| $subjectId = $this->realEscapeString($subjectId); |
| $courseTypeId = $this->realEscapeString($courseTypeId); |
| $subject = null; |
| try{ |
| if ( $courseTypeId ) { |
| $sql = "SELECT s.isTheory, IF ( ct.course_Type = 'PG', 1, IF ( ct.course_Type = 'UG', 0, NULL) ) AS isPG,ct.course_Type as courseType FROM subjects s INNER JOIN course_type ct ON (s.courseTypeID = ct.courseTypeID) WHERE s.subjectID = '$subjectId' AND s.courseTypeID = '$courseTypeId' "; |
| } |
| else { |
| $sql = "SELECT s.isTheory FROM subjects s WHERE s.subjectID = '$subjectId'"; |
| } |
| $subject = $this->executeQueryForObject($sql); |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); |
| } |
| return $subject; |
| } |
| |
| |
| |
| |
| |
| |
| |
| public function getExamValuationReportStudentmarks ($examValuationRequest) { |
| $examValuationRequest = $this->realEscapeObject ( $examValuationRequest ); |
| $sql_marks = null; |
| $condition = null; |
| $studentMarkDetails = []; |
| |
| if ( $examValuationRequest->examType === ExamType::REGULAR ) { |
| $condition = " AND eesag.examRegId = '$examValuationRequest->examRegId' "; |
| } |
| else if ( $examValuationRequest->examType === ExamType::SUPPLY ) { |
| $condition = " AND eesag.supplyExamRegId = '$examValuationRequest->examRegId' "; |
| } |
| |
| if ( intval($examValuationRequest->packetNo) ) { |
| $condition .= " AND easvs.packetNo = '$examValuationRequest->packetNo' "; |
| } |
| try { |
| $sql_marks = "SELECT sa.regNo, easvs.packetNo, GROUP_CONCAT(staff.staffID) AS staffID, GROUP_CONCAT(staff.staffName) AS staffName, MAX(CASE WHEN eme.mark THEN eme.mark END) AS firstValMark, MAX(CASE WHEN eem.valuationCount = 2 THEN eem.mark END ) AS secondValMark, MAX(CASE WHEN eem.valuationCount = 3 THEN eem.mark END ) AS thirdValMark,sa.studentName FROM exam_answerSheetGroup_student_valuated_staff easvs INNER JOIN exam_examReg_subject_answerSheet_group eesag ON (easvs.examReg_subject_answerSheet_group_id = eesag.id) INNER JOIN studentaccount sa ON (sa.studentID = easvs.studentId) LEFT JOIN staffaccounts staff ON FIND_IN_SET(staff.staffID, easvs.valuatedStaffId) LEFT JOIN exammarks_external eme ON (eme.id = easvs.exammarks_external_id) LEFT JOIN external_exammarks eem ON (eem.exstdntmarkID = easvs.external_exammarks_id ) WHERE eesag.subjectId = '$examValuationRequest->subjectId' AND eesag.semId = '$examValuationRequest->semId' $condition GROUP BY sa.regNo ORDER BY regNo ASC"; |
| $studentMarkDetails = $this->executeQueryForList($sql_marks); |
| |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); |
| } |
| return $studentMarkDetails; |
| } |
| |
| |
| |
| |
| public function getValuatedStudentCountInSubject ( $examRegistrationSubjectRequest ) { |
| $examRegistrationSubjectRequest = $this->realEscapeObject ( $examRegistrationSubjectRequest ); |
| $sql = null; |
| $condition = null; |
| $valuatedStudentCount = null; |
| |
| if ( $examRegistrationSubjectRequest->examType === ExamType::REGULAR ) { |
| $condition = " AND eesag.examRegId = '$examRegistrationSubjectRequest->examRegId' "; |
| } |
| else if ( $examRegistrationSubjectRequest->examType === ExamType::SUPPLY ) { |
| $condition = " AND eesag.supplyExamRegId = '$examRegistrationSubjectRequest->examRegId' "; |
| } |
| try { |
| $sql = "SELECT COUNT(DISTINCT studentId) AS studentCount FROM exam_answerSheetGroup_student_valuated_staff easvs INNER JOIN exam_examReg_subject_answerSheet_group eesag ON ( easvs.examReg_subject_answerSheet_group_id = eesag.id ) WHERE eesag.subjectId = '$examRegistrationSubjectRequest->subjectId' AND eesag.semId = '$examRegistrationSubjectRequest->semId' $condition "; |
| |
| $valuatedStudentCount = $this->executeQueryForObject($sql)->studentCount; |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); |
| } |
| return $valuatedStudentCount; |
| } |
| |
| |
| |
| |
| |
| public function getExternalValuationStudentMarksDirect($examRegistrationSubjectRequest) { |
| $sql = null; |
| $examRegistrationSubjectRequest = $this->realEscapeObject ( $examRegistrationSubjectRequest ); |
| $studentDetails = []; |
| $condition = null; |
| $includeFailedStudents = 0; |
| if ($examRegistrationSubjectRequest->examType == ExamType::SUPPLY ){ |
| $includeFailedStudents = CommonService::getInstance()->getSettings(SettingsConstents::EXAM_CONTROLLER, SettingsConstents::ENABLE_FAILED_BATCH_STUDENT_DATA); |
| } |
| if ( !empty ( $examRegistrationSubjectRequest->batches ) ) { |
| if($includeFailedStudents){ |
| $condition .= " AND IF (fs.previousBatch, fs.previousBatch, sa.batchID) IN (" . implode(',', $examRegistrationSubjectRequest->batches) . ") "; |
| }else{ |
| $condition .= " AND sa.batchID IN (" . implode(',', $examRegistrationSubjectRequest->batches) . ") "; |
| } |
| } |
| $orderByCondition = $examRegistrationSubjectRequest->orderByRegNo ? "ORDER BY sa.rollNo ASC" : "ORDER BY sa.batchID ASC, sa.regNo ASC"; |
| try { |
| if ($examRegistrationSubjectRequest->examType == ExamType::REGULAR ) { |
| $sql = "SELECT DISTINCT sa.studentID,sa.studentName, sa.regNo, sa.rollNo, sa.batchID, e.examID, e.examTotalMarks, ee.mark AS eeMark, eef.mark AS eefMark, ea.isAbsent, IF(ees.id, 1, 0) AS isExempted,e.valuationMaxMark FROM exam_reg_studentsubject ers INNER JOIN studentaccount sa ON (ers.studentID = sa.studentID) INNER JOIN exam e ON (e.subjectID = ers.subjectID AND sa.batchID = e.batchID AND e.examregID = ers.examRegId) INNER JOIN exam_reg_studentchallan ersc ON (ersc.studentID = ers.studentID AND ersc.examregID = ers.examregID AND ersc.paid = 1) LEFT JOIN exammarks_external ee ON (ee.examID = e.examID AND ee.studentID = sa.studentID) LEFT JOIN externalexammarks_finalized eef ON (eef.examID = e.examID AND eef.studentID = sa.studentID) LEFT JOIN exam_attendance ea ON (ea.examID = e.examID AND ea.studentID = sa.studentID) LEFT JOIN exam_exempted_students ees ON (ees.exam_id = e.examID AND ees.studentaccount_id = sa.studentID) WHERE e.semID = '$examRegistrationSubjectRequest->semId' AND ers.examRegId = '$examRegistrationSubjectRequest->examRegId' AND ers.subjectID = '$examRegistrationSubjectRequest->subjectId' $condition $orderByCondition"; |
| } |
| else if ($examRegistrationSubjectRequest->examType == ExamType::SUPPLY ) { |
| if($includeFailedStudents){ |
| $sql = "SELECT DISTINCT sa.studentID,sa.studentName, sa.regNo, sa.rollNo, sa.batchID, e.examID, e.examTotalMarks, ee.mark AS eeMark, eef.mark AS eefMark, ea.isAbsent, IF(ees.id, 1, 0) AS isExempted,e.valuationMaxMark FROM exam e |
| INNER JOIN exam e1 ON (e.subjectID = e1.subjectID AND e.semID = e1.semID AND e.batchID = e1.batchID AND e1.supply_examreg_id IS NULL ) |
| INNER JOIN exam_supplementary_student_subjects esss ON (esss.examID = e1.examID AND esss.exam_supplementary_id = e.supply_examreg_id) |
| LEFT JOIN failed_students fs ON fs.studentID = esss.studentID AND FIND_IN_SET(e.semID, fs.hisSemestersInThisbatch) |
| INNER JOIN studentaccount sa ON (sa.studentID = esss.studentID AND IF (fs.previousBatch, fs.previousBatch, sa.batchID) = e.batchID) |
| LEFT JOIN exammarks_external ee ON (ee.examID = e.examID AND ee.studentID = sa.studentID) |
| LEFT JOIN externalexammarks_finalized eef ON (eef.examID = e.examID AND eef.studentID = sa.studentID) |
| LEFT JOIN exam_attendance ea ON (ea.examID = e.examID AND ea.studentID = sa.studentID) |
| LEFT JOIN exam_exempted_students ees ON (ees.exam_id = e.examID AND ees.studentaccount_id = sa.studentID) |
| WHERE e.supply_examreg_id = '$examRegistrationSubjectRequest->examRegId' AND e.subjectID = '$examRegistrationSubjectRequest->subjectId' $condition $orderByCondition"; |
| }else{ |
| $sql = "SELECT DISTINCT sa.studentID,sa.studentName, sa.regNo, sa.rollNo, sa.batchID, e.examID, e.examTotalMarks, ee.mark AS eeMark, eef.mark AS eefMark, ea.isAbsent, IF(ees.id, 1, 0) AS isExempted,e.valuationMaxMark FROM exam e INNER JOIN exam e1 ON (e.subjectID = e1.subjectID AND e.semID = e1.semID AND e.batchID = e1.batchID AND e1.supply_examreg_id IS NULL ) INNER JOIN exam_supplementary_student_subjects esss ON (esss.examID = e1.examID AND esss.exam_supplementary_id = e.supply_examreg_id) INNER JOIN studentaccount sa ON (sa.studentID = esss.studentID AND sa.batchID = e.batchID) LEFT JOIN exammarks_external ee ON (ee.examID = e.examID AND ee.studentID = sa.studentID) LEFT JOIN externalexammarks_finalized eef ON (eef.examID = e.examID AND eef.studentID = sa.studentID) LEFT JOIN exam_attendance ea ON (ea.examID = e.examID AND ea.studentID = sa.studentID) LEFT JOIN exam_exempted_students ees ON (ees.exam_id = e.examID AND ees.studentaccount_id = sa.studentID) WHERE e.supply_examreg_id = '$examRegistrationSubjectRequest->examRegId' AND e.subjectID = '$examRegistrationSubjectRequest->subjectId' $condition $orderByCondition"; |
| } |
| } |
| $studentDetails = $this->executeQueryForList($sql); |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); |
| } |
| return $studentDetails; |
| } |
| |
| |
| |
| |
| |
| public function saveExamValuationStudentMarksDirect ( $studentMarks ) { |
| $studentMarks = $this->realEscapeArray ( $studentMarks ); |
| $values = []; |
| $exempted = []; |
| $absentees = []; |
| $deleteExempted = []; |
| $sql = null; |
| $updatingStaffName = $_SESSION['adminID'] ? StaffService::getInstance()->getExamControllerDetails($_SESSION['adminID']) : ""; |
| $courseType = CourseTypeService::getInstance()->getCourseTypesById ( $studentMarks["courseType"] ); |
| if ( ($courseType->courseTypeMethod == "PG") || ($courseType->courseTypeMethod == "PGD") || ($courseType->courseTypeMethod == "LIB")|| ($courseType->courseTypeMethod == "MSW") || ($courseType->courseTypeMethod == "MBA") || ($courseType->courseTypeMethod == "PG_BLISC") || ($courseType->courseTypeMethod == "MPHIL") || ($courseType->courseTypeMethod == "MTECH") || ($courseType->courseTypeMethod == "MCA") || ($courseType->courseTypeMethod == "PHD")) { |
| foreach ($studentMarks["markDetails"] as $studentMark ) { |
| $studentMark['mark'] = strtoupper($studentMark['mark']); |
| $studentMark['updatingStaffName'] = $updatingStaffName; |
| $result = MarkService::getInstance()->saveStudentExamMarkLog($studentMark); |
| if ( $studentMark['mark'] == "AB" ) { |
| $absentees[] = " ( '".$studentMark['examId']."', '".$studentMark['studentId']."', '1' ) "; |
| $deleteExempted[] = " ( exam_id = '".$studentMark['examId']."' AND studentaccount_id = '".$studentMark['studentId']."' ) "; |
| if ($studentMarks['deleteMarksOnAb']) { |
| $this->deleteStudentExamMarkForPgAndUg($studentMark['examId'], $studentMark['studentId'], 1); |
| } |
| } |
| else if ( $studentMark['mark'] == "MAL" ) { |
| $absentees[] = " ( '".$studentMark['examId']."', '".$studentMark['studentId']."', '2' ) "; |
| $deleteExempted[] = " ( exam_id = '".$studentMark['examId']."' AND studentaccount_id = '".$studentMark['studentId']."' ) "; |
| if ($studentMarks['deleteMarksOnAb']) { |
| $this->deleteStudentExamMarkForPgAndUg($studentMark['examId'], $studentMark['studentId'], 1); |
| } |
| } |
| else if ($studentMark['mark'] == "WH") { |
| $absentees[] = " ( '" . $studentMark['examId'] . "', '" . $studentMark['studentId'] . "', '3' ) "; |
| $deleteExempted[] = " ( exam_id = '" . $studentMark['examId'] . "' AND studentaccount_id = '" . $studentMark['studentId'] . "' ) "; |
| } |
| |
| else if ($studentMark['mark'] == "I") { |
| $absentees[] = " ( '" . $studentMark['examId'] . "', '" . $studentMark['studentId'] . "', '6' ) "; |
| $deleteExempted[] = " ( exam_id = '" . $studentMark['examId'] . "' AND studentaccount_id = '" . $studentMark['studentId'] . "' ) "; |
| if($studentMarks['deleteMarksOnAb']){ |
| $this->deleteStudentExamMarkForPgAndUg($studentMark['examId'], $studentMark['studentId'], 1); |
| } |
| } |
| else if ( $studentMark['mark'] == "SP" ) { |
| $absentees[] = " ( '".$studentMark['examId']."', '".$studentMark['studentId']."', '5' ) "; |
| $deleteExempted[] = " ( exam_id = '".$studentMark['examId']."' AND studentaccount_id = '".$studentMark['studentId']."' ) "; |
| |
| } |
| |
| else if ( $studentMark['mark'] == "EX" ) { |
| $exempted[] = " ( '".$studentMark['examId']."', '".$studentMark['studentId']."', '".$studentMarks['adminID']."', utc_timestamp(), '".$studentMarks['adminID']."', utc_timestamp() ) "; |
| } else { |
| $values[] = " ( '".$studentMark['examId']."', '".$studentMark['studentId']."', '".$studentMark['mark']."', '".$studentMarks['adminID']."' ) "; |
| $absentees[] = " ( '".$studentMark['examId']."', '".$studentMark['studentId']."', '0' ) "; |
| $deleteExempted[] = " ( exam_id = '".$studentMark['examId']."' AND studentaccount_id = '".$studentMark['studentId']."' ) "; |
| } |
| } |
| if ( !empty ( $values ) ) { |
| $sql = "INSERT INTO externalexammarks_finalized (examID, studentID, mark, created_by) VALUES " . implode ( ",", $values ) . " ON DUPLICATE KEY UPDATE mark = VALUES(mark), updated_by = VALUES(created_by)"; |
| $this->executeQuery($sql); |
| } |
| } |
| else if ( $courseType->courseTypeMethod == "UG" || $courseType->courseTypeMethod == "UG_PRO" || $courseType->courseTypeMethod == "BPED" || $courseType->courseTypeMethod == "DIPLOMA_PG") { |
| foreach ($studentMarks["markDetails"] as $studentMark ) { |
| $studentMark['mark'] = strtoupper($studentMark['mark']); |
| $studentMark['updatingStaffName'] = $updatingStaffName; |
| $result = MarkService::getInstance()->saveStudentExamMarkLog($studentMark); |
| if ( $studentMark['mark'] == "AB" ) { |
| $absentees[] = " ( '".$studentMark['examId']."', '".$studentMark['studentId']."', '1' ) "; |
| $deleteExempted[] = " ( exam_id = '".$studentMark['examId']."' AND studentaccount_id = '".$studentMark['studentId']."' ) "; |
| if ($studentMarks['deleteMarksOnAb']) { |
| $this->deleteStudentExamMarkForPgAndUg($studentMark['examId'], $studentMark['studentId'], 0); |
| } |
| } |
| else if ( $studentMark['mark'] == "MAL" ) { |
| $absentees[] = " ( '".$studentMark['examId']."', '".$studentMark['studentId']."', '2' ) "; |
| $deleteExempted[] = " ( exam_id = '".$studentMark['examId']."' AND studentaccount_id = '".$studentMark['studentId']."' ) "; |
| if ($studentMarks['deleteMarksOnAb']) { |
| $this->deleteStudentExamMarkForPgAndUg($studentMark['examId'], $studentMark['studentId'], 0); |
| } |
| } |
| else if ($studentMark['mark'] == "WH") { |
| $absentees[] = " ( '" . $studentMark['examId'] . "', '" . $studentMark['studentId'] . "', '3' ) "; |
| $deleteExempted[] = " ( exam_id = '" . $studentMark['examId'] . "' AND studentaccount_id = '" . $studentMark['studentId'] . "' ) "; |
| } |
| |
| else if ($studentMark['mark'] == "I") { |
| $absentees[] = " ( '" . $studentMark['examId'] . "', '" . $studentMark['studentId'] . "', '6' ) "; |
| $deleteExempted[] = " ( exam_id = '" . $studentMark['examId'] . "' AND studentaccount_id = '" . $studentMark['studentId'] . "' ) "; |
| if ($studentMarks['deleteMarksOnAb']) { |
| $this->deleteStudentExamMarkForPgAndUg($studentMark['examId'], $studentMark['studentId'], 0); |
| } |
| } |
| |
| else if ($studentMark['mark'] == "SP") { |
| $absentees[] = " ( '" . $studentMark['examId'] . "', '" . $studentMark['studentId'] . "', '5' ) "; |
| $deleteExempted[] = " ( exam_id = '" . $studentMark['examId'] . "' AND studentaccount_id = '" . $studentMark['studentId'] . "' ) "; |
| |
| } |
| else if ( $studentMark['mark'] == "EX" ) { |
| $exempted[] = " ( '".$studentMark['examId']."', '".$studentMark['studentId']."', '".$studentMarks['adminID']."', utc_timestamp(), '".$studentMarks['adminID']."', utc_timestamp() ) "; |
| } else { |
| $values[] = " ( '".$studentMark['examId']."', '".$studentMark['studentId']."', '".$studentMark['mark']."', '".$studentMarks['adminID']."', '".$studentMark['isRevalued']."', '".$studentMarks['adminID']."' ) "; |
| $absentees[] = " ( '".$studentMark['examId']."', '".$studentMark['studentId']."', '0' ) "; |
| $deleteExempted[] = " ( exam_id = '".$studentMark['examId']."' AND studentaccount_id = '".$studentMark['studentId']."' ) "; |
| } |
| } |
| $markEntryBy = "adminID"; |
| if($studentMarks['staffID']){ |
| $markEntryBy ="staffID"; |
| } |
| if ( !empty ( $values ) ) { |
| $sql = "INSERT INTO exammarks_external (examID, studentID, mark, $markEntryBy, isRevalued, created_by) VALUES " . implode ( ",", $values ) . " ON DUPLICATE KEY UPDATE mark = VALUES(mark), isRevalued = VALUES(isRevalued), updated_by = VALUES(created_by)"; |
| $this->executeQuery($sql); |
| } |
| } |
| else { |
| throw new ProfessionalException(ProfessionalException::INVALID_COURSE_CODE,"Course type method not defined"); |
| } |
| try { |
| if ( !empty ( $absentees ) ) { |
| $sql_absent = "INSERT INTO exam_attendance ( examID, studentID, isAbsent ) VALUES " . implode ( ",", $absentees ) . " ON DUPLICATE KEY UPDATE isAbsent = VALUES(isAbsent)"; |
| $this->executeQuery($sql_absent); |
| } |
| if ( !empty ( $exempted ) ) { |
| $sql_exempted = "INSERT IGNORE INTO exam_exempted_students ( exam_id, studentaccount_id, createdBy, createdDate, updatedBy, updatedDate ) VALUES " . implode ( ",", $exempted ); |
| $this->executeQuery($sql_exempted); |
| } |
| if ( !empty ( $deleteExempted ) ) { |
| $sql_deleteExempted = "DELETE FROM exam_exempted_students WHERE " . implode ( " OR ", $deleteExempted); |
| $this->executeQuery($sql_deleteExempted); |
| } |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); |
| } |
| return true; |
| } |
| |
| |
| |
| |
| |
| |
| |
| public function getAllExternalExaminers() { |
| $sql = null; |
| $faculties = []; |
| try { |
| $sql = "SELECT staffID, staffName FROM external_examiners "; |
| $faculties = $this->executeQueryForList($sql); |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); |
| } |
| return $faculties; |
| } |
| |
| |
| |
| |
| |
| public function getExamBatchesForSubject ($examRegistrationSubjectRequest) { |
| $sql = null; |
| $examRegistrationSubjectRequest = $this->realEscapeObject ( $examRegistrationSubjectRequest ); |
| $batchDetails = []; |
| $condition = null; |
| |
| if ( $examRegistrationSubjectRequest->subjectId ) { |
| $condition .= " AND e.subjectID = '$examRegistrationSubjectRequest->subjectId' "; |
| } |
| if ( $examRegistrationSubjectRequest->courseTypeId ) { |
| $condition .= " AND b.courseTypeID = '$examRegistrationSubjectRequest->courseTypeId' "; |
| } |
| try { |
| if ($examRegistrationSubjectRequest->examType == ExamType::REGULAR ) { |
| $sql = "SELECT DISTINCT b.batchID, b.batchName FROM batches b INNER JOIN exam e ON (b.batchID = e.batchID) INNER JOIN exam_registration_batches erb ON (erb.examregID = e.examregID AND e.batchID = erb.batchID AND e.semID = erb.semID) WHERE e.semID = '$examRegistrationSubjectRequest->semId' AND e.examregID = '$examRegistrationSubjectRequest->examRegId' $condition ORDER BY b.batchName ASC"; |
| } |
| else if ($examRegistrationSubjectRequest->examType == ExamType::SUPPLY ) { |
| $sql = "SELECT DISTINCT b.batchID, b.batchName FROM batches b INNER JOIN exam e ON (b.batchID = e.batchID) INNER JOIN supply_improve_batches sib ON (sib.batchID = e.batchID AND sib.exam_supplementary_id = e.supply_examreg_id) WHERE e.semID = '$examRegistrationSubjectRequest->semId' AND e.supply_examreg_id = '$examRegistrationSubjectRequest->examRegId' $condition ORDER BY b.batchName ASC"; |
| } |
| $batchDetails = $this->executeQueryForList($sql); |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); |
| } |
| return $batchDetails; |
| } |
| |
| |
| |
| |
| public function checkWhetherStudentMarkIsAlreadyEnteredInAnotherPacket ( $examValuationStudent ) { |
| $sql = null; |
| $examValuationStudent = $this->realEscapeObject ( $examValuationStudent ); |
| $packetNo = null; |
| |
| if ($examValuationStudent->examType == ExamType::REGULAR ) { |
| $condition = " AND eesag.examRegId = '$examValuationStudent->examRegId' "; |
| } |
| else if ($examValuationStudent->examType == ExamType::SUPPLY ) { |
| $condition = " AND eesag.supplyExamRegId = '$examValuationStudent->examRegId' "; |
| } |
| try { |
| $sql = "SELECT DISTINCT easvs.packetNo FROM exam_answerSheetGroup_student_valuated_staff easvs INNER JOIN exam_examReg_subject_answerSheet_group eesag ON (easvs.examReg_subject_answerSheet_group_id = eesag.id) INNER JOIN studentaccount sa ON (sa.studentID = easvs.studentId) WHERE sa.regNo = '$examValuationStudent->regNo' AND eesag.semId = '$examValuationStudent->semId' AND eesag.subjectId = '$examValuationStudent->subjectId' AND easvs.packetNo != '$examValuationStudent->packetNo' $condition"; |
| |
| $packetNo = $this->executeQueryForObject($sql)->packetNo; |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); |
| } |
| return $packetNo; |
| } |
| |
| |
| |
| |
| |
| public function issuePacketToStaffForValuation ($packetIssueRegister) { |
| $packetIssueRegister = $this->realEscapeObject ($packetIssueRegister); |
| $sql = null; |
| $sqlInsertValues = null; |
| $sqlInsertValueString = null; |
| $adminId = $_SESSION['adminID']; |
| |
| if (!$packetIssueRegister->examRegSubjectAnswerSheetGroupId) { |
| throw new ProfessionalException(ProfessionalException::INVALID_REQUEST,'Answer Sheet Group can not be Null'); |
| } |
| if (!$packetIssueRegister->packets) { |
| throw new ProfessionalException(ProfessionalException::INVALID_REQUEST,'Packet Number can not be Null'); |
| } |
| foreach ($packetIssueRegister->packets as $packet) { |
| $staffId = $packet['staffId']; |
| $packetNum = $packet['packetNum']; |
| $issueDate = date('Y-m-d G:i',strtotime($packet['issueDate'])); |
| if ($staffId && $packetNum && $issueDate) { |
| $sqlInsertValues[] = "(".$packetIssueRegister->examRegSubjectAnswerSheetGroupId.", $staffId, $packetNum, $adminId, '$issueDate', $adminId)"; |
| $staffId = ""; |
| $packetNum = ""; |
| $issueDate = ""; |
| } |
| |
| } |
| |
| try { |
| $sqlInsertValueString = implode(',',$sqlInsertValues); |
| $sql = "INSERT IGNORE INTO ec_packet_issue_register |
| (exam_examReg_subject_answerSheet_group_id, |
| exam_examReg_subject_valuation_staff_id, |
| packet_no, |
| issued_by, |
| issue_date, |
| created_by) |
| VALUES $sqlInsertValueString"; |
| |
| $id = $this->executeQuery($sql,true); |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); |
| } |
| return $id; |
| } |
| |
| |
| |
| |
| |
| public function deleteIssuedPacketToStaffForValuation ($id) { |
| $id = $this->realEscapeString ($id); |
| $sql = null; |
| |
| try { |
| if (!$id) { |
| throw new ProfessionalException(ProfessionalException::INVALID_REQUEST,'Invalid id'); |
| } |
| else{ |
| $sqlIssuedPacketDetails = "SELECT |
| return_to AS returnTo, |
| IF(return_date = '0000-00-00 00:00:00','', DATE_FORMAT(return_date, '%Y-%m-%d %h:%i %p')) AS returnDate |
| FROM |
| ec_packet_issue_register |
| WHERE |
| id = $id"; |
| $issuedPacketDetails = $this->executeQueryForObject($sqlIssuedPacketDetails); |
| } |
| if ($issuedPacketDetails->returnDate && $issuedPacketDetails->returnTo) { |
| throw new ProfessionalException(ProfessionalException::INVALID_REQUEST,'Can not delete returned packets'); |
| } |
| $sql = "DELETE FROM ec_packet_issue_register |
| WHERE |
| id = $id"; |
| |
| $this->executeQuery($sql); |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); |
| } |
| } |
| |
| |
| |
| |
| |
| public function searchPacketsDetails ($packetIssueRegisterRequest) { |
| $packetIssueRegisterRequest = $this->realEscapeObject ($packetIssueRegisterRequest); |
| $sql = NULL; |
| $sqlCondition = NULL; |
| $sqlColumns = NULL; |
| $customMapper = NULL; |
| $sqlGroupBy = NULL; |
| $sqlLimit = NULL; |
| |
| try { |
| if (!$packetIssueRegisterRequest->examType) { |
| throw new ProfessionalException(ProfessionalException::INVALID_REQUEST,'Exam Type can not be Null!'); |
| } |
| else { |
| $examTableExamRegColumn = ""; |
| if ($packetIssueRegisterRequest->examType == ExamType::REGULAR) { |
| $examTableExamRegColumn = "examregID"; |
| $remainingTableExamRegColumn = "examRegId"; |
| $examRegTable = "exam_registration"; |
| $examRegTableExamRegId = "examregID"; |
| $examRegTableExamName = "examregName"; |
| $examRegTableExamDesc = "examregDesc"; |
| $sqlColumns .= ",'".ExamType::REGULAR."' AS examType"; |
| } |
| else if ($packetIssueRegisterRequest->examType == ExamType::SUPPLY) { |
| $examTableExamRegColumn = "supply_examreg_id"; |
| $remainingTableExamRegColumn = "supplyExamRegId"; |
| $examRegTable = "exam_supplementary"; |
| $examRegTableExamRegId = "id"; |
| $examRegTableExamName = "supplyDesc"; |
| $examRegTableExamDesc = "supplyDesc"; |
| $sqlColumns .= ",'".ExamType::SUPPLY."' AS examType"; |
| } |
| } |
| if ($packetIssueRegisterRequest->examRegId) { |
| $sqlCondition .= " AND asgc.$remainingTableExamRegColumn IN ($packetIssueRegisterRequest->examRegId)"; |
| } |
| if ($packetIssueRegisterRequest->semId) { |
| $sqlCondition .= " AND asgc.semId IN ($packetIssueRegisterRequest->semId)"; |
| } |
| if ($packetIssueRegisterRequest->subjectIds) { |
| $subjectIdString = implode(',',$packetIssueRegisterRequest->subjectIds) ? implode(',',$packetIssueRegisterRequest->subjectIds) : $packetIssueRegisterRequest->subjectIds; |
| $sqlCondition .= " AND asgc.subjectId IN ($subjectIdString)"; |
| } |
| if ($packetIssueRegisterRequest->subjectCategorySelected) { |
| $subjectCategorySelectedString = implode(',',$packetIssueRegisterRequest->subjectCategorySelected) ? implode(',',$packetIssueRegisterRequest->subjectCategorySelected) : $packetIssueRegisterRequest->subjectCategorySelected; |
| $sqlCondition .= " AND s.subjectcatID IN ($subjectCategorySelectedString)"; |
| } |
| if ($packetIssueRegisterRequest->fromDate && $packetIssueRegisterRequest->toDate) { |
| $sqlCondition .= " AND e.examDate BETWEEN '$packetIssueRegisterRequest->fromDate' AND '$packetIssueRegisterRequest->toDate'"; |
| } |
| else if($packetIssueRegisterRequest->fromDate){ |
| $sqlCondition .= " AND e.examDate >= '$packetIssueRegisterRequest->fromDate'"; |
| } |
| else if($packetIssueRegisterRequest->toDate){ |
| $sqlCondition .= " AND e.examDate <= '$packetIssueRegisterRequest->toDate'"; |
| } |
| if ($packetIssueRegisterRequest->includePacketDetails) { |
| $sqlColumns .= ", sa.staffID, |
| sa.staffName, |
| pir.id AS packetId, |
| pir.packet_no, |
| pir.issued_by, |
| pir.return_to, |
| IF(pir.issue_date IS NULL,'', DATE_FORMAT(pir.issue_date, '%d-%m-%Y %h:%i %p') AS issue_date, |
| IF(pir.return_date = '0000-00-00 00:00:00','', DATE_FORMAT(pir.return_date, '%d-%m-%Y %h:%i %p')) AS return_date"; |
| $customMapper = ExamValuationRuleServiceMapper::GET_ISSUED_PACKET_DETAILS; |
| $sqlGroupBy = ""; |
| } |
| else { |
| $sqlColumns .= ", asgc.id AS configId, |
| asgc.studentsPerAnswerSheetGroupCount AS packetCount, |
| COUNT(CASE |
| WHEN pir.packet_no IS NOT NULL THEN 1 |
| END)/COUNT(DISTINCT e.batchID) AS issuedCount, |
| asgc.studentsPerAnswerSheetGroupCount - (COUNT(CASE |
| WHEN pir.id IS NOT NULL THEN 1 |
| END)/COUNT(DISTINCT e.batchID)) AS notIssuedCount"; |
| $customMapper = ExamValuationRuleServiceMapper::GET_ISSUED_PACKET_COUNT; |
| $sqlGroupBy = " GROUP BY s.subjectID,asgc.id"; |
| } |
| |
| if ($packetIssueRegisterRequest->startIndex !== "" && $packetIssueRegisterRequest->endIndex !== "") { |
| $sqlLimit = " LIMIT $packetIssueRegisterRequest->startIndex, $packetIssueRegisterRequest->endIndex"; |
| } |
| |
| $sqlJoinedTable = "FROM |
| exam_examReg_subject_answerSheet_group ersasg |
| INNER JOIN |
| exam_examReg_subject_answerSheet_group_config asgc ON asgc.id = ersasg.examReg_subject_answerSheet_group_config_id |
| AND asgc.$remainingTableExamRegColumn = ersasg.$remainingTableExamRegColumn |
| AND asgc.semId = ersasg.semId |
| AND asgc.subjectId = ersasg.subjectId |
| INNER JOIN |
| exam e ON e.$examTableExamRegColumn = asgc.$remainingTableExamRegColumn |
| AND e.$examTableExamRegColumn = asgc.$remainingTableExamRegColumn |
| AND e.$examTableExamRegColumn = ersasg.$remainingTableExamRegColumn |
| AND e.semID = asgc.semId |
| AND e.semID = ersasg.semId |
| AND e.subjectID = asgc.subjectId |
| AND e.subjectID = ersasg.subjectId |
| INNER JOIN |
| $examRegTable er ON er.$examRegTableExamRegId = e.$examTableExamRegColumn |
| AND er.$examRegTableExamRegId = asgc.$remainingTableExamRegColumn |
| AND er.$examRegTableExamRegId = ersasg.$remainingTableExamRegColumn |
| INNER JOIN |
| subjects s ON s.subjectID = ersasg.subjectId |
| INNER JOIN |
| batches b ON b.batchID = e.batchID |
| LEFT JOIN |
| exam_examReg_subject_valuation_staff ersvs ON ersasg.subjectId = ersvs.subjectId |
| AND ersasg.semId = ersvs.semId |
| AND ersasg.$remainingTableExamRegColumn = ersvs.$remainingTableExamRegColumn |
| AND e.semID = ersvs.semId |
| AND e.subjectID = ersvs.subjectId |
| AND er.$examRegTableExamRegId = ersvs.$remainingTableExamRegColumn |
| AND s.subjectID = ersvs.subjectId |
| LEFT JOIN |
| staffaccounts sa ON sa.staffID = ersvs.staffId |
| LEFT JOIN |
| ec_packet_issue_register pir ON pir.exam_examReg_subject_valuation_staff_id = ersvs.id |
| AND pir.exam_examReg_subject_answerSheet_group_id = ersasg.id |
| WHERE |
| e.examID > 0 |
| $sqlCondition |
| $sqlGroupBy |
| ORDER BY e.examDate DESC, s.subjectDesc ASC"; |
| |
| $sql = "SELECT |
| s.subjectID, |
| s.subjectName, |
| s.subjectDesc, |
| s.syllabusName, |
| DATE_FORMAT(e.examDate, '%d-%m-%Y') AS examDate, |
| e.examStartTime, |
| e.examEndTime, |
| e.examTypeID, |
| asgc.semId AS semID, |
| b.courseTypeID, |
| er.$examRegTableExamRegId AS examRegId, |
| er.$examRegTableExamName AS examRegName, |
| er.$examRegTableExamDesc AS examregDesc |
| $sqlColumns |
| $sqlJoinedTable |
| $sqlLimit"; |
| $issuedPacketDetailRecords = $this->executeQueryForList($sql,$this->mapper[$customMapper]); |
| |
| $countSql = "SELECT |
| COUNT(DISTINCT s.subjectID) AS totalCount |
| $sqlJoinedTable"; |
| $totalCount = $this->executeQueryForObject($countSql)->totalCount; |
| |
| $issuedPacketDetails = new \stdClass(); |
| $issuedPacketDetails->issuedPacketDetailRecords = $issuedPacketDetailRecords; |
| $issuedPacketDetails->issuedPacketDetailTotalRecords = $totalCount; |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); |
| } |
| return $issuedPacketDetails; |
| } |
| |
| public function getSubjectWisePacketDetails ( $examRegistrationSubjectRequest ) { |
| $condition = null; |
| $subjectWisePacketDetails = null; |
| if ( $examRegistrationSubjectRequest->examType === ExamType::REGULAR ) { |
| $condition = " AND ersvs.examRegId = '$examRegistrationSubjectRequest->examRegId' "; |
| } |
| else if ( $examRegistrationSubjectRequest->examType === ExamType::SUPPLY ) { |
| $condition = " AND ersvs.supplyExamRegId = '$examRegistrationSubjectRequest->examRegId' "; |
| } |
| |
| $sql = "SELECT |
| pir.id, |
| sa.staffId, |
| sa.staffName, |
| ersvs.id AS subjectValuationStaffId, |
| ersasg.id AS subjectAnswerSheetGroupId, |
| asgc.id AS subjectAnswerSheetGroupConfigId, |
| pir.packet_no AS packetNum, |
| DATE_FORMAT(pir.issue_date, '%d-%m-%Y %h:%i %p') AS issueDate, |
| pir.issued_by AS issuedBy, |
| IF(pir.return_date = '0000-00-00 00:00:00','', DATE_FORMAT(pir.return_date, '%d-%m-%Y %h:%i %p')) AS returnDate, |
| IF(pir.return_date = '0000-00-00 00:00:00','',1) AS dataFromDb |
| FROM |
| exam_examReg_subject_valuation_staff ersvs |
| INNER JOIN |
| exam_examReg_subject_answerSheet_group ersasg ON ersasg.subjectId = ersvs.subjectId |
| INNER JOIN |
| exam_examReg_subject_answerSheet_group_config asgc ON asgc.id = ersasg.examReg_subject_answerSheet_group_config_id |
| INNER JOIN |
| staffaccounts sa ON sa.staffID = ersvs.staffId |
| INNER JOIN |
| ec_packet_issue_register pir ON pir.exam_examReg_subject_valuation_staff_id = ersvs.id |
| AND pir.exam_examReg_subject_answerSheet_group_id = ersasg.id |
| WHERE |
| ersvs.semId = '$examRegistrationSubjectRequest->semId' AND |
| ersvs.subjectId = '$examRegistrationSubjectRequest->subjectId' |
| $condition |
| ORDER BY pir.packet_no"; |
| try { |
| $subjectWisePacketDetails = $this->executeQueryForList($sql); |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); |
| } |
| return $subjectWisePacketDetails; |
| } |
| |
| public function getSubjectValuationStaffAndGroupDetails ( $examRegistrationSubjectRequest ) { |
| $condition = null; |
| $subjectWisePacketDetails = null; |
| if ( $examRegistrationSubjectRequest->examType === ExamType::REGULAR ) { |
| $condition = " AND ersvs.examRegId = '$examRegistrationSubjectRequest->examRegId' "; |
| } |
| else if ( $examRegistrationSubjectRequest->examType === ExamType::SUPPLY ) { |
| $condition = " AND ersvs.supplyExamRegId = '$examRegistrationSubjectRequest->examRegId' "; |
| } |
| |
| $sql = "SELECT |
| sa.staffId, |
| sa.staffName, |
| ersvs.id AS subjectValuationStaffId, |
| ersasg.id AS subjectAnswerSheetGroupId, |
| asgc.id AS subjectAnswerSheetGroupConfigId |
| FROM |
| exam_examReg_subject_valuation_staff ersvs |
| INNER JOIN |
| exam_examReg_subject_answerSheet_group ersasg ON ersasg.subjectId = ersvs.subjectId |
| INNER JOIN |
| exam_examReg_subject_answerSheet_group_config asgc ON asgc.id = ersasg.examReg_subject_answerSheet_group_config_id |
| INNER JOIN |
| staffaccounts sa ON sa.staffID = ersvs.staffId |
| WHERE |
| ersvs.semId = '$examRegistrationSubjectRequest->semId' AND |
| ersvs.subjectId = '$examRegistrationSubjectRequest->subjectId' |
| $condition "; |
| try { |
| $subjectWisePacketDetails = $this->executeQueryForList($sql); |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); |
| } |
| return $subjectWisePacketDetails; |
| } |
| |
| |
| |
| |
| |
| public function updateIssuedPacketToStaffForValuation ($request) { |
| $request = $this->realEscapeArray($request); |
| $sql = null; |
| $adminId = $_SESSION['adminID']; |
| |
| try { |
| if (!$request) { |
| throw new ProfessionalException(ProfessionalException::INVALID_REQUEST,'Invalid entry'); |
| } |
| else{ |
| $returnDate = date('Y-m-d G:i',strtotime($request['returnDate'])); |
| $id = $request['id']; |
| $sql = "UPDATE ec_packet_issue_register |
| SET return_date = '$returnDate', updated_by = $adminId, return_to = $adminId |
| WHERE |
| id = $id"; |
| } |
| $this->executeQuery($sql); |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); |
| } |
| } |
| |
| |
| |
| |
| |
| |
| |
| |
| public function getValuatedStudentCountInPacket ( $examRegistrationSubjectRequest ) { |
| $examRegistrationSubjectRequest = $this->realEscapeObject ( $examRegistrationSubjectRequest ); |
| $sql = null; |
| $condition = null; |
| $valuatedStudentCount = null; |
| |
| if ( $examRegistrationSubjectRequest->examType === ExamType::REGULAR ) { |
| $condition = " AND eesag.examRegId = '$examRegistrationSubjectRequest->examRegId' "; |
| } |
| else if ( $examRegistrationSubjectRequest->examType === ExamType::SUPPLY ) { |
| $condition = " AND eesag.supplyExamRegId = '$examRegistrationSubjectRequest->examRegId' "; |
| } |
| try { |
| $sql = "SELECT COUNT(DISTINCT studentId) AS studentCount FROM exam_answerSheetGroup_student_valuated_staff easvs INNER JOIN exam_examReg_subject_answerSheet_group eesag ON ( easvs.examReg_subject_answerSheet_group_id = eesag.id ) WHERE eesag.subjectId = '$examRegistrationSubjectRequest->subjectId' AND eesag.semId = '$examRegistrationSubjectRequest->semId' AND easvs.packetNo = '$examRegistrationSubjectRequest->packetNo' $condition "; |
| |
| $valuatedStudentCount = $this->executeQueryForObject($sql)->studentCount; |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); |
| } |
| return $valuatedStudentCount; |
| } |
| |
| |
| |
| |
| |
| public function getThirdValuationMarkDifference($courseTypeId) |
| { |
| $courseTypeId = $this->realEscapeString($courseTypeId); |
| $sql = "SELECT markdfID, markdiff from externalexam_thirdvalmarkdiff where courseTypeID='$courseTypeId'"; |
| try { |
| $result = $this->executeQueryForObject($sql); |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); |
| } |
| return $result; |
| } |
| |
| |
| |
| |
| public function saveThirdValStudents($request) |
| { |
| $request = $this->realEscapeObject($request); |
| $result = null; |
| $sql = "INSERT INTO externalexam_thirdvalstudents (examID, studentID,revaluationFlag) |
| VALUES('$request->examID','$request->studentID','0') |
| ON DUPLICATE KEY UPDATE |
| studentID = VALUES(studentID)"; |
| try { |
| $this->executeQueryForObject($sql); |
| $result = true; |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); |
| } |
| return $result; |
| } |
| |
| |
| |
| |
| |
| public function createExamValuationPackets($packet) |
| { |
| $packet = $this->realEscapeObject($packet); |
| $examRegField = $packet->isSupply ? "supplyRegId" : "examRegId"; |
| $sql = "INSERT INTO examValuationPackets($examRegField,prefix,startNumber,endNumber) VALUES('$packet->examRegId','$packet->prefix','$packet->start','$packet->end') ON DUPLICATE KEY UPDATE |
| prefix = VALUES(prefix), |
| startNumber = VALUES(startNumber), |
| endNumber = VALUES(endNumber)"; |
| try { |
| if ($packet->examRegId && $packet->prefix && $packet->start && $packet->end) { |
| $this->executeQueryForObject($sql); |
| } |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); |
| } |
| return true; |
| } |
| |
| |
| |
| |
| |
| public function getExamValuationPackets($request) |
| { |
| $request = $this->realEscapeObject($request); |
| $packet = null; |
| $examRegField = $request->isSupply ? "supplyRegId" : "examRegId"; |
| $sql = "SELECT prefix,startNumber as start,endNumber as end FROM examValuationPackets WHERE $examRegField = '$request->examRegId'"; |
| try { |
| $packet = $this->executeQueryForObject($sql); |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); |
| } |
| return $packet; |
| } |
| |
| |
| |
| public function getExamsForExamValuationInternalStaffAssign($request) |
| { |
| $request = $this->realEscapeObject($request); |
| $examList = null; |
| $condition = $request->examIds ? " and t1.examID IN ($request->examIds) " :""; |
| |
| if($request->examRegId){ |
| $sql="SELECT t1.examID, t1.examName, t1.subjectID, t1.examTotalMarks, t1.examStartTime, t1.examEndTime, t1.examDate, t1.semID, t1.examTypeID, t2.subjectName, t2.subjectDesc, t3.typeName, t3.typeDesc, t4.batchName, t4.batchDesc, t1.batchID from exam t1, subjects t2, exam_type t3, batches t4, exam_registration_batches t5 where t1.examID not in (select exam_id from assignstaff_exam_group_relation where exam_registration_id = $request->examRegId) and t1.examDate=\"$request->examDate\" and t2.subjectID = t1.subjectID and t3.typeID = t1.examTypeID and t4.batchID = t1.batchID and t1.examregID=t5.examregID and t5.examregID=\"$request->examRegId\" and t1.batchID=t5.batchID and t1.semID=t5.semID $condition order by t1.examDate asc"; |
| }else{ |
| $sql="SELECT t1.examID, t1.examName, t1.subjectID, t1.examTotalMarks, t1.examStartTime, t1.examEndTime, t1.examDate, t1.semID, t1.examTypeID, t2.subjectName, t2.subjectDesc, t3.typeName, t3.typeDesc, t4.batchName, t4.batchDesc, t1.batchID from exam t1, subjects t2, exam_type t3, batches t4 where t1.examID not in (select exam_id from assignstaff_exam_group_relation) and t1.examDate=\"$request->examDate\" and t2.subjectID = t1.subjectID and t3.typeID = t1.examTypeID and t4.batchID = t1.batchID $condition and t1.examregID IS NOT NULL order by t1.examDate asc"; |
| } |
| try { |
| $examList = $this->executeQueryForList($sql); |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); |
| } |
| return $examList; |
| } |
| |
| |
| |
| |
| |
| |
| public function getStudentCountByExamIds($examIds) |
| { |
| $examIds = $this->realEscapeString($examIds); |
| $result = null; |
| $sql = "SELECT count(studentID) as studentCount from exam_attendance where isAbsent=0 and examID IN($examIds)"; |
| try { |
| $result = $this->executeQueryForObject($sql); |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); |
| } |
| return $result; |
| } |
| |
| |
| |
| |
| |
| public function getExamValuationStaffsAssignedByExams($examIds) |
| { |
| $examIds = $this->realEscapeString($examIds); |
| $result = null; |
| $sql = "SELECT staffIDs FROM exam_valuation_staffs WHERE examID IN ($examIds)"; |
| try { |
| $result = $this->executeQueryForList($sql); |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); |
| } |
| return $result; |
| } |
| |
| |
| |
| |
| |
| |
| public function getStudentExamSubjectsByExamRegistration($request) |
| { |
| $request = $this->realEscapeObject($request); |
| $result = null; |
| $examMarkTable = " externalexammarks_finalized "; |
| if($request->courseType == CourseTypeConstants::UG || $request->courseType == CourseTypeConstants::UG_PRO || $request->courseType == CourseTypeConstants::BPED){ |
| $examMarkTable = " exammarks_external "; |
| } |
| $sql = "SELECT e.examID,e.semID,e.batchID,ess.studentID,ess.subjectID,s.subjectName,s.subjectDesc,im.internalMarks as internalMark,ims.maxInternalMarks as internalMaxMark,ee.mark as externalMark,e.examTotalMarks as externalMaxMark,smb.oldMark,smb.exam_revaluation_mark |
| FROM exam_reg_studentsubject ess |
| INNER JOIN studentaccount sa ON sa.studentID = ess.studentID |
| INNER JOIN subjects s ON s.subjectID = ess.subjectID |
| INNER JOIN exam e ON e.subjectID = ess.subjectID AND e.examregID = ess.examregID AND e.batchID = sa.batchID |
| LEFT JOIN internal_marks im ON im.studentID= ess.studentID AND im.batchID = e.batchID AND im.semID = e.semID AND im.subjectID = e.subjectID |
| LEFT JOIN internal_marks_settings ims ON ims.batchID = e.batchID AND ims.semID = e.semID AND ims.subjectID = e.subjectID |
| INNER JOIN $examMarkTable ee ON ee.examID = e.examID AND ee.studentID = ess.studentID |
| LEFT JOIN student_mark_before_moderation smb ON smb.studentID = ess.studentID AND smb.examID = e.examID |
| WHERE ess.studentID = '$request->studentId' and ess.examregID = '$request->examRegId' ORDER BY s.subjectID"; |
| try { |
| $result = $this->executeQueryForList($sql); |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); |
| } |
| return $result; |
| } |
| |
| |
| |
| |
| |
| public function saveExamvaluationStudentPackets($request) |
| { |
| $request = $this->realEscapeObject($request); |
| $insertValues=[]; |
| $insertStr = ""; |
| foreach($request->studentList as $student){ |
| $student = (object) $student; |
| if($student->scannedFalseNumber){ |
| $studentDetails = new \stdClass(); |
| $studentDetails = FalseNumberService::getInstance()->getFalseNumberDetails($student->scannedFalseNumber); |
| $insertValues[] ="('$request->packetNo','$request->hallId','$studentDetails->examId','$studentDetails->studentId')"; |
| unset($studentDetails); |
| } |
| } |
| if(!empty($insertValues)){ |
| $insertStr = implode(",", $insertValues); |
| $sql = "INSERT INTO examValuationStudentPacketsRelation(packetNo,hallId,examId,studentId) |
| VALUES $insertStr ON DUPLICATE KEY UPDATE |
| packetNo = VALUES(packetNo), |
| hallId = VALUES(hallId)"; |
| } |
| try { |
| $this->executeQueryForObject($sql); |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); |
| } |
| return true; |
| } |
| |
| |
| |
| public function getStudentSupplyExamSubjectsByExamRegistration($request) |
| { |
| $request = $this->realEscapeObject($request); |
| $result = null; |
| $examMarkTable = " externalexammarks_finalized "; |
| if($request->courseType == CourseTypeConstants::UG || $request->courseType == CourseTypeConstants::UG_PRO || $request->courseType == CourseTypeConstants::BPED){ |
| $examMarkTable = " exammarks_external "; |
| } |
| $sql = "SELECT e.examregID, |
| e.examID as regularExamId, |
| ex.examID, |
| e.semID, |
| e.batchID, |
| ess.studentID, |
| sa.regNo, |
| s.subjectID, |
| s.subjectName, |
| s.subjectDesc, |
| im.internalMarks as internalMark, |
| ims.maxInternalMarks as internalMaxMark, |
| ee.mark as externalMark, |
| e.examTotalMarks as externalMaxMark, |
| smb.oldMark, |
| smb.exam_revaluation_mark |
| FROM exam_supplementary_student_subjects ess |
| INNER JOIN studentaccount sa ON sa.studentID = ess.studentID |
| INNER JOIN exam e ON e.examID = ess.examID |
| INNER JOIN subjects s ON s.subjectID = e.subjectID |
| LEFT JOIN internal_marks im ON im.studentID= ess.studentID AND im.batchID = e.batchID AND im.semID = e.semID AND im.subjectID = e.subjectID |
| LEFT JOIN internal_marks_settings ims ON ims.batchID = e.batchID AND ims.semID = e.semID AND ims.subjectID = e.subjectID |
| INNER JOIN exam ex ON ex.batchID = e.batchID AND ex.semID = e.semID AND ex.subjectID = e.subjectID AND ex.supply_examreg_id = ess.exam_supplementary_id |
| INNER JOIN $examMarkTable ee ON ee.examID = ex.examID AND ee.studentID = ess.studentID |
| LEFT JOIN student_mark_before_moderation smb ON smb.studentID = ess.studentID AND smb.examID = ex.examID |
| WHERE ess.studentID = '$request->studentId' and ess.exam_supplementary_id = '$request->examRegId' ORDER BY s.subjectID"; |
| try { |
| $result = $this->executeQueryForList($sql); |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); |
| } |
| return $result; |
| } |
| |
| |
| |
| |
| |
| public function saveStudentModerationMarkDetails($request) |
| { |
| $request = $this->realEscapeObject($request); |
| $result = null; |
| $insertOldMarks=[]; |
| $insertOldMarksStr = ""; |
| $examMarkTable = " externalexammarks_finalized "; |
| if ($request->courseType == CourseTypeConstants::UG || $request->courseType == CourseTypeConstants::UG_PRO || $request->courseType == CourseTypeConstants::BPED) { |
| $examMarkTable = " exammarks_external "; |
| } |
| foreach($request->subjectList as $subject){ |
| $subject = (object) $subject; |
| if($subject->moderationMark && is_numeric($subject->moderationMark)){ |
| if($subject->revaluationMark && is_numeric($subject->revaluationMark )){ |
| $subject->externalMark = "0"; |
| } |
| else{ |
| $subject->revaluationMark = "NULL"; |
| } |
| $insertOldMarks[] = "($subject->studentID,$subject->examID,$subject->externalMark,$subject->revaluationMark )"; |
| if($subject->revaluationMark && is_numeric($subject->revaluationMark )){ |
| $subject->newMark = $subject->revaluationMark + $subject->moderationMark; |
| $updateSql = "UPDATE revaluation_marks_finalized set mark='$subject->newMark' WHERE studentID= '$subject->studentID' AND examID= '$subject->examID'"; |
| } |
| else{ |
| $subject->newMark = $subject->externalMark + $subject->moderationMark; |
| $updateSql = "UPDATE $examMarkTable set mark='$subject->newMark' WHERE studentID= '$subject->studentID' AND examID= '$subject->examID'"; |
| } |
| $this->executeQueryForObject($updateSql); |
| } |
| } |
| $insertOldMarksStr = implode(",", $insertOldMarks); |
| $sql = "INSERT INTO student_mark_before_moderation (studentID,examID,oldMark,exam_revaluation_mark) VALUES $insertOldMarksStr"; |
| try { |
| if(!empty($insertOldMarks)){ |
| $result = $this->executeQueryForObject($sql); |
| } |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); |
| } |
| return $result; |
| } |
| |
| |
| |
| |
| |
| |
| public function getModerationMarkStudentDetails($request) |
| { |
| $request = $this->realEscapeObject($request); |
| $request->isSupply = (int)$request->isSupply; |
| $result = null; |
| $examMarkTable = " externalexammarks_finalized "; |
| if ($request->courseType == CourseTypeConstants::UG || $request->courseType == CourseTypeConstants::UG_PRO || $request->courseType == CourseTypeConstants::BPED) { |
| $examMarkTable = " exammarks_external "; |
| } |
| $condition = $request->isSupply ? " e.supply_examreg_id " : " e.examregID "; |
| |
| $sql = "SELECT smb.studentID,sa.studentName,sa.regNo,e.subjectID,s.subjectName,s.subjectDesc,e.batchID,b.batchName,im.internalMarks as internalMark,ee.mark as externalMark,smb.oldMark,(ee.mark - smb.oldMark) as moderationMark,ROUND((im.internalMarks + ee.mark),2) as totalMark,e.examID as examId from student_mark_before_moderation smb |
| INNER JOIN studentaccount sa ON sa.studentID = smb.studentID |
| INNER JOIN exam e ON e.examID = smb.examID |
| INNER JOIN subjects s ON s.subjectID = e.subjectID |
| INNER JOIN batches b ON b.batchID = e.batchID |
| INNER JOIN internal_marks im ON im.batchID = e.batchID AND im.semID = e.semID AND im.subjectID = e.subjectID AND im.studentID = smb.studentID |
| INNER JOIN $examMarkTable ee ON ee.examID = smb.examID AND ee.studentID = smb.studentID |
| WHERE $condition = '$request->examRegId' ORDER BY sa.regNo"; |
| try { |
| $result = $this->executeQueryForList($sql); |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); |
| } |
| return $result; |
| } |
| |
| |
| |
| |
| |
| public function getExamValuationAssignedPackets($request) |
| { |
| $request = $this->realEscapeObject($request); |
| $packets = null; |
| $examRegField = $request->isSupply ? "supply_examreg_id" : "examregID"; |
| $sql = "SELECT distinct evpr.packetNo from examValuationStudentPacketsRelation evpr |
| INNER JOIN exam e ON e.examID = evpr.examId |
| WHERE e.$examRegField = '$request->examRegId' AND e.subjectID NOT IN ($request->subjectId)"; |
| try { |
| $packets = $this->executeQueryForList($sql); |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); |
| } |
| return $packets; |
| } |
| |
| |
| |
| |
| |
| public function deleteExamvaluationStudentPacket($request) |
| { |
| $request = $this->realEscapeObject($request); |
| $studentDetails = FalseNumberService::getInstance()->getFalseNumberDetails($request->falseNumber); |
| if (!empty($studentDetails)) { |
| $sql = "DELETE FROM examValuationStudentPacketsRelation WHERE packetNo='$request->packetNo' AND hallId = '$request->hallId' AND examId = '$studentDetails->examId' AND studentId ='$studentDetails->studentId'"; |
| } |
| try { |
| $this->executeQueryForObject($sql); |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); |
| } |
| return true; |
| } |
| |
| |
| |
| |
| |
| |
| public function assignFacultyToValuationPackets($request) |
| { |
| $request = $this->realEscapeObject($request); |
| $assignDetails = $request->assignDetails; |
| $createdBy = $_SESSION['adminID']; |
| $examRegFiled = $request->isSupply ? "supplyRegId" : "examRegId"; |
| $insertValues = []; |
| if($request->valCount == 1 && $request->additionalInfo->showChief){ |
| foreach($assignDetails->packetSelected as $packetSelected){ |
| $insertValues [] = "('$request->examRegId','$request->subjectId','$request->valCount','$packetSelected','$assignDetails->chiefSelected','$assignDetails->additionalSelected','$createdBy')"; |
| } |
| $sql = "INSERT INTO examValuationFacultyPackets($examRegFiled,subjectId,valuationCount,packetNo,chiefEvaluator,additionalEvaluator,created_by) |
| VALUES ".implode(",", $insertValues); |
| }else{ |
| foreach ($assignDetails->packetSelected as $packetSelected) { |
| $insertValues[] = "('$request->examRegId','$request->subjectId','$request->valCount','$packetSelected','$assignDetails->additionalSelected','$createdBy')"; |
| } |
| $sql = "INSERT INTO examValuationFacultyPackets($examRegFiled,subjectId,valuationCount,packetNo,additionalEvaluator,created_by) |
| VALUES " . implode(",", $insertValues);; |
| } |
| |
| try { |
| $this->executeQueryForObject($sql); |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); |
| } |
| return true; |
| } |
| |
| |
| |
| |
| |
| public function getAssignedFacultyToValuationPackets($request) |
| { |
| $request = $this->realEscapeObject($request); |
| $examRegFiled = $request->isSupply ? "supplyRegId" : "examRegId"; |
| $conditions =""; |
| if($request->subjectId){ |
| $conditions .= " AND evf.subjectId = '$request->subjectId' "; |
| } |
| if($request->getPacketsAssignedForValCount){ |
| $conditions .= " AND evf.valuationCount = '$request->valCount' "; |
| } |
| $packetList =[]; |
| $sql = "SELECT evf.valuationCount,evf.packetNo,evf.chiefEvaluator as chiefEvaluatorId,sa.staffName as chiefEvaluator,evf.additionalEvaluator as additionalEvaluatorId,saa.staffName as additionalEvaluator from examValuationFacultyPackets evf |
| LEFT JOIN staffaccounts sa ON sa.staffID = evf.chiefEvaluator |
| LEFT JOIN staffaccounts saa ON saa.staffID = evf.additionalEvaluator |
| WHERE evf.$examRegFiled = '$request->examRegId' |
| $conditions |
| ORDER BY evf.valuationCount,evf.packetNo"; |
| |
| try { |
| $packetList = $this->executeQueryForList($sql); |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); |
| } |
| return $packetList; |
| } |
| |
| |
| |
| |
| |
| public function deleteAssignedFacultyToValuationPackets($request) |
| { |
| $request = $this->realEscapeObject($request); |
| $facultyDetails = $request->facultyDetails; |
| $examRegFiled = $request->isSupply ? "supplyRegId" : "examRegId"; |
| $sql = "DELETE from examValuationFacultyPackets |
| WHERE $examRegFiled = '$request->examRegId' AND subjectId = '$request->subjectId' AND valuationCount='$facultyDetails->valuationCount' AND packetNo ='$facultyDetails->packetNo' AND additionalEvaluator ='$facultyDetails->additionalEvaluatorId'"; |
| |
| try { |
| $this->executeQueryForObject($sql); |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); |
| } |
| return true; |
| } |
| |
| |
| |
| |
| |
| public function getAssignedValuationPacketsBySubject($request) |
| { |
| $request = $this->realEscapeObject($request); |
| $examRegFiled = $request->isSupply ? "supply_examreg_id" : "examregID"; |
| $conditions = ""; |
| if ($request->subjectId) { |
| $conditions .= " AND e.subjectId = '$request->subjectId' "; |
| } |
| $packetList = []; |
| $sql = "SELECT distinct evpr.packetNo as id,evpr.packetNo as name from examValuationStudentPacketsRelation evpr |
| INNER JOIN exam e ON e.examID = evpr.examId |
| WHERE e.$examRegFiled = '$request->examRegId' |
| $conditions |
| ORDER BY evpr.packetNo"; |
| |
| try { |
| $packetList = $this->executeQueryForList($sql); |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); |
| } |
| return $packetList; |
| } |
| |
| |
| |
| |
| |
| |
| public function getExamValuationSubjectsByStaff($request) |
| { |
| $request = $this->realEscapeObject($request); |
| $examRegFiled = $request->isSupply ? "supplyRegId" : "examRegId"; |
| $conditions = ""; |
| if($request->subjectId){ |
| $conditions .= " AND evfp.subjectId = $request->subjectId "; |
| } |
| if ($request->valuationCount) { |
| $conditions .= " AND evfp.valuationCount = $request->valuationCount "; |
| } |
| if ($request->packetNo) { |
| $conditions .= " AND evfp.packetNo = '$request->packetNo' "; |
| } |
| $sql = "SELECT evfp.subjectId,s.subjectName,s.subjectDesc,evfp.valuationCount,evfp.packetNo,evfp.chiefEvaluator,evfp.additionalEvaluator from examValuationFacultyPackets evfp |
| INNER JOIN subjects s ON s.subjectID = evfp.subjectId |
| WHERE evfp.$examRegFiled ='$request->examRegId' |
| AND (evfp.chiefEvaluator IN($request->staffId) OR evfp.additionalEvaluator IN($request->staffId)) |
| $conditions |
| group by evfp.$request->groupBy"; |
| try { |
| $exams = $this->executeQueryForList($sql); |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getMessage(), $e->getCode()); |
| } |
| return $exams; |
| } |
| |
| |
| |
| |
| |
| public function getExamValuationStudentsByPacket($request) |
| { |
| $request = $this->realEscapeObject($request); |
| $studentList=[]; |
| $joinThirdVal=""; |
| $condition =""; |
| if($request->valuationCount ==1){ |
| $examMarkTableFiled ="ee"; |
| } |
| elseif($request->valuationCount == 2){ |
| $examMarkTableFiled = "ee2"; |
| }elseif($request->valuationCount == 3){ |
| $examMarkTableFiled = "ee3"; |
| $joinThirdVal = " INNER JOIN externalexam_thirdvalstudents eth ON eth.examID = evpr.examId AND eth.studentID = evpr.studentId "; |
| } |
| if($request->falseNumber){ |
| $condition =" and efn.false_number = '$request->falseNumber' "; |
| } |
| $examRegFiled = $request->isSupply ? "supply_examreg_id" : "examregID"; |
| $examFalseNoExamRegField = $request->isSupply ? "exam_supplementary_id" : "examregID"; |
| $sql = "SELECT evpr.studentId,sa.regNo,sa.studentName,sa.batchID,evpr.examId,e.examTotalMarks,efn.false_number as falseNumber,efn.alpha_numeric_code as alphaNumericCode,ee.mark as mark1,ee2.mark as mark2,ee3.mark as mark3 |
| ,efn2.false_number as falseNumberInput,efn2.alpha_numeric_code as alphaNumericCodeInput |
| FROM examValuationStudentPacketsRelation evpr |
| INNER JOIN studentaccount sa ON sa.studentID = evpr.studentId |
| INNER JOIN exam e ON e.examID = evpr.examId |
| INNER JOIN examcontroller_false_number efn ON efn.studentID = evpr.studentId AND efn.examID = evpr.examId |
| LEFT JOIN exammarks_external ee ON ee.studentID = evpr.studentId AND ee.examID = evpr.examId |
| LEFT JOIN external_exammarks ee2 ON ee2.studentID = evpr.studentId AND ee2.examID = evpr.examId AND ee2.valuationCount = 2 |
| LEFT JOIN external_exammarks ee3 ON ee3.studentID = evpr.studentId AND ee3.examID = evpr.examId AND ee3.valuationCount = 3 |
| LEFT JOIN examcontroller_false_number efn2 ON efn2.studentID = evpr.studentId AND efn2.examID = $examMarkTableFiled.examID AND efn2.$examFalseNoExamRegField = e.$examRegFiled |
| $joinThirdVal |
| WHERE e.$examRegFiled = $request->examRegId and e.subjectID='$request->subjectId' and e.batchID = sa.batchID and evpr.packetNo='$request->packetNo' |
| $condition"; |
| try { |
| $studentList = $this->executeQueryForList($sql); |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getMessage(), $e->getCode()); |
| } |
| return $studentList; |
| } |
| |
| public function saveExamValuationPacketFalseNoOrder($request, $studentList){ |
| $request = $this->realEscapeObject($request); |
| $examRegFiled = $request->isSupply ? "supplyRegId" : "examRegId"; |
| $studentList = $this->realEscapeObject($studentList); |
| $sql = ""; |
| $falseNoOrder=[]; |
| $createdBy = $_SESSION['staffID']; |
| $order = new stdClass; |
| try { |
| if($request->adminId){ |
| $createdBy = $request->adminId; |
| $request->valuationCount=0; |
| foreach ($studentList as $key => $student) { |
| $student = (object)$student; |
| $order->sl = $key + 1; |
| $order->falseNo = $student->falseNumberInput; |
| if($request->saveExtraDetails){ |
| $order->mark = $student->externalMark; |
| $order->submitTime = $student->submitTime ? $student->submitTime : date("Y-m-d H:i:s"); |
| } |
| $falseNoOrder[] = $order; |
| unset($order); |
| } |
| }else{ |
| foreach ($studentList as $key => $student) { |
| $order->sl = $key + 1; |
| $order->falseNo = $student->falseNumberInput; |
| if ($request->saveExtraDetails) { |
| $order->mark = $student->externalMark; |
| $order->submitTime = $student->submitTime ? $student->submitTime : date("Y-m-d H:i:s"); |
| } |
| $falseNoOrder[] = $order; |
| unset($order); |
| } |
| } |
| $falseNoOrderJson = json_encode($falseNoOrder); |
| $sql ="INSERT INTO examValuationPacketFalseNoOrder($examRegFiled,subjectId,valuationCount,packetNo,falseNoOrder,created_by) |
| VALUES ($request->examRegId,$request->subjectId,$request->valuationCount,'$request->packetNo','$falseNoOrderJson',$createdBy) ON DUPLICATE KEY UPDATE falseNoOrder = VALUES (falseNoOrder)"; |
| $this->executeQueryForObject($sql); |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getMessage(), $e->getCode()); |
| } |
| return true; |
| } |
| |
| public function getExamValuationPacketFalseNoOrder($request) |
| { |
| $request = $this->realEscapeObject($request); |
| $examRegFiled = $request->isSupply ? "supplyRegId" : "examRegId"; |
| $falseNoOrder=null; |
| try { |
| $sql ="SELECT falseNoOrder FROM examValuationPacketFalseNoOrder |
| WHERE $examRegFiled = $request->examRegId AND subjectId=$request->subjectId AND valuationCount=$request->valuationCount AND packetNo='$request->packetNo'"; |
| $falseNoOrder = $this->executeQueryForObject($sql); |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getMessage(), $e->getCode()); |
| } |
| return $falseNoOrder; |
| } |
| |
| |
| |
| |
| |
| public function getExamValuationSubjectsBatchByStaff($request) |
| { |
| $request = $this->realEscapeObject($request); |
| $examRegFiled = $request->isSupply ? "supplyRegId" : "examRegId"; |
| $examRegistrationFiled = $request->isSupply ? "supply_examreg_id" : "examregID"; |
| $conditions = ""; |
| if ($request->subjectId) { |
| $conditions .= " AND evfp.subjectId = $request->subjectId "; |
| } |
| if ($request->valuationCount) { |
| $conditions .= " AND evfp.valuationCount = $request->valuationCount "; |
| } |
| $sql = "SELECT evfp.subjectId,e.examID,e.batchID from examValuationFacultyPackets evfp |
| INNER JOIN subjects s ON s.subjectID = evfp.subjectId |
| INNER JOIN exam e ON e.$examRegistrationFiled = evfp.examRegId AND e.subjectID = evfp.subjectId |
| WHERE evfp.$examRegFiled ='$request->examRegId' |
| AND (evfp.chiefEvaluator IN($request->staffId) OR evfp.additionalEvaluator IN($request->staffId)) |
| $conditions |
| group by e.batchID"; |
| try { |
| $exams = $this->executeQueryForList($sql); |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getMessage(), $e->getCode()); |
| } |
| return $exams; |
| } |
| |
| |
| public function saveStudentExternalExamValuationMarkAndAttendance($students, $request) |
| { |
| $students = $this->realEscapeArray($students); |
| $request = $this->realEscapeObject($request); |
| $eemValues = []; |
| $eeValues = []; |
| $markEnteringPersonColumn = ""; |
| |
| if (empty($students)) { |
| throw new ProfessionalException(ProfessionalException::INVALID_PARAMETER, "Students can not be null"); |
| } |
| if (empty($request->valuationCount)) { |
| throw new ProfessionalException(ProfessionalException::INVALID_PARAMETER, "Valuation count can not be null"); |
| } |
| $markEnteringPersonColumn = $request->isChiefValuator ? "chiefEvaluator" : "additionalEvaluator"; |
| foreach ($students as $student) { |
| if (empty($student->examId) || empty($student->studentId)) { |
| throw new ProfessionalException(ProfessionalException::INVALID_PARAMETER, "Some students have no valid data"); |
| } |
| if ($student->mark == "AB") { |
| $isAbsent = 1; |
| } elseif ($student->mark == "MAL") { |
| $isAbsent = 2; |
| } else { |
| $isAbsent = 0; |
| } |
| if($request->valuationCount == 2){ |
| $student->revalTypeSelected = $student->revalTypeSelected2; |
| } |
| $eemValues[] = "('$student->examId','$student->studentId'," . ($student->mark ? $student->mark : 0) . ",$request->valuationCount,'$request->packetNo',$request->staffId,$request->staffId)"; |
| if (!$isAbsent) { |
| $eeValues[] = "('$student->examId','$student->studentId'," . ($student->mark ? $student->mark : 0) . ",$request->valuationCount,'$request->packetNo',$request->staffId,'$student->revalTypeSelected',$request->staffId)"; |
| } |
| $exAtt[] = "('$student->examId','$student->studentId','$isAbsent')"; |
| } |
| |
| try { |
| if ($request->isChiefValuator || $request->valuationCount == 2 ) { |
| $eeValuesString = implode(", ", $eeValues); |
| $sql = "INSERT INTO examValuationMarks (examId,studentId,mark,valuationCount,packetNo,$markEnteringPersonColumn,revalType,created_by) VALUES $eeValuesString ON DUPLICATE KEY UPDATE mark = VALUES(mark),revalType = values(revalType)"; |
| $this->executeQuery($sql); |
| } else { |
| $eemValuesString = implode(", ", $eemValues); |
| $sql = "INSERT INTO examValuationMarks (examId,studentId,mark,valuationCount,packetNo,$markEnteringPersonColumn,created_by) VALUES $eemValuesString ON DUPLICATE KEY UPDATE mark = VALUES(mark)"; |
| $exAttString = implode(", ", $exAtt); |
| $sql2 = "INSERT INTO exam_attendance (examID, studentID, isAbsent) VALUES $exAttString ON DUPLICATE KEY UPDATE isAbsent = VALUES(isAbsent)"; |
| $this->executeQuery($sql); |
| $this->executeQuery($sql2); |
| } |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); |
| } |
| return; |
| } |
| |
| |
| |
| |
| |
| |
| public function getUgExamValuationStudentsByPacket($request) |
| { |
| $request = $this->realEscapeObject($request); |
| $studentList=[]; |
| $joinThirdVal=""; |
| $condition =""; |
| if(!$request->isChiefValuator && $request->valuationCount == 1){ |
| $examValMarkTable = "evm1"; |
| }elseif($request->isChiefValuator && $request->valuationCount == 1){ |
| $examValMarkTable = "evm1"; |
| } |
| else if($request->valuationCount == 2){ |
| $examValMarkTable ="evm3"; |
| $joinThirdVal = " INNER JOIN externalexam_thirdvalstudents eth ON eth.examID = evpr.examId AND eth.studentID = evpr.studentId "; |
| } |
| if($request->falseNumber){ |
| $condition =" and efn.false_number = '$request->falseNumber' "; |
| } |
| $examRegFiled = $request->isSupply ? "supply_examreg_id" : "examregID"; |
| $examFalseNoExamRegField = $request->isSupply ? "exam_supplementary_id" : "examregID"; |
| $sql = "SELECT evpr.studentId,sa.regNo,sa.studentName,sa.batchID,evpr.examId,e.examTotalMarks,efn.false_number as falseNumber,efn.alpha_numeric_code as alphaNumericCode,evm1.mark as mark1,evm2.mark as mark2,evm3.mark as mark3 |
| ,efn2.false_number as falseNumberInput,efn2.alpha_numeric_code as alphaNumericCodeInput, |
| evm2.revalType as revalTypeSelected,evm3.revalType as revalTypeSelected2 |
| FROM examValuationStudentPacketsRelation evpr |
| INNER JOIN studentaccount sa ON sa.studentID = evpr.studentId |
| INNER JOIN exam e ON e.examID = evpr.examId |
| INNER JOIN examcontroller_false_number efn ON efn.studentID = evpr.studentId AND efn.examID = evpr.examId |
| |
| LEFT JOIN examValuationMarks evm1 ON evm1.studentID = evpr.studentId AND evm1.examId = evpr.examId AND evm1.valuationCount =1 AND evm1.additionalEvaluator IS NOT NULL |
| LEFT JOIN examValuationMarks evm2 ON evm2.studentID = evpr.studentId AND evm2.examId = evpr.examId AND evm2.valuationCount =1 AND evm2.chiefEvaluator IS NOT NULL |
| LEFT JOIN examValuationMarks evm3 ON evm3.studentID = evpr.studentId AND evm3.examId = evpr.examId AND evm3.valuationCount = 2 |
| LEFT JOIN examcontroller_false_number efn2 ON efn2.studentID = evpr.studentId AND efn2.examID = $examValMarkTable.examID AND efn2.$examFalseNoExamRegField = e.$examRegFiled |
| $joinThirdVal |
| WHERE e.$examRegFiled = $request->examRegId and e.subjectID='$request->subjectId' and e.batchID = sa.batchID and evpr.packetNo='$request->packetNo' |
| $condition"; |
| try { |
| $studentList = $this->executeQueryForList($sql); |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getMessage(), $e->getCode()); |
| } |
| return $studentList; |
| } |
| |
| |
| |
| |
| |
| public function getThirdValStudentsExamValuationPackets($request) |
| { |
| $request = $this->realEscapeObject($request); |
| $examRegistrationFiled = $request->isSupply ? "supply_examreg_id" : "examregID"; |
| $conditions = ""; |
| if ($request->subjectId) { |
| $conditions .= " AND e.subjectID = $request->subjectId "; |
| } |
| $sql = "SELECT eth.studentID,evpr.packetNo from exam e |
| INNER JOIN externalexam_thirdvalstudents eth ON eth.examID = e.examID |
| INNER JOIN examValuationStudentPacketsRelation evpr ON evpr.examId = e.examID AND evpr.studentId = eth.studentID |
| where e.$examRegistrationFiled = '$request->examRegId' |
| $conditions group by evpr.packetNo"; |
| try { |
| $packets = $this->executeQueryForList($sql); |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getMessage(), $e->getCode()); |
| } |
| return $packets; |
| } |
| |
| |
| |
| |
| |
| public function deleteStudentExamMark($request) |
| { |
| $request = $this->realEscapeObject($request); |
| if ($request->courseType == CourseTypeConstants::UG) { |
| $sql = "DELETE FROM exammarks_external WHERE examID = '$request->examId' AND studentID='$request->studentId'"; |
| } |
| if ($request->courseType == CourseTypeConstants::PG) { |
| $sql = "DELETE FROM externalexammarks_finalized WHERE examID = '$request->examId' AND studentID='$request->studentId'"; |
| } |
| try { |
| $this->executeQueryForList($sql); |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getMessage(), $e->getCode()); |
| } |
| return true; |
| } |
| |
| |
| |
| |
| |
| public function getAssignedStudentsCountForSubjectValuation($request) |
| { |
| $request = $this->realEscapeObject($request); |
| $studentCounts = []; |
| $sql= "SELECT count(distinct(evss.studentId)) as assignedStudentCount,evss.valuationCount FROM exam_reg_studentsubject erss |
| INNER JOIN exam_reg_studentchallan ersc ON ersc.examregID = erss.examregID AND ersc.studentID= erss.studentID |
| INNER JOIN examValuationStaffAssignedStudents evss ON evss.examRegId = erss.examregID AND evss.subjectId = erss.subjectID AND evss.studentId = erss.studentID |
| WHERE erss.examregID IN($request->examRegId) AND erss.subjectID IN($request->subjectId) AND ersc.paid=1 AND evss.examType = 'REGULAR' |
| group by evss.valuationCount ORDER BY evss.valuationCount"; |
| try { |
| $studentCounts = $this->executeQueryForList($sql); |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getMessage(), $e->getCode()); |
| } |
| return $studentCounts; |
| } |
| |
| |
| |
| |
| |
| public function getMarkConfirmedStudentsCountForSubjectValuation($request) |
| { |
| $request = $this->realEscapeObject($request); |
| $studentCounts = []; |
| $sql = "SELECT count(distinct erss.studentID) as confirmedStudentCount,oec.valuation_count as valuationCount |
| 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 oe_exam_marks_confirm oec ON oec.oe_exams_id = oe.id AND oec.oe_users_id = erss.studentID |
| WHERE erss.examregID IN($request->examRegId) AND erss.subjectID IN($request->subjectId) AND ersc.paid=1 AND oec.is_confirmed = 1 AND oec.revaluation_id IS NULL |
| group by oec.valuation_count order by oec.valuation_count,sa.regNo"; |
| try { |
| $studentCounts = $this->executeQueryForList($sql); |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getMessage(), $e->getCode()); |
| } |
| return $studentCounts; |
| } |
| |
| |
| |
| |
| |
| public function getExamSubjectRegisteredStudentCount($request) |
| { |
| $request = $this->realEscapeObject($request); |
| $studentCounts = []; |
| $sql = "SELECT count(distinct ersc.studentID) as totalStudentCount FROM exam_reg_studentchallan ersc |
| INNER JOIN exam_reg_studentsubject erss ON erss.examregID = ersc.examregID AND erss.studentID = ersc.studentID |
| WHERE erss.examregID IN ($request->examRegId) AND erss.subjectID IN ($request->subjectId) AND ersc.paid=1"; |
| try { |
| $studentCounts = $this->executeQueryForObject($sql); |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getMessage(), $e->getCode()); |
| } |
| return $studentCounts; |
| } |
| |
| |
| |
| |
| |
| |
| public function createExamRevaluationPackets($packet) |
| { |
| $packet = $this->realEscapeObject($packet); |
| $sql = "INSERT INTO examRevaluationPackets(revaluationId,revaluationTypeId,prefix,startNumber,endNumber) VALUES('$packet->revaluationId','$packet->revaluationTypeId','$packet->prefix','$packet->start','$packet->end') ON DUPLICATE KEY UPDATE |
| prefix = VALUES(prefix), |
| startNumber = VALUES(startNumber), |
| endNumber = VALUES(endNumber)"; |
| try { |
| if ($packet->revaluationId && $packet->revaluationTypeId && $packet->prefix && $packet->start && $packet->end) { |
| $this->executeQueryForObject($sql); |
| } |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); |
| } |
| return true; |
| } |
| |
| |
| |
| |
| |
| public function getExamRevaluationPackets($request) |
| { |
| $request = $this->realEscapeObject($request); |
| $packet = null; |
| $sql = "SELECT prefix,startNumber as start,endNumber as end FROM examRevaluationPackets WHERE revaluationId = '$request->revaluationId' AND revaluationTypeId = '$request->revaluationTypeId'"; |
| try { |
| $packet = $this->executeQueryForObject($sql); |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); |
| } |
| return $packet; |
| } |
| |
| |
| |
| |
| |
| |
| public function saveExamRevaluationStudentPackets($request) |
| { |
| $request = $this->realEscapeObject($request); |
| $insertValues = []; |
| $insertStr = ""; |
| foreach ($request->studentList as $student) { |
| $student = (object) $student; |
| if ($student->scannedFalseNumber) { |
| $studentDetails = new \stdClass(); |
| $studentDetails = FalseNumberService::getInstance()->getFalseNumberDetails($student->scannedFalseNumber); |
| $insertValues[] = "('$request->packetNo','$request->revaluationId','$request->revaluationTypeId','$studentDetails->examId','$studentDetails->studentId','$request->adminId')"; |
| unset($studentDetails); |
| } |
| } |
| if (!empty($insertValues)) { |
| $insertStr = implode(",", $insertValues); |
| $sql = "INSERT INTO examRevaluationStudentPacketsRelation(packetNo,revaluationId,revaluationTypeId,examId,studentId,created_by) |
| VALUES $insertStr ON DUPLICATE KEY UPDATE |
| packetNo = VALUES(packetNo), |
| updated_by = VALUES(created_by)"; |
| } |
| try { |
| $this->executeQueryForObject($sql); |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); |
| } |
| return true; |
| } |
| |
| |
| |
| |
| |
| public function deleteExamRevaluationStudentPacket($request) |
| { |
| $request = $this->realEscapeObject($request); |
| $studentDetails = FalseNumberService::getInstance()->getFalseNumberDetails($request->falseNumber); |
| if (!empty($studentDetails)) { |
| $sql = "DELETE FROM examRevaluationStudentPacketsRelation WHERE packetNo='$request->packetNo' |
| AND revaluationId = '$request->revaluationId' |
| AND revaluationTypeId = '$request->revaluationTypeId' |
| AND examId = '$studentDetails->examId' AND studentId ='$studentDetails->studentId'"; |
| } |
| try { |
| $this->executeQueryForObject($sql); |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); |
| } |
| return true; |
| } |
| |
| |
| |
| |
| |
| public function getExamRevaluationAssignedPackets($request) |
| { |
| $request = $this->realEscapeObject($request); |
| $packets = null; |
| $sql = "SELECT distinct evpr.packetNo from examRevaluationStudentPacketsRelation evpr |
| INNER JOIN exam e ON e.examID = evpr.examId |
| WHERE evpr.revaluationId = '$request->revaluationId' |
| AND evpr.revaluationTypeId = '$request->revaluationTypeId' |
| AND e.subjectID NOT IN ($request->subjectId)"; |
| try { |
| $packets = $this->executeQueryForList($sql); |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); |
| } |
| return $packets; |
| } |
| |
| |
| public function saveExamRevaluationPacketFalseNoOrder($request, $studentList) |
| { |
| $request = $this->realEscapeObject($request); |
| $studentList = $this->realEscapeObject($studentList); |
| $sql = ""; |
| $falseNoOrder = []; |
| $createdBy = $_SESSION['staffID']; |
| $request->isFinalized = $request->isFinalized ? $request->isFinalized : 0; |
| $order = new stdClass; |
| try { |
| if ($request->adminId) { |
| $createdBy = $request->adminId; |
| $request->valuationCount = 0; |
| foreach ($studentList as $key => $student) { |
| $student = (object)$student; |
| $order->sl = $key + 1; |
| $order->falseNo = $student->falseNumberInput; |
| $falseNoOrder[] = $order; |
| unset($order); |
| } |
| } else { |
| foreach ($studentList as $key => $student) { |
| $order->sl = $key + 1; |
| $order->falseNo = $student->falseNumberInput; |
| $falseNoOrder[] = $order; |
| unset($order); |
| } |
| } |
| $falseNoOrderJson = json_encode($falseNoOrder); |
| $sql = "INSERT INTO examRevaluationPacketFalseNoOrder(revaluationId,revaluationTypeId,subjectId,valuationCount,packetNo,falseNoOrder,isFinalized,created_by) |
| VALUES ($request->revaluationId,$request->revaluationTypeId,$request->subjectId,$request->valuationCount,'$request->packetNo','$falseNoOrderJson','$request->isFinalized',$createdBy) |
| ON DUPLICATE KEY UPDATE falseNoOrder = VALUES (falseNoOrder), isFinalized = VALUES (isFinalized)"; |
| $this->executeQueryForObject($sql); |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getMessage(), $e->getCode()); |
| } |
| return true; |
| } |
| |
| public function getExamRevaluationPacketFalseNoOrder($request) |
| { |
| $request = $this->realEscapeObject($request); |
| $falseNoOrder = null; |
| try { |
| $sql = "SELECT falseNoOrder,isFinalized FROM examRevaluationPacketFalseNoOrder |
| WHERE revaluationId = $request->revaluationId AND revaluationTypeId = $request->revaluationTypeId AND subjectId=$request->subjectId AND valuationCount=$request->valuationCount AND packetNo='$request->packetNo'"; |
| $falseNoOrder = $this->executeQueryForObject($sql); |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getMessage(), $e->getCode()); |
| } |
| return $falseNoOrder; |
| } |
| |
| |
| |
| |
| |
| |
| public function assignFacultyToRevaluationPackets($request) |
| { |
| $request = $this->realEscapeObject($request); |
| $assignDetails = $request->assignDetails; |
| $createdBy = $_SESSION['adminID']; |
| $insertValues = []; |
| |
| foreach ($assignDetails->packetSelected as $packetSelected) { |
| $insertValues[] = "('$request->revaluationId','$request->revaluationTypeId','$request->subjectId','$request->valCount','$packetSelected','$assignDetails->evaluatorSelected','$createdBy')"; |
| } |
| $sql = "INSERT INTO examRevaluationFacultyPackets(revaluationId,revaluationTypeId,subjectId,valuationCount,packetNo,evaluator,created_by) |
| VALUES " . implode(",", $insertValues);; |
| |
| try { |
| $this->executeQueryForObject($sql); |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); |
| } |
| return true; |
| } |
| |
| |
| |
| |
| |
| |
| public function getAssignedRevaluationPacketsBySubject($request) |
| { |
| $request = $this->realEscapeObject($request); |
| $conditions = ""; |
| if ($request->subjectId) { |
| $conditions .= " AND e.subjectId = '$request->subjectId' "; |
| } |
| $packetList = []; |
| $sql = "SELECT distinct evpr.packetNo as id,evpr.packetNo as name from examRevaluationStudentPacketsRelation evpr |
| INNER JOIN exam e ON e.examID = evpr.examId |
| WHERE evpr.revaluationId = '$request->revaluationId' AND evpr.revaluationTypeId = '$request->revaluationTypeId' |
| $conditions |
| ORDER BY evpr.packetNo"; |
| |
| try { |
| $packetList = $this->executeQueryForList($sql); |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); |
| } |
| return $packetList; |
| } |
| |
| |
| |
| |
| |
| |
| public function getAssignedFacultyToRevaluationPackets($request) |
| { |
| $request = $this->realEscapeObject($request); |
| $conditions = ""; |
| if ($request->subjectId) { |
| $conditions .= " AND evf.subjectId = '$request->subjectId' "; |
| } |
| if ($request->getPacketsAssignedForValCount) { |
| $conditions .= " AND evf.valuationCount = '$request->valCount' "; |
| } |
| $packetList = []; |
| $sql = "SELECT evf.valuationCount,evf.packetNo,evf.evaluator as evaluatorId,sa.staffName as evaluator |
| FROM examRevaluationFacultyPackets evf |
| LEFT JOIN staffaccounts sa ON sa.staffID = evf.evaluator |
| WHERE evf.revaluationId = '$request->revaluationId' AND evf.revaluationTypeId = '$request->revaluationTypeId' |
| $conditions |
| ORDER BY evf.valuationCount,evf.packetNo"; |
| |
| try { |
| $packetList = $this->executeQueryForList($sql); |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); |
| } |
| return $packetList; |
| } |
| |
| |
| |
| |
| |
| public function deleteAssignedFacultyToRevaluationPackets($request) |
| { |
| $request = $this->realEscapeObject($request); |
| $facultyDetails = $request->facultyDetails; |
| $sql = "DELETE from examRevaluationFacultyPackets |
| WHERE revaluationId = '$request->revaluationId' AND revaluationTypeId = '$request->revaluationTypeId' AND subjectId = '$request->subjectId' AND valuationCount='$facultyDetails->valuationCount' AND packetNo ='$facultyDetails->packetNo' AND evaluator ='$facultyDetails->evaluatorId'"; |
| |
| try { |
| $this->executeQueryForObject($sql); |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); |
| } |
| return true; |
| } |
| |
| |
| |
| |
| |
| public function getExamRevaluationSubjectsByStaff($request) |
| { |
| $request = $this->realEscapeObject($request); |
| $conditions = ""; |
| if ($request->subjectId) { |
| $conditions .= " AND evfp.subjectId = $request->subjectId "; |
| } |
| if ($request->valuationCount) { |
| $conditions .= " AND evfp.valuationCount = $request->valuationCount "; |
| } |
| if ($request->packetNo) { |
| $conditions .= " AND evfp.packetNo = '$request->packetNo' "; |
| } |
| $sql = "SELECT evfp.subjectId,s.subjectName,s.subjectDesc,evfp.valuationCount,evfp.packetNo,evfp.evaluator from examRevaluationFacultyPackets evfp |
| INNER JOIN subjects s ON s.subjectID = evfp.subjectId |
| WHERE evfp.revaluationId = '$request->revaluationId' AND evfp.revaluationTypeId = '$request->revaluationTypeId' |
| AND evfp.evaluator IN($request->staffId) |
| $conditions |
| group by evfp.$request->groupBy"; |
| try { |
| $exams = $this->executeQueryForList($sql); |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getMessage(), $e->getCode()); |
| } |
| return $exams; |
| } |
| |
| |
| |
| |
| |
| |
| |
| public function getSubjectExamRevaluationDates($request) |
| { |
| $request = $this->realEscapeObject($request); |
| $valuationDates = null; |
| $dateColumns = ""; |
| 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 revaluationDatesSubjectWise |
| WHERE revaluationId = '$request->revaluationId' AND revaluationTypeId = '$request->revaluationTypeId' AND subjectId='$request->subjectId'"; |
| $valuationDates = $this->executeQueryForObject($sql); |
| } catch (\Exception $e) { |
| |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); |
| } |
| |
| return $valuationDates; |
| } |
| |
| |
| |
| |
| |
| public function getExamRevaluationStudentsByPacket($request) |
| { |
| $request = $this->realEscapeObject($request); |
| $studentList = []; |
| $joinThirdVal = ""; |
| $condition = ""; |
| $revaluationMarksTable = "erm"; |
| if ($request->valuationCount == 2) { |
| $joinThirdVal = " INNER JOIN externalexam_thirdvalstudents eth ON eth.examID = evpr.examId AND eth.studentID = evpr.studentId AND eth.revaluationFlag = 1"; |
| $revaluationMarksTable = "ermt"; |
| } |
| if ($request->falseNumber) { |
| $condition = " and efn.false_number = '$request->falseNumber' "; |
| } |
| |
| if ($request->courseType == CourseTypeConstants::PG) { |
| $markCondition = "externalexammarks_finalized ee ON (ee.examID = evpr.examId |
| AND ee.studentID = evpr.studentId)"; |
| } else { |
| $markCondition = "exammarks_external ee ON (ee.examID = evpr.examId |
| AND ee.studentID = evpr.studentId)"; |
| } |
| $sql = "SELECT evpr.studentId,sa.regNo,sa.studentName,sa.batchID,evpr.examId,e.examTotalMarks,efn.false_number as falseNumber,efn.alpha_numeric_code as alphaNumericCode,ee.mark as oldMark,erm.mark as revalMark,ermt.mark as thirdValMark |
| ,efn2.false_number as falseNumberInput,efn2.alpha_numeric_code as alphaNumericCodeInput |
| FROM examRevaluationStudentPacketsRelation evpr |
| INNER JOIN studentaccount sa ON sa.studentID = evpr.studentId |
| INNER JOIN exam e ON e.examID = evpr.examId |
| INNER JOIN examcontroller_false_number efn ON efn.studentID = evpr.studentId AND efn.examID = evpr.examId |
| LEFT JOIN $markCondition |
| LEFT JOIN exam_revaluation_marks erm ON erm.studentID = evpr.studentId AND erm.examID = evpr.examId AND erm.exam_revaluation_id = evpr.revaluationId |
| LEFT JOIN exam_revaluation_marks_thirdval ermt ON ermt.studentID = evpr.studentId AND ermt.examID = evpr.examId AND ermt.exam_revaluation_id = evpr.revaluationId |
| LEFT JOIN examcontroller_false_number efn2 ON efn2.studentID = evpr.studentId AND efn2.examID = $revaluationMarksTable.examID |
| $joinThirdVal |
| WHERE evpr.revaluationId = '$request->revaluationId' AND evpr.revaluationTypeId = '$request->revaluationTypeId' and e.subjectID='$request->subjectId' and e.batchID = sa.batchID and evpr.packetNo='$request->packetNo' |
| $condition"; |
| try { |
| $studentList = $this->executeQueryForList($sql); |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getMessage(), $e->getCode()); |
| } |
| return $studentList; |
| } |
| |
| |
| |
| |
| |
| |
| public function getThirdValStudentsExamRevaluationPackets($request) |
| { |
| $request = $this->realEscapeObject($request); |
| $examRegistrationFiled = $request->isSupply ? "supply_examreg_id" : "examregID"; |
| $conditions = ""; |
| if ($request->subjectId) { |
| $conditions .= " AND e.subjectID = $request->subjectId "; |
| } |
| $sql = "SELECT eth.studentID,evpr.packetNo from exam e |
| INNER JOIN externalexam_thirdvalstudents eth ON eth.examID = e.examID |
| INNER JOIN examRevaluationStudentPacketsRelation evpr ON evpr.examId = e.examID AND evpr.studentId = eth.studentID |
| where evpr.revaluationId = '$request->revaluationId' AND evpr.revaluationTypeId='$request->revaluationTypeId' |
| AND eth.revaluationFlag = 1 |
| $conditions group by evpr.packetNo"; |
| try { |
| $packets = $this->executeQueryForList($sql); |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getMessage(), $e->getCode()); |
| } |
| return $packets; |
| } |
| |
| |
| |
| |
| public function getExamModerationRules() |
| { |
| $rules=[]; |
| $sql = "SELECT id,rule_name as name, |
| max_sum as maxSum, |
| max_sum_select maxSumType, |
| max_each_sub maxEachSub, |
| max_each_sub_select maxEachSubType, |
| max_only_one_sub maxOnlyOneSubFail, |
| max_only_one_sub_select maxOnlyOneSubFailType, |
| if_fullpass ifFullPass, |
| only_passmark onlyPassMark |
| FROM moderation_rule"; |
| try { |
| $rules = $this->executeQueryForList($sql); |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getMessage(), $e->getCode()); |
| } |
| return $rules; |
| } |
| |
| |
| |
| |
| |
| public function getBatchesSubjectsWithModerationMarksByExamregistration($request) |
| { |
| $batches = []; |
| $condition=""; |
| $request = $this->realEscapeObject($request); |
| if($request->batchId){ |
| $condition .= " AND b.batchID IN ($request->batchId)"; |
| if($request->isTheory == 1){ |
| $condition .= " AND s.isTheory = 1"; |
| } |
| else if ($request->isTheory == 2) { |
| $condition .= " AND s.isTheory = 0"; |
| } |
| }else{ |
| $condition .= " AND s.isTheory = 1"; |
| } |
| if ($request->batchIds){ |
| $condition .= " AND b.batchID IN ($request->batchIds)"; |
| } |
| else if($request->excludeBatchIds){ |
| $condition .= " AND b.batchID NOT IN ($request->excludeBatchIds)"; |
| } |
| if ($request->isSupply) { |
| $sql = "SELECT distinct(b.batchID) as batchId,b.batchName,es.semId,b.batchDesc, |
| e.examID as examId,e.examName,s.subjectID as subjectId,s.subjectName,s.subjectDesc,s.isTheory,e.examTotalMarks,mmes.max_mark as subjectModerationMaxMark |
| FROM exam_supplementary_student_details essd |
| INNER JOIN studentaccount sa ON sa.studentID = essd.studentID |
| INNER JOIN batches b ON b.batchID = sa.batchID |
| INNER JOIN exam_supplementary es ON es.id = essd.exam_supplementary_id |
| INNER JOIN exam e ON e.batchID = b.batchID AND e.supply_examreg_id = essd.exam_supplementary_id AND e.semID = es.semId |
| INNER JOIN subjects s ON s.subjectID = e.subjectID |
| LEFT JOIN moderation_max_mark_each_sub mmes ON mmes.examId = e.examID AND mmes.exam_supplementary_id = essd.exam_supplementary_id AND mmes.batchId = e.batchID AND mmes.moderation_rule_id IN($request->ruleId) |
| WHERE essd.exam_supplementary_id IN ($request->examRegId) and essd.paid=1 |
| $condition |
| GROUP BY b.batchID,e.examID order by b.batchID asc,e.examID asc"; |
| } else { |
| $sql = "SELECT distinct(erb.batchID) as batchId,b.batchName,erb.semID as semId,b.batchDesc, |
| e.examID as examId,e.examName,s.subjectID as subjectId,s.subjectName,s.subjectDesc,s.isTheory,e.examTotalMarks,mmes.max_mark as subjectModerationMaxMark |
| FROM exam_registration_batches erb |
| INNER JOIN batches b ON b.batchID = erb.batchID |
| INNER JOIN exam_registration er ON er.examregID = erb.examregID |
| INNER JOIN exam e ON e.batchID = b.batchID AND e.examregID = erb.examregID AND e.semID = erb.semID |
| INNER JOIN subjects s ON s.subjectID = e.subjectID |
| LEFT JOIN moderation_max_mark_each_sub mmes ON mmes.examId = e.examID AND mmes.exam_registration_id = erb.examregID AND mmes.batchId = e.batchID AND mmes.moderation_rule_id IN($request->ruleId) |
| WHERE erb.examregID IN ($request->examRegId) |
| $condition |
| GROUP BY b.batchID,e.examID order by b.batchID asc,e.examID asc"; |
| } |
| try { |
| $batches = $this->executeQueryForList($sql); |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getMessage(), $e->getCode()); |
| } |
| return $batches; |
| } |
| |
| |
| |
| |
| |
| |
| public function saveModerationMarksForEachSubjects($request) |
| { |
| $request = $this->realEscapeObject($request); |
| $insertValues=[]; |
| $subjectList = $request->subjectList; |
| $examRegField = $request->isSupply ? "exam_supplementary_id" : "exam_registration_id"; |
| $considerWhichPaperFlag = $request->isSupply ? "2" : "1"; |
| foreach($subjectList as $subject){ |
| $examIdArray[] = $subject->examId; |
| $insertValues[]= "('$request->ruleId','$subject->examId','$subject->subjectModerationMaxMark','$subject->batchId','$request->examRegId','$considerWhichPaperFlag')"; |
| } |
| |
| $queryDel = "DELETE from moderation_rule_assign where |
| moderation_rule_id = '$request->ruleId' and $examRegField = '$request->examRegId' and batchID = '$request->batchId'"; |
| $this->executeQueryForList($queryDel); |
| |
| $query = "INSERT into moderation_rule_assign (moderation_rule_id,$examRegField,batchID) values |
| ('$request->ruleId','$request->examRegId','$request->batchId')"; |
| $this->executeQueryForList($query); |
| |
| |
| if(!empty($examIdArray)){ |
| $examIdArray = implode(",", $examIdArray); |
| $deleteSql = "DELETE FROM moderation_max_mark_each_sub WHERE moderation_rule_id = '$request->ruleId' AND batchId = '$request->batchId' AND $examRegField = '$request->examRegId' AND examId IN ($examIdArray)"; |
| $this->executeQueryForList($deleteSql); |
| } |
| |
| |
| if(!empty($insertValues)){ |
| $insertValues = implode(",", $insertValues); |
| $sql = "INSERT INTO moderation_max_mark_each_sub (moderation_rule_id,examId,max_mark,batchId,$examRegField,considerWhichPaperFlag) VALUES $insertValues"; |
| try { |
| $this->executeQueryForList($sql); |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getMessage(), $e->getCode()); |
| } |
| } |
| } |
| |
| |
| |
| public function confirmExamValuationMarkEntry($request) |
| { |
| $request = $this->realEscapeObject($request); |
| $examRegField = $request->isSupply ? "supplyRegId" : "examRegId"; |
| $evaluatorField = $request->isChiefEvaluator ? "chiefEvaluator" : "additionalEvaluator"; |
| $sql = "INSERT INTO examValuationMarksConfirm ($examRegField,subjectId,valuationCount,packetNo,$evaluatorField,created_by) values('$request->examRegId','$request->subjectId','$request->valuationCount','$request->packetNo','$request->staffId','$request->staffId')"; |
| try { |
| $this->executeQueryForObject($sql); |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getMessage(), $e->getCode()); |
| } |
| return true; |
| } |
| |
| |
| |
| |
| |
| public function getExamModerationBatchRulesByExamRegistrtaion($request) |
| { |
| $rules = []; |
| $examRegField = $request->isSupply ? "exam_supplementary_id" : "exam_registration_id"; |
| $sql = "SELECT |
| mmes.id as id, |
| mmes.max_mark as subjectMaxMark, |
| mr.max_each_sub_select as subjectMarkType, |
| mr.max_sum as semMaxMark, |
| mr.max_sum_select as semMarkType, |
| mr.max_only_one_sub as oneSubFailMaxMark, |
| mr.max_only_one_sub_select as oneSubFailMarkType, |
| mr.if_fullpass as ifFullPass,mr.only_passmark as onlyPassMark, |
| mra.batchID as batchId,mmes.examId |
| FROM moderation_rule mr |
| INNER JOIN moderation_rule_assign mra ON mra.moderation_rule_id = mr.id |
| INNER JOIN moderation_max_mark_each_sub mmes ON mmes.moderation_rule_id = mr.id AND mmes.batchId = mra.batchID AND mmes.$examRegField = mra.$examRegField |
| where mra.$examRegField IN($request->examRegId) AND mr.id IN($request->ruleId)"; |
| try { |
| $rules = $this->executeQueryForList($sql); |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getMessage(), $e->getCode()); |
| } |
| return $rules; |
| } |
| |
| |
| |
| |
| |
| |
| public function saveStudentModerationMarks($request) |
| { |
| $request = $this->realEscapeObject($request); |
| $studentList = $request->studentList; |
| $examRegField = $request->isSupply ? "supplyRegId": "examRegId"; |
| $sql = ""; |
| $insertValues=[]; |
| foreach($studentList as $student){ |
| foreach($student->subjects as $subject){ |
| $insertValues[]= "('$request->examRegId','$request->ruleId','$subject->examId','$student->studentId','$subject->moderationMark','$request->createdBy')"; |
| } |
| } |
| try { |
| if(!empty($insertValues)){ |
| $insertValues = implode(",", $insertValues); |
| $sql = "INSERT INTO studentExamModerationMarks($examRegField,ruleId,examId,studentId,mark,created_by) |
| VALUES $insertValues ON DUPLICATE KEY UPDATE mark = VALUES (mark)"; |
| |
| $this->executeQueryForObject($sql); |
| } |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getMessage(), $e->getCode()); |
| } |
| return true; |
| } |
| |
| |
| |
| |
| |
| public function getExamModerationMarksAppliedByExamRegAndRule($request) |
| { |
| $request = $this->realEscapeObject($request); |
| $examRegField = $request->isSupply ? "supplyRegId" : "examRegId"; |
| $sql = ""; |
| if($request->studentMarkBeforeModerationMethod){ |
| $examRegField = $request->isSupply ? "supply_examreg_id" : "examregID"; |
| $sql = "SELECT examID,moderation_rule_id FROM student_mark_before_moderation WHERE moderation_rule_id IN($request->ruleId) AND examID IN (SELECT examID from exam where $examRegField IN($request->examRegId)) GROUP BY examID,moderation_rule_id "; |
| }else{ |
| $sql = "SELECT $examRegField,ruleId FROM studentExamModerationMarks WHERE ruleId IN($request->ruleId) AND $examRegField IN($request->examRegId) GROUP BY ruleId,$examRegField "; |
| } |
| try { |
| $applied = $this->executeQueryForObject($sql); |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getMessage(), $e->getCode()); |
| } |
| return $applied; |
| } |
| |
| |
| |
| |
| |
| public function getExamModerationRuleAppliedBatches($request) |
| { |
| $request = $this->realEscapeObject($request); |
| $examRegField = $request->isSupply ? "exam_supplementary_id" : "exam_registration_id"; |
| $condition = ""; |
| $condition .= $request->getAllBatchesForExamRegistration ? "": " AND moderation_rule_id IN($request->ruleId)"; |
| $condition .= $request->excludeSameRuleBatches ? " AND moderation_rule_id NOT IN($request->ruleId)" : ""; |
| $assignedBatches = []; |
| $sql = ""; |
| $sql = "SELECT batchID as batchId from moderation_rule_assign where $examRegField IN($request->examRegId) $condition GROUP BY batchID "; |
| try { |
| $assignedBatches = $this->executeQueryForList($sql); |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getMessage(), $e->getCode()); |
| } |
| return $assignedBatches; |
| } |
| |
| |
| |
| |
| |
| public function getExamModerationMarksAppliedStudents($request) |
| { |
| $request = $this->realEscapeObject($request); |
| $examRegField = $request->isSupply ? "supplyRegId" : "examRegId"; |
| $condition=""; |
| $appliedStudents =[]; |
| $condition .= $request->ruleId ? " AND sm.ruleId IN ($request->ruleId) ":""; |
| $sql = ""; |
| $sql = "SELECT sm.id,e.batchID as batchId,b.batchName,e.subjectID as subjectId,s.subjectName,s.subjectDesc,sa.studentID as studentId,sa.regNo,sa.studentName,e.examID as examId,sm.mark FROM studentExamModerationMarks sm |
| INNER JOIN exam e ON e.examID = sm.examId |
| INNER JOIN studentaccount sa ON sa.studentID = sm.studentId |
| INNER JOIN subjects s ON s.subjectID = e.subjectID |
| INNER JOIN batches b ON b.batchID = e.batchID |
| WHERE sm.$examRegField IN($request->examRegId) $condition"; |
| try { |
| $appliedStudents = $this->executeQueryForList($sql); |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getMessage(), $e->getCode()); |
| } |
| return $appliedStudents; |
| } |
| |
| |
| |
| |
| |
| |
| |
| public function getStudentExamModerationMark($request) |
| { |
| $request = $this->realEscapeObject($request); |
| $sql = ""; |
| $sql = "SELECT examId,studentId,mark FROM studentExamModerationMarks WHERE examId IN ($request->examId) AND studentId IN($request->studentId)"; |
| try { |
| $studentMark = $this->executeQueryForObject($sql); |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getMessage(), $e->getCode()); |
| } |
| return $studentMark; |
| } |
| |
| |
| |
| |
| |
| public function checkRuleModerationMarksApplied($request) |
| { |
| $request = $this->realEscapeObject($request); |
| $examRegField = $request->isSupply ? "supplyRegId" : "examRegId"; |
| $sql = ""; |
| $sql = "SELECT ruleId FROM studentExamModerationMarks WHERE ruleId IN ($request->ruleId) AND $examRegField IN($request->examRegId) GROUP BY ruleId"; |
| try { |
| $rule = $this->executeQueryForObject($sql); |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getMessage(), $e->getCode()); |
| } |
| return $rule; |
| } |
| |
| |
| |
| |
| public function getExamValuationPacketFalseNoOrderByStaff($request) |
| { |
| $request = $this->realEscapeObject($request); |
| $examRegFiled = $request->isSupply ? "supplyRegId" : "examRegId"; |
| $packetNo = $request->facultyDetails->packetNo; |
| if($request->facultyDetails->chiefEvaluatorId && $request->facultyDetails->additionalEvaluatorId){ |
| $staffId = $request->facultyDetails->chiefEvaluatorId.",". $request->facultyDetails->additionalEvaluatorId; |
| } |
| else{ |
| $staffId = $request->facultyDetails->chiefEvaluatorId? $request->facultyDetails->chiefEvaluatorId : $request->facultyDetails->additionalEvaluatorId; |
| } |
| try { |
| $sql ="SELECT falseNoOrder FROM examValuationPacketFalseNoOrder |
| WHERE $examRegFiled = $request->examRegId AND subjectId=$request->subjectId AND valuationCount=$request->valCount AND packetNo='$packetNo' And created_by IN ($staffId)"; |
| $result = $this->executeQueryForList($sql); |
| }catch (\Exception $e) { |
| throw new ProfessionalException($e->getMessage(), $e->getCode()); |
| } |
| return $result; |
| } |
| |
| |
| |
| public function getExamValuationMarkEntryConfirmStatus($request) |
| { |
| $request = $this->realEscapeObject($request); |
| $result=null; |
| $examRegField = $request->isSupply ? "supplyRegId" : "examRegId"; |
| $evaluatorField = $request->isChiefEvaluator ? "chiefEvaluator" : "additionalEvaluator"; |
| $condition =""; |
| if ($request->checkPreviousValuation) { |
| $prevValCount = $request->isChiefEvaluator ? $request->valuationCount : $request->valuationCount - 1; |
| $prevValField = "additionalEvaluator"; |
| if($request->isUg && !$request->isChiefEvaluator){ |
| $prevValField = "chiefEvaluator"; |
| } |
| if($prevValCount){ |
| $sql = "SELECT id,chiefEvaluator,additionalEvaluator from examValuationMarksConfirm |
| WHERE $examRegField='$request->examRegId' AND subjectId='$request->subjectId' AND valuationCount='$prevValCount' AND packetNo='$request->packetNo' AND $prevValField IS NOT NULL"; |
| } |
| } |
| else { |
| if($request->staffId){ |
| $condition = " AND $evaluatorField='$request->staffId' "; |
| } |
| $sql = "SELECT id,chiefEvaluator,additionalEvaluator from examValuationMarksConfirm |
| WHERE $examRegField='$request->examRegId' AND subjectId='$request->subjectId' AND valuationCount='$request->valuationCount' AND packetNo='$request->packetNo' $condition"; |
| } |
| try { |
| $result = $this->executeQueryForObject($sql); |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getMessage(), $e->getCode()); |
| } |
| return $result; |
| } |
| |
| |
| |
| |
| |
| public function getAssignedStaffStudentsCountForSubjectValuation($request) |
| { |
| $request = $this->realEscapeObject($request); |
| $studentCounts = []; |
| $condition = ""; |
| if($request->subjectId){ |
| $condition = " AND erss.subjectID IN($request->subjectId)"; |
| } |
| if($request->staffId){ |
| $condition = " AND evss.staffId IN($request->staffId)"; |
| } |
| if($request->valuationCount){ |
| $condition = " AND evss.valuationCount IN($request->valuationCount)"; |
| } |
| $sql = "SELECT count(distinct(evss.studentId)) as assignedStudentCount,evss.valuationCount,evss.staffId,sa.staffName,evss.subjectId,DATE_FORMAT(evss.valuationEndDate, '%d-%m-%Y') as valuationEndDate FROM exam_reg_studentsubject erss |
| INNER JOIN exam_reg_studentchallan ersc ON ersc.examregID = erss.examregID AND ersc.studentID= erss.studentID |
| INNER JOIN examValuationStaffAssignedStudents evss ON evss.examRegId = erss.examregID AND evss.subjectId = erss.subjectID AND evss.studentId = erss.studentID |
| INNER JOIN staffaccounts sa ON sa.staffID = evss.staffId |
| WHERE erss.examregID IN($request->examRegId) AND ersc.paid=1 AND evss.examType = 'REGULAR' $condition |
| GROUP BY evss.subjectId,evss.valuationCount,evss.staffId ORDER BY evss.valuationCount,evss.staffId"; |
| try { |
| $studentCounts = $this->executeQueryForList($sql); |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getMessage(), $e->getCode()); |
| } |
| return $studentCounts; |
| } |
| |
| |
| |
| |
| |
| public function getStudentPacketNoByRequest($request) |
| { |
| $request = $this->realEscapeObject($request); |
| $sql = ""; |
| $packet=null; |
| $sql = "SELECT evps.packetNo,efn.alpha_numeric_code as alphaNumericCode FROM examValuationStudentPacketsRelation evps |
| INNER JOIN examcontroller_false_number efn ON efn.studentID = evps.studentId AND efn.examID = evps.examId |
| WHERE evps.studentId IN($request->studentId) AND efn.false_number IN ('$request->falseNumber')"; |
| try { |
| $packet = $this->executeQueryForObject($sql); |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getMessage(), $e->getCode()); |
| } |
| return $packet; |
| } |
| |
| |
| |
| |
| |
| |
| public function getMarkConfirmedStaffStudentsCountForSubjectValuation($request) |
| { |
| $request = $this->realEscapeObject($request); |
| $condition = ""; |
| if(!$request->getValuationStarted){ |
| $condition .= " AND oec.is_confirmed = 1"; |
| } |
| if($request->subjectId){ |
| $condition .= " AND erss.subjectID IN($request->subjectId)"; |
| } |
| if($request->staffId){ |
| $condition .= " AND oec.created_by IN($request->staffId)"; |
| } |
| if($request->valuationCount){ |
| $condition .= " AND oec.valuation_count IN($request->valuationCount)"; |
| } |
| $studentCounts = []; |
| $sql = "SELECT count(distinct erss.studentID) as confirmedStudentCount,oec.valuation_count as valuationCount,oec.created_by as staffId,sf.staffName,oec.review_id,erss.subjectID as subjectId |
| 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 |
| LEFT JOIN failed_students fs ON fs.studentID = sa.studentID AND FIND_IN_SET(erb.semID, fs.hisSemestersInThisbatch) |
| INNER JOIN exam e ON e.examregID = erss.examregID AND e.subjectID = erss.subjectID AND e.batchID = IF (fs.previousBatch, fs.previousBatch, 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 oe_exam_marks_confirm oec ON oec.oe_exams_id = oe.id AND oec.oe_users_id = erss.studentID |
| INNER JOIN staffaccounts sf ON sf.staffID = oec.created_by |
| WHERE erss.examregID IN($request->examRegId) AND ersc.paid=1 AND oec.revaluation_id IS NULL AND erb.batchID = e.batchID $condition |
| GROUP BY erss.subjectID,oec.valuation_count,oec.created_by order by oec.valuation_count,oec.created_by"; |
| try { |
| $studentCounts = $this->executeQueryForList($sql); |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getMessage(), $e->getCode()); |
| } |
| return $studentCounts; |
| } |
| |
| |
| |
| |
| public function saveStudentExamAttendanceByRequest($request) |
| { |
| $request = $this->realEscapeObject($request); |
| $studentList = $request->studentList; |
| $sql = ""; |
| $insertValues=[]; |
| foreach($studentList as $student){ |
| $insertValues[]= "('$student->examId','$student->studentId' ,'$student->isAbsent')"; |
| } |
| if (!empty($insertValues)) { |
| $insertValues = implode(",", $insertValues); |
| $sql = "INSERT into exam_attendance(examID,studentID,isAbsent) |
| values $insertValues ON DUPLICATE KEY UPDATE isAbsent = VALUES(isAbsent)"; |
| } |
| if ($sql) { |
| try { |
| $this->executeQueryForObject($sql); |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); |
| } |
| } |
| return true; |
| } |
| |
| |
| |
| |
| |
| public function getExamValuationMarkConfirmedSubjectsByExamRegistration($request) |
| { |
| $request = $this->realEscapeObject($request); |
| $examRegField = $request->isSupply ? "supplyRegId" : "examRegId"; |
| $sql = ""; |
| $subjectList=[]; |
| $sql = "SELECT evmc.subjectId as id,s.subjectName,s.subjectDesc,CONCAT(s.subjectName,' [',s.subjectDesc,']') as name from examValuationMarksConfirm evmc |
| INNER JOIN subjects s ON s.subjectID = evmc.subjectId |
| WHERE evmc.$examRegField IN ($request->examRegId) GROUP BY evmc.subjectId"; |
| try { |
| $subjectList = $this->executeQueryForList($sql); |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getMessage(), $e->getCode()); |
| } |
| return $subjectList; |
| } |
| |
| |
| |
| |
| |
| public function getExamValuationMarkConfirmedFacultyByRequest($request) |
| { |
| $request = $this->realEscapeObject($request); |
| $examRegField = $request->isSupply ? "supplyRegId" : "examRegId"; |
| $condition = $sql = ""; |
| $facultyList = []; |
| if($request->subjectId){ |
| $condition .= " AND evmc.subjectId IN ($request->subjectId)"; |
| } |
| $sql = "SELECT distinct evmc.chiefEvaluator as id,sa.staffName as name from examValuationMarksConfirm evmc |
| INNER JOIN staffaccounts sa ON sa.staffID = evmc.chiefEvaluator |
| WHERE evmc.$examRegField IN ($request->examRegId) AND evmc.chiefEvaluator IS NOT NULL $condition |
| group by evmc.chiefEvaluator |
| UNION |
| SELECT distinct evmc.additionalEvaluator as id ,sa.staffName as name from examValuationMarksConfirm evmc |
| INNER JOIN staffaccounts sa ON sa.staffID = evmc.additionalEvaluator |
| WHERE evmc.$examRegField IN ($request->examRegId) AND evmc.additionalEvaluator IS NOT NULL $condition |
| group by evmc.additionalEvaluator"; |
| try { |
| $facultyList = $this->executeQueryForList($sql); |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getMessage(), $e->getCode()); |
| } |
| return $facultyList; |
| } |
| |
| |
| |
| |
| |
| public function getExamValuationMarkConfirmedStaffPackectsByRequest($request) |
| { |
| $request = $this->realEscapeObject($request); |
| $examRegField = $request->isSupply ? "supplyRegId" : "examRegId"; |
| $condition = $sql = ""; |
| $packetList=[]; |
| if ($request->subjectId) { |
| $condition .= " AND evmc.subjectId IN ($request->subjectId)"; |
| } |
| if ($request->staffId) { |
| $condition .= " AND (evmc.chiefEvaluator IN ($request->staffId) OR evmc.additionalEvaluator IN($request->staffId))"; |
| } |
| $sql = "SELECT evmc.packetNo as id ,evmc.packetNo as name,sa.staffID as staffId,sa.staffName from examValuationMarksConfirm evmc |
| INNER JOIN staffaccounts sa ON (sa.staffID = evmc.chiefEvaluator OR sa.staffID = evmc.additionalEvaluator) |
| WHERE evmc.$examRegField IN ($request->examRegId) |
| $condition |
| GROUP BY evmc.packetNo"; |
| try { |
| $packetList = $this->executeQueryForList($sql); |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getMessage(), $e->getCode()); |
| } |
| return $packetList; |
| } |
| |
| |
| |
| |
| public function getMaxModerationMarkByExamRequest($request) |
| { |
| $request = $this->realEscapeObject($request); |
| $examRegField = $request->isSupply ? "exam_supplementary_id": "exam_registration_id"; |
| $sql = ""; |
| $moderationMark=null; |
| $sql = "SELECT max_mark as maxModerationMark FROM moderation_max_mark_each_sub mmes |
| WHERE mmes.examId IN ($request->examId) AND mmes.$examRegField IN ($request->examRegId)"; |
| try { |
| $moderationMark = $this->executeQueryForObject($sql); |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getMessage(), $e->getCode()); |
| } |
| return $moderationMark; |
| } |
| |
| |
| |
| |
| |
| public function removeExamValuationMarkConfirmedStaffPackectsByRequest($request) |
| { |
| $request = $this->realEscapeObject($request); |
| $examRegField = $request->isSupply ? "supplyRegId" : "examRegId"; |
| $condition = $sql = ""; |
| if ($request->subjectId) { |
| $condition .= " AND subjectId IN ($request->subjectId)"; |
| } |
| if ($request->staffId) { |
| $condition .= " AND (chiefEvaluator IN ($request->staffId) OR additionalEvaluator IN($request->staffId))"; |
| } |
| if ($request->packetNo) { |
| $condition .= " AND packetNo IN ('$request->packetNo')"; |
| } |
| $sql = "DELETE FROM examValuationMarksConfirm |
| WHERE $examRegField IN ($request->examRegId) |
| $condition"; |
| try { |
| $this->executeQueryForObject($sql); |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getMessage(), $e->getCode()); |
| } |
| return true; |
| } |
| |
| |
| |
| |
| public function getExamValuationStaffBySubject($request) |
| { |
| $request = $this->realEscapeObject($request); |
| $condtion=""; |
| if ($request->examType == ExamType::REGULAR) { |
| $condtion = "AND esvs.examRegId IN ($request->examRegId)"; |
| } else if ($request->examType == ExamType::SUPPLY) { |
| $condtion = "AND esvs.supplyExamRegId IN ($request->supplyRegId)"; |
| } |
| $sql = ""; |
| $assignedStaff = []; |
| try { |
| if($request->examRegId && $request->semId && $request->subjectId){ |
| $sql = "SELECT DISTINCT esvs.staffId,sa.staffName FROM exam_examReg_subject_valuation_staff esvs |
| INNER JOIN staffaccounts sa ON sa.staffID = esvs.staffId |
| WHERE esvs.semId IN($request->semId) AND esvs.subjectId IN ($request->subjectId) |
| $condtion"; |
| |
| $assignedStaff = $this->executeQueryForList($sql); |
| } |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getMessage(), $e->getCode()); |
| } |
| return $assignedStaff; |
| } |
| |
| |
| |
| |
| |
| public function getExamMarkEntryAdminPrivileges() |
| { |
| $sql = ""; |
| $adminPrivileges = []; |
| $sql = "SELECT exa.adminID as id,exa.adminName as name,emp.externalMarkView,emp.externalMarkEdit,emp.internalMarkView,emp.internalMarkEdit,emp.sessionalMarkView,emp.sessionalMarkEdit |
| FROM examcontroller_admin exa |
| LEFT JOIN examMarkEntryAdminPrivileges emp ON emp.adminId = exa.adminID"; |
| try { |
| $adminPrivileges = $this->executeQueryForList($sql); |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getMessage(), $e->getCode()); |
| } |
| return $adminPrivileges; |
| } |
| |
| |
| |
| |
| public function saveExamMarkEntryAdminPrivileges($request) |
| { |
| $sql = ""; |
| $request = $this->realEscapeObject($request); |
| $admin = $request->adminPrivilege; |
| if($admin->extPrivilege == 2){ |
| $admin->externalMarkView = $admin->externalMarkEdit = 1; |
| } |
| else if ($admin->extPrivilege == 1) { |
| $admin->externalMarkView = 1; |
| $admin->externalMarkEdit = 0; |
| } |
| if ($admin->intPrivilege == 2) { |
| $admin->internalMarkView = $admin->internalMarkEdit = 1; |
| } else if ($admin->intPrivilege == 1) { |
| $admin->internalMarkView = 1; |
| $admin->internalMarkEdit = 0; |
| } |
| |
| if ($admin->sesPrivilege == 2) { |
| $admin->sessionalMarkView = $admin->sessionalMarkEdit = 1; |
| } else if ($admin->sesPrivilege == 1) { |
| $admin->sessionalMarkView = 1; |
| $admin->sessionalMarkEdit = 0; |
| } |
| |
| $createdBy = $_SESSION['adminID']; |
| $sql = "INSERT INTO examMarkEntryAdminPrivileges(adminId,externalMarkView,externalMarkEdit,internalMarkView,internalMarkEdit,sessionalMarkView,sessionalMarkEdit,created_by) |
| VALUES($admin->id,$admin->externalMarkView,$admin->externalMarkEdit,$admin->internalMarkView,$admin->internalMarkEdit,$admin->sessionalMarkView,$admin->sessionalMarkEdit,$createdBy) |
| ON DUPLICATE KEY UPDATE externalMarkView = VALUES(externalMarkView), |
| externalMarkEdit = VALUES(externalMarkEdit), |
| internalMarkView = VALUES(internalMarkView), |
| internalMarkEdit = VALUES(internalMarkEdit), |
| sessionalMarkView = VALUES(sessionalMarkView), |
| sessionalMarkEdit = VALUES(sessionalMarkEdit), |
| updated_by = VALUES(created_by), |
| updated_date = VALUES(updated_date)"; |
| try { |
| $this->executeQueryForObject($sql); |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getMessage(), $e->getCode()); |
| } |
| return true; |
| } |
| |
| |
| |
| |
| public function getExamMarkEntryAdminEditPrivilege($request) |
| { |
| $request = $this->realEscapeObject($request); |
| $sql = ""; |
| $editEnabled = false; |
| try { |
| if($request->adminId){ |
| $sql = "SELECT exa.adminID as id,exa.adminName as name,emp.externalMarkEdit as externalMarkEditEnabled,emp.internalMarkEdit as internalMarkEditEnabled,emp.sessionalMarkEdit as sessionalMarkEditEnabled |
| FROM examcontroller_admin exa |
| LEFT JOIN examMarkEntryAdminPrivileges emp ON emp.adminId = exa.adminID |
| WHERE exa.adminID = '$request->adminId'"; |
| $editEnabled = $this->executeQueryForObject($sql); |
| if ($request->isExternal && $editEnabled) { |
| return $editEnabled->externalMarkEditEnabled ? true : false; |
| } else if ($request->isInternal && $editEnabled) { |
| return $editEnabled->internalMarkEditEnabled ? true : false; |
| } else if ($request->isSessional && $editEnabled) { |
| return $editEnabled->sessionalMarkEditEnabled ? true : false; |
| } |
| else { |
| return false; |
| } |
| } |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getMessage(), $e->getCode()); |
| } |
| return false; |
| } |
| |
| |
| |
| |
| |
| public function getDistinctExamsByExamRegistrationRequest($request) |
| { |
| $request = $this->realEscapeObject($request); |
| $whereConditions = ""; |
| if ($request->semId) { |
| $whereConditions .= " AND e.semID IN ($request->semId)"; |
| } |
| if ($request->batchId) { |
| $whereConditions .= " AND e.batchID IN ($request->batchId)"; |
| } |
| if($request->studentId){ |
| $whereConditions .= " AND ersd.studentID IN ($request->studentId) AND ersd.paid = 1 "; |
| } |
| if($request->examType == ExamType::SUPPLY){ |
| $sql = "SELECT |
| e.subjectID as 'subjectId', |
| s.subjectName, |
| s.subjectDesc, |
| e.semID as 'semId', |
| e.examID as 'examId', |
| e.batchID as 'batchId' |
| FROM |
| exam_supplementary_student_details ersd |
| INNER JOIN exam_supplementary_student_subjects erss ON erss.exam_supplementary_id = ersd.exam_supplementary_id AND erss.studentID = ersd.studentID |
| INNER JOIN exam ex ON ex.examID = erss.examID |
| INNER JOIN exam e ON e.supply_examreg_id = ersd.exam_supplementary_id AND e.subjectID = ex.subjectID |
| INNER JOIN subjects s ON s.subjectID = e.subjectID |
| WHERE |
| 1 = 1 |
| AND ersd.exam_supplementary_id IN ($request->examRegId) |
| $whereConditions |
| group by e.examID"; |
| } |
| else if($request->examType == ExamType::REGULAR){ |
| $sql= "SELECT distinct |
| e.subjectID as 'subjectId', |
| s.subjectName, |
| s.subjectDesc, |
| e.semID as 'semId', |
| e.examID as 'examId', |
| e.batchID as 'batchId' |
| FROM |
| exam e |
| INNER JOIN subjects s ON s.subjectID = e.subjectID |
| INNER JOIN exam_reg_studentchallan ersd ON ersd.examregID = e.examregID |
| INNER JOIN exam_reg_studentsubject erss ON erss.examregID = e.examregID AND erss.subjectID = e.subjectID |
| WHERE |
| 1 = 1 |
| AND e.examregID IN ($request->examRegId) |
| $whereConditions |
| group by e.examID"; |
| } |
| |
| try { |
| $exams = $this->executeQueryForList($sql); |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getMessage(), $e->getCode()); |
| } |
| return $exams; |
| } |
| |
| |
| |
| |
| |
| |
| public function getStudentAlphaNumericCodeNoByRequest($request) |
| { |
| $request = $this->realEscapeObject($request); |
| $sql = ""; |
| $alphaNumericCode = null; |
| $sql = "SELECT efn.alpha_numeric_code as alphaNumericCode |
| FROM examcontroller_false_number efn |
| WHERE efn.studentID IN($request->studentId) AND efn.false_number IN ('$request->falseNumber')"; |
| try { |
| $alphaNumericCode = $this->executeQueryForObject($sql); |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getMessage(), $e->getCode()); |
| } |
| return $alphaNumericCode; |
| } |
| |
| |
| |
| |
| |
| |
| public function getAllExamRegisteredStudentsForSubject ( $examRegistrationSubjectRequest ) { |
| $sql = null; |
| $examRegistrationSubjectRequest = $this->realEscapeObject ( $examRegistrationSubjectRequest ); |
| $studentDetails = []; |
| try { |
| if ($examRegistrationSubjectRequest->examType == ExamType::REGULAR ) { |
| $sql = "SELECT sa.studentID, sa.regNo, sa.batchID, e.examID, e.examTotalMarks FROM exam_reg_studentsubject ers INNER JOIN studentaccount sa ON (ers.studentID = sa.studentID) INNER JOIN exam e ON (e.subjectID = ers.subjectID AND e.examregID = ers.examRegId) INNER JOIN exam_reg_studentchallan ersc ON (ersc.studentID = ers.studentID AND ersc.examregID = ers.examregID AND ersc.paid = 1) LEFT JOIN failed_students fs ON fs.studentID = sa.studentID AND FIND_IN_SET(e.semID, fs.hisSemestersInThisbatch) WHERE e.semID = '$examRegistrationSubjectRequest->semId' AND ers.examRegId = '$examRegistrationSubjectRequest->examRegId' AND ers.subjectID = '$examRegistrationSubjectRequest->subjectId' |
| AND e.batchID = IF (fs.previousBatch, fs.previousBatch, sa.batchID) GROUP BY ers.studentID "; |
| } |
| else if ($examRegistrationSubjectRequest->examType == ExamType::SUPPLY ) { |
| $sql = "SELECT sa.studentID, sa.regNo, sa.batchID, e.examID, e.examTotalMarks FROM exam e INNER JOIN exam e1 ON (e.subjectID = e1.subjectID AND e.semID = e1.semID AND e.batchID = e1.batchID AND e1.supply_examreg_id IS NULL ) INNER JOIN exam_supplementary_student_subjects esss ON (esss.examID = e1.examID AND esss.exam_supplementary_id = e.supply_examreg_id) INNER JOIN studentaccount sa ON (sa.studentID = esss.studentID) WHERE e.supply_examreg_id = '$examRegistrationSubjectRequest->examRegId' AND e.subjectID = '$examRegistrationSubjectRequest->subjectId' |
| GROUP BY esss.studentID "; |
| } |
| $studentDetails = $this->executeQueryForList($sql); |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); |
| } |
| return $studentDetails; |
| } |
| |
| |
| |
| |
| |
| public function getSupplyExamSubjectRegisteredStudentCount($request) |
| { |
| $request = $this->realEscapeObject($request); |
| $studentCounts = []; |
| $sql = "SELECT count(distinct ersd.studentID) as totalStudentCount FROM exam_supplementary_student_details ersd |
| INNER JOIN exam_supplementary_student_subjects erss ON erss.exam_supplementary_id = ersd.exam_supplementary_id AND erss.studentID = ersd.studentID |
| INNER JOIN exam e ON e.examID = erss.examID |
| WHERE ersd.exam_supplementary_id IN ($request->examRegId) AND e.subjectID IN ($request->subjectId) AND ersd.paid=1 "; |
| try { |
| $studentCounts = $this->executeQueryForObject($sql); |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getMessage(), $e->getCode()); |
| } |
| return $studentCounts; |
| } |
| |
| |
| |
| |
| |
| |
| public function getAssignedSupplyStudentsCountForSubjectValuation($request) |
| { |
| $request = $this->realEscapeObject($request); |
| $studentCounts = []; |
| $sql = "SELECT count(distinct(evss.studentId)) as assignedStudentCount,evss.valuationCount FROM exam_supplementary_student_subjects erss |
| INNER JOIN exam_supplementary_student_details ersd ON ersd.exam_supplementary_id = erss.exam_supplementary_id AND ersd.studentID= erss.studentID |
| INNER JOIN exam e ON e.examID = erss.examID |
| INNER JOIN examValuationStaffAssignedStudents evss ON evss.examRegId = erss.exam_supplementary_id AND evss.subjectId = e.subjectID AND evss.studentId = erss.studentID |
| WHERE erss.exam_supplementary_id IN($request->examRegId) AND e.subjectID IN($request->subjectId) AND ersd.paid=1 AND evss.examType = 'SUPPLY' |
| group by evss.valuationCount ORDER BY evss.valuationCount"; |
| try { |
| $studentCounts = $this->executeQueryForList($sql); |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getMessage(), $e->getCode()); |
| } |
| return $studentCounts; |
| } |
| |
| |
| |
| |
| |
| public function getMarkConfirmedSupplyStudentsCountForSubjectValuation($request) |
| { |
| $request = $this->realEscapeObject($request); |
| $studentCounts = []; |
| $condition = ""; |
| if(!$request->getValuationStarted){ |
| $condition .= " AND oec.is_confirmed = 1"; |
| } |
| $sql = "SELECT count(distinct erss.studentID) as confirmedStudentCount,oec.valuation_count as valuationCount |
| FROM exam_supplementary_student_subjects erss |
| INNER JOIN exam_supplementary_student_details ersd ON ersd.studentID = erss.studentID AND ersd.exam_supplementary_id = erss.exam_supplementary_id |
| INNER JOIN studentaccount sa ON sa.studentID = erss.studentID |
| INNER JOIN exam ex ON ex.examID = erss.examID |
| INNER JOIN supply_improve_batches erb ON erb.exam_supplementary_id = erss.exam_supplementary_id AND erb.batchID = ex.batchID |
| INNER JOIN exam e ON e.supply_examreg_id = erss.exam_supplementary_id AND e.subjectID = ex.subjectID AND e.batchID = ex.batchID AND e.semID = ex.semID |
| 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 = erss.studentID |
| WHERE erss.exam_supplementary_id IN($request->examRegId) AND e.subjectID IN($request->subjectId) AND ersd.paid=1 AND oec.revaluation_id IS NULL $condition |
| group by oec.valuation_count order by oec.valuation_count,sa.regNo"; |
| try { |
| $studentCounts = $this->executeQueryForList($sql); |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getMessage(), $e->getCode()); |
| } |
| return $studentCounts; |
| } |
| |
| |
| |
| |
| |
| |
| public function getAssignedSupplyStaffStudentsCountForSubjectValuation($request) |
| { |
| $request = $this->realEscapeObject($request); |
| $condition = ""; |
| if($request->subjectId){ |
| $condition = " AND e.subjectID IN($request->subjectId)"; |
| } |
| if($request->staffId){ |
| $condition = " AND evss.staffId IN($request->staffId)"; |
| } |
| $studentCounts = []; |
| $sql = "SELECT count(distinct(evss.studentId)) as assignedStudentCount,evss.valuationCount,evss.staffId,sa.staffName,evss.subjectId,DATE_FORMAT(evss.valuationEndDate, '%d-%m-%Y') as valuationEndDate |
| FROM exam_supplementary_student_subjects erss |
| INNER JOIN exam_supplementary_student_details ersd ON ersd.exam_supplementary_id = erss.exam_supplementary_id AND ersd.studentID= erss.studentID |
| INNER JOIN exam e ON e.examID = erss.examID |
| INNER JOIN examValuationStaffAssignedStudents evss ON evss.examRegId = erss.exam_supplementary_id AND evss.subjectId = e.subjectID AND evss.studentId = erss.studentID |
| INNER JOIN staffaccounts sa ON sa.staffID = evss.staffId |
| WHERE erss.exam_supplementary_id IN($request->examRegId) AND ersd.paid=1 AND evss.examType = 'SUPPLY' $condition |
| GROUP BY evss.subjectId,evss.valuationCount,evss.staffId ORDER BY evss.valuationCount,evss.staffId"; |
| try { |
| $studentCounts = $this->executeQueryForList($sql); |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getMessage(), $e->getCode()); |
| } |
| return $studentCounts; |
| } |
| |
| |
| |
| |
| |
| public function getMarkConfirmedSupplyStaffStudentsCountForSubjectValuation($request) |
| { |
| $request = $this->realEscapeObject($request); |
| $condition = ""; |
| if(!$request->getValuationStarted){ |
| $condition .= " AND oec.is_confirmed = 1"; |
| } |
| if($request->subjectId){ |
| $condition = " AND e.subjectID IN($request->subjectId)"; |
| } |
| if($request->staffId){ |
| $condition = " AND oec.created_by IN($request->staffId)"; |
| } |
| $studentCounts = []; |
| $sql = "SELECT count(distinct erss.studentID) as confirmedStudentCount,oec.valuation_count as valuationCount,oec.created_by as staffId,sf.staffName,oec.review_id,e.subjectID as subjectId |
| FROM exam_supplementary_student_subjects erss |
| INNER JOIN exam_supplementary_student_details ersd ON ersd.exam_supplementary_id = erss.exam_supplementary_id AND ersd.studentID= erss.studentID |
| INNER JOIN studentaccount sa ON sa.studentID = erss.studentID |
| INNER JOIN exam ex ON ex.examID = erss.examID |
| INNER JOIN supply_improve_batches erb ON erb.exam_supplementary_id = erss.exam_supplementary_id AND erb.batchID = ex.batchID |
| INNER JOIN exam e ON e.supply_examreg_id = erss.exam_supplementary_id AND e.subjectID = ex.subjectID AND e.batchID = ex.batchID AND e.semID = ex.semID |
| 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 = erss.studentID |
| INNER JOIN staffaccounts sf ON sf.staffID = oec.created_by |
| WHERE erss.exam_supplementary_id IN($request->examRegId) AND ersd.paid=1 AND oec.revaluation_id IS NULL $condition |
| GROUP BY e.subjectID,oec.valuation_count,oec.created_by order by oec.valuation_count,oec.created_by"; |
| try { |
| $studentCounts = $this->executeQueryForList($sql); |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getMessage(), $e->getCode()); |
| } |
| return $studentCounts; |
| } |
| |
| |
| |
| |
| |
| |
| public function getStudentFalseNumberExamAttendanceStatus($request) |
| { |
| $request = $this->realEscapeObject($request); |
| $studentList = []; |
| $sql = "SELECT efn.false_number as falseNumber,ex.isAbsent FROM examcontroller_false_number efn |
| INNER JOIN exam_attendance ex ON ex.examID = efn.examID AND ex.studentID = efn.studentID |
| WHERE efn.examID IN ($request->examId) |
| ORDER BY efn.false_number"; |
| try { |
| $studentList = $this->executeQueryForList($sql); |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getMessage(), $e->getCode()); |
| } |
| return $studentList; |
| } |
| |
| |
| |
| |
| |
| public function saveExamValuationStudentMarksInStudentMarks($studentMarks) |
| { |
| $studentMarks = $this->realEscapeArray($studentMarks); |
| $values = []; |
| $exempted = []; |
| $absentees = []; |
| $deleteExempted = []; |
| $sql = null; |
| foreach ($studentMarks["markDetails"] as $studentMark) { |
| $studentMark['mark'] = strtoupper($studentMark['mark']); |
| if ($studentMark['mark'] == "AB") { |
| $absentees[] = " ( '" . $studentMark['examId'] . "', '" . $studentMark['studentId'] . "', '1' ) "; |
| $deleteExempted[] = " ( exam_id = '" . $studentMark['examId'] . "' AND studentaccount_id = '" . $studentMark['studentId'] . "' ) "; |
| } else if ($studentMark['mark'] == "WH") { |
| $absentees[] = " ( '" . $studentMark['examId'] . "', '" . $studentMark['studentId'] . "', '3' ) "; |
| $deleteExempted[] = " ( exam_id = '" . $studentMark['examId'] . "' AND studentaccount_id = '" . $studentMark['studentId'] . "' ) "; |
| } else if ($studentMark['mark'] == "EX") { |
| $exempted[] = " ( '" . $studentMark['examId'] . "', '" . $studentMark['studentId'] . "', '" . $studentMarks['adminID'] . "', utc_timestamp(), '" . $studentMarks['adminID'] . "', utc_timestamp() ) "; |
| } else { |
| $values[] = " ('" . $studentMark['examId'] . "','" . $studentMark['studentId'] . "', '" . $studentMark['mark'] . "', '" . $studentMarks['adminID'] . "', '" . $studentMarks['valuationCount'] . "','" . $studentMarks['adminID'] . "') "; |
| $absentees[] = " ( '" . $studentMark['examId'] . "', '" . $studentMark['studentId'] . "', '0' ) "; |
| $deleteExempted[] = " ( exam_id = '" . $studentMark['examId'] . "' AND studentaccount_id = '" . $studentMark['studentId'] . "' ) "; |
| } |
| } |
| try { |
| if (!empty($values)) { |
| $sql = "INSERT INTO studentLabExamValuationMarks(examId,studentId,mark,staffId,valuationCount,created_by) VALUES " . implode(",", $values) . " ON DUPLICATE KEY UPDATE mark = VALUES(mark),updated_by = VALUES(staffId)"; |
| $this->executeQuery($sql); |
| } |
| if (!empty($absentees)) { |
| $sql_absent = "INSERT INTO exam_attendance ( examID, studentID, isAbsent ) VALUES " . implode(",", $absentees) . " ON DUPLICATE KEY UPDATE isAbsent = VALUES(isAbsent)"; |
| $this->executeQuery($sql_absent); |
| } |
| if (!empty($exempted)) { |
| $sql_exempted = "INSERT IGNORE INTO exam_exempted_students ( exam_id, studentaccount_id, createdBy, createdDate, updatedBy, updatedDate ) VALUES " . implode(",", $exempted); |
| $this->executeQuery($sql_exempted); |
| } |
| if (!empty($deleteExempted)) { |
| $sql_deleteExempted = "DELETE FROM exam_exempted_students WHERE " . implode(" OR ", $deleteExempted); |
| $this->executeQuery($sql_deleteExempted); |
| } |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); |
| } |
| return true; |
| } |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| public function getStudentExternalMarksWithMaxMark($request) |
| { |
| $sql = ''; |
| $request = $this->realEscapeObject($request); |
| $studentExternalMark = null; |
| $request->valuationCount = $request->valuationCount ? $request->valuationCount : 1; |
| try { |
| $sql = "SELECT ee.mark AS studentExternalMark, e.examTotalMarks AS examTotalMark |
| FROM exam e |
| LEFT JOIN studentLabExamValuationMarks ee ON ee.examId = e.examID AND ee.studentId = '$request->studentId' AND ee.valuationCount = '$request->valuationCount' |
| WHERE e.examID ='$request->examId'"; |
| $studentExternalMark = $this->executeQueryForObject($sql); |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); |
| } |
| return $studentExternalMark; |
| } |
| |
| |
| |
| |
| |
| |
| |
| |
| public function getMarksEnteredStudentsByStaffRequest($request) |
| { |
| $sql = ''; |
| $request = $this->realEscapeObject($request); |
| $studentExternalMark = []; |
| $externalMarkTable = $request->isPg ? "externalexammarks_finalized" : "exammarks_external"; |
| $condition = ""; |
| if($request->staffId){ |
| $condition .= " AND ee.created_by = '$request->staffId'"; |
| } |
| if ($request->examIds) { |
| $condition .= " AND ee.examID IN($request->examIds)"; |
| } |
| |
| try { |
| $sql = "SELECT ee.studentID,ee.mark,ee.examID FROM $externalMarkTable ee |
| WHERE 1 = 1 |
| $condition"; |
| $studentExternalMark = $this->executeQueryForList($sql); |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); |
| } |
| return $studentExternalMark; |
| } |
| |
| |
| |
| |
| |
| public function getExamMarkConfirmedStaffByExam($request) |
| { |
| $request = $this->realEscapeObject($request); |
| $externalMarkTable = $request->isPg ? "externalexammarks_finalized" : "exammarks_external"; |
| $staffList = []; |
| $sql = "SELECT DISTINCT(ee.created_by) as staffId,sa.staffName,1 as isConfirmed FROM $externalMarkTable ee |
| INNER JOIN staffaccounts sa ON sa.staffID = ee.created_by |
| WHERE ee.examID IN ($request->examIds) |
| GROUP BY ee.created_by"; |
| try { |
| $staffList = $this->executeQueryForList($sql); |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getMessage(), $e->getCode()); |
| } |
| return $staffList; |
| } |
| |
| |
| |
| |
| |
| public function removeConfirmedExamMarkByStaff($request) |
| { |
| $request = $this->realEscapeObject($request); |
| $externalMarkTable = $request->isPg ? "externalexammarks_finalized" : "exammarks_external"; |
| $sql = "DELETE FROM $externalMarkTable |
| WHERE examID IN ($request->examIds) AND created_by IN ($request->staffId)"; |
| try { |
| $this->executeQueryForObject($sql); |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getMessage(), $e->getCode()); |
| } |
| return true; |
| } |
| |
| |
| |
| |
| public function deleteStudentModerationMarkDetails($request) |
| { |
| $request = $this->realEscapeObject($request); |
| $result = null; |
| $examMarkTable = " externalexammarks_finalized "; |
| if ($request->courseType == CourseTypeConstants::UG || $request->courseType == CourseTypeConstants::UG_PRO || $request->courseType == CourseTypeConstants::BPED) { |
| $examMarkTable = " exammarks_external "; |
| } |
| $subject = (object) $request->subject; |
| if($subject->revaluationMark && is_numeric($subject->revaluationMark )){ |
| $updateSql = "UPDATE revaluation_marks_finalized set mark='$subject->exam_revaluation_mark' WHERE studentID= '$subject->studentID' AND examID= '$subject->examID'"; |
| } |
| else{ |
| $updateSql = "UPDATE $examMarkTable set mark='$subject->oldMark' WHERE studentID= '$subject->studentID' AND examID= '$subject->examID'"; |
| } |
| $this->executeQuery($updateSql); |
| $sql = "DELETE FROM student_mark_before_moderation WHERE studentID= '$subject->studentID' AND examID= '$subject->examID'"; |
| |
| try { |
| $result = $this->executeQuery($sql); |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); |
| } |
| return $result; |
| } |
| |
| |
| |
| |
| |
| public function deleteStudentExamMarkForPgAndUg($examId,$studentId,$isPg) |
| { |
| if($examId && $studentId){ |
| if ($isPg) { |
| $sql = "DELETE FROM externalexammarks_finalized WHERE examID = '$examId' AND studentID='$studentId'"; |
| } else { |
| $sql = "DELETE FROM exammarks_external WHERE examID = '$examId' AND studentID='$studentId'"; |
| } |
| try { |
| $this->executeQueryForObject($sql); |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getMessage(), $e->getCode()); |
| } |
| } |
| return true; |
| } |
| |
| |
| |
| |
| |
| public function assignStudentGuideRelation($request) |
| { |
| $request = $this->realEscapeObject($request); |
| if($request->isAssignFaculty){ |
| $sql = "UPDATE student_guide_relation set facultyId='$request->facultyId' WHERE studentId = '$request->studentId' AND type = '$request->type'"; |
| }else{ |
| $sql = "INSERT INTO student_guide_relation(studentId,staffId,type,created_by) VALUES($request->studentId,$request->staffId,'$request->type',$request->adminId) |
| ON DUPLICATE KEY UPDATE staffId = VALUES (staffId), updated_date = VALUES (updated_date),updated_by = VALUES(created_by)"; |
| } |
| try { |
| $this->executeQueryForObject($sql); |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getMessage(), $e->getCode()); |
| } |
| return true; |
| } |
| |
| |
| public function getStudentsWithGideDetailsByBatch($request) |
| { |
| $sql = $condition = $getReportApprovedStudentsOnly = $additionalCreditFields = $additionalCreditJoin = ""; |
| $studentArray = []; |
| $request = $this->realEscapeObject($request); |
| if($request->batchId){ |
| $condition .=" AND sa.batchID IN ($request->batchId)"; |
| } |
| if ($request->studentId) { |
| $condition .= " AND sa.studentID IN ($request->studentId) "; |
| } |
| if($request->staffId){ |
| $condition .= " AND sgr.staffId IN ($request->staffId) "; |
| } |
| if ($request->facultyId) { |
| $condition .= " AND FIND_IN_SET($request->facultyId,sgr.facultyId)"; |
| } |
| if($request->isAssignFaculty){ |
| $condition .= " AND sgr.staffId IS NOT NULL "; |
| $getReportApprovedStudentsOnly = " INNER JOIN ec_certificate_upload ecu ON ecu.studentID = sa.studentID AND ecu.status=1 AND ecu.certificate_type = '$request->reportType' "; |
| } |
| if($request->getAdditionalCredits){ |
| $additionalCreditFields = " , sac.additional_credit as credit ,sac.activity "; |
| $additionalCreditJoin = " LEFT JOIN semester_additional_credit sac ON sac.student_id = sa.studentID"; |
| } |
| try { |
| $sql = "SELECT sa.studentID, |
| sa.studentName, |
| sa.rollNo, |
| sa.regNo, |
| sa.admissionNo , |
| sa.myImage , |
| sa.studentSignImage , |
| b.batchName , |
| sgr.staffId, |
| DATE_FORMAT (sgr.startDate, '%d-%m-%Y') as startDate, |
| DATE_FORMAT (sgr. endDate, '%d-%m-%Y') as endDate, |
| sgr.facultyId, |
| st.staffName, |
| b.final_semester as finalSem |
| $additionalCreditFields |
| from studentaccount sa |
| inner join batches b |
| on b.batchID = sa.batchID |
| LEFT JOIN student_guide_relation sgr ON sgr.studentId = sa.studentID AND sgr.type ='$request->type' |
| LEFT JOIN staffaccounts st ON st.staffID = sgr.staffId |
| $getReportApprovedStudentsOnly |
| $additionalCreditJoin |
| where 1 = 1 $condition order by sa.regNo,sa.rollNo"; |
| $studentArray = $this->executeQueryForList($sql); |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); |
| } |
| return $studentArray; |
| } |
| |
| |
| |
| |
| |
| |
| public function assignStudentGuideDates($request) |
| { |
| $request = $this->realEscapeObject($request); |
| $insertValues =[]; |
| foreach($request->studentList as $student){ |
| if($student->staffId){ |
| $insertValues[] = "($student->studentID,'$request->type','$request->startDate','$request->endDate',$request->adminId)"; |
| } |
| } |
| if(!empty($insertValues)){ |
| $values = implode(",", $insertValues); |
| $sql = "INSERT INTO student_guide_relation(studentId,type,startDate,endDate,created_by) VALUES $values |
| ON DUPLICATE KEY UPDATE startDate = VALUES (startDate), endDate = VALUES (endDate), updated_date = VALUES (updated_date),updated_by = VALUES(created_by)"; |
| try { |
| $this->executeQueryForObject($sql); |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getMessage(), $e->getCode()); |
| } |
| } |
| return true; |
| } |
| |
| |
| |
| |
| |
| public function getRolesForStaffInStudentProject() |
| { |
| $staffId = $_SESSION['staffID']; |
| $result = new \stdClass(); |
| $result->isVivafaculty = $result->isGuide = false; |
| if ($staffId) { |
| $sql = "SELECT id FROM student_guide_relation WHERE staffId = $staffId"; |
| $sqlViva = "SELECT id FROM student_guide_relation WHERE FIND_IN_SET($staffId,facultyId)"; |
| try { |
| $guideList = $this->executeQueryForList($sql); |
| if(!empty($guideList)){ |
| $result->isGuide = true; |
| } |
| $vivaList = $this->executeQueryForList($sqlViva); |
| if(!empty($vivaList)){ |
| $result->isVivafaculty = true; |
| } |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getMessage(), $e->getCode()); |
| } |
| } |
| return $result; |
| } |
| |
| |
| |
| |
| |
| public function deleteStudentGuideRelation($request) |
| { |
| $request = $this->realEscapeObject($request); |
| try { |
| |
| if ($request->isAssignFaculty) { |
| $sql = "UPDATE student_guide_relation set facultyId=null WHERE studentId = '$request->studentId' AND type = '$request->type'"; |
| } |
| else{ |
| $sql = "DELETE FROM student_guide_relation WHERE studentId = '$request->studentId' AND type = '$request->type'"; |
| } |
| $this->executeQueryForObject($sql); |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getMessage(), $e->getCode()); |
| } |
| return true; |
| } |
| |
| |
| |
| |
| |
| |
| public function saveStudentAdditionalActivityCredits($request) |
| { |
| $request = $this->realEscapeObject($request); |
| $staffId = $_SESSION['adminID']; |
| $sql = "SELECT id, additional_credit from semester_additional_credit WHERE student_id='$request->studentId' AND semester_id = '$request->semId'"; |
| $result = $this->executeQueryForObject($sql); |
| if ($result) { |
| $sql = "UPDATE semester_additional_credit SET additional_credit ='$request->credit',activity = '$request->activity', updated_by='$staffId' WHERE student_id='$request->studentId' AND semester_id = '$request->semId'"; |
| } else { |
| $sql = "INSERT into semester_additional_credit (semester_id, student_id , additional_credit, created_by,activity) values ('$request->semId','$request->studentId','$request->credit','$staffId','$request->activity')"; |
| } |
| $this->executeQuery($sql); |
| return; |
| } |
| |
| |
| |
| |
| |
| |
| public function getStudentAdditionalActivityCredits($request) |
| { |
| $request = $this->realEscapeObject($request); |
| $condition=""; |
| if($request->semId){ |
| $condition .= " AND semester_id IN ($request->semId)"; |
| } |
| $sql = "SELECT id, additional_credit as credit,activity from semester_additional_credit WHERE student_id='$request->studentId' $condition"; |
| $result = $this->executeQueryForObject($sql); |
| return $result; |
| } |
| |
| |
| |
| |
| |
| public function getExamSubjectRegisteredStudentsByStudentGuideRelation($request) |
| { |
| $request = $this->realEscapeObject($request); |
| $students = []; |
| $studentMarksTable = "exammarks_external"; |
| $request->valuationCount = $request->valuationCount ? $request->valuationCount : 1; |
| if($request->isPg){ |
| $studentMarksTable = "externalexammarks_finalized"; |
| } |
| $valuationCount = $request->valuationCount ? " AND exm.valuationCount IN ($request->valuationCount) " :""; |
| $sql = "SELECT ersc.studentID as studentId,sa.regNo,sa.studentName,e.examID as examId,e.batchID as batchId,e.examTotalMarks,ee.mark as finalMark,ea.isAbsent,exm.mark FROM exam_reg_studentchallan ersc |
| INNER JOIN exam_reg_studentsubject erss ON erss.examregID = ersc.examregID AND erss.studentID = ersc.studentID |
| INNER JOIN studentaccount sa ON sa.studentID = ersc.studentID |
| INNER JOIN batches b ON b.batchID = sa.batchID |
| INNER JOIN exam e ON e.examregID = erss.examregID AND e.batchID = b.batchID AND e.subjectID = erss.subjectID |
| INNER JOIN student_guide_relation sgr ON sgr.studentId = ersc.studentID |
| LEFT JOIN $studentMarksTable ee ON ee.examID= e.examID AND ee.studentID = ersc.studentID |
| LEFT JOIN studentLabExamValuationMarks exm ON exm.examId = e.examID AND exm.studentId = ersc.studentID $valuationCount |
| LEFT JOIN exam_attendance ea ON ea.studentID = ersc.studentID AND ea.examID = e.examID |
| WHERE erss.examregID IN ($request->examRegId) AND erss.subjectID IN ($request->subjectId) |
| AND sgr.facultyId IN ($request->staffId) |
| AND ersc.paid=1 GROUP BY ersc.studentID ORDER BY sa.regNo"; |
| try { |
| $students = $this->executeQueryForList($sql); |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getMessage(), $e->getCode()); |
| } |
| return $students; |
| } |
| |
| |
| |
| |
| |
| |
| public function getStudentConsolidatedResultDetails($request) |
| { |
| $request = $this->realEscapeObject($request); |
| $condition = ""; |
| $students=[]; |
| if ($request->batchStartYear) { |
| $condition .= " AND b.batchStartYear IN ($request->batchStartYear) "; |
| } |
| if ($request->courseTypeId) { |
| $condition .= " AND b.courseTypeID IN ($request->courseTypeId) "; |
| } |
| if ($request->batchId) { |
| $condition .= " AND b.batchID IN ($request->batchId) "; |
| } |
| if ($request->studentId) { |
| $condition .= " AND sa.studentID IN ($request->studentId) "; |
| } |
| $sql = "SELECT sa.studentID,sa.regNo,sa.studentName,UPPER(sa.studentGender) as studentGender,b.batchID as batchId,b.batchName,ecmd.percentage,ecmd.no_of_arrears,ecmd.status,ecmd.cgpa FROM ec_course_consolidated_mark_details ecmd |
| INNER JOIN studentaccount sa ON sa.studentID = ecmd.student_id |
| INNER JOIN batches b ON b.batchID = sa.batchID |
| WHERE ecmd.no_of_arrears = 0 $condition"; |
| try { |
| $students = $this->executeQueryForList($sql); |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getMessage(), $e->getCode()); |
| } |
| return $students; |
| } |
| |
| |
| |
| |
| |
| public function getStudentAdditionalCredits($request) |
| { |
| $request = $this->realEscapeObject($request); |
| $condition = ""; |
| if ($request->semId) { |
| $condition .= " AND semester_id IN ($request->semId)"; |
| } |
| if ($request->creditType) { |
| $condition .= " AND properties->'$.creditType' = '$request->creditType'"; |
| } |
| $sql = "SELECT id, additional_credit as credit from student_additional_credit WHERE student_id='$request->studentId' $condition"; |
| $result = $this->executeQueryForObject($sql); |
| return $result; |
| } |
| |
| |
| |
| |
| |
| public function saveStudentSessionalMarksInStudentMarks($request) |
| { |
| $request = $this->realEscapeObject($request); |
| $studentMarks = $request->studentMarks; |
| $examDetails = $request->examDetails; |
| $values = []; |
| $sql = null; |
| $staffId = $_SESSION['staffID'] ? $_SESSION['staffID'] :0; |
| foreach ($studentMarks as $studentMark) { |
| $studentMark->mark = strtoupper($studentMark->mark); |
| $studentMark->percentage = 0; |
| if($studentMark->mark == ''){ |
| $sql = "DELETE from student_marks where batchID=\"$studentMark->batchID\" and examID=\"$studentMark->examId\" and studentID=\"$studentMark->studentID\""; |
| $this->executeQuery($sql); |
| continue; |
| } |
| if ($studentMark->mark == "A") { |
| $studentMark->mark = -1; |
| } else if ($studentMark->mark == "MAL") { |
| $studentMark->mark = -.001; |
| }else if($studentMark->mark && $examDetails->examTotalMarks){ |
| if($studentMark->mark > $examDetails->examTotalMarks){ |
| continue; |
| } |
| $studentMark->percentage = ($studentMark->mark*100)/$examDetails->examTotalMarks; |
| } |
| $values[] = " ('" . $studentMark->batchID . "','" . $studentMark->studentID . "','" . $studentMark->examId . "','" . $studentMark->mark . "', '" . $examDetails->subjectID . "', '" . $studentMark->percentage . "', '" . $examDetails->semID . "','" . $examDetails->examTypeID . "',$staffId) "; |
| } |
| try { |
| if (!empty($values)) { |
| $sql = "INSERT INTO student_marks(batchID,studentID,examID,marksObtained,subjectID,percentage,semID,examTypeID,staffID) VALUES " . implode(",", $values) . " ON DUPLICATE KEY UPDATE marksObtained = VALUES(marksObtained),staffID = VALUES(staffID)"; |
| $this->executeQuery($sql); |
| } |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); |
| } |
| return true; |
| } |
| |
| public function getStudentSessionalMarksInStudentMarks($request) |
| { |
| $request = $this->realEscapeObject($request); |
| $examDetails = $request->examDetails; |
| $sql = null; |
| $result = null; |
| try { |
| $sql = "SELECT marksObtained as mark from student_marks where batchID=\"$request->batchId\" and examID=\"$request->examId\" and studentID=\"$request->studentId\""; |
| $result = $this->executeQueryForObject($sql); |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); |
| } |
| return $result; |
| } |
| |
| |
| |
| |
| |
| |
| public function copyStudentPreviousDigitalValuationMarks($request){ |
| $request = $this->realEscapeObject($request); |
| |
| |
| |
| $sql = "UPDATE oe_exam_user_mark |
| SET valuation_marks = JSON_SET(valuation_marks, |
| '$.\"$request->valuationTo\"', |
| CASE |
| WHEN valuation_marks->'$.\"$request->valuationFrom\".grade' IS NOT NULL |
| THEN JSON_OBJECT( |
| \"mark\", valuation_marks->'$.\"$request->valuationFrom\".mark', |
| \"staffId\", '$request->staffId', |
| \"grade\", JSON_UNQUOTE(valuation_marks->'$.\"$request->valuationFrom\".grade') |
| ) |
| ELSE JSON_OBJECT( |
| \"mark\", valuation_marks->'$.\"$request->valuationFrom\".mark', |
| \"staffId\", '$request->staffId' |
| ) |
| 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; |
| } |
| |
| |
| |
| |
| |
| public function approveSessionalMarks($request) |
| { |
| $request = $this->realEscapeObject($request); |
| $isExistSql = "SELECT isAproved from aprove_exam_marks where semID=" . $request->semId . " and batchID=" . $request->batchId . " and examTypeID=" . $request->examTypeId . " and examID = " . $request->examId . ""; |
| try { |
| $isExist = $this->executeQueryForObject($isExistSql); |
| if($isExist){ |
| $sql = "UPDATE aprove_exam_marks set isAproved=\"$request->isApproved\",staffID=\"$request->staffId\", updatedBy=" . $request->staffId . ", updatedDate=utc_timestamp() where semID=$request->semId and batchID=$request->batchId and examTypeID=\"$request->examTypeId\" and examId = " . $request->examId . ""; |
| }else{ |
| $sql = "INSERT into aprove_exam_marks (batchID, semID, isAproved, staffID,examTypeID, examId, createdBy, createdDate, updatedBy, updatedDate) values (" . $request->batchId . "," . $request->semId . "," . $request->isApproved . " ," . $request->staffId . "," . $request->examTypeId . ", " . $request->examId . ", " . $request->staffId . ", utc_timestamp(), " . $request->staffId . ", utc_timestamp())"; |
| } |
| $this->executeQueryForObject($sql); |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getMessage(), $e->getCode()); |
| } |
| return true; |
| } |
| |
| |
| |
| |
| |
| public function getS3FilePathExist($fileDetails) |
| { |
| $fileDetails = $this->realEscapeObject($fileDetails); |
| $s3Details = new \stdClass(); |
| try { |
| $s3Details->key = $fileDetails->path; |
| $s3Details->bucket = getenv('DIGITAL_EVAL_S3_BUCKET_NAME'); |
| $s3Details->credentials = new Credentials(getenv('DIGITAL_EVAL_AWS_ACCESS_KEY'), getenv('DIGITAL_EVAL_AWS_CLIENT_SECRET_KEY')); |
| |
| $s3FileUrl = $this->getS3FileUrl($s3Details); |
| |
| $attachment = new \stdClass(); |
| $attachment->url = $s3FileUrl; |
| return $attachment; |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getMessage(), $e->getCode()); |
| } |
| } |
| |
| |
| |
| |
| |
| |
| |
| |
| public function getS3FileUrl($s3Details) |
| { |
| $s3FileUrl = null; |
| try { |
| if (empty($s3Details->key)) { |
| throw new ProfessionalException("INVALID_KEY", "Invalid file path details"); |
| } else if (empty($s3Details->bucket)) { |
| $s3Details->bucket = getenv('S3_BUCKET_NAME'); |
| } else if (empty($s3Details->credentials)) { |
| $s3Details->credentials = new Credentials(getenv('AWS_ACCESS_KEY'), getenv('AWS_CLIENT_SECRET_KEY')); |
| } |
| $s3Client = new S3Client([ |
| 'version' => '2006-03-01', |
| 'region' => 'ap-south-1', |
| 'credentials' => $s3Details->credentials, |
| ]); |
| |
| $fileResponse = $s3Client->doesObjectExist($s3Details->bucket, $s3Details->key); |
| if ($fileResponse) { |
| $cmd = $s3Client->getCommand('GetObject', [ |
| 'Bucket' => $s3Details->bucket, |
| 'Key' => $s3Details->key |
| ]); |
| $request = $s3Client->createPresignedRequest($cmd, '+30 minutes'); |
| $s3FileUrl = (string) $request->getUri(); |
| } else { |
| throw new ProfessionalException("NO_FILE_FOUND", "File not Found!"); |
| } |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); |
| } |
| return $s3FileUrl; |
| } |
| |
| |
| |
| |
| |
| |
| public function getExamSubjectRegisteredFalseNoStudentCount($request) |
| { |
| $request = $this->realEscapeObject($request); |
| $studentCounts = []; |
| if ($request->examType == ExamType::REGULAR) { |
| $sql = "SELECT count(distinct ersc.studentID) as totalStudentCount FROM exam_reg_studentchallan ersc |
| INNER JOIN exam_reg_studentsubject erss ON erss.examregID = ersc.examregID AND erss.studentID = ersc.studentID |
| INNER JOIN studentaccount sa ON sa.studentID = ersc.studentID |
| INNER JOIN exam e ON e.examregID = erss.examregID AND e.subjectID = erss.subjectID AND e.batchID = sa.batchID |
| INNER JOIN examcontroller_false_number efn ON efn.studentID = ersc.studentID AND efn.examID = e.examID |
| WHERE erss.examregID IN ($request->examRegId) AND erss.subjectID IN ($request->subjectId) AND ersc.paid=1"; |
| } |
| else if($request->examType == ExamType::SUPPLY){ |
| $sql = "SELECT count(distinct ersd.studentID) as totalStudentCount FROM exam_supplementary_student_details ersd |
| INNER JOIN exam_supplementary_student_subjects erss ON erss.exam_supplementary_id = ersd.exam_supplementary_id AND erss.studentID = ersd.studentID |
| INNER JOIN exam e ON e.examID = erss.examID |
| INNER JOIN exam ex ON ex.subjectID = e.subjectID AND ex.supply_examreg_id = ersd.exam_supplementary_id AND ex.batchID = e.batchID |
| INNER JOIN examcontroller_false_number efn ON efn.studentID = ersd.studentID AND efn.examID = ex.examID |
| WHERE ersd.exam_supplementary_id IN ($request->examRegId) AND e.subjectID IN ($request->subjectId) AND ersd.paid=1 "; |
| } |
| try { |
| $studentCounts = $this->executeQueryForObject($sql); |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getMessage(), $e->getCode()); |
| } |
| return $studentCounts; |
| } |
| |
| |
| |
| |
| |
| |
| public function getS3FileDetails($s3Details) |
| { |
| $files = []; |
| try { |
| if (empty($s3Details->folderPath)) { |
| throw new ProfessionalException("INVALID_KEY", "Invalid file path details"); |
| } |
| if (empty($s3Details->bucket)) { |
| $s3Details->bucket = getenv('DIGITAL_EVAL_S3_BUCKET_NAME'); |
| } |
| if (empty($s3Details->credentials)) { |
| $s3Details->credentials = new Credentials(getenv('DIGITAL_EVAL_AWS_ACCESS_KEY'), getenv('DIGITAL_EVAL_AWS_CLIENT_SECRET_KEY')); |
| } |
| $s3Client = new S3Client([ |
| 'version' => '2006-03-01', |
| 'region' => 'ap-south-1', |
| 'credentials' => $s3Details->credentials, |
| ]); |
| |
| |
| $objects = []; |
| |
| |
| $continuationToken = null; |
| do { |
| |
| $result = $s3Client->listObjectsV2([ |
| 'Bucket' => $s3Details->bucket, |
| 'MaxKeys' => 1000, |
| 'ContinuationToken' => $continuationToken, |
| 'Prefix' => $s3Details->folderPath.'/', |
| ]); |
| |
| |
| $objects = array_merge($objects, $result['Contents']); |
| |
| |
| $continuationToken = $result['NextContinuationToken']; |
| |
| } while ($continuationToken !== null); |
| |
| |
| foreach ($objects as $object) { |
| if(substr($object['Key'], -1) !== '/'){ |
| $files[] = $object['Key']; |
| } |
| |
| } |
| |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); |
| } |
| return $files; |
| } |
| |
| |
| |
| |
| |
| |
| |
| public function getExamRegisteredStudentForSubjectByRequest($examRegistrationSubjectRequest) |
| { |
| $sql = null; |
| $examRegistrationSubjectRequest = $this->realEscapeObject($examRegistrationSubjectRequest); |
| $studentDetails = []; |
| try { |
| if ($examRegistrationSubjectRequest->examType == ExamType::REGULAR) { |
| $sql = "SELECT sa.studentID, sa.regNo, sa.batchID, e.examID, e.examTotalMarks FROM exam_reg_studentsubject ers |
| INNER JOIN studentaccount sa ON (ers.studentID = sa.studentID) |
| LEFT JOIN failed_students fs ON fs.studentID = sa.studentID AND FIND_IN_SET($examRegistrationSubjectRequest->semId, fs.hisSemestersInThisbatch) |
| INNER JOIN exam e ON (e.subjectID = ers.subjectID AND IF(fs.previousBatch,fs.previousBatch,sa.batchID) = e.batchID AND e.examregID = ers.examRegId) |
| INNER JOIN exam_reg_studentchallan ersc ON (ersc.studentID = ers.studentID AND ersc.examregID = ers.examregID AND ersc.paid = 1) |
| WHERE e.semID = '$examRegistrationSubjectRequest->semId' AND ers.examRegId = '$examRegistrationSubjectRequest->examRegId' AND ers.subjectID = '$examRegistrationSubjectRequest->subjectId' "; |
| } else if ($examRegistrationSubjectRequest->examType == ExamType::SUPPLY) { |
| $sql = "SELECT sa.studentID, sa.regNo, sa.batchID, e.examID, e.examTotalMarks FROM exam e |
| INNER JOIN exam e1 ON (e.subjectID = e1.subjectID AND e.semID = e1.semID AND e.batchID = e1.batchID AND e1.supply_examreg_id IS NULL ) |
| INNER JOIN exam_supplementary_student_subjects esss ON (esss.examID = e1.examID AND esss.exam_supplementary_id = e.supply_examreg_id) |
| INNER JOIN exam_supplementary_student_details essd ON essd.exam_supplementary_id = e.supply_examreg_id AND essd.studentID = esss.studentID |
| LEFT JOIN failed_students fs ON fs.studentID = essd.studentID AND FIND_IN_SET(e.semID, fs.hisSemestersInThisbatch) |
| INNER JOIN studentaccount sa ON (sa.studentID = esss.studentID AND sa.batchID = e.batchID) |
| WHERE essd.paid =1 AND e.supply_examreg_id = '$examRegistrationSubjectRequest->examRegId' AND e.subjectID = '$examRegistrationSubjectRequest->subjectId' "; |
| } |
| $studentDetails = $this->executeQueryForList($sql); |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); |
| } |
| return $studentDetails; |
| } |
| |
| |
| |
| |
| |
| public function getPacketAssignedStudentList($request) |
| { |
| $request = $this->realEscapeObject($request); |
| $condition = ""; |
| $students = []; |
| if ($request->examType == ExamType::REGULAR) { |
| $examRegField = "e.examregID"; |
| } else if ($request->examType == ExamType::SUPPLY) { |
| $examRegField = "e.supply_examreg_id"; |
| } |
| if($request->examRegId){ |
| $condition .= " AND $examRegField IN ($request->examRegId)"; |
| } |
| if ($request->subjectId) { |
| $condition .= " AND e.subjectID IN ($request->subjectId)"; |
| } |
| $sql = "SELECT easvs.packetNo,sa.studentID ,sa.regNo ,sa.studentName,b.batchName FROM exam_answerSheetGroup_student_valuated_staff easvs |
| INNER JOIN exam e ON e.examID = easvs.examId |
| INNER JOIN studentaccount sa ON sa.studentID = easvs.studentId |
| INNER JOIN batches b ON b.batchID = e.batchID |
| WHERE 1 = 1 $condition"; |
| try { |
| $students = $this->executeQueryForList($sql); |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getMessage(), $e->getCode()); |
| } |
| return $students; |
| } |
| |
| |
| |
| |
| |
| |
| public function getExamSubjectAttendedStudentCount($request) |
| { |
| $request = $this->realEscapeObject($request); |
| $studentCounts = []; |
| |
| if($request->isSupply){ |
| |
| $sql = "SELECT count(distinct ersc.studentID) as totalStudentCount FROM exam_supplementary_student_details ersc |
| INNER JOIN exam_supplementary_student_subjects erss ON erss.exam_supplementary_id = ersc.exam_supplementary_id AND erss.studentID = ersc.studentID |
| INNER JOIN exam ex ON ex.examID = erss.examID |
| INNER JOIN studentaccount sa ON sa.studentID = ersc.studentID |
| LEFT JOIN failed_students fs ON fs.studentID = ersc.studentID AND FIND_IN_SET($request->semId, fs.hisSemestersInThisbatch) |
| INNER JOIN exam e ON e.subjectID = ex.subjectID AND e.supply_examreg_id = erss.exam_supplementary_id AND e.batchID = IF (fs.previousBatch, fs.previousBatch, sa.batchID) |
| INNER JOIN exam_attendance ea ON ea.examID = e.examID AND ea.studentID = erss.studentID |
| WHERE erss.exam_supplementary_id IN ($request->examRegId) AND e.subjectID IN ($request->subjectId) AND ersc.paid=1 AND ea.isAbsent =0"; |
| }else{ |
| $sql = "SELECT count(distinct ersc.studentID) as totalStudentCount FROM exam_reg_studentchallan ersc |
| INNER JOIN exam_reg_studentsubject erss ON erss.examregID = ersc.examregID AND erss.studentID = ersc.studentID |
| INNER JOIN studentaccount sa ON sa.studentID = ersc.studentID |
| LEFT JOIN failed_students fs ON fs.studentID = ersc.studentID AND FIND_IN_SET($request->semId, fs.hisSemestersInThisbatch) |
| INNER JOIN exam e ON e.subjectID = erss.subjectID AND e.examregID = erss.examregID AND e.batchID = IF (fs.previousBatch, fs.previousBatch, sa.batchID) |
| INNER JOIN exam_attendance ea ON ea.examID = e.examID AND ea.studentID = erss.studentID |
| WHERE erss.examregID IN ($request->examRegId) AND erss.subjectID IN ($request->subjectId) AND ersc.paid=1 AND ea.isAbsent =0"; |
| } |
| try { |
| $studentCounts = $this->executeQueryForObject($sql); |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getMessage(), $e->getCode()); |
| } |
| return $studentCounts; |
| } |
| |
| |
| |
| |
| |
| public function getStudentMarksBeforeModerationByRule($request) |
| { |
| $request = $this->realEscapeObject($request); |
| $studentMarks = []; |
| |
| $examRegField = $request->isSupply ? "supply_examreg_id" : "examregID"; |
| $sql = " SELECT sm.id,e.batchID as batchId,b.batchName,e.subjectID as subjectId,s.subjectName,s.subjectDesc,sa.studentID as studentId,sa.regNo,sa.studentName,e.examID as examId,sm.oldMark |
| FROM student_mark_before_moderation sm |
| INNER JOIN exam e ON e.examID = sm.examID |
| INNER JOIN studentaccount sa ON sa.studentID = sm.studentID |
| INNER JOIN subjects s ON s.subjectID = e.subjectID |
| INNER JOIN batches b ON b.batchID = e.batchID |
| WHERE sm.examID IN(SELECT examID from exam where $examRegField IN ($request->examRegId)) AND sm.moderation_rule_id IN ($request->ruleId)"; |
| try { |
| $studentMarks = $this->executeQueryForList($sql); |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getMessage(), $e->getCode()); |
| } |
| return $studentMarks; |
| } |
| |
| |
| |
| |
| |
| public function getDistinctDatesAssignedStaffStudentsValuation($request) |
| { |
| $request = $this->realEscapeObject($request); |
| $examDates =[]; |
| $condition = ""; |
| if($request->examRegId){ |
| $condition .= " AND evss.examregId IN($request->examRegId)"; |
| } |
| if($request->examType){ |
| $condition .= " AND evss.examType = '$request->examType'"; |
| } |
| $sql = "SELECT DISTINCT(evss.valuationStartDate) AS valuationStartDate,DATE_FORMAT(evss.valuationStartDate, '%d-%m-%Y') AS valuationStartDateFormatted |
| FROM examValuationStaffAssignedStudents evss |
| WHERE 1=1 $condition |
| ORDER BY evss.valuationStartDate"; |
| try { |
| $examDates = $this->executeQueryForList($sql); |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getMessage(), $e->getCode()); |
| } |
| return $examDates; |
| } |
| |
| |
| |
| |
| |
| public function getDistinctSubjectsAssignedStaffStudentsValuation($request) |
| { |
| $request = $this->realEscapeObject($request); |
| $subjects =[]; |
| $condition = ""; |
| if($request->examRegId){ |
| $condition .= " AND evss.examregId IN($request->examRegId)"; |
| } |
| if($request->examType){ |
| $condition .= " AND evss.examType = '$request->examType'"; |
| } |
| if($request->valuationStartDate){ |
| $valuationStartDateString = "'" . implode("','", $request->valuationStartDate) . "'"; |
| $condition .= " AND evss.valuationStartDate IN ($valuationStartDateString)"; |
| } |
| $sql = "SELECT DISTINCT(evss.subjectId) AS subjectId |
| FROM examValuationStaffAssignedStudents evss |
| WHERE 1=1 $condition"; |
| try { |
| $subjects = $this->executeQueryForList($sql); |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getMessage(), $e->getCode()); |
| } |
| return $subjects; |
| } |
| |
| |
| |
| |
| |
| public function mapValuationStaffsWithReviewer($request) |
| { |
| $request = $this->realEscapeObject($request); |
| $sql = ""; |
| $condition = ""; |
| if ($request->examType) { |
| $condition .= " AND examType = '$request->examType'"; |
| } |
| if ($request->examRegId) { |
| $condition .= " AND examRegId IN ($request->examRegId)"; |
| } |
| if ($request->subjectId) { |
| $condition .= " AND subjectId IN ($request->subjectId)"; |
| } |
| if ($request->valuationCount) { |
| $condition .= " AND valuationCount IN ($request->valuationCount)"; |
| } |
| if ($request->staffId) { |
| $condition .= " AND staffId IN ($request->staffId)"; |
| } |
| if($request->reviewerId){ |
| $sql = "UPDATE examValuationStaffAssignedStudents SET properties = JSON_SET(IFNULL(properties, '{}'),'$.reviewerId',$request->reviewerId) |
| WHERE 1=1 $condition"; |
| }else{ |
| $sql = "UPDATE examValuationStaffAssignedStudents SET properties = JSON_SET(IFNULL(properties, '{}'),'$.reviewerId','') |
| WHERE 1=1 $condition"; |
| } |
| |
| try { |
| $this->executeQueryForList($sql); |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getMessage(), $e->getCode()); |
| } |
| return true; |
| } |
| |
| |
| |
| |
| |
| public function getValuerByStudentDetails($request) |
| { |
| $request = $this->realEscapeObject($request); |
| $valuers = []; |
| $condition = ""; |
| if($request->examRegId){ |
| $condition .=" AND evsas.examRegId IN ($request->examRegId)"; |
| } |
| if($request->examType){ |
| $condition .=" AND evsas.examType ='$request->examType'"; |
| } |
| if($request->subjectId){ |
| $condition .=" AND evsas.subjectId IN ($request->subjectId)"; |
| } |
| if($request->studentId){ |
| $condition .=" AND evsas.studentId IN ($request->studentId)"; |
| } |
| if($request->valuationCount){ |
| $condition .=" AND evsas.valuationCount IN ($request->valuationCount)"; |
| } |
| $sql = "SELECT evsas.valuationCount,evsas.staffId ,sa.staffName FROM examValuationStaffAssignedStudents evsas |
| INNER JOIN staffaccounts sa ON sa.staffID = evsas.staffId |
| WHERE 1=1 $condition"; |
| try { |
| $result = $this->executeQueryForList($sql); |
| |
| foreach ($result as $row) { |
| $valuers[$row->valuationCount] = $row; |
| } |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getMessage(), $e->getCode()); |
| } |
| return $valuers; |
| } |
| |
| |
| |
| |
| |
| |
| public function getSupplyValuerByStudentDetails($request) |
| { |
| $request = $this->realEscapeObject($request); |
| $valuers = []; |
| $condition = ""; |
| if($request->studentId && $request->examId){ |
| $supplyExamRegs = ExamRegistrationService::getInstance()->getStudentSupplyExamRegistrationsByRegularExamId($request); |
| } |
| if(!empty($supplyExamRegs)){ |
| if($request->examType){ |
| $condition .=" AND evsas.examType ='$request->examType'"; |
| } |
| if($request->subjectId){ |
| $condition .=" AND evsas.subjectId IN ($request->subjectId)"; |
| } |
| if($request->studentId){ |
| $condition .=" AND evsas.studentId IN ($request->studentId)"; |
| } |
| if($request->valuationCount){ |
| $condition .=" AND evsas.valuationCount IN ($request->valuationCount)"; |
| } |
| foreach($supplyExamRegs as $supplyExamReg){ |
| $request->examRegId = $supplyExamReg->supplyExamRegId; |
| if($request->examRegId){ |
| $condition .=" AND evsas.examRegId IN ($request->examRegId)"; |
| } |
| $sql = "SELECT evsas.valuationCount,evsas.staffId ,sa.staffName FROM examValuationStaffAssignedStudents evsas |
| INNER JOIN staffaccounts sa ON sa.staffID = evsas.staffId |
| WHERE 1=1 $condition"; |
| try { |
| $result = $this->executeQueryForList($sql); |
| |
| foreach ($result as $row) { |
| $row->supplyDesc = $supplyExamReg->supplyDesc; |
| $valuers[$supplyExamReg->supplyExamRegId][$row->valuationCount] = $row; |
| } |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getMessage(), $e->getCode()); |
| } |
| } |
| } |
| return $valuers; |
| } |
| |
| |
| |
| |
| |
| |
| public function getPacketAssignedSubjectStaffDetails($request) |
| { |
| $request = $this->realEscapeObject($request); |
| $result =[]; |
| $condition = ""; |
| $examRegIdField = $request->examType == "REGULAR" ? "e.examregID":"e.supply_examreg_id"; |
| $examRegIdFieldInPacketsTable = $request->examType == "REGULAR" ? "evsp.examRegId":"evsp.supplyRegId"; |
| if($request->examRegId){ |
| $condition .= " AND $examRegIdField IN($request->examRegId)"; |
| } |
| $groupByValType = $request->groupByChiefValuator ? "evsp.chiefEvaluator":"evsp.additionalEvaluator"; |
| $sql = "SELECT evsp.additionalEvaluator as additionalEvaluatorId,sa.staffName as additionalEvaluator,evsp.chiefEvaluator as chiefEvaluatorId,sc.staffName as chiefEvaluator,evsp.subjectId ,s.subjectName,s.subjectDesc,evsp.valuationCount,count(evspr.studentId) as studentCount from examValuationStudentPacketsRelation evspr |
| INNER JOIN exam e ON e.examID = evspr.examId |
| INNER JOIN subjects s ON s.subjectID = e.subjectID |
| INNER JOIN examValuationFacultyPackets evsp ON $examRegIdFieldInPacketsTable = $examRegIdField AND evsp.subjectId = e.subjectID AND evsp.packetNo = evspr.packetNo |
| INNER JOIN exam_attendance ea ON ea.examID = e.examID AND ea.studentID = evspr.studentId |
| LEFT JOIN staffaccounts sa ON sa.staffID = evsp.additionalEvaluator |
| LEFT JOIN staffaccounts sc ON sc.staffID = evsp.chiefEvaluator |
| WHERE ea.isAbsent = 0 $condition GROUP BY $groupByValType,evsp.subjectId,evsp.valuationCount"; |
| try { |
| $result = $this->executeQueryForList($sql); |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getMessage(), $e->getCode()); |
| } |
| return $result; |
| } |
| |
| |
| |
| |
| |
| |
| public function saveStudentPacketRelation($request) |
| { |
| $request = $this->realEscapeObject($request); |
| $condition = ""; |
| if($request->examId){ |
| $condition .= "ee.examID IN($request->examId)"; |
| } |
| if($request->studentId){ |
| $condition .= " AND ee.studentID = '$request->studentId'"; |
| } |
| $examRegId = $request->examRegId ? $request->examRegId : "NULL"; |
| $supplyRegId = $request->supplyRegId ? $request->supplyRegId : "NULL"; |
| $sql = "INSERT |
| INTO |
| exam_answerSheetGroup_student_valuated_staff (examType, |
| examRegId, |
| supplyExamRegId, |
| examId, |
| studentId, |
| valuatedStaffId, |
| examReg_subject_answerSheet_group_id, |
| packetNo, |
| exammarks_external_id, |
| mark_inserted_staffId, |
| valuationType, |
| createdBy, |
| createdDate, |
| updatedBy, |
| updatedDate ) |
| SELECT |
| '".$request->examType."', |
| ".$examRegId.", |
| ".$supplyRegId.", |
| ee.examID, |
| ee.studentID, |
| ".$request->staffId.", |
| ".$request->esagId.", |
| ".$request->packetNo.", |
| ee.id, |
| ".$request->adminId.", |
| '".$request->valuationType."', |
| 1, |
| utc_timestamp(), |
| 1, |
| utc_timestamp() |
| FROM |
| exammarks_external ee |
| WHERE |
| " .$condition . " ON |
| DUPLICATE KEY |
| UPDATE |
| updatedDate = VALUES(updatedDate) , |
| valuatedStaffId = VALUES(valuatedStaffId)"; |
| try { |
| $this->executeQueryForObject($sql); |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getMessage(), $e->getCode()); |
| } |
| return true; |
| } |
| |
| |
| |
| |
| |
| |
| public function getExamStudentPackets($request) |
| { |
| $request = $this->realEscapeObject($request); |
| $condition = ""; |
| if($request->excludeStaffId){ |
| $condition .= " AND valuatedStaffId NOT IN ($request->excludeStaffId)"; |
| } |
| if($request->staffId){ |
| $condition .= " AND valuatedStaffId IN ($request->staffId)"; |
| } |
| if($request->packetNo){ |
| $condition .= " AND packetNo IN ($request->packetNo)"; |
| } |
| $sql = "SELECT packetNo,studentId FROM exam_answerSheetGroup_student_valuated_staff where examId IN ($request->examId) $condition"; |
| try { |
| return $this->executeQueryForList($sql); |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getMessage(), $e->getCode()); |
| } |
| } |
| |
| |
| |
| |
| |
| public function deleteStudentPacketRelation($request) |
| { |
| $request = $this->realEscapeObject($request); |
| $condition = ""; |
| try { |
| if($request->staffId && $request->staffId && $request->staffId){ |
| if($request->staffId){ |
| $condition .= " AND valuatedStaffId IN ($request->staffId)"; |
| } |
| if($request->studentId){ |
| $condition .= " AND studentId IN ($request->studentId)"; |
| } |
| $sql = "DELETE FROM exam_answerSheetGroup_student_valuated_staff where examId IN ($request->examId) $condition"; |
| return $this->executeQueryForObject($sql); |
| } |
| |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getMessage(), $e->getCode()); |
| } |
| } |
| |
| |
| |
| |
| |
| public function getStudentPacketForExam($examId,$studentId) |
| { |
| $examId = $this->realEscapeString($examId); |
| $studentId = $this->realEscapeString($studentId); |
| $condition = ""; |
| if($studentId ){ |
| $condition .= " AND studentId IN ($studentId )"; |
| } |
| $sql = "SELECT packetNo,studentId FROM exam_answerSheetGroup_student_valuated_staff where examId IN ($examId) $condition"; |
| try { |
| return $this->executeQueryForObject($sql); |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getMessage(), $e->getCode()); |
| } |
| } |
| |
| |
| |
| |
| |
| public function getExternalExamThiredValStudentsByExamId($examId) |
| { |
| $examId = $this->realEscapeString($examId); |
| $sql = "SELECT studentID FROM externalexam_thirdvalstudents where examID IN ($examId)"; |
| try { |
| return $this->executeQueryForList($sql); |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getMessage(), $e->getCode()); |
| } |
| } |
| |
| |
| |
| |
| |
| public function getDigitalValuationAssignedExamRegistrationsByStaff($request) |
| { |
| $request = $this->realEscapeObject($request); |
| $condition = ""; |
| $groupBy = "GROUP BY evsas.examType,evsas.examRegId"; |
| if($request->staffId){ |
| $condition .= " AND evsas.staffId IN ($request->staffId)"; |
| } |
| if($request->subjectId){ |
| $condition .= " AND evsas.subjectId IN ($request->subjectId)"; |
| } |
| if($request->examMonth){ |
| $condition .= " AND IF(er.examMonth,er.examMonth,es.examMonth) IN ($request->examMonth)"; |
| } |
| if($request->examYear){ |
| $condition .= " AND IF(er.examYear ,er.examYear,es.examYear) IN ($request->examYear)"; |
| } |
| if($request->getStudentCount){ |
| $groupBy = ""; |
| } |
| $sql = "SELECT evsas.examType,evsas.examRegId,er.examregName,es.supplyDesc,IF(er.examMonth,er.examMonth,es.examMonth) as examMonth,IF(er.examYear ,er.examYear,es.examYear) as examYear |
| FROM examValuationStaffAssignedStudents evsas |
| LEFT JOIN exam_registration er ON evsas.examType = 'REGULAR' AND er.examregID = evsas.examRegId |
| LEFT JOIN exam_supplementary es ON evsas.examType = 'SUPPLY' AND es.id = evsas.examRegId |
| WHERE 1=1 $condition $groupBy"; |
| try { |
| return $this->executeQueryForList($sql); |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getMessage(), $e->getCode()); |
| } |
| } |
| |
| |
| |
| |
| |
| public function getDigitalRevaluationAssignedExamRegistrationsByStaff($request) |
| { |
| $request = $this->realEscapeObject($request); |
| $condition = ""; |
| $groupBy = " GROUP BY ersas.revaluationId"; |
| if($request->staffId){ |
| $condition .= " AND ersas.staffId IN ($request->staffId)"; |
| } |
| if($request->subjectId){ |
| $condition .= " AND e.subjectID IN ($request->subjectId)"; |
| } |
| if($request->examMonth){ |
| $condition .= " AND IF(er.examMonth,er.examMonth,es.examMonth) IN ($request->examMonth)"; |
| } |
| if($request->examYear){ |
| $condition .= " AND IF(er.examYear ,er.examYear,es.examYear) IN ($request->examYear)"; |
| } |
| if($request->getStudentCount){ |
| $groupBy = ""; |
| } |
| $sql = "SELECT IF(er.examregID ,'REGULAR','SUPPLY') as examType ,IF(er.examregID ,er.examregID,es.id) as examRegId ,IF(er.examregID ,er.examregName ,es.supplyDesc) as examregName,IF(er.examMonth,er.examMonth,es.examMonth) as examMonth,IF(er.examYear ,er.examYear,es.examYear) as examYear,ersas.revaluationId ,ers.revalDesc FROM examRevaluationStaffAssignedStudents ersas |
| INNER JOIN exam_revaluation ers ON ers.id= ersas.revaluationId |
| INNER JOIN exam e ON e.examID = ersas.examId |
| LEFT JOIN exam_registration er ON e.examregID IS NOT NULL AND er.examregID = e.examregID |
| LEFT JOIN exam_supplementary es ON e.supply_examreg_id IS NOT NULL AND es.id = e.supply_examreg_id |
| WHERE 1=1 $condition $groupBy"; |
| try { |
| return $this->executeQueryForList($sql); |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getMessage(), $e->getCode()); |
| } |
| } |
| |
| |
| |
| |
| |
| |
| public function getDigitalValuationAssignedSubjectStudentsByStaff($request) |
| { |
| $request = $this->realEscapeObject($request); |
| $condition = ""; |
| if($request->staffId){ |
| $condition .= " AND evsas.staffId IN ($request->staffId)"; |
| } |
| if($request->subjectId){ |
| $condition .= " AND s.subjectID IN ($request->subjectId)"; |
| } |
| if($request->examMonth){ |
| $condition .= " AND IF(er.examMonth,er.examMonth,es.examMonth) IN ($request->examMonth)"; |
| } |
| if($request->examYear){ |
| $condition .= " AND IF(er.examYear ,er.examYear,es.examYear) IN ($request->examYear)"; |
| } |
| if($request->groupBySubject){ |
| $groupBy = "GROUP BY evsas.subjectId"; |
| }else{ |
| $groupBy = "GROUP BY evsas.subjectId,evsas.examRegId,evsas.examType,e.examID,oem.id"; |
| } |
| $sql = "SELECT evsas.examType,evsas.examRegId,IF(er.examregID ,er.examregName ,es.supplyDesc) as examregName,IF(er.examMonth,er.examMonth,es.examMonth) as examMonth,IF(er.examYear ,er.examYear,es.examYear) as examYear,evsas.subjectId,s.subjectName,s.subjectDesc,e.examID,oe.id,e.semID as semId |
| FROM examValuationStaffAssignedStudents evsas |
| INNER JOIN subjects s ON s.subjectID = evsas.subjectId |
| LEFT JOIN exam_registration er ON evsas.examType = 'REGULAR' AND er.examregID = evsas.examRegId |
| LEFT JOIN exam_supplementary es ON evsas.examType = 'SUPPLY' AND es.id = evsas.examRegId |
| INNER JOIN exam e ON e.subjectID = s.subjectID AND IF(er.examregID,er.examregID,es.id) = IF(e.examregID,e.examregID,e.supply_examreg_id) |
| INNER JOIN oe_exams oe ON oe.identifying_context ->> '$.examId' = e.examID AND (oe.properties ->> '$.isMockExam' != 'true' OR oe.properties ->> '$.isMockExam' is null) |
| INNER JOIN oe_exam_marks_confirm oem ON oem.oe_exams_id = oe.id AND oem.created_by = evsas.staffId AND oem.is_confirmed =1 AND oem.revaluation_id IS NULL |
| WHERE 1=1 $condition $groupBy"; |
| try { |
| return $this->executeQueryForList($sql); |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getMessage(), $e->getCode()); |
| } |
| } |
| |
| |
| |
| |
| |
| public function getDigitalRevaluationAssignedSubjectStudentsByStaff($request) |
| { |
| $request = $this->realEscapeObject($request); |
| $condition = ""; |
| if($request->staffId){ |
| $condition .= " AND ersas.staffId IN ($request->staffId)"; |
| } |
| if($request->subjectId){ |
| $condition .= " AND s.subjectID IN ($request->subjectId)"; |
| } |
| if($request->examMonth){ |
| $condition .= " AND IF(er.examMonth,er.examMonth,es.examMonth) IN ($request->examMonth)"; |
| } |
| if($request->examYear){ |
| $condition .= " AND IF(er.examYear ,er.examYear,es.examYear) IN ($request->examYear)"; |
| } |
| if($request->groupBySubject){ |
| $groupBy ="GROUP BY s.subjectID"; |
| }else{ |
| $groupBy = "GROUP BY ersas.examId,ersas.revaluationId,oem.id"; |
| } |
| $sql = "SELECT ersas.revaluationId ,ers.revalDesc ,IF(er.examMonth,er.examMonth,es.examMonth) as examMonth,IF(er.examYear ,er.examYear,es.examYear) as examYear,e.subjectId,s.subjectName,s.subjectDesc,e.examID,oe.id,e.semID as semId from examRevaluationStaffAssignedStudents ersas |
| INNER JOIN exam_revaluation ers ON ers.id= ersas.revaluationId |
| INNER JOIN exam e ON e.examID = ersas.examId |
| INNER JOIN subjects s ON s.subjectID = e.subjectID |
| LEFT JOIN exam_registration er ON e.examregID IS NOT NULL AND er.examregID = e.examregID |
| LEFT JOIN exam_supplementary es ON e.supply_examreg_id IS NOT NULL AND es.id = e.supply_examreg_id |
| INNER JOIN oe_exams oe ON oe.identifying_context ->> '$.examId' = e.examID AND (oe.properties ->> '$.isMockExam' != 'true' OR oe.properties ->> '$.isMockExam' is null) |
| INNER JOIN oe_exam_marks_confirm oem ON oem.oe_exams_id = oe.id AND oem.created_by = ersas.staffId AND oem.is_confirmed =1 AND oem.revaluation_id = ers.id |
| WHERE 1=1 $condition $groupBy"; |
| try { |
| return $this->executeQueryForList($sql); |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getMessage(), $e->getCode()); |
| } |
| } |
| |
| |
| |
| |
| |
| |
| public function submitRemunerationBill($request) |
| { |
| $properties = $request->properties ? addslashes(json_encode($request->properties, JSON_INVALID_UTF8_IGNORE | JSON_PARTIAL_OUTPUT_ON_ERROR | JSON_ERROR_INVALID_PROPERTY_NAME)) : ""; |
| $request = $this->realEscapeObject($request); |
| $identifier = $request->identifier; |
| $subjectId = $request->subjectId; |
| $userId = $request->userId; |
| $entryType = $request->entryType; |
| $userType = $request->userType; |
| $identifierType = $request->identifierType; |
| $sql = "INSERT INTO subjectWiseSubmissions (identifier, subjectId, userId, properties, entryType, userType, identifierType, created_by, created_date) VALUES ('$identifier', '$subjectId', '$userId', '$properties', '$entryType', '$userType', '$identifierType', '$userId', NOW())"; |
| try { |
| return $this->executeQuery($sql); |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getMessage(), $e->getCode()); |
| } |
| } |
| |
| |
| |
| |
| |
| public function getSubmittedRemunerationBill($request) |
| { |
| $request = $this->realEscapeObject($request); |
| $condition = ""; |
| $orderBy = ""; |
| if($request->subjectId){ |
| $condition .= " AND subjectId = '$request->subjectId'"; |
| } |
| if($request->userId){ |
| $condition .= " AND userId IN($request->userId)"; |
| } |
| if($request->identifier){ |
| $condition .= " AND identifier = '$request->identifier'"; |
| } |
| if($request->entryType){ |
| $condition .= " AND entryType = '$request->entryType'"; |
| } |
| if($request->userType){ |
| $condition .= " AND userType = '$request->userType'"; |
| } |
| if($request->identifierType){ |
| $condition .= " AND identifierType = '$request->identifierType'"; |
| } |
| if($request->id){ |
| $condition .= " AND id = '$request->id'"; |
| } |
| if($request->fromDate && $request->toDate){ |
| $condition .= " AND created_date BETWEEN '$request->fromDate' AND '$request->toDate' "; |
| $orderBy = " ORDER BY created_date;"; |
| } |
| $sql = "SELECT id,subjectId,userId,properties FROM subjectWiseSubmissions |
| WHERE 1=1 $condition $orderBy "; |
| try { |
| if($request->getList){ |
| return $this->executeQueryForList($sql); |
| }else{ |
| return $this->executeQueryForObject($sql); |
| } |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getMessage(), $e->getCode()); |
| } |
| } |
| |
| |
| |
| |
| |
| public function submitStudentRoomDetails($request) |
| { |
| $properties = $request->properties ? addslashes(json_encode($request->properties, JSON_INVALID_UTF8_IGNORE | JSON_PARTIAL_OUTPUT_ON_ERROR | JSON_ERROR_INVALID_PROPERTY_NAME)) : ""; |
| $request = $this->realEscapeObject($request); |
| $identifier = $request->identifier; |
| $userId = $request->userId; |
| $entryType = $request->entryType; |
| $userType = $request->userType; |
| $identifierType = $request->identifierType; |
| $studentList = $request->studentList; |
| $createdBy = $request->createdBy; |
| $insertStr = []; |
| foreach($studentList as $student){ |
| $userId = $student['studentID']; |
| $insertStr[] = "('$identifier', $userId, '$properties', '$entryType', '$userType', '$identifierType', '$createdBy', NOW())"; |
| } |
| if(!empty($insertStr)){ |
| $insertStr = implode($insertStr,","); |
| $sql = "INSERT INTO subjectWiseSubmissions (identifier, userId, properties, entryType, userType, identifierType, created_by, created_date) VALUES $insertStr"; |
| try { |
| return $this->executeQuery($sql); |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getMessage(), $e->getCode()); |
| } |
| } |
| return false; |
| } |
| |
| |
| |
| |
| |
| public function getStudentRoomDetails($request) |
| { |
| $request = $this->realEscapeObject($request); |
| $identifier = $request->identifier; |
| $entryType = $request->entryType; |
| $userType = $request->userType; |
| $identifierType = $request->identifierType; |
| $groupBy = ""; |
| if($request->groupByRooms){ |
| $groupBy = " GROUP BY properties->>'$.roomNo'"; |
| } |
| $sql = "SELECT properties->>'$.roomNo' AS roomNo,userId FROM subjectWiseSubmissions WHERE identifierType ='$identifierType' AND userType ='$userType' AND entryType ='$entryType' and identifier =$identifier $groupBy"; |
| try { |
| return $this->executeQueryForList($sql); |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getMessage(), $e->getCode()); |
| } |
| return false; |
| } |
| |
| |
| |
| |
| public function getExamRegisteredStudentsWithRoomDetails($request){ |
| $request = $this->realEscapeObject($request); |
| $examRegId = $request->examRegId; |
| $batchId = $request->batchId; |
| $subjectId = $request->subjectId; |
| $condition = ""; |
| if($request->roomNo){ |
| $condition .= " AND sws.properties->>'$.roomNo' IN($request->roomNo)"; |
| } |
| if($request->isSupply){ |
| $sql = "SELECT essd.exam_supplementary_id,e.examID,se.examID as supplyExamId,essd.studentID,sa.regNo,sa.studentName,sws.properties->>'$.roomNo' AS roomNo from exam_supplementary_student_details essd |
| INNER JOIN exam e ON e.batchID='$batchId' and e.subjectID='$subjectId' and examregID IS NOT NULL |
| INNER JOIN exam se ON se.batchID='$batchId' and se.subjectID='$subjectId' and se.supply_examreg_id = '$examRegId' |
| INNER JOIN exam_supplementary_student_subjects esss on esss.exam_supplementary_id = essd.exam_supplementary_id and esss.examID = e.examID and esss.studentID = essd.studentID |
| INNER JOIN studentaccount sa on sa.studentID=essd.studentID |
| LEFT JOIN subjectWiseSubmissions sws ON sws.userId = essd.studentID AND sws.identifier = essd.exam_supplementary_id AND identifierType ='SUPPLY' AND userType ='STUDENT' AND entryType ='EXAM_REG_STUDENT_ROOM_NO' |
| WHERE essd.exam_supplementary_id='$examRegId' and sa.batchID='$batchId' and essd.paid=1 $condition |
| order by sa.regNo"; |
| }else{ |
| $sql = "SELECT ers.studentID, sa.regNo ,sa.studentName, sa.rollNo,sws.properties->>'$.roomNo' AS roomNo from exam_reg_studentchallan ers |
| INNER JOIN exam_reg_studentsubject ersb ON ersb.examregID=ers.examregID and ersb.studentID = ers.studentID |
| INNER JOIN studentaccount sa ON sa.studentID = ers.studentID AND sa.studentID = ersb.studentID |
| LEFT JOIN subjectWiseSubmissions sws ON sws.userId = ers.studentID AND sws.identifier = ers.examregID AND identifierType ='REGULAR' AND userType ='STUDENT' AND entryType ='EXAM_REG_STUDENT_ROOM_NO' |
| where ers.examregID='$examRegId' and sa.batchID='$batchId' and ersb.subjectID='$subjectId' and ers.paid=1 $condition |
| order by sa.regNo"; |
| } |
| |
| try { |
| $subjectStudents = $this->executeQueryForList($sql); |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); |
| } |
| return $subjectStudents; |
| } |
| |
| |
| |
| |
| |
| public function updateStudentValuationDates($request) |
| { |
| $request = $this->realEscapeObject($request); |
| $condition =""; |
| if($request->examRegId){ |
| $condition .= " AND examRegId IN ($request->examRegId)"; |
| } |
| if($request->examType){ |
| $condition .= " AND examType = '$request->examType'"; |
| } |
| if($request->subjectId){ |
| $condition .= " AND subjectId IN ($request->subjectId)"; |
| } |
| if($request->staffId){ |
| $condition .= " AND staffId IN ($request->staffId)"; |
| } |
| if($request->valuationCount){ |
| $condition .= " AND valuationCount IN ($request->valuationCount)"; |
| } |
| try { |
| if($request->startDate && $request->endDate){ |
| $sql = "UPDATE examValuationStaffAssignedStudents set valuationStartDate = '$request->startDate' ,valuationEndDate = '$request->endDate' |
| WHERE 1=1 $condition"; |
| } |
| $this->executeQuery($sql); |
| } catch (\Exception $e) { |
| throw new ProfessionalException($e->getMessage(), $e->getCode()); |
| } |
| return true; |
| } |
| |
| |
| } |