return: text
lang: plpgsql
src: |
  DECLARE	--select * from eco.p_stock order by date_fin desc
  	result TEXT;
  BEGIN
  	
  	
  	UPDATE eco.p_mouvements_articles 
  		SET
  		est_premier_jour = sub.est_premier_jour,
  		est_dernier_jour = sub.est_dernier_jour,
  		date_fin = sub.date_fin
  		FROM (SELECT
  		oid,
  		 CASE
              WHEN rank() OVER (PARTITION BY p_mouvements_articles.article_id, p_mouvements_articles.lieu_id,date ORDER BY p_mouvements_articles.oid) = 1 THEN 1
              ELSE 0
          END AS est_premier_jour,
          CASE
              WHEN cume_dist() OVER (PARTITION BY p_mouvements_articles.article_id, p_mouvements_articles.lieu_id, p_mouvements_articles.date ORDER BY p_mouvements_articles.oid) = 1 THEN 1
              ELSE 0
          END AS est_dernier_jour,
          CASE
              WHEN cume_dist() OVER (PARTITION BY p_mouvements_articles.article_id, p_mouvements_articles.lieu_id, p_mouvements_articles.date ORDER BY p_mouvements_articles.oid) = 1
  		THEN lead(p_mouvements_articles.date) OVER (PARTITION BY p_mouvements_articles.article_id, p_mouvements_articles.lieu_id ORDER BY p_mouvements_articles.date) - interval '1 day'
              ELSE date 
          END AS date_fin
          FROm eco.p_mouvements_articles 
          ) sub
          WHERE sub.oid = p_mouvements_articles.oid;
  
  	UPDATE eco.p_mouvements_articles 
  	SET date_fin = date_trunc('month',now()) - interval '1 day' + interval '1 month'
  	WHERE date_fin IS NULL
  	;
  	
          UPDATE eco.p_mouvements_articles 
  	SET
  		stock_quantite_debut = 0,
  		stock_quantite_fin = 0
  	;
  
  	UPDATE eco.p_mouvements_articles
  	SET
  		stock_quantite_debut = p_stock.stock_quantite_fin - w_stock_var.entree_var + w_stock_var.sortie_var,
  		stock_quantite_fin = p_stock.stock_quantite_fin - w_stock_var.entree_var + w_stock_var.sortie_var + p_mouvements_articles.entree_quantite - p_mouvements_articles.sortie_quantite
  	FROM
  	(
  		SELECT
  			oid,
  			lieu_id,
  			article_id,
  			date,
  			date_trunc('month',date) as month,
  			sum(entree_quantite) OVER (PARTITION BY lieu_id, article_id, date_trunc('month',date) ORDER BY date DESC, oid DESC) AS entree_var,
  			sum(sortie_quantite) OVER (PARTITION BY lieu_id, article_id, date_trunc('month',date) ORDER BY date DESC, oid DESC) AS sortie_var
  		FROM eco.p_mouvements_articles
  		ORDER BY 1,2,3,4
  		) w_stock_var
  		JOIN 
  		eco.p_stock
  		ON 1=1
  			AND w_stock_var.article_id = p_stock.article_id
  			AND w_stock_var.lieu_id = p_stock.lieu_id
  			AND date_trunc('month',date_fin) = month 
  		WHERE 1=1
  			AND p_mouvements_articles.article_id = w_stock_var.article_id
  			AND p_mouvements_articles.lieu_id = w_stock_var.lieu_id
  			AND p_mouvements_articles.oid = w_stock_var.oid
  		;
  
  
  		
  UPDATE eco.p_mouvements_articles
  	SET prix_unitaire_calcule = null;
  
  	-- initialisation des pump à première entrée
  	UPDATE eco.p_mouvements_articles
  	SET prix_unitaire_calcule = prix_unitaire
  	FROM (
  		SELECT
  			article_id,
  			lieu_id,
  			(MIN(ARRAY[CASE WHEN entree_montant_ht > 0 and entree_quantite > 0 THEN date::text ELSE '2099-12-31' END, oid::text]))[2] as oid
  		FROM eco.p_mouvements_articles
  		GROUP BY 1,2
  		) sub
  	WHERE p_mouvements_articles.oid = sub.oid
  		AND  p_mouvements_articles.lieu_id = sub.lieu_id
  		AND  p_mouvements_articles.article_id = sub.article_id
  		;
  	
  	-- ou si le stock est nul avant et pas après
  	UPDATE eco.p_mouvements_articles
  	SET prix_unitaire_calcule = prix_unitaire
  	WHERE prix_unitaire_calcule IS NULL 
  	AND stock_quantite_debut <= 0
  	AND stock_quantite_fin > 0;
  	
  	-- pump 0 si pas de stock
  	UPDATE eco.p_mouvements_articles
  	SET prix_unitaire_calcule = 0
  	WHERE stock_quantite_fin = 0
  	;
  
  	ANALYSE eco.p_mouvements_articles;
  
  	DROP SEQUENCE IF EXISTS s_mv_sto;
  	CREATE TEMP SEQUENCE s_mv_sto; 
  
  	DROP TABLE IF EXISTS w_mvt_sto;
  	CREATE TEMP TABLE w_mvt_sto AS
  	SELECT
  		nextval(' s_mv_sto'::regclass) AS sequence,
  		*,
  		0::bigint AS derniere_entree
  		FROM (
  			SELECT article_id,
  			lieu_id,
  			date,
  			oid,
  			stock_quantite_fin,
  			entree_quantite,
  			entree_montant_ht,
  			prix_unitaire_calcule,
  			date_fin
  		FROM eco.p_mouvements_articles
  		ORDER BY 1,2,3,4
  		) sub;
  
  	-- sélection des mouvements d'entrée
  	DROP TABLE IF EXISTS w_periode_sto;
  	CREATE TEMP TABLE w_periode_sto AS 
  			SELECT article_id,
  			lieu_id,
  			date,
  			oid,
  			lead(oid,1) OVER (PARTITION BY article_id, lieu_id ORDER BY sequence) AS oid_next,
  			lead(date,1) OVER (PARTITION BY article_id, lieu_id ORDER BY sequence) as date_fin
  			FROM w_mvt_sto
  			WHERE (entree_montant_ht > 0 AND entree_quantite > 0)
  			ORDER BY 1,2,3
  	;
  
  	UPDATE w_periode_sto SET date_fin = '2099-12-31' WHERE date_fin IS NULL;
  
  	-- calcul des dernière entrées
  	UPDATE w_mvt_sto 
  	SET derniere_entree = w_periode_sto.oid
  	FROM w_periode_sto
  	WHERE 1=1 
  		AND w_mvt_sto.article_id = w_periode_sto.article_id
  		AND w_mvt_sto.lieu_id = w_periode_sto.lieu_id
  		AND (w_mvt_sto.oid = w_periode_sto.oid_next
  		OR (w_mvt_sto.oid > w_periode_sto.oid AND w_mvt_sto.date BETWEEN w_periode_sto.date AND w_periode_sto.date_fin))
  		;
  
  	UPDATE w_mvt_sto 
  	SET derniere_entree = w_first_ent.oid
  	FROM (SELECT article_id, lieu_id, (MIN(ARRAY[date::text, oid::text]))[2]::bigint as oid FROM w_periode_sto GROUP BY 1,2) w_first_ent
  	WHERE 1=1 
  		AND derniere_entree = 0
  		AND w_mvt_sto.article_id = w_first_ent.article_id
  		AND w_mvt_sto.lieu_id = w_first_ent.lieu_id
  		;
  
  
  	CREATE INDEX w_mvt_article_id ON w_mvt_sto USING btree (article_id);
  	CREATE INDEX w_mvt_lieu_id ON w_mvt_sto USING btree (lieu_id);
  	CREATE INDEX w_mvt_oid ON w_mvt_sto USING btree (oid);
  
  	ANALYSE w_mvt_sto;
  
  	-- maj des pump
  	PERFORM base.cti_execute('
  
  	UPDATE w_mvt_sto
  	SET prix_unitaire_calcule = base.cti_division(abs(w_mvt_prev.stock_quantite_fin)*w_mvt_prev.prix_unitaire_calcule + abs(w_mvt_sto.entree_montant_ht), abs(w_mvt_prev.stock_quantite_fin) +  abs(w_mvt_sto.entree_quantite))
  	FROM w_mvt_sto w_mvt_prev
  	WHERE 1=1 
  		AND w_mvt_sto.prix_unitaire_calcule IS NULL
  		AND w_mvt_prev.prix_unitaire_calcule IS NOT NULL
  		AND w_mvt_sto.derniere_entree = w_mvt_prev.oid
  		AND (w_mvt_sto.entree_montant_ht != 0)',2000)
  		--select article_id, lieu_id, (MIN(ARRAY[sequence::text, prix_unitaire_calcule::text]))[2] from w_mvt_sto group by 1,2 having (MIN(ARRAY[sequence::text, prix_unitaire_calcule::text]))[2] is null order by 3 desc
  		--select * from eco.v_mouvements_articles_1 where article_id = 6634 and lieu_id = 2 order by date
  		--select * from w_mvt_sto where article_id = 9663 and lieu_id = 2 order by sequence
  ;		
  	-- extension aux mouvements de sortie
  	UPDATE w_mvt_sto
  	SET prix_unitaire_calcule = w_mvt_prev.prix_unitaire_calcule
  	FROM w_mvt_sto w_mvt_prev 
  	WHERE 1=1 
  	AND w_mvt_sto.prix_unitaire_calcule IS NULL 
  	AND w_mvt_sto.derniere_entree != 0
  	AND w_mvt_prev.oid = w_mvt_sto.derniere_entree
  	AND w_mvt_prev.prix_unitaire_calcule IS NOT NULL 
  	;
  
  	-- prise en compte des lieu différents au besoin
  	UPDATE w_mvt_sto
  	SET prix_unitaire_calcule = w_mvt_prev.prix_unitaire_calcule
  	FROM w_mvt_sto w_mvt_prev 
  	WHERE 1=1 
  	AND w_mvt_sto.prix_unitaire_calcule IS NULL 
  	AND w_mvt_prev.article_id = w_mvt_sto.article_id
  	AND w_mvt_prev.prix_unitaire_calcule IS NOT NULL
  	AND w_mvt_sto.date BETWEEN w_mvt_prev.date AND w_mvt_prev.date_fin
  	AND w_mvt_prev.lieu_id != w_mvt_sto.lieu_id
  	;
  
  	-- maj des mouvements
  	UPDATE eco.p_mouvements_articles 
  	SET prix_unitaire_calcule = w_mvt_sto.prix_unitaire_calcule
  	FROM w_mvt_sto
  	WHERE p_mouvements_articles.oid = w_mvt_sto.oid
  	;
  
  	-- sinon (au pire si pas d'information) pump = pu
  	UPDATE eco.p_mouvements_articles 
  	SET prix_unitaire_calcule = prix_unitaire
  	WHERE 1=1 
  	AND prix_unitaire_calcule IS NULL
  	;
  
  	UPDATE eco.p_mouvements_articles SET date_fin = date_fin + interval '1 day' WHERE est_dernier_jour = 1;
  	RETURN 'OK';
  END;