def get_products(self):
session = Session()
products = session.query(P).filter(P.status == P.ProductStatusActive,
P.deleted == 0).all()
return products
def process(args, redis):
session = Session()
order = Order()
order.user_id = args['USER_ID']
order.p_no = args['P_NO']
order.direction = Order.OrderDirectionBuy
order.volume = args['VOLUME']
order.price = args['PRICE']
order.status = Order.OrderStatusCommitted
order.created_at = time.time()
session.add(order)
#TODO need to check user's total money
max_volume = args['MAX_VOLUME']
money = args['MONEY']
try:
session.commit()
except DBAPIError as e:
session.rollback()
session.close()
return 'failed'
else:
redis.publish_json('chan:matching', args)
redis.publish('chan:updates', 'CMD_GET_DELEGATE_BUY:{}'.format(args['P_NO']))
return {'ID':order.id,'USER_ID':order.user_id,'P_NO':order.p_no,'DIRECTION':order.direction,'VOLUME':order.volume,'PRICE':order.price,'STATUS':order.status}
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 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 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 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 get_product_name(p_no):
session = Session()
name = session.scalar('select name from wx_product where p_no=:p_no',
{'p_no': p_no})
return name
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 set_top_5_delegate_buy_orders_to_redis(redis, p_no=None):
session = Session()
data = Common.get_top_5_delegate_buy_orders(session, p_no)
key = "{}{}".format('REDIS_KEY_FOR_TOP_5_BUY_DATA_', p_no)
print(data, key)
redis.set(key, json.JSONEncoder().encode(data))
session.close()
def get_k_data(p_no):
session = Session()
data = Common.get_k_data(session, p_no)
session.close()
return data
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 get_product_qty(p_no):
session = Session()
qty = session.scalar(
'select qty from wx_product where p_no=:p_no limit 1',
{'p_no': p_no})
return qty
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 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 is_active(p_no):
session = Session()
status = session.scalar(
'select status from wx_product where deleted=0 and p_no=:p_no limit 1',
{'p_no': p_no})
if operator.eq(status, P.ProductStatusActive):
return True
else:
return False
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 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 is_first_exchange_day(p_no):
session = Session()
num = session.scalar(
'select count(*) from wx_product_history where deleted=0 and p_no=:p_no',
{'p_no': p_no})
if operator.le(num, 1):
return True
return False
def save_to_mysql_by_sql_alchemy(self, prod, status):
#print('open sqlalchrmy session...')
db_session = Session()
try:
tm = Timer()
tm.start()
db_session.add(
DarenGoodInfo(createTime=prod['createTime'],
darenId=prod['userId'],
darenNoteId=prod['darenNoteId'],
darenNoteUrl=prod['darenNoteUrl'],
darenNoteTitle=prod['darenNoteTitle'],
darenNoteReason=prod['darenNoteReason'],
darenNoteCover=prod['darenNoteCover'],
darenNotePubDate=prod['darenNotePubDate'],
goodId=prod['goodId'],
goodUrl=prod['goodUrl'],
goodNoteDetailStep=3))
db_session.commit()
self.insert_cot += 1
tm.end()
print('{}\tSave {} to mysql: OK. spent {} / {} s'\
.format(status,prod['darenNoteId'],
round(tm.gap,2),round(self.db_time/self.thread_cot,2),
))
self.db_time += tm.gap
except Exception as e:
print('{}\tSave to mysql ERROR: {}'\
.format(status,str(e)))
del tm
db_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 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_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_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_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 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_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