|
|
|
@@ -0,0 +1,264 @@
|
|
|
|
|
<?php
|
|
|
|
|
|
|
|
|
|
declare(strict_types=1);
|
|
|
|
|
|
|
|
|
|
namespace DoctrineMigrations;
|
|
|
|
|
|
|
|
|
|
use App\Shared\Infrastructure\Database\ColumnCommentsCatalog;
|
|
|
|
|
use Doctrine\DBAL\Schema\Schema;
|
|
|
|
|
use Doctrine\Migrations\AbstractMigration;
|
|
|
|
|
|
|
|
|
|
/**
|
|
|
|
|
* M5 — Tickets de pesee (ERP-182) : creation du schema BDD du module Logistique.
|
|
|
|
|
*
|
|
|
|
|
* Objets crees :
|
|
|
|
|
* - site.code : code court du site (86/17/82), prefixe de numerotation des
|
|
|
|
|
* tickets (RG-5.02). Backfill depuis les 2 premiers chiffres du code postal
|
|
|
|
|
* + index unique uq_site_code (§ 2.5). NULLABLE a ce ticket (l'entite Site ne
|
|
|
|
|
* mappe pas encore `code`) ; le mapping ORM + peuplement + SET NOT NULL sont
|
|
|
|
|
* portes par le ticket entite (WeighingTicket).
|
|
|
|
|
* - weighing_ticket_counter : sequence du numero de ticket par site (RG-5.02).
|
|
|
|
|
* - weighbridge_dsd_counter : compteur DSD du pont bascule par site (RG-5.04).
|
|
|
|
|
* - weighing_ticket : table principale (contrepartie Client/Fournisseur/Autre,
|
|
|
|
|
* immatriculation partagee, pesees a vide + a plein en colonnes plates,
|
|
|
|
|
* poids net derive, soft-delete prepare + Timestampable/Blamable).
|
|
|
|
|
*
|
|
|
|
|
* Namespace racine `DoctrineMigrations` (regle ABSOLUE n°11) et NON modulaire :
|
|
|
|
|
* la table porte des FK cross-module (user, client, supplier, site). Le tri par
|
|
|
|
|
* timestamp au sein du namespace racine garantit l'ordre apres la creation de
|
|
|
|
|
* ces tables sur base vide ; un namespace modulaire casserait `make db-reset`.
|
|
|
|
|
*
|
|
|
|
|
* Convention IDs (spec § 2.2) : `INT GENERATED BY DEFAULT AS IDENTITY`,
|
|
|
|
|
* horodatages `TIMESTAMP(0) WITHOUT TIME ZONE` (le TimestampableBlamableTrait
|
|
|
|
|
* mappe `datetime_immutable`).
|
|
|
|
|
*
|
|
|
|
|
* Chaque colonne porte son `COMMENT ON COLUMN` (regle ABSOLUE n°12).
|
|
|
|
|
*
|
|
|
|
|
* NB schema:update (test-db-setup) :
|
|
|
|
|
* - weighing_ticket_counter / weighbridge_dsd_counter ne sont JAMAIS mappees en
|
|
|
|
|
* ORM (DBAL brut sous verrou FOR UPDATE, § 2.5 / § 2.7) -> exclues du
|
|
|
|
|
* `schema_filter` (config/packages/doctrine.yaml) pour que schema:update ne
|
|
|
|
|
* les drope pas. Leurs descriptions sont aussi catalogue-es dans
|
|
|
|
|
* ColumnCommentsCatalog (rejeu par `app:apply-column-comments`).
|
|
|
|
|
* - weighing_ticket et la colonne site.code seront mappes en ORM au ticket
|
|
|
|
|
* suivant (entite WeighingTicket + propriete Site::code) ; d'ici la,
|
|
|
|
|
* schema:update les drope sur la base de TEST uniquement (sans impact : aucun
|
|
|
|
|
* test ne les reference encore, et dev/prod ne lancent jamais schema:update).
|
|
|
|
|
*/
|
|
|
|
|
final class Version20260617150000 extends AbstractMigration
|
|
|
|
|
{
|
|
|
|
|
public function getDescription(): string
|
|
|
|
|
{
|
|
|
|
|
return 'ERP-182 (M5) : site.code + compteurs (numero ticket / DSD) + table weighing_ticket (tickets de pesee).';
|
|
|
|
|
}
|
|
|
|
|
|
|
|
|
|
public function up(Schema $schema): void
|
|
|
|
|
{
|
|
|
|
|
$this->addSiteCode();
|
|
|
|
|
$this->createWeighingTicketCounter();
|
|
|
|
|
$this->createWeighbridgeDsdCounter();
|
|
|
|
|
$this->createWeighingTicket();
|
|
|
|
|
}
|
|
|
|
|
|
|
|
|
|
public function down(Schema $schema): void
|
|
|
|
|
{
|
|
|
|
|
// Ordre inverse : table principale puis compteurs, enfin la colonne site.code.
|
|
|
|
|
$this->addSql('DROP TABLE IF EXISTS weighing_ticket');
|
|
|
|
|
$this->addSql('DROP TABLE IF EXISTS weighbridge_dsd_counter');
|
|
|
|
|
$this->addSql('DROP TABLE IF EXISTS weighing_ticket_counter');
|
|
|
|
|
$this->addSql('DROP INDEX IF EXISTS uq_site_code');
|
|
|
|
|
$this->addSql('ALTER TABLE site DROP COLUMN IF EXISTS code');
|
|
|
|
|
}
|
|
|
|
|
|
|
|
|
|
// =================================================================
|
|
|
|
|
// site.code — prefixe de numerotation des tickets (§ 2.5)
|
|
|
|
|
// =================================================================
|
|
|
|
|
|
|
|
|
|
private function addSiteCode(): void
|
|
|
|
|
{
|
|
|
|
|
// Colonne NULLABLE a ce ticket : l'entite Site ne mappe pas encore `code`,
|
|
|
|
|
// donc tout persist ORM (fixtures, tests) l'omettrait -> un NOT NULL casserait
|
|
|
|
|
// `make db-reset`. Le mapping ORM Site::code, son peuplement (86/17/82) et le
|
|
|
|
|
// passage `SET NOT NULL` sont portes par le ticket suivant (entite WeighingTicket
|
|
|
|
|
// + Site::code), via une 2e migration. L'index unique est pose des maintenant
|
|
|
|
|
// (Postgres tolere plusieurs NULL) : il garantit l'unicite des qu'ils seront peuples.
|
|
|
|
|
$this->addSql('ALTER TABLE site ADD COLUMN code VARCHAR(8) DEFAULT NULL');
|
|
|
|
|
// Backfill : 2 premiers chiffres du code postal (departement) par defaut,
|
|
|
|
|
// editable ensuite cote admin Sites. No-op sur base fraiche (aucun site encore).
|
|
|
|
|
$this->addSql('UPDATE site SET code = LEFT(postal_code, 2) WHERE code IS NULL');
|
|
|
|
|
$this->addSql('CREATE UNIQUE INDEX uq_site_code ON site (code)');
|
|
|
|
|
|
|
|
|
|
$this->comment('site', 'code', 'Code court du site (ex. 86/17/82) — prefixe de numerotation des tickets de pesee (RG-5.02). Unique (uq_site_code). Backfill = 2 premiers chiffres du CP. NOT NULL pose au ticket entite.');
|
|
|
|
|
}
|
|
|
|
|
|
|
|
|
|
// =================================================================
|
|
|
|
|
// Compteur du numero de ticket (sequence par site) — RG-5.02
|
|
|
|
|
// =================================================================
|
|
|
|
|
|
|
|
|
|
private function createWeighingTicketCounter(): void
|
|
|
|
|
{
|
|
|
|
|
$this->addSql(<<<'SQL'
|
|
|
|
|
CREATE TABLE weighing_ticket_counter (
|
|
|
|
|
site_id INT NOT NULL,
|
|
|
|
|
last_value INT DEFAULT 0 NOT NULL,
|
|
|
|
|
PRIMARY KEY (site_id),
|
|
|
|
|
CONSTRAINT fk_wt_counter_site
|
|
|
|
|
FOREIGN KEY (site_id) REFERENCES site (id) ON DELETE CASCADE
|
|
|
|
|
)
|
|
|
|
|
SQL);
|
|
|
|
|
|
|
|
|
|
$this->comment('weighing_ticket_counter', '_table', 'Sequence du numero de ticket de pesee par site (RG-5.02, M5 Logistique) — incrementee en DBAL brut sous verrou FOR UPDATE, hors ORM.');
|
|
|
|
|
$this->comment('weighing_ticket_counter', 'site_id', 'Site proprietaire de la sequence (1 ligne par site). PK + FK -> site.id, ON DELETE CASCADE.');
|
|
|
|
|
$this->comment('weighing_ticket_counter', 'last_value', 'Dernier numero de ticket attribue pour le site. Increment verrouille FOR UPDATE (RG-5.02).');
|
|
|
|
|
}
|
|
|
|
|
|
|
|
|
|
// =================================================================
|
|
|
|
|
// Compteur DSD (pesee du pont, par site) — RG-5.04
|
|
|
|
|
// =================================================================
|
|
|
|
|
|
|
|
|
|
private function createWeighbridgeDsdCounter(): void
|
|
|
|
|
{
|
|
|
|
|
$this->addSql(<<<'SQL'
|
|
|
|
|
CREATE TABLE weighbridge_dsd_counter (
|
|
|
|
|
site_id INT NOT NULL,
|
|
|
|
|
last_value INT DEFAULT 0 NOT NULL,
|
|
|
|
|
PRIMARY KEY (site_id),
|
|
|
|
|
CONSTRAINT fk_dsd_counter_site
|
|
|
|
|
FOREIGN KEY (site_id) REFERENCES site (id) ON DELETE CASCADE
|
|
|
|
|
)
|
|
|
|
|
SQL);
|
|
|
|
|
|
|
|
|
|
$this->comment('weighbridge_dsd_counter', '_table', 'Compteur DSD du pont bascule par site (RG-5.04, M5 Logistique) — chaque pesee consomme une valeur. Incremente en DBAL brut sous verrou FOR UPDATE, hors ORM.');
|
|
|
|
|
$this->comment('weighbridge_dsd_counter', 'site_id', 'Site proprietaire du compteur (1 pont par site). PK + FK -> site.id, ON DELETE CASCADE.');
|
|
|
|
|
$this->comment('weighbridge_dsd_counter', 'last_value', 'Derniere valeur DSD attribuee pour le site (pont bascule). Increment verrouille FOR UPDATE (RG-5.04).');
|
|
|
|
|
}
|
|
|
|
|
|
|
|
|
|
// =================================================================
|
|
|
|
|
// Table principale `weighing_ticket`
|
|
|
|
|
// =================================================================
|
|
|
|
|
|
|
|
|
|
private function createWeighingTicket(): void
|
|
|
|
|
{
|
|
|
|
|
$this->addSql(<<<'SQL'
|
|
|
|
|
CREATE TABLE weighing_ticket (
|
|
|
|
|
id INT GENERATED BY DEFAULT AS IDENTITY NOT NULL,
|
|
|
|
|
site_id INT NOT NULL,
|
|
|
|
|
number VARCHAR(20) NOT NULL,
|
|
|
|
|
counterparty_type VARCHAR(12) NOT NULL,
|
|
|
|
|
client_id INT DEFAULT NULL,
|
|
|
|
|
supplier_id INT DEFAULT NULL,
|
|
|
|
|
other_label VARCHAR(255) DEFAULT NULL,
|
|
|
|
|
immatriculation VARCHAR(20) NOT NULL,
|
|
|
|
|
plate_free_format BOOLEAN DEFAULT FALSE NOT NULL,
|
|
|
|
|
empty_date TIMESTAMP(0) WITHOUT TIME ZONE DEFAULT NULL,
|
|
|
|
|
empty_weight INT DEFAULT NULL,
|
|
|
|
|
empty_dsd INT DEFAULT NULL,
|
|
|
|
|
empty_mode VARCHAR(8) DEFAULT NULL,
|
|
|
|
|
empty_manual_number VARCHAR(50) DEFAULT NULL,
|
|
|
|
|
full_date TIMESTAMP(0) WITHOUT TIME ZONE DEFAULT NULL,
|
|
|
|
|
full_weight INT DEFAULT NULL,
|
|
|
|
|
full_dsd INT DEFAULT NULL,
|
|
|
|
|
full_mode VARCHAR(8) DEFAULT NULL,
|
|
|
|
|
full_manual_number VARCHAR(50) DEFAULT NULL,
|
|
|
|
|
net_weight INT DEFAULT NULL,
|
|
|
|
|
deleted_at TIMESTAMP(0) WITHOUT TIME ZONE DEFAULT NULL,
|
|
|
|
|
created_at TIMESTAMP(0) WITHOUT TIME ZONE NOT NULL,
|
|
|
|
|
updated_at TIMESTAMP(0) WITHOUT TIME ZONE NOT NULL,
|
|
|
|
|
created_by INT DEFAULT NULL,
|
|
|
|
|
updated_by INT DEFAULT NULL,
|
|
|
|
|
PRIMARY KEY (id),
|
|
|
|
|
CONSTRAINT chk_wt_counterparty_type
|
|
|
|
|
CHECK (counterparty_type IN ('CLIENT', 'FOURNISSEUR', 'AUTRE')),
|
|
|
|
|
CONSTRAINT chk_wt_empty_mode
|
|
|
|
|
CHECK (empty_mode IS NULL OR empty_mode IN ('AUTO', 'MANUAL')),
|
|
|
|
|
CONSTRAINT chk_wt_full_mode
|
|
|
|
|
CHECK (full_mode IS NULL OR full_mode IN ('AUTO', 'MANUAL')),
|
|
|
|
|
CONSTRAINT chk_wt_client_branch
|
|
|
|
|
CHECK (counterparty_type <> 'CLIENT' OR (client_id IS NOT NULL AND supplier_id IS NULL AND other_label IS NULL)),
|
|
|
|
|
CONSTRAINT chk_wt_supplier_branch
|
|
|
|
|
CHECK (counterparty_type <> 'FOURNISSEUR' OR (supplier_id IS NOT NULL AND client_id IS NULL AND other_label IS NULL)),
|
|
|
|
|
CONSTRAINT chk_wt_other_branch
|
|
|
|
|
CHECK (counterparty_type <> 'AUTRE' OR (other_label IS NOT NULL AND client_id IS NULL AND supplier_id IS NULL)),
|
|
|
|
|
CONSTRAINT fk_wt_site
|
|
|
|
|
FOREIGN KEY (site_id) REFERENCES site (id) ON DELETE RESTRICT,
|
|
|
|
|
CONSTRAINT fk_wt_client
|
|
|
|
|
FOREIGN KEY (client_id) REFERENCES client (id) ON DELETE RESTRICT,
|
|
|
|
|
CONSTRAINT fk_wt_supplier
|
|
|
|
|
FOREIGN KEY (supplier_id) REFERENCES supplier (id) ON DELETE RESTRICT,
|
|
|
|
|
CONSTRAINT fk_wt_created_by
|
|
|
|
|
FOREIGN KEY (created_by) REFERENCES "user" (id) ON DELETE SET NULL,
|
|
|
|
|
CONSTRAINT fk_wt_updated_by
|
|
|
|
|
FOREIGN KEY (updated_by) REFERENCES "user" (id) ON DELETE SET NULL
|
|
|
|
|
)
|
|
|
|
|
SQL);
|
|
|
|
|
|
|
|
|
|
$this->addSql('CREATE UNIQUE INDEX uq_weighing_ticket_number ON weighing_ticket (site_id, number)');
|
|
|
|
|
$this->addSql('CREATE INDEX idx_wt_site ON weighing_ticket (site_id)');
|
|
|
|
|
$this->addSql('CREATE INDEX idx_wt_client ON weighing_ticket (client_id)');
|
|
|
|
|
$this->addSql('CREATE INDEX idx_wt_supplier ON weighing_ticket (supplier_id)');
|
|
|
|
|
$this->addSql('CREATE INDEX idx_wt_deleted_at ON weighing_ticket (deleted_at)');
|
|
|
|
|
$this->addSql('CREATE INDEX idx_wt_created_by ON weighing_ticket (created_by)');
|
|
|
|
|
$this->addSql('CREATE INDEX idx_wt_updated_by ON weighing_ticket (updated_by)');
|
|
|
|
|
|
|
|
|
|
$this->comment('weighing_ticket', '_table', 'Tickets de pesee (M5 Logistique) — pesee a vide + a plein au pont bascule, contrepartie Client/Fournisseur/Autre. Cloisonne par site courant.');
|
|
|
|
|
$this->comment('weighing_ticket', 'id', 'Identifiant interne auto-incremente.');
|
|
|
|
|
$this->comment('weighing_ticket', 'site_id', 'Site du pont bascule (cloisonnement § 2.3). FK -> site.id, ON DELETE RESTRICT. Renseigne serveur depuis le site courant, immuable (RG-5.09).');
|
|
|
|
|
$this->comment('weighing_ticket', 'number', 'Numero {siteCode}-TP-{NNNN}, unique par site (uq_weighing_ticket_number), immuable. Sequence weighing_ticket_counter (RG-5.02).');
|
|
|
|
|
$this->comment('weighing_ticket', 'counterparty_type', 'Contrepartie : CLIENT, FOURNISSEUR ou AUTRE (chk_wt_counterparty_type, RG-5.03). Pilote l obligation client_id / supplier_id / other_label.');
|
|
|
|
|
$this->comment('weighing_ticket', 'client_id', 'Branche CLIENT (RG-5.03) : client concerne. FK -> client.id, ON DELETE RESTRICT. Requis ssi counterparty_type = CLIENT, nul sinon (chk_wt_client_branch).');
|
|
|
|
|
$this->comment('weighing_ticket', 'supplier_id', 'Branche FOURNISSEUR (RG-5.03) : fournisseur concerne. FK -> supplier.id, ON DELETE RESTRICT. Requis ssi counterparty_type = FOURNISSEUR (chk_wt_supplier_branch).');
|
|
|
|
|
$this->comment('weighing_ticket', 'other_label', 'Branche AUTRE (RG-5.03) : libelle libre de la contrepartie. Requis ssi counterparty_type = AUTRE, nul sinon (chk_wt_other_branch).');
|
|
|
|
|
$this->comment('weighing_ticket', 'immatriculation', 'Plaque du vehicule, partagee entre pesee vide et plein. Masque XX-000-XX sauf si plate_free_format (RG-5.01). Normalisee serveur (trim/UPPER).');
|
|
|
|
|
$this->comment('weighing_ticket', 'plate_free_format', '« Tout format » : desactive le masque XX-000-XX de l immatriculation (RG-5.01). Partage entre les 2 formulaires. Faux par defaut.');
|
|
|
|
|
$this->comment('weighing_ticket', 'empty_date', 'Date/heure de la pesee a vide (tare). Defaut jour courant cote front (RG-5.07). Null tant que la pesee vide n est pas faite.');
|
|
|
|
|
$this->comment('weighing_ticket', 'empty_weight', 'Poids a vide (tare) en kg — readonly UI, rempli par la pesee (RG-5.07).');
|
|
|
|
|
$this->comment('weighing_ticket', 'empty_dsd', 'Compteur DSD du pont a la pesee a vide. AUTO = valeur du pont ; MANUAL = dernier dsd du site + 1 (RG-5.04).');
|
|
|
|
|
$this->comment('weighing_ticket', 'empty_mode', 'Mode de la pesee a vide : AUTO (pont bascule) ou MANUAL (saisie) — chk_wt_empty_mode (RG-5.06).');
|
|
|
|
|
$this->comment('weighing_ticket', 'empty_manual_number', 'Numero de pesee saisi en pesee manuelle (distinct du DSD) — formulaire a vide (RG-5.04).');
|
|
|
|
|
$this->comment('weighing_ticket', 'full_date', 'Date/heure de la pesee a plein (brut). Null tant que la pesee plein n est pas faite.');
|
|
|
|
|
$this->comment('weighing_ticket', 'full_weight', 'Poids a plein (brut) en kg — readonly UI, rempli par la pesee (RG-5.07).');
|
|
|
|
|
$this->comment('weighing_ticket', 'full_dsd', 'Compteur DSD du pont a la pesee a plein. AUTO = valeur du pont ; MANUAL = dernier dsd du site + 1 (RG-5.04).');
|
|
|
|
|
$this->comment('weighing_ticket', 'full_mode', 'Mode de la pesee a plein : AUTO (pont bascule) ou MANUAL (saisie) — chk_wt_full_mode (RG-5.06).');
|
|
|
|
|
$this->comment('weighing_ticket', 'full_manual_number', 'Numero de pesee saisi en pesee manuelle (distinct du DSD) — formulaire a plein (RG-5.04).');
|
|
|
|
|
$this->comment('weighing_ticket', 'net_weight', 'Poids net = full_weight - empty_weight (kg), calcule serveur (RG-5.05). Null si une pesee manque. Colonne Poids de la liste.');
|
|
|
|
|
$this->comment('weighing_ticket', 'deleted_at', 'Horodatage du soft-delete technique — prepare mais non expose par l API au M5 (§ 2.13). Null = ligne active.');
|
|
|
|
|
$this->addTimestampableBlamableComments('weighing_ticket');
|
|
|
|
|
}
|
|
|
|
|
|
|
|
|
|
// =================================================================
|
|
|
|
|
// Helpers (identiques au M4 Version20260615150000)
|
|
|
|
|
// =================================================================
|
|
|
|
|
|
|
|
|
|
/**
|
|
|
|
|
* Pose les 4 commentaires standardises Timestampable/Blamable sur une table,
|
|
|
|
|
* en reutilisant le catalogue partage (source unique, ERP-67).
|
|
|
|
|
*/
|
|
|
|
|
private function addTimestampableBlamableComments(string $table): void
|
|
|
|
|
{
|
|
|
|
|
foreach (ColumnCommentsCatalog::timestampableBlamableComments() as $column => $description) {
|
|
|
|
|
$this->comment($table, $column, $description);
|
|
|
|
|
}
|
|
|
|
|
}
|
|
|
|
|
|
|
|
|
|
/**
|
|
|
|
|
* Emet un `COMMENT ON TABLE` (colonne speciale `_table`) ou `COMMENT ON COLUMN`
|
|
|
|
|
* en dollar-quoting Postgres ($_$...$_$) pour eviter tout echappement d apostrophe.
|
|
|
|
|
*/
|
|
|
|
|
private function comment(string $table, string $column, string $description): void
|
|
|
|
|
{
|
|
|
|
|
$quotedTable = '"'.str_replace('"', '""', $table).'"';
|
|
|
|
|
|
|
|
|
|
if ('_table' === $column) {
|
|
|
|
|
$this->addSql(sprintf('COMMENT ON TABLE %s IS $_$%s$_$', $quotedTable, $description));
|
|
|
|
|
|
|
|
|
|
return;
|
|
|
|
|
}
|
|
|
|
|
|
|
|
|
|
$this->addSql(sprintf(
|
|
|
|
|
'COMMENT ON COLUMN %s.%s IS $_$%s$_$',
|
|
|
|
|
$quotedTable,
|
|
|
|
|
'"'.str_replace('"', '""', $column).'"',
|
|
|
|
|
$description,
|
|
|
|
|
));
|
|
|
|
|
}
|
|
|
|
|
}
|
Matthieu