DolphinDB/modules/task_fundPerformance.dos

module fundit::task_fundPerformance

use fundit::dataPuller;
use fundit::dataSaver;
use fundit::returnCalculator;
use fundit::indicatorCalculator;
use fundit::bfiMatcher;
use fundit::rankingCalculator;

/*
 *   [定时任务]：最新净值触发的业绩指标计算
 * 
 *   @param entityType <STRING>: 'MF', 'HF'...
 *   @param date <DATETIME>: 净值更新时间, 为空时缺省为当前时间-1天；为1900.01.01或更早日期时代表初始化，指标会被存入本地数据库
 *   
 *   NOTE: 与Java不同的是当月indicator计算每日触发，不必等到Month-end production
 *   
 *   Example: calFundPerformanceTask('MF', 2024.10.28);
 *            calFundPerformanceTask('MI', 2024.10.28);
 */
def calFundPerformanceTask(entityType, mutable date) {

    rt = '';

    if(!(entityType IN ['MF', 'HF', 'MI', 'FI'])) return null;

    if(date.isNothing() || date.isNull()) date = temporalAdd(now(), -1d);

    // 取有最新净值变动的基金列表 (1s)
    tb_cal_funds = get_entity_list_by_nav_updatetime(entityType, NULL, date, true);

    if(tb_cal_funds.isVoid() || tb_cal_funds.size() == 0 ) return;

    // 按照 MySQL 建好各表
    tb_fund_performance = create_entity_performance();
    tb_fund_indicator = create_entity_indicator();
    tb_fund_risk_stats = create_entity_risk_stats();
    tb_fund_riskadjret_stats = create_entity_riskadjret_stats();
    tb_fund_style_stats = create_entity_style_stats();

    tb_fund_performance_weekly = create_entity_performance_weekly();
    tb_fund_latest_performance = create_entity_latest_performance();

    // 分批跑
    i = 0;
    batch_size = 1000;


    do {

        funds = tb_cal_funds[i : min(tb_cal_funds.size(), i+batch_size)];

        if(funds.isVoid() || funds.size() == 0) break;

        // 200ms
        fund_info = SELECT entity_id, price_date, inception_date, benchmark_id, ini_value 
                    FROM ej(funds, get_entity_info(entityType, funds.entity_id), 'entity_id');

        // 计算月收益 (12s)
        rets = mix_monthly_returns(entityType, fund_info);

        if(!rets.isVoid() && rets.size() > 0) {

            // 计算月度指标 (56s)
            rets.rename!('cumulative_nav', 'nav');
            indicators = cal_monthly_indicators(entityType, 'PBI', rets);

            // 仿照MySQL的表结构准备好记录 （1s)
            generate_entity_performance(fund_info, indicators, true, tb_fund_performance);
            generate_entity_indicator(fund_info, indicators, true, tb_fund_indicator);
            generate_entity_risk_stats(fund_info, indicators, true, tb_fund_risk_stats);
            generate_entity_riskadjret_stats(fund_info, indicators, true, tb_fund_riskadjret_stats);
            generate_entity_style_stats(fund_info, indicators, true, tb_fund_style_stats);

        }
        
        // 计算周收益 (8s)
        rets_w = cal_weekly_returns(entityType, fund_info);

        if(! rets_w.isVoid() && rets_w.size() > 0) {
            generate_entity_performance_weekly(fund_info, rets_w, true, tb_fund_performance_weekly);
        }

        // 计算最新收益 (69s)
        perf_latest = cal_latest_performance(entityType, fund_info, true);

        if(! perf_latest.isVoid() && perf_latest.size() > 0) {
            generate_entity_latest_performance(fund_info, perf_latest, true, tb_fund_latest_performance);
        }

        i += batch_size;

//    } while (i < batch_size);
    } while (i <= tb_cal_funds.size());


    if(! tb_fund_performance.isVoid() && tb_fund_performance.size() > 0) {

        // save data to MySQL  (13s)
        try {

            chg_columns_for_mysql(tb_fund_performance, 'fund_id');
            save_and_sync(tb_fund_performance, 'raw_db.fund_performance', 'raw_db.fund_performance');

            chg_columns_for_mysql(tb_fund_indicator, 'fund_id');
            save_and_sync(tb_fund_indicator, 'raw_db.fund_indicator', 'raw_db.fund_indicator');

            chg_columns_for_mysql(tb_fund_risk_stats, 'fund_id');
            // mfdb.fund_performance 表中 maxdrawdown_6m & maxdrawdown_ytd 是虚拟列，这里用数据列顺序强行写入真实列 6m_maxdrawdown & ytd_maxdrawdown (DolphinDB 不允许字段名以数字开头)
            save_and_sync(tb_fund_risk_stats, 'raw_db.fund_risk_stats', 'raw_db.fund_risk_stats');

            chg_columns_for_mysql(tb_fund_riskadjret_stats, 'fund_id');
            save_and_sync(tb_fund_riskadjret_stats, 'raw_db.fund_riskadjret_stats', 'raw_db.fund_riskadjret_stats');

            chg_columns_for_mysql(tb_fund_style_stats, 'fund_id');
            save_and_sync(tb_fund_style_stats, 'raw_db.fund_style_stats', 'raw_db.fund_style_stats');

            save_and_sync(tb_fund_performance_weekly, 'raw_db.fund_performance_weekly', 'raw_db.fund_performance_weekly');

            save_and_sync(tb_fund_latest_performance, 'raw_db.fund_latest_performance', 'raw_db.fund_latest_nav_performance');

            // 数据初始化时将指标存入本地，做排名之用
            if(date <= 1900.01.01) {
            	save_table(tb_fund_performance, 'raw_db.fund_performance', false);
            	save_table(tb_fund_indicator, 'raw_db.fund_indicator', false);
            	save_table(tb_fund_risk_stats, 'raw_db.fund_risk_stats', false);
            	save_table(tb_fund_riskadjret_stats, 'raw_db.fund_riskadjret_stats', false);
            	save_table(tb_fund_style_stats, 'raw_db.fund_style_stats', false);
            }

        } catch(ex) {

            //TODO: Log errors
            rt = ex;
        }
    }
    
    return rt;
	
}



/*
 *   【临时】用于数据初始化：只计算收益
 * 
 *   @param entityType <STRING>: 'MF', 'HF'...
 *   @param date <DATETIME>: 净值更新时间
 * 
 */
def ms_calFundReturns() {

    rt = '';

    very_old_date = 1990.01.01;

    // 取基金列表 (27s)
    tb_cal_funds = ms_get_fund_list_by_nav_updatetime(NULL, very_old_date);

    if(tb_cal_funds.isVoid() || tb_cal_funds.size() == 0 ) return;

    tb_fund_performance = create_entity_performance();

    tb_fund_indicator = create_entity_indicator();
    tb_fund_risk_stats = create_entity_risk_stats();
    tb_fund_riskadjret_stats = create_entity_riskadjret_stats();
    tb_fund_style_stats = create_entity_style_stats();

    tb_fund_performance_weekly = create_entity_performance_weekly();
    tb_fund_latest_performance = create_entity_latest_performance();

    // 分批跑
    i = 0;
    batch_size = 1000;


    do {

        funds = tb_cal_funds[i : min(tb_cal_funds.size(), i+batch_size)];

        if(funds.isVoid() || funds.size() == 0) break;

        // 200ms
        fund_info = SELECT entity_id, price_date, inception_date, benchmark_id, ini_value 
                    FROM ej(funds, ms_get_fund_info(funds.entity_id), 'entity_id', 'fund_id');

        // 计算月收益 (19s)
        tb_nav = ms_get_fund_monthly_nav(fund_info.entity_id);

        rets = cal_monthly_returns_by_nav(fund_info, tb_nav);

        if(!rets.isVoid() && rets.size() > 0) {

            // 计算月度指标 (67s)
            rets.rename!('cumulative_nav', 'nav');
            indicators = cal_monthly_indicators('MF', 'PBI', rets);

            // 仿照MySQL的表结构准备好记录 （1s)
            generate_entity_performance(fund_info, indicators, true, tb_fund_performance);

            generate_entity_indicator(fund_info, indicators, true, tb_fund_indicator);
            generate_entity_risk_stats(fund_info, indicators, true, tb_fund_risk_stats);
            generate_entity_riskadjret_stats(fund_info, indicators, true, tb_fund_riskadjret_stats);
            generate_entity_style_stats(fund_info, indicators, true, tb_fund_style_stats);

        }
        
        // 计算周收益 (49s)
        rets_w = cal_weekly_returns('MF', fund_info);

        if(! rets_w.isVoid() && rets_w.size() > 0) {
            generate_entity_performance_weekly(fund_info, rets_w, true, tb_fund_performance_weekly);
        }

        // 计算最新收益 (23s)
        perf_latest = cal_latest_performance('MF', fund_info, true);

        if(! perf_latest.isVoid() && perf_latest.size() > 0) {
            generate_entity_latest_performance(fund_info, perf_latest, true, tb_fund_latest_performance);
        }

        i += batch_size;

//    } while (i < batch_size);
    } while (i <= tb_cal_funds.size());


    if(! tb_fund_performance.isVoid() && tb_fund_performance.size() > 0) {

        // save data to MySQL  (26m)
        try {

            chg_columns_for_mysql(tb_fund_performance, 'fund_id');
            save_and_sync(tb_fund_performance, 'raw_db.fund_performance', 'raw_db.fund_performance');

            chg_columns_for_mysql(tb_fund_indicator, 'fund_id');
            save_and_sync(tb_fund_indicator, 'raw_db.fund_indicator', 'raw_db.fund_indicator');

            chg_columns_for_mysql(tb_fund_risk_stats, 'fund_id');
            // mfdb.fund_performance 表中 maxdrawdown_6m & maxdrawdown_ytd 是虚拟列，这里用数据列顺序强行写入真实列 6m_maxdrawdown & ytd_maxdrawdown (DolphinDB 不允许字段名以数字开头)
            save_and_sync(tb_fund_risk_stats, 'raw_db.fund_risk_stats', 'raw_db.fund_risk_stats');

            chg_columns_for_mysql(tb_fund_riskadjret_stats, 'fund_id');
            save_and_sync(tb_fund_riskadjret_stats, 'raw_db.fund_riskadjret_stats', 'raw_db.fund_riskadjret_stats');

            chg_columns_for_mysql(tb_fund_style_stats, 'fund_id');
            save_and_sync(tb_fund_style_stats, 'raw_db.fund_style_stats', 'raw_db.fund_style_stats');

            save_and_sync(tb_fund_performance_weekly, 'raw_db.fund_performance_weekly', 'raw_db.fund_performance_weekly');

            save_and_sync(tb_fund_latest_performance, 'raw_db.fund_latest_performance', 'raw_db.fund_latest_nav_performance');

        } catch(ex) {

            //TODO: Log errors
            rt = ex;
        }
    }
    
    return rt;
	
}

/*
 *   实验性质的API
 * 
 * 
 */
def calFundIndexCorrelation(entityType, date) {

    if(find(['HF', 'MF'], entityType) < 0) return null;

    // 取有最新净值变动的基金列表 (1s)
    tb_cal_funds = get_entity_list_by_nav_updatetime(entityType, NULL, date, true);

    if(tb_cal_funds.isVoid() || tb_cal_funds.size() == 0 ) return;

//    tb_fund_index_coe = create_entity_index_coe();
    // (7m)
    coe = cal_entity_index_coe(entityType, tb_cal_funds[0:1000]);

    return coe;
}


/*
 *   计算排名并存入数据库
 * 
 *   @param end_date <MONTH>
 * 
 *   Example: cal_entity_ranking('MF', 2024.09M, true);
 */
def CalEntityRanking(entity_type, end_date, isFromMySQL) {
/*
entity_type = 'MF';
end_date = 2024.09M;
isFromMySQL = true;
*/
	entity_info = get_entity_info(entity_type, NULL);

	// 收益
	v_ranking_tables = cal_ret_ranking(entity_type, entity_info, end_date, isFromMySQL);
    save_ranking_tables(v_ranking_tables);

    // 风险相关的指标
    v_ranking_tables = cal_risk_ranking(entity_type, entity_info, end_date, isFromMySQL);
    save_ranking_tables(v_ranking_tables);

    // 风险调整收益指标
    v_ranking_tables = cal_risk_adj_return_ranking(entity_type, entity_info, end_date, isFromMySQL);
    save_ranking_tables(v_ranking_tables);

    // 杂项指标
    v_ranking_tables = cal_other_indicator_ranking(entity_type, entity_info, end_date, isFromMySQL);
    save_ranking_tables(v_ranking_tables);

/*
    v_tables = NULL;

    if(entity_type IN ['MF', 'HF', 'MI', 'FI']) {
    	
    	v_tables = ['mfdb.fund_performance', 'mfdb.fund_risk_stats', 'mfdb.fund_riskadjret_stats', 'mfdb.fund_indicator', 'mfdb.fund_style_stats'];
    	
    } else if(entity_type = 'PF') {

    	v_tables = ['pfdb.pf_portfolio_performance', 'pfdb.pf_portfolio_risk_stats', 'pfdb.pf_portfolio_riskadjret_stats', 'pfdb.pf_portfolio_indicator', 'pfdb.pf_portfolio_style_stats'];

    } else if(entity_type = 'CF') {

    	v_tables = ['pfdb.pf_cus_fund_performance', 'pfdb.pf_cus_fund_risk_stats', 'pfdb.pf_cus_fund_riskadjret_stats', 'pfdb.pf_cus_fund_indicator', 'pfdb.pf_cus_fund_style_stats'];

    } else if(entity_type = 'FA') {
    	
    	v_tables = ['pfdb.cm_factor_performance', 'pfdb.cm_factor_risk_stats', 'pfdb.cm_factor_riskadjret_stats', 'pfdb.cm_factor_indicator', 'pfdb.cm_factor_style_stats'];

    } else if(entity_type = 'CI') {
    	
    	v_tables = ['pfdb.cm_udf_index_performance', 'pfdb.cm_udf_index_risk_stats', 'pfdb.cm_udf_index_riskadjret_stats', 'pfdb.cm_udf_index_indicator', 'pfdb.cm_udf_index_style_stats'];
    }

    if(v_tables.isNull()) return null;
    
	for(table in v_tables) {
		
        tb_data = get_monthly_indicator_data(table, end_date, isFromMySQL);
        
	}
*/
}