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()
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 loadFromEbay():
itemList = []
# Get first page, which also gets the total number of pages
totalNumberOfPages = getPage(1,pageSize,itemList)
# Already got first page, so start at page 2
for pageNo in (range(2,totalNumberOfPages+1)):
getPage(pageNo,pageSize,itemList)
# Clean up the table
cursor.execute("DELETE FROM ebayList")
# Add the new data to the table
for (title,itemId,quantity) in itemList:
cursor.execute("INSERT INTO ebayList (title,itemId,quantity) VALUES (?,?,?)",(title,itemId,quantity))
c.commit()
def loadFromEbay():
# specify the connection to the eBay Sandbox environment
connection = httplib.HTTPSConnection(serverUrl)
itemList = []
# Get first page, which also gets the total number of pages
totalNumberOfPages = getPage(connection,1,pageSize,itemList)
# Already got first page, so start at page 2
for pageNo in (range(2,totalNumberOfPages+1)):
getPage(connection,pageNo,pageSize,itemList)
# Clean up the table
cursor.execute("DELETE FROM ebayList")
# Add the new data to the table
for (title,itemId,quantity) in itemList:
cursor.execute("INSERT INTO ebayList (title,itemId,quantity) VALUES (?,?,?)",(title,itemId,quantity))
c.commit()
cgitb.enable()
form = cgi.FieldStorage()
printHeader1('Expenses & Fees')
errorString = None
######################
# Handle delete
if form.has_key('deleteId'):
try:
deleteId = int(form['deleteId'].value)
cursor.execute('BEGIN IMMEDIATE TRANSACTION')
cursor.execute('DELETE FROM expense WHERE expenseId = ?',(deleteId,))
c.commit()
printRedirect('Deleted expense','expenses.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())
######################
# Handle add
addManufacturer = None
addBrand = None
if form.has_key('add'):
try:
#!/python26/python.exe
# 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('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')
cgitb.enable()
form = cgi.FieldStorage()
printHeader1('Sale Details')
errorString = None
tranId = form['tranId'].value
if form.has_key('tracking'):
try:
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()
import cgitb
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>
# 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
form = cgi.FieldStorage()
printHeader1('Kits')
errorString = None
#cgi.print_form(form)
##################
# Handle add kit request
if form.has_key('AddKit'):
try:
name = form['name'].value
maxItemIdx = form['addLastItem'].value
cursor.execute('BEGIN IMMEDIATE TRANSACTION')
cursor.execute("INSERT INTO Kit (name) VALUES (?)",(name,))
kitId = cursor.lastrowid
for i in range(1,int(maxItemIdx)+1):
if form.has_key('addItem-'+str(i)):
itemId = form['addItem-'+str(i)].value
quantity = form['quantity-'+str(i)].value
if int(quantity) == 0: continue
cursor.execute('INSERT INTO KitItem (kitId,itemId,quantity) VALUES (?,?,?)',
(kitId,itemId,quantity))
c.commit()
# redirect to same page, so reload doesn't re-add purchase
printRedirect('Added kit','kits.py',0)
sys.exit()
from myutils import c,cursor,sql, printHeader, printFooter, gotoButton, centsToDollarString, dollarStringToCents, cell, moneyCell, getItemName
cgitb.enable()
form = cgi.FieldStorage()
printHeader('Link Ebay title to stock item')
if not form.has_key('title'):
print "No ebay listing title specified"
sys.exit()
title = form['title'].value
print "<p>Please select a stock item to link to ebay listing item <b>%s</b></p>"%title
# Get a list of all items
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)))
print '<FORM ACTION=ebayListing.py>'
print '<SELECT NAME=itemid>'
for option in itemOptions: print option
print '</SELECT>'
print '<INPUT TYPE=HIDDEN NAME=title VALUE=%s />'%urllib.quote_plus(title)
print '<INPUT TYPE=SUBMIT VALUE="Add link" NAME=linkToItem>'
print '</FORM>'
printFooter()
import cgitb
import cgi
from myutils import c,cursor,sql, printHeader, printFooter, gotoButton
cgitb.enable()
form = cgi.FieldStorage()
printHeader('Edit item')
if not form.has_key('itemId'):
print '<p class=error>Sorry, page called with no item to edit</p>'
else:
itemId = form['itemId'].value
cursor.execute('SELECT manufacturer,brand,name FROM item WHERE itemId = ?',(itemId,))
(manufacturer,brand,name) = cursor.fetchone()
if not brand: brand = ''
print '''
<div class=addthing>
<FORM ACTION=items.py>
<H2>Edit item type</H2>
<table>
<tr><td align=right>Manufacturer:</td><td><INPUT TYPE=TEXT NAME=manufacturer ID=mfr SIZE=70 VALUE="%s" /></td></tr>
<tr><td align=right>Brand:</td><td><INPUT TYPE=TEXT NAME=brand ID=brand SIZE=70 VALUE="%s" /></td></tr>
<tr><td align=right>Name:</td><td><INPUT TYPE=TEXT NAME=name ID=name SIZE=70 VALUE="%s" /></td></tr>
<INPUT TYPE=hidden NAME=edit VALUE=%s />
</table>
<INPUT TYPE=SUBMIT VALUE='Submit changes' onClick='return validateForm();' />
</FORM>
# -*- coding: UTF-8 -*-
# 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)
importCountFromEbay(itemid)
except Exception,e:
c.rollback()
print "<p class=error>Problem importing data:</p><pre>",sys.exc_info(),"</pre>"
###############################################################################
# Count total items
cursor.execute('''
SELECT
Item.itemId AS id,
manufacturer,
brand,
name,
countOnEbay,
SUM(BinItems.quantity) AS number,
( SELECT title FROM ebayList2item INNER JOIN ebayList USING (title) where ebayList2item.itemId = Item.itemId )
FROM
Item
INNER JOIN BinItems USING (itemId)
GROUP BY Item.itemId
ORDER BY manufacturer,brand,name
''')
stockList = []
total = 0
for itemDetails in cursor:
stockList.append(itemDetails)
total += itemDetails[5]
#######################################
print '''<SCRIPT LANGUAGE='javascript'>
function validateForm()
{
return (checkField('addBinName',"You must enter a bin name"));
}
document.getElementById('addBinName').focus();
</SCRIPT>'''
###############
# Handle add
if form.has_key('add'):
try:
sql = "INSERT INTO bin (name,slots) VALUES (?,?)"
cursor.execute(sql,(form['name'].value,form['slots'].value))
c.commit()
except sqlite3.IntegrityError:
print "<p class=error>Oops - bin %s already exists!</p><br />"%form['name'].value
###############
# Handle delete
if form.has_key('delete'):
try:
sql = "DELETE FROM bin WHERE binId = ?"
cursor.execute(sql,(form['delete'].value,))
c.commit()
print "<p class=info>Deleted",form['name'].value,"</p>"
except:
print "<p class=error>Sorry, something went wrong with the deletion.</p>"
errorString = None
#cgi.print_form(form)
##################
# Handle add purchase request
if form.has_key('AddPurchase'):
try:
seller = form['seller'].value
shipping = dollarStringToCents(form['shipping'].value)
if form.has_key('reconcile'):
tranType = 'RECONCILE'
else:
tranType = 'REAL'
maxItemIdx = form['addLastItem'].value
cursor.execute('BEGIN IMMEDIATE TRANSACTION')
cursor.execute("INSERT INTO Trans (type,direction,tranDate,description,shipping) VALUES (?,'ADD',DATE('now'),?,?)",
(tranType,seller,shipping))
tranId = cursor.lastrowid
history = "BUY %d"%tranId
for i in range(1,int(maxItemIdx)+1):
if form.has_key('addItem-'+str(i)):
itemId = form['addItem-'+str(i)].value
quantity = form['quantity-'+str(i)].value
if int(quantity) == 0: continue
history += " %s OF %s TO 1"%(quantity,itemId) # binId 1 - unstocked - is hard-coded
pricePerItem = dollarStringToCents(form['pricePerItem-'+str(i)].value)
cursor.execute('INSERT INTO TransItem (tranId,itemId,quantity,pricePerItem) VALUES (?,?,?,?)',
(tranId,itemId,quantity,pricePerItem))
function validateForm()
{
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
delItem= form['delItem'].value
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()
cgitb.enable()
form = cgi.FieldStorage()
printHeader('Bin list')
itemId = form['itemId'].value
binId = form['binId'].value
##################################
# Get the details of what we're transferring from
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('''
import cgi
from myutils import c,cursor,sql, printHeader, printFooter, printOptions, centsToDollarString, getItemName
cgitb.enable()
form = cgi.FieldStorage()
printHeader('Stock list')
cursor.execute('''
SELECT
Item.itemId AS id,
manufacturer,
brand,
name,
SUM(BinItems.quantity) AS number,
( SELECT title FROM ebayList2item LEFT JOIN ebayList USING (title) where ebayList2item.itemId = Item.itemId )
FROM
Item
INNER JOIN BinItems USING (itemId)
GROUP BY Item.itemId
ORDER BY manufacturer,brand,name
''')
stockList = []
for itemDetails in cursor: stockList.append(itemDetails)
if len(stockList) == 0:
print "<H2>You don't have any stock</H2>"
else:
print "<TABLE BORDER=1 class='listthings sortable'>"
print "<TR><TH>Item</TH><TH>Qty</TH><TH>Listed?</TH></TR>"
#cgi.print_form(form)
################
# Handle add sale
if form.has_key('addSale'):
try:
#cgi.print_form(form)
buyer = form['buyer'].value
shipping = dollarStringToCents(form['shipping'].value)
if form.has_key('reconcile'):
tranType = 'RECONCILE'
else:
tranType = 'REAL'
cursor.execute('BEGIN IMMEDIATE TRANSACTION')
cursor.execute("INSERT INTO Trans (type,direction,tranDate,description,shipping) VALUES (?,'DEL',DATE('now'),?,?)",
(tranType,buyer,shipping))
tranId = cursor.lastrowid
history = "SELL %d"%tranId
numberOfBins = {}
kitSales = []
for field in form.keys():
if field.startswith('numberOfBins-'):
numberOfBins[field.replace('numberOfBins-','')] = int(form[field].value)
if field.startswith('kitId-'):
key = field.replace('kitId-','')
kitId = form[field].value
if form.has_key('import'):
try:
loadFromEbay()
except Exception,e:
c.rollback()
print "<p class=error>Problem importing data:</p><pre>",sys.exc_info(),"</pre>"
print gotoButton('Import listings from Ebay','ebayListing.py?import=1')
###########################################################################
# Add link to item
if form.has_key('linkToItem'):
itemid = form['itemid'].value
title = urllib.unquote_plus(form['title'].value)
print "<p>Linking <i>"+title+"<i> to item id "+itemid+"</p>"
cursor.execute('INSERT INTO ebayList2Item (title,itemid) VALUES (?,?)',(title,itemid))
c.commit()
###########################################################################
# Add link to kit
if form.has_key('linkToKit'):
kitid = form['kitid'].value
title = urllib.unquote_plus(form['title'].value)
print "<p>Linking <i>"+title+"<i> to kit id "+kitid+"</p>"
cursor.execute('INSERT INTO ebayList2Kit (title,kitid) VALUES (?,?)',(title,kitid))
c.commit()
###########################################################################
# Remove link
if form.has_key('unlink'):
if form.has_key('itemid'):
# -*- coding: UTF-8 -*-
# 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)
if form.has_key('import'):
try:
loadFromEbay()
except Exception,e:
c.rollback()
print "<p class=error>Problem importing data:</p><pre>",sys.exc_info(),"</pre>"
print gotoButton('Import listings from Ebay','ebaylisting.py?import=1')
###########################################################################
# Add link
if form.has_key('link'):
itemid = form['itemid'].value
title = urllib.unquote_plus(form['title'].value)
print "<p>Linking <i>"+title+"<i> to item id "+itemid+"</p>"
cursor.execute('INSERT INTO ebayList2Item (title,itemid) VALUES (?,?)',(title,itemid))
c.commit()
######################################
# Table
cursor.execute('''
SELECT
title,ebayList.quantity,ebayList.itemId,Item.manufacturer,Item.brand,Item.name,SUM(BinItems.quantity)
FROM
ebayList
LEFT JOIN ebayList2Item USING (title)
LEFT JOIN BinItems USING (itemId)
LEFT JOIN Item USING (itemId)
GROUP BY ebayList.itemId
ORDER BY
title''')
from myutils import c,cursor,sql, printHeader, printFooter, gotoButton, centsToDollarString, dollarStringToCents, cell, moneyCell, getItemName
cgitb.enable()
form = cgi.FieldStorage()
printHeader('Link Ebay title to kit')
if not form.has_key('title'):
print "No ebay listing title specified"
sys.exit()
title = form['title'].value
print "<p>Please select a kit to link to ebay listing item <b>%s</b></p>"%title
# Get a list of all items
cursor.execute("SELECT kitId,name FROM Kit ORDER BY name")
itemOptions = []
for (itemId,name) in cursor:
itemOptions.append('<OPTION VALUE=%s>%s</OPTION>'%(itemId,name))
print '<FORM ACTION=ebayListing.py>'
print '<SELECT NAME=kitid>'
for option in itemOptions: print option
print '</SELECT>'
print '<INPUT TYPE=HIDDEN NAME=title VALUE=%s />'%urllib.quote_plus(title)
print '<INPUT TYPE=SUBMIT VALUE="Add link" NAME=linkToKit>'
print '</FORM>'
printFooter()
salesKits = []
# Now print move table for each item
for i in range(1,int(maxItemIdx)+1):
if form.has_key('addItem-'+str(i)):
itemId = form['addItem-'+str(i)].value
if itemId.startswith('Item'):
# item is an item
itemId = itemId.replace('Item','')
salesItems.append((itemId,form['quantity-'+str(i)].value,dollarStringToCents(form['pricePerItem-'+str(i)].value)))
else:
# 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
