CREATE OR REPLACE PROCEDURE SP_JCBB_ZYJX_YXB(as_KSRQ00 in char,  --开始日期
                                           as_JSRQ00 in char,   --结束日期
                                           as_id0000 out NUMBER
                                           )
AS
LS_XY NUMBER(20);
LS_CHENGY NUMBER(20);
LS_CAOY NUMBER(20);
LS_YNZJ NUMBER(20);
LS_KL NUMBER(20);
LS_ZYYP NUMBER(20);
LS_ZYYPRCS NUMBER(20);
LS_ZYFYWLF NUMBER(20);
LS_JZZRC NUMBER(20);
LS_CFZSL NUMBER(20);
LS_ZYCYZRCS NUMBER(20);--出院患者总人次数（出院人数）
LS_ZYZYYPRCS NUMBER(20);--4出院患者中应用中药饮片人次数
LS_ZYZYFYWRCS NUMBER(20);--出院患者中使用中医非药物疗法人次数
LS_ZYZYZZLRS NUMBER(20);--7中医为主治疗人数
LS_ZYZYSSZRCS NUMBER(20);--9住院手术患者总人次数
LS_ZYSSHZSSQYYZYZL NUMBER(20);--9住院手术患者围手术期应用中医治疗人次数
LS_xm1_1 varchar2(20);--1.门诊中药处方比例▲
LS_xm1_2 NUMBER(20);--1.门诊中药处方数
LS_xm2_1 varchar2(20);
LS_xm2_2 NUMBER(20);
LS_xm3_1 varchar2(20);
LS_xm3_2 NUMBER(20);
LS_XM4_1 varchar2(20);
LS_XM5_1 varchar2(20);
LS_XM6_1 varchar2(20);
LS_XM7_1 varchar2(20);
LS_XM9_1 varchar2(20);
LS_XM11_1 varchar2(20);
LS_SSBFZ varchar2(20);
LS_JZZRC1 NUMBER(20);
LS_XM7_1_1 varchar2(20);
LS_XM7_1_2 varchar2(20);
LS_XM7_1_3 varchar2(20);
LS_MZJYL varchar2(20);
LS_MZywzrc varchar2(20);
LS_MZJBYWZRC varchar2(20);
ls_zyywzrc varchar2(20);
ls_ZYJBYWZRC varchar2(20);
LS_ZYJYL varchar2(20);
LS_JBYWJE varchar2(20);
LS_QBYWJE varchar2(20);
LS_YWJEBL varchar2(20);
LS_JBYWPZS varchar2(20);
LS_YWPZS varchar2(20);
LS_JBYWPZBL varchar2(20);
LS_ZDJKYWJE varchar2(20);
LS_YWJE varchar2(20);
LS_ZDJKYWJEBL varchar2(20);
LS_47ZBSL varchar2(20);
LS_47SL varchar2(20);
LS_47ZBBL varchar2(20);
begin
select sq_jcbb_id0000.nextval into as_id0000 from dual;
if as_KSRQ00='20991231'
then return;
end if;

--西药处方数
select sum(cs0000) INTO LS_XY from (
select srrq00,brid00,GHKS00,1 cs0000 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 not in ('568','616','61','68','186','1000','506','973','315')--剔除部分挂号科室
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='诊疗项目带出处方')--剔除诊疗项目带出处方
group by srrq00,brid00,GHKS00);--一天一患一科

--成药处方数
select count(distinct(a.cflsh0)) INTO LS_CHENGY 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 not in ('568','616','61','68','186','1000','506','973','315')
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='诊疗项目带出处方');

--草药处方数（含颗粒）
select sum(cs0002) INTO LS_CAOY from (
select a.cflsh0,a.zyts00,sum(ceil(a.zyts00/7)) cs0002 from yf_mzcf00 a where a.srrq00 between as_KSRQ00 and as_JSRQ00--每7贴算一个处方--放弃了一天一患一科算1次
and a.ypdlbh='2'  AND a.bccfh0 IS NULL AND a.cfzt00 IN ('1', '2')
and a.ghks00 not in ('568','616','61','68','186','1000','506','973','315')
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='诊疗项目带出处方') group by a.cflsh0,a.zyts00);



 --院内制剂数
select count(distinct(a.cflsh0)) INTO LS_YNZJ 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 not in ('568','616','61','68','186','1000','506','973','315')
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.ykbmbh = '166' and k.ylbh00 in ('14', '36'));



-- 颗粒处方数  257138
select sum(cs0002) INTO LS_KL from (
select a.cflsh0,a.zyts00,sum(ceil(a.zyts00/7)) cs0002 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 not in ('568','616','61','68','186','1000','506','973','315')
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.ykbmbh+0 in ('1010','787'))
group by a.cflsh0,a.zyts00);


--中药饮片 指标2分子
select sum(cs0002) INTO LS_ZYYP from (
select a.cflsh0,a.zyts00,sum(ceil(a.zyts00/7)) cs0002 from yf_mzcf00 a where a.srrq00 between as_KSRQ00 and as_JSRQ00--每7贴算一个处方--放弃了一天一患一科算1次
and a.ypdlbh='2'  AND a.bccfh0 IS NULL AND a.cfzt00 IN ('1', '2')--有效的中药饮片处方
and a.ghks00 not in ('568','616','61','68','186','1000','506','973','315')
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.ykbmbh+0 in ('13'))--剔除颗粒
group by a.cflsh0,a.zyts00);

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





--门诊总人次数
select sum(cs0000) INTO LS_JZZRC  from (
select b.jzrq00,b.brid00,b.JZKS00,1 cs0000--一天一患一科算一次
from sf_brxxb0 b,ys_brzdxx c
where b.ghid00=c.ghid00  and b.ghrq00  between as_KSRQ00 and as_JSRQ00
and b.ghks00 not in (select bmbh00 from bm_bmbm00 where bmmc00 like'%急诊%')
and b.ghks00 not in (select bmbh00 from bm_bmbm00 where bmmc00 like'%护理%')
and b.ghlb00 not in (select lbbh00 from bm_ghlbb0 where lbmc00 like'%急诊%')
and not exists (select 1 from bm_ygbm00 h where h.ygbh00=b.jzys00 and h.bmbh00  in('14')) --去掉信息科管理员挂号
and b.ghks00 not in (select bmbh00 from bm_bmbm00 where bmmc00 in
('东二环放射科','影像科','体检中心放射科','检验科','病理科','超声科','心电图室','东二环检验科','东二环超声科','体检中心超声科','病区手术室','门诊换药室','体检中心胃镜室','体检中心影像科','微信公众号','影像咨询门诊','超声门诊','体检中心检验科','肺功能室'))--还要剔除互联网医院
and b.thrq00 is null--没有退号
and b.ghlb00>0--去除预挂号
and c.zdmc00 not in ('健康查体')--剔除诊断是健康查体的
group by b.jzrq00,b.brid00,b.JZKS00);

 --就诊人次，剔除未开处方病人
 select sum(cs0000) INTO LS_JZZRC1  from (
select b.jzrq00,b.brid00,b.JZKS00,1 cs0000
from sf_brxxb0 b,ys_brzdxx c
where b.ghid00=c.ghid00  and b.ghrq00 between as_KSRQ00 and as_JSRQ00
and b.ghks00 not in (select bmbh00 from bm_bmbm00 where bmmc00 like'%急诊%')
and b.ghlb00 not in (select lbbh00 from bm_ghlbb0 where lbmc00 like'%急诊%')
and b.thrq00 is null
and b.ghlb00>0
and c.zdmc00 not in ('健康查体')
and exists (select 1 from yf_mzcf00 h where h.ghid00=b.ghid00)
group by b.jzrq00,b.brid00,b.JZKS00);


--中药饮片 草药+颗粒人次数   253136
select sum(cs0000) INTO LS_ZYYPRCS  from

(select  b.jzrq00,b.brid00,b.JZKS00,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 b.ghks00 not in (select bmbh00 from bm_bmbm00 where bmmc00 like'%急诊%')
and b.ghks00 not in (select bmbh00 from bm_bmbm00 where bmmc00 like'%护理%')
and b.ghlb00 not in (select lbbh00 from bm_ghlbb0 where lbmc00 like'%急诊%')
and not exists (select 1 from bm_ygbm00 h where h.ygbh00=b.jzys00 and h.bmbh00  in('14')) --去掉信息科管理员挂号
and b.ghks00 not in (select bmbh00 from bm_bmbm00 where bmmc00 in
('东二环放射科','影像科','体检中心放射科','检验科','病理科','超声科','心电图室','东二环检验科','东二环超声科','体检中心超声科','病区手术室','门诊换药室','体检中心胃镜室','体检中心影像科','微信公众号','影像咨询门诊','超声门诊','体检中心检验科','肺功能室'))
and c.zdmc00 not in ('健康查体')
and b.thrq00 is null
and b.ghlb00>0
group by b.jzrq00,b.brid00,b.JZKS00);



--住院


select
       count(distinct zyid00), -- as "出院患者总人次数（出院人数）",
       sum(sign(cyje00)),--as "4出院患者中应用中药饮片人次数",
       count(distinct DECODE(ywzrc,'1',zyid00)) --药物总人次
       ,count(distinct DECODE(jbywzrc,'1',zyid00)) --基本药物总人次
       INTO LS_ZYCYZRCS,LS_ZYZYYPRCS,/*LS_ZYZYFYWRCS,LS_ZYZYZZLRS,LS_ZYZYSSZRCS,LS_ZYSSHZSSQYYZYZL,LS_SSBFZ,*/ls_zyywzrc,ls_ZYJBYWZRC
from (select sjcyrq,zyid00,cyje00/*,zyzlfy,(cyje00+zcyje0) zyypfy,zzlfy0,sfsshz,sfssbf,qksfgr,ylqk00,decode(zzlfy0,0,0,trunc(((zyzlfy+cyje00+zcyje0)/(zzlfy0+cyje00+zcyje0+xyje00)/0.6))) zybl00,sign(zyzlfy+cyje00+zcyje0) zyzfy0*/
  ,ywzrc,jbywzrc
from (select c.sjcyrq,c.zyid00,
               sum(decode(a.xmbh00,9999999999,a.hjje00,0)) cyje00,--草药金额
               (select 1 from(select zyid00 from yf_yzypsq union all select zyid00 from yf_zycf00) where zyid00=c.zyid00 and rownum=1) ywzrc,

               (select 1 from(
                                select zyid00 from yf_yzypsq ypsq,bm_yd0000 yd1 where ypsq.ypnm00=yd1.ypnm00
                                and yd1.ypxz00='1' and (yd1.ypmc00 not like'%葡萄糖注射液%' or yd1.ypmc00 not like'%氯化钠注射液%') and ypsq.YPDLBH <>'2'
                                union all
                                select zyid00 from yf_zycf00 cf,yf_zycfmx cfmx,bm_yd0000 yd2 where cf.cflsh0=cfmx.cflsh0 and cfmx.ypnm00=yd2.ypnm00
                                and yd2.ypxz00='1' and (yd2.ypmc00 not like'%葡萄糖注射液%' or yd2.ypmc00 not like'%氯化钠注射液%') and CF.YPDLBH <>'2'
               ) where zyid00=c.zyid00 and rownum=1) jbywzrc

          from zy_fymx00 a,zy_brfy00 b,zy_brxxb0 c,bm_yysfxm d,bm_baxm00 e
         where a.djh000=b.djh000
           and b.zyid00=c.zyid00
           and b.brid00=c.brid00
           and a.xmbh00=d.sfxmid
           and d.baxmid=e.baxmid
           and c.brzt00 in ('3','4','5')
           and c.sjcyrq between as_KSRQ00 and as_JSRQ00
           group by c.sjcyrq,c.zyid00
   ));






--出院患者中医药治疗费用比例
select ROUND(DECODE(sum(ZYZL00+ZYQT00+ZCYF00+ZCYF10),0,0,sum(ZYZL00+ZYQT00+ZCYF00+ZCYF10)/sum(ZYZFY0))*100,3)||'%',sum(ZYZL00+ZYQT00+ZCYF00+ZCYF10),sum(ZYZFY0) into LS_XM7_1_1,LS_XM7_1_2,LS_XM7_1_3  from BA_SYFYB0 a,ba_brzyxx b  where a.zyid00=b.LSH000 and b.cyrq00 between as_KSRQ00 and as_JSRQ00;


SELECT SUM(JYC),SUM(ZRC),round(decode(SUM(JYC),0,0,SUM(JYC)/SUM(ZRC))*100,3)||'%' into LS_MZJBYWZRC,LS_MZywzrc,LS_MZJYL FROM (
 select 0 JYC,sum(cs0000) ZRC  from (
select b.jzrq00,b.brid00,b.JZKS00,1 cs0000
from sf_brxxb0 b,ys_brzdxx c
where b.ghid00=c.ghid00  and b.ghrq00 between as_KSRQ00 and as_JSRQ00
and b.ghks00 not in (select bmbh00 from bm_bmbm00 where bmmc00 like'%急诊%')
and b.ghks00 not in (select bmbh00 from bm_bmbm00 where bmmc00 like'%护理%')
and b.ghlb00 not in (select lbbh00 from bm_ghlbb0 where lbmc00 like'%急诊%')
and not exists (select 1 from bm_ygbm00 h where h.ygbh00=b.jzys00 and h.bmbh00  in('14'))
and b.ghks00 not in (select bmbh00 from bm_bmbm00 where bmmc00 in
('东二环放射科','影像科','体检中心放射科','检验科','病理科','超声科','心电图室','东二环检验科','东二环超声科','体检中心超声科','病区手术室','门诊换药室','体检中心胃镜室','体检中心影像科','微信公众号','影像咨询门诊','超声门诊','体检中心检验科','肺功能室'))
and b.thrq00 is null
and b.ghlb00>0
and c.zdmc00 not in ('健康查体')
and exists (select 1 from yf_mzcf00 h where h.ghid00=b.ghid00)
group by b.jzrq00,b.brid00,b.JZKS00)
UNION ALL
 select sum(cs0000) JYC,0 ZRC from (
select b.jzrq00,b.brid00,b.JZKS00,1 cs0000
from sf_brxxb0 b,ys_brzdxx c
where b.ghid00=c.ghid00  and b.ghrq00 between as_KSRQ00 and as_JSRQ00
and b.ghks00 not in (select bmbh00 from bm_bmbm00 where bmmc00 like'%急诊%')
and b.ghks00 not in (select bmbh00 from bm_bmbm00 where bmmc00 like'%护理%')
and b.ghlb00 not in (select lbbh00 from bm_ghlbb0 where lbmc00 like'%急诊%')
and not exists (select 1 from bm_ygbm00 h where h.ygbh00=b.jzys00 and h.bmbh00  in('14'))
and b.ghks00 not in (select bmbh00 from bm_bmbm00 where bmmc00 in
('东二环放射科','影像科','体检中心放射科','检验科','病理科','超声科','心电图室','东二环检验科','东二环超声科','体检中心超声科','病区手术室','门诊换药室','体检中心胃镜室','体检中心影像科','微信公众号','影像咨询门诊','超声门诊','体检中心检验科','肺功能室'))
and b.thrq00 is null
and b.ghlb00>0
and c.zdmc00 not in ('健康查体')
and exists(select 1 from yf_mzcf00 x,yf_mzcfmx y,bm_yd0000 z where x.cflsh0=y.cflsh0 and y.ypnm00=z.ypnm00 and z.ypxz00='1' and (z.ypmc00 not like'%葡萄糖注射液%' or z.ypmc00 not like'%氯化钠注射液%')
and X.YPDLBH <>'2'
and b.ghh000=x.ghh000
)
group by b.jzrq00,b.brid00,b.JZKS00)
);



--基本药物金额比

select sum(decode(YPXZ00,'1',a.hjje00,0)),sum(hjje00) INTO LS_JBYWJE,LS_QBYWJE  from (
SELECT
m.ypnm00,
sum(ROUND(M.LSDJ00*M.YPZSL0,2)) hjje00
FROM YF_MZCF00 Z,YF_MZCFMX M WHERE  Z.CFLSH0=M.CFLSH0  AND M.SFZBY0='N'
AND Z.JZRQ00 between  as_KSRQ00 and as_JSRQ00
AND CFZT00<>'3'
group by m.ypnm00
union all
SELECT
z.ypnm00,
sum(ROUND(LSDJ00*YPZSL0,2)) hjje00
FROM vw_yf_zyypck_zyjx Z
WHERE Z.JZRQ00 between  as_KSRQ00 and as_JSRQ00
group by z.ypnm00
) A,BM_YD0000 B WHERE A.YPNM00=B.ypnm00 AND b.lbbh00 in ('0','1');




 SELECT SUM(ZSL)-SUM(THZSL),SUM(JYSL)-SUM(THJYSL) into LS_YWPZS,LS_JBYWPZS FROM (
SELECT count(distinct b.yppzbh) ZSL,count(distinct decode(B.ypxz00,'1',b.yppzbh)) JYSL,0 THZSL,0 THJYSL
FROM VW_YK_YPRKCX A,bm_yd0000 b WHERE a.ypnm00=b.ypnm00
 and rklxmc like'%入库%'
 and b.lbbh00 in ('0','1')
 AND A.QRRQ00 between  as_KSRQ00 and as_JSRQ00
/*UNION ALL
 select 0,0,sum(a.sfsl00) THZSL,sum(decode(B.ypxz00,'1',a.sfsl00)) THJYSL
from VW_YK_YPCKCX A,bm_yd0000 b WHERE a.ypnm00=b.ypnm00
 AND cklxmc like'%退货%'
 and b.lbbh00 in ('0','1')
 and QRRQ00 between  as_KSRQ00 and as_JSRQ00*/);

 --重点监控
select sum(decode(tssx,1,a.hjje00,0)),sum(hjje00) INTO LS_ZDJKYWJE,LS_YWJE  from (
SELECT
m.ypnm00,
(select SIGN(count(*)) from BM_YDTSSX b where  b.ypnm00=m.ypnm00 and b.sxbh00='17') tssx,
sum(ROUND(M.LSDJ00*M.YPZSL0,2)) hjje00
FROM YF_MZCF00 Z,YF_MZCFMX M WHERE  Z.CFLSH0=M.CFLSH0  AND M.SFZBY0='N'
AND Z.JZRQ00 between  as_KSRQ00 and as_JSRQ00
AND CFZT00<>'3'
group by m.ypnm00
union all
SELECT
z.ypnm00,
(select SIGN(count(*)) from BM_YDTSSX b where  b.ypnm00=z.ypnm00 and b.sxbh00='17') tssx,
sum(ROUND(LSDJ00*YPZSL0,2)) hjje00
FROM vw_yf_zyypck_zyjx Z
WHERE Z.JZRQ00 between  as_KSRQ00 and as_JSRQ00
group by z.ypnm00
) A;

--4+7中标入库金额

 SELECT SUM(ZBSL)-SUM(TH47ZBSL),SUM(SL)-SUM(TH47SL) INTO LS_47ZBSL,LS_47SL FROM(
 SELECT SUM(ZBSL) ZBSL,SUM(SL) SL,0 TH47ZBSL,0 TH47SL FROM(
 SELECT SUM(DECODE(B.SF4700,'4+7',A.gjje00)) AS ZBSL,sum(A.gjje00) AS SL
FROM VW_YK_YPRKCX A,tj_sqyptj1 b WHERE a.ypnm00=b.ypnm00
 and rklxmc like'%入库%'
 AND A.QRRQ00 <= '20200531' AND A.QRRQ00 between  as_KSRQ00 and as_JSRQ00
 UNION ALL
  SELECT SUM(DECODE(B.SF4700,'国家第二批集采',A.gjje00,'省集采',A.gjje00)) AS ZBSL,sum(A.gjje00) AS SL
FROM VW_YK_YPRKCX A,tj_sqyptj2 b WHERE a.ypnm00=b.ypnm00
 and rklxmc like'%入库%'
 AND A.QRRQ00 >= '20200424' AND A.QRRQ00 between  as_KSRQ00 and as_JSRQ00
 UNION ALL
   SELECT SUM(DECODE(B.SF4700,'4+7续签',A.gjje00)) AS ZBSL,sum(A.gjje00) AS SL
FROM VW_YK_YPRKCX A,tj_sqyptj3 b WHERE a.ypnm00=b.ypnm00
 and rklxmc like'%入库%'
 AND A.QRRQ00 >= '20200601' AND A.QRRQ00 between  as_KSRQ00 and as_JSRQ00
  UNION ALL
   SELECT SUM(DECODE(B.SF4700,'第三批国家集采',A.gjje00)) AS ZBSL,sum(A.gjje00) AS SL
FROM VW_YK_YPRKCX A,tj_sqyptj4 b WHERE a.ypnm00=b.ypnm00
 and rklxmc like'%入库%'
 AND A.QRRQ00 >= '20201113' AND A.QRRQ00 between  as_KSRQ00 and as_JSRQ00)
 UNION ALL
  SELECT 0,0,SUM(ZBSL) TH47ZBSL,SUM(SL) TH47SL FROM(
  SELECT SUM(DECODE(B.SF4700,'4+7',A.sfsl00*A.GJDJ00)) AS ZBSL,sum(A.sfsl00*A.GJDJ00) AS SL
FROM VW_YK_YPCKCX A,tj_sqyptj1 b WHERE a.ypnm00=b.ypnm00
 and cklxmc like'%退货%'
 AND A.QRRQ00 <= '20200531' AND A.QRRQ00 between  as_KSRQ00 and as_JSRQ00
 UNION ALL
 SELECT SUM(DECODE(B.SF4700,'国家第二批集采',A.sfsl00*A.GJDJ00,'省集采',A.sfsl00*A.GJDJ00)) AS ZBSL,sum(A.sfsl00*A.GJDJ00) AS SL
FROM VW_YK_YPCKCX A,tj_sqyptj2 b WHERE a.ypnm00=b.ypnm00
 and cklxmc like'%退货%'
 AND A.QRRQ00 >=  '20200424'  AND A.QRRQ00 between  as_KSRQ00 and as_JSRQ00
 UNION ALL
 SELECT SUM(DECODE(B.SF4700,'4+7续签',A.sfsl00*A.GJDJ00)) AS ZBSL,sum(A.sfsl00*A.GJDJ00) AS SL
FROM VW_YK_YPCKCX A,tj_sqyptj3 b WHERE a.ypnm00=b.ypnm00
 and cklxmc like'%退货%'
 AND A.QRRQ00 >=  '20200601'  AND A.QRRQ00 between  as_KSRQ00 and as_JSRQ00
 UNION ALL
 SELECT SUM(DECODE(B.SF4700,'第三批国家集采',A.sfsl00*A.GJDJ00)) AS ZBSL,sum(A.sfsl00*A.GJDJ00) AS SL
FROM VW_YK_YPCKCX A,tj_sqyptj4 b WHERE a.ypnm00=b.ypnm00
 and cklxmc like'%退货%'
 AND A.QRRQ00 >=  '20201113' AND A.QRRQ00 between  as_KSRQ00 and as_JSRQ00)
);




LS_47ZBBL:=ROUND((LS_47ZBSL/LS_47SL)*100,3)||'%';
LS_ZDJKYWJEBL:=ROUND((LS_ZDJKYWJE/LS_YWJE)*100,3)||'%';
LS_JBYWPZBL:=ROUND((LS_JBYWPZS/LS_YWPZS)*100,3)||'%';
LS_CFZSL:=LS_XY+LS_CHENGY+LS_CAOY; --总处方数=西药处方数+成药处方数+中药饮片数（含颗粒）
LS_XM1_2:=LS_CAOY+LS_CHENGY;
LS_XM1_1:=ROUND((LS_XM1_2/LS_CFZSL)*100,3)||'%';
LS_XM2_2:=LS_ZYYP;
LS_XM2_1:=ROUND((LS_XM2_2/LS_CFZSL)*100,3)||'%';
LS_XM3_2:=LS_ZYYPRCS;
LS_XM3_1:=ROUND((LS_ZYYPRCS/LS_JZZRC)*100,3)||'%';
LS_XM4_1:=ROUND((LS_ZYZYYPRCS/LS_ZYCYZRCS)*100,3)||'%';
LS_ZYJYL:=ROUND((ls_ZYJBYWZRC/ls_zyywzrc)*100,3)||'%';
LS_YWJEBL:=ROUND((LS_JBYWJE/LS_QBYWJE)*100,3)||'%';



INSERT INTO lsb_jcbb_jxkh(ID,XH,ZD1,ZD2,ZD3)values(AS_ID0000,sq_jcbb_id0000.nextval,'1.门诊中药处方比例',LS_XM1_1,'%');
INSERT INTO lsb_jcbb_jxkh(ID,XH,ZD1,ZD2,ZD3)values(AS_ID0000,sq_jcbb_id0000.nextval,'门诊中药（含中药饮片、中成药和医疗机构中药制剂）处方数（不含急诊处方）',LS_XM1_2,'个');
INSERT INTO lsb_jcbb_jxkh(ID,XH,ZD1,ZD2,ZD3)values(AS_ID0000,sq_jcbb_id0000.nextval,' 门诊处方总数（不含急诊处方）',LS_CFZSL,'个');
INSERT INTO lsb_jcbb_jxkh(ID,XH,ZD1,ZD2,ZD3)values(AS_ID0000,sq_jcbb_id0000.nextval,'2.门诊散装中药饮片和小包装中药饮片处方比例▲',LS_XM2_1,'%');
INSERT INTO lsb_jcbb_jxkh(ID,XH,ZD1,ZD2,ZD3)values(AS_ID0000,sq_jcbb_id0000.nextval,'门诊散装中药饮片和小包装中药饮片处方数（不含中药配方颗粒处方）',LS_XM2_2,'个');
INSERT INTO lsb_jcbb_jxkh(ID,XH,ZD1,ZD2,ZD3)values(AS_ID0000,sq_jcbb_id0000.nextval,'门诊处方总数（不含急诊处方）',LS_CFZSL,'个');
INSERT INTO lsb_jcbb_jxkh(ID,XH,ZD1,ZD2,ZD3)values(AS_ID0000,sq_jcbb_id0000.nextval,'3.门诊患者中药饮片使用率▲',LS_XM3_1,'%');
INSERT INTO lsb_jcbb_jxkh(ID,XH,ZD1,ZD2,ZD3)values(AS_ID0000,sq_jcbb_id0000.nextval,'  门诊患者中应用中药饮片人次数',LS_XM3_2,'人次');
INSERT INTO lsb_jcbb_jxkh(ID,XH,ZD1,ZD2,ZD3)values(AS_ID0000,sq_jcbb_id0000.nextval,'  门诊总人次数（不含急诊处方）',LS_JZZRC,'人次');
INSERT INTO lsb_jcbb_jxkh(ID,XH,ZD1,ZD2,ZD3)values(AS_ID0000,sq_jcbb_id0000.nextval,'4.出院患者中药饮片使用率▲',LS_XM4_1,'%');
INSERT INTO lsb_jcbb_jxkh(ID,XH,ZD1,ZD2,ZD3)values(AS_ID0000,sq_jcbb_id0000.nextval,'  出院患者中应用中药饮片人次数',LS_ZYZYYPRCS,'人次');
INSERT INTO lsb_jcbb_jxkh(ID,XH,ZD1,ZD2,ZD3)values(AS_ID0000,sq_jcbb_id0000.nextval,'  出院患者总人次数（出院人数）',LS_ZYCYZRCS,'人次');
INSERT INTO lsb_jcbb_jxkh(ID,XH,ZD1,ZD2,ZD3)values(AS_ID0000,sq_jcbb_id0000.nextval,'7.1.出院患者的中医药治疗费用比例',LS_XM7_1_1,'%');
INSERT INTO lsb_jcbb_jxkh(ID,XH,ZD1,ZD2,ZD3)values(AS_ID0000,sq_jcbb_id0000.nextval,'中医药治疗费用',LS_XM7_1_2,'元');
INSERT INTO lsb_jcbb_jxkh(ID,XH,ZD1,ZD2,ZD3)values(AS_ID0000,sq_jcbb_id0000.nextval,'住院治疗总费用',LS_XM7_1_3,'元');
INSERT INTO lsb_jcbb_jxkh(ID,XH,ZD1,ZD2,ZD3)values(AS_ID0000,sq_jcbb_id0000.nextval,'21.门诊基本药物使用率',LS_MZJYL,'%');
INSERT INTO lsb_jcbb_jxkh(ID,XH,ZD1,ZD2,ZD3)values(AS_ID0000,sq_jcbb_id0000.nextval,'门诊基本药物使用人次',LS_MZJBYWZRC,'人次');
INSERT INTO lsb_jcbb_jxkh(ID,XH,ZD1,ZD2,ZD3)values(AS_ID0000,sq_jcbb_id0000.nextval,'同期门诊诊疗总人次数',LS_MZywzrc,'人次');
INSERT INTO lsb_jcbb_jxkh(ID,XH,ZD1,ZD2,ZD3)values(AS_ID0000,sq_jcbb_id0000.nextval,'22.住院基本药物使用率',LS_ZYJYL,'%');
INSERT INTO lsb_jcbb_jxkh(ID,XH,ZD1,ZD2,ZD3)values(AS_ID0000,sq_jcbb_id0000.nextval,'住院基本药物使用人次',ls_ZYJBYWZRC,'人次');
INSERT INTO lsb_jcbb_jxkh(ID,XH,ZD1,ZD2,ZD3)values(AS_ID0000,sq_jcbb_id0000.nextval,'住院药物总人次',ls_zyywzrc,'人次');
INSERT INTO lsb_jcbb_jxkh(ID,XH,ZD1,ZD2,ZD3)values(AS_ID0000,sq_jcbb_id0000.nextval,'23.1.国家基本药物配备使用金额比例',LS_JBYWPZBL,'%');
INSERT INTO lsb_jcbb_jxkh(ID,XH,ZD1,ZD2,ZD3)values(AS_ID0000,sq_jcbb_id0000.nextval,'23.1.1.医院采购基本药物品种数（不含中药饮片）',LS_JBYWPZS,'数量');
INSERT INTO lsb_jcbb_jxkh(ID,XH,ZD1,ZD2,ZD3)values(AS_ID0000,sq_jcbb_id0000.nextval,'23.1.2.医院同期采购药物品种总数（不含中药饮片）',LS_YWPZS,'数量');
INSERT INTO lsb_jcbb_jxkh(ID,XH,ZD1,ZD2,ZD3)values(AS_ID0000,sq_jcbb_id0000.nextval,'23.2.国家基本药物配备使用金额比例',LS_YWJEBL,'%');
INSERT INTO lsb_jcbb_jxkh(ID,XH,ZD1,ZD2,ZD3)values(AS_ID0000,sq_jcbb_id0000.nextval,'23.2.1医院配备使用基本药物金额',LS_JBYWJE,'元');
INSERT INTO lsb_jcbb_jxkh(ID,XH,ZD1,ZD2,ZD3)values(AS_ID0000,sq_jcbb_id0000.nextval,'23.2.2医院同期全部药品配备使用总金额',LS_QBYWJE,'元');
INSERT INTO lsb_jcbb_jxkh(ID,XH,ZD1,ZD2,ZD3)values(AS_ID0000,sq_jcbb_id0000.nextval,'24.国家组织药品集中采购中标药品使用比例',LS_47ZBBL,'%');
INSERT INTO lsb_jcbb_jxkh(ID,XH,ZD1,ZD2,ZD3)values(AS_ID0000,sq_jcbb_id0000.nextval,'24.1.中标药品用量',LS_47ZBSL,'元');
INSERT INTO lsb_jcbb_jxkh(ID,XH,ZD1,ZD2,ZD3)values(AS_ID0000,sq_jcbb_id0000.nextval,'24.2.同种药品用量',LS_47SL,'元');
INSERT INTO lsb_jcbb_jxkh(ID,XH,ZD1,ZD2,ZD3)values(AS_ID0000,sq_jcbb_id0000.nextval,'34.1.重点监控化学药品和生物制剂收入占比',LS_ZDJKYWJEBL,'%');
INSERT INTO lsb_jcbb_jxkh(ID,XH,ZD1,ZD2,ZD3)values(AS_ID0000,sq_jcbb_id0000.nextval,'34.1.1重点监控化学药品和生物制剂收入',LS_ZDJKYWJE,'元');
INSERT INTO lsb_jcbb_jxkh(ID,XH,ZD1,ZD2,ZD3)values(AS_ID0000,sq_jcbb_id0000.nextval,'34.1.2药品总收入',LS_YWJE,'元');
commit;
end;