-- ก่อนนำไปใส่ ให้ตรวจสอบชื่อ ฐานข้อมูล Ealumni แบบเก่าและใหม่

-- INSERT IGNORE INTO registealumni.TypeAward SELECT * FROM ealumni.TypeAword;

-- INSERT IGNORE INTO registealumni.Education SELECT * FROM ealumni.Education;

-- INSERT IGNORE INTO registealumni.LevelAward (levelAwardId, levelAwardNameE, 
-- levelAwardNameT) SELECT levelawordId, levelawordNameE, levelawordNameT FROM 
-- ealumni.LevelAword;

INSERT IGNORE INTO registealumni.ea_CurriculumConfig 
SELECT ccId, ccCode, ccName, ccStatus FROM regist.rg_CurriculumConfig;

-- INSERT IGNORE INTO registealumni.LevelEdu SELECT * FROM ealumni.LevelEdu;

-- INSERT IGNORE INTO registealumni.MajorEdu SELECT * FROM ealumni.MajorEdu;

-- INSERT IGNORE INTO registealumni.ImportUpdate SELECT importId, '0', importDate, '' 
-- FROM ealumni.ImportUpdate;

-- INSERT IGNORE INTO registealumni.MapProgram SELECT mapId, '0', programId_reg, 
-- programId_alumni FROM ealumni.MapProgram;

INSERT IGNORE INTO registealumni.ea_CurriculumDetails (curId,
curImproveY, curName, curNameE, curAbbr, curAbbrE, curStatus, curCcId, ccCode, ccName, 
curEdgId, edgName, edgNameE, edgAbbr, edgAbbrE, curElvId, levelName, levelNameEng, 
levelAbbr, levelAbbrEng, curAddType) SELECT curId, curImproveY, curName, curNameE, 
curAbbr, curAbbrE, curStatus, curCcId, ccCode, ccName, curEdgId, edgName, edgNameE, 
edgAbbr, edgAbbrE, curElvId, levelName, levelNameEng, levelAbbr, levelAbbrEng , 'R' AS 
curAddType FROM regist.rg_Curriculum
LEFT JOIN regist.rg_CurriculumConfig ON curCcId=ccId
LEFT JOIN regist.rg_EduDegree ON curEdgId=edgId
LEFT JOIN peoplecenter.Level ON curElvId=levelId;

INSERT IGNORE INTO registealumni.ea_EduDegree
SELECT edgId, edgName, edgNameE, edgAbbr, edgAbbrE FROM regist.rg_EduDegree;

INSERT IGNORE INTO registealumni.ea_Generation
SELECT genId, genAcY, genNo, genCurId, genTmId FROM regist.rg_Generation;

-- INSERT IGNORE INTO registealumni.ProgramAlumni 
-- SELECT * FROM ealumni.ProgramAlumni;

-- UPDATE registealumni.AlumniMain SET `admitDate`=CONCAT( LEFT( `admitDate` , 4 ) -543, RIGHT( `admitDate` , 6 ) );
-- UPDATE registealumni.AlumniMain SET `finishDate`=CONCAT( LEFT( `finishDate` , 4 ) -543, RIGHT( `finishDate` , 6 ) );


-- ***************** อัพเดท programId
-- ตรวจสอบหลักสูตร AlumniMain.programId ว่าเป็นหลักสูตรที่จับคู่แล้วหรือยัง
-- SELECT alumniId, programId, programId_alumni, programId_reg
-- FROM  registealumni.`AlumniMain` 
-- LEFT JOIN registealumni.MapProgram ON programId = programId_alumni;

UPDATE registealumni.`AlumniMain` 
LEFT JOIN registealumni.MapProgram ON programId = programId_alumni
SET `amProgramAlumni`=programId;

-- SELECT alumniId, programId, programId_alumni, programId_reg, curId, curElvId, curName
-- FROM registealumni.`AlumniMain` 
-- LEFT JOIN registealumni.MapProgram ON programId = programId_alumni
-- LEFT JOIN registealumni.ea_CurriculumDetails ON programId_reg = curId;

UPDATE registealumni.`AlumniMain` 
LEFT JOIN registealumni.MapProgram ON programId = programId_alumni
LEFT JOIN registealumni.ea_CurriculumDetails ON programId_reg = curId
SET programId=programId_reg, levelId=curElvId;


-- ***************** จัดการฐาน alumni_ums
INSERT IGNORE INTO alumni_ums.umuser 
SELECT '', CONCAT(prefixName,studentName,' ',studentSurname), studentCode, MD5(CONCAT('O]O',studentCode,'O[O')), studentCode, 6, NULL, NULL, NULL, 1, 0, NULL, NULL, NULL, NULL, '' 
FROM registealumni.AlumniMain am 
LEFT JOIN peoplecenter.Prefix pf ON am.prefixId=pf.prefixId;

-- ***************** จัดการสิทธิ์ umusergroup
INSERT IGNORE INTO alumni_ums.umusergroup
SELECT 1, 191001, UsID
FROM alumni_ums.umuser