adam.a.flanagan

Looking for a few pointers with GEL and properties.xml

Discussion created by adam.a.flanagan on Nov 15, 2010
Latest reply on Nov 15, 2010 by adam.a.flanagan
Hi,

I'm just starting out with GEL and I've written a fairly simple script to update cost details. It validates through the command line but won't run, which I think is due to not being able to grab the properties.xml to know where the db is. Ho do I pass it the file ? Or place the file correctly for it to get picked up ?? Also, when I run this inside a process I get an error about badly formed XML.

Any help gratefully appreciated.
<!-- Some header stuff -->
<gel:script xmlns:core="jelly:core"
    xmlns:file="jelly:com.niku.union.gel.FileTagLibrary"
    xmlns:gel="jelly:com.niku.union.gel.GELTagLibrary"
    xmlns:j="jelly:core" xmlns:jx="jelly.com.ms.util"
    xmlns:soap="jelly:com.niku.union.gel.SOAPTagLibrary"
    xmlns:soap-env="http://schemas.xmlsoap.org/soap/envelope/"
    xmlns:sql="jelly:sql" xmlns:util="jelly:util"
    xmlns:xog="http://localhost/niku/xog" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">


    <!-- XOG web server & credentials -->
    <gel:parameter default="admin" var="XOGusername"/>
    <gel:parameter default="password" secure="false" var="XOGpassword"/>
    <gel:parameter default="http://us2salxv0016.corpnet2.com:8080/niku/xog" var="XOGserver"/>



<!-- Get a DB Connection to Clarity -->
    <gel:setDataSource dbId="Niku"/>



<!-- Setup a var for debugging purposes -->

<core:set value="${gel_objectInstanceId}" var="intSpend"/>

<!--<core:set value="5013438" var="intSpend"/>-->



<!-- Get the details of the project -->

<j:catch var="errGetProject">
        <sql:query escapeText="false" var="GetProject">                            





SELECT I.id PRJ_ID, I.name PRJ_NAME, I.code PRJ_CODE, CP.code CP_CODE, BP.code BP_CODE, SP.period_name START_PERIOD, EP.period_name END_PERIOD 





FROM inv_investments I, fin_plans CP, fin_plans BP, biz_com_periods SP, biz_com_periods EP





WHERE CP.object_id = I.id 





AND CP.name = 'Cost Plan' 





AND BP.object_id = I.id 





AND BP.name = 'Hard Benefits' 





AND SP.id = CP.start_period_id 





AND EP.id = CP.end_period_id 





AND I.id IN ( SELECT odf_parent_id FROM odf_ca_gsk_spend WHERE id = ? )




  <sql:param value="${intSpend}"/>
        </sql:query>
    </j:catch>
    <j:choose>
        <j:when test="${errGetProject != null}">
            <gel:log level="ERROR">SQL Error on getting project details ${errGetProject}</gel:log>
        </j:when>
        <j:otherwise>
            <core:set value="${GetProject.rows[0].prj_id}" var="intProjectID"/>
            <core:set value="${GetProject.rows[0].prj_name}" var="strProjectName"/>
            <core:set value="${GetProject.rows[0].prj_code}" var="strProjectCode"/>
            <core:set value="${GetProject.rows[0].cp_code}" var="strCostCode"/>
            <core:set value="${GetProject.rows[0].bp_code}" var="strBenCode"/>
            <core:set value="${GetProject.rows[0].start_period}" var="strStartPeriod"/>
            <core:set value="${GetProject.rows[0].end_period}" var="strEndPeriod"/>
        </j:otherwise>
    </j:choose>



<!-- Get the spends/transactions -->

<j:catch var="errSpends">
        <sql:query escapeText="false" var="GetSpends">                            




SELECT exp_id, cat_id, TO_CHAR(start_spend, 'YYYY-MM-DD') start_spend, TO_CHAR(end_spend, 'YYYY-MM-DD') end_spend, 







 TO_CHAR(SUM(spend_amt), 999999999.9) spend_amt, exp_id || '_' || cat_id Detail




FROM (






SELECT SP.expenditure, EX.prexternalid EXP_ID, EX.prname EXP_NAME, 






SP.category, TC.transclass CAT_ID, TC.description CAT_NAME, 






SP.name, SP.spend_amt, 






SP.spend_date, 






TO_DATE('01/'||TO_CHAR(SP.spend_date, 'MM/YYYY'), 'DD/MM/YYYY') START_SPEND,  






ADD_MONTHS(TO_DATE('01/'||TO_CHAR(SP.spend_date, 'MM/YYYY'), 'DD/MM/YYYY'), 1) - 1 END_SPEND 






FROM odf_ca_gsk_spend SP, prchargecode EX, transclass TC 






WHERE SP.expenditure = EX.prid 






AND SP.category = TC.id 






AND SP.odf_parent_id = ? 




)




GROUP BY exp_id, cat_id, start_spend, end_spend 




ORDER BY exp_id, cat_id, start_spend ASC 




<sql:param value="${intProjectID}"/>
        </sql:query>
    </j:catch>
    <j:choose>
        <j:when test="${errSpends != null}">
            <gel:log level="ERROR">SQL Error on getting spends/transactions ${errSpends}</gel:log>
        </j:when>
        <j:otherwise>
            



<!-- Do our ting -->







<!-- Build the main XML -->



<gel:parse var="xmlDocument">








<NikuDataBus xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="../xsd/nikuxog_costPlan.xsd">




<Header action="write" externalSource="NIKU" objectType="costPlan" version="12.0.6.5471"/>




<CostPlans>









<CostPlan benefitPlanCode="${strBenCode}" code="${strCostCode}"




finishPeriod="${strEndPeriod}" investmentCode="${strProjectCode}"




investmentType="PROJECT" isPlanOfRecord="1" name="Cost Plan"




periodType="MONTHLY" primaryPlanBy="CHARGECODE"




secondaryPlanBy="TRANSCLASS" startPeriod="${strStartPeriod}">









<Details></Details>









<CustomInformation><ColumnValue name="partition_code">NIKU.ROOT</ColumnValue></CustomInformation>




</CostPlan>









</CostPlans>




</NikuDataBus>







</gel:parse>







<!-- Build a node template -->



<gel:parse var="nodeTemp">








<Detail detail2Name="category" detailName="expenditure">





<CustomInformation>






<ColumnValue name="gsk_it_cr_currency">GBP</ColumnValue>






<ColumnValue name="partition_code">NIKU.ROOT</ColumnValue>





</CustomInformation>




</Detail>









<PlanData cost="0.0" end_date="2010-12-31"
revenue="0.0" start_date="2010-12-01" units="0.0"/>








</gel:parse>




<!-- Setup a switch or two -->



<core:set value="" var="strCurrentDetail"/>





<!-- Iterate through the rows -->



<core:forEach items="${GetSpends.getRows()}" var="row">











<core:if test="${row.Detail != strCurrentDetail}">





<!-- start a new detail up -->





<core:set value="${row.Detail}" var="strCurrentDetail"/>





<!-- grab a copy of the template -->





<gel:set select="$nodeTemp/Detail" var="detailNode"/>





<!-- update the values -->





<gel:set value="${row.exp_id}" select="$detailNode/@detailName"/>





<gel:set value="${row.cat_id}" select="$detailNode/@detail2Name"/>





<!-- insert it into the master doc -->





<gel:set value="${detailNode}" select="$xmlDocument/Details" insert="true"/>




</core:if>













<!-- spit the row -->





<!-- grab a copy of the template -->





<gel:set select="$nodeTemp/PlanData" var="planNode"/>





<!-- update the values -->





<gel:set value="${row.spend_amt}" select="$planNode/@cost"/>





<gel:set value="${row.start_spend}" select="$planNode/@start_date"/>





<gel:set value="${row.end_spend}" select="$planNode/@end_date"/>





<!-- insert it into the master doc -->





<gel:set value="${planNode}" select="$xmlDocument/Details/Detail" insert="true"/>















</core:forEach>











<!-- send output to a file 



<core:catch var="serializeThis">




<gel:serialize fileName="update_cost_plan_debug.xml" var="${xmlDocument}"/>



</core:catch>



-->







<!-- ok, now we need to zap this thing in -->




<core:catch var="thisXog">





<soap:invoke endpoint="${XOGserver}" var="result">





<soap:message>






<soap-env:Envelope
xmlns:soap-env="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xog="http://www.niku.com/xog">







<soap-env:Body>








<xog:Login xmlns="http://www.niku.com/xog">









<xog:Username>${XOGusername}</xog:Username>









<xog:Password>${XOGpassword}</xog:Password>








</xog:Login>








<gel:include select="$xmlDocument"/>







</soap-env:Body>






</soap-env:Envelope>





</soap:message>




</soap:invoke>



   



</core:catch>







<!-- End do our ting -->




        </j:otherwise>
    </j:choose>


</gel:script>

Outcomes