def get_products():
con = dbconn.get_new_connection()
with con:
cur = con.cursor()
cur.execute("SELECT * FROM product")
rows = cur.fetchall()
return rows
def get_shop_types():
con = dbconn.get_new_connection()
with con:
cur = con.cursor()
cur.execute("SELECT * FROM shop_type")
rows = cur.fetchall()
return rows
def sell_stock(sellingCorpId, targetCorpId, quantity):
sellerCorp = corp_api.get_corp_by_id(sellingCorpId)
sellerMoney = sellerCorp['money']
sellerShares = shares_api.get_corp_shares_for_corp_id(
sellingCorpId, targetCorpId)
quantityOfShares = sellerShares['quantity']
targetCorp = corp_api.get_corp_by_id(targetCorpId)
targetCorpSharePrice = targetCorp['share_price']
totalAmount = targetCorpSharePrice * int(quantity)
if quantityOfShares >= quantity:
con = dbconn.get_new_connection()
with con:
cur = con.cursor()
cur.execute(
"DELETE FROM shares WHERE corporation_id = %s AND owning_corporation_id = %s LIMIT %s",
(targetCorpId, sellingCorpId, quantity))
cur.execute(
"UPDATE corporation SET money = money + %s WHERE id = %s",
(totalAmount, sellingCorpId))
cur.execute(
"UPDATE corporation SET momentum = momentum + %s WHERE id = %s",
(quantity, targetCorpId))
def get_users():
con = dbconn.get_new_connection()
with con:
cur = con.cursor()
cur.execute("SELECT id, username, email FROM user")
rows = cur.fetchall()
return rows
def get_product_by_id(id):
con = dbconn.get_new_connection()
with con:
cur = con.cursor()
cur.execute("SELECT * FROM product WHERE id = %s", id)
rows = cur.fetchone()
return rows
def get_user_by_id(id):
con = dbconn.get_new_connection()
with con:
cur = con.cursor()
cur.execute("SELECT id, username, email FROM user WHERE id = %s", id)
row = cur.fetchone()
row['corp'] = corp_api.get_corp_by_id(row['id'])
return row
def get_corp_shares_for_corp_id(id, corpId):
con = dbconn.get_new_connection()
with con:
cur = con.cursor()
cur.execute(
"SELECT count(*) as quantity FROM shares WHERE owning_corporation_id = %s AND corporation_id = %s",
(id, corpId))
row = cur.fetchone()
return row
def get_corp_by_id(id):
con = dbconn.get_new_connection()
with con:
cur = con.cursor()
cur.execute(
"SELECT id, name, ticker, money, share_price FROM corporation WHERE id = %s",
id)
rows = cur.fetchone()
rows['shops'] = shop_api.get_shops_by_corp_id(rows['id'])
return rows
def get_userid_from_session(sessionid):
con = dbconn.get_new_connection()
with con:
cur = con.cursor()
cur.execute("SELECT user_id FROM sessions WHERE session_id = %s",
(sessionid, ))
rows = cur.fetchone()
if rows:
return rows['user_id']
return None
def create_session(userid):
con = dbconn.get_new_connection()
with con:
sessionid = str(uuid.uuid4())
cur = con.cursor()
cur.execute("INSERT INTO sessions VALUES (%s, %s, NOW())",
(sessionid, userid))
cur.close()
con.commit()
return sessionid
def is_valid_session(sessionid, userid):
con = dbconn.get_new_connection()
with con:
cur = con.cursor()
cur.execute(
"SELECT count(*) as cnt FROM sessions WHERE session_id = %s and user_id = %s",
(sessionid, userid))
row = cur.fetchone()
if row['cnt'] > 0:
return True
return False
def get_all_shares_for_corp_id(id):
con = dbconn.get_new_connection()
with con:
cur = con.cursor()
cur.execute(
"""SELECT corporation_id, count(*) as quantity, round(avg(purchase_price), 2) as purchase_price,
c.share_price, round((c.share_price - avg(purchase_price)) * count(*), 2) as net_gain
FROM shares s
JOIN corporation c on c.id = s.corporation_id WHERE owning_corporation_id = %s GROUP BY corporation_id, date_created""",
id)
rows = cur.fetchall()
return rows
def login(username, password):
con = dbconn.get_new_connection()
with con:
cur = con.cursor()
passwordhash = hashlib.sha224(password).hexdigest()
cur.execute(
"SELECT id FROM user WHERE username = %s and password_hash = %s",
(username, passwordhash))
row = cur.fetchone()
if row:
sessionid = create_session(row['id'])
return sessionid
return None
def get_shops_by_corp_id(id):
con = dbconn.get_new_connection()
with con:
cur = con.cursor()
cur.execute(
"SELECT corporation_id, name, shop_type_id, product_id, product_start, product_complete FROM shop WHERE corporation_id = %s",
id)
rows = cur.fetchall()
for row in rows:
if row['product_id']:
row['product'] = product_api.get_product_by_id(
row['product_id'])
else:
row['product'] = None
return rows
def buy_stock(buyingCorpId, targetCorpId, quantity):
buyerCorp = corp_api.get_corp_by_id(buyingCorpId)
buyerMoney = buyerCorp['money']
targetCorp = corp_api.get_corp_by_id(targetCorpId)
targetCorpSharePrice = targetCorp['share_price']
totalAmount = targetCorpSharePrice * int(quantity)
if buyerMoney >= totalAmount:
con = dbconn.get_new_connection()
with con:
cur = con.cursor()
for x in range(0, quantity):
cur.execute(
"INSERT INTO shares VALUES (null, %s, %s, %s, NOW())",
(targetCorpId, buyingCorpId, targetCorpSharePrice))
cur.execute(
"UPDATE corporation SET money = money - %s WHERE id = %s",
(totalAmount, buyingCorpId))
cur.execute(
"UPDATE corporation SET momentum = momentum - %s WHERE id = %s",
(quantity, targetCorpId))
import dbconn
#UPDATE STOCKS
con = dbconn.get_new_connection()
with con:
cur = con.cursor()
cur.execute("UPDATE corporation SET momentum = momentum - sign(momentum)")
def destroy_session(sessionid):
con = dbconn.get_new_connection()
with con:
cur = con.cursor()
cur.execute("DELETE FROM sessions WHERE session_id = %s", sessionid)
return True