
- Employee punches out on POS terminal
- POS terminal hangs for about 30 seconds
- POS terminal prints sales audit
- Employee brings printed audit to manager
- Manager enters audit into reconciliation sheet
- Manager collects monies owed
- Manager dismisses employee
After some investigation, I found that the main office computer was hosting an MSSQL server. The jig was up. All I had to do was access it and pull the information, run the same calculations the POS software was running, then enter the information into the sheet programmatically. Luckily, the POS software was a POS, and kept the database credentials in the application directory without even a hint of encryption or obfuscation. The database structure was a nightmare, and I soon found that the reason the POS system would hang when generating reports is that a lot of the calculations and summations were being done application-side.
# recon de-obfuscator # (c) 2014 Albert Sharkis import pymssql from openpyxl import load_workbook from openpyxl import Workbook from datetime import date from collections import OrderedDict print("Initializing...") workbookfile = input('Drag recon file here: ') workbookfile = workbookfile[1:len(workbookfile)-1] print("Loading recon file...") today = date.today() correctday = input("Is today "+today.strftime("%m/%d/%Y")+"? (Y/N) ") if(correctday.lower()=='n'): businessdate = input("Enter today's business date: ") else: businessdate = today.day systemday = str(today.month)+"/"+str(businessdate)+"/"+str(today.year) server = "*****" user = "*****" password = "12345" db = "*****" wb = load_workbook(workbookfile) ws = wb[str(businessdate)] print("Initialization complete") conn = pymssql.connect(server,user,password,db) print("Connection established.") cursor = conn.cursor() cursor.execute(""" select checknumber.owner from checknumber where checknumber.systemday='"""+systemday+"""' order by autonum asc """) results = cursor.fetchall() userids=[] for result in results: userids.append(result[0]) #filter duplicate userids userids = list(OrderedDict.fromkeys(userids)) omega=[] iter=5 for userid in userids: cursor.execute(""" select MajorClass.className, sum(checkDetail.itemPrice+coalesce(mods.modcost,0)) as cost from checkNumber right join checkDetail on checkDetail.checkId = checkNumber.autonum left join menuItem on menuItem.autonum = checkDetail.itemId left join MinorClass on MinorClass.classId = menuItem.classId left join MajorClass on MajorClass.classId = MinorClass.majorClassId left join (select checkDetailId, sum(checkModDetail.modValue) as modcost from checkModDetail group by checkDetailId) as mods on mods.checkDetailId=checkDetail.autonum where systemday = '"""+systemday+"""' and owner="""+str(userid)+""" group by MajorClass.className """) catbreakdown = cursor.fetchall() cursor.execute(""" select userName from Users where userId="""+str(userid-100)+""" """) result = cursor.fetchone() namecell = ws.cell(row=iter,column=2) namecell.value=result[0] print(result[0]) beertot = 0 for cat in catbreakdown: if cat[0]=="Beer" or cat[0]=="DRAFT BEER": beertot+=cat[1] elif cat[0]=="Liquor": ws.cell(row=iter,column=4).value=cat[1] elif cat[0]=="Wine": ws.cell(row=iter,column=5).value=cat[1] elif cat[0]=="Non-Alcoholic": ws.cell(row=iter,column=6).value=cat[1] elif cat[0]=="Food": ws.cell(row=iter,column=7).value=cat[1] elif cat[0]=="Tickets": ws.cell(row=iter,column=8).value=cat[1] elif cat[0]=="Payouts": ws.cell(row=iter,column=21).value=abs(cat[1]) ws.cell(row=iter,column=3).value=beertot cursor.execute(""" select sum(tax), sum(gratuity) from checknumber where systemday='"""+systemday+"""' and owner="""+str(userid)+""" """) result = cursor.fetchone() ws.cell(row=iter,column=12).value=result[0] tip = result[1] ws.cell(row=iter,column=26).value=tip cursor.execute(""" select paytype, sum(cost) from checknumber where systemday='"""+systemday+"""' and owner="""+str(userid)+""" and cost>0 group by paytype """) results=cursor.fetchall() for result in results: if result[0]==1: ws.cell(row=iter,column=15).value=result[1] elif result[0]==2: ws.cell(row=iter,column=16).value=result[1] ws.cell(row=iter,column=30).value=result[1]+tip iter+=1 print("Saving updated recon...") wb.save(workbookfile)For my small application, I chose Python because I could run it on the office computer, plus it's easy to modify on the target system in case of bugs. I used pymssql for easy access to the database, openpyxl for opening/writing to the xlsx file, and OrderedDict to filter duplicate userids in the initial steps of the process. As you can see, I used a highly detailed database query for each user in order to breakdown sales by product category, including order modifiers. This saved immense amounts of time, maybe 0.25 seconds per user instead of the 30 seconds that the POS would require. I could have broken down the item classes to the class ids, but leaving it with the name doesn't cause too much trouble and helped when debugging. The sheet was already structured with formulas, so simply inserting the values into the appropriate cells for each user sufficed. Initial deployment was successful, there were only a few tweaks necessary to accommodate alternate methods of payment and some of the specials that the venue offered. After that the next issues was categorizing the order modifiers appropriately, and, Bob's your uncle, we had just saved tons of time, kept people on the floor for the maximum amount of time, and reduced our errors to zero, through a simple piece of programming and reverse-engineering.