====== Integration Connector between Databases ======

===== Configuration =====
\\

The database connector is installed as a service, and for its proper functioning it is necessary to configure 2 files: \\
\\
<box 90% blue left| ** Xone ITF Advanced.exe.config **> 
This file is the first one that reads the executable, and it is used to indicate which one is the configuration file of main data, as well as the name.
\\

<code xml>
<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <appSettings>
    <add key="Application Name" value="XOne ITF Advanced" />
    <add key="Configuration Path" value="C:\Archivos de programa\XOne\XOne ITF Advanced\ITF Config.xml" />
    <!-- Activar Seguridad SSL en el envio de correo -->
    <add key="mailSettings.EnableSsl" value="true" />
    <!-- Tiempo estipulado en segundos para reenviar un mensaje que ya ha sido enviado -->
    <add key="mailSettings.RetrySend" value="300" />
  </appSettings>
  <system.net>
    <mailSettings>
      <smtp from="">
        <network host="" userName="" password="" />
      </smtp>
    </mailSettings>
  </system.net>
</configuration>
</code>

</box>
\\
\\

<box 90% blue left| ** ITF Config.xml **> 

File  which we program our interface with. It should be noted as an important point, that the same executable allows you to have several systems running at the same time, so that we can have several integrations that run against different database with a single executable. It is also commonly used to separate the upload with the download of data to be mobilized.
\\
<code xml>
<xml>
	<!-- IMPORTANTE MANTENER COMO ESTAN LAS MAYUSCULAS O MINUSCULAS PARA LOS SETTINGS DE LA APLICACION -->
        <!-- Descripción Atributos:
           name: nombre de la interface
           versión: versión de la interface para su mejor visión en el visor de sucesos
           disabled: con este parametro activaremos o desactivaremos la interface
                   
        -->
	<itf name="ITF 1" version="1.0" disabled="false">
                <!-- Para controlar el intervalo con el que se ejecuta la aplicación, tenemos dos opciones: Interval o cron-expression.-->
                <!-- Solo se debe de quedar activo uno de los dos nodos. Si estan los dos, predomina el Interval. -->
                <!-- El Interval da el tiempo en segundos. Tiene la limitación de 21600 segundos -->
		<item name="Interval" value="1"/>	
                <!-- El cron es mucho mas amplio y es el que se debe utilizar, ya que tienes un control total del tiempo. -->
                <!-- Mirar este enlace para documentarse: 
                 http://www.xoneisp.com/xonewiki/doku.php?id=f.-herramientas:f02.-xonemonitor:start#anexo_i_expresiones_cron              
                -->
                <item name="cron-expression" value="0 0/30 7-20 ? * *"/>
		<!-- ruta donde se encuentra el xml de configuracion de aplicacion-->
		<item name="XML Path" value="C:\Archivos de Programa\XOne\itf\itf1.xml"/>		
		<!-- Valor maximo de valores cacheados. Los valores que cachea son los filtros a buscar en el mapval -->
	        <item name="MaxCacheValues" value="15000"/>    
		<!-- Valor del MID con el que escribiremos en la base de datos -->
		<item name="MID" value="999"/>		
		<!-- Cuenta de correo a donde queremos que reporte los errores y advertencias que aparezcan -->
		<item name="Mail To" value="correo1@xone.es"/>
		<item name="Mail To 2" value="correo2@hotmail.com"/>
		<item name="Mail To 3" value="correo3@gmail.com"/>
                <!-- Creacion por defecto de la tabla interclient_sync_tables en caso de no existir en la base de datos, para control de cambios -->
		<item name="Inter Sync Create" value="CREATE TABLE interclient_sync_tables (ID int(11) NOT NULL Identity(1,1),   OBJECTNAME varchar(150) NULL,LASTMODIFIED datetime NULL, PRIMARY KEY (ID))"/>
  
                <!-- Parametros de conexion de tabla de eventos. 
                     Si queremos pasar el log a Base de datos en lugar de hacerlo en el visor de sucesos.
                     Habría que poner el nodo itflog en cada itf que queramos guardar log en base de datos.
                     En la siguiente línea, name especifica el nombre de la tabla de log.
                -->
		<itflog name="ITFLOG" provider="System.Data.OracleClient" connstring="Data Source=BASEDATOS;User id=USUARIOQUESEA;Password=CLAVEQUESEA"/>

		<!--  Estructura de la tabla ITFLOG:

				Nombre       Nulo     Tipo           
				------------ -------- -------------- 
				ID           NOT NULL NUMBER(11)     
				APPNAME               VARCHAR2(50)   
				APPVERSION            VARCHAR2(50)   
				EVENTDATE             VARCHAR2(50)   
				EVENTTYPE             NUMBER(4)      
				TRANSFORM             VARCHAR2(50)   
				EVENTMESSAGE          VARCHAR2(4000)
                
		-->
	</itf>

	<itf name="ITF 2" version="1.5" disabled="false">
		<item name="Interval" value="1"/>
		<item name="XML Path" value="C:\archivos de programa\XOne\itf\itf2.xml"/>
		<item name="MaxCacheValues" value="1000"/>
		<item name="MID" value="777"/>
	</itf>
	<!--
	...
	<itf>
	...
	</itf>
	...
	-->	
</xml>
</code>
</box>
\\


<WRAP center round help 70%>
You can check the different CHRON configurations, to set the date and time you want to make the different tasks: [[wiki:5.-miscelanea:5.8.-expresiones-cron:start|Aquí]].
</WRAP>

===== Interclient =====

\\
Configuration file where the integration itself will be carried out between the source system, the client's system, and the XOne system. \\

\\
<WRAP info>
The basic functioning of the interface is the following one:
  - In each transform there is a coll node with a source-table and dest-table attributes, which will indicate us the table or view of origin of the data and the table of destination of the same.
  - There is an  "**SQL**" attribute that defines us the query we are going to send over the source that is going to delimit us the data which we are going to try to transfer to the destination table. (It is advisable to limit this query so that if there are many records, those ones are transfered in groups of 1000 records for instance, by adding the LIMIT, TOP, ROWNUM clauses, or whatever corresponds to the DBMS we are using ).
  - In the fields that we are going to transfer, we must put a **key="true"** attribute in those fields that are going to define us a record as unique, which it could be a field that already have as unique key or it could be the combination of several fields that its conjunction make that the record may be located unequivocally. Besides with the  **key="true"** attribute in the different fields, there is an attribute to COLL level that enables to the machinery to keep the keys of the records that fall within the transformation. This attribute is: **saved-source="true"** and **saved-dest="true"** to keep the keys of the records of the source and destination respectively. These two attributes fulfill the value to the **##SOURCEKEYVALUES##** and **##DESTKEYVALUES##** macros.
  - <wrap hi>Once we have the key field or fields (The ones that have the  key="true" attribute defined), the interface looks in the destination to see if already there is a records with these values, in case it does not exist, an **INSERT** will be made, and if these values already exist in the destination, an  **UPDATE** will be made from those fields that change value in the transformation</wrap>.
  - Finally, by general rule, we will want to mark the source of the records that we have processed, by putting them with some mark to know that this records we already have them "synchronized" in the destination. For this, we make use of the "**after action**" node in which we will put the UPDATE over the data source to put these "marked" records as processed ones.
</WRAP>
\\
^  **Nodes**  ^^
|<**?xml version** >=**"1.0" encoding="ISO-8859-1"**?>| Node to put at the upper part of the file. It is useful to be able to use the ISO-8859-1 codification, and admits, among many other things, the accents. **OPTIONAL**.|
|<**xml**>| Main node, it is the father of the all nodes, and every file must have this node, in which everything else will go.  **MANDATORY**.|
|<**interface**>| Node child of the xml node, within this will go the rest of the options. **MANDATORY**.|
\\
====  APP Node ====

<box 100% blue left| ** APP **> 

**Node in which is define who are the source and destination databases to be able to make the interface. MANDATORY. This node, has several attributes, such as:**


^       Attributes      ^ Description ^
| NAME | "INTERFASE": application name |
| LOGOPER | "true": to enable the LOG, by showing everything the  <nowiki>CGInterclientXone</nowiki> does, in the Windows Events Visor. |
| SQLFIELDNAME | the application when transfers the data from a database toward another one, also, it writes in the replica queue. This table, has a SQL field, which the replica SQL are entered. So, that name of that field is a reserved word in some database systems such as the MYSQL 5.0. What we have to do is create the replica queue, and change the SQL name for any other, and then, this name is put in this attribute, in order the program knows the change there is.|
| TBL-QUEUE | if we want to fulfill or we want not the value of the TBL field in the  master_replica_queue table. It will take values **true** or **false** |
| SLASH | attribute that we can indicate not to duplicate the  “\” character. The program duplicates automatically, but there are some database systems that it is not necessary to duplicate them, by putting this attribute to **false**. Duplicate is because there are systems that this attribute is a reserved word, and to tell it that we do not want to do anything, we duplicate it, and then the system removes the duplicate. |
| ROWIDFIELDNAME | attribute to change the value of the ROWID mandatory field. This is usually used when the replica database is in ORACLE, since the ROWID is a reserved word or that system. Only for the Server database. |
| ROWIDFIELDNAMEDEST | attribute to change the value of the ROWID mandatory field. This is usually used when the replica database is in ORACLE, since the ROWID is a reserved word for that system. Only for the Devices database. |
| ORACLE-SESSION | modifying the session in Oracle in which among other options we can change date format with which we want to work in the ITF, it is executed over each provider connection <nowiki>OracleClient</nowiki>, by default ALTER SESSION SET NLS_DATE_FORMAT='DD/MM/YYYY HH24:MI:SS' |
\\


<box 90% green left| ******  Connection  ******  > 

** Node in which are the calls to the database with which the interface interacts. It consists of child nodes, with calls to the database. **

^  Nodes         ^ Description ^
|  **client**       | Node that has the call to the source database, the database to mobility which always will be the  database of the client.  |
|  **source**       | Node that has the call to the destination database, the XOne database.   |
|  **optional**     | It is a connection with an optional database  (auxiliary) being able to be the same that the one that exists in the client or source nodes, or it may be a different connection. It can be put all the optional nodes we need, it is not limited to 1. They are called by making reference to its name  (it is explained below in DATA-SOURCE, DATA-DEST). |
\\
\\

^  Provider   ^   Connstring   ^
|  MYSQL      | connstring="Persist Security Info=False;database=salesdemo;server=IPSERVIDORBD;user id=user;Password=pass" |
|  SQLSERVER  | connstring="Data Source=IPSERVIDORBD;Initial Catalog=beltranprod;Persist Security Info=True;User ID=user;Password=pass" |
|  ORACLE     | connstring="Data Source=CGS;Persist Security Info=True;User ID=user;Password=pass;Unicode=True" |
|  ODBC       | DSN=nombredsnsistema;uid=usuario;pwd=clave |
|  <nowiki>OLeDB</nowiki>       | provider=ej.[SQLOLEDB.1,MSDAORA...];data source=DATASOURCE;user id=USER;password=PWD.. |
\\
\\


Sample code of the APP node. Each of the connections that can be identified is specified.
<code xml>
<app name="ITF_PRUEBA" logoper="false" rowidfieldname="CGSROWID"  sqlfieldname="SQL" oracle-session="ALTER SESSION SET NLS_DATE_FORMAT='DD/MM/YYYY HH24:MI:SS'">
   <!--
     logoper - deja log de todas las operaciones que realiza la interface, por defecto es false
     rowidfieldname - nombre del campo ROWID, por defecto es ROWID pudiendo cambiarse para gestores como ORACLE
     rowidfieldnamedest - nombre que va a tomar el campo ROWID en el SQL del Queue, pudiendo ser diferente al rowidfieldname, por defecto es ROWID
     tbl-queue - si queremos rellenar o no el valor del campo TBL. Por defecto **true**
     sqlfieldname - nombre del campo en el que vamos a introducir el insert, update o delete ejecutados en el queue, siendo por defecto SQL
     oracle-session - modificar la sesión de oracle en la que entre otras opciones podemos cambiar el formato de fecha con el que queramos trabajar en la ITF, se ejecuta sobre cada conexión provider OracleClient, por defecto ALTER SESSION SET NLS_DATE_FORMAT='DD/MM/YYYY HH24:MI:SS'
   -->
   <connection>
	<!--
	provider: especificamos el provider de la conexión, interpretando SqlClient, OracleClient, MySQLClient, SQLServerCE, 
	OleDB y ODBC. Por defecto es ODBC.
	connstring: cadena de conexión de la base de datos
        -->
	<client name="NOMBRE1" provider="System.Data.PROVIDER" connstring="Data Source=ORACLESRC;Persist Security Info=False;User ID=CGSOFT;password=CGSOFT;Unicode=True"/>
	<source name="NOMBRE2" provider="System.Data.PROVIDER" connstring="Data Source=CGSOFTBD;Persist Security Info=False;User ID=CGSOFT_ITF;password=CGSOFT_ITF;Unicode=True"/>
	<optional name="NOMBRE3" provider="System.Data.PROVIDER" connstring="......."/>
	<!--
	Se pueden definir todas las conexiones optinal que se deseen (conexion auxiliar), se llaman seleccionando 
	su "name" en data-source o data-dest dependiento de si se usan como source o dest en la transformación, 
	dentro del nodo coll
	-->
	<!-- Conexiones de ejemplo 
	**ODBC**:  dsn=nombredsnsistema;uid=usuario;pwd=clave
	**OleDB**: provider=ej.[SQLOLEDB.1,MSDAORA...];data source=DATASOURCE;user id=USER;password=PWD..
	**System.Data.SqlClient**: User ID=USER;Password=PWD;Initial Catalog=BD;Data Source=DATASOURCE...
	**MySql.Data.MySqlClient**:Database=[NOMBRE BD];Data Source=[SERVIDOR{localhost...}];User Id=[USER];Password=[PASSWORD]
	**System.Data.OracleClient**: Data Source=[datasource{tns service name}];Persist Security Info=False;User ID=[user];Password=[password];Unicode=True 
	-->
    </connection>
</app>
</code>
</box> 




</box> 
\\
\\
\\
====  TRANSFORMS Node ====

<box 100% blue left| ** TRANSFORMS **> 

Node that defines the address of sending the data, by deciding which connection is the source and which one is the destination, being able to vary between client-to-source or source-to-client.
\\
^   ADDRESS TYPE   ^   DESCRIPTION   ^
|  **client-to-source**  | Definition of the source table  the connection of the  **client** node, and as destination the  **source**connection. This connection is the one used to replicate the information to the devices, since the SQL sentences are  inserted in the **master_replica_queue** table. |
|  **source-to-client**  | Definition of the source table the connection of the  **source** node and as destination the **client** connection. With this option nothing is written in any additional table, only in the one that is defined to which table  the destination data go.  |
\\
</box> 
\\
\\
\\

Once it has been decided in which order the data are transferred, each of the transform nodes begins to be defined, which are the ones that carry out the transfer of data from one table to another.\\



====  TRANSFORM Node====

<box 100% blue left| ** TRANSFORM **> 

Node that defines the data transfer to be made. Each transform is for one or several tables dependent on each other, and only have an attribute, **name**, which must be unique for the whole  xml configuration file of the interface. If there is any error, the log will show the name of the transform where there is in and where the possible error did occur.
\\ 
\\
<note important>
It is important to remark that if there is a fail when executing the active **transform** , it will skip to the next **transform** node, in such way that the tables dependent on each other, must go in the same  **transform** node consecutively. So, the not dependent tables or in which ones an error is produced in the previous one we can continue with it, they must go in different **transform** nodes.
</note>
\\
\\
Transformation with a single coll node, in such way that the table does not depend on other tables. 
<code xml>
<transform name="tabla_sin_dependencias">
       <coll dest-table="GEN_DETTARIFA" source-table="
	SELECT a.*,trim(a.TARIFA) || replace(a.SECUENCIA,' ','-') AS CODSEC,b.ID
	FROM TARIFAS a,CARGA_PARCIAL b,ARTICULOS a2
	WHERE b.ARTICULO=a2.ARTICULO AND a.ARTICULO=b.ARTICULO AND a.TARIFA=b.TARIFA AND a.SECUENCIA=b.SECUENCIA
	AND b.TABLA='TARIFAS' AND b.ESTADO='B'">
	<prop source-name="ID" dest-name="IDTARIFA" key="true" saved-source="true" type="N" saved-dest="false">
		</prop>
	<prop source-name="CODSEC" dest-name="IDTARIFA" key="true" type="N" saved-dest="true">
	    <oper type="mapval" source="gen_tarifas" targetfld="ID">
		<prop mapfld="CODSEC" mapvalue="##SELF##"/>
    	    </oper>
	</prop>
	<prop source-name="SECUENCIA" dest-name="SECUENCIA" key="true" type="T" saved-dest="true">
		<oper type="copy"/>
	</prop>
	<delete>
	        <action name="delete-conditional" delete-dest-table="gen_DetTarifa" use-saved-dest="true"/>
	</delete>		
	<after-action>
		<action name="execute-query">
			<param name="source" value="delete from carga_parcial	WHERE ##SOURCEKEYVALUES##"/>
		</action>
	</after-action>
	</coll>
</transform>
</code>
\\
Transform with two coll nodes, that have been inserted in the same transform, because they are tables dependent on each other, and if they do not transfer the data of the first one, the second one must not be inserted. 
<code xml>
<transform name="gen_dettarifacgs_cparcial"> 
	<coll dest-table="GEN_DETTARIFA" source-table="
	SELECT a.*,trim(a.TARIFA) || replace(a.SECUENCIA,' ','-') AS CODSEC
	FROM TARIFAS_CGS a,ARTICULOS_CGS a2
	WHERE a.ARTICULO=a2.ARTICULO AND a.CG_ESTADO IN(7)">
	<prop source-name="ID" dest-name="IDTARIFA" key="true" saved-source="true" type="N" saved-dest="false">
		</prop>
	<prop source-name="CODSEC" dest-name="IDTARIFA" key="true" type="N">
		<oper type="mapval" source="gen_tarifas" targetfld="ID">
			<prop mapfld="CODSEC" mapvalue="##SELF##"/>
		</oper>
	</prop>
	<prop dest-name="UTARREM" type="N">
			<oper type="setval" value="9"/>
	</prop>
	<prop source-name="U_TAR_REM" dest-name="UTARREM" type="N">
			<oper type="copy"/>
	</prop>
	<after-action>
		<action name="execute-query">
			<param name="source" value="UPDATE TARIFAS_CGS SET CG_ESTADO=5 WHERE ##SOURCEKEYVALUES##"/>
		</action>
	</after-action>
	</coll>
	<coll dest-table="GEN_DETTARIFA" source-table="
	SELECT a.*,trim(a.TARIFA) || replace(a.SECUENCIA,' ','-') AS CODSEC,b.ID
	FROM TARIFAS a,CARGA_PARCIAL b,ARTICULOS a2
	WHERE b.ARTICULO=a2.ARTICULO AND a.ARTICULO=b.ARTICULO AND a.TARIFA=b.TARIFA AND a.SECUENCIA=b.SECUENCIA
	AND b.TABLA='TARIFAS' AND b.ESTADO='B'">
	<prop source-name="ID" dest-name="IDTARIFA" key="true" saved-source="true" type="N" saved-dest="false">
	</prop>
	<prop source-name="CODSEC" dest-name="IDTARIFA" key="true" type="N" saved-dest="true">
		<oper type="mapval" source="gen_tarifas" targetfld="ID">
			<prop mapfld="CODSEC" mapvalue="##SELF##"/>
		</oper>
	</prop>
	<prop source-name="SECUENCIA" dest-name="SECUENCIA" key="true" type="T" saved-dest="true">
		<oper type="copy"/>
	</prop>
	<after-action>
		<action name="execute-query">
			<param name="source" value="UPDATE TARIFAS_CGS SET CG_ESTADO=5 WHERE ##SOURCEKEYVALUES##"/>
		</action>
	</after-action>
	</coll>
</transform>
</code>

</box> 
\\
\\
\\

====  COLL  Node====

Node in which the source and destination tables of the data to insert are taken.\\ 
\\
The attributes of the node are:
\\
^  ATTRIBUTE  ^   DESCRIPTION   ^
|  dest-table  | Name of the destination table.   |
|  source-table  | Selection from a query of the data to integrate from the source into the destination.   |
|  rows-limit  | **DEPRECATED**. If we want to limit the number of records processed, we should  have to rely on the functions of the DBMS we are using (**TOP**, **LIMIT**, **ROWNUM**, etc).  |
|  norowid  | Its value by default is false. With //true// value it would not fill the ROWID field of the DEST-TABLE table.   |
|  type-not-allowed  |  It may take values 1 yand3, in such way that if the value is 1, it will not execute inserts and with value 3 it will not execute  updates. |
|  extrainfo  | By default//true//. With false value it would not let any record in the  master_replica_queue table. |
|  data-source  | Attribute that admits the name of any connection <optional> over we want to get the source of the coll data, if we want it to be different from the one that was configured by default in the client node or source node in interface / app / connection.  |
|  data-dest  | Attribute that admits the name of any connection  <optional> over we want to get the destination of the coll data, if we want it to be different from the one that was configured by default in the client node or source node in interface/app/connection.   |
|  always-insert  | Attribute that if we are going to make an update by change in the data transfered, really at the replica queue: master_replica_queue, it puts it like an   //insert//.  |
|  repeat-until-end  | With true value  (false by default) it will repeat that transformation until the query stops returning results.  |
|  continue-on-error  | With true value (false by default) it will continue with the rest of transformations, although the current one is giving errors.  |
\\
\\

Within the coll node, each and every one of the fields that have to be transfered from a table into another one. For this, we have to put different **prop** nodes, which will be the ones that have the source field name and the destination  field name.\\
Later, we will put several attributes defining the type or which type of action we need to do. 
 
\\

====  PROP Node ====

Node where is defined which field will be transfered and which action will be made in the destination table. 
\\
The attributes of this node are: 
\\
^  ATTRIBUTE  ^   DESCRIPTION   ^
|  source-name  | Name of the source field.  |
|  dest-name  | Name of the destination field.  |
|  type  | Attribute to tell it the type of data of the field which is going to be transfered. The options are: **T**(type text field). **Nx**:(numeric field. The x character means that it can be put the number of decimals it has. It will vary between not putting anything, N, or putting the number of decimals that are going to be transfered,  N2, N3, N4, etc.) **D:**(type date field.) **X:**(To pass data that are going to be keys.) |
|  key  | To tell it that the field is primary key or not in the destination table. Searches for this field are performed if the record exists in the destination table. The key can of course be multiple, so there can be more than one field that is the primary key of the record. ** This field is mandatory that at least one of the "prop" that has the "coll", have this attribute to true **. |
|  saved-source  | Attribute that is going to save the value of the  **source-name** field in a macro, to later perform operations over the fields that meet the condition that the macro saves. The macro is **##SOURCEKEYVALUES##**.  |
|  saved-dest  | Attribute that saves the value of the **dest-name** field in a macro, to later operate with the records that meet the condition. The macro **##DESTKEYVALUES##**.  |
|  not-replicate  | Attribute to indicate that if the operation of this field is inserted or it is not in the operations of the master_replica_queue. If it is **true** this value is not inserted, if it does not exist or it is **false**, it will be inserted at the replica operations.  |
|  always-insert| If the attribute is  **true** we are forcing it to insert an update in the “master_replica_queue” replica queue, although its value does not change.  |
\\
\\
Once defined which field is the origin and the destination field, with its individual characteristics, we have to define the action to be performed, which will be defined within a node ** oper ** within the  ** prop ** node.
\\
\\
====  OPER Node ====

Types of operations that can be performed with the fields that we want to transfer from one table to another, in the ** prop ** node.\\
\\
===  SETVAL Action ===
\\
In this operation, what is done is to put a given value in the destination field. Therefore, the  ** prop ** node, only has the type of the field, ** type **, and the name of the field that is the destination, ** dest-name **.
\\
In the example, it can be seen that in the IDPADRE field of the destination table, which is of numeric type, N, the value 1 will be saved.
\\
<code xml>
<prop dest-name="IDPADRE" type="N">
	<oper type="setval" value="1"/>
</prop>
</code>


In this other example we put the value "xone" in the destination field NAME, which is of type text.\\
\\
<code xml>
<prop dest-name="NOMBRE" type="T">
	<oper type="setval" value="xone"/>
</prop>
</code>

===  COPY Action ===
	
Operation to copy the value of a source field into a destination field.\\


Example where it is seen how the value of the "delegation" field will be copied to the "LABEL" field. This field is text type. You can also see how it is the primary key, so to find if there is such a record in the destination, you will search for the value of this field. It is also being told to save the value of the source in the  ** ## SOURCEKEYVALUES ## ** macro, and to save the value of the destination in the  ** ## DESTKEYVALUES ## ** macro.\\

<code xml>
<prop source-name="delegación" dest-name="ETIQUETA" key="true" type="T" saved-source="true" saved-dest="true">
		<oper type="copy"/>
</prop>
</code>

Node that transfers the value of the "PASSWORD_E" field to the PWD field. As we can see, it has type X, so it is being indicated that it is a password field, and its value keeps it encrypted. Encryption is supported by the XOne Platform, so that users can subsequently enter the application.
\\

<code xml>
<prop source-name="PASSWORD_E" dest-name="PWD" type="X">
           <oper type="copy"/>
</prop>	
</code>

	
Node that transfers the value of the "DESCPTE" field to the "INCIDENCE" field. It is of  text type. We also see that it has the attribute "always-update", so we are forcing it into the "master_replica_queue" replication queue, to insert an update even if its value does not change.\\

<code xml>
<prop source-name="DESCPTE" dest-name="INCIDENCIA" key="false" type="T" always-update="true">
	<oper type="copy"/>
</prop>	
</code>

Node that transfers the value of the "FECCARGA" field to the "DATE" field. The field is a date type. You can also see a new node, ** format **, node that is used within the  ** prop ** nodes, and that is used to format the value in the destination with the characteristics that are told in the value attribute. In the example, when you put it, we are telling it that in the destination the date has to be inserted in day / month / year mode.\\


<code xml>
<prop source-name="FECCARGA" dest-name="FECHA" type="D">				
  <oper type="copy"/>
  <format name="dest-name" value="dd/mm/yyyy"/>
</prop>	
</code>


Node that transfers the data from the "CODAVISO" field to the "TIPOPARTE" field, and we see that it is of the text type. We can also see that there is the ** format ** node. Here we can see that it has a new attribute, ** trim **. This attribute does or does not eliminate the blank spaces that come from the origin in the destination. If the attribute is not defined is "true", that is, it deletes the blank spaces. In order it works, it is necessary to put the value "NULL" in the "value" attribute.
\\

<code xml>
<prop source-name="CODAVISO" dest-name="TIPOPARTE" type="T">
	<oper type="copy"/>
	<format value="NULL" trim="false"/>
</prop>	
</code>


Node that transfers the data from the "PRETAR" field to the "PRICE" field. It is numeric, and specifically with 5 decimals. In order for the copy to be done well, the ** format ** node has been set, placing exactly the number of decimals that will be put to it.\\

<code xml>
<prop source-name="PRETAR" dest-name="PRECIO" type="N5">
	<oper type="copy"/>
	<format name="dest-name" value="0.00000"/>
</prop>
</code>


===  MAPVAL  Action===  
\\

Operation to transfer data from fields related to values ​​that are in other tables. What would be in the mappings fields of type IDEMPRESA or IDUSUARIO. For this, what is done is to take the value of the source field, and look for it in a table, when it finds the value, it puts the value of a field that we say of that table in the destination field of the beginning.

In the example that is seen, the value of the "delegation" field is taken, and compared with the "LABEL" field, which is put in the ** mapfld ** attribute, which exists in the "Gen_Business" table, defined in the ** source ** attribute. If there is a record that has that value, there only has to be one, since the field by which the search is made in the "Gen_Business" table, must be the primary key of it, it takes the value of the field that is defined in the attribute ** targetfld **, "ID" for this case, and put it in the field ** dest-name **, field "IDEMPRESA".

In SQL: IDEMPRESA=SELECT ID FROM Gen_Empresa WHERE ETIQUETA=delegacion

<code xml>
<prop source-name="delegacion" dest-name="IDEMPRESA" type=”N”>
	<oper type="mapval" source="Gen_Empresa" targetfld="ID">
		<prop mapfld="ETIQUETA" mapvalue="##SELF##"/>
	</oper>
</prop>	
</code>

In this case, several props can be used within the same operation, which is for those cases in which the first key of the record we are looking for is defined by several fields, in this case "COMPANY_ID" and "CAMPO2".
\\
Example:\\
\\

<code xml>
<oper type="mapval" source="gen_usuarios" targetfld="ID">
     <prop mapfld="COMPANY_ID" mapvalue="##SELF##"/>
     <prop mapfld="CAMPO2" mapvalue="##FLD_PROPIEDAD##"/>
</oper>  
</code>


===  MAIL Action === 


If we want to send a certain email with fields rescued in the coll query, we will use the **type="mail"** oper.\\
For the other side, in the **mailfield** attribute we specify which type of value is going to be transfered, by being able to take the values: **To**, **Subject**, **Message** and **File**. \\

In the end, there is the  ** value ** attribute, which is the one that transfers the value chosen in the SQL.\\
\\
The interface will try to send an email as long as the ** oper ** with value ** To ** is defined.
\\
<code xml>
<prop source-name="DESTINO" type="T">
	<oper type="mail" mailfield="To" value="##SELF##"/>
</prop>
<prop source-name="ASUNTO" type="T">
	<oper type="mail" mailfield="Subject" value="##SELF##"/>
</prop>
<prop source-name="MENSAJE" type="T">
	<oper type="mail" mailfield="Message" value="##SELF##"/>
</prop>
<prop source-name="FICHERO" type="T">
	<oper type="mail" mailfield="File" value="##SELF##"/>
</prop>
</code>




====  AFTER-ACTION Node====


Node that is executed when we finish performing the data transfer from one table to another.\\


===  EXECUTE-QUERY Action ===

It executes a SQL. This is quite useful to put a field in the source table with a value, to indicate that this value it was already transfered to the destination table. \\

At the example, we can see that it has two **param** nodes, so we are telling it which  SQL is going to be executed, in the **value** attribute. Also, in which database is put  "dest" to the **name** attibute, that is in the destination database, and "source", that is the source database.

Besides, we can see that it uses the  **##SOURCEKEYVALUES##** and **##DESTKEYVALUES##** macros, that have been filled while the data are being transfered from a database into another one. \\
\\

<code xml>
<action name="execute-query">
<param name="source" value="Update Gen_DetDocumento SET ACTUALIZADO=1 WHERE ##SOURCEKEYVALUES##"/>
<param name="dest" value="UPDATE Gen_Documentos  SET ACTUALIZADO=1  WHERE ID IN (SELECT IDDOCUMENTO FROM GEN_DETDOCUMENTO WHERE ##DESTKEYVALUES##)"/>	
</action>
</code>

====  BEFORE-ACTION Node====
		
Node that is executed before starting the data transfer. You can use all the operations that exist in the ** after-action ** node.\\
\\

====  DELETE Node====

Node to perform an erasure of the indicated data. We can delete both the origin and destination. \\

<WRAP center round important 80%>
At the examples we see below, we only see the  “delete” node, but we have to keep in mind that we must do a  “coll” node as if we were to transfer data from a table into another one, so it must have as minimum a  “prop” node which has the  “key” attribute equal to “true”.\\
</WRAP>
\\
There are several types of "delete" operations to be made, such as:\\
\\
^ DELETE Node Actions  ^ DESCRIPTION ^
| **delete-conditional**  | This is to make the data deletion which coincides with the data searched in the source, from the field or fields which coincide with the primary key we have defined.   |
| **delete-sql**  | This operation is useful to make the deletion both in the client and in the XOne database, so that, besides clearing the normal table, it inserts the deletion at the replica queue.  |
| **delete-fichero**  | Operation useful to make the deletion of a file. |
\\
===DELETE-CONDITIONAL===
\\
This is to make the data deletion that coincide with the data searched at the source, from the field or fields which coincide with the primary key we have defined.\\
\\
**Example of use**
\\
<code xml>
<delete>
	/* En el ejemplo se realiza el borrado en la tabla “gen_sat” y "gen_usuarios, de la base de datos de destino, 
	   por utilizar el atributo “delete-dest-table”, de aquellos datos que coinciden con la macro ##DESTKEYVALUES##. */

	<action name="delete-conditional" delete-dest-table="gen_sat" use-saved-dest="true"/>
	<action name="delete-conditional" delete-dest-table="gen_usuarios" use-saved-dest="true"/>


	/* Con este ejemplo se realiza el borrado en la base de datos origen. 
	   Para indicar una tabla de origen se utiliza el atributo “delete-source-table”, 
	   con este atributo se utiliza la macro ##SOURCEKEYVALUES##. */

	<action name="delete-conditional" delete-source-table="EVUSUA" use-saved-source="true"/>
</delete>
</code>
\\
===DELETE-SQL===
\\
This operation is useful to make the deletion both in the client and in the XOne database, so that besides clearing the normal table, it inserts the deletion in the replica queue.\\


\\
^   Attributes  ^^
|**WHERE**| It is the deletion condition. The macros ##SOURCEKEYVALUES## and ##DESTKEYVALUES## can be used.|
|**DELETE-DEST-TABLE**| Attribute to indicate in which table we want to make the deletion. If this attribute is not put, we take the value there is in attribute of the  “coll” node, “dest”. It makes the deletion of the records that have the ROWID that meet the condition put in the  “where” attribute.|
|**SELECT-DEST-TABLE**| Attribute to make the search of the records we want to delete. It may be any table, but we have to keep in mind, that the search of the values is made in that table, by taking the ROWID of the records that meet the  “where” attribute, and from that ROWID the deletion is made. If nothing is put, the value there is in the attribute of the "coll" node, "dest" is taken.  |
|**ALIAS-DEST-TABLE**| Alias of the table from the search is made, and later the deletion. |
|**SOURCE-TABLE**| To indicate from which source table we want to make the deletion. |
|**DELETE-SOURCE**| If this attribute is declared, and besides the “source-table” attribute is declared, it makes the deletion in the source table also, with the condition put in the “where” attribute.|
\\
\\
**Example of use:**\\
\\
<code xml>
<delete>
	/* 1.- Se realiza la búsqueda de en la base de datos de destino, en la tabla “gen_colla”(select-dest-table) de los ROWID 
	   que cumplen la condición del “where”(where), con el alias “c”(alias-dest-table). 

	   SELECT c.ROWID FROM Gen_Colla WHERE ESTADO=1

	   2.- Si hay valores con esa condición, los borra de la tabla “gen_colla”( delete-dest-table).
	   3.- Si se encontró algún registro con el punto 1, existe el atributo delete-source=”true”, el atributo “source-table” 
	       con un nombre de tabla y hay una condición, se realiza el borrado en la tabla “Gen_USUARIOS”( source-table), con la condición
 	       que hay en el atributo “where”. */

	<action name="delete-sql" source-table="Gen_USUARIOS" delete-source="true" where="ESTADO=1" delete-dest-table="Gen_Colla" 
	 select-dest-table="Gen_Colla" alias-dest-table="c"/>
</delete>			
</code>
\\
===DELETE-FILE===
\\
Operation useful to make the deletion of a file. \\
\\
**Example of use:**\\
\\
<code xml>
<delete>
	/* Realiza el borrado del fichero que existe en el atributo “PATH”, con lugar y nombre del mismo. */

	<action name="delete-fichero" path="C:\Archivos de programa\CGSoft\CGInterclient\Captura\Ficheros\ESPPDA.TXT"/>
</delete>
</code>



====  DATA-CONDITION Node ====

\\
For changes control.\\
\\
For this it will be put within the **nodo client**, with **type="##LASTMODIFIED##"**, in which it will enter when finishing the transformation in the  interclient_sync_tables  table, the values defined in the  data-condition, where  **##LASTMODIFIEDDATETIME##** is the date and the time when the transformation starts.\\ 
\\
<code xml>
<data-condition>
	<client type="##LASTMODIFIED##">
		<param name="FECHA" value="##LASTMODIFIEDDATETIME##">
			<format value="MM-dd-yyyy"/>
		</param>
		<param name="HORAINI" value="##LASTMODIFIEDDATETIME##">
			<format value="HH:mm:ss"/>
		</param>
		<param name="HORAFIN" value="##NOW##">
			<format value="HH:mm:ss"/>
		</param>
	</client>
</data-condition>
</code>
\\
Within the client node, the "param" node  will be put, which has the attributes: \\
\\
^  **PARAM** Node Attributes  ^^
|**NAME**| destination field of the  interclient_sync_tables.|
|**VALUE**| value that the field will take.|

\\
 ==== MACRO Node====
\\

Macros as auxiliary values can be used, both to a  **source-table** level within the **coll** node, and to performance an action. \\
\\


Within the **source-table** attribute, to a  **coll** level.\\
\\

<code xml>
<coll name="COLL_PASAR" source-table="SELECT t.* FROM Tabla1 t WHERE t.CAMPO='Valor1' and t.FECHA>='##MACRO##'" ...
</code>
\\
Here the "macro" node is shown, which  will be within the "coll" node. \\ 
\\
<code xml>
<macro name="##MACRO##" source-table="XONEPIMAUX" sql="SELECT TIMESTAMP AS X FROM INTERCLIENT_SYNC_TABLES WHERE OBJECTNAME='gen_contactos'" field="X" type="T">
	<format value="yyyy-MM-dd HH:mm:ss"/>
</macro>
</code>
	
The macro node attributes are:\\
\\


^       Attributes      ^ Description ^
| NAME | Name used to call to the macro that we will create.  |
| SOURCE-TABLE | Connection over which we are going to call to the macro, being mandatory to be about an **optional** connection. |
| SQL | Query to choose the values  which we are going to replace the macro with. It will take the first record returned, in case more than 1 records will be returned. In case of returning more than 1 field, the first field obtained, as long as the field attribute do not specify it.  |
| FIELD | Query field that we are going to return, by default it will be first one that exists in case not being FIELD. |
| TYPE | Attribute which returns us the type of data, being possible the same types that exist in the props nodes which will be detailed below. |


\\
===== Example Code =====
\\
It is an example code about how an interclient.xml file will be.\\
\\
<code xml>
<xml>
	<interface>
		<app name="ITF_PRUEBA" logoper="true" rowidfieldname="CGSROWID"  sqlfieldname="SQL" oracle-session="ALTER SESSION SET NLS_DATE_FORMAT='DD/MM/YYYY HH24:MI:SS'">
			<connection>
				<client name="NOMBRE1" provider="System.Data.PROVIDER" connstring="Data Source=ORACLESRC;Persist Security Info=False;User ID=CGSOFT;password=CGSOFT;Unicode=True"/>
				<source name="NOMBRE2" provider="System.Data.PROVIDER" connstring="Data Source=CGSOFTBD;Persist Security Info=False;User ID=CGSOFT_ITF;password=CGSOFT_ITF;Unicode=True"/>
				<optional name="NOMBRE3" provider="System.Data.PROVIDER" connstring="......."/>				
			</connection>
		</app>
		<transforms type="client-to-source">
			<transform name="gen_usuarios_cp">
				<coll dest-table="GEN_USUARIOS"
				source-table="SELECT a.*, 1 AS IDEMPRESA,trim(TARIFA) || '-' AS CODSEC FROM DELEGADOS_CGS a
				WHERE a.CG_ESTADO IN(7)">
					<prop source-name="DELEGADO" dest-name="LOGIN" key="true" saved-source="true">
						<oper type="copy"/>
					</prop>					
					<prop source-name="NOMBRE" dest-name="NOMBRE" type="T">
						<oper type="copy"/>
					</prop>													
					<prop source-name="CODSEC" dest-name="IDTARIFA" type="T">
						<oper type="mapval" source="gen_tarifas" targetfld="ID">
							<prop mapfld="CODSEC" mapvalue="##SELF##"/>
						</oper>
					</prop> 
					<prop source-name="TARIFA_ESPECIALISTA_SN" dest-name="TARIFA_ESPECIALISTA_SN" type="N">
						<oper type="copy"/>
					</prop> 
					<prop  dest-name="TIPO" type="N">
						<oper type="setval" value="1"/>
					</prop>	
					<after-action>
						<action name="execute-query">
							<param name="source" value="UPDATE DELEGADOS_CGS SET CG_ESTADO=5 WHERE ##SOURCEKEYVALUES##"/>
						</action>
					</after-action>	
				</coll>
			</transform>
			<transform name="gen_dettarifacgs_cparcial"> 
				<coll dest-table="GEN_DETTARIFA" source-table="
				SELECT a.*,trim(a.TARIFA) || replace(a.SECUENCIA,' ','-') AS CODSEC
				FROM TARIFAS_CGS a,ARTICULOS_CGS a2
				WHERE a.ARTICULO=a2.ARTICULO AND a.CG_ESTADO IN(7)">
				<data-condition>
						<client type="##LASTMODIFIED##">
							<param name="FECHA" value="##LASTMODIFIEDDATETIME##">
								<format value="MM-dd-yyyy"/>
							</param>
						 	<param name="HORAINI" value="##LASTMODIFIEDDATETIME##">
							 	<format value="HH:mm:ss"/>
							</param>
							<param name="HORAFIN" value="##NOW##">
							 	<format value="HH:mm:ss"/>
							</param>
						</client>
					</data-condition>
					<prop source-name="ID" dest-name="IDTARIFA" key="true" saved-source="true" type="N" saved-dest="false">
					</prop>
					<prop source-name="CODSEC" dest-name="IDTARIFA" key="true" type="N">
						<oper type="mapval" source="gen_tarifas" targetfld="ID">
							<prop mapfld="CODSEC" mapvalue="##SELF##"/>
						</oper>
					</prop>
					<prop source-name="ARTICULO" dest-name="IDARTICULO" key="true" type="N">
						<oper type="mapval" source="gen_articulos" targetfld="ID">
							<prop mapfld="REFERENCIA1" mapvalue="##SELF##"/>
						</oper>
					</prop>
					<prop source-name="SECUENCIA" dest-name="SECUENCIA" key="true" type="T">
						<oper type="copy"/>
					</prop>			
					<prop source-name="U_MAXIMA" dest-name="CANTMAX" type="N">
						<oper type="copy"/>
					</prop>
					<after-action>
						<action name="execute-query">
							<param name="source" value="UPDATE TARIFAS_CGS SET CG_ESTADO=5 WHERE ##SOURCEKEYVALUES##"/>
						</action>
					</after-action>
				</coll>
			</transform>
			<transform name="gen_dettarifacgs_cparcial"> 
				<coll dest-table="GEN_DETTARIFA" source-table="
				SELECT a.*,trim(a.TARIFA) || replace(a.SECUENCIA,' ','-') AS CODSEC
				FROM TARIFAS_CGS a,ARTICULOS_CGS a2
				WHERE a.ARTICULO=a2.ARTICULO AND a.CG_ESTADO IN(7)">
				<prop source-name="ID" dest-name="IDTARIFA" key="true" saved-source="true" type="N" saved-dest="false">
					</prop>
				<prop source-name="CODSEC" dest-name="IDTARIFA" key="true" type="N">
					<oper type="mapval" source="gen_tarifas" targetfld="ID">
						<prop mapfld="CODSEC" mapvalue="##SELF##"/>
					</oper>
				</prop>
				<prop dest-name="UTARREM" type="N">
						<oper type="setval" value="9"/>
				</prop>
				<prop source-name="U_TAR_REM" dest-name="UTARREM" type="N">
						<oper type="copy"/>
				</prop>
				<after-action>
					<action name="execute-query">
						<param name="source" value="UPDATE TARIFAS_CGS SET CG_ESTADO=5 WHERE ##SOURCEKEYVALUES##"/>
					</action>
				</after-action>
				</coll>
				<coll dest-table="GEN_DETTARIFA" source-table="
				SELECT a.*,trim(a.TARIFA) || replace(a.SECUENCIA,' ','-') AS CODSEC,b.ID
				FROM TARIFAS a,CARGA_PARCIAL b,ARTICULOS a2
				WHERE b.ARTICULO=a2.ARTICULO AND a.ARTICULO=b.ARTICULO AND a.TARIFA=b.TARIFA AND a.SECUENCIA=b.SECUENCIA
				AND b.TABLA='TARIFAS' AND b.ESTADO='B'">
				<prop source-name="ID" dest-name="IDTARIFA" key="true" saved-source="true" type="N" saved-dest="false">
				</prop>
				<prop source-name="CODSEC" dest-name="IDTARIFA" key="true" type="N" saved-dest="true">
					<oper type="mapval" source="gen_tarifas" targetfld="ID">
						<prop mapfld="CODSEC" mapvalue="##SELF##"/>
					</oper>
				</prop>
				<prop source-name="SECUENCIA" dest-name="SECUENCIA" key="true" type="T" saved-dest="true">
					<oper type="copy"/>
				</prop>
				<after-action>
					<action name="execute-query">
						<param name="source" value="UPDATE TARIFAS_CGS SET CG_ESTADO=5 WHERE ##SOURCEKEYVALUES##"/>
					</action>
				</after-action>
				</coll>
			</transform>
		</transforms>
		<transforms type="source-to-client">
			<transform name="clientes_ent_cgs">
				<coll dest-table="CLIENTES_ENT_CGS" 
				source-table="SELECT c.*,
				SUBSTR(c.CP,1,5) AS CP,SYSDATE AS MAP_FMODIF,
				SUBSTR(replace(replace(c.CUENTABANC,'-',''),' ',''),1,20) AS MAP_CUENTABANC,u.LOGIN AS DELEGADO,t.CODIGO AS TARIFA,g.CODIGO AS GRUPO_COMPRAS,p.CODIGO AS POTENCIAL_MERCADO,
				NVL(c.FORMAPAGO,'_') AS MAP_FORMAPAGO
				FROM ((((Gen_Clientes_Mod c
				LEFT OUTER JOIN Gen_Usuarios u ON c.IDUSUARIO=u.ID)
				LEFT OUTER JOIN Gen_Tarifas t ON c.IDTARIFA=t.ID)
				LEFT OUTER JOIN Gen_GruposCompra g ON c.IDGRUPOCOMPRA=g.ID)
				LEFT OUTER JOIN Gen_PotencialMercado p ON c.IDPOTMERCADO=p.ID)
				WHERE (c.MODIFICADO=1 or c.MODIFICADO=3) AND u.LOGIN IS NOT NULL AND c.EXPEDIENTE IS NOT NULL AND c.EXPEDIENTE&lt;&gt;''
				">
					<prop source-name="EXPEDIENTE" dest-name="CLIENTE" type="N" key="true">
						<oper type="copy"/>
					</prop>
					<prop source-name="DELEGADO" dest-name="DELEGADO" key="true" type="T">
						<oper type="copy"/>
					</prop>		
					<prop source-name="TIPO" dest-name="TIPO_CLIENTE" type="N">
						<oper type="copy"/>
					</prop>					
					<prop source-name="ACCIONISTA" dest-name="ACCIONISTA" type="N">
						<oper type="copy"/>
					</prop>
					<prop source-name="MAP_FMODIF" dest-name="F_MODIF" key="true" type="T">
						<oper type="copy"/>
						<format name="dest-name" value="dd/MM/yyyy HH:mm:ss"/>   
					</prop>																																												
				</coll>
			</transform>
		</transforms>		
	</interface>
</xml>



</code>