create or replace procedure SP_BA_SSRCSTJ(
            J_KSRQYM    in    char,       -- 开始日期
            J_JSRQYM    in    char        -- 结束日期

                            )
as
-- MODifICATION HISTORY
-- Person      Date        Comments
-- shieq      2023.06.25    create for   BAGLTYB-20230518-001 手术人次数统计
-- czc        2023.08.04   新增过滤手术、介入治疗条件
-- czc        2023.08.07   调整统计手术等级条件保持与SP_BA_SSXGBB一致

cursor CUR_KS is select distinct CYKBMC from BA_BRZYXX BA join BA_BASSSB  SS on BA.LSH000=SS.LSH000  where CYRQ00 >=J_KSRQYM and CYRQ00<=J_JSRQYM;
Row_KS CUR_KS%rowtype;

 yijCount number(10);--一级手术
 erjCount number(10);--二级手术
 sajCount number(10);--三级手术
 sijCount number(10);--四级手术
 jzCount number(10);--急诊手术
 zqCount number(10);--择期手术
 xqCount number(10);--限期手术
 rjCount number(10);--日间手术

 mCount number(10);--总计
 jsCount number(4);--计数
 begin

     delete from BA_YLZLZB;
     commit;
     jsCount:=1;
    open CUR_KS;
      loop
       Fetch CUR_KS into Row_KS;
         exit when CUR_KS%notFound;

         select count(distinct BA.LSH000 ) into yijCount from BA_BRZYXX BA join BA_BASSSB SS on BA.LSH000=SS.LSH000 join XT_CM3000 xt on XT.CM3000 =SS.SSM000
         where CYKBMC=Row_KS.CYKBMC and CYRQ00 >=J_KSRQYM and CYRQ00<=J_JSRQYM 
         and (SS.LB0000='手术' or SS.LB0000='介入治疗') and XT.SSDJBH='3';

         select count(distinct BA.LSH000 ) into erjCount from BA_BRZYXX BA join BA_BASSSB SS on BA.LSH000=SS.LSH000 join XT_CM3000 xt on XT.CM3000 =SS.SSM000
         where CYKBMC=Row_KS.CYKBMC and CYRQ00 >=J_KSRQYM and CYRQ00<=J_JSRQYM
         and (SS.LB0000='手术' or SS.LB0000='介入治疗') and XT.SSDJBH='2';

         select count(distinct BA.LSH000 ) into sajCount from BA_BRZYXX BA join BA_BASSSB SS on BA.LSH000=SS.LSH000 join XT_CM3000 xt on XT.CM3000 =SS.SSM000
         where CYKBMC=Row_KS.CYKBMC and CYRQ00 >=J_KSRQYM and CYRQ00<=J_JSRQYM
         and (SS.LB0000='手术' or SS.LB0000='介入治疗') and XT.SSDJBH='1';

         select count(distinct BA.LSH000 ) into sijCount from BA_BRZYXX BA join BA_BASSSB SS on BA.LSH000=SS.LSH000 join XT_CM3000 xt on XT.CM3000 =SS.SSM000
         where CYKBMC=Row_KS.CYKBMC and CYRQ00 >=J_KSRQYM and CYRQ00<=J_JSRQYM
         and (SS.LB0000='手术' or SS.LB0000='介入治疗') and XT.SSDJBH='0';

         jzCount:=0;
         zqCount:=0;
         xqCount:=0;
         rjCount:=0;

        --mCount :=yijCount+erjCount+sajCount+sijCount+jzCount+zqCount+xqCount+rjCount;
         select count(distinct BA.LSH000 ) into mCount from BA_BRZYXX BA join BA_BASSSB SS on BA.LSH000=SS.LSH000
         where   CYKBMC=Row_KS.CYKBMC and CYRQ00 >=J_KSRQYM and CYRQ00<=J_JSRQYM and (SS.LB0000='手术' or SS.LB0000='介入治疗');



         insert into BA_YLZLZB(F1,F2,F3,F4,F5,F6,F7,F8,F9,F10,F11,F12,F13,F14,F21)
           select  Row_KS.CYKBMC ,
           yijCount,case when sum(mCount)=0 then '0' else to_char(round(yijCount*100/mCount,2),'Fm9999990.00') end yijl,
           erjCount,case when sum(mCount)=0 then '0' else to_char(round(erjCount*100/mCount,2),'Fm9999990.00') end erjl,
           sajCount,case when sum(mCount)=0 then '0' else to_char(round(sajCount*100/mCount,2),'Fm9999990.00') end sajl,
           sijCount,case when sum(mCount)=0 then '0' else to_char(round(sijCount*100/mCount,2),'Fm9999990.00') end sijl,
           jzCount,zqCount,xqCount,rjCount,mCount,jsCount
           from dual;

         jsCount:=jsCount+1;

     end loop;
     close CUR_KS;
     commit;
     insert into BA_YLZLZB(F1,F2,F3,F4,F5,F6,F7,F8,F9,F10,F11,F12,F13,F14,F21)
     select '合计',
     sum(F2),case when sum(F14)=0 then '0' else to_char(round(sum(F2)*100/sum(F14),2),'Fm9999990.00') end yijl,
     sum(F4),case when sum(F14)=0 then '0' else to_char(round(sum(F4)*100/sum(F14),2),'Fm9999990.00') end erjl,
     sum(F6),case when sum(F14)=0 then '0' else to_char(round(sum(F6)*100/sum(F14),2),'Fm9999990.00') end sajl,
     sum(F8),case when sum(F14)=0 then '0' else to_char(round(sum(F8)*100/sum(F14),2),'Fm9999990.00') end sijl,
     sum(F10),sum(F11),sum(F12),sum(F13),sum(F14),99999
     from BA_YLZLZB;
      commit;
 end;
/