MySQL Payroll System

Welcome Friends, मे हु संदीप निगम और आज हम बात कर रहे हैं एक Payroll System के बारे मे, जैसा की नाम से ही समझ मे आ रहा हैं की हम Employee Salary Generation के लिए एक Project बना रहे है । यह project बहुत ही Basic हैं इसमे मेने कोई भी Advance पार्ट नहीं लिया है । इसमे केवल एक Salary का Income Head है और एक ही Deduction Head हैं। यदि आप और भी बहुत से Head बढ़ाना चाहे तो इसमे आसानी से बढ़ा सकते हैं ।

छोटा बनाने का उद्देश केवल इतना हैं की आपको आसानी से समझ मे आ सके । और आप खुद से ही Project Logic लगा सके । और खुद ही Project बना सके ।

• 1. Payroll Master
• 2. Payroll Transaction
  • Version 1 : Payroll For Company with Less number of Employees.
  • Version 2 : Payroll For Company with Large number of Employees.

Related Video : Payroll System Planning

1. Payroll Masters

सबसे पहले हम Master बना लेते हैं , हमने Master मे केवल Employee Master ही लिया है । आप जिसमे भी हमने केवल एक Earning Head और एक Deduction Head लिया हैं । आप इनको देख कर आसानी से अपने अनुसार अन्य Columns भी बढ़ा सकते हैं ।

Table Structure for Payroll System

CREATE TABLE `mst_emp` (
 `id` int(10) NOT NULL AUTO_INCREMENT,
 `emp_nm` varchar(40) COLLATE utf8_unicode_ci NOT NULL,
 `basic` decimal(9,2) NOT NULL DEFAULT '0.00',
 `ded_per` decimal(4,2) NOT NULL DEFAULT '0.00',
 PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

• Storage Engine : Innodb लेना हैं ताकि हम Foreign Key Constraint का उपयोग कर सके ।

Related Video : Payroll System Table Structure

---

2. Payroll Transactions

मेने यहा पर Project को दो Version मे बनाया हैं ।

• पहले Version मे केवल एक Table हैं जिसका नाम trn_salary हैं । सभी calculation इसी Table पर Virtual Column और Before Triggers से हो रहे हैं ।
• जबकि दूसरे Version मे दो Tables हैं , इसमे Entry के Columns को पहली Table मे Store किया हैं और Auto Generation वाले Columns को दूसरी Table मे Store किया हैं , जो validation से Related वर्क हैं उन्हे पहली टेबल पर ही किया हैं । अब पहली से दूसरी टेबल मे Data ले जाने के लिए Procedure का उपयोग किया हैं ।

Version 1 : Payroll For Company with less number of Employees.

यहा पर हमने एक ही Table मे Entry करने वाले Columns और Auto Calculate होने वाले सभी कॉलम लिए हैं ।

• Manual Entry : इन सभी भी Directly Entry होगी , इन्ही Column पर Validation लगाए जाते हैं ।
  • TR_DT: इसे Primary मे लिया हैं ताकि Duplicate Entry न हो और न ही Blank हो ।
  • EMP_ID: इसे Primary मे लिया हैं ताकि Duplicate Entry न हो और न ही Blank हो । और साथ मे Foreign Key Constraint set किया हैं जिससे कि Master से Entry Delete भी ना हो ।
  • PAID_DAYS: इस पर Validation लगाया हैं ।
• Validation : इन Column पर हमे Validation लगाना है ।
  • PAID_DAYS: Paid days Base से कभी भी बढ़ा नहीं होना चाहिए ।
• From Master : Trigger का उपयोग कर इनकी Value को Master Table से Before Trigger पर Set की हैं ।
  • S_BASIC
  • DED_PER
• Calculate : इन सभी Column पर Calculation लिखे हैं ।
  • BASE_DAYS: Month की last Date इसमे सेट की हैं ।
  • E_BASIC: Basic के आधार पर जीतने दिन आए हैं उतने दिनों का Calculation इस Column मे किया हैं ।
  • DED_AMT: Earn Basic से Ded_per Calculation कर Ded Amount इसमे सेट किया हैं ।
  • NET_PAY: Earn मे से Deduction को Less कर Final Payment Amount इस कॉलम मे सेट किया हैं ।

Use of Virtual Columns

इसमे हमने Calculation के लिए Virtual Columns का उपयोग किया हैं ऐसा करने से हमे Before Trigger पर Calculation नहीं करने होंगे ।

Related Video Playlist : A Complete Payroll System

आइए इस Version की तीनों Steps को समझते हैं ।

• Step 1: Create Table
• Step 2: Create Before Insert Trigger
• Step 3: Create Before Update Trigger

उपरोक्त तीनों Steps को Detail मे समझते हैं ।

V1 Step 1 : Create a Table (trn_salary)

CREATE TABLE `trn_salary` (
 `id` int(10) NOT NULL AUTO_INCREMENT,
 `tr_dt` date NOT NULL,
 `emp_id` int(10) NOT NULL,
 `paid_days` decimal(4,2) NOT NULL,
 `base_days` decimal(4,2) GENERATED ALWAYS AS (dayofmonth(last_day(`tr_dt`))) STORED NOT NULL,
 `s_basic` decimal(9,2) NOT NULL,
 `e_basic` decimal(9,2) GENERATED ALWAYS AS (((`s_basic` * `paid_days`) / `base_days`)) STORED NOT NULL,
 `ded_per` decimal(4,2) NOT NULL,
 `ded_amt` decimal(9,2) GENERATED ALWAYS AS (((`e_basic` * `ded_per`) / 100)) STORED NOT NULL,
 `net_pay` decimal(9,2) GENERATED ALWAYS AS ((`e_basic` - `ded_amt`)) STORED NOT NULL,
 PRIMARY KEY (`tr_dt`,`emp_id`),
 UNIQUE KEY `u_id` (`id`),
 KEY `cons_emp` (`emp_id`),
 CONSTRAINT `cons_emp` FOREIGN KEY (`emp_id`) REFERENCES `mst_emp` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Before Triggers

• इसकी दोनों ही Triggers की Help से हमने Master Table (mst_emp) से Basic और Deduction % ला कर Transaction table (trn_salary) मे store किया हैं ।
• इसके साथ ही हमने Validation यानि की यदि Base Days से Paid Days बढ़ा हुवा तो यह Entry Accept नहीं करेगा। इस तरह का validation भी लगाया हैं ।

Related Video : Payroll System Before Triggers

V1 Step 2 : Create Before Insert Trigger

DELIMITER $$
CREATE TRIGGER `trn_salary_bi` BEFORE INSERT ON `trn_salary` FOR EACH ROW 
BEGIN

DECLARE v_basic decimal(9,2) DEFAULT 0;
DECLARE v_ded_per decimal(4,2) DEFAULT 0;

SELECT basic, ded_per INTO v_basic, v_ded_per
FROM mst_emp
WHERE id = new.emp_id;

SET new.s_basic = v_basic;
SET new.ded_per = v_ded_per;

IF (new.paid_days > new.base_days) THEN
	SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Invalid Paid Days';
END IF;

END$$
DELIMITER ;

V1 Step 3 : Create Before Update Trigger

DELIMITER $$
CREATE TRIGGER `trn_salary_bu` BEFORE UPDATE ON `trn_salary`
 FOR EACH ROW BEGIN

DECLARE v_basic decimal(9,2) DEFAULT 0;
DECLARE v_ded_per decimal(4,2) DEFAULT 0;

SELECT basic, ded_per INTO v_basic, v_ded_per
FROM mst_emp
WHERE id = new.emp_id;

SET new.s_basic = v_basic;
SET new.ded_per = v_ded_per;

IF (new.paid_days > new.base_days) THEN
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'Invalid Paid Days';
END IF;

END$$
DELIMITER ;

Version 2 : Payroll For Company with Large number of Employees.

इस Version मे कुल 3 step हमे करना होगी ।

• Step 1: Create First Table (trn_attn)
• Step 2: Create Second Table (trn_salary_c)
• Step 3: Create Procedure ()

आइए उपरोक्त तीनों Steps को One by One समझते हैं ।

V2 Step 1 : Create First Table

• इस Table मे हमने केवल Input वाले Columns लिए हैं

CREATE TABLE `trn_attn` (
 `id` int(10) NOT NULL AUTO_INCREMENT,
 `tr_dt` date NOT NULL,
 `emp_id` int(10) NOT NULL,
 `paid_days` decimal(4,2) NOT NULL,
 `base_days` decimal(4,2) GENERATED ALWAYS AS (dayofmonth(last_day(`tr_dt`))) STORED NOT NULL,
 PRIMARY KEY (`tr_dt`,`emp_id`),
 UNIQUE KEY `u_id` (`id`),
 KEY `cons_emp_a` (`emp_id`),
 CONSTRAINT `cons_emp_a` FOREIGN KEY (`emp_id`) REFERENCES `mst_emp` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

V2 Step 2 : Create Second Table

निम्न table मे हमने Primary Column के साथ अन्य सभी Columns लिए हैं । परंतु किसी भी प्रकार के Virtual Columns इसमे नहीं लिए हैं ।

CREATE TABLE `trn_salary_c` (
 `id` int(10) NOT NULL AUTO_INCREMENT,
 `tr_dt` date NOT NULL,
 `emp_id` int(10) NOT NULL,
 `paid_days` decimal(4,2) NOT NULL,
 `base_days` decimal(4,2) NOT NULL,
 `s_basic` decimal(9,2) NOT NULL,
 `e_basic` decimal(9,2) NOT NULL,
 `ded_per` decimal(4,2) NOT NULL,
 `ded_amt` decimal(9,2) NOT NULL,
 `net_pay` decimal(9,2) NOT NULL,
 PRIMARY KEY (`tr_dt`,`emp_id`),
 UNIQUE KEY `u_id` (`id`),
 KEY `cons_emp_c` (`emp_id`),
 CONSTRAINT `cons_emp_c` FOREIGN KEY (`emp_id`) REFERENCES `mst_emp` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

V2 Step 3 : Create Procedure

• Calculation के लिए अलग से Procedure लिख कर पहली वाली table से Data ले कर उस पर Calculation कर उनको दूसरी Table मे Insert किया हैं ।
• Procedure मे Cursor का उपयोग किया हैं ताकि एक से ज्यादा Rows पर Calculation किया हैं ।

Related Video : Payroll System Cursor

DELIMITER $$
CREATE PROCEDURE `proc_salary`(IN `p_dt` DATE)
    NO SQL
BEGIN

DECLARE v_id int(10);
DECLARE v_tr_dt date;
DECLARE v_emp_id int(10);
DECLARE v_paid_days decimal(4,2);
DECLARE v_base_days decimal(4,2);

DECLARE done int DEFAULT false;

DECLARE cur1 CURSOR FOR SELECT id, tr_dt, emp_id, paid_days, base_days from trn_attn where tr_dt = p_dt;

DECLARE CONTINUE HANDLER for NOT found SET done=true;

OPEN cur1;


lbl : LOOP

FETCH cur1 INTO v_id, v_tr_dt, v_emp_id, v_paid_days, v_base_days;

if done THEN
	LEAVE lbl;
END if;


BEGIN
    DECLARE v_basic decimal(9,2) DEFAULT 0;
    DECLARE v_ded_per decimal(4,2) DEFAULT 0;
    DECLARE v_e_basic decimal(9,2) DEFAULT 0;
    DECLARE v_ded_amt decimal(9,2) DEFAULT 0;
    DECLARE v_net_pay decimal(9,2) DEFAULT 0;


	#
	DELETE FROM trn_salary_c WHERE id = v_id;
    
    #From Master
    

    SELECT basic, ded_per INTO v_basic, v_ded_per
    FROM mst_emp
    WHERE id = v_emp_id;
    
    
    
    set v_e_basic = v_basic * v_paid_days / v_base_days;
	set v_ded_amt = v_e_basic * v_ded_per / 100;
	set v_net_pay = v_e_basic - v_ded_amt;
   
    
    #Insert
    INSERT into trn_salary_c (id, tr_dt, emp_id, paid_days, base_days, s_basic, ded_per, e_basic, ded_amt, net_pay )
    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);


END;

END LOOP;

CLOSE cur1;

END$$
DELIMITER ;

Related Video : Payroll System Cursor Parameter Passing

आशा हैं मुझे की आपको यह MySQL Payroll Management System बहुत ही अच्छे से समझ मे आ गया होगा । फिर भी यदि आपको कोई भी Doubt हो तो आप मुझे निसंकोच Comment कर सकते हैं ।

यह Article MySQL Payroll System कैसा लगा बताना न भूले मिलते हैं Next Article मे Thanks for Reading ।

हमारे अन्य आर्टिकल

• MySQL Function
• MySQL Stored Procedure
• PHP Get /Post Method के बारे में जाने
• PHP Math Function के बारे में जाने
• C से C ++ सीखे सरल शब्दों में
• PHP Variable के बारे में जाने
• Arduino Control Structure 
• Union in C
• Declaration of Pointer in C
• jQuery Search Filter
• MySQL Create Table | Alter Table | Drop Table
• API Using NODE REACT EXPRESS MYSQL SEQUELIZE (For Backend OR Server Side)
• CPANEL In Hindi