Doubt in Gel script

[Riya Shree]

Hello, 

I have written a gel script that will send an email notification to resources who has not logged more than 5 months. When I execute the processes it should send an email to each user and in email that contain user details. 

Example: There are like 10 users who has logged more than 5 months once I run the processes each user should be notified to with their details. If email is sent to user A the email should contain only user A details. 

I have an issue like when I run the processes that email is sent to each users but it is fetching all the users row instead of fetching particular users. Could you please assist me here. 

Thank you! 

Haripriya

[David Morton]

There is presumably a bug in your script, so you would need to post some details for anybody to be able to help.

[Riya Shree]

<! --connecting to the database-->
<! --to get url-->
<! --to get username-->
<! --to get password-->
<core:set var="tableHeader"><! [CDATA[<table border=2 cellspacing=0 cellpadding=0 BORDERCOLOR="Black" style="border-collapse:collapse;width:100%">
<font size="3"><tr>
<th valign=top ><p class="MsoNormalTable">Last Name</p></th>
<th valign=top ><p class="MsoNormalTable">First Name</p></th>
<th valign=top ><p class="MsoNormalTable">Last Login date</p></th></core:set>
<core:catch var="v_fetchResource">
<sql:query escapeText="false" var="fetchResource">
Select distinct cm.email_address, sm. Email
From cmn_sec_users cm
Join srm_resources sm on cm.email_address=sm.email where cm.user_status_id='200' and (TRUNC(CURRENT_DATE) - TRUNC(last_logged_in_date) between 100 and 150)
</sql:query>
</core:catch>
<core:if test="${v_fetchResource != Null}>
<gel:log level="ERROR">SQL ERROR = ${v_fetchResource}</gel:log>
</core:if>
<core:choose>
<core:when test="${v_fetchResource.Rowcount != Null}">
<core:forEach items="${v_fetchResource.rows}" var="Resource">
<core:set value="${Resource.get('EMAIL_ADDRESS')}" var="EMAIL_ADDRESS"/>
<core:catch var="v_fetchUser">
<sql:query escapeText="false" var="fetchUser"><! [CDATA[
Select cm. Last_Name, cm. First_Name, cm.Last_Logged_in_date
From cmn_sec_users cm
Join srm_resources sm on cm.email_address=sm.email where cm.user_status_id='200' and (TRUNC(CURRENT_DATE) - TRUNC(last_logged_in_date) between 100 and 150)  and cm.email_address=? ]]>
<sql:param value="${EMAIL_ADDRESS}"/>
</sql:query>
</core:catch>
<core:if test="${v_fetchUser != Null}">
<gel:log level="ERROR">SQL ERROR = ${v_fetchUser}</gel:log>
</core:if test="${v_fetchUser == Null}">
<core:catch var="parse_excp">
<core:for each items="${v_fetchUser.rows}" var="row">
<core:set var="tableDetail"><! [CDATA[${tableDetail} <font size="2"> <tr><td valign=top style="padding-left: 3px"><p class="MsoNormalTable">${row.get('LAST_NAME')}</p></td>
<td valign=top style="padding-left: 3px"><p class="MsoNormalTable">${row.get('FIRST_NAME')}</p></td>
<td valign=top style="padding-left: 3px"><p class="MsoNormalTable">${row.get('LAST_LOGGED_IN_DATE')}</p></td></core:set>
</core:forEach>
<core:set var="notifyuser"><![CDATA[${tableHeader}${tableDetail}</table></br>]]></core:set>
<util:replace escapeText="false" new="" old="amp" value="${notifyuser}" var="notifyuser"/>
<core:invokeStatic className="org.apache.commons.lang.StringEscapeUtils" method="unescapeHtml" var="notifyuserList">
<core:arg type="java.lang.String" value="${notifyuserList}"/>
<core:invokeStatic>
<core:set value="License" var="email_subject"/>
<core:set value="[e-mail]" var="Sender"/>
<gel:email from="${Sender}" fromName="abc" subject="${email_subject}" to="${EMAIL_ADDRESS}">
<! [CDATA[
MESSAGE]]></gel:email>
</core:catch>
</core:if>
</core:when>
</core:choose>

[David Morton]

You looping "looks wrong" to me

I think you should be looping (foreach-ing) around the variables fetchResource not v_fetchResource (which is your "catch" variable)
( and around fetchUser not v_fetchUser )

--

Also, the "scope" of your foreach-loops looks wrong - you do all the looping and then construct a single email with all the results in it ; your code that sends the email should be within the last loop.

--

Functionally though, I would not have even thought you needed two loops ; a single SQL statement (probably the second one in your code) can get all the information that you need - then just loop around the result of that single SQL statement, sending an email each iteration of the loop

[David Morton]

"I modified my query by adding forEach loop around the variable fetchResource even though it is providing same output as for first email it is fetching one row. For second email it is fetching 1st and 2nd row. Third email it is fetching 1st, 2nd and 3rd row."

That just sounds like you are adding to one of your variables each time you iterate around the loop rather than resetting the  variable each iteration (i.e. for each new email).

--

If you are struggling with the "flow" of your code, you can add debug to it (gel:log and/or gel:out statements depending on how you are running your GEL) and trace what is happening where in your script. 

If you don't really understand GEL then the documents contained in the Gel.zip file attached to the old FAQ thread here ; CA Clarity General Discussion - FAQs are very useful in understanding how to work with GEL.