Diferencias

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

--- mav:doc:documentacion_integracion_kilotou [2026/07/30 15:38] – juanma
+++ mav:doc:documentacion_integracion_kilotou [2026/10/25 21:19] (actual) – juanma
@@ Línea 24: / Línea 24: @@
 http://#ip:#port/services/ismartwebdoc/?format=json
-For this installation: http://172.30.0.98:8000/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 ===
   - 8000
 === Possible IP addresses ===
-  - 172.30.0.98
+  - 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 ===
 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.
@@ 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 640: / 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 682: / 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 722: / Línea 762: @@
     {
       "sql":"SELECT * FROM KIL_STOCK_SNAPSHOTS(:fecha)",
-      "params": { "fecha" : "15/6/2026"}
+      "params": { "fecha" : "15/06/2026"}
     }
   ]
@@ Línea 737: / 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 775: / Línea 815: @@
     {
       "sql":"SELECT * FROM KIL_VALORISATION(:fecha)",
-      "params": { "fecha" : "30/6/2026"}
+      "params": { "fecha" : "30/06/2026"}
     }
   ]
@@ Línea 790: / 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 828: / 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).