# enable debugging import cgitb import cgi from myutils import c,cursor,sql, printHeader, printFooter, printOptions, centsToDollarString, getTranType, getItemName cgitb.enable() form = cgi.FieldStorage() printHeader('Stock detail') itemId = form['itemId'].value cursor.execute('SELECT manufacturer,brand,name FROM item WHERE itemId = ?',(itemId,)) (manufacturer,brand,name) = cursor.fetchone() print "<b>Item transactions for %s</b>"%getItemName(manufacturer,brand,name) cursor.execute(''' SELECT TransItem.quantity, TransItem.pricePerItem, tranDate, type, direction, description, shipping*priceperitem/(SELECT SUM(quantity*priceperitem) FROM transitem WHERE tranid = trans.tranid) FROM transItem INNER JOIN Trans USING (tranId) WHERE
import cgi from myutils import c,cursor,sql, printHeader, printFooter, gotoButton,centsToString cgitb.enable() form = cgi.FieldStorage() printHeader('Edit expense') if not form.has_key('expenseId'): print '<p class=error>Sorry, page called with no expense to edit</p>' else: expenseId = form['expenseId'].value cursor.execute('SELECT expDate,description,amount FROM expense WHERE expenseId = ?',(expenseId,)) (expDate,description,amount) = cursor.fetchone() print ''' <div class=addthing> <FORM ACTION=expenses.py> <H2>Edit expense</H2> <table> <tr><td align=right>Date:</td><td><INPUT TYPE=TEXT NAME=date ID=date SIZE=20 VALUE="%s" /> Format: YYYY-MM-DD</td></tr> <tr><td align=right>Description:</td><td><INPUT TYPE=TEXT NAME=description ID=description SIZE=70 VALUE="%s" /></td></tr> <tr><td align=right>Amount:</td><td><INPUT TYPE=TEXT NAME=amount ID=amount VALUE="%s" SIZE=5 onBlur='moneyFormat(event.target)'/>Enter negative amount for a credit</td></tr> <INPUT TYPE=hidden NAME=edit VALUE=%s /> </table> <INPUT TYPE=SUBMIT VALUE='Submit changes' onClick='return validateForm();' /> </FORM> </div>
{ return ( checkField('addItemMfr','Fill out the manufacturer name') && checkField('name','Fill out the product name') ); } </script> """ ###################### # Handle delete if form.has_key("delete"): deleteId = int(form["delete"].value) # First check again that the item is not referenced cursor.execute("SELECT COUNT(*) FROM binItems where itemId = ?", (deleteId,)) (count,) = cursor.fetchone() if int(count): print "<p class=error>Item is stored in bins - cannot delete</p>" else: cursor.execute("SELECT COUNT(*) FROM transItem where itemId = ?", (deleteId,)) (count,) = cursor.fetchone() if int(count): print "<p class=error>Item is referenced in old transactions - cannot delete</p>" else: try: cursor.execute("DELETE FROM Item WHERE itemId = ?", (deleteId,)) c.commit() print "<p class=info>Deleted item</p>" except Exception, e: print "<p class=error>Sorry, something went wrong with the deletion</p>", e
history = "MOVE %s FROM %s"%(delItem,delBin) moveDetails = [] totalToMove = 0 for i in range(2,tableSize+1): binId = form['addBin-'+str(i)].value binQty = int(form['addQty-'+str(i)].value) if binQty == 0: continue totalToMove += binQty moveDetails.append([binId,binQty]) history += " %d TO %s"%(binQty,binId) cursor.execute('SELECT SUM(quantity) FROM binItems WHERE binId = ? and itemId = ? GROUP BY binId',(delBin,delItem)) (foundQty,) = cursor.fetchone() if foundQty < totalToMove: raise ValueError, "<p class=error>Didn't find enough items to move</p>" cursor.execute('DELETE FROM binItems WHERE binId = ? and itemId = ?',(delBin,delItem)) for (binId,binQty) in moveDetails: cursor.execute('INSERT INTO binItems (binId,itemId,quantity) VALUES (?,?,?)',(binId,delItem,binQty)) if foundQty > totalToMove: cursor.execute('INSERT INTO binItems (binId,itemId,quantity) VALUES (?,?,?)',(delBin,delItem,foundQty-totalToMove)) cursor.execute('''INSERT INTO history (historyDate,body) VALUES (DATETIME('now'),?)''',(history,)) c.commit() # Redirect to same page, so page reload doesn't re-add move
cursor.execute('SELECT itemId,manufacturer,brand,name FROM Item') itemName = {} for (thisId,thisMfr,thisBrand,thisName) in cursor: thisLongName = getItemName(thisMfr,thisBrand,thisName) itemName[thisId] = thisLongName ############################################################################### if form.has_key('undo'): try: undo = int(form['undo'].value) cursor.execute('BEGIN IMMEDIATE TRANSACTION') cursor.execute('SELECT body FROM history WHERE historyId = ?',(undo,)) (s,) = cursor.fetchone() sl = s.split() stype = sl.pop(0) if stype == 'MOVE': itemId = int(sl.pop(0)) sl.pop(0) binId = int(sl.pop(0)) total = 0 while len(sl) > 0: qty = int(sl.pop(0)) sl.pop(0) to = int(sl.pop(0)) total += qty # reduce quantity in 'to' by qty
tracking = form['tracking'].value actualShipping = dollarStringToCents(form['actualShipping'].value) cursor.execute('BEGIN IMMEDIATE TRANSACTION') cursor.execute('UPDATE Trans SET tracking=?, actualShipping=? WHERE tranId = ?',(tracking,actualShipping,tranId)) c.commit() # redirect to page, so page reload doesn't re-add change printRedirect('Updating Sale','sales.py',0) sys.exit() except Exception,e: c.rollback() errorString = "<p class=error>Problem with database updte:</p><pre></pre>"%str(sys.exc_info()) printHeader2('Sale Details',errorString) cursor.execute('SELECT type,direction,tranDate,description,shipping,actualShipping,tracking FROM Trans where tranId = ?',(tranId,)) (type,direction,tranDate,description,shipping,actualShipping,tracking) = cursor.fetchone() mytype = getTranType(type,direction) print '<H2>Details for %s: %s</H2>'%(mytype,description) print '<p>Date: %s</p>'%tranDate cursor.execute('SELECT manufacturer,brand,name,quantity,pricePerItem FROM TransItem LEFT JOIN Item USING (itemId) WHERE tranId = ?', (tranId,)) print '<TABLE BORDER=1><TR><TH>Item</TH><TH>qty</TH><TH>unit price</TH><TH>tot price</TH>' totalPrice = 0 for (manufacturer,brand,name,quantity,pricePerItem) in cursor: itemsTotalPrice = (int(quantity)*int(pricePerItem)) totalPrice += itemsTotalPrice
# enable debugging import cgitb import cgi from myutils import c,cursor,sql, printHeader, printFooter, gotoButton, centsToDollarString,cell,moneyCell,getTranType,getItemName cgitb.enable() form = cgi.FieldStorage() printHeader('Purchase Details') tranId = form['tranId'].value cursor.execute('SELECT type,direction,tranDate,description,shipping FROM Trans where tranId = ?',(tranId,)) (type,direction,tranDate,description,shipping) = cursor.fetchone() mytype = getTranType(type,direction) print '<H2>Details for %s: %s</H2>'%(mytype,description) print '<p>Date: %s</p>'%tranDate sql = ''' SELECT manufacturer, brand, name, quantity, pricePerItem, shipping*priceperitem/(SELECT SUM(quantity*priceperitem) FROM transitem WHERE tranid = trans.tranid) FROM
# enable debugging import cgitb import cgi from myutils import c,cursor,sql, printHeader, printFooter, printOptions, centsToDollarString, getItemName, gotoButton, db_removeFromBin, db_addToBin import sys cgitb.enable() form = cgi.FieldStorage() printHeader('Monthly Report') ############################################################################### # Get the earliest transaction date cursor.execute('SELECT tranDate FROM trans ORDER BY trandate LIMIT 1') (earliest,) = cursor.fetchone() (earliestYear,earliestMonth,earliestDay) = earliest.split('-') earliestYear = int(earliestYear) earliestMonth = int(earliestMonth) # Get the latest transaction date cursor.execute('SELECT tranDate FROM trans ORDER BY trandate DESC LIMIT 1') (latest,) = cursor.fetchone() (latestYear,latestMonth,latestDay) = latest.split('-') latestYear = int(latestYear) latestMonth = int(latestMonth) # Generate the list of year/date starting & ending points year = earliestYear month = earliestMonth dates = []
# item is a kit kitId = itemId.replace('Kit','') quantity = int(form['quantity-'+str(i)].value) pricePerKit = int(dollarStringToCents(form['pricePerItem-'+str(i)].value)) cursor.execute('SELECT itemId,quantity FROM KitItem WHERE kitId = ?',(kitId,)) itemList = [] for (itemId,itemQty) in cursor: itemList.append((itemId,int(itemQty))) itemCount = len(itemList)*quantity for (itemId,itemQty) in itemList: salesItems.append((itemId,itemQty*quantity,pricePerKit/itemCount)) salesKits.append((kitId,quantity)) for (itemId,quantity,pricePerItem) in salesItems: cursor.execute('SELECT manufacturer,brand,name FROM Item WHERE itemId = ?',(itemId,)) (mfg,brand,name) = cursor.fetchone() print "<H3>Item %s</H3>"%getItemName(mfg,brand,name) cursor.execute(''' SELECT binId,Bin.name,SUM(quantity) FROM Bin INNER JOIN BinItems using (binId) INNER JOIN Item USING (ItemId) WHERE ItemId = ? GROUP BY binId ''',(itemId,)) totalFound = 0 bins = []