GEL script not sending emails

[matej256]

Hi All,

 

I've a question if somebody experienced in GEL has ever met with issue with not sending emails in GEL script. Mentioned script runs smoothly without any errors and included SQL gives me 4 results which are expected as

sent 4 emails to PMs in Clarity. In spite of that no emails are sent. Sending emails in CLarity is working fine for us (tried another processes) so it's not Email server issue. I've tried to also Query withou Sum, Min, Max function, Withou CDATA also, nothing helped.

 

Could you please advice what it could be? Thanks a lot

 

Matej

<gel:script xmlns:gel = "jelly:com.niku.union.gel.GELTagLibrary"
  xmlns:core = "jelly:core"
  xmlns:sql = "jelly:sql"
  xmlns:soap = "jelly:com.niku.union.gel.SOAPTagLibrary" 
  xmlns:file="jelly:com.niku.union.gel.FileTagLibrary"  
    xmlns:soap-env="http://schemas.xmlsoap.org/soap/envelope/" >

  <gel:parameter default="niku" var="dbID"/>
  <gel:parameter default="http://localhost:8080" var="XOGURL"/>
  <gel:parameter default="***" var="***"/>
  <gel:parameter default="***" secure="true" var="***"/>

  <gel:setDataSource dbId = "niku" var = "DBfetch"/>

   <!-- Retrieve Affected projects with PjMs for who emails will be sent -->

  <core:catch var = "message1">
  <sql:query dataSource = "${DBfetch}" escapeText = "false" var = "sqlResult">
  <![CDATA[
          WITH act_table AS 
  (
  select w.project_code, ii.name, pwv.totalcost, w.transdate start_date, w.transdate end_date, w.lastupdatedate,
  (select email from srm_resources where user_id = ii.manager_id) manager_email,
  (select full_name from srm_resources where user_id = ii.manager_id) manager_name, ii.id project_id
  from ppa_wip w
  join ppa_wip_values pwv on pwv.transno = w.transno 
  inner join inv_investments ii on w.project_code = ii.code 
  Where  w.lastupdatedate like To_char(SYSDATE, 'DD-MON-RR') 
  and pwv.currency_type = 'HOME' 
  and w.status != 2 
  and w.status != 4 
  )
  select act.project_code, act.name, sum(act.totalcost) actuals, to_char(min(act.start_date),'YYYY-MM') start_month, 
  to_char(max(act.start_date),'YYYY-MM') end_month, act.manager_email, act.manager_name, act.project_id
  from act_table act 
  GROUP BY act.project_code, act.name, act.manager_email, act.manager_name, act.project_id
  ]]>
  </sql:query>
  </core:catch>

  <core:catch var = "message">
  <core:forEach items = "${sqlResult.rowsByIndex}" trim="false" var="row">
<gel:email from="[e-mail]" fromName="Clarity"
            subject="New actuals received on your project ${row[1]} in Clarity" to="${row[5]}">Dear ${row[6]},&lt;BR>

  Please find following Clarity project data about your project: &lt;BR>

  &lt;BR>
  - Project Code: ${row[0]}&lt;BR>
  - Project Name: ${row[1]}&lt;BR>
  - Updated Actual Costs: ${row[2]}&lt;BR>
  - Start Month Period: ${row[3]}&lt;BR> 
  - End Month Period: ${row[4]}&lt;BR>

  &lt;BR>
  &lt;BR>
  Best regards,&lt;BR>
  Your Clarity Team

  </gel:email>
  </core:forEach>
  </core:catch>

</gel:script>

 

 

[David Morton]

Can you tell if the emails are getting to your email-server at all?

Can you send send emails (via that other "process" you mentioned) to those specific email accounts?

 

I ask, because I have a situation where my GEL process will not send emails to addresses outside of the local domain - i.e. it works OK for dave(at)mylocal-email.com but not for dave(at)someexternal-email.com - but this is all down to how the email server is configured, not Clarity.

 

--

 

You could also try using the <gel:mail> methods instead of <gel:email> ?

[matej256]

Hi Dave,

 

Yes it worked also for my domain which is outside. When I for example adjusted SQL query to just retrieve Project ID, Name and Recipient email address,

it worked well for me, so I guess this issue is somehow connected to above query, but don't know how....

 

Matej

[David Morton]

Hmm - you seem to be messaging about with setDataSource in a couple of places, I don't really follow what/why that is - if you are just using the internal connection you just need to set it once at the "start" of the script and then you are done (you then would not need to specify datasource in the sql statements then either).

Also, not sure whether having the "sqlResult" sql statement before you check the connection in the "dual" statement is helping either (it is confusing me at least!).

[matej256]

Hi Dave, Gene,

 

thanks a lot for your valuable inputs, I was off yesterday so my response got delayed.

Nevertheless I got some idea, tried it and I've found it as it's my issue:

 

When I've removed this "function" from code

where  w.lastupdatedate like To_char(SYSDATE, 'DD-MON-RR')

and replaced it with just a project code

Where ii.code - 123456

it worked well.

 

EDIT: the issue is only with SYSDATE func, to_char functionality works well, I've just tested it.

Do you have idea how this to_char(sysdate) clausule can be replaced to save my functionality?

 

Thanks

Matej

[David Morton]

OK - to be honest I never even looked at the SQL because I assumed that it worked OK since you posted some output

 

In SQL terms, "where  w.lastupdatedate like To_char(SYSDATE, 'DD-MON-RR')" is just wrong though - what you are dong there is comparing an Oracle DATE field (PP_WIP.lastupdatedate) with a character string (and what the 'like' is meant to do I don't know ) and so you are reliant on what automatic date-to-string conversion the database happens to use - the default format is 'DD-MON-RR' which is probably why SQL*Developer doesn't complain, but within Clarity (i.e. where your GEL script is running) then this is different!

 

Best practice when comparing DATE fields, is to compare them as date-fields rather than convert them to stings (the conversion is just unnecessary).

 

So, assuming that you are trying to pick up data where the lastupdatedate is 'today', a better clause would be;

 

where  trunc(w.lastupdatedate) = trunc(SYSDATE)

 

'trunc' just removes the time element of a date-time field and this is performed with DATE fields on both sides of the test.

[matej256]

Hi Dave,

 

I've used 'like' because '=' not worked due to date conversion as you said. I've always used 'to_char(date,'some-stamp')' in pure SQL without any issues so I didn't know it's not so preferable method.....

Now I've learned it should be used differently Finally, your suggestion is working pretty well, so thanks again for your hints.

 

Matej

[David Morton]

If you are comparing dates to strings then you do need some flavour of a to_char around the date field (to make sure that the converted date format matches the format the the text in your string is in)  - but if you are doing pure date-to-date comparisons, do them all as dates and you shouldn't get "weirdness" happening. 

[Eugene Greiff]

So any time I am building something that emails, I use this little utility to capture what is being emailed.

 

smtp4dev - Home

 

So after I run my script and I don't see the email in stmpdev then I know my code is the issue.  If I see emails, then I know something downstream is messing with the emails.

 

V/r,

Gene