Cube_PA is a heap in SQL Server with no primary key, while the
PostgreSQL cube_pa table uses artnoid as PRIMARY KEY. The cube
normally emits one row per article, but for 3 articles supplied by
two vendors it emitted both vendor prices, so fullRefresh hit:
duplicate key value violates unique constraint "cube_pa_pkey"
These are not exact duplicates: the two rows carry different PA
values, so the choice changes the article's purchase price. Resolve
by keeping the price whose last change is the most recent, read from
the dated history packed into ARTFOU2.PRIXACHAT
("[01/01/1901,0.990:16/05/2018,1.010]"). SUIVIDATEMODIF cannot be
used alone: it is not touched when only the price changes (S028 shows
modif 2017 but a price change in 2025). It only breaks ties.
The lookup runs solely when duplicates are present, so the nightly
sync is unaffected in the normal case.
Verified against the live 427 752-row Cube_PA: 427 749 rows out, all
artnoid distinct, no fallback triggered. Parsing PRIXACHAT over 20 000
ARTFOU2 rows: 19 999 parsed, 1 empty, 0 failures.
Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>
The code centrale is LaFoir'Fouille's 11-digit product identifier
(prefix 1000x). Only products referenced by the central purchasing
office have one: ~355 800 of 427 700 articles.
The column was already synced from SQL Server and returned by the
detail route via `a.*`, but was absent from the list route and could
not be filtered on.
- list route: select artcentrale, add `artcentrale` (partial match)
and `has_artcentrale=1|0` query params
- filter matches on prefix AND length 11, so malformed values with a
valid prefix ('10000', '10000192289P') are excluded
- COALESCE in the predicate: without it, NOT (NULL LIKE '1000%') is
NULL and the 61 442 NULL rows would vanish from has_artcentrale=0
- normalise '' to null in both routes so they agree
- sync: use safeStr so empty values are stored as NULL, not ''
Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>
The source MSSQL FOUCAD table can return several rows for the same
FOUCODE, but the PostgreSQL foucad table uses foucode as PRIMARY KEY.
The bulk INSERT in fullRefresh has no conflict handling, so duplicate
FOUCODE values triggered:
duplicate key value violates unique constraint "foucad_pkey"
Deduplicate by foucode before insert, keeping the most recently
modified row (suividatemodif). Routes join foucad 1:1 on foucode, so
one config row per supplier code is the intended shape.
Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>
Claude-Session: https://claude.ai/code/session_011ad1qwhDEHNniSUDveTQHL
FOUCAD ne couvre que 53/153 fournisseurs actifs (commande auto uniquement).
FOUPORT2.SEUIL est la source officielle de l'ERP et couvre 108 fournisseurs.
4 fournisseurs dans COMMANDE_AUTO_QTEPROPO n'avaient pas de franco visible
(IMDIFA, MYCANDYSHOP, PTIT CLOWN, PUCKATOR BV).
- Sync : ajout de FOUPORT et FOUPORT2 dans le pipeline de synchronisation
- API : franco = COALESCE(fouport2.seuil, foucad.franco) dans les deux routes
- Schema PG : création des tables fouport et fouport2
Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
- Nouvelle route GET /api/commandes-auto : propositions par site et fournisseur
avec comparaison au franco (franco_atteint, ecart_franco)
- Nouvelle route GET /api/commandes-auto/:codefou : détail articles d'un fournisseur
- Sync FOUCAD (config commande auto + franco) vers PostgreSQL
- Ajout table foucad dans init.sql
Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
- ranking.js: déduplique par (gencod, site) avant batchUpsert pour éviter
"ON CONFLICT DO UPDATE command cannot affect row a second time"
- server.js: bouton "Copier les logs" utilise execCommand fallback quand
navigator.clipboard indisponible (accès HTTP sans HTTPS)
Co-Authored-By: Claude Opus 4.6 <noreply@anthropic.com>
- Add ON CONFLICT DO NOTHING to mvtart insert to work with unique index
- Document ranking endpoints in API_DOCUMENTATION.md
Co-Authored-By: Claude Opus 4.6 <noreply@anthropic.com>
Sync Ranking table from SQL Server to PostgreSQL with ranking_ca,
ranking_qte, ranking_mag_* metrics. Expose via /api/ranking and
integrate into /api/articles/:id/referentiel response.
Co-Authored-By: Claude Opus 4.6 <noreply@anthropic.com>
MichaelandClaude Opus 4.8