Code Coverage |
||||||||||
Classes and Traits |
Functions and Methods |
Lines |
||||||||
| Total | |
0.00% |
0 / 1 |
|
0.00% |
0 / 81 |
CRAP | |
0.00% |
0 / 1630 |
| AssignmentService | |
0.00% |
0 / 1 |
|
0.00% |
0 / 81 |
88506.00 | |
0.00% |
0 / 1630 |
| __construct | |
0.00% |
0 / 1 |
2.00 | |
0.00% |
0 / 3 |
|||
| __clone | |
0.00% |
0 / 1 |
2.00 | |
0.00% |
0 / 1 |
|||
| getInstance | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 5 |
|||
| getAssignmentNum | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 13 |
|||
| getAssiNum | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 16 |
|||
| getAssignmentCountBySubject | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 9 |
|||
| getAssignmentMarkListByStudent | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 18 |
|||
| getAllAssignmentMarkListByStudentId | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 18 |
|||
| getCoValuesForAnAssignment | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 20 |
|||
| getAssignmentNumByBatch | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 10 |
|||
| deleteAssignmentMarksById | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 10 |
|||
| getAssignmentDetailsBySubjectId | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 22 |
|||
| getAssignmentDetailsById | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 11 |
|||
| getAssignmentDetailsByAssiNu | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 13 |
|||
| createAssignment | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 9 |
|||
| createNewAssignment | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 13 |
|||
| updateAssignment | |
0.00% |
0 / 1 |
30.00 | |
0.00% |
0 / 47 |
|||
| updateAssignmentIsPublish | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 30 |
|||
| deleteAssignment | |
0.00% |
0 / 1 |
72.00 | |
0.00% |
0 / 42 |
|||
| getStudentAssignmentMarkByAssiNu | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 11 |
|||
| createStudentAssignmentMarks | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 9 |
|||
| updateStudentAssignmentMarks | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 8 |
|||
| deleteStudentAssignmentMarks | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 8 |
|||
| getAssignmentDetailsBySbsId | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 27 |
|||
| isSubmissionAllowed | |
0.00% |
0 / 1 |
90.00 | |
0.00% |
0 / 46 |
|||
| getAssignmentNumsOfAsubject | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 11 |
|||
| getMaxAssignmentNumber | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 11 |
|||
| getBatchwiseMaxAssignmentNumber | |
0.00% |
0 / 1 |
42.00 | |
0.00% |
0 / 24 |
|||
| getBatchOrSubjectwiseAssignmentRules | |
0.00% |
0 / 1 |
42.00 | |
0.00% |
0 / 20 |
|||
| deleteBatchOrSubjectSpecificAssignmentRules | |
0.00% |
0 / 1 |
42.00 | |
0.00% |
0 / 20 |
|||
| getGeneralMaxAssignmentNumberByBatchId | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 9 |
|||
| getGeneralMaxAssignmentNumberDetails | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 9 |
|||
| saveOrUpdateGeneralAssignmentRule | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 16 |
|||
| deleteGeneralAssignmentRulesById | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 7 |
|||
| getGeneralMaxAssignmentNumberByCourseTypeId | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 9 |
|||
| getMaxAssignmentNumberForPseudoSubject | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 15 |
|||
| getAssignmentDetailsBySubmissionDate | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 22 |
|||
| getAssignmentDetailsByBatchSemAndSubjectId | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 13 |
|||
| getAllStudentMarksAndCoReportOfABatchAndSemester | |
0.00% |
0 / 1 |
30.00 | |
0.00% |
0 / 21 |
|||
| getStudentMaxMarkGivenForAssignmentByAssignmentID | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 13 |
|||
| getStudentMaxMarkGivenForPsassignment | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 14 |
|||
| saveAssessmentQuestion | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 20 |
|||
| getAssessmentStructureByBatchSemSubject | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 22 |
|||
| getAssessmentByAssignmentId | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 39 |
|||
| getAssignmentSubmittedStudentIds | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 12 |
|||
| getAssignmentStudentListForQuestionWiseMarkEnrty | |
0.00% |
0 / 1 |
56.00 | |
0.00% |
0 / 160 |
|||
| assignmentMarkIsApproved | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 11 |
|||
| checkAssignmentMark | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 18 |
|||
| upsertAssignmentStudentTotalMark | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 17 |
|||
| upsertConfirmStudentAssignmentTotalMarks | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 18 |
|||
| getStudentAssignmentDetailsByStudentId | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 13 |
|||
| addStudentAssignment | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 18 |
|||
| getPseudoSubjectAssignmentDetails | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 21 |
|||
| createAssignmentForEachSubBatches | |
0.00% |
0 / 1 |
56.00 | |
0.00% |
0 / 25 |
|||
| getAllStudentMarksAndCoReportOfABatchAndSemesterForAssignment | |
0.00% |
0 / 1 |
110.00 | |
0.00% |
0 / 59 |
|||
| getAssignmentMarksOfAStudent | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 10 |
|||
| getAssignmentList | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 14 |
|||
| getExternalAssignmentDetailsBySubjectId | |
0.00% |
0 / 1 |
30.00 | |
0.00% |
0 / 27 |
|||
| getAssignmentListByStudent | |
0.00% |
0 / 1 |
30.00 | |
0.00% |
0 / 26 |
|||
| confirmStudentAssignment | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 10 |
|||
| unconfirmStudentAssignment | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 10 |
|||
| updateStudentAssignmentRemarks | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 11 |
|||
| getAllAssignmentDetailsBySbsId | |
0.00% |
0 / 1 |
420.00 | |
0.00% |
0 / 67 |
|||
| getAssignmentStudentsForMarkEntry | |
0.00% |
0 / 1 |
110.00 | |
0.00% |
0 / 59 |
|||
| getAssignmentStudentsForMarkEntryWithCount | |
0.00% |
0 / 1 |
110.00 | |
0.00% |
0 / 56 |
|||
| saveBatchwiseAssignmentRules | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 16 |
|||
| updateBatchwiseAssignmentRules | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 16 |
|||
| getAssignmentDetailsByRequest | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 8 |
|||
| deleteBatchwiseAssignmentRules | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 15 |
|||
| checkAssignmentQuestionWiseMarkEntry | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 14 |
|||
| saveStudentAssignmentMarks | |
0.00% |
0 / 1 |
42.00 | |
0.00% |
0 / 32 |
|||
| updateAsyncReportDetails | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 8 |
|||
| setAsyncDocStatus | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 8 |
|||
| getAssigmentAsyncFile | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 8 |
|||
| updateAssignmentIsPublishMarks | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 17 |
|||
| findPseudoSubjectAssignment | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 15 |
|||
| getSumOfQuestionMarksInAnAssignment | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 21 |
|||
| getMaxStudentMarkInAnAssignment | |
0.00% |
0 / 1 |
42.00 | |
0.00% |
0 / 17 |
|||
| getAssignmentMarksByIds | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 8 |
|||
| getAssignmentMarkListByRequest | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 18 |
|||
| getPseudoSubjectAssignmentList | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 13 |
|||
| <?php | |
| namespace com\linways\core\ams\professional\service; | |
| use com\linways\core\ams\professional\dto\Assignment; | |
| use com\linways\core\ams\professional\dto\nba\NBAQuestion; | |
| use com\linways\core\ams\professional\service\StudentService; | |
| use com\linways\core\ams\professional\dto\StudentAssignmentDetails; | |
| use com\linways\core\ams\professional\mapper\AssignmentServiceMapper; | |
| use com\linways\core\ams\professional\exception\ProfessionalException; | |
| use com\linways\core\ams\professional\service\SemesterService; | |
| use com\linways\core\ams\professional\service\nba\NbaCoService; | |
| use com\linways\core\ams\professional\request\AssignmentBatchSubjectRequest; | |
| use com\linways\core\ams\professional\dto\StudentAssinments; | |
| use com\linways\core\ams\professional\response\SearchStudentResponse; | |
| class AssignmentService 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 = AssignmentServiceMapper::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; | |
| } | |
| /** | |
| * Get assignment numbers in a subbatch | |
| * @param int $batchId | |
| * @param int $semId | |
| * @param int $subbatchId | |
| */ | |
| public function getAssignmentNum($batchId,$semId,$subbatchId = null) | |
| { | |
| $condition = ""; | |
| if($subbatchId){ | |
| $condition = " AND subbatchID = $subbatchId "; | |
| } | |
| $sql = "SELECT DISTINCT assiNu FROM batch_assignment WHERE batchID=$batchId AND semID=$semId $condition ORDER BY assiNu ASC"; | |
| try { | |
| $assignmentNum = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode,$e->getMessage()); | |
| } | |
| return $assignmentNum; | |
| } | |
| public function getAssiNum($batchId,$semId,$subjectId,$subbatchId = NULL) | |
| { | |
| $batchId = $this->realEscapeString($batchId); | |
| $subjectId = $this->realEscapeString($subjectId); | |
| $semId = $this->realEscapeString($semId); | |
| $sql = "SELECT assiNu, externalAssiNu FROM batch_assignment where batchID = ".$batchId." AND subjectID = ".$subjectId." AND semID = ".$semId.""; | |
| if (!empty($subbatchId)) { | |
| $subbatchId = $this->realEscapeString($subbatchId); | |
| $sql .= " AND ( subbatchID = 0 OR subbatchID = ".$subbatchId.")"; | |
| } | |
| try { | |
| $assignmentNum = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode,$e->getMessage()); | |
| } | |
| return $assignmentNum; | |
| } | |
| /** | |
| * Get count of assignments given for a subject in a sem | |
| * @param int $batchId | |
| * @param int $semID | |
| * @param int $subject | |
| * @throws ProfessionalException | |
| * @return $assignmentCount | |
| */ | |
| public function getAssignmentCountBySubject($batchId,$semId,$subjectId) | |
| { | |
| $sql = "SELECT count(distinct assiNu) as count from batch_assignment where batchID = $batchId and semID = $semId and subjectID = $subjectId AND assiNu is not null AND assiNu !=0"; | |
| try { | |
| $assignmentCount = $this->executeQueryForObject($sql)->count; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); | |
| } | |
| return $assignmentCount; | |
| } | |
| /** | |
| * get student assignment marks | |
| * @param int $studentId | |
| * @param int $batchId | |
| * @param int $semId | |
| * @throws ProfessionalException | |
| * @return object|array|\com\linways\base\util\$objectList[] | |
| */ | |
| public function getAssignmentMarkListByStudent($studentId, $batchId, $semId, $subjectId = null) | |
| { | |
| $assignmentList=NULL; | |
| $studentId= $this->realEscapeString($studentId); | |
| $batchId= $this->realEscapeString($batchId); | |
| $semId= $this->realEscapeString($semId); | |
| $subjectId = $this->realEscapeString($subjectId); | |
| $sql = "SELECT sa.studentID, sa.studentName, ba.subjectID, sub.subjectName, ba.assignmentID, ba.assiNu, ba.max_mark, am.marksObtained,concat(ba.subjectID,ba.assignmentID,ba.assiNu) as subjAssign, ba.description,sub.subjectDesc,ba.is_published_student_marks as isPublishedStudentMarks FROM batch_assignment ba INNER JOIN subjects sub ON ba.subjectID = sub.subjectID INNER JOIN studentaccount sa ON ba.batchID=sa.batchID AND sa.studentID=$studentId LEFT JOIN assignment_marks am ON am.assignmentID=ba.assignmentID WHERE ba.batchID = $batchId AND ba.semID = $semId AND am.csID IS NULL AND ba.assiNu is not null AND ba.assiNu !=0 AND am.studentID = $studentId "; | |
| if($subjectId){ | |
| $sql .= " AND sub.subjectID = $subjectId"; | |
| } | |
| $sql .= " ORDER BY ba.assiNu ASC"; | |
| try { | |
| $assignmentList = $this->executeQueryForList($sql,$this->mapper[AssignmentServiceMapper::GET_ASSIGNMENT_MARK_LIST_BY_STUDENT]); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); | |
| } | |
| return $assignmentList; | |
| } | |
| /** | |
| * Undocumented function | |
| * | |
| * @param [type] $studentId | |
| * @param [type] $batchId | |
| * @param [type] $semId | |
| * @param [type] $subjectId | |
| * @return void | |
| */ | |
| public function getAllAssignmentMarkListByStudentId($studentId, $batchId, $semId, $subjectId = null) | |
| { | |
| $assignmentList=NULL; | |
| $studentId= $this->realEscapeString($studentId); | |
| $batchId= $this->realEscapeString($batchId); | |
| $semId= $this->realEscapeString($semId); | |
| $subjectId = $this->realEscapeString($subjectId); | |
| $sql = "SELECT ba.subjectID, sub.subjectName, ba.assignmentID, ba.assiNu, ba.max_mark as maxMarks, am.marksObtained,concat(ba.subjectID,ba.assignmentID,ba.assiNu) as subjAssign,ba.is_published_student_marks as isPublishedStudentMarks FROM batch_assignment ba INNER JOIN subjects sub ON ba.subjectID = sub.subjectID LEFT JOIN assignment_marks am ON am.assignmentID=ba.assignmentID WHERE ba.batchID = $batchId AND ba.semID = $semId AND am.csID IS NULL AND am.studentID = $studentId "; | |
| if($subjectId){ | |
| $sql .= " AND sub.subjectID = $subjectId"; | |
| } | |
| $sql .= " ORDER BY ba.assiNu ASC"; | |
| try { | |
| $assignmentList = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); | |
| } | |
| return $assignmentList; | |
| } | |
| public function getCoValuesForAnAssignment($assignmentId, $studentId, $batchId, $semId, $subjectId){ | |
| $assignmentId= $this->realEscapeString($assignmentId); | |
| $studentId= $this->realEscapeString($studentId); | |
| $batchId= $this->realEscapeString($batchId); | |
| $semId= $this->realEscapeString($semId); | |
| $subjectId = $this->realEscapeString($subjectId); | |
| $sql = "SELECT ba.max_mark as maxMarks, nco.nba_course_outcome_id, co.code, co.objective,nco.nba_course_outcome_value as courseOutcomeValue, am.marksObtained FROM batch_assignment ba INNER JOIN subjects sub ON ba.subjectID = sub.subjectID LEFT JOIN assignment_marks am ON am.assignmentID=ba.assignmentID LEFT JOIN nba_assignment_co_relation nco ON nco.nba_batch_assignment_id = ba.assignmentID AND nco.nba_batch_assignment_id = am.assignmentID LEFT JOIN nba_course_outcome co ON co.id = nco.nba_course_outcome_id WHERE ba.batchID = $batchId AND ba.semID = $semId AND am.csID IS NULL AND am.studentID = $studentId AND sub.subjectID = $subjectId AND am.assignmentID = $assignmentId"; | |
| try { | |
| $coList = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); | |
| } | |
| foreach($coList as $co){ | |
| if(empty($co->courseOutcomeValue)){ | |
| $co->coObtained = NULL; | |
| continue; | |
| } | |
| $co->coObtained = ($co->marksObtained/$co->maxMarks)*100; | |
| } | |
| return $coList; | |
| } | |
| public function getAssignmentNumByBatch($batchId) | |
| { | |
| $assignmentList=NULL; | |
| $sql = "SELECT DISTINCT(ba.assiNu) FROM batch_assignment ba INNER JOIN batches bth ON bth.batchID=ba.batchID WHERE ba.batchID = '".$batchId."'"; | |
| try { | |
| $assignmentList = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); | |
| } | |
| return $assignmentList; | |
| } | |
| /** | |
| * Delete assignment marks by assignentId | |
| * @param int $assignmentId | |
| * @return boolean | |
| */ | |
| public function deleteAssignmentMarksById($assignmentId) | |
| { | |
| $assignmentId = $this->realEscapeString($assignmentId); | |
| $sql = "DELETE FROM assignment_marks WHERE assignmentID=$assignmentId"; | |
| try{ | |
| $this->executeQueryForList($sql); | |
| }catch (\Exception $e){ | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return true; | |
| } | |
| /** | |
| * get assignment details by subject id | |
| * @param int $batchId | |
| * @param int $subjectId | |
| * @param int $staffId | |
| * @throws ProfessionalException | |
| * @return object|array|\com\linways\base\util\$objectList[] | |
| */ | |
| public function getAssignmentDetailsBySubjectId($batchId, $subjectId, $staffId, $semId = null, $subbatchID = null) | |
| { | |
| $batchId = $this->realEscapeString($batchId); | |
| $subjectId = $this->realEscapeString($subjectId); | |
| $staffId = $this->realEscapeString($staffId); | |
| $conditions = ""; | |
| if ( $semId ) { | |
| $semId = $this->realEscapeString($semId); | |
| $conditions .= " AND bs.semID = ".$semId." "; | |
| } | |
| if($subbatchID){ | |
| $subbatchID = $this->realEscapeString($subbatchID); | |
| $conditions .= " AND bs.subbatchID = ".$subbatchID." "; | |
| } | |
| $assignmentList=[]; | |
| $sql = "SELECT bs.assignmentID, bs.question, bs.description, bs.submissionDate, bs.submissionTime, bs.assiNu,bs.docName, bs.docPath, bs.max_mark, bs.returnDate, bs.subbatchID, if(bs.subbatchID=0, 'All', sub.subbatchName) AS subbatchName, restrictStudentSubmit, bs.is_published as isPublished, bs.externalAssiNu, bs.publishDate, bs.publishTime, bs.userType, bs.enableConfirm, bs.is_published_student_marks as isPublishedStudentMarks FROM batch_assignment bs LEFT JOIN subbatches sub ON bs.subbatchID=sub.subbatchID WHERE bs.batchID=$batchId AND bs.subjectID=$subjectId AND bs.staffID=$staffId ".$conditions." ORDER BY bs.assignmentID DESC"; | |
| try{ | |
| $assignmentList = $this->executeQueryForList($sql,$this->mapper[AssignmentServiceMapper::GET_ASSIGNMENT_DETAILS]); | |
| }catch (\Exception $e){ | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $assignmentList; | |
| } | |
| /** | |
| * get assignment details by assignmentId | |
| * @param int $assignmentId | |
| * @throws ProfessionalException | |
| * @return object|array|\com\linways\base\util\$objectList[] | |
| */ | |
| public function getAssignmentDetailsById($assignmentId) | |
| { | |
| $assignmentId = $this->realEscapeString($assignmentId); | |
| $assignmentDetails=null; | |
| $sql = "select bs.assignmentID, bs.question, bs.description, bs.submissionDate, bs.submissionTime, bs.assiNu,bs.docName, bs.docPath, bs.max_mark, bs.returnDate, bs.subbatchID, if(bs.subbatchID=0, 'All', sub.subbatchName) as subbatchName, bs.externalAssiNu, bs.publishDate, bs.publishTime, bs.is_published as isPublished, bs.enableConfirm, bs.asyncReportsId, ar.lin_resource_id as linResourseId,bs.is_published_student_marks as isPublishedStudentMarks from batch_assignment bs LEFT JOIN subbatches sub ON bs.subbatchID=sub.subbatchID left join async_reports ar ON bs.asyncReportsId=ar.id where bs.assignmentID=$assignmentId"; | |
| try{ | |
| $assignmentDetails = $this->executeQueryForObject($sql,false,$this->mapper[AssignmentServiceMapper::GET_ASSIGNMENT_DETAILS]); | |
| }catch (\Exception $e){ | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $assignmentDetails; | |
| } | |
| /** | |
| * get assignment details for the subject by assiNu | |
| * @param int $batchId | |
| * @param int $semId | |
| * @param int $subjectId | |
| * @param int $assiNu | |
| * @throws ProfessionalException | |
| * @return boolean | |
| */ | |
| public function getAssignmentDetailsByAssiNu($batchId, $semId, $subjectId, $assiNu) | |
| { | |
| $batchId = $this->realEscapeString($batchId); | |
| $semId = $this->realEscapeString($semId); | |
| $assiNu = $this->realEscapeString($assiNu); | |
| $subjectId = $this->realEscapeString($subjectId); | |
| $sql = "select assignmentID,max_mark from batch_assignment where batchId = $batchId and semId = $semId and subjectID = $subjectId and assiNu = $assiNu"; | |
| try { | |
| $assignmentId = $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $assignmentId; | |
| } | |
| /** | |
| * Create assignmet | |
| * @param StudentAssignmentDetails $assignmentDetail | |
| * @throws ProfessionalException | |
| * @return object|NULL|\com\linways\base\util\$objectList[] | |
| */ | |
| public function createAssignment($assignmentDetail) | |
| { | |
| $assignmentDetail = $this->realEscapeObject($assignmentDetail); | |
| $sql = "INSERT into batch_assignment (question, submissionDate, batchID, staffID, subjectID, assiNu, semID, max_mark) values (\"$assignmentDetail->question\", UTC_TIMESTAMP(), $assignmentDetail->batchId, $assignmentDetail->staffId, $assignmentDetail->subjectId, $assignmentDetail->assiNu, $assignmentDetail->semId, $assignmentDetail->maxMark )"; | |
| try { | |
| return $this->executeQueryForObject($sql,true); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| public function createNewAssignment($assignmentDetail) | |
| { | |
| $assignmentDetail = $this->realEscapeObject($assignmentDetail); | |
| $response = new \stdClass(); | |
| $sql = "INSERT into batch_assignment (description, submissionDate, submissionTime, batchID, staffID, subjectID, assiNu, semID, max_mark, returnDate, subbatchID, restrictStudentSubmit, psID, publishDate, publishTime, userType, externalAssiNu, is_published, enableConfirm) values ('".$assignmentDetail->description."','$assignmentDetail->submissionDate','$assignmentDetail->submissionTime','$assignmentDetail->batchId','$assignmentDetail->staffId','$assignmentDetail->subjectId','$assignmentDetail->assiNu','$assignmentDetail->semId','$assignmentDetail->max_mark','$assignmentDetail->returnDate','$assignmentDetail->subbatchId', '$assignmentDetail->restrictStudentSubmit', '$assignmentDetail->pseudoSubjectId', '$assignmentDetail->publishDate', '$assignmentDetail->publishTime', '$assignmentDetail->userType', '$assignmentDetail->externalAssiNu', $assignmentDetail->isPublished, '$assignmentDetail->enableConfirm')"; | |
| try { | |
| $response->batchAssignmentId = $this->executeQueryForObject($sql,true); | |
| $sql_insert_into_assessment_structure = "INSERT INTO assessment_structure ( subjectID, staffID, batchID, semID, maxMark, duration, subbatchID, assessment_key, assessment_type) VALUE ('$assignmentDetail->subjectId', '$assignmentDetail->staffId', '$assignmentDetail->batchId', '$assignmentDetail->semId', '$assignmentDetail->max_mark', '0', '$assignmentDetail->subbatchId', '".($assignmentDetail->externalAssiNu?$assignmentDetail->externalAssiNu:$assignmentDetail->assiNu)."', 'ASSIGNMENT')"; | |
| $response->assessmentStructureId = $this->executeQuery($sql_insert_into_assessment_structure,true)->id; | |
| return $response; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| public function updateAssignment($assignmentDetail){ | |
| $assignmentDetail = $this->realEscapeObject($assignmentDetail); | |
| $batchAssignmentConditions = null; | |
| if ( $assignmentDetail->pseudoSubjectId ) { | |
| $batchAssignmentConditions .= " | |
| subjectID = '$assignmentDetail->subjectId' | |
| AND staffID = '$assignmentDetail->staffId' | |
| AND batchID='$assignmentDetail->batchId' | |
| AND semID = '$assignmentDetail->semId' | |
| AND subbatchID = '$assignmentDetail->subbatchId' | |
| AND psID = '$assignmentDetail->pseudoSubjectId' "; | |
| if($assignmentDetail->assiNu) | |
| { | |
| $batchAssignmentConditions .= " AND assiNu = '$assignmentDetail->assiNu'"; | |
| } | |
| else | |
| { | |
| $batchAssignmentConditions .= " AND externalAssiNu = '$assignmentDetail->externalAssiNu'"; | |
| } | |
| } else { | |
| $batchAssignmentConditions .= " assignmentID IN ($assignmentDetail->id)"; | |
| } | |
| $sql = "UPDATE batch_assignment SET | |
| description = '" . $assignmentDetail->description . "', | |
| submissionDate = '" . $assignmentDetail->submissionDate . "', | |
| submissionTime = '" . $assignmentDetail->submissionTime . "', | |
| max_mark = '" . $assignmentDetail->max_mark . "', | |
| returnDate = '" . $assignmentDetail->returnDate . "', | |
| publishDate = '".$assignmentDetail->publishDate."', | |
| publishTime = '".$assignmentDetail->publishTime."', | |
| restrictStudentSubmit = '" . $assignmentDetail->restrictStudentSubmit . "', | |
| enableConfirm = '$assignmentDetail->enableConfirm' | |
| WHERE $batchAssignmentConditions "; | |
| try { | |
| $this->executeQueryForObject($sql,true); | |
| $condition = ""; | |
| if($assignmentDetail->subbatchId){ | |
| $condition .= "AND subbatchID = '$assignmentDetail->subbatchId'"; | |
| } | |
| $sql_update_assessment_structure = " | |
| UPDATE assessment_structure SET | |
| maxMark = '" . $assignmentDetail->max_mark . "' | |
| WHERE subjectID ='$assignmentDetail->subjectId' AND staffID = '$assignmentDetail->staffId' AND batchID='$assignmentDetail->batchId' AND semID = '$assignmentDetail->semId' AND assessment_key = '$assignmentDetail->assiNu' AND assessment_type = 'ASSIGNMENT'" .$condition.""; | |
| return $this->executeQueryForObject($sql_update_assessment_structure,true); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| public function updateAssignmentIsPublish($assignmentDetail){ | |
| $assignmentDetail = $this->realEscapeObject($assignmentDetail); | |
| $batchAssignmentConditions = null; | |
| if ( $assignmentDetail->pseudoSubjectId ) { | |
| $batchAssignmentConditions .= " | |
| subjectID = '$assignmentDetail->subjectId' | |
| AND staffID = '$assignmentDetail->staffId' | |
| AND batchID='$assignmentDetail->batchId' | |
| AND semID = '$assignmentDetail->semId' | |
| AND subbatchID = '$assignmentDetail->subbatchId' | |
| AND psID = '$assignmentDetail->pseudoSubjectId' "; | |
| if($assignmentDetail->assiNu==0) | |
| { | |
| $batchAssignmentConditions .= "AND externalAssiNu = '$assignmentDetail->externalAssiNu'"; | |
| } | |
| else | |
| { | |
| $batchAssignmentConditions .= "AND assiNu = '$assignmentDetail->assiNu'"; | |
| } | |
| } else { | |
| $batchAssignmentConditions .= " assignmentID IN ($assignmentDetail->id)"; | |
| } | |
| $sql = "UPDATE batch_assignment SET | |
| is_published = '" . $assignmentDetail->isPublished . "' | |
| WHERE $batchAssignmentConditions "; | |
| try { | |
| $this->executeQueryForObject($sql,true); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| public function deleteAssignment($assignmentDetail) | |
| { | |
| $assignmentDetail = $this->realEscapeObject($assignmentDetail); | |
| $batchAssignmentConditions = null; | |
| if ( $assignmentDetail->pseudoSubjectId ) { | |
| $batchAssignmentConditions .= " | |
| AND subjectID = '$assignmentDetail->subjectId' | |
| AND staffID = '$assignmentDetail->staffId' | |
| AND batchID='$assignmentDetail->batchId' | |
| AND semID = '$assignmentDetail->semId' | |
| AND subbatchID = '$assignmentDetail->subbatchId' | |
| AND psID = '$assignmentDetail->pseudoSubjectId' "; | |
| if($assignmentDetail->externalAssiNu) | |
| { | |
| $batchAssignmentConditions .= " AND externalAssiNu = '$assignmentDetail->externalAssiNu' "; | |
| } | |
| else | |
| { | |
| $batchAssignmentConditions .= " AND assiNu = '$assignmentDetail->assiNu' "; | |
| } | |
| } else { | |
| if($assignmentDetail->externalAssiNu) | |
| { | |
| $batchAssignmentConditions .= " AND externalAssiNu = '$assignmentDetail->externalAssiNu' "; | |
| } | |
| $batchAssignmentConditions .= " AND assignmentID IN ($assignmentDetail->assignmentID)"; | |
| } | |
| $sql = "DELETE FROM batch_assignment WHERE assignmentID is not null $batchAssignmentConditions "; | |
| try { | |
| $condition=""; | |
| if($assignmentDetail->subbatchId){ | |
| $condition .= "AND subbatchID = '$assignmentDetail->subbatchId'"; | |
| } | |
| $sql_delete_from_assessment_structure = "DELETE from assessment_structure where subjectID ='$assignmentDetail->subjectId' AND staffID = '$assignmentDetail->staffId' AND batchID='$assignmentDetail->batchId' AND semID = '$assignmentDetail->semId' AND assessment_key = '".($assignmentDetail->externalAssiNu?$assignmentDetail->externalAssiNu:$assignmentDetail->assiNu)."' AND assessment_type = 'ASSIGNMENT'" .$condition." "; | |
| $this->executeQuery($sql_delete_from_assessment_structure,true); | |
| return $this->executeQuery($sql,true); | |
| } catch (\Exception $e) { | |
| if($e->getCode() == 'CANNOT_DELETE_OR_UPDATE_ROW_FOREIGN_KEY_FAILED'){ | |
| throw new ProfessionalException($e->getCode(), 'Assignment cannot be deleted since Assignment contain questions.'); | |
| } | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * Get assignement mark of student by assignmentId | |
| * @param int $assignmentId | |
| * @param int $studentId | |
| * @throws ProfessionalException | |
| * @return object|NULL|\com\linways\base\util\$objectList[] | |
| */ | |
| public function getStudentAssignmentMarkByAssiNu($assignmentId, $studentId) | |
| { | |
| $assignmentId = $this->realEscapeString($assignmentId); | |
| $studentId = $this->realEscapeString($studentId); | |
| $sql = "select marksObtained from assignment_marks am inner join batch_assignment ba on am.assignmentID = ba.assignmentID where am.assignmentID = $assignmentId and am.studentID = $studentId"; | |
| try { | |
| $assignmentMark = $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $assignmentMark; | |
| } | |
| /** | |
| * Create assignment mark of student | |
| * @param StudentAssignmentDetails $assignmentDetail | |
| * @throws ProfessionalException | |
| * @return object|NULL|\com\linways\base\util\$objectList[] | |
| */ | |
| public function createStudentAssignmentMarks($assignmentDetail) | |
| { | |
| $assignmentDetail = $this->realEscapeObject($assignmentDetail); | |
| $sql = "insert into assignment_marks (studentID, marksObtained, assignmentID, percentage) values ($assignmentDetail->studentId, $assignmentDetail->mark, $assignmentDetail->assignmentId, $assignmentDetail->percentage)"; | |
| try { | |
| return $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * Update assignment mark of student | |
| * @param StudentAssignmentDetails $assignmentDetail | |
| * @throws ProfessionalException | |
| * @return \com\linways\base\dto\MySqlResult | |
| */ | |
| public function updateStudentAssignmentMarks($assignmentDetail) | |
| { | |
| $sql = "update assignment_marks set marksObtained = $assignmentDetail->mark , percentage = $assignmentDetail->percentage where assignmentID = $assignmentDetail->assignmentId and studentID = $assignmentDetail->studentId"; | |
| try { | |
| return $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * Delete student assignment marks | |
| * @param int $studentId | |
| * @param int $assignmentId | |
| * @throws ProfessionalException | |
| * @return \com\linways\base\dto\MySqlResult | |
| */ | |
| public function deleteStudentAssignmentMarks($studentId,$assignmentId) | |
| { | |
| $sql = "delete from assignment_marks where assignmentID = $assignmentId and studentID = $studentId"; | |
| try { | |
| return $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * Get assignment details in a date range by sbsId | |
| * @param int $sbsId | |
| * @param string $fromDate | |
| * @param string $toDate | |
| * @throws ProfessionalException | |
| * @return object|array|\com\linways\base\util\$objectList[] | |
| */ | |
| public function getAssignmentDetailsBySbsId($sbsId, $fromDate, $toDate) | |
| { | |
| $sbsId = $this->realEscapeString($sbsId); | |
| $fromDate = $this->realEscapeString($fromDate); | |
| $toDate = $this->realEscapeString($toDate); | |
| $sbs = SubjectService::getInstance()->getSubjectDetailsBySbsId($sbsId); | |
| $semId = $sbs->semID; | |
| $batchId = $sbs->batchID; | |
| $semDetails = SemesterService::getInstance()->getSemDetailsBySemId($semId); | |
| $isCurrentSem = SemesterService::getInstance()->isCurrentSemester($batchId, $semId); | |
| if($isCurrentSem) | |
| { | |
| $sql = "select noOfassign, count(pending) as pending, sum(case when pending is null then 1 end) as submitted from (SELECT assignmentID, sa.studentID, isSubmited, count(assignmentID) as noOfassign, sum(case when isSubmited = 0 then 1 end) as pending FROM batch_assignment ba inner join sbs_relation sr on ba.batchID = sr.batchID and ba.semID = sr.semID and ba.subjectID = sr.subjectID and ba.staffID = sr.staffID left join student_assinments sa on sa.assinmentID = ba.assignmentID left join studentaccount sta on sa.studentID = sta.studentID left join semesters sem on sem.semID = sta.joiningSemId where sr.sbsID = $sbsId and ba.submissionDate between '$fromDate' and '$toDate' and sem.orderNo <= ".$semDetails->orderNo." group by sa.studentID) as assign; "; | |
| } | |
| else | |
| { | |
| $sql = "select noOfassign, count(pending) as pending, sum(case when pending is null then 1 end) as submitted from (SELECT assignmentID, sa.studentID, isSubmited, count(assignmentID) as noOfassign, sum(case when isSubmited = 0 then 1 end) as pending FROM batch_assignment ba inner join sbs_relation sr on ba.batchID = sr.batchID and ba.semID = sr.semID and ba.subjectID = sr.subjectID and ba.staffID = sr.staffID left join student_assinments sa on sa.assinmentID = ba.assignmentID left join studentaccount sta on sa.studentID = sta.studentID where sr.sbsID = ".$sbsId." and ba.submissionDate between '".$fromDate."' and '".$toDate."' and sa.studentID in (select sa.studentID from studentaccount sa inner join batches ba on sa.batchID = ba.batchID inner join semesters joinedSem on sa.joiningSemId = joinedSem.semID where ba.batchID = ".$batchId." and joinedSem.orderNo <= ".$semDetails->orderNo." | |
| union select sa.studentID from failed_students fs left join studentaccount sa on fs.studentID= sa.studentID inner join semesters fsem on fsem.semID = fs.failedInSemester inner join semesters joinedSem on sa.joiningSemId = joinedSem.semID where previousBatch = ".$batchId." and fsem.orderNo > ".$semDetails->orderNo." and joinedSem.orderNo <= ".$semDetails->orderNo.") group by sa.studentID) as assign; "; | |
| } | |
| try | |
| { | |
| return $this->executeQueryForObject($sql); | |
| } | |
| catch (\Exception $e) | |
| { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| public function isSubmissionAllowed($assignmentID) | |
| { | |
| $allowed=true; | |
| $assignmentDetails=''; | |
| $submissionDate=''; | |
| $submissionTime=''; | |
| $sql = "SELECT submissionDate, submissionTime, restrictStudentSubmit from batch_assignment WHERE assignmentID=$assignmentID"; | |
| try { | |
| $assignmentDetails = $this->executeQueryForObject($sql); | |
| if(!empty($assignmentDetails)) | |
| { | |
| $restrictStudentSubmit = $assignmentDetails->restrictStudentSubmit; | |
| if($assignmentDetails->submissionDate && $restrictStudentSubmit=='1') | |
| { | |
| $submissionDate = date('Y-m-d', strtotime($assignmentDetails->submissionDate)); | |
| $date = date("Y-m-d"); | |
| if($date == $submissionDate) | |
| { | |
| if($assignmentDetails->submissionTime) | |
| { | |
| $currentTime = date('H:i:s'); | |
| $submissionTime = date("H:i:s", strtotime($assignmentDetails->submissionTime)); | |
| if ($currentTime <= $submissionTime) | |
| { | |
| $allowed=true; | |
| } | |
| else | |
| { | |
| $allowed=false; | |
| } | |
| } | |
| } | |
| else if($date < $submissionDate) | |
| { | |
| $allowed=true; | |
| } | |
| else | |
| { | |
| $allowed=false; | |
| } | |
| } | |
| } | |
| return $allowed; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * Get assignment numbers given for a subject in a sem | |
| * @param int $batchId | |
| * @param int $subjectId | |
| * @param int $semId | |
| * @throws ProfessionalException | |
| * @return object|array|\com\linways\base\util\$objectList[] | |
| */ | |
| public function getAssignmentNumsOfAsubject($batchId, $subjectId, $semId) | |
| { | |
| $batchId = $this->realEscapeString($batchId); | |
| $semId = $this->realEscapeString($semId); | |
| $subjectId = $this->realEscapeString($subjectId); | |
| $sql = "select distinct(assiNu) FROM batch_assignment bs WHERE bs.batchID=$batchId AND bs.subjectID=$subjectId AND bs.semID = $semId ORDER BY bs.assiNu;"; | |
| try{ | |
| return $this->executeQueryForList($sql); | |
| }catch (\Exception $e){ | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * Get max assignment number | |
| * @param int $batchId | |
| * @param int $subjectId | |
| * @param int $semId | |
| * @throws ProfessionalException | |
| * @return object|array|\com\linways\base\util\$objectList[] | |
| * @author Vishnu M | |
| */ | |
| public function getMaxAssignmentNumber ( $batchId, $semId, $subjectId = null) | |
| { | |
| $maxAssignmentNum = 0; | |
| $batchId = $this->realEscapeString($batchId); | |
| $semId = $this->realEscapeString($semId); | |
| $subjectId = $this->realEscapeString($subjectId); | |
| $maxAssignmentNum = $this->getBatchwiseMaxAssignmentNumber ( $batchId, $semId, $subjectId ); | |
| if ( !$maxAssignmentNum ) { | |
| $maxAssignmentNum = $this->getGeneralMaxAssignmentNumberByBatchId ( $batchId ); | |
| } | |
| return $maxAssignmentNum > 0 ? $maxAssignmentNum : 0; | |
| } | |
| public function getBatchwiseMaxAssignmentNumber ( $batchId, $semId, $subjectId = null ) { | |
| $batchId = $this->realEscapeString($batchId); | |
| $semId = $this->realEscapeString($semId); | |
| $subjectId = $this->realEscapeString($subjectId); | |
| $maxAssignmentNum = 0; | |
| if ( $subjectId ) { | |
| $sqlSubjectSpecific = "SELECT maxassignmentNum FROM batchwise_assignment_rules WHERE batchID = ".$batchId." AND semID = ".$semId." AND subjectID = ".$subjectId.""; | |
| try{ | |
| $maxAssignmentNum = $this->executeQueryForObject($sqlSubjectSpecific)->maxassignmentNum; | |
| } | |
| catch (\Exception $e){ | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| if ( !$maxAssignmentNum ) { | |
| $sql = "SELECT maxassignmentNum FROM batchwise_assignment_rules WHERE batchID = ".$batchId." AND semID = ".$semId." AND subjectID IS NULL"; | |
| try { | |
| $maxAssignmentNum = $this->executeQueryForObject($sql)->maxassignmentNum; | |
| } | |
| catch (\Exception $e){ | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| return $maxAssignmentNum > 0 ? $maxAssignmentNum : 0; | |
| } | |
| public function getBatchOrSubjectwiseAssignmentRules($request){ | |
| $sql = "SELECT b.batchName,sem.semName,s.subjectName,ba.maxassignmentNum FROM batchwise_assignment_rules ba LEFT JOIN subjects s ON ba.subjectID = s.subjectID LEFT JOIN batches b ON b.batchID = ba.batchID LEFT JOIN semesters sem ON sem.semID = ba.semID WHERE 1=1"; | |
| if($request->courseTypeId){ | |
| $sql .= " AND b.courseTypeID = $request->courseTypeId"; | |
| } | |
| if($request->batchId){ | |
| $sql .= " AND b.batchID = $request->batchId"; | |
| } | |
| if($request->semId){ | |
| $sql .= " AND sem.semID = $request->semId"; | |
| } | |
| if($request->subjectSpec){ | |
| $sql .= " AND s.subjectName is not null"; | |
| } | |
| try { | |
| return $this->executeQueryForList($sql); | |
| } | |
| catch (\Exception $e){ | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| public function deleteBatchOrSubjectSpecificAssignmentRules($request){ | |
| $sql = "DELETE ba FROM batchwise_assignment_rules ba LEFT JOIN subjects s ON ba.subjectID = s.subjectID LEFT JOIN batches b ON b.batchID = ba.batchID LEFT JOIN semesters sem ON sem.semID = ba.semID WHERE 1=1"; | |
| if($request->batchId){ | |
| $sql .= " AND b.batchID = $request->batchId"; | |
| } | |
| if($request->semId){ | |
| $sql .= " AND sem.semID = $request->semId"; | |
| } | |
| if($request->subjectSpec){ | |
| $sql .= " AND s.subjectName is not null"; | |
| } | |
| if($request->courseTypeId){ | |
| $sql .= " AND b.courseTypeID = $request->courseTypeId"; | |
| } | |
| try { | |
| return $this->executeQueryForList($sql); | |
| } | |
| catch (\Exception $e){ | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| public function getGeneralMaxAssignmentNumberByBatchId ( $batchId ) { | |
| $batchId = $this->realEscapeString($batchId); | |
| $sql = "SELECT rule.maxassignmentNum FROM general_assignment_rules rule INNER JOIN batches b ON (rule.courseTypeID = b.courseTypeID) WHERE b.batchID = ".$batchId.""; | |
| try { | |
| return $this->executeQueryForObject($sql)->maxassignmentNum; | |
| } | |
| catch (\Exception $e){ | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * get assignment number rules defined for courses | |
| * @return array|null | |
| */ | |
| public function getGeneralMaxAssignmentNumberDetails(){ | |
| $sql = ""; | |
| $sql = "SELECT ga.id, ga.maxassignmentNum, ct.typeName FROM general_assignment_rules ga, course_type ct WHERE ct.courseTypeID=ga.courseTypeID ORDER BY ga.id"; | |
| try { | |
| return $this->executeQueryForList($sql); | |
| } | |
| catch (\Exception $e){ | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * save general assignment number rule | |
| * @param $courseTypeId | |
| * @param $assignmentNo | |
| * @return $id|null | |
| */ | |
| public function saveOrUpdateGeneralAssignmentRule($courseTypeId,$assignmentNo){ | |
| try{ | |
| $sql_rule_exist = "SELECT maxassignmentNum,id FROM general_assignment_rules WHERE courseTypeID = $courseTypeId"; | |
| $rule_exist = $this->executeQueryForObject($sql_rule_exist); | |
| if(empty($rule_exist)){ | |
| $sql = "INSERT INTO general_assignment_rules (courseTypeID, maxassignmentNum) VALUES ($courseTypeId,$assignmentNo)"; | |
| return $this->executeQueryForObject($sql, true); | |
| } | |
| else{ | |
| $sql = "UPDATE general_assignment_rules SET maxassignmentNum = $assignmentNo WHERE id = $rule_exist->id"; | |
| return $this->executeQuery($sql); | |
| } | |
| } | |
| catch (\Exception $e) { | |
| throw new ProfessionalException ($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * delete general assignment number rule | |
| * @param $ruleId | |
| * @return null | |
| */ | |
| public function deleteGeneralAssignmentRulesById($ruleId){ | |
| $sql = "DELETE FROM general_assignment_rules WHERE id = $ruleId"; | |
| try { | |
| return $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| public function getGeneralMaxAssignmentNumberByCourseTypeId ( $courseTypeId ) { | |
| $courseTypeId = $this->realEscapeString($courseTypeId); | |
| $sql = "SELECT maxassignmentNum FROM general_assignment_rules WHERE courseTypeID = ".$courseTypeId.""; | |
| try { | |
| return $this->executeQueryForObject($sql)->maxassignmentNum; | |
| } | |
| catch (\Exception $e){ | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| public function getMaxAssignmentNumberForPseudoSubject ( $pseudoSubjectId ) { | |
| $maxAssignmentNum = 0; | |
| $pseudoSubjectId = $this->realEscapeString($pseudoSubjectId); | |
| $sql = "SELECT MAX(bar.maxassignmentNum) AS maxAssignmentNum FROM batchwise_assignment_rules bar INNER JOIN sbs_relation sbs ON (bar.batchID = sbs.batchID AND bar.semID = sbs.semID AND (bar.subjectID = sbs.subjectID OR bar.subjectID IS NULL)) INNER JOIN pseudosubjects_sbs psbs ON (psbs.sbsID = sbs.sbsID) WHERE psbs.pseudosubjectID = ".$pseudoSubjectId; | |
| try { | |
| $maxAssignmentNum = $this->executeQueryForObject($sql)->maxAssignmentNum; | |
| if ( !$maxAssignmentNum) { | |
| $sql = "SELECT MAX(gar.maxassignmentNum) AS maxAssignmentNum FROM general_assignment_rules gar INNER JOIN batches b ON (b.courseTypeID = gar.courseTypeID) INNER JOIN sbs_relation sbs ON (b.batchID = sbs.batchID AND b.semID = sbs.semID) INNER JOIN pseudosubjects_sbs psbs ON (psbs.sbsID = sbs.sbsID) WHERE psbs.pseudosubjectID = ".$pseudoSubjectId; | |
| $maxAssignmentNum = $this->executeQueryForObject($sql)->maxAssignmentNum; | |
| } | |
| } | |
| catch (\Exception $e){ | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $maxAssignmentNum > 0 ? $maxAssignmentNum : 0; | |
| } | |
| /** | |
| * get assignment details by subject id | |
| * @param int $batchId | |
| * @param string $submissionDate | |
| * @param int $deptId | |
| * @throws ProfessionalException | |
| * @return object|array|\com\linways\base\util\$objectList[] | |
| */ | |
| public function getAssignmentDetailsBySubmissionDate($submissionDate, $deptId='', $batchId='') | |
| { | |
| $batchId = $this->realEscapeString($batchId); | |
| $deptId = $this->realEscapeString($deptId); | |
| $submissionDate = $this->realEscapeString($submissionDate); | |
| $sql = "SELECT bs.batchID, bs.assignmentID, bs.question, bs.description, bs.submissionDate, bs.submissionTime, bs.assiNu,bs.docName, bs.docPath, bs.max_mark, bs.returnDate, bs.subbatchID, bs.question, if(bs.subbatchID=0, 'All', sub.subbatchName) AS subbatchName, bt.batchName, subj.subjectName, subj.subjectDesc, sa.staffName FROM batch_assignment bs INNER JOIN batches bt ON bt.batchID=bs.batchID AND bt.semID=bs.semID INNER JOIN subjects subj ON subj.subjectID=bs.subjectID INNER JOIN staffaccounts sa ON sa.staffID=bs.staffID LEFT JOIN subbatches sub ON bs.subbatchID=sub.subbatchID WHERE submissionDate='".date('Y-m-d', strtotime($submissionDate))."' AND bs.assiNu is not null AND bs.assiNu !=0 "; | |
| if($deptId) | |
| { | |
| $sql .="AND bt.deptID=$deptId "; | |
| } | |
| if($batchId) | |
| { | |
| $sql .="AND bs.batchID=$batchId "; | |
| } | |
| $sql .="ORDER BY bs.assignmentID DESC"; | |
| try | |
| { | |
| return $this->executeQueryForList($sql); | |
| } | |
| catch(\Exception $e){ | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| public function getAssignmentDetailsByBatchSemAndSubjectId($batchID, $semID, $subjectId){ | |
| $batchID = $this->realEscapeString($batchID); | |
| $semID = $this->realEscapeString($semID); | |
| $subjectId = $this->realEscapeString($subjectId); | |
| $sql = "SELECT assignmentID as id, question, assiNu, max_mark as maxMark FROM batch_assignment WHERE batchID = $batchID AND subjectID = $subjectId AND semID = $semID ORDER BY assiNu"; | |
| try | |
| { | |
| $assignmentList = $this->executeQueryForList($sql); | |
| } | |
| catch(\Exception $e){ | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $assignmentList; | |
| } | |
| public function getAllStudentMarksAndCoReportOfABatchAndSemester($batchID, $semID, $subjectId = null, $subbatchID = 0, $sortByColumn = "rollNo"){ | |
| $batchID = $this->realEscapeString($batchID); | |
| $semID = $this->realEscapeString($semID); | |
| $subjectId = $this->realEscapeString($subjectId); | |
| $subbatchID = $this->realEscapeString($subbatchID); | |
| $sortByColumn = $this->realEscapeString($sortByColumn); | |
| try{ | |
| $studentList = StudentService::getInstance()->getAllStudentsOfABatchByBatchIdSemIdAndSubbatchId($batchID, $semID,$subbatchID, $sortByColumn); | |
| }catch(\Exception $e){ | |
| $studentList = null; | |
| } | |
| if(empty($studentList)){ | |
| return null; | |
| } | |
| foreach($studentList as $student){ | |
| $student->assignmentMarkAndCoDetails = $this->getAllAssignmentMarkListByStudentId($student->studentID, $batchID, $semID, $subjectId); | |
| foreach($student->assignmentMarkAndCoDetails as $markDetails){ | |
| $markDetails->coList = $this->getCoValuesForAnAssignment($markDetails->assignmentID,$student->studentID, $batchID, $semID, $subjectId); | |
| } | |
| } | |
| return $studentList; | |
| } | |
| /** | |
| * to get the maximum mark gien for a student in an assignment by assignmentID | |
| * | |
| * @param int $assignmentID | |
| * @return int | |
| */ | |
| public function getStudentMaxMarkGivenForAssignmentByAssignmentID($assignmentID) | |
| { | |
| $assignmentID = $this->realEscapeString($assignmentID); | |
| $sql="select MAX(marksObtained) as maxMarkObtained from assignment_marks where assignmentID='$assignmentID';"; | |
| try | |
| { | |
| $maxMark= $this->executeQueryForObject($sql)->maxMarkObtained; | |
| } | |
| catch(\Exception $e) | |
| { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $maxMark; | |
| } | |
| /** | |
| * To get the maximum mark given for a student in a pseudo assignment | |
| * | |
| * @param int $psID | |
| * @param int $assiNuP | |
| * @return int | |
| */ | |
| public function getStudentMaxMarkGivenForPsassignment($psID,$assiNuP) | |
| { | |
| $psID = $this->realEscapeString($psID); | |
| $assiNu = $this->realEscapeString($assiNuP); | |
| $sql="select MAX(am.marksObtained) as mark from batch_assignment ba left join assignment_marks am on ba.assignmentID=am.assignmentID where ba.psID='$psID' and ba.assiNu='$assiNu';"; | |
| try | |
| { | |
| $maxMark= $this->executeQueryForObject($sql)->mark; | |
| } | |
| catch(\Exception $e) | |
| { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $maxMark; | |
| } | |
| /** | |
| * @param NBAQuestion $assessmentQuestion | |
| * @return Object|null | |
| * @throws ProfessionalException | |
| * @author Vishnu M (Fri, Jan 17, 2020) | |
| */ | |
| public function saveAssessmentQuestion ( NBAQuestion $assessmentQuestion ) { | |
| $assessmentQuestion = $this->realEscapeObject($assessmentQuestion); | |
| $sql = null; | |
| $assessmentQuestionId = null; | |
| try { | |
| $sql = "INSERT INTO assessment_questions (question, mark, attachments, subjectID, sbs_id, public_question, blooms_level, lin_resource_id ) VALUES ( | |
| '$assessmentQuestion->question', | |
| '$assessmentQuestion->mark', | |
| '$assessmentQuestion->attachments', | |
| '$assessmentQuestion->subjectID', | |
| '$assessmentQuestion->sbsId', | |
| '$assessmentQuestion->publicQuestion', | |
| '$assessmentQuestion->bloomsLevel', | |
| '$assessmentQuestion->linResourceId' | |
| )"; | |
| $assessmentQuestionId = $this->executeQueryForObject($sql, true); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException ($e->getCode(), $e->getMessage()); | |
| } | |
| return $assessmentQuestionId; | |
| } | |
| /** | |
| * @param $request | |
| * @return Object|null | |
| * @throws ProfessionalException | |
| * @author Vishnu M | |
| */ | |
| public function getAssessmentStructureByBatchSemSubject( $request ) { | |
| $request = $this->realEscapeObject($request); | |
| $sql = null; | |
| $assessmentStructure = null; | |
| try { | |
| $sql = "SELECT | |
| id | |
| FROM | |
| assessment_structure | |
| WHERE | |
| batchID = '$request->batchId' | |
| AND semID = '$request->semId' | |
| AND subjectID = '$request->subjectId' | |
| AND subbatchID = '$request->subbatchId' | |
| AND staffID = '$request->staffId' | |
| AND assessment_key = '$request->assessmentKey' | |
| AND assessment_type = '$request->assessmentType' "; | |
| $assessmentStructure = $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException ($e->getCode(), $e->getMessage()); | |
| } | |
| return $assessmentStructure; | |
| } | |
| /** | |
| * @param $assignmentId | |
| * @return Object|null | |
| * @throws ProfessionalException | |
| * @author Vishnu M | |
| */ | |
| public function getAssessmentByAssignmentId($assignmentId) { | |
| $assignmentId = $this->realEscapeObject($assignmentId); | |
| $sql = null; | |
| $assessment = null; | |
| try { | |
| $sql = "SELECT | |
| ass.id, | |
| ass.exam_type_id AS examTypeId, | |
| ass.header, | |
| ass.qpcode, | |
| ass.subjectID AS subjectId, | |
| ass.staffID AS staffId, | |
| ass.batchID AS batchId, | |
| ass.semID AS semId, | |
| ass.maxMark, | |
| ass.duration, | |
| ass.subbatchID AS subbatchId, | |
| ass.isPublic, | |
| ass.isFinalised, | |
| ass.isSubmitted, | |
| ass.assessment_key AS assessmentKey, | |
| ass.assessment_type AS assessmentType | |
| FROM | |
| assessment_structure ass | |
| INNER JOIN | |
| batch_assignment ba ON ass.batchID = ba.batchID | |
| AND ass.semID = ba.semID | |
| AND ass.subjectID = ba.subjectID | |
| AND ass.staffID = ba.staffID | |
| AND ass.subbatchID = ba.subbatchID | |
| AND (ass.assessment_key = ba.assiNu OR ass.assessment_key = ba.externalAssiNu) | |
| AND ass.assessment_type = 'ASSIGNMENT' | |
| WHERE | |
| ba.assignmentID = '$assignmentId' "; | |
| $assessment = $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException ($e->getCode(), $e->getMessage()); | |
| } | |
| return $assessment; | |
| } | |
| public function getAssignmentSubmittedStudentIds($assignmentId) | |
| { | |
| $submittedStudentIds = []; | |
| $submittedStudentResult = []; | |
| $submittedStudentSql = "SELECT DISTINCT studentID as studentId from assignment_marks where assignmentID='$assignmentId'"; | |
| $submittedStudentResult = $this->executeQueryForList($submittedStudentSql); | |
| if ($submittedStudentResult && count($submittedStudentResult) > 0) { | |
| foreach ($submittedStudentResult as $student) { | |
| $submittedStudentIds[] = $student->studentId; | |
| } | |
| } | |
| return $submittedStudentIds; | |
| } | |
| public function getAssignmentStudentListForQuestionWiseMarkEnrty($isCurrentSem,$semId,$batchId,$subbatchId,$assignmentId,$studentId = null) | |
| { | |
| $isCurrentSem = $this->realEscapeString($isCurrentSem); | |
| $semId = $this->realEscapeString($semId); | |
| $batchId = $this->realEscapeString($batchId); | |
| $subbatchId = $this->realEscapeString($subbatchId); | |
| $assignmentId = $this->realEscapeString($assignmentId); | |
| $sortByColumn = BatchService::getInstance()->getStudentSortByColumnOfABatch($batchId); | |
| if(empty($sortByColumn)){ | |
| $sortByColumn = 'rollNo'; | |
| } | |
| $condition = ""; | |
| if(!empty($studentId)){ | |
| $condition = " AND sa.studentID = $studentId"; | |
| } | |
| if ($subbatchId) | |
| { | |
| if($isCurrentSem) | |
| { | |
| $sql = "select sa.studentID, sa.studentName,sa.rollNo,sa.studentAccount, asm.assessment_structure_questions_id, | |
| asm.mark_obtained,sas.resourseId as resourceId, sas.isSubmited, sas.isConfirmed, sas.submissionDate, lr.path, lr.storage_object, lr.backend_type, | |
| rwasm.rubric_wise_assessment_id, | |
| rwasm.rubric_wise_assessment_criteria_and_details_id, | |
| rwasm.mark_obtained AS rubricMark,qa.question FROM studentaccount sa inner join subbatch_student ss on sa.studentID = ss.studentID inner join batches ba on ba.batchID = sa.batchID inner join semesters sem on sem.semID = ba.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 = $assignmentId AND asm.assessment_type = 'ASSIGNMENT') LEFT JOIN student_assinments sas ON sas.studentID = sa.studentID AND assinmentID =$assignmentId left join lin_resource lr ON lr.id=sas.resourseId 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 | |
| LEFT JOIN | |
| assessment_questions qa ON qa.id = asq.assessment_questions_id | |
| where sa.batchID=$batchId and ss.subbatchID = $subbatchId and joinedSem.orderNo <= sem.orderNo $condition ORDER BY sa.$sortByColumn;"; | |
| } | |
| else | |
| { | |
| $semDetails = SemesterService::getInstance()->getSemDetailsBySemId($semId); | |
| $sql= "SELECT | |
| sa.studentID, | |
| sa.studentName, | |
| rollNo, | |
| sa.regNo, | |
| asm.assessment_structure_questions_id, | |
| asm.mark_obtained, | |
| sas.resourseId AS resourceId, | |
| sas.isSubmited, | |
| sas.submissionDate, | |
| lr.path, | |
| lr.storage_object, | |
| lr.backend_type, | |
| sas.isConfirmed, | |
| rwasm.rubric_wise_assessment_id, | |
| rwasm.rubric_wise_assessment_criteria_and_details_id, | |
| rwasm.mark_obtained AS rubricMark | |
| FROM | |
| studentaccount sa | |
| INNER JOIN | |
| subbatch_student ss ON sa.studentID = ss.studentID | |
| LEFT JOIN | |
| assessment_student_marks asm ON (asm.studentID = sa.studentID | |
| AND asm.assessment_id = $assignmentId | |
| AND asm.assessment_type = 'ASSIGNMENT') | |
| LEFT JOIN | |
| student_assinments sas ON sas.studentID = sa.studentID | |
| AND assinmentID = $assignmentId | |
| LEFT JOIN | |
| lin_resource lr ON lr.id = sas.resourseId | |
| 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 | |
| subbatchID = $subbatchId | |
| AND sa.studentID IN (SELECT | |
| studentID | |
| FROM | |
| studentaccount sa | |
| INNER JOIN | |
| batches ba ON sa.batchID = ba.batchID | |
| INNER JOIN | |
| semesters sem ON sem.semID = ba.semID | |
| 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 | |
| { | |
| if($isCurrentSem) | |
| { | |
| $sql = "select sa.studentID, sa.studentName,sa.rollNo, asm.assessment_structure_questions_id, | |
| asm.mark_obtained,sas.resourseId as resourceId, sas.isSubmited, sas.submissionDate, lr.path, lr.storage_object, lr.backend_type, sas.isConfirmed, | |
| rwasm.rubric_wise_assessment_id, | |
| rwasm.rubric_wise_assessment_criteria_and_details_id, | |
| rwasm.mark_obtained AS rubricMark ,qa.question FROM studentaccount sa inner join batches ba on ba.batchID = sa.batchID inner join semesters sem on sem.semID = ba.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 = $assignmentId AND asm.assessment_type = 'ASSIGNMENT') LEFT JOIN student_assinments sas ON sas.studentID = sa.studentID AND assinmentID =$assignmentId left join lin_resource lr ON lr.id=sas.resourseId 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 | |
| LEFT JOIN | |
| assessment_questions qa ON qa.id = asq.assessment_questions_id where sa.batchID=$batchId and joinedSem.orderNo <= sem.orderNo $condition ORDER BY sa.$sortByColumn"; | |
| } | |
| else | |
| { | |
| $semDetails = SemesterService::getInstance()->getSemDetailsBySemId($semId); | |
| $sql = "select sa.studentID, sa.studentName, sa.rollNo, sa.studentAccount, sa.regNo, asm.assessment_structure_questions_id, | |
| asm.mark_obtained,sas.resourseId as resourceId, sas.isSubmited, sas.submissionDate, lr.path, lr.storage_object, lr.backend_type, sas.isConfirmed, | |
| rwasm.rubric_wise_assessment_id, | |
| rwasm.rubric_wise_assessment_criteria_and_details_id, | |
| rwasm.mark_obtained AS rubricMark,qa.question FROM studentaccount sa inner join batches ba on sa.batchID = ba.batchID inner join semesters joinedSem on sa.joiningSemId = joinedSem.semID LEFT JOIN | |
| assessment_student_marks asm ON (asm.studentID = sa.studentID and asm.assessment_id = $assignmentId AND asm.assessment_type = 'ASSIGNMENT') LEFT JOIN student_assinments sas ON sas.studentID = sa.studentID AND assinmentID =$assignmentId left join lin_resource lr ON lr.id=sas.resourseId 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 | |
| LEFT JOIN | |
| assessment_questions qa ON qa.id = asq.assessment_questions_id | |
| where ba.batchID = $batchId and joinedSem.orderNo <= $semDetails->orderNo | |
| UNION | |
| select sa.studentID, sa.studentName, sa.rollNo, sa.studentAccount, sa.regNo, asm.assessment_structure_questions_id, | |
| asm.mark_obtained,sas.resourseId as resourceId, sas.isSubmited, sas.submissionDate, lr.path, lr.storage_object, lr.backend_type, | |
| sas.isConfirmed,rwasm.rubric_wise_assessment_id, | |
| rwasm.rubric_wise_assessment_criteria_and_details_id, | |
| rwasm.mark_obtained AS rubricMark,qa.question from failed_students fs left join studentaccount sa on fs.studentID= sa.studentID LEFT JOIN | |
| assessment_student_marks asm ON (asm.studentID = sa.studentID and asm.assessment_id = $assignmentId AND asm.assessment_type = 'ASSIGNMENT') LEFT JOIN student_assinments sas ON sas.studentID = sa.studentID AND assinmentID =$assignmentId left join lin_resource lr ON lr.id=sas.resourseId inner join semesters fsem on fsem.semID = fs.failedInSemester inner join semesters joinedSem on sa.joiningSemId = joinedSem.semID 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 | |
| LEFT JOIN | |
| assessment_questions qa ON qa.id = asq.assessment_questions_id where previousBatch =$batchId and fsem.orderNo > $semDetails->orderNo and joinedSem.orderNo <= $semDetails->orderNo $condition order by $sortByColumn"; | |
| } | |
| } | |
| try{ | |
| return $this->executeQueryForList($sql, $this->mapper[AssignmentServiceMapper::GET_STUDENT_ASSESSMENT_QUESTION_AND_MARK]); | |
| }catch (\Exception $e) { | |
| throw new ProfessionalException ($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| public function assignmentMarkIsApproved($assiNu,$batchId,$semId) | |
| { | |
| $assiNu = $this->realEscapeString($assiNu); | |
| $semId = $this->realEscapeString($semId); | |
| $batchId = $this->realEscapeString($batchId); | |
| $markApproved = "select isAproved from aprove_assignment_marks where semID=$semId and batchID=$batchId and assignmentnumber=$assiNu"; | |
| try{ | |
| return $this->executeQueryForObject($markApproved)->isAproved; | |
| }catch (\Exception $e) { | |
| throw new ProfessionalException ($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| public function checkAssignmentMark($assignmentId) | |
| { | |
| $assignmentId = $this->realEscapeString($assignmentId); | |
| try { | |
| $sql_check = "SELECT DISTINCT markID FROM assignment_marks WHERE assignmentID = ".$assignmentId.""; | |
| $assessmentMarks = $this->executeQueryForList($sql_check); | |
| if(count($assessmentMarks)){ | |
| $sql_check_question_mark = "SELECT id FROM assessment_student_marks WHERE assessment_id = ".$assignmentId." AND assessment_type = 'ASSIGNMENT'"; | |
| $assessmentQuestionMarks = $this->executeQueryForList($sql_check_question_mark); | |
| if(count($assessmentQuestionMarks)==0){ | |
| $enableAssessmentQuestionMarkEntry = 0; | |
| return $enableAssessmentQuestionMarkEntry; | |
| } | |
| } | |
| return $enableAssessmentQuestionMarkEntry = 1; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException ($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| public function upsertAssignmentStudentTotalMark($assignmentDetail) | |
| { | |
| $assignmentDetail = $this->realEscapeObject($assignmentDetail); | |
| $sqlCheckMark = "SELECT t1.markID, t1.marksObtained, t1.studentID FROM assignment_marks t1 WHERE t1.studentID=$assignmentDetail->studentId AND t1.csID IS NULL AND t1.assignmentID = $assignmentDetail->assignmentId"; | |
| $markResult = $this->executeQueryForObject($sqlCheckMark); | |
| if ($markResult) { | |
| $sql = "UPDATE assignment_marks SET marksObtained=" . $assignmentDetail->studentTotalMark . ", percentage = " . $assignmentDetail->percentage . " where studentID=$assignmentDetail->studentId AND csID IS NULL and assignmentID = $assignmentDetail->assignmentId"; | |
| } else { | |
| $sql = "insert into assignment_marks (studentID, marksObtained, assignmentID, percentage,psID) values ($assignmentDetail->studentId, $assignmentDetail->studentTotalMark, $assignmentDetail->assignmentId, $assignmentDetail->percentage,$assignmentDetail->pseudoSubjectId)"; | |
| } | |
| $sqlUpdateStatus = "update student_assinments set isSubmited=1, submissionDate= UTC_TIMESTAMP() where assinmentID=$assignmentDetail->assignmentId and studentID=$assignmentDetail->studentId"; | |
| try { | |
| $this->executeQueryForObject($sqlUpdateStatus); | |
| return $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| public function upsertConfirmStudentAssignmentTotalMarks($assignmentDetails){ | |
| $assignmentDetails = $this->realEscapeArray($assignmentDetails); | |
| $values = []; | |
| $studentIDs = []; | |
| foreach( $assignmentDetails as $assignmentDetail) { | |
| $assignmentDetail->percentage = ($assignmentDetail->studentTotalMark / $assignmentDetail->totalMark) * 100; | |
| $assignmentDetail->pseudoSubjectId = $assignmentDetail->pseudoSubjectId ? $assignmentDetail->pseudoSubjectId : 0; | |
| $studentIDs[] = $assignmentDetail->studentId; | |
| $values[] = "($assignmentDetail->studentId, '$assignmentDetail->studentTotalMark', $assignmentDetail->assignmentId, $assignmentDetail->percentage, $assignmentDetail->pseudoSubjectId)"; | |
| } | |
| $sqlUpdateStatus = "UPDATE student_assinments SET isSubmited=1, submissionDate= UTC_TIMESTAMP() WHERE assinmentID = ".$assignmentDetails[0]->assignmentId." AND studentID IN (" .implode(",",$studentIDs) .")"; | |
| $sql_insert = "INSERT INTO assignment_marks (studentID, marksObtained, assignmentID, percentage, psID) VALUES " .implode(",", $values) ."ON DUPLICATE KEY UPDATE marksObtained = VALUES (marksObtained), percentage = VALUES (percentage)"; | |
| try { | |
| $this->executeQuery($sqlUpdateStatus); | |
| return $this->executeQuery($sql_insert); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| public function getStudentAssignmentDetailsByStudentId($studentId, $assignmentId) | |
| { | |
| $studentId = $this->realEscapeString($studentId); | |
| $assignmentId = $this->realEscapeString($assignmentId); | |
| $sql = "SELECT sa.studentAssinmentID, sa.resourseId, sa.docPDFPath, DATE_FORMAT(CONVERT_TZ(sa.docUploadtime,'+00:00', @@global .time_zone), '%Y-%m-%d %H:%i:%s') as docUploadtime, sa.isSubmited, sa.submissionDate, lr.path, lr.storage_object, lr.backend_type, sa.isConfirmed, sa.assignmentStatus, sa.remarks FROM student_assinments sa left join lin_resource lr ON lr.id=sa.resourseId WHERE studentID = $studentId AND assinmentID =$assignmentId"; | |
| try | |
| { | |
| return $this->executeQueryForObject($sql); | |
| } | |
| catch(\Exception $e) | |
| { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| public function addStudentAssignment(StudentAssinments $studentAssignments) | |
| { | |
| if(!$studentAssignments->studentAssinmentID) | |
| { | |
| $sql = "insert into student_assinments (assinmentID, docPDFPath, docImgPath, docSWFPath, studentID,isSubmited,resourseId, assignmentStatus, submissionDate, docUploadtime) values ('$studentAssignments->assinmentID','$studentAssignments->docPDFPath','$studentAssignments->docImgPath' ,'$studentAssignments->docSWFPath' ,'$studentAssignments->studentID',1,'$studentAssignments->resourseId', '$studentAssignments->assignmentStatus','$studentAssignments->submissionDate',utc_timestamp())"; | |
| } | |
| else | |
| { | |
| $sql = "UPDATE student_assinments SET resourseId='$studentAssignments->resourseId', submissionDate='$studentAssignments->submissionDate',docUploadtime=utc_timestamp(), isSubmited=1, assignmentStatus='$studentAssignments->assignmentStatus' WHERE studentAssinmentID=$studentAssignments->studentAssinmentID"; | |
| } | |
| try | |
| { | |
| return $this->executeQuery($sql,true); | |
| } | |
| catch(\Exception $e) | |
| { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * @param $pseudoSubjectId | |
| * @param $assiNu | |
| * @param $staffId | |
| */ | |
| public function getPseudoSubjectAssignmentDetails($pseudoSubjectId,$assiNu,$staffId, $isExternal=0) | |
| { | |
| $pseudoSubjectId = $this->realEscapeString($pseudoSubjectId); | |
| $assiNu = $this->realEscapeString($assiNu); | |
| $staffId = $this->realEscapeString($staffId); | |
| $isExternal = $this->realEscapeString($isExternal); | |
| $sql = "SELECT ps.pseudosubjectID,sbs.batchID as batchId,sbs.semID as semId,sbs.subjectID,sbs.staffID,ps.subjectName,bat.batchDesc,bat.batchName,su.subbatchID as subbatchId,ba.assignmentID,ba.assiNu, | |
| ba.question, ba.description, ba.submissionDate, ba.submissionTime, ba.batchID, ba.staffID, ba.subjectID, ba.semID, ba.assiNu, ba.docPath, ba.docName, ba.max_mark, ba.returnDate, ba.psID, ba.subbatchID, ba.restrictStudentSubmit, ba.enableConfirm, ba.asyncReportsId, ar.lin_resource_id as linResourseId, ba.is_published_student_marks as isPublishedStudentMarks | |
| FROM pseudosubjects_sbs psbs | |
| INNER JOIN sbs_relation sbs ON sbs.sbsID = psbs.sbsID | |
| INNER JOIN pseudosubjects ps ON ps.pseudosubjectID = psbs.pseudosubjectID | |
| INNER JOIN subbatches su ON su.psID = psbs.pseudosubjectID AND su.batchID = sbs.batchID AND su.semID = sbs.semID | |
| INNER JOIN batches bat ON bat.batchID = sbs.batchID | |
| LEFT JOIN batch_assignment ba ON ba.batchID = sbs.batchID AND ba.semID = sbs.semID AND ba.subjectID = sbs.subjectID AND ba.staffID = sbs.staffID AND ".($isExternal?"ba.externalAssiNu = $assiNu":"ba.assiNu = $assiNu")." LEFT JOIN async_reports ar ON ba.asyncReportsId=ar.id | |
| WHERE psbs.pseudosubjectID = '$pseudoSubjectId' AND sbs.staffID = $staffId ORDER BY ba.assignmentID DESC;"; | |
| try { | |
| return $this->executeQueryForList($sql); | |
| } | |
| catch(\Exception $e){ | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| public function createAssignmentForEachSubBatches($assignmentDetail,$subbatches) | |
| { | |
| $assignmentDetail = $this->realEscapeObject($assignmentDetail); | |
| $subbatches = $this->realEscapeObject($subbatches); | |
| $sql = "INSERT INTO `batch_assignment` | |
| (`question`, `description`, `submissionDate`, `submissionTime`, `batchID`, `staffID`, `subjectID`, `semID`, `assiNu`, `docPath`, `docName`, `max_mark`, `returnDate`, `psID`, `subbatchID`, `restrictStudentSubmit`) | |
| VALUES "; | |
| $row = ""; | |
| foreach($subbatches as $subbatch) | |
| { | |
| $row =$row?$row.',':$row; | |
| $row .= "('$assignmentDetail->question', '$assignmentDetail->description',".($assignmentDetail->submissionDate?"'".$assignmentDetail->submissionDate."'":'NULL').", '$assignmentDetail->submissionTime', '$subbatch->batchId', '$assignmentDetail->staffID', '$assignmentDetail->subjectID', '$assignmentDetail->semID', '$assignmentDetail->assiNu', '', '', '$assignmentDetail->max_mark', ".($assignmentDetail->returnDate?"'".$assignmentDetail->returnDate."'":'NULL').", '$assignmentDetail->psID', '$subbatch->subbatchId', '$assignmentDetail->restrictStudentSubmit')"; | |
| } | |
| try { | |
| if($row) | |
| { | |
| $this->executeQuery($sql.$row); | |
| } | |
| else | |
| { | |
| return false; | |
| } | |
| } catch(\Exception $e){ | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| public function getAllStudentMarksAndCoReportOfABatchAndSemesterForAssignment($batchID, $semID, $subjectId = null, $subbatchID = 0, $sortByColumn = "rollNo"){ | |
| $batchID = $this->realEscapeString($batchID); | |
| $semID = $this->realEscapeString($semID); | |
| $subjectID = $this->realEscapeString($subjectId); | |
| $subbatchID = $this->realEscapeString($subbatchID); | |
| $sortByColumn = $this->realEscapeString($sortByColumn); | |
| try{ | |
| $studentList = StudentService::getInstance()->getAllStudentsOfABatchByBatchIdSemIdAndSubbatchId($batchID, $semID,$subbatchID, $sortByColumn); | |
| }catch(\Exception $e){ | |
| $studentList = null; | |
| } | |
| if(empty($studentList)){ | |
| return null; | |
| } | |
| foreach($studentList as $student){ | |
| $studentID = $student->studentID; | |
| $studentName = $student->studentName; | |
| $rollNo = $student->rollNo; | |
| $sql_type = "SELECT t1.assessment_id as assignmentId,t2.assiNu from assessment_student_marks t1,batch_assignment t2 WHERE t1.assessment_id = t2.assignmentID | |
| AND t1.assessment_type = 'ASSIGNMENT' AND t1.studentID = \"" . $studentID . "\" AND t2.subjectID = \"" . $subjectID . "\" " . $condition . " GROUP BY assignmentId"; | |
| $assignmentIdList = $this->executeQueryForList($sql_type); | |
| $student->assignments = []; | |
| foreach ($assignmentIdList as $row_type) { | |
| $assignmentId = $row_type->assignmentId; | |
| $assignmentNo = $row_type->assiNu; | |
| // $student->assignments[$assignmentId] = new \StdClass(); | |
| $student->assignments[$assignmentId]->coList = []; | |
| $studentMarkList = NbaCoService::getInstance()->getCOReportOfAssignment($assignmentId, $studentID, $subjectID); | |
| $validStudentQuestionIds = NbaCoService::getInstance()->getSectionWiseValidQuestionIdsForCoCalculationOfAStudentForAssignment($assignmentId, $studentID); | |
| $student->assignments[$assignmentId]->marksObtained = $this->getAssignmentMarksOfAStudent($studentID,$assignmentId); | |
| // $result_mark = sql_query($sql_mark, $connect); | |
| if (!empty($studentMarkList)) { | |
| //Assumption that all marks have same question paper id | |
| $questionPaperId = $studentMarkList[0]->id; | |
| $totalCoPercentList = NbaCoService::getInstance()->calculateTotalCOPercents($validStudentQuestionIds->nbaCourseOutcomeQuestionsIdList, $questionPaperId); | |
| foreach ($studentMarkList as $row_mark) { | |
| $row_mark = (array)$row_mark; | |
| if (!in_array($row_mark['assessment_structure_questions_id'], $validStudentQuestionIds->nbaCourseOutcomeQuestionPaperQuestionsIdList) || !in_array($row_mark['assessment_questions_id'], $validStudentQuestionIds->nbaCourseOutcomeQuestionsIdList)) { | |
| continue; | |
| } | |
| $assessment_structure_questions_id = $row_mark['assessment_structure_questions_id']; | |
| $mark_obtained = $row_mark['mark_obtained']; | |
| $maxMark = $row_mark['mark']; | |
| $assessment_questions_id = $row_mark['assessment_questions_id']; | |
| $nba_course_outcome_id = $row_mark['nba_course_outcome_id']; | |
| $course_outcome_value = $row_mark['course_outcome_value']; | |
| $course_outcome_question_paper_id = $row_mark['id']; | |
| $course_outcome_value = $course_outcome_value / 100; | |
| //1.Take distinct sections | |
| //2.Calculate Individual Section co total | |
| $per_percent = $totalCoPercentList[$nba_course_outcome_id] / 100; | |
| $percentage = ($mark_obtained / $maxMark) * ($course_outcome_value); | |
| $exactValue = ($percentage / $per_percent) * 100; | |
| $student->assignments[$assignmentId]->coList[$nba_course_outcome_id] += $exactValue; | |
| } | |
| } | |
| foreach($student->assignments[$assignmentId]->coList as $key =>$co){ | |
| $student->assignments[$assignmentId]->coList[$key] = round($co,2); | |
| } | |
| } | |
| unset($values); | |
| unset($totalValue); | |
| } | |
| return $studentList; | |
| } | |
| public function getAssignmentMarksOfAStudent($studentId,$assignmentId) | |
| { | |
| $studentId = $this->realEscapeString($studentId); | |
| $assignmentId = $this->realEscapeString($assignmentId); | |
| $sql = "select marksObtained from assignment_marks WHERE assignmentID =$assignmentId AND studentID = $studentId "; | |
| try { | |
| return $this->executeQueryForObject($sql)->marksObtained; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| public function getAssignmentList($batchId,$semId,$subjectId,$subBatchId = 0) | |
| { | |
| $batchId = $this->realEscapeString($batchId); | |
| $subjectId = $this->realEscapeString($subjectId); | |
| $semId = $this->realEscapeString($semId); | |
| $subBatchId = $this->realEscapeString($subBatchId); | |
| $cond = ""; | |
| $cond .= $subBatchId?" AND subbatchID = ".$subBatchId."": ""; | |
| $sql = "SELECT assignmentID as id, max_mark as maxMark,assiNu, submissionDate, description, returnDate, question, docPath, docName from batch_assignment WHERE batchID =$batchId AND semID = $semId AND subjectID = $subjectId ".$cond; | |
| try { | |
| return $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * get assignment details by subject id | |
| * @param int $batchId | |
| * @param int $subjectId | |
| * @param int $staffId | |
| * @throws ProfessionalException | |
| * @return object|array|\com\linways\base\util\$objectList[] | |
| */ | |
| public function getExternalAssignmentDetailsBySubjectId($batchId, $subjectId, $staffId, $semId = null, $subbatchID = null, $userType=null) | |
| { | |
| $batchId = $this->realEscapeString($batchId); | |
| $subjectId = $this->realEscapeString($subjectId); | |
| $staffId = $this->realEscapeString($staffId); | |
| $conditions = ""; | |
| if ( $semId ) { | |
| $semId = $this->realEscapeString($semId); | |
| $conditions .= " AND bs.semID = ".$semId." "; | |
| } | |
| if($subbatchID){ | |
| $subbatchID = $this->realEscapeString($subbatchID); | |
| $conditions .= " AND bs.subbatchID = ".$subbatchID." "; | |
| } | |
| if($userType) | |
| { | |
| $userType = $this->realEscapeString($userType); | |
| $conditions .= " AND bs.userType = '".$userType."' "; | |
| } | |
| $assignmentList=[]; | |
| $sql = "SELECT bs.assignmentID, bs.question, bs.description, bs.submissionDate, bs.submissionTime, bs.assiNu,bs.docName, bs.docPath, bs.max_mark, bs.returnDate, bs.subbatchID, if(bs.subbatchID=0, 'All', sub.subbatchName) AS subbatchName, restrictStudentSubmit, bs.is_published as isPublished, bs.publishDate, bs.publishTime, bs.userType, bs.externalAssiNu, bs.enableConfirm FROM batch_assignment bs LEFT JOIN subbatches sub ON bs.subbatchID=sub.subbatchID WHERE bs.batchID=$batchId AND bs.subjectID=$subjectId AND bs.staffID=$staffId ".$conditions." AND (bs.assiNu=0 OR bs.assiNu is null) ORDER BY bs.assignmentID DESC"; | |
| try{ | |
| $assignmentList = $this->executeQueryForList($sql,$this->mapper[AssignmentServiceMapper::GET_ASSIGNMENT_DETAILS]); | |
| }catch (\Exception $e){ | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $assignmentList; | |
| } | |
| /** | |
| * get student assignment list | |
| * @param int $studentId | |
| * @param int $batchId | |
| * @throws ProfessionalException | |
| * @return object|array|\com\linways\base\util\$objectList[] | |
| */ | |
| public function getAssignmentListByStudent($studentId, $batchId, $staffId=null, $subjectId=null, $checkPublish = null) | |
| { | |
| $studentId= $this->realEscapeString($studentId); | |
| $batchId= $this->realEscapeString($batchId); | |
| $staffId= $this->realEscapeString($staffId); | |
| $subjectId= $this->realEscapeString($subjectId); | |
| $condition=""; | |
| if($staffId) | |
| { | |
| $condition .=" AND (ba.staffID=$staffId OR ba.userType='ADMIN')"; | |
| } | |
| if($subjectId) | |
| { | |
| $condition .=" AND ba.subjectID=$subjectID"; | |
| } | |
| $checkCondition = ""; | |
| if($checkPublish){ | |
| $checkCondition .=" AND (((!ba.publishDate AND !ba.publishTime) OR ba.publishDate = '00-00-0000') OR (ba.publishDate AND ba.publishTime AND ba.publishDate <> '00-00-0000' AND concat(ba.publishDate,' ',ba.publishTime) <= '".date('Y-m-d h:i A')."') | |
| ) "; | |
| } | |
| $sql = "SELECT ba.assignmentID, ba.question, sub.subjectName, ba.submissionDate, ba.submissionTime, sub.subjectDesc,ba.docPath,ba.docName ,ba.assiNu, ba.externalAssiNu, ba.publishDate, ba.publishTime, sa.isSubmited, sa.submissionDate as submittedDate, sa.isConfirmed,sa.assignmentStatus FROM batch_assignment ba INNER JOIN subjects sub ON sub.subjectID=ba.subjectID INNER JOIN batches bt ON bt.batchID=ba.batchID AND bt.semID=ba.semID LEFT JOIN student_assinments sa ON sa.studentID=$studentId AND sa.assinmentID=ba.assignmentID LEFT JOIN subbatches subb ON subb.subbatchID=ba.subbatchID LEFT JOIN subbatch_student ss ON ss.subbatchID=subb.subbatchID AND ss.studentID=$studentId WHERE bt.batchID=$batchId AND ba.is_published =1 AND (ss.subbatchID is not null OR subb.subbatchID is null) $checkCondition order by ba.publishDate desc, str_to_date(ba.publishTime, '%l:%i %p') desc, ba.submissionDate asc"; | |
| try { | |
| return $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); | |
| } | |
| } | |
| public function confirmStudentAssignment($studentId, $assignmentId) | |
| { | |
| $studentId= $this->realEscapeString($studentId); | |
| $assignmentId= $this->realEscapeString($assignmentId); | |
| $sql="update student_assinments set isConfirmed=1, assignmentStatus='CONFIRMED' where studentID='$studentId' and assinmentID='$assignmentId'"; | |
| try { | |
| return $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| public function unconfirmStudentAssignment($studentId, $assignmentId) | |
| { | |
| $studentId= $this->realEscapeString($studentId); | |
| $assignmentId= $this->realEscapeString($assignmentId); | |
| $sql="update student_assinments set isConfirmed=0, assignmentStatus='RECONFIRM_PENDING' where studentID='$studentId' and assinmentID='$assignmentId'"; | |
| try { | |
| return $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| public function updateStudentAssignmentRemarks($studentId, $assignmentId, $remarks) | |
| { | |
| $studentId= $this->realEscapeString($studentId); | |
| $assignmentId= $this->realEscapeString($assignmentId); | |
| $remarks = $this->realEscapeString($remarks); | |
| $sql="update student_assinments set remarks='$remarks' where studentID='$studentId' and assinmentID='$assignmentId'"; | |
| try { | |
| return $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| public function getAllAssignmentDetailsBySbsId($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| try{ | |
| //SUBMITED const given in 'student_assinments' for submitted students | |
| $detailed_select = "select std.studentID,std.studentName,std.batchID,std.regNo,std.rollNo,concat('[',group_concat(distinct JSON_OBJECT('assignmentId',ba.assignmentID,'assignmentNo',ba.assiNu,'maxMark',ba.max_mark,'marksObtained',am.marksObtained,'status',sa.assignmentStatus)),']') as studentMarks"; | |
| $minimal_select = "select ba.assignmentID,ba.assiNu,count(am.studentID) as submitted, count(std.studentID) as totalStudents,ba.max_mark,(sum(am.marksObtained)/(count(am.studentID)*ba.max_mark))*100 as averageMarks,ba.question,ba.description, concat(ba.submissionDate,' ',ba.submissionTime) as submissionDate,sum(if(sa.assignmentStatus = 'PENDING',1,0)) as pendingCount,sum(if(sa.assignmentStatus = 'SUBMITED',1,0)) as submittedCount"; | |
| $condition = $request->assiNu?" and ba.assiNu = $request->assiNu ":""; | |
| $condition .= $request->semId?" and ba.semId = $request->semId ":""; | |
| $condition .= $request->subjectId?" and ba.subjectID = $request->subjectId ":""; | |
| switch ($request->subjectType) { | |
| case 'pseudoSubjects': | |
| $condition .= $request->staffId?" and ba.staffID = '$request->staffId' ":""; | |
| $join = " ,pstd.pseudosubjectID as pseudosubjectId,null as sbsId from batch_assignment ba | |
| inner join studentaccount std on std.batchID = ba.batchID | |
| inner join pseudosubjects_students pstd on pstd.studentID = std.studentID and pstd.pseudosubjectID = ba.psID | |
| inner join subbatch_student sstd on sstd.subbatchID = ba.subbatchID and pstd.studentID = sstd.studentID | |
| left join assignment_marks am on am.studentID = pstd.studentID and am.assignmentID = ba.assignmentID | |
| left join student_assinments sa on sa.studentID = pstd.studentID and sa.assinmentID = ba.assignmentID and ba.batchID = sa.batchID | |
| where ba.psID = ".$request->pseudoSubjectId . $condition; | |
| $sql = $minimal_select.$join." group by ba.assiNu;"; | |
| $sql_detailed = $detailed_select.$join." group by std.studentID;"; | |
| break; | |
| case 'subBatch': | |
| $condition .= $request->batchId?" and ba.batchId = $request->batchId ":""; | |
| $condition .= $request->sbsId?" and sbs.sbsID = $request->sbsId ":""; | |
| $condition .= $request->subjectId?" and ba.subjectID = $request->subjectId ":""; | |
| $condition .= is_array($request->subBatchIds) && !empty($request->subBatchIds)?" and ba.subbatchID in (".implode(',',$request->subBatchIds).") ":""; | |
| $condition .= !is_array($request->subBatchIds) && !empty($request->subBatchIds)?" and ba.subbatchID in (".$request->subBatchIds.") ":""; | |
| $join = " ,null as pseudosubjectId,sbs.sbsID as sbsId from sbs_relation sbs | |
| inner join subbatch_sbs ssbs on ssbs.sbsID = sbs.sbsID | |
| inner join subbatches sub on sub.batchID = sbs.batchID and sub.semID = sbs.semID | |
| inner join batch_assignment ba on ba.batchID=sbs.batchID and ba.staffID = sbs.staffID and ba.subjectID = sbs.subjectID and ba.semID = sbs.semID and ba.subbatchID in (sub.subbatchID,0) | |
| inner join studentaccount std on std.batchID = sbs.batchID | |
| inner join subbatch_student sstd on sstd.studentID = std.studentID and sstd.subbatchID = sub.subbatchID | |
| left join assignment_marks am on am.assignmentID = ba.assignmentID and am.studentID = std.studentID | |
| left join student_assinments sa on sa.studentID = std.studentID and sa.assinmentID = ba.assignmentID and ba.batchID = sa.batchID | |
| where 1 = 1 ". $condition; | |
| $sql = $minimal_select.$join." group by ba.assiNu,ba.assignmentID;"; | |
| $sql_detailed = $detailed_select.$join." group by std.studentID;"; | |
| break; | |
| case 'allBatch': | |
| $condition .= $request->sbsId?" and sbs.sbsID = $request->sbsId ":""; | |
| $condition .= $request->subjectId?" and ba.subjectID = $request->subjectId ":""; | |
| $join = " ,null as pseudosubjectId,sbs.sbsID as sbsId from sbs_relation sbs | |
| inner join batch_assignment ba on ba.batchID=sbs.batchID and ba.staffID = sbs.staffID and ba.subjectID = sbs.subjectID and ba.semID = sbs.semID and ba.subbatchID = 0 | |
| inner join studentaccount std on std.batchID = sbs.batchID | |
| left join assignment_marks am on am.assignmentID = ba.assignmentID and am.studentID = std.studentID | |
| left join student_assinments sa on sa.studentID = std.studentID and sa.assinmentID = ba.assignmentID and ba.batchID = sa.batchID | |
| where 1=1 ". $condition; | |
| $sql = $minimal_select.$join." group by ba.assiNu,ba.assignmentID;"; | |
| $sql_detailed = $detailed_select.$join." group by std.studentID;"; | |
| break; | |
| } | |
| $assignments = new \stdClass(); | |
| $assignments->basicDetails = $request->overallData?$this->executeQueryForList($sql):null; | |
| if($request->detailedData){ | |
| $sqlSession = "SET SESSION group_concat_max_len = 1000000;"; | |
| $this->executeQuery($sqlSession); | |
| $assignments->assignmentDetails = $this->executeQueryForList($sql_detailed); | |
| }else{ | |
| $assignments->assignmentDetails = null; | |
| } | |
| } | |
| catch(\Exception $e){ | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $assignments; | |
| } | |
| public function getAssignmentStudentsForMarkEntry($request) { | |
| try { | |
| if($request->batchId) | |
| { | |
| $isCurrentSem = SemesterService::getInstance()->isCurrentSemester($request->batchId, $request->semId); | |
| $sortByColumn = BatchService::getInstance()->getStudentSortByColumnOfABatch($request->batchId); | |
| } | |
| $sortByColumn = $sortByColumn?$sortByColumn:'rollNo'; | |
| $insert_sql=''; | |
| if($request->psId) | |
| { | |
| $sql = "select sa.studentID, sa.regNo,sa.admissionNo, sa.studentName,sa.rollNo,sa.studentAccount, sas.studentAssinmentID, sas.resourseId, sas.docPDFPath, if(docUploadtime,DATE_FORMAT(CONVERT_TZ(sas.docUploadtime,'+00:00', @@global .time_zone), '%Y-%m-%d %H:%i:%s'),'') as docUploadtime, sas.isSubmited, sas.submissionDate, lr.path, lr.storage_object, lr.backend_type, sas.isConfirmed, sas.assignmentStatus, sas.remarks, sm.markID, sm.marksObtained, sm.percentage, bas.assignmentID, ba.batchName from studentaccount sa inner join subbatch_student ss on sa.studentID = ss.studentID inner join subbatches sb ON sb.subbatchID=ss.subbatchID inner join batches ba on ba.batchID = sa.batchID inner join semesters sem on sem.semID = ba.semID inner join semesters joinedSem on sa.joiningSemId = joinedSem.semID inner join batch_assignment bas ON bas.psID=sb.psID and ba.batchID=bas.batchID left join student_assinments sas ON sas.studentID=sa.studentID AND sas.assinmentID=bas.assignmentID left join assignment_marks sm ON sm.studentID=sa.studentID AND sm.assignmentID=bas.assignmentID AND sm.csID IS NULL left join lin_resource lr ON lr.id=sas.resourseId where sb.psID = $request->psId and ".($request->isExternal?"bas.externalAssiNu = $request->assiNu":"bas.assiNu = $request->assiNu")." and joinedSem.orderNo <= sem.orderNo GROUP BY sa.studentID ORDER BY sa.$sortByColumn"; | |
| } | |
| else | |
| { | |
| if($request->subbatchId) | |
| { | |
| if($isCurrentSem) | |
| { | |
| $sql = "select sa.studentID, sa.regNo,sa.admissionNo, sa.studentName,sa.rollNo,sa.studentAccount, sas.studentAssinmentID, sas.resourseId, sas.docPDFPath, if(docUploadtime,DATE_FORMAT(CONVERT_TZ(sas.docUploadtime,'+00:00', @@global .time_zone), '%Y-%m-%d %H:%i:%s'),'') as docUploadtime, sas.isSubmited, sas.submissionDate, lr.path, lr.storage_object, lr.backend_type, sas.isConfirmed, sas.assignmentStatus, sas.remarks, sm.markID, sm.marksObtained, sm.percentage, $request->assignmentId as assignmentID from studentaccount sa inner join subbatch_student ss on sa.studentID = ss.studentID inner join batches ba on ba.batchID = sa.batchID inner join semesters sem on sem.semID = ba.semID inner join semesters joinedSem on sa.joiningSemId = joinedSem.semID left join student_assinments sas ON sas.studentID=sa.studentID AND sas.assinmentID=$request->assignmentId left join assignment_marks sm ON sm.studentID=sa.studentID AND sm.assignmentID=sas.assinmentID AND sm.csID IS NULL left join lin_resource lr ON lr.id=sas.resourseId where sa.batchID=$request->batchId and ss.subbatchID = $request->subbatchId and joinedSem.orderNo <= sem.orderNo ORDER BY sa.$sortByColumn"; | |
| $insert_sql="insert ignore into student_assinments (assinmentID,studentID,batchID, assignmentStatus) SELECT $request->assignmentId, sa.studentID, sa.batchID, 'PENDING' FROM studentaccount sa inner join subbatch_student ss on sa.studentID = ss.studentID inner join batches ba on ba.batchID = sa.batchID inner join semesters sem on sem.semID = ba.semID inner join semesters joinedSem on sa.joiningSemId = joinedSem.semID WHERE sa.batchID=$request->batchId and ss.subbatchID = $request->subbatchId and joinedSem.orderNo <= sem.orderNo"; | |
| } | |
| else | |
| { | |
| $semDetails = SemesterService::getInstance()->getSemDetailsBySemId($request->semId); | |
| $sql = "select sa.studentID,sa.studentName, sa.rollNo, sa.regNo,sa.admissionNo, sas.studentAssinmentID, sas.resourseId, sas.docPDFPath, if(docUploadtime,DATE_FORMAT(CONVERT_TZ(sas.docUploadtime,'+00:00', @@global .time_zone), '%Y-%m-%d %H:%i:%s'),'') as docUploadtime, sas.isSubmited, sas.submissionDate, lr.path, lr.storage_object, lr.backend_type, sas.isConfirmed, sas.assignmentStatus, sas.remarks, sm.markID, sm.marksObtained, sm.percentage, $request->assignmentId as assignmentID from studentaccount sa inner join subbatch_student ss on sa.studentID = ss.studentID left join student_assinments sas ON sas.studentID=sa.studentID AND sas.assinmentID=$request->assignmentId left join assignment_marks sm ON sm.studentID=sa.studentID AND sm.assignmentID=sas.assinmentID AND sm.csID IS NULL left join lin_resource lr ON lr.id=sas.resourseId where subbatchID = $request->subbatchId and sa.studentID in(select studentID from studentaccount sa inner join batches ba on sa.batchID = ba.batchID inner join semesters sem on sem.semID = ba.semID inner join semesters joinedSem on sa.joiningSemId = joinedSem.semID where ba.batchID = $request->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 = $request->batchId and fsem.orderNo > $semDetails->orderNo and joinedSem.orderNo <= $semDetails->orderNo) ORDER BY $sortByColumn"; | |
| } | |
| } | |
| else | |
| { | |
| if($isCurrentSem) | |
| { | |
| $sql = "select sa.studentID, sa.regNo,sa.admissionNo, sa.studentName,sa.rollNo, sas.studentAssinmentID, sas.resourseId, sas.docPDFPath, if(docUploadtime,DATE_FORMAT(CONVERT_TZ(sas.docUploadtime,'+00:00', @@global .time_zone), '%Y-%m-%d %H:%i:%s'),'') as docUploadtime, sas.isSubmited, sas.submissionDate, lr.path, lr.storage_object, lr.backend_type, sas.isConfirmed, sas.assignmentStatus, sas.remarks, sm.markID, sm.marksObtained, sm.percentage, $request->assignmentId as assignmentID from studentaccount sa inner join batches ba on ba.batchID = sa.batchID inner join semesters sem on sem.semID = ba.semID inner join semesters joinedSem on sa.joiningSemId = joinedSem.semID left join student_assinments sas ON sas.studentID=sa.studentID AND sas.assinmentID=$request->assignmentId left join assignment_marks sm ON sm.studentID=sa.studentID AND sm.assignmentID=sas.assinmentID AND sm.csID IS NULL left join lin_resource lr ON lr.id=sas.resourseId where sa.batchID=$request->batchId and joinedSem.orderNo <= sem.orderNo ORDER BY sa.$sortByColumn"; | |
| $insert_sql="insert ignore into student_assinments (assinmentID,studentID,batchID, assignmentStatus) SELECT $request->assignmentId, sa.studentID, sa.batchID, 'PENDING' FROM studentaccount sa inner join batches ba on ba.batchID = sa.batchID inner join semesters sem on sem.semID = ba.semID inner join semesters joinedSem on sa.joiningSemId = joinedSem.semID WHERE sa.batchID=$request->batchId and joinedSem.orderNo <= sem.orderNo"; | |
| } | |
| else | |
| { | |
| // $sql = "select sa.studentID, sa.regNo,sa.admissionNo, sa.studentName, sa.rollNo, sa.studentAccount from studentaccount sa inner join batches ba on sa.batchID = ba.batchID where ba.batchID = $batchID union select sa.studentID, sa.regNo,sa.admissionNo, sa.studentName, sa.rollNo, sa.studentAccount from failed_students fs left join studentaccount sa on fs.studentID= sa.studentID where previousBatch = $batchID and failedInSemester > $semID order by rollNo"; | |
| $semDetails = SemesterService::getInstance()->getSemDetailsBySemId($request->semId); | |
| $sql = "select sa.studentID, sa.studentName, sa.rollNo, sa.studentAccount, sa.regNo,sa.admissionNo, | |
| sas.studentAssinmentID, sas.resourseId, sas.docPDFPath, | |
| if(docUploadtime,DATE_FORMAT(CONVERT_TZ(sas.docUploadtime,'+00:00', @@global .time_zone), '%Y-%m-%d %H:%i:%s'),'') as docUploadtime, | |
| sas.isSubmited, sas.submissionDate, lr.path, lr.storage_object, lr.backend_type, sas.isConfirmed, sas.assignmentStatus, sas.remarks, | |
| sm.markID, sm.marksObtained, sm.percentage, $request->assignmentId as assignmentID from studentaccount sa | |
| inner join batches ba on sa.batchID = ba.batchID inner join semesters joinedSem on sa.joiningSemId = joinedSem.semID | |
| left join student_assinments sas ON sas.studentID=sa.studentID AND sas.assinmentID=$request->assignmentId | |
| left join assignment_marks sm ON sm.studentID=sa.studentID AND sm.assignmentID=sas.assinmentID AND sm.csID IS NULL | |
| left join lin_resource lr ON lr.id=sas.resourseId where ba.batchID = $request->batchId and joinedSem.orderNo <= $semDetails->orderNo | |
| union select sa.studentID, sa.studentName, sa.rollNo, sa.studentAccount, sa.regNo, sa.admissionNo, sas.studentAssinmentID, sas.resourseId, sas.docPDFPath, if(docUploadtime,DATE_FORMAT(CONVERT_TZ(sas.docUploadtime,'+00:00', @@global .time_zone), '%Y-%m-%d %H:%i:%s'),'') as docUploadtime, sas.isSubmited, sas.submissionDate, lr.path, lr.storage_object, lr.backend_type, sas.isConfirmed, sas.assignmentStatus, sas.remarks, sm.markID, sm.marksObtained, sm.percentage, $request->assignmentId as assignmentID from failed_students fs left join studentaccount sa on fs.studentID= sa.studentID inner join semesters fsem on fsem.semID = fs.failedInSemester inner join semesters joinedSem on sa.joiningSemId = joinedSem.semID left join student_assinments sas ON sas.studentID=sa.studentID AND sas.assinmentID=$request->assignmentId left join assignment_marks sm ON sm.studentID=sa.studentID AND sm.assignmentID=sas.assinmentID AND sm.csID IS NULL left join lin_resource lr ON lr.id=sas.resourseId where previousBatch =$request->batchId and fsem.orderNo > $semDetails->orderNo and joinedSem.orderNo <= $semDetails->orderNo order by $sortByColumn"; | |
| } | |
| } | |
| } | |
| if($insert_sql) | |
| { | |
| $this->executeQuery($insert_sql); | |
| } | |
| return $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| public function getAssignmentStudentsForMarkEntryWithCount($request) { | |
| try { | |
| if($request->batchId) | |
| { | |
| $isCurrentSem = SemesterService::getInstance()->isCurrentSemester($request->batchId, $request->semId); | |
| $sortByColumn = BatchService::getInstance()->getStudentSortByColumnOfABatch($request->batchId); | |
| } | |
| $sortByColumn = $sortByColumn?$sortByColumn:'rollNo'; | |
| $insert_sql=''; | |
| if($request->psId) | |
| { | |
| $sql = "select sa.studentID, sa.studentName,sa.rollNo,sa.studentAccount, sas.studentAssinmentID, sas.resourseId, sas.docPDFPath, if(docUploadtime,DATE_FORMAT(CONVERT_TZ(sas.docUploadtime,'+00:00', @@global .time_zone), '%Y-%m-%d %H:%i:%s'),'') as docUploadtime, sas.isSubmited, sas.submissionDate, lr.path, lr.storage_object, lr.backend_type, sas.isConfirmed, sas.assignmentStatus, sas.remarks, sm.markID, sm.marksObtained, sm.percentage, bas.assignmentID, ba.batchName from studentaccount sa inner join subbatch_student ss on sa.studentID = ss.studentID inner join subbatches sb ON sb.subbatchID=ss.subbatchID inner join batches ba on ba.batchID = sa.batchID inner join semesters sem on sem.semID = ba.semID inner join semesters joinedSem on sa.joiningSemId = joinedSem.semID inner join batch_assignment bas ON bas.psID=sb.psID and ba.batchID=bas.batchID left join student_assinments sas ON sas.studentID=sa.studentID AND sas.assinmentID=bas.assignmentID left join assignment_marks sm ON sm.studentID=sa.studentID AND sm.assignmentID=bas.assignmentID AND sm.csID IS NULL left join lin_resource lr ON lr.id=sas.resourseId where sb.psID = $request->psId and ".($request->isExternal?"bas.externalAssiNu = $request->assiNu":"bas.assiNu = $request->assiNu")." and joinedSem.orderNo <= sem.orderNo GROUP BY sa.studentID ORDER BY sa.$sortByColumn"; | |
| } | |
| else | |
| { | |
| if($request->subbatchId) | |
| { | |
| if($isCurrentSem) | |
| { | |
| $sql = "select sa.studentID, sa.studentName,sa.rollNo,sa.studentAccount, sas.studentAssinmentID, sas.resourseId, sas.docPDFPath, if(docUploadtime,DATE_FORMAT(CONVERT_TZ(sas.docUploadtime,'+00:00', @@global .time_zone), '%Y-%m-%d %H:%i:%s'),'') as docUploadtime, sas.isSubmited, sas.submissionDate, lr.path, lr.storage_object, lr.backend_type, sas.isConfirmed, sas.assignmentStatus, sas.remarks, sm.markID, sm.marksObtained, sm.percentage, $request->assignmentId as assignmentID from studentaccount sa inner join subbatch_student ss on sa.studentID = ss.studentID inner join batches ba on ba.batchID = sa.batchID inner join semesters sem on sem.semID = ba.semID inner join semesters joinedSem on sa.joiningSemId = joinedSem.semID left join student_assinments sas ON sas.studentID=sa.studentID AND sas.assinmentID=$request->assignmentId left join assignment_marks sm ON sm.studentID=sa.studentID AND sm.assignmentID=sas.assinmentID AND sm.csID IS NULL left join lin_resource lr ON lr.id=sas.resourseId where sa.batchID=$request->batchId and ss.subbatchID = $request->subbatchId and joinedSem.orderNo <= sem.orderNo ORDER BY sa.$sortByColumn"; | |
| $insert_sql="insert ignore into student_assinments (assinmentID,studentID,batchID, assignmentStatus) SELECT $request->assignmentId, sa.studentID, sa.batchID, 'PENDING' FROM studentaccount sa inner join subbatch_student ss on sa.studentID = ss.studentID inner join batches ba on ba.batchID = sa.batchID inner join semesters sem on sem.semID = ba.semID inner join semesters joinedSem on sa.joiningSemId = joinedSem.semID WHERE sa.batchID=$request->batchId and ss.subbatchID = $request->subbatchId and joinedSem.orderNo <= sem.orderNo"; | |
| } | |
| else | |
| { | |
| $semDetails = SemesterService::getInstance()->getSemDetailsBySemId($request->semId); | |
| $sql = "select sa.studentID, sa.studentName, rollNo, sa.regNo, sas.studentAssinmentID, sas.resourseId, sas.docPDFPath, if(docUploadtime,DATE_FORMAT(CONVERT_TZ(sas.docUploadtime,'+00:00', @@global .time_zone), '%Y-%m-%d %H:%i:%s'),'') as docUploadtime, sas.isSubmited, sas.submissionDate, lr.path, lr.storage_object, lr.backend_type, sas.isConfirmed, sas.assignmentStatus, sas.remarks, sm.markID, sm.marksObtained, sm.percentage, $request->assignmentId as assignmentID from studentaccount sa inner join subbatch_student ss on sa.studentID = ss.studentID left join student_assinments sas ON sas.studentID=sa.studentID AND sas.assinmentID=$request->assignmentId left join assignment_marks sm ON sm.studentID=sa.studentID AND sm.assignmentID=sas.assinmentID AND sm.csID IS NULL left join lin_resource lr ON lr.id=sas.resourseId where subbatchID = $request->subbatchId and sa.studentID in(select studentID from studentaccount sa inner join batches ba on sa.batchID = ba.batchID inner join semesters sem on sem.semID = ba.semID inner join semesters joinedSem on sa.joiningSemId = joinedSem.semID where ba.batchID = $request->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 = $request->batchId and fsem.orderNo > $semDetails->orderNo and joinedSem.orderNo <= $semDetails->orderNo) ORDER BY $sortByColumn"; | |
| } | |
| } | |
| else | |
| { | |
| if($isCurrentSem) | |
| { | |
| $sql = "select sa.studentID, sa.studentName,sa.rollNo, sas.studentAssinmentID, sas.resourseId, sas.docPDFPath, if(docUploadtime,DATE_FORMAT(CONVERT_TZ(sas.docUploadtime,'+00:00', @@global .time_zone), '%Y-%m-%d %H:%i:%s'),'') as docUploadtime, sas.isSubmited, sas.submissionDate, lr.path, lr.storage_object, lr.backend_type, sas.isConfirmed, sas.assignmentStatus, sas.remarks, sm.markID, sm.marksObtained, sm.percentage, $request->assignmentId as assignmentID from studentaccount sa inner join batches ba on ba.batchID = sa.batchID inner join semesters sem on sem.semID = ba.semID inner join semesters joinedSem on sa.joiningSemId = joinedSem.semID left join student_assinments sas ON sas.studentID=sa.studentID AND sas.assinmentID=$request->assignmentId left join assignment_marks sm ON sm.studentID=sa.studentID AND sm.assignmentID=sas.assinmentID AND sm.csID IS NULL left join lin_resource lr ON lr.id=sas.resourseId where sa.batchID=$request->batchId and joinedSem.orderNo <= sem.orderNo ORDER BY sa.$sortByColumn"; | |
| $insert_sql="insert ignore into student_assinments (assinmentID,studentID,batchID, assignmentStatus) SELECT $request->assignmentId, sa.studentID, sa.batchID, 'PENDING' FROM studentaccount sa inner join batches ba on ba.batchID = sa.batchID inner join semesters sem on sem.semID = ba.semID inner join semesters joinedSem on sa.joiningSemId = joinedSem.semID WHERE sa.batchID=$request->batchId and joinedSem.orderNo <= sem.orderNo"; | |
| } | |
| else | |
| { | |
| // $sql = "select sa.studentID, sa.studentName, sa.rollNo, sa.studentAccount from studentaccount sa inner join batches ba on sa.batchID = ba.batchID where ba.batchID = $batchID union select sa.studentID, sa.studentName, sa.rollNo, sa.studentAccount from failed_students fs left join studentaccount sa on fs.studentID= sa.studentID where previousBatch = $batchID and failedInSemester > $semID order by rollNo"; | |
| $semDetails = SemesterService::getInstance()->getSemDetailsBySemId($request->semId); | |
| $sql = "select sa.studentID, sa.studentName, sa.rollNo, sa.studentAccount, sa.regNo, sas.studentAssinmentID, sas.resourseId, sas.docPDFPath, if(docUploadtime,DATE_FORMAT(CONVERT_TZ(sas.docUploadtime,'+00:00', @@global .time_zone), '%Y-%m-%d %H:%i:%s'),'') as docUploadtime, sas.isSubmited, sas.submissionDate, lr.path, lr.storage_object, lr.backend_type, sas.isConfirmed, sas.assignmentStatus, sas.remarks, sm.markID, sm.marksObtained, sm.percentage, $request->assignmentId as assignmentID from studentaccount sa inner join batches ba on sa.batchID = ba.batchID inner join semesters joinedSem on sa.joiningSemId = joinedSem.semID left join student_assinments sas ON sas.studentID=sa.studentID AND sas.assinmentID=$request->assignmentId left join assignment_marks sm ON sm.studentID=sa.studentID AND sm.assignmentID=sas.assinmentID AND sm.csID IS NULL left join lin_resource lr ON lr.id=sas.resourseId where ba.batchID = $request->batchId and joinedSem.orderNo <= $semDetails->orderNo union select sa.studentID, sa.studentName, sa.rollNo, sa.studentAccount, sa.regNo, sas.studentAssinmentID, sas.resourseId, sas.docPDFPath, if(docUploadtime,DATE_FORMAT(CONVERT_TZ(sas.docUploadtime,'+00:00', @@global .time_zone), '%Y-%m-%d %H:%i:%s'),'') as docUploadtime, sas.isSubmited, sas.submissionDate, lr.path, lr.storage_object, lr.backend_type, sas.isConfirmed, sas.assignmentStatus, sas.remarks, sm.markID, sm.marksObtained, sm.percentage, $request->assignmentId as assignmentID from failed_students fs left join studentaccount sa on fs.studentID= sa.studentID inner join semesters fsem on fsem.semID = fs.failedInSemester inner join semesters joinedSem on sa.joiningSemId = joinedSem.semID left join student_assinments sas ON sas.studentID=sa.studentID AND sas.assinmentID=$request->assignmentId left join assignment_marks sm ON sm.studentID=sa.studentID AND sm.assignmentID=sas.assinmentID AND sm.csID IS NULL left join lin_resource lr ON lr.id=sas.resourseId where previousBatch =$request->batchId and fsem.orderNo > $semDetails->orderNo and joinedSem.orderNo <= $semDetails->orderNo order by $sortByColumn"; | |
| } | |
| } | |
| } | |
| if($insert_sql) | |
| { | |
| $this->executeQuery($insert_sql); | |
| } | |
| $sql_count="SELECT count(*) as totalRecord FROM (".$sql.") as stdnt"; | |
| $studentCount = $this->executeQueryForObject($sql_count)->totalRecord; | |
| $studentDetails = new SearchStudentResponse(); | |
| $studentDetails->totalRecords = $studentCount; | |
| $sql .= " LIMIT $request->startIndex,$request->endIndex"; | |
| $studentDetails->students = $this->executeQueryForList($sql); | |
| return $studentDetails; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * @param $batchId,$semId,$maxAssignmentNum | |
| * @throws ProfessionalException | |
| */ | |
| public function saveBatchwiseAssignmentRules($batchId,$semId,$maxAssignmentNum,$subjectId=null){ | |
| $batchId = $this->realEscapeString($batchId); | |
| $semId = $this->realEscapeString($semId); | |
| $subjectId = $this->realEscapeString($subjectId); | |
| $maxAssignmentNum = $this->realEscapeString($maxAssignmentNum); | |
| if($subjectId){ | |
| $sql = "INSERT INTO batchwise_assignment_rules (batchID, semID, subjectID, maxassignmentNum) VALUES ($batchId,$semId,$subjectId,$maxAssignmentNum)"; | |
| } | |
| else{ | |
| $sql = "INSERT INTO batchwise_assignment_rules (batchID, semID, maxassignmentNum) VALUES ($batchId,$semId,$maxAssignmentNum)"; | |
| } | |
| try { | |
| return $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * @param $batchId,$semId,$maxAssignmentNum | |
| * @throws ProfessionalException | |
| */ | |
| public function updateBatchwiseAssignmentRules($batchId,$semId,$maxAssignmentNum,$subjectId=null){ | |
| $batchId = $this->realEscapeString($batchId); | |
| $semId = $this->realEscapeString($semId); | |
| $subjectId = $this->realEscapeString($subjectId); | |
| $maxAssignmentNum = $this->realEscapeString($maxAssignmentNum); | |
| if($subjectId){ | |
| $sql = "UPDATE batchwise_assignment_rules SET maxassignmentNum = $maxAssignmentNum WHERE batchID = $batchId AND semID = $semId AND subjectID = '$subjectId'"; | |
| } | |
| else{ | |
| $sql = "UPDATE batchwise_assignment_rules SET maxassignmentNum = $maxAssignmentNum WHERE batchID = $batchId AND semID = $semId AND subjectID IS NULL"; | |
| } | |
| try { | |
| return $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| public function getAssignmentDetailsByRequest(AssignmentBatchSubjectRequest $assignment) | |
| { | |
| $sql = "select ba.assiNu, ba.assignmentID, ba.max_mark, ba.submissionDate, ba.enableConfirm, ba.asyncReportsId, ar.lin_resource_id as linResourseId, ba.is_published_student_marks as isPublishedStudentMarks from batch_assignment ba left join async_reports ar ON ba.asyncReportsId=ar.id where ba.batchID=$assignment->batchId and ba.staffID=$assignment->staffId and ba.semID=$assignment->semId and ba.subjectID=$assignment->subjectId and ".($assignment->isExternal?"ba.externalAssiNu=$assignment->assiNu":"ba.assiNu=$assignment->assiNu")." and ba.subbatchID = $assignment->subbatchId"; | |
| try { | |
| return $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * @param $batchId,$semId | |
| * @throws ProfessionalException | |
| */ | |
| public function deleteBatchwiseAssignmentRules($batchId,$semId,$subjectId=null){ | |
| $batchId = $this->realEscapeString($batchId); | |
| $semId = $this->realEscapeString($semId); | |
| $subjectId = $this->realEscapeString($subjectId); | |
| if($subjectId){ | |
| $sql = "DELETE FROM batchwise_assignment_rules WHERE batchID = $batchId AND semID = $semId AND subjectID = $subjectId"; | |
| } | |
| else{ | |
| $sql = "DELETE FROM batchwise_assignment_rules WHERE batchID = $batchId AND semID = $semId AND subjectID IS NULL"; | |
| } | |
| try { | |
| return $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| public function checkAssignmentQuestionWiseMarkEntry($assignmentId) | |
| { | |
| $assignmentId = $this->realEscapeString($assignmentId); | |
| $enableAssessmentQuestionMarkEntry=true; | |
| try { | |
| $sql_check_question_mark = "SELECT id FROM assessment_student_marks WHERE assessment_id = ".$assignmentId." AND assessment_type = 'ASSIGNMENT'"; | |
| $assessmentQuestionMarks = $this->executeQueryForList($sql_check_question_mark); | |
| if(count($assessmentQuestionMarks)==0){ | |
| $enableAssessmentQuestionMarkEntry = false; | |
| } | |
| return $enableAssessmentQuestionMarkEntry; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException ($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| public function saveStudentAssignmentMarks($assignmentStudentList) | |
| { | |
| try { | |
| foreach($assignmentStudentList as $assignment) | |
| { | |
| if($assignment->mark=='') | |
| { | |
| $this->deleteStudentAssignmentMarks($assignment->studentId, $assignment->assignmentId); | |
| } | |
| else | |
| { | |
| if($assignment->markId) | |
| { | |
| $this->updateStudentAssignmentMarks($assignment); | |
| } | |
| else | |
| { | |
| $this->createStudentAssignmentMarks($assignment); | |
| } | |
| $sqlCheck = "select studentAssinmentID from student_assinments where assinmentID='$assignment->assignmentId' and studentID ='$assignment->studentId'"; | |
| $studentAssinmentID = $this->executeQueryForObject($sqlCheck)->studentAssinmentID; | |
| if($studentAssinmentID){ | |
| $sql = "update student_assinments set isSubmited=\"$assignment->isSubmited\", submissionDate=\"$assignment->submissionDate\", assignmentStatus=\"$assignment->status\" where assinmentID=\"$assignment->assignmentId\" and studentID=$assignment->studentId"; | |
| }else{ | |
| $sql = "insert into student_assinments (assinmentID, studentID,isSubmited,assignmentStatus, submissionDate) values ('$assignment->assignmentId','$assignment->studentId','$assignment->isSubmited', '$assignment->status','$assignment->submissionDate')"; | |
| } | |
| $this->executeQuery($sql); | |
| } | |
| } | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException ($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| public function updateAsyncReportDetails($asyncReportId, $assignmentId) | |
| { | |
| $sql ="UPDATE batch_assignment SET asyncReportsId=$asyncReportId, asyncReportChanged=0 WHERE assignmentID=$assignmentId"; | |
| try { | |
| $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException ($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| public function setAsyncDocStatus($assignmentId) | |
| { | |
| $sql ="UPDATE batch_assignment SET asyncReportChanged=1 WHERE assignmentID=$assignmentId"; | |
| try { | |
| $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException ($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| public function getAssigmentAsyncFile($assignmentId) | |
| { | |
| $sql="select ba.asyncReportsId, sr.lin_resource_id, lr.path, lr.storage_object, lr.backend_type from async_reports sr inner join batch_assignment ba ON ba.asyncReportsId=sr.id left join lin_resource lr ON lr.id=sr.lin_resource_id WHERE ba.assignmentID=$assignmentId"; | |
| try { | |
| return $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException ($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| public function updateAssignmentIsPublishMarks($assignmentDetail){ | |
| $assignmentDetail = $this->realEscapeObject($assignmentDetail); | |
| $batchAssignmentConditions = null; | |
| if ( $assignmentDetail->pseudoSubjectId ) { | |
| $batchAssignmentConditions .= | |
| " staffID = '$assignmentDetail->staffId' | |
| AND psID = '$assignmentDetail->pseudoSubjectId' "; | |
| $batchAssignmentConditions .= "AND assiNu = '$assignmentDetail->assiNu'"; | |
| } else { | |
| $batchAssignmentConditions .= " assignmentID IN ($assignmentDetail->id)"; | |
| } | |
| $sql = "UPDATE batch_assignment SET is_published_student_marks = '" . $assignmentDetail->isPublished . "' WHERE $batchAssignmentConditions "; | |
| try { | |
| $this->executeQueryForObject($sql,true); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| public function findPseudoSubjectAssignment($pseudoSubjectId,$assiNu,$isExternal = NULL){ | |
| $pseudoSubjectId = $this->realEscapeString($pseudoSubjectId); | |
| $assiNu = $this->realEscapeString($assiNu); | |
| if($isExternal){ | |
| $sql = "select * from batch_assignment where psID = '$pseudoSubjectId' and externalAssiNu = '$assiNu';"; | |
| } | |
| else{ | |
| $sql = "select * from batch_assignment where psID = '$pseudoSubjectId' and assiNu = '$assiNu';"; | |
| } | |
| try { | |
| $data = $this->executeQueryForList($sql); | |
| return !empty($data)?true:false; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| public function getSumOfQuestionMarksInAnAssignment($request){ | |
| $request = $this->realEscapeObject($request); | |
| if($request->pseudoSubjectId){ | |
| $assSql = "select ba.assignmentID, ba.question, ba.description, ba.submissionDate, ba.submissionTime, ba.batchID, ba.staffID, ba.subjectID, ba.semID, ba.assiNu, ba.docPath, ba.docName, ba.max_mark, ba.returnDate, ba.psID, ba.subbatchID, ba.restrictStudentSubmit, ba.is_published, ba.publishDate, ba.publishTime, ba.userType, ba.externalAssiNu, ba.enableConfirm, ba.asyncReportsId, ba.asyncReportChanged from batch_assignment ba | |
| where ba.psID = $request->pseudoSubjectId and ".($request->extAssiNu?"ba.externalAssiNu = $request->extAssiNu":"ba.assiNu = $request->assiNu").";"; | |
| $singleAssignment = $this->executeQueryForObject($assSql); | |
| $assignmentId = $singleAssignment->assignmentID; | |
| }else{ | |
| $assignmentId = $request->assignmentId; | |
| } | |
| $sql="select sum(mark) as totalMarks from batch_assignment ba | |
| inner join assessment_structure ast on ast.assessment_key = ba.assiNu and ba.semID = ast.semID and ba.batchID = ast.batchID and ast.subjectID = ba.subjectID and ast.subbatchID = ba.subbatchID and ast.assessment_type= 'ASSIGnMENT' | |
| inner join assessment_structure_section ass on ass.assessment_structure_id = ast.id | |
| inner join assessment_structure_section_questions assq on assq.assessment_structure_section_id = ass.id | |
| inner join assessment_questions aq on aq.id = assq.assessment_question_id | |
| where ba.assignmentID = $assignmentId;"; | |
| try { | |
| return $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException ($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| public function getMaxStudentMarkInAnAssignment($request){ | |
| $request = $this->realEscapeObject($request); | |
| if($request->pseudoSubjectId && $request->assiNu){ | |
| $condition = "ba.psID = $request->pseudoSubjectId and ba.assiNu = $request->assiNu;"; | |
| }else if($request->pseudoSubjectId && $request->extAssiNu){ | |
| $condition = "ba.psID = $request->pseudoSubjectId and ba.externalAssiNu = $request->extAssiNu;"; | |
| }else{ | |
| $condition = "ba.assignmentID = $request->assignmentId;"; | |
| } | |
| $sql="select max(marksObtained) as maxMark,count(studentID) as studentCount from batch_assignment ba | |
| inner join assignment_marks am on am.assignmentID = ba.assignmentID | |
| where $condition"; | |
| try { | |
| return $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException ($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| public function getAssignmentMarksByIds($request){ | |
| $request = $this->realEscapeObject($request); | |
| try{ | |
| $sql = "SELECT t1.marksObtained FROM assignment_marks t1, batch_assignment t2 WHERE t1.assignmentID = t2.assignmentID AND t2.batchID = ".$request->batchId." AND t2.semID = ".$request->semId." AND t1.studentID = ".$request->studentId." AND t2.subjectID = ".$request->subjectId." AND t2.assiNu = ".$request->assiNu." and t1.csID IS NULL"; | |
| return $this->executeQueryForObject($sql); | |
| }catch (\Exception $e) { | |
| throw new ProfessionalException ($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * get student assignment marks | |
| * @param Object $request | |
| * @throws ProfessionalException | |
| * @return object|array|\com\linways\base\util\$objectList[] | |
| */ | |
| public function getAssignmentMarkListByRequest($request) | |
| { | |
| $assignmentList=NULL; | |
| $request= $this->realEscapeObject($request); | |
| $sql = "SELECT sa.studentID, sa.studentName, ba.subjectID, sub.subjectName, ba.assignmentID, ba.assiNu, ba.max_mark, am.marksObtained,concat(ba.subjectID,ba.assignmentID,ba.assiNu) as subjAssign, ba.description,sub.subjectDesc FROM batch_assignment ba INNER JOIN subjects sub ON ba.subjectID = sub.subjectID INNER JOIN studentaccount sa ON ba.batchID=sa.batchID AND sa.studentID=$request->studentId LEFT JOIN assignment_marks am ON am.assignmentID=ba.assignmentID WHERE ba.batchID = $request->batchId AND ba.semID = $request->semId AND am.csID IS NULL AND ba.assiNu is not null AND ba.assiNu !=0 AND am.studentID = $request->studentId "; | |
| if($request->subjectId){ | |
| $sql .= " AND sub.subjectID = $request->subjectId"; | |
| } | |
| if($request->onlyIfPublishedStudentMarks){ | |
| $sql .= " AND ba.is_published_student_marks = '1' "; | |
| } | |
| $sql .= " ORDER BY ba.assiNu ASC"; | |
| try { | |
| $assignmentList = $this->executeQueryForList($sql,$this->mapper[AssignmentServiceMapper::GET_ASSIGNMENT_MARK_LIST_BY_STUDENT]); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); | |
| } | |
| return $assignmentList; | |
| } | |
| public function getPseudoSubjectAssignmentList($pseudoSubjectId) | |
| { | |
| $pseudoSubjectId = $this->realEscapeString($pseudoSubjectId); | |
| $sql = "SELECT ba.assignmentID as id, ba.max_mark as maxMark,ba.assiNu, ba.submissionDate, ba.description, ba.returnDate, ba.question, ba.docPath, ba.docName from sbs_relation sbs | |
| inner join pseudosubjects_sbs psbs on psbs.sbsID = sbs.sbsID | |
| inner join batch_assignment ba on ba.batchID = sbs.batchID and ba.semID = sbs.semID and ba.subjectID = sbs.subjectID and ba.staffID = sbs.staffID | |
| where psbs.pseudosubjectID = '$pseudoSubjectId' | |
| group by ba.assiNu;"; | |
| try { | |
| return $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| } |