In this article, I am going to explain you how to generate Employee pay slip or salary slip in PDF format using itextsharp in asp.net . I will be using visual studio 2013 professional. Before continuing this article, let's see the screen-shots.
When the page is loaded for the first time then you will see the below screen. You will select employee id and month and click on generate button to download salary slip. I have hard-coded some employee id and month in dropdownlist control in aspx file.
I have shared pdf file sample as shown below in iframe.
So, below is the step by step tutorial.
Creating table and procedure
Below is the scripts to create 2 tables tbl_EmployeeDetails and tbl_SalaryDetails for employee details and salary details.
create tabletbl_EmployeeDetails
(
EmpId int,
Name varchar(100),
PFNumber varchar(100),
DOJ datetime,
Designation varchar(50),
Department varchar(50),
AccountNumber varchar(50),
PAN varchar(50),
ModeofPay varchar(50),
UAN varchar(50)
)
go
insert intotbl_EmployeeDetails values(1001,'Rahul Singh','MH/0001/0001','2016-01-10',
'Software Engineer','Development','123456789','PAN1234X','ABC Bank','101010101010')
insert intotbl_EmployeeDetails values(1002,'Vijay Singh','MH/0002/0002','2016-01-10',
'Software Engineer','Testing','10101010','PAN1101X','ABC Bank','000000011111')
go
create tabletbl_SalaryDetails
(
EmpId int,
PaidMonth varchar(25),
NoOfDays int,
NoOfDaysPaid int,
Basic decimal(12,2),
HRA decimal(12,2),
CityCompAllowance decimal(12,2),
EduAllowance decimal(12,2),
MedicalReimbursement decimal(12,2),
LTA decimal(12,2),
ConveyanceAllowance decimal(12,2),
FoodAllowance decimal(12,2),
Mobile decimal(12,2),
ProfessionalPersuit decimal(12,2),
ProvidentFund decimal(12,2),
ProfessionalTax decimal(12,2),
)
go
insert intotbl_SalaryDetails values(1001,'Aug/2017',30,30,15000,9167,5015,200,1250,3000,1600,2500,200,2000,1800,200)
insert intotbl_SalaryDetails values(1001,'Sept/2017',30,29,15000,9167,5015,200,1250,3000,1600,2500,200,2000,1700,200)
insert intotbl_SalaryDetails values(1002,'Aug/2017',30,30,13000,7000,5015,200,1250,3000,1600,2500,200,2000,1800,200)
insert intotbl_SalaryDetails values(1002,'Sept/2017',30,29,13000,7000,5015,200,1250,3000,1600,2500,200,2000,1700,200)
Below is script to create stored procedure that will return 5 tables i.e. Employee details, salary earning details, salary deduction details, gross earning total amount and gross deduction total amount.
create procedure[dbo].[usp_GetSalaryDetails]
@EmpId int,
@PaidMonth varchar(25)
as
begin
--Employee details
select
e.EmpId as 'Employee ID',
e.Name as 'Employee Name',
e.PFNumber as 'PF. No.',
s.NoOfDays as 'No of Days Worked',
s.NoOfDaysPaid as 'No of Days Paid',
convert(varchar, e.DOJ, 103) as 'Date of Joining',
e.Designation as 'Designation',
e.Department as 'Department',
e.AccountNumber as 'Bank Account Number',
e.PAN as 'PAN Number',
e.ModeofPay as 'Mode of Pay',
e.UAN as 'UAN'
fromtbl_EmployeeDetails as e join tbl_SalaryDetails ass
on e.EmpId=s.EmpId
where e.EmpId=@EmpId and s.PaidMonth=@PaidMonth
--Salary Earning details
select
Basic,
HRA,
CityCompAllowance as'City Comp. Allowance',
EduAllowance as'Edu. Allowance',
MedicalReimbursement as'Medical Reimbursement',
LTA,
ConveyanceAllowance as'Conveyance Allowance',
FoodAllowance as'Food Allowance',
Mobile,
ProfessionalPersuit as'Professional Persuit'
from
tbl_SalaryDetails
where EmpId=@EmpId and PaidMonth=@PaidMonth
--salary deduction details
select
ProvidentFund as'Provident Fund',
ProfessionalTax as'Professional Tax'
from
tbl_SalaryDetails
where EmpId=@EmpId and PaidMonth=@PaidMonth
--Gross earning total amount
select
Basic+
HRA+
CityCompAllowance+
EduAllowance +
MedicalReimbursement +
LTA+
ConveyanceAllowance+
FoodAllowance+
Mobile+
ProfessionalPersuit as'Gross Earning'
from
tbl_SalaryDetails
where EmpId=@EmpId and PaidMonth=@PaidMonth
--Gross deduction total amount
select
ProvidentFund+
ProfessionalTax as'Gross Deduction'
from
tbl_SalaryDetails
where EmpId=@EmpId and PaidMonth=@PaidMonth
end
Creating asp.net application
Create an asp.net empty web application as shown below.
1) Add a new Web Form.
2) Add 3 folders BLL, DAL and images.
3) In images folder, add logo.png of company.
4) In BLL folder add Code.cs class file.
5) In DAL folder add Employee.cs class file.
In Employee.cs file
Add below lines of code in Employee.cs file.
usingSystem;
usingSystem.Collections.Generic;
usingSystem.Data;
usingSystem.Data.SqlClient;
usingSystem.Linq;
usingSystem.Web;
namespaceWebApplication1.DAL
{
publicclassEmployee
{
publicDataSetGetData(int EmpId, string PaidMonth)
{
stringMyConnString = @"server=###;database=###;uid=###;password=###;";
SqlConnection con = new SqlConnection(MyConnString);
SqlCommandcmd = new SqlCommand("usp_GetSalaryDetails", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@EmpId", EmpId);
cmd.Parameters.AddWithValue("@PaidMonth", PaidMonth);
con.Open();
