CREATE OR REPLACE PROCEDURE SP_SF_YSGZLHZ
(
     as_KSRQ00    IN   CHAR,
     as_JSRQ00    IN   CHAR,
     as_KDKS00    IN   CHAR,
     as_KDYS00    in   CHAR,
     as_KSFS00    in   CHAR default '0',  --0开单科室 1执行科室
     ad_ID0000    OUT  NUMBER,
     as_YHMSG0    OUT  VARCHAR,
     as_SYSMSG    OUT  VARCHAR
)
-- MODIFICATION HISTORY
-- Person      Date        Comments
-- zhangyc     2011.09.01  create;
-- zhangyc     2011.09.30  增加入参as_KSFS00 按开单科室或是执行科室  
as
       ls_count0  number(5);
       ls_temp00  number(5);
       ls_temp01  number(5);
       ls_XMHJ00  number(10,2);
       
       ls_kdks00  number(5);
       ls_kdys00  number(5);
       ls_xh0000  varchar2(10);
       ls_xh0001  varchar2(10);
       ls_xmje00  number(10,2);
       ls_bh0000  varchar2(20);
       minNum number(5);
       maxNum number(5);
       j      number(5);
       ls_ypje00 number(10,2);
       ls_yphj00 number(10,2);
       ls_ypbl00 number(10,2);
       ls_xh0002  varchar2(10);
       ls_btxh00  varchar2(10);
       ls varchar2(100);
   CURSOR  CUR_SF_FYTJ00  IS  
   select GHRQ00,GHKS01,GHKS02,XMHJ00  from BM_GHKSTJ  where ID0000=ad_ID0000  and DYID00=3 
   order by GHRQ00;
   
   CURSOR  CUR_SF_FYMX00  IS  
      select sum(XMHJ00)XMHJ00,GHKS01,GHKS02,nvl(b.bh0000,'10')bh0000 from BM_GHKSTJ a,BM_CWXMDY b  
     where  a.GHKS03=b.hsxmid(+) and  ID0000=ad_ID0000  and DYID00=1  
     group by GHKS01,GHKS02,b.bh0000
     order by GHKS01,GHKS02;
     
     CURSOR  CUR_SF_DLHSXM  IS
     
     select A.KDKS00,a.KDYS00,e.bh0000,sum(A.HJJE00)HJJE00
                   from  SF_FYMX00 A,VW_BM_YJHSXM B,BM_YYSFXM C,SF_BRFY00 D,BM_CWXMDY e 
                        where A.XMBH00 = C.SFXMID
                        and  B.HSXMID = C.HSXMID
                        and  A.DJH000 = D.DJH000
                        and  c.sfxmid=e.hsxmid
                        and  D.CZRQ00 >= as_KSRQ00
                        and  D.CZRQ00 <= as_JSRQ00
                        and  (((a.kdks00 = as_KDKS00 and as_KSFS00='0') or (a.ZXKS00 = as_KDKS00 and as_KSFS00='1')) or as_KDKS00 ='-1')
                        and(a.KDYS00 = as_KDYS00 or as_KDYS00 ='-1')
           group by a.KDYS00,A.KDKS00,e.bh0000
           ORDER BY A.KDKS00,a.KDYS00;
           
 
BEGIN
ls :='1@';
    ls_count0:=0;
    Select  SQ_BM_YYSFTJ_ID0000.nextval  into  ad_ID0000  from  dual;
    
    --插入门诊汇总项目
    insert into BM_GHKSTJ(ID0000,GHRQ00,BEIZHU,DYID00)
    select ad_ID0000,BH0000,XMMC00,99 from VW_SF_CWZBFL;
    --插入合计项目
    insert into BM_GHKSTJ(ID0000,GHRQ00,BEIZHU,DYID00)
    select ad_ID0000,9999,'合计',999 from dual;
    
    --插入独立核算项目
    insert into BM_GHKSTJ(ID0000,GHRQ00,BEIZHU,DYID00)  ---空白行 
     values(ad_ID0000,'0.0','',9999);
     
        insert into BM_GHKSTJ(ID0000,GHRQ00,BEIZHU,DYID00)
    select ad_ID0000,'0.1','药品比率%',9999 from dual;
     insert into BM_GHKSTJ(ID0000,GHRQ00,BEIZHU,DYID00)  --控制行 
     values(ad_ID0000,'0.2','控制',9999);
     
    insert into BM_GHKSTJ(ID0000,GHRQ00,BEIZHU,DYID00)
     select ad_ID0000,BH0000,XMMC00,9999  from  BM_CWXMBM where ZBLB00=1 
     order by bh0000; 
ls :='2@';
    
    --插入标题列
  --  insert into BM_GHKSTJ(ID0000,GHRQ00,DYID00)
  --  select ad_ID0000,'标题列',8 from dual;
    --插入显示内容 0：科室 1：医生
   -- insert into BM_GHKSTJ(ID0000,GHRQ00,DYID00)
    --select ad_ID0000,'显示内容',9 from dual;
    
    ---插入费用金额GHKS99=1
    insert into BM_GHKSTJ(ID0000,GHKS01,GHKS02,GHKS03,XMHJ00,GHRQ00,DYID00,BEIZH3)
    select ad_ID0000,A.KDKS00,a.KDYS00,C.HSXMID,sum(A.HJJE00)HJJE00,0,1,E.BH0000
                   from  SF_FYMX00 A,VW_BM_YJHSXM B,BM_YYSFXM C,SF_BRFY00 D,bm_bmbm00 e
                        where A.XMBH00 = C.SFXMID
                        and  B.HSXMID = C.HSXMID
                        and  A.DJH000 = D.DJH000
                         and  A.KDKS00=E.BMBH00 
                        and  D.CZRQ00 >= as_KSRQ00
                        and  D.CZRQ00 <= as_JSRQ00
                        and(((a.kdks00 = as_KDKS00 and as_KSFS00='0') or (a.ZXKS00 = as_KDKS00 and as_KSFS00='1')) or as_KDKS00 ='-1')
                        and(a.KDYS00 = as_KDYS00 or as_KDYS00 ='-1') 
           group by  ad_ID0000,a.KDYS00,A.KDKS00,C.hsxmid,E.BH0000;  
           ls :='3@';
---插入费用列顺序GHKS99=3          
insert into BM_GHKSTJ(ID0000,GHRQ00,GHKS01,GHKS02,XMHJ00,DYID00)
   select ad_ID0000,decode(length(rownum),1,lpad(to_char(rownum),2,'0'),to_char(rownum))xh000,GHKS01,GHKS02,XMHJ00,3
  from
(
select GHKS01,GHKS02,XMHJ00,BEIZH3
from
(
select GHKS01,-1 GHKS02, BEIZH3,sum(XMHJ00) XMHJ00 from BM_GHKSTJ  
where ID0000=ad_ID0000  and DYID00=1
group by GHKS01,BEIZH3
union all
select GHKS01,GHKS02,BEIZH3,sum(XMHJ00) XMHJ00 from BM_GHKSTJ  
where ID0000=ad_ID0000  and DYID00=1
group by GHKS01,GHKS02,BEIZH3
) order by BEIZH3,GHKS01,GHKS02
);

ls :='4@';
 for sfymx in CUR_SF_FYMX00 loop
       
       
       ls_kdks00:=sfymx.GHKS01;
       ls_kdys00:=sfymx.GHKS02;
       ls_bh0000:=sfymx.bh0000;
       ls_xmje00:=sfymx.XMHJ00;
     select GHRQ00 into  ls_xh0000 from BM_GHKSTJ where ID0000=ad_ID0000  and DYID00=3 and GHKS01=ls_kdks00 and GHKS02=ls_kdys00; --取医生  GHRQ00
     select GHRQ00 into  ls_xh0001 from BM_GHKSTJ where ID0000=ad_ID0000  and DYID00=3 and GHKS01=ls_kdks00 and GHKS02=-1; --取科室  GHRQ00
     
     if length(ls_bh0000)>2 then  
       ---更新科室数据 
       SP_EXECUTE_SQL('UPDATE BM_GHKSTJ SET GHKS'||ls_xh0000||'= '||to_char(ls_xmje00)||',XMHJ00=NVL(XMHJ00,0)+'||to_char(ls_xmje00)||' where ID0000='||to_char(ad_ID0000)||' and GHRQ00='||to_char(ls_bh0000)||' and DYID00=99'); 
     end if;
       --更新各科室下医生数据 
      SP_EXECUTE_SQL('UPDATE BM_GHKSTJ SET GHKS'||ls_xh0000||'= '||'NVL(GHKS'||ls_xh0000||',0)+'||to_char(ls_xmje00)||',XMHJ00=NVL(XMHJ00,0)+'||to_char(ls_xmje00)||' where ID0000='||to_char(ad_ID0000)||' and GHRQ00='||to_char(substr(ls_bh0000,1,2))||' and DYID00=99'); 
     
      if length(ls_bh0000)>2 then
      --更新节点科室数据 
       SP_EXECUTE_SQL('UPDATE BM_GHKSTJ SET GHKS'||ls_xh0001||'= '||'NVL(GHKS'||ls_xh0001||',0)+'||to_char(ls_xmje00)||' where ID0000='||to_char(ad_ID0000)||' and GHRQ00='||to_char(ls_bh0000)||' and DYID00=99'); 
      end if;
      --更新节点各科室下医生数据 
     SP_EXECUTE_SQL('UPDATE BM_GHKSTJ SET GHKS'||ls_xh0001||'= '||'NVL(GHKS'||ls_xh0001||',0)+'||to_char(ls_xmje00)||' where ID0000='||to_char(ad_ID0000)||' and GHRQ00='||to_char(substr(ls_bh0000,1,2))||' and DYID00=99'); 
            
  end loop;
  ls :='5@';
 for sfy in CUR_SF_FYTJ00 loop
    
    --ls_temp01: 0：科室 1：医生
     if sfy.GHKS02=-1 then 
       ls_temp00:=sfy.GHKS01; --科室
       ls_temp01:=0;
       ls_XMHJ00:=sfy.XMHJ00;
     else
       ls_temp00:=sfy.GHKS02; --医生
       ls_temp01:=1;
       ls_XMHJ00:=0;
     end if;
     --分别更新合计，标题列，显示内容
    SP_EXECUTE_SQL('UPDATE BM_GHKSTJ SET GHKS'||sfy.GHRQ00||'= '||to_char(sfy.XMHJ00)||',XMHJ00=NVL(XMHJ00,0)+'||to_char(ls_XMHJ00)||' where ID0000='||to_char(ad_ID0000)||' and DYID00=999'); 
   -- SP_EXECUTE_SQL('UPDATE BM_GHKSTJ SET GHKS'||sfy.GHRQ00||'= '||to_char(ls_temp00)||' where ID0000='||to_char(ad_ID0000)||' and DYID00=8');
   -- SP_EXECUTE_SQL('UPDATE BM_GHKSTJ SET GHKS'||sfy.GHRQ00||'= '||to_char(ls_temp01)||' where ID0000='||to_char(ad_ID0000)||' and DYID00=9');            
end loop;

ls :='6@';

 for sfyDLHS in CUR_SF_DLHSXM loop  
       ls_kdks00:=sfyDLHS.KDKS00;
       ls_kdys00:=sfyDLHS.KDYS00;
       ls_bh0000:=sfyDLHS.bh0000;
       ls_xmje00:=sfyDLHS.HJJE00;
     select GHRQ00 into  ls_xh0000 from BM_GHKSTJ where ID0000=ad_ID0000  and DYID00=3 and GHKS01=ls_kdks00 and GHKS02=ls_kdys00; --取科室  GHRQ00
     select GHRQ00 into  ls_xh0001 from BM_GHKSTJ where ID0000=ad_ID0000  and DYID00=3 and GHKS01=ls_kdks00 and GHKS02=-1; --取科室  GHRQ00
     
     SP_EXECUTE_SQL('UPDATE BM_GHKSTJ SET GHKS'||ls_xh0000||'= '||to_char(ls_xmje00)||',XMHJ00=NVL(XMHJ00,0)+'||to_char(ls_xmje00)||' where ID0000='||to_char(ad_ID0000)||' and GHRQ00='||to_char(ls_bh0000)||' and DYID00=9999'); 
     
     SP_EXECUTE_SQL('UPDATE BM_GHKSTJ SET GHKS'||ls_xh0001||'= '||'NVL(GHKS'||ls_xh0001||',0)+'||to_char(ls_xmje00)||' where ID0000='||to_char(ad_ID0000)||' and GHRQ00='||to_char(ls_bh0000)||' and DYID00=9999'); 

     

end loop;
ls :='7@';
select NVL(XMHJ00,0) into  ls_ypje00  from   BM_GHKSTJ where    ID0000 =ad_ID0000 and DYID00=99 and GHRQ00='01';
    select NVL(XMHJ00,0) into  ls_yphj00  from   BM_GHKSTJ where    ID0000 =ad_ID0000 and DYID00=999 and GHRQ00='9999';
    if  nvl(ls_yphj00,0)=0 then  
      ls_ypbl00:=0;
     else  ls_ypbl00:=ls_ypje00*100/ls_yphj00;
   end if;
   ls :='8@';
    SP_EXECUTE_SQL('UPDATE BM_GHKSTJ SET XMHJ00= '||to_char(ls_ypbl00)||' where ID0000='||to_char(ad_ID0000)||' and GHRQ00=''0.1'' and DYID00=9999 ');    
    select min(to_number(GHRQ00)),max(to_number(GHRQ00)) into minNum,maxNum from  BM_GHKSTJ where   DYID00=3 AND  ID0000 =ad_ID0000;
    J:=minNum;
    ls :='9@';
    while J<=maxNum loop 
    
     if j<10 then 
       ls_xh0002:=lpad(to_char(J),2,'0');
     else
       ls_xh0002:=to_char(J);
     end if;
      SP_EXECUTE_SQL('UPDATE BM_GHKSTJ SET GHKS'||ls_xh0002||'= '||'(nvl((select GHKS'||ls_xh0002||'   from   BM_GHKSTJ where    ID0000 ='||ad_ID0000||' and DYID00=99 and GHRQ00=''01''),0)*100/greatest(nvl((select GHKS'||ls_xh0002||'   from   BM_GHKSTJ where    ID0000 ='||ad_ID0000||' and DYID00=999 and GHRQ00=''9999''),0),1))'||
            ' where ID0000='||to_char(ad_ID0000)||' and GHRQ00=''0.1'' and DYID00=9999');   
       j:=j+1;
    end loop;   

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