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