<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">
<!-- Set Variables -->
<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="propertiesXML"/>
<gel:parameter default="" var="senderEmailAddress"/> (here is where I am adding my email address)
<gel:set asString="true" select="$propertiesXML/properties/webServer/webServerInstance[@id='app']/@sslEntryUrl" var="appURL"/>
<gel:set asString="true" select="$properties/properties/mailServer/@defaultSenderAddress" var="senderEmailAddress"/>
<gel:set asString="true" select="$properties/properties/mailServer/@host" var="mailServer"/>
<gel:log level="INFO">App URL: ${appURL}</gel:log>
<!-- Get a DB Connection to Clarity -->
<gel:setDataSource dbId="Niku" />
<!-- QUERY FOR PROJECT INFO -->
<sql:query escapeText="0" var="result">
<![CDATA[
SELECT
INV.ID intProjectID,
INV.CODE ProjectID,
INV.NAME ProjectName,
REPLACE(ODF.OBJ_OBJECTIVE,CHR(10),'<br/>') Objective,
(SELECT SRM.first_name || ' ' || SRM.last_name FROM SRM_RESOURCES SRM WHERE SRM.ID = odf.it_pm) itProjectManager,
(SELECT SRM.first_name || ' ' || SRM.last_name FROM SRM_RESOURCES SRM WHERE SRM.USER_ID = inv.manager_id) projectmanager,
TO_CHAR(INV.SCHEDULE_START,'dd-Mon-YYYY') strt,
TO_CHAR(INV.SCHEDULE_FINISH,'dd-Mon-YYYY') fin
FROM
INV_INVESTMENTS INV
JOIN
ODF_CA_project ODF ON ODF.ID = INV.ID
JOIN
odf_ca_inv ODFINV ON ODFINV.ID = INV.ID
WHERE
INV.ODF_OBJECT_CODE='project'
AND INV.ID = ?
]]>
<sql:param value="${gel_objectInstanceId}"/>
</sql:query>
<gel:log level="INFO">projectIntID ${gel_objectInstanceId}</gel:log>
<gel:log level="INFO">Row Count ${result.rowCount}</gel:log>
<core:if test="${result.rowCount > 0}">
<!-- QUERY FOR EMAIL INFO -->
<sql:query escapeText="0" var="result2">
<![CDATA[
SELECT DISTINCT * FROM
(SELECT
INV.ID intProjectID,
INV.CODE ProjectID,
INV.NAME ProjectName,
(SELECT SRM.id FROM SRM_RESOURCES SRM WHERE SRM.user_ID = inv.manager_id) resid,
(SELECT SRM.email FROM SRM_RESOURCES SRM WHERE SRM.user_ID = inv.manager_id) resemail,
(SELECT SRM.first_name || ' ' || SRM.last_name FROM SRM_RESOURCES SRM WHERE SRM.USER_ID = inv.manager_id) resname
FROM
INV_INVESTMENTS INV
JOIN
ODF_CA_project ODF ON ODF.ID = INV.ID
JOIN
odf_ca_inv ODFINV ON ODFINV.ID = INV.ID
WHERE
INV.ODF_OBJECT_CODE='project'
UNION
SELECT
INV.ID intProjectID,
INV.CODE ProjectID,
INV.NAME ProjectName,
(SELECT SRM.ID FROM SRM_RESOURCES SRM WHERE SRM.ID = odf.it_pm) resid,
(SELECT SRM.email FROM SRM_RESOURCES SRM WHERE SRM.ID = odf.it_pm) resemail,
(SELECT SRM.first_name || ' ' || SRM.last_name FROM SRM_RESOURCES SRM WHERE SRM.ID = odf.it_pm) resname
FROM
INV_INVESTMENTS INV
JOIN
ODF_CA_project ODF ON ODF.ID = INV.ID
JOIN
odf_ca_inv ODFINV ON ODFINV.ID = INV.ID
WHERE
INV.ODF_OBJECT_CODE='project'
)
WHERE intProjectID =?
]]>
<sql:param value="${gel_objectInstanceId}"/>
</sql:query>
<core:forEach items="${result2.rows}" trim="true" var="row2">
<gel:log trim="false"> Sending Email... Email Recipient: ${row2.resEmail}</gel:log>
<core:catch var="mailException">
<gel:email from="${senderEmailAddress}" subject="PPM Notification for You – A Project is Submitted for Gate Approval" to="${row2.resEmail}">
<![CDATA[
<html>
<head>
<meta charset="UTF-8">
<style>
#emailBody {
font-family : Calibri, arial;
font-size: 14px;
}
table.myDataTable {
border-collapse: collapse;
border: 1px solid black;
width: 900px;
}
table.myDataTable td.sectionHeader {
border: 1px solid black;
background-color: #6d737c;
font-family : Calibri, arial;
font-size: 14px;
color: #ffffff;
font-weight: bold;
padding: 6px;
padding-left: 8px;
text-align:left;
}
table.myDataTable td.vals {
border: 1px solid black;
font-family : Calibri, arial;
font-size: 13px;
color: #000000;
font-weight: normal;
padding: 6px;
padding-left: 8px;
vertical-align: top;
}
</style>
</head>
<body><div id="emailBody">
Dear ${row2.resName},<br/>
<br/>
]]>
<![CDATA[
The following project is submitted for gate approval. You will be notified once the project is approved/rejected by the approvers.
<br/><br/>
<table class="myDataTable">
<tr>
<td class="sectionHeader"> Project ID </td>
<td class="sectionHeader"> Project Name </td>
<td class="sectionHeader"> Project Manager </td>
<td class="sectionHeader"> IT Project Manager </td>
<td class="sectionHeader"> Start Date </td>
<td class="sectionHeader"> Finish Date </td>
</tr>
]]>
<core:forEach items="${result.rows}" trim="true" var="row">
<![CDATA[
<tr>
<td class="vals">${row.ProjectID}</td>
<td class="vals">${row.ProjectName}</td>
<td class="vals">${row.projectmanager}</td>
<td class="vals">${row.itProjectManager}</td>
<td class="vals">${row.strt}</td>
<td class="vals">${row.fin}</td>
</tr>
<tr>
<td class="sectionHeader" colspan="6">Objective</td>
</tr>
<tr>
<td class="vals" colspan="6">${row.Objective}</td>
</tr>
]]>
</core:forEach>
<![CDATA[
</table>
<br/>
<strong>Note:</strong>This is an system generated email. Please do not reply.
]]>
</gel:email>
</core:catch>
<core:if test="${!empty mailException}">
<gel:log>${mailException}</gel:log>
</core:if>
</core:forEach>
</core:if>
</gel:script>