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'); |
