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 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 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 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 editItem(name, stock, price, image, cid, iid):
"""edit an item based on the iid that is given in the submit/POST::(name, stock, price, image, cid, iid)"""
sql.begin()
sql.cur.execute(
"UPDATE items SET item_name=?, stock=?, current_price=?, pic_url=?, cid=? WHERE iid=?",
[str(name),
int(stock),
float(price),
str(image),
int(cid),
int(iid)])
sql.commit()
sql.end()
def removeOrderItem(oid, iid):
sql.begin()
pid = sql.cur.execute("SELECT pid FROM orders WHERE oid=?", [oid])
pid = pid.fetchone()
res = sql.cur.execute(
"SELECT quantity, price FROM orderitems WHERE oid=? AND iid=?",
[oid, iid])
res = res.fetchone()
totalRemoved = res[0] * res[1]
addUserBalance(pid[0], totalRemoved)
sql.begin()
sql.cur.execute("UPDATE orders SET total = total - ? WHERE oid=?",
[totalRemoved, oid])
sql.commit()
sql.cur.execute("DELETE FROM orderitems WHERE oid=? AND iid=?", [oid, iid])
sql.commit()
sql.end()
def CreateTransAndBask(
pid, item
): #should update the pid to become keyhash from user scan, or we return value of pid gotten above?
"""Create the basket and Transactions of different items and update userbalance, stock accordingly"""
sql.begin()
sql.cur.execute(
"""INSERT INTO orders (pid,date) VALUES(?,CURRENT_TIMESTAMP)""", [pid])
oid = sql.lastId()
if len(item) >= 1:
for i in [item]:
z = len(i) - 1
while z >= 0:
x = sql.cur.execute(
"SELECT current_price FROM items WHERE iid=?", [i[z][0]])
x = x.fetchone()
#print for every item (z), oid, itemid, amount(int), price
iid = i[z][0]
quant = i[z][1]
#technically you wouldn't expect multiple entries of the same iid, so we don't have to catch those
sql.cur.execute(
"""INSERT INTO orderitems (oid,iid,quantity,price) VALUES (?,?,?,?) """,
[oid, iid, quant, x[0]])
z -= 1
sql.cur.execute("UPDATE items SET stock=stock-? WHERE iid=?",
[quant, iid])
#keep adding up all the time
some = sql.cur.execute(
"""SELECT round(SUM(price*quantity),2) FROM orderitems where oid=?""",
[oid])
rex = some.fetchone()
#sql.cur.execute("UPDATE items SET item_name=?, stock=?, current_price=? WHERE iid=?", [str(name),int(stock),float(price),int(iid)])
sql.cur.execute("UPDATE persons SET balance=balance+? WHERE pid=?",
[rex[0], pid])
sql.cur.execute("UPDATE orders SET total=? WHERE oid=?", [rex[0], oid])
result = sql.cur.execute("SELECT total FROM orders WHERE oid=?", [oid])
res = result.fetchone() #should allways only return one
sql.commit(
) #only commit after everything has been inserted on the right place
sql.end()
return res
sql.end()
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 makeAdmin(pid):
"""Up someones usertype"""
sql.begin()
sql.cur.execute("UPDATE persons SET usertype=1 WHERE pid=?", [pid])
sql.commit()
sql.end()
def removeKey(kid):
"""Remove a key, can be based on selection"""
sql.begin()
sql.cur.execute("DELETE FROM keys WHERE kid=?", [str(kid)])
sql.commit()
sql.end()
def removeOrderItems(oid):
sql.begin()
sql.cur.execute("DELETE FROM orderitems WHERE oid=?", [oid])
sql.commit()
sql.end()
def newCategory(name):
"""Creates new category"""
sql.begin()
sql.cur.execute('INSERT INTO categories (name) VALUES (?)', [name])
sql.commit()
sql.end()
def resetUserBalance(pid):
"""Sets one users balance to 0, useful for "system clean" after invoices"""
sql.begin()
sql.cur.execute("UPDATE persons SET balance = 0.00 where pid=?", [pid])
sql.commit()
sql.end()
def delCategory(cid):
"""Deletes category"""
sql.begin()
sql.cur.execute("DELETE FROM categories WHERE cid=?", [int(cid)])
sql.commit()
sql.end()
def editCategory(name, cid):
"""Edit the name of a category"""
sql.begin()
sql.cur.execute("UPDATE categories SET name=? WHERE cid=?", [name, cid])
sql.commit()
sql.end()
def removeOrdersWithUser(pid):
"""Use for removing orders linked to user::(int)"""
sql.begin()
sql.cur.execute("DELETE FROM orders WHERE pid=?", [pid])
sql.commit()
sql.end()
def delItem(iid):
"""deletes an Item, based on iid given in the submit/POST"""
sql.begin()
sql.cur.execute("DELETE FROM items WHERE iid=?", [iid])
sql.commit()
sql.end()
