module fundit::operationDataPuller
use fundit::sqlUtilities
/*
* 取所有一级策略
*
*/
def get_strategy_list() {
s_query = "SELECT strategy_id, strategy, rasie_type AS raise_type FROM mfdb.d_strategy WHERE isvalid = 1";
conn = connect_mysql()
t = odbc::query(conn, s_query)
conn.close()
return t
}
/*
* 取所有二级策略
*
*/
def get_substrategy_list() {
s_query = "SELECT substrategy_id, substrategy, raise_type FROM mfdb.d_substrategy WHERE isvalid = 1";
conn = connect_mysql()
t = odbc::query(conn, s_query)
conn.close()
return t
}
/*
* 取有效基金基本信息
*
* Example: get_fund_info("'HF000004KN','HF00018WXG'");
* get_fund_info(null);
*
*/
def get_fund_info(fund_ids) {
s_entity_ids = ids_to_string(fund_ids);
s_entity_sql = iif(s_entity_ids == NULL, '', " AND fi.fund_id IN (" + s_entity_ids + ")");
s_query = "SELECT fi.fund_id, fi.inception_date, fi.primary_benchmark_id AS benchmark_id, IFNULL(fi.initial_unit_value, 1) AS ini_value, fs.strategy, fs.substrategy, fi.raise_type, fi.p_fund_id
FROM mfdb.fund_information fi
INNER JOIN mfdb.fund_strategy fs ON fi.fund_id = fs.fund_id
WHERE fs.isvalid = 1
AND fi.isvalid = 1" +
s_entity_sql + "
ORDER BY fi.fund_id"
conn = connect_mysql()
t = odbc::query(conn, s_query)
conn.close()
return t
}
/*
* 取有效指数基本信息
*
* Example: get_index_info("'IN00000008','IN000002GE'");
* get_index_info(null);
*
*/
def get_index_info(index_ids) {
s_entity_ids = ids_to_string(index_ids);
s_entity_sql = iif(s_entity_ids == NULL || s_entity_ids == '', '', " AND fi.index_id IN (" + s_entity_ids + ")");
s_query = "SELECT fi.index_id, fi.inception_date, NULL AS benchmark_id, IFNULL(fi.index_initial_value, 1) AS ini_value, fi.index_code, fi.index_type_id
FROM mfdb.indexes_profile fi
WHERE fi.isvalid = 1" +
s_entity_sql + "
ORDER BY fi.index_id";
conn = connect_mysql();
t = odbc::query(conn, s_query);
conn.close();
return t;
}
/*
* 取组合有效信息
*
* NOTE: portfolio 的 strategy 统一为公募混合基金102， sub_strategy 用 sub_type (哪里维护的？）
*
* Example: get_portfolio_info('166002,166114');
* get_portfolio_info(NULL);
*
*/
def get_portfolio_info(portfolio_ids) {
s_entity_ids = ids_to_string(portfolio_ids);
s_entity_sql = iif(s_entity_ids == NULL || s_entity_ids == '', '', " AND cpm.id IN (" + s_entity_ids + ")");
s_query = "SELECT cpm.id AS portfolio_id, cpm.userid, cpm.customer_id, cpm.inception_date, 1 AS ini_value,
cpm.portfolio_source, cpm.portfolio_type, 102 AS strategy, sub_type AS substrategy, u.org_id
FROM pfdb.`pf_customer_portfolio_map` cpm
INNER JOIN pfdb.cm_user u ON cpm.userid = u.userid
WHERE cpm.isvalid = 1
AND u.isvalid = 1" +
s_entity_sql + "
ORDER BY cpm.id"
conn = connect_mysql()
t = odbc::query(conn, s_query)
conn.close()
return t
}
/*
* 取私有基金有效信息
*
* Example: get_cus_fund_info(['CF0000005V','CF000000CE']);
* get_cus_fund_info(NULL);
*
*/
def get_cus_fund_info(fund_ids) {
s_entity_ids = ids_to_string(fund_ids);
s_entity_sql = iif(s_entity_ids == NULL || s_entity_ids == '', '', " AND fi.fund_id IN (" + s_entity_ids + ")");
s_query = "SELECT fi.fund_id, fi.userid, fi.inception_date, IFNULL(fi.primary_benchmark_id, 'IN00000008') AS benchmark_id,
IFNULL(initial_unit_value, 1) AS ini_value, raise_type, strategy, substrategy
FROM pfdb.pf_cus_fund_information fi
INNER JOIN pfdb.cm_user u ON fi.userid = u.userid
WHERE fi.isvalid = 1
AND u.isvalid = 1" +
s_entity_sql + "
ORDER BY fi.fund_id"
conn = connect_mysql()
t = odbc::query(conn, s_query)
conn.close()
return t
}
/*
* 取因子有效信息
*
* Example: get_factor_info(['FA00000VNB','FA00000VMJ']);
* get_factor_info(NULL);
*
*/
def get_factor_info(fund_ids) {
s_entity_ids = ids_to_string(fund_ids);
s_entity_sql = iif(s_entity_ids == NULL || s_entity_ids == '', '', " AND fi.factor_id IN (" + s_entity_ids + ")");
s_query = "SELECT fi.factor_id, fi.factor_type, fi.inception_date, NULL AS benchmark_id, IFNULL(initial_value, 1) AS ini_value
FROM pfdb.cm_factor_information fi
WHERE fi.isvalid = 1 " +
s_entity_sql + "
ORDER BY fi.factor_id"
conn = connect_mysql()
t = odbc::query(conn, s_query)
conn.close()
return t
}
/*
* 取基金组合基础有效信息
*
* Example: get_entity_info('HF', ['HF000004KN','HF000103EU','HF00018WXG']);
* get_entity_info('PF', '166002,166114');
* get_entity_info('MI', NULL);
*/
def get_entity_info(entity_type, entity_ids) {
t = null;
s_entity_ids = ids_to_string(entity_ids);
if(entity_type == 'MF' || entity_type == 'HF') {
t = get_fund_info(s_entity_ids);
t.rename!('fund_id', 'entity_id');
} else if(entity_type == 'PF') {
t = get_portfolio_info(s_entity_ids);
t.rename!('portfolio_id', 'entity_id');
} else if(entity_type IN ['MI', 'FI']) {
t = get_index_info(s_entity_ids);
t.rename!('index_id', 'entity_id');
} else if(entity_type == 'CF') {
t = get_cus_fund_info(s_entity_ids);
t.rename!('fund_id', 'entity_id');
} else if(entity_type == 'FA') {
t = get_factor_info(s_entity_ids);
t.rename!('factor_id', 'entity_id');
}
return t;
}
/*
* 取某时间段的基金主基准
* NOTE: 目前数据库里只存最新的基准，以后很可能会支持时间序列
*
* Example: get_fund_primary_benchmark("'MF00003PW2', 'MF00003PW1', 'MF00003PXO'", '1990-01', '2024-06');
*/
def get_fund_primary_benchmark(fund_ids, month_start, month_end) {
s_query = "SELECT fund_id, primary_benchmark_id AS benchmark_id, inception_date
FROM mfdb.fund_information
WHERE fund_id IN (" + fund_ids + ")
AND isvalid = 1;";
conn = connect_mysql();
t = odbc::query(conn, s_query);
conn.close();
t.addColumn('end_date', MONTH);
m_start = temporalParse(month_start, 'yyyy-MM');
m_end = temporalParse(month_end, 'yyyy-MM');
tb_end_date = table(m_start..m_end AS end_date);
return (SELECT t.fund_id, d.end_date, t.benchmark_id FROM t JOIN tb_end_date d WHERE d.end_date >= t.inception_date.month());
}
/*
* 取某时间段的组合主基准
* NOTE: 目前所有Java指标计算组合默认主基准是FA00000VNB，以后很可能会改
*
* Example: get_portfolio_primary_benchmark("166002,166114", '1990-01', '2024-08');
*/
def get_portfolio_primary_benchmark(portfolio_ids, month_start, month_end) {
s_query = "SELECT id AS portfolio_id, 'FA00000VNB' AS benchmark_id, inception_date
FROM pfdb.pf_customer_portfolio_map
WHERE id IN (" + portfolio_ids + ")
AND isvalid = 1;";
conn = connect_mysql();
t = odbc::query(conn, s_query);
conn.close();
t.addColumn('end_date', MONTH);
m_start = temporalParse(month_start, 'yyyy-MM');
m_end = temporalParse(month_end, 'yyyy-MM');
tb_end_date = table(m_start..m_end AS end_date);
return (SELECT t.portfolio_id, d.end_date, t.benchmark_id FROM t JOIN tb_end_date d WHERE d.end_date >= t.inception_date.month());
}
/*
* 取某时间段的基金组合主基准
*
* NOTE: 指数和因子的”主基准”设置为沪深300
*
* Example: get_entity_primary_benchmark('MF', "'MF00003PW2', 'MF00003PW1', 'MF00003PXO'", '1990-01', '2024-06');
* get_entity_primary_benchmark('PF', [166002,166114], '1990-01', '2024-08');
* get_entity_primary_benchmark('MI', ['IN00000008', 'IN0000000M'], '2024-07', '2024-08');
* get_entity_primary_benchmark('FA', ['FA00000VNB', 'FA00000VMJ'], '2024-01', '2024-08');
*/
def get_entity_primary_benchmark(entity_type, entity_ids, month_start, month_end) {
t = table(100:0,
['entity_id', 'end_date', 'benchmark_id'],
[iif(entity_type == 'PF', INT, SYMBOL), MONTH, SYMBOL]);
s_entity_ids = ids_to_string(entity_ids);
if(s_entity_ids == null || s_entity_ids == '') return null;
if(entity_type == 'MF' || entity_type == 'HF') {
t = get_fund_primary_benchmark(s_entity_ids, month_start, month_end);
t.rename!('fund_id', 'entity_id');
} else if(entity_type == 'PF') {
t = get_portfolio_primary_benchmark(s_entity_ids, month_start, month_end);
t.rename!('portfolio_id', 'entity_id');
} else if(entity_type IN ['MI', 'FI', 'FA', 'CI', 'EQ']) {
t = SELECT entity_id, end_date, NULL AS benchmark_id
FROM cj(get_entity_info(entity_type, s_entity_ids), table(temporalParse(month_start, 'yyyy-MM')..temporalParse(month_end, 'yyyy-MM') AS end_date))
WHERE end_date >= iif(inception_date.isNull(), 1990.01M, inception_date.month())
}
return t;
}
/*
* 取某时间段的基金组合BFI基准
*
*
* Example: get_entity_bfi_factors('MF', "'MF00003PW2', 'MF00003PW1', 'MF00003PXO'", 1990.01M, 2024.06M);
* get_entity_bfi_factors('PF', [166002,166114], 1990.01M, 2029.12M, 2024.10.01);
*/
def get_entity_bfi_factors(entity_type, entity_ids, month_start, month_end, updatetime=1990.01.01) {
tmp = get_bfi_by_category_group_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 + ")";
}
s_query = "SELECT " + tmp.sec_id_col[0] + " AS entity_id, end_date, factor_id
FROM " + tmp.table_name[0] + "
WHERE isvalid = 1 " +
sql_entity_id + "
AND end_date >= '" + month_start.temporalFormat('yyyy-MM') + "'
AND end_date <= '" + month_end.temporalFormat('yyyy-MM') + "'
AND updatetime >= '" + updatetime$STRING + "'
ORDER BY " + tmp.sec_id_col[0] + ", end_date, factor_id";
conn = connect_mysql();
t = odbc::query(conn, s_query);
conn.close();
return t;
}
/*
* 取基金-经理关系表
*
* Example：get_fund_manager_mapping(['MF00003PW1', 'MF00003PW2']);
*/
def get_fund_manager_mapping(fund_ids) {
t = null;
s_entity_ids = ids_to_string(fund_ids);
s_query = "SELECT fund_id, fund_manager_id, management_start_date, management_end_date
FROM mfdb.fund_manager_mapping
WHERE fund_id IN (" + s_entity_ids + ")
AND isvalid = 1
ORDER BY fund_id, management_start_date, management_end_date;";
conn = connect_mysql();
t = odbc::query(conn, s_query);
conn.close();
return t;
}