gistfile1.txt

-- -- --  Data Migration For Organizor -- -- --

-- What is it?
--  List of SQL queries to migrate data from old database to the newly designed database.
--  It has been classified into two levels
--    1. Level 1 (which will be in top of database hierarchy) 
--    2. Level 2 (which will be depend on the Level 1 tables)

-- Prerequisites for data migration
--  Login into MySQL prompt
--   create database organizor;
--  Get into application folder(organizor)
--   Check for the password in database.yml whether it matches your local database.
--   RAILS_ENV=development bundle exec rake db:migrate 

-- How it works?
--  These are normal SQL commands which could be execute in a sequence as written in the file.
--  Reference: http://dev.mysql.com/doc/refman/5.0/en/batch-mode.html

-- How to execute it?
--   mysql -u root -ppassword organizor < database_migration.sql > output.out

SET @start=UNIX_TIMESTAMP();

-- Level 1

-- Migrating Country
INSERT INTO countries(name)
  SELECT DISTINCT(CountryName)
  FROM 527433_iec.iec_country;

-- Migrating Branch
INSERT INTO branches(name)
  SELECT DISTINCT(BranchName)
  FROM 527433_iec.iec_branch;

-- Migrating course_levels
INSERT INTO course_levels(name)
  SELECT DISTINCT(Graduation)
  FROM 527433_iec.iec_university;

-- Migrating application_statuses
INSERT INTO application_statuses(name,desc,email_registration)
  SELECT (StatusName,StudentMessageE,MailToStudent)
  FROM 527433_iec.iec_status;

-- Update country id in branch
UPDATE branches b
  INNER JOIN 527433_iec.iec_branch ob
  ON ob.BranchName = b.name
  INNER JOIN 527433_iec.iec_country oc
  ON ob.country = oc.CountryId
  INNER JOIN countries c
  ON oc.CountryName = c.name
SET b.country_id = c.id;

-- Migration qualification_name
INSERT INTO qualification_names(name)
  SELECT DISTINCT(Class)
  FROM 527433_iec.iec_qualification;

-- Migration course_subjects
INSERT INTO course_subjects(name)
  SELECT DISTINCT(CourseName)
  FROM 527433_iec.iec_course_master;

-- Migration sub_agents
INSERT INTO sub_agents(name)
  SELECT DISTINCT(AgentName)
  FROM 527433_iec.iec_sub_agent;


-- Level 2

-- Migration users
INSERT INTO users(first_name, email, encrypted_password, address)
  SELECT UserName, UserId, Password, Address
  FROM 527433_iec.iec_user;

UPDATE users u
  INNER JOIN 527433_iec.iec_user ou
  ON ou.UserName = u.first_name
  INNER JOIN 527433_iec.iec_branch ob
  ON ob.BranchId = ou.BranchId
  INNER JOIN branches b
  ON ob.BranchName = b.name
SET u.branch_id = b.id;

-- Migration registrations
INSERT INTO registrations(ref_no, first_name, surname, date_of_birth, address_line1, email, alternate_email,
   mobile1, home_phone, passport_number, visa_type, visa_valid_till,
   sub_agent_id, encrypted_password, updated_at,  created_at)
  SELECT RefNo, FName, LName, DOB, Address, id, Email2,
   Mobile, Phone,PassportNo, visa_type, visa_exp_date,
   AgentId, StPassword, EditDate, Date
  FROM 527433_iec.iec_students;

UPDATE registrations r
  INNER JOIN 527433_iec.iec_students os
  ON os.RefNo = r.ref_no
  INNER JOIN 527433_iec.iec_branch ob
  ON ob.BranchId = os.BranchId
  INNER JOIN branches b
  ON ob.BranchName = b.name
SET r.branch_id = b.id;

UPDATE registrations r
  INNER JOIN 527433_iec.iec_students os
  ON os.RefNo = r.ref_no
  INNER JOIN 527433_iec.iec_country oc
  ON oc.CountryId = os.CountryId
  INNER JOIN countries c
  ON oc.CountryName = c.name
SET r.country_id = c.id;


UPDATE registrations r
  INNER JOIN 527433_iec.iec_students os
  ON os.RefNo = r.ref_no
  INNER JOIN 527433_iec.iec_sub_agent osa
  ON osa.AgentId = os.AgentId
  INNER JOIN people sa
  ON osa.AgentName = sa.first_name
SET r.sub_agent_id = sa.id;

-- Migration Notes
INSERT INTO notes (content,created_by,created_type,noteable_id,noteable_type)
SELECT comment,user_id,"User",student_id,"Registration" FROM 527433_iec.iec_students_comments;

UPDATE notes n
  INNER JOIN users u
  ON n.created_by = u.email
SET n.created_by = u.id;  

​UPDATE notes n
  INNER JOIN registrations r
  ON n.noteable_id = r.email
SET n.noteable_id = r.id;​

-- Migration partners
INSERT INTO partner_types (name) values ('University'), ('Language_School'),
 ('Business_Partner'), ('Education_Provider'), ('Official_Sub_Agent'), ('Private_Provider');


INSERT INTO partners (name)
  SELECT DISTINCT(UniversityName)
  FROM 527433_iec.iec_universitylist;

UPDATE partners set type_id = (SELECT id from partner_types where name = 'University');

INSERT INTO partners (name)
  SELECT DISTINCT(CenterName)
  FROM 527433_iec.iec_language_center;

UPDATE partners set type_id = (SELECT id from partner_types where name = 'Language_School') where type_id IS NULL;


-- Migration programmes

INSERT INTO programmes (course_subject_text, registration_id, created_at)
 SELECT ou.Course, r.id, ou.Date
 FROM 527433_iec.iec_university ou, 527433_iec.iec_students os, registrations r
 where ou.StId = os.id and os.RefNo = r.ref_no;


UPDATE programmes p
  INNER JOIN 527433_iec.iec_university ou
  ON ou.Course = p.course_subject_text
  INNER JOIN 527433_iec.iec_universitylist oul
  ON oul.UniversityId = ou.UniversityId
  INNER JOIN partners i
  ON oul.UniversityName = i.name
  INNER JOIN course_levels cl
  ON ou.Graduation = cl.name 
SET p.partner_id = i.id, p.country_id = i.country_id, 
    p.type_id = i.type_id, p.level_id = cl.id, 
    p.start_date = str_to_date(concat(concat(concat(ou.iyear,' 1'),','),ou.intake),'%Y %d,%M'),
    p.ins_ref_no = ou.URefNo,
    p.app_status_id = ou.StatusId;

-- programmes for language center
INSERT INTO programmes (course_subject_text, registration_id)
  SELECT olc.course, r.id
  FROM 527433_iec.iec_language_centerlist olc, 527433_iec.iec_students os, registrations r
  where olc.StId = os.id and os.RefNo = r.ref_no;

-- Insert english course level
INSERT INTO course_levels(name) VALUES('english');

UPDATE programmes p
  INNER JOIN 527433_iec.iec_language_centerlist olc
  ON olc.course = p.course_subject_text
  INNER JOIN 527433_iec.iec_language_center ol
  ON ol.CenterId = olc.CenterId
  INNER JOIN partners i
  ON ol.CenterName = i.name
SET p.partner_id = i.id, p.country_id = i.country_id, 
    p.type_id = i.type_id, p.start_date = olc.start_date, 
    p.end_date = olc.end_date,
    p.level_id = (SELECT id FROM course_levels WHERE name = 'english'),
    p.ins_ref_no = olc.refernce,
    p.app_status_id = olc.StatusId
WHERE p.type_id IS NULL and p.country_id IS NULL;

-- Update programmes app_status_id
UPDATE programmes p
INNER JOIN 527433_iec.iec_status is
ON p.app_status_id = is.id
INNER JOIN application_statuses ast
ON is.StatusName = ast.name
SET p.app_status_id = ast.id;

UPDATE partners p
  INNER JOIN 527433_iec.iec_universitylist ul
  ON ul.UniversityName = p.name  
SET p.country_id = ul.CountryId; 

UPDATE partners p
  INNER JOIN 527433_iec.iec_language_center lc
  ON lc.CenterName = p.name
SET p.country_id = lc.CountryId; 



UPDATE partners p
  INNER JOIN 527433_iec.iec_country ic
  ON p.country_id = ic.countryid
  INNER JOIN countries c
  ON c.name = ic.countryname
SET p.country_id = c.id;

-- Update assigned to in registrations
UPDATE registrations r
  INNER JOIN 527433_iec.iec_students ist
  ON r.email = ist.id
  INNER JOIN 527433_iec.iec_user iu
  ON ist.userid = iu.userid
  INNER JOIN users u
  ON u.first_name = iu.username
SET r.assigned_to = u.id;

SET
@s=@seconds:=UNIX_TIMESTAMP()-@start,
@d=TRUNCATE(@s/86400,0), @s=MOD(@s,86400),
@h=TRUNCATE(@s/3600,0), @s=MOD(@s,3600),
@m=TRUNCATE(@s/60,0), @s=MOD(@s,60),
@day=IF(@d>0,CONCAT(@d,' day'),''),
@hour=IF(@d+@h>0,CONCAT(IF(@d>0,LPAD(@h,2,'0'),@h),' hour'),''),
@min=IF(@d+@h+@m>0,CONCAT(IF(@d+@h>0,LPAD(@m,2,'0'),@m),' min.'),''),
@sec=CONCAT(IF(@d+@h+@m>0,LPAD(@s,2,'0'),@s),' sec.');

SELECT
CONCAT(@seconds,' sec.') AS seconds,
CONCAT_WS(' ',@day,@hour,@min,@sec) AS elapsed;