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()