-
Notifications
You must be signed in to change notification settings - Fork 0
/
employees.py
504 lines (389 loc) · 24.5 KB
/
employees.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
from tkinter import *
from tkinter import ttk # for combobox
from tkinter import messagebox
from datetime import date # to access todays date (clear_fields)
from tkcalendar import DateEntry # terminal: pip install tkcalendar
import pymysql # terminal: pip install pymysql
def connect_database():
# Function to establish a connection to the MySQL database
try:
connection = pymysql.connect(host='localhost', user='root', password='1234')
cursor = connection.cursor()
except:
# Show an error message if there's an issue connecting to the database
messagebox.showerror('Error', 'Database connectivity issue, open mysql command line client')
return None, None
return cursor, connection
def create_database_table():
# Function to create a database and a table for employee data if they don't exist
cursor, connection = connect_database()
# Create database and use it
cursor.execute('CREATE DATABASE IF NOT EXISTS inventory_system')
cursor.execute('USE inventory_system')
# Create the employee_data table if it doesn't already exist
cursor.execute('CREATE TABLE IF NOT EXISTS employee_data (empid INT PRIMARY KEY, name VARCHAR(100), '
'email VARCHAR(100), gender VARCHAR(50), dob VARCHAR(30), contact VARCHAR(30),employement_type VARCHAR(50),'
'education VARCHAR(30), work_shift VARCHAR(50), address VARCHAR(100), doj VARCHAR(30), '
'salary VARCHAR(50), usertype VARCHAR(50), password VARCHAR(50))')
def treeview_data():
# Function to fetch all data from the employee_data table and populate the Treeview widget
cursor, connection = connect_database()
if not cursor or not connection:
return
cursor.execute('USE inventory_system')
try:
# Fetch all employee records
cursor.execute('SELECT * from employee_data')
employee_records = cursor.fetchall()
# Clear the Treeview before adding new data
treeview.delete(*treeview.get_children())
# Insert fetched records into the Treeview
for record in employee_records:
treeview.insert('', END, values=record)
except Exception as e:
# Show error message in case of any issue
messagebox.showerror('Error', f'Error due to {e}')
finally:
# Close the database connection
cursor.close()
connection.close()
def select_data(event, empId_entry, name_entry, email_entry, dob_date_entry, gender_combobox, contact_entry,
employement_type_combobox, education_combobox, work_shift_combobox, address_text, doj_date_entry,
salary_entry, usertype_combobox, password_entry):
# Function to select a row from Treeview and fill the form fields with its data
index = treeview.selection()
content = treeview.item(index)
row = content['values']
# Clear previous data from the form fields
clear_fields(empId_entry, name_entry, email_entry, dob_date_entry, gender_combobox, contact_entry,
employement_type_combobox, education_combobox, work_shift_combobox, address_text, doj_date_entry,
salary_entry, usertype_combobox, password_entry, False)
# Fill form fields with the selected row data
empId_entry.insert(0, row[0])
name_entry.insert(0, row[1])
email_entry.insert(0, row[2])
gender_combobox.set(row[3])
dob_date_entry.set_date(row[4])
contact_entry.insert(0, row[5])
employement_type_combobox.set(row[6])
education_combobox.set(row[7])
work_shift_combobox.set(row[8])
address_text.insert(1.0, row[9])
doj_date_entry.set_date(row[10])
salary_entry.insert(0, row[11])
usertype_combobox.set(row[12])
password_entry.insert(0, row[13])
def add_employee(empid, name, email, gender, dob, contact, employement_type, education, work_shift, address, doj, salary, user_type, password):
# Function to add a new employee record into the employee_data table
# Check if all fields are filled
if (empid == '' or name == '' or email == '' or gender == 'Select Gender' or contact == ''
or employement_type == 'Select Type' or education == 'Select Education' or work_shift == 'Select Work Shift'
or address == '\n' or salary == '' or user_type == 'Select User Type' or password == ''):
# Show error message if any field is empty
messagebox.showerror('Error', 'All fields are required')
else:
# Connect to the database
cursor, connection = connect_database()
if not cursor or not connection:
return
cursor.execute('USE inventory_system')
try:
address = address.strip() # to not add \n at the end of the data
# Insert the new employee record into the employee_data table
cursor.execute('INSERT INTO employee_data VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)',
(empid, name, email, gender, dob, contact, employement_type, education, work_shift, address, doj, salary, user_type, password))
connection.commit() # Commit the transaction
# Refresh the Treeview with the new data
treeview_data()
# Show success message
messagebox.showinfo('Success', 'Data is inserted successfully')
except Exception as e:
# Show error message in case of any issue
messagebox.showerror('Error', f'Error due to {e}')
finally:
# Close the database connection
cursor.close()
connection.close()
def clear_fields(empId_entry, name_entry, email_entry, dob_date_entry, gender_combobox,
contact_entry, employement_type_combobox, education_combobox, work_shift_combobox,
address_text, doj_date_entry, salary_entry, usertype_combobox, password_entry, check):
# Function to clear all input fields in the form
empId_entry.delete(0, END) # Clear Employee ID field
name_entry.delete(0, END) # Clear Name field
email_entry.delete(0, END) # Clear Email field
dob_date_entry.set_date(date.today()) # Reset DOB to today's date
gender_combobox.set('Select Gender') # Reset Gender combobox
contact_entry.delete(0, END) # Clear Contact field
employement_type_combobox.set('Select Type') # Reset Employment Type combobox
education_combobox.set('Select Education') # Reset Education combobox
work_shift_combobox.set('Select Work Shift') # Reset Work Shift combobox
address_text.delete(1.0, END) # Clear Address text box (from 1st row and 1st character)
doj_date_entry.set_date(date.today()) # Reset DOJ to today's date
salary_entry.delete(0, END) # Clear Salary field
usertype_combobox.set('Select User Type') # Reset User Type combobox
password_entry.delete(0, END) # Clear Password field
if check:
treeview.selection_remove(treeview.selection()) # Remove any row selection in Treeview
def update_employee(empid, name, email, gender, dob, contact, employement_type, education, work_shift, address, doj, salary, user_type, password):
selected = treeview.selection()
if not selected:
messagebox.showerror('Error', 'No row is selected')
else:
# Connect to the database
cursor, connection = connect_database()
if not cursor or not connection:
return
try:
cursor.execute('USE inventory_system')
# Select the row which is going to be updated (check if update is needed or not)
cursor.execute('SELECT * from employee_data WHERE empid=%s', (empid,)) # provided comma(,) after empid -> to treat empid as an element of tuple
current_data = cursor.fetchone()
current_data = current_data[1:] # exluding empid (primary key) to compare
address = address.strip()
new_data = (name, email, gender, dob, contact, employement_type, education, work_shift, address, doj, salary, user_type, password)
if current_data == new_data:
messagebox.showinfo('Information', 'No changes detected')
return
except Exception as e:
# Show error message in case of any issue
messagebox.showerror('Error', f'Error due to {e}')
# Update Database
cursor.execute('UPDATE employee_data SET name=%s, email=%s, gender=%s, dob=%s, contact=%s, employement_type=%s,'
'education=%s, work_shift=%s, address=%s, doj=%s, salary=%s, usertype=%s, password=%s WHERE empid=%s',
(name, email, gender, dob, contact, employement_type, education, work_shift, address, doj, salary, user_type, password, empid))
connection.commit()
treeview_data()
messagebox.showinfo('Success', 'Data is updated successfully')
# Close the database connection
cursor.close()
connection.close()
def delete_employee(empid):
selected = treeview.selection()
if not selected:
messagebox.showerror('Error', 'No row is selected')
else:
result = messagebox.askyesno('Confirm', 'Do you really want to delete the record')
if result:
# Connect to the database
cursor, connection = connect_database()
if not cursor or not connection:
return
try:
cursor.execute('USE inventory_system')
cursor.execute('DELETE FROM employee_data where empid=%s', (empid,))
connection.commit()
treeview_data()
messagebox.showinfo('Success', 'Record is deleted')
except Exception as e:
# Show error message in case of any issue
messagebox.showerror('Error', f'Error due to {e}')
finally:
# Close the database connection
cursor.close()
connection.close()
def search_employee(search_option, value):
if search_option == 'Search By':
messagebox.showerror('Error', 'No option is selected')
elif value == '':
messagebox.showerror('Error', 'Enter the value to search')
else:
search_option = search_option.replace(' ', '_')
cursor, connection = connect_database()
if not cursor or not connection:
return
try:
cursor.execute('USE inventory_system')
cursor.execute(f'SELECT * from employee_data WHERE {search_option} LIKE %s', f'%{value}%')
records = cursor.fetchall()
treeview.delete(*treeview.get_children())
for record in records:
treeview.insert('', END, value=record)
except Exception as e:
# Show error message in case of any issue
messagebox.showerror('Error', f'Error due to {e}')
finally:
# Close the database connection
cursor.close()
connection.close()
def show_all(search_combobox, search_entry):
treeview_data()
search_combobox.set('Search By')
search_entry.delete(0,END)
def employee_form(window):
# Function to display the Employee Management form
create_database_table()
# Create a frame for the employee form
global back_image, treeview
employee_frame = Frame(window, width=1070, height=567, bg='white')
employee_frame.place(x=200, y=100)
# Create a heading label for the employee management section
heading_label = Label(employee_frame, text='Manage Employee Details', font=('times new roman', 16, 'bold'), bg='#0F4D7D', fg='white')
heading_label.place(x=0, y=0, relwidth=1)
# Top frame for search options
top_frame = Frame(employee_frame, bg='white')
top_frame.place(x=0, y=40, relwidth=1, height=235)
# Back button to exit the employee form
back_image = PhotoImage(file='assets/back.png')
back_button = Button(top_frame, image=back_image, bd=0, cursor='hand2', bg='white', command= lambda: employee_frame.place_forget())
back_button.place(x=10, y=0)
# Search frame for search criteria and buttons
search_frame = Frame(top_frame, bg='white')
search_frame.pack()
# Combobox for selecting search criteria (Id, Name, Email)
search_combobox = ttk.Combobox(search_frame, values=('EmpId', 'Name', 'Email', 'Gender', 'Employement Type', 'Work Shift'), font=('times new roman', 12), state='readonly')
search_combobox.set('Search By')
search_combobox.grid(row=0, column=0, padx=20)
# Entry field to input search query
search_entry = Entry(search_frame, font=('times new roman', 12), bg='lightyellow')
search_entry.grid(row=0, column=1)
# Search button
search_button = Button(search_frame, text='Search', font=('times new roman', 12), width=10, cursor='hand2', fg='white', bg='#0F4D7D', command= lambda: search_employee(search_combobox.get(), search_entry.get()))
search_button.grid(row=0, column=2, padx=20)
# Show All button to display all employees
show_button = Button(search_frame, text='Show All', font=('times new roman', 12), width=10, cursor='hand2', fg='white', bg='#0F4D7D', command= lambda: show_all(search_combobox, search_entry))
show_button.grid(row=0, column=3)
# Horizontal and vertical scrollbars for the treeview (employee table)
horizontal_scrollbar = Scrollbar(top_frame, orient=HORIZONTAL)
vertical_scrollbar = Scrollbar(top_frame, orient=VERTICAL)
# Treeview to display employee data in table form
treeview = ttk.Treeview(top_frame, columns=('empId', 'name', 'email', 'gender', 'dob', 'contact', 'employement_type',
'education', 'work_shift', 'address', 'doj', 'salary', 'usertype'), show='headings',
yscrollcommand=vertical_scrollbar.set, xscrollcommand=horizontal_scrollbar.set)
# Configuring the scrollbar behavior for the treeview
horizontal_scrollbar.pack(side=BOTTOM, fill=X)
vertical_scrollbar.pack(side=RIGHT, fill=Y, pady=(10,0))
horizontal_scrollbar.config(command=treeview.xview)
vertical_scrollbar.config(command=treeview.yview)
# Packing the treeview
treeview.pack(pady=(10,0))
# Defining columns and their headings in the treeview
treeview.heading('empId', text='EmpId')
treeview.heading('name', text='Name')
treeview.heading('email', text='Email')
treeview.heading('gender', text='Gender')
treeview.heading('dob', text='Date of Birth')
treeview.heading('contact', text='Contact')
treeview.heading('employement_type', text='Employement Type')
treeview.heading('education', text='Education')
treeview.heading('work_shift', text='Work Shift')
treeview.heading('address', text='Address')
treeview.heading('doj', text='Date of Joining')
treeview.heading('salary', text='Salary')
treeview.heading('usertype', text='User Type')
# Setting the width of each column
treeview.column('empId', width=60)
treeview.column('name', width=140)
treeview.column('email', width=180)
treeview.column('gender', width=80)
treeview.column('contact', width=100)
treeview.column('dob', width=100)
treeview.column('employement_type', width=120)
treeview.column('education', width=120)
treeview.column('work_shift', width=100)
treeview.column('address', width=200)
treeview.column('doj', width=100)
treeview.column('salary', width=140)
treeview.column('usertype', width=120)
treeview_data() # show all the employees information in mysql
# Frame to hold employee details form (input fields)
detail_frame = Frame(employee_frame, bg='white')
detail_frame.place(x=20, y=280)
# Labels and Entry fields for employee details (e.g., EmpId, Name, Email, etc.)
empId_label = Label(detail_frame, text='EmpId', font=('times new roman', 12), bg='white')
empId_label.grid(row=0, column=0, padx=20, pady=10, sticky='w')
empId_entry = Entry(detail_frame, font=('times new roman', 12), bg='lightyellow')
empId_entry.grid(row=0, column=1, padx=20, pady=10)
name_label = Label(detail_frame, text='Name', font=('times new roman', 12), bg='white')
name_label.grid(row=0, column=2, padx=20, pady=10, sticky='w')
name_entry = Entry(detail_frame, font=('times new roman', 12), bg='lightyellow')
name_entry.grid(row=0, column=3, padx=20, pady=10)
email_label = Label(detail_frame, text='Email', font=('times new roman', 12), bg='white')
email_label.grid(row=0, column=4, padx=20, pady=10, sticky='w')
email_entry = Entry(detail_frame, font=('times new roman', 12), bg='lightyellow')
email_entry.grid(row=0, column=5, padx=20, pady=10)
gender_label = Label(detail_frame, text='Gender', font=('times new roman', 12), bg='white')
gender_label.grid(row=1, column=0, padx=20, pady=10, sticky='w')
# Combobox for selecting gender
gender_combobox = ttk.Combobox(detail_frame, values=('Male', 'Female'), font=('times new roman', 12), width=18, state='readonly')
gender_combobox.set('Select Gender')
gender_combobox.grid(row=1, column=1)
dob_label = Label(detail_frame, text='Date of Birth', font=('times new roman', 12), bg='white')
dob_label.grid(row=1, column=2, padx=20, pady=10, sticky='w')
# DateEntry widget for selecting the Date of Birth
dob_date_entry = DateEntry(detail_frame, width=18, font=('times new roman', 12), state='readonly', date_pattern='dd/mm/yyyy')
dob_date_entry.grid(row=1, column=3)
contact_label = Label(detail_frame, text='Contact', font=('times new roman', 12), bg='white')
contact_label.grid(row=1, column=4, padx=20, pady=10, sticky='w')
contact_entry = Entry(detail_frame, font=('times new roman', 12), bg='lightyellow')
contact_entry.grid(row=1, column=5, padx=20, pady=10)
# Employment Type selection
employement_type_label = Label(detail_frame, text='Employement Type', font=('times new roman', 12), bg='white')
employement_type_label.grid(row=2, column=0, padx=20, pady=10, sticky='w')
employement_type_combobox = ttk.Combobox(detail_frame, values=('Full Time', 'Part Time', 'Casual', 'Contract', 'Intern'), font=('times new roman', 12), width=18, state='readonly')
employement_type_combobox.set('Select Type')
employement_type_combobox.grid(row=2, column=1)
# Education selection
education_label = Label(detail_frame, text='Education', font=('times new roman', 12), bg='white')
education_label.grid(row=2, column=2, padx=20, pady=10, sticky='w')
education_options = ["B.Tech", "B.Com", "M.Tech", "M.Com", "B.Sc", "M.Sc", "BBA", "MBA", "LLB", "LLM", "B.Arch", "M.Arch"]
education_combobox = ttk.Combobox(detail_frame, values=education_options, font=('times new roman', 12), width=18, state='readonly')
education_combobox.set('Select Education')
education_combobox.grid(row=2, column=3)
# Work shift selection
work_shift_label = Label(detail_frame, text='Work Shift', font=('times new roman', 12), bg='white')
work_shift_label.grid(row=2, column=4, padx=20, pady=10, sticky='w')
work_shift_combobox = ttk.Combobox(detail_frame, values=('Morning', 'Evening', 'Night'), font=('times new roman', 12), width=18, state='readonly')
work_shift_combobox.set('Select Work Shift')
work_shift_combobox.grid(row=2, column=5)
# Address entry (multiline text widget)
address_label = Label(detail_frame, text='Address', font=('times new roman', 12), bg='white')
address_label.grid(row=3, column=0, padx=20, pady=10, sticky='w')
address_text = Text(detail_frame, width=20, height=3, font=('times new roman', 12), bg='lightyellow')
address_text.grid(row=3, column=1, rowspan=2)
# Date of Joining entry
doj_label = Label(detail_frame, text='Date of Joining', font=('times new roman', 12), bg='white')
doj_label.grid(row=3, column=2, padx=20, pady=10, sticky='w')
doj_date_entry = DateEntry(detail_frame, width=18, font=('times new roman', 12), state='readonly', date_pattern='dd/mm/yyyy')
doj_date_entry.grid(row=3, column=3)
# User Type selection
usertype_label = Label(detail_frame, text='User Type', font=('times new roman', 12), bg='white')
usertype_label.grid(row=4, column=2, padx=20, pady=10, sticky='w')
usertype_combobox = ttk.Combobox(detail_frame, values=('Admin', 'Employee'), font=('times new roman', 12), width=18, state='readonly')
usertype_combobox.set('Select User Type')
usertype_combobox.grid(row=4, column=3)
# Salary entry
salary_label = Label(detail_frame, text='Salary', font=('times new roman', 12), bg='white')
salary_label.grid(row=3, column=4, padx=20, pady=10, sticky='w')
salary_entry = Entry(detail_frame, font=('times new roman', 12), bg='lightyellow')
salary_entry.grid(row=3, column=5, padx=20, pady=10)
# Password entry for user creation
password_label = Label(detail_frame, text='Password', font=('times new roman', 12), bg='white')
password_label.grid(row=4, column=4, padx=20, pady=10, sticky='w')
password_entry = Entry(detail_frame, font=('times new roman', 12), bg='lightyellow')
password_entry.grid(row=4, column=5, padx=20, pady=10)
# Frame for Add, Update, Delete, and Clear buttons
button_frame = Frame(employee_frame, bg='white')
button_frame.place(x=200, y=520)
# Add Button
add_button = Button(button_frame, text='Add', font=('times new roman', 12), width=10, cursor='hand2', fg='white', bg='#0F4D7D', command=lambda: add_employee(empId_entry.get(), name_entry.get(), email_entry.get(), gender_combobox.get(),
dob_date_entry.get(), contact_entry.get(), employement_type_combobox.get(),
education_combobox.get(), work_shift_combobox.get(), address_text.get(1.0, END),
doj_date_entry.get(), salary_entry.get(), usertype_combobox.get(), password_entry.get()))
add_button.grid(row=0, column=0, padx=20)
# Update Button
update_button = Button(button_frame, text='Update', font=('times new roman', 12), width=10, cursor='hand2', fg='white', bg='#0F4D7D', command= lambda: update_employee(empId_entry.get(), name_entry.get(), email_entry.get(), gender_combobox.get(),
dob_date_entry.get(), contact_entry.get(), employement_type_combobox.get(),
education_combobox.get(), work_shift_combobox.get(), address_text.get(1.0, END),
doj_date_entry.get(), salary_entry.get(), usertype_combobox.get(), password_entry.get()))
update_button.grid(row=0, column=1, padx=20)
# Delete Button
delete_button = Button(button_frame, text='Delete', font=('times new roman', 12), width=10, cursor='hand2', fg='white', bg='#0F4D7D', command=lambda: delete_employee(empId_entry.get()))
delete_button.grid(row=0, column=2, padx=20)
# Clear Button to reset the form
clear_button = Button(button_frame, text='Clear', font=('times new roman', 12), width=10, cursor='hand2', fg='white', bg='#0F4D7D', command= lambda: clear_fields(empId_entry, name_entry, email_entry, dob_date_entry, gender_combobox,
contact_entry, employement_type_combobox, education_combobox, work_shift_combobox,
address_text, doj_date_entry, salary_entry, usertype_combobox, password_entry, True))
clear_button.grid(row=0, column=3, padx=20)
treeview.bind('<ButtonRelease-1>',lambda event: select_data(event, empId_entry, name_entry, email_entry, dob_date_entry, gender_combobox,contact_entry,
employement_type_combobox, education_combobox, work_shift_combobox,address_text, doj_date_entry,
salary_entry, usertype_combobox, password_entry)) # left click any row select_data function will be called for that