Clarity

 View Only
Expand all | Collapse all

Bulk upload of timesheets?

  • 1.  Bulk upload of timesheets?

    Posted May 16, 2016 06:08 AM

    My company has around 100 users who's time sheets will almost always be exactly the same week to week. We wanted to upload these in bulk so that they didn't need to do this so am trying to find out the best way to go about this from a position of knowing very little about the system!

     

    Browsing some of the knowledge base articles it seems like this should be possible with the XOG, should I be looking at any other methods?

     

    In terms of using XOG would there be any other prerequisites than the below?

     

    XOG client

    Administration - Access

    Administration - XOG

    Knowledge of XML?

     

    I have to say from what I read it seems like XOG is probably quite a steep learning curve.



  • 2.  Re: Bulk upload of timesheets?

    Posted May 16, 2016 08:14 AM


  • 3.  Re: Bulk upload of timesheets?



  • 4.  Re: Bulk upload of timesheets?

    Posted May 17, 2016 11:31 AM

    Many thanks, I had had a look at some of those, will go through the others you have included.



  • 5.  Re: Bulk upload of timesheets?
    Best Answer

    Posted May 16, 2016 08:14 AM

    You are correct, XOG is the way to do this.

     

    Look at the 'prj_timeperiods_write.xml' file provided with the XOG client downloads from the application for example on how you need to format the XML to write timesheet(s).



  • 6.  Re: Bulk upload of timesheets?

    Posted May 17, 2016 11:30 AM

    Many thanks, I had a look at that template. I'll read through some of the other posts just to get a better idea of it all.



  • 7.  Re: Bulk upload of timesheets?

    Posted May 17, 2016 02:30 PM

    You can XOG in timesheets.  I wrote a whole process if you're interested.



  • 8.  Re: Bulk upload of timesheets?

    Posted May 20, 2016 05:27 AM

    Hi Lowell, thanks for your reply. Yes I would be interested in what you did. I'm still looking into it all so any guidance is appreciated.



  • 9.  Re: Bulk upload of timesheets?

    Posted Jun 23, 2016 02:02 PM

    Hi -

    I had some trouble getting back to this conversation.

     

    How do I attach text files?  I see others have some "inserts" in their discussions or attachments.  But, I can't find that option in my menu.

     

    Thanks,

    Lowell



  • 10.  Re: Bulk upload of timesheets?

    Posted Jun 23, 2016 02:03 PM

    Here is import of data into a staging table:

     

     

    <gel:script xmlns:core="jelly:core"
        xmlns:file="jelly:com.niku.union.gel.FileTagLibrary"
        xmlns:gel="jelly:com.niku.union.gel.GELTagLibrary"
        xmlns:sql="jelly:sql" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
        <!--Parameters section-->
        <gel:parameter default="True" var="PUseToday"/>
        <gel:parameter default="Import_Hours_01112013.csv" var="PFileName"/>
        <gel:parameter default="Import_Hours" var="PFileType"/>
        <gel:parameter default="\\fileshare\filefolder\file" var="PLocalDirectory"/>
        <gel:parameter default="TriZ-Gen" var="PooledResource"/>
        <gel:parameter default="5231225" var="PooledResource_ID"/>
        <gel:parameter default="True" var="PoolHours"/>
       

        <!--PoolHours variable sets all hours to roll up to PooledResource -->
        <core:choose>
            <core:when test="${PUseToday}">
                <gel:setDataSource dbId="Niku"/>

                <sql:query var="vnow_qry">
                 select to_char(sysdate -1,'MMDDYYYY') v_now from dual
               </sql:query>
                <core:set var="vnow">${vnow_qry.rows[0].v_now}</core:set>
                <core:set value="${PFileType}_${vnow}.csv" var="vFile"/>
                <gel:out>"${vFile}"</gel:out>
    <!--            <gel:log level="INFO">Date Set vnow: ${vnow}</gel:log>
                <gel:log level="INFO">Date Set v_now: ${vnow_qry.v_now}</gel:log>
                <gel:log level="INFO">Date Set v_now: ${vnow_qry.rows[0].v_now}</gel:log>-->
            </core:when>
            <core:otherwise>
                <core:set value="${PFileName}" var="vFile"/>
            </core:otherwise>
        </core:choose>
        <core:catch var="ex">
            <file:readFile commentIndicator="#" delimiter=","
                embedded="false" fileName="${PLocalDirectory}/${vFile}" var="input"/>
        </core:catch>
        <gel:log level="INFO">Reading file named - ${PLocalDirectory}/${vFile}</gel:log>
        <!--Verify that a file was obtained and load into staging table-->
        <core:choose>
            <core:when test="${ex == null}">
                <gel:setDataSource dbId="Niku"/>
                <!--Delete records from last run-->
            <sql:update>
               delete from trg_trz_time_stage
            </sql:update>

        <!--PoolHours variable sets all hours to roll up to PooledResource -->
        <core:choose>
            <core:when test="${PoolHours}">
                <gel:log level="INFO">select R.id from srm_resources R where R.UNIQUE_NAME = '${PooledResource}'</gel:log>
                <sql:query var="v_pooledresource">
                 select R.id from srm_resources R where R.UNIQUE_NAME = '${PooledResource}'
                </sql:query>
             ${v_pooledresource.rows[0].id}
                <core:set value="${v_pooledresource.rows[0].id}" var="PooledResourceID"/>
                <gel:log level="INFO">"v_pooledresource.rows0.id ${v_pooledresource.rows[0].id}"</gel:log>
                <gel:log level="INFO">"PooledResourceID ${PooledResourceID}"</gel:log>
            </core:when>
            <core:otherwise>
             <!--Set Pooled Resource ID manually from variable if query fails-->
                <core:set value="${PooledResource_ID}" var="PooledResourceID"/>
                <gel:log level="INFO">"v_pooledresource.rows0.id ${v_pooledresource.rows[0].id}"</gel:log>
                <gel:log level="INFO">"PooledResourceID ${PooledResourceID}"</gel:log>
            </core:otherwise>
        </core:choose>

                <gel:log level="INFO">"v_pooledresource.rows0.id ${v_pooledresource.rows[0].id}"</gel:log>
                <gel:log level="INFO">"PooledResourceID ${PooledResourceID}"</gel:log>
                <gel:log level="INFO">"PooledResource ${PooledResource}"</gel:log>${PooledResource}

                <core:forEach begin="0" items="${input.rows}" trim="true" var="row">
                    <core:catch var="ex2">
    <gel:log level="INFO">"row0: ${row[0]}"</gel:log><gel:log level="INFO">"row1: ${row[1]}"</gel:log><gel:log level="INFO">"row2: ${row[2]}"</gel:log><gel:log level="INFO">"row3: ${row[3]}"</gel:log><gel:log level="INFO">"row4: ${row[4]}"</gel:log><gel:log level="INFO">"row5: ${row[5]}"</gel:log><gel:log level="INFO">"row6: ${row[6]}"</gel:log><gel:log level="INFO">"row7: ${row[7]}"</gel:log><gel:log level="INFO">"row8: ${row[8]}"</gel:log><gel:log level="INFO">"row9: ${row[9]}"</gel:log><gel:log level="INFO">"row10: ${row[10]}"</gel:log><gel:log level="INFO">"row11: ${row[11]}"</gel:log><gel:log level="INFO">"row12: ${row[12]}"</gel:log><gel:log level="INFO">"row13: ${row[13]}"</gel:log><gel:log level="INFO">"row14: ${row[14]}"</gel:log><gel:log level="INFO">"row15: ${row[15]}"</gel:log><gel:log level="INFO">"row16: ${row[16]}"</gel:log><gel:log level="INFO">"row17: ${row[17]}"</gel:log><gel:log level="INFO">"row18: ${row[18]}"</gel:log><gel:log level="INFO">"row19: ${row[19]}"</gel:log><gel:log level="INFO">"PooledResourceID: ${PooledResourceID}"</gel:log><gel:log level="INFO">"PooledResource: ${PooledResource}"</gel:log>

                        <sql:update>
                 INSERT INTO trg_trz_time_stage
                (FIRST_NAME,LAST_NAME,TRZ_RESOURCE_ID,PCR,Week_Start,Week_end,day_1,day_1_hours,day_2,day_2_hours,day_3,day_3_hours,day_4,day_4_hours,day_5,day_5_hours,day_6,day_6_hours,day_7,day_7_hours,prresourceid,resourceid,load_date)
                 VALUES (?,?,?,?,to_date(?,'YYYY-MM-DD'),to_date(?,'YYYY-MM-DD'),to_date(?,'YYYY-MM-DD'),?,to_date(?,'YYYY-MM-DD'),?,to_date(?,'YYYY-MM-DD'),?,to_date(?,'YYYY-MM-DD'),?,to_date(?,'YYYY-MM-DD'),?,to_date(?,'YYYY-MM-DD'),?,to_date(?,'YYYY-MM-DD'),?,?,?,sysdate)
                            <sql:param value="${row[0]}"/>
                            <sql:param value="${row[1]}"/>
                            <sql:param value="${row[2]}"/>
                            <sql:param value="${row[3]}"/>
                            <sql:param value="${row[4]}"/>
                            <sql:param value="${row[5]}"/>
                            <sql:param value="${row[6]}"/>
                            <sql:param value="${row[7]}"/>
                            <sql:param value="${row[8]}"/>
                            <sql:param value="${row[9]}"/>
                            <sql:param value="${row[10]}"/>
                            <sql:param value="${row[11]}"/>
                            <sql:param value="${row[12]}"/>
                            <sql:param value="${row[13]}"/>
                            <sql:param value="${row[14]}"/>
                            <sql:param value="${row[15]}"/>
                            <sql:param value="${row[16]}"/>
                            <sql:param value="${row[17]}"/>
                            <sql:param value="${row[18]}"/>
                            <sql:param value="${row[19]}"/>
                            <sql:param value="${PooledResourceID}"/>
                            <sql:param value="${PooledResource}"/>
                        </sql:update>
                    </core:catch>
                    <gel:out>${ex2}</gel:out>
                </core:forEach>
                <sql:query var="vrec_count">
                 select count(*) as num
                 from trg_trz_time_stage
               </sql:query>
                <gel:log level="INFO">Number of records insert into trg_trz_time_stage -- "${vrec_count.rows[0].num}"</gel:log>
            </core:when>
            <core:otherwise>
                <!--Log the exception-->
                <gel:log level="INFO">Error locating or reading file -- "${ex}"</gel:log>
            </core:otherwise>
        </core:choose>
    </gel:script>



  • 11.  Re: Bulk upload of timesheets?

    Posted Jun 23, 2016 02:05 PM

    Email warnings for duplicate entries in data:

     

    <gel:script xmlns:core="jelly:core" xmlns:file="jelly:com.niku.union.gel.FileTagLibrary" xmlns:gel="jelly:com.niku.union.gel.GELTagLibrary"
      xmlns:sql="jelly:sql" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
      <!--Parameters section-->
      <gel:parameter default="reciptient@recipient.com" var="email_addresses"/>
      <!-- The script sends email to recipient


                      CUSTOMER:

                       AUTHOR: Lowell Wetzel

                       DATE:  2/1/2013
        -->
      <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>
      <gel:setDataSource dbId="Niku"/>
      <gel:log level="INFO">"Fixing Duplicate Tasks per PCR"</gel:log>
      <!--Query for Duplicate Count-->
      <sql:query escapeText="False" var="count_duplicaterecords">                
              Select count(T.PRNAME) Duplicates
              from srm_projects p inner join prtask t on T.PRPROJECTID = P.ID
              inner join ODF_CA_TASK tsk on TSK.ID = T.PRID
              inner join (select count(TSK.TRG_PCR) Duplicate, TSK.TRG_PCR PCR
              from srm_projects p inner join prtask t on t.PRPROJECTID = p.ID
              inner join ODF_CA_TASK tsk on tsk.ID = t.PRID
              Where T.IS_OPEN_TE = 1
              AND TSK.TRG_PCR is not null
              AND TSK.TRG_PCR IN (select distinct TRZ.PCR from TRG_TRZ_TIME_STAGE TRZ)
              group by TSK.TRG_PCR) DUP on DUP.PCR = TSK.TRG_PCR
              WHERE DUP.Duplicate &gt; 1
              ORDER BY TSK.TRG_PCR
            </sql:query>
      <gel:log level="INFO">"PCR Duplicates: ${count_duplicaterecords.rows[0].Duplicates}"</gel:log>
      <!--Reset emailtextbody variable for each pass-->
      <core:set value="" var="emailtextbody"/>
      <core:choose>
        <core:when test="${count_duplicaterecords.rows[0].Duplicates > 1}">
          <!--Query Project Name, Project ID, Task Name, Task ID, PCR -->
          <sql:query escapeText="False" var="v_duplicaterecords">                
            Select P.NAME, P.UNIQUE_NAME, P.ID, T.PRID, T.PRNAME, T.PREXTERNALID, TSK.TRG_PCR from srm_projects p inner join prtask t on T.PRPROJECTID = P.ID
              inner join ODF_CA_TASK tsk on TSK.ID = T.PRID
              inner join (select count(TSK.TRG_PCR) Duplicate, TSK.TRG_PCR PCR
              from srm_projects p inner join prtask t on t.PRPROJECTID = p.ID
              inner join ODF_CA_TASK tsk on tsk.ID = t.PRID
              Where T.IS_OPEN_TE = 1
              AND TSK.TRG_PCR is not null
              AND TSK.TRG_PCR IN (select distinct TRZ.PCR from TRG_TRZ_TIME_STAGE TRZ)
              group by TSK.TRG_PCR) DUP on DUP.PCR = TSK.TRG_PCR
              WHERE DUP.Duplicate &gt; 1
              ORDER BY TSK.TRG_PCR
        </sql:query>
          <!--Loop through duplicates and build email variable. -->
          <core:forEach begin="0" items="${v_duplicaterecords.rows}" trim="true" var="pcr_row">
            <core:set escapeText="false" trim="false"
              value="${emailtextbody} &lt;br> &lt;br> -------------------------------------------------------------------- &lt;br> To access this Project Task with Duplicate Work Order, click on : ${eURL}/niku/app?action=projmgr.taskProperties&amp;id=${pcr_row.prid}&amp;returnAction=projmgr.keyTaskListReturn&amp;projectId=${pcr_row.id}  &lt;br> &lt;br>    Project Name:   ${pcr_row.NAME} &lt;br>      Project ID:   ${pcr_row.UNIQUE_NAME} &lt;br>       Task Name:   ${pcr_row.PRNAME} &lt;br>         Task ID:   ${pcr_row.PREXTERNALID} &lt;br> Task Work Order:   ${pcr_row.TRG_PCR} &lt;br> &lt;br> Please, fix the PCR number on this Active task. &lt;br> " var="emailtextbody"/>
            <gel:log level="INFO">${emailtextbody}</gel:log>
          </core:forEach>
          <sql:update escapeText="False">
    update (select TRZ.PCR, TRZ.DUPLICATE from TRG_TRZ_TIME_STAGE TRZ
    WHERE TRZ.PCR in (Select DISTINCT TSK.TRG_PCR from srm_projects p inner join prtask t on T.PRPROJECTID = P.ID
                 inner join ODF_CA_TASK tsk on TSK.ID = T.PRID
                 inner join (select count(TSK.TRG_PCR) Duplicate, TSK.TRG_PCR PCR
                 from srm_projects p inner join prtask t on t.PRPROJECTID = p.ID
                 inner join ODF_CA_TASK tsk on tsk.ID = t.PRID
                 Where T.IS_OPEN_TE = 1
                 AND TSK.TRG_PCR is not null
                 AND TSK.TRG_PCR IN (select distinct TRZ.PCR from TRG_TRZ_TIME_STAGE TRZ)
                 group by TSK.TRG_PCR) DUP on DUP.PCR = TSK.TRG_PCR
                 WHERE DUP.Duplicate &gt; 1))
    SET Duplicate = 1
        </sql:update>
          <core:catch var="v_mail_exception">
            <gel:email from="clarity@ca.com"
              subject="The following ${count_duplicaterecords.rows[0].Duplicates} Work Orders are assigned to more than 1 task" to="${email_addresses}" trim="false">
    The following ${count_duplicaterecords.rows[0].Duplicates} Work Orders are assigned to more than 1 task.

    ${emailtextbody}

               </gel:email>
          </core:catch>
          <core:otherwise>
            <!--Log the exception-->
            <gel:log level="INFO">Error Emailing email@email.com -- "${ex}"</gel:log>
          </core:otherwise>
        </core:when>
        <otherwise>
          <gel:log level="INFO">No Duplicates</gel:log>
        </otherwise>
      </core:choose>
    </gel:script>



  • 12.  Re: Bulk upload of timesheets?

    Posted Jun 23, 2016 02:06 PM

    Here to find Resources with no tasks:

     

     

    <gel:script xmlns:core="jelly:core" xmlns:file="jelly:com.niku.union.gel.FileTagLibrary" xmlns:gel="jelly:com.niku.union.gel.GELTagLibrary"
      xmlns:sql="jelly:sql" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
      <!--Parameters section-->
      <gel:parameter default="recipient@recipient.com" var="email_addresses"/>
      <!-- The script finds PCRs not associated with tasks and sends notification email to recipient@recipient.com


                      CUSTOMER:

                       AUTHOR: Lowell Wetzel

                       DATE:  2/1/2013
        -->
      <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>
      <gel:setDataSource dbId="Niku"/>
      <gel:log level="INFO">"Fixing PCRs with no Task"</gel:log>
      <!--Query for Duplicate Count-->
      <sql:query escapeText="False" var="count_nullrecords">                
              Select count(TRI.PCR) Duplicates FROM
         (SELECT distinct TRZ.PCR from TRG_TRZ_TIME_STAGE TRZ left outer join (Select TSK.TRG_PCR PCR from srm_projects p inner join prtask t on T.PRPROJECTID = P.ID
          inner join ODF_CA_TASK tsk on TSK.ID = T.PRID) PC on TRZ.PCR = PC.PCR
         WHERE PC.PCR is null) TRI
            </sql:query>
      <gel:log level="INFO">"PCR Nulls: ${count_nullrecords.rows[0].Duplicates}"</gel:log>
      <!--Reset emailtextbody variable for each pass-->
      <core:set value="" var="emailtextbody"/>
      <core:choose>
        <core:when test="${count_nullrecords.rows[0].Duplicates > 0}">
          <!--Query PCR with no Project or Task -->
          <sql:query escapeText="False" var="v_nullrecords">                
         SELECT distinct TRZ.PCR from TRG_TRZ_TIME_STAGE TRZ left outer join (Select TSK.TRG_PCR PCR from srm_projects p inner join prtask t on T.PRPROJECTID = P.ID
          inner join ODF_CA_TASK tsk on TSK.ID = T.PRID) PC on TRZ.PCR = PC.PCR
         WHERE PC.PCR is null
        </sql:query>
          <!--Loop through duplicates and build email variable. -->
          <core:forEach begin="0" items="${v_nullrecords.rows}" trim="true" var="pcr_row">
            <core:set escapeText="false" trim="false"
              value="${emailtextbody} &lt;br> &lt;br> -------------------------------------------------------------------- &lt;br> The Following Work Order has no Task associated with it:  &lt;br> &lt;br> Work Order:   ${pcr_row.PCR} &lt;br> &lt;br> Please, fix the Work Order number on this Active task. &lt;br> " var="emailtextbody"/>
            <gel:log level="INFO">${emailtextbody}</gel:log>
          </core:forEach>
          <sql:update escapeText="False">
       update (select TRZ.PCR, TRZ.DUPLICATE from TRG_TRZ_TIME_STAGE TRZ
       WHERE TRZ.PCR in (SELECT distinct TRZ.PCR from TRG_TRZ_TIME_STAGE TRZ left outer join (Select TSK.TRG_PCR PCR from srm_projects p inner join prtask t on T.PRPROJECTID = P.ID
        inner join ODF_CA_TASK tsk on TSK.ID = T.PRID) PC on TRZ.PCR = PC.PCR
       WHERE PC.PCR is null))
       SET Duplicate = 1
        </sql:update>
          <core:catch var="v_mail_exception">
            <gel:email from="clarity@regence.com" subject="The following ${count_nullrecords.rows[0].Duplicates} Work Orders are orphans."
              to="${email_addresses}" trim="false">

    The following ${count_nullrecords.rows[0].Duplicates} Work Orders are orphans.

    ${emailtextbody}

               </gel:email>
          </core:catch>
          <core:otherwise>
            <!--Log the exception-->
            <gel:log level="INFO">Error Emailing ITVendorManagement@regence.com -- "${v_mail_exception}"</gel:log>
          </core:otherwise>
        </core:when>
        <otherwise>
          <gel:log level="INFO">No Null Tasks</gel:log>
        </otherwise>
      </core:choose>
    </gel:script>



  • 13.  Re: Bulk upload of timesheets?