INNER JOIN Trans USING (tranId)
WHERE
itemId = ?
ORDER BY tranDate DESC
''',(itemId,))
print "<TABLE BORDER=1 class=listthings><TR>"
print "<TH>Date</TH>"
print "<TH>Type</TH>"
print "<TH>Party</TH>"
print "<TH>Qty</TH>"
print "<TH>Price/item</TH>"
print "<TH>Prorated<br />shipping</TH>"
print "<TH>Total<br />per unit</TH>"
print "</TR>"
for (quantity,pricePerItem,purchaseDate,type,direction,description,proratedShipping) in cursor:
if not proratedShipping: proratedShipping = 0
tranType = getTranType(type,direction)
print "<TR>"
print "<TD>%s</TD>"%purchaseDate
print "<TD>%s</TD>"%tranType
print "<TD>%s</TD>"%description
print "<TD>%d</TD>"%quantity
print "<TD>%s</TD>"%centsToDollarString(pricePerItem)
print "<TD>%s</TD>"%centsToDollarString(proratedShipping)
print "<TD>%s</TD>"%centsToDollarString(pricePerItem+proratedShipping)
print "</TR>"
print "</TABLE>"
printFooter()
cursor.execute('''
SELECT
tranId,type,direction,tranDate,description,shipping,SUM(quantity*pricePerItem)
FROM Trans
LEFT JOIN TransItem USING (tranId)
WHERE direction == 'ADD'
GROUP BY tranId
ORDER BY tranDate DESC, tranid DESC
LIMIT 200
''')
print "<H2>Last 200 purchases</H2>"
print "<TABLE BORDER=1 class='listthings sortable'>"
print "<TR><TH>Type</TH><TH>Date</TH><TH>Seller</TH><TH>Total cost</TH><TH></TH></TR>"
for (tranId,type,direction,tranDate,seller,shipping,totalCost) in cursor:
typeDetail = getTranType(type,direction)
print "<TR>"
print cell(typeDetail)
print cell(tranDate)
print cell(seller)
print moneyCell(int(shipping)+int(totalCost))
print "<TD>",gotoButton('See details','purchaseDetails.py?tranId=%s'%tranId),"</TD>"
print "</TR>"
print "</TABLE>"
printFooter()