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.
102 lines
4.0 KiB
102 lines
4.0 KiB
return: text
|
|
lang: plpgsql
|
|
src: |
|
|
DECLARE
|
|
result TEXT;
|
|
_champs TEXT[];
|
|
_i INTEGER;
|
|
_table TEXT;
|
|
_champ TEXT;
|
|
_affectations TEXT;
|
|
_selects TEXT;
|
|
_tests TEXT;
|
|
_joins TEXT;
|
|
_sqlcmd TEXT;
|
|
_affected_rows bigint;
|
|
BEGIN
|
|
|
|
-- Salariés sans profil
|
|
UPDATE rh.p_salaries
|
|
SET profil_id = 0
|
|
WHERE profil_id IS NULL;
|
|
|
|
INSERT INTO rh.p_profils(oid)
|
|
SELECT 0
|
|
WHERE 0 NOT IN (SELECT oid FROM rh.p_profils) AND
|
|
0 IN (SELECT profil_id FROM rh.p_salaries WHERE profil_id = 0);
|
|
-- Initialisation des champs
|
|
_champs = Array[
|
|
't_types_contrat', 'type_contrat',
|
|
't_motifs_debut_contrat', 'motif_debut',
|
|
't_motifs_fin_contrat', 'motif_fin',
|
|
't_qualifications', 'qualification',
|
|
't_services', 'service',
|
|
't_specialites', 'specialite',
|
|
't_types_temps_travail', 'type_temps_travail',
|
|
't_categories_socio_professionnelle', 'categorie_socio_professionnelle',
|
|
't_statuts', 'statut',
|
|
't_codes_emploi', 'code_emploi',
|
|
't_societes_interim', 'societe_interim',
|
|
't_sections_analytiques_paie', 'section_analytique_paie',
|
|
't_sections_analytiques', 'section_analytique',
|
|
't_grilles', 'grille',
|
|
't_grilles_groupes', 'grille_groupe',
|
|
't_types_horaire', 'type_horaire',
|
|
't_groupes_cotisant', 'groupe_cotisant',
|
|
't_cadre_emploi', 'cadre_emploi',
|
|
't_categorie_conge', 'categorie_conge',
|
|
't_categorie_statutaire', 'categorie_statutaire',
|
|
't_commission_paritaire', 'commission_paritaire',
|
|
't_compte', 'compte_salarie',
|
|
't_filiere', 'filiere',
|
|
't_lettre_budgetaire', 'lettre_budgetaire',
|
|
't_unite_fonctionnelle', 'unite_fonctionnelle'
|
|
];
|
|
_i = 0;
|
|
_affectations = '';
|
|
_selects = '';
|
|
_joins = '';
|
|
_tests = '';
|
|
WHILE _i <= 48 LOOP
|
|
_i = _i + 1;
|
|
_table = _champs[_i];
|
|
_i = _i + 1;
|
|
_champ = _champs[_i];
|
|
IF _affectations <> '' THEN
|
|
_affectations = _affectations || ', ' || chr(10);
|
|
END IF;
|
|
IF _selects <> '' THEN
|
|
_selects = _selects || ', ' || chr(10);
|
|
END IF;
|
|
IF _tests <> '' THEN
|
|
_tests = _tests || ' OR ' || chr(10);
|
|
END IF;
|
|
_affectations = _affectations || _champ || '_id = new_' || _champ || '_id, ' || chr(10);
|
|
_affectations = _affectations || _champ || '_code = new_' || _champ || '_code, ' || chr(10);
|
|
_affectations = _affectations || _champ || '_texte = new_' || _champ || '_texte';
|
|
_selects = _selects || 'COALESCE(' || _table || '.oid,0) AS new_' || _champ || '_id, ' || chr(10);
|
|
_selects = _selects || 'COALESCE(' || _table || '.code,' || _table || '_0.code) AS new_' || _champ || '_code, ' || chr(10);
|
|
_selects = _selects || 'COALESCE(' || _table || '.texte,' || _table || '_0.texte) AS new_' || _champ || '_texte';
|
|
_joins = _joins || chr(10) || 'LEFT JOIN rh.' || _table || ' ' || _table || '_0 ON ' || _table || '_0.oid = 0';
|
|
_joins = _joins || chr(10) || 'LEFT JOIN rh.' || _table || ' ON ' || _table || '.code_original = ' || _champ || '_code_original';
|
|
_tests = _tests || _champ || '_id IS DISTINCT FROM new_' || _champ || '_id OR ' || chr(10);
|
|
_tests = _tests || _champ || '_code IS DISTINCT FROM new_' || _champ || '_code OR ' || chr(10);
|
|
_tests = _tests || _champ || '_texte IS DISTINCT FROM new_' || _champ || '_texte';
|
|
|
|
END LOOP;
|
|
_sqlcmd = 'UPDATE rh.p_profils SET' || chr(10);
|
|
_sqlcmd = _sqlcmd || _affectations || chr(10);
|
|
_sqlcmd = _sqlcmd || 'FROM (' || chr(10);
|
|
_sqlcmd = _sqlcmd || 'SELECT' || chr(10);
|
|
_sqlcmd = _sqlcmd || 'p_profils.oid AS new_profil_id,' || chr(10);
|
|
_sqlcmd = _sqlcmd || _selects || chr(10);
|
|
_sqlcmd = _sqlcmd || 'FROM rh.p_profils' || chr(10);
|
|
_sqlcmd = _sqlcmd || _joins || chr(10);
|
|
_sqlcmd = _sqlcmd || ') subview' || chr(10);
|
|
_sqlcmd = _sqlcmd || 'WHERE p_profils.oid = new_profil_id AND' || chr(10);
|
|
_sqlcmd = _sqlcmd || '(' || _tests || ')';
|
|
EXECUTE _sqlcmd;
|
|
GET DIAGNOSTICS _affected_rows = ROW_COUNT;
|
|
|
|
return (_affected_rows::text || ' ligne(s) modifiée(s)')::text;
|
|
END;
|