Code Coverage |
||||||||||
Classes and Traits |
Functions and Methods |
Lines |
||||||||
| Total | |
0.00% |
0 / 1 |
|
0.00% |
0 / 18 |
CRAP | |
0.00% |
0 / 496 |
| QuizAndSurveyService | |
0.00% |
0 / 1 |
|
0.00% |
0 / 18 |
4692.00 | |
0.00% |
0 / 496 |
| __construct | |
0.00% |
0 / 1 |
2.00 | |
0.00% |
0 / 3 |
|||
| __clone | |
0.00% |
0 / 1 |
2.00 | |
0.00% |
0 / 1 |
|||
| getInstance | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 5 |
|||
| getAllQuizesOfAStudent | |
0.00% |
0 / 1 |
30.00 | |
0.00% |
0 / 63 |
|||
| getAllQuizesBySBS | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 10 |
|||
| getQuizDetailsById | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 9 |
|||
| getQuizAttendedStudentDetails | |
0.00% |
0 / 1 |
72.00 | |
0.00% |
0 / 117 |
|||
| getQuizQuestionsAndAnswersByQuizid | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 9 |
|||
| getTotalScoreOfAStudentInAQuiz | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 20 |
|||
| getAllQuizOfAStaff | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 19 |
|||
| getTotalPointsOfAQuiz | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 10 |
|||
| getAllStudentMarksAndCoReportOfABatchAndSemester | |
0.00% |
0 / 1 |
156.00 | |
0.00% |
0 / 70 |
|||
| getQuizAttendedStudentList | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 14 |
|||
| getAllQuizzesBySbsId | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 30 |
|||
| studentMarksDetails | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 44 |
|||
| getConsolidatedQuizReport | |
0.00% |
0 / 1 |
56.00 | |
0.00% |
0 / 38 |
|||
| getQuizzesSubectwise | |
0.00% |
0 / 1 |
42.00 | |
0.00% |
0 / 27 |
|||
| getMaxQuestionsInQuiz | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 7 |
|||
| <?php | |
| namespace com\linways\core\ams\professional\service; | |
| use com\linways\core\ams\professional\service\BaseService; | |
| use com\linways\core\ams\professional\service\BatchService; | |
| use com\linways\core\ams\professional\exception\ProfessionalException; | |
| use com\linways\core\ams\professional\mapper\QuizAndSurveyServiceMapper; | |
| use com\linways\core\ams\professional\request\SearchStudentRequest; | |
| use com\linways\core\ams\professional\service\StudentService; | |
| class QuizAndSurveyService extends BaseService | |
| { | |
| // /Condition 1 - Presence of a static member variable | |
| private static $_instance = null; | |
| private $mapper = []; | |
| // /Condition 2 - Locked down the constructor | |
| private function __construct() | |
| { | |
| $this->mapper = QuizAndSurveyServiceMapper::getInstance()->getMapper(); | |
| } | |
| // Prevent any oustide instantiation of this class | |
| // /Condition 3 - Prevent any object or instance of that class to be cloned | |
| private function __clone() | |
| {} | |
| // Prevent any copy of this object | |
| // /Condition 4 - Have a single globally accessible static method | |
| public static function getInstance() | |
| { | |
| if (! is_object(self::$_instance)) // or if( is_null(self::$_instance) ) or if( self::$_instance == null ) | |
| self::$_instance = new self(); | |
| return self::$_instance; | |
| } | |
| /** | |
| * | |
| * | |
| * @param [int] $studentId | |
| * @return array | |
| */ | |
| public function getAllQuizesOfAStudent($studentId){ | |
| $sql = ""; | |
| $quizList = []; | |
| $studentId = $this->realEscapeString($studentId); | |
| $sql = "SELECT | |
| q.quizID AS id, | |
| q.quizName AS name, | |
| q.quizDesc AS description, | |
| q.timeLImit AS timeLimit, | |
| q.subjectID AS subjectId, | |
| sub.subjectName AS subjectName, | |
| sub.subjectDesc as subjectDescription, | |
| sem.semName as semesterName, | |
| sr.staffID AS staffId, | |
| sa.staffName, | |
| q.subbatchIDs AS subbatchIds, | |
| COUNT(qs.answerID) AS answered, | |
| IF(qsas.is_attended IS NOT NULL | |
| AND qsas.is_attended != 0, | |
| 1, | |
| 0) AS isAttended, | |
| TIMESTAMPDIFF(MINUTE, | |
| FROM_UNIXTIME(qsas.timeStart), | |
| FROM_UNIXTIME(qsas.timeEnd)) AS timeTakenByStudent | |
| FROM | |
| quiz q | |
| INNER JOIN | |
| subjects sub ON sub.subjectID = q.subjectID | |
| INNER JOIN | |
| sbs_relation sr ON sr.sbsID = q.sbsID | |
| AND sr.subjectID = q.subjectID | |
| AND q.unlocked = 1 | |
| INNER JOIN | |
| semesters sem ON sr.semID = sem.semID | |
| INNER JOIN | |
| staffaccounts sa ON sa.staffID = sr.staffID | |
| INNER JOIN | |
| studentaccount sta ON sta.batchID = sr.batchID | |
| AND sta.studentID = '$studentId' | |
| LEFT JOIN | |
| quiz_quiz_questions qqq ON qqq.quizID = q.quizID | |
| LEFT JOIN | |
| quiz_studentanswer qs ON qs.studentID = sta.studentID | |
| AND qs.quizquestID = qqq.quizquestID | |
| LEFT JOIN | |
| quiz_student_answer_starttime qsas ON qsas.studentID = sta.studentID | |
| AND qsas.quizID = q.quizID | |
| GROUP BY q.quizID | |
| ORDER BY qsas.is_attended ASC"; | |
| try{ | |
| $quizList = $this->executeQueryForList($sql); | |
| }catch(\Exception $e){ | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| foreach($quizList as $key => $quiz){ | |
| $sql = "select subbatchID from subbatch_student where subbatchID IN($quiz->subbatchIds) AND studentID = $studentId"; | |
| $response = $this->executeQueryForObject($sql); | |
| if((empty($response) && $quiz->subbatchIds != "0")){ | |
| array_splice($quizList,$key,1); | |
| continue; | |
| } | |
| // if($quiz->timeTakenByStudent >= $quiz->timeLimit){ | |
| // $quiz->isAttended = 1; | |
| // } | |
| } | |
| return $quizList; | |
| } | |
| public function getAllQuizesBySBS($subjectId, $batchId, $semId) { | |
| $sql = ""; | |
| $quizList = []; | |
| $sql = "SELECT quizID, quizName FROM quiz WHERE sbsID in (SELECT sbsID FROM sbs_relation WHERE subjectID = '".$subjectId."' AND batchID = '".$batchId."' AND semID = '".$semId."')"; | |
| try{ | |
| $quizList = $this->executeQueryForList($sql); | |
| } catch(\Exception $e){ | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $quizList; | |
| } | |
| public function getQuizDetailsById($quizId) | |
| { | |
| $quizId = $this->realEscapeString($quizId); | |
| $sql = "SELECT quizID, quizName, quizDesc, deptID, q.subjectID, q.sbsID, passPercentage, timeLImit, immediate_result, show_correctans, unlocked, subbatchIDs, startDate, startTime, endDate, endTime, noOfQuestionsPerPage, is_question_shuffle, isFinalised, sr.batchID, sr.semID FROM quiz q inner join sbs_relation sr ON sr.sbsID = q.sbsID where quizID = ".$quizId.""; | |
| try { | |
| return $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| public function getQuizAttendedStudentDetails($quizId, $sortByColumn = 'rollNo') | |
| { | |
| $quizId = $this->realEscapeString($quizId); | |
| $sortByColumn = $this->realEscapeString($sortByColumn); | |
| $quiz = $this->getQuizDetailsById($quizId); | |
| if(empty($sortByColumn)){ | |
| try{ | |
| $sortByColumn = BatchService::getInstance()->getStudentSortByColumnOfABatch($quiz->batchID); | |
| }catch(\Exception $e){ | |
| $sortByColumn = 'rollNo'; | |
| } | |
| } | |
| if(!empty($quiz)) | |
| { | |
| $isCurrentSem = SemesterService::getInstance()->isCurrentSemester($quiz->batchID, $quiz->semID); | |
| $semDetails = SemesterService::getInstance()->getSemDetailsBySemId($quiz->semID); | |
| if(!empty($quiz->subbatchIDs)) | |
| { | |
| // subbatch | |
| if($isCurrentSem) | |
| { | |
| $sql = "select q.quizID, q.quizName, q.subjectID, q.sbsID, q.timeLimit, q.subbatchIDs, sta.studentID, sta.studentAccount, sta.studentName, sta.admissionNo, sta.regNo, sta.rollNo, qqq.quizquestID, qqq.questionID, qqq.question_order,qsq.question, qqqsta.answerID, qqqsta.answer, qqqsta.point FROM quiz q | |
| INNER JOIN | |
| sbs_relation sr ON sr.sbsID = q.sbsID | |
| AND sr.subjectID = q.subjectID | |
| inner join subbatches sub on find_in_set(sub.subbatchID, q.subbatchIDs) and sub.batchID = sr.batchID inner join | |
| subbatch_student ss on sub.subbatchID = ss.subbatchID inner join | |
| studentaccount sta ON sta.batchID = sr.batchID and ss.studentID = sta.studentID | |
| inner join semesters sem on sem.semID = sta.joiningSemId | |
| left join quiz_quiz_questions qqq ON qqq.quizID = q.quizID | |
| left join quiz_subject_questions qsq on qsq.questionID = qqq.questionID | |
| left join quiz_studentanswer qs ON qs.studentID = sta.studentID | |
| AND qs.quizquestID = qqq.quizquestID | |
| left join quiz_quiz_question_answers qqqsta on qqqsta.answerID = qs.answerID and qqqsta.quizquestID = qs.quizquestID | |
| LEFT JOIN | |
| quiz_student_answer_starttime qsas ON qsas.studentID = sta.studentID | |
| AND qsas.quizID = q.quizID where q.quizID = ".$quizId." and sem.orderNo <= ".$semDetails->orderNo." order by sta.$sortByColumn, qqq.questionID;"; | |
| } | |
| else | |
| { | |
| $sql = "select q.quizID, q.quizName, q.subjectID, q.sbsID, q.timeLimit, q.subbatchIDs, sta.studentID, sta.studentAccount, sta.studentName, sta.admissionNo, sta.regNo, sta.rollNo, qqq.quizquestID, qqq.questionID, qqq.question_order,qsq.question, qqqsta.answerID, qqqsta.answer, qqqsta.point FROM quiz q | |
| INNER JOIN | |
| sbs_relation sr ON sr.sbsID = q.sbsID | |
| AND sr.subjectID = q.subjectID | |
| inner join subbatches sub on find_in_set(sub.subbatchID, q.subbatchIDs) and sub.batchID = sr.batchID inner join | |
| subbatch_student ss on sub.subbatchID = ss.subbatchID inner join | |
| studentaccount sta ON ss.studentID = sta.studentID | |
| inner join semesters sem on sem.semID = sta.joiningSemId | |
| left join quiz_quiz_questions qqq ON qqq.quizID = q.quizID | |
| left join quiz_subject_questions qsq on qsq.questionID = qqq.questionID | |
| left join quiz_studentanswer qs ON qs.studentID = sta.studentID | |
| AND qs.quizquestID = qqq.quizquestID | |
| left join quiz_quiz_question_answers qqqsta on qqqsta.answerID = qs.answerID and qqqsta.quizquestID = qs.quizquestID | |
| LEFT JOIN | |
| quiz_student_answer_starttime qsas ON qsas.studentID = sta.studentID | |
| AND qsas.quizID = q.quizID where q.quizID = ".$quizId." and sem.orderNo <= ".$semDetails->orderNo." and sta.studentID in (select sa.studentID from studentaccount sa inner join batches ba on sa.batchID = ba.batchID inner join semesters joinedSem on sa.joiningSemId = joinedSem.semID where ba.batchID = ".$quiz->batchID." and joinedSem.orderNo <= ".$semDetails->orderNo." union select sa.studentID from failed_students fs left join studentaccount sa on fs.studentID= sa.studentID inner join semesters fsem on fsem.semID = fs.failedInSemester inner join semesters joinedSem on sa.joiningSemId = joinedSem.semID where previousBatch = ".$quiz->batchID." and fsem.orderNo > ".$semDetails->orderNo." and joinedSem.orderNo <= ".$semDetails->orderNo.") order by sta.$sortByColumn, qqq.questionID;"; | |
| } | |
| } | |
| else | |
| { | |
| if($isCurrentSem) | |
| { | |
| $sql = "select q.quizID, q.quizName, q.subjectID, q.sbsID, q.timeLimit, q.subbatchIDs, sta.studentID, sta.studentAccount, sta.studentName, sta.admissionNo, sta.regNo, sta.rollNo, qqq.quizquestID, qqq.questionID, qqq.question_order,qsq.question, qqqsta.answerID, qqqsta.answer, qqqsta.point FROM quiz q | |
| INNER JOIN | |
| sbs_relation sr ON sr.sbsID = q.sbsID | |
| AND sr.subjectID = q.subjectID | |
| inner join | |
| studentaccount sta ON sta.batchID = sr.batchID | |
| inner join semesters sem on sem.semID = sta.joiningSemId | |
| left join quiz_quiz_questions qqq ON qqq.quizID = q.quizID | |
| left join quiz_subject_questions qsq on qsq.questionID = qqq.questionID | |
| left join quiz_studentanswer qs ON qs.studentID = sta.studentID | |
| AND qs.quizquestID = qqq.quizquestID | |
| left join quiz_quiz_question_answers qqqsta on qqqsta.answerID = qs.answerID and qqqsta.quizquestID = qs.quizquestID | |
| LEFT JOIN | |
| quiz_student_answer_starttime qsas ON qsas.studentID = sta.studentID | |
| AND qsas.quizID = q.quizID where q.quizID = ".$quizId." and sem.orderNo <= ".$semDetails->orderNo." order by sta.$sortByColumn, qqq.questionID"; | |
| } | |
| else | |
| { | |
| $sql = "select * from (select q.quizID, q.quizName, q.subjectID, q.sbsID, q.timeLimit, q.subbatchIDs, sta.studentID, sta.studentAccount, sta.studentName, sta.admissionNo, sta.regNo, sta.rollNo, qqq.quizquestID, qqq.questionID, qqq.question_order,qsq.question, qqqsta.answerID, qqqsta.answer, qqqsta.point FROM quiz q | |
| INNER JOIN | |
| sbs_relation sr ON sr.sbsID = q.sbsID | |
| AND sr.subjectID = q.subjectID | |
| inner join | |
| studentaccount sta ON sta.batchID = sr.batchID | |
| inner join semesters sem on sem.semID = sta.joiningSemId | |
| left join quiz_quiz_questions qqq ON qqq.quizID = q.quizID | |
| left join quiz_subject_questions qsq on qsq.questionID = qqq.questionID | |
| left join quiz_studentanswer qs ON qs.studentID = sta.studentID | |
| AND qs.quizquestID = qqq.quizquestID | |
| left join quiz_quiz_question_answers qqqsta on qqqsta.answerID = qs.answerID and qqqsta.quizquestID = qs.quizquestID | |
| LEFT JOIN | |
| quiz_student_answer_starttime qsas ON qsas.studentID = sta.studentID | |
| AND qsas.quizID = q.quizID where q.quizID = ".$quizId." and sem.orderNo <= ".$semDetails->orderNo." union select q.quizID, q.quizName, q.subjectID, q.sbsID, q.timeLimit, q.subbatchIDs, sta.studentID, sta.studentAccount, sta.studentName, sta.admissionNo, sta.regNo, sta.rollNo, qqq.quizquestID, qqq.questionID, qqq.question_order,qsq.question, qqqsta.answerID, qqqsta.answer, qqqsta.point FROM quiz q | |
| INNER JOIN | |
| sbs_relation sr ON sr.sbsID = q.sbsID | |
| AND sr.subjectID = q.subjectID | |
| inner join | |
| failed_students fs on fs.previousBatch = sr.batchID inner join studentaccount sta ON sta.studentID = fs.studentID | |
| inner join semesters fsem on fs.failedInSemester = fsem.semID | |
| inner join semesters sem on sem.semID = sta.joiningSemId | |
| left join quiz_quiz_questions qqq ON qqq.quizID = q.quizID | |
| left join quiz_subject_questions qsq on qsq.questionID = qqq.questionID | |
| left join quiz_studentanswer qs ON qs.studentID = sta.studentID | |
| AND qs.quizquestID = qqq.quizquestID | |
| left join quiz_quiz_question_answers qqqsta on qqqsta.answerID = qs.answerID and qqqsta.quizquestID = qs.quizquestID | |
| LEFT JOIN | |
| quiz_student_answer_starttime qsas ON qsas.studentID = sta.studentID | |
| AND qsas.quizID = q.quizID where q.quizID = ".$quizId." and sem.orderNo <= ".$semDetails->orderNo." and fs.previousBatch = ".$quiz->batchID." and fsem.orderNo > ".$semDetails->orderNo." ) as students order by $sortByColumn, questionID"; | |
| } | |
| } | |
| } | |
| try { | |
| return $this->executeQueryForList($sql, $this->mapper[QuizAndSurveyServiceMapper::GET_QUIZ_ATTENDED_STUDENTS]); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| public function getQuizQuestionsAndAnswersByQuizid($quizId) | |
| { | |
| $quizId = $this->realEscapeString($quizId); | |
| $sql = "SELECT qsq.questionID, qsq.question, answerID, answer, point FROM quiz_subject_questions qsq inner join quiz_quiz_questions qqq on qsq.questionID =qqq.questionID left join quiz_quiz_question_answers qqqa on qqqa.quizquestID = qqq.quizquestID where qqq.quizID = ".$quizId." order by qqq.question_order,qsq.questionID, answerID"; | |
| try { | |
| return $this->executeQueryForList($sql, $this->mapper[QuizAndSurveyServiceMapper::GET_QUIZ_QUESTIONS]); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| public function getTotalScoreOfAStudentInAQuiz($studentId, $quizId){ | |
| $quizId = $this->realEscapeString($quizId); | |
| $studentId = $this->realEscapeString($studentId); | |
| $totalpoint = null; | |
| $questionIdsList = []; | |
| $sql = "SELECT quizquestID as questionId from quiz_quiz_questions where quizID=$quizId"; | |
| $questionIdsList = $this->executeQueryForList($sql); | |
| foreach($questionIdsList as $quizquestID) | |
| { | |
| $sql="SELECT sum(t2.point) as totalPoint from quiz_studentanswer t1, quiz_quiz_question_answers t2 where t1.quizquestID=\"$quizquestID->questionId\" and t1.studentID=\"$studentId\" and t2.answerID = t1.answerID"; | |
| $tempTotalPoint = $this->executeQueryForObject($sql)->totalPoint; | |
| if($tempTotalPoint != null){ | |
| $totalpoint += $tempTotalPoint; | |
| } | |
| } | |
| if($totalpoint<0) | |
| { | |
| $totalpoint=0; | |
| } | |
| return $totalpoint; | |
| } | |
| /** | |
| * Undocumented function | |
| * | |
| * @param [type] $batchID | |
| * @param [type] $semID | |
| * @param [type] $subjectId | |
| * @param [type] $staffID | |
| * @return void | |
| */ | |
| public function getAllQuizOfAStaff($batchID, $semID, $subjectId, $staffID){ | |
| $batchID = $this->realEscapeString($batchID); | |
| $semID = $this->realEscapeString($semID); | |
| $subjectId = $this->realEscapeString($subjectId); | |
| $staffID = $this->realEscapeString($staffID); | |
| $batchDetails = BatchService::getInstance()->getBatchDetails($batchID); | |
| $sql ="select t1.sbsID, t2.deptID from sbs_relation t1, batches t2 where t1.staffID='$staffID' and t1.subjectID='$subjectId' and t1.batchID='$batchID' and t2.batchID = t1.batchID and t1.semID = ".$semID.""; | |
| try{ | |
| $sbsId = $this->executeQueryForObject($sql)->sbsID; | |
| }catch(\Exception $e){ | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| $sql = "SELECT quizID, quizName, quizDesc, passPercentage, timeLImit, immediate_result, show_correctans, unlocked, subbatchIDs, startDate, startTime, endDate, endTime, noOfQuestionsPerPage FROM quiz where deptID=\"$batchDetails->deptId\" and subjectID=\"$subjectId\" and sbsID=\"$sbsId\" ORDER BY quizID desc"; | |
| try{ | |
| $quizList = $this->executeQueryForList($sql); | |
| }catch(\Exception $e){ | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $quizList; | |
| } | |
| public function getTotalPointsOfAQuiz($quizId){ | |
| $quizId = $this->realEscapeString($quizId); | |
| $sql = "SELECT quizquestID as questionId from quiz_quiz_questions where quizID=$quizId"; | |
| $questionIdsList = $this->executeQueryForList($sql); | |
| foreach($questionIdsList as $questionId){ | |
| $tempPoint = 0; | |
| $sql = "select max(point) as maxPoint from quiz_quiz_question_answers where quizquestID=$questionId->questionId"; | |
| $maxpoint += $this->executeQueryForObject($sql)->maxPoint; | |
| } | |
| return $maxpoint; | |
| } | |
| /** | |
| * Undocumented function | |
| * | |
| * @param [type] $batchID | |
| * @param [type] $semID | |
| * @param [type] $subjectId | |
| * @param integer $subbatchID | |
| * @param string $sortByColumn | |
| * @return void | |
| */ | |
| public function getAllStudentMarksAndCoReportOfABatchAndSemester($batchID, $semID, $subjectId, $staffID, $subbatchID = 0, $sortByColumn = "rollNo"){ | |
| $batchID = $this->realEscapeString($batchID); | |
| $semID = $this->realEscapeString($semID); | |
| $subjectId = $this->realEscapeString($subjectId); | |
| $staffID = $this->realEscapeString($staffID); | |
| $subbatchID = $this->realEscapeString($subbatchID); | |
| $sortByColumn = $this->realEscapeString($sortByColumn); | |
| $batchDetails = BatchService::getInstance()->getBatchDetails($batchID); | |
| $sql ="select t1.sbsID, t2.deptID from sbs_relation t1, batches t2 where t1.staffID='$staffID' and t1.subjectID='$subjectId' and t1.batchID='$batchID' and t2.batchID = t1.batchID and t1.semID = ".$semID.""; | |
| $sbsId = $this->executeQueryForObject($sql)->sbsID; | |
| $sql = "SELECT quizID, quizName, quizDesc, passPercentage, timeLImit, immediate_result, show_correctans, unlocked, subbatchIDs, startDate, startTime, endDate, endTime, noOfQuestionsPerPage FROM quiz where deptID=\"$batchDetails->deptId\" and subjectID=\"$subjectId\" and sbsID=\"$sbsId\" ORDER BY quizID desc"; | |
| $quizList = $this->executeQueryForList($sql); | |
| try{ | |
| $studentList = StudentService::getInstance()->getAllStudentsOfABatchByBatchIdSemIdAndSubbatchId($batchID, $semID, $subbatchID, $sortByColumn); | |
| }catch(\Exception $e){ | |
| $studentList = null; | |
| } | |
| if(empty($studentList)){ | |
| return null; | |
| } | |
| foreach ($studentList as $student) { | |
| $student->quizes = []; | |
| foreach($quizList as $quiz){ | |
| $student->quizes[$quiz->quizID] = new \StdClass(); | |
| $student->quizes[$quiz->quizID]->totalPoint = $this->getTotalScoreOfAStudentInAQuiz($student->studentID, $quiz->quizID); | |
| $student->quizes[$quiz->quizID]->coList = []; | |
| $condition = " and t2.quizID = ". $quiz->quizID; | |
| $studentID = $student->studentID; | |
| $sql_type = "SELECT t1.quizquestID, t2.quizID from quiz_studentanswer t1,quiz_quiz_questions t2 WHERE t1.quizquestID = t2.quizquestID AND t1.studentID = '". $studentID . "' " . $condition . ""; | |
| $res_type = $this->executeQueryForList($sql_type); | |
| if (!empty($res_type)) { | |
| foreach ($res_type as $row_type) { | |
| $quizquestID = $row_type->quizquestID; | |
| $quizID_tmp = $row_type->quizID; | |
| $sql_mark = "SELECT t2.point,t3.nba_course_outcome_id,t3.nba_course_outcome_value,t4.quizID,t3.quiz_questionID FROM quiz_studentanswer t1,quiz_quiz_question_answers t2,nba_quiz_question_co_relation t3,quiz_quiz_questions t4 WHERE t1.quizquestID = t4.quizquestID AND t2.quizquestID = t4.quizquestID AND t3.quiz_questionID = t4.quizquestID AND t1.answerID = t2.answerID AND t1.quizquestID = t2.quizquestID AND t2.quizquestID = t3.quiz_questionID AND t1.studentID = \"" . $studentID . "\" AND t1.quizquestID = \"" . $quizquestID . "\" AND t1.quizquestID = t3.quiz_questionID"; | |
| $result_mark = $this->executeQueryForList($sql_mark); | |
| if (!empty($result_mark)) { | |
| foreach ($result_mark as $row_mark) { | |
| $mark_obtained = $row_mark->point; | |
| $nba_course_outcome_id = $row_mark->nba_course_outcome_id; | |
| $course_outcome_value = $row_mark->nba_course_outcome_value; | |
| $course_outcome_value = $course_outcome_value / 100; | |
| $QUIZID = $row_mark->quizID; | |
| $quiz_questionID = $row_mark->quiz_questionID; | |
| $sql_maxpoint = "SELECT max(t1.point)as maxPoint FROM quiz_quiz_question_answers t1,quiz_quiz_questions t2 WHERE t1.quizquestID = t2.quizquestID AND t1.quizquestID = t2.quizquestID " . $condition . " AND t2.quizquestID = \"" . $quiz_questionID . "\""; | |
| $maxpoint = null; | |
| try{ | |
| $maxpoint = $this->executeQueryForObject($sql_maxpoint)->maxPoint; | |
| }catch(\Exception $e){ | |
| $maxpoint = null; | |
| } | |
| $sql_per_value = "SELECT sum(t1.nba_course_outcome_value)/100 as coValue FROM nba_quiz_question_co_relation t1,quiz_quiz_questions t2 WHERE t1.quiz_questionID = t2.quizquestID AND t1.nba_course_outcome_id = \"" . $nba_course_outcome_id . "\" " . $condition . " and t2.quizID = \"" . $QUIZID . "\""; | |
| $per_percent = $this->executeQueryForObject($sql_per_value)->coValue; | |
| if ($mark_obtained != 0.000) { | |
| $percentage = ($mark_obtained / $maxpoint) * $course_outcome_value; | |
| $exactValue = ($percentage / $per_percent) * 100; | |
| if($per_percent == 0){ | |
| $exactValue = 0; | |
| } | |
| $student->quizes[$quiz->quizID]->coList[$nba_course_outcome_id]->coObtained += $exactValue; | |
| } else { | |
| $student->quizes[$quiz->quizID]->coList[$nba_course_outcome_id]->coObtained += 0; | |
| } | |
| } | |
| } | |
| //check if this student has left this question unattempted | |
| } | |
| } | |
| } | |
| } | |
| return $studentList; | |
| } | |
| public function getQuizAttendedStudentList($quizId, $subbatchID = 0) | |
| { | |
| $quizId = $this->realEscapeString($quizId); | |
| $subbatchID = $this->realEscapeString($subbatchID); | |
| if($subbatchID==0){ | |
| $sql = "select * from quiz_student_answer_starttime where quizID = '$quizId' and is_attended =1"; | |
| }else{ | |
| $sql = "select * from quiz_student_answer_starttime qsas inner join subbatch_student ss ON (qsas.studentID = ss.studentID AND ss.subbatchID = '$subbatchID' ) where quizID = '$quizId' and is_attended =1"; | |
| } | |
| try { | |
| return $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| public function getAllQuizzesBySbsId($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| if(!empty($request->sbsIds)){ | |
| $condition = " and qa.sbsID in (".implode(',',$request->sbsIds).") "; | |
| }else{ | |
| $condition = " and qa.sbsID = ".$request->sbsId." "; | |
| } | |
| $sql = "SELECT qa.quizID,qa.quizName,qa.quizDesc,qa.passPercentage,qa.timeLImit,CONCAT(qa.startDate, ' ', qa.startTime) AS startDate,CONCAT(qa.endDate, ' ', qa.endTime) AS endDate,COUNT(std.studentID) AS totalStudents,GROUP_CONCAT(DISTINCT(json_object('batchId',sbs.batchID,'batchName',bat.batchName,'sbsId',sbs.sbsID))) AS identifyingContext,COUNT(is_attended) AS attended,'OldQuiz' AS type, if(sbs.semID = bat.semID,1,0) as currentBatch | |
| FROM sbs_relation sbs | |
| INNER JOIN batches bat ON bat.batchID = sbs.batchID | |
| INNER JOIN quiz qa ON qa.sbsID = sbs.sbsID and sbs.subjectID = qa.subjectID and qa.deptID = bat.deptID | |
| INNER JOIN studentaccount std ON std.batchID = sbs.batchID | |
| LEFT JOIN quiz_student_answer_starttime qs3 ON qs3.quizID = qa.quizID AND std.studentID = qs3.studentID | |
| WHERE qa.subbatchIDs = 0 $condition | |
| GROUP BY qa.quizID | |
| UNION | |
| SELECT qa.quizID,qa.quizName,qa.quizDesc,qa.passPercentage,qa.timeLImit,CONCAT(qa.startDate, ' ', qa.startTime) AS startDate,CONCAT(qa.endDate, ' ', qa.endTime) AS endDate,COUNT(distinct sstd.studentID) AS totalStudents,GROUP_CONCAT(DISTINCT(json_object('batchId',sbs.batchID,'batchName',bat.batchName,'sbsId',sbs.sbsID))) AS identifyingContext,COUNT(is_attended) AS attended,'OldQuiz' AS type,if(sbs.semID = bat.semID,1,0) as currentBatch | |
| FROM sbs_relation sbs | |
| INNER JOIN quiz qa ON qa.sbsID = sbs.sbsID and sbs.subjectID = qa.subjectID | |
| INNER JOIN batches bat on bat.batchID = sbs.batchID | |
| INNER JOIN subbatch_student sstd ON FIND_IN_SET(sstd.subbatchID, qa.subbatchIDs) | |
| INNER JOIN studentaccount std on std.studentID = sstd.studentID and std.batchID = sbs.batchID | |
| LEFT JOIN quiz_student_answer_starttime qs3 ON qs3.quizID = qa.quizID AND sstd.studentID = qs3.studentID | |
| WHERE sstd.studentID IS NOT NULL AND qa.subbatchIDs <> 0 $condition | |
| GROUP BY qa.quizID;"; try { | |
| return $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| public function studentMarksDetails($quizID) | |
| { | |
| $quiz = $this->getQuizDetailsById($quizID); | |
| if($quiz->subbatchIDs) | |
| { | |
| $sql = "select q.quizID,q.subjectID,sta.studentID, sta.studentName,sem.semName, sum(qqqsta.point) as marks,count(qsas.is_attended) as isAttended FROM quiz q | |
| INNER JOIN | |
| sbs_relation sr ON sr.sbsID = q.sbsID | |
| AND sr.subjectID = q.subjectID | |
| inner join subbatches sub on find_in_set(sub.subbatchID, q.subbatchIDs) and sub.batchID = sr.batchID | |
| inner join subbatch_student ss on sub.subbatchID = ss.subbatchID | |
| inner join studentaccount sta ON sta.batchID = sr.batchID and ss.studentID = sta.studentID | |
| inner join semesters sem on sem.semID = sr.semID | |
| left join quiz_quiz_questions qqq ON qqq.quizID = q.quizID | |
| left join quiz_subject_questions qsq on qsq.questionID = qqq.questionID | |
| left join quiz_studentanswer qs ON qs.studentID = sta.studentID AND qs.quizquestID = qqq.quizquestID | |
| left join quiz_quiz_question_answers qqqsta on qqqsta.answerID = qs.answerID and qqqsta.quizquestID = qs.quizquestID | |
| LEFT JOIN quiz_student_answer_starttime qsas ON qsas.studentID = sta.studentID | |
| AND qsas.quizID = q.quizID where q.quizID = 9 | |
| group by sta.studentID | |
| order by sta.rollNo, qqq.questionID;"; | |
| } | |
| else | |
| { | |
| $sql = "select q.quizID,q.subjectID,sta.studentID, sta.studentName,bat.batchName,sem.semName, sum(qqqsta.point) as marks,count(qsas.is_attended) as isAttended FROM quiz q | |
| INNER JOIN sbs_relation sr ON sr.sbsID = q.sbsID AND sr.subjectID = q.subjectID | |
| inner join semesters sem on sem.semID = sr.semID | |
| inner join studentaccount sta ON sta.batchID = sr.batchID | |
| inner join batches bat on bat.batchID = sr.batchID | |
| left join quiz_quiz_questions qqq ON qqq.quizID = q.quizID | |
| left join quiz_subject_questions qsq on qsq.questionID = qqq.questionID | |
| left join quiz_studentanswer qs ON qs.studentID = sta.studentID | |
| AND qs.quizquestID = qqq.quizquestID | |
| left join quiz_quiz_question_answers qqqsta on qqqsta.answerID = qs.answerID and qqqsta.quizquestID = qs.quizquestID | |
| LEFT JOIN quiz_student_answer_starttime qsas ON qsas.studentID = sta.studentID | |
| AND qsas.quizID = q.quizID where q.quizID = $quizID | |
| group by sta.studentID | |
| order by sta.rollNo, qqq.questionID;"; | |
| } | |
| try { | |
| return $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * get consilodated quiz&survey report | |
| * @param $request | |
| * @return ArrayList | |
| * @throws ProfessionalException | |
| */ | |
| public function getConsolidatedQuizReport($request){ | |
| $sql = ""; | |
| $sql = "SELECT q.quizID,q.quizName,q.subjectID,s.subjectName,sa.staffName,sta.studentID,sta.rollNo,sta.regNo, sta.studentName,bat.batchName,sem.semName, sum(qqqsta.point) as markObtained,count(qsas.is_attended) as isAttended ,q.startDate ,q.endDate FROM quiz q | |
| INNER JOIN sbs_relation sr ON sr.sbsID = q.sbsID AND sr.subjectID = q.subjectID AND sr.semID = $request->semId | |
| INNER JOIN semesters sem ON sem.semID = sr.semID | |
| INNER JOIN studentaccount sta ON sta.batchID = sr.batchID | |
| INNER JOIN batches bat ON bat.batchID = sr.batchID | |
| LEFT JOIN quiz_quiz_questions qqq ON qqq.quizID = q.quizID | |
| LEFT JOIN quiz_subject_questions qsq ON qsq.questionID = qqq.questionID | |
| LEFT JOIN quiz_studentanswer qs ON qs.studentID = sta.studentID | |
| AND qs.quizquestID = qqq.quizquestID | |
| LEFT JOIN quiz_quiz_question_answers qqqsta ON qqqsta.answerID = qs.answerID and qqqsta.quizquestID = qs.quizquestID | |
| LEFT JOIN quiz_student_answer_starttime qsas ON qsas.studentID = sta.studentID | |
| AND qsas.quizID = q.quizID | |
| LEFT JOIN subjects s ON s.subjectID = sr.subjectID | |
| LEFT JOIN staffaccounts sa ON sa.staffID = sr.staffID | |
| where bat.batchID=$request->batchId "; | |
| if($request->subjectId && $request->staffId){ | |
| $sql_sbs = "SELECT sbs.sbsID, b.deptID from sbs_relation sbs inner join batches b on b.batchID = sbs.batchID where sbs.staffID=$request->staffId and sbs.subjectID=$request->subjectId and sbs.batchID=$request->batchId and sbs.semID = $request->semId"; | |
| $sbsId = $this->executeQueryForObject($sql_sbs)->sbsID; | |
| if($sbsId){ | |
| $sql .=" AND q.subjectID=$request->subjectId and q.sbsID=$sbsId"; | |
| } | |
| else{ | |
| return false; | |
| } | |
| } | |
| if($request->fromDate && $request->toDate){ | |
| $sql .=" group by q.quizID, sta.studentID HAVING q.startDate >= '$request->fromDate' AND q.endDate <= '$request->toDate' order by sta.rollNo, q.startDate"; | |
| } | |
| else{ | |
| $sql .=" group by q.quizID, sta.studentID order by sta.rollNo, q.startDate"; | |
| } | |
| try { | |
| //$test= $this->executeQueryForList($sql); | |
| return $this->executeQueryForList($sql, $this->mapper[QuizAndSurveyServiceMapper::GET_QUIZ_REPORT]); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * get subect-wise quizzes | |
| * @param $request | |
| * @return ArrayList | |
| * @throws ProfessionalException | |
| */ | |
| public function getQuizzesSubectwise($request){ | |
| $sql=" | |
| SELECT q.quizID,q.quizName,q.startDate,q.subjectID,s.subjectName,sa.staffName,bat.batchName,sem.semName, q.endDate FROM quiz q | |
| INNER JOIN sbs_relation sr ON sr.sbsID = q.sbsID AND sr.subjectID = q.subjectID | |
| INNER JOIN semesters sem ON sem.semID = sr.semID | |
| INNER JOIN batches bat ON bat.batchID = sr.batchID | |
| LEFT JOIN subjects s ON s.subjectID = sr.subjectID | |
| LEFT JOIN staffaccounts sa ON sa.staffID = sr.staffID | |
| where bat.batchID=$request->batchId and sr.semID = $request->semId "; | |
| if($request->staffId){ | |
| $sql .= " AND sa.staffID = $request->staffId"; | |
| } | |
| if($request->subjectId){ | |
| $sql .= " AND s.subjectID = $request->subjectId"; | |
| } | |
| if($request->fromDate && $request->toDate){ | |
| $sql .=" group by q.quizID HAVING q.startDate >= '$request->fromDate' AND q.endDate <= '$request->toDate' order by q.startDate"; | |
| } | |
| else{ | |
| $sql .= " group by q.quizID order by q.startDate"; | |
| } | |
| try { | |
| $result = $this->executeQueryForList($sql, $this->mapper[QuizAndSurveyServiceMapper::GET_SUBJECTWISE_QUIZZES]); | |
| return $result; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| public function getMaxQuestionsInQuiz($quizId){ | |
| try { | |
| //code... | |
| $sql1 = "SELECT count(quizquestID)+10 as maxOrderNumber from quiz_quiz_questions where quizID='".$quizId."';"; | |
| return $this->executeQueryForObject($sql1)->maxOrderNumber; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| } | |
| ?> |