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