def put(self, _id):
        """
        修改投资组合
        """
        # data_dic = request.get_json() or request.form
        # logger.debug("data_dic(get_json): %s", data_dic)
        # data_dic = request.args
        # logger.debug("data_dic(args): %s", data_dic)
        data_dic = json.loads(str(request.data, encoding='utf-8'))
        logger.debug("data_dic(data): %s", data_dic)
        # data_dic = request.values
        # logger.debug("data_dic(values): %s", data_dic)

        # TODO: 增加权限检查,只能修改自己创建的投资组合
        # TODO: 交易日期必须大于等于当日,如果下午3点以后不得等于当日
        if data_dic is None:
            raise BadRequest('缺少json请求数据')
            # return jsonify({"status": "error", "message": "no json"})
        if "data" not in data_dic:
            # return jsonify({"status": "error", "message": "'data' key in json"})
            logger.error('请求数据:%s', data_dic)
            raise BadRequest('json 请求数据缺少 data 数据')
        # data = json_dic["data"]
        add_pl_data(_id, data_dic)

        return {"status": "ok", 'id': _id}
    def post(self):
        """
        创建预测信息
        """
        # logger.debug("request.args: %s", request.args)
        # logger.debug("request.values: %s", request.values)
        # logger.debug("request.json: %s", request.json)
        # logger.debug("request.data: %s", request.data)
        data_dic = request.get_json() or request.form
        logger.debug("data_dic: %s", data_dic)

        # 添加投资组合信息
        data_obj = PortfolioCompareInfo()
        populate_obj(
            data_obj,
            data_dic,
            attr_list=["name", "date_from", "date_to", "access_type", "desc"],
            error_if_no_key=True)
        # logger.debug("data_dic['params']<%s>: %s", type(data_dic['params']), data_dic['params'])
        data_obj.params = json.dumps(data_dic['params'])
        # TODO: 需要进行:1)参数合法性检查 2)pl_id user_id create_dt 等参数不得传入,类似无效参数过滤
        user_id = session.get('user_id')
        data_obj.create_user_id = user_id
        db.session.add(data_obj)
        db.session.commit()
        logger.info('%s:id=%d 成功插入数据库 %s', data_obj.__class__.__name__,
                    data_obj.cmp_id, data_obj.__tablename__)

        return {"status": "ok", 'id': data_obj.cmp_id}
    def post(self):
        """
        创建投资组合
        """
        # logger.debug("request.json %s", request.json)
        # json_dic = request.json
        # if json_dic is None:
        #     return {"status": "error", "message": "no json"}
        # if "data" not in json_dic:
        #     return {"status": "error", "message": "'data' key in json"}
        # data_dic = json_dic["data"]
        data_dic = request.get_json() or request.form
        logger.debug("data_dic: %s", data_dic)

        # 添加投资组合信息
        data_obj = PortfolioInfo()
        populate_obj(data_obj,
                     data_dic,
                     attr_list=["name", "access_type", "desc"],
                     error_if_no_key=True)
        # TODO: 需要进行:1)参数合法性检查 2)pl_id user_id create_dt 等参数不得传入,类似无效参数过滤
        user_id = session.get('user_id')
        data_obj.create_user_id = user_id
        db.session.add(data_obj)
        db.session.commit()
        logger.info('%s:id=%d 成功插入数据库 %s', data_obj.__class__.__name__,
                    data_obj.pl_id, data_obj.__tablename__)

        # 添加投资组合
        if 'pl_data' in data_dic:
            pl_data_dic = data_dic['pl_data']
            add_pl_data(data_obj.pl_id, pl_data_dic)

        return {"status": "ok", 'id': data_obj.pl_id}
 def delete(self, _id):
     """
     删除指定预测
     """
     logger.debug('cmp_id=%d', _id)
     PortfolioCompareInfo.query.filter(
         PortfolioCompareInfo.cmp_id == _id).update({'is_del': 1})
     db.session.commit()
     return {'status': 'ok', 'id': _id}
 def get(self, _id):
     """
     预测结果数据(供 charts 使用,无需分页返回)
     """
     sql_str = """SELECT DATE_FORMAT(trade_date, "%%Y-%%m-%%d") trade_date,
     asset_1, asset_2, asset_3, result, shift_value, shift_rate FROM pl_compare_result
     WHERE cmp_id = %s"""
     data_df = pd.read_sql(sql_str, db.engine, params=[_id])
     ret_df = data_df.where(data_df.notna(), None)
     ret_dic_list = ret_df.to_dict('list')
     logger.debug('%d 条数据', ret_df.shape[0])
     ret_dic = {'data': ret_dic_list, 'count': len(ret_dic_list)}
     return ret_dic
 def get(self, _id, status, method):
     """
     获取制定投资组合的成分及权重数据(分页)
     """
     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 method == 'record':
         ret_dic = PortfolioListResource.get_pl_data_list(
             _id, status, page_no, count)
     elif method == 'date':
         ret_dic = PortfolioListResource.get_pl_data_list_by_date(
             _id, status, page_no, count)
     else:
         raise KeyError('status 参数错误 method = %s' % method)
     return ret_dic
def add_pl_data(_id, pl_data_dic: dict):
    """
    更新或插入投资组合
    :param _id:
    :param pl_data_dic:
    :return:
    """
    date_str = pl_data_dic['trade_date']
    price_type = pl_data_dic['price_type']
    pl_data_dic_bulk = pl_data_dic['data']

    PortfolioData.query.filter(PortfolioData.pl_id == _id,
                               PortfolioData.trade_date == date_str).delete()
    logger.debug("pl_id=%d: 剔除 %s 历史数据", _id, date_str)
    # 获取上一个调仓日时的持仓纪录
    pl_data_obj_list_last_date = PortfolioData.query.filter(
        PortfolioData.pl_id == _id,
        PortfolioData.trade_date == (db.session.query(
            func.max(PortfolioData.trade_date)).filter(
                PortfolioData.pl_id == _id))).all()
    pl_data_obj_dic_last_date = {(data.asset_type, data.asset_code): data
                                 for data in pl_data_obj_list_last_date}

    # 建立持仓数据
    pl_data_obj_list = []
    for pl_d_dic in pl_data_dic_bulk:
        pl_d_obj = PortfolioData()
        pl_d_obj.pl_id = _id
        populate_obj(pl_d_obj, pl_d_dic,
                     ["asset_code", "asset_type", "weight", "direction"])
        key = (pl_d_dic["asset_type"], pl_d_dic["asset_code"])
        pl_d_obj.weight_before = pl_data_obj_dic_last_date[
            key].weight if key in pl_data_obj_dic_last_date else 0
        pl_d_obj.trade_date = date_str
        pl_d_obj.price_type = price_type
        pl_data_obj_list.append(pl_d_obj)

    # 批量插入
    db.session.bulk_save_objects(pl_data_obj_list)
    db.session.commit()
    logger.debug("pl_id=%d: %d 投资组合数据插入到 pl_data 表", _id,
                 len(pl_data_obj_list))
    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_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