class User_stock_value_dao:
db = None
def __init__(self):
self.db = PDO().get_connection(LINK_HEADERS.DB_HOST,
LINK_HEADERS.DB_USER,
LINK_HEADERS.DB_PASSWORD,
LINK_HEADERS.DB_NAME)
def insert(self, user, profit, total_stock_values):
self.db.query("insert into user_stock_value values ('%s','%s','%s')" %
(user, round(Decimal(profit), 2),
round(Decimal(total_stock_values), 2)) + ";")
def update_profit(self, user, profit):
self.db.query(
"update user_stock_value set profit=('%s') where user=('%s')" %
(round(Decimal(profit), 2), user) + ";")
def update_total_stock_values(self, user, total_stock_values):
self.db.query(
"update user_stock_value set total_stock_values=('%s') where user=('%s')"
% (round(Decimal(total_stock_values), 2), user) + ";")
def get_user_stock_value_model(self, user):
result = self.db.query(
"select * from user_stock_value where user = ('%s')" % (user) +
";")
if result:
u = User_stock_value(user, result[0]['profit'],
result[0]['total_stock_values'])
return u
class Algorithms_dao:
db = None
def __init__(self):
self.db = PDO().get_connection(LINK_HEADERS.DB_HOST, LINK_HEADERS.DB_USER, LINK_HEADERS.DB_PASSWORD, LINK_HEADERS.DB_NAME)
def select_distinct_algorithms(self):
result = self.db.query("select distinct algo_name from algorithms;")
if result:
l = []
for i in range(len(result)):
if result[i]['algo_name'] != None:
a = Algorithms(result[i]['algo_name'])
l.append(a)
return l
def insert_algorithm(self, user, algo_name, algo_id):
self.db.query("insert into algorithms(user, algo_name, algo_id) values('%s','%s','%s')"%(user, algo_name, algo_id) + ";")
def select_inactive_algorithms(self, user):
l =[]
result = self.db.query("select * from algorithms where algorithms.algo_id not in (select active_algorithms.algo_id from active_algorithms) and user=('%s')"%(user) + ";")
if result:
for i in range(len(result)):
if result[i]['algo_name'] != None:
a = Algorithms(result[i]['user'], result[i]['algo_name'], result[i]['algo_id'])
l.append(a)
return l
def select_all_algorithms(self, user):
l=[]
result = self.db.query("select * from algorithms where user=('%s')"%(user) + ";")
if result:
for i in range(len(result)):
if result[i]['algo_name'] != None:
a = Algorithms(result[i]['user'], result[i]['algo_name'], result[i]['algo_id'])
l.append(a)
return l
def delete_algorithm(self, user, algo_name):
result = self.db.query("delete from algorithms where user=('%s') and algo_name=('%s')"%(user, algo_name) + ";")
def select_used_algorithms(self, user):
l=[]
result = self.db.query("select distinct algo_name, algorithms.algo_id from algorithms, transactions where algorithms.algo_id=transactions.algo_id and algorithms.user=('%s')"%(user) + ";")
if result:
for i in range(len(result)):
a=Algorithms(user,result[i]['algo_name'],result[i]['algo_id'])
l.append(a)
return l
class User_portfolio_dao:
db = None
def __init__(self):
self.db = PDO().get_connection(LINK_HEADERS.DB_HOST,
LINK_HEADERS.DB_USER,
LINK_HEADERS.DB_PASSWORD,
LINK_HEADERS.DB_NAME)
def insert(self, user, total_portfolio, available_funds, total_deposited):
self.db.query(
"insert into user_portfolio values ('%s','%s','%s','%s')" %
(user, round(Decimal(total_portfolio), 2),
round(Decimal(available_funds), 2),
round(Decimal(total_deposited), 2)) + ";")
def update_total_portfolio(self, user, total):
self.db.query(
"update user_portfolio set total_portfolio=('%s') where user=('%s')"
% (round(Decimal(total), 2), user) + ";")
def update_available_funds(self, user, available_funds):
self.db.query(
"update user_portfolio set available_funds=('%s') where user=('%s')"
% (round(Decimal(available_funds), 2), user) + ";")
def update_total_deposited(self, user, funds):
self.db.query(
"update user_portfolio set total_deposited=('%s') where user=('%s')"
% (round(Decimal(funds), 2), user) + ";")
def get_user_portfolio_model(self, user):
result = self.db.query(
"select * from user_portfolio where user = ('%s')" % (user) + ";")
if result:
u = User_portfolio(user, result[0]['total_portfolio'],
result[0]['available_funds'],
result[0]['total_deposited'])
return u
class User_portfolio_dao:
db = None
def __init__(self):
self.db = PDO().get_connection(LINK_HEADERS.DB_HOST, LINK_HEADERS.DB_USER, LINK_HEADERS.DB_PASSWORD, LINK_HEADERS.DB_NAME)
def insert(self, user, total_portfolio, available_funds, total_deposited):
self.db.query("insert into user_portfolio values ('%s','%s','%s','%s')"%(user, round(Decimal(total_portfolio), 2), round(Decimal(available_funds), 2), round(Decimal(total_deposited),2))+";")
def update_total_portfolio(self, user, total):
self.db.query("update user_portfolio set total_portfolio=('%s') where user=('%s')"%(round(Decimal(total),2), user)+";")
def update_available_funds(self, user, available_funds):
self.db.query("update user_portfolio set available_funds=('%s') where user=('%s')"%(round(Decimal(available_funds),2), user)+";")
def update_total_deposited(self, user, funds):
self.db.query("update user_portfolio set total_deposited=('%s') where user=('%s')"%(round(Decimal(funds),2), user)+";")
def get_user_portfolio_model(self, user):
result = self.db.query("select * from user_portfolio where user = ('%s')"%(user)+";")
if result:
u = User_portfolio(user,result[0]['total_portfolio'],result[0]['available_funds'],result[0]['total_deposited'])
return u
class Sector_info_dao: #{
db_connection = None
def __init__(self):
self.db_connection = PDO().get_connection(LINK_HEADERS.DB_HOST,
LINK_HEADERS.DB_USER,
LINK_HEADERS.DB_PASSWORD,
LINK_HEADERS.DB_NAME)
def get_sector_by_symbol(self, symbol):
SQL = "SELECT sector FROM sector_info WHERE symbol='%s'"
result = self.db_connection.query(SQL % (symbol))
return result[0]['sector']
def group_by_sector(self, username):
t_dao = Transaction_dao()
c_dao = Company_dao()
distinct_stocks = t_dao.get_user_stock_list(username)
sector_volume = {}
for symbol in distinct_stocks:
company_info = c_dao.get_company_model(symbol)
volume = t_dao.get_owned_stock_model(
username, symbol, company_info.get_ask()).get_volume()
try:
sector = self.get_sector_by_symbol(symbol)
if (sector.strip() == ''): sector = "Other"
except:
sector = "Other"
if (sector not in sector_volume):
sector_volume[sector] = volume
else:
sector_volume[sector] += volume
return sector_volume
class User_stock_value_dao:
db = None
def __init__(self):
self.db = PDO().get_connection(LINK_HEADERS.DB_HOST, LINK_HEADERS.DB_USER, LINK_HEADERS.DB_PASSWORD, LINK_HEADERS.DB_NAME)
def insert(self, user, profit, total_stock_values):
self.db.query("insert into user_stock_value values ('%s','%s','%s')"%(user, round(Decimal(profit),2), round(Decimal(total_stock_values),2))+";")
def update_profit(self, user, profit):
self.db.query("update user_stock_value set profit=('%s') where user=('%s')"%(round(Decimal(profit),2), user)+";")
def update_total_stock_values(self, user, total_stock_values):
self.db.query("update user_stock_value set total_stock_values=('%s') where user=('%s')"%(round(Decimal(total_stock_values),2), user)+";")
def get_user_stock_value_model(self, user):
result = self.db.query("select * from user_stock_value where user = ('%s')"%(user)+";")
if result:
u = User_stock_value(user, result[0]['profit'],result[0]['total_stock_values'])
return u
class Active_algorithms_dao:#{
db_connection = None
def __init__(self):
self.db_connection = PDO().get_connection(LINK_HEADERS.DB_HOST, LINK_HEADERS.DB_USER, LINK_HEADERS.DB_PASSWORD, LINK_HEADERS.DB_NAME)
def execute(self, task):
SQL="""
INSERT INTO active_algorithms
(user, algo_id)
VALUES
('%s','%s')
"""
user = task.get_user()
algo_id = task.get_algo_id()
self.db_connection.query(SQL % (user, algo_id))
def stop(self, task):
SQL = "DELETE FROM active_algorithms WHERE user='******' and algo_id='%s'";
user = task.get_user()
algo_id = task.get_algo_id()
self.db_connection.query(SQL % (user, algo_id))
def select_all(self):
results = self.db_connection.query("SELECT * FROM active_algorithms;")
if results:
lst = []
for i in range(len(results)):
a = Active_algorithms(results[i]["user"], results[i]["algo_id"])
lst.append(a)
return lst
else:
return False
def get_active(self, user):
SQL = """SELECT active_algorithms.algo_id, algorithms.algo_name
FROM active_algorithms, algorithms
WHERE active_algorithms.user='******' AND algorithms.algo_id=active_algorithms.algo_id
"""
result=self.db_connection.query(SQL%(user))
return result
class Sector_info_dao:#{
db_connection = None
def __init__(self):
self.db_connection = PDO().get_connection(LINK_HEADERS.DB_HOST, LINK_HEADERS.DB_USER, LINK_HEADERS.DB_PASSWORD, LINK_HEADERS.DB_NAME)
def get_sector_by_symbol(self, symbol):
SQL="SELECT sector FROM sector_info WHERE symbol='%s'";
result=self.db_connection.query(SQL % (symbol))
return result[0]['sector']
def group_by_sector(self,username):
t_dao=Transaction_dao()
c_dao=Company_dao()
distinct_stocks=t_dao.get_user_stock_list(username)
sector_volume={}
for symbol in distinct_stocks:
company_info=c_dao.get_company_model(symbol)
volume=t_dao.get_owned_stock_model(username,symbol,company_info.get_ask()).get_volume()
try:
sector=self.get_sector_by_symbol(symbol);
if(sector.strip()==''):sector="Other"
except:
sector="Other"
if(sector not in sector_volume):
sector_volume[sector]=volume;
else:
sector_volume[sector]+=volume
return sector_volume;
def __init__(self):
self.db_connection = PDO().get_connection(LINK_HEADERS.DB_HOST,
LINK_HEADERS.DB_USER,
LINK_HEADERS.DB_PASSWORD,
LINK_HEADERS.DB_NAME)
class Login_dao: #{
db_connection = None
def __init__(self):
self.db_connection = PDO().get_connection(LINK_HEADERS.DB_HOST,
LINK_HEADERS.DB_USER,
LINK_HEADERS.DB_PASSWORD,
LINK_HEADERS.DB_NAME)
def create_user(self, profile):
SQL = """
INSERT INTO login
(username, salt, passcode, first_name, last_name)
VALUES
('%s', '%s', '%s', '%s', '%s')
"""
self.db_connection.query(SQL % profile.get_values())
def update_user(self, profile):
SQL = """
UPDATE login SET
first_name='%s',
last_name='%s'
WHERE username='******'
"""
first_name = profile.get_first_name()
last_name = profile.get_last_name()
username = profile.get_username()
self.db_connection.query(SQL % (first_name, last_name, username))
def update_passcode(self, profile):
SQL = """
UPDATE login SET
passcode='%s',
salt = '%s'
WHERE
username='******'
"""
username = profile.get_username()
passcode = profile.get_passcode()
salt = profile.get_salt()
self.db_connection.query(SQL % (passcode, salt, username))
def get_salt(self, username):
SQL = "SELECT salt FROM login WHERE username='******'"
result = self.db_connection.query(SQL % (username, ))
if (result): return str(result[0]['salt'])
return "False"
def is_valid_user(self, username, passcode):
SQL = "SELECT * FROM login WHERE username ='******' AND passcode='%s'"
result = self.db_connection.query(SQL % (username, passcode))
if (result): return True
return False
def get_user_list(self):
result = self.db_connection.query("select username from login;")
if result:
l = []
for i in range(len(result)):
lo = Login(result[i]['username'])
l.append(lo)
return l
else:
return False
class Algorithms_dao:
db = None
def __init__(self):
self.db = PDO().get_connection(LINK_HEADERS.DB_HOST,
LINK_HEADERS.DB_USER,
LINK_HEADERS.DB_PASSWORD,
LINK_HEADERS.DB_NAME)
def select_distinct_algorithms(self):
result = self.db.query("select distinct algo_name from algorithms;")
if result:
l = []
for i in range(len(result)):
if result[i]['algo_name'] != None:
a = Algorithms(result[i]['algo_name'])
l.append(a)
return l
def insert_algorithm(self, user, algo_name, algo_id):
self.db.query(
"insert into algorithms(user, algo_name, algo_id) values('%s','%s','%s')"
% (user, algo_name, algo_id) + ";")
def select_inactive_algorithms(self, user):
l = []
result = self.db.query(
"select * from algorithms where algorithms.algo_id not in (select active_algorithms.algo_id from active_algorithms) and user=('%s')"
% (user) + ";")
if result:
for i in range(len(result)):
if result[i]['algo_name'] != None:
a = Algorithms(result[i]['user'], result[i]['algo_name'],
result[i]['algo_id'])
l.append(a)
return l
def select_all_algorithms(self, user):
l = []
result = self.db.query("select * from algorithms where user=('%s')" %
(user) + ";")
if result:
for i in range(len(result)):
if result[i]['algo_name'] != None:
a = Algorithms(result[i]['user'], result[i]['algo_name'],
result[i]['algo_id'])
l.append(a)
return l
def delete_algorithm(self, user, algo_name):
result = self.db.query(
"delete from algorithms where user=('%s') and algo_name=('%s')" %
(user, algo_name) + ";")
def select_used_algorithms(self, user):
l = []
result = self.db.query(
"select distinct algo_name, algorithms.algo_id from algorithms, transactions where algorithms.algo_id=transactions.algo_id and algorithms.user=('%s')"
% (user) + ";")
if result:
for i in range(len(result)):
a = Algorithms(user, result[i]['algo_name'],
result[i]['algo_id'])
l.append(a)
return l
def __init__(self):
self.db = PDO().get_connection(LINK_HEADERS.DB_HOST, LINK_HEADERS.DB_USER, LINK_HEADERS.DB_PASSWORD, LINK_HEADERS.DB_NAME)
class History_dao:
db = None
def __init__(self):
self.db = PDO().get_connection(LINK_HEADERS.DB_HOST, LINK_HEADERS.DB_USER, LINK_HEADERS.DB_PASSWORD, LINK_HEADERS.DB_NAME)
def insert(self, user, trans_type, stock, price, total_price, volume, algo_id):
self.db.query("insert into history (user, trans_type, stock, price, total_price, volume, algo_id) values ('%s','%s','%s','%s','%s',%s,'%s')"%(user, trans_type, stock, round(Decimal(price),2), round(Decimal(total_price),2), volume, algo_id)+";")
def select_all(self, user):
result = self.db.query("select * from history where user=('%s')"%(user)+";")
if result:
l=[]
for i in range(len(result)):
h = History(user, result[i]['trans_date'], result[i]['trans_type'], result[i]['stock'], result[i]['price'], result[i]['total_price'], result[i]['volume'], result[i]['algo_id'])
l.append(h)
return l
def select_all_algo_trades(self, user):
result = self.db.query("select * from history where user=('%s') and algo_id != '0'"%(user)+";")
if result:
l=[]
for i in range(len(result)):
h = History(user, result[i]['trans_date'], result[i]['trans_type'], result[i]['stock'], result[i]['price'], result[i]['total_price'], result[i]['volume'], result[i]['algo_id'])
l.append(h)
return l
def select_all_user_trades(self, user):
result = self.db.query("select * from history where user=('%s') and algo_id = '0'"%(user)+";")
if result:
l=[]
for i in range(len(result)):
h = History(user, result[i]['trans_date'], result[i]['trans_type'], result[i]['stock'], result[i]['price'], result[i]['total_price'], result[i]['volume'], result[i]['algo_id'])
l.append(h)
return l
def select_algo_trades(self, user, algo_id):
result = self.db.query("select * from history where user=('%s') and algo_id = '%s'"%(user, algo_id)+";")
if result:
l=[]
for i in range(len(result)):
h = History(user, result[i]['trans_date'], result[i]['trans_type'], result[i]['stock'], result[i]['price'], result[i]['total_price'], result[i]['volume'], result[i]['algo_id'])
l.append(h)
return l
def get_algorithm_buy_sell_volume(self, user):
result1= self.db.query("select count(trans_type) as buy, algo_id, sum(volume) as volume from history where user=('%s') and trans_type = 'buy' group by algo_id order by algo_id"%(user) + ";")
result2= self.db.query("select count(trans_type) as sell, algo_id, sum(volume) as volume from history where user=('%s') and trans_type = 'sell' group by algo_id order by algo_id"%(user) + ";")
if result1 or result2:
l=[]
iterations=max(len(result1), len(result2))
for i in range(iterations):
ad=Algorithm_details(user, result1[i]['algo_id'],str(int(result1[i]['volume']) + int(result2[i]['volume'])), result1[i]['buy'], result2[i]['sell'])
l.append(ad)
return l
def get_distinct_traded_stocks(self, user):
result = self.db.query("select distinct stock from history where user=('%s')"%(user) + ";")
if result:
l=[]
for i in range(len(result)):
c = Company()
c.set_name(result[i]['stock'])
l.append(c)
return l
def get_all_volume_per_day(self,user):
result = self.db.query("select UNIX_TIMESTAMP(DATE(trans_date))*1000 as date, sum(volume) as volume from history where user=('%s') group by DATE(trans_date)"%(user)+";");
if result:
l = []
for i in range(len(result)):
t = Date_history()
t.set_date(result[i]['date'])
t.set_volume(result[i]['volume'])
l.append(t)
return l
else:
return False
def get_user_volume_per_day(self,user):
result = self.db.query("select UNIX_TIMESTAMP(DATE(trans_date))*1000 as date, sum(volume) as volume from history where user=('%s') and algo_id='0' group by DATE(trans_date)"%(user)+";");
if result:
l = []
for i in range(len(result)):
t = Date_history()
t.set_date(result[i]['date'])
t.set_volume(result[i]['volume'])
l.append(t)
return l
else:
return False
def get_all_algorithms_volume_per_day(self,user):
result = self.db.query("select UNIX_TIMESTAMP(DATE(trans_date))*1000 as date, sum(volume) as volume from history where user=('%s') and algo_id!='0' group by DATE(trans_date)"%(user)+";");
if result:
l = []
for i in range(len(result)):
t = Date_history()
t.set_date(result[i]['date'])
t.set_volume(result[i]['volume'])
l.append(t)
return l
else:
return False
def get_algorithm_volume_per_day(self,user,algo_id):
result = self.db.query("select UNIX_TIMESTAMP(DATE(trans_date))*1000 as date, sum(volume) as volume from history where user=('%s') and algo_id=('%s') group by DATE(trans_date)"%(user, algo_id)+";");
if result:
l = []
for i in range(len(result)):
t = Date_history()
t.set_date(result[i]['date'])
t.set_volume(result[i]['volume'])
l.append(t)
return l
else:
return False
class Company_dao:
db = None
def __init__(self):
self.db = PDO().get_connection(LINK_HEADERS.DB_HOST,
LINK_HEADERS.DB_USER,
LINK_HEADERS.DB_PASSWORD,
LINK_HEADERS.DB_NAME)
def get_all_ask(self):
result = self.db.query("select Symbol, Name, Ask from company_info;")
if result:
l = []
for i in range(len(result)):
if result[i]['Ask'] != None:
c = Company_base()
c.set_name(result[i]['Name'])
c.set_ask(result[i]['Ask'])
c.set_symbol(result[i]['Symbol'])
l.append(c)
return l
def get_list_of_company_models(self, symbols_list):
if symbols_list:
symbols_string = ""
for i in range(len(symbols_list)):
symbols_string = symbols_string + "'" + symbols_list[i] + "'"
if i != len(symbols_list) - 1:
symbols_string = symbols_string + ","
result = self.db.query(
"select ask, name, symbol from company_info where symbol in ("
+ symbols_string + ");")
if result:
l = []
for i in range(len(result)):
c = Company()
c.set_ask(result[i]['ask'])
c.set_name(result[i]['name'])
c.set_symbol(result[i]['symbol'])
l.append(c)
return l
else:
return False
def get_company_model(self, symbol):
result = self.db.query(
"select Ask, Name, Volume from company_info where symbol = ('%s')"
% (symbol) + ";")
if result:
c = Company()
c.set_ask(result[0]['Ask'])
c.set_name(result[0]['Name'])
#c.set_percent_change(result[0]['PercentChange'])
c.set_symbol(symbol)
#c.set_avg_daily_volume(result[0]['AverageDailyVolume'])
c.set_volume(result[0]['Volume'])
return c
def get_all_companies_percentchange(self):
result = self.db.query(
"select symbol, PercentChange from company_info WHERE PercentChange IS NOT NULL AND PercentChange!='None';"
)
if result:
l = []
for i in range(len(result)):
c = Active_company()
c.set_symbol(result[i]['symbol'])
#r = result[i]['PercentChange'].translate(None, '+%')
#c.set_percent_change(r)
c.set_percent_change(
result[i]['PercentChange']) #.translate(None, '+%'))
#c.set_volume(result[i]['Volume'])
#c.set_avg_daily_volume(result[i]['AverageDailyVolume'])
l.append(c)
return l
def get_all_companies_volume(self):
result = self.db.query("select symbol, Volume from company_info;")
if result:
l = []
for i in range(len(result)):
c = Active_company()
c.set_symbol(result[i]['symbol'])
c.set_volume(result[i]['Volume'])
l.append(c)
return l
def get_all_companies_averagedailyvolume(
self
): #NOT TO SELF: fix the averagedailyvolume and avg_daily_volume difference
result = self.db.query(
"select symbol, AverageDailyVolume from company_info;")
if result:
l = []
for i in range(len(result)):
c = Active_company()
c.set_symbol(result[i]['symbol'])
c.set_avg_daily_volume(result[i]['AverageDailyVolume'])
l.append(c)
return l
def get_all_companies_stock(self):
result = self.db.query("select Symbol from company_info;")
if result:
l = []
for i in range(len(result)):
c = Company_base()
c.set_symbol(result[i]['Symbol'])
l.append(c)
return l
def get_moving_average(self):
result = self.db.query(
"SELECT Symbol, Ask, FiftydayMovingAverage, TwoHundreddayMovingAverage FROM company_info WHERE FiftydayMovingAverage!='None' and TwoHundreddayMovingAverage!='None' and ask!='None';"
)
if result:
l = []
for i in range(len(result)):
c = Moving_average()
c.set_symbol(result[i]['Symbol'])
c.set_ask(result[i]['Ask'])
c.set_fifty_day_ask(result[i]["FiftydayMovingAverage"])
c.set_two_hundred_day_ask(
result[i]['TwoHundreddayMovingAverage'])
l.append(c)
return l
else: #DO THIS FOR ALL
return False
def get_ask_comp(self, symbols):
result = self.db.query(
"select symbol, ask from company_info where symbol in (" +
symbols + ");")
if result:
l = []
for i in range(len(result)):
c = Company_base()
c.set_ask(result[i]['ask'])
c.set_symbol(result[i]['symbol'])
l.append(c)
return l
class Login_dao:#{
db_connection = None
def __init__(self):
self.db_connection = PDO().get_connection(LINK_HEADERS.DB_HOST, LINK_HEADERS.DB_USER, LINK_HEADERS.DB_PASSWORD, LINK_HEADERS.DB_NAME)
def create_user(self, profile):
SQL="""
INSERT INTO login
(username, salt, passcode, first_name, last_name)
VALUES
('%s', '%s', '%s', '%s', '%s')
"""
self.db_connection.query(SQL % profile.get_values());
def update_user(self, profile):
SQL="""
UPDATE login SET
first_name='%s',
last_name='%s'
WHERE username='******'
"""
first_name = profile.get_first_name()
last_name = profile.get_last_name()
username = profile.get_username()
self.db_connection.query(SQL % (first_name, last_name, username));
def update_passcode(self, profile):
SQL="""
UPDATE login SET
passcode='%s',
salt = '%s'
WHERE
username='******'
"""
username = profile.get_username()
passcode = profile.get_passcode()
salt = profile.get_salt()
self.db_connection.query(SQL % (passcode, salt, username))
def get_salt(self, username):
SQL="SELECT salt FROM login WHERE username='******'"
result=self.db_connection.query(SQL % (username,))
if (result):return str(result[0]['salt'])
return "False"
def is_valid_user(self, username, passcode):
SQL= "SELECT * FROM login WHERE username ='******' AND passcode='%s'"
result = self.db_connection.query(SQL % (username,passcode))
if(result): return True
return False
def get_user_list(self):
result = self.db_connection.query("select username from login;")
if result:
l = []
for i in range(len(result)):
lo = Login(result[i]['username'])
l.append(lo)
return l
else:
return False
class Transaction_dao:
db = None
def __init__(self):
self.db = PDO().get_connection(LINK_HEADERS.DB_HOST,
LINK_HEADERS.DB_USER,
LINK_HEADERS.DB_PASSWORD,
LINK_HEADERS.DB_NAME)
def select(self):
result = self.db.query("select * from transactions;")
if result:
l = []
for i in range(len(result)):
t = Transaction(result[i]['user'], result[i]['trans_date'],
result[i]['stock'], result[i]['price'],
result[i]['sold'], result[i]['order_id'],
result[i]['profit'], result[i]['algo_id'])
l.append(t)
return l
def update_profit(self, user, trans_date, order_id, profit, stock,
algo_id):
self.db.query(
"update transactions set profit=('%s') where user=('%s') and trans_date=('%s') and order_id=('%s') and stock=('%s') and algo_id=('%s')"
% (round(Decimal(profit), 2), user, trans_date, order_id, stock,
algo_id) + ";")
def select_all(self, user):
result = self.db.query("select * from transactions where user=('%s')" %
(user) + ";")
if result:
l = []
for i in range(len(result)):
t = Transaction(result[i]['user'], result[i]['trans_date'],
result[i]['stock'], result[i]['price'],
result[i]['sold'], result[i]['order_id'],
result[i]['profit'], result[i]['algo_id'])
l.append(t)
l.sort(key=lambda x: x.get_trans_date(), reverse=False)
return l
def select_all_active(self, user):
result = self.db.query(
"select * from transactions where user=('%s') and sold='0'" %
(user) + ";")
if result:
l = []
for i in range(len(result)):
t = Transaction(result[i]['user'], result[i]['trans_date'],
result[i]['stock'], result[i]['price'],
result[i]['sold'], result[i]['order_id'],
result[i]['profit'], result[i]['algo_id'])
l.append(t)
l.sort(key=lambda x: x.get_trans_date(), reverse=False)
return l
def select_all_sold(self, user):
result = self.db.query(
"select * from transactions where user=('%s') and sold='1'" %
(user) + ";")
if result:
l = []
for i in range(len(result)):
t = Transaction(result[i]['user'], result[i]['trans_date'],
result[i]['stock'], result[i]['price'],
result[i]['sold'], result[i]['order_id'],
result[i]['profit'], result[i]['algo_id'])
l.append(t)
l.sort(key=lambda x: x.get_trans_date(), reverse=False)
return l
def buy(self, user, stock, volume, price, algo_id):
for i in range(volume):
self.db.query(
"insert into transactions (user, stock, price, sold, order_id, profit, algo_id) values ('%s','%s',%f,'%s',%d,%f,'%s')"
% (user, stock, round(Decimal(price), 2), 0, int(i),
Decimal(0), algo_id) + ";")
def sell(self, user, stock, volume, price, algo_id):
result = self.db.query(
"select * from transactions where user=('%s') and stock=('%s') and sold='0'"
% (user, stock) + " ORDER BY trans_date ASC, order_id ASC LIMIT " +
str(volume) + ";")
time = datetime.datetime.utcnow()
if result:
if len(result) >= volume:
for i in range(len(result)):
profit = Decimal(price) - Decimal(result[i]['price'])
self.db.query(
"update transactions set sold='1', profit=('%s'), trans_date=('%s') where user=('%s') and stock=('%s') and trans_date=('%s') and order_id=(%d) and algo_id=('%s')"
% (round(Decimal(profit), 2), str(time), user, stock,
result[i]['trans_date'], result[i]['order_id'],
algo_id) + ";")
def get_user_stock_list(self, user):
result = self.db.query(
"select distinct stock from transactions where user=('%s') and sold='0'"
% (user) + ";")
if result:
l = []
for i in range(len(result)):
l.append(result[i]['stock'])
return l
def get_all_algo_stock_list(self, user):
result = self.db.query(
"select distinct stock from transactions where user=('%s') and sold='0' and algo_id != '0'"
% (user) + ";")
if result:
l = []
for i in range(len(result)):
l.append(result[i]['stock'])
return l
def get_only_user_stock_list(self, user):
result = self.db.query(
"select distinct stock from transactions where user=('%s') and sold='0' and algo_id = '0'"
% (user) + ";")
if result:
l = []
for i in range(len(result)):
l.append(result[i]['stock'])
return l
def get_algo_stock_list(self, user, algo_id):
result = self.db.query(
"select distinct stock from transactions where user=('%s') and sold='0' and algo_id = ('%s')"
% (user, algo_id) + ";")
if result:
l = []
for i in range(len(result)):
l.append(result[i]['stock'])
return l
#def get_user_owned_stocks_list(self, user):
# result=self.db.query("select distinct stock from transactions where user=('%s') and sold='0'"%(user) + ";")
# if result:
# l=[]
# for i in range(len(result)):
# l.append(result[i]['stock'])
# return l
def get_owned_stock_model(self, user, stock, price):
volume_result = self.db.query(
"select count(*),sum(profit) from transactions where user=('%s') and stock=('%s') and sold='0'"
% (user, stock) + ";")
profit_result = volume_result
if volume_result and profit_result:
volume = int(volume_result[0]['count(*)'])
#Check for decimal operation failure
try:
total_worth = int(volume) * Decimal(price)
except InvalidOperation:
total_worth = 0
if profit_result[0]['sum(profit)'] != None:
profit = Decimal(profit_result[0]['sum(profit)'])
else:
profit = 0
o = Owned_stock(stock, volume, price, total_worth, profit)
return o
def get_owned_stock_volume_per_algorithm(self, user, algo_id):
volume_result = self.db.query(
"select stock, count(*) as volume from transactions where user = ('%s') and sold = '0' and algo_id=('%s') group by stock"
% (user, algo_id) + ";")
if volume_result:
l = []
for i in range(len(volume_result)):
o = Owned_stock(volume_result[i]['stock'],
volume_result[i]['volume'], None, None, None)
l.append(o)
return l
def get_profit_per_algorithm(self, user):
profit_result = self.db.query(
"select sum(profit) as profit from transactions where user = ('%s') and sold = '1' group by algo_id"
% (user) + ";")
if profit_result:
l = []
for i in range(len(profit_result)):
o = Owned_stock(None, None, None, None,
profit_result[i]['profit'])
l.append(o)
return l
# def get_algorithm_stock_value_model(self, user):
# result1=self.db.query("select sum(profit) as profit from transactions where user=('%s') and sold = '1' group by algo_id"%(user) + ";")
# if result1:
# l=[]
# for i in range(len(result)):
# l.append(result[i]['profit'])
# return l
def get_user_stock_value_model(self, user):
result1 = self.db.query(
"select sum(profit) from transactions where user=('%s') and sold='1'"
% (user) + ";")
result2 = self.db.query(
"select sum(price) from transactions where user=('%s') and sold='0'"
% (user) + ";")
if result1[0]['sum(profit)'] != None:
profit = Decimal(result1[0]['sum(profit)'])
else:
profit = 0
if result2[0]['sum(price)'] != None:
total_stock_values = Decimal(result2[0]['sum(price)'])
else:
total_stock_values = 0
u = User_stock_value(user, profit, total_stock_values)
return u
def get_trades_per_day(self, user):
result = self.db.query(
"select DATE(trans_date) as date, count(id) as num_trades from transactions where user=('%s') group by DATE(trans_date)"
% (user) + ";")
if result:
l = []
for i in range(len(result)):
t = Date_transaction()
t.set_date(result[i]['date'])
t.set_num_trades(result[i]['num_trades'])
l.append(t)
return l
else:
return False
def select_all_profit_per_day(self, user):
result = self.db.query(
"select UNIX_TIMESTAMP(DATE(trans_date))*1000 as date, sum(profit) as profit from transactions where user=('%s') and sold='1' group by DATE(trans_date)"
% (user) + ";")
if result:
l = []
for i in range(len(result)):
t = Date_transaction()
t.set_date(result[i]['date'])
t.set_profit(result[i]['profit'])
l.append(t)
return l
else:
return False
def select_user_profit_per_day(self, user):
result = self.db.query(
"select UNIX_TIMESTAMP(DATE(trans_date))*1000 as date, sum(profit) as profit from transactions where user=('%s') and sold='1' and algo_id='0' group by DATE(trans_date)"
% (user) + ";")
if result:
l = []
for i in range(len(result)):
t = Date_transaction()
t.set_date(result[i]['date'])
t.set_profit(result[i]['profit'])
l.append(t)
return l
else:
return False
def select_all_algorithms_profit_per_day(self, user):
result = self.db.query(
"select UNIX_TIMESTAMP(DATE(trans_date))*1000 as date, sum(profit) as profit from transactions where user=('%s') and sold='1' and algo_id!='0' group by DATE(trans_date)"
% (user) + ";")
if result:
l = []
for i in range(len(result)):
t = Date_transaction()
t.set_date(result[i]['date'])
t.set_profit(result[i]['profit'])
l.append(t)
return l
else:
return False
def select_algorithm_profit_per_day(self, user, algo_id):
result = self.db.query(
"select UNIX_TIMESTAMP(DATE(trans_date))*1000 as date, sum(profit) as profit from transactions where user=('%s') and sold='1' and algo_id=('%s') group by DATE(trans_date)"
% (user, algo_id) + ";")
if result:
l = []
for i in range(len(result)):
t = Date_transaction()
t.set_date(result[i]['date'])
t.set_profit(result[i]['profit'])
l.append(t)
return l
else:
return False
class Transaction_dao:
db = None
def __init__(self):
self.db = PDO().get_connection(LINK_HEADERS.DB_HOST, LINK_HEADERS.DB_USER, LINK_HEADERS.DB_PASSWORD, LINK_HEADERS.DB_NAME)
def select(self):
result = self.db.query("select * from transactions;")
if result:
l = []
for i in range(len(result)):
t = Transaction(result[i]['user'],result[i]['trans_date'],result[i]['stock'],result[i]['price'],result[i]['sold'],result[i]['order_id'],result[i]['profit'],result[i]['algo_id'])
l.append(t)
return l
def update_profit(self, user, trans_date, order_id, profit, stock, algo_id):
self.db.query("update transactions set profit=('%s') where user=('%s') and trans_date=('%s') and order_id=('%s') and stock=('%s') and algo_id=('%s')"%(round(Decimal(profit), 2), user, trans_date, order_id, stock, algo_id)+";")
def select_all(self, user):
result = self.db.query("select * from transactions where user=('%s')"%(user)+";")
if result:
l = []
for i in range(len(result)):
t = Transaction(result[i]['user'],result[i]['trans_date'],result[i]['stock'],result[i]['price'],result[i]['sold'],result[i]['order_id'],result[i]['profit'],result[i]['algo_id'])
l.append(t)
l.sort(key=lambda x: x.get_trans_date(), reverse=False)
return l
def select_all_active(self, user):
result = self.db.query("select * from transactions where user=('%s') and sold='0'"%(user)+";")
if result:
l = []
for i in range(len(result)):
t = Transaction(result[i]['user'],result[i]['trans_date'],result[i]['stock'],result[i]['price'],result[i]['sold'],result[i]['order_id'],result[i]['profit'],result[i]['algo_id'])
l.append(t)
l.sort(key=lambda x: x.get_trans_date(), reverse=False)
return l
def select_all_sold(self, user):
result = self.db.query("select * from transactions where user=('%s') and sold='1'"%(user)+";")
if result:
l = []
for i in range(len(result)):
t = Transaction(result[i]['user'],result[i]['trans_date'],result[i]['stock'],result[i]['price'],result[i]['sold'],result[i]['order_id'],result[i]['profit'],result[i]['algo_id'])
l.append(t)
l.sort(key=lambda x: x.get_trans_date(), reverse=False)
return l
def buy(self,user, stock, volume, price, algo_id):
for i in range(volume):
self.db.query("insert into transactions (user, stock, price, sold, order_id, profit, algo_id) values ('%s','%s',%f,'%s',%d,%f,'%s')"%(user, stock, round(Decimal(price), 2), 0, int(i), Decimal(0), algo_id)+";")
def sell(self, user, stock, volume, price, algo_id):
result = self.db.query("select * from transactions where user=('%s') and stock=('%s') and sold='0'"%(user, stock)+" ORDER BY trans_date ASC, order_id ASC LIMIT "+str(volume)+";")
time = datetime.datetime.utcnow()
if result:
if len(result) >= volume:
for i in range(len(result)):
profit = Decimal(price) - Decimal(result[i]['price'])
self.db.query("update transactions set sold='1', profit=('%s'), trans_date=('%s') where user=('%s') and stock=('%s') and trans_date=('%s') and order_id=(%d) and algo_id=('%s')"%(round(Decimal(profit),2), str(time), user, stock, result[i]['trans_date'], result[i]['order_id'], algo_id)+";")
def get_user_stock_list(self, user):
result = self.db.query("select distinct stock from transactions where user=('%s') and sold='0'"%(user)+";")
if result:
l=[]
for i in range(len(result)):
l.append(result[i]['stock'])
return l
def get_all_algo_stock_list(self, user):
result = self.db.query("select distinct stock from transactions where user=('%s') and sold='0' and algo_id != '0'"%(user)+";")
if result:
l=[]
for i in range(len(result)):
l.append(result[i]['stock'])
return l
def get_only_user_stock_list(self, user):
result = self.db.query("select distinct stock from transactions where user=('%s') and sold='0' and algo_id = '0'"%(user)+";")
if result:
l=[]
for i in range(len(result)):
l.append(result[i]['stock'])
return l
def get_algo_stock_list(self, user, algo_id):
result = self.db.query("select distinct stock from transactions where user=('%s') and sold='0' and algo_id = ('%s')"%(user, algo_id)+";")
if result:
l=[]
for i in range(len(result)):
l.append(result[i]['stock'])
return l
#def get_user_owned_stocks_list(self, user):
# result=self.db.query("select distinct stock from transactions where user=('%s') and sold='0'"%(user) + ";")
# if result:
# l=[]
# for i in range(len(result)):
# l.append(result[i]['stock'])
# return l
def get_owned_stock_model(self, user, stock, price):
volume_result= self.db.query("select count(*),sum(profit) from transactions where user=('%s') and stock=('%s') and sold='0'"%(user, stock)+";")
profit_result = volume_result
if volume_result and profit_result:
volume = int(volume_result[0]['count(*)'])
#Check for decimal operation failure
try:
total_worth = int(volume) * Decimal(price)
except InvalidOperation:
total_worth = 0
if profit_result[0]['sum(profit)'] != None:
profit = Decimal(profit_result[0]['sum(profit)'])
else:
profit = 0
o = Owned_stock(stock, volume, price, total_worth, profit)
return o
def get_owned_stock_volume_per_algorithm(self, user, algo_id):
volume_result=self.db.query("select stock, count(*) as volume from transactions where user = ('%s') and sold = '0' and algo_id=('%s') group by stock"%(user,algo_id)+";")
if volume_result:
l=[]
for i in range(len(volume_result)):
o = Owned_stock(volume_result[i]['stock'], volume_result[i]['volume'], None, None, None)
l.append(o)
return l
def get_profit_per_algorithm(self, user):
profit_result = self.db.query("select sum(profit) as profit from transactions where user = ('%s') and sold = '1' group by algo_id"%(user) + ";")
if profit_result:
l=[]
for i in range(len(profit_result)):
o = Owned_stock(None, None, None, None, profit_result[i]['profit'])
l.append(o)
return l
# def get_algorithm_stock_value_model(self, user):
# result1=self.db.query("select sum(profit) as profit from transactions where user=('%s') and sold = '1' group by algo_id"%(user) + ";")
# if result1:
# l=[]
# for i in range(len(result)):
# l.append(result[i]['profit'])
# return l
def get_user_stock_value_model(self, user):
result1 = self.db.query("select sum(profit) from transactions where user=('%s') and sold='1'"%(user)+";")
result2 = self.db.query("select sum(price) from transactions where user=('%s') and sold='0'"%(user)+";")
if result1[0]['sum(profit)'] != None:
profit = Decimal(result1[0]['sum(profit)'])
else:
profit = 0
if result2[0]['sum(price)'] != None:
total_stock_values = Decimal(result2[0]['sum(price)'])
else:
total_stock_values = 0
u = User_stock_value(user, profit, total_stock_values)
return u
def get_trades_per_day(self,user):
result = self.db.query("select DATE(trans_date) as date, count(id) as num_trades from transactions where user=('%s') group by DATE(trans_date)"%(user)+";");
if result:
l = []
for i in range(len(result)):
t = Date_transaction()
t.set_date(result[i]['date'])
t.set_num_trades(result[i]['num_trades'])
l.append(t)
return l
else:
return False
def select_all_profit_per_day(self, user):
result = self.db.query("select UNIX_TIMESTAMP(DATE(trans_date))*1000 as date, sum(profit) as profit from transactions where user=('%s') and sold='1' group by DATE(trans_date)"%(user)+";");
if result:
l = []
for i in range(len(result)):
t = Date_transaction()
t.set_date(result[i]['date'])
t.set_profit(result[i]['profit'])
l.append(t)
return l
else:
return False
def select_user_profit_per_day(self, user):
result = self.db.query("select UNIX_TIMESTAMP(DATE(trans_date))*1000 as date, sum(profit) as profit from transactions where user=('%s') and sold='1' and algo_id='0' group by DATE(trans_date)"%(user)+";");
if result:
l = []
for i in range(len(result)):
t = Date_transaction()
t.set_date(result[i]['date'])
t.set_profit(result[i]['profit'])
l.append(t)
return l
else:
return False
def select_all_algorithms_profit_per_day(self, user):
result = self.db.query("select UNIX_TIMESTAMP(DATE(trans_date))*1000 as date, sum(profit) as profit from transactions where user=('%s') and sold='1' and algo_id!='0' group by DATE(trans_date)"%(user)+";");
if result:
l = []
for i in range(len(result)):
t = Date_transaction()
t.set_date(result[i]['date'])
t.set_profit(result[i]['profit'])
l.append(t)
return l
else:
return False
def select_algorithm_profit_per_day(self, user, algo_id):
result = self.db.query("select UNIX_TIMESTAMP(DATE(trans_date))*1000 as date, sum(profit) as profit from transactions where user=('%s') and sold='1' and algo_id=('%s') group by DATE(trans_date)"%(user, algo_id)+";");
if result:
l = []
for i in range(len(result)):
t = Date_transaction()
t.set_date(result[i]['date'])
t.set_profit(result[i]['profit'])
l.append(t)
return l
else:
return False
class Company_dao:
db = None
def __init__(self):
self.db = PDO().get_connection(LINK_HEADERS.DB_HOST, LINK_HEADERS.DB_USER, LINK_HEADERS.DB_PASSWORD, LINK_HEADERS.DB_NAME)
def get_all_ask(self):
result = self.db.query("select Symbol, Name, Ask from company_info;")
if result:
l=[]
for i in range(len(result)):
if result[i]['Ask'] != None:
c = Company_base()
c.set_name(result[i]['Name'])
c.set_ask(result[i]['Ask'])
c.set_symbol(result[i]['Symbol'])
l.append(c)
return l
def get_list_of_company_models(self, symbols_list):
if symbols_list:
symbols_string=""
for i in range(len(symbols_list)):
symbols_string = symbols_string + "'" + symbols_list[i] + "'"
if i != len(symbols_list)-1:
symbols_string = symbols_string + ","
result = self.db.query("select ask, name, symbol from company_info where symbol in ("+symbols_string+");")
if result:
l=[]
for i in range(len(result)):
c = Company()
c.set_ask(result[i]['ask'])
c.set_name(result[i]['name'])
c.set_symbol(result[i]['symbol'])
l.append(c)
return l
else:
return False
def get_company_model(self, symbol):
result = self.db.query("select Ask, Name, Volume from company_info where symbol = ('%s')"%(symbol)+";")
if result:
c = Company()
c.set_ask(result[0]['Ask'])
c.set_name(result[0]['Name'])
#c.set_percent_change(result[0]['PercentChange'])
c.set_symbol(symbol)
#c.set_avg_daily_volume(result[0]['AverageDailyVolume'])
c.set_volume(result[0]['Volume'])
return c
def get_all_companies_percentchange(self):
result = self.db.query("select symbol, PercentChange from company_info WHERE PercentChange IS NOT NULL AND PercentChange!='None';")
if result:
l = []
for i in range(len(result)):
c = Active_company()
c.set_symbol(result[i]['symbol'])
#r = result[i]['PercentChange'].translate(None, '+%')
#c.set_percent_change(r)
c.set_percent_change(result[i]['PercentChange'])#.translate(None, '+%'))
#c.set_volume(result[i]['Volume'])
#c.set_avg_daily_volume(result[i]['AverageDailyVolume'])
l.append(c)
return l
def get_all_companies_volume(self):
result = self.db.query("select symbol, Volume from company_info;")
if result:
l = []
for i in range(len(result)):
c = Active_company()
c.set_symbol(result[i]['symbol'])
c.set_volume(result[i]['Volume'])
l.append(c)
return l
def get_all_companies_averagedailyvolume(self): #NOT TO SELF: fix the averagedailyvolume and avg_daily_volume difference
result = self.db.query("select symbol, AverageDailyVolume from company_info;")
if result:
l = []
for i in range(len(result)):
c = Active_company()
c.set_symbol(result[i]['symbol'])
c.set_avg_daily_volume(result[i]['AverageDailyVolume'])
l.append(c)
return l
def get_all_companies_stock(self):
result = self.db.query("select Symbol from company_info;")
if result:
l = []
for i in range(len(result)):
c = Company_base()
c.set_symbol(result[i]['Symbol'])
l.append(c)
return l
def get_moving_average(self):
result = self.db.query("SELECT Symbol, Ask, FiftydayMovingAverage, TwoHundreddayMovingAverage FROM company_info WHERE FiftydayMovingAverage!='None' and TwoHundreddayMovingAverage!='None' and ask!='None';")
if result:
l = []
for i in range(len(result)):
c = Moving_average()
c.set_symbol(result[i]['Symbol'])
c.set_ask(result[i]['Ask'])
c.set_fifty_day_ask(result[i]["FiftydayMovingAverage"])
c.set_two_hundred_day_ask(result[i]['TwoHundreddayMovingAverage'])
l.append(c)
return l
else: #DO THIS FOR ALL
return False
def get_ask_comp(self, symbols):
result = self.db.query("select symbol, ask from company_info where symbol in (" + symbols + ");")
if result:
l = []
for i in range(len(result)):
c = Company_base()
c.set_ask(result[i]['ask'])
c.set_symbol(result[i]['symbol'])
l.append(c)
return l
class History_dao:
db = None
def __init__(self):
self.db = PDO().get_connection(LINK_HEADERS.DB_HOST,
LINK_HEADERS.DB_USER,
LINK_HEADERS.DB_PASSWORD,
LINK_HEADERS.DB_NAME)
def insert(self, user, trans_type, stock, price, total_price, volume,
algo_id):
self.db.query(
"insert into history (user, trans_type, stock, price, total_price, volume, algo_id) values ('%s','%s','%s','%s','%s',%s,'%s')"
% (user, trans_type, stock, round(Decimal(price), 2),
round(Decimal(total_price), 2), volume, algo_id) + ";")
def select_all(self, user):
result = self.db.query("select * from history where user=('%s')" %
(user) + ";")
if result:
l = []
for i in range(len(result)):
h = History(user, result[i]['trans_date'],
result[i]['trans_type'], result[i]['stock'],
result[i]['price'], result[i]['total_price'],
result[i]['volume'], result[i]['algo_id'])
l.append(h)
return l
def select_all_algo_trades(self, user):
result = self.db.query(
"select * from history where user=('%s') and algo_id != '0'" %
(user) + ";")
if result:
l = []
for i in range(len(result)):
h = History(user, result[i]['trans_date'],
result[i]['trans_type'], result[i]['stock'],
result[i]['price'], result[i]['total_price'],
result[i]['volume'], result[i]['algo_id'])
l.append(h)
return l
def select_all_user_trades(self, user):
result = self.db.query(
"select * from history where user=('%s') and algo_id = '0'" %
(user) + ";")
if result:
l = []
for i in range(len(result)):
h = History(user, result[i]['trans_date'],
result[i]['trans_type'], result[i]['stock'],
result[i]['price'], result[i]['total_price'],
result[i]['volume'], result[i]['algo_id'])
l.append(h)
return l
def select_algo_trades(self, user, algo_id):
result = self.db.query(
"select * from history where user=('%s') and algo_id = '%s'" %
(user, algo_id) + ";")
if result:
l = []
for i in range(len(result)):
h = History(user, result[i]['trans_date'],
result[i]['trans_type'], result[i]['stock'],
result[i]['price'], result[i]['total_price'],
result[i]['volume'], result[i]['algo_id'])
l.append(h)
return l
def get_algorithm_buy_sell_volume(self, user):
result1 = self.db.query(
"select count(trans_type) as buy, algo_id, sum(volume) as volume from history where user=('%s') and trans_type = 'buy' group by algo_id order by algo_id"
% (user) + ";")
result2 = self.db.query(
"select count(trans_type) as sell, algo_id, sum(volume) as volume from history where user=('%s') and trans_type = 'sell' group by algo_id order by algo_id"
% (user) + ";")
if result1 or result2:
l = []
iterations = max(len(result1), len(result2))
for i in range(iterations):
ad = Algorithm_details(
user, result1[i]['algo_id'],
str(int(result1[i]['volume']) + int(result2[i]['volume'])),
result1[i]['buy'], result2[i]['sell'])
l.append(ad)
return l
def get_distinct_traded_stocks(self, user):
result = self.db.query(
"select distinct stock from history where user=('%s')" % (user) +
";")
if result:
l = []
for i in range(len(result)):
c = Company()
c.set_name(result[i]['stock'])
l.append(c)
return l
def get_all_volume_per_day(self, user):
result = self.db.query(
"select UNIX_TIMESTAMP(DATE(trans_date))*1000 as date, sum(volume) as volume from history where user=('%s') group by DATE(trans_date)"
% (user) + ";")
if result:
l = []
for i in range(len(result)):
t = Date_history()
t.set_date(result[i]['date'])
t.set_volume(result[i]['volume'])
l.append(t)
return l
else:
return False
def get_user_volume_per_day(self, user):
result = self.db.query(
"select UNIX_TIMESTAMP(DATE(trans_date))*1000 as date, sum(volume) as volume from history where user=('%s') and algo_id='0' group by DATE(trans_date)"
% (user) + ";")
if result:
l = []
for i in range(len(result)):
t = Date_history()
t.set_date(result[i]['date'])
t.set_volume(result[i]['volume'])
l.append(t)
return l
else:
return False
def get_all_algorithms_volume_per_day(self, user):
result = self.db.query(
"select UNIX_TIMESTAMP(DATE(trans_date))*1000 as date, sum(volume) as volume from history where user=('%s') and algo_id!='0' group by DATE(trans_date)"
% (user) + ";")
if result:
l = []
for i in range(len(result)):
t = Date_history()
t.set_date(result[i]['date'])
t.set_volume(result[i]['volume'])
l.append(t)
return l
else:
return False
def get_algorithm_volume_per_day(self, user, algo_id):
result = self.db.query(
"select UNIX_TIMESTAMP(DATE(trans_date))*1000 as date, sum(volume) as volume from history where user=('%s') and algo_id=('%s') group by DATE(trans_date)"
% (user, algo_id) + ";")
if result:
l = []
for i in range(len(result)):
t = Date_history()
t.set_date(result[i]['date'])
t.set_volume(result[i]['volume'])
l.append(t)
return l
else:
return False
