Diferencias

Muestra las diferencias entre dos versiones de la página.

--- mav:doc:documentacion_integracion_kilotou [2026/07/30 10:39] – juanma
+++ mav:doc:documentacion_integracion_kilotou [2026/10/25 21:19] (actual) – juanma
@@ Línea 14: / Línea 14: @@
 The data can be reached in two ways:
-  * **SmartWebDoc** (web service): the procedures are called over HTTP with a POST request, as described below and used throughout the examples of this document. //SmartWebDoc can be installed by Smart Software Solutions.//
+  * **SmartWebDoc** (web service): the procedures are called over HTTP with a POST request, as described below and used throughout the examples of this document. **This service is already installed and running** at this installation. //SmartWebDoc is installed by Smart Software Solutions.// See the SmartWebDoc documentation: [[https://wiki.smartastur.com/doku.php?id=usu:smartweb:introduccion_a_smartwebdoc|Introducción a SmartWebDoc]].
-  * **ODBC**: alternatively, the same procedures can be queried directly through an ODBC connection to the Firebird database. The procedure and field names are identical, and the ''SELECT * FROM <procedure>(...)'' statements are the same; **only the transport and the response format change**. Over ODBC the client receives the query result set directly (rows and columns), without the JSON envelope (''version'' / ''resultados'' / ''metaData'' / ''datos'') described below, which is specific to SmartWebDoc. //The ODBC driver installation is the responsibility of the client's system administrator.//
+  * **ODBC**: alternatively, the same procedures can be queried directly through an ODBC connection to the Firebird database. The procedure and field names are identical, and the ''SELECT * FROM <procedure>(...)'' statements are the same; **only the transport and the response format change**. Over ODBC the client receives the query result set directly (rows and columns), without the JSON envelope (''version'' / ''resultados'' / ''metaData'' / ''datos'') described below, which is specific to SmartWebDoc. //The ODBC driver installation is the responsibility of the client's system administrator.// This option is not set up; SmartWebDoc is the method in use.
-At present the company has **neither** of the two services installed; one of them must be set up before the integration can run.
 The connection details and examples below refer to the **SmartWebDoc** service.
@@ Línea 25: / Línea 23: @@
 === Format ===
 http://#ip:#port/services/ismartwebdoc/?format=json
+For this installation (remote, over the VPN): http://25.15.243.110:8000/services/ismartwebdoc/?format=json
+From inside the company's LAN: http://172.30.0.98:8000/services/ismartwebdoc/?format=json
 === Possible ports ===
-  - //(to be defined)//
+  - 8000
 === Possible IP addresses ===
-  - //(to be defined)//
+  - 25.15.243.110 — Hamachi VPN address, for remote access (the one Kiloutou uses).
+  - 172.30.0.98 — local network address, only reachable from inside the company's LAN.
 === User credentials ===
-These credentials are not required by the interface, but are stated here so that the Smart administrator can set access restrictions or privileges on tables and procedures.
+No credentials need to be entered on the client side: the SmartWebDoc service is already configured with a dedicated user, created ad-hoc for Kiloutou, through which the Smart administrator sets the access restrictions and privileges on tables and procedures.
-  - Id: //(to be provided by the Smart administrator)//
-  - Password: //(to be provided by the Smart administrator)//
 === Other ===
 "Postman v8.0.4" was used for the examples.
+SmartWebDoc documentation: [[https://wiki.smartastur.com/doku.php?id=usu:smartweb:introduccion_a_smartwebdoc|Introducción a SmartWebDoc]].
 === Response envelope ===
@@ Línea 91: / Línea 94: @@
   - In ''metaData'', ''tipo'' is the data type of each field (''ftString'' for text, a numeric type for amounts and quantities, a date type for dates). ''etiqueta'' is, by default, the field name itself.
   - ''datos'' is the list of rows. Empty fields are returned as ''null''.
+----
+====== Health check ======
+Two quick calls to run before a load, to tell a connection/service problem apart from a data or date-format problem.
+=== 1. Connectivity and service ===
+A trivial query on a small referential. It must return ''estado'': "OK" and a row count. If it fails, the problem is the VPN, the service or the credentials, not the data.
+<code>
+{
+  "version"  : "1.2",
+  "metodo"   : "Raw",
+  "opciones" : {},
+  "comandos" :
+  [
+    { "sql": "SELECT COUNT(*) AS N FROM KIL_REF_BRANCHES" }
+  ]
+}
+</code>
+=== 2. Date format ===
+A small, date-bounded count. Replace the two dates with a recent one-day window in ''DD/MM/YYYY'' (here, 1 August 2026). A response with ''estado'': "OK" confirms the date format was accepted; the value of ''N'' is secondary (it may be 0 on a day with no invoices).
+  * If the response is an error such as ''"2026-08-01" is not a valid date format'', the dates are being sent in the wrong format (ISO or US order): switch to ''DD/MM/YYYY'' (see "General integration notes").
+<code>
+{
+  "version"  : "1.2",
+  "metodo"   : "Raw",
+  "opciones" : {},
+  "comandos" :
+  [
+    { "sql": "SELECT COUNT(*) AS N FROM KIL_INVOICES(:d, :h)",
+      "params": { "d": "01/08/2026", "h": "02/08/2026" } }
+  ]
+}
+</code>
 ----
@@ Línea 101: / Línea 140: @@
   * **Country**: the ''PAYCOD'' field is always "ES".
   * **Third-party codes**: customer, employee, etc. codes are issued without a type prefix (e.g. "027893", not "CL-027893").
-  * **Date range of the fact tables**: the ''KIL_INVOICES'', ''KIL_CONTRACTS'', ''KIL_REPAIR_ORDERS'' and ''KIL_SALES_USED_EQUIPMENTS'' procedures take two dates, ''P_FECHA_DESDE'' and ''P_FECHA_HASTA''. The interval is **half-open**: it includes ''P_FECHA_DESDE'' and **excludes** ''P_FECHA_HASTA''. To extract June 2026 you would pass from "1/6/2026" to "1/7/2026".
+  * **Date format**: pass dates as **''DD/MM/YYYY''** (day/month/year, e.g. "01/08/2026" for 1 August 2026). This is the format the service expects (Spanish regional settings). Do **not** use ISO ''YYYY-MM-DD'' — the service rejects it with a "... is not a valid date format" error — and do **not** use US ''MM/DD/YYYY''.
+  * **Date range of the fact tables**: the ''KIL_INVOICES'', ''KIL_CONTRACTS'', ''KIL_REPAIR_ORDERS'' and ''KIL_SALES_USED_EQUIPMENTS'' procedures take two dates, ''P_FECHA_DESDE'' and ''P_FECHA_HASTA''. The interval is **half-open**: it includes ''P_FECHA_DESDE'' and **excludes** ''P_FECHA_HASTA''. To extract June 2026 you would pass from "01/06/2026" to "01/07/2026".
   * **Snapshots**: the ''KIL_STOCK_SNAPSHOTS'' and ''KIL_VALORISATION'' procedures take **a single date** and compute that day's snapshot on the fly (there is no stored history).
   * **VAT (Invoices, Contracts)**: in SMART, VAT is computed at invoice closing, not line by line. In the extraction it is split per line with 5 decimals, and the tax amount includes the equivalence surcharge when the customer is subject to it; this way ''rate x base = amount'' and the sum of the lines matches the document total.
@@ Línea 369: / Línea 409: @@
   - BOOL_MATERIEL_SOUS_LOC VARCHAR(1)
 === Behaviour ===
-Returns the whole fleet (''ACTIVO''), including out-of-stock material.
+Returns the current fleet: the equipment that has live stock today, derived from the live inventory line in ''MOV_INV''. It includes material that is down for scrapping, breakdown or inspection (which keeps its line in the corresponding warehouse). Equipment that has left the fleet (sold, with no live line) is not included.
 ''RACNUMMAT'' is the equipment code (registration). ''RACMRQ'' (brand) and ''RACTYP'' (model) are obtained by splitting the single technical-sheet field. ''RACPRXACH''/''RACPRXACHDEV'' is the inventory value in €. ''RACSTACOD'' is the material status (DISPO, LOCATION, CONTROLE, PANNE...). ''AGECOD'' is the branch where it is located; ''CODE_AGENCE_AFFECTATION'' is the owning branch (in SMART it matches ''AGECOD''). ''BOOL_MATERIEL_SOUS_LOC'' is "1" if the material is sub-rented.
@@ Línea 486: / Línea 526: @@
     {
       "sql":"SELECT * FROM KIL_INVOICES(:desde, :hasta)",
-      "params": { "desde" : "1/6/2026", "hasta" : "1/7/2026"}
+      "params": { "desde" : "01/06/2026", "hasta" : "01/07/2026"}
     }
   ]
@@ Línea 501: / Línea 541: @@
     {
       "sql":"SELECT * FROM KIL_INVOICES(:desde, :hasta) WHERE FACCODAGECOM=:sucursal ORDER BY FACNUM",
-      "params": { "desde" : "1/6/2026", "hasta" : "1/7/2026", "sucursal" : "E031"}
+      "params": { "desde" : "01/06/2026", "hasta" : "01/07/2026", "sucursal" : "E031"}
     }
   ]
@@ Línea 566: / Línea 606: @@
     {
       "sql":"SELECT * FROM KIL_CONTRACTS(:desde, :hasta)",
-      "params": { "desde" : "1/6/2026", "hasta" : "1/7/2026"}
+      "params": { "desde" : "01/06/2026", "hasta" : "01/07/2026"}
     }
   ]
@@ Línea 581: / Línea 621: @@
     {
       "sql":"SELECT * FROM KIL_CONTRACTS(:desde, :hasta) WHERE CTRCODSTA=:estado",
-      "params": { "desde" : "1/6/2026", "hasta" : "1/7/2026", "estado" : "3"}
+      "params": { "desde" : "01/06/2026", "hasta" : "01/07/2026", "estado" : "3"}
     }
   ]
@@ Línea 618: / Línea 658: @@
   - COUT_UNITAIRE_LIGNE_OR NUMERIC(17,5),
   - BOOL_LIGNE_OR_A_REFACTURER VARCHAR(1),
-  - PRIX_UNITAIRE_VENTE_CLIENT_LIGNE_OR NUMERIC(17,5),
+  - PRIX_UNIT_VTE_CLIENT_LIGNE_OR NUMERIC(17,5),   //(template field: PRIX_UNITAIRE_VENTE_CLIENT_LIGNE_OR)//
   - TAUX_REMISE_CLIENT_LIGNE_OR NUMERIC(17,5),
   - MONTANT_A_REFACTURER_LIGNE_OR NUMERIC(17,5),
@@ Línea 627: / Línea 667: @@
 ''NUMERO_OR'' is the order number (Kiloutou's "OR-0000031566" format cannot be reproduced; SMART's number is issued). ''CODE_STATUT_OR'': "1" (open), "TERMINE" (in progress), "CLOS" (closed). //These values are provisional pending Kiloutou's status logic.//
+**Shortened column name:** the template field ''PRIX_UNITAIRE_VENTE_CLIENT_LIGNE_OR'' (35 characters) exceeds Firebird's 31-character identifier limit, so it is emitted as the output column ''PRIX_UNIT_VTE_CLIENT_LIGNE_OR''. The export process maps it back to the full template name.
 === Example ===
 == One month's repair orders ==
@@ Línea 638: / Línea 680: @@
     {
       "sql":"SELECT * FROM KIL_REPAIR_ORDERS(:desde, :hasta)",
-      "params": { "desde" : "1/6/2026", "hasta" : "1/7/2026"}
+      "params": { "desde" : "01/06/2026", "hasta" : "01/07/2026"}
     }
   ]
@@ Línea 680: / Línea 722: @@
     {
       "sql":"SELECT * FROM KIL_SALES_USED_EQUIPMENTS(:desde, :hasta) ORDER BY INVOICE_DATE",
-      "params": { "desde" : "1/1/2026", "hasta" : "1/1/2027"}
+      "params": { "desde" : "01/01/2026", "hasta" : "01/01/2027"}
     }
   ]
@@ Línea 720: / Línea 762: @@
     {
       "sql":"SELECT * FROM KIL_STOCK_SNAPSHOTS(:fecha)",
-      "params": { "fecha" : "15/6/2026"}
+      "params": { "fecha" : "15/06/2026"}
     }
   ]
@@ Línea 735: / Línea 777: @@
     {
       "sql":"SELECT * FROM KIL_STOCK_SNAPSHOTS(:fecha) WHERE RACSTACOD=:estado",
-      "params": { "fecha" : "15/6/2026", "estado" : "DISPO"}
+      "params": { "fecha" : "15/06/2026", "estado" : "DISPO"}
     }
   ]
@@ Línea 773: / Línea 815: @@
     {
       "sql":"SELECT * FROM KIL_VALORISATION(:fecha)",
-      "params": { "fecha" : "30/6/2026"}
+      "params": { "fecha" : "30/06/2026"}
     }
   ]
@@ Línea 788: / Línea 830: @@
     {
       "sql":"SELECT CTRCODAGE, SUM(CTRMNTVAL) FROM KIL_VALORISATION(:fecha) GROUP BY CTRCODAGE",
-      "params": { "fecha" : "30/6/2026"}
+      "params": { "fecha" : "30/06/2026"}
     }
   ]
+}
+</code>
+----
+====== Exploring the database ======
+Kiloutou has full read access to the database, not only to the datasets described above. The two system-catalog queries below let the agent discover any table and its columns; once a table and its columns are known, it can be read with an ordinary ''SELECT''.
+=== List all tables ===
+Returns every user table (system tables and views excluded), ordered by name.
+<code>
+{
+  "version"  : "1.2",
+  "metodo"   : "Raw",
+  "opciones" : {},
+  "comandos" :
+  [
+    { "sql": "SELECT TRIM(RDB$RELATION_NAME) AS TABLA FROM RDB$RELATIONS WHERE RDB$VIEW_BLR IS NULL AND COALESCE(RDB$SYSTEM_FLAG,0)=0 ORDER BY RDB$RELATION_NAME" }
+  ]
+}
+</code>
+To include views as well, remove the ''RDB$VIEW_BLR IS NULL'' condition.
+=== Columns of a table ===
+Pass the table name (in upper or lower case) in the ''tabla'' parameter. For each column it returns: ''CAMPO'' (name), ''TIPO'' (SMALLINT, INTEGER, BIGINT, NUMERIC, FLOAT, DOUBLE PRECISION, DATE, TIME, TIMESTAMP, CHAR, VARCHAR, BLOB...), ''LONGITUD'' (length in characters for text types), ''PRECISION_'' and ''ESCALA'' (meaningful for NUMERIC/DECIMAL), and ''ACEPTA_NULOS'' ("N" = NOT NULL, "S" = accepts nulls). The columns come back in their physical order.
+<code>
+{
+  "version"  : "1.2",
+  "metodo"   : "Raw",
+  "opciones" : {},
+  "comandos" :
+  [
+    {
+      "sql": "SELECT TRIM(RF.RDB$FIELD_NAME) AS CAMPO, CASE WHEN F.RDB$FIELD_TYPE IN (7,8,16) AND F.RDB$FIELD_SCALE < 0 THEN 'NUMERIC' WHEN F.RDB$FIELD_TYPE=7 THEN 'SMALLINT' WHEN F.RDB$FIELD_TYPE=8 THEN 'INTEGER' WHEN F.RDB$FIELD_TYPE=16 THEN 'BIGINT' WHEN F.RDB$FIELD_TYPE=10 THEN 'FLOAT' WHEN F.RDB$FIELD_TYPE=27 THEN 'DOUBLE PRECISION' WHEN F.RDB$FIELD_TYPE=12 THEN 'DATE' WHEN F.RDB$FIELD_TYPE=13 THEN 'TIME' WHEN F.RDB$FIELD_TYPE=35 THEN 'TIMESTAMP' WHEN F.RDB$FIELD_TYPE=14 THEN 'CHAR' WHEN F.RDB$FIELD_TYPE=37 THEN 'VARCHAR' WHEN F.RDB$FIELD_TYPE=261 THEN 'BLOB' ELSE 'TYPE_' || CAST(F.RDB$FIELD_TYPE AS VARCHAR(5)) END AS TIPO, COALESCE(F.RDB$CHARACTER_LENGTH, F.RDB$FIELD_LENGTH) AS LONGITUD, F.RDB$FIELD_PRECISION AS PRECISION_, (-F.RDB$FIELD_SCALE) AS ESCALA, CASE WHEN COALESCE(RF.RDB$NULL_FLAG, F.RDB$NULL_FLAG)=1 THEN 'N' ELSE 'S' END AS ACEPTA_NULOS FROM RDB$RELATION_FIELDS RF JOIN RDB$FIELDS F ON F.RDB$FIELD_NAME=RF.RDB$FIELD_SOURCE WHERE RF.RDB$RELATION_NAME=UPPER(:tabla) ORDER BY RF.RDB$FIELD_POSITION",
+      "params": { "tabla": "ACTIVO" }
+    }
+  ]
+}
+</code>
+Example response for the table ''ZONA'':
+<code>
+{
+    "version": "1.2",
+    "resultados": [
+        {
+            "estado": "OK",
+            "datos": [
+                { "CAMPO": "CODIGO",      "TIPO": "VARCHAR", "LONGITUD": 10, "PRECISION_": null, "ESCALA": 0, "ACEPTA_NULOS": "N" },
+                { "CAMPO": "DESCRIPCION", "TIPO": "VARCHAR", "LONGITUD": 30, "PRECISION_": null, "ESCALA": 0, "ACEPTA_NULOS": "S" }
+            ]
+        }
+    ]
 }
 </code>
@@ Línea 826: / Línea 921: @@
 == Document change log ==
+ --- //[[juanma@smartastur.com|Juanma]] 2026/10/25 // Equipment: now derived from the live MOV_INV line (current fleet); out-of-stock/sold equipment no longer listed.
+ --- //[[juanma@smartastur.com|Juanma]] 2026/10/22 // "Exploring the database" section: list all tables and list the columns of a table (full read access).
+ --- //[[juanma@smartastur.com|Juanma]] 2026/10/22 // Local network IP (172.30.0.98) kept as an alternative alongside the Hamachi VPN address.
+ --- //[[juanma@smartastur.com|Juanma]] 2026/10/22 // Health check section (connectivity and date-format probes).
+ --- //[[juanma@smartastur.com|Juanma]] 2026/10/22 // Date format clarified: the service requires DD/MM/YYYY and rejects ISO (verified against the live service). Examples use DD/MM/YYYY.
+ --- //[[juanma@smartastur.com|Juanma]] 2026/10/22 // Database IP changed to the Hamachi VPN address (25.15.243.110).
+ --- //[[juanma@smartastur.com|Juanma]] 2026/07/30 // Repair Orders: column PRIX_UNITAIRE_VENTE_CLIENT_LIGNE_OR shortened to PRIX_UNIT_VTE_CLIENT_LIGNE_OR (Firebird 31-char identifier limit).
+ --- //[[juanma@smartastur.com|Juanma]] 2026/07/30 // SmartWebDoc connection details (IP, port) and documentation link. Credentials configured ad-hoc on the service.
  --- //[[juanma@smartastur.com|Juanma]] 2026/07/30 // KIL_RACINE loading workflow (spreadsheet round-trip).
  --- //[[juanma@smartastur.com|Juanma]] 2026/07/30 // Initial document. SMART → Kiloutou extraction (EX06468): referentials, fact tables and snapshots.