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: