/**
 * Author:  Maxime Levert
 * Created: 10 sept. 2019
 */



-- DROP VIEW public.v_notedatesemis_calcul;

CREATE OR REPLACE VIEW public.v_notedatesemis_calcul AS

SELECT 
adv.idadventice, rl.numdecade,idculture, rl.proplevee, 
rld6.proplevee as levee_d6, rld7.proplevee as levee_d7,rld8.proplevee as levee_d8,
--- Calcul de la nouvelle note :
( 
-- Decade D :
	rl.proplevee +
 -- Decade D+1 :
 CASE
 WHEN rl.numdecade <= 35
	THEN (LEAD(rl.proplevee,1,null) OVER(order by c.idculture, adv.idadventice, rl.numdecade ))*5/6
	ELSE (LAG(rl.proplevee,35) OVER(order by c.idculture, adv.idadventice, rl.numdecade ))*5/6
END 
+
 -- Decade D+2 :
CASE
 WHEN rl.numdecade <= 34
	THEN (LEAD(rl.proplevee,2,null) OVER(order by c.idculture, adv.idadventice, rl.numdecade ))*4/6
	ELSE (LAG(rl.proplevee,34) OVER(order by c.idculture, adv.idadventice, rl.numdecade ))*4/6
END
+
 -- Decade D+3 :
CASE
 WHEN rl.numdecade <= 33
	THEN (LEAD(rl.proplevee,3,null) OVER(order by c.idculture, adv.idadventice, rl.numdecade ))*3/6
	ELSE (LAG(rl.proplevee,33) OVER(order by c.idculture, adv.idadventice, rl.numdecade ))*3/6
END
+
 -- Decade D+4 :
 CASE
 WHEN rl.numdecade <= 32
	THEN (LEAD(rl.proplevee,4) OVER(order by c.idculture, adv.idadventice, rl.numdecade ))*2/6
	ELSE (LAG(rl.proplevee,32) OVER(order by c.idculture, adv.idadventice, rl.numdecade ))*2/6
END	+
 -- Decade D+5 :
 CASE
 WHEN rl.numdecade <= 31
	THEN (LEAD(rl.proplevee,5) OVER(order by c.idculture, adv.idadventice, rl.numdecade ))*1/6
	ELSE (LAG(rl.proplevee,31) OVER(order by c.idculture, adv.idadventice, rl.numdecade ))*1/6
END
-- Cas du Maïs, decades D+6, D+7 et D+8... :
+
CASE WHEN(c.idculture IN(21)) -- Seulement pour le maïs
	THEN
		CASE
		 WHEN rl.numdecade <= 30
			THEN (LEAD(rl.proplevee,6) OVER(order by c.idculture, adv.idadventice, rl.numdecade ))*1/6
			ELSE (LAG(rl.proplevee,30) OVER(order by c.idculture, adv.idadventice, rl.numdecade ))*1/6
		END +
		CASE
			 WHEN rl.numdecade <= 29
				THEN (LEAD(rl.proplevee,7) OVER(order by c.idculture, adv.idadventice, rl.numdecade ))*1/6
				ELSE (LAG(rl.proplevee,29) OVER(order by c.idculture, adv.idadventice, rl.numdecade ))*1/6
			END +
			CASE
			 WHEN rl.numdecade <= 28
				THEN (LEAD(rl.proplevee,8) OVER(order by c.idculture, adv.idadventice, rl.numdecade ))*1/6
				ELSE (LAG(rl.proplevee,28) OVER(order by c.idculture, adv.idadventice, rl.numdecade ))*1/6
			END 
	ELSE 0
END
+
-- Cas du blé d'hiver : ajout des decades 6, 7 et 8
CASE WHEN(c.idculture IN(1) AND (rl.numdecade < 7 OR rl.numdecade > 18)) -- Seulement pour le blé
	THEN 
		(CASE WHEN (rl.numdecade <=6 ) -- entre 1 et 6, on ne compte pas la decade 6
			THEN 0
			ELSE rld6.proplevee
		END +
		CASE WHEN (rl.numdecade >= 2 AND rl.numdecade <=7 ) -- entre 2 et 7, on ne compte pas la decade 7
				THEN 0
				ELSE rld7.proplevee	
		END +
		CASE WHEN (rl.numdecade >= 3 AND rl.numdecade <=8 ) -- entre 3 et 8, on ne compte pas la decade 8
				THEN 0
				ELSE rld8.proplevee	
		END ) *1/6
		
	ELSE 0
END
) *3

AS note


FROM  t_refadventice adv
CROSS JOIN t_refculture c
LEFT JOIN t_refleveeadventice rl ON (rl.idadventice = adv.idadventice)
--LEFT JOIN t_refnotedatesemis ON (t_refnotedatesemis.idadventice = adv.idadventice
--								AND t_refnotedatesemis.numdecade = rl.numdecade)
LEFT JOIN t_refleveeadventice rld6 ON rld6.idadventice = adv.idadventice AND rld6.numdecade = 6 -- fev3
LEFT JOIN t_refleveeadventice rld7 ON rld7.idadventice = adv.idadventice AND rld7.numdecade = 7 -- mar1
LEFT JOIN t_refleveeadventice rld8 ON rld8.idadventice = adv.idadventice AND rld8.numdecade = 8 -- mar2
								
 --WHERE idculture IN (1, 4 ) --and adv.idadventice IN (1,2)
 ORDER BY idculture,adv.idadventice, rl.numdecade;

ALTER TABLE public.v_notedatesemis_calcul
    OWNER TO oderaroot;

COMMENT ON VIEW public.v_notedatesemis_calcul
    IS '09/2019 : Remplace la table statique t_refnodedatesemis dans Odera v2';

--------------------
 
-- View: public.v_notedatesemis

-- DROP VIEW public.v_notedatesemis;

CREATE OR REPLACE VIEW public.v_notedatesemis AS
 SELECT t_itk.iditk,
    v_notedatesemis_calcul.note  as notedatesemis,
    v_notedatesemis_calcul.idadventice
   FROM t_itk
     JOIN t_adventiceetudie _adv ON t_itk.idscenario = _adv.idscenario
     LEFT JOIN v_notedatesemis_calcul 
	 	ON t_itk.decadesemis = v_notedatesemis_calcul.numdecade
	 		AND t_itk.idculture = v_notedatesemis_calcul.idculture
	 ;


ALTER TABLE public.v_notedatesemis
    OWNER TO oderaroot;
	
COMMENT ON VIEW public.v_notedatesemis
    IS 'modifiee en 09/2019 afin d''utiliser une vue de calcul à la place de la table de reference trefnodedatesemis';

