# 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 = []
