CREATE OR REPLACE PROCEDURE SP_YS_YSGZLJL_SS ( as_KDYS00 in char, --开单医生 as_KDKS00 in char, --开单科室 as_SRKSRQ in char, --输入开始日期 as_SRJSRQ in char, --输入结束时间 ad_ID0000 out number --传出查询ID ) as -- MODIFICATION HISTORY -- Person Date Comments -- yangy 2012.12.07 create -- yangy 2012.12.11 根据省人民医院的算法统计中药使用率 -- yangy 2013.01.25 增加抗菌药物使用率统计 -- yangy 2013.04.12 中药使用率统计把挂号数改为处方数 -- huanglw 2018.06.04 过程加上SP_TRANSLOG MZYS9-20180531-002 -- ruanbh 2019.09.09 医生统计增加一行合计 MZYSSJ5-20190703-002 -- ruanbh 2020.12.17 增加中医治疗率、中医综合治疗项目使用人次、中医综合治疗项目使用率、中成药处方数 MZYSSJ5-20201029-001 ls_JKDH00 char(10); ls_KDKS00 number(5); --开单科室 ls_KDKSTP number(5); --开单科室临时 ls_KDYSTP number(5); --开单医生临时 ls_KJYWS0 number(5); --抗菌药物处方数 --抗菌药品使用率 --select round(sum(CFS000)/DECODE(sum(RCS000),0,1,sum(RCS000)),4)*100||'%' as BL0000,JZYS00 CURSOR CUR_YS_KJYPSYL IS select count(distinct(A.GHH000)) CFS000,a.YSGZH0 JZYS00,A.GHKS00 from YF_MZCF00 A,YF_MZCFMX B,SF_BRXXB0 C,BM_YD0000 D where A.CFLSH0=B.CFLSH0 and A.GHH000=C.GHH000 and A.CFZT00 in ('1','2') and A.BCCFH0 is null and B.YPNM00=D.YPNM00 and D.KSSJB0>0 and C.GHRQ00 between as_SRKSRQ and as_SRJSRQ AND (C.JZYS00=as_KDYS00 or as_KDYS00=0) and (A.GHKS00=as_KDKS00 or as_KDKS00=0) and not exists (select E.CFLSH0 from YF_MZCFMX E where E.CFLSH0=A.CFLSH0 and E.YPNM00=D.YPNM00 and E.YPYFMC in ('外敷','滴眼','滴鼻','滴耳','外洗','塞阴','漱口','外塞','膀胱洗涤', '灌肠','灌肠*','外用（中成药）','外用','外用(西药)','外贴（中成药）','坐浴','外贴','塞肛','直肠给药')) group by A.GHKS00,A.YSGZH0; v_LogStr varchar(500); begin v_LogStr:='SP_YS_YSGZLJL_SS(' ||as_KDYS00||',' ||as_KDKS00||',' ||as_SRKSRQ||',' ||as_SRJSRQ||',' ||to_char(ad_ID0000)||')'; SP_TRANSLOG(sysdate,'SP_YS_YSGZLJL_SS',0,0,v_LogStr); if (as_KDYS00 = 0 or as_KDYS00 is null) then --如果有传入开单医生，开单科室不要传 ls_KDKS00 := as_KDKS00; else ls_KDKS00 := 0; end if; select SQ_YS_YSGZLJL_TMP.nextval into ls_JKDH00 from dual; --中药饮片使用率 insert into YS_MZYSGZLJL_TMP (ID0000,KS0000,YS0000,BZ0000,GHZS00,BYZJS0,ZYCFS0,ZCFS00, GZLJL0,ZYJS00,KLCFS0,GZLJL2,ZYZLL0,ZYZHZL,ZYZHL0,ZCYS00) select ls_JKDH00,GHKS00,JZYS00,'1',SUM(ZGHS00) GHZS00, sum(CFS003) YNZJS0, sum(CFS001) ZYYPS0, sum(ZCFS00) ZCFS00, substr ((round((sum(CFS001)+sum(CFS003)) / decode(SUM(ZCFS00) ,0,1,SUM(ZCFS00) ),4) * 100), 1, 5) || '%' as GZLJL0,sum(zyts00) ZYTS00,sum(CFS002) KLCFS0, substr ((round((sum(YPJE00)) / decode(SUM(HJJE00) ,0,1,SUM(HJJE00) ),4) * 100), 1, 5) || '%' as GZLJL2,--药占比 round(sum(ZYZLRC)*100/decode(sum(JZRC00),0,1,sum(JZRC00)),2)||'%' ZYZLL0,--中医治疗率=中医治疗人次/就诊人次，中医治疗人次=(中药+成药+中医综合治疗项目) sum(ZYZHZL) ZYZHZL,--中医综合治疗项目使用人次 round(sum(ZYZHZL)*100/decode(sum(JZRC00),0,1,sum(JZRC00)),2)||'%' ZYZHL0,--中医综合治疗项目使用率=中医综合治疗项目使用人次/就诊人次 sum(ZCYS00) ZCYS00--中成药处方数 from ( --中草药 select sum(zyts00) zyts00,count(distinct a.cflsh0) CFS001,0 CFS002, 0 CFS003, 0 ZCFS00, YSGZH0 as JZYS00, GHKS00, 0 ZGHS00,0 YPJE00,0 HJJE00,0 ZCYS00,0 ZCYRC0,count(A.BRID00) CYRC00,0 ZYZHZL,0 JZRC00,0 ZYZLRC from YF_MZCF00 A where A.SRRQ00 between as_SRKSRQ and as_SRJSRQ and A.ypdlbh = '2' and A.bccfh0 is null --and not exists(select 1 from yf_mzcf00 where bccfh0=a.cflsh0 and ghh000=a.ghh000) and A.cfzt00 in ('1', '2') AND (YSGZH0=as_KDYS00 or as_KDYS00=0) and (GHKS00=ls_KDKS00 or ls_KDKS00=0) group by GHKS00,YSGZH0 union all --颗粒处方数 select 0 zyts00, 0 CFS001,count(distinct a.cflsh0) CFS002, 0 CFS003, 0 ZCFS00, YSGZH0 as JZYS00, GHKS00, 0 ZGHS00,0 YPJE00,0 HJJE000,0 ZCYS00,0 ZCYRC0,0 CYRC00,0 ZYZHZL,0 JZRC00,0 ZYZLRC from YF_MZCF00 A,BM_YD0000 B,YF_MZCFMX C where 1= 1 and A.CFLSH0 = C.Cflsh0 and C.YPNM00 = B.YPNM00 and A.SRRQ00 between as_SRKSRQ and as_SRJSRQ and (B.Ypczfl = '1' or B.Ypczfl = '2') and A.bccfh0 is null --and not exists(select 1 from yf_mzcf00 where bccfh0=a.cflsh0 and ghh000=a.ghh000) and A.cfzt00 in ('1', '2') AND (YSGZH0=as_KDYS00 or as_KDYS00=0) and (GHKS00=ls_KDKS00 or ls_KDKS00=0) group by GHKS00,YSGZH0 union all --本院制剂 SELECT 0 zyts00,0 cfs001,0 CFS002, COUNT(DISTINCT(a.cflsh0)) cfs003, 0 zcfs00, a.YSGZH0 as JZYS00, a.GHKS00, 0 ZGHS00,0 YPJE00,0 HJJE000,0 ZCYS00,0 ZCYRC0,0 CYRC00,0 ZYZHZL,0 JZRC00,0 ZYZLRC FROM yf_mzcf00 a, bm_ygbm00 b, bm_ghlbb0 c, bm_bmbm00 d, yf_mzcfmx e,bm_yd0000 f WHERE a.cflsh0 = e.cflsh0 and f.lbbh00 = '1' and f.ykbmbh = '166' and f.ylbh00 in ('14', '36') and f.ypnm00 = e.ypnm00 and a.ghks00 = d.bmbh00 AND a.ysgzh0 = b.ygbh00 AND b.mrghlb = c.lbbh00 AND ((b.ysflbh IN ('0', '1')) or ('9' in ('0', '1'))) AND a.srrq00 BETWEEN as_SRKSRQ and as_SRJSRQ AND a.bccfh0 IS NULL AND a.cfzt00 IN ('1', '2') AND (A.YSGZH0 = as_KDYS00 or as_KDYS00 = 0) and (A.GHKS00 = ls_KDKS00 or ls_KDKS00 = 0) group by a.GHKS00, a.YSGZH0 union all --总处方数 select 0 zyts00,0 CFS001,0 CFS002, 0 CFS003, count(distinct a.cflsh0) ZCFS00, YSGZH0 as JZYS00, GHKS00, 0 ZGHS00,0 YPJE00,0 HJJE000,0 ZCYS00,0 ZCYRC0,0 CYRC00,0 ZYZHZL,0 JZRC00,0 ZYZLRC from YF_MZCF00 A,YF_MZCFMX e where A.SRRQ00 between as_SRKSRQ and as_SRJSRQ and A.bccfh0 is null --and not exists(select 1 from yf_mzcf00 where bccfh0=a.cflsh0 and ghh000=a.ghh000) and A.cfzt00 in ('1', '2') AND (YSGZH0=as_KDYS00 or as_KDYS00=0) and e.cflsh0 = A.cflsh0 and e.ypnm00 not in(3926,5118,5119,4442,10954,43568,43570,12333,5305,3885,6140,45936,45947,44758,43565,39221,13168,11470,46610,10913,49487,49681,49683,49685,49687,37306) and (GHKS00=ls_KDKS00 or ls_KDKS00=0) group by GHKS00,YSGZH0 union all --总挂号数 select 0 zyts00,0 CFS001,0 CFS002, 0 CFS003, 0 ZCFS00, JZYS00, GHKS00, sum(GHCS00) ZGHS00,0 YPJE00,0 HJJE000,0 ZCYS00,0 ZCYRC0,0 CYRC00,0 ZYZHZL,count(S.BRID00) JZRC00,0 ZYZLRC from VW_SF_GHLSXX S where CZRQ00 between as_SRKSRQ and as_SRJSRQ and (JZYS00=as_KDYS00 or as_KDYS00=0) and (GHKS00=ls_KDKS00 or ls_KDKS00=0) group by GHKS00,JZYS00 union all --药占比 select 0 zyts00,0 CFS001,0 CFS002, 0 CFS003, 0 ZCFS00, b.KDYS00, b.KDKS00, 0 ZGHS00, sum(decode(b.XMBH00,9999999997,b.HJJE00,9999999998,b.HJJE00,9999999999,b.HJJE00,0)) YPJE00,sum(b.HJJE00) HJJE00,0 ZCYS00,0 ZCYRC0,0 CYRC00,0 ZYZHZL,0 JZRC00,0 ZYZLRC from SF_BRFY00 a,SF_FYMX00 b where a.CZRQ00 between as_SRKSRQ and as_SRJSRQ and a.DJH000=b.DJH000 and (b.KDYS00=as_KDYS00 or as_KDYS00=0) and (b.KDKS00=ls_KDKS00 or ls_KDKS00=0) group by b.KDKS00,b.KDYS00 union all --成药数 select 0 zyts00,0 CFS001,0 CFS002, 0 CFS003, 0 ZCFS00, YSGZH0 as JZYS00, GHKS00, 0 ZGHS00,0 YPJE00,0 HJJE00,count(distinct a.cflsh0) ZCYS00,count(distinct A.BRID00) ZCYRC0,0 CYRC00,0 ZYZHZL,0 JZRC00,0 ZYZLRC from YF_MZCF00 A where A.SRRQ00 between as_SRKSRQ and as_SRJSRQ and A.ypdlbh = '1' and A.bccfh0 is null and A.cfzt00 in ('1', '2') AND (YSGZH0=as_KDYS00 or as_KDYS00=0) and (GHKS00=ls_KDKS00 or ls_KDKS00=0) group by GHKS00,YSGZH0 union all --中医综合治疗 select 0 zyts00,0 CFS001,0 CFS002, 0 CFS003, 0 ZCFS00, KDYS00 as JZYS00, KDKSBH, 0 ZGHS00,0 YPJE00,0 HJJE00,0 ZCYS00,0 ZCYRC0,0 CYRC00,count(distinct BRID00) ZYZHZL,0 JZRC00,0 ZYZLRC from YJ_YW0000 A where A.KDRQ00 between as_SRKSRQ and as_SRJSRQ and exists(select 1 from YJ_YWJJ00 X,BM_YYSFXM Y where X.YJDJH0=A.YJDJH0 and X.SFXMID=Y.SFXMID and Y.BH0000||'' like '04%' and Y.BH0000||''>'041' and X.SFXMID not in ('40468','25729','25730','40465','40466','40467'))--中医综合治疗项目 and A.XMZT00 in ('2','3','4') AND (KDYS00=as_KDYS00 or as_KDYS00=0) and (KDKSBH=ls_KDKS00 or ls_KDKS00=0) group by KDKSBH,KDYS00 union all --中药+成药+中医综合治疗项目 select 0 zyts00,0 CFS001,0 CFS002, 0 CFS003, 0 ZCFS00, JZYS00, GHKS00,0 ZGHS00,0 YPJE00,0 HJJE000,0 ZCYS00,0 ZCYRC0,0 CYRC00,0 ZYZHZL,0 JZRC00,COUNT(DISTINCT S.BRID00) ZYZLRC from VW_SF_GHLSXX S where CZRQ00 between as_SRKSRQ and as_SRJSRQ and (JZYS00=as_KDYS00 or as_KDYS00=0) and (GHKS00=ls_KDKS00 or ls_KDKS00=0) AND ( EXISTS( select 1 from YJ_YW0000 A WHERE exists(select 1 from YJ_YWJJ00 X,BM_YYSFXM Y where X.YJDJH0=A.YJDJH0 and X.SFXMID=Y.SFXMID and Y.BH0000||'' like '04%' and Y.BH0000||''>'041' and X.SFXMID not in ('40468','25729','25730','40465','40466','40467'))--中医综合治疗项目 and A.XMZT00 in ('2','3','4') AND A.ZYHGHH=S.GHH000 ) or EXISTS( select 1 from YF_MZCF00 A where A.ypdlbh IN('1','2') and A.bccfh0 is null and A.cfzt00 in ('1', '2') ) ) group by GHKS00,JZYS00 ) group BY GHKS00,JZYS00; --抗菌药品使用率 open CUR_YS_KJYPSYL; loop fetch CUR_YS_KJYPSYL into ls_KJYWS0,ls_KDYSTP,ls_KDKSTP; exit WHEN CUR_YS_KJYPSYL%NOTFOUND; update YS_MZYSGZLJL_TMP set KJYWS0=ls_KJYWS0,GZLJL1=round(ls_KJYWS0/DECODE(ZCFS00,0,1,ZCFS00),4)*100||'%' where ID0000=ls_JKDH00 and KS0000=ls_KDKSTP and YS0000=ls_KDYSTP and BZ0000='1'; if SQL%NOTFOUND then insert into YS_MZYSGZLJL_TMP (ID0000,KS0000,YS0000,KJYWS0,BZ0000) values(ls_JKDH00,ls_KDKSTP,ls_KDYSTP,ls_KJYWS0,'1'); end if; end loop; --不是按医生统计时，增加一个按科室的合计 if (as_KDYS00 = 0 or as_KDYS00 is null) then insert into YS_MZYSGZLJL_TMP (ID0000,KS0000,YS0000,BZ0000,GHZS00,BYZJS0,ZYCFS0,KJYWS0,ZCFS00, GZLJL0, GZLJL1,ZYJS00,KLCFS0) select ls_JKDH00,KS0000,0,'1',sum(GHZS00),sum(BYZJS0),sum(ZYCFS0),sum(KJYWS0),sum(ZCFS00), round(((sum(BYZJS0)+sum(ZYCFS0)) / decode(SUM(ZCFS00),0,1,SUM(ZCFS00) )),4) * 100 || '%' as GZLJL0, round(nvl(sum(KJYWS0),0)/DECODE(SUM(ZCFS00),0,1,SUM(ZCFS00)),4)*100||'%' as GZLJL1,sum(ZYJS00),sum(KLCFS0) from YS_MZYSGZLJL_TMP where ID0000=ls_JKDH00 group by KS0000; else insert into YS_MZYSGZLJL_TMP (ID0000,KS0000,YS0000,BZ0000,GHZS00,BYZJS0,ZYCFS0,KJYWS0,ZCFS00, GZLJL0, GZLJL1,ZYJS00,KLCFS0) select ls_JKDH00,-1,YS0000,'1',sum(GHZS00),sum(BYZJS0),sum(ZYCFS0),sum(KJYWS0),sum(ZCFS00), round(((sum(BYZJS0)+sum(ZYCFS0)) / decode(SUM(ZCFS00),0,1,SUM(ZCFS00) )),4) * 100 || '%' as GZLJL0, round(nvl(sum(KJYWS0),0)/DECODE(SUM(ZCFS00),0,1,SUM(ZCFS00)),4)*100||'%' as GZLJL1,sum(ZYJS00),sum(KLCFS0) from YS_MZYSGZLJL_TMP where ID0000=ls_JKDH00 group by YS0000; end if; commit; ad_ID0000 := ls_JKDH00; exception when no_data_found then rollback; raise_application_error(-20001,substrb('没有找到数据！*'||sqlerrm,1,240)); when others then rollback; raise_application_error(-20002,substrb(nvl(sqlerrm, '原因不明出错！')||'！*',1,240)); end;