PRAAM04

GEL using the JasperSoft API to send the report attachment to target email address

Blog Post created by PRAAM04 Employee on May 30, 2018

My proposed solution was to use the API that can get executed through GEL.
Here is a link to some of the documentation https://community.jaspersoft.com/documentation/jasperreports-server-ultimate-guide/v55/report-scheduling-api

For the solution to work for the client we need to do the following

1. Execute a report ON DEMAND for a specific change request. This report can get turned into a PDF and emailed to a specific email with a specific subject.
We have accomplished this via a process in GEL using the Jaspersoft API.

 

Sample Script:

 

<gel:script xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:bpm="jelly:com.niku.bpm.gel.BPMTagLibrary" xmlns:core="jelly:core"
xmlns:file="jelly:com.niku.union.gel.FileTagLibrary" xmlns:ftp="jelly:com.niku.union.gel.FTPTagLibrary"
xmlns:gel="jelly:com.niku.union.gel.GELTagLibrary" xmlns:mail="jelly:email" xmlns:soap="jelly:com.niku.union.gel.SOAPTagLibrary"
xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:sql="jelly:sql" xmlns:util="jelly:util" xmlns:xog="http://www.niku.com/xog"
xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<core:catch var="v_global_exception">
<!-- Debug Parameters -->
<!-- Author: Praveen Amudalapalli -->
<!-- This Flag is used to display all the logs in the script which are not part of REST and JSON -->
<gel:parameter default="true" var="bDebug"/>
<!-- This Flag is used to display all the REST logs except the REST Response Body -->
<gel:parameter default="true" var="bDebugREST"/>
<!-- This Flag is used to display all the JSON logs except the JSON Response Body -->
<gel:parameter default="true" var="bDebugJSON"/>
<!-- This Flag is used to display only REST Response Body -->
<gel:parameter default="true" var="bDebugRESTResponse"/>
<!-- This Flag is used to display only JSON Response Body -->
<gel:parameter default="true" var="bDebugJSONResponse"/>
<!-- Share point Parameters -->
<gel:parameter default="06. Finance\\01. Vendor Requisition Requests" var="share_folder"/>
<!-- CA PPM Parameters -->
<gel:parameter default="" var="v_nikuDB"/>
<gel:setDataSource dbId="${v_nikuDB}"/>
<gel:parameter default="admin" var="username"/>
<gel:parameter default="" var="emailTo"/>
<!-- TFS Parameters -->
<gel:parameter default="" var="jasperHostName"/>
<gel:parameter default="" var="jasperUser"/>
<gel:parameter default="" var="timeZone"/>
<gel:parameter default="" var="populateprj"/>
<gel:parameter default="" var="project_id"/>
<gel:parameter default="" var="populatereq"/>
<gel:parameter default="" var="requisition_id"/>
<gel:parameter default="" var="repositoryURI"/>
<gel:parameter default="/Custom_Reports/Outputs" var="jp_outputfolder"/>
<gel:parameter default="" secure="true" var="jasperPassword"/>
<core:set value="${jasperUser}:${jasperPassword}" var="jasperCredentials"/>
<!-- Encode Password -->
<core:new className="sun.misc.BASE64Encoder" var="encoder"/>
<core:set value="${encoder.encode( jasperCredentials.getBytes() )}" var="encodedCredentials"/>
<core:if test="${bDebug}">
<gel:log>Encoded Credentials: ${encodedCredentials}</gel:log>
</core:if>
<gel:formatDate format="MM-dd-YYYY" stringVar="v_current_date"/>
<core:catch var="v_sql_exc">
<sql:query var="thisDate"><![CDATA[
SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD"T"HH24:MI:SS') NOW FROM DUAL
]]></sql:query>
<sql:query
var="thisProjectId"><![CDATA[ select
req.id req_id
,req.code req_code
,REPLACE(REPLACE(REPLACE(req.name,CHR(10),' '),CHR(13),' '),' ','_') req_name
,NVL(req.co_sap_purch_req_no,'NA') sap_purch_no
,inv.id inv_id
,inv.code inv_code
,inv.name inv_name
from
inv_investments inv
join odf_ca_co_requisition_req req on inv.id=req.odf_parent_id
where inv.odf_object_code='project'
and req.id = ?]]><sql:param value="${gel_objectInstanceId}"/>
</sql:query>
</core:catch>
<core:set value=" " var="blank"/>
<core:set value="${thisProjectId.rows[0].req_id}" var="req_id"/>
<core:set value="${thisProjectId.rows[0].req_code}" var="req_code"/>
<core:set value="${thisProjectId.rows[0].req_name}" var="req_name"/>
<core:set value="${thisProjectId.rows[0].sap_purch_no}" var="sap_purch_no"/>
<core:set value="${thisProjectId.rows[0].inv_id}" var="inv_id"/>
<core:set value="${thisProjectId.rows[0].inv_code}" var="inv_code"/>
<core:set value="${thisProjectId.rows[0].inv_name}" var="inv_name"/>
<!--<core:set value="${inv_code}:${sap_purch_no}_${req_name}\\${req_code}\\${share_folder}" var="subject"/>-->
<core:set value="${inv_code}:${share_folder}\\${req_code}" var="subject"/>
<core:set value="${sap_purch_no}_${req_name}" var="report_name"/>
<gel:log level="INFO">${report_name}</gel:log>
<core:if test="${v_sql_exc != null}">
<gel:log level="ERROR" message=" Exception while executing query: ${v_sql_exc.getMessage()}"/>
</core:if>
<core:set value="reportservice/rest_v2/jobs/" var="jasperUrlPath"/>
<core:set encode="0" var="httpRESTURL">https://${jasperHostName}/${jasperUrlPath}</core:set>
<core:if test="${bDebugREST}">
<gel:log level="INFO">REST URL: ${httpRESTURL}</gel:log>
</core:if>
<core:new className="java.net.URL" var="remoteURL">
<core:arg type="java.lang.String" value="${httpRESTURL}"/>
</core:new>
<core:set value="${remoteURL.openConnection()}" var="connection"/>
<core:set value="${connection.setDoInput(true)}" var="void"/>
<core:set value="${connection.setDoOutput(true)}" var="void"/>
<core:set value="${connection.setUseCaches(false)}" var="void"/>
<core:invoke method="setRequestMethod" on="${connection}">
<core:arg type="java.lang.String" value="PUT"/>
</core:invoke>
<core:invoke method="setRequestProperty" on="${connection}">
<core:arg type="java.lang.String" value="Content-Type"/>
<core:arg type="java.lang.String" value="application/json"/>
</core:invoke>
<core:invoke method="setRequestProperty" on="${connection}">
<core:arg type="java.lang.String" value="Accept"/>
<core:arg type="java.lang.String" value="application/json"/>
</core:invoke>
<core:invoke method="setRequestProperty" on="${connection}">
<core:arg type="java.lang.String" value="Authorization"/>
<core:arg type="java.lang.String" value="Basic ${encodedCredentials}"/>
</core:invoke>
<core:set value="${connection.connect()}" var="void"/>
<core:set trim="true" var="createJob"><![CDATA[
{
"version": 0,
"username": "${jasperUser}",
"label": "Final",
"description": "Final",
"trigger": {
"simpleTrigger": {
"version": 0,
"timezone": "America/Los_Angeles",
"startType": 1,
"occurrenceCount": 1
}
},
"source": {
"reportUnitURI": "${repositoryURI}",
"parameters": {
"parameterValues": {
"populateprj":1,
"project_id": ["${inv_id}"],
"populatereq":1,
"requisition_id":["${req_id}"]
}
}
},
"baseOutputFilename": "${report_name}",
"mailNotification": {
"version": 0,
"toAddresses": {
"address": [
"${emailTo}"
]
},
"ccAddresses": {
"address": []
},
"bccAddresses": {
"address": ["praam04@ca.com"]
},
"subject": "${subject}",
"messageText": "Please find attached PDF of Approved Requisition Report.",
"skipEmptyReports": false,
"includingStackTraceWhenJobFails": false,
"skipNotificationWhenJobFails": false,
"resultSendType": "SEND_ATTACHMENT"
},
"outputTimeZone": "America/Los_Angeles",
"repositoryDestination": {
"version": 0,
"folderURI": "${jp_outputfolder}",
"sequentialFilenames": false,
"overwriteFiles": true,
"saveToRepository": true,
"usingDefaultReportOutputFolderURI": false,
"outputFTPInfo": {
"userName": "anonymous",
"password": "",
"pbsz": 0,
"implicit": true,
"type": "ftp",
"port": 21
}
},
"outputFormats": {
"outputFormat": [
"PDF"
]
}
} ]]></core:set>
<core:new className="java.io.OutputStreamWriter" var="wr">
<core:arg type="java.io.OutputStream" value="${connection.getOutputStream()}"/>
</core:new>
<core:set value="${wr.write(createJob)}" var="void"/>
<core:set value="${wr.flush()}" var="void"/>
<core:set value="${wr.close()}" var="void"/>
<core:set value="${connection.getHeaderField(0)}" var="isAuthenticated"/>
<core:set value="${connection.getResponseMessage()}" var="response_msg"/>
<core:choose>
<core:when test="${isAuthenticated == 'HTTP/1.1 200 OK' || isAuthenticated =='HTTP/1.1 200'}">
<core:set value="${connection.getInputStream()}" var="httpInputStream"/>
<core:new className="java.io.InputStreamReader" var="v_Input">
<core:arg type="java.io.InputStream" value="${httpInputStream}"/>
</core:new>
<core:new className="java.io.BufferedReader" var="v_InputData">
<core:arg type="java.io.InputStreamReader" value="${v_Input}"/>
</core:new>
<core:set value="${v_InputData.readLine()}" var="restResponse"/>
<core:if test="${bDebugRESTResponse}">
<gel:log>REST Response: ${restResponse}</gel:log>
</core:if>
<core:set value="${httpInputStream.close()}" var="dummy"/>
<core:if test="${bDebug}">
<gel:log>Parse the JSON... </gel:log>
</core:if>
<core:new className="org.json.JSONObject" var="jsonResp">
<core:arg type="java.lang.String" value="${restResponse}"/>
</core:new>
<gel:log>${jsonResp.toString()}</gel:log>
</core:when>
<core:otherwise>
<core:if test="${bDebug}">
<gel:log category="Rest Request" level="INFO" message=" ${isAuthenticated}"/>
</core:if>
</core:otherwise>
</core:choose>
</core:catch>
<core:if test="${v_global_exception != null}">
<gel:log level="ERROR" message="${v_global_exception.getMessage()}"/>
</core:if>
</gel:script>

Outcomes