Code Coverage |
||||||||||
Classes and Traits |
Functions and Methods |
Lines |
||||||||
| Total | |
0.00% |
0 / 1 |
|
0.00% |
0 / 69 |
CRAP | |
0.00% |
0 / 1966 |
| getDataType | |
0.00% |
0 / 1 |
30.00 | |
0.00% |
0 / 15 |
|||
| CommonExamService | |
0.00% |
0 / 1 |
|
0.00% |
0 / 68 |
148610.00 | |
0.00% |
0 / 1951 |
| __construct | |
0.00% |
0 / 1 |
2.00 | |
0.00% |
0 / 2 |
|||
| getAllFeesTypes | |
0.00% |
0 / 1 |
90.00 | |
0.00% |
0 / 44 |
|||
| getAllFineTypes | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 16 |
|||
| getAllRevaluationFeesTypes | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 15 |
|||
| getFirstYearSemesters | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 15 |
|||
| getDifferentSemesterName | |
0.00% |
0 / 1 |
156.00 | |
0.00% |
0 / 69 |
|||
| getCollegeDetails | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 40 |
|||
| getAllDepartmentByOterDetails | |
0.00% |
0 / 1 |
42.00 | |
0.00% |
0 / 29 |
|||
| getAllBatchesByOtherDetails | |
0.00% |
0 / 1 |
72.00 | |
0.00% |
0 / 32 |
|||
| getAssignedSubjectsInBatch | |
0.00% |
0 / 1 |
342.00 | |
0.00% |
0 / 94 |
|||
| getSessionalExamTypes | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 14 |
|||
| getAutonomousSettingsByNameAndType | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 10 |
|||
| getFinalAcademicTermDetails | |
0.00% |
0 / 1 |
42.00 | |
0.00% |
0 / 28 |
|||
| getAllAcademicTermsDetailsByBatch | |
0.00% |
0 / 1 |
42.00 | |
0.00% |
0 / 31 |
|||
| getGradeByPercentage | |
0.00% |
0 / 1 |
30.00 | |
0.00% |
0 / 13 |
|||
| getDepartmentsByAssignedStaffs | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 14 |
|||
| getAllDegreeDetails | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 12 |
|||
| setObject | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 5 |
|||
| returnUniqueProperty | |
0.00% |
0 / 1 |
2.00 | |
0.00% |
0 / 4 |
|||
| returnUniqueArrayProperty | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 9 |
|||
| getCustumYears | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 16 |
|||
| searchSubjectTypes | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 8 |
|||
| searchSubjectSlots | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 13 |
|||
| searchSubjectCategories | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 8 |
|||
| getStudentAcademicTerms | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 24 |
|||
| checkIsFailedByPassPercentCriteria | |
0.00% |
0 / 1 |
756.00 | |
0.00% |
0 / 54 |
|||
| addOrdinalNumberSuffix | |
0.00% |
0 / 1 |
42.00 | |
0.00% |
0 / 20 |
|||
| getStudentExamMarkListSerialNumber | |
0.00% |
0 / 1 |
56.00 | |
0.00% |
0 / 25 |
|||
| getStudentDetailsByStudentId | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 25 |
|||
| getAcademicTermDetailsUptoFinalTerm | |
0.00% |
0 / 1 |
42.00 | |
0.00% |
0 / 34 |
|||
| getStudentsDetailsByBatchProgram | |
0.00% |
0 / 1 |
72.00 | |
0.00% |
0 / 72 |
|||
| getBatchGroupDetailsByGroupId | |
0.00% |
0 / 1 |
30.00 | |
0.00% |
0 / 46 |
|||
| getGradeByCreditPercentage | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 8 |
|||
| getFailedGrade | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 9 |
|||
| checkIsFailedByPassPercentCreditCriteria | |
0.00% |
0 / 1 |
2070.00 | |
0.00% |
0 / 105 |
|||
| checkIfSujectIsElective | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 16 |
|||
| getStudentAdditionalCredits | |
0.00% |
0 / 1 |
56.00 | |
0.00% |
0 / 33 |
|||
| getCustomFieldObjectList | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 17 |
|||
| getAcademicTermDetailsUptoCurrentAcademicTermId | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 22 |
|||
| getStudentBatchUniqueNumber | |
0.00% |
0 / 1 |
42.00 | |
0.00% |
0 / 25 |
|||
| getStudentProfilePic | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 10 |
|||
| getStudentSignPic | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 10 |
|||
| staffAccessibleProgramDepartmentAndBatches | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 34 |
|||
| getStudentMarkHistoryByGroup | |
0.00% |
0 / 1 |
42.00 | |
0.00% |
0 / 64 |
|||
| getAssignedSubjectsDetailsInBatch | |
0.00% |
0 / 1 |
272.00 | |
0.00% |
0 / 117 |
|||
| returnUniquePropertyArray | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 9 |
|||
| getSyllabusType | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 9 |
|||
| getAllSubCurriculum | |
0.00% |
0 / 1 |
156.00 | |
0.00% |
0 / 65 |
|||
| saveSyllabusProperties | |
0.00% |
0 / 1 |
42.00 | |
0.00% |
0 / 23 |
|||
| getSubjectClassTypes | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 9 |
|||
| getStudentCountForPaperSubjects | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 25 |
|||
| getStaffBankAccountDetails | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 30 |
|||
| getStudentsActivityPoints | |
0.00% |
0 / 1 |
56.00 | |
0.00% |
0 / 32 |
|||
| getAllDegrees | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 18 |
|||
| getAllBatchesByDegree | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 27 |
|||
| getAllSubjectSlots | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 20 |
|||
| isValidURL | |
0.00% |
0 / 1 |
2.00 | |
0.00% |
0 / 10 |
|||
| getAllRolesByUserId | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 22 |
|||
| getExamHallByAssessment | |
0.00% |
0 / 1 |
110.00 | |
0.00% |
0 / 42 |
|||
| getExambatchesByRequest | |
0.00% |
0 / 1 |
42.00 | |
0.00% |
0 / 29 |
|||
| getstudentRegisteredForExam | |
0.00% |
0 / 1 |
30.00 | |
0.00% |
0 / 26 |
|||
| getExternalValuationMarkDiff | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 25 |
|||
| geSubjectPaperDetails | |
0.00% |
0 / 1 |
56.00 | |
0.00% |
0 / 44 |
|||
| getAllSchools | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 20 |
|||
| getAcademicTermsDetails | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 21 |
|||
| getFacultyAssignedExamRegMonthYears | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 25 |
|||
| geSubjectCategoryDetails | |
0.00% |
0 / 1 |
72.00 | |
0.00% |
0 / 57 |
|||
| getSubCourseRelations | |
0.00% |
0 / 1 |
132.00 | |
0.00% |
0 / 52 |
|||
| <?php | |
| namespace com\linways\ec\core\service; | |
| use com\linways\base\util\SecurityUtils; | |
| use com\linways\base\util\MakeSingletonTrait; | |
| use com\linways\ec\core\constant\StatusConstants; | |
| use com\linways\ec\core\exception\ExamControllerException; | |
| use com\linways\core\ams\professional\service\StaffService; | |
| use com\linways\core\ams\professional\service\CommonService; | |
| use com\linways\core\ams\professional\constant\SettingsConstants; | |
| use com\linways\base\util\TwigRenderer; | |
| use com\linways\core\ams\professional\util\PdfUtil; | |
| use com\linways\ec\core\mapper\ExamRevaluationFeeTypeMapper; | |
| use com\linways\ec\core\mapper\CommonExamMapper; | |
| use com\linways\core\ams\professional\util\CommonUtil; | |
| use com\linways\core\ams\professional\service\StudentService; | |
| use com\linways\ec\core\constant\SyllabusTypeConstants; | |
| use stdClass; | |
| class CommonExamService extends BaseService{ | |
| use MakeSingletonTrait; | |
| private function __construct() { | |
| $this->mapper2 = CommonExamMapper::getInstance()->getMapper(); | |
| } | |
| /** | |
| * Get All Fees Types | |
| * @return feeTypes | |
| */ | |
| public function getAllFeesTypes($searchRequest){ | |
| $searchRequest = $this->realEscapeObject($searchRequest); | |
| $whereQuery = ""; | |
| $sortBy = ""; | |
| if(!empty($searchRequest->examType)) { | |
| $examTypeString = is_array($searchRequest->examType) ? "'" . implode("','",$searchRequest->examType) . "'" : "'".$searchRequest->examType."'"; | |
| $whereQuery .= " AND exam_type IN ( $examTypeString )"; | |
| } | |
| if(!empty($searchRequest->excludeCommonFees)) { | |
| $whereQuery .= " AND is_common != 1"; | |
| } | |
| if(!empty($searchRequest->sortByCommonFee)) { | |
| $sortBy .= " ORDER BY is_common DESC"; | |
| } | |
| if(!empty($searchRequest->feeTypeIds)) { | |
| $whereQuery .= " AND examfeesID IN ($searchRequest->feeTypeIds)"; | |
| } | |
| try { | |
| $query = "SELECT | |
| examfeesID as id, | |
| examfeesName as name, | |
| exam_fee_code as code, | |
| is_common as isCommon, | |
| mark_entry_needed as markEntryNeeded, | |
| isTheory, | |
| everySubject, | |
| isSubject_fee_limit, | |
| subject_type as subjectType, | |
| properties as properties, | |
| properties ->> '$.feeSelectionSubjectLimit' as feeSelectionSubjectLimit, | |
| properties ->> '$.considerFeeSelectionSubject' as considerFeeSelectionSubject | |
| FROM | |
| exam_feestype | |
| WHERE 1=1 "; | |
| $feeTypes = $this->executeQueryForList($query.$whereQuery.$sortBy); | |
| array_walk($feeTypes, function($feeType){ | |
| $feeType->properties = $feeType->properties ? json_decode($feeType->properties) : new \stdClass(); | |
| $feeType->description = $feeType->properties ? $feeType->properties->description : ""; | |
| }); | |
| } | |
| catch (\Exception $e) | |
| { | |
| throw new ExamControllerException($e->getCode(),$e->getMessage()); | |
| } | |
| return $feeTypes; | |
| } | |
| /** | |
| * Get All Fine Types | |
| * @return feeTypes | |
| */ | |
| public function getAllFineTypes(){ | |
| try{ | |
| $orderBy = " ORDER BY priority ASC "; | |
| $query = "SELECT | |
| examfineID as id, | |
| examfineName as name, | |
| priority | |
| FROM | |
| exam_finetype "; | |
| $fineTypes = $this->executeQueryForList($query.$orderBy); | |
| } | |
| catch (\Exception $e) | |
| { | |
| throw new ExamControllerException($e->getCode(),$e->getMessage()); | |
| } | |
| return $fineTypes; | |
| } | |
| /** | |
| * Get All Revaluation Fees Types | |
| * @return feeTypes | |
| */ | |
| public function getAllRevaluationFeesTypes(){ | |
| try { | |
| $query = "SELECT | |
| id, | |
| name, | |
| properties | |
| FROM | |
| ec_revaluation_fee_types "; | |
| $feeTypes = $this->executeQueryForList($query,$this->mapper[ExamRevaluationFeeTypeMapper::SEARCH_EXAM_REVALUATION_FEE_TYPES]); | |
| } | |
| catch (\Exception $e) | |
| { | |
| throw new ExamControllerException($e->getCode(),$e->getMessage()); | |
| } | |
| return $feeTypes; | |
| } | |
| /** | |
| * Get FirstYear Semester (For Pg Diploma) | |
| * @return semesters | |
| */ | |
| public function getFirstYearSemesters(){ | |
| try { | |
| $query = "SELECT | |
| id, | |
| name | |
| FROM | |
| academic_term | |
| WHERE type = 'SEMESTER' AND properties->>'$.year' = '1' order by name"; | |
| $semesters = $this->executeQueryForList($query); | |
| } | |
| catch (\Exception $e) | |
| { | |
| throw new ExamControllerException($e->getCode(),$e->getMessage()); | |
| } | |
| return $semesters; | |
| } | |
| /** | |
| * get Different Semester Name | |
| * @return semesterName | |
| * @author Krishnajith | |
| */ | |
| public function getDifferentSemesterName($semesterName,$considerFullTermName = false){ | |
| $semesterData = new \stdClass; | |
| $semesterData->prefix = "th"; | |
| if($considerFullTermName){ | |
| $semNumber = $semesterName; | |
| }else{ | |
| $semNumber = substr($semesterName, -1); | |
| } | |
| switch ($semNumber) { | |
| case '1': | |
| $semesterData->romanLetter = "I"; | |
| $semesterData->fullName = "First"; | |
| $semesterData->semNumber = "1"; | |
| $semesterData->prefix = "st"; | |
| break; | |
| case '2': | |
| $semesterData->romanLetter = "II"; | |
| $semesterData->fullName = "Second"; | |
| $semesterData->semNumber = "2"; | |
| $semesterData->prefix = "nd"; | |
| break; | |
| case '3': | |
| $semesterData->romanLetter = "III"; | |
| $semesterData->fullName = "Third"; | |
| $semesterData->semNumber = "3"; | |
| $semesterData->prefix = "rd"; | |
| break; | |
| case '4': | |
| $semesterData->romanLetter = "IV"; | |
| $semesterData->fullName = "Fourth"; | |
| $semesterData->semNumber = "4"; | |
| break; | |
| case '5': | |
| $semesterData->romanLetter = "V"; | |
| $semesterData->fullName = "Fifth"; | |
| $semesterData->semNumber = "5"; | |
| break; | |
| case '6': | |
| $semesterData->romanLetter = "VI"; | |
| $semesterData->fullName = "Sixth"; | |
| $semesterData->semNumber = "6"; | |
| break; | |
| case '7': | |
| $semesterData->romanLetter = "VII"; | |
| $semesterData->fullName = "Seventh"; | |
| $semesterData->semNumber = "7"; | |
| break; | |
| case '8': | |
| $semesterData->romanLetter = "VIII"; | |
| $semesterData->fullName = "Eighth"; | |
| $semesterData->semNumber = "8"; | |
| break; | |
| case '9': | |
| $semesterData->romanLetter = "IX"; | |
| $semesterData->fullName = "Ninth"; | |
| $semesterData->semNumber = "9"; | |
| break; | |
| case '10': | |
| $semesterData->romanLetter = "X"; | |
| $semesterData->fullName = "Tenth"; | |
| $semesterData->semNumber = "10"; | |
| break; | |
| default: | |
| $semesterData->romanLetter = ""; | |
| $semesterData->fullName = ""; | |
| $semesterData->semNumber = ""; | |
| break; | |
| } | |
| return $semesterData; | |
| } | |
| /** | |
| * get College And Other Data | |
| * @author Krishnajith | |
| */ | |
| public function getCollegeDetails(){ | |
| $collegeData = new \stdClass; | |
| $collegeData->collegeName = $GLOBALS['COLLEGE_NAME']; | |
| $collegeData->place = $GLOBALS['PLACE']; | |
| if($GLOBALS['UNIVERSITY_NAME']){ | |
| $collegeData->universityName = "AUTONOMOUS COLLEGE AFFILIATED TO ".$GLOBALS['UNIVERSITY_NAME']; | |
| } | |
| else{ | |
| $collegeData->universityName = "AUTONOMOUS"; | |
| } | |
| $baseUrl = "http://".$_SERVER['HTTP_HOST']; | |
| $collegeCode = $GLOBALS['COLLEGE_CODE']; | |
| $collegeData->collageLogo = "$baseUrl/libcommon/images/college/$collegeCode/logo.png"; | |
| $collegeData->coeSign = "$baseUrl/libcommon/images/college/$collegeCode/examcontrollerSignature.png"; | |
| $collegeData->waterMark = "$baseUrl/libcommon/images/college/$collegeCode/watermark.png"; | |
| $collegeData->collegeSeal = "$baseUrl/libcommon/images/college/$collegeCode/college-seal.png"; | |
| $collegeData->principalSign = "$baseUrl/libcommon/images/college/$collegeCode/principalSign.png"; | |
| $collegeData->principalSignOld = "$baseUrl/libcommon/images/college/$collegeCode/principalSignOld.png"; | |
| $collegeData->collegeHeader = "$baseUrl/libcommon/images/college/$collegeCode/college_header_banner.png"; | |
| $collegeData->collegeHeaderProvisional = "$baseUrl/libcommon/images/college/$collegeCode/college-header-provisional.png"; | |
| $collegeData->directorSign = "$baseUrl/libcommon/images/college/$collegeCode/directorSign.png"; | |
| $collegeData->viceChancellorSign = "$baseUrl/libcommon/images/college/$collegeCode/viceChancellorSign.png"; | |
| $collegeData->principalSeal = "$baseUrl/libcommon/images/college/$collegeCode/principalSeal.png"; | |
| // this function may be changed ........ | |
| $principalDetail = StaffService::getInstance()->getPrincipalDetails(); | |
| if($principalDetail){ | |
| $collegeData->principalName = $principalDetail->salutation ." ". $principalDetail->principalName; | |
| } | |
| $collegeData->examControllerName = $GLOBALS['CONTROLLER_OF_EXAMINATION_NAME']; | |
| $collegeData->collegeAddress = $GLOBALS['COLLEGE_ADDRESS1']; | |
| // $collegeData->collageLogo = ''; | |
| $collegeData->collegeAddress2 = $GLOBALS['COLLEGE_ADDRESS2']; | |
| $collegeData->collegeAddress3 = $GLOBALS['COLLEGE_ADDRESS3']; | |
| $collegeData->autonomous = $GLOBALS['autonomous']; | |
| $collegeData->collegePhone = $GLOBALS['COLLEGE_CONTACT1']; | |
| $collegeData->collegeEail = $GLOBALS['COLLEGE_CONTACT2']; | |
| $collegeData->collegeWebsite = $GLOBALS['COLLEGE_WEBSITE']; | |
| $collegeData->place = $GLOBALS['PLACE']; | |
| $collegeData->centerOfExamination = $GLOBALS['CENTER_OF_EXAMINATION']; | |
| $collegeData->collegeBanner = ""; | |
| $collegeData->nameOfAffiliatingUniversity = $GLOBALS['AFFILIATING_UNIVERSITY_NAME']; | |
| return $collegeData; | |
| } | |
| /** | |
| * get All Department By Oter Details | |
| * @author Krishnajith | |
| */ | |
| public function getAllDepartmentByOterDetails($searchRequest){ | |
| $searchRequest = $this->realEscapeObject($searchRequest); | |
| $whereQuery = ""; | |
| if(!empty($searchRequest->examRegistrationId)) { | |
| $examRegistrationIdString = is_array($searchRequest->examRegistrationId) ? "'" . implode("','",$searchRequest->examRegistrationId) . "'" : "'".$searchRequest->examRegistrationId."'"; | |
| $whereQuery .= " AND eerb.ec_exam_registration_id IN ( $examRegistrationIdString )"; | |
| } | |
| if(!empty($searchRequest->departmentId)) { | |
| $departmentIdString = is_array($searchRequest->departmentId) ? "'" . implode("','",$searchRequest->departmentId) . "'" : "'".$searchRequest->departmentId."'"; | |
| $whereQuery .= " AND dept.deptID IN ( $departmentIdString )"; | |
| } | |
| $sql = "SELECT DISTINCT | |
| dept.deptID AS id, | |
| dept.deptName AS name, | |
| dept.departmentDesc AS description | |
| FROM | |
| ec_exam_registration_batch eerb | |
| INNER JOIN `groups` g ON | |
| g.id = eerb.groups_id | |
| INNER JOIN department dept ON | |
| dept.deptID = g.properties ->> '$.departmentId' | |
| WHERE 1=1 "; | |
| try { | |
| $departments = $this->executeQueryForList($sql.$whereQuery); | |
| return $departments; | |
| } | |
| catch (\Exception $e) { | |
| throw new ExamControllerException($e->getCode(),$e->getMessage()); | |
| } | |
| } | |
| /** | |
| * get All Batches By Other Details | |
| * @author Krishnajith | |
| */ | |
| public function getAllBatchesByOtherDetails($searchRequest){ | |
| $searchRequest = $this->realEscapeObject($searchRequest); | |
| $whereQuery = ""; | |
| if(!empty($searchRequest->examRegistrationId)) { | |
| $examRegistrationIdString = is_array($searchRequest->examRegistrationId) ? "'" . implode("','",$searchRequest->examRegistrationId) . "'" : "'".$searchRequest->examRegistrationId."'"; | |
| $whereQuery .= " AND eerb.ec_exam_registration_id IN ( $examRegistrationIdString )"; | |
| } | |
| if(!empty($searchRequest->departmentId)) { | |
| $departmentIdString = is_array($searchRequest->departmentId) ? "'" . implode("','",$searchRequest->departmentId) . "'" : "'".$searchRequest->departmentId."'"; | |
| $whereQuery .= " AND dept.deptID IN ( $departmentIdString )"; | |
| } | |
| if(!empty($searchRequest->type)) { | |
| $groupTypeString = is_array($searchRequest->type) ? "'" . implode("','",$searchRequest->type) . "'" : "'".$searchRequest->type."'"; | |
| $whereQuery .= " AND g.type IN ( $groupTypeString )"; | |
| } | |
| $sql = "SELECT DISTINCT | |
| g.id AS id, | |
| g.name AS name | |
| FROM | |
| ec_exam_registration_batch eerb | |
| INNER JOIN `groups` g ON | |
| g.id = eerb.groups_id | |
| INNER JOIN department dept ON | |
| dept.deptID = g.properties ->> '$.departmentId' | |
| WHERE 1=1 "; | |
| try { | |
| $departments = $this->executeQueryForList($sql.$whereQuery); | |
| return $departments; | |
| } | |
| catch (\Exception $e) { | |
| throw new ExamControllerException($e->getCode(),$e->getMessage()); | |
| } | |
| } | |
| /** | |
| * get Assigned Subjects In Batch | |
| * @param $searchRequest | |
| * @return $subjectDetails | |
| * @author Krishnajith | |
| */ | |
| public function getAssignedSubjectsInBatch($searchRequest) { | |
| $searchRequest = $this->realEscapeObject($searchRequest); | |
| $searchRequest->academicPaperSubjectIds = stripslashes($searchRequest->academicPaperSubjectIds); | |
| try{ | |
| $subjectOrderBy = CommonService::getInstance()->getSettings(SettingsConstants::EXAM_CONTROLLER, SettingsConstants::SUBJECT_ORDER_TAKEN_BY); | |
| if($subjectOrderBy == "ORDER"){ | |
| $orderBy = " ORDER BY CAST(aps.properties ->> '$.orderNo' AS UNSIGNED) ASC"; | |
| } | |
| else{ | |
| $orderBy = " ORDER BY CAST(aps.properties ->> '$.priority' AS UNSIGNED) DESC"; | |
| } | |
| $groupBy = " GROUP BY aps.id "; | |
| $whereQuery = ""; | |
| if($searchRequest->isEnableMinorHonorExamRegistration){ | |
| if($searchRequest->syllabusType == SyllabusTypeConstants::MINOR){ | |
| $whereQuery .= " AND cs.type IN ('MINOR')"; | |
| } | |
| elseif($searchRequest->syllabusType == SyllabusTypeConstants::HONOURS){ | |
| $whereQuery .= " AND cs.type IN ('HONOURS')"; | |
| } | |
| else{ | |
| $whereQuery .= " AND cs.type NOT IN ('HONOURS','MINOR')"; | |
| } | |
| } | |
| if(!empty($searchRequest->courseTypeId)) { | |
| $courseTypeIdString = is_array($searchRequest->courseTypeId) ? "'" . implode("','",$searchRequest->courseTypeId) . "'" : "'".$searchRequest->courseTypeId."'"; | |
| $whereQuery .= " AND p.course_type_id IN ( $courseTypeIdString )"; | |
| } | |
| if(!empty($searchRequest->groupId)) { | |
| $groupIdString = is_array($searchRequest->groupId) ? "'" . implode("','",$searchRequest->groupId) . "'" : "'".$searchRequest->groupId."'"; | |
| $whereQuery .= " AND g.id IN ( $groupIdString )"; | |
| } | |
| if(!empty($searchRequest->startYear)) { | |
| $startYearStrng = is_array($searchRequest->startYear) ? "'" . implode("','",$searchRequest->startYear) . "'" : "'".$searchRequest->startYear."'"; | |
| $whereQuery .= " AND g.properties ->> '$.startYear' IN ( $startYearStrng )"; | |
| } | |
| if(!empty($searchRequest->academicTermId)) { | |
| $academicTermIdString = is_array($searchRequest->academicTermId) ? "'" . implode("','",$searchRequest->academicTermId) . "'" : "'".$searchRequest->academicTermId."'"; | |
| $whereQuery .= " AND sg.academic_term_id IN ( $academicTermIdString )"; | |
| } | |
| if(!empty($searchRequest->academicPaperSubjectIds)) { | |
| $academicPaperSubjectIdsString = is_array($searchRequest->academicPaperSubjectIds) ? "'" . implode("','",$searchRequest->academicPaperSubjectIds) . "'" : $searchRequest->academicPaperSubjectIds; | |
| $whereQuery .= " AND aps.id IN ( $academicPaperSubjectIdsString )"; | |
| } | |
| if(!empty($searchRequest->academicPaperSubjectId)) { | |
| $academicPaperSubjectIdString = is_array($searchRequest->academicPaperSubjectId) ? "'" . implode("','",$searchRequest->academicPaperSubjectId) . "'" : "'".$searchRequest->academicPaperSubjectId."'"; | |
| $whereQuery .= " AND aps.id IN ( $academicPaperSubjectIdString )"; | |
| } | |
| $query = "SELECT DISTINCT | |
| aps.id AS id, | |
| aps.id AS academicPaperSubjectId, | |
| sub.id AS subjectId, | |
| sub.code AS subjectCode, | |
| sub.name AS subjectName, | |
| sub.name AS name, | |
| sg.academic_term_id as academicTermId, | |
| g.id AS groupId, | |
| g.name AS groupName, | |
| aps.properties->>'$.externalMaxMark' as externalMaxMark, | |
| aps.properties ->> '$.isInternal' as isInternal, | |
| aps.properties ->> '$.isExternal' as isExternal, | |
| aps.properties ->> '$.internalMaxMark' as internalMaxMark, | |
| aps.properties->>'$.classType' as subjectPropertyType | |
| FROM `groups` g | |
| INNER JOIN groups_relations gr ON | |
| gr.parent_groups_id = g.id | |
| INNER JOIN `program` p ON | |
| p.id = g.properties->>'$.programId' | |
| INNER JOIN `groups` sg ON | |
| sg.id = gr.child_groups_id | |
| INNER JOIN group_members sgm ON | |
| sgm.groups_id = sg.id AND | |
| sgm.academic_status IN ('ACTIVE') | |
| INNER JOIN cluster_groups_relations cgr ON | |
| cgr.groups_id = sg.id | |
| INNER JOIN cluster c ON | |
| c.id = cgr.cluster_id AND c.trashed IS NULL | |
| INNER JOIN cm_academic_paper_subjects aps ON | |
| aps.id = sg.paperSubjectId | |
| INNER JOIN cm_academic_paper ap ON | |
| aps.cm_academic_paper_id = ap.id | |
| INNER JOIN cm_syllabus_academic_term_settings csats ON | |
| csats.id = ap.cm_syllabus_academic_term_settings_id | |
| INNER JOIN v4_ams_subject sub ON | |
| sub.id = aps.ams_subject_id | |
| INNER JOIN cm_syllabus cs ON | |
| cs.id = csats.cm_syllabus_id | |
| WHERE 1=1 AND g.type = 'BATCH' AND sg.type = 'SUBJECT' AND sg.academic_term_id = csats.academic_term_id"; | |
| $subjectDetails = $this->executeQueryForList($query.$whereQuery.$groupBy.$orderBy); | |
| } | |
| catch (\Exception $e){ | |
| throw new ExamControllerException($e->getCode(),$e->getMessage()); | |
| } | |
| return $subjectDetails; | |
| } | |
| /** | |
| * get Sessional Exam Types | |
| * @return examTypes | |
| */ | |
| public function getSessionalExamTypes(){ | |
| try { | |
| $query = "SELECT | |
| typeID as id, | |
| typeName as name | |
| FROM | |
| exam_type | |
| WHERE isInternal = 1"; | |
| $examTypes = $this->executeQueryForList($query); | |
| } | |
| catch (\Exception $e){ | |
| throw new ExamControllerException($e->getCode(),$e->getMessage()); | |
| } | |
| return $examTypes; | |
| } | |
| /** | |
| * get value of a setting (autonomous_feature_settings) | |
| * @author Krishnajith | |
| * @param $name | |
| * @param $type | |
| * @throws ExamControllerException | |
| */ | |
| public function getAutonomousSettingsByNameAndType($name,$type){ | |
| $name=$this->realEscapeString($name); | |
| $type=$this->realEscapeString($type); | |
| $query="select value from autonomous_feature_settings where name='$name' and type='$type'"; | |
| try{ | |
| $settingsValue = $this->executeQueryForObject($query)->value; | |
| }catch(\Exception $e){ | |
| throw new ExamControllerException($e->getCode(),$e->getMessage()); | |
| } | |
| return $settingsValue; | |
| } | |
| /** | |
| * getFinalAcademicTermDetails | |
| * @author Krishnajith | |
| * @param $searchRequest | |
| * @throws ExamControllerException | |
| */ | |
| public function getFinalAcademicTermDetails($searchRequest){ | |
| $searchRequest = $this->realEscapeObject($searchRequest); | |
| $whereQuery = ""; | |
| if(!empty($searchRequest->examRegistrationId)) { | |
| $examRegistrationIdString = is_array($searchRequest->examRegistrationId) ? "'" . implode("','",$searchRequest->examRegistrationId) . "'" : "'".$searchRequest->examRegistrationId."'"; | |
| $whereQuery .= " AND eerb.ec_exam_registration_id IN ( $examRegistrationIdString )"; | |
| } | |
| if(!empty($searchRequest->groupId)) { | |
| $groupIdString = is_array($searchRequest->groupId) ? "'" . implode("','",$searchRequest->groupId) . "'" : "'".$searchRequest->groupId."'"; | |
| $whereQuery .= " AND g.id IN ( $groupIdString )"; | |
| } | |
| $sql = "SELECT DISTINCT | |
| act.id | |
| FROM | |
| ec_exam_registration_batch eerb | |
| INNER JOIN `groups` g ON | |
| g.id = eerb.groups_id | |
| INNER JOIN academic_term act ON | |
| act.id = CAST(g.properties ->> '$.finalTermId'AS CHAR) AND | |
| act.type = 'SEMESTER' | |
| WHERE 1=1 "; | |
| try { | |
| $finalAcademicTermId = $this->executeQueryForObject($sql.$whereQuery)->id; | |
| return $finalAcademicTermId; | |
| } | |
| catch (\Exception $e) { | |
| throw new ExamControllerException($e->getCode(),$e->getMessage()); | |
| } | |
| } | |
| /** | |
| * get All Academic Terms Details | |
| * @author Krishnajith | |
| * @param $searchRequest | |
| * @throws ExamControllerException | |
| */ | |
| public function getAllAcademicTermsDetailsByBatch($searchRequest){ | |
| $searchRequest = $this->realEscapeObject($searchRequest); | |
| $orderBy = " ORDER BY CAST(act1.properties ->> '$.orderNo' AS UNSIGNED) ASC"; | |
| $whereQuery = ""; | |
| if(!empty($searchRequest->groupId)) { | |
| $groupIdString = is_array($searchRequest->groupId) ? "'" . implode("','",$searchRequest->groupId) . "'" : "'".$searchRequest->groupId."'"; | |
| $whereQuery .= " AND g.id IN ( $groupIdString )"; | |
| } | |
| if(!empty($searchRequest->academicTermId)) { | |
| $academicTermIdString = is_array($searchRequest->academicTermId) ? "'" . implode("','",$searchRequest->academicTermId) . "'" : "'".$searchRequest->academicTermId."'"; | |
| $whereQuery .= " AND act1.id IN ( $academicTermIdString )"; | |
| } | |
| $sql = "SELECT DISTINCT | |
| act1.id, | |
| act1.name, | |
| act1.properties->>'$.orderNo' AS OrderNo, | |
| act1.type | |
| FROM | |
| `groups` g | |
| INNER JOIN academic_term act ON | |
| act.id = CAST(g.properties ->> '$.finalTermId'AS CHAR) | |
| INNER JOIN academic_term act1 ON | |
| act1.type = act.type AND CAST(act1.properties ->> '$.orderNo' AS UNSIGNED) <= CAST(act.properties ->> '$.orderNo' AS UNSIGNED) | |
| WHERE 1=1 "; | |
| try { | |
| $academicTerms = $this->executeQueryForList($sql.$whereQuery.$orderBy); | |
| return $academicTerms; | |
| } | |
| catch (\Exception $e) { | |
| throw new ExamControllerException($e->getCode(),$e->getMessage()); | |
| } | |
| } | |
| public function getGradeByPercentage($percentage, $gradeDetails){ | |
| $maxPercentTo = max(array_column($gradeDetails, "percentTo")); | |
| foreach ($gradeDetails as $grade) { | |
| $percentTo = $grade->percentTo; | |
| if ($grade->percentTo != $maxPercentTo) { | |
| $percentTo = floatval($grade->percentTo); | |
| $percentTo = $percentTo . "999999"; | |
| } | |
| if ($grade->percentFrom <= $percentage && $percentTo >= $percentage) { | |
| return $grade; | |
| } | |
| } | |
| return null; | |
| } | |
| public function getDepartmentsByAssignedStaffs($staffIds){ | |
| try { | |
| $query = "SELECT | |
| DISTINCT(deptId) AS deptId | |
| FROM | |
| staffaccounts | |
| WHERE | |
| staffID IN (" . implode(",", $staffIds) . ") "; | |
| $departments = $this->executeQueryForList($query); | |
| } | |
| catch (\Exception $e){ | |
| throw new ExamControllerException($e->getCode(),$e->getMessage()); | |
| } | |
| return $departments; | |
| } | |
| public function getAllDegreeDetails(){ | |
| try { | |
| $query = "SELECT | |
| id, name ,description | |
| FROM | |
| degree"; | |
| $degree = $this->executeQueryForList($query); | |
| } | |
| catch (\Exception $e){ | |
| throw new ExamControllerException($e->getCode(),$e->getMessage()); | |
| } | |
| return $degree; | |
| } | |
| /** | |
| * set Object to DTO | |
| * @param object $from | |
| * @return dtoObject $to | |
| */ | |
| public function setObject($from,$to){ | |
| foreach($to as $val=>$ele){ | |
| $to->{$val} = $from->{$val}; | |
| } | |
| return $to; | |
| } | |
| /** | |
| * Filter the Unique Objects in a PHP object array | |
| * How to use: returnUniqueProperty($names, 'name'); | |
| */ | |
| public static function returnUniqueProperty($array, $property) | |
| { | |
| $tempArray = array_unique(array_column($array, $property)); | |
| $moreUniqueArray = array_values(array_intersect_key($array, $tempArray)); | |
| return array_filter($moreUniqueArray, function($item){return $item->id;}); | |
| } | |
| /** | |
| * Filter the Unique Objects in a PHP object array | |
| * How to use: returnUniqueProperty($names, 'name'); | |
| */ | |
| public static function returnUniqueArrayProperty($array, $property) { | |
| $inputArray = []; | |
| foreach($array as $singleArray){ | |
| foreach($singleArray as $arrayObj){ | |
| $inputArray[] = $arrayObj; | |
| } | |
| } | |
| // array_walk($array, function($singleArray,$key) use($inputArray){ | |
| // $inputArray[] = $singleArray; | |
| // }); | |
| $tempArray = array_unique(array_column($inputArray, $property)); | |
| $moreUniqueArray = array_values(array_intersect_key($inputArray, $tempArray)); | |
| return array_filter($moreUniqueArray, function($item){return $item->id;}); | |
| } | |
| public function getCustumYears($upperLimit=5,$lowerLimit=5){ | |
| $years = []; | |
| $year = date("Y"); | |
| for($x=$year-$lowerLimit;$x < $year;$x++){ | |
| $currentYear = new \stdClass; | |
| $currentYear->id = $x; | |
| $currentYear->name = $x; | |
| $years[] = $currentYear; | |
| } | |
| for($x=$year;$x <= $year+$upperLimit;$x++){ | |
| $currentYear = new \stdClass; | |
| $currentYear->id = $x; | |
| $currentYear->name = $x; | |
| $years[] = $currentYear; | |
| } | |
| return $years; | |
| } | |
| /** | |
| * Search Subject Types | |
| * @return $subjectTypes | |
| */ | |
| public function searchSubjectTypes(){ | |
| $query = "select `id`,`name`, `name` as `text`, `descriptions`, `code` from cm_subject_types;"; | |
| try { | |
| $subjectTypes = $this->executeQueryForList($query); | |
| } catch (\Exception $e) { | |
| throw new ExamControllerException(ExamControllerException::ERROR_FETCHING_SUBJECT_TYPES,"Cannot fetch subject types! Please try again"); | |
| } | |
| return $subjectTypes; | |
| } | |
| /** | |
| * Search Subject Slots | |
| * @return $subjectSlots | |
| */ | |
| public function searchSubjectSlots(){ | |
| $query = "SELECT DISTINCT | |
| c.id, | |
| c.name, | |
| c.name AS text | |
| FROM cm_common_list_object c | |
| WHERE c.type = 'SLOT'"; | |
| try { | |
| $subjectSlots = $this->executeQueryForList($query); | |
| } catch (\Exception $e) { | |
| throw new ExamControllerException(ExamControllerException::ERROR_FETCHING_SUBJECT_TYPES,"Cannot fetch subject types! Please try again"); | |
| } | |
| return $subjectSlots; | |
| } | |
| /** | |
| * Search Subject Types | |
| * @return $subjectTypes | |
| */ | |
| public function searchSubjectCategories(){ | |
| $query = "SELECT subjectcatID AS id, subjectcatName AS `name`, subjectcatName AS `text` from subject_category sc where canShow = 0;"; | |
| try { | |
| $subjectCategories = $this->executeQueryForList($query); | |
| } catch (\Exception $e) { | |
| throw new ExamControllerException(ExamControllerException::ERROR_FETCHING_SUBJECT_TYPES,"Cannot fetch subject types! Please try again"); | |
| } | |
| return $subjectCategories; | |
| } | |
| /** | |
| * get student active completed academic terms | |
| * @param $searchRequest | |
| * @return $academicTerms | |
| * @author Krishnajith | |
| */ | |
| public function getStudentAcademicTerms($searchRequest) { | |
| $searchRequest = $this->realEscapeObject($searchRequest); | |
| try{ | |
| $orderBy = " ORDER BY CAST(act.properties ->> '$.orderNo' AS UNSIGNED) ASC"; | |
| $whereQuery = ""; | |
| if(!empty($searchRequest->studentId)) { | |
| $studentIdString = is_array($searchRequest->studentId) ? "'" . implode("','",$searchRequest->studentId) . "'" : "'".$searchRequest->studentId."'"; | |
| $whereQuery .= " AND spa.student_id IN ( $studentIdString )"; | |
| } | |
| $query = "SELECT DISTINCT | |
| act.id, | |
| act.name | |
| FROM student_program_account spa | |
| INNER JOIN student_program_batch_log spbl ON | |
| spbl.program_student_id = spa.id AND spbl.properties->>'$.academicStatus' IN ('ACTIVE','COMPLETED') | |
| INNER JOIN academic_term act ON | |
| act.id = spbl.term_id | |
| WHERE spa.properties->>'$.academicStatus' IN ('ACTIVE','COMPLETED') "; | |
| $academicTerms = $this->executeQueryForList($query.$whereQuery.$orderBy); | |
| } | |
| catch (\Exception $e){ | |
| throw new ExamControllerException($e->getCode(),$e->getMessage()); | |
| } | |
| return $academicTerms; | |
| } | |
| /** | |
| * check is failed by pass percentage criteria | |
| * @param $checkPassPercentCriteria | |
| * @return $response | |
| * @author Krishnajith | |
| */ | |
| public function checkIsFailedByPassPercentCriteria ( $checkPassPercentCriteria ) { | |
| $studentExamTotal = 0; | |
| $examTotal = 0; | |
| $isFailed = 0; | |
| $response = new \stdClass(); | |
| $internalPassCriteria = $externalPassCriteria = $overallPassCriteria = null; | |
| if ($checkPassPercentCriteria->passPercentConfig->internalPassCriteria) { | |
| $internalPassCriteria = (float) $checkPassPercentCriteria->passPercentConfig->internalPassCriteria; | |
| } | |
| if ($checkPassPercentCriteria->passPercentConfig->externalPassCriteria) { | |
| $externalPassCriteria = (float) $checkPassPercentCriteria->passPercentConfig->externalPassCriteria; | |
| } | |
| if ($checkPassPercentCriteria->passPercentConfig->overallPassCriteria) { | |
| $overallPassCriteria = (float) $checkPassPercentCriteria->passPercentConfig->overallPassCriteria; | |
| } | |
| if ($checkPassPercentCriteria->isInternal) { | |
| $studentExamTotal = round($checkPassPercentCriteria->internalMark); | |
| $examTotal = $checkPassPercentCriteria->internalMaxMark; | |
| if (!empty ($internalPassCriteria)) { | |
| $internalPercent = $checkPassPercentCriteria->internalMaxMark ? (100 * $checkPassPercentCriteria->internalMark / $checkPassPercentCriteria->internalMaxMark) : 0; | |
| $response->internalPassMark = $checkPassPercentCriteria->internalMaxMark * ($internalPassCriteria / 100); | |
| $response->internalMarkNeededToPass = $response->internalPassMark > $checkPassPercentCriteria->internalMark ? $response->internalPassMark - $checkPassPercentCriteria->internalMark : 0; | |
| $response->internalPercentageNeededToPass = $internalPassCriteria > $internalPercent ? $internalPassCriteria - $internalPercent : 0; | |
| if ( $checkPassPercentCriteria->considerOverallPassCriteriaOnly == 0 ) { | |
| $response->internalFailedStatus = $internalPassCriteria <= $internalPercent ? 'PASSED' : 'FAILED'; | |
| $isFailed = $internalPassCriteria <= $internalPercent ? $isFailed : 1; | |
| } | |
| } | |
| } | |
| if ($checkPassPercentCriteria->isExternal) { | |
| $studentExamTotal += round($checkPassPercentCriteria->externalMark); | |
| $examTotal += $checkPassPercentCriteria->externalMaxMark; | |
| if (!empty ($externalPassCriteria)) { | |
| $externalPercent = $checkPassPercentCriteria->externalMaxMark ? (100 * | |
| $checkPassPercentCriteria->externalMark / $checkPassPercentCriteria->externalMaxMark) : 0; | |
| $response->externalPassMark = $checkPassPercentCriteria->externalMaxMark * ($externalPassCriteria / 100); | |
| $response->externalMarkNeededToPass = $response->externalPassMark > $checkPassPercentCriteria->externalMark ? $response->externalPassMark - $checkPassPercentCriteria->externalMark : 0; | |
| $response->externalPercentageNeededToPass = $externalPassCriteria > $externalPercent ? $externalPassCriteria - $externalPercent : 0; | |
| if ( $checkPassPercentCriteria->considerOverallPassCriteriaOnly == 0 ) { | |
| $response->externalFailedStatus = $externalPassCriteria <= $externalPercent ? 'PASSED' : 'FAILED'; | |
| $isFailed = $externalPassCriteria <= $externalPercent ? $isFailed : 1; | |
| } | |
| } | |
| } | |
| if (!empty ($overallPassCriteria)) { | |
| $overallPercent = $examTotal ? 100 * $studentExamTotal / $examTotal : 0; | |
| $response->overAllPassMark = $examTotal * ($overallPassCriteria / 100); | |
| $response->overAllMarkNeededToPass = $response->overAllPassMark > $studentExamTotal ? $response->overAllPassMark - $studentExamTotal : 0; | |
| $response->overAllPercentageNeededToPass = $overallPassCriteria > $overallPercent ? $overallPassCriteria - $overallPercent : 0; | |
| $isFailed = $overallPassCriteria <= $overallPercent ? $isFailed : 1; | |
| $response->overAllFailedStatus = $overallPassCriteria <= $overallPercent ? 'PASSED' : 'FAILED'; | |
| } | |
| $response->failedStatus = $isFailed ? 'FAILED' : 'PASSED'; | |
| return $response; | |
| } | |
| /** | |
| * method for addOrdinalNumberSuffix | |
| * @param string $num | |
| * @return string | |
| * @author Krishnajith V | |
| */ | |
| public static function addOrdinalNumberSuffix($num, $withNo = true){ | |
| $sufix = 'th'; | |
| if (!in_array(($num % 100), array(11, 12, 13))) { | |
| switch ($num % 10) { | |
| // Handle 1st, 2nd, 3rd | |
| case 1 : | |
| $sufix = 'st'; | |
| break; | |
| case 2 : | |
| $sufix = 'nd'; | |
| break; | |
| case 3 : | |
| $sufix = 'rd'; | |
| break; | |
| } | |
| } | |
| if ($withNo == true) { | |
| return $num . $sufix; | |
| } else { | |
| return $sufix; | |
| } | |
| } | |
| /** | |
| * method for Get Student Mark List Serial Number | |
| * @param Object $request | |
| * @return String $slNoExist | |
| * @author Krishnajith V | |
| */ | |
| public function getStudentExamMarkListSerialNumber($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $slNoExist = null; | |
| if ($request->examRegistrationId && $request->studentId) { | |
| $slNoExistSql = "SELECT properties ->> '$.markListSerialNo' as markListSerialNo FROM ec_student_exam_registration_details WHERE ec_exam_registration_id = '$request->examRegistrationId' AND student_id = '$request->studentId'"; | |
| $maxSlNoSql = "SELECT max(CAST(properties ->> '$.markListSerialNo' AS UNSIGNED)) as maxMarkListSerialNo FROM ec_student_exam_registration_details"; | |
| try { | |
| $slNoExist = $this->executeQueryForObject($slNoExistSql); | |
| if( $request->checkExistance == true ){ | |
| return $slNoExist; | |
| } | |
| if($slNoExist->markListSerialNo && $slNoExist->markListSerialNo != 'null'){ | |
| return $slNoExist; | |
| }else{ | |
| $slNo = (int)$this->executeQueryForObject($maxSlNoSql)->maxMarkListSerialNo + 1; | |
| $sql = "UPDATE ec_student_exam_registration_details SET properties = JSON_SET(properties, '$.markListSerialNo','$slNo') WHERE ec_exam_registration_id = '$request->examRegistrationId' AND student_id = '$request->studentId'"; | |
| $this->executeQueryForObject($sql); | |
| $slNoExist = $this->executeQueryForObject($slNoExistSql); | |
| } | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| return $slNoExist; | |
| } | |
| public function getStudentDetailsByStudentId($studentId) | |
| { | |
| try { | |
| $studentId = $this->realEscapeString($studentId); | |
| $sql = "SELECT | |
| spa.id, st.studentId,st.studentAccount AS `userName`,st.studentName AS name,st.studentEmail AS `email`,st.studentGender AS `genderId`,st.studentAddress, st.studentPhone AS phone, spa.student_id, JSON_OBJECT('id',g.id,'name',g.name,'termId',bat.id,'termName',bat.name,'finalTermId',bfat.id,'finalTermName',bfat.name,'programId',bp.id,'programName',bp.name, 'startYear', g.properties->>'$.startYear') AS `batch_detail`, spa.properties, sat.id AS `student_term_id`, sat.name AS `student_term_name`,spa.properties->>'$.registerNumber' AS regNo | |
| FROM `student_program_account` spa | |
| INNER JOIN `studentaccount` st | |
| ON st.studentID = spa.student_id | |
| INNER JOIN `groups` g | |
| ON g.id = spa.current_batch_id | |
| INNER JOIN `academic_term` sat | |
| ON sat.id = spa.current_term_id | |
| INNER JOIN `academic_term` bat | |
| ON bat.id = g.properties->>'$.currentTermId' | |
| INNER JOIN `program` bp | |
| ON bp.id = g.properties->>'$.programId' | |
| LEFT JOIN `academic_term` bfat | |
| ON bfat.id = g.properties->>'$.finalTermId' | |
| WHERE spa.student_id = '$studentId';"; | |
| $studentDetails = $this->executeQueryForList($sql, $this->mapper2[CommonExamMapper::ALL_STUDENT_LIST])[0]; | |
| } catch (\Exception $e) { | |
| throw new ExamControllerException($e->getCode(), "Failed to fetch student! Please try again"); | |
| } | |
| return $studentDetails; | |
| } | |
| /** | |
| * get Academic Term Details Upto FinalTerm | |
| * @param Object $searchRequest | |
| * @return $academicTerms | |
| * @author Krishnajith V | |
| */ | |
| public function getAcademicTermDetailsUptoFinalTerm($searchRequest){ | |
| $searchRequest = $this->realEscapeObject($searchRequest); | |
| $orderBy = " ORDER BY CAST(act.properties ->> '$.orderNo' AS UNSIGNED) ASC"; | |
| $whereQuery = ""; | |
| if(!empty($searchRequest->examRegistrationId)) { | |
| $examRegistrationIdString = is_array($searchRequest->examRegistrationId) ? "'" . implode("','",$searchRequest->examRegistrationId) . "'" : "'".$searchRequest->examRegistrationId."'"; | |
| $whereQuery .= " AND eerb.ec_exam_registration_id IN ( $examRegistrationIdString )"; | |
| } | |
| if(!empty($searchRequest->groupId)) { | |
| $groupIdString = is_array($searchRequest->groupId) ? "'" . implode("','",$searchRequest->groupId) . "'" : "'".$searchRequest->groupId."'"; | |
| $whereQuery .= " AND g.id IN ( $groupIdString )"; | |
| } | |
| $sql = "SELECT DISTINCT | |
| act.id, | |
| act.name, | |
| act.properties->>'$.orderNo' AS OrderNo, | |
| act.type | |
| FROM | |
| ec_exam_registration_batch eerb | |
| INNER JOIN `groups` g ON | |
| g.id = eerb.groups_id | |
| INNER JOIN academic_term actf ON | |
| actf.id = CAST(g.properties ->> '$.finalTermId'AS CHAR) | |
| INNER JOIN academic_term act ON | |
| CAST(actf.properties ->> '$.orderNo' AS UNSIGNED) >= CAST(act.properties ->> '$.orderNo' AS UNSIGNED) AND | |
| act.type = actf.type | |
| WHERE 1=1 "; | |
| try { | |
| $academicTerms = $this->executeQueryForList($sql.$whereQuery.$orderBy); | |
| return $academicTerms; | |
| } | |
| catch (\Exception $e) { | |
| throw new ExamControllerException($e->getCode(),$e->getMessage()); | |
| } | |
| } | |
| /** | |
| * get Students Details By Batch Program | |
| * @param $searchRequest | |
| * @return $students | |
| * @author Krishnajith | |
| */ | |
| public function getStudentsDetailsByBatchProgram($searchRequest) { | |
| $searchRequest = $this->realEscapeObject($searchRequest); | |
| try{ | |
| $orderBy = " ORDER BY spa.properties->>'$.registerNumber' "; | |
| $whereQuery = ""; | |
| if(!empty($searchRequest->groupId)) { | |
| $groupIdString = is_array($searchRequest->groupId) ? "'" . implode("','",$searchRequest->groupId) . "'" : "'".$searchRequest->groupId."'"; | |
| $whereQuery .= " AND g.id IN ( $groupIdString )"; | |
| } | |
| if(!empty($searchRequest->studentId)) { | |
| $studentIdString = is_array($searchRequest->studentId) ? "'" . implode("','",$searchRequest->studentId) . "'" : "'".$searchRequest->studentId."'"; | |
| $whereQuery .= " AND s.studentID IN ( $studentIdString )"; | |
| } | |
| if(!empty($searchRequest->programId)) { | |
| $programIdString = is_array($searchRequest->programId) ? "'" . implode("','",$searchRequest->programId) . "'" : "'".$searchRequest->programId."'"; | |
| $whereQuery .= " AND p.id IN ( $programIdString )"; | |
| } | |
| $query = "SELECT DISTINCT | |
| s.studentID as id, | |
| g.id AS groupId, | |
| g.name AS groupName, | |
| deg.name AS degreeName, | |
| p.name as programName, | |
| g.properties ->>'$.programId' AS programId, | |
| ct.courseTypeID as courseTypeId, | |
| ct.typeName as courseTypeName, | |
| spa.student_id AS studentId, | |
| spa.properties->>'$.rollNumber' AS studentRollNo, | |
| IF(IFNULL(spa.properties ->> '$.registerNumber', 'null') = 'null','',spa.properties ->> '$.registerNumber') AS studentRegisterNo, | |
| s.studentName AS studentName, | |
| s.studentPhone, | |
| s.studentEmail, | |
| s.studentBirthday as dob, | |
| st.state_name AS state, | |
| sae.parentPincode, | |
| IF(s.studentGender = 'male','M','F') AS sex, | |
| CONCAT_WS(' ' , re.religionName, sca.casteName) AS religionCasteCombination , | |
| d.districtName AS district, | |
| s.studentAddress, | |
| rs.reservationCode, | |
| rs.reservationName | |
| FROM | |
| studentaccount s | |
| INNER JOIN student_program_account spa | |
| ON spa.student_id = s.studentID | |
| INNER JOIN student_program_batch_log spbl | |
| ON spbl.program_student_id = spa.id AND | |
| spbl.properties->>'$.academicStatus' IN ('ACTIVE','COMPLETED') | |
| INNER JOIN `program` p | |
| ON p.id = spbl.program_id | |
| INNER JOIN `groups` g | |
| ON g.id = spbl.batch_group_id | |
| INNER JOIN degree deg ON | |
| deg.id = p.degree_id | |
| INNER JOIN `course_type` ct ON | |
| ct.courseTypeID = p.course_type_id | |
| INNER JOIN group_members gm ON | |
| gm.groups_id = g.id AND | |
| gm.members->>'$.studentId' = spa.id | |
| LEFT JOIN student_caste sca ON sca.casteID = s.casteID | |
| LEFT JOIN state st ON st.id = s.state_id | |
| LEFT JOIN studentaccount_extras sae ON sae.studentID = s.studentID | |
| LEFT JOIN religion re ON re.religionID = s.religion | |
| LEFT JOIN districts d ON d.id = s.district_id | |
| LEFT JOIN reservation_students rs ON rs.reservationID = s.reservationID | |
| WHERE 1=1 AND spa.academic_status IN ('ACTIVE', 'COMPLETED')"; | |
| $students = $this->executeQueryForList($query.$whereQuery.$orderBy); | |
| } | |
| catch (\Exception $e){ | |
| throw new ExamControllerException($e->getCode(),$e->getMessage()); | |
| } | |
| return $students; | |
| } | |
| /** | |
| * get current batch group details by groupId | |
| * @author Krishnajith | |
| * @param $searchRequest | |
| * @throws ExamControllerException | |
| */ | |
| public function getBatchGroupDetailsByGroupId($searchRequest){ | |
| $searchRequest = $this->realEscapeObject($searchRequest); | |
| $orderBy = ""; | |
| $whereQuery = ""; | |
| if(empty($searchRequest->groupId)) { | |
| throw new ExamControllerException(ExamControllerException::EMPTY_PARAMETERS, "Empty parameter!"); | |
| } | |
| if(!empty($searchRequest->groupId)) { | |
| $groupIdString = is_array($searchRequest->groupId) ? "'" . implode("','",$searchRequest->groupId) . "'" : "'".$searchRequest->groupId."'"; | |
| $whereQuery .= " AND g.id IN ( $groupIdString )"; | |
| } | |
| $sql = "SELECT DISTINCT | |
| g.id AS groupId, | |
| g.name AS groupName, | |
| g.properties ->> '$.startYear' AS startYear, | |
| g.properties->>'$.endYear' AS endYear, | |
| ct.typeName AS courseTypeName, | |
| ct.course_Type AS courseType, | |
| d.deptID AS departmentId, | |
| d.deptName AS departmentName, | |
| d.departmentDesc AS departmentDesc, | |
| deg.id AS degreeId, | |
| deg.name as degreeName, | |
| p.stream_id as streamId, | |
| group_concat(str.name SEPARATOR ' and ') as streamName, | |
| GROUP_CONCAT(str.properties->>'$.abbreviation') AS streamDesc | |
| FROM | |
| `groups` g | |
| INNER JOIN program p ON | |
| p.id = g.properties->>'$.programId' | |
| INNER JOIN department d ON | |
| d.deptID = g.properties->>'$.departmentId' | |
| INNER JOIN degree deg ON | |
| deg.id = p.degree_id | |
| INNER JOIN `course_type` ct ON | |
| ct.courseTypeID = p.course_type_id | |
| LEFT JOIN stream str ON | |
| JSON_SEARCH( p.stream_id, 'one', str.id) IS NOT NULL | |
| WHERE 1=1 "; | |
| try { | |
| $groups = $this->executeQueryForList($sql.$whereQuery.$orderBy); | |
| return $groups; | |
| } | |
| catch (\Exception $e) { | |
| throw new ExamControllerException($e->getCode(),$e->getMessage()); | |
| } | |
| } | |
| protected function getGradeByCreditPercentage($percentage, $gradeDetails) | |
| { | |
| foreach ($gradeDetails as $grade) { | |
| if ($grade->rangeFrom <= $percentage && $grade->rangeTo >= $percentage) { | |
| return $grade; | |
| } | |
| } | |
| return null; | |
| } | |
| protected function getFailedGrade($isFailed, $gradeDetails){ | |
| foreach ($gradeDetails as $grade) { | |
| if ($isFailed) { | |
| if($grade->failStatus == '1'){ | |
| return $grade; | |
| } | |
| } | |
| } | |
| return null; | |
| } | |
| /** | |
| * check is failed by pass percentage criteria | |
| * @param $checkPassPercentCriteria | |
| * @return $response | |
| * @author Krishnajith | |
| */ | |
| public function checkIsFailedByPassPercentCreditCriteria ( $checkPassPercentCriteria ) { | |
| $studentExamTotal = 0; | |
| $examTotal = 0; | |
| $isFailed = 0; | |
| $response = new \stdClass(); | |
| $x = 1; | |
| $internalPassCriteria = $externalPassCriteria = $overallPassCriteria = null; | |
| if ($checkPassPercentCriteria->passPercentConfig->internalPassCriteria) { | |
| $internalPassCriteria = (float) $checkPassPercentCriteria->passPercentConfig->internalPassCriteria; | |
| } | |
| if ($checkPassPercentCriteria->passPercentConfig->externalPassCriteria) { | |
| $externalPassCriteria = (float) $checkPassPercentCriteria->passPercentConfig->externalPassCriteria; | |
| } | |
| if ($checkPassPercentCriteria->passPercentConfig->overallPassCriteria) { | |
| $overallPassCriteria = (float) $checkPassPercentCriteria->passPercentConfig->overallPassCriteria; | |
| } | |
| if($checkPassPercentCriteria->courseTypeMethod == 'PG' && $checkPassPercentCriteria->schemeType == 'CREDIT'){ | |
| if ($checkPassPercentCriteria->isInternal) { | |
| $internalMarkPer = round($checkPassPercentCriteria->maxGradePercent * $checkPassPercentCriteria->internalMark / $checkPassPercentCriteria->internalMaxMark, 2); | |
| } | |
| if ($checkPassPercentCriteria->isExternal) { | |
| $externalMarkPer = round($checkPassPercentCriteria->maxGradePercent * $checkPassPercentCriteria->externalMark / $checkPassPercentCriteria->externalMaxMark, 2); | |
| } | |
| if ($checkPassPercentCriteria->isInternal && $checkPassPercentCriteria->isExternal) { | |
| $gradePoint = round((($x * $internalMarkPer) + ( ($checkPassPercentCriteria->maxGradePercent-$x) * $externalMarkPer ) ) / $checkPassPercentCriteria->maxGradePercent, 2); | |
| } | |
| else { | |
| $gradePoint = $internalMarkPer + $externalMarkPer; | |
| } | |
| $externalPercentPG = 100 * $externalMarkPer / $checkPassPercentCriteria->maxGradePercent ?? 0; | |
| $overallPercentPG = 100 * $gradePoint / $checkPassPercentCriteria->maxGradePercent ?? 0; | |
| if (!empty ($externalPassCriteria)) { | |
| $response->externalFailedStatus = $externalPassCriteria <= $externalPercentPG ? 'PASSED' : 'FAILED'; | |
| $response->externalPassMark = $checkPassPercentCriteria->externalMaxMark * ($externalPassCriteria / 100); | |
| $response->externalMarkNeededToPass = $response->externalPassMark > $checkPassPercentCriteria->externalMark ? $response->externalPassMark - $checkPassPercentCriteria->externalMark : 0; | |
| $isFailed = $externalPassCriteria <= $externalPercentPG ? $isFailed : 1; | |
| } | |
| if (!empty ($overallPassCriteria)) { | |
| $response->overAllFailedStatus = $overallPassCriteria <= $overallPercentPG ? 'PASSED' : 'FAILED'; | |
| $passGradePoint = $checkPassPercentCriteria->maxGradePercent * ( $overallPassCriteria / 100); | |
| $response->overAllPassExternalPer = (($passGradePoint * $checkPassPercentCriteria->maxGradePercent ) - ($x * $internalMarkPer)) / ($checkPassPercentCriteria->maxGradePercent-$x); | |
| $response->overAllPassMark = round(($response->overAllPassExternalPer * $checkPassPercentCriteria->externalMaxMark) / $checkPassPercentCriteria->maxGradePercent ,2); | |
| $response->overAllMarkNeededToPass = $response->overAllPassMark > $checkPassPercentCriteria->externalMark ? $response->overAllPassMark - $checkPassPercentCriteria->externalMark : 0; | |
| $isFailed = $overallPassCriteria <= $overallPercentPG ? $isFailed : 1; | |
| } | |
| if($isFailed){ | |
| $grade = $this->getFailedGrade($isFailed, $checkPassPercentCriteria->gradeDetails); | |
| $grade->gradePoint = $gradePoint ?? 0; | |
| $grade->totalExternalMark = $gradePoint ?? 0; | |
| $isFailed = $grade->failStatus ? 1 : $isFailed; | |
| } | |
| else{ | |
| $grade = $this->getGradeByCreditPercentage($gradePoint, $checkPassPercentCriteria->gradeDetails); | |
| $grade->gradePoint = $gradePoint ?? 0; | |
| $grade->totalExternalMark = $gradePoint ?? 0; | |
| $isFailed = $grade->failStatus ? 1 : $isFailed; | |
| } | |
| $response->grade = $grade; | |
| } | |
| else{ | |
| if ($checkPassPercentCriteria->isInternal) { | |
| $studentExamTotal = round($checkPassPercentCriteria->internalMark); | |
| $examTotal = $checkPassPercentCriteria->internalMaxMark; | |
| if (!empty ($internalPassCriteria)) { | |
| $internalPercent = $checkPassPercentCriteria->internalMaxMark ? (100 * $checkPassPercentCriteria->internalMark / $checkPassPercentCriteria->internalMaxMark) : 0; | |
| $response->internalPassMark = $checkPassPercentCriteria->internalMaxMark * ($internalPassCriteria / 100); | |
| $response->internalMarkNeededToPass = $response->internalPassMark > $checkPassPercentCriteria->internalMark ? $response->internalPassMark - $checkPassPercentCriteria->internalMark : 0; | |
| $response->internalPercentageNeededToPass = $internalPassCriteria > $internalPercent ? $internalPassCriteria - $internalPercent : 0; | |
| if ( $checkPassPercentCriteria->considerOverallPassCriteriaOnly == 0 ) { | |
| $response->internalFailedStatus = $internalPassCriteria <= $internalPercent ? 'PASSED' : 'FAILED'; | |
| $isFailed = $internalPassCriteria <= $internalPercent ? $isFailed : 1; | |
| } | |
| } | |
| } | |
| if ($checkPassPercentCriteria->isExternal) { | |
| $studentExamTotal += round($checkPassPercentCriteria->externalMark); | |
| $examTotal += $checkPassPercentCriteria->externalMaxMark; | |
| if (!empty ($externalPassCriteria)) { | |
| $externalPercent = $checkPassPercentCriteria->externalMaxMark ? (100 * | |
| $checkPassPercentCriteria->externalMark / $checkPassPercentCriteria->externalMaxMark) : 0; | |
| $response->externalPassMark = $checkPassPercentCriteria->externalMaxMark * ($externalPassCriteria / 100); | |
| $response->externalMarkNeededToPass = $response->externalPassMark > $checkPassPercentCriteria->externalMark ? $response->externalPassMark - $checkPassPercentCriteria->externalMark : 0; | |
| $response->externalPercentageNeededToPass = $externalPassCriteria > $externalPercent ? $externalPassCriteria - $externalPercent : 0; | |
| if ( $checkPassPercentCriteria->considerOverallPassCriteriaOnly == 0 ) { | |
| $response->externalFailedStatus = $externalPassCriteria <= $externalPercent ? 'PASSED' : 'FAILED'; | |
| $isFailed = $externalPassCriteria <= $externalPercent ? $isFailed : 1; | |
| } | |
| } | |
| } | |
| $overallPercent = $examTotal ? 100 * $studentExamTotal / $examTotal : 0; | |
| if (!empty ($overallPassCriteria)) { | |
| $response->overAllPassMark = $examTotal * ($overallPassCriteria / 100); | |
| $response->overAllMarkNeededToPass = $response->overAllPassMark > $studentExamTotal ? $response->overAllPassMark - $studentExamTotal : 0; | |
| $response->overAllPercentageNeededToPass = $overallPassCriteria > $overallPercent ? $overallPassCriteria - $overallPercent : 0; | |
| $isFailed = $overallPassCriteria <= $overallPercent ? $isFailed : 1; | |
| $response->overAllFailedStatus = $overallPassCriteria <= $overallPercent ? 'PASSED' : 'FAILED'; | |
| } | |
| $overallPercent = $isFailed ? 0 : $overallPercent; | |
| $grade = $this->getGradeByCreditPercentage($overallPercent, $checkPassPercentCriteria->gradeDetails); | |
| $grade->gradePoint = $gradePoint ?? 0; | |
| $grade->totalExternalMark = $studentExamTotal ?? 0; | |
| $response->grade = $grade; | |
| } | |
| $response->failedStatus = $isFailed ? 'FAILED' : 'PASSED'; | |
| return $response; | |
| } | |
| /** | |
| * check subject is Elective | |
| */ | |
| public function checkIfSujectIsElective($groupId, $academicPaperSubjectId) { | |
| $groupId = $this->realEscapeString($groupId); | |
| $academicPaperSubjectId = $this->realEscapeString($academicPaperSubjectId); | |
| $sql = "SELECT DISTINCT | |
| esgpsm.id AS id | |
| FROM | |
| ec_subject_group_paper_subject_mapping esgpsm | |
| INNER JOIN `subjectGroups` sg ON | |
| sg.id = esgpsm.subject_groups_id | |
| WHERE sg.code = 'ELECTIVE' AND esgpsm.groups_id = '$groupId' AND esgpsm.cm_academic_paper_subjects_id = '$academicPaperSubjectId'"; | |
| try{ | |
| $mappingTableId = $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ExamControllerException($e->getCode(), "Failed to fetch student! Please try again"); | |
| } | |
| return $mappingTableId->id ? 1 : 0; | |
| } | |
| /** | |
| * @param $request | |
| * @throws ExamControllerException | |
| */ | |
| public function getStudentAdditionalCredits($request){ | |
| $request = $this->realEscapeObject($request); | |
| $whereQuery = ""; | |
| if ($request->groupId) { | |
| $whereQuery .= " AND groups_id = '$request->groupId' "; | |
| } | |
| if ($request->academicTermId) { | |
| $whereQuery .= " AND academic_term_id = '$request->academicTermId' "; | |
| } | |
| if ($request->creditType) { | |
| $whereQuery .= " AND properties->'$.creditType' = '$request->creditType'"; | |
| } | |
| if ($request->studentId) { | |
| $whereQuery .= " AND student_id = '$request->studentId'"; | |
| } | |
| $query = "SELECT DISTINCT | |
| id, | |
| academic_term_id as termId, | |
| additional_credit as credit | |
| FROM | |
| student_additional_credit esgpsm | |
| WHERE 1=1 "; | |
| try{ | |
| if( $request->requestForList ){ | |
| $creditTrasfer = $this->executeQueryForList($query.$whereQuery); | |
| } | |
| else{ | |
| $creditTrasfer = $this->executeQueryForObject($query.$whereQuery); | |
| } | |
| } catch (\Exception $e) { | |
| throw new ExamControllerException($e->getCode(), "Failed to fetch student! Please try again"); | |
| } | |
| return $creditTrasfer; | |
| } | |
| public function getCustomFieldObjectList($customFields){ | |
| $fieldList = []; | |
| $response = new \stdClass(); | |
| $studentData = new \stdClass(); | |
| $orderNo = 1; | |
| foreach ($customFields as $customField) { | |
| $field = json_decode('{"columnName":"","displayName":"","fieldType":"","format":"","orderNo":0,"validation":"text","editable":true,"required":false,"relatedFieldList":[],"relatedFieldSeperator":"","foreignkeyTableDetails":{},"tableType":"CUSTOM"}'); | |
| $field->columnName = $customField->code; | |
| $field->displayName = $customField->label; | |
| $field->fieldType = getDataType($customField->dataType); | |
| $field->orderNo = $orderNo++; | |
| $studentData->{$customField->code} = $customField->value; | |
| $fieldList[] = $field; | |
| } | |
| $response->fieldList = $fieldList; | |
| $response->studentData = $studentData; | |
| return $response; | |
| } | |
| /** | |
| * get Academic Term Details up to required academic term | |
| * @param String $academicTermId | |
| * @return $academicTerms | |
| * @author Krishnajith V | |
| */ | |
| public function getAcademicTermDetailsUptoCurrentAcademicTermId($academicTermId){ | |
| $academicTermId = $this->realEscapeString($academicTermId); | |
| $orderBy = " ORDER BY CAST(act.properties ->> '$.orderNo' AS UNSIGNED) ASC"; | |
| $whereQuery = ""; | |
| $sql = "SELECT DISTINCT | |
| act.id as id, | |
| act.name as name, | |
| act.properties->>'$.orderNo' AS OrderNo, | |
| act.type as type | |
| FROM | |
| academic_term actf | |
| INNER JOIN academic_term act ON | |
| CAST(actf.properties ->> '$.orderNo' AS UNSIGNED) >= CAST(act.properties ->> '$.orderNo' AS UNSIGNED) AND | |
| act.type = actf.type | |
| WHERE 1=1 AND actf.id = '$academicTermId' "; | |
| try { | |
| $academicTerms = $this->executeQueryForList($sql.$orderBy); | |
| return $academicTerms; | |
| } | |
| catch (\Exception $e) { | |
| throw new ExamControllerException($e->getCode(),$e->getMessage()); | |
| } | |
| } | |
| /** | |
| * method for Get Student Batch unique number | |
| * @param Object $request | |
| * @return String $slNoExist | |
| * @author Krishnajith V | |
| */ | |
| public function getStudentBatchUniqueNumber($request){ | |
| $userId = $GLOBALS['userId']; | |
| $request = $this->realEscapeObject($request); | |
| $slNoExist = null; | |
| if ($request->groupId && $request->studentId && $request->type) { | |
| $slNoExistSql = "SELECT unique_no as uniqueNo FROM ec_student_group_unique_number WHERE groups_id = '$request->groupId' AND student_id = '$request->studentId' AND `type` = '$request->type' "; | |
| $maxSlNoSql = "SELECT max(CAST(unique_no AS UNSIGNED)) as maxUniqueNo FROM ec_student_group_unique_number WHERE `type` = '$request->type'"; | |
| try { | |
| $slNoExist = $this->executeQueryForObject($slNoExistSql); | |
| if($slNoExist->uniqueNo){ | |
| return $slNoExist; | |
| }else{ | |
| $slNo = (int)$this->executeQueryForObject($maxSlNoSql)->maxUniqueNo + 1; | |
| $query = "INSERT INTO ec_student_group_unique_number | |
| (`student_id`,`groups_id`,`type`,`unique_no`,`created_by`,`updated_by`) | |
| VALUES | |
| ('$request->studentId','$request->groupId','$request->type','$slNo','$userId','$userId')"; | |
| $this->executeQueryForObject($query); | |
| $slNoExist = $this->executeQueryForObject($slNoExistSql); | |
| } | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| return $slNoExist; | |
| } | |
| /** | |
| * Fetch Student Profile Pic in S3 Or Local Storage | |
| * @param Object $studentId | |
| * @return String $myImage | |
| * @author Krishnajith V | |
| */ | |
| public function getStudentProfilePic($studentId){ | |
| $baseUrl = CommonUtil::getBaseUrl(); | |
| $imageDetails = StudentService::getInstance()->getStudentProfilePic($studentId); | |
| if($imageDetails->backend_type == 'LOCAL_STORAGE'){ | |
| $myImage = $baseUrl.$imageDetails->docpath; | |
| } | |
| else{ | |
| $myImage = $imageDetails->docpath; | |
| } | |
| return $myImage; | |
| } | |
| /** | |
| * Fetch Student Sign in S3 Or Local Storage | |
| * @param Object $studentId | |
| * @return String $studentSignature | |
| * @author Krishnajith V | |
| */ | |
| public function getStudentSignPic($studentId){ | |
| $baseUrl = CommonUtil::getBaseUrl(); | |
| $imageDetails = StudentService::getInstance()->getStudentSignPic($studentId); | |
| if($imageDetails->backend_type == 'LOCAL_STORAGE'){ | |
| $studentSignature = $baseUrl.$imageDetails->docpath; | |
| } | |
| else{ | |
| $studentSignature = $imageDetails->docpath; | |
| } | |
| return $studentSignature; | |
| } | |
| public function staffAccessibleProgramDepartmentAndBatches(){ | |
| $staffId = $GLOBALS['userId']; | |
| try{ | |
| $sql = "SELECT r.id from roles r | |
| INNER JOIN user_account_roles uar ON uar.role_id = r.id | |
| WHERE uar.user_type = 'STAFF' AND uar.user_id = $staffId AND if(r.properties->'$.haveDepartmentRestriction',0,1)"; | |
| $properties = []; | |
| $accessibleProperties = new \stdClass(); | |
| $accessibleProperties->haveDepartmentRestriction = false; | |
| $accessibleProperties->departmentRestrictionDataFound = false; | |
| $data = $this->executeQueryForList($sql); | |
| if(!count($data)){ | |
| $sql = "SELECT g.id AS batch_id,pdr.program_id,d.deptID AS department_id | |
| FROM staffaccounts s | |
| INNER JOIN v4_ams_staff_department_relations vasdr on vasdr.staff_id = s.staffID | |
| INNER JOIN department d ON d.deptID = vasdr.department_id | |
| LEFT JOIN program_department_relation pdr ON pdr.department_id = d.deptID | |
| LEFT JOIN `groups` g ON g.program_id = pdr.program_id | |
| WHERE s.staffID = '$staffId';"; | |
| $properties = $this->executeQueryForList($sql); | |
| $accessibleProperties->haveDepartmentRestriction = true; | |
| if(count($properties)){ | |
| $accessibleProperties->departmentRestrictionDataFound = true; | |
| } | |
| }else{ | |
| $accessibleProperties->departmentRestrictionDataFound = true; | |
| } | |
| $accessibleProperties->batchIds = $this->returnUniquePropertyArray($properties,'batch_id'); | |
| $accessibleProperties->programIds = $this->returnUniquePropertyArray($properties,'program_id'); | |
| $accessibleProperties->departmentIds = $this->returnUniquePropertyArray($properties,'department_id'); | |
| return $accessibleProperties; | |
| }catch(\Exception $e){ | |
| throw new AcademicException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * Fetch Students mark history by group | |
| * @param Object $searchRequest groupId | |
| * @return String $student mark history list | |
| * @author Sibin | |
| */ | |
| public function getStudentMarkHistoryByGroup($searchRequest){ | |
| $searchRequest = $this->realEscapeObject($searchRequest); | |
| try{ | |
| $orderBy = " ORDER BY spa.properties->>'$.registerNumber',CAST(act.properties ->> '$.orderNo' AS UNSIGNED),sub.code,CONCAT(ecsmd.mark_details->>'$.examYear',LPAD(ecsmd.mark_details->>'$.examMonth', 2, '0'))"; | |
| $whereQuery = ""; | |
| if(!empty($searchRequest->groupId)) { | |
| $groupIdString = is_array($searchRequest->groupId) ? "'" . implode("','",$searchRequest->groupId) . "'" : "'".$searchRequest->groupId."'"; | |
| $whereQuery .= " AND spa.current_batch_id IN ( $groupIdString )"; | |
| } | |
| if($searchRequest->examType) { | |
| if($searchRequest->examType == "SUPPLY"){ | |
| $whereQuery .= " AND ecsmd.mark_details->>'$.examMarkType' IN ('SUPPLY','IMPROVEMENT') "; | |
| } | |
| else{ | |
| $whereQuery .= " AND ecsmd.mark_details->>'$.examMarkType' = '".$searchRequest->examType."'"; | |
| } | |
| } | |
| $sql = "SELECT | |
| s.studentID as id, | |
| g.id AS groupId, | |
| g.name AS groupName, | |
| spa.student_id AS studentId, | |
| p.name AS programName, | |
| g.properties ->>'$.programId' AS programId, | |
| IF(IFNULL(spa.properties ->> '$.registerNumber', 'null') = 'null','',spa.properties ->> '$.registerNumber') AS studentRegisterNo, | |
| ecsmd.mark_details, | |
| spbl.batch_group_id, | |
| sub.code AS subjectCode, | |
| sub.name AS subjectDesc, | |
| CONCAT(ecsmd.mark_details->>'$.examMonth','/',ecsmd.mark_details->>'$.examYear') AS examMonthYear, | |
| aa.properties_value->>'$.assessmentDate' AS examDate, eer.name AS examRegName, | |
| ecsmd.mark_details->>'$.internalMark' AS internalMark, | |
| ecsmd.mark_details->>'$.attendanceStatus' AS attendanceStatus, | |
| IF(ecsmd.mark_details->>'$.moderationMark',ecsmd.mark_details->>'$.moderationMark',0) AS moderationMark, | |
| IF(ecsmd.mark_details->>'$.moderationMark',ecsmd.mark_details->>'$.externalMark' - ecsmd.mark_details->>'$.moderationMark',ecsmd.mark_details->>'$.externalMark') as externalMark, | |
| IF(ecsmd.mark_details->>'$.resultStatus'= 'PASSED','P','F') AS resultStatus, | |
| IF(eer.properties->>'$.isSpecialExam' = true AND eer.properties->>'$.criteriaDuringSpecialExam' = 'absent','SPECIAL','NORMAL') AS examSpecialType, | |
| eer.type AS examRegistrationType, | |
| eerb.academicTermId, | |
| act.properties->>'$.orderNo' AS academicTermOrderNo, | |
| CASE | |
| WHEN ecsmd.mark_details->>'$.examMarkType' = 'REGULAR' THEN 'R' | |
| WHEN eer.properties->>'$.isSpecialExam' = true AND eer.properties->>'$.criteriaDuringSpecialExam' = 'absent' THEN 'R' | |
| WHEN ecsmd.mark_details->>'$.examMarkType' = 'SUPPLY' THEN 'S' | |
| WHEN ecsmd.mark_details->>'$.examMarkType' = 'IMPROVEMENT' THEN 'I' | |
| END AS examType | |
| FROM studentaccount s | |
| INNER JOIN student_program_account spa ON spa.student_id = s.studentID | |
| INNER JOIN student_program_batch_log spbl ON spbl.program_student_id = spa.id | |
| INNER JOIN `program` p ON p.id = spbl.program_id | |
| INNER JOIN `groups` g ON g.id = spbl.batch_group_id | |
| INNER JOIN ec_subject_mark_details ecsmd ON ecsmd.groups_id = g.id AND ecsmd.student_id = s.studentID | |
| INNER JOIN ec_exam_registration eer ON eer.id = ecsmd.ec_exam_registration_id | |
| INNER JOIN ec_exam_registration_batch eerb ON eerb.ec_exam_registration_id = eer.id AND eerb.groups_id = spbl.batch_group_id AND eerb.academicTermId = spbl.term_id | |
| INNER JOIN academic_term act ON act.id = spbl.term_id | |
| INNER JOIN ec_exam_registration_subject eers ON eers.ec_exam_registration_batch_id = eerb.id AND eers.cm_academic_paper_subjects_id = ecsmd.cm_academic_paper_subjects_id | |
| INNER JOIN am_assessment aa ON aa.id = eers.am_assessment_id | |
| INNER JOIN cm_academic_paper_subjects aps ON aps.id = ecsmd.cm_academic_paper_subjects_id | |
| INNER JOIN v4_ams_subject sub ON sub.id = aps.ams_subject_id | |
| WHERE 1=1 AND g.type = 'BATCH' $whereQuery AND ecsmd.is_active=1 and spbl.properties->> '$.academicStatus' IN ('ACTIVE','COMPLETED') $orderBy"; | |
| return $this->executeQueryForList($sql); | |
| }catch(\Exception $e){ | |
| throw new ExamControllerException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * get Assigned Subjects details in batch | |
| * @param $searchRequest | |
| * @return $subjectDetails | |
| * @author Sibin | |
| */ | |
| public function getAssignedSubjectsDetailsInBatch($searchRequest) { | |
| $searchRequest = $this->realEscapeObject($searchRequest); | |
| try{ | |
| $joinQuery = ""; | |
| $subjectOrderBy = CommonService::getInstance()->getSettings(SettingsConstants::EXAM_CONTROLLER, SettingsConstants::SUBJECT_ORDER_TAKEN_BY); | |
| if($subjectOrderBy == "ORDER"){ | |
| $orderBy = " ORDER BY CAST(act.properties ->> '$.orderNo' AS UNSIGNED) ASC , CAST(ap.properties ->> '$.order' AS UNSIGNED) ASC , CAST(aps.properties ->> '$.order' AS UNSIGNED) ASC"; | |
| } | |
| else{ | |
| $orderBy = " ORDER BY CAST(act.properties ->> '$.orderNo' AS UNSIGNED) ASC , CAST(aps.properties ->> '$.priority' AS UNSIGNED) DESC"; | |
| } | |
| $whereQuery = ""; | |
| if($searchRequest->isEnableMinorHonorExamRegistration){ | |
| $joinQuery .= " INNER JOIN cm_syllabus_academic_term_settings csats ON | |
| csats.id = ap.cm_syllabus_academic_term_settings_id | |
| INNER JOIN cm_syllabus cs ON | |
| cs.id = csats.cm_syllabus_id "; | |
| if($searchRequest->syllabusType == SyllabusTypeConstants::MINOR){ | |
| $whereQuery .= " AND cs.type IN ('MINOR')"; | |
| } | |
| elseif($searchRequest->syllabusType == SyllabusTypeConstants::HONOURS){ | |
| $whereQuery .= " AND cs.type IN ('HONOURS')"; | |
| } | |
| else{ | |
| $whereQuery .= " AND cs.type NOT IN ('HONOURS','MINOR')"; | |
| } | |
| } | |
| if(!empty($searchRequest->courseTypeId)) { | |
| $courseTypeIdString = is_array($searchRequest->courseTypeId) ? "'" . implode("','",$searchRequest->courseTypeId) . "'" : "'".$searchRequest->courseTypeId."'"; | |
| $whereQuery .= " AND p.course_type_id IN ( $courseTypeIdString )"; | |
| } | |
| if(!empty($searchRequest->groupId)) { | |
| $groupIdString = is_array($searchRequest->groupId) ? "'" . implode("','",$searchRequest->groupId) . "'" : "'".$searchRequest->groupId."'"; | |
| $whereQuery .= " AND g.id IN ( $groupIdString )"; | |
| } | |
| if(!empty($searchRequest->startYear)) { | |
| $startYearStrng = is_array($searchRequest->startYear) ? "'" . implode("','",$searchRequest->startYear) . "'" : "'".$searchRequest->startYear."'"; | |
| $whereQuery .= " AND g.properties ->> '$.startYear' IN ( $startYearStrng )"; | |
| } | |
| if(!empty($searchRequest->academicTermId)) { | |
| $academicTermIdString = is_array($searchRequest->academicTermId) ? "'" . implode("','",$searchRequest->academicTermId) . "'" : "'".$searchRequest->academicTermId."'"; | |
| $whereQuery .= " AND sg.academic_term_id IN ( $academicTermIdString )"; | |
| } | |
| if(!empty($searchRequest->academicPaperSubjectId)) { | |
| $academicPaperSubjectIdString = is_array($searchRequest->academicPaperSubjectId) ? "'" . implode("','",$searchRequest->academicPaperSubjectId) . "'" : "'".$searchRequest->academicPaperSubjectId."'"; | |
| $whereQuery .= " AND aps.id IN ( $academicPaperSubjectIdString )"; | |
| } | |
| $query = "SELECT DISTINCT | |
| aps.id AS id, | |
| aps.id AS academicPaperSubjectId, | |
| sub.id AS subjectId, | |
| sub.code AS subjectCode, | |
| sub.name AS subjectName, | |
| sub.name AS name, | |
| sg.academic_term_id as academicTermId, | |
| act.properties->>'$.orderNo' AS academicTermOrderNo, | |
| pdc.patterncourseCode AS patternCourseCode, | |
| str.properties->>'$.code' AS streamCode, | |
| cst.code as subjectTypeConstantCode, | |
| LEFT(cst.code, 1) AS subjectTypeCode, | |
| g.id AS groupId, | |
| g.name AS groupName, | |
| LEFT(aps.properties->>'$.classType', 1) AS classTypeCode, | |
| aps.properties->>'$.classType' AS classType, | |
| aps.properties->>'$.externalMaxMark' AS externalMaxMark, | |
| aps.properties ->> '$.isInternal' AS isInternal, | |
| aps.properties ->> '$.isExternal' AS isExternal, | |
| aps.properties ->> '$.internalMaxMark' AS internalMaxMark, | |
| aps.properties->>'$.classType' AS subjectPropertyType, | |
| aps.properties->>'$.syllabusYear' AS syllabusYear, | |
| vm.properties AS internalPercentage, | |
| vm1.properties AS externalPercentage, | |
| vm2.properties AS subjectPercentage, | |
| str.properties->>'$.abbreviation' AS streamDesc, | |
| aps.properties->>'$.classType' AS classType, | |
| aps.properties->>'$.credit' AS credit | |
| FROM `groups` g | |
| INNER JOIN groups_relations gr ON | |
| gr.parent_groups_id = g.id | |
| INNER JOIN `program` p ON | |
| p.id = g.properties->>'$.programId' | |
| INNER JOIN `groups` sg ON | |
| sg.id = gr.child_groups_id | |
| INNER JOIN academic_term act ON | |
| act.id = sg.academic_term_id | |
| INNER JOIN cm_academic_paper_subjects aps ON | |
| aps.id = sg.paperSubjectId | |
| INNER JOIN cm_academic_paper ap ON | |
| aps.cm_academic_paper_id = ap.id | |
| INNER JOIN v4_ams_subject sub ON | |
| sub.id = aps.ams_subject_id | |
| $joinQuery | |
| LEFT JOIN pattern_deptcourses pdc ON | |
| pdc.program_id = p.id | |
| LEFT JOIN cm_subject_types cst ON | |
| cst.id = aps.properties ->> '$.subjectTypeId' | |
| LEFT JOIN valuation_method vm ON | |
| aps.id = vm.identifying_context->>'$.academicPaperSubjectId' | |
| AND vm.`type` = 'ACADEMIC_PAPER_SUBJECT' | |
| AND vm.identifying_context->>'$.passCriteriaType' = 'INTERNAL' | |
| LEFT JOIN valuation_method vm1 ON | |
| aps.id = vm1.identifying_context->>'$.academicPaperSubjectId' | |
| AND vm1.`type` = 'ACADEMIC_PAPER_SUBJECT' | |
| AND vm1.identifying_context->>'$.passCriteriaType' = 'EXTERNAL' | |
| LEFT JOIN valuation_method vm2 ON | |
| aps.id = vm2.identifying_context->>'$.academicPaperSubjectId' | |
| AND vm2.`type` = 'ACADEMIC_PAPER_SUBJECT' | |
| AND vm2.identifying_context->>'$.passCriteriaType' = 'AGGREGATE' | |
| LEFT JOIN stream str ON | |
| JSON_SEARCH( p.stream_id, 'one', str.id) IS NOT NULL | |
| WHERE 1=1 AND g.type = 'BATCH' AND sg.type = 'SUBJECT'"; | |
| $subjectDetails = $this->executeQueryForList($query.$whereQuery.$orderBy); | |
| } | |
| catch (\Exception $e){ | |
| throw new ExamControllerException($e->getCode(),$e->getMessage()); | |
| } | |
| return $subjectDetails; | |
| } | |
| /** | |
| * Filter the Unique Objects in a PHP object array | |
| * How to use: returnUniquePropertyArray($names, 'name'); | |
| */ | |
| public static function returnUniquePropertyArray($array, $property){ | |
| $tempArray = array(); | |
| foreach ($array as $value) { | |
| if($value->{$property}){ | |
| $tempArray[$value->{$property}] = $value->{$property}; | |
| } | |
| } | |
| ksort($tempArray); | |
| return array_values($tempArray); | |
| } | |
| /** | |
| * Fetch All Syllabus Types | |
| * @return Array $syllabusTypeList | |
| * @author Krishnajith V | |
| */ | |
| public function getSyllabusType() { | |
| $syllabusTypeList = []; | |
| $query = "SELECT DISTINCT `type` as name , `type` as id from cm_syllabus"; | |
| try { | |
| $syllabusTypeList = $this->executeQueryForList($query); | |
| return $syllabusTypeList; | |
| } catch (\Exception $e) { | |
| throw new ExamControllerException(ExamControllerException::EMPTY_PARAMETERS,"Failed to fetch syllabus types"); | |
| } | |
| } | |
| /** | |
| * Fetch All Sub Curriculum | |
| * @param Object $studentId | |
| * @return Array $subCurriculums | |
| * @author Krishnajith V | |
| */ | |
| public function getAllSubCurriculum($searchRequest){ | |
| $searchRequest = $this->realEscapeObject($searchRequest); | |
| try{ | |
| $joinQuery = ""; | |
| $whereQuery = ""; | |
| if(!empty($searchRequest->programId)) { | |
| $programIdString = is_array($searchRequest->programId) ? "'" . implode("','",$searchRequest->programId) . "'" : "'".$searchRequest->programId."'"; | |
| $whereQuery .= " AND p.id IN ( $programIdString )"; | |
| } | |
| if(!empty($searchRequest->departmentId)) { | |
| $departmentIdString = is_array($searchRequest->departmentId) ? "'" . implode("','",$searchRequest->departmentId) . "'" : "'".$searchRequest->departmentId."'"; | |
| $whereQuery .= " AND d.deptID IN ( $departmentIdString )"; | |
| } | |
| if(!empty($searchRequest->groupId)) { | |
| $groupIdString = is_array($searchRequest->groupId) ? "'" . implode("','",$searchRequest->groupId) . "'" : "'".$searchRequest->groupId."'"; | |
| $whereQuery .= " AND bg.id IN ( $groupIdString )"; | |
| } | |
| if(!empty($searchRequest->curriculumId)) { | |
| $curriculumIdString = is_array($searchRequest->curriculumId) ? "'" . implode("','",$searchRequest->curriculumId) . "'" : "'".$searchRequest->curriculumId."'"; | |
| $whereQuery .= " AND cc.id IN ( $curriculumIdString )"; | |
| } | |
| if(!empty($searchRequest->syllabusType)) { | |
| $syllabusTypeStrng = is_array($searchRequest->syllabusType) ? "'" . implode("','",$searchRequest->syllabusType) . "'" : "'".$searchRequest->syllabusType."'"; | |
| $whereQuery .= " AND cs.`type` IN ( $syllabusTypeStrng )"; | |
| } | |
| $query = "SELECT DISTINCT | |
| cc.id as curriculumId, | |
| cc.name as curriculumName, | |
| cc.description as curriculumDesc, | |
| cc.program_id, | |
| cs.id as syllabusId, | |
| cs.name as syllabusName, | |
| cs.description as syllabusDesc, | |
| cs.`type` as syllabusType, | |
| cs.department_id as departmentId, | |
| d.deptName, | |
| cs.properties as syllabusProperties, | |
| cs.trashed as syllabusTrashed, | |
| csats.academic_term_id as termId, | |
| at2.name as termName, | |
| at2.properties AS academic_term_properties, | |
| at2.type AS academic_term_type, | |
| cap.id as academicPaperId, | |
| cap.name as academicPaperName, | |
| caps.id as academicPaperSubjectId, | |
| vas.id as subjectId, | |
| vas.code as subjectCode, | |
| vas.name as subjectName | |
| FROM cm_curriculum cc | |
| INNER JOIN program p ON p.id = cc.program_id | |
| INNER JOIN `groups` bg ON bg.cm_curriculum_id = cc.id | |
| INNER JOIN cm_curriculum_syllabus_relation ccsr on ccsr.cm_curriculum_id = cc.id | |
| INNER JOIN cm_syllabus cs on cs.id = ccsr.cm_syllabus_id | |
| INNER JOIN department d on d.deptID = cs.department_id | |
| INNER JOIN cm_syllabus_academic_term_settings csats on csats.cm_syllabus_id = ccsr.cm_syllabus_id | |
| INNER JOIN academic_term at2 on at2.id = csats.academic_term_id | |
| INNER JOIN cm_academic_paper cap on cap.cm_syllabus_academic_term_settings_id = csats.id | |
| INNER JOIN cm_academic_paper_subjects caps ON caps.cm_academic_paper_id = cap.id | |
| INNER JOIN v4_ams_subject vas on vas.id = caps.ams_subject_id "; | |
| $subCurriculums = $this->executeQueryForList($query.$whereQuery.$orderBy,$this->mapper2[CommonExamMapper::ALL_SUB_CURRICULUM_DETAILS]); | |
| } | |
| catch (\Exception $e){ | |
| throw new ExamControllerException($e->getCode(),$e->getMessage()); | |
| } | |
| return $subCurriculums; | |
| } | |
| /** | |
| * save Syllabus Properties (this properties using for rank list report) | |
| * @param Object $subCurriculum | |
| * @return NULL | |
| */ | |
| public function saveSyllabusProperties($subCurriculum){ | |
| $subCurriculum = $this->realEscapeObject($subCurriculum); | |
| $updatedBy = $GLOBALS['userId']; | |
| if(empty($subCurriculum->id)) | |
| throw new ExamControllerException(ExamControllerException::EMPTY_PARAMETERS,"Invailed Syllabus"); | |
| $subCurriculum->rankListDepartmentName = $subCurriculum->rankListDepartmentName ? $subCurriculum->rankListDepartmentName : ""; | |
| $subCurriculum->studentCount = $subCurriculum->studentCount ? $subCurriculum->studentCount : ""; | |
| $subCurriculum->isLanguage = $subCurriculum->isLanguage ? 1 : 0; | |
| $query = "UPDATE | |
| cm_syllabus | |
| SET | |
| properties = JSON_SET(properties, '$.rankListDepartmentName', '$subCurriculum->rankListDepartmentName'), | |
| properties = JSON_SET(properties, '$.rankListStudentCount', '$subCurriculum->studentCount'), | |
| properties = JSON_SET(properties, '$.rankListIsLanguage', $subCurriculum->isLanguage), | |
| updated_by = '$updatedBy' | |
| WHERE | |
| id = '$subCurriculum->id'"; | |
| try { | |
| $this->executeQuery($query); | |
| } catch (\Exception $e) { | |
| throw new ExamControllerException(ExamControllerException::ERROR_UPDATE_PUBLISH_STATUS_EXAM_REGISTRATION,"Error update syllabus properties ! Please try again"); | |
| } | |
| return true; | |
| } | |
| /** | |
| * Search Class Type | |
| */ | |
| public function getSubjectClassTypes() | |
| { | |
| $subjectClassTypesQuery = "SELECT cct.code AS id, cct.name AS name, cct.name AS text FROM cm_class_types cct;"; | |
| try { | |
| $subjectClassTypes = $this->executeQueryForList($subjectClassTypesQuery); | |
| } catch (\Exception $e) { | |
| throw new ExamControllerException($e->getCode(),$e->getMessage()); | |
| } | |
| return $subjectClassTypes; | |
| } | |
| /** | |
| * get Student Count For Paper Subject | |
| * @param request $request | |
| */ | |
| public function getStudentCountForPaperSubjects($request){ | |
| try { | |
| $paperId = is_array($request->paperIdArray) ? "'" . implode("','",$request->paperIdArray) . "'" : "'".$request->academicPaperSubjectId."'"; | |
| $query = "SELECT | |
| COUNT(DISTINCT esar.student_id ) as countOfStudent | |
| FROM | |
| ec_student_assessment_registration esar | |
| INNER JOIN ec_exam_registration_subject eers ON | |
| eers.am_assessment_id = esar.am_assessment_id | |
| INNER JOIN ec_exam_registration_batch eerb ON | |
| eerb.id = eers.ec_exam_registration_batch_id | |
| INNER JOIN ec_exam_registration eer | |
| ON eer.id = eerb.ec_exam_registration_id | |
| WHERE | |
| esar.ec_exam_registration_type = eer.type AND | |
| CAST(esar.properties ->> '$.registrationStatus' AS CHAR) = 'REGISTERED' AND | |
| CAST(esar.properties ->> '$.feeStatus' AS CHAR) = 'PAID' AND | |
| eerb.ec_exam_registration_id='$request->examRegistrationId' AND | |
| eers.cm_academic_paper_subjects_id IN ($paperId)"; | |
| $studentCount = $this->executeQueryForObject($query); | |
| } | |
| catch (\Exception $e){ | |
| throw new ExamControllerException($e->getCode(),$e->getMessage()); | |
| } | |
| return $studentCount->countOfStudent; | |
| } | |
| /** | |
| * Get Staff Bank Acc Details | |
| * @param $request | |
| * @return array|object|$staffs[] | |
| * @throws ExamControllerException | |
| * @author Krishnajith | |
| */ | |
| public function getStaffBankAccountDetails($request){ | |
| $staffs = []; | |
| $request = $this->realEscapeObject($request); | |
| $sql = ""; | |
| $sql = "SELECT | |
| sa.staffID as id, | |
| sa.staffName as name, | |
| sa.ifscCode as ifscCode, | |
| sa.bankAccountNumber as bankAccountNumber, | |
| sa.bankName as bankName, | |
| dept.deptName as deptName | |
| FROM | |
| staffaccounts sa | |
| INNER JOIN v4_ams_staff_department_relations sta_d | |
| ON sta_d.staff_id = sa.staffID | |
| INNER JOIN department dept | |
| ON dept.deptID = sta_d.department_id | |
| WHERE 1=1 "; | |
| if ($request) { | |
| if (!empty($request->staffIds)) { | |
| $sql .= " AND sa.staffID IN ('" . implode("','", $request->staffIds) . "')"; | |
| } | |
| } | |
| $sql .= " ORDER BY sa.staffName ASC"; | |
| try { | |
| $staffs = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ExamControllerException ($e->getCode(), $e->getMessage()); | |
| } | |
| return $staffs; | |
| } | |
| /** | |
| * To get student activity points | |
| * @param $request | |
| * @throws ExamControllerException | |
| */ | |
| public function getStudentsActivityPoints($request){ | |
| $searchRequest = $this->realEscapeObject($request); | |
| $whereQuery = ""; | |
| $studentActivityPoints = []; | |
| if(!empty($searchRequest->groupId)) { | |
| $groupIdString = is_array($searchRequest->groupId) ? "'" . implode("','",$searchRequest->groupId) . "'" : "'".$searchRequest->groupId."'"; | |
| $whereQuery .= " AND g.id IN ( $groupIdString )"; | |
| } | |
| if(!empty($searchRequest->studentId)) { | |
| $studentIdString = is_array($searchRequest->studentId) ? "'" . implode("','",$searchRequest->studentId) . "'" : "'".$searchRequest->studentId."'"; | |
| $whereQuery .= " AND aps.student_id IN ( $studentIdString )"; | |
| } | |
| $query = "SELECT DISTINCT | |
| aps.id, | |
| aps.student_id as studentId, | |
| aps.academic_term_id as academicTermId, | |
| aps.credit_achieved as activityPoint | |
| FROM | |
| activity_point_student aps | |
| INNER JOIN `groups` g ON | |
| g.program_id = aps.program_id | |
| WHERE 1=1 AND aps.status = '2' "; | |
| try{ | |
| $studentActivityPointDetails = $this->executeQueryForList($query.$whereQuery); | |
| foreach($studentActivityPointDetails as $student){ | |
| $studentActivityPoints[$student->studentId]->id = $student->studentId; | |
| $studentActivityPoints[$student->studentId]->activityPoint = $studentActivityPoints[$student->studentId]->activityPoint + $student->activityPoint; | |
| } | |
| } catch (\Exception $e) { | |
| throw new ExamControllerException($e->getCode(), "Failed to fetch student! Please try again"); | |
| } | |
| return $studentActivityPoints; | |
| } | |
| /** | |
| * get all degrees | |
| * @param $request | |
| * @return degrees | |
| */ | |
| public function getAllDegrees($request) | |
| { | |
| $whereQuery = ""; | |
| $orderBy = " ORDER BY deg.name ASC"; | |
| $searchRequest = $this->realEscapeObject($request); | |
| $query = "SELECT DISTINCT | |
| deg.id, | |
| deg.name | |
| FROM | |
| degree deg | |
| WHERE | |
| 1=1 "; | |
| try { | |
| $degree = $this->executeQueryForList($query.$whereQuery.$orderBy); | |
| } catch (\Exception $e) { | |
| throw new ExamControllerException($e->getCode(), "Failed to fetch degrees! Please try again"); | |
| } | |
| return $degree; | |
| } | |
| /** | |
| * get all batches by degree | |
| * @param $request | |
| * @return batches | |
| */ | |
| public function getAllBatchesByDegree($request) | |
| { | |
| $whereQuery = ""; | |
| $orderBy = " ORDER BY g.name ASC"; | |
| $request = $this->realEscapeObject($request); | |
| if(!empty($request->degreeId)) { | |
| $degreeIdStr = is_array($request->degreeId) ? "'" . implode("','",$request->degreeId) . "'" : "'".$request->degreeId."'"; | |
| $whereQuery .= " AND deg.id IN ($degreeIdStr)"; | |
| } | |
| $query = " SELECT DISTINCT | |
| g.id as groupId, | |
| g.name AS groupName, | |
| g.name AS text, | |
| g.academic_term_id AS academicTermId | |
| FROM | |
| `groups` g | |
| INNER JOIN program p ON | |
| p.id = CAST(g.properties ->> '$.programId'AS CHAR) | |
| INNER JOIN degree deg ON | |
| deg.id = p.degree_id | |
| WHERE 1=1 "; | |
| try { | |
| $groups = $this->executeQueryForList($query.$whereQuery.$orderBy); | |
| } catch (\Exception $e) { | |
| throw new ExamControllerException($e->getCode(), "Failed to fetch groups! Please try again"); | |
| } | |
| return $groups; | |
| } | |
| /** | |
| * get All Subject Slots | |
| * @author Krishnajith | |
| * @param $searchRequest | |
| * @throws ExamControllerException | |
| */ | |
| public function getAllSubjectSlots($searchRequest = null){ | |
| if ($searchRequest === null) { | |
| $searchRequest = new \stdClass(); | |
| } | |
| $searchRequest = $this->realEscapeObject($searchRequest); | |
| $subjectSlots = []; | |
| $whereQuery = ""; | |
| $sql = "SELECT DISTINCT | |
| c.id, | |
| c.name, | |
| c.name AS text | |
| FROM cm_common_list_object c | |
| WHERE c.type = 'SLOT' "; | |
| try { | |
| $subjectSlots = $this->executeQueryForList($sql.$whereQuery); | |
| return $subjectSlots; | |
| } | |
| catch (\Exception $e) { | |
| throw new ExamControllerException($e->getCode(),$e->getMessage()); | |
| } | |
| } | |
| /** | |
| * @param $url | |
| * @return bool | |
| */ | |
| public function isValidURL($url) { | |
| $ch = curl_init($url); | |
| curl_setopt($ch, CURLOPT_NOBODY, true); // Only header response | |
| curl_setopt($ch, CURLOPT_TIMEOUT, 5); // Timeout after 5 seconds | |
| curl_setopt($ch, CURLOPT_FOLLOWLOCATION, true); // Follow redirects | |
| curl_setopt($ch, CURLOPT_RETURNTRANSFER, true); // Don't output the response directly | |
| curl_exec($ch); | |
| $httpCode = curl_getinfo($ch, CURLINFO_HTTP_CODE); | |
| curl_close($ch); | |
| return $httpCode == 200; | |
| } | |
| /** | |
| * get All Roles By User Id | |
| * @author Sibin C | |
| * @param $searchRequest | |
| * @throws ExamControllerException | |
| */ | |
| public function getAllRolesByUserId($searchRequest){ | |
| $searchRequest = $this->realEscapeObject($searchRequest); | |
| $roles = []; | |
| $whereQuery = ""; | |
| try { | |
| if (!empty($searchRequest->userId) && !empty($searchRequest->userType)) { | |
| $userId = $this->realEscapeString($searchRequest->userId); | |
| $whereQuery .= " AND uar.user_id = '$userId'"; | |
| $sql = "SELECT DISTINCT | |
| r.id, | |
| r.name, | |
| r.code, | |
| r.properties | |
| FROM roles r | |
| INNER JOIN user_account_roles uar ON uar.role_id = r.id | |
| WHERE uar.user_type = '$searchRequest->userType' "; | |
| $roles = $this->executeQueryForList($sql.$whereQuery); | |
| } | |
| return $roles; | |
| }catch (\Exception $e) { | |
| throw new ExamControllerException($e->getCode(),$e->getMessage()); | |
| } | |
| } | |
| /** | |
| * get All Halls By Exam Assessment | |
| * @author Krishnajith | |
| */ | |
| public function getExamHallByAssessment($searchRequest){ | |
| $searchRequest = $this->realEscapeObject($searchRequest); | |
| $whereQuery = ""; | |
| if(!empty($searchRequest->examRegistrationId)) { | |
| $examRegistrationIdString = is_array($searchRequest->examRegistrationId) ? "'" . implode("','",$searchRequest->examRegistrationId) . "'" : "'".$searchRequest->examRegistrationId."'"; | |
| $whereQuery .= " AND eerb.ec_exam_registration_id IN ( $examRegistrationIdString )"; | |
| } | |
| if(is_array($searchRequest->assessmentId)){ | |
| foreach($searchRequest->assessmentId as $key => $assessmentId){ | |
| $searchRequest->assessmentId[$key] = stripslashes($assessmentId); | |
| } | |
| } | |
| if(!empty($searchRequest->assessmentId)) { | |
| $assessmentIdString = is_array($searchRequest->assessmentId) ? "'" . implode("','",$searchRequest->assessmentId) . "'" : "'".$searchRequest->assessmentId."'"; | |
| $whereQuery .= " AND ehagas.am_assessment_id IN ( $assessmentIdString )"; | |
| } | |
| if(!empty($searchRequest->hallId)) { | |
| $hallIdString = is_array($searchRequest->hallId) ? "'" . implode("','",$searchRequest->hallId) . "'" : "'".$searchRequest->hallId."'"; | |
| $whereQuery .= " AND eh.id IN ( $hallIdString )"; | |
| } | |
| $sql = "SELECT DISTINCT | |
| eh.id, | |
| eh.name, | |
| eh.name AS text | |
| FROM | |
| ec_hall_arrangement_group_assigned_student ehagas | |
| INNER JOIN ec_hall_arrangement_group_assigned_hall ehagh ON | |
| ehagh.id = ehagas.ec_hall_arrangement_group_assigned_hall_id | |
| INNER JOIN ec_exam_hall eh ON | |
| eh.id = ehagh.ec_exam_hall_id | |
| INNER JOIN ec_exam_registration_subject eers ON | |
| eers.am_assessment_id = ehagas.am_assessment_id | |
| INNER JOIN ec_exam_registration_batch eerb ON | |
| eerb.id = eers.ec_exam_registration_batch_id | |
| WHERE 1=1 AND ehagh.is_locked = 1 "; | |
| try { | |
| $examHalls = $this->executeQueryForList($sql.$whereQuery); | |
| return $examHalls; | |
| } | |
| catch (\Exception $e) { | |
| throw new ExamControllerException($e->getCode(),$e->getMessage()); | |
| } | |
| } | |
| /** | |
| * Retrieves exam batches based on the provided search criteria. | |
| * | |
| * @param object $searchRequest Search parameters object containing: | |
| * - examRegistrationId: string|array Registration ID(s) | |
| * - batchStartYear: string Start year of the batch | |
| * - deptId: string Department ID | |
| * | |
| * @return array Array of batches with groupId and batchName | |
| * | |
| * @throws ExamControllerException when database query fails | |
| * | |
| * The method performs an SQL query joining multiple tables: | |
| * - ec_exam_registration_batch | |
| * - groups | |
| * - program_department_relation | |
| * - batches | |
| * to fetch batch information based on the provided filters | |
| */ | |
| public function getExambatchesByRequest($searchRequest){ | |
| $searchRequest = $this->realEscapeObject($searchRequest); | |
| $whereQuery = ""; | |
| $orderBy = " ORDER BY b.batchID"; | |
| if(!empty($searchRequest->examRegistrationId)) { | |
| $examRegistrationIdString = is_array($searchRequest->examRegistrationId) ? "'" . implode("','",$searchRequest->examRegistrationId) . "'" : "'".$searchRequest->examRegistrationId."'"; | |
| $whereQuery .= " AND eerb.ec_exam_registration_id IN ( $examRegistrationIdString )"; | |
| } | |
| if(!empty($searchRequest->batchStartYear)) { | |
| $whereQuery .= " AND g.properties ->> '$.startYear' = '$searchRequest->batchStartYear'"; | |
| } | |
| if(!empty($searchRequest->deptId)) { | |
| $whereQuery .= " AND pdr.department_id = '$searchRequest->deptId'"; | |
| } | |
| $sql = "SELECT | |
| g.id AS groupId, | |
| g.name as batchName | |
| FROM ec_exam_registration_batch eerb | |
| INNER JOIN `groups` g ON g.id = eerb.groups_id | |
| INNER JOIN program_department_relation pdr ON pdr.program_id = g.program_id | |
| INNER JOIN batches b On b.groups_id = eerb.groups_id | |
| WHERE 1 = 1 "; | |
| try { | |
| $batches = $this->executeQueryForList($sql.$whereQuery.$orderBy); | |
| return $batches; | |
| } | |
| catch (\Exception $e) { | |
| throw new ExamControllerException($e->getCode(),$e->getMessage()); | |
| } | |
| } | |
| /** | |
| * Retrieves a list of students registered for an exam based on the provided search criteria. | |
| * | |
| * @param object $searchRequest An object containing the search criteria. | |
| * - examRegistrationId: (string|array) The ID(s) of the exam registration. | |
| * - groupsId: (string) The ID of the group. | |
| * | |
| * @return array A list of students registered for the exam. | |
| * | |
| * @throws ExamControllerException If there is an error executing the query. | |
| */ | |
| public function getstudentRegisteredForExam($searchRequest){ | |
| $searchRequest = $this->realEscapeObject($searchRequest); | |
| $whereQuery = ""; | |
| $groupBy = " GROUP BY esar.student_id "; | |
| $orderBy = " ORDER BY CAST(spa.properties->>'$.registerNumber' AS UNSIGNED) ASC, spa.properties->>'$.registerNumber' ASC "; | |
| if(!empty($searchRequest->examRegistrationId)) { | |
| $examRegistrationIdString = is_array($searchRequest->examRegistrationId) ? "'" . implode("','",$searchRequest->examRegistrationId) . "'" : "'".$searchRequest->examRegistrationId."'"; | |
| $whereQuery .= " AND eerb.ec_exam_registration_id IN ( $examRegistrationIdString )"; | |
| } | |
| if(!empty($searchRequest->groupId)) { | |
| $whereQuery .= " AND eerb.groups_id = '$searchRequest->groupId'"; | |
| } | |
| $sql = "SELECT spa.properties->>'$.registerNumber' AS registerNo, esar.student_id AS studentId FROM ec_student_assessment_registration esar | |
| INNER JOIN ec_exam_registration_subject eers ON eers.am_assessment_id = esar.am_assessment_id | |
| INNER JOIN ec_exam_registration_batch eerb ON eerb.id = eers.ec_exam_registration_batch_id | |
| INNER JOIN student_program_account spa ON spa.student_id = esar.student_id | |
| INNER JOIN cm_academic_paper_subjects caps ON caps.id = eers.cm_academic_paper_subjects_id | |
| INNER JOIN cm_academic_paper cap ON cap.id = caps.cm_academic_paper_id | |
| WHERE 1 = 1 AND esar.properties ->> '$.registrationStatus' = 'REGISTERED' "; | |
| try { | |
| $registeredStudents = $this->executeQueryForList($sql.$whereQuery.$groupBy.$orderBy); | |
| return $registeredStudents; | |
| } | |
| catch (\Exception $e) { | |
| throw new ExamControllerException($e->getCode(),$e->getMessage()); | |
| } | |
| } | |
| /* Get mark difference threshold value by courseTypeId add the percentage case | |
| * @param Integer $courseTypeId | |
| * @param $academicPaperSubjectId | |
| * @return Integer $markDiff (when scheme type is percentage the $markDiff is mark of percentage) | |
| * @throws ExamControllerException | |
| * @author Krishnajith V | |
| */ | |
| public function getExternalValuationMarkDiff($courseTypeId,$externalMaxMark = null){ | |
| $courseTypeId = $this->realEscapeString($courseTypeId); | |
| try { | |
| $markDiff = null; | |
| $sql = "SELECT courseTypeID, | |
| markdiff AS markDiff, | |
| properties ->> '$.scheme' as scheme | |
| FROM externalexam_thirdvalmarkdiff | |
| WHERE courseTypeID = '$courseTypeId' "; | |
| $markDifference = $this->executeQueryForObject($sql); | |
| if($markDifference->scheme == 'PERCENTAGE'){ | |
| if(empty($externalMaxMark)){ | |
| throw new ExamControllerException(ExamControllerException::EMPTY_PARAMETERS, "External Valuation Mark Difference not found for the given course type"); | |
| } | |
| else{ | |
| $markDiff = ( (float)$markDifference->markDiff * (float)$externalMaxMark ) / 100; | |
| } | |
| } | |
| else{ | |
| $markDiff = $markDifference->markDiff; | |
| } | |
| } catch (\Exception $e) { | |
| throw new ExamControllerException($e->getCode(), $e->getMessage()); | |
| } | |
| return $markDiff; | |
| } | |
| /** | |
| * get subject paper details | |
| * @param $request | |
| */ | |
| public function geSubjectPaperDetails($request){ | |
| $request = $this->realEscapeObject($request); | |
| $whereQuery = ""; | |
| if(!empty($request->groupId)) { | |
| $groupIdString = is_array($request->groupId) ? implode("','",$request->groupId) : $request->groupId; | |
| $whereQuery .= " AND eerb.groups_id IN ('$groupIdString') "; | |
| } | |
| if(!empty($request->acacdemicTermId)) { | |
| $acacdemicTermIdString = is_array($request->acacdemicTermId) ? implode(",",$request->acacdemicTermId) : $request->acacdemicTermId; | |
| $whereQuery .= " AND eerb.properties->>'$.academicTermId' IN ($acacdemicTermIdString) "; | |
| } | |
| try{ | |
| $sql = "SELECT | |
| eerb.groups_id as groupId, | |
| eerb.properties->>'$.academicTermId' as academicTermId, | |
| caps.id AS academicPaperSubjectId, | |
| caps.properties as academicPaperSubjectProperties, | |
| IF(caps.properties ->> '$.classType' LIKE '%THEORY%',1,0) AS isTheory, | |
| ap.name AS academicPaperName, | |
| ap.id AS academicPaperId | |
| FROM | |
| ec_exam_registration eer | |
| INNER JOIN ec_exam_registration_batch eerb ON | |
| eerb.ec_exam_registration_id = eer.id | |
| INNER JOIN ec_exam_registration_subject eers ON | |
| eers.ec_exam_registration_batch_id = eerb.id | |
| INNER JOIN cm_academic_paper_subjects caps ON | |
| caps.id = eers.cm_academic_paper_subjects_id | |
| INNER JOIN cm_academic_paper ap ON | |
| ap.id = caps.cm_academic_paper_id | |
| WHERE | |
| 1= 1 "; | |
| $academicPaperSubjects = $this->executeQueryForList($sql.$whereQuery); | |
| }catch (\Exception $e){ | |
| throw new ExamControllerException($e->getCode(),$e->getMessage()); | |
| } | |
| $academicPaperDetails = []; | |
| foreach ($academicPaperSubjects as $subject) { | |
| $subject->academicPaperSubjectProperties = json_decode($subject->academicPaperSubjectProperties); | |
| $academicPaperDetails[$subject->academicPaperId]->id = $subject->academicPaperId; | |
| $academicPaperDetails[$subject->academicPaperId]->name = $subject->academicPaperName; | |
| $academicPaperDetails[$subject->academicPaperId]->subjects[] = $subject; | |
| } | |
| return $academicPaperDetails; | |
| } | |
| /** | |
| * get All School By Department | |
| * @author Krishnajith | |
| * @param $searchRequest | |
| * @throws ExamControllerException | |
| */ | |
| public function getAllSchools($searchRequest = null){ | |
| if ($searchRequest === null) { | |
| $searchRequest = new \stdClass(); | |
| } | |
| $searchRequest = $this->realEscapeObject($searchRequest); | |
| $schools = []; | |
| $whereQuery = ""; | |
| $sql = "SELECT DISTINCT | |
| s.id, | |
| s.name, | |
| s.description AS description | |
| FROM v4_school s | |
| WHERE 1=1 "; | |
| try { | |
| $schools = $this->executeQueryForList($sql.$whereQuery); | |
| return $schools; | |
| } | |
| catch (\Exception $e) { | |
| throw new ExamControllerException($e->getCode(),$e->getMessage()); | |
| } | |
| } | |
| /** | |
| * get Academic terms details | |
| * @param $searchRequest | |
| * @return $academicTerms | |
| * @author Krishnajith | |
| */ | |
| public function getAcademicTermsDetails($searchRequest) { | |
| $searchRequest = $this->realEscapeObject($searchRequest); | |
| try{ | |
| $orderBy = " ORDER BY CAST(act.properties ->> '$.orderNo' AS UNSIGNED) ASC"; | |
| $whereQuery = ""; | |
| if(!empty($searchRequest->academicTermId)) { | |
| $academicTermIdString = is_array($searchRequest->academicTermId) ? "'" . implode("','",$searchRequest->academicTermId) . "'" : "'".$searchRequest->academicTermId."'"; | |
| $whereQuery .= " AND act.id IN ( $academicTermIdString )"; | |
| } | |
| $query = "SELECT DISTINCT | |
| act.id as id, | |
| act.name as name, | |
| act.properties ->> '$.orderNo' as orderNo | |
| FROM academic_term act | |
| WHERE 1 = 1 "; | |
| $academicTerms = $this->executeQueryForList($query.$whereQuery.$orderBy); | |
| } | |
| catch (\Exception $e){ | |
| throw new ExamControllerException($e->getCode(),$e->getMessage()); | |
| } | |
| return $academicTerms; | |
| } | |
| /** | |
| * get staff assigned exam reg month years | |
| * @param $searchRequest | |
| * @return $staffAssignedExamRegMonthYears | |
| */ | |
| public function getFacultyAssignedExamRegMonthYears($searchRequest){ | |
| $searchRequest = $this->realEscapeObject($searchRequest); | |
| $whereQuery = ""; | |
| if(!$searchRequest->staffId){ | |
| throw new ExamControllerException(ExamControllerException::EMPTY_PARAMETERS,"Faculty ID is required"); | |
| } | |
| $orderBy = " ORDER BY CAST(eer.properties->>'$.examYear' AS UNSIGNED) DESC, CAST(eer.properties->>'$.examMonth' AS UNSIGNED) DESC"; | |
| $whereQuery .= " AND oe.`type` ='EXAM_CONTROLLER' AND oec.revaluation_id IS NULL AND oe.assessment_id IS NOT NULL AND oec.is_confirmed =1 AND (oe.properties ->> '$.isMockExam' != 'true' OR oe.properties ->> '$.isMockExam' is null)"; | |
| $whereQuery .= " AND oec.created_by IN ($searchRequest->staffId)"; | |
| $sql = "SELECT DISTINCT eer.properties->>'$.examMonth' as examMonth,eer.properties->>'$.examYear' as examYear FROM oe_exam_marks_confirm oec | |
| INNER JOIN oe_exams oe ON oe.id = oec.oe_exams_id | |
| INNER JOIN am_assessment aa ON aa.id = oe.assessment_id | |
| INNER JOIN ec_exam_registration_subject eers ON eers.am_assessment_id = aa.id | |
| INNER JOIN ec_exam_registration_batch eerb ON eerb.id = eers.ec_exam_registration_batch_id | |
| INNER JOIN ec_exam_registration eer ON eer.id = eerb.ec_exam_registration_id | |
| INNER JOIN cm_academic_paper_subjects caps ON caps.id = eers.cm_academic_paper_subjects_id | |
| INNER JOIN v4_ams_subject vas ON vas.id = caps.ams_subject_id | |
| WHERE 1=1"; | |
| try { | |
| $facultyAssignedExamRegMonthYears = $this->executeQueryForList($sql.$whereQuery.$orderBy); | |
| return $facultyAssignedExamRegMonthYears; | |
| } | |
| catch (\Exception $e) { | |
| throw new ExamControllerException($e->getCode(),$e->getMessage()); | |
| } | |
| } | |
| /** | |
| * get subject category details | |
| * @param $request | |
| */ | |
| public function geSubjectCategoryDetails($request){ | |
| $request = $this->realEscapeObject($request); | |
| $whereQuery = ""; | |
| if(!empty($request->groupId)) { | |
| $groupIdString = is_array($request->groupId) ? implode("','",$request->groupId) : $request->groupId; | |
| $whereQuery .= " AND eerb.groups_id IN ('$groupIdString') "; | |
| } | |
| if(!empty($request->acacdemicTermId)) { | |
| $acacdemicTermIdString = is_array($request->acacdemicTermId) ? implode(",",$request->acacdemicTermId) : $request->acacdemicTermId; | |
| $whereQuery .= " AND eerb.properties->>'$.academicTermId' IN ($acacdemicTermIdString) "; | |
| } | |
| try{ | |
| $sql = "SELECT | |
| eerb.groups_id as groupId, | |
| eerb.properties->>'$.academicTermId' as academicTermId, | |
| caps.id AS academicPaperSubjectId, | |
| caps.properties as academicPaperSubjectProperties, | |
| IF(caps.properties ->> '$.classType' LIKE '%THEORY%',1,0) AS isTheory, | |
| ap.name AS academicPaperName, | |
| ap.id AS academicPaperId, | |
| st.id AS subjectTypeId, | |
| st.name AS subjectTypeName, | |
| st.descriptions AS subjectTypeDescription | |
| FROM | |
| ec_exam_registration eer | |
| INNER JOIN ec_exam_registration_batch eerb ON | |
| eerb.ec_exam_registration_id = eer.id | |
| INNER JOIN ec_exam_registration_subject eers ON | |
| eers.ec_exam_registration_batch_id = eerb.id | |
| INNER JOIN cm_academic_paper_subjects caps ON | |
| caps.id = eers.cm_academic_paper_subjects_id | |
| INNER JOIN cm_academic_paper ap ON | |
| ap.id = caps.cm_academic_paper_id | |
| LEFT JOIN cm_subject_types st ON | |
| st.id = caps.subject_type_id | |
| WHERE | |
| 1= 1 "; | |
| $academicPaperSubjects = $this->executeQueryForList($sql.$whereQuery); | |
| }catch (\Exception $e){ | |
| throw new ExamControllerException($e->getCode(),$e->getMessage()); | |
| } | |
| $academicSubjectTypes = []; | |
| foreach ($academicPaperSubjects as $subject) { | |
| $subject->academicPaperSubjectProperties = json_decode($subject->academicPaperSubjectProperties); | |
| if($subject->subjectTypeId){ | |
| $academicSubjectTypes[$subject->subjectTypeId]->id = $subject->subjectTypeId; | |
| $academicSubjectTypes[$subject->subjectTypeId]->name = $subject->subjectTypeDescription; | |
| $academicSubjectTypes[$subject->subjectTypeId]->description = $subject->subjectTypeDescription; | |
| $academicSubjectTypes[$subject->subjectTypeId]->subjects[] = $subject; | |
| } | |
| else{ | |
| $academicSubjectTypes[$subject->academicPaperId]->id = $subject->academicPaperId; | |
| $academicSubjectTypes[$subject->academicPaperId]->name = $subject->academicPaperName; | |
| $academicSubjectTypes[$subject->academicPaperId]->subjects[] = $subject; | |
| } | |
| } | |
| return $academicSubjectTypes; | |
| } | |
| public function getSubCourseRelations($searchRequest) { | |
| $searchRequest = $this->realEscapeObject($searchRequest); | |
| $whereQuery = ""; | |
| if(!empty($searchRequest->academicPaperSubjectIds)) { | |
| $academicPaperSubjectIdsString = is_array($searchRequest->academicPaperSubjectIds) ? "'" . implode("','",$searchRequest->academicPaperSubjectIds) . "'" : "'".$searchRequest->academicPaperSubjectIds."'"; | |
| $whereQuery .= " AND caps.id IN ( $academicPaperSubjectIdsString )"; | |
| } | |
| if(!empty($searchRequest->courseTypeId)) { | |
| $courseTypeIdsString = is_array($searchRequest->courseTypeId) ? "'" . implode("','",$searchRequest->courseTypeId) . "'" : "'".$searchRequest->courseTypeId."'"; | |
| $whereQuery .= " AND p.course_type_id IN ( $courseTypeIdsString )"; | |
| } | |
| if(!empty($searchRequest->academicTermId)) { | |
| $academicTermIdsString = is_array($searchRequest->academicTermId) ? "'" . implode("','",$searchRequest->academicTermId) . "'" : "'".$searchRequest->academicTermId."'"; | |
| $whereQuery .= " AND eerb.academicTermId IN ( $academicTermIdsString )"; | |
| } | |
| if(!empty($searchRequest->admissionYear)) { | |
| $admissionYearString = is_array($searchRequest->admissionYear) ? "'" . implode("','",$searchRequest->admissionYear) . "'" : "'".$searchRequest->admissionYear."'"; | |
| $whereQuery .= " AND g.properties->>'$.startYear' IN ( $admissionYearString )"; | |
| } | |
| try { | |
| $query = "SELECT | |
| vsrm.parent_subject_id AS parentSubjectId, | |
| vsrm.child_subject_id AS childSubjectId, | |
| vas.code AS parentCode, | |
| vas.name AS parentName | |
| FROM ec_exam_registration_subject eers | |
| INNER JOIN ec_exam_registration_batch eerb ON | |
| eerb.id = eers.ec_exam_registration_batch_id | |
| INNER JOIN `groups` g ON | |
| g.id = eerb.groups_id | |
| INNER JOIN program p ON | |
| p.id = g.properties ->> '$.programId' | |
| INNER JOIN cm_academic_paper_subjects caps ON | |
| caps.id = eers.cm_academic_paper_subjects_id | |
| INNER JOIN v4_subject_relation_mapping vsrm ON | |
| vsrm.child_subject_id = caps.ams_subject_id | |
| INNER JOIN v4_ams_subject vas ON | |
| vas.id = vsrm.parent_subject_id | |
| WHERE | |
| 1 = 1"; | |
| $subjectList = $this->executeQueryForList($query. $whereQuery); | |
| $subCourseSubjects = []; | |
| foreach ($subjectList as $subject) { | |
| $subCourseSubjects[$subject->childSubjectId]->childSubjectId = $subject->childSubjectId; | |
| $subCourseSubjects[$subject->childSubjectId]->parentSubjectId = $subject->parentSubjectId; | |
| $subCourseSubjects[$subject->childSubjectId]->parentCode = $subject->parentCode; | |
| $subCourseSubjects[$subject->childSubjectId]->parentName = $subject->parentName; | |
| } | |
| } catch (\Exception $e) { | |
| throw new ExamControllerException($e->getCode(),$e->getMessage()); | |
| } | |
| return $subCourseSubjects; | |
| } | |
| } | |
| function getDataType($dataType){ | |
| switch($dataType) | |
| { | |
| case "longtext": | |
| return "textarea"; | |
| break; | |
| case "varchar": | |
| return "text"; | |
| break; | |
| case "integer": | |
| return "text"; | |
| case "textarea": | |
| return "text"; | |
| break; | |
| // default: | |
| // return "select"; | |
| } | |
| } | |