{"id":9645,"date":"2022-12-20T07:00:00","date_gmt":"2022-12-20T01:30:00","guid":{"rendered":"https:\/\/selfimagination.in\/tips\/?p=9645"},"modified":"2022-12-16T19:41:18","modified_gmt":"2022-12-16T14:11:18","slug":"mysql-payroll-system","status":"publish","type":"post","link":"https:\/\/selfimagination.in\/tips\/mysql-payroll-system\/","title":{"rendered":"MySQL Payroll System"},"content":{"rendered":"\n<p>Welcome Friends, \u092e\u0947 \u0939\u0941 \u0938\u0902\u0926\u0940\u092a \u0928\u093f\u0917\u092e \u0914\u0930 \u0906\u091c \u0939\u092e \u092c\u093e\u0924 \u0915\u0930 \u0930\u0939\u0947 \u0939\u0948\u0902 \u090f\u0915 Payroll System \u0915\u0947 \u092c\u093e\u0930\u0947 \u092e\u0947, \u091c\u0948\u0938\u093e \u0915\u0940 \u0928\u093e\u092e \u0938\u0947 \u0939\u0940 \u0938\u092e\u091d \u092e\u0947 \u0906 \u0930\u0939\u093e \u0939\u0948\u0902 \u0915\u0940 \u0939\u092e Employee Salary Generation \u0915\u0947 \u0932\u093f\u090f \u090f\u0915 Project \u092c\u0928\u093e \u0930\u0939\u0947 \u0939\u0948 \u0964 \u092f\u0939 project \u092c\u0939\u0941\u0924 \u0939\u0940 Basic \u0939\u0948\u0902 \u0907\u0938\u092e\u0947 \u092e\u0947\u0928\u0947 \u0915\u094b\u0908 \u092d\u0940 Advance \u092a\u093e\u0930\u094d\u091f \u0928\u0939\u0940\u0902 \u0932\u093f\u092f\u093e \u0939\u0948 \u0964 \u0907\u0938\u092e\u0947 \u0915\u0947\u0935\u0932 \u090f\u0915 Salary \u0915\u093e Income Head \u0939\u0948 \u0914\u0930 \u090f\u0915 \u0939\u0940 Deduction Head \u0939\u0948\u0902\u0964 \u092f\u0926\u093f \u0906\u092a \u0914\u0930 \u092d\u0940 \u092c\u0939\u0941\u0924 \u0938\u0947 Head \u092c\u095d\u093e\u0928\u093e \u091a\u093e\u0939\u0947 \u0924\u094b \u0907\u0938\u092e\u0947 \u0906\u0938\u093e\u0928\u0940 \u0938\u0947 \u092c\u095d\u093e \u0938\u0915\u0924\u0947 \u0939\u0948\u0902 \u0964 <\/p>\n\n\n\n<p>\u091b\u094b\u091f\u093e \u092c\u0928\u093e\u0928\u0947 \u0915\u093e \u0909\u0926\u094d\u0926\u0947\u0936 \u0915\u0947\u0935\u0932 \u0907\u0924\u0928\u093e \u0939\u0948\u0902 \u0915\u0940 \u0906\u092a\u0915\u094b \u0906\u0938\u093e\u0928\u0940 \u0938\u0947 \u0938\u092e\u091d \u092e\u0947 \u0906 \u0938\u0915\u0947 \u0964 \u0914\u0930 \u0906\u092a \u0916\u0941\u0926 \u0938\u0947 \u0939\u0940 Project Logic \u0932\u0917\u093e \u0938\u0915\u0947 \u0964 \u0914\u0930 \u0916\u0941\u0926 \u0939\u0940 Project \u092c\u0928\u093e \u0938\u0915\u0947 \u0964 <\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>1. Payroll Master<\/li>\n\n\n\n<li>2. Payroll Transaction\n<ul class=\"wp-block-list\">\n<li>Version 1 : Payroll For Company with Less number of Employees. <\/li>\n\n\n\n<li>Version 2 : Payroll For Company with Large number of Employees. <\/li>\n<\/ul>\n<\/li>\n<\/ul>\n\n\n\n<p><a href=\"https:\/\/youtu.be\/-yQEKZI6--c\" target=\"_blank\" rel=\"noopener\">Related Video : Payroll System Planning<\/a><\/p>\n\n\n\n<h2 class=\"wp-block-heading\">1. Payroll Masters <\/h2>\n\n\n\n<p>\u0938\u092c\u0938\u0947 \u092a\u0939\u0932\u0947 \u0939\u092e Master \u092c\u0928\u093e \u0932\u0947\u0924\u0947 \u0939\u0948\u0902 , \u0939\u092e\u0928\u0947 Master \u092e\u0947 \u0915\u0947\u0935\u0932 Employee Master \u0939\u0940 \u0932\u093f\u092f\u093e \u0939\u0948 \u0964 \u0906\u092a \u091c\u093f\u0938\u092e\u0947 \u092d\u0940 \u0939\u092e\u0928\u0947 \u0915\u0947\u0935\u0932 \u090f\u0915 Earning Head \u0914\u0930 \u090f\u0915 Deduction Head \u0932\u093f\u092f\u093e \u0939\u0948\u0902 \u0964 \u0906\u092a \u0907\u0928\u0915\u094b \u0926\u0947\u0916 \u0915\u0930 \u0906\u0938\u093e\u0928\u0940 \u0938\u0947 \u0905\u092a\u0928\u0947 \u0905\u0928\u0941\u0938\u093e\u0930 \u0905\u0928\u094d\u092f Columns \u092d\u0940 \u092c\u095d\u093e \u0938\u0915\u0924\u0947 \u0939\u0948\u0902 \u0964 <\/p>\n\n\n\n<p><strong>Table Structure<\/strong> for Payroll System<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code lang=\"sql\" class=\"language-sql\">CREATE TABLE `mst_emp` (\n `id` int(10) NOT NULL AUTO_INCREMENT,\n `emp_nm` varchar(40) COLLATE utf8_unicode_ci NOT NULL,\n `basic` decimal(9,2) NOT NULL DEFAULT '0.00',\n `ded_per` decimal(4,2) NOT NULL DEFAULT '0.00',\n PRIMARY KEY (`id`)\n) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci<\/code><\/pre>\n\n\n\n<figure class=\"wp-block-image size-full\"><img data-recalc-dims=\"1\" loading=\"lazy\" decoding=\"async\" width=\"640\" height=\"136\" src=\"https:\/\/i0.wp.com\/selfimagination.in\/tips\/wp-content\/uploads\/2022\/12\/image-13.png?resize=640%2C136&#038;ssl=1\" alt=\"\" class=\"wp-image-9666\" srcset=\"https:\/\/i0.wp.com\/selfimagination.in\/tips\/wp-content\/uploads\/2022\/12\/image-13.png?w=864&amp;ssl=1 864w, https:\/\/i0.wp.com\/selfimagination.in\/tips\/wp-content\/uploads\/2022\/12\/image-13.png?resize=300%2C64&amp;ssl=1 300w, https:\/\/i0.wp.com\/selfimagination.in\/tips\/wp-content\/uploads\/2022\/12\/image-13.png?resize=768%2C164&amp;ssl=1 768w, https:\/\/i0.wp.com\/selfimagination.in\/tips\/wp-content\/uploads\/2022\/12\/image-13.png?resize=150%2C32&amp;ssl=1 150w\" sizes=\"auto, (max-width: 640px) 100vw, 640px\" \/><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Storage Engine : <\/strong>Innodb \u0932\u0947\u0928\u093e \u0939\u0948\u0902 \u0924\u093e\u0915\u093f \u0939\u092e Foreign Key Constraint \u0915\u093e \u0909\u092a\u092f\u094b\u0917 \u0915\u0930 \u0938\u0915\u0947 \u0964 <\/li>\n<\/ul>\n\n\n\n<p><a href=\"https:\/\/youtu.be\/NuF789DnwaY\" target=\"_blank\" rel=\"noopener\">Related Video : Payroll System Table Structure<\/a><\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">2. Payroll Transactions <\/h2>\n\n\n\n<p>\u092e\u0947\u0928\u0947 \u092f\u0939\u093e \u092a\u0930 Project \u0915\u094b \u0926\u094b Version \u092e\u0947 \u092c\u0928\u093e\u092f\u093e \u0939\u0948\u0902 \u0964 <\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>\u092a\u0939\u0932\u0947 Version \u092e\u0947 \u0915\u0947\u0935\u0932 \u090f\u0915 Table \u0939\u0948\u0902 \u091c\u093f\u0938\u0915\u093e \u0928\u093e\u092e trn_salary \u0939\u0948\u0902 \u0964  \u0938\u092d\u0940 calculation \u0907\u0938\u0940 Table \u092a\u0930 Virtual Column \u0914\u0930 Before Triggers \u0938\u0947 \u0939\u094b \u0930\u0939\u0947 \u0939\u0948\u0902 \u0964 <\/li>\n\n\n\n<li>\u091c\u092c\u0915\u093f \u0926\u0942\u0938\u0930\u0947 Version \u092e\u0947 \u0926\u094b Tables \u0939\u0948\u0902 , \u0907\u0938\u092e\u0947 Entry \u0915\u0947 Columns \u0915\u094b \u092a\u0939\u0932\u0940 Table \u092e\u0947 Store \u0915\u093f\u092f\u093e \u0939\u0948\u0902 \u0914\u0930 Auto Generation \u0935\u093e\u0932\u0947 Columns \u0915\u094b \u0926\u0942\u0938\u0930\u0940 Table \u092e\u0947 Store \u0915\u093f\u092f\u093e \u0939\u0948\u0902 , \u091c\u094b validation \u0938\u0947 Related \u0935\u0930\u094d\u0915 \u0939\u0948\u0902 \u0909\u0928\u094d\u0939\u0947 \u092a\u0939\u0932\u0940 \u091f\u0947\u092c\u0932 \u092a\u0930 \u0939\u0940 \u0915\u093f\u092f\u093e \u0939\u0948\u0902 \u0964 \u0905\u092c \u092a\u0939\u0932\u0940 \u0938\u0947 \u0926\u0942\u0938\u0930\u0940 \u091f\u0947\u092c\u0932 \u092e\u0947 Data \u0932\u0947 \u091c\u093e\u0928\u0947 \u0915\u0947 \u0932\u093f\u090f Procedure \u0915\u093e \u0909\u092a\u092f\u094b\u0917 \u0915\u093f\u092f\u093e \u0939\u0948\u0902 \u0964 <\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading\">Version 1 : Payroll For Company with less number of Employees. <\/h2>\n\n\n\n<p>\u092f\u0939\u093e \u092a\u0930 \u0939\u092e\u0928\u0947 \u090f\u0915 \u0939\u0940 Table \u092e\u0947 Entry \u0915\u0930\u0928\u0947 \u0935\u093e\u0932\u0947 Columns \u0914\u0930 Auto Calculate \u0939\u094b\u0928\u0947 \u0935\u093e\u0932\u0947 \u0938\u092d\u0940 \u0915\u0949\u0932\u092e \u0932\u093f\u090f \u0939\u0948\u0902 \u0964 <\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img data-recalc-dims=\"1\" loading=\"lazy\" decoding=\"async\" width=\"640\" height=\"290\" src=\"https:\/\/i0.wp.com\/selfimagination.in\/tips\/wp-content\/uploads\/2022\/12\/image-14.png?resize=640%2C290&#038;ssl=1\" alt=\"Payroll System Table Structure\" class=\"wp-image-9670\" srcset=\"https:\/\/i0.wp.com\/selfimagination.in\/tips\/wp-content\/uploads\/2022\/12\/image-14.png?w=885&amp;ssl=1 885w, https:\/\/i0.wp.com\/selfimagination.in\/tips\/wp-content\/uploads\/2022\/12\/image-14.png?resize=300%2C136&amp;ssl=1 300w, https:\/\/i0.wp.com\/selfimagination.in\/tips\/wp-content\/uploads\/2022\/12\/image-14.png?resize=768%2C348&amp;ssl=1 768w, https:\/\/i0.wp.com\/selfimagination.in\/tips\/wp-content\/uploads\/2022\/12\/image-14.png?resize=150%2C68&amp;ssl=1 150w\" sizes=\"auto, (max-width: 640px) 100vw, 640px\" \/><\/figure>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Manual Entry : <\/strong>\u0907\u0928 \u0938\u092d\u0940 \u092d\u0940 Directly Entry \u0939\u094b\u0917\u0940 , \u0907\u0928\u094d\u0939\u0940 Column \u092a\u0930 Validation \u0932\u0917\u093e\u090f \u091c\u093e\u0924\u0947 \u0939\u0948\u0902 \u0964 \n<ul class=\"wp-block-list\">\n<li>TR_DT: \u0907\u0938\u0947 Primary \u092e\u0947 \u0932\u093f\u092f\u093e \u0939\u0948\u0902 \u0924\u093e\u0915\u093f Duplicate Entry \u0928 \u0939\u094b \u0914\u0930 \u0928 \u0939\u0940 Blank \u0939\u094b \u0964 <\/li>\n\n\n\n<li>EMP_ID: \u0907\u0938\u0947 Primary \u092e\u0947 \u0932\u093f\u092f\u093e \u0939\u0948\u0902 \u0924\u093e\u0915\u093f Duplicate Entry \u0928 \u0939\u094b \u0914\u0930 \u0928 \u0939\u0940 Blank \u0939\u094b \u0964 \u0914\u0930 \u0938\u093e\u0925 \u092e\u0947 Foreign Key Constraint set \u0915\u093f\u092f\u093e \u0939\u0948\u0902 \u091c\u093f\u0938\u0938\u0947 \u0915\u093f Master \u0938\u0947 Entry Delete \u092d\u0940 \u0928\u093e \u0939\u094b \u0964  <\/li>\n\n\n\n<li>PAID_DAYS: \u0907\u0938 \u092a\u0930 Validation \u0932\u0917\u093e\u092f\u093e \u0939\u0948\u0902 \u0964 <\/li>\n<\/ul>\n<\/li>\n\n\n\n<li><strong>Validation :<\/strong> \u0907\u0928 Column \u092a\u0930 \u0939\u092e\u0947 Validation \u0932\u0917\u093e\u0928\u093e \u0939\u0948 \u0964 \n<ul class=\"wp-block-list\">\n<li>PAID_DAYS: Paid days Base \u0938\u0947 \u0915\u092d\u0940 \u092d\u0940 \u092c\u095d\u093e \u0928\u0939\u0940\u0902 \u0939\u094b\u0928\u093e \u091a\u093e\u0939\u093f\u090f \u0964 <\/li>\n<\/ul>\n<\/li>\n\n\n\n<li><strong>From Master :<\/strong> Trigger \u0915\u093e \u0909\u092a\u092f\u094b\u0917 \u0915\u0930 \u0907\u0928\u0915\u0940 Value \u0915\u094b Master Table \u0938\u0947 Before Trigger \u092a\u0930 Set \u0915\u0940 \u0939\u0948\u0902 \u0964 \n<ul class=\"wp-block-list\">\n<li>S_BASIC <\/li>\n\n\n\n<li>DED_PER<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li><strong>Calculate : <\/strong>\u0907\u0928 \u0938\u092d\u0940 Column \u092a\u0930 Calculation \u0932\u093f\u0916\u0947 \u0939\u0948\u0902 \u0964 \n<ul class=\"wp-block-list\">\n<li>BASE_DAYS: Month \u0915\u0940 last Date \u0907\u0938\u092e\u0947 \u0938\u0947\u091f \u0915\u0940 \u0939\u0948\u0902 \u0964 <\/li>\n\n\n\n<li>E_BASIC: Basic \u0915\u0947 \u0906\u0927\u093e\u0930 \u092a\u0930 \u091c\u0940\u0924\u0928\u0947 \u0926\u093f\u0928 \u0906\u090f \u0939\u0948\u0902 \u0909\u0924\u0928\u0947 \u0926\u093f\u0928\u094b\u0902 \u0915\u093e Calculation \u0907\u0938 Column \u092e\u0947 \u0915\u093f\u092f\u093e \u0939\u0948\u0902 \u0964 <\/li>\n\n\n\n<li>DED_AMT: Earn Basic \u0938\u0947 Ded_per Calculation \u0915\u0930 Ded Amount \u0907\u0938\u092e\u0947 \u0938\u0947\u091f \u0915\u093f\u092f\u093e \u0939\u0948\u0902 \u0964 <\/li>\n\n\n\n<li>NET_PAY: Earn \u092e\u0947 \u0938\u0947 Deduction \u0915\u094b Less \u0915\u0930 Final Payment Amount \u0907\u0938 \u0915\u0949\u0932\u092e \u092e\u0947 \u0938\u0947\u091f \u0915\u093f\u092f\u093e \u0939\u0948\u0902 \u0964 <\/li>\n<\/ul>\n<\/li>\n<\/ul>\n\n\n\n<p><strong>Use of Virtual Columns <\/strong><\/p>\n\n\n\n<p>\u0907\u0938\u092e\u0947 \u0939\u092e\u0928\u0947 Calculation \u0915\u0947 \u0932\u093f\u090f Virtual Columns \u0915\u093e \u0909\u092a\u092f\u094b\u0917 \u0915\u093f\u092f\u093e \u0939\u0948\u0902 \u0910\u0938\u093e \u0915\u0930\u0928\u0947 \u0938\u0947 \u0939\u092e\u0947 Before Trigger \u092a\u0930 Calculation \u0928\u0939\u0940\u0902 \u0915\u0930\u0928\u0947 \u0939\u094b\u0902\u0917\u0947 \u0964 <\/p>\n\n\n\n<p><a href=\"https:\/\/www.youtube.com\/playlist?list=PLhJ6tQK0qIcNTHV-gxaBmxb7UHIM3Z3E2\" target=\"_blank\" rel=\"noopener\">Related Video Playlist : A Complete Payroll System<\/a><\/p>\n\n\n\n<p>\u0906\u0907\u090f \u0907\u0938 Version \u0915\u0940 \u0924\u0940\u0928\u094b\u0902 Steps \u0915\u094b \u0938\u092e\u091d\u0924\u0947 \u0939\u0948\u0902 \u0964 <\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Step 1: Create Table <\/li>\n\n\n\n<li>Step 2: Create Before Insert Trigger <\/li>\n\n\n\n<li>Step 3: Create Before Update Trigger <\/li>\n<\/ul>\n\n\n\n<p>\u0909\u092a\u0930\u094b\u0915\u094d\u0924 \u0924\u0940\u0928\u094b\u0902 Steps \u0915\u094b Detail \u092e\u0947 \u0938\u092e\u091d\u0924\u0947 \u0939\u0948\u0902 \u0964 <\/p>\n\n\n\n<h3 class=\"wp-block-heading\">V1 Step 1 : Create a Table (trn_salary)<\/h3>\n\n\n\n<pre title=\"Table Structure for trn_salary\" class=\"wp-block-code\"><code lang=\"sql\" class=\"language-sql\">CREATE TABLE `trn_salary` (\n `id` int(10) NOT NULL AUTO_INCREMENT,\n `tr_dt` date NOT NULL,\n `emp_id` int(10) NOT NULL,\n `paid_days` decimal(4,2) NOT NULL,\n `base_days` decimal(4,2) GENERATED ALWAYS AS (dayofmonth(last_day(`tr_dt`))) STORED NOT NULL,\n `s_basic` decimal(9,2) NOT NULL,\n `e_basic` decimal(9,2) GENERATED ALWAYS AS (((`s_basic` * `paid_days`) \/ `base_days`)) STORED NOT NULL,\n `ded_per` decimal(4,2) NOT NULL,\n `ded_amt` decimal(9,2) GENERATED ALWAYS AS (((`e_basic` * `ded_per`) \/ 100)) STORED NOT NULL,\n `net_pay` decimal(9,2) GENERATED ALWAYS AS ((`e_basic` - `ded_amt`)) STORED NOT NULL,\n PRIMARY KEY (`tr_dt`,`emp_id`),\n UNIQUE KEY `u_id` (`id`),\n KEY `cons_emp` (`emp_id`),\n CONSTRAINT `cons_emp` FOREIGN KEY (`emp_id`) REFERENCES `mst_emp` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT\n) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">Before Triggers<\/h2>\n\n\n\n<ul class=\"wp-block-list\">\n<li>\u0907\u0938\u0915\u0940 \u0926\u094b\u0928\u094b\u0902 \u0939\u0940 Triggers \u0915\u0940 Help \u0938\u0947 \u0939\u092e\u0928\u0947 Master Table (mst_emp) \u0938\u0947 Basic \u0914\u0930 Deduction % \u0932\u093e \u0915\u0930 Transaction table (trn_salary) \u092e\u0947 store \u0915\u093f\u092f\u093e \u0939\u0948\u0902 \u0964 <\/li>\n\n\n\n<li>\u0907\u0938\u0915\u0947 \u0938\u093e\u0925 \u0939\u0940 \u0939\u092e\u0928\u0947 Validation \u092f\u093e\u0928\u093f \u0915\u0940 \u092f\u0926\u093f Base Days \u0938\u0947 Paid Days \u092c\u095d\u093e \u0939\u0941\u0935\u093e \u0924\u094b \u092f\u0939 Entry Accept \u0928\u0939\u0940\u0902 \u0915\u0930\u0947\u0917\u093e\u0964 \u0907\u0938 \u0924\u0930\u0939 \u0915\u093e validation \u092d\u0940 \u0932\u0917\u093e\u092f\u093e \u0939\u0948\u0902 \u0964<\/li>\n<\/ul>\n\n\n\n<p><a href=\"https:\/\/youtu.be\/LbkdH-gRQYU\" target=\"_blank\" rel=\"noopener\">Related Video : Payroll System Before Triggers<\/a><\/p>\n\n\n\n<h3 class=\"wp-block-heading\">V1 Step 2 : Create Before Insert Trigger<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code class=\"\">DELIMITER $$\nCREATE TRIGGER `trn_salary_bi` BEFORE INSERT ON `trn_salary` FOR EACH ROW \nBEGIN\n\nDECLARE v_basic decimal(9,2) DEFAULT 0;\nDECLARE v_ded_per decimal(4,2) DEFAULT 0;\n\nSELECT basic, ded_per INTO v_basic, v_ded_per\nFROM mst_emp\nWHERE id = new.emp_id;\n\nSET new.s_basic = v_basic;\nSET new.ded_per = v_ded_per;\n\nIF (new.paid_days > new.base_days) THEN\n\tSIGNAL SQLSTATE '45000'\n        SET MESSAGE_TEXT = 'Invalid Paid Days';\nEND IF;\n\nEND$$\nDELIMITER ;<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">V1 Step 3 : Create Before Update Trigger<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code class=\"\">DELIMITER $$\nCREATE TRIGGER `trn_salary_bu` BEFORE UPDATE ON `trn_salary`\n FOR EACH ROW BEGIN\n\nDECLARE v_basic decimal(9,2) DEFAULT 0;\nDECLARE v_ded_per decimal(4,2) DEFAULT 0;\n\nSELECT basic, ded_per INTO v_basic, v_ded_per\nFROM mst_emp\nWHERE id = new.emp_id;\n\nSET new.s_basic = v_basic;\nSET new.ded_per = v_ded_per;\n\nIF (new.paid_days > new.base_days) THEN\n    SIGNAL SQLSTATE '45000'\n    SET MESSAGE_TEXT = 'Invalid Paid Days';\nEND IF;\n\nEND$$\nDELIMITER ;<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">Version 2 : Payroll For Company with Large number of Employees. <\/h2>\n\n\n\n<p>\u0907\u0938 Version \u092e\u0947 \u0915\u0941\u0932 3 step \u0939\u092e\u0947 \u0915\u0930\u0928\u093e \u0939\u094b\u0917\u0940 \u0964 <\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Step 1: Create First Table (trn_attn)<\/li>\n\n\n\n<li>Step 2: Create Second Table (trn_salary_c)<\/li>\n\n\n\n<li>Step 3: Create Procedure ()<\/li>\n<\/ul>\n\n\n\n<p>\u0906\u0907\u090f \u0909\u092a\u0930\u094b\u0915\u094d\u0924 \u0924\u0940\u0928\u094b\u0902 Steps \u0915\u094b One by One \u0938\u092e\u091d\u0924\u0947 \u0939\u0948\u0902 \u0964 <\/p>\n\n\n\n<h3 class=\"wp-block-heading\">V2 Step 1 : Create First Table<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>\u0907\u0938 Table \u092e\u0947 \u0939\u092e\u0928\u0947 \u0915\u0947\u0935\u0932 Input \u0935\u093e\u0932\u0947 Columns \u0932\u093f\u090f \u0939\u0948\u0902<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code class=\"\">CREATE TABLE `trn_attn` (\n `id` int(10) NOT NULL AUTO_INCREMENT,\n `tr_dt` date NOT NULL,\n `emp_id` int(10) NOT NULL,\n `paid_days` decimal(4,2) NOT NULL,\n `base_days` decimal(4,2) GENERATED ALWAYS AS (dayofmonth(last_day(`tr_dt`))) STORED NOT NULL,\n PRIMARY KEY (`tr_dt`,`emp_id`),\n UNIQUE KEY `u_id` (`id`),\n KEY `cons_emp_a` (`emp_id`),\n CONSTRAINT `cons_emp_a` FOREIGN KEY (`emp_id`) REFERENCES `mst_emp` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT\n) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">V2 Step 2 : Create Second Table<\/h3>\n\n\n\n<p>\u0928\u093f\u092e\u094d\u0928 table \u092e\u0947 \u0939\u092e\u0928\u0947 Primary Column \u0915\u0947 \u0938\u093e\u0925 \u0905\u0928\u094d\u092f \u0938\u092d\u0940 Columns \u0932\u093f\u090f \u0939\u0948\u0902 \u0964 \u092a\u0930\u0902\u0924\u0941 \u0915\u093f\u0938\u0940 \u092d\u0940 \u092a\u094d\u0930\u0915\u093e\u0930 \u0915\u0947 Virtual Columns  \u0907\u0938\u092e\u0947 \u0928\u0939\u0940\u0902 \u0932\u093f\u090f \u0939\u0948\u0902 \u0964  <\/p>\n\n\n\n<pre class=\"wp-block-code\"><code class=\"\">CREATE TABLE `trn_salary_c` (\n `id` int(10) NOT NULL AUTO_INCREMENT,\n `tr_dt` date NOT NULL,\n `emp_id` int(10) NOT NULL,\n `paid_days` decimal(4,2) NOT NULL,\n `base_days` decimal(4,2) NOT NULL,\n `s_basic` decimal(9,2) NOT NULL,\n `e_basic` decimal(9,2) NOT NULL,\n `ded_per` decimal(4,2) NOT NULL,\n `ded_amt` decimal(9,2) NOT NULL,\n `net_pay` decimal(9,2) NOT NULL,\n PRIMARY KEY (`tr_dt`,`emp_id`),\n UNIQUE KEY `u_id` (`id`),\n KEY `cons_emp_c` (`emp_id`),\n CONSTRAINT `cons_emp_c` FOREIGN KEY (`emp_id`) REFERENCES `mst_emp` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT\n) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">V2 Step 3 : Create Procedure <\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Calculation \u0915\u0947 \u0932\u093f\u090f \u0905\u0932\u0917 \u0938\u0947 Procedure \u0932\u093f\u0916 \u0915\u0930 \u092a\u0939\u0932\u0940 \u0935\u093e\u0932\u0940 table \u0938\u0947 Data \u0932\u0947 \u0915\u0930 \u0909\u0938 \u092a\u0930 Calculation \u0915\u0930 \u0909\u0928\u0915\u094b \u0926\u0942\u0938\u0930\u0940 Table \u092e\u0947 Insert \u0915\u093f\u092f\u093e \u0939\u0948\u0902 \u0964 <\/li>\n\n\n\n<li>Procedure \u092e\u0947 Cursor \u0915\u093e \u0909\u092a\u092f\u094b\u0917 \u0915\u093f\u092f\u093e \u0939\u0948\u0902 \u0924\u093e\u0915\u093f \u090f\u0915 \u0938\u0947 \u091c\u094d\u092f\u093e\u0926\u093e Rows \u092a\u0930 Calculation \u0915\u093f\u092f\u093e \u0939\u0948\u0902 \u0964 <\/li>\n<\/ul>\n\n\n\n<p><a href=\"https:\/\/youtu.be\/7Br32MQ7oIQ\" target=\"_blank\" rel=\"noopener\">Related Video : Payroll System Cursor<\/a><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code class=\"\">DELIMITER $$\r\nCREATE PROCEDURE `proc_salary`(IN `p_dt` DATE)\r\n    NO SQL\nBEGIN\n\nDECLARE v_id int(10);\nDECLARE v_tr_dt date;\nDECLARE v_emp_id int(10);\nDECLARE v_paid_days decimal(4,2);\nDECLARE v_base_days decimal(4,2);\n\nDECLARE done int DEFAULT false;\n\nDECLARE cur1 CURSOR FOR SELECT id, tr_dt, emp_id, paid_days, base_days from trn_attn where tr_dt = p_dt;\n\nDECLARE CONTINUE HANDLER for NOT found SET done=true;\n\nOPEN cur1;\n\n\nlbl : LOOP\n\nFETCH cur1 INTO v_id, v_tr_dt, v_emp_id, v_paid_days, v_base_days;\n\nif done THEN\n\tLEAVE lbl;\nEND if;\n\n\nBEGIN\n    DECLARE v_basic decimal(9,2) DEFAULT 0;\n    DECLARE v_ded_per decimal(4,2) DEFAULT 0;\n    DECLARE v_e_basic decimal(9,2) DEFAULT 0;\n    DECLARE v_ded_amt decimal(9,2) DEFAULT 0;\n    DECLARE v_net_pay decimal(9,2) DEFAULT 0;\n\n\n\t#\n\tDELETE FROM trn_salary_c WHERE id = v_id;\n    \n    #From Master\n    \n\n    SELECT basic, ded_per INTO v_basic, v_ded_per\n    FROM mst_emp\n    WHERE id = v_emp_id;\n    \n    \n    \n    set v_e_basic = v_basic * v_paid_days \/ v_base_days;\n\tset v_ded_amt = v_e_basic * v_ded_per \/ 100;\n\tset v_net_pay = v_e_basic - v_ded_amt;\n   \n    \n    #Insert\n    INSERT into trn_salary_c (id, tr_dt, emp_id, paid_days, base_days, s_basic, ded_per, e_basic, ded_amt, net_pay )\n    VALUES (v_id, v_tr_dt, v_emp_id, v_paid_days, v_base_days, v_basic, v_ded_per, v_e_basic, v_ded_amt, v_net_pay);\n\n\nEND;\n\nEND LOOP;\n\nCLOSE cur1;\n\r\nEND$$\r\nDELIMITER ;<\/code><\/pre>\n\n\n\n<p><a href=\"https:\/\/youtu.be\/__IRQCxxD_8\" target=\"_blank\" rel=\"noopener\">Related Video : Payroll System Cursor Parameter Passing<\/a><\/p>\n\n\n\n<p>\u0906\u0936\u093e \u0939\u0948\u0902 \u092e\u0941\u091d\u0947 \u0915\u0940 \u0906\u092a\u0915\u094b \u092f\u0939 MySQL Payroll Management System \u092c\u0939\u0941\u0924 \u0939\u0940 \u0905\u091a\u094d\u091b\u0947 \u0938\u0947 \u0938\u092e\u091d \u092e\u0947 \u0906 \u0917\u092f\u093e \u0939\u094b\u0917\u093e \u0964 \u092b\u093f\u0930 \u092d\u0940 \u092f\u0926\u093f \u0906\u092a\u0915\u094b \u0915\u094b\u0908 \u092d\u0940 Doubt \u0939\u094b \u0924\u094b \u0906\u092a \u092e\u0941\u091d\u0947 \u0928\u093f\u0938\u0902\u0915\u094b\u091a Comment \u0915\u0930 \u0938\u0915\u0924\u0947 \u0939\u0948\u0902 \u0964<\/p>\n\n\n\n<p>\u092f\u0939 Article MySQL Payroll System \u0915\u0948\u0938\u093e \u0932\u0917\u093e \u092c\u0924\u093e\u0928\u093e \u0928 \u092d\u0942\u0932\u0947 \u092e\u093f\u0932\u0924\u0947 \u0939\u0948\u0902 Next Article \u092e\u0947 Thanks for Reading \u0964<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>\u0939\u092e\u093e\u0930\u0947 \u0905\u0928\u094d\u092f \u0906\u0930\u094d\u091f\u093f\u0915\u0932<\/strong><\/h2>\n\n\n\n<ul class=\"wp-block-list\">\n<li><a href=\"https:\/\/selfimagination.in\/tips\/mysql-function\/\">MySQL Function<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/selfimagination.in\/tips\/mysql-stored-procedure\/\">MySQL Stored Procedure<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/selfimagination.in\/tips\/get-post-method\/\">PHP Get \/Post Method \u0915\u0947 \u092c\u093e\u0930\u0947 \u092e\u0947\u0902 \u091c\u093e\u0928\u0947<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/selfimagination.in\/tips\/php-math-function\/\">PHP Math Function \u0915\u0947 \u092c\u093e\u0930\u0947 \u092e\u0947\u0902 \u091c\u093e\u0928\u0947<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/selfimagination.in\/tips\/learn-cpp-from-c\/\">C \u0938\u0947 C ++ \u0938\u0940\u0916\u0947 \u0938\u0930\u0932 \u0936\u092c\u094d\u0926\u094b\u0902 \u092e\u0947\u0902<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/selfimagination.in\/tips\/php-variables\/\">PHP Variable \u0915\u0947 \u092c\u093e\u0930\u0947 \u092e\u0947\u0902 \u091c\u093e\u0928\u0947<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/selfimagination.in\/tips\/arduino-control-structure\/\">Arduino Control Structure<\/a>&nbsp;<\/li>\n\n\n\n<li><a href=\"https:\/\/selfimagination.in\/tips\/c-union\/\">Union in C<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/selfimagination.in\/tips\/c-pointer\/\">Declaration of Pointer in C<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/selfimagination.in\/tips\/search-jquery\/\">jQuery Search Filter<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/selfimagination.in\/tips\/mysql-table-create-alter-drop\/\">MySQL Create Table | Alter Table | Drop Table<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/selfimagination.in\/tips\/api-using-node-react-express-mysql\/\">API Using NODE REACT EXPRESS MYSQL SEQUELIZE (For Backend OR Server Side)<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/selfimagination.in\/tips\/cpanel\/\">CPANEL In Hindi<\/a><\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>Welcome Friends, \u092e\u0947 \u0939\u0941 \u0938\u0902\u0926\u0940\u092a \u0928\u093f\u0917\u092e \u0914\u0930 \u0906\u091c \u0939\u092e \u092c\u093e\u0924 \u0915\u0930 \u0930\u0939\u0947 \u0939\u0948\u0902 \u090f\u0915 Payroll System \u0915\u0947 \u092c\u093e\u0930\u0947 \u092e\u0947, \u091c\u0948\u0938\u093e \u0915\u0940 \u0928\u093e\u092e \u0938\u0947 \u0939\u0940 \u0938\u092e\u091d \u092e\u0947 \u0906 \u0930\u0939\u093e \u0939\u0948\u0902 \u0915\u0940 \u0939\u092e Employee Salary Generation \u0915\u0947 \u0932\u093f\u090f \u090f\u0915 Project \u092c\u0928\u093e \u0930\u0939\u0947 \u0939\u0948 \u0964 \u092f\u0939 project \u092c\u0939\u0941\u0924 \u0939\u0940 Basic \u0939\u0948\u0902 \u0907\u0938\u092e\u0947 \u092e\u0947\u0928\u0947 \u0915\u094b\u0908 \u092d\u0940 Advance \u092a\u093e\u0930\u094d\u091f \u0928\u0939\u0940\u0902 [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":9811,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[5,27],"tags":[],"class_list":["post-9645","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-mysql","category-projects"],"jetpack_sharing_enabled":true,"jetpack_featured_media_url":"https:\/\/i0.wp.com\/selfimagination.in\/tips\/wp-content\/uploads\/2022\/12\/payroll-system.jpg?fit=1200%2C628&ssl=1","_links":{"self":[{"href":"https:\/\/selfimagination.in\/tips\/wp-json\/wp\/v2\/posts\/9645","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/selfimagination.in\/tips\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/selfimagination.in\/tips\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/selfimagination.in\/tips\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/selfimagination.in\/tips\/wp-json\/wp\/v2\/comments?post=9645"}],"version-history":[{"count":97,"href":"https:\/\/selfimagination.in\/tips\/wp-json\/wp\/v2\/posts\/9645\/revisions"}],"predecessor-version":[{"id":9812,"href":"https:\/\/selfimagination.in\/tips\/wp-json\/wp\/v2\/posts\/9645\/revisions\/9812"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/selfimagination.in\/tips\/wp-json\/wp\/v2\/media\/9811"}],"wp:attachment":[{"href":"https:\/\/selfimagination.in\/tips\/wp-json\/wp\/v2\/media?parent=9645"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/selfimagination.in\/tips\/wp-json\/wp\/v2\/categories?post=9645"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/selfimagination.in\/tips\/wp-json\/wp\/v2\/tags?post=9645"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}