Merge pull request #479 from ShruthiRajaram/FINERACT-628-reports
FINERACT-628 self service user reports
diff --git a/api-docs/apiLive.htm b/api-docs/apiLive.htm
index 4ead11a..e381755 100644
--- a/api-docs/apiLive.htm
+++ b/api-docs/apiLive.htm
@@ -3872,6 +3872,14 @@
<td>self/clients/{clientId}/obligeedetails</td>
<td></td>
<td><a href="#self_obligee_list">Obligee list</a></td>
+ </td>
+ </td>
+ </tr>
+ <tr>
+ <td><a href="#self_service_user_reports">Run Report</a></td>
+ <td>self/runreports/{reportName}</td>
+ <td></td>
+ <td><a href="#self_service_user_reports">Run a Report</a></td>
<td></td>
<td></td>
</tr>
@@ -49299,6 +49307,7 @@
} </code>
</div>
</div>
+
<a id="self_loanproducts" name="self_loanproducts" class="old-syle-anchor"> </a>
<div class="method-section">
<div class="method-description">
@@ -50554,6 +50563,134 @@
</div>
</div>
+ <a id="self_service_user_reports" name="self_service_user_reports" class="old-syle-anchor"> </a>
+ <div class="method-section">
+ <div class="method-description">
+ <h4>Running a Report</h4>
+ <p>This resource allows you to run and receive output from pre-defined Apache Fineract reports. </p>
+ <p>
+ The default output is a JSON formatted "Generic Resultset". The Generic Resultset contains
+ Column Heading as well as Data information. However, you can
+ export to CSV format by simply adding "&exportCSV=true" to the end
+ of your URL.</p>
+ <p>If Pentaho reports have been pre-defined, they can also be
+ run through this resource. Pentaho reports can return HTML, PDF or
+ CSV formats.</p>
+ <p>The Apache Fineract reference application uses a
+ JQuery plugin called stretchyreporting which, itself, uses this
+ reports resource to provide a pretty flexible reporting User
+ Interface (UI).</p>
+ <h5>Arguments</h5>
+ <dl class="argument-list">
+ <dt>R_'parameter names' ...</dt>
+ <dd>
+ optional, <span>No defaults</span>
+ </dd>
+ <dd>The number and names of the parameters depend on the
+ specific report and how it has been configured. R_officeId is an
+ example parameter name.</dd>
+ <dd>Note: the prefix R_ stands for Reporting</dd>
+ <dt>genericResultSet</dt>
+ <dd>
+ <span>optional, defaults to true</span>
+ </dd>
+ <dd>If 'true' an optimised JSON format is returned suitable for tabular display of data.
+ <dd>If 'false' a simple JSON format is returned.
+ <dt>parameterType</dt>
+ <dd>
+ optional, <span>The only valid value is 'true'. If any
+ other value is provided the argument will be ignored</span>
+ </dd>
+ <dd>Determines whether the request looks in the list of
+ reports or the list of parameters for its data. Doesn't apply to
+ Pentaho reports.</dd>
+ <dt>exportCSV</dt>
+ <dd>
+ optional, <span>The only valid value is 'true'. If any
+ other value is provided the argument will be ignored</span>
+ </dd>
+ <dd>Output will be delivered as a CSV file instead of JSON.
+ Doesn't apply to Pentaho reports.</dd>
+ <dt>output-type</dt>
+ <dd>
+ optional, <span>Defaults to HTML.</span>
+ </dd>
+ <dd>Valid Values are HTML, XLS, XSLX, CSV and PDF for html, Excel, Excel 2007+,
+ CSV and PDF formats respectively.</dd>
+ <dd>Only applies to Pentaho reports.</dd>
+ <dt>locale</dt>
+ <dd>
+ optional
+ </dd>
+ <dd>Any valid locale Ex: en_US, en_IN, fr_FR etc</dd>
+ <dd>Only applies to Pentaho reports.</dd>
+ </dl>
+ <p>Example Requests:</p>
+ <div class=apiClick>self/runreports/Client%20Details?R_officeId=1</div>
+ <br>
+ <br>
+ <div class=apiClick>self/runreports/Client%20Details?R_officeId=1&exportCSV=true</div>
+ <br>
+ <br>
+ </div>
+ <div class="method-example">
+ <code class="method-declaration">
+GET https://DomainName/api/v1/self/runreports/{reportName}
+ </code>
+ <code class="method-response">
+{
+ "columnHeaders": [
+ {
+ "columnName": "Office/Branch",
+ "columnType": "VARCHAR",
+ "isColumnNullable": false,
+ "isColumnPrimaryKey": false,
+ "columnValues": []
+ },
+ {
+ "columnName": "Client Account No.",
+ "columnType": "VARCHAR",
+ "isColumnNullable": false,
+ "isColumnPrimaryKey": false,
+ "columnValues": []
+ },
+ {
+ "columnName": "Name",
+ "columnType": "VARCHAR",
+ "isColumnNullable": false,
+ "isColumnPrimaryKey": false,
+ "columnValues": []
+ },
+ {
+ "columnName": "Joined",
+ "columnType": "DATE",
+ "isColumnNullable": false,
+ "isColumnPrimaryKey": false,
+ "columnValues": []
+ },
+ {
+ "columnName": "External Id",
+ "columnType": "VARCHAR",
+ "isColumnNullable": false,
+ "isColumnPrimaryKey": false,
+ "columnValues": []
+ }
+ ],
+ "data": [
+ {
+ "row": [
+ "Head Office",
+ "000000001",
+ "John Doe",
+ "2017-03-04",
+ "786YYH7"
+ ]
+ }
+ ]
+}
+ </code>
+ </div>
+ </div>
<!-- end of Customer Self Service APIs-->
</div>
<!-- main-content-wrapper -->
diff --git a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/dataqueries/api/RunreportsApiResource.java b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/dataqueries/api/RunreportsApiResource.java
index ff83770..e13e652 100644
--- a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/dataqueries/api/RunreportsApiResource.java
+++ b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/dataqueries/api/RunreportsApiResource.java
@@ -24,10 +24,12 @@
import java.util.Set;
import javax.ws.rs.Consumes;
+import javax.ws.rs.DefaultValue;
import javax.ws.rs.GET;
import javax.ws.rs.Path;
import javax.ws.rs.PathParam;
import javax.ws.rs.Produces;
+import javax.ws.rs.QueryParam;
import javax.ws.rs.core.Context;
import javax.ws.rs.core.MediaType;
import javax.ws.rs.core.MultivaluedMap;
@@ -82,9 +84,11 @@
@ApiOperation(value = "Running a Report", notes = "This resource allows you to run and receive output from pre-defined Apache Fineract reports.\n" + "\n" + "Reports can also be used to provide data for searching and workflow functionality.\n" + "\n" + "The default output is a JSON formatted \"Generic Resultset\". The Generic Resultset contains Column Heading as well as Data information. However, you can export to CSV format by simply adding \"&exportCSV=true\" to the end of your URL.\n" + "\n" + "If Pentaho reports have been pre-defined, they can also be run through this resource. Pentaho reports can return HTML, PDF or CSV formats.\n" + "\n" + "The Apache Fineract reference application uses a JQuery plugin called stretchy reporting which, itself, uses this reports resource to provide a pretty flexible reporting User Interface (UI).\n\n" + "\n" +
"\n" + "Example Requests:\n" + "\n" + "runreports/Client%20Listing?R_officeId=1\n" + "\n" + "\n" + "runreports/Client%20Listing?R_officeId=1&exportCSV=true\n" + "\n" + "\n" + "runreports/OfficeIdSelectOne?R_officeId=1¶meterType=true\n" + "\n" + "\n" + "runreports/OfficeIdSelectOne?R_officeId=1¶meterType=true&exportCSV=true\n" + "\n" + "\n" + "runreports/Expected%20Payments%20By%20Date%20-%20Formatted?R_endDate=2013-04-30&R_loanOfficerId=-1&R_officeId=1&R_startDate=2013-04-16&output-type=HTML&R_officeId=1\n" + "\n" + "\n" + "runreports/Expected%20Payments%20By%20Date%20-%20Formatted?R_endDate=2013-04-30&R_loanOfficerId=-1&R_officeId=1&R_startDate=2013-04-16&output-type=XLS&R_officeId=1\n" + "\n" + "\n" + "runreports/Expected%20Payments%20By%20Date%20-%20Formatted?R_endDate=2013-04-30&R_loanOfficerId=-1&R_officeId=1&R_startDate=2013-04-16&output-type=CSV&R_officeId=1\n" + "\n" + "\n" + "runreports/Expected%20Payments%20By%20Date%20-%20Formatted?R_endDate=2013-04-30&R_loanOfficerId=-1&R_officeId=1&R_startDate=2013-04-16&output-type=PDF&R_officeId=1")
@ApiResponses({@ApiResponse(code = 200, message = "", response = RunreportsApiResourceSwagger.GetReportNameResponse.class)})
- public Response runReport(@PathParam("reportName") @ApiParam(value = "reportName") final String reportName, @Context final UriInfo uriInfo) {
+ public Response runReport(@PathParam("reportName") @ApiParam(value = "reportName") final String reportName,
+ @Context final UriInfo uriInfo,
+ @DefaultValue("false") @QueryParam("isSelfServiceUserReport") @ApiParam(value = "isSelfServiceUserReport") final boolean isSelfServiceUserReport) {
- final MultivaluedMap<String, String> queryParams = uriInfo.getQueryParameters();
+ final MultivaluedMap<String, String> queryParams = uriInfo.getQueryParameters();
final boolean prettyPrint = ApiParameterHelper.prettyPrint(uriInfo.getQueryParameters());
final boolean exportCsv = ApiParameterHelper.exportCsv(uriInfo.getQueryParameters());
@@ -96,7 +100,7 @@
String parameterTypeValue = null;
if (!parameterType) {
parameterTypeValue = "report";
- String reportType = this.readExtraDataAndReportingService.getReportType(reportName);
+ String reportType = this.readExtraDataAndReportingService.getReportType(reportName, isSelfServiceUserReport);
ReportingProcessService reportingProcessService = this.reportingProcessServiceProvider.findReportingProcessService(reportType);
if (reportingProcessService != null) { return reportingProcessService.processRequest(reportName, queryParams); }
} else {
@@ -108,7 +112,7 @@
if (exportPdf) {
final Map<String, String> reportParams = getReportParams(queryParams);
final String pdfFileName = this.readExtraDataAndReportingService
- .retrieveReportPDF(reportName, parameterTypeValue, reportParams);
+ .retrieveReportPDF(reportName, parameterTypeValue, reportParams, isSelfServiceUserReport);
final File file = new File(pdfFileName);
@@ -124,7 +128,7 @@
final Map<String, String> reportParams = getReportParams(queryParams);
final GenericResultsetData result = this.readExtraDataAndReportingService.retrieveGenericResultset(reportName,
- parameterTypeValue, reportParams);
+ parameterTypeValue, reportParams, isSelfServiceUserReport);
String json = "";
final boolean genericResultSetIsPassed = ApiParameterHelper.genericResultSetPassed(uriInfo.getQueryParameters());
@@ -145,7 +149,7 @@
// CSV Export
final Map<String, String> reportParams = getReportParams(queryParams);
final StreamingOutput result = this.readExtraDataAndReportingService
- .retrieveReportCSV(reportName, parameterTypeValue, reportParams);
+ .retrieveReportCSV(reportName, parameterTypeValue, reportParams, isSelfServiceUserReport);
return Response.ok().entity(result).type("text/csv")
.header("Content-Disposition", "attachment;filename=" + reportName.replaceAll(" ", "") + ".csv").build();
diff --git a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/dataqueries/domain/Report.java b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/dataqueries/domain/Report.java
index f909c33..95965ab 100644
--- a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/dataqueries/domain/Report.java
+++ b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/dataqueries/domain/Report.java
@@ -75,6 +75,9 @@
@OneToMany(cascade = CascadeType.ALL, mappedBy = "report", orphanRemoval = true, fetch=FetchType.EAGER)
private Set<ReportParameterUsage> reportParameterUsages = new HashSet<>();
+
+ @Column(name = "self_service_user_report")
+ private boolean isSelfServiceUserReport;
public static Report fromJson(final JsonCommand command, final Collection<String> reportTypes) {
@@ -278,6 +281,7 @@
}
public Set<ReportParameterUsage> getReportParameterUsages() {
- return this.reportParameterUsages;
- }
+ return this.reportParameterUsages;
+ }
+
}
\ No newline at end of file
diff --git a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/dataqueries/service/ReadReportingService.java b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/dataqueries/service/ReadReportingService.java
index 952a36a..bc8b9a6 100644
--- a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/dataqueries/service/ReadReportingService.java
+++ b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/dataqueries/service/ReadReportingService.java
@@ -32,13 +32,13 @@
public interface ReadReportingService {
- StreamingOutput retrieveReportCSV(String name, String type, Map<String, String> extractedQueryParams);
+ StreamingOutput retrieveReportCSV(String name, String type, Map<String, String> extractedQueryParams, boolean isSelfServiceUserReport);
- GenericResultsetData retrieveGenericResultset(String name, String type, Map<String, String> extractedQueryParams);
+ GenericResultsetData retrieveGenericResultset(String name, String type, Map<String, String> extractedQueryParams, boolean isSelfServiceUserReport);
- String retrieveReportPDF(String name, String type, Map<String, String> extractedQueryParams);
+ String retrieveReportPDF(String name, String type, Map<String, String> extractedQueryParams, boolean isSelfServiceUserReport);
- String getReportType(String reportName);
+ String getReportType(String reportName, boolean isSelfServiceUserReport);
Collection<ReportData> retrieveReportList();
diff --git a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/dataqueries/service/ReadReportingServiceImpl.java b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/dataqueries/service/ReadReportingServiceImpl.java
index c732f0d..2d9d271 100644
--- a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/dataqueries/service/ReadReportingServiceImpl.java
+++ b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/dataqueries/service/ReadReportingServiceImpl.java
@@ -90,15 +90,16 @@
}
@Override
- public StreamingOutput retrieveReportCSV(final String name, final String type, final Map<String, String> queryParams) {
+ public StreamingOutput retrieveReportCSV(final String name, final String type,
+ final Map<String, String> queryParams, final boolean isSelfServiceUserReport) {
- return new StreamingOutput() {
+ return new StreamingOutput() {
@Override
public void write(final OutputStream out) {
try {
- final GenericResultsetData result = retrieveGenericResultset(name, type, queryParams);
+ final GenericResultsetData result = retrieveGenericResultset(name, type, queryParams, isSelfServiceUserReport);
final StringBuffer sb = generateCsvFileBuffer(result);
final InputStream in = new ByteArrayInputStream(sb.toString().getBytes("UTF-8"));
@@ -171,12 +172,13 @@
}
@Override
- public GenericResultsetData retrieveGenericResultset(final String name, final String type, final Map<String, String> queryParams) {
+ public GenericResultsetData retrieveGenericResultset(final String name, final String type,
+ final Map<String, String> queryParams, final boolean isSelfServiceUserReport) {
final long startTime = System.currentTimeMillis();
logger.info("STARTING REPORT: " + name + " Type: " + type);
- final String sql = getSQLtoRun(name, type, queryParams);
+ final String sql = getSQLtoRun(name, type, queryParams, isSelfServiceUserReport);
final GenericResultsetData result = this.genericDataService.fillGenericResultSet(sql);
@@ -185,7 +187,8 @@
return result;
}
- private String getSQLtoRun(final String name, final String type, final Map<String, String> queryParams) {
+ private String getSQLtoRun(final String name, final String type, final Map<String, String> queryParams,
+ final boolean isSelfServiceUserReport) {
String sql = getSql(name, type);
@@ -203,18 +206,22 @@
// (typically used to return report lists containing only reports
// permitted to be run by the user
sql = this.genericDataService.replace(sql, "${currentUserId}", currentUser.getId().toString());
+
+ sql = this.genericDataService.replace(sql, "${isSelfServiceUser}",
+ Integer.toString(isSelfServiceUserReport ? 1 : 0));
- sql = this.genericDataService.wrapSQL(sql);
+ sql = this.genericDataService.wrapSQL(sql);
- return sql;
+ return sql;
- }
+ }
private String getSql(final String name, final String type) {
- final String inputSql = "select " + type + "_sql as the_sql from stretchy_" + type + " where " + type + "_name = '" + name + "'";
+ final String inputSql = "select " + type + "_sql as the_sql from stretchy_" + type + " where " + type + "_name = '" + name + "'" ;
final String inputSqlWrapped = this.genericDataService.wrapSQL(inputSql);
+ // the return statement contains the exact sql required
final SqlRowSet rs = this.jdbcTemplate.queryForRowSet(inputSqlWrapped);
if (rs.next() && rs.getString("the_sql") != null) { return rs.getString("the_sql"); }
@@ -222,21 +229,22 @@
}
@Override
- public String getReportType(final String reportName) {
+ public String getReportType(final String reportName, final boolean isSelfServiceUserReport) {
- final String sql = "SELECT ifnull(report_type,'') as report_type FROM `stretchy_report` where report_name = '" + reportName + "'";
+ final String sql = "SELECT ifnull(report_type,'') as report_type FROM `stretchy_report` where report_name = '" + reportName + "' and self_service_user_report = ?";
this.columnValidator.validateSqlInjection(sql, reportName);
final String sqlWrapped = this.genericDataService.wrapSQL(sql);
- final SqlRowSet rs = this.jdbcTemplate.queryForRowSet(sqlWrapped);
+ final SqlRowSet rs = this.jdbcTemplate.queryForRowSet(sqlWrapped, new Object [] {isSelfServiceUserReport});
if (rs.next()) { return rs.getString("report_type"); }
throw new ReportNotFoundException(sql);
}
@Override
- public String retrieveReportPDF(final String reportName, final String type, final Map<String, String> queryParams) {
+ public String retrieveReportPDF(final String reportName, final String type, final Map<String, String> queryParams,
+ final boolean isSelfServiceUserReport) {
final String fileLocation = FileSystemContentRepository.FINERACT_BASE_DIR + File.separator + "";
if (!new File(fileLocation).isDirectory()) {
@@ -246,7 +254,7 @@
final String genaratePdf = fileLocation + File.separator + reportName + ".pdf";
try {
- final GenericResultsetData result = retrieveGenericResultset(reportName, type, queryParams);
+ final GenericResultsetData result = retrieveGenericResultset(reportName, type, queryParams, isSelfServiceUserReport);
final List<ResultsetColumnHeaderData> columnHeaders = result.getColumnHeaders();
final List<ResultsetRowData> data = result.getData();
diff --git a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/reportmailingjob/service/ReportMailingJobWritePlatformServiceImpl.java b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/reportmailingjob/service/ReportMailingJobWritePlatformServiceImpl.java
index 6e9f5e3..338303f 100644
--- a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/reportmailingjob/service/ReportMailingJobWritePlatformServiceImpl.java
+++ b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/reportmailingjob/service/ReportMailingJobWritePlatformServiceImpl.java
@@ -422,7 +422,8 @@
final MultivaluedMap<String, String> reportParams, final String reportName, final StringBuilder errorLog) {
try {
- final String reportType = this.readReportingService.getReportType(reportName);
+ final boolean isSelfServiceUserReport = false;
+ final String reportType = this.readReportingService.getReportType(reportName, isSelfServiceUserReport);
final ReportingProcessService reportingProcessService = this.reportingProcessServiceProvider.findReportingProcessService(reportType);
if (reportingProcessService != null) {
diff --git a/fineract-provider/src/main/java/org/apache/fineract/portfolio/self/runreport/SelfRunReportApiResource.java b/fineract-provider/src/main/java/org/apache/fineract/portfolio/self/runreport/SelfRunReportApiResource.java
index b86d176..941aff0 100644
--- a/fineract-provider/src/main/java/org/apache/fineract/portfolio/self/runreport/SelfRunReportApiResource.java
+++ b/fineract-provider/src/main/java/org/apache/fineract/portfolio/self/runreport/SelfRunReportApiResource.java
@@ -54,7 +54,8 @@
@Produces({ MediaType.APPLICATION_JSON, "text/csv", "application/vnd.ms-excel", "application/pdf", "text/html" })
public Response runReport(@PathParam("reportName") final String reportName, @Context final UriInfo uriInfo) {
this.context.authenticatedUser();
- return this.runreportsApiResource.runReport(reportName, uriInfo);
+ final boolean isSelfServiceUserReport = true;
+ return this.runreportsApiResource.runReport(reportName, uriInfo, isSelfServiceUserReport);
}
}
diff --git a/fineract-provider/src/main/resources/sql/migrations/core_db/V345__reports_for_self_service_user.sql b/fineract-provider/src/main/resources/sql/migrations/core_db/V345__reports_for_self_service_user.sql
new file mode 100644
index 0000000..92c4283
--- /dev/null
+++ b/fineract-provider/src/main/resources/sql/migrations/core_db/V345__reports_for_self_service_user.sql
@@ -0,0 +1,29 @@
+--
+-- Licensed to the Apache Software Foundation (ASF) under one
+-- or more contributor license agreements. See the NOTICE file
+-- distributed with this work for additional information
+-- regarding copyright ownership. The ASF licenses this file
+-- to you under the Apache License, Version 2.0 (the
+-- "License"); you may not use this file except in compliance
+-- with the License. You may obtain a copy of the License at
+--
+-- http://www.apache.org/licenses/LICENSE-2.0
+--
+-- Unless required by applicable law or agreed to in writing,
+-- software distributed under the License is distributed on an
+-- "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+-- KIND, either express or implied. See the License for the
+-- specific language governing permissions and limitations
+-- under the License.
+--
+
+ALTER TABLE `stretchy_report`
+ ADD COLUMN `self_service_user_report` TINYINT(1) NOT NULL DEFAULT '0' AFTER `use_report`;
+
+
+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 and sr.self_service_user_report = \'${isSelfServiceUser}\'\r where sr.report_name in(${reportListing})\r and srp.parameter_id = sp.id\r )\r order by sp.id' WHERE `parameter_name` = 'FullParameterList';
+
+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 and r.self_service_user_report = \'${isSelfServiceUser}\'\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 `parameter_name` = 'FullReportList';
+
+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 and r.self_service_user_report = \'${isSelfServiceUser}\' \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 `parameter_name`='reportCategoryList';
+
