def CheckOrderList(self, uaid, OrderTicket):
# 返回查询的列表的所有订单
with (yield pool.Connection()) as conn:
with conn.cursor() as cursor:
try:
sql = "SELECT * FROM proxy_order WHERE uaid=%s AND orderid in %s" % (
uaid, OrderTicket)
yield cursor.execute(sql)
datas = cursor.fetchall()
return datas
except Exception as err:
yield conn.rollback()
logging.error(
"[ProxyOrderModel:CheckOrderList:SELECT]: %s" % err)
logging.error(
"[ProxyOrderModel:CheckOrderList:SELECT]: %s" % sql)
return []
def editPost(self, news_dist):
# 修改博文
with (yield pool.Connection()) as conn:
with conn.cursor() as cursor:
try:
up_date = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
sql2 = "update posts set `post_title` = %s, `post_status`=%s, `post_excerpt`=%s,`post_content`=%s," \
"`post_modified`=%s WHERE post_id = %s"
# print(sql2 % (news_dist['post_title'], news_dist['post_status'], news_dist['post_excerpt'],
# news_dist['post_content'], up_date, int(news_dist['post_id'])))
yield cursor.execute(sql2, (news_dist['post_title'], news_dist['post_status'], news_dist['post_excerpt'],
news_dist['post_content'], up_date, int(news_dist['post_id'])))
yield conn.commit()
return True
except Exception as err:
yield conn.rollback()
logger.error("[PostsModel:editPost] %s" % err)
return False
def delSeperateList(self, fid):
# 删除表中的相关的所有订单
with (yield pool.Connection()) as conn:
with conn.cursor() as cursor:
try:
sql = "DELETE FROM seperate_arr WHERE fid=%s" % (fid, )
yi = yield cursor.execute(sql)
datas = cursor.fetchall()
# print("delSeperateList:yi:", yi)
# print("delSeperateList:", datas)
return datas
except Exception as err:
yield conn.rollback()
self.logger.error(
"[SeperateModel:delSeperateList:SELECT]: %s" % err)
self.logger.error(
"[SeperateModel:delSeperateList:SELECT]: %s" % sql)
return []
def findFollowid(self, strComment, uaid):
# 根据备注查找订单的跟单ID
import re
par = "([0-9]+)"
arr1 = re.compile(par).findall(strComment)
with (yield pool.Connection()) as conn:
with conn.cursor() as cursor:
sql = "SELECT tid,followid FROM trader WHERE uaid=%s AND orderid=%s" % (uaid, int(arr1[0]))
# print(sql)
yield cursor.execute(sql)
datas = cursor.fetchone()
if datas:
if datas['followid'] == 0:
return datas['tid']
else:
return datas['followid']
else:
return 0
def getCs_info_List(self, uid, csid, start, length):
with (yield pool.Connection()) as conn:
with conn.cursor() as cursor:
# sql0 = "SELECT count(*) as num FROM cs WHERE csid=%s and cs_status=1"
# # print(sql)
# yield cursor.execute(sql0, uid)
# datas2 = cursor.fetchone()
sql = "SELECT users.uname,cs.uid,cs.csid,cs.cs_id,cs.cs_name,cs.cs_text,cs.utime " \
"FROM cs INNER JOIN users ON cs.uid = users.uid " \
"WHERE cs.csid=%s and cs.cs_status=1 ORDER BY cs.cs_id ASC "
# print(sql)
yield cursor.execute(sql, (csid, ))
datas = cursor.fetchall()
if len(datas) > 0:
# datas.append({"allnum": datas2['num']})
return datas
else:
return []
def add_proxy_info(self, web_uid, uname="", iban=""):
# 增加信息
with (yield pool.Connection()) as conn:
with conn.cursor() as cursor:
try: #
if uname == "":
sql = "INSERT INTO proxy_users(uid,grade_id) VALUES(%s,%s)"
yield cursor.execute(sql % (web_uid, 3))
else:
sql = "INSERT INTO proxy_users(uid,uname,iban,grade_id) VALUES(%s,%s,%s,%s)"
yield cursor.execute(sql, (web_uid, uname, iban, 3))
yield conn.commit()
return True
except Exception as err:
yield conn.rollback()
logging.error(
"[ProxyOrderModel:add_proxy_info:INSERT]: %s" % err)
return False
def getMaterInfo(self, masterid, pid):
with (yield pool.Connection()) as conn:
with conn.cursor() as cursor:
sql = "SELECT product_info.piname,product.pname_cn,product.pname_en,p_order.oid,p_order.uaid," \
"p_order.otype,p_order.amount,p_order.amount_less,p_order.amount_cny,p_order.onum,p_order.strattime, " \
"p_order.trade_status,p_order.endtime,product_info.info10,product_info.info11,product_info.info12 " \
"FROM p_order " \
"INNER JOIN product_info ON p_order.piid = product_info.piid " \
"INNER JOIN product ON product_info.pid = product.pid " \
"WHERE p_order.uaid = %s AND product_info.pid = %s AND p_order.endtime > now() AND p_order.otype = 1"
# logger.debug(sql % (masterid, pid))
yield cursor.execute(sql, (masterid, pid))
datas = cursor.fetchall()
MaterInfo = {}
MaterInfo['mater_max_num'] = 0 # 可用跟单账户数量
MaterInfo['endtime'] = None # 到期时间
MaterInfo['position_maxnum'] = 0 # info10持仓最大限制
MaterInfo['spsl'] = "0" # info11止损止盈
MaterInfo['reverse'] = "0" # info11反向平仓
if len(datas) > 0:
# print(datas)
for data in datas:
if data['strattime'] <= datetime.datetime.strptime(
datetime.datetime.now().strftime(
'%Y-%m-%d %H:%M:%S'), '%Y-%m-%d %H:%M:%S'):
MaterInfo['mater_max_num'] = MaterInfo[
'mater_max_num'] + data['onum']
if MaterInfo['endtime'] == None:
MaterInfo['endtime'] = data['endtime']
else:
if data['endtime'] > MaterInfo['endtime']:
MaterInfo['endtime'] = data['endtime']
if int(data['info10']
) > MaterInfo['position_maxnum']:
MaterInfo['position_maxnum'] = int(
data['info10'])
#info11止损止盈
if data['info11'] == "1":
MaterInfo['spsl'] = "1"
#info11反向平仓
if data['info12'] == "1":
MaterInfo['reverse'] = "1"
logger.debug("MaterInfo%s" % MaterInfo)
return MaterInfo
def getTags(self, tags_list):
# 已经有的标签
with (yield pool.Connection()) as conn:
with conn.cursor() as cursor:
if len(tags_list) == 1:
tags_list.append("fxcns")
sql = "select pt_id,pt_name_cn from posts_terms where `pt_name_cn` in %s"
# print(sql)
yield cursor.execute(sql, (tuple(tags_list),))
rows = cursor.fetchall()
rows2 = []
if "fxcns" in tags_list:
tags_list.remove("fxcns")
for pt in rows:
if pt['pt_name_cn'] != "fxcns":
rows2.append(pt)
return rows2
else:
return rows
def AddOrder(self, uaid, orderid, proname, num, t_type, stime, sprice,
etime, eprice, sl, tp, commission, swap, profit, maxprofit,
minprofit, comment, magic, followid):
# 增加订单
with (yield pool.Connection()) as conn:
with conn.cursor() as cursor:
sql = "INSERT INTO trader(uaid,orderid,proname,num,t_type,stime,sprice,etime,eprice,sl,tp,commission,swap,profit,maxprofit,minprofit,comment,magic,followid) VALUES(%s,%s,'%s',%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,'%s',%s,%s)"
try: # print(sql)
yield cursor.execute(
sql %
(uaid, orderid, proname, num, t_type, stime, sprice,
etime, eprice, sl, tp, commission, swap, profit,
maxprofit, minprofit, comment, magic, followid))
yield conn.commit()
return True
except Exception as err:
yield conn.rollback()
logger.error("[OrderModel:AddOrder:INSERT]: %s" % err)
return False
def getProxySettlementAllCount(self, uid):
with (yield pool.Connection()) as conn:
with conn.cursor() as cursor:
try:
yield cursor.callproc('count_proxy_settlement',
(uid, "@out_proxy_profit",
"@out_profit", "@out_amount"))
yield cursor.execute(
"select @out_proxy_profit, @out_profit, @out_amount;")
# for result in cursor.stored_results():
# print(result.fetchall())
row = cursor.fetchone()
return row['@out_proxy_profit'], row['@out_profit'], row[
'@out_amount']
except Exception as err:
logging.error(
"[ProxyOrderModel:getProxySettlementAllCount:update]: %s"
% err)
return -1, -1, -1
def AddMailAccount(self, umail):
with (yield pool.Connection()) as conn:
with conn.cursor() as cursor:
# users['ptname']db.cursor(MySQLdb.cursors.DictCursor)
sql = "SELECT uid,email FROM users WHERE email=%s and emailflag=1"
try:
up_date = datetime.datetime.now().strftime(
'%Y-%m-%d %H:%M:%S')
sql3 = "INSERT INTO users(email,emailflag,vipid,starttime,flag) VALUES('%s', %s, %s, '%s', %s)"
yield cursor.execute(sql3 % (umail, 1, 1, up_date, 1))
uid = conn.insert_id()
# logger.info(sql2)
yield conn.commit()
return uid
except Exception as err:
yield conn.rollback()
logger.error(
"[SwissquoteModel:AddMailAccount:INSERT]: %s" % err)
return None
def UpOrder(self, uaid, orderid, proname, num, t_type, stime, sprice,
etime, eprice, sl, tp, commission, swap, profit, maxprofit,
minprofit, comment, magic, followid):
# 修改订单
with (yield pool.Connection()) as conn:
with conn.cursor() as cursor:
sql = "update trader set proname='%s',num=%s,t_type=%s,stime=%s,sprice=%s,etime=%s,eprice=%s,sl=%s,tp=%s,commission=%s,swap=%s,profit=%s,maxprofit=%s,minprofit=%s,comment='%s',magic=%s WHERE uaid=%s AND orderid=%s"
try: # print(sql)
yield cursor.execute(
sql %
(proname, num, t_type, stime, sprice, etime, eprice,
sl, tp, commission, swap, profit, maxprofit,
minprofit, comment, magic, uaid, orderid))
yield conn.commit()
return True
except Exception as err:
yield conn.rollback()
logger.error("[OrderModel:UpOrder:update]: %s" % err)
return False
def setMaterAuthorize(self, followid, uaid, follow_flag):
if uaid != None and followid != None:
with (yield pool.Connection()) as conn:
with conn.cursor() as cursor:
sql = "update follow set follow_flag = %s WHERE followid = %s AND uaid=%s" % (
follow_flag, followid, uaid)
# print(sql)
try:
yield cursor.execute(sql)
yield conn.commit()
return True
except Exception as err:
yield conn.rollback()
logger.error(
"[master_model:setMaterAuthorize:update]: %s" %
err)
return False
else:
return False
def CheckOrderList(self, uaid, OrderTicket):
# 返回查询的列表的所有订单
with (yield pool.Connection()) as conn:
with conn.cursor() as cursor:
try:
sql = "SELECT uaid,etime,sl,tp,orderid,num FROM trader WHERE uaid=%s AND orderid in %s" % (
uaid, OrderTicket)
yield cursor.execute(sql)
datas = cursor.fetchall()
if datas != None:
return datas
else:
return None
except Exception as err:
yield conn.rollback()
logger.error("[OrderModel:CheckOrderList:SELECT]: %s" %
err)
logger.error("[OrderModel:CheckOrderList:SELECT]: %s" %
sql)
return False
def CheckUserAccount(self, account, a_code):
# 查账号存在与否
with (yield pool.Connection()) as conn:
with conn.cursor() as cursor:
sql = "SELECT allname, soft_id FROM users_account WHERE pt_id=20 AND account=%s" % (
account, )
# print(sql)
yield cursor.execute(sql)
datas = cursor.fetchone()
if datas != None:
datas['allname'] = datas['allname'].strip()
datas['allname'] = datas['allname'].replace(" ", "")
a_code = a_code.strip()
a_code = a_code.replace(" ", "")
if a_code == datas['allname']:
return True, datas['soft_id']
else:
return False, 0
else:
return False, 0
def findProxyGradePrice(self, accountTuple):
# 查找等级价格
with (yield pool.Connection()) as conn:
with conn.cursor() as cursor:
# print(accountTuple)
sql = "SELECT account,grade_price FROM proxy_account_price WHERE account in %s" % (
accountTuple, )
# print(sql)
yield cursor.execute(sql)
datas = cursor.fetchall()
grade_price = []
for account in accountTuple:
flag = 0
for data in datas:
if account == data['account']:
grade_price.append(data['grade_price'])
flag = 1
break
if flag == 0:
grade_price.append(0)
return grade_price
def getProxySettlementCount(self, uid, page_main=None):
with (yield pool.Connection()) as conn:
with conn.cursor() as cursor:
# print(page_main.get('time_type'))
the_stime, the_etime = yield self.getStime(
page_main.get('time_type'))
sql = "FROM proxy_settlement WHERE uid = %s " % (uid, )
if page_main['time_type'].find("last") >= 0:
sql = sql + " AND stime >= %s AND stime < %s " % (
the_stime, the_etime)
else:
sql = sql + " AND stime >= %s " % the_stime
sql5 = "SELECT Count(*) AS all_count,Sum(amount) AS all_profit " + sql
yield cursor.execute(sql5)
datas = cursor.fetchall()
# print(datas)
if len(datas) > 0:
# datas.append({"allnum": len(datas2)})
return datas
else:
return []
def getProxyCount2(self, uid, page_main=None):
with (yield pool.Connection()) as conn:
with conn.cursor() as cursor:
# print(page_main.get('time_type'))
the_stime, the_etime = yield self.getStime(
page_main.get('time_type'))
sql = "FROM proxy_account INNER JOIN proxy_order ON proxy_account.account = proxy_order.account " \
"WHERE proxy_account.uid = %s AND proxy_account.verify = 2 AND proxy_order.flag =1 AND proxy_account.account = %s " % (uid, page_main.get('account'))
if page_main['time_type'].find("last") >= 0:
sql = sql + " AND proxy_order.stime >= %s AND proxy_order.stime < %s " % (
the_stime, the_etime)
else:
sql = sql + " AND proxy_order.stime >= %s " % the_stime
sql5 = "SELECT Count(*) AS all_count,Sum(proxy_order.proxy_profit) AS all_profit " + sql
yield cursor.execute(sql5)
datas = cursor.fetchall()
if len(datas) > 0:
# datas.append({"allnum": len(datas2)})
return datas
else:
return []
def getPostsList(self, page_main=None):
with (yield pool.Connection()) as conn:
with conn.cursor() as cursor:
# users['ptname']db.cursor(MySQLdb.cursors.DictCursor)
# print("search:", page_main.get('search'))
from_str = """FROM posts_type
INNER JOIN posts_type_relationship ON posts_type.posts_type_id = posts_type_relationship.posts_type_id
INNER JOIN posts ON posts_type_relationship.post_id = posts.post_id
INNER JOIN users ON posts.uid = users.uid"""
sql = ""
# from_str +
if page_main.get('prc_type') != None and page_main.get('prc_type') != 0 and page_main.get('prc_type') != "None":
sql = " WHERE posts_type.posts_type_id = %s" % page_main.get('prc_type')
if page_main != None and page_main.get('search') != "0" and page_main.get('search') != "":
search = "%" + page_main.get('search') + "%"
if sql == "":
sql = " WHERE posts.post_title like '%s'" % search
else:
sql = sql + " AND posts.post_title like '%s'" % search
sql2 = "SELECT COUNT(*) as allnum " + from_str + sql
# print(sql2)
yield cursor.execute(sql2)
allnum = cursor.fetchone()
if allnum['allnum'] > 0:
start = 0 if page_main.get('start') == None else page_main.get('start')
length = 10 if page_main.get('length') == None else page_main.get('length')
sql3 = "SELECT posts_type.posts_type_name_cn,posts.post_id,posts.post_date,posts.post_status,posts.post_modified,users.uname,posts.post_title," \
"posts.post_id,posts_type.posts_type_id,posts.read_count " + from_str + sql + " ORDER BY posts.post_modified DESC limit %s, %s" % (int(start), int(length))
# print(sql3)
yield cursor.execute(sql3)
datas = cursor.fetchall()
if len(datas) > 0:
datas.append(allnum)
# print(datas)
return datas
else:
return []
else:
return []
def setOrderTwo(self, Odata, uid, pid, followid):
# 提交支付订单
with (yield pool.Connection()) as conn:
with conn.cursor() as cursor:
try:
from models.public.headers_model import Headers_Models
H = Headers_Models()
# 生成个订单号
OrderNo = H.getOrderNo()
yield cursor.callproc('setOrderTwo', (followid, uid, pid, Odata['piid'], Odata['PaymentTypes'], Odata['cnh'],
Odata['fx_num'], Odata['daytype'], Odata['datetype'], OrderNo,"@out_mflag"))
yield cursor.execute("select @out_mflag;")
# for result in cursor.stored_results():
# print(result.fetchall())
row = cursor.fetchone()
if row != None:
return row['@out_mflag'], OrderNo
else:
return -6
except Exception as err:
self.logger.error("[AccountsModel:setOrderTwo:]: %s" % err)
return -6
def CheckOrder(self, uaid, orderid, CloseT, OrderStopLoss, OrderTakeProfit):
# 查订单存在与否
edit_flag = 0
with (yield pool.Connection()) as conn:
with conn.cursor() as cursor:
sql = "SELECT uaid,etime,sl,tp FROM trader WHERE uaid=%s AND orderid=%s" % (uaid, orderid)
# print(sql)
yield cursor.execute(sql)
datas = cursor.fetchone()
if datas != None:
if CloseT != datas['etime']:
edit_flag = 1
if OrderStopLoss != datas['sl']:
edit_flag = 1
if OrderTakeProfit != datas['tp']:
edit_flag = 1
else:
return None
if edit_flag == 0:
return True
else:
return False
def checkMaterFollow(self, followid, uaid):
with (yield pool.Connection()) as conn:
with conn.cursor() as cursor:
# users['ptname']db.cursor(MySQLdb.cursors.DictCursor)
sql = "SELECT uaid,follow_flag,followid,f_flag FROM follow WHERE followid = %s AND uaid = %s"
# logger.info(sql)
yield cursor.execute(sql % (followid, uaid))
datas = cursor.fetchone()
if datas == None:
try:
up_date = datetime.datetime.now().strftime(
'%Y-%m-%d %H:%M:%S')
sql2 = "INSERT INTO follow(uaid,follow_flag,followid,f_flag,update_time) VALUES(%s, %s, %s, %s,'%s')" % (
uaid, 0, followid, 1, up_date)
# logger.info(sql2)
yield cursor.execute(sql2)
yield conn.commit()
except Exception as err:
yield conn.rollback()
logger.error(
"[master_model:checkMaterFollow:INSERT]: %s" % err)
return False
return True
def editPAVerify2(self, account):
# 更改账号的返佣状态
p_flag = yield self.CheckProxyOrder(account)
if p_flag == True:
verify_flag = 2
else:
verify_flag = 1
with (yield pool.Connection()) as conn:
with conn.cursor() as cursor:
sql = "update proxy_account set verify=%s WHERE account=%s"
try:
yield cursor.execute(sql % (verify_flag, account))
yield conn.commit()
if p_flag == True:
return 5
else:
return 1
except Exception as err:
yield conn.rollback()
logging.error("[ProxyOrderModel:editPAVerify:update]: %s" %
err)
return -1
def getProxyOrderCountListAll(self, page_main=None):
with (yield pool.Connection()) as conn:
with conn.cursor() as cursor:
# print(page_main.get('time_type'))
the_stime, the_etime = yield self.getStime(
page_main.get('time_type'))
sql = "FROM proxy_users INNER JOIN proxy_account ON proxy_users.uid = proxy_account.uid INNER JOIN proxy_order ON proxy_account.account = proxy_order.account " \
"WHERE proxy_account.verify = 2 AND proxy_order.flag =1 "
if page_main.get('search') != "0" and page_main.get(
'search') != "":
search = "%" + page_main.get('search') + "%"
sql = sql + " AND proxy_users.uname like '" + search + "' "
if page_main['time_type'].find("last") >= 0:
sql = sql + " AND proxy_order.stime >= %s AND proxy_order.stime < %s " % (
the_stime, the_etime)
else:
sql = sql + " AND proxy_order.stime >= %s " % the_stime
sql2 = "SELECT proxy_users.uid " + sql + " GROUP BY proxy_users.uid "
# print(sql2)
yield cursor.execute(sql2)
datas2 = cursor.fetchall()
start = 0 if page_main.get('start') == None else page_main.get(
'start')
length = 10 if page_main.get(
'length') == None else page_main.get('length')
sql3 = "SELECT Count(*) AS t_count,proxy_users.uid,proxy_users.uname,Sum(proxy_order.profit) AS to_profit,Sum(proxy_order.proxy_profit) AS sum_profit "
sql3 = sql3 + sql + "GROUP BY proxy_users.uid ORDER BY sum_profit DESC limit %s, %s" % (
int(start), int(length))
# print(sql3)
yield cursor.execute(sql3)
datas = cursor.fetchall()
if len(datas) > 0:
datas.append({"allnum": len(datas2)})
return datas
else:
return []
def setStrategyOpenFlag(self, page_main=None):
if page_main == None:
return []
with (yield pool.Connection()) as conn:
with conn.cursor() as cursor:
try:
if int(page_main['fx_flag']) == 0:
page_main['fx_flag'] = 1
else:
page_main['fx_flag'] = 0
sql = "update predict_stratepy SET open_flag = %s WHERE strategy_id = %s AND symbol_name = %s"
yield cursor.execute(sql, (int(
page_main['fx_flag']), int(page_main['strategy_id']),
page_main['symbol_name']))
yield conn.commit()
if page_main['fx_flag'] == 1:
return 1
else:
return 0
except Exception as err:
yield conn.rollback()
self.logger.error(
"[StrategyModel:setStrategyOpenFlag] %s" % err)
return 9
def getStrategySymbolMa(self, page_main=None):
if page_main == None:
return []
with (yield pool.Connection()) as conn:
with conn.cursor() as cursor:
# users['ptname']db.cursor(MySQLdb.cursors.DictCursor)
sql = "SELECT predict_date AS g_date, straregy_cumsum FROM predict WHERE strategy_id = %s AND symbol_name = %s ORDER BY predict_date DESC limit 800"
yield cursor.execute(
sql, (page_main['strategy_id'], page_main['symbol_name']))
datas = cursor.fetchall()
# print(datas)
if len(datas) > 0:
datas = pd.DataFrame(list(reversed(datas)))
for timeperiod_ in self.time_period:
datas['sg_ma' + str(timeperiod_)] = datas[
'straregy_cumsum'].rolling(timeperiod_).mean()
datas['g_date'] = datas['g_date'].apply(
lambda x: str(x.strftime('%Y-%m-%d %H')))
datas_columns = list(datas.columns)
datas_columns.remove("g_date")
return json.loads(
datas.to_json(orient='records')), datas_columns
else:
return [], []
def getPayOrderList(self, page_main=None):
with (yield pool.Connection()) as conn:
with conn.cursor() as cursor:
# users['ptname']db.cursor(MySQLdb.cursors.DictCursor)
if page_main == None or page_main.get('fx_id') == 9:
sql = " FROM p_order INNER JOIN product_info ON p_order.piid = product_info.piid INNER JOIN users ON p_order.uid = users.uid INNER JOIN users_account ON p_order.uaid = users_account.uaid WHERE p_order.pid=%s" % (config.PID,)
else:
# search = "%" + page_main.get('search') + "%"
sql = " FROM p_order INNER JOIN product_info ON p_order.piid = product_info.piid INNER JOIN users ON p_order.uid = users.uid INNER JOIN users_account ON p_order.uaid = users_account.uaid WHERE p_order.pid=%s AND otype = %s" % (config.PID, page_main.get('fx_id'))
if page_main != None and page_main.get('search') != "0" and page_main.get('search') != "":
search = "%" + page_main.get('search') + "%"
if sql == "":
sql = " WHERE p_order.order_no like '%s' OR users.email like '%s' OR users_account.account like '%s' " % (search, search, search)
else:
sql = sql + " AND p_order.order_no like '%s' OR users.email like '%s' OR users_account.account like '%s' " % (search, search, search)
sql2 = "SELECT product_info.piname " + sql
# print(sql2)
yield cursor.execute(sql2)
datas2 = cursor.fetchall()
start = 0 if page_main.get('start') == None else page_main.get('start')
length = 10 if page_main.get('length') == None else page_main.get('length')
sql3 = "SELECT product_info.piname,product_info.info2,product_info.info3,p_order.onum,p_order.otime," \
"p_order.amount_cny,p_order.amount,p_order.otype,product_info.info1,p_order.strattime," \
"p_order.endtime,p_order.order_no,p_order.date_num,p_order.payment_type,p_order.potype," \
"p_order.oid,users.email,users_account.account "\
+ sql + " ORDER BY p_order.oid DESC limit %s, %s" % (int(start), int(length))
print(sql3)
yield cursor.execute(sql3)
datas = cursor.fetchall()
if len(datas) > 0:
datas.append({"allnum": len(datas2)})
# print(datas)
return datas
else:
return []
def addProxyAccount(self, web_uid, account, a_code):
# 新增账号
with (yield pool.Connection()) as conn:
with conn.cursor() as cursor:
u_flag, soft_id = yield self.CheckUserAccount(account, a_code)
if u_flag == True:
p_flag = yield self.CheckProxyAccount(account)
if p_flag == False:
sql = "INSERT INTO proxy_account(uid,account,verify,soft_id) VALUES(%s,%s,1,%s)"
try: # print(sql)
yield cursor.execute(sql %
(web_uid, account, soft_id))
yield conn.commit()
return 5
except Exception as err:
yield conn.rollback()
logging.error(
"[ProxyOrderModel:addProxyAccount:INSERT]: %s"
% err)
return -1
else:
return -2
else:
return -3
def getStrategyCount(self, page_main=None):
# 返回统计数据
with (yield pool.Connection()) as conn:
with conn.cursor() as cursor:
if page_main == None:
return []
H = Headers_Models()
the_year = yield H.getMktime("theYear")
the_month = yield H.getMktime("theMonth")
the_week = yield H.getMktime("theWeek")
# 持仓统计
sql = "SELECT Sum(trader.num) as out_position_num,Sum(trader.swap) as out_position_swap,Sum(trader.commission) as out_position_comm,Sum(trader.profit) as out_position_profit,COUNT(*) as out_position_order_num FROM trader WHERE t_type < 2 AND uaid = " + str(
page_main['uaid']) + " AND etime = 0"
yield cursor.execute(sql)
data = cursor.fetchone()
# 今天
sql2 = "SELECT Sum(trader.swap) as out_swap_today,Sum(trader.commission) as out_comm_today,Sum(trader.profit) as out_profit_today FROM trader WHERE t_type < 2 AND uaid = " + str(
page_main['uaid']
) + " AND etime > unix_timestamp(curdate())"
yield cursor.execute(sql2)
data2 = cursor.fetchone()
# 当周
sql3 = "SELECT Sum(trader.swap) as out_swap_week,Sum(trader.commission) as out_comm_week,Sum(trader.profit) as out_profit_week FROM trader WHERE t_type < 2 AND uaid = " + str(
page_main['uaid']) + " AND etime > " + str(the_week)
yield cursor.execute(sql3)
data3 = cursor.fetchone()
# 当月
sql4 = "SELECT Sum(trader.swap) as out_swap_month,Sum(trader.commission) as out_comm_month,Sum(trader.profit) as out_profit_month FROM trader WHERE t_type < 2 AND uaid = " + str(
page_main['uaid']) + " AND etime > " + str(the_month)
yield cursor.execute(sql4)
data4 = cursor.fetchone()
# 当年
sql5 = "SELECT Sum(trader.swap) as out_swap_year,Sum(trader.commission) as out_comm_year,Sum(trader.profit) as out_profit_year FROM trader WHERE t_type < 2 AND uaid = " + str(
page_main['uaid']) + " AND etime > " + str(the_year)
yield cursor.execute(sql5)
data5 = cursor.fetchone()
# 全部
sql6 = "SELECT Sum(trader.swap) as out_swap_all,Sum(trader.commission) as out_comm_all,Sum(trader.profit) as out_profit_all FROM trader WHERE t_type < 2 AND uaid = " + str(
page_main['uaid']) + " AND etime > 0"
yield cursor.execute(sql6)
data6 = cursor.fetchone()
# 统计资金
sql7 = "SELECT balance as out_balance, credit as out_credit, quity as out_quity, profit as out_profit FROM users_account WHERE uaid = " + str(
page_main['uaid'])
yield cursor.execute(sql7)
data7 = cursor.fetchone()
# 格式化
data = yield self.getDictValue(data)
data2 = yield self.getDictValue(data2)
data3 = yield self.getDictValue(data3)
data4 = yield self.getDictValue(data4)
data5 = yield self.getDictValue(data5)
data6 = yield self.getDictValue(data6)
# 计算
try:
data7['out_position_profit'] = data[
'out_position_swap'] + data[
'out_position_comm'] + data['out_position_profit']
data7['out_today'] = data2['out_swap_today'] + data2[
'out_comm_today'] + data2['out_profit_today']
data7['out_week'] = data3['out_swap_week'] + data3[
'out_comm_week'] + data3['out_profit_week']
data7['out_month'] = data4['out_swap_month'] + data4[
'out_comm_month'] + data4['out_profit_month']
data7['out_year'] = data5['out_swap_year'] + data5[
'out_comm_year'] + data5['out_profit_year']
data7['out_all'] = data6['out_swap_all'] + data6[
'out_comm_all'] + data6['out_profit_all']
data7['out_position_num'] = data['out_position_num']
data7['out_position_order_num'] = data[
'out_position_order_num']
except:
data7['out_position_profit'] = 0
data7['out_today'] = 0
data7['out_week'] = 0
data7['out_month'] = 0
data7['out_year'] = 0
data7['out_all'] = 0
data7['out_position_num'] = 0
data7['out_position_order_num'] = 0
return data7
def getOrderCount(self, page_main=None):
if page_main == None:
return []
with (yield pool.Connection()) as conn:
with conn.cursor() as cursor:
try:
if page_main == None:
the_etime = 0
else:
from models.public.headers_model import Headers_Models
H = Headers_Models()
if page_main['time_type'] == "the_year":
the_stime = yield H.getMktime("theYear")
select_sql = " DATE_FORMAT(FROM_UNIXTIME(etime), '%Y%m') as group_time "
elif page_main['time_type'] == "the_month":
the_stime = yield H.getMktime("theMonth")
select_sql = " DATE_FORMAT(FROM_UNIXTIME(etime), '%Y%m%d') as group_time "
elif page_main['time_type'] == "the_week":
the_stime = yield H.getMktime("theWeek")
select_sql = " DATE_FORMAT(FROM_UNIXTIME(etime), '%Y%m%d') as group_time "
elif page_main['time_type'] == "last_month":
the_stime = yield H.getMktime("lastMonthOne")
the_etime = yield H.getMktime("lastMonth")
select_sql = " DATE_FORMAT(FROM_UNIXTIME(etime), '%Y%m%d') as group_time "
elif page_main['time_type'] == "last_year":
the_stime = yield H.getMktime("lastYearOne")
the_etime = yield H.getMktime("lastYear")
select_sql = " DATE_FORMAT(FROM_UNIXTIME(etime), '%Y%m') as group_time "
elif page_main['time_type'] == "recent_week":
the_stime = int(time.time()) - 60 * 60 * 24 * 7
select_sql = " DATE_FORMAT(FROM_UNIXTIME(etime), '%Y%m%d') as group_time "
elif page_main['time_type'] == "recent_month":
the_stime = int(time.time()) - 60 * 60 * 24 * 30
select_sql = " DATE_FORMAT(FROM_UNIXTIME(etime), '%Y%m%d') as group_time "
elif page_main['time_type'] == "recent_month3":
the_stime = int(time.time()) - 60 * 60 * 24 * 90
select_sql = " DATE_FORMAT(FROM_UNIXTIME(etime), '%Y%m%d') as group_time "
elif page_main['time_type'] == "recent_month6":
the_stime = int(time.time()) - 60 * 60 * 24 * 180
select_sql = " DATE_FORMAT(FROM_UNIXTIME(etime), '%Y%m%d') as group_time "
elif page_main['time_type'] == "recent_year":
the_stime = int(time.time()) - 60 * 60 * 24 * 365
select_sql = " DATE_FORMAT(FROM_UNIXTIME(etime), '%Y%m') as group_time "
else:
the_stime = 0
select_sql = " DATE_FORMAT(FROM_UNIXTIME(etime), '%Y%m') as group_time "
sql = "FROM trader WHERE uaid = %s AND etime > 0 " % page_main[
'uaid']
if page_main['time_type'].find("last") >= 0:
sql = sql + " AND etime >= %s AND etime < %s " % (
the_stime, the_etime)
else:
sql = sql + " AND etime >= %s " % the_stime
sql2 = "SELECT uaid, " + select_sql + sql + " GROUP BY group_time "
# print(sql2)
yield cursor.execute(sql2)
datas2 = cursor.fetchall()
start = 0 if page_main.get(
'start') == None else page_main.get('start')
length = 10 if page_main.get(
'length') == None else page_main.get('length')
sql3 = "SELECT " + select_sql + ", Count(*) AS t_count,Sum(trader.num) AS t_num,Sum(trader.profit) AS t_profit,Sum(trader.swap) AS t_swap," \
"Sum(trader.commission) AS t_comm "
sql3 = sql3 + sql + " GROUP BY group_time ORDER BY group_time DESC limit %s, %s" % (
int(start), int(length))
# print(sql3)
yield cursor.execute(sql3)
datas = cursor.fetchall()
if len(datas) > 0:
datas.append({"allnum": len(datas2)})
return datas
else:
return []
except Exception as err:
yield conn.rollback()
self.logger.error("[StrategyModel:getOrderCount] %s" % err)
return []
