def get_realtime_statistic_data(p_no=None):
day = date.today()
end_day = day + timedelta(days=1)
from_timestamp = time.mktime(day.timetuple())
end_timestamp = time.mktime(end_day.timetuple())
session = Session()
if p_no:
results = session.query(OrderDeal.p_no, func.sum(cast((OrderDeal.deal_volume),VARCHAR)), func.sum(cast(OrderDeal.deal_time * OrderDeal.price),VARCHAR),\
func.max(OrderDeal.price), func.min(OrderDeal.price))\
.filter(OrderDeal.p_no==p_no, OrderDeal.deleted==0, OrderDeal.deal_time.between(from_timestamp, end_timestamp))\
.group_by(OrderDeal.p_no).order_by(OrderDeal.p_no).first()
else:
results = session.query(OrderDeal.p_no, func.sum(cast((OrderDeal.deal_volume), VARCHAR)),\
func.sum(cast(OrderDeal.deal_time * OrderDeal.price), VARCHAR), \
func.max(OrderDeal.price), func.min(OrderDeal.price)) \
.filter(OrderDeal.deleted == 0, OrderDeal.deal_time.between(from_timestamp, end_timestamp)) \
.group_by(OrderDeal.p_no).order_by(OrderDeal.p_no).all()
session.close()
return results
def get_in_using_money(user_id):
session = Session()
day = date.today()
end_day = day + timedelta(days=1)
from_timestamp = time.mktime(day.timetuple())
end_timestamp = time.mktime(end_day.timetuple())
in_buying_money = session.query(func.sum((Order.volume-Order.deal_volume) * Order.price * (1 + UserHandler.get_bonus_ratio(user_id)))) \
.filter(Order.deleted == 0, Order.user_id == user_id, Order.direction==Order.OrderDirectionBuy,\
or_(Order.status == Order.OrderStatusPending, \
Order.status == Order.OrderStatusCommitted, \
Order.status == Order.OrderStatusPartialFinished), Order.created_at.between(from_timestamp, end_timestamp)).group_by(Order.user_id).scalar()
if not in_buying_money:
in_buying_money = 0
in_withdrawing_money = session.query(func.sum(UW.amount))\
.filter(UW.deleted==0,UW.user_id==user_id,UW.status==UW.StatusPending).scalar()
if not in_withdrawing_money:
in_withdrawing_money = 0
session.close()
return round((float(in_buying_money)+float(in_withdrawing_money)),2)
def process(cmd, args):
session = Session()
if isinstance(args, dict):
p_no = args['P_NO']
else:
p_no = args
if cmd == 'CMD_GET_DELEGATE_SALE':
direction = Order.OrderDirectionSale
elif cmd == 'CMD_GET_DELEGATE_BUY':
direction = Order.OrderDirectionBuy
day = date.today()
end_day = day + timedelta(days=1)
from_timestamp = time.mktime(day.timetuple())
end_timestamp = time.mktime(end_day.timetuple())
results = session.query(Order.p_no, Order.price, func.sum(cast((Order.volume-Order.deal_volume),VARCHAR)))\
.filter( \
Order.deleted==0, \
or_(Order.status==Order.OrderStatusCommitted, Order.status==Order.OrderStatusPartialFinished), \
Order.p_no==p_no, \
Order.direction==direction, \
Order.created_at.between(from_timestamp, end_timestamp)
).group_by(Order.price, Order.p_no).order_by(Order.price).limit(5).all()
return results
def get_products(self):
session = Session()
products = session.query(P).filter(P.status == P.ProductStatusActive,
P.deleted == 0).all()
return products
def process(args):
session = Session()
if isinstance(args, dict):
p_no = args['P_NO']
else:
p_no = args
if isinstance(args, dict) and args.get('DATE', None):
day = datetime.strptime(args['DATE'], '%Y-%m-%d')
end_day = day + timedelta(days=1)
else:
day = date.today()
end_day = day + timedelta(days=1)
from_timestamp = time.mktime(day.timetuple())
end_timestamp = time.mktime(end_day.timetuple())
results = session.query(OrderDeal.id, OrderDeal.p_no, OrderDeal.volume, OrderDeal.price, OrderDeal.deal_time)\
.filter(OrderDeal.p_no==p_no, OrderDeal.deleted==0, OrderDeal.deal_time.between(from_timestamp, end_timestamp))\
.order_by(OrderDeal.id.asc()).all()
session.close()
return results
def process(args):
#ids = args['IDS']
#catalogs = args['CATALOGS']
session = Session()
results = session.query(Product).filter_by(
deleted=0,
status=Product.ProductStatusActive).order_by(Product.p_no).all()
args = []
for i in range(len(results)):
p = results[i]
args.append({
p.p_no: {
'ID': p.id,
'NAME': p.name,
'PUB_ID': p.pub_id,
'ISSUE_PRICE': p.issue_price,
'UNIT': p.unit,
'QTY': p.qty,
'TURN_QTY': p.turn_qty,
'LAST_PRICE': p.last_price
}
})
return args
def process(args):
session = Session()
name = args['LOGIN_NAME']
password = User.encrypt_pass(args['LOGIN_PASS'])
users = session.query(User).filter_by(login_name=name,
login_pass=password,
deleted=0).all()
if len(users) == 0:
return 'FAILED' #not exists or password error
if len(users) == 1:
user = users[0]
if user.status == User.UserStatusPending:
return 'PENDING'
if user.status == User.UserStatusFrozen:
return 'FROZEN'
if user.status == User.UserStatusBlack:
return 'BLACK'
if user.status == User.UserStatusActive:
return 'SUCCESS'
#TODO maybe there are replicate user
return 'SYSTEM_ERROR'
def set_k_data_to_redis(redis, p_no):
session = Session()
results = session.query(cast(PH.work_date, VARCHAR), PH.open_price, PH.current_price, PH.min_price,
PH.max_price) \
.filter(PH.p_no == p_no) \
.order_by(PH.work_date.asc()).all()
rlist = []
for i in range(len(results)):
tmp = []
work_date = results[i][0]
if operator.eq(work_date, date.today().strftime('%Y-%m-%d')):
open_price = results[i][1]
if operator.eq(open_price, 0.00): # not opened
tmp.append(work_date)
tmp.append(results[i][2])
tmp.append(results[i][2])
tmp.append(results[i][2])
tmp.append(results[i][2])
else:
tmp = list(results[i])
else:
tmp = list(results[i])
rlist.append(tmp)
data = rlist
key = "{}{}".format('REDIS_KEY_FOR_CHART_K_DATA_', p_no)
redis.set(key, json.JSONEncoder().encode(data))
session.close()
def process(args):
session = Session()
o_id = args['O_ID']
message = 'success'
try:
order = session.query(Order).with_for_update().filter(
Order.id == o_id).first()
if order.status == Order.OrderStatusPending or \
order.status == Order.OrderStatusCommitted:
order.status = Order.OrderStatusClosed
elif order.status == Order.OrderStatusPartialFinished:
order.status = Order.OrderStatusPartialClosed
else:
return 'fail to close status:{} for order:{}'.format(
order.status, o_id)
session.commit()
except DBAPIError as e:
print(e.args[0])
message = 'DBAPI Error occured!'
session.rollback()
except DatabaseError as e:
print(e.args[0])
message = 'Lock wait timeout exceeded'
session.rollback()
finally:
session.close()
return message
def do_cancel(user_id, order_id):
session = Session()
order = session.query(Order).with_for_update().filter(
Order.id == order_id,
Order.user_id == user_id).limit(1).one_or_none()
if not order:
return False, '委托订单无效'
if not ProductHandler.is_in_exchange(order.p_no):
return False, '非交易时间不允许委托'
if order.status == Order.OrderStatusCommitted or Order.status == Order.OrderStatusPending:
order.status = Order.OrderStatusClosed
elif order.status == Order.OrderStatusPartialFinished:
order.status = Order.OrderStatusPartialClosed
p_no = order.p_no
try:
session.commit()
except:
session.rollback()
session.close()
return False, '系统出错', p_no
else:
session.close()
return True, 'success', p_no
def login(login_name, login_pass):
session = Session()
user = session.query(User).filter(User.login_name==login_name,User.login_pass==User.encrypt_pass(login_pass),\
User.deleted==0).one_or_none()
return user
def get_available_money(user_id):
session = Session()
# calc whole deposit money
total_money = session.query(func.sum(UM.amount - UM.frozen_part)) \
.filter(UM.deleted == 0, UM.user_id == user_id, UM.status == UM.StatusActive, UM.can_deal == True).scalar()
if not total_money:
total_money = 0
# calc whole income by sold(in orders)
income_money = session.query(func.sum(OrderDeal.price * OrderDeal.volume * (1 - UserHandler.get_bonus_ratio(user_id)))) \
.filter(OrderDeal.suser_id == user_id, OrderDeal.deleted == 0).scalar()
if not income_money:
income_money = 0
# calc whole consumed money(in orders)
'''
consumed_money = session.query(func.sum(Order.deal_volume * Order.price * (1 + UserHandler.get_bonus_ratio(user_id)))) \
.filter(Order.deleted == 0, Order.user_id == user_id, Order.direction == Order.OrderDirectionBuy, \
or_(Order.status == Order.OrderStatusFinished, \
Order.status == Order.OrderStatusPartialFinished, \
Order.status == Order.OrderStatusPartialClosed)).scalar()
'''
consumed_money = session.query(func.sum(OrderDeal.price * OrderDeal.volume) * (1 + UserHandler.get_bonus_ratio(user_id)))\
.filter(OrderDeal.deleted==0,OrderDeal.buser_id==user_id).scalar()
if not consumed_money:
consumed_money = 0
# calc withdraw finished
withdraw_money = session.query(func.sum(UW.amount))\
.filter(UW.deleted==0,UW.user_id==user_id,UW.status==UW.StatusFinished)\
.scalar()
if not withdraw_money:
withdraw_money = 0
available_money = float(total_money) + float(income_money) - float(consumed_money) - float(withdraw_money)
if operator.le(available_money, 0):
available_money = 0
session.close()
return round(available_money,2)
def get_deposit_history(user_id):
session = Session()
results = session.query(UserMoney.id,UserMoney.amount,UserMoney.frozen_part,UserMoney.status,UserMoney.source,UserMoney.can_deal,UserMoney.can_withdraw, UserMoney.created_at)\
.filter(UserMoney.deleted==0, UserMoney.user_id==user_id)\
.order_by(UserMoney.created_at.desc()).all()
return results
def get_banks(user_id):
session = Session()
result = session.query(UserBank.id,UserBank.bank_no,UserBank.bank_name)\
.filter(UserBank.deleted==0, UserBank.user_id==user_id)\
.order_by(UserBank.is_default.desc(),UserBank.id).all()
return result
def checkIdNo(id_no):
session = Session()
user = session.query(User).filter(User.id_no==id_no, User.deleted==0).limit(1).one_or_none()
if user:
return True
return False
def get_withdraw_history(user_id):
session = Session()
results = session.query(UW.id,UserBank.bank_no,UW.amount,UW.status,UW.created_at)\
.join(UserBank, UserBank.id==UW.bank_id)\
.filter(UW.deleted==0,UW.user_id==user_id)\
.order_by(UW.id.desc()).all()
return results
def get_frozen_money(user_id):
session = Session()
money = session.query(func.sum(UM.frozen_part)).filter(UM.user_id==user_id,UM.deleted==0).scalar()
if not money:
money = 0
session.close()
return round(float(money), 2)
def get_app_products_list(args=None):
session = Session()
results = session.query(P.p_no,P.name,P.status).filter(P.deleted==0,or_(P.status==P.ProductStatusActive,P.status==P.ProductStatusPending, P.status==P.ProductStatusStopped))\
.order_by(P.name.asc())
tmp_list = []
for p_no, name, status in results:
phs = session.query(PH.last_price,PH.open_price,PH.current_price,PH.max_price,PH.min_price,PH.total_volume,PH.total_volume)\
.filter(PH.p_no==p_no, PH.work_date==datetime.date.today()).order_by(PH.id.desc()).limit(1).one_or_none()
print(phs)
d = {}
d['p_no'] = p_no
d['name'] = name
d['status'] = status
if phs and len(phs):
d['last_price'] = phs[0]
d['open_price'] = phs[1]
d['current_price'] = phs[2]
d['max_price'] = phs[3]
d['min_price'] = phs[4]
d['total_volume'] = phs[5]
d['total_amount'] = phs[6]
d['gains'] = round(
(float(phs[2]) - float(phs[0])) / phs[0] * 100, 2)
d['turn'] = round((float(phs[5])) /
float(ProductHandler.get_product_qty(p_no)),
2)
else:
d['last_price'] = ProductHandler.get_last_price(p_no)
d['open_price'] = 0.00
d['current_price'] = d['last_price']
d['max_price'] = 0.00
d['min_price'] = 0.00
d['total_volume'] = 0
d['total_amount'] = 0.00
d['gains'] = 0.00
d['turn'] = 0.00
tmp_list.append(d)
return tmp_list
def get_frozen_asset_volume(user_id, p_no):
session = Session()
volume = session.query(func.sum(UserAssets.qty)).filter(UserAssets.deleted==0,UserAssets.user_id==user_id,UserAssets.p_no==p_no,UserAssets.can_deal==False)\
.group_by(UserAssets.user_id,UserAssets.p_no).scalar()
if not volume:
volume = 0
session.close()
return volume
def get_can_withdraw_money(user_id):
session = Session()
can_not_withdraw_money = session.query(func.sum(UM.amount-UM.frozen_part))\
.filter(UM.deleted == 0, UM.user_id == user_id, UM.status == UM.StatusActive, UM.can_withdraw==False).scalar()
if not can_not_withdraw_money:
can_not_withdraw_money = 0
can_used_money = UserHandler.get_can_used_money(user_id)
return round(can_used_money-float(can_not_withdraw_money), 2)
def update_prodoct_history(p_no, current_price):
session = Session()
ph = session.query(PH).filter_by(p_no=p_no,
work_date=datetime.date.today(),
deleted=0).one_or_none()
if not ph:
ph = PH()
ph.work_date = datetime.date.today()
ph.p_no = p_no
ph.last_price = Common.get_last_price(session, p_no)
ph.open_price = current_price #Common.get_open_price(session, p_no)
if not operator.eq(current_price, 0):
ph.current_price = current_price
else:
ph.current_price = ph.last_price
max_price, min_price, total_volume, total_amount = ProductHandler.get_statistic_total(
p_no) #Common.get_statistic_total(session, p_no)
ph.max_price = max_price
ph.min_price = min_price
ph.total_volume = total_volume
ph.total_amount = total_amount
session.add(ph)
else:
if operator.eq(ph.open_price, 0):
ph.open_price = current_price
if not operator.eq(current_price, 0):
ph.current_price = current_price
elif operator.eq(ph.current_price, 0):
ph.current_price = ph.last_price
max_price, min_price, total_volume, total_amount = ProductHandler.get_statistic_total(
p_no) #Common.get_statistic_total(session, p_no)
ph.max_price = max_price
ph.min_price = min_price
ph.total_volume = total_volume
ph.total_amount = total_amount
try:
session.commit()
except DBAPIError as e:
print(e.arg[0])
finally:
session.close()
def get_my_delegates(user_id):
session = Session()
day = date.today()
end_day = day + timedelta(days=1)
from_timestamp = time.mktime(day.timetuple())
end_timestamp = time.mktime(end_day.timetuple())
results = session.query(Order.id, Order.direction,func.from_unixtime(Order.created_at,'%H:%i:%s'),Product.name,Order.p_no,Order.price,Order.volume,Order.deal_volume,Order.status)\
.join(Product,Product.p_no==Order.p_no)\
.filter(Order.deleted==0, Order.user_id==user_id, Order.created_at.between(from_timestamp, end_timestamp))\
.order_by(Order.p_no.desc(),Order.created_at.desc()).all()
return results
def get_app_product_info(p_no=None):
if not p_no:
raise ValueError
session = Session()
result = session.query(PH.last_price,PH.open_price,PH.current_price,PH.max_price,PH.min_price,PH.total_volume,PH.total_amount)\
.filter(PH.p_no==p_no, PH.work_date==datetime.date.today(),PH.deleted==0).limit(1).one_or_none()
d = {}
d['p_no'] = p_no
d['name'] = ProductHandler.get_product_name(p_no)
if result and len(result):
d['last_price'] = result[0]
d['open_price'] = result[1]
d['current_price'] = result[2]
d['max_price'] = result[3]
d['min_price'] = result[4]
d['total_volume'] = result[5]
d['total_amount'] = result[6]
d['gains'] = round(
(float(d['current_price']) - float(d['last_price'])) /
float(d['last_price']) * 100, 2)
d['delta'] = round(
float(d['current_price']) - float(d['last_price']), 2)
d['turn'] = round((float(result[5])) /
float(ProductHandler.get_product_qty(p_no)), 2)
else:
d['last_price'] = ProductHandler.get_last_price(p_no)
d['open_price'] = 0.00
d['current_price'] = d['last_price']
d['max_price'] = 0.00
d['min_price'] = 0.00
d['total_volume'] = 0
d['total_amount'] = 0.00
d['gains'] = 0.00
d['delta'] = 0.00
d['turn'] = 0.00
session.close()
return d
def is_in_exchange(p_no):
if ConfigParser.get('development', None):
return True
session = Session()
ex_delta = session.query(P.ex_from, P.ex_end).filter(
P.p_no == p_no, P.deleted == 0,
P.status == P.ProductStatusActive).limit(1).one_or_none()
session.close()
today = datetime.date.today()
if ex_delta:
ex_from = ex_delta[0]
ex_end = ex_delta[1]
if ex_from:
if operator.lt(today, ex_from):
return False
else:
if ex_end:
if operator.lt(ex_end, today):
return False
else:
return False #force to set start exchanging date
else:
return False #terrible errors
weekday = datetime.date.today().isoweekday()
if operator.ge(weekday, 1) and operator.le(weekday, 5):
tnow = datetime.datetime.now().strftime('%H:%M')
if operator.ge(tnow, '09:30') and operator.lt(tnow, '11:30') \
or operator.ge(tnow, '13:00') and operator.lt(tnow, '15:00'):
return True
else:
return False
else:
return False
'''
def get_suns(user_id, level=1):
session = Session()
tmp = [user_id]
li = 1
suns = 0
while True:
ids = session.query(User.id).filter(User.referrer_id.in_(','.join(tmp)),User.deleted==0,User.status==User.UserStatusActive).all()
if not ids or not len(ids):
break
if not operator.eq(level, 0) and operator.eq(li,level):
suns += len(ids)
break
tmp.clear()
for id in ids:
tmp.append(id[0])
session.close()
return suns
def update_prodoct_history(p_no, current_price, max_price, min_price,
total_volume, total_amount):
session = Session()
ph = session.query(ProductHistory).filter_by(
p_no=p_no, work_date=datetime.date.today()).order_by(
ProductHistory.id.desc()).first()
print(ph)
if not ph:
ph = ProductHistory()
ph.work_date = datetime.date.today()
ph.p_no = p_no
ph.last_price = OrderDeal.get_last_price(p_no)
ph.open_price = OrderDeal.get_open_price(p_no)
ph.current_price = current_price
ph.max_price = max_price
ph.min_price = min_price
ph.total_volume = total_volume
ph.total_amount = total_amount
session.add(ph)
else:
ph.current_price = current_price
ph.max_price = max_price
ph.min_price = min_price
ph.total_volume = total_volume
ph.total_amount = total_amount
try:
session.commit()
except DBAPIError as e:
print(e.args[0])
finally:
session.close()
def get_inusing_asset_volume(user_id, p_no):
session = Session()
day = date.today()
end_day = day + timedelta(days=1)
from_timestamp = time.mktime(day.timetuple())
end_timestamp = time.mktime(end_day.timetuple())
volume = session.query(func.sum(Order.volume - Order.deal_volume))\
.filter(Order.deleted==0, Order.direction==Order.OrderDirectionSale, \
or_(Order.status==Order.OrderStatusCommitted,\
Order.status==Order.OrderStatusPartialFinished),\
Order.user_id==user_id,Order.p_no==p_no,\
Order.created_at.between(from_timestamp, end_timestamp)
).group_by(Order.user_id,Order.p_no).scalar()
print('using assets:{},{}'.format(p_no,volume))
if not volume:
volume = 0
session.close()
return float(volume)
def get_statistic_total(p_no, d=datetime.date.today()):
session = Session()
result = session.query(func.max(OrderDeal.price), func.min(OrderDeal.price), func.sum(OrderDeal.volume),
func.sum(cast((OrderDeal.price * OrderDeal.volume), VARCHAR))) \
.filter(OrderDeal.p_no == p_no, OrderDeal.deal_time > time.mktime(d.timetuple())) \
.group_by(OrderDeal.p_no).all()
print(result)
if result:
for max_price, min_price, total_volume, total_amount in result:
max_price = max_price
min_price = min_price
total_volume = total_volume
total_amount = total_amount
else:
max_price = 0.00
min_price = 0.00
total_volume = 0
total_amount = 0.00
session.close()
return max_price, min_price, total_volume, total_amount
def get_products_paginate(page, page_size):
session = Session()
products = session.query(P).all()
return products
def set_deal_data_to_redis(redis, p_no=None):
session = Session()
day = date.today()
end_day = day + timedelta(days=1)
from_timestamp = time.mktime(day.timetuple())
end_timestamp = time.mktime(end_day.timetuple())
results = session.query(OrderDeal.id, func.from_unixtime(OrderDeal.deal_time, '%H:%i'), OrderDeal.price,
OrderDeal.volume) \
.filter(OrderDeal.p_no == p_no, OrderDeal.deleted == 0,
OrderDeal.deal_time.between(from_timestamp, end_timestamp)) \
.order_by(OrderDeal.id.asc()).all()
dr = {}
t = 0
p = 0
v = 0
for id, dt_m, price, volume in results:
if dt_m not in dr.keys():
dr[dt_m] = {}
t = 0
p = 0
v = 0
dr[dt_m]['price'] = price
t = t + 1
p = p + price
v = v + volume
dr[dt_m]['times'] = t
dr[dt_m]['avg_price'] = round(p / t, 2)
dr[dt_m]['volume'] = v
ret = []
prev_price = Common.get_last_price(session, p_no)
prev_avg_price = prev_price
x_times = ProductHandler.get_transaction_times(p_no)
now = datetime.datetime.now().strftime('%H:%M')
for t in x_times:
if t not in dr.keys():
if operator.gt(t, now):
break
ret.append(tuple((t, prev_price, prev_avg_price, 0)))
else:
ret.append(
tuple((t, dr[t]['price'], dr[t]['avg_price'],
dr[t]['volume'])))
prev_price = dr[t]['price']
prev_avg_price = dr[t]['avg_price']
data = ret
key = "{}{}".format('REDIS_KEY_FOR_CHART_TIME_DATA_', p_no)
redis.set(key, json.JSONEncoder().encode(data))
session.close()