def getTaxRate(self):
self.normalize()
if self.badSetup:
log(self.ID, "Employee is invalid")
return
if not self.exists:
log(self.ID, "Employee does not exist yet, did you mean to create?")
return
DB.execute(Query.FIND(Entity.STATE, self.state, "tax_rate"))
return float(DB.result()[0][0])
def create(self):
self.normalize()
if self.badSetup:
log(self.ID, "Dependent is invalid")
return
if self.exists:
log(self.ID, "Dependent already created, did you mean to update?")
return
DB.execute(
Query.CREATE(Entity.DEPENDENT, self.d_id, self.first_name,
self.last_name, self.ssn, self.benefitSelection))
if DB.result():
log(self.ID, "Created Dependent {}".format(self.d_id))
def signIn(first_name: str, e_id: str):
global currentTitle, currentUser, currentEmployee
""" Sign in given first name and e_id of employee """
DB.execute(Query.SELECT_WHERE(Entity.EMPLOYEE, "first_name='{}' and e_id='{}'".format(first_name, e_id), "job_title"))
try:
currentTitle = DataType.JobTitle(DB.result()[0][0])
currentUser = first_name
if currentTitle:
log(ID, getString())
currentEmployee = Employee(e_id)
except IndexError:
log(ID, "Failed to Sign In")
currentTitle = None
currentUser = None
currentEmployee = None
def getInsurnacePlanCost(self):
self.normalize()
if self.badSetup:
log(self.ID, "Employee is invalid")
return
if not self.exists:
log(self.ID, "Employee does not exist yet, did you mean to create?")
# return
DB.execute(
Query.FIND(
Entity.INSURNACE_PLAN,
self.insurancePlan,
"employer_cost_for_family" if len(self.Dependents) > 0 else "employer_cost_for_individual",
)
)
return float(DB.result()[0][0])
def __init__(
self,
d_id: str = None,
first_name: str = None,
last_name: str = None,
ssn: str = None,
benefitSelection: DataType.BenefitSelection = None,
):
""" Initialize a new Dependent to be created or updated"""
self.d_id = d_id
DB.execute(
Query.SELECT_WHERE(Entity.DEPENDENT, "d_id='{}'".format(d_id),
"*"))
self.exists = DB.result()
if self.exists != None:
if len(self.exists) == 0:
self.exists = None
if self.exists and first_name:
log(
self.ID,
"Warning: Dependent already exists but options parameters were still passed"
)
if not self.exists:
if not d_id or not first_name or not last_name or not ssn or not benefitSelection:
log(self.ID, "Dependent does not exist")
self.badSetup = True
self.exists = False
return
self.d_id = d_id
self.first_name = first_name
self.last_name = last_name
self.ssn = ssn
self.benefitSelection = benefitSelection.value
else:
log(self.ID, "Dependent found")
self.exists = self.exists[0]
self.first_name = self.exists[1]
self.last_name = self.exists[2]
self.ssn = self.exists[3]
self.benefitSelection = self.exists[4]
self.normalize()
if self.badSetup:
return
def update(self):
self.normalize()
if self.badSetup:
log(self.ID, "Dependent is invalid")
return
if not self.exists:
log(self.ID,
"Dependent does not exist yet, did you mean to create?")
return
_first_name = "first_name='{}'".format(self.first_name)
_last_name = "last_name='{}'".format(self.last_name)
_ssn = "ssn='{}'".format(self.ssn)
_benefitSelection = "benefitSelection='{}'".format(
self.benefitSelection)
DB.execute(
Query.UPDATE_SINGLE(Entity.DEPENDENT, self.d_id, _first_name,
_last_name, _ssn, _benefitSelection))
def expenseReport():
repo = ""
employeeCount = 0
employeeWages = 0
bonusCount = 0
bonuses = 0
f01k = 0
social = 0
premium = 0
DB.execute(Query.SELECT(Entity.EMPLOYEE, "e_id"))
for ID in DB.result():
e = Employee(ID[0])
wage = e.rate * e.hours
if e.salary_type == DataType.Salary.SALARY.value:
wage = e.rate
bonuses += rnd(0, int(e.rate)) + e.rate % 1
bonusCount += 1
social += 0.075 * wage
employeeWages += wage
f01k += wage * min(float(e.F01k_deduction) / 100.0, 0.07)
employeeCount += 1
premium += e.getInsurnacePlanCost() / 2
repo += """
---------[ COMPANY EXPENSE REPORT ]---------
Employee Wages @ {} : ${}
Bonuses @ {} : ${}
401K Contributions : ${}
Social Security Contribution @ {} : ${}
Insurance Premiums : ${}
""".format(employeeCount, employeeWages, bonusCount, bonuses,
round(f01k, 2), bonusCount, social, premium)
return repo
def create(self): # TODO: use prepared statements instead
self.normalize()
if self.badSetup:
log(self.ID, "Employee is invalid")
return
if self.exists:
log(self.ID, "Employee already created, did you mean to update?")
return
DB.execute(
Query.CREATE(
Entity.EMPLOYEE,
self.e_id,
self.first_name,
self.last_name,
self.ssn,
self.job_title,
self.salary_type,
self.insurancePlan,
self.email,
self.country,
self.state,
self.street_name,
self.postal_code,
self.F01k_deduction,
self.rate,
self.hours,
)
)
if DB.result():
log(self.ID, "Created employee {}".format(self.e_id))
for d in self.Dependents:
DB.execute(Query.CREATE(Relation.HAS, self.e_id, d))
for p in self.PhoneNumbers:
DB.execute(Query.CREATE(Multivalue.EMPLOYEE_PHONE, self.e_id, p))
for b in self.Benefits:
DB.execute(Query.CREATE(Multivalue.EMPLOYEE_BENEFIT_SELECTION, self.e_id, b))
def __init__(
self,
e_id: str,
first_name: str = None,
last_name: str = None,
ssn: str = None,
job_title: DataType.JobTitle = None,
salary_type: DataType.Salary = None,
insurancePlan: str = None,
email: str = None,
country: str = None,
state: str = None,
street_name: str = None,
postal_code: int = None,
F01k_deduction: int = None,
rate: float = None,
hours: int = None,
):
""" Initialize a new Employee to be created or updated"""
self.e_id = e_id
DB.execute(Query.SELECT_WHERE(Entity.EMPLOYEE, "e_id='{}'".format(e_id), "*"))
self.exists = DB.result()
if self.exists != None:
if len(self.exists) == 0:
self.exists = None
if self.exists and first_name:
log(self.ID, "Warning: Employee already exists but options parameters were still passed")
if not self.exists:
if (
not first_name
or not last_name
or not ssn
or not job_title
or not salary_type
or not insurancePlan
or not email
or not country
or not state
or not street_name
or not postal_code
or not F01k_deduction
or not rate
or not hours
):
log(self.ID, "Employee does not exist")
self.badSetup = True
self.exists = False
return
self.first_name = first_name
self.last_name = last_name
self.ssn = ssn
self.job_title = job_title.value
self.salary_type = salary_type.value
self.insurancePlan = insurancePlan
self.email = email
self.country = country
self.state = state
self.street_name = street_name
self.postal_code = postal_code
self.F01k_deduction = F01k_deduction
self.rate = rate
self.hours = hours
if type(postal_code) != int or type(F01k_deduction) != int or type(rate) != float or type(hours) != int:
log(self.ID, "Bad parameter types")
self.badSetup = True
self.exists = False
else:
log(self.ID, "Employee found")
self.exists = self.exists[0]
self.first_name = self.exists[1]
self.last_name = self.exists[2]
self.ssn = self.exists[3]
self.job_title = self.exists[4]
self.salary_type = self.exists[5]
self.insurancePlan = self.exists[6]
self.email = self.exists[7]
self.country = self.exists[8]
self.state = self.exists[9]
self.street_name = self.exists[10]
self.postal_code = self.exists[11]
self.F01k_deduction = self.exists[12]
self.rate = self.exists[13]
self.hours = self.exists[14]
self.normalize()
DB.execute(Query.FIND(Entity.STATE, self.state))
found = DB.result()
if not found:
log(self.ID, "Employee State {} not found".format(self.state))
self.badSetup = True
self.exists = False
DB.execute(Query.FIND(Entity.INSURNACE_PLAN, self.insurancePlan))
found = DB.result()
if not found:
log(self.ID, "Employee State {} not found".format(self.insurancePlan))
self.badSetup = True
self.exists = False
if self.badSetup:
return
DB.execute(Query.FIND(Relation.HAS, self.e_id))
res = DB.result()
for d in res:
self.Dependents.add(str(d[1]))
DB.execute(Query.FIND(Multivalue.EMPLOYEE_PHONE, self.e_id))
res = DB.result()
for p in res:
self.PhoneNumbers.add(str(p[1]))
DB.execute(Query.FIND(Multivalue.EMPLOYEE_BENEFIT_SELECTION, self.e_id))
res = DB.result()
for b in res:
self.Benefits.add(str(b[1]))
def update(self):
self.normalize()
if self.badSetup:
log(self.ID, "Employee is invalid")
return
if not self.exists:
log(self.ID, "Employee does not exist yet, did you mean to create?")
return
_first_name = "first_name='{}'".format(self.first_name)
_last_name = "last_name='{}'".format(self.last_name)
_ssn = "ssn='{}'".format(self.ssn)
_job_title = "job_title='{}'".format(self.job_title)
_salary_type = "salary_type='{}'".format(self.salary_type)
_insurancePlan = "insurancePlan='{}'".format(self.insurancePlan)
_email = "email='{}'".format(self.email)
_country = "country='{}'".format(self.country)
_state = "state='{}'".format(self.state)
_street_name = "street_name='{}'".format(self.street_name)
_postal_code = "postal_code='{}'".format(self.postal_code)
_F01k_deduction = "F01k_deduction='{}'".format(self.F01k_deduction)
_rate = "rate='{}'".format(self.rate)
_hours = "hours='{}'".format(self.hours)
DB.execute(
Query.UPDATE_SINGLE(
Entity.EMPLOYEE,
self.e_id,
_first_name,
_last_name,
_ssn,
_job_title,
_salary_type,
_insurancePlan,
_email,
_country,
_state,
_street_name,
_postal_code,
_F01k_deduction,
_rate,
_hours,
)
)
# TODO: Only modify tables that actually changed
DB.execute(Query.DELETE(Relation.HAS, "e_id='{}'".format(self.e_id)))
DB.execute(Query.DELETE(Multivalue.EMPLOYEE_PHONE, "e_id='{}'".format(self.e_id)))
DB.execute(Query.DELETE(Multivalue.EMPLOYEE_BENEFIT_SELECTION, "e_id='{}'".format(self.e_id)))
for d in self.Dependents:
DB.execute(Query.CREATE(Relation.HAS, self.e_id, d))
for p in self.PhoneNumbers:
DB.execute(Query.CREATE(Multivalue.EMPLOYEE_PHONE, self.e_id, p))
for b in self.Benefits:
DB.execute(Query.CREATE(Multivalue.EMPLOYEE_BENEFIT_SELECTION, self.e_id, b))
def __init__(self, master=None, app=None):
global currentEmployee
currentEmployee = Auth.getCurrentEmployee()
self.master = master
self.app = app
self.frame = tk.Frame(self.master)
self.master = master
self.app = app
self.frame = tk.Frame(self.master)
self.topLabel = tk.Label(self.frame, text="Edit User Page")
self.topLabel.grid(row=0, column=1)
titles = list()
payTypes = list()
insurances = list()
states = list()
for t in list(DataType.JobTitle):
titles.append(t.value)
for s in list(DataType.Salary):
payTypes.append(s.value)
DB.execute(DB.Query.SELECT(DB.Entity.INSURNACE_PLAN, "*"))
for i in DB.result():
insurances.append(i[0])
DB.execute(DB.Query.SELECT(DB.Entity.STATE, "*"))
for s in DB.result():
states.append(s[0])
self.title = tk.StringVar(self.frame)
self.title.set(currentEmployee.job_title)
self.payType = tk.StringVar(self.frame)
self.payType.set(currentEmployee.salary_type)
self.insurance = tk.StringVar(self.frame)
self.insurance.set(currentEmployee.insurancePlan)
self.state = tk.StringVar(self.frame)
self.state.set(currentEmployee.state)
tk.Label(self.frame, text="eID").grid(row=1, column=0)
self.e_id = tk.Entry(self.frame)
self.e_id.insert(0, currentEmployee.e_id)
self.e_id.grid(row=1, column=1)
self.e_id.configure(state="disable")
tk.Label(self.frame, text="First name").grid(row=2, column=0)
self.first_name = tk.Entry(self.frame)
self.first_name.insert(0, currentEmployee.first_name)
self.first_name.grid(row=2, column=1)
tk.Label(self.frame, text="Last name").grid(row=3, column=0)
self.last_name = tk.Entry(self.frame)
self.last_name.insert(0, currentEmployee.last_name)
self.last_name.grid(row=3, column=1)
tk.Label(self.frame, text="ssn").grid(row=4, column=0)
self.ssn = tk.Entry(self.frame)
self.ssn.insert(0, currentEmployee.ssn)
self.ssn.grid(row=4, column=1)
tk.Label(self.frame, text="Job title").grid(row=5, column=0)
jobTitleOptionMenu = tk.OptionMenu(self.frame, self.title, *titles)
jobTitleOptionMenu.grid(row=5, column=1)
if currentEmployee.job_title != "ADMIN":
jobTitleOptionMenu.configure(state="disable")
tk.Label(self.frame, text="Salary type").grid(row=6, column=0)
salaryOptionMenu = tk.OptionMenu(self.frame, self.payType, *payTypes)
salaryOptionMenu.grid(row=6, column=1)
if currentEmployee.job_title != "ADMIN":
salaryOptionMenu.configure(state="disable")
tk.Label(self.frame, text="Insurance plan").grid(row=7, column=0)
tk.OptionMenu(self.frame, self.insurance, *insurances).grid(row=7, column=1)
tk.Label(self.frame, text="Email").grid(row=8, column=0)
self.email = tk.Entry(self.frame)
self.email.insert(0, currentEmployee.email)
self.email.grid(row=8, column=1)
tk.Label(self.frame, text="Country").grid(row=9, column=0)
self.country = tk.Entry(self.frame)
self.country.insert(0, currentEmployee.country)
self.country.grid(row=9, column=1)
tk.Label(self.frame, text="State").grid(row=10, column=0)
tk.OptionMenu(self.frame, self.state, *states).grid(row=10, column=1)
tk.Label(self.frame, text="Street Name").grid(row=11, column=0)
self.street_name = tk.Entry(self.frame)
self.street_name.insert(0, currentEmployee.street_name)
self.street_name.grid(row=11, column=1)
tk.Label(self.frame, text="Postal Code").grid(row=12, column=0)
self.postal_code = tk.Entry(self.frame)
self.postal_code.insert(0, currentEmployee.postal_code)
self.postal_code.grid(row=12, column=1)
tk.Label(self.frame, text="401k Deduction").grid(row=13, column=0)
self.F01k_deduction = tk.Entry(self.frame)
self.F01k_deduction.insert(0, currentEmployee.F01k_deduction)
self.F01k_deduction.grid(row=13, column=1)
tk.Label(self.frame, text="Rate").grid(row=14, column=0) # TODO: change label based on hourly or salaried
self.rate = tk.Entry(self.frame)
self.rate.insert(0, currentEmployee.rate)
self.rate.grid(row=14, column=1)
if currentEmployee.job_title != "ADMIN":
self.rate.configure(state="disable")
tk.Label(self.frame, text="Hours").grid(row=15, column=0) # TODO: change label based on hourly or salaried
self.hours = tk.Entry(self.frame)
self.hours.insert(0, currentEmployee.hours)
self.hours.grid(row=15, column=1)
if currentEmployee.job_title == "EMPLOYEE" and currentEmployee.salary_type == "SALARY":
self.hours.configure(state="disable")
self.DependentsTreeview = ListboxEditable(self.frame, list(currentEmployee.Dependents), 17, 0)
self.PhoneNumbersTreeview = ListboxEditable(self.frame, list(currentEmployee.PhoneNumbers), 17, 1)
self.BenefitsTreeview = ListboxEditable(self.frame, list(currentEmployee.Benefits), 17, 2)
tk.Button(self.frame, text="Add Dependent", command=self.DependentsTreeview.addPlaceRow).grid(row=16, column=0)
tk.Button(self.frame, text="Add Number", command=self.PhoneNumbersTreeview.addPlaceRow).grid(row=16, column=1)
tk.Button(self.frame, text="Add Benefit", command=self.BenefitsTreeview.addPlaceRow).grid(row=16, column=2)
self.DependentsTreeview.placeListBoxEditable()
self.PhoneNumbersTreeview.placeListBoxEditable()
self.BenefitsTreeview.placeListBoxEditable()
tk.Button(self.frame, text="Update", command=self.update).grid(sticky="s")
tk.Button(self.frame, text="Back", command=self.go_back).grid(sticky="s")
def __init__(self, master=None, app=None):
self.master = master
self.app = app
self.frame = tk.Frame(self.master)
tk.Label(self.frame, text="Add User Page").grid(row=0, column=1)
titles = list()
payTypes = list()
insurances = list()
states = list()
for t in list(DataType.JobTitle):
titles.append(t.value)
for s in list(DataType.Salary):
payTypes.append(s.value)
DB.execute(DB.Query.SELECT(DB.Entity.INSURNACE_PLAN, "*"))
for i in DB.result():
insurances.append(i[0])
DB.execute(DB.Query.SELECT(DB.Entity.STATE, "*"))
for s in DB.result():
states.append(s[0])
title = tk.StringVar(self.frame)
title.set(titles[0])
payType = tk.StringVar(self.frame)
payType.set(payTypes[0])
insurance = tk.StringVar(self.frame)
insurance.set(insurances[0])
state = tk.StringVar(self.frame)
state.set(states[0])
tk.Label(self.frame, text="eID").grid(row=1, column=0)
self.e_id = tk.Entry(self.frame)
self.e_id.grid(row=1, column=1)
tk.Label(self.frame, text="First name").grid(row=2, column=0)
self.first_name = tk.Entry(self.frame)
self.first_name.grid(row=2, column=1)
tk.Label(self.frame, text="Last name").grid(row=3, column=0)
self.last_name = tk.Entry(self.frame)
self.last_name.grid(row=3, column=1)
tk.Label(self.frame, text="ssn").grid(row=4, column=0)
self.ssn = tk.Entry(self.frame)
self.ssn.grid(row=4, column=1)
tk.Label(self.frame, text="Job title").grid(row=5, column=0)
tk.OptionMenu(self.frame, title, *titles).grid(row=5, column=1)
tk.Label(self.frame, text="Salary type").grid(row=6, column=0)
tk.OptionMenu(self.frame, payType, *payTypes).grid(row=6, column=1)
tk.Label(self.frame, text="Insurance plan").grid(row=7, column=0)
tk.OptionMenu(self.frame, insurance, *insurances).grid(row=7, column=1)
tk.Label(self.frame, text="Email").grid(row=8, column=0)
self.email = tk.Entry(self.frame)
self.email.grid(row=8, column=1)
tk.Label(self.frame, text="Country").grid(row=9, column=0)
self.country = tk.Entry(self.frame)
self.country.grid(row=9, column=1)
tk.Label(self.frame, text="State").grid(row=10, column=0)
tk.OptionMenu(self.frame, state, *states).grid(row=10, column=1)
tk.Label(self.frame, text="Street Name").grid(row=11, column=0)
self.street_name = tk.Entry(self.frame)
self.street_name.grid(row=11, column=1)
tk.Label(self.frame, text="Postal Code").grid(row=12, column=0)
self.postal_code = tk.Entry(self.frame)
self.postal_code.grid(row=12, column=1)
tk.Label(self.frame, text="401k Deduction").grid(row=13, column=0)
self.F01k_deduction = tk.Entry(self.frame)
self.F01k_deduction.grid(row=13, column=1)
tk.Label(self.frame, text="Rate").grid(row=14, column=0) # TODO: change label based on hourly or salaried
self.rate = tk.Entry(self.frame)
self.rate.grid(row=14, column=1)
tk.Label(self.frame, text="Hours").grid(row=15, column=0) # TODO: change label based on hourly or salaried
self.hours = tk.Entry(self.frame)
self.hours.grid(row=15, column=1)
self.DependentsTreeview = ListboxEditable(self.frame, list(), 17, 0)
self.PhoneNumbersTreeview = ListboxEditable(self.frame, list(), 17, 1)
self.BenefitsTreeview = ListboxEditable(self.frame, list(), 17, 2)
tk.Button(self.frame, text="Add Dependent", command=self.DependentsTreeview.addPlaceRow).grid(row=16, column=0)
tk.Button(self.frame, text="Add Number", command=self.PhoneNumbersTreeview.addPlaceRow).grid(row=16, column=1)
tk.Button(self.frame, text="Add Benefit", command=self.BenefitsTreeview.addPlaceRow).grid(row=16, column=2)
self.DependentsTreeview.placeListBoxEditable()
self.PhoneNumbersTreeview.placeListBoxEditable()
self.BenefitsTreeview.placeListBoxEditable()
def create():
newBoi = Employee(
self.e_id.get(),
self.first_name.get(),
self.last_name.get(),
self.ssn.get(),
DataType.JobTitle(title.get().upper()),
DataType.Salary(payType.get().upper()),
insurance.get(),
self.email.get(),
self.country.get(),
state.get(),
self.street_name.get(),
int(self.postal_code.get()),
int(self.F01k_deduction.get()),
float(self.rate.get()),
int(self.hours.get()),
)
newBoi.Dependents = set(self.DependentsTreeview.getList())
newBoi.PhoneNumbers = set(self.PhoneNumbersTreeview.getList())
newBoi.Benefits = set(self.BenefitsTreeview.getList())
newBoi.create()
def back():
self.go_back()
tk.Button(self.frame, text="Create", command=create).grid(sticky="s")
tk.Button(self.frame, text="Go Back", command=back).grid(sticky="s")
def deleteUser(self):
DB.execute(DB.Query.DELETE(DB.Entity.EMPLOYEE, "e_id='{}'".format(currentEmployee.e_id)))
self.go_back
def checkReport():
biweeklyReport = ""
ssn = ""
biweeklyPay = 0
stateTax = 0
federalTax = 0
socialsPay = 0
medicarePay = 0
F01kPay = 0
insurancePay = 0
finalPay = 0
DB.execute(Query.SELECT(Entity.EMPLOYEE, "e_id"))
# get current employee
e = Auth.getCurrentEmployee()
ssn = e.ssn
if e.salary_type == DataType.Salary.HOURLY.value:
biweeklyPay = round(e.hours * e.rate, 2)
else:
biweeklyPay = round(e.rate / 26, 2)
sTax = biweeklyPay * e.getTaxRate()
stateTax = round(sTax, 2)
if e.rate < 10000:
fTax = biweeklyPay * 0.01
elif e.rate < 40000:
fTax = biweeklyPay * 0.02
elif e.rate < 85000:
fTax = biweeklyPay * 0.04
elif e.rate < 163301:
fTax = biweeklyPay * 0.06
elif e.rate < 207351:
fTax = biweeklyPay * 0.08
else:
fTax = biweeklyPay * 0.09
federalTax = round(fTax, 2)
# SS
if e.salary_type == DataType.Salary.HOURLY.value:
ssPay = biweeklyPay * 0.15
socialsPay = round(ssPay, 2)
else:
ssPay = biweeklyPay * 0.075
socialsPay = round(ssPay, 2)
# company pays other 0.075 in their expense report
# medicare
mcPay = biweeklyPay * 0.025
medicarePay = round(mcPay, 2)
# 401k deduction
F01kPay = round(biweeklyPay * (e.F01k_deduction / 100), 2)
# company matched up to 7%
insurancePay = round(float(e.getInsurnacePlanCost()), 2)
# here
finalPay = round(
biweeklyPay - stateTax - federalTax - socialsPay - medicarePay -
F01kPay - insurancePay, 2)
biweeklyReport += """
---------[ BIWEEKLY CHECK REPORT ]---------
SSN : {}
income no deductions: ${}
state tax: ${}
federal tax: ${}
social security contribution: ${}
medicare contribution: ${}
401k contribution: ${}
insurance cost: ${}
Final pay: ${}
""".format(ssn, biweeklyPay, stateTax, federalTax, socialsPay, medicarePay,
F01kPay, insurancePay, finalPay)
return biweeklyReport