def getStrategyLogsByStrategyId(creator, strategy_id):
strategyLogList = []
# SQL 查询语句
sql = " SELECT strategy_log_id," \
" strategy_id," \
" start_date," \
" end_date," \
" init_balance," \
" coin_category," \
" creator," \
" create_time," \
" execution_result," \
" (select strategy_name from strategy where strategy.strategy_id = strategy_log.strategy_id) strategy_name," \
" final_margin ," \
" benchmark,max_drawdown " \
" FROM strategy_log" \
" where creator=%s and strategy_id =%s and del_flag = 0 " \
" order by create_time desc"
# 执行SQL语句
param = (creator, strategy_id)
# print(sql)
results = db.fetch_db_with_param(sql, param)
for row in results:
# 打印结果
pro = StrategyLog(row[0], row[1], row[2], row[3], row[4], row[5], row[6], row[7], row[8], row[9], row[10],
row[11], row[12])
# print("row[0]:"+str(row[0])+"|row[a]:"+row[a]+"|row[b]:"+str(row[b])+"|row[3]:"+str(row[3])+"|row[4]:"+row[4]+"|row[5]:"+str(row[5])+"|row[6]:"+row[6]+"|row[7]:"+str(row[7]))
strategyLogList.append(pro)
return strategyLogList
def getALLStrategy(creator):
strategyList = []
# SQL 查询语句
sql = "SELECT strategy_id," \
" strategy_name," \
" description," \
" create_time," \
" update_time," \
" loading_times," \
" creator," \
" script_url," \
" peroid," \
" method_name," \
" coin_category, " \
" init_balance," \
" start_time," \
" end_time," \
" (select create_time from strategy_log sl where sl.strategy_id=s.strategy_id order by create_time desc limit 1) last_run," \
" (select count(1) from strategy_log sl where sl.strategy_id=s.strategy_id) run_times," \
" duration,benchmark,drawdown,status FROM strategy s" \
" where creator=%s and del_flag = 0"
# 执行SQL语句
# 获取所有记录列表
results = db.fetch_db_with_param(sql, creator)
for row in results:
# 打印结果
strategy = Strategy(row[0], row[1], row[2], row[3], row[4], row[5], row[6], row[7], row[8], row[9], row[10],
row[11], row[12], row[13], row[14], row[15], row[16], row[17], row[18], row[19])
strategyList.append(strategy)
return strategyList
def getStrategyDetail(creator, strategy_id):
# SQL 查询语句
sql = "SELECT strategy_id," \
" strategy_name," \
" description," \
" create_time," \
" update_time," \
" loading_times," \
" creator," \
" script_url," \
" peroid," \
" method_name," \
" coin_category, " \
" init_balance," \
" start_time," \
" end_time," \
" (select create_time from strategy_log sl where sl.strategy_id=s.strategy_id order by create_time desc limit 1) last_run," \
" (select count(1) from strategy_log sl where sl.strategy_id=s.strategy_id) run_times," \
" duration,benchmark,drawdown,status FROM strategy s" \
" where creator=%s and strategy_id = %s "
# 执行SQL语句
param = (creator, strategy_id)
rows = db.fetch_db_with_param(sql, param)
# 获取所有记录列表
row = rows[0]
strategy = Strategy(row[0], row[1], row[2], row[3], row[4], row[5], row[6], row[7], row[8], row[9], row[10],
row[11], row[12], row[13], row[14], row[15], row[16], row[17], row[18], row[19])
return strategy
def getStrategy(userId, strategyId):
strategyList = []
# SQL 查询语句
sql = " SELECT strategy_id," \
" strategy_name," \
" description," \
" create_time," \
" update_time," \
" loading_times," \
" creator," \
" script_url," \
" peroid," \
" method_name," \
" coin_category, " \
" init_balance," \
" start_time," \
" end_time," \
" (select create_time from strategy_log sl where sl.strategy_id=s.strategy_id order by create_time desc limit 1) last_run," \
" (select count(1) from strategy_log sl where sl.strategy_id=s.strategy_id) run_times," \
" duration,benchmark,drawdown,status" \
" FROM strategy s where creator=%s and strategy_id=%s and del_flag = 0 "
# 执行SQL语句
param = (userId, strategyId)
rows = db.fetch_db_with_param(sql, param)
# 获取所有记录列表
# 打印结果
row = rows[0]
strategy = Strategy(row[0], row[1], row[2], row[3], row[4], row[5], row[6], row[7], row[8], row[9], row[10],
row[11], row[12], row[13], row[14], row[15], row[16], row[17], row[18], row[19])
# print(strategy)
strategy.set_strategy_conf_items(getStrategyConfItem(row[0]))
# strategyConf.printAll()
return strategy
def checkStrategyName(strategy_name, creator):
strategyConfList = []
# SQL 查询语句
sql = " select * from strategy where strategy_name=%s and creator=%s"
param = (strategy_name, creator)
results = db.fetch_db_with_param(sql, param)
if (len(results) > 0):
return True
else:
return False
def checkPreviousStrategyName(strategy_id, creator, strategy_name):
# sql 查询语句
sql = "select strategy_name from strategy where strategy_id=%s and creator=%s"
params = (strategy_id, creator)
while True:
result = db.fetch_db_with_param(sql, params)
if result == None:
break
if result[0][0] == strategy_name:
return True
else:
return False
def get_strategy_profit_list(strategy_log_id):
strategy_profit_list = []
sql = " SELECT current_total_margin_rate FROM strategy_account where strategy_log_id = %s " \
" order by strategy_account_id asc "
params = (strategy_log_id)
results = db.fetch_db_with_param(sql, params)
for item in results:
strategy_profit_list.append(item[0])
return strategy_profit_list
def get_all_strategy_name(creator):
strategy_name_list = []
sql = " SELECT strategy_name FROM strategy where del_flag = 0 and creator = %s " \
" order by strategy_name asc "
params = (creator)
results = db.fetch_db_with_param(sql, params)
for item in results:
strategy_name_list.append(item[0])
return strategy_name_list
def mob_my_strategy_list(creator):
my_strategy_list = []
sql0 = "CREATE OR REPLACE VIEW MY_STRATEGY_LIST AS " \
"SELECT s.strategy_id, s.strategy_name, sl.create_time from strategy s " \
"inner join strategy_log sl on sl.strategy_id = s.strategy_id " \
"where s.creator = %s " \
"order by sl.create_time desc; "
sql1 = "select strategy_id,strategy_name,max(create_time),count(create_time)" \
" from MY_STRATEGY_LIST " \
"group by strategy_id;"
sql2 = "DROP VIEW MY_STRATEGY_LIST"
params = (creator)
db.fetch_db_with_param(sql0, params)
results = db.fetch_db(sql1)
db.update_db(sql2)
for item in results:
strategy = MyStrategy(strategy_id=item[0], strategy_name=item[1], create_time=item[2], run_times=item[3])
my_strategy_list.append(strategy)
return my_strategy_list
def getUserByPhone(phone):
UserList = []
# SQL 查询语句
sql = "SELECT u.user_id,u.phone,u.password,u.nick_name,u.open_id,u.age,u.gender," \
"u.avator,u.levels," \
"style,experience FROM user_info u WHERE u.phone = %s"
# 执行SQL语句
param = (phone)
results = db.fetch_db_with_param(sql, param)
for row in results:
# 打印结果
item = UserByPhone(row[0], row[1], row[2], row[3], row[4], row[5], row[6], row[7], row[8], row[9], row[10])
UserList.append(item)
return UserList
def getStrategyConfItem(strategy_id):
strategyConfItemList = []
# SQL 查询语句
sql = " SELECT strategy_conf_item_id,strategy_id,index_label,formular,price,direction" \
" FROM strategy_conf_item where strategy_id=%s"
# 执行SQL语句
param = (strategy_id)
results = db.fetch_db_with_param(sql, param)
# 获取所有记录列表
for row in results:
# 打印结果
item = StrategyConfItem(row[0], row[1], row[2], row[3], row[4], row[5])
strategyConfItemList.append(item)
return strategyConfItemList
def getUser(user_id):
UserList = []
# SQL 查询语句
sql = "SELECT u.user_id,u.phone,u.password,u.nick_name,u.open_id,u.age,u.gender," \
"u.avator,u.levels," \
"(SELECT COUNT(s.strategy_id) FROM strategy s WHERE s.creator = u.user_id and s.del_flag = 0) strategy_amount," \
"(SELECT COUNT(sl.strategy_log_id) FROM strategy_log sl WHERE sl.creator = u.user_id and sl.del_flag = 0) history_amount," \
"style,experience FROM user_info u WHERE u.user_id = %s"
# 执行SQL语句
param = (user_id)
results = db.fetch_db_with_param(sql, param)
for row in results:
# 打印结果
item = User(row[0], row[1], row[2], row[3], row[4], row[5], row[6], row[7], row[8], row[9], row[10], row[11],
row[12])
UserList.append(item)
return UserList
def mob_get_strategy_log_list(strategy_id, creator):
strategy_logs = []
sql = "select sl.create_time,s.strategy_name, " \
"sl.final_margin, sl.strategy_log_id," \
"sl.benchmark,sl.start_date,sl.end_date,sl.max_drawdown " \
"from strategy_log sl " \
"inner join strategy s on sl.strategy_id = s.strategy_id " \
"where sl.strategy_id = %s and s.creator = %s " \
"order by sl.create_time desc ;"
params = (strategy_id, creator)
results = db.fetch_db_with_param(sql, params)
for item in results:
strategy_log = StrategyLog(strategy_log_id=item[3],
create_time=item[0], strategy_name=item[1],
final_margin=item[2], benchmark=item[4],
strategy_id=strategy_id, start_date=item[5], end_date=item[6], creator=creator,
coin_category=None, execution_result=None, init_balance=None, max_drawdown=item[7])
strategy_logs.append(strategy_log)
return strategy_logs
def mob_trade_history(strategy_log_id):
trade_historys = []
sql = "select st.t, st.flag, " \
"st.pre_position, " \
"st.post_position, " \
"st.pre_balance," \
"st.post_balance," \
"st.flag from strategy_log sl " \
"inner join strategy s on sl.strategy_id = s.strategy_id " \
"inner join strategy_account sa on sl.strategy_log_id = sa.strategy_log_id " \
"inner join strategy_transaction st on sa.strategy_account_id = st.strategy_account_id " \
"where sl.strategy_log_id = %s;"
params = (strategy_log_id)
results = db.fetch_db_with_param(sql, params)
for item in results:
trade_history = TradeHistory(item[0], item[1], item[2], item[3], item[4], item[5], item[6])
trade_historys.append(trade_history)
return trade_historys