------------------------------ QUERY 1.1

INSERT INTO `people_300`.`cb_payroll_headers` (`header_desc`, `header_key`, `is_calculated`, `is_emf`, `status`, `created_at`, `updated_at`, `updated_by`, `created_by`) VALUES ('Invoice Amount', 'invoice_amount', '1', '0', '1', '2023-06-05 12:08:04', '2023-06-05 12:08:05', '1', '1');

------------------------------ QUERY 1.2

INSERT INTO cb_payroll_headers_link (payroll_headers_id, payroll_headers_type_id,STATUS)
VALUES (
(SELECT payroll_headers_id FROM cb_payroll_headers WHERE header_key LIKE 'invoice_amount'),
(SELECT payroll_headers_type_id FROM cb_payroll_headers_type WHERE header_type LIKE 'calculated_headers'),1
);

------------------------------ QUERY 1.3

INSERT INTO cb_headers_screen_filters (payroll_headers_id,payroll_setup_id,headers_type_id, status, is_editable) 
VALUES((SELECT payroll_headers_id FROM cb_payroll_headers WHERE header_key LIKE 'invoice_amount'),
(SELECT payroll_setup_id FROM cb_payroll_setup WHERE setup_type LIKE 'payroll' AND setup_desc LIKE 'payroll_detail'),
(SELECT payroll_headers_type_id FROM cb_payroll_headers_type WHERE header_type LIKE 'Calculated_Headers'),
1,0);

------------------------------ QUERY 2

ALTER TABLE `cb_payroll_process`
	ADD COLUMN `actual_gross_salary` FLOAT NULL DEFAULT NULL AFTER `increment`;


------------------------------ QUERY 3.1

INSERT INTO `people_300`.`cb_payroll_headers` (`header_desc`, `header_key`, `is_calculated`, `is_emf`, `status`, `created_at`, `updated_at`, `updated_by`, `created_by`) VALUES ('Actual Gross Salary', 'actual_gross_salary', '1', '0', '1', '2023-06-08 14:07:56', '2023-06-08 14:07:57', '1', '1');

------------------------------ QUERY 3.2

INSERT INTO cb_payroll_headers_link (payroll_headers_id, payroll_headers_type_id, STATUS)
VALUES(
(SELECT payroll_headers_id FROM cb_payroll_headers WHERE header_key LIKE 'actual_gross_salary'),
(SELECT pht.payroll_headers_type_id FROM cb_payroll_headers_type pht WHERE pht.header_type LIKE 'Calculated_Headers'),1
);

------------------------------ QUERY 3.3

INSERT INTO cb_headers_screen_filters (payroll_headers_id,payroll_setup_id,headers_type_id,specific_lable,is_editable,status)
VALUES (
(SELECT payroll_headers_id FROM cb_payroll_headers WHERE header_key LIKE 'actual_gross_salary'),
(SELECT payroll_setup_id FROM cb_payroll_setup  WHERE setup_type LIKE 'payroll' AND setup_desc LIKE 'payroll_detail'),
(SELECT payroll_headers_type_id FROM cb_payroll_headers_type WHERE header_type LIKE 'calculated_headers'),
'Gross Salary', 0,1
);

------------------------------ QUERY 4

UPDATE cb_headers_screen_filters hsf
INNER JOIN cb_payroll_headers ph ON hsf.payroll_headers_id=ph.payroll_headers_id
INNER JOIN cb_payroll_setup ps ON hsf.payroll_setup_id=ps.payroll_setup_id
INNER JOIN cb_payroll_headers_type pht ON hsf.headers_type_id=pht.payroll_headers_type_id
SET hsf.`status`=0
WHERE ps.setup_type LIKE 'payroll' AND ps.setup_desc LIKE 'payroll_detail' 
AND ph.header_key LIKE 'gross_salary' AND pht.header_type LIKE 'calculated_headers';

------------------------------ QUERY 5

UPDATE cb_headers_screen_filters hsf
INNER JOIN cb_payroll_headers_type pht ON hsf.headers_type_id=pht.payroll_headers_type_id
SET hsf.is_editable=0 WHERE pht.header_type IN ('MIS_Headers', 'Calculated_Headers');

------------------------------ QUERY 6

UPDATE cb_headers_screen_filters hsf
INNER JOIN cb_payroll_headers_type pht ON hsf.headers_type_id=pht.payroll_headers_type_id
SET hsf.is_editable=1 WHERE pht.header_type NOT IN ('MIS_Headers', 'Calculated_Headers');

------------------------------ QUERY 7

ALTER TABLE `cb_payroll_process`
	CHANGE COLUMN `taxable_netpay` `taxable_netpay` FLOAT NULL DEFAULT NULL AFTER `ghi_contribution_employee`,
	CHANGE COLUMN `non_taxable_netpay` `non_taxable_netpay` FLOAT NULL DEFAULT NULL AFTER `taxable_netpay`,
	CHANGE COLUMN `payroll_cost` `payroll_cost` FLOAT NULL DEFAULT NULL AFTER `non_taxable_netpay`,
	CHANGE COLUMN `invoice_amount` `invoice_amount` FLOAT NULL DEFAULT NULL AFTER `payroll_cost`,
	CHANGE COLUMN `advance_salary_loan_deduction_employee` `advance_salary_loan_deduction_employee` FLOAT NULL DEFAULT NULL AFTER `invoice_amount`,
	CHANGE COLUMN `ghi_contritbution` `ghi_contritbution` FLOAT NULL DEFAULT NULL AFTER `lwop_new_joiner`;

------------------------------ QUERY 8

UPDATE approval_role_config arc
INNER JOIN approval_config ac ON arc.approval_config_id=ac.id
INNER JOIN modules m ON ac.module_id=m.id
INNER JOIN roles r ON ac.role_id=r.id
SET arc.empid= (SELECT id FROM proll_employee WHERE loginname LIKE 'irfan.bhatti@people.net')
WHERE m.name LIKE 'payroll' AND r.role LIKE 'hr';

------------------------------ QUERY 9

ALTER TABLE `cb_payroll_payments_transactions`
	CHANGE COLUMN `transaction_date` `transaction_date` DATE NULL AFTER `id`,
	CHANGE COLUMN `creation_date` `creation_date` DATE NULL AFTER `transaction_date`;

------------------------------ QUERY 10

ALTER TABLE `cb_payroll_emf_change`
	ADD COLUMN `increment_amount` FLOAT NULL DEFAULT NULL AFTER `band`;
	
	
------------------------------ QUERY 11

UPDATE cb_payroll_headers_link phl
INNER JOIN cb_payroll_headers ph ON phl.payroll_headers_id=ph.payroll_headers_id
INNER JOIN cb_payroll_headers_type pht ON phl.payroll_headers_type_id=pht.payroll_headers_type_id
SET phl.`status`=0
WHERE pht.header_type LIKE 'Employee_Contribitions' AND ph.header_key IN ('eobi_deduction','eobi_arrears_deduction','ghi_deduction');

