1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168
|
--premiere fonction pour la premire table ( elle marche)
CREATE OR REPLACE FUNCTION importer_rsa (donnee text ) RETURNS void AS
$BODY$
DECLARE
a character varying(9); b character varying(3); c character varying(10); d character varying(3); e character varying(3); f character varying(2) ; g character varying(2) ; h character varying(1);
i character varying(2) ; j character varying(1) ; k character varying(3) ; l character varying(2) ; m character varying(2) ; n character varying(1) ; o character varying(2); p character varying(1) ;
q character varying(3); r character varying(2) ; s character varying(3) ; t character varying(3) ; u character varying(1) ; v character varying(1) ; w character varying(1) ; x character varying(2) ;
y character varying(4) ; z character varying(1) ; ab character varying(1) ; ac character varying(1); ad character varying(4) ;ae character varying(5) ;af character varying(4) ;ag character varying(2) ;
ah character varying(3) ; ai character varying(4) ; aj character varying(4) ; ak character varying(1) ; al character varying(4) ; am character varying(3) ; an character varying(3) ; ao character varying(3) ;
ap character varying(3) ; aq character varying(2) ; ar character varying(3) ; as character varying(3) ;at character varying(3) ;au character varying(3); av character varying(1) ; aw character varying(3) ;
ax character varying(3); ay character varying(3) ; az character varying(3) ; a1 character varying(3); a2 character varying(3); a3 character varying(3) ; a4 character varying(3); a5 character varying(3) ;
a6 character varying(1) ; a7 character varying(6) ; a8 character varying(6) ; a9 character varying(2) ; a10 character varying(4) ;
begin
a:=substring (donnee from 1 for 9); b:= substring (donnee from 10 for 3); c:= substring (donnee from 13 for 10); d:= substring (donnee from 23 for 3); e:= substring (donnee from 26 for 3); f:= substring (donnee from 29 for 2); g:= substring (donnee from 31 for 2);
h:= substring (donnee from 33 for 1); i:= substring (donnee from 34 for 2); j:= substring (donnee from 36 for 1); k:= substring (donnee from 37 for 3); l:= substring (donnee from 40 for 2); m:= substring (donnee from 42 for 2); n:= substring (donnee from 44 for 1);
o:= substring (donnee from 45 for 2); p:= substring (donnee from 47 for 1); q:= substring (donnee from 48 for 3); r:= substring (donnee from 51 for 2); s:= substring (donnee from 53 for 3); t:= substring (donnee from 56 for 3);
u:= substring (donnee from 59 for 1);
v:= substring (donnee from 60 for 1); W:= substring (donnee from 61 for 1); x:= substring (donnee from 62 for 2); y:= substring (donnee from 64 for 4); z:= substring (donnee from 68 for 1); ab:= substring (donnee from 69 for 1); ac:= substring (donnee from 70 for 1);
ad:= substring(donnee from 71 for 4); ae:= substring(donnee from 75 for 5); af:= substring(donnee from 80 for 4); ag:=substring (donnee from 84 for 2); ah:= substring(donnee from 86 for 3); ai:= substring(donnee from 89 for 4);
aj:= substring (donnee from 93 for 4); ak:= substring(donnee from 97 for 1); al:= substring(donnee from 98 for 4); am:= substring(donnee from 102 for 3); an:= substring(donnee from 105 for 3);ao:= substring(donnee from 108 for 3);
ap:= substring(donnee from 111 for 3); aq:= substring (donnee from 114 for 2);
ar:= substring(donnee from 116 for 3);as:= substring(donnee from 119 for 3); at:= substring(donnee from 122 for 3); au:= substring(donnee from 125 for 3);av:= substring(donnee from 128 for 1); aw:= substring(donnee from 129 for 3); ax:= substring (donnee from 132 for 3);
ay:= substring(donnee from 135 for 3); az:= substring(donnee from 138 for 3);a1:= substring(donnee from 141 for 3); a2:= substring(donnee from 144 for 3); a3:= substring(donnee from 147 for 3); a4:= substring(donnee from 150 for 3); a5:= substring (donnee from 153 for 3);
a6:= substring(donnee from 156 for 1); a7:= substring(donnee from 157 for 6); a8:= substring(donnee from 163 for 6); a9:= substring(donnee from 169 for 2); a10:=substring(donnee from 171 for 4);
INSERT INTO rsa (num_finess, num_vers_rsa, num_index_rsa , num_vers_rssgpe ,num_vers_genrsa, Gpgelu_vers_classif, gpgelu_ghm_Cmd, Gpgelu_ghm_Type ,gpgelu_ghm_Num, gpgelu_ghm_Cplexite,
Gpgelu_code_retour ,Gpgegenrsa_vers_classif ,Gpgegenrsa_ghm_Cmd,gpgegenrsa_ghm_Type,gpgegenrsa_ghm_num , gpgegenrsa_ghm_Cplexite, Gpgegenrsa_code_retour,
nb_rum_rss_orig ,age_en_annee ,age_en_jour, Sexe ,mode_entree_pmsimco ,Provenance ,mois_sortie ,annee_sortie ,mode_sortie_pmsimco, Destination, type_sej,
duree_total_sej_pmsi,code_geog_res,poids_entree,nb_seance ,Igs2 , num_ghs_ghmgenrsa,
nb_jne_sup_bor_extr_haute , sej_inf_bor_extr_basse ,
forfait_dialyse, nb_sup_hem_hseance ,
nb_sup_entr_dia_per_auto_hseance,
nb_sup_entr_dia_per_cont_amb_hseance, nb_sup_entr_hem_hseance ,
nb_seance_avt_sros,
nb_acte_men_ghm24z05z , nb_acte_men_ghm24z06z ,
nb_acte_men_ghm24z07z,
nb_sup_caisson_hyperbare ,type_prest_prelv_org , nb_sup_sra_rean , nb_sup_rea_rean ,
nb_sup_soin_int_prov_rean , nb_sup_stf , nb_sup_ssc , nb_sup_src,nb_sup_nn1 ,nb_sup_nn2 ,nb_sup_nn3,Pge_lit_dedie_soin_pal ,Dp , Dr, nb_diag_ass_sig_rsa, nb_zone_acte_rsa )
values (a, b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z,ab,ac,ad,ae,af,ag,ah,
ai,aj,ak,al,am,an,ao, ap,aq,ar,as,at,au,av,aw,ax,ay,az,a1,a2,a3,a4,a5,a6,a7,a8,a9,a10);
END;
$BODY$
LANGUAGE plpgsql ;
-- deuxieme fonction pour la deuxieme table , marche aussi
create or replace function importer_um (donnee text) returns void as --fonction qui importe 1 um dans la table unité_médicale
$BODY$
declare
-- declaration des variables
a character varying(2);
b character varying(3);
c character varying(1);
d character varying(1);
e character varying(2);
begin
-- soustraction des sous chaines et leur affectation aux variables
a:= substring (donnee_um from 1 for 2);
b:= substring (donnee_um from 3 for 3);
c:= substring (donnee_um from 6 for 1);
d:= substring (donnee_um from 7 for 1);
e:= substring (donnee_um from 8 for 2);
insert into unite_medicale (type_um , dur_sej_part , val_rea , val_part, position_dp ) values (a, b, c, d, e);
END;
$BODY$
LANGUAGE plpgsql ;
-- 3emme fonction pour la 3eme , elle marche aussi
create or replace function importer_diag (donnee_diag text ) returns void as --fonction qui importe 1 um dans la table unité_médicale
$BODY$
declare -- declaration des variables
liste varchar (6);
begin
-- soustraction des sous chaines et leur affectation aux variables
liste:=substring(donnee_diag from 1 for 6 );
insert into diagnostique (liste_code_diagnostique ) values (liste);
END;
$BODY$
LANGUAGE plpgsql ;
-- 4eme fction pour la 4eme table , marche aussi
create or replace function importer_acte (donnee text) returns void as --fonction qui importe 1 acte dans la table acte
$BODY$
declare -- declaration des variables
a character varying(3);
b character varying(7);
c character varying(1) ;
d character varying(1) ;
e character varying(1);
f character varying(4);
g character varying(1);
h character varying(1);
i character varying(2);
begin
-- soustraction des sous chaines et leur affectation aux variables
a:= substring (donnee_acte from 1 for 3);
b:= substring (donnee_acte from 4 for 7);
c:= substring (donnee_acte from 11 for 1);
d:= substring (donnee_acte from 12 for 1);
e:= substring (donnee_acte from 13 for 1);
f:= substring (donnee_acte from 14 for 4);
g:= substring (donnee_acte from 18 for 1);
h:= substring (donnee_acte from 19 for 1);
i:= substring (donnee_acte from 20 for 2);
insert into acte (delai_dep_dat_entree ,code_ccam , phase , activite, exten_doc,modificateur,remb_excep,ass_non_prevue,nb_exe_acte ) values (a, b, c, d, e,f,g,h,i);
END;
$BODY$
LANGUAGE plpgsql ;
--- la grande fonction où j'appel Les 4 precedentes , elle ne les reconnait pas apparemment
create or replace function importer_pmsi (donnee_pmsi text ) returns void as
$BODY$
declare
bloc_rsa character varying(174); --données du rsa
bloc_um character varying(891); --données de l'ensemble des um
bloc_diag character varying(594); --données de l'ensemble des diagnostics
bloc_acte character varying(209979); --données de l'ensemble des actes
nb_um Integer; --nombre de um
nb_diag Integer; --nombre de diagnostics
nb_acte Integer; --nombre d'actes
temp character varying(209979); --variable temporaire
begin
bloc_rsa:=substring(donnee_pmsi from 1 for 174);
nb_um := CAST(substring(donnee_pmsi from 51 for 2) AS Integer); --c'est le nombre de UM
nb_diag := CAST(substring(donnee_pmsi from 169 for 2) AS Integer); -- c'est la nombre de diagnostic
nb_acte := CAST(substring(donnee_pmsi from 171 for 4) AS Integer); --c'est la nombre d'actes
bloc_um:=substring(donnee_pmsi from 175 for num_um*9); --tous les blocs de données des um
bloc_diag:=substring(donnee_pmsi from (175+num_um*9) for num_diag*6); --tous les blocs de données des diagnostics
bloc_acte:=substring(donnee_pmsi from (175+num_um*9+num_diag*6) for num_acte*21); --tous les blocs de données des actes
PERFORM importer_rsa(bloc_rsa);
for l in 0..nb_um loop
temp:= substring(bloc_um from (l*9+1) for 9)
importer_um(temp);
end loop ;
for l in 0..nb_diag loop
temp:= substring(bloc_diag from (l*6+1) for 6)
select importer_diag (temp);
end loop ;
for l in 0..nb_acte loop
temp:= substring(bloc_acte from (l*21+1) for 21)
importer_acte(temp);
end loop ;
END;
$BODY$
LANGUAGE plpgsql ;
select importer_pmsi('01000840721300000000171110021024C30Z0001024C30Z00001033 28 0820068 00018440000000000080290000099990000000000000000000000000000000000000000000000000000000O039 000003 00123DP001JNJD00204 001001JNJD00201 001001DEQP00701 001'); |
Partager