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.
313 lines
14 KiB
313 lines
14 KiB
return: text
|
|
lang: plpgsql
|
|
src: |
|
|
DECLARE
|
|
|
|
|
|
|
|
result TEXT;
|
|
BEGIN
|
|
-- Réplication des médecins dans Base
|
|
IF EXISTS (SELECT * FROM activite.t_medecins_administratifs WHERE oid <> 0 AND COALESCE(medecin_id,0) = 0) THEN
|
|
BEGIN
|
|
PERFORM base.cti_initialize_medecins();
|
|
EXCEPTION
|
|
WHEN others THEN RAISE NOTICE 'Erreur %' , 'base.cti_initialize_medecins';
|
|
END;
|
|
END IF;
|
|
|
|
BEGIN
|
|
-- Mise à jour code médecin
|
|
UPDATE base.t_medecins
|
|
SET code = subview.code
|
|
FROM
|
|
(
|
|
SELECT medecin_id, (MAX(Array[date_entree::text, t_medecins_administratifs.code]))[2] AS code
|
|
FROM activite.p_sejours
|
|
JOIN activite.t_medecins_administratifs ON medecin_sejour_id = t_medecins_administratifs.oid
|
|
JOIN base.t_medecins ON t_medecins_administratifs.medecin_id = t_medecins.oid AND t_medecins.oid <> 0 AND (t_medecins.code = '' OR t_medecins.code IS NULL)
|
|
GROUP BY 1
|
|
) subview
|
|
WHERE t_medecins.oid = subview.medecin_id;
|
|
UPDATE base.t_medecins
|
|
SET code = subview.code
|
|
FROM
|
|
(
|
|
SELECT medecin_id, (MAX(Array[date_debut::text, t_medecins_administratifs.code]))[2] AS code
|
|
FROM activite.p_factures_lignes_h
|
|
JOIN activite.t_medecins_administratifs ON p_factures_lignes_h.medecin_facture_id = t_medecins_administratifs.oid
|
|
JOIN base.t_medecins ON t_medecins_administratifs.medecin_id = t_medecins.oid AND t_medecins.oid <> 0 AND (t_medecins.code = '' OR t_medecins.code IS NULL)
|
|
GROUP BY 1
|
|
) subview
|
|
WHERE t_medecins.oid = subview.medecin_id;
|
|
UPDATE base.t_medecins
|
|
SET code = subview.code
|
|
FROM
|
|
(
|
|
SELECT medecin_id, (MAX(Array[date_debut::text, t_medecins_administratifs.code]))[2] AS code
|
|
FROM activite.p_factures_lignes_non_facturees_h
|
|
JOIN activite.t_medecins_administratifs ON p_factures_lignes_non_facturees_h.medecin_facture_id = t_medecins_administratifs.oid
|
|
JOIN base.t_medecins ON t_medecins_administratifs.medecin_id = t_medecins.oid AND t_medecins.oid <> 0 AND (t_medecins.code = '' OR t_medecins.code IS NULL)
|
|
GROUP BY 1
|
|
) subview
|
|
WHERE t_medecins.oid = subview.medecin_id;
|
|
UPDATE base.t_medecins
|
|
SET code = subview.code
|
|
FROM
|
|
(
|
|
SELECT medecin_id, (MIN(Array[to_char(t_medecins_administratifs.oid,'FM000000000000000000000000'), t_medecins_administratifs.code]))[2] AS code
|
|
FROM activite.t_medecins_administratifs
|
|
JOIN base.t_medecins ON t_medecins_administratifs.medecin_id = t_medecins.oid AND t_medecins.oid <> 0 AND (t_medecins.code = '' OR t_medecins.code IS NULL)
|
|
GROUP BY 1
|
|
) subview
|
|
WHERE t_medecins.oid = subview.medecin_id;
|
|
UPDATE base.t_medecins
|
|
SET code = subview.code
|
|
FROM
|
|
(
|
|
SELECT t_medecins_administratifs.medecin_id, MAX(t_medecins_administratifs.code) AS code
|
|
FROM activite.t_medecins_administratifs
|
|
JOIN base.t_medecins ON t_medecins_administratifs.medecin_id = t_medecins.oid
|
|
LEFT JOIN activite.t_budget_cle_rule ON
|
|
t_medecins.code <> '' AND
|
|
t_budget_cle_rule.liste_medecin LIKE '(R)%' AND
|
|
t_budget_cle_rule.liste_medecin LIKE '%'||t_medecins.code||'%'
|
|
WHERE t_medecins_administratifs.oid <> 0 AND
|
|
t_medecins_administratifs.medecin_id <> 0 AND
|
|
t_budget_cle_rule.oid IS NULL
|
|
GROUP BY 1
|
|
HAVING count(*) = 1 AND
|
|
MAX(t_medecins_administratifs.code) <> MAX(t_medecins.code)
|
|
) subview
|
|
WHERE t_medecins.oid = subview.medecin_id
|
|
;
|
|
EXCEPTION
|
|
WHEN others THEN RAISE NOTICE 'Erreur %' , '1';
|
|
END;
|
|
BEGIN
|
|
INSERT INTO base.t_specialites_medecin (code_original, code, texte, texte_court)
|
|
SELECT t_specialites_internes_pmsi.code, t_specialites_internes_pmsi.code, t_specialites_internes_pmsi.texte, t_specialites_internes_pmsi.texte
|
|
FROM base.t_medecins
|
|
JOIN activite.t_medecins_administratifs ON medecin_id = t_medecins.oid
|
|
JOIN pmsi.t_medecins t_medecins_pmsi ON t_medecins_administratifs.code = t_medecins_pmsi.code
|
|
JOIN pmsi.t_specialites_internes_medecin t_specialites_internes_pmsi ON t_medecins_pmsi.specialite_interne_id = t_specialites_internes_pmsi.oid
|
|
WHERE t_medecins.specialite_id = 0 AND
|
|
t_specialites_internes_pmsi.code NOT IN (SELECT code_original FROM base.t_specialites_medecin)
|
|
GROUP BY 1,2,3,4;
|
|
EXCEPTION
|
|
WHEN others THEN RAISE NOTICE 'Erreur %' , '2';
|
|
END;
|
|
|
|
|
|
BEGIN
|
|
UPDATE base.t_medecins
|
|
SET specialite_id = t_specialites_medecin.oid
|
|
FROM activite.t_medecins_administratifs
|
|
JOIN pmsi.t_medecins t_medecins_pmsi ON t_medecins_administratifs.code = t_medecins_pmsi.code
|
|
JOIN pmsi.t_specialites_internes_medecin t_specialites_internes_pmsi ON t_medecins_pmsi.specialite_interne_id = t_specialites_internes_pmsi.oid
|
|
JOIN base.t_specialites_medecin ON t_specialites_internes_pmsi.code = t_specialites_medecin.code_original
|
|
WHERE t_medecins_administratifs.medecin_id = t_medecins.oid AND
|
|
t_medecins.specialite_id = 0;
|
|
EXCEPTION
|
|
WHEN others THEN RAISE NOTICE 'Erreur %' , '3';
|
|
END;
|
|
|
|
-- Mise à jour du code rpps médecin des médecins administratifs depuis les données du module base
|
|
BEGIN
|
|
UPDATE activite.t_medecins_administratifs
|
|
SET code_rpps = subview.code_rpps
|
|
FROM
|
|
(
|
|
SELECT t_medecins.code_rpps, t_medecins.oid
|
|
FROM base.t_medecins
|
|
JOIN activite.t_medecins_administratifs ON t_medecins_administratifs.medecin_id = t_medecins.oid
|
|
WHERE t_medecins.code_rpps <> ''
|
|
AND medecin_id <> 0
|
|
) subview
|
|
WHERE t_medecins_administratifs.code_rpps = ''
|
|
AND t_medecins_administratifs.medecin_id = subview.oid
|
|
;
|
|
EXCEPTION
|
|
WHEN others THEN RAISE NOTICE 'Erreur %' , 'Mise à jour des code rpps médecins';
|
|
END;
|
|
-- Conditions d'exercice, alimentation automatique d'une classe
|
|
BEGIN
|
|
INSERT INTO activite.t_classes(
|
|
code,
|
|
texte,
|
|
table_id,
|
|
is_cti,
|
|
sequence,
|
|
contenu_by_code,
|
|
contenu_non_replicable_ailleurs
|
|
)
|
|
SELECT
|
|
'CTI_MEDLIBSAL'::text AS code,
|
|
'Conditions d''exercice'::text AS texte,
|
|
t_listes_tables.oid AS table_id,
|
|
'1'::text AS is_cti,
|
|
(SELECT COALESCE(MAX(sequence),0)+1 FROM activite.t_classes JOIN activite.t_listes_tables ON t_classes.table_id = t_listes_tables.oid AND t_listes_tables.code = 'MEDECIN' WHERE sequence < 30) AS sequence,
|
|
'0'::text AS contenu_by_code,
|
|
'1'::text AS contenu_non_replicable_ailleurs
|
|
FROM activite.t_listes_tables
|
|
WHERE t_listes_tables.Code = 'MEDECIN' AND
|
|
'CTI_MEDLIBSAL'::text NOT IN (SELECT code FROM activite.t_classes)
|
|
;
|
|
|
|
INSERT INTO activite.t_classes_sections(
|
|
code,
|
|
texte,
|
|
classe_id,
|
|
condition,
|
|
include_code,
|
|
exclude_code
|
|
)
|
|
SELECT
|
|
'1'::text AS code,
|
|
'Libéraux'::text AS texte,
|
|
t_classes.oid AS classe_id,
|
|
''::text AS condition,
|
|
''::text AS include_code,
|
|
''::text AS exclude_code
|
|
FROM activite.t_classes
|
|
WHERE t_classes.code = 'CTI_MEDLIBSAL' AND
|
|
(t_classes.oid::text||'-1') NOT IN (SELECT classe_id::text||'-'||code FROM activite.t_classes_sections WHERE code IS NOT NULL)
|
|
;
|
|
|
|
INSERT INTO activite.t_classes_sections(
|
|
code,
|
|
texte,
|
|
classe_id,
|
|
condition,
|
|
include_code,
|
|
exclude_code
|
|
)
|
|
SELECT
|
|
'2'::text AS code,
|
|
'Salariés'::text AS texte,
|
|
t_classes.oid AS classe_id,
|
|
''::text AS condition,
|
|
''::text AS include_code,
|
|
''::text AS exclude_code
|
|
FROM activite.t_classes
|
|
WHERE t_classes.code = 'CTI_MEDLIBSAL' AND
|
|
(t_classes.oid::text||'-2') NOT IN (SELECT classe_id::text||'-'||code FROM activite.t_classes_sections WHERE code IS NOT NULL)
|
|
;
|
|
|
|
|
|
DROP TABLE IF EXISTS w_classe_CTI_MEDLIBSAL;
|
|
CREATE TEMP TABLE w_classe_CTI_MEDLIBSAL AS
|
|
SELECT t_medecins_administratifs.oid AS to_id,
|
|
MIN(classe_id) AS classe_id,
|
|
MIN(section_id_1) AS section_id_1,
|
|
MIN(section_id_2) AS section_id_2,
|
|
MIN(CASE WHEN est_medecin_salarie = '1' OR t_medecins.numero_ordre = t_finess.code THEN section_id_2 ELSE section_id_1 END) AS section_id
|
|
FROM activite.t_medecins_administratifs
|
|
LEFT JOIN base.t_finess ON t_finess.secondaire = '0'
|
|
LEFT JOIN base.t_medecins ON t_medecins_administratifs.medecin_id = t_medecins.oid
|
|
JOIN
|
|
(
|
|
SELECT classe_id,
|
|
MAX(CASE WHEN t_classes_sections.code = '1' THEN t_classes_sections.oid ELSE 0 END) AS section_id_1,
|
|
MAX(CASE WHEN t_classes_sections.code = '2' THEN t_classes_sections.oid ELSE 0 END) AS section_id_2
|
|
FROM activite.t_classes
|
|
JOIN activite.t_classes_sections ON classe_id = t_classes.oid
|
|
WHERE t_classes.code = 'CTI_MEDLIBSAL'
|
|
GROUP BY 1
|
|
) subview ON 1=1
|
|
GROUP BY 1
|
|
;
|
|
|
|
DELETE
|
|
FROM activite.t_classes_sections_elements
|
|
USING w_classe_CTI_MEDLIBSAL
|
|
WHERE t_classes_sections_elements.to_id = w_classe_CTI_MEDLIBSAL.to_id AND
|
|
t_classes_sections_elements.section_id = section_id_2 AND
|
|
w_classe_CTI_MEDLIBSAL.section_id = section_id_1
|
|
;
|
|
|
|
DELETE
|
|
FROM activite.t_classes_sections_elements
|
|
USING w_classe_CTI_MEDLIBSAL
|
|
WHERE t_classes_sections_elements.to_id = w_classe_CTI_MEDLIBSAL.to_id AND
|
|
t_classes_sections_elements.section_id = section_id_1 AND
|
|
w_classe_CTI_MEDLIBSAL.section_id = section_id_2
|
|
;
|
|
|
|
INSERT INTO activite.t_classes_sections_elements(section_id, to_id)
|
|
SELECT section_id, to_id
|
|
FROM w_classe_CTI_MEDLIBSAL
|
|
WHERE (section_id::text||to_id::text) NOT IN (SELECT (section_id::text||to_id::text) FROM activite.t_classes_sections_elements)
|
|
;
|
|
|
|
PERFORM activite.cti_update_schema_classes('MEDECIN')
|
|
;
|
|
EXCEPTION
|
|
WHEN others THEN RAISE NOTICE 'Erreur %' , '4';
|
|
END;
|
|
|
|
BEGIN
|
|
UPDATE activite.t_medecins_administratifs_c
|
|
SET
|
|
adm_code = t_medecins_administratifs.code,
|
|
adm_nom = t_medecins_administratifs.nom,
|
|
adm_prenom = t_medecins_administratifs.prenom,
|
|
adm_nom_prenom = (t_medecins_administratifs.nom || ' ') || t_medecins_administratifs.prenom,
|
|
medecin_id = t_medecins_administratifs.medecin_id,
|
|
medecin_code = COALESCE(t_medecins.code,''),
|
|
nom = t_medecins.nom,
|
|
prenom = t_medecins.prenom,
|
|
nom_prenom = (t_medecins.nom || ' ') || t_medecins.prenom,
|
|
specialite_id = t_medecins.specialite_id,
|
|
specialite_code = t_specialites_medecin.code,
|
|
specialite_texte = t_specialites_medecin.texte_court
|
|
FROM activite.t_medecins_administratifs, base.t_medecins, base.t_specialites_medecin
|
|
WHERE t_medecins_administratifs_c.oid = t_medecins_administratifs.oid
|
|
AND t_medecins_administratifs.medecin_id = t_medecins.oid
|
|
AND t_medecins.specialite_id = t_specialites_medecin.oid
|
|
AND (
|
|
t_medecins_administratifs_c.adm_code IS DISTINCT FROM t_medecins_administratifs.code OR
|
|
t_medecins_administratifs_c.adm_nom IS DISTINCT FROM t_medecins_administratifs.nom OR
|
|
t_medecins_administratifs_c.adm_prenom IS DISTINCT FROM t_medecins_administratifs.prenom OR
|
|
t_medecins_administratifs_c.adm_nom_prenom IS DISTINCT FROM (t_medecins_administratifs.nom || ' ' || t_medecins_administratifs.prenom) OR
|
|
t_medecins_administratifs_c.medecin_id IS DISTINCT FROM t_medecins_administratifs.medecin_id OR
|
|
t_medecins_administratifs_c.medecin_code IS DISTINCT FROM COALESCE (t_medecins.code,'') OR
|
|
t_medecins_administratifs_c.nom IS DISTINCT FROM t_medecins.nom OR
|
|
t_medecins_administratifs_c.prenom IS DISTINCT FROM t_medecins.prenom OR
|
|
t_medecins_administratifs_c.nom_prenom IS DISTINCT FROM (t_medecins.nom || ' ' || t_medecins.prenom) OR
|
|
t_medecins_administratifs_c.specialite_id IS DISTINCT FROM t_medecins.specialite_id OR
|
|
t_medecins_administratifs_c.specialite_code IS DISTINCT FROM t_specialites_medecin.code OR
|
|
t_medecins_administratifs_c.specialite_texte IS DISTINCT FROM t_specialites_medecin.texte_court
|
|
);
|
|
EXCEPTION
|
|
WHEN others THEN RAISE NOTICE 'Erreur %' , '5';
|
|
END;
|
|
BEGIN
|
|
INSERT INTO activite.t_medecins_administratifs_c(
|
|
oid, adm_code, adm_nom, adm_prenom, adm_nom_prenom, medecin_id, medecin_code,
|
|
nom, prenom, nom_prenom, specialite_id, specialite_code, specialite_texte)
|
|
SELECT
|
|
t_medecins_administratifs.oid, t_medecins_administratifs.code, t_medecins_administratifs.nom, t_medecins_administratifs.prenom,
|
|
(t_medecins_administratifs.nom::text || ' '::text) || t_medecins_administratifs.prenom::text,
|
|
t_medecins_administratifs.medecin_id, COALESCE(t_medecins.code,''), t_medecins.nom, t_medecins.prenom,
|
|
(t_medecins.nom::text || ' '::text) || t_medecins.prenom::text,
|
|
t_medecins.specialite_id, t_specialites_medecin.code, t_specialites_medecin.texte_court
|
|
FROM activite.t_medecins_administratifs, base.t_medecins, base.t_specialites_medecin
|
|
WHERE t_medecins_administratifs.medecin_id = t_medecins.oid
|
|
AND t_medecins.specialite_id = t_specialites_medecin.oid
|
|
AND t_medecins_administratifs.oid NOT IN (SELECT oid FROM activite.t_medecins_administratifs_c)
|
|
ORDER BY t_medecins_administratifs.oid;
|
|
UPDATE activite.t_medecins_administratifs_c
|
|
SET medecin_code = subview.code
|
|
FROM
|
|
(SELECT subview.medecin_id, t_medecins_administratifs.code
|
|
FROM activite.t_medecins_administratifs,
|
|
(SELECT medecin_id, min(oid) AS oid FROM activite.t_medecins_administratifs WHERE medecin_id <> 0 GROUP BY 1) subview
|
|
WHERE t_medecins_administratifs.oid = subview.oid) subview
|
|
WHERE t_medecins_administratifs_c.medecin_id = subview.medecin_id AND medecin_code = '';
|
|
EXCEPTION
|
|
WHEN others THEN RAISE NOTICE 'Erreur %' , '6';
|
|
END;
|
|
|
|
RETURN 'OK';
|
|
END;
|