Entry 3748

#!/usr/bin/env python2.5
# import CGI and debugging modules
import cgi
import cgitb
cgitb.enable()
# import the hashing module
import md5 
# import MySQL connection module
import MySQLdb

# return cursor for querying database
def createCursor():
	db = MySQLdb.connect(host='localhost',
                     user='samgar_spoonful',
                     passwd='1234',
                     db='samgar_spoonful',)
	return db.cursor()

# create new order and return ID of the new order
def newOrder(name, c):
	sql = 'INSERT INTO orders (name) VALUES("%s")' % (name) 
	c.execute(sql)
	# use MySQL function to get ID of last inserted item
	sql = 'SELECT LAST_INSERT_ID()'
	c.execute(sql)
	r = c.fetchone()[0]
	return int(r)

# return list of category IDs in database
def countCats(c):
	sql = 'SELECT catID FROM categories WHERE active=1'
	c.execute(sql)
	# create new list
	l = []
	n = c.execute(sql)
	# append each of the category IDs to the list
	for i in range(int(n)):
		l.append(int(c.fetchone()[0]))
	return l
	
# return name of category with given ID
def catName(i, c):
	sql = 'SELECT name FROM categories WHERE catID=%i' % (i)
	c.execute(sql)
	r = c.fetchone()[0]
	return str(r)
	
# return items in given category
def getItems(c, cat=0):
	# select all items
	if not cat:
		sql = 'SELECT itemID FROM menuItems WHERE active=1'
	# select items from given category only
	else:
		sql = 'SELECT itemID FROM menuItems WHERE catID=%i AND active=1' % (cat)
	l = []
	n = c.execute(sql)
	# append item IDs to list
	for i in range(n):
		l.append(c.fetchone()[0])
	return l
	
# return boolean of whether item has size variable or not
def sizeCheck(n, c):
	sql = 'SELECT size FROM menuItems WHERE itemID = %i' % (n)
	c.execute(sql)
	r = c.fetchone()[0]
	return r

# return name of item with given ID
def itemName(i, c):
	sql = 'SELECT name from menuItems WHERE itemID = %i' % (i)
	c.execute(sql)
	r = c.fetchone()[0]
	return r

# return price of item with given ID	
def itemPrice(i, c):
	sql = 'SELECT price from menuItems WHERE itemID = %i' % (i)
	c.execute(sql)
	r = c.fetchone()[0]
	return r

# return description of item with given ID
def itemDesc(i, c):
	sql = 'SELECT description from menuItems WHERE itemID = %i' % (i)
	c.execute(sql)
	r = c.fetchone()[0]
	return r
	
# return boolean of whether item with given ID is out of stock or not
def outOfStock(i, c):
	sql = 'SELECT outOfStock from menuItems WHERE itemID = %i' % (i)
	c.execute(sql)
	r = int(c.fetchone()[0])
	return r
	
# check if item with ItemID is already in cart with given OrderID
def cartCheck(i, o, c):
	sql = 'SELECT COUNT(*) FROM orderedItems WHERE orderID = %i AND itemID = %i' % (o, i)
	c.execute(sql)
	r = c.fetchone()[0]
	# if item is in cart...
	if r > 0:
		sql = 'SELECT quantity FROM orderedItems WHERE orderID = %i AND itemID = %i' % (o, i)
		c.execute(sql)
		r = c.fetchone()[0]
		# ...return quantity
		return int(r)
	# else return false
	else:
		return False

# add item to cart with given ItemID and given OrderID
def addItem(i, o, c):	
	now = cartCheck(i, o, c)
	# if item is in cart and < 9, increase quantity
	if now:
		if now < 9:
			sql = 'UPDATE orderedItems SET quantity=%i Where itemID=%i AND orderID=%i' % (now+1, i, o)
			c.execute(sql)
	# else add to cart
	else:
		sql = 'INSERT INTO orderedItems (orderID, itemID, quantity) VALUES (%i, %i, 1)' % (o, i)
		c.execute(sql)
	
# get item IDs of items in cart
def getOrder(c, o):
	sql = 'SELECT itemID FROM orderedItems WHERE orderID=%i' % (o)
	l = []
	n = c.execute(sql)
	for i in range(n):
		l.append(c.fetchone()[0])
	return l		
	
# return quantity of item with given ItemID in order with given OrderID
def itemQuantity(i, o, c):
	sql = 'SELECT quantity FROM orderedItems WHERE orderID = %i AND itemID = %i' % (o, i)
	c.execute(sql)
	r = c.fetchone()[0]
	return int(r)
	
# decrease quantity of item in cart with given itemID and given orderID
def downItem(i, o, c):
	now = itemQuantity(i, o, c)
	if now > 1:
		sql = 'UPDATE orderedItems SET quantity=%i Where itemID=%i AND orderID=%i' % (now-1, i, o)
		c.execute(sql)
	return itemQuantity(i, o, c)
	
# remove item with given ItemID from cart with given OrderID
def delItem(i, o, c):
	sql = 'DELETE FROM orderedItems WHERE orderID = %i AND itemID = %i' % (o, i)
	c.execute(sql)
	
# return total price of all items in cart with given OrderID
def getTotal(o, c):
	sql = "SELECT SUM(orderedItems.quantity * menuItems.price) FROM orderedItems, menuItems WHERE orderedItems.orderID = %i AND orderedItems.itemID = menuItems.itemID" % (o)
	c.execute(sql)
	n = c.fetchone()[0]
	if n:
		r = float(n)
		# return price with 2 decimal places
		t = "%.2f" % (r)
		return t
	else:
		return '0'
	
# return sub total of item with given ItemID from cart with given OrderID
def getSubTotal(i, o, c):
	sql = "SELECT menuItems.price * orderedItems.quantity FROM menuItems, orderedItems WHERE orderedItems.itemID = menuItems.itemID AND orderedItems.itemID = %i AND orderedItems.orderID = %i" % (i, o)
	c.execute(sql)
	r = float(c.fetchone()[0])
	# return price with 2 decimal places
	t = "%.2f" % (r)
	return t
	
# check whether order has been paid or not
def orderCheck(o, c):
	sql = "SELECT paid FROM orders WHERE orderID=%i" % (o)
	c.execute(sql)
	r = int(c.fetchone()[0])
	return r
	
# mark order with given OrderID as paid
def pay(o, c):
	sql = 'UPDATE orders SET paid=1 WHERE orderID=%i' % (o)
	c.execute(sql)

# mark given order as finished and input extra details	
def finishOrder(o, time, p, email, c):
	sql = "UPDATE orders SET time='%s', phone='%s', email='%s' WHERE orderid=%i" % (time, p, email, o)
	c.execute(sql)

# update menu with new information
def updateMenu(d, c):
	sql = "UPDATE menuItems SET name='%s', price='%s', description='%s' WHERE itemID='%s'" % (d['name'], d['price'], d['description'], d['id'])
	c.execute(sql)
	if d.has_key('size'):
		sql = 'UPDATE menuItems SET size="%s" WHERE itemID="%s"' % (d['size'], d['id'])
		c.execute(sql)
	if d.has_key('outOfStock'):
		sql = 'UPDATE menuItems SET outOfStock="%s" WHERE itemID="%s"' % (d['outOfStock'], d['id'])
		c.execute(sql)

# create new menu item
def newItem(d, c):
	sql = "INSERT INTO menuItems(name, price, description, catID) VALUES ('%s', '%s', '%s', '%s')" % (d['name'], d['price'], d['description'], d['catID'])
	c.execute(sql)
	if d.has_key('size'):
		sql = 'UPDATE menuItems SET size="%s" WHERE itemID=@@IDENTITY' % (d['size'])
		c.execute(sql)

# remove given item from menu
def deleteItem(i, c):
	sql = 'UPDATE menuItems SET active=0 WHERE itemID=%s' % (i)
	c.execute(sql)

# create new menu category
def newCat(n, c):
	sql = 'INSERT INTO categories(name) VALUES ("%s")' % (n)
	c.execute(sql)

# remove category from menu
def delCat(n, c):
	sql = 'UPDATE categories SET active=0 WHERE catID = "%s"' % (n)
	c.execute(sql)
	sql = 'UPDATE menuItems SET active=0 WHERE catID = "%s"' % (n)
	c.execute(sql)

def getUsers(c):
	sql = 'SELECT userID, username FROM users'
	c.execute(sql)
	l = [[int(x[0]), x[1]] for x in c.fetchall()]
	return l
	
def newUser(u, p, c):
	sql = 'INSERT INTO users(username, passHash) VALUES ("%s", "%s")' % (u, p)
	c.execute(sql)

def delUser(i, c):
	sql = 'DELETE FROM users WHERE userID = %s' % i
	c.execute(sql)

def updateUser(id, old, new, c):
	if not validate(id, old, c):
		print '0'
	else:
		sql = 'UPDATE users SET passHash="%s" WHERE userID=%s' % (md5.md5(new).hexdigest(), id)
		c.execute(sql)
		print '1'

def validate(id, new, c):
	sql = 'SELECT passHash FROM users WHERE userID=%s' % (id)
	c.execute(sql)
	old = c.fetchone()[0]
	new = md5.md5(new).hexdigest()
	if old == new:
		return True
	else:
		return False
	

# if script is navigated to, rather than imported
if __name__ == '__main__':
	c = createCursor()
	print "Content-type:text/html\r\n\r\n" 
	# fetch data which was passed to page via cgi module
	form = cgi.FieldStorage()
	
	# fetch what task is needed
	cmd = form['cmd'].value
	# fetch other variables if present
	if form.has_key('itemID'):
		itemID = int(form['itemID'].value)
	if form.has_key('orderID'):
		orderID = int(form['orderID'].value)
	
	# run the necessary function
	if cmd == 'add':
		addItem(itemID, orderID, c)
		o = cartCheck(itemID, orderID, c)
		print o
		
	elif cmd == 'down':
		n = downItem(itemID, orderID, c)
		if n < 1:
			delItem(itemID, orderID, c)
			print '0'
		else:
			print n
				
	elif cmd == 'del':
		delItem(itemID, orderID, c)
		print 0

	elif cmd == 'total':
		print getTotal(orderID, c)

	elif cmd == 'subtotal':
		print getSubTotal(itemID, orderID, c)

	elif cmd == 'finish':
		time = form['time'].value
		if 'phone' in form:
			phone = form['phone'].value
		else:
			phone = ''
		if 'email' in form:
			email = form['email'].value
		else:
			email = ''
		finishOrder(orderID, time, phone, email, c)
		print 'finished'

	elif cmd == 'update':
		d = {
		'id': form['id'].value,
		'name': form['name'].value,
		'price': form['price'].value,
		'description': form['description'].value
		}
		if form.has_key('size'):
			d['size'] = form['size'].value
		if form.has_key('outOfStock'):
			d['outOfStock'] = '1'
		else:
			d['outOfStock'] = '0'
		
		for i in d.keys():
			d[i] = " ".join(d[i].split('+'))
		
		updateMenu(d, c)

	elif cmd == "newItem":
		d = {
		'name': form['name'].value,
		'price': form['price'].value,
		'description': form['description'].value,
		'catID': form['cat'].value
		}
		if form.has_key('size'):
			d['size'] = form['size'].value
		
		for i in d.keys():
			d[i] = " ".join(d[i].split('+'))
		d['price'] = float(d['price'])
		newItem(d, c)
		
		for j in range(int(form['num'].value)):
			i = str(j+1)
			d = {
			'name': form['name_' + i].value,
			'price': form['price_' + i].value,
			'description': form['description_' + i].value,
			'catID': form['cat_' + i].value
			}
			
			if form.has_key('size_' + i):
				d['size'] = form['size_' + i].value

			for i in d.keys():
				d[i] = " ".join(d[i].split('+'))
			d['price'] = float(d['price'])
			print d
			newItem(d, c)
			
	elif cmd == "deleteItem":
		deleteItem(itemID, c)

	elif cmd == 'newCat':
		name = form['name'].value
		newCat(name, c)

	elif cmd == 'delCat':
		name = form['cat'].value
		delCat(name, c)
	elif cmd == 'newUser':
		user = form['user'].value
		passw = md5.md5(form['pass'].value).hexdigest()
		newUser(user, passw, c)
	elif cmd == 'delUser':
		uID = form['id'].value
		delUser(uID, c)
	elif cmd == 'editUser':
		new = form['newPass'].value
		old = form['oldPass'].value
		id = form['id'].value
		
		updateUser(id, old, new, c)