Code Coverage |
||||||||||
Classes and Traits |
Functions and Methods |
Lines |
||||||||
| Total | |
0.00% |
0 / 1 |
|
0.00% |
0 / 29 |
CRAP | |
0.00% |
0 / 2524 |
| MigartionsService | |
0.00% |
0 / 1 |
|
0.00% |
0 / 29 |
23870.00 | |
0.00% |
0 / 2524 |
| __construct | |
0.00% |
0 / 1 |
2.00 | |
0.00% |
0 / 1 |
|||
| __clone | |
0.00% |
0 / 1 |
2.00 | |
0.00% |
0 / 1 |
|||
| getInstance | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 4 |
|||
| getMigrationTables | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 19 |
|||
| updateMigrationTables | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 18 |
|||
| migrationTable | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 15 |
|||
| migrate_ec_exam_registration | |
0.00% |
0 / 1 |
272.00 | |
0.00% |
0 / 357 |
|||
| migrate_ec_exam_registration_batch | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 124 |
|||
| migrate_ec_exam_registration_retest_mapping | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 66 |
|||
| migrate_ec_exam_registration_subject | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 16 |
|||
| generate_ec_exam_registration_subject | |
0.00% |
0 / 1 |
2756.00 | |
0.00% |
0 / 587 |
|||
| migrate_am_assessment | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 187 |
|||
| migrate_ec_student_assessment_registration | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 143 |
|||
| migrate_oe_student_total_mark | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 118 |
|||
| migrate_valuation_method | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 164 |
|||
| migrate_grade_scheme | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 124 |
|||
| migrate_grade | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 72 |
|||
| migrate_common | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 118 |
|||
| migrate_internal_marks | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 48 |
|||
| migrate_consolidated_mark | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 35 |
|||
| validateAndGenerateConsolidatedMarks | |
0.00% |
0 / 1 |
72.00 | |
0.00% |
0 / 56 |
|||
| generateConsolidatedMarks | |
0.00% |
0 / 1 |
420.00 | |
0.00% |
0 / 100 |
|||
| upsertStudentCourseConsolidatedMarkDetails | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 22 |
|||
| upsertStudentSemesterConsolidatedMarkDetails | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 25 |
|||
| upsertStudentSubjectConsolidatedMarkDetails | |
0.00% |
0 / 1 |
90.00 | |
0.00% |
0 / 43 |
|||
| getPublishedSupplyExamRegistrationByStudentId | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 25 |
|||
| getPublishedRegularExamRegistrationOfFailedStudent | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 9 |
|||
| getPublishedRegularExamRegistration | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 9 |
|||
| updateMigrationTableLogs | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 18 |
|||
| <?php | |
| namespace com\linways\core\ams\professional\service\examcontroller\migration; | |
| use com\linways\core\ams\professional\service\BaseService; | |
| use com\linways\core\ams\professional\exception\ProfessionalException; | |
| use com\linways\core\ams\professional\service\StudentService; | |
| use com\linways\core\ams\professional\service\examcontroller\falsenumber\ConsolidatedMarkListService; | |
| use com\linways\core\ams\professional\service\ExamService; | |
| use com\linways\core\ams\professional\service\examcontroller\migration\finalMarkList\ConsolidatedMarkReportService; | |
| use com\linways\core\ams\professional\request\examcontroller\ConsolidatedMarkReportRequest; | |
| use com\linways\core\ams\professional\request\GenerateConsolidatedMarkRequest; | |
| use com\linways\core\ams\professional\constant\StatusConstants; | |
| use com\linways\core\ams\professional\dto\examcontroller\exam\ConsolidatedMarkGenerateStatus; | |
| use com\linways\core\ams\professional\queue\AMSTaskQueue; | |
| use com\linways\base\util\SecurityUtils; | |
| use com\linways\fee\core\request\TemplateForOtherModulesRequest; | |
| use com\linways\fee\core\service\TemplateService; | |
| use com\linways\core\ams\professional\service\BatchService; | |
| class MigartionsService extends BaseService | |
| { | |
| private static $_instance = null; | |
| private $mapper = []; | |
| /// Condition 2 - Locked down the constructor | |
| private function __construct() { | |
| } | |
| // 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 migration tables | |
| */ | |
| public function getMigrationTables() | |
| { | |
| // $request = $this->realEscapeObject(); | |
| $sql = ""; | |
| $sql = "SELECT | |
| id, | |
| table_name AS name, | |
| `log` | |
| FROM | |
| new_system_migration nsm | |
| WHERE | |
| properties->>'$.display' = 'VISIBLE'"; | |
| try { | |
| $tables = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getMessage(), $e->getCode()); | |
| } | |
| array_map(function($table){ | |
| $table->displayName = ucwords(str_replace("_"," ",$table->name)); | |
| }, $tables); | |
| return $tables; | |
| } | |
| /** | |
| * update migration tables | |
| */ | |
| public function updateMigrationTables($logData, $table) | |
| { | |
| $logData = $this->realEscapeObject($logData); | |
| $table = $this->realEscapeString($table); | |
| try { | |
| $staffId = $_SESSION['adminID']; | |
| $logDataJSON = stripslashes(json_encode($logData)); | |
| $sql = "UPDATE | |
| new_system_migration | |
| SET | |
| `log` = JSON_ARRAY_APPEND(`log`, '$', CAST('$logDataJSON' AS JSON)), | |
| updated_by = $staffId | |
| WHERE | |
| table_name = '$table'"; | |
| $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getMessage(), $e->getCode()); | |
| } | |
| } | |
| /** | |
| * Migrate table | |
| */ | |
| public function migrationTable($tableName) | |
| { | |
| $request = $this->realEscapeString($tableName); | |
| $functionName = "migrate_$tableName"; | |
| try { | |
| if (method_exists($this, $functionName)) { | |
| $migration = $this->{$functionName}(); | |
| } | |
| else{ | |
| throw new ProfessionalException("Table migration not defined", ProfessionalException::INVALID_ENTITY); | |
| } | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getMessage(), $e->getCode()); | |
| } | |
| return $migration; | |
| } | |
| /** | |
| * Migrate table | |
| */ | |
| public function migrate_ec_exam_registration() | |
| { | |
| try { | |
| $staffId = $_SESSION['adminID']; | |
| $migrationDetails = new \stdClass(); | |
| $migrationDetails->startTime = time(); | |
| $migrationDetails->tables = ["exam_registration"]; | |
| $query = "INSERT IGNORE INTO ec_exam_registration(id,name,`type`,properties,created_by,fees_properties) | |
| SELECT | |
| CONCAT(LEFT(UUID(), 8), RIGHT(UUID(), 9)), | |
| examregName, | |
| 'REGULAR', | |
| CONCAT('{', | |
| '\"publish\": true', | |
| ',\"examDate\": \"',IF(er.examDate IS NOT NULL,er.examDate,''),'\"', | |
| ',\"examYear\": ',IF(er.examYear IS NOT NULL,er.examYear,'\"\"'), | |
| ',\"examMonth\": ',IF(er.examMonth IS NOT NULL,er.examMonth,'\"\"'), | |
| ',\"description\": \"',IF(er.examregDesc IS NOT NULL,REGEXP_REPLACE(er.examregDesc,'\\n|\\t',''),''),'\"', | |
| ',\"shortTermCourse\": false', | |
| ',\"allowNotification\":', IF(er.allowNotification=1,'true','false'), | |
| ',\"displayProfileImage\": ',IF(er.pimage=1,'true','false'), | |
| ',\"attendanceClosingDate\": \"',IF(er.attClosingDate IS NOT NULL,er.attClosingDate,''),'\"', | |
| ',\"displaySignatureImage\":', IF(er.simage=1,'true','false'), | |
| ',\"registrationStartDate\": \"',IF(er.regStartDate IS NOT NULL,er.regStartDate,''),'\"', | |
| ',\"minimumAttendancePercentage\": ',IF(er.min_att_percent IS NOT NULL,er.min_att_percent,0), | |
| ',\"registrationWithoutFineEndDate\": \"',IF(er.rgstnWithoutFine IS NOT NULL,er.rgstnWithoutFine,''),'\"', | |
| ',\"examregId\": ',er.examregID, | |
| '}'), | |
| $staffId, | |
| JSON_OBJECT() | |
| FROM | |
| exam_registration er WHERE er.ec_exam_registration_id IS NULL"; | |
| $result = $this->executeQuery($query); | |
| $this->executeQuery("UPDATE | |
| exam_registration er | |
| INNER JOIN ec_exam_registration eer ON | |
| eer.properties->>'$.examregId' = er.examregID | |
| AND eer.`type` = 'REGULAR' SET | |
| er.ec_exam_registration_id = eer.id"); | |
| // Fees Migratiion - Common Fees | |
| $commonFeeQuery = "SELECT | |
| er.examRegID,er.examregName, CONCAT(er.examregName,' ',eft.examfeesName) AS examfeesName, erf.examfeesAmount | |
| FROM | |
| exam_registration er | |
| INNER JOIN | |
| exam_registration_fees erf ON er.examregID = erf.examregID | |
| INNER JOIN | |
| exam_feestype eft ON erf.examfeesID = eft.examfeesID | |
| WHERE eft.everySubject=0;"; | |
| $examRegistrations = $this->executeQueryForList($commonFeeQuery); | |
| $examRegistrationDetails = []; | |
| foreach ($examRegistrations as $examRegistration) { | |
| if($examRegistration->examfeesAmount > 0) { | |
| $examRegistration->examfeesAmount = (int) $examRegistration->examfeesAmount; | |
| if(!isset($examRegistrationDetails[$examRegistration->examregName])) { | |
| $examRegistrationDetails[$examRegistration->examregName]['feeHeads'] = []; | |
| } | |
| $examRegistrationDetails[$examRegistration->examregName]['feeHeads'] [] = ["name" => $examRegistration->examfeesName,"amount" => $examRegistration->examfeesAmount]; | |
| $examRegistrationDetails[$examRegistration->examregName]['id'] = $examRegistration->examRegID; | |
| } | |
| } | |
| $paymentDateOperandQuery = "SELECT id FROM fm_operand where name='PAYMENT_DATE' and type='FINE'"; | |
| $paymentDateOperand = $this->executeQueryForObject($paymentDateOperandQuery); | |
| foreach($examRegistrationDetails as $examRegistrationName => $examRegistration) { | |
| $templateForOtherModuleRequest = new TemplateForOtherModulesRequest(); | |
| $templateForOtherModuleRequest->name = $examRegistrationName." Common Fee"; | |
| $templateForOtherModuleRequest->feeHeads = $examRegistration['feeHeads']; | |
| $templateForOtherModuleRequest->module = "EXAM_CONTROLLER"; | |
| $fineQuery = "SELECT CONCAT(er.examregName,' ',eft.examfineName) AS examfineName,erf.examfineAmount,erf.startDate,erf.lastDate from exam_registration_fine erf inner join exam_finetype eft on erf.examfineID=eft.examfineID INNER JOIN exam_registration er ON er.examregID=erf.examregID where erf.examregID=".$examRegistration['id'].";"; | |
| $fines = $this->executeQueryForList($fineQuery); | |
| foreach ($fines as $fine ) { | |
| $templateForOtherModuleRequest->fineHeads [] = ["name" => $fine->examfineName,"rules" => [(object) [ | |
| "amount" => $fine->examfineAmount, | |
| "condition"=>"AND", | |
| "id"=>"rule-".rand(), | |
| "innerRules" => [(object) [ | |
| "id"=>"-".rand(), | |
| "operandId"=>$paymentDateOperand->id, | |
| "operator"=>"BTW", | |
| "value"=>[date('d-m-Y',strtotime($fine->startDate)),date('d-m-Y',strtotime($fine->lastDate))] | |
| ]] | |
| ]]]; | |
| } | |
| $examRegCommonFees = TemplateService::getInstance()->saveTemplateForOtherModules($templateForOtherModuleRequest); | |
| $this->executeQuery("UPDATE | |
| ec_exam_registration eer | |
| INNER JOIN exam_registration er ON | |
| er.ec_exam_registration_id = eer.id SET | |
| eer.properties = JSON_SET( eer.properties, | |
| '$.templateId', '$examRegCommonFees->id' | |
| ) | |
| WHERE | |
| er.examregID = ".$examRegistration['id']); | |
| } | |
| $this->executeQuery("UPDATE | |
| ec_exam_registration eer | |
| SET | |
| eer.properties = JSON_REMOVE( | |
| eer.properties, | |
| '$.examregId' | |
| ) | |
| WHERE | |
| eer.`type` = 'REGULAR'"); | |
| $migrationDetails->endTime = time(); | |
| $migrationDetails->status = "SUCCESS"; | |
| $migrationDetails->recordCount = $result->sqlResult->num_rows; | |
| $this->updateMigrationTables($migrationDetails, "ec_exam_registration"); | |
| // Supply | |
| $migrationDetails = new \stdClass(); | |
| $migrationDetails->startTime = time(); | |
| $migrationDetails->tables = ["exam_supplementary"]; | |
| $query = "INSERT IGNORE INTO ec_exam_registration(id,name,`type`,properties,created_by,fees_properties) | |
| SELECT | |
| CONCAT(LEFT(UUID(), 8), RIGHT(UUID(), 9)), | |
| supplyDesc, | |
| 'SUPPLEMENTARY', | |
| JSON_OBJECT( | |
| \"publish\", true, | |
| \"examDate\",IF(er.examDate IS NOT NULL,er.examDate,''), | |
| \"examYear\",IF(er.examYear IS NOT NULL,er.examYear,'\"\"'), | |
| \"examMonth\",IF(er.examMonth IS NOT NULL,er.examMonth,'\"\"'), | |
| \"subjectLimit\",IF(er.subjectLimit =0,'\"ALL\"',er.subjectLimit), | |
| \"academicTermId\",IF(at2.id IS NOT NULL,at2.id,'\"\"'), | |
| \"valuationStartDate\",IF(er.valuation_startDate IS NOT NULL,er.valuation_startDate,''), | |
| \"valuationEndDate\",IF(er.valuation_endDate IS NOT NULL,er.valuation_endDate,''), | |
| \"patternId\",IF(er.patternID IS NOT NULL,er.patternID,''), | |
| \"enableHallticket\",IF(er.enable_hlticket=1,true,false), | |
| \"publishFromDate\",IF(er.publishFromDate IS NOT NULL,er.publishFromDate,''), | |
| \"publishToDate\",IF(er.publishToDate IS NOT NULL,er.publishToDate,''), | |
| \"criteriaDuringSpecialExam\",IF(er.considerFlag=1,'supply','absent'), | |
| \"description\",IF(er.supplyDesc IS NOT NULL,REGEXP_REPLACE(er.supplyDesc,'\\n|\\t',''),''), | |
| \"allowNotification\",IF(er.allowNotification=1,true,false), | |
| \"isSpecialExam\",IF(er.isSpecialExam=1,true,false), | |
| \"displayProfileImage\",IF(er.pimage=1,true,false), | |
| \"displaySignatureImage\",IF(er.simage=1,true,false), | |
| \"registrationStartDate\",IF(er.startDate IS NOT NULL,er.startDate,''), | |
| \"registrationWithoutFineEndDate\",IF(er.endDate IS NOT NULL,er.endDate,''), | |
| \"examregId\",er.id | |
| ), | |
| $staffId, | |
| CONCAT('{', '}') | |
| FROM | |
| exam_supplementary er | |
| INNER JOIN semesters s ON s.semID = er.semID | |
| INNER JOIN academic_term at2 ON at2.name = s.semName | |
| WHERE er.ec_exam_registration_id IS NULL"; | |
| $result = $this->executeQuery($query); | |
| $this->executeQuery("UPDATE | |
| exam_supplementary er | |
| INNER JOIN ec_exam_registration eer ON | |
| eer.properties->>'$.examregId' = er.id | |
| AND eer.`type` = 'SUPPLEMENTARY' SET | |
| er.ec_exam_registration_id = eer.id"); | |
| // Fees Migratiion - Common Fees | |
| $commonFeeQuery = "SELECT DISTINCT | |
| es.id, | |
| es.supplyDesc, | |
| sief.supply_feesAmount AS examfeesAmount, | |
| eft.examfeesName | |
| FROM | |
| exam_supplementary es | |
| INNER JOIN | |
| supply_improve_exam_fees sief ON es.id = sief.exam_supplementary_id | |
| INNER JOIN | |
| exam_feestype eft ON sief.examfeesID = eft.examfeesID | |
| WHERE | |
| eft.everySubject = 0"; | |
| $examSupplementarys = $this->executeQueryForList($commonFeeQuery); | |
| $examSupplementaryDetails = []; | |
| foreach ($examSupplementarys as $examSupplementary) { | |
| $examSupplementary->supplyDesc = trim($examSupplementary->supplyDesc); | |
| if($examSupplementary->examfeesAmount > 0) { | |
| $examSupplementary->examfeesAmount = (int) $examSupplementary->examfeesAmount; | |
| if(!isset($examSupplementaryDetails[$examSupplementary->supplyDesc])) { | |
| $examSupplementaryDetails[$examSupplementary->supplyDesc]['feeHeads'] = []; | |
| } | |
| $examSupplementaryDetails[$examSupplementary->supplyDesc]['feeHeads'] [] = ["name" => $examSupplementary->supplyDesc." ".trim($examSupplementary->examfeesName),"amount" => $examSupplementary->examfeesAmount]; | |
| $examSupplementaryDetails[$examSupplementary->supplyDesc]['id'] = $examSupplementary->id; | |
| } | |
| } | |
| $paymentDateOperandQuery = "SELECT id FROM fm_operand where name='PAYMENT_DATE' and type='FINE'"; | |
| $paymentDateOperand = $this->executeQueryForObject($paymentDateOperandQuery); | |
| foreach($examSupplementaryDetails as $examSupplementaryName => $examSupplementary) { | |
| $templateForOtherModuleRequest = new TemplateForOtherModulesRequest(); | |
| $templateForOtherModuleRequest->name= $examSupplementaryName." Common Fee"; | |
| $templateForOtherModuleRequest->feeHeads = $examSupplementary['feeHeads']; | |
| $templateForOtherModuleRequest->module = "EXAM_CONTROLLER"; | |
| $fineQuery = "SELECT DISTINCT | |
| es.id, | |
| es.supplyDesc, | |
| siefin.supply_fineAmount AS examfineAmount, | |
| efint.examfineName, | |
| siefin.supply_startDate, | |
| siefin.supply_endDate | |
| FROM | |
| exam_supplementary es | |
| INNER JOIN | |
| supply_improve_exam_fine siefin ON es.id = siefin.exam_supplementary_id | |
| INNER JOIN | |
| exam_finetype efint ON siefin.examfineID = efint.examfineID | |
| WHERE | |
| es.id='".$examSupplementary['id']."';"; | |
| $fines = $this->executeQueryForList($fineQuery); | |
| foreach ($fines as $fine ) { | |
| $templateForOtherModuleRequest->fineHeads [] = ["name" => $fine->examfineName,"rules" => [(object) [ | |
| "amount" => $fine->examfineAmount, | |
| "condition"=>"AND", | |
| "id"=>"rule-".rand(), | |
| "innerRules" => [(object) [ | |
| "id"=>"-".rand(), | |
| "operandId"=>$paymentDateOperand->id, | |
| "operator"=>"BTW", | |
| "value"=>[date('d-m-Y',strtotime($fine->supply_startDate)),date('d-m-Y',strtotime($fine->supply_endDate))] | |
| ]] | |
| ]]]; | |
| } | |
| $examRegCommonFees = TemplateService::getInstance()->saveTemplateForOtherModules($templateForOtherModuleRequest); | |
| $this->executeQuery("UPDATE | |
| ec_exam_registration eer | |
| INNER JOIN exam_supplementary er ON | |
| er.ec_exam_registration_id = eer.id SET | |
| eer.properties = JSON_SET( eer.properties, | |
| '$.templateId', '$examRegCommonFees->id' | |
| ) | |
| WHERE | |
| er.id = ".$examSupplementary['id']); | |
| } | |
| $this->executeQuery("UPDATE | |
| ec_exam_registration eer | |
| SET | |
| eer.properties = JSON_REMOVE( | |
| eer.properties, | |
| '$.examregId' | |
| ) | |
| WHERE | |
| eer.`type` = 'SUPPLEMENTARY'"); | |
| $migrationDetails->endTime = time(); | |
| $migrationDetails->status = "SUCCESS"; | |
| $migrationDetails->recordCount = $result->sqlResult->num_rows; | |
| $this->updateMigrationTables($migrationDetails, "ec_exam_registration"); | |
| // Revaluation | |
| $migrationDetails = new \stdClass(); | |
| $migrationDetails->startTime = time(); | |
| $migrationDetails->tables = ["exam_revaluation"]; | |
| $query = "INSERT IGNORE INTO ec_exam_registration(id,name,`type`,properties,created_by,fees_properties) | |
| SELECT | |
| CONCAT(LEFT(UUID(), 8), RIGHT(UUID(), 9)), | |
| revalDesc, | |
| 'REVALUATION', | |
| CONCAT('{', | |
| '\"publish\": ', IF(er.published=1,'true','false'), | |
| ',\"subjectLimit\": ',IF(er.subjectLimit =0 OR er.subjectLimit IS NULL,'\"ALL\"',er.subjectLimit), | |
| ',\"margin\": ',IF(er.margin IS NOT NULL,er.margin,'\"\"'), | |
| ',\"percentage\": ',IF(er.percentage IS NOT NULL,er.percentage,'\"\"'), | |
| ',\"memoNum\": \"',IF(er.memoNum IS NOT NULL,er.memoNum,''),'\"', | |
| ',\"memoDate\": \"',IF(er.memoDate IS NOT NULL,er.memoDate,''),'\"', | |
| ',\"publishFromDate\": \"',IF(er.fromDate IS NOT NULL,er.fromDate,''),'\"', | |
| ',\"publishToDate\": \"',IF(er.toDate IS NOT NULL,er.toDate,''),'\"', | |
| ',\"description\": \"',IF(er.revalDesc IS NOT NULL,REGEXP_REPLACE(er.revalDesc,'\n|\t',''),''),'\"', | |
| ',\"registrationStartDate\": \"',IF(er.startDate IS NOT NULL,er.startDate,''),'\"', | |
| ',\"registrationWithoutFineEndDate\": \"',IF(er.endDate IS NOT NULL,er.endDate,''),'\"', | |
| ',\"examregId\": ',er.id, | |
| '}'), | |
| $staffId, | |
| CONCAT('{', '}') | |
| FROM | |
| exam_revaluation er WHERE er.ec_exam_registration_id IS NULL"; | |
| $result = $this->executeQuery($query); | |
| $query = "INSERT IGNORE INTO ec_exam_registration(id,name,`type`,properties,created_by,fees_properties) | |
| SELECT | |
| CONCAT(LEFT(UUID(), 8), RIGHT(UUID(), 9)), | |
| CONCAT(revalDesc,IF(revalDesc IS NOT NULL, '_','')), | |
| 'REVALUATION', | |
| CONCAT('{', | |
| '\"publish\": ', IF(er.published=1,'true','false'), | |
| ',\"subjectLimit\": ',IF(er.subjectLimit =0 OR er.subjectLimit IS NULL,'\"ALL\"',er.subjectLimit), | |
| ',\"margin\": ',IF(er.margin IS NOT NULL,er.margin,'\"\"'), | |
| ',\"percentage\": ',IF(er.percentage IS NOT NULL,er.percentage,'\"\"'), | |
| ',\"memoNum\": \"',IF(er.memoNum IS NOT NULL,er.memoNum,''),'\"', | |
| ',\"memoDate\": \"',IF(er.memoDate IS NOT NULL,er.memoDate,''),'\"', | |
| ',\"publishFromDate\": \"',IF(er.fromDate IS NOT NULL,er.fromDate,''),'\"', | |
| ',\"publishToDate\": \"',IF(er.toDate IS NOT NULL,er.toDate,''),'\"', | |
| ',\"description\": \"',IF(er.revalDesc IS NOT NULL,REGEXP_REPLACE(er.revalDesc,'\n|\t',''),''),'\"', | |
| ',\"registrationStartDate\": \"',IF(er.startDate IS NOT NULL,er.startDate,''),'\"', | |
| ',\"registrationWithoutFineEndDate\": \"',IF(er.endDate IS NOT NULL,er.endDate,''),'\"', | |
| ',\"examregId\": ',er.id, | |
| '}'), | |
| $staffId, | |
| CONCAT('{', '}') | |
| FROM | |
| exam_revaluation er WHERE er.ec_exam_registration_id IS NULL"; | |
| $result = $this->executeQuery($query); | |
| $this->executeQuery("UPDATE | |
| exam_revaluation er | |
| INNER JOIN ec_exam_registration eer ON | |
| eer.properties->>'$.examregId' = er.id | |
| AND eer.`type` = 'REVALUATION' SET | |
| er.ec_exam_registration_id = eer.id"); | |
| // Fees Migratiion - Common Fees | |
| $commonFeeQuery = "SELECT DISTINCT | |
| er.id, | |
| er.revalDesc, | |
| erf.exam_fees_name, | |
| erf.exam_fees_amount | |
| FROM | |
| exam_revaluation er | |
| INNER JOIN | |
| exam_revaluation_fees erf ON erf.exam_revaluation_id = er.id | |
| WHERE | |
| erf.isCommon = 1"; | |
| $examRevaluations = $this->executeQueryForList($commonFeeQuery); | |
| $examRevaluationDetails = []; | |
| foreach ($examRevaluations as $examRevaluation) { | |
| if($examRevaluation->exam_fees_amount > 0) { | |
| $examRevaluation->exam_fees_amount = (int) $examRevaluation->exam_fees_amount; | |
| $examRevaluation->revalDesc = trim($examRevaluation->revalDesc); | |
| if(!isset($examRevaluationDetails[$examRevaluation->revalDesc])) { | |
| $examRevaluationDetails[$examRevaluation->revalDesc]['feeHeads'] = []; | |
| } | |
| $examRevaluationDetails[$examRevaluation->revalDesc]['feeHeads'] [] = ["name" => $examRevaluation->exam_fees_name,"amount" => $examRevaluation->exam_fees_amount]; | |
| $examRevaluationDetails[$examRevaluation->revalDesc]['id'] = $examRevaluation->id; | |
| } | |
| } | |
| $paymentDateOperandQuery = "SELECT id FROM fm_operand where name='PAYMENT_DATE' and type='FINE'"; | |
| $paymentDateOperand = $this->executeQueryForObject($paymentDateOperandQuery); | |
| foreach($examRevaluationDetails as $examRevaluationName => $examRevaluation) { | |
| $templateForOtherModuleRequest = new TemplateForOtherModulesRequest(); | |
| $templateForOtherModuleRequest->name= $examRevaluationName." Common Fee"; | |
| $templateForOtherModuleRequest->feeHeads = $examRevaluation['feeHeads']; | |
| $templateForOtherModuleRequest->module = "EXAM_CONTROLLER"; | |
| // $fineQuery = "select CONCAT(er.examregName,' ',eft.examfineName) AS examfineName,erf.examfineAmount,erf.startDate,erf.lastDate from exam_registration_fine erf inner join exam_finetype eft on erf.examfineID=eft.examfineID INNER JOIN exam_registration er ON er.examregID=erf.examregID where erf.examregID=".$examRevaluation['id'].";"; | |
| // $fines = $this->executeQueryForList($fineQuery); | |
| // foreach ($fines as $fine ) { | |
| // $templateForOtherModuleRequest->fineHeads [] = ["name" => $fine->examfineName,"rules" => [(object) [ | |
| // "amount" => $fine->examfineAmount, | |
| // "condition"=>"AND", | |
| // "id"=>"rule-".rand(), | |
| // "innerRules" => [(object) [ | |
| // "id"=>"-".rand(), | |
| // "operandId"=>$paymentDateOperand->id, | |
| // "operator"=>"BTW", | |
| // "value"=>[date('d-m-Y',strtotime($fine->startDate)),date('d-m-Y',strtotime($fine->lastDate))] | |
| // ]] | |
| // ]]]; | |
| // } | |
| $examRegCommonFees = TemplateService::getInstance()->saveTemplateForOtherModules($templateForOtherModuleRequest); | |
| $this->executeQuery("UPDATE | |
| ec_exam_registration eer | |
| INNER JOIN exam_revaluation er ON | |
| er.ec_exam_registration_id = eer.id SET | |
| eer.properties = JSON_SET( eer.properties, | |
| '$.templateId', '$examRegCommonFees->id' | |
| ) | |
| WHERE | |
| er.id = ".$examRevaluation['id']); | |
| } | |
| $this->executeQuery("UPDATE | |
| ec_exam_registration eer | |
| SET | |
| eer.properties = JSON_REMOVE( | |
| eer.properties, | |
| '$.examregId' | |
| ) | |
| WHERE | |
| eer.`type` = 'REVALUATION'"); | |
| $migrationDetails->endTime = time(); | |
| $migrationDetails->status = "SUCCESS"; | |
| $migrationDetails->recordCount = $result->sqlResult->num_rows; | |
| $this->updateMigrationTables($migrationDetails, "ec_exam_registration"); | |
| } catch (\Exception $e) { | |
| $migrationDetails->endTime = time(); | |
| $migrationDetails->status = "FAILED"; | |
| $migrationDetails->recordCount = 0; | |
| $this->updateMigrationTables($migrationDetails, "ec_exam_registration"); | |
| throw new ProfessionalException($e->getMessage(), $e->getCode()); | |
| } | |
| return true; | |
| } | |
| /** | |
| * Migrate table | |
| */ | |
| public function migrate_ec_exam_registration_batch() | |
| { | |
| try { | |
| $staffId = $_SESSION['adminID']; | |
| $migrationDetails = new \stdClass(); | |
| $migrationDetails->startTime = time(); | |
| $migrationDetails->tables = ["exam_registration_batches"]; | |
| $query = "INSERT IGNORE INTO ec_exam_registration_batch(id,groups_id,ec_exam_registration_id,properties,created_by) | |
| SELECT | |
| CONCAT(LEFT(UUID(), 8), RIGHT(UUID(), 9)), | |
| g.id, | |
| eer.id, | |
| CONCAT('{', | |
| '\"academicTermId\": \"',IF(at2.id IS NOT NULL,at2.id,'\"\"'),'\"', | |
| ',\"attendanceClosingDate\": \"',IF(erb.attClosingDate IS NOT NULL,erb.attClosingDate,''),'\"', | |
| ',\"publish\": ', IF(erb.publish=1,'true','false'), | |
| ',\"publishFromDate\": \"',IF(erb.publish_fromDate IS NOT NULL,erb.publish_fromDate,''),'\"', | |
| ',\"publishToDate\": \"',IF(erb.publish_toDate IS NOT NULL,erb.publish_toDate,''),'\"', | |
| '}'), | |
| $staffId | |
| FROM | |
| exam_registration_batches erb | |
| INNER JOIN exam_registration er ON er.examregID=erb.examregID | |
| INNER JOIN batches b ON b.batchID=erb.batchID | |
| INNER JOIN semesters s ON s.semID=erb.semID | |
| INNER JOIN ec_exam_registration eer ON eer.id=er.ec_exam_registration_id AND eer.`type`='REGULAR' | |
| INNER JOIN `groups` g ON g.id=b.groups_id AND g.`type`='BATCH' | |
| INNER JOIN academic_term at2 ON at2.id=s.semID"; | |
| $result = $this->executeQuery($query); | |
| $migrationDetails->endTime = time(); | |
| $migrationDetails->status = "SUCCESS"; | |
| $migrationDetails->recordCount = $result->sqlResult->num_rows; | |
| $this->updateMigrationTables($migrationDetails, "ec_exam_registration_batch"); | |
| // Supply | |
| $migrationDetails = new \stdClass(); | |
| $migrationDetails->startTime = time(); | |
| $migrationDetails->tables = ["supply_improve_batches"]; | |
| // $query = "INSERT IGNORE INTO ec_exam_registration_batch(id,groups_id,ec_exam_registration_id,properties,created_by) | |
| // SELECT | |
| // CONCAT(LEFT(UUID(), 8), RIGHT(UUID(), 9)), | |
| // g.id, | |
| // eer.id, | |
| // CONCAT('{', | |
| // '\"academicTermId\": \"',IF(at2.id IS NOT NULL,at2.id,''),'\"', | |
| // '}'), | |
| // $staffId | |
| // FROM | |
| // supply_improve_batches sib | |
| // INNER JOIN exam_supplementary es ON | |
| // es.id = sib.exam_supplementary_id | |
| // INNER JOIN batches b ON | |
| // b.batchID = sib.batchID | |
| // INNER JOIN exam e ON | |
| // e.batchID = sib.batchID | |
| // AND e.batchID = b.batchID | |
| // AND e.supply_examreg_id = sib.exam_supplementary_id | |
| // AND e.supply_examreg_id = es.id | |
| // INNER JOIN semesters s ON | |
| // s.semID = e.semID | |
| // INNER JOIN ec_exam_registration eer ON | |
| // eer.id = es.ec_exam_registration_id | |
| // AND eer.`type` = 'SUPPLEMENTARY' | |
| // INNER JOIN `groups` g ON | |
| // g.id = b.groups_id | |
| // AND g.`type` = 'BATCH' | |
| // INNER JOIN academic_term at2 ON | |
| // at2.id = s.semID | |
| // ON DUPLICATE KEY UPDATE properties=VALUES(properties)"; | |
| $query = "INSERT IGNORE INTO ec_exam_registration_batch(id,groups_id,ec_exam_registration_id,properties,created_by) | |
| SELECT DISTINCT | |
| CONCAT(LEFT(UUID(), 8), RIGHT(UUID(), 9)), | |
| b.groups_id, | |
| es.ec_exam_registration_id, | |
| CONCAT('{', | |
| '\"academicTermId\": \"',IF(at2.id IS NOT NULL,at2.id,''),'\"', | |
| '}'), | |
| '$staffId' | |
| FROM | |
| exam e | |
| INNER JOIN exam_supplementary es ON | |
| e.supply_examreg_id = es.id | |
| INNER JOIN batches b ON | |
| e.batchID = b.batchID | |
| INNER JOIN semesters s ON | |
| s.semID = e.semID | |
| INNER JOIN academic_term at2 ON | |
| at2.id = s.semID | |
| ON DUPLICATE KEY UPDATE properties=VALUES(properties)"; | |
| $result = $this->executeQuery($query); | |
| $migrationDetails->endTime = time(); | |
| $migrationDetails->status = "SUCCESS"; | |
| $migrationDetails->recordCount = $result->sqlResult->num_rows; | |
| $this->updateMigrationTables($migrationDetails, "ec_exam_registration_batch"); | |
| // Special Exam | |
| $migrationDetails = new \stdClass(); | |
| $migrationDetails->startTime = time(); | |
| $migrationDetails->tables = ["special_exam_assigned_students"]; | |
| $query = "INSERT IGNORE INTO ec_exam_registration_batch(id,groups_id,ec_exam_registration_id,properties,created_by) | |
| SELECT | |
| * | |
| FROM | |
| ( | |
| SELECT | |
| LEFT(REPLACE(UUID(),'-',''),17), | |
| b.groups_id, | |
| es.ec_exam_registration_id, | |
| JSON_OBJECT('academicTermId', IF(e.semID IS NOT NULL, e.semID, ''),'seasId',seas.id), | |
| '$staffId' | |
| FROM | |
| special_exam_assigned_students seas | |
| INNER JOIN batches b ON | |
| b.batchID = seas.batches_id | |
| INNER JOIN exam_supplementary es ON | |
| es.id = seas.exam_supplementary_id | |
| INNER JOIN exam e ON | |
| e.examID = seas.exam_id | |
| GROUP BY | |
| seas.batches_id, | |
| seas.exam_supplementary_id | |
| )AS p"; | |
| $result = $this->executeQuery($query); | |
| $migrationDetails->endTime = time(); | |
| $migrationDetails->status = "SUCCESS"; | |
| $migrationDetails->recordCount = $result->sqlResult->num_rows; | |
| $this->updateMigrationTables($migrationDetails, "ec_exam_registration_batch"); | |
| // Revaluation | |
| $migrationDetails = new \stdClass(); | |
| $migrationDetails->startTime = time(); | |
| $migrationDetails->tables = ["exam_revaluation_batch_groups"]; | |
| $query = "INSERT IGNORE INTO ec_exam_registration_batch(id,groups_id,ec_exam_registration_id,properties,created_by) | |
| SELECT | |
| CONCAT(LEFT(UUID(), 8), RIGHT(UUID(), 9)), | |
| g.id, | |
| eer.id, | |
| CONCAT('{', | |
| '\"subjectLimit\": ',IF(erbg.subjectLimit =0 OR erbg.subjectLimit IS NULL,'\"ALL\"',erbg.subjectLimit), | |
| ',\"verificationDate\": \"',IF(erbg.verificationDate IS NOT NULL,erbg.verificationDate,''),'\"', | |
| '}'), | |
| $staffId | |
| FROM | |
| exam_revaluation_batch_groups erbg | |
| INNER JOIN exam_revaluation er ON er.id=erbg.exam_revaluation_id | |
| INNER JOIN batches b ON b.batchID=erbg.batchID | |
| INNER JOIN ec_exam_registration eer ON eer.id=er.ec_exam_registration_id AND eer.`type`='REVALUATION' | |
| INNER JOIN `groups` g ON g.id=b.groups_id AND g.`type`='BATCH'"; | |
| $result = $this->executeQuery($query); | |
| $migrationDetails->endTime = time(); | |
| $migrationDetails->status = "SUCCESS"; | |
| $migrationDetails->recordCount = $result->sqlResult->num_rows; | |
| $this->updateMigrationTables($migrationDetails, "ec_exam_registration_batch"); | |
| } catch (\Exception $e) { | |
| $migrationDetails->endTime = time(); | |
| $migrationDetails->status = "FAILED"; | |
| $migrationDetails->recordCount = 0; | |
| $this->updateMigrationTables($migrationDetails, "ec_exam_registration_batch"); | |
| throw new ProfessionalException($e->getMessage(), $e->getCode()); | |
| } | |
| return true; | |
| } | |
| /** | |
| * Migrate table | |
| */ | |
| public function migrate_ec_exam_registration_retest_mapping() | |
| { | |
| try { | |
| $staffId = $_SESSION['adminID']; | |
| $migrationDetails = new \stdClass(); | |
| $migrationDetails->startTime = time(); | |
| $migrationDetails->tables = ["supply_improve_subject_fees"]; | |
| $query = "INSERT INTO ec_exam_registration_retest_mapping(ec_exam_registration_id,retest_exam_registration_id,created_by) | |
| SELECT DISTINCT | |
| eer.id, | |
| supply.id, | |
| $staffId | |
| FROM | |
| supply_improve_subject_fees sisf | |
| INNER JOIN exam e ON | |
| e.examID = sisf.examID | |
| INNER JOIN exam_registration er ON | |
| er.examregID = e.examregID | |
| INNER JOIN exam_supplementary es ON | |
| es.id = sisf.exam_supplementary_id | |
| INNER JOIN ec_exam_registration eer ON | |
| eer.id = er.ec_exam_registration_id | |
| AND eer.`type` = 'REGULAR' | |
| INNER JOIN ec_exam_registration supply ON | |
| supply.id = es.ec_exam_registration_id | |
| AND supply.`type` = 'SUPPLEMENTARY'"; | |
| $result = $this->executeQuery($query); | |
| $migrationDetails->endTime = time(); | |
| $migrationDetails->status = "SUCCESS"; | |
| $migrationDetails->recordCount = $result->sqlResult->num_rows; | |
| $this->updateMigrationTables($migrationDetails, "ec_exam_registration_retest_mapping"); | |
| // Revaluation | |
| $migrationDetails = new \stdClass(); | |
| $migrationDetails->startTime = time(); | |
| $migrationDetails->tables = ["exam_revaluation_batch_groups"]; | |
| $query = "INSERT INTO ec_exam_registration_retest_mapping(ec_exam_registration_id,retest_exam_registration_id,created_by) | |
| SELECT DISTINCT | |
| eer.id, | |
| supply.id, | |
| $staffId | |
| FROM | |
| exam_revaluation_subject_fees sisf | |
| INNER JOIN exam e ON | |
| e.examID = sisf.examID | |
| INNER JOIN exam_registration er ON | |
| er.examregID = e.examregID | |
| INNER JOIN exam_revaluation reval ON | |
| reval.id = sisf.exam_revaluation_id | |
| INNER JOIN ec_exam_registration eer ON | |
| eer.id = er.ec_exam_registration_id | |
| AND eer.`type` = 'REGULAR' | |
| INNER JOIN ec_exam_registration supply ON | |
| supply.id = reval.ec_exam_registration_id | |
| AND supply.`type` = 'REVALUATION'"; | |
| $result = $this->executeQuery($query); | |
| $migrationDetails->endTime = time(); | |
| $migrationDetails->status = "SUCCESS"; | |
| $migrationDetails->recordCount = $result->sqlResult->num_rows; | |
| $this->updateMigrationTables($migrationDetails, "ec_exam_registration_retest_mapping"); | |
| } catch (\Exception $e) { | |
| $migrationDetails->endTime = time(); | |
| $migrationDetails->status = "FAILED"; | |
| $migrationDetails->recordCount = 0; | |
| $this->updateMigrationTables($migrationDetails, "ec_exam_registration_retest_mapping"); | |
| throw new ProfessionalException($e->getMessage(), $e->getCode()); | |
| } | |
| return true; | |
| } | |
| public function migrate_ec_exam_registration_subject() | |
| { | |
| $taskQueRequest = new \stdClass(); | |
| $batches = BatchService::getInstance()->getBatchesByRequest($taskQueRequest); | |
| $batchList = array_chunk(array_unique(array_column($batches,"id")), 5, true); | |
| // $batchList = $batchList[0]; | |
| $staffId = $_SESSION['adminID']; | |
| $taskQueRequest->staffId = $staffId; | |
| foreach ($batchList as $batch) { | |
| $taskQueRequest->batchId = $batch; | |
| $taskQueue = new AMSTaskQueue(); | |
| $params = ['className' => 'com\linways\core\ams\professional\service\examcontroller\migration\MigartionsService', | |
| 'methodName' => 'generate_ec_exam_registration_subject', | |
| 'methodParams' => [$taskQueRequest]]; | |
| $taskQueue->enqueue('EXECUTE SERVICE', $params); | |
| // $this->generate_ec_exam_registration_subject($taskQueRequest); | |
| $startProcessing = true; | |
| } | |
| } | |
| /** | |
| * Migrate table | |
| */ | |
| public function generate_ec_exam_registration_subject($taskQueRequest) | |
| { | |
| try { | |
| ini_set('max_execution_time', '5200'); | |
| ini_set('memory_limit', '5120M'); | |
| // ini_set('memory_limit', 0); | |
| $comonCondition = ""; | |
| // $selectedBatchStartyear = [2016]; | |
| // if (!empty($selectedBatchStartyear)) { | |
| // $comonCondition .= " AND b.batchStartYear IN (".implode(',',$selectedBatchStartyear).") "; | |
| // } | |
| $taskQueRequest->batchId = (array) $taskQueRequest->batchId; | |
| if($taskQueRequest->batchId){ | |
| $comonCondition .= " AND b.batchID IN (".implode(',',$taskQueRequest->batchId).") "; | |
| } | |
| $staffId = $taskQueRequest->staffId; | |
| $migrationDetails = new \stdClass(); | |
| $examRegistrationSubjectFeeHead = []; | |
| $migrationDetails->startTime = time(); | |
| $migrationDetails->tables = ["exam_registration_subject_fees"]; | |
| $examRegSubjectArray = []; | |
| $migrationDetails->endTime = time(); | |
| $migrationDetails->status = "PROCESSING"; | |
| $migrationDetails->batchId = implode(',',$taskQueRequest->batchId); | |
| $this->updateMigrationTableLogs($migrationDetails, "ec_exam_registration_subject",$staffId); | |
| $query = "SELECT | |
| eerb.id AS examregBatchId, | |
| caps.id AS paperSubjectId, | |
| eer.name AS examRegistationName, | |
| cap.name AS subjectName, | |
| eer.id AS examRegistrationId, | |
| ersf.id AS examregSubjectId, | |
| e.am_assessment_id AS assessmentId, | |
| b.batchID AS batchId, | |
| er.examregID AS examRegId, | |
| ersf.semID AS semId, | |
| ersf.subjectID AS subjectId | |
| FROM | |
| exam_registration_subject_fees ersf | |
| INNER JOIN batches b ON | |
| b.batchID = ersf.batchID | |
| INNER JOIN exam_registration er ON | |
| er.examregID = ersf.examregID | |
| INNER JOIN `groups` g ON | |
| g.id = b.groups_id | |
| AND g.`type` = 'BATCH' | |
| INNER JOIN ec_exam_registration_batch eerb ON | |
| eerb.groups_id = g.id | |
| INNER JOIN ec_exam_registration eer ON | |
| eer.id = eerb.ec_exam_registration_id | |
| AND eer.id = er.ec_exam_registration_id | |
| AND eer.`type` = 'REGULAR' | |
| INNER JOIN academic_term at2 ON | |
| at2.id = CAST(eerb.properties->>'$.academicTermId' AS CHAR) | |
| AND at2.id = ersf.semID | |
| INNER JOIN cm_curriculum cc ON cc.id = CAST(g.properties->>'$.curriculumId' AS CHAR) | |
| AND cc.properties->>'$.programId' = g.properties->>'$.programId' | |
| INNER JOIN cm_curriculum_syllabus_relation ccsr ON ccsr.cm_curriculum_id = cc.id | |
| INNER JOIN cm_syllabus cs ON | |
| cs.id = ccsr.cm_syllabus_id | |
| INNER JOIN cm_syllabus_academic_term_settings csats ON | |
| csats.academic_term_id = at2.id | |
| AND csats.cm_syllabus_id = cs.id | |
| INNER JOIN cm_academic_paper cap ON | |
| cap.cm_syllabus_academic_term_settings_id = csats.id | |
| INNER JOIN cm_academic_paper_subjects caps ON | |
| caps.subject_id = ersf.subjectID | |
| AND caps.cm_academic_paper_id = cap.id | |
| LEFT JOIN exam e ON | |
| e.batchID = ersf.batchID | |
| AND e.semID = ersf.semID | |
| AND e.subjectID = ersf.subjectID | |
| AND e.subjectID = caps.subject_id | |
| AND e.examregID = ersf.examregID | |
| AND e.examregID = er.examregID | |
| AND e.batchID = b.batchID | |
| LEFT JOIN ec_exam_registration_subject eers ON | |
| eers.valuation_details->>'$.examregSubjectId' = ersf.id | |
| WHERE | |
| 1=1 $comonCondition | |
| "; | |
| $rows = $this->executeQueryForList($query); | |
| // ====================Fees Migratiion - Subject Fees================= | |
| $commonFeeQuery = "SELECT | |
| er.examRegID, | |
| er.examregName, | |
| s.subjectName, | |
| e.batchID, | |
| e.semID, | |
| ersf.examfeesAmount, | |
| ersf.examfeesID, | |
| CONCAT(er.examregName,' ',eft.examfeesName) AS examfeesName, | |
| b.batchName, | |
| b.groups_id AS groupId, | |
| s.subjectID | |
| FROM | |
| exam_registration er | |
| INNER JOIN | |
| exam_registration_subject_fees ersf ON er.examregID = ersf.examregID | |
| INNER JOIN | |
| subjects s ON ersf.subjectID = s.subjectID | |
| INNER JOIN | |
| batches b on b.batchID=ersf.batchID | |
| INNER JOIN | |
| exam e ON e.examregID = ersf.examregID | |
| AND e.subjectID = ersf.subjectID | |
| AND e.batchID=ersf.batchID | |
| AND e.semID=ersf.semID | |
| INNER JOIN | |
| exam_feestype eft ON ersf.examfeesID = eft.examfeesID | |
| WHERE | |
| e.examregID IS NOT NULL AND eft.everySubject=1 | |
| $comonCondition | |
| "; | |
| $examRegistrations = $this->executeQueryForList($commonFeeQuery); | |
| $examRegistrationDetails = []; | |
| foreach ($examRegistrations as $examRegistration) { | |
| if($examRegistration->examfeesID > 0) { | |
| $examRegistration->examfeesAmount = (int) $examRegistration->examfeesAmount; | |
| if(!isset($examRegistrationDetails[$examRegistration->examregName])) { | |
| $examRegistrationDetails[$examRegistration->examregName]['batches'] = []; | |
| } | |
| if(!isset($examRegistrationDetails[$examRegistration->examregName]['batches'][$examRegistration->batchID])) { | |
| $examRegistrationDetails[$examRegistration->examregName]['batches'][$examRegistration->batchID]['feeHeads'] = []; | |
| } | |
| $examRegSubjectDetails = reset(array_filter($rows,function($elem)use($examRegistration){ | |
| return $examRegistration->batchID == $elem->batchId && $examRegistration->subjectID == $elem->subjectId && $examRegistration->semID == $elem->semId && $examRegistration->examRegID == $elem->examRegId; | |
| })); | |
| $examRegistrationDetails[$examRegistration->examregName]['batches'][$examRegistration->batchID]['feeHeads'] [$examRegistration->examfeesName] [] = ["name" => $examRegistration->subjectName,"amount" => $examRegistration->examfeesAmount,"examregBatchId"=>$examRegSubjectDetails->examregBatchId,"paperSubjectId"=>$examRegSubjectDetails->paperSubjectId,"examregSubjectId"=>$examRegSubjectDetails->examregSubjectId,"assessmentId"=>$examRegSubjectDetails->assessmentId]; | |
| $examRegistrationDetails[$examRegistration->examregName]['id'] = $examRegistration->examRegID; | |
| $examRegistrationDetails[$examRegistration->examregName]['batches'][$examRegistration->batchID]['semesterId'] = $examRegistration->semID; | |
| $examRegistrationDetails[$examRegistration->examregName]['batches'][$examRegistration->batchID]['batchName'] = $examRegistration->batchName; | |
| $examRegistrationDetails[$examRegistration->examregName]['batches'][$examRegistration->batchID]['groupId'] = $examRegistration->groupId; | |
| } | |
| } | |
| foreach($examRegistrationDetails as $examRegistrationName => $examRegistration) { | |
| foreach($examRegistration['batches'] as $batchId => $batchDetails) { | |
| $templateForOtherModuleRequest = new TemplateForOtherModulesRequest(); | |
| $templateForOtherModuleRequest->name= $examRegistrationName." Subject Fee ".$batchDetails['batchName']; | |
| foreach($batchDetails['feeHeads'] as $feeHeadName => $feeSubHead) { | |
| $feeHeadName = preg_replace('/\s+/u', ' ', $feeHeadName); | |
| $templateForOtherModuleRequest->feeHeads [] = ["name" => $feeHeadName,"hasSubHeads"=>"1","feeSubHeads" => $feeSubHead]; | |
| } | |
| $templateForOtherModuleRequest->module = "EXAM_CONTROLLER"; | |
| $templateForOtherModuleRequest->groupId = $batchDetails['groupId'];//TODO: Get group details from batchId | |
| $templateForOtherModuleRequest->semesterId = $batchDetails['semesterId']; | |
| $examRegSubjectFees = TemplateService::getInstance()->saveTemplateForOtherModules($templateForOtherModuleRequest); | |
| $this->executeQuery("UPDATE | |
| ec_exam_registration eer | |
| INNER JOIN exam_supplementary er ON | |
| er.ec_exam_registration_id = eer.id SET | |
| eer.properties = JSON_SET( eer.properties, | |
| '$.subjectFeeTemplateId', '$examRegSubjectFees->id', | |
| '$.subjectFeeSubjectHeadId', '".reset($examRegSubjectFees->feeRules)["id"]."' | |
| ) | |
| WHERE | |
| er.id = ".$examRegistration['id']); | |
| // $templateForOtherModuleRequest = json_decode(json_encode($templateForOtherModuleRequest)); | |
| foreach (reset($templateForOtherModuleRequest->feeHeads)['feeSubHeads'] as $feeSubHead) { | |
| if(empty($feeSubHead['examregSubjectId'])) continue; | |
| $examRegSubjectArray[] = "('".$feeSubHead['examregBatchId']."','".$feeSubHead['paperSubjectId']."',".reset($examRegSubjectFees->feeRules)["id"].",JSON_OBJECT('examregSubjectId','".$feeSubHead['examregSubjectId']."'),'".$feeSubHead['assessmentId']."',$staffId)"; | |
| } | |
| } | |
| } | |
| // =====================================END================================================ | |
| // foreach ($rows as $row) { | |
| // if (!$row->assessmentId) continue; | |
| // if (empty($examRegistrationSubjectFeeHead[$row->examRegistrationId])) { | |
| // $sql="INSERT INTO fm_fee_head (name,description,is_active,created_by,updated_by,created_date,updated_date,account_id,has_subheads,frequency,refundable_fee_head,fm_head_group_id,module) VALUES | |
| // ('$row->examRegistationName-Subject-Fee-Head','',1,$staffId,$staffId,now(),now(),NULL,1,'CUSTOM',0,NULL,'EXAM_CONTROLLER') ON DUPLICATE KEY UPDATE updated_date=now()"; | |
| // $id = $this->executeQuery($sql,true)->id; | |
| // $examRegistrationSubjectFeeHead[$row->examRegistrationId] = $id; | |
| // } | |
| // $subjectName = $this->realEscapeString($row->subjectName); | |
| // $sql = "INSERT INTO fm_fee_subhead (name,frequency,fm_fee_head_id,`year`,created_by,created_date,updated_by,updated_date) VALUES | |
| // ('$subjectName',NULL,".$examRegistrationSubjectFeeHead[$row->examRegistrationId].",NULL,$staffId,now(),NULL,NULL) ON DUPLICATE KEY UPDATE updated_date=now()"; | |
| // $feeHeadId = $this->executeQuery($sql,true)->id; | |
| // $feeHeadId = $feeHeadId > 0 ? $feeHeadId : $this->executeQueryForObject("SELECT | |
| // id | |
| // FROM | |
| // fm_fee_subhead ffs | |
| // WHERE | |
| // name = '$subjectName' | |
| // AND fm_fee_head_id = ".$examRegistrationSubjectFeeHead[$row->examRegistrationId])->id; | |
| // if (empty($feeHeadId)) { | |
| // $feeHeadId = "NULL"; | |
| // } | |
| // $examRegSubjectArray[] = "('$row->examregBatchId','$row->paperSubjectId',$feeHeadId,JSON_OBJECT('examregSubjectId',$row->examregSubjectId),'$row->assessmentId',$staffId)"; | |
| // } | |
| $examRegSubjectValues = implode(',',$examRegSubjectArray); | |
| $sql = "INSERT INTO ec_exam_registration_subject(ec_exam_registration_batch_id, cm_academic_paper_subjects_id, fm_head_id, valuation_details, am_assessment_id, created_by) VALUES $examRegSubjectValues | |
| ON DUPLICATE KEY UPDATE | |
| am_assessment_id=VALUES(am_assessment_id), | |
| fm_head_id=VALUES(fm_head_id), | |
| valuation_details=json_merge_patch(valuation_details,VALUES(valuation_details))"; | |
| if (!empty($examRegSubjectArray) && $examRegSubjectValues) { | |
| $result = $this->executeQuery($sql,true); | |
| } | |
| unset($examRegistrations); | |
| unset($examRegistrationDetails); | |
| unset($examRegSubjectDetails); | |
| unset($templateForOtherModuleRequest); | |
| unset($examRegSubjectArray); | |
| // Supply | |
| $migrationDetails = new \stdClass(); | |
| $examRegistrationSubjectFeeHead = []; | |
| $migrationDetails->startTime = time(); | |
| $migrationDetails->tables = ["supply_improve_subject_fees"]; | |
| $query = "SELECT DISTINCT | |
| eerb.id AS examregBatchId, | |
| caps.id AS paperSubjectId, | |
| eer.name AS examRegistationName, | |
| cap.name AS subjectName, | |
| eer.id AS examRegistrationId, | |
| sisf.id AS examregSubjectId, | |
| e.am_assessment_id AS assessmentId, | |
| e.batchID AS batchId, | |
| caps.subject_id AS subjectId, | |
| es.id AS examRegID, | |
| es.semID AS semId | |
| FROM | |
| supply_improve_subject_fees sisf | |
| INNER JOIN exam_supplementary es ON | |
| es.id = sisf.exam_supplementary_id | |
| INNER JOIN ec_exam_registration eer ON | |
| eer.id = es.ec_exam_registration_id | |
| AND eer.`type` = 'SUPPLEMENTARY' | |
| INNER JOIN supply_improve_batches sib ON | |
| sib.exam_supplementary_id = sisf.exam_supplementary_id | |
| INNER JOIN exam e1 ON | |
| e1.examID = sisf.examID | |
| INNER JOIN exam e ON | |
| e.supply_examreg_id = sisf.exam_supplementary_id | |
| AND e.supply_examreg_id = sib.exam_supplementary_id | |
| AND e.subjectID = e1.subjectID | |
| AND e.semID = e1.semID | |
| AND e.batchID = sib.batchID | |
| INNER JOIN batches b ON | |
| b.batchID = e.batchID | |
| AND b.batchID = sib.batchID | |
| INNER JOIN `groups` g ON | |
| g.id = b.groups_id | |
| AND g.`type` = 'BATCH' | |
| INNER JOIN ec_exam_registration_batch eerb ON | |
| eerb.ec_exam_registration_id = eer.id | |
| AND eerb.groups_id = g.id | |
| AND CAST(eerb.properties->>'$.academicTermId' AS CHAR) = e.semID | |
| INNER JOIN academic_term at2 ON | |
| at2.id = e.semID | |
| INNER JOIN cm_curriculum cc ON | |
| cc.id = CAST(g.properties->>'$.curriculumId' AS CHAR) | |
| AND cc.properties->>'$.programId' = g.properties->>'$.programId' | |
| INNER JOIN cm_curriculum_syllabus_relation ccsr ON | |
| ccsr.cm_curriculum_id = cc.id | |
| INNER JOIN cm_syllabus cs ON | |
| cs.id = ccsr.cm_syllabus_id | |
| INNER JOIN cm_syllabus_academic_term_settings csats ON | |
| csats.academic_term_id = at2.id | |
| AND csats.cm_syllabus_id = cs.id | |
| INNER JOIN cm_academic_paper cap ON | |
| cap.cm_syllabus_academic_term_settings_id = csats.id | |
| INNER JOIN cm_academic_paper_subjects caps ON | |
| caps.subject_id = e.subjectID | |
| AND caps.cm_academic_paper_id = cap.id | |
| LEFT JOIN ec_exam_registration_subject eers ON | |
| eers.valuation_details->>'$.examregSubjectId' = sisf.id | |
| WHERE | |
| 1=1 $comonCondition | |
| -- eers.valuation_details->>'$.examregSubjectId' IS NULL | |
| "; | |
| $rows = $this->executeQueryForList($query); | |
| $examRegSubjectArray = []; | |
| // ====================Fees Migratiion - Subject Fees================= | |
| $commonFeeQuery = "SELECT DISTINCT | |
| sisf.exam_supplementary_id, | |
| es.supplyDesc, | |
| s.subjectName, | |
| s.subjectID, | |
| e_supplementary.batchID, | |
| es.semID, | |
| sisf.supply_subject_amount AS examfeesAmount, | |
| sisf.id AS examregSubjectId, | |
| eft.examfeesName, | |
| b.batchID, | |
| b.batchName, | |
| b.groups_id AS groupId, | |
| s.subjectID | |
| FROM | |
| supply_improve_subject_fees sisf | |
| INNER JOIN | |
| exam_supplementary es ON sisf.exam_supplementary_id = es.id | |
| INNER JOIN | |
| exam e ON sisf.examID = e.examID | |
| INNER JOIN | |
| exam e_supplementary ON e.semID=e_supplementary.semID AND e_supplementary.subjectID=e.subjectID AND e_supplementary.supply_examreg_id=sisf.exam_supplementary_id AND e_supplementary.examregID IS NULL | |
| INNER JOIN | |
| exam_feestype eft ON sisf.examfeesID = eft.examfeesID | |
| INNER JOIN | |
| subjects s ON s.subjectID = e.subjectID | |
| INNER JOIN batches b ON b.batchID=e_supplementary.batchID WHERE eft.everySubject=1 | |
| $comonCondition"; | |
| $examSupplementarys = $this->executeQueryForList($commonFeeQuery); | |
| $examSupplementaryDetails = []; | |
| $examSupplementarys = array_filter($examSupplementarys, function($elem){ | |
| return $elem->examregSubjectId > 0; | |
| }); | |
| foreach ($examSupplementarys as $examSupplementary) { | |
| $examSupplementary->supplyDesc = trim($examSupplementary->supplyDesc); | |
| $examSupplementary->subjectName = trim($examSupplementary->subjectName); | |
| if($examSupplementary->examregSubjectId > 0) { | |
| $examSupplementary->examfeesAmount = (int) $examSupplementary->examfeesAmount; | |
| if(!isset($examSupplementaryDetails[$examSupplementary->supplyDesc])) { | |
| $examSupplementaryDetails[$examSupplementary->supplyDesc]['batches'] = []; | |
| } | |
| if(!isset($examSupplementaryDetails[$examSupplementary->supplyDesc]['batches'][$examSupplementary->batchID])) { | |
| $examSupplementaryDetails[$examSupplementary->supplyDesc]['batches'][$examSupplementary->batchID]['feeHeads'] = []; | |
| } | |
| $examRegSubjectDetails = reset(array_filter($rows,function($elem)use($examSupplementary){ | |
| return $examSupplementary->batchID == $elem->batchId && $examSupplementary->subjectID == $elem->subjectId && $examSupplementary->semID == $elem->semId && $examSupplementary->exam_supplementary_id == $elem->examRegID; | |
| })); | |
| $examSupplementaryDetails[$examSupplementary->supplyDesc]['batches'][$examSupplementary->batchID]['feeHeads'] [$examSupplementary->supplyDesc." ".trim($examSupplementary->examfeesName)] [] = ["name" => $examSupplementary->subjectName,"amount" => $examSupplementary->examfeesAmount,"examregBatchId"=>$examRegSubjectDetails->examregBatchId,"paperSubjectId"=>$examRegSubjectDetails->paperSubjectId,"examregSubjectId"=>$examRegSubjectDetails->examregSubjectId,"assessmentId"=>$examRegSubjectDetails->assessmentId]; | |
| $examSupplementaryDetails[$examSupplementary->supplyDesc]['id'] = $examSupplementary->exam_supplementary_id; | |
| $examSupplementaryDetails[$examSupplementary->supplyDesc]['batches'][$examSupplementary->batchID]['semesterId'] = $examSupplementary->semID; | |
| $examSupplementaryDetails[$examSupplementary->supplyDesc]['batches'][$examSupplementary->batchID]['batchName'] = $examSupplementary->batchName; | |
| $examSupplementaryDetails[$examSupplementary->supplyDesc]['batches'][$examSupplementary->batchID]['groupId'] = $examSupplementary->groupId; | |
| } | |
| } | |
| foreach($examSupplementaryDetails as $examRegistrationName => $examRegistration) { | |
| if(empty($examRegistration['id'])) continue; | |
| foreach($examRegistration['batches'] as $batchId => $batchDetails) { | |
| $templateForOtherModuleRequest = new TemplateForOtherModulesRequest(); | |
| $templateForOtherModuleRequest->name= $examRegistrationName." Subject Fee ".$batchDetails['batchName']; | |
| foreach($batchDetails['feeHeads'] as $feeHeadName => $feeSubHead) { | |
| $templateForOtherModuleRequest->feeHeads [] = ["name" => $feeHeadName,"hasSubHeads"=>"1","feeSubHeads" => $feeSubHead]; | |
| } | |
| $templateForOtherModuleRequest->module = "EXAM_CONTROLLER"; | |
| $templateForOtherModuleRequest->groupId = $batchDetails['groupId']; | |
| $templateForOtherModuleRequest->semesterId = $batchDetails['semesterId']; | |
| $examRegSubjectFees = TemplateService::getInstance()->saveTemplateForOtherModules($templateForOtherModuleRequest); | |
| $this->executeQuery("UPDATE | |
| ec_exam_registration eer | |
| INNER JOIN exam_supplementary er ON | |
| er.ec_exam_registration_id = eer.id SET | |
| eer.properties = JSON_SET( eer.properties, | |
| '$.subjectFeeTemplateId', '$examRegSubjectFees->id', | |
| '$.subjectFeeSubjectHeadId', '".reset($examRegSubjectFees->feeRules)["id"]."' | |
| ) | |
| WHERE | |
| er.id = '".$examRegistration['id']."'"); | |
| foreach (reset($templateForOtherModuleRequest->feeHeads)['feeSubHeads'] as $feeSubHead) { | |
| if(empty($feeSubHead['examregSubjectId'])) continue; | |
| $examRegSubjectArray[] = "('".$feeSubHead['examregBatchId']."','".$feeSubHead['paperSubjectId']."',".reset($examRegSubjectFees->feeRules)["id"].",JSON_OBJECT('examregSubjectId','".$feeSubHead['examregSubjectId']."'),'".$feeSubHead['assessmentId']."',$staffId)"; | |
| } | |
| } | |
| } | |
| // =====================================END================================================ | |
| // foreach ($rows as $row) { | |
| // if (empty($examRegistrationSubjectFeeHead[$row->examRegistrationId])) { | |
| // $sql="INSERT INTO fm_fee_head (name,description,is_active,created_by,updated_by,created_date,updated_date,account_id,has_subheads,frequency,refundable_fee_head,fm_head_group_id,module) VALUES | |
| // ('$row->examRegistationName-Subject-Fee-Head','',1,$staffId,$staffId,now(),now(),NULL,1,'CUSTOM',0,NULL,'EXAM_CONTROLLER') ON DUPLICATE KEY UPDATE updated_date=now()"; | |
| // $id = $this->executeQuery($sql,true)->id; | |
| // $examRegistrationSubjectFeeHead[$row->examRegistrationId] = $id; | |
| // } | |
| // $subjectName = $this->realEscapeString($row->subjectName); | |
| // $sql = "INSERT INTO fm_fee_subhead (name,frequency,fm_fee_head_id,`year`,created_by,created_date,updated_by,updated_date) VALUES | |
| // ('$subjectName',NULL,".$examRegistrationSubjectFeeHead[$row->examRegistrationId].",NULL,$staffId,now(),NULL,NULL) ON DUPLICATE KEY UPDATE updated_date=now()"; | |
| // $feeHeadId = $this->executeQuery($sql,true)->id; | |
| // $feeHeadId = $feeHeadId > 0 ? $feeHeadId : $this->executeQueryForObject("SELECT | |
| // id | |
| // FROM | |
| // fm_fee_subhead ffs | |
| // WHERE | |
| // name = '$subjectName' | |
| // AND fm_fee_head_id = ".$examRegistrationSubjectFeeHead[$row->examRegistrationId])->id; | |
| // $examRegSubjectArray[] = "('$row->examregBatchId','$row->paperSubjectId','$feeHeadId',JSON_OBJECT('examregSubjectId',$row->examregSubjectId), '$row->assessmentId',$staffId)"; | |
| // } | |
| $examRegSubjectValues = implode(',',$examRegSubjectArray); | |
| $sql = "INSERT INTO ec_exam_registration_subject( ec_exam_registration_batch_id, cm_academic_paper_subjects_id, fm_head_id, valuation_details, am_assessment_id, created_by) VALUES $examRegSubjectValues | |
| ON DUPLICATE KEY UPDATE | |
| am_assessment_id=VALUES(am_assessment_id), | |
| fm_head_id=VALUES(fm_head_id), | |
| valuation_details=json_merge_patch(valuation_details,VALUES(valuation_details))"; | |
| if (!empty($examRegSubjectArray) && $examRegSubjectValues) { | |
| $result = $this->executeQuery($sql,true); | |
| } | |
| $migrationDetails->endTime = time(); | |
| $migrationDetails->status = "SUCCESS"; | |
| $migrationDetails->recordCount = $result->sqlResult->num_rows; | |
| // $this->updateMigrationTables($migrationDetails, "ec_exam_registration_subject"); | |
| // Supply special | |
| $migrationDetails = new \stdClass(); | |
| $examRegistrationSubjectFeeHead = []; | |
| $migrationDetails->startTime = time(); | |
| $migrationDetails->tables = ["special_exam_assigned_students"]; | |
| $query = "SELECT | |
| eerb.id AS examregBatchId, | |
| caps.id AS paperSubjectId, | |
| eer.name AS examRegistationName, | |
| cap.name AS subjectName, | |
| eer.id AS examRegistrationId, | |
| seas.id AS examregSubjectId, | |
| e.am_assessment_id AS assessmentId | |
| FROM | |
| special_exam_assigned_students seas | |
| INNER JOIN exam e ON | |
| e.examID = seas.exam_id | |
| INNER JOIN exam e2 ON | |
| e2.supply_examreg_id = seas.exam_supplementary_id | |
| AND e2.subjectID = e.subjectID | |
| AND e2.batchID = e.batchID | |
| AND e2.batchID = seas.batches_id | |
| AND e2.semID = e.semID | |
| INNER JOIN batches b ON | |
| b.batchID = e.batchID | |
| AND b.batchID = e2.batchID | |
| AND b.batchID = seas.batches_id | |
| INNER JOIN exam_supplementary es ON | |
| es.id = seas.exam_supplementary_id | |
| AND es.id = e2.supply_examreg_id | |
| INNER JOIN ec_exam_registration_batch eerb ON | |
| eerb.groups_id = b.groups_id | |
| AND eerb.ec_exam_registration_id = es.ec_exam_registration_id | |
| INNER JOIN ec_exam_registration eer ON | |
| eer.id = es.ec_exam_registration_id | |
| INNER JOIN `groups` g ON | |
| g.id = b.groups_id | |
| AND g.`type` = 'BATCH' | |
| INNER JOIN cm_curriculum cc ON | |
| cc.id = CAST(g.properties->>'$.curriculumId' AS CHAR) | |
| AND cc.properties->>'$.programId' = g.properties->>'$.programId' | |
| INNER JOIN cm_curriculum_syllabus_relation ccsr ON | |
| ccsr.cm_curriculum_id = cc.id | |
| INNER JOIN cm_syllabus cs ON | |
| cs.id = ccsr.cm_syllabus_id | |
| INNER JOIN cm_syllabus_academic_term_settings csats ON | |
| csats.cm_syllabus_id = cs.id | |
| INNER JOIN cm_academic_paper cap ON | |
| cap.cm_syllabus_academic_term_settings_id = csats.id | |
| INNER JOIN cm_academic_paper_subjects caps ON | |
| caps.cm_academic_paper_id = cap.id | |
| AND caps.subject_id = e.subjectID | |
| AND caps.subject_id = e2.subjectID | |
| LEFT JOIN ec_exam_registration_subject eers ON | |
| eers.valuation_details->>'$.specialExamregSubjectId' = seas.id | |
| WHERE | |
| 1=1 $comonCondition | |
| -- eers.valuation_details->>'$.specialExamregSubjectId' IS NULL | |
| "; | |
| $rows = $this->executeQueryForList($query); | |
| $examRegSubjectArray = []; | |
| // ====================Fees Migratiion - Subject Fees================= | |
| // $commonFeeQuery = "SELECT DISTINCT | |
| // sisf.exam_supplementary_id, | |
| // es.supplyDesc, | |
| // s.subjectName, | |
| // s.subjectID, | |
| // e_supplementary.batchID, | |
| // es.semID, | |
| // sisf.supply_subject_amount AS examfeesAmount, | |
| // eft.examfeesName, | |
| // b.batchID | |
| // b.batchName, | |
| // b.groups_id AS groupId, | |
| // s.subjectID | |
| // FROM | |
| // supply_improve_subject_fees sisf | |
| // INNER JOIN | |
| // exam_supplementary es ON sisf.exam_supplementary_id = es.id | |
| // INNER JOIN | |
| // exam e ON sisf.examID = e.examID | |
| // INNER JOIN | |
| // exam e_supplementary ON e.semID=e_supplementary.semID AND e_supplementary.subjectID=e.subjectID AND e_supplementary.supply_examreg_id=sisf.exam_supplementary_id AND e_supplementary.examregID IS NULL | |
| // INNER JOIN | |
| // exam_feestype eft ON sisf.examfeesID = eft.examfeesID | |
| // INNER JOIN | |
| // subjects s ON s.subjectID = e.subjectID | |
| // INNER JOIN batches b ON b.batchID=e_supplementary.batchID WHERE eft.everySubject=1"; | |
| // $examSupplementarys = $this->executeQueryForList($commonFeeQuery); | |
| // $examSupplementaryDetails = []; | |
| // foreach ($examSupplementarys as $examSupplementary) { | |
| // $examSupplementary->supplyDesc = trim($examSupplementary->supplyDesc); | |
| // $examSupplementary->subjectName = trim($examSupplementary->subjectName); | |
| // if($examSupplementary->examfeesAmount > 0) { | |
| // $examSupplementary->examfeesAmount = (int) $examSupplementary->examfeesAmount; | |
| // if(!isset($examSupplementaryDetails[$examSupplementary->supplyDesc])) { | |
| // $examSupplementaryDetails[$examSupplementary->supplyDesc]['batches'] = []; | |
| // } | |
| // if(!isset($examSupplementaryDetails[$examSupplementary->supplyDesc]['batches'][$examSupplementary->batchID])) { | |
| // $examSupplementaryDetails[$examSupplementary->supplyDesc]['batches'][$examSupplementary->batchID]['feeHeads'] = []; | |
| // } | |
| // $examRegSubjectDetails = reset(array_filter($rows,function($elem)use($examSupplementary){ | |
| // return $examSupplementary->batchID == $elem->batchId && $examSupplementary->subjectID == $elem->subjectId && $examSupplementary->semID == $elem->semId && $examSupplementary->examRegID == $elem->exam_supplementary_id; | |
| // })); | |
| // $examSupplementaryDetails[$examSupplementary->supplyDesc]['feeHeadNew'][$examSupplementary->batchID]['feeHeads'] [$examSupplementary->supplyDesc." ".trim($examSupplementary->examfeesName)] [] = ["name" => $examSupplementary->subjectName,"amount" => $examSupplementary->examfeesAmount,"examregBatchId"=>$examSupplementary->examregBatchId,"paperSubjectId"=>$examSupplementary->paperSubjectId,"examregSubjectId"=>$examSupplementary->examregSubjectId,"assessmentId"=>$examSupplementary->assessmentId]; | |
| // $examSupplementaryDetails[$examSupplementary->supplyDesc]['batches'][$examSupplementary->batchID]['feeHeads'] [$examSupplementary->supplyDesc." ".trim($examSupplementary->examfeesName)] [] = ["name" => $examSupplementary->subjectName,"amount" => $examSupplementary->examfeesAmount,"examregBatchId"=>$examSupplementary->examregBatchId,"paperSubjectId"=>$examSupplementary->paperSubjectId,"examregSubjectId"=>$examSupplementary->examregSubjectId,"assessmentId"=>$examSupplementary->assessmentId]; | |
| // $examSupplementaryDetails[$examSupplementary->supplyDesc]['id'] = $examSupplementary->exam_supplementary_id; | |
| // $examSupplementaryDetails[$examSupplementary->supplyDesc]['batches'][$examSupplementary->batchID]['semesterId'] = $examSupplementary->semID; | |
| // $examSupplementaryDetails[$examSupplementary->supplyDesc]['batches'][$examSupplementary->batchID]['batchName'] = $examSupplementary->batchName; | |
| // $examSupplementaryDetails[$examSupplementary->examregName]['batches'][$examRegistration->batchID]['groupId'] = $examSupplementary->groupId; | |
| // } | |
| // } | |
| // foreach($examSupplementaryDetails as $examRegistrationName => $examRegistration) { | |
| // foreach($examRegistration['batches'] as $batchId => $batchDetails) { | |
| // $templateForOtherModuleRequest = new TemplateForOtherModulesRequest(); | |
| // $templateForOtherModuleRequest->name= $examRegistrationName." Subject Fee ".$batchDetails['batchName']; | |
| // foreach($batchDetails['feeHeads'] as $feeHeadName => $feeSubHead) { | |
| // $templateForOtherModuleRequest->feeHeads [] = ["name" => $feeHeadName,"hasSubHeads"=>"1","feeSubHeads" => $feeSubHead]; | |
| // } | |
| // $templateForOtherModuleRequest->module = "EXAM_CONTROLLER"; | |
| // $templateForOtherModuleRequest->groupId = $batchDetails['groupId']; | |
| // $templateForOtherModuleRequest->semesterId = $batchDetails['semesterId']; | |
| // $examRegSubjectFees = TemplateService::getInstance()->saveTemplateForOtherModules($templateForOtherModuleRequest); | |
| // $this->executeQuery("UPDATE | |
| // ec_exam_registration eer | |
| // INNER JOIN exam_supplementary er ON | |
| // er.ec_exam_registration_id = eer.id SET | |
| // eer.properties = JSON_SET( eer.properties, | |
| // '$.subjectFeeTemplateId', '$examRegSubjectFees->id', | |
| // '$.subjectFeeSubjectHeadId', '".reset($examRegSubjectFees->feeRules)["id"]."' | |
| // ) | |
| // WHERE | |
| // er.id = ".$examRegistration['id']); | |
| // foreach (reset($templateForOtherModuleRequest->feeHeads)['feeSubHeads'] as $feeSubHead) { | |
| // if(empty($feeSubHead['examregSubjectId'])) continue; | |
| // $examRegSubjectArray[] = "('".$feeSubHead['examregBatchId']."','".$feeSubHead['paperSubjectId']."',".reset($examRegSubjectFees->feeRules)["id"].",JSON_OBJECT('examregSubjectId','".$feeSubHead['examregSubjectId']."'),'".$feeSubHead['assessmentId']."',$staffId)"; | |
| // } | |
| // } | |
| // } | |
| // =====================================END================================================ | |
| foreach ($rows as $row) { | |
| // $templateForOtherModuleRequest = new TemplateForOtherModulesRequest(); | |
| // $templateForOtherModuleRequest->name = $row->examRegistationName." Subject Fee ".$row->examRegistationName->batchName; | |
| // foreach($batchDetails['feeHeads'] as $feeHeadName => $feeSubHead) { | |
| // $templateForOtherModuleRequest->feeHeads [] = ["name" => $feeHeadName,"hasSubHeads"=>"1","feeSubHeads" => $feeSubHead]; | |
| // } | |
| // $templateForOtherModuleRequest->module = "EXAM_CONTROLLER"; | |
| // $templateForOtherModuleRequest->groupId = $batchDetails['groupId']; | |
| // $templateForOtherModuleRequest->semesterId = $batchDetails['semesterId']; | |
| // $examRegSubjectFees = TemplateService::getInstance()->saveTemplateForOtherModules($templateForOtherModuleRequest); | |
| if (empty($examRegistrationSubjectFeeHead[$row->examRegistrationId])) { | |
| $sql="INSERT INTO fm_fee_head (name,description,is_active,created_by,updated_by,created_date,updated_date,account_id,has_subheads,frequency,refundable_fee_head,fm_head_group_id,module) VALUES | |
| ('$row->examRegistationName-Subject-Fee-Head','',1,$staffId,$staffId,now(),now(),NULL,1,'CUSTOM',0,NULL,'EXAM_CONTROLLER') ON DUPLICATE KEY UPDATE updated_date=now()"; | |
| $id = $this->executeQuery($sql,true)->id; | |
| $examRegistrationSubjectFeeHead[$row->examRegistrationId] = $id; | |
| } | |
| $subjectName = $this->realEscapeString($row->subjectName); | |
| $sql = "INSERT INTO fm_fee_subhead (name,frequency,fm_fee_head_id,`year`,created_by,created_date,updated_by,updated_date) VALUES | |
| ('$subjectName _',NULL,".$examRegistrationSubjectFeeHead[$row->examRegistrationId].",NULL,$staffId,now(),NULL,NULL) ON DUPLICATE KEY UPDATE updated_date=now()"; | |
| $feeHeadId = $this->executeQuery($sql,true)->id; | |
| $feeHeadId = $feeHeadId > 0 ? $feeHeadId : $this->executeQueryForObject("SELECT | |
| id | |
| FROM | |
| fm_fee_subhead ffs | |
| WHERE | |
| name = '$subjectName' | |
| AND fm_fee_head_id = ".$examRegistrationSubjectFeeHead[$row->examRegistrationId])->id; | |
| $examRegSubjectArray[] = "('$row->examregBatchId','$row->paperSubjectId','$feeHeadId',JSON_OBJECT('specialExamregSubjectId',$row->examregSubjectId), '$row->assessmentId',$staffId)"; | |
| } | |
| $examRegSubjectValues = implode(',',$examRegSubjectArray); | |
| $sql = "INSERT INTO ec_exam_registration_subject(ec_exam_registration_batch_id, cm_academic_paper_subjects_id, fm_head_id, valuation_details, am_assessment_id, created_by) VALUES $examRegSubjectValues | |
| ON DUPLICATE KEY UPDATE | |
| am_assessment_id=VALUES(am_assessment_id), | |
| fm_head_id=VALUES(fm_head_id)"; | |
| if (!empty($examRegSubjectArray) && $examRegSubjectValues) { | |
| $result = $this->executeQuery($sql,true); | |
| } | |
| $migrationDetails->endTime = time(); | |
| $migrationDetails->status = "SUCCESS"; | |
| $migrationDetails->recordCount = $result->sqlResult->num_rows; | |
| // $this->updateMigrationTables($migrationDetails, "ec_exam_registration_subject"); | |
| // Revaluation | |
| $migrationDetails = new \stdClass(); | |
| $examRegistrationSubjectFeeHead = []; | |
| $migrationDetails->startTime = time(); | |
| $migrationDetails->tables = ["exam_revaluation_subject_fees"]; | |
| $query = "SELECT | |
| eerb.id AS examregBatchId, | |
| caps.id AS paperSubjectId, | |
| eer.name AS examRegistationName, | |
| cap.name AS subjectName, | |
| eer.id AS examRegistrationId, | |
| ersf.id AS examregSubjectId, | |
| e.am_assessment_id AS assessmentId, | |
| e.batchID AS batchId, | |
| caps.subject_id AS subjectId, | |
| er.id AS examRegID | |
| FROM | |
| exam_revaluation_subject_fees ersf | |
| INNER JOIN exam_revaluation er ON | |
| er.id = ersf.exam_revaluation_id | |
| INNER JOIN ec_exam_registration eer ON | |
| eer.id = er.ec_exam_registration_id | |
| AND eer.`type` = 'REVALUATION' | |
| INNER JOIN exam e ON | |
| e.examID = ersf.examID | |
| INNER JOIN batches b ON | |
| ersf.batchID | |
| INNER JOIN `groups` g ON | |
| g.id = b.groups_id | |
| AND g.`type` = 'BATCH' | |
| INNER JOIN ec_exam_registration_batch eerb ON | |
| eerb.ec_exam_registration_id = eer.id | |
| AND eerb.groups_id = g.id | |
| INNER JOIN academic_term at2 ON | |
| at2.id = ersf.semID | |
| INNER JOIN cm_curriculum cc ON | |
| cc.id = CAST(g.properties->>'$.curriculumId' AS CHAR) | |
| AND cc.properties->>'$.programId' = g.properties->>'$.programId' | |
| INNER JOIN cm_curriculum_syllabus_relation ccsr ON | |
| ccsr.cm_curriculum_id = cc.id | |
| INNER JOIN cm_syllabus cs ON | |
| cs.id = ccsr.cm_syllabus_id | |
| INNER JOIN cm_syllabus_academic_term_settings csats ON | |
| csats.academic_term_id = at2.id | |
| AND csats.cm_syllabus_id = cs.id | |
| INNER JOIN cm_academic_paper cap ON | |
| cap.cm_syllabus_academic_term_settings_id = csats.id | |
| INNER JOIN cm_academic_paper_subjects caps ON | |
| caps.subject_id = e.subjectID | |
| AND caps.cm_academic_paper_id = cap.id | |
| LEFT JOIN ec_exam_registration_subject eers ON | |
| eers.valuation_details->>'$.examregSubjectId' = ersf.id | |
| WHERE | |
| 1=1 $comonCondition | |
| -- eers.valuation_details->>'$.examregSubjectId' IS NULL | |
| "; | |
| $rows = $this->executeQueryForList($query); | |
| $examRegSubjectArray = []; | |
| // ====================Fees Migratiion - Subject Fees================= | |
| $commonFeeQuery = "SELECT DISTINCT | |
| er.id AS examRegID, | |
| er.revalDesc AS examregName, | |
| s.subjectName, | |
| e.semID, | |
| CONCAT(er.revalDesc,' ',erf.exam_fees_name) AS examfeesName, | |
| b.batchName, | |
| b.groups_id AS groupId, | |
| er.id, | |
| er.revalDesc, | |
| s.subjectName, | |
| e.semID, | |
| e_revaluation.batchID, | |
| erf.exam_fees_name, | |
| erf.exam_fees_amount, | |
| b.batchName, | |
| b.groups_id AS groupId, | |
| s.subjectID, | |
| erss.id AS examregSubjectId | |
| FROM | |
| exam_revaluation_student_subjects erss | |
| INNER JOIN | |
| exam_revaluation er ON erss.exam_revaluation_id = er.id | |
| INNER JOIN | |
| exam_revaluation_fees erf ON erf.exam_revaluation_id = er.id | |
| AND erf.id = erss.exam_revaluation_fees_id | |
| INNER JOIN | |
| exam e ON erss.examID = e.examID | |
| INNER JOIN | |
| subjects s ON s.subjectID = e.subjectID | |
| INNER JOIN | |
| exam e_revaluation ON e.subjectID = e_revaluation.subjectID | |
| AND e.semID = e_revaluation.semID | |
| AND e_revaluation.examregID = er.exam_registration_id | |
| INNER JOIN | |
| batches b ON b.batchID = e_revaluation.batchID | |
| WHERE | |
| er.exam_registration_id IS NOT NULL | |
| AND erf.isCommon = 0 | |
| $comonCondition"; | |
| $examRevaluations = $this->executeQueryForList($commonFeeQuery); | |
| $examRevaluationDetails = []; | |
| foreach ($examRevaluations as $examRevaluation) { | |
| $examRevaluation->revalDesc = trim($examRevaluation->revalDesc); | |
| if($examRevaluation->examregSubjectId > 0) { | |
| $examRevaluation->exam_fees_amount = (int) $examRevaluation->exam_fees_amount; | |
| if(!isset($examRevaluationDetails[$examRevaluation->revalDesc])) { | |
| $examRevaluationDetails[$examRevaluation->revalDesc]['batches'] = []; | |
| } | |
| if(!isset($examRevaluationDetails[$examRevaluation->revalDesc]['batches'][$examRevaluation->batchID])) { | |
| $examRevaluationDetails[$examRevaluation->revalDesc]['batches'][$examRevaluation->batchID]['feeHeads'] = []; | |
| } | |
| $examRegSubjectDetails = reset(array_filter($rows,function($elem)use($examRevaluation){ | |
| return $examRevaluation->batchID == $elem->batchId && $examRevaluation->subjectID == $elem->subjectId && $examRevaluation->semID == $elem->semId && $examRevaluation->examRegID == $elem->examRegId; | |
| })); | |
| $examRevaluationDetails[$examRevaluation->revalDesc]['batches'][$examRevaluation->batchID]['feeHeads'] [$examRevaluation->revalDesc." ".$examRevaluation->exam_fees_name] [] = ["name" => $examRevaluation->subjectName,"amount" => $examRevaluation->exam_fees_amount,"examregBatchId"=>$examRegSubjectDetails->examregBatchId,"paperSubjectId"=>$examRegSubjectDetails->paperSubjectId,"examregSubjectId"=>$examRegSubjectDetails->examregSubjectId,"assessmentId"=>$examRegSubjectDetails->assessmentId]; | |
| $examRevaluationDetails[$examRevaluation->revalDesc]['id'] = $examRevaluation->id; | |
| $examRevaluationDetails[$examRevaluation->revalDesc]['batches'][$examRevaluation->batchID]['semesterId'] = $examRevaluation->semID; | |
| $examRevaluationDetails[$examRevaluation->revalDesc]['batches'][$examRevaluation->batchID]['batchName'] = $examRevaluation->batchName; | |
| $examRevaluationDetails[$examRevaluation->revalDesc]['batches'][$examRevaluation->batchID]['groupId'] = $examRevaluation->groupId; | |
| } | |
| } | |
| foreach($examRevaluationDetails as $examRevaluationName => $examRevaluation) { | |
| if(empty($examRevaluation['id'])) continue; | |
| foreach($examRevaluation['batches'] as $batchId => $batchDetails) { | |
| $templateForOtherModuleRequest = new TemplateForOtherModulesRequest(); | |
| $templateForOtherModuleRequest->name= $examRevaluationName." Subject Fee ".$batchDetails['batchName']; | |
| foreach($batchDetails['feeHeads'] as $feeHeadName => $feeSubHead) { | |
| $templateForOtherModuleRequest->feeHeads [] = ["name" => $feeHeadName,"hasSubHeads"=>"1","feeSubHeads" => $feeSubHead]; | |
| } | |
| $templateForOtherModuleRequest->module = "EXAM_CONTROLLER"; | |
| $templateForOtherModuleRequest->groupId = $batchDetails['groupId']; | |
| $templateForOtherModuleRequest->semesterId = $batchDetails['semesterId']; | |
| $examRegSubjectFees = TemplateService::getInstance()->saveTemplateForOtherModules($templateForOtherModuleRequest); | |
| $this->executeQuery("UPDATE | |
| ec_exam_registration eer | |
| INNER JOIN exam_supplementary er ON | |
| er.ec_exam_registration_id = eer.id SET | |
| eer.properties = JSON_SET( eer.properties, | |
| '$.subjectFeeTemplateId', '$examRegSubjectFees->id', | |
| '$.subjectFeeSubjectHeadId', '".reset($examRegSubjectFees->feeRules)["id"]."' | |
| ) | |
| WHERE | |
| er.id = '".$examRevaluation['id']."'"); | |
| foreach (reset($templateForOtherModuleRequest->feeHeads)['feeSubHeads'] as $feeSubHead) { | |
| if(empty($feeSubHead['examregSubjectId'])) continue; | |
| $examRegSubjectArray[] = "('".$feeSubHead['examregBatchId']."','".$feeSubHead['paperSubjectId']."',".reset($examRegSubjectFees->feeRules)["id"].",JSON_OBJECT('examregSubjectId','".$feeSubHead['examregSubjectId']."'),'".$feeSubHead['assessmentId']."',$staffId)"; | |
| } | |
| } | |
| } | |
| // foreach ($rows as $row) { | |
| // if (empty($examRegistrationSubjectFeeHead[$row->examRegistrationId])) { | |
| // $sql="INSERT INTO fm_fee_head (name,description,is_active,created_by,updated_by,created_date,updated_date,account_id,has_subheads,frequency,refundable_fee_head,fm_head_group_id,module) VALUES | |
| // ('$row->examRegistationName-Subject-Fee-Head','',1,$staffId,$staffId,now(),now(),NULL,1,'CUSTOM',0,NULL,'EXAM_CONTROLLER') ON DUPLICATE KEY UPDATE updated_date=now()"; | |
| // $id = $this->executeQuery($sql,true)->id; | |
| // $examRegistrationSubjectFeeHead[$row->examRegistrationId] = $id; | |
| // } | |
| // $subjectName = $this->realEscapeString($row->subjectName); | |
| // $sql = "INSERT INTO fm_fee_subhead (name,frequency,fm_fee_head_id,`year`,created_by,created_date,updated_by,updated_date) VALUES | |
| // ('$subjectName',NULL,".$examRegistrationSubjectFeeHead[$row->examRegistrationId].",NULL,$staffId,now(),NULL,NULL) ON DUPLICATE KEY UPDATE updated_date=now()"; | |
| // $feeHeadId = $this->executeQuery($sql,true)->id; | |
| // $feeHeadId = $feeHeadId > 0 ? $feeHeadId : $this->executeQueryForObject("SELECT | |
| // id | |
| // FROM | |
| // fm_fee_subhead ffs | |
| // WHERE | |
| // name = '$subjectName' | |
| // AND fm_fee_head_id = ".$examRegistrationSubjectFeeHead[$row->examRegistrationId])->id; | |
| // $examRegSubjectArray[] = "('$row->examregBatchId','$row->paperSubjectId','$feeHeadId',JSON_OBJECT('examregSubjectId',$row->examregSubjectId), '$row->assessmentId',$staffId)"; | |
| // } | |
| $examRegSubjectValues = implode(',',$examRegSubjectArray); | |
| $sql = "INSERT INTO ec_exam_registration_subject(ec_exam_registration_batch_id, cm_academic_paper_subjects_id, fm_head_id, valuation_details, am_assessment_id, created_by) VALUES $examRegSubjectValues | |
| ON DUPLICATE KEY UPDATE | |
| am_assessment_id=VALUES(am_assessment_id), | |
| fm_head_id=VALUES(fm_head_id)"; | |
| if (!empty($examRegSubjectArray) && $examRegSubjectValues) { | |
| $result = $this->executeQuery($sql,true); | |
| } | |
| $migrationDetails->endTime = time(); | |
| $migrationDetails->status = "SUCCESS"; | |
| $migrationDetails->batchId = implode(',',$taskQueRequest->batchId); | |
| $this->updateMigrationTableLogs($migrationDetails, "ec_exam_registration_subject",$staffId); | |
| } catch (\Exception $e) { | |
| $migrationDetails->endTime = time(); | |
| $migrationDetails->status = "FAILED"; | |
| $migrationDetails->batchId = implode(',',$taskQueRequest->batchId); | |
| $this->updateMigrationTableLogs($migrationDetails, "ec_exam_registration_subject",$staffId); | |
| throw new ProfessionalException($e->getMessage(), $e->getCode()); | |
| } | |
| return true; | |
| } | |
| /** | |
| * Migrate table | |
| */ | |
| public function migrate_am_assessment() | |
| { | |
| try { | |
| $staffId = $_SESSION['adminID']; | |
| $migrationDetails = new \stdClass(); | |
| $examRegistrationSubjectFeeHead = []; | |
| $migrationDetails->startTime = time(); | |
| $migrationDetails->tables = ["exam"]; | |
| $query = "INSERT IGNORE INTO am_assessment(id,name,description,am_instance_id,properties_value,identifying_context,permissions,is_active,created_by) | |
| SELECT | |
| LEFT(REPLACE(UUID(),'-',''), 17), | |
| e.examName, | |
| e.examName, | |
| (SELECT id FROM am_instance ai WHERE ai.code='REGULAR'), | |
| JSON_OBJECT('endTime', e.examEndTime, 'startTime', e.examStartTime, 'assessmentDate', e.examDate, 'assessmentSubmissionDate', '','examCode', e.examCode,'isRoundOf',IF(e.isRoundOff=1,TRUE,FALSE),'examId',e.examID), | |
| JSON_OBJECT('module', 'EXAM_CONTROLLER', 'context', 'ASSESSMENT_MANAGEMENT', 'academicPaperSubjectId', caps.id,'batchId',e.batchID,'groupId', g.id,'subjectId', e.subjectID,'examRegistrationId', eer.id), | |
| JSON_OBJECT(), | |
| 1, | |
| $staffId | |
| FROM | |
| exam e | |
| INNER JOIN batches b ON | |
| b.batchID = e.batchID | |
| INNER JOIN exam_registration er ON | |
| er.examregID = e.examregID | |
| INNER JOIN `groups` g ON | |
| g.id = b.groups_id | |
| AND g.`type`='BATCH' | |
| INNER JOIN academic_term atm ON | |
| atm.id = e.semID | |
| INNER JOIN cm_curriculum cc ON | |
| cc.id = CAST(g.properties->>'$.curriculumId' AS CHAR) | |
| AND cc.properties->>'$.programId' = g.properties->>'$.programId' | |
| INNER JOIN cm_curriculum_syllabus_relation ccsr ON | |
| ccsr.cm_curriculum_id = cc.id | |
| INNER JOIN cm_syllabus cs ON | |
| cs.id = ccsr.cm_syllabus_id | |
| INNER JOIN cm_syllabus_academic_term_settings csats ON | |
| csats.academic_term_id = atm.id | |
| AND csats.cm_syllabus_id = cs.id | |
| INNER JOIN cm_academic_paper cap ON | |
| cap.cm_syllabus_academic_term_settings_id = csats.id | |
| INNER JOIN cm_academic_paper_subjects caps ON | |
| caps.subject_id = e.subjectID | |
| AND caps.cm_academic_paper_id = cap.id | |
| INNER JOIN ec_exam_registration eer ON | |
| eer.id = er.ec_exam_registration_id | |
| AND eer.`type` = 'REGULAR' | |
| WHERE e.am_assessment_id IS NULL"; | |
| $result = $this->executeQuery($query); | |
| $migrationDetails->endTime = time(); | |
| $migrationDetails->status = "SUCCESS"; | |
| $migrationDetails->recordCount = $result->sqlResult->num_rows; | |
| $this->updateMigrationTables($migrationDetails, "ec_am_assessment"); | |
| // Supply | |
| $migrationDetails = new \stdClass(); | |
| $migrationDetails->startTime = time(); | |
| $migrationDetails->tables = ["exam"]; | |
| $query = "INSERT IGNORE INTO am_assessment(id,name,description,am_instance_id,properties_value,identifying_context,permissions,is_active,created_by) | |
| SELECT | |
| LEFT(REPLACE(UUID(),'-',''), 17), | |
| e.examName, | |
| e.examName, | |
| (SELECT id FROM am_instance ai WHERE ai.code='RETEST'), | |
| JSON_OBJECT('endTime', e.examEndTime, 'startTime', e.examStartTime, 'assessmentDate', e.examDate, 'assessmentSubmissionDate', '','examCode', e.examCode,'isRoundOf',IF(e.isRoundOff=1,TRUE,FALSE),'examId',e.examID,'externalMaxMark',e.examTotalMarks), | |
| JSON_OBJECT('batchId',e.batchID,'groupId', g.id,'subjectId', e.subjectID,'paperSubjectId',caps.id,'examRegistrationId', eer.id), | |
| JSON_OBJECT(), | |
| 1, | |
| $staffId | |
| FROM | |
| exam e | |
| INNER JOIN batches b ON | |
| b.batchID = e.batchID | |
| INNER JOIN exam_supplementary es ON | |
| es.id = e.supply_examreg_id | |
| INNER JOIN `groups` g ON | |
| g.id = b.groups_id | |
| AND g.`type`='BATCH' | |
| INNER JOIN academic_term atm ON | |
| atm.id = e.semID | |
| INNER JOIN cm_curriculum cc ON | |
| cc.id = CAST(g.properties->>'$.curriculumId' AS CHAR) | |
| AND cc.properties->>'$.programId' = g.properties->>'$.programId' | |
| INNER JOIN cm_curriculum_syllabus_relation ccsr ON | |
| ccsr.cm_curriculum_id = cc.id | |
| INNER JOIN cm_syllabus cs ON | |
| cs.id = ccsr.cm_syllabus_id | |
| INNER JOIN cm_syllabus_academic_term_settings csats ON | |
| csats.academic_term_id = atm.id | |
| AND csats.cm_syllabus_id = cs.id | |
| INNER JOIN cm_academic_paper cap ON | |
| cap.cm_syllabus_academic_term_settings_id = csats.id | |
| INNER JOIN cm_academic_paper_subjects caps ON | |
| caps.subject_id = e.subjectID | |
| AND caps.cm_academic_paper_id = cap.id | |
| INNER JOIN ec_exam_registration eer ON | |
| eer.id = es.ec_exam_registration_id | |
| AND eer.`type` = 'SUPPLEMENTARY' | |
| WHERE | |
| e.am_assessment_id IS NULL"; | |
| $result = $this->executeQuery($query); | |
| $migrationDetails->endTime = time(); | |
| $migrationDetails->status = "SUCCESS"; | |
| $migrationDetails->recordCount = $result->sqlResult->num_rows; | |
| $this->updateMigrationTables($migrationDetails, "ec_am_assessment"); | |
| // other exams | |
| $migrationDetails = new \stdClass(); | |
| $migrationDetails->startTime = time(); | |
| $migrationDetails->tables = ["exam"]; | |
| $query = "INSERT IGNORE INTO am_assessment(id,name,description,am_instance_id,properties_value,identifying_context,permissions,is_active,created_by) | |
| SELECT | |
| LEFT(REPLACE(UUID(),'-',''), 17), | |
| e.examName, | |
| e.examName, | |
| (SELECT id FROM am_instance ai WHERE ai.code='OTHER'), | |
| JSON_OBJECT('endTime', e.examEndTime, 'startTime', e.examStartTime, 'assessmentDate', e.examDate, 'assessmentSubmissionDate', '','examCode', e.examCode,'isRoundOf',IF(e.isRoundOff=1,TRUE,FALSE),'examId',e.examID,'externalMaxMark',e.examTotalMarks), | |
| JSON_OBJECT('batchId',e.batchID,'groupId', g.id,'subjectId', e.subjectID,'paperSubjectId',caps.id), | |
| JSON_OBJECT(), | |
| 1, | |
| $staffId | |
| FROM | |
| exam e | |
| INNER JOIN batches b ON | |
| b.batchID = e.batchID | |
| INNER JOIN `groups` g ON | |
| g.id = b.groups_id | |
| AND g.`type`='BATCH' | |
| INNER JOIN academic_term atm ON | |
| atm.id = e.semID | |
| INNER JOIN cm_curriculum cc ON | |
| cc.id = CAST(g.properties->>'$.curriculumId' AS CHAR) | |
| AND cc.properties->>'$.programId' = g.properties->>'$.programId' | |
| INNER JOIN cm_curriculum_syllabus_relation ccsr ON | |
| ccsr.cm_curriculum_id = cc.id | |
| INNER JOIN cm_syllabus cs ON | |
| cs.id = ccsr.cm_syllabus_id | |
| INNER JOIN cm_syllabus_academic_term_settings csats ON | |
| csats.academic_term_id = atm.id | |
| AND csats.cm_syllabus_id = cs.id | |
| INNER JOIN cm_academic_paper cap ON | |
| cap.cm_syllabus_academic_term_settings_id = csats.id | |
| INNER JOIN cm_academic_paper_subjects caps ON | |
| caps.subject_id = e.subjectID | |
| AND caps.cm_academic_paper_id = cap.id | |
| WHERE | |
| e.am_assessment_id IS NULL | |
| AND e.examregID IS NULL | |
| AND e.supply_examreg_id IS NULL"; | |
| $result = $this->executeQuery($query); | |
| $sql = "UPDATE exam e INNER JOIN am_assessment aa ON e.examID=aa.properties_value->'$.examId' SET e.am_assessment_id=aa.id WHERE e.am_assessment_id IS NULL"; | |
| $result = $this->executeQuery($sql); | |
| $sql = "UPDATE | |
| ec_exam_registration_batch eerb | |
| INNER JOIN ec_exam_registration_subject eers ON | |
| eers.ec_exam_registration_batch_id = eerb.id | |
| INNER JOIN am_assessment aa ON | |
| aa.identifying_context->>'$.groupId' = eerb.groups_id | |
| AND aa.identifying_context->>'$.paperSubjectId' = eers.cm_academic_paper_subjects_id | |
| AND aa.identifying_context->>'$.examRegistrationId' = eerb.ec_exam_registration_id | |
| SET eers.am_assessment_id=aa.id"; | |
| $result = $this->executeQuery($sql); | |
| $result = $this->executeQuery("UPDATE am_assessment aa SET aa.properties_value = JSON_REMOVE(aa.properties_value, '$.examId')"); | |
| $result = $this->executeQuery("INSERT INTO oe_exams (id, name, description, `type`, identifying_context, properties, settings, user_type, is_deleted, created_by, updated_by, acknowledgement, is_archived) | |
| SELECT | |
| LEFT(REPLACE(UUID(),'-',''), 17), | |
| aa.name, | |
| aa.description, | |
| 'ASSESSMENT', | |
| aa.identifying_context, | |
| JSON_OBJECT('endTime', IF (aa.properties_value->>'$.assessmentDate'='0000-00-00' OR aa.properties_value->>'$.endTime' = 'null' OR aa.properties_value->>'$.assessmentDate' IS NULL OR aa.properties_value->>'$.endTime' IS NULL, '',STR_TO_DATE(CONCAT(aa.properties_value->>'$.assessmentDate',' ',aa.properties_value->>'$.endTime'),'%Y-%m-%d %l:%i %p')), 'startTime', IF (aa.properties_value->>'$.assessmentDate'='0000-00-00' OR aa.properties_value->>'$.startTime' = 'null' OR aa.properties_value->>'$.assessmentDate' IS NULL OR aa.properties_value->>'$.startTime' IS NULL, '',STR_TO_DATE(CONCAT(aa.properties_value->>'$.assessmentDate',' ',aa.properties_value->>'$.startTime'),'%Y-%m-%d %l:%i %p')), 'maxMark', '', 'isLocked', '1'), | |
| JSON_OBJECT('isValidated','0'), | |
| 'STAFF', | |
| '$staffId', | |
| '$staffId', | |
| NULL, | |
| 0 | |
| FROM | |
| am_assessment aa"); | |
| $migrationDetails->endTime = time(); | |
| $migrationDetails->status = "SUCCESS"; | |
| $migrationDetails->recordCount = $result->sqlResult->num_rows; | |
| $this->updateMigrationTables($migrationDetails, "ec_am_assessment"); | |
| } catch (\Exception $e) { | |
| $migrationDetails->endTime = time(); | |
| $migrationDetails->status = "FAILED"; | |
| $migrationDetails->recordCount = 0; | |
| $this->updateMigrationTables($migrationDetails, "ec_am_assessment"); | |
| throw new ProfessionalException($e->getMessage(), $e->getCode()); | |
| } | |
| return true; | |
| } | |
| /** | |
| * Migrate table | |
| */ | |
| public function migrate_ec_student_assessment_registration() | |
| { | |
| try { | |
| $staffId = $_SESSION['adminID']; | |
| $migrationDetails = new \stdClass(); | |
| $migrationDetails->startTime = time(); | |
| $migrationDetails->tables = ["exam_reg_studentsubject"]; | |
| $query = "INSERT INTO ec_student_assessment_registration(id,am_assessment_id,student_id,properties,valuation_details,created_by) | |
| SELECT | |
| LEFT(REPLACE(UUID(),'-',''), 17), | |
| e.am_assessment_id, | |
| ers.studentID, | |
| JSON_OBJECT( | |
| 'feeStatus', | |
| IF (ersc.paid=1,'PAID','UNPAID'), | |
| 'registrationStatus','REGISTERED', | |
| 'registeredDate',ersc.dateofRegistration, | |
| 'dateOfPay',ersc.dateOfPay | |
| ), | |
| JSON_OBJECT(), | |
| $staffId | |
| FROM | |
| exam_reg_studentsubject ers | |
| INNER JOIN studentaccount s ON | |
| s.studentID = ers.studentID | |
| INNER JOIN exam e ON | |
| e.examregID = ers.examregID | |
| AND e.subjectID = ers.subjectID | |
| AND e.batchID = s.batchID | |
| INNER JOIN exam_reg_studentchallan ersc ON | |
| ers.examregID = ersc.examregID | |
| AND ers.studentID = ersc.studentID | |
| INNER JOIN am_assessment aa ON | |
| aa.id = e.am_assessment_id | |
| ON DUPLICATE KEY UPDATE | |
| properties = VALUES(properties)"; | |
| $result = $this->executeQuery($query); | |
| $migrationDetails->endTime = time(); | |
| $migrationDetails->status = "SUCCESS"; | |
| $migrationDetails->recordCount = $result->sqlResult->num_rows; | |
| $this->updateMigrationTables($migrationDetails, "ec_student_assessment_registration"); | |
| // Supply | |
| $migrationDetails = new \stdClass(); | |
| $migrationDetails->startTime = time(); | |
| $migrationDetails->tables = ["exam_supplementary_student_subjects"]; | |
| $query = "INSERT INTO ec_student_assessment_registration(id,am_assessment_id,student_id,properties,valuation_details,created_by) | |
| SELECT | |
| LEFT(REPLACE(UUID(),'-',''), 17), | |
| e.am_assessment_id, | |
| esss.studentID, | |
| JSON_OBJECT('feeStatus',IF(essd.paid=1,'PAID','UNPAID'), | |
| 'registrationStatus','REGISTERED', | |
| 'registeredDate',essd.appliedDate, | |
| 'dateOfPay',essd.fee_paidDate, | |
| 'registrationType',IF(essd.isSupply=1,'SUPPLEMENTARY','IMPROVEMENT')), | |
| JSON_OBJECT(), | |
| $staffId | |
| FROM | |
| exam_supplementary_student_subjects esss | |
| INNER JOIN exam_supplementary_student_details essd ON | |
| esss.exam_supplementary_id = essd.exam_supplementary_id | |
| AND esss.studentID = essd.studentID | |
| INNER JOIN studentaccount s ON | |
| s.studentID = esss.studentID | |
| INNER JOIN exam t1 ON | |
| t1.examID = esss.examID | |
| INNER JOIN exam e ON | |
| e.subjectID = t1.subjectID | |
| AND e.batchID = t1.batchID | |
| AND e.supply_examreg_id = esss.exam_supplementary_id | |
| INNER JOIN am_assessment aa ON | |
| aa.id = e.am_assessment_id | |
| ON DUPLICATE KEY UPDATE | |
| properties = VALUES(properties)"; | |
| $result = $this->executeQuery($query); | |
| $migrationDetails->endTime = time(); | |
| $migrationDetails->status = "SUCCESS"; | |
| $migrationDetails->recordCount = $result->sqlResult->num_rows; | |
| $this->updateMigrationTables($migrationDetails, "ec_student_assessment_registration"); | |
| // Supply | |
| $migrationDetails = new \stdClass(); | |
| $migrationDetails->startTime = time(); | |
| $migrationDetails->tables = ["exam_revaluation_student_subjects"]; | |
| $query = "INSERT INTO ec_student_assessment_registration(id,am_assessment_id,student_id,properties,valuation_details,created_by) | |
| SELECT | |
| LEFT(REPLACE(UUID(),'-',''), 17), | |
| e.am_assessment_id, | |
| ersd.studentID, | |
| JSON_OBJECT('feeStatus',IF(ersd.paid=1,'PAID','UNPAID'), | |
| 'registrationStatus','REGISTERED', | |
| 'registeredDate',ersd.appliedDate, | |
| 'dateOfPay',ersd.fee_paid_date), | |
| JSON_OBJECT(), | |
| $staffId | |
| FROM | |
| studentaccount sa | |
| INNER JOIN exam_revaluation_student_details ersd ON | |
| sa.studentID = ersd.studentID | |
| INNER JOIN exam_revaluation_student_subjects erss ON | |
| erss.studentID = ersd.studentID | |
| AND ersd.exam_revaluation_id = erss.exam_revaluation_id | |
| AND erss.studentID = sa.studentID | |
| INNER JOIN exam e ON | |
| erss.examID = e.examID | |
| ON DUPLICATE KEY UPDATE | |
| properties = VALUES(properties)"; | |
| $result = $this->executeQuery($query); | |
| $migrationDetails->endTime = time(); | |
| $migrationDetails->status = "SUCCESS"; | |
| $migrationDetails->recordCount = $result->sqlResult->num_rows; | |
| $this->updateMigrationTables($migrationDetails, "ec_student_assessment_registration"); | |
| // Special exam | |
| $migrationDetails = new \stdClass(); | |
| $migrationDetails->startTime = time(); | |
| $migrationDetails->tables = ["special_exam_assigned_students"]; | |
| $query = "INSERT IGNORE INTO ec_student_assessment_registration(id,am_assessment_id,student_id,properties,valuation_details,created_by) | |
| SELECT | |
| LEFT(REPLACE(UUID(),'-',''), 17), | |
| e2.am_assessment_id, | |
| seas.studentaccount_id, | |
| JSON_OBJECT('feeStatus',IF(1=1,'PAID','UNPAID'), 'registrationStatus','REGISTERED'), | |
| JSON_OBJECT(), | |
| '$staffId' | |
| FROM | |
| special_exam_assigned_students seas | |
| INNER JOIN exam e ON | |
| e.examID = seas.exam_id | |
| INNER JOIN exam e2 ON | |
| e2.supply_examreg_id = seas.exam_supplementary_id | |
| AND e2.subjectID = e.subjectID | |
| AND e2.batchID = e.batchID | |
| AND e2.batchID = seas.batches_id | |
| AND e2.semID = e.semID"; | |
| $result = $this->executeQuery($query); | |
| $migrationDetails->endTime = time(); | |
| $migrationDetails->status = "SUCCESS"; | |
| $migrationDetails->recordCount = $result->sqlResult->num_rows; | |
| $this->updateMigrationTables($migrationDetails, "ec_student_assessment_registration"); | |
| } catch (\Exception $e) { | |
| $migrationDetails->endTime = time(); | |
| $migrationDetails->status = "FAILED"; | |
| $migrationDetails->recordCount = 0; | |
| $this->updateMigrationTables($migrationDetails, "ec_student_assessment_registration"); | |
| throw new ProfessionalException($e->getMessage(), $e->getCode()); | |
| } | |
| return true; | |
| } | |
| /** | |
| * Migrate table | |
| */ | |
| public function migrate_oe_student_total_mark() | |
| { | |
| try { | |
| $staffId = $_SESSION['adminID']; | |
| $migrationDetails = new \stdClass(); | |
| $migrationDetails->startTime = time(); | |
| $migrationDetails->tables = ["exammarks_external"]; | |
| $query = "INSERT IGNORE INTO `oe_student_total_mark` (oe_exams_id,am_assessment_id,student_id,mark_obtained,valuation_count,valuation_type,attendance_status,created_by) | |
| SELECT | |
| oee.id, | |
| e.am_assessment_id, | |
| ee.studentID, | |
| ee.mark, | |
| IF(ct.course_Type IN ('UG','UGPRO'),'FINALIZED','1'), | |
| NULL, | |
| IF(ea.isAbsent > 0,'ABSENT','PRESENT'), | |
| '$staffId' | |
| FROM | |
| exammarks_external ee | |
| INNER JOIN exam e ON | |
| e.examID = ee.examID | |
| INNER JOIN exam_attendance ea ON | |
| ea.examID = ee.examID | |
| AND ea.studentID = ee.studentID | |
| INNER JOIN batches b ON | |
| b.batchID = e.batchID | |
| INNER JOIN course_type ct ON | |
| ct.courseTypeID = b.courseTypeID | |
| INNER JOIN oe_exams oee ON | |
| oee.identifying_context->>'$.assessmentId' = e.am_assessment_id"; | |
| $result = $this->executeQuery($query); | |
| $migrationDetails->endTime = time(); | |
| $migrationDetails->status = "SUCCESS"; | |
| $migrationDetails->recordCount = $result->sqlResult->num_rows; | |
| $this->updateMigrationTables($migrationDetails, "oe_student_total_mark"); | |
| $migrationDetails = new \stdClass(); | |
| $migrationDetails->startTime = time(); | |
| $migrationDetails->tables = ["externalexammarks_finalized"]; | |
| $query = "INSERT IGNORE INTO `oe_student_total_mark` (oe_exams_id,am_assessment_id,student_id,mark_obtained,valuation_count,valuation_type,attendance_status,created_by) | |
| SELECT | |
| oee.id, | |
| e.am_assessment_id, | |
| ef.studentID, | |
| ef.mark, | |
| 'FINALIZED', | |
| NULL, | |
| IF(ea.isAbsent > 0,'ABSENT','PRESENT'), | |
| '$staffId' | |
| FROM | |
| externalexammarks_finalized ef | |
| INNER JOIN exam e ON | |
| e.examID = ef.examID | |
| INNER JOIN exam_attendance ea ON | |
| ea.examID = ef.examID | |
| AND ea.studentID = ef.studentID | |
| INNER JOIN oe_exams oee ON | |
| oee.identifying_context->>'$.assessmentId' = e.am_assessment_id"; | |
| $result = $this->executeQuery($query); | |
| $migrationDetails->endTime = time(); | |
| $migrationDetails->status = "SUCCESS"; | |
| $migrationDetails->recordCount = $result->sqlResult->num_rows; | |
| $this->updateMigrationTables($migrationDetails, "oe_student_total_mark"); | |
| $migrationDetails = new \stdClass(); | |
| $migrationDetails->startTime = time(); | |
| $migrationDetails->tables = ["external_exammarks"]; | |
| $query = "INSERT IGNORE INTO `oe_student_total_mark` (oe_exams_id,am_assessment_id,student_id,mark_obtained,valuation_count,valuation_type,attendance_status,created_by) | |
| SELECT | |
| oee.id, | |
| e.am_assessment_id, | |
| ee.studentID, | |
| ee.mark, | |
| IF(ee.valuationCount IS NULL, '2', '3'), | |
| NULL, | |
| IF(ea.isAbsent > 0,'ABSENT','PRESENT'), | |
| '$staffId' | |
| FROM | |
| external_exammarks ee | |
| INNER JOIN exam e ON | |
| e.examID = ee.examID | |
| INNER JOIN exam_attendance ea ON | |
| ea.examID = ee.examID | |
| AND ea.studentID = ee.studentID | |
| INNER JOIN oe_exams oee ON | |
| oee.identifying_context->>'$.assessmentId' = e.am_assessment_id"; | |
| $result = $this->executeQuery($query); | |
| $migrationDetails->endTime = time(); | |
| $migrationDetails->status = "SUCCESS"; | |
| $migrationDetails->recordCount = $result->sqlResult->num_rows; | |
| $this->updateMigrationTables($migrationDetails, "oe_student_total_mark"); | |
| $query = "UPDATE | |
| ec_student_assessment_registration esar | |
| INNER JOIN exam e ON | |
| e.am_assessment_id = esar.am_assessment_id | |
| INNER JOIN exam_attendance ea ON | |
| ea.examID = e.examID | |
| AND ea.studentID = esar.student_id SET | |
| esar.properties = JSON_SET( | |
| properties, | |
| '$.attendanceStatus', | |
| IF( | |
| ea.isAbsent = 0, | |
| 'PRSENT', | |
| IF( | |
| ea.isAbsent = 1, | |
| 'ABSENT', | |
| 'MAL' | |
| ) | |
| ) | |
| )"; | |
| $result = $this->executeQuery($query); | |
| } catch (\Exception $e) { | |
| $migrationDetails->endTime = time(); | |
| $migrationDetails->status = "FAILED"; | |
| $migrationDetails->recordCount = 0; | |
| $this->updateMigrationTables($migrationDetails, "oe_student_total_mark"); | |
| throw new ProfessionalException($e->getMessage(), $e->getCode()); | |
| } | |
| return true; | |
| } | |
| /** | |
| * Migrate table | |
| */ | |
| public function migrate_valuation_method() | |
| { | |
| try { | |
| $staffId = $_SESSION['adminID']; | |
| $migrationDetails = new \stdClass(); | |
| $migrationDetails->startTime = time(); | |
| $migrationDetails->tables = ["universityMarkListPassPercentConfigs"]; | |
| $query = "INSERT INTO valuation_method (id,identifying_context,`type`,properties,created_by) | |
| SELECT * FROM ( | |
| SELECT DISTINCT | |
| LEFT(REPLACE(UUID(),'-',''), 17), | |
| JSON_OBJECT('academicPaperSubjectId',caps.id,'passCriteriaType','INTERNAL'), | |
| 'ACADEMIC_PAPER_SUBJECT', | |
| JSON_ARRAY(JSON_OBJECT('type','PERCENTAGE','value',IF(umlppc.internalCutOff IS NULL,0,umlppc.internalCutOff) )), | |
| $staffId | |
| FROM | |
| cm_curriculum cc | |
| INNER JOIN cm_curriculum_syllabus_relation ccsr ON | |
| ccsr.cm_curriculum_id = cc.id | |
| INNER JOIN cm_syllabus cs ON | |
| cs.id = ccsr.cm_syllabus_id | |
| INNER JOIN cm_syllabus_academic_term_settings csats ON | |
| csats.cm_syllabus_id = cs.id | |
| INNER JOIN cm_academic_paper cap ON | |
| cap.cm_syllabus_academic_term_settings_id = csats.id | |
| INNER JOIN cm_academic_paper_subjects caps ON | |
| caps.cm_academic_paper_id = cap.id | |
| INNER JOIN `groups` g ON | |
| cc.id = CAST(g.properties->>'$.curriculumId' AS CHAR) | |
| AND cc.properties->>'$.programId' = g.properties->>'$.programId' | |
| AND g.`type`='BATCH' | |
| INNER JOIN program p ON | |
| CAST(g.properties->>'$.programId' AS CHAR) = p.id | |
| INNER JOIN | |
| universityMarkListPassPercentConfigs umlppc ON | |
| p.course_type_id = umlppc.course_type_id | |
| AND g.properties->>'$.startYear' = umlppc.batchYear | |
| GROUP BY caps.id) AS p"; | |
| $result = $this->executeQuery($query); | |
| $migrationDetails->endTime = time(); | |
| $migrationDetails->status = "SUCCESS"; | |
| $migrationDetails->recordCount = $result->sqlResult->num_rows; | |
| $this->updateMigrationTables($migrationDetails, "valuation_method"); | |
| $migrationDetails = new \stdClass(); | |
| $migrationDetails->startTime = time(); | |
| $migrationDetails->tables = ["universityMarkListPassPercentConfigs"]; | |
| $query = "INSERT INTO valuation_method (id,identifying_context,`type`,properties,created_by) | |
| SELECT * FROM ( | |
| SELECT DISTINCT | |
| LEFT(REPLACE(UUID(),'-',''), 17), | |
| JSON_OBJECT('academicPaperSubjectId',caps.id,'passCriteriaType','EXTERNAL'), | |
| 'ACADEMIC_PAPER_SUBJECT', | |
| JSON_ARRAY(JSON_OBJECT('type','PERCENTAGE','value',IF(umlppc.semExamCutOff IS NULL,0,umlppc.semExamCutOff) )), | |
| $staffId | |
| FROM | |
| cm_curriculum cc | |
| INNER JOIN cm_curriculum_syllabus_relation ccsr ON | |
| ccsr.cm_curriculum_id = cc.id | |
| INNER JOIN cm_syllabus cs ON | |
| cs.id = ccsr.cm_syllabus_id | |
| INNER JOIN cm_syllabus_academic_term_settings csats ON | |
| csats.cm_syllabus_id = cs.id | |
| INNER JOIN cm_academic_paper cap ON | |
| cap.cm_syllabus_academic_term_settings_id = csats.id | |
| INNER JOIN cm_academic_paper_subjects caps ON | |
| caps.cm_academic_paper_id = cap.id | |
| INNER JOIN `groups` g ON | |
| cc.id = CAST(g.properties->>'$.curriculumId' AS CHAR) | |
| AND cc.properties->>'$.programId' = g.properties->>'$.programId' | |
| AND g.`type`='BATCH' | |
| INNER JOIN program p ON | |
| CAST(g.properties->>'$.programId' AS CHAR) = p.id | |
| INNER JOIN | |
| universityMarkListPassPercentConfigs umlppc ON | |
| p.course_type_id = umlppc.course_type_id | |
| AND g.properties->>'$.startYear' = umlppc.batchYear | |
| GROUP BY caps.id) AS p"; | |
| $result = $this->executeQuery($query); | |
| $migrationDetails->endTime = time(); | |
| $migrationDetails->status = "SUCCESS"; | |
| $migrationDetails->recordCount = $result->sqlResult->num_rows; | |
| $this->updateMigrationTables($migrationDetails, "valuation_method"); | |
| $migrationDetails = new \stdClass(); | |
| $migrationDetails->startTime = time(); | |
| $migrationDetails->tables = ["universityMarkListPassPercentConfigs"]; | |
| $query = "INSERT INTO valuation_method (id,identifying_context,`type`,properties,created_by) | |
| SELECT * FROM ( | |
| SELECT DISTINCT | |
| LEFT(REPLACE(UUID(),'-',''), 17), | |
| JSON_OBJECT('academicPaperSubjectId',caps.id,'passCriteriaType','AGGREGATE'), | |
| 'ACADEMIC_PAPER_SUBJECT', | |
| JSON_ARRAY(JSON_OBJECT('type','PERCENTAGE','value',IF(umlppc.subjectCutOff IS NULL,0,umlppc.subjectCutOff) )), | |
| $staffId | |
| FROM | |
| cm_curriculum cc | |
| INNER JOIN cm_curriculum_syllabus_relation ccsr ON | |
| ccsr.cm_curriculum_id = cc.id | |
| INNER JOIN cm_syllabus cs ON | |
| cs.id = ccsr.cm_syllabus_id | |
| INNER JOIN cm_syllabus_academic_term_settings csats ON | |
| csats.cm_syllabus_id = cs.id | |
| INNER JOIN cm_academic_paper cap ON | |
| cap.cm_syllabus_academic_term_settings_id = csats.id | |
| INNER JOIN cm_academic_paper_subjects caps ON | |
| caps.cm_academic_paper_id = cap.id | |
| INNER JOIN `groups` g ON | |
| cc.id = CAST(g.properties->>'$.curriculumId' AS CHAR) | |
| AND cc.properties->>'$.programId' = g.properties->>'$.programId' | |
| AND g.`type`='BATCH' | |
| INNER JOIN program p ON | |
| CAST(g.properties->>'$.programId' AS CHAR) = p.id | |
| INNER JOIN | |
| universityMarkListPassPercentConfigs umlppc ON | |
| p.course_type_id = umlppc.course_type_id | |
| AND g.properties->>'$.startYear' = umlppc.batchYear | |
| GROUP BY caps.id) AS p"; | |
| $result = $this->executeQuery($query); | |
| $migrationDetails->endTime = time(); | |
| $migrationDetails->status = "SUCCESS"; | |
| $migrationDetails->recordCount = $result->sqlResult->num_rows; | |
| $this->updateMigrationTables($migrationDetails, "valuation_method"); | |
| $migrationDetails = new \stdClass(); | |
| $migrationDetails->startTime = time(); | |
| $migrationDetails->tables = ["universityMarkListPassPercentConfigs"]; | |
| $query = "INSERT INTO valuation_method (id,identifying_context,`type`,properties,created_by) | |
| SELECT * FROM ( | |
| SELECT | |
| LEFT(REPLACE(UUID(),'-',''), 17), | |
| JSON_OBJECT('syllabusId',cs.id,'academicTermId',csats.academic_term_id,'satsId',csats.id,'passCriteriaType','TERM'), | |
| 'SYLLABUS_ACADEMIC_TERM', | |
| JSON_ARRAY(JSON_OBJECT('type','PERCENTAGE','value',IF(umlppc.aggregateCutOff IS NULL,0,umlppc.aggregateCutOff) )), | |
| $staffId | |
| FROM | |
| cm_curriculum cc | |
| INNER JOIN cm_curriculum_syllabus_relation ccsr ON | |
| ccsr.cm_curriculum_id = cc.id | |
| INNER JOIN cm_syllabus cs ON | |
| cs.id = ccsr.cm_syllabus_id | |
| INNER JOIN cm_syllabus_academic_term_settings csats ON | |
| csats.cm_syllabus_id = cs.id | |
| INNER JOIN `groups` g ON | |
| cc.id = CAST(g.properties->>'$.curriculumId' AS CHAR) | |
| AND cc.properties->>'$.programId' = g.properties->>'$.programId' | |
| AND g.`type`='BATCH' | |
| INNER JOIN program p ON | |
| CAST(g.properties->>'$.programId' AS CHAR) = p.id | |
| INNER JOIN | |
| universityMarkListPassPercentConfigs umlppc ON | |
| p.course_type_id = umlppc.course_type_id | |
| AND g.properties->>'$.startYear' = umlppc.batchYear | |
| GROUP BY csats.id) AS p"; | |
| $result = $this->executeQuery($query); | |
| $migrationDetails->endTime = time(); | |
| $migrationDetails->status = "SUCCESS"; | |
| $migrationDetails->recordCount = $result->sqlResult->num_rows; | |
| $this->updateMigrationTables($migrationDetails, "valuation_method"); | |
| } catch (\Exception $e) { | |
| $migrationDetails->endTime = time(); | |
| $migrationDetails->status = "FAILED"; | |
| $migrationDetails->recordCount = 0; | |
| $this->updateMigrationTables($migrationDetails, "valuation_method"); | |
| throw new ProfessionalException($e->getMessage(), $e->getCode()); | |
| } | |
| return true; | |
| } | |
| /** | |
| * Migrate table | |
| */ | |
| public function migrate_grade_scheme() | |
| { | |
| try { | |
| $staffId = $_SESSION['adminID']; | |
| $migrationDetails = new \stdClass(); | |
| $migrationDetails->startTime = time(); | |
| $migrationDetails->tables = ["exam_gradingscheme"]; | |
| $query = "INSERT INTO grade_scheme(id,identifying_context,`type`,name,properties,created_by) | |
| SELECT | |
| LEFT(REPLACE(UUID(),'-',''), 17), | |
| JSON_OBJECT(), | |
| IF(eg.scheme_type='PERCENTAGE','PERCENTAGE','CREDIT'), | |
| eg.schemeName, | |
| JSON_OBJECT('courseTypeId',IF(eg.courseTypeID IS NULL,'',eg.courseTypeID),'gradeSchemeId',eg.schemeID), | |
| $staffId | |
| FROM | |
| exam_gradingscheme eg"; | |
| $result = $this->executeQuery($query); | |
| $result = $this->executeQuery("UPDATE | |
| cm_curriculum cc | |
| INNER JOIN cm_curriculum_syllabus_relation ccsr ON | |
| ccsr.cm_curriculum_id = cc.id | |
| INNER JOIN cm_syllabus cs ON | |
| cs.id = ccsr.cm_syllabus_id | |
| INNER JOIN cm_syllabus_academic_term_settings csats ON | |
| csats.cm_syllabus_id = cs.id | |
| INNER JOIN cm_academic_paper cap ON | |
| cap.cm_syllabus_academic_term_settings_id = csats.id | |
| INNER JOIN cm_academic_paper_subjects caps ON | |
| caps.cm_academic_paper_id = cap.id | |
| INNER JOIN `groups` g ON | |
| cc.id = CAST(g.properties->>'$.curriculumId' AS CHAR) | |
| AND cc.properties->>'$.programId' = g.properties->>'$.programId' | |
| AND g.`type`='BATCH' | |
| INNER JOIN batches b ON b.groups_id = g.id | |
| INNER JOIN assignedSubjectGradeBatches asgb ON | |
| asgb.batches_id = b.batchID | |
| INNER JOIN grade_scheme gs ON | |
| gs.properties->>'$.gradeSchemeId' = asgb.exam_gradingscheme_id | |
| SET caps.properties=JSON_MERGE_PATCH(caps.properties,JSON_OBJECT('gradeSchemId',gs.id))"); | |
| $migrationDetails->endTime = time(); | |
| $migrationDetails->status = "SUCCESS"; | |
| $migrationDetails->recordCount = $result->sqlResult->num_rows; | |
| $this->updateMigrationTables($migrationDetails, "grade_scheme"); | |
| $migrationDetails = new \stdClass(); | |
| $migrationDetails->startTime = time(); | |
| $migrationDetails->tables = ["exam_gradingscheme","exam_semestergrade"]; | |
| $query = "INSERT INTO grade_scheme(id,identifying_context,`type`,name,properties,created_by) | |
| SELECT DISTINCT | |
| LEFT(REPLACE(UUID(),'-',''), 17), | |
| JSON_OBJECT(), | |
| IF(eg.scheme_type='PERCENTAGE','PERCENTAGE','CREDIT'), | |
| CONCAT('Semester ', eg.schemeName), | |
| JSON_OBJECT('courseTypeId',IF(eg.courseTypeID IS NULL,'',eg.courseTypeID),'semGradeSchemeId',eg.schemeID), | |
| $staffId | |
| FROM | |
| exam_gradingscheme eg | |
| INNER JOIN exam_semestergrade esg ON eg.schemeID=esg.schemeID"; | |
| $result = $this->executeQuery($query); | |
| $result = $this->executeQuery("UPDATE | |
| cm_curriculum cc | |
| INNER JOIN cm_curriculum_syllabus_relation ccsr ON | |
| ccsr.cm_curriculum_id = cc.id | |
| INNER JOIN cm_syllabus cs ON | |
| cs.id = ccsr.cm_syllabus_id | |
| INNER JOIN cm_syllabus_academic_term_settings csats ON | |
| csats.cm_syllabus_id = cs.id | |
| INNER JOIN `groups` g ON | |
| cc.id = CAST(g.properties->>'$.curriculumId' AS CHAR) | |
| AND cc.properties->>'$.programId' = g.properties->>'$.programId' | |
| AND g.`type`='BATCH' | |
| INNER JOIN batches b ON b.groups_id = g.id | |
| INNER JOIN assignedSemesterGradeBatches asgb ON | |
| asgb.batches_id = b.batchID | |
| INNER JOIN grade_scheme gs ON | |
| gs.properties->>'$.semGradeSchemeId' = asgb.exam_gradingscheme_id | |
| SET csats.properties= JSON_SET(csats.properties,'$.gradeSchemId',gs.id)"); | |
| $migrationDetails->endTime = time(); | |
| $migrationDetails->status = "SUCCESS"; | |
| $migrationDetails->recordCount = $result->sqlResult->num_rows; | |
| $this->updateMigrationTables($migrationDetails, "grade_scheme"); | |
| $migrationDetails = new \stdClass(); | |
| $migrationDetails->startTime = time(); | |
| $migrationDetails->tables = ["exam_cgpa_gradingscheme"]; | |
| $query = "INSERT INTO grade_scheme(id,identifying_context,`type`,name,properties,created_by) | |
| SELECT | |
| LEFT(REPLACE(UUID(),'-',''), 17), | |
| JSON_OBJECT(), | |
| 'CREDIT', | |
| CONCAT('CGPA ',eg.schemeName), | |
| JSON_OBJECT('courseTypeId',IF(eg.courseTypeID IS NULL,'',eg.courseTypeID),'cgpaGradeSchemeId',eg.schemeID), | |
| $staffId | |
| FROM | |
| exam_cgpa_gradingscheme eg"; | |
| $result = $this->executeQuery($query); | |
| $result = $this->executeQuery("UPDATE | |
| cm_curriculum cc | |
| INNER JOIN cm_curriculum_syllabus_relation ccsr ON | |
| ccsr.cm_curriculum_id = cc.id | |
| INNER JOIN cm_syllabus cs ON | |
| cs.id = ccsr.cm_syllabus_id | |
| INNER JOIN `groups` g ON | |
| cc.id = CAST(g.properties->>'$.curriculumId' AS CHAR) | |
| AND cc.properties->>'$.programId' = g.properties->>'$.programId' | |
| AND g.`type`='BATCH' | |
| INNER JOIN batches b ON b.groups_id = g.id | |
| INNER JOIN assignedCGPAGradeBatches acb ON | |
| acb.batches_id = b.batchID | |
| INNER JOIN grade_scheme gs ON | |
| gs.properties->>'$.cgpaGradeSchemeId' = acb.exam_cgpa_gradingscheme_id | |
| SET cs.properties= JSON_MERGE_PATCH(cs.properties,JSON_OBJECT('gradeSchemId',gs.id)), | |
| cc.properties=JSON_MERGE_PATCH(cc.properties,JSON_OBJECT('gradeSchemId',gs.id))"); | |
| $migrationDetails->endTime = time(); | |
| $migrationDetails->status = "SUCCESS"; | |
| $migrationDetails->recordCount = $result->sqlResult->num_rows; | |
| $this->updateMigrationTables($migrationDetails, "grade_scheme"); | |
| } catch (\Exception $e) { | |
| $migrationDetails->endTime = time(); | |
| $migrationDetails->status = "FAILED"; | |
| $migrationDetails->recordCount = 0; | |
| $this->updateMigrationTables($migrationDetails, "grade_scheme"); | |
| throw new ProfessionalException($e->getMessage(), $e->getCode()); | |
| } | |
| return true; | |
| } | |
| /** | |
| * Migrate table | |
| */ | |
| public function migrate_grade() | |
| { | |
| try { | |
| $staffId = $_SESSION['adminID']; | |
| $migrationDetails = new \stdClass(); | |
| $migrationDetails->startTime = time(); | |
| $migrationDetails->tables = ["exam_gradepoints"]; | |
| $query = "INSERT INTO grade(id,grade_scheme_id,range_from,range_to,name,properties,created_by) | |
| SELECT | |
| LEFT(REPLACE(UUID(),'-',''), 17), | |
| gs.id, | |
| percentFrom, | |
| percentTo, | |
| letterGrade, | |
| JSON_OBJECT('class',IF(eg.className IS NULL, '', eg.className), 'failStatus', eg.failStatus, 'gradePoint', eg.gradePoint, 'gradePointId', eg.gradePointID), | |
| $staffId | |
| FROM | |
| exam_gradepoints eg | |
| INNER JOIN grade_scheme gs ON gs.properties->'$.gradeSchemeId' = eg.schemeID"; | |
| $result = $this->executeQuery($query); | |
| $migrationDetails->endTime = time(); | |
| $migrationDetails->status = "SUCCESS"; | |
| $migrationDetails->recordCount = $result->sqlResult->num_rows; | |
| $this->updateMigrationTables($migrationDetails, "grade"); | |
| $migrationDetails = new \stdClass(); | |
| $migrationDetails->startTime = time(); | |
| $migrationDetails->tables = ["exam_semestergrade"]; | |
| $query = "INSERT IGNORE INTO grade(id,grade_scheme_id,range_from,range_to,name,properties,created_by) | |
| SELECT | |
| LEFT(REPLACE(UUID(),'-',''), 17), | |
| gs.id, | |
| gradePointFrom, | |
| gradePointTo, | |
| letterGrade, | |
| JSON_OBJECT('class',IF(es.className IS NULL, '', es.className), 'failStatus', IF(es.letterGrade='F',TRUE,FALSE), 'gradePointId', es.semGradeID), | |
| $staffId | |
| FROM | |
| exam_semestergrade es | |
| INNER JOIN grade_scheme gs ON gs.properties->'$.semGradeSchemeId' = es.schemeID"; | |
| $result = $this->executeQuery($query); | |
| $migrationDetails->endTime = time(); | |
| $migrationDetails->status = "SUCCESS"; | |
| $migrationDetails->recordCount = $result->sqlResult->num_rows; | |
| $this->updateMigrationTables($migrationDetails, "grade"); | |
| $migrationDetails = new \stdClass(); | |
| $migrationDetails->startTime = time(); | |
| $migrationDetails->tables = ["exam_cgpa_gradepoints"]; | |
| $query = "INSERT IGNORE INTO grade(id,grade_scheme_id,range_from,range_to,name,properties,created_by) | |
| SELECT | |
| LEFT(REPLACE(UUID(),'-',''), 17), | |
| gs.id, | |
| es.percentFrom, | |
| es.percentTo, | |
| es.letterGrade, | |
| JSON_OBJECT('class',IF(es.classname IS NULL, '', es.classname), 'failStatus', es.failstatus, 'gradePointId', es.gradePointID), | |
| $staffId | |
| FROM | |
| exam_cgpa_gradepoints es | |
| INNER JOIN grade_scheme gs ON gs.properties->'$.cgpaGradeSchemeId' = es.schemeID"; | |
| $result = $this->executeQuery($query); | |
| $migrationDetails->endTime = time(); | |
| $migrationDetails->status = "SUCCESS"; | |
| $migrationDetails->recordCount = $result->sqlResult->num_rows; | |
| $this->updateMigrationTables($migrationDetails, "grade"); | |
| } catch (\Exception $e) { | |
| $migrationDetails->endTime = time(); | |
| $migrationDetails->status = "FAILED"; | |
| $migrationDetails->recordCount = 0; | |
| $this->updateMigrationTables($migrationDetails, "grade"); | |
| throw new ProfessionalException($e->getMessage(), $e->getCode()); | |
| } | |
| return true; | |
| } | |
| /** | |
| * Migrate table | |
| */ | |
| public function migrate_common() | |
| { | |
| try { | |
| $staffId = $_SESSION['adminID']; | |
| $migrationDetails = new \stdClass(); | |
| $migrationDetails->startTime = time(); | |
| $migrationDetails->tables = ["cm_academic_paper_subjects"]; | |
| $query = "UPDATE | |
| cm_curriculum cc | |
| INNER JOIN cm_curriculum_syllabus_relation ccsr ON | |
| ccsr.cm_curriculum_id = cc.id | |
| INNER JOIN cm_syllabus cs ON | |
| cs.id = ccsr.cm_syllabus_id | |
| INNER JOIN cm_syllabus_academic_term_settings csats ON | |
| csats.cm_syllabus_id = cs.id | |
| INNER JOIN cm_academic_paper cap ON | |
| cap.cm_syllabus_academic_term_settings_id = csats.id | |
| INNER JOIN cm_academic_paper_subjects caps ON | |
| caps.cm_academic_paper_id = cap.id | |
| INNER JOIN `groups` g ON | |
| cc.id = CAST(g.properties->>'$.curriculumId' AS CHAR) | |
| AND cc.properties->>'$.programId' = g.properties->>'$.programId' | |
| AND g.`type`='BATCH' | |
| INNER JOIN batches b ON b.groups_id = g.id | |
| INNER JOIN academic_term at2 ON at2.id=csats.academic_term_id | |
| INNER JOIN exam_subjectcredit es ON | |
| es.subjectID = caps.subject_id | |
| AND es.semID = at2.id | |
| AND es.batchID = b.batchID | |
| SET | |
| caps.properties = JSON_MERGE_PATCH( | |
| caps.properties, | |
| JSON_OBJECT( | |
| 'credit', | |
| es.credit, | |
| 'isInternal', | |
| IF( | |
| es.isInternal = 1, | |
| TRUE, | |
| FALSE | |
| ), | |
| 'isExternal', | |
| IF( | |
| es.isExternal = 1, | |
| TRUE, | |
| FALSE | |
| ), | |
| 'excludeSubjectFromTotal', | |
| IF( | |
| es.excludeSubjectFromTotal = 1, | |
| TRUE, | |
| FALSE | |
| ), | |
| 'order', | |
| IF( | |
| es.subjectOrder IS NULL, | |
| '', | |
| es.subjectOrder | |
| ) | |
| ) | |
| )"; | |
| $result = $this->executeQuery($query); | |
| $migrationDetails->endTime = time(); | |
| $migrationDetails->status = "SUCCESS"; | |
| $migrationDetails->recordCount = $result->sqlResult->num_rows; | |
| $this->updateMigrationTables($migrationDetails, "common"); | |
| $migrationDetails = new \stdClass(); | |
| $migrationDetails->startTime = time(); | |
| $migrationDetails->tables = ["cm_academic_paper_subjects"]; | |
| $query = "UPDATE cm_academic_paper_subjects a INNER JOIN ( | |
| SELECT | |
| max(e.examTotalMarks) AS maxmark, | |
| caps.id | |
| FROM | |
| exam e | |
| INNER JOIN am_assessment aa ON | |
| aa.id = e.am_assessment_id | |
| INNER JOIN cm_academic_paper_subjects caps ON | |
| caps.id = CAST(aa.identifying_context->>'$.academicPaperSubjectId' AS CHAR) | |
| AND caps.subject_id = e.subjectID | |
| GROUP BY caps.id | |
| ) AS b ON b.id = a.id | |
| SET a.properties = JSON_SET(a.properties,'$.externalMaxMark',b.maxmark)"; | |
| $result = $this->executeQuery($query); | |
| $migrationDetails->endTime = time(); | |
| $migrationDetails->status = "SUCCESS"; | |
| $migrationDetails->recordCount = $result->sqlResult->num_rows; | |
| $this->updateMigrationTables($migrationDetails, "common"); | |
| $migrationDetails = new \stdClass(); | |
| $migrationDetails->startTime = time(); | |
| $migrationDetails->tables = ["coursetype_code"]; | |
| $query = "UPDATE coursetype_code cc INNER JOIN batches b ON b.batchID=cc.batchID SET cc.groups_id = b.groups_id"; | |
| $result = $this->executeQuery($query); | |
| $migrationDetails->endTime = time(); | |
| $migrationDetails->status = "SUCCESS"; | |
| $migrationDetails->recordCount = $result->sqlResult->num_rows; | |
| $this->updateMigrationTables($migrationDetails, "common"); | |
| $migrationDetails = new \stdClass(); | |
| $migrationDetails->startTime = time(); | |
| $migrationDetails->tables = ["coursetype_code"]; | |
| $query = "UPDATE | |
| marklist_categorize_subjects mcs | |
| INNER JOIN batches b ON | |
| b.batchID = mcs.batches_id SET | |
| mcs.groups_id = b.groups_id"; | |
| $result = $this->executeQuery($query); | |
| $migrationDetails->endTime = time(); | |
| $migrationDetails->status = "SUCCESS"; | |
| $migrationDetails->recordCount = $result->sqlResult->num_rows; | |
| $this->updateMigrationTables($migrationDetails, "common"); | |
| } catch (\Exception $e) { | |
| $migrationDetails->endTime = time(); | |
| $migrationDetails->status = "FAILED"; | |
| $migrationDetails->recordCount = 0; | |
| $this->updateMigrationTables($migrationDetails, "common"); | |
| throw new ProfessionalException($e->getMessage(), $e->getCode()); | |
| } | |
| return true; | |
| } | |
| /** | |
| * Migrate table internal_marks | |
| */ | |
| public function migrate_internal_marks() | |
| { | |
| try { | |
| $staffId = $_SESSION['adminID']; | |
| $migrationDetails = new \stdClass(); | |
| $migrationDetails->startTime = time(); | |
| $migrationDetails->tables = ["internal_marks"]; | |
| $query = "UPDATE | |
| internal_marks im | |
| INNER JOIN batches b ON | |
| b.batchID = im.batchID | |
| INNER JOIN | |
| `groups` g ON | |
| g.id = b.groups_id | |
| INNER JOIN cm_curriculum cc ON | |
| cc.id = CAST(g.properties->>'$.curriculumId' AS CHAR) | |
| AND cc.properties->>'$.programId' = g.properties->>'$.programId' | |
| INNER JOIN cm_curriculum_syllabus_relation ccsr ON | |
| ccsr.cm_curriculum_id = cc.id | |
| INNER JOIN cm_syllabus cs ON | |
| cs.id = ccsr.cm_syllabus_id | |
| INNER JOIN cm_syllabus_academic_term_settings csats ON | |
| csats.academic_term_id = im.semID | |
| AND csats.cm_syllabus_id = cs.id | |
| INNER JOIN cm_academic_paper cap ON | |
| cap.cm_syllabus_academic_term_settings_id = csats.id | |
| INNER JOIN cm_academic_paper_subjects caps ON | |
| caps.subject_id = im.subjectID | |
| AND caps.cm_academic_paper_id = cap.id | |
| SET | |
| im.groups_id = b.groups_id, | |
| im.academic_term_id = im.semID, | |
| im.academic_paper_subjects_id = caps.id, | |
| im.program_id = g.properties->>'$.programId', | |
| im.attendance_status = IF (im.isAbsent = 0, 'PRESENT', 'ABSENT')"; | |
| $result = $this->executeQuery($query); | |
| $migrationDetails->endTime = time(); | |
| $migrationDetails->status = "SUCCESS"; | |
| $migrationDetails->recordCount = $result->sqlResult->num_rows; | |
| $this->updateMigrationTables($migrationDetails, "internal_marks"); | |
| } catch (\Exception $e) { | |
| $migrationDetails->endTime = time(); | |
| $migrationDetails->status = "FAILED"; | |
| $migrationDetails->recordCount = 0; | |
| $this->updateMigrationTables($migrationDetails, "internal_marks"); | |
| throw new ProfessionalException($e->getMessage(), $e->getCode()); | |
| } | |
| return true; | |
| } | |
| /** | |
| * Migrate table | |
| */ | |
| public function migrate_consolidated_mark($batchId=null) | |
| { | |
| try { | |
| $staffId = $_SESSION['adminID']; | |
| $migrationDetails = new \stdClass(); | |
| $migrationDetails->startTime = time(); | |
| $migrationDetails->tables = ["exammarks_external"]; | |
| if (empty($batchId)) { | |
| $query = "SELECT batchID AS batchId FROM batches b WHERE batchHide=0 AND batchName NOT LIKE 'failed'"; | |
| $batches = $this->executeQueryForList($query); | |
| foreach ($batches as $batch) { | |
| $requetForBatch = new ConsolidatedMarkReportRequest(); | |
| $requetForBatch->batchId = $batch->batchId; | |
| $requetForBatch->staffId = $staffId; | |
| $this->validateAndGenerateConsolidatedMarks($requetForBatch); | |
| } | |
| } | |
| else { | |
| $requetForBatch = new ConsolidatedMarkReportRequest(); | |
| $requetForBatch->batchId = $batchId; | |
| $requetForBatch->staffId = $staffId; | |
| $this->validateAndGenerateConsolidatedMarks($requetForBatch); | |
| } | |
| $migrationDetails->endTime = time(); | |
| $migrationDetails->status = "SUCCESS"; | |
| $migrationDetails->recordCount = $studentCount; | |
| $this->updateMigrationTables($migrationDetails, "am_student_total_mark"); | |
| } catch (\Exception $e) { | |
| $migrationDetails->endTime = time(); | |
| $migrationDetails->status = "FAILED"; | |
| $migrationDetails->recordCount = 0; | |
| $this->updateMigrationTables($migrationDetails, "am_student_total_mark"); | |
| throw new ProfessionalException($e->getMessage(), $e->getCode()); | |
| } | |
| return true; | |
| } | |
| // =============================================================================================== | |
| // --------------------------------------MIGRATE USING QUEUE-------------------------------------- | |
| // =============================================================================================== | |
| public function validateAndGenerateConsolidatedMarks(ConsolidatedMarkReportRequest $request){ | |
| $request = $this->realEscapeObject($request); | |
| if (!$request->batchId) { | |
| throw new ProfessionalException(ProfessionalException::INVALID_PARAMETER,"Invalid request given for getiing status!"); | |
| } | |
| global $COLLEGE_CODE; | |
| $queryCollege = "SELECT value FROM settings s WHERE name='CODE' AND module='OTHER'"; | |
| $CODE = $this->executeQueryForObject($queryCollege)->value; | |
| $COLLEGE_CODE = $CODE ? $CODE : $COLLEGE_CODE; | |
| $request->COLLEGE_CODE = $COLLEGE_CODE; | |
| // $request->COLLEGE_CODE = "VIMALA"; | |
| $requestConsolidated = new GenerateConsolidatedMarkRequest(); | |
| $requestConsolidated->COLLEGE_CODE = $COLLEGE_CODE; | |
| $requestConsolidated->batchId = $request->batchId; | |
| $requestConsolidated->staffId = $request->staffId; | |
| $currentGenerateStatus = reset(ConsolidatedMarkListService::getInstance()->getGenerateAndValidationStatus($requestConsolidated)); | |
| if ($currentGenerateStatus->generateStatus->status == StatusConstants::PROCESSING) { | |
| return; | |
| } | |
| // $requestConsolidated->batchId = $request->batchId = 145; | |
| $taskQueRequest = new \stdClass(); | |
| $taskQueRequest->request = $request; | |
| $studentsInBatch = StudentService::getInstance()->getStudentsByBatch($request->batchId); | |
| $studentChunks = array_chunk(array_unique(array_column($studentsInBatch,"studentID")), 5, true); | |
| $taskQueRequest->totalStudents = count($studentsInBatch); | |
| $taskQueRequest->totalChunks = count($studentChunks); | |
| try { | |
| if ($currentGenerateStatus->validationStatus->isValid) { | |
| $generateStatus = new ConsolidatedMarkGenerateStatus(); | |
| $generateStatus->status = StatusConstants::PROCESSING; | |
| $generateStatus->processedStudentsCount = 0; | |
| $generateStatus->processedChucks = 0; | |
| $generateStatus->processeFailedStudents = []; | |
| ConsolidatedMarkListService::getInstance()->updateGenerateStatus($requestConsolidated,$generateStatus); | |
| $startProcessing = false; | |
| foreach ($studentChunks as $students) { | |
| $taskQueRequest->studentIds = $students; | |
| $taskQueue = new AMSTaskQueue(); | |
| $params = ['className' => 'com\linways\core\ams\professional\service\examcontroller\migration\MigartionsService', | |
| 'methodName' => 'generateConsolidatedMarks', | |
| 'methodParams' => [$taskQueRequest]]; | |
| $taskQueue->enqueue('EXECUTE SERVICE', $params); | |
| // $this->generateConsolidatedMarks($taskQueRequest); | |
| $startProcessing = true; | |
| } | |
| } | |
| else { | |
| $generateStatus = new ConsolidatedMarkGenerateStatus(); | |
| $generateStatus->status = "NOT GENERATED"; | |
| ConsolidatedMarkListService::getInstance()->updateGenerateStatus($requestConsolidated,$generateStatus); | |
| } | |
| } | |
| catch(\Exception $e) { | |
| if (!$startProcessing) { | |
| $generateStatusFailed = new \stdClass(); | |
| $generateStatusFailed->status = StatusConstants::FAILED; | |
| ConsolidatedMarkListService::getInstance()->updateGenerateStatus($requestConsolidated,$generateStatusFailed); | |
| } | |
| } | |
| } | |
| public function generateConsolidatedMarks($taskQueRequest){ | |
| try { | |
| $request = $taskQueRequest->request; | |
| $studentIds = $taskQueRequest->studentIds; | |
| // $studentIds = [4748]; | |
| $generateStatus = new ConsolidatedMarkGenerateStatus(); | |
| $generateStatus->status = StatusConstants::PROCESSING; | |
| $currentSavedGenerateStatus = ConsolidatedMarkListService::getInstance()->getSavedGenerateStatus($request); | |
| $processedStudentCount = $currentSavedGenerateStatus->generateStatus->processedStudentsCount ? $currentSavedGenerateStatus->generateStatus->processedStudentsCount : 0; | |
| $processedChucks = $currentSavedGenerateStatus->generateStatus->processedChucks ? $currentSavedGenerateStatus->generateStatus->processedChucks : 0; | |
| $calcConsolidatedMarkRequest = new ConsolidatedMarkReportRequest(); | |
| $calcConsolidatedMarkRequest->batchId = $request->batchId; | |
| $calcConsolidatedMarkRequest->fetchMarkHistory = true; | |
| $calcConsolidatedMarkRequest->publishedSupplyExam = false; | |
| $calcConsolidatedMarkRequest->COLLEGE_CODE = $request->COLLEGE_CODE; | |
| if($request->COLLEGE_CODE == "CHRISTIJK"){ | |
| $calcConsolidatedMarkRequest->considerInternalAbsent = true; | |
| $calcConsolidatedMarkRequest->supplyMarkCalcType = "LATEST"; | |
| } | |
| if($request->COLLEGE_CODE == "SCEK"){ | |
| $calcConsolidatedMarkRequest->considerSupplyModeration = true; | |
| } | |
| if($request->COLLEGE_CODE == "VIMALA"){ | |
| $calcConsolidatedMarkRequest->supplyMarkCalcType = "LATEST"; | |
| } | |
| $calcConsolidatedMarkRequest->considerGraceMark = true; | |
| $calcConsolidatedMarkRequest->considerRevaluation = true; | |
| foreach ($studentIds as $slNo => $studentId) { | |
| $pulishedExamRegId = $this->getPublishedRegularExamRegistration($request->batchId); | |
| $failedStudentsExamRegId = $this->getPublishedRegularExamRegistrationOfFailedStudent($studentId); | |
| $pulishedExamRegId = array_merge($pulishedExamRegId,$failedStudentsExamRegId); | |
| if(empty($pulishedExamRegId)){ | |
| break; | |
| } | |
| try { | |
| echo PHP_EOL . "\e[0;31m ===========CONSOLIDATED MARKS=========\e[0m" . PHP_EOL ; | |
| echo ($slNo + 1) . "\r . Batch: $request->batchId ||| Student:$studentId"; | |
| echo PHP_EOL . "\e[0;31m ======================================\e[0m" . PHP_EOL ; | |
| $calcConsolidatedMarkRequest->studentId = $studentId; | |
| // $calcConsolidatedMarkRequest->examRegId = implode(',',array_column($pulishedExamRegId,'examregID')); | |
| $calcConsolidatedMarkRequest->examRegIdArray = array_column($pulishedExamRegId,'examregID'); | |
| $pulishedSupplyRegId = $this->getPublishedSupplyExamRegistrationByStudentId($studentId); | |
| // $calcConsolidatedMarkRequest->supplyRegId = implode(',',array_column($pulishedSupplyRegId,'exam_supplementary_id')); | |
| $calcConsolidatedMarkRequest->supplyRegIdArray = $pulishedSupplyRegId; | |
| $consolidatedMarkDetails = reset(ConsolidatedMarkReportService::getInstance()->getStudentCosolidatedMarkDetails($calcConsolidatedMarkRequest)); | |
| if (!empty($consolidatedMarkDetails)) { | |
| foreach ($consolidatedMarkDetails->semMarks as $semester) { | |
| foreach ($semester->subject as $subject) { | |
| $subject->consolidated->staffId = $request->staffId; | |
| $this->upsertStudentSubjectConsolidatedMarkDetails($subject->consolidated); | |
| } | |
| $semester->consolidated->staffId = $request->staffId; | |
| $this->upsertStudentSemesterConsolidatedMarkDetails($semester->consolidated); | |
| } | |
| $consolidatedMarkDetails->consolidated->staffId = $request->staffId; | |
| $this->upsertStudentCourseConsolidatedMarkDetails($consolidatedMarkDetails->consolidated); | |
| $generateStatus->status = StatusConstants::PARTIALLY_COMPLETED; | |
| $processedStudentCount++; | |
| } | |
| else{ | |
| $generateStatus->processeFailedStudents[] = $studentId; | |
| } | |
| } | |
| catch(\Exception $e) { | |
| $generateStatus->processeFailedStudents[] = $studentId; | |
| } | |
| } | |
| $processedChucks++; | |
| if ($taskQueRequest->totalStudents == $processedStudentCount) { | |
| $generateStatus->status = StatusConstants::SUCCESS; | |
| } | |
| else{ | |
| if ($taskQueRequest->totalChunks == $processedChucks) { | |
| $generateStatus->status = $processedStudentCount ? StatusConstants::PARTIALLY_COMPLETED : StatusConstants::FAILED; | |
| } | |
| else { | |
| $generateStatus->status = StatusConstants::PROCESSING; | |
| } | |
| } | |
| $generateStatus->processedStudentsCount = $processedStudentCount; | |
| $generateStatus->processedChucks = $processedChucks; | |
| ConsolidatedMarkListService::getInstance()->updateGenerateStatus($request,$generateStatus); | |
| } | |
| catch(\Exception $e) { | |
| if ($e->getCode() != "STATUS_UPDATE_ERROR") { | |
| $processedChucks++; | |
| } | |
| if ($taskQueRequest->totalStudents == $processedStudentCount) { | |
| $generateStatus->status = StatusConstants::SUCCESS; | |
| } | |
| else{ | |
| if ($taskQueRequest->totalChunks == $processedChucks) { | |
| $generateStatus->status = $processedStudentCount ? StatusConstants::PARTIALLY_COMPLETE : StatusConstants::FAILED; | |
| } | |
| else { | |
| $generateStatus->status = StatusConstants::PROCESSING; | |
| } | |
| } | |
| $generateStatus->processedStudentsCount = $processedStudentCount; | |
| $generateStatus->processedChucks = $processedChucks; | |
| ConsolidatedMarkListService::getInstance()->updateGenerateStatus($requestConsolidated,$generateStatus); | |
| } | |
| echo PHP_EOL ."processedStudentsCount : $generateStatus->processedStudentsCount || processedChucks : $generateStatus->processedChucks || status : $generateStatus->status" . PHP_EOL ; | |
| } | |
| public function upsertStudentCourseConsolidatedMarkDetails ( $consolidatedMarkDetails ) { | |
| $consolidatedMarkDetails = $this->realEscapeObject($consolidatedMarkDetails); | |
| $markDetailsJSON = json_encode($consolidatedMarkDetails->markDetails); | |
| // $id = SecurityUtils::getRandomString(); | |
| try { | |
| $sql = "INSERT INTO ec_course_mark_details (groups_id,student_id,mark_details,total_supply_attempt_count,total_marks,percentage,grade,cgpa,class,failed_status,is_dirty,no_of_arrears,created_by) | |
| VALUES ('$consolidatedMarkDetails->groupId',$consolidatedMarkDetails->studentId,'$markDetailsJSON',$consolidatedMarkDetails->supplyAttemptCount,$consolidatedMarkDetails->totalMark,$consolidatedMarkDetails->percentage,'$consolidatedMarkDetails->grade',$consolidatedMarkDetails->cgpa,'$consolidatedMarkDetails->class','$consolidatedMarkDetails->status',0,'$consolidatedMarkDetails->noOfArrears',$consolidatedMarkDetails->staffId) | |
| ON DUPLICATE KEY UPDATE | |
| updated_by = VALUES(created_by), | |
| groups_id = VALUES(groups_id), | |
| mark_details = VALUES(mark_details), | |
| total_supply_attempt_count = VALUES(total_supply_attempt_count), | |
| total_marks = VALUES(total_marks), | |
| percentage = VALUES(percentage), | |
| grade = VALUES(grade), | |
| cgpa = VALUES(cgpa), | |
| class = VALUES(class), | |
| failed_status = VALUES(failed_status)"; | |
| $this->executeQuery($sql); | |
| } | |
| catch(\Exception $e) { | |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); | |
| } | |
| } | |
| public function upsertStudentSemesterConsolidatedMarkDetails ( $consolidatedMarkDetails ) { | |
| $consolidatedMarkDetails = $this->realEscapeObject($consolidatedMarkDetails); | |
| $markDetailsJSON = json_encode($consolidatedMarkDetails->markDetails); | |
| $markHisoryJSON = json_encode($consolidatedMarkDetails->markHistory); | |
| $regularExamMarkDetailsJSON = json_encode($consolidatedMarkDetails->regularExamMarkDetails); | |
| $regularExamMarkDetailsJSON = $regularExamMarkDetailsJSON ?? "{}"; | |
| // $id = SecurityUtils::getRandomString(); | |
| try { | |
| $sql = "INSERT INTO ec_semester_mark_details (groups_id,academic_term_id,student_id,mark_details,total_supply_attempt_count,total_mark,percentage,grade,class,sgpa,failed_status,is_dirty,mark_history,created_by) | |
| VALUES ('$consolidatedMarkDetails->groupId','$consolidatedMarkDetails->academicTermId',$consolidatedMarkDetails->studentId,'$markDetailsJSON',$consolidatedMarkDetails->supplyAttemptCount,$consolidatedMarkDetails->totalMarks,$consolidatedMarkDetails->percentage,'$consolidatedMarkDetails->grade','$consolidatedMarkDetails->class',$consolidatedMarkDetails->sgpa,'$consolidatedMarkDetails->status',0,'$markHisoryJSON',$consolidatedMarkDetails->staffId) | |
| ON DUPLICATE KEY UPDATE | |
| updated_by = VALUES(created_by), | |
| mark_details = VALUES(mark_details), | |
| total_supply_attempt_count = VALUES(total_supply_attempt_count), | |
| total_mark = VALUES(total_mark), | |
| percentage = VALUES(percentage), | |
| grade = VALUES(grade), | |
| sgpa = VALUES(sgpa), | |
| class = VALUES(class), | |
| mark_history = VALUES(mark_history), | |
| failed_status = VALUES(failed_status)"; | |
| $this->executeQuery($sql); | |
| } | |
| catch(\Exception $e) { | |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); | |
| } | |
| } | |
| public function upsertStudentSubjectConsolidatedMarkDetails ( $consolidatedMarkDetails ) { | |
| // $consolidatedMarkDetails = $this->realEscapeObject($consolidatedMarkDetails); | |
| $markDetailsJSON = json_encode($consolidatedMarkDetails->markDetails); | |
| $markHistoryJSON = json_encode($consolidatedMarkDetails->markHistory); | |
| // $id = SecurityUtils::getRandomString(); | |
| try { | |
| $sql = "INSERT INTO ec_consolidated_subject_mark_details (groups_id,cm_academic_paper_subjects_id,student_id,mark_details,mark_history,no_of_chances_taken,total_mark,percentage,grade,class,failed_status,is_dirty,created_by) | |
| VALUES ('$consolidatedMarkDetails->groupId','$consolidatedMarkDetails->id',$consolidatedMarkDetails->studentId,'$markDetailsJSON','$markHistoryJSON',$consolidatedMarkDetails->noOfChancesTaken,".($consolidatedMarkDetails->totalMarks ? $consolidatedMarkDetails->totalMarks : 0).",".($consolidatedMarkDetails->percentage ? $consolidatedMarkDetails->percentage : 0).",'$consolidatedMarkDetails->grade','$consolidatedMarkDetails->class','$consolidatedMarkDetails->status',0,$consolidatedMarkDetails->staffId) | |
| ON DUPLICATE KEY UPDATE | |
| updated_by = VALUES(created_by), | |
| mark_details = VALUES(mark_details), | |
| mark_history = VALUES(mark_history), | |
| no_of_chances_taken = VALUES(no_of_chances_taken), | |
| total_mark = VALUES(total_mark), | |
| percentage = VALUES(percentage), | |
| grade = VALUES(grade), | |
| class = VALUES(class), | |
| failed_status = VALUES(failed_status)"; | |
| $this->executeQuery($sql); | |
| $examRegMarkDetailsValues = []; | |
| $storeExamTypes = ["REGULAR","SUPPLY","SUPPLIMENTARY","IMPROVEMENT","REVALUATION"]; | |
| foreach ($consolidatedMarkDetails->markHistory as $markHistoryObj) { | |
| if (!in_array($markHistoryObj->examMarkType,$storeExamTypes)) continue; | |
| $markHistoryObjJSON = json_encode($markHistoryObj); | |
| // $id = SecurityUtils::getRandomString(); | |
| $examRegMarkDetailsValues[] = "('$consolidatedMarkDetails->groupId','$markHistoryObj->examRegistrationId','$consolidatedMarkDetails->id',$consolidatedMarkDetails->studentId,'$markHistoryObjJSON',$consolidatedMarkDetails->noOfChancesTaken,".($markHistoryObj->totalMark ? $markHistoryObj->totalMark : 0).",".($markHistoryObj->percentage ? $markHistoryObj->percentage : 0).",'$markHistoryObj->grade','$markHistoryObj->class','$markHistoryObj->resultStatus',0,$consolidatedMarkDetails->staffId)"; | |
| } | |
| $valuesJSON = implode(',',$examRegMarkDetailsValues); | |
| $sql = "INSERT INTO ec_subject_mark_details (groups_id,ec_exam_registration_id,cm_academic_paper_subjects_id,student_id,mark_details,no_of_chances_taken,total_mark,percentage,grade,class,failed_status,is_dirty,created_by) | |
| VALUES $valuesJSON | |
| ON DUPLICATE KEY UPDATE | |
| updated_by = VALUES(created_by), | |
| mark_details = VALUES(mark_details), | |
| no_of_chances_taken = VALUES(no_of_chances_taken), | |
| total_mark = VALUES(total_mark), | |
| percentage = VALUES(percentage), | |
| grade = VALUES(grade), | |
| class = VALUES(class), | |
| failed_status = VALUES(failed_status)"; | |
| if (!empty($examRegMarkDetailsValues)) { | |
| $this->executeQuery($sql); | |
| } | |
| } | |
| catch(\Exception $e) { | |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); | |
| } | |
| } | |
| /* get published exam registration by studentId | |
| */ | |
| public function getPublishedSupplyExamRegistrationByStudentId($studentId){ | |
| $studentId = $this->realEscapeString($studentId); | |
| try { | |
| $sql = "SELECT | |
| DISTINCT essd.exam_supplementary_id AS supplyRegId, | |
| es.semID AS semId | |
| FROM | |
| exam_supplementary_student_details essd | |
| INNER JOIN exam_supplementary es ON | |
| es.id = essd.exam_supplementary_id | |
| INNER JOIN studentaccount sa ON | |
| ( | |
| sa.studentID = essd.studentID | |
| ) | |
| LEFT JOIN failed_students fs ON | |
| ( | |
| sa.studentID = fs.studentID | |
| AND FIND_IN_SET(es.semID, fs.hisSemestersInThisbatch) | |
| ) | |
| WHERE essd.studentID IN ($studentId)"; | |
| $exam = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $exam; | |
| } | |
| /* get published exam registration by studentId | |
| */ | |
| public function getPublishedRegularExamRegistrationOfFailedStudent($studentId){ | |
| $studentId = $this->realEscapeString($studentId); | |
| try { | |
| $sql = "SELECT DISTINCT erb.examregID FROM failed_students fs INNER JOIN exam_reg_studentchallan ers ON (fs.studentID = ers.studentID ) INNER JOIN exam_registration_batches erb ON (fs.previousBatch = erb.batchID AND ers.examregID = erb.examregID AND FIND_IN_SET(erb.semID, fs.hisSemestersInThisbatch)) WHERE ers.studentID in($studentId)"; | |
| $exam = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $exam; | |
| } | |
| /* get published exam registration by batchId | |
| */ | |
| public function getPublishedRegularExamRegistration($batchId){ | |
| $batchId = $this->realEscapeString($batchId); | |
| try { | |
| $sql = "SELECT DISTINCT examregID , semID from exam_registration_batches WHERE batchID =$batchId "; | |
| $exam = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $exam; | |
| } | |
| /** | |
| * update migration tables | |
| */ | |
| public function updateMigrationTableLogs($logData, $table, $staffId) | |
| { | |
| $logData = $this->realEscapeObject($logData); | |
| $table = $this->realEscapeString($table); | |
| $staffId = $this->realEscapeString($staffId); | |
| try { | |
| $logDataJSON = stripslashes(json_encode($logData)); | |
| $sql = "UPDATE | |
| new_system_migration | |
| SET | |
| `log` = JSON_ARRAY_APPEND(`log`, '$', CAST('$logDataJSON' AS JSON)), | |
| updated_by = $staffId | |
| WHERE | |
| table_name = '$table'"; | |
| $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getMessage(), $e->getCode()); | |
| } | |
| } | |
| } |