def get(self): """ 获取投资组合汇总信息:全部组合,我的组合,关注组合 数量及对应 status """ user_id = session.get('user_id') count_all, count_my = db.session.query( func.count().label('all'), func.sum(func.if_(PortfolioInfo.create_user_id == user_id, 1, 0))).filter(PortfolioInfo.is_del == 0).first() favorite = db.session.query(func.count()).filter( FavoritePortfolio.user_id == user_id).scalar() ret_data = { 'data': [{ "name": "全部", "status": "all", "count": try_2_float(count_all) }, { "name": "我的", "status": "my", "count": try_2_float(count_my) }, { "name": "关注组合", "status": "favorite", "count": try_2_float(favorite) }] } return ret_data
def get(self): """ 获取预测比较汇总信息:待验证,已验证,关注预言 数量及对应 status """ user_id = session.get('user_id') sql_str = """SELECT sum(if(trade_date_max IS NULL, 0, if(trade_date_max<cmp_info.date_to, 1, 0))) unverified, sum(if(trade_date_max IS NULL, 0, if(trade_date_max>=cmp_info.date_to, 1, 0))) verified FROM pl_compare_info cmp_info LEFT JOIN ( SELECT cmp_id, max(trade_date) trade_date_max FROM pl_compare_result GROUP BY cmp_id ) cmp_rslt ON cmp_info.cmp_id = cmp_rslt.cmp_id WHERE is_del=0""" raw = db.engine.execute(sql_str).first() count_unverified, count_verified = raw favorite = db.session.query( func.count()).filter(FavoriteCompare.user_id == user_id).scalar() ret_data = { 'data': [{ "name": "待验证", "status": "unverified", "count": try_2_float(count_unverified) }, { "name": "已验证", "status": "verified", "count": try_2_float(count_verified) }, { "name": "关注预言", "status": "favorite", "count": try_2_float(favorite) }] } return ret_data
def get(self, status): """ 获取投资组合列表数据 """ args = paginate_parser.parse_args() page_no = args['page_no'] count = args['count'] user_id = session.get('user_id') # 获取各个组合 最新交易日 date_latest_query = db.session.query( PortfolioValueDaily.pl_id, func.max(PortfolioValueDaily.trade_date).label( 'trade_date_max')).group_by( PortfolioValueDaily.pl_id).subquery('date_latest') # 获取各个投资组合 最新净值 nav_latest_query = db.session.query( PortfolioValueDaily.pl_id, PortfolioValueDaily.trade_date, PortfolioValueDaily.rr, PortfolioValueDaily.nav).filter( PortfolioValueDaily.pl_id == date_latest_query.c.pl_id, PortfolioValueDaily.trade_date == date_latest_query.c.trade_date_max).subquery('nav_latest') # 分页查询投资组合信息及最新净值 if status == 'my': filter_c = PortfolioInfo.create_user_id == user_id elif status == 'all': filter_c = or_(PortfolioInfo.access_type == 'public', PortfolioInfo.create_user_id == user_id) elif status == 'star': # TODO: 星标投资组合 filter_c = not_(func.isnull(FavoriteCompare.update_time)) else: filter_c = None if filter_c is None: raise KeyError('status 参数错误 status = %s' % status) else: pagination = PortfolioInfo.query.outerjoin( nav_latest_query, PortfolioInfo.pl_id == nav_latest_query.c.pl_id).add_columns( nav_latest_query.c.trade_date, nav_latest_query.c.rr, nav_latest_query.c.nav, ).outerjoin(User).add_columns(User.username).outerjoin( FavoritePortfolio, and_(PortfolioInfo.pl_id == FavoritePortfolio.pl_id, FavoritePortfolio.user_id == user_id)).add_columns( func.if_( func.isnull(FavoritePortfolio.update_time), 0, 1).label('favorite')).filter( filter_c).paginate(page_no, count) logger.debug('%d / %d 页 %d / %d 条数据', pagination.page, pagination.pages, len(pagination.items), pagination.total) ret_dic_list = [{ 'pl_id': data.PortfolioInfo.pl_id, 'name': data.PortfolioInfo.name, 'date_from': date_2_str(data.PortfolioInfo.date_from), 'date_to': date_2_str(data.PortfolioInfo.date_to), 'status': data.PortfolioInfo.status, 'desc': data.PortfolioInfo.desc, 'create_user_id': data.PortfolioInfo.create_user_id, 'username': data.username, 'favorite': data.favorite, 'trade_date': date_2_str(data.trade_date), 'rr': try_2_float(data.rr), 'nav': try_2_float(data.nav), 'access_type': data.PortfolioInfo.access_type, } for data in pagination.items] ret_dic = { 'page': pagination.page, 'pages': pagination.pages, 'count': len(pagination.items), 'total': pagination.total, 'has_prev': pagination.has_prev, 'has_next': pagination.has_next, 'data': ret_dic_list, } return ret_dic
def get(self, _id, status): """ 投资组合资产分布比例 """ logger.info('pl_id=%s, status=%s', _id, status) if status == 'latest': result_list = db.session.query( PortfolioData.trade_date, PortfolioData.asset_type, func.sum(PortfolioData.weight).label('weight')).group_by( PortfolioData.trade_date, PortfolioData.asset_type).filter( and_( PortfolioData.trade_date == (db.session.query( func.max(PortfolioData.trade_date)).filter( PortfolioData.pl_id == _id)), PortfolioData.pl_id == _id)).all() elif status == 'recent': # count = request.args.get('count', 5, type=int) args = paginate_parser.parse_args() count = args['count'] data_list = [ date_2_str(d[0]) for d in db.session.query(PortfolioData.trade_date).filter( PortfolioData.pl_id == _id).group_by( PortfolioData.trade_date).limit(count).all() ] result_list = db.session.query( PortfolioData.trade_date, PortfolioData.asset_type, func.sum(PortfolioData.weight).label('weight')).group_by( PortfolioData.trade_date, PortfolioData.asset_type).filter( and_(PortfolioData.trade_date.in_(data_list), PortfolioData.pl_id == _id)).all() elif status == 'all': result_list = db.session.query( PortfolioData.trade_date, PortfolioData.asset_type, func.sum(PortfolioData.weight).label('weight')).group_by( PortfolioData.trade_date, PortfolioData.asset_type).all() else: raise KeyError('status 参数错误 status = %s' % status) # 合并数据结果 ret_dic_list = [] ret_dic_dic = {} for data in result_list: trade_date = data.trade_date if trade_date in ret_dic_dic: pl_list, asset_name_list = ret_dic_dic[trade_date] else: pl_list = [] asset_name_list = [] ret_dic_dic[trade_date] = (pl_list, asset_name_list) ret_dic_list.append({ 'trade_date': date_2_str(trade_date), 'data': pl_list, 'name_list': asset_name_list }) # 扩展 pl_list.append({ 'name': data.asset_type, 'value': try_2_float(data.weight), }) asset_name_list.append(data.asset_type) ret_dic = { 'count': len(ret_dic_list), 'data': ret_dic_list, } # logger.debug("ret_dic:%s", ret_dic) return ret_dic
def get_pl_data_list_by_date(_id, status, page_no, count): """ 获取制定投资组合的成分及权重数据(分页) status :param _id: :param status: latest 最近一次调仓数据, recent 最近几日调仓数据,日期逆序排列 :return: """ # logger.info('pl_id=%s, status=%s', _id, status) # PortfolioData.query.filter(PortfolioData.id == pl_id, PortfolioData.trade_date == 1) if status == 'latest': date_cur = db.session.query(func.max( PortfolioData.trade_date)).filter( PortfolioData.pl_id == _id).scalar() # 最新调仓日期 pagination = PortfolioData.query.filter( PortfolioData.pl_id == _id, PortfolioData.trade_date == date_cur).paginate(page_no, count) elif status == 'recent': pagination = PortfolioData.query.group_by( PortfolioData.trade_date).filter( PortfolioData.pl_id == _id).order_by( PortfolioData.trade_date.desc()).paginate( page_no, count) else: raise KeyError('status = %s 不支持' % status) logger.debug('%d / %d 页 %d / %d 条数据', pagination.page, pagination.pages, len(pagination.items), pagination.total) date_list = [data.trade_date for data in pagination.items] date_grouped_dic = {} ret_dic_list = [] if len(date_list) > 0: # date_grouped_dic 中的 value 为 data_list 实际与 ret_dic_list 中对应日期的 data_list 为同一对象 # 因此可以直接修改 items = db.session.query(PortfolioData).filter( PortfolioData.pl_id == _id, PortfolioData.trade_date.in_(date_list)).all() for data in items: date_cur = date_2_str(data.trade_date) if date_cur in date_grouped_dic: data_list = date_grouped_dic[date_cur] else: data_list = [] date_grouped_dic[date_cur] = data_list ret_dic_list.append({ 'trade_date': date_cur, 'data': data_list }) # 获取资产及资产类别中文名称 asset_name = get_asset_name(data.asset_type, data.asset_code) # 因为list是引用性数据,直接放入 ret_dic_list # 对 data_list 的修改直接反应到 ret_dic_list 中去 data_list.append({ 'id': data.id, 'asset_code': data.asset_code, 'asset_name': asset_name, 'asset_type': data.asset_type, 'trade_date': date_cur, 'weight': try_2_float(data.weight), 'weight_before': try_2_float(data.weight_before), 'price_type': data.price_type, 'direction': data.direction, }) ret_dic = { 'page': pagination.page, 'pages': pagination.pages, 'count': len(pagination.items), 'total': pagination.total, 'has_prev': pagination.has_prev, 'has_next': pagination.has_next, 'data': ret_dic_list, } # logger.debug(ret_dic) return ret_dic
def get_pl_data_list(_id, status, page_no, count): """ 获取制定投资组合的成分及权重数据(分页) :param _id: :param status: :param page_no: :param count: :return: """ if status == 'latest': pagination = PortfolioData.query.filter( PortfolioData.pl_id == _id, PortfolioData.trade_date == (db.session.query( func.max(PortfolioData.trade_date)).filter( PortfolioData.pl_id == _id))).paginate(page_no, count) elif status == 'recent': pagination = PortfolioData.query.filter( PortfolioData.pl_id == _id).order_by( PortfolioData.trade_date.desc()).paginate(page_no, count) else: raise KeyError('status = %s 不支持' % status) logger.debug('%d / %d 页 %d / %d 条数据', pagination.page, pagination.pages, len(pagination.items), pagination.total) date_grouped_dic = {} ret_dic_list = [] for data in pagination.items: date_cur = date_2_str(data.trade_date) if date_cur in date_grouped_dic: data_list = date_grouped_dic[date_cur] else: data_list = [] date_grouped_dic[date_cur] = data_list ret_dic_list.append({ 'trade_date': date_cur, 'data': data_list }) # 获取资产及资产类别中文名称 asset_name = get_asset_name(data.asset_type, data.asset_code) # 因为list是引用性数据,直接放入 ret_dic_list # 对 data_list 的修改直接反应到 ret_dic_list 中去 data_list.append({ 'id': data.id, 'asset_code': data.asset_code, 'asset_name': asset_name, 'asset_type': data.asset_type, 'trade_date': date_cur, 'weight': try_2_float(data.weight), 'weight_before': try_2_float(data.weight_before), 'price_type': data.price_type, 'direction': data.direction, }) ret_dic = { 'page': pagination.page, 'pages': pagination.pages, 'count': len(pagination.items), 'total': pagination.total, 'has_prev': pagination.has_prev, 'has_next': pagination.has_next, 'data': ret_dic_list, } # logger.debug(ret_dic) return ret_dic
def get(self, status): """ 获取比较列表数据(分页) """ args = paginate_parser.parse_args() page_no = args['page_no'] count = args['count'] user_id = session.get('user_id') logger.debug('get_cmp_list user_id:%s', user_id) if status == 'my': filter_c = PortfolioCompareInfo.create_user_id == user_id having_c = None elif status == 'all': filter_c = or_(PortfolioCompareInfo.create_user_id == user_id, PortfolioCompareInfo.access_type == 'public') having_c = None elif status == 'star': filter_c = and_( or_(PortfolioCompareInfo.create_user_id == user_id, PortfolioCompareInfo.access_type == 'public'), not_(func.isnull(FavoriteCompare.update_time))) having_c = None elif status == 'verified': filter_c = or_(PortfolioCompareInfo.create_user_id == user_id, PortfolioCompareInfo.access_type == 'public') having_c = column('complete_rate') >= 1 elif status == 'unverified': filter_c = or_(PortfolioCompareInfo.create_user_id == user_id, PortfolioCompareInfo.access_type == 'public') having_c = or_( column('complete_rate').is_(None), column('complete_rate') < 1) else: raise KeyError('status 参数错误 status = %s' % status) # 整理数据 # logger.debug("data_list_df len:%d", data_list_df.shape[0]) # data_list_df = data_list_df.where(data_list_df.notna(), None) # data_list = data_list_df.to_dict('record') # data_table_dic = {'data': data_list} # logger.debug(data_table_dic) query = PortfolioCompareInfo.query.outerjoin( PortfolioCompareResult ).group_by(PortfolioCompareResult.cmp_id).add_columns( func.count().label('tot_count'), func.min( PortfolioCompareResult.trade_date).label('trade_date_min'), func.max( PortfolioCompareResult.trade_date).label('trade_date_max'), func.sum(PortfolioCompareResult.result).label('fit_count'), (func.sum(PortfolioCompareResult.result) / func.count()).label('fit_rate'), ((func.max(PortfolioCompareResult.trade_date) - PortfolioCompareInfo.date_from) / (PortfolioCompareInfo.date_to - PortfolioCompareInfo.date_from) ).label('complete_rate')).outerjoin(User).add_columns( User.username).outerjoin( FavoriteCompare, and_( PortfolioCompareInfo.cmp_id == FavoriteCompare.cmp_id, FavoriteCompare.user_id == user_id)).add_columns( func.if_( func.isnull(FavoriteCompare.update_time), 0, 1).label('favorite')).filter( and_(filter_c, PortfolioCompareInfo.is_del == 0)) if having_c is None: pagination = query.paginate(page_no, count) else: pagination = query.having(having_c).paginate(page_no, count) logger.debug('%d / %d 页 %d / %d 条数据', pagination.page, pagination.pages, len(pagination.items), pagination.total) ret_dic_list = [{ 'cmp_id': data.PortfolioCompareInfo.cmp_id, 'name': data.PortfolioCompareInfo.name, 'status': data.PortfolioCompareInfo.status, 'params': data.PortfolioCompareInfo.params, 'desc': data.PortfolioCompareInfo.desc, 'date_from': date_2_str(data.PortfolioCompareInfo.date_from), 'date_to': date_2_str(data.PortfolioCompareInfo.date_to), 'trade_date_min': date_2_str(data.trade_date_min), 'trade_date_max': date_2_str(data.trade_date_max), 'create_user_id': data.PortfolioCompareInfo.create_user_id, 'username': data.username, 'favorite': data.favorite, 'complete_rate': try_2_float(data.complete_rate), } for data in pagination.items] ret_dic = { 'page': pagination.page, 'pages': pagination.pages, 'count': len(pagination.items), 'total': pagination.total, 'has_prev': pagination.has_prev, 'has_next': pagination.has_next, 'data': ret_dic_list, } return ret_dic