def importCountFromEbay(itemId): cursor.execute('''SELECT manufacturer,brand,name,countOnEbay FROM Item WHERE itemId = ?''',(int(itemId),)) for itemDetails in cursor: (manufacturer,brand,name,countOnEbay) = itemDetails print "Looking for %s on EBay... "%getItemName(manufacturer,brand,name) (manufacturer,brand,name,countOnEbay) = itemDetails count = ebayFindItem(getItemName(manufacturer,brand,name)) print "Found %d listings<br />"%count if count != countOnEbay: cursor.execute('UPDATE Item SET countOnEbay = ? WHERE itemId = ?',(count,itemId)) c.commit()
def importCountFromEbay(itemId): cursor.execute('''SELECT manufacturer,brand,name,countOnEbay FROM Item WHERE itemId = ?''',(int(itemId),)) for itemDetails in cursor: (manufacturer,brand,name,countOnEbay) = itemDetails (manufacturer,brand,name,countOnEbay) = itemDetails count = ebayFindItem(getItemName(manufacturer,brand,name)) print count if count != countOnEbay: cursor.execute('UPDATE Item SET countOnEbay = ? WHERE itemId = ?',(count,itemId)) c.commit()
# 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 itemId = ?
INNER JOIN BinItems using (binId) INNER JOIN Item USING (itemId) GROUP BY binId,itemId ''') binList = [] for binDetails in cursor: binList.append(binDetails) def nameToList(a): (binId,itemId,binName,itemMfr,itemBrand,itemName,qty) = a return binName.split() binList.sort(sortLists,nameToList) if len(binList) == 0: print "<H2>You don't have any stock</H2>" else: print "<TABLE BORDER=1 class='listthings sortable'>" print "<TR><TH>Bin</TH><TH>Item</TH><TH>Quantity</TH></TR>" for (binId,itemId,binName,manufacturer,brand,name,number) in binList: print "<TR>" print "<TD>%s</TD>"%binName print "<TD>%s</TD>"%getItemName(manufacturer,brand,name) print "<TD>%d</TD>"%(number,) print "<TD>",gotoButton('Move','moveStock.py?binId=%s&itemId=%s'%(binId,itemId)),"</TD>" print "</TR>" print "</TABLE>" printFooter()
printRedirect('Added Sale','sales.py',0) sys.exit() except Exception,e: c.rollback() errorString = "<p class=error>Problem with database update:</p><pre>%s</pre>"%str(sys.exc_info()) printHeader2('Sales',errorString) ################ # Get list of items for add sale form cursor.execute("SELECT itemId,manufacturer,brand,name FROM item INNER JOIN binItems USING (itemId) WHERE quantity > 0 GROUP BY manufacturer,brand,name ORDER BY manufacturer,brand,name") itemOptions = [] for (itemId,manufacturer,brand,name) in cursor: itemOptions.append('<OPTION VALUE=Item%s>%s</OPTION>'%(itemId,getItemName(manufacturer,brand,name))) ################### # Get list of kits for add sale form cursor.execute("SELECT kitId,name FROM Kit ORDER BY name") for (kitId,name) in cursor: itemOptions.append('<OPTION VALUE=Kit%s>Kit: %s</OPTION>'%(kitId,getName(name))) ##### # Add form print ''' <div class=addthing> <H2>Add new sale</H2> <FORM name=addTran ACTION=newSaleDetails.py> Buyer: <INPUT TYPE=TEXT NAME=buyer ID=buyer SIZE=40 />
printRedirect('Deleted kit','kits.py',0) sys.exit() except Exception,e: c.rollback() errorString = "<p class=error>Problem with database update:</p><pre>%s</pre>"%str(sys.exc_info()) printHeader2('Kits',errorString) ################ # Get list of items for add kit form cursor.execute("SELECT itemId,manufacturer,brand,name FROM item ORDER BY manufacturer,brand,name") itemOptions = [] for (itemId,manufacturer,brand,name) in cursor: itemOptions.append('<OPTION VALUE=%s>%s</OPTION>'%(itemId,getItemName(manufacturer,brand,name))) ##### # Add form print "<div class=addthing>" print "<H2>New kit</H2>" print "<FORM name=addKit>" print "Kit name: <INPUT TYPE=TEXT NAME=name ID=name SIZE=40/>" print "<br />" print "<TABLE BORDER=1 ID=addKitItemTable>" print "<TR><TH>Item</TH><TH>Qty</TH></TR>" print "</TABLE>" print "<INPUT TYPE=HIDDEN ID=addKitItemTableLastRow NAME=addLastItem VALUE=0 />" print "<INPUT TYPE=button VALUE='add another item' onClick='addItemRow();' />" print "<p />"
cursor.execute(''' SELECT binId,itemId,Bin.name,Item.manufacturer,Item.brand,Item.name,quantity FROM Bin INNER JOIN BinItems using (binId) INNER JOIN Item USING (itemId) WHERE itemId = ? AND binId = ? ''',(itemId,binId)) quantities = [] total = 0 for (thisBinId,thisItemId,binName,mfr,brand,name,quantity) in cursor: quantities.append(quantity) total += quantity itemName = getItemName(mfr,brand,name) ################################### # Get list of all bins and their size cursor.execute(''' SELECT binId,name,slots,SUM(quantity) FROM Bin LEFT JOIN BinItems USING (binId) WHERE binId != ? GROUP BY binId ''',(binId,)) toBins = [] for (thisBinId,name,slots,used) in cursor:
cgitb.enable() form = cgi.FieldStorage() printHeader('History') # Read in the bin names and the item names - we're going to need them later cursor.execute('SELECT binId,name FROM Bin') binName = {} for (thisId,thisName) in cursor: binName[thisId] = thisName 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)
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 print '<TR>' print cell(getItemName(manufacturer,brand,name)) print cell(quantity) print moneyCell(pricePerItem) print moneyCell(itemsTotalPrice) print '</TR>' print "<TR><TD COLSPAN=3 ALIGN=RIGHT>Shipping:</TD>",moneyCell(shipping),"</TR>" totalPrice += shipping print "<TR><TD COLSPAN=3 ALIGN=RIGHT><b>Total:</b></TD>",moneyCell(totalPrice),"</TR>" print "</TABLE>" print "<FORM name=modTran ACTION=saleDetails.py>" print "<INPUT TYPE=hidden NAME=tranId VALUE=%s>"%tranId print "Actual shipping costs: <INPUT TYPE=TEXT CLASS=money NAME=actualShipping ID=actualShipping VALUE=%s SIZE=5 onBlur='moneyFormat(event.target)'/>"%centsToString(actualShipping) print "Tracking number: <INPUT TYPE=TEXT NAME=tracking ID=tracking VALUE=%s SIZE=25/>"%tracking print "<BR />"
itemQtyInKits[itemId] += qty ####################################### #print '<button type=button onclick=\"updateAllFromEbay()\">Update all from ebay</button>' if len(stockList) == 0: print "<H2>You don't have any stock</H2>" else: print "<TABLE BORDER=1 class='listthings sortable' id=stockTable>" print "<TR><TH>Item</TH><TH>Qty not<br />in kits</TH><TH>Qty<br />in kits</TH><TH># on ebay</TH><TH>Listed?</TH></TR>" for (itemId,manufacturer,brand,name,countOnEbay,number,ebayTitle) in stockList: if itemId in itemQtyInKits: qtyInKits = itemQtyInKits[itemId] else: qtyInKits = 0 print "<TR>" print "<TD><A HREF=singleitem.py?itemId=%s>%s</A></TD>"%(itemId,getItemName(manufacturer,brand,name)) print "<TD>%d</TD>"%(number-qtyInKits,) print "<TD>%d</TD>"%(qtyInKits,) print "<TD><font id=itemId-%d>%d</font> <button type=button onclick=\"updateFromEbay(%d)\">Update</button></TD>"%(itemId,countOnEbay,itemId) if ebayTitle: print "<TD>Yes</TD>" else: print "<TD>No</TD>" print "</TR>" print "</TABLE>" print "<p><i>Total items: %d</i></p>"%total printFooter()
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 = [] for (binId,binName,binQuantity) in cursor: