return: text
lang: plpgsql
src: |
  DECLARE	
  	_marge_erreur numeric;
  	_ignorer_reglements text;
  	result TEXT;
  BEGIN
  	RAISE NOTICE 'Initialisations';
  	
  		
  	
  	
  	
  							
  	RAISE NOTICE 'Lignes detaillees';
  	
  	DROP TABLE IF EXISTS w_cumuls;
  	CREATE TEMP TABLE w_cumuls AS
  	SELECT site_id, 0::bigint AS provider_id, mois_comptable, clinique_honoraire, 
  		round(SUM(montant_debit),2) AS montant_debit_d, 
  		round(SUM(montant_credit),2) AS montant_credit_d, 
  		0::numeric AS montant_debit_t, 
  		0::numeric AS montant_credit_t, 
  		0::numeric AS montant_debit_c, 
  		0::numeric AS montant_credit_c
  	FROM compta.p_historique_ecritures
  	WHERE ajustement NOT IN ('1','2','3') AND ecriture_consolidee <= '0' AND
  		is_budget <> '1'
  	GROUP BY 1,2,3,4
  	ORDER BY 1,2,3,4;
  	
  	RAISE NOTICE 'Lignes totalisées';
  					
  	INSERT INTO w_cumuls
  	SELECT site_id, 0::bigint AS provider_id, mois_comptable, clinique_honoraire, 
  		0::numeric AS montant_debit_d, 
  		0::numeric AS montant_credit_d, 
  		round(SUM(montant_debit),2) AS montant_debit_t, 
  		round(SUM(montant_credit),2) AS montant_credit_t, 
  		0::numeric AS montant_debit_c, 
  		0::numeric AS montant_credit_c
  	FROM compta.p_historique_ecritures_total
  	WHERE ajustement NOT IN ('1','2','3') AND ecriture_consolidee <= '0' AND
  		is_budget <> '1'
  	GROUP BY 1,2,3,4
  	ORDER BY 1,2,3,4;
  					
  	RAISE NOTICE 'Chiffrier production';
  					
  	INSERT INTO w_cumuls
  	SELECT site_id, COALESCE(provider_id,0), mois_comptable, clinique_honoraire, 
  		0::numeric AS montant_debit_d, 
  		0::numeric AS montant_credit_d, 
  		0::numeric AS montant_debit_t, 
  		0::numeric AS montant_credit_t, 
  		round(SUM(montant_debit),2) AS montant_debit_c, 
  		round(SUM(montant_credit),2) AS montant_credit_c
  	FROM compta.p_chiffrier_comptable
  	WHERE mois_comptable >= (SELECT MIN(mois_comptable) FROM w_cumuls)
  	GROUP BY 1,2,3,4
  	ORDER BY 1,2,3,4;
  	
  	RAISE NOTICE 'Controles';
  	DROP TABLE IF EXISTS w_controle_chiffrier;
  	CREATE TEMP TABLE w_controle_chiffrier AS
  	SELECT site_id, provider_id, mois_comptable, clinique_honoraire, 
  		SUM(montant_debit_d) AS montant_debit_d, 
  		SUM(montant_credit_d) AS montant_credit_d, 
  		SUM(montant_debit_t) AS montant_debit_t, 
  		SUM(montant_credit_t) AS montant_credit_t, 
  		SUM(montant_debit_c) AS montant_debit_c, 
  		SUM(montant_credit_c) AS montant_credit_c,
  		CASE 
  			WHEN SUM(montant_debit_d) = SUM(montant_debit_t) AND
  			SUM(montant_debit_c) = 0 AND
  			SUM(montant_credit_d) = SUM(montant_credit_t) AND
  			SUM(montant_credit_c) = 0
  			THEN  '1'
  			WHEN SUM(montant_debit_d) = SUM(montant_debit_t) AND
  			SUM(montant_debit_d) = SUM(montant_debit_c) AND
  			SUM(montant_credit_d) = SUM(montant_credit_t) AND
  			SUM(montant_credit_d) = SUM(montant_credit_c)
  			THEN '1' 
  			ELSE '0' END AS valide
  	FROM w_cumuls
  	GROUP BY 1,2,3,4
  	ORDER BY 1,2,3,4;
  	UPDATE w_controle_chiffrier
  	SET valide = '1'
  	FROM compta.t_controle_chiffrier
  	WHERE w_controle_chiffrier.site_id = t_controle_chiffrier.site_id AND
  		w_controle_chiffrier.provider_id = t_controle_chiffrier.provider_id AND
  		w_controle_chiffrier.mois_comptable = t_controle_chiffrier.mois_comptable AND
  		w_controle_chiffrier.clinique_honoraire = t_controle_chiffrier.clinique_honoraire AND
  		w_controle_chiffrier.valide = '0' AND
  		t_controle_chiffrier.valide = '1' AND
  		w_controle_chiffrier.montant_debit_d = t_controle_chiffrier.montant_debit_d AND
  		w_controle_chiffrier.montant_debit_t = t_controle_chiffrier.montant_debit_t AND
  		w_controle_chiffrier.montant_debit_c = t_controle_chiffrier.montant_debit_c AND
  		w_controle_chiffrier.montant_credit_d = t_controle_chiffrier.montant_credit_d AND
  		w_controle_chiffrier.montant_credit_t = t_controle_chiffrier.montant_credit_t AND
  		w_controle_chiffrier.montant_credit_c = t_controle_chiffrier.montant_credit_c;
  	-- Antérieur
  	UPDATE w_controle_chiffrier
  	SET valide = '1'
  	WHERE valide <> '1' AND
  		mois_comptable < (SELECT MIN(mois_comptable) AS mois_comptable_first FROM compta.p_historique_ecritures);
  					
  	TRUNCATE compta.t_controle_chiffrier;
  					
  	INSERT INTO compta.t_controle_chiffrier
  		(
  		site_id, provider_id, mois_comptable, clinique_honoraire, 
  		montant_debit_d,
  		montant_credit_d,
  		montant_debit_t,
  		montant_credit_t,
  		montant_debit_c,
  		montant_credit_c,
  		valide
  		)
  	SELECT w_controle_chiffrier.site_id, w_controle_chiffrier.provider_id, w_controle_chiffrier.mois_comptable, w_controle_chiffrier.clinique_honoraire, 
  		montant_debit_d,
  		montant_credit_d,
  		montant_debit_t,
  		montant_credit_t,
  		montant_debit_c,
  		montant_credit_c,
  		valide
  	FROM w_controle_chiffrier
  	ORDER BY 1,2,3,4;
  			
  		
  	RETURN 'OK';
  END;