Structure de base de données implémentée
Statut : ✅ Entièrement implémenté le 18 décembre 2025
Approche : Système simplifié avec réutilisation de la table orders existante
1. licence_regio (NIEUW)
CREATE TABLE [dbo].[licence_regio](
[regioID] INT IDENTITY(1,1) PRIMARY KEY,
[regio_code] VARCHAR(20) UNIQUE NOT NULL,
[regioNL] NVARCHAR(100) NOT NULL,
[regioFR] NVARCHAR(100) NOT NULL,
[regioEN] NVARCHAR(100) NULL,
[website] VARCHAR(100) NOT NULL DEFAULT 'syndi.be',
[active] BIT NOT NULL DEFAULT 1,
[sort_order] INT NOT NULL DEFAULT 0,
[created_date] DATETIME NOT NULL DEFAULT GETDATE(),
[modified_date] DATETIME NOT NULL DEFAULT GETDATE()
)
Données d'exemple (13 régions) :
- BE - België / Belgique
- ANT - Antwerpen / Anvers
- LIM - Limburg / Limbourg
- OVL - Oost-Vlaanderen / Flandre-Orientale
- WVL - West-Vlaanderen / Flandre-Occidentale
- VBR - Vlaams-Brabant / Brabant flamand
- LUI - Luik / Liège
- LUX - Luxemburg / Luxembourg
- NAM - Namen / Namur
- HEN - Henegouwen / Hainaut
- WBR - Waals-Brabant / Brabant wallon
- BHG - Région de Bruxelles-Capitale
- NL - Pays-Bas (inactif - futur)
2. licence_products (NIEUW)
CREATE TABLE [dbo].[licence_products](
[listID] INT IDENTITY(1,1) PRIMARY KEY,
[product_code] VARCHAR(50) UNIQUE NOT NULL,
[licence_type] VARCHAR(20) NOT NULL, -- 'master', 'executive', 'excel'
[regioID] INT NULL, -- FK, NULL = all regions
[duration_months] INT NOT NULL,
[titelNL] NVARCHAR(200) NOT NULL,
[titelFR] NVARCHAR(200) NOT NULL,
[beschrijvingNL] NVARCHAR(MAX) NULL,
[beschrijvingFR] NVARCHAR(MAX) NULL,
[price_excl_btw] MONEY NOT NULL,
[promo_price_excl_btw] MONEY NULL,
[max_downloads] INT NULL, -- NULL = unlimited
[active] BIT NOT NULL DEFAULT 1,
[visible] BIT NOT NULL DEFAULT 1,
[created_date] DATETIME NOT NULL DEFAULT GETDATE(),
[modified_date] DATETIME NOT NULL DEFAULT GETDATE(),
CONSTRAINT [FK_licence_products_regio] FOREIGN KEY ([regioID])
REFERENCES [dbo].[licence_regio]([regioID])
)
Produits d'exemple (6 produits) :
- MASTER-BE-12 - Master Belgique 1 an (€1299)
- MASTER-BE-24 - Master Belgique 2 ans (€2468, promo €2340)
- MASTER-ANT-12 - Master Anvers 1 an (€499)
- EXEC-LUI-12 - Executive Liège 1 an (€299)
- EXCEL-BE-12 - Téléchargements Excel Belgique 1 an (€399, max 100 téléchargements)
- EXCEL-BE-24 - Téléchargements Excel Belgique 2 ans (€699, illimité)
3. orders (EXISTANT - ÉTENDU)
-- Nouvelles colonnes ajoutées à la table orders existante :
ALTER TABLE [dbo].[orders] ADD [listID] INT NULL
ALTER TABLE [dbo].[orders] ADD [regioID] INT NULL
ALTER TABLE [dbo].[orders] ADD [duration_months] INT NULL
ALTER TABLE [dbo].[orders] ADD [start_date] DATE NULL
ALTER TABLE [dbo].[orders] ADD [download_count] INT NOT NULL DEFAULT 0
ALTER TABLE [dbo].[orders] ADD [max_downloads] INT NULL
-- Foreign keys:
CONSTRAINT [FK_orders_licence_products] FOREIGN KEY ([listID])
CONSTRAINT [FK_orders_licence_regio] FOREIGN KEY ([regioID])
Champs existants réutilisés :
orderID - Primary keyclientID - Lien vers le clientinvoiceID - NULL = non payé, NOT NULL = payésubscription - Type de licence ('master', 'executive', 'excel')expirationdate - Date de fin (calculée : start_date + duration_months)VATpct - Pourcentage de TVA (0 ou 21)payment - Statut de paiement ('openstaand', 'mollie', 'factuur')total - Montant total TTC
4. Outils
View: vw_active_licences
Vue d'ensemble de toutes les licences actives avec calcul du statut.
Procédures stockées :
sp_check_licence_access - Vérifier si le client a accès à une régionsp_get_client_licences - Récupérer toutes les licences d'un clientsp_increment_download - Incrémenter le compteur de téléchargements avec contrôle de limite
Décisions importantes
Pourquoi une approche simplifiée ?
- ✅ Compatible rétro : Les commandes existantes et le système de facturation continuent à fonctionner
- ✅ Pas de migration de données : Les anciennes commandes restent dans la même table
- ✅ Petit volume : ~100 commandes/an, pas besoin d'overkill
- ✅ Maintenance simple : Seulement 2 nouvelles tables + 6 colonnes
Logique des licences actives
-- Une licence est active si :
WHERE invoiceID IS NOT NULL -- Payé
AND expirationdate >= GETDATE() -- Non expiré
AND listID IS NOT NULL -- Nouvelle commande de licence
-- En cas de licences en doublon : prendre celle qui expire le plus tard
ORDER BY expirationdate DESC
Notes d'implémentation (18 décembre 2025)
Détails importants d'implémentation
Database: kbo (SQL Server)
Scripts exécutés : licence_system_setup.sql
Statut : Totalement opérationnel et testé
Modifications de la table orders existante :
listID INT NULL - Lien vers le catalogue de produits (licence_products)regioID INT NULL - Lien vers la région (licence_regio)duration_months INT NULL - Durée en mois (12, 24, 36, 60)start_date DATE NULL - Date de début de la licence (par défaut = orderdate)download_count INT DEFAULT 0 - Compteur pour les téléchargements Excelmax_downloads INT NULL - Limite de téléchargements (NULL = illimité)
Pourquoi des valeurs NULL ? Les anciennes commandes (avant le système de licences) n'ont pas ces champs. Cela rend le système compatible rétro.
Champs existants réutilisés :
subscription VARCHAR(50) - Utilisé pour licence_type ('master', 'executive', 'excel')invoiceID INT - NULL = non payé, NOT NULL = payé et actifexpirationdate DATETIME - Calculé comme start_date + duration_monthsVATpct INT - Toujours 0 ou 21 (B2B déterminé lors de la création de la commande)payment VARCHAR(50) - 'openstaand', 'mollie', 'factuur', 'bank'
Référence rapide : requêtes fréquemment utilisées
1. Vérifier si le client a accès à une région
-- Via procédure stockée (recommandé)
EXEC sp_check_licence_access
@clientID = 123,
@regioID = 2; -- ANT
-- Direct query
SELECT TOP 1 1
FROM orders
WHERE clientID = @clientID
AND regioID = @regioID
AND subscription IN ('master', 'executive')
AND invoiceID IS NOT NULL
AND expirationdate >= GETDATE()
ORDER BY expirationdate DESC;
2. Récupérer toutes les licences actives d'un client
-- Via stored procedure
EXEC sp_get_client_licences @clientID = 123;
-- Via view
SELECT * FROM vw_active_licences
WHERE clientID = 123
AND status = 'active'
ORDER BY end_date DESC;
3. Vérifier et incrémenter la limite de téléchargements
DECLARE @errorMsg NVARCHAR(500);
EXEC sp_increment_download
@orderID = 456,
@errorMessage = @errorMsg OUTPUT;
IF @errorMsg IS NOT NULL
PRINT 'Error: ' + @errorMsg;
ELSE
PRINT 'Téléchargement autorisé';
4. Créer une nouvelle commande (exemple PHP)
// Récupérer le produit depuis le catalogue
$sql = "SELECT * FROM licence_products WHERE product_code = ?";
$stmt = sqlsrv_query($conn, $sql, ['MASTER-ANT-12']);
$product = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC);
// Calculer les dates
$startDate = date('Y-m-d');
$durationMonths = $product['duration_months'];
$endDate = date('Y-m-d', strtotime("+{$durationMonths} months"));
// Déterminer la TVA (0 à l'étranger, 21 pour la Belgique)
$vatPct = validateKBO($vatNumber) ? 21 : 0;
$total = $product['price_excl_btw'] * (1 + $vatPct/100);
// Insertion de la commande
$sql = "INSERT INTO orders (
userID, clientID, listID, regioID, subscription,
duration_months, start_date, expirationdate,
orderdate, VATpct, total, product, payment,
max_downloads, quantiy, credits
) VALUES (?, ?, ?, ?, ?, ?, ?, ?, GETDATE(), ?, ?, ?, 'openstaand', ?, 1, 0)";
$params = [
$userID, // ID de l'utilisateur qui commande
$clientID,
$product['listID'],
$product['regioID'],
$product['licence_type'],
$durationMonths,
$startDate,
$endDate,
$vatPct,
$total,
$product['titelNL'],
$product['max_downloads']
];
sqlsrv_query($conn, $sql, $params);
Gestion des produits
Ajouter un nouveau produit
INSERT INTO licence_products (
product_code, licence_type, regioID, duration_months,
titelNL, titelFR, beschrijvingNL, beschrijvingFR,
price_excl_btw, promo_price_excl_btw, max_downloads
) VALUES (
'MASTER-OVL-12', -- Code unique
'master', -- Type
4, -- Flandre-Orientale (depuis licence_regio)
12, -- 1 an
'Master Flandre-Orientale 1 an',
'Master Flandre-Orientale 1 an',
'Accès complet à toutes les ACP...',
'Accès complet à toutes les ACP...',
499.00, -- Prix HT
NULL, -- Pas de promo
NULL -- Téléchargements illimités
);
Modifier un prix
UPDATE licence_products
SET price_excl_btw = 549.00,
promo_price_excl_btw = 499.00,
modified_date = GETDATE()
WHERE product_code = 'MASTER-ANT-12';
Désactiver un produit (ne pas supprimer !)
UPDATE licence_products
SET active = 0, visible = 0
WHERE product_code = 'OLD-PRODUCT-12';
Dépannage & questions fréquentes
Q : Les anciennes commandes ne fonctionnent plus ?
R : Les anciennes commandes (avant le système de licences) ont listID = NULL. Vérifiez toujours :
WHERE (listID IS NOT NULL OR subscription IS NOT NULL)
Q : Le client a une licence en double pour la même région ?
R : Prenez toujours celle qui expire le plus tard avec ORDER BY expirationdate DESC. Les procédures stockées le font automatiquement.
Q : La limite de téléchargement ne fonctionne pas ?
R : Vérifiez que max_downloads est enregistré lors de la création de la commande (snapshot du produit). NULL = illimité.
Q : Le calcul de la TVA n'est pas correct ?
R : La TVA est déterminée lors de la création de la commande (pas au paiement). B2B étranger = 0%, B2B belge = 21%. Vérifiez la validation KBO.
Q : Comment renouveler une licence ?
R : Créez une nouvelle commande avec start_date = ancienne expirationdate. Les deux commandes restent dans la table, la plus longue prévaut.
Q : Puis-je supprimer des produits ?
R : NON ! Ne jamais supprimer (des commandes existantes y font référence). Utilisez active=0 et visible=0 au lieu de DELETE.
Checklist de tests
Avant mise en production :
- ☐ Tester la création de commande pour chaque type de produit
- ☐ Tester le traitement des paiements (mise à jour invoiceID)
- ☐ Tester le contrôle d'accès par région
- ☐ Tester la limite de téléchargement (avec et sans limite)
- ☐ Tester une licence expirée (expirationdate dans le passé)
- ☐ Tester les licences en double (plusieurs pour la même région)
- ☐ Tester le calcul de la TVA (0% et 21%)
- ☐ Tester le multilinguisme (titres produits NL/FR)
- ☐ Tester la compatibilité des anciennes commandes (listID = NULL)
- ☐ Test de performance (requête 1000+ orders < 100ms)
Chemin de migration (futur)
Pour les clients existants avec l'ancien champ subscription :
-- Optionnel : convertir d'anciennes commandes vers le nouveau système
-- À exécuter uniquement si vous souhaitez migrer les anciennes données !
UPDATE orders
SET listID = (
SELECT TOP 1 listID
FROM licence_products
WHERE licence_type = orders.subscription
AND duration_months = 12
),
regioID = (SELECT regioID FROM licence_regio WHERE regio_code = 'BE'),
duration_months = 12,
start_date = orderdate,
download_count = 0
WHERE listID IS NULL
AND subscription IN ('master', 'executive')
AND invoiceID IS NOT NULL;
Avertissement : Testez d'abord ceci sur la base de données de développement !
Optimisation des performances
-- Index pour des requêtes rapides (à ajouter si nécessaire)
CREATE NONCLUSTERED INDEX IX_orders_active_licences
ON orders (clientID, invoiceID, expirationdate, listID)
INCLUDE (subscription, regioID);
CREATE NONCLUSTERED INDEX IX_orders_region_lookup
ON orders (regioID, subscription, expirationdate)
WHERE invoiceID IS NOT NULL AND listID IS NOT NULL;
CREATE NONCLUSTERED INDEX IX_products_active
ON licence_products (active, visible, licence_type)
INCLUDE (product_code, titelNL, price_excl_btw);