ALTER TABLE `proll_employee_nomines`
	CHANGE COLUMN `date_of_birth` `date_of_birth` DATE NULL;
ALTER TABLE `bank_accounts`
	CHANGE COLUMN `transaction_type_id` `transaction_type_id` INT(11) NULL;
ALTER TABLE `bank_accounts`
	CHANGE COLUMN `account_type_id` `account_type_id` INT(11) NULL;
ALTER TABLE `bank_accounts`
	CHANGE COLUMN `status` `status` TINYINT(1) NULL;
ALTER TABLE `proll_employee_nomines`
	CHANGE COLUMN `next_of_kins` `next_of_kins` TINYINT(1) NULL DEFAULT 1;
ALTER TABLE `proll_employee_dependents`
	CHANGE COLUMN `next_of_kins` `next_of_kins` INT(11) NULL DEFAULT NULL;
ALTER TABLE `bank_accounts`
	CHANGE COLUMN `currency_id` `currency_id` INT(11) NULL;
ALTER TABLE `proll_employee_documents`
	CHANGE COLUMN `doc_title` `doc_title` VARCHAR(100) NULL;

ALTER TABLE `proll_employee_dependents`
	CHANGE COLUMN `dependent_name` `dependent_name` VARCHAR(120) NULL,
	CHANGE COLUMN `gender` `gender` ENUM('Male','Female') NULL ,
	CHANGE COLUMN `date_of_birth` `date_of_birth` DATE NULL,
	CHANGE COLUMN `relationship` `relationship` VARCHAR(120) NULL,
	CHANGE COLUMN `cninc_number` `cninc_number` VARCHAR(100) NULL,
	CHANGE COLUMN `education` `education` VARCHAR(100) NULL,
	CHANGE COLUMN `occupation` `occupation` VARCHAR(100) NULL;

ALTER TABLE `proll_employee_nomines`
	CHANGE COLUMN `gender` `gender` ENUM('Male','Female') NULL;

ALTER TABLE `proll_employee_education`
	CHANGE COLUMN `name` `name` VARCHAR(50) NULL ,
	CHANGE COLUMN `discipline_name` `discipline_name` VARCHAR(50) NULL,
	CHANGE COLUMN `institute_name` `institute_name` VARCHAR(100) NULL,
	CHANGE COLUMN `passing_year` `passing_year` INT(7) NULL,
	CHANGE COLUMN `grade` `grade` VARCHAR(12) NULL,
	CHANGE COLUMN `attachment_name` `attachment_name` VARCHAR(30) NULL,
	CHANGE COLUMN `created_by` `created_by` INT(11) NULL,
	CHANGE COLUMN `updated_by` `updated_by` INT(11) NULL,
	CHANGE COLUMN `created_date` `created_date` DATE NULL,
	CHANGE COLUMN `updated_date` `updated_date` DATE NULL;


ALTER TABLE `bank_accounts`
	CHANGE COLUMN `bank_branch_id` `bank_branch_id` INT(11) NULL,
	CHANGE COLUMN `account_title` `account_title` VARCHAR(100) NULL,
	CHANGE COLUMN `account_number` `account_number` VARCHAR(20) NULL,
	CHANGE COLUMN `created_by` `created_by` INT(11) NULL;

ALTER TABLE `proll_employee_visa_details`
	CHANGE COLUMN `visa_type` `visa_type` ENUM('visit','business','worker','citizenship') NULL COLLATE 'latin1_swedish_ci' AFTER `emp_id`,
	CHANGE COLUMN `allowed_entry` `allowed_entry` ENUM('single','multiple') NULL COLLATE 'latin1_swedish_ci' AFTER `visa_type`,
	CHANGE COLUMN `allowed_work_hours` `allowed_work_hours` FLOAT NULL AFTER `allowed_entry`,
	CHANGE COLUMN `visa_issued_by_country_id` `visa_issued_by_country_id` INT(11) NULL AFTER `allowed_work_hours`,
	CHANGE COLUMN `visa_issued_date` `visa_issued_date` DATE NULL AFTER `visa_issued_by_country_id`,
	CHANGE COLUMN `visa_expiry_date` `visa_expiry_date` DATE NULL AFTER `visa_issued_date`,
	CHANGE COLUMN `employement_status` `employement_status` ENUM('contractual','permanent') NULL COLLATE 'latin1_swedish_ci' AFTER `visa_expiry_date`,
	CHANGE COLUMN `employment_country_id` `employment_country_id` INT(11) NULL AFTER `employement_status`,
	CHANGE COLUMN `employment_expiry_date` `employment_expiry_date` DATE NULL AFTER `employment_country_id`,
	CHANGE COLUMN `resident_country_id` `resident_country_id` INT(11) NULL AFTER `employment_expiry_date`,
	CHANGE COLUMN `resident_country_expiry_date` `resident_country_expiry_date` DATE NULL AFTER `resident_country_id`,
	CHANGE COLUMN `resident_document_name` `resident_document_name` VARCHAR(70) NULL COLLATE 'latin1_swedish_ci' AFTER `resident_country_expiry_date`,
	CHANGE COLUMN `resident_country_labour_no` `resident_country_labour_no` VARCHAR(150) NULL COLLATE 'latin1_swedish_ci' AFTER `resident_document_name`,
	CHANGE COLUMN `resident_country_labour_expiry_date` `resident_country_labour_expiry_date` DATE NULL AFTER `resident_country_labour_no`,
	CHANGE COLUMN `resident_country_labour_doucment_name` `resident_country_labour_doucment_name` VARCHAR(70) NULL COLLATE 'latin1_swedish_ci' AFTER `resident_country_labour_expiry_date`,
	CHANGE COLUMN `other_ids` `other_ids` VARCHAR(30) NULL;
	CHANGE COLUMN `other_expiry_date` `other_expiry_date` VARCHAR(30) NULL DEFAULT NULL;
	
ALTER TABLE `proll_employee_detail`
	CHANGE COLUMN `PPRRating` `PPRRating` VARCHAR(50) NULL DEFAULT NULL;

ALTER TABLE `proll_employee_visa_details`
	CHANGE COLUMN `employement_status` `employement_status` INT NULL DEFAULT NULL;

ALTER TABLE `proll_employee_detail`
	CHANGE COLUMN `lastworkingdate` `lastworkingdate` DATE NULL DEFAULT NULL;

ALTER TABLE `proll_employee_visa_details`
	CHANGE COLUMN `other_doucment_name` `other_doucment_name` VARCHAR(70) NULL;

## Colums Added
ALTER TABLE `proll_people_work_experience`
	ADD COLUMN `is_relative_worked` TINYINT NULL DEFAULT 0;

ALTER TABLE `proll_work_experience_with_our_client`
	ADD COLUMN `is_worked_with_client` TINYINT NULL DEFAULT 0;




UPDATE `dev_integration_merged_2022_05_18`.`proll_reference_data` SET `ref_id`='' WHERE  `description`= 'All';
UPDATE `dev_integration_merged_2022_05_18`.`proll_employee_documents` SET `country_id`='154' WHERE  `country_id`= (select country_id from countries where country = 'All');
DELETE FROM `dev_integration_merged_2022_05_18`.`countries` WHERE  country = 'All';


-- Delete unecessary columns

ALTER TABLE proll_employee 
DROP hom_address, 
DROP cast, 
DROP location, 
DROP hom_phone,
DROP fs_report, 
DROP reason, 
DROP imp_id, 
DROP fs_status, 
DROP cnic_image, 
DROP alert_sent, 
DROP added, 
DROP passport_alert_sent, 
DROP loggedin, 
DROP bg_img, 
DROP bg_color, 
DROP contract_expiry, 
DROP reporting_to_empcode, 
DROP view_status, 
DROP alm_view_status, 
DROP lm_view_status, 
DROP hr_view_status, 
DROP portal_id, 
DROP user_id, 
DROP employee_weight, 
DROP employee_height, 
DROP nomination_agree_check, 
DROP ex_relevent_experience_flag, 
DROP experience_with_client_flag, 
DROP employment_file, 
DROP allow_policy_manual, 
DROP remember_token, 
DROP main_lm, 
DROP in_active_type, 
DROP leave_bank_updated, 
DROP leave_bank_updated_at,
DROP visa_image, 
DROP visa_expiry, 
DROP visa_alert_sent, 
DROP ntn_number, 
DROP ntn_country_id, 
DROP ntn_status, 
DROP passport_no, 
DROP passport_country_id, 
DROP passport_image, 
DROP passport_issued_on, 
DROP passport_expiry, 
DROP cnic_country_id, 
DROP cnic_issued_on, 
DROP cnic_expiry, 
DROP cnic_alert_sent, 
DROP driving_license_number, 
DROP driving_license_image, 
DROP driving_license_country_id, 
DROP driving_license_issued_on, 
DROP driving_license_expiry; 

-- proll_employee_detail table
ALTER TABLE proll_employee_detail 
DROP sap_id,
DROP employeementRegionCurrent,
DROP gbsStatus,
DROP employeementDatCurrent,
DROP qualification,
DROP personalEmail,
DROP visaStatus,
DROP lastAnnBonusPayout,
DROP lastAnnBonusPayout,
DROP bonusCategory,
DROP previous_appraisal_rating,
DROP emgContactPerson,
DROP emgContactNo,
DROP homeCountryAddress,
DROP homeCountryContactNo,
DROP homeCountryContactNo,
DROP bloodGroup,
DROP gender,
DROP bank_name,
DROP bank_account,
DROP branch,
DROP last_market_adj_date,
DROP last_market_adj_date,
DROP dateoflastincrement,
DROP managername,
DROP manageremail,
DROP departmentname;


-- proll_employee_info table
ALTER TABLE proll_employee_info
DROP sub_department_level1_id,
DROP sub_department_level2_id,
DROP weekend_day_id,
DROP father_dob,
DROP hrms_id,
DROP unique_position_id,
DROP date_of_rejection,
DROP hr_email_id,
DROP hom_address,
DROP hom_address,
DROP postalcode,
DROP country,
DROP cast,
DROP city,
DROP location,
DROP hom_phone,
DROP fs_report,
DROP reason,
DROP imp_id,
DROP fs_status,
DROP visa_image,
DROP visa_expiry,
DROP degree_image,
DROP added,
DROP alert_sent,
DROP passport_alert_sent,
DROP visa_alert_sent,
DROP cnic_alert_sent,
DROP loggedin,
DROP bg_img,
DROP bg_color,
DROP contract_expiry,
DROP view_status,
DROP alm_view_status,
DROP lm_view_status,
DROP hr_view_status,
DROP user_id,
DROP portal_id,
DROP employee_height,
DROP employee_weight,
DROP ntn_number,
DROP ntn_country_id,
DROP ntn_status,
DROP nomination_agree_check,
DROP ex_relevent_experience_flag,
DROP employment_file,
DROP allow_policy_manual,
DROP remember_token,
DROP main_lm,
DROP contract_start_date,
DROP contract_end_date,
DROP in_active_type,
DROP leave_bank_updated,
DROP leave_bank_updated_at,
DROP is_mobile_attendance,
DROP remote_attendance_allowed;

-- proll_employee_dependents table
ALTER TABLE proll_employee_dependents
DROP date_of_marriage;

-- proll_employee_contracts table
ALTER TABLE proll_employee_contracts
DROP extension;

-- ALTER TABLE proll_employee_documents
-- ALTER TABLE proll_employee_nomines
-- ALTER TABLE proll_employee_reference
-- ALTER TABLE bank_accounts
-- ALTER TABLE proll_employee_education
-- ALTER TABLE proll_employee_skills
-- ALTER TABLE proll_employee_language
-- ALTER TABLE proll_employee_job_history
-- ALTER TABLE proll_people_work_experience
-- ALTER TABLE proll_work_experience_with_our_client
-- ALTER TABLE proll_employee_salary_summary
-- ALTER TABLE proll_employee_salary_summary_detail
-- ALTER TABLE proll_employee_salary_info_detail
-- ALTER TABLE proll_employee_visa_details
-- ALTER TABLE proll_employee_contact 


SELECT name_salute,
       NAME,
       f_hname,
       picture,
       micro_picture,
       pass_word,
       e_pass_word,
       status,
       applyed_roster,
       cnic,
       city,
       created_at,
       updated_at,
       dob,
       contract_status,
       job_status,
       cid,
	   cnic,
	   empcode,
	   employeeno,
	   doj,
	   picture,
	   micro_picture,
	   loginname,
	   emp_band,
	   contract_status,
	   cell_number,
	   country,
	   department_id,
	   designation,
	   dept_id,
	   dept_doj,
	   loc_id,
	   applyed_roster,
	   default_shift_id,
	   is_mobile_attendance,
	   remote_attendance_allowed,
	   reporting_country,
	   reporting_to_id,
	   second_reporting_to_country_id,
	   second_reporting_to_id
FROM   proll_employee;

SELECT empid,
       empcode,
       employeeno,
       lastpromotiondate,
       lastworkingdate
FROM   proll_employee_detail;

SELECT emp_id,
       birth_state_id,
       domicile_state_id,
       father_occupation,
       dob,
       gender,
       religion_id,
       native_language_id,
       birth_country_id,
       birth_city_id,
       domicile_country_id,
       domicile_city_id,
       second_nationality_id,
       blood_group_id,
       m_status,
       no_of_dependants,
	   emp_band,
	   contract_status,
	   cell_number,
	   reasonforleaving
FROM   proll_employee_info; 

SELECT doc_title,
       doc_type_id,
       doc_path,
       doc_number,
       doc_issue_date,
       doc_expiry_date,
       doc_issuance_authority,
       emp_id,
       country_id
FROM   proll_employee_documents; 
 
 
 SELECT employee_id,
       dependent_name,
       gender,
       date_of_birth,
       relationship,
       cninc_number,
       next_of_kins
FROM   proll_employee_dependents;

SELECT employee_id,
       NAME,
       gender,
       date_of_birth,
       cnic,
       relationship,
       next_of_kins,
       created_by,
       updated_by,
       created_date,
       updated_date
FROM   proll_employee_nomines; 

SELECT emp_id,
       address_type,
       email,
       mobile_number,
       skype_name,
       emergency_contact_person_name,
       emergency_contact_number,
       country_id,
       state_province_id,
       city_id,
       address,
       telephone_no,
       zip_code
FROM   proll_employee_contact;

SELECT employee_id,
       education_type,
       NAME,
       discipline_name,
       subject,
       institute_name,
       passing_year,
       grade,
       attachment_name,
       created_by,
       updated_by,
       created_date,
       updated_date,
       institute_country_id,
       institute_province_id,
       institute_city_id
FROM   proll_employee_education;

SELECT employee_id,
       skill_id,
       skill_level,
       created_by,
       updated_by,
       created_time,
       updated_date
FROM   proll_employee_skills;

SELECT emp_id,
       language_id,
       reading_level,
       writing_level,
       speaking_leve
FROM   proll_employee_language;

SELECT empid,
       organization,
       designation,
       salary,
       job_from,
       job_to,
       country_id,
       state_province_id,
       city_id,
       employer_address,
       experience_attached,
       employer_contact_number,
       reason_for_leave
FROM   proll_employee_job_history;

SELECT empid,
       reference_person_name,
       organization,
       designation,
       relation,
       address,
       contact_no,
       known_since,
       email,
       remarks
FROM   proll_employee_reference;

SELECT bank_id,
       bank_branch_id,
       account_title,
       account_number,
       routing_code,
       iban,
       branch_code,
       swift_code,
       city_id,
       state_province_id,
       country_id,
       client_id,
       employee_id,
       transaction_type_id,
       currency_id,
       account_type_id,
       status,
       created_by,
       updated_by,
       created_date,
       updated_date
FROM   bank_accounts;

SELECT empid,
       empcode,
       employeeno,
       lastpromotiondate,
       lastworkingdat
FROM   proll_employee_detail;

SELECT employee_id,
       contract_start_date,
       contract_end_date,
       contract_value,
       payment_frequency,
       reference_no,
       working_days,
       extension_type,
       created_by,
       updated_by,
       created_at,
       updated_at
FROM   proll_employee_contracts;

SELECT emp_id,
       basic_salary,
       base_salary,
       housing_allow,
       car_allow,
       mobile_allow,
       special_allow,
       transportation_allow,
       food_allow,
       gross_salary,
       annual_gross_salary,
       source,
       salary_view,
       created_at,
       updated_at
FROM   proll_employee_salary_summary;

SELECT employee_salary_summary_id,
       last_increment_date,
       statutory_bonus,
       annual_bonus_category,
       last_annual_bonus,
       social_security_applicable,
       eobi_applicable,
       created_by,
       updated_by,
       created_at,
       updated_at
FROM   proll_employee_salary_summary_detail;

SELECT empid,
       empcode,
       employeeno,
       lastpromotiondate,
       lastworkingdat
FROM   proll_employee_salary_info_detail; 