CREATE OR REPLACE PROCEDURE SP_SF_GHLBTJ_RBB000_YS
(
        as_KSRQ00    in    char,       --开始日期
        as_KSSJ00    in    char,       --开始时间
        as_JSRQ00    in    char,       --结束日期
        as_JSSJ00    in    char,       --结束时间
        ad_ID0000    out   number,     --统计报表对应的ID
        as_YHMSG0    out   varchar,    --存储过程提示的错误信息
        as_SYSMSG    out   varchar     --系统提示的错误信息
)
as
        ls_SFCZ00    SF_BRXXB0.SFCZ00%type;
        ls_GHLB00    SF_BRXXB0.GHLB00%type;
        ls_GHKS00    SF_BRXXB0.GHKS00%type;
        ls_BMMC00    BM_BMBM00.BMMC00%type;
        ls_GHY000    BM_YGBM00.YGBH00%type;
        ls_GHYSXM    BM_GHKSTJ.BEIZHU%type;
        ls_COUNT0    number(5);               --挂号次数
        ls_GHFZJE    SF_BRXXB0.GHFZJE%type;   --挂号费总金额
        ls_GHLBBH    char(2);
        VCounter     number(5);

        cursor CUR_SF_GHLBTJ_RBB000 is
          select SFCZ00,GHLB00,GHKS00,GHY000,sum(GHCS00),sum(GHF000)
                  from VW_SF_GHLSXX
                 where CZRQ00 >= as_KSRQ00
                   and CZRQ00 <= as_JSRQ00
                   and CZRQ00||CZSJ00 >= as_KSRQ00||nvl(as_KSSJ00,'00:00:00')
                   and CZRQ00||CZSJ00 <= as_JSRQ00||nvl(as_JSSJ00,'23:59:59')
              group by SFCZ00,GHLB00,GHKS00,GHY000;
begin
  select  SQ_BM_GHKSTJ_ID0000.nextval  into  ad_ID0000  from  dual;

  open CUR_SF_GHLBTJ_RBB000;
       loop
          fetch CUR_SF_GHLBTJ_RBB000 into  ls_SFCZ00,ls_GHLB00,ls_GHKS00,ls_GHY000,ls_COUNT0,ls_GHFZJE;
          exit when CUR_SF_GHLBTJ_RBB000%notfound;
          select bmmc00 into ls_BMMC00 from BM_BMBM00 where bmbh00=ls_GHKS00;
          select lpad(to_char(ls_GHLB00),2,'0') into ls_GHLBBH from dual;
          SELECT lpad(replace(ls_GHLBBH,'-'),2,'0') into ls_GHLBBH from dual; --把-去掉，以免出错

          if  ls_SFCZ00 = '0'  then
                update  BM_GHKSTJ  set  GHKS61 = NVL(GHKS61,0)+ls_COUNT0 ,GHKS63=NVL(GHKS63,0)+ls_COUNT0,GHKS64=nvl(GHKS64,0)+ls_GHFZJE  where  DYID00 = ls_GHKS00 and BEIZHU=to_char(ls_GHY000) and ID0000=ad_ID0000;
                if sql%notfound  then
                    insert into BM_GHKSTJ(GHRQ00,GHKS61,ID0000,GHKS63,GHKS64,DYID00,BEIZHU) values(ls_BMMC00,ls_COUNT0,ad_ID0000,ls_COUNT0,ls_GHFZJE,ls_GHKS00,to_char(ls_GHY000));
                end if;
          end if;

          if  ls_SFCZ00 = '1'  then
                update  BM_GHKSTJ  Set  GHKS62 = NVL(GHKS62,0)+ls_COUNT0 ,GHKS63=NVL(GHKS63,0)+ls_COUNT0,GHKS64=nvl(GHKS64,0)+ls_GHFZJE  where  DYID00 = ls_GHKS00  and ID0000=ad_ID0000;
                if sql%notfound  then
                    insert into BM_GHKSTJ(GHRQ00,GHKS62,ID0000,GHKS63,GHKS64,DYID00,BEIZHU) values(ls_BMMC00,ls_COUNT0,ad_ID0000,ls_COUNT0,ls_GHFZJE,ls_GHKS00,to_char(ls_GHY000));
                end if;
          end if;

         select count(1) into VCounter from BM_GHKSTJ where ID0000=ad_ID0000 and DYID00=ls_GHKS00 and BEIZHU=to_char(ls_GHY000);
         if VCounter>0 then
              sp_execute_sql('UPDATE BM_GHKSTJ SET GHKS'||ls_GHLBBH||'= NVL(GHKS'||ls_GHLBBH||',0)+'||to_char(ls_COUNT0)||',XMHJ00=NVL(XMHJ00,0)+'||to_char(ls_COUNT0)||' where  DYID00 = '||to_char(ls_GHKS00)||' and ID0000='||to_char(ad_ID0000)||' and BEIZHU='||to_char(ls_GHY000));
         else
              sp_execute_sql('Insert into BM_GHKSTJ(GHRQ00,GHKS'||ls_GHLBBH||',ID0000,XMHJ00,DYID00,BEIZHU) values('''||ls_BMMC00||''','||to_char(ls_COUNT0)||','||to_char(ad_ID0000)||','||to_char(ls_COUNT0)||','||to_char(ls_GHKS00)||','||to_char(ls_GHY000)||')');
         end if;
        --GHKS40 为合计字段
       end loop;
  close CUR_SF_GHLBTJ_RBB000;

  /*--添加一条总计行
  insert into BM_GHKSTJ(ID0000,GHRQ00,GHKS01,GHKS02,GHKS03,GHKS04,GHKS05,GHKS06,GHKS07,GHKS08,GHKS09,GHKS10,GHKS11,
                      GHKS12,GHKS13,GHKS14,GHKS15,GHKS16,GHKS17,GHKS18,GHKS19,GHKS20,GHKS21,GHKS22,GHKS23,GHKS24,
                      GHKS25,GHKS26,GHKS27,GHKS28,GHKS29,GHKS30,GHKS31,GHKS32,GHKS33,GHKS34,GHKS35,GHKS36,GHKS37,
                      GHKS38,GHKS39,GHKS40,GHKS61,GHKS62,GHKS63,GHKS64,XMHJ00)
    select ad_ID0000,' 合计',sum(GHKS01),sum(GHKS02),sum(GHKS03),sum(GHKS04),sum(GHKS05),sum(GHKS06),sum(GHKS07),
           sum(GHKS08),sum(GHKS09),sum(GHKS10),sum(GHKS11),sum(GHKS12),sum(GHKS13),sum(GHKS14),sum(GHKS15),sum(GHKS16),
           sum(GHKS17),sum(GHKS18),sum(GHKS19),sum(GHKS20),sum(GHKS21),sum(GHKS22),sum(GHKS23),sum(GHKS24),sum(GHKS25),
           sum(GHKS26),sum(GHKS27),sum(GHKS28),sum(GHKS29),sum(GHKS30),sum(GHKS31),sum(GHKS32),sum(GHKS33),sum(GHKS34),
           sum(GHKS35),sum(GHKS36),sum(GHKS37),sum(GHKS38),sum(GHKS39),sum(GHKS40),sum(GHKS61),sum(GHKS62),sum(GHKS63),sum(GHKS64),sum(XMHJ00)
      from BM_GHKSTJ
     where ID0000 = ad_ID0000;
*/

exception
      when others then
                as_YHMSG0:='出错原因不详,请记录此信息并和系统管理员联系！';
                as_SYSMSG:=substr(SQLERRM||'执行存储过程错误:SP_SF_GHLBTJ_RBB000_YS('||as_KSRQ00||','||as_KSSJ00||','||as_JSRQ00||','||
                    as_JSSJ00||','||'ad_ID0000,as_YHMSG0,as_SYSMSG',1,150);
                rollback;
end;