CREATE OR REPLACE PROCEDURE SP_YSGJSJ(as_KSRQ00 in char,  --开始日期
                                           as_JSRQ00 in char,   --结束日期
                                           as_YGBH00 in char,--员工编号
                                           as_KS0000 in char,--科室编号
                                           as_id0000 out NUMBER
                                           )
AS
-- MODIFICATION HISTORY
-- Person      Date        Comments
-- caobin      2021.11.01  create 创建国家三级公立中医医院绩效考核 MZYSSJ5-20211027-002
-- ruanbh      2023.04.24  原过程只适用省二，修改适用所有医院 MZYSSJ5-20230418-001
-- ruanbh      2023.04.24  增加门诊患者使用中医非药物疗法比例 MZYSSJ5-20230423-003
-- ruanbh      2023.06.09  增加门诊患者散装中药饮片和小包装中药饮片处方比例   MZYSSJ5-20230608-003
-- ruanbh      2023.06.28  过滤医技系统、中医非药物疗法医生取decode(KDYSXM,LRXM00,KDYS00,ZXR000) MZYSSJ5-20230628-001
ls_KDKS00    number(5);
begin
  select SQ_YS_MZYSGZLJL_TMPZYJX_id0000.nextval into as_id0000 from dual;
  if as_KSRQ00='20991231'
  then return;
  end if;

    if (as_YGBH00 = 0 or as_YGBH00 is null) then  --如果有传入开单医生，开单科室不要传
          ls_KDKS00 :=  as_KS0000;
      else
        ls_KDKS00 := 0;
      end if;

  insert into YS_MZYSGZLJL_TMPZYJX(id0000,KS0000,YS0000,ZYCFBL,YPCFBL,YPCFB1,YPSYBL,YPCFS0,YPCFS1,cycfs0,YNZJCFS,klcfs0,xycfs0,ZCFS00,ZYYPRCS,JZZRC0,ZYFYFC,ZYFYFL)
  select as_id0000,
  (SELECT BM.BMMC00 FROM BM_BMBM00 BM WHERE BM.BMBH00=X.KS) KS,
  (SELECT BM.ZWXM00 FROM BM_YGBM00 BM WHERE BM.YGBH00=X.YS) YS,
  decode((SUM(xycfs)+SUM(cycfs)+SUM(CAOYCFS)),0,'0'||'%',ROUND((SUM(cycfs)+SUM(CAOYCFS))/(SUM(xycfs)+SUM(cycfs)+SUM(CAOYCFS))*100,3)||'%') ZYCFBL,--门诊中药处方比例
  DECODE((SUM(xycfs)+SUM(cycfs)+SUM(CAOYCFS)),0,'0'||'%',ROUND(SUM(CAOYCFS)/(SUM(xycfs)+SUM(cycfs)+SUM(CAOYCFS))*100,3)||'%') YPCFBL,--门诊饮片处方比例
  DECODE((SUM(xycfs)+SUM(cycfs)+SUM(CAOYCFS)),0,'0'||'%',ROUND(SUM(YPCFS)/(SUM(xycfs)+SUM(cycfs)+SUM(CAOYCFS))*100,3)||'%') YPCFBL,--门诊饮片处方比例(不包含颗粒)
  decode(SUM(JZZRC),0,'0'||'%',ROUND(SUM(ZYYPRCS)/SUM(JZZRC)*100,3)||'%') YPSYBL, --门诊患者中药饮片使用率
  SUM(CAOYCFS),
  SUM(YPCFS),
  SUM(cycfs),
  SUM(YNZJCFS),
  SUM(klcfs),
  SUM(xycfs),
  (SUM(xycfs)+SUM(cycfs)+SUM(CAOYCFS)) ZCFS,
  SUM(ZYYPRCS),
  SUM(JZZRC),
  SUM(ZYFYFC),--中医非药物治疗总人次
  decode(SUM(JZZRC),0,'0'||'%',ROUND(SUM(ZYFYFC)/SUM(JZZRC)*100,3)||'%')--中医非药物治疗总人次比例
  FROM
  (
  --西药处方数
  select A.GHKS00 KS,a.ysgzh0 YS,count(distinct(a.cflsh0)) xycfs,0 cycfs,0 CAOYCFS,0 YNZJCFS,0 klcfs,0 YPCFS,0 JZZRC,0 ZYYPRCS,0 ZYFYFC
  from yf_mzcf00 a where a.srrq00 between as_KSRQ00 and as_JSRQ00
  and a.ypdlbh='0'  AND a.bccfh0 IS NULL AND a.cfzt00 IN ('1', '2') --有效的西药处方
  and (A.GHKS00 =ls_KDKS00 or ls_KDKS00=0)
  and (a.ysgzh0 =as_YGBH00 or as_YGBH00=0)
  and not exists (select 1 from sf_brxxb0 h,bm_ghlbb0 k where h.lbbh00=k.lbbh00 and h.ghid00=a.ghid00 and k.lbmc00 like '%急诊%')--剔除急诊挂号
  and not exists (select 1 from yf_mzcf00 h where h.cflsh0=a.cflsh0 and a.bz0000='诊疗项目带出处方')--剔除诊疗项目带出处方
  and exists (select 1 from BM_BMBM00 X where X.BMBH00=A.GHKS00 and X.BMXZ00='1' and instr(BMMC00,'急诊')=0 and instr(BMMC00,'120')=0)
   group by GHKS00,ysgzh0--一天一患一科
  union all
  --成药处方数
  select A.GHKS00 KS,a.ysgzh0 YS,0 xycfs,count(distinct(a.cflsh0)) cycfs,0 CAOYCFS,0 YNZJCFS,0 klcfs,0 YPCFS,0 JZZRC,0 ZYYPRCS,0 ZYFYFC
  from yf_mzcf00 a where a.srrq00 between as_KSRQ00 and as_JSRQ00
  and exists(select 1 from yf_mzcfmx x where x.cflsh0=a.cflsh0 and x.ypdlbh='1')
  AND a.bccfh0 IS NULL AND a.cfzt00 IN ('1', '2')--有效的成药处方
  and (A.GHKS00 =ls_KDKS00 or ls_KDKS00=0)
  and (a.ysgzh0 =as_YGBH00 or as_YGBH00=0)
  and not exists (select 1 from sf_brxxb0 h,bm_ghlbb0 k where h.lbbh00=k.lbbh00 and h.ghid00=a.ghid00 and k.lbmc00 like '%急诊%')
  and not exists (select 1 from yf_mzcf00 h where h.cflsh0=a.cflsh0 and a.bz0000='诊疗项目带出处方')
  and exists (select 1 from BM_BMBM00 X where X.BMBH00=A.GHKS00 and X.BMXZ00='1' and instr(BMMC00,'急诊')=0 and instr(BMMC00,'120')=0)
  group by A.GHKS00,a.ysgzh0
  union all
  --草药处方数（含颗粒）
  select A.GHKS00 KS,ysgzh0 YS,0 xycfs,0 cycfs,count(distinct a.cflsh0) CAOYCFS,0 YNZJCFS,0 klcfs,0 YPCFS,0 JZZRC,0 ZYYPRCS,0 ZYFYFC
  from yf_mzcf00 a where a.srrq00 between as_KSRQ00 and as_JSRQ00
  and a.ypdlbh='2'  AND a.bccfh0 IS NULL AND a.cfzt00 IN ('1', '2')
  and (A.GHKS00 =ls_KDKS00 or ls_KDKS00=0)
  and (a.ysgzh0 =as_YGBH00 or as_YGBH00=0)
  and not exists (select 1 from sf_brxxb0 h,bm_ghlbb0 k where h.lbbh00=k.lbbh00 and h.ghid00=a.ghid00 and k.lbmc00 like '%急诊%')
  and not exists (select 1 from yf_mzcf00 h where h.cflsh0=a.cflsh0 and a.bz0000='诊疗项目带出处方')
  and exists (select 1 from BM_BMBM00 X where X.BMBH00=A.GHKS00 and X.BMXZ00='1' and instr(BMMC00,'急诊')=0 and instr(BMMC00,'120')=0)
  group by A.GHKS00,a.ysgzh0
  union all
   --院内制剂数
  select A.GHKS00 KS,a.ysgzh0 YS,0 xycfs,0 cycfs,0 CAOYCFS,count(distinct(a.cflsh0)) YNZJCFS,0 klcfs,0 YPCFS,0 JZZRC,0 ZYYPRCS,0 ZYFYFC
  from yf_mzcf00 a where a.srrq00 between as_KSRQ00 and as_JSRQ00
  and a.ypdlbh='1'  AND a.bccfh0 IS NULL AND a.cfzt00 IN ('1', '2')
  and (A.GHKS00 =ls_KDKS00 or ls_KDKS00=0)
  and (a.ysgzh0 =as_YGBH00 or as_YGBH00=0)
  and not exists (select 1 from sf_brxxb0 h,bm_ghlbb0 k where h.lbbh00=k.lbbh00 and h.ghid00=a.ghid00 and k.lbmc00 like '%急诊%')
  and not exists (select 1 from yf_mzcf00 h where h.cflsh0=a.cflsh0 and a.bz0000='诊疗项目带出处方')
  and exists (select 1 from yf_mzcfmx h,bm_yd0000 k where h.cflsh0=a.cflsh0 and h.ypnm00=k.ypnm00 and K.ZJBZ00='1')
  and exists (select 1 from BM_BMBM00 X where X.BMBH00=A.GHKS00 and X.BMXZ00='1' and instr(BMMC00,'急诊')=0 and instr(BMMC00,'120')=0)
  group by A.GHKS00,a.ysgzh0
  union all
  -- 颗粒处方数  257138
  select A.GHKS00 KS,ysgzh0 YS,0 xycfs,0 cycfs,0 CAOYCFS,0 YNZJCFS,count(distinct a.cflsh0) klcfs,0 YPCFS,0 JZZRC,0 ZYYPRCS,0 ZYFYFC
  from yf_mzcf00 a where a.srrq00 between as_KSRQ00 and as_JSRQ00
  and a.ypdlbh='2'  AND a.bccfh0 IS NULL AND a.cfzt00 IN ('1', '2')
  and (A.GHKS00 =ls_KDKS00 or ls_KDKS00=0)
  and (a.ysgzh0 =as_YGBH00 or as_YGBH00=0)
  and not exists (select 1 from sf_brxxb0 h,bm_ghlbb0 k where h.lbbh00=k.lbbh00 and h.ghid00=a.ghid00 and k.lbmc00 like '%急诊%')
  and not exists (select 1 from yf_mzcf00 h where h.cflsh0=a.cflsh0 and a.bz0000='诊疗项目带出处方')
  and exists (select 1 from yf_mzcfmx h,bm_yd0000 k where h.cflsh0=a.cflsh0 and h.ypnm00=k.ypnm00 and K.YPCZFL in('1','2'))
  and exists (select 1 from BM_BMBM00 X where X.BMBH00=A.GHKS00 and X.BMXZ00='1' and instr(BMMC00,'急诊')=0 and instr(BMMC00,'120')=0)
  group by A.GHKS00,a.ysgzh0
  union all
  --中药饮片 指标2分子
  select A.GHKS00 KS,ysgzh0 YS,0 xycfs,0 cycfs,0 CAOYCFS,0 YNZJCFS,0 klcfs,count(distinct a.cflsh0) YPCFS,0 JZZRC,0 ZYYPRCS,0 ZYFYFC
  from yf_mzcf00 a where a.srrq00 between as_KSRQ00 and as_JSRQ00
  and a.ypdlbh='2'  AND a.bccfh0 IS NULL AND a.cfzt00 IN ('1', '2')--有效的中药饮片处方
  and (A.GHKS00 =ls_KDKS00 or ls_KDKS00=0)
  and (a.ysgzh0 =as_YGBH00 or as_YGBH00=0)
  and not exists (select 1 from sf_brxxb0 h,bm_ghlbb0 k where h.lbbh00=k.lbbh00 and h.ghid00=a.ghid00 and k.lbmc00 like '%急诊%')
  and not exists (select 1 from yf_mzcf00 h where h.cflsh0=a.cflsh0 and a.bz0000='诊疗项目带出处方')
  and not exists (select 1 from yf_mzcfmx h,bm_yd0000 k where h.cflsh0=a.cflsh0 and h.ypnm00=k.ypnm00 and K.YPCZFL in('1','2'))
 and exists (select 1 from BM_BMBM00 X where X.BMBH00=A.GHKS00 and X.BMXZ00='1' and instr(BMMC00,'急诊')=0 and instr(BMMC00,'120')=0)
  group by A.GHKS00,a.ysgzh0

  ---------------------------------------走旧算法-----------------------------------------

  UNION ALL



  --门诊总人次数
  select JZKS00 KS,jzys00 YS,
  0 xycfs,0 cycfs,0 CAOYCFS,0 YNZJCFS,0 klcfs,0 YPCFS,count(1) JZZRC,0 ZYYPRCS,0 ZYFYFC
  from VW_SF_GHLSXX_MZDZBL b
  where b.czrq00  between as_KSRQ00 and as_JSRQ00
  and exists (select 1 from BM_BMBM00 X where X.BMBH00=B.JZKS00 and X.BMXZ00='1' and instr(BMMC00,'急诊')=0 and instr(BMMC00,'120')=0 and instr(BMMC00,'体检')=0)
  and b.ghlb00>0--去除预挂号
  and b.fbmc00 not like '%体检%'
  and (b.JZKS00 =ls_KDKS00 or ls_KDKS00=0)
  and (b.jzys00 =as_YGBH00 or as_YGBH00=0)
  GROUP BY JZKS00,jzys00
  UNION ALL
  --中药饮片 草药+颗粒人次数   253136
  select GHKS00 KS,ysgzh0 YS,
  0 xycfs,0 cycfs,0 CAOYCFS,0 YNZJCFS,0 klcfs,0 YPCFS,0 JZZRC,sum(cs0000) ZYYPRCS,0 ZYFYFC
   from
  (select  b.jzrq00,b.brid00,A.GHKS00,A.ysgzh0,1 cs0000
  from yf_mzcf00 a,sf_brxxb0 b,ys_brzdxx c
  where a.ghid00=b.ghid00 and b.ghid00=c.ghid00  and a.srrq00 between as_KSRQ00 and as_JSRQ00
  and a.ypdlbh='2' and cfzt00 in ('1','2') and bccfh0 is null --有开中药处方
  and exists (select 1 from BM_BMBM00 X where X.BMBH00=B.JZKS00 and X.BMXZ00='1' and instr(BMMC00,'急诊')=0 and instr(BMMC00,'120')=0)
  and b.ghlb00 not in (select lbbh00 from bm_ghlbb0 where lbmc00 like'%急诊%')
  and c.zdmc00 not in ('健康查体')
  and b.thbz00!=0
  and b.ghlb00>0
  and (B.JZKS00 =ls_KDKS00 or ls_KDKS00=0)
  and (a.ysgzh0 =as_YGBH00 or as_YGBH00=0)
  group by b.jzrq00,b.brid00,A.GHKS00,A.ysgzh0)GROUP BY GHKS00,ysgzh0
  union all
  --中医非药物疗法总人次数
  select A.KDKSBH KS,decode(KDYSXM,LRXM00,KDYS00,ZXR000) YS,0 xycfs,0 cycfs,0 CAOYCFS,0 YNZJCFS,0 klcfs,0 YPCFS,0 JZZRC,0 ZYYPRCS,count(distinct zyhghh) ZYFYFC
 from YJ_YW0000 A
    where A.KDRQ00 between as_KSRQ00 and as_JSRQ00
    and exists(select 1 from YJ_YWJJ00 X,BM_YYSFXM Y where X.YJDJH0=A.YJDJH0 and X.SFXMID=Y.SFXMID and y.bh0000 between '041000000100' and '048000000200')--中医综合治疗项目
    and A.XMZT00 in ('2','3','4')  AND (KDYS00=as_YGBH00 or as_YGBH00=0)
    and (KDKSBH=ls_KDKS00 or ls_KDKS00=0)
    and A.MZZYBZ='0'
    and exists (select 1 from BM_BMBM00 X where X.BMBH00=A.KDKSBH and X.BMXZ00='1' and instr(BMMC00,'急诊')=0 and instr(BMMC00,'120')=0)
    group by KDKSBH,decode(KDYSXM,LRXM00,KDYS00,ZXR000)

  ) X group by ks,ys;
end;