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 view():
print()
print("View Options: ")
print("2: All data")
print("3: Year wise data")
print("0: Home")
print("1: Management Information")
ch = input("Enter your choice: ")
while ch not in '0123' or len(ch) != 1:
ch = input("Invalid choice! Enter again: ")
if ch == '0':
return '0'
elif ch == '1':
return '1'
elif ch == '2':
actions.format_print(actions.get_columns("Management"),
actions.show_all("Management"))
elif ch == '3':
years = input(
"Enter year range(yyyy-yyyy) Leave empty to include all years: ")
while years != '' and (
len(years) != 9 or years.count('-') != 1 or
not (len(years.split('-')[0]) == len(years.split('-')[1]) == 4)
or not years.replace('-', '0').isdigit()):
years = input(
"Year range you entered is not correct! enter again: ")
if years == '':
cur.execute(
"""select Year, sum(CostPrice), sum(SellingPrice), sum(NetGain),
sum(NetGain)/sum(CostPrice)*100 from Management group by Year"""
)
columns = ("Year", "CostPrice", "SellingPrice", "NetGain",
"NetPercent")
values = cur.fetchall()
else:
query = f"""select Year, sum(CostPrice), sum(SellingPrice), sum(NetGain),
sum(NetGain)/sum(CostPrice)*100 from Management group by Year
having year<= '{years.split('-')[1]}' and year>='{years.split('-')[0]}'"""
cur.execute(query)
values = cur.fetchall()
columns = ("Year", "CostPrice", "SellingPrice", "NetGain",
"NetPercent")
actions.format_print(columns, values)
def delete():
print()
print("Delete Options")
print("2. Delete using barcode")
print("3. Delete using name")
print("4. Delete using condition")
print("0. Home")
print("1. Medicine Information")
ch = input("Enter your choice: ")
while ch not in '01234' or len(ch) != 1:
ch = input("Invalid choice! Please enter again: ")
if ch == '0':
return 0
elif ch == '1':
return '1'
elif ch == '2':
bar = input("Enter barcode of medicine to be deleted: ")
while not bar.isdigit():
bar = input(
"Barcode should be an integer only! Please enter again: ")
actions.delete_record("MedicineInfo", "Barcode", bar)
print("Deleted record successfully...")
elif ch == '3':
name = input("Enter name of medicine: ")
actions.delete_record("MedicineInfo", "Name", name)
print("Deleted record successfully...")
elif ch == '4':
print("All columns are")
print(actions.get_columns("MedicineInfo"))
condition = input(
'Enter condition for deletion(<column_name><operator>"<value>"): ')
try:
actions.delete_by_condition("MedicineInfo", condition)
print("Deleted record(s) successfully...")
except Exception as e:
print(e)
print("Your condition had the above error!")
def view():
print()
print("How many columns do you want to view:")
print("2: All columns, All records")
print("3: All columns, Some records")
print("4: Some columns, All records")
print("5: Some columns, Some records")
print("6: Expired Medicines")
print("0: Go to home")
print("1: Go to Stock Information")
ch = input()
columns_all = actions.get_columns("Stock")
col_dict = {i + 1: columns_all[i] for i in range(len(columns_all))}
while ch not in '0123456' or len(ch) != 1:
ch = input("Invalid choice. Enter again: ")
if ch == '0':
return '0'
elif ch == '1':
return '1'
elif ch == '2':
actions.format_print(columns_all, actions.show_all("Stock"))
elif ch == '3':
print("The columns are:")
print(str(col_dict).lstrip('{').rstrip('}'))
column = input(
"Which column no. do you want to use for record matching: ").lower(
)
while not column.isdigit() or int(column) not in col_dict:
column = input(
"Column no. you entered is not in option. Please enter again: "
).lower()
records = actions.input_rows()
actions.format_print(
columns_all,
actions.search_multiple("Stock", col_dict[int(column)], records))
elif ch == '4':
clm = actions.input_cols("Stock")
actions.format_print(clm, actions.show_columns("Stock", clm))
elif ch == '5':
columns = actions.input_cols("Stock")
column = input(
"Which column no. do you want to use for record matching").lower()
while not column.isdigit() or int(column) not in col_dict:
column = input(
"Column no. you entered is not in option. Please enter again: "
).lower()
records = actions.input_rows()
actions.format_print(
columns,
actions.search_multiple("Stock", col_dict[int(column)], records,
columns))
elif ch == '6':
cur.execute("select * from Stock where Exp<(select sysdate())")
data = cur.fetchall()
actions.format_print(actions.get_columns("Stock"), data)
def search():
print()
print("Search Options:")
print("2: Search using batch no.")
print("3: Search using many fields")
print("4: Search using condition")
print("0: Home")
print("1: Stock information")
ch = input("Enter your choice: ")
columns_all = actions.get_columns("Stock")
col_dict = {i + 1: columns_all[i] for i in range(len(columns_all))}
while ch not in '01234' or len(ch) != 1:
ch = input("Invalid choice. Enter again: ")
if ch == '0':
return '0'
elif ch == '1':
return '1'
elif ch == '2':
batch = input("Enter batch no.: ")
while not batch.isdigit():
batch = input("Batch no. should only be integer. Enter again: ")
actions.format_print(
columns_all,
actions.search_by_condition("Stock", f"BatchNo like '%{batch}%'"))
elif ch == '3':
num = input("Enter no. of columns you want to use: ")
while not num.isdigit():
num = input("Enter integer value only: ")
print("The columns are:")
print(str(col_dict).lstrip('{').rstrip('}'))
condition = ''
for i in range(int(num)):
col = input(f"Enter code for column{i + 1}: ")
while not col.isdigit() or int(col) not in col_dict:
col = input(
"The column no. you entered is not in option. Please enter again: "
)
col = col_dict[int(col)]
val = input(f"Enter value for column{i + 1}: ")
op = " like "
condition += col + op + "'%" + val + "%'"
if i != int(num) - 1:
condition += '&&'
actions.format_print(columns_all,
actions.search_by_condition("Stock", condition))
elif ch == '4':
try:
print("All columns are:")
print(columns_all)
condition = input(
'Enter condition(<column_name><operator>"<value>"): ')
while True:
try:
cur.execute(f"select 1+2 from Stock where {condition}")
cur.fetchall()
break
except Exception as e:
print(e)
condition = input(
"Your condition had above error! Enter again: ")
actions.format_print(
actions.get_columns("Stock"),
actions.search_by_condition("Stock", condition))
except Exception as e:
print(e)
print("There was an error!! Error code: 022")
traceback.print_exc()
def update():
print("Update options:")
print("2. Update using batch no.")
print("3. Update using condition")
print("0. Home")
print("1. Medicine Information")
ch = input("Enter your choice: ")
condition = '1=1'
while ch not in '0123' or len(ch) != 1:
ch = input("Invalid choice! Please enter again: ")
if ch == '0':
return 0
elif ch == '1':
return '1'
elif ch == '2':
batch = input("Enter batch no. of stock: ")
while not batch.isdigit():
batch = input(
"Batch no. should be an integer only! Please enter again: ")
condition = f"BatchNo='{batch}'"
elif ch == '3':
print("All columns are: ")
print(actions.get_columns("Stock"))
condition = input(
'Enter condition(<column_name><operator>"<value>"): ')
while True:
try:
cur.execute(f"select 1+2 where {condition}")
cur.fetchall()
break
except Exception as e:
print(e)
condition = input(
'Your condition had above error! Enter again(<column_name><operator>"<value>"): '
)
if ch in '23' and len(ch) == 1:
columns_all = actions.get_columns("Stock")
col_dict = {i + 1: columns_all[i] for i in range(len(columns_all))}
print(col_dict)
column = input("Which column no. do you want to update: ").lower()
while column not in col_dict:
column = input(
"Column you entered is not in table! Enter again: ").lower()
column = col_dict[int(column)]
val = input("Enter value: ")
try:
actions.update("Stock", column, val, condition)
print("Updated successfully...")
except Exception as e:
print("An error occurred!! Error code: 021" if ch ==
'2' else "Your condition had an error!!")
print(e)
traceback.print_exc()
def view():
print()
print("How many columns do you want to view:")
print("2: All columns, All records")
print("3: All columns, Some records")
print("4: Some columns, All records")
print("5: Some columns, Some records")
print("0: Go to home")
print("1: Go to Medicine Information")
ch = input()
columns_all = actions.get_columns("MedicineInfo")
col_dict = {i + 1: columns_all[i] for i in range(len(columns_all))}
while ch not in '012345' or len(ch) != 1:
ch = input("Invalid choice. Enter again: ")
if ch == '0':
return '0'
elif ch == '1':
return '1'
elif ch == '2':
actions.format_print(actions.get_columns("MedicineInfo"),
actions.show_all("MedicineInfo"))
elif ch == '3':
print("All columns are:")
print(str(col_dict).lstrip('{').rstrip('}'))
column = input(
"Which column no. do you want to use for record matching: ").lower(
)
while not column.isdigit() or int(column) not in col_dict:
column = input(
"Column no. you entered is not in option. Please enter again: "
).lower()
records = actions.input_rows()
column = col_dict[int(column)]
if column.lower() == "composition":
q = f"select * from MedicineInfo where {column} like '%{records[0]}%' "
for i in range(1, len(records)):
q += f"or {column} like '%{records[i]}%' "
cur.execute(q)
actions.format_print(columns_all, cur.fetchall())
else:
actions.format_print(
columns_all,
actions.search_multiple("MedicineInfo", column, records))
elif ch == '4':
clm = actions.input_cols("MedicineInfo")
actions.format_print(clm, actions.show_columns("MedicineInfo", clm))
elif ch == '5':
columns = actions.input_cols("MedicineInfo")
column = input(
"Which column no. do you want to use for record matching: ").lower(
)
while not column.isdigit() or int(column) not in col_dict:
column = input(
"Column no. you entered is not in option. Please enter again: "
).lower()
column = col_dict[int(column)]
records = tuple(actions.input_rows())
cols = str(columns).lstrip('[').rstrip(']').replace("'", '')
if column.lower() == "composition":
q = f"select {cols} from MedicineInfo where {column} like '%{records[0]}%' "
for i in range(1, len(records)):
q += f"or {column} like '%{records[i]}%' "
cur.execute(q)
actions.format_print(columns, cur.fetchall())
else:
actions.format_print(
columns,
actions.search_multiple("MedicineInfo", column, records))
def update():
print("Update options:")
print("2. Update using barcode")
print("3. Update using name")
print("4. Update using condition")
print("0. Home")
print("1. Medicine Information")
ch = input("Enter your choice: ")
condition = '1=1'
while ch not in '01234' or len(ch) != 1:
ch = input("Invalid choice! Please enter again: ")
if ch == '0':
return 0
elif ch == '1':
return '1'
elif ch == '2':
bar = input("Enter barcode of medicine: ")
while not bar.isdigit():
bar = input(
"Barcode should be an integer only! Please enter again: ")
condition = f"Barcode='{bar}'"
elif ch == '3':
name = input("Enter name of medicine: ")
condition = f"Name='{name}'"
elif ch == '4':
print("All columns are")
print(actions.get_columns("MedicineInfo"))
condition = input(
'Enter condition(<column_name><operator>"<value>"): ')
while True:
try:
cur.execute(f"select 1+2 from MedicineInfo where {condition}")
cur.fetchall()
break
except Exception as e:
print(e)
condition = input(
'Your condition had above error! \nEnter again(<column_name><operator>"<value>"): '
)
if ch in '234' and len(ch) == 1:
columns_all = actions.get_columns("MedicineInfo")
col_dict = {i + 1: columns_all[i] for i in range(len(columns_all))}
print(col_dict)
column = input("Which column no. do you want to update: ").lower()
while not column.isdigit() or int(column) not in col_dict:
column = input(
"Column no. you entered is not in option! Enter again: "
).lower()
column = col_dict[int(column)]
val = input("Enter value: ")
try:
actions.update("MedicineInfo", column, val, condition)
print("Updated records successfully...")
except Exception as e:
print("Your condition had below error!")
print(e)
def search():
print("Search options:")
print("2: Using Receipt No.")
print("3: Using Customer Name")
print("4: Using Date range")
print("5: Using Time range")
print("6: Using date and time ranges")
print("0: Home")
print("1: Sale Information")
ch = input("Enter your choice: ")
while ch not in '0123456' or len(ch) != 1:
ch = input("Invalid choice! Enter again: ")
if ch == '0':
return '0'
elif ch == '1':
return '1'
elif ch == '2':
receipt = input("Enter Receipt No.: ")
while not receipt.isdigit():
receipt = input("Receipt No. should be an integer! Enter again: ")
cur.execute(f"select * from Sale where ReceiptNo like '%{receipt}%'")
data = cur.fetchall()
actions.format_print(actions.get_columns("Sale"), data)
elif ch == '3':
name = input("Enter Customer Name: ")
actions.format_print(
actions.get_columns("Sale"),
actions.search("Sale", "CustomerName", "%" + name + "%", " like "))
elif ch == '4':
start = input("Enter starting date(yyyy-mm-dd): ")
start = actions.check_date(start)
while not start:
start = input(
"Date you entered is not of correct format! Enter again(yyyy-mm-dd): "
)
start = actions.check_date(start)
print(f"Starting Date: {start}")
end = input("Enter ending date(yyyy-mm-dd): ")
end = actions.check_date(end)
while not end:
end = input(
"Date you entered is not of correct format! Enter again(yyyy-mm-dd): "
)
end = actions.check_date(end)
print(f"Ending Date: {end}")
actions.format_print(
actions.get_columns("Sale"),
actions.search_by_condition(
"Sale", f"'{end}'>=SaleDate and SaleDate>='{start}'"))
elif ch == '5':
start = input("Enter starting time(hh:mm:ss): ")
while not actions.check_time(start):
start = input(
"Time you entered is not of correct format! Enter again(hh:mm:ss): "
)
end = input("Enter ending time(hh:mm:ss): ")
while not actions.check_time(end):
end = input(
"Time you entered is not of correct format! Enter again(hh:mm:ss): "
)
actions.format_print(
actions.get_columns("Sale"),
actions.search_by_condition(
"Sale", f"'{end}'>=SaleTime and SaleTime>='{start}'"))
elif ch == '6':
start1 = input("Enter starting date(yyyy-mm-dd): ")
start1 = actions.check_date(start1)
while not start1:
start1 = input(
"Date you entered is not of correct format! Enter again(yyyy-mm-dd): "
)
start1 = actions.check_date(start1)
print(f"Starting date: {start1}")
end1 = input("Enter ending date(yyyy-mm-dd): ")
end1 = actions.check_date(end1)
while not end1:
end1 = input(
"Date you entered is not of correct format! Enter again(yyyy-mm-dd): "
)
end1 = actions.check_date(end1)
print(f"Ending Date: {end1}")
start2 = input("Enter starting time(hh:mm:ss): ")
while not actions.check_time(start2):
start2 = input(
"Time you entered is not of correct format! Enter again(hh:mm:ss): "
)
end2 = input("Enter ending time(hh:mm:ss): ")
while not actions.check_time(end2):
end2 = input(
"Time you entered is not of correct format! Enter again(hh:mm:ss): "
)
actions.format_print(
actions.get_columns("Sale"),
actions.search_by_condition(
"Sale", f"""'{end2}'>=SaleTime and SaleTime>='{start2}' and
'{end1}'>=SaleDate and SaleDate>='{start1}'"""))
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)