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 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 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 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 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() 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 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 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 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 get_k_data(p_no): session = Session() data = Common.get_k_data(session, p_no) session.close() return data
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_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 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_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_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 insert_sale_order(user_id, p_no, volume, price): if not ProductHandler.is_in_exchange(p_no): return False, '非交易时间不允许委托' ups = ProductHandler.get_up_stop_price(p_no) dps = ProductHandler.get_down_stop_price(p_no) if operator.lt(float(price), dps) or operator.gt(float(price), ups): return False, '委托价格有误' #assets = UserHandler.get_can_sold_assets(user_id, p_no) assets = UserHandler.get_can_sale_asset_volume(user_id, p_no) if operator.gt(int(volume), assets) or operator.le(int(volume), 0): return False, '委托数量有误' order = Order() order.user_id = user_id order.p_no = p_no order.direction = Order.OrderDirectionSale order.volume = volume order.price = price order.status = Order.OrderStatusCommitted order.created_at = time.time() session = Session() session.add(order) try: session.commit() except: session.rollback() session.close() return False, '系统出错' else: session.close() return True, 'success'
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_deal_data(p_no=None): if not p_no: raise ValueError 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 #print(dr) ret = [] prev_price = Common.get_last_price( session, p_no) #ProductHandler.get_last_price(p_no) prev_avg_price = prev_price x_times = ProductHandler.get_transaction_times(p_no) now = 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'] #print(ret) session.close() return ret
def get_assets(user_id): session = Session() products = session.query(Product).filter(Product.deleted==0,or_(Product.status==Product.ProductStatusCreated, Product.status==Product.ProductStatusActive,Product.status==Product.ProductStatusStopped)).all() ret = [] for p in products: gift_assets = session.query(UserAssets.p_no, func.sum(UserAssets.qty), func.sum(UserAssets.qty*UserAssets.cost_price))\ .filter(UserAssets.deleted==0, UserAssets.user_id==user_id,UserAssets.p_no==p.p_no,UserAssets.status==UserAssets.StatusActive)\ .group_by(UserAssets.user_id,UserAssets.p_no).one_or_none() buy_assets = session.query(OrderDeal.p_no,func.sum(OrderDeal.volume), func.sum(OrderDeal.price*OrderDeal.volume))\ .filter(OrderDeal.buser_id==user_id,OrderDeal.p_no==p.p_no,OrderDeal.deleted==0)\ .group_by(OrderDeal.buser_id,OrderDeal.p_no).one_or_none() sub_assets = session.query(OrderSub.p_no,func.sum(OrderSub.qty), func.sum(OrderSub.amount))\ .filter(OrderSub.p_no==p.p_no,OrderSub.deleted==0,OrderSub.status==OrderSub.StatusPaid,OrderSub.user_id==user_id)\ .group_by(OrderSub.user_id,OrderSub.p_no).one_or_none() sale_assets = session.query(OrderDeal.p_no,func.sum(OrderDeal.volume), func.sum(OrderDeal.price*OrderDeal.volume))\ .filter(OrderDeal.suser_id==user_id,OrderDeal.p_no==p.p_no,OrderDeal.deleted==0)\ .group_by(OrderDeal.suser_id,OrderDeal.p_no).one_or_none() print('asset:{},{},{},{}'.format(gift_assets,buy_assets, sub_assets, sale_assets)) left_volume = 0 left_cost = 0 if not buy_assets: if not sub_assets: if not gift_assets: continue else: left_volume += float(gift_assets[1]) left_cost += float(gift_assets[2]) else: left_volume += float(sub_assets[1]) left_cost += float(sub_assets[2]) if gift_assets: left_volume += float(gift_assets[1]) left_cost += float(gift_assets[2]) else: left_volume += float(buy_assets[1]) left_cost += float(buy_assets[2]) if sub_assets: left_volume += float(sub_assets[1]) left_cost += float(sub_assets[2]) if gift_assets: left_volume += float(gift_assets[1]) left_cost += float(gift_assets[2]) print('buy left:{},{},{}'.format(p.p_no,left_volume,left_cost)) if sale_assets: left_volume = int(left_volume - float(sale_assets[1])) left_cost = float(left_cost - float(sale_assets[2])) if left_volume > 0: market_value = left_volume * float(ProductHandler.get_current_price(p.p_no)) if operator.eq(left_cost,0): gains = 100 else: gains = (float(market_value) - float(left_cost))/abs(left_cost)*100 win_value = float(market_value) - float(left_cost) ret.append(tuple((p.p_no,p.name,int(left_volume),round(float(left_cost),2),round(market_value, 2) ,round(gains,2), round(win_value,2)))) session.close() return ret
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()
#UserWithdraw.install(engine) p = Product() p.p_no = 'CZQH' p.name = '启航' p.pub_id = 1 p.issue_price = 5.00 p.unit = 'h' p.qty = 10000000 p.turn_qty = 8000000 p.status = Product.ProductStatusCreated p.ex_from = '2017-12-01' p.ex_end = '2018-12-31' session = Session() session.add(p) session.commit() session.close() ''' UserMeta.install(engine) Publisher.install(engine) Catalog.install(engine) Product.install(engine) Order.install(engine) OrderDeal.install(engine) UserMoney.install(engine) UserAssets.install(engine) ''' ##raw_data()