def select_receipts_for_drawer_close():
# This will get the date from today, and select
info = []
try:
last_close_id = SQLS.db_fetch("SELECT max(idclosings) FROM closings;")[0][0]
except:
print " UNABLE TO OBTAIN ID OF LAST CLOSE DATE "
try:
last_close_date = SQLS.db_fetch("SELECT endDate FROM closings WHERE idclosings = %s;" % last_close_id)[0][0]
except:
print " UNABLE TO OBTAIN THE MOST RECENT CLOSE DATE "
info.append(str(last_close_date))
info.append("%")
now = datetime.datetime.now()
try:
for el in SQLS.db_fetch("SELECT * FROM receipts WHERE dateCreated between '%s' AND '%s';" %
(last_close_date, now)):
for ell in el:
info.append(str(ell))
info.append("|")
info.append("%")
info.pop(-1)
if info[-1] == "|":
info.pop(-1)
except:
info.append("NA")
return info
def check_user_information(request):
info = []
user_id = SQLS.db_fetch("SELECT username FROM userAccess WHERE username = '******';" % request[2])
if len(user_id) > 0:
user_id = user_id[0][0]
password = SQLS.db_fetch("SELECT password FROM userAccess WHERE username = '******';" % user_id)[0][0]
if password == request[3]:
info.append("1")
return info
else:
info.append("3")
return info
info.append("2")
return info
def select_receipt_for_report_by_month_year(request):
month = request.split("|")[0]
year = request.split("|")[1]
info = []
try:
for el in SQLS.db_fetch(
"SELECT * "
"FROM receipts "
"WHERE MONTH(dateCreated) = %s "
"AND YEAR(dateCreated) = %s "
"AND transactionType = '%s' "
"ORDER BY deptCode;" % (month, year, "CHARGE BACK")
):
for ell in el:
info.append(str(ell))
info.append("|")
info.append("%")
info.pop(-1)
if info[-1] == "|":
info.pop(-1)
except:
info.append("NA")
SQLS.output_update_to_screen("Request for report by month and year . -> ")
return info
def select_sales_information():
info = []
for el in SQLS.db_fetch("SELECT * FROM information WHERE idinformation = 0;"):
for ell in el:
info.append(str(ell))
info.append("|")
info.pop(-1)
SQLS.output_update_to_screen("Request for sales information. -> ")
return info
def update_receipt_by_id(request):
info = []
cells = request[2].split("|")
original_data = SQLS.db_fetch("SELECT * FROM receipts WHERE receiptId = %s;" % cells[0])[0]
# Add quantities to inventory of items originally on receipt
for el in original_data[5].split(","):
item_id = el.split("#")[0]
quantity = int(el.split("#")[1])
current_item_quantity = int(SQLS.db_fetch("SELECT quantity FROM inventory WHERE inventoryId = %s;"
% item_id)[0][0])
updated_quantity = current_item_quantity + quantity
SQLS.db_update("UPDATE inventory SET quantity = %s WHERE inventoryId = %s;" % (updated_quantity, item_id))
# Update receipt information
SQLS.db_update("UPDATE receipts SET seller = '%s', firstName = '%s', lastName = '%s', itemsSold = '%s',"
"totalPrice = %s, description = '%s', deptCode = '%s', transactionType = '%s', "
"transactionCode = '%s' WHERE receiptId = %s;" %
(cells[2], cells[3], cells[4],
cells[5], cells[6], cells[7],
cells[8], cells[9], cells[10],
cells[0]))
# Go through each inventory item and update quantity in db
for el in cells[5].split(","):
item_id = el.split("#")[0]
quantity = int(el.split("#")[1])
current_item_quantity = int(SQLS.db_fetch("SELECT quantity FROM inventory WHERE inventoryId = %s;"
% item_id)[0][0])
updated_quantity = current_item_quantity - quantity
if updated_quantity < 0:
updated_quantity = 0
SQLS.db_update("UPDATE inventory SET quantity = %s WHERE inventoryId = %s;" % (updated_quantity, item_id))
info.append("1")
return info
def delete_receipt_by_id(id):
# Get items from receipt
items_sold = ""
try:
items_sold = SQLS.db_fetch(
"SELECT itemsSold FROM receipts WHERE receiptId = %s;" % id
)[0][0]
except:
SQLS.output_update_to_screen("Unable to fetch items sold in receipt id : %s ." % id )
# Add their quantities back into inventory
if items_sold != "":
for el in items_sold.split(","):
item_id = el.split("#")[0]
quantity = int(el.split("#")[1])
# If the item isn't a general library item, update quantity
if item_id != "94":
current_item_quantity = int(SQLS.db_fetch("SELECT quantity FROM inventory WHERE inventoryId = %s;"
% item_id)[0][0])
updated_quantity = current_item_quantity + quantity
SQLS.db_update("UPDATE inventory SET quantity = %s WHERE inventoryId = %s;" %
(updated_quantity, item_id))
else:
# If it is a general lib item, set to 0
SQLS.db_update("UPDATE inventory SET quantity = %s WHERE inventoryId = 0;")
# Delete receipt
info = []
info.append(str(SQLS.db_update(
"DELETE FROM receipts WHERE receiptId = %s;" % id
)))
return info
def select_department_information():
info = []
try:
for el in SQLS.db_fetch("SELECT * FROM departmentInformation ORDER BY deptName;"):
for ell in el:
info.append(str(ell))
info.append("|")
info.append("%")
info.pop(-1)
if info[-1] == "|":
info.pop(-1)
except:
info.append("NA")
SQLS.output_update_to_screen("Request for department list. -> ")
return info
def select_inventory():
info = []
try:
for el in SQLS.db_fetch("SELECT * FROM inventory WHERE quantity > 0 OR price = 0 ORDER BY itemName;"):
for ell in el:
info.append(str(ell))
info.append("|")
info.append("%")
info.pop(-1)
if info[-1] == "|":
info.pop(-1)
except:
info.append("NA")
SQLS.output_update_to_screen("Request for inventory list. -> ")
return info
def select_receipts_by_id_range(range):
info = []
try:
for el in SQLS.db_fetch("SELECT * FROM receipts WHERE receiptId between '%s' AND '%s';" %
(range.split("|")[0], range.split("|")[1])):
for ell in el:
info.append(str(ell))
info.append("|")
info.append("%")
info.pop(-1)
if info[-1] == "|":
info.pop(-1)
except:
info.append("NA")
return info
def select_closings_by_date_range(dates):
info = []
try:
for el in SQLS.db_fetch("SELECT * FROM closings WHERE DATE(endDate) between '%s' AND '%s' "
"AND comments != 'START_MARKER';" %
(dates.split("|")[0], dates.split("|")[1])):
for ell in el:
info.append(str(ell))
info.append("|")
info.append("%")
info.pop(-1)
if info[-1] == "|":
info.pop(-1)
except:
info.append("NA")
return info
def select_receipts_by_descriptor(range, switch):
info = []
first_date = range.split("|")[0]
second_date = range.split("|")[1]
query = ""
if switch == "1":
query = ("SELECT * FROM receipts "
"WHERE transactionType != 'CHARGE BACK' "
"AND DATE(dateCreated) between DATE('%s') AND DATE('%s');" %
(first_date, second_date))
if switch == "2":
query = ("SELECT * FROM receipts "
"WHERE transactionType = 'CHARGE BACK' "
"AND DATE(dateCreated) between DATE('%s') AND DATE('%s');" %
(first_date, second_date))
if switch == "3":
query = ("SELECT * FROM receipts "
"WHERE DATE(dateCreated) between DATE('%s') AND DATE('%s');" %
(first_date, second_date))
try:
for el in SQLS.db_fetch(query):
for ell in el:
info.append( str(ell).replace('\n', ''))
info.append("|")
info.append("%")
info.pop(-1)
if info[-1] == "|":
info.pop(-1)
except:
info.append("NA")
print info
SQLS.output_update_to_screen("Request for receipts between %s , and %s -> " % (first_date, second_date))
return info
