from py_jftech import read, where, write, format_date, mapper_columns from api import PortfoliosRisk __COLUMNS__ = { 'rhp_id': 'id', 'rhp_date': 'date', 'rhp_risk': 'risk', 'rhp_div': 'dividend', 'rhp_div_acc': 'div_acc', 'rhp_rrs_id': 'signal_id', 'rhp_rebalance': 'rebalance', 'rhp_portfolios': 'portfolios', 'rhp_nav': 'nav', 'rhp_fund_av': 'fund_av', 'rhp_fund_div': 'fund_div', 'rhp_div_forecast': 'div_forecast', 'rhp_asset_nav': 'asset_nav', 'rhp_port_div': 'port_div', 'v_nav_div_acc': 'acc_av', } @read def get_list(risk: PortfoliosRisk = None, min_date=None, max_date=None, rebalance: bool = None): sqls = [] if min_date: sqls.append(f"rhp_date >= '{format_date(min_date)}'") if max_date: sqls.append(f"rhp_date <= '{format_date(max_date)}'") return f''' select {','.join([f'{x[0]} as {x[1]}' for x in __COLUMNS__.items()])} from robo_hold_portfolios {where(*sqls, rhp_risk=risk, rhp_rebalance=rebalance)} order by rhp_risk, rhp_date ''' @read(one=True) def get_one(day, risk: PortfoliosRisk): return f'''select {','.join([f'{x[0]} as {x[1]}' for x in __COLUMNS__.items()])} from robo_hold_portfolios {where(rhp_date=day, rhp_risk=risk)}''' @read(one=True) def get_last_one(risk: PortfoliosRisk = None, max_date=None, rebalance: bool = None, signal_id=None): sql = f"rhp_date <= '{format_date(max_date)}'" if max_date else None return f''' select {','.join([f'{x[0]} as {x[1]}' for x in __COLUMNS__.items()])} from robo_hold_portfolios {where(sql, rhp_risk=risk, rhp_rrs_id=signal_id, rhp_rebalance=rebalance)} order by rhp_date desc limit 1 ''' def get_count(risk: PortfoliosRisk = None): @read(one=True) def exec(): return f'''select count(*) as `count` from robo_hold_portfolios {where(rhp_risk=risk)}''' result = exec() return result['count'] @write def insert(datas): datas = mapper_columns(datas=datas, columns=__COLUMNS__) return f''' insert into robo_hold_portfolios({','.join([x for x in datas.keys()])}) values ({','.join([f"'{x[1]}'" for x in datas.items()])}) ''' @write def delete(min_date=None, risk: PortfoliosRisk = None): if min_date is None and risk is None: return 'truncate table robo_hold_portfolios' else: sql = f"rhp_date >= '{format_date(min_date)}'" if min_date else None return f"delete from robo_hold_portfolios {where(sql, rhp_risk=risk)}"