· 9 years ago · Oct 12, 2016, 07:02 PM
1CREATE OR REPLACE FUNCTION "public"."fnc_union_lpu"()
2 RETURNS "pg_catalog"."void" AS $BODY$
3
4DECLARE _r TEXT [];
5_c TEXT [];
6_arr INTEGER [];
7_arr_dist INTEGER [];
8_min INTEGER;
9_max INTEGER;
10_clause INTEGER;
11_rate INTEGER;
12_period TEXT;
13errCode varchar;
14errText varchar;
15rc record;
16BEGIN
17 /*------------------------------------------
18 Механизм Ð¾Ð±ÑŠÐµÐ´Ð¸Ð½ÐµÐ½Ð¸Ñ ÐœÐž.
19 ------------------------------------------*/
20 ------- Проверка на наличие данных Ð´Ð»Ñ Ð¾Ð±ÑŠÐµÐ´Ð¸Ð½ÐµÐ½Ð¸Ñ ( tmp_union_org )
21 CREATE TABLE IF NOT EXISTS "public"."log_table" (
22"errCode" varchar(255) COLLATE "default",
23"errText" varchar(500) COLLATE "default",
24"type" varchar(255) COLLATE "default"
25) ;
26
27 IF NOT EXISTS (
28 SELECT true
29 FROM tmp_union_org
30 ) THEN RAISE EXCEPTION 'Ðет данных Ð´Ð»Ñ Ð¾Ð±ÑŠÐµÐ´Ð¸Ð½ÐµÐ½Ð¸Ñ!!! Заполните таблицу: tmp_union_org';END
31 IF ;
32 --------DROP TEMP TABLE----------
33 DROP TABLE
34 IF EXISTS tmp_new_services;
35 DROP TABLE
36 IF EXISTS tmp_old_sevice;
37
38 --ÑтатиÑтика
39 INSERT into tmp_union_stat (
40 WITH param AS (
41 SELECT unnest((array_agg(old_org_id) || array_agg(new_org_id))::INTEGER []) AS org_id
42 FROM tmp_union_org
43 ) SELECT 'ДО'::TEXT AS "Шаг"
44 ,p.org_id AS "ЛПУ"
45 ,dep.cnt AS "Kол-во подразделений"
46 ,pos.cnt AS "Kол-во штаток"
47 ,emp.cnt AS "Kол-во Ñотрудников"
48 ,res.cnt AS "Kол-во ÑоÑÑ‚.реÑурÑов"
49 ,ser.cnt AS "Kол-во уÑлуг"
50 ,cs.cnt AS "Kол-во Ñлучаев"
51 ,srv.cnt AS "Kол-во оказанных уÑлуг"
52 ,reg.cnt AS "Kол-во пациентов" FROM param p LEFT JOIN (
53 SELECT count(1) AS cnt
54 ,d.org_id AS org_id
55 FROM pim_department d
56 WHERE d.org_id IN (
57 SELECT org_id
58 FROM param
59 )
60 GROUP BY 2
61 ) dep ON dep.org_id = p.org_id LEFT JOIN (
62 SELECT count(1) AS cnt
63 ,d.organization_id AS org_id
64 FROM pim_position d
65 WHERE d.organization_id IN (
66 SELECT org_id
67 FROM param
68 )
69 GROUP BY 2
70 ) pos ON pos.org_id = p.org_id LEFT JOIN (
71 SELECT count(1) AS cnt
72 ,d.organization_id AS org_id
73 FROM pim_employee d
74 WHERE d.organization_id IN (
75 SELECT org_id
76 FROM param
77 )
78 GROUP BY 2
79 ) emp ON emp.org_id = p.org_id LEFT JOIN (
80 SELECT count(1) AS cnt
81 ,d.org_id AS org_id
82 FROM sr_res_group d
83 WHERE d.org_id IN (
84 SELECT org_id
85 FROM param
86 )
87 GROUP BY 2
88 ) res ON res.org_id = p.org_id LEFT JOIN (
89 SELECT count(1) AS cnt
90 ,d.org_id AS org_id
91 FROM sr_service d
92 WHERE d.org_id IN (
93 SELECT org_id
94 FROM param
95 )
96 GROUP BY 2
97 ) ser ON ser.org_id = p.org_id LEFT JOIN (
98 SELECT count(1) AS cnt
99 ,d.clinic_id AS org_id
100 FROM mc_case d
101 WHERE d.clinic_id IN (
102 SELECT org_id
103 FROM param
104 )
105 GROUP BY 2
106 ) cs ON cs.org_id = p.org_id LEFT JOIN (
107 SELECT count(1) AS cnt
108 ,d.org_id AS org_id
109 FROM sr_srv_rendered d
110 WHERE d.org_id IN (
111 SELECT org_id
112 FROM param
113 )
114 GROUP BY 2
115 ) srv ON srv.org_id = p.org_id LEFT JOIN (
116 SELECT count(1) AS cnt
117 ,d.clinic_id AS org_id
118 FROM pci_patient_reg d
119 WHERE d.clinic_id IN (
120 SELECT org_id
121 FROM param
122 )
123 GROUP BY 2
124 ) reg ON reg.org_id = p.org_id ORDER BY 2 ASC
125 );
126
127 /*-----------START DEPARTMENT---------- еÑли необходимо Ñамим Ñоздать подразделение
128 SELECT setval('dc_departament_seq', (
129 SELECT max(id)
130 FROM pim_department
131 ))
132 INTO _min + 1;
133 CREATE TEMP TABLE temp_dep_id AS (
134 WITH ins_dep AS (
135 INSERT INTO pim_department (
136 id
137 ,code
138 ,NAME
139 ,type_id
140 ,org_id
141 ) (
142 SELECT nextval('dc_departament_seq') AS id
143 ,o.id AS old_org_id
144 ,replace(substring(o.short_name, position('"' IN o.short_name)), '"', '') || ' (Ф)' AS NAME
145 ,10 AS type_id
146 ,t.new_org_id FROM pim_organization o JOIN (
147 SELECT old_org_id
148 ,new_org_id
149 FROM tmp_union_org
150 ) t ON o.id = t.old_org_id
151 ) RETURNING code::INTEGER AS old_org_id
152 ,id::INTEGER AS new_dep_id
153 ) SELECT * FROM ins_dep
154 );
155 SELECT currval('dc_departament_seq')
156 INTO _max;
157 INSERT INTO tmp_union_lpu_log (
158 num
159 ,step
160 ,query
161 )
162 VALUES (
163 1
164 ,'DEPARTMENT'
165 ,'DELETE FROM pim_department WHERE id between ' || _min || ' AND ' || _max || ';'
166 );
167 _min = NULL;
168 _max = NULL;
169 UPDATE tmp_union_org u
170 SET new_dep_id = t.new_dep_id
171 FROM temp_dep_id t
172 WHERE t.old_org_id = u.old_org_id;
173 UPDATE pim_department d
174 SET parent_id = NULL
175 WHERE d.parent_id = d.id;*/
176---переноÑим подразделение Ñтарых мо
177 INSERT INTO tmp_union_lpu_log (
178 num
179 ,step
180 ,query
181 ) (
182 WITH param AS (
183 SELECT d.id
184 ,d.org_id
185 ,d.parent_id
186 FROM pim_department d
187 WHERE d.org_id IN (
188 SELECT old_org_id
189 FROM tmp_union_org
190 )
191 ) SELECT 2
192 ,'DEPARTMENT'
193 ,CONCAT (
194 'UPDATE pim_department u SET parent_id = '
195 ,f.parent_id
196 ,', org_id = '
197 ,f.org_id
198 ,' WHERE u.id = '
199 ,f.id
200 ,';'
201 ) FROM param f
202 );
203 UPDATE pim_department d
204 SET parent_id = f.new_dep_id
205 ,org_id = f.new_org_id
206 FROM tmp_union_org f
207 WHERE d.org_id = f.old_org_id
208 AND d.parent_id IS NULL;
209 INSERT INTO tmp_union_lpu_log (
210 num
211 ,step
212 ,query
213 ) (
214 WITH param AS (
215 SELECT o.unit_id AS old_unit
216 ,o2.unit_id AS new_unit
217 FROM tmp_union_org t
218 JOIN pim_organization o ON o.id = t.old_org_id
219 JOIN pim_organization o2 ON o2.id = t.new_org_id
220 ) SELECT 2
221 ,'CONTEXT'
222 ,CONCAT (
223 'UPDATE sec_user_context_default u SET org_unit_id = '
224 ,f.new_unit
225 ,' WHERE u.org_unit_id = '
226 ,f.old_unit
227 ,' and id ?;'
228 ) FROM param f
229 );
230 UPDATE sec_user_context_default u
231 SET org_unit_id = f.new_unit
232 FROM (
233 SELECT o.unit_id AS old_unit
234 ,o2.unit_id AS new_unit
235 FROM tmp_union_org t
236 JOIN pim_organization o ON o.id = t.old_org_id
237 JOIN pim_organization o2 ON o2.id = t.new_org_id
238 ) f
239 WHERE u.org_unit_id = f.old_unit;
240INSERT INTO tmp_union_lpu_log (
241 num
242 ,step
243 ,query
244 ) (
245 WITH param AS (
246 SELECT old_org_id,new_org_id
247 FROM tmp_union_org t
248 ) SELECT 2
249 ,'SEC_USER'
250 ,CONCAT (
251 'UPDATE sec_user_org u SET org_id = '
252 ,f.new_org_id
253 ,' WHERE u.org_id = '
254 ,f.old_org_id
255 ,';'
256 ) FROM param f
257 );
258 UPDATE sec_user_org u
259 SET org_id = t.new_org_id
260 FROM
261 tmp_union_org t
262 WHERE u.org_id = t.old_org_id
263 and not exists(select 1 from sec_user_org where org_id=t.new_org_id and user_id=u.user_id);
264 delete from sec_user_org where org_id in (select old_org_id from tmp_union_org where not is_delete);
265-----PCI_PATIENT_REG
266Insert into pci_patient_reg
267select nextval('pci_patient_reg_id_seq'),current_date,p.request_dt,p.request_uid,null,t.new_org_id,p.district_id,p.district_by_addr_id,p.address_id,p.patient_id,p.state_id,p.type_id,null,p.is_assertion,p.automatic_district_dt,p.number_attach
268from pci_patient_reg p
269join tmp_union_org t on t.old_org_id=p.clinic_id
270where state_id=1
271and unreg_dt is null
272and not exists(select 1 from pci_patient_reg where clinic_id=t.new_org_id and patient_id=p.patient_id and type_id=p.type_id and state_id=p.state_id)
273;
274/* UPDATE pci_patient_reg p
275 SET clinic_id = t.new_org_id
276 FROM
277 tmp_union_org t
278 WHERE p.clinic_id = t.old_org_id
279 and state_id=1
280 and not exists(select 1 from pci_patient_reg where clinic_id=t.new_org_id and patient_id=p.patient_id and type_id=p.type_id and state_id=p.state_id);
281delete from pci_patient_reg r
282where exists (select 1 from tmp_union_org u where u.old_org_id=r.clinic_id and not is_delete);*/
283UPDATE pci_patient_reg p SET unreg_dt=current_date,state_id=2
284where clinic_id in (select old_org_id from tmp_union_org where not is_delete )
285and state_id=1;
286
287INSERT INTO tmp_union_lpu_log (
288 num
289 ,step
290 ,query
291 ) (
292 WITH param AS (
293 SELECT old_org_id,new_org_id
294 FROM tmp_union_org t
295 ) SELECT 2
296 ,'TICKET'
297 ,CONCAT (
298 'UPDATE md_ticket_number_org SET org_id = '
299 ,f.new_org_id
300 ,' WHERE org_id = '
301 ,f.old_org_id
302 ,';'
303 ) FROM param f
304 );
305 UPDATE md_ticket_number_org n
306 SET org_id = t.new_org_id
307 FROM
308 tmp_union_org t
309 WHERE n.org_id = t.old_org_id;
310
311 -------- SR_RES_GROUP
312 INSERT INTO tmp_union_lpu_log (
313 num
314 ,step
315 ,query
316 ) (
317 WITH param AS (
318 SELECT g.id
319 ,g.org_id
320 ,g.department_id
321 FROM sr_res_group g
322 JOIN tmp_union_org t ON g.org_id = t.old_org_id
323 JOIN sr_res_group_source s ON g.id = s.res_group_id
324 AND s.source_id = 0
325 ) SELECT 3
326 ,'SR_RES_GROUP'
327 ,CONCAT (
328 'UPDATE sr_res_group u SET department_id = '
329 ,f.department_id
330 ,', org_id = '
331 ,f.org_id
332 ,' WHERE u.id = '
333 ,f.id
334 ,';'
335 ) FROM param f
336 );
337 UPDATE sr_res_group u
338 SET department_id = f.new_dep_id
339 FROM (
340 SELECT sg.id
341 ,t.new_dep_id
342 FROM sr_res_group sg
343 JOIN tmp_union_org t ON sg.org_id = t.old_org_id
344 WHERE sg.department_id IS NULL
345 ) f
346 WHERE f.id = u.id;
347 UPDATE sr_res_group AS g
348 SET org_id = t.new_org_id
349 FROM tmp_union_org AS t
350 ,sr_res_group_source AS s
351 WHERE g.org_id = t.old_org_id
352 AND g.id = s.res_group_id
353 AND s.source_id = 0 --portal
354 ;
355 -----------START SERVICES----------
356 PERFORM(SELECT setval('sr_service_id_seq', (
357 SELECT max(id)
358 FROM sr_service
359 )));
360
361/* END */
362CREATE TABLE tmp_new_services AS (
363 SELECT * FROM sr_service WHERE org_id IN (
364 SELECT t.new_org_id
365 FROM tmp_union_org t
366 )
367 );
368INSERT INTO tmp_new_services (
369 id
370 ,code
371 ,cul
372 ,is_death
373 ,duration
374 ,is_fictitious
375 ,is_independent
376 ,is_multuplicity
377 ,NAME
378 ,category_id
379 ,duration_unit_id
380 ,prototype_id
381 ,type_id
382 ,from_dt
383 ,to_dt
384 ,is_repeated
385 ,is_inherit_protocol
386 ,is_expendable_materials
387 ,is_actual_cul
388 ,is_paraclinical
389 ,is_complex
390 ,accounting_id
391 ,org_id
392 ,terms
393 ) (
394 SELECT s.id
395 ,s.code
396 ,s.cul
397 ,s.is_death
398 ,s.duration
399 ,s.is_fictitious
400 ,s.is_independent
401 ,s.is_multuplicity
402 ,s.NAME--||' '||t.old_org_id
403 ,s.category_id
404 ,s.duration_unit_id
405 ,s.prototype_id
406 ,s.type_id
407 ,s.from_dt
408 ,s.to_dt
409 ,s.is_repeated
410 ,s.is_inherit_protocol
411 ,s.is_expendable_materials
412 ,s.is_actual_cul
413 ,s.is_paraclinical
414 ,s.is_complex
415 ,s.accounting_id
416 ,s.org_id
417 ,t.old_org_id || ' to ' || t.new_org_id
418FROM sr_service s JOIN tmp_union_org t ON t.old_org_id = s.org_id LEFT JOIN tmp_new_services n ON trim(lower(s.NAME)) = trim(lower(n.NAME))
419 AND s.code = n.code
420 AND n.type_id = s.type_id
421 AND n.org_id = t.new_org_id WHERE n.id IS NULL
422 );----old_org service
423INSERT INTO tmp_union_lpu_log (
424 num
425 ,step
426 ,query
427 ) (
428 WITH param AS (
429 SELECT g.id
430 ,g.org_id
431 FROM sr_service g
432 JOIN tmp_union_org t ON g.org_id = t.old_org_id
433 ) SELECT 4
434 ,'SR_SERVICE'
435 ,CONCAT (
436 'UPDATE sr_service u SET org_id = '
437 ,f.org_id
438 ,' WHERE u.id = '
439 ,f.id
440 ,';'
441 ) FROM param f
442 );
443UPDATE sr_service s
444SET org_id = f.new_org_id
445,name=f.name
446FROM (
447 SELECT t.new_org_id,f.name
448 ,f.id
449 FROM tmp_new_services f
450 JOIN tmp_union_org t ON f.terms LIKE '%to%'
451 AND t.old_org_id = f.org_id
452 ) f
453WHERE s.id = f.id;
454CREATE TABLE tmp_old_sevice AS (
455 SELECT s.id AS id0
456 ,n.id AS id1,t.is_delete FROM (
457 SELECT id
458 ,type_id
459 ,prototype_id
460 ,NAME
461 ,code
462 ,org_id
463 FROM sr_service
464 WHERE org_id IN (
465 SELECT t.old_org_id
466 FROM tmp_union_org t
467 )
468 ) s JOIN tmp_union_org t ON t.old_org_id = s.org_id JOIN tmp_new_services n ON trim(lower(s.NAME)) = trim(lower(n.NAME))
469 AND s.code = n.code
470 AND n.type_id = s.type_id
471 AND n.org_id = t.new_org_id
472 );
473-------------BILLINGS-----------
474insert into fin_bill_main_department(id,main_bill_id,department_id)
475select nextval('fin_bill_main_department_seq'),
476m.id,u.new_dep_id
477from tmp_union_org u
478join fin_bill_main m on u.old_org_id=m.clinic_id;
479
480INSERT INTO tmp_union_lpu_log (
481 num
482 ,step
483 ,query
484 ) (
485 WITH param AS (
486 SELECT g.id
487 ,g.clinic_id
488 FROM fin_price_list g
489 JOIN tmp_union_org t ON g.clinic_id = t.old_org_id
490 ) SELECT 5
491 ,'FIN_PRICE_LIST'
492 ,CONCAT (
493 'UPDATE fin_price_list u SET clinic_id = '
494 ,f.clinic_id
495 ,' WHERE u.id = '
496 ,f.id
497 ,';'
498 ) FROM param f
499 );
500UPDATE fin_price_list u
501SET clinic_id = f.new_org_id
502 ,commentary = f.old_org_id || 'to' || f.new_org_id
503FROM tmp_union_org f
504WHERE u.clinic_id = f.old_org_id;
505_min = NULL;
506_max = NULL;
507update fin_pl_pos_to_clinic_srv c
508set clinic_service_id=o.id1
509from tmp_old_sevice o
510where clinic_service_id=o.id0
511and not exists (select 1 from fin_pl_pos_to_clinic_srv where pl_position_id=c.pl_position_id and clinic_service_id=o.id1);
512update fin_bill_generate
513set service_id=o.id1
514from tmp_old_sevice o
515where service_id=o.id0;
516update fin_bill_steps set service_id=o.id1
517from tmp_old_sevice o
518where service_id=o.id0;
519-----------END SERVICES----------
520-----------START PORTAL-----------
521INSERT INTO tmp_union_lpu_log (
522 num
523 ,step
524 ,query
525 ) (
526 WITH param AS (
527 SELECT g.id
528 ,g.clinic_id
529 FROM pc_clinic_to_group g
530 JOIN tmp_union_org t ON g.clinic_id = t.old_org_id
531 ) SELECT 8
532 ,'PORTAL'
533 ,CONCAT (
534 'UPDATE pc_clinic_to_group u SET clinic_id = '
535 ,f.clinic_id
536 ,' WHERE u.id = '
537 ,f.id
538 ,' AND NOT EXISTS ( select true from pc_clinic_to_group where clinic_id = '
539 ,f.clinic_id
540 ,' and group_id = u.group_id )'
541 ,';'
542 ) FROM param f
543 );
544UPDATE pc_clinic_to_group g
545SET clinic_id = t.new_org_id
546FROM tmp_union_org t
547WHERE g.clinic_id = t.old_org_id
548 AND NOT EXISTS (
549 SELECT true
550 FROM pc_clinic_to_group
551 WHERE clinic_id = t.new_org_id
552 AND group_id = g.group_id
553 );
554INSERT INTO pc_department (
555 NAME
556 ,id
557 ,is_visible
558 ,ordern
559 )
560 SELECT d.NAME
561 ,d.id
562 ,TRUE
563 ,1 FROM pim_department d JOIN tmp_union_org t ON d.id = t.new_dep_id and exists(select 1 from pc_clinic where id=t.new_org_id)
564 ;
565INSERT INTO tmp_union_lpu_log (
566 num
567 ,step
568 ,query
569 ) (
570 WITH param AS (
571 SELECT new_dep_id
572 FROM tmp_union_org
573 ) SELECT 9
574 ,'PORTAL'
575 ,'DELETE FROM pc_department WHERE id = ' || new_dep_id || ';' FROM param
576 );
577INSERT INTO pc_department_to_group (id, group_id, department_id)
578 select nextval('pc_department_to_group_seq'),
579 c.group_id,
580 u.new_dep_id
581 from tmp_union_org u join pc_clinic_to_group c ON c.clinic_id = u.new_org_id
582and not exists (select 1 from pc_department_to_group where department_id=u.new_dep_id and group_id=c.group_id)
583;
584DELETE
585FROM pc_clinic_to_group p
586WHERE p.clinic_id IN (
587 SELECT old_org_id
588 FROM tmp_union_org
589 );
590DELETE
591FROM pc_clinic p
592WHERE p.id IN (
593 SELECT old_org_id
594 FROM tmp_union_org
595 );
596-----------END PORTAL----------
597-----------КВОТЫ---------------
598FOR
599rc in
600(select o.id as clause_id,qc.quotum_id,r.id as rate_id,r.edate,rp.code as period_code,qr.value,o.org_id,u.new_org_id from tmp_union_org u
601join md_quotum_refer_org o on o.org_id=u.old_org_id
602join sr_quotum_clause qc on qc.id=o.id
603join sr_quotum_rate r on r.clause_id=qc.id
604join sr_quotum_quantity_rate qr on qr.id=r.id
605join sr_quotum_rate_period rp on rp.id=r.period_id
606)
607 loop
608select o.id as clause_id,r.id as rate_id,rp.code
609into _clause,_rate,_period
610from md_quotum_refer_org o
611join sr_quotum_clause qc on qc.id=o.id and qc.quotum_id=rc.quotum_id
612join sr_quotum_rate r on r.clause_id=qc.id
613join sr_quotum_quantity_rate qr on qr.id=r.id and (edate>CURRENT_DATE or edate is null)
614join sr_quotum_rate_period rp on rp.id=r.period_id
615where o.org_id=rc.new_org_id;
616if rc.edate<=CURRENT_DATE THEN
617delete from sr_srv_rendered_q_rate where clause_id=rc.clause_id;
618delete from md_quotum_dispensary where id=rc.clause_id;
619delete from md_quotum_referral where id=rc.clause_id;
620delete from md_quotum_executor where id=rc.clause_id;
621delete from md_quotum_service where id=rc.clause_id;
622delete from md_quotum_srv_proto where id=rc.clause_id;
623delete from sr_q_template_clause where id=rc.clause_id;
624delete from sr_quotum_clause where id=rc.clause_id;
625end if;
626if (rc.edate>CURRENT_DATE or rc.edate is null) and _rate is null
627then
628update md_quotum_refer_org set org_id=rc.new_org_id where org_id=rc.org_id;
629end if;
630if (rc.edate>CURRENT_DATE or rc.edate is null) and _rate is not null THEN
631 if _period=rc.period_code THEN
632update sr_srv_rendered_q_rate set rate_id=_rate,clause_id=_clause
633where clause_id=rc.clause_id;
634update sr_quotum_quantity_rate set value=value+rc.value
635where id=_rate;
636delete from sr_srv_rendered_q_rate where clause_id=rc.clause_id;
637delete from md_quotum_dispensary where id=rc.clause_id;
638delete from md_quotum_referral where id=rc.clause_id;
639delete from md_quotum_executor where id=rc.clause_id;
640delete from md_quotum_service where id=rc.clause_id;
641delete from md_quotum_srv_proto where id=rc.clause_id;
642delete from sr_q_template_clause where id=rc.clause_id;
643delete from sr_quotum_clause where id=rc.clause_id;
644else RAISE EXCEPTION 'Периоды квот разные!';
645 end if;
646end if;
647end loop;
648-----------------
649-----------SCOPE --------------
650/*FOR
651_r IN
652WITH param AS (
653 SELECT c.scope_id AS old_scope_id
654 ,cl.scope_id AS new_scope_id
655 ,t.old_org_id
656 ,t.new_org_id,t.is_delete
657 FROM tmp_union_org t
658 JOIN md_clinic c ON t.old_org_id = c.id
659 JOIN md_clinic cl ON t.new_org_id = cl.id
660 WHERE cl.scope_id <> c.scope_id
661 )
662 ,cte AS (
663 SELECT CASE
664 WHEN k.column_name <> 'id'
665 AND k.table_name NOT IN ('cmn_setting_value','md_clinic')
666 THEN CONCAT (
667 'UPDATE '
668 ,t.constraint_schema
669 ,'.'
670 ,k.table_name
671 ,' AS u SET '
672 ,k.column_name
673 ,' = '
674 ,f.new_scope_id
675 ,' WHERE u.'
676 ,k.column_name
677 ,' = '
678 ,f.old_scope_id
679 ,';'
680 )
681 when k.column_name = 'id' and f.is_delete then 'DELETE FROM ' || k.table_name || ' WHERE ' || k.column_name || ' = ' || f.old_scope_id || ';'
682 END AS query
683 FROM param f
684 ,information_schema.constraint_table_usage t
685 JOIN information_schema.constraint_column_usage l ON t.constraint_name = l.constraint_name
686 JOIN information_schema.key_column_usage k ON t.constraint_name = k.constraint_name
687 JOIN information_schema.table_constraints c ON t.constraint_name = c.constraint_name
688 JOIN pg_constraint ON pg_constraint.conname = t.constraint_name
689 WHERE c.constraint_type = 'FOREIGN KEY'
690 AND t.table_name = 'cmn_scope'
691 AND k.table_name NOT LIKE '%aud%'
692 AND l.column_name = 'id'
693 )
694SELECT DISTINCT string_to_array((CAST(c.query AS TEXT)), ';') AS query
695FROM cte c
696ORDER BY 1 DESC LOOP if _r[1] is not null then
697INSERT INTO tmp_union_lpu_log (
698 num
699 ,step
700 ,query
701 )
702VALUES (
703 200
704 ,'SCOPE'
705 ,_r [1]
706 );
707EXECUTE (_r [1]);
708end if;
709END
710LOOP;*/
711-----------SERVICE--------------
712FOR
713_r IN
714WITH param AS (
715 SELECT DISTINCT t.id0 AS old_service_id
716 ,t.id1 AS new_service_id,t.is_delete
717 FROM tmp_old_sevice t
718 )
719 ,cte AS (
720 SELECT DISTINCT CASE
721 WHEN k.column_name <> 'id'
722 AND k.table_name NOT IN (
723 'fin_pl_pos_to_clinic_srv'
724 )
725 THEN CONCAT (
726 'UPDATE '
727 ,t.constraint_schema
728 ,'.'
729 ,k.table_name
730 ,' AS u SET '
731 ,k.column_name
732 ,' = '
733 ,f.new_service_id
734 ,' WHERE u.'
735 ,k.column_name
736 ,' = '
737 ,f.old_service_id
738 ,';'
739 )
740 when k.column_name = 'id' and f.is_delete then 'DELETE FROM ' || k.table_name || ' WHERE '|| k.column_name || ' = ' || f.old_service_id || ';'
741 END AS query
742 FROM param f
743 ,information_schema.constraint_table_usage t
744 JOIN information_schema.constraint_column_usage l ON t.constraint_name = l.constraint_name
745 JOIN information_schema.key_column_usage k ON t.constraint_name = k.constraint_name
746 JOIN information_schema.table_constraints c ON t.constraint_name = c.constraint_name
747 JOIN pg_constraint ON pg_constraint.conname = t.constraint_name
748 WHERE c.constraint_type = 'FOREIGN KEY'
749 AND t.table_name = 'sr_service'
750 AND k.table_name NOT LIKE '%aud%'
751 AND l.column_name = 'id'
752 )
753SELECT DISTINCT string_to_array((CAST(c.query AS TEXT)), ',') AS query
754FROM cte c
755ORDER BY 1 DESC LOOP if _r[1] is not null then
756INSERT INTO tmp_union_lpu_log (
757 num
758 ,step
759 ,query
760 )
761VALUES (
762 300
763 ,'SERVICE'
764 ,_r [1]
765 );
766EXECUTE (_r [1]);
767 end if;
768END
769LOOP;
770DELETE
771FROM sr_service
772WHERE id IN (
773 SELECT id0
774 FROM tmp_old_sevice
775 );
776-----------CLINIC --------------
777FOR
778_r IN
779WITH param AS (
780 SELECT t.old_org_id
781 ,t.new_org_id,t.is_delete
782 FROM tmp_union_org t
783 )
784 ,cte AS (
785 SELECT CASE
786 WHEN k.column_name <> 'id'
787 AND k.table_name NOT IN (
788 'md_nosol_registr_clinic'
789 ,'fin_bill_main_child_clinic'
790 ,'fin_bill_main_template_child_clinic'
791 ,'pc_clinic_to_group'
792 ,'pci_patient_reg'
793 )
794 THEN CONCAT (
795 'UPDATE '
796 ,t.constraint_schema
797 ,'.'
798 ,k.table_name
799 ,' AS u SET '
800 ,k.column_name
801 ,' = '
802 ,f.new_org_id
803 ,' WHERE u.'
804 ,k.column_name
805 ,' = '
806 ,f.old_org_id
807 ,';'
808 )
809 when k.column_name = 'id' and f.is_delete then 'DELETE FROM ' || k.table_name || ' WHERE ' || k.column_name || ' = ' || f.old_org_id || ';'
810 END AS query
811 FROM param f
812 ,information_schema.constraint_table_usage t
813 JOIN information_schema.constraint_column_usage l ON t.constraint_name = l.constraint_name
814 JOIN information_schema.key_column_usage k ON t.constraint_name = k.constraint_name
815 JOIN information_schema.table_constraints c ON t.constraint_name = c.constraint_name
816 JOIN pg_constraint ON pg_constraint.conname = t.constraint_name
817 WHERE c.constraint_type = 'FOREIGN KEY'
818 AND t.table_name = 'md_clinic'
819 AND k.table_name NOT LIKE '%aud%'
820 AND l.column_name = 'id'
821 )
822SELECT DISTINCT string_to_array((CAST(c.query AS TEXT)), ';') AS query
823FROM cte c
824ORDER BY 1 DESC LOOP if _r[1] is not null then
825INSERT INTO tmp_union_lpu_log (
826 num
827 ,step
828 ,query
829 )
830VALUES (
831 400
832 ,'CLINIC'
833 ,_r [1]
834 );
835EXECUTE (_r [1]);
836 end if;
837END
838LOOP;
839-----------ORGANIZATION--------------
840FOR
841_r IN
842WITH param AS (
843 SELECT t.old_org_id
844 ,t.new_org_id,t.is_delete
845 FROM tmp_union_org t
846 )
847 ,cte AS (
848 SELECT CASE
849 WHEN k.column_name <> 'id'
850 AND k.table_name NOT IN (
851 'pim_org_funding_type'
852 ,'sr_schedule_org'
853 ,'ntf_event_value'
854 ,'trust_doc'
855 )
856 THEN CONCAT (
857 'UPDATE '
858 ,t.constraint_schema
859 ,'.'
860 ,k.table_name
861 ,' AS u SET '
862 ,k.column_name
863 ,' = '
864 ,f.new_org_id
865 ,' WHERE u.'
866 ,k.column_name
867 ,' = '
868 ,f.old_org_id
869 ,';'
870 )
871 when k.column_name = 'id' and f.is_delete then 'DELETE FROM ' || k.table_name || ' WHERE ' || k.column_name || ' = ' || f.old_org_id || ';'
872 END AS query
873 FROM param f
874 ,information_schema.constraint_table_usage t
875 JOIN information_schema.constraint_column_usage l ON t.constraint_name = l.constraint_name
876 JOIN information_schema.key_column_usage k ON t.constraint_name = k.constraint_name
877 JOIN information_schema.table_constraints c ON t.constraint_name = c.constraint_name
878 JOIN pg_constraint ON pg_constraint.conname = t.constraint_name
879 WHERE c.constraint_type = 'FOREIGN KEY'
880 AND t.table_name = 'pim_organization'
881 AND k.table_name NOT LIKE '%aud%'
882 --and t.constraint_schema='public'
883 AND t.constraint_schema||'.'||k.table_name not in ('public.pim_org_code','sec.n2o_context','public.context')
884 AND l.column_name = 'id'
885 )
886SELECT DISTINCT string_to_array((CAST(c.query AS TEXT)), ';') AS query
887FROM cte c
888ORDER BY 1 DESC LOOP if _r[1] is not null then
889INSERT INTO tmp_union_lpu_log (
890 num
891 ,step
892 ,query
893 )
894VALUES (
895 500
896 ,'ORGANIZATION'
897 ,_r [1]
898 );
899EXECUTE (_r [1]);
900end if;
901END
902LOOP;
903-----------PARTY--------------
904UPDATE sec_user_role_party u
905SET party_id = f.new_org_id
906FROM tmp_union_org f
907WHERE f.old_org_id = u.party_id
908 AND NOT EXISTS (
909 SELECT TRUE
910 FROM sec_user_role_party s
911 WHERE s.party_id = f.new_org_id
912 AND s.user_role_id = u.user_role_id
913 );
914UPDATE sec_user_party u
915SET party_id = f.new_org_id
916FROM tmp_union_org f
917WHERE f.old_org_id = u.party_id
918 AND NOT EXISTS (
919 SELECT TRUE
920 FROM sec_user_party s
921 WHERE s.party_id = f.new_org_id
922 );
923UPDATE pim_party_role_to_party u
924SET party_id = f.new_org_id
925FROM tmp_union_org f
926WHERE f.old_org_id = u.party_id
927 AND NOT EXISTS (
928 SELECT TRUE
929 FROM pim_party_role_to_party s
930 WHERE s.party_id = f.new_org_id
931 AND s.role_id = u.role_id
932 );
933/*FOR
934_r IN
935WITH param AS (
936 SELECT t.old_org_id
937 ,t.new_org_id,t.is_delete
938 FROM tmp_union_org t
939 )
940 ,cte AS (
941 SELECT 'DELETE FROM ' || k.table_name || ' WHERE ' || k.column_name || ' = ' || f.old_org_id || ';'
942 AS query
943 FROM param f
944 ,information_schema.constraint_table_usage t
945 JOIN information_schema.constraint_column_usage l ON t.constraint_name = l.constraint_name
946 JOIN information_schema.key_column_usage k ON t.constraint_name = k.constraint_name
947 JOIN information_schema.table_constraints c ON t.constraint_name = c.constraint_name
948 JOIN pg_constraint ON pg_constraint.conname = t.constraint_name
949 WHERE c.constraint_type = 'FOREIGN KEY'
950 AND t.table_name = 'pim_party'
951 AND k.table_name NOT LIKE '%aud%'
952 AND l.column_name = 'id'
953 )
954SELECT DISTINCT string_to_array((CAST(c.query AS TEXT)), ';') AS query
955FROM cte c
956ORDER BY 1 DESC LOOP if _r[1] is not null then
957INSERT INTO tmp_union_lpu_log (
958 num
959 ,step
960 ,query
961 )
962VALUES (
963 600
964 ,'PARTY'
965 ,_r [1]
966 );
967EXECUTE (_r [1]); end if;
968END
969LOOP;*/
970INSERT INTO tmp_union_stat (
971 WITH param AS (
972 SELECT unnest((array_agg(old_org_id) || array_agg(new_org_id))::INTEGER []) AS org_id
973 FROM tmp_union_org
974 ) SELECT 'ПОСЛЕ'::TEXT AS "Шаг"
975 ,p.org_id AS "ЛПУ"
976 ,dep.cnt AS "Kол-во подразделений"
977 ,pos.cnt AS "Kол-во штаток"
978 ,emp.cnt AS "Kол-во Ñотрудников"
979 ,res.cnt AS "Kол-во ÑоÑÑ‚.реÑурÑов"
980 ,ser.cnt AS "Kол-во уÑлуг"
981 ,cs.cnt AS "Kол-во Ñлучаев"
982 ,srv.cnt AS "Kол-во оказанных уÑлуг"
983 ,reg.cnt AS "Kол-во пациентов" FROM param p LEFT JOIN (
984 SELECT count(1) AS cnt
985 ,d.org_id AS org_id
986 FROM pim_department d
987 WHERE d.org_id IN (
988 SELECT org_id
989 FROM param
990 )
991 GROUP BY 2
992 ) dep ON dep.org_id = p.org_id LEFT JOIN (
993 SELECT count(1) AS cnt
994 ,d.organization_id AS org_id
995 FROM pim_position d
996 WHERE d.organization_id IN (
997 SELECT org_id
998 FROM param
999 )
1000 GROUP BY 2
1001 ) pos ON pos.org_id = p.org_id LEFT JOIN (
1002 SELECT count(1) AS cnt
1003 ,d.organization_id AS org_id
1004 FROM pim_employee d
1005 WHERE d.organization_id IN (
1006 SELECT org_id
1007 FROM param
1008 )
1009 GROUP BY 2
1010 ) emp ON emp.org_id = p.org_id LEFT JOIN (
1011 SELECT count(1) AS cnt
1012 ,d.org_id AS org_id
1013 FROM sr_res_group d
1014 WHERE d.org_id IN (
1015 SELECT org_id
1016 FROM param
1017 )
1018 GROUP BY 2
1019 ) res ON res.org_id = p.org_id LEFT JOIN (
1020 SELECT count(1) AS cnt
1021 ,d.org_id AS org_id
1022 FROM sr_service d
1023 WHERE d.org_id IN (
1024 SELECT org_id
1025 FROM param
1026 )
1027 GROUP BY 2
1028 ) ser ON ser.org_id = p.org_id LEFT JOIN (
1029 SELECT count(1) AS cnt
1030 ,d.clinic_id AS org_id
1031 FROM mc_case d
1032 WHERE d.clinic_id IN (
1033 SELECT org_id
1034 FROM param
1035 )
1036 GROUP BY 2
1037 ) cs ON cs.org_id = p.org_id LEFT JOIN (
1038 SELECT count(1) AS cnt
1039 ,d.org_id AS org_id
1040 FROM sr_srv_rendered d
1041 WHERE d.org_id IN (
1042 SELECT org_id
1043 FROM param
1044 )
1045 GROUP BY 2
1046 ) srv ON srv.org_id = p.org_id LEFT JOIN (
1047 SELECT count(1) AS cnt
1048 ,d.clinic_id AS org_id
1049 FROM pci_patient_reg d
1050 WHERE d.clinic_id IN (
1051 SELECT org_id
1052 FROM param
1053 )
1054 GROUP BY 2
1055 ) reg ON reg.org_id = p.org_id ORDER BY 2 ASC
1056 );
1057DELETE
1058FROM sec_user_role_party
1059WHERE party_id IN (
1060 SELECT t.old_org_id
1061 FROM tmp_union_org t
1062 );
1063DELETE from sec_user_party
1064WHERE party_id IN (
1065 SELECT t.old_org_id
1066 FROM tmp_union_org t
1067 );
1068DELETE
1069FROM pim_party_role_to_party
1070WHERE party_id IN (
1071 SELECT t.old_org_id
1072 FROM tmp_union_org t where is_delete
1073 );
1074DELETE
1075FROM pim_party
1076WHERE id IN (
1077 SELECT t.old_org_id
1078 FROM tmp_union_org t where is_delete
1079 );
1080
1081UPDATE pim_organization o
1082SET close_dt = CURRENT_DATE
1083FROM tmp_union_org f
1084WHERE f.old_org_id = o.id
1085AND not is_delete
1086;
1087
1088DELETE
1089FROM tmp_union_org;
1090EXCEPTION WHEN unique_violation THEN
1091errCode:=SQLSTATE;
1092 errText:=substr(SQLERRM,1,500);
1093 insert into log_table values (errCode,errText,'all errors');
1094END;
1095
1096$BODY$
1097 LANGUAGE 'plpgsql' VOLATILE COST 100
1098;