/* get all RPA Receipts */
SELECT 
investor_investments.investor_id, 
intermediary.ref_id as intermediary_ref_id,
investor_investments.investment_id, 
investor_investments.investment_date, 
investor_investments.investment_type,
investor_investments.investment_source,
transaction_types.transaction_name, 
FORMAT(investor_investments.amount,2) as amount, 
investor_investments.status, 
investor.ref_id as investor_ref_id, 
IF(investor.register_type=1,investor.organization_name, GROUP_CONCAT(DISTINCT CONCAT(contact_details.first_name,' ',contact_details.last_name)  SEPARATOR ' & ')) as realname, 
currencies.currency_code,
investor.temp_reg_id, 
'' as actions, 
is_latest 
FROM investor_investments
LEFT JOIN temp_registration as investor ON investor.user_id = investor_investments.investor_id 
LEFT JOIN currencies ON currencies.currency_id = investor_investments.currency_id 
LEFT JOIN transaction_types ON transaction_types.type_id=1 AND transaction_types.transaction_id = investor_investments.investment_type 
LEFT JOIN investor_relation ON investor_relation.investor_id = investor.temp_reg_id 
LEFT JOIN temp_registration as intermediary ON intermediary.user_id = investor_relation.intermediary_id 
LEFT JOIN contact_details ON contact_details.temp_reg_id = investor.temp_reg_id  AND (contact_details.contact_flag=3 OR (contact_details.contact_flag=0 AND contact_details.bank_id=0)) 
WHERE investor_investments.status IN (1,0) AND investor_investments.investment_type IN (1) 
GROUP BY investor_investments.investment_id 

/* ---------------- */

/* get all RPA Addition */
SELECT 
investor_investments.investor_id, 
intermediary.ref_id as intermediary_ref_id,
investor_investments.investment_id, 
investor_investments.investment_date, 
investor_investments.investment_type,
investor_investments.investment_source,
transaction_types.transaction_name, 
FORMAT(investor_investments.amount,2) as amount, 
investor_investments.status, 
investor.ref_id as investor_ref_id, 
IF(investor.register_type=1,investor.organization_name, GROUP_CONCAT(DISTINCT CONCAT(contact_details.first_name,' ',contact_details.last_name)  SEPARATOR ' & ')) as realname, 
currencies.currency_code,
investor.temp_reg_id, 
'' as actions, 
is_latest 
FROM investor_investments
LEFT JOIN temp_registration as investor ON investor.user_id = investor_investments.investor_id 
LEFT JOIN currencies ON currencies.currency_id = investor_investments.currency_id 
LEFT JOIN transaction_types ON transaction_types.type_id=1 AND transaction_types.transaction_id = investor_investments.investment_type 
LEFT JOIN investor_relation ON investor_relation.investor_id = investor.temp_reg_id 
LEFT JOIN temp_registration as intermediary ON intermediary.user_id = investor_relation.intermediary_id 
LEFT JOIN contact_details ON contact_details.temp_reg_id = investor.temp_reg_id  AND (contact_details.contact_flag=3 OR (contact_details.contact_flag=0 AND contact_details.bank_id=0)) 
WHERE investor_investments.status IN (1,0) AND investor_investments.investment_type IN (8) 
GROUP BY investor_investments.investment_id 

/* ---------------- */

/* get all Partial Redemption */
SELECT 
investor_investments.investor_id, 
intermediary.ref_id as intermediary_ref_id,
investor_investments.investment_id, 
investor_investments.investment_date, 
investor_investments.investment_type,
investor_investments.investment_source,
transaction_types.transaction_name, 
FORMAT(investor_investments.amount,2) as amount, 
investor_investments.status, 
investor.ref_id as investor_ref_id, 
IF(investor.register_type=1,investor.organization_name, GROUP_CONCAT(DISTINCT CONCAT(contact_details.first_name,' ',contact_details.last_name)  SEPARATOR ' & ')) as realname, 
currencies.currency_code,
investor.temp_reg_id, 
'' as actions, 
is_latest 
FROM investor_investments
LEFT JOIN temp_registration as investor ON investor.user_id = investor_investments.investor_id 
LEFT JOIN currencies ON currencies.currency_id = investor_investments.currency_id 
LEFT JOIN transaction_types ON transaction_types.type_id=1 AND transaction_types.transaction_id = investor_investments.investment_type 
LEFT JOIN investor_relation ON investor_relation.investor_id = investor.temp_reg_id 
LEFT JOIN temp_registration as intermediary ON intermediary.user_id = investor_relation.intermediary_id 
LEFT JOIN contact_details ON contact_details.temp_reg_id = investor.temp_reg_id  AND (contact_details.contact_flag=3 OR (contact_details.contact_flag=0 AND contact_details.bank_id=0)) 
WHERE investor_investments.status IN (1,0) AND investor_investments.investment_type IN (3) 
GROUP BY investor_investments.investment_id 

/* ---------------- */

/* get all Full Redemption */
SELECT 
investor_investments.investor_id, 
intermediary.ref_id as intermediary_ref_id,
investor_investments.investment_id, 
investor_investments.investment_date, 
investor_investments.investment_type,
investor_investments.investment_source,
transaction_types.transaction_name, 
FORMAT(investor_investments.amount,2) as amount, 
investor_investments.status, 
investor.ref_id as investor_ref_id, 
IF(investor.register_type=1,investor.organization_name, GROUP_CONCAT(DISTINCT CONCAT(contact_details.first_name,' ',contact_details.last_name)  SEPARATOR ' & ')) as realname, 
currencies.currency_code,
investor.temp_reg_id, 
'' as actions, 
is_latest 
FROM investor_investments
LEFT JOIN temp_registration as investor ON investor.user_id = investor_investments.investor_id 
LEFT JOIN currencies ON currencies.currency_id = investor_investments.currency_id 
LEFT JOIN transaction_types ON transaction_types.type_id=1 AND transaction_types.transaction_id = investor_investments.investment_type 
LEFT JOIN investor_relation ON investor_relation.investor_id = investor.temp_reg_id 
LEFT JOIN temp_registration as intermediary ON intermediary.user_id = investor_relation.intermediary_id 
LEFT JOIN contact_details ON contact_details.temp_reg_id = investor.temp_reg_id  AND (contact_details.contact_flag=3 OR (contact_details.contact_flag=0 AND contact_details.bank_id=0)) 
WHERE investor_investments.status IN (1,0) AND investor_investments.investment_type IN (4) 
GROUP BY investor_investments.investment_id 

/* ---------------- */

/* get all Partial Coupon */
SELECT 
investor_investments.investor_id, 
intermediary.ref_id as intermediary_ref_id,
investor_investments.investment_id, 
investor_investments.investment_date, 
investor_investments.investment_type,
investor_investments.investment_source,
transaction_types.transaction_name, 
FORMAT(investor_investments.amount,2) as amount, 
investor_investments.status, 
investor.ref_id as investor_ref_id, 
IF(investor.register_type=1,investor.organization_name, GROUP_CONCAT(DISTINCT CONCAT(contact_details.first_name,' ',contact_details.last_name)  SEPARATOR ' & ')) as realname, 
currencies.currency_code,
investor.temp_reg_id, 
'' as actions, 
is_latest 
FROM investor_investments
LEFT JOIN temp_registration as investor ON investor.user_id = investor_investments.investor_id 
LEFT JOIN currencies ON currencies.currency_id = investor_investments.currency_id 
LEFT JOIN transaction_types ON transaction_types.type_id=1 AND transaction_types.transaction_id = investor_investments.investment_type 
LEFT JOIN investor_relation ON investor_relation.investor_id = investor.temp_reg_id 
LEFT JOIN temp_registration as intermediary ON intermediary.user_id = investor_relation.intermediary_id 
LEFT JOIN contact_details ON contact_details.temp_reg_id = investor.temp_reg_id  AND (contact_details.contact_flag=3 OR (contact_details.contact_flag=0 AND contact_details.bank_id=0)) 
WHERE investor_investments.status IN (1,0) AND investor_investments.investment_type IN (11) 
GROUP BY investor_investments.investment_id 

/* ---------------- */

/* get all Coupon */
SELECT 
investor_investments.investor_id, 
intermediary.ref_id as intermediary_ref_id,
investor_investments.investment_id, 
investor_investments.investment_date, 
investor_investments.investment_type,
investor_investments.investment_source,
transaction_types.transaction_name, 
FORMAT(investor_investments.amount,2) as amount, 
investor_investments.status, 
investor.ref_id as investor_ref_id, 
IF(investor.register_type=1,investor.organization_name, GROUP_CONCAT(DISTINCT CONCAT(contact_details.first_name,' ',contact_details.last_name)  SEPARATOR ' & ')) as realname, 
currencies.currency_code,
investor.temp_reg_id, 
'' as actions, 
is_latest 
FROM investor_investments
LEFT JOIN temp_registration as investor ON investor.user_id = investor_investments.investor_id 
LEFT JOIN currencies ON currencies.currency_id = investor_investments.currency_id 
LEFT JOIN transaction_types ON transaction_types.type_id=1 AND transaction_types.transaction_id = investor_investments.investment_type 
LEFT JOIN investor_relation ON investor_relation.investor_id = investor.temp_reg_id 
LEFT JOIN temp_registration as intermediary ON intermediary.user_id = investor_relation.intermediary_id 
LEFT JOIN contact_details ON contact_details.temp_reg_id = investor.temp_reg_id  AND (contact_details.contact_flag=3 OR (contact_details.contact_flag=0 AND contact_details.bank_id=0)) 
WHERE investor_investments.status IN (1,0) AND investor_investments.investment_type IN (2) 
GROUP BY investor_investments.investment_id 

/* ---------------- */

/* get all Renewal */
SELECT 
investor_investments.investor_id, 
intermediary.ref_id as intermediary_ref_id,
investor_investments.investment_id, 
investor_investments.investment_date, 
investor_investments.investment_type,
investor_investments.investment_source,
transaction_types.transaction_name, 
FORMAT(investor_investments.amount,2) as amount, 
investor_investments.status, 
investor.ref_id as investor_ref_id, 
IF(investor.register_type=1,investor.organization_name, GROUP_CONCAT(DISTINCT CONCAT(contact_details.first_name,' ',contact_details.last_name)  SEPARATOR ' & ')) as realname, 
currencies.currency_code,
investor.temp_reg_id, 
'' as actions, 
is_latest 
FROM investor_investments
LEFT JOIN temp_registration as investor ON investor.user_id = investor_investments.investor_id 
LEFT JOIN currencies ON currencies.currency_id = investor_investments.currency_id 
LEFT JOIN transaction_types ON transaction_types.type_id=1 AND transaction_types.transaction_id = investor_investments.investment_type 
LEFT JOIN investor_relation ON investor_relation.investor_id = investor.temp_reg_id 
LEFT JOIN temp_registration as intermediary ON intermediary.user_id = investor_relation.intermediary_id 
LEFT JOIN contact_details ON contact_details.temp_reg_id = investor.temp_reg_id  AND (contact_details.contact_flag=3 OR (contact_details.contact_flag=0 AND contact_details.bank_id=0)) 
WHERE investor_investments.status IN (1,0) AND investor_investments.investment_type IN (9) 
GROUP BY investor_investments.investment_id 

/* ---------------- */

/* get all Compound */
SELECT 
investor_investments.investor_id, 
intermediary.ref_id as intermediary_ref_id,
investor_investments.investment_id, 
investor_investments.investment_date, 
investor_investments.investment_type,
investor_investments.investment_source,
transaction_types.transaction_name, 
FORMAT(investor_investments.amount,2) as amount, 
investor_investments.status, 
investor.ref_id as investor_ref_id, 
IF(investor.register_type=1,investor.organization_name, GROUP_CONCAT(DISTINCT CONCAT(contact_details.first_name,' ',contact_details.last_name)  SEPARATOR ' & ')) as realname, 
currencies.currency_code,
investor.temp_reg_id, 
'' as actions, 
is_latest 
FROM investor_investments
LEFT JOIN temp_registration as investor ON investor.user_id = investor_investments.investor_id 
LEFT JOIN currencies ON currencies.currency_id = investor_investments.currency_id 
LEFT JOIN transaction_types ON transaction_types.type_id=1 AND transaction_types.transaction_id = investor_investments.investment_type 
LEFT JOIN investor_relation ON investor_relation.investor_id = investor.temp_reg_id 
LEFT JOIN temp_registration as intermediary ON intermediary.user_id = investor_relation.intermediary_id 
LEFT JOIN contact_details ON contact_details.temp_reg_id = investor.temp_reg_id  AND (contact_details.contact_flag=3 OR (contact_details.contact_flag=0 AND contact_details.bank_id=0)) 
WHERE investor_investments.status IN (1,0) AND investor_investments.investment_type IN (5) 
GROUP BY investor_investments.investment_id 

/* ---------------- */