-- https://www.phpmyadmin.net/
--

START TRANSACTION;



--
-- Database: `payroll_finaltest`
--

-- --------------------------------------------------------

--
-- Table structure for table `attendance_rules`
--

CREATE TABLE `attendance_rules` (
  `atte_rule_id` int(11) NOT NULL,
  `in_time` varchar(11) NOT NULL,
  `out_time` varchar(11) NOT NULL,
  `allowed_in_time` varchar(11) NOT NULL,
  `allowed_out_time` varchar(11) NOT NULL,
  `latemark_allowed_time` varchar(11) NOT NULL,
  `latemark_allowed_in_month` int(11) NOT NULL,
  `pl_allowed_per_month` int(11) NOT NULL,
  `cl_allowed_per_month` int(11) NOT NULL,
  `lop_allowed_per_month` int(11) NOT NULL,
  `emp_ids` varchar(250) NOT NULL,
  `dept_id` int(11) NOT NULL,
  `is_active` int(11) NOT NULL DEFAULT '1',
  `added_by` int(11) NOT NULL,
  `added_date` datetime NOT NULL,
  `modified_by` int(11) DEFAULT NULL,
  `modified_date` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `insertion_type` varchar(15) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- --------------------------------------------------------

--
-- Table structure for table `company`
--

CREATE TABLE `company` (
  `cmp_id` int(11) NOT NULL,
  `cmp_name` varchar(250) NOT NULL,
  `cmp_address` varchar(250) NOT NULL,
  `country` varchar(250) NOT NULL,
  `state` varchar(250) NOT NULL,
  `city` varchar(250) NOT NULL,
  `is_active` int(1) NOT NULL,
  `pincode` int(10) NOT NULL,
  `created_by` int(11) NOT NULL,
  `created_on` datetime NOT NULL,
  `updated_by` int(11) NOT NULL,
  `updated_on` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- --------------------------------------------------------

--
-- Table structure for table `company_branches`
--

CREATE TABLE `company_branches` (
  `branch_id` int(11) NOT NULL,
  `branch_name` varchar(50) NOT NULL,
  `is_active` int(1) NOT NULL DEFAULT '0',
  `created_by` int(11) NOT NULL,
  `created_on` datetime NOT NULL,
  `updated_by` int(11) NOT NULL,
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- --------------------------------------------------------

--
-- Table structure for table `contact_us`
--

CREATE TABLE `contact_us` (
  `contact_us_id` int(11) NOT NULL,
  `c_name` varchar(250) NOT NULL,
  `c_mail` varchar(250) NOT NULL,
  `c_ph` int(15) NOT NULL,
  `msg` text NOT NULL,
  `created_by` int(11) NOT NULL,
  `created_on` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- --------------------------------------------------------

--
-- Table structure for table `department`
--

CREATE TABLE `department` (
  `dept_id` int(11) NOT NULL,
  `branch_id` int(11) NOT NULL,
  `dept_name` varchar(250) NOT NULL,
  `is_active` int(1) NOT NULL,
  `created_by` int(11) NOT NULL,
  `created_on` datetime NOT NULL,
  `updated_by` int(11) NOT NULL,
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- --------------------------------------------------------

--
-- Table structure for table `email_logs`
--

CREATE TABLE `email_logs` (
  `id` int(11) NOT NULL,
  `to` varchar(200) DEFAULT NULL,
  `cc` varchar(200) DEFAULT NULL,
  `bcc` varchar(200) DEFAULT NULL,
  `subject` varchar(200) DEFAULT NULL,
  `attachment` text,
  `msg` longtext,
  `sent_on` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `status` int(1) DEFAULT '1',
  `error_log` text NOT NULL,
  `sent_by` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- --------------------------------------------------------

--
-- Table structure for table `employee_bank_master`
--

CREATE TABLE `employee_bank_master` (
  `id` int(11) NOT NULL,
  `emp_id` int(11) NOT NULL,
  `emp_code` varchar(250) NOT NULL,
  `acc_hol_name` varchar(400) NOT NULL,
  `bank_name` varchar(400) NOT NULL,
  `branch_name` varchar(400) NOT NULL,
  `acc_no` varchar(50) NOT NULL,
  `ifsc_code` varchar(50) NOT NULL,
  `is_active` int(1) NOT NULL,
  `created_by` int(11) NOT NULL,
  `created_on` datetime NOT NULL,
  `updated_by` int(11) NOT NULL,
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- --------------------------------------------------------

--
-- Table structure for table `emp_sal_breakup`
--

CREATE TABLE `emp_sal_breakup` (
  `id` int(11) NOT NULL,
  `term_name` varchar(50) NOT NULL,
  `is_active` int(1) NOT NULL,
  `ded_type` varchar(1) NOT NULL,
  `is_usable` int(1) NOT NULL,
  `created_by` int(11) NOT NULL,
  `created_on` datetime NOT NULL,
  `updated_by` int(11) NOT NULL,
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `emp_sal_breakup`
--

INSERT INTO `emp_sal_breakup` (`id`, `term_name`, `is_active`, `ded_type`, `is_usable`, `created_by`, `created_on`, `updated_by`, `updated_on`) VALUES
(1, 'Employee Code', 1, 'U', 1, 3, '2023-10-03 11:15:52', 3, '2023-10-04 21:08:35'),
(2, 'Employee Name', 1, 'U', 1, 3, '2023-10-03 11:16:12', 0, '2023-10-03 05:46:12'),
(3, 'Days Worked', 1, 'U', 1, 3, '2023-10-03 11:16:30', 0, '2023-10-03 05:46:30'),
(4, 'Week Off', 1, 'U', 1, 3, '2023-10-03 11:16:46', 0, '2023-10-03 05:46:46'),
(5, 'Full Day Leaves', 1, 'U', 1, 3, '2023-10-03 11:17:28', 0, '2023-10-03 05:47:28'),
(6, 'Half Day Leaves', 1, 'U', 1, 3, '2023-10-03 11:17:46', 0, '2023-10-03 05:47:46'),
(7, 'Paid Leaves', 1, 'U', 1, 3, '2023-10-03 11:18:07', 0, '2023-10-03 05:48:07'),
(8, 'Parking Charges', 1, 'D', 1, 3, '2023-10-03 11:20:17', 0, '2023-10-03 05:50:17'),
(9, 'Arrears', 1, 'N', 1, 3, '2023-10-03 11:20:37', 0, '2023-10-03 05:50:37'),
(10, 'Bonus Ex-Gratia', 1, 'N', 1, 3, '2023-10-03 11:20:56', 0, '2023-10-03 05:50:56'),
(11, 'Advance', 1, 'D', 1, 3, '2023-10-05 08:34:57', 0, '2023-10-05 03:04:57'),
(12, 'Increment', 1, 'N', 1, 3, '2023-10-05 08:35:23', 0, '2023-10-05 03:05:23');

-- --------------------------------------------------------

--
-- Table structure for table `itr_master`
--

CREATE TABLE `itr_master` (
  `itr_id` int(11) NOT NULL,
  `gross_amt_from` decimal(10,2) NOT NULL,
  `gross_amt_to` decimal(10,2) NOT NULL,
  `percentage` decimal(10,2) NOT NULL,
  `is_active` int(1) NOT NULL,
  `created_by` int(11) NOT NULL,
  `created_on` datetime NOT NULL,
  `updated_by` int(11) NOT NULL,
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- --------------------------------------------------------

--
-- Table structure for table `monthly_salary_details`
--

CREATE TABLE `monthly_salary_details` (
  `id` int(11) NOT NULL,
  `emp_code` varchar(255) NOT NULL,
  `salary_month` varchar(100) NOT NULL,
  `term_name` varchar(255) NOT NULL,
  `term_value` varchar(255) NOT NULL,
  `created_by` int(11) NOT NULL,
  `created_on` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  `updated_by` int(11) NOT NULL,
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- --------------------------------------------------------

--
-- Table structure for table `monthly_sal_data`
--

CREATE TABLE `monthly_sal_data` (
  `sal_id` int(11) NOT NULL,
  `sal_month` varchar(50) NOT NULL,
  `emp_code` varchar(250) NOT NULL,
  `paid_days` int(11) NOT NULL,
  `month_days` int(11) NOT NULL,
  `breakup_label` varchar(30) NOT NULL,
  `sal_breakup_id` int(11) NOT NULL,
  `monthly_gained_amts` varchar(250) NOT NULL,
  `created_by` int(11) NOT NULL,
  `created_on` datetime NOT NULL,
  `updated_by` int(11) NOT NULL,
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- --------------------------------------------------------

--
-- Table structure for table `reports_permissoion`
--

CREATE TABLE `reports_permissoion` (
  `id` int(11) NOT NULL,
  `org_id` int(11) NOT NULL,
  `main_tbl_id` int(11) NOT NULL,
  `main_tbl_nm` varchar(50) NOT NULL,
  `main_tbl_col` text NOT NULL,
  `join_t_id` int(11) NOT NULL,
  `join_t_nm` varchar(50) NOT NULL,
  `join_t_col` text NOT NULL,
  `main_t_clause` varchar(50) NOT NULL,
  `join_t_clause` varchar(50) NOT NULL,
  `query` text NOT NULL,
  `main_t_cond_col` varchar(50) NOT NULL,
  `clause` varchar(20) NOT NULL,
  `main_condition` varchar(50) NOT NULL,
  `is_active` int(1) NOT NULL DEFAULT '1',
  `created_by` int(11) NOT NULL,
  `created_on` datetime NOT NULL,
  `updated_by` int(11) NOT NULL,
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- --------------------------------------------------------

--
-- Table structure for table `salary_breakup`
--

CREATE TABLE `salary_breakup` (
  `term_id` int(11) NOT NULL,
  `term_name` varchar(250) NOT NULL,
  `term_val` decimal(10,2) NOT NULL,
  `fin_yr` varchar(10) NOT NULL,
  `is_active` int(11) NOT NULL,
  `is_deducted` int(1) NOT NULL,
  `is_usable` int(1) NOT NULL,
  `component_type` varchar(1) NOT NULL,
  `formulas` varchar(250) NOT NULL,
  `is_visible` int(1) NOT NULL,
  `created_by` int(11) NOT NULL,
  `created_on` datetime NOT NULL,
  `updated_by` int(11) NOT NULL,
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `salary_breakup`
--

INSERT INTO `salary_breakup` (`term_id`, `term_name`, `term_val`, `fin_yr`, `is_active`, `is_deducted`, `is_usable`, `component_type`, `formulas`, `is_visible`, `created_by`, `created_on`, `updated_by`, `updated_on`) VALUES
(1, 'Basic', 50.00, '2023-2024', 1, 0, 1, 'F', '', 1, 1, '2023-10-17 08:10:36', 0, '2023-10-17 02:40:36'),
(2, 'DA', 10.00, '2023-2024', 1, 0, 1, 'F', '', 1, 1, '2023-10-17 08:11:14', 0, '2023-10-17 02:41:14'),
(3, 'HRA', 30.00, '2023-2024', 1, 0, 1, 'F', '', 1, 1, '2023-10-17 08:12:56', 0, '2023-10-17 02:42:56'),
(4, 'Conveyance', 5.00, '2023-2024', 1, 0, 1, 'F', '', 1, 1, '2023-10-17 08:13:17', 0, '2023-10-17 02:43:17'),
(5, 'Medical Allowance', 5.00, '2023-2024', 1, 0, 1, 'F', '', 1, 1, '2023-10-17 08:13:39', 0, '2023-10-17 02:43:39'),
(6, 'Travel Reimbersement', 0.00, '2023-2024', 0, 0, 0, 'F', '', 0, 1, '2023-10-17 08:14:07', 0, '2023-10-17 02:44:07'),
(7, 'Employer PF', 13.00, '2023-2024', 1, 1, 1, 'C', '', 0, 1, '2023-10-17 08:14:52', 1, '2023-10-16 22:03:50'),
(8, 'Employer ESIC', 3.25, '2023-2024', 1, 1, 1, 'C', '', 0, 1, '2023-10-17 08:15:25', 1, '2023-10-16 22:04:00'),
(9, 'Employee PF', 12.00, '2023-2024', 1, 1, 1, 'D', '', 1, 1, '2023-10-17 08:15:57', 0, '2023-10-17 02:45:57'),
(10, 'Employee ESIC', 0.75, '2023-2024', 1, 1, 1, 'D', '', 1, 1, '2023-10-17 08:16:24', 0, '2023-10-17 02:46:24'),
(11, 'Professional Tax', 200.00, '2023-2024', 1, 1, 1, 'D', '', 1, 1, '2023-10-17 08:16:47', 0, '2023-10-17 02:46:47'),
(12, 'Bonus', 8.33, '2023-2024', 1, 1, 1, 'C', '', 0, 1, '2023-10-17 08:17:24', 1, '2023-10-16 22:03:42');

-- --------------------------------------------------------

--
-- Table structure for table `salary_details`
--

CREATE TABLE `salary_details` (
  `id` int(11) NOT NULL,
  `emp_code` varchar(255) NOT NULL,
  `salary_month` varchar(100) NOT NULL,
  `term_name` varchar(255) NOT NULL,
  `term_value` varchar(255) NOT NULL,
  `created_by` int(11) NOT NULL,
  `created_on` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  `updated_by` int(11) NOT NULL,
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- --------------------------------------------------------

--
-- Table structure for table `salary_rules`
--

CREATE TABLE `salary_rules` (
  `rule_id` int(11) NOT NULL,
  `is_esic` int(1) NOT NULL,
  `is_pf` int(1) NOT NULL,
  `description` varchar(500) NOT NULL,
  `is_active` int(1) NOT NULL,
  `created_by` int(11) NOT NULL,
  `created_on` datetime NOT NULL,
  `updated_by` int(11) NOT NULL,
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `salary_rules`
--

INSERT INTO `salary_rules` (`rule_id`, `is_esic`, `is_pf`, `description`, `is_active`, `created_by`, `created_on`, `updated_by`, `updated_on`) VALUES
(1, 0, 0, 'No PF and No ESIC', 1, 1, '2023-09-12 06:59:48', 3, '2023-09-18 00:04:32'),
(2, 0, 1, 'With PF and No ESIC', 1, 1, '2023-09-12 07:08:54', 1, '2023-09-21 02:32:58'),
(3, 1, 1, 'With PF and With ESIC', 1, 1, '2023-09-16 08:10:53', 0, '2023-09-21 02:32:21'),
(4, 1, 0, 'No PF and With ESIC', 1, 1, '2023-09-16 08:11:55', 0, '2023-09-21 02:32:41');

-- --------------------------------------------------------

--
-- Table structure for table `system_log`
--

CREATE TABLE `system_log` (
  `log_id` int(11) NOT NULL,
  `operation` varchar(50) NOT NULL,
  `table_name` varchar(50) NOT NULL,
  `reference_id` int(11) NOT NULL,
  `from_data` text NOT NULL,
  `to_data` text NOT NULL,
  `updated_by` int(11) NOT NULL,
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- --------------------------------------------------------

--
-- Table structure for table `tax`
--

CREATE TABLE `tax` (
  `tax_id` int(11) NOT NULL,
  `tax_name` varchar(250) NOT NULL,
  `tax_details` varchar(250) NOT NULL,
  `tax_percent` varchar(255) NOT NULL,
  `is_active` int(11) NOT NULL DEFAULT '1',
  `created_by` int(11) NOT NULL,
  `created_on` datetime NOT NULL,
  `updated_by` int(11) NOT NULL,
  `updated_on` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- --------------------------------------------------------

--
-- Table structure for table `tds_master`
--

CREATE TABLE `tds_master` (
  `tds_id` int(11) NOT NULL,
  `emp_id` int(11) NOT NULL,
  `fin_yr` int(11) NOT NULL,
  `tds_type` varchar(20) NOT NULL,
  `tds_amt` decimal(10,2) NOT NULL,
  `tds_perc` decimal(10,2) NOT NULL,
  `tds_certi_no` varchar(50) NOT NULL,
  `tds_certi_dt` date NOT NULL,
  `tds_payment_dt` date NOT NULL,
  `tds_chalan_no` varchar(50) NOT NULL,
  `tds_challan_dt` date NOT NULL,
  `remark` text NOT NULL,
  `is_active` int(1) NOT NULL,
  `created_by` int(11) NOT NULL,
  `created_on` datetime NOT NULL,
  `updated_by` int(11) NOT NULL,
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `attendance_rules`
--
ALTER TABLE `attendance_rules`
  ADD PRIMARY KEY (`atte_rule_id`);

--
-- Indexes for table `company`
--
ALTER TABLE `company`
  ADD PRIMARY KEY (`cmp_id`);

--
-- Indexes for table `company_branches`
--
ALTER TABLE `company_branches`
  ADD PRIMARY KEY (`branch_id`);

--
-- Indexes for table `contact_us`
--
ALTER TABLE `contact_us`
  ADD PRIMARY KEY (`contact_us_id`);

--
-- Indexes for table `department`
--
ALTER TABLE `department`
  ADD PRIMARY KEY (`dept_id`);

--
-- Indexes for table `email_logs`
--
ALTER TABLE `email_logs`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `employee_bank_master`
--
ALTER TABLE `employee_bank_master`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `emp_sal_breakup`
--
ALTER TABLE `emp_sal_breakup`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `itr_master`
--
ALTER TABLE `itr_master`
  ADD PRIMARY KEY (`itr_id`);

--
-- Indexes for table `monthly_salary_details`
--
ALTER TABLE `monthly_salary_details`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `monthly_sal_data`
--
ALTER TABLE `monthly_sal_data`
  ADD PRIMARY KEY (`sal_id`);

--
-- Indexes for table `reports_permissoion`
--
ALTER TABLE `reports_permissoion`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `salary_breakup`
--
ALTER TABLE `salary_breakup`
  ADD PRIMARY KEY (`term_id`);

--
-- Indexes for table `salary_details`
--
ALTER TABLE `salary_details`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `salary_rules`
--
ALTER TABLE `salary_rules`
  ADD PRIMARY KEY (`rule_id`);

--
-- Indexes for table `system_log`
--
ALTER TABLE `system_log`
  ADD PRIMARY KEY (`log_id`);

--
-- Indexes for table `tax`
--
ALTER TABLE `tax`
  ADD PRIMARY KEY (`tax_id`);

--
-- Indexes for table `tds_master`
--
ALTER TABLE `tds_master`
  ADD PRIMARY KEY (`tds_id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `company`
--
ALTER TABLE `company`
  MODIFY `cmp_id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `contact_us`
--
ALTER TABLE `contact_us`
  MODIFY `contact_us_id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `email_logs`
--
ALTER TABLE `email_logs`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
COMMIT;

