{"id":5744,"date":"2024-06-11T13:41:02","date_gmt":"2024-06-11T11:41:02","guid":{"rendered":"https:\/\/gpmfactory.com\/?p=5744"},"modified":"2024-06-11T22:04:06","modified_gmt":"2024-06-11T20:04:06","slug":"office-word-and-oracle-ords","status":"publish","type":"post","link":"https:\/\/gpmfactory.com\/index.php\/2024\/06\/11\/office-word-and-oracle-ords\/","title":{"rendered":"Office Word and Oracle ORDS"},"content":{"rendered":"\n\n\n<h1 class=\"wp-block-heading\">Objective<\/h1>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Combine the use of Office Word and Oracle ORDS to create a document generator controlled from Word.<\/li>\n\n\n\n<li>Automatically export these documents to PDF format.<\/li>\n\n\n\n<li>Develop a simplified service (Windows only) capable of launching multiple instances of document generation.<\/li>\n<\/ul>\n\n\n\n<p><\/p>\n\n\n\n<h1 class=\"wp-block-heading\">Description<\/h1>\n\n\n\n<p>This is a prototype I developed that prioritizes the use of Office Word both as a design tool and for merging with data from an Oracle REST ORDS service. I tested several approaches with data obtained in JSON and XML formats.<\/p>\n\n\n\n<p>This project, <a href=\"https:\/\/github.com\/patrickmonaco\/woords\">available from Github<\/a>,  is called <code>WOORDS<\/code>. It is implemented as a module written in VBA within a Word template. This module handles the following tasks:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Obtaining REST data from an ORDS server, in JSON or XML format<\/li>\n\n\n\n<li>Loading a Word template suitable for the document to be generated<\/li>\n\n\n\n<li>Merging elements using Content Controls<\/li>\n\n\n\n<li>Exporting the document as a PDF<\/li>\n\n\n\n<li>Optionally, sending the PDF document via a REST API.<\/li>\n<\/ul>\n\n\n\n<p>Woords relies on a third-party module, vba-json, for reading documents in JSON format.<\/p>\n\n\n\n<div class=\"wp-block-group is-nowrap is-layout-flex wp-container-core-group-is-layout-2 wp-block-group-is-layout-flex\">\n<figure class=\"wp-block-image size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"623\" src=\"http:\/\/gpmfactory.com\/wp-content\/uploads\/2024\/05\/image-15-1024x623.png\" alt=\"\" class=\"wp-image-5814\" style=\"width:273px;height:auto\" srcset=\"https:\/\/gpmfactory.com\/wp-content\/uploads\/2024\/05\/image-15-1024x623.png 1024w, https:\/\/gpmfactory.com\/wp-content\/uploads\/2024\/05\/image-15-300x182.png 300w, https:\/\/gpmfactory.com\/wp-content\/uploads\/2024\/05\/image-15-768x467.png 768w, https:\/\/gpmfactory.com\/wp-content\/uploads\/2024\/05\/image-15.png 1306w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<div class=\"wp-block-columns is-layout-flex wp-container-core-columns-is-layout-1 wp-block-columns-is-layout-flex\">\n<div class=\"wp-block-column is-layout-flow wp-block-column-is-layout-flow\" style=\"flex-basis:100%\">\n<div class=\"wp-block-group\"><div class=\"wp-block-group__inner-container is-layout-constrained wp-block-group-is-layout-constrained\">\n<figure class=\"wp-block-image size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" width=\"867\" height=\"1024\" src=\"http:\/\/gpmfactory.com\/wp-content\/uploads\/2024\/05\/image-12-867x1024.png\" alt=\"\" class=\"wp-image-5803\" style=\"width:276px;height:auto\" srcset=\"https:\/\/gpmfactory.com\/wp-content\/uploads\/2024\/05\/image-12-867x1024.png 867w, https:\/\/gpmfactory.com\/wp-content\/uploads\/2024\/05\/image-12-254x300.png 254w, https:\/\/gpmfactory.com\/wp-content\/uploads\/2024\/05\/image-12-768x907.png 768w, https:\/\/gpmfactory.com\/wp-content\/uploads\/2024\/05\/image-12.png 1247w\" sizes=\"auto, (max-width: 867px) 100vw, 867px\" \/><\/figure>\n<\/div><\/div>\n<\/div>\n<\/div>\n<\/div>\n\n\n\n<p>A command line script, woords.cmd, launches Office Word with the needed parameters.<\/p>\n\n\n\n<p>Let&rsquo;s have a look to the way Word is started:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>WINWORD.EXE \/q \/p%1 \/z%REPDIR%\\templates\\woords.dotm<\/code><\/pre>\n\n\n\n<p>The tricky part involves calling Word with parameter passing. Specifically, in our case, it is necessary to pass information regarding the template to be used and possibly an ID to select specific data. In command mode, there is no selector to add parameters, but I found that \/p was not used, so I utilized it to convey the parameters.<\/p>\n\n\n\n<p>The structure of the parameter string is defined as follows:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>c<\/strong>:config<\/li>\n\n\n\n<li><strong>i<\/strong>:pkid<\/li>\n\n\n\n<li><strong>f<\/strong>:folder Name<\/li>\n<\/ul>\n\n\n\n<p>ex: <code><strong>c:order:i:652:f:zzzzzz<\/strong><\/code><\/p>\n\n\n\n<p>All these details are hidden in the woords command script. We just need to launch the Woords in the following ways:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>From the command line<\/li>\n\n\n\n<li>Via a URL call<\/li>\n\n\n\n<li>Through a WebSocket (not implemented) <a href=\"https:\/\/gpmfactory.com\/index.php\/2024\/03\/09\/call-host-commands-from-a-browser\/\">see my other post<\/a><\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\">Architecture<\/h3>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"519\" src=\"https:\/\/gpmfactory.com\/wp-content\/uploads\/2024\/05\/image-1-1024x519.png\" alt=\"\" class=\"wp-image-5768\" srcset=\"https:\/\/gpmfactory.com\/wp-content\/uploads\/2024\/05\/image-1-1024x519.png 1024w, https:\/\/gpmfactory.com\/wp-content\/uploads\/2024\/05\/image-1-300x152.png 300w, https:\/\/gpmfactory.com\/wp-content\/uploads\/2024\/05\/image-1-768x389.png 768w, https:\/\/gpmfactory.com\/wp-content\/uploads\/2024\/05\/image-1-1536x778.png 1536w, https:\/\/gpmfactory.com\/wp-content\/uploads\/2024\/05\/image-1-2048x1037.png 2048w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<p>Important prerequisite: The solution described in this article only works on a physical machine running Windows, as Office Word needs to be activated, or in a Windows VirtualBox under Linux, or in a Windows Docker container under Windows.<\/p>\n\n\n\n<p>In server mode, the HTTP listener receiving the request must be able to trigger the execution of Office Word in command mode, and the listener must be on the same machine as Office Word. Therefore, we can imagine the server composed of an HTTP\/HTTPS listener based on Node.js (using the <code>access<\/code> module), for example, or an Apache HTTP server with PHP installed. Woords includes a server, swoords.js, running with Node.js.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Use Cases<\/h3>\n\n\n\n<p>Woords is suitable for the following types of documents:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Master\/detail style documents with one document per PDF file (invoice, purchase order, employee record, product sheet, etc.)<\/li>\n\n\n\n<li>Simple lists in table form<\/li>\n<\/ul>\n\n\n\n<p>Woords isn&rsquo;t for:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Reports with grouping and page breaks based on break levels<\/li>\n<\/ul>\n\n\n\n<h4 class=\"wp-block-heading\">Using in server mode<\/h4>\n\n\n\n<p>Since the operation of a VBA module is single-threaded, it is not feasible to make it a service at its level. However, it is possible to launch multiple instances of Word from a Node.js server. The principle is very basic, as the server launches Word in command line mode as discussed previously.<\/p>\n\n\n\n<p>This allows for a certain level of parallelism. However, it is not advisable to expect intensive use with a large number of simultaneous requests, as this quickly leads to stability issues, resulting in Word instances that remain open.<\/p>\n\n\n\n<p>A simple example server is provided with Woords (server\\swoords.js). As a prerequisite, it is necessary to have previously installed Node.js along with the dependencies:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>express<\/li>\n\n\n\n<li>fs<\/li>\n\n\n\n<li>crypto<\/li>\n\n\n\n<li>child_process<\/li>\n<\/ul>\n\n\n\n<p>This server provides two endpoints:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><code>\/viewpdf\/:config\/:pkid?<\/code>  for launching pdf generation and viewing output pdf file<\/li>\n\n\n\n<li><code>\/pdf\/:config\/:pkid?<\/code>  for generation only<\/li>\n<\/ul>\n\n\n\n<p><\/p>\n\n\n\n<h4 class=\"wp-block-heading\">Office Word specificities used for merging<\/h4>\n\n\n\n<p>To represent variable fields, I chose to use Content Controls (CC) and Repeating Content Controls (RCC).<\/p>\n\n\n\n<p>Each CC has at least the following standard attributes:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Title<\/li>\n\n\n\n<li>Tag<\/li>\n<\/ul>\n\n\n\n<p>I use the Tag attribute to record the name of the JSON or XML node with which it should be merged. In the case where it needs to display an array, the \u00earging will stand between a RCC and a Array type node wich the same name.<\/p>\n\n\n\n<h1 class=\"wp-block-heading\">Setup and Use<\/h1>\n\n\n\n<h2 class=\"wp-block-heading\">Installation<\/h2>\n\n\n\n<p>Installation steps<\/p>\n\n\n\n<p><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Download woords01.zip from <a href=\"https:\/\/github.com\/patrickmonaco\/woords\" target=\"_blank\" rel=\"noreferrer noopener\">https:\/\/github.com\/patrickmonaco\/woords<\/a><\/li>\n\n\n\n<li>Create a directory (ie: c:\\app\\woords) and unzip woords.zip<\/li>\n\n\n\n<li>In Office Word, add this directory to the list of trust locations in the Trust Center<\/li>\n\n\n\n<li>Complete the content of the seettings.xml file by entering the endpoint for obtaining OAUTH tokens and the endpoint corresponding to the REST module<\/li>\n\n\n\n<li>Optional: Install Node.js if you want to set up a Woords service<\/li>\n\n\n\n<li>Start woords by entering: <code>bin\\woords configfile<\/code><\/li>\n<\/ul>\n\n\n\n<figure class=\"wp-block-image size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"728\" src=\"https:\/\/gpmfactory.com\/wp-content\/uploads\/2024\/05\/image-7-1024x728.png\" alt=\"\" class=\"wp-image-5785\" style=\"width:653px;height:auto\" srcset=\"https:\/\/gpmfactory.com\/wp-content\/uploads\/2024\/05\/image-7-1024x728.png 1024w, https:\/\/gpmfactory.com\/wp-content\/uploads\/2024\/05\/image-7-300x213.png 300w, https:\/\/gpmfactory.com\/wp-content\/uploads\/2024\/05\/image-7-768x546.png 768w, https:\/\/gpmfactory.com\/wp-content\/uploads\/2024\/05\/image-7-1536x1093.png 1536w, https:\/\/gpmfactory.com\/wp-content\/uploads\/2024\/05\/image-7.png 1628w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<h3 class=\"wp-block-heading\">Access Control<\/h3>\n\n\n\n<p>The data is obtained by Word from the VBA code and requests to the REST APIs are made via the MSXML2.XMLHTTP class from VBA.<\/p>\n\n\n\n<p>Access is controlled by OAUTH with a Client Credentials type client.<\/p>\n\n\n\n<p>This means that you must create an OAUTH Client of the <em>Client Credentials<\/em> type at ORDS level (see appendices) and save the Client ID and Client Secret in a settings.xml file accessible by Word. (see appendices)<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Create an OAUTH client (see appendices) and copy the client Id and Secret values<\/li>\n\n\n\n<li>Adapt the settings.xml file by copying the ID and SECRET.<\/li>\n\n\n\n<li>You must indicate the endpoint to obtain a valid token and the endpoint of the module. Do not put a trailing slash for the value of the ENDPOINT tag.<\/li>\n<\/ul>\n\n\n\n<figure class=\"wp-block-image size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"421\" src=\"https:\/\/gpmfactory.com\/wp-content\/uploads\/2024\/05\/image-8-1024x421.png\" alt=\"\" class=\"wp-image-5786\" style=\"width:546px;height:auto\" srcset=\"https:\/\/gpmfactory.com\/wp-content\/uploads\/2024\/05\/image-8-1024x421.png 1024w, https:\/\/gpmfactory.com\/wp-content\/uploads\/2024\/05\/image-8-300x123.png 300w, https:\/\/gpmfactory.com\/wp-content\/uploads\/2024\/05\/image-8-768x316.png 768w, https:\/\/gpmfactory.com\/wp-content\/uploads\/2024\/05\/image-8.png 1128w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\">Organizing models and data sources<\/h2>\n\n\n\n<p>The same Word template can be used with different data types and vice versa. For this reason, I introduced the notion of configuration. This is an XML file which indicates the template, the REST endpoint to use as well as miscellaneous informations:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>&lt;CONF&gt;\n    &lt;TEMPLATE&gt;Vision3&lt;\/TEMPLATE&gt;\n    &lt;ROOT_NODE&gt;ORDERS&lt;\/ROOT_NODE&gt;\n    &lt;HANDLER&gt;\/ordersxml\/&lt;\/HANDLER&gt;\n    &lt;HEADER&gt;HEADER&lt;\/HEADER&gt;\n    &lt;SPLIT&gt;Y&lt;\/SPLIT&gt;\n    &lt;LIST&gt;N&lt;\/LIST&gt;\n    &lt;SAVEDOC&gt;N&lt;\/SAVEDOC&gt;\n    &lt;POST_URL&gt;https:\/xxx\/&lt;\/POST_URL&gt;\n    &lt;DEBUG_LEVEL&gt;0&lt;\/DEBUG_LEVEL&gt;\n&lt;\/CONF&gt;<\/code><\/pre>\n\n\n\n<p>In the configuration above, we tell Woords to use a template called vision3 and to fetch the data by making a REST call to a handler called \/ordersxml\/ and which must be suffixed at the endpoint specified in a global settings file.<br>There must be a configuration for each document template. These files are saved in the conf directory but it is also possible to place them in a global directory accessible from a REST service. In this case, you must create a woo_repo table (see the setup\\woords_createrepository.sql creation script) and you must indicate the corresponding handler in the settings file described below: \/conf\/ . This handler is provided in the gpm.demo.sales module (setup\\ORDS_REST_DEMO_gpm.demo.sales)<\/p>\n\n\n\n<p>SAVEDOC: Indicates whether you wish to keep the intermediate Word file that was generated.<br>POST_URL: REST handler to which to post the PDF file or not. NOT IMPLEMENTED.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Global Settings for Woords<\/h2>\n\n\n\n<pre class=\"wp-block-preformatted\">&lt;CONF&gt;\n    &lt;OAUTH_ENDPOINT&gt;https:\/\/xxx\/ords\/demo\/oauth\/token&lt;\/OAUTH_ENDPOINT&gt; \n    &lt;ID&gt;XXXXXXXXXXXXXXXXXXXXXXX&lt;\/ID&gt;\n    &lt;SECRET&gt;ZZZZZZZZZZZZZZZZZ&lt;\/SECRET&gt; \n    &lt;ENDPOINT&gt;yyyyyyyyyyyyyy\/ords\/demo\/sales&lt;\/ENDPOINT&gt; \n    &lt;VERBOSE&gt;N&lt;\/VERBOSE&gt;\n    &lt;DEBUG&gt;N&lt;\/DEBUG&gt;\n    &lt;REPO&gt;\/conf\/&lt;\/REPO&gt;\n&lt;\/CONF&gt;   <\/pre>\n\n\n\n<p>Comments on the settings file:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>OAUTH_ENDPOINT corresponds to the URL which provides an OAUTH token (see ORDS doc)<\/li>\n\n\n\n<li>ID is the Client ID value for Client Credentials<\/li>\n\n\n\n<li>SECRET is the value of Customer Secret<\/li>\n\n\n\n<li>ENDPOINT: REST module URL<\/li>\n\n\n\n<li>VERBOSE: display or not a detailed log<\/li>\n\n\n\n<li>DEBUG: Display a detailed log<\/li>\n\n\n\n<li>REPO: optional. REST handler that provides the content of a configuration based on a name passed in the URL. Overrides individual xxx.conf configuration files<\/li>\n<\/ul>\n\n\n\n<p><\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Functional tests<\/h2>\n\n\n\n<p>I took as a sample the one supplied with the APEX installations and which installs tables corresponding to order management.<br><em><mark style=\"background-color:rgba(0, 0, 0, 0)\" class=\"has-inline-color has-pale-cyan-blue-color\">A collection of customers, stores, products, and orders. This dataset includes JSON data for the product description, and longitude \/ latitude for the stores.<\/mark><\/em><br><\/p>\n\n\n\n<p>This is sufficient to illustrate common cases.<br>Reminders for installing this dataset<\/p>\n\n\n\n<p>Sql workshop \/ Utilities \/ Sample Datasets. <br>Choisir <em>Customer Orders<\/em>.<br><\/p>\n\n\n\n<p>Samples provided with <code>Woords<\/code><\/p>\n\n\n\n<p>The following configurations are ready to use for demo\/training purpose<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>helloworld (basic message)<\/li>\n\n\n\n<li>invoice  (standard invoice with a master\/detail<\/li>\n\n\n\n<li>order  (standard purchase order with a master\/detail)<\/li>\n\n\n\n<li>emp (standard emp table )<\/li>\n\n\n\n<li>cols (use for report )<\/li>\n<\/ul>\n\n\n\n<p><\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Designing a Word template suitable for merging<\/h2>\n\n\n\n<p>To test my application, I used a standard template offered in Word.<br>It&rsquo;s called <em>Professional Sales Invoice<\/em> and it&rsquo;s included in the Business Blue Design business template series.<\/p>\n\n\n\n<p>The dynamic fields are represented by <em>Content Controls<\/em>.<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Give a tag name to the CC corresponding to a XML tag or to a Json node to be displayed..<\/li>\n\n\n\n<li>Implement the multi-line part, corresponding to the order lines, using a <em>Repeating Content Control<\/em> (RCC) and incorporating other <em>Content Controls<\/em> (CC), one per column.<\/li>\n\n\n\n<li>Set the mapping between a XML tag or JSON node and the CC by using the <strong>Tag<\/strong> attribute (cf below).<\/li>\n<\/ul>\n\n\n\n<p>The tag name in RCC must be the same as the XML \u00ab\u00a0father\u00a0\u00bb tag. (<code>ORDER_ITEMS_FOP_V<\/code> in my example)<\/p>\n\n\n\n<figure class=\"wp-block-image size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"707\" src=\"https:\/\/gpmfactory.com\/wp-content\/uploads\/2024\/05\/image-2-1024x707.png\" alt=\"\" class=\"wp-image-5773\" style=\"width:672px;height:auto\" srcset=\"https:\/\/gpmfactory.com\/wp-content\/uploads\/2024\/05\/image-2-1024x707.png 1024w, https:\/\/gpmfactory.com\/wp-content\/uploads\/2024\/05\/image-2-300x207.png 300w, https:\/\/gpmfactory.com\/wp-content\/uploads\/2024\/05\/image-2-768x530.png 768w, https:\/\/gpmfactory.com\/wp-content\/uploads\/2024\/05\/image-2.png 1292w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<figure class=\"wp-block-image size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"719\" src=\"https:\/\/gpmfactory.com\/wp-content\/uploads\/2024\/05\/image-3-1024x719.png\" alt=\"\" class=\"wp-image-5774\" style=\"width:655px;height:auto\" srcset=\"https:\/\/gpmfactory.com\/wp-content\/uploads\/2024\/05\/image-3-1024x719.png 1024w, https:\/\/gpmfactory.com\/wp-content\/uploads\/2024\/05\/image-3-300x211.png 300w, https:\/\/gpmfactory.com\/wp-content\/uploads\/2024\/05\/image-3-768x539.png 768w, https:\/\/gpmfactory.com\/wp-content\/uploads\/2024\/05\/image-3.png 1270w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<h3 class=\"wp-block-heading\">Repeating Content Control setup<\/h3>\n\n\n\n<figure class=\"wp-block-image size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"971\" src=\"https:\/\/gpmfactory.com\/wp-content\/uploads\/2024\/05\/image-6-1024x971.png\" alt=\"\" class=\"wp-image-5778\" style=\"width:657px;height:auto\" srcset=\"https:\/\/gpmfactory.com\/wp-content\/uploads\/2024\/05\/image-6-1024x971.png 1024w, https:\/\/gpmfactory.com\/wp-content\/uploads\/2024\/05\/image-6-300x285.png 300w, https:\/\/gpmfactory.com\/wp-content\/uploads\/2024\/05\/image-6-768x728.png 768w, https:\/\/gpmfactory.com\/wp-content\/uploads\/2024\/05\/image-6.png 1472w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<figure class=\"wp-block-image size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"633\" src=\"https:\/\/gpmfactory.com\/wp-content\/uploads\/2024\/05\/image-4-1024x633.png\" alt=\"\" class=\"wp-image-5776\" style=\"width:726px;height:auto\" srcset=\"https:\/\/gpmfactory.com\/wp-content\/uploads\/2024\/05\/image-4-1024x633.png 1024w, https:\/\/gpmfactory.com\/wp-content\/uploads\/2024\/05\/image-4-300x186.png 300w, https:\/\/gpmfactory.com\/wp-content\/uploads\/2024\/05\/image-4-768x475.png 768w, https:\/\/gpmfactory.com\/wp-content\/uploads\/2024\/05\/image-4-200x125.png 200w, https:\/\/gpmfactory.com\/wp-content\/uploads\/2024\/05\/image-4.png 1332w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<p>Content Controls types to be used for repeating sections<br>When adding a Content Control inside a repeating section, it is important to note that the oblong symbol designating the CC must be light blue and not with a pictogram of arrows pointing to the right or the left. If this second case occurs (which I consider to be an anomaly, but for which I have no explanation), an additional CC must be created just before. If we leave things as is (with \u00ab\u00a0arrows\u00a0\u00bb), we lose the formatting and alignment that we could have given to CCs containing numbers.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Conditional displaying<\/h3>\n\n\n\n<p>It is possible to condition the display of the content of an RCC according to the presence or absence of data.<\/p>\n\n\n\n<p>For example, if there are no direct reports at the level of a manager, we neutralize the display of the corresponding RCC. To do this, you must encapsulate the RCC in another RCC with the same tag name but prefixed with the \u201c?\u201d sign.<br>If the RCC tag = direct_reports, the RCC container tag will be: ?direct_reports<\/p>\n\n\n\n<figure class=\"wp-block-image size-full is-resized\"><img loading=\"lazy\" decoding=\"async\" width=\"838\" height=\"398\" src=\"https:\/\/gpmfactory.com\/wp-content\/uploads\/2024\/05\/image-14.png\" alt=\"\" class=\"wp-image-5807\" style=\"width:463px;height:auto\" srcset=\"https:\/\/gpmfactory.com\/wp-content\/uploads\/2024\/05\/image-14.png 838w, https:\/\/gpmfactory.com\/wp-content\/uploads\/2024\/05\/image-14-300x142.png 300w, https:\/\/gpmfactory.com\/wp-content\/uploads\/2024\/05\/image-14-768x365.png 768w\" sizes=\"auto, (max-width: 838px) 100vw, 838px\" \/><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\">Producing REST data<\/h2>\n\n\n\n<p><\/p>\n\n\n\n<h4 class=\"wp-block-heading\">JSON based on a collection Query<\/h4>\n\n\n\n<h4 class=\"wp-block-heading\">XML Data input<\/h4>\n\n\n\n<p>I have already developed a <a href=\"https:\/\/gpmfactory.com\/index.php\/2023\/09\/06\/fop-lab-experimental-apex-project\/\" target=\"_blank\" rel=\"noreferrer noopener\">tool to produce SQL queries that generate XML<\/a>. The constraint is that I cannot use a Collection Query handler. Instead, I have to use a Media Resource query, which results in the loss of pagination elements.<\/p>\n\n\n\n<p>The <a href=\"#Requete_XML_retournant_des_commandes\" target=\"_blank\" rel=\"noreferrer noopener\">XML results generated<\/a> by these queries can also be converted to JSON. Thus, a Media Resource handler with a MIME type of application\/json can be used. Since vba-json is case sensitive, it is better to generate column aliases in lowercase.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">PDF files and logs<\/h3>\n\n\n\n<p>Each Woords session generates a random label that is used to create a folder in the pdf directory and to create a log file in the logs directory.<\/p>\n\n\n\n<p>It is the user&rsquo;s responsibility to purge these directories and files.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\">Performances<\/h4>\n\n\n\n<p>Launching 1 to 5 Word instances. During the test with 5 instances, one ended with an error and a second one stopped after two documents. Only three threads survived.<\/p>\n\n\n\n<p>The ramp-up was two seconds between each launch. Conclusion: the concurrency level is a maximum of three. Beyond that, there are risks of abrupt termination.<\/p>\n\n\n\n<p>The maximum throughput is approximately 1.7 PDF documents per second.<\/p>\n\n\n\n<figure class=\"wp-block-image size-full is-resized\"><img loading=\"lazy\" decoding=\"async\" width=\"566\" height=\"186\" src=\"https:\/\/gpmfactory.com\/wp-content\/uploads\/2024\/05\/image-13.png\" alt=\"\" class=\"wp-image-5805\" style=\"width:409px;height:auto\" srcset=\"https:\/\/gpmfactory.com\/wp-content\/uploads\/2024\/05\/image-13.png 566w, https:\/\/gpmfactory.com\/wp-content\/uploads\/2024\/05\/image-13-300x99.png 300w\" sizes=\"auto, (max-width: 566px) 100vw, 566px\" \/><\/figure>\n\n\n\n<h1 class=\"wp-block-heading\">Annexes<\/h1>\n\n\n\n<p><\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Module ORDS pour la gestion des commandes<\/h3>\n\n\n\n<figure class=\"wp-block-image size-full is-resized\"><img loading=\"lazy\" decoding=\"async\" width=\"464\" height=\"498\" src=\"https:\/\/gpmfactory.com\/wp-content\/uploads\/2024\/04\/image-2.png\" alt=\"\" class=\"wp-image-5751\" style=\"width:299px;height:auto\" srcset=\"https:\/\/gpmfactory.com\/wp-content\/uploads\/2024\/04\/image-2.png 464w, https:\/\/gpmfactory.com\/wp-content\/uploads\/2024\/04\/image-2-280x300.png 280w\" sizes=\"auto, (max-width: 464px) 100vw, 464px\" \/><\/figure>\n\n\n\n<p><br>The module gpm.demo.sales is protected by a privilege named orders.priv.<\/p>\n\n\n\n<p>One must create a CLIENT_CREDENTIAL client and get back the Client id  and Client secret. These informations must be stored in the settings.xml file.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">ORDERS data model<\/h3>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"495\" src=\"https:\/\/gpmfactory.com\/wp-content\/uploads\/2024\/04\/image-1-1024x495.png\" alt=\"\" class=\"wp-image-5749\" srcset=\"https:\/\/gpmfactory.com\/wp-content\/uploads\/2024\/04\/image-1-1024x495.png 1024w, https:\/\/gpmfactory.com\/wp-content\/uploads\/2024\/04\/image-1-300x145.png 300w, https:\/\/gpmfactory.com\/wp-content\/uploads\/2024\/04\/image-1-768x371.png 768w, https:\/\/gpmfactory.com\/wp-content\/uploads\/2024\/04\/image-1.png 1328w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<figure class=\"wp-block-image size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"711\" src=\"https:\/\/gpmfactory.com\/wp-content\/uploads\/2024\/04\/image-1024x711.png\" alt=\"\" class=\"wp-image-5747\" style=\"width:759px;height:auto\" srcset=\"https:\/\/gpmfactory.com\/wp-content\/uploads\/2024\/04\/image-1024x711.png 1024w, https:\/\/gpmfactory.com\/wp-content\/uploads\/2024\/04\/image-300x208.png 300w, https:\/\/gpmfactory.com\/wp-content\/uploads\/2024\/04\/image-768x533.png 768w, https:\/\/gpmfactory.com\/wp-content\/uploads\/2024\/04\/image.png 1341w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<h4 class=\"wp-block-heading\">Vues ajout\u00e9es <\/h4>\n\n\n\n<p>In order to add formating and computing, it&rsquo;s better to minimize the code in the REST handler code, and move this task to a sql view level.<\/p>\n\n\n\n<p>ORDER_ITEMS_FOP_V<\/p>\n\n\n\n<p>This view calculates the amounts for each order line<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>CREATE OR REPLACE FORCE EDITIONABLE VIEW \"ORDER_ITEMS_FOP_V\" (\"ORDER_ID\", \"PRODUCT_ID\", \"PRODUCT_NAME\", \"QUANTITY\", \"UNIT_PRICE\", \"MNT\",<br>FOREIGN KEY (\"ORDER_ID\")<br>REFERENCES \"ORDERS\" (\"ORDER_ID\") DISABLE) AS<br>select order_id, i.product_id, product_name,<br>quantity, i.unit_price ,(quantity * i.unit_price) mnt<br>from order_items i,<br>products p<br>where i.product_id = p.product_id;<\/code><\/pre>\n\n\n\n<p>ORDER_FOOTER_FOP_V<\/p>\n\n\n\n<p>This view provides the global amounts for the footer.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>CREATE OR REPLACE FORCE EDITIONABLE VIEW \"ORDER_FOOTER_FOP_V\" (\"ORDER_ID\", \"TOTAL\", \"TVA\", \"TTC\", \"SHIPPING\",<br>FOREIGN KEY (\"ORDER_ID\")<br>REFERENCES \"ORDERS\" (\"ORDER_ID\") DISABLE) AS<br>select order_id, sum(quantity * i.unit_price) total,<br>round(sum(quantity * i.unit_price * 0.2),2) tva,<br>round(sum(quantity * i.unit_price * 1.2),2) ttc,<br>100 as \"shipping\"<br>from order_items i,<br>products p<br>where i.product_id = p.product_id<br>group by order_id;<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">XML statement giving orders<\/h3>\n\n\n\n<p>In the sample module, see the <code>ordersxml<\/code> handler<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code><\/code><\/pre>\n\n\n\n<h4 class=\"wp-block-heading\">JSON Collection Query<\/h4>\n\n\n\n<p>In order to keep the advantages provided by the use of a query collection (pagination, among others), we can create a handler with a main query and secondary links to be able to search the data via an additional REST call.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>\nselect CUSTOMERS.CUSTOMER_ID as CUSTOMER_ID,\nCUSTOMERS.FULL_NAME as FULL_NAME,\nCUSTOMERS.EMAIL_ADDRESS as EMAIL_ADDRESS,\nORDERS.ORDER_DATETIME as ORDER_DATETIME,\nORDERS.ORDER_ID as ORDER_ID,\n'..\/orders\/' || ORDERS.ORDER_ID || '\/lines' AS \"<strong>$order_lines<\/strong>\"\nfrom\nORDERS ORDERS,\nCUSTOMERS CUSTOMERS\nwhere CUSTOMERS.CUSTOMER_ID = ORDERS.CUSTOMER_ID\nand ORDER_ID = :id<\/code><\/pre>\n\n\n\n<figure class=\"wp-block-image size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"623\" src=\"https:\/\/gpmfactory.com\/wp-content\/uploads\/2024\/05\/image-15-1024x623.png\" alt=\"\" class=\"wp-image-5814\" style=\"width:557px;height:auto\" srcset=\"https:\/\/gpmfactory.com\/wp-content\/uploads\/2024\/05\/image-15-1024x623.png 1024w, https:\/\/gpmfactory.com\/wp-content\/uploads\/2024\/05\/image-15-300x182.png 300w, https:\/\/gpmfactory.com\/wp-content\/uploads\/2024\/05\/image-15-768x467.png 768w, https:\/\/gpmfactory.com\/wp-content\/uploads\/2024\/05\/image-15.png 1306w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<p>Pour la technique d&rsquo;insertion d&rsquo;un <a href=\"https:\/\/oracle-base.com\/articles\/misc\/oracle-rest-data-services-ords-including-hyperlinks-in-json-output\">lien relatif, cf article Oracle-Base<\/a>.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Ajout d&rsquo;un client OAUTH <\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>BEGIN\n  OAUTH.create_client(\n    p_name            =&gt; 'word_client',\n    p_grant_type      =&gt; 'client_credentials',\n    p_owner           =&gt; 'GPM Factory',\n    p_description     =&gt; 'A client Office Word for Orders printing',\n    p_support_email   =&gt; 'pmo@email.com',\n    p_privilege_names =&gt; 'orders.priv'\n  );\n COMMIT;\nEND;\n <\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">Starting woords in command line<\/h3>\n\n\n\n<p>Enter <code>bin\\woords<\/code><\/p>\n\n\n\n<p>Available Word selectors used:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>\/q Word trigger in silent mode<\/li>\n\n\n\n<li>\/p selector not used by Word and added to allow passing a parameter when launching Word which will be used by a VBA macro. The character string which immediately follows the \/p selector responds to a precise syntax, specific to woords and which is indicated earlier in this document.<\/li>\n\n\n\n<li>\/z Opens a new document based on a Word template (woords.dotm) and TRIGGERS a possible macro named Autonew.<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\">Documents samples<\/h3>\n\n\n\n<h4 class=\"wp-block-heading\">Purchase order from TemplateLAB site<\/h4>\n\n\n\n<figure class=\"wp-block-image size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" width=\"867\" height=\"1024\" src=\"https:\/\/gpmfactory.com\/wp-content\/uploads\/2024\/05\/image-12-867x1024.png\" alt=\"\" class=\"wp-image-5803\" style=\"width:664px;height:auto\" srcset=\"https:\/\/gpmfactory.com\/wp-content\/uploads\/2024\/05\/image-12-867x1024.png 867w, https:\/\/gpmfactory.com\/wp-content\/uploads\/2024\/05\/image-12-254x300.png 254w, https:\/\/gpmfactory.com\/wp-content\/uploads\/2024\/05\/image-12-768x907.png 768w, https:\/\/gpmfactory.com\/wp-content\/uploads\/2024\/05\/image-12.png 1247w\" sizes=\"auto, (max-width: 867px) 100vw, 867px\" \/><\/figure>\n\n\n\n<h4 class=\"wp-block-heading\">Invoice from  Microsoft templates library<\/h4>\n\n\n\n<figure class=\"wp-block-image size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" width=\"813\" height=\"1024\" src=\"https:\/\/gpmfactory.com\/wp-content\/uploads\/2024\/04\/image-3-813x1024.png\" alt=\"\" class=\"wp-image-5753\" style=\"width:650px;height:auto\" srcset=\"https:\/\/gpmfactory.com\/wp-content\/uploads\/2024\/04\/image-3-813x1024.png 813w, https:\/\/gpmfactory.com\/wp-content\/uploads\/2024\/04\/image-3-238x300.png 238w, https:\/\/gpmfactory.com\/wp-content\/uploads\/2024\/04\/image-3-768x967.png 768w, https:\/\/gpmfactory.com\/wp-content\/uploads\/2024\/04\/image-3.png 1039w\" sizes=\"auto, (max-width: 813px) 100vw, 813px\" \/><\/figure>\n\n\n\n<h4 class=\"wp-block-heading\">EMP table with two childs<\/h4>\n\n\n\n<p>The following sample illustrates the rendering of a master with two detail parts:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Direct reports<\/li>\n\n\n\n<li>Other people working in the same department<\/li>\n<\/ul>\n\n\n\n<p>cf emp template and \/emp\/handler<\/p>\n\n\n\n<figure class=\"wp-block-image size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"791\" src=\"https:\/\/gpmfactory.com\/wp-content\/uploads\/2024\/06\/image-3-1024x791.png\" alt=\"\" class=\"wp-image-5827\" style=\"width:636px;height:auto\" srcset=\"https:\/\/gpmfactory.com\/wp-content\/uploads\/2024\/06\/image-3-1024x791.png 1024w, https:\/\/gpmfactory.com\/wp-content\/uploads\/2024\/06\/image-3-300x232.png 300w, https:\/\/gpmfactory.com\/wp-content\/uploads\/2024\/06\/image-3-768x593.png 768w, https:\/\/gpmfactory.com\/wp-content\/uploads\/2024\/06\/image-3-1536x1186.png 1536w, https:\/\/gpmfactory.com\/wp-content\/uploads\/2024\/06\/image-3.png 1580w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<h4 class=\"wp-block-heading\">Simple lists<\/h4>\n\n\n\n<figure class=\"wp-block-image size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"545\" src=\"https:\/\/gpmfactory.com\/wp-content\/uploads\/2024\/05\/image-9-1024x545.png\" alt=\"\" class=\"wp-image-5790\" style=\"width:681px;height:auto\" srcset=\"https:\/\/gpmfactory.com\/wp-content\/uploads\/2024\/05\/image-9-1024x545.png 1024w, https:\/\/gpmfactory.com\/wp-content\/uploads\/2024\/05\/image-9-300x160.png 300w, https:\/\/gpmfactory.com\/wp-content\/uploads\/2024\/05\/image-9-768x409.png 768w, https:\/\/gpmfactory.com\/wp-content\/uploads\/2024\/05\/image-9.png 1436w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<figure class=\"wp-block-image size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"653\" src=\"https:\/\/gpmfactory.com\/wp-content\/uploads\/2024\/05\/image-10-1024x653.png\" alt=\"\" class=\"wp-image-5793\" style=\"width:657px;height:auto\" srcset=\"https:\/\/gpmfactory.com\/wp-content\/uploads\/2024\/05\/image-10-1024x653.png 1024w, https:\/\/gpmfactory.com\/wp-content\/uploads\/2024\/05\/image-10-300x191.png 300w, https:\/\/gpmfactory.com\/wp-content\/uploads\/2024\/05\/image-10-768x489.png 768w, https:\/\/gpmfactory.com\/wp-content\/uploads\/2024\/05\/image-10.png 1478w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n","protected":false},"excerpt":{"rendered":"<p>Objective Description This is a prototype I developed that prioritizes the use of Office Word both as a design tool and for merging with&#8230;<\/p>\n","protected":false},"author":1,"featured_media":5762,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"ppma_author":[150],"class_list":["post-5744","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-non-classe"],"authors":[{"term_id":150,"user_id":1,"is_guest":0,"slug":"admin8700","display_name":"Patrick","avatar_url":"https:\/\/secure.gravatar.com\/avatar\/209d5ed69b74d288390621ab4c1d3773?s=96&d=mm&r=g","0":null,"1":"","2":"","3":"","4":"","5":"","6":"","7":"","8":""}],"_links":{"self":[{"href":"https:\/\/gpmfactory.com\/index.php\/wp-json\/wp\/v2\/posts\/5744","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/gpmfactory.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/gpmfactory.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/gpmfactory.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/gpmfactory.com\/index.php\/wp-json\/wp\/v2\/comments?post=5744"}],"version-history":[{"count":122,"href":"https:\/\/gpmfactory.com\/index.php\/wp-json\/wp\/v2\/posts\/5744\/revisions"}],"predecessor-version":[{"id":5898,"href":"https:\/\/gpmfactory.com\/index.php\/wp-json\/wp\/v2\/posts\/5744\/revisions\/5898"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/gpmfactory.com\/index.php\/wp-json\/wp\/v2\/media\/5762"}],"wp:attachment":[{"href":"https:\/\/gpmfactory.com\/index.php\/wp-json\/wp\/v2\/media?parent=5744"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/gpmfactory.com\/index.php\/wp-json\/wp\/v2\/categories?post=5744"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/gpmfactory.com\/index.php\/wp-json\/wp\/v2\/tags?post=5744"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/gpmfactory.com\/index.php\/wp-json\/wp\/v2\/ppma_author?post=5744"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}