gel:script - regoUniversity

Report
Hidden
Automation
The Power of GEL Scripting to Enhance Functionality
www.regoconsulting.com
Phone: 1-888-813-0444
Topics
●
●
●
●
●
●
●
Background of GEL
Basic Script Structure
Commonly Used Tags
Hands on exercises with creating GEL Scripts
Best Practices
Real-life Examples
Q&A
2
www.regoconsulting.com
Phone: 1-888-813-0444
Background of GEL
● GEL (Generic Execution Language) is a tool you can use to turn
XML into executable code. It is based on Jelly, a
jakarta.apache.org Commons project. It has been extended
and embedded into CA Clarity PPM to enable custom logic to
solve business problems.
● Additional information can be found in the CA Documentation
(CAClarityPPM_XOG_DeveloperGuide_ENU.pdf) and at the
Apache Jelly website at
http://jakarta.apache.org/commons/jelly/index.html.
3
www.regoconsulting.com
Phone: 1-888-813-0444
GEL Setup
● The GEL run-time is packaged with XOG in the XOG client.
Once the client is installed, you can use the GEL command in
the bin directory of the XOG client to validate and execute GEL
scripts.
● GEL can also be used within Clarity processes.
4
www.regoconsulting.com
Phone: 1-888-813-0444
Script Structure
Header
Comment
Footer
<gel:script
xmlns:core="jelly:core"
xmlns:gel="jelly:com.niku.union.gel.GELTagLibrary"
xmlns:sql="jelly:sql"
xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<!-- CODE GOES HERE -->
</gel:script>
5
www.regoconsulting.com
Phone: 1-888-813-0444
GEL Tags
A GEL script is an executable XML file that is built from qualified
elements bound to Java code called tags. Using namespace
declarations, tags are organized into tag libraries which are made
available in a script.
Hello World Example:
<gel:script xmlns:core="jelly:core"
xmlns:gel="jelly:com.niku.union.gel.GELTagLibrary">
<core:forEach indexVar='i' begin='1' end='3'>
<gel:out>Hello World ${i}!</gel:out>
</core:forEach>
</gel:script>
● Note: An entire script always resides within the GEL script tag.
6
www.regoconsulting.com
Phone: 1-888-813-0444
Common Namespaces
xmlns:core="jelly:core"
xmlns:gel="jelly:com.niku.union.gel.GELTagLibrary"
xmlns:sql="jelly:sql"
xmlns:xog=http://www.niku.com/xog"
xmlns:soap="jelly:com.niku.union.gel.SOAPTagLibrary"
xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:file="jelly:com.niku.union.gel.FileTagLibrary"
xmlns:util="jelly:util"
xmlns:email="jelly:email"
xmlns:ftp="jelly:com.niku.union.gel.FTPTagLibrary"
7
www.regoconsulting.com
Phone: 1-888-813-0444
Variables
Variables are used extensively throughout GEL scripts. Many tags can
set variables. An example of a tag that can set variables is core:set. You
can use the common syntax ${variable_name} to reference variables.
In the Hello World example, 'i' is a variable which is set by the forEach
tag and is incremented with each loop.
Hello World Example:
<gel:script xmlns:core="jelly:core"
xmlns:gel="jelly:com.niku.union.gel.GELTagLibrary">
<core:forEach indexVar='i' begin='1' end='3'>
<gel:out>Hello World ${i}!</gel:out>
</core:forEach>
</gel:script>
8
www.regoconsulting.com
Phone: 1-888-813-0444
Commonly Used Tags
●
●
●
●
●
●
●
●
Conditionals / Loops
Variables / Parameters
Database Operations
Logging
Exception
SOAP / XOG
File Operations
Email
9
www.regoconsulting.com
Phone: 1-888-813-0444
Conditionals / Loops
● <core:if>
<core:if test="${hasDocs}">
…
</core:if>
● <core:choose>
<core:choose>
<core:when test="${row[6].equals(&quot;&quot;)}">
…
</core:when>
<core:otherwise>
…
</core:otherwise>
</core:choose>
10
www.regoconsulting.com
Phone: 1-888-813-0444
Conditionals / Loops cont.
● <core:forEach>
<core:forEach trim="true" items="${queryResult.rows}" var="row">
…
</core:forEach>
● <gel:forEach>
<gel:forEach select="$projectsXML/NikuDataBus/Projects/Project"
var="currentPrj">
…
</gel:forEach>
● Note that there are two separate forEach loop types. The core version
performs basic FOR looping. If you need to retrieve values from an XML
document to use in the loop condition (that is, need to use a SELECT
clause), then you need the GEL implementation.
11
www.regoconsulting.com
Phone: 1-888-813-0444
Variables
● <gel:parameter>
This tag allows values to be passed into a GEL script from a CA Clarity PPM
process. Inside the GEL script, you can refer to the parameter as you would
any other variable (that is, using the ${variablename} syntax). The optional
attribute secure="true" causes CA Clarity PPM to hide the actual value in the
user interface with asterisks (*).
<gel:parameter var="XOGUsername" default="admin"/>
<gel:parameter var="XOGPassword" default="password" secure="true"/>
12
www.regoconsulting.com
Phone: 1-888-813-0444
Variables cont.
● <core:set>
This tag is used to set basic variables; that is, ones that do not need to be extracted from an
XML document. Refer to the variable using the ${variablename} syntax.
<core:set value="1" var="yes"/>
<gel:out>${yes}</gel:out>
You can do some basic math on the variable:
<gel:out>${yes+2}</gel:out>
● <gel:set>
Use this tag when it is necessary to extract the value of the variable from an XML
document. This tag differs from the <core:set> tag in that it takes a select attribute which in
turn requires an XPath statement. If you are unfamiliar with XPath, think of it as a hierarchy
mapping of the XML document. In the example below, the select statement points the way
to the Statistics node of a XOG output file.
<gel:set asString="true" select="$result//XOGOutput/Statistics" var="xogStats"/>
13
www.regoconsulting.com
Phone: 1-888-813-0444
Variables cont.
● <gel:persist>
This tag allows you to set variables with a scope that extends beyond the current script.
● <gel:parse>
The <gel:parse> tag is used to create an XML document in memory. This is how you will build XOG
requests. The tag can be used to generate an entire XML document, or specific nodes that can later be
attached into an existing XML document.
<gel:parse var="loadContent">
<NikuDataBus xmlns:xsi=http://www.w3.org/2001/XMLSchema-instance
xsi:noNamespaceSchemaLocation="../xsd/nikuxog_resource.xsd">
<Header version="12.0.0.5028" action="write" objectType="resource“ externalSource=“PS"/>
<Resources>
<Resource resourceId="abc" isActive="true">
<PersonalInformation lastName="doe" firstName="john" emailAddress="[email protected]"/>
</Resource>
</Resources>
</NikuDataBus>
</gel:parse>
14
www.regoconsulting.com
Phone: 1-888-813-0444
Built-in Parameters
Custom Action GEL scripts associated with processes have the
following parameters available to them:
● Object instance ID
If no object is associated with the process, the ID is -1. Otherwise the
${gel_objectInstanceId} parameter contains the object instance ID.
● Process ID
${gel_processId} is the process identifier; all instances of this process
share this identifier.
● Process instance ID
${gel_processInstanceId} is the process instance identifier; all instances
have a unique value.
● All built-in parameters have a "gel_" prefix and are of data type numeric.
15
www.regoconsulting.com
Phone: 1-888-813-0444
Case Sensitive Issues
Information contained within GEL tags is case sensitive. For
example, if you declare a variable as follows:
<core:set var="v_ProjectID">PRJ-123456</core:set>
Then reference the variable as follows:
<gel:out>${v_projectid}</gel:out>
This will not output PRJ-123456. However, this will not cause an
actual error to occur, so you can’t “catch” this error.
16
www.regoconsulting.com
Phone: 1-888-813-0444
Database Operations
●
●
●
●
●
Datasources
SQL Queries
SQL Updates
SQL Bind Variables / Parameters
Transactions
17
www.regoconsulting.com
Phone: 1-888-813-0444
Datasources
There are a couple of methods you can use within GEL to connect to
your Clarity database. The recommended method is to use:
<gel:setDataSource/>
The tag takes the database id attribute as follows:
<gel:setDataSource dbId="Niku" [var="clarityDS"]/>
The above tag takes the connection properties (including password)
from Clarity’s properties (set in the CSA). Niku, in the above example,
is the ID of the default internal Clarity database. If you create
additional external database definitions, you can reference them in the
same manner. For example, if you added a second database
connection definition called “ORA-FIN” you could reference it as
follows:
<gel:setDataSource dbId="ORA-FIN"/>
18
www.regoconsulting.com
Phone: 1-888-813-0444
Datasources cont.
Note that the var attribute is optional. If you do not specify a variable, the tag
uses the default, and all subsequent SQL calls will use the same default. For
example, you can do:
<gel:setDataSource dbId="Niku"/>
<sql:query var="result">
SELECT 1 from dual
</sql:query>
OR
<gel:setDataSource dbId="Niku" var="clarityDS"/>
<sql:query dataSource="${clarityDS}" var="result">
SELECT 1 from dual
</sql:query>
19
www.regoconsulting.com
Phone: 1-888-813-0444
Datasources cont.
<sql:setDataSource>
Reasons to use the <sql:setDataSource> tag
● You are unable to add the database connection to the CSA
● You need to connect to a DB that isn’t Oracle or SQL Server
● You want to run the script from the command line and don’t have a
copy of the properties.xml file on your computer
<sql:setDataSource url="jdbc:oracle:thin:@localhost:1521:NIKU”
driver="oracle.jdbc.driver.OracleDriver” user="${ClarityUser}"
password="${ClarityPassword}" var="clarityDS"/>
Note: The above example allows you to have more than one
datasource defined, and to specify which datasource to use through
the “clarityDS” variable.
20
www.regoconsulting.com
Phone: 1-888-813-0444
SQL Query
The following is an example of a simple SQL query called from within a GEL script
<sql:query dataSource="${clarityDS}" escapeText="0" var="result">
<![CDATA[
SELECT r.full_name resName,
r.email resEmail
FROM srm_resources r
]]>
</sql:query>
This query returns data in an array (for all intents and purposes) with the name
“result.”
Note: Due to the nature of XML, using certain characters (‘<‘, ‘>’) within your
query would cause an error without enclosing the query in the CDATA tag.
Because of this, it is a best practice to include this tag in all queries.
21
www.regoconsulting.com
Phone: 1-888-813-0444
SQL Query cont.
You can then access the data in the “result” array using the <core:forEach>
tag. There are a couple of different ways to do this:
<!-- By Index -->
<core:forEach trim="true" items="${result.rowsByIndex}" var="row">
<gel:out>Resource Name: ${row[0]}</gel:out>
</core:forEach>
<!-- By Column Name (Preferred Method) -->
<core:forEach trim="true" items="${result.rows}" var="row">
<gel:out>Resource Name: ${row.resName}</gel:out>
</core:forEach>
Note: When possible, avoid setting a bunch of variables with the results of
the query.
22
www.regoconsulting.com
Phone: 1-888-813-0444
SQL Update
Often times processes require updating the values of specific
attributes. One of the ways to do this is with a SQL Update statement.
Keep in mind the following when performing direct database updates
● Generally speaking, it is best to avoid Insert statements – these are
best suited for XOG
● Direct database updates will not trigger a process to start – XOG
updates typically will trigger processes to start
● Avoid updating OOTB tables when possible – using XOG will ensure
all Clarity business rules are followed
● It is generally safe to update custom attributes with a SQL update.
These are typically found in tables beginning with odf_ca
● Extra caution should also be used within On-demand environments
23
www.regoconsulting.com
Phone: 1-888-813-0444
SQL Update cont.
Example
<sql:update dataSource="${clarityDS}" escapeText="0"
var="updateCnt">
<![CDATA[
UPDATE odf_ca_project ocp
SET
ocp.rego_appr_date = sysdate
WHERE ocp.id = ${gel_objectInstanceId}
]]>
</sql:update>
The variable ${updateCnt} will contain the number of rows that the
update statement affects
Note: Using CDATA tags in update statements is also preferred.
24
www.regoconsulting.com
Phone: 1-888-813-0444
SQL Bind Variables / Parameters
Many times SQL statements will include variables that are passed in to the statement. It is best to set
these up as parameters within the SQL Statement. This serves a couple of purposes:
● Ensures data types are correct
● Prevents SQL injection
● Allows reuse of SQL statements for performance gains
Example:
<sql:update dataSource="${clarityDS}" escapeText="0" var="updateCnt">
<![CDATA[
UPDATE odf_ca_project ocp
SET
ocp.rego_appr_date = sysdate,
ocp.rego_resource = ?
WHERE ocp.id = ?
]]>
<sql:param value="${row.resourceId}"/>
<sql:param value="${gel_objectInstanceId}"/>
</sql:update>
Note: You must have a <sql:param> tag for each ? in the SQL statement, even if they are using the same
value. The parameters also must be placed in the order that they appear in the statement.
25
www.regoconsulting.com
Phone: 1-888-813-0444
SQL Transaction
●
Very Useful if you need your updates to be “All or Nothing”
<sql:transaction dataSource="${clarityDS}">
<core:set value="0" var="errorCnt"/>
<core:catch var="sqlException">
<sql:update escapeText="0" var="updateCnt">
<!-- SQL UPDATE -->
</sql:update>
</core:catch>
<core:if test="${!empty sqlException}">
<core:set value="1" var="errorCnt"/>
</core:if>
<!-- COMMIT OR ROLLBACK SQL TRANSACTION -->
<core:choose>
<core:when test="${errorCnt == 0}">
<!-- COMMIT TRANSACTIONS -->
<sql:update>COMMIT</sql:update>
</core:when>
<core:otherwise>
<!-- ROLLBACK TRANSACTIONS -->
<sql:update>ROLLBACK</sql:update>
</core:otherwise>
</core:choose>
</sql:transaction>
26
www.regoconsulting.com
Phone: 1-888-813-0444
Logging Messages
● Use this tag to insert status messages into the process engine
log table
● Very useful when debugging scripts
● Avoid using too many logging statements, as it can impact
performance
● Different levels of logging – INFO, WARN, ERROR
<gel:log level="INFO">This is an example log message.</gel:log>
27
www.regoconsulting.com
Phone: 1-888-813-0444
Exceptions
Use the GEL fault-handling tags to catch exceptions and exit gracefully when a process
failure occurs. Use the <core:catch> tag to capture exceptions into a variable. Outside of
the catch tags, you can check the variable and write it to the console.
Example:
<core:catch var="sqlException">
<sql:query dataSource="${clarityDS}" escapeText="0" var="result">
<![CDATA[
SELECT r.id,
FROM srm_resources r
]]>
</sql:query>
</core:catch>
<core:if test="${!empty sqlException}">
<gel:log level="ERROR">Exception: ${sqlException}</gel:log>
</core:if>
28
www.regoconsulting.com
Phone: 1-888-813-0444
Hands On Exercises
Clarity Environment:
http://54.193.48.58:8081/niku/nu#action:homeActionId
Username: regou
Password: Clarity123
Note: When creating processes, please distinguish your process
from others by using your name in the process name and id
Example:
29
www.regoconsulting.com
Phone: 1-888-813-0444
Limitations to Rego U Environment
● URLs are not set up correctly in the properties.xml file. This
means that if you need to reference the URL, you’ll have to
hard-code it.
Example:
<gel:parameter var=“URL" default="http://54.193.48.58:8081"/>
● Caching Issues
30
www.regoconsulting.com
Phone: 1-888-813-0444
Exercise #1
Create a process that uses a GEL script to update the following fields
on the project object:
Approved By (rego_appr_by): Resource that started the process
Approval Date (rego_appr_date): Today’s Date
Note: Environment is an Oracle environment
Approved By field uses the Resource Browse lookup (srm_resources.id)
31
www.regoconsulting.com
Phone: 1-888-813-0444
Exercise #1 Hint
SQL to update project fields:
UPDATE odf_ca_project ocp
SET
ocp.rego_appr_date = sysdate,
ocp.rego_appr_by = (SELECT r.id
FROM bpm_run_processes brp
JOIN srm_resources r ON brp.initiated_by = r.user_id
WHERE brp.id = ?)
WHERE ocp.id = ?
32
www.regoconsulting.com
Phone: 1-888-813-0444
Exercise #1 Review
33
www.regoconsulting.com
Phone: 1-888-813-0444
SOAP / XOG
By including the SOAP and XOG namespaces in GEL scripts, you give
GEL the ability to communicate with the XOG web service. You must
package each invocation in a proper SOAP envelope.
The following steps must be used within a GEL script to communicate
with the XOG web service:
●
●
●
●
●
●
Include the proper namespaces
Obtain a session ID
Create the XML file to send
Execute the XOG
Parse the Results
Logout
34
www.regoconsulting.com
Phone: 1-888-813-0444
XOG - Include the proper namespaces
<gel:script xmlns:core="jelly:core“
xmlns:gel="jelly:com.niku.union.gel.GELTagLibrary“
xmlns:xog="http://www.niku.com/xog"
xmlns:soap="jelly:com.niku.union.gel.SOAPTagLibrary"
xmlns:soapenv="http://schemas.xmlsoap.org/soap/envel
ope/">
35
www.regoconsulting.com
Phone: 1-888-813-0444
XOG - Obtain a Session ID
<!-- Get sessionId by username -->
<gel:parameter var="username" default="admin"/>
<core:new className="com.niku.union.security.DefaultSecurityIdentifier" var="secId" />
<core:invokeStatic var="userSessionCtrl"
className="com.niku.union.security.UserSessionControllerFactory" method="getInstance"
/>
<core:set var="secId" value="${userSessionCtrl.init(username, secId)}"/>
<core:set var="XOGUsername" value="${secId.getUserName()}"/>
<core:set var="sessionID" value="${secId.getSessionId()}"/>
<core:choose>
<core:when test="${sessionID == null}">
<gel:log level="ERROR"> Unable to obtain a Session ID. </gel:log>
</core:when>
<core:otherwise>
<!-- Execute XOG -->
</core:otherwise>
</core:choose>
36
www.regoconsulting.com
Phone: 1-888-813-0444
XOG - Create the XML File
Example:
<gel:parse var="userXML">
<NikuDataBus xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:noNamespaceSchemaLocation="../xsd/nikuxog_user.xsd">
<Header action="write" externalSource="NIKU" objectType="user" version="13.2.0.472"/>
<Users>
<User externalId=" " isLDAP="false" uiThemeDefaultPartitionCode=" "
userLanguage="English" userLocale="en_US” userName="${row.userName}"
userStatus="${row.userStatus}" userTimezone="America/Los_Angeles"
userType="INTERNAL">
<PersonalInformation emailAddress="${row.email}" firstName="${row.firstName}"
lastName="${row.lastName}"/>
<Resource resourceId="${row.resourceId}"/>
<Groups/>
</User>
</Users>
</NikuDataBus>
</gel:parse>
37
www.regoconsulting.com
Phone: 1-888-813-0444
XOG - Execute the XOG
<!-- Execute XOG -->
<soap:invoke endpoint=“internal" var="result">
<soap:message>
<soapenv:Envelope
xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:xog="http://www.niku.com/xog">
<soapenv:Header>
<xog:Auth>
<xog:SessionID>${sessionID}</xog:SessionID>
</xog:Auth>
</soapenv:Header>
<soapenv:Body>
<gel:include select="$userXML"/>
</soapenv:Body>
</soapenv:Envelope>
</soap:message>
</soap:invoke>
38
www.regoconsulting.com
Phone: 1-888-813-0444
XOG - Parse the Results
<!-- Parse Results -->
<gel:set asString="true" select="[email protected]" var="XOGState"/>
<core:choose>
<!-- Success -->
<core:when test="${XOGState == 'SUCCESS'}">
<gel:set asString="true" select="$result//XOGOutput/Statistics" var="xogStats"/>
<gel:log level="INFO">User XOG Stats: ${xogStats} </gel:log>
</core:when>
<!-- Failure -->
<core:otherwise>
<gel:log level="WARN"><gel:expr select="$userXML/"/></gel:log>
<gel:log level="ERROR"><gel:expr select="$result/"/></gel:log>
</core:otherwise>
</core:choose>
39
www.regoconsulting.com
Phone: 1-888-813-0444
XOG - Logout
<!-- Logout XOG-->
<soap:invoke endpoint=“internal" var="result">
<soap:message>
<soapenv:Envelope
xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:xog="http://www.niku.com/xog">
<soapenv:Header>
<xog:Auth>
<xog:SessionID>${sessionID}</xog:SessionID>
</xog:Auth>
</soapenv:Header>
<soapenv:Body>
<xog:Logout/>
</soapenv:Body>
</soapenv:Envelope>
</soap:message>
</soap:invoke>
40
www.regoconsulting.com
Phone: 1-888-813-0444
Exercise #2
Create a process that can be initiated from a custom object for a
Project Request that will XOG in a new project
• Create project using the
Name and ID of the Project
Request
41
www.regoconsulting.com
Phone: 1-888-813-0444
Exercise #2 Hint
Sample Project XOG:
<NikuDataBus xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:noNamespaceSchemaLocation="../xsd/nikuxog_project.xsd">
<Header version="6.0.11" action="write" objectType="project"
externalSource="NIKU"/>
<Projects>
<Project name="Test Project" projectID="PR1234" />
</Projects>
</NikuDataBus>
SQL to get Project Request Name and ID:
SELECT
FROM
WHERE
pr.code prjCode,
pr.name prjName
odf_ca_rego_prj_req pr
pr.id = ?
42
www.regoconsulting.com
Phone: 1-888-813-0444
Exercise #2 Review
43
www.regoconsulting.com
Phone: 1-888-813-0444
File Operations
GEL can open a file (and if it is an XML file or a comma-delimited
file, parse out all the nodes and attributes), read the file, and
write to it. It can also perform FTP operations on files. It cannot,
however, create a directory to put files in, move files around, or
delete files after it is done with them.
44
www.regoconsulting.com
Phone: 1-888-813-0444
File Operations – Read File
<gel:script xmlns:core="jelly:core"
xmlns:gel="jelly:com.niku.union.gel.GELTagLibrary"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:files="jelly:com.niku.union.gel.FileTagLibrary">
<gel:parameter var="vFileName" default="/fs0/clarity1/share/RESOURCES.CSV"/>
<files:readFile fileName="${vFileName}" delimiter="\|" var="vResourceData"
embedded="false"/>
<core:forEach items="${vResourceData.rows}" var="row" begin="1" end="10">
<gel:log level="INFO"> Resource Last Name: ${row[0]} </gel:log>
<gel:log level="INFO"> Resource First Name: ${row[1]} </gel:log>
</core:forEach>
</gel:script>
45
www.regoconsulting.com
Phone: 1-888-813-0444
File Operations – Write File
<file:writeFile delimiter="," embedded="false" fileName=" Resources.csv ">
<sql:query dataSource="${clarityDS}" escapeText="0" var="result">
<![CDATA[
SELECT u.first_name firstName,
u.last_name lastName,
u.user_name userName
FROM cmn_sec_users u
WHERE u.user_status_id = 200
]]>
</sql:query>
<core:forEach items="${result.rows}" trim="true" var="row">
<file:line>
<file:column value="${row.userName}"/>
<file:column value="${row.lastName}"/>
<file:column value="${row.firstName}"/>
</file:line>
</core:forEach>
</file:writeFile>
46
www.regoconsulting.com
Phone: 1-888-813-0444
Email
GEL scripts are often used to send emails. Using a GEL script
notification allows for a lot more flexibility than sending an
action item. There are two types of email tags, <gel:email> and
<email:email>. It is recommended to use the <gel:email> tag
where possible.
47
www.regoconsulting.com
Phone: 1-888-813-0444
Email cont.
<gel:email>
● Email server information is derived from the properties.xml of the installation.
● Supports HTML
Example:
<gel:script xmlns:gel="jelly:com.niku.union.gel.GELTagLibrary">
<gel:email from="[email protected]" subject="Clarity - Test Email" to="[email protected]">
<![CDATA[
This is a test email.
<br/>
<br/>
------------------------------------------------------------------<br/>
This is an automated message, please do not reply.
]]>
</gel:email>
</gel:script>
48
www.regoconsulting.com
Phone: 1-888-813-0444
Email cont.
<email:email>
● Mail Server must be specified within the tag
● Does not support HTML
● Supports Attachments
Example:
<gel:script xmlns:core="jelly:core"
xmlns:gel="jelly:com.niku.union.gel.GELTagLibrary"
xmlns:email="jelly:email">
<core:invokeStatic className="java.lang.System" method="getenv" var="NIKU_HOME">
<core:arg value="NIKU_HOME"/>
</core:invokeStatic>
<gel:parse file="${NIKU_HOME}/config/properties.xml" var="properties"/>
<gel:set asString="true" select="[email protected]" var="mailServer"/>
<email:email to="[email protected]" from="[email protected]" subject=“app-ca.log file"
server="${mailServer}" attach="${NIKU_HOME}/logs/app-ca.log">
App-ca.log File
</email:email>
</gel:script>
49
www.regoconsulting.com
Phone: 1-888-813-0444
Exercise #3
Write a script that will email a list of all active projects with
managers that are not active users in Clarity to the resources
within the following group:
50
www.regoconsulting.com
Phone: 1-888-813-0444
Exercise #3 Hint
SQL to get Resources in Group:
SELECT r.first_name || ' ' || r.last_name resName,
r.email resEmail
FROM cmn_sec_user_groups ug
JOIN cmn_sec_groups g ON ug.group_id = g.id AND g.group_code = 'regou_gel_script'
JOIN srm_resources r ON r.user_id = ug.user_id
SQL to get Projects:
SELECT i.id prjId,
i.code prjCode,
i.name prjName,
r.full_name prjMgr
FROM inv_investments i
JOIN inv_projects ip ON ip.prid = i.id AND ip.is_template = 0 AND ip.is_program = 0 AND (i.purge_flag =
0 OR i.purge_flag IS NULL)
JOIN odf_ca_project ocp ON i.id = ocp.id
JOIN cmn_sec_users u ON i.manager_id = u.id AND u.user_status_id != 200
JOIN srm_resources r ON u.id = r.user_id
Send Emails from the following email address: [email protected]
51
www.regoconsulting.com
Phone: 1-888-813-0444
Exercise #3 Review
52
www.regoconsulting.com
Phone: 1-888-813-0444
Best Practices
● If a variable can be changed by an admin, or stores a password, use
the <gel:parameter> tag instead of <core:set>
● Comment your code
● Properly format and indent your code
● Place all code within the <gel:script> tags, even comments
● Avoid excessive logging, but keep enough for debugging purposes
● When possible, pull server info from properties file on server
Example:
<core:invokeStatic className="java.lang.System" method="getenv" var="NIKU_HOME">
<core:arg value="NIKU_HOME"/>
</core:invokeStatic>
<gel:parse file="${NIKU_HOME}/config/properties.xml" var="properties"/>
<gel:set asString="true"
select="$properties/properties/webServer/webServerInstance[@id='app'][email protected]"
var="URL"/>
53
www.regoconsulting.com
Phone: 1-888-813-0444
Real Life Examples
●
●
●
●
Integrations with HR
Integrations with Financial systems
Timesheet Notifications
Updating Resource Rights
● What have you used GEL scripts for?
54
www.regoconsulting.com
Phone: 1-888-813-0444
Questions
Contact US
888.813.0444
Email Contact
[email protected]
Web Site
www.regoconsulting.com
55
www.regoconsulting.com
Phone: 1-888-813-0444

similar documents