You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
338 lines
20 KiB
338 lines
20 KiB
return: text
|
|
lang: plpgsql
|
|
src: |
|
|
DECLARE
|
|
result TEXT;
|
|
domains RECORD;
|
|
tmpSchema TEXT;
|
|
tmpTable TEXT;
|
|
tmpListe TEXT;
|
|
tmpReq TEXT;
|
|
BEGIN
|
|
RAISE NOTICE '%' , 'Initialisation Populations'
|
|
;
|
|
-- Contenu table
|
|
INSERT INTO rh.t_population(oid, code, texte, texte_court)
|
|
SELECT 0, chr(127) || '*', 'Non renseignée', 'N/R'
|
|
WHERE 0 NOT IN (SELECT oid FROM rh.t_population)
|
|
;
|
|
|
|
DROP TABLE IF EXISTS w_domains
|
|
;
|
|
CREATE TEMP TABLE w_domains AS
|
|
SELECT
|
|
domain
|
|
FROM unnest(ARRAY[
|
|
'base|t_finess|finess',
|
|
'rh|t_etablissements|etablissement',
|
|
'rh|t_services|service',
|
|
'rh|t_specialites|specialite',
|
|
'rh|t_sections_analytiques|section_analytique',
|
|
'rh|t_sections_analytiques_paie|section_analytique_paie',
|
|
'rh|t_qualifications|qualification',
|
|
'rh|t_grilles|grille',
|
|
'rh|t_codes_emploi|code_emploi',
|
|
'rh|t_categories_socio_professionnelle|categorie_socio_professionnelle',
|
|
'rh|t_statuts|statut'
|
|
]) as domain
|
|
;
|
|
|
|
tmpReq = '';
|
|
FOR domains IN SELECT * FROM w_domains LOOP
|
|
tmpSchema = split_part(domains.domain, '|', 1);
|
|
tmpTable = split_part(domains.domain, '|', 2);
|
|
tmpListe = split_part(domains.domain, '|', 3);
|
|
|
|
tmpReq = concat_ws(', ', tmpReq,
|
|
format('
|
|
(select coalesce(min(position (''^'' in token)) = 0, false)
|
|
from unnest(string_to_array(replace(liste_%s, '','', ''''), '' '')) as token
|
|
where length(trim(token)) > 0
|
|
) as has_liste_%s,
|
|
(select
|
|
array_agg(coalesce(table_liste.oid, listes.oid, classes.oid))
|
|
from unnest(string_to_array(replace(liste_%s, '','', ''''), '' '')) as token
|
|
left join %s.t_listes_tables on t_listes_tables.name = ''%s''
|
|
left join %s.%s as table_liste on table_liste.code like replace(trim(token), ''*'', ''%%'')
|
|
left join listes on listes.code like ''{''||t_listes_tables.code||''}''||replace(trim(token), ''*'', ''%%'')
|
|
left join classes on classes.code like ''{''||t_listes_tables.code||''}''||replace(trim(token), ''*'', ''%%'')
|
|
where 1=1
|
|
and token != ''''
|
|
and coalesce(table_liste.oid, listes.oid, classes.oid) is distinct from null
|
|
) as liste_%s_id', tmpListe, tmpListe, tmpListe, tmpSchema, tmpTable, tmpSchema, tmpTable, tmpListe),
|
|
format('
|
|
(select coalesce(max(position (''^'' in token)) > 0, false)
|
|
from unnest(string_to_array(replace(liste_%s, '','', ''''), '' '')) as token
|
|
where length(trim(token)) > 0
|
|
) as has_exc_liste_%s,
|
|
(select
|
|
array_agg(coalesce(table_liste.oid, listes_exc.oid, classes_exc.oid))
|
|
from unnest(string_to_array(replace(liste_%s, '','', ''''), '' '')) as token
|
|
left join %s.t_listes_tables on t_listes_tables.name = ''%s''
|
|
left join %s.%s as table_liste on ''^''||table_liste.code like replace(trim(token), ''*'', ''%%'')
|
|
left join listes_exc on listes_exc.code like ''{''||t_listes_tables.code||''}''||replace(trim(token), ''*'', ''%%'')
|
|
left join classes_exc on classes_exc.code like ''{''||t_listes_tables.code||''}''||replace(trim(token), ''*'', ''%%'')
|
|
where 1=1
|
|
and token != ''''
|
|
and coalesce(table_liste.oid, listes_exc.oid, classes_exc.oid) is distinct from null
|
|
) as liste_exc_%s_id', tmpListe, tmpListe, tmpListe, tmpSchema, tmpTable, tmpSchema, tmpTable, tmpListe));
|
|
|
|
END LOOP;
|
|
|
|
-- Traduction des règles en oid
|
|
RAISE NOTICE '%' , 'Application des regles. Traduction en oid'
|
|
;
|
|
DROP TABLE IF EXISTS w_population_regle
|
|
;
|
|
EXECUTE format('
|
|
CREATE TEMP TABLE w_population_regle AS
|
|
with listes as (
|
|
select
|
|
''{''||t_listes_tables.code||''}[LISTE:''||t_listes.code||'']'' as code,
|
|
t_listes_contenu.to_id as oid
|
|
from rh.t_listes
|
|
join rh.t_listes_tables on t_listes_tables.oid = t_listes.table_id
|
|
join rh.t_listes_contenu on t_listes_contenu.liste_id = t_listes.oid
|
|
UNION ALL
|
|
select
|
|
''{''||t_listes_tables.code||''}[LISTE:''||t_listes.code||'']'' as code,
|
|
t_listes_contenu.to_id as oid
|
|
from base.t_listes
|
|
join base.t_listes_tables on t_listes_tables.oid = t_listes.table_id
|
|
join base.t_listes_contenu on t_listes_contenu.liste_id = t_listes.oid
|
|
)
|
|
, classes as (
|
|
select
|
|
''{''||t_listes_tables.code||''}[CLASSE:''||t_classes.code||'':''||t_classes_sections.code||'']'' as code,
|
|
t_classes_sections_elements.to_id as oid
|
|
from rh.t_classes
|
|
join rh.t_listes_tables on t_listes_tables.oid = t_classes.table_id
|
|
join rh.t_classes_sections on t_classes_sections.classe_id = t_classes.oid
|
|
join rh.t_classes_sections_elements on t_classes_sections_elements.section_id = t_classes_sections.oid
|
|
UNION ALL
|
|
select
|
|
''{''||t_listes_tables.code||''}[CLASSE:''||t_classes.code||'':''||t_classes_sections.code||'']'' as code,
|
|
t_classes_sections_elements.to_id as oid
|
|
from base.t_classes
|
|
join base.t_listes_tables on t_listes_tables.oid = t_classes.table_id
|
|
join base.t_classes_sections on t_classes_sections.classe_id = t_classes.oid
|
|
join base.t_classes_sections_elements on t_classes_sections_elements.section_id = t_classes_sections.oid
|
|
)
|
|
, listes_exc as (
|
|
select
|
|
''{''||t_listes_tables.code||''}^[LISTE:''||t_listes.code||'']'' as code,
|
|
t_listes_contenu.to_id as oid
|
|
from rh.t_listes
|
|
join rh.t_listes_tables on t_listes_tables.oid = t_listes.table_id
|
|
join rh.t_listes_contenu on t_listes_contenu.liste_id = t_listes.oid
|
|
UNION ALL
|
|
select
|
|
''{''||t_listes_tables.code||''}^[LISTE:''||t_listes.code||'']'' as code,
|
|
t_listes_contenu.to_id as oid
|
|
from base.t_listes
|
|
join base.t_listes_tables on t_listes_tables.oid = t_listes.table_id
|
|
join base.t_listes_contenu on t_listes_contenu.liste_id = t_listes.oid
|
|
)
|
|
, classes_exc as (
|
|
select
|
|
''{''||t_listes_tables.code||''}^[CLASSE:''||t_classes.code||'':''||t_classes_sections.code||'']'' as code,
|
|
t_classes_sections_elements.to_id as oid
|
|
from rh.t_classes
|
|
join rh.t_listes_tables on t_listes_tables.oid = t_classes.table_id
|
|
join rh.t_classes_sections on t_classes_sections.classe_id = t_classes.oid
|
|
join rh.t_classes_sections_elements on t_classes_sections_elements.section_id = t_classes_sections.oid
|
|
UNION ALL
|
|
select
|
|
''{''||t_listes_tables.code||''}^[CLASSE:''||t_classes.code||'':''||t_classes_sections.code||'']'' as code,
|
|
t_classes_sections_elements.to_id as oid
|
|
from base.t_classes
|
|
join base.t_listes_tables on t_listes_tables.oid = t_classes.table_id
|
|
join base.t_classes_sections on t_classes_sections.classe_id = t_classes.oid
|
|
join base.t_classes_sections_elements on t_classes_sections_elements.section_id = t_classes_sections.oid
|
|
)
|
|
select
|
|
t_population_regle.oid,
|
|
population_id,
|
|
priorite
|
|
%s
|
|
from rh.t_population_regle
|
|
JOIN rh.t_population ON t_population.oid = t_population_regle.population_id
|
|
ORDER BY priorite, population_id
|
|
', tmpReq);
|
|
|
|
|
|
|
|
DROP TABLE IF EXISTS temp.toto
|
|
;
|
|
CREATE TABLE temp.toto AS
|
|
select * from w_population_regle
|
|
;
|
|
|
|
RAISE NOTICE '%' , tmpReq
|
|
;
|
|
|
|
|
|
RAISE NOTICE '%' , 'Application des regles.'
|
|
;
|
|
DROP TABLE IF EXISTS w_profil_contrat_mois
|
|
;
|
|
CREATE TEMP TABLE w_profil_contrat_mois AS
|
|
SELECT
|
|
p_profil_contrat_mois.oid,
|
|
COALESCE(MIN(priorite), 999999999) AS population_priorite,
|
|
COALESCE((MIN(ARRAY[priorite, w_population_regle.population_id]))[2], 0) AS population_id
|
|
FROM rh.p_profil_contrat_mois
|
|
JOIN rh.p_contrats_mois ON p_profil_contrat_mois.contrat_mois_id = p_contrats_mois.oid
|
|
JOIN rh.t_etablissements ON t_etablissements.oid = etablissement_id
|
|
JOIN rh.p_profils ON p_profil_contrat_mois.profil_id = p_profils.oid
|
|
JOIN w_population_regle ON true
|
|
AND (NOT has_liste_finess OR finess_id = ANY (liste_finess_id))
|
|
AND (NOT (has_exc_liste_finess AND liste_exc_finess_id IS NOT NULL) OR NOT (finess_id = ANY (liste_exc_finess_id)))
|
|
AND (NOT has_liste_etablissement OR etablissement_id = ANY (liste_etablissement_id))
|
|
AND (NOT (has_exc_liste_etablissement AND liste_exc_etablissement_id IS NOT NULL) OR NOT (etablissement_id = ANY (liste_exc_etablissement_id)))
|
|
AND (NOT has_liste_service OR p_profils.service_id = ANY (liste_service_id))
|
|
AND (NOT (has_exc_liste_service AND liste_exc_service_id IS NOT NULL) OR NOT (p_profils.service_id = ANY (liste_exc_service_id)))
|
|
AND (NOT has_liste_specialite OR p_profils.specialite_id = ANY (liste_specialite_id))
|
|
AND (NOT (has_exc_liste_specialite AND liste_exc_specialite_id IS NOT NULL) OR NOT (p_profils.specialite_id = ANY (liste_exc_specialite_id)))
|
|
AND (NOT has_liste_qualification OR p_profils.qualification_id = ANY (liste_qualification_id))
|
|
AND (NOT (has_exc_liste_qualification AND liste_exc_qualification_id IS NOT NULL) OR NOT (p_profils.qualification_id = ANY (liste_exc_qualification_id)))
|
|
AND (NOT has_liste_section_analytique OR p_profils.section_analytique_id = ANY (liste_section_analytique_id))
|
|
AND (NOT (has_exc_liste_section_analytique AND liste_exc_section_analytique_id IS NOT NULL) OR NOT (p_profils.section_analytique_id = ANY (liste_exc_section_analytique_id)))
|
|
AND (NOT has_liste_section_analytique_paie OR p_profils.section_analytique_paie_id = ANY (liste_section_analytique_paie_id))
|
|
AND (NOT (has_exc_liste_section_analytique_paie AND liste_exc_section_analytique_paie_id IS NOT NULL) OR NOT (p_profils.section_analytique_paie_id = ANY (liste_exc_section_analytique_paie_id)))
|
|
AND (NOT has_liste_grille OR p_profils.grille_id = ANY (liste_grille_id))
|
|
AND (NOT (has_exc_liste_grille AND liste_exc_grille_id IS NOT NULL) OR NOT (p_profils.grille_id = ANY (liste_exc_grille_id)))
|
|
AND (NOT has_liste_code_emploi OR p_profils.code_emploi_id = ANY (liste_code_emploi_id))
|
|
AND (NOT (has_exc_liste_code_emploi AND liste_exc_code_emploi_id IS NOT NULL) OR NOT (p_profils.code_emploi_id = ANY (liste_exc_code_emploi_id)))
|
|
AND (NOT has_liste_categorie_socio_professionnelle OR p_profils.categorie_socio_professionnelle_id = ANY (liste_categorie_socio_professionnelle_id))
|
|
AND (NOT (has_exc_liste_categorie_socio_professionnelle AND liste_exc_categorie_socio_professionnelle_id IS NOT NULL) OR NOT (p_profils.categorie_socio_professionnelle_id = ANY (liste_exc_categorie_socio_professionnelle_id)))
|
|
AND (NOT has_liste_statut OR p_profils.statut_id = ANY (liste_statut_id))
|
|
AND (NOT (has_exc_liste_statut AND liste_exc_statut_id IS NOT NULL) OR NOT (p_profils.statut_id = ANY (liste_exc_statut_id)))
|
|
GROUP BY 1
|
|
ORDER BY 2,1
|
|
;
|
|
|
|
DROP TABLE IF EXISTS w_contrats_mois
|
|
;
|
|
CREATE TEMP TABLE w_contrats_mois AS
|
|
SELECT
|
|
p_contrats_mois.oid,
|
|
COALESCE(MIN(priorite), 999999999) AS population_priorite,
|
|
COALESCE((MIN(ARRAY[priorite, w_population_regle.population_id]))[2], 0) AS population_id
|
|
FROM rh.p_contrats_mois
|
|
LEFT JOIN rh.t_etablissements ON t_etablissements.oid = etablissement_id
|
|
LEFT JOIN rh.p_profils ON p_contrats_mois.profil_id = p_profils.oid
|
|
JOIN w_population_regle ON true
|
|
AND (NOT has_liste_finess OR finess_id = ANY (liste_finess_id))
|
|
AND (NOT (has_exc_liste_finess AND liste_exc_finess_id IS NOT NULL) OR NOT (finess_id = ANY (liste_exc_finess_id)))
|
|
AND (NOT has_liste_etablissement OR etablissement_id = ANY (liste_etablissement_id))
|
|
AND (NOT (has_exc_liste_etablissement AND liste_exc_etablissement_id IS NOT NULL) OR NOT (etablissement_id = ANY (liste_exc_etablissement_id)))
|
|
AND (NOT has_liste_service OR p_profils.service_id = ANY (liste_service_id))
|
|
AND (NOT (has_exc_liste_service AND liste_exc_service_id IS NOT NULL) OR NOT (p_profils.service_id = ANY (liste_exc_service_id)))
|
|
AND (NOT has_liste_specialite OR p_profils.specialite_id = ANY (liste_specialite_id))
|
|
AND (NOT (has_exc_liste_specialite AND liste_exc_specialite_id IS NOT NULL) OR NOT (p_profils.specialite_id = ANY (liste_exc_specialite_id)))
|
|
AND (NOT has_liste_qualification OR p_profils.qualification_id = ANY (liste_qualification_id))
|
|
AND (NOT (has_exc_liste_qualification AND liste_exc_qualification_id IS NOT NULL) OR NOT (p_profils.qualification_id = ANY (liste_exc_qualification_id)))
|
|
AND (NOT has_liste_section_analytique OR p_profils.section_analytique_id = ANY (liste_section_analytique_id))
|
|
AND (NOT (has_exc_liste_section_analytique AND liste_exc_section_analytique_id IS NOT NULL) OR NOT (p_profils.section_analytique_id = ANY (liste_exc_section_analytique_id)))
|
|
AND (NOT has_liste_section_analytique_paie OR p_profils.section_analytique_paie_id = ANY (liste_section_analytique_paie_id))
|
|
AND (NOT (has_exc_liste_section_analytique_paie AND liste_exc_section_analytique_paie_id IS NOT NULL) OR NOT (p_profils.section_analytique_paie_id = ANY (liste_exc_section_analytique_paie_id)))
|
|
AND (NOT has_liste_grille OR p_profils.grille_id = ANY (liste_grille_id))
|
|
AND (NOT (has_exc_liste_grille AND liste_exc_grille_id IS NOT NULL) OR NOT (p_profils.grille_id = ANY (liste_exc_grille_id)))
|
|
AND (NOT has_liste_code_emploi OR p_profils.code_emploi_id = ANY (liste_code_emploi_id))
|
|
AND (NOT (has_exc_liste_code_emploi AND liste_exc_code_emploi_id IS NOT NULL) OR NOT (p_profils.code_emploi_id = ANY (liste_exc_code_emploi_id)))
|
|
AND (NOT has_liste_categorie_socio_professionnelle OR p_profils.categorie_socio_professionnelle_id = ANY (liste_categorie_socio_professionnelle_id))
|
|
AND (NOT (has_exc_liste_categorie_socio_professionnelle AND liste_exc_categorie_socio_professionnelle_id IS NOT NULL) OR NOT (p_profils.categorie_socio_professionnelle_id = ANY (liste_exc_categorie_socio_professionnelle_id)))
|
|
AND (NOT has_liste_statut OR p_profils.statut_id = ANY (liste_statut_id))
|
|
AND (NOT (has_exc_liste_statut AND liste_exc_statut_id IS NOT NULL) OR NOT (p_profils.statut_id = ANY (liste_exc_statut_id)))
|
|
GROUP BY 1
|
|
ORDER BY 1
|
|
;
|
|
|
|
DROP TABLE IF EXISTS w_contrats
|
|
;
|
|
CREATE TEMP TABLE w_contrats AS
|
|
SELECT
|
|
p_contrats.oid,
|
|
COALESCE(MIN(priorite), 999999999) AS population_priorite,
|
|
COALESCE((MIN(ARRAY[priorite, w_population_regle.population_id]))[2], 0) AS population_id
|
|
FROM rh.p_contrats
|
|
LEFT JOIN rh.t_etablissements ON t_etablissements.oid = etablissement_id
|
|
LEFT JOIN rh.p_profils ON p_contrats.profil_id = p_profils.oid
|
|
JOIN w_population_regle ON true
|
|
AND (NOT has_liste_finess OR finess_id = ANY (liste_finess_id))
|
|
AND (NOT (has_exc_liste_finess AND liste_exc_finess_id IS NOT NULL) OR NOT (finess_id = ANY (liste_exc_finess_id)))
|
|
AND (NOT has_liste_etablissement OR etablissement_id = ANY (liste_etablissement_id))
|
|
AND (NOT (has_exc_liste_etablissement AND liste_exc_etablissement_id IS NOT NULL) OR NOT (etablissement_id = ANY (liste_exc_etablissement_id)))
|
|
AND (NOT has_liste_service OR p_profils.service_id = ANY (liste_service_id))
|
|
AND (NOT (has_exc_liste_service AND liste_exc_service_id IS NOT NULL) OR NOT (p_profils.service_id = ANY (liste_exc_service_id)))
|
|
AND (NOT has_liste_specialite OR p_profils.specialite_id = ANY (liste_specialite_id))
|
|
AND (NOT (has_exc_liste_specialite AND liste_exc_specialite_id IS NOT NULL) OR NOT (p_profils.specialite_id = ANY (liste_exc_specialite_id)))
|
|
AND (NOT has_liste_qualification OR p_profils.qualification_id = ANY (liste_qualification_id))
|
|
AND (NOT (has_exc_liste_qualification AND liste_exc_qualification_id IS NOT NULL) OR NOT (p_profils.qualification_id = ANY (liste_exc_qualification_id)))
|
|
AND (NOT has_liste_section_analytique OR p_profils.section_analytique_id = ANY (liste_section_analytique_id))
|
|
AND (NOT (has_exc_liste_section_analytique AND liste_exc_section_analytique_id IS NOT NULL) OR NOT (p_profils.section_analytique_id = ANY (liste_exc_section_analytique_id)))
|
|
AND (NOT has_liste_section_analytique_paie OR p_profils.section_analytique_paie_id = ANY (liste_section_analytique_paie_id))
|
|
AND (NOT (has_exc_liste_section_analytique_paie AND liste_exc_section_analytique_paie_id IS NOT NULL) OR NOT (p_profils.section_analytique_paie_id = ANY (liste_exc_section_analytique_paie_id)))
|
|
AND (NOT has_liste_grille OR p_profils.grille_id = ANY (liste_grille_id))
|
|
AND (NOT (has_exc_liste_grille AND liste_exc_grille_id IS NOT NULL) OR NOT (p_profils.grille_id = ANY (liste_exc_grille_id)))
|
|
AND (NOT has_liste_code_emploi OR p_profils.code_emploi_id = ANY (liste_code_emploi_id))
|
|
AND (NOT (has_exc_liste_code_emploi AND liste_exc_code_emploi_id IS NOT NULL) OR NOT (p_profils.code_emploi_id = ANY (liste_exc_code_emploi_id)))
|
|
AND (NOT has_liste_categorie_socio_professionnelle OR p_profils.categorie_socio_professionnelle_id = ANY (liste_categorie_socio_professionnelle_id))
|
|
AND (NOT (has_exc_liste_categorie_socio_professionnelle AND liste_exc_categorie_socio_professionnelle_id IS NOT NULL) OR NOT (p_profils.categorie_socio_professionnelle_id = ANY (liste_exc_categorie_socio_professionnelle_id)))
|
|
AND (NOT has_liste_statut OR p_profils.statut_id = ANY (liste_statut_id))
|
|
AND (NOT (has_exc_liste_statut AND liste_exc_statut_id IS NOT NULL) OR NOT (p_profils.statut_id = ANY (liste_exc_statut_id)))
|
|
GROUP BY 1
|
|
ORDER BY 1
|
|
;
|
|
|
|
RAISE NOTICE '%' , 'Application des regles. Validation des Populations'
|
|
;
|
|
|
|
UPDATE rh.p_profil_contrat_mois SET
|
|
population_id[1] = w_profil_contrat_mois.population_id
|
|
FROM w_profil_contrat_mois
|
|
WHERE true
|
|
AND p_profil_contrat_mois.oid = w_profil_contrat_mois.oid
|
|
AND p_profil_contrat_mois.population_id[1] IS DISTINCT FROM w_profil_contrat_mois.population_id
|
|
;
|
|
|
|
UPDATE rh.p_profil_contrat_mois SET
|
|
population_id[1] = 0
|
|
WHERE true
|
|
AND oid NOT IN (SELECT oid FROM w_profil_contrat_mois)
|
|
AND population_id[1] IS DISTINCT FROM 0
|
|
;
|
|
|
|
UPDATE rh.p_contrats_mois SET
|
|
population_id[1] = w_contrats_mois.population_id
|
|
FROM w_contrats_mois
|
|
WHERE true
|
|
AND p_contrats_mois.oid = w_contrats_mois.oid
|
|
AND p_contrats_mois.population_id[1] IS DISTINCT FROM w_contrats_mois.population_id
|
|
;
|
|
|
|
UPDATE rh.p_contrats SET
|
|
population_id[1] = w_contrats.population_id
|
|
FROM w_contrats
|
|
WHERE true
|
|
AND p_contrats.oid = w_contrats.oid
|
|
AND p_contrats.population_id[1] IS DISTINCT FROM w_contrats.population_id
|
|
;
|
|
|
|
ANALYSE rh.p_profil_contrat_mois
|
|
;
|
|
ANALYSE rh.p_contrats_mois
|
|
;
|
|
ANALYSE rh.p_contrats
|
|
;
|
|
|
|
INSERT INTO rh.p_oids (code_table, oid)
|
|
SELECT 'population', population_id[1]
|
|
FROM rh.p_profil_contrat_mois
|
|
WHERE population_id[1] NOT IN (SELECT oid FROM rh.p_oids WHERE code_table = 'population')
|
|
GROUP BY 2
|
|
;
|
|
RETURN 'OK'
|
|
;
|
|
END;
|