[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 =-- 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