def itemSearch():
query = "SELECT * FROM item WHERE description ILIKE '%{0}%'".format(
searchTerm.get())
cur.execute(query)
rows = cur.fetchall()
for w in findItemFrameB.winfo_children():
w.destroy()
constructItemSearchHeaders()
i = 1
for r in rows:
itemNum = tk.Label(findItemFrameB, text=r[0], bg=bgcolor, fg="black",
font="Times 12", borderwidth=1, relief="solid",
width=10)
itemNum.grid(column=0, row=i)
category = tk.Label(findItemFrameB, text=r[1], bg=bgcolor, fg="black",
font="Times 12", borderwidth=1, relief="solid",
width=20)
category.grid(column=1, row=i)
description = tk.Label(
findItemFrameB, text=r[2], bg=bgcolor, fg="black", font="Times 12",
borderwidth=1, relief="solid", width=50)
description.grid(column=2, row=i)
risk = tk.Label(findItemFrameB, text=r[3], bg=bgcolor, fg="black",
font="Times 12", borderwidth=1, relief="solid",
width=10)
risk.grid(column=3, row=i)
amount = tk.Label(findItemFrameB, text=r[4], bg=bgcolor, fg="black",
font="Times 12", borderwidth=1, relief="solid",
width=10)
amount.grid(column=4, row=i)
i += 1
def search():
cur.execute(
f"SELECT * FROM item WHERE description LIKE '%{searchBar.get()}%'")
rows = cur.fetchall()
for r in rows:
itemNum = tk.Label(b, text="Item#", bg="white", fg="black",
font="Times 12", borderwidth=1, relief="solid",
width=10)
itemNum.grid(column=0, row=0)
category = tk.Label(b, text="Category", bg="white", fg="black",
font="Times 12", borderwidth=1, relief="solid",
width=20)
category.grid(column=1, row=0)
description = tk.Label(b, text="Description", bg="white",
fg="black", font="Times 12", borderwidth=1,
relief="solid", width=50)
description.grid(column=2, row=0)
risk = tk.Label(b, text="Risk Level", bg="white", fg="black",
font="Times 12", borderwidth=1, relief="solid",
width=10)
risk.grid(column=3, row=0)
amount = tk.Label(b, text="Amount", bg="white", fg="black",
font="Times 12", borderwidth=1, relief="solid",
width=10)
amount.grid(column=4, row=0)
def search():
cur.execute(
f"SELECT * FROM item WHERE description ILIKE '%{searchBar.get()}%'")
rows = cur.fetchall()
i = 1
for r in rows:
itemNum = tk.Label(findItemFrameB,
text=r[0],
bg=bgcolor,
fg="black",
font="Times 12",
borderwidth=1,
relief="solid",
width=10)
itemNum.grid(column=0, row=i)
category = tk.Label(findItemFrameB,
text=r[1],
bg=bgcolor,
fg="black",
font="Times 12",
borderwidth=1,
relief="solid",
width=20)
category.grid(column=1, row=i)
description = tk.Label(findItemFrameB,
text=r[2],
bg=bgcolor,
fg="black",
font="Times 12",
borderwidth=1,
relief="solid",
width=50)
description.grid(column=2, row=i)
risk = tk.Label(findItemFrameB,
text=r[3],
bg=bgcolor,
fg="black",
font="Times 12",
borderwidth=1,
relief="solid",
width=10)
risk.grid(column=3, row=i)
amount = tk.Label(findItemFrameB,
text=r[4],
bg=bgcolor,
fg="black",
font="Times 12",
borderwidth=1,
relief="solid",
width=10)
amount.grid(column=4, row=i)
i += 1
def list_expired():
cur.execute(
"SELECT i.item_no, i.category, i.description, p.due_date, i.amount, "
"r.interest_rate FROM item i, pawn_ticket p, risk r, inventory_tag t "
"WHERE i.item_no=t.item_no AND t.ticket_no=p.ticket_no AND "
"i.risk_level=r.risk_level AND p.due_date <= CURRENT_DATE AND "
"p.payment_date IS NULL;")
rows = cur.fetchall()
i = 1
for r in rows:
itemNum = tk.Label(expiredItemFrameB, text=r[0], bg=bgcolor,
fg="black", font="Times 12", borderwidth=1,
relief="solid", width=10)
itemNum.grid(column=0, row=i)
category = tk.Label(expiredItemFrameB, text=r[1], bg=bgcolor,
fg="black", font="Times 12", borderwidth=1,
relief="solid", width=20)
category.grid(column=1, row=i)
description = tk.Label(expiredItemFrameB, text=r[2], bg=bgcolor,
fg="black", font="Times 12", borderwidth=1,
relief="solid", width=40)
description.grid(column=2, row=i)
dueDate = tk.Label(expiredItemFrameB, text=r[3], bg=bgcolor,
fg="black", font="Times 12", borderwidth=1,
relief="solid", width=10)
dueDate.grid(column=3, row=i)
amount = tk.Label(expiredItemFrameB, text=r[4], bg=bgcolor, fg="black",
font="Times 12", borderwidth=1, relief="solid",
width=10)
amount.grid(column=4, row=i)
rate = tk.Label(expiredItemFrameB, text=r[5], bg=bgcolor, fg="black",
font="Times 12", borderwidth=1, relief="solid",
width=10)
rate.grid(column=5, row=i)
i += 1
def search():
try:
int(searchBar.get())
except Exception:
searchBar.configure(text="Enter A Ticket Number")
return
cur.execute(
f"SELECT DISTINCT pt.ticket_no, pt.pawn_date, CONCAT(c.last_name, ', ', c.given_name, ' ', c.middle_initial, '.' ), pt.due_date, CONCAT(c.address, ', ', c.city), c.mobile, c.landline FROM pawn_ticket pt, inventory_tag it, customer c WHERE pt.ticket_no={searchBar.get()} AND pt.ticket_no=it.ticket_no;"
)
rows = cur.fetchone()
if (str(rows) == "None"):
searchBar.configure(text="Cannot Find Ticket")
return
a = 0
for i in recieptDetails:
i.configure(text=rows[a])
a += 1
cur.execute(
f"SELECT DISTINCT i.category, i.description, i.amount, r.interest_rate, (i.amount * r.interest_rate) FROM pawn_ticket pt, inventory_tag it, item i, risk r WHERE pt.ticket_no={searchBar.get()} AND pt.ticket_no=it.ticket_no AND it.item_no=i.item_no AND i.risk_level=r.risk_level;"
)
rows = cur.fetchall()
a = 1
for i in rows:
itemCategory = tk.Label(processPaymentPawnedItems,
text=i[0],
bg=bgcolor,
fg="black",
font="Times 12",
borderwidth=1,
relief="solid",
width=20)
itemCategory.grid(column=0, row=a)
itemDescription = tk.Label(processPaymentPawnedItems,
text=i[1],
bg=bgcolor,
fg="black",
font="Times 12",
borderwidth=1,
relief="solid",
width=35)
itemDescription.grid(column=1, row=a)
itemAmount = tk.Label(processPaymentPawnedItems,
text=i[2],
bg=bgcolor,
fg="black",
font="Times 12",
borderwidth=1,
relief="solid",
width=15)
itemAmount.grid(column=2, row=a)
itemInterestRate = tk.Label(processPaymentPawnedItems,
text=i[3],
bg=bgcolor,
fg="black",
font="Times 12",
borderwidth=1,
relief="solid",
width=15)
itemInterestRate.grid(column=3, row=a)
itemInterest = tk.Label(processPaymentPawnedItems,
text=round(i[4], 2),
bg=bgcolor,
fg="black",
font="Times 12",
borderwidth=1,
relief="solid",
width=15)
itemInterest.grid(column=4, row=a)
a += 1
cur.execute(
f"SELECT COUNT(DISTINCT i.item_no), SUM(DISTINCT (i.amount * (r.interest_rate + 1))), SUM(DISTINCT (i.amount * (r.interest_rate + 1))*0.15), SUM(DISTINCT (i.amount * (r.interest_rate + 1))*1.15) FROM pawn_ticket pt, inventory_tag it, item i, risk r WHERE pt.ticket_no={searchBar.get()} AND pt.ticket_no=it.ticket_no AND it.item_no=i.item_no AND i.risk_level=r.risk_level;"
)
rows = cur.fetchone()
noItems.configure(text=rows[0])
a = 1
for i in recieptPaymentDueDetails:
i.configure(text=round(rows[a], 2))
a += 1
def search():
#erase previous searches
i = 0
for a in regCustomerFrameB.winfo_children():
if i < len(customerAttributes):
i += 1
else:
a.destroy()
#create results for current search
cur.execute(
f"SELECT * FROM customer WHERE last_name ILIKE '%{searchBar.get()}%'")
rows = cur.fetchall()
i = 1
for r in rows:
customerID = tk.Button(regCustomerFrameB,
text=r[0],
bg="white",
fg="black",
font="Times 12",
wraplength=225,
command=customerInfo)
customerID.grid(column=0, row=i)
lastName = tk.Label(regCustomerFrameB,
text=r[1],
bg="white",
fg="black",
font="Times 12",
wraplength=225)
lastName.grid(column=1, row=i)
givenName = tk.Label(regCustomerFrameB,
text=r[2],
bg="white",
fg="black",
font="Times 12",
wraplength=225)
givenName.grid(column=2, row=i)
middleInitial = tk.Label(regCustomerFrameB,
text=r[3],
bg="white",
fg="black",
font="Times 12",
wraplength=225)
middleInitial.grid(column=3, row=i)
address = tk.Label(regCustomerFrameB,
text=r[4],
bg="white",
fg="black",
font="Times 12",
wraplength=225)
address.grid(column=4, row=i)
city = tk.Label(regCustomerFrameB,
text=r[5],
bg="white",
fg="black",
font="Times 12",
wraplength=225)
city.grid(column=5, row=i)
mobile = tk.Label(regCustomerFrameB,
text=r[6],
bg="white",
fg="black",
font="Times 12",
wraplength=225)
mobile.grid(column=6, row=i)
landline = tk.Label(regCustomerFrameB,
text=r[7],
bg="white",
fg="black",
font="Times 12",
wraplength=225)
landline.grid(column=7, row=i)
postalCode = tk.Label(regCustomerFrameB,
text=r[8],
bg="white",
fg="black",
font="Times 12",
wraplength=225)
postalCode.grid(column=8, row=i)
birthDate = tk.Label(regCustomerFrameB,
text=r[9],
bg="white",
fg="black",
font="Times 12",
wraplength=225)
birthDate.grid(column=9, row=i)
i += 1
def customerSearch():
# erase previous searches
i = 0
for a in regCustomerFrameB.winfo_children():
if i < len(customerAttributes):
i += 1
else:
a.destroy()
# create results for current search
cur.execute(
f"SELECT *, CAST((CAST(TO_CHAR(CURRENT_DATE, 'YYYYMMDD') AS INT) - "
f"CAST(TO_CHAR(birth_date, 'YYYYMMDD') AS INT)) AS INT)/10000 \"Age\" "
f"FROM customer WHERE last_name ILIKE '%{searchBar.get()}%' OR "
f"given_name ILIKE '%{searchBar.get()}%'")
rows = cur.fetchall()
i = 1
for r in rows:
customerID = tk.Button(
regCustomerFrameB, text=r[0], bg="white", fg="black",
font="Times 12", wraplength=225,
command=lambda: customerInfo(r[0]))
customerID.grid(column=0, row=i)
lastName = tk.Label(
regCustomerFrameB, text=r[1], bg="white", fg="black",
font="Times 12", wraplength=225)
lastName.grid(column=1, row=i)
givenName = tk.Label(
regCustomerFrameB, text=r[2], bg="white", fg="black",
font="Times 12", wraplength=225)
givenName.grid(column=2, row=i)
middleInitial = tk.Label(
regCustomerFrameB, text=r[3], bg="white", fg="black",
font="Times 12", wraplength=225)
middleInitial.grid(column=3, row=i)
address = tk.Label(
regCustomerFrameB, text=r[4], bg="white", fg="black",
font="Times 12", wraplength=225)
address.grid(column=4, row=i)
city = tk.Label(
regCustomerFrameB, text=r[5], bg="white", fg="black",
font="Times 12", wraplength=225)
city.grid(column=5, row=i)
mobile = tk.Label(
regCustomerFrameB, text=r[6], bg="white", fg="black",
font="Times 12", wraplength=225)
mobile.grid(column=6, row=i)
landline = tk.Label(
regCustomerFrameB, text=r[7], bg="white", fg="black",
font="Times 12", wraplength=225)
landline.grid(column=7, row=i)
postalCode = tk.Label(
regCustomerFrameB, text=r[8], bg="white", fg="black",
font="Times 12", wraplength=225)
postalCode.grid(column=8, row=i)
birthDate = tk.Label(
regCustomerFrameB, text=r[9], bg="white", fg="black",
font="Times 12", wraplength=225)
birthDate.grid(column=9, row=i)
age = tk.Label(
regCustomerFrameB, text=r[10], bg="white", fg="black",
font="Times 12", wraplength=255)
age.grid(column=10, row=i)
i += 1