DolphinDB/modules/task_monthlyPerformance.dos

module fundit::task_monthlyPerformance

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

/*
 *   计算基金排名并存入数据库
 * 
 *   @param entity_type <STRING>: 目前传入'MF'和'HF'都可以将公私募同时跑
 *   @param end_date <MONTH>: XXXX.YYM
 *   @param isFromMySQL <BOOL>: false 时读取dolphin本地的收益及指标表，用于初始化数据
 *   
 *   NOTE: 在假设公募和私募分类互相独立成立的前提下，之用跑一次即可计算公募和私募的排名
 *   TODO: BFI ranking
 * 
 *   Example: cal_entity_ranking('MF', 2024.09M, true);
 */
def CalEntityRanking(entity_type, end_date, isFromMySQL=true) {
/*
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(entity_type, v_ranking_tables);

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

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

    // 杂项指标
    v_ranking_tables = cal_other_indicator_ranking(entity_type, entity_info, end_date, isFromMySQL);
    save_ranking_tables(entity_type, 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);
        
	}
*/
}

/*
 * 
 *   以公募基金为评级参考，计算组合、私有基金收益及指标排名
 * 
 * 
 *   Example: CalRelativeRanking('PF', 2024.09M, true);
 */
def CalRelativeRanking(entity_type, end_date, isFromMySQL=true) {

	entity_info = get_entity_info(entity_type, NULL);

	if(entity_type == 'PF')
	    entity_info = SELECT * FROM entity_info WHERE portfolio_type IN (1, 2) // 1: 用户组合、2：客户真实组合，忽略客户推荐组合、总览综合等虚拟组合

    v_ranking_by = ['strategy', 'substrategy'/*, 'factor_id'*/];

    // 暂时以公募混合基金为排名参考
    for(ranking_by in v_ranking_by) {

    	if(ranking_by == 'strategy') {
        
        	v_category = EXEC DISTINCT strategy FROM entity_info WHERE strategy IS NOT NULL;
	        tb_fund_ranking = get_fund_indicator_ranking(NULL, end_date, v_category, true)
        
    	} else if(ranking_by == 'substrategy') {

   			v_category = EXEC DISTINCT substrategy FROM entity_info WHERE substrategy IS NOT NULL;
	    	tb_fund_ranking = get_fund_indicator_substrategy_ranking(NULL, end_date, v_category, true)
    	
    	} else if(ranking_by == 'bfi') {

    		tb_fund_ranking = null;
	    }

    	if(tb_fund_ranking == null) return;

	    tb_fund_ranking.rename!(ranking_by, 'category_id');

    	// 收益
	    v_indicator_id = [1];
    	benchmark_ranking = SELECT * FROM tb_fund_ranking WHERE indicator_id IN v_indicator_id;

    
	    // 风险指标
    	v_indicator_id = [2, 6, 9, 10, 11, 12, 21, 50, 52, 59];
	    benchmark_ranking = SELECT * FROM tb_fund_ranking WHERE indicator_id IN v_indicator_id;
    
	    entity_ranking = transform_risk_stats_for_ranking(entity_type, entity_info, end_date, ranking_by, isFromMySQL).rename!(ranking_by, 'category_id');

	    cal_relative_ranking(benchmark_ranking, entity_ranking, isFromMySQL);

	    entity_ranking.rename!('category_id', ranking_by);
	    save_relative_ranking_table(entity_type, entity_ranking, ranking_by);


    	// 风险调整收益指标
	    v_indicator_id = [14, 15, 16, 17, 18, 19, 40, 58];
    	benchmark_ranking = SELECT * FROM tb_fund_ranking WHERE indicator_id IN v_indicator_id;
    
	    entity_ranking = transform_risk_adj_ret_stats_for_ranking(entity_type, entity_info, end_date, ranking_by, isFromMySQL).rename!(ranking_by, 'category_id');

    	cal_relative_ranking(benchmark_ranking, entity_ranking, isFromMySQL);

	    entity_ranking.rename!('category_id', ranking_by);
	    save_relative_ranking_table(entity_type, entity_ranking, ranking_by);    

	    // 其它风险指标
    	v_indicator_id = [37, 38, 41, 42, 43, 44, 45, 46, 47, 48, 49];
	    benchmark_ranking = SELECT * FROM tb_fund_ranking WHERE indicator_id IN v_indicator_id;

    	entity_ranking = transform_other_indicator_for_ranking(entity_type, entity_info, end_date, ranking_by, isFromMySQL).rename!(ranking_by, 'category_id');

	    cal_relative_ranking(benchmark_ranking, entity_ranking, isFromMySQL);

	    entity_ranking.rename!('category_id', ranking_by);
	    save_relative_ranking_table(entity_type, entity_ranking, ranking_by);
    }
//    SELECT * FROM entity_ranking WHERE entity_id = 143109
	
}