CREATE OR REPLACE PROCEDURE SF_SF_SDS000   
(
  as_KSRQ00    in    char,     --开始统计日期
  as_JSRQ00    in    char,     --结束统计日期
  as_YSLB00    in    char,     --医生类别 -1：全部  0：院内聘   1：院外聘
  as_KDKS00    in    char,     --科室名称 ‘-1’:全部
  as_KDYS00    in    number,   --开单医生  
  as_TJFS00    in    char,     --统计方式 0：正常工作日 1：节假日
  as_YYID00    in    char,     --医院ID -1：全院
  ad_ID0000    in    number    --统计报表对应的ID
)
as
LS_GHKS00  YF_MZCF00.GHKS00%type;
LS_GHKS01  YF_MZCF00.GHKS00%type;
LS_YSGZH0  YF_MZCF00.YSGZH0%type;
LS_YSLB00  BM_YGBM00.YSLB00%type;
LS_LBBH00  BM_GHLBB0.LBBH00%type;
LS_CFZJE0  YF_MZCF00.CFZJE0%type;
LS_YPDLBH  YF_MZCF00.YPDLBH%type;
lv_HJJE00  SF_FYMX00.HJJE00%TYPE;
ls_GDBLB1  number(10,2);
ls_GDBLB2  number(10,2);
ls_GDBLB3  number(10,2);
ls_GDBLB4  number(10,2);
ls_GDBLB5  number(10,2);
ls_GDBLB6  number(10,2);
ls_GDBLB0  number(10,2);
ls_YSFLBH  BM_YGBM00.YSFLBH%type;
--实得数 
 cursor CUR_SF_SDS000 is
 select a.GHKS00,a.YSGZH0,a.LBBH00,a.YSLB00,a.YPDLBH,SUM(CFZJE0) CFZJE0
   from VW_SF_MZCF00 a,BM_YGBM00 b
  where A.cfzt00 in (1,2) 
   and  A.JZRQ00 >= AS_KSRQ00
   and  A.JZRQ00 <= AS_JSRQ00 
   and  A.YSGZH0 = b.YGBH00
   and  A.YSGZH0=as_KDYS00 and (A.GHKS00 = as_KDKS00 or as_KDKS00 ='-1')
   and  A.YSGZH0 in (select BMBH00 from BM_BMBM00 k where (K.YYID00=as_YYID00 or as_YYID00='-1'))
   and  SF_SF_GetZCGZR(A.JZRQ00,A.JZSJ00)=as_TJFS00 
   and (b.YSPQLB=as_YSLB00 or as_YSLB00='-1')
 group by a.GHKS00,a.YSGZH0,a.LBBH00,a.YSLB00,a.YPDLBH;
begin 
  LS_GHKS01:=-1;
  open CUR_SF_SDS000;
  loop
  fetch CUR_SF_SDS000 into LS_GHKS00,LS_YSGZH0,LS_LBBH00,LS_YSLB00,LS_YPDLBH,LS_CFZJE0;
  exit when CUR_SF_SDS000%notfound;
  
     if LS_GHKS01<>-1 and LS_GHKS01<>LS_GHKS00 and ls_GDBLB3>0 then

          if ls_GDBLB5=0 then
            ls_GDBLB6:=0;
          else 
            ls_GDBLB6:=ls_GDBLB4/ls_GDBLB5;
          end if;
          
          select YSFLBH into ls_YSFLBH from BM_YGBM00 where YGBH00=as_KDYS00;
      
          if ls_YSFLBH='0' then
              select round((decode(ls_GDBLB1,0,0,(nvl(ls_GDBLB6,0)-nvl(ls_GDBLB1, 0))/ls_GDBLB1)),2) into ls_GDBLB0 from dual;
          elsif ls_YSFLBH='1' then
              select round((decode(ls_GDBLB2,0,0,(nvl(ls_GDBLB6,0)-nvl(ls_GDBLB2, 0))/ls_GDBLB2)),2) into ls_GDBLB0 from dual;
          end if;
      
          select round(decode(sign(floor(nvl(ls_GDBLB0,0)/1)),-1,ls_GDBLB3*0.3,0,ls_GDBLB3*0.3+ls_GDBLB3*0.2*ls_GDBLB0,1,ls_GDBLB3*0.5),0) into ls_GDBLB0 from dual;
      
          update BM_YYSFTJ Set SFXM51=nvl(SFXM51,0) +ls_GDBLB3,SFXM52 = NVL(SFXM52,0)+ls_GDBLB0 where  ID0000=ad_ID0000  and BMBH00=as_KDYS00 and SFXM99=1;
          if SQL%NOTFOUND  then
            insert into BM_YYSFTJ(ID0000,BMBH00,SFXM51,SFXM52,SFXM99) values(ad_ID0000,as_KDYS00,nvl(ls_GDBLB3,0),nvl(ls_GDBLB0,0),1);
          end if;     
     end if;
     if LS_GHKS01<>LS_GHKS00 then
       ls_GDBLB4:=0;
       ls_GDBLB5:=0;
     end if;
      ls_GDBLB1:=SF_SF_KSGDBL(LS_GHKS00,LS_YSLB00,LS_YSGZH0,0);
      ls_GDBLB2:=SF_SF_KSGDBL(LS_GHKS00,LS_YSLB00,LS_YSGZH0,1);
    begin  
     select sum(a.HJJE00) into ls_GDBLB3 
       from SF_FYMX00 a,SF_BRFY00 b,XT_ZLSFGX c,YS_ZYBZLZ d
      where a.DJH000 = b.DJH000 and a.XMBH00 = c.SFXMID
        and c.ZLXMID = d.ZLXMID and a.czrq00 >=as_KSRQ00
        and a.KDKS00 =LS_GHKS00 and a.KDYS00 = LS_YSGZH0
        and a.CZRQ00 <=as_JSRQ00 group by KDYS00,KDKS00;  
    exception
       when others then
        ls_GDBLB3:=0;
    end;  
    if LS_YPDLBH=2 then
      ls_GDBLB4:=nvl(ls_GDBLB4,0)+LS_CFZJE0;
    end if;  
     
    ls_GDBLB5:=nvl(ls_GDBLB5,0)+LS_CFZJE0;
     
    LS_GHKS01:=LS_GHKS00;
  end loop;
  close CUR_SF_SDS000;
  --只有单条记录时 
    if ls_GDBLB3>0 then
      if ls_GDBLB5=0 then
        ls_GDBLB6:=0;
      else 
        ls_GDBLB6:=ls_GDBLB4/ls_GDBLB5;
      end if;
      
      select YSFLBH into ls_YSFLBH from BM_YGBM00 where YGBH00=as_KDYS00;
  
      if ls_YSFLBH='0' then
          select round((decode(ls_GDBLB1,0,0,(nvl(ls_GDBLB6,0)-nvl(ls_GDBLB1, 0))/ls_GDBLB1)),2) into ls_GDBLB0 from dual;
      elsif ls_YSFLBH='1' then
          select round((decode(ls_GDBLB2,0,0,(nvl(ls_GDBLB6,0)-nvl(ls_GDBLB2, 0))/ls_GDBLB2)),2) into ls_GDBLB0 from dual;
      end if;
  
      select round(decode(sign(floor(nvl(ls_GDBLB0,0)/1)),-1,ls_GDBLB3*0.3,0,ls_GDBLB3*0.3+ls_GDBLB3*0.2*ls_GDBLB0,1,ls_GDBLB3*0.5),0) into ls_GDBLB0 from dual;
  
      update BM_YYSFTJ Set SFXM51=nvl(SFXM51,0) + ls_GDBLB3,SFXM52 = NVL(SFXM52,0)+ls_GDBLB0 where  ID0000=ad_ID0000  and BMBH00=as_KDYS00 and SFXM99=1;
      if SQL%NOTFOUND  then
        insert into BM_YYSFTJ(ID0000,BMBH00,SFXM51,SFXM52,SFXM99) values(ad_ID0000,as_KDYS00,nvl(ls_GDBLB3,0),nvl(ls_GDBLB0,0),1);
      end if; 
    end if; 

 --下午出诊奖励 
   begin
     select sum(b.HJJE00) into lv_HJJE00
                          from SF_BRXXB0 a, SF_BRFY00 b,BM_YGBM00 c
                         where a.GHRQ00 >= as_KSRQ00
                           and a.GHRQ00 <= as_JSRQ00
                           and to_char(to_date(a.GHRQ00,'YYYY.MM.DD')-1,'D') in ('1', '2', '3', '5')
                           and a.GHSJ00 >= '14:30:00'
                           and a.GHSJ00 <= '17:30:00'
                           and a.THRQ00 is null
                           and a.GHLB00 <> '-99999'
                           and a.JZYS00 > 0
                           and a.GHID00 = b.MZID00
                           and a.JZYS00 = as_KDYS00
                           and a.JZYS00 = c.YGBH00
                           and (c.YSPQLB=as_YSLB00 or as_YSLB00='-1')
                           and SF_SF_GetZCGZR(A.GHRQ00,A.GHSJ00)=as_TJFS00
                           and (a.JZKS00 = as_KDKS00 or as_KDKS00 ='-1')
                           and a.JZYS00 in (select BMBH00 from BM_BMBM00 k where (K.YYID00=as_YYID00 or as_YYID00='-1'))
                      group by a.JZYS00; 
   exception
   when others then
   lv_HJJE00:=0;
   end;               
   update  BM_YYSFTJ  Set  SFXM62 = NVL(SFXM61,0)+lv_HJJE00,SFXM61 = NVL(SFXM61,0)+lv_HJJE00 where   ID0000=ad_ID0000  and BMBH00=as_KDYS00 and SFXM99=1;
   if SQL%NOTFOUND  then
     insert into BM_YYSFTJ(ID0000,BMBH00,SFXM61,SFXM62,SFXM99) values(ad_ID0000,as_KDYS00,NVL(lv_HJJE00,0),NVL(lv_HJJE00,0),1);
   end if;
--预约挂号奖励
   begin
         select count(YYID00) into lv_HJJE00
            from SF_YYBRB0 a,BM_YGBM00 b
           where a.YYRQ00>=as_KSRQ00 AND a.YYRQ00<=as_JSRQ00
             and a.YYZT00 in ('1', '3')
             and a.YYYSBH=as_KDYS00 and (a.YYKSBH=as_KDKS00 or as_KDKS00='-1')
             and SF_SF_GetZCGZR(a.YYRQ00,a.YYJSSJ)=as_TJFS00 
             and a.YYKSBH in (select BMBH00 from BM_BMBM00 k where (K.YYID00=as_YYID00 or as_YYID00='-1'))
             and a.YYYSBH=b.YGBH00
             and (b.YSPQLB=as_YSLB00 or as_YSLB00='-1')
        group by YYYSBH;
   exception
   when others then
    lv_HJJE00:=0;
   end;
   update  BM_YYSFTJ  Set  SFXM72 = NVL(SFXM71,0)+lv_HJJE00,SFXM71 = NVL(SFXM71,0)+lv_HJJE00 where   ID0000=ad_ID0000  and BMBH00=as_KDYS00 and SFXM99=1;
   if SQL%NOTFOUND  then
     insert into BM_YYSFTJ(ID0000,BMBH00,SFXM71,SFXM72,SFXM99) values(ad_ID0000,as_KDYS00,NVL(lv_HJJE00,0),NVL(lv_HJJE00,0),1);
   end if;
--病历协诊奖励   
   begin  
        select nvl(count(*),0)*20 into lv_HJJE00 
          from ZY_BRXXB0 a,BM_YGBM00 b,BM_BMBM00 c  
         where a.BRZT00<>'9' and  a.SZYS00=b.YGBH00 and b.BMBH00=c.BMBH00 
           and (c.YYID00=as_YYID00 or as_YYID00='-1')
           and SF_SF_GetZCGZR(RYRQ00,RYSJ00)=as_TJFS00 
           and RYRQ00>= as_KSRQ00 and RYRQ00<=as_JSRQ00
           and (a.DQKS00=as_KDKS00 or as_KDKS00='-1')
           and a.SZYS00=as_KDYS00
           and (b.YSPQLB=as_YSLB00 or as_YSLB00='-1')
      group by a.SZYS00; 
   exception
   when others then
    lv_HJJE00:=0;
   end;
   update  BM_YYSFTJ  Set  SFXM82 = NVL(SFXM81,0)+lv_HJJE00,SFXM81 = NVL(SFXM81,0)+lv_HJJE00 where   ID0000=ad_ID0000  and BMBH00=as_KDYS00 and SFXM99=1;
   if SQL%NOTFOUND  then
     insert into BM_YYSFTJ(ID0000,BMBH00,SFXM81,SFXM82,SFXM99) values(ad_ID0000,as_KDYS00,NVL(lv_HJJE00,0),NVL(lv_HJJE00,0),1);
   end if;     
end;  
/
--%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

CREATE OR REPLACE PROCEDURE SP_SF_CWJJSHTJ 
( as_KSRQ00    in    char,     --开始统计日期
  as_JSRQ00    in    char,     --结束统计日期
  as_YSLB00    in    char,     --医生类别 -1：全部  0：院内聘   1：院外聘
  as_KDKS00    in    char,     --科室名称 ‘-1’:全部
  as_TJFS00    in    char,     --统计方式 0：正常工作日 1：节假日
  as_YYID00    in    char,     --医院ID -1：全院
  ad_ID0000    out   number,   --统计报表对应的ID
  as_YHMSG0    out   varchar,  --存储过程提示的错误信息
  as_SYSMSG    out   varchar   --系统提示的错误信息
)
as
--MODIFY HISTORY
--date        person     comments
--2011.07.13  zhangyc    create; by MZSF-20110704-001
lv_KDYS00  SF_FYMX00.KDYS00%TYPE;
lv_FPBH00  BM_FPXM00.bh0000%TYPE;
lv_HJJE00  SF_FYMX00.HJJE00%TYPE;
lv_HJJE01  SF_FYMX00.HJJE00%TYPE;
lv_YSPQLB  bm_ygbm00.YSPQLB%TYPE;

 CURSOR CUR_SF_FYJE00 IS
 Select BMBH00,BMMC00,SFXM77,sum(XMHJ00)XMHJ00 from BM_YYSFTJ
   where ID0000=ad_ID0000 and SFXM99=0
 group by BMBH00,BMMC00,SFXM77;
 --统计医生 
 cursor CUR_SF_TJYS00 is 
 select distinct BMBH00 KDYS00 from BM_YYSFTJ where ID0000=ad_ID0000;

begin
 select  SQ_BM_YYSFTJ_ID0000.nextval  into  ad_ID0000  from  dual;

 ---BMBH00:医生编号 BMMC00：发票编号 SJYSXM：发票名称 XMHJ00：合计金额  SFXM77:院内聘或院外聘  SFXM88：是否正常工作日 0：正常 1节假日
Insert into BM_YYSFTJ(ID0000,BMBH00,BMMC00,SJYSXM,XMHJ00,SFXM77,SFXM99)
 select  ad_ID0000,A.kdys00,B.bh0000,B.XMMC00,sum(A.HJJE00)HJJE00,e.YSPQLB,0
        from  SF_FYMX00 A,SF_BRFY00 D,BM_YYSFXM C,BM_YGBM00 e,BM_FPXM00 B
            where  A.XMBH00 = C.SFXMID  and  C.MZFPID = B.FPXMID
            and  A.DJH000 = D.DJH000
            and  A.kdys00=e.YGBH00(+)
            and   d.czrq00>=as_KSRQ00 and d.czrq00<=as_JSRQ00
            and (a.kdks00 = as_KDKS00 or as_KDKS00 ='-1')
            and (
                 (SF_SF_GetZCGZR(D.czrq00,D.czsj00)=as_TJFS00 and (B.BH0000||'' not in ('15','16','08','09','14')))
                 or
                 ((B.BH0000||'' in ('15','16','08','09','14')) and as_TJFS00=0)
                 )
            and  B.bh0000||'' not in ('01','02','03') ---扣除掉药品费
            and a.kdks00 in (select bmbh00 from bm_bmbm00 k where (K.yyid00=as_YYID00 OR as_YYID00='-1'))
            and (e.YSPQLB=as_YSLB00 or as_YSLB00='-1')
group by ad_ID0000,A.kdys00,B.bh0000,B.XMMC00,e.YSPQLB;

  --01:西药费 02:成药费 03:草药费 04:挂号费 05:床位费 06:诊察费 07:护理费 08:检查费 09:化验费 10:治疗费
  --11:手术费 12:救护车费 13:其他费 14:MRI费 15:CT费 16:彩超费 17:输氧费  18:输血费 19:麻醉费 20:麻醉相关 21:其它医疗

 --SFXM01:检查费费用  SFXM02:应得检查费奖金  SFXM11:治疗费 SFXM12:应得治疗奖金
 --SFXM21:制剂  SFXM22:应得制剂奖金  SFXM31:会诊费 SFXM32:应得会诊费奖金
 --SFXM41:诊疗费用  SFXM42:应得诊疗费奖金
 --SFXM51 中医辨证论治 SFXM71 预约挂号奖励
 --SFXM61 下午出诊奖励 SFXM81 病历协诊奖励
 --SFXM111 横向合计  
 open CUR_SF_TJYS00;
 loop 
 fetch CUR_SF_TJYS00 into lv_kdys00;
 exit when CUR_SF_TJYS00%notfound;

 --实得数 
   SF_SF_SDS000(as_KSRQ00,as_JSRQ00,as_YSLB00,as_KDKS00,lv_kdys00,as_TJFS00,as_YYID00,ad_ID0000);

   
 end loop;
 close CUR_SF_TJYS00;
 
 
 OPEN CUR_SF_FYJE00;
	LOOP
	FETCH CUR_SF_FYJE00 INTO  lv_kdys00,lv_FPBH00,lv_YSPQLB,lv_HJJE00;
                   EXIT WHEN CUR_SF_FYJE00%NOTFOUND;

  --检查费费用统计(检查费)包含内容：发票项目为  CT、彩超、检查、化验、MRI
   IF  lv_FPBH00  IN ('15','16','08','09','14')  then
           Update  BM_YYSFTJ  Set  SFXM01 = NVL(SFXM01,0)+lv_HJJE00 where   ID0000=ad_ID0000  and BMBH00=lv_kdys00 and SFXM99=1;
          if SQL%NOTFOUND  then
          Insert into BM_YYSFTJ(ID0000,BMBH00,SFXM01,SFXM99) values(ad_ID0000,lv_kdys00,NVL(lv_HJJE00,0),1);
         end if;
   end IF;

  --治疗费包含内容：治疗费、手术费  治疗费：按开单治疗费用的10%来计算。（节假日才有，正常没有。外聘的都有）
   IF  (lv_FPBH00  IN ('11','10')) then
--      if lv_YSPQLB='0'then   --内聘,正常工作日
--          if as_TJFS00='0' then
--            lv_HJJE01:=0;
--          else
--            lv_HJJE01:=lv_HJJE00;
--          end if;
--      else   --外聘聘
--          lv_HJJE01:=lv_HJJE00;
--       end if;

           Update  BM_YYSFTJ  Set  SFXM11 = NVL(SFXM11,0)+lv_HJJE00 where   ID0000=ad_ID0000  and BMBH00=lv_kdys00 and SFXM99=1;
          if SQL%NOTFOUND  then
          Insert into BM_YYSFTJ(ID0000,BMBH00,SFXM11,SFXM99) values(ad_ID0000,lv_kdys00,NVL(lv_HJJE00,0),1);
         end if;
   end IF;

    end loop;

 delete  BM_YYSFTJ where ID0000=ad_ID0000 and SFXM99=0;


   ----治疗费包含内容：治疗费
   --3、	增加参数或通过其他标识来设置 某些科室为外科系统科室（外科系统科室才有治疗费的补助），且可以某些医生的奖金提取比例可以单独设置。
--  Insert into BM_YYSFTJ(ID0000,BMBH00,SFXM11,SFXM99)
-- select  ad_ID0000,A.kdys00,sum(A.HJJE00)HJJE00,1
--        from  SF_FYMX00 A,SF_BRFY00 D,BM_YYSFXM C,BM_YGBM00 E,BM_FPXM00 B
--            where  A.XMBH00 = C.SFXMID  and  C.MZFPID = B.FPXMID
--             and  A.DJH000 = D.DJH000   and  B.bh0000||''  in ('10','11')
----             and  a.kdks00 in (select bmbh00 from  BM_BMBM00 where SFWKKS='1')
--             and  d.czrq00>=as_KSRQ00 and d.czrq00<=as_JSRQ00
--             and (a.kdks00 = as_KDKS00 or as_KDKS00 ='-1')
--             and SF_SF_GetZCGZR(D.czrq00,D.czsj00)=as_TJFS00
--             and  A.kdys00=e.YGBH00(+)
--             and (e.YSPQLB=as_YSLB00 or as_YSLB00='-1')
--group by ad_ID0000,A.kdys00;

---制剂费用统计 bm_yd0000.zjbz00=1
Insert into BM_YYSFTJ(ID0000,BMBH00,SFXM21,SFXM99)
select ad_ID0000,C.YSGZH0,sum(ROUND(A.YPZSL0*A.LSDJ00,2)),1  from  yf_mzcfmx A,yf_mzcf00 C, bm_yd0000 B,BM_YGBM00 e
    where  A.CFLSH0 = C.CFLSH0  and  A.YPNM00 = B.YPNM00
     and  B.zjbz00 ='1'
     and  C.FYRQ00>=as_KSRQ00 and  C.FYRQ00<=as_JSRQ00
     and C.SSks00 in (select bmbh00 from bm_bmbm00 k where (K.yyid00=as_YYID00 OR as_YYID00='-1'))
     and (c.SSKS00 = as_KDKS00 or as_KDKS00 ='-1')
     and  SF_SF_GetZCGZR(C.FYRQ00,c.FYSJ00)=as_TJFS00
     and  C.YSGZH0=e.YGBH00(+)
     and (e.YSPQLB=as_YSLB00 or as_YSLB00='-1')
     group by ad_ID0000,C.YSGZH0;

--会诊费：按所开会诊费全给。（主任及副主任才有会诊费，且要判断开单科室与该医生与属科室要为不同科室才可以收取）
--会诊费判断（select sfxmid from bm_yysfxm where bh0000 like ‘01110%’）
Insert into BM_YYSFTJ(ID0000,BMBH00,SFXM31,SFXM99)
 select  ad_ID0000,A.zxys00,sum(A.HJJE00)HJJE00,1
        from  SF_FYMX00 A,BM_FPXM00 B,BM_YYSFXM C,SF_BRFY00 D,BM_YGBM00 E
            where  A.XMBH00 = C.SFXMID  and  C.MZFPID = B.FPXMID
             and  A.DJH000 = D.DJH000   and  A.zxys00=e.ygbh00(+)
             and e.yslb00  in (select yslb00 from bm_yslb00 where (yslbmc like '主任%') or (yslbmc like '副主任%'))
             and a.kdks00<>e.bmbh00
             and a.kdks00 in (select bmbh00 from bm_bmbm00 k where (K.yyid00=as_YYID00 OR as_YYID00='-1'))
             and   d.czrq00>=as_KSRQ00 and d.czrq00<=as_JSRQ00
             and (a.kdks00 = as_KDKS00 or as_KDKS00 ='-1')
             and SF_SF_GetZCGZR(D.czrq00,D.czsj00)=as_TJFS00
             and  c.bh0000||'' like '01110%'
             and (e.YSPQLB=as_YSLB00 or as_YSLB00='-1')
group by ad_ID0000,A.zxys00
union all
select  ad_ID0000,A.zxys00,sum(A.HJJE00)HJJE00,1
        from  ZY_FYMX00 A,BM_FPXM00 B,BM_YYSFXM C,ZY_BRFY00 D,BM_YGBM00 E
            where  A.XMBH00 = C.SFXMID  and  C.MZFPID = B.FPXMID
             and  A.DJH000 = D.DJH000   and  A.zxys00=e.ygbh00(+)
             and e.yslb00  in (select yslb00 from bm_yslb00 where (yslbmc like '主任%') or (yslbmc like '副主任%'))
             and a.kdks00<>e.bmbh00
             and   d.czrq00>=as_KSRQ00 and d.czrq00<=as_JSRQ00
             and a.kdks00 in (select bmbh00 from bm_bmbm00 k where (K.yyid00=as_YYID00 OR as_YYID00='-1'))
             and (a.kdks00 = as_KDKS00 or as_KDKS00 ='-1')
             and SF_SF_GetZCGZR(D.czrq00,D.czsj00)=as_TJFS00
             and  c.bh0000||'' like '01110%'
             and (e.YSPQLB=as_YSLB00 or as_YSLB00='-1')
group by ad_ID0000,A.zxys00;

/*
   --诊疗费用：发票项目为06:诊察费
   IF  lv_FPBH00  IN ('06')  then
           Update  BM_YYSFTJ  Set  SFXM41 = NVL(SFXM41,0)+lv_HJJE00 where   ID0000=ad_ID0000  and BMBH00=lv_kdys00 and SFXM99=11;
          if SQL%NOTFOUND  then
          Insert into BM_YYSFTJ(ID0000,BMBH00,SFXM41,SFXM99) values(ad_ID0000,lv_kdys00,NVL(lv_HJJE00,0),11);
         end if;
   end IF;
 */
 --诊疗费的补助按该医生就诊时给病人挂号的类别来计算，主任1.8元、副主任1.2元、主治及以下0.6元，
 --院外聘人员按收取诊疗费用的50%来补助。
Insert into BM_YYSFTJ(ID0000,BMBH00,SFXM01,SFXM66,SFXM41,SFXM55,SFXM99)
 select  ad_ID0000,A.kdys00,sign(A.HJJE00),e.GHLB00,sum(A.HJJE00)HJJE00,count(distinct E.GHID00),11
        from  SF_FYMX00 A,SF_BRFY00 D,sf_brxxb0 E,BM_YYSFXM C,BM_YGBM00 F,BM_FPXM00 B
            where  A.XMBH00 = C.SFXMID  and  C.MZFPID = B.FPXMID
             and  A.DJH000 = D.DJH000   and  d.MZID00 = e.GHID00(+)
             and  B.bh0000||''  in ('06')   and A.HJJE00<>0 and A.HJJE00<>1
             and   d.czrq00>=as_KSRQ00 and d.czrq00<=as_JSRQ00
             and a.kdks00 in (select bmbh00 from bm_bmbm00 k where (K.yyid00=as_YYID00 OR as_YYID00='-1'))
             and (a.kdks00 = as_KDKS00 or as_KDKS00 ='-1')
             and SF_SF_GetZCGZR(D.czrq00,D.czsj00)=as_TJFS00
             and exists (select 1 from SF_FJGHF0 where FJFFBH=A.XMBH00)
             and  A.KDYS00=F.YGBH00(+)
             and (F.YSPQLB=as_YSLB00 or as_YSLB00='-1')
group by ad_ID0000,A.kdys00,sign(A.HJJE00),e.GHLB00;

Insert into BM_YYSFTJ(ID0000,BMBH00,SFXM66,SFXM41,SFXM55,SFXM99)
select ID0000,BMBH00,SFXM66,sum(SFXM41),sum(SFXM01*SFXM55),111
from   BM_YYSFTJ where id0000=ad_ID0000  and SFXM99=11
group by ID0000,BMBH00,SFXM66;

delete BM_YYSFTJ  where id0000=ad_ID0000  and SFXM99=11;

--SFXM66:挂号类别  SFXM55：挂号次数
Insert into BM_YYSFTJ(ID0000,BMBH00,SFXM41,SFXM42,SFXM99)
select ID0000,BMBH00,SFXM41,SF_SF_GETJJTCJE(BMBH00,NVL(SFXM41,0),SFXM66,1,nvl(SFXM55,0),as_TJFS00),1
from   BM_YYSFTJ where id0000=ad_ID0000  and SFXM99=111;

delete BM_YYSFTJ  where id0000=ad_ID0000  and SFXM99=111;


Insert into BM_YYSFTJ(ID0000,BMBH00,
           SFXM01,SFXM02,
           SFXM11,SFXM12,
           SFXM21,SFXM22,
           SFXM31,SFXM32,
           SFXM41,SFXM42,
           SFXM51,SFXM52,
           SFXM61,SFXM62,
           SFXM71,SFXM72,
           SFXM81,SFXM82,
           XMHJ00,SFXM99)
select  ID0000,BMBH00,
          sum(nvl(SFXM01,0)),sum(nvl(SFXM02,0)),
          sum(nvl(SFXM11,0)),sum(nvl(SFXM12,0)),
          sum(nvl(SFXM21,0)),sum(nvl(SFXM22,0)),
          sum(nvl(SFXM31,0)),sum(nvl(SFXM32,0)),
          sum(nvl(SFXM41,0)),sum(nvl(SFXM42,0)),
          sum(nvl(SFXM51,0)),sum(nvl(SFXM52,0)),
          sum(nvl(SFXM61,0)),sum(nvl(SFXM62,0)),
          sum(nvl(SFXM71,0)),sum(nvl(SFXM72,0)),
          sum(nvl(SFXM81,0)),sum(nvl(SFXM82,0)),
          sum(nvl(SFXM01,0)+nvl(SFXM11,0)+nvl(SFXM21,0)+nvl(SFXM31,0)+nvl(SFXM41,0)),2
 from  BM_YYSFTJ where ID0000=ad_ID0000 and  SFXM99=1
 group by  ID0000,BMBH00;

 delete  BM_YYSFTJ where  ID0000=ad_ID0000 and  SFXM99=1;

 Insert into BM_YYSFTJ(ID0000,BMBH00,
           SFXM01,SFXM02,
           SFXM11,SFXM12,
           SFXM21,SFXM22,
           SFXM31,SFXM32,
           SFXM41,SFXM42,
           SFXM51,SFXM52,
           SFXM61,SFXM62,
           SFXM71,SFXM72,
           SFXM81,SFXM82,
           XMHJ00,SFXM99)
select  ID0000,BMBH00,
         SFXM01,round(SFXM01*0.05), --检查费：按医生开单检查费用的5%来计算。
         SFXM11,SF_SF_GETJJTCJE(BMBH00,NVL(SFXM11,0),0,0,0,as_TJFS00),  --治疗费：按开单治疗费用的10%来计算。（节假日才有，正常没有。外聘的都有）SF_SF_KSGDBL.JJTCBL
         SFXM21,round(SFXM21*0.05), --按所开制剂药品费用的5%来计算
         SFXM31,round(SFXM31*1),    --按所开会诊费全给
         SFXM41,round(SFXM42),--诊疗费的补助按该医生就诊时给病人挂号的类别来计算，主任1.8元、副主任1.2元、主治及以下0.6元，院外聘人员按收取诊疗费用的50%来补助。
         SFXM51,SFXM52,
         SFXM61,SFXM62,
         SFXM71,SFXM72,
         SFXM81,SFXM82,
         XMHJ00,9
 from  BM_YYSFTJ where ID0000=ad_ID0000 and  SFXM99=2;
 --费用合计 
 update  BM_YYSFTJ a set SFXM111=
 (select SFXM01+SFXM11+SFXM21+SFXM31+SFXM41+SFXM51+SFXM61+SFXM71+SFXM81 
 from BM_YYSFTJ where a.BMBH00=BMBH00 and ID0000=a.ID0000 and SFXM99=9) where ID0000=ad_ID0000 and SFXM99=9;
 --奖金合计 
 update  BM_YYSFTJ a set SFXM112=
 (select SFXM02+SFXM12+SFXM22+SFXM32+SFXM42+SFXM52+SFXM62+SFXM72+SFXM82 
 from BM_YYSFTJ where a.BMBH00=BMBH00 and ID0000=a.ID0000 and SFXM99=9) where ID0000=ad_ID0000 and SFXM99=9;

  delete  BM_YYSFTJ where  ID0000=ad_ID0000 and  SFXM99=2;
  
  update BM_YYSFTJ SET XMHJ00=NVL(SFXM111,0)+NVL(SFXM112,0) WHERE ID0000=ad_ID0000;
  
  --修改医生对应的科室
  update BM_YYSFTJ A SET SFXM200=(Select B.BMBH00 from BM_YGBM00  B WHERE  B.YGBH00=A.BMBH00)
  where A.ID0000=ad_ID0000 and A.BMBH00>0;
  
  --修改明细数据标识 SFXM201=0 明细  SFXM201=1 科室汇总 SFXM201=2  全部汇总
  
  update BM_YYSFTJ A SET SFXM201=0 WHERE ID0000=ad_ID0000;
  
  --插入科室合计
  insert INTO BM_YYSFTJ (ID0000,BMBH00,XMHJ00,SFXM200,SFXM201,SFXM01,SFXM02,SFXM11,SFXM12,SFXM21,SFXM22,SFXM31,SFXM32,SFXM41,SFXM42,SFXM51,SFXM52,SFXM61,
                          SFXM62,SFXM71,SFXM72,SFXM81,SFXM82,SFXM111,SFXM112)
  Select ad_ID0000,0,sum(XMHJ00),SFXM200,1,SUM(SFXM01),SUM(SFXM02),SUM(SFXM11),SUM(SFXM12),SUM(SFXM21),SUM(SFXM22),SUM(SFXM31),SUM(SFXM32),
         SUM(SFXM41),SUM(SFXM42),SUM(SFXM51),SUM(SFXM52),SUM(SFXM61),SUM(SFXM62),SUM(SFXM71),SUM(SFXM72),SUM(SFXM81),SUM(SFXM82),SUM(SFXM111),
         SUM(SFXM112)
  from BM_YYSFTJ where ID0000=ad_ID0000 AND BMBH00>0 GROUP BY SFXM200;
  
  --插入总合计
  
  insert INTO BM_YYSFTJ (ID0000,BMBH00,XMHJ00,SFXM200,SFXM201,SFXM01,SFXM02,SFXM11,SFXM12,SFXM21,SFXM22,SFXM31,SFXM32,SFXM41,SFXM42,SFXM51,SFXM52,SFXM61,
                          SFXM62,SFXM71,SFXM72,SFXM81,SFXM82,SFXM111,SFXM112)
  Select ad_ID0000,0,sum(XMHJ00),0,2,SUM(SFXM01),SUM(SFXM02),SUM(SFXM11),SUM(SFXM12),SUM(SFXM21),SUM(SFXM22),SUM(SFXM31),SUM(SFXM32),
         SUM(SFXM41),SUM(SFXM42),SUM(SFXM51),SUM(SFXM52),SUM(SFXM61),SUM(SFXM62),SUM(SFXM71),SUM(SFXM72),SUM(SFXM81),SUM(SFXM82),SUM(SFXM111),
         SUM(SFXM112)
  from BM_YYSFTJ where ID0000=ad_ID0000  and SFXM201=0;
--横向合计
 
--commit;
     EXCEPTION
      WHEN OTHERS THEN
                as_YHMSG0:='出错原因不详,请记录此信息并和系统管理员联系！';
                as_SYSMSG:=substr(SQLERRM||'执行存储过程错误:SP_SF_CWJJSHTJ('||as_KSRQ00||','||as_JSRQ00|| ','||as_YSLB00||','||as_KDKS00||','||as_TJFS00||
                'ad_ID0000,as_YHMSG0,as_SYSMSG',1,150);
		ROLLBACK;

end;
/
show error;
--%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%


CREATE OR REPLACE PROCEDURE SP_SF_CWJJSHTJ_ALL 
( as_KSRQ00    in    char,     --开始统计日期
  as_JSRQ00    in    char,     --结束统计日期
  as_YSLB00    in    char,     --医生类别 -1：全部  0：院内聘   1：院外聘
  as_KDKS00    in    char,     --科室名称 ‘-1’:全部
  --as_TJFS00    in    char,     --统计方式 0：正常工作日 1：节假日
  as_YYID00    in    char,     --医院ID -1：全院
  ad_ID0000    out   number,   --统计报表对应的ID
  as_YHMSG0    out   varchar,  --存储过程提示的错误信息
  as_SYSMSG    out   varchar   --系统提示的错误信息
)
as
--MODIFY HISTORY
--date        person     comments
--2011.07.13  zhangyc    create; by MZSF-20110704-001
lv_KDYS00  SF_FYMX00.KDYS00%TYPE;
lv_FPBH00  BM_FPXM00.bh0000%TYPE;
lv_HJJE00  SF_FYMX00.HJJE00%TYPE;
lv_HJJE01  SF_FYMX00.HJJE00%TYPE;
lv_YSPQLB  bm_ygbm00.YSPQLB%TYPE;
lv_oldid0  BM_YYSFTJ.ID0000%TYPE;

 CURSOR CUR_SF_FYJE00 IS
 Select BMBH00,BMMC00,SFXM77,sum(XMHJ00)XMHJ00 from BM_YYSFTJ
   where ID0000=ad_ID0000 and SFXM99=0
 group by BMBH00,BMMC00,SFXM77;
 --统计医生 
 cursor CUR_SF_TJYS00 is 
 select distinct BMBH00 KDYS00 from BM_YYSFTJ where ID0000=ad_ID0000;

begin
 --节假日	
 SP_SF_CWJJSHTJ(as_KSRQ00,as_JSRQ00,as_YSLB00,as_KDKS00,'1',as_YYID00,ad_ID0000,as_YHMSG0,as_SYSMSG);
 IF as_YHMSG0<>'' then
  return;
 end if;
 	
 lv_oldid0:=ad_ID0000;
 --工作日
 SP_SF_CWJJSHTJ(as_KSRQ00,as_JSRQ00,as_YSLB00,as_KDKS00,'0',as_YYID00,ad_ID0000,as_YHMSG0,as_SYSMSG);
 IF as_YHMSG0<>'' then
  return;
 end if;
 
 
 --删除合计数据
 delete from BM_YYSFTJ where ID0000=ad_ID0000 and SFXM201>0;
 delete from BM_YYSFTJ where ID0000=lv_oldid0 and SFXM201>0;
 
 --将节假日工作量和正常日工作日合并
 update BM_YYSFTJ a SET (a.SFXM03,a.SFXM04,a.SFXM13,a.SFXM14,a.SFXM23,a.SFXM24,a.SFXM33,a.SFXM34,a.SFXM43,a.SFXM44,a.SFXM53,a.SFXM54,a.SFXM63,a.SFXM64,a.SFXM73,a.SFXM74,a.SFXM83,a.SFXM84,a.SFXM113,a.SFXM114,a.XMHJ00)=
               (SELECT   b.SFXM01,b.SFXM02,b.SFXM11,b.SFXM12,b.SFXM21,b.SFXM22,b.SFXM31,b.SFXM32,b.SFXM41,b.SFXM42,b.SFXM51,b.SFXM52,b.SFXM61,b.SFXM62,b.SFXM71,b.SFXM72,b.SFXM81,b.SFXM82,b.SFXM111,b.SFXM112,0
                 from BM_YYSFTJ B where ID0000=lv_oldid0 and b.BMBH00=a.BMBH00)
                 where ID0000=ad_ID0000 and exists (select 1 from BM_YYSFTJ c where c.ID0000=lv_oldid0 and c.BMBH00=a.BMBH00 );
                 
 insert into BM_YYSFTJ(ID0000,SFXM200,SFXM201,BMBH00,XMHJ00,SFXM03,SFXM04,SFXM13,SFXM14,SFXM23,SFXM24,SFXM33,SFXM34,SFXM43,SFXM44,SFXM53,SFXM54,SFXM63,SFXM64,SFXM73,SFXM74,SFXM83,SFXM84,SFXM113,SFXM114)
                Select ad_ID0000,SFXM200,SFXM201,BMBH00,0,SFXM01,SFXM02,SFXM11,SFXM12,SFXM21,SFXM22,SFXM31,SFXM32,SFXM41,SFXM42,SFXM51,SFXM52,SFXM61,SFXM62,SFXM71,SFXM72,SFXM81,SFXM82,SFXM111,SFXM112
                from BM_YYSFTJ a where ID0000=lv_oldid0 and not exists (select 1 from BM_YYSFTJ c where c.ID0000=ad_ID0000 and c.BMBH00=a.BMBH00);
                 
 update BM_YYSFTJ set XMHJ00=NVL(SFXM111,0)+NVL(SFXM112,0)+NVL(SFXM113,0)+NVL(SFXM114,0) where ID0000=ad_ID0000;
 
 --插入科室合计
  insert INTO BM_YYSFTJ (ID0000,BMBH00,XMHJ00,SFXM200,SFXM201,SFXM01,SFXM02,SFXM11,SFXM12,SFXM21,SFXM22,SFXM31,SFXM32,SFXM41,SFXM42,SFXM51,SFXM52,SFXM61,
                          SFXM62,SFXM71,SFXM72,SFXM81,SFXM82,SFXM111,SFXM112,
                          SFXM03,SFXM04,SFXM13,SFXM14,SFXM23,SFXM24,SFXM33,SFXM34,SFXM43,SFXM44,SFXM53,SFXM54,SFXM63,SFXM64,SFXM73,SFXM74,SFXM83,SFXM84,SFXM113,SFXM114
                          )
  Select ad_ID0000,0,sum(XMHJ00),SFXM200,1,SUM(SFXM01),SUM(SFXM02),SUM(SFXM11),SUM(SFXM12),SUM(SFXM21),SUM(SFXM22),SUM(SFXM31),SUM(SFXM32),
         SUM(SFXM41),SUM(SFXM42),SUM(SFXM51),SUM(SFXM52),SUM(SFXM61),SUM(SFXM62),SUM(SFXM71),SUM(SFXM72),SUM(SFXM81),SUM(SFXM82),SUM(SFXM111),
         SUM(SFXM112),SUM(SFXM03),SUM(SFXM04),SUM(SFXM13),SUM(SFXM14),SUM(SFXM23),SUM(SFXM24),SUM(SFXM33),SUM(SFXM34),SUM(SFXM43),SUM(SFXM44),
         SUM(SFXM53),SUM(SFXM54),SUM(SFXM63),SUM(SFXM64),SUM(SFXM73),SUM(SFXM74),SUM(SFXM83),SUM(SFXM84),SUM(SFXM113),SUM(SFXM114)
  from BM_YYSFTJ where ID0000=ad_ID0000 AND BMBH00>0 GROUP BY SFXM200;
  
  --插入总合计
  
  insert INTO BM_YYSFTJ (ID0000,BMBH00,XMHJ00,SFXM200,SFXM201,SFXM01,SFXM02,SFXM11,SFXM12,SFXM21,SFXM22,SFXM31,SFXM32,SFXM41,SFXM42,SFXM51,SFXM52,SFXM61,
                          SFXM62,SFXM71,SFXM72,SFXM81,SFXM82,SFXM111,SFXM112,
                          SFXM03,SFXM04,SFXM13,SFXM14,SFXM23,SFXM24,SFXM33,SFXM34,SFXM43,SFXM44,SFXM53,SFXM54,SFXM63,SFXM64,SFXM73,SFXM74,SFXM83,SFXM84,SFXM113,SFXM114)
  Select ad_ID0000,0,sum(XMHJ00),0,2,SUM(SFXM01),SUM(SFXM02),SUM(SFXM11),SUM(SFXM12),SUM(SFXM21),SUM(SFXM22),SUM(SFXM31),SUM(SFXM32),
         SUM(SFXM41),SUM(SFXM42),SUM(SFXM51),SUM(SFXM52),SUM(SFXM61),SUM(SFXM62),SUM(SFXM71),SUM(SFXM72),SUM(SFXM81),SUM(SFXM82),SUM(SFXM111),
         SUM(SFXM112),
         SUM(SFXM03),SUM(SFXM04),SUM(SFXM13),SUM(SFXM14),SUM(SFXM23),SUM(SFXM24),SUM(SFXM33),SUM(SFXM34),SUM(SFXM43),SUM(SFXM44),
         SUM(SFXM53),SUM(SFXM54),SUM(SFXM63),SUM(SFXM64),SUM(SFXM73),SUM(SFXM74),SUM(SFXM83),SUM(SFXM84),SUM(SFXM113),SUM(SFXM114)
  from BM_YYSFTJ where ID0000=ad_ID0000 AND SFXM201=0;
 
 
--commit;
     EXCEPTION
      WHEN OTHERS THEN
                as_YHMSG0:='出错原因不详,请记录此信息并和系统管理员联系！';
                as_SYSMSG:=substr(SQLERRM||'执行存储过程错误:SP_SF_CWJJSHTJ('||as_KSRQ00||','||as_JSRQ00|| ','||as_YSLB00||','||as_KDKS00||
                'ad_ID0000,as_YHMSG0,as_SYSMSG',1,150);
		ROLLBACK;

end;
/
show error;
--%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%