awe-104 Data retrieval and manipulation

The retrieval of data using AWE can be done from different sources: enumerated, queries, queues, services (Java and C), etc. This helps the integration of client side with the bussiness logic hosted in the server.

Enumerated

AWE's enumerated are useful to manage lists of key-value pairs. They are controlled from Enumerated.xml file which can be found in project's "global" folder.

This file follows this structure:

<?xml version="1.0" encoding="UTF-8"?>
<enumerated xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation = "https://aweframework.gitlab.io/awe/docs/schemas/enumerated.xsd">

  <group id="[Group Id]">
    <option label="[Option label]"  value="[Option value]" />
    <option label="[Option label]"  value="[Option value]" />
    ...
  </group>
  ... 
</enumerated>

Creation of Enumerated.xml file

The Enumerated.xml file should be created in project's "global" folder.

This file must have a header that makes reference to the schema to be followed. You have to make sure that the path is right.

<?xml version="1.0" encoding="UTF-8"?>
<enumerated xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation = "https://aweframework.gitlab.io/awe/docs/schemas/enumerated.xsd">

....

</enumerated>

Our first enumerated

For this tutorial we will use a numeric enumerated as an example, like for instance, "YES-NO" values.

For this, we will add to the file's content the XML tag group with their corresponding options.

<?xml version="1.0" encoding="UTF-8"?>
<enumerated xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation = "https://aweframework.gitlab.io/awe/docs/schemas/enumerated.xsd">
    <!-- Enumerated YES (0) | NO (1) -->
    <group id="Es1Es0">
      <option label="ENUM_NO"  value="0" />
      <option label="ENUM_YES" value="1" />
    </group>
    ...
</enumerated>

Each of the option tags inserted will represent a different element of the enumerated.

The most usual case of use for enumerated is inside of select components.

<criteria label="PARAMETER_ACTIVE" component="select" id="CrtSta" initial-load="enum" target-action="Es1Es0" style="col-xs-6 col-sm-3 col-lg-2" optional="true" />

Enumerated examples

These are some examples of enumerated:

<enumerated xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation = "https://aweframework.gitlab.io/awe/docs/schemas/enumerated.xsd">
    <!-- Enumerated YES (0) | NO (1) -->
    <group id="Es1Es0">
      <option label="ENUM_NO"  value="0" />
      <option label="ENUM_YES" value="1" />
    </group>
      ...
    <!-- LANGUAGES -->
    <group id="LanUsr">
      <option label="ENUM_LAN_ES" value="ESP"/>
      <option label="ENUM_LAN_EN" value="ENG"/>
      <option label="ENUM_LAN_FR" value="FRA"/>
    </group>
    ...
</enumerated>

Variables

Variables in AWE are used to make easier data retrieval, allowing to pass values that we have on screen or available in the server (i.e. in a Java service). For instance, if we want to use the value of an onscreen criteria in a where clause, we should use a variable.

In the following lines we will see some examples that will clarify this concept.

DB Queries

Database queries are done from the Queries.xml file. In this file, SQL queries are created using the format given by AWE, and it is its duty to translate them. Like in the previous section, this file should be located in global folder.

Nota: The QUERY concept in AWE's environment makes reference to an action requested to the server where the client expects some data as an answer. Should not be mistaken with SQL QUERY.

Inside of SQL queries, there are several ways of retrieving data: field, computed and compound.

Retrieval with field is used to get fields present in a SQL query, or put in another way, column's values.

  <query id="CheckAdeudoId">
    <field id="Id" transform="NUMBER" pattern="###" />
    <field id="Id_File" />
    <table id="Adeudo" />
    <where>
      <and>
        <filter left-field="Id_File" condition="eq" right-variable="varIdFile" trim="true" />
      </and>
    </where>
    <variable type="STRING" id="varIdFile" name="idFile" />
  </query>

In this example, we have a variable with file's identifier in order to obtain it from the table using a WHERE clause. This field retrieved is string type but we trasnform it to a numeric format.

As you can see, we have different attributes that allow to perform several standart SQL operations. For instance, trim attribute.

select and select-multiple components

select and select-multiple components require special naming on query fields to be filled:

<criteria label="PARAMETER_SELECT" id="select" variable="select" component="select" 
          initial-load="query" target-action="FillSelect" optional="true"/>

The fields which are required are the following:

• value: The value which will be sent to the server as the selected option of this criterion.
• label: The text shown on each option

  <query id="FillSelect">
    <table id="MyTable"/>
    <field id="field1" alias="value" />
    <field id="field2" alias="label" />
    <where>
      <and>
        ...
      </and>
    </where>
    ...
  </query>

Important: If you fill a select type criterion with the initial-load attribute, you must check that the variables on the query doesn't refer to the current window (as it is being created on screen generation).

suggest and suggest-multiple components

suggest and suggest-multiple components require the same special naming as select type components, but they are not populated on page generation, as they search the values when user types any text.

<criteria label="PARAMETER_SUGGEST" id="suggest" variable="suggest" component="suggest" 
          server-action="data" target-action="FillSuggest"/>

A special variable called suggest is sent when user types some text, and it can be used in the query to retrieve the filtered data:

  <query id="FillSuggest">
    <table id="MyTable"/>
    <field id="field1" alias="value" />
    <field id="field2" alias="label" />
    <where>
      <and>
        <filter left-field="field2" condition="like" right-variable="suggest" ignorecase="true" />
      </and>
    </where>
    <variable id="suggest" type="STRINGB" name="suggest"/>
  </query>

DB Maintains

Database maintains are those actions that perform modifications on stored data, that is, in SQL, actions like INSERT, UPDATE and DELETE. Also, inside of a single maintain several of these operations can be done at one time.

Maintains are created inside of Maintain.xml file and they are found in global folder, like we have seen previously.

Inside if the file, maintains should be defined with target tag.

In this INSERT example, a row is inserted into AwePro table. Field IdePro is filled with AWE's sequence ProKey.

  <target name="ProNew">
    <insert audit="HISAwePro">
      <table id="AwePro" />
      <field id="IdePro" sequence="ProKey" />
      <field id="Acr" variable="Acr" />
      <field id="Nam" variable="Nam" />
      <field id="IdeThm" variable="IdeThm" />
      <field id="ScrIni" variable="ScrIni" />
      <field id="Res" variable="Res" />
      <field id="Act" variable="Act" />
      <variable id="Acr" type="STRINGN" name="Acr" />
      <variable id="Nam" type="STRINGN" name="Nam" />
      <variable id="IdeThm" type="INTEGER" name="IdeThm" />
      <variable id="ScrIni" type="STRING" name="ScrIni" />
      <variable id="Res" type="STRING" name="Res" />
      <variable id="Act" type="INTEGER" name="Act" />
    </insert>
  </target>

Here you can see a modification (UPDATE) over the same table.

  <target name="ProUpd">
    <update audit="HISAwePro">
      <table id="AwePro" />
      <field id="IdePro" variable="IdePro" />
      <field id="Acr" variable="Acr" />
      <field id="Nam" variable="Nam" />
      <field id="IdeThm" variable="IdeThm" />
      <field id="ScrIni" variable="ScrIni" />
      <field id="Res" variable="Res" />
      <field id="Act" variable="Act" />
      <where>
        <and>
          <filter left-field="IdePro" condition="eq" right-variable="IdePro" />
        </and>
      </where>
      <variable id="IdePro" type="INTEGER" name="IdePro" />
      <variable id="Acr" type="STRINGN" name="Acr" />
      <variable id="Nam" type="STRINGN" name="Nam" />
      <variable id="IdeThm" type="INTEGER" name="IdeThm" />
      <variable id="ScrIni" type="STRING" name="ScrIni" />
      <variable id="Res" type="STRING" name="Res" />
      <variable id="Act" type="INTEGER" name="Act" />
    </update>
  </target>

In the following example with a DELETE action, we can se how one of them is performed first and before the second starts a confirmation (commit) of the first operation. If no explicit commit is declared, it will be done anyway but after all operations defined inside the target tag.

Along with this, you can see below an example using the multiple attribute. When set to true it means that IdePro variable is a list of values so there will be executed as many DELETE actions as elements are in that list.

  <target name="ProDel">
    <delete multiple="true" audit="HISAweModPro">
      <table id="AweModPro" />
      <field id="IdePro" variable="IdePro" audit="true" />
      <where>
        <and>
          <filter left-field="IdePro" condition="eq" right-variable="IdePro" />
        </and>
      </where>
      <variable id="IdePro" type="INTEGER" name="IdePro" />
    </delete>
    <commit/>
    <delete multiple="true" audit="HISAweScrRes">
      <table id="AweScrRes" />
      <field id="IdePro" variable="IdePro" audit="true" />
      <where>
        <and>
          <filter left-field="IdePro" condition="eq" right-variable="IdePro" />
        </and>
      </where>
      <variable id="IdePro" type="INTEGER" name="IdePro" />
    </delete>
  </target>

Finally, you can also use maintains of type multiple, that is, XML tag is multiple. This means that actions that can be performed are any of those 3 (INSERT, UPDATE, DELETE). The way of knowing what kind of operation we want to perform is via an implicit variable generated automatically that contains this information. The name of this variable is grid_name-RowTyp and the way to bind this maintain with a grid is with the grid attribute (in this example, GrdScrAccLst).

  <!-- Screen Access Multiple -->
  <target name="ScrAccUpd">
    <multiple audit="HISAweScrRes" grid="GrdScrAccLst">
      <table id="AweScrRes" />
      <field id="IdeAweScrRes" variable="IdeAweScrRes" sequence="ScrResKey" key="true" />
      <field id="IdePro" variable="IdePro" />
      <field id="IdeOpe" variable="IdeOpe" />
      <field id="IdeMod" variable="IdeMod" />
      <field id="Opt" variable="Opt" />
      <field id="AccMod" variable="AccMod" />
      <field id="Act" variable="Act" />
      <variable id="IdeAweScrRes" type="INTEGER" name="IdeAweScrRes" />
      <variable id="IdeOpe" type="INTEGER" name="IdeOpe"/>
      <variable id="IdePro" type="INTEGER" name="IdePro"/>
      <variable id="IdeMod" type="INTEGER" name="IdeMod"/>
      <variable id="Opt" type="STRING" name="Opt" />
      <variable id="AccMod" type="STRING" name="AccMod" />
      <variable id="Act" type="INTEGER" name="Act" />
    </multiple>
  </target>

Email delivery

AWE has the capability to send emails in a easy way. These email deliveries can be defined in Email.xml file.

  <email id="TransfersSNCEEmaRep">
    <from value="FromValue" label="FromValue" />
    <to label="RepEmaDst" value="RepEmaDst" />
    <subject label="RepTit" value="RepTit" />
    <body label="RepMsg" type="html" value="RepMsg"/>
    <body label="RepMsg" type="text" value="RepMsg"/>
    <variable type="STRING" id="FromValue" property="sch.report.email"/>
    <variable type="STRING" id="RepMsg" name="RepMsg"/>
    <variable type="STRING" id="RepTit" name="RepTit"/>
  </email>

The most common way of sending an email would be with an action inside of a maintain.

Example:

  <!-- Send reports by e-mail -->
  <target name="SndRep">
    <send-email id="SndRep" />
  </target>

Queue connection

Queue connections through AWE are defined in Queues.xml file inside of project's global folder.

The main two parts of the definition of a queue are the definition of the request and response messages.

Here we can see some examples:

<queues xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="https://aweframework.gitlab.io/awe/docs/schemas/queues.xsd">
  <!-- Queue retreive sync test with wrappers -->
  <queue id="SynQueWrpTxt">
    <request-message destination="AweReq" type="TEXT" selector="wrapper">
      <message-wrapper type="XML" classname="com.almis.awe.test.wrappers.Casa" />
    </request-message>
    <response-message destination="AweRes" type="TEXT">
      <message-wrapper type="XML" classname="com.almis.awe.test.wrappers.Casa" />
    </response-message>
  </queue>

  <!-- Queue retreive sync test (parameter list) -->
  <queue id="TstGrdQue">
    <request-message destination="AweReq" type="MAP" selector="grid">
    </request-message>
    <response-message destination="AweRes" type="MAP">
      <message-parameter id="OutFld1" type="STRING" name="MapKey1" list="true" />
      <message-parameter id="OutFld2" type="STRING" name="MapKey2" list="true" />
      <message-parameter id="OutFld3" type="STRING" name="MapKey3" list="true" />
      <message-parameter id="OutFld4" type="STRING" name="MapKey4" list="true" />
    </response-message>
  </queue>
  ...
</queues>

Services

Services defined in AWE, inside of Services.xml file, can be invoked from a query (Queries.xml file) or from maintains (Maintain.xml file).

From queries:

  <query id="GetFechasCancelacionesSup" service="GetFilteredDates">
    <field id="label" alias="label" transform="DATE" function="TRUNCDATE" />
    <field id="value" alias="value" transform="DATE" function="TRUNCDATE" />
    <variable id="Dias" type="INTEGER" name="Dias" value="10" />
  </query>

From maintains:

  <target name="checkYearHolidays">
    <serve service="checkYearHolidays"/>
  </target>