ldc-arcadia-api/documentation/Arcadia

Métamodèle Arcadia & Intégration Oracle

Ce document résume les principes d'intégration avec les bases de données Oracle du SI Arcadia (REFP, METAP, GCP).

---

1. Documentation Vivante des Tables & Vues (Living Documentation)

La structure complète des 56 tables et vues, leurs types de données réels, clés primaires et commentaires SQL sont maintenus directement dans le code source sous :

👉 database/arcadia-replicate/

• Tables REFP & METAP : database/arcadia-replicate/Tables/
• Vues REFP & GCP : database/arcadia-replicate/Views/
• Orchestrateur & Migrations : database/arcadia-replicate/Migrator.php

Tip

En cas d'évolution du métamodèle Arcadia, modifiez uniquement la classe PHP concernée dans database/arcadia-replicate/. Aucun fichier Markdown de structure n'a besoin d'être maintenu manuellement.

---

2. Requêtes SQL de Référence (Adapters Oracle)

A. Fiche Produit INCO & Nutrition (REFP.INCO + REFP.INCO_COMPO + REFP.ARTICLE_REF)

Liaisons principales :

• Nutrition : REFP.INCO.REF_COMPO = REFP.INCO_COMPO.ID_INCO
• Marque & Famille de vente : REFP.INCO.NO_ART = REFP.ARTICLE_REF.NO_ART (colonnes COD_MARQUE et COD_FAMVTE) (Note : REFP.INCO ne possède pas de colonne MARQUE directe ; le filtrage par marque s'effectue via WHERE EXISTS (SELECT 1 FROM REFP.ARTICLE_REF ar WHERE ar.NO_ART = i.NO_ART AND ar.COD_MARQUE IN (...)))

SELECT 
    i.NO_ART                     AS id,
    i.DENOM_LEG                  AS name,
    i.DESCRIPTIF                 AS description,
    i.ASTUCES                    AS tips,
    i.CONSEIL_PREPA              AS preparation_advices,
    i.ARG_VENTE                  AS sell_arguments,
    i.ALLEGATIONS                AS allegations,
    i.ALLEGAT_SANTE              AS allegations_health,
    i.ALLEGAT_NUTRIT             AS allegations_nutrition,
    i.LST_ING                    AS ingredients,
    i.NB_PORTION                 AS portions,
    i.LST_ALLERGENE              AS allergens,
    i.NUTRISCORE_ON_PACK         AS nutriscore,
    i.PDS_NET_UVC                AS pdsNetUvc,
    i.PDS_BRUT_UVC               AS pdsBrutUvc,
    i.CONDIT_CONSERV             AS conditConserv,
    i.LST_ADDITIF                AS lstAdditif,
    i.PROD_SAISON                AS is_seasonal,
    i.REF_COMPO                  AS compo,
    i.DAT_CRE                    AS created_at,
    i.DAT_MAJ                    AS updated_at,
    c.ID                         AS compo_id,
    c.VAL_ENERG_KJ_R             AS compo_valEnergKjReference,
    c.VAL_ENERG_KCAL_R           AS compo_valEnergKcalReference,
    c.MAT_GRASS_R                AS compo_matGrassReference,
    c.AGS_R                      AS compo_acideGrasSatureReference,
    c.GLUCIDES_R                 AS compo_glucidesReference,
    c.SUCRE_R                    AS compo_sucreReference,
    c.FIB_ALIM_R                 AS compo_fibAlimReference,
    c.PROTEINE_R                 AS compo_proteineReference,
    c.SEL_R                      AS compo_selReference,
    c.SCORE_NUTRI_2022           AS compo_scoreNutri2022,
    c.NUTRI_SCORE_2022           AS compo_nutriScore2022
FROM REFP.INCO i
LEFT JOIN REFP.INCO_COMPO c 
    ON i.REF_COMPO = c.ID_INCO;

B. Référentiel Salariés SIRH (REFP.RU_PERSONNE_SIRH)

SELECT 
    e.MATRICULE_FIC     AS matricule,
    e.NOM_FIC           AS lastname,
    e.PRENOM_FIC        AS firstname,
    e.DATE_DEB_FIC      AS start_date,
    e.DATE_FIN_FIC      AS end_date,
    e.ETAB_FIC          AS establishment,
    e.SITE_FIC          AS site,
    e.SERVICE_FIC       AS service,
    e.DAT_CRE           AS created_at,
    e.DAT_MAJ           AS updated_at
FROM REFP.RU_PERSONNE_SIRH e;

C. Utilisateurs Référents (METAP.META_UTIL)

SELECT 
    i.NO_ART            AS id,
    i.UTIL_CMCL         AS util_cmcl_code,
    u_cmcl.LIB_UTIL     AS util_cmcl_libelle,
    u_cmcl.EMAIL        AS util_cmcl_email,
    i.UTIL_MKT          AS util_mkt_code,
    u_mkt.LIB_UTIL      AS util_mkt_libelle,
    u_mkt.EMAIL         AS util_mkt_email,
    i.UTIL_QUAL         AS util_qual_code,
    u_qual.LIB_UTIL     AS util_qual_libelle,
    u_qual.EMAIL        AS util_qual_email
FROM REFP.INCO i
LEFT JOIN METAP.META_UTIL u_cmcl ON i.UTIL_CMCL = u_cmcl.COD_UTIL
LEFT JOIN METAP.META_UTIL u_mkt ON i.UTIL_MKT = u_mkt.COD_UTIL
LEFT JOIN METAP.META_UTIL u_qual ON i.UTIL_QUAL = u_qual.COD_UTIL;

D. Référentiel des Marques (REFP.MARQUE)

Requête SQL Oracle de référence

SELECT 
    m.COD_MARQ         AS id,
    m.LIB_MARQ         AS name,
    m.COD_RGPT_MARQ    AS rgpt_code,
    m.COMMENTAIRE      AS "comment",
    m.TYPE_MARQUE      AS type,
    m.SOCIETE          AS societe,
    m.MARQUE_PROPRE    AS is_own_brand,
    m.GLN_SOC          AS gln
FROM REFP.MARQUE m
ORDER BY m.COD_MARQ ASC;

Requête HTTP API

GET /api/v1/brands
Authorization: Bearer <API_TOKEN>
Accept: application/json

Exemple avec cURL :

curl -X GET "https://api.arcadia.ldc.fr/api/v1/brands" \
  -H "Authorization: Bearer YOUR_API_TOKEN" \
  -H "Accept: application/json"

Exemple de réponse JSON (200 OK) :

{
  "data": [
    {
      "id": "1",
      "name": "LE GAULOIS"
    },
    {
      "id": "2",
      "name": "MAITRE COQ"
    },
    {
      "id": "3",
      "name": "LOUÉ"
    }
  ],
  "links": {
    "self": "http://localhost/api/v1/brands"
  }
}

Commande CLI Artisan

php artisan uwil:arcadia:brand:list