DROP PROCEDURE sp_sf_gzltj2
/


CREATE OR REPLACE
procedure SP_SF_GZLTJ2
(
        AS_KSRQ00    in    char,                --开始日期
        AS_JSRQ00    in    char,                --结束日期
        AS_KSSJ00    in    char,                --开始时间
        AS_JSSJ00    in    char,                --结束时间
        AS_KSBH00    in    char,                --科室
        AS_GHLB00    in    char,
        AD_ID0000    out   number,                --统计报表对应的ID
        AS_YHMSG0    out   varchar,               --存储过程提示的错误信息
        AS_SYSMSG    out   varchar                --系统提示的错误信息
)

as

  LS_GHKS00  YF_MZCF00.GHKS00%type;
  LS_YSGZH0  YF_MZCF00.YSGZH0%type;
  LS_YPDLBH  YF_MZCF00.YPDLBH%type;
  LS_YSLB00  BM_YGBM00.YSLB00%type;
  LS_LBBH00  BM_GHLBB0.LBBH00%type;
  LS_CFZJE0  YF_MZCF00.CFZJE0%type;


CURSOR  CUR_CF_TOTAL is


select a.GHKS00,a.YSGZH0,a.LBBH00,a.YSLB00,a.YPDLBH,count(*) CFZJE0
 from VW_SF_MZCF00 a
  WHERE  A.cfzt00  IN (1,2) AND
   A.JZRQ00>=AS_KSRQ00 and  A.JZRQ00 <=AS_JSRQ00
  and A.JZRQ00||A.JZSJ00 >= AS_KSRQ00||AS_KSSJ00
 and A.JZRQ00||A.JZSJ00 <= AS_JSRQ00||AS_JSSJ00
  and  (a.GHKS00=AS_KSBH00 or AS_KSBH00='-1')
 and    (a.LBBH00=AS_GHLB00 or AS_GHLB00='-1')
 GROUP  BY  a.GHKS00,a.YSGZH0,a.LBBH00,a.YSLB00,a.YPDLBH;


BEGIN

    select  SQ_BM_YYSFTJ_ID0000.nextval  into  ad_ID0000  from  dual;
     OPEN CUR_CF_TOTAL;
     LOOP
       FETCH CUR_CF_TOTAL INTO  LS_GHKS00,LS_YSGZH0,LS_LBBH00,LS_YSLB00,LS_YPDLBH,LS_CFZJE0;
       EXIT WHEN CUR_CF_TOTAL%NOTFOUND;


--SFXM01 科室  SFXM02 医生  SFXM03  挂号类别  SFXM13 医师类别 SFXM04  草药处分  SFXM05  药品处分  XMHJ00  总处分
    IF  ls_YPDLBH =2   THEN  --草药处方SFXM04

    Update  BM_YYSFTJ  Set  SFXM04 = NVL(SFXM04,0)+LS_CFZJE0,  XMHJ00= NVL(XMHJ00,0)+LS_CFZJE0

     where   ID0000=ad_ID0000 and SFXM01=LS_GHKS00 and SFXM02=LS_YSGZH0 and SFXM03=LS_LBBH00 and SFXM13=LS_YSLB00 ;

    if SQL%NOTFOUND  then
     Insert into BM_YYSFTJ(ID0000,BMBH00,SFXM01,SFXM02,SFXM03,SFXM13,SFXM04,XMHJ00)
     values(ad_ID0000,0,LS_GHKS00,LS_YSGZH0,LS_LBBH00,LS_YSLB00,LS_CFZJE0,LS_CFZJE0);

    end if;
    else   --药品SFXM05

    Update  BM_YYSFTJ  Set  SFXM05 = NVL(SFXM05,0)+LS_CFZJE0,  XMHJ00= NVL(XMHJ00,0)+LS_CFZJE0

     where   ID0000=ad_ID0000 and SFXM01=LS_GHKS00 and SFXM02=LS_YSGZH0 and SFXM03=LS_LBBH00 and SFXM13=LS_YSLB00 ;

    if SQL%NOTFOUND  then
     Insert into BM_YYSFTJ(ID0000,BMBH00,SFXM01,SFXM02,SFXM03,SFXM13,SFXM05,XMHJ00)
     values(ad_ID0000,0,LS_GHKS00,LS_YSGZH0,LS_LBBH00,LS_YSLB00,LS_CFZJE0,LS_CFZJE0);
    end if;

  END IF;
  end loop;


---合计
 Insert into BM_YYSFTJ(ID0000,BMBH00,SFXM01,SFXM02,SFXM03,SFXM13,SFXM04,SFXM05,XMHJ00,SFXM06)
select ad_ID0000,1,a.SFXM01,a.SFXM02,a.SFXM03,a.SFXM13,sum(a.SFXM04),sum(a.SFXM05), sum(a.XMHJ00),
decode(sum(a.XMHJ00),0,0,sum(a.SFXM04)/sum(a.XMHJ00))
from  BM_YYSFTJ a
  where
  a.ID0000=ad_ID0000  and a.BMBH00=0
  group by a.SFXM01,a.SFXM02,a.SFXM03,a.SFXM13 ;

delete BM_YYSFTJ  where  ID0000=ad_ID0000 and BMBH00=0;


EXCEPTION
      WHEN OTHERS THEN
                as_YHMSG0:='出错原因不详,请记录此信息并和系统管理员联系！';
                as_SYSMSG:=substr(SQLERRM||'执行存储过程错误:SP_SF_GZLTJ2',1,150);
                ROLLBACK;
END;
/