ALTER TABLE `divisions` ADD `city` varchar(50) NULL;

CREATE TABLE `payroll` (
 `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
 `type` enum('payroll','budget') NOT NULL DEFAULT 'payroll',
 `month` enum('january','february','march','april','may','june','july','august','september','october','november','december') NOT NULL,
 `year` year(4) NOT NULL,
 `start` date DEFAULT NULL,
 `end` date DEFAULT NULL,
 `division_id` int(10) unsigned NOT NULL,
 `hr_status` enum('pending','approved','rejected') NOT NULL DEFAULT 'pending',
 `cfo_status` enum('pending','approved','rejected','amendments-required') NOT NULL DEFAULT 'pending',
 `hr_approver` bigint(20) unsigned DEFAULT NULL,
 `cfo_approver` bigint(20) unsigned DEFAULT NULL,
 `processed` enum('queued','in-progress','done') NOT NULL DEFAULT 'queued',
 `created_at` datetime NOT NULL,
 `updated_at` datetime NOT NULL,
 PRIMARY KEY (`id`),
 KEY `month` (`month`),
 KEY `year` (`year`),
 KEY `division_id` (`division_id`),
 KEY `hr_status` (`hr_status`),
 KEY `cfo_status` (`cfo_status`),
 KEY `created_at` (`created_at`),
 KEY `updated_at` (`updated_at`),
 KEY `hr_approver` (`hr_approver`),
 KEY `cfo_approver` (`cfo_approver`),
 KEY `processed` (`processed`),
 KEY `type` (`type`),
 KEY `start` (`start`),
 KEY `end` (`end`)
) ENGINE=InnoDB;

CREATE TABLE `payroll_attendance` (
 `payroll_id` bigint(20) unsigned NOT NULL,
 `employee_id` bigint(20) unsigned NOT NULL,
 `day` date NOT NULL,
 `flag` enum('p','a','pl','la','off','res','ter','h-d','uh-d','sus') NOT NULL,
 KEY `payroll_id` (`payroll_id`),
 KEY `employee_id` (`employee_id`),
 KEY `day` (`day`),
 KEY `flag` (`flag`)
) ENGINE=InnoDB;

CREATE TABLE `payroll_employee` (
 `payroll_id` bigint(20) unsigned NOT NULL,
 `employee_id` bigint(20) unsigned NOT NULL,
 `hrm_id` varchar(15) NOT NULL,
 `status` enum('active','terminated','released', 'resigned') NOT NULL DEFAULT 'active',
 `name` varchar(255) NOT NULL,
 `center` varchar(100) NOT NULL,
 `dob` text NOT NULL,
 `hire_date` date NOT NULL,
 `duty_type` int(11) unsigned NOT NULL DEFAULT '0',
 `position` varchar(255) NOT NULL,
 `department` varchar(255) NOT NULL,
 `project` varchar(255) NOT NULL,
 `leave_quota` DECIMAL(10,2) NOT NULL,
 `gross_salary` text NOT NULL,
 `basic_salary` text NOT NULL,
 `actual_salary` text DEFAULT NULL,
 `arrears_changed` bit(1) NOT NULL DEFAULT b'0',
 `advances_changed` bit(1) NOT NULL DEFAULT b'0',
 `ncns_changed` bit(1) NOT NULL DEFAULT b'0',
 `others_changed` bit(1) NOT NULL DEFAULT b'0',
 `incentives_changed` bit(1) NOT NULL DEFAULT b'0',
 `income_tax` text NOT NULL,
 `eobi` smallint(5) unsigned NOT NULL,
 `provident_fund` text NOT NULL,
 `account_title` text NOT NULL,
 `account_number` text NOT NULL,
 `branch_address` text NOT NULL,
 `branch_code` text NOT NULL,
 `cnic` text NOT NULL,
 `presents` decimal(4,2) unsigned NOT NULL DEFAULT '0.00',
 `ncns` decimal(4,2) unsigned NOT NULL DEFAULT '0.00',
 `suspensions` decimal(4,2) unsigned NOT NULL DEFAULT '0.00',
 `late_arrivals` tinyint(3) unsigned NOT NULL DEFAULT '0',
 `unpaid_leaves` decimal(4,2) unsigned NOT NULL DEFAULT '0.00',
 `paid_leaves` decimal(4,2) unsigned NOT NULL DEFAULT '0.00',
 `special_leaves` decimal(4,2) unsigned NOT NULL DEFAULT '0.00',
 `offs` decimal(4,2) unsigned NOT NULL DEFAULT '0.00',
 `half_days` decimal(4,2) unsigned NOT NULL DEFAULT '0.00',
 `highlighted` BIT(1) NOT NULL DEFAULT b'0',
 KEY `payroll_id` (`payroll_id`),
 KEY `employee_id` (`employee_id`),
 KEY `hrm_id` (`hrm_id`),
 KEY `status` (`status`),
 KEY `arrears_changed` (`arrears_changed`),
 KEY `advances_changed` (`advances_changed`),
 KEY `incentives_changed` (`incentives_changed`),
 KEY `others_changed` (`others_changed`),
 INDEX (`highlighted`)
) ENGINE=InnoDB;

CREATE TABLE `payroll_tax_slabs` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `min` decimal(20,2) unsigned NOT NULL,
 `max` decimal(20,2) unsigned NOT NULL,
 `fixed` decimal(20,2) unsigned NOT NULL DEFAULT '0.00',
 `rate` decimal(5,2) unsigned NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB;

CREATE TABLE `payroll_used_leaves` (
 `payroll_id` bigint(20) unsigned NOT NULL,
 `employee_id` bigint(20) unsigned NOT NULL,
 `year` YEAR NULL,
 `jan` decimal(4,2) unsigned NOT NULL DEFAULT '0.00',
 `feb` decimal(4,2) unsigned NOT NULL DEFAULT '0.00',
 `mar` decimal(4,2) unsigned NOT NULL DEFAULT '0.00',
 `apr` decimal(4,2) unsigned NOT NULL DEFAULT '0.00',
 `may` decimal(4,2) unsigned NOT NULL DEFAULT '0.00',
 `jun` decimal(4,2) unsigned NOT NULL DEFAULT '0.00',
 `jul` decimal(4,2) unsigned NOT NULL DEFAULT '0.00',
 `aug` decimal(4,2) unsigned NOT NULL DEFAULT '0.00',
 `sep` decimal(4,2) unsigned NOT NULL DEFAULT '0.00',
 `oct` decimal(4,2) unsigned NOT NULL DEFAULT '0.00',
 `nov` decimal(4,2) unsigned NOT NULL DEFAULT '0.00',
 `dec` decimal(4,2) unsigned NOT NULL DEFAULT '0.00',
 KEY `payroll_id` (`payroll_id`),
 KEY `employee_id` (`employee_id`)
) ENGINE=InnoDB;

CREATE TABLE `payroll_adjustments` (
 `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
 `payroll_id` bigint(20) unsigned NOT NULL,
 `employee_id` bigint(20) unsigned NOT NULL,
 `column` enum('arrears','advances','incentives','ncns','others') NOT NULL,
 `value` int(10) unsigned NOT NULL,
 `type` enum('system','user') NOT NULL,
 `reason` text NOT NULL,
 `user_id` bigint(20) unsigned NOT NULL,
 `user_name` varchar(100) NOT NULL,
 `updated_at` datetime NOT NULL,
 PRIMARY KEY (`id`),
 KEY `payroll_id` (`payroll_id`),
 KEY `employee_id` (`employee_id`),
 KEY `column` (`column`),
 KEY `type` (`type`),
 KEY `user_id` (`user_id`),
 KEY `user_name` (`user_name`),
 KEY `updated_at` (`updated_at`)
) ENGINE=InnoDB;

CREATE TABLE `payroll_payorders` (
 `payroll_id` bigint(20) unsigned NOT NULL,
 `employee_id` bigint(20) unsigned NOT NULL,
 `account_title` varchar(255) DEFAULT NULL,
 `comments` text,
 KEY `payroll_id` (`payroll_id`),
 KEY `employee_id` (`employee_id`)
) ENGINE=InnoDB;

CREATE TABLE `payroll_payslips` (
 `payroll_id` bigint(20) unsigned NOT NULL,
 `employee_id` bigint(20) unsigned NOT NULL,
 `outbox_id` bigint(20) unsigned DEFAULT NULL,
 `body` text DEFAULT NULL,
 `status` enum('pending', 'generated', 'queued', 'failed') NOT NULL DEFAULT 'pending',
 KEY `outbox_id` (`outbox_id`),
 KEY `payroll_id` (`payroll_id`),
 KEY `employee_id` (`employee_id`),
 KEY `status` (`status`)
) ENGINE=InnoDB;

CREATE TABLE `payroll_budget` (
 `payroll_id` bigint(20) unsigned NOT NULL,
 `employees` text DEFAULT NULL,
 `departments` text DEFAULT NULL,
 KEY `payroll_id` (`payroll_id`)
) ENGINE=InnoDB;

CREATE TABLE `payroll_comments` (
 `payroll_id` bigint(20) unsigned NOT NULL,
 `comments` text NOT NULL,
 `user_id` bigint(20) unsigned NOT NULL,
 `added_at` datetime NOT NULL,
 KEY `payroll_id` (`payroll_id`),
 KEY `user_id` (`user_id`),
 KEY `added_at` (`added_at`)
) ENGINE=InnoDB;

CREATE PROCEDURE `get_slab`(IN `v_min` DECIMAL(20,2) UNSIGNED, IN `v_max` DECIMAL(20,2) UNSIGNED, IN `v_id` INT(11) UNSIGNED) SQL SECURITY INVOKER SELECT * FROM `payroll_tax_slabs` WHERE (min BETWEEN v_min AND v_max OR max BETWEEN v_min AND v_max) AND id <> v_id;
CREATE PROCEDURE `get_slab_by_salary`(IN `salary` BIGINT UNSIGNED) SQL SECURITY INVOKER SELECT * FROM payroll_tax_slabs WHERE (salary * 12) BETWEEN min AND max;
CREATE PROCEDURE `get_holidays_in_range`(IN `start` DATE, IN `end` DATE) SQL SECURITY INVOKER SELECT payroll_holiday.holiday_name, payroll_holiday.start_date, payroll_holiday.end_date, GROUP_CONCAT(payroll_holiday_shift_wise.shift_id) as shift_id FROM payroll_holiday LEFT JOIN payroll_holiday_shift_wise ON payroll_holiday.payrl_holi_id = payroll_holiday_shift_wise.payrl_holi_id WHERE (payroll_holiday.start_date BETWEEN start and end) OR (payroll_holiday.end_date BETWEEN start and end) GROUP BY payroll_holiday.payrl_holi_id;
CREATE PROCEDURE `get_leave_in_range`(IN `e_id` BIGINT UNSIGNED, IN `start` DATE, IN `end` DATE) SQL SECURITY INVOKER SELECT LEAVES.* FROM ( SELECT leave_apply.*, leave_applied_days.day, IFNULL( leave_applied_days.type, 'full_day' ) AS TYPE FROM leave_apply LEFT JOIN leave_applied_days ON leave_applied_days.leave_appl_id = leave_apply.leave_appl_id AND leave_applied_days.active = 1 UNION SELECT leave_apply.*, leave_applied_days.day, IFNULL( leave_applied_days.type, 'full_day' ) AS TYPE FROM leave_apply RIGHT JOIN leave_applied_days ON leave_applied_days.leave_appl_id = leave_apply.leave_appl_id AND leave_applied_days.active = 1 ) AS LEAVES LEFT JOIN leave_type ON LEAVES.leave_type_id = leave_type.leave_type_id WHERE leave_type.leave_type NOT LIKE '%special%' AND employee_id = e_id AND ((leave_aprv_strt_date BETWEEN start AND end) OR (leave_aprv_end_date BETWEEN start AND end)) AND hr_status = 'approved';
CREATE PROCEDURE `get_special_leave_in_range`(IN `e_id` BIGINT UNSIGNED, IN `start` DATE, IN `end` DATE) SQL SECURITY INVOKER SELECT LEAVES.* FROM ( SELECT leave_apply.*, leave_applied_days.day, IFNULL( leave_applied_days.type, 'full_day' ) AS TYPE FROM leave_apply LEFT JOIN leave_applied_days ON leave_applied_days.leave_appl_id = leave_apply.leave_appl_id AND leave_applied_days.active = 1 UNION SELECT leave_apply.*, leave_applied_days.day, IFNULL( leave_applied_days.type, 'full_day' ) AS TYPE FROM leave_apply RIGHT JOIN leave_applied_days ON leave_applied_days.leave_appl_id = leave_apply.leave_appl_id AND leave_applied_days.active = 1 ) AS LEAVES LEFT JOIN leave_type ON LEAVES.leave_type_id = leave_type.leave_type_id WHERE leave_type.leave_type LIKE '%special%' AND employee_id = e_id AND ((leave_aprv_strt_date BETWEEN start AND end) OR (leave_aprv_end_date BETWEEN start AND end)) AND hr_status = 'approved';
CREATE PROCEDURE `get_payroll_active_employees`(IN `p_id` BIGINT UNSIGNED) SQL SECURITY INVOKER SELECT `payroll_employee`.*, `arrears`.`arrears`, `advances`.`advances`, `incentives`.`incentives`, `ncns`.`ncns` as `ncns_penalty`, `others`.`others` FROM `payroll_employee` LEFT JOIN( SELECT `employee_id`, SUM(`value`) AS `arrears` FROM `payroll_adjustments` WHERE `column` = 'arrears' AND `payroll_id` = p_id GROUP BY `employee_id` ) AS `arrears` ON `payroll_employee`.`employee_id` = `arrears`.`employee_id` LEFT JOIN( SELECT `employee_id`, SUM(`value`) AS `advances` FROM `payroll_adjustments` WHERE `column` = 'advances' AND `payroll_id` = p_id GROUP BY `employee_id` ) AS `advances` ON `payroll_employee`.`employee_id` = `advances`.`employee_id` LEFT JOIN( SELECT `employee_id`, SUM(`value`) AS `incentives` FROM `payroll_adjustments` WHERE `column` = 'incentives' AND `payroll_id` = p_id GROUP BY `employee_id` ) AS `incentives` ON `payroll_employee`.`employee_id` = `incentives`.`employee_id` LEFT JOIN( SELECT `employee_id`, SUM(`value`) AS `ncns` FROM `payroll_adjustments` WHERE `column` = 'ncns' AND `payroll_id` = p_id GROUP BY `employee_id` ) AS `ncns` ON `payroll_employee`.`employee_id` = `ncns`.`employee_id` LEFT JOIN( SELECT `employee_id`, SUM(`value`) AS `others` FROM `payroll_adjustments` WHERE `column` = 'others' AND `payroll_id` = p_id GROUP BY `employee_id` ) AS `others` ON `payroll_employee`.`employee_id` = `others`.`employee_id` WHERE `payroll_id` = p_id AND `payroll_employee`.`status` IN ('active') ORDER BY `payroll_employee`.`employee_id` ASC;
CREATE PROCEDURE `get_payroll_all_employees`(IN `p_id` BIGINT UNSIGNED) SQL SECURITY INVOKER SELECT `payroll_employee`.*, `arrears`.`arrears`, `advances`.`advances`, `incentives`.`incentives`, `ncns`.`ncns` as `ncns_penalty`, `others`.`others` FROM `payroll_employee` LEFT JOIN( SELECT `employee_id`, SUM(`value`) AS `arrears` FROM `payroll_adjustments` WHERE `column` = 'arrears' AND `payroll_id` = p_id GROUP BY `employee_id` ) AS `arrears` ON `payroll_employee`.`employee_id` = `arrears`.`employee_id` LEFT JOIN( SELECT `employee_id`, SUM(`value`) AS `advances` FROM `payroll_adjustments` WHERE `column` = 'advances' AND `payroll_id` = p_id GROUP BY `employee_id` ) AS `advances` ON `payroll_employee`.`employee_id` = `advances`.`employee_id` LEFT JOIN( SELECT `employee_id`, SUM(`value`) AS `incentives` FROM `payroll_adjustments` WHERE `column` = 'incentives' AND `payroll_id` = p_id GROUP BY `employee_id` ) AS `incentives` ON `payroll_employee`.`employee_id` = `incentives`.`employee_id` LEFT JOIN( SELECT `employee_id`, SUM(`value`) AS `ncns` FROM `payroll_adjustments` WHERE `column` = 'ncns' AND `payroll_id` = p_id GROUP BY `employee_id` ) AS `ncns` ON `payroll_employee`.`employee_id` = `ncns`.`employee_id` LEFT JOIN( SELECT `employee_id`, SUM(`value`) AS `others` FROM `payroll_adjustments` WHERE `column` = 'others' AND `payroll_id` = p_id GROUP BY `employee_id` ) AS `others` ON `payroll_employee`.`employee_id` = `others`.`employee_id` WHERE `payroll_id` = p_id ORDER BY `payroll_employee`.`employee_id` ASC;
CREATE PROCEDURE `get_payroll_inactive_employees`(IN `p_id` BIGINT UNSIGNED) SQL SECURITY INVOKER SELECT `payroll_employee`.*, `arrears`.`arrears`, `advances`.`advances`, `incentives`.`incentives`, `ncns`.`ncns` as `ncns_penalty`, `others`.`others` FROM `payroll_employee` LEFT JOIN( SELECT `employee_id`, SUM(`value`) AS `arrears` FROM `payroll_adjustments` WHERE `column` = 'arrears' AND `payroll_id` = p_id GROUP BY `employee_id` ) AS `arrears` ON `payroll_employee`.`employee_id` = `arrears`.`employee_id` LEFT JOIN( SELECT `employee_id`, SUM(`value`) AS `advances` FROM `payroll_adjustments` WHERE `column` = 'advances' AND `payroll_id` = p_id GROUP BY `employee_id` ) AS `advances` ON `payroll_employee`.`employee_id` = `advances`.`employee_id` LEFT JOIN( SELECT `employee_id`, SUM(`value`) AS `incentives` FROM `payroll_adjustments` WHERE `column` = 'incentives' AND `payroll_id` = p_id GROUP BY `employee_id` ) AS `incentives` ON `payroll_employee`.`employee_id` = `incentives`.`employee_id` LEFT JOIN( SELECT `employee_id`, SUM(`value`) AS `ncns` FROM `payroll_adjustments` WHERE `column` = 'ncns' AND `payroll_id` = p_id GROUP BY `employee_id` ) AS `ncns` ON `payroll_employee`.`employee_id` = `ncns`.`employee_id` LEFT JOIN( SELECT `employee_id`, SUM(`value`) AS `others` FROM `payroll_adjustments` WHERE `column` = 'others' AND `payroll_id` = p_id GROUP BY `employee_id` ) AS `others` ON `payroll_employee`.`employee_id` = `others`.`employee_id` WHERE `payroll_id` = p_id AND `payroll_employee`.`status` IN ('released', 'resigned', 'terminated') ORDER BY `payroll_employee`.`employee_id` ASC; 
CREATE PROCEDURE `get_payroll_employee`(IN `p_id` BIGINT UNSIGNED, IN `e_id` BIGINT UNSIGNED) SQL SECURITY INVOKER SELECT `payroll`.*, `payroll_employee`.*, `employee_history`.`dob`, ( SELECT SUM(`value`) FROM `payroll_adjustments` WHERE `column` = 'arrears' AND `payroll_id` = p_id AND `employee_id` = e_id GROUP BY `employee_id` ) AS `arrears`, ( SELECT SUM(`value`) FROM `payroll_adjustments` WHERE `column` = 'advances' AND `payroll_id` = p_id AND `employee_id` = e_id GROUP BY `employee_id` ) AS `advances`, ( SELECT SUM(`value`) FROM `payroll_adjustments` WHERE `column` = 'incentives' AND `payroll_id` = p_id AND `employee_id` = e_id GROUP BY `employee_id` ) AS `incentives`, ( SELECT SUM(`value`) FROM `payroll_adjustments` WHERE `column` = 'ncns' AND `payroll_id` = p_id AND `employee_id` = e_id GROUP BY `employee_id` ) AS `ncns_penalty`, ( SELECT SUM(`value`) FROM `payroll_adjustments` WHERE `column` = 'others' AND `payroll_id` = p_id AND `employee_id` = e_id GROUP BY `employee_id` ) AS `others` FROM `payroll` LEFT JOIN `payroll_employee` ON `payroll_employee`.`payroll_id` = `payroll`.`id` LEFT JOIN `employee_history` ON `payroll_employee`.`employee_id` = `employee_history`.`employee_id` WHERE `payroll`.`id` = p_id AND `payroll_employee`.`employee_id` = e_id;
CREATE PROCEDURE `get_payorders`(IN `p_id` BIGINT UNSIGNED) SQL SECURITY INVOKER SELECT `payroll_employee`.*, `payroll_payorders`.`account_title`, `payroll_payorders`.`comments`, `arrears`.`arrears`, `advances`.`advances`, `incentives`.`incentives`, `ncns`.`ncns` AS `ncns_penalty`, `others`.`others` FROM `payroll_payorders` LEFT JOIN `payroll_employee` ON `payroll_payorders`.`payroll_id` = `payroll_employee`.`payroll_id` AND `payroll_payorders`.`employee_id` = `payroll_employee`.`employee_id` LEFT JOIN( SELECT `employee_id`, SUM(`value`) AS `arrears` FROM `payroll_adjustments` WHERE `column` = 'arrears' AND `payroll_id` = p_id GROUP BY `employee_id` ) AS `arrears` ON `payroll_employee`.`employee_id` = `arrears`.`employee_id` LEFT JOIN( SELECT `employee_id`, SUM(`value`) AS `advances` FROM `payroll_adjustments` WHERE `column` = 'advances' AND `payroll_id` = p_id GROUP BY `employee_id` ) AS `advances` ON `payroll_employee`.`employee_id` = `advances`.`employee_id` LEFT JOIN( SELECT `employee_id`, SUM(`value`) AS `incentives` FROM `payroll_adjustments` WHERE `column` = 'incentives' AND `payroll_id` = p_id GROUP BY `employee_id` ) AS `incentives` ON `payroll_employee`.`employee_id` = `incentives`.`employee_id` LEFT JOIN( SELECT `employee_id`, SUM(`value`) AS `ncns` FROM `payroll_adjustments` WHERE `column` = 'ncns' AND `payroll_id` = p_id GROUP BY `employee_id` ) AS `ncns` ON `payroll_employee`.`employee_id` = `ncns`.`employee_id` LEFT JOIN( SELECT `employee_id`, SUM(`value`) AS `others` FROM `payroll_adjustments` WHERE `column` = 'others' AND `payroll_id` = p_id GROUP BY `employee_id` ) AS `others` ON `payroll_employee`.`employee_id` = `others`.`employee_id` WHERE `payroll_payorders`.`payroll_id` = p_id ORDER BY `payroll_employee`.`employee_id` ASC;

BEGIN;
UPDATE `divisions` SET `city` = 'Karachi' WHERE `id` = 3;
UPDATE `divisions` SET `city` = 'Islamabad' WHERE `id` = 4;
DELETE FROM `sec_menu_item` WHERE `module` = 'payroll';
INSERT INTO `language` (`phrase`, `english`) VALUES
('pending_payroll', 'Pending Payroll'),
('settings', 'Settings'),
('tax_slabs', 'Tax Slabs'),
('add_tax_slab', 'Add Tax Slab'),
('edit_tax_slab', 'Edit Tax Slab'),
('approved_payroll', 'Approved Payroll'),
('viewas_payroll', 'View As'),
('budget_payroll', 'Budget Payroll'),
('payorders_salaryslips', 'Payorder & Salary Slips'),
('salaryslips', 'Salary Slips'),
('payorders', 'Payorders'),
('slip_generator', 'Salary Slip Generator'),
('receive_cfo_notifications', 'Receive CFO Notifications');
INSERT INTO `sec_menu_item` (`menu_title`, `page_url`, `module`, `parent_menu`, `is_report`, `createby`, `createdate`, `active`) VALUES
('pending_payroll', 'payroll/pending', 'payroll', NULL, NULL, '1', '2021-11-12 17:38:18.000000', '1'),
('approved_payroll', 'payroll/pending', 'payroll', NULL, NULL, '1', '2021-11-12 17:38:18.000000', '1'),
('settings', '', 'payroll', NULL, NULL, '1', '2021-11-12 17:38:18.000000', '1'),
('tax_slabs', 'payroll/tax/slabs', 'payroll', NULL, NULL, '1', '2021-11-12 17:38:18.000000', '1'),
('viewas_payroll', 'payroll/view_as', 'payroll', NULL, NULL, '1', '2021-11-12 17:38:18.000000', '1'),
('budget_payroll', 'payroll/budget/list', 'payroll', NULL, NULL, '1', '2021-11-12 17:38:18.000000', '1'),
('payorders_salaryslips', '', 'payroll', NULL, NULL, '1', '2021-11-12 17:38:18.000000', '1'),
('salaryslips', '', 'payroll', NULL, NULL, '1', '2021-11-12 17:38:18.000000', '1'),
('payorders', '', 'payroll', NULL, NULL, '1', '2021-11-12 17:38:18.000000', '1'),
('slip_generator', '', 'payroll', NULL, NULL, '1', '2021-11-12 17:38:18.000000', '1'),
('receive_cfo_notifications', '', 'notification', '221', '0', '1', '2018-10-04 00:00:00', '1');
INSERT INTO `payroll_tax_slabs` (`id`, `min`, `max`, `fixed`, `rate`) VALUES
(1, '0.00', '600000.00', '0.00', '0.00'),
(2, '600001.00', '1200000.00', '0.00', '5.00'),
(3, '1200001.00', '1800000.00', '30000.00', '10.00'),
(4, '1800001.00', '2500000.00', '90000.00', '15.00'),
(5, '2500001.00', '3500000.00', '195000.00', '17.50'),
(6, '3500001.00', '5000000.00', '370000.00', '20.00'),
(7, '5000001.00', '8000000.00', '670000.00', '22.50'),
(8, '8000001.00', '12000000.00', '1345000.00', '25.00'),
(9, '12000001.00', '30000000.00', '2345000.00', '27.50'),
(10, '30000001.00', '50000000.00', '7295000.00', '30.00'),
(11, '50000001.00', '75000000.00', '13295000.00', '32.50'),
(12, '75000001.00', '999999999999999999.00', '21420000.00', '35.00');
COMMIT;