Module xw.export

Exporting record procedures

Also provides an 'export' package that can be called from applications as a direct Stored Procedure and Wraps those functions.

Usage:

local export = require 'xw.export'

Type xw.export

xw.export.seleToCSVFile(seleId, query, rules, separ, trigger, fileName, zip, bom, notifyTo) Create a CSV export from one or more records according to a selection (done or still to do) and a set of rules.
xw.export.seleToCSVString() Create a CSV export from one or more records according to a selection (done or still to do) and a set of rules The result is the CSV content itself.
xw.export.seleToCSVTAble() Create a table from one or more records according to a selection (done or still to do) and a set of rules The result is the table itself.

Type xw.export

Field(s)

xw.export.seleToCSVFile(seleId, query, rules, separ, trigger, fileName, zip, bom, notifyTo)
Create a CSV export from one or more records according to a selection (done or still to do) and a set of rules.
The result is one or more file CSV stocked into a Zip file if required

Let's see how the rules must be done and how they can lead the operation. The 'rules' parameter is an XML itself and contains one 'export' element containing one or more 'rule' elements.
Each of them MUST have a 'label' attribute and CAN contain zero, one or more 'item' elements. Each of them are a column to export and MUST have a 'label' attribute.
'rule' CAN also contain one (or more...) 'collection' element. If present, the 'collection' element CAN have a 'label' attribute.
Both 'item' and 'collection' elements MUST have a 'xpath' attribute telling what is going to be exported. That value defines what is exported for 'item' elements and which repeatable node of the XML to work on.
Each collection causes the export to be extended to any repetition of the node defined through the collection element that's why it's assumed that only one collection is handled per time. If more than one collection is present, the function behaves as different rules are used.
Let's see the following example:

  <export>
     <rule label="pi">
        <item label="NRECORD"            xpath="/persona_interna/@nrecord"/>
        <item label="MATRICOLA"          xpath="/persona_interna/@matricola"/>
        <item label="NOME"               xpath="/persona_interna/@nome"/>
        <item label="COGNOME"            xpath="/persona_interna/@cognome"/>
        <collection  label="diritti"     xpath="/persona_interna/personal_rights/right">
           <item label="COD"             xpath="./@cod"/>
           <item label="DESCR"           xpath="./@label"/>
           <item label="VALUE"           xpath="."/>
        </collection>
        <collection                      xpath="/persona_interna/recapito">
           <item label="VIA"             xpath="./indirizzo"/>
           <item label="COMUNE"          xpath="./indirizzo/@comune"/>
           <collection                        xpath="./telefono">
              <item label="TIPO"              xpath="./@tipo"/>
              <item label="NUM"               xpath="./@num"/>
           </collection>
        </collection>
     </rule>
     <rule label="si">
        <item label="NRECORD"       xpath="/struttura_interna/@nrecord"/>
        <item label="NOME"          xpath="/struttura_interna/nome"/>
     </rule>
  </export>

This rule set provides two different rules.
The first one is named 'pi' and asks 4 iterms (NRECORD, MATRICOLA, NOME and COGNOME) that can be seen as fixed values in the record. The record also has some repetitions for user rights (collection 'diritti') and user addess and phone number (unnamed collection). Since this rule uses more than one collection the function split this rule in two as if the main 'items' where asked once with the first collection and once with the second one.
There's a second rule, named 'si' that appies to differente columns.
Now, let's see the behavior (imagine the required output file name is 'output'.
The function will export a first CSV file named output-pi-diritti.csv that will contain the following columns:
NRECORD, MATRICOLA, NOME, COGNOME, COD, DESCR, VALUE
Than the function will export a second CSV file nameed output-pi-gr2 (the second collection has no label so the output is numbered) that will contain the following columns:
NRECORD, MATRICOLA, NOME, COGNOME, VIA, COMUNE, TIPO, NUM
Than we will have the last file named output-si.csv that will simply contain:
NRECORD, NOME

Pay attention: you can combine columns from different information units within the same rule, it's not forbidden, even if the result can be hard to handle. Whitin the same rule you SHOULD have just one collection but you CAN have more than one. The function will split as said. Within a collection, anyway, you CAN have zero, one or more 'item' but you MUST have zero or one 'collection'. Having more than one collection is allowed only at the root of the rule.

More: 'xpath' of root elements (both 'item' or 'collection') MUST start with a '/', so they are supposed to be absolute. When inside a collection the same 'xpath' MUST start with a '.', so they are required to be relative paths.

There's no limit to the collection nesting, only on the number of collections you can use inside each collection.

Parameters

  • #string seleId : The selection ID of all records to export
  • #string query : An alternative query to execute (if seleId is nil or empty string)
  • #string rules : XML rules to apply
  • #string separ : The separator pattern. If nil or empty string, a single comma (,) is used
  • #string trigger : Tell if trigger must be switched off (false) or let on (true)
  • #string fileName : The required file name. It's assumed to be a simple name, the server will decide where to put the created file
  • #string zip :

    Ask for the result to be a Zip File if value is "true" or "yes". In this case the fileName parameter will be used

                         to name the Zip file while it's content will follow what said for rules file naming.
  • #string bom : Ask for the result file to contain a BOM to specify encoding.
  • #string notifyTo : Mail address to which send a end of job notify

Return values

  1. #boolean: True or false (success or failure)
  2. #string: The file name (or separated file name list using the same separ parameter)
xw.export.seleToCSVString()
Create a CSV export from one or more records according to a selection (done or still to do) and a set of rules The result is the CSV content itself.
This cause the function to act with one and only one rule. If more than one rule is present, only the first one is evaluated See seleToCSVFile for parameter details

Return value

#string: The CSV export result

xw.export.seleToCSVTAble()
Create a table from one or more records according to a selection (done or still to do) and a set of rules The result is the table itself.
This cause the function to act with one and only one rule. If more than one rule is present, only the first one is evaluated See seleToCSVFile for parameter details

Return value

#string: The CSV export result