CREATE OR REPLACE PROCEDURE SP_ZY_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执行科室 2医生所属科室
     ad_ID0000    OUT  NUMBER,
     as_YHMSG0    OUT  VARCHAR,
     as_SYSMSG    OUT  VARCHAR
)
-- MODIFICATION HISTORY
-- Person      Date        Comments
-- zhangyc     2011.11.01  create;
-- zhangyc      2011.11.24  增加按医生所属科室统计 by ZYSF-20111118-002
-- liuj        2012.10.19  修改视图VW_SF_CWZBFL为VW_ZY_CWZBFL for  XMGL-20120613-001
-- liuj        2012.11.20  财务独立核算项目对应门诊住院分开并解决住院医生收入汇总报表报错问题 for XMGL-20121109-001
-- liuj        2012.11.29  修改独立核算数据出不来问题并归档 for ZYSF-20121129-003
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);
   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  and b.bh0000 in (select bh0000 from  bm_cwxmbm where zblb00='2')
     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  ZY_FYMX00 A,VW_BM_YJHSXM B,BM_YYSFXM C,ZY_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 e.bh0000 in (select bh0000 from  bm_cwxmbm where zblb00='3')
                        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;


     CURSOR  CUR_SF_DLHSXM_YSKS00  IS

          select DECODE(NVL(A.YSKS00,0),0,F.BMBH00,A.YSKS00)YSKS00, A.KDYS00,E.BH0000,SUM(A.HJJE00)HJJE00
                   from  ZY_FYMX00 A,VW_BM_YJHSXM B,BM_YYSFXM C,ZY_BRFY00 D,BM_CWXMDY E,BM_YGBM00 F
                        where A.XMBH00 = C.SFXMID
                        and  B.HSXMID = C.HSXMID
                        and  A.DJH000 = D.DJH000
                        and  C.SFXMID=E.HSXMID and e.bh0000 in (select bh0000 from  bm_cwxmbm where zblb00='3')
                        and  A.KDYS00=F.YGBH00
                        and  D.CZRQ00 >= AS_KSRQ00
                        and  D.CZRQ00 <= AS_JSRQ00
                        and  (DECODE(NVL(A.YSKS00,0),0,F.BMBH00,A.YSKS00)=AS_KDKS00 OR AS_KDKS00='-1')
                        and(A.KDYS00 = AS_KDYS00 OR AS_KDYS00 ='-1')
           group by DECODE(NVL(A.YSKS00,0),0,F.BMBH00,A.YSKS00),A.KDYS00,E.BH0000
           order by DECODE(NVL(A.YSKS00,0),0,F.BMBH00,A.YSKS00),A.KDYS00;

BEGIN
    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_ZY_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=3
     order by bh0000;


    --插入标题列
  --  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

   If  as_KSFS00 in ('0','1') then

    insert into BM_GHKSTJ(ID0000,GHKS01,GHKS02,GHKS03,XMHJ00,GHRQ00,DYID00)
    select ad_ID0000,A.KDKS00,a.KDYS00,C.HSXMID,sum(A.HJJE00)HJJE00,0,1
                   from  ZY_FYMX00 A,VW_BM_YJHSXM B,BM_YYSFXM C,ZY_BRFY00 D
                        where A.XMBH00 = C.SFXMID
                        and  B.HSXMID = C.HSXMID
                        and  A.DJH000 = D.DJH000
                        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;
    end if;

     If  as_KSFS00 in ('2') then

    insert into BM_GHKSTJ(ID0000,GHKS01,GHKS02,GHKS03,XMHJ00,GHRQ00,DYID00)
    select ad_ID0000,DECODE(NVL(A.YSKS00,0),0,F.BMBH00,A.YSKS00)YSKS00,a.KDYS00,C.HSXMID,sum(A.HJJE00)HJJE00,0,1
                   from  ZY_FYMX00 A,VW_BM_YJHSXM B,BM_YYSFXM C,ZY_BRFY00 D,BM_YGBM00 F
                        where A.XMBH00 = C.SFXMID
                        and  B.HSXMID = C.HSXMID
                        and  A.DJH000 = D.DJH000
                        and  A.KDYS00=F.YGBH00
                        and  D.CZRQ00 >= as_KSRQ00
                        and  D.CZRQ00 <= as_JSRQ00
                        and  (DECODE(NVL(A.YSKS00,0),0,F.BMBH00,A.YSKS00)=AS_KDKS00 OR AS_KDKS00='-1')
                        and(a.KDYS00 = as_KDYS00 or as_KDYS00 ='-1')
           group by  ad_ID0000,DECODE(NVL(A.YSKS00,0),0,F.BMBH00,A.YSKS00),a.KDYS00,C.hsxmid;
    end if;


   ---插入费用列顺序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 A.GHKS01,A.GHKS02,A.XMHJ00
         from
       (
       select GHKS01,-1 GHKS02,sum(XMHJ00) XMHJ00 from BM_GHKSTJ
         where ID0000=ad_ID0000  and DYID00=1
        group by GHKS01
       union all
         select GHKS01,GHKS02,sum(XMHJ00) XMHJ00 from BM_GHKSTJ
        where ID0000=ad_ID0000  and DYID00=1
        group by GHKS01,GHKS02
        )A,BM_BMBM00 B Where A.GHKS01=B.BMBH00 order by B.BH0000,GHKS01,GHKS02
       );


  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;
  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;


  If  as_KSFS00 in ('0','1') then

   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;

  end if;

  If as_KSFS00 in ('2') then

   for sfyDLHS in CUR_SF_DLHSXM_YSKS00 loop
       ls_kdks00:=sfyDLHS.YSKS00;
       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;

  end if;


  begin
    select NVL(XMHJ00,0) into  ls_ypje00  from   BM_GHKSTJ where ID0000 =ad_ID0000 and DYID00=99 and BEIZHU='药费合计';
   EXCEPTION
      WHEN OTHERS THEN
      ls_ypje00:=0;
  end;
  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;

    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 ');
    j:=1;
    while j<=400 loop
       if j<100 then
          ls_xh0000:=lpad(to_char(j),2,'0');
       else ls_xh0000:=to_char(j);
       end if;
       execute immediate 'select NVL(GHKS'||ls_xh0000||',0)   from   BM_GHKSTJ where    ID0000 ='||ad_ID0000||' and DYID00=999 and GHRQ00=''9999'''  into ls_yphj00;
       if ls_yphj00=0 then
         ls_yphj00:=1;
       end if;
       SP_EXECUTE_SQL('UPDATE BM_GHKSTJ SET GHKS'||ls_xh0000||'= (select nvl(GHKS'||ls_xh0000||',0)*100/'||to_char(ls_yphj00)||' from BM_GHKSTJ  where  ID0000='||ad_ID0000||' and DYID00=99 and BEIZHU=''药费合计'')  where ID0000='||to_char(ad_ID0000)||' and GHRQ00=''0.1'' and DYID00=9999 ');
       j:=j+1;
    end loop;

    /*
    select min(to_number(GHRQ00)),max(to_number(GHRQ00)) into minNum,maxNum from  BM_GHKSTJ where   DYID00=3 AND  ID0000 =ad_ID0000;
    J:=minNum;
    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:='出错原因不详,请记录此信息并和系统管理员联系！';
                as_SYSMSG:=substr(SQLERRM||'执行存储过程错误:SP_SF_YSGZLHZ('||as_KSRQ00||','||as_JSRQ00||'ad_ID0000,as_YHMSG0,as_SYSMSG',1,150);
    rollback;
end;