/*----------------------------------------------------------------------------------*
*SCRIPT D'IMPORTATION DE L'ANCIENNE DB GIFTCARD DANS LA NOUVELLE DB					*
*  _																		  _		*
* /!\ PENSER A DESACTIVER LES TRIGGER AFTER UPDATE SUR LES TABLES STS ET SFS /!\	*	
* ŻŻŻ																		 ŻŻŻ	*
*-----------------------------------------------------------------------------------*/
SET ANSI_WARNINGS OFF;

DECLARE @SEQNRHIST	INT;
DECLARE @BARCODE	VARCHAR(13);
DECLARE @DATE		DATETIME;
DECLARE @ACTION		VARCHAR(1);
DECLARE	@ORIGIN		INT;
DECLARE @DESTTYPE	INT;
DECLARE	@DEST		INT;
DECLARE @VALUE		INT;
DECLARE	@SALETYPE	INT;
DECLARE	@USERTYPE	INT;	

DECLARE @GFT_ID		INT;
DECLARE @STR_ID		INT;
DECLARE @DOS_ID		INT;
DECLARE @DECIMAL_VALUE	DECIMAL(6,2);
DECLARE @TMP		VARCHAR(100);
DECLARE @TRN_TILL	SMALLINT;
DECLARE @TRN_TICKET	SMALLINT;
DECLARE @USERNAME	VARCHAR(50);

DECLARE C CURSOR FOR
		
SELECT
		SEQNRHIST,
		BARCODE,
		DATTIM,
		ACTION,
		ORIGIN,
		DESTTYPE,
		DEST,
		VALUE,
		SALETYPE,
		USERTYPE
FROM
		DBO.T_CHEQUE_ACHAT_HISTORY
ORDER BY
		SeqNrHist,
		Barcode,
		DatTim
		
OPEN C;

FETCH NEXT FROM C INTO @SEQNRHIST, @BARCODE, @DATE, @ACTION, @ORIGIN, @DESTTYPE, @DEST, @VALUE, @SALETYPE, @USERTYPE;

WHILE @@FETCH_STATUS = 0
BEGIN
	--ON RECUPERE L'ID DU GIFT
	EXEC TMP_UP_CREATE_GIFT_IF_NOT_EXISTS @BARCODE, @GFT_ID OUTPUT, @VALUE;
	
	--ON NOTE LA LIGNE EN COURS DE TRAITEMENT POUR IDENTIFIER SI PLANTAGE
	UPDATE DBO.T_CURRENT_LINE_PROCESSING_CLP
	SET
			SEQNRHIST = @SEQNRHIST,
			BARCODE = @BARCODE,
			ACTION = @ACTION
	
	--ON DEFINIT SI L'UTILISATEUR EST NORMAL OU ADMIN
	IF @USERTYPE <> 0
		SET @USERNAME = 'ADMIN';
	ELSE
		SET @USERNAME = 'NORMAL';
	
	--AJOUT DANS LE STOCK DE LA CENTRALE
	IF @ACTION = 'E' 
		INSERT INTO DBO.V_CENTRALE_STOCK_VCS(GFT_ID, CSE_DATE_IN)
		VALUES(@GFT_ID, @DATE);

	--ENVOYE VERS UN MAGASIN
	IF @ACTION = 'S'
	BEGIN
		--ON RECUPERE L'ID DU MAGASIN
		IF @DEST = 0
			SET @STR_ID = 17
		ELSE
			EXEC TMP_UP_GET_STORE_ID @DEST, @STR_ID OUTPUT;
		
		--ON CREE L'ENVOI VERS LE MAGASIN DEPUIS LA CENTRALE
		INSERT INTO DBO.T_SEND_TO_STORE_STS(GFT_ID, STR_ID, STS_DATE, STS_FROM_CENTRALE, STS_FROM_ENTREPOT, STS_PENDING, STS_CREATED_BY)
		VALUES(@GFT_ID, @STR_ID, @DATE, 1, 0, 1, @USERNAME);
	END

	--ARRIVE DANS UN MAGASIN
	IF @ACTION = 'A'
	BEGIN
		--ON RECUPERE L'ID DU MAGASIN
		SET @ORIGIN = @ORIGIN * 10000
		IF @ORIGIN = 0
			SET @STR_ID = 17
		ELSE
			EXEC TMP_UP_GET_STORE_ID @ORIGIN, @STR_ID OUTPUT
	
		--AJOUT DANS LE STOCK DU MAGASIN
		INSERT INTO DBO.V_STORE_STOCK_VSS (GFT_ID, STR_ID, SSE_DATE_IN)
		VALUES(@GFT_ID, @STR_ID, @DATE);
		
		--L'update de l'envoi est fait dans le trigger instead of insert de la vue.
		/*--UPDATE DE L'ENVOI (STS_PENDING = 0)
		WITH T1 (STS_ID, GFT_ID,	STS_DATE, STS_PENDING, STS_MODIFIED_BY, STS_MODIFIED_ON, RANG)
		AS (
			SELECT	
					STS_ID, GFT_ID, STS_DATE, STS_PENDING, STS_MODIFIED_BY, STS_MODIFIED_ON, ROW_NUMBER() OVER(PARTITION BY GFT_ID ORDER BY STS_DATE DESC) AS 'RANG'
			FROM
					DBO.T_SEND_TO_STORE_STS
			WHERE
					GFT_ID = @GFT_ID
				AND 	STR_ID = @STR_ID
				AND	STS_PENDING = 1
				AND	STS_DATE < @DATE
		)
		UPDATE T1
		SET
				STS_PENDING = 0,
				STS_MODIFIED_BY = @USERNAME,
				STS_MODIFIED_ON = @DATE 
		WHERE 
				T1.RANG = 1*/

	END

	--VENDU EN B2B PAR SSSD
	IF @ACTION = 'V' OR @ACTION = 'Y'
	BEGIN
		--ON RECUPERE L'ID DU DOSSIER
		IF LEFT(@BARCODE,2) = '21' 
			EXEC TMP_UP_CREATE_DOSSIER_IF_NOT_EXISTS @SEQNRHIST, @DEST, @DATE, 'CHQ', @VALUE, @DOS_ID OUTPUT;
		ELSE
			EXEC TMP_UP_CREATE_DOSSIER_IF_NOT_EXISTS @SEQNRHIST, @DEST, @DATE, 'CRD', @VALUE, @DOS_ID OUTPUT;
		
		--ON AJOUTE DANS LA TABLE DE JOINTURE ENTRE GIFT ET DOSSIER
		SET @DECIMAL_VALUE = CAST(@VALUE AS DECIMAL(10,2)) / 100;
		INSERT INTO DBO.TJ_GFT_DOS_JGD(GFT_ID, DOS_ID, JGD_VALUE, JGD_CREATED_BY)
		VALUES(@GFT_ID, @DOS_ID, @DECIMAL_VALUE, @USERNAME)
		
		IF @ACTION = 'V' --ALORS IL FAUT ACTIVER
		INSERT INTO dbo.T_ACTIVATION_ACT (GFT_ID, DOS_ID, ACT_DATE, ACT_CREATED_BY)
		VALUES(@GFT_ID, @DOS_ID, @DATE, @USERNAME);
	END
	
	--VENDU A LA CAISSE (TTR_ID = 0)
	IF @ACTION = 'T'
	BEGIN
		--ON RECUPERE L'ID DU MAGASIN
		SET @ORIGIN = @ORIGIN * 10000
		IF @ORIGIN = 0
			SET @STR_ID = 17
		ELSE
			EXEC TMP_UP_GET_STORE_ID @ORIGIN, @STR_ID OUTPUT;
		
		--ON RECUPERE LA CAISSE ET LE TICKET
		SET @TMP =  RIGHT('000000'+CAST(@DEST AS VARCHAR(6)),6);
		SET @TRN_TILL = CAST(LEFT(@TMP,2) AS SMALLINT);
		SET @TRN_TICKET = CAST(RIGHT(@TMP,4) AS SMALLINT);
		IF @USERTYPE <> 0 --SI L'UTILISATION EST SIMULEE PAR UN ADMIN --> CAISSE ET TICKET = 0
		BEGIN
			SET @TRN_TICKET = 0;
			SET @TRN_TILL = 0;
		END
		
		--ON INSERE LA TRANSACTION
		SET @DECIMAL_VALUE = CAST(@VALUE/100 AS DECIMAL(6,2));
		INSERT INTO DBO.T_TRANSACTION_TRN(	TTR_ID, GFT_ID, STR_ID, TRN_DATE, TRN_TILL, TRN_TICKET, TRN_VALUE, TRN_CREATED_BY)
		VALUES(0, @GFT_ID, @STR_ID, @DATE, @TRN_TILL, @TRN_TICKET, @DECIMAL_VALUE, @USERNAME);
		
		--ON LE RETIRE DU STOCK DU MAGASIN
		UPDATE dbo.V_STORE_STOCK_VSS
		SET
				SSS_DATE_OUT = @DATE
		WHERE
				GFT_ID = @GFT_ID
			AND SSS_DATE_OUT IS NULL
			AND STR_ID = @STR_ID

		
	END
				
	--UTILISE A LA CAISSE (TTR_ID = 1)
	IF @ACTION = 'P'
	BEGIN
		--ON RECUPERE L'ID DU MAGASIN
		SET @ORIGIN = @ORIGIN * 10000
		IF @ORIGIN = 0
			SET @ORIGIN = 17
		ELSE 
			EXEC TMP_UP_GET_STORE_ID @ORIGIN, @STR_ID OUTPUT;
		
		--ON RECUPERE LA CAISSE ET LE TICKET
		IF @USERTYPE <> 0 --SI L'UTILISATION EST SIMULEE PAR UN ADMIN --> CAISSE ET TICKET = 0
		BEGIN
			SET @TRN_TICKET = 0;
			SET @TRN_TILL = 0;
		END
		ELSE
		BEGIN
			SET @TMP =  RIGHT('000000'+CAST(@DEST AS VARCHAR(6)),6);
			SET @TRN_TILL = CAST(LEFT(@TMP,2) AS SMALLINT);
			SET @TRN_TICKET = CAST(RIGHT(@TMP,4) AS SMALLINT);
		END
		
		--ON INSERE LA TRANSACTION
		SET @DECIMAL_VALUE = CAST(@VALUE AS DECIMAL(10,2)) / 100;
		INSERT INTO DBO.T_TRANSACTION_TRN(	TTR_ID, GFT_ID, STR_ID, TRN_DATE, TRN_TILL, TRN_TICKET, TRN_VALUE, TRN_CREATED_BY)
		VALUES(1, @GFT_ID, @STR_ID, @DATE, @TRN_TILL, @TRN_TICKET, @DECIMAL_VALUE, @USERNAME);
	END
	
	--RENVOYE AU DAMIER
	IF @ACTION = 'R'
	BEGIN
		--ON RECUPERE L'ID DU MAGASIN
		SET @ORIGIN = @ORIGIN * 10000
		IF @ORIGIN = 0
			SET @STR_ID = 17
		ELSE 
			EXEC TMP_UP_GET_STORE_ID @ORIGIN, @STR_ID OUTPUT;
		
		--ON CREE L'ENVOI VERS LA CENTRALE DEPUIS LE MAGASIN
		INSERT INTO DBO.T_SEND_FROM_STORE_SFS(	GFT_ID, STR_ID, SFS_DATE, SFS_TO_CENTRALE, SFS_TO_ENTREPOT, SFS_PENDING, SFS_CREATED_BY)
		VALUES(@GFT_ID, @STR_ID, @DATE, 1, 0, 1, @USERNAME);
	END
	
	--RETOURNE EN STOCK
	IF @ACTION = 'B'
	BEGIN
		--ON RECUPERE L'ID DU MAGASIN
		IF @DEST = 0
			SET @STR_ID = 17
		ELSE
			EXEC TMP_UP_GET_STORE_ID @DEST, @STR_ID OUTPUT;
		
		--AJOUT DANS LE STOCK DE LA CENTRALE
		INSERT INTO dbo.V_CENTRALE_STOCK_VCS(GFT_ID, CSE_DATE_IN)
		VALUES(@GFT_ID, @DATE);
		
		--L'update de l'envoi est fait dans le trigger de la vue
		/*--UPDATE DE L'ENVOI (SFS_PENDING = 0)
		WITH T1 (SFS_ID, GFT_ID,	SFS_DATE, SFS_PENDING, SFS_MODIFIED_BY, SFS_MODIFIED_ON, RANG)
		AS (
			SELECT	
					SFS_ID, GFT_ID, SFS_DATE, SFS_PENDING, SFS_MODIFIED_BY, SFS_MODIFIED_ON, ROW_NUMBER() OVER(PARTITION BY GFT_ID ORDER BY SFS_DATE DESC) AS 'RANG'
			FROM
					DBO.T_SEND_FROM_STORE_SFS
			WHERE
					GFT_ID = @GFT_ID
				AND 	STR_ID = @STR_ID
				AND	SFS_PENDING = 1
				AND	SFS_DATE < @DATE
		)
		UPDATE T1
		SET
				SFS_PENDING = 0,
				SFS_MODIFIED_BY = @USERNAME,
				SFS_MODIFIED_ON = @DATE 
		WHERE 
				T1.RANG = 1*/

	END
	
	--DETRUIT
	IF @ACTION = 'D'
	BEGIN
		--INSERT DANS LA TABLE DESTRUCTION
		INSERT INTO dbo.T_DESTRUCTION_DES(GFT_ID, DES_DATE, DES_CREATED_BY)
		VALUES(@GFT_ID, @DATE, @USERNAME);
		
		--ON RETIRE LE GIFT DU STOCK DE LA CENTRALE
		UPDATE DBO.V_CENTRALE_STOCK_VCS
		SET
				CSS_DATE_OUT = @DATE
		WHERE
				GFT_ID = @GFT_ID
			AND	CSS_DATE_OUT IS NULL
	END
	
	--VOLE (TDC_ID = 2)
	IF @ACTION = 'X'
	BEGIN
		--ON RECUPERE L'ID DU MAGASIN
		SET @ORIGIN = @ORIGIN * 10000
		IF @ORIGIN = 0
			SET @STR_ID = 17
		ELSE
			EXEC TMP_UP_GET_STORE_ID @ORIGIN, @STR_ID OUTPUT;
			
		--INSERT DANS LA TABLE DECLARATION
		INSERT INTO dbo.T_DECLARATION_DEC(TDC_ID, DCL_ID, GFT_ID, DEC_DATE, STR_ID, DEC_CREATED_BY)
		VALUES(2, 0, @GFT_ID, @DATE, @STR_ID, @USERNAME);
	END
	
	FETCH NEXT FROM C INTO @SEQNRHIST, @BARCODE, @DATE, @ACTION, @ORIGIN, @DESTTYPE, @DEST, @VALUE, @SALETYPE, @USERTYPE;
END

CLOSE C;
DEALLOCATE C;