*-------------------------------------------------------------------------
* t_stamp_ops_evt_tmp.bteq
* Insertion des données
*-------------------------------------------------------------------------
*    Date    | Utilisateur     | Commentaires
*-------------------------------------------------------------------------
* 19/08/2015 | ASTEK TS        | Creation                          
* ??/07/2017 | N BOUCHER       | Refonte du calcul des routes et stations.                                 
* 13/10/2017 | N BOUCHER       | UPDATE de la date des EVT niveau Container DR_REC0   
* 14/03/2018 | N BOUCHER       | Modification on Route ENd calculation   
*-------------------------------------------------------------------------

/* ************************************************** */
/* Alimentation table STAMP_OPS_EVT_TMP               */
/* Contient les Evènements au niveau Receptacle (Sac) */
/* et au niveau Container (CARDIT)                    */
/* ************************************************** */

*------------------------------------------------------------------------------
* Vidage de la table STAMP_OPS_EVT_TMP
*------------------------------------------------------------------------------
DELETE FROM STAMP_OPS_EVT_TMP ALL;

.if ERRORCODE <> 0 then .GOTO ERREUR_DELETE

*--------------------------------------------------------------------------
* Debut de la transaction
*--------------------------------------------------------------------------
.SET RETRY OFF
BEGIN TRANSACTION;
lock table STAMP_OPS_EVT_TMP for write;

*------------------------------------------------------------------------------
* Alimentation de la table finale
*------------------------------------------------------------------------------
INSERT INTO STAMP_OPS_EVT_TMP
Select 
	--ETAPE TMP7: Pour chaque station qui n est pas nouvelle (voir TMP5), on reporte le numéro précédent
	NUM_RTE
	--On reporte les numéro de station qui ne changent pas.
    ,case when NUM_STA is null then max(NUM_STA) OVER (PARTITION BY IDT_CNT_RCU, NUM_RTE  ORDER BY DAT_EVT_UTC ASC, case when COD_EVT in ('74','43') then '1000'  when COD_EVT in ('21', '42') then '0' else COD_EVT end desc, DAT_DEP asc ROWS UNBOUNDED PRECEDING) else NUM_STA 
    end as NUM_STA
	,COD_EVT
    ,IDT_CNT_RCU
    ,DAT_EVT_UTC
    ,DAT_LOC_EVT
    ,DAT_DEP
    ,LCT_SCA
	,ESC_ORI
    ,ESC_DST
    ,NOM_PRN_ORI
    ,NOM_PRN_DST
    ,NUM_VOL_DEP
    ,NUM_VOL_ARV
    ,IDT_VOL
    
 FROM 
	(
	--ETAPE TMP6: Pour chaque route, pour chaque nouvelle station, On affecte un numéro croissant à chaque nouvelle station (voir TMP5)
	Select 
	NUM_RTE
		
		--S il s agit d une nouvelle station (voir TMP5), on effectue un compte les indicateurs sur les lignes pécédentes pour attribuer le bon numéro de station
		,case 
		when NUM_STA=1 then coalesce(Count(NUM_STA) OVER (PARTITION BY IDT_CNT_RCU, NUM_RTE  ORDER BY DAT_EVT_UTC ASC, case when COD_EVT in ('74','43') then '1000'  when COD_EVT in ('21', '42') then '0' else COD_EVT end desc, DAT_DEP asc ROWS UNBOUNDED PRECEDING),0) 
		else NUM_STA 
		end as 
		 NUM_STA
		,COD_EVT
		,IDT_CNT_RCU
		,DAT_EVT_UTC
		,DAT_LOC_EVT
		,DAT_DEP
		,LCT_SCA
		,ESC_ORI
		,ESC_DST
		,NOM_PRN_ORI
		,NOM_PRN_DST
		,NUM_VOL_DEP
		,NUM_VOL_ARV
		,IDT_VOL
		
	 FROM 
		(
		-- ETAPE TMP5 Pour chaque route, on identifie les nouvelles stations avec un indicateur '1'
		Select 
			NUM_RTE
			--indicateur de changement de station pour chaque route
			,case 
				when coalesce(min(LCT_SCA) over ( PARTITION BY IDT_CNT_RCU , NUM_RTE order by DAT_EVT_UTC ASC, case when COD_EVT in ('74','43') then '1000'  when COD_EVT in ('21', '42') then '0' else COD_EVT end desc, DAT_DEP asc  rows between 1 preceding and 1 preceding), 0) <> LCT_SCA  then  1
				else null
				end  as NUM_STA
			,COD_EVT
			,IDT_CNT_RCU
			,DAT_EVT_UTC
			,DAT_LOC_EVT
			,DAT_DEP
			,LCT_SCA
			,ESC_ORI
			,ESC_DST
			,NOM_PRN_ORI
			,NOM_PRN_DST
			,NUM_VOL_DEP
			,NUM_VOL_ARV
			,IDT_VOL

		FROM 
			(
			--ETAPE TMP4: Pour les lignes qui ne sont pas un début de route, on reporte le numéro précédent
				Select 
				case when NUM_RTE is null then 
											-- Si la ligne n'a pas de prédécésseur alors on mets 1 sinon on mets la valeur du prédécesseur
											Case when max(NUM_RTE) OVER (PARTITION BY IDT_CNT_RCU ORDER BY DAT_EVT_UTC ASC, case when COD_EVT in ('74','43') then '1000'  when COD_EVT in ('21', '42') then '0' else COD_EVT end desc, DAT_DEP asc ROWS UNBOUNDED PRECEDING) is null
											then 1
											Else max(NUM_RTE) OVER (PARTITION BY IDT_CNT_RCU ORDER BY DAT_EVT_UTC ASC, case when COD_EVT in ('74','43') then '1000'  when COD_EVT in ('21', '42') then '0' else COD_EVT end desc, DAT_DEP asc ROWS UNBOUNDED PRECEDING)
											end
										else NUM_RTE end as NUM_RTE
				,COD_EVT
				,IDT_CNT_RCU
				,DAT_EVT_UTC
				,DAT_LOC_EVT
				,DAT_DEP
				,LCT_SCA
				,ESC_ORI
				,ESC_DST
				,NOM_PRN_ORI
				,NOM_PRN_DST
				,NUM_VOL_DEP
				,NUM_VOL_ARV
				,IDT_VOL

			FROM 
				(
				--ETAPE TMP3 : On affecte un numéro croissant à chaque début de route
				SELECT distinct
					 --s il s agit d un début de route, on effectue un compte les indicateurs sur les lignes pécédentes pour attribuer le bon numéro de début de route
					case 
					when RTE=1 then coalesce(Count(RTE) OVER (PARTITION BY IDT_CNT_RCU ORDER BY DAT_EVT_UTC asc , case when COD_EVT in ('74','43') then '1000'  when COD_EVT in ('21', '42') then '0' else COD_EVT end desc, DAT_DEP asc ROWS UNBOUNDED PRECEDING),0) 
					else RTE 
					end as NUM_RTE			   
					,TMP2.COD_EVT
					,TMP2.IDT_CNT_RCU
					,TMP2.DAT_EVT_UTC
					,TMP2.DAT_LOC_EVT
					,TMP2.DAT_DEP
					,TMP2.LCT_SCA
					,TMP2.ESC_ORI
					,TMP2.ESC_DST
					,TMP2.NOM_PRN_ORI
					,TMP2.NOM_PRN_DST
					,TMP2.NUM_VOL_DEP
					,TMP2.NUM_VOL_ARV
					,TMP2.IDT_VOL
				FROM
				   ( --ETAPE TMP2: On identifie les début de route avec des indicateur '1' 
					SELECT
						--On affecte un indicateur  pour chaque début de route du Receptacle
						--s il s agit d un premier evènement (sauf iSC) sans début identifié
						case 
						when ROW_NUMBER() over ( PARTITION BY IDT_CNT_RCU 
												order by DAT_EVT_UTC ASC, case when COD_EVT in ('74','43') then '1000'  
																				when COD_EVT in ('21', '42') then '0' 
																			else COD_EVT end desc, DAT_DEP asc  ) = 1 
								AND COD_EVT <>'iSC' 
								AND COD_EVT <>'6'
						then 1
						--s il s agit bien d un début identifié
						when COD_EVT in ('74','43') 
						then 1
						-- s il n y a pas de début de route mais que la ligne précédente est une fin de route, alors on crée une nouvelle route quand même
						when coalesce(min(COD_EVT) over ( PARTITION BY IDT_CNT_RCU order by DAT_EVT_UTC ASC, case when COD_EVT in ('74','43') then '1000' when COD_EVT in ('21', '42') then '0' else COD_EVT end desc, DAT_DEP asc  rows between 1 preceding and 1 preceding), '#') in ('21', '42')  
						then 1
						else null end as RTE
					   ,TMP1.*
					   FROM
					   
								
						   (
							-- ETAPE TMP1: On reccupère les information de base
							SELECT 
								'RECEPTACLE' as LVL
								,COD_EVT
								,IDT_CNT_RCU
								,DAT_EVT_UTC as NON_CORR_DAT_EVT_UTC
								,cast (NULL as Timestamp(0)) as NON_CORR_DAT_EVT_CNT_UTC
								,DAT_EVT_UTC
								,DAT_LOC_EVT
								,DAT_DEP
								,LCT_SCA
								,ESC_ORI
								,ESC_DST
								,NOM_PRN_ORI
								,NOM_PRN_DST
								,NUM_VOL_DEP
								,NUM_VOL_ARV
								,IDT_VOL
							FROM
								DWHFRT.STAMP_EVT_CNT
							
							--Receptacle level
							WHERE IDT_TYP = 'R'
							
							UNION 
							
							SELECT 
								 'CONTAINER' as LVL
								 ,EVT.COD_EVT
								,CNT.IDT_CNT_RCU
								--DR_REC0 Event at the COntainer level must have the data of the max(STAMP_CNT_RCU.DAT_DEB_VLD)
								,EVT.DAT_EVT_UTC as NON_CORR_DAT_EVT_UTC
								,CNT.DAT_DEB_VLD as NON_CORR_DAT_EVT_CNT_UTC
								--,Las plus grande des deux dates :(CNT.DAT_DEB_VLD  , EVT.DAT_EVT_UTC     ) as DAT_EVT_UTC
								,CAST(
										SUBSTRING (CAST ( 
												 TO_TIMESTAMP(
															GREATEST(
															COALESCE(TO_CHAR(CNT.DAT_DEB_VLD , 'yyyymmddhh24miss'),'19000101000000'),
															COALESCE(TO_CHAR(EVT.DAT_EVT_UTC, 'yyyymmddhh24miss'),'19000101000000')
															),'yyyymmddhh24miss')
										AS VARCHAR(25))  FROM 1 FOR 19)  
									AS TIMESTAMP(0) FORMAT 'YYYY/MM/DDBHH:MI:SS')
								AS DAT_EVT_UTC
								,EVT.DAT_LOC_EVT
								,EVT.DAT_DEP
								,EVT.LCT_SCA
								,EVT.ESC_ORI
								,EVT.ESC_DST
								,EVT.NOM_PRN_ORI
								,EVT.NOM_PRN_DST
								,EVT.NUM_VOL_DEP
								,EVT.NUM_VOL_ARV
								,EVT.IDT_VOL
							FROM
								DWHFRT.STAMP_EVT_CNT EVT
							
							INNER JOIN DWHFRT.STAMP_CNT_RCU CNT
									ON EVT.IDT_COD_CNT = CNT.IDT_COD_CNT
							
							--Container level
							WHERE EVT.IDT_TYP = 'C'
							  AND CNT.IDT_TYP = 'R'		
							) as TMP1
							--where IDT_CNT_RCU='AEDXBASESTOABTT70050001000054'
					) as TMP2
				) as TMP3

			) as TMP4
		) as TMP5
	) as TMP6
 --order by 4,5
;

.if ERRORCODE <> 0 then .GOTO ERREUR_INSERT

*--------------------------------------------------------------------------
* Fin de la transaction
*--------------------------------------------------------------------------
END TRANSACTION;

*--------------------------------------------------------------------------
* Collecte des statistiques sur index primaire
*--------------------------------------------------------------------------

COLLECT STATISTICS ON STAMP_OPS_EVT_TMP INDEX PI_STAMP_OPS_EVT_TMP;
.if ERRORCODE <> 0 then .GOTO ERREUR_COLLECT

*--------------------------------------------------------------------------
* Sortie ok
*--------------------------------------------------------------------------
 -- Fin  du traitement OK
.LABEL SORTIE_OK
.QUIT 0


*--------------------------------------------------------------------------
* Pb lors du chargement de la table
*--------------------------------------------------------------------------
.LABEL ERREUR_DELETE
.os echo "Erreur delete table STAMP_OPS_EVT_TMP"
.quit 12

.LABEL ERREUR_INSERT
.os echo "Erreur insert dans STAMP_OPS_EVT_TMP"
.quit 12

.LABEL ERREUR_COLLECT
.os echo "Erreur collect stats table STAMP_OPS_EVT_TMP"
.quit 12

