Code Coverage |
||||||||||
Classes and Traits |
Functions and Methods |
Lines |
||||||||
| Total | |
0.00% |
0 / 1 |
|
0.00% |
0 / 108 |
CRAP | |
0.00% |
0 / 2567 |
| ExamRegistrationService | |
0.00% |
0 / 1 |
|
0.00% |
0 / 108 |
218556.00 | |
0.00% |
0 / 2567 |
| __construct | |
0.00% |
0 / 1 |
2.00 | |
0.00% |
0 / 2 |
|||
| __clone | n/a |
0 / 0 |
1 | n/a |
0 / 0 |
|||||
| getInstance | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 4 |
|||
| searchExamRegistrations | |
0.00% |
0 / 1 |
90.00 | |
0.00% |
0 / 55 |
|||
| getExamRegistrationById | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 29 |
|||
| saveExamRegistration | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 48 |
|||
| removeExamRegistrationBatches | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 9 |
|||
| checkIsStudentRegistered | |
0.00% |
0 / 1 |
30.00 | |
0.00% |
0 / 20 |
|||
| assignBatchesToExamRegistration | |
0.00% |
0 / 1 |
30.00 | |
0.00% |
0 / 19 |
|||
| updateExamRegistrationBatch | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 14 |
|||
| updateExamRegistrationBatchAttnClosingDate | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 14 |
|||
| getExamRegistrationFees | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 27 |
|||
| saveExamRegistrationFees | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 17 |
|||
| getExamRegistrationFine | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 29 |
|||
| saveExamRegistrationFine | |
0.00% |
0 / 1 |
42.00 | |
0.00% |
0 / 27 |
|||
| getExamRegistrationBatches | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 32 |
|||
| getExamRegistrationSubjectFees | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 49 |
|||
| saveExamRegistrationSubjectFees | |
0.00% |
0 / 1 |
30.00 | |
0.00% |
0 / 25 |
|||
| enableDisableHallTicket | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 9 |
|||
| deleteExamRegistration | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 10 |
|||
| deleteExamRegistrationSubjectFees | |
0.00% |
0 / 1 |
90.00 | |
0.00% |
0 / 29 |
|||
| saveExamPaymentOption | |
0.00% |
0 / 1 |
72.00 | |
0.00% |
0 / 34 |
|||
| enableDisableHallTicketPreview | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 9 |
|||
| getExamRegistrationForBatch | |
0.00% |
0 / 1 |
42.00 | |
0.00% |
0 / 29 |
|||
| regularExamResultPublishDetails | |
0.00% |
0 / 1 |
42.00 | |
0.00% |
0 / 36 |
|||
| getStudentRegularExamResultWithHeldDetails | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 20 |
|||
| getStudentSupplyExamResultWithHeldDetails | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 20 |
|||
| verifyStudentRegularExamPayment | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 26 |
|||
| getStudentExamHallTicketBlockedStatus | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 24 |
|||
| getStudentExamRegistrationBySem | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 17 |
|||
| getAllRegisteredRegularExams | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 15 |
|||
| getExamRegSubjectDetails | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 18 |
|||
| saveStudentExamTotalFees | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 16 |
|||
| getStudentExamTotalFees | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 11 |
|||
| withheldStudentExamResultByExamRegistration | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 14 |
|||
| enableDisableNominalRole | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 10 |
|||
| getNominalRoleStatusByExamRegistration | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 17 |
|||
| getExamRegistrationsByAdmissionYear | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 18 |
|||
| getExamDatesByExamRegistration | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 20 |
|||
| getExamRegistrationsUpToAdmissionYear | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 18 |
|||
| getExamDatesByExamRegistrationRequest | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 13 |
|||
| getExamRegisteredStudentsByExamRegId | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 22 |
|||
| getDistinctExamRegistrationByRequest | |
0.00% |
0 / 1 |
30.00 | |
0.00% |
0 / 28 |
|||
| getDistinctExamRegisteredSubjectsByRequest | |
0.00% |
0 / 1 |
30.00 | |
0.00% |
0 / 32 |
|||
| getExamDatesBySupplyRegistration | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 12 |
|||
| examRegisteredExamStudentsByBatchAndExamRegId | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 18 |
|||
| getSubjectListReg | |
0.00% |
0 / 1 |
42.00 | |
0.00% |
0 / 31 |
|||
| getDistinctExamRegBatchesByRequest | |
0.00% |
0 / 1 |
30.00 | |
0.00% |
0 / 30 |
|||
| getStudentCountDetailsReg | |
0.00% |
0 / 1 |
42.00 | |
0.00% |
0 / 32 |
|||
| getStudentExamHallTicketBlockedSubjects | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 20 |
|||
| getPublishedExamRegistrationsByRequest | |
0.00% |
0 / 1 |
56.00 | |
0.00% |
0 / 39 |
|||
| getExamRegisteredStudentStatusByRequest | |
0.00% |
0 / 1 |
56.00 | |
0.00% |
0 / 44 |
|||
| getStudentExamMaxMarkBySemId | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 20 |
|||
| finalizeRegularExamDetails | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 9 |
|||
| getRegularExamFinalizeDetails | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 10 |
|||
| setExamRegBatchPublishDates | |
0.00% |
0 / 1 |
30.00 | |
0.00% |
0 / 23 |
|||
| getAllStudentExamRegBlockedStatusByRequest | |
0.00% |
0 / 1 |
30.00 | |
0.00% |
0 / 50 |
|||
| editStudentRegularExamPayment | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 30 |
|||
| getExamRegDetailsByQpCode | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 19 |
|||
| searchExamQpCodeByString | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 14 |
|||
| getExamDetailsByExamDateTime | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 17 |
|||
| getStudentExamRegistrationDetailsByStudent | |
0.00% |
0 / 1 |
42.00 | |
0.00% |
0 / 31 |
|||
| getStudentPseudoSubjectsByExamRegistration | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 23 |
|||
| getExamFeeDetailsByExamRegistration | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 12 |
|||
| getExamFineDetailsByExamRegistration | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 13 |
|||
| saveStudentRegularExamRegistration | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 21 |
|||
| getStudentExamRegistrationApplySection | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 24 |
|||
| getExamDatesFromExamRegistrations | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 16 |
|||
| getExamRegistrationForBatchByRequest | |
0.00% |
0 / 1 |
72.00 | |
0.00% |
0 / 37 |
|||
| verifyMoocStudentCertificates | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 10 |
|||
| getExamRegistrationsByDate | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 30 |
|||
| getWithheldExamRegistrationsByStudent | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 11 |
|||
| getExamRegisteredStudentBySubjectRequest | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 32 |
|||
| getStudentRegularExamResultWithHeldReasonDetails | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 28 |
|||
| getStudentSupplyExamResultWithHeldReasonDetails | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 28 |
|||
| getRegisteredRegularExamsByRequest | |
0.00% |
0 / 1 |
30.00 | |
0.00% |
0 / 35 |
|||
| getExamRegistrationsByValuationDate | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 18 |
|||
| getExamRegistrationsByCourseType | |
0.00% |
0 / 1 |
30.00 | |
0.00% |
0 / 23 |
|||
| getStudentDetailsForExamRegistration | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 20 |
|||
| getExamRegDetailsByType | |
0.00% |
0 / 1 |
90.00 | |
0.00% |
0 / 43 |
|||
| getExamRegPublishStatusByExamType | |
0.00% |
0 / 1 |
56.00 | |
0.00% |
0 / 18 |
|||
| getStudentRegisteredExamRegistrations | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 20 |
|||
| getExamRegisteredStudentSubjectListByRequest | |
0.00% |
0 / 1 |
30.00 | |
0.00% |
0 / 39 |
|||
| excludeStudentsExamRegBlockByFeeDue | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 13 |
|||
| getExamFeeReservationCategoryListByRequest | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 20 |
|||
| updateExamFeeReservationCategoryListByRequest | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 25 |
|||
| getExamRegistartionMonthYearByExamId | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 13 |
|||
| getStudentRegularSupplyExamRegWithHeldStatus | |
0.00% |
0 / 1 |
110.00 | |
0.00% |
0 / 33 |
|||
| getAllExamRegisteredStudents | |
0.00% |
0 / 1 |
462.00 | |
0.00% |
0 / 121 |
|||
| getStudentExamResultBlockedDetails | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 26 |
|||
| getStudentExamRegBlockStatus | |
0.00% |
0 / 1 |
30.00 | |
0.00% |
0 / 23 |
|||
| getStudentExamRegFeePaidDetails | |
0.00% |
0 / 1 |
110.00 | |
0.00% |
0 / 40 |
|||
| getExamsByExamRegAndDate | |
0.00% |
0 / 1 |
30.00 | |
0.00% |
0 / 27 |
|||
| getOeExamDetailsByExamId | |
0.00% |
0 / 1 |
30.00 | |
0.00% |
0 / 23 |
|||
| getExamDetailsByRequest | |
0.00% |
0 / 1 |
72.00 | |
0.00% |
0 / 36 |
|||
| getStudentAttendedExamsBySem | |
0.00% |
0 / 1 |
42.00 | |
0.00% |
0 / 28 |
|||
| getExamRegisteredStudentsWithSubjects | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 32 |
|||
| updateExcludeHallTicketBlockSubjectStatus | |
0.00% |
0 / 1 |
42.00 | |
0.00% |
0 / 12 |
|||
| getStudentSupplyExamRegistrationsByRegularExamId | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 15 |
|||
| getMoocExamRegistrationStudentSubject | |
0.00% |
0 / 1 |
72.00 | |
0.00% |
0 / 38 |
|||
| updateMoocExamRegistrationStudentSubject | |
0.00% |
0 / 1 |
72.00 | |
0.00% |
0 / 29 |
|||
| checkStudentExamRegEligibilityByDayWiseAttendance | |
0.00% |
0 / 1 |
462.00 | |
0.00% |
0 / 50 |
|||
| checkHaveMinimumAttendance | |
0.00% |
0 / 1 |
2.00 | |
0.00% |
0 / 3 |
|||
| isEligibleForCondonation | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 7 |
|||
| getWorkFlowIdByExamRegId | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 12 |
|||
| checkEligibleForCondonation | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 12 |
|||
| getAttendanceClosingDateByStudentSem | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 13 |
|||
| getExamRegistrationDetailsByExamId | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 12 |
|||
| getRegularMalStudentsBySupplyRegId | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 19 |
|||
| <?php | |
| namespace com\linways\core\ams\professional\service\examcontroller; | |
| use com\linways\core\ams\professional\dto\examcontroller\ExamRegistration; | |
| use com\linways\core\ams\professional\exception\ProfessionalException; | |
| use com\linways\core\ams\professional\mapper\ExamRegistrationServiceMapper; | |
| use com\linways\core\ams\professional\service\BaseService; | |
| use com\linways\core\ams\professional\service\StudentService; | |
| use com\linways\core\ams\professional\dto\ExamType; | |
| use com\linways\core\ams\professional\dto\SettingsConstents; | |
| use com\linways\core\ams\professional\service\AttendanceService; | |
| use com\linways\core\ams\professional\service\CommonService; | |
| use com\linways\core\ams\professional\service\ExamService; | |
| use Symfony\Component\Routing\RouterInterface; | |
| use stdClass; | |
| use com\linways\core\ams\professional\service\SubjectService; | |
| use com\linways\core\ams\professional\constant\examcontroller\CourseTypeConstants; | |
| use com\linways\core\ams\professional\service\ExamSupplementaryService; | |
| class ExamRegistrationService extends BaseService { | |
| // /Condition 1 - Presence of a static member variable | |
| private static $_instance = null; | |
| private $mapper = []; | |
| // /Condition 2 - Locked down the constructor | |
| private function __construct() { | |
| $this->mapper = ExamRegistrationServiceMapper::getInstance()->getMapper(); | |
| } | |
| // Prevent any oustide instantiation of this class | |
| // /Condition 3 - Prevent any object or instance of that class to be cloned | |
| private function __clone() { } | |
| // Prevent any copy of this object | |
| // /Condition 4 - Have a single globally accessible static method | |
| public static function getInstance() { | |
| if (! is_object ( self::$_instance )) // or if( is_null(self::$_instance) ) or if( self::$_instance == null ) | |
| self::$_instance = new self (); | |
| return self::$_instance; | |
| } | |
| /** | |
| * @author Vishnu M | |
| */ | |
| public function searchExamRegistrations ( $request ) { | |
| $request = $this->realEscapeObject ($request); | |
| $conditions = null; | |
| $response = null; | |
| $limitCondition = null; | |
| if ( $request->id ) { | |
| $conditions .= " AND er.examregID = '$request->id' "; | |
| } | |
| // if ( $request->semId ) { | |
| // $conditions .= " AND es.semID = $request->semId "; | |
| // } | |
| if ( $request->name ) { | |
| $conditions .= " AND er.examregName LIKE '%".$request->name."%' "; | |
| } | |
| $sortBy = "er.examregID"; | |
| $sortOrder = "DESC"; | |
| if ( $request->sortBy ) { | |
| $sortBy = $request->sortBy; | |
| $sortOrder = $request->sortOrder; | |
| } | |
| if ( $request->startIndex !== "" && $request->startIndex !== null ) { | |
| if ( $request->startIndex !== "" && $request->recordsPerPage ) { | |
| $limitCondition = " LIMIT $request->startIndex , $request->recordsPerPage "; | |
| } | |
| } | |
| $sql = "SELECT | |
| er.examregID, | |
| er.examregName, | |
| er.examregDesc, | |
| er.enable_hlticket, | |
| er.examMonth, | |
| er.examYear, | |
| er.min_att_percent, | |
| er.shortCourse, | |
| er.simage, | |
| er.pimage, | |
| er.allowNotification, | |
| IF(er.examDate != '0000-00-00 00:00:00', er.examDate, null) AS examDate , | |
| IF(er.lastAPC != '0000-00-00 00:00:00', er.lastAPC, null) AS lastAPC, | |
| IF(er.regStartDate != '0000-00-00 00:00:00', er.regStartDate, null) AS regStartDate , | |
| IF(er.rgstnWithoutFine != '0000-00-00 00:00:00', er.rgstnWithoutFine, null) AS rgstnWithoutFine, | |
| IF(er.attClosingDate != '0000-00-00 00:00:00', er.attClosingDate, null) AS attClosingDate, | |
| er.enable_hlticket_exam, | |
| er.enableNominalRole | |
| FROM | |
| exam_registration er | |
| WHERE | |
| er.examregID IS NOT NULL | |
| $conditions ORDER BY $sortBy $sortOrder $limitCondition "; | |
| try { | |
| $sqlCount = "SELECT COUNT(er.examregID) as totalRecords FROM exam_registration er WHERE er.examregID IS NOT NULL $conditions "; | |
| $response->totalRecords = $this->executeQueryForObject($sqlCount)->totalRecords; | |
| $response->examRegistrations = $this->executeQueryForList($sql, | |
| $this->mapper[ExamRegistrationServiceMapper::GET_EXAM_REGISTRATION_DETAILS]); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); | |
| } | |
| return $response; | |
| } | |
| /** | |
| * WIP : Get exam registration's all details | |
| * @param int $examRegId | |
| * @return object|NULL|\com\linways\base\util\$objectList[] | |
| * @throws ProfessionalException | |
| * @author Vishnu M | |
| */ | |
| public function getExamRegistrationById($examRegId) { | |
| $examRegId = $this->realEscapeString($examRegId); | |
| $sql = "SELECT | |
| er.examregID, | |
| er.examregName, | |
| er.examregDesc, | |
| er.enable_hlticket, | |
| er.examMonth, | |
| er.examYear, | |
| er.min_att_percent, | |
| er.shortCourse, | |
| er.simage, | |
| er.pimage, | |
| IF(er.examDate != '0000-00-00 00:00:00', er.examDate, null) AS examDate , | |
| IF(er.lastAPC != '0000-00-00 00:00:00', er.lastAPC, null) AS lastAPC, | |
| IF(er.regStartDate != '0000-00-00 00:00:00', er.regStartDate, null) AS regStartDate , | |
| IF(er.rgstnWithoutFine != '0000-00-00 00:00:00', er.rgstnWithoutFine, null) AS rgstnWithoutFine, | |
| IF(er.attClosingDate != '0000-00-00 00:00:00', er.attClosingDate, null) AS attClosingDate | |
| FROM | |
| exam_registration er | |
| WHERE | |
| er.examregID = $examRegId "; | |
| try { | |
| $examRegistration = reset($this->executeQueryForList($sql, | |
| $this->mapper[ExamRegistrationServiceMapper::GET_EXAM_REGISTRATION_DETAILS])); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $examRegistration; | |
| } | |
| /** | |
| * @param ExamRegistration $examRegistration | |
| * @return \com\linways\base\dto\MySqlResult|null | |
| * @throws ProfessionalException | |
| * @author Vishnu M | |
| */ | |
| public function saveExamRegistration ( ExamRegistration $examRegistration ) { | |
| $examRegistration = $this->realEscapeObject($examRegistration); | |
| try { | |
| if ( $examRegistration->id ) { | |
| $sql = "UPDATE exam_registration SET | |
| examregName = '$examRegistration->name', | |
| examregDesc = '$examRegistration->description', | |
| enable_hlticket = '$examRegistration->enableHallTicket', | |
| examDate = '$examRegistration->examDate', | |
| lastAPC = '$examRegistration->lastAPC', | |
| regStartDate = '$examRegistration->registrationStartDate', | |
| rgstnWithoutFine = '$examRegistration->registrationWithoutFine', | |
| examMonth = '$examRegistration->examMonth', | |
| examYear = '$examRegistration->examYear', | |
| min_att_percent = '$examRegistration->minAttPercentage', | |
| attClosingDate = '$examRegistration->attClosingDate', | |
| shortCourse = '$examRegistration->shortCourse', | |
| allowNotification= '$examRegistration->allowNotification', | |
| simage = '$examRegistration->signatureImage', | |
| pimage = '$examRegistration->profileImage' | |
| WHERE examregID = $examRegistration->id "; | |
| $this->executeQuery($sql); | |
| $examRegId = $examRegistration->id; | |
| } | |
| else { | |
| $sql = "INSERT INTO exam_registration (examregName, examregDesc, enable_hlticket, examDate, lastAPC, regStartDate, rgstnWithoutFine, examMonth, examYear, min_att_percent, attClosingDate, shortCourse, simage, pimage, allowNotification) VALUES ( | |
| '$examRegistration->name', | |
| '$examRegistration->description', | |
| '$examRegistration->enableHallTicket', | |
| '$examRegistration->examDate', | |
| '$examRegistration->lastAPC', | |
| '$examRegistration->registrationStartDate', | |
| '$examRegistration->registrationWithoutFine', | |
| '$examRegistration->examMonth', | |
| '$examRegistration->examYear', | |
| '$examRegistration->minAttPercentage', | |
| '$examRegistration->attClosingDate', | |
| '$examRegistration->shortCourse', | |
| '$examRegistration->signatureImage', | |
| '$examRegistration->profileImage', | |
| '$examRegistration->allowNotification' | |
| )"; | |
| $examRegId = $this->executeQueryForObject($sql, true); | |
| } | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $examRegId; | |
| } | |
| /** | |
| * Delete Exam registration batches | |
| * @param $examRegId | |
| * @param $batchId | |
| * @throws ProfessionalException | |
| * @author Vishnu M | |
| */ | |
| public function removeExamRegistrationBatches ( $examRegId , $batchId ) { | |
| $examRegId = $this->realEscapeString($examRegId); | |
| $batchId = $this->realEscapeString($batchId); | |
| try { | |
| $sql = "DELETE FROM exam_registration_batches WHERE examregID = '$examRegId' AND batchID = '$batchId' "; | |
| $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * Check whether students are registered for that exam registration | |
| * @param $request | |
| * @return Integer|null | |
| * @throws ProfessionalException | |
| * @author Vishnu M | |
| */ | |
| public function checkIsStudentRegistered ( $request ) { | |
| $request = $this->realEscapeObject($request); | |
| $conditions = null; | |
| $studentCount = null; | |
| if ( $request->batchId ) { | |
| $conditions .= " AND sa.batchID = '$request->batchId' "; | |
| } | |
| if ( $request->subjectId ) { | |
| $conditions .= " AND erss.subjectID = '$request->subjectId' "; | |
| } | |
| if ( $request->studentId ) { | |
| $conditions .= " AND ersc.studentID = '$request->studentId' "; | |
| } | |
| try { | |
| $sql = "SELECT COUNT( DISTINCT sa.studentID) AS studentCount FROM exam_reg_studentchallan ersc INNER JOIN exam_reg_studentsubject erss ON (ersc.studentID = erss.studentID AND ersc.examregID = erss.examregID) INNER JOIN studentaccount sa ON (ersc.studentID = sa.studentID AND erss.studentID = sa.studentID) WHERE ersc.examregID = '$request->examRegId' $conditions"; | |
| $studentCount = $this->executeQueryForObject($sql)->studentCount; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $studentCount; | |
| } | |
| /** | |
| * Assign Exam Registration Batches | |
| * @param $request | |
| * @throws ProfessionalException | |
| * @author Vishnu M | |
| */ | |
| public function assignBatchesToExamRegistration ( $request ) { | |
| $request = $this->realEscapeObject($request); | |
| try { | |
| if ( $request->examRegId && !empty ( $request->batches ) ) { | |
| foreach ($request->batches as $batch) { | |
| $batch = (object) $batch; | |
| $batch->attClosingDate = date("Y-m-d", strtotime($batch->attClosingDate)); | |
| $values[] = "(" . $request->examRegId . ", " . $batch->id . ", " . $batch->semId . ", '" . $batch->attClosingDate . "')"; | |
| } | |
| $sql = "INSERT INTO exam_registration_batches (examregID, batchID, semID, attClosingDate) VALUES " . | |
| implode(",", $values) ." ON DUPLICATE KEY UPDATE attClosingDate = VALUES(attClosingDate) "; | |
| $this->executeQuery($sql); | |
| } | |
| else { | |
| throw new ProfessionalException(ProfessionalException::DATA_EMPTY, "Please provide a valid exam registration and batch"); | |
| } | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * Update exam_registration_batches details | |
| * @param $request | |
| * @throws ProfessionalException | |
| * @author Vishnu M | |
| */ | |
| public function updateExamRegistrationBatch ( $request ) { | |
| $request = $this->realEscapeObject($request); | |
| try { | |
| if ( $request->examRegId && !empty ( $request->batch ) ) { | |
| $batch = $request->batch; | |
| $sql = "UPDATE exam_registration_batches SET attClosingDate = '$batch->attClosingDate' WHERE batchID = '$batch->id' AND examregID = '$request->examRegId' "; | |
| $this->executeQuery($sql); | |
| } | |
| else { | |
| throw new ProfessionalException(ProfessionalException::DATA_EMPTY, "Please provide a valid exam registration and batch"); | |
| } | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * Updating attendance closing date of all batches in an exam registration | |
| * @param $examRegId | |
| * @param $attClosingDate | |
| * @throws ProfessionalException | |
| * @author Vishnu M | |
| */ | |
| public function updateExamRegistrationBatchAttnClosingDate ( $examRegId, $attClosingDate ) { | |
| $examRegId = $this->realEscapeString($examRegId); | |
| $attClosingDate = $this->realEscapeString($attClosingDate); | |
| try { | |
| if ( $examRegId && $attClosingDate ) { | |
| $sql = "UPDATE exam_registration_batches SET attClosingDate = '$attClosingDate' WHERE examregID = '$examRegId' "; | |
| $this->executeQuery($sql); | |
| } | |
| else { | |
| throw new ProfessionalException(ProfessionalException::DATA_EMPTY, "Please provide a valid exam registration and batch"); | |
| } | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * @param $examRegId | |
| * @return Object|null | |
| * @throws ProfessionalException | |
| * @author Vishnu M | |
| */ | |
| public function getExamRegistrationFees ( $examRegId ) { | |
| $examRegId = $this->realEscapeString($examRegId); | |
| $examRegistrationFees = null; | |
| try { | |
| if ( $examRegId) { | |
| $sql = "SELECT | |
| erf.examregID AS examRegId, | |
| eft.examFeesID AS examFeeId, | |
| eft.examfeesName AS examFeesName, | |
| eft.isTheory, | |
| eft.everySubject, | |
| eft.isSubject_fee_limit AS isSubjectFeeLimit, | |
| erf.examfeesAmount AS feeAmount | |
| FROM | |
| exam_feestype eft | |
| LEFT JOIN | |
| exam_registration_fees erf ON (erf.examFeesID = eft.examFeesID | |
| AND erf.examregID = '$examRegId' )"; | |
| $examRegistrationFees = $this->executeQueryForList($sql); | |
| } | |
| else { | |
| throw new ProfessionalException(ProfessionalException::DATA_EMPTY, "Please provide a valid exam registration"); | |
| } | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $examRegistrationFees; | |
| } | |
| /** | |
| * @param $examRegId | |
| * @param $fees | |
| * @throws ProfessionalException | |
| * @author Vishnu M | |
| */ | |
| public function saveExamRegistrationFees ( $examRegId, $fees ) { | |
| $examRegId = $this->realEscapeString($examRegId); | |
| $fees = $this->realEscapeArray($fees); | |
| $values = []; | |
| try { | |
| $sqlDelete = "DELETE FROM exam_registration_fees WHERE examregID = $examRegId "; | |
| $this->executeQuery($sqlDelete); | |
| foreach ( $fees as $fee ) { | |
| $fee = (Object) $fee; | |
| $values[] = "( $examRegId , $fee->examFeeId, $fee->feeAmount )"; | |
| } | |
| $sql = "INSERT INTO exam_registration_fees (examregID, examfeesID, examfeesAmount) VALUES " . implode | |
| (",", $values ); | |
| $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * @param $examRegId | |
| * @return Object|null | |
| * @throws ProfessionalException | |
| * @author Vishnu M | |
| */ | |
| public function getExamRegistrationFine ( $examRegId ) { | |
| $examRegId = $this->realEscapeString($examRegId); | |
| $examRegistrationFine = null; | |
| try { | |
| if ( $examRegId) { | |
| $sql = "SELECT | |
| erf.examregID AS examRegId, | |
| eft.examfineID AS examFineId, | |
| eft.examfineName AS examFeesName, | |
| erf.examfineAmount AS fineAmount, | |
| erf.startDate AS fineStartDate, | |
| erf.lastDate AS fineEndDate, | |
| erf.verificationDate, | |
| eft.priority | |
| FROM | |
| exam_finetype eft | |
| LEFT JOIN | |
| exam_registration_fine erf ON (erf.examfineID = eft.examfineID | |
| AND erf.examregID = '$examRegId') | |
| ORDER BY eft.priority ASC"; | |
| $examRegistrationFine = $this->executeQueryForList($sql); | |
| } | |
| else { | |
| throw new ProfessionalException(ProfessionalException::DATA_EMPTY, "Please provide a valid exam registration"); | |
| } | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $examRegistrationFine; | |
| } | |
| /** | |
| * Save Exam registration Fine | |
| * @param $examRegId | |
| * @param $fines | |
| * @return mixed | |
| * @throws ProfessionalException | |
| * @author Vishnu M | |
| */ | |
| public function saveExamRegistrationFine($examRegId , $fines) { | |
| $examRegId = $this->realEscapeString($examRegId); | |
| $fines = $this->realEscapeObject($fines); | |
| $sql = null; | |
| $fines = $this->realEscapeArray($fines); | |
| $values = []; | |
| try { | |
| $sqlDelete = "DELETE FROM exam_registration_fine WHERE examregID = $examRegId "; | |
| $this->executeQuery($sqlDelete); | |
| foreach ( $fines as $fine ) { | |
| $fine = (Object) $fine; | |
| if ( $fine->fineAmount == "" || $fine->fineAmount == "null" ) { | |
| continue; | |
| } | |
| $fine->fineEndDate = date("Y-m-d 23:59:59", strtotime($fine->fineEndDate)); | |
| $fine->fineStartDate = date("Y-m-d 00:00:00", strtotime($fine->fineStartDate)); | |
| $fine->verificationDate = date("Y-m-d", strtotime($fine->verificationDate)); | |
| $values[] = "( $examRegId , $fine->examFineId, $fine->fineAmount , '$fine->fineEndDate', '$fine->fineStartDate', '$fine->verificationDate' )"; | |
| } | |
| if ( !empty ( $values ) ) { | |
| $sql = "INSERT INTO exam_registration_fine (examregID, examfineID, examfineAmount, lastDate, startDate, verificationDate) VALUES " . implode | |
| (",", $values); | |
| $this->executeQuery($sql); | |
| } | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException ($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * Get all batches corresponding to an exam registration | |
| * @param $examRegId | |
| * @return Object|null | |
| * @throws ProfessionalException | |
| * @author Vishnu M | |
| */ | |
| public function getExamRegistrationBatches ( $examRegId, $semId = null ) { | |
| $examRegId = $this->realEscapeString($examRegId); | |
| $semId = $this->realEscapeArray($semId); | |
| $sqlCondition = ""; | |
| if (!empty($semId)) { | |
| $semIdString = is_array($semId) ? implode(",", $semId) : $semId; | |
| $sqlCondition .= " AND erb.semID IN ($semIdString) "; | |
| } | |
| $examRegistrationBatches = null; | |
| try { | |
| $sql = "SELECT | |
| erb.examregID, | |
| erb.batchID, | |
| b.batchName, | |
| b.batchDesc, | |
| erb.semID, | |
| erb.publish, | |
| erb.attClosingDate, | |
| erb.publish_fromDate, | |
| erb.publish_toDate | |
| FROM | |
| exam_registration_batches erb | |
| INNER JOIN | |
| batches b ON (b.batchID = erb.batchID) | |
| WHERE | |
| examregID = '$examRegId' | |
| $sqlCondition"; | |
| $examRegistrationBatches = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $examRegistrationBatches; | |
| } | |
| /** | |
| * Get all subject registration fees by exam reg id | |
| * @param $examRegId | |
| * @return Object|null | |
| * @throws ProfessionalException | |
| * @author Vishnu M | |
| */ | |
| public function getExamRegistrationSubjectFees( $examRegId ) { | |
| $examRegId = $this->realEscapeString( $examRegId); | |
| $sql = null; | |
| $examRegistrationSubjectFees = null; | |
| try { | |
| $sql = "SELECT DISTINCT | |
| s.subjectID, | |
| s.subjectName, | |
| s.syllabusName, | |
| s.subjectDesc, | |
| s.isTheory, | |
| b.batchID, | |
| b.batchName, | |
| b.batchDesc, | |
| sbs.semID, | |
| eft.examfeesID, | |
| ersf.examfeesAmount, | |
| IF(ersf.subjectID IS NULL, 0, 1) AS isChecked, | |
| erb.examregID, | |
| erf.examfeesAmount AS examRegFeesAmount, | |
| sc.subjectcatName | |
| FROM | |
| sbs_relation sbs | |
| INNER JOIN | |
| exam_registration_batches erb ON (erb.batchID = sbs.batchID | |
| AND erb.semID = sbs.semID) | |
| INNER JOIN | |
| subjects s ON (s.subjectID = sbs.subjectID) | |
| INNER JOIN | |
| batches b ON (b.batchID = sbs.batchID) | |
| INNER JOIN | |
| exam_feestype eft ON (eft.isTheory = s.isTheory | |
| AND eft.everySubject = 1) | |
| LEFT JOIN | |
| exam_registration_subject_fees ersf ON (erb.examregID = ersf.examregID | |
| AND ersf.subjectID = sbs.subjectID | |
| AND ersf.batchID = sbs.batchID | |
| AND ersf.semID = sbs.semID) | |
| LEFT JOIN | |
| exam_registration_fees erf ON (erb.examregID = erf.examregID | |
| AND erf.examfeesID = eft.examfeesID) | |
| LEFT JOIN subject_category sc ON sc.subjectcatID = s.subjectcatID | |
| WHERE | |
| erb.examregID = '$examRegId' "; | |
| $examRegistrationSubjectFees = $this->executeQueryForList($sql, $this->mapper[ExamRegistrationServiceMapper::GET_EXAM_REGISTRATION_SUBJECT_FEE_DETAILS]); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException ($e->getCode(), $e->getMessage()); | |
| } | |
| return $examRegistrationSubjectFees; | |
| } | |
| /** | |
| * @param $examRegId | |
| * @param $batch | |
| * @throws ProfessionalException | |
| * @author Vishnu M | |
| */ | |
| public function saveExamRegistrationSubjectFees($examRegId, $batch ) { | |
| $examRegId = $this->realEscapeString($examRegId); | |
| $batch = $this->realEscapeObject($batch); | |
| $sql = null; | |
| $values = null; | |
| try { | |
| foreach ($batch->subjects as $subject) { | |
| $subject = (Object) $subject; | |
| if ( $subject->feeAmount == "null" || $subject->feeAmount == "" ) { | |
| continue; | |
| } | |
| $values[] = "( | |
| $examRegId, | |
| $subject->id, | |
| $batch->id, | |
| $batch->semId, | |
| $subject->feeId, | |
| $subject->feeAmount | |
| )"; | |
| } | |
| $sql = "INSERT INTO exam_registration_subject_fees (examregID, subjectID, batchID, semID, examfeesID, examfeesAmount) VALUES " . implode(",", $values) . " ON DUPLICATE KEY UPDATE examfeesAmount = VALUES(examfeesAmount)"; | |
| $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException ($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * @param $examRegId | |
| * @throws ProfessionalException | |
| * @author Vishnu M | |
| */ | |
| public function enableDisableHallTicket ( $examRegId ) { | |
| $examRegId = $this->realEscapeString($examRegId); | |
| $sql = null; | |
| try { | |
| $sql = "UPDATE exam_registration SET enable_hlticket = !enable_hlticket WHERE examregID = $examRegId "; | |
| $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException ($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * @param $examRegId | |
| * @throws ProfessionalException | |
| * @author Vishnu M | |
| */ | |
| public function deleteExamRegistration($examRegId) | |
| { | |
| $examRegId = $this->realEscapeString($examRegId); | |
| $sql = null; | |
| try { | |
| $sql = "DELETE FROM exam_registration WHERE examregID = $examRegId "; | |
| $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException ($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * @param $request | |
| * @throws ProfessionalException | |
| */ | |
| public function deleteExamRegistrationSubjectFees($request) { | |
| $request = $this->realEscapeObject($request); | |
| $sql = null; | |
| $sqlCondition = ""; | |
| $subjectIds = is_array($request->subjectId) ? implode(",", $request->subjectId) : $request->subjectId; | |
| if (!empty($request->batchId)) { | |
| $batchIds = is_array($request->batchId) ? implode(",", $request->batchId) : $request->batchId; | |
| $sqlCondition .= " AND batchID IN ($batchIds) "; | |
| } | |
| if (!empty($request->semId)) { | |
| $semIds = is_array($request->semId) ? implode(",", $request->semId) : $request->semId; | |
| $sqlCondition .= " AND semID IN ($semIds) "; | |
| } | |
| if (!empty($request->examfeesId)) { | |
| $examfeesIds = is_array($request->examfeesId) ? implode(",", $request->examfeesId) : $request->examfeesId; | |
| $sqlCondition .= " AND examfeesID IN ($examfeesIds) "; | |
| } | |
| try { | |
| $sql = "DELETE | |
| FROM | |
| exam_registration_subject_fees | |
| WHERE | |
| subjectID IN ($subjectIds) | |
| AND examregID = $request->examregId | |
| $sqlCondition"; | |
| $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException ($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * save Regular Exam Payment option method | |
| * | |
| * @param $examRegId | |
| * @param $examPaymentOptions | |
| * @return void | |
| */ | |
| public function saveExamPaymentOption($examRegId, $examPaymentOptions) { | |
| $examRegId = $this->realEscapeString($examRegId); | |
| $examPaymentOptions = $this->realEscapeArray($examPaymentOptions); | |
| $sql = null; | |
| $sqlCondition = ""; | |
| $registrationType = "regular"; | |
| if (empty($examRegId)) { | |
| throw new ProfessionalException(ProfessionalException::DATA_EMPTY, "Please provide a valid exam registration"); | |
| } | |
| if (empty($examPaymentOptions) || !is_array($examPaymentOptions)) { | |
| throw new ProfessionalException(ProfessionalException::DATA_EMPTY, "Invalid Payment method"); | |
| } | |
| try { | |
| $paymentOptions = [1, 2, 3]; | |
| if ( !empty ( $examPaymentOptions ) ) { | |
| $sql = "INSERT IGNORE INTO exam_paymentmethod_settings (exam_paymentmethod_id, exam_registration_type, exam_registration_type_id) VALUES "; | |
| foreach ( $examPaymentOptions as $paymentOption ) { | |
| $values[] = "( | |
| $paymentOption, | |
| '$registrationType', | |
| $examRegId | |
| )"; | |
| } | |
| $sql .= implode ( ",", $values ); | |
| $this->executeQuery($sql); | |
| $deletePaymentOptions = array_diff($paymentOptions, $examPaymentOptions); | |
| if ( !empty ( $deletePaymentOptions ) ) { | |
| $sql = "DELETE FROM exam_paymentmethod_settings WHERE exam_paymentmethod_id IN (".implode ( ",", $deletePaymentOptions ).") AND exam_registration_type = '$registrationType' AND exam_registration_type_id = $examRegId "; | |
| $this->executeQuery($sql); | |
| } | |
| } | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException ($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * @param $examRegId | |
| * @throws ProfessionalException | |
| */ | |
| public function enableDisableHallTicketPreview ( $examRegId ) { | |
| $examRegId = $this->realEscapeString($examRegId); | |
| $sql = null; | |
| try { | |
| $sql = "UPDATE exam_registration SET enable_hlticket_exam = !enable_hlticket_exam WHERE examregID = $examRegId "; | |
| $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException ($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| public function getExamRegistrationForBatch($batchId, $semId) { | |
| $batchId = $this->realEscapeString($batchId); | |
| $semId = $this->realEscapeString($semId); | |
| $sqlCondition = ""; | |
| if (!empty($batchId)) { | |
| $batchIdString = is_array($batchId) ? implode(",", $batchId) : $batchId; | |
| $sqlCondition .= " AND erb.batchID IN ($batchIdString) "; | |
| } | |
| if (!empty($semId)) { | |
| $semIdString = is_array($semId) ? implode(",", $semId) : $semId; | |
| $sqlCondition .= " AND erb.semID IN ($semIdString) "; | |
| } | |
| $sql = "SELECT | |
| er.examregID AS id, | |
| er.examregName AS name, | |
| er.examregDesc | |
| FROM | |
| exam_registration er | |
| INNER JOIN exam_registration_batches erb ON | |
| erb.examregID = er.examregID | |
| WHERE | |
| er.examregID = er.examregID | |
| $sqlCondition"; | |
| try { | |
| $examRegistration = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $examRegistration; | |
| } | |
| public function regularExamResultPublishDetails ( $examRegId, $semId, $batchId ) { | |
| $examRegId = $this->realEscapeString($examRegId); | |
| $semId = $this->realEscapeArray($semId); | |
| $batchId = $this->realEscapeArray($batchId); | |
| $sqlCondition = ""; | |
| if (!empty($batchId)) { | |
| $batchIdString = is_array($batchId) ? implode(",", $batchId) : $batchId; | |
| $sqlCondition .= " AND erb.batchID IN ($batchIdString) "; | |
| } | |
| else { | |
| throw new ProfessionalException(ProfessionalException::DATA_EMPTY, "Invalid Batch"); | |
| } | |
| if (!empty($semId)) { | |
| $semIdString = is_array($semId) ? implode(",", $semId) : $semId; | |
| $sqlCondition .= " AND erb.semID IN ($semIdString) "; | |
| } | |
| else { | |
| throw new ProfessionalException(ProfessionalException::DATA_EMPTY, "Invalid Semeter"); | |
| } | |
| $examRegistrationBatches = null; | |
| try { | |
| $sql = "SELECT | |
| erb.publish, | |
| erb.attClosingDate, | |
| erb.publish_fromDate AS publishFromDate, | |
| erb.publish_toDate AS publishToDate | |
| FROM | |
| exam_registration_batches erb | |
| WHERE | |
| examregID = '$examRegId' | |
| $sqlCondition"; | |
| $examRegistrationBatch = $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $examRegistrationBatch; | |
| } | |
| public function getStudentRegularExamResultWithHeldDetails ($examRegId, $studentId) { | |
| $examRegId = $this->realEscapeString($examRegId); | |
| $studentId = $this->realEscapeArray($studentId); | |
| $sqlCondition = ""; | |
| $withHeldData = null; | |
| try { | |
| $sql = "SELECT | |
| esw.withheld,erbr.reason,erbr.otherDetails,sa.staffName | |
| FROM | |
| exam_students_withheld esw | |
| LEFT JOIN exam_reg_block_reason erbr ON erbr.id = esw.reasonId | |
| LEFT JOIN staffaccounts sa ON sa.staffID = erbr.contact_person_id | |
| WHERE | |
| esw.examregID = $examRegId | |
| AND esw.studentID = $studentId"; | |
| $withHeldData = $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $withHeldData; | |
| } | |
| public function getStudentSupplyExamResultWithHeldDetails ($examRegId, $studentId) { | |
| $examRegId = $this->realEscapeString($examRegId); | |
| $studentId = $this->realEscapeArray($studentId); | |
| $sqlCondition = ""; | |
| $withHeldData = null; | |
| try { | |
| $sql = "SELECT | |
| esw.studentID,esw.withheld,erbr.reason,erbr.otherDetails,sa.staffName | |
| FROM | |
| supplyexam_students_withheld esw | |
| LEFT JOIN exam_reg_block_reason erbr ON erbr.id = esw.reasonId | |
| LEFT JOIN staffaccounts sa ON sa.staffID = erbr.contact_person_id | |
| WHERE | |
| esw.exam_supplementary_id = $examRegId | |
| AND esw.studentID = $studentId"; | |
| $withHeldData = $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $withHeldData; | |
| } | |
| /**to verify student regular exam payment | |
| * @param $studentID, $examregID,$isPaid, $updated_by, $updated_date,$remarks | |
| * @throws ProfessionalException | |
| * @author Sibin | |
| */ | |
| public function verifyStudentRegularExamPayment($studentID, $examregID,$isPaid, $updated_by, $updated_date,$remarks = NULL) | |
| { | |
| $studentId = $this->realEscapeString($studentID); | |
| $examRegId = $this->realEscapeString($examregID); | |
| $isPaid = $this->realEscapeString($isPaid); | |
| $updated_by = $this->realEscapeString($updated_by); | |
| $updated_date = $this->realEscapeString($updated_date); | |
| $remarks = $this->realEscapeString($remarks); | |
| $remarkEntry = $remarks ? ", remarks = '$remarks' " :""; | |
| $sql = null; | |
| $status=0; | |
| try { | |
| $sql ="UPDATE exam_reg_studentchallan | |
| set paid='$isPaid', | |
| updated_by='$updated_by', | |
| updated_date='$updated_date' , | |
| dateofPay ='$updated_date' , | |
| payment_method = 'BACK_END' | |
| $remarkEntry | |
| where studentID='$studentId' and examregID='$examRegId'"; | |
| $this->executeQuery($sql); | |
| $status=1; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $status; | |
| } | |
| /**to get Student Exam Hall Ticket Blocked Status | |
| * @param $studentID, $examregID,$isSupply | |
| * @throws ProfessionalException | |
| * @author Sibin | |
| */ | |
| public function getStudentExamHallTicketBlockedStatus($studentID, $examregID,$isSupply) { | |
| $studentId = $this->realEscapeString($studentID); | |
| $examRegId = $this->realEscapeString($examregID); | |
| $isSupply = $this->realEscapeString($isSupply); | |
| $sql = null; | |
| $isBlocked = false; | |
| try { | |
| if($isSupply){ | |
| $sql = "SELECT id,student_id from exam_reg_blocked_student | |
| where student_id ='$studentId' and supplyreg_id='$examRegId'"; | |
| }else{ | |
| $sql = "SELECT id,student_id from exam_reg_blocked_student | |
| where student_id ='$studentId' and examreg_id='$examRegId'"; | |
| } | |
| $studentEntries = $this->executeQueryForList($sql); | |
| if(empty($studentEntries)){ | |
| $isBlocked = false; | |
| }else{ | |
| $isBlocked = true;; | |
| } | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $isBlocked; | |
| } | |
| /** | |
| * @param $batchId,semId | |
| * @throws ProfessionalException | |
| * @author Sibin | |
| */ | |
| public function getStudentExamRegistrationBySem($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $fetchedBatchID = StudentService::getInstance()->getStudentPreviousBatchBySemId($request->studentId, $request->semId); | |
| $request->batchId = $fetchedBatchID ? $fetchedBatchID : $request->batchId; | |
| $sql = null; | |
| $examReg = null; | |
| try { | |
| $sql = "SELECT e.examregID, e.examMonth, e.examYear, erb.publish, erb.publish_fromDate, erb.publish_toDate | |
| from exam_registration e | |
| INNER JOIN exam_registration_batches erb where e.examregID=erb.examregID and erb.batchID='$request->batchId' and erb.semID='$request->semId' | |
| and e.shortCourse = 0"; | |
| $examReg = $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $examReg; | |
| } | |
| /** | |
| * @author Sibin | |
| */ | |
| public function getAllRegisteredRegularExams($showFutureExamsOnly = FALSE) | |
| { | |
| $sql = "SELECT examregID AS id, examregName AS name,examMonth,examYear FROM exam_registration "; | |
| if ($showFutureExamsOnly) { | |
| $month = (int)date("m"); | |
| $year = (int)date("Y") - 1; | |
| $sql .= " WHERE examYear >= $year"; | |
| } | |
| $sql .= " ORDER BY IF(CAST(examYear AS SIGNED) = 0, 99999, CAST(examYear AS SIGNED)) DESC,IF(CAST(examMonth AS SIGNED) = 0, 99999, CAST(examMonth AS SIGNED)) DESC"; | |
| try { | |
| $regExam = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $regExam; | |
| } | |
| /**get Exam Registration SubjectDetails with student count | |
| * @author Sibin | |
| */ | |
| public function getExamRegSubjectDetails($examRegId) | |
| { | |
| $examRegId = $this->realEscapeString($examRegId); | |
| $examRegSubjectDetails = []; | |
| $sql = "SELECT e.examID as regularExamId,count(e.examID) AS examRegStudentCount,s.subjectID,s.subjectName,s.subjectDesc, | |
| e.examDate ,e.examStartTime,CONCAT(e.examDate ,' ',e.examStartTime) as examDateTime, | |
| CONCAT(DATE_FORMAT(e.examDate,'%d-%m-%Y'),' ',e.examStartTime) as examDateTimeFormatted | |
| FROM exam_reg_studentsubject erss | |
| INNER JOIN studentaccount sa ON sa.studentID = erss.studentID | |
| INNER JOIN subjects s ON s.subjectID = erss.subjectID | |
| INNER JOIN exam e ON e.examregID = erss.examregID and e.batchID = sa.batchID and e.subjectID = erss.subjectID AND e.examregID IS NOT NULL | |
| where erss.examregID = '$examRegId' GROUP BY e.examID order by e.examID"; | |
| try { | |
| $examRegSubjectDetails = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $examRegSubjectDetails; | |
| } | |
| /** | |
| * save student exam total fees | |
| * @author Sibin | |
| */ | |
| public function saveStudentExamTotalFees($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $result = null; | |
| $createdBy = $_SESSION['studentID']; | |
| $deleteSql = "DELETE FROM studentExamTotalFees where studentId = '$request->studentId' AND examRegId = '$request->examRegId'"; | |
| $sql = "INSERT INTO studentExamTotalFees(studentId,examRegId,examTotalFees,created_by) | |
| VALUES ('$request->studentId','$request->examRegId','$request->totalFees','$createdBy')"; | |
| try { | |
| $this->executeQuery($deleteSql); | |
| $this->executeQuery($sql); | |
| $result = true; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $result; | |
| } | |
| /** | |
| * get student exam total fees | |
| * @author Sibin | |
| */ | |
| public function getStudentExamTotalFees($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $studentTotalFees = null; | |
| $sql = "SELECT studentId,examRegId,examTotalFees from studentExamTotalFees WHERE studentId ='$request->studentId' AND examRegId = '$request->examRegId'"; | |
| try { | |
| $studentTotalFees = $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $studentTotalFees; | |
| } | |
| /** | |
| * with held student ExamResult by examregistration | |
| * @author Sibin | |
| */ | |
| public function withheldStudentExamResultByExamRegistration($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| if($request->status){ | |
| $sql = "INSERT INTO exam_students_withheld(examregID, studentID, reason) VALUES($request->examRegId,$request->studentId,'$request->reason') ON DUPLICATE KEY UPDATE reason = values(reason)"; | |
| }else{ | |
| $sql = "DELETE FROM exam_students_withheld WHERE examregID = '$request->examRegId' AND studentID = '$request->studentId'"; | |
| } | |
| try { | |
| $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return true; | |
| } | |
| /** | |
| * @param $examRegId | |
| * @throws ProfessionalException | |
| * @author Sibin | |
| */ | |
| public function enableDisableNominalRole($examRegId) | |
| { | |
| $examRegId = $this->realEscapeString($examRegId); | |
| $sql = null; | |
| try { | |
| $sql = "UPDATE exam_registration SET enableNominalRole = !enableNominalRole WHERE examregID = $examRegId "; | |
| $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * @param $examRegId | |
| * @throws ProfessionalException | |
| * @author Sibin | |
| */ | |
| public function getNominalRoleStatusByExamRegistration($examRegId,$isSupply) | |
| { | |
| $examRegId = $this->realEscapeString($examRegId); | |
| $isSupply = $this->realEscapeString($isSupply); | |
| $sql = null; | |
| $nominalRole = null; | |
| try { | |
| if($isSupply){ | |
| $sql = "SELECT enableNominalRole FROM exam_supplementary WHERE id = $examRegId "; | |
| }else{ | |
| $sql = "SELECT enableNominalRole FROM exam_registration WHERE examregID = $examRegId "; | |
| } | |
| $nominalRole = $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $nominalRole; | |
| } | |
| /** | |
| * Get exam registration's by admission yar | |
| * @param int $admissionyear | |
| * @return object|NULL|\com\linways\base\util\$objectList[] | |
| * @throws ProfessionalException | |
| * @author sibin | |
| */ | |
| public function getExamRegistrationsByAdmissionYear($admissionYear = Null) | |
| { | |
| $admissionYear = $this->realEscapeString($admissionYear); | |
| $conditions =""; | |
| if($admissionYear){ | |
| $conditions="WHERE b.batchStartYear = '$admissionYear'"; | |
| } | |
| $sql = "SELECT distinct er.examregID as id, er.examregName as name FROM exam_registration er | |
| INNER JOIN exam_registration_batches erb ON erb.examregID = er.examregID | |
| INNER JOIN batches b ON b.batchID = erb.batchID | |
| $conditions | |
| order by er.examregID DESC"; | |
| try { | |
| $examRegistrations = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $examRegistrations; | |
| } | |
| /** | |
| * Get exam date and time by exam registration | |
| * @param $request | |
| * @throws ProfessionalException | |
| * @author sibin | |
| */ | |
| public function getExamDatesByExamRegistration($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $examRegField = ($request->examType == ExamType::SUPPLY) ? "supply_examreg_id" : "examregID"; | |
| $examDates = []; | |
| $condition = ""; | |
| if($request->batchId){ | |
| $condition = " AND batchID IN ($request->batchId)"; | |
| } | |
| $sql = "SELECT DISTINCT examDate,examStartTime,concat(examDate,\" \",examStartTime) as examDateTime, | |
| concat(examDate,\" \",DATE_FORMAT(CAST(STR_TO_DATE(examStartTime,'%l:%i%p') AS DATETIME),'%r')) AS formattedExamDateTime | |
| from exam where $examRegField='$request->examRegId' $condition | |
| group by formattedExamDateTime | |
| order by examDate,examStartTime"; | |
| try { | |
| $examDates = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $examDates; | |
| } | |
| /** | |
| * Get exam registration's up to admission yar | |
| * @param int $admissionyear | |
| * @return object|NULL|\com\linways\base\util\$objectList[] | |
| * @throws ProfessionalException | |
| */ | |
| public function getExamRegistrationsUpToAdmissionYear($admissionYear = Null) | |
| { | |
| $admissionYear = $this->realEscapeString($admissionYear); | |
| $conditions =""; | |
| if($admissionYear){ | |
| $conditions="WHERE b.batchStartYear <= '$admissionYear'"; | |
| } | |
| $sql = "SELECT distinct er.examregID as id, er.examregName as name FROM exam_registration er | |
| INNER JOIN exam_registration_batches erb ON erb.examregID = er.examregID | |
| INNER JOIN batches b ON b.batchID = erb.batchID | |
| $conditions | |
| order by er.examregID DESC"; | |
| try { | |
| $examRegistrations = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $examRegistrations; | |
| } | |
| /** | |
| * Get exam dates by exam registration request | |
| * @param $request | |
| * @throws ProfessionalException | |
| * @author Sibin | |
| */ | |
| public function getExamDatesByExamRegistrationRequest($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $examRegField = ($request->examType == ExamType::SUPPLY) ? "supply_examreg_id" : "examregID"; | |
| $examDates = []; | |
| $sql = "SELECT distinct examDate as id ,DATE_FORMAT(examDate,'%d-%m-%Y') AS name,batchID as batchId from exam where $examRegField='$request->examRegId' | |
| GROUP BY examDate order by examDate"; | |
| try { | |
| $examDates = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $examDates; | |
| } | |
| /** | |
| * Get exam registered students by exam reg Id | |
| * @param int $examRegId | |
| * @throws ProfessionalException | |
| */ | |
| public function getExamRegisteredStudentsByExamRegId($examRegId,$startDate,$endDate) | |
| { | |
| $examRegId = $this->realEscapeString($examRegId); | |
| $startDate = $this->realEscapeString($startDate); | |
| $endDate = $this->realEscapeString($endDate); | |
| $condition = ""; | |
| if($startDate && $endDate){ | |
| $condition = " AND ers.dateofPay BETWEEN '$startDate' AND '$endDate'"; | |
| } | |
| $sql = " SELECT sa.regNo,sa.studentID,sa.rollNo,sa.studentName,ers.challanNo,ers.examtotalFees,ers.payment_method as paymentMethod,ers.paid,ers.dateofPay,b.batchName,erb.semID,dept.deptName,b.batchID, sa.rollNo | |
| from exam_reg_studentchallan ers | |
| INNER JOIN studentaccount sa ON sa.studentID = ers.studentID | |
| INNER JOIN batches b on b.batchID = sa.batchID | |
| LEFT JOIN department dept ON dept.deptID = sa.deptID | |
| LEFT JOIN exam_registration_batches erb ON erb.examregID = ers.examregID AND erb.batchID = sa.batchID | |
| where ers.examregID='$examRegId' $condition"; | |
| try { | |
| $examRegistrations = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $examRegistrations; | |
| } | |
| /** | |
| * Get exam registrations by request | |
| * @param int $REQUEST | |
| * @throws ProfessionalException | |
| */ | |
| public function getDistinctExamRegistrationByRequest($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $condition = ""; | |
| if ($request->courseTypeId) { | |
| $condition .= " AND ct.courseTypeID IN($request->courseTypeId) "; | |
| } | |
| if ($request->semId) { | |
| $condition .= " AND erb.semID IN($request->semId) "; | |
| } | |
| if($request->orderByExamYear){ | |
| $orderByExamYear = " ORDER BY er.examYear DESC,erb.examregID DESC "; | |
| } | |
| $examRegistrations =[]; | |
| $sql = "SELECT DISTINCT erb.examregID as id, er.examregName as name | |
| FROM exam_registration_batches erb | |
| INNER JOIN batches b on erb.batchID = b.batchID | |
| INNER JOIN exam_registration er on er.examregID = erb.examregID | |
| INNER JOIN course_type ct on ct.courseTypeID = b.courseTypeID | |
| WHERE 1=1 | |
| $condition | |
| $orderByExamYear"; | |
| try { | |
| $examRegistrations = $this->executeQueryForList($sql); | |
| return $examRegistrations; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * Get exam registrations by request | |
| * @param int $REQUEST | |
| * @throws ProfessionalException | |
| */ | |
| public function getDistinctExamRegisteredSubjectsByRequest($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $condition = ""; | |
| $groupByCondition = "s.subjectID"; | |
| $orderByCondition = ""; | |
| if($request->examRegId){ | |
| $condition .= " AND erss.examregID IN($request->examRegId) "; | |
| } | |
| if ($request->studentId) { | |
| $condition .= " AND erss.studentID IN($request->studentId) "; | |
| } | |
| if($request->subjectId){ | |
| $condition .= " AND erss.subjectID IN($request->subjectId) "; | |
| $groupByCondition = "erss.studentID "; | |
| $orderByCondition = "ORDER BY sa.rollNo"; | |
| } | |
| $subjectList = []; | |
| $sql = "SELECT erss.examregID as examRegId,s.subjectID as subjectId,s.subjectName,s.subjectDesc,sa.studentID,sa.regNo,sa.studentName,sa.rollNo,erss.properties | |
| FROM exam_reg_studentsubject erss | |
| INNER JOIN subjects s ON s.subjectID = erss.subjectID | |
| INNER JOIN studentaccount sa ON sa.studentID = erss.studentID | |
| INNER JOIN exam_reg_studentchallan ersc ON ersc.studentID = erss.studentID AND ersc.examregID = erss.examregID | |
| WHERE ersc.paid=1 $condition | |
| GROUP BY $groupByCondition | |
| $orderByCondition"; | |
| try { | |
| $subjectList = $this->executeQueryForList($sql); | |
| return $subjectList; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * Get exam date and time by supply registration | |
| * @param $request | |
| * @throws ProfessionalException | |
| */ | |
| public function getExamDatesBySupplyRegistration($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $examDates = []; | |
| $sql = "SELECT DISTINCT examDate,examStartTime,concat(examDate,\" \",examStartTime) as examDateTime from exam where supply_examreg_id='$request->examRegId' | |
| order by examDate,examStartTime"; | |
| try { | |
| $examDates = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $examDates; | |
| } | |
| /** | |
| * Method for getting last registered exam stduents for a batchId | |
| * @input $batchId $examRegId | |
| */ | |
| public function examRegisteredExamStudentsByBatchAndExamRegId($batchId, $examRegId) | |
| { | |
| $sql = null; | |
| $batchId = $this->realEscapeString($batchId); | |
| $examRegId = $this->realEscapeString($examRegId); | |
| $lastRegisteredExamStudents = null; | |
| $sql = "SELECT ersc.studentID,sa.studentGender, sa.regNo, sa.studentName,sl.secondLangaugeName | |
| FROM exam_reg_studentchallan ersc | |
| INNER JOIN studentaccount sa ON ersc.studentID = sa.studentID | |
| LEFT JOIN secondLangauge sl on sl.secondlangaugeID = sa.secondlangaugeID | |
| WHERE ersc.examregID = $examRegId AND ersc.paid = 1 AND sa.batchID = $batchId | |
| ORDER BY sa.rollNo ASC"; | |
| try { | |
| $lastRegisteredExamStudents = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $lastRegisteredExamStudents; | |
| } | |
| /** | |
| * Method to get subject List for a regular exam | |
| * | |
| * @author Sibin | |
| */ | |
| public function getSubjectListReg($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $examDetails = null; | |
| $condition= $innerJoinExam = $examStartTime= ""; | |
| if($request->batchId){ | |
| $condition .=" AND ersf.batchID = $request->batchId "; | |
| } | |
| if ($request->subjectTypeId) { | |
| $condition .= " AND s.paperTypeId = $request->subjectTypeId "; | |
| } | |
| if ($request->examDate) { | |
| $condition .= " AND e.examDate = '$request->examDate' "; | |
| $innerJoinExam = "INNER JOIN exam e ON e.examregID = ersf.examregID AND e.batchID = ersf.batchID AND e.subjectID = s.subjectID"; | |
| $examStartTime = " , e.examStartTime"; | |
| } | |
| $orderCondition = $request->orderBySyllabusYear ? "s.syllabusYear" : "s.subjectName"; | |
| $sql = "SELECT DISTINCT s.subjectID as subjectId,s.subjectName as subjectName,s.subjectDesc as subjectDesc,s.syllabusYear as syllabusYear,s.syllabusName | |
| $examStartTime | |
| FROM subjects s | |
| INNER JOIN exam_registration_subject_fees ersf ON s.subjectID = ersf.subjectID | |
| $innerJoinExam | |
| WHERE ersf.examregID IN ($request->examRegId) | |
| $condition | |
| ORDER BY $orderCondition ASC"; | |
| try { | |
| $examDetails = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $examDetails; | |
| } | |
| /** | |
| * Get supply exam reg batches by request | |
| * @param int $REQUEST | |
| * @throws ProfessionalException | |
| */ | |
| public function getDistinctExamRegBatchesByRequest($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $condition = ""; | |
| if ($request->examRegId) { | |
| $condition .= " AND erb.examregID IN($request->examRegId) "; | |
| } | |
| if ($request->courseTypeId) { | |
| $condition .= " AND ct.courseTypeID IN($request->courseTypeId) "; | |
| } | |
| if ($request->batchStartYear) { | |
| $condition .= " AND b.batchStartYear IN($request->batchStartYear) "; | |
| } | |
| $examRegistrations = []; | |
| $sql = "SELECT DISTINCT erb.batchID,b.batchName,IF(erb.publish_fromDate = '0000-00-00','',erb.publish_fromDate) AS fromDate,IF(erb.publish_toDate = '0000-00-00','',erb.publish_toDate) AS toDate,erb.publish, | |
| dp.deptName,dp.departmentDesc,cp.patternName,s.semName,s.semID as semId | |
| FROM exam_registration_batches erb | |
| INNER JOIN batches b ON b.batchID = erb.batchID | |
| INNER JOIN course_type ct on ct.courseTypeID = b.courseTypeID | |
| LEFT JOIN department dp ON dp.deptID = b.deptID | |
| LEFT JOIN course_pattern cp ON cp.patternID = b.patternID | |
| LEFT JOIN semesters s ON s.semID = erb.semID | |
| WHERE 1=1 | |
| $condition"; | |
| try { | |
| $examRegistrations = $this->executeQueryForList($sql); | |
| return $examRegistrations; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * Method to get student count for a regular exam | |
| * | |
| * @author Sibin | |
| */ | |
| public function getStudentCountDetailsReg($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $studentCount = null; | |
| $condition=""; | |
| $conditionMooc = ""; | |
| if($request->batchId){ | |
| $condition .=" AND sa.batchID IN ($request->batchId) "; | |
| } | |
| if($request->isShortTermCourse == 1) | |
| { | |
| if($request->excludeMoocStudents == 1) | |
| { | |
| $conditionMooc .=" AND ersc.courseMode <> 'MOOC' "; | |
| } | |
| } | |
| if($request->excludeMoocStudents == 1) | |
| { | |
| $conditionMooc .=" AND (ersc.courseMode = 'REGULAR' OR ersc.courseMode is NULL) "; | |
| } | |
| $sql = "SELECT count(distinct erss.studentID) as studentCount FROM subjects s | |
| INNER JOIN exam_reg_studentsubject erss ON s.subjectID = erss.subjectID | |
| INNER JOIN exam_reg_studentchallan ersc ON erss.examregID = ersc.examregID AND erss.studentID = ersc.studentID | |
| INNER JOIN studentaccount sa ON sa.studentID = ersc.studentID | |
| WHERE erss.examregID IN ($request->examRegId) AND s.subjectID = $request->subjectId AND ersc.paid = 1 | |
| $condition $conditionMooc"; | |
| try { | |
| $studentCount = $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $studentCount; | |
| } | |
| /**to get Student Exam Hall Ticket Blocked subjects | |
| * @param $studentID, $examregID,$isSupply | |
| * @throws ProfessionalException | |
| * @author Sibin | |
| */ | |
| public function getStudentExamHallTicketBlockedSubjects($studentId, $examregId, $isSupply) | |
| { | |
| $studentId = $this->realEscapeString($studentId); | |
| $examRegId = $this->realEscapeString($examregId); | |
| $isSupply = $this->realEscapeString($isSupply); | |
| $studentEntries = []; | |
| $sql = null; | |
| try { | |
| if ($isSupply) { | |
| $sql = "SELECT id,student_id as studentId,subject_id as subjectId from exam_reg_blocked_student | |
| where student_id ='$studentId' and supplyreg_id='$examRegId' GROUP BY subject_id"; | |
| } else { | |
| $sql = "SELECT id,student_id as studentId,subject_id as subjectId from exam_reg_blocked_student | |
| where student_id ='$studentId' and examreg_id='$examRegId' GROUP BY subject_id"; | |
| } | |
| $studentEntries = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $studentEntries; | |
| } | |
| public function getPublishedExamRegistrationsByRequest($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $sql = null; | |
| $examRegistrations = []; | |
| $condition = ""; | |
| if (!$request->considerPublishStatusOnly) { | |
| $condition .= " AND TIMESTAMP(erb.publish_fromDate,'00:00:00') < NOW() "; | |
| } | |
| if ($request->examRegId) { | |
| $condition .= " AND erb.examregID IN($request->examRegId) "; | |
| } | |
| if($request->batchId){ | |
| $condition .= " AND erb.batchID IN($request->batchId) "; | |
| } | |
| if($request->semId){ | |
| $condition .= " AND erb.semID IN($request->semId) "; | |
| } | |
| if(!$request->considerAll){ | |
| $condition .= " AND erb.publish = 1 "; | |
| } | |
| try { | |
| $sql = "SELECT | |
| erb.examregID as examRegId, | |
| erb.publish, | |
| erb.attClosingDate, | |
| erb.publish_fromDate AS publishFromDate, | |
| erb.publish_toDate AS publishToDate, | |
| erb.semID as semId | |
| FROM | |
| exam_registration_batches erb | |
| WHERE 1 = 1 | |
| $condition | |
| ORDER BY erb.semID"; | |
| $examRegistrations = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $examRegistrations; | |
| } | |
| /** | |
| * get student exam registered registered by request | |
| * @param $request | |
| * @return list | |
| * @throws ProfessionalException | |
| */ | |
| public function getExamRegisteredStudentStatusByRequest($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $sql = null; | |
| $condition = ''; | |
| $studentList = []; | |
| if ($request->examRegId) { | |
| if($request->isSupply){ | |
| $condition .= " AND ersc.exam_supplementary_id IN ($request->examRegId)"; | |
| }else{ | |
| $condition .= " AND ersc.examregID IN ($request->examRegId)"; | |
| } | |
| } | |
| if ($request->paid) { | |
| $condition .= " AND ersc.paid = '$request->paid'"; | |
| } | |
| if ($request->reservationId) { | |
| $condition .= " AND sa.reservID IN ($request->reservationId) "; | |
| } | |
| try { | |
| if ($request->isSupply) { | |
| $sql = "SELECT ersc.studentID, sa.regNo, sa.studentName, sa.batchID, b.batchName, ersc.total_fees as amount, ersc.paid, DATE_FORMAT(ersc.fee_paidDate,'%d-%m-%Y') as transactionDate,DATE_FORMAT(ersc.appliedDate,'%d-%m-%Y') as dateOfReg | |
| FROM exam_supplementary_student_details ersc | |
| INNER JOIN studentaccount sa ON sa.studentID = ersc.studentID | |
| INNER JOIN batches b on b.batchID = sa.batchID | |
| WHERE | |
| 1 = 1 | |
| $condition | |
| GROUP BY ersc.studentID"; | |
| }else{ | |
| $sql = "SELECT ersc.studentID, sa.regNo, sa.studentName, sa.batchID, b.batchName, ersc.examtotalFees as amount, ersc.paid, DATE_FORMAT(ersc.dateofPay,'%d-%m-%Y') as transactionDate,DATE_FORMAT(ersc.dateofRegistration,'%d-%m-%Y') as dateOfReg | |
| FROM exam_reg_studentchallan ersc | |
| INNER JOIN studentaccount sa ON sa.studentID = ersc.studentID | |
| INNER JOIN batches b on b.batchID = sa.batchID | |
| WHERE | |
| 1 = 1 | |
| $condition | |
| GROUP BY ersc.studentID"; | |
| } | |
| $studentList = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $studentList; | |
| } | |
| /**get student exam max mark by semester | |
| * @param $request | |
| * @return Object | |
| * @throws ProfessionalException | |
| */ | |
| public function getStudentExamMaxMarkBySemId($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| if( $request->considerInternalExternalSubjects){ | |
| $condition = " AND es.isExternal = 1"; | |
| } | |
| $sql = "SELECT SUM(ims.maxInternalMarks + e.examTotalMarks) as totalMaxMark | |
| FROM exam_reg_studentsubject ers | |
| INNER JOIN studentaccount sa ON (sa.studentID = ers.studentID ) | |
| LEFT JOIN internal_marks_settings ims ON (ers.subjectID = ims.subjectID) | |
| LEFT JOIN exam e ON (ers.subjectID = e.subjectID AND ers.examregID = e.examregID AND ims.semID = e.semID AND ims.batchID = e.batchID AND sa.batchID = e.batchID) | |
| INNER JOIN exam_subjectcredit es ON (es.batchID = e.batchID AND es.semID = e.semID AND es.subjectID = e.subjectID) | |
| WHERE | |
| e.semID = $request->semId AND ers.studentID = $request->studentId AND NOT (es.subjectType <=> 'ELECTIVE') $condition"; | |
| try { | |
| $examRegSubjectDetails = $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $examRegSubjectDetails; | |
| } | |
| /** Finalize student regular exam details | |
| * @param $request | |
| * @return Object | |
| * @throws ProfessionalException | |
| */ | |
| public function finalizeRegularExamDetails($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $sql = "UPDATE exam_registration_batches SET finalize = $request->finalize WHERE batchID = '$request->batchId' AND examregID = '$request->examRegId' AND semID = '$request->semId' "; | |
| try { | |
| return $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** get finalized student regular exam details | |
| * @param $request | |
| * @return Object | |
| * @throws ProfessionalException | |
| */ | |
| public function getRegularExamFinalizeDetails($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $sql = "SELECT finalize FROM exam_registration_batches WHERE batchID = '$request->batchId' AND examregID = '$request->examRegId' AND semID = '$request->semId' "; | |
| try { | |
| $finalizeDetails = $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $finalizeDetails; | |
| } | |
| /** set exam reg batch publish dates | |
| * @param $request | |
| * @return Object | |
| * @throws ProfessionalException | |
| */ | |
| public function setExamRegBatchPublishDates($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $insertValues=[]; | |
| try { | |
| foreach ($request->batches as $batch) { | |
| $insertValues[] = "($request->examRegId,$batch->batchID,$batch->semId,$request->publish,'$batch->fromDate','$batch->toDate',$request->updatedBy)"; | |
| } | |
| if(!empty($insertValues)){ | |
| $insertValues = implode(",", $insertValues); | |
| if ($request->publish){ | |
| $sql = "INSERT INTO exam_registration_batches(examregID,batchID,semID,publish,publish_fromDate,publish_toDate,updatedBy) | |
| VALUES $insertValues ON DUPLICATE KEY UPDATE publish_fromDate = VALUES(publish_fromDate),publish_toDate = VALUES(publish_toDate),publish = VALUES(publish) , updatedBy = VALUES(updatedBy)"; | |
| }else{ | |
| $sql = "INSERT INTO exam_registration_batches(examregID,batchID,semID,publish,publish_fromDate,publish_toDate,updatedBy) | |
| VALUES $insertValues ON DUPLICATE KEY UPDATE publish = VALUES(publish) , updatedBy = VALUES(updatedBy)"; | |
| } | |
| $this->executeQueryForObject($sql); | |
| } | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return true; | |
| } | |
| /** | |
| * @param $request | |
| * @return array|Object | |
| * @throws ProfessionalException | |
| * @author Sibin | |
| */ | |
| public function getAllStudentExamRegBlockedStatusByRequest($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $studentBlockStatus = []; | |
| $condition = ($request->isSupply == 1) ? " erbs.supplyreg_id = '$request->examregId' " : " erbs.examreg_id = '$request->examregId'"; | |
| if($request->reasonId){ | |
| $condition .= " AND erbr.id IN ($request->reasonId) "; | |
| } | |
| if($request->groupByReason){ | |
| $condition .= " GROUP BY erbr.id "; | |
| } | |
| $sql = "SELECT | |
| sa.studentID, | |
| sa.studentName, | |
| sa.regNo, | |
| s.subjectID, | |
| s.subjectName, | |
| s.subjectDesc, | |
| erbr.id AS reasonId, | |
| erbr.reason, | |
| erbr.reason_type AS reasonType, | |
| sta.staffName AS contactPerson, | |
| erbr.reason, | |
| b.batchID, | |
| b.batchName, | |
| pdc.patterncourseID, | |
| pdc.patterncourseName | |
| FROM | |
| exam_reg_blocked_student erbs | |
| INNER JOIN | |
| studentaccount sa ON (erbs.student_id = sa.studentID) | |
| INNER JOIN | |
| subjects s ON (s.subjectID = erbs.subject_id) | |
| INNER JOIN | |
| exam_reg_block_reason erbr ON (erbr.id = erbs.reason_id) | |
| INNER JOIN | |
| batches b ON (sa.batchID = b.batchID) | |
| LEFT JOIN | |
| pattern_deptcourses pdc ON (pdc.patterncourseID = b.patterncourseID) | |
| LEFT JOIN | |
| staffaccounts sta ON (erbr.contact_person_id = sta.staffID) | |
| WHERE | |
| $condition | |
| ORDER BY sa.regNo ASC , sa.batchID ASC, FIELD(erbr.reason_type, 'SUBJECT_WISE') DESC"; | |
| try { | |
| $studentBlockStatus = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $studentBlockStatus; | |
| } | |
| /**Edit student exam payment | |
| * @param $request | |
| * @throws ProfessionalException | |
| */ | |
| public function editStudentRegularExamPayment($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $remarks = new \stdClass(); | |
| $remarks->oldMark = $request->oldMark; | |
| $remarks->remarks = $request->remarks; | |
| $remarks->updatedBy = $request->updatedBy; | |
| $remarks->updatedDate = $request->updatedDate; | |
| $json = json_encode($remarks); | |
| $sql = null; | |
| try { | |
| $sql = "UPDATE exam_reg_studentchallan | |
| set examtotalFees='$request->examFee' , | |
| payment_remarks = IF( | |
| `payment_remarks` IS NULL OR | |
| JSON_TYPE(`payment_remarks`) != 'ARRAY', | |
| JSON_ARRAY(), | |
| `payment_remarks` | |
| ), | |
| `payment_remarks` = JSON_ARRAY_APPEND( | |
| `payment_remarks`, | |
| '$', | |
| CAST('$json' AS JSON) | |
| ) | |
| where studentID='$request->studentId' and examregID='$request->examRegId'"; | |
| $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return true; | |
| } | |
| /** get exam reg details by qp code | |
| * @param $request | |
| * @return Object | |
| * @throws ProfessionalException | |
| */ | |
| public function getExamRegDetailsByQpCode($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $examRegDetails=[]; | |
| $sql = "SELECT qp.id,s.subjectID,s.subjectName,s.subjectDesc, | |
| IF(er.examregID,0,1) as isSupply, | |
| IF(er.examregID,er.examregID,es.id) as examRegId, | |
| IF(er.examregID,er.examregName,es.supplyDesc) as examRegName | |
| FROM examQpCodes qp | |
| INNER JOIN subjects s ON s.subjectID = qp.subjectId | |
| LEFT JOIN exam_registration er ON er.examregID = qp.examRegId | |
| LEFT JOIN exam_supplementary es ON es.id = qp.supplyRegId | |
| WHERE qp.qpCode='$request->qpCode'"; | |
| try { | |
| $examRegDetails = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $examRegDetails; | |
| } | |
| /** | |
| * @param $request | |
| * @return Object | |
| * @throws ProfessionalException | |
| * | |
| */ | |
| public function searchExamQpCodeByString($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $sql = "SELECT qpCode FROM examQpCodes WHERE 1=1 "; | |
| if ($request->qpCode) { | |
| $sql .= "AND qpCode LIKE '%$request->qpCode%'"; | |
| } | |
| $sql .= $request->limitBy ? $request->limitBy : ""; | |
| try { | |
| $qpCodes = $this->executeQueryForList($sql); | |
| return $qpCodes; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * @param $request | |
| * @return Object | |
| * @throws ProfessionalException | |
| * | |
| */ | |
| public function getExamDetailsByExamDateTime($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $examRegField = ($request->examType == ExamType::SUPPLY) ? "supply_examreg_id" : "examregID"; | |
| $exams=[]; | |
| $sql = "SELECT e.examID as examId,e.examDate,e.examStartTime,e.semID as semId,e.subjectID,s.subjectName,s.subjectDesc,e.batchID,b.batchName,b.batchStartYear,b.deptID,dp.deptName,dp.departmentDesc | |
| FROM exam e | |
| INNER JOIN subjects s ON s.subjectID = e.subjectID | |
| INNER JOIN batches b ON b.batchID = e.batchID | |
| INNER JOIN department dp ON dp.deptID = b.deptID | |
| where concat(e.examDate,\" \",DATE_FORMAT(CAST(STR_TO_DATE(e.examStartTime,'%l:%i%p') AS DATETIME),'%r')) ='$request->examDateTime' AND e.$examRegField='$request->examRegId'"; | |
| try { | |
| $exams = $this->executeQueryForList($sql); | |
| return $exams; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * @param $studentId | |
| * @throws ProfessionalException | |
| * @author Sibin | |
| */ | |
| public function getStudentExamRegistrationDetailsByStudent($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $fetchedBatchID = current(StudentService::getInstance()->getStudentDetailsByIds($request->studentId))->batchID; | |
| $request->batchId = $request->batchId ? $request->batchId :$fetchedBatchID; | |
| $sql = null; | |
| $examRegistrations = []; | |
| $condition=""; | |
| if ($request->batchId) { | |
| $condition .= " AND erb.batchID IN($request->batchId)"; | |
| } | |
| if($request->semId){ | |
| $condition .=" AND erb.semID IN ($request->semId)"; | |
| } | |
| $condition .= $request->isShortTerm ? " AND e.shortCourse NOT IN (0) " : " AND e.shortCourse = 0 "; | |
| try { | |
| $sql = "SELECT e.examregID,e.examregName,e.examregDesc,e.examMonth, e.examYear,erb.publish, erb.publish_fromDate, erb.publish_toDate,erb.semID as semId, | |
| ersc.paid,ersc.examstdregID as applied,ersc.examfineID,e.lastAPC,erf.lastDate,erf.startDate,ebs.block_student as examRegBlocked,ersc.courseMode,ersc.approved | |
| FROM exam_registration e | |
| INNER JOIN exam_registration_batches erb ON erb.examregID = e.examregID | |
| LEFT JOIN exam_registration_fine erf ON erf.examregID = e.examregID | |
| LEFT JOIN exam_reg_studentchallan ersc ON ersc.examregID = e.examregID AND ersc.studentID = $request->studentId | |
| LEFT JOIN exam_blocked_students ebs ON ebs.studentId = $request->studentId AND ebs.batchId = erb.batchID AND ebs.examRegId = e.examregID AND ebs.block_student = 1 | |
| WHERE 1 = 1 | |
| $condition | |
| GROUP BY e.examregID"; | |
| $examRegistrations = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $examRegistrations; | |
| } | |
| /** | |
| * @param $studentId | |
| * @throws ProfessionalException | |
| * @author Sibin | |
| */ | |
| public function getStudentPseudoSubjectsByExamRegistration($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $fetchedBatchID = current(StudentService::getInstance()->getStudentDetailsByIds($request->studentId))->batchID; | |
| $request->batchId = $request->batchId ? $request->batchId : $fetchedBatchID; | |
| $sql = null; | |
| $subjects = []; | |
| $condition = ""; | |
| try { | |
| $sql = "SELECT t3.subjectName, sum(t2.examfeesAmount) as examFees, t3.subjectID, t3.subjectDesc | |
| FROM exam_registration_subject_fees t2, subjects t3, exam_registration_batches t4 | |
| where t2.subjectID = t3.subjectID AND t2.examregID = t4.examregID AND t4.batchID = t2.batchID AND t2.semID = t4.semID | |
| AND t2.examregID = '$request->examRegId' AND t2.batchID = '$request->batchId' | |
| AND t3.subjectID IN | |
| (SELECT DISTINCT sub.subjectID FROM pseudosubjects_students pss | |
| INNER JOIN pseudosubjects_sbs psbs ON psbs.pseudosubjectID = pss.pseudosubjectID AND pss.studentID = '$request->studentId' | |
| INNER JOIN sbs_relation sbs ON sbs.sbsID=psbs.sbsID AND sbs.batchID = '$request->batchId' AND sbs.semID = '$request->semId' | |
| INNER JOIN subjects sub ON sbs.subjectID = sub.subjectID) group by t3.subjectID"; | |
| $subjects = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $subjects; | |
| } | |
| /** | |
| * @param $request | |
| * @return Object | |
| * @throws ProfessionalException | |
| * | |
| */ | |
| public function getExamFeeDetailsByExamRegistration($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $examsFees = []; | |
| $sql = "SELECT t1.examfeesName, t2.examfeesAmount ,t1.examfeesID FROM exam_feestype t1, exam_registration_fees t2 | |
| WHERE t1.examfeesID = t2.examfeesID AND t2.examregID = \"".$request->examRegId."\" AND t1.everySubject = 0 "; | |
| try { | |
| $examsFees = $this->executeQueryForList($sql); | |
| return $examsFees; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * @param $request | |
| * @return Object | |
| * @throws ProfessionalException | |
| * | |
| */ | |
| public function getExamFineDetailsByExamRegistration($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $request->today = date('Y-m-d'); | |
| $examFines = []; | |
| $sql = "SELECT t2.examfineAmount, t1.examfineName, t1.examfineID, t2.lastDate FROM exam_finetype t1, exam_registration_fine t2 | |
| WHERE t1.examfineID = t2.examfineID AND t2.examregID = \"".$request->examRegId."\" AND DATE(t2.startDate) <= \"" . $request->today . "\" order by t2.startDate desc limit 1 "; | |
| try { | |
| $examFines = $this->executeQueryForList($sql); | |
| return $examFines; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * @param $request | |
| * @return Object | |
| * @throws ProfessionalException | |
| * | |
| */ | |
| public function saveStudentRegularExamRegistration($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $request->today = date('Y-m-d'); | |
| //delete if already exist | |
| $deleteSubjectSql = " DELETE FROM exam_reg_studentsubject WHERE studentID = \"" . $request->studentId. "\" AND examregID = \"" . $request->examRegId . "\" "; | |
| $deleteSql = " DELETE FROM exam_reg_studentchallan WHERE studentID = \"" . $request->studentId . "\" AND examregID = \"" . $request->examRegId . "\" "; | |
| $subjectSql = "INSERT INTO exam_reg_studentsubject (studentID, examregID, subjectID, examFees) | |
| SELECT \"" . $request->studentId . "\", \"" . $request->examRegId. "\", t3.subjectID, sum(t2.examfeesAmount) from exam_registration_fees t1, exam_registration_subject_fees t2, subjects t3, exam_registration_batches t4 where t1.examfeesID = t2.examfeesID AND t1.examregID = t2.examregID AND t2.subjectID = t3.subjectID | |
| AND t2.examregID = t4.examregID AND t4.batchID = t2.batchID AND t2.semID = t4.semID AND t1.examregID = \"" . $request->examRegId . "\" AND t2.batchID = \"" . $request->batchId . "\" AND t3.subjectID IN (" . implode(',', $request->subjectIds) . ") group by t2.subjectID "; | |
| $sql = "INSERT INTO exam_reg_studentchallan (studentID, examregID, examfineID, examtotalFees,dateofRegistration,paid,dateofPay,courseMode) | |
| VALUES ('$request->studentId', '$request->examRegId','$request->examFineId','$request->totalAmount','$request->today',$request->paid,'$request->dateOfPay','$request->examMode') | |
| ON DUPLICATE KEY UPDATE paid='$request->paid',dateofPay='$request->dateOfPay'"; | |
| try { | |
| $this->executeQueryForObject($deleteSubjectSql); | |
| $this->executeQueryForObject($deleteSql); | |
| $this->executeQueryForObject($subjectSql); | |
| $this->executeQueryForObject($sql); | |
| return true; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * @param $request | |
| * @return Object | |
| * @throws ProfessionalException | |
| * | |
| */ | |
| public function getStudentExamRegistrationApplySection($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $request->today = date('Y-m-d'); | |
| $response = new \stdClass(); | |
| $sql = "SELECT t1.examfineID from exam_registration_fine t1, exam_reg_studentchallan t2 where t1.examregID=\"" . $request->examregID . "\" and t2.studentID=\"" . $request->studentId . "\" and t1.examregID=t2.examregID and t1.examfineID=t2.examfineID | |
| and UNIX_TIMESTAMP(t1.startDate) <= UNIX_TIMESTAMP(now()) and UNIX_TIMESTAMP(now()) <= (UNIX_TIMESTAMP(t1.lastDate) + 86399) and UNIX_TIMESTAMP(t1.startDate) <= (UNIX_TIMESTAMP(t2.dateofRegistration)+86399) and (UNIX_TIMESTAMP(t2.dateofRegistration)+86399) <= (UNIX_TIMESTAMP(t1.lastDate)+86399)"; | |
| try { | |
| $result = $this->executeQueryForList($sql); | |
| if(empty($result)){ | |
| $sql = "SELECT t1.examfineID from exam_registration_fine t1, exam_reg_studentchallan t2 where t1.examregID=\"" . $request->examregID. "\" and t2.studentID=\"" . $request->studentId . "\" and t1.examregID=t2.examregID and UNIX_TIMESTAMP(t2.dateofRegistration) > (UNIX_TIMESTAMP(t1.lastDate)+86399) and t1.lastDate in (select max(lastDate) from exam_registration_fine where examregID=\"" . $request->examregID . "\")"; | |
| $result = $this->executeQueryForList($sql); | |
| } | |
| if (!empty($result)) { | |
| $response->sameSection = true; | |
| } else { | |
| $response->sameSection = false; | |
| } | |
| $currentFineSql= "SELECT t1.examfineID FROM exam_finetype t1, exam_registration_fine t2 WHERE t1.examfineID = t2.examfineID AND t2.examregID = \"" . $request->examregID . "\" AND t2.startDate <= \"" . $request->today . "\" order by t2.startDate desc limit 1"; | |
| $response->currentFineId = $this->executeQueryForObject($currentFineSql)->examfineID; | |
| return $response; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * Get exam date and time by exam registration | |
| * @param $request | |
| * @throws ProfessionalException | |
| */ | |
| public function getExamDatesFromExamRegistrations($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $examRegField = ($request->examType == ExamType::SUPPLY) ? "supply_examreg_id" : "examregID"; | |
| $examDates = []; | |
| $sql = "SELECT DISTINCT examDate,examStartTime,concat(examDate,\" \",examStartTime) as examDateTime, | |
| concat(examDate,\" \",DATE_FORMAT(CAST(STR_TO_DATE(examStartTime,'%l:%i%p') AS DATETIME),'%r')) AS formattedExamDateTime | |
| from exam where $examRegField IN ($request->examRegId) | |
| group by formattedExamDateTime | |
| order by examDate,examStartTime"; | |
| try { | |
| $examDates = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $examDates; | |
| } | |
| public function getExamRegistrationForBatchByRequest($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $sqlCondition = ""; | |
| $joinBatches = ""; | |
| if (!empty($request->batchId)) { | |
| $batchIdString = is_array($request->batchId) ? implode(",", $request->batchId) : $request->batchId; | |
| $sqlCondition .= " AND erb.batchID IN ($batchIdString) "; | |
| } | |
| if (!empty($request->semId)) { | |
| $semIdString = is_array($request->semId) ? implode(",", $request->semId) : $request->semId; | |
| $sqlCondition .= " AND erb.semID IN ($semIdString) "; | |
| } | |
| if($request->courseTypeId){ | |
| $joinBatches = " INNER JOIN batches b ON b.batchID = erb.batchID"; | |
| $sqlCondition .= " AND b.courseTypeID IN ($request->courseTypeId)"; | |
| } | |
| $sqlCondition .= $request->isShortTerm ? " AND er.shortCourse IN (1,2) " : " AND er.shortCourse = 0 "; | |
| $sql = "SELECT | |
| er.examregID AS id, | |
| er.examregName AS name, | |
| er.examregDesc | |
| FROM | |
| exam_registration er | |
| INNER JOIN exam_registration_batches erb ON | |
| erb.examregID = er.examregID | |
| $joinBatches | |
| WHERE | |
| er.examregID = er.examregID | |
| $sqlCondition | |
| GROUP BY er.examregID"; | |
| try { | |
| $examRegistration = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $examRegistration; | |
| } | |
| public function verifyMoocStudentCertificates($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $sql = "UPDATE exam_reg_studentchallan set approved = 1 WHERE examregID IN ($request->examRegId) AND studentID IN ($request->studentId)"; | |
| try { | |
| $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return true; | |
| } | |
| /** | |
| * @param $request | |
| * get exam registrations by date | |
| */ | |
| public function getExamRegistrationsByDate($request) | |
| { | |
| $condition = ""; | |
| if ($request->examDate) { | |
| $condition .= " AND e.examDate = '$request->examDate' "; | |
| } | |
| $request = $this->realEscapeObject($request); | |
| if ($request->isSupply) { | |
| $sql = "SELECT es.id,es.supplyDesc from exam_supplementary es | |
| INNER JOIN supply_improve_batches erb ON erb.exam_supplementary_id = es.id | |
| INNER JOIN batches b on erb.batchID = b.batchID | |
| INNER JOIN course_type ct on ct.courseTypeID = b.courseTypeID | |
| INNER JOIN exam e ON e.supply_examreg_id = es.id | |
| WHERE 1 = 1 | |
| $condition GROUP BY es.id"; | |
| } else { | |
| $sql = "SELECT er.examregID,er.examregName from exam_registration er | |
| INNER JOIN exam_registration_batches erb ON erb.examregID = er.examregID | |
| INNER JOIN batches b on erb.batchID = b.batchID | |
| INNER JOIN course_type ct on ct.courseTypeID = b.courseTypeID | |
| INNER JOIN exam e ON e.examregID = er.examregID | |
| WHERE 1 = 1 | |
| $condition GROUP BY er.examregID"; | |
| } | |
| try { | |
| $result = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $result; | |
| } | |
| /** | |
| * Method to get withheld exam reg of student | |
| * @author Sibin | |
| */ | |
| public function getWithheldExamRegistrationsByStudent($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $examRegList = []; | |
| $sql = "SELECT studentID as studentId,reason,examregID as examRegId from exam_students_withheld where withheld=1 and studentID='$request->studentId'"; | |
| try { | |
| $examRegList = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $examRegList; | |
| } | |
| /** | |
| * Method to get exam registered students by subject | |
| * @author Sibin | |
| */ | |
| public function getExamRegisteredStudentBySubjectRequest($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $studentList = []; | |
| if($request->isSupply){ | |
| $sql = "SELECT essd.exam_supplementary_id,e.examID,se.examID as supplyExamId,essd.studentID,sa.regNo,sa.studentName,efn.false_number as falseNumber,ea.isAbsent from exam_supplementary_student_details essd | |
| INNER JOIN exam e ON e.batchID='$request->batchId' and e.subjectID='$request->subjectId' and examregID IS NOT NULL | |
| INNER JOIN exam se ON se.batchID='$request->batchId' and se.subjectID='$request->subjectId' and se.supply_examreg_id = '$request->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 examcontroller_false_number efn ON efn.studentID = sa.studentID AND efn.examID = se.examID | |
| LEFT JOIN exam_attendance ea ON ea.examID = se.examID AND ea.studentID = essd.studentID | |
| WHERE essd.exam_supplementary_id='$request->examRegId' and sa.batchID='$request->batchId' and essd.paid=1 | |
| order by sa.regNo"; | |
| }else{ | |
| $sql = "SELECT e.examID,erb.batchID,ersb.subjectID,ers.studentID,sa.regNo,sa.studentName,efn.false_number as falseNumber,ea.isAbsent from exam_registration_batches erb | |
| INNER JOIN exam_reg_studentchallan ers on ers.examregID = erb.examregID | |
| INNER JOIN exam_reg_studentsubject ersb ON ersb.examregID=erb.examregID and ersb.studentID = ers.studentID | |
| INNER JOIN exam e on e.examregID =erb.examregID AND e.subjectID = ersb.subjectID and e.batchID=erb.batchID | |
| INNER JOIN studentaccount sa ON sa.studentID = ers.studentID AND sa.batchID=erb.batchID | |
| LEFT JOIN examcontroller_false_number efn ON efn.studentID = sa.studentID AND efn.examID = e.examID | |
| LEFT JOIN exam_attendance ea ON ea.examID = e.examID AND ea.studentID = ers.studentID | |
| where erb.examregID='$request->examRegId' and erb.batchID='$request->batchId' and ersb.subjectID='$request->subjectId' and ers.paid=1 | |
| order by sa.regNo"; | |
| } | |
| try { | |
| $studentList = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $studentList; | |
| } | |
| public function getStudentRegularExamResultWithHeldReasonDetails($examRegId, $studentId) | |
| { | |
| $examRegId = $this->realEscapeString($examRegId); | |
| $studentId = $this->realEscapeArray($studentId); | |
| $withHeldData = null; | |
| try { | |
| $sql= "SELECT esw.withheld,esw.reasonId FROM exam_students_withheld esw WHERE esw.withheld =1 AND esw.examregID = $examRegId AND esw.studentID = $studentId "; | |
| $withHeldReasons = $this->executeQueryForObject($sql); | |
| if($withHeldReasons){ | |
| if ($withHeldReasons->reasonId){ | |
| $sql = "SELECT | |
| esw.withheld,erbr.reason,erbr.otherDetails,sa.staffName | |
| FROM | |
| exam_students_withheld esw | |
| LEFT JOIN exam_reg_block_reason erbr ON erbr.id IN ($withHeldReasons->reasonId) | |
| LEFT JOIN staffaccounts sa ON sa.staffID = erbr.contact_person_id | |
| WHERE | |
| esw.examregID = $examRegId | |
| AND esw.studentID = $studentId"; | |
| $withHeldData = $this->executeQueryForList($sql); | |
| } else { | |
| $withHeldData[] = $withHeldReasons; | |
| } | |
| } | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $withHeldData; | |
| } | |
| public function getStudentSupplyExamResultWithHeldReasonDetails($examRegId, $studentId) | |
| { | |
| $examRegId = $this->realEscapeString($examRegId); | |
| $studentId = $this->realEscapeArray($studentId); | |
| $withHeldData = null; | |
| try { | |
| $sql = "SELECT esw.withheld,esw.reasonId FROM supplyexam_students_withheld esw WHERE esw.withheld =1 AND esw.exam_supplementary_id IN($examRegId) AND esw.studentID = $studentId "; | |
| $withHeldReasons = $this->executeQueryForObject($sql); | |
| if($withHeldReasons){ | |
| if ($withHeldReasons->reasonId) { | |
| $sql = "SELECT | |
| esw.studentID,esw.withheld,erbr.reason,erbr.otherDetails,sa.staffName | |
| FROM | |
| supplyexam_students_withheld esw | |
| LEFT JOIN exam_reg_block_reason erbr ON erbr.id IN ($withHeldReasons->reasonId) | |
| LEFT JOIN staffaccounts sa ON sa.staffID = erbr.contact_person_id | |
| WHERE | |
| esw.exam_supplementary_id IN($examRegId) | |
| AND esw.studentID = $studentId"; | |
| $withHeldData = $this->executeQueryForList($sql); | |
| } else { | |
| $withHeldData[] = $withHeldReasons; | |
| } | |
| } | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $withHeldData; | |
| } | |
| /** | |
| * @author Sibin | |
| */ | |
| public function getRegisteredRegularExamsByRequest($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $condition = ""; | |
| if($request->batchStartYear){ | |
| $condition .="AND b.batchStartYear IN($request->batchStartYear)"; | |
| } | |
| if ($request->courseTypeId) { | |
| $condition .= " AND b.courseTypeID IN($request->courseTypeId)"; | |
| } | |
| if($request->isSupply){ | |
| $sql = "SELECT er.id, er.supplyDesc as name, er.semID AS semId FROM exam_supplementary er | |
| INNER JOIN supply_improve_batches erb ON erb.exam_supplementary_id = er.id | |
| INNER JOIN batches b ON b.batchID = erb.batchID | |
| WHERE 1 = 1 | |
| $condition | |
| GROUP BY er.id | |
| ORDER BY er.examYear,er.id"; | |
| } | |
| else{ | |
| $sql = "SELECT er.examregID AS id, er.examregName AS name,er.examMonth,er.examYear | |
| FROM exam_registration er | |
| INNER JOIN exam_registration_batches erb ON erb.examregID = er.examregID | |
| INNER JOIN batches b ON b.batchID = erb.batchID | |
| WHERE 1 = 1 | |
| $condition | |
| GROUP BY er.examregID | |
| ORDER BY er.examYear,er.examregID DESC"; | |
| } | |
| try { | |
| $regExam = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $regExam; | |
| } | |
| /** | |
| * get exam registration by valuation date end | |
| * | |
| * @param int $request | |
| * @return object|NULL|$objectList[] | |
| * @throws ProfessionalException | |
| */ | |
| public function getExamRegistrationsByValuationDate($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $examRegIds = []; | |
| $valuationDateStartField = "firstval_Datestart"; | |
| $valuationDateEndField = "firstval_Dateend"; | |
| $condition = ""; | |
| if($request->examType == ExamType::REGULAR){ | |
| $sql = "SELECT examRegId as examRegId FROM valuationDatesSubjectWise | |
| WHERE TIMESTAMP($valuationDateStartField,'00:00:00') < NOW() AND TIMESTAMP($valuationDateEndField,'23:59:59') > NOW() $condition | |
| GROUP BY examRegId"; | |
| } | |
| try { | |
| $examRegIds = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $examRegIds; | |
| } | |
| /** | |
| * get exam registration by coursetype | |
| * | |
| * @param int $request | |
| * @return object|NULL|$objectList[] | |
| * @throws ProfessionalException | |
| */ | |
| public function getExamRegistrationsByCourseType($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $condition = ""; | |
| 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) "; | |
| } | |
| $sql = "SELECT erb.examregID,erb.semID FROM batches b | |
| INNER JOIN exam_registration_batches erb ON b.batchID = erb.batchID AND erb.semID = b.final_semester | |
| INNER JOIN exam_registration er ON er.examregID = erb.examregID | |
| WHERE er.shortCourse = 0 $condition GROUP BY erb.examregID"; | |
| try { | |
| $examRegIds = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $examRegIds; | |
| } | |
| /** | |
| * Method to get registered student | |
| * @param $request | |
| * @return object|NULL|$objectList[] | |
| * @throws ProfessionalException | |
| * @author Sibin | |
| */ | |
| public function getStudentDetailsForExamRegistration($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $condition = ""; | |
| if ($request->examRegId) { | |
| $condition .= " AND ersc.examregID IN ($request->examRegId) "; | |
| } | |
| if ($request->batchId) { | |
| $condition .= " AND sa.batchID IN ($request->batchId) "; | |
| } | |
| $sql = "SELECT distinct ersc.studentID,UPPER(sa.studentGender) as studentGender FROM exam_reg_studentchallan ersc | |
| INNER JOIN studentaccount sa ON sa.studentID = ersc.studentID | |
| WHERE ersc.paid = 1 | |
| $condition"; | |
| try { | |
| $students = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $students; | |
| } | |
| /** | |
| * get regular,supply,revaluation reg details | |
| * @param request | |
| * @return List | |
| */ | |
| public function getExamRegDetailsByType($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $sql = $detailsSql = ""; | |
| $exam_registration_type = ""; | |
| if ($request->revaluationId) { | |
| $sql = "SELECT id,revalDesc,exam_registration_id as regular_examRegId,exam_supplementary_id as supply_examRegId | |
| from exam_revaluation | |
| where id='$request->revaluationId'"; | |
| try { | |
| $revaluation = $this->executeQueryForObject($sql); | |
| if($revaluation->regular_examRegId){ | |
| $request->regular_examRegId = $revaluation->regular_examRegId; | |
| } | |
| else if($revaluation->supply_examRegId){ | |
| $request->supply_examRegId = $revaluation->supply_examRegId; | |
| } | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| if ($request->regular_examRegId) { | |
| $detailsSql = "SELECT er.examregID as id,er.examregName as examregName,0 as isSupply,er.examMonth,er.examYear,erb.semID as semId | |
| from exam_registration er | |
| INNER JOIN exam_registration_batches erb ON erb.examregID = er.examregID | |
| where er.examregID='$request->regular_examRegId'"; | |
| $exam_registration_type = "REGULAR"; | |
| } else if ($request->supply_examRegId) { | |
| $detailsSql = "SELECT id as id,supplyDesc as examregName,1 as isSupply,examMonth,examYear,semID as semId | |
| from exam_supplementary | |
| where id='$request->supply_examRegId'"; | |
| $exam_registration_type = "SUPPLY"; | |
| } | |
| try { | |
| if($detailsSql){ | |
| $examRegDetails = $this->executeQueryForObject($detailsSql); | |
| $examRegDetails->exam_registration_type = $exam_registration_type; | |
| $examRegDetails->revaluationName = $revaluation->revalDesc; | |
| } | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $examRegDetails; | |
| } | |
| /** | |
| * get exam registration publish status | |
| * | |
| * @param int $request | |
| * @return object|NULL|$objectList[] | |
| * @throws ProfessionalException | |
| */ | |
| public function getExamRegPublishStatusByExamType($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| if ($request->examRegId && $request->batchId) { | |
| $sql = "SELECT publish as isPublished FROM exam_registration_batches WHERE examregID='$request->examRegId' AND batchID='$request->batchId'"; | |
| } | |
| else if ($request->revaluationId) { | |
| $sql ="SELECT published as isPublished FROM exam_revaluation WHERE id='$request->revaluationId'"; | |
| } | |
| else if ($request->supplyRegId && $request->batchId) { | |
| $sql = "SELECT publish as isPublished FROM supplyexam_publishresult WHERE exam_supplementary_id='$request->supplyRegId' AND batchID='$request->batchId'"; | |
| } | |
| try { | |
| $status = $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $status; | |
| } | |
| /** | |
| * @param $studentId | |
| * @throws ProfessionalException | |
| * @author Sibin | |
| */ | |
| public function getStudentRegisteredExamRegistrations($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $examRegs=[]; | |
| $condition=""; | |
| if($request->studentId){ | |
| $condition =" AND ersc.studentID IN ($request->studentId)"; | |
| } | |
| try { | |
| $sql = "SELECT er.examregID,erb.semID AS semId, er.examMonth, er.examYear | |
| from exam_registration er | |
| INNER JOIN exam_reg_studentchallan ersc ON ersc.examregID = er.examregID | |
| INNER JOIN studentaccount sa ON sa.studentID = ersc.studentID | |
| INNER JOIN exam_registration_batches erb ON erb.examregID = er.examregID AND erb.batchID = sa.batchID | |
| WHERE er.shortCourse = 0 AND ersc.paid = 1 $condition ORDER BY erb.semID ASC"; | |
| $examRegs = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $examRegs; | |
| } | |
| /** | |
| * get exam registered student subjct list | |
| * @author Sibin | |
| */ | |
| public function getExamRegisteredStudentSubjectListByRequest($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $result=[]; | |
| $conditions =""; | |
| if($request->paid){ | |
| $conditions .= " AND ersc.paid = 1"; | |
| } | |
| if ($request->checkIsTheory) { | |
| $conditions .= " AND s.isTheory IN ($request->isTheory)"; | |
| } | |
| if($request->isSupply){ | |
| $sql = "SELECT sa.studentID,sa.regNo,sa.studentName,b.batchName,s.subjectID,s.subjectName,s.subjectDesc,e.examID as regularExamId, | |
| e.examDate,DATE_FORMAT(e.examDate,'%d-%m-%Y') as examDateFormatted | |
| FROM exam_supplementary_student_subjects erss | |
| INNER JOIN studentaccount sa ON sa.studentID = erss.studentID | |
| INNER JOIN exam ex ON ex.examID = erss.examID | |
| INNER JOIN exam e ON e.supply_examreg_id = erss.exam_supplementary_id and e.batchID = ex.batchID and e.subjectID = ex.subjectID AND e.supply_examreg_id IS NOT NULL | |
| INNER JOIN exam_supplementary_student_details ersc ON ersc.exam_supplementary_id = erss.exam_supplementary_id AND ersc.studentID = erss.studentID | |
| INNER JOIN batches b ON b.batchID = e.batchID | |
| INNER JOIN subjects s ON s.subjectID = e.subjectID | |
| where erss.exam_supplementary_id IN ($request->examRegId) $conditions ORDER BY b.batchID,e.subjectID"; | |
| }else{ | |
| $sql = "SELECT sa.studentID,sa.regNo,sa.studentName,b.batchName,s.subjectID,s.subjectName,s.subjectDesc,e.examID as regularExamId, | |
| e.examDate ,DATE_FORMAT(e.examDate,'%d-%m-%Y') as examDateFormatted | |
| FROM exam_reg_studentsubject erss | |
| INNER JOIN studentaccount sa ON sa.studentID = erss.studentID | |
| INNER JOIN subjects s ON s.subjectID = erss.subjectID | |
| INNER JOIN exam e ON e.examregID = erss.examregID and e.batchID = sa.batchID and e.subjectID = erss.subjectID AND e.examregID IS NOT NULL | |
| INNER JOIN exam_reg_studentchallan ersc ON ersc.examregID = erss.examregID AND ersc.studentID = erss.studentID | |
| INNER JOIN batches b ON b.batchID = e.batchID | |
| where erss.examregID IN ($request->examRegId) $conditions ORDER BY b.batchID,e.subjectID"; | |
| } | |
| try { | |
| $result = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $result; | |
| } | |
| /** | |
| * Update settings for exam reg blcok by fee due | |
| * @author Sibin | |
| */ | |
| public function excludeStudentsExamRegBlockByFeeDue($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $settingsName = $request->settingsName; | |
| $type = $request->type; | |
| $value = addslashes(json_encode($request->value, JSON_INVALID_UTF8_IGNORE | JSON_PARTIAL_OUTPUT_ON_ERROR | JSON_ERROR_INVALID_PROPERTY_NAME)); | |
| $sql = "UPDATE settings set value ='$value' WHERE name= '$settingsName' AND type='$type'"; | |
| try { | |
| $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return true; | |
| } | |
| /** get exam fees with reservation categories | |
| * @param $request | |
| * @return Object | |
| * @throws ProfessionalException | |
| */ | |
| public function getExamFeeReservationCategoryListByRequest($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $condition = ""; | |
| $examFeesList = []; | |
| if($request->isSupply){ | |
| $condition .= " AND erc.supplyExamRegId = '$request->examRegId' "; | |
| }else{ | |
| $condition .= " AND erc.examRegId = '$request->examRegId' "; | |
| } | |
| $sql = "SELECT ef.examfeesID as examFeesId,ef.examfeesName as examFeesName,sr.reservID,sr.reservName,sr.reservDesc,erc.examRegId,erc.supplyExamRegId | |
| FROM exam_feestype ef | |
| LEFT JOIN examFeesReservationCategoryRelation erc ON erc.examfeesID = ef.examfeesID $condition | |
| LEFT JOIN student_reservation sr ON sr.reservID = erc.reservID"; | |
| try { | |
| $examFeesList = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $examFeesList; | |
| } | |
| /** update exam fees with reservation categories | |
| * @param $request | |
| * @return Object | |
| * @throws ProfessionalException | |
| */ | |
| public function updateExamFeeReservationCategoryListByRequest($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $condition = ""; | |
| $examFeesList = []; | |
| $examRegField = ""; | |
| if ($request->isSupply) { | |
| $condition .= " AND supplyExamRegId = '$request->examRegId' "; | |
| $examRegField= "supplyExamRegId"; | |
| } else { | |
| $condition .= " AND examRegId = '$request->examRegId' "; | |
| $examRegField = "examRegId"; | |
| } | |
| try { | |
| $deleteSql = "DELETE FROM examFeesReservationCategoryRelation WHERE examfeesID IN($request->examFeesId) $condition"; | |
| $this->executeQueryForList($deleteSql); | |
| foreach ($request->reservationIds as $reservationId) { | |
| $request->reservationId = $reservationId; | |
| $sql = "INSERT INTO examFeesReservationCategoryRelation($examRegField,reservID,examfeesID,created_by) VALUES($request->examRegId,$request->reservationId,$request->examFeesId,$request->createdBy)"; | |
| $this->executeQueryForList($sql); | |
| } | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return true; | |
| } | |
| /** | |
| * get exam reg month year by examId | |
| * @author Sibin | |
| */ | |
| public function getExamRegistartionMonthYearByExamId($examId) | |
| { | |
| $examId = $this->realEscapeString($examId); | |
| $sql = "SELECT IF(er.examMonth,er.examMonth,es.examMonth) as month,IF(er.examYear,er.examYear,es.examYear) as year FROM exam e | |
| LEFT JOIN exam_registration er ON er.examregID = e.examregID | |
| LEFT JOIN exam_supplementary es ON es.id = e.supply_examreg_id | |
| WHERE e.examID IN ($examId)"; | |
| try { | |
| $result = $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $result; | |
| } | |
| /** | |
| * get exam reg withheld status | |
| * @author Sibin | |
| */ | |
| public function getStudentRegularSupplyExamRegWithHeldStatus($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $batchCondition =""; | |
| $result = false; | |
| try { | |
| if(!$request->studentId || !$request->semId){ | |
| return null; | |
| } | |
| $batchId = StudentService::getInstance()->getStudentBatchBySemId($request->studentId, $request->semId); | |
| $batchCondition = $batchId ? $batchId :"sa.batchID"; | |
| $regularSql = "SELECT er.examregID as examRegId FROM exam_registration er | |
| INNER JOIN exam_registration_batches erb ON erb.examregID = er.examregID | |
| INNER JOIN exam_reg_studentchallan ers ON ers.examregID = er.examregID | |
| INNER JOIn studentaccount sa ON sa.studentID = ers.studentID | |
| WHERE erb.batchID = $batchCondition AND erb.semID IN ($request->semId) AND ers.studentID IN ($request->studentId)"; | |
| $supplySql = "SELECT es.id as supplyRegId FROM exam_supplementary es | |
| INNER JOIN exam_supplementary_student_details essd ON essd.exam_supplementary_id = es.id | |
| WHERE es.semID IN ($request->semId) AND essd.studentID IN ($request->studentId)"; | |
| $examRegId = $this->executeQueryForObject($regularSql)->examRegId; | |
| $resultSupply = $this->executeQueryForList($supplySql); | |
| if(!empty($resultSupply)){ | |
| $supplyRegId = implode(",", array_column($resultSupply, "supplyRegId")); | |
| } | |
| $withHeldRegular = $examRegId ? $this->getStudentRegularExamResultWithHeldReasonDetails($examRegId, $request->studentId) : ""; | |
| $withHeldSupply = $supplyRegId ? $this->getStudentSupplyExamResultWithHeldReasonDetails($supplyRegId, $request->studentId) :""; | |
| if($withHeldRegular || $withHeldSupply){ | |
| $result = true; | |
| } | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $result; | |
| } | |
| /** | |
| *get students details in suply/regular exam registration regular batch + failed batch | |
| * @return studentDetails | |
| */ | |
| public function getAllExamRegisteredStudents($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $examRegType = $request->examRegType; | |
| $examRegId = $request->examRegId; | |
| $departmentsSelected = $request->departmentsSelected; | |
| $batchSelected = is_array($request->batchSelected) ? implode(",", $request->batchSelected) : $request->batchSelected; | |
| $isMarklistSerialNo = $request->isMarklistSerialNo; | |
| $secondLanguage = $request->secondLanguage; | |
| $coursePatternId = $request->coursePatternId; | |
| $deptIdSql = ""; | |
| $batchIdSql = ""; | |
| $columns = ""; | |
| $joinTable = ""; | |
| $sqlConditions = ''; | |
| if ($departmentsSelected) { | |
| $sqlConditions .= " AND b.deptID IN ($departmentsSelected)"; | |
| } | |
| if ($batchSelected) { | |
| $sqlConditions .= " AND e.batchID IN ($batchSelected)"; | |
| } | |
| if ($examRegType == ExamType::REGULAR) { | |
| $examRegStudentTable = "exam_reg_studentchallan"; | |
| $examRegStudentTableRegColumnName = "examregID"; | |
| $examRegTable = "exam_registration"; | |
| $examRegTableRegColumnName = "examregID"; | |
| $examTableColumnName = "examregID"; | |
| $markListSerialTableExamRegColumnName = "examregID"; | |
| if ($request->excludeStudentsWithCourseMode) { | |
| $sqlConditions .= " AND (ers.courseMode != '$request->excludeStudentsWithCourseMode' || ers.courseMode is null)"; | |
| } | |
| } else if ($examRegType == ExamType::SUPPLY) { | |
| $examRegStudentTable = "exam_supplementary_student_details"; | |
| $examRegStudentTableRegColumnName = "exam_supplementary_id"; | |
| $examRegTable = "exam_supplementary"; | |
| $examRegTableRegColumnName = "id"; | |
| $examTableColumnName = "supply_examreg_id"; | |
| $markListSerialTableExamRegColumnName = "supplyExamRegId"; | |
| } | |
| if ($isMarklistSerialNo) { | |
| $columns .= ",mss.serialNo, | |
| mss.createdDate AS issueDate"; | |
| $joinTable .= " LEFT JOIN | |
| marklist_serialNo_student mss ON mss.studentId = sa.studentID | |
| AND mss.$markListSerialTableExamRegColumnName = ers.$examRegStudentTableRegColumnName"; | |
| } | |
| if ($request->includeBatchDetails) { | |
| $columns .= ",b.batchDesc, | |
| d.deptID, | |
| d.deptName, | |
| d.departmentDesc, | |
| sem.semName AS sem"; | |
| $joinTable .= " INNER JOIN | |
| department d ON d.deptID = b.deptID | |
| INNER JOIN | |
| semesters sem ON sem.semID = e.semID"; | |
| } | |
| if ($request->studentIds) { | |
| $studentIdString = implode(',', $request->studentIds) ? implode(',', $request->studentIds) : $request->studentIds; | |
| $sqlConditions .= " AND sa.studentID IN($studentIdString)"; | |
| } | |
| if ($request->coursePatternId) { | |
| $coursePatternIdString = is_array($request->coursePatternId) ? implode(',', $request->coursePatternId) : $request->coursePatternId; | |
| $sqlConditions .= " AND b.patternID IN($coursePatternIdString)"; | |
| } | |
| if ($request->semId) { | |
| $semIdString = is_array($request->semId) ? implode(',', $request->semId) : $request->semId; | |
| $sqlConditions .= " AND e.semID IN($semIdString)"; | |
| } | |
| if ($request->gender) { | |
| $sqlConditions .= " AND sa.studentGender LIKE '$request->gender'"; | |
| } | |
| if ($request->batchId) { | |
| $batchIdString = is_array($request->batchId) ? implode(',', | |
| $request->batchId | |
| ) : $request->batchId; | |
| $sqlConditions .= " AND b.batchID IN($batchIdString)"; | |
| } | |
| $batchJoinCondition = "INNER JOIN | |
| batches b ON sa.batchID = b.batchID"; | |
| $failedBatchStudentJoinCondition=""; | |
| if ($request->getFailedStudents) { | |
| $batchJoinCondition = "LEFT JOIN | |
| failed_students fs ON fs.studentID = sa.studentID AND FIND_IN_SET(e.semID, fs.hisSemestersInThisbatch) | |
| INNER JOIN | |
| batches b ON b.batchID = IF (fs.previousBatch, fs.previousBatch, sa.batchID)"; | |
| $failedBatchStudentJoinCondition = $batchSelected ? " AND IF (fs.previousBatch, fs.previousBatch, sa.batchID) IN($batchSelected) " :""; | |
| } | |
| try { | |
| $sql = "SELECT DISTINCT | |
| sa.studentID, | |
| sa.studentName, | |
| IF (fs.previousBatch, fs.previousBatch, sa.batchID) as batchID, | |
| sa.myImage, | |
| sa.regNo, | |
| sa.rollNo, | |
| e.semID, | |
| b.batchName, | |
| b.batchOptionName, | |
| pdc.patterncourseCode | |
| $columns | |
| FROM | |
| $examRegStudentTable ers | |
| INNER JOIN | |
| studentaccount sa ON ers.studentID = sa.studentID | |
| INNER JOIN | |
| exam e ON e.$examTableColumnName = ers.$examRegStudentTableRegColumnName | |
| $batchJoinCondition | |
| LEFT JOIN pattern_deptcourses pdc ON pdc.patterncourseID = b.patterncourseID | |
| $joinTable | |
| WHERE | |
| ers.paid = 1 | |
| $sqlConditions | |
| AND ers.$examRegStudentTableRegColumnName = $examRegId | |
| $failedBatchStudentJoinCondition | |
| ORDER BY sa.batchID, sa.regNo"; | |
| $studentDetails = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $studentDetails; | |
| } | |
| public function getStudentExamResultBlockedDetails($studentId) | |
| { | |
| $studentId = $this->realEscapeString($studentId); | |
| $sql = ""; | |
| $withHeldData = new \stdClass; | |
| $withHeldData->isBlocked = false; | |
| try { | |
| $sql = "SELECT | |
| esw.studentID | |
| FROM | |
| exam_students_withheld esw | |
| WHERE esw.withheld = 1 AND esw.studentID = $studentId"; | |
| $sqlSupply = "SELECT | |
| esw.studentID | |
| FROM | |
| supplyexam_students_withheld esw | |
| WHERE esw.withheld = 1 AND esw.studentID = $studentId"; | |
| $withHeldDataRegular = $this->executeQueryForList($sql); | |
| $withHeldDataSupply = $this->executeQueryForList($sqlSupply); | |
| if(!empty($withHeldDataRegular) || !empty($withHeldDataSupply)){ | |
| $withHeldData->isBlocked = true; | |
| } | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $withHeldData; | |
| } | |
| /** | |
| *get students exam reg block status | |
| * @return studentDetails | |
| */ | |
| public function getStudentExamRegBlockStatus($request){ | |
| $request = $this->realEscapeObject($request); | |
| $result = []; | |
| $condition = ""; | |
| if($request->examRegId){ | |
| if ($request->isSupply) { | |
| $condition .= " AND ebs.supplyRegID IN ($request->examRegId)"; | |
| } | |
| else { | |
| $condition .= " AND ebs.examRegId IN ($request->examRegId)"; | |
| } | |
| } | |
| if($request->studentId){ | |
| $condition .= " AND ebs.studentId IN ($request->studentId)"; | |
| } | |
| try { | |
| $sql = "SELECT * FROM exam_blocked_students ebs | |
| WHERE 1 = 1 $condition"; | |
| $result = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $result; | |
| } | |
| public function getStudentExamRegFeePaidDetails($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $sql = ""; | |
| $feeDues = []; | |
| try { | |
| //FOR REGULAR EXAM REGISTRATION FEES | |
| if($request->considerRegular){ | |
| $type = "REGULAR"; | |
| $condition = ""; | |
| $condition .= $request->paid ? " AND ers.paid = 1 " : " AND ers.paid = 0 "; | |
| if ($request->excludeExamRegId) { | |
| $condition .= " AND ers.examregID NOT IN ($request->excludeExamRegId)"; | |
| } | |
| $sql = "SELECT ers.studentID as studentId,ers.examregID as examRegId,ers.paid,'$type' as examType FROM exam_reg_studentchallan ers | |
| WHERE ers.studentID IN ($request->studentId) | |
| $condition"; | |
| $result = $this->executeQueryForList($sql); | |
| if(!empty($result)){ | |
| $feeDues[$type]= $result; | |
| } | |
| } | |
| //FOR SUPPLY EXAM REGISTRATION FEES | |
| if ($request->considerSupply) { | |
| $type = "SUPPLY"; | |
| $condition = ""; | |
| $condition .= $request->paid ? " AND essd.paid = 1 " : " AND essd.paid = 0 "; | |
| if ($request->excludeSupplyRegId) { | |
| $condition .= " AND essd.exam_supplementary_id NOT IN ($request->excludeSupplyRegId)"; | |
| } | |
| $sql = "SELECT essd.studentID as studentId,essd.exam_supplementary_id as examRegId,essd.paid,'$type' as examType FROM exam_supplementary_student_details essd | |
| WHERE essd.studentID IN ($request->studentId) | |
| $condition"; | |
| $result = $this->executeQueryForList($sql); | |
| if (!empty($result)) { | |
| $feeDues[$type] = $result; | |
| } | |
| } | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $feeDues; | |
| } | |
| /** | |
| * get exam reg and date time | |
| * @param $request | |
| * @author sibin | |
| */ | |
| public function getExamsByExamRegAndDate($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $condition = ""; | |
| if ($request->examDateTime) { | |
| $condition .= " AND concat(e.examDate,\" \",DATE_FORMAT(CAST(STR_TO_DATE(e.examStartTime,'%l:%i%p') AS DATETIME),'%r')) = '$request->examDateTime' "; | |
| } | |
| if ($request->examDate) { | |
| $condition .= " AND e.examDate = '$request->examDate' "; | |
| } | |
| if ($request->batchId) { | |
| $condition .= " AND e.batchID IN($request->batchId)"; | |
| } | |
| $exams = []; | |
| $sql = "SELECT e.examID,e.batchID,e.subjectID,s.subjectName,s.subjectDesc,er.examregName,e.examDate,e.examStartTime,er.examregDesc as examRegDesc FROM exam e | |
| INNER JOIN subjects s ON s.subjectID = e.subjectID | |
| INNER JOIN batches b ON b.batchID = e.batchID | |
| INNER JOIN exam_registration er ON er.examregID = e.examregID | |
| WHERE 1=1 AND e.examregID = '$request->examRegId' | |
| $condition | |
| group by e.examID"; | |
| try { | |
| $exams = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $exams; | |
| } | |
| /** | |
| *get oe exam details by examId | |
| * @return studentDetails | |
| * Auther sibin | |
| */ | |
| public function getOeExamDetailsByExamId($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $result = []; | |
| $condition = ""; | |
| if($request->examId){ | |
| if(is_array($request->examId)){ | |
| $request->examId = implode(",", $request->examId); | |
| } | |
| $condition .= " AND oe.identifying_context->>'$.examId' IN ($request->examId)"; | |
| } | |
| if($request->isLocked){ | |
| $condition .=" AND oe.properties ->>'$.isLocked' = '1'"; | |
| } | |
| try { | |
| $sql = "SELECT oe.id,oe.properties,oe.properties ->>'$.isLocked' as isLocked FROM oe_exams oe | |
| WHERE oe.type = 'EXAM_CONTROLLER' AND (oe.properties ->> '$.isMockExam' != 'true' OR oe.properties ->> '$.isMockExam' is null) AND oe.is_archived = 0 | |
| $condition"; | |
| $result = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $result; | |
| } | |
| /** | |
| * Get details of exam of a subject | |
| * @throws ProfessionalException | |
| * @author Sibin | |
| */ | |
| public function getExamDetailsByRequest($examRequest) | |
| { | |
| $examRequest = $this->realEscapeObject($examRequest); | |
| $sql = null; | |
| $condition = null; | |
| $exam = null; | |
| if (!$examRequest->examType || !$examRequest->examRegId) { | |
| return; | |
| } | |
| if ($examRequest->examType === ExamType::REGULAR) { | |
| $condition .= " AND e.examRegId = '$examRequest->examRegId' "; | |
| } else if ($examRequest->examType === ExamType::SUPPLY) { | |
| $condition .= " AND e.supply_examreg_id = '$examRequest->examRegId' "; | |
| } | |
| if($examRequest->subjectId){ | |
| $condition .=" AND e.subjectId = '$examRequest->subjectId'"; | |
| } | |
| if($examRequest->semId){ | |
| $condition .=" AND e.semId = '$examRequest->semId'"; | |
| } | |
| try { | |
| $sql = "SELECT distinct s.subjectID as subjectId,s.subjectName, s.subjectDesc, sem.semName, IF(e.examRegId, er.examregName, es.supplyDesc) AS examRegName,et.typeName ,e.examDate ,e.examID as examId,b.batchName,b.batchID as batchId | |
| FROM exam e INNER JOIN subjects s ON s.subjectID = e.subjectID | |
| INNER JOIN semesters sem ON sem.semID = e.semID | |
| LEFT JOIN exam_registration er ON er.examregID = e.examregID | |
| LEFT JOIN exam_supplementary es ON es.id = e.supply_examreg_id | |
| LEFT JOIN exam_type et ON et.typeID = e.examTypeID | |
| LEFT JOIN batches b ON b.batchID = e.batchID | |
| WHERE 1=1 | |
| $condition | |
| "; | |
| $exam = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $exam; | |
| } | |
| /** | |
| * Get details of exam of a students by attendance | |
| * @throws ProfessionalException | |
| * @author Sibin | |
| */ | |
| public function getStudentAttendedExamsBySem($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $sql = null; | |
| $condition = null; | |
| $exams=[]; | |
| if($request->batchId){ | |
| $condition.= " AND e.batchID IN($request->batchId)"; | |
| } | |
| if ($request->semId) { | |
| $condition .= " AND e.semID IN($request->semId)"; | |
| } | |
| if ($request->isTheory) { | |
| $condition .= " AND s.isTheory IN($request->isTheory)"; | |
| } | |
| if ($request->studentId) { | |
| $condition .= " AND ea.studentID IN($request->studentId)"; | |
| } | |
| try { | |
| $sql = "SELECT e.examID,e.examTotalMarks,ea.isAbsent,s.isTheory FROM exam e | |
| INNER JOIN subjects s ON s.subjectID = e.subjectID | |
| INNER JOIN exam_attendance ea ON ea.examID = e.examID AND ea.isAbsent NOT IN (1) | |
| WHERE e.examregID IS NOT NULL $condition"; | |
| $exams = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $exams; | |
| } | |
| /** | |
| * @author Sibin | |
| */ | |
| public function getExamRegisteredStudentsWithSubjects($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $examRegId = $request->examRegId; | |
| $batchId = $request->batchId; | |
| $studentDetails = []; | |
| if ((int)$request->isSupply) { | |
| $sql = "SELECT sa.studentID as id,sa.regNo,sa.studentName,er.examMonth,er.examYear,es.semID,es.subjectID ,s.subjectName,sc.subjectcatName,subjectDesc,es.examDate ,es.examStartTime FROM studentaccount sa | |
| INNER JOIN exam_supplementary_student_subjects esss ON (sa.studentID = esss.studentID) | |
| INNER JOIN exam e ON (e.examID = esss.examID) | |
| INNER JOIN exam es ON (e.subjectID = es.subjectID AND es.supply_examreg_id = esss.exam_supplementary_id AND es.batchID = e.batchID AND es.semID = e.semID) | |
| INNER JOIN exam_supplementary_student_details essd ON essd.studentID = sa.studentID AND essd.exam_supplementary_id =esss.exam_supplementary_id | |
| INNER JOIN subjects s ON s.subjectID = e.subjectID | |
| INNER JOIN exam_supplementary er ON er.id = es.supply_examreg_id | |
| LEFT JOIN subject_category sc ON sc.subjectcatID = s.subjectcatID | |
| WHERE esss.exam_supplementary_id = '$examRegId' AND e.batchID='$batchId' AND essd.paid=1 order by sa.regNo,es.examDate"; | |
| } else { | |
| $sql = "SELECT sa.studentID as id,sa.regNo,sa.studentName,er.examMonth,er.examYear,e.semID,e.subjectID ,s.subjectName,sc.subjectcatName,subjectDesc,e.examDate ,e.examStartTime FROM studentaccount sa | |
| INNER JOIN exam_reg_studentsubject erss ON (sa.studentID = erss.studentID) | |
| INNER JOIN exam e ON (e.examregID = erss.examregID AND e.subjectID = erss.subjectID AND sa.batchID = e.batchID) | |
| INNER JOIN exam_reg_studentchallan ersc ON ersc.studentID = sa.studentID AND ersc.examregID =erss.examregID | |
| INNER JOIN subjects s ON s.subjectID = e.subjectID | |
| INNER JOIN exam_registration er ON er.examregID = e.examregID | |
| LEFT JOIN subject_category sc ON sc.subjectcatID = s.subjectcatID | |
| WHERE erss.examregID = '$examRegId' AND sa.batchID='$batchId' AND ersc.paid=1 order by sa.regNo,e.examDate"; | |
| } | |
| try { | |
| $studentDetails = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $studentDetails; | |
| } | |
| /**Update property for excluding from hall ticket subject hide | |
| * @author Sibin | |
| */ | |
| public function updateExcludeHallTicketBlockSubjectStatus($request){ | |
| $request = $this->realEscapeObject($request); | |
| $properties = $request->properties ? json_encode($request->properties):""; | |
| try { | |
| if($request->studentId && $request->examRegId && $request->subjectId){ | |
| $sql = "UPDATE exam_reg_studentsubject set properties = '$properties' WHERE studentId = $request->studentId AND examregID = $request->examRegId AND subjectID = $request->subjectId"; | |
| $this->executeQueryForObject($sql); | |
| return true; | |
| } | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** Get students supply exam registrations by regular examid | |
| * @author Sibin | |
| */ | |
| public function getStudentSupplyExamRegistrationsByRegularExamId($request){ | |
| $request = $this->realEscapeObject($request); | |
| $examReg = []; | |
| try { | |
| if($request->studentId && $request->examId){ | |
| $sql = "SELECT distinct(essd.exam_supplementary_id) as supplyExamRegId,es.supplyDesc FROM exam_supplementary_student_details essd | |
| INNER JOIN exam_supplementary_student_subjects esss ON esss.studentID = essd.studentID AND esss.exam_supplementary_id = essd.exam_supplementary_id | |
| INNER JOIN exam_supplementary es ON es.id = essd.exam_supplementary_id | |
| WHERE essd.studentID = $request->studentId AND esss.examID = $request->examId AND essd.paid = 1"; | |
| $examReg = $this->executeQueryForList($sql); | |
| } | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $examReg; | |
| } | |
| /** | |
| * get students by mooc exam registration subject | |
| * @throws ProfessionalException | |
| */ | |
| public function getMoocExamRegistrationStudentSubject($req) | |
| { | |
| $req = $this->realEscapeObject($req); | |
| $request = clone $req; | |
| $sql = null; | |
| $condition = ""; | |
| $students=[]; | |
| if($request->examRegId){ | |
| $condition .=" AND ersc.examregID IN ($request->examRegId)"; | |
| } | |
| if ($request->batchId) { | |
| $condition .= " AND sa.batchID IN ($request->batchId)"; | |
| } | |
| if ($request->courseMode) { | |
| $condition .= " AND ersc.courseMode = '$request->courseMode' "; | |
| } | |
| if ($request->studentId) { | |
| $condition .= " AND ersc.studentID = '$request->studentId' "; | |
| } | |
| try { | |
| if($request->courseType == CourseTypeConstants::PG){ | |
| $request->subjectType = "MOOC"; | |
| $request->subjectId = (int)current(SubjectService::getInstance()->getStudentAppliedSubjectDetails($request))->subjectId; | |
| } | |
| if ($request->subjectId) { | |
| $condition .= " AND erss.subjectID IN ($request->subjectId)"; | |
| } | |
| $sql = "SELECT ersc.studentID,sa.regNo,sa.studentName,ersc.approved,ersc.courseMode,ersc.payment_remarks,erss.properties,erss.subjectID as subjectId | |
| 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 | |
| WHERE 1 =1 | |
| $condition"; | |
| $result = $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $result; | |
| } | |
| /** | |
| * get students by mooc exam registration subject | |
| * @throws ProfessionalException | |
| */ | |
| public function updateMoocExamRegistrationStudentSubject($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $sql = null; | |
| $condition = ""; | |
| if($request->examRegId){ | |
| $condition .=" AND examregID IN ($request->examRegId)"; | |
| } | |
| if ($request->studentId) { | |
| $condition .= " AND studentID IN ($request->studentId)"; | |
| } | |
| if ($request->subjectId) { | |
| $condition .= " AND subjectID IN ($request->subjectId)"; | |
| } | |
| try { | |
| if($request->moocSubjectName && $request->examRegId && $request->studentId){ | |
| $sql = "UPDATE exam_reg_studentsubject SET properties = JSON_SET( | |
| COALESCE(properties, '{}'), | |
| '$.MOOC_SUBJECT_NAME', '$request->moocSubjectName', | |
| '$.MOOC_SUBJECT_CODE', '$request->moocSubjectCode', | |
| '$.MOOC_SUBJECT_MONTH_YEAR', '$request->moocSubjectMonthYear' | |
| ) | |
| WHERE 1 =1 $condition"; | |
| $result = $this->executeQueryForObject($sql); | |
| } | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $result; | |
| } | |
| /** Get students exam reg eligibilty by day wise attenadance | |
| * param request | |
| * @author Sibin | |
| */ | |
| public function checkStudentExamRegEligibilityByDayWiseAttendance($request){ | |
| if(is_array($request)){ | |
| $request = (object)$request; | |
| } | |
| $request = $this->realEscapeObject($request); | |
| $studentId = $request->studentId; | |
| $semId = $request->semId; | |
| $examRegId = $request->examRegId; | |
| $attClosingDate = $this->getAttendanceClosingDateByStudentSem($request)->attClosingDate; | |
| $attClosingDate = $attClosingDate ? $attClosingDate : null; | |
| $response = new stdClass; | |
| $response->enableDayWiseAttendanceChecking = false; | |
| $response->eligibleToApplyByDayWiseAttendance = true; | |
| $condonationStatus = ""; | |
| try { | |
| if($studentId){ | |
| $minAttPercent = $examRegId ? ExamRegistrationService::getInstance()->getExamRegistrationById($examRegId)->minAttPercentage :""; | |
| $minAttendanceForExamReg = json_decode(CommonService::getInstance()->getSettings(SettingsConstents::EXAM_CONTROLLER, SettingsConstents::SEMESTER_EXAM_SETTINGS))->minAttendanceForExamReg; | |
| if($minAttendanceForExamReg->enableDayWiseAttendanceChecking){ | |
| $response->enableDayWiseAttendanceChecking = true; | |
| $response->eligibleToApplyByDayWiseAttendance = false; | |
| $response->isCondonationApproved = false; | |
| //Get the attendance of student for a semester ,datewise | |
| $dayWiseAttendance = current(AttendanceService::getInstance()->getAttendanceDetailsApi($studentId,$semId ? $semId : null,$attClosingDate ? $attClosingDate : null)->dayWise->data); | |
| $studentDetail = StudentService::getInstance()->getStudentDetailsById($studentId); | |
| $studentGender = strtolower($studentDetail->studentGender); | |
| if($studentDetail->studentGender){ | |
| // SET MIN ATTENDANCE PERCENT ACCORDING TO GENDER | |
| $minAttendanceForExamReg->minAttPercent = $minAttendanceForExamReg->$studentGender; | |
| $minAttendanceForExamReg->minAttPercent = $minAttendanceForExamReg->minAttPercent ? $minAttendanceForExamReg->minAttPercent : $minAttPercent; | |
| $minAttendanceForExamReg->minAttPercent = $minAttendanceForExamReg->minAttPercent ? $minAttendanceForExamReg->minAttPercent : $minAttendanceForExamReg->other; | |
| $response->minAttPercent = $minAttendanceForExamReg->minAttPercent; | |
| $response->haveMinAttendance = $this->checkHaveMinimumAttendance($dayWiseAttendance->currentDayWiseAttendancePercentageInASemester,$minAttendanceForExamReg->minAttPercent); | |
| $response->eligibleToApplyByDayWiseAttendance = $response->haveMinAttendance ? true : $response->eligibleToApplyByDayWiseAttendance; | |
| $response->eligibleForCondonation = $response->eligibleToApplyByDayWiseAttendance ? false : $this->isEligibleForCondonation($dayWiseAttendance,$minAttendanceForExamReg->minAttPercent); | |
| if($response->eligibleForCondonation){ | |
| //block for getting condonation approved status | |
| // studentId,semId,workflowid need to be passed to get condonation workflow status | |
| if($semId && $examRegId){ | |
| $workflowId = $this->getWorkFlowIdByExamRegId($examRegId)->condonation; | |
| $response->workflowId = $workflowId; | |
| $response->eligibleForCondonation = $workflowId ? $response->eligibleForCondonation : false; | |
| $condonationStatus = $workflowId ? ExamService::getInstance()->getStatusOfWorkflowRequest($studentId,$semId,$workflowId ) :""; | |
| } | |
| $response->isCondonationApproved = ($condonationStatus == "APPROVED") ? true : false; | |
| $response->eligibleToApplyByDayWiseAttendance = $response->isCondonationApproved ? true : $response->eligibleToApplyByDayWiseAttendance; | |
| } | |
| } | |
| } | |
| } | |
| return $response; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| private function checkHaveMinimumAttendance($attPercent,$minAttend){ | |
| $haveMinAttendance = (float) $attPercent >= (float)$minAttend; | |
| return $haveMinAttendance; | |
| } | |
| private function isEligibleForCondonation($dayWiseAttendance,$minAttend){ | |
| // value can be made dynamic later as of now it is 10 | |
| $maxAttendanceAllowance = 10; | |
| $totalAttandaceDays = $dayWiseAttendance->totalNoOfWorkingDays; | |
| $totalPresentDays = $dayWiseAttendance->totalAttendedWorkingDaysInASemester + $maxAttendanceAllowance; | |
| $attendancePercentage = $totalAttandaceDays ? ($totalPresentDays / $totalAttandaceDays) * 100 : 0; | |
| $haveMinAttendance = $this->checkHaveMinimumAttendance($attendancePercentage,$minAttend); | |
| return $haveMinAttendance; | |
| } | |
| public function getWorkFlowIdByExamRegId($examRegId){ | |
| $examRegId = $this->realEscapeString($examRegId); | |
| try { | |
| if($examRegId){ | |
| $sql = "SELECT properties->> '$.workflowIds' as workflowIds FROM exam_registration WHERE examregID IN($examRegId)"; | |
| $result = $this->executeQueryForObject($sql); | |
| $workflowIds = $result ? json_decode($result->workflowIds):""; | |
| } | |
| return $workflowIds; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| public function checkEligibleForCondonation($params) | |
| { | |
| $checkStudentExamRegEligibilityByDayWiseAttendance = new \stdClass; | |
| $checkStudentExamRegEligibilityByDayWiseAttendance->studentId = $params['studentId']; | |
| $checkStudentExamRegEligibilityByDayWiseAttendance->semId = $params['academicTermId']; | |
| $checkStudentExamRegEligibilityByDayWiseAttendance->examRegId = $params['examRegId']; | |
| try{ | |
| $isEligible = $this->checkStudentExamRegEligibilityByDayWiseAttendance($checkStudentExamRegEligibilityByDayWiseAttendance)->eligibleForCondonation ? "1" : "0"; | |
| return $isEligible; | |
| }catch(\Exception $e) { | |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); | |
| } | |
| } | |
| public function getAttendanceClosingDateByStudentSem($request){ | |
| $request = $this->realEscapeObject($request); | |
| try{ | |
| if($request->studentId && $request->semId){ | |
| $sql = "SELECT erb.id,erb.attClosingDate FROM exam_registration_batches erb | |
| INNER JOIN studentaccount sa ON sa.batchID = erb.batchID | |
| WHERE erb.semID = '$request->semId' AND sa.studentID IN ($request->studentId)"; | |
| $result = $this->executeQueryForObject($sql); | |
| return $result; | |
| } | |
| }catch(\Exception $e) { | |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); | |
| } | |
| } | |
| /** | |
| * get exam reg details by examId | |
| * @author Sibin | |
| */ | |
| public function getExamRegistrationDetailsByExamId($examId) | |
| { | |
| $examId = $this->realEscapeString($examId); | |
| $sql = "SELECT e.subjectID as subjectId,e.examregID as examRegId,e.semID as semId,e.batchID as batchId,er.shortCourse,er.examMonth as month,er.examYear as year FROM exam e | |
| INNER JOIN exam_registration er ON er.examregID = e.examregID | |
| WHERE e.examID IN ($examId)"; | |
| try { | |
| $result = $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $result; | |
| } | |
| /** | |
| * get regular eam mal students by supply regId | |
| * @author Sibin | |
| */ | |
| public function getRegularMalStudentsBySupplyRegId($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| try { | |
| $regularExamReg = ExamSupplementaryService::getInstance()->getRegularExamRegistrationByExamSupplyRegId($request); | |
| if($regularExamReg){ | |
| $regularExamRegIds = implode(",",array_column($regularExamReg,'examRegId')); | |
| $sql = "SELECT sa.studentID,sa.studentName ,sa.regNo ,er.examregID,er.examregName,sms.isPaid,sms.id FROM exam_attendance ea | |
| INNER JOIN exam e ON e.examID = ea.examID | |
| INNER JOIN exam_registration er ON er.examregID = e.examregID | |
| INNER JOIN studentaccount sa ON sa.studentID = ea.studentID | |
| LEFT JOIN supply_improve_examcontroller_mal_students sms ON sms.examregID = er.examregID AND sms.studentID = sa.studentID AND sms.exam_supplementary_id IN ($request->examRegId) | |
| WHERE ea.isAbsent =2 AND e.examregID IN ($regularExamRegIds) GROUP BY sa.studentID"; | |
| $result = $this->executeQueryForList($sql); | |
| } | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $result; | |
| } | |
| } | |
| ?> |