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.
939 lines
42 KiB
939 lines
42 KiB
return: text
|
|
lang: plpgsql
|
|
src: |
|
|
DECLARE
|
|
result TEXT;
|
|
BEGIN
|
|
|
|
UPDATE activite.t_divers SET
|
|
valeur2 = CASE WHEN valeur = 'ETA' THEN 'ETAGE'
|
|
WHEN valeur = 'UF' THEN 'UF'
|
|
WHEN valeur = 'SER' THEN 'SERVICE_F'
|
|
WHEN valeur = 'UM' THEN 'UM'
|
|
ELSE '' END
|
|
where code = 'PLACESREF';
|
|
|
|
INSERT INTO activite.t_divers (code, valeur, valeur2)
|
|
SELECT 'PLACESREF', 'ETA', 'ETAGE'
|
|
WHERE 'PLACESREF' NOT IN (SELECT code FROM activite.t_divers);
|
|
|
|
INSERT INTO activite.t_divers(code, texte, valeur, description)
|
|
SELECT 'TAUXSORAVANT', 'Taux de sortie avant hh:mm', '120000', 'par default taux de sortie avant 12hh00'
|
|
WHERE 'TAUXSORAVANT' NOT IN (SELECT code FROM activite.t_divers);
|
|
|
|
INSERT INTO activite.t_etages_historique(
|
|
etage_id,
|
|
date,
|
|
nb_lits_theoriques,
|
|
nb_box_ambulatoires_theoriques,
|
|
nb_box_seances_theoriques,
|
|
nb_lits_cp_theoriques,
|
|
nb_lits_ouverts,
|
|
nb_box_ambulatoires_ouverts,
|
|
nb_box_seances_ouverts,
|
|
nb_lits_cp_ouverts,
|
|
est_ouvert_1,
|
|
est_ouvert_2,
|
|
est_ouvert_3,
|
|
est_ouvert_4,
|
|
est_ouvert_5,
|
|
est_ouvert_6,
|
|
est_ouvert_7)
|
|
SELECT
|
|
oid,
|
|
'20070101'::date,
|
|
COALESCE(nb_lits,0),
|
|
0,
|
|
0,
|
|
COALESCE(nb_cp,0),
|
|
COALESCE(nb_lits,0),
|
|
0,
|
|
0,
|
|
COALESCE(nb_cp,0),
|
|
'1',
|
|
'1',
|
|
'1',
|
|
'1',
|
|
'1',
|
|
'1',
|
|
'1'
|
|
FROM activite.t_etages
|
|
LEFT JOIN activite.t_etages_historique ON t_etages_historique.etage_id = t_etages.oid AND t_etages_historique.date = '20070101'::date
|
|
WHERE t_etages_historique.etage_id IS NULL;
|
|
|
|
DROP TABLE IF EXISTS w_etages_historique
|
|
;
|
|
CREATE TEMP TABLE w_etages_historique AS
|
|
SELECT
|
|
t_etages_historique.etage_id,
|
|
t_etages_historique.date,
|
|
COALESCE(LEAD(date - interval '1 day') OVER (PARTITION BY etage_id ORDER BY etage_id, date), '2099-12-31'::date)::date AS moins_un_jour
|
|
FROM activite.t_etages_historique
|
|
ORDER BY 1, 2
|
|
;
|
|
|
|
UPDATE activite.t_etages_historique
|
|
SET date_fin = date_suivante.moins_un_jour
|
|
FROM (
|
|
SELECT
|
|
w_etages_historique.*
|
|
FROM w_etages_historique
|
|
) date_suivante
|
|
WHERE
|
|
t_etages_historique.etage_id = date_suivante.etage_id
|
|
AND t_etages_historique.date = date_suivante.date
|
|
;
|
|
|
|
UPDATE activite.t_etages
|
|
SET
|
|
nb_lits = t_etages_historique.nb_lits_theoriques + coalesce(t_etages_historique.nb_box_ambulatoires_theoriques) + coalesce(t_etages_historique.nb_box_seances_theoriques),
|
|
nb_cp = t_etages_historique.nb_lits_cp_theoriques
|
|
FROM activite.t_etages_historique
|
|
WHERE t_etages.oid = t_etages_historique.etage_id AND
|
|
date_fin = '20991231';
|
|
|
|
UPDATE activite.t_etages_historique SET
|
|
heberge_hospitalises = '0',
|
|
heberge_ambulatoires = '0',
|
|
heberge_seances = '0',
|
|
heberge_externes = '0',
|
|
heberge_urgences = '0',
|
|
heberge_bebes = '0';
|
|
|
|
UPDATE activite.t_etages_historique SET
|
|
heberge_hospitalises = subview.heberge_hospitalises,
|
|
heberge_ambulatoires = subview.heberge_ambulatoires,
|
|
heberge_seances = subview.heberge_seances,
|
|
heberge_externes = subview.heberge_externes,
|
|
heberge_urgences = subview.heberge_urgences,
|
|
heberge_bebes = subview.heberge_bebes
|
|
FROM (
|
|
SELECT
|
|
t_lieux_c.etage_id,
|
|
t_etages_historique.date,
|
|
MAX(CASE WHEN est_jour_hospitalisation = '1' THEN '1' ELSE '0' END) AS heberge_hospitalises,
|
|
MAX(CASE WHEN nb_ambulatoires > 0 THEN '1' ELSE '0' END) AS heberge_ambulatoires,
|
|
MAX(CASE WHEN nb_seances > 0 AND nb_jours_js_inclus = 0 AND nb_ambulatoires = 0 THEN '1' ELSE '0' END) AS heberge_seances,
|
|
MAX(CASE WHEN nb_externes > 0 THEN '1' ELSE '0' END) AS heberge_externes,
|
|
MAX(CASE WHEN nb_urgences > 0 THEN '1' ELSE '0' END) AS heberge_urgences,
|
|
MAX(CASE WHEN nb_bebes > 0 THEN '1' ELSE '0' END) AS heberge_bebes
|
|
FROM activite.p_mouvements_sejour
|
|
JOIN activite.t_lieux_c ON lieu_id = t_lieux_c.oid
|
|
JOIN activite.t_etages_historique ON t_lieux_c.etage_id = t_etages_historique.etage_id AND p_mouvements_sejour.date BETWEEN t_etages_historique.date AND t_etages_historique.date_fin
|
|
GROUP BY 1,2
|
|
ORDER BY 1,2
|
|
) subview
|
|
WHERE subview.etage_id = t_etages_historique.etage_id AND subview.date = t_etages_historique.date;
|
|
|
|
INSERT INTO activite.t_services_facturation_historique(
|
|
service_facturation_id,
|
|
date,
|
|
nb_lits_theoriques,
|
|
nb_box_ambulatoires_theoriques,
|
|
nb_box_seances_theoriques,
|
|
nb_lits_cp_theoriques,
|
|
nb_lits_ouverts,
|
|
nb_box_ambulatoires_ouverts,
|
|
nb_box_seances_ouverts,
|
|
nb_lits_cp_ouverts,
|
|
est_ouvert_1,
|
|
est_ouvert_2,
|
|
est_ouvert_3,
|
|
est_ouvert_4,
|
|
est_ouvert_5,
|
|
est_ouvert_6,
|
|
est_ouvert_7)
|
|
SELECT
|
|
oid,
|
|
'20070101'::date,
|
|
COALESCE(nb_lits,0),
|
|
0,
|
|
0,
|
|
COALESCE(nb_cp,0),
|
|
COALESCE(nb_lits,0),
|
|
0,
|
|
0,
|
|
COALESCE(nb_cp,0),
|
|
'1',
|
|
'1',
|
|
'1',
|
|
'1',
|
|
'1',
|
|
'1',
|
|
'1'
|
|
FROM activite.t_services_facturation
|
|
LEFT JOIN activite.t_services_facturation_historique ON t_services_facturation_historique.service_facturation_id = t_services_facturation.oid AND t_services_facturation_historique.date = '20070101'::date
|
|
WHERE t_services_facturation_historique.service_facturation_id IS NULL;
|
|
|
|
DROP TABLE IF EXISTS w_services_facturation_historique
|
|
;
|
|
CREATE TEMP TABLE w_services_facturation_historique AS
|
|
SELECT
|
|
t_services_facturation_historique.service_facturation_id,
|
|
t_services_facturation_historique.date,
|
|
COALESCE(LEAD(date - interval '1 day') OVER (PARTITION BY service_facturation_id ORDER BY service_facturation_id, date), '2099-12-31'::date)::date AS moins_un_jour
|
|
FROM activite.t_services_facturation_historique
|
|
ORDER BY 1, 2
|
|
;
|
|
|
|
UPDATE activite.t_services_facturation_historique
|
|
SET date_fin = date_suivante.moins_un_jour
|
|
FROM (
|
|
SELECT
|
|
w_services_facturation_historique.*
|
|
FROM w_services_facturation_historique
|
|
) date_suivante
|
|
WHERE
|
|
t_services_facturation_historique.service_facturation_id = date_suivante.service_facturation_id
|
|
AND t_services_facturation_historique.date = date_suivante.date
|
|
;
|
|
|
|
UPDATE activite.t_services_facturation
|
|
SET
|
|
nb_lits = t_services_facturation_historique.nb_lits_theoriques + COALESCE(t_services_facturation_historique.nb_box_ambulatoires_theoriques,0) + coalesce(t_services_facturation_historique.nb_box_seances_theoriques),
|
|
nb_cp = t_services_facturation_historique.nb_lits_cp_theoriques
|
|
FROM activite.t_services_facturation_historique
|
|
WHERE t_services_facturation.oid = t_services_facturation_historique.service_facturation_id AND
|
|
date_fin = '20991231';
|
|
|
|
UPDATE activite.t_services_facturation_historique SET
|
|
heberge_hospitalises = '0',
|
|
heberge_ambulatoires = '0',
|
|
heberge_seances = '0',
|
|
heberge_externes = '0',
|
|
heberge_urgences = '0',
|
|
heberge_bebes = '0';
|
|
|
|
UPDATE activite.t_services_facturation_historique SET
|
|
heberge_hospitalises = subview.heberge_hospitalises,
|
|
heberge_ambulatoires = subview.heberge_ambulatoires,
|
|
heberge_seances = subview.heberge_seances,
|
|
heberge_externes = subview.heberge_externes,
|
|
heberge_urgences = subview.heberge_urgences,
|
|
heberge_bebes = subview.heberge_bebes
|
|
FROM (
|
|
SELECT
|
|
t_lieux_c.service_facturation_id,
|
|
t_services_facturation_historique.date,
|
|
MAX(CASE WHEN est_jour_hospitalisation = '1' THEN '1' ELSE '0' END) AS heberge_hospitalises,
|
|
MAX(CASE WHEN nb_ambulatoires > 0 THEN '1' ELSE '0' END) AS heberge_ambulatoires,
|
|
MAX(CASE WHEN nb_seances > 0 AND nb_ambulatoires = 0 AND est_jour_hospitalisation <> '1' THEN '1' ELSE '0' END) AS heberge_seances,
|
|
MAX(CASE WHEN nb_externes > 0 THEN '1' ELSE '0' END) AS heberge_externes,
|
|
MAX(CASE WHEN nb_urgences > 0 THEN '1' ELSE '0' END) AS heberge_urgences,
|
|
MAX(CASE WHEN nb_bebes > 0 THEN '1' ELSE '0' END) AS heberge_bebes
|
|
FROM activite.p_mouvements_sejour
|
|
JOIN activite.t_lieux_c ON lieu_id = t_lieux_c.oid
|
|
JOIN activite.t_services_facturation_historique ON t_lieux_c.service_facturation_id = t_services_facturation_historique.service_facturation_id AND p_mouvements_sejour.date BETWEEN t_services_facturation_historique.date AND t_services_facturation_historique.date_fin
|
|
GROUP BY 1,2
|
|
ORDER BY 1,2
|
|
) subview
|
|
WHERE subview.service_facturation_id = t_services_facturation_historique.service_facturation_id AND subview.date = t_services_facturation_historique.date;
|
|
|
|
INSERT INTO activite.t_unites_fonctionnelles_historique(
|
|
unite_fonctionnelle_id,
|
|
date,
|
|
nb_lits_theoriques,
|
|
nb_box_ambulatoires_theoriques,
|
|
nb_box_seances_theoriques,
|
|
nb_lits_cp_theoriques,
|
|
nb_lits_ouverts,
|
|
nb_box_ambulatoires_ouverts,
|
|
nb_box_seances_ouverts,
|
|
nb_lits_cp_ouverts,
|
|
est_ouvert_1,
|
|
est_ouvert_2,
|
|
est_ouvert_3,
|
|
est_ouvert_4,
|
|
est_ouvert_5,
|
|
est_ouvert_6,
|
|
est_ouvert_7)
|
|
SELECT
|
|
oid,
|
|
'20070101'::date,
|
|
0,
|
|
0,
|
|
0,
|
|
0,
|
|
0,
|
|
0,
|
|
0,
|
|
0,
|
|
'1',
|
|
'1',
|
|
'1',
|
|
'1',
|
|
'1',
|
|
'1',
|
|
'1'
|
|
FROM activite.t_unites_fonctionnelles
|
|
LEFT JOIN activite.t_unites_fonctionnelles_historique ON t_unites_fonctionnelles_historique.unite_fonctionnelle_id = t_unites_fonctionnelles.oid AND t_unites_fonctionnelles_historique.date = '20070101'::date
|
|
WHERE t_unites_fonctionnelles_historique.unite_fonctionnelle_id IS NULL;
|
|
|
|
DROP TABLE IF EXISTS w_unites_fonctionnelles_historique
|
|
;
|
|
CREATE TEMP TABLE w_unites_fonctionnelles_historique AS
|
|
SELECT
|
|
t_unites_fonctionnelles_historique.unite_fonctionnelle_id,
|
|
t_unites_fonctionnelles_historique.date,
|
|
COALESCE(LEAD(date - interval '1 day') OVER (PARTITION BY unite_fonctionnelle_id ORDER BY unite_fonctionnelle_id, date), '2099-12-31'::date)::date AS moins_un_jour
|
|
FROM activite.t_unites_fonctionnelles_historique
|
|
ORDER BY 1, 2
|
|
;
|
|
|
|
UPDATE activite.t_unites_fonctionnelles_historique
|
|
SET date_fin = date_suivante.moins_un_jour
|
|
FROM (
|
|
SELECT
|
|
w_unites_fonctionnelles_historique.*
|
|
FROM w_unites_fonctionnelles_historique
|
|
) date_suivante
|
|
WHERE
|
|
t_unites_fonctionnelles_historique.unite_fonctionnelle_id = date_suivante.unite_fonctionnelle_id
|
|
AND t_unites_fonctionnelles_historique.date = date_suivante.date
|
|
;
|
|
|
|
UPDATE activite.t_unites_fonctionnelles_historique SET
|
|
heberge_hospitalises = '0',
|
|
heberge_ambulatoires = '0',
|
|
heberge_seances = '0',
|
|
heberge_externes = '0',
|
|
heberge_urgences = '0',
|
|
heberge_bebes = '0';
|
|
|
|
UPDATE activite.t_unites_fonctionnelles_historique SET
|
|
heberge_hospitalises = subview.heberge_hospitalises,
|
|
heberge_ambulatoires = subview.heberge_ambulatoires,
|
|
heberge_seances = subview.heberge_seances,
|
|
heberge_externes = subview.heberge_externes,
|
|
heberge_urgences = subview.heberge_urgences,
|
|
heberge_bebes = subview.heberge_bebes
|
|
FROM (
|
|
SELECT
|
|
t_lieux_c.unite_fonctionnelle_id,
|
|
t_unites_fonctionnelles_historique.date,
|
|
MAX(CASE WHEN est_jour_hospitalisation = '1' THEN '1' ELSE '0' END) AS heberge_hospitalises,
|
|
MAX(CASE WHEN nb_ambulatoires > 0 THEN '1' ELSE '0' END) AS heberge_ambulatoires,
|
|
MAX(CASE WHEN nb_seances > 0 AND est_jour_hospitalisation <> '1' AND nb_ambulatoires = 0 THEN '1' ELSE '0' END) AS heberge_seances,
|
|
MAX(CASE WHEN nb_externes > 0 THEN '1' ELSE '0' END) AS heberge_externes,
|
|
MAX(CASE WHEN nb_urgences > 0 THEN '1' ELSE '0' END) AS heberge_urgences,
|
|
MAX(CASE WHEN nb_bebes > 0 THEN '1' ELSE '0' END) AS heberge_bebes
|
|
FROM activite.p_mouvements_sejour
|
|
JOIN activite.t_lieux_c ON lieu_id = t_lieux_c.oid
|
|
JOIN activite.t_unites_fonctionnelles_historique ON t_lieux_c.unite_fonctionnelle_id = t_unites_fonctionnelles_historique.unite_fonctionnelle_id AND p_mouvements_sejour.date BETWEEN t_unites_fonctionnelles_historique.date AND t_unites_fonctionnelles_historique.date_fin
|
|
GROUP BY 1,2
|
|
ORDER BY 1,2
|
|
) subview
|
|
WHERE subview.unite_fonctionnelle_id = t_unites_fonctionnelles_historique.unite_fonctionnelle_id AND subview.date = t_unites_fonctionnelles_historique.date;
|
|
|
|
INSERT INTO activite.t_unites_medicales_historique(
|
|
unite_medicale_id,
|
|
date,
|
|
nb_lits_theoriques,
|
|
nb_box_ambulatoires_theoriques,
|
|
nb_box_seances_theoriques,
|
|
nb_lits_cp_theoriques,
|
|
nb_lits_ouverts,
|
|
nb_box_ambulatoires_ouverts,
|
|
nb_box_seances_ouverts,
|
|
nb_lits_cp_ouverts,
|
|
est_ouvert_1,
|
|
est_ouvert_2,
|
|
est_ouvert_3,
|
|
est_ouvert_4,
|
|
est_ouvert_5,
|
|
est_ouvert_6,
|
|
est_ouvert_7)
|
|
SELECT
|
|
oid,
|
|
'20070101'::date,
|
|
0,
|
|
0,
|
|
0,
|
|
0,
|
|
0,
|
|
0,
|
|
0,
|
|
0,
|
|
'1',
|
|
'1',
|
|
'1',
|
|
'1',
|
|
'1',
|
|
'1',
|
|
'1'
|
|
FROM activite.t_unites_medicales
|
|
LEFT JOIN activite.t_unites_medicales_historique ON t_unites_medicales_historique.unite_medicale_id = t_unites_medicales.oid AND t_unites_medicales_historique.date = '20070101'::date
|
|
WHERE t_unites_medicales_historique.unite_medicale_id IS NULL;
|
|
|
|
DROP TABLE IF EXISTS w_unites_medicales_historique
|
|
;
|
|
CREATE TEMP TABLE w_unites_medicales_historique AS
|
|
SELECT
|
|
t_unites_medicales_historique.unite_medicale_id,
|
|
t_unites_medicales_historique.date,
|
|
COALESCE(LEAD(date - interval '1 day') OVER (PARTITION BY unite_medicale_id ORDER BY unite_medicale_id, date), '2099-12-31'::date)::date AS moins_un_jour
|
|
FROM activite.t_unites_medicales_historique
|
|
ORDER BY 1, 2
|
|
;
|
|
|
|
UPDATE activite.t_unites_medicales_historique
|
|
SET date_fin = date_suivante.moins_un_jour
|
|
FROM (
|
|
SELECT
|
|
w_unites_medicales_historique.*
|
|
FROM w_unites_medicales_historique
|
|
) date_suivante
|
|
WHERE
|
|
t_unites_medicales_historique.unite_medicale_id = date_suivante.unite_medicale_id
|
|
AND t_unites_medicales_historique.date = date_suivante.date
|
|
;
|
|
|
|
UPDATE activite.t_unites_medicales_historique SET
|
|
heberge_hospitalises = '0',
|
|
heberge_ambulatoires = '0',
|
|
heberge_seances = '0',
|
|
heberge_externes = '0',
|
|
heberge_urgences = '0',
|
|
heberge_bebes = '0';
|
|
|
|
UPDATE activite.t_unites_medicales_historique SET
|
|
heberge_hospitalises = subview.heberge_hospitalises,
|
|
heberge_ambulatoires = subview.heberge_ambulatoires,
|
|
heberge_seances = subview.heberge_seances,
|
|
heberge_externes = subview.heberge_externes,
|
|
heberge_urgences = subview.heberge_urgences,
|
|
heberge_bebes = subview.heberge_bebes
|
|
FROM (
|
|
SELECT
|
|
t_lieux_c.unite_medicale_id,
|
|
t_unites_medicales_historique.date,
|
|
MAX(CASE WHEN est_jour_hospitalisation = '1' THEN '1' ELSE '0' END) AS heberge_hospitalises,
|
|
MAX(CASE WHEN nb_ambulatoires > 0 THEN '1' ELSE '0' END) AS heberge_ambulatoires,
|
|
MAX(CASE WHEN nb_seances > 0 AND est_jour_hospitalisation <> '1' AND nb_ambulatoires = 0 THEN '1' ELSE '0' END) AS heberge_seances,
|
|
MAX(CASE WHEN nb_externes > 0 THEN '1' ELSE '0' END) AS heberge_externes,
|
|
MAX(CASE WHEN nb_urgences > 0 THEN '1' ELSE '0' END) AS heberge_urgences,
|
|
MAX(CASE WHEN nb_bebes > 0 THEN '1' ELSE '0' END) AS heberge_bebes
|
|
FROM activite.p_mouvements_sejour
|
|
JOIN activite.t_lieux_c ON lieu_id = t_lieux_c.oid
|
|
JOIN activite.t_unites_medicales_historique ON t_lieux_c.unite_medicale_id = t_unites_medicales_historique.unite_medicale_id AND p_mouvements_sejour.date BETWEEN t_unites_medicales_historique.date AND t_unites_medicales_historique.date_fin
|
|
GROUP BY 1,2
|
|
ORDER BY 1,2
|
|
) subview
|
|
WHERE subview.unite_medicale_id = t_unites_medicales_historique.unite_medicale_id AND subview.date = t_unites_medicales_historique.date;
|
|
|
|
DROP TABLE IF EXISTS w_places;
|
|
|
|
CREATE TEMP TABLE w_places AS
|
|
SELECT
|
|
t_etages.oid AS parent_id,
|
|
t_type_sejour.oid AS type_sejour_id,
|
|
p_calendrier.date,
|
|
p_calendrier.jour_semaine,
|
|
p_calendrier.is_weekend,
|
|
CASE
|
|
WHEN t_type_sejour.oid = 1 THEN t_etages_historique.nb_lits_theoriques
|
|
WHEN t_type_sejour.oid = 2 THEN t_etages_historique.nb_box_ambulatoires_theoriques
|
|
WHEN t_type_sejour.oid = 5 THEN t_etages_historique.nb_box_seances_theoriques
|
|
ELSE 0 END AS nb_lits_theoriques,
|
|
CASE
|
|
WHEN t_type_sejour.oid = 1 THEN t_etages_historique.nb_lits_ouverts
|
|
WHEN t_type_sejour.oid = 2 THEN t_etages_historique.nb_box_ambulatoires_ouverts
|
|
WHEN t_type_sejour.oid = 5 THEN t_etages_historique.nb_box_seances_ouverts
|
|
ELSE 0 END AS nb_lits_ouverts,
|
|
CASE
|
|
WHEN t_type_sejour.oid = 1 THEN t_etages_historique.nb_lits_cp_theoriques
|
|
ELSE 0 END AS nb_lits_cp_theoriques,
|
|
CASE
|
|
WHEN t_type_sejour.oid = 1 THEN t_etages_historique.nb_lits_cp_ouverts
|
|
ELSE 0 END AS nb_lits_cp_ouverts,
|
|
CASE
|
|
WHEN t_type_sejour.oid = 5 THEN t_etages_historique.nb_box_seances_theoriques
|
|
ELSE 0 END AS nb_box_seances_theoriques,
|
|
CASE
|
|
WHEN t_type_sejour.oid = 5 THEN t_etages_historique.nb_box_seances_ouverts
|
|
ELSE 0 END AS nb_box_seances_ouverts,
|
|
CASE WHEN date_part('DOW',p_calendrier.date) = 1 THEN est_ouvert_1
|
|
WHEN date_part('DOW',p_calendrier.date) = 2 THEN est_ouvert_2
|
|
WHEN date_part('DOW',p_calendrier.date) = 3 THEN est_ouvert_3
|
|
WHEN date_part('DOW',p_calendrier.date) = 4 THEN est_ouvert_4
|
|
WHEN date_part('DOW',p_calendrier.date) = 5 THEN est_ouvert_5
|
|
WHEN date_part('DOW',p_calendrier.date) = 6 THEN est_ouvert_6
|
|
WHEN date_part('DOW',p_calendrier.date) = 0 THEN est_ouvert_7
|
|
ELSE '0' END AS est_ouvert,
|
|
heberge_hospitalises,
|
|
heberge_ambulatoires,
|
|
heberge_seances,
|
|
heberge_externes,
|
|
heberge_urgences,
|
|
heberge_bebes
|
|
FROM activite.t_etages
|
|
JOIN activite.t_etages_historique ON t_etages.oid = t_etages_historique.etage_id
|
|
JOIN base.p_calendrier ON p_calendrier.date BETWEEN t_etages_historique.date AND t_etages_historique.date_fin AND p_calendrier.date BETWEEN '20070101'::date AND now() + interval '1 month'
|
|
JOIN activite.p_chiffrier ON p_calendrier.mois = p_chiffrier.mois
|
|
LEFT JOIN activite.t_divers ON t_divers.code = 'PLACESREF'
|
|
JOIN activite.t_type_sejour ON t_type_sejour.oid IN (1,2,5)
|
|
WHERE t_divers.valeur = 'ETA' OR t_divers.valeur IS NULL
|
|
ORDER BY 1,2;
|
|
|
|
INSERT INTO w_places
|
|
SELECT
|
|
t_services_facturation.oid AS service_facturation_id,
|
|
t_type_sejour.oid AS type_sejour_id,
|
|
p_calendrier.date,
|
|
p_calendrier.jour_semaine,
|
|
p_calendrier.is_weekend,
|
|
CASE
|
|
WHEN t_type_sejour.oid = 1 THEN t_services_facturation_historique.nb_lits_theoriques
|
|
WHEN t_type_sejour.oid = 2 THEN t_services_facturation_historique.nb_box_ambulatoires_theoriques
|
|
WHEN t_type_sejour.oid = 5 THEN t_services_facturation_historique.nb_box_seances_theoriques
|
|
ELSE 0 END AS nb_lits_theoriques,
|
|
CASE
|
|
WHEN t_type_sejour.oid = 1 THEN t_services_facturation_historique.nb_lits_ouverts
|
|
WHEN t_type_sejour.oid = 2 THEN t_services_facturation_historique.nb_box_ambulatoires_ouverts
|
|
WHEN t_type_sejour.oid = 5 THEN t_services_facturation_historique.nb_box_seances_ouverts
|
|
ELSE 0 END AS nb_lits_ouverts,
|
|
CASE
|
|
WHEN t_type_sejour.oid = 1 THEN t_services_facturation_historique.nb_lits_cp_theoriques
|
|
ELSE 0 END AS nb_lits_cp_theoriques,
|
|
CASE
|
|
WHEN t_type_sejour.oid = 1 THEN t_services_facturation_historique.nb_lits_cp_ouverts
|
|
ELSE 0 END AS nb_lits_cp_ouverts,
|
|
CASE
|
|
WHEN t_type_sejour.oid = 5 THEN t_services_facturation_historique.nb_box_seances_theoriques
|
|
ELSE 0 END AS nb_box_seances_theoriques,
|
|
CASE
|
|
WHEN t_type_sejour.oid = 5 THEN t_services_facturation_historique.nb_box_seances_ouverts
|
|
ELSE 0 END AS nb_box_seances_ouverts,
|
|
CASE WHEN date_part('DOW',p_calendrier.date) = 1 THEN est_ouvert_1
|
|
WHEN date_part('DOW',p_calendrier.date) = 2 THEN est_ouvert_2
|
|
WHEN date_part('DOW',p_calendrier.date) = 3 THEN est_ouvert_3
|
|
WHEN date_part('DOW',p_calendrier.date) = 4 THEN est_ouvert_4
|
|
WHEN date_part('DOW',p_calendrier.date) = 5 THEN est_ouvert_5
|
|
WHEN date_part('DOW',p_calendrier.date) = 6 THEN est_ouvert_6
|
|
WHEN date_part('DOW',p_calendrier.date) = 0 THEN est_ouvert_7
|
|
ELSE '0' END AS est_ouvert,
|
|
heberge_hospitalises,
|
|
heberge_ambulatoires,
|
|
heberge_seances,
|
|
heberge_externes,
|
|
heberge_urgences,
|
|
heberge_bebes
|
|
FROM activite.t_services_facturation
|
|
JOIN activite.t_services_facturation_historique ON t_services_facturation.oid = t_services_facturation_historique.service_facturation_id
|
|
JOIN base.p_calendrier ON p_calendrier.date BETWEEN t_services_facturation_historique.date AND t_services_facturation_historique.date_fin AND p_calendrier.date BETWEEN '20070101'::date AND now() + interval '1 month'
|
|
JOIN activite.p_chiffrier ON p_calendrier.mois = p_chiffrier.mois
|
|
JOIN activite.t_divers ON t_divers.code = 'PLACESREF' AND t_divers.valeur = 'SER'
|
|
JOIN activite.t_type_sejour ON t_type_sejour.oid IN (1,2,5)
|
|
ORDER BY 1,2;
|
|
|
|
INSERT INTO w_places
|
|
SELECT
|
|
t_unites_fonctionnelles.oid AS unite_fonctionnelle_id,
|
|
t_type_sejour.oid AS type_sejour_id,
|
|
p_calendrier.date,
|
|
p_calendrier.jour_semaine,
|
|
p_calendrier.is_weekend,
|
|
CASE
|
|
WHEN t_type_sejour.oid = 1 THEN t_unites_fonctionnelles_historique.nb_lits_theoriques
|
|
WHEN t_type_sejour.oid = 2 THEN t_unites_fonctionnelles_historique.nb_box_ambulatoires_theoriques
|
|
WHEN t_type_sejour.oid = 5 THEN t_unites_fonctionnelles_historique.nb_box_seances_theoriques
|
|
ELSE 0 END AS nb_lits_theoriques,
|
|
CASE
|
|
WHEN t_type_sejour.oid = 1 THEN t_unites_fonctionnelles_historique.nb_lits_ouverts
|
|
WHEN t_type_sejour.oid = 2 THEN t_unites_fonctionnelles_historique.nb_box_ambulatoires_ouverts
|
|
WHEN t_type_sejour.oid = 5 THEN t_unites_fonctionnelles_historique.nb_box_seances_ouverts
|
|
ELSE 0 END AS nb_lits_ouverts,
|
|
CASE
|
|
WHEN t_type_sejour.oid = 1 THEN t_unites_fonctionnelles_historique.nb_lits_cp_theoriques
|
|
ELSE 0 END AS nb_lits_cp_theoriques,
|
|
CASE
|
|
WHEN t_type_sejour.oid = 1 THEN t_unites_fonctionnelles_historique.nb_lits_cp_ouverts
|
|
ELSE 0 END AS nb_lits_cp_ouverts,
|
|
CASE
|
|
WHEN t_type_sejour.oid = 5 THEN t_unites_fonctionnelles_historique.nb_box_seances_theoriques
|
|
ELSE 0 END AS nb_box_seances_theoriques,
|
|
CASE
|
|
WHEN t_type_sejour.oid = 5 THEN t_unites_fonctionnelles_historique.nb_box_seances_ouverts
|
|
ELSE 0 END AS nb_box_seances_ouverts,
|
|
CASE WHEN date_part('DOW',p_calendrier.date) = 1 THEN est_ouvert_1
|
|
WHEN date_part('DOW',p_calendrier.date) = 2 THEN est_ouvert_2
|
|
WHEN date_part('DOW',p_calendrier.date) = 3 THEN est_ouvert_3
|
|
WHEN date_part('DOW',p_calendrier.date) = 4 THEN est_ouvert_4
|
|
WHEN date_part('DOW',p_calendrier.date) = 5 THEN est_ouvert_5
|
|
WHEN date_part('DOW',p_calendrier.date) = 6 THEN est_ouvert_6
|
|
WHEN date_part('DOW',p_calendrier.date) = 0 THEN est_ouvert_7
|
|
ELSE '0' END AS est_ouvert,
|
|
heberge_hospitalises,
|
|
heberge_ambulatoires,
|
|
heberge_seances,
|
|
heberge_externes,
|
|
heberge_urgences,
|
|
heberge_bebes
|
|
FROM activite.t_unites_fonctionnelles
|
|
JOIN activite.t_unites_fonctionnelles_historique ON t_unites_fonctionnelles.oid = t_unites_fonctionnelles_historique.unite_fonctionnelle_id
|
|
JOIN base.p_calendrier ON p_calendrier.date BETWEEN t_unites_fonctionnelles_historique.date AND t_unites_fonctionnelles_historique.date_fin AND p_calendrier.date BETWEEN '20070101'::date AND now() + interval '1 month'
|
|
JOIN activite.p_chiffrier ON p_calendrier.mois = p_chiffrier.mois
|
|
JOIN activite.t_divers ON t_divers.code = 'PLACESREF' AND t_divers.valeur = 'UF'
|
|
JOIN activite.t_type_sejour ON t_type_sejour.oid IN (1,2,5)
|
|
ORDER BY 1,2;
|
|
|
|
INSERT INTO w_places
|
|
SELECT
|
|
t_unites_medicales.oid AS unite_medicale_id,
|
|
t_type_sejour.oid AS type_sejour_id,
|
|
p_calendrier.date,
|
|
p_calendrier.jour_semaine,
|
|
p_calendrier.is_weekend,
|
|
CASE
|
|
WHEN t_type_sejour.oid = 1 THEN t_unites_medicales_historique.nb_lits_theoriques
|
|
WHEN t_type_sejour.oid = 2 THEN t_unites_medicales_historique.nb_box_ambulatoires_theoriques
|
|
WHEN t_type_sejour.oid = 5 THEN t_unites_medicales_historique.nb_box_seances_theoriques
|
|
ELSE 0 END AS nb_lits_theoriques,
|
|
CASE
|
|
WHEN t_type_sejour.oid = 1 THEN t_unites_medicales_historique.nb_lits_ouverts
|
|
WHEN t_type_sejour.oid = 2 THEN t_unites_medicales_historique.nb_box_ambulatoires_ouverts
|
|
WHEN t_type_sejour.oid = 5 THEN t_unites_medicales_historique.nb_box_seances_ouverts
|
|
ELSE 0 END AS nb_lits_ouverts,
|
|
CASE
|
|
WHEN t_type_sejour.oid = 1 THEN t_unites_medicales_historique.nb_lits_cp_theoriques
|
|
ELSE 0 END AS nb_lits_cp_theoriques,
|
|
CASE
|
|
WHEN t_type_sejour.oid = 1 THEN t_unites_medicales_historique.nb_lits_cp_ouverts
|
|
ELSE 0 END AS nb_lits_cp_ouverts,
|
|
CASE
|
|
WHEN t_type_sejour.oid = 5 THEN t_unites_medicales_historique.nb_box_seances_theoriques
|
|
ELSE 0 END AS nb_box_seances_theoriques,
|
|
CASE
|
|
WHEN t_type_sejour.oid = 5 THEN t_unites_medicales_historique.nb_box_seances_ouverts
|
|
ELSE 0 END AS nb_box_seances_ouverts,
|
|
CASE WHEN date_part('DOW',p_calendrier.date) = 1 THEN est_ouvert_1
|
|
WHEN date_part('DOW',p_calendrier.date) = 2 THEN est_ouvert_2
|
|
WHEN date_part('DOW',p_calendrier.date) = 3 THEN est_ouvert_3
|
|
WHEN date_part('DOW',p_calendrier.date) = 4 THEN est_ouvert_4
|
|
WHEN date_part('DOW',p_calendrier.date) = 5 THEN est_ouvert_5
|
|
WHEN date_part('DOW',p_calendrier.date) = 6 THEN est_ouvert_6
|
|
WHEN date_part('DOW',p_calendrier.date) = 0 THEN est_ouvert_7
|
|
ELSE '0' END AS est_ouvert,
|
|
heberge_hospitalises,
|
|
heberge_ambulatoires,
|
|
heberge_seances,
|
|
heberge_externes,
|
|
heberge_urgences,
|
|
heberge_bebes
|
|
FROM activite.t_unites_medicales
|
|
JOIN activite.t_unites_medicales_historique ON t_unites_medicales.oid = t_unites_medicales_historique.unite_medicale_id
|
|
JOIN base.p_calendrier ON p_calendrier.date BETWEEN t_unites_medicales_historique.date AND t_unites_medicales_historique.date_fin AND p_calendrier.date BETWEEN '20070101'::date AND now() + interval '1 month'
|
|
JOIN activite.p_chiffrier ON p_calendrier.mois = p_chiffrier.mois
|
|
JOIN activite.t_divers ON t_divers.code = 'PLACESREF' AND t_divers.valeur = 'UM'
|
|
JOIN activite.t_type_sejour ON t_type_sejour.oid IN (1,2,5)
|
|
ORDER BY 1,2;
|
|
|
|
|
|
-- Selon lits réellement occupés (ambulatoires)
|
|
DROP TABLE IF EXISTS w_etages_date;
|
|
|
|
CREATE TEMP TABLE w_etages_date AS
|
|
SELECT t_etages_historique.etage_id, p_calendrier.date
|
|
FROM base.p_calendrier
|
|
JOIN activite.t_etages_historique ON nb_box_ambulatoires_ouverts < 0 AND
|
|
p_calendrier.date BETWEEN t_etages_historique.date AND t_etages_historique.date_fin
|
|
WHERE p_calendrier.date <= date(now())
|
|
;
|
|
|
|
|
|
DROP TABLE IF EXISTS w_places_lits_reels;
|
|
|
|
CREATE TEMP TABLE w_places_lits_reels AS
|
|
SELECT w_etages_date.etage_id, w_etages_date.date, count(DISTINCT lit_id) AS nb_box_ambulatoires
|
|
FROM w_etages_date
|
|
LEFT JOIN (activite.p_mouvements_sejour
|
|
JOIN activite.t_lieux_c ON lieu_id = t_lieux_c.oid
|
|
JOIN activite.t_etages ON etage_id = t_etages.oid
|
|
) ON w_etages_date.etage_id = t_lieux_c.etage_id AND
|
|
w_etages_date.date = p_mouvements_sejour.date
|
|
GROUP BY 1,2
|
|
;
|
|
|
|
|
|
UPDATE w_places SET
|
|
nb_lits_ouverts = nb_box_ambulatoires
|
|
FROM w_places_lits_reels
|
|
WHERE w_places.parent_id = w_places_lits_reels.etage_id AND
|
|
w_places.date = w_places_lits_reels.date AND
|
|
type_sejour_id = 2
|
|
;
|
|
|
|
TRUNCATE activite.t_places;
|
|
|
|
INSERT INTO activite.t_places(
|
|
parent_id,
|
|
type_sejour_id,
|
|
date,
|
|
jour_semaine,
|
|
is_weekend,
|
|
nb_lits_theoriques,
|
|
nb_lits_ouverts,
|
|
nb_lits_cp_theoriques,
|
|
nb_lits_cp_ouverts,
|
|
est_ouvert,
|
|
heberge_hospitalises,
|
|
heberge_ambulatoires,
|
|
heberge_seances,
|
|
heberge_externes,
|
|
heberge_urgences,
|
|
heberge_bebes,
|
|
nb_box_seances_theoriques,
|
|
nb_box_seances_ouverts
|
|
)
|
|
SELECT
|
|
w_places.parent_id,
|
|
w_places.type_sejour_id,
|
|
w_places.date,
|
|
w_places.jour_semaine,
|
|
w_places.is_weekend,
|
|
w_places.nb_lits_theoriques,
|
|
w_places.nb_lits_ouverts,
|
|
w_places.nb_lits_cp_theoriques,
|
|
w_places.nb_lits_cp_ouverts,
|
|
w_places.est_ouvert,
|
|
w_places.heberge_hospitalises,
|
|
w_places.heberge_ambulatoires,
|
|
w_places.heberge_seances,
|
|
w_places.heberge_externes,
|
|
w_places.heberge_urgences,
|
|
w_places.heberge_bebes,
|
|
w_places.nb_box_seances_theoriques,
|
|
w_places.nb_box_seances_ouverts
|
|
FROM w_places
|
|
LEFT JOIN activite.t_places t_places_deja ON w_places.parent_id = t_places_deja.parent_id AND
|
|
w_places.date = t_places_deja.date AND
|
|
w_places.type_sejour_id = t_places_deja.type_sejour_id
|
|
WHERE t_places_deja.parent_id IS NULL;
|
|
|
|
|
|
ANALYSE activite.t_places;
|
|
|
|
DROP TABLE IF EXISTS w_places;
|
|
|
|
DROP TABLE IF EXISTS w_places_parents;
|
|
|
|
CREATE TEMP TABLE w_places_parents AS
|
|
SELECT
|
|
t_etages.oid AS oid,
|
|
t_etages.code AS parent_code,
|
|
t_etages.texte AS parent_texte,
|
|
'ETA'::text AS parent_type,
|
|
t_etages.section_id AS parent_section_id,
|
|
t_etages.section_code AS parent_section_code,
|
|
t_etages.section_texte AS parent_section_texte,
|
|
finess_id, t_finess_c.code AS finess_code,
|
|
t_finess_c.texte AS finess_texte,
|
|
t_finess_c.section_id AS finess_section_id,
|
|
t_finess_c.section_code AS finess_section_code,
|
|
t_finess_c.section_texte AS finess_section_texte,
|
|
-111::bigint AS mode_traitement_id,
|
|
''::text AS mode_traitement_code,
|
|
''::text AS mode_traitement_texte
|
|
FROM
|
|
activite.t_etages
|
|
LEFT JOIN activite.t_divers ON t_divers.code = 'PLACESREF'
|
|
LEFT JOIN activite.t_finess_c ON finess_id = t_finess_c.oid
|
|
WHERE
|
|
t_divers.valeur = 'ETA' OR t_divers.valeur IS NULL
|
|
UNION all
|
|
SELECT
|
|
t_services_facturation.oid AS oid,
|
|
t_services_facturation.code AS parent_code,
|
|
t_services_facturation.texte AS parent_texte,
|
|
'SER'::text AS parent_type,
|
|
t_services_facturation.section_id AS parent_section_id,
|
|
t_services_facturation.section_code AS parent_section_code,
|
|
t_services_facturation.section_texte AS parent_section_texte,
|
|
finess_id,
|
|
t_finess_c.code AS finess_code,
|
|
t_finess_c.texte AS finess_texte,
|
|
t_finess_c.section_id AS finess_section_id,
|
|
t_finess_c.section_code AS finess_section_code,
|
|
t_finess_c.section_texte AS finess_section_texte,
|
|
t_services_facturation.mode_traitement_id,
|
|
COALESCE(t_modes_traitement.code,''::text)::text AS mode_traitement_code,
|
|
COALESCE(t_modes_traitement.texte_court,''::text)::text AS mode_traitement_texte
|
|
FROM
|
|
activite.t_services_facturation
|
|
JOIN activite.t_divers ON t_divers.code = 'PLACESREF' AND t_divers.valeur = 'SER'
|
|
LEFT JOIN activite.t_finess_c ON finess_id = t_finess_c.oid
|
|
LEFT JOIN base.t_modes_traitement ON t_services_facturation.mode_traitement_id = t_modes_traitement.oid
|
|
UNION All
|
|
SELECT
|
|
t_unites_fonctionnelles.oid AS oid,
|
|
t_unites_fonctionnelles.code AS parent_code,
|
|
t_unites_fonctionnelles.texte AS parent_texte,
|
|
'UF'::text AS parent_type,
|
|
t_unites_fonctionnelles.section_id AS parent_section_id,
|
|
t_unites_fonctionnelles.section_code AS parent_section_code,
|
|
t_unites_fonctionnelles.section_texte AS parent_section_texte,
|
|
finess_id,
|
|
t_finess_c.code AS finess_code,
|
|
t_finess_c.texte AS finess_texte,
|
|
t_finess_c.section_id AS finess_section_id,
|
|
t_finess_c.section_code AS finess_section_code,
|
|
t_finess_c.section_texte AS finess_section_texte,
|
|
-111::bigint AS mode_traitement_id,
|
|
''::text AS mode_traitement_code,
|
|
''::text AS mode_traitement_texte
|
|
FROM
|
|
activite.t_unites_fonctionnelles
|
|
JOIN activite.t_divers ON t_divers.code = 'PLACESREF' AND t_divers.valeur = 'UF'
|
|
LEFT JOIN activite.t_finess_c ON finess_id = t_finess_c.oid
|
|
UNION All
|
|
SELECT
|
|
t_unites_medicales.oid AS oid,
|
|
t_unites_medicales.code AS parent_code,
|
|
t_unites_medicales.texte AS parent_texte,
|
|
'UM'::text AS parent_type,
|
|
t_unites_medicales.section_id AS parent_section_id,
|
|
t_unites_medicales.section_code AS parent_section_code,
|
|
t_unites_medicales.section_texte AS parent_section_texte,
|
|
finess_id,
|
|
t_finess_c.code AS finess_code,
|
|
t_finess_c.texte AS finess_texte,
|
|
t_finess_c.section_id AS finess_section_id,
|
|
t_finess_c.section_code AS finess_section_code,
|
|
t_finess_c.section_texte AS finess_section_texte,
|
|
-111::bigint AS mode_traitement_id,
|
|
''::text AS mode_traitement_code,
|
|
''::text AS mode_traitement_texte
|
|
FROM
|
|
activite.t_unites_medicales
|
|
JOIN activite.t_divers ON t_divers.code = 'PLACESREF' AND t_divers.valeur = 'UM'
|
|
LEFT JOIN activite.t_finess_c ON finess_id = t_finess_c.oid;
|
|
|
|
TRUNCATE activite.t_places_parents;
|
|
|
|
INSERT INTO activite.t_places_parents (
|
|
oid,
|
|
parent_code,
|
|
parent_texte,
|
|
parent_type,
|
|
parent_section_id,
|
|
parent_section_code,
|
|
parent_section_texte,
|
|
finess_id,
|
|
finess_code,
|
|
finess_texte,
|
|
finess_section_id,
|
|
finess_section_code,
|
|
finess_section_texte,
|
|
mode_traitement_id,
|
|
mode_traitement_code,
|
|
mode_traitement_texte
|
|
)
|
|
SELECT
|
|
oid,
|
|
parent_code,
|
|
parent_texte,
|
|
parent_type,
|
|
parent_section_id,
|
|
parent_section_code,
|
|
parent_section_texte,
|
|
finess_id,
|
|
finess_code,
|
|
finess_texte,
|
|
finess_section_id,
|
|
finess_section_code,
|
|
finess_section_texte,
|
|
mode_traitement_id,
|
|
mode_traitement_code,
|
|
mode_traitement_texte
|
|
FROM w_places_parents
|
|
WHERE oid NOT IN (SELECT oid FROM activite.t_places_parents);
|
|
|
|
ANALYSE activite.t_places_parents;
|
|
|
|
-- Désactivation RAZ séances dans taux car géré dans cti_calcul_journees_mouvements
|
|
IF 1<>1 THEN
|
|
UPDATE activite.p_mouvements_sejour
|
|
SET nb_seances_dans_taux =
|
|
CASE
|
|
WHEN t_places.nb_box_seances_theoriques > 0 THEN nb_seances
|
|
WHEN nb_seances_dans_taux > 0 AND t_places.nb_box_seances_theoriques = 0 THEN 0
|
|
ELSE nb_seances_dans_taux END
|
|
FROM activite.t_lieux
|
|
JOIN activite.t_lits ON lit_id = t_lits.oid
|
|
JOIN activite.t_services_facturation ON service_facturation_id = t_services_facturation.oid
|
|
LEFT JOIN activite.t_etages t_etages_forces_service ON t_services_facturation.etage_force_id = t_etages_forces_service.oid
|
|
LEFT JOIN activite.t_etages t_etages_par_defaut_service ON t_services_facturation.etage_par_defaut_id = t_etages_par_defaut_service.oid
|
|
JOIN activite.t_divers ON t_divers.code = 'PLACESREF' AND t_divers.valeur = 'ETA'
|
|
JOIN activite.t_places ON
|
|
(t_lits.etage_id = t_places.parent_id AND t_services_facturation.etage_par_defaut_id = 0 AND t_services_facturation.etage_force_id = 0) OR
|
|
(t_lits.etage_id = t_places.parent_id AND t_services_facturation.etage_par_defaut_id <> 0 AND t_lits.etage_id <> 0 AND t_services_facturation.etage_force_id = 0) OR
|
|
(t_lits.etage_id = 0 AND t_services_facturation.etage_par_defaut_id <> 0 AND t_services_facturation.etage_par_defaut_id = t_places.parent_id) OR
|
|
(t_services_facturation.etage_force_id = t_places.parent_id AND t_services_facturation.etage_force_id <> 0)
|
|
WHERE lieu_id = t_lieux.oid AND nb_seances > 0 AND t_places.date = p_mouvements_sejour.date AND
|
|
nb_seances_dans_taux IS DISTINCT FROM
|
|
CASE
|
|
WHEN t_places.nb_box_seances_theoriques > 0 THEN nb_seances
|
|
WHEN nb_seances_dans_taux > 0 AND t_places.nb_box_seances_theoriques = 0 THEN 0
|
|
ELSE nb_seances_dans_taux END;
|
|
|
|
UPDATE activite.p_mouvements_sejour
|
|
SET nb_seances_dans_taux =
|
|
CASE
|
|
WHEN t_places.nb_box_seances_theoriques > 0 THEN nb_seances
|
|
WHEN nb_seances_dans_taux > 0 AND t_places.nb_box_seances_theoriques = 0 THEN 0
|
|
ELSE nb_seances_dans_taux END
|
|
FROM activite.t_lieux
|
|
JOIN activite.t_divers ON t_divers.code = 'PLACESREF' AND t_divers.valeur = 'SER'
|
|
JOIN activite.t_places ON service_facturation_id = t_places.parent_id
|
|
WHERE lieu_id = t_lieux.oid AND nb_seances > 0 AND t_places.date = p_mouvements_sejour.date AND
|
|
nb_seances_dans_taux IS DISTINCT FROM
|
|
CASE
|
|
WHEN t_places.nb_box_seances_theoriques > 0 THEN nb_seances
|
|
WHEN nb_seances_dans_taux > 0 AND t_places.nb_box_seances_theoriques = 0 THEN 0
|
|
ELSE nb_seances_dans_taux END;
|
|
|
|
UPDATE activite.p_mouvements_sejour
|
|
SET nb_seances_dans_taux =
|
|
CASE
|
|
WHEN t_places.nb_box_seances_theoriques > 0 THEN nb_seances
|
|
WHEN nb_seances_dans_taux > 0 AND t_places.nb_box_seances_theoriques = 0 THEN 0
|
|
ELSE nb_seances_dans_taux END
|
|
FROM activite.t_lieux
|
|
JOIN activite.t_divers ON t_divers.code = 'PLACESREF' AND t_divers.valeur = 'UF'
|
|
JOIN activite.t_places ON unite_fonctionnelle_id = t_places.parent_id
|
|
WHERE lieu_id = t_lieux.oid AND nb_seances > 0 AND t_places.date = p_mouvements_sejour.date AND
|
|
nb_seances_dans_taux IS DISTINCT FROM
|
|
CASE
|
|
WHEN t_places.nb_box_seances_theoriques > 0 THEN nb_seances
|
|
WHEN nb_seances_dans_taux > 0 AND t_places.nb_box_seances_theoriques = 0 THEN 0
|
|
ELSE nb_seances_dans_taux END;
|
|
|
|
UPDATE activite.p_mouvements_sejour
|
|
SET nb_seances_dans_taux =
|
|
CASE
|
|
WHEN t_places.nb_box_seances_theoriques > 0 THEN nb_seances
|
|
WHEN nb_seances_dans_taux > 0 AND t_places.nb_box_seances_theoriques = 0 THEN 0
|
|
ELSE nb_seances_dans_taux END
|
|
FROM activite.t_lieux
|
|
JOIN activite.t_divers ON t_divers.code = 'PLACESREF' AND t_divers.valeur = 'UM'
|
|
JOIN activite.t_places ON unite_medicale_id = t_places.parent_id
|
|
WHERE lieu_id = t_lieux.oid AND nb_seances > 0 AND t_places.date = p_mouvements_sejour.date AND
|
|
nb_seances_dans_taux IS DISTINCT FROM
|
|
CASE
|
|
WHEN t_places.nb_box_seances_theoriques > 0 THEN nb_seances
|
|
WHEN nb_seances_dans_taux > 0 AND t_places.nb_box_seances_theoriques = 0 THEN 0
|
|
ELSE nb_seances_dans_taux END;
|
|
END IF;
|
|
|
|
-- Application des poles aux places
|
|
UPDATE activite.t_places
|
|
SET pole_id = pole_occupation_id
|
|
FROM activite.t_places_parents
|
|
JOIN activite.t_etages_historique ON t_places_parents.oid = t_etages_historique.etage_id
|
|
WHERE t_places.parent_id = t_places_parents.oid AND
|
|
parent_type = 'ETA' AND
|
|
t_places.date BETWEEN t_etages_historique.date AND t_etages_historique.date_fin
|
|
;
|
|
|
|
RETURN 'OK';
|
|
END;
|