return: text
lang: plpgsql
src: |
  DECLARE 
  	year int;
  	tableToCheck text;
  	maxYearInserted int;
  	yearsToDo int[];
  	tablesToCheck text[];
  	request text;
  	canDoYear bool;
  	startDate date;
  	endDate date;
  	targetTable text;
  	
  	BEGIN
  		startDate = (COALESCE((SELECT MAX(annee) FROM rh.p_sae_contrats) + 1, 2017)|| '-01-01')::date;
  		endDate = (date_part('YEAR', NOW())::text || '-01-01')::date - interval '1 year';
  		yearsToDo = (SELECT array_agg(date_part) FROM (SELECT date_part('YEAR', (generate_series(startDate, endDate, '1 year'::interval)))::int) AS sub);
  		tablesToCheck = ARRAY['t_#YEAR#_sae_contrat', 't_#YEAR#_sae_groupe_discipline'];
  
  		-- Si une des 2 tables nécessaires n'existent pas
  		IF yearsToDo IS NULL THEN RETURN 'Aucune année à traiter'; END IF; 
  
  		FOREACH year IN ARRAY yearsToDo LOOP
  			-- Vérification de l'existence des tables requises pour l'année de la boucle
  			FOREACH tableToCheck IN ARRAY tablesToCheck LOOP
  
  				IF year = 2017 THEN 
  					tableToCheck = REPLACE(tableToCheck, '_#YEAR#_', '_'); 
  				END IF;
  
  				IF year > 2017 THEN 
  					tableToCheck = REPLACE(tableToCheck, '#YEAR#', year); 
  				END IF;
  
  				canDoYear = ((SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'ssae' AND table_name = tableToCheck) = 1);                
  			END LOOP;
  
  			-- Si une des 2 tables nécessaires n'existent pas
  			IF NOT canDoYear THEN RETURN 'Une table nécessaire n''existe pas'; END IF;   
  
  			request = '
  			INSERT INTO rh.p_sae_contrats (
  				annee, 
  				mois_activite_debut_id, 
  				mois_activite_fin_id, 
  				salarie_id, 
  				contrat_code, 
  				contrat_id, 
  				groupe_discipline_texte, 
  				qualification_texte, 
  				filiere_texte
  			)
  			SELECT 
  					#YEAR# AS annee,
  					saec.mois_activite_debut_id,
  					saec.mois_activite_fin_id,
  					saec.salarie_id,
  					saec.contrat_code,
  					saec.contrat_id,
  					saegd.texte AS groupe_discipline_texte,
  					saec.qualification_texte,
  					CASE filiere_code
  						WHEN ''01'' THEN ''MEDECIN SOIGNANT''
  						WHEN ''11'' THEN ''SOIGNANT''
  						WHEN ''21'' THEN ''NON SOIGNANT''
  						WHEN ''91'' THEN ''Hors champ SAE''
  						ELSE ''???'' END AS filiere_texte
  				FROM ssae.t_#YEAR#_sae_contrat AS saec
  				LEFT JOIN ssae.t_#YEAR#_sae_groupe_discipline AS saegd ON saegd.oid = saec.groupe_discipline_id[1]'
  			;
  
  			IF year = 2017 THEN 
  				request = replace(request, '_#YEAR#_', '_'); 
  				request = replace(request, '#YEAR#', year); 
  			END IF;
  
  			IF year > 2017 THEN 
  				request = replace(request, '#YEAR#', year); 
  			END IF;
  
  			EXECUTE request;
  			RAISE NOTICE 'Année % traitée', year;
  
  		END LOOP;
  	RETURN 'OK';			
  END;