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 {
        
    }
}

/*
 *   按照 fund_risk_stats 表结构准备数据记录
 * 
 * 
 */
def generate_fund_risk_stats(fund_info, indicators, isToMySQL, mutable fund_risk_stats) {

    t = null;

    if(isToMySQL) {

        t = SELECT entity_id, end_date, std_dev_a AS stddev_6m, ds_dev_a AS downsidedev_6m, alpha_a AS alpha_6m, winrate AS winrate_6m, beta AS beta_6m,
                                        skewness AS skewness_6m, kurtosis AS kurtosis_6m, wrst_month AS worstmonth_6m, drawdown AS maxdrawdown_6m
            FROM indicators['PBI-6M'] 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 stddev_1y = std_dev_a, downsidedev_1y = ds_dev_a, alpha_1y = alpha_a, winrate_1y = winrate, beta_1y = beta,
            skewness_1y = skewness, kurtosis_1y = kurtosis, worstmonth_1y = wrst_month, maxdrawdown_1y = drawdown
        FROM ej(t, indicators['PBI-1Y'], ['entity_id', 'end_date']);
        
        UPDATE t
        SET stddev_2y = std_dev_a, downsidedev_2y = ds_dev_a, alpha_2y = alpha_a, winrate_2y = winrate, beta_2y = beta,
            skewness_2y = skewness, kurtosis_2y = kurtosis, worstmonth_2y = wrst_month, maxdrawdown_2y = drawdown
        FROM ej(t, indicators['PBI-2Y'], ['entity_id', 'end_date']);
        
        UPDATE t
        SET stddev_3y = std_dev_a, downsidedev_3y = ds_dev_a, alpha_3y = alpha_a, winrate_3y = winrate, beta_3y = beta,
            skewness_3y = skewness, kurtosis_3y = kurtosis, worstmonth_3y = wrst_month, maxdrawdown_3y = drawdown
        FROM ej(t, indicators['PBI-3Y'], ['entity_id', 'end_date']);
        
        UPDATE t
        SET stddev_4y = std_dev_a, downsidedev_4y = ds_dev_a, alpha_4y = alpha_a, winrate_4y = winrate, beta_4y = beta,
            skewness_4y = skewness, kurtosis_4y = kurtosis, worstmonth_4y = wrst_month, maxdrawdown_4y = drawdown
        FROM ej(t, indicators['PBI-4Y'], ['entity_id', 'end_date']);
        
        UPDATE t
        SET stddev_5y = std_dev_a, downsidedev_5y = ds_dev_a, alpha_5y = alpha_a, winrate_5y = winrate, beta_5y = beta,
            skewness_5y = skewness, kurtosis_5y = kurtosis, worstmonth_5y = wrst_month, maxdrawdown_5y = drawdown
        FROM ej(t, indicators['PBI-5Y'], ['entity_id', 'end_date']);
        
        UPDATE t
        SET stddev_10y = std_dev_a, downsidedev_10y = ds_dev_a, alpha_10y = alpha_a, winrate_10y = winrate, beta_10y = beta,
            skewness_10y = skewness, kurtosis_10y = kurtosis, worstmonth_10y = wrst_month, maxdrawdown_10y = drawdown
        FROM ej(t, indicators['PBI-10Y'], ['entity_id', 'end_date']);
        
        UPDATE t
        SET stddev_ytd = std_dev_a, downsidedev_ytd = ds_dev_a, alpha_ytd = alpha_a, winrate_ytd = winrate, beta_ytd = beta,
            skewness_ytd = skewness, kurtosis_ytd = kurtosis, worstmonth_ytd = wrst_month, maxdrawdown_ytd = drawdown
        FROM ej(t, indicators['PBI-YTD'], ['entity_id', 'end_date']);
        
        UPDATE t
        SET stddev_incep = std_dev_a, downsidedev_incep = ds_dev_a, alpha_incep = alpha_a, winrate_incep = winrate, beta_incep = beta,
            skewness_incep = skewness, kurtosis_incep = kurtosis, worstmonth_incep = wrst_month, maxdrawdown_incep = drawdown
        FROM ej(t, indicators['PBI-INCEP'], ['entity_id', 'end_date']);
        
        INSERT INTO fund_risk_stats SELECT * FROM t;

    } else {
        
    }
}

/*
 *   按照 fund_riskadjret_stats 表结构准备数据记录
 * 
 * 
 */
def generate_fund_riskadjret_stats(fund_info, indicators, isToMySQL, mutable fund_riskadjret_stats) {

    t = null;

    if(isToMySQL) {

        t = SELECT entity_id, end_date,
                   sharpe_a AS sharperatio_6m, sortino_a AS sortinoratio_6m, treynor AS treynorratio_6m, jensen_a AS jensen_6m,
                   calmar AS calmarratio_6m, omega AS omegaratio_6m, kappa AS kapparatio_6m
            FROM indicators['PBI-6M'] 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 sharperatio_1y = sharpe_a, sortinoratio_1y = sortino_a, treynorratio_1y = treynor, jensen_1y = jensen_a,
            calmarratio_1y = calmar, omegaratio_1y = omega, kapparatio_1y = kappa
        FROM ej(t, indicators['PBI-1Y'], ['entity_id', 'end_date']);
        
        UPDATE t
        SET sharperatio_2y = sharpe_a, sortinoratio_2y = sortino_a, treynorratio_2y = treynor, jensen_2y = jensen_a,
            calmarratio_2y = calmar, omegaratio_2y = omega, kapparatio_2y = kappa
        FROM ej(t, indicators['PBI-2Y'], ['entity_id', 'end_date']);
        
        UPDATE t
        SET sharperatio_3y = sharpe_a, sortinoratio_3y = sortino_a, treynorratio_3y = treynor, jensen_3y = jensen_a,
            calmarratio_3y = calmar, omegaratio_3y = omega, kapparatio_3y = kappa
        FROM ej(t, indicators['PBI-3Y'], ['entity_id', 'end_date']);
        
        UPDATE t
        SET sharperatio_4y = sharpe_a, sortinoratio_4y = sortino_a, treynorratio_4y = treynor, jensen_4y = jensen_a,
            calmarratio_4y = calmar, omegaratio_4y = omega, kapparatio_4y = kappa
        FROM ej(t, indicators['PBI-4Y'], ['entity_id', 'end_date']);
        
        UPDATE t
        SET sharperatio_5y = sharpe_a, sortinoratio_5y = sortino_a, treynorratio_5y = treynor, jensen_5y = jensen_a,
            calmarratio_5y = calmar, omegaratio_5y = omega, kapparatio_5y = kappa
        FROM ej(t, indicators['PBI-5Y'], ['entity_id', 'end_date']);
        
        UPDATE t
        SET sharperatio_10y = sharpe_a, sortinoratio_10y = sortino_a, treynorratio_10y = treynor, jensen_10y = jensen_a,
            calmarratio_10y = calmar, omegaratio_10y = omega, kapparatio_10y = kappa
        FROM ej(t, indicators['PBI-10Y'], ['entity_id', 'end_date']);
        
        UPDATE t
        SET sharperatio_ytd = sharpe_a, sortinoratio_ytd = sortino_a, treynorratio_ytd = treynor, jensen_ytd = jensen_a,
            calmarratio_ytd = calmar, omegaratio_ytd = omega, kapparatio_ytd = kappa
        FROM ej(t, indicators['PBI-YTD'], ['entity_id', 'end_date']);
        
        UPDATE t
        SET sharperatio_incep = sharpe_a, sortinoratio_incep = sortino_a, treynorratio_incep = treynor, jensen_incep = jensen_a,
            calmarratio_incep = calmar, omegaratio_incep = omega, kapparatio_incep = kappa
        FROM ej(t, indicators['PBI-INCEP'], ['entity_id', 'end_date']);
        
        INSERT INTO fund_riskadjret_stats SELECT * FROM t;

    } else {
        
    }
}

/*
 *   按照 fund_indicator 表结构准备数据记录
 * 
 * 
 */
def generate_fund_indicator(fund_info, indicators, isToMySQL, mutable fund_indicator) {

    t = null;

    if(isToMySQL) {

        t = SELECT entity_id, end_date, info_a AS info_ratio_6m, m2_a AS m2_6m, track_error_a AS tracking_error_6m
            FROM indicators['PBI-6M'] 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 info_ratio_1y = info_a, m2_1y = m2_a, tracking_error_1y = track_error_a
        FROM ej(t, indicators['PBI-1Y'], ['entity_id', 'end_date']);
        
        UPDATE t
        SET info_ratio_2y = info_a, m2_2y = m2_a, tracking_error_2y = track_error_a, var_2y = var, cvar_2y = cvar
        FROM ej(t, indicators['PBI-2Y'], ['entity_id', 'end_date']);
        
        UPDATE t
        SET info_ratio_3y = info_a, m2_3y = m2_a, tracking_error_3y = track_error_a, var_3y = var, cvar_3y = cvar
        FROM ej(t, indicators['PBI-3Y'], ['entity_id', 'end_date']);
        
        UPDATE t
        SET info_ratio_4y = info_a, m2_4y = m2_a, tracking_error_4y = track_error_a, var_4y = var, cvar_4y = cvar
        FROM ej(t, indicators['PBI-4Y'], ['entity_id', 'end_date']);
        
        UPDATE t
        SET info_ratio_5y = info_a, m2_5y = m2_a, tracking_error_5y = track_error_a, var_5y = var, cvar_5y = cvar
        FROM ej(t, indicators['PBI-5Y'], ['entity_id', 'end_date']);
        
        UPDATE t
        SET info_ratio_10y = info_a, m2_10y = m2_a, tracking_error_10y = track_error_a, var_10y = var, cvar_10y = cvar
        FROM ej(t, indicators['PBI-10Y'], ['entity_id', 'end_date']);
        
        UPDATE t
        SET info_ratio_ytd = info_a, m2_ytd = m2_a, tracking_error_ytd = track_error_a
        FROM ej(t, indicators['PBI-YTD'], ['entity_id', 'end_date']);
        
        UPDATE t
        SET info_ratio_incep = info_a, m2_incep = m2_a, tracking_error_incep = track_error_a, var_incep = var, cvar_incep = cvar
        FROM ej(t, indicators['PBI-INCEP'], ['entity_id', 'end_date']);
        
        INSERT INTO fund_indicator SELECT * FROM t;

    } else {
        
    }
}

/*
 *   按照 fund_style_stats 表结构准备数据记录
 * 
 * 
 */
def generate_fund_style_stats(fund_info, indicators, isToMySQL, mutable fund_style_stats) {

    t = null;

    if(isToMySQL) {

        t = SELECT entity_id, end_date, upside_capture_ret AS upsidecapture_ret_6m, downside_capture_ret AS downsidecapture_ret_6m,
                                        upside_capture_ratio AS upsidecapture_ratio_6m, downside_capture_ratio AS downsidecapture_ratio_6m
            FROM indicators['PBI-6M'] 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 upsidecapture_ret_1y = upside_capture_ret, downsidecapture_ret_1y = downside_capture_ret,
            upsidecapture_ratio_1y = upside_capture_ratio, downsidecapture_ratio_1y = downside_capture_ratio
        FROM ej(t, indicators['PBI-1Y'], ['entity_id', 'end_date']);
        
        UPDATE t
        SET upsidecapture_ret_2y = upside_capture_ret, downsidecapture_ret_2y = downside_capture_ret,
            upsidecapture_ratio_2y = upside_capture_ratio, downsidecapture_ratio_2y = downside_capture_ratio
        FROM ej(t, indicators['PBI-2Y'], ['entity_id', 'end_date']);
        
        UPDATE t
        SET upsidecapture_ret_3y = upside_capture_ret, downsidecapture_ret_3y = downside_capture_ret,
            upsidecapture_ratio_3y = upside_capture_ratio, downsidecapture_ratio_3y = downside_capture_ratio
        FROM ej(t, indicators['PBI-3Y'], ['entity_id', 'end_date']);
        
        UPDATE t
        SET upsidecapture_ret_4y = upside_capture_ret, downsidecapture_ret_4y = downside_capture_ret,
            upsidecapture_ratio_4y = upside_capture_ratio, downsidecapture_ratio_4y = downside_capture_ratio
        FROM ej(t, indicators['PBI-4Y'], ['entity_id', 'end_date']);
        
        UPDATE t
        SET upsidecapture_ret_5y = upside_capture_ret, downsidecapture_ret_5y = downside_capture_ret,
            upsidecapture_ratio_5y = upside_capture_ratio, downsidecapture_ratio_5y = downside_capture_ratio
        FROM ej(t, indicators['PBI-5Y'], ['entity_id', 'end_date']);
        
        UPDATE t
        SET upsidecapture_ret_10y = upside_capture_ret, downsidecapture_ret_10y = downside_capture_ret,
            upsidecapture_ratio_10y = upside_capture_ratio, downsidecapture_ratio_10y = downside_capture_ratio
        FROM ej(t, indicators['PBI-10Y'], ['entity_id', 'end_date']);
        
        UPDATE t
        SET upsidecapture_ret_ytd = upside_capture_ret, downsidecapture_ret_ytd = downside_capture_ret,
            upsidecapture_ratio_ytd = upside_capture_ratio, downsidecapture_ratio_ytd = downside_capture_ratio
        FROM ej(t, indicators['PBI-YTD'], ['entity_id', 'end_date']);
        
        UPDATE t
        SET upsidecapture_ret_incep = upside_capture_ret, downsidecapture_ret_incep = downside_capture_ret,
            upsidecapture_ratio_incep = upside_capture_ratio, downsidecapture_ratio_incep = downside_capture_ratio
        FROM ej(t, indicators['PBI-INCEP'], ['entity_id', 'end_date']);
        
        INSERT INTO fund_style_stats SELECT * FROM t;

    } else {
        
    }
}

/*
 *   建表 fund_performance
 */
def create_fund_performance() {

    return 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]);
}

/*
 *   建表 fund_indicator
 */
def create_fund_indicator() {

    return table(1000:0,
               ['entity_id', 'end_date',
                'info_ratio_6m', 'm2_6m', 'tracking_error_6m',
                'info_ratio_1y', 'm2_1y', 'tracking_error_1y',
                'info_ratio_2y', 'm2_2y', 'tracking_error_2y', 'var_2y', 'cvar_2y', 
                'info_ratio_3y', 'm2_3y', 'tracking_error_3y', 'var_3y', 'cvar_3y', 
                'info_ratio_4y', 'm2_4y', 'tracking_error_4y', 'var_4y', 'cvar_4y', 
                'info_ratio_5y', 'm2_5y', 'tracking_error_5y', 'var_5y', 'cvar_5y', 
                'info_ratio_10y', 'm2_10y', 'tracking_error_10y', 'var_10y', 'cvar_10y', 
                'info_ratio_ytd', 'm2_ytd', 'tracking_error_ytd', 
                'info_ratio_incep', 'm2_incep','tracking_error_incep', 'var_incep', 'cvar_incep'],
               [SYMBOL, MONTH,
                DOUBLE, DOUBLE, DOUBLE,
                DOUBLE, DOUBLE, DOUBLE,
                DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE,
                DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE,
                DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE,
                DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE,
                DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE,
                DOUBLE, DOUBLE, DOUBLE,
                DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE]);
}

/*
 *   建表 fund_risk_stats
 */
def create_fund_risk_stats() {

    return table(1000:0,
               ['entity_id', 'end_date',
                'stddev_6m', 'downsidedev_6m', 'alpha_6m', 'winrate_6m', 'beta_6m', 'skewness_6m', 'kurtosis_6m', 'worstmonth_6m', 'maxdrawdown_6m', 
                'stddev_1y', 'downsidedev_1y', 'alpha_1y', 'winrate_1y', 'beta_1y', 'skewness_1y', 'kurtosis_1y', 'worstmonth_1y', 'maxdrawdown_1y', 
                'stddev_2y', 'downsidedev_2y', 'alpha_2y', 'winrate_2y', 'beta_2y', 'skewness_2y', 'kurtosis_2y', 'worstmonth_2y', 'maxdrawdown_2y', 
                'stddev_3y', 'downsidedev_3y', 'alpha_3y', 'winrate_3y', 'beta_3y', 'skewness_3y', 'kurtosis_3y', 'worstmonth_3y', 'maxdrawdown_3y', 
                'stddev_4y', 'downsidedev_4y', 'alpha_4y', 'winrate_4y', 'beta_4y', 'skewness_4y', 'kurtosis_4y', 'worstmonth_4y', 'maxdrawdown_4y', 
                'stddev_5y', 'downsidedev_5y', 'alpha_5y', 'winrate_5y', 'beta_5y', 'skewness_5y', 'kurtosis_5y', 'worstmonth_5y', 'maxdrawdown_5y', 
                'stddev_10y', 'downsidedev_10y','alpha_10y', 'winrate_10y', 'beta_10y', 'skewness_10y', 'kurtosis_10y', 'worstmonth_10y', 'maxdrawdown_10y', 
                'stddev_ytd', 'downsidedev_ytd', 'alpha_ytd', 'winrate_ytd', 'beta_ytd', 'skewness_ytd', 'kurtosis_ytd', 'worstmonth_ytd', 'maxdrawdown_ytd', 
                'stddev_incep', 'downsidedev_incep', 'alpha_incep', 'winrate_incep', 'beta_incep', 'skewness_incep', 'kurtosis_incep', 'worstmonth_incep', 'maxdrawdown_incep'],
               [SYMBOL, MONTH,
                DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE,
                DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE,
                DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE,
                DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE,
                DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE,
                DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE,
                DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE,
                DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE,
                DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE]);

}

/*
 *   建表 fund_riskadjret_stats
 */
def create_fund_riskadjret_stats() {

    return table(1000:0,
               ['entity_id', 'end_date',
                'sharperatio_6m', 'sortinoratio_6m', 'treynorratio_6m', 'jensen_6m', 'calmarratio_6m', 'omegaratio_6m', 'kapparatio_6m',
                'sharperatio_1y', 'sortinoratio_1y', 'treynorratio_1y', 'jensen_1y', 'calmarratio_1y', 'omegaratio_1y', 'kapparatio_1y',
                'sharperatio_2y', 'sortinoratio_2y', 'treynorratio_2y', 'jensen_2y', 'calmarratio_2y', 'omegaratio_2y', 'kapparatio_2y',
                'sharperatio_3y', 'sortinoratio_3y', 'treynorratio_3y', 'jensen_3y', 'calmarratio_3y', 'omegaratio_3y', 'kapparatio_3y',
                'sharperatio_4y', 'sortinoratio_4y', 'treynorratio_4y', 'jensen_4y', 'calmarratio_4y', 'omegaratio_4y', 'kapparatio_4y',
                'sharperatio_5y', 'sortinoratio_5y', 'treynorratio_5y', 'jensen_5y', 'calmarratio_5y', 'omegaratio_5y', 'kapparatio_5y',
                'sharperatio_10y', 'sortinoratio_10y', 'treynorratio_10y', 'jensen_10y', 'calmarratio_10y', 'omegaratio_10y', 'kapparatio_10y',
                'sharperatio_ytd', 'sortinoratio_ytd', 'treynorratio_ytd', 'jensen_ytd', 'calmarratio_ytd', 'omegaratio_ytd', 'kapparatio_ytd',
                'sharperatio_incep', 'sortinoratio_incep', 'treynorratio_incep', 'jensen_incep', 'calmarratio_incep', 'omegaratio_incep', 'kapparatio_incep'],
               [SYMBOL, MONTH,
                DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE,
                DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE,
                DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE,
                DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE,
                DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE,
                DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE,
                DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE,
                DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE,
                DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE]);
}

/*
 *   建表 fund_style_stats
 */
def create_fund_style_stats() {

    return table(1000:0,
               ['entity_id', 'end_date',
                'upsidecapture_ret_6m', 'downsidecapture_ret_6m', 'upsidecapture_ratio_6m', 'downsidecapture_ratio_6m',
                'upsidecapture_ret_1y', 'downsidecapture_ret_1y', 'upsidecapture_ratio_1y', 'downsidecapture_ratio_1y',
                'upsidecapture_ret_2y', 'downsidecapture_ret_2y', 'upsidecapture_ratio_2y', 'downsidecapture_ratio_2y',
                'upsidecapture_ret_3y', 'downsidecapture_ret_3y', 'upsidecapture_ratio_3y', 'downsidecapture_ratio_3y',
                'upsidecapture_ret_4y', 'downsidecapture_ret_4y', 'upsidecapture_ratio_4y', 'downsidecapture_ratio_4y',
                'upsidecapture_ret_5y', 'downsidecapture_ret_5y', 'upsidecapture_ratio_5y', 'downsidecapture_ratio_5y',
                'upsidecapture_ret_10y', 'downsidecapture_ret_10y', 'upsidecapture_ratio_10y', 'downsidecapture_ratio_10y',
                'upsidecapture_ret_ytd', 'downsidecapture_ret_ytd', 'upsidecapture_ratio_ytd', 'downsidecapture_ratio_ytd',
                'upsidecapture_ret_incep', 'downsidecapture_ret_incep', 'upsidecapture_ratio_incep', 'downsidecapture_ratio_incep'],
               [SYMBOL, MONTH,
                DOUBLE, DOUBLE, DOUBLE, DOUBLE,
                DOUBLE, DOUBLE, DOUBLE, DOUBLE,
                DOUBLE, DOUBLE, DOUBLE, DOUBLE,
                DOUBLE, DOUBLE, DOUBLE, DOUBLE,
                DOUBLE, DOUBLE, DOUBLE, DOUBLE,
                DOUBLE, DOUBLE, DOUBLE, DOUBLE,
                DOUBLE, DOUBLE, DOUBLE, DOUBLE,
                DOUBLE, DOUBLE, DOUBLE, DOUBLE, 
                DOUBLE, DOUBLE, DOUBLE, DOUBLE]);
}

/*
 *   根据 mysql 表改动某些字段
 */
def chg_columns_for_mysql(mutable tb_mysql) {

    tb_mysql.rename!('entity_id', 'fund_id');

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


/*
 *   定时任务：最新净值触发的业绩指标计算
 * 
 *   @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;


    tb_fund_performance = create_fund_performance();
    tb_fund_indicator = create_fund_indicator();
    tb_fund_risk_stats = create_fund_risk_stats();
    tb_fund_riskadjret_stats = create_fund_riskadjret_stats();
    tb_fund_style_stats = create_fund_style_stats();

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


    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);
        generate_fund_indicator(fund_info, indicators, true, tb_fund_indicator);
        generate_fund_risk_stats(fund_info, indicators, true, tb_fund_risk_stats);
        generate_fund_riskadjret_stats(fund_info, indicators, true, tb_fund_riskadjret_stats);
        generate_fund_style_stats(fund_info, indicators, true, tb_fund_style_stats);

        i += batch_size;

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


    // saverisk_stats
    try {

        chg_columns_for_mysql(tb_fund_performance);
        save_table(tb_fund_performance, 'mfdb.fund_performance', true);

        chg_columns_for_mysql(tb_fund_indicator);
        save_table(tb_fund_indicator, 'mfdb.fund_indicator', true);

        chg_columns_for_mysql(tb_fund_risk_stats);
        save_table(tb_fund_risk_stats, 'mfdb.fund_risk_stats', true);

        chg_columns_for_mysql(tb_fund_riskadjret_stats);
        save_table(tb_fund_riskadjret_stats, 'mfdb.fund_riskadjret_stats', true);

        chg_columns_for_mysql(tb_fund_style_stats);
        save_table(tb_fund_style_stats, 'mfdb.fund_style_stats', true);

    } catch(ex) {

        //TODO: Log errors

        rt = ex;

    }
    
    return rt;
	
}