DolphinDB/modules/performanceDataPuller.dos

module fundit::performanceDataPuller

use fundit::sqlUtilities


/*
 *   通用取有更新的数据
 *   
 *   NOTE: isvalid = 0 的记录也会被返回
 * 
 *   Example: get_data_by_updatetime('mfdb.fund_performance_weekly', 2024.11.14T11:10:00, true);
 * 
 */
def get_data_by_updatetime(table_name, updatetime, isFromMySQL=true) {


    if(isFromMySQL == true) {

        s_query = "SELECT * FROM " + table_name + "
                   WHERE updatetime >= '" + updatetime + "'";
     
        conn = connect_mysql()
     
        t = odbc::query(conn, s_query)
     
        conn.close()

    } else {
        
    
    }

    return t
}

/*
 *  取有周收益更新的基金组合列表及其最早日期
 *  
 *  @param entity_type <STRING>: MF,HF,MI,FI
 *  @param entity_ids <VECTOR|STRING>
 * 
 *  Example: get_entity_list_by_weekly_return_updatetime('PF', NULL, 2024.11.20, true);
 */
def get_entity_list_by_weekly_return_updatetime(entity_type, entity_ids, updatetime, isFromMySQL=true) {

    tmp = get_performance_weekly_table_description(entity_type);

    s_entity_ids = ids_to_string(entity_ids);

    sql_entity_id = '';

    if(s_entity_ids != NULL) {
    	sql_entity_id = " AND " + tmp.sec_id_col[0] + " IN (" + s_entity_ids + ")";
    }

    sql_entity_id += iif(entity_type == 'PF', '', " AND " + tmp.sec_id_col[0] + " LIKE '" + tmp.prefix[0] + "%'");

    if(isFromMySQL == true) {

        s_query = "SELECT " + tmp.sec_id_col[0] + " AS entity_id, MIN(price_date) AS price_date 
                   FROM " + tmp.table_name[0] + "
                   WHERE isvalid = 1 " +
                     sql_entity_id + "
                     AND " + tmp.cumulative_nav_col[0] + " > 0
                     AND updatetime >= '" + updatetime$STRING + "'
                   GROUP BY " + tmp.sec_id_col[0] + "
                   ORDER BY " + tmp.sec_id_col[0] + ", price_date";
     
        conn = connect_mysql()
     
        t = odbc::query(conn, s_query)
     
        conn.close()

    } else {
        
    
    }

    return t
}


/*
 *  取有更新的最早周收益数据日期
 *  
 *  @return <DATE>
 * 
 *  Example: get_oldest_date_by_weekly_return_updatetime('MF', 2024.11.20, true);
 */
def get_oldest_date_by_weekly_return_updatetime(entity_type, updatetime, isFromMySQL=true) {

	rt = null;

    tmp = get_performance_weekly_table_description(entity_type);

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

    sql_entity_id = iif(entity_type == 'PF', '', " AND " + tmp.sec_id_col[0] + " LIKE '" + tmp.prefix[0] + "%'");

    if(isFromMySQL == true) {

        nav_table_name = tmp.table_name[0];
    
        s_query = "SELECT MIN(price_date) AS price_date 
                   FROM " + tmp.table_name[0] + "
                   WHERE updatetime >= '" + updatetime$STRING + "'" +
                     sql_entity_id + "
                     AND " + tmp.cumulative_nav_col[0] + " > 0
                   GROUP BY " + tmp.sec_id_col[0];

        conn = connect_mysql()
     
        t = odbc::query(conn, s_query)
     
        conn.close()

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

		rt = t[0].price_date;


    } else {
        
    
    }

    return rt;
}


/*
 *   通用取周净值
 * 
 *   Example: get_weekly_ret('MF', ['MF00003PW1'], 2024.06.01, today(), true);
 *            get_weekly_ret('PF', [166002], 2024.06.01, today(), true);
 */
def get_weekly_ret(entity_type, entity_ids, start_date=1990.01.01, end_date=2099.12.31, isFromMySQL=true){

    s_entity_ids = ids_to_string(entity_ids);

    if(s_entity_ids == null || s_entity_ids == '') return null;

    tmp = get_performance_weekly_table_description(entity_type);

    if(isFromMySQL == true) {

        s_query = "SELECT " + tmp.sec_id_col[0] + " AS entity_id, year_week, price_date, ret_1w AS ret, " + tmp.cumulative_nav_col[0] + " AS nav
                   FROM " + tmp.table_name[0] + "
                   WHERE " + tmp.sec_id_col[0] + " IN (" + s_entity_ids + ")
                      AND isvalid = 1
                      AND price_date BETWEEN '" + start_date + "' AND '" + end_date + "'
                   ORDER BY " + tmp.sec_id_col[0] + ", price_date";
     
        conn = connect_mysql()
     
        t = odbc::query(conn, s_query)
     
        conn.close()

    } else {
        
        tb_local = load_table_from_local("fundit", tmp.table_name[0])
    
    }

    return t
}

/*
 *   通用取周收益
 * 
 */
def get_entity_weekly_rets(entity_type, entity_info) {

    rets = null;

    if(entity_info.isVoid() || entity_info.size() == 0) return rets;

    very_old_date = '1990.01.01';

    // 简单起见，取整个数据集的最新日期（month-end production时取上月最后一天即可
    end_day = entity_info.price_date.max();

    return get_weekly_ret(entity_type, entity_info.entity_id, very_old_date, end_day, true);

}

/*
 *  通用取月收益
 *  
 *  @param entity_type <STRING>:
 *  @param entity_ids <VECTOR|STRING>:
 *  @param start_date <DATE>:
 *  @param end_date <DATE>:
 *  @param isFromMySQL <BOOL>:
 * 
 * 
 *  Example: get_monthly_ret('HF', ['HF000004KN','HF000103EU','HF00018WXG'], 2000.01.01, 2024.03.01, true);
 *           get_monthly_ret('PF', [166002], 2000.01.01, 2024.03.01, true);
 *           get_monthly_ret('PL', ['PL000000NS', 'PL00000ICF'], 2000.01.01, 2024.12.02, true);
 */
def get_monthly_ret(entity_type, entity_ids, start_date, end_date, isFromMySQL) {

    s_entity_ids = ids_to_string(entity_ids);

    if(s_entity_ids == null || s_entity_ids == '') return null;

    tmp = get_performance_table_description(entity_type);

    yyyymm_start = start_date.temporalFormat("yyyy-MM")
    yyyymm_end = end_date.temporalFormat("yyyy-MM")

    sql_extra_cols = iif(entity_type in ['PL', 'CO'], "curve_type, strategy, ", "");

    if(isFromMySQL == true) {

        s_query = "SELECT " + tmp.sec_id_col[0] + " AS entity_id, " + sql_extra_cols + "end_date, price_date, ret_1m AS ret, " + tmp.cumulative_nav_col[0] + " AS nav
                   FROM " + tmp.table_name[0] + "
                   WHERE " + tmp.sec_id_col[0] + " IN (" + s_entity_ids + ")
                      AND isvalid = 1
                      AND end_date BETWEEN '" + yyyymm_start + "' AND '" + yyyymm_end + "'
                   ORDER BY " + tmp.sec_id_col[0] + ", end_date";
     
        conn = connect_mysql()
     
        t = odbc::query(conn, s_query)
     
        conn.close()

    } else {
        
        tb_local = load_table_from_local("fundit", tmp.table_name[0])

        s_col = (sqlCol(tmp.sec_id_col[0]), sqlCol("end_date"), sqlColAlias(<ret_1m>, "ret"), sqlColAlias(<cumulative_nav>, "nav"), sqlCol("ret_ytd_a"), sqlCol("ret_incep_a"))
        // TODO: how to make the "fund_id" dynamicly decided by tmp.sec_id_col[0], then rename to "entity_id"?
        s_where = expr(<fund_id>, in, s_entity_ids.strReplace("'", "").split(","))
        
        t = sql(s_col, tb_local, s_where).eval()
    
    }

    return t
}

/*
 * 取无风险月度利率
 *
 * get_risk_free_rate(1990.01.01, today())
 */
def get_risk_free_rate(start_date, end_date) {
    
    return get_monthly_ret('MI', "'IN0000000M'", start_date, end_date, true);
}


/*
 *  取基金最新收益及净值
 *
 *  Example: get_entity_list_by_latest_return_updatetime('HF', ['HF000004KN','HF00018WXG'], 2024.01.01, true)
 *  		 get_entity_list_by_latest_return_updatetime('HF', NULL, 2024.11.01, true)
 */
def get_entity_list_by_latest_return_updatetime(entity_type, entity_ids, updatetime, isFromMySQL=true) {


    tmp = get_latest_performance_table_description(entity_type);

    s_entity_ids = ids_to_string(entity_ids);

    sql_entity_id = '';

    if(s_entity_ids != NULL) {
    	sql_entity_id = " AND " + tmp.sec_id_col[0] + " IN (" + s_entity_ids + ")";
    }

    sql_entity_id += iif(entity_type == 'PF', '', " AND " + tmp.sec_id_col[0] + " LIKE '" + tmp.prefix[0] + "%'");

    if(isFromMySQL == true) {

        s_query = "SELECT " + tmp.sec_id_col[0] + " AS entity_id, price_date, end_date
                   FROM " + tmp.table_name[0] + "
                   WHERE isvalid = 1 " +
                     sql_entity_id + "
                     AND " + tmp.cumulative_nav_col[0] + " > 0
                     AND updatetime >= '" + updatetime$STRING + "'
                   ORDER BY " + tmp.sec_id_col[0];
     
        conn = connect_mysql()
     
        t = odbc::query(conn, s_query)
     
        conn.close()

    } else {

        tb_local = load_table_from_local("fundit", "mfdb.fund_latest_nav_performance")

        s_col = sqlCol("*")

        s_where = expr(<fund_id>, in, entity_ids.strReplace("'", "").split(","))
        
        t = sql(s_col, tb_local, s_where).eval()

    }

    return t

}

/*
 * 通用取给定日期之后（含此日期）的净值
 * 
 * @param price_date <DATE/MONTH>
 *             
 * 
 *
 * Example: get_nav_by_price_date('HF', "'HF000004KN','HF00018WXG'", 2024.05.01, true);
 *          get_nav_by_price_date('MI', "'IN00000008','IN0000000M'", 2024.05.01, true);
 *          get_nav_by_price_date('PL', NULL, 2024.11.01, true);
 */
def get_nav_by_price_date(entity_type, entity_ids, price_date, isFromMySQL) {

    s_entity_ids = ids_to_string(entity_ids);

    tmp = get_nav_table_description(entity_type);
    
    if(s_entity_ids == null || s_entity_ids == '') sql_entity_id = '';
    else sql_entity_id = " AND " +  tmp.sec_id_col[0] + " IN (" + s_entity_ids + ")";

	if(entity_type in ('PL', 'CO')) {
		sql_price_date = " AND " + tmp.price_date_col[0] + " >= '" + price_date.month().temporalFormat('yyyy-MM') + "'";
	}
	else {
		sql_price_date = " AND " + tmp.price_date_col[0] + " >= '" + price_date.temporalFormat('yyyy-MM-dd') + "'";
	}

    if(isFromMySQL == true) {

        nav_table_name = tmp.table_name[0];
    
        s_query = "SELECT DISTINCT " + tmp.sec_id_col[0] + " AS entity_id, " + tmp.price_date_col[0] + ", " + tmp.cumulative_nav_col[0] + " AS cumulative_nav, " + tmp.nav_col[0] + " AS nav
                   FROM " + tmp.table_name[0] + "
                   WHERE isvalid = 1 " +
                     sql_entity_id + sql_price_date + "
                     AND " + tmp.cumulative_nav_col[0] + " > 0
                   ORDER BY " + tmp.sec_id_col[0] + ", " +  tmp.price_date_col[0];
    
        conn = connect_mysql();
    
        t = odbc::query(conn, s_query);
    
        conn.close();

    } else {
    
        tb_local = load_table_from_local("fundit", tmp.table_name[0])

        s_col = sqlCol("*")

        // TODO: how to make the "fund_id" dynamicly decided by tmp.sec_id_col[0]?
        s_where = [expr(<fund_id>, in, s_entity_ids.strReplace("'", "").split(",")), <price_date >= price_date>]
        
        t = sql(s_col, tb_local, s_where).eval()
    
    }

    return t

}


/*
 * 取指数因子点位
 *
 * get_index_nav_by_price_date("'IN00000008','FA00000WKG'", 2024.06.01)
 */
def get_index_nav_by_price_date(index_ids, price_date) {

    s_query = "SELECT index_id, price_date, close AS cumulative_nav
               FROM mfdb.market_indexes
               WHERE index_id IN (" + index_ids + ")
                 AND isvalid = 1
                 AND close > 0
                 AND price_date >= '" + price_date + "'
               UNION
               SELECT index_id AS index_id, price_date, index_value AS cumulative_nav
               FROM mfdb.indexes_ty_index
               WHERE index_id IN (" + index_ids + ")
                 AND isvalid = 1
                 AND index_value > 0
                 AND price_date >= '" + price_date + "'
               UNION
               SELECT factor_id AS index_id, price_date, factor_value AS cumulative_nav
               FROM pfdb.cm_factor_value
               WHERE factor_id IN (" + index_ids + ")
                 AND isvalid = 1
                 AND factor_value > 0
                 AND price_date >= '" + price_date + "'
               ORDER BY price_date"

    conn = connect_mysql()

    t = odbc::query(conn, s_query)

    conn.close()

    return t

}


/*
 *  取某时间后更新的各基金组合最早净值日期
 *
 *  @param entity_type <STRING>: MF, HF, EQ, CF, MI, TI, CI, FA, PF
 *  @param entity_ids <VECTOR|STRING>: NULL时取全量
 *  @param update_time <DATETIME>: all updates after this time
 *  @param isFromMySQL <BOOL>:
 *
 *  Example: get_entity_list_by_nav_updatetime('MF', ['MF00003PW1', 'MF00003PW2'], 2024.09.26, true);
 *           get_entity_list_by_nav_updatetime('HF', null, 2024.07.19T10:00:00, true);
 *           get_entity_list_by_nav_updatetime('PF', '166002,166114', 2024.06.20, true);
 *           get_entity_list_by_nav_updatetime('PL', NULL, 2024.10.20, true);
 * 
 */
def get_entity_list_by_nav_updatetime(entity_type, entity_ids, updatetime, isFromMySQL) {

    tmp = get_nav_table_description(entity_type);

    s_entity_ids = ids_to_string(entity_ids);

    sql_entity_id = '';

    if(s_entity_ids != NULL) {
    	sql_entity_id = " AND " + tmp.sec_id_col[0] + " IN (" + s_entity_ids + ")";
    }

    sql_extra_keys = iif(entity_type in ('PL', 'CO'), ", curve_type, strategy", "");

    if(isFromMySQL == true) {

        s_query = "SELECT " + tmp.sec_id_col[0] + " AS entity_id" + sql_extra_keys + ", MIN(" + tmp.price_date_col[0] + ") AS price_date 
                   FROM " + tmp.table_name[0] + "
                   WHERE isvalid = 1 " +
                     sql_entity_id + "
                     AND " + tmp.cumulative_nav_col[0] + " > 0
                     AND updatetime >= '" + updatetime$STRING + "'
                   GROUP BY " + tmp.sec_id_col[0] + sql_extra_keys + "
                   ORDER BY " + tmp.sec_id_col[0] + ", " + tmp.price_date_col[0];
    
        conn = connect_mysql();
    
        t = odbc::query(conn, s_query);
    
        conn.close();

    } else {
    	//TODO
    }

    return t

}



/*
 *  取基金证券从某日期后的所有净值及前值
 *  
 *  @param entity_type <STRING>: MF, HF, EQ, CF, MI, TI, CI, FA, PF
 *  @param freq <STRING>: m, w, d
 *  @param pre_nav_incld <INT>: 0- no pre_nav; 1- pre_nav only; 2- pre_nav + afters
 *  @param json_query <JSON>: [{sec_id:xxx, price_date: yyyy-mm-dd}]
 * 
 *  Example: get_nav_for_return_calculation('MI', 'm', '[{"sec_id": "IN00000008","price_date": "2004-12-31"}, {"sec_id": "IN00000077","price_date": "2003-12-31"}]', 1)
 *  
 */
def get_nav_for_return_calculation(entity_type, freq, json_query, pre_nav_incld=2) {

    s_query = "CALL pfdb.sp_get_nav_for_return_cal('" + entity_type + "', '" + freq + "', " + pre_nav_incld + ", '" + json_query + "')";

    conn = connect_mysql();

    t = odbc::query(conn, s_query);

    conn.close();

    return t;
}



/*
 *  取基金经理/公司从某日期后的所有净值及前值
 *  
 *  @param entity_type <STRING>: PL, CO
 *  @param json_query <JSON>: [{entity_id:xxx, curve_type:1, strategy:0, end_date: yyyy-mm}]
 *  @param pre_nav_incld <INT>: 0- no pre_nav; 1- pre_nav only; 2- pre_nav + afters
 *  @param freq <STRING>: m- monthly, w-weekly
 * 
 *  Example: get_mc_nav_for_return_calculation('PL', '[{"entity_id":"PL000000NS", "curve_type":"4", "strategy":"0", "effective_date":"2024-07"}]', 2);
 *           get_mc_nav_for_return_calculation('CO', '[{"entity_id":"CO00000017", "curve_type":"4", "strategy":"0", "effective_date":"202406"}]', 1, 'w');
 *  
 */
def get_mc_nav_for_return_calculation(entity_type, json_query, pre_nav_incld=2, freq='m') {

    s_query = "CALL pfdb.sp_get_mc_nav_for_return_cal('" + entity_type + "', '" + freq + "', " + pre_nav_incld + ", '" + json_query + "')";

    conn = connect_mysql();

    t = odbc::query(conn, s_query);

    conn.close();

    return t;
}


/*
 *   取主基准和BFI的历史月收益率
 *   
 *   @param benchmarks <TABLE>: entity-benchmark 的对应关系表
 *   @param end_day <DATE>: 收益的截止日期
 * 
 *   @return <TABLE>: benchmark_id, end_date, ret
 *   
 */
def get_benchmark_return(benchmarks, end_day) {

    s_index_ids = '';
    s_factor_ids = '';

    if(benchmarks.isVoid() || benchmarks.size() == 0) { return null; }

    // 前缀为 IN 的 benchmark id
    t_index_id = SELECT DISTINCT benchmark_id FROM benchmarks WHERE benchmark_id LIKE 'IN%';;
    s_index_ids = iif(isVoid(t_index_id), "",  "'" + t_index_id.benchmark_id.concat("','") + "'");

    // 前缀为 FA 的 benchmark id
    t_factor_id = SELECT DISTINCT benchmark_id FROM benchmarks WHERE benchmark_id LIKE 'FA%';
    s_factor_ids = iif(isVoid(t_factor_id), "",  "'" + t_factor_id.benchmark_id.concat("','") + "'");


    // 目前指数的月度业绩存在 fund_performance 表
    t_bmk = SELECT entity_id AS benchmark_id, temporalParse(end_date, 'yyyy-MM') AS end_date, ret FROM get_monthly_ret('MI', s_index_ids, 1990.01.01, end_day, true);

    // 而因子的月度业绩存在 cm_factor_performance 表
    INSERT INTO t_bmk SELECT entity_id AS factor_id, temporalParse(end_date, 'yyyy-MM') AS end_date, ret FROM get_monthly_ret('FA', s_factor_ids, 1990.01.01, end_day, true);

	return t_bmk;
}

/*
 *  将十位标准字符型ID分门别类
 * 
 *  @param entity_ids <VECTOR>: vector of ids
 *  @return <DICT>: entity_type - vector of ids
 *  
 *  NOTE: "IN"打头的ID无法判断是市场指数还是图译指数，所以 MI, FI 都会被返回
 *  
 *  Example: check_universe(['MF00003PW1','HF12345678','MF00003PW2','IN00000008']);
 */
def check_universe (entity_ids) {

   	v_id = array(STRING);
   	dic = dict(STRING, ANY);

    if(entity_ids.isVoid() || entity_ids.size() == 0) return dic;

   	for(r in get_entity_id_info()) {

   		v_id = entity_ids[like(entity_ids, r.prefix + '%') && strlen(entity_ids)==10];

		if(v_id.size() > 0) {
			dic[r.entity_type] = v_id;
		}
   	}

	return dic;
}

/*
 *   通用取收益数据
 *   
 *   @param entity_type <STRING>:
 *   @param entity_ids <VECTOR>
 *   @param freq <STRING>: m, w, d
 *   @param start_day <DATE>
 *   @param end_day <DATE>
 *   @param isFromMySQL <BOOL>
 * 
 *   Example: get_entity_return('MF', ['MF00003PW1', 'MF00003PW2'], 'w', 2024.02.01, 2024.09.30, true);
 *            get_entity_return(NULL, ['FA00000VML', 'FA00000VMM', 'FA00000VMN', 'FA00000VMO', 'IN0000007G'], 'w');
 *            get_entity_return('PF', [166002], 'm');
 */
def get_entity_return(entity_type, entity_ids, freq='m', start_day=1990.01.01, end_day=2099.12.31, isFromMySQL=true) {

	t_ret = table(1000:0, ['entity_id', 'effective_date', 'price_date', 'ret', 'nav'], [iif(entity_type=='PF', INT, SYMBOL), STRING, DATE, DOUBLE, DOUBLE]);

	d = dict(STRING, ANY);

	if(entity_type != null) d[entity_type] = entity_ids;
	else
		d = check_universe(entity_ids);

	for(univ in d.keys()) {

		if(freq == 'm') {
			
			t = SELECT entity_id, end_date$STRING AS effective_date, price_date, ret, nav
			    FROM get_monthly_ret(univ, d[univ], start_day, end_day, isFromMySQL);
	
			t_ret.tableInsert(t);
			
		} else if(freq == 'w') {
	
			t = SELECT entity_id, year_week$STRING AS effective_date, price_date, ret, nav
			    FROM get_weekly_ret(univ, d[univ], start_day, end_day, isFromMySQL);
	
			t_ret.tableInsert(t);
			
		} else { // 缺省是日收益
	
			s_json = table(d[univ] AS sec_id, take(start_day, d[univ].size()) AS price_date).toStdJson();
	            
			// 取基金组合在包括各自的某净值日期的前值及之后的所有净值
			tb_nav = get_nav_for_return_calculation(univ, 'd', s_json);
			if(!tb_nav.isVoid() && tb_nav.size() > 0) {
	
				tb_nav.rename!('sec_id', 'entity_id').sortBy!(['entity_id', 'price_date'], [1, 1]);
	
				t = SELECT entity_id, price_date$STRING AS effective_date, price_date, cumulative_nav.ratios()-1 AS ret, cumulative_nav AS nav
				    FROM tb_nav
				    WHERE price_date <= end_day CONTEXT BY entity_id;

				t_ret.tableInsert(t);
			}
		}
	}

	return t_ret;
	
}

/*
 *  取持有基金净值更新的组合列表
 *  
 *  TODO: 需要跑3分钟，待优化
 * 
 *  Example: get_portfolio_list_by_fund_nav_updatetime([166002,166114], 2024.10.28, true);
 */
def get_portfolio_list_by_fund_nav_updatetime(portfolio_ids, updatetime, isFromMySQL) {

    t = null;

    s_entity_ids = ids_to_string(portfolio_ids);

    if(isFromMySQL == true) {

        s_query = "CALL pfdb.sp_get_portfolios_to_cal_nav(" + iif(s_entity_ids.isNull(), 'NULL', "'" + s_entity_ids + "'") + ",'" + updatetime + "')";

    conn = connect_mysql();

    t = odbc::query(conn, s_query);

    conn.close();

    }

    return t
}

/*
 *   根据指数净值更新日期，取受影响的合成因子列表
 * 
 *   Modify  20241218   去掉 factor_type = 5的限制，支持非BFI因子使用 cm_factor_index_map
 * 
 *   Example: get_bfi_factor_list_by_index_nav_updatetime(['FA00000VMH','FA00000VMK','FA00000SMB'], 2024.11.08, true);
 *            get_bfi_factor_list_by_index_nav_updatetime(NULL, NULL, true);
 */
def get_bfi_factor_list_by_index_nav_updatetime(factor_ids, updatetime,  isFromMySQL) {

    t = null;

    s_entity_ids = ids_to_string(factor_ids);
    sql_entity_id = iif(s_entity_ids == NULL || s_entity_ids == '', '', " AND fi.factor_id IN (" + s_entity_ids + ")");

    sql_updatetime = iif(updatetime == NULL, '', " AND mi.updatetime > '" + updatetime + "'");

    if(isFromMySQL == true) {

        s_query = "SELECT fi.factor_id, GREATEST(MIN(mi.price_date), fi.inception_date) AS first_cal_date, LEAST(MAX(mi.price_date), CURRENT_DATE) AS latest_cal_date 
				   FROM pfdb.cm_factor_information fi
				   INNER JOIN pfdb.cm_factor_index_map map ON fi.factor_id = map.factor_id
				   INNER JOIN mfdb.market_indexes mi ON map.index_id = mi.index_id
				   WHERE fi.isvalid = 1" +
				     sql_entity_id + "
				     AND map.isvalid = 1
				     AND mi.isvalid = 1
				     AND mi.close > 0 " +
				     sql_updatetime + "
				   GROUP BY fi.factor_id";

	    conn = connect_mysql();
	
	    t = odbc::query(conn, s_query);
	
	    conn.close();

    }

    return t
}

/*
 *   取固定权重的组合持仓，包括BFI因子，日再平衡组合等
 * 
 *   Example: get_fixed_weight_portfolio_holding('PF', [145589, 170904]);
 *            get_fixed_weight_portfolio_holding('FA', ['FA00000VMH', 'FA00000VMI']);
 */
def get_fixed_weight_portfolio_holding(entity_type, entity_ids) {

    t = null;

    s_query = '';

    s_entity_ids = ids_to_string(entity_ids);

	if(entity_type == 'FA') {

	    sql_entity_id = iif(s_entity_ids == NULL || s_entity_ids == '', '', " AND factor_id IN (" + s_entity_ids + ")");
	
	    s_query = "SELECT factor_id AS entity_id, holding_date, index_id AS sec_id, weight, 2 AS entity_type
				   FROM pfdb.cm_factor_index_map
				   WHERE isvalid = 1" +
				     sql_entity_id;

	} else if(entity_type == 'PF') {

	    sql_entity_id = iif(s_entity_ids == NULL || s_entity_ids == '', '', " AND portfolio_id IN (" + s_entity_ids + ")");
	
	    s_query = "SELECT portfolio_id AS entity_id, effective_date AS holding_date, entity_id AS sec_id, weight, entity_type
				   FROM pfdb.pf_portfolio_rebalance_weights
				   WHERE isvalid = 1" +
				     sql_entity_id;
		
	}

	if( s_query == '') return t;

    conn = connect_mysql();

    t = odbc::query(conn, s_query);

    conn.close();

    return t;
}

/*
 *  通用取Json中指定的证券当日净值
 * 
 *
 *  Example: get_entity_nav_by_date('PF', '[{"entity_id": 166002,"price_date": "2024.10.25"},{"entity_id": 166114,"price_date": "2024.03.13"}]', true);
 */
def get_entity_nav_by_date(entity_type, s_json, isFromMySQL=true) {

    t = null;

	desc = get_nav_table_description(entity_type)[0];

    if(isFromMySQL == true) {

        s_query = "SELECT t.entity_id, nav.price_date, nav." + desc.cumulative_nav_col + " AS cumulative_nav, nav." + desc.nav_col + " AS nav
                   FROM JSON_TABLE ( '" + s_json + "', '$[*]'
                           COLUMNS ( entity_id " + iif(entity_type=='PF', 'INT', 'VARCHAR(10)') + " PATH '$.entity_id',
                                     price_date DATE PATH '$.price_date' ) ) t
                   LEFT JOIN " + desc.table_name + " nav ON t.entity_id = nav." + desc.sec_id_col + " AND t.price_date = nav.price_date
                   WHERE nav.isvalid = 1
                     AND nav." + desc.cumulative_nav_col + " > 0
                   ORDER BY t.entity_id;";

        conn = connect_mysql();

        t = odbc::query(conn, s_query);
     
        conn.close();
    }

    return t;
	
}


/*
 *  同步专用：取Json中指定的证券当日净值及isvalid, createtime、updatetime
 * 
 *  TODO: need adding index on json table
 *
 *  Example: sync_entity_nav_by_date('MF', '[{"entity_id": "MF00003PW1","price_date": "2024.10.25"},{"entity_id": "MF00003PW2","price_date": "2024.03.13"}]');
 *           sync_entity_nav_by_date('MI', '[{"entity_id": "IN00000008","price_date": "2000.01.25"}]');
 *  		 sync_entity_nav_by_date('PF', '[{"entity_id": 166002,"price_date": "2024.10.25"},{"entity_id": 166114,"price_date": "2024.03.13"}]');
 */
def sync_entity_nav_by_date(entity_type, s_json) {

    t = null;

    s_query = "CALL pfdb.sp_sync_nav_for_dolphin('" + entity_type + "','" + s_json + "');";

    conn = connect_mysql();

    t = odbc::query(conn, s_query);
 
    conn.close();

    return t;
}

/*
 *  通用取Json中指定日期后(含当日)的证券收益
 *  
 *  @param freq <STRING>: m, w
 * 
 *
 *  Example: get_entity_ret_by_date('PF', '[{"entity_id": 166002,"price_date": "2024.10.25"},{"entity_id": 166114,"price_date": "2024.03.13"}]', 'm', true);
 *           get_entity_ret_by_date('MF', '[{"entity_id": "MF00003PW1","price_date": "2023.12.28"}]', 'w', true);
 */
def get_entity_ret_by_date(entity_type, s_json, freq, isFromMySQL=true) {

    t = null;

    if(isFromMySQL == true) {

        s_query = "CALL pfdb.sp_get_return_by_json('" + entity_type + "', '" + freq + "', '" + s_json + "');";

        conn = connect_mysql();

        t = odbc::query(conn, s_query);
     
        conn.close();
    }

    return t;
	
}

/*
 *   取月度指标表
 *   
 *   @param table_name <STRING>: 指标表名
 *   @param end_date <MONTH>
 *   @param isFromMySQL <BOOL>
 * 
 *   Example: get_monthly_indicator_data('mfdb.fund_performance', 2024.09M, true);
 */
def get_monthly_indicator_data(table_name, end_date, isFromMySQL=true) {

    t = null;

    s_end_date_sql = iif(end_date.isNull(), '', " AND end_date = '" + end_date.temporalFormat('yyyy-MM') + "'" );

    if(isFromMySQL == true) {

        s_query = "SELECT * FROM " + table_name + " WHERE isvalid = 1 " + s_end_date_sql;

        conn = connect_mysql();
     
        t = odbc::query(conn, s_query);
     
        conn.close();
    }

    return t;
}

/*
 *  取 pf_fund_indicator_ranking 表
 *  
 *  
 *  Example: get_fund_indicator_ranking("'MF00003PW1'", 2024.09M, 102, true);
 *           get_fund_indicator_ranking(NULL, 2023.09M, [1, 3], true);
 */
def get_fund_indicator_ranking(fund_ids, end_date, strategy, isFromMySQL=true) {

    t = null;

    s_entity_ids = ids_to_string(fund_ids);
    sql_entity_id = '';
    if(s_entity_ids != NULL) sql_entity_id = " AND fund_id IN (" + s_entity_ids + ")";

    s_strategy_ids = ids_to_string(strategy);
    sql_strategy_id = '';
    if(s_strategy_ids != NULL) sql_strategy_id = " AND strategy IN (" + s_strategy_ids + ")";


    if(isFromMySQL == true) {

        s_query = "SELECT * 
                   FROM pfdb.pf_fund_indicator_ranking 
                   WHERE isvalid = 1 
                     AND end_date = '" + end_date.temporalFormat('yyyy-MM') + "'" +
                     sql_strategy_id +
                     sql_entity_id;

        conn = connect_mysql();
     
        t = odbc::query(conn, s_query);
     
        conn.close();
    }

    return t;

}


/*
 *  取 pf_fund_indicator_substrategy_ranking 表
 *  
 *  
 *  Example: get_fund_indicator_substrategy_ranking("'MF00003PW1'", 2024.09M, 23, true);
 *           get_fund_indicator_substrategy_ranking(NULL, 2023.09M, [22, 23], true);
 */
def get_fund_indicator_substrategy_ranking(fund_ids, end_date, substrategy, isFromMySQL=true) {

    t = null;

    s_entity_ids = ids_to_string(fund_ids);
    sql_entity_id = '';
    if(s_entity_ids != NULL) sql_entity_id = " AND fund_id IN (" + s_entity_ids + ")";

    s_strategy_ids = ids_to_string(substrategy);
    sql_strategy_id = '';
    if(s_strategy_ids != NULL) sql_strategy_id = " AND substrategy IN (" + s_strategy_ids + ")";


    if(isFromMySQL == true) {

        s_query = "SELECT * 
                   FROM pfdb.pf_fund_indicator_substrategy_ranking 
                   WHERE isvalid = 1 
                     AND end_date = '" + end_date.temporalFormat('yyyy-MM') + "'" +
                     sql_strategy_id +
                     sql_entity_id;

        conn = connect_mysql();
     
        t = odbc::query(conn, s_query);
     
        conn.close();
    }

    return t;

}


/*
 *  取 pf_fund_bfi_bm_indicator_ranking 表
 *  
 *  
 *  Example: get_fund_bfi_bm_indicator_ranking("'MF00003PW1'", 2024.09M, NULL, true);
 *  
 */
def get_fund_bfi_bm_indicator_ranking(fund_ids, end_date, factor_ids, isFromMySQL=true) {

    t = null;

    s_entity_ids = ids_to_string(fund_ids);
    sql_entity_id = '';
    if(s_entity_ids != NULL) sql_entity_id = " AND fund_id IN (" + s_entity_ids + ")";

    s_factor_ids = ids_to_string(factor_ids);
    sql_factor_id = '';
    if(s_factor_ids != NULL) sql_factor_id = " AND factor_id IN (" + s_factor_ids + ")";


    if(isFromMySQL == true) {

        s_query = "SELECT * 
                   FROM pfdb.pf_fund_bfi_bm_indicator_ranking 
                   WHERE isvalid = 1 
                     AND end_date = '" + end_date.temporalFormat('yyyy-MM') + "'" +
                     sql_factor_id +
                     sql_entity_id;

        conn = connect_mysql();
     
        t = odbc::query(conn, s_query);
     
        conn.close();
    }

    return t;
}

/*
 *  取算相关性所需要的指数/因子ID
 * 
 *  NOTE: 与 Java 使用的逻辑相同， 在cm_class_asset_index中有当月performance的指数加上 CSI5 和 Cap3Style 的 factor
 *        唯一的不同是将历史已经停止维护的老指数筛掉（必须有上月的指数收益）
 *
 *  Example: get_correlation_index_list();
 */
def get_correlation_index_list() {

    s_query = "SELECT a.fund_id AS entity_id
    		   FROM mfdb.fund_performance AS a
          	   LEFT JOIN pfdb.cm_class_asset_index AS b ON a.fund_id = b.index_id 
	           WHERE a.end_date = LEFT(DATE_SUB(CURRENT_DATE, INTERVAL 1 MONTH), 7)
	           AND a.fund_id LIKE 'IN%' 
	           AND b.isvalid = 1 
	           ORDER BY a.fund_id ASC";

    conn = connect_mysql();
 
    t = odbc::query(conn, s_query);
 
    conn.close();

	INSERT INTO t VALUES (['FA00000VML','FA00000VMM','FA00000VMN','FA00000VMO','FA00000WKG','FA00000WKH']);

	return t;
}

/*
 *  取BFI所需要的因子ID
 * 
 *  TODO: 大盘风格、小盘风格 (FA00000WKG, FA00000WKH) 没有category_group_id & order_id, 在哪里用？
 */
def get_bfi_index_list() {

	s_query = "SELECT factor_id, factor_name, category_group_id, maximum_num, order_id
    		   FROM pfdb.cm_factor_information
	           WHERE isvalid = 1
	             AND ( factor_type = 5 OR factor_id IN ('FA00000SMB', 'FA00000VMG') )
	           ORDER BY order_id ASC";

    conn = connect_mysql();
 
    t = odbc::query(conn, s_query);
 
    conn.close();

	return t;
}


/*
 *  取基金分类平均的周收益
 *  
 *  TODO: 需要跑1分钟，待优化
 * 
 *  Example: get_category_avg_weekly_return('bfi', 2024.09.28, 5, 30, true);
 *           get_category_avg_weekly_return('strategy', 2014.06.27, 5, 30, true);
 */
def get_category_avg_weekly_return(category_type, begin_day, trim_pct=5, min_cnt=30, isFromMySQL=true) {

    t = null;

    if(isFromMySQL == true) {

        s_query = "CALL pfdb.sp_get_category_avg_weekly_return('" + category_type + "', '" + begin_day + "', " + trim_pct + ", " + min_cnt + ")";

	    conn = connect_mysql();
	
	    t = odbc::query(conn, s_query);
	
	    conn.close();

    }

    return t
}


/*
 *  取基金经理或公司月收益
 *  
 * 
 *  Example: get_mc_average_return('PL', 'm', '[{"entity_id": "PL000000NS","effective_date": "2024-07"},{"entity_id": "PL00000ICF","effective_date": "2023-12"}]', 0, 1, true);
 *           get_mc_average_return('CO', 'w', '[{"entity_id": "CO00000017","effective_date": "202407"},{"entity_id": "CO0001003W","effective_date": "202306"}]', 0, 1, true);
 */
def get_mc_average_return(entity_type, freq, json_query, trim_pct=0, min_cnt=1, isFromMySQL=true) {

    t = null;

    if(isFromMySQL == true) {

        s_query = "CALL pfdb.sp_get_mc_avg_return('" + entity_type + "', '" + freq + "', '" + json_query + "', " + trim_pct + ", " + min_cnt + ")";

	    conn = connect_mysql();
	
	    t = odbc::query(conn, s_query);
	
	    conn.close();

    }

    return t
}


/*
 *   取有基金月收益更新的基金公司列表
 *   
 *   NOTE: 月收益表 isvalid = 0 的记录也会被返回
 * 
 *   Example: get_company_list_by_fund_updatetime(2024.11.14 11:10:00, 'm', true);
 *            get_company_list_by_fund_updatetime(2024.12.24 11:10:00, 'w', true);
 */
def get_company_list_by_fund_updatetime(updatetime, freq='m', isFromMySQL=true) {


    if(isFromMySQL == true) {

		if(freq == 'm') {
	        s_query = "SELECT ci.company_id, MIN(fp.end_date) AS effective_date
	                   FROM mfdb.company_information ci
	                   INNER JOIN mfdb.fund_information fi ON ci.company_id = fi.advisor_id
	                   INNER JOIN mfdb.fund_performance fp ON fi.fund_id = fp.fund_id
	                   WHERE ci.isvalid = 1
	                     AND fi.isvalid = 1
	                     AND fp.updatetime >= '" + updatetime + "'
	                   GROUP BY ci.company_id;";
		} else {
	        s_query = "SELECT ci.company_id, MIN(fp.year_week) AS effective_date
	                   FROM mfdb.company_information ci
	                   INNER JOIN mfdb.fund_information fi ON ci.company_id = fi.advisor_id
	                   INNER JOIN mfdb.fund_performance_weekly fp ON fi.fund_id = fp.fund_id
	                   WHERE ci.isvalid = 1
	                     AND fi.isvalid = 1
	                     AND fp.updatetime >= '" + updatetime + "'
	                   GROUP BY ci.company_id;";
		}
		
        conn = connect_mysql();
     
        t = odbc::query(conn, s_query);
     
        conn.close();

    } else {
        
    
    }

    return t
}


/*
 *   取有基金月收益更新的基金经理列表
 *   
 *   NOTE: 月收益表 isvalid = 0 的记录也会被返回
 * 
 *   Example: get_manager_list_by_fund_updatetime(2024.11.01, 'm', true);
 *            get_manager_list_by_fund_updatetime(2024.12.12, 'w', true);
 * 
 */
def get_manager_list_by_fund_updatetime(updatetime, freq='m', isFromMySQL=true) {

    if(isFromMySQL == true) {

		if(freq == 'm') {
	        s_query = "SELECT mi.personnel_id AS manager_id, MIN(fp.end_date) AS effective_date
	                   FROM mfdb.personnel_information mi
	                   INNER JOIN mfdb.fund_manager_mapping fmp ON mi.personnel_id = fmp.fund_manager_id
	                   INNER JOIN mfdb.fund_information fi ON fmp.fund_id = fi.fund_id
	                   INNER JOIN mfdb.fund_performance fp ON fi.fund_id = fp.fund_id
	                   WHERE mi.isvalid = 1
	                     AND fmp.isvalid = 1
	                     AND fi.isvalid = 1
	                     AND fp.updatetime >= '" + updatetime + "'
	                     AND fp.price_date <= IFNULL(fmp.management_end_date, CURRENT_DATE)
	                   GROUP BY mi.personnel_id;";
		} else {
	        s_query = "SELECT mi.personnel_id AS manager_id, MIN(fp.year_week) AS effective_date
	                   FROM mfdb.personnel_information mi
	                   INNER JOIN mfdb.fund_manager_mapping fmp ON mi.personnel_id = fmp.fund_manager_id
	                   INNER JOIN mfdb.fund_information fi ON fmp.fund_id = fi.fund_id
	                   INNER JOIN mfdb.fund_performance_weekly fp ON fi.fund_id = fp.fund_id
	                   WHERE mi.isvalid = 1
	                     AND fmp.isvalid = 1
	                     AND fi.isvalid = 1
	                     AND fp.updatetime >= '" + updatetime + "'
	                     AND fp.price_date <= IFNULL(fmp.management_end_date, CURRENT_DATE)
	                   GROUP BY mi.personnel_id;";
		}
     
        conn = connect_mysql();;
     
        t = odbc::query(conn, s_query);
     
        conn.close();

    } else {
        
    
    }

    return t;
}

/*
 *   取基金经理/公司月度业绩表更新的收益日期
 * 
 *   Example: get_mc_performance_by_updatetime('PL', 2024.11.01, true);
 *   		  get_mc_performance_by_updatetime('CO', 2024.11.01, true);
 */
def get_mc_performance_by_updatetime(entity_type, updatetime, isFromMySQL=true) {

	des = get_performance_table_description(entity_type)[0];

    if(isFromMySQL == true) {

        s_query = "SELECT " + des.sec_id_col + " AS entity_id, curve_type, strategy, MIN(fp.end_date) AS end_date, MIN(fp.price_date) AS price_date
                   FROM " + des.table_name + " fp
                   WHERE fp.isvalid = 1
                     AND fp.updatetime >= '" + updatetime + "'
                   GROUP BY fp. " + des.sec_id_col + ", curve_type, strategy
                   ORDER BY fp." + des.sec_id_col + ", curve_type, strategy;";
     
        conn = connect_mysql();
     
        t = odbc::query(conn, s_query);
     
        conn.close();

    } else {
    
    }

    return t;
}