def print_format(receipt_no):
print()
print(f"ReceiptNo.: {receipt_no}")
cur.execute(f"select * from Sale where ReceiptNo={receipt_no}")
_, name, count, date, time, cp, sp = cur.fetchall()[0]
print(f"Customer Name: {name}")
print(f"No. of medicines: {count}")
print(f"Date: {date}")
print(f"Time: {time}")
print(f"Cost Price: {cp}")
print(f"Selling Price: {sp}")
print("Other info:")
query = f"Select * from Sales.t{receipt_no}"
columns = actions.get_columns(f"Sales.t{receipt_no}")
a = 1
bm = actions.get_values("MedicineInfo", "Barcode")
for i in actions.get_values(f"Sales.t{receipt_no}", "Barcode"):
if i not in bm:
a = 0
if a:
query = f"""select t.SNo, t.Barcode, m.Name, m.Type, m.Composition, t.CostPrice, t.SellingPrice, t.Profit
from t{receipt_no} as t, MedicalStore.MedicineInfo as m where t.Barcode=m.barcode"""
columns = ("SNo", "Barcode", "Name", "Type", "Composition",
"CostPrice", "SellingPrice", "Profit")
cur.execute("use Sales")
cur.execute(query)
actions.format_print(columns, cur.fetchall())
cur.execute("Use MedicalStore")
def insert(bar=None):
print()
if bar is None:
bar = input("Enter barcode of medicine: ")
bars = actions.get_values("MedicineInfo", "Barcode")
while not bar.isdigit() or bar in bars:
if not bar.isdigit():
bar = input(
" Barcode should be an integer only! Enter again: ")
else:
bar = input("This barcode is already taken! Enter another: ")
name = input("Enter name of medicine: ")
while name == '':
name = input("Please enter a name: ")
m_type = input("Enter type (eg: antipyretic, analgesic etc.): ")
while m_type == "":
m_type = input("Please enter a medicine type: ")
composition = input("Enter composition: ")
if composition == '':
composition = "NULL"
create_record(bar, name, m_type, composition)
print("Inserted record successfully...")
def delete():
receipt = input("Enter receipt no. of record to be deleted: ")
while not receipt.isdigit():
receipt = input("Receipt no. should be an integer! Enter Again: ")
receipts = actions.get_values("Sale", "ReceiptNo")
if receipt not in receipts:
print(f"Receipt No. {receipt} is not there! Skipping delete.")
else:
cur.execute(
f"select SellingPrice, CostPrice, SaleDate from Sale where ReceiptNo={receipt}"
)
sp, cp, date = cur.fetchall()[0]
date = str(date.isoformat())
month = months[int(date.split('-')[1])]
year = date.split("-")[0]
management.update_record(month, year, -int(cp), -int(sp))
cur.execute(f"delete from Sale where ReceiptNo='{receipt}'")
cur.execute("use Sales")
cur.execute(f"drop table t{receipt}")
cur.execute("use MedicalStore")
conn.commit()
print("Deleted record successfully...")
def insert():
print("Enter records")
receipt_no = input("Receipt no.: ")
receipt_nos = actions.get_values("Sale", "ReceiptNo")
while not receipt_no.isdigit() or receipt_no in receipt_nos:
if not receipt_no.isdigit():
receipt_no = input(
"Receipt no. should be an integer only! Enter again: ")
else:
receipt_no = input(
"This receipt no. is already taken! Enter another: ")
cust = input("Customer Name: ")
while cust == '':
cust = input("Customer name cannot be empty! Enter a name: ")
count = input("No. of medicines sold: ")
while not count.isdigit():
count = input("No. of medicines should be integer! Enter again: ")
date = actions.date()
time = actions.time()
create_record(receipt_no, cust, count, date, time)
print("Created record successfully...")
def create_record(receipt_no, name, count, date, time):
barcodes = actions.get_values("MedicineInfo", "Barcode")
cur.execute("use Sales")
print("\nNow enter record for each medicine.")
cp, sp = create_table(receipt_no, count, barcodes)
query = f"insert into Sale values('{receipt_no}', '{name}', '{count}', '{date}', '{time}', '{cp}', '{sp}')"
cur.execute("use MedicalStore")
cur.execute(query)
management.update_record(months[int(date.split('-')[1])],
date.split('-')[0], cp, sp)
conn.commit()
def insert():
print()
print("Enter Records")
batch = input("Batch No.: ")
batch_nos = actions.get_values("Stock", "BatchNo")
while not batch.isdigit() or batch in batch_nos:
if not batch.isdigit():
batch = input(
"Batch No. should be an integer only. Please enter again: ")
else:
batch = input("This batch no. is already there! Enter another: ")
bar = input("Barcode of medicine: ")
while not bar.isdigit():
bar = input("Barcode should be an integer only! Please enter again: ")
cp = input("Enter Cost per packet (Rs): ")
while not cp.isdigit():
cp = "Cost should be an integer! Please enter again: "
p_date = input("Purchase date (yyyy-mm-dd): ")
p_date = actions.check_date(p_date)
while not p_date:
p_date = input(
"Your date format is nor correct! Enter again(yyyy-mm-dd): ")
p_date = actions.check_date(p_date)
print(f"Purchase Date: {p_date}")
qty = input("Quantity left (no. of packets): ")
while not qty.isdigit():
qty = input("Quantity should be an integer only! Please enter again: ")
mfg = input("Manufacturing date: ")
mfg = actions.check_date(mfg)
while not mfg:
mfg = input(
"Your date format is nor correct! Enter again(yyyy-mm-dd): ")
mfg = actions.check_date(mfg)
print(f"Manufacturing date: {mfg}")
exp = input("Expiry date: ")
exp = actions.check_date(exp)
while not exp:
exp = input(
"Your date format is nor correct! Enter again(yyyy-mm-dd): ")
exp = actions.check_date(exp)
print(f"Expiry date: {exp}")
create_record(batch, bar, cp, p_date, qty, mfg, exp)
print("Created record successfully...")
def update():
rec = input("Enter receipt no. of record to be updated: ")
receipts = actions.get_values("Sale", "ReceiptNo")
while rec not in receipts:
rec = input("Receipt no. you entered is not in table! Enter again: ")
print("Enter new records. Leave blank to not update")
receipt = input("Receipt No.: ")
while receipt != '' and not receipt.isdigit():
receipt = input("Receipt no. should be an integer! Enter again: ")
if receipt != '':
cur.execute(
f"update Sale set ReceiptNo='{receipt}' where ReceiptNo = '{rec}'")
cur.execute(f"alter table Sales.t{rec} rename Sales.t{receipt}")
print("Updated successfully...")
rec = receipt
name = input("Customer Name: ")
if name != '':
cur.execute(
f"update Sale set CustomerName='{name}' where ReceiptNo = '{rec}'")
print("Updated successfully...")
count = input("No. of medicines sold: ")
while count != '' and not count.isdigit():
count = input("No. of medicines should be an integer! Enter again: ")
if count != '':
cur.execute(
f"update Sale set TypeCount='{count}' where ReceiptNo = '{rec}'")
print("Updated successfully...")
date = input("Date (yyyy-mm-dd): ")
cur.execute(f"select SaleDate from Sale where ReceiptNo='{rec}'")
old_date = cur.fetchall()[0][0]
dt = actions.check_date(date)
while not dt and date != '':
date = input(
"Date you entered is not of correct format! Enter again: ")
dt = actions.check_date(date)
if date != '':
print(f"Date: {dt}")
cur.execute(
f"update Sale set SaleDate='{dt}' where ReceiptNo = '{rec}'")
print("Updated successfully...")
time = input("Time (hh:mm:ss)")
while not actions.check_time(time) and time != '':
time = input(
"Time you entered is not of correct format! Enter again: ")
if time != '':
cur.execute(
f"update Sale set SaleTime='{time}' where ReceiptNo = '{rec}'")
print("Updated successfully...")
conn.commit()
ch = input("Enter 'y' if you want to change other info: ").lower()
receipt = rec
if ch == 'y':
total_cp = total_sp = 0
while True:
bar = input(
"Enter barcode of medicine you want to update. Leave empty to quit: "
).lower()
cur.execute("use Sales")
barcodes = actions.get_values(f"t{rec}", "Barcode")
while bar not in barcodes and bar != '':
bar = input(
"Barcode you entered is not in table! Enter again: ")
if bar == '':
break
print("\nEnter new records. Leave empty to not update.")
barcode = input("Barcode: ")
while not barcode.isdigit() and barcode != '':
barcode = input("Barcode should be an integer! Enter again: ")
if barcode != '':
cur.execute(
f"update Sales.t{rec} set Barcode='{barcode}' where Barcode={bar}"
)
print("Updated successfully...")
if barcode == '':
barcode = bar
cp = input("Cost Price: ")
while not cp.isdigit() and cp != '':
cp = input("Cost Price should be an integer! Enter again: ")
if cp != '':
cur.execute(
f"select CostPrice from t{rec} where Barcode={barcode}")
old_cp = cur.fetchall()[0][0]
cur.execute(
f"update t{rec} set CostPrice='{cp}' where Barcode={barcode}"
)
print("Updated successfully...")
total_cp += int(cp) - int(old_cp)
sp = input("Selling Price: ")
while not sp.isdigit() and sp != '':
sp = input("Selling Price should be an integer! Enter again: ")
if sp != '':
cur.execute(
f"select SellingPrice from t{rec} where Barcode={barcode}")
old_sp = cur.fetchall()[0][0]
cur.execute(
f"update t{receipt} set SellingPrice='{sp}' where Barcode={barcode}"
)
print("Updated successfully...")
total_sp += int(sp) - int(old_sp)
print("Record Updated...")
cur.execute("use MedicalStore")
month = months[int(str(old_date).split('-')[1])]
year = str(old_date).split('-')[0]
management.update_record(month, year, total_cp, total_sp)
def view():
print()
print("Viewing Options:")
print("2: All data")
print("3: One record using Receipt No.")
print("4: many records using Receipt No.")
print("5: All Receipt Numbers")
print("6: Receipt numbers by condition")
print("7: All costumers")
print("0: Go to home")
print("1: Go to Medicine Information")
ch = input()
while ch not in '01234567' or len(ch) != 1:
ch = input("Invalid choice. Enter again: ")
num = 0
if ch == '0':
return '0'
elif ch == '1':
return '1'
elif ch == '2':
values = actions.get_values("Sale", "ReceiptNo")
for i in values:
print_format(i)
if len(values) == 0:
print("No records!!")
elif ch == '3':
num = 1
elif ch == '4':
num = input("How many records do tou want to view: ")
while not num.isdigit():
num = input("No. of records should be integer only! Enter again: ")
elif ch == '5':
cur.execute("select ReceiptNo from Sale")
actions.format_print([
"ReceiptNo",
], cur.fetchall())
elif ch == '6':
print("All columns are")
print(actions.get_columns("Sale"))
condition = input(
'Enter condition(<column_name><operator>"<value>"): ')
while True:
try:
cur.execute(f"select 1+2 from Sale where {condition}")
cur.fetchall()
break
except Exception as e:
print(e)
condition = input(
'Your condition had above error! Enter again(<column_name><operator>"<value>"): '
)
try:
cur.execute(f"select ReceiptNo from Sale where {condition}")
actions.format_print(["ReceiptNo"], cur.fetchall())
except Exception as e:
print("Your condition had an error!!")
print(e)
traceback.print_exc()
elif ch == '7':
actions.format_print(actions.get_columns("Sale"),
actions.show_all("Sale"))
records = []
receipts = actions.get_values("Sale", "ReceiptNo")
for i in range(int(num)):
rec = input(f"Enter record{i + 1} Receipt No.: ")
while rec not in receipts or not rec.isdigit():
if not rec.isdigit():
rec = input("Receipt No. should be an integer! Enter again: ")
else:
rec = input(f"Receipt No.: {rec} is not there! Enter again: ")
records.append(rec)
for i in records:
print_format(i)