By Gautham Chandrasekhar
INDEX
1. ACKNOWLEDGEMENT
2. AIM
3. APPARATUS
4. FILE STRUCTURE
5. PROGRAM DESCRIPTION
6. PROGRAM
7. PROGRAM OUTPUT
8. BIBLIOGRAPHY
ACKNOWLEDGEMENT
I would like to extend my deepest
gratitude to Reshimi Mam for her
invaluable guidance, unwavering support,
and exceptional expertise throughout this
project. Her dedication and passion for
teaching have been a beacon of inspiration
and played a crucial role in our success. I
am profoundly thankful for the
opportunity to learn and grow under her
mentorship.
AIM
This program is to make a payroll
management system so as to ease the
duty of official to manage the salary of
employees more efficient
APPARATUS
HARDWARE
1. KEYBOARD
2. Printer
3. Moniter
4. Cpu
SOFTWARE
1. PYTHON
2. MYSQL
3. WINDOWS 10
4. MS WORLD
FILE STRUCTURE
EMPLOYEE TABLE
FILE STRUCTURE
EMPLOYEE TABLE
+------------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+------------+------+-------+-----------+---------+
| empno | int(11) | NO | PRI | NULL | |
| ename | char(20) | YES | | NULL | |
| eaddress | char(20) | YES | | NULL | |
| eage | int(11) | YES | | NULL | |
| esex | char(1) | YES | | NULL | |
| phone | char(10) | YES | | NULL | |
+----------+----------+------+-----+---------+-------+
PAYROLL TABLE
+-------------+-------------------+----------+-------+-----------+--------+
| Field | Type | Null | Key | Default | Extra|
+---------------+-----------------+------------+-----+------------+-------+
| pno | int(11) | NO | PRI | NULL | |
| empno | int(11) | YES | | NULL | |
| month | varchar(29) | YES | | NULL | |
| basicpay | int(11) | YES | | NULL | |
| HRA | float(5,1) | YES | | NULL | |
| DA | float(5,1) | YES | | NULL | |
| PF | float(5,1) | YES | | NULL | |
| incometax | float(5,1) | YES | | NULL | |
| grosssalary | int(11) | YES | | NULL | |
| netsalary | int(20) | YES | | NULL | |
+-------------+-------------+------+-----+---------+-------+
Program
Description
This program is based on payroll
management system. This is a menu
driven program in which the user can
add record, delete, record, update
record, display record of employee,
payroll.
PROGRAM
PAYROLL MANAGEMENT
SYSTEM
CREATING EMPLOYEE TABLE
def addemp():
try:
mycon = sqlcon.connect(host='localhost', user='root', passwd='12345',
database='company')
cur = mycon.cursor()
qry = '''CREATE TABLE IF NOT EXISTS employe (
empno INT NOT NULL PRIMARY KEY,
ename CHAR(20),
eaddress CHAR(20),
eage INT(11),
esex CHAR(1),
phone CHAR(10)
);'''
cur.execute(qry)
print("EMPLOYE table created successfully")
ans = 'y'
while ans.lower() == 'y':
eno = int(input("Enter emp no: "))
n = input("Enter name: ")
s = input("Enter address: ")
age = int(input("Enter age: "))
sex = input("Enter sex: ")
phone = input("Enter phone number: ") # Changed to input() for consistency
qry1 = '''INSERT INTO employe VALUES ({}, '{}', '{}', {}, '{}', '{}')'''.format(eno, n, s,
age, sex, phone)
cur.execute(qry1)
mycon.commit()
ans = input("Continue? y/n: ")
except Exception as e:
print("Error =", e)
finally:
cur.close()
mycon.close()
CREATING TABLE PAYROLL
def addpayroll():
try:
mycon=sqlcon.connect(host='localhost',user='root',passwd='12345',database='company')
cur=mycon.cursor()
qryPAY='''create table if not exists payroll
(pno int(11) not null primary key,
empno int(11),
month varchar(29),
basicpay int(11),
HRA float(5,1),
DA float(5,1),
PF float(5,1),
incometax float(5,1),
grosssalary int(11),
netsalary int(20)
);'''
cur.execute(qryPAY)
print("PAYROLL table created succesfully")
ans='y'
while ans=='y':
pno=int(input("Enter payroll no: "))
empno=int(input("Enter Employe number: "))
month=input("Enter month: ")
basicpay=int(input("Enter BASICPAY: "))
HRA=int(input("Enter HRA %: "))
DA=int(input("Enter DA%: "))
PF=int(input("Enter PF%: "))
incometax=int(input("Enter IncomeTax%: "))
hra=basicpay*HRA/100
da=basicpay*DA/100
pf=basicpay*PF/100
inc=basicpay*incometax/100
gro=basicpay+hra+da
nett=gro-(pf+inc)
qryPAYi='''insert into payroll
values({},{},'{}',{},{},{},{},{},{},{})'''.
f t( th b i HRA DA PF i t tt)
format(pno,empno,month,basicpay,HRA,DA,PF,incometax,gro,nett)
cur.execute(qryPAYi)
mycon.commit()
ans=input("continue? y/n")
except Exception as e:
print("error=",e)
cur.close()
mycon.close()
DELETE EMPLOYEE DETAILS
def deletingemp():
try:
mycon = sqlcon.connect(host='localhost', user='root', passwd='12345',
database='company')
cur = mycon.cursor()
d = int(input("ENTER EMPLOYEE NUMBER TO DELETE"))
qry = '''select * from employe where empno={}'''.format(d)
print("searching...")
cur.execute(qry)
de = cur.fetchall()
if de == []:
print("no such data exists")
else:
print(de)
ans = input("Confirm deleting the data?y/n")
if ans == 'y':
qrytodel = '''delete from employe where empno={}'''.format(d)
cur.execute(qrytodel)
mycon.commit()
print("Data successfully deleted")
elif ans == 'n':
print("Data not deleted")
except Exception as e:
print("error=", e)
cur.close()
mycon.close()
UPDATE EMPLOYEE DETAILS
def updated():
import mysql.connector as sqlcon
try:
mycon = sqlcon.connect(host='localhost', user='root', passwd='12345',
database='company')
cur = mycon.cursor()
d = int(input("ENTER EMPLOYEE NUMBER TO UPDATE"))
qry = '''select * from employe where empno={}'''.format(d)
print('searching...')
cur.execute(qry)
de = cur.fetchall()
if de == []:
print("no such data exists")
else:
print("Exsisting data=", de)
n = input('Enter new name')
ad = input("Enter new address")
ag = int(input("Enter new age"))
s = input("Enter new sex")
p = int(input("Enter new phone number"))
qry1 = '''update employe set ename='{}',eaddress='{}',eage={},esex='{}',phone=
{} where empno={}'''.format(n, ad, ag, s, p, d)
cur.execute(qry1)
mycon.commit()
print("Data succesfully updated")
except Exception as e:
print("error=", e)
cur.close()
mycon.close()
UPDATE PAYROLL DETAILS
def updatep():
import mysql.connector as sqlcon
try:
mycon=sqlcon.connect(host='localhost',user='root',passwd='12345',database='compa
ny')
cur=mycon.cursor()
d=int(input("enter payroll number to update"))
qry='''select * from payroll
where pno={}'''.format(d)
print('')
print('')
print("searching...")
print('')
cur.execute(qry)
de=cur.fetchall()
if de==[]:
print("no such data exists")
print('')
else:
print("Exsisting data=",de)
print('')
empno=int(input("Enter new Employe number: "))
month=input("Enter new month: ")
basicpay=int(input("Enter new BASICPAY: "))
HRA=int(input("Enter new HRA %: "))
DA=int(input("Enter new DA%: "))
PF=int(input("Enter new PF%: "))
incometax=int(input("Enter new IncomeTax%: "))
hra=basicpay*HRA/100
da=basicpay*DA/100
pf=basicpay*PF/100
inc=basicpay*incometax/100
gro=basicpay+hra+da
nett=gro-(pf+inc)
qry1='''update payroll
t {} th '{}' b i {} HRA {} DA {} PF {} i t
set empno={},month='{}',basicpay={},HRA={},DA={},PF={},incometax=
{},grosssalary={},netsalary={}
wherepno={}'''.format
(empno,month,basicpay,HRA,DA,PF,incometax,gro,nett,d)
cur.execute(qry1)
mycon.commit()
print('')
print("Data succesfully updated")
except Exception as e:
print("error=",e)
cur.close()
mycon.close()
DISPLAY EMPLOYEE DETAILS
def displayemptab():
import mysql.connector as sqlcon
try:
mycon=sqlcon.connect(host='localhost',user='root',passwd='12345',database='company')
cur=mycon.cursor()
qry='''select * from employe;'''
print('')
print("searching...")
print('')
cur.execute(qry)
d=cur.fetchall()
if d==[]:
print("no such data exists")
print("empno","\t","ename","\t","eadd","\t","eage","\t",'esex',"\t",'phone')
for i in d:
print(i[0],"\t",end="")
print(i[1],"\t",end="")
print(i[2],"\t",end="")
print(i[3],"\t",end="")
print(i[4],"\t",end="")
print(i[5])
except Exception as e:
print("error=",e)
cur.close()
mycon.close()
DISPLAY PAYROLL DETAILS
def displaypaytab():
import mysql.connector as sqlcon
try:
mycon=sqlcon.connect(host='localhost',user='root',passwd='12345',database='company')
cur=mycon.cursor()
qry='''select * from payroll;'''
print('')
print("searching...")
print('')
cur.execute(qry)
d=cur.fetchall()
if d==[]:
print("no such data exists")
print("pno","\t","empno","\t","month","\t","bp","\t",'HRA',"\t",
'DA',"\t",'PF',"\t",'tax',"\t",'grsa',"\t","netsa")
for i in d:
print(i[0],"\t",end="")
print(i[1],"\t",end="")
print(i[2],"\t",end="")
print(i[3],"\t",end="")
print(i[4],"\t",end="")
print(i[5],"\t",end="")
print(i[6],"\t",end="")
print(i[7],"\t",end="")
print(i[8],"\t",end="")
print(i[9])
except Exception as e:
print("error=",e)
cur.close()
mycon.close()
SEARCH EMPLOYEE
def searchemp():
import mysql.connector as sqlcon
try:
mycon=sqlcon.connect(host='localhost',user='root',passwd='12345',database='company')
cur=mycon.cursor()
se=int(input("Enter the number to search"))
print('')
print("searching...")
print('')
qry='''select * from employe
where empno ={};'''.format(se)
cur.execute(qry)
d=cur.fetchone()
print("empno=",d[1])
print("ename=",d[0])
print("eadd=",d[2])
print("eage=",d[3])
print("esex=",d[4])
print("phone=",d[5])
print('')
except Exception as e:
print("error=",e)
cur.close()
mycon.close()
SEARCH FROM PAYROLL
def searchpay():
import mysql.connector as sqlcon
try:
mycon=sqlcon.connect(host='localhost',user='root',passwd='12345',database='company')
cur=mycon.cursor()
se=int(input("PAYROLL NUMBER TO SEARCH"))
print('')
print("searching...")
print('')
qry='''select * from payroll
where pno ={};'''.format(se)
cur.execute(qry)
d=cur.fetchone()
print("pno=",d[0])
print("empno=",d[1])
print("month=",d[2])
print("basic pay=",d[3])
print("HRA=",d[4])
print("DA=",d[5])
print("PF=",d[6])
print("Income tax=",d[7])
print("Gross salary=",d[8])
print("Net Salary=",d[9])
print('')
except Exception as e:
print("error=",e)
cur.close()
mycon.close()
MENU
#MENU
passw='1234'
user='root'
u = input("ENTER USERNAME: ")
p = input("ENTER PASSWORD: ")
print('\n')
if u == user and p == passw:
for i in range(1, 11):
print("LOADING...", i, '%')
print('\nAccess granted, welcome :)\n\n')
ch = 0
while ch != 5:
print("+-" + '-'*20)
print("1.ADD")
print("2.DELETE")
print("3.UPDATE")
print("4.DISPLAY")
print("5.EXIT")
print("+-" + '-'*20 + '\n')
ch = int(input("ENTER YOUR CHOICE: "))
print('\n')
# CHOICE 1 - ADD
if ch == 1:
chadd = 0
while chadd != 3:
print("+-" + '-'*20)
print("1.ADD EMPLOYEE DETAILS")
print("2.ADD PAYROLL DETAILS")
print("3.BACK")
print("+-" + '-'*20 + '\n')
chadd = int(input("ENTER YOUR CHOICE: "))
print('\n')
if chadd == 1:
addemp()
elif chadd == 2:
dd ll()
addpayroll()
elif chadd == 3:
print("Rerouting back to main menu.....\n")
else:
print("INVALID CHOICE\n")
# CHOICE 2 - DELETE
elif ch == 2:
chadd = 0
while chadd != 3:
print("+-" + '-'*20)
print("1.DELETE EMPLOYEE RECORD")
print("2.DELETE PAYROLL RECORD")
print("3.BACK")
print("+-" + '-'*20 + '\n')
chadd = int(input("ENTER YOUR CHOICE: "))
print('\n')
if chadd == 1:
deletingemp()
elif chadd == 2:
deletingpayroll()
elif chadd == 3:
print("Rerouting back to main menu.....\n")
else:
print("INVALID CHOICE\n")
# CHOICE 3 - UPDATE
elif ch == 3:
chadd = 0
while chadd != 3:
print("+-" + '-'*20)
print("1.UPDATE EMPLOYEE RECORD")
print("2.UPDATE PAYROLL RECORD")
print("3.BACK")
print("+-" + '-'*20 + '\n')
chadd = int(input("ENTER YOUR CHOICE: "))
print('\n')
if chadd == 1:
updated()
elif chadd == 2:
d t ()
updatep()
elif chadd == 3:
print("Rerouting back to main menu.....\n")
else:
print("INVALID CHOICE\n")
# CHOICE 4 - DISPLAY
elif ch == 4:
chadd = 0
while chadd != 5:
print("+-" + '-'*20)
print("1.DISPLAY EMPLOYEE TABLE")
print("2.DISPLAY PAYROLL TABLE")
print("3.SEARCH AN EMPLOYEE")
print("4.SEARCH A PAYROLL NUMBER")
print("5.BACK")
print("+-" + '-'*20 + '\n')
chadd = int(input("ENTER YOUR CHOICE: "))
print('\n')
if chadd == 1:
displayemptab()
elif chadd == 2:
displaypaytab()
elif chadd == 3:
searchemp()
elif chadd == 4:
searchpay()
elif chadd == 5:
print("Rerouting back to main menu.....\n")
else:
print("INVALID CHOICE\n")
elif ch == 5:
print("Exiting...")
else:
print("INVALID CHOICE\n")
else:
for i in range(1, 100):
print("LOADING...", i, '%')
print("LOADING FAILED :(\nWRONG CREDENTIALS.....")
OUTPUT
ENTER USERNAME: root
ENTER PASSWORD: 1234
LOADING... 1 %
LOADING... 2 %
LOADING... 3 %
LOADING... 4 %
LOADING... 5 %
LOADING... 6 %
LOADING... 7 %
LOADING... 8 %
LOADING... 9 %
LOADING... 10 %
Access granted, welcome :)
+---------------------
1.ADD
2.DELETE
3.UPDATE
4.DISPLAY
5.EXIT
+---------------------
ENTER YOUR CHOICE: 1
+---------------------
1.ADD EMPLOYEE DETAILS
2.ADD PAYROLL DETAILS
3.BACK
+---------------------
ENTER YOUR CHOICE: 1
EMPLOYE table created succesfully
enter emp no: 1
Enter name: Shyam
Enter address: Vytilla
Enter age: 20
Enter sex: M
E t h b 8723
Enter phone number: 8723
continue? y/ny
enter emp no: 2
Enter name: Shravani
Enter address: Kaloor
Enter age: 24
Enter sex: F
Enter phone number: 1169
continue? y/ny
enter emp no: 3
Enter name: Joel
Enter address: Aluva
Enter age: 21
Enter sex: M
Enter phone number: 7264
continue? y/nn
+---------------------
1.ADD EMPLOYEE DETAILS
2.ADD PAYROLL DETAILS
3.BACK
+---------------------
ENTER YOUR CHOICE: 2
PAYROLL table created succesfully
Enter payroll no: 1
Enter Employe number: 1
Enter month: Jan
Enter BASICPAY: 75000
Enter HRA %: 8
Enter DA%: 9
Enter PF%: 2
Enter IncomeTax%: 9
continue? y/ny
Enter payroll no: 2
Enter Employe number: 2
Enter month: Feb
Enter BASICPAY: 80000
Enter HRA %: 8
Enter DA%: 9
E t PF% 2
Enter PF%: 2
Enter IncomeTax%: 14
continue? y/ny
Enter payroll no: 3
Enter Employe number: 3
Enter month: May
Enter BASICPAY: 90000
Enter HRA %: 8
Enter DA%: 7
Enter PF%: 2
Enter IncomeTax%: 12
continue? y/nn
+---------------------
1.ADD EMPLOYEE DETAILS
2.ADD PAYROLL DETAILS
3.BACK
+---------------------
ENTER YOUR CHOICE: 3
Rerouting back to main menu.....
+---------------------
1.ADD
2.DELETE
3.UPDATE
4.DISPLAY
5.EXIT
+---------------------
ENTER YOUR CHOICE: 2
+---------------------
1.DELETE EMPLOYEE RECORD
2.DELETE PAYROLL RECORD
3.BACK
+---------------------
ENTER YOUR CHOICE: 1
ENTER EMPLOYEE NUMBER TO DELETE1
ENTER EMPLOYEE NUMBER TO DELETE1
searching...
[(1, 'Shyam', 'Vytilla', 20, 'M', '8723')]
Confirm deleting the data?y/ny
Data succesfully deleted
+---------------------
1.DELETE EMPLOYEE RECORD
2.DELETE PAYROLL RECORD
3.BACK
+---------------------
ENTER YOUR CHOICE: 2
ENTER PAYROLL NUMBER TO DELETE1
searching...
[(1, 1, 'Jan', 75000, 8.0, 9.0, 2.0, 9.0, 87750, 79500)]
Confirm deleting the data?y/ny
Data succesfully deleted
+---------------------
1.DELETE EMPLOYEE RECORD
2.DELETE PAYROLL RECORD
3.BACK
+---------------------
ENTER YOUR CHOICE: 3
Rerouting back to main menu.....
+---------------------
1.ADD
2.DELETE
3.UPDATE
4.DISPLAY
5.EXIT
+---------------------
ENTER YOUR CHOICE: 3
+---------------------
1.UPDATE EMPLOYEE RECORD
2.UPDATE PAYROLL RECORD
3.BACK
+---------------------
ENTER YOUR CHOICE: 1
ENTER EMPLOYEE NUMBER TO UPDATE2
searching...
Exsisting data= [(2, 'Shravani', 'Kaloor', 24, 'F', '1169')]
Enter new name: Shivani
Enter new address: Eloor
Enter new age: 22
Enter new sex: F
Enter new phone number: 9669
Data succesfully updated
+---------------------
1.UPDATE EMPLOYEE RECORD
2.UPDATE PAYROLL RECORD
3.BACK
+---------------------
ENTER YOUR CHOICE: 2
enter payroll number to update3
searching...
Exsisting data= [(3, 3, 'May', 90000, 8.0, 7.0, 2.0, 12.0, 103500, 90900)]
Enter new Employe number: 5
Enter new month: Dec
E t BASICPAY 80000
Enter new BASICPAY: 80000
Enter new HRA %: 5
Enter new DA%: 4
Enter new PF%: 2
Enter new IncomeTax%: 14
Data succesfully updated
+---------------------
1.UPDATE EMPLOYEE RECORD
2.UPDATE PAYROLL RECORD
3.BACK
+---------------------
ENTER YOUR CHOICE: 3
Rerouting back to main menu.....
+---------------------
1.ADD
2.DELETE
3.UPDATE
4.DISPLAY
5.EXIT
+---------------------
ENTER YOUR CHOICE: 4
+---------------------
1.DISPLAY EMPLOYEE TABLE
2.DISPLAY PAYROLL TABLE
3.SEARCH AN EMPLOYEE
4.SEARCH A PAYROLL NUMBER
5.BACK
+---------------------
ENTER YOUR CHOICE: 1
searching...
empno ename eadd eage esex phone
2 Shi i El 22 F 9669
2 Shivani Eloor 22 F 9669
3 Joel Aluva 21 M 7264
+---------------------
1.DISPLAY EMPLOYEE TABLE
2.DISPLAY PAYROLL TABLE
3.SEARCH AN EMPLOYEE
4.SEARCH A PAYROLL NUMBER
5.BACK
+---------------------
ENTER YOUR CHOICE: 2
searching...
pno empno month bp HRA DA PF tax grsa netsa
2 2 Feb 80000 8.0 9.0 2.0 14.0 93600 80800
3 5 Dec 80000 5.0 4.0 2.0 14.0 87200 74400
+---------------------
1.DISPLAY EMPLOYEE TABLE
2.DISPLAY PAYROLL TABLE
3.SEARCH AN EMPLOYEE
4.SEARCH A PAYROLL NUMBER
5.BACK
+---------------------
ENTER YOUR CHOICE: 3
Enter the number to search3
searching...
empno= Joel
ename= 3
eadd= Aluva
eage= 21
esex= M
phone= 7264
+---------------------
1.DISPLAY EMPLOYEE TABLE
2 DISPLAY PAYROLL TABLE
2.DISPLAY PAYROLL TABLE
3.SEARCH AN EMPLOYEE
4.SEARCH A PAYROLL NUMBER
5.BACK
+---------------------
ENTER YOUR CHOICE: 4
PAYROLL NUMBER TO SEARCH2
searching...
pno= 2
empno= 2
month= Feb
basic pay= 80000
HRA= 8.0
DA= 9.0
PF= 2.0
Income tax= 14.0
Gross salary= 93600
Net Salary= 80800
+---------------------
1.DISPLAY EMPLOYEE TABLE
2.DISPLAY PAYROLL TABLE
3.SEARCH AN EMPLOYEE
4.SEARCH A PAYROLL NUMBER
5.BACK
+---------------------
ENTER YOUR CHOICE: 5
Rerouting back to main menu.....
+---------------------
1.ADD
2.DELETE
3.UPDATE
4 DISPLAY
4.DISPLAY
5.EXIT
+---------------------
ENTER YOUR CHOICE: 5
Exiting...
BIBLIOGRAPHY
~GOOGLE
~C.S TEXTBOOK
~WIKIPEDIA
CONCLUSION
As we reach the culmination of this project, I
want to express my sincere gratitude to
everyone who contributed to its success. The
journey of developing this payroll
management system has been both
challenging and rewarding, and it would not
have been possible without the unwavering
support and guidance from my mentors and
peers. I hope that this project serves as a
valuable tool for managing employee payroll
efficiently and effectively. I also hope that it
stands as a testament to the dedication and
hard work invested into it. I am optimistic
that this endeavor will pave the way for
future innovations and improvements in
payroll management systems. Thank you
once again for this opportunity to learn and
grow, and I look forward to applying these
insights in my future projects.
