Code Coverage |
||||||||||
Classes and Traits |
Functions and Methods |
Lines |
||||||||
| Total | |
0.00% |
0 / 1 |
|
0.00% |
0 / 28 |
CRAP | |
0.00% |
0 / 539 |
| ExamHallService | |
0.00% |
0 / 1 |
|
0.00% |
0 / 28 |
8742.00 | |
0.00% |
0 / 539 |
| __construct | |
0.00% |
0 / 1 |
2.00 | |
0.00% |
0 / 2 |
|||
| __clone | |
0.00% |
0 / 1 |
2.00 | |
0.00% |
0 / 2 |
|||
| getInstance | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 5 |
|||
| getAllExamHalls | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 8 |
|||
| getExamDeatilsByGroupAndHallId | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 36 |
|||
| getSeatArrangementDetails | |
0.00% |
0 / 1 |
42.00 | |
0.00% |
0 / 52 |
|||
| getExamHallArrangementByExam | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 14 |
|||
| getExamHallsBySubject | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 15 |
|||
| getStudentsInExamHallBySubject | |
0.00% |
0 / 1 |
56.00 | |
0.00% |
0 / 30 |
|||
| getExamHallAssignedStaffs | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 15 |
|||
| getExamHallsByExamRegAndDate | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 23 |
|||
| getExamsByExamRegHallAndDate | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 27 |
|||
| getStudentsByExamAndHall | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 15 |
|||
| getExamHallDetailsByRequest | |
0.00% |
0 / 1 |
56.00 | |
0.00% |
0 / 24 |
|||
| getExamHallArrangementDetailsByExamId | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 16 |
|||
| getExamHallsBySupplyExamRegAndDate | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 22 |
|||
| getExamsBySupplyExamRegHallAndDate | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 27 |
|||
| getStaffTotalInvigilationDuty | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 20 |
|||
| getExamHallAssignedStaffsByGroup | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 14 |
|||
| getExamGroups | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 10 |
|||
| getExamHallsByRequest | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 14 |
|||
| getExamGroupSubjectsByRequest | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 20 |
|||
| getExamGroupExamByRequest | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 13 |
|||
| saveStudentClassArrangementDetails | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 22 |
|||
| getExamHallArrangedStudentByExam | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 24 |
|||
| getExamHallStudentCount | |
0.00% |
0 / 1 |
30.00 | |
0.00% |
0 / 21 |
|||
| getExamHallBatchesByRequest | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 15 |
|||
| getExamHallsByExamSubject | |
0.00% |
0 / 1 |
56.00 | |
0.00% |
0 / 33 |
|||
| <?php | |
| namespace com\linways\core\ams\professional\service; | |
| use com\linways\core\ams\professional\exception\ProfessionalException; | |
| class ExamHallService extends BaseService | |
| { | |
| private static $_instance = null; | |
| // /Condition 2 - Locked down the constructor | |
| private function __construct() | |
| { | |
| } | |
| // Prevent any oustide instantiation of this class | |
| // /Condition 3 - Prevent any object or instance of that class to be cloned | |
| private function __clone() | |
| { | |
| } | |
| // Prevent any copy of this object | |
| // /Condition 4 - Have a single globally accessible static method | |
| public static function getInstance() | |
| { | |
| if (!is_object(self::$_instance)) // or if( is_null(self::$_instance) ) or if( self::$_instance == null ) | |
| self::$_instance = new self (); | |
| return self::$_instance; | |
| } | |
| /** | |
| * @return Object | |
| * @throws ProfessionalException | |
| */ | |
| public function getAllExamHalls() | |
| { | |
| $sql = "SELECT hallID AS id, hallName AS name FROM exam_halls"; | |
| try { | |
| return $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * Method get examDetailsByGroupId and Hall Id | |
| * @param unknown $groupID,$hallID,$isSupply | |
| * @throws ProfessionalException | |
| * @return unknown | |
| * @author Sibin | |
| */ | |
| public function getExamDeatilsByGroupAndHallId($groupID,$hallID,$isSupply) | |
| { | |
| if ($isSupply === 0) { //Regular Exam | |
| $selection=" er.examregID, | |
| er.examregName, | |
| er.examYear, | |
| er.examMonth"; | |
| $condition ="INNER JOIN exam_registration er | |
| ON er.examregID=e.examregID | |
| "; | |
| } else if ($isSupply === 1) { | |
| $selection=" es.id, | |
| es.supplyDesc, | |
| es.examYear, | |
| es.examMonth"; | |
| $condition ="INNER JOIN exam_supplementary es | |
| ON es.id=e.supply_examreg_id | |
| "; | |
| } | |
| $sql ="SELECT distinct | |
| s.semName, | |
| $selection | |
| from exam_hall_arranged_students ehs | |
| INNER JOIN exam e | |
| ON e.examID=ehs.examID | |
| $condition | |
| INNER JOIN semesters s | |
| ON s.semID = e.semID | |
| where groupID='$groupID' AND hallID='$hallID' | |
| "; | |
| try { | |
| $examDetails = $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $examDetails; | |
| } | |
| //end get examDetailsByGroupId and Hall Id | |
| /** | |
| * get seat arrangement in a hall | |
| * | |
| * @param $groupId | |
| * @param $hallId | |
| * @param $examId | |
| * @return Array $seatArrangementList | |
| */ | |
| public function getSeatArrangementDetails($groupId, $hallId, $examId = null, $batchId = null) | |
| { | |
| $groupId = $this->realEscapeString($groupId); | |
| $hallId = $this->realEscapeString($hallId); | |
| $examId = $this->realEscapeArray($examId); | |
| $batchId = $this->realEscapeArray($batchId); | |
| $sqlCondition = ""; | |
| if (!empty($examId)) { | |
| $examIdString = is_array($examId) ? implode(",", $examId) : $examId; | |
| $sqlCondition .= " AND ehas.examID IN ($examIdString) "; | |
| } | |
| if (!empty($batchId)) { | |
| $batchIdString = is_array($batchId) ? implode(",", $batchId) : $batchId; | |
| $sqlCondition .= " AND b.batchID IN ($batchIdString) "; | |
| } | |
| try | |
| { | |
| $sql = "SELECT | |
| sa.studentID AS studentId, | |
| sa.studentName, | |
| sa.rollNo, | |
| sa.regNo, | |
| b.batchName, | |
| ehas.seatNo, | |
| ehas.rowNo, | |
| ehas.columnNo, | |
| ehas.seat, | |
| s.subjectID AS subjectId, | |
| s.subjectName, | |
| s.subjectDesc, | |
| s.syllabusName | |
| FROM | |
| exam_hall_arranged_students ehas | |
| INNER JOIN studentaccount sa ON | |
| sa.studentID = ehas.studentID | |
| INNER JOIN batches b ON | |
| b.batchID = sa.batchID | |
| INNER JOIN exam e ON | |
| e.examID = ehas.examID | |
| AND e.batchID = b.batchID | |
| INNER JOIN subjects s ON | |
| s.subjectID = e.subjectID | |
| WHERE | |
| ehas.groupID = '$groupId' | |
| AND ehas.hallID = '$hallId' | |
| $sqlCondition | |
| ORDER BY ehas.seatNo ASC"; | |
| $seatArrangementList = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $seatArrangementList; | |
| } | |
| /** | |
| * get Exam Hall Arrangement By Exam | |
| * @param $examId | |
| * @author sibin | |
| */ | |
| public function getExamHallArrangementByExam($examId) | |
| { | |
| $halls = []; | |
| $examId = $this->realEscapeString($examId); | |
| $sql = "SELECT ehs.hallID,eh.hallName,min(sa.regNo) as regNoFrom,max(sa.regNo) as regNoTo,count(sa.regNo) as hallStudentCount from exam_hall_arranged_students ehs | |
| INNER JOIN exam_halls eh ON eh.hallID = ehs.hallID | |
| INNER JOIN studentaccount sa ON sa.studentID = ehs.studentID where ehs.examID='$examId' | |
| group by ehs.hallID order by regNoFrom asc"; | |
| try { | |
| $halls = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $halls; | |
| } | |
| /** | |
| * get Exam Hall Arrangement By subject | |
| * @param $request | |
| * @author sibin | |
| */ | |
| public function getExamHallsBySubject($request) | |
| { | |
| $halls = []; | |
| $request = $this->realEscapeObject($request); | |
| $examRegField = $request->isSupply ? "e.supply_examreg_id" : "e.examregID"; | |
| $sql = "SELECT distinct ehs.hallID,eh.hallName from exam_hall_arranged_students ehs | |
| INNER JOIN exam e ON e.examID = ehs.examID | |
| INNER JOIN exam_halls eh ON eh.hallID = ehs.hallID | |
| WHERE $examRegField='$request->examRegId' and e.subjectID='$request->subjectId'"; | |
| try { | |
| $halls = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $halls; | |
| } | |
| /** | |
| * get Students In ExamHall By Subject | |
| * @param $request | |
| * @author sibin | |
| */ | |
| public function getStudentsInExamHallBySubject($request) | |
| { | |
| $studentList = []; | |
| $packetCondition = ""; | |
| $request = $this->realEscapeObject($request); | |
| $filterPacket = $request->viewOnly ? "":" AND evpr.packetNo ='$request->packetNo' "; | |
| $filterPacketJoin = $request->viewOnly ? " INNER JOIN " : " LEFT JOIN "; | |
| $examRegField = $request->isSupply ? "supply_examreg_id" : "examregID"; | |
| $falseNoExamRegField = $request->isSupply ? "exam_supplementary_id" : "examregID"; | |
| $packetOrder = "evpr.packetNo ASC,"; | |
| if($request->packetNo){ | |
| $packetOrder = ""; | |
| $packetCondition=" AND ehs.studentID NOT IN(SELECT pr.studentId from examValuationStudentPacketsRelation pr | |
| INNER JOIN exam ex ON ex.examID = pr.examId | |
| WHERE pr.hallID='$request->hallId' and ex.subjectID='$request->subjectId' and pr.packetNo NOT IN('$request->packetNo') and pr.packetNo IS NOT NULL and ex.$examRegField='$request->examRegId')"; | |
| } | |
| $sql="SELECT ehs.studentID,efn.false_number as falseNumber,efn.alpha_numeric_code as alphaNumericCode,ehs.examID,efna.false_number as falseNumberInput,efna.false_number as hasFalseNumberAssigned,evpr.packetNo, null as falseNumberMismatch from exam_hall_arranged_students ehs | |
| INNER JOIN exam e ON e.examID = ehs.examID | |
| INNER JOIN examcontroller_false_number efn ON efn.studentID = ehs.studentID AND efn.examID = ehs.examID AND efn.$falseNoExamRegField = e.$examRegField | |
| $filterPacketJoin examValuationStudentPacketsRelation evpr ON evpr.studentId = ehs.studentID AND evpr.examId = ehs.examID $filterPacket | |
| LEFT JOIN examcontroller_false_number efna ON efna.examID = e.examID AND efna.$falseNoExamRegField = e.$examRegField AND efna.studentID = evpr.studentId | |
| WHERE e.$examRegField='$request->examRegId' and e.subjectID='$request->subjectId' and ehs.hallID='$request->hallId' | |
| $packetCondition | |
| ORDER BY $packetOrder efna.false_number DESC,efn.false_number DESC"; | |
| try { | |
| $studentList = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $studentList; | |
| } | |
| /** get Exam Hall Assigned Staffs | |
| * @param $examId | |
| * @author sibin | |
| */ | |
| public function getExamHallAssignedStaffs($hallID,$groupID) | |
| { | |
| $hallID = $this->realEscapeString($hallID); | |
| $groupID = $this->realEscapeString($groupID); | |
| $staffs=[]; | |
| $sql = "SELECT distinct ehs.staffID,sa.staffName | |
| from exam_hall_arranged_staffs ehs | |
| INNER JOIN staffaccounts sa ON sa.staffID = ehs.staffID | |
| where ehs.groupID='$groupID' and ehs.hallID='$hallID'"; | |
| try { | |
| $staffs = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $staffs; | |
| } | |
| /** | |
| * get Exam Halls by exam reg and date time | |
| * @param $request | |
| * @author sibin | |
| */ | |
| public function getExamHallsByExamRegAndDate($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $condition = ""; | |
| if($request->examDateTime){ | |
| $condition .= " AND concat(e.examDate,\" \",DATE_FORMAT(CAST(STR_TO_DATE(e.examStartTime,'%l:%i%p') AS DATETIME),'%r')) = '$request->examDateTime' "; | |
| } | |
| if($request->examDate){ | |
| $condition .= " AND e.examDate = '$request->examDate' "; | |
| } | |
| $halls = []; | |
| $sql = "SELECT ehs.hallID,eh.hallName,e.examID,e.batchID,e.subjectID,e.semID,ehs.groupID as groupId from exam e | |
| INNER JOIN exam_hall_arranged_students ehs ON ehs.examID=e.examID | |
| INNER JOIN exam_halls eh ON eh.hallID = ehs.hallID | |
| where e.examregID ='$request->examRegId' | |
| $condition | |
| group by ehs.hallID"; | |
| try { | |
| $halls = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $halls; | |
| } | |
| /** | |
| * get Exam Halls by hall Id ,exam reg and date time | |
| * @param $request | |
| * @author sibin | |
| */ | |
| public function getExamsByExamRegHallAndDate($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $condition = ""; | |
| if ($request->examDateTime) { | |
| $condition .= " AND concat(e.examDate,\" \",DATE_FORMAT(CAST(STR_TO_DATE(e.examStartTime,'%l:%i%p') AS DATETIME),'%r')) = '$request->examDateTime' "; | |
| } | |
| if ($request->examDate) { | |
| $condition .= " AND e.examDate = '$request->examDate' "; | |
| } | |
| $exams = []; | |
| $sql = "SELECT ehs.hallID,ehs.examID,e.batchID,e.subjectID,s.subjectName,s.subjectDesc,pd.patternAbbreviation as programme,er.examregName,e.examDate,e.examStartTime,eh.hallName,er.examregDesc as examRegDesc from exam_hall_arranged_students ehs | |
| INNER JOIN exam e ON e.examID = ehs.examID | |
| INNER JOIN subjects s ON s.subjectID = e.subjectID | |
| INNER JOIN batches b ON b.batchID = e.batchID | |
| INNER JOIN pattern_deptcourses pd ON pd.patterncourseID = b.patterncourseID | |
| INNER JOIN exam_registration er ON er.examregID = e.examregID | |
| INNER JOIN exam_halls eh ON eh.hallID = ehs.hallID | |
| WHERE ehs.hallID='$request->hallId' AND e.examregID = '$request->examRegId' | |
| $condition | |
| group by ehs.examID"; | |
| try { | |
| $exams = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $exams; | |
| } | |
| /** | |
| * get students by Exams and hall | |
| * @param $request | |
| * @author sibin | |
| */ | |
| public function getStudentsByExamAndHall($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $students = []; | |
| $sql = "SELECT ehs.hallID,ehs.examID,ehs.studentID,sa.regNo,sa.studentName,sa.myImage,sa.studentSignImage,b.batchID as batchId,b.batchName,eg.groupID as groupId,eg.groupName from exam_hall_arranged_students ehs | |
| INNER JOIN studentaccount sa ON sa.studentID = ehs.studentID | |
| INNER JOIN batches b ON b.batchID = sa.batchID | |
| INNER JOIN exam_groups eg ON eg.groupID = ehs.groupID | |
| WHERE ehs.examID = '$request->examId' and ehs.hallID = '$request->hallId' "; | |
| try { | |
| $students = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $students; | |
| } | |
| /** | |
| * get Exam Hall Details By Request | |
| * @param $request | |
| * @author sibin | |
| */ | |
| public function getExamHallDetailsByRequest($request) | |
| { | |
| $halls = []; | |
| $request = $this->realEscapeObject($request); | |
| $examRegField = $request->isSupply ? "e.supply_examreg_id" : "e.examregID"; | |
| $examRegTable = $request->isSupply ? "exam_supplementary" : "exam_registration"; | |
| $examRegTableId = $request->isSupply ? "id" : "examregID"; | |
| $examRegName = $request->isSupply ? "supplyDesc" : "examregName"; | |
| $condition = ""; | |
| if ($request->hallId) { | |
| $condition .= " AND eh.hallID = '$request->hallId' "; | |
| } | |
| $sql = "SELECT distinct ehs.hallID,eh.hallName,er.$examRegName as examRegName from exam_hall_arranged_students ehs | |
| INNER JOIN exam e ON e.examID = ehs.examID | |
| INNER JOIN exam_halls eh ON eh.hallID = ehs.hallID | |
| LEFT JOIN $examRegTable er ON er.$examRegTableId = $examRegField | |
| WHERE $examRegField='$request->examRegId' and e.subjectID='$request->subjectId' | |
| $condition"; | |
| try { | |
| $halls = $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $halls; | |
| } | |
| /** | |
| * get Exam Hall Arrangement By Exam | |
| * @param $examId | |
| */ | |
| public function getExamHallArrangementDetailsByExamId($examId) | |
| { | |
| $halls = []; | |
| $examId = $this->realEscapeString($examId); | |
| $sql = "SELECT ehs.hallID,eh.hallName,sa.regNo,efn.false_number as falseNumber from exam_hall_arranged_students ehs | |
| INNER JOIN exam_halls eh ON eh.hallID = ehs.hallID | |
| INNER JOIN studentaccount sa ON sa.studentID = ehs.studentID | |
| LEFT JOIN examcontroller_false_number efn ON efn.studentID = sa.studentID AND efn.examID = ehs.examID | |
| where ehs.examID='$examId' | |
| order by sa.regNo asc"; | |
| try { | |
| $halls = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $halls; | |
| } | |
| /** | |
| * get Exam Halls by supply exam reg and date time | |
| * @param $request | |
| */ | |
| public function getExamHallsBySupplyExamRegAndDate($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $condition = ""; | |
| if ($request->examDateTime) { | |
| $condition .= " AND concat(e.examDate,\" \",DATE_FORMAT(CAST(STR_TO_DATE(e.examStartTime,'%l:%i%p') AS DATETIME),'%r')) = '$request->examDateTime' "; | |
| } | |
| if ($request->examDate) { | |
| $condition .= " AND e.examDate = '$request->examDate' "; | |
| } | |
| $halls = []; | |
| $sql = "SELECT ehs.hallID,eh.hallName,e.examID,e.batchID,e.subjectID,e.semID,ehs.groupID as groupId from exam e | |
| INNER JOIN exam_hall_arranged_students ehs ON ehs.examID=e.examID | |
| INNER JOIN exam_halls eh ON eh.hallID = ehs.hallID | |
| where e.supply_examreg_id ='$request->examRegId' | |
| group by ehs.hallID"; | |
| try { | |
| $halls = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $halls; | |
| } | |
| /** | |
| * get Exam Halls by hall Id ,supplyexam reg and date time | |
| * @param $request | |
| */ | |
| public function getExamsBySupplyExamRegHallAndDate($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $condition = ""; | |
| if ($request->examDateTime) { | |
| $condition .= " AND concat(e.examDate,\" \",DATE_FORMAT(CAST(STR_TO_DATE(e.examStartTime,'%l:%i%p') AS DATETIME),'%r')) = '$request->examDateTime' "; | |
| } | |
| if ($request->examDate) { | |
| $condition .= " AND e.examDate = '$request->examDate' "; | |
| } | |
| $exams = []; | |
| $sql = "SELECT ehs.hallID,ehs.examID,e.batchID,e.subjectID,s.subjectName,s.subjectDesc,pd.patternAbbreviation as programme,er.supplyDesc as examregName,e.examDate,e.examStartTime,eh.hallName,er.supplyDescription as examRegDesc from exam_hall_arranged_students ehs | |
| INNER JOIN exam e ON e.examID = ehs.examID | |
| INNER JOIN subjects s ON s.subjectID = e.subjectID | |
| INNER JOIN batches b ON b.batchID = e.batchID | |
| INNER JOIN pattern_deptcourses pd ON pd.patterncourseID = b.patterncourseID | |
| INNER JOIN exam_supplementary er ON er.id = e.supply_examreg_id | |
| INNER JOIN exam_halls eh ON eh.hallID = ehs.hallID | |
| WHERE ehs.hallID='$request->hallId' AND e.supply_examreg_id = '$request->examRegId' | |
| $condition | |
| group by ehs.examID"; | |
| try { | |
| $exams = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $exams; | |
| } | |
| /** | |
| * get getStaffTotalInvigilationDuty | |
| * @param $request | |
| */ | |
| public function getStaffTotalInvigilationDuty($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $dates = []; | |
| foreach($request->dates as $date){ | |
| $dates[] = "'".$date."'"; | |
| } | |
| $dates = implode(",",$dates); | |
| $exams = []; | |
| $sql = "SELECT distinct t1.examID, t1.examStartTime, t1.examEndTime from exam t1, exam_group_exams t2, exam_hall_arranged_staffs t3, exam_hall_arranged_students t4, studentaccount t5 | |
| where t1.examDate IN ($dates) and t2.examID = t1.examID and t3.groupID = t2.groupID and t3.staffID ='$request->staffId' | |
| and t4.groupID = t2.groupID and t4.studentID = t5.studentID and t5.batchID = t1.batchID and t3.hallID = t4.hallID"; | |
| try { | |
| if (!empty($request->dates)) { | |
| $exams = $this->executeQueryForList($sql); | |
| } | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $exams; | |
| } | |
| /** get Exam Hall Assigned Staffs by group | |
| * @param $examId | |
| * @author sibin | |
| */ | |
| public function getExamHallAssignedStaffsByGroup($groupID) | |
| { | |
| $groupID = $this->realEscapeString($groupID); | |
| $staffs = []; | |
| $sql = "SELECT distinct ehs.staffID,sa.staffName | |
| from exam_group_staffs ehs | |
| INNER JOIN staffaccounts sa ON sa.staffID = ehs.staffID | |
| where ehs.groupID='$groupID'"; | |
| try { | |
| $staffs = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $staffs; | |
| } | |
| /** get Exam groups | |
| * @param $examId | |
| * @author sibin | |
| */ | |
| public function getExamGroups() | |
| { | |
| $groups = []; | |
| $sql = "SELECT groupID as id,groupName as name,allowStudentSeatDisplay FROM exam_groups"; | |
| try { | |
| $groups = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $groups; | |
| } | |
| /** get Exam halls | |
| * @param $examId | |
| * @author sibin | |
| */ | |
| public function getExamHallsByRequest($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $halls = []; | |
| $sql = "SELECT egh.hallID as id,eh.hallName as name | |
| FROM exam_group_halls egh | |
| INNER JOIN exam_halls eh ON eh.hallID = egh.hallID | |
| WHERE egh.groupID IN ($request->groupId) group BY egh.hallID"; | |
| try { | |
| $halls = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $halls; | |
| } | |
| /** get Exam group subjects | |
| * @param $examId | |
| * @author sibin | |
| */ | |
| public function getExamGroupSubjectsByRequest($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $condition = ""; | |
| if($request->subjectId){ | |
| $condition .=" AND e.subjectID IN ($request->subjectId) "; | |
| } | |
| $subjects = []; | |
| $sql = "SELECT s.subjectID as id,s.subjectName,s.subjectDesc,concat(s.subjectName,' - ',s.subjectDesc) as name,e.semID as semId FROM exam_group_exams ege | |
| INNER JOIN exam e ON e.examID = ege.examID | |
| INNER JOIN subjects s ON s.subjectID = e.subjectID | |
| WHERE ege.groupID IN ($request->groupId) | |
| $condition | |
| group BY s.subjectID"; | |
| try { | |
| $subjects = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $subjects; | |
| } | |
| /** get Exam group subjects exams | |
| * @param $examId | |
| * @author sibin | |
| */ | |
| public function getExamGroupExamByRequest($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $exam = ""; | |
| $sql = "SELECT ege.examID as examId FROM exam_group_exams ege | |
| INNER JOIN exam e ON e.examID = ege.examID | |
| WHERE e.batchID IN ($request->batchId) AND e.subjectID IN ($request->subjectId) AND ege.groupID IN ($request->groupId)"; | |
| try { | |
| $exam = $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $exam; | |
| } | |
| /** save Student class arrange details | |
| * @param $examId | |
| * @author sibin | |
| */ | |
| public function saveStudentClassArrangementDetails($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $result =""; | |
| $sqlExist = "SELECT ehs.hallID,ehs.studentID,ehs.examID,ehs.seatNo,ehs.rowNo,ehs.columnNo,ehs.seat,eh.hallName FROM exam_hall_arranged_students ehs | |
| INNER JOIN exam_halls eh ON eh.hallID = ehs.hallID | |
| WHERE studentID IN ($request->studentId) AND examID IN ($request->examId)"; | |
| try { | |
| $exist = $this->executeQueryForObject($sqlExist); | |
| if($exist){ | |
| $exist->exist = true; | |
| return $exist; | |
| }else{ | |
| $sql = "INSERT INTO exam_hall_arranged_students(groupID,hallID,studentID,examID,seatNo,rowNo,columnNo,seat) | |
| VALUES($request->groupId,$request->hallId,$request->studentId,$request->examId,$request->seatNo,$request->rowNo,$request->colNo,$request->seatPos)"; | |
| $this->executeQueryForObject($sql); | |
| $result->isSuccess = true; | |
| return $result; | |
| } | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** get Student class arrange details | |
| * @param $examId | |
| * @author sibin | |
| */ | |
| public function getExamHallArrangedStudentByExam($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $studentList=[]; | |
| $condition = ""; | |
| if($request->examId){ | |
| $condition .=" AND ehs.examID IN ($request->examId)"; | |
| } | |
| if ($request->isAbsent) { | |
| $condition .= " AND et.isAbsent NOT IN (0)"; | |
| } | |
| $sql = "SELECT ehs.hallID,eh.hallName,sa.studentID,sa.regNo,ehs.examID,et.isAbsent from exam_hall_arranged_students ehs | |
| INNER JOIN exam_halls eh ON eh.hallID = ehs.hallID | |
| INNER JOIN studentaccount sa ON sa.studentID = ehs.studentID | |
| INNER JOIN exam_attendance et ON et.examID = ehs.examID AND et.studentID= ehs.studentID | |
| where 1 =1 | |
| $condition | |
| order by sa.regNo asc"; | |
| try { | |
| $studentList = $this->executeQueryForList($sql); | |
| return $studentList; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** get Student class student count | |
| * @param $examId | |
| * @author sibin | |
| */ | |
| public function getExamHallStudentCount($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $condition = ""; | |
| if ($request->examId) { | |
| $condition .= " AND ehs.examID IN ($request->examId)"; | |
| } | |
| if ($request->isAbsent) { | |
| $condition .= " AND et.isAbsent NOT IN (0)"; | |
| } | |
| if ($request->hallId) { | |
| $condition .= " AND ehs.hallID IN ($request->hallId)"; | |
| } | |
| $sql = "SELECT count(ehs.studentID) as hallStudentCount from exam_hall_arranged_students ehs | |
| where 1=1 | |
| $condition"; | |
| try { | |
| return $this->executeQueryForObject($sql)->hallStudentCount; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** get exam batches in exam hall | |
| * @param $examId | |
| * @author sibin | |
| */ | |
| public function getExamHallBatchesByRequest($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $batchList = []; | |
| $sql = "SELECT distinct t1.hallID, t3.hallName ,t2.batchID,b.batchName | |
| FROM exam_hall_arranged_students t1, studentaccount t2, exam_halls t3,exam t4 ,batches b | |
| where t1.groupID in ($request->groupIds) and t1.studentID = t2.studentID and t3.hallID = t1.hallID and t1.examID=t4.examID and t4.examTypeID='$request->examTypeId' and t1.hallID='$request->hallId' and t2.batchID = b.batchID | |
| group by t2.batchID | |
| order by t3.hallprefNo asc"; | |
| try { | |
| $batchList = $this->executeQueryForList($sql); | |
| return $batchList; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** get exam halls by subject request | |
| * @param $request | |
| * @author sibin | |
| */ | |
| public function getExamHallsByExamSubject($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $condition=""; | |
| if(!$request->examRegId){ | |
| return false; | |
| } | |
| $examRegFiled = "e.examregID"; | |
| if($request->isSupply){ | |
| $examRegFiled = "e.supply_examreg_id"; | |
| } | |
| if($request->subjectId){ | |
| $condition .=" AND e.subjectID IN ($request->subjectId)"; | |
| } | |
| if ($request->semId) { | |
| $condition .= " AND e.semID IN ($request->semId)"; | |
| } | |
| if ($request->hallId) { | |
| $condition .= " AND eh.hallID IN ($request->hallId)"; | |
| } | |
| $hallList = []; | |
| $sql = "SELECT eh.hallID as hallId,eh.hallName,ehrs.examID as examId FROM exam_hall_arranged_students ehrs | |
| INNER JOIN exam e ON e.examID = ehrs.examID | |
| INNER JOIN exam_halls eh ON eh.hallID = ehrs.hallID | |
| WHERE $examRegFiled IN ($request->examRegId) | |
| $condition | |
| GROUP BY eh.hallID"; | |
| try { | |
| $hallList = $this->executeQueryForList($sql); | |
| return $hallList; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| } |