Code Coverage |
||||||||||
Classes and Traits |
Functions and Methods |
Lines |
||||||||
| Total | |
0.00% |
0 / 1 |
|
0.00% |
0 / 27 |
CRAP | |
0.00% |
0 / 1075 |
| NbaCoService | |
0.00% |
0 / 1 |
|
0.00% |
0 / 27 |
30450.00 | |
0.00% |
0 / 1075 |
| __construct | |
0.00% |
0 / 1 |
2.00 | |
0.00% |
0 / 3 |
|||
| __clone | |
0.00% |
0 / 1 |
2.00 | |
0.00% |
0 / 2 |
|||
| getInstance | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 5 |
|||
| getAllCosBySubjectId | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 11 |
|||
| getAllCosBySubjectIdBatchIdAndSemId | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 13 |
|||
| getCosWeightageToPo | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 13 |
|||
| finaliseQuestionPaperCo | |
0.00% |
0 / 1 |
42.00 | |
0.00% |
0 / 30 |
|||
| finaliseQuizCo | |
0.00% |
0 / 1 |
42.00 | |
0.00% |
0 / 30 |
|||
| quizIsFinalised | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 15 |
|||
| getCOReportOfInternalExam | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 20 |
|||
| getCOReportOfAssignment | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 46 |
|||
| calculateTotalCOPercents | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 14 |
|||
| getSectionWiseValidQuestionIdsForCoCalculationOfAStudent | |
0.00% |
0 / 1 |
72.00 | |
0.00% |
0 / 92 |
|||
| getSectionWiseValidQuestionIdsForCoCalculationOfAStudentForAssignment | |
0.00% |
0 / 1 |
72.00 | |
0.00% |
0 / 90 |
|||
| getSectionWiseValidQuestionIdsForCoCalculationOfAStudentForOnlineExam | |
0.00% |
0 / 1 |
1260.00 | |
0.00% |
0 / 207 |
|||
| getAllAnalysisCategories | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 10 |
|||
| createNewCourseOutcome | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 10 |
|||
| updateCourseOutcome | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 10 |
|||
| deleteCourseOutcome | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 10 |
|||
| deleteCourseOutcomeByCoIdList | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 11 |
|||
| findIfCoPresentForASubject | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 15 |
|||
| getBatchIdsAndSemIdUsingPsId | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 11 |
|||
| getSubjectIdBySubbatchId | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 13 |
|||
| getCoDetailsOfOnlineExam | |
0.00% |
0 / 1 |
182.00 | |
0.00% |
0 / 73 |
|||
| calculateTotalCOPercentsOnlineExam | |
0.00% |
0 / 1 |
110.00 | |
0.00% |
0 / 37 |
|||
| getCoDetailsOfOnlineExamForSelectAllAnswerdAndMandatoryUnAswered | |
0.00% |
0 / 1 |
182.00 | |
0.00% |
0 / 72 |
|||
| getSectionWiseValidQuestionIdsSelectAllAnswerdAndMandatoryUnAsweredForCoCalculationOfAStudentForOnlineExam | |
0.00% |
0 / 1 |
1406.00 | |
0.00% |
0 / 212 |
|||
| <?php | |
| namespace com\linways\core\ams\professional\service\nba; | |
| ////hello | |
| use ArrayObject; | |
| use com\linways\core\ams\professional\service\BaseService; | |
| use com\linways\core\ams\professional\dto\nba\NBACourseOutcome; | |
| use com\linways\core\ams\professional\mapper\nba\NbaCoServiceMapper; | |
| use com\linways\core\ams\professional\exception\ProfessionalException; | |
| use com\linways\core\ams\professional\constant\SettingsConstants; | |
| use com\linways\core\ams\professional\service\CommonService; | |
| use com\linways\core\ams\professional\constant\nba\NbaMethod; | |
| class NbaCoService 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 = NbaCoServiceMapper::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; | |
| } | |
| public function getAllCosBySubjectId($subjectId) | |
| { | |
| $subjectId = $this->realEscapeString($subjectId); | |
| $sql = "select id, code, description, objective, subjectID as subjectId, staffID as staffId from nba_course_outcome WHERE subjectID = $subjectId"; | |
| $responseList = []; | |
| try { | |
| $responseList = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $responseList; | |
| } | |
| /** | |
| * Undocumented function | |
| * | |
| * @param [type] $subjectId | |
| * @param [type] $batchId | |
| * @param [type] $semId | |
| * @return <void></void> | |
| */ | |
| public function getAllCosBySubjectIdBatchIdAndSemId($subjectId, $batchId, $semId) | |
| { | |
| $subjectId = $this->realEscapeString($subjectId); | |
| $batchId = $this->realEscapeString($batchId); | |
| $semId = $this->realEscapeString($semId); | |
| $sql = "select id, code, objective, description,subjectID as subjectId, staffID as staffId,order_no as orderNo from nba_course_outcome WHERE subjectID = $subjectId AND batchID = $batchId AND semID = $semId"; | |
| $responseList = []; | |
| try { | |
| $responseList = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $responseList; | |
| } | |
| /** | |
| * Undocumented function | |
| * | |
| * @param [type] $poId | |
| * @param [type] $coId | |
| * @return int $weightage | |
| */ | |
| public function getCosWeightageToPo($poId, $coId) | |
| { | |
| $sql = ""; | |
| $weightage = null; | |
| $poId = $this->realEscapeString($poId); | |
| $coId = $this->realEscapeString($coId); | |
| $sql = "SELECT t2.weightage FROM nba_co_po_relation t1, nba_descriptor t2 WHERE t1.nba_course_outcome_id = $coId AND t1.nba_program_outcome_id = $poId AND t1.nba_descriptor_id = t2.id"; | |
| try { | |
| $weightage = $this->executeQueryForObject($sql)->weightage; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $weightage; | |
| } | |
| /** | |
| * for calulation of co values of question paper assigned cos | |
| * @param int $questionPaperId | |
| * @throws ProfessionalException | |
| */ | |
| public function finaliseQuestionPaperCo($questionPaperId) | |
| { | |
| //for distinct co details | |
| $sql ="SELECT nqc.id, nqc.nba_course_outcome_id, nq.mark, nqs.id as sectionId, if(nqs.no_of_questions_to_answer=0,nqs.total_questions,nqs.no_of_questions_to_answer) noOfQuestion FROM assessment_structure_question_co_relation nqc INNER JOIN assessment_structure_section_questions nsq ON nsq.id=nqc.assessment_structure_question_id INNER JOIN assessment_structure_section nqs ON nqs.id=nsq.assessment_structure_section_id INNER JOIN assessment_structure nqp ON nqp.id=nqs.assessment_structure_id INNER JOIN assessment_questions nq ON nq.id=nsq.assessment_question_id WHERE nqp.id=$questionPaperId order by nqc.nba_course_outcome_id"; | |
| $sql_co = "SELECT nqc.id, nqc.nba_course_outcome_id, nq.mark, nqs.id as sectionId, if(nqs.no_of_questions_to_answer=0,nqs.total_questions,nqs.no_of_questions_to_answer) noOfQuestion FROM assessment_structure_question_co_relation nqc INNER JOIN assessment_structure_section_questions nsq ON nsq.id=nqc.assessment_structure_question_id INNER JOIN assessment_structure_section nqs ON nqs.id=nsq.assessment_structure_section_id INNER JOIN assessment_structure nqp ON nqp.id=nqs.assessment_structure_id INNER JOIN assessment_questions nq ON nq.id=nsq.assessment_question_id WHERE nqp.id=$questionPaperId group by nqc.nba_course_outcome_id, nq.mark order by nqc.nba_course_outcome_id"; | |
| $maxTotal=[]; | |
| try { | |
| $coList = $this->executeQueryForList($sql_co); | |
| $coMapList = $this->executeQueryForList($sql); | |
| if(!empty($coList)) | |
| { | |
| foreach ($coList as $co) | |
| { | |
| if(empty($maxTotal[$co->nba_course_outcome_id])) | |
| { | |
| $maxTotal[$co->nba_course_outcome_id]=0; | |
| } | |
| $maxTotal[$co->nba_course_outcome_id] +=$co->mark*$co->noOfQuestion; | |
| } | |
| foreach ($coMapList as $co) | |
| { | |
| $value=($co->mark/$maxTotal[$co->nba_course_outcome_id])*100; | |
| $sql_update = "UPDATE assessment_structure_question_co_relation SET course_outcome_value='$value' WHERE id=$co->id"; | |
| $this->executeQueryForObject($sql_update); | |
| } | |
| } | |
| $sql_qpFlag = "UPDATE assessment_structure SET isFinalised=1 WHERE id=$questionPaperId"; | |
| $this->executeQueryForObject($sql_qpFlag); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * for calulation of co values of quiz | |
| * @param int $quizId | |
| * @throws ProfessionalException | |
| */ | |
| public function finaliseQuizCo($quizId) | |
| { | |
| $quizId = $this->realEscapeString($quizId); | |
| $sql ="select qcr.id, qcr.nba_course_outcome_id, (select max(point) from quiz_quiz_question_answers WHERE quizquestID=qcr.quiz_questionID) as maxPint from nba_quiz_question_co_relation qcr INNER JOIN quiz_quiz_questions qq ON qq.quizquestID=qcr.quiz_questionID WHERE qq.quizID=$quizId"; | |
| $maxTotal=[]; | |
| try { | |
| $maxTotal=[]; | |
| $coList = $this->executeQueryForList($sql); | |
| if(!empty($coList)) | |
| { | |
| foreach ($coList as $co) | |
| { | |
| if(empty($maxTotal[$co->nba_course_outcome_id])) | |
| { | |
| $maxTotal[$co->nba_course_outcome_id]=0; | |
| } | |
| $maxTotal[$co->nba_course_outcome_id] +=$co->maxPint; | |
| } | |
| foreach ($coList as $co) | |
| { | |
| $value=($co->maxPint/$maxTotal[$co->nba_course_outcome_id])*100; | |
| $sql_update = "UPDATE nba_quiz_question_co_relation SET nba_course_outcome_value='$value' WHERE id=$co->id"; | |
| $this->executeQueryForObject($sql_update); | |
| } | |
| $sql_qpFlag = "UPDATE quiz SET isFinalised=1 WHERE quizID=$quizId"; | |
| $this->executeQueryForObject($sql_qpFlag); | |
| } | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * check quiz co finalised or not | |
| * @param int $quizId | |
| * @throws ProfessionalException | |
| * @return boolean | |
| */ | |
| public function quizIsFinalised($quizId) | |
| { | |
| $quizId = $this->realEscapeString($quizId); | |
| $flag=FALSE; | |
| $sql ="SELECT isFinalised FROM quiz WHERE quizID=$quizId"; | |
| try { | |
| $isFinalised = $this->executeQueryForObject($sql)->isFinalised; | |
| if($isFinalised) | |
| { | |
| $flag=TRUE; | |
| } | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $flag; | |
| } | |
| /** | |
| * Undocumented function | |
| * | |
| * @param [type] $examId | |
| * @param [type] $studentId | |
| * @param [type] $subjectId | |
| * @return void | |
| */ | |
| public function getCOReportOfInternalExam($examId, $studentId, $subjectId) | |
| { | |
| $sql = ""; | |
| $examId = $this->realEscapeString($examId); | |
| $studentId = $this->realEscapeString($studentId); | |
| $subjectId = $this->realEscapeString($subjectId); | |
| $sql = "SELECT t1.assessment_structure_questions_id, | |
| t1.mark_obtained, | |
| t2.assessment_questions_id, | |
| t3.mark,t4.nba_course_outcome_id, | |
| qqcr.course_outcome_value, | |
| t6.id FROM assessment_student_marks t1 INNER JOIN assessment_structure_questions t2 ON t1.assessment_structure_questions_id = t2.id AND t1.studentID = '$studentId' AND t1.assessment_id = '$examId' AND t1.assessment_type = 'EXAM' INNER JOIN assessment_questions t3 ON t2.assessment_questions_id = t3.id INNER JOIN assessment_question_co_relation t4 ON t4.assessment_questions_id = t2.assessment_questions_id AND | |
| t3.id = t4.assessment_questions_id INNER JOIN exam t5 ON t1.assessment_id = t5.examID AND t5.subjectID = '$subjectId' INNER JOIN assessment_structure t6 ON t6.id = t2.assessment_structure_id AND | |
| t6.exam_type_id = t5.examTypeID INNER JOIN assessment_structure_section_questions sq ON sq.assessment_question_id = t2.assessment_questions_id INNER JOIN assessment_structure_section qps ON qps.id = sq.assessment_structure_section_id AND qps.assessment_structure_id = t6.id INNER JOIN assessment_structure_question_co_relation qqcr ON qqcr.assessment_structure_question_id = sq.id AND qqcr.nba_course_outcome_id = t4.nba_course_outcome_id"; | |
| try { | |
| $responseList = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $responseList; | |
| } | |
| public function getCOReportOfAssignment($assignmentId, $studentId, $subjectId) | |
| { | |
| $sql = ""; | |
| $assignmentId = $this->realEscapeString($assignmentId); | |
| $studentId = $this->realEscapeString($studentId); | |
| $subjectId = $this->realEscapeString($subjectId); | |
| $sql = "SELECT | |
| t1.assessment_structure_questions_id, | |
| t1.mark_obtained, | |
| t2.assessment_questions_id, | |
| t3.mark, | |
| t4.nba_course_outcome_id, | |
| qqcr.course_outcome_value, | |
| t6.id | |
| FROM | |
| assessment_student_marks t1 | |
| INNER JOIN | |
| assessment_structure_questions t2 ON t1.assessment_structure_questions_id = t2.id | |
| AND t1.studentID = '$studentId' | |
| AND t1.assessment_id = '$assignmentId' | |
| AND t1.assessment_type = 'ASSIGNMENT' | |
| INNER JOIN | |
| assessment_questions t3 ON t2.assessment_questions_id = t3.id | |
| INNER JOIN | |
| assessment_question_co_relation t4 ON t4.assessment_questions_id = t2.assessment_questions_id | |
| AND t3.id = t4.assessment_questions_id | |
| INNER JOIN | |
| batch_assignment t5 ON t1.assessment_id = t5.assignmentID | |
| AND t5.subjectID = '$subjectId' | |
| INNER JOIN | |
| assessment_structure t6 ON t6.id = t2.assessment_structure_id | |
| AND t6.assessment_key = t5.assiNu | |
| INNER JOIN | |
| assessment_structure_section_questions sq ON sq.assessment_question_id = t2.assessment_questions_id | |
| INNER JOIN | |
| assessment_structure_section qps ON qps.id = sq.assessment_structure_section_id | |
| AND qps.assessment_structure_id = t6.id | |
| INNER JOIN | |
| assessment_structure_question_co_relation qqcr ON qqcr.assessment_structure_question_id = sq.id | |
| AND qqcr.nba_course_outcome_id = t4.nba_course_outcome_id "; | |
| try { | |
| $responseList = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $responseList; | |
| } | |
| public function calculateTotalCOPercents($validQuestionIds, $questionPaperId){ | |
| $totalCoPercent = []; | |
| foreach($validQuestionIds as $questionId){ | |
| $sql = "SELECT qqcr.nba_course_outcome_id as coId,qqcr.course_outcome_value as value FROM assessment_structure_section qps INNER JOIN assessment_structure_section_questions sq ON sq.assessment_structure_section_id = qps.id AND qps.assessment_structure_id = '$questionPaperId' AND sq.assessment_question_id='$questionId' INNER JOIN assessment_structure_question_co_relation qqcr ON qqcr.assessment_structure_question_id = sq.id"; | |
| try { | |
| $coDetailsList = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| foreach($coDetailsList as $coDetails){ | |
| $totalCoPercent[$coDetails->coId] += $coDetails->value; | |
| } | |
| } | |
| return $totalCoPercent; | |
| } | |
| // public function getTotalCoPercent($course_outcome_question_paper_id, $nba_course_outcome_id) | |
| // { | |
| // $sql = ""; | |
| // $coContribution = 0; | |
| // $sql = "SELECT | |
| // qps.id, if(qps.no_of_questions_to_answer > 0, qps.no_of_questions_to_answer, qps.total_questions ) as totalValidQuestions | |
| // FROM | |
| // assessment_structure_question_co_relation qqcr | |
| // INNER JOIN | |
| // assessment_structure_section_questions sq ON qqcr.assessment_structure_question_id = sq.id | |
| // INNER JOIN | |
| // assessment_structure_section qps ON qps.id = sq.assessment_structure_section_id | |
| // WHERE qps.question_paper_id = '$course_outcome_question_paper_id' AND qqcr.nba_course_outcome_id = '$nba_course_outcome_id' GROUP BY qps.id"; | |
| // try { | |
| // $sectionDetails = $this->executeQueryForList($sql); | |
| // } catch (\Exception $e) { | |
| // throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| // } | |
| // foreach ($sectionDetails as $section) { | |
| // $sql = ""; | |
| // $sql = "SELECT | |
| // DISTINCT (qqcr.course_outcome_value)*$section->totalValidQuestions as contribution | |
| // FROM | |
| // assessment_structure_question_co_relation qqcr | |
| // INNER JOIN | |
| // assessment_structure_section_questions sq ON qqcr.assessment_structure_question_id = sq.id | |
| // INNER JOIN | |
| // assessment_structure_section qps ON qps.id = sq.assessment_structure_section_id | |
| // WHERE qps.question_paper_id = '$course_outcome_question_paper_id' AND qqcr.nba_course_outcome_id = '$nba_course_outcome_id' AND qps.id = $section->id"; | |
| // try { | |
| // $coContribution += $this->executeQueryForObject($sql)->contribution; | |
| // } catch (\Exception $e) { | |
| // throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| // } | |
| // } | |
| // return $coContribution/100; | |
| // } | |
| public function getSectionWiseValidQuestionIdsForCoCalculationOfAStudent($examId, $studentId) | |
| { | |
| $examId = $this->realEscapeString($examId); | |
| $studentId = $this->realEscapeString($studentId); | |
| $response = new \stdClass(); | |
| $coPoCourseWiseCalculationDisplayStyle = CommonService::getInstance()->getSettings(SettingsConstants::NBA, NbaMethod::OBE_CO_PO_CALCULATION_DISPlAY_STYLE_IN_TREE_NODE); | |
| $sql = ""; | |
| $sql = "SELECT | |
| section.id, section.section_name, section.no_of_questions_to_answer | |
| FROM | |
| assessment_structure_section section | |
| INNER JOIN | |
| assessment_structure qp ON (section.assessment_structure_id = qp.id) | |
| WHERE | |
| qp.id IN (SELECT | |
| t1.id AS questionPaperId | |
| FROM | |
| assessment_structure t1 INNER JOIN | |
| exam t2 ON t1.exam_type_id = t2.examTypeID AND t1.subjectID = t2.subjectID | |
| AND t1.batchID = t2.batchID | |
| AND t1.semID | |
| AND t2.semID | |
| INNER JOIN assessment_student_marks cosm ON (cosm.assessment_id = t2.examID AND cosm.studentID = $studentId AND cosm.assessment_type = 'EXAM') | |
| INNER JOIN assessment_structure_questions coqp ON coqp.id = cosm.assessment_structure_questions_id AND coqp.assessment_structure_id = t1.id | |
| WHERE | |
| t1.exam_type_id = t2.examTypeID | |
| AND t1.subjectID = t2.subjectID | |
| AND t1.batchID = t2.batchID | |
| AND t1.semID | |
| AND t2.semID | |
| AND t2.examID = $examId AND cosm.studentID = $studentId)"; | |
| try { | |
| $sectionDetails = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| foreach ($sectionDetails as $section) { | |
| if ($coPoCourseWiseCalculationDisplayStyle==NbaMethod::OBE_CO_PO_CALCULATION_COURSE_WISE_DISPlAY_AND_CALCULATION_TWO){ | |
| $sql = "SELECT | |
| section.assessment_structure_section_id, | |
| marks.studentID, | |
| qpquestion.assessment_questions_id, | |
| marks.assessment_structure_questions_id, | |
| marks.mark_obtained | |
| FROM | |
| assessment_structure_section_questions section | |
| INNER JOIN | |
| assessment_structure_questions qpquestion ON (section.assessment_question_id = qpquestion.assessment_questions_id) | |
| INNER JOIN | |
| assessment_student_marks marks ON (marks.assessment_structure_questions_id = qpquestion.id | |
| AND marks.assessment_id = $examId AND marks.assessment_type = 'EXAM') | |
| WHERE | |
| marks.studentID = $studentId | |
| AND section.assessment_structure_section_id = $section->id | |
| ORDER BY mark_obtained DESC | |
| "; | |
| try { | |
| $questionDetails = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| }else{ | |
| $sql = "SELECT | |
| section.assessment_structure_section_id, | |
| marks.studentID, | |
| qpquestion.assessment_questions_id, | |
| marks.assessment_structure_questions_id, | |
| marks.mark_obtained | |
| FROM | |
| assessment_structure_section_questions section | |
| INNER JOIN | |
| assessment_structure_questions qpquestion ON (section.assessment_question_id = qpquestion.assessment_questions_id) | |
| INNER JOIN | |
| assessment_student_marks marks ON (marks.assessment_structure_questions_id = qpquestion.id | |
| AND marks.assessment_id = $examId AND marks.assessment_type = 'EXAM') | |
| WHERE | |
| marks.studentID = $studentId | |
| AND section.assessment_structure_section_id = $section->id | |
| ORDER BY mark_obtained DESC | |
| " . ($section->no_of_questions_to_answer > 0 ? ' LIMIT ' . $section->no_of_questions_to_answer : ''); | |
| try { | |
| $questionDetails = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| foreach ($questionDetails as $question) { | |
| $response->nbaCourseOutcomeQuestionsIdList[] = $question->assessment_questions_id; | |
| $response->nbaCourseOutcomeQuestionPaperQuestionsIdList[] = $question->assessment_structure_questions_id; | |
| } | |
| } | |
| return $response; | |
| } | |
| public function getSectionWiseValidQuestionIdsForCoCalculationOfAStudentForAssignment($assignmentId, $studentId) | |
| { | |
| $assignmentId = $this->realEscapeString($assignmentId); | |
| $studentId = $this->realEscapeString($studentId); | |
| $response = new \stdClass(); | |
| $coPoCourseWiseCalculationDisplayStyle = CommonService::getInstance()->getSettings(SettingsConstants::NBA, NbaMethod::OBE_CO_PO_CALCULATION_DISPlAY_STYLE_IN_TREE_NODE); | |
| $sql = ""; | |
| $sql = "SELECT | |
| section.id, section.section_name, section.no_of_questions_to_answer | |
| FROM | |
| assessment_structure_section section | |
| INNER JOIN | |
| assessment_structure qp ON (section.assessment_structure_id = qp.id) | |
| WHERE | |
| qp.id IN (SELECT | |
| t1.id AS questionPaperId | |
| FROM | |
| assessment_structure t1 INNER JOIN | |
| batch_assignment t2 ON t1.assessment_key = t2.assiNu AND t1.subjectID = t2.subjectID | |
| AND t1.batchID = t2.batchID | |
| AND t1.semID | |
| AND t2.semID | |
| INNER JOIN assessment_student_marks cosm ON (cosm.assessment_id = t2.assignmentID AND cosm.studentID = $studentId AND cosm.assessment_type = 'ASSIGNMENT') | |
| INNER JOIN assessment_structure_questions coqp ON coqp.id = cosm.assessment_structure_questions_id AND coqp.assessment_structure_id = t1.id | |
| WHERE | |
| t1.assessment_key = t2.assiNu | |
| AND t1.subjectID = t2.subjectID | |
| AND t1.batchID = t2.batchID | |
| AND t1.semID | |
| AND t2.semID | |
| AND t2.assignmentID = $assignmentId AND cosm.studentID = $studentId)"; | |
| try { | |
| $sectionDetails = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| foreach ($sectionDetails as $section) { | |
| if ($coPoCourseWiseCalculationDisplayStyle==NbaMethod::OBE_CO_PO_CALCULATION_COURSE_WISE_DISPlAY_AND_CALCULATION_TWO){ | |
| $sql = "SELECT | |
| section.assessment_structure_section_id, | |
| $studentId as studentID, | |
| qpquestion.assessment_questions_id, | |
| qpquestion.id as assessment_structure_questions_id, | |
| marks.mark_obtained | |
| FROM | |
| assessment_structure_section_questions section | |
| INNER JOIN | |
| assessment_structure_questions qpquestion ON (section.assessment_question_id = qpquestion.assessment_questions_id) | |
| LEFT JOIN | |
| assessment_student_marks marks ON (marks.assessment_structure_questions_id = qpquestion.id | |
| AND marks.assessment_id = $assignmentId AND marks.studentID = $studentId AND marks.assessment_type = 'ASSIGNMENT') | |
| WHERE | |
| section.assessment_structure_section_id = $section->id | |
| ORDER BY mark_obtained DESC | |
| "; | |
| try { | |
| $questionDetails = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| }else{ | |
| $sql = "SELECT | |
| section.assessment_structure_section_id, | |
| $studentId as studentID, | |
| qpquestion.assessment_questions_id, | |
| qpquestion.id as assessment_structure_questions_id, | |
| marks.mark_obtained | |
| FROM | |
| assessment_structure_section_questions section | |
| INNER JOIN | |
| assessment_structure_questions qpquestion ON (section.assessment_question_id = qpquestion.assessment_questions_id) | |
| LEFT JOIN | |
| assessment_student_marks marks ON (marks.assessment_structure_questions_id = qpquestion.id | |
| AND marks.assessment_id = $assignmentId AND marks.studentID = $studentId AND marks.assessment_type = 'ASSIGNMENT') | |
| WHERE | |
| section.assessment_structure_section_id = $section->id | |
| ORDER BY mark_obtained DESC | |
| " . ($section->no_of_questions_to_answer > 0 ? ' LIMIT ' . $section->no_of_questions_to_answer : ''); | |
| try { | |
| $questionDetails = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| foreach ($questionDetails as $question) { | |
| $response->nbaCourseOutcomeQuestionsIdList[] = $question->assessment_questions_id; | |
| $response->nbaCourseOutcomeQuestionPaperQuestionsIdList[] = $question->assessment_structure_questions_id; | |
| } | |
| } | |
| return $response; | |
| } | |
| public function getSectionWiseValidQuestionIdsForCoCalculationOfAStudentForOnlineExam($oeExamId, $studentId) | |
| { | |
| $oeExamId = $this->realEscapeString($oeExamId); | |
| $studentId = $this->realEscapeString($studentId); | |
| $coPoCourseWiseCalculationDisplayStyle = CommonService::getInstance()->getSettings(SettingsConstants::NBA, NbaMethod::OBE_CO_PO_CALCULATION_DISPlAY_STYLE_IN_TREE_NODE); | |
| $response = new \stdClass(); | |
| $sql = ""; | |
| $sql = "SELECT | |
| section.id, section.name, section.properties | |
| FROM | |
| oe_exam_sections section | |
| INNER JOIN | |
| oe_exams oee ON (section.oe_exams_id = oee.id) | |
| WHERE | |
| oee.id = '$oeExamId'"; | |
| try { | |
| $sectionDetails = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| $response->nbaCourseOutcomeQuestionsIdList=array(); | |
| $response->totalMarkObtained=0; | |
| if(count($sectionDetails)){ | |
| foreach ($sectionDetails as $section) { | |
| $section->properties= json_decode($section->properties); | |
| if(property_exists($section->properties, "noOfQuestionsToAnswer")){ | |
| $conditions =""; | |
| $contexts =[]; | |
| if ( $section->id ) { | |
| $contexts[] = '"section":"'.$section->id.'"'; | |
| } | |
| $conditions .= " AND JSON_CONTAINS(oeq.properties, '{" . implode (",", $contexts ) . "}')"; | |
| if($section->properties->noOfQuestionsToAnswer > 0){ | |
| $sql ="SELECT | |
| oeq.id, | |
| oeua.user_id, | |
| oeq.oe_exams_id, | |
| oeq.id AS oe_exam_questions_id, | |
| oeq.properties, | |
| oeua.oe_exam_answers_id, | |
| oeua.answer, | |
| oea.point, | |
| oeum.mark, | |
| oeua.is_submitted, | |
| oeua.attended_duration | |
| FROM | |
| oe_exam_questions oeq | |
| LEFT JOIN | |
| oe_exam_user_answers oeua ON (oeq.oe_exams_id = oeua.oe_exams_id AND oeq.id = oeua.oe_exam_questions_id AND oeua.user_id = '$studentId') | |
| LEFT JOIN | |
| oe_exam_answers oea ON (oea.oe_exams_id = oeua.oe_exams_id AND oea.oe_exam_questions_id = oeua.oe_exam_questions_id AND oea.id = oeua.oe_exam_answers_id) | |
| LEFT JOIN | |
| oe_exam_user_mark oeum ON (oeum.oe_exams_id = oeq.oe_exams_id AND oeum.oe_exam_questions_id = oeq.id AND oeum.user_id = '$studentId') | |
| WHERE | |
| oeq.oe_exams_id = '$oeExamId' $conditions "; | |
| try { | |
| $questionDetails = $this->executeQueryForList($sql); | |
| $questionTotalMarks=[]; | |
| $questionDetailsNew = array(); | |
| foreach ($questionDetails as $k => $v) { | |
| $v->properties= json_decode($v->properties); | |
| $questionDetailsNew[$k] = clone $v; | |
| } | |
| foreach ($questionDetails as $question) { | |
| // $question->properties= json_decode($question->properties); | |
| $obj1 = new \stdClass; | |
| $obj1->subQuestions=[]; | |
| $obj1->totalMark=null; | |
| $obj1->questionId=$question->id; | |
| if(property_exists($question->properties, "hasSubQuestion") && $question->properties->hasSubQuestion=="1"){ | |
| foreach($questionDetailsNew as $questionNew){ | |
| if(property_exists($questionNew->properties, "mainQuestionId") && $questionNew->properties->mainQuestionId == $question->id ){ | |
| $obj1->subQuestions[] = $questionNew->id; | |
| if(is_numeric($questionNew->point) || is_numeric($questionNew->mark)){ | |
| if($questionNew->properties->type=="MCQ"){ | |
| $obj1->totalMark= $obj1->totalMark+$questionNew->point; | |
| }else{ | |
| $obj1->totalMark= $obj1->totalMark+$questionNew->mark; | |
| } | |
| } | |
| } | |
| } | |
| if($obj1->totalMark!==null){ | |
| $questionTotalMarks[] = $obj1; | |
| } | |
| }elseif((!property_exists($question->properties, "hasSubQuestion")||$question->properties->hasSubQuestion==false) && !(property_exists($question->properties, "isSubQuestion")|| $question->properties->isSubQuestion=="0")){ | |
| if($question->properties->type=="MCQ"){ | |
| $obj1->totalMark= $question->point; | |
| }else{ | |
| $obj1->totalMark= $question->mark; | |
| } | |
| $questionTotalMarks[] = $obj1; | |
| } | |
| } | |
| usort($questionTotalMarks, function($a, $b) { | |
| if($a->totalMark===$b->totalMark){ | |
| return 0; | |
| } | |
| return ($a->totalMark>$b->totalMark)?-1:1; | |
| }); | |
| if ($coPoCourseWiseCalculationDisplayStyle==NbaMethod::OBE_CO_PO_CALCULATION_COURSE_WISE_DISPlAY_AND_CALCULATION_TWO){ | |
| for($i=0; $i<count($questionTotalMarks); $i++) { | |
| if(count($questionTotalMarks[$i]->subQuestions)){ | |
| $response->nbaCourseOutcomeQuestionsIdList=array_merge( $response->nbaCourseOutcomeQuestionsIdList,$questionTotalMarks[$i]->subQuestions); | |
| $response->totalMarkObtained= $response->totalMarkObtained+ $questionTotalMarks[$i]->totalMark; | |
| }else{ | |
| $response->nbaCourseOutcomeQuestionsIdList[] =$questionTotalMarks[$i]->questionId; | |
| $response->totalMarkObtained= $response->totalMarkObtained+ $questionTotalMarks[$i]->totalMark; | |
| } | |
| } | |
| } else{ | |
| for($i=0; $i<$section->properties->noOfQuestionsToAnswer; $i++) { | |
| if(count($questionTotalMarks[$i]->subQuestions)){ | |
| $response->nbaCourseOutcomeQuestionsIdList=array_merge( $response->nbaCourseOutcomeQuestionsIdList,$questionTotalMarks[$i]->subQuestions); | |
| $response->totalMarkObtained= $response->totalMarkObtained+ $questionTotalMarks[$i]->totalMark; | |
| }else{ | |
| $response->nbaCourseOutcomeQuestionsIdList[] =$questionTotalMarks[$i]->questionId; | |
| $response->totalMarkObtained= $response->totalMarkObtained+ $questionTotalMarks[$i]->totalMark; | |
| } | |
| } | |
| } | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| }else{ | |
| $sql ="SELECT | |
| oeq.id, | |
| if(oeua.user_id, oeua.user_id, oeum.user_id) AS user_id, | |
| oeq.oe_exams_id, | |
| oeq.id AS oe_exam_questions_id, | |
| oeq.properties, | |
| oeua.oe_exam_answers_id, | |
| oeua.answer, | |
| if(oea.point, oea.point, oeum.mark) AS mark, | |
| oeua.is_submitted | |
| FROM | |
| oe_exam_questions oeq | |
| LEFT JOIN oe_exam_user_answers oeua ON ( | |
| oeq.oe_exams_id = oeua.oe_exams_id | |
| AND oeq.id = oeua.oe_exam_questions_id | |
| AND oeua.user_id = '$studentId' | |
| ) | |
| LEFT JOIN oe_exam_answers oea ON ( | |
| oea.oe_exams_id = oeua.oe_exams_id | |
| AND oea.oe_exam_questions_id = oeua.oe_exam_questions_id | |
| AND oea.id = oeua.oe_exam_answers_id | |
| ) | |
| LEFT JOIN oe_exam_user_mark oeum ON ( | |
| oeum.oe_exams_id = oeq.oe_exams_id | |
| AND oeum.oe_exam_questions_id = oeq.id | |
| AND oeum.user_id = '$studentId' | |
| ) | |
| WHERE | |
| oeq.oe_exams_id = '$oeExamId' | |
| AND ( oeum.id IS NOT NULL OR oeua.id IS NOT NULL) $conditions "; | |
| try { | |
| $questionDetails = $this->executeQueryForList($sql); | |
| foreach ($questionDetails as $question) { | |
| $response->nbaCourseOutcomeQuestionsIdList[] = $question->id; | |
| $response->totalMarkObtained= $response->totalMarkObtained+ $question->mark; | |
| } | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| } | |
| } | |
| }else{ | |
| $sql ="SELECT | |
| oeq.id, | |
| if(oeua.user_id, oeua.user_id, oeum.user_id) AS user_id, | |
| oeq.oe_exams_id, | |
| oeq.id AS oe_exam_questions_id, | |
| oeq.properties, | |
| oeua.oe_exam_answers_id, | |
| oeua.answer, | |
| if(oea.point, oea.point, oeum.mark) AS mark, | |
| oeua.is_submitted | |
| FROM | |
| oe_exam_questions oeq | |
| LEFT JOIN oe_exam_user_answers oeua ON ( | |
| oeq.oe_exams_id = oeua.oe_exams_id | |
| AND oeq.id = oeua.oe_exam_questions_id | |
| AND oeua.user_id = '$studentId' | |
| ) | |
| LEFT JOIN oe_exam_answers oea ON ( | |
| oea.oe_exams_id = oeua.oe_exams_id | |
| AND oea.oe_exam_questions_id = oeua.oe_exam_questions_id | |
| AND oea.id = oeua.oe_exam_answers_id | |
| ) | |
| LEFT JOIN oe_exam_user_mark oeum ON ( | |
| oeum.oe_exams_id = oeq.oe_exams_id | |
| AND oeum.oe_exam_questions_id = oeq.id | |
| AND oeum.user_id = '$studentId' | |
| ) | |
| WHERE | |
| oeq.oe_exams_id = '$oeExamId' | |
| AND ( oeum.id IS NOT NULL OR oeua.id IS NOT NULL) "; | |
| try { | |
| $questionDetails = $this->executeQueryForList($sql); | |
| foreach ($questionDetails as $question) { | |
| $response->nbaCourseOutcomeQuestionsIdList[] = $question->id; | |
| $response->totalMarkObtained= $response->totalMarkObtained+ $question->mark; | |
| } | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| return $response; | |
| } | |
| /** | |
| * Undocumented function | |
| * | |
| * @return void | |
| */ | |
| public function getAllAnalysisCategories(){ | |
| $sql = ""; | |
| $categories = []; | |
| $sql = "SELECT id, name, minimum_value as minimumValue, maximum_value as maximumValue FROM nba_co_report_analysis_categories order by order_no"; | |
| try { | |
| $categories = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $categories; | |
| } | |
| /** | |
| * Create new course outcome | |
| * | |
| * @param NBACourseOutcome $courseOutcome | |
| * @return void | |
| */ | |
| public function createNewCourseOutcome($courseOutcome){ | |
| $sql = ""; | |
| $courseOutcome = $this->realEscapeObject($courseOutcome); | |
| //description should be htmlencoded | |
| $sql = "INSERT INTO `nba_course_outcome` (`code`, `objective`, `description`, `subjectID`, `staffID`, `batchID`, `semID`, `order_no`) VALUES ('$courseOutcome->code', '$courseOutcome->objective', '$courseOutcome->description', '$courseOutcome->subjectId', '$courseOutcome->staffId', '$courseOutcome->batchId', '$courseOutcome->semId', '$courseOutcome->orderNumber') "; | |
| try{ | |
| $id = $this->executeQueryForObject($sql, true); | |
| }catch(\Exception $e){ | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $id; | |
| } | |
| public function updateCourseOutcome($courseOutcome){ | |
| $sql = ""; | |
| $courseOutcome = $this->realEscapeObject($courseOutcome); | |
| //description should be htmlencoded | |
| $sql = "UPDATE `nba_course_outcome` SET `code`='$courseOutcome->code', `objective`='$courseOutcome->objective', `order_no`='$courseOutcome->orderNumber', `description`='$courseOutcome->description' WHERE `id`='$courseOutcome->id'"; | |
| try{ | |
| $this->executeQuery($sql); | |
| }catch(\Exception $e){ | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $courseOutcome->id; | |
| } | |
| /** | |
| * Undocumented function | |
| * | |
| * @param [type] $coId | |
| * @return void | |
| */ | |
| public function deleteCourseOutcome($coId){ | |
| $sql = ""; | |
| $coId = $this->realEscapeString($coId); | |
| $sql = "DELETE FROM `nba_course_outcome` WHERE `id`='$coId'"; | |
| try{ | |
| $this->executeQuery($sql); | |
| }catch(\Exception $e){ | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $coId; | |
| } | |
| public function deleteCourseOutcomeByCoIdList($coIdList){ | |
| $sql = ""; | |
| $coIdList = $this->realEscapeArray($coIdList); | |
| $ids = implode("','", $coIdList); | |
| $sql = "DELETE FROM `nba_course_outcome` WHERE id IN ('".$ids."')"; | |
| try{ | |
| $this->executeQuery($sql); | |
| }catch(\Exception $e){ | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $coIdList; | |
| } | |
| public function findIfCoPresentForASubject($batchId, $semId, $subjectId){ | |
| $sql = ""; | |
| $batchId = $this->realEscapeString($batchId); | |
| $semId = $this->realEscapeString($semId); | |
| $subjectId = $this->realEscapeString($subjectId); | |
| $sql = "SELECT count(id) as numberOfCos FROM nba_course_outcome WHERE subjectID = '$subjectId' AND batchID = '$batchId' AND semID = '$semId'"; | |
| try{ | |
| $count = $this->executeQueryForObject($sql); | |
| }catch(\Exception $e){ | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| if($count->numberOfCos > 0){ | |
| return true; | |
| } | |
| return false; | |
| } | |
| /** | |
| * Undocumented function | |
| * | |
| * @param [type] $psId | |
| * @return void | |
| */ | |
| public function getBatchIdsAndSemIdUsingPsId($psId) | |
| { | |
| $psId = $this->realEscapeString($psId); | |
| $sql = "select subbatchId,semID from subbatches WHERE psID=$psId"; | |
| $responseList = []; | |
| try { | |
| $responseList = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $responseList; | |
| } | |
| /** | |
| * Undocumented function | |
| * | |
| * @param [type] $subbatchId | |
| * @return void | |
| */ | |
| public function getSubjectIdBySubbatchId($subbatchId) | |
| { | |
| $subbatchId = $this->realEscapeString($subbatchId); | |
| $sql = "select sbsr.subjectID,sbsr.batchID, b.batchName | |
| from subbatch_sbs sbbs inner join sbs_relation sbsr on sbsr.sbsID=sbbs.sbsID inner join subjects s on s.subjectID=sbsr.subjectID inner join batches b on b.batchID =sbsr.batchID where sbbs.subbatchID= '$subbatchId'"; | |
| $responseList = []; | |
| try { | |
| $subjectId = null; | |
| $subjectId = $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $subjectId; | |
| } | |
| public function getCoDetailsOfOnlineExam($oeExamId, $studentId,$batchId,$semId,$subjectId) | |
| { | |
| $sql = ""; | |
| $oeExamId = $this->realEscapeString($oeExamId); | |
| $studentId = $this->realEscapeString($studentId); | |
| $batchId = $this->realEscapeString($batchId); | |
| $semId = $this->realEscapeString($semId); | |
| $subjectId = $this->realEscapeString($subjectId); | |
| $sql ="SELECT | |
| oeum.oe_exam_questions_id, | |
| oeum.oe_exams_id, | |
| oeum.mark as markObtained, | |
| oeq.question, | |
| JSON_EXTRACT(oeq.properties, \"$.COURSE_OUTCOME\") as cos, | |
| oeq.mark, | |
| oeum.created_by | |
| FROM | |
| oe_exam_user_mark oeum | |
| INNER JOIN | |
| oe_exam_questions oeq ON (oeq.id = oeum.oe_exam_questions_id) | |
| WHERE | |
| oeum.user_id = '$studentId' | |
| AND oeum.oe_exams_id = '$oeExamId' | |
| GROUP BY oeum.oe_exam_questions_id"; | |
| try { | |
| $responseList = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| foreach($responseList as $key =>$questionDetails){ | |
| $coDetails =json_decode($questionDetails->cos); | |
| foreach($coDetails as $coData){ | |
| if($coData->batchId == $batchId && $coData->semId==$semId && $coData->subjectId == $subjectId){ | |
| $responseList[$key]->cos = $coData->coList; | |
| } | |
| } | |
| } | |
| $sql="SELECT | |
| oeq.id, | |
| oeua.user_id, | |
| oeq.oe_exams_id, | |
| oeq.id AS oe_exam_questions_id, | |
| oeq.question, | |
| JSON_EXTRACT(oeq.properties, \"$.COURSE_OUTCOME\") as cos, | |
| oeua.oe_exam_answers_id, | |
| oeua.answer, | |
| oea.point as markObtained, | |
| oeua.is_submitted, | |
| oeua.attended_duration, | |
| oeq.mark | |
| FROM | |
| oe_exam_user_answers oeua | |
| INNER JOIN | |
| oe_exam_answers oea ON (oea.oe_exams_id = oeua.oe_exams_id AND oea.oe_exam_questions_id = oeua.oe_exam_questions_id AND oea.id = oeua.oe_exam_answers_id) | |
| INNER JOIN | |
| oe_exam_questions oeq ON (oeq.oe_exams_id = oeua.oe_exams_id AND oeq.id = oeua.oe_exam_questions_id) | |
| WHERE | |
| oeua.oe_exams_id = '$oeExamId' AND oeua.user_id = '$studentId'"; | |
| try { | |
| $responseListMcq = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| foreach($responseListMcq as $key =>$questionDetails){ | |
| $coDetails =json_decode($questionDetails->cos); | |
| foreach($coDetails as $coData){ | |
| if($coData->batchId == $batchId && $coData->semId==$semId && $coData->subjectId == $subjectId){ | |
| $responseListMcq[$key]->cos = $coData->coList; | |
| } | |
| } | |
| } | |
| $responseList = array_merge($responseList,$responseListMcq); | |
| return $responseList; | |
| } | |
| public function calculateTotalCOPercentsOnlineExam($validStudentQuestionIds, $batchId, $semId, $subjectId) | |
| { | |
| $sql = ""; | |
| $totalCoPercent =[]; | |
| $validStudentQuestionIds = $this->realEscapeArray($validStudentQuestionIds); | |
| $batchId = $this->realEscapeString($batchId); | |
| $semId = $this->realEscapeString($semId); | |
| $subjectId = $this->realEscapeString($subjectId); | |
| $ids = implode("','", $validStudentQuestionIds); | |
| $sql ="SELECT | |
| JSON_EXTRACT(oeq.properties, \"$.COURSE_OUTCOME\") as cos | |
| FROM | |
| oe_exam_questions oeq | |
| WHERE | |
| oeq.id IN ('".$ids."')"; | |
| try { | |
| $response = $this->executeQueryForList($sql); | |
| foreach($response as $questionDetails){ | |
| $coDetails =json_decode($questionDetails->cos); | |
| foreach($coDetails as $coData){ | |
| if($coData->batchId == $batchId && $coData->semId==$semId && $coData->subjectId == $subjectId){ | |
| $questionDetails->cos = $coData->coList; | |
| } | |
| } | |
| } | |
| foreach($response as $coDetails){ | |
| $data[] =$coDetails->cos; | |
| } | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| foreach($data as $coDetails){ | |
| foreach($coDetails as $co){ | |
| $totalCoPercent[$co->coId] += $co->value; | |
| } | |
| } | |
| return $totalCoPercent; | |
| } | |
| public function getCoDetailsOfOnlineExamForSelectAllAnswerdAndMandatoryUnAswered($oeExamId, $studentId,$batchId,$semId,$subjectId) | |
| { | |
| $sql = ""; | |
| $oeExamId = $this->realEscapeString($oeExamId); | |
| $studentId = $this->realEscapeString($studentId); | |
| $batchId = $this->realEscapeString($batchId); | |
| $semId = $this->realEscapeString($semId); | |
| $subjectId = $this->realEscapeString($subjectId); | |
| $sql ="SELECT | |
| oeq.id oe_exam_questions_id, | |
| oeum.oe_exams_id, | |
| oeum.mark as markObtained, | |
| oeq.question, | |
| JSON_EXTRACT(oeq.properties, \"$.COURSE_OUTCOME\") as cos, | |
| oeq.mark, | |
| oeum.created_by | |
| FROM | |
| oe_exam_questions oeq | |
| LEFT JOIN | |
| oe_exam_user_mark oeum | |
| ON (oeum.oe_exam_questions_id = oeq.id AND oeum.user_id = '$studentId') | |
| WHERE | |
| oeq.oe_exams_id = '$oeExamId'"; | |
| try { | |
| $responseList = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| foreach($responseList as $key =>$questionDetails){ | |
| $coDetails =json_decode($questionDetails->cos); | |
| foreach($coDetails as $coData){ | |
| if($coData->batchId == $batchId && $coData->semId==$semId && $coData->subjectId == $subjectId){ | |
| $responseList[$key]->cos = $coData->coList; | |
| } | |
| } | |
| } | |
| $sql="SELECT | |
| oeq.id, | |
| oeua.user_id, | |
| oeq.oe_exams_id, | |
| oeq.id AS oe_exam_questions_id, | |
| oeq.question, | |
| JSON_EXTRACT(oeq.properties, \"$.COURSE_OUTCOME\") as cos, | |
| oeua.oe_exam_answers_id, | |
| oeua.answer, | |
| oea.point as markObtained, | |
| oeua.is_submitted, | |
| oeua.attended_duration, | |
| oeq.mark | |
| FROM | |
| oe_exam_questions oeq | |
| LEFT JOIN | |
| oe_exam_user_answers oeua ON (oeq.oe_exams_id = oeua.oe_exams_id AND oeq.id = oeua.oe_exam_questions_id) | |
| INNER JOIN | |
| oe_exam_answers oea ON (oea.oe_exams_id = oeua.oe_exams_id AND oea.oe_exam_questions_id = oeua.oe_exam_questions_id AND oea.id = oeua.oe_exam_answers_id) | |
| WHERE | |
| oeq.oe_exams_id = '$oeExamId' AND oeua.user_id = '$studentId'"; | |
| try { | |
| $responseListMcq = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| foreach($responseListMcq as $key =>$questionDetails){ | |
| $coDetails =json_decode($questionDetails->cos); | |
| foreach($coDetails as $coData){ | |
| if($coData->batchId == $batchId && $coData->semId==$semId && $coData->subjectId == $subjectId){ | |
| $responseListMcq[$key]->cos = $coData->coList; | |
| } | |
| } | |
| } | |
| $responseList = array_merge($responseList,$responseListMcq); | |
| return $responseList; | |
| } | |
| public function getSectionWiseValidQuestionIdsSelectAllAnswerdAndMandatoryUnAsweredForCoCalculationOfAStudentForOnlineExam($oeExamId, $studentId) | |
| { | |
| $oeExamId = $this->realEscapeString($oeExamId); | |
| $studentId = $this->realEscapeString($studentId); | |
| // $coPoCourseWiseCalculationDisplayStyle = CommonService::getInstance()->getSettings(SettingsConstants::NBA, NbaMethod::OBE_CO_PO_CALCULATION_DISPlAY_STYLE_IN_TREE_NODE); | |
| // $questionsForCoCalculation = CommonService::getInstance()->getSettings(SettingsConstants::NBA, NbaMethod::OBE_SELECT_QUESTIONS_IN_THE_QUESTION_PAPER_FOR_OBE_CALCULATION); | |
| $response = new \stdClass(); | |
| $sql = ""; | |
| $sql = "SELECT | |
| section.id, section.name, section.properties | |
| FROM | |
| oe_exam_sections section | |
| INNER JOIN | |
| oe_exams oee ON (section.oe_exams_id = oee.id) | |
| WHERE | |
| oee.id = '$oeExamId'"; | |
| try { | |
| $sectionDetails = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| $response->nbaCourseOutcomeQuestionsIdList=array(); | |
| $response->totalMarkObtained=0; | |
| if(count($sectionDetails)){ | |
| foreach ($sectionDetails as $section) { | |
| $section->properties= json_decode($section->properties); | |
| if(property_exists($section->properties, "noOfQuestionsToAnswer")){ | |
| $conditions =""; | |
| $contexts =[]; | |
| if ( $section->id ) { | |
| $contexts[] = '"section":"'.$section->id.'"'; | |
| } | |
| $conditions .= " AND JSON_CONTAINS(oeq.properties, '{" . implode (",", $contexts ) . "}')"; | |
| if($section->properties->noOfQuestionsToAnswer > 0){ | |
| $sql ="SELECT | |
| oeq.id, | |
| oeua.user_id, | |
| oeq.oe_exams_id, | |
| oeq.id AS oe_exam_questions_id, | |
| oeq.properties, | |
| oeua.oe_exam_answers_id, | |
| oeua.answer, | |
| oea.point, | |
| oeum.mark, | |
| oeua.is_submitted, | |
| oeua.attended_duration | |
| FROM | |
| oe_exam_questions oeq | |
| LEFT JOIN | |
| oe_exam_user_answers oeua ON (oeq.oe_exams_id = oeua.oe_exams_id AND oeq.id = oeua.oe_exam_questions_id AND oeua.user_id = '$studentId') | |
| LEFT JOIN | |
| oe_exam_answers oea ON (oea.oe_exams_id = oeua.oe_exams_id AND oea.oe_exam_questions_id = oeua.oe_exam_questions_id AND oea.id = oeua.oe_exam_answers_id) | |
| LEFT JOIN | |
| oe_exam_user_mark oeum ON (oeum.oe_exams_id = oeq.oe_exams_id AND oeum.oe_exam_questions_id = oeq.id AND oeum.user_id = '$studentId') | |
| WHERE | |
| oeq.oe_exams_id = '$oeExamId' $conditions "; | |
| try { | |
| $questionDetails = $this->executeQueryForList($sql); | |
| $questionTotalMarks=[]; | |
| $questionDetailsNew = array(); | |
| foreach ($questionDetails as $k => $v) { | |
| $v->properties= json_decode($v->properties); | |
| $questionDetailsNew[$k] = clone $v; | |
| } | |
| foreach ($questionDetails as $question) { | |
| // $question->properties= json_decode($question->properties); | |
| $obj1 = new \stdClass; | |
| $obj1->subQuestions=[]; | |
| $obj1->totalMark=0; | |
| $obj1->questionId=$question->id; | |
| if(property_exists($question->properties, "hasSubQuestion") && $question->properties->hasSubQuestion=="1"){ | |
| foreach($questionDetailsNew as $questionNew){ | |
| if(property_exists($questionNew->properties, "mainQuestionId") && $questionNew->properties->mainQuestionId == $question->id ){ | |
| $obj1->subQuestions[] = $questionNew->id; | |
| if($questionNew->properties->type=="MCQ"){ | |
| $obj1->totalMark= $obj1->totalMark+$questionNew->point; | |
| }else{ | |
| $obj1->totalMark= $obj1->totalMark+$questionNew->mark; | |
| } | |
| } | |
| } | |
| $questionTotalMarks[] = $obj1; | |
| }elseif((!property_exists($question->properties, "hasSubQuestion")||$question->properties->hasSubQuestion==false) && !(property_exists($question->properties, "isSubQuestion")|| $question->properties->isSubQuestion=="0")){ | |
| if($question->properties->type=="MCQ"){ | |
| $obj1->totalMark= $question->point; | |
| }else{ | |
| $obj1->totalMark= $question->mark; | |
| } | |
| $questionTotalMarks[] = $obj1; | |
| } | |
| } | |
| usort($questionTotalMarks, function($a, $b) { | |
| if($a->totalMark===$b->totalMark){ | |
| return 0; | |
| } | |
| return ($a->totalMark>$b->totalMark)?-1:1; | |
| }); | |
| $countOfAnsweredQuestions = 0; | |
| for($i=0; $i<count($questionTotalMarks); $i++) { | |
| if($questionTotalMarks[$i]->totalMark != null){ | |
| if(count($questionTotalMarks[$i]->subQuestions)){ | |
| $response->nbaCourseOutcomeQuestionsIdList=array_merge( $response->nbaCourseOutcomeQuestionsIdList,$questionTotalMarks[$i]->subQuestions); | |
| $response->totalMarkObtained= $response->totalMarkObtained+ $questionTotalMarks[$i]->totalMark; | |
| $countOfAnsweredQuestions = $countOfAnsweredQuestions +1; | |
| }else{ | |
| $response->nbaCourseOutcomeQuestionsIdList[] =$questionTotalMarks[$i]->questionId; | |
| $response->totalMarkObtained= $response->totalMarkObtained+ $questionTotalMarks[$i]->totalMark; | |
| $countOfAnsweredQuestions = $countOfAnsweredQuestions +1; | |
| } | |
| } | |
| } | |
| if($countOfAnsweredQuestions<$section->properties->noOfQuestionsToAnswer){ | |
| $balanceQuestionCount = $section->properties->noOfQuestionsToAnswer-$countOfAnsweredQuestions; | |
| for($i=$countOfAnsweredQuestions; $i<($countOfAnsweredQuestions+$balanceQuestionCount); $i++) { | |
| if(count($questionTotalMarks[$i]->subQuestions)){ | |
| $response->nbaCourseOutcomeQuestionsIdList=array_merge( $response->nbaCourseOutcomeQuestionsIdList,$questionTotalMarks[$i]->subQuestions); | |
| $response->totalMarkObtained= $response->totalMarkObtained+ $questionTotalMarks[$i]->totalMark; | |
| }else{ | |
| $response->nbaCourseOutcomeQuestionsIdList[] =$questionTotalMarks[$i]->questionId; | |
| $response->totalMarkObtained= $response->totalMarkObtained+ $questionTotalMarks[$i]->totalMark; | |
| } | |
| } | |
| } | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| }else{ | |
| $sql ="SELECT | |
| oeq.id, | |
| if(oeua.user_id, oeua.user_id, oeum.user_id) AS user_id, | |
| oeq.oe_exams_id, | |
| oeq.id AS oe_exam_questions_id, | |
| oeq.properties, | |
| oeua.oe_exam_answers_id, | |
| oeua.answer, | |
| if(oea.point, oea.point, oeum.mark) AS mark, | |
| oeua.is_submitted | |
| FROM | |
| oe_exam_questions oeq | |
| LEFT JOIN oe_exam_user_answers oeua ON ( | |
| oeq.oe_exams_id = oeua.oe_exams_id | |
| AND oeq.id = oeua.oe_exam_questions_id | |
| AND oeua.user_id = '$studentId' | |
| ) | |
| LEFT JOIN oe_exam_answers oea ON ( | |
| oea.oe_exams_id = oeua.oe_exams_id | |
| AND oea.oe_exam_questions_id = oeua.oe_exam_questions_id | |
| AND oea.id = oeua.oe_exam_answers_id | |
| ) | |
| LEFT JOIN oe_exam_user_mark oeum ON ( | |
| oeum.oe_exams_id = oeq.oe_exams_id | |
| AND oeum.oe_exam_questions_id = oeq.id | |
| AND oeum.user_id = '$studentId' | |
| ) | |
| WHERE | |
| oeq.oe_exams_id = '$oeExamId' | |
| $conditions "; | |
| try { | |
| $questionDetails = $this->executeQueryForList($sql); | |
| foreach ($questionDetails as $question) { | |
| if(property_exists($question->properties, "hasSubQuestion") && $question->properties->hasSubQuestion=="1"){ | |
| }else{ | |
| $response->nbaCourseOutcomeQuestionsIdList[] = $question->id; | |
| $response->totalMarkObtained= $response->totalMarkObtained+ $question->mark; | |
| } | |
| } | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| } | |
| } | |
| }else{ | |
| $sql ="SELECT | |
| oeq.id, | |
| if(oeua.user_id, oeua.user_id, oeum.user_id) AS user_id, | |
| oeq.oe_exams_id, | |
| oeq.id AS oe_exam_questions_id, | |
| oeq.properties, | |
| oeua.oe_exam_answers_id, | |
| oeua.answer, | |
| if(oea.point, oea.point, oeum.mark) AS mark, | |
| oeua.is_submitted | |
| FROM | |
| oe_exam_questions oeq | |
| LEFT JOIN oe_exam_user_answers oeua ON ( | |
| oeq.oe_exams_id = oeua.oe_exams_id | |
| AND oeq.id = oeua.oe_exam_questions_id | |
| AND oeua.user_id = '$studentId' | |
| ) | |
| LEFT JOIN oe_exam_answers oea ON ( | |
| oea.oe_exams_id = oeua.oe_exams_id | |
| AND oea.oe_exam_questions_id = oeua.oe_exam_questions_id | |
| AND oea.id = oeua.oe_exam_answers_id | |
| ) | |
| LEFT JOIN oe_exam_user_mark oeum ON ( | |
| oeum.oe_exams_id = oeq.oe_exams_id | |
| AND oeum.oe_exam_questions_id = oeq.id | |
| AND oeum.user_id = '$studentId' | |
| ) | |
| WHERE | |
| oeq.oe_exams_id = '$oeExamId' "; | |
| try { | |
| $questionDetails = $this->executeQueryForList($sql); | |
| foreach ($questionDetails as $question) { | |
| if(property_exists($question->properties, "hasSubQuestion") && $question->properties->hasSubQuestion=="1"){ | |
| }else{ | |
| $response->nbaCourseOutcomeQuestionsIdList[] = $question->id; | |
| $response->totalMarkObtained= $response->totalMarkObtained+ $question->mark; | |
| } | |
| } | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| return $response; | |
| } | |
| } |