DROP TABLE IF EXISTS rlcos_data; CREATE TABLE rlcos_data ( id int(11), name varchar(255), category_id int(11), category_id_name varchar(255), department_id int(11), department_id_name varchar(255), latitude varchar(255), longitude varchar(255), main_info_video_path varchar(255), main_info_photo_path varchar(255), requirment_info_photo_path varchar(255), document_info_photo_path varchar(255), registration_num varchar(255), process_time varchar(255), process_time_unit varchar(255), contact_num varchar(255), fees varchar(255), renewal_period varchar(255), renewal_period_unit varchar(255), address varchar(255), doc_requirements text ); LOAD DATA LOCAL INFILE 'docs/rlcos-data.csv' INTO TABLE rlcos_data FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES; TRUNCATE rlcos; INSERT INTO rlcos (id, name, category_id, category_id_name, department_id, department_id_name, latitude, longitude, main_info_video_path, main_info_photo_path, requirment_info_photo_path, document_info_photo_path, registration_num, process_time, process_time_unit, contact_num, fees, renewal_period, renewal_period_unit, address, doc_requirements) SELECT id, name, category_id, category_id_name, department_id, department_id_name, latitude, longitude, main_info_video_path, main_info_photo_path, requirment_info_photo_path, document_info_photo_path, registration_num, process_time, process_time_unit, contact_num, fees, renewal_period, renewal_period_unit, address, doc_requirements from rlcos_data; update rlcos set main_info_video_path="rlcos/2/led-001.ffc8d9fc98d41c12ec46.mp4", requirment_info_photo_path="rlcos/2/labourAndHumanResource.945f6acd071cf1bf047e.PNG" where id%2==0 update rlcos set main_info_video_path="rlcos/8/LIcensetomanufacturedrug.596c413bca8f66153551.mp4", requirment_info_photo_path="rlcos/8/HealthDepartment.d8d6c2ef08b308b9b02e.PNG" where id=id%2==0 update rlcos set doc_requirements = REPLACE(doc_requirements,"â–ª", " • "); # TRUNCATE lookup_categories; #INSERT INTO lookup_categories (name, photo_path) SELECT DISTINCT category_id, category_id_name FROM rlcos; UPDATE lookup_categories s, rlcos t SET t.category_id=s.id WHERE s.name=t.category_id_name; # CHECK ALL CATEGORIES ASSIGNED SELECT DISTINCT category_id_name FROM rlcos WHERE category_id NOT IN (SELECT id FROM lookup_categories); TRUNCATE lookup_departments; INSERT INTO lookup_departments (name, latitude, longitude, is_active) SELECT DISTINCT department_id_name, latitude, longitude, 1 FROM rlcos; UPDATE lookup_departments s, rlcos t SET t.department_id=s.id WHERE s.name=t.department_id_name; # CHECK ALL DEPARTMENTS ASSIGNED SELECT DISTINCT department_id_name FROM rlcos WHERE department_id NOT IN (SELECT id FROM lookup_departments); TRUNCATE department_category; INSERT INTO department_category(category_id, department_id) SELECT DISTINCT category_id, department_id FROM rlcos; DROP TABLE IF EXISTS rlcos_document_data; CREATE TABLE rlcos_document_data ( rlcos_id int(11), name varchar(255), document_path varchar(255) ); LOAD DATA LOCAL INFILE 'docs/ rlcos-document-data.csv' INTO TABLE rlcos_document_data FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES; INSERT INTO rlcos_documents (rlcos_id, document_path) SELECT DISTINCT rlcos_id, document_path FROM rlcos_document_data; mysqldump -uroot --no-create-info click_api rlcos > database\sql\rlcos.sql mysqldump -uroot --no-create-info click_api rlcos_documents > database\sql\rlcos_documents.sql mysqldump -uroot --no-create-info click_api lookup_departments > database\sql\lookup_departments.sql mysqldump -uroot --no-create-info click_api department_category > database\sql\department_category.sql mysqldump -uroot --no-create-info click_api lookup_rlcos_fee_types > database\sql\lookup_rlcos_fee_types.sql mysqldump -uroot --no-create-info click_api rlcos_fees > database\sql\rlcos_fees.sql