AnsweredAssumed Answered

GEL Serialize Question

Question asked by Lowell on Sep 20, 2012
Latest reply on Sep 21, 2012 by nick_darlington
I have a "Serialize" command that works when writing other things in other GEL scripts, but for this particular variable (which is rather large), it gives an error. Any thoughts?

<gel:serialize fileName="${PLocalDirectory}\Email_Finance_${vnow}.xml" var="${emailtextbody}"/>

Gives: Query failed org.apache.commons.jelly.JellyTagException: null:214:96: <gel:serialize> Property 'var' has no write method".

I have never gotten this error message from Serialize. Something is now preventing this email from being sent out from the "<gel:email" command and I'm trying to output the content to send manually until I can find where the email disappears to.
<gel:script xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"
    xmlns:core="jelly:core"
    xmlns:file="jelly:com.niku.union.gel.FileTagLibrary"
    xmlns:gel="jelly:com.niku.union.gel.GELTagLibrary"
    xmlns:soap="jelly:com.niku.union.gel.SOAPTagLibrary"
    xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"
    xmlns:sql="jelly:sql" 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:set value="0" var="totalEmailCount"/>
    <core:set value="0" var="totalEmailSentCount"/>
    <!-- The script sends email    -->
    <!-- SETUP DATABASE CONNECTION -->
    <gel:setDataSource dbId="Niku"/>
<gel:parse file="../config/properties.xml" var="properties"/>
<gel:set asString="true" select="$properties/properties/webServer/webServerInstance/@entryUrl"  var="eURL"/>
<gel:set asString="true" select="$properties/properties/webServer/webServerInstance/@sslEntryUrl"  var="ssleURL"/>
<gel:log>Entry URL = ${eURL}</gel:log>
<gel:log>SSL Entry URL  = ${ssleURL}</gel:log>
<!-- Only used with GEL Serialize command at end -->
    <gel:parameter default="./outputfiles" var="PLocalDirectory"/>
<!--vnow used for output file-->
<core:set var="vnow">
     <gel:formatDate format="MMddyy"/>
</core:set>
<!-- Only used with GEL Serialize command at end -->

<!--Same query to pull Information
1.        Total Estimated Effort Hours

0 no

1 yes
2.         Is this replacing existing software?

4040 no

4039 yes
3.         Is this modifying existing software?

4040 no

4039 yes
-->
    <sql:query escapeText="false" var="v_emails">
        SELECT COUNT(*) as num
from (
select
p.pkid,
p.project_id Request_id,
p.project_name Request_Title,
nvl(O23.SMT_ID, r.unique_name) SMT_ID,
nvl(O23.SMT_Lead, r.full_name) SMT_Lead,
nvl(O23.SMT_Email, r.email) SMT_Email,
--O31.trg_fin_mod_sw,
case I.trg_fin_rev
when 0 then 'No'
when 1 then 'Yes'
end as trg_fin_rev,
O31.trg_fin_mod_sw,
O32.trg_fin_rep_sw,
I.Total_Hours

from
trg_mv_project p 
inner join srm_resources r 
on p.project_manager_pkid = r.user_id 
inner join inv_investments Inv on p.pkid = Inv.id
inner join (select OC.id, OC.trg_rits_hours RITS_TOTAL_HOURS, OC.trg_req_rel_date RWR_REQ_REL_DATE,
OP.trg_prod_rel_date RWR_TRGT_REL_DATE, OP.trg_act_prod_rd RWR_ACT_REL_DATE, OC.trg_prim_key_init,
OC.trg_pet, OC.trg_mandate_type, OC.trg_rqst_div, OP.trg_proj_stat, OC.trg_reg_req_stat,
OC.trg_prim_del_mgr, OC.trg_spon_name, OC.trg_fund_source, OC.trg_type_of_req, OC.trg_new_date RWR_REQ_SUB_DATE,
OC.trg_hold_reason, OC.trg_smt_lead, trg_prd_sod Production_Signoff_Date, OC.trg_stag_date, OC.trg_fin_rev, OC.trg_fin_mod_sw, OC.trg_fin_rep_sw,
((OC.trg_man_score*1) + (OC.trg_mtm_imp_score*1) + (OC.trg_est_ben_score*1) + (OC.trg_prm_key_in_score*1) + (OC.trg_type_req_score*1)) Score,
(nvl(OC.trg_rits_hours,0) + nvl(OC.trg_bus_hours,0) + nvl(OC.trg_tot_ven,0)) Total_Hours
FROM odf_ca_inv OC inner join odf_ca_project OP on OC.id = OP.id WHERE OC.odf_object_code = 'project') I on p.pkid = i.id
left outer join 
(--Field23: SMT Lead
select Res.id, Res.unique_name SMT_ID, Res.full_name SMT_Lead, Res.email SMT_Email from srm_resources Res) O23 on i.trg_smt_lead = O23.id
left outer join 
(--Field5:  Project Status
SELECT L.LOOKUP_CODE, N.NAME RWR_PROJ_STAT FROM CMN_LOOKUPS L, CMN_CAPTIONS_NLS N
WHERE L.ID = N.PK_ID AND N.LANGUAGE_CODE = 'en'
AND L.LOOKUP_TYPE = 'TRG_PROJ_STAT' AND N.table_name = 'CMN_LOOKUPS') O5 on I.trg_proj_stat = O5.lookup_code

left outer join
(--Field31:  Is this modifying existing software?
SELECT L.id, L.LOOKUP_CODE, N.NAME trg_fin_mod_sw FROM CMN_LOOKUPS L, CMN_CAPTIONS_NLS N
WHERE L.ID = N.PK_ID AND N.LANGUAGE_CODE = 'en'
AND L.LOOKUP_TYPE = 'PAC_RPT_YESNO' AND N.table_name = 'CMN_LOOKUPS') O31 on I.trg_fin_mod_sw = O31.id

left outer join
(--Field32:  Is this modifying existing software?
SELECT L.id, L.LOOKUP_CODE, N.NAME trg_fin_rep_sw FROM CMN_LOOKUPS L, CMN_CAPTIONS_NLS N
WHERE L.ID = N.PK_ID AND N.LANGUAGE_CODE = 'en'
AND L.LOOKUP_TYPE = 'PAC_RPT_YESNO' AND N.table_name = 'CMN_LOOKUPS') O32 on I.trg_fin_rep_sw = O32.id


where 
Inv.idea_id is not null
and (I.Total_Hours > 1499)
and I.trg_fin_rev = 0
)

    </sql:query>
    <gel:log level="INFO">Number of RWRs ${v_emails.rows[0].num} to be in email.</gel:log>
    <core:choose>
        <core:when test="${v_emails.rows[0].num > 0}">
            <core:set value="${v_emails.rows[0].num}" var="totalEmailCount"/>
            <core:catch var="v_exception">
                <gel:log level="INFO">${v_emails.rows[0].num} </gel:log>
<!--Query to pull all projects with hours > 1500 and "finance has reviewed" not checked (=no) -->
                <sql:query escapeText="false" var="v_email_data">
select
p.pkid,
p.project_id Request_id,
p.project_name Request_Title,
nvl(O23.SMT_ID, r.unique_name) SMT_ID,
nvl(O23.SMT_Lead, r.full_name) SMT_Lead,
nvl(O23.SMT_Email, r.email) SMT_Email,
--O31.trg_fin_mod_sw,
case I.trg_fin_rev
when 0 then 'No'
when 1 then 'Yes'
end as trg_fin_rev,
nvl(O31.trg_fin_mod_sw,'No selection') trg_fin_mod_sw,
nvl(O32.trg_fin_rep_sw,'No selection') trg_fin_rep_sw,
I.Total_Hours

from
trg_mv_project p 
inner join srm_resources r 
on p.project_manager_pkid = r.user_id 
inner join inv_investments Inv on p.pkid = Inv.id
inner join (select OC.id, OC.trg_rits_hours RITS_TOTAL_HOURS, OC.trg_req_rel_date RWR_REQ_REL_DATE,
OP.trg_prod_rel_date RWR_TRGT_REL_DATE, OP.trg_act_prod_rd RWR_ACT_REL_DATE, OC.trg_prim_key_init,
OC.trg_pet, OC.trg_mandate_type, OC.trg_rqst_div, OP.trg_proj_stat, OC.trg_reg_req_stat,
OC.trg_prim_del_mgr, OC.trg_spon_name, OC.trg_fund_source, OC.trg_type_of_req, OC.trg_new_date RWR_REQ_SUB_DATE,
OC.trg_hold_reason, OC.trg_smt_lead, trg_prd_sod Production_Signoff_Date, OC.trg_stag_date, OC.trg_fin_rev, OC.trg_fin_mod_sw, OC.trg_fin_rep_sw,
((OC.trg_man_score*1) + (OC.trg_mtm_imp_score*1) + (OC.trg_est_ben_score*1) + (OC.trg_prm_key_in_score*1) + (OC.trg_type_req_score*1)) Score,
(nvl(OC.trg_rits_hours,0) + nvl(OC.trg_bus_hours,0) + nvl(OC.trg_tot_ven,0)) Total_Hours
FROM odf_ca_inv OC inner join odf_ca_project OP on OC.id = OP.id WHERE OC.odf_object_code = 'project') I on p.pkid = i.id
left outer join 
(--Field23: SMT Lead
select Res.id, Res.unique_name SMT_ID, Res.full_name SMT_Lead, Res.email SMT_Email from srm_resources Res) O23 on i.trg_smt_lead = O23.id
left outer join 
(--Field5:  Project Status
SELECT L.LOOKUP_CODE, N.NAME RWR_PROJ_STAT FROM CMN_LOOKUPS L, CMN_CAPTIONS_NLS N
WHERE L.ID = N.PK_ID AND N.LANGUAGE_CODE = 'en'
AND L.LOOKUP_TYPE = 'TRG_PROJ_STAT' AND N.table_name = 'CMN_LOOKUPS') O5 on I.trg_proj_stat = O5.lookup_code

left outer join
(--Field31:  Is this modifying existing software?
SELECT L.id, L.LOOKUP_CODE, N.NAME trg_fin_mod_sw FROM CMN_LOOKUPS L, CMN_CAPTIONS_NLS N
WHERE L.ID = N.PK_ID AND N.LANGUAGE_CODE = 'en'
AND L.LOOKUP_TYPE = 'PAC_RPT_YESNO' AND N.table_name = 'CMN_LOOKUPS') O31 on I.trg_fin_mod_sw = O31.id

left outer join
(--Field32:  Is this modifying existing software?
SELECT L.id, L.LOOKUP_CODE, N.NAME trg_fin_rep_sw FROM CMN_LOOKUPS L, CMN_CAPTIONS_NLS N
WHERE L.ID = N.PK_ID AND N.LANGUAGE_CODE = 'en'
AND L.LOOKUP_TYPE = 'PAC_RPT_YESNO' AND N.table_name = 'CMN_LOOKUPS') O32 on I.trg_fin_rep_sw = O32.id


where 
Inv.idea_id is not null
and (I.Total_Hours > 1499)
and I.trg_fin_rev = 0

          </sql:query>

<!--Loop Through Each project recipient and build the "full email" -->
<!--Reset emailtextbody variable for each pass-->
<core:set value=""  var="emailtextbody"/>

<core:forEach items="${v_email_data.rows}" trim="true" var="emaildata">

<!--Step 1:  get the id and projects list to use as criteria for the "content" email-->

<!--<gel:log level="INFO">emaildata.SMT_ID: ${emaildata.SMT_ID} </gel:log>-->


<!--Step 1a:  get the projects list to use as criteria for the "content" email-->
<gel:log level="INFO">emaildata.project_id: ${emaildata.project_id} </gel:log>
<gel:log level="INFO">emaildata.project_id: ${emaildata.project_id} </gel:log>
<gel:log level="INFO">emaildata.pkid: ${emaildata.pkid} </gel:log>

<gel:log level="INFO">emaildata.trg_fin_rev: ${emaildata.trg_fin_rev} </gel:log>
<gel:log level="INFO">emaildata.trg_fin_mod_sw: ${emaildata.trg_fin_mod_sw} </gel:log>
<gel:log level="INFO">emaildata.trg_fin_rep_sw: ${emaildata.trg_fin_rep_sw} </gel:log>
<gel:log level="INFO">emaildata.Total_Hours: ${emaildata.Total_Hours} </gel:log>


<!--Set beginning link to project in emailtextbody-->
<core:set trim="false" escapeText="false" value="${emailtextbody} &#xA;
&#xA;
--------------------------------------------------------------------
&#xA;
To access this RITS Work Request click on : ${eURL}/niku/app?action=projmgr.projectProperties&amp;odf_view=project.trg_proj_acct&amp;id=${emaildata.pkid}&amp;odf_pk=${emaildata.pkid}  &#xA;
&#xA;
Description: ${emaildata.Request_Title} &#xA;
          #: ${emaildata.Request_id} &#xA;
Total_Hours: ${emaildata.Total_Hours} &#xA;
Is this replacing existing software?: ${emaildata.trg_fin_rep_sw} &#xA;
&#xA;
Is this modifying existing software?: ${emaildata.trg_fin_mod_sw} &#xA;
&#xA;
for questions or additional details please contact your request lead - ${emaildata.SMT_Lead} &#xA;
"
  var="emailtextbody"/>

</core:forEach> <!--<core:forEach items="${v_email_data.rows}" trim="true" var="emaildata">-->

<gel:serialize fileName="${PLocalDirectory}\Email_Finance_${vnow}.xml" var="${emailtextbody}"/>

                <core:catch var="v_mail_exception">
<gel:email trim="false" from="clarity@regence.com" 
subject="The following ${v_emails.rows[0].num} RWRs were entered with greater than or equal to 1500 hours" 
to="trg_projects@regence.com">
The following ${v_emails.rows[0].num} RWRs were entered with greater than or equal to 1500 hours.

${emailtextbody}

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

            </core:catch>

            <gel:log level="INFO">1.0 </gel:log>
            <core:choose>
                <core:when test="${v_exception != null}">
                    <gel:log level="INFO">Query failed ${v_exception}".</gel:log>
                </core:when>
            </core:choose>
            <gel:log level="INFO">2.0 </gel:log>
        </core:when>
    </core:choose>
    <gel:log level="INFO">So long, and thanks for all the fish.</gel:log>
</gel:script>

Outcomes