-- MIGRATE DEGREE ============================================================================================================================================ INSERT INTO degree (id,name,description,created_by,created_date,updated_by,updated_date) SELECT patternID AS id, patternName AS name,patternDesc AS description, 1 AS created_by,UTC_TIMESTAMP() AS created_date,1 AS updated_by,UTC_TIMESTAMP() AS updated_date from course_pattern; update course_pattern cp inner join degree d on d.id = cp.patternID set cp.degree_id = d.id where cp.patternID; ============================================================================================================================================ SELECT * FROM pattern_deptcourses; SELECT * FROM stream; select LEFT(MD5(RAND()), 17); ============================================================================================================================================ -- MIGRATE STREAM ============================================================================================================================================ ALTER TABLE `stream` DROP INDEX `name_UNIQUE_stream_idx`; ALTER TABLE stream ADD UNIQUE `department_stream_unique`(name, departmentId); INSERT INTO stream (id, name, properties, trashed, departmentId, created_by, created_date, updated_by, updated_date) SELECT patterncourseID AS id, patterncourseName AS name, JSON_OBJECT('code',patterncourseCode,'abbreviation',IFNULL(patternAbbreviation,""),'isActive',isHide) AS properties, NULL AS trashed, deptID AS departmentId, 1 AS created_by, UTC_TIMESTAMP() AS created_date, 1 AS updated_by, UTC_TIMESTAMP() AS updated_date FROM pattern_deptcourses WHERE deptID in (select deptID from department) AND patterncourseName <> "" GROUP BY patterncourseName,deptID; -- MIGRATE PROGRAM ============================================================================================================================================ INSERT IGNORE INTO program (id, name, course_type_id, degree_id, stream_id, specialization, properties, trashed, created_by, created_date, updated_by, updated_date) SELECT LEFT(MD5(RAND()), 17) AS id, IF(pdc.patterncourseName LIKE CONCAT('%',cp.patternName,'%'),pdc.patterncourseName,concat(cp.patternName,' - ',pdc.patterncourseName)) AS name, b.courseTypeID AS course_type_id, pdc.patternID AS degree_id, JSON_ARRAY(cast(pdc.patterncourseID AS CHAR)) AS stream_id, JSON_OBJECT() AS specialization, JSON_OBJECT() AS properties, NULL AS trashed, 1 AS created_by, UTC_TIMESTAMP() AS created_date, 1 AS updated_by, UTC_TIMESTAMP() AS updated_date FROM batches b INNER JOIN course_type ct ON ct.courseTypeID = b.courseTypeID INNER JOIN course_pattern cp ON cp.patternID = b.patternID LEFT JOIN pattern_deptcourses pdc ON pdc.patternID = b.patternID AND b.deptID = pdc.deptID GROUP BY b.courseTypeID,b.patternID,b.patterncourseID; ALTER TABLE `stthomas_07`.`program` CHANGE COLUMN `stream_id` `stream_id` VARCHAR(600) NULL DEFAULT NULL ; ALTER TABLE `stthomas_07_test1`.`program` ADD UNIQUE INDEX `index6` (`stream_id` ASC, `degree_id` ASC, `course_type_id` ASC, `name` ASC), DROP INDEX `index5` ; update pattern_deptcourses pds left join stream s on pds.patterncourseID = s.id set pds.stream_ids = json_array(s.id) where s.id is not null; -- Checking all course_pattern migrated correctly select * from course_pattern cp left join degree d on d.id = cp.degree_id where d.id is null; -- Create Batches -- insert ignore into groups (id,name,type,properties,trashed,created_by,created_date,updated_by,updated_date) select b.batchID as id,b.batchName as name,'BATCH' as type, JSON_OBJECT( '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','SEMESTER', 'currentTermId',at.id, 'finalTermId',cast(b.final_semester as CHAR), 'description',b.batchDesc, 'isPassout',false, 'tcIssueDate',b.tcIssueDate, 'yearOfPassing',if(b.batchEndYear,cast(b.batchEndYear AS CHAR),null), 'campusTypeId',b.campus_typeID, 'universityId',b.universityId, 'studentSortBy',b.studentSortBy, 'departmentId',b.deptID, 'programId',p.id, 'curriculumId','', 'extraSyllabusId',json_array(), 'createdNow',true, 'isActive',b.batchHide, 'batchDisplayOrder',b.batchDisplayOrder ) as properties,1 as trashed,1 as created_by,utc_timestamp() as created_date,1 as updated_by,utc_timestamp() as updated_date from batches b inner join semesters sem on sem.semID = b.semID inner join academic_term at on sem.termId = at.id inner join course_type ct on ct.courseTypeID = b.campus_typeID inner join course_pattern cp on cp.patternID = b.patternID inner join pattern_deptcourses pdc on pdc.patternID = b.patternID and pdc.deptID = b.deptID INNER join degree d on d.id = cp.degree_id INNER join stream s on ( (pdc.patterncourseName = s.name and pdc.deptID = s.departmentId ) ) INNER join program p on p.course_type_id = ct.courseTypeID and p.degree_id = d.id and JSON_CONTAINS(p.stream_id,concat('"',s.id,'"'),'$') -- set g.properties = JSON_SET(g.properties, "$.departmentId", d.deptID) group by b.batchID; delete from groups -- update batches set -- groups_id = null where id not in (select groups_id from group_members) and id not in (select groups_id from batch_paper_subject); update batches set groups_id = batchID where batchID in (select id from groups); -- FOR CREATING CURRICULUM -- =============================================== -- insert into cm_curriculum (id, name, description, type, properties, trashed, created_by, created_date, updated_by, updated_date) select LEFT(MD5(RAND()), 17) AS id, concat(p.name,' (',min(b.batchStartYear),'-',max(b.batchEndYear),')-',group_concat(distinct d.deptName),'-',group_concat(distinct ct.typeName)) as name, concat(p.name,' (',min(b.batchStartYear),'-',max(b.batchEndYear),')') as description, 'SINGLE_MAJOR' as type, json_object('programId',p.id,'startYear',cast(min(b.batchStartYear) as CHAR),'endYear',cast(max(b.batchEndYear) as CHAR)) as properties, null as trashed, 1 as createdBy, 1 as updated_by, utc_timestamp() as created_date, utc_timestamp() as updated_date from batches b inner join groups g on g.type = 'BATCH' and g.id = b.groups_id inner join sbs_relation sbs on sbs.batchID = b.batchID inner join program p on p.id = g.properties->'$.programId' inner join course_type ct on ct.courseTypeID = p.course_type_id inner join stream s on JSON_CONTAINS(p.stream_id,concat('"',s.id,'"'),'$') inner join department d on d.deptID = s.departmentId group by g.properties->'$.programId'; select * from program; select * from cm_syllabus; -- FOR CREATING SYLLABUS -- ========================================================== -- insert into cm_syllabus (id, name, description, type, properties, trashed, created_by, created_date, updated_by, updated_date) select LEFT(MD5(RAND()), 17) AS id, concat(p.name,' (',min(b.batchStartYear),'-',max(b.batchEndYear),')-',d.deptName,'-',group_concat(distinct ct.typeName)) as name, concat(p.name,' (',min(b.batchStartYear),'-',max(b.batchEndYear),')') as description, 'MAJOR' as type, json_object('departmentId',d.deptID,'curriculumId',cm.id) as properties, null as trashed, 1 as createdBy, 1 as updated_by, utc_timestamp() as created_date, utc_timestamp() as updated_date from batches b inner join groups g on g.type = 'BATCH' and g.id = b.groups_id inner join sbs_relation sbs on sbs.batchID = b.batchID inner join program p on p.id = g.properties->'$.programId' inner join course_type ct on ct.courseTypeID = p.course_type_id inner join stream s on JSON_CONTAINS(p.stream_id,concat('"',s.id,'"'),'$') inner join department d on d.deptID = s.departmentId inner join cm_curriculum cm on cm.properties->'$.programId' = p.id group by g.properties->'$.programId',d.deptID; -- FOR CREATING CURRICULUM SYLLABUS RELATIONS -- ========================================================== -- insert into cm_curriculum_syllabus_relation (cm_curriculum_id, cm_syllabus_id, properties, trashed, createdBy, updated_by, created_date, updated_date) select cm.id as cm_curriculum_id, sy.id as cm_syllabus_id, json_object('departmentId',cast(sy.properties->'$.departmentId' as CHAR)) as properties, null as trashed, 1 as createdBy, 1 as updated_by, utc_timestamp() as created_date, utc_timestamp() as updated_date from cm_curriculum cm inner join cm_syllabus sy on sy.properties->'$.curriculumId' = cm.id group by cm.id; -- FOR CREATING ACADEMICS TERM SETTINGS (SYLLABUS TERM RELATIONS) -- ========================================================== -- insert into cm_syllabus_academic_term_settings (id, cm_syllabus_id, academic_term_id, properties, trashed, created_by, updated_by, created_date, updated_date) select UPPER(LEFT(MD5(RAND()), 17)) AS id, sy.id as cm_syllabus_id, at.id as academic_term_id, json_object() as properties, null as trashed, 1 as created_by, 1 as updated_by, utc_timestamp() as created_date, utc_timestamp() as updated_date from batches b inner join groups g on g.id = b.groups_id inner join sbs_relation sbs on sbs.batchID = b.batchID inner join semesters sem on sem.semID = sbs.semID inner join academic_term at on sem.termId = at.id inner join program p on p.id = g.properties->'$.programId' inner join cm_curriculum cm on cm.properties->'$.programId' = p.id inner join cm_curriculum_syllabus_relation csr on csr.cm_curriculum_id = cm.id inner join cm_syllabus sy on sy.id = csr.cm_syllabus_id where g.type = 'BATCH' group by sy.id,sbs.semID; -- FOR CREATING ACADEMICS PAPER -- ========================================================== -- insert into cm_academic_paper (id, name, cm_syllabus_academic_term_settings_id, properties, trashed, created_by, updated_by, created_date, updated_date) select UPPER(LEFT(MD5(RAND()), 17)) AS id, sub.subjectDesc as name, ats.id as cm_syllabus_academic_term_settings_id, json_object('subjectTypeId','','noOfSubjectThatAStudentCanChoose',1,'subjectId',sbs.subjectID) as properties, null as trashed, 1 as created_by, 1 as updated_by, utc_timestamp() as created_date, utc_timestamp() as updated_date from batches b inner join groups g on g.id = b.groups_id inner join sbs_relation sbs on sbs.batchID = b.batchID inner join subjects sub on sub.subjectID = sbs.subjectID inner join semesters sem on sem.semID = sbs.semID inner join academic_term at on sem.termId = at.id inner join cm_curriculum cm on cm.properties->'$.programId' = g.properties->'$.programId' inner join cm_curriculum_syllabus_relation csr on csr.cm_curriculum_id = cm.id inner join cm_syllabus sy on sy.id = csr.cm_syllabus_id inner join cm_syllabus_academic_term_settings ats on ats.cm_syllabus_id = csr.cm_syllabus_id and ats.academic_term_id = at.id where g.type = 'BATCH' group by ats.id,sbs.subjectID; -- FOR CREATING ACADEMICS PAPER SUBJECTS -- ========================================================== -- insert into cm_academic_paper_subjects (id, cm_academic_paper_id, subject_id, properties, trashed, created_by, updated_by, created_date, updated_date) select UPPER(LEFT(MD5(RAND()), 17)) AS id, ap.id as cm_academic_paper_id, ap.properties->'$.subjectId' as subject_id, json_object('credit','','isActive',true,'isExternal','','isInternal','','gradeSchemeId','', 'subjectTypeId','', 'studentCanChoose',1, 'excludeSubjectFromTotal','', 'noOfSubjectThatAStudentCanChoose',1) as properties, null as trashed, 1 as created_by, 1 as updated_by, utc_timestamp() as created_date, utc_timestamp() as updated_date from batches b inner join groups g on g.id = b.groups_id inner join sbs_relation sbs on sbs.batchID = b.batchID inner join subjects sub on sub.subjectID = sbs.subjectID inner join semesters sem on sem.semID = sbs.semID inner join academic_term at on sem.termId = at.id inner join cm_curriculum cm on cm.properties->'$.programId' = g.properties->'$.programId' inner join cm_curriculum_syllabus_relation csr on csr.cm_curriculum_id = cm.id inner join cm_syllabus sy on sy.id = csr.cm_syllabus_id inner join cm_syllabus_academic_term_settings ats on ats.cm_syllabus_id = csr.cm_syllabus_id and ats.academic_term_id = at.id inner join cm_academic_paper ap on ap.cm_syllabus_academic_term_settings_id = ats.id and ap.properties->'$.subjectId' = sbs.subjectID where g.type = 'BATCH' group by ats.id,sbs.subjectID; -- SET CURRICULUM TO BATCH -- ===================================================================== update groups g inner join batches b on g.id = b.groups_id inner join sbs_relation sbs on sbs.batchID = b.batchID inner join subjects sub on sub.subjectID = sbs.subjectID inner join semesters sem on sem.semID = sbs.semID inner join academic_term at on sem.termId = at.id inner join cm_curriculum cm on cm.properties->'$.programId' = g.properties->'$.programId' set g.properties = JSON_SET(g.properties, '$.curriculumId', cm.id);