DolphinDB/modules/task_fundPerformance.dos

module fundit::task_fundPerformance

use fundit::sqlUtilities
use fundit::dataPuller
use fundit::returnCalculator
use fundit::indicatorCalculator
use fundit::fundCalculator


/*
 *   存 fund_performance 表
 * 
 * 
 */
def generate_fund_performance(fund_info, indicators, isToMySQL, mutable fund_performance) {

    t = null;

    if(isToMySQL) {

        t = SELECT entity_id, end_date, price_date, nav AS cumulative_nav, ret AS ret_1m, ret AS ret_1m_a, trailing_ret AS ret_3m, trailing_ret_a AS ret_3m_a
            FROM indicators['PBI-3M'] AS ind
            INNER JOIN fund_info fi ON ind.entity_id = fi.entity_id
            WHERE ind.end_date >= fi.price_date.month(); // 过滤掉不必更新的旧记录
        
        UPDATE t
        SET ret_6m = trailing_ret, ret_6m_a = trailing_ret_a
        FROM ej(t, indicators['PBI-6M'], ['entity_id', 'end_date']);
        
        UPDATE t
        SET ret_1y = trailing_ret, ret_1y_a = trailing_ret_a
        FROM ej(t, indicators['PBI-1Y'], ['entity_id', 'end_date']);
        
        UPDATE t
        SET ret_2y = trailing_ret, ret_2y_a = trailing_ret_a
        FROM ej(t, indicators['PBI-2Y'], ['entity_id', 'end_date']);
        
        UPDATE t
        SET ret_3y = trailing_ret, ret_3y_a = trailing_ret_a
        FROM ej(t, indicators['PBI-3Y'], ['entity_id', 'end_date']);
        
        UPDATE t
        SET ret_4y = trailing_ret, ret_4y_a = trailing_ret_a
        FROM ej(t, indicators['PBI-4Y'], ['entity_id', 'end_date']);
        
        UPDATE t
        SET ret_5y = trailing_ret, ret_5y_a = trailing_ret_a
        FROM ej(t, indicators['PBI-5Y'], ['entity_id', 'end_date']);
        
        UPDATE t
        SET ret_10y = trailing_ret, ret_10y_a = trailing_ret_a
        FROM ej(t, indicators['PBI-10Y'], ['entity_id', 'end_date']);
        
        UPDATE t
        SET ret_ytd = trailing_ret, ret_ytd_a = trailing_ret_a
        FROM ej(t, indicators['PBI-YTD'], ['entity_id', 'end_date']);
        
        // 取消了 ret_incep_a_all （没意义） 和 ret_incep_a_gips (ret_incep_a 与之相等）
        UPDATE t
        SET ret_incep = trailing_ret, ret_incep_a = trailing_ret_a
        FROM ej(t, indicators['PBI-INCEP'], ['entity_id', 'end_date']);

        INSERT INTO fund_performance SELECT * FROM t;

    } else {
        
    }
}

/*
 *   定时任务：最新净值触发的业绩指标计算
 * 
 *   @param entityType <STRING>: 'MF', 'HF'...
 *   @param date <DATETIME>: 净值更新时间
 * 
 *   TODO: 目前收益表在MySQL中，所以需要将计算的最新收益与MySQL中的历史数据合并
 */
def calFundPerformance(entityType, date) {

    rt = '';

    very_old_date = 1990.01.01;

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

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

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

    // 分批跑
    i = 0;
    batch_size = 1000;
    tb_fund_performance = table(1000:0, 
                              ['entity_id', 'end_date', 'price_date', 'cumulative_nav',
                               'ret_1m', 'ret_1m_a', 'ret_3m', 'ret_3m_a', 'ret_6m', 'ret_6m_a',
                               'ret_1y', 'ret_1y_a', 'ret_2y', 'ret_2y_a', 'ret_3y', 'ret_3y_a', 'ret_4y', 'ret_4y_a', 
                               'ret_5y', 'ret_5y_a', 'ret_10y', 'ret_10y_a', 'ret_ytd', 'ret_ytd_a', 'ret_incep', 'ret_incep_a'],
                              [SYMBOL, MONTH, DATE, DOUBLE,
                               DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE,
                               DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE,
                               DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE]);

    do {

        funds = tb_cal_funds[i:batch_size];

        fund_info = SELECT entity_id, price_date, inception_date, benchmark_id, ini_value 
                    FROM ej(funds, get_fund_info(funds.entity_id), 'entity_id', 'fund_id');

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

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

        // TODO: 最新更新的指标存入数据库
        generate_fund_performance(fund_info, indicators, true, tb_fund_performance);

        i += batch_size;

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

    tb_fund_performance.rename!('entity_id', 'fund_id');
    // 将 dolphinDB 的 MONTH 换成 MySQL 的 YYYY-MM 格式
    v_end_date = EXEC end_date.temporalFormat('yyyy-MM') FROM tb_fund_performance;
    tb_fund_performance.replaceColumn!('end_date', v_end_date);

    try {
        save_table(tb_fund_performance, 'mfdb.fund_performance', true);

    } catch(ex) {

        //TODO: Log errors

        rt = ex;

    }
    
    return rt;
	
}