IF DB_ID('RapprochementBIAT') IS NULL
CREATE DATABASE RapprochementBIAT;
GO
USE RapprochementBIAT;
GO
-------------------------------------------------------------------------------
-- 1. TABLE DES BANQUES
-------------------------------------------------------------------------------
IF OBJECT_ID('dbo.Banques','U') IS NULL
CREATE TABLE dbo.Banques (
bank_id NVARCHAR(50) NOT NULL PRIMARY KEY,
nom NVARCHAR(100) NOT NULL,
compte NVARCHAR(100) NULL,
excel_path NVARCHAR(500) NULL,
feuille_gl NVARCHAR(50) DEFAULT 'GL',
feuille_bq NVARCHAR(50) DEFAULT 'Relevé',
feuille_export NVARCHAR(50) DEFAULT 'DataExport',
actif BIT DEFAULT 1,
created_at DATETIME DEFAULT GETDATE(),
updated_at DATETIME DEFAULT GETDATE()
);
GO
MERGE dbo.Banques AS target
USING (VALUES
('jerba', 'BIAT DJERBA', '33 10 00547 8 67', 'Jerba/RapprochementBIAT.xlsx', 'GL', 'Relevé', 'DataExport', 1),
('hammamet', 'BIAT HAMMAMET', '01 10 53954 1 32', 'Hammamet/Rapprochement Biat hammamet.xlsx', 'GL', 'Relevé', 'DataExport', 1),
('jendouba', 'BIAT JENDOUBA', '80-30-00013/8', 'jendouba/Rapprochement BiatJendouba.xlsx', 'GL', 'Relevé', 'DataExport', 1),
('biat_ba', 'BIAT BEN AROUS', '73-30-00003/6', 'Benarous/Rapprochement Biat Ben aoust.xlsx','GL', 'Relevé', 'DataExport', 1),
('bh_ba', 'BH BEN AROUS', '008 00 8101700 1635 49', 'BH/Rapprochement BH Ben Arous.xlsx', 'GL', 'Relevé', 'DataExport', 1),
('att_meg', 'ATTIJARI MEGRINE', '00079-0071009542-2','Attijari Megrine/Rapprochement Attijari.xlsx', 'GL', 'Relevé', 'DataExportN', 1),
('att_met', 'ATTIJARI METLAOUI', '00079-4046001014-4','Attijari Megrine/Rapprochement Attijari.xlsx', 'GL', 'Relevé', 'DataExport2', 1)
) AS source (bank_id, nom, compte, excel_path, feuille_gl, feuille_bq, feuille_export, actif)
ON target.bank_id = source.bank_id
WHEN MATCHED THEN
UPDATE SET nom = source.nom, compte = source.compte, excel_path = source.excel_path,
feuille_gl = source.feuille_gl, feuille_bq = source.feuille_bq,
feuille_export = source.feuille_export, updated_at = GETDATE()
WHEN NOT MATCHED THEN
INSERT (bank_id, nom, compte, excel_path, feuille_gl, feuille_bq, feuille_export, actif)
VALUES (source.bank_id, source.nom, source.compte, source.excel_path,
source.feuille_gl, source.feuille_bq, source.feuille_export, source.actif);
GO
-------------------------------------------------------------------------------
-- 2. TABLE GL (équivalent à la feuille GL de chaque Excel)
-------------------------------------------------------------------------------
IF OBJECT_ID('dbo.GL','U') IS NULL
CREATE TABLE dbo.GL (
id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
bank_id NVARCHAR(50) NOT NULL FOREIGN KEY REFERENCES dbo.Banques(bank_id),
type_operation NVARCHAR(100) NULL,
montant DECIMAL(18,3) NULL,
date_operation DATE NULL,
reference NVARCHAR(100) NULL,
libelle NVARCHAR(255) NULL,
occurrence INT DEFAULT 1,
cle_rapprochement NVARCHAR(100) NULL,
periode NVARCHAR(10) NULL,
import_batch NVARCHAR(50) NULL,
created_at DATETIME DEFAULT GETDATE(),
INDEX IX_GL_bank_periode (bank_id, periode),
INDEX IX_GL_cle (cle_rapprochement),
INDEX IX_GL_date (date_operation)
);
GO
-------------------------------------------------------------------------------
-- 3. TABLE RELEVE_BANQUE (équivalent à la feuille Relevé)
-------------------------------------------------------------------------------
IF OBJECT_ID('dbo.ReleveBanque','U') IS NULL
CREATE TABLE dbo.ReleveBanque (
id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
bank_id NVARCHAR(50) NOT NULL FOREIGN KEY REFERENCES dbo.Banques(bank_id),
type_operation NVARCHAR(100) NULL,
montant DECIMAL(18,3) NULL,
date_operation DATE NULL,
reference NVARCHAR(100) NULL,
libelle NVARCHAR(500) NULL,
rang INT NULL,
cle_rapprochement NVARCHAR(100) NULL,
periode NVARCHAR(10) NULL,
import_batch NVARCHAR(50) NULL,
created_at DATETIME DEFAULT GETDATE(),
INDEX IX_RB_bank_periode (bank_id, periode),
INDEX IX_RB_cle (cle_rapprochement),
INDEX IX_RB_date (date_operation)
);
GO
-------------------------------------------------------------------------------
-- 4. TABLE ECRITURES_RECONCILIEES
-------------------------------------------------------------------------------
IF OBJECT_ID('dbo.EcrituresReconciliation','U') IS NULL
CREATE TABLE dbo.EcrituresReconciliation (
id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
bank_id NVARCHAR(50) NOT NULL FOREIGN KEY REFERENCES dbo.Banques(bank_id),
-- Côté GL
type_gl NVARCHAR(100) NULL,
montant_gl DECIMAL(18,3) NULL,
date_gl DATE NULL,
num_gl NVARCHAR(100) NULL,
des_gl NVARCHAR(500) NULL,
cle_gl NVARCHAR(100) NULL,
-- Côté Banque
date_bq DATE NULL,
libelle_bq NVARCHAR(500) NULL,
montant_bq DECIMAL(18,3) NULL,
cle_bq NVARCHAR(100) NULL,
-- État de rapprochement
etat NVARCHAR(50) DEFAULT 'En attente Banque',
ecart DECIMAL(18,3) DEFAULT 0,
-- Métadonnées
source NVARCHAR(50) DEFAULT 'Auto',
is_manual BIT DEFAULT 0,
manual_type NVARCHAR(20) NULL,
groupe_rapprochement NVARCHAR(100) NULL,
import_batch NVARCHAR(50) NULL,
created_at DATETIME DEFAULT GETDATE(),
updated_at DATETIME DEFAULT GETDATE(),
INDEX IX_ER_bank_etat (bank_id, etat),
INDEX IX_ER_cle_gl (cle_gl),
INDEX IX_ER_cle_bq (cle_bq)
);
GO
-------------------------------------------------------------------------------
-- 5. TABLE ECRITURES_MANUELLES
-------------------------------------------------------------------------------
IF OBJECT_ID('dbo.EcrituresManuelles','U') IS NULL
CREATE TABLE dbo.EcrituresManuelles (
id NVARCHAR(50) NOT NULL PRIMARY KEY,
bank_id NVARCHAR(50) NOT NULL FOREIGN KEY REFERENCES dbo.Banques(bank_id),
etat NVARCHAR(50) DEFAULT 'En attente Banque',
source NVARCHAR(50) DEFAULT 'Manuel',
type_op NVARCHAR(20) DEFAULT 'MANUEL',
accdat DATE NULL,
montant_gl DECIMAL(18,3) NULL,
num_gl NVARCHAR(100) NULL,
des_gl NVARCHAR(500) NULL,
bnk_date DATE NULL,
bnk_montant DECIMAL(18,3) NULL,
bnk_cle NVARCHAR(100) NULL,
bnk_libelle NVARCHAR(500) NULL,
ecart DECIMAL(18,3) NULL,
created_at DATETIME DEFAULT GETDATE(),
INDEX IX_EM_bank (bank_id)
);
GO
-------------------------------------------------------------------------------
-- 6. TABLE RAPPROCHEMENTS_MANUELS
-------------------------------------------------------------------------------
IF OBJECT_ID('dbo.RapprochementsManuels','U') IS NULL
CREATE TABLE dbo.RapprochementsManuels (
id_ecriture NVARCHAR(50) NOT NULL,
groupe_rapprochement NVARCHAR(100) NOT NULL,
cote NVARCHAR(10) NOT NULL,
bank_id NVARCHAR(50) NOT NULL FOREIGN KEY REFERENCES dbo.Banques(bank_id),
etat NVARCHAR(50) DEFAULT 'Rapproché',
date_rapprochement DATE DEFAULT GETDATE(),
montant DECIMAL(18,3) NULL,
libelle NVARCHAR(500) NULL,
created_at DATETIME DEFAULT GETDATE(),
PRIMARY KEY (id_ecriture, groupe_rapprochement),
INDEX IX_RM_groupe (groupe_rapprochement),
INDEX IX_RM_bank (bank_id)
);
GO
-------------------------------------------------------------------------------
-- 7. TABLE SOLDES
-------------------------------------------------------------------------------
IF OBJECT_ID('dbo.Soldes','U') IS NULL
CREATE TABLE dbo.Soldes (
bank_id NVARCHAR(50) NOT NULL FOREIGN KEY REFERENCES dbo.Banques(bank_id),
solde_gl DECIMAL(18,3) DEFAULT 0,
solde_bq DECIMAL(18,3) DEFAULT 0,
periode_gl NVARCHAR(10) NULL,
periode_bq NVARCHAR(10) NULL,
updated_at DATETIME DEFAULT GETDATE(),
PRIMARY KEY (bank_id)
);
GO
-------------------------------------------------------------------------------
-- 8. TABLE IMPORT_BATCH
-------------------------------------------------------------------------------
IF OBJECT_ID('dbo.ImportBatch','U') IS NULL
CREATE TABLE dbo.ImportBatch (
batch_id NVARCHAR(50) NOT NULL PRIMARY KEY,
bank_id NVARCHAR(50) NOT NULL FOREIGN KEY REFERENCES dbo.Banques(bank_id),
type_import NVARCHAR(20) NOT NULL, -- 'RELEVE', 'GL', etc.
fichier_source NVARCHAR(500) NULL,
nb_lignes INT DEFAULT 0,
status NVARCHAR(20) DEFAULT 'OK',
message NVARCHAR(MAX) NULL,
created_at DATETIME DEFAULT GETDATE()
);
GO
PRINT 'Schéma appliquée avec succès.';
GO
To embed this project on your website, copy the following code and paste it into your website's HTML: