Albert Sharkis

This is the monster Excel sheet that the company used to reconcile their sales at the end of each day. Every day gets a new sheet, and as people leave the sales floor for the day, their name and numbers (broken down into product categories) are entered. It's a hideously inefficient procedure, but I was told that their accounting department can only interpret this specific sheet (they probably had someone write a program for them), so this system persists. The process was as follows:

1. Employee punches out on POS terminal
2. POS terminal hangs for about 30 seconds
3. POS terminal prints sales audit
4. Employee brings printed audit to manager
5. Manager enters audit into reconciliation sheet
6. Manager collects monies owed
7. Manager dismisses employee

The first bit is what interested me here. The POS terminal was hanging for a ridiculous amount of time, and would be unresponsive to user input. It was doing some heavy processing, or was hanging waiting for something. Secondly, where ever this information was coming from, it was obviously computer-generated. So why were we spending all this time generating the sales audit, printing it, and re-entering the information into a computer? It doesn't make sense.
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.