Code Coverage |
||||||||||
Classes and Traits |
Functions and Methods |
Lines |
||||||||
| Total | |
0.00% |
0 / 1 |
|
0.00% |
0 / 28 |
CRAP | |
0.00% |
0 / 1090 |
| V4MigrationService | |
0.00% |
0 / 1 |
|
0.00% |
0 / 28 |
27060.00 | |
0.00% |
0 / 1090 |
| __construct | |
0.00% |
0 / 1 |
2.00 | |
0.00% |
0 / 3 |
|||
| __clone | |
0.00% |
0 / 1 |
2.00 | |
0.00% |
0 / 2 |
|||
| getInstance | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 5 |
|||
| saveV4InsertScript | |
0.00% |
0 / 1 |
42.00 | |
0.00% |
0 / 29 |
|||
| getColumnsForTable | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 10 |
|||
| formatPropertyValue | |
0.00% |
0 / 1 |
90.00 | |
0.00% |
0 / 21 |
|||
| getAllDetailsFromRequestTable | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 10 |
|||
| deleteRowByRequestTable | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 9 |
|||
| updateDetailsByRequestTable | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 9 |
|||
| insertStudentProgramAccount | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 33 |
|||
| insertBatchGroupMembers | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 30 |
|||
| insertCurriculumDetails | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 71 |
|||
| insertStaffRelation | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 9 |
|||
| insertClusterRelation | |
0.00% |
0 / 1 |
5256.00 | |
0.00% |
0 / 474 |
|||
| insertStudentProgramRelation | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 163 |
|||
| insertV4Subjects | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 26 |
|||
| mergeSubBatches | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 11 |
|||
| getIds | |
0.00% |
0 / 1 |
2.00 | |
0.00% |
0 / 4 |
|||
| getPaperSubjectId | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 8 |
|||
| createRelations | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 21 |
|||
| createLog | |
0.00% |
0 / 1 |
2.00 | |
0.00% |
0 / 16 |
|||
| getGroupLog | |
0.00% |
0 / 1 |
2.00 | |
0.00% |
0 / 11 |
|||
| setLog | |
0.00% |
0 / 1 |
110.00 | |
0.00% |
0 / 15 |
|||
| getCreatedLog | |
0.00% |
0 / 1 |
72.00 | |
0.00% |
0 / 15 |
|||
| createSearchLogRequest | |
0.00% |
0 / 1 |
2.00 | |
0.00% |
0 / 10 |
|||
| findGroup | |
0.00% |
0 / 1 |
156.00 | |
0.00% |
0 / 9 |
|||
| createGroup | |
0.00% |
0 / 1 |
42.00 | |
0.00% |
0 / 35 |
|||
| createCluster | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 31 |
|||
| <?php | |
| namespace com\linways\core\ams\professional\service\v3Tov4Migration; | |
| use com\linways\core\ams\professional\constant\SemesterConstants; | |
| use com\linways\core\ams\professional\constant\SemRegistrationFormConstants; | |
| use com\linways\core\ams\professional\constant\ExamType; | |
| use com\linways\core\ams\professional\dto\AMSConstants; | |
| use com\linways\core\ams\professional\dto\Batch; | |
| use com\linways\core\ams\professional\dto\Semester; | |
| use com\linways\core\ams\professional\dto\SemRegistration; | |
| use com\linways\core\ams\professional\dto\SemRegistrationDetails; | |
| use com\linways\core\ams\professional\dto\Subject; | |
| use com\linways\core\ams\professional\exception\ProfessionalException; | |
| use com\linways\core\ams\professional\mapper\SemesterServiceMapper; | |
| use com\linways\core\ams\professional\request\GetSemRegistrationRequest; | |
| use com\linways\core\ams\professional\response\GetSemRegistrationResponse; | |
| use com\linways\core\ams\professional\service\v3Tov4Migration\BaseServiceSecondary; | |
| class V4MigrationService extends BaseServiceSecondary | |
| { | |
| // /Condition 1 - Presence of a static member variable | |
| private static $_instance = null; | |
| private $mapper = []; | |
| // /Condition 2 - Locked down the constructor | |
| private function __construct() | |
| { | |
| $this->mapper = SemesterServiceMapper::getInstance()->getMapper(); | |
| } | |
| // Prevent any oustide instantiation of this class | |
| // /Condition 3 - Prevent any object or instance of that class to be cloned | |
| private function __clone() | |
| { | |
| } | |
| // Prevent any copy of this object | |
| // /Condition 4 - Have a single globally accessible static method | |
| public static function getInstance() | |
| { | |
| if (!is_object(self::$_instance)) // or if( is_null(self::$_instance) ) or if( self::$_instance == null ) | |
| self::$_instance = new self (); | |
| return self::$_instance; | |
| } | |
| public function saveV4InsertScript($studentDetails, $tableName) | |
| { | |
| // Perform input validation or sanitation if needed | |
| $studentDetails = $this->realEscapeArray($studentDetails); | |
| if (empty($studentDetails)) { | |
| throw new ProfessionalException(ProfessionalException::EMPTY_PARAMETERS, "Empty Parameters! Please specify marks to save"); | |
| } | |
| $columns = $this->getColumnsForTable($tableName); | |
| $values = []; | |
| $fields = []; | |
| $updateFields = []; | |
| foreach ($studentDetails as $student) { | |
| $studentProperties = []; | |
| foreach ($student as $fieldKey => $studentField) { | |
| if ($fieldKey == 'biometricNo') { | |
| continue; | |
| } | |
| $fields[$fieldKey] = "`$fieldKey`"; | |
| $updateFields[$fieldKey] = "`$fieldKey` = VALUES(`$fieldKey`)"; | |
| $studentProperties[] = $this->formatPropertyValue($studentField, $columns[$fieldKey]); | |
| } | |
| $values[] = "(" . implode(', ', $studentProperties) . ")"; | |
| } | |
| try { | |
| $sql = "INSERT INTO `$tableName` (" . implode(', ', $fields) . ") VALUES " . implode(', ', $values) . " ON DUPLICATE KEY UPDATE " . implode(', ', $updateFields); | |
| $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return true; | |
| } | |
| // Fetch columns from the table | |
| private function getColumnsForTable($tableName) | |
| { | |
| $sqlColumns = "SHOW COLUMNS FROM `$tableName`"; | |
| $studentAccountColumns = $this->executeQueryForList($sqlColumns); | |
| $columns = []; | |
| foreach ($studentAccountColumns as $column) { | |
| $column = (object)$column; | |
| $columns[$column->Field] = $column->Type; | |
| } | |
| return $columns; | |
| } | |
| // Format property values based on column type | |
| private function formatPropertyValue($value, $columnType) | |
| { | |
| if (strpos(strtoupper($columnType), "INT") !== false || strpos(strtoupper($columnType), "TINYINT") !== false || strpos(strtoupper($columnType), "FLOAT") !== false) { | |
| if ($value == "0"){ | |
| return 0; | |
| } | |
| else if (empty($value)) { | |
| return "NULL"; | |
| } | |
| else{ | |
| return $value; | |
| } | |
| } elseif (strpos(strtoupper($columnType), "DATE") !== false || strpos(strtoupper($columnType), "JSON") !== false) { | |
| if (empty($value)) { | |
| return "NULL"; | |
| } | |
| else{ | |
| return '"' . $value . '"'; | |
| } | |
| } | |
| return '"' . $value . '"'; | |
| } | |
| public function getAllDetailsFromRequestTable($fields, $tableName, $condition) | |
| { | |
| $fields = $fields ? $fields : "*"; | |
| $sql = "select $fields from $tableName $condition"; | |
| try { | |
| $semDetails = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException ($e->getCode(), $e->getMessage()); | |
| } | |
| return $semDetails; | |
| } | |
| public function deleteRowByRequestTable($tableName, $condition) | |
| { | |
| $sql = "DELETE IGNORE FROM $tableName $condition"; | |
| try { | |
| $semDetails = $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException ($e->getCode(), $e->getMessage()); | |
| } | |
| return $semDetails; | |
| } | |
| public function updateDetailsByRequestTable($setCondition, $tableName, $condition) | |
| { | |
| $sql = "UPDATE $tableName $setCondition $condition"; | |
| try { | |
| $semDetails = $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException ($e->getCode(), $e->getMessage()); | |
| } | |
| return $semDetails; | |
| } | |
| public function insertStudentProgramAccount($currentTimestamp) | |
| { | |
| $sql = "INSERT IGNORE INTO `student_program_account` (`id`, `student_id`, `current_program_id`, `properties`, `created_by`, `updated_by`) | |
| SELECT `studentID`,`program_student_id`, programId, properties, 1, 1 FROM ( | |
| SELECT | |
| s.studentID, | |
| s.studentID AS program_student_id, | |
| JSON_UNQUOTE(g.properties->>'$.programId') AS programId, | |
| g.id AS batch_group_id, | |
| json_object( | |
| 'registerNumber',s.regNo, | |
| 'rollNumber',s.rollNo, | |
| 'admissionNumber',s.admissionNo, | |
| 'status', 'ACTIVE', | |
| 'tcIssued', s.tcIssued, | |
| 'admitSemester', s.admitSemester, | |
| 'yearOfPassing', s.yearOfPassing, | |
| 'academicStatus', 'ACTIVE', | |
| 'currentBatchId',g.id, | |
| 'termId',b.semID | |
| ) AS`properties` | |
| FROM `batches` b | |
| INNER JOIN studentaccount s ON s.batchID = b.batchID | |
| INNER JOIN `groups` g ON g.id = b.groups_id | |
| WHERE b.batchName <> 'failed' AND b.groups_id IS NOT NULL AND (s.studentCreateTime >= '$currentTimestamp' OR s.studentLastupdate >= '$currentTimestamp') | |
| ) AS t | |
| GROUP BY batch_group_id,studentID;"; | |
| try { | |
| $semDetails = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException ($e->getCode(), $e->getMessage()); | |
| } | |
| return $semDetails; | |
| } | |
| public function insertBatchGroupMembers($currentTimestamp) | |
| { | |
| $sql = "INSERT IGNORE INTO `group_members` (`id`, `groups_id`, `members`, `properties`) | |
| SELECT LEFT(REPLACE(UUID(),'-',''), 17) AS `id`, `groups_id`, `members`, `properties` FROM ( | |
| SELECT | |
| b.groups_id, | |
| s.studentID, | |
| JSON_OBJECT('studentId',s.studentID) AS `members`, | |
| JSON_OBJECT('isFailed',false,'academicStatus','ACTIVE','reason','','regNo',s.regNo,'rollNo', s.rollNo,'admissionNo', s.admissionNo,'admitSemester', s.admitSemester, 'yearOfPassing', s.yearOfPassing,'secondLanguageId', s.secondlangaugeID) `properties` | |
| FROM `batches` b | |
| INNER JOIN studentaccount s ON s.batchID = b.batchID | |
| WHERE b.batchName <> 'failed' AND b.groups_id IS NOT NULL AND (s.studentCreateTime >= '$currentTimestamp' OR s.studentLastupdate >= '$currentTimestamp') | |
| UNION | |
| SELECT | |
| b.groups_id, | |
| s.studentID, | |
| JSON_OBJECT('studentId',s.studentID) AS members, | |
| JSON_OBJECT('isFailed',if(fs.isFailNow,true,false),'academicStatus',if(fs.isFailNow,'FAILED','ACTIVE'),'reason',fs.reason,'regNo', s.regNo,'rollNo', s.rollNo,'admissionNo', s.admissionNo,'admitSemester', s.admitSemester, 'yearOfPassing', s.yearOfPassing,'secondlangaugeId', s.secondlangaugeID) properties | |
| FROM `batches` b | |
| INNER JOIN failed_students fs ON fs.previousBatch = b.batchID | |
| INNER JOIN studentaccount s ON s.studentID = fs.studentID | |
| WHERE b.groups_id IS NOT NULL AND (fs.failedDate >= '$currentTimestamp' OR fs.updated_date >= '$currentTimestamp') | |
| ) AS t | |
| GROUP BY groups_id,studentID"; | |
| try { | |
| $semDetails = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException ($e->getCode(), $e->getMessage()); | |
| } | |
| return $semDetails; | |
| } | |
| public function insertCurriculumDetails($currentTimestamp) | |
| { | |
| try { | |
| echo "\nupdating batch group"; | |
| $this->executeQuery("UPDATE `groups` g | |
| INNER JOIN batches b ON b.groups_id = g.id | |
| INNER JOIN `academic_term` t ON t.id = b.semID | |
| SET g.properties = JSON_SET( | |
| g.properties, | |
| '$.startYear', IF(b.batchStartYear, CAST(b.batchStartYear AS CHAR), NULL), | |
| '$.endYear', IF(b.batchEndYear, CAST(b.batchEndYear AS CHAR), NULL), | |
| '$.description', b.batchDesc, | |
| '$.optionName', b.batchOptionName, | |
| '$.studentShowingFields', JSON_ARRAY(JSON_OBJECT('name', b.studentSortBy, 'order', 1, 'displayName', b.studentShowField)), | |
| '$.universityId', b.universityId, | |
| '$.totalTerms', b.totalSemester, | |
| '$.termType', t.type, | |
| '$.currentTermId', t.id, | |
| '$.finalTermId', CAST(b.final_semester AS CHAR), | |
| '$.tcIssueDate', b.tcIssueDate, | |
| '$.yearOfPassing', IF(b.batchEndYear, CAST(b.batchEndYear AS CHAR), NULL), | |
| '$.campusTypeId', b.campus_typeID, | |
| '$.courseTypeId', b.courseTypeID, | |
| '$.studentSortBy', b.studentSortBy, | |
| '$.departmentId', CAST(b.deptID AS CHAR), | |
| '$.extraSyllabusIds', NULL, | |
| '$.isActive', b.batchHide, | |
| '$.batchDisplayOrder', b.batchDisplayOrder | |
| ) | |
| WHERE 1 = 1"); | |
| echo "\nCreating syllabus term relations"; | |
| $this->executeQuery("INSERT IGNORE INTO `cm_syllabus_academic_term_settings` (`id`, `cm_syllabus_id`, `academic_term_id`, `properties`) | |
| SELECT | |
| LEFT(REPLACE(UUID(),'-',''), 17) AS id, | |
| cs.cm_syllabus_id, | |
| at.id, | |
| JSON_OBJECT() | |
| FROM `groups` g | |
| INNER JOIN `batches` b ON b.groups_id = g.id | |
| INNER JOIN `cm_curriculum_syllabus_relation` cs ON cs.cm_curriculum_id = g.properties->>'$.curriculumId' | |
| INNER JOIN `sbs_relation` sbs ON sbs.batchID = b.batchID | |
| INNER JOIN `semesters` s ON s.semID = sbs.semID | |
| INNER JOIN `academic_term` at ON at.id = s.termId | |
| WHERE (sbs.createdDate >= '$currentTimestamp' OR sbs.updatedDate >= '$currentTimestamp') | |
| GROUP BY g.properties->'$.curriculumId',at.id"); | |
| // -- FOR CREATING ACADEMICS PAPER | |
| echo "\nCreating academic papers"; | |
| $this->executeQuery("INSERT IGNORE INTO `cm_academic_paper` (`id`, `name`, `cm_syllabus_academic_term_settings_id`, `properties`) | |
| SELECT | |
| LEFT(REPLACE(UUID(),'-',''), 17) AS `id`, | |
| ifnull(s.subjectDesc,s.subjectName), | |
| sat.id, | |
| json_object('subjectTypeId','','noOfSubjectThatAStudentCanChoose',1,'subjectId',sbs.subjectID) | |
| FROM `groups` g | |
| INNER JOIN `batches` b on b.groups_id = g.id | |
| INNER JOIN `sbs_relation` sbs on sbs.batchID = b.batchID | |
| INNER JOIN `cm_curriculum_syllabus_relation` cs on cs.cm_curriculum_id = g.properties->>'$.curriculumId' | |
| INNER JOIN `cm_syllabus_academic_term_settings` sat on sat.cm_syllabus_id = cs.cm_syllabus_id and sbs.semID = sat.academic_term_id | |
| INNER JOIN `subjects` s on s.subjectID = sbs.subjectID | |
| WHERE (sbs.createdDate >= '$currentTimestamp' OR sbs.updatedDate >= '$currentTimestamp') AND CONCAT(ifnull(s.subjectDesc,s.subjectName),sat.id) NOT IN ( SELECT CONCAT(name,cm_syllabus_academic_term_settings_id) FROM cm_academic_paper WHERE created_date >= '$currentTimestamp' OR updated_date >= '$currentTimestamp') | |
| group by g.properties->'$.curriculumId',sbs.semID,sbs.subjectID;"); | |
| // -- FOR CREATING ACADEMICS PAPER SUBJECTS | |
| echo "\nCreating academic papers subjects"; | |
| $this->executeQuery("INSERT IGNORE INTO `cm_academic_paper_subjects` (`id`, `cm_academic_paper_id`, `subject_id`, `properties`) | |
| SELECT | |
| LEFT(REPLACE(UUID(),'-',''), 17) AS id, | |
| id, | |
| properties->'$.subjectId', | |
| json_object('order',0,'credit','','isActive',true,'isExternal','','isInternal','','gradeSchemeId','', 'subjectTypeId','', 'studentCanChoose',1, 'excludeSubjectFromTotal','', 'noOfSubjectThatAStudentCanChoose',1) AS `properties` | |
| FROM `cm_academic_paper` WHERE (created_date >= '$currentTimestamp' OR updated_date >= '$currentTimestamp');"); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException ($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| public function insertStaffRelation($currentTimestamp) | |
| { | |
| try { | |
| echo "\nupdating batch group"; | |
| $this->executeQuery(" INSERT IGNORE INTO v4_ams_staff_department_relations (`staff_id`,`department_id`,`created_by`,`created_date`) | |
| SELECT s.staffID,s.deptID,0,UTC_TIMESTAMP FROM staffaccounts s INNER JOIN department d ON d.deptID = s.deptID WHERE s.staffCreateDate >= '$currentTimestamp' OR s.updated_date >= '$currentTimestamp';"); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException ($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| public function insertClusterRelation($currentTimestamp){ | |
| try { | |
| $this->executeQuery("SET SESSION GROUP_CONCAT_max_len = 1000000;"); | |
| $allSubjectsSql = "SELECT s.id,s.batchType, s.groups_id, s.sbsID, s.sbsIds, s.staffID, s.batchID, s.semID, s.subjectID, s.subjectID2, s.csID, s.isPseudosubject, s.subbatches, s.pseudosubjectID, s.subjectName, s.batchType, s.handling_department, s.subjectClusterId | |
| FROM | |
| ( | |
| ( | |
| SELECT | |
| LEFT(REPLACE(UUID(),'-',''), 17) AS id, | |
| sbs.sbsID, | |
| CONCAT('[',GROUP_CONCAT(DISTINCT CONCAT('\"',sbs.sbsID,'\"')),']') AS sbsIds, | |
| CONCAT('[',GROUP_CONCAT(DISTINCT CONCAT('\"',sbs.staffID,'\"')),']') AS staffID, | |
| CONCAT('[',GROUP_CONCAT(DISTINCT CONCAT('\"',sbs.batchID,'\"')),']') AS batchID, | |
| CONCAT('[',GROUP_CONCAT(DISTINCT CONCAT('\"',b.groups_id,'\"')),']') AS groups_id, | |
| CONCAT('[',GROUP_CONCAT(DISTINCT CONCAT('\"',sbs.semID,'\"')),']') AS semID, | |
| CONCAT('[',GROUP_CONCAT(DISTINCT CONCAT('\"',sbs.subjectID,'\"')),']') AS subjectID2, | |
| sbs.subjectID, | |
| sbs.csID, | |
| sbs.isPseudosubject, | |
| NULL AS subbatches, | |
| ps.pseudosubjectID, | |
| ps.subjectName, | |
| 'PseudoSubject' AS batchType, | |
| ps.hdl_deptID AS handling_department, | |
| ps.cluster_id AS subjectClusterId, | |
| NULL AS subjectGroupId | |
| from pseudosubjects ps | |
| INNER JOIN pseudosubjects_sbs psbs ON ps.pseudosubjectID = psbs.pseudosubjectID | |
| INNER JOIN sbs_relation sbs ON psbs.sbsID = sbs.sbsID | |
| INNER JOIN batches b ON b.batchID = sbs.batchID | |
| WHERE (psbs.created_date >= '$currentTimestamp' OR psbs.updated_date >= '$currentTimestamp') | |
| GROUP BY ps.pseudosubjectID | |
| ORDER BY ps.pseudosubjectID | |
| ) | |
| UNION ALL | |
| ( | |
| SELECT | |
| LEFT(REPLACE(UUID(),'-',''), 17) AS id, | |
| sbs.sbsID, | |
| CONCAT('[',GROUP_CONCAT(DISTINCT CONCAT('\"',sbs.sbsID,'\"')),']') AS sbsIds, | |
| CONCAT('[',GROUP_CONCAT(DISTINCT CONCAT('\"',sbs.staffID,'\"')),']') AS staffID, | |
| CONCAT('[',GROUP_CONCAT(DISTINCT CONCAT('\"',sbs.batchID,'\"')),']') AS batchID, | |
| CONCAT('[',GROUP_CONCAT(DISTINCT CONCAT('\"',b.groups_id,'\"')),']') AS groups_id, | |
| CONCAT('[',GROUP_CONCAT(DISTINCT CONCAT('\"',sbs.semID,'\"')),']') AS semID, | |
| CONCAT('[',GROUP_CONCAT(DISTINCT CONCAT('\"',sbs.subjectID,'\"')),']') AS subjectID2, | |
| sbs.subjectID, | |
| sbs.csID, | |
| sbs.isPseudosubject, | |
| CONCAT('[',GROUP_CONCAT(JSON_OBJECT( | |
| 'subbatchID',sub.subbatchID, | |
| 'subbatchName',concat(s.subjectName,' ',b.batchName,' ',sem.semName,' - ',sub.subbatchName), | |
| 'staffIds',sbs.staffID | |
| )),']') AS `subbatches`, | |
| NULL AS `pseudosubjectID`, | |
| NULL AS `subjectName`, | |
| 'Sub-batch' AS `batchType`, | |
| NULL AS `handling_department`, | |
| sub.cluster_id AS `subjectClusterId`, | |
| NULL AS `subjectGroupId` | |
| FROM `subbatches` sub | |
| INNER JOIN `subbatch_sbs` ssbs ON ssbs.subbatchID = sub.subbatchID | |
| INNER JOIN `batches` b ON b.batchID = sub.batchID | |
| INNER JOIN `sbs_relation` sbs ON sbs.sbsID = ssbs.sbsID AND sub.batchID = sbs.batchID AND sub.semID = sbs.semID | |
| INNER JOIN `semesters` sem ON sem.semID = sbs.semID | |
| INNER JOIN `subjects` s ON s.subjectID = sbs.subjectID | |
| WHERE sub.psID = 0 AND ssbs.sbsID NOT IN (SELECT DISTINCT `sbsID` FROM `pseudosubjects_sbs`) AND (ssbs.created_date >= '$currentTimestamp' OR ssbs.updated_date >= '$currentTimestamp') | |
| GROUP BY sbs.semID,sbs.batchID,sbs.subjectID | |
| ORDER BY sbs.semID,sbs.batchID,sbs.subjectID | |
| ) | |
| UNION ALL | |
| ( | |
| SELECT | |
| LEFT(REPLACE(UUID(),'-',''), 17) AS id, | |
| sbs.sbsID, | |
| CONCAT('[',GROUP_CONCAT(DISTINCT CONCAT('\"',sbs.sbsID,'\"')),']') AS sbsIds, | |
| CONCAT('[',GROUP_CONCAT(DISTINCT CONCAT('\"',sbs.staffID,'\"')),']') AS staffID, | |
| CONCAT('[',GROUP_CONCAT(DISTINCT CONCAT('\"',sbs.batchID,'\"')),']') AS batchID, | |
| CONCAT('[',GROUP_CONCAT(DISTINCT CONCAT('\"',b.groups_id,'\"')),']') AS groups_id, | |
| CONCAT('[',GROUP_CONCAT(DISTINCT CONCAT('\"',sbs.semID,'\"')),']') AS semID, | |
| CONCAT('[',GROUP_CONCAT(DISTINCT CONCAT('\"',sbs.subjectID,'\"')),']') AS subjectID2, | |
| sbs.subjectID, | |
| sbs.csID, | |
| sbs.isPseudosubject, | |
| NULL AS `subbatches`, | |
| NULL AS `pseudosubjectID`, | |
| concat(sub.subjectName,' ',b.batchName,' ',sem.semName) AS `subjectName`, | |
| 'Subject' AS `batchType`, | |
| NULL AS `handling_department`, | |
| NULL AS `subjectClusterId`, | |
| NULL AS `subjectGroupId` | |
| FROM `sbs_relation` sbs | |
| INNER JOIN `batches` b ON b.batchID = sbs.batchID | |
| INNER JOIN `subjects` sub ON sub.subjectID = sbs.subjectID | |
| INNER JOIN `semesters` sem ON sbs.semID = sem.semID | |
| WHERE sbs.sbsID NOT IN (SELECT DISTINCT `sbsID` FROM `subbatch_sbs`) AND sbs.sbsID NOT IN (SELECT DISTINCT `sbsID` FROM `pseudosubjects_sbs`) AND (sbs.createdDate >= '$currentTimestamp' OR sbs.updatedDate >= '$currentTimestamp') | |
| GROUP BY sbs.semID,sbs.batchID,sbs.subjectID | |
| ORDER BY sbs.semID,sbs.batchID,sbs.subjectID | |
| ) | |
| ) AS s | |
| GROUP BY s.id;"; | |
| $allSubjects = $this->executeQueryForList($allSubjectsSql); | |
| foreach ($allSubjects AS $i => $inSubject) { | |
| $row = (object)$inSubject; | |
| echo "\n\n==================== \e[0;31m(".($i+1)." / ".count($allSubjects).")\e[0m ====================\n"; | |
| $subjectID = (int)$row->subjectID; | |
| $where = []; | |
| switch ($row->batchType) { | |
| case 'PseudoSubject': | |
| $row->pseudosubjectID ? $where[] = " log->'$.pseudosubjectId' = '" . $row->pseudosubjectID . "' " : null; | |
| $row->batchType ? $where[] = " log->'$.type' = '" . $row->batchType . "' " : null; | |
| $subjectID ? $where[] = " log->'$.subjectId' = " . $subjectID . " " : null; | |
| break; | |
| case 'Sub-batch': | |
| case 'Subject': | |
| json_decode($row->batchID)[0] ? $where[] = " log->'$.batchId' = " . json_decode($row->batchID)[0] . " " : null; | |
| json_decode($row->semID)[0] ? $where[] = " log->'$.semId' = " . json_decode($row->semID)[0] . " " : null; | |
| $row->batchType ? $where[] = " log->'$.type' = '" . $row->batchType . "' " : null; | |
| $subjectID ? $where[] = " log->'$.subjectId' = " . $subjectID . " " : null; | |
| break; | |
| } | |
| $fetedLog = $this->executeQueryForList("SELECT id ,log FROM new_academics_migrations WHERE " . (implode(' AND ', $where)) . ";"); | |
| $createdLog = []; | |
| foreach ($fetedLog AS $key => $allLog) { | |
| $allLog = (object) $allLog; | |
| $r = new \stdClass(); | |
| $r->log = json_decode($allLog->log); | |
| $r->id = $allLog->id; | |
| $r->log->complete = false; | |
| $createdLog [] = $r; | |
| } | |
| if (!count($createdLog)) { | |
| //echo "\n* it doesn't have log \n"; | |
| // get subject from curriculum by subject, batches AND sem | |
| $curriculumArray = $this->executeQueryForList("SELECT | |
| csr.cm_curriculum_id AS `curriculumId`, | |
| concat('[',GROUP_CONCAT(DISTINCT JSON_OBJECT('id',g.id,'batchId',b.batchID)),']') AS groupId, | |
| csr.cm_syllabus_id AS syllabusId, | |
| GROUP_CONCAT(DISTINCT ts.id) AS `termSettingsId`, | |
| at.id AS termId, | |
| GROUP_CONCAT(DISTINCT ps.id) AS `paperSubjectId`, | |
| GROUP_CONCAT(DISTINCT ps.subject_id) AS `subject_id`, | |
| LEFT(REPLACE(UUID(),'-',''), 17) AS `tempPaperId`, | |
| LEFT(REPLACE(UUID(),'-',''), 17) AS `tempPaperSubjectId` | |
| FROM `groups` g | |
| INNER JOIN `batches` b ON g.id = b.groups_id | |
| INNER JOIN `cm_curriculum_syllabus_relation` csr ON csr.cm_curriculum_id = g.properties->'$.curriculumId' | |
| INNER JOIN `cm_syllabus_academic_term_settings` ts ON ts.cm_syllabus_id = csr.cm_syllabus_id | |
| INNER JOIN `academic_term` at ON at.id = ts.academic_term_id | |
| INNER JOIN `cm_academic_paper` p ON p.cm_syllabus_academic_term_settings_id = ts.id | |
| LEFT JOIN `cm_academic_paper_subjects` ps ON ps.cm_academic_paper_id = p.id AND ps.subject_id = " . $subjectID . " | |
| WHERE b.batchID IN (" . (implode(',', array_unique(array_map(function ($obj) { | |
| return (int)$obj; | |
| }, json_decode($row->batchID))))) . ") AND at.id IN (" . (implode(',', array_unique(array_map(function ($obj) { | |
| return (int)$obj; | |
| }, json_decode($row->semID))))) . ") | |
| GROUP BY g.properties->'$.curriculumId';"); | |
| $subjectArray = []; | |
| foreach ($curriculumArray AS $key => $curriculumObj) { | |
| $curriculum = (object) $curriculumObj; | |
| foreach (json_decode($curriculum->groupId) AS $key => $value) { | |
| $groupCurriculum[$value->id] = $curriculum->curriculumId; | |
| $batchCurriculum[$value->batchId] = $curriculum->curriculumId; | |
| } | |
| $cluster = new \stdClass(); | |
| if (!$curriculum->paperSubjectId) { | |
| $this->executeQuery("INSERT IGNORE INTO cm_academic_paper (id,name,cm_syllabus_academic_term_settings_id,properties) VALUES ('$curriculum->tempPaperId','$row->subjectName', '$curriculum->termSettingsId',JSON_OBJECT('subjectTypeId','','noOfSubjectThatAStudentCanChoose',1,'subjectId'," . $subjectID . "))"); | |
| $this->executeQuery("INSERT IGNORE INTO cm_academic_paper_subjects (id,cm_academic_paper_id,subject_id,properties) VALUES ('" . $curriculum->tempPaperSubjectId . "', '" . $curriculum->tempPaperId . "', " . $subjectID . ",JSON_OBJECT('credit','','isActive',true,'isExternal','','isInternal','','gradeSchemeId','', 'subjectTypeId','', 'studentCanChoose',1, 'excludeSubjectFromTotal','', 'noOfSubjectThatAStudentCanChoose',1) )"); | |
| $subject = new \stdClass(); | |
| $subject->curriculumId = $curriculum->curriculumId; | |
| $subject->syllabusId = $curriculum->syllabusId; | |
| $subject->termSettingsId = $curriculum->termSettingsId; | |
| $subject->paperSubjectId = $curriculum->tempPaperSubjectId; | |
| $subject->subject_id = $subjectID; | |
| $subjectArray[] = $subject; | |
| echo "\n* created paper subject for subject id : $subjectID \n"; | |
| } else { | |
| $subject = new \stdClass(); | |
| $subject->curriculumId = $curriculum->curriculumId; | |
| $subject->syllabusId = $curriculum->syllabusId; | |
| $subject->termSettingsId = $curriculum->termSettingsId; | |
| $subject->paperSubjectId = $curriculum->paperSubjectId; | |
| $subject->subject_id = $subjectID; | |
| $subjectArray[] = $subject; | |
| } | |
| } | |
| } | |
| $updateQuery = []; | |
| switch ($row->batchType) { | |
| case 'PseudoSubject': | |
| echo "\n* it is $row->batchType : $row->pseudosubjectID"; | |
| $logId = null; | |
| if (count($createdLog)) { | |
| $log = $createdLog[0]->log; | |
| $logId = $createdLog[0]->id; | |
| } else { | |
| $log = $this->createLog(); | |
| $log->type = $row->batchType; | |
| $log->pseudosubjectId = $row->pseudosubjectID; | |
| $log->clusterId = $row->id; | |
| $log->subjectId = $subjectID; | |
| } | |
| if($log->complete){ | |
| echo "\n* Already Migrated"; | |
| }else{ | |
| $cluster = new \stdClass(); | |
| $cluster->staffIds = json_decode($row->staffID); | |
| $cluster->paperSubjectIds = []; | |
| //get all students in pseudo subjects | |
| //both failed AND pass students | |
| $oldGroups = $this->executeQueryForList( | |
| "SELECT | |
| p.pseudosubjectID, | |
| sub.batchID, | |
| JSON_ARRAYAGG(JSON_OBJECT('id',sstd.studentID,'isFailed',false)) AS `students`, | |
| sub.semID, | |
| b.groups_id AS `groupId` | |
| FROM `pseudosubjects` p | |
| INNER JOIN `subbatches` sub ON sub.psID = p.pseudosubjectID | |
| INNER JOIN `subbatch_student` sstd ON sstd.subbatchID = sub.subbatchID | |
| INNER JOIN `pseudosubjects_students` pstd ON pstd.studentID = sstd.studentID | |
| INNER JOIN `batches` b ON b.batchID = sub.batchID | |
| WHERE p.pseudosubjectID = $row->pseudosubjectID | |
| GROUP BY sub.batchID,sub.semID;" | |
| ); | |
| $groupIds = []; | |
| foreach ($oldGroups AS $key => $oldGroup) { | |
| $group = (object) $oldGroup; | |
| $name = $row->subjectName . " " . ($key ? ' ' . $key : ''); | |
| $gotLogGroup = false; | |
| $groupLog = $this->findGroup($log->groups, $group->batchID, $group->semID, null); | |
| echo "\n* batchId : $group->batchID , semId : $group->semID "; | |
| if (empty($groupLog)) { | |
| $id = $this->getIds($this); | |
| $paperSubjectId = $this->getPaperSubjectId($batchCurriculum[(int)$group->batchID], $subjectArray); | |
| $groupLog = $this->getGroupLog(); | |
| $groupLog->id = $id; | |
| $groupLog->batchId = $group->batchID; | |
| $groupLog->semId = $group->semID; | |
| $groupLog->paperSubjectId = $paperSubjectId; | |
| } else { | |
| echo "\n* got log group details"; | |
| $gotLogGroup = true; | |
| $paperSubjectId = $groupLog->paperSubjectId; | |
| $cluster->paperSubjectIds[] = $paperSubjectId; | |
| } | |
| if(empty($paperSubjectId)){ | |
| unset($oldGroups[$key]); | |
| continue; | |
| } | |
| if (!$groupLog->created) { | |
| $groupLog->created = $this->createGroup($groupLog->id,$name,'SUBJECT',$paperSubjectId,$group->groupId,$subjectID); | |
| $groupIds[] = $groupLog->id; | |
| } | |
| $updateQuery[] = "UPDATE `subbatches` SET `cluster_id` = '$row->id' , `group_id` = '" . $groupLog->id . "' WHERE batchID = " . $group->batchID . " AND `semID` = " . $group->semID . " AND `psID` = " . $row->pseudosubjectID . " ;"; | |
| $groupLog->setMembers = false; | |
| if ($groupLog->created && !$groupLog->setMembers) { | |
| try { | |
| $this->executeQuery("INSERT IGNORE INTO `group_members` (`id`, `groups_id`, `members`, `properties`) SELECT LEFT(REPLACE(UUID(),'-',''), 17) AS `id`, '$groupLog->id' AS `groups_id`, JSON_OBJECT('studentId',`studentID`) AS `members`, JSON_OBJECT('isFailed',false,'academicStatus','ACTIVE','temp',true) AS `properties` FROM `studentaccount` WHERE `studentID` IN (" . (implode(',', array_unique(array_map(function ($std) {return (int)$std->id;}, json_decode($group->students))))) . ");"); | |
| $groupLog->setMembers = true; | |
| //echo "\n* create group members "; | |
| } catch (\Throwable $th) { | |
| echo "\n* couldn't create group members"; | |
| } | |
| } | |
| if (!$gotLogGroup) { | |
| $log->groups[] = $groupLog; | |
| } | |
| } | |
| $cluster->paperSubjectIds = array_unique($cluster->paperSubjectIds); | |
| $cluster->temp = true; | |
| //create clusters | |
| $groupSet = array_unique(array_merge(array_map(function ($obj) {return $obj->created; }, $log->groups), array_map(function ($obj) {return $obj->setMembers;}, $log->groups))); | |
| if (count($groupSet) == 1 && $groupSet[0] && !$log->created) { | |
| $log->created = $this->createCluster($row->id,$row->subjectName,'PSEUDO-SUBJECT',$groupIds,$cluster); | |
| } | |
| !$log->setClusterRelation && $log->created ? $log->setClusterRelation = $this->createRelations($row->id, $this) : null; | |
| if ($log->created && !$log->localUpdates) { | |
| try { | |
| foreach ($updateQuery AS $key => $query) { | |
| $this->executeQuery($query); | |
| } | |
| $this->executeQuery("UPDATE pseudosubjects set cluster_id = '$row->id' where pseudosubjectID = '$row->pseudosubjectID';"); | |
| $log->localUpdates = true; | |
| //echo "\n* create relational data"; | |
| } catch (\Throwable $th) { | |
| echo "\n\e[0;31m* couldn't update relational data\e[0m"; | |
| $log->localUpdates = false; | |
| } | |
| } | |
| $this->setLog($log, $this, $logId); | |
| } | |
| unset($log); | |
| break; | |
| case 'Sub-batch': | |
| # code... | |
| if (!$row->subjectClusterId) { | |
| $subBatches = $this->mergeSubBatches($row->subbatches); | |
| $batchGroupId = json_decode($row->groups_id)[0]; | |
| $batchId = (int)json_decode($row->batchID)[0]; | |
| $semID = (int)json_decode($row->semID)[0]; | |
| $logId = null; | |
| echo "\n* it is $row->batchType : batch : $batchId, sem : $semID, subject : $subjectID"; | |
| foreach ($subBatches AS $key => $subBatch) { | |
| echo "\n* sub-batch : $subBatch->subbatchID, name : $subBatch->name "; | |
| $gotGroupLog = false; | |
| foreach ($createdLog AS $key => $part) { | |
| if($gotGroupLog) continue; | |
| if ($part->log->subbatchId == $subBatch->subbatchID) { | |
| $gotGroupLog = true; | |
| $log = $createdLog[$key]->log; | |
| $logId = $createdLog[$key]->id; | |
| } | |
| } | |
| if(!$log->complete){ | |
| if (empty($log)) { | |
| $paperSubjectId = $this->getPaperSubjectId($batchCurriculum[$batchId], $subjectArray); | |
| $log = $this->createLog(); | |
| $log->type = $row->batchType; | |
| $log->clusterId = $this->getIds($this); | |
| $log->batchId = $batchId; | |
| $log->semId = $semID; | |
| $log->subbatchId = $subBatch->subbatchID; | |
| $log->subjectId = $subjectID; | |
| $groupLog = $this->getGroupLog(); | |
| $groupLog->id = $this->getIds($this); | |
| $groupLog->batchId = $batchId; | |
| $groupLog->semId = $semID; | |
| $groupLog->subbatchId = $subBatch->subbatchID; | |
| $groupLog->paperSubjectId = $paperSubjectId; | |
| } else { | |
| $gotGroupLog = true; | |
| $groupLog = $log->groups[0]; | |
| $paperSubjectId = $groupLog->paperSubjectId; | |
| } | |
| $cluster = new \stdClass(); | |
| $cluster->staffIds = json_decode($row->staffID); | |
| $cluster->paperSubjectIds[] = $paperSubjectId; | |
| $cluster->temp = true; | |
| if (!$groupLog->created) { | |
| $groupLog->created = $this->createGroup($groupLog->id,$subBatch->name,'SUBJECT',$paperSubjectId,$batchGroupId,$subjectID); | |
| } | |
| $groupLog->setMembers = false; | |
| if ($groupLog->created && !$groupLog->setMembers) { | |
| try { | |
| $this->executeQuery("INSERT IGNORE INTO `group_members` (`id`, `groups_id`, `members`, `properties`) | |
| SELECT LEFT(REPLACE(UUID(),'-',''), 17) AS id, '$groupLog->id' AS `groups_id`, JSON_OBJECT('studentId',`studentID`) AS `members`, JSON_OBJECT('isFailed',false,'academicStatus','ACTIVE','temp',true) AS `properties` | |
| FROM `studentaccount` WHERE `studentID` IN (select `studentID` from `subbatch_student` where `subbatchID` = $subBatch->subbatchID)"); | |
| $groupLog->setMembers = true; | |
| //echo "\n* created group members"; | |
| } catch (\Throwable $th) { | |
| echo "\n\e[0;31m* couldn't created group members"; | |
| } | |
| } | |
| !$gotGroupLog ? $log->groups[] = $groupLog : null; | |
| if ($groupLog->created && $groupLog->setMembers && !$log->created) { | |
| $log->created = $this->createCluster($log->clusterId,$subBatch->name,'SUB-BATCH',array($groupLog->id),$cluster); | |
| } | |
| if(!$log->setClusterRelation && $log->created){ | |
| $log->setClusterRelation = $this->createRelations($log->clusterId, $this); | |
| } | |
| if (!$log->localUpdates && $log->created) { | |
| try { | |
| $sql = "UPDATE IGNORE `subbatches` SET `cluster_id` = '" . $log->clusterId . "' , `group_id` = '" . $groupLog->id . "' WHERE `batchID` = " . $batchId . " AND `semID` = " . $semID . " AND `subbatchID` = " . $subBatch->subbatchID . " ;"; | |
| $this->executeQuery($sql); | |
| $log->localUpdates = true; | |
| //echo "\n* created all relations"; | |
| } catch (\Throwable $th) { | |
| echo "\n\e[0;31m* couldn't update all relations\e[0m"; | |
| } | |
| } | |
| $this->setLog($log, $this, $logId); | |
| }else{ | |
| echo "\n* Already Migrated"; | |
| } | |
| unset($log);unset($groupLog); | |
| } | |
| } | |
| break; | |
| case 'Subject': | |
| $key = 0; | |
| $batchId = (int)json_decode($row->batchID)[0]; | |
| $groupId = json_decode($row->groups_id)[0]; | |
| $semId = (int)json_decode($row->semID)[0]; | |
| $gotGroupLog = false; | |
| $logId = null; | |
| echo "\n* it is $row->batchType : batch : $batchId, sem : $semId, subject : $subjectID "; | |
| if (empty($createdLog)) { | |
| $paperSubjectId = $subjectArray[0]->paperSubjectId; | |
| $log = $this->createLog(); | |
| $log->type = $row->batchType; | |
| $log->clusterId = $row->id; | |
| $log->batchId = $batchId; | |
| $log->semId = $semId; | |
| $log->subjectId = $subjectID; | |
| $groupLog = $this->getGroupLog(); | |
| $groupLog->id = $row->id; | |
| $groupLog->batchId = $batchId; | |
| $groupLog->semId = $semId; | |
| $groupLog->paperSubjectId = $paperSubjectId; | |
| } else { | |
| $gotGroupLog = true; | |
| $logId = $createdLog[0]->id; | |
| $log = $createdLog[0]->log; | |
| $groupLog = $log->groups[0]; | |
| $paperSubjectId = $groupLog->paperSubjectId; | |
| } | |
| if($log->complete){ | |
| echo "\n* Already Migrated"; | |
| }else{ | |
| $cluster = new \stdClass(); | |
| $cluster->staffIds = json_decode($row->staffID); | |
| $cluster->paperSubjectIds[] = $paperSubjectId; | |
| $cluster->temp = true; | |
| if (!$groupLog->created) { | |
| $groupLog->created = $this->createGroup($groupLog->id,$row->subjectName,'SUBJECT',$paperSubjectId,$groupId,$subjectID); | |
| } | |
| $groupLog->setMembers = false; | |
| if ($groupLog->created && !$groupLog->setMembers) { | |
| try { | |
| $this->executeQuery("INSERT IGNORE INTO `group_members` (`id`, `groups_id`, `members`, `properties`) | |
| SELECT LEFT(REPLACE(UUID(),'-',''), 17) AS id, '" . $groupLog->id . "' AS `groups_id`, JSON_OBJECT('studentId',t.`studentID`) AS members, JSON_OBJECT('isFailed',false,'academicStatus','ACTIVE','temp',true) AS `properties` FROM ( | |
| SELECT std.studentID,std.batchID,sbs.semID FROM sbs_relation sbs | |
| INNER JOIN `studentaccount` std ON std.batchID = sbs.batchID | |
| WHERE sbs.batchID = " . $batchId . " AND sbs.semID = " . $semId . " AND sbs.subjectID = $subjectID | |
| UNION | |
| SELECT std.studentID,fs.previousBatch AS batchID,1 AS semID FROM `failed_students` fs | |
| INNER JOIN `studentaccount` std ON std.studentID = fs.studentID | |
| WHERE fs.previousBatch = " . $batchId . " AND ( fs.failedInSemester = " . $semId . " OR find_in_set(" . $semId . ",fs.hisSemestersInThisbatch) ) | |
| ) AS t | |
| GROUP BY t.studentID | |
| ORDER BY t.studentID"); | |
| $groupLog->setMembers = true; | |
| //echo "\n* created group members "; | |
| } catch (\Throwable $th) { | |
| //echo "\n\e[0;31m* couldn't create group members \e[0m"; | |
| } | |
| } | |
| !$gotGroupLog ? $log->groups[] = $groupLog : null; | |
| if ($groupLog->created && $groupLog->setMembers && !$log->created) { | |
| $log->created = $this->createCluster($log->clusterId,$row->subjectName,'SUBJECT',array($groupLog->id),$cluster); | |
| } | |
| if ($log->created) { | |
| $log->setClusterRelation = $this->createRelations($log->clusterId, $this); | |
| $log->localUpdates = true; | |
| } | |
| $this->setLog($log, $this, $logId); | |
| } | |
| unset($log); | |
| break; | |
| default: | |
| # code... | |
| break; | |
| } | |
| } | |
| // SETTING UP FILED STUDENTS | |
| echo "\n\n Setting up failed students in group members"; | |
| $sql = | |
| "UPDATE IGNORE `group_members` gm | |
| INNER JOIN `groups` sg ON sg.id = gm.groups_id | |
| INNER JOIN `groups_relations` gg ON gg.child_groups_id = sg.id | |
| INNER JOIN `groups` bg ON bg.id = gg.parent_groups_id | |
| INNER JOIN `cm_academic_paper_subjects` aps ON aps.id = sg.paperSubjectId | |
| INNER JOIN `cm_academic_paper` ap ON ap.id = aps.cm_academic_paper_id | |
| INNER JOIN `cm_syllabus_academic_term_settings` str ON str.id = ap.cm_syllabus_academic_term_settings_id | |
| INNER JOIN `semesters` sem ON sem.termId = str.academic_term_id | |
| INNER JOIN `batches` b ON b.groups_id = bg.id | |
| INNER JOIN `failed_students` fs ON fs.studentID = gm.student_id AND fs.previousBatch = b.batchID AND sem.semID = fs.failedInSemester | |
| SET gm.properties = JSON_SET(gm.properties,'$.isFailed',true,'$.academicStatus','FAILED') | |
| WHERE sg.type = 'SUBJECT' AND fs.isFailNow = 1 AND (fs.failedDate >= '$currentTimestamp' OR fs.updated_date >= '$currentTimestamp')"; | |
| $this->executeQuery($sql); | |
| // $sql = | |
| // "UPDATE `group_members` gm | |
| // INNER JOIN ( | |
| // SELECT g.id,g.name,b.batchID,b.batchName,gm.id AS memberId,gm.student_id,gm.properties->>'$.academicStatus' AS academicStatus,st.studentID,st.studentName,st.batchID AS studentBatchId | |
| // FROM `groups` g | |
| // INNER JOIN `batches` b ON b.groups_id = g.id | |
| // INNER JOIN `group_members` gm ON gm.groups_id = g.id | |
| // LEFT JOIN `studentaccount` st ON st.studentID = gm.student_id) t ON t.memberId = gm.id | |
| // SET gm.properties = JSON_SET(gm.properties,'$.isFailed',true,'$.academicStatus','FAILED') | |
| // WHERE t.studentBatchId <> t.batchID;"; | |
| // $this->executeQuery($sql); | |
| $sql = | |
| "UPDATE IGNORE `group_members` sgm | |
| INNER JOIN `groups` sg ON sg.id = sgm.groups_id | |
| INNER JOIN `groups_relations` gg ON gg.child_groups_id = sg.id | |
| INNER JOIN `groups` bg ON bg.id = gg.parent_groups_id | |
| INNER JOIN `group_members` bgm ON bgm.groups_id = bg.id AND sgm.student_id = bgm.student_id | |
| INNER JOIN `cm_academic_paper_subjects` aps ON aps.id = sg.paperSubjectId | |
| INNER JOIN `cm_academic_paper` ap ON ap.id = aps.cm_academic_paper_id | |
| INNER JOIN `cm_syllabus_academic_term_settings` str ON str.id = ap.cm_syllabus_academic_term_settings_id AND str.academic_term_id = bg.properties->'$.currentTermId' | |
| SET sgm.properties = JSON_SET(sgm.properties,'$.isFailed',true,'$.academicStatus','FAILED') | |
| where sg.type = 'SUBJECT' AND bgm.properties->>'$.academicStatus' = 'FAILED';"; | |
| $this->executeQuery($sql); | |
| $this->executeQuery("INSERT IGNORE INTO `cluster_members` (`id`, `cluster_id`, `member`) | |
| SELECT LEFT(REPLACE(UUID(),'-',''), 17) AS `id`,c.id AS `cluster_id`,JSON_OBJECT('staffId',s.staffID) AS `member` FROM `cluster` c | |
| INNER JOIN `staffaccounts` s ON JSON_CONTAINS(c.properties->'$.staffIds',concat('\"',s.staffID,'\"'),'$');"); | |
| $this->executeQuery("update cm_academic_paper_subjects caps | |
| inner join subjects s on s.subjectID = caps.subject_id | |
| inner join v4_ams_subject vas ON vas.code = s.subjectName and vas.name = s.subjectDesc | |
| set caps.ams_subject_id = vas.id WHERE (caps.created_date >= '$currentTimestamp' OR caps.updated_date >= '$currentTimestamp');"); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException ($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| public function insertStudentProgramRelation($currentTimestamp){ | |
| try { | |
| echo "\n Adding students to student program accounts \n"; | |
| $this->executeQuery("INSERT IGNORE INTO `student_program_account` (`id`, `student_id`, `current_program_id`, `properties`, `created_by`, `updated_by`) | |
| SELECT `studentID`,`program_student_id`, programId, properties, 1, 1 FROM ( | |
| SELECT st.studentID,st.studentID AS program_student_id, JSON_UNQUOTE(g.properties->>'$.programId') AS programId,g.id AS batch_group_id,at.name,at.id AS termId,json_object( | |
| 'registerNumber',st.regNo, | |
| 'rollNumber',st.rollNo, | |
| 'admissionNumber',st.admissionNo, | |
| 'status',gm.academic_status, | |
| 'tcIssued', st.tcIssued, | |
| 'admitSemester', st.admitSemester, | |
| 'yearOfPassing', st.yearOfPassing, | |
| 'academicStatus', gm.academic_status, | |
| 'currentBatchId',g.id, | |
| 'termId',at.id | |
| ) AS properties,at.properties->>'$.orderNo' AS `orderNo` | |
| FROM `groups` g | |
| INNER JOIN `group_members` gm ON gm.groups_id = g.id | |
| INNER JOIN `studentaccount` st ON st.studentID = gm.student_id | |
| INNER JOIN `groups_relations` gr ON gr.parent_groups_id = g.id | |
| INNER JOIN `groups` sg ON sg.id = gr.child_groups_id | |
| INNER JOIN `group_members` sgm ON sgm.groups_id = gr.child_groups_id and sgm.student_id = st.studentID | |
| INNER JOIN `cm_academic_paper_subjects` aps ON aps.id = sg.paperSubjectId | |
| INNER JOIN `cm_academic_paper` ap ON ap.id = aps.cm_academic_paper_id | |
| INNER JOIN `cm_syllabus_academic_term_settings` sats ON sats.id = ap.cm_syllabus_academic_term_settings_id | |
| INNER JOIN `academic_term` at ON at.id = sats.academic_term_id | |
| WHERE g.type = 'BATCH' | |
| GROUP BY st.studentID,at.id,gm.academic_status | |
| ORDER BY st.studentID,at.properties->>'$.orderNo' DESC | |
| ) AS t | |
| GROUP BY t.program_student_id | |
| ORDER BY t.program_student_id,t.orderNo;"); | |
| echo "\n Adding students to student batch log \n"; | |
| $this->executeQuery("INSERT IGNORE INTO `student_program_batch_log` (`program_student_id`, `program_id`, `batch_group_id`, `term_id`, `properties`, `created_by`, `updated_by`) | |
| SELECT st.studentID, JSON_UNQUOTE(g.properties->>'$.programId'),g.id,at.id,JSON_OBJECT( | |
| 'isActive',false, | |
| 'reason' , gm.properties->>'$.reason', | |
| 'academicStatus' , gm.academic_status | |
| ) AS `properties`,1,1 | |
| FROM `groups` g | |
| INNER JOIN `group_members` gm ON gm.groups_id = g.id | |
| INNER JOIN `studentaccount` st ON st.studentID = gm.student_id | |
| INNER JOIN `groups_relations` gr ON gr.parent_groups_id = g.id | |
| INNER JOIN `groups` sg ON sg.id = gr.child_groups_id | |
| INNER JOIN `group_members` sgm ON sgm.groups_id = gr.child_groups_id and sgm.student_id = st.studentID | |
| INNER JOIN `cm_academic_paper_subjects` aps ON aps.id = sg.paperSubjectId | |
| INNER JOIN `cm_academic_paper` ap ON ap.id = aps.cm_academic_paper_id | |
| INNER JOIN `cm_syllabus_academic_term_settings` sats ON sats.id = ap.cm_syllabus_academic_term_settings_id | |
| INNER JOIN `academic_term` at ON at.id = sats.academic_term_id | |
| WHERE g.type = 'BATCH' | |
| GROUP BY st.studentID,at.id,gm.academic_status | |
| ORDER BY st.studentID,at.properties->>'$.orderNo' DESC;"); | |
| echo "\n Adding students from group members to student program accounts \n"; | |
| $this->executeQuery("INSERT IGNORE INTO `student_program_account` (`id`, `student_id`, `current_program_id`, `properties`, `created_by`, `updated_by`) | |
| SELECT `student_id`,`student_id`,JSON_UNQUOTE(g.properties->>'$.programId') AS `current_program_id`,JSON_OBJECT( | |
| 'registerNumber', a.regNo, | |
| 'rollNumber', a.rollNo, | |
| 'admissionNumber', a.admissionNo, | |
| 'status', gm.academic_status, | |
| 'tcIssued', a.tcIssued, | |
| 'admitSemester', a.admitSemester, | |
| 'yearOfPassing', a.yearOfPassing, | |
| 'academicStatus', gm.academic_status, | |
| 'currentBatchId', g.id, | |
| 'termId', g.academic_term_id, | |
| 'academicStatus' , gm.academic_status, | |
| 'reason', gm.properties->>'$.reason', | |
| 'tcIssued' , a.tcIssued, | |
| 'yearOfPassing', g.properties->>'$.yearOfPassing' | |
| ) AS `properties`,1,1 FROM `groups` g | |
| INNER JOIN `group_members` gm ON gm.groups_id = g.id | |
| INNER JOIN `studentaccount` a ON a.studentID = gm.student_id | |
| WHERE `student_id` NOT IN (SELECT `student_id` FROM `student_program_account`) AND g.type = 'BATCH';"); | |
| echo "\n Adding newly added students to student program batch log \n"; | |
| $this->executeQuery("INSERT IGNORE INTO `student_program_batch_log` (program_student_id, program_id, batch_group_id, term_id, properties, created_by, updated_by) | |
| SELECT st.studentID, json_unquote(g.properties->>'$.programId'),g.id,json_unquote(g.properties->>'$.currentTermId'),JSON_OBJECT( | |
| 'isActive',false, | |
| 'reason' , gm.properties->>'$.reason', | |
| 'academicStatus' , gm.academic_status | |
| ) AS properties,1,1 | |
| FROM `groups` g | |
| INNER JOIN `group_members` gm ON gm.groups_id = g.id | |
| INNER JOIN `studentaccount` st ON st.studentID = gm.student_id | |
| WHERE g.type = 'BATCH' and gm.student_id not in (select `program_student_id` FROM `student_program_batch_log`);"); | |
| echo "\n Adding newly added students to student program batch log - type 2 \n"; | |
| $this->executeQuery("INSERT IGNORE INTO `student_program_batch_log` (`program_student_id`,`program_id`,`batch_group_id`,`term_id`,`properties`) | |
| SELECT sp.id,p.id,sp.current_batch_id,sp.current_term_id,JSON_OBJECT('reason',null,'isActive',true,'academicStatus','ACTIVE') | |
| FROM `studentaccount` s | |
| INNER JOIN `student_program_account` sp ON sp.student_id = s.studentID | |
| INNER JOIN `program` p ON p.id = sp.current_program_id | |
| WHERE sp.id NOT IN (SELECT `program_student_id` FROM `student_program_batch_log`);"); | |
| echo "\n Adding newly added students to student program batch log - type 3 \n"; | |
| $this->executeQuery("UPDATE IGNORE `student_program_batch_log` log | |
| INNER JOIN ( | |
| SELECT * FROM ( | |
| SELECT log.id,log.program_student_id,log.program_id,log.term_id,at.properties->>'$.orderNo' AS orderNo | |
| FROM `student_program_batch_log` log | |
| INNER JOIN `groups` g ON g.id = log.batch_group_id | |
| INNER JOIN `academic_term` at ON at.id = log.term_id | |
| INNER JOIN `batches` b ON b.groups_id = g.id | |
| ORDER BY b.batchCreateTime DESC,at.properties->>'$.orderNo' DESC) AS t | |
| GROUP BY t.program_student_id) AS t ON t.id = log.id | |
| SET log.properties = JSON_SET(log.properties,'$.isActive',true);"); | |
| echo "\n Adding students from failed_students to student program account - type 3 \n"; | |
| $this->executeQuery("UPDATE IGNORE `student_program_account` spa | |
| INNER JOIN `student_program_batch_log` log ON log.program_student_id = spa.id | |
| INNER JOIN `groups` g ON g.id = spa.current_batch_id | |
| INNER JOIN `batches` b ON b.groups_id = g.id | |
| INNER JOIN `academic_term` sat ON sat.id = spa.current_term_id | |
| INNER JOIN `academic_term` bat ON bat.id = g.academic_term_id | |
| INNER JOIN `semesters` s ON s.termId = log.term_id | |
| INNER JOIN `failed_students` fs ON fs.failedInSemester = s.semID | |
| AND spa.student_id = fs.studentID | |
| AND fs.previousBatch = b.batchID | |
| SET | |
| spa.properties = JSON_SET(spa.properties,'$.status','FAILED'), | |
| spa.updated_date = fs.updated_date,spa.created_date = fs.updated_date, | |
| spa.created_by = fs.updated_by, | |
| spa.updated_by = fs.updated_by | |
| WHERE fs.isFailNow = 1;"); | |
| echo "\n Updating logs to newly added students \n"; | |
| $this->executeQuery("UPDATE IGNORE `student_program_batch_log` log | |
| INNER JOIN `student_program_account` spa ON log.program_student_id = spa.id | |
| INNER JOIN `groups` g ON g.id = spa.current_batch_id | |
| INNER JOIN `batches` b ON b.groups_id = g.id | |
| INNER JOIN `academic_term` sat ON sat.id = spa.current_term_id | |
| INNER JOIN `academic_term` bat ON bat.id = g.academic_term_id | |
| INNER JOIN `semesters` s ON s.termId = log.term_id | |
| INNER JOIN `failed_students` fs ON fs.failedInSemester = s.semID AND spa.student_id = fs.studentID AND fs.previousBatch = b.batchID | |
| SET | |
| log.properties = JSON_SET(log.properties,'$.academicStatus','FAILED','$.reason',fs.reason), | |
| log.updated_date = fs.updated_date, | |
| log.created_date = fs.updated_date, | |
| log.created_by = fs.updated_by, | |
| log.updated_by = fs.updated_by | |
| WHERE fs.isFailNow = 1;"); | |
| echo "\n Updating logs to newly added students - type 2 \n"; | |
| $this->executeQuery("UPDATE IGNORE `student_program_batch_log` log | |
| INNER JOIN `student_program_account` spa ON spa.id = log.program_student_id | |
| INNER JOIN `groups` g ON g.id = spa.current_batch_id | |
| SET log.properties = json_set(log.properties,'$.academicStatus','COMPLETED') | |
| WHERE CONCAT(log.batch_group_id,'-',log.term_id) <> CONCAT(spa.current_batch_id,'-',spa.current_term_id) | |
| AND log.properties->>'$.academicStatus' NOT IN ('FAILED','DISCONTINUE') ;"); | |
| echo "\n Updating logs to newly added students - type 3 \n"; | |
| $this->executeQuery("UPDATE IGNORE `student_program_batch_log` log | |
| INNER JOIN `student_program_account` spa ON spa.id = log.program_student_id AND spa.current_batch_id = log.batch_group_id AND spa.current_term_id = log.term_id | |
| INNER JOIN `groups` g ON g.id = spa.current_batch_id | |
| INNER JOIN `academic_term` bat ON bat.id = g.academic_term_id | |
| INNER JOIN `academic_term` sat ON sat.id = spa.current_term_id | |
| SET log.properties = JSON_SET(log.properties,'$.academicStatus','COMPLETED') | |
| WHERE log.properties->>'$.academicStatus' = 'ACTIVE' | |
| AND sat.properties->>'$.orderNo' < bat.properties->>'$.orderNo' | |
| AND bat.name = 'Passout Students';"); | |
| echo "\n Checking for other students \n"; | |
| $this->executeQuery("UPDATE IGNORE `student_program_account` sp | |
| INNER JOIN `student_program_batch_log` log ON log.program_student_id = sp.id | |
| AND log.term_id = sp.current_term_id | |
| AND log.batch_group_id = sp.current_batch_id | |
| SET sp.properties = json_set(sp.properties,'$.academicStatus',log.properties->>'$.academicStatus');"); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException ($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| public function insertV4Subjects($currentTimestamp){ | |
| try { | |
| echo "\n Adding students to student program accounts \n"; | |
| $this->executeQuery("INSERT IGNORE INTO v4_ams_subject (code,name,department_id,properties,created_by,created_date,updated_by,updated_date) | |
| select t.* from ( | |
| select s.subjectName,s.subjectDesc,s.hdl_deptID,json_object(),0 as created_by,UTC_TIMESTAMP() as created_date,0 as updated_by,UTC_TIMESTAMP() as updated_date | |
| from subjects s | |
| inner join department d ON d.deptID = s.hdl_deptID | |
| WHERE (s.created_date >= '$currentTimestamp' OR s.updated_date >= '$currentTimestamp') | |
| group by subjectName,subjectDesc) | |
| as t;"); | |
| $this->executeQuery("insert ignore into v4_subject_teaching_department_relation (subject_id,department_id,properties,created_by,created_date,updated_by,updated_date) | |
| select vas.id,sr.deptID,json_object(),0 as created_by,UTC_TIMESTAMP() as created_date,0 as updated_by,UTC_TIMESTAMP() as updated_date from subjects s | |
| inner join department d ON d.deptID = s.hdl_deptID | |
| inner join sd_relation sr ON sr.subjectID = s.subjectID | |
| inner join department srd ON srd.deptID = sr.deptID | |
| inner join v4_ams_subject vas ON vas.code = s.subjectName and vas.name = s.subjectDesc | |
| group by vas.id,sr.deptID;"); | |
| // Update academic paper subjects with the new v4_ams_subjects table | |
| $this->executeQuery("update cm_academic_paper_subjects caps | |
| inner join subjects s on s.subjectID = caps.subject_id | |
| inner join v4_ams_subject vas ON vas.code = s.subjectName and vas.name = s.subjectDesc | |
| set caps.ams_subject_id = vas.id ; | |
| "); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException ($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| public function mergeSubBatches($data) | |
| { | |
| $subBatches = []; | |
| foreach (json_decode($data) AS $key => $obj) { | |
| if (empty($subBatches[$obj->subbatchID])) { | |
| $subBatches[$obj->subbatchID]->subbatchID = (int)$obj->subbatchID; | |
| $subBatches[$obj->subbatchID]->name = $obj->subbatchName; | |
| } | |
| $subBatches[$obj->subbatchID]->staffIds[] = strval($obj->staffIds); | |
| } | |
| return array_values($subBatches); | |
| } | |
| public function getIds($connect) | |
| { | |
| $getIds = $connect->executeQueryForObject("SELECT LEFT(REPLACE(UUID(),'-',''), 17) AS id;"); | |
| return $getIds->id; | |
| } | |
| public function getPaperSubjectId($curriculumId, $curriculums) | |
| { | |
| foreach ($curriculums AS $key => $curriculum) { | |
| if ($curriculum->curriculumId === $curriculumId) { | |
| return $curriculum->paperSubjectId; | |
| } | |
| } | |
| return null; | |
| } | |
| public function createRelations($clusterId, $connect) | |
| { | |
| try { | |
| $sqlGroup = "INSERT IGNORE INTO `cluster_groups_relations` (`id`, `groups_id`, `cluster_id`, `properties`) | |
| SELECT LEFT(REPLACE(UUID(),'-',''), 17) AS `id`,g.id AS `groups_id`,c.id AS `cluster_id`,JSON_OBJECT() AS `properties` from `cluster` c | |
| INNER JOIN `groups` g ON JSON_CONTAINS(c.groups_ids, concat('\"',g.id,'\"'),'$') | |
| WHERE c.id = '$clusterId'"; | |
| $sqlCluster = "INSERT IGNORE INTO `groups_relations` (`id`, `parent_groups_id`, `child_groups_id`, `properties`) | |
| SELECT LEFT(REPLACE(UUID(),'-',''), 17) AS `id`,g.id AS `parent_groups_id`,sg.id AS `child_groups_id`,JSON_OBJECT() AS `properties` FROM | |
| `cluster_groups_relations` cg | |
| inner JOIN `groups` sg ON sg.id = cg.groups_id | |
| INNER JOIN `groups` g ON JSON_CONTAINS(sg.properties->'$.batchGroupIds', concat('\"',g.id,'\"'),'$') | |
| where cg.cluster_id = '$clusterId' AND sg.type = 'SUBJECT' AND g.type = 'BATCH' | |
| group by sg.id,g.id;"; | |
| $connect->executeQuery($sqlGroup); | |
| $connect->executeQuery($sqlCluster); | |
| return true; | |
| } catch (\Throwable $th) { | |
| echo "\n\e[0;31m* couldn't set relations \e[0m"; | |
| return false; | |
| } | |
| } | |
| public function createLog() | |
| { | |
| $log = new \stdClass(); | |
| $log->pseudosubjectId = ""; | |
| $log->batchId = ""; | |
| $log->type = ""; | |
| $log->semId = ""; | |
| $log->subjectId = ""; | |
| $log->clusterId = ""; | |
| $log->subbatchId = ""; | |
| $log->setClusterRelation = false; | |
| $log->complete = false; | |
| $log->created = false; | |
| $log->localUpdates = false; | |
| $log->groups = array(); | |
| return $log; | |
| } | |
| public function getGroupLog() | |
| { | |
| $log = new \stdClass(); | |
| $log->id = ""; | |
| $log->batchId = ""; | |
| $log->paperSubjectId = ""; | |
| $log->semId = ""; | |
| $log->subbatchId = ""; | |
| $log->created = false; | |
| $log->setMembers = false; | |
| return $log; | |
| } | |
| public function setLog($log, $connect, $id = null) | |
| { | |
| $log->complete = count($log) ? true : false; | |
| foreach ($log->groups AS $key => $o) { | |
| $log->complete = $o->setMembers && $o->created && $log->complete && $log->localUpdates && $log->setClusterRelation && $log->created; | |
| } | |
| try { | |
| if ($id) { | |
| $connect->executeQuery("UPDATE IGNORE `new_academics_migrations` SET `log` = '" . json_encode($log) . "' WHERE id = '$id';"); | |
| } else { | |
| $connect->executeQuery("INSERT IGNORE INTO `new_academics_migrations` (`log`) VALUES ('" . json_encode($log) . "')"); | |
| } | |
| //echo "\n* set log"; | |
| } catch (\Throwable $th) { | |
| echo "\n\e[0;31m* Couldn't Create or Update log\e[0m\n"; | |
| } | |
| } | |
| public function getCreatedLog($searchLog, $connect) | |
| { | |
| $where = []; | |
| $searchLog->pseudosubjectId ? $where[] = " log->'$.pseudosubjectId' = '" . $searchLog->pseudosubjectId . "' " : null; | |
| $searchLog->batchId ? $where[] = " log->'$.batchId' = " . $searchLog->batchId . " " : null; | |
| $searchLog->semId ? $where[] = " log->'$.semId' = " . $searchLog->semId . " " : null; | |
| $searchLog->type ? $where[] = " log->'$.type' = '" . $searchLog->type . "' " : null; | |
| $searchLog->subjectId ? $where[] = " log->'$.subjectId' = " . $searchLog->subjectId . " " : null; | |
| $searchLog->subbatchId ? $where[] = " log->'$.subbatchId' = " . $searchLog->subbatchId . " " : null; | |
| $allLogs = $connect->executeQuery("SELECT id ,log FROM new_academics_migrations WHERE " . implode(' AND ', $where) . ";"); | |
| foreach ($allLogs AS $key => $log) { | |
| $log = (object) $log; | |
| $log->log = json_decode($log->log); | |
| } | |
| return $allLogs; | |
| } | |
| public function createSearchLogRequest() | |
| { | |
| $searchLog = new \stdClass(); | |
| $searchLog->pseudosubjectId = ""; | |
| $searchLog->batchId = ""; | |
| $searchLog->semId = ""; | |
| $searchLog->type = ""; | |
| $searchLog->subjectId = ""; | |
| $searchLog->subbatchId = ""; | |
| return $searchLog; | |
| } | |
| public function findGroup($groups, $batchId, $semId, $subbatchId) | |
| { | |
| $get = false; | |
| foreach ($groups AS $key => $log) { | |
| if (((!$batchId) || ($batchId && $batchId == $log->batchId)) && ((!$semId) || ($semId && $semId == $log->semId)) && ((!$subbatchId) || ($subbatchId && $subbatchId == $log->subbatchId)) && !$get) { | |
| $logKey = $key; | |
| } | |
| } | |
| return $groups[$logKey]; | |
| } | |
| public function createGroup($id,$name,$type,$paperSubjectId,$batchGroupId,$subjectId){ | |
| $onceMore = false; | |
| $created = false; | |
| if($paperSubjectId){ | |
| echo "\n* paperSubjectId : $paperSubjectId \n"; | |
| try { | |
| $sql = "INSERT IGNORE INTO `groups`(`id`,`identifying_context`,`name`,`type`,`properties`) VALUES ('" . $id . "',JSON_OBJECT('paperSubjectId','" . $paperSubjectId . "'),\"" . $name . "\",'".$type."',JSON_OBJECT('batchGroupIds',json_array('$batchGroupId'),'currentTermId',null,'temp',true));"; | |
| // echo "\ncreate\n--------\n".$sql; | |
| $this->executeQuery($sql); | |
| $groupsData = $this->executeQueryForObject("select `id` from `groups` where id = '$id';"); | |
| $created = true; | |
| echo "\n* created groups :::: ".$groupsData->id."\n"; | |
| } catch (\Throwable $th) { | |
| $onceMore = true; | |
| } | |
| if($onceMore){ | |
| try { | |
| $nameSubjectCount = $this->executeQueryForObject("SELECT IF(COUNT(g.id) = 0,0,COUNT(g.id)+1) AS `subjectCount` FROM `groups` g | |
| INNER JOIN `cm_academic_paper_subjects` aps ON aps.id = g.paperSubjectId | |
| WHERE g.name LIKE \"$name%\";"); | |
| $subjectCount = (int) $nameSubjectCount->subjectCount; | |
| if ($subjectCount) { | |
| $sql = "INSERT IGNORE INTO `groups`(`id`,`identifying_context`,`name`,`type`,`properties`) VALUES ('" . $id . "',JSON_OBJECT('paperSubjectId','" . $paperSubjectId . "'),\"" . $name . " - " . $subjectCount . "\",'".$type."',JSON_OBJECT('batchGroupIds',json_array('$batchGroupId'),'currentTermId',null,'temp',true));"; | |
| // echo "\ncreate new\n-------------\n".$sql; | |
| $this->executeQuery($sql); | |
| $groupsData = $this->executeQueryForObject("select `id` from `groups` where `id` = '$id';"); | |
| echo "\n* created groups :::: ".$groupsData->id." \n"; | |
| $created = true; | |
| } | |
| } catch (\Throwable $th) { | |
| echo "\n\e[0;31m* couldn't create groups \e[0m"; | |
| } | |
| } | |
| }else{ | |
| echo "\n\e[0;31m* couldn't found paperSubjectId \e[0m"; | |
| } | |
| return $created; | |
| } | |
| public function createCluster($id,$name,$type,$groupIds,$properties){ | |
| $onceMore = false; | |
| $created = false; | |
| // $nameSubjectCountSql = "SELECT id FROM `cluster` WHERE `name` LIKE \"$name%\" AND `type` = '$type' GROUP BY `type`;"; | |
| // $nameSubjectCount = $this->executeQueryForObject($nameSubjectCountSql); | |
| // $clusterExistId = (int) $nameSubjectCount->id; | |
| // if($clusterExistId){ | |
| // return true; | |
| // } | |
| try { | |
| $sql = "INSERT IGNORE INTO `cluster` (`id`,`name`,`type`,`groups_ids`,`properties`) | |
| VALUES ('$id',\"$name\",'$type','" . json_encode($groupIds) . "','" . json_encode($properties) . "') ON DUPLICATE KEY UPDATE `properties` = VALUES(`properties`), `groups_ids` = VALUES(`groups_ids`)"; | |
| // echo "\ncreate\n--------\n".$sql; | |
| $this->executeQuery($sql); | |
| $created = true; | |
| $clusterData = $this->executeQueryForObject("select `id` from `cluster` where `id` = '$id';"); | |
| echo "\n* created cluster :::: ".$clusterData->id." \n"; | |
| $created = true; | |
| } catch (\Throwable $th) { | |
| $onceMore = true; | |
| echo "\n\e[0;31m* couldn't create cluster \n"; | |
| } | |
| if($onceMore){ | |
| try { | |
| $nameSubjectCount = $this->executeQueryForObject("SELECT (count(type)+1) AS `clusterCount` FROM `cluster` WHERE `name` LIKE \"$name%\" AND `type` = '$type' GROUP BY `type`;"); | |
| $clusterCount = (int) $nameSubjectCount->clusterCount; | |
| $sql = "INSERT IGNORE INTO `cluster` (`id`,`name`,`type`,`groups_ids`,`properties`) | |
| VALUES ('$id',\"".$name." - ".$clusterCount."\",'$type','" . json_encode($groupIds) . "','" . json_encode($properties) . "') ON DUPLICATE KEY UPDATE `properties` = VALUES(`properties`), `groups_ids` = VALUES(`groups_ids`)"; | |
| // echo "\ncreate\n--------\n".$sql; | |
| $this->executeQuery($sql); | |
| $created = true; | |
| $clusterData = $this->executeQueryForObject("select `id` from `cluster` where `id` = '$id';"); | |
| echo "\n* created cluster :::: ".$clusterData->id." \n"; | |
| // echo "\n* create cluster\n"; | |
| $created = true; | |
| } catch (\Throwable $th) { | |
| echo "\n\e[0;31m* couldn't create cluster \e[0m\n"; | |
| } | |
| } | |
| return $created; | |
| } | |
| } |