DolphinDB/modules/operationDataPuller.dos

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
               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_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');
    }

	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');
 */
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, 'IN00000008' 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;
}