fineract-provider/src/main/resources/sql/migrations/core_db/V18__update_stretchy_reporting_reportSql.sql - fineract - Git at Google


 UPDATE `stretchy_parameter` SET `parameter_sql`='select sp.parameter_name, sp.parameter_variable, sp.parameter_label, sp.parameter_displayType, \r sp.parameter_FormatType, sp.parameter_default, sp.selectOne,  sp.selectAll, spp.parameter_name as parentParameterName\r from stretchy_parameter sp\r left join stretchy_parameter spp on spp.id = sp.parent_id\r where sp.special is null\r and exists \r 	(select \'f\' \r 	from stretchy_report sr\r 	join stretchy_report_parameter srp on srp.report_id = sr.id\r 	where sr.report_name in(${reportListing})\r 	and srp.parameter_id = sp.id\r 	)\r order by sp.id' WHERE `id`='1002';

 UPDATE `stretchy_parameter` SET `parameter_sql`='select  r.id as report_id, r.report_name, r.report_type, r.report_subtype, r.report_category,\nrp.id as parameter_id, rp.report_parameter_name, p.parameter_name\n  from stretchy_report r\n  left join stretchy_report_parameter rp on rp.report_id = r.id \n  left join stretchy_parameter p on p.id = rp.parameter_id\n  where r.use_report is true\n  and exists\n  ( select \'f\'\n  from m_appuser_role ur \n  join m_role r on r.id = ur.role_id\n  join m_role_permission rp on rp.role_id = r.id\n  join m_permission p on p.id = rp.permission_id\n  where ur.appuser_id = ${currentUserId}\n  and (p.code in (\'ALL_FUNCTIONS_READ\', \'ALL_FUNCTIONS\') or p.code = concat(\"READ_\", r.report_name)) )\n  order by r.report_category, r.report_name, rp.id' WHERE `id`='1001';

 UPDATE `stretchy_parameter` SET `parameter_sql`='select  r.id as report_id, r.report_name, r.report_type, r.report_subtype, r.report_category,\n  rp.id as parameter_id, rp.report_parameter_name, p.parameter_name\n  from stretchy_report r\n  left join stretchy_report_parameter rp on rp.report_id = r.id\n  left join stretchy_parameter p on p.id = rp.parameter_id\n  where r.report_category = \'${reportCategory}\'\n  and r.use_report is true\n  and exists\n  (select \'f\'\n  from m_appuser_role ur \n  join m_role r on r.id = ur.role_id\n  join m_role_permission rp on rp.role_id = r.id\n  join m_permission p on p.id = rp.permission_id\n  where ur.appuser_id = ${currentUserId}\n  and (p.code in (\'ALL_FUNCTIONS_READ\', \'ALL_FUNCTIONS\') or p.code = concat(\"READ_\", r.report_name)) )\n  order by r.report_category, r.report_name, rp.id' WHERE `id`='1003';

	UPDATE `stretchy_parameter` SET `parameter_sql`='select sp.parameter_name, sp.parameter_variable, sp.parameter_label, sp.parameter_displayType, \r sp.parameter_FormatType, sp.parameter_default, sp.selectOne, sp.selectAll, spp.parameter_name as parentParameterName\r from stretchy_parameter sp\r left join stretchy_parameter spp on spp.id = sp.parent_id\r where sp.special is null\r and exists \r (select \'f\' \r from stretchy_report sr\r join stretchy_report_parameter srp on srp.report_id = sr.id\r where sr.report_name in(${reportListing})\r and srp.parameter_id = sp.id\r )\r order by sp.id' WHERE `id`='1002';

	UPDATE `stretchy_parameter` SET `parameter_sql`='select r.id as report_id, r.report_name, r.report_type, r.report_subtype, r.report_category,\nrp.id as parameter_id, rp.report_parameter_name, p.parameter_name\n from stretchy_report r\n left join stretchy_report_parameter rp on rp.report_id = r.id \n left join stretchy_parameter p on p.id = rp.parameter_id\n where r.use_report is true\n and exists\n ( select \'f\'\n from m_appuser_role ur \n join m_role r on r.id = ur.role_id\n join m_role_permission rp on rp.role_id = r.id\n join m_permission p on p.id = rp.permission_id\n where ur.appuser_id = ${currentUserId}\n and (p.code in (\'ALL_FUNCTIONS_READ\', \'ALL_FUNCTIONS\') or p.code = concat(\"READ_\", r.report_name)) )\n order by r.report_category, r.report_name, rp.id' WHERE `id`='1001';

	UPDATE `stretchy_parameter` SET `parameter_sql`='select r.id as report_id, r.report_name, r.report_type, r.report_subtype, r.report_category,\n rp.id as parameter_id, rp.report_parameter_name, p.parameter_name\n from stretchy_report r\n left join stretchy_report_parameter rp on rp.report_id = r.id\n left join stretchy_parameter p on p.id = rp.parameter_id\n where r.report_category = \'${reportCategory}\'\n and r.use_report is true\n and exists\n (select \'f\'\n from m_appuser_role ur \n join m_role r on r.id = ur.role_id\n join m_role_permission rp on rp.role_id = r.id\n join m_permission p on p.id = rp.permission_id\n where ur.appuser_id = ${currentUserId}\n and (p.code in (\'ALL_FUNCTIONS_READ\', \'ALL_FUNCTIONS\') or p.code = concat(\"READ_\", r.report_name)) )\n order by r.report_category, r.report_name, rp.id' WHERE `id`='1003';