DolphinDB/modules/task_monthlyPerformance.dos

module fundit::task_monthlyPerformance

use fundit::sqlUtilities;
use fundit::operationDataPuller;
use fundit::performanceDataPuller;
use fundit::indicatorCalculator;
use fundit::dataSaver;
use fundit::bfiMatcher;
use fundit::rankingCalculator;
use fundit::navCalculator;


/*
 *   [定时任务] 计算基金一、二级分类排名并存入数据库
 * 
 *   @param entity_type <STRING>: 'MF', 'HF' (MF=HF)
 *   @param end_date <MONTH>:
 *   @param isFromMySQL <BOOL>: false 时读取dolphin本地的收益及指标表，用于初始化数据
 *   
 * 
 *   Example: CalEntityRankingTask('MF', 2024.09M, true);
 */
def CalEntityRankingTask(entity_type, end_date, isFromMySQL=true) {

	if(!(entity_type in ['MF', 'HF'])) return NULL;
	
	entity_info = get_entity_info(entity_type, NULL);

	v_ranking_tables = cal_indicator_ranking('strategy', entity_type, entity_info, end_date, isFromMySQL);

    save_ranking_tables(entity_type, v_ranking_tables);

}

/*
 *   [定时任务] 计算基金BFI排名并存入数据库
 * 
 *   @param entity_type <STRING>: 'MF', 'HF' (MF=HF)
 *   @param end_date <MONTH>:
 *   @param isFromMySQL <BOOL>: false 时读取dolphin本地的收益及指标表，用于初始化数据
 *   
 * 
 *   Example: CalEntityBfiRankingTask('MF', 2024.09M, true);
 */
def CalEntityBfiRankingTask(entity_type, end_date, isFromMySQL=true) {

	if(!(entity_type in ['MF', 'HF'])) return NULL;
	
	entity_info = get_entity_info(entity_type, NULL);

	v_ranking_tables = cal_indicator_ranking('bfi', entity_type, entity_info, end_date, isFromMySQL);
		
    save_ranking_tables(entity_type, v_ranking_tables);

}

/*
 *   Private Method: 计算相对排名并存入数据库
 * 
 * 
 */
def cal_and_save_relative_ranking(entity_type, benchmark_ranking, entity_ranking, ranking_by, isFromMySQL=true) {
// benchmark_ranking= tb_fund_ranking
    t_entity_ranking = entity_ranking;
    
    cal_relative_ranking(benchmark_ranking, t_entity_ranking, isFromMySQL);

    t_entity_ranking.rename!('category_id', iif(ranking_by=='bfi', 'factor_id', ranking_by));

    save_relative_ranking_table(entity_type, t_entity_ranking, ranking_by);
	
}


/*
 * 
 *   [定时任务] 以公募基金为评级参考，计算组合、私有基金收益及指标排名
 * 
 *   @param entityType <STRING>: PF
 * 
 *   TODO: customer fund
 *   TODO: 计算单个组合时总耗时1.5min, 大部分时间用来获取Mysql数据
 * 
 *   Example: CalRelativeRankingTask('PF', NULL, 2024.09M, true);
 *            CalRelativeRankingTask('PF', 143109, 2024.09M, true); 
 */
def CalRelativeRankingTask(entity_type, entity_ids, end_date, isFromMySQL=true) {
// entity_type = 'PF'
// end_date = 2024.09M
// isFromMySQL = true
// ranking_by = 'bfi'
// entity_ids = 143109

	entity_info = get_entity_info(entity_type, entity_ids);

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

    v_ranking_by = ['strategy', 'substrategy', 'bfi'];

    // 暂时以公募混合基金为排名参考
    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, isFromMySQL);

	        UPDATE tb_fund_ranking SET category_id = strategy$STRING;
        
    	} 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, isFromMySQL);

	    	UPDATE tb_fund_ranking SET category_id = substrategy$STRING;
    	
    	} else if(ranking_by == 'bfi') {

			if(entity_ids != NULL || entity_ids != '')
				v_category = EXEC DISTINCT factor_id FROM get_entity_bfi_factors(entity_type, entity_ids, end_date, end_date);
			else
			    v_category = NULL;

    		tb_fund_ranking = get_fund_bfi_bm_indicator_ranking(NULL, end_date, v_category, isFromMySQL);

    		UPDATE tb_fund_ranking SET category_id = factor_id;
	    }

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

    	entity_ranking = transform_data_for_ranking(entity_type, entity_info, end_date, ranking_by, isFromMySQL);

        cal_and_save_relative_ranking(entity_type, tb_fund_ranking, entity_ranking, ranking_by, isFromMySQL);

    }
	
}

/*
 *  计算并存储基金经理和公司月度净值
 * 
 *  @return <TABLE>: [COLUMNS] entity_id, curve_type, strategy, end_date, price_date, ret, nav
 * 
 */
def cal_and_save_mc_nav(entity_type, entity_date, is_save_local) {

    tb_monthly_ret = table(1000:0, ['entity_id', 'curve_type', 'strategy', 'end_date', 'price_date', 'ret', 'nav'],
    							   [SYMBOL, INT, INT, MONTH, DATE, DOUBLE, DOUBLE]);

	if(entity_type == 'PL') s_entity_type = 'manager';
	else if(entity_type == 'CO') s_entity_type = 'company';
	else return tb_monthly_ret;

	if(entity_date.isVoid() || entity_date.size() == 0) return tb_monthly_ret;

    // 准备类似MySQL结构的数据表
    tb_entity_nav = create_mc_fitted_curve();

    // 暂时与 MySQL 保持一致，只计算公募，私募，公私募综合三条时间序列。未来可细化至公、私募+主策略
    d_curve_type = dict(INT, INT);
    d_curve_type[1] = 1; // 私募
    d_curve_type[4] = 2; // 公募
    d_curve_type[7] = -99; // 公私募综合

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

    all_entity_id = entity_date.entity_id.distinct();

    do { // 14 sec

        tb_entity = SELECT * FROM entity_date
                    WHERE entity_id IN all_entity_id[i : min(all_entity_id.size(), i+batch_size)];

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

		s_json = tb_entity.toStdJson();
		t_ret = get_mc_monthly_return(s_entity_type, s_json, 0, 1, true);
        
        for(cur in d_curve_type.keys()) {

			tmp = SELECT entity_id, cur AS curve_type, 0 AS strategy, end_date, price_date, ret, incl_cal_cnt 
			      FROM t_ret WHERE raise_type = d_curve_type[cur] AND strategy = -99; // 目前只需要全策略

			// 计算月收益
        	tb_nav = cal_mc_nav_by_return(entity_type, tmp);

	        INSERT INTO tb_entity_nav 
	        	SELECT entity_id, curve_type, strategy, end_date, nav, incl_cal_cnt
	            FROM ej(tb_nav, tmp, ['entity_id', 'curve_type', 'strategy', 'end_date']);

			INSERT INTO tb_monthly_ret
				SELECT entity_id, curve_type, strategy, end_date.temporalParse('yyyy-MM').month(), price_date, ret, nav
				FROM ej(tmp, tb_nav, ['entity_id', 'curve_type', 'strategy', 'end_date']);
        }

        i += batch_size;

    } while (i <= all_entity_id.size());


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

        // save data to MySQL  (12 sec)
        try {

            tb_entity_nav.rename!('entity_id', iif(entity_type == 'PL', 'fund_manager_id', 'company_id'));
            save_and_sync(tb_entity_nav, iif(entity_type == 'PL', 'raw_db.fund_manager_fitted_curve', 'raw_db.company_fitted_curve'), );

            // 数据初始化时将指标存入本地
            if(is_save_local == true) {
            	save_table(tb_entity_nav, iif(entity_type == 'PL', 'mfdb.fund_manager_fitted_curve', 'mfdb.company_fitted_curve'), false);
            }

        } catch(ex) {

            //TODO: Log errors
        }
    }

    return tb_monthly_ret;
	
}

/*
 *   计算并存储基金经理/公司的月度收益及指标
 * 
 * 
 */
def cal_and_save_mc_indicator(entity_type, entity_date, monthly_returns, is_save_local) {

    rt = '';

	if(!(entity_type IN ['PL', 'CO'])) return rt;
	if(entity_date.isVoid() || entity_date.size() == 0) return rt;

	indicators = cal_mc_monthly_indicators(entity_type, 'PBI', monthly_returns);

	
}

/*
 *  [定时任务]: 基金经理月净值计算
 * 
 * 
 */
def CalManagerNavTask(updatetime) {
//updatetime = 2024.11.05;

	is_save_local = iif(updatetime <= get_ini_data_const()['updatetime'], true, false);

	// 31 sec
	entity_date = get_manager_list_by_fund_updatetime(updatetime);

	entity_date.rename!('manager_id', 'entity_id');

	tb_monthly_ret = cal_and_save_mc_nav('PL', entity_date, is_save_local);

	cal_and_save_mc_indicator('PL', entity_date, tb_monthly_ret, is_save_local);

	entity_date = null;
	tb_monthly_ret = null;
}


/*
 *  [定时任务]: 基金公司月净值计算
 * 
 * 
 */
def CalCompanyNavTask(updatetime) {
//updatetime = 2024.11.05;

	is_save_local = iif(updatetime <= get_ini_data_const()['updatetime'], true, false);

	// 31 sec
	entity_date = get_company_list_by_fund_updatetime(updatetime);

	entity_date.rename!('company_id', 'entity_id');

	cal_and_save_mc_nav('CO', entity_date, is_save_local);

	entity_date = null;
}

/*
 * 
 *  [定时任务]: 基金经理的BFI MATCHING
 * 
 * 
 */
def MatchManagerBFITask(updatetime) {

	rt = '';

	is_save_local = iif(updatetime <= get_ini_data_const()['updatetime'], true, false);

	entity_type = 'PL';

	// 31 sec
	entity_date = get_mc_performance_by_updatetime(entity_type, updatetime);

	if(entity_date.isVoid() || entity_date.size() == 0) return rt;

	i = 0;
	batch_size = 1000;
	max_cnt = entity_date.size();

	do {

		t_entity_date = entity_date[i : min(max_cnt, i + batch_size)];
		// 22 min per 1000 records, way too slow
		t_bfi = match_mc_bfi(entity_type, t_entity_date);

		t_max_r2 = SELECT entity_id , curve_type, strategy, factor_id.first() AS factor_id, end_date,
			              string(NULL) AS performance_flag, coe.first() AS coe, r2.first() AS r2, concat(factor_name, ",") AS rz_portrait
		           FROM ej(t_bfi, get_bfi_index_list(), 'factor_id')
		           GROUP BY entity_id, curve_type, strategy, end_date;
		try {
			// 高度怀疑 pf_manager_factor_bfi 表只是中间表，没有用，这里就不存了

			// 有效 factors 存到 xxx_factor_bfi_by_category_group 表
			chg_columns_for_mysql(t_bfi, 'manager_id');
			save_and_sync(t_bfi, 'raw_db.pf_manager_factor_bfi_by_category_group', );

			// 有效因子中 R2 最大的因子存 xxx_max_r2 
			chg_columns_for_mysql(t_max_r2, 'manager_id');
			save_and_sync(t_max_r2, 'raw_db.pf_manager_factor_bfi_max_r2', );
			
		} catch (ex) {
            //TODO: Log errors
            rt += ex + '\n';
		}

		i += batch_size

	} while (i < max_cnt);

	return rt;
}