Code Coverage |
||||||||||
Classes and Traits |
Functions and Methods |
Lines |
||||||||
| Total | |
0.00% |
0 / 1 |
|
0.00% |
0 / 74 |
CRAP | |
0.00% |
0 / 1403 |
| LabService | |
0.00% |
0 / 1 |
|
0.00% |
0 / 74 |
54990.00 | |
0.00% |
0 / 1403 |
| __construct | |
0.00% |
0 / 1 |
2.00 | |
0.00% |
0 / 3 |
|||
| __clone | |
0.00% |
0 / 1 |
2.00 | |
0.00% |
0 / 2 |
|||
| getInstance | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 7 |
|||
| getExperiments | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 16 |
|||
| getExperimentById | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 18 |
|||
| getMarkEntryTemplateId | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 15 |
|||
| getConsolidatedMarkEntryTemplateId | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 15 |
|||
| addExperiment | |
0.00% |
0 / 1 |
42.00 | |
0.00% |
0 / 24 |
|||
| addExperimentSubbatch | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 13 |
|||
| updateExperiment | |
0.00% |
0 / 1 |
72.00 | |
0.00% |
0 / 38 |
|||
| deleteExperiment | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 14 |
|||
| deleteExperimentStudentMarks | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 14 |
|||
| deleteExperimentSubbatch | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 12 |
|||
| deleteExperimentSubbatchBySubbatchId | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 13 |
|||
| generateExperimentNumber | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 20 |
|||
| getDefinedExpNos | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 16 |
|||
| addExperimentTemplateColumnMark | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 24 |
|||
| addStudentExperimentDetail | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 22 |
|||
| getExperimentMarkTemplateColumnById | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 13 |
|||
| getExperimentFormulaRule | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 14 |
|||
| getExperimentRule | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 14 |
|||
| getExperimentMarkByStudentId | |
0.00% |
0 / 1 |
30.00 | |
0.00% |
0 / 29 |
|||
| getDependentColumnsByColumnId | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 18 |
|||
| getColumnNoById | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 14 |
|||
| recalculateMarkByStudentId | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 46 |
|||
| getVariableValues | |
0.00% |
0 / 1 |
30.00 | |
0.00% |
0 / 36 |
|||
| recalculateExperimentMarks | |
0.00% |
0 / 1 |
42.00 | |
0.00% |
0 / 30 |
|||
| getExperimentColumnMarks | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 25 |
|||
| getFormulawiseStudentMarks | |
0.00% |
0 / 1 |
90.00 | |
0.00% |
0 / 67 |
|||
| getExperimentwiseStudentMarks | |
0.00% |
0 / 1 |
272.00 | |
0.00% |
0 / 92 |
|||
| deleteStudentColumnMarks | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 17 |
|||
| getMarkEntryTemplateIdByExperimentId | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 14 |
|||
| getExperimentTemplateColumNos | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 14 |
|||
| getExperimentTemplateColumns | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 19 |
|||
| assignCompletionDate | |
0.00% |
0 / 1 |
2.00 | |
0.00% |
0 / 4 |
|||
| getExperimentMarkReport | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 41 |
|||
| getConsolidatedExperimentMarkReport | |
0.00% |
0 / 1 |
30.00 | |
0.00% |
0 / 40 |
|||
| populateAttendanceColumn | |
0.00% |
0 / 1 |
42.00 | |
0.00% |
0 / 17 |
|||
| getMarkEntryTemplate | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 18 |
|||
| createMarkEntryTemplate | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 12 |
|||
| updateMarkEntryTemplate | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 12 |
|||
| createMarkEntryTemplateBatches | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 19 |
|||
| deleteMarkEntryTemplate | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 22 |
|||
| getMarkEntryTemplateBatchesByParentTempId | |
0.00% |
0 / 1 |
56.00 | |
0.00% |
0 / 36 |
|||
| deleteMarkEntryTemplateBatches | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 20 |
|||
| getExperimentTemplateColumnRules | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 12 |
|||
| generateNextColumnNoById | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 14 |
|||
| createMarkEntryTemplateColumn | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 20 |
|||
| updateMarkEntryTemplateColumn | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 19 |
|||
| updateMarkEntryTemplateColumnFormula | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 9 |
|||
| createMarkEntryTemplateColumnFormula | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 9 |
|||
| getExperimentTemplateByParentTemplateId | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 9 |
|||
| createMArkEntryExpRule | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 9 |
|||
| updateMArkEntryExpRule | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 9 |
|||
| getExperimentTemplateById | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 9 |
|||
| deleteMarkEntryTemplateColumn | |
0.00% |
0 / 1 |
90.00 | |
0.00% |
0 / 43 |
|||
| getMarkEntryTemplateColumnByReferredColumnId | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 9 |
|||
| deleteMarkEntryTemplateFormula | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 9 |
|||
| deleteMarkEntryTemplateExperimentRule | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 9 |
|||
| updateExperimentTemplate | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 10 |
|||
| getAllExperimentTemplateDetails | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 11 |
|||
| getLabExperimentRulesByRuleId | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 14 |
|||
| updateLabExperimentRules | |
0.00% |
0 / 1 |
42.00 | |
0.00% |
0 / 32 |
|||
| getLabSettingsByDeptId | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 9 |
|||
| deleteSettingsByTempId | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 8 |
|||
| createLabTemplateSettings | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 8 |
|||
| updateLabTemplateSettings | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 8 |
|||
| canFacultyCreateLabTemplate | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 10 |
|||
| getMarkEntryTemplateBySubjectId | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 14 |
|||
| createMarkEntryTemplateSubjects | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 8 |
|||
| getLabTemplate | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 10 |
|||
| getMarkEntryTemplateIdBySubject | |
0.00% |
0 / 1 |
30.00 | |
0.00% |
0 / 33 |
|||
| getConsolidatedMarkEntryTemplateIdBySubjectId | |
0.00% |
0 / 1 |
30.00 | |
0.00% |
0 / 33 |
|||
| isExperimentCreated | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 17 |
|||
| <?php | |
| namespace com\linways\core\ams\professional\service; | |
| use com\linways\core\ams\professional\util\ArrayUtil; | |
| use com\linways\core\ams\professional\mapper\LabServiceMapper; | |
| use com\linways\core\ams\professional\dto\LabMarkEntryTemplate; | |
| use com\linways\core\ams\professional\service\AttendanceService; | |
| use com\linways\core\ams\professional\dto\LabMarkEntryExperimentRule; | |
| use com\linways\core\ams\professional\dto\LabMarkEntryTemplateColumn; | |
| use com\linways\core\ams\professional\dto\LabMarkEntryTemplateBatches; | |
| use com\linways\core\ams\professional\exception\ProfessionalException; | |
| use com\linways\core\ams\professional\dto\LabMarkEntryTemplateRuleType; | |
| use com\linways\core\ams\professional\dto\LabStudentTemplateColumnMark; | |
| use com\linways\core\ams\professional\dto\LabMarkEntryTemplateColumnFormula; | |
| use com\linways\core\ams\professional\dto\LabMarkEntryTemplateSettings; | |
| use com\linways\core\ams\professional\dto\LabMarkEntryTemplateSubjects; | |
| class LabService 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 = LabServiceMapper::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 experiments by batchId, semId, subjectId | |
| * | |
| * @param int $batchId | |
| * @param int $semId | |
| * @param int $subjectId | |
| * @return LabExperiment[] | |
| */ | |
| public function getExperiments($batchId, $semId, $subjectId) | |
| { | |
| $experiments = [ ]; | |
| $batchId = $this->realEscapeString ( $batchId ); | |
| $semId = $this->realEscapeString ( $semId ); | |
| $subjectId = $this->realEscapeString ( $subjectId ); | |
| $sql = "SELECT le.id, le.no, le.name, le.description, le.labMarkEntryTemplateId, le.date as experimentDate, le.createdBy, le.createdDate, le.updatedBy, le.updatedDate, lsb.subbatchId, lsb.date, sb.subbatchName FROM labExperiment le LEFT JOIN labExperimentSubbatch lsb ON lsb.labExperimentId = le.id LEFT JOIN subbatches sb ON sb.subbatchID = lsb.subbatchId WHERE le.batchId = '$batchId' AND le.semId = '$semId' AND le.subjectId = '$subjectId' order by le.no"; | |
| try | |
| { | |
| $experiments = $this->executeQueryForList ( $sql, $this->mapper [LabServiceMapper::GET_EXPERIMENTS] ); | |
| } | |
| catch ( \Exception $e ) | |
| { | |
| throw new ProfessionalException ( $e->getCode (), $e->getMessage () ); | |
| } | |
| return $experiments; | |
| } | |
| /** | |
| * Get experiment by experimentId | |
| * | |
| * @param int $experimentId | |
| * @return LabExperiment | |
| */ | |
| public function getExperimentById($experimentId) | |
| { | |
| $experiment = NULL; | |
| $experimentId = $this->realEscapeString ( $experimentId ); | |
| $sql = "SELECT exp.id as experimentId, exp.no, exp.name, exp.description, exp.labMarkEntryTemplateId, exp.date as experimentDate, exp.batchId, exp.subjectId, exp.semId, exp.createdBy, exp.createdDate, exp.updatedBy, exp.updatedDate, expSub.subbatchId, expSub.date | |
| FROM | |
| labExperiment exp | |
| LEFT JOIN labExperimentSubbatch expSub ON exp.id = expSub.labExperimentId | |
| WHERE exp.id=$experimentId"; | |
| try | |
| { | |
| $experiment = $this->executeQueryForObject ( $sql, FALSE, $this->mapper [LabServiceMapper::GET_EXPERIMENTBYID] ); | |
| } | |
| catch ( \Exception $e ) | |
| { | |
| throw new ProfessionalException ( $e->getCode (), $e->getMessage () ); | |
| } | |
| return $experiment; | |
| } | |
| /** | |
| * Get template id for experment mark entry | |
| * | |
| * @param int $deptId | |
| * @param int $batchId | |
| * @param int $semId | |
| * @throws ProfessionalException | |
| * @return int | |
| */ | |
| public function getMarkEntryTemplateId($batchId, $semId) | |
| { | |
| $templateId = NULL; | |
| $batchId = $this->realEscapeString ( $batchId ); | |
| $semId = $this->realEscapeString ( $semId ); | |
| $sql = " SELECT lt.id FROM labMarkEntryTemplate lt inner join labMarkEntryTemplateBatches lba on lt.parentExpTempId = lba.parentTempId WHERE batchID = ".$batchId." AND semID = ".$semId." AND lt.parentExpTempId IS not NULL;"; | |
| try | |
| { | |
| $templateId = $this->executeQueryForObject ( $sql )->id; | |
| } | |
| catch ( \Exception $e ) | |
| { | |
| throw new ProfessionalException ( $e->getCode (), $e->getMessage () ); | |
| } | |
| return $templateId; | |
| } | |
| /** | |
| * Get template id for consolidated experment mark entry | |
| * | |
| * @param int $deptId | |
| * @param int $batchId | |
| * @param int $semId | |
| * @throws ProfessionalException | |
| * @return int | |
| */ | |
| public function getConsolidatedMarkEntryTemplateId($batchId, $semId) | |
| { | |
| $templateId = NULL; | |
| $batchId = $this->realEscapeString ( $batchId ); | |
| $semId = $this->realEscapeString ( $semId ); | |
| $sql = "SELECT lt.id FROM labMarkEntryTemplate lt inner join labMarkEntryTemplateBatches lba on lt.id = lba.parentTempId WHERE lba.batchId = ".$batchId." AND lba.semId = ".$semId." AND lt.parentExpTempId IS NULL "; | |
| try | |
| { | |
| $templateId = $this->executeQueryForObject ( $sql )->id; | |
| } | |
| catch ( \Exception $e ) | |
| { | |
| throw new ProfessionalException ( $e->getCode (), $e->getMessage () ); | |
| } | |
| return $templateId; | |
| } | |
| /** | |
| * Add a Lab Experiment | |
| * | |
| * @param LabExperiment $labExperiment | |
| */ | |
| public function addExperiment($labExperiment) | |
| { | |
| $labExperiment = $this->realEscapeObject ( $labExperiment ); | |
| $labExperimentId = NULL; | |
| $templateId = $this->getMarkEntryTemplateIdBySubject($labExperiment->subjectId, $labExperiment->batchId, $labExperiment->semId); | |
| $templateId = !empty($templateId) ? $templateId : 'NULL'; | |
| $labExperiment->date = !empty($labExperiment->date) ? "'$labExperiment->date'" : 'NULL'; | |
| $sql = "INSERT INTO labExperiment (no, name, description, batchId, semId, subjectId, labMarkEntryTemplateId, date, createdBy, createdDate, updatedBy, updatedDate) VALUES($labExperiment->no, '$labExperiment->name', '$labExperiment->description', $labExperiment->batchId, $labExperiment->semId, $labExperiment->subjectId, $templateId, $labExperiment->date,$labExperiment->createdBy, utc_timestamp(), $labExperiment->updatedBy, utc_timestamp())"; | |
| try | |
| { | |
| $labExperimentId = $this->executeQuery ( $sql, TRUE )->id; | |
| if (! empty ( $labExperiment->labExperimentSubbatches )) | |
| { | |
| foreach ( $labExperiment->labExperimentSubbatches as $labExperimentSubbatch ) | |
| { | |
| $this->addExperimentSubbatch ( $labExperimentSubbatch, $labExperimentId ); | |
| } | |
| } | |
| } | |
| catch ( \Exception $e ) | |
| { | |
| throw new ProfessionalException ( $e->getCode (), $e->getMessage () ); | |
| } | |
| return $labExperimentId; | |
| } | |
| /** | |
| * Add subbatch details of an experiment | |
| * | |
| * @param LabExperimentSubbatch $labExperimentSubbatch | |
| * @param int $labExperimentId | |
| */ | |
| public function addExperimentSubbatch($labExperimentSubbatch, $labExperimentId) | |
| { | |
| $labExperimentSubbatch = $this->realEscapeObject ( $labExperimentSubbatch ); | |
| $labExperimentId = $this->realEscapeString ( $labExperimentId ); | |
| $sql = "INSERT INTO labExperimentSubbatch(labExperimentId, subbatchId, date, createdBy, createdDate, updatedBy, updatedDate) VALUES($labExperimentId, $labExperimentSubbatch->subbatchId, '$labExperimentSubbatch->date', $labExperimentSubbatch->createdBy, utc_timestamp(), $labExperimentSubbatch->updatedBy, utc_timestamp())"; | |
| try | |
| { | |
| return $this->executeQuery ( $sql ); | |
| } | |
| catch ( \Exception $e ) | |
| { | |
| throw new ProfessionalException ( $e->getCode (), $e->getMessage () ); | |
| } | |
| } | |
| /** | |
| * Update Lab Experiment | |
| * | |
| * @param LabExperiment $labExperiment | |
| */ | |
| public function updateExperiment($labExperiment) | |
| { | |
| $labExperiment = $this->realEscapeObject ( $labExperiment ); | |
| $staffSubatches = [ ]; | |
| $sql = "UPDATE labExperiment SET "; | |
| if (! empty ( $labExperiment->description )) | |
| { | |
| $sql .= " description = '$labExperiment->description', "; | |
| } | |
| if (! empty ( $labExperiment->name )) | |
| { | |
| $sql .= " name = '$labExperiment->name', "; | |
| } | |
| if (! empty ( $labExperiment->date )) | |
| { | |
| $sql .= " date = '$labExperiment->date', "; | |
| } | |
| $sql .= "updatedBy = $labExperiment->updatedBy, updatedDate=utc_timestamp() WHERE id = $labExperiment->id"; | |
| try | |
| { | |
| $this->executeQuery ( $sql ); | |
| // Delete subbatch details which is assigned to this staff | |
| $staffSubatches = BatchService::getInstance ()->getSubbatchBystaffIdAndSubjectId ( $labExperiment->batchId, $labExperiment->subjectId, $labExperiment->semId, $labExperiment->updatedBy ); | |
| foreach ( $staffSubatches as $subbatch ) | |
| { | |
| // Deleting existing subbatches | |
| $this->deleteExperimentSubbatchBySubbatchId ( $labExperiment->id, $subbatch->subbatchID ); | |
| } | |
| if ($labExperiment->labExperimentSubbatches != NULL) | |
| { | |
| // Adding new subbatch details | |
| foreach ( $labExperiment->labExperimentSubbatches as $labExperimentSubbatch ) | |
| { | |
| $this->addExperimentSubbatch ( $labExperimentSubbatch, $labExperiment->id ); | |
| } | |
| } | |
| } | |
| catch ( \Exception $e ) | |
| { | |
| throw new ProfessionalException ( $e->getCode (), $e->getMessage () ); | |
| } | |
| } | |
| /* | |
| * Delete Experiment by id | |
| * @param int $labExperimentId | |
| */ | |
| public function deleteExperiment($labExperimentId) | |
| { | |
| $labExperimentId = $this->realEscapeString ( $labExperimentId ); | |
| // Delete labExperiment | |
| $sql = "DELETE FROM labExperiment WHERE id=$labExperimentId"; | |
| try | |
| { | |
| // Delete StudentMarks | |
| $this->deleteExperimentStudentMarks ( $labExperimentId ); | |
| // Delete experiment subbatch | |
| $this->deleteExperimentSubbatch ( $labExperimentId ); | |
| $this->executeQuery ( $sql ); | |
| } | |
| catch ( \Exception $e ) | |
| { | |
| throw new ProfessionalException ( $e->getCode (), $e->getMessage () ); | |
| } | |
| } | |
| function deleteExperimentStudentMarks($labExperimentId) | |
| { | |
| $labExperimentId = $this->realEscapeString ( $labExperimentId ); | |
| // deleting subbatch dates | |
| $sqlDetail = "DELETE FROM labExperimentStudentDetail WHERE labExperimentId = $labExperimentId"; | |
| // Delete labExperiment marks | |
| $sql = "DELETE FROM labStudentTemplateColumnMark WHERE labExperimentId = $labExperimentId"; | |
| try | |
| { | |
| $this->executeQuery ( $sqlDetail ); | |
| $this->executeQuery ( $sql ); | |
| } | |
| catch ( \Exception $e ) | |
| { | |
| throw new ProfessionalException ( $e->getCode (), $e->getMessage () ); | |
| } | |
| } | |
| /** | |
| * Delete experiment subbatches by experimentId | |
| * | |
| * @param unknown $labExperimentId | |
| * @throws ProfessionalException | |
| */ | |
| public function deleteExperimentSubbatch($labExperimentId) | |
| { | |
| $labExperimentId = $this->realEscapeString ( $labExperimentId ); | |
| $sql = "DELETE FROM labExperimentSubbatch WHERE labExperimentId=$labExperimentId"; | |
| try | |
| { | |
| $this->executeQuery ( $sql ); | |
| } | |
| catch ( \Exception $e ) | |
| { | |
| throw new ProfessionalException ( $e->getCode (), $e->getMessage () ); | |
| } | |
| } | |
| /** | |
| * Delete experiment subbatches by subbatchID and experimentId | |
| * | |
| * @param unknown $labExperimentId | |
| * @param int $subbatchId | |
| * @throws ProfessionalException | |
| */ | |
| public function deleteExperimentSubbatchBySubbatchId($labExperimentId, $subbatchId) | |
| { | |
| $labExperimentId = $this->realEscapeString ( $labExperimentId ); | |
| $subbatchId = $this->realEscapeString ( $subbatchId ); | |
| $sql = "DELETE FROM labExperimentSubbatch WHERE labExperimentId=$labExperimentId AND subbatchId=$subbatchId"; | |
| try | |
| { | |
| $this->executeQuery ( $sql ); | |
| } | |
| catch ( \Exception $e ) | |
| { | |
| throw new ProfessionalException ( $e->getCode (), $e->getMessage () ); | |
| } | |
| } | |
| /** | |
| * Generate number for new experiment. | |
| * | |
| * @param int $batchId | |
| * @param int $semId | |
| * @param int $subjectId | |
| * @throws ProfessionalException | |
| * @return number | |
| */ | |
| public function generateExperimentNumber($batchId, $semId, $subjectId) | |
| { | |
| $batchId = $this->realEscapeString ( $batchId ); | |
| $semId = $this->realEscapeString ( $semId ); | |
| $subjectId = $this->realEscapeString ( $subjectId ); | |
| $experimentNo = 1; | |
| $sql = "select max(no)+1 as no from labExperiment WHERE batchId=$batchId AND semId=$semId AND subjectId=$subjectId"; | |
| try | |
| { | |
| $number = $this->executeQueryForObject ( $sql )->no; | |
| if ($number) | |
| { | |
| $experimentNo = $number; | |
| } | |
| } | |
| catch ( \Exception $e ) | |
| { | |
| throw new ProfessionalException ( $e->getCode (), $e->getMessage () ); | |
| } | |
| return $experimentNo; | |
| } | |
| public function getDefinedExpNos($batchId, $semId, $subjectId) | |
| { | |
| $batchId = $this->realEscapeString ( $batchId ); | |
| $semId = $this->realEscapeString ( $semId ); | |
| $subjectId = $this->realEscapeString ( $subjectId ); | |
| $definedExpNos = ""; | |
| $sql = "SELECT GROUP_CONCAT(no) as definedExpNos FROM labExperiment WHERE batchId = $batchId AND semId = $semId AND subjectId = $subjectId"; | |
| try | |
| { | |
| $definedExpNos = $this->executeQueryForObject ( $sql )->definedExpNos; | |
| } | |
| catch ( \Exception $e ) | |
| { | |
| throw new ProfessionalException ( $e->getCode (), $e->getMessage () ); | |
| } | |
| return $definedExpNos; | |
| } | |
| /** | |
| * Add experiment column mark | |
| * | |
| * @param LabStudentTemplateColumnMark $labStudentTemplateColumnMark | |
| */ | |
| public function addExperimentTemplateColumnMark($labStudentTemplateColumnMark) | |
| { | |
| try | |
| { | |
| $labStudentTemplateColumnMark = $this->realEscapeObject ( $labStudentTemplateColumnMark ); | |
| $markEntered = 0; | |
| $sqlCheck = "SELECT IF(id, 1, 0) as markEntered FROM labStudentTemplateColumnMark WHERE labExperimentId = $labStudentTemplateColumnMark->labBatchExperimentId AND labMarkEntryTemplateColumnId = $labStudentTemplateColumnMark->labMarkEntryTemplateColumnId AND subjectId = $labStudentTemplateColumnMark->subjectId AND studentId = $labStudentTemplateColumnMark->studentId AND semId = ".$labStudentTemplateColumnMark->semId.""; | |
| $markEntered = $this->executeQueryForObject ( $sqlCheck )->markEntered; | |
| if ($markEntered) | |
| { | |
| $deleteSql = "DELETE FROM labStudentTemplateColumnMark WHERE labExperimentId = $labStudentTemplateColumnMark->labBatchExperimentId AND labMarkEntryTemplateColumnId = $labStudentTemplateColumnMark->labMarkEntryTemplateColumnId AND subjectId = $labStudentTemplateColumnMark->subjectId AND studentId = $labStudentTemplateColumnMark->studentId AND semId = ".$labStudentTemplateColumnMark->semId.""; | |
| $this->executeQuery ( $deleteSql ); | |
| $sql = "INSERT INTO labStudentTemplateColumnMark (labExperimentId, labMarkEntryTemplateColumnId, subjectId, batchId, studentId, semId, marks, createdBy, createdDate, updatedBy, updatedDate) VALUES ($labStudentTemplateColumnMark->labBatchExperimentId, $labStudentTemplateColumnMark->labMarkEntryTemplateColumnId, $labStudentTemplateColumnMark->subjectId, $labStudentTemplateColumnMark->batchId, $labStudentTemplateColumnMark->studentId, ".$labStudentTemplateColumnMark->semId.", $labStudentTemplateColumnMark->marks, $labStudentTemplateColumnMark->createdBy, utc_timestamp(), $labStudentTemplateColumnMark->updatedBy, utc_timestamp())"; | |
| // $sql = "UPDATE labStudentTemplateColumnMark SET marks = $labStudentTemplateColumnMark->marks, updatedBy = $labStudentTemplateColumnMark->updatedBy, updatedDate = utc_timestamp() WHERE labExperimentId = $labStudentTemplateColumnMark->labBatchExperimentId AND labMarkEntryTemplateColumnId = $labStudentTemplateColumnMark->labMarkEntryTemplateColumnId AND subjectId = $labStudentTemplateColumnMark->subjectId AND batchId = $labStudentTemplateColumnMark->batchId AND studentId = $labStudentTemplateColumnMark->studentId AND semId = ".$labStudentTemplateColumnMark->semId.""; | |
| } | |
| else | |
| { | |
| $sql = "INSERT INTO labStudentTemplateColumnMark (labExperimentId, labMarkEntryTemplateColumnId, subjectId, batchId, studentId, semId, marks, createdBy, createdDate, updatedBy, updatedDate) VALUES ($labStudentTemplateColumnMark->labBatchExperimentId, $labStudentTemplateColumnMark->labMarkEntryTemplateColumnId, $labStudentTemplateColumnMark->subjectId, $labStudentTemplateColumnMark->batchId, $labStudentTemplateColumnMark->studentId, ".$labStudentTemplateColumnMark->semId.", $labStudentTemplateColumnMark->marks, $labStudentTemplateColumnMark->createdBy, utc_timestamp(), $labStudentTemplateColumnMark->updatedBy, utc_timestamp())"; | |
| } | |
| return $this->executeQuery ( $sql ); | |
| } | |
| catch ( \Exception $e ) | |
| { | |
| throw new ProfessionalException ( $e->code, $e->message ); | |
| } | |
| } | |
| /** | |
| * Add experiment student detail | |
| * | |
| * @param LabStudentExperimentDetail $labStudentExperimentDetail | |
| */ | |
| public function addStudentExperimentDetail($labStudentExperimentDetail) | |
| { | |
| $labStudentExperimentDetail = $this->realEscapeObject ( $labStudentExperimentDetail ); | |
| $detailEntered = 0; | |
| $sqlCheck = "SELECT IF(id, 1, 0) FROM labExperimentStudentDetail WHERE labExperimentId = $labStudentExperimentDetail->experimentId AND studentId = $labStudentExperimentDetail->studentId"; | |
| $detailEntered = $this->executeQueryForObject ( $sqlCheck ); | |
| if ($detailEntered) | |
| { | |
| $sql = "UPDATE labExperimentStudentDetail SET completionDate =' $labStudentExperimentDetail->completionDate', updatedBy = $labStudentExperimentDetail->updatedBy, updatedDate = utc_timestamp() WHERE labExperimentId = $labStudentExperimentDetail->experimentId AND studentId = $labStudentExperimentDetail->studentId"; | |
| } | |
| else | |
| { | |
| $sql = "INSERT INTO labExperimentStudentDetail (studentId, labExperimentId, completionDate, createdBy, createdDate, updatedBy, updatedDate) VALUES ($labStudentExperimentDetail->studentId, $labStudentExperimentDetail->experimentId, '$labStudentExperimentDetail->completionDate', $labStudentExperimentDetail->createdBy, utc_timestamp(), $labStudentExperimentDetail->updatedBy, utc_timestamp())"; | |
| } | |
| try | |
| { | |
| return $this->executeQuery ( $sql ); | |
| } | |
| catch ( \Exception $e ) | |
| { | |
| throw new ProfessionalException ( $e->code, $e->message ); | |
| } | |
| } | |
| /** | |
| * Get Template column details | |
| * | |
| * @param unknown $columnId | |
| * @throws ProfessionalException | |
| * @return \com\linways\base\connection\Object | |
| */ | |
| public function getExperimentMarkTemplateColumnById($columnId) | |
| { | |
| $labMarkEntryTemplateColumn = null; | |
| $columnId = $this->realEscapeString ( $columnId ); | |
| $sql = "SELECT id, labMarkEntryTemplateId, number, name, ruleType, totalMark, createdBy, createdDate, updatedBy, updatedDate FROM labMarkEntryTemplateColumn WHERE id = $columnId"; | |
| try | |
| { | |
| return $this->executeQueryForObject ( $sql ); | |
| } | |
| catch ( \Exception $e ) | |
| { | |
| throw new ProfessionalException ( $e->code, $e->message ); | |
| } | |
| } | |
| /** | |
| * Get experiment formula | |
| * | |
| * @param int $columnId | |
| * @return LabMarkEntryTemplateColumnFormula | |
| */ | |
| public function getExperimentFormulaRule($columnId) | |
| { | |
| $labMarkEntryTemplateColumnFormula = null; | |
| $columnId = $this->realEscapeString ( $columnId ); | |
| $sql = "SELECT id, labMarkEntryTemplateColumnId, formula FROM labMarkEntryTemplateColumnFormula WHERE labMarkEntryTemplateColumnId = $columnId"; | |
| try | |
| { | |
| $labMarkEntryTemplateColumnFormula = $this->executeQueryForObject ( $sql ); | |
| } | |
| catch ( \Exception $e ) | |
| { | |
| throw new ProfessionalException ( $e->getCode (), $e->getMessage () ); | |
| } | |
| return $labMarkEntryTemplateColumnFormula; | |
| } | |
| /** | |
| * Get experiment rule | |
| * | |
| * @param int $columnId | |
| * @return LabMarkEntryTemplateColumnFormula | |
| */ | |
| public function getExperimentRule($columnId) | |
| { | |
| $labMarkEntryTemplateColumnRule = null; | |
| $columnId = $this->realEscapeString ( $columnId ); | |
| $sql = "SELECT id, labMarkEntryTemplateColumnId, expNos, referredlabMarkEntryTemplateColumnId, mark, bestOf, isAvg, ordinalNo FROM labMArkEntryExpRule WHERE labMarkEntryTemplateColumnId = $columnId"; | |
| try | |
| { | |
| $labMarkEntryTemplateColumnFormula = $this->executeQueryForObject ( $sql ); | |
| } | |
| catch ( \Exception $e ) | |
| { | |
| throw new ProfessionalException ( $e->getCode (), $e->getMessage () ); | |
| } | |
| return $labMarkEntryTemplateColumnFormula; | |
| } | |
| /** | |
| * | |
| * @param int $studentId | |
| * @param int $labExperimentId | |
| * @param int $columnId | |
| * @param int $batchId | |
| * @param int $subjectId | |
| * @return array[] | |
| */ | |
| public function getExperimentMarkByStudentId($studentId, $labExperimentId, $columnId, $batchId, $subjectId, $staffId, $semId) | |
| { | |
| $studentId = $this->realEscapeString ( $studentId ); | |
| $columnId = $this->realEscapeString ( $columnId ); | |
| $labExperimentId = $this->realEscapeString ( $labExperimentId ); | |
| $batchId = $this->realEscapeString ( $batchId ); | |
| $subjectId = $this->realEscapeString ( $subjectId ); | |
| $staffId = $this->realEscapeString ( $staffId ); | |
| $semId = $this->realEscapeString($semId); | |
| $studentMarks = [ ]; | |
| $dependentColumnList = NULL; | |
| $rule = NULL; | |
| try | |
| { | |
| $dependentColumnList = $this->getDependentColumnsByColumnId ( $columnId ); | |
| if (! empty ( $dependentColumnList ) && $dependentColumnList != NULL) | |
| { | |
| foreach ( $dependentColumnList as $column ) | |
| { | |
| $rule = $this->getExperimentFormulaRule ( $column->id ); | |
| $studentMarks [] = $this->recalculateMarkByStudentId ( $studentId, $labExperimentId, $rule, $column->id, $batchId, $subjectId, $staffId, $semId ); | |
| } | |
| } | |
| } | |
| catch ( \Exception $e ) | |
| { | |
| throw new ProfessionalException ( $e->getCode (), $e->getMessage () ); | |
| } | |
| return $studentMarks; | |
| } | |
| /** | |
| * Get dependent columnIds by a columnId | |
| * | |
| * @param int $columnId | |
| * @throws ProfessionalException | |
| * @return \com\linways\base\connection\Object | |
| */ | |
| public function getDependentColumnsByColumnId($columnId) | |
| { | |
| $columnId = $this->realEscapeString ( $columnId ); | |
| $columnNo = NULL; | |
| $columnIds = [ ]; | |
| $columnNo = $this->getColumnNoById ( $columnId ); | |
| $sql = "SELECT clmn.id, clmn.number FROM labMarkEntryTemplateColumn clmn | |
| INNER JOIN labMarkEntryTemplateColumnFormula frmla ON clmn.id = frmla.labMarkEntryTemplateColumnId | |
| WHERE clmn.labMarkEntryTemplateId = (SELECT labMarkEntryTemplateId FROM labMarkEntryTemplateColumn WHERE id = $columnId) AND frmla.formula LIKE '%$columnNo%' "; | |
| try | |
| { | |
| $columnIds = $this->executeQueryForList ( $sql ); | |
| } | |
| catch ( \Exception $e ) | |
| { | |
| throw new ProfessionalException ( $e->getCode (), $e->getMessage () ); | |
| } | |
| return $columnIds; | |
| } | |
| /** | |
| * Get ColumnNo by columnId | |
| * | |
| * @param unknown $columnId | |
| * @throws ProfessionalException | |
| * @return unknown | |
| */ | |
| public function getColumnNoById($columnId) | |
| { | |
| $columnId = $this->realEscapeString ( $columnId ); | |
| $columnNo = ""; | |
| $sql = "select number from labMarkEntryTemplateColumn WHERE id = $columnId"; | |
| try | |
| { | |
| $columnNo = $this->executeQueryForObject ( $sql )->number; | |
| } | |
| catch ( \Exception $e ) | |
| { | |
| throw new ProfessionalException ( $e->getCode (), $e->getMessage () ); | |
| } | |
| return $columnNo; | |
| } | |
| /** | |
| * Recalculates the value of given Column | |
| * | |
| * @param int $studentId | |
| * @param int $labExperimentId | |
| * @param LabMarkEntryTemplateColumnFormula $rule | |
| * @param int $columnId | |
| * @param int $batchId | |
| * @param int $subjectId | |
| */ | |
| public function recalculateMarkByStudentId($studentId, $labExperimentId, $rule, $columnId, $batchId, $subjectId, $staffId, $semId) | |
| { | |
| $studentId = $this->realEscapeString ( $studentId ); | |
| $labExperimentId = $this->realEscapeString ( $labExperimentId ); | |
| $rule = $this->realEscapeObject ( $rule ); | |
| $columnId = $this->realEscapeString ( $columnId ); | |
| $batchId = $this->realEscapeString ( $batchId ); | |
| $subjectId = $this->realEscapeString ( $subjectId ); | |
| $staffId = $this->realEscapeString ( $staffId ); | |
| $semId = $this->realEscapeString($semId); | |
| $internalAssessmentStudentColumnMarks = 0; | |
| $formula_temp = NULL; | |
| $variables [] = NULL; | |
| $values [] = NULL; | |
| $mark = 0; | |
| $columnDetails = $this->getExperimentMarkTemplateColumnById ( $columnId ); | |
| $variables = array_unique ( preg_split ( "/[-+*\/^%\(\)]/", $rule->formula, - 1, PREG_SPLIT_NO_EMPTY ) ); | |
| $variables = array_filter ( $variables, function ($arrayEntry) | |
| { | |
| return ! is_numeric ( $arrayEntry ); | |
| } ); | |
| usort ( $variables, function ($a, $b) | |
| { | |
| return strlen ( $b ) - strlen ( $a ); | |
| } ); | |
| $labStudentTemplateColumnMark = new LabStudentTemplateColumnMark (); | |
| $labStudentTemplateColumnMark->labMarkEntryTemplateColumnId = $columnId; | |
| $labStudentTemplateColumnMark->labBatchExperimentId = $labExperimentId; | |
| $labStudentTemplateColumnMark->batchId = $batchId; | |
| $labStudentTemplateColumnMark->subjectId = $subjectId; | |
| $labStudentTemplateColumnMark->semId = $semId; | |
| $labStudentTemplateColumnMark->studentId = $studentId; | |
| $labStudentTemplateColumnMark->createdBy = $staffId; | |
| $labStudentTemplateColumnMark->updatedBy = $staffId; | |
| $formula_temp = $rule->formula; | |
| $values = $this->getVariableValues ( $studentId, $variables, $labExperimentId, $batchId, $subjectId, $semId ); | |
| foreach ( $variables as $variable ) | |
| { | |
| if ($values [$variable] != NULL) | |
| { | |
| $formula_temp = str_replace ( $variable, $values [$variable], $formula_temp ); | |
| } | |
| } | |
| // Evaluating formula. | |
| $mark = eval ( 'return ' . $formula_temp . ';' ); | |
| $mark = empty ( $mark ) ? 0 : round ( $mark, 2 ); | |
| $labStudentTemplateColumnMark->marks = $mark; | |
| // Saving column Marks | |
| $this->addExperimentTemplateColumnMark ( $labStudentTemplateColumnMark ); | |
| return $labStudentTemplateColumnMark; | |
| } | |
| /** | |
| * Get variable values | |
| * | |
| * @param int $studentId | |
| * @param int $variables | |
| * @param int $labExperimentId | |
| * @param int $batchId | |
| * @param int $subjectId | |
| */ | |
| public function getVariableValues($studentId, $variables, $labExperimentId, $batchId, $subjectId, $semId) | |
| { | |
| $studentId = $this->realEscapeString ( $studentId ); | |
| $variables = $this->realEscapeArray ( $variables ); | |
| $labExperimentId = $this->realEscapeString ( $labExperimentId ); | |
| $batchId = $this->realEscapeString ( $batchId ); | |
| $subjectId = $this->realEscapeString ( $subjectId ); | |
| $semId = $this->realEscapeString($semId); | |
| if($labExperimentId != 0) | |
| { | |
| $templateId = $this->getMarkEntryTemplateIdBySubject($subjectId, $batchId, $semId); | |
| } | |
| else | |
| { | |
| $templateId = $this->getConsolidatedMarkEntryTemplateIdBySubjectId($batchId, $semId, $subjectId); | |
| } | |
| $values = [ ]; | |
| foreach ( $variables as $variable ) | |
| { | |
| $sql = "SELECT clmn_mark.marks FROM labMarkEntryTemplateColumn clmn | |
| INNER JOIN labStudentTemplateColumnMark clmn_mark ON clmn.id = clmn_mark.labMarkEntryTemplateColumnId | |
| WHERE clmn_mark.labExperimentId = ".$labExperimentId." AND clmn.number='".$variable."' AND studentId=".$studentId." AND clmn_mark.batchId=".$batchId." AND clmn_mark.subjectId=".$subjectId." AND clmn.labMarkEntryTemplateId= ".$templateId." AND clmn_mark.semId = ".$semId.""; | |
| try | |
| { | |
| $value = $this->executeQueryForObject ( $sql )->marks; | |
| if ($value) | |
| { | |
| $values [$variable] = $value; | |
| } | |
| } | |
| catch ( \Exception $e ) | |
| { | |
| throw new ProfessionalException ( $e->getCode (), $e->getMessage () ); | |
| } | |
| } | |
| return $values; | |
| } | |
| /** | |
| * Recalculate the whole experiment mark | |
| * | |
| * @param int $experimentId | |
| * @param int $batchId | |
| * @param int $subjectId | |
| * @throws ProfessionalException | |
| */ | |
| public function recalculateExperimentMarks($experimentId, $batchId, $subjectId, $semId, $staffId) | |
| { | |
| $experimentId = $this->realEscapeString ( $experimentId ); | |
| $batchId = $this->realEscapeString ( $batchId ); | |
| $subjectId = $this->realEscapeString ( $subjectId ); | |
| $semId = $this->realEscapeString ( $semId ); | |
| $staffId = $this->realEscapeString ( $staffId ); | |
| if ($experimentId != 0) | |
| { | |
| $templateId = $this->getMarkEntryTemplateIdByExperimentId ( $experimentId ); | |
| } | |
| else | |
| { | |
| $templateId = $this->getConsolidatedMarkEntryTemplateIdBySubjectId($batchId, $semId, $subjectId ); | |
| } | |
| $columnIds = $this->getExperimentTemplateColumns ( $templateId, 1 ); | |
| if (! empty ( $columnIds ) && $columnIds != NULL) | |
| { | |
| foreach ( $columnIds as $columnId ) | |
| { | |
| try | |
| { | |
| $this->getExperimentColumnMarks ( $experimentId, $columnId->id, $batchId, $subjectId, $semId, $staffId ); | |
| } | |
| catch ( \Exception $e ) | |
| { | |
| throw new ProfessionalException ( $e->getCode (), $e->getMessage () ); | |
| } | |
| } | |
| } | |
| } | |
| /** | |
| * Get experiment column marks | |
| * | |
| * @param int $experimentId | |
| * @param int $markEntryColumnId | |
| * @param int $batchId | |
| * @param int $subjectId | |
| * @throws ProfessionalException | |
| */ | |
| public function getExperimentColumnMarks($experimentId, $markEntryColumnId, $batchId, $subjectId, $semId, $staffId) | |
| { | |
| $experimentId = $this->realEscapeString ( $experimentId ); | |
| $markEntryColumnId = $this->realEscapeString ( $markEntryColumnId ); | |
| $batchId = $this->realEscapeString ( $batchId ); | |
| $subjectId = $this->realEscapeString ( $subjectId ); | |
| $semId = $this->realEscapeString ( $semId ); | |
| $staffId = $this->realEscapeString ( $staffId ); | |
| $experimentStudentColumnMarks = [ ]; | |
| $experimentColumn = $this->getExperimentMarkTemplateColumnById ( $markEntryColumnId ); | |
| switch ($experimentColumn->ruleType) | |
| { | |
| case LabMarkEntryTemplateRuleType::FORMULA : | |
| $rules = $this->getExperimentFormulaRule ( $markEntryColumnId ); | |
| // Evaluate attendance marks of each student if rule is successfully saved. | |
| $experimentStudentColumnMarks = $this->getFormulawiseStudentMarks ( $rules, $experimentId, $experimentColumn, $batchId, $subjectId, $semId, $staffId ); | |
| break; | |
| case LabMarkEntryTemplateRuleType::EXPERIMENT : | |
| $rules = $this->getExperimentRule ( $markEntryColumnId ); | |
| // Evaluate attendance marks of each student if rule is successfully saved. | |
| $experimentStudentColumnMarks = $this->getExperimentwiseStudentMarks ( $rules, $experimentId, $experimentColumn, $batchId, $subjectId, $semId, $staffId ); | |
| break; | |
| default : | |
| throw new ProfessionalException ( ProfessionalException::ASSESSMENT_NO_RULE_DEFINED, "Invalid Rule for column:$experimentColumn->name" ); | |
| break; | |
| } | |
| return $experimentStudentColumnMarks; | |
| } | |
| /** | |
| * Get formulawise studentMarks | |
| * | |
| * @param LabMarkEntryTemplateColumnFormula $rules | |
| * @param int $experimentId | |
| * @param LabMarkEntryTemplateColumn $experimentColumn | |
| * @param int $batchId | |
| * @param int $subjectId | |
| */ | |
| public function getFormulawiseStudentMarks($rules, $experimentId, $experimentColumn, $batchId, $subjectId, $semId, $staffId) | |
| { | |
| $rules = $this->realEscapeObject ( $rules ); | |
| $experimentId = $this->realEscapeString ( $experimentId ); | |
| $experimentColumn = $this->realEscapeObject ( $experimentColumn ); | |
| $batchId = $this->realEscapeString ( $batchId ); | |
| $subjectId = $this->realEscapeString ( $subjectId ); | |
| $staffId = $this->realEscapeString ( $staffId ); | |
| $semId = $this->realEscapeString($semId); | |
| $experimentStudentColumnMarks = [ ]; | |
| $formula_temp = NULL; | |
| $variables [] = NULL; | |
| $values [] = NULL; | |
| $mark = 0; | |
| $this->deleteStudentColumnMarks ( $experimentId, $experimentColumn->id, $batchId, $subjectId , $semId); | |
| if ($batchId && $semId) | |
| { | |
| $isCurrentSem = SemesterService::getInstance()->isCurrentSemester($batchId, $semId); | |
| if ($isCurrentSem) | |
| { | |
| $sql_students = "SELECT studentID, studentName, rollNo, regNo FROM studentaccount WHERE batchID=" . $batchId . " order by rollNo"; | |
| } | |
| else | |
| { | |
| $sql_students = "select sa.studentID, sa.studentName, sa.rollNo, sa.regNo 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.regNo from failed_students fs left join studentaccount sa on fs.studentID= sa.studentID where previousBatch = $batchId and failedInSemester > $semId order by rollNo"; | |
| } | |
| $studentDetails = $this->executeQueryForList ( $sql_students ); | |
| } | |
| if (! empty ( $studentDetails )) | |
| { | |
| $variables = array_unique ( preg_split ( "/[-+*\/^%\(\)]/", $rules->formula, - 1, PREG_SPLIT_NO_EMPTY ) ); | |
| $variables = array_filter ( $variables, function ($arrayEntry) | |
| { | |
| return ! is_numeric ( $arrayEntry ); | |
| } ); | |
| usort ( $variables, function ($a, $b) | |
| { | |
| return strlen ( $b ) - strlen ( $a ); | |
| } ); | |
| foreach ( $studentDetails as $studentDetail ) | |
| { | |
| $mark = 0; | |
| $labStudentTemplateColumnMark = new LabStudentTemplateColumnMark (); | |
| $labStudentTemplateColumnMark->labMarkEntryTemplateColumnId = $experimentColumn->id; | |
| $labStudentTemplateColumnMark->labBatchExperimentId = $experimentId; | |
| $labStudentTemplateColumnMark->batchId = $batchId; | |
| $labStudentTemplateColumnMark->subjectId = $subjectId; | |
| $labStudentTemplateColumnMark->semId = $semId; | |
| $labStudentTemplateColumnMark->studentId = $studentDetail->studentID; | |
| $labStudentTemplateColumnMark->createdBy = $staffId; | |
| $labStudentTemplateColumnMark->updatedBy = $staffId; | |
| $values [] = NULL; | |
| $formula_temp = $rules->formula; | |
| $values = $this->getVariableValues ( $studentDetail->studentID, $variables, $experimentId, $batchId, $subjectId, $semId ); | |
| foreach ( $variables as $variable ) | |
| { | |
| if ($values [$variable] != NULL) | |
| { | |
| $formula_temp = str_replace ( $variable, $values [$variable], $formula_temp ); | |
| } | |
| } | |
| // Evaluating formula. | |
| $mark = eval ( 'return ' . $formula_temp . ';' ); | |
| $mark = empty ( $mark ) ? 0 : round ( $mark, 2 ); | |
| $labStudentTemplateColumnMark->marks = $mark; | |
| // Saving column Marks | |
| $this->addExperimentTemplateColumnMark ( $labStudentTemplateColumnMark ); | |
| $labStudentTemplateColumnMarks [] = $labStudentTemplateColumnMark; | |
| } | |
| } | |
| return $experimentStudentColumnMarks; | |
| } | |
| /** | |
| * Get experimentwise studentMarks for consolidated | |
| * | |
| * @param LabMarkEntryTemplateColumnFormula $rules | |
| * @param int $experimentId | |
| * @param LabMarkEntryTemplateColumn $experimentColumn | |
| * @param int $batchId | |
| * @param int $subjectId | |
| */ | |
| public function getExperimentwiseStudentMarks($rules, $experimentId, $experimentColumn, $batchId, $subjectId, $semId, $staffId) | |
| { | |
| $rules = $this->realEscapeObject ( $rules ); | |
| $experimentId = $this->realEscapeString ( $experimentId ); | |
| $experimentColumn = $this->realEscapeObject ( $experimentColumn ); | |
| $batchId = $this->realEscapeString ( $batchId ); | |
| $subjectId = $this->realEscapeString ( $subjectId ); | |
| $staffId = $this->realEscapeString ( $staffId ); | |
| $experimentStudentColumnMarks = [ ]; | |
| $expSubCount = []; | |
| $mark = 0; | |
| $templateId = $this->getMarkEntryTemplateIdBySubject($subjectId, $batchId, $semId); | |
| $this->deleteStudentColumnMarks ( $experimentId, $experimentColumn->id, $batchId, $subjectId , $semId); | |
| if ($batchId && $semId) | |
| { | |
| if ($isCurrentSem) | |
| { | |
| $sql_students = "SELECT studentID, studentName, rollNo, regNo FROM studentaccount WHERE batchID=" . $batchId . " order by rollNo"; | |
| } | |
| else | |
| { | |
| $sql_students = "select sa.studentID, sa.studentName, sa.rollNo, sa.regNo 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.regNo from failed_students fs left join studentaccount sa on fs.studentID= sa.studentID where previousBatch = $batchId and failedInSemester > $semId order by rollNo"; | |
| } | |
| $studentDetails = $this->executeQueryForList ( $sql_students ); | |
| } | |
| $sqlCount = "SELECT exp.id,sub.subbatchId FROM labExperiment exp LEFT JOIN labExperimentSubbatch sub ON sub.labExperimentId = exp.id WHERE exp.batchId = $batchId AND exp.subjectId = $subjectId AND exp.semId=$semId "; | |
| $expCounts = $this->executeQueryForList ( $sqlCount ); | |
| foreach ($expCounts as $expCount) | |
| { | |
| if(empty($expCount->subbatchId)) | |
| { | |
| $expSubCount[0] += 1; | |
| } | |
| else | |
| { | |
| $expSubCount[$expCount->subbatchId] += 1; | |
| } | |
| } | |
| if (! empty ( $studentDetails )) | |
| { | |
| foreach ( $studentDetails as $studentDetail ) | |
| { | |
| $mark = 0; | |
| $sum = 0; | |
| $expNo = 0; | |
| $labStudentTemplateColumnMark = new LabStudentTemplateColumnMark (); | |
| $labStudentTemplateColumnMark->labMarkEntryTemplateColumnId = $experimentColumn->id; | |
| $labStudentTemplateColumnMark->labBatchExperimentId = $experimentId; | |
| $labStudentTemplateColumnMark->batchId = $batchId; | |
| $labStudentTemplateColumnMark->subjectId = $subjectId; | |
| $labStudentTemplateColumnMark->semId = $semId; | |
| $labStudentTemplateColumnMark->studentId = $studentDetail->studentID; | |
| $labStudentTemplateColumnMark->createdBy = $staffId; | |
| $labStudentTemplateColumnMark->updatedBy = $staffId; | |
| if ($rules->expNos != 0) | |
| { | |
| $expNos = explode ( ",", $rules->expNos ); | |
| } | |
| else | |
| { | |
| $typeIDs = 0; | |
| } | |
| // Retrieving marks obtained in given column | |
| $sql_ex_mark = "SELECT mark.labExperimentId, mark.marks, exp.no FROM labStudentTemplateColumnMark mark | |
| INNER JOIN labExperiment exp ON mark.labExperimentId = exp.id | |
| WHERE mark.studentId = " . $studentDetail->studentID . " AND mark.batchId = $batchId AND mark.subjectId = " . $subjectId . " AND mark.labMarkEntryTemplateColumnId = $rules->referredlabMarkEntryTemplateColumnId AND mark.labExperimentId != 0 and labMarkEntryTemplateId = ".$templateId." and mark.semId = ".$semId." order by mark.marks desc"; | |
| $studentMarkDetails = $this->executeQueryForList ( $sql_ex_mark ); | |
| if ($studentMarkDetails != NULL) | |
| { | |
| foreach ( $studentMarkDetails as $studentMarkDetail ) | |
| { | |
| $sum += $studentMarkDetail->marks; | |
| } | |
| } | |
| foreach ($expSubCount as $subbatchId => $count) | |
| { | |
| if($subbatchId == 0 ) | |
| { | |
| $expNo += $count; | |
| } | |
| else if (BatchService::getInstance()->isStudentInSubbatch($subbatchId, $studentDetail->studentID)) | |
| { | |
| $expNo += $count; | |
| } | |
| } | |
| $mark = $expNo!=0?$sum/$expNo:0; | |
| $mark = empty ( $mark ) ? 0 : round ( $mark, 2 ); | |
| $labStudentTemplateColumnMark->marks = $mark; | |
| // Saving column Marks | |
| $this->addExperimentTemplateColumnMark ( $labStudentTemplateColumnMark ); | |
| $labStudentTemplateColumnMarks [] = $labStudentTemplateColumnMark; | |
| } | |
| } | |
| return $experimentStudentColumnMarks; | |
| } | |
| /** | |
| * Delete student column marks by experiment columnId | |
| * | |
| * @param int $experimentId | |
| * @param int $experimentColumnId | |
| * @param int $batchId | |
| * @param int $subjectId | |
| * @throws ProfessionalException | |
| * @return boolean | |
| */ | |
| public function deleteStudentColumnMarks($experimentId, $experimentColumnId, $batchId, $subjectId, $semId) | |
| { | |
| $experimentId = $this->realEscapeString ( $experimentId ); | |
| $experimentColumnId = $this->realEscapeString ( $experimentColumnId ); | |
| $batchId = $this->realEscapeString ( $batchId ); | |
| $subjectId = $this->realEscapeString ( $subjectId ); | |
| $semId = $this->realEscapeString($semId); | |
| $sql = "DELETE FROM labStudentTemplateColumnMark WHERE labExperimentId = $experimentId AND labMarkEntryTemplateColumnId = $experimentColumnId AND batchId=$batchId AND subjectId=$subjectId and semId = ".$semId.""; | |
| try | |
| { | |
| $this->executeQuery ( $sql ); | |
| } | |
| catch ( \Exception $e ) | |
| { | |
| throw new ProfessionalException ( $e->getCode (), $e->getMessage () ); | |
| } | |
| return true; | |
| } | |
| /** | |
| * Get mark entry templateId of an experiment | |
| * | |
| * @param int $experimentId | |
| * @throws ProfessionalException | |
| * @return $templateId | |
| */ | |
| public function getMarkEntryTemplateIdByExperimentId($experimentId) | |
| { | |
| $experimentId = $this->realEscapeString ( $experimentId ); | |
| $templateId = NULL; | |
| $sql = "SELECT labMarkEntryTemplateId as templateId FROM labExperiment WHERE id = $experimentId"; | |
| try | |
| { | |
| $templateId = $this->executeQueryForObject ( $sql )->templateId; | |
| } | |
| catch ( \Exception $e ) | |
| { | |
| throw new ProfessionalException ( $e->getCode (), $e->getMessage () ); | |
| } | |
| return $templateId; | |
| } | |
| /** | |
| * get template columns by templateId | |
| * | |
| * @param | |
| * int templateId | |
| * @throws ProfessionalException | |
| * @return object|array|\com\linways\base\util\$objectList[] | |
| */ | |
| public function getExperimentTemplateColumNos($templateId) | |
| { | |
| $templateId = $this->realEscapeString ( $templateId ); | |
| $columnIds = NULL; | |
| $sql = "SELECT id FROM labMarkEntryTemplateColumn WHERE labMarkEntryTemplateId=$templateId ORDER BY number"; | |
| try | |
| { | |
| $columnIds = $this->executeQueryForList ( $sql ); | |
| } | |
| catch ( \Exception $e ) | |
| { | |
| throw new ProfessionalException ( $e->getCode (), $e->getMessage () ); | |
| } | |
| return $columnIds; | |
| } | |
| /** | |
| * get template columns by templateId | |
| * | |
| * @param | |
| * int templateId | |
| * @throws ProfessionalException | |
| * @return object|array|\com\linways\base\util\$objectList[] | |
| */ | |
| public function getExperimentTemplateColumns($templateId, $forRecalculation = 0) | |
| { | |
| $templateId = $this->realEscapeString ( $templateId ); | |
| $forRecalculation = $this->realEscapeString ( $forRecalculation ); | |
| if ($forRecalculation) | |
| { | |
| $cond = " AND (ruletype='" . LabMarkEntryTemplateRuleType::FORMULA . "' OR ruletype='" . LabMarkEntryTemplateRuleType::EXPERIMENT . "' )"; | |
| } | |
| $columnIds = NULL; | |
| $sql = "SELECT id, number, name, ruleType, totalMark FROM labMarkEntryTemplateColumn WHERE labMarkEntryTemplateId=$templateId $cond ORDER BY number"; | |
| try | |
| { | |
| $columnIds = $this->executeQueryForList ( $sql ); | |
| } | |
| catch ( \Exception $e ) | |
| { | |
| throw new ProfessionalException ( $e->getCode (), $e->getMessage () ); | |
| } | |
| return $columnIds; | |
| } | |
| /** | |
| * assign subbatch dates | |
| * | |
| * @param int $experimentId | |
| * @param int $subjectId | |
| * @param int $batchId | |
| * @param int $semId | |
| * @throws ProfessionalException | |
| * @return object|array|\com\linways\base\util\$objectList[] | |
| */ | |
| public function assignCompletionDate($experimentId, $studentId) | |
| { | |
| $experimentId = $this->realEscapeString ( $experimentId ); | |
| $studentId = $this->realEscapeString ( $studentId ); | |
| } | |
| /** | |
| * get student marks by experimentId | |
| * | |
| * @param int $experimentId | |
| * @throws ProfessionalException | |
| * @return object|array|\com\linways\base\util\$objectList[] | |
| */ | |
| public function getExperimentMarkReport($experimentId) | |
| { | |
| $experimentReport = NULL; | |
| $experimentId = $this->realEscapeString ( $experimentId ); | |
| $experiment = $this->getExperimentById ( $experimentId ); | |
| $isCurrentSem = SemesterService::getInstance ()->isCurrentSemester ( $experiment->batchId, $experiment->semId ); | |
| if (! empty ( $experiment->labExperimentSubbatches )) | |
| { | |
| $subCond = "INNER JOIN subbatch_student subStu ON sa.studentID = subStu.studentID AND subStu.subbatchID IN (SELECT subbatchId FROM labExperimentSubbatch WHERE labExperimentId=$experiment->id)"; | |
| $subSelect = ", subStu.subbatchID"; | |
| } | |
| if ($isCurrentSem) | |
| { | |
| $sql = "SELECT sa.studentID, sa.studentName,sa.regNo, sa.rollNo, lStu.completionDate, col.name, col.number, | |
| colMarks.marks, col.id, col.ruleType, col.totalMark $subSelect FROM studentaccount sa | |
| $subCond | |
| INNER JOIN labExperiment lexp ON (sa.batchId = lexp.batchId AND lexp.batchId = $experiment->batchId ) | |
| LEFT JOIN labExperimentStudentDetail lStu ON (sa.studentID = lStu.studentId AND lexp.id = lStu.labExperimentId) | |
| INNER JOIN labMarkEntryTemplateColumn col ON (lexp.labMarkEntryTemplateId = col.labMarkEntryTemplateId) | |
| LEFT JOIN labStudentTemplateColumnMark colMarks ON (col.id = colMarks.labMarkEntryTemplateColumnId and sa.studentId = colMarks.studentId AND colMarks.labExperimentId=lexp.id) where lexp.id = $experiment->id order by sa.rollNo,sa.studentName,col.number | |
| "; | |
| } | |
| else | |
| { | |
| $sql = "SELECT sa.studentID, sa.studentName,sa.regNo, sa.rollNo, lStu.completionDate, col.name, col.number, | |
| colMarks.marks, col.id, col.ruleType, col.totalMark $subSelect FROM studentaccount sa LEFT JOIN failed_students fs on sa.studentID = fs.studentID | |
| $subCond | |
| INNER JOIN labExperiment lexp ON (sa.batchId = lexp.batchId AND lexp.batchId = $experiment->batchId) | |
| LEFT JOIN labExperimentStudentDetail lStu ON (sa.studentID = lStu.studentId AND lexp.id = lStu.labExperimentId) | |
| INNER JOIN labMarkEntryTemplateColumn col ON (lexp.labMarkEntryTemplateId = col.labMarkEntryTemplateId) | |
| LEFT JOIN labStudentTemplateColumnMark colMarks ON (col.id = colMarks.labMarkEntryTemplateColumnId and sa.studentId = colMarks.studentId AND colMarks.labExperimentId=lexp.id) where lexp.id = $experiment->id order by sa.rollNo,sa.studentName,col.number "; | |
| } | |
| try | |
| { | |
| $experimentReport = $this->executeQueryForList ( $sql, $this->mapper [LabServiceMapper::GET_LAB_EXPERIMENT_REPORT] ); | |
| } | |
| catch ( \Exception $e ) | |
| { | |
| throw new ProfessionalException ( $e->getCode (), $e->getMessage () ); | |
| } | |
| return $experimentReport; | |
| } | |
| /** | |
| * get student marks by experimentId | |
| * | |
| * @param int $experimentId | |
| * @throws ProfessionalException | |
| * @return object|array|\com\linways\base\util\$objectList[] | |
| */ | |
| public function getConsolidatedExperimentMarkReport($batchId, $subjectId, $semId, $studentId = NULL, $columnId = NULL) | |
| { | |
| $experimentReport = NULL; | |
| $batchId = $this->realEscapeString ( $batchId ); | |
| $subjectId = $this->realEscapeString ( $subjectId ); | |
| $semId = $this->realEscapeString ( $semId ); | |
| $studentId = $this->realEscapeString ( $studentId ); | |
| $columnId = $this->realEscapeString ( $columnId ); | |
| $studentAndColumnCondition = ""; | |
| if(!empty($studentId) && !empty($columnId)){ | |
| $studentAndColumnCondition = " AND col.id = $columnId AND sa.studentID = $studentId "; | |
| } | |
| $isCurrentSem = SemesterService::getInstance ()->isCurrentSemester ( $batchId, $semId ); | |
| $templateId = $this->getConsolidatedMarkEntryTemplateIdBySubjectId($batchId, $semId, $subjectId); | |
| if ($isCurrentSem) | |
| { | |
| $sql = "SELECT sa.studentID, sa.studentName,sa.regNo, sa.rollNo, col.name, col.number, | |
| colMarks.marks, col.id, col.ruleType, col.totalMark FROM studentaccount sa | |
| INNER JOIN labMarkEntryTemplateColumn col ON (col.labMarkEntryTemplateId = $templateId) | |
| LEFT JOIN labStudentTemplateColumnMark colMarks ON (col.id = colMarks.labMarkEntryTemplateColumnId AND colMarks.subjectId = $subjectId AND sa.studentID = colMarks.studentId and semId = ".$semId.") | |
| WHERE sa.batchId = $batchId ".$studentAndColumnCondition." order by sa.rollNo,sa.studentName,col.number | |
| "; | |
| } | |
| else | |
| { | |
| $sql = "SELECT sa.studentID, sa.studentName,sa.regNo, sa.rollNo, col.name, col.number, | |
| colMarks.marks, col.id, col.ruleType, col.totalMark FROM studentaccount sa LEFT JOIN failed_students fs on sa.studentID = fs.studentID | |
| INNER JOIN labMarkEntryTemplateColumn col ON (col.labMarkEntryTemplateId = $templateId) | |
| LEFT JOIN labStudentTemplateColumnMark colMarks ON (col.id = colMarks.labMarkEntryTemplateColumnId AND colMarks.subjectId = $subjectId AND sa.studentID = colMarks.studentId and semId = ".$semId.") | |
| WHERE sa.batchId = $batchId ".$studentAndColumnCondition." order by sa.rollNo,sa.studentName,col.number "; | |
| } | |
| try | |
| { | |
| $experimentReport = $this->executeQueryForList ( $sql, $this->mapper [LabServiceMapper::GET_LAB_EXPERIMENT_REPORT] ); | |
| } | |
| catch ( \Exception $e ) | |
| { | |
| throw new ProfessionalException ( $e->getCode (), $e->getMessage () ); | |
| } | |
| return $experimentReport; | |
| } | |
| public function populateAttendanceColumn($batchId, $subjectId, $semId, $consolidatedExperimentReport) { | |
| try | |
| { | |
| $studentAttendancePercentageList = AttendanceService::getInstance()->getStudentAttendanceDetailsForASubject($batchId, $semId, $subjectId); | |
| }catch ( \Exception $e ) | |
| { | |
| $studentAttendancePercentageList = []; | |
| } | |
| $studentAttendancePercentageList = ArrayUtil::getIndexedArrayFromAnObjectList($studentAttendancePercentageList, 'studentId'); | |
| foreach($consolidatedExperimentReport as $report){ | |
| foreach($report->columnMarks as $columnMark){ | |
| if($columnMark->ruleType == LabMarkEntryTemplateRuleType::ATTENDANCE){ | |
| $columnMark->marks = !empty($studentAttendancePercentageList[$report->studentId]->attPercent)?$studentAttendancePercentageList[$report->studentId]->attPercent:0; | |
| } | |
| } | |
| } | |
| return $consolidatedExperimentReport; | |
| } | |
| /** | |
| * Get template id for experment mark entry | |
| * | |
| * @param int $deptId | |
| * @param int $batchId | |
| * @param int $semId | |
| * @throws ProfessionalException | |
| * @return int | |
| */ | |
| public function getMarkEntryTemplate($deptId = NULL, $batchId = NULL, $semId = NULL) | |
| { | |
| $templateId = NULL; | |
| $deptId = $this->realEscapeString ( $deptId ); | |
| $batchId = $this->realEscapeString ( $batchId ); | |
| $semId = $this->realEscapeString ( $semId ); | |
| $sql = " SELECT id, name, parentExpTempId, lt.createdBy, deptId FROM labMarkEntryTemplate lt WHERE parentExpTempId IS NOT NULL AND isFacultyCreated=0"; | |
| if($deptId != NULL) | |
| { | |
| $sql.= " AND (lt.deptId= ".$deptId." or deptId = 0)"; | |
| } | |
| // if($batchId != NULL) | |
| // { | |
| // $sql.= " AND lt.batchId = ".$batchId.""; | |
| // } | |
| // if($semId != NULL) | |
| // { | |
| // $sql.= " AND lt.semId = ".$semId.""; | |
| // } | |
| try { | |
| return $this->executeQueryForList( $sql ); | |
| } | |
| catch ( \Exception $e ) | |
| { | |
| throw new ProfessionalException ( $e->getCode (), $e->getMessage () ); | |
| } | |
| } | |
| /** | |
| * Create mark entry template | |
| * @param LabMarkEntryTemplate $labMarkEntryTemplate | |
| * @throws ProfessionalException | |
| * @return object|NULL|\com\linways\base\util\$objectList[] | |
| */ | |
| public function createMarkEntryTemplate($labMarkEntryTemplate) | |
| { | |
| $labMarkEntryTemplate = $this->realEscapeObject($labMarkEntryTemplate); | |
| $sql = "INSERT INTO labMarkEntryTemplate (name, deptId, parentExpTempId, isFacultyCreated, createdBy, createdDate, updatedBy, updatedDate) VALUES ('".$labMarkEntryTemplate->name."', ".$labMarkEntryTemplate->deptId.", ".$labMarkEntryTemplate->parentTemplateId.", ".$labMarkEntryTemplate->isFacultyCreated.", ".$labMarkEntryTemplate->createdBy.", UTC_TIMESTAMP(), ".$labMarkEntryTemplate->updatedBy.", UTC_TIMESTAMP())"; | |
| try | |
| { | |
| return $this->executeQueryForObject($sql, true); | |
| } | |
| catch ( \Exception $e ) | |
| { | |
| throw new ProfessionalException ( $e->getCode (), $e->getMessage () ); | |
| } | |
| } | |
| /** | |
| * Update mark entry template | |
| * @param LabMarkEntryTemplate $labMarkEntryTemplate | |
| * @throws ProfessionalException | |
| * @return object|NULL|\com\linways\base\util\$objectList[] | |
| */ | |
| public function updateMarkEntryTemplate($labMarkEntryTemplate) | |
| { | |
| $labMarkEntryTemplate = $this->realEscapeObject($labMarkEntryTemplate); | |
| $sql = "UPDATE labMarkEntryTemplate SET name = '".$labMarkEntryTemplate->name."', deptId = ".$labMarkEntryTemplate->deptId.", parentExpTempId = ".$labMarkEntryTemplate->parentTemplateId.", updatedBy = ".$labMarkEntryTemplate->updatedBy.", updatedDate = UTC_TIMESTAMP() WHERE id = ".$labMarkEntryTemplate->id.""; | |
| try | |
| { | |
| return $this->executeQueryForObject($sql, true); | |
| } | |
| catch ( \Exception $e ) | |
| { | |
| throw new ProfessionalException ( $e->getCode (), $e->getMessage () ); | |
| } | |
| } | |
| /** | |
| * Create mark entry template batches | |
| * @param LabMarkEntryTemplateBatches $markEntryTemplateBatches | |
| * @throws ProfessionalException | |
| * @return object|NULL|\com\linways\base\util\$objectList[] | |
| */ | |
| public function createMarkEntryTemplateBatches($markEntryTemplateBatches) | |
| { | |
| $markEntryTemplateBatches = $this->realEscapeObject($markEntryTemplateBatches); | |
| foreach ($markEntryTemplateBatches->batchId as $batchId => $semIds ) | |
| { | |
| foreach ($semIds as $semId) | |
| { | |
| $values[] = "(".$markEntryTemplateBatches->parentTempId.", ".$batchId.", ".$semId.", ".$markEntryTemplateBatches->createdBy.", UTC_TIMESTAMP(), ".$markEntryTemplateBatches->updatedBy.", UTC_TIMESTAMP())"; | |
| } | |
| } | |
| $sql = "INSERT INTO labMarkEntryTemplateBatches (parentTempId, batchId, semId, createdBy, createdDate, updatedBy, updatedDate) VALUES ".implode(",", $values).""; | |
| try | |
| { | |
| return $this->executeQueryForObject($sql, true); | |
| } | |
| catch ( \Exception $e ) | |
| { | |
| throw new ProfessionalException ( $e->getCode (), $e->getMessage () ); | |
| } | |
| } | |
| /** | |
| * Delete mark entry template batches | |
| * @param int $id | |
| * @throws ProfessionalException | |
| * @return unknown | |
| */ | |
| public function deleteMarkEntryTemplate($parentTempId) | |
| { | |
| $parentTempId = $this->realEscapeObject($parentTempId); | |
| $sql = "DELETE FROM labMarkEntryTemplate WHERE parentExpTempId =".$parentTempId." OR id= ".$parentTempId.""; | |
| // delete column | |
| $sqlColumn = "DELETE clm.* FROM labMarkEntryTemplateColumn clm INNER JOIN labMarkEntryTemplate tem on tem.id = clm.labMarkEntryTemplateId WHERE tem.id = ".$parentTempId." or tem.parentExpTempId = ".$parentTempId.""; | |
| $sqlFormula = "DELETE cf FROM labMarkEntryTemplateColumnFormula cf INNER JOIN labMarkEntryTemplateColumn clm ON clm.id = cf.labMarkEntryTemplateColumnId INNER JOIN labMarkEntryTemplate tem ON tem.id = clm.labMarkEntryTemplateId WHERE tem.id = ".$parentTempId." OR tem.parentExpTempId=".$parentTempId.""; | |
| /// formula | |
| $sqlRule = "DELETE rule FROM labMArkEntryExpRule rule INNER JOIN labMarkEntryTemplateColumn clm ON clm.id = rule.labMarkEntryTemplateColumnId WHERE clm.labMarkEntryTemplateId = ".$parentTempId.""; | |
| $sqlResetExp = "UPDATE labExperiment SET labMarkEntryTemplateId = NULL WHERE labMarkEntryTemplateId IN(SELECT id FROM labMarkEntryTemplate WHERE parentExpTempId=$parentTempId)"; | |
| $delMarkSql ="DELETE lst.* FROM labMarkEntryTemplateColumn clm INNER JOIN labStudentTemplateColumnMark lst ON lst.labMarkEntryTemplateColumnId=clm.id INNER JOIN labMarkEntryTemplate tem on tem.id = clm.labMarkEntryTemplateId WHERE tem.id = ".$parentTempId." or tem.parentExpTempId = ".$parentTempId.""; | |
| try | |
| { | |
| $this->executeQuery($sqlResetExp); | |
| $this->executeQuery($sqlFormula); | |
| $this->executeQuery($sqlRule); | |
| $this->executeQuery($delMarkSql); | |
| $this->executeQuery($sqlColumn); | |
| return $this->executeQuery($sql); | |
| } | |
| catch ( \Exception $e ) | |
| { | |
| throw new ProfessionalException ( $e->getCode (), $e->getMessage () ); | |
| } | |
| } | |
| /** | |
| * Get mark entry template batches by parent tempId | |
| * @param int $tempId | |
| * @throws ProfessionalException | |
| * @return object|array|\com\linways\base\util\$objectList[] | |
| */ | |
| public function getMarkEntryTemplateBatchesByParentTempId($tempId, $deptId = NULL, $staffId = NULL) | |
| { | |
| $tempId = $this->realEscapeString($tempId); | |
| $deptId = $this->realEscapeString($deptId); | |
| $staffId = $this->realEscapeString($staffId); | |
| $hodPrefCond = NULL; | |
| if(!empty($staffId)) | |
| { | |
| $sqlHodPref = "SELECT batchIDs FROM hodPrivileges where staffID = ".$staffId.""; | |
| $hodPrefBatches = $this->executeQueryForList($sqlHodPref); | |
| if ($hodPrefBatches != NULL) | |
| { | |
| foreach($hodPrefBatches as $hodPrefBatch) | |
| { | |
| $prefBatches .= ($hodPrefBatch->batchIDs).','; | |
| } | |
| $prefBatches = substr($prefBatches, 0, -1); | |
| } | |
| } | |
| $sql = "select lt.id as templateId, name, lt.deptId, parentExpTempId, ltb.id, parentTempId, ltb.batchId, ltb.semId, ba.deptID as batchDept from labMarkEntryTemplate lt left join labMarkEntryTemplateBatches ltb on lt.parentExpTempId = ltb.parentTempId left join batches ba on ba.batchID = ltb.batchId where lt.parentExpTempId = ".$tempId." "; | |
| if(!empty($hodPrefBatches)) | |
| { | |
| $sql .= " AND (ba.deptID = ".$deptId." OR ba.batchID in (".$prefBatches."))"; | |
| } | |
| else if(!empty($deptId)) | |
| { | |
| $sql.= " and ba.deptID = ".$deptId.""; | |
| } | |
| try | |
| { | |
| return $this->executeQueryForList($sql, $this->mapper[LabServiceMapper::GET_MARK_ENTRY_TEMPLATE]); | |
| } | |
| catch ( \Exception $e ) | |
| { | |
| throw new ProfessionalException ( $e->getCode (), $e->getMessage () ); | |
| } | |
| } | |
| /** | |
| * Delete mark entry template batches | |
| * @param int $batchId | |
| * @param int $semId | |
| * @param int $parentTempId | |
| * @throws ProfessionalException | |
| * @return \com\linways\base\dto\MySqlResult | |
| */ | |
| public function deleteMarkEntryTemplateBatches($batchId, $semId, $parentTempId) | |
| { | |
| $batchId = $this->realEscapeObject($batchId); | |
| $semId = $this->realEscapeObject($semId); | |
| $parentTempId = $this->realEscapeObject($parentTempId); | |
| $sqlDelBatch = "DELETE FROM labMarkEntryTemplateBatches WHERE batchId = ".$batchId." AND semId = ".$semId." AND parentTempId=".$parentTempId.""; | |
| $sqlDelMark = "DELETE FROM labStudentTemplateColumnMark WHERE batchId = ".$batchId." AND semId = ".$semId.""; | |
| $sqlDelStd = "DELETE st FROM labExperimentStudentDetail st INNER JOIN labExperiment lb ON st.labExperimentId = lb.id WHERE lb.batchId = ".$batchId." AND lb.semId = ".$semId.""; | |
| $sqlResetExp = "UPDATE labExperiment SET labMarkEntryTemplateId = NULL WHERE batchId=$batchId AND semId=$semId"; | |
| try | |
| { | |
| $this->executeQuery($sqlDelStd); | |
| $this->executeQuery($sqlDelMark); | |
| $this->executeQuery($sqlResetExp); | |
| return $this->executeQuery($sqlDelBatch); | |
| } | |
| catch ( \Exception $e ) | |
| { | |
| throw new ProfessionalException ( $e->getCode (), $e->getMessage () ); | |
| } | |
| } | |
| /** | |
| * Get experiment template columnRules | |
| * @param int $templateId | |
| * @throws ProfessionalException | |
| * @return unknown | |
| */ | |
| public function getExperimentTemplateColumnRules($templateId) | |
| { | |
| $templateId = $this->realEscapeString($templateId); | |
| $sql = "select lt.name as templateName, parentExpTempId, ltc.id, ltc.number, ltc.name, ltc.ruleType, ltc.totalMark, ltcf.formula from labMarkEntryTemplate lt inner join labMarkEntryTemplateColumn ltc on lt.id = ltc.labMarkEntryTemplateId left join labMarkEntryTemplateColumnFormula ltcf on ltcf.labMarkEntryTemplateColumnId = ltc.id where lt.id = ".$templateId." order by ltc.id"; | |
| try | |
| { | |
| return $this->executeQueryForList($sql); | |
| } | |
| catch ( \Exception $e ) | |
| { | |
| throw new ProfessionalException ( $e->getCode (), $e->getMessage () ); | |
| } | |
| } | |
| /** | |
| * Generate coloumnNo for a template | |
| * | |
| * @param int $templateId | |
| * @return $newColumnNo | |
| */ | |
| public function generateNextColumnNoById($templateId) | |
| { | |
| $templateId = $this->realEscapeString($templateId); | |
| $newColumnNo = NULL; | |
| $sql = "select number from labMarkEntryTemplateColumn where labMarkEntryTemplateId = ".$templateId." ORDER BY id DESC limit 1"; | |
| try | |
| { | |
| $columnNo = $this->executeQueryForObject($sql)->number; | |
| $newColumnNo = $columnNo?++$columnNo:'A'; | |
| } | |
| catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $newColumnNo; | |
| } | |
| /** | |
| * Create mark entry template column | |
| * @param LabMarkEntryTemplateColumn $markEntryTemplateColumn | |
| * @throws ProfessionalException | |
| * @return object|NULL|\com\linways\base\util\$objectList[] | |
| */ | |
| public function createMarkEntryTemplateColumn($markEntryTemplateColumn, $columnRule = NULL) | |
| { | |
| $markEntryTemplateColumn = $this->realEscapeObject($markEntryTemplateColumn); | |
| $sql = "INSERT INTO labMarkEntryTemplateColumn (labMarkEntryTemplateId, number, name, ruleType, totalMark, createdBy, createdDate, updatedBy, updatedDate) VALUES (".$markEntryTemplateColumn->labMarkEntryTemplateId.", '".$markEntryTemplateColumn->number."', '".$markEntryTemplateColumn->name."', '".$markEntryTemplateColumn->ruleType."', ".$markEntryTemplateColumn->totalMark.", ".$markEntryTemplateColumn->createdBy.", UTC_TIMESTAMP(), ".$markEntryTemplateColumn->updatedBy.", UTC_TIMESTAMP())"; | |
| try { | |
| $markEntryTemplateColumn->id = $this->executeQueryForObject($sql, true); | |
| if($markEntryTemplateColumn->ruleType == LabMarkEntryTemplateRuleType::FORMULA) | |
| { | |
| $columnRule->labMarkEntryTemplateColumnId = $markEntryTemplateColumn->id; | |
| $this->createMarkEntryTemplateColumnFormula($columnRule); | |
| } | |
| else if($markEntryTemplateColumn->ruleType == LabMarkEntryTemplateRuleType::EXPERIMENT) | |
| { | |
| $columnRule->labMarkEntryTemplateColumnId = $markEntryTemplateColumn->id; | |
| $this->createMArkEntryExpRule($columnRule); | |
| } | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $markEntryTemplateColumn->id; | |
| } | |
| /** | |
| * Update mark entry template column | |
| * @param LabMarkEntryTemplateColumn $markEntryTemplateColumn | |
| * @throws ProfessionalException | |
| * @return object|NULL|\com\linways\base\util\$objectList[] | |
| */ | |
| public function updateMarkEntryTemplateColumn($markEntryTemplateColumn, $columnRule = NULL) | |
| { | |
| $markEntryTemplateColumn = $this->realEscapeObject($markEntryTemplateColumn); | |
| $sql = "UPDATE labMarkEntryTemplateColumn set labMarkEntryTemplateId = ".$markEntryTemplateColumn->labMarkEntryTemplateId.", number = '".$markEntryTemplateColumn->number."', name = '".$markEntryTemplateColumn->name."', ruleType = '".$markEntryTemplateColumn->ruleType."', totalMark = ".$markEntryTemplateColumn->totalMark.", updatedBy = ".$markEntryTemplateColumn->updatedBy.", updatedDate = UTC_TIMESTAMP() WHERE id = $markEntryTemplateColumn->id"; | |
| try { | |
| if($markEntryTemplateColumn->ruleType == LabMarkEntryTemplateRuleType::FORMULA) | |
| { | |
| $columnRule->labMarkEntryTemplateColumnId = $markEntryTemplateColumn->id; | |
| $this->updateMarkEntryTemplateColumnFormula($columnRule); | |
| } | |
| else if($markEntryTemplateColumn->ruleType == LabMarkEntryTemplateRuleType::EXPERIMENT) | |
| { | |
| $columnRule->labMarkEntryTemplateColumnId = $markEntryTemplateColumn->id; | |
| $this->updateMArkEntryExpRule($columnRule); | |
| } | |
| return $this->executeQueryForObject($sql, true); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * Update mark entry template column formula | |
| * @param LabMarkEntryTemplateColumnFormula $markEntryTemplateColumnFormula | |
| * @throws ProfessionalException | |
| * @return object|NULL|\com\linways\base\util\$objectList[] | |
| */ | |
| public function updateMarkEntryTemplateColumnFormula($markEntryTemplateColumnFormula) | |
| { | |
| $markEntryTemplateColumnFormula = $this->realEscapeObject($markEntryTemplateColumnFormula); | |
| $sql = "UPDATE labMarkEntryTemplateColumnFormula SET formula = '".$markEntryTemplateColumnFormula->formula."', updatedBy = ".$markEntryTemplateColumnFormula->updatedBy.", updatedDate = UTC_TIMESTAMP() WHERE labMarkEntryTemplateColumnId = ".$markEntryTemplateColumnFormula->labMarkEntryTemplateColumnId.""; | |
| try { | |
| return $this->executeQueryForObject($sql, true); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * Create mark entry template column formula | |
| * @param LabMarkEntryTemplateColumnFormula $markEntryTemplateColumnFormula | |
| * @throws ProfessionalException | |
| * @return object|NULL|\com\linways\base\util\$objectList[] | |
| */ | |
| public function createMarkEntryTemplateColumnFormula($markEntryTemplateColumnFormula) | |
| { | |
| $markEntryTemplateColumnFormula = $this->realEscapeObject($markEntryTemplateColumnFormula); | |
| $sql = "INSERT INTO labMarkEntryTemplateColumnFormula (labMarkEntryTemplateColumnId, formula, createdBy, createdDate, updatedBy, updatedDate) VALUES (".$markEntryTemplateColumnFormula->labMarkEntryTemplateColumnId.", '".$markEntryTemplateColumnFormula->formula."', ".$markEntryTemplateColumnFormula->createdBy.", UTC_TIMESTAMP(), ".$markEntryTemplateColumnFormula->updatedBy.", UTC_TIMESTAMP())"; | |
| try { | |
| return $this->executeQueryForObject($sql, true); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * Get experiment template by parent template id | |
| * @param int $parentTempId | |
| * @throws ProfessionalException | |
| * @return object|NULL|\com\linways\base\util\$objectList[] | |
| */ | |
| public function getExperimentTemplateByParentTemplateId($parentTempId) | |
| { | |
| $parentTempId = $this->realEscapeString($parentTempId); | |
| $sql = "SELECT * FROM labMarkEntryTemplate WHERE parentExpTempId = ".$parentTempId.""; | |
| try { | |
| return $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * Create lab mark entry experiment rule | |
| * @param LabMarkEntryExperimentRule $labMArkEntryExpRule | |
| * @throws ProfessionalException | |
| * @return object|NULL|\com\linways\base\util\$objectList[] | |
| */ | |
| public function createMArkEntryExpRule($labMArkEntryExpRule) | |
| { | |
| $labMArkEntryExpRule = $this->realEscapeObject($labMArkEntryExpRule); | |
| $sql = "INSERT INTO labMArkEntryExpRule (labMarkEntryTemplateColumnId, expNos, referredlabMarkEntryTemplateColumnId, mark, bestOf, isAvg, ordinalNo, createdBy, createdDate, updatedBy, updatedDate) VALUES (".$labMArkEntryExpRule->labMarkEntryTemplateColumnId.", ".$labMArkEntryExpRule->expNos.", '".$labMArkEntryExpRule->referredlabMarkEntryTemplateColumnId."', ".$labMArkEntryExpRule->mark.", ".$labMArkEntryExpRule->bestOf.", ".$labMArkEntryExpRule->isAvg.", ".$labMArkEntryExpRule->ordinalNo.", ".$labMArkEntryExpRule->createdBy.", UTC_TIMESTAMP(), ".$labMArkEntryExpRule->updatedBy.", UTC_TIMESTAMP())"; | |
| try { | |
| return $this->executeQueryForObject($sql, true); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * Upadte mark entry experiment rule | |
| * @param LabMarkEntryExperimentRule $labMArkEntryExpRule | |
| * @throws ProfessionalException | |
| * @return object|NULL|\com\linways\base\util\$objectList[] | |
| */ | |
| public function updateMArkEntryExpRule($labMArkEntryExpRule) | |
| { | |
| $labMArkEntryExpRule = $this->realEscapeObject($labMArkEntryExpRule); | |
| $sql = "UPDATE labMArkEntryExpRule SET expNos = ".$labMArkEntryExpRule->expNos.", referredlabMarkEntryTemplateColumnId = '".$labMArkEntryExpRule->referredlabMarkEntryTemplateColumnId."', mark = ".$labMArkEntryExpRule->mark.", bestOf = ".$labMArkEntryExpRule->bestOf.", isAvg = ".$labMArkEntryExpRule->isAvg.", ordinalNo= ".$labMArkEntryExpRule->ordinalNo.", updatedBy = ".$labMArkEntryExpRule->updatedBy.", updatedDate = UTC_TIMESTAMP() WHERE labMarkEntryTemplateColumnId = ".$labMArkEntryExpRule->labMarkEntryTemplateColumnId.""; | |
| try { | |
| return $this->executeQueryForObject($sql, true); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * Get experiment template by id | |
| * @param int $id | |
| * @throws ProfessionalException | |
| * @return object|NULL|\com\linways\base\util\$objectList[] | |
| */ | |
| public function getExperimentTemplateById($id) | |
| { | |
| $id = $this->realEscapeString($id); | |
| $sql = "select id, name, deptId, parentExpTempId from labMarkEntryTemplate where id = ".$id.""; | |
| try { | |
| return $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * | |
| * @param unknown $id | |
| * @throws ProfessionalException | |
| * @return object|NULL|\com\linways\base\util\$objectList[] | |
| */ | |
| public function deleteMarkEntryTemplateColumn($columnId) | |
| { | |
| $id = $this->realEscapeString($columnId); | |
| $sql = "DELETE FROM labMarkEntryTemplateColumn WHERE id = ".$columnId.""; | |
| try { | |
| $dependentColumns = $this->getDependentColumnsByColumnId($columnId); | |
| $dependentColumnsStr = ""; | |
| if(!empty($dependentColumns)) | |
| { | |
| foreach ($dependentColumns as $dependentColumn) | |
| { | |
| $dependentColumns_arr[]= $dependentColumn->number; | |
| } | |
| } | |
| $referredColumns = $this->getMarkEntryTemplateColumnByReferredColumnId($columnId); | |
| if(!empty($referredColumns)) | |
| { | |
| foreach ($referredColumns as $referredColumn) | |
| { | |
| $dependentColumns_arr[] = $referredColumn->number; | |
| } | |
| } | |
| if(count($dependentColumns_arr)) | |
| { | |
| $dependentColumnsStr = implode(",", $dependentColumns_arr); | |
| throw new ProfessionalException("LAB_MARK_ENTRY_TEMPLATE_DEPENDENT_COLUMN", "Column can't be deleted !!. Please remove the reference(s) from following column(s) [".$dependentColumnsStr."]"); | |
| } | |
| $column = $this->getExperimentMarkTemplateColumnById($columnId); | |
| $sqlMarkDelete = "DELETE FROM labStudentTemplateColumnMark WHERE labMarkEntryTemplateColumnId = $columnId"; | |
| $this->executeQueryForObject($sqlMarkDelete); | |
| switch ($column->ruleType) | |
| { | |
| case LabMarkEntryTemplateRuleType::FORMULA : $formula = $this->getExperimentFormulaRule($columnId); | |
| $this->deleteMarkEntryTemplateFormula($formula->id); | |
| break; | |
| case LabMarkEntryTemplateRuleType::EXPERIMENT : $expRule = $this->getExperimentRule($columnId); | |
| $this->deleteMarkEntryTemplateExperimentRule($expRule->id); | |
| break; | |
| } | |
| return $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * Get mark entry template columns where the given column is referred | |
| * @param int $columnId | |
| * @throws ProfessionalException | |
| * @return object|array|\com\linways\base\util\$objectList[] | |
| */ | |
| public function getMarkEntryTemplateColumnByReferredColumnId($columnId) | |
| { | |
| $columnId = $this->realEscapeString($columnId); | |
| $sql = "select clm.id, clm.number from labMArkEntryExpRule er inner join labMarkEntryTemplateColumn clm on er.labMarkEntryTemplateColumnId = clm.id where er.referredlabMarkEntryTemplateColumnId = ".$columnId.""; | |
| try { | |
| return $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * Delete lab mark entry template column formula | |
| * @param int $id | |
| * @throws ProfessionalException | |
| * @return \com\linways\base\dto\MySqlResult | |
| */ | |
| public function deleteMarkEntryTemplateFormula($id) | |
| { | |
| $id = $this->realEscapeString($id); | |
| $sql = "DELETE FROM labMarkEntryTemplateColumnFormula WHERE id = ".$id.""; | |
| try { | |
| return $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * Delete mark entry template experiment rule | |
| * @param int $id | |
| * @throws ProfessionalException | |
| * @return \com\linways\base\dto\MySqlResult | |
| */ | |
| public function deleteMarkEntryTemplateExperimentRule($id) | |
| { | |
| $id = $this->realEscapeString($id); | |
| $sql = "DELETE FROM labMArkEntryExpRule WHERE id = ".$id.""; | |
| try { | |
| return $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * Update template assigned to experiment | |
| * @param int $templateId | |
| * @param int $experimentId | |
| * @throws ProfessionalException | |
| * @return \com\linways\base\dto\MySqlResult | |
| */ | |
| public function updateExperimentTemplate($templateId, $experimentId) | |
| { | |
| $templateId = $this->realEscapeString($templateId); | |
| $experimentId = $this->realEscapeString($experimentId); | |
| $sql = "UPDATE labExperiment SET labMarkEntryTemplateId = ".$templateId." WHERE id = ".$experimentId.""; | |
| try { | |
| return $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * Get all experiment template Details | |
| * @throws ProfessionalException | |
| * @return object|array|\com\linways\base\util\$objectList[] | |
| */ | |
| public function getAllExperimentTemplateDetails() | |
| { | |
| $sql = "select lt.id as templateId, name, deptId, parentExpTempId, ltb.id, parentTempId, batchId, semId from labMarkEntryTemplate lt left join labMarkEntryTemplateBatches ltb on lt.parentExpTempId = ltb.parentTempId WHERE lt.parentExpTempId is not null"; | |
| try | |
| { | |
| return $this->executeQueryForList($sql, $this->mapper[LabServiceMapper::GET_MARK_ENTRY_TEMPLATE]); | |
| } | |
| catch ( \Exception $e ) | |
| { | |
| throw new ProfessionalException ( $e->getCode (), $e->getMessage () ); | |
| } | |
| } | |
| /** | |
| * Method to get lab experiment rules for normalisation | |
| * | |
| * @param [type] $ruleId | |
| * @return void | |
| */ | |
| public function getLabExperimentRulesByRuleId($ruleId){ | |
| $ruleId = $this->realEscapeString($ruleId); | |
| $sql = ""; | |
| $ruleList = []; | |
| $sql = "SELECT rule_id as ruleId, consolidated_marks_column_id as consolidatedMarksColumnId, normalise_to_mark as normaliseToMark, label FROM normalization_rule3_lab_experiment WHERE rule_id = '$ruleId'"; | |
| try | |
| { | |
| $ruleList = $this->executeQueryForList($sql); | |
| } | |
| catch ( \Exception $e ) | |
| { | |
| throw new ProfessionalException ( $e->getCode (), $e->getMessage () ); | |
| } | |
| return $ruleList; | |
| } | |
| public function updateLabExperimentRules($labExperimentRules, $ruleId, $createdBy = 'NULL', $includeLabRule = false){ | |
| $labExperimentRules = $this->realEscapeArray($labExperimentRules); | |
| $ruleId = $this->realEscapeArray($ruleId); | |
| $createdBy = $this->realEscapeString($createdBy); | |
| if(empty($createdBy)){ | |
| $createdBy = $_SESSION['staffID']; | |
| } | |
| $sql = "DELETE FROM normalization_rule3_lab_experiment WHERE rule_id = '$ruleId';"; | |
| try | |
| { | |
| $this->executeQuery($sql); | |
| } | |
| catch ( \Exception $e ) | |
| { | |
| throw new ProfessionalException ( $e->getCode (), $e->getMessage () ); | |
| } | |
| //stop execution here if we donot want to include lab rule | |
| if(!$includeLabRule){ | |
| return null; | |
| } | |
| $sql = "INSERT INTO `normalization_rule3_lab_experiment` (`rule_id`, `consolidated_marks_column_id`, `normalise_to_mark`, `label`, `created_by`, `updated_by`, `created_date`, `updated_date`) VALUES "; | |
| $values = []; | |
| foreach($labExperimentRules as $rule){ | |
| $rule = (object)$rule; | |
| //maximum 3 chars allowed for label | |
| $rule->label = ucfirst(substr($rule->label,0,3)); | |
| $values[] = "($ruleId,$rule->consolidatedColumnId,$rule->normaliseToMark,'$rule->label',$createdBy,$createdBy,UTC_TIMESTAMP(),UTC_TIMESTAMP())"; | |
| } | |
| $sql .= implode(',',$values); | |
| try{ | |
| $this->executeQuery($sql); | |
| }catch(\Exception $e){ | |
| throw new ProfessionalException ( $e->getCode (), $e->getMessage () ); | |
| } | |
| } | |
| public function getLabSettingsByDeptId($deptId) | |
| { | |
| $deptId = $this->realEscapeString($deptId); | |
| $sql ="SELECT ls.id, b.batchID,b.batchName,b.semID,b.isPassOut,b.batchDesc, ls.canFacultyCreate, ls.firstPriorityTemplate from batches b LEFT JOIN labMarkEntryTemplateSettings ls ON ls.batchId=b.batchID where b.isPassOut = 0 AND b.deptID='$deptId'"; | |
| try{ | |
| return $this->executeQueryForList($sql); | |
| }catch(\Exception $e){ | |
| throw new ProfessionalException ($e->getCode (), $e->getMessage ()); | |
| } | |
| } | |
| public function deleteSettingsByTempId($templateId) | |
| { | |
| $sql ="DELETE FROM `labMarkEntryTemplateSubjects` WHERE parentTempId=$templateId"; | |
| try{ | |
| return $this->executeQuery($sql); | |
| }catch(\Exception $e){ | |
| throw new ProfessionalException ($e->getCode (), $e->getMessage ()); | |
| } | |
| } | |
| public function createLabTemplateSettings(LabMarkEntryTemplateSettings $labMarkEntryTemplateSettings) | |
| { | |
| $sql ="INSERT INTO `labMarkEntryTemplateSettings` (`batchId`, `canFacultyCreate`, `firstPriorityTemplate`, `createdBy`, `createdDate`, `updatedBy`, `updatedDate`) VALUES ('$labMarkEntryTemplateSettings->batchId', '$labMarkEntryTemplateSettings->canFacultyCreate', '$labMarkEntryTemplateSettings->firstPriorityTemplate', '$labMarkEntryTemplateSettings->createdBy', utc_timestamp(), '$labMarkEntryTemplateSettings->updatedBy', utc_timestamp())"; | |
| try{ | |
| return $this->executeQuery($sql,true); | |
| }catch(\Exception $e){ | |
| throw new ProfessionalException ($e->getCode (), $e->getMessage ()); | |
| } | |
| } | |
| public function updateLabTemplateSettings(LabMarkEntryTemplateSettings $labMarkEntryTemplateSettings) | |
| { | |
| $sql ="UPDATE `labMarkEntryTemplateSettings` SET `canFacultyCreate`='$labMarkEntryTemplateSettings->canFacultyCreate', `firstPriorityTemplate`='$labMarkEntryTemplateSettings->firstPriorityTemplate', `updatedBy`='$labMarkEntryTemplateSettings->updatedBy', `updatedDate`=utc_timestamp() WHERE batchId='$labMarkEntryTemplateSettings->batchId'"; | |
| try{ | |
| return $this->executeQuery($sql,true); | |
| }catch(\Exception $e){ | |
| throw new ProfessionalException ($e->getCode (), $e->getMessage ()); | |
| } | |
| } | |
| public function canFacultyCreateLabTemplate($batchId) | |
| { | |
| $canFacultyCreate=0; | |
| $sql ="SELECT canFacultyCreate from labMarkEntryTemplateSettings WHERE batchId='$batchId'"; | |
| try{ | |
| $canFacultyCreate = $this->executeQueryForObject($sql)->canFacultyCreate; | |
| }catch(\Exception $e){ | |
| throw new ProfessionalException ($e->getCode (), $e->getMessage ()); | |
| } | |
| return $canFacultyCreate?true:false; | |
| } | |
| /** | |
| * Get template id for experment mark entry | |
| * | |
| * @param int $deptId | |
| * @param int $batchId | |
| * @param int $semId | |
| * @throws ProfessionalException | |
| * @return int | |
| */ | |
| public function getMarkEntryTemplateBySubjectId($subjectId, $batchId, $semId) | |
| { | |
| $templateId = NULL; | |
| $subjectId = $this->realEscapeString ( $subjectId ); | |
| $batchId = $this->realEscapeString ( $batchId ); | |
| $semId = $this->realEscapeString ( $semId ); | |
| $sql = "SELECT lt.id, name, parentExpTempId, lt.createdBy, deptId FROM labMarkEntryTemplate lt INNER JOIN labMarkEntryTemplateSubjects lts ON lts.parentTempId=lt.parentExpTempId WHERE parentExpTempId IS NOT NULL AND lts.subjectId='$subjectId' AND lts.batchId='$batchId' AND lts.semId='$semId'"; | |
| try { | |
| return $this->executeQueryForObject( $sql ); | |
| } | |
| catch ( \Exception $e ) | |
| { | |
| throw new ProfessionalException ( $e->getCode (), $e->getMessage () ); | |
| } | |
| } | |
| public function createMarkEntryTemplateSubjects(LabMarkEntryTemplateSubjects $labMarkEntryTemplateSubjects) | |
| { | |
| $sql="INSERT INTO `labMarkEntryTemplateSubjects` (`parentTempId`, `batchId`, `semId`, `subjectId`, `createdBy`, `createdDate`, `updatedBy`, `updatedDate`) VALUES ('$labMarkEntryTemplateSubjects->parentTempId', '$labMarkEntryTemplateSubjects->batchId', '$labMarkEntryTemplateSubjects->semId', '$labMarkEntryTemplateSubjects->subjectId', '$labMarkEntryTemplateSubjects->createdBy', utc_timestamp(), '$labMarkEntryTemplateSubjects->updatedBy', utc_timestamp())"; | |
| try{ | |
| return $this->executeQuery($sql); | |
| }catch(\Exception $e){ | |
| throw new ProfessionalException ($e->getCode (), $e->getMessage ()); | |
| } | |
| } | |
| public function getLabTemplate($batchId) | |
| { | |
| $firstPriorityTemplate='BATCH'; | |
| $sql ="SELECT firstPriorityTemplate from labMarkEntryTemplateSettings WHERE batchId='$batchId'"; | |
| try{ | |
| $firstPriorityTemplate = $this->executeQueryForObject($sql)->firstPriorityTemplate; | |
| }catch(\Exception $e){ | |
| throw new ProfessionalException ($e->getCode (), $e->getMessage ()); | |
| } | |
| return $firstPriorityTemplate; | |
| } | |
| /** | |
| * Get template id for experment mark entry | |
| * | |
| * @param int $deptId | |
| * @param int $batchId | |
| * @param int $semId | |
| * @throws ProfessionalException | |
| * @return int | |
| */ | |
| public function getMarkEntryTemplateIdBySubject($subjectId, $batchId, $semId) | |
| { | |
| $templateId = NULL; | |
| $batchId = $this->realEscapeString ( $batchId ); | |
| $semId = $this->realEscapeString ( $semId ); | |
| $subjectId = $this->realEscapeString ( $subjectId ); | |
| $labTempDef = $this->getLabTemplate($batchId); | |
| $sql_faculty = " SELECT lt.id FROM labMarkEntryTemplate lt inner join labMarkEntryTemplateSubjects lba on lt.parentExpTempId = lba.parentTempId WHERE lba.batchId = ".$batchId." AND lba.semId = ".$semId." AND lba.subjectId=$subjectId AND lt.parentExpTempId IS not NULL"; | |
| $sql_batch = " SELECT lt.id FROM labMarkEntryTemplate lt inner join labMarkEntryTemplateBatches lba on lt.parentExpTempId = lba.parentTempId WHERE batchID = ".$batchId." AND semID = ".$semId." AND lt.parentExpTempId IS not NULL;"; | |
| try | |
| { | |
| if($labTempDef=='FACULTY') | |
| { | |
| $templateId = $this->executeQueryForObject ($sql_faculty)->id; | |
| if(!$templateId) | |
| { | |
| $templateId = $this->executeQueryForObject ($sql_batch)->id; | |
| } | |
| } | |
| else | |
| { | |
| $templateId = $this->executeQueryForObject ($sql_batch)->id; | |
| if(!$templateId) | |
| { | |
| $templateId = $this->executeQueryForObject ($sql_faculty)->id; | |
| } | |
| } | |
| } | |
| catch ( \Exception $e ) | |
| { | |
| throw new ProfessionalException ( $e->getCode (), $e->getMessage () ); | |
| } | |
| return $templateId; | |
| } | |
| /** | |
| * Get template id for consolidated experment mark entry | |
| * | |
| * @param int $deptId | |
| * @param int $batchId | |
| * @param int $semId | |
| * @throws ProfessionalException | |
| * @return int | |
| */ | |
| public function getConsolidatedMarkEntryTemplateIdBySubjectId($batchId, $semId, $subjectId=null) | |
| { | |
| $templateId = NULL; | |
| $batchId = $this->realEscapeString ( $batchId ); | |
| $semId = $this->realEscapeString ( $semId ); | |
| $subjectId = $this->realEscapeString ( $subjectId ); | |
| $labTempDef = $this->getLabTemplate($batchId); | |
| $sql_faculty = "SELECT lt.id FROM labMarkEntryTemplate lt inner join labMarkEntryTemplateSubjects lba on lt.id = lba.parentTempId WHERE lba.batchId = ".$batchId." AND lba.semId = ".$semId." AND lba.subjectId=$subjectId AND lt.parentExpTempId IS NULL "; | |
| $sql_batch = "SELECT lt.id FROM labMarkEntryTemplate lt inner join labMarkEntryTemplateBatches lba on lt.id = lba.parentTempId WHERE lba.batchId = ".$batchId." AND lba.semId = ".$semId." AND lt.parentExpTempId IS NULL "; | |
| try | |
| { | |
| if($labTempDef=='FACULTY') | |
| { | |
| $templateId = $this->executeQueryForObject ($sql_faculty)->id; | |
| if(!$templateId) | |
| { | |
| $templateId = $this->executeQueryForObject ($sql_batch)->id; | |
| } | |
| } | |
| else | |
| { | |
| $templateId = $this->executeQueryForObject ($sql_batch)->id; | |
| if(!$templateId) | |
| { | |
| $templateId = $this->executeQueryForObject ($sql_faculty)->id; | |
| } | |
| } | |
| } | |
| catch ( \Exception $e ) | |
| { | |
| throw new ProfessionalException ( $e->getCode (), $e->getMessage () ); | |
| } | |
| return $templateId; | |
| } | |
| public function isExperimentCreated($batchId, $semId) | |
| { | |
| $created=false; | |
| $sql="select count(le.id) as totalExp from labExperiment le INNER JOIN labMarkEntryTemplate lmt ON lmt.id=le.labMarkEntryTemplateId WHERE le.batchId=$batchId AND le.semId=$semId"; | |
| try | |
| { | |
| $count = $this->executeQueryForObject($sql)->totalExp; | |
| if($count>0) | |
| { | |
| $created=true; | |
| } | |
| } | |
| catch ( \Exception $e ) | |
| { | |
| throw new ProfessionalException ( $e->getCode (), $e->getMessage () ); | |
| } | |
| return $created; | |
| } | |
| } | |
| ?> |