| Próxima revisión | Revisión previa |
| mav:doc:documentacion_integracion_kilotou [2026/07/30 09:29] – creado juanma | mav:doc:documentacion_integracion_kilotou [2026/10/25 21:19] (actual) – juanma |
|---|
| |
| ====== General information ====== | ====== General information ====== |
| | |
| | === Access methods === |
| | 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. **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.// This option is not set up; SmartWebDoc is the method in use. |
| | |
| | The connection details and examples below refer to the **SmartWebDoc** service. |
| | |
| === Method === | === Method === |
| POST | POST |
| === Format === | === Format === |
| http://#ip:#port/services/ismartwebdoc/?format=json | 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 === | === Possible ports === |
| - //(to be defined)// | - 8000 |
| === Possible IP addresses === | === 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 === | === 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 === | === Other === |
| "Postman v8.0.4" was used for the examples. | "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 === | === Response envelope === |
| - 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. | - 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''. | - ''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> |
| |
| ---- | ---- |
| * **Country**: the ''PAYCOD'' field is always "ES". | * **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"). | * **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). | * **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. | * **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. |
| - BOOL_MATERIEL_SOUS_LOC VARCHAR(1) | - BOOL_MATERIEL_SOUS_LOC VARCHAR(1) |
| === Behaviour === | === 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. | ''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. |
| { | { |
| "sql":"SELECT * FROM KIL_INVOICES(:desde, :hasta)", | "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"} |
| } | } |
| ] | ] |
| { | { |
| "sql":"SELECT * FROM KIL_INVOICES(:desde, :hasta) WHERE FACCODAGECOM=:sucursal ORDER BY FACNUM", | "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"} |
| } | } |
| ] | ] |
| { | { |
| "sql":"SELECT * FROM KIL_CONTRACTS(:desde, :hasta)", | "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"} |
| } | } |
| ] | ] |
| { | { |
| "sql":"SELECT * FROM KIL_CONTRACTS(:desde, :hasta) WHERE CTRCODSTA=:estado", | "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"} |
| } | } |
| ] | ] |
| - COUT_UNITAIRE_LIGNE_OR NUMERIC(17,5), | - COUT_UNITAIRE_LIGNE_OR NUMERIC(17,5), |
| - BOOL_LIGNE_OR_A_REFACTURER VARCHAR(1), | - 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), | - TAUX_REMISE_CLIENT_LIGNE_OR NUMERIC(17,5), |
| - MONTANT_A_REFACTURER_LIGNE_OR NUMERIC(17,5), | - MONTANT_A_REFACTURER_LIGNE_OR NUMERIC(17,5), |
| |
| ''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.// | ''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 === | === Example === |
| == One month's repair orders == | == One month's repair orders == |
| { | { |
| "sql":"SELECT * FROM KIL_REPAIR_ORDERS(:desde, :hasta)", | "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"} |
| } | } |
| ] | ] |
| { | { |
| "sql":"SELECT * FROM KIL_SALES_USED_EQUIPMENTS(:desde, :hasta) ORDER BY INVOICE_DATE", | "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"} |
| } | } |
| ] | ] |
| { | { |
| "sql":"SELECT * FROM KIL_STOCK_SNAPSHOTS(:fecha)", | "sql":"SELECT * FROM KIL_STOCK_SNAPSHOTS(:fecha)", |
| "params": { "fecha" : "15/6/2026"} | "params": { "fecha" : "15/06/2026"} |
| } | } |
| ] | ] |
| { | { |
| "sql":"SELECT * FROM KIL_STOCK_SNAPSHOTS(:fecha) WHERE RACSTACOD=:estado", | "sql":"SELECT * FROM KIL_STOCK_SNAPSHOTS(:fecha) WHERE RACSTACOD=:estado", |
| "params": { "fecha" : "15/6/2026", "estado" : "DISPO"} | "params": { "fecha" : "15/06/2026", "estado" : "DISPO"} |
| } | } |
| ] | ] |
| { | { |
| "sql":"SELECT * FROM KIL_VALORISATION(:fecha)", | "sql":"SELECT * FROM KIL_VALORISATION(:fecha)", |
| "params": { "fecha" : "30/6/2026"} | "params": { "fecha" : "30/06/2026"} |
| } | } |
| ] | ] |
| { | { |
| "sql":"SELECT CTRCODAGE, SUM(CTRMNTVAL) FROM KIL_VALORISATION(:fecha) GROUP BY CTRCODAGE", | "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> | </code> |
| === Usage === | === Usage === |
| ''RACCOD'', ''GBV'' and ''GBVA'' feed the fields of the same name in the referentials and fact tables (Equipment, Stock snapshots, Invoices...). The FCCS consolidation account (''KIL_ACCOUNT'' in Invoices) is mapped by Kiloutou in its platform. | ''RACCOD'', ''GBV'' and ''GBVA'' feed the fields of the same name in the referentials and fact tables (Equipment, Stock snapshots, Invoices...). The FCCS consolidation account (''KIL_ACCOUNT'' in Invoices) is mapped by Kiloutou in its platform. |
| | |
| | === Loading workflow === |
| | ''KIL_RACINE'' is populated through an iterative spreadsheet round-trip: |
| | |
| | - **Extract the pending equipment.** Smart runs a procedure that returns every asset not yet present in ''KIL_RACINE'', and exports it to an Excel sheet. The sheet has five columns: ''COD_ACTIVO'' and ''DESCRIPCION'' (already filled in, for identification only) and ''RACCOD'', ''GBV'', ''GBVA'' (empty, to be completed). |
| | - **Send the sheet to Kiloutou.** The Fleet Dept. fills in ''RACCOD'' (Kiloutou root) and the finance team fills in ''GBV''/''GBVA''. ''COD_ACTIVO'' and ''DESCRIPCION'' must not be changed. |
| | - **Return and import.** Smart loads the completed sheet back into SmartOffice and processes it into ''KIL_RACINE''. |
| | - **Repeat** until step 1 returns no rows, i.e. every asset has a Kiloutou root. |
| | |
| | Notes: |
| | - Only rows that come back **with a ''RACCOD''** are registered. Rows left without a root are ignored and will appear again in the next extraction, so the loop keeps requesting them until they are resolved. |
| | - ''GBV'' and ''GBVA'' may be left empty in a given round and completed later; the amount fields accept decimals. |
| | - New equipment added after a round is automatically picked up by the next extraction. |
| |
| ---- | ---- |
| |
| == Document change log == | == 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. | --- //[[juanma@smartastur.com|Juanma]] 2026/07/30 // Initial document. SMART → Kiloutou extraction (EX06468): referentials, fact tables and snapshots. |
| |
