Clarity

Expand all | Collapse all

How to download attachments in bulk uploaded to custom attachment attributes from clarity

  • 1.  How to download attachments in bulk uploaded to custom attachment attributes from clarity

    Posted 02-21-2017 12:20 PM

    We have couple of custom "attachment" attributes in custom objects where we upload the files (.ppt, .txt). Is there any way to download files in bulk at portfolio level. Note: we are storing the files in Clarity database.



  • 2.  Re: How to download attachments in bulk uploaded to custom attachment attributes from clarity

    Posted 03-06-2017 06:19 AM

    Hello,

     

    I created export of documents for one customer, but it was for migration to another system.

     

    My script did just export of attachments to the server and renaming to correct names (XOG exports file, but the name of it is just 5 milion ID as you know from many places in CA PPM).

     

    Please let me know if this can be helpful for you. I can provide more detail then.

     

    Regards,

    Martin



  • 3.  Re: How to download attachments in bulk uploaded to custom attachment attributes from clarity

    Posted 03-06-2017 04:32 PM

    Hello Martin,

     

    Thank you.

     

    Yes, it will be certainly helpful. Please share it. 

     

    I will XOG out the attachments and export the respective 5 Million ID' s from database and match them. Hopefully this should work for our requirement.

     

    Regards,

    Lalitharjun



  • 4.  Re: How to download attachments in bulk uploaded to custom attachment attributes from clarity

    Posted 03-13-2017 03:24 PM

    Martin, Can you please provide more details on the solution that you mentioned? Let me know if you want to email the details, I will share my email.

     

    Thanks,

    Lalith



  • 5.  Re: How to download attachments in bulk uploaded to custom attachment attributes from clarity

    Posted 03-14-2017 04:37 AM

    Hi Lalith,

     

    sorry for such a long time to my response, but I was quite busy at work.

     

    Please see what I can provide to you:

    Here is a GEL script which exports all idea attachments according the condition in the query:

    <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:util="jelly:util"
         xmlns:xog="http://www.niku.com/xog"
         xmlns:xsd="http://www.w3.org/2001/XMLSchema"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">


    <!-- DB communication string -->
         <gel:setDataSource dbId="Niku"/>
         
    <!-- Parameters -->
         <gel:parameter default="http://192.168.100.243:12122/niku/xog" var="Xog_Url"/>
         <gel:parameter default="admin" var="Xog_Admin"/>
         <gel:parameter default="niku2000" var="Xog_Password"/>
         <gel:parameter default="/apps/home/test2/niku/tmp" var="file_location"/>

    <!-- Login to WS -->
         <soap:invoke endpoint="${Xog_Url}" var="auth">
              <soap:message>
                   <soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xog="http://www.niku.com/xog">
                        <soapenv:Header/>
                        <soapenv:Body>
                             <xog:Login>
                                  <xog:Username>${Xog_Admin}</xog:Username>
                                  <xog:Password>${Xog_Password}</xog:Password>
                             </xog:Login>
                        </soapenv:Body>
                   </soapenv:Envelope>
              </soap:message>
         </soap:invoke>
     
    <!-- Get session id and check if we were succesfully loged in -->
         <gel:set asString="true" select="$auth/soapenv:Envelope/soapenv:Body/xog:SessionID/text()" var="nrsessionID"/>
         <core:choose>
              <core:when test="${nrsessionID == null}">
                   <gel:log category="XOG" level="ERROR">Couldn't Log in to XOG. Check the username/password and Application has started.</gel:log>
              </core:when>
              <core:otherwise>
                   <gel:log category="XOG" level="INFO">Log in successful ${nrsessionID}</gel:log>
              </core:otherwise>
         </core:choose>
         
    <!-- getting all codes of ideas -->
         <sql:query var="MAIN_A">
              select inv.code idea_code
              from inv_investments inv
              left join ODF_CA_IDEA oci on oci.id=inv.ID
              left join ODF_CA_INV ocinv on ocinv.ID=inv.ID
              left join INV_IDEAS invid on invid.ID=inv.ID
              where inv.CODE not in (select idea_name from odf_ca_requirement
                                       where subpozadavek=0 and idea_name is not null) and inv.ODF_OBJECT_CODE='idea'
         </sql:query>

    <!-- read all ideas and export of all attachments to destination specified within the parameter file_location -->
         <core:forEach items="${MAIN_A.rowsByIndex}" var="row">
              <core:set var="idea_code">${row[0]}</core:set>
              <gel:log category="XOG" level="INFO">idea CODE: ${idea_code}</gel:log>
              <!-- přečtení z WS -->
              <soap:invoke endpoint="${Xog_Url}" var="nrrunresulthvp">
                   <soap:message>
                        <soapenv:Envelope xmlns:obj="http://www.niku.com/xog/Object" xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">
                             <soapenv:Header>
                               <obj:Auth>
                                  <obj:SessionID>${nrsessionID}</obj:SessionID>
                                  <obj:Username>${Xog_Admin}</obj:Username>
                                  <obj:Password>${Xog_Password}</obj:Password>
                               </obj:Auth>
                             </soapenv:Header>
                             <soapenv:Body>
                                  <NikuDataBus xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="../xsd/nikuxog_read.xsd">
                                       <Header version="8.0" action="read" objectType="idea" externalSource="NIKU">
                                            <args name="documentLocation" value="${file_location}"/>
                                            <args name="include_resources" value="false"/>
                                            <args name="include_tasks" value="false"/>
                                            <args name="include_allocations" value="false"/>
                                       </Header>
                                       <Query>
                                            <Filter name="objectID" criteria="EQUALS">${idea_code}</Filter>
                                       </Query>
                                  </NikuDataBus>
                             </soapenv:Body>
                        </soapenv:Envelope>
                   </soap:message>
              </soap:invoke>
         </core:forEach>
      <!-- logout -->
      <soap:invoke endpoint="${Xog_Url}" var="nrlogoutresult">
        <soap:message>
          <soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xog="http://www.niku.com/xog">
            <soapenv:Header>
              <xog:Auth>
                <xog:SessionID>${nrsessionID}</xog:SessionID>
              </xog:Auth>
            </soapenv:Header>
            <soapenv:Body>
              <xog:Logout/>
            </soapenv:Body>
          </soapenv:Envelope>
        </soap:message>
      </soap:invoke>
      <gel:log category="XOG" level="INFO">Logged out </gel:log>
    </gel:script>

    You have to specify all parameters. Definitely you will have to change the query to get all projects of your portfolio and as well you have to change the XOG message.

     

    Here you can see query I used to get the commands to rename files from 5million IDs to real names.

    select
    'for /f "delims=" %%i in (''dir /s /b /a-d "'+convert(varchar(7),(select id from CLB_DMS_VERSIONS where FILE_ID=dfil.id))+'"'') do (copy "%%i" c:\tests\idea\'+INV.CODE+'_'+replace(replace(replace(replace(replace(replace(replace(dfil.NAME,' ','_'),'+','_'),'(','_'),')','_'),',','_'),'@','_'),'+','_')+')'
    from inv_investments inv
    left join ODF_CA_IDEA oci on oci.id=inv.ID
    left join ODF_CA_INV ocinv on ocinv.ID=inv.ID
    left join INV_IDEAS invid on invid.ID=inv.ID
    left join CLB_DMS_FILES dfil on dfil.PARENT_FOLDER_ID=oci.ba
    where inv.CODE not in (select idea_name from odf_ca_requirement
                             where subpozadavek=0 and idea_name is not null) and inv.ODF_OBJECT_CODE='idea' and oci.ba is not null

     

    I hope this helps, if you have got any question do not hesitate to ask.

     

    Regards,

    Martin