Code Coverage |
||||||||||
Classes and Traits |
Functions and Methods |
Lines |
||||||||
| Total | |
0.00% |
0 / 1 |
|
0.00% |
0 / 67 |
CRAP | |
0.00% |
0 / 1563 |
| QuestionPaperService | |
0.00% |
0 / 1 |
|
0.00% |
0 / 67 |
38612.00 | |
0.00% |
0 / 1563 |
| __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 |
|||
| viewQuestionPaperById | |
0.00% |
0 / 1 |
42.00 | |
0.00% |
0 / 100 |
|||
| viewQuestionPaperByIdForView | |
0.00% |
0 / 1 |
42.00 | |
0.00% |
0 / 98 |
|||
| getSectionById | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 9 |
|||
| getQuestionPaperSectionsById | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 13 |
|||
| createQuestionPaperSection | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 21 |
|||
| updateQuestionPaperSection | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 17 |
|||
| deleteQuestionPaperSection | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 8 |
|||
| saveQuestionsToSection | |
0.00% |
0 / 1 |
56.00 | |
0.00% |
0 / 38 |
|||
| deleteQuestionsFromSection | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 21 |
|||
| updateQuestionInSection | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 14 |
|||
| getSectionQuestionsById | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 12 |
|||
| getAllQuestionsByMark | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 8 |
|||
| getQuestions | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 15 |
|||
| enterMarkView | |
0.00% |
0 / 1 |
2.00 | |
0.00% |
0 / 2 |
|||
| getQuestionCoNames | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 10 |
|||
| questionPaperIsFinalised | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 14 |
|||
| toggleQuestionPaperToPublic | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 10 |
|||
| getQuestionPaperDetailsById | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 77 |
|||
| checkIfQuestPaperExistsForExamType | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 24 |
|||
| createQuestionPaper | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 9 |
|||
| createQuestion | |
0.00% |
0 / 1 |
30.00 | |
0.00% |
0 / 24 |
|||
| copyQuestionPaper | |
0.00% |
0 / 1 |
90.00 | |
0.00% |
0 / 61 |
|||
| getQuestionPapersFromSubjComm | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 21 |
|||
| getAllCODetailsOfAsubjInABatch | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 11 |
|||
| getAllQuestionCoValues | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 9 |
|||
| getQuestionPaperCopiedInfo | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 10 |
|||
| getQuestionCopiedInfo | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 10 |
|||
| createNbaQuestionCORelation | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 12 |
|||
| createNbaQuestionPaperQuestionCORelation | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 15 |
|||
| deleteAllCORelationByQuestionID | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 8 |
|||
| getFinalisedQuestionPaperDetailsByQuestionId | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 13 |
|||
| updateQuestionPaperSectionCoRelation | |
0.00% |
0 / 1 |
30.00 | |
0.00% |
0 / 20 |
|||
| getQuestionPaperDetailsByQuestionId | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 9 |
|||
| submitQuestionPaperToExamController | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 10 |
|||
| getQuestionByQuestionId | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 9 |
|||
| getTotalCountOfQuestionsInQP | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 10 |
|||
| updateQuestion | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 24 |
|||
| getQuestionPaperCOPOMappingsByQuestionPaperId | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 28 |
|||
| getAllQuestionPaperByStaffIdAndSubjectId | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 32 |
|||
| checkIfQuestionPaperExists | |
0.00% |
0 / 1 |
42.00 | |
0.00% |
0 / 56 |
|||
| getSingleQuestionPaperDetailsById | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 28 |
|||
| deleteQuestionPaperById | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 37 |
|||
| updateQuestionPaper | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 23 |
|||
| getQuestionPaperSectionsByQuestionPaperId | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 9 |
|||
| getCoDetailsByBatchIdSemIdAndSubjectId | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 18 |
|||
| deleteQuestionById | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 21 |
|||
| getQuestionDetailsForEdit | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 47 |
|||
| updateQuestionDetailsById | |
0.00% |
0 / 1 |
132.00 | |
0.00% |
0 / 75 |
|||
| getResourceId | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 8 |
|||
| deleteQuestionResourceIdById | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 11 |
|||
| deleteAssessmentAssignmentQuestionById | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 25 |
|||
| getPseudoSubjectAssessmentQuestionsByQuestionId | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 11 |
|||
| getQuestionPaperDetailsForMarkEntry | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 68 |
|||
| getExamDetailsByQuestionPaperId | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 16 |
|||
| getStudentListAndDetails | |
0.00% |
0 / 1 |
30.00 | |
0.00% |
0 / 142 |
|||
| deleteStudentExamMark | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 10 |
|||
| upsertAssessmentStudentQuestionWiseMark | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 8 |
|||
| upsertStudentAttendanceInExam | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 10 |
|||
| upsertAssessmentStudentTotalMark | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 8 |
|||
| upsertConfirmStudentExamTotalMarks | |
0.00% |
0 / 1 |
42.00 | |
0.00% |
0 / 30 |
|||
| deleteStudentExamQuestionsMarks | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 9 |
|||
| studentExamQuestionsMarksConfirm | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 10 |
|||
| deleteAssessmentStudentQuestionWiseMark | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 9 |
|||
| deleteAssessmentStudentQuestionWiseMarkByQuestionIdAndStudentId | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 8 |
|||
| <?php | |
| namespace com\linways\core\ams\professional\service\nba; | |
| use com\linways\base\util\RequestUtil; | |
| use com\linways\core\ams\professional\dto\nba\NBAQuestion; | |
| use com\linways\core\ams\professional\request\RemoveResourceRequest; | |
| use com\linways\core\ams\professional\service\BaseService; | |
| use com\linways\core\ams\professional\service\CommonService; | |
| use com\linways\core\ams\professional\service\SubjectService; | |
| use com\linways\core\ams\professional\service\ResourceService; | |
| use com\linways\core\ams\professional\dto\nba\NBAQuestionPaper; | |
| use com\linways\core\ams\professional\dto\OBESettingsConstents; | |
| use com\linways\core\ams\professional\service\DepartmentService; | |
| use com\linways\core\ams\professional\dto\nba\NBASectionQuestion; | |
| use com\linways\core\ams\professional\request\GetPreSignedUrlRequest; | |
| use com\linways\core\ams\professional\dto\nba\NBAQuestionPaperSection; | |
| use com\linways\core\ams\professional\exception\ProfessionalException; | |
| use com\linways\core\ams\professional\request\nba\getAllQuestionRequest; | |
| use com\linways\core\ams\professional\request\nba\UpdateQuestionRequest; | |
| use com\linways\core\ams\professional\mapper\nba\QuestionPaperServiceMapper; | |
| use com\linways\core\ams\professional\service\BatchService; | |
| use com\linways\core\ams\professional\service\SemesterService; | |
| /** | |
| * Undocumented class | |
| */ | |
| class QuestionPaperService 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 = QuestionPaperServiceMapper::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; | |
| } | |
| /** | |
| * [viewQuestionPaperById description] | |
| * @param int $questionPaperID | |
| * @throws ProfessionalException | |
| * @return void|\com\linways\core\ams\professional\dto\nba\NBAQuestionPaper | |
| */ | |
| public function viewQuestionPaperById($questionPaperID) | |
| { | |
| $questionPaperID = $this->realEscapeString($questionPaperID); | |
| $resultObject = []; | |
| try { | |
| $sql_questionPaper = " | |
| SELECT ncoqp.header, | |
| ncoqp.qpcode, | |
| ncoqp.exam_type_id, | |
| ncoqp.subjectID, | |
| ncoqp.staffID, | |
| ncoqp.batchID, | |
| ncoqp.semID, | |
| ncoqp.maxMark, | |
| ncoqp.subbatchID, | |
| ncoqp.duration, | |
| nqps.id AS sectionID, | |
| nqps.section_name, | |
| nqps.total_questions, | |
| nqps.no_of_questions_to_answer, | |
| nqps.marks_per_question, | |
| nqps.instruction, | |
| nqps.hide_default_instruction as hideDefaultInstruction, | |
| nqps.seq_number AS sectionSeqNumber, | |
| nqps.createdBy AS nqps_createdBy, | |
| nqps.createdDate AS nqps_createdDate, | |
| nqps.updatedBy AS nqps_updatedBy, | |
| nqps.updatedDate AS nqps_updatedDate, | |
| nsq.id AS qpSecQuesID, | |
| nsq.seq_number AS questionSeqNumber, | |
| ncoq.id as questionID, | |
| ncoq.question, | |
| ncoq.attachments, | |
| ncoq.blooms_level as bloomsLevel, | |
| ncoq.lin_resource_id as linResourceId | |
| FROM assessment_structure ncoqp | |
| JOIN assessment_structure_section nqps ON (ncoqp.id = nqps.assessment_structure_id) | |
| JOIN assessment_structure_section_questions nsq ON (nqps.id = nsq.assessment_structure_section_id) | |
| JOIN assessment_questions ncoq ON (nsq.assessment_question_id = ncoq.id) | |
| WHERE ncoqp.id = '" . $questionPaperID . "' ORDER BY nqps.seq_number, nsq.seq_number"; | |
| $resultObject = $this->executeQueryForList($sql_questionPaper); | |
| if (empty ($resultObject)) | |
| return; | |
| $questionPaperObject = new NBAQuestionPaper(); | |
| foreach ($resultObject as $questionPaperResult) { | |
| $questionPaperObject->id = $questionPaperID; | |
| $questionPaperObject->header = $questionPaperResult->header; | |
| $questionPaperObject->questionPaperCode = $questionPaperResult->qpcode; | |
| $questionPaperObject->examTypeId = $questionPaperResult->exam_type_id; | |
| $questionPaperObject->subjectId = $questionPaperResult->subjectID; | |
| $questionPaperObject->staffId = $questionPaperResult->staffID; | |
| $questionPaperObject->batchId = $questionPaperResult->batchID; | |
| $questionPaperObject->semId = $questionPaperResult->semID; | |
| $questionPaperObject->subbatchId = $questionPaperResult->subbatchID; | |
| $questionPaperObject->maxMark = $questionPaperResult->maxMark; | |
| $questionPaperObject->duration = $questionPaperResult->duration; | |
| // Sections | |
| $sectionID = $questionPaperResult->sectionID; | |
| $questionPaperSectionObject = $questionPaperObject->nbaQuestionPaperSection[$sectionID]; | |
| if (!$questionPaperSectionObject) { | |
| $questionPaperSectionObject = new NBAQuestionPaperSection(); | |
| } | |
| $questionPaperSectionObject->id = $sectionID; | |
| $questionPaperSectionObject->assessmentStructureId = $questionPaperID; | |
| $questionPaperSectionObject->sectionName = $questionPaperResult->section_name; | |
| $questionPaperSectionObject->totalQuestions = $questionPaperResult->total_questions; | |
| $questionPaperSectionObject->noOfQuestionsToAnswer = $questionPaperResult->no_of_questions_to_answer; | |
| $questionPaperSectionObject->marksPerQuestion = $questionPaperResult->marks_per_question; | |
| $questionPaperSectionObject->instruction = $questionPaperResult->instruction; | |
| $questionPaperSectionObject->seqNumber = $questionPaperResult->sectionSeqNumber; | |
| $questionPaperSectionObject->hideDefaultInstruction = $questionPaperResult->hideDefaultInstruction; | |
| $questionPaperSectionObject->createdBy = $questionPaperResult->nqps_createdBy; | |
| $questionPaperSectionObject->createdDate = $questionPaperResult->nqps_createdDate; | |
| $questionPaperSectionObject->updatedBy = $questionPaperResult->nqps_updatedBy; | |
| $questionPaperSectionObject->updatedDate = $questionPaperResult->nqps_updatedDate; | |
| // Section Questions | |
| $sectionQuestionObject = new NBASectionQuestion(); | |
| $sectionQuestionObject->id = $questionPaperResult->qpSecQuesID; | |
| $sectionQuestionObject->questionId = $questionPaperResult->questionID; | |
| $sectionQuestionObject->seqNumber = $questionPaperResult->questionSeqNumber; | |
| $sectionQuestionObject->question = $questionPaperResult->question; | |
| $sectionQuestionObject->bloomsLevel = $questionPaperResult->bloomsLevel; | |
| $sectionQuestionObject->attachments = $questionPaperResult->attachments; | |
| $sectionQuestionObject->linResourceId = $questionPaperResult->linResourceId; | |
| $sectionQuestionObject->documentName = ""; | |
| $sectionQuestionObject->documentUrl = ""; | |
| if($sectionQuestionObject->linResourceId){ | |
| $request = new GetPreSignedUrlRequest(); | |
| $request->resourceId = $sectionQuestionObject->linResourceId; | |
| $request->secretKey = getenv("AWS_CLIENT_SECRET_KEY"); | |
| $request->accessKey = getenv("AWS_ACCESS_KEY"); | |
| $response = ResourceService::getInstance()->getPreSignedUrlByResourceId($request); | |
| $sectionQuestionObject->documentName = $response->name; | |
| $sectionQuestionObject->documentUrl = $response->url; | |
| } | |
| $questionPaperSectionObject->nbaSectionQuestion[] = $sectionQuestionObject; | |
| $questionPaperObject->nbaQuestionPaperSection[$sectionID] = $questionPaperSectionObject; | |
| } | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $questionPaperObject; | |
| } | |
| //service copied for get sections as array | |
| public function viewQuestionPaperByIdForView($questionPaperID) | |
| { | |
| $questionPaperID = $this->realEscapeString($questionPaperID); | |
| $resultObject = []; | |
| try { | |
| $sql_questionPaper = "SELECT ncoqp.header, | |
| ncoqp.qpcode, | |
| ncoqp.exam_type_id, | |
| ncoqp.subjectID, | |
| ncoqp.staffID, | |
| ncoqp.batchID, | |
| ncoqp.semID, | |
| ncoqp.maxMark, | |
| ncoqp.subbatchID, | |
| ncoqp.duration, | |
| nqps.id AS sectionID, | |
| nqps.section_name, | |
| nqps.total_questions, | |
| nqps.no_of_questions_to_answer, | |
| nqps.marks_per_question, | |
| nqps.instruction, | |
| nqps.hide_default_instruction as hideDefaultInstruction, | |
| nqps.seq_number AS sectionSeqNumber, | |
| nqps.createdBy AS nqps_createdBy, | |
| nqps.createdDate AS nqps_createdDate, | |
| nqps.updatedBy AS nqps_updatedBy, | |
| nqps.updatedDate AS nqps_updatedDate, | |
| nsq.id AS qpSecQuesID, | |
| nsq.seq_number AS questionSeqNumber, | |
| ncoq.id as questionID, | |
| ncoq.question, | |
| ncoq.attachments, | |
| ncoq.lin_resource_id as linResourceId | |
| FROM | |
| assessment_structure ncoqp | |
| JOIN assessment_structure_section nqps ON (ncoqp.id = nqps.assessment_structure_id) | |
| JOIN assessment_structure_section_questions nsq ON (nqps.id = nsq.assessment_structure_section_id) | |
| JOIN assessment_questions ncoq ON (nsq.assessment_question_id = ncoq.id) | |
| WHERE ncoqp.id = '" . $questionPaperID . "' ORDER BY nqps.seq_number, nsq.seq_number"; | |
| $resultObject = $this->executeQueryForList($sql_questionPaper); | |
| if (empty ($resultObject)) | |
| return; | |
| $questionPaperObject = new NBAQuestionPaper(); | |
| foreach ($resultObject as $questionPaperResult) { | |
| $questionPaperObject->id = $questionPaperID; | |
| $questionPaperObject->header = $questionPaperResult->header; | |
| $questionPaperObject->questionPaperCode = $questionPaperResult->qpcode; | |
| $questionPaperObject->examTypeId = $questionPaperResult->exam_type_id; | |
| $questionPaperObject->subjectId = $questionPaperResult->subjectID; | |
| $questionPaperObject->staffId = $questionPaperResult->staffID; | |
| $questionPaperObject->batchId = $questionPaperResult->batchID; | |
| $questionPaperObject->semId = $questionPaperResult->semID; | |
| $questionPaperObject->subbatchId = $questionPaperResult->subbatchID; | |
| $questionPaperObject->maxMark = $questionPaperResult->maxMark; | |
| $questionPaperObject->duration = $questionPaperResult->duration; | |
| // Sections | |
| $sectionID = $questionPaperResult->sectionID; | |
| $questionPaperSectionObject = $questionPaperObject->nbaQuestionPaperSection[$sectionID]; | |
| if (!$questionPaperSectionObject) { | |
| $questionPaperSectionObject = new NBAQuestionPaperSection(); | |
| } | |
| $questionPaperSectionObject->id = $sectionID; | |
| $questionPaperSectionObject->assessmentStructureId = $questionPaperID; | |
| $questionPaperSectionObject->sectionName = $questionPaperResult->section_name; | |
| $questionPaperSectionObject->totalQuestions = $questionPaperResult->total_questions; | |
| $questionPaperSectionObject->noOfQuestionsToAnswer = $questionPaperResult->no_of_questions_to_answer; | |
| $questionPaperSectionObject->marksPerQuestion = $questionPaperResult->marks_per_question; | |
| $questionPaperSectionObject->instruction = $questionPaperResult->instruction; | |
| $questionPaperSectionObject->seqNumber = $questionPaperResult->sectionSeqNumber; | |
| $questionPaperSectionObject->hideDefaultInstruction = $questionPaperResult->hideDefaultInstruction; | |
| $questionPaperSectionObject->createdBy = $questionPaperResult->nqps_createdBy; | |
| $questionPaperSectionObject->createdDate = $questionPaperResult->nqps_createdDate; | |
| $questionPaperSectionObject->updatedBy = $questionPaperResult->nqps_updatedBy; | |
| $questionPaperSectionObject->updatedDate = $questionPaperResult->nqps_updatedDate; | |
| // Section Questions | |
| $sectionQuestionObject = new NBASectionQuestion(); | |
| $sectionQuestionObject->id = $questionPaperResult->qpSecQuesID; | |
| $sectionQuestionObject->questionId = $questionPaperResult->questionID; | |
| $sectionQuestionObject->seqNumber = $questionPaperResult->questionSeqNumber; | |
| $sectionQuestionObject->question = $questionPaperResult->question; | |
| $sectionQuestionObject->attachments = $questionPaperResult->attachments; | |
| $sectionQuestionObject->linResourceId = $questionPaperResult->linResourceId; | |
| $sectionQuestionObject->documentName = ""; | |
| $sectionQuestionObject->documentUrl = ""; | |
| if($sectionQuestionObject->linResourceId){ | |
| $request = new GetPreSignedUrlRequest(); | |
| $request->resourceId = $sectionQuestionObject->linResourceId; | |
| $request->secretKey = getenv("AWS_CLIENT_SECRET_KEY"); | |
| $request->accessKey = getenv("AWS_ACCESS_KEY"); | |
| $response = ResourceService::getInstance()->getPreSignedUrlByResourceId($request); | |
| $sectionQuestionObject->documentName = $response->name; | |
| $sectionQuestionObject->documentUrl = $response->url; | |
| } | |
| $questionPaperSectionObject->nbaSectionQuestion[] = $sectionQuestionObject; | |
| $questionPaperObject->nbaQuestionPaperSection[$sectionID] = $questionPaperSectionObject; | |
| } | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $questionPaperObject; | |
| } | |
| /** | |
| * get section by id | |
| * @param int $sectionID | |
| * @throws ProfessionalException | |
| * @return object|NULL|\com\linways\base\util\$objectList[] | |
| */ | |
| public function getSectionById($sectionID) | |
| { | |
| $sectionID = $this->realEscapeString($sectionID); | |
| $sql_section = "SELECT id, section_name AS sectionName, assessment_structure_id AS questionPaperId, total_questions AS totalQuestions, no_of_questions_to_answer AS noOfQuestionsToAnswer, marks_per_question AS marksPerQuestion, instruction, hide_default_instruction AS hideDefaultInstruction, seq_number AS seqNumber FROM assessment_structure_section WHERE id = " . $sectionID . ""; | |
| try { | |
| return $this->executeQueryForObject($sql_section); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * Method for getting all sections in a questionpaper along with questions By passing QP id | |
| * @param int $questionPaperID | |
| * @param string $sortBy | |
| * @param string $sortOrder | |
| * @throws ProfessionalException | |
| * @throws \com\linways\base\exception\CoreException | |
| * @return object|array|\com\linways\base\util\$objectList[] | |
| */ | |
| public function getQuestionPaperSectionsById($questionPaperID, $sortBy = "seq_number", $sortOrder = "ASC") | |
| { | |
| $questionPaperID = $this->realEscapeString($questionPaperID); | |
| $sortBy = $this->realEscapeString($sortBy); | |
| $sortOrder = $this->realEscapeString($sortOrder); | |
| $questionPaperSection = []; | |
| // Validate sortOrder and sortBy | |
| RequestUtil::validateSortOrder($sortBy, $sortOrder); | |
| $sql_section = "SELECT id, section_name AS sectionName, assessment_structure_id AS questionPaperId, total_questions AS totalQuestions, no_of_questions_to_answer AS noOfQuestionsToAnswer, marks_per_question AS marksPerQuestion, instruction, seq_number AS seqNumber FROM assessment_structure_section WHERE assessment_structure_id = " . $questionPaperID . " ORDER BY " . $sortBy . " " . $sortOrder . ""; | |
| try { | |
| return $this->executeQueryForList($sql_section); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * Method for creating Question Paper Section | |
| * @param NBAQuestionPaperSection $nbaQuestionPaperSection | |
| * @throws ProfessionalException | |
| * @return object|NULL|\com\linways\base\util\$objectList[] | |
| */ | |
| public function createQuestionPaperSection($nbaQuestionPaperSection) | |
| { | |
| $nbaQuestionPaperSection = $this->realEscapeObject($nbaQuestionPaperSection); | |
| $sql = "INSERT INTO assessment_structure_section ( | |
| section_name, assessment_structure_id, total_questions, no_of_questions_to_answer, marks_per_question, instruction, seq_number,hide_default_instruction, createdBy ) | |
| VALUES ( | |
| '" . $nbaQuestionPaperSection->sectionName . "', | |
| '" . $nbaQuestionPaperSection->assessmentStructureId . "', | |
| '" . $nbaQuestionPaperSection->totalQuestions . "', | |
| '" . $nbaQuestionPaperSection->noOfQuestionsToAnswer . "', | |
| '" . $nbaQuestionPaperSection->marksPerQuestion . "', | |
| '" . $nbaQuestionPaperSection->instruction . "', | |
| '" . $nbaQuestionPaperSection->seqNumber . "', | |
| '" . $nbaQuestionPaperSection->hideDefaultInstruction . "', | |
| '" . $nbaQuestionPaperSection->createdBy . "' | |
| )"; | |
| try { | |
| return $this->executeQueryForObject($sql, true); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * Method for Updating Question Paper Section | |
| * (total_questions is updated by MySQL Trigger) | |
| * @param NBAQuestionPaperSection $nbaQuestionPaperSection | |
| * @throws ProfessionalException | |
| * @return object|NULL|\com\linways\base\util\$objectList[] | |
| */ | |
| public function updateQuestionPaperSection($nbaQuestionPaperSection) | |
| { | |
| $nbaQuestionPaperSection = $this->realEscapeObject($nbaQuestionPaperSection); | |
| $sql = "UPDATE assessment_structure_section SET | |
| section_name = '" . $nbaQuestionPaperSection->sectionName . "', | |
| no_of_questions_to_answer = '" . $nbaQuestionPaperSection->noOfQuestionsToAnswer . "', | |
| marks_per_question = '" . $nbaQuestionPaperSection->marksPerQuestion . "', | |
| instruction = '" . $nbaQuestionPaperSection->instruction . "', | |
| seq_number = '" . $nbaQuestionPaperSection->seqNumber . "', | |
| hide_default_instruction = '" . $nbaQuestionPaperSection->hideDefaultInstruction . "', | |
| updatedBy = '" . $nbaQuestionPaperSection->updatedBy . "' | |
| WHERE id = " . $nbaQuestionPaperSection->id . ""; | |
| try { | |
| return $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * Method for Deleting Question Paper Section | |
| * @param int $sectionID | |
| * @throws ProfessionalException | |
| * @return object|NULL|\com\linways\base\util\$objectList[] | |
| */ | |
| public function deleteQuestionPaperSection($sectionID) | |
| { | |
| $sql = "DELETE FROM assessment_structure_section WHERE id = " . $sectionID . ""; | |
| try { | |
| return $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| // Services for Handling questions in the Sections | |
| /** | |
| * Method for saving questions in a section | |
| * @param NBAQuestionPaperSection $nbaQuestionPaperSection | |
| * @param int $batchId | |
| * @throws ProfessionalException | |
| * @return object|NULL|\com\linways\base\util\$objectList[] | |
| */ | |
| public function saveQuestionsToSection($nbaQuestionPaperSection, $batchId) | |
| { | |
| $nbaQuestionPaperSection = $this->realEscapeObject($nbaQuestionPaperSection); | |
| $batchId = $this->realEscapeString($batchId); | |
| $questions = $nbaQuestionPaperSection->nbaSectionQuestions; | |
| try { | |
| $deptId = DepartmentService::getInstance()->getDepartmentByBatchId($batchId)->deptID; | |
| $coCalculation = CommonService::getInstance()->getOBESettings(OBESettingsConstents::NBA_CO_MAPPING, OBESettingsConstents::QUESTION_CO_MAPPING_CALCULATION, $deptId); | |
| $coCalculation = $coCalculation ? $coCalculation : 'PERCENT'; | |
| $sectionId = ''; | |
| if (count($questions)) { | |
| $sql = "INSERT INTO assessment_structure_section_questions (assessment_structure_section_id, assessment_question_id, seq_number) | |
| VALUES "; | |
| foreach ($questions as $key => $nbaSectionQuestion) { | |
| $sql .= "( | |
| '" . $nbaQuestionPaperSection->id . "', | |
| '" . $nbaSectionQuestion->questionId . "', | |
| '" . $nbaSectionQuestion->seqNumber . "' | |
| ),"; | |
| $sectionId = $nbaQuestionPaperSection->id; | |
| } | |
| $sql = substr($sql, 0, -1); | |
| $id = $this->executeQueryForObject($sql, TRUE); | |
| $sql_delete = "DELETE ncr.* FROM assessment_structure_question_co_relation ncr INNER JOIN assessment_structure_section_questions nsq ON nsq.id= ncr.assessment_structure_question_id WHERE nsq.assessment_structure_section_id=$sectionId"; | |
| $this->executeQueryForObject($sql_delete); | |
| if ($coCalculation == 'PERCENT') { | |
| $sql_co_map = "INSERT INTO assessment_structure_question_co_relation (assessment_structure_question_id, nba_course_outcome_id, course_outcome_value) SELECT nsq.id, nqc.nba_course_outcome_id, nqc.course_outcome_value FROM assessment_question_co_relation nqc INNER JOIN assessment_structure_section_questions nsq ON nsq.assessment_question_id=nqc.assessment_questions_id WHERE nsq.assessment_structure_section_id=$sectionId"; | |
| } else if ($coCalculation == 'MARKS') { | |
| $sql_co_map = "INSERT INTO assessment_structure_question_co_relation (assessment_structure_question_id, nba_course_outcome_id, course_outcome_value) SELECT nsq.id, nqc.nba_course_outcome_id,nqc.course_outcome_value FROM assessment_question_co_relation nqc INNER JOIN assessment_structure_section_questions nsq ON | |
| nsq.assessment_question_id=nqc.assessment_questions_id WHERE nsq.assessment_structure_section_id=$sectionId"; | |
| } | |
| $sql_qpFlag = "UPDATE assessment_structure qp INNER JOIN assessment_structure_section nsq ON nsq.assessment_structure_id=qp.id SET qp.isFinalised=0 WHERE nsq.id=$sectionId"; | |
| $this->executeQueryForObject($sql_qpFlag); | |
| return $this->executeQueryForObject($sql_co_map); | |
| } | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * delete questions from section | |
| * @param int $sectionID | |
| * @param array $deleteQuestionIDs | |
| * @throws ProfessionalException | |
| * @return object|NULL|\com\linways\base\util\$objectList[] | |
| */ | |
| public function deleteQuestionsFromSection($sectionID, $deleteQuestionIDs = []) | |
| { | |
| $sectionID = $this->realEscapeString($sectionID); | |
| $deleteQuestionIDs = $this->realEscapeArray($deleteQuestionIDs); | |
| try { | |
| if (count($deleteQuestionIDs)) { | |
| $questionIDs = implode(',', $deleteQuestionIDs); | |
| $sql = "DELETE FROM assessment_structure_section_questions WHERE assessment_structure_section_id = " . $sectionID . " AND assessment_question_id IN (" . $questionIDs . ")"; | |
| } else { | |
| $sql = "DELETE FROM assessment_structure_section_questions WHERE assessment_structure_section_id = " . $sectionID . ""; | |
| } | |
| $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException(ProfessionalException::MARK_ALREADY_ENTERED, $e->getMessage()); | |
| } | |
| try{ | |
| $sql_qpFlag = "UPDATE assessment_structure qp INNER JOIN assessment_structure_section nsq ON nsq.assessment_structure_id=qp.id SET qp.isFinalised=0 WHERE nsq.id=$sectionID"; | |
| return $this->executeQueryForObject($sql_qpFlag); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * Method for updating questions in a section | |
| * @param NBAQuestionPaperSection $nbaQuestionPaperSection | |
| * @return void $objectList[] | |
| * @throws ProfessionalException | |
| */ | |
| public function updateQuestionInSection($nbaQuestionPaperSection) | |
| { | |
| $nbaQuestionPaperSection = $this->realEscapeObject($nbaQuestionPaperSection); | |
| $questions = $nbaQuestionPaperSection->nbaSectionQuestions; | |
| try { | |
| if (count($questions)) { | |
| foreach ($questions as $key => $nbaSectionQuestion) { | |
| $sql = "UPDATE assessment_structure_section_questions SET seq_number = '" . $nbaSectionQuestion->seqNumber . "' WHERE assessment_structure_section_id = '" . $nbaQuestionPaperSection->id . "' AND assessment_question_id = '" . $nbaSectionQuestion->questionId . "'"; | |
| $this->executeQueryForObject($sql); | |
| } | |
| } | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * Method for getting all questions in a section - by passing Section Id | |
| * @param int $sectionID | |
| * @param string $sortBy | |
| * @param string $sortOrder | |
| * @return object|array|\com\linways\base\util\$objectList[] | |
| * @throws ProfessionalException | |
| * @throws \com\linways\base\exception\CoreException | |
| */ | |
| public function getSectionQuestionsById($sectionID, $sortBy = "seq_number", $sortOrder = "ASC") | |
| { | |
| $sectionID = $this->realEscapeString($sectionID); | |
| $sortBy = $this->realEscapeString($sortBy); | |
| $sortOrder = $this->realEscapeString($sortOrder); | |
| // Validate sortOrder and sortBy | |
| RequestUtil::validateSortOrder($sortBy, $sortOrder); | |
| $sql_sec_ques = "SELECT ncoq.id, ncoq.question, ncoq.mark, ncoq.attachments, nsq.seq_number as seqNumber FROM assessment_questions ncoq JOIN assessment_structure_section_questions nsq ON (ncoq.id = nsq.assessment_question_id) WHERE nsq.assessment_structure_section_id = " . $sectionID . " ORDER BY " . $sortBy . " " . $sortOrder . ""; | |
| try { | |
| return $this->executeQueryForList($sql_sec_ques); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * Method for getting all questions according to mark | |
| * @param getAllQuestionRequest $getAllQuestionRequest | |
| * @throws ProfessionalException | |
| * @return object|array|\com\linways\base\util\$objectList[] | |
| */ | |
| public function getAllQuestionsByMark($getAllQuestionRequest) | |
| { | |
| try { | |
| $sql_ques = "SELECT id, question, mark, attachments FROM assessment_questions WHERE subjectID = " . $getAllQuestionRequest->subjectId . " AND mark = '" . $getAllQuestionRequest->mark . "' AND (sbs_id = (SELECT sbsID FROM sbs_relation WHERE staffID = " . $getAllQuestionRequest->staffId . " AND batchID = " . $getAllQuestionRequest->batchId . " AND semID = " . $getAllQuestionRequest->semId . " AND subjectID = " . $getAllQuestionRequest->subjectId . ") OR public_question = 1)"; | |
| return $this->executeQueryForList($sql_ques); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * Method for getting all questions along with selected question according to mark | |
| * @param GetAllQuestionRequest $getAllQuestionRequest | |
| * @throws ProfessionalException | |
| * @return object|array|\com\linways\base\util\$objectList[] | |
| */ | |
| public function getQuestions($getAllQuestionRequest) | |
| { | |
| $questions = []; | |
| try { | |
| $sql_ques = "SELECT ncoq.id, ncoq.question, ncoq.mark, ncoq.attachments, nsq.seq_number AS seqNumber, nsq.id AS isSelected FROM assessment_questions ncoq LEFT JOIN assessment_structure_section_questions nsq ON (ncoq.id = nsq.assessment_question_id AND nsq.assessment_structure_section_id = " . $getAllQuestionRequest->sectionId . ") WHERE ncoq.subjectID = " . $getAllQuestionRequest->subjectId . " AND ncoq.mark = '" . $getAllQuestionRequest->mark . "' AND (ncoq.sbs_id = (SELECT sbsID FROM sbs_relation WHERE staffID = " . $getAllQuestionRequest->staffId . " AND batchID = " . $getAllQuestionRequest->batchId . " AND semID = " . $getAllQuestionRequest->semId . " AND subjectID = " . $getAllQuestionRequest->subjectId . ")) AND ncoq.id NOT IN (SELECT | |
| asq.assessment_questions_id | |
| FROM | |
| assessment_structure_questions asq | |
| INNER JOIN | |
| assessment_structure ase ON (ase.id = asq.assessment_structure_id | |
| AND ase.assessment_type = 'ASSIGNMENT'))"; | |
| return $this->executeQueryForList($sql_ques); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| public function enterMarkView($questionPaperID) | |
| { | |
| } | |
| /** | |
| * get question related co name | |
| * @param int $qId | |
| * @throws ProfessionalException | |
| * @return string | |
| */ | |
| public function getQuestionCoNames($qId) | |
| { | |
| $coName = ''; | |
| try { | |
| $sql = "select group_concat(nco.code) as coName from assessment_question_co_relation nqcr INNER JOIN nba_course_outcome nco ON nco.id=nqcr.nba_course_outcome_id WHERE nqcr.assessment_questions_id=$qId"; | |
| $coName = $this->executeQueryForObject($sql)->coName; | |
| return $coName; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * check question paper co finalised or not | |
| * @param int $questionPaperId | |
| * @throws ProfessionalException | |
| * @return boolean | |
| */ | |
| public function questionPaperIsFinalised($questionPaperId) | |
| { | |
| $questionPaperId = $this->realEscapeString($questionPaperId); | |
| $flag = FALSE; | |
| $sql = "SELECT isFinalised FROM assessment_structure WHERE id=$questionPaperId"; | |
| try { | |
| $isFinalised = $this->executeQueryForObject($sql)->isFinalised; | |
| if ($isFinalised) { | |
| $flag = TRUE; | |
| } | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $flag; | |
| } | |
| /** | |
| * Toggle question paper to public | |
| * @param int $questionPaperId | |
| * @param int $value | |
| * @throws ProfessionalException | |
| * @return \com\linways\base\dto\MySqlResult | |
| */ | |
| public function toggleQuestionPaperToPublic($questionPaperId, $value) | |
| { | |
| $questionPaperId = $this->realEscapeString($questionPaperId); | |
| $value = $this->realEscapeString($value); | |
| $sql = "UPDATE assessment_structure SET isPublic = $value WHERE id = $questionPaperId"; | |
| try { | |
| return $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * Get question paper details by questionId | |
| * @param int $questionPaperId | |
| * @throws ProfessionalException | |
| * @return object|array|\com\linways\base\util\$objectList[] | |
| */ | |
| public function getQuestionPaperDetailsById($questionPaperId) | |
| { | |
| $questionPaperId = $this->realEscapeString($questionPaperId); | |
| $sql = "SELECT | |
| qp.id AS assessment_structure_id, | |
| exam_type_id AS assessment_structure_exam_type_id, | |
| header AS assessment_structure_header, | |
| qpcode AS assessment_structure_qpcode, | |
| qp.subjectID AS assessment_structure_subjectID, | |
| qp.staffID AS assessment_structure_staffID, | |
| qp.batchID AS assessment_structure_batchID, | |
| qp.semID AS assessment_structure_semID, | |
| maxMark AS assessment_structure_maxMark, | |
| duration AS assessment_structure_duration, | |
| subbatchID AS assessment_structure_subbatchID, | |
| isPublic AS assessment_structure_isPublic, | |
| qps.id AS assessment_structure_section_id, | |
| section_name AS assessment_structure_section_section_name, | |
| assessment_structure_id AS assessment_structure_section_assessment_structure_id, | |
| total_questions AS assessment_structure_section_total_questions, | |
| no_of_questions_to_answer AS assessment_structure_section_no_of_questions_to_answer, | |
| marks_per_question AS assessment_structure_section_marks_per_question, | |
| instruction AS assessment_structure_section_instruction, | |
| qps.seq_number AS assessment_structure_section_seq_number, | |
| qps.hide_default_instruction as hideDefaultInstruction, | |
| sq.id AS assessment_structure_section_questions_id, | |
| assessment_structure_section_id AS assessment_structure_section_questions_section_id, | |
| sq.assessment_question_id AS assessment_structure_section_questions_question_id, | |
| sq.seq_number AS assessment_structure_section_questions_seq_number, | |
| quest.id AS assessment_questions_id, | |
| question AS assessment_questions_question, | |
| mark AS assessment_questions_mark, | |
| attachments AS assessment_questions_attachments, | |
| quest.subjectID AS assessment_questions_subjectID, | |
| public_question AS assessment_questions_public_question, | |
| attName AS assessment_questions_attName, | |
| sbs_id AS assessment_questions_sbs_id, | |
| nco.id AS coId, | |
| nco.code AS code, | |
| nco.code AS coValue, | |
| nco.id AS course_outcome_id, | |
| nco.code AS course_outcome_code, | |
| qco.course_outcome_value AS course_outcome_value, | |
| quest.id AS question_id, | |
| qps.id AS question_paper_section_id, | |
| quest.lin_resource_id AS linResourceId, | |
| rwa.id question_rubric_id, | |
| rwa.assessment_question_id as assessment_question_id_rubric , | |
| rwa.is_percentage, | |
| rwacd.id rubric_criteria_id, | |
| rwacd.rubric_wise_assessment_id, | |
| rwacd.criteria, | |
| rwacd.value_and_justification | |
| FROM | |
| assessment_structure qp | |
| LEFT JOIN | |
| assessment_structure_section qps ON qp.id = qps.assessment_structure_id | |
| LEFT JOIN | |
| assessment_structure_section_questions sq ON sq.assessment_structure_section_id = qps.id | |
| LEFT JOIN | |
| assessment_questions quest ON quest.id = sq.assessment_question_id | |
| AND qp.subjectID = quest.subjectID | |
| LEFT JOIN | |
| assessment_structure_question_co_relation qco ON qco.assessment_structure_question_id = sq.id | |
| LEFT JOIN | |
| nba_course_outcome nco ON nco.id = qco.nba_course_outcome_id | |
| LEFT JOIN | |
| rubric_wise_assessment rwa ON rwa.assessment_question_id = quest.id | |
| LEFT JOIN | |
| rubric_wise_assessment_criteria_details rwacd ON (rwacd.rubric_wise_assessment_id = rwa.id) | |
| WHERE | |
| qp.id = $questionPaperId"; | |
| try { | |
| return $this->executeQueryForObject($sql, false, $this->mapper[QuestionPaperServiceMapper::GET_QUESTION_PAPER_DETAILS]); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * Check if question paper exists for an examtype | |
| * @param int $examTypeId | |
| * @param int $batchId | |
| * @param int $semId | |
| * @param int $subatchId | |
| * @param int $subjectId | |
| * @throws ProfessionalException | |
| * @return boolean | |
| */ | |
| public function checkIfQuestPaperExistsForExamType($examTypeId, $batchId, $semId, $subatchId, $subjectId) | |
| { | |
| $examTypeId = $this->realEscapeString($examTypeId); | |
| $sql = "SELECT | |
| id , | |
| header, | |
| qpcode, | |
| staffID, | |
| maxMark, | |
| duration, | |
| isPublic | |
| FROM | |
| assessment_structure | |
| WHERE | |
| exam_type_id = $examTypeId and batchID = $batchId and semID = $semId and subbatchID = $subatchId and subjectID = $subjectId"; | |
| try { | |
| $questionPaper = $this->executeQueryForObject($sql); | |
| if (empty($questionPaper)) { | |
| return false; | |
| } | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return true; | |
| } | |
| /** | |
| * Create question paper | |
| * @param int $questionPaper | |
| * @throws ProfessionalException | |
| * @return object|NULL|\com\linways\base\util\$objectList[] | |
| */ | |
| public function createQuestionPaper($questionPaper) | |
| { | |
| $questionPaper = $this->realEscapeObject($questionPaper); | |
| $sql = "INSERT INTO assessment_structure (exam_type_id, header, qpcode, subjectID, staffID, batchID, semID, maxMark, duration, subbatchID, isPublic, assessment_key, assessment_type) VALUE ($questionPaper->examTypeId, '$questionPaper->header', '$questionPaper->questionPaperCode', '$questionPaper->subjectId', '$questionPaper->staffId', '$questionPaper->batchId', '$questionPaper->semId', '$questionPaper->maxMark', '$questionPaper->duration', '$questionPaper->subbatchId', '$questionPaper->isPublic', '$questionPaper->examTypeId', 'EXAM')"; | |
| try { | |
| return $this->executeQueryForObject($sql, true); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * TODO : Remove DTO from function argument and create Request Class corresponding to the DTO | |
| * create question | |
| * @param NBAQuestion $question | |
| * @throws ProfessionalException | |
| * @return object|NULL|\com\linways\base\util\$objectList[] | |
| */ | |
| public function createQuestion(NBAQuestion $question) | |
| { | |
| $question = $this->realEscapeObject($question); | |
| if (!empty($question->attachments)) { | |
| $question->attName = $question->attName . '.' . substr($question->attachments, (strripos($question->attachments, ".")) + 1); | |
| } | |
| $linResourceId = 'NULL'; | |
| if(!empty($question->linResourceId)) | |
| { | |
| $linResourceId = "'".$question->linResourceId."'"; | |
| } | |
| $psQuestionKey = 'NULL'; | |
| if ( !empty ( $question->psQuestionKey ) ) { | |
| $psQuestionKey = "'".$question->psQuestionKey."'"; | |
| } | |
| $sql = "INSERT INTO assessment_questions (question, mark, attachments, subjectID, attName, sbs_id,blooms_level,lin_resource_id, ps_question_key) | |
| VALUE ('" . $question->question . "', '" . $question->mark . "', '" . $question->attachments . "', " | |
| . $question->subjectID . ",'" . $question->attName . "', " . $question->sbsId . ",'" . | |
| $question->bloomsLevel . "', " . $linResourceId . ", ".$psQuestionKey." )"; | |
| try { | |
| return $this->executeQueryForObject($sql, true); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * Copy question paper | |
| * @param NBAQuestion $questionPaper | |
| * @return \com\linways\base\util\|NULL|object | |
| * @throws ProfessionalException | |
| */ | |
| public function copyQuestionPaper($questionPaper) | |
| { | |
| $questionPaper = $this->realEscapeObject($questionPaper); | |
| try { | |
| if (!$this->checkIfQuestPaperExistsForExamType($questionPaper->examTypeId, $questionPaper->batchId, $questionPaper->semId, $questionPaper->subbatchId, $questionPaper->subjectId)) { | |
| if ($questionPaper->subbatchId == 0) { | |
| $sql_subcheck = "SELECT id FROM assessment_structure WHERE exam_type_id = $questionPaper->examTypeId and batchID = $questionPaper->batchId and semID = $questionPaper->semId and subbatchID != 0 and subjectID = $questionPaper->subjectId and assessment_type = 'EXAM'"; | |
| if (!empty($this->executeQueryForObject($sql_subcheck))) { | |
| throw new ProfessionalException("QUESTION_PAPER_ALREADY_EXISTS_FOR_THE_EXAM_TYPE", "Question paper for this exam type already exists for subbatch"); | |
| } | |
| } else { | |
| $sql_subcheck = "SELECT id FROM assessment_structure WHERE exam_type_id = $questionPaper->examTypeId and batchID = $questionPaper->batchId and semID = $questionPaper->semId and subbatchID = 0 and subjectID = $questionPaper->subjectId and assessment_type = 'EXAM'"; | |
| if (!empty($this->executeQueryForObject($sql_subcheck))) { | |
| throw new ProfessionalException("QUESTION_PAPER_ALREADY_EXISTS_FOR_THE_EXAM_TYPE", "Question paper for this exam type already exists for all subbatch."); | |
| } | |
| } | |
| $questionPaperDetails = $this->getQuestionPaperDetailsById($questionPaper->id); | |
| $sbsId = SubjectService::getInstance()->getSBSIdBySubjectIdAndStaffIdAndSemId($questionPaper->subjectId, $questionPaper->batchId, $questionPaper->staffId); | |
| if (!empty($questionPaperDetails)) { | |
| $copiedQuestnPaperId = $questionPaperDetails->id; | |
| $questionPaper->id = $this->createQuestionPaper($questionPaper); | |
| $sql_copiedQpInfo = "insert into copied_assessment_structure_info (sbsId, copied_assessment_structure_Id, new_assessment_structure_id, createdBy, createdDate, updatedBy, updatedDate) VALUE ($sbsId, $copiedQuestnPaperId, $questionPaper->id, $questionPaper->staffId, UTC_TIMESTAMP(), $questionPaper->staffId, UTC_TIMESTAMP()) "; | |
| $copiedInfoId = $this->executeQueryForObject($sql_copiedQpInfo, true); | |
| $nbaQuestionPaperSections = $questionPaperDetails->nbaQuestionPaperSection; | |
| foreach ($nbaQuestionPaperSections as $nbaQuestionPaperSection) { | |
| $nbaQuestionPaperSectionObj = new NBAQuestionPaperSection(); | |
| $nbaQuestionPaperSectionObj->assessmentStructureId = $questionPaper->id; | |
| $nbaQuestionPaperSectionObj->sectionName = $nbaQuestionPaperSection->sectionName; | |
| $nbaQuestionPaperSectionObj->totalQuestions = $nbaQuestionPaperSection->totalQuestions; | |
| $nbaQuestionPaperSectionObj->noOfQuestionsToAnswer = $nbaQuestionPaperSection->noOfQuestionsToAnswer; | |
| $nbaQuestionPaperSectionObj->marksPerQuestion = $nbaQuestionPaperSection->marksPerQuestion; | |
| $nbaQuestionPaperSectionObj->instruction = $nbaQuestionPaperSection->instruction; | |
| $nbaQuestionPaperSectionObj->seqNumber = $nbaQuestionPaperSection->seqNumber; | |
| $nbaQuestionPaperSectionObj->hideDefaultInstruction = $nbaQuestionPaperSection->hideDefaultInstruction; | |
| $nbaQuestionPaperSectionObj->createdBy = $nbaQuestionPaperSection->createdBy; | |
| $nbaQuestionPaperSection->id = $this->createQuestionPaperSection($nbaQuestionPaperSectionObj); | |
| $nbaSectionQuestions = $nbaQuestionPaperSection->nbaSectionQuestions; | |
| foreach ($nbaSectionQuestions as $nbaSectionQuestion) { | |
| $cpyQuestId = $nbaSectionQuestion->question->id; | |
| $question = $nbaSectionQuestion->question; | |
| $question->question = htmlspecialchars_decode($question->question); | |
| $question->subjectId = $questionPaper->subjectId; | |
| $question->sbsId = $sbsId; | |
| $question->id = $this->createQuestion($question); | |
| $sql_copiedQustInfo = "INSERT INTO copied_assessment_question_info(sbsId,copiedQuestionId,newQuestionId,createdBy,createdDate,updatedBy,updatedDate) VALUES($sbsId,$cpyQuestId,$question->id,$questionPaper->staffId, UTC_TIMESTAMP(), $questionPaper->staffId, UTC_TIMESTAMP())"; | |
| $copiedQuestInfoId = $this->executeQueryForObject($sql_copiedQustInfo, true); | |
| $nbaSectionQuestion->questionId = $question->id; | |
| $nbaSectionQuestion->questionPaperSectionId = $nbaQuestionPaperSection->id; | |
| } | |
| $this->saveQuestionsToSection($nbaQuestionPaperSection, $questionPaper->batchId); | |
| } | |
| } else { | |
| throw new ProfessionalException("NO_SECTIONS_OR_QUESTIONS", "Question paper cannot be copied. Sections or questions not added"); | |
| } | |
| } else { | |
| throw new ProfessionalException("QUESTION_PAPER_ALREADY_EXISTS_FOR_THE_EXAM_TYPE", "Question paper for this exam type already exists"); | |
| } | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $questionPaper->id; | |
| } | |
| /** | |
| * Get all questions from subject community | |
| * @param int $subjectId | |
| * @param int $batchId | |
| * @param int $semId | |
| * @param int $staffId | |
| * @throws ProfessionalException | |
| * @return object|array|\com\linways\base\util\$objectList[] | |
| */ | |
| public function getQuestionPapersFromSubjComm($subjectId, $sbsId) | |
| { | |
| $subjectId = $this->realEscapeString($subjectId); | |
| $sbsId = $this->realEscapeString($sbsId); | |
| $sql = "SELECT qp.id AS assessment_structure_id, | |
| exam_type_id AS assessment_structure_exam_type_id, | |
| header AS assessment_structure_header, | |
| qpcode AS assessment_structure_qpcode, | |
| qp.subjectID AS assessment_structure_subjectID, | |
| qp.staffID AS assessment_structure_staffID, | |
| qp.batchID AS assessment_structure_batchID, | |
| qp.semID AS assessment_structure_semID, | |
| maxMark AS assessment_structure_maxMark, | |
| duration AS assessment_structure_duration, | |
| qp.subbatchID AS assessment_structure_subbatchID, | |
| isPublic AS assessment_structure_isPublic, et.typeName, staffName, qp.batchID, batchName, sr.sbsID, cpyInfo.sbsId, copied_assessment_structure_Id as copiedQuestionPaperId, new_assessment_structure_id as newQuestionPaperId FROM assessment_structure qp inner join exam_type et on qp.exam_type_id = et.typeID inner join sbs_relation sr on sr.subjectID = qp.subjectID and sr.batchID = qp.batchID and sr.semID = qp.semID and sr.staffID = qp.staffID inner join staffaccounts sa on sa.staffID = qp.staffID inner join batches ba on ba.batchID = qp.batchID left join copied_assessment_structure_info cpyInfo on cpyInfo.sbsID = $sbsId and copied_assessment_structure_Id = qp.id WHERE qp.subjectID = $subjectId AND sr.sbsID != $sbsId and isPublic = 1 and qp.assessment_type = 'EXAM' ORDER BY qp.id DESC"; | |
| try { | |
| return $this->executeQueryForList($sql, $this->mapper[QuestionPaperServiceMapper::GET_SUBJECT_COMM_QUEST_PAPER]); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * Get all co details of a subject in a batch | |
| * @param int $subjectId | |
| * @param int $batchId | |
| * @param int $subjectId | |
| * @throws ProfessionalException | |
| * @return object|array|\com\linways\base\util\$objectList[] | |
| */ | |
| public function getAllCODetailsOfAsubjInABatch($subjectId, $batchId, $semId) | |
| { | |
| $batchId = $this->realEscapeString($batchId); | |
| $subjectId = $this->realEscapeString($subjectId); | |
| $semId = $this->realEscapeString($semId); | |
| $sql = "SELECT id, code, objective, order_no FROM nba_course_outcome WHERE batchID = $batchId AND semID = $semId AND subjectID = $subjectId ORDER BY order_no ASC"; | |
| try { | |
| return $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * Get all question co values | |
| * @param int $questionId | |
| * @throws ProfessionalException | |
| * @return object|array|\com\linways\base\util\$objectList[] | |
| */ | |
| public function getAllQuestionCoValues($questionId) | |
| { | |
| $questionId = $this->realEscapeString($questionId); | |
| $sql = "SELECT nba_course_outcome_id, course_outcome_value FROM assessment_question_co_relation WHERE assessment_questions_id = $questionId"; | |
| try { | |
| return $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * Get question paper copied info | |
| * @param int $copiedQuestionpaperId | |
| * @param int $sbsId | |
| * @throws ProfessionalException | |
| * @return object|NULL|\com\linways\base\util\$objectList[] | |
| */ | |
| public function getQuestionPaperCopiedInfo($copiedQuestionpaperId, $sbsId) | |
| { | |
| $copiedQuestionpaperId = $this->realEscapeString($copiedQuestionpaperId); | |
| $sbsId = $this->realEscapeString($sbsId); | |
| $sql = "select * from copied_assessment_structure_info where copied_assessment_structure_Id = $copiedQuestionpaperId and sbsId = $sbsId"; | |
| try { | |
| return $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * Get question copied info | |
| * @param int $newQuestionId | |
| * @param int $sbsId | |
| * @throws ProfessionalException | |
| * @return object|NULL|\com\linways\base\util\$objectList[] | |
| */ | |
| public function getQuestionCopiedInfo($newQuestionId, $sbsId) | |
| { | |
| $newQuestionId = $this->realEscapeString($newQuestionId); | |
| $sbsId = $this->realEscapeString($sbsId); | |
| $sql = "select * from copied_assessment_question_info where newQuestionId = $newQuestionId and sbsId = $sbsId"; | |
| try { | |
| return $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * create co question relation | |
| * @param int $questionId | |
| * @param array $coVals | |
| * @throws ProfessionalException | |
| * @return object|NULL|\com\linways\base\util\$objectList[] | |
| */ | |
| public function createNbaQuestionCORelation($questionId, $coVals) | |
| { | |
| $sql = "INSERT INTO assessment_question_co_relation(assessment_questions_id, nba_course_outcome_id, course_outcome_value) VALUES "; | |
| foreach ($coVals as $coId => $value) { | |
| $sql .= "($questionId, $coId, $value), "; | |
| } | |
| $sql = rtrim($sql, ", "); | |
| try { | |
| return $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /*** | |
| * create section question co reation while copying | |
| */ | |
| public function createNbaQuestionPaperQuestionCORelation($sectionId, $deptId, $coCalculation) | |
| { | |
| try { | |
| $sql_delete = "DELETE ncr.* FROM assessment_structure_question_co_relation ncr INNER JOIN assessment_structure_section_questions nsq ON nsq.id= ncr.assessment_structure_question_id WHERE nsq.assessment_structure_section_id=$sectionId"; | |
| $this->executeQueryForObject($sql_delete); | |
| if ($coCalculation == 'PERCENT') { | |
| $sql_co_map = "INSERT INTO assessment_structure_question_co_relation (assessment_structure_question_id, nba_course_outcome_id, course_outcome_value) SELECT nsq.id, nqc.nba_course_outcome_id, nqc.course_outcome_value FROM assessment_question_co_relation nqc INNER JOIN assessment_structure_section_questions nsq ON nsq.assessment_question_id=nqc.assessment_questions_id WHERE nsq.assessment_structure_section_id=$sectionId"; | |
| } else if ($coCalculation == 'MARKS') { | |
| $sql_co_map = "INSERT INTO assessment_structure_question_co_relation (assessment_structure_question_id, nba_course_outcome_id) SELECT nsq.id, nqc.nba_course_outcome_id FROM assessment_question_co_relation nqc INNER JOIN assessment_structure_section_questions nsq ON | |
| nsq.assessment_question_id=nqc.assessment_questions_id WHERE nsq.assessment_structure_section_id=$sectionId"; | |
| } | |
| return $this->executeQueryForObject($sql_co_map); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * @param $questionId | |
| * @throws ProfessionalException | |
| */ | |
| public function deleteAllCORelationByQuestionID($questionId) | |
| { | |
| $sql = "DELETE FROM assessment_question_co_relation WHERE assessment_questions_id=$questionId"; | |
| try { | |
| $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * get question added finalised question paper deatils | |
| * @param int $questionId | |
| * @throws ProfessionalException | |
| * @return object|array|\com\linways\base\util\$objectList[] | |
| */ | |
| public function getFinalisedQuestionPaperDetailsByQuestionId($questionId) | |
| { | |
| $questionId = $this->realEscapeString($questionId); | |
| $sql = "SELECT nsq.assessment_structure_section_id as sectionId, ncqp.batchID, ncqp.id as qpId | |
| FROM assessment_structure_section_questions nsq | |
| INNER JOIN assessment_structure_section nqps ON nqps.id=nsq.assessment_structure_section_id | |
| INNER JOIN assessment_structure ncqp ON nqps.assessment_structure_id=ncqp.id | |
| WHERE ncqp.isFinalised=1 AND nsq.assessment_question_id=$questionId"; | |
| try { | |
| return $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * update question paper section co relation | |
| * @param int $sectionId | |
| * @param int $batchId | |
| * @return object|NULL|\com\linways\base\util\$objectList[] | |
| * @throws ProfessionalException | |
| */ | |
| public function updateQuestionPaperSectionCoRelation($sectionId, $batchId) | |
| { | |
| $sectionId = $this->realEscapeString($sectionId); | |
| $batchId = $this->realEscapeString($batchId); | |
| $deptId = DepartmentService::getInstance()->getDepartmentByBatchId($batchId)->deptID; | |
| $coCalculation = CommonService::getInstance()->getOBESettings(OBESettingsConstents::NBA_CO_MAPPING, OBESettingsConstents::QUESTION_CO_MAPPING_CALCULATION, $deptId); | |
| $coCalculation = $coCalculation ? $coCalculation : 'PERCENT'; | |
| try { | |
| $sql_delete = "DELETE ncr.* FROM assessment_structure_question_co_relation ncr INNER JOIN assessment_structure_section_questions nsq ON nsq.id= ncr.assessment_structure_question_id WHERE nsq.assessment_structure_section_id=$sectionId"; | |
| $this->executeQueryForObject($sql_delete); | |
| if ($coCalculation == 'PERCENT') { | |
| $sql_co_map = "INSERT INTO assessment_structure_question_co_relation (assessment_structure_question_id, nba_course_outcome_id, course_outcome_value) SELECT nsq.id, nqc.nba_course_outcome_id, nqc.course_outcome_value FROM assessment_question_co_relation nqc INNER JOIN assessment_structure_section_questions nsq ON nsq.assessment_question_id=nqc.assessment_questions_id WHERE nsq.assessment_structure_section_id=$sectionId"; | |
| } else if ($coCalculation == 'MARKS') { | |
| $sql_co_map = "INSERT INTO assessment_structure_question_co_relation (assessment_structure_question_id, nba_course_outcome_id) SELECT nsq.id, nqc.nba_course_outcome_id FROM assessment_question_co_relation nqc INNER JOIN assessment_structure_section_questions nsq ON | |
| nsq.assessment_question_id=nqc.assessment_questions_id WHERE nsq.assessment_structure_section_id=$sectionId"; | |
| } | |
| return $this->executeQueryForObject($sql_co_map); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * get question added question paper deatils | |
| * @param int $questionId | |
| * @throws ProfessionalException | |
| * @return object|array|\com\linways\base\util\$objectList[] | |
| */ | |
| public function getQuestionPaperDetailsByQuestionId($questionId) | |
| { | |
| $questionId = $this->realEscapeString($questionId); | |
| $sql = "select nsq.assessment_structure_section_id as sectionId, ncqp.batchID, ncqp.id as qpId from assessment_structure_section_questions nsq INNER JOIN assessment_structure_section nqps ON nqps.id=nsq.assessment_structure_section_id INNER JOIN assessment_structure ncqp ON nqps.assessment_structure_id=ncqp.id WHERE nsq.assessment_question_id=$questionId"; | |
| try { | |
| return $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * Submit question paper to exam controller | |
| * @param int $questionPaperId | |
| * @param int $value | |
| * @throws ProfessionalException | |
| * @return object|NULL|\com\linways\base\util\$objectList[] | |
| */ | |
| public function submitQuestionPaperToExamController($questionPaperId, $value) | |
| { | |
| $questionPaperId = $this->realEscapeString($questionPaperId); | |
| $value = $this->realEscapeString($value); | |
| $sql = "UPDATE assessment_structure SET isSubmitted = " . $value . " WHERE id = " . $questionPaperId . ""; | |
| try { | |
| return $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * Get question details by questionId | |
| * @param int $questionId | |
| * @throws ProfessionalException | |
| * @return object|NULL|\com\linways\base\util\$objectList[] | |
| */ | |
| public function getQuestionByQuestionId($questionId) | |
| { | |
| $questionId = $this->realEscapeString($questionId); | |
| $sql = "select ncq.id, ncq.question, ncq.attachments,ncq.mark, nc.id as coId, nc.code, objective from assessment_questions ncq left join assessment_question_co_relation nco on nco.assessment_questions_id = ncq.id left join nba_course_outcome nc on nc.id = nco.nba_course_outcome_id WHERE ncq.id = " . $questionId . ""; | |
| try { | |
| return $this->executeQueryForList($sql, $this->mapper[QuestionPaperServiceMapper::GET_QUESTION]); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * Get total count of questions in question paper | |
| * @param int $questionPaperId | |
| * @throws ProfessionalException | |
| * @return unknown | |
| */ | |
| public function getTotalCountOfQuestionsInQP($questionPaperId) | |
| { | |
| $questionPaperId = $this->realEscapeString($questionPaperId); | |
| $questionPaperId = $this->realEscapeString($questionPaperId); | |
| $sql = "select count(assessment_questions_id) as count from assessment_structure_questions where assessment_structure_id = " . $questionPaperId . ""; | |
| try { | |
| return $this->executeQueryForObject($sql)->count; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * @param UpdateQuestionRequest $request | |
| * @throws ProfessionalException | |
| */ | |
| public function updateQuestion(UpdateQuestionRequest $request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| if (!empty($request->attachmentPath)) { | |
| $request->attachmentName = $request->attachmentName . '.' . substr($request->attachmentPath, (strripos($request->attachmentPath, ".")) + 1); | |
| } | |
| $linRes = ''; | |
| if(!empty($request->linResourceId)) | |
| { | |
| $linResourceId = "'".$request->linResourceId."'"; | |
| $linRes = ", lin_resource_id = " . $linResourceId . ""; | |
| } | |
| $sql = "UPDATE assessment_questions | |
| SET question = '" . $request->question . "', | |
| mark = '" . $request->mark . "', | |
| blooms_level = '" . $request->bloomsLevel . "', | |
| attachments = '" . $request->attachmentPath . "', | |
| attName ='" . $request->attachmentName . "' $linRes | |
| WHERE id = " . $request->questionId; | |
| try { | |
| $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| public function getQuestionPaperCOPOMappingsByQuestionPaperId($questionId) | |
| { | |
| $questionId = $this->realEscapeString($questionId); | |
| $sql = "SELECT questionPaper.id as question_paper_id,questionPaper.qpcode as question_paper_code, | |
| questionSection.id as question_paper_section_id, questionSection.section_name, | |
| questionSection.seq_number as section_sequence_number, | |
| sectionQuestion.seq_number as question_paper_sequence_number, | |
| question.id as question_id,question.blooms_level,question.question, | |
| courseOutcome.id as course_outcome_id,courseOutcome.code as course_outcome_code, | |
| programmeOutcome.id as programme_outcome_id,programmeOutcome.poCode as programme_outcome_code, | |
| programmeSpecific.id as programme_specific_outcome_id, programmeSpecific.poCode as programme_specific_outcome_code | |
| FROM assessment_structure questionPaper | |
| INNER JOIN assessment_structure_section questionSection ON questionPaper.id = questionSection.assessment_structure_id | |
| INNER JOIN assessment_structure_section_questions sectionQuestion ON sectionQuestion.assessment_structure_section_id = questionSection.id | |
| INNER JOIN assessment_questions question ON question.id = sectionQuestion.assessment_question_id | |
| LEFT JOIN assessment_question_co_relation questionCoRelation ON questionCoRelation.assessment_questions_id = question.id | |
| LEFT JOIN nba_course_outcome courseOutcome ON courseOutcome.id=questionCoRelation.nba_course_outcome_id | |
| LEFT JOIN nba_co_po_relation coPoRelation ON coPoRelation.nba_course_outcome_id = courseOutcome.id | |
| LEFT JOIN nba_program_outcome programmeOutcome ON programmeOutcome.id = coPoRelation.nba_program_outcome_id | |
| AND programmeOutcome.isPSO = 0 | |
| LEFT JOIN nba_program_outcome programmeSpecific ON programmeSpecific.id= coPoRelation.nba_program_outcome_id | |
| AND programmeSpecific.isPSO =1 | |
| WHERE questionPaper.id = $questionId ORDER BY questionSection.seq_number,sectionQuestion.seq_number;"; | |
| try { | |
| return $this->executeQueryForObject($sql, false, $this->mapper[QuestionPaperServiceMapper::GET_QUESTION_PAPER_CO_PO_MAPPINGS_BY_QUESTION_PAPER_ID]); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| public function getAllQuestionPaperByStaffIdAndSubjectId($batchID,$semID,$subjectID,$staffID){ | |
| $batchID = $this->realEscapeString($batchID); | |
| $semID = $this->realEscapeString($semID); | |
| $subjectID = $this->realEscapeString($subjectID); | |
| $staffID = $this->realEscapeArray($staffID); | |
| $sql = "SELECT | |
| nco.id, | |
| nco.header, | |
| nco.qpcode, | |
| et.typeName, | |
| nco.maxMark, | |
| nco.duration, | |
| nco.isPublic, | |
| nco.isSubmitted, | |
| sta.staffName, | |
| sta.staffID | |
| FROM | |
| assessment_structure nco | |
| INNER JOIN | |
| exam_type et ON et.typeID = nco.exam_type_id | |
| INNER JOIN | |
| staffaccounts sta ON sta.staffID = nco.staffID | |
| WHERE | |
| nco.subjectID = '$subjectID' | |
| AND nco.staffID IN (".implode(',',$staffID).") | |
| AND nco.batchID = '$batchID' | |
| AND nco.semID = '$semID'"; | |
| try { | |
| return $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * check if question paper exist in a subbatch or all subbatches for a selected exam type | |
| * | |
| * @param $questionPaper | |
| * @return questionpaperstatus | |
| */ | |
| public function checkIfQuestionPaperExists($questionPaper){ | |
| $questionPaper = $this->realEscapeObject($questionPaper); | |
| $questionPaperExists = ""; | |
| //if question paper exists for selected batch it return true | |
| $sqlqpCheck = "SELECT | |
| id | |
| FROM | |
| assessment_structure | |
| WHERE | |
| exam_type_id = '$questionPaper->examTypeId' | |
| AND batchID = '$questionPaper->batchId' | |
| AND semID = '$questionPaper->semId' | |
| AND subbatchID = '$questionPaper->subbatchId' | |
| AND subjectID = '$questionPaper->subjectId' | |
| AND id != '$questionPaper->id'"; | |
| try{ | |
| $questionPaperExists = $this->executeQueryForObject($sqlqpCheck); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| if(!empty($questionPaperExists->id)){ | |
| return true; | |
| } | |
| //if question paper created for all subbatches then it check two conditions 1.question paper exists for all subatches 2.question paper exist for atleast one of the subbatches | |
| if($questionPaper->subbatchId == 0){ | |
| $sql = "SELECT | |
| id | |
| FROM | |
| assessment_structure | |
| WHERE | |
| exam_type_id = '$questionPaper->examTypeId' | |
| AND batchID = '$questionPaper->batchId' | |
| AND semID = '$questionPaper->semId' | |
| AND subbatchID != 0 | |
| AND subjectID = '$questionPaper->subjectId' | |
| AND id != '$questionPaper->id'"; | |
| }else{ | |
| $sql = "SELECT | |
| id | |
| FROM | |
| assessment_structure | |
| WHERE | |
| exam_type_id = '$questionPaper->examTypeId' | |
| AND batchID = '$questionPaper->batchId' | |
| AND semID = '$questionPaper->semId' | |
| AND subbatchID = 0 | |
| AND subjectID = '$questionPaper->subjectId' | |
| AND id != '$questionPaper->id'"; | |
| } | |
| try{ | |
| $questionPaperExists = $this->executeQueryForObject($sql); | |
| if(!empty($questionPaperExists->id)){ | |
| return true; | |
| } | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return false; | |
| } | |
| /** | |
| * get question paper details by question paper id | |
| * | |
| * @param $questionPaperId | |
| * @return void | |
| */ | |
| public function getSingleQuestionPaperDetailsById($questionPaperId){ | |
| $questionPaperId = $this->realEscapeString($questionPaperId); | |
| $sql= "SELECT | |
| nco.id as questionPaperId, | |
| nco.header as questionPaperHeader, | |
| nco.qpcode as questionPaperCode, | |
| nco.subbatchID as selectedSubBatch, | |
| et.typeName, | |
| nco.exam_type_id as selectedExamType, | |
| nco.maxMark as maximumMark, | |
| nco.duration as examDuration, | |
| nco.isPublic, | |
| nco.isSubmitted, | |
| sta.staffName, | |
| sta.staffID as selectedAuthor | |
| FROM | |
| assessment_structure nco | |
| INNER JOIN | |
| exam_type et ON et.typeID = nco.exam_type_id | |
| INNER JOIN | |
| staffaccounts sta ON sta.staffID = nco.staffID | |
| WHERE | |
| nco.id ='$questionPaperId'"; | |
| try{ | |
| return $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * delete question paper by id | |
| * | |
| * @param $questionPaperId | |
| * @return void | |
| */ | |
| public function deleteQuestionPaperById($questionPaperId){ | |
| $questionPaperId = $this->realEscapeString($questionPaperId); | |
| $sectionExists = ""; | |
| $deleteinfo = ""; | |
| $deletequestionpaper = ""; | |
| $sql = "SELECT | |
| id | |
| FROM | |
| assessment_structure_section | |
| where | |
| assessment_structure_id = $questionPaperId"; | |
| try{ | |
| $sectionExists = $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| if($sectionExists->id) { | |
| return false; | |
| } | |
| $sqldeleteinfo = "DELETE | |
| FROM | |
| copied_assessment_structure_info | |
| WHERE | |
| copied_assessment_structure_Id = $questionPaperId | |
| OR new_assessment_structure_id = $questionPaperId"; | |
| $sqldeletequestionpaper = "DELETE | |
| FROM | |
| assessment_structure | |
| WHERE | |
| id = $questionPaperId"; | |
| try{ | |
| $deleteinfo = $this->executeQueryForObject($sqldeleteinfo); | |
| $deletequestionpaper = $this->executeQueryForObject($sqldeletequestionpaper); | |
| return true; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * function to update question paper | |
| */ | |
| public function updateQuestionPaper($questionPaper){ | |
| $questionPaper = $this->realEscapeObject($questionPaper); | |
| $updatedQuestionPaper = ""; | |
| $sql ="UPDATE | |
| assessment_structure | |
| SET | |
| exam_type_id = '$questionPaper->examTypeId' , | |
| header = '$questionPaper->header', | |
| qpcode = '$questionPaper->questionPaperCode', | |
| maxMark = '$questionPaper->maxMark', | |
| duration = '$questionPaper->duration', | |
| subbatchID = '$questionPaper->subbatchId', | |
| staffID = '$questionPaper->staffId', | |
| assessment_key = '$questionPaper->examTypeId' , | |
| assessment_type = 'EXAM' | |
| WHERE | |
| id = '$questionPaper->id' "; | |
| try{ | |
| $updatedQuestionPaper = $this->executeQueryForObject($sql); | |
| return true; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| public function getQuestionPaperSectionsByQuestionPaperId($questionPaperID) | |
| { | |
| $questionPaperID = $this->realEscapeString($questionPaperID); | |
| $sql_section = "SELECT id, section_name AS sectionName, assessment_structure_id AS questionPaperId, total_questions AS totalQuestions, no_of_questions_to_answer AS noOfQuestionsToAnswer, marks_per_question AS marksPerQuestion, instruction, seq_number AS seqNumber FROM assessment_structure_section WHERE assessment_structure_id = $questionPaperID"; | |
| try { | |
| return $this->executeQueryForList($sql_section); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| public function getCoDetailsByBatchIdSemIdAndSubjectId($batchId, $semId, $subjectId){ | |
| $batchId = $this->realEscapeString($batchId); | |
| $semId = $this->realEscapeString($semId); | |
| $subjectId = $this->realEscapeString($subjectId); | |
| $sql="SELECT | |
| id, code, objective, order_no | |
| FROM | |
| nba_course_outcome | |
| WHERE | |
| batchID = $batchId | |
| AND semID = $semId | |
| AND subjectID = $subjectId | |
| ORDER BY order_no ASC"; | |
| try { | |
| return $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| public function deleteQuestionById($questionId,$sectionId){ | |
| $questionId = $this->realEscapeString($questionId); | |
| $sectionId = $this->realEscapeString($sectionId); | |
| $sql = "DELETE FROM assessment_structure_section_questions WHERE assessment_structure_section_id = " . $sectionId . " AND assessment_question_id = " . $questionId . ""; | |
| try { | |
| $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException(ProfessionalException::MARK_ALREADY_ENTERED, $e->getMessage()); | |
| } | |
| $sqlDeleteCo = "DELETE FROM assessment_question_co_relation WHERE assessment_questions_id = ".$questionId.""; | |
| try { | |
| $this->executeQueryForObject($sqlDeleteCo); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| $sqlDelete = "DELETE FROM assessment_questions WHERE id = ".$questionId.""; | |
| try { | |
| return $this->executeQueryForObject($sqlDelete); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| public function getQuestionDetailsForEdit($questionId){ | |
| $response = new \stdClass(); | |
| $questionId = $this->realEscapeString($questionId); | |
| $sqlQuestion ="SELECT | |
| id, | |
| question, | |
| mark, | |
| attachments, | |
| public_question, | |
| attName, | |
| blooms_level, | |
| sbs_id, | |
| lin_resource_id as linResourceId | |
| FROM | |
| assessment_questions | |
| WHERE | |
| id = '$questionId' "; | |
| try { | |
| $response->questionDetails = $this->executeQueryForObject($sqlQuestion); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| $sqlCODetails = "SELECT | |
| nba_course_outcome_id AS id, | |
| course_outcome_value AS coValue, | |
| assessment_questions_id AS questionId | |
| FROM | |
| assessment_question_co_relation | |
| WHERE | |
| assessment_questions_id = '$questionId'"; | |
| try { | |
| $response->coDetails = $this->executeQueryForList($sqlCODetails); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| $sqlSeqno ="SELECT | |
| seq_number | |
| FROM | |
| assessment_structure_section_questions | |
| WHERE | |
| assessment_question_id = '$questionId'"; | |
| try { | |
| $response->seqNumber = $this->executeQueryForObject($sqlSeqno); | |
| return $response; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| public function updateQuestionDetailsById( $question ){ | |
| $question = $this->realEscapeObject($question); | |
| $linResourceId = 'NULL'; | |
| $linResources= []; | |
| if(!empty($question->linResourceId)) { | |
| $linResourceId = "'".$question->linResourceId."'"; | |
| } | |
| $sql = "SELECT lin_resource_id FROM assessment_questions WHERE id IN ($question->id) "; | |
| $linResources = $this->executeQueryForList($sql); | |
| if($linResources[0]->lin_resource_id==$question->linResourceId){ | |
| $linResources =[]; | |
| } | |
| $updateColumns = null; | |
| if ( !empty ( $question->sbsId ) ) { | |
| $updateColumns .= " sbs_id = '" .$question->sbsId. "', "; | |
| } | |
| $sql = "UPDATE assessment_questions | |
| SET $updateColumns | |
| question = '" . $question->question . "', | |
| mark = '" . $question->mark . "', | |
| blooms_level = '" . $question->bloomsLevel . "', | |
| attachments = '" . $question->attachmentPath . "', | |
| attName ='" . $question->attachmentName . "', | |
| lin_resource_id = " . $linResourceId . " | |
| WHERE id IN ($question->id) "; | |
| $sql_update_section = "UPDATE assessment_structure_section | |
| SET | |
| marks_per_question = '" . $question->mark . "' | |
| WHERE id IN ( select assessment_structure_section_id from assessment_structure_section_questions where assessment_question_id IN ($question->id))"; | |
| try { | |
| $this->executeQueryForObject($sql); | |
| $this->executeQueryForObject($sql_update_section); | |
| if ( !empty ( $linResources ) ) { | |
| foreach ($linResources as $linResource) { | |
| if($linResource->lin_resource_id){ | |
| $removeResourceRequest = new RemoveResourceRequest(); | |
| $removeResourceRequest->resourceId = $linResource->lin_resource_id; | |
| $removeResourceRequest->accessKey = getenv('AWS_ACCESS_KEY'); | |
| $removeResourceRequest->secretKey = getenv("AWS_CLIENT_SECRET_KEY"); | |
| ResourceService::getInstance()->removeResource($removeResourceRequest); | |
| } | |
| } | |
| } | |
| if ( !empty ( $question->courseOutcome ) ) { | |
| $values = []; | |
| foreach ( $question->courseOutcome as $co ) { | |
| $questionIdArr = explode(",", $question->id); | |
| foreach ( $questionIdArr as $questionId ) { | |
| $values[] = "( $questionId, $co->id, '$co->value' )"; | |
| } | |
| } | |
| // foreach ($coVals as $coId => $value) { | |
| // $sql .= "($questionId, $coId, $value), "; | |
| // } | |
| // $sql = rtrim($sql, ", "); | |
| $sql = "DELETE FROM assessment_question_co_relation WHERE assessment_questions_id IN ($question->id) "; | |
| $this->executeQuery($sql); | |
| $sql = "INSERT INTO assessment_question_co_relation (assessment_questions_id, nba_course_outcome_id, course_outcome_value) VALUES | |
| " . implode(",", $values) ; | |
| $this->executeQuery($sql); | |
| $sql = "DELETE FROM assessment_structure_question_co_relation WHERE assessment_structure_question_id IN | |
| (SELECT id FROM assessment_structure_section_questions WHERE assessment_question_id IN ($question->id))"; | |
| $this->executeQuery($sql); | |
| $sql = "INSERT INTO assessment_structure_question_co_relation (assessment_structure_question_id, nba_course_outcome_id, course_outcome_value) | |
| SELECT | |
| assq.id, | |
| aqco.nba_course_outcome_id, | |
| aqco.course_outcome_value | |
| FROM | |
| assessment_structure_section_questions assq | |
| INNER JOIN | |
| assessment_question_co_relation aqco ON (assq.assessment_question_id = aqco.assessment_questions_id) | |
| WHERE | |
| assq.assessment_question_id IN ($question->id) "; | |
| $this->executeQuery($sql); | |
| } | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| public function getResourceId($questionId){ | |
| $questionId = $this->realEscapeObject($questionId); | |
| $sql = "SELECT lin_resource_id from assessment_questions where id = '$questionId'"; | |
| try { | |
| return $this->executeQueryForObject($sql)->lin_resource_id; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| public function deleteQuestionResourceIdById($questionId){ | |
| $questionId = $this->realEscapeObject($questionId); | |
| $sql = "UPDATE assessment_questions | |
| SET | |
| lin_resource_id = NULL | |
| where id = '$questionId'"; | |
| try { | |
| return $this->executeQueryForObject($sql)->lin_resource_id; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * @param $questionId | |
| * @throws ProfessionalException | |
| * @author Vishnu M | |
| */ | |
| public function deleteAssessmentAssignmentQuestionById ( $questionId ) { | |
| $questionId = $this->realEscapeString($questionId); | |
| $sql = null; | |
| $result = null; | |
| /** | |
| * Tables from which the data has to be deleted : | |
| * 1. assessment_structure_section ( Each section has only 1 question, so we can delete the section ) | |
| * 2. assessment_structure_section_questions | |
| * 3. assessment_structure_question_co_relation | |
| * 4. assessment_structure_questions | |
| * 5. assessment_question_co_relation | |
| * 6. assessment_questions | |
| */ | |
| try { | |
| /** | |
| * First we have to check whether mark for this question is already entered | |
| * 'assessment_student_marks' | |
| */ | |
| $sqlCheck = "SELECT id FROM assessment_student_marks WHERE assessment_structure_questions_id IN (SELECT id FROM assessment_structure_questions WHERE assessment_questions_id IN ($questionId))"; | |
| $studentMark = $this->executeQueryForObject($sqlCheck); | |
| if ( !empty ( $studentMark ) ) { | |
| throw new ProfessionalException ("", ""); | |
| } | |
| /** | |
| * On deleting from 'assessment_structure_section' | |
| * The question under this section will get deleted along with their CO relation mapping | |
| * 'assessment_structure_section_questions' & 'assessment_structure_question_co_relation' | |
| */ | |
| $sql = "DELETE FROM assessment_structure_section WHERE id IN (SELECT assessment_structure_section_id FROM assessment_structure_section_questions WHERE assessment_question_id IN ($questionId) )"; | |
| $this->executeQuery($sql); | |
| $sql = "DELETE FROM assessment_structure_questions WHERE assessment_questions_id IN ($questionId) "; | |
| $this->executeQuery($sql); | |
| $sql = "DELETE FROM assessment_question_co_relation WHERE assessment_questions_id IN ($questionId) "; | |
| $this->executeQuery($sql); | |
| $sqlDeleteQuestionRubricDetails = "DELETE FROM rubric_wise_assessment_criteria_details WHERE rubric_wise_assessment_id = (SELECT id FROM rubric_wise_assessment WHERE assessment_question_id IN ($questionId))"; | |
| $this->executeQuery($sqlDeleteQuestionRubricDetails); | |
| $sqlDeleteQuestionRubric = "DELETE FROM rubric_wise_assessment WHERE assessment_question_id IN ($questionId) "; | |
| $this->executeQuery($sqlDeleteQuestionRubric); | |
| $sql = "DELETE FROM assessment_questions WHERE id IN ($questionId) "; | |
| $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException ($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * Method to get all the pseudo subject questions of a question by question id | |
| * @param $questionId | |
| * @return Object|null | |
| * @throws ProfessionalException | |
| * @author Vishnu M | |
| */ | |
| public function getPseudoSubjectAssessmentQuestionsByQuestionId ( $questionId ) { | |
| $questionId = $this->realEscapeString($questionId); | |
| $sql = null; | |
| $questions = null; | |
| try { | |
| $sql = "SELECT aqps.id, aqps.question, aqps.mark, aqps.attachments, aqps.subjectID, aqps.sbs_id, aqps.public_question, aqps.attName, aqps.blooms_level, aqps.lin_resource_id, aqps.ps_question_key FROM assessment_questions aq INNER JOIN assessment_questions aqps ON (aq.ps_question_key = aqps.ps_question_key) WHERE aq.id = '$questionId' "; | |
| $questions = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException ($e->getCode(), $e->getMessage()); | |
| } | |
| return $questions; | |
| } | |
| public function getQuestionPaperDetailsForMarkEntry ( $questionPaperId ) { | |
| $questionPaperId = $this->realEscapeString($questionPaperId); | |
| $sql = null; | |
| $questions = null; | |
| try { | |
| $sql = "SELECT | |
| ase.id as assessment_structure_id, | |
| ase.header as assessment_structure_header, | |
| ase.qpcode as assessment_structure_qpcode, | |
| ase.subjectID as assessment_structure_subjectId, | |
| ase.batchID as assessment_structure_batchId, | |
| ase.semID as assessment_structure_semId, | |
| ase.staffID as assessment_structure_staffId, | |
| ase.subbatchID as assessment_structure_subbatchId, | |
| ase.assessment_key, | |
| ase.assessment_type, | |
| asst.id as assessment_structure_section_id, | |
| asst.section_name as assessment_structure_section_section_name, | |
| asst.total_questions as assessment_structure_section_total_questions, | |
| asst.no_of_questions_to_answer as assessment_structure_section_no_of_questions_to_answer, | |
| asst.marks_per_question as assessment_structure_section_marks_per_question, | |
| asst.assessment_structure_id as assessment_structure_section_assessment_structure_id, | |
| asst.seq_number as assessment_structure_section_seq_number, | |
| asq.id as assessment_structure_questions_id, | |
| assq.seq_number as assessment_structure_section_questions_seq_number, | |
| asq.assessment_questions_id, | |
| aq.question, | |
| aq.lin_resource_id AS linResourceId, | |
| nco.code as course_outcome_code, | |
| nco.id AS course_outcome_id, | |
| nqcr.course_outcome_value AS course_outcome_value, | |
| rwa.id question_rubric_id, | |
| rwa.assessment_question_id as assessment_question_id_rubric , | |
| rwa.is_percentage, | |
| rwacd.id rubric_criteria_id, | |
| rwacd.rubric_wise_assessment_id, | |
| rwacd.criteria, | |
| rwacd.value_and_justification, | |
| IF(asst.no_of_questions_to_answer, | |
| (asst.no_of_questions_to_answer * asst.marks_per_question), | |
| (asst.total_questions * asst.marks_per_question)) AS sectionTotalMark | |
| FROM | |
| assessment_structure ase | |
| LEFT JOIN | |
| assessment_structure_section asst ON (ase.id = asst.assessment_structure_id) | |
| LEFT JOIN | |
| assessment_structure_section_questions assq ON (asst.id = assq.assessment_structure_section_id) | |
| LEFT JOIN | |
| assessment_structure_questions asq ON (assq.assessment_question_id = asq.assessment_questions_id | |
| AND asq.assessment_structure_id = asst.assessment_structure_id) | |
| LEFT JOIN | |
| assessment_questions aq ON (aq.id = assq.assessment_question_id) | |
| LEFT JOIN | |
| assessment_question_co_relation nqcr ON (nqcr.assessment_questions_id =assq.assessment_question_id) | |
| LEFT JOIN | |
| nba_course_outcome nco ON (nco.id = nqcr.nba_course_outcome_id) | |
| LEFT JOIN | |
| rubric_wise_assessment rwa ON rwa.assessment_question_id = aq.id | |
| LEFT JOIN | |
| rubric_wise_assessment_criteria_details rwacd ON (rwacd.rubric_wise_assessment_id = rwa.id) | |
| WHERE | |
| ase.id = '$questionPaperId' | |
| ORDER BY asst.seq_number ASC , asst.id , assq.seq_number ASC | |
| "; | |
| return $this->executeQueryForObject($sql, false, $this->mapper[QuestionPaperServiceMapper::GET_QUESTION_PAPER_DETAILS_FOR_MARK_ENTRY]); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException ($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| public function getExamDetailsByQuestionPaperId ( $questionPaperDetails ) { | |
| $questionPaperDetails = $this->realEscapeObject($questionPaperDetails); | |
| $sql = null; | |
| $examDetails = null; | |
| try { | |
| if(!empty($questionPaperDetails->subbatchId)){ | |
| $subbatchConditionForSqlExam = " AND t1.subbatchID = t2.subbatchID"; | |
| $sql_exam = "SELECT t1.examID, t1.examTotalMarks, t1.examTypeID, t1.batchID, t1.semID, t2.maxMark FROM exam t1, assessment_structure t2 WHERE t1.examTypeID = t2.exam_type_id AND t1.subjectID = t2.subjectID AND t1.batchID = t2.batchID AND t1.semID = t2.semID AND t2.id = ".$questionPaperDetails->id." ".$subbatchConditionForSqlExam; | |
| }else{ | |
| $sql_exam = "SELECT t1.examID, t1.examTotalMarks, t1.examTypeID, t1.batchID, t1.semID, t2.maxMark FROM exam t1, assessment_structure t2 WHERE t1.examTypeID = t2.exam_type_id AND t1.subjectID = t2.subjectID AND t1.batchID = t2.batchID AND t1.semID = t2.semID AND t2.id = ".$questionPaperDetails->id; | |
| } | |
| $examDetails = $this->executeQueryForObject($sql_exam); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException ($e->getCode(), $e->getMessage()); | |
| } | |
| return $examDetails; | |
| } | |
| public function getStudentListAndDetails($isCurrentSem,$questionPaperId,$semId,$batchId,$subBatchId,$examId){ | |
| $questionPaperId = $this->realEscapeString($questionPaperId); | |
| $semId = $this->realEscapeString($semId); | |
| $batchId = $this->realEscapeString($batchId); | |
| $subBatchId = $this->realEscapeString($subBatchId); | |
| $examId = $this->realEscapeString($examId); | |
| try{ | |
| $sortByColumn = BatchService::getInstance()->getStudentSortByColumnOfABatch($batchId); | |
| if($isCurrentSem) | |
| { | |
| if($subBatchId == 0) { | |
| $sql_students = "SELECT distinct sa.studentID, | |
| sa.rollNo, | |
| sa.studentName, | |
| asm.assessment_structure_questions_id, | |
| asm.mark_obtained, | |
| ea.isAbsent, | |
| rwasm.rubric_wise_assessment_id, | |
| rwasm.rubric_wise_assessment_criteria_and_details_id, | |
| rwasm.mark_obtained AS rubricMark | |
| FROM assessment_structure qp | |
| inner join | |
| studentaccount sa on qp.batchID = sa.batchID | |
| inner join | |
| semesters sem on sem.semID = qp.semID | |
| inner join | |
| semesters joinedSem on sa.joiningSemId = joinedSem.semID | |
| LEFT JOIN | |
| assessment_student_marks asm ON (asm.studentID = sa.studentID and asm.assessment_id = $examId AND asm.assessment_type = 'EXAM') | |
| LEFT JOIN | |
| exam_attendance ea ON (ea.studentID = sa.studentID and ea.examID = $examId) | |
| LEFT JOIN | |
| assessment_structure_questions asq ON asq.id = asm.assessment_structure_questions_id | |
| LEFT JOIN | |
| rubric_wise_assessment rwa ON rwa.assessment_question_id = asq.assessment_questions_id | |
| LEFT JOIN | |
| rubric_wise_assessment_student_mark rwasm ON rwasm.rubric_wise_assessment_id = rwa.id | |
| AND rwasm.student_id = sa.studentID | |
| WHERE qp.id = $questionPaperId and joinedSem.orderNo <= sem.orderNo order by sa.$sortByColumn;"; | |
| } | |
| else { | |
| $sql_students = "SELECT distinct sa.studentID, | |
| sa.rollNo, | |
| sa.studentName, | |
| asm.assessment_structure_questions_id, | |
| asm.mark_obtained, | |
| ea.isAbsent, | |
| rwasm.rubric_wise_assessment_id, | |
| rwasm.rubric_wise_assessment_criteria_and_details_id, | |
| rwasm.mark_obtained AS rubricMark | |
| FROM assessment_structure qp | |
| inner join | |
| studentaccount sa on qp.batchID = sa.batchID | |
| inner join | |
| subbatch_student ss on ss.studentID = sa.studentID | |
| inner join | |
| semesters sem on sem.semID = qp.semID | |
| inner join | |
| semesters joinedSem on sa.joiningSemId = joinedSem.semID | |
| LEFT JOIN | |
| assessment_student_marks asm ON (asm.studentID = sa.studentID and asm.assessment_id = $examId AND asm.assessment_type = 'EXAM') | |
| LEFT JOIN | |
| exam_attendance ea ON (ea.studentID = sa.studentID and ea.examID = $examId) | |
| LEFT JOIN | |
| assessment_structure_questions asq ON asq.id = asm.assessment_structure_questions_id | |
| LEFT JOIN | |
| rubric_wise_assessment rwa ON rwa.assessment_question_id = asq.assessment_questions_id | |
| LEFT JOIN | |
| rubric_wise_assessment_student_mark rwasm ON rwasm.rubric_wise_assessment_id = rwa.id | |
| AND rwasm.student_id = sa.studentID | |
| WHERE | |
| qp.id = $questionPaperId AND ss.subbatchID=$subBatchId and joinedSem.orderNo <= sem.orderNo order by sa.$sortByColumn;"; | |
| } | |
| } | |
| else | |
| { | |
| $semDetails = SemesterService::getInstance()->getSemDetailsBySemId($semId); | |
| if($subBatchId == 0) { | |
| $sql_students = "SELECT distinct sa.studentID, | |
| sa.rollNo, | |
| sa.studentName, | |
| sa.regNo, | |
| asm.assessment_structure_questions_id, | |
| asm.mark_obtained, | |
| ea.isAbsent, | |
| rwasm.rubric_wise_assessment_id, | |
| rwasm.rubric_wise_assessment_criteria_and_details_id, | |
| rwasm.mark_obtained AS rubricMark | |
| from | |
| studentaccount sa | |
| left join | |
| failed_students fs on sa.studentID = fs.studentID | |
| inner join | |
| assessment_structure qp on (qp.batchID = sa.batchID or qp.batchID = fs.previousBatch) | |
| LEFT JOIN | |
| assessment_student_marks asm ON (asm.studentID = sa.studentID and asm.assessment_id = $examId AND asm.assessment_type = 'EXAM') | |
| LEFT JOIN | |
| exam_attendance ea ON (ea.studentID = sa.studentID and ea.examID = $examId) | |
| LEFT JOIN | |
| assessment_structure_questions asq ON asq.id = asm.assessment_structure_questions_id | |
| LEFT JOIN | |
| rubric_wise_assessment rwa ON rwa.assessment_question_id = asq.assessment_questions_id | |
| LEFT JOIN | |
| rubric_wise_assessment_student_mark rwasm ON rwasm.rubric_wise_assessment_id = rwa.id | |
| AND rwasm.student_id = sa.studentID | |
| WHERE qp.id = $questionPaperId and sa.studentID in (select 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 = $batchId and joinedSem.orderNo <= $semDetails->orderNo union select sa.studentID from failed_students fs inner join studentaccount sa on sa.studentID = fs.studentID inner join semesters fsem on fsem.semID = fs.failedInSemester inner join semesters joinedSem on sa.joiningSemId = joinedSem.semID where previousBatch = $batchId and fsem.orderNo > $semDetails->orderNo and joinedSem.orderNo <= $semDetails->orderNo) order by $sortByColumn"; | |
| } | |
| else { | |
| $sql_students = "SELECT distinct sa.studentID, | |
| sa.rollNo, | |
| sa.studentName, | |
| sa.regNo, | |
| asm.assessment_structure_questions_id, | |
| asm.mark_obtained, | |
| ea.isAbsent, | |
| rwasm.rubric_wise_assessment_id, | |
| rwasm.rubric_wise_assessment_criteria_and_details_id, | |
| rwasm.mark_obtained AS rubricMark | |
| from | |
| studentaccount sa left join failed_students fs on sa.studentID = fs.studentID | |
| inner join | |
| assessment_structure qp on (qp.batchID = sa.batchID or qp.batchID = fs.previousBatch) | |
| inner join | |
| subbatch_student ss on ss.studentID = sa.studentID | |
| LEFT JOIN | |
| assessment_student_marks asm ON (asm.studentID = sa.studentID and asm.assessment_id = $examId AND asm.assessment_type = 'EXAM') | |
| LEFT JOIN | |
| exam_attendance ea ON (ea.studentID = sa.studentID and ea.examID = $examId) | |
| LEFT JOIN | |
| assessment_structure_questions asq ON asq.id = asm.assessment_structure_questions_id | |
| LEFT JOIN | |
| rubric_wise_assessment rwa ON rwa.assessment_question_id = asq.assessment_questions_id | |
| LEFT JOIN | |
| rubric_wise_assessment_student_mark rwasm ON rwasm.rubric_wise_assessment_id = rwa.id | |
| AND rwasm.student_id = sa.studentID | |
| WHERE qp.id = $questionPaperId AND ss.subbatchID=$subBatchId and sa.studentID in (select 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 = $batchId and joinedSem.orderNo <= $semDetails->orderNo union select sa.studentID from failed_students fs inner join studentaccount sa on sa.studentID = fs.studentID inner join semesters fsem on fsem.semID = fs.failedInSemester inner join semesters joinedSem on sa.joiningSemId = joinedSem.semID where previousBatch = $batchId and fsem.orderNo > $semDetails->orderNo and joinedSem.orderNo <= $semDetails->orderNo) order by $sortByColumn;"; | |
| } | |
| } | |
| return $this->executeQueryForList($sql_students, $this->mapper[QuestionPaperServiceMapper::GET_STUDENT_ASSESSMENT_QUESTION_AND_MARK]); | |
| }catch (\Exception $e) { | |
| throw new ProfessionalException ($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * delete student total exam mark . if , first staff enter student total mark and then staff try to enter student question wise mark we need to delete student tatel mark entered first. | |
| * | |
| * @param [type] $batchId | |
| * @return void | |
| */ | |
| public function deleteStudentExamMark($batchId,$examId,$subjectId){ | |
| $batchId = $this->realEscapeString($batchId); | |
| $examId = $this->realEscapeString($examId); | |
| $subjectId = $this->realEscapeString($subjectId); | |
| $sql_del = "DELETE FROM student_marks WHERE batchID = ".$batchId." AND examID = ".$examId." AND subjectID = ".$subjectId.""; | |
| try { | |
| return $this->executeQuery($sql_del); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| public function upsertAssessmentStudentQuestionWiseMark($studentMarkDetails){ | |
| $studentMarkDetails = $this->realEscapeObject($studentMarkDetails); | |
| $sql_insert = "INSERT INTO assessment_student_marks (assessment_id, assessment_structure_questions_id, studentID, mark_obtained, staffID, assessment_type) VALUES (".$studentMarkDetails->assessmentId.", ".$studentMarkDetails->assessmentStructureQuestionsId.", ".$studentMarkDetails->studentId.", \"".$studentMarkDetails->assessmentStructureQuestionStudentMark."\", ".$studentMarkDetails->staffId.", '".$studentMarkDetails->assessmentType."') ON DUPLICATE KEY UPDATE mark_obtained = VALUES(mark_obtained),staffID = VALUES(staffID)"; | |
| try { | |
| return $this->executeQuery($sql_insert); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| public function upsertStudentAttendanceInExam($studentId,$examId,$isAbsent){ | |
| $studentId = $this->realEscapeString($studentId); | |
| $examId = $this->realEscapeString($examId); | |
| $isAbsent = $this->realEscapeString($isAbsent); | |
| $sql_insert = "INSERT INTO exam_attendance (examID, studentID, isAbsent) VALUES (".$examId.", ".$studentId.", ".$isAbsent.") ON DUPLICATE KEY UPDATE isAbsent = VALUES(isAbsent)"; | |
| try { | |
| return $this->executeQuery($sql_insert); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| public function upsertAssessmentStudentTotalMark($studentMarkDetails){ | |
| $studentMarkDetails = $this->realEscapeObject($studentMarkDetails); | |
| $sql_insert = "INSERT INTO student_marks ( batchID, studentID, examID, marksObtained, subjectID, staffID, percentage, semID, examTypeID ) ( SELECT batchID, ".$studentMarkDetails->studentId.", ".$studentMarkDetails->examId.", '".$studentMarkDetails->studentTotalMark."', subjectID, ".$studentMarkDetails->staffId.", ".$studentMarkDetails->markPercentage." , semID, exam_type_id FROM assessment_structure WHERE id = ".$studentMarkDetails->assessmentStructureId.") ON DUPLICATE KEY UPDATE marksObtained = VALUES (marksObtained), percentage = VALUES (percentage)"; | |
| try { | |
| return $this->executeQuery($sql_insert); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| public function upsertConfirmStudentExamTotalMarks($studentMarkDetails, $staffId){ | |
| $studentMarkDetails = $this->realEscapeArray($studentMarkDetails); | |
| $staffId = $this->realEscapeString($staffId); | |
| $values = []; | |
| try { | |
| $sql = "SELECT batchID, subjectID, semID, exam_type_id FROM assessment_structure WHERE id = ".$studentMarkDetails[0]->assessmentStructureId.""; | |
| $assessmentStructure = $this->executeQueryForObject($sql); | |
| } catch(\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| foreach( $studentMarkDetails as $studentMarkDetail) { | |
| $studentMarkDetail->markPercentage = ($studentMarkDetail->studentTotalMark / $studentMarkDetail->examTotalMarks) * 100; | |
| switch ($studentMarkDetail->studentIsAbsent) { | |
| case 1: | |
| $studentMarkDetail->studentTotalMark = -1; | |
| $studentMarkDetail->markPercentage = 0; | |
| break; | |
| case 2: | |
| $studentMarkDetail->studentTotalMark = -0.001; | |
| $studentMarkDetail->markPercentage = 0; | |
| break; | |
| } | |
| $values[] = "($assessmentStructure->batchID, $assessmentStructure->subjectID, $assessmentStructure->semID, $assessmentStructure->exam_type_id, $studentMarkDetail->studentId, $studentMarkDetail->examId, '$studentMarkDetail->studentTotalMark', $staffId, $studentMarkDetail->markPercentage)"; | |
| } | |
| $sql_insert = "INSERT INTO student_marks ( batchID, subjectID, semID, examTypeID, studentID, examID, marksObtained, staffID, percentage) VALUES " .implode(",", $values) ."ON DUPLICATE KEY UPDATE marksObtained = VALUES (marksObtained), percentage = VALUES (percentage)"; | |
| try { | |
| return $this->executeQuery($sql_insert); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| public function deleteStudentExamQuestionsMarks($studentId,$examId){ | |
| $studentId = $this->realEscapeString($studentId); | |
| $examId = $this->realEscapeString($examId); | |
| $sql_delete = "DELETE from assessment_student_marks WHERE assessment_id ='$examId' AND studentID='$studentId' AND assessment_type = 'EXAM'"; | |
| try { | |
| return $this->executeQuery($sql_delete); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| public function studentExamQuestionsMarksConfirm($examDetails){ | |
| $examDetails = $this->realEscapeObject($examDetails); | |
| $sql_delete = "DELETE from aprove_exam_marks where semID=".$examDetails->semId." and batchID=".$examDetails->batchId." and examTypeID=".$examDetails->examTypeId." and examID = ".$examDetails->examId." AND subbatchID=$examDetails->subBatchId"; | |
| $sql_insert = "insert into aprove_exam_marks (batchID, semID, isAproved, staffID,examTypeID, examId, subbatchID, createdBy, createdDate, updatedBy, updatedDate) values (".$examDetails->batchId.",".$examDetails->semId.",1 ,".$examDetails->staffId.",".$examDetails->examTypeId.", ".$examDetails->examId.", $examDetails->subBatchId, ".$examDetails->staffId.", utc_timestamp(), ".$examDetails->staffId.", utc_timestamp())"; | |
| try { | |
| $this->executeQuery($sql_delete); | |
| return $this->executeQuery($sql_insert); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| public function deleteAssessmentStudentQuestionWiseMark($assessmentId,$assessmentType){ | |
| $assessmentId = $this->realEscapeString($assessmentId); | |
| $assessmentType = $this->realEscapeString($assessmentType); | |
| $sql_delete = "DELETE FROM assessment_student_marks WHERE assessment_id = ".$assessmentId." AND assessment_type = '$assessmentType'"; | |
| try { | |
| return $this->executeQuery($sql_delete); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| public function deleteAssessmentStudentQuestionWiseMarkByQuestionIdAndStudentId($assessmentDetails){ | |
| $assessmentDetails = $this->realEscapeObject($assessmentDetails); | |
| $sql_delete = "DELETE FROM assessment_student_marks WHERE assessment_id = ".$assessmentDetails->assessmentId." AND studentID = ".$assessmentDetails->studentId." AND assessment_structure_questions_id = ".$assessmentDetails->assessmentStructureQuestionsId." AND assessment_type = '$assessmentDetails->assessmentType'"; | |
| try { | |
| return $this->executeQuery($sql_delete); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| } |