def getOrders(): """Get all orders and their information""" sql.begin() result = sql.cur.execute("SELECT oid, total, date, pid FROM orders") res = result.fetchall() sql.end() return res
def keyUserList(): """Get list of kid and pid in Tables""" sql.begin() result = sql.cur.execute("SELECT kid, pid FROM keys") res = result.fetchall() sql.end() return res
def spendingsTotalStart(): """The total spending from the system since start""" sql.begin() result = sql.cur.execute("""SELECT SUM(total) FROM orders""") res = result.fetchone() sql.end() return res
def categoriesList(): """Returns list of all categories :: [(a,b)(a,b)]""" sql.begin() result = sql.cur.execute('SELECT cid, name FROM categories') res = result.fetchall() sql.end() return res
def newUser( name, sid, password, hash, keyname ): # user will not be created under a new name if SID already exists due to UNIQUE CONSTRAINT """Create a new keyhash entry, new persons (pid) and link these in KPL""" sql.begin() hash = str(hash) # insert card sql.cur.execute("INSERT INTO keys (kid, keyname) VALUES(?,?)", [hash, keyname]) sql.commit() x = sql.cur.execute("SELECT * FROM persons WHERE sid=?", [sid]) res = x.fetchone() if res == None: # insert user password = password.encode('utf-8') hashed = bcrypt.hashpw(password, bcrypt.gensalt()) sql.cur.execute( "INSERT INTO persons (name,sid,usertype,balance,password) VALUES(?,?,0,?,?)", [name, sid, 0, hashed]) sql.commit() pid = sql.lastId() else: pid = res[0] sql.cur.execute("UPDATE keys SET pid=? WHERE kid=?", [pid, hash]) sql.commit() sql.end()
def getUser(pid): sql.begin() result = sql.cur.execute( "SELECT pid, sid, name, balance FROM persons where pid=?", [pid]) res = result.fetchone() sql.end() return res
def removeAdmin(pid): """Down someones usertype""" if len(adminList()) > 1: sql.begin() sql.cur.execute("UPDATE persons SET usertype=0 WHERE pid=?", [pid]) sql.commit() sql.end()
def getCategory(cid): sql.begin() result = sql.cur.execute('SELECT cid, name FROM categories WHERE cid=?', [int(cid)]) res = result.fetchone() sql.end() return res
def spendingsTotal(): """The total spending from the system since last RESET""" sql.begin() result = sql.cur.execute("""SELECT SUM(balance) FROM persons""") res = result.fetchone() sql.end() return res
def addUserBalance(pid, add): """Sets one users balance to 0, useful for "system clean" after one invoice""" sql.begin() sql.cur.execute("UPDATE persons SET balance = balance - ? where pid=?", [add, pid]) sql.commit() sql.end()
def editUser(name, balance, sid, pid): sql.begin() sql.cur.execute( "UPDATE persons SET name=?, sid=?, balance=? WHERE pid=?", [str(name), str(sid), float(balance), int(pid)]) sql.commit() sql.end()
def getItems(): """Gives an array of all items :: () -> [(a,b,c),(a,b,c)]""" sql.begin() result = sql.cur.execute( 'SELECT iid, item_name, stock, current_price, pic_url, cid FROM items') res = result.fetchall() sql.end() return res
def adminList(): """Get list of all current admins""" sql.begin() result = sql.cur.execute( "SELECT pid,name,sid,balance FROM persons WHERE usertype=1") res = result.fetchall() sql.end() return res
def getStats(): """Return should be: most sold item, least sold item, cheapest item, average money spend, most items of most sold, number of different snacks""" """ First (iid,item_name,quant) is Most sold Second (iid,item_name,quant) is least sold Third (iid,item_name,current_price) is cheapest item Fourth (average order price) is avg_order_price Fifth (pid, username, quant) user who ate most of most sold item Sixth (number of different items in system) """ res = [] sql.begin() result = sql.cur.execute("""SELECT * FROM orderitems""") z = (result.fetchone()) if z == None: return None result = sql.cur.execute( """SELECT items.iid, item_name, SUM(orderitems.quantity) as quant FROM items, orderitems, orders WHERE items.iid = orderitems.iid AND orders.oid = orderitems.oid GROUP by items.iid order by quant desc """) res.append((result.fetchone())) x = res[0][0] result = sql.cur.execute( """SELECT items.iid, item_name, SUM(orderitems.quantity) as quant FROM items, orderitems, orders WHERE items.iid = orderitems.iid AND orders.oid = orderitems.oid GROUP by items.iid order by quant asc """) res.append((result.fetchone())) result = sql.cur.execute("""SELECT iid, item_name, MIN(current_price) FROM items """) res.append((result.fetchone())) result = sql.cur.execute("""SELECT AVG(total) FROM orders """) res.append((result.fetchone())) result = sql.cur.execute( """SELECT persons.pid, persons.name, SUM(orderitems.quantity) as quantitem FROM persons, orders, orderitems WHERE persons.pid = orders.pid AND orderitems.iid = ? AND orders.oid = orderitems.oid GROUP BY persons.pid order by quantitem desc """, [x]) res.append((result.fetchone())) result = sql.cur.execute("SELECT COUNT(iid) FROM items") res.append((result.fetchone())) sql.end() return res
def getLinkedKeys(pid): """Gives info of keys for user:: (int pid) --> (pid, name, keyname)""" sql.begin() results = sql.cur.execute( "SELECT persons.pid, name, keyname FROM keys, persons WHERE keys.pid = persons.pid AND persons.pid=?", [pid]) res = results.fetchall() sql.end() return res
def getFilterdItems(cid): """Gives an array of all items based on Category:: int(f) -> [(a,b,c,d,e,f),(a,b,c,d,e,f)]""" sql.begin() result = sql.cur.execute( 'SELECT iid, item_name, stock, current_price, pic_url, cid FROM items WHERE cid=?', [int(cid)]) res = result.fetchall() sql.end() return res
def getItem(iid): """Gives info of One item:: int(a) -> (a, b, c, d, e, f)""" sql.begin() result = sql.cur.execute( 'SELECT iid, item_name, stock, current_price, pic_url, cid FROM items WHERE iid=?', [int(iid)]) res = result.fetchone() sql.end() return res
def newItem(name, stock, price, image, cid): """Insert new item, iid should be created automatically by the DB::(name, stock, price, image, cid)""" sql.begin() sql.cur.execute( 'INSERT INTO items (item_name, stock, current_price, pic_url, cid) VALUES (?, ?, ?, ?, ?)', [str(name), int(stock), price, str(image), int(cid)]) sql.commit() sql.end()
def userList(): """Fetch list of /normal/ users""" sql.begin() result = sql.cur.execute( "SELECT pid, name, sid, balance, usertype FROM persons WHERE usertype=0" ) res = result.fetchall() sql.end() return res
def spendingsByUser(): """Get list of spendings per user for last time since RESET""" sql.begin() result = sql.cur.execute("""SELECT persons.pid, persons.name,persons.balance FROM persons """) res = result.fetchall() sql.end() return res
def getSQLDump(): sql.begin() filename = 'dump.sql' path = 'template_static/sql/{}'.format(filename) sql_out = open(path, 'w') with sql_out: for line in sql.con.iterdump(): sql_out.write('%s\n' % line) sql.end() emit('sql', {'data': filename})
def getUsers(): """Mainly debug for quick overview of data to test allowed inputs for other queries""" sql.begin() result = sql.cur.execute( '''SELECT kid, persons.pid, persons.name,persons.sid,persons.balance, usertype FROM persons, keys WHERE keys.pid = persons.pid ''') res = result.fetchall() sql.end() return res
def CheckInsOuts(date) : """Check what has gone in (stock) and what has gone out (from transactions since x date)""" sql.begin() result = sql.cur.execute("""SELECT items.stock, SUM(transactions.quantity) FROM items, orderitems WHERE items.iid = orderitems.iid AND date >= datetime(?) GROUP BY items.iid"""[date]) res = result.fetchone() sql.end() return res
def removeOrder(oid): """Removes Order :: (int)""" sql.begin() order = sql.cur.execute("SELECT total, pid FROM orders WHERE oid=?", [oid]) order = order.fetchone() addUserBalance(order[1], order[0]) sql.begin() sql.cur.execute("DELETE FROM orders WHERE oid=?", [oid]) sql.commit() sql.end() removeOrderItems(oid)
def getUserOrders(pid): """Get orders for pid:: (int)--> [(pid,name,oid,total,date)]""" sql.begin() result = sql.cur.execute( """SELECT persons.pid, name, oid, total, date FROM persons, orders WHERE orders.pid = persons.pid and persons.pid=?""", [pid]) res = result.fetchall() sql.end() return res
def spendingsByUserTotal(): """Spendings by ALL users in system since start of system""" sql.begin() result = sql.cur.execute("""SELECT DISTINCT orders.pid, persons.name, SUM(orders.total) FROM orders, persons where persons.pid = orders.pid GROUP BY orders.pid """) res = result.fetchall() sql.end() return res
def UserCheck(kid): """Select user data from keyhash recieved""" sql.begin() result = sql.cur.execute( """SELECT persons.name,persons.sid,persons.balance from persons, keys WHERE kid = ? AND keys.pid = persons.pid""", [kid]) res = result.fetchall() sql.end() return res
def getOrderItems(oid): """Get all items for an oid""" sql.begin() result = sql.cur.execute( """SELECT oid, orderitems.iid,items.item_name, quantity, price FROM items, orderitems WHERE orderitems.oid = ? and items.iid = orderitems.iid""", [oid]) res = result.fetchall() sql.end() return res
def getUserId(hash): sql.begin() sql.cur.execute("SELECT pid FROM keys WHERE kid=?", [str(hash)]) pid = sql.cur.fetchone() if pid == None: sql.end() return None else: sql.cur.execute("SELECT * FROM persons WHERE pid=?", [pid[0]]) person = sql.cur.fetchone() sql.end() return person
def loginUser(sid, password): sql.begin() res = sql.cur.execute("SELECT * FROM persons WHERE sid=?", [sid]) person = res.fetchone() sql.end() if person != None: password = password.encode('utf-8') if bcrypt.hashpw(password, person[5]) == person[5]: return person else: return None else: return None