def getUserAvailable():
sql = "select * from users u where u.id not in (select t.id_user from t_tree t)"
conn = mysql.connect()
cursor = conn.cursor(pymysql.cursors.DictCursor)
cursor.execute(sql)
user = cursor.fetchall()
return user
def getAllUsers(self):
conn = mysql.connect()
cursor = conn.cursor(pymysql.cursors.DictCursor)
sql = "select * from users"
cursor.execute(sql)
user = cursor.fetchall()
return user
def getTree():
sql = "select * from t_tree tr join users u on u.id = tr.id_user"
conn = mysql.connect()
cursor = conn.cursor(pymysql.cursors.DictCursor)
cursor.execute(sql)
user = cursor.fetchall()
return user
def searchParent(id):
if (id is not None):
sql = "select * from t_tree t join users u on u.id = t.id_parent where t.id_user=%s"
id = id
conn = mysql.connect()
cursor = conn.cursor(pymysql.cursors.DictCursor)
cursor.execute(sql, id)
user = cursor.fetchone()
return user
def searchUserNetwork(self, id):
if (id is not None):
sql = "select * from t_tree t where t.id_user=%s"
id = id
conn = mysql.connect()
cursor = conn.cursor(pymysql.cursors.DictCursor)
cursor.execute(sql, id)
user = cursor.fetchone()
return user
def searchNetworkDown(id):
if (id is not None):
user = TreeModel.searchUserNetwork(id)
data = (user['Lnum'], user['Rnum'])
sql = "select * from t_tree t join users u on u.id = t.id_user where t.Lnum >= %s and t.Rnum <= %s"
conn = mysql.connect()
cursor = conn.cursor(pymysql.cursors.DictCursor)
cursor.execute(sql, data)
tree = cursor.fetchall()
return tree
def saveUser(req):
if (req is not None):
try:
sql = "insert into users(username,fullname) values (%s,%s)"
data = (req.form['username'], req.form['fullname'])
conn = mysql.connect()
cursor = conn.cursor()
cursor.execute(sql, data)
conn.commit()
return 'success'
except ex:
return ex
def updateLnumRnum(rnum):
if (rnum is not None):
try:
rNum = rnum
conn = mysql.connect()
sql = "select * from t_tree t where t.Lnum >= %s and t.Rnum >= %s"
cursor = conn.cursor(pymysql.cursors.DictCursor)
cursor.execute(sql, rNum, rNum)
for lr in cursor:
data = ((lr['Lnum'] + 2), (lr['Rnum'] + 2), lr['id'])
sqlUpdateLnumRnum = "update t_tree set Lnum = %s, Rnum = %s where id= %s"
cursorUpdate = conn.cursor()
cursorUpdate.execute(sqlUpdateLnumRnum, data)
conn.commit()
return 'success'
except:
return 'Error'
def insertTree(req):
if (req is not None):
lnum = req.form['rnum']
rnum = int(req.form['rnum']) + 1
idparent = req.form['idparent']
iduser = req.form['user']
try:
data = (iduser, idparent, lnum, rnum)
sql = "insert into t_tree (id_user, id_parent,Lnum,Rnum) values (%s,%s,%s,%s)"
conn = mysql.connect()
cursor = conn.cursor(pymysql.cursors.DictCursor)
cursor.execute(sql, data)
conn.commit()
TreeModel.updateLnumRnum(rnum)
return 'success'
except ex:
return ex
def add_stock():
conn = None
cursor = None
try:
request_obj = request.get_json()
stock_name = request_obj['stock_name']
conn = mysql.connect()
cursor = conn.cursor(pymysql.cursors.DictCursor)
cursor.execute("INSERT IGNORE INTO stock_list (name) VALUES (%s)",
stock_name)
rowcount = cursor.rowcount
conn.commit()
ErrorMsg.InsertSuccess["data"] = '{} Row Successfully Inserted'.format(
rowcount)
return ErrorMsg.InsertSuccess
except Exception as e:
print(e)
finally:
cursor.close()
conn.close()
def update_trade():
conn = None
cursor = None
try:
request_obj = request.get_json()
rate = request_obj['rate']
trade = request_obj['trade']
quantity = request_obj['quantity']
date = request_obj['date']
stock_name = request_obj['stock_name']
trade_id = request_obj['trade_id']
conn = mysql.connect()
cursor = conn.cursor(pymysql.cursors.DictCursor)
if type(rate) is not int or type(quantity) is not int:
return ErrorMsg.NonIntegerValueError
if rate < 0 or quantity < 0:
return ErrorMsg.NegativeValueError
if trade not in ['BUY', 'SELL']:
return ErrorMsg.TradeOptionSupport
cursor.execute("SELECT id from stock_list WHERE name=%s", stock_name)
rows = cursor.fetchall()
if len(rows) != 0:
stock_id = rows[0]['id']
cursor.execute(
"UPDATE user_trade set rate=%s,trade=%s,quantity=%s,date=%s,\
stock_id= %s where id =%s",
(rate, trade, quantity, date, stock_id, trade_id))
rowcount = cursor.rowcount
conn.commit()
ErrorMsg.UpdateSuccess[
"data"] = '{} Row Successfully Updated'.format(rowcount)
return ErrorMsg.UpdateSuccess
else:
return ErrorMsg.StockNotAvailable
except Exception as e:
print(e)
finally:
cursor.close()
conn.close()
def remove_trade():
conn = None
cursor = None
try:
request_obj = request.get_json()
trade_id = request_obj['trade_id']
conn = mysql.connect()
cursor = conn.cursor(pymysql.cursors.DictCursor)
cursor.execute("DELETE FROM user_trade WHERE id=%s", trade_id)
rows = cursor.fetchall()
rowcount = cursor.rowcount
conn.commit()
ErrorMsg.RemovedSuccess["data"] = '{} Row Deleted'.format(rowcount)
if rowcount == 0:
ErrorMsg.RemovedSuccess["success"] = False
ErrorMsg.RemovedSuccess[
"description"] = 'Given Trade id not Available'
return ErrorMsg.RemovedSuccess
except Exception as e:
print(e)
finally:
cursor.close()
conn.close()
def portfolio(portfolio_type=None):
conn = None
cursor = None
try:
conn = mysql.connect()
cursor = conn.cursor(pymysql.cursors.DictCursor)
portfolio_data = {}
if portfolio_type == 'returns' or portfolio_type is None:
cursor.execute(
"select * from stock_list stock inner join user_trade usr_trade on stock.id=usr_trade.stock_id"
)
rows1 = cursor.fetchall()
stock_data = []
for result in rows1:
stock_data.append(result)
portfolio_data['stocks'] = stock_data
if portfolio_type == 'holdings' or portfolio_type is None:
cursor.execute("select buy_average.name,buy_avg,sum_quant from\
(select name,AVG(rate) as buy_avg from stock_list stock inner join user_trade usr_trade\
on stock.id=usr_trade.stock_id where usr_trade.trade='BUY' group by name ) buy_average \
inner join (select name,SUM(quantity * IF(trade = 'SELL', -1, 1)) as sum_quant from \
stock_list stock inner join user_trade usr_trade on stock.id=usr_trade.stock_id group by name ) \
sum_quantity on buy_average.name=sum_quantity.name")
rows2 = cursor.fetchall()
holdings = []
for result in rows2:
result["buy_avg"] = round(result["buy_avg"], 0)
holdings.append(result)
portfolio_data['holdings'] = holdings
response_data = {"success": True, 'data': portfolio_data}
return json.dumps(response_data, indent=4, sort_keys=True, default=str)
except Exception as e:
print(e)
finally:
cursor.close()
conn.close()