[SQL Server] Traitement de Tracking
-- ======================================================================
-- = Activer le Tracking au niveau de la BD MA_BASE
-- ======================================================================
USE [master]
GO
ALTER DATABASE [MA_BASE] SET CHANGE_TRACKING = ON
GO
-- ======================================================================
-- = Activer le Tracking au niveau de la table Facture
-- ======================================================================
USE [MA_BASE]
GO
ALTER TABLE [dbo].[ Facture] ENABLE CHANGE_TRACKING
GO
-- ======================================================================
-- = Création de la table pour sauvegarder les nouvelles données trackées
-- ======================================================================
CREATE TABLE [dbo].[SAS_List_Facture_To_Sync](
[Num_Fact] [bigint] NOT NULL
) ON [PRIMARY]
-- ======================================================================
-- = Création de la table pour sauvegarder les dernières version de Tracking
-- ======================================================================
CREATE TABLE [dbo].[SAS_Ancre](
[PAYS_ABRG] [char](2) NOT NULL,
[SYNC_INITIALIZED] [bigint] NOT NULL,
[SYNC_LAST_RECEIVED_ANCHOR] [bigint] NULL,
CONSTRAINT [PK_SAS_Ancre] PRIMARY KEY CLUSTERED
(
[PAYS_ABRG] ASC
) ON [PRIMARY]
) ON [PRIMARY]
-- ======================================================================
-- = Création de la vue pour récupérer la version de tracking
-- ======================================================================
CREATE VIEW [dbo].[vw_change_tracking_current_version]
AS
SELECT Change_tracking_current_version() AS sync_new_received_anchor
CREATE PROCEDURE [dbo].[SAS_Prepare_Facture_To_Sync]
@ManuelSync smallint,
@CommercialDate NVARCHAR(10)=NULL,
@RestaurantCode int
AS
-- ======================================================================
-- = Preparer les Facture a synchroniser vers les tables SAS
-- = Si @ManuelSync = 1 alors en fonction de la date @CommercialDate
-- = Si @ManuelSync = 0 alors en fonction du CHANGETABLE
-- ======================================================================
DECLARE @sync_initialized BIGINT = NULL
DECLARE @sync_last_received_anchor BIGINT
DECLARE @sync_new_received_anchor BIGINT = NULL
DECLARE @PaysAbrev Char(2)
DECLARE @FiscalStartDate DATE
DECLARE @FiscalEndDate DATE
DECLARE @DateDebutRechAuto DATE
SET @PaysAbrev = 'TN'
SET @DateDebutRechAuto = DateAdd( d, -7, GETDATE())
IF ISDATE(@CommercialDate)<>1
BEGIN
SELECT @FiscalStartDate = DateAdd( d, -1, GETDATE())
END
ELSE
BEGIN
SELECT @FiscalStartDate = @CommercialDate
END
SELECT @FiscalEndDate = DateAdd( d, 1, @FiscalStartDate )
IF @ManuelSync = 0 -- mode automatique
BEGIN
PRINT 'Automatique'
--récuperer la nouvelle valeur d'ancrage
SELECT @sync_initialized = sync_initialized, @sync_last_received_anchor = sync_last_received_anchor
FROM SAS_Ancre
WHERE PAYS_ABRG = @PaysAbrev
--récupérer la valeur du change_tracking_current_version
SELECT @sync_new_received_anchor = sync_new_received_anchor
FROM vw_change_tracking_current_version
IF @sync_initialized = 0
BEGIN
INSERT INTO SAS_List_Facture_To_Sync( Num_Fact )
SELECT FAC1.Num_Fact
FROM Facture FAC1
LEFT OUTER JOIN CHANGETABLE(CHANGES Facture, NULL ) FAC2 ON FAC2.Num_Fact = FAC1.Num_Fact
WHERE FAC1.Date_Fact >= @DateDebutRechAuto
END
ELSE
BEGIN
INSERT INTO SAS_List_Facture_To_Sync( Num_Fact )
SELECT FAC1.Num_Fact
FROM Facture FAC1
JOIN CHANGETABLE( CHANGES Facture, @sync_last_received_anchor ) FAC2 ON FAC2.Num_Fact = FAC1.Num_Fact
WHERE (FAC2.SYS_CHANGE_OPERATION = 'I'
AND FAC2.SYS_CHANGE_CREATION_VERSION <= @sync_new_received_anchor)
END
--Sauvegarder lsync_last_received_anchor dans la table Sas_Ancre
IF @@ERROR =0
BEGIN
PRINT 'No error'
UPDATE SAS_Ancre
SET sync_initialized = 1, sync_last_received_anchor = @sync_new_received_anchor
WHERE PAYS_ABRG = @PaysAbrev
END
END
ELSE
BEGIN -- mode manuel
PRINT 'Manuel'
IF @RestaurantCode = 0
INSERT INTO SAS_List_Facture_To_Sync( Num_Fact )
SELECT DISTINCT Num_Fact
FROM Facture
WHERE ( Date_Fact >= @FiscalStartDate AND Date_Fact < @FiscalEndDate )
ELSE
INSERT INTO SAS_List_Facture_To_Sync( Num_Fact )
SELECT DISTINCT Num_Fact
FROM Facture
WHERE Date_Fact BETWEEN @FiscalStartDate AND @FiscalEndDate
AND RestaurantCode = @RestaurantCode
END