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.
256 lines
9.5 KiB
256 lines
9.5 KiB
return: text
|
|
lang: plpgsql
|
|
src: |
|
|
DECLARE
|
|
_sqlcmd TEXT;
|
|
result TEXT;
|
|
BEGIN
|
|
-- Initialisation du nouveau champ
|
|
UPDATE rh.p_salaries
|
|
SET salarie_fusionne_id = 0
|
|
WHERE salarie_fusionne_id IS NULL;
|
|
UPDATE rh.p_salaries
|
|
SET salarie_fusionne_id = 0
|
|
WHERE salarie_fusionne_id <> 0 AND
|
|
salarie_fusionne_id NOT IN (SELECT oid FROM rh.p_salaries);
|
|
|
|
-- Paramétrés
|
|
INSERT INTO rh.t_divers (code, texte, valeur, description)
|
|
SELECT
|
|
'FUSIONSALARIE',
|
|
'Fusion des salariés',
|
|
'0',
|
|
'1 = Fusion des salariés activée'
|
|
WHERE 'FUSIONSALARIE' NOT IN (SELECT code FROM rh.t_divers);
|
|
IF NOT EXISTS (SELECT *
|
|
FROM rh.t_divers
|
|
WHERE t_divers.code = 'FUSIONSALARIE' AND
|
|
t_divers.valeur = '1'
|
|
LIMIT 1) THEN
|
|
return 'Fusion non activée';
|
|
END IF;
|
|
-- Stockage des salaries par nom
|
|
DROP TABLE IF EXISTS w_fusion_salaries;
|
|
CREATE TEMP TABLE w_fusion_salaries AS
|
|
SELECT *,
|
|
'0'::text AS origine,
|
|
base.cti_soundex_nom(nom) AS nom_soundex,
|
|
base.cti_soundex_prenom(prenom) AS prenom_soundex
|
|
FROM rh.p_salaries
|
|
WHERE nom <> '' AND
|
|
(nom_naissance = nom OR nom_naissance = '') AND
|
|
oid IN (SELECT salarie_id FROM rh.p_contrats);
|
|
-- Stockage des salaries par nom de naissance
|
|
INSERT INTO w_fusion_salaries
|
|
SELECT *,
|
|
'1'::text AS origine,
|
|
base.cti_soundex_nom(nom_naissance) AS nom_soundex,
|
|
base.cti_soundex_prenom(prenom) AS prenom_soundex
|
|
FROM rh.p_salaries
|
|
WHERE nom <> '' AND
|
|
nom_naissance <> '' AND
|
|
nom_naissance <> nom AND
|
|
oid IN (SELECT salarie_id FROM rh.p_contrats);
|
|
|
|
-- Détection salariés à regrouper et regrouper sur le plus récent
|
|
UPDATE rh.p_salaries
|
|
SET salarie_fusionne_id = CASE WHEN p_salaries.oid <> subview.salarie_fusionne_id THEN subview.salarie_fusionne_id ELSE 0 END
|
|
FROM
|
|
(
|
|
SELECT
|
|
w_fusion_salaries.oid,
|
|
subview.salarie_fusionne_id,
|
|
w_fusion_salaries.entreprise_id,
|
|
w_fusion_salaries.nom_soundex,
|
|
w_fusion_salaries.prenom_soundex,
|
|
w_fusion_salaries.date_naissance,
|
|
w_fusion_salaries.sexe
|
|
FROM w_fusion_salaries
|
|
JOIN
|
|
(
|
|
SELECT
|
|
entreprise_id,
|
|
nom_soundex,
|
|
prenom_soundex,
|
|
date_naissance,
|
|
sexe,
|
|
count(*) AS nb,
|
|
(MAX(ARRAY[date_debut::text,oid::text]))[2]::bigint AS salarie_fusionne_id,
|
|
base.cti_array_accum(w_fusion_salaries.oid) AS salaries_array
|
|
FROM w_fusion_salaries
|
|
GROUP BY 1,2,3,4,5
|
|
HAVING count(*) > 1
|
|
) subview ON
|
|
w_fusion_salaries.oid = ANY(salaries_array)
|
|
ORDER BY 1,2,3,4,5,6
|
|
) subview
|
|
WHERE subview.oid = p_salaries.oid
|
|
;
|
|
|
|
-- Mise à jour du salarié cible
|
|
UPDATE rh.p_salaries SET
|
|
date_debut = subview.date_debut,
|
|
date_fin = subview.date_fin,
|
|
date_entree_ets = subview.date_entree_ets,
|
|
date_entree_fp = subview.date_entree_fp,
|
|
date_entree_fph = subview.date_entree_fph,
|
|
date_sortie_ets = subview.date_sortie_ets
|
|
FROM
|
|
(
|
|
SELECT
|
|
CASE WHEN salarie_fusionne_id <> 0 THEN salarie_fusionne_id ELSE oid END,
|
|
MIN(date_debut) AS date_debut,
|
|
MAX(date_fin) AS date_fin,
|
|
MIN(date_entree_ets) AS date_entree_ets,
|
|
MIN(date_entree_fp) AS date_entree_fp,
|
|
MIN(date_entree_fph) AS date_entree_fph,
|
|
MAX(date_sortie_ets) AS date_sortie_ets
|
|
FROM rh.p_salaries
|
|
GROUP BY 1
|
|
HAVING COUNT(*) > 1
|
|
) subview
|
|
WHERE p_salaries.oid = subview.oid;
|
|
-- Mise à jour référence salarié dans contrats
|
|
UPDATE rh.p_contrats
|
|
SET salarie_id = p_salaries.salarie_fusionne_id
|
|
FROM rh.p_salaries
|
|
WHERE p_contrats.salarie_id = p_salaries.oid AND
|
|
salarie_fusionne_id <> 0 AND
|
|
salarie_fusionne_id <> salarie_id;
|
|
UPDATE rh.p_contrats
|
|
SET salarie_remplace_id = p_salaries.salarie_fusionne_id
|
|
FROM rh.p_salaries
|
|
WHERE p_contrats.salarie_remplace_id <> 0 AND
|
|
p_contrats.salarie_remplace_id = p_salaries.oid AND
|
|
salarie_fusionne_id <> 0 AND
|
|
salarie_fusionne_id <> salarie_remplace_id;
|
|
--fusion ancienneté sans prendre en compte l'entreprise
|
|
INSERT INTO rh.t_divers (code, texte, valeur, description)
|
|
SELECT
|
|
'FUSIONENTREESALARIE',
|
|
'Fusion des dates entrées salariés (quelque soit entreprise)',
|
|
'0',
|
|
'1 = Fusion des dates entrée salariés activée'
|
|
WHERE 'FUSIONENTREESALARIE' NOT IN (SELECT code FROM rh.t_divers);
|
|
-- Mise à jour du salarié cible
|
|
UPDATE rh.p_salaries SET
|
|
date_entree_ets = subview.date_entree_ets
|
|
FROM
|
|
(
|
|
SELECT
|
|
w_fusion_salaries.nom_soundex,
|
|
w_fusion_salaries.prenom_soundex,
|
|
w_fusion_salaries.date_naissance,
|
|
w_fusion_salaries.sexe,
|
|
(MAX(ARRAY[date_debut::text,oid::text]))[2]::bigint AS salarie_fusionne_id,
|
|
base.cti_array_accum(w_fusion_salaries.oid) AS salaries_array,
|
|
MIN(date_debut) AS date_debut,
|
|
MAX(date_fin) AS date_fin,
|
|
MIN(date_entree_ets) AS date_entree_ets,
|
|
MIN(date_entree_fp) AS date_entree_fp,
|
|
MIN(date_entree_fph) AS date_entree_fph,
|
|
MAX(date_sortie_ets) AS date_sortie_ets
|
|
FROM w_fusion_salaries
|
|
GROUP BY 1,2,3,4
|
|
HAVING COUNT(*) > 1
|
|
) subview
|
|
WHERE p_salaries.oid = ANY(salaries_array)
|
|
AND (SELECT valeur = 1 FROM rh.t_divers WHERE code = 'FUSIONENTREESALARIE');
|
|
|
|
UPDATE rh.p_contrats SET
|
|
anciennete_anterieure_jours = subview.anciennete_anterieure_jours,
|
|
anciennete_anterieure_calculee_mois = subview.anciennete_anterieure_calculee_mois
|
|
FROM
|
|
(
|
|
SELECT p_contrats.oid,
|
|
SUM(p_contrats_anterieurs.date_fin-p_contrats_anterieurs.date_debut+1) AS anciennete_anterieure_jours,
|
|
floor(SUM(p_contrats_anterieurs.date_fin-p_contrats_anterieurs.date_debut+1)/30.00) AS anciennete_anterieure_calculee_mois
|
|
FROM rh.p_contrats
|
|
JOIN rh.p_contrats p_contrats_anterieurs ON
|
|
p_contrats_anterieurs.salarie_id = p_contrats.salarie_id AND
|
|
p_contrats_anterieurs.etablissement_id = p_contrats.etablissement_id AND
|
|
p_contrats_anterieurs.date_debut < p_contrats.date_debut
|
|
WHERE p_contrats.salarie_id <> 0 AND
|
|
p_contrats.salarie_id IN (SELECT salarie_fusionne_id FROM rh.p_salaries)
|
|
|
|
GROUP BY 1
|
|
) subview
|
|
WHERE p_contrats.oid = subview.oid;
|
|
-- Mise à jour référence salarié dans contrats mois
|
|
UPDATE rh.p_contrats_mois
|
|
SET salarie_id = p_salaries.salarie_fusionne_id
|
|
FROM rh.p_salaries
|
|
WHERE p_contrats_mois.salarie_id = p_salaries.oid AND
|
|
salarie_fusionne_id <> 0 AND
|
|
salarie_fusionne_id <> p_contrats_mois.salarie_id;
|
|
UPDATE rh.p_contrats_mois SET
|
|
anciennete_jours = p_contrats_mois.date_fin - p_contrats.date_debut + 1 + p_contrats.anciennete_anterieure_jours,
|
|
anciennete_mois = floor((p_contrats_mois.date_fin - p_contrats.date_debut + 1 + p_contrats.anciennete_anterieure_jours)/30.00),
|
|
anciennete_annee_id = floor((p_contrats_mois.date_fin - p_contrats.date_debut + 1 + p_contrats.anciennete_anterieure_jours)/365.25),
|
|
nombre_entrees = CASE WHEN date_entree_ets BETWEEN p_contrats_mois.date_debut AND p_contrats_mois.date_fin THEN 1 ELSE 0 END
|
|
FROM rh.p_contrats
|
|
JOIN rh.p_salaries ON p_contrats.salarie_id = p_salaries.oid
|
|
WHERE p_contrats_mois.contrat_id = p_contrats.oid AND
|
|
p_contrats.salarie_id IN (SELECT salarie_fusionne_id FROM rh.p_salaries)
|
|
;
|
|
UPDATE rh.p_contrats_mois
|
|
SET nombre_entrees = 0
|
|
FROM
|
|
(SELECT salarie_id, (MIN(Array[date_debut::text, oid::text]))[2] AS oid_ok FROM rh.p_contrats_mois WHERE nombre_entrees = 1 GROUP BY 1 HAVING count(*) > 1) subview
|
|
WHERE p_contrats_mois.salarie_id = subview.salarie_id AND
|
|
p_contrats_mois.nombre_entrees = 1 AND
|
|
p_contrats_mois.oid <> oid_ok
|
|
;
|
|
|
|
UPDATE rh.p_profil_contrat_mois
|
|
SET salarie_id = p_salaries.salarie_fusionne_id
|
|
FROM rh.p_salaries
|
|
WHERE p_profil_contrat_mois.salarie_id = p_salaries.oid AND
|
|
salarie_fusionne_id <> 0 AND
|
|
salarie_fusionne_id <> p_profil_contrat_mois.salarie_id;
|
|
-- Mise à jour référence salarié dans autres tables
|
|
UPDATE rh.p_historique_paie
|
|
SET salarie_id = p_salaries.salarie_fusionne_id
|
|
FROM rh.p_salaries
|
|
WHERE p_historique_paie.salarie_id = p_salaries.oid AND
|
|
salarie_fusionne_id <> 0 AND
|
|
salarie_fusionne_id <> p_historique_paie.salarie_id;
|
|
UPDATE rh.p_accident_travail
|
|
SET salarie_id = p_salaries.salarie_fusionne_id
|
|
FROM rh.p_salaries
|
|
WHERE p_accident_travail.salarie_id = p_salaries.oid AND
|
|
salarie_fusionne_id <> 0 AND
|
|
salarie_fusionne_id <> p_accident_travail.salarie_id;
|
|
UPDATE rh.p_arrets_travail
|
|
SET salarie_id = p_salaries.salarie_fusionne_id
|
|
FROM rh.p_salaries
|
|
WHERE p_arrets_travail.salarie_id = p_salaries.oid AND
|
|
salarie_fusionne_id <> 0 AND
|
|
salarie_fusionne_id <> p_arrets_travail.salarie_id;
|
|
UPDATE rh.p_arrets_travail_mois
|
|
SET salarie_id = p_salaries.salarie_fusionne_id
|
|
FROM rh.p_salaries
|
|
WHERE p_arrets_travail_mois.salarie_id = p_salaries.oid AND
|
|
salarie_fusionne_id <> 0 AND
|
|
salarie_fusionne_id <> p_arrets_travail_mois.salarie_id;
|
|
UPDATE rh.p_formations
|
|
SET salarie_id = p_salaries.salarie_fusionne_id
|
|
FROM rh.p_salaries
|
|
WHERE p_formations.salarie_id = p_salaries.oid AND
|
|
salarie_fusionne_id <> 0 AND
|
|
salarie_fusionne_id <> p_formations.salarie_id;
|
|
UPDATE rh.p_handicap
|
|
SET salarie_id = p_salaries.salarie_fusionne_id
|
|
FROM rh.p_salaries
|
|
WHERE p_handicap.salarie_id = p_salaries.oid AND
|
|
salarie_fusionne_id <> 0 AND
|
|
salarie_fusionne_id <> p_handicap.salarie_id;
|
|
UPDATE rh.p_visites_medicales
|
|
SET salarie_id = p_salaries.salarie_fusionne_id
|
|
FROM rh.p_salaries
|
|
WHERE p_visites_medicales.salarie_id = p_salaries.oid AND
|
|
salarie_fusionne_id <> 0 AND
|
|
salarie_fusionne_id <> p_visites_medicales.salarie_id;
|
|
|
|
RETURN 'OK';
|
|
END;
|