1 --创建包
2 create or replace package PAK_ExecuteAttendingDoctor is
3 type cp_results is ref cursor;
4 procedure ExecuteAttendingDoctor(start_time in varchar2,end_time in varchar2,cp_results out cp_results);
5 end PAK_ExecuteAttendingDoctor;
1 --创建包体
2 create or replace package body PAK_ExecuteAttendingDoctor is
3 procedure ExecuteAttendingDoctor(start_time in varchar2,
4 end_time in varchar2,
5 cp_results out cp_results) is
6 begin
7 open cp_results for
8 select distinct *
9 from (select t.dept_code, t.dept_name
10 from dcp_sys_user t
11 where t.dept_code > 0
12 and t.dept_code is not null
13 group by t.dept_code, t.dept_name) mm,
14 (select a.execute_dept,
15 a.attending_doctor,
16 nvl(a.fhrs, 0) fhrs,
17 nvl(b.nrrs, 0) nrrs,
18 nvl(c.wcrs, 0) wcrs,
19 round(nvl(b.nrrs / a.fhrs, 0), 4) nrl,
20 round(nvl(c.wcrs / b.nrrs, 0), 4) wcl,
21 nvl(d.xdyzzs, 0) xdyzzs,
22 nvl(xdlcljyzs, 0) xdlcljyzs,
23 round(nvl(xdlcljyzs / d.xdyzzs, 0), 4) xdl
24 from --按医生统计符合人数
25 (select execute_dept, attending_doctor, count(patient_no) fhrs
26 from lcp_patient_visit v
27 where v.conform_master_id > 0
28 and v.admission_date >=
29 to_date(start_time, 'yyyy-mm-dd')
30 and v.admission_date <=
31 to_date(end_time, 'yyyy-mm-dd hh24:mi:ss')
32 group by v.execute_dept, v.attending_doctor) a,
33 ----纳入人数---
34 (select execute_dept,
35 v1.attending_doctor,
36 count(patient_no) nrrs
37 from lcp_patient_visit v1
38 where v1.cp_master_id > 0
39 and v1.admission_date >=
40 to_date(start_time, 'yyyy-mm-dd')
41 and v1.admission_date <=
42 to_date(end_time, 'yyyy-mm-dd hh24:mi:ss')
43 group by v1.execute_dept, v1.attending_doctor) b,
44 ----完成人数-----
45 (select execute_dept,
46 v2.attending_doctor,
47 count(patient_no) wcrs
48 from lcp_patient_visit v2
49 where v2.cp_master_id > 0
50 and v2.cp_state = 11
51 and v2.admission_date >=
52 to_date(start_time, 'yyyy-mm-dd')
53 and v2.admission_date <=
54 to_date(end_time, 'yyyy-mm-dd hh24:mi:ss')
55 group by v2.execute_dept, v2.attending_doctor) c,
56 ----下达医嘱总数-----
57 (select count(*) xdyzzs, v.execute_dept, v.attending_doctor
58 from lcp_patient_log_order t, lcp_patient_visit v
59 where t.patient_no in
60 (select t.patient_no
61 from lcp_patient_visit t
62 where t.cp_state in (1, 11, 21)
63 and t.admission_date >=
64 to_date(start_time, 'yyyy-mm-dd')
65 and t.admission_date <=
66 to_date(end_time, 'yyyy-mm-dd hh24:mi:ss'))
67 and t.patient_no = v.patient_no
68 group by v.execute_dept, v.attending_doctor) d,
69 ----下达临床路径医嘱数-----
70 (select count(*) xdlcljyzs, v.execute_dept, v.attending_doctor
71 from lcp_patient_log_order t, lcp_patient_visit v
72 where t.patient_no in
73 (select t.patient_no
74 from lcp_patient_visit t
75 where t.cp_state in (1, 11, 21)
76 and t.admission_date >=
77 to_date(start_time, 'yyyy-mm-dd')
78 and t.admission_date <=
79 to_date(end_time, 'yyyy-mm-dd hh24:mi:ss'))
80 and t.cp_node_order_item_id > 0
81 and t.patient_no = v.patient_no
82 group by v.execute_dept, v.attending_doctor) e
83
84 where a.execute_dept = b.execute_dept(+)
85 and a.attending_doctor = b.attending_doctor(+)
86 and a.execute_dept = c.execute_dept(+)
87 and a.attending_doctor = c.attending_doctor(+)
88 and a.execute_dept = d.execute_dept(+)
89 and a.attending_doctor = d.attending_doctor(+)
90 and a.execute_dept = e.execute_dept(+)
91 and a.attending_doctor = e.attending_doctor(+)) aa
92 where mm.dept_name = aa.execute_dept
93 order by aa.execute_dept, nrrs desc;
94 end ExecuteAttendingDoctor;
95
96 end PAK_ExecuteAttendingDoctor;
浙公网安备 33010602011771号