-- Start of DDL Script for Procedure SD_HOSPITAL.SP_YS_HZDLWH
-- Generated 2013/8/29 15:11:27 from SD_HOSPITAL@(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.9.202.9)(PORT = 1521))) (CONNECT_DATA = (SID = dyhis1))) 

CREATE OR REPLACE 
PROCEDURE sp_ys_hzdlwh
--过程说明，源病人插到目标病人的前面
(
   psoid00      in      number     ,         --源ID0000
   pdeid00      in      number     ,         --目标ID0000
   Pczy000    	in  	number     ,         --操作员id号
   Pzxjg00	out     varchar2   ,         --执行结果提示
   Pzxbz00	out     number     ,         --传出参数说明
   Pcommit      in      varchar2 default 'N' --是否提交
)
AS
-- MODIFICATION HISTORY
-- Person      Date        Comments
-- csf         20080908    省二要求允许调整优待病人的候诊顺序
-- dsm         20120328    MZHS_YDBRSFYXTD的控制有问题，为N的还是可以拖动 for MZHS-20120314-001  
-- ----------  ----------  ---------------------------------------------------------------------------------
   Vsysdate      date;                       --日期变量
   Vcounter      number(10);                 --计数器变量
   Vparams       varchar2(255);              --参数变量
   VsoHzbr       sf_hzbr00%rowtype;          --源候诊队列
   VdeHzbr       sf_hzbr00%rowtype;          --目标候诊队列
   Vlastxh       sf_hzbr00.dlxh00%type;      --目标病人的前一个病人的队列序号
   Vcurrxh       sf_hzbr00.dlxh00%type;      --源病人的队列序号
   Vpthzlb       bm_hzlb00.hzlb00%type;      --普通病人的候诊类别
   VYDBRSFYXTD   xt_xtcs00.value0%type;      --优待病人是否允许拖动 默认为N 省二为Y
   Verrmsg       varchar2(255);              --错误提示变量
   Ecustom	 exception;                  --错误变量
   --往后挪              
   Cursor C_bkhzbr is select id0000,dlxh00,HZZT00 from sf_hzbr00 
          where  ghys00=Vsohzbr.ghys00 and jzks00=Vsohzbr.jzks00 and HZZT00<>'Z' and
                 hzqsrq=to_char(sysdate,'yyyymmdd') and dlxh00>0 and 
                 dlxh00>Vsohzbr.dlxh00 and dlxh00<=Vdehzbr.dlxh00 order by dlxh00;
   --往前挪
   Cursor C_fohzbr is select id0000,dlxh00,HZZT00 from sf_hzbr00 
        where  ghys00=Vsohzbr.ghys00 and jzks00=Vsohzbr.jzks00 and HZZT00<>'Z' and
               hzqsrq=to_char(sysdate,'yyyymmdd') and dlxh00>0 and 
               dlxh00>=Vdehzbr.dlxh00 and dlxh00<Vsohzbr.dlxh00 order by dlxh00 desc;
   
begin
  Vcounter:=0;
  Vsysdate:=sysdate;--'0'候诊'1'应诊'2'错诊'3'诊结'4'叫诊
  Vparams :='Sp_Ys_hzdlwh('||nvl(to_char(Psoid00),'null')||','||nvl(to_char(Pdeid00),'null')||','||
             nvl(to_char(Pczy000),'null')||',pzxjg00,pzxbz00,'''||Pcommit||''')';
  /*取参数 优待病人是否允许拖动 默认为N 省二为Y*/
  begin
  	select trim(value0) into VYDBRSFYXTD from xt_xtcs00 where name00='MZHS_YDBRSFYXTD';
  exception
    when others then
      VYDBRSFYXTD:='N';
  end;           
  if nvl(psoid00,0)=0 then
     Verrmsg:='没有指定源候诊病人';
     Raise Ecustom;
  end if;
  select * into Vsohzbr from sf_hzbr00 where id0000=psoid00;
  --判定是否普通病人
  select min(hzlb00) into Vpthzlb from bm_hzlb00;
  --2006.06.28要求对续诊过诊的普通病人，允许进行调位置，变成普通候诊病人,只能往后挪
  --2006.10.30继2006.06.28，可以把这类病人往前/后挪。
  if Vsohzbr.hzlb00<=Vpthzlb and Vsohzbr.hzzt00 not in ('2','5') then
     Verrmsg:='不能调整普通病人的候诊队列';
     Raise Ecustom;
  elsif Vsohzbr.hzlb00<=Vpthzlb and Vsohzbr.hzzt00 in ('2','5') and nvl(pdeid00,0)>0 then --对续诊过诊的普通病人，允许进行调位置，变成普通候诊病人
     select * into Vdehzbr from sf_hzbr00 where id0000=pdeid00;
     select count(*) into Vcounter from sf_hzbr00 where id0000=pdeid00 and dlxh00<Vsohzbr.dlxh00;
     if Vcounter<=0 then
        --把源病人至目标病人的候诊序号往前挪  
        Vlastxh:=Vsohzbr.dlxh00;
        for hzbr in C_bkhzbr loop
              if hzbr.HZZT00 IN ('2','5','0') THEN
                Update sf_hzbr00 set dlxh00=Vlastxh where id0000=hzbr.id0000;
                Vlastxh:= hzbr.dlxh00;
              END IF;
        end loop;
     else
        --把源病人至目标病人的候诊序号往后挪  
        Vlastxh:=Vsohzbr.dlxh00;
        for hzbr in C_fohzbr loop
          IF hzbr.HZZT00 IN ('2','5','0') THEN
            Update sf_hzbr00 set dlxh00=Vlastxh where id0000=hzbr.id0000;
            Vlastxh:= hzbr.dlxh00;
          END IF;
        end loop;
     end if;  
     --把病人状态改为正常
     update sf_hzbr00 set dlxh00=Vlastxh,hzzt00='0' ,BZ0000=DECODE(Vsohzbr.HZZT00,'2','过诊','5','续诊','') where id0000=Vsohzbr.id0000;
     if pcommit='Y' then
        commit;
     end if;
     return; 
  end if;
  
  --没有指定目标侯诊病人(新安排的),插入到该候诊时间段普通病人的后面
  --判定源病人是否该特殊候诊队列的No1
  if nvl(pdeid00,0)=0 then --
    --Verrmsg:='没有指定目标侯诊病人';
    --Raise Ecustom;
    select min(hzxh00) into Vcounter from sf_hzbr00
    where  ghys00=Vsohzbr.ghys00 and jzks00=Vsohzbr.jzks00 and HZZT00<>'Z' and
           hzqsrq=to_char(sysdate,'yyyymmdd') and nvl(dlxh00,0)=0 and hzlb00=Vsohzbr.hzlb00;
    if Vcounter<>Vsohzbr.hzxh00 then
       Verrmsg:='必须先安排该特殊候诊病人队列的第一位';
       Raise Ecustom;
    end if;
    --排在相应候诊段的最后一位
    select max(DLXH00) into Vcounter from sf_hzbr00
    where  ghys00=Vsohzbr.ghys00 and jzks00=Vsohzbr.jzks00 and HZZT00<>'Z' and
           hzqsrq=to_char(sysdate,'yyyymmdd') and dlxh00>0 and 
           hzqsrq||hzqssj=Vsohzbr.hzqsrq||Vsohzbr.hzqssj;
    if Vcounter>0 then
      Update sf_hzbr00 set DLXH00=Vcounter+1 where id0000=Vsohzbr.id0000;
    else--该候诊段没有病人，排到下一个候诊段之前
      select min(DLXH00) into Vcounter from sf_hzbr00
      where  ghys00=Vsohzbr.ghys00 and jzks00=Vsohzbr.jzks00 and HZZT00<>'Z' and
             hzqsrq=to_char(sysdate,'yyyymmdd') and dlxh00>0 and 
             hzqsrq||hzqssj>Vsohzbr.hzqsrq||Vsohzbr.hzqssj;
      if Vcounter>0 then
         Update sf_hzbr00 set DLXH00=Vcounter-1 where id0000=Vsohzbr.id0000;
      else--排到全队最后
        select max(DLXH00) into Vcounter from sf_hzbr00
        where  ghys00=Vsohzbr.ghys00 and jzks00=Vsohzbr.jzks00 and HZZT00<>'Z' and
               hzqsrq=to_char(sysdate,'yyyymmdd') and dlxh00>0;
        Update sf_hzbr00 set DLXH00=Nvl(Vcounter,0)+1 where id0000=Vsohzbr.id0000;
      end if;  
    end if;  
    --select max() from sf_hzbr00 where hzqsrq=to_char(sysdate,'yyyymmdd') 
  else --有指定目标病人 
    --判定源病人是否排队
    select * into Vdehzbr from sf_hzbr00 where id0000=pdeid00;
    if nvl(Vsohzbr.dlxh00,0)=0 then--源病人未排队
      --同候诊段判定
      if Vdehzbr.hzqsrq||Vdehzbr.hzqssj<>Vsohzbr.hzqsrq||Vsohzbr.hzqssj then
         Verrmsg:='优待病人只能插队到同一个候诊段';
         --Raise Ecustom; 2004.06.25不作限制
      end if;
      --同类队列秩序判定
      if VYDBRSFYXTD='N' then --优待病人是否允许拖动 默认为N 省二为Y
        select count(*) into Vcounter from sf_hzbr00 
        where  ghys00=Vsohzbr.ghys00 and jzks00=Vsohzbr.jzks00 and HZZT00<>'Z' and
             hzqsrq=to_char(sysdate,'yyyymmdd') and dlxh00>0 and 
             hzqsrq||hzqssj=Vsohzbr.hzqsrq||Vsohzbr.hzqssj   and
             hzlb00=Vsohzbr.hzlb00 and dlxh00>=Vdehzbr.dlxh00 and 
             hzxh00<Vsohzbr.hzxh00;
        if Vcounter>0 then
           Verrmsg:='优待病人插队时必须遵循同等待遇病人的先后顺序';
           Raise Ecustom;
        end if;
      end if;
      --取预分配的队列序号
      Vlastxh:=0;
      select count(*) into Vcounter from sf_hzbr00 where DLXH00<Vdehzbr.DLXH00 and hzqsrq=to_char(sysdate,'yyyymmdd') and HZZT00<>'Z';
      if Vcounter>0 then --目标病人有前一个病人
         select max(DLXH00) into Vlastxh from sf_hzbr00 where DLXH00<Vdehzbr.DLXH00 and hzqsrq=to_char(sysdate,'YYYYMMDD') and HZZT00<>'Z';
      end if;
      Vcurrxh:=Vlastxh+1;
      if Vcurrxh<Vdehzbr.dlxh00 then
         Update sf_hzbr00 set dlxh00=Vcurrxh where id0000=Vsohzbr.id0000;
      else
         --队列序号都往后顺延1
         Update sf_hzbr00 set dlxh00=dlxh00+1
         where  dlxh00>Vlastxh and Vlastxh+rownum=dlxh00 and ghys00=Vsohzbr.ghys00 and 
                jzks00=Vsohzbr.jzks00 and hzqsrq=to_char(sysdate,'yyyymmdd') and dlxh00>0 and HZZT00<>'Z';
         Update sf_hzbr00 set dlxh00=Vcurrxh where id0000=Vsohzbr.id0000;
      end if;
    else--源病人已经排队，做队列调整
      --同候诊段判定
      if Vdehzbr.hzqsrq||Vdehzbr.hzqssj<>Vsohzbr.hzqsrq||Vsohzbr.hzqssj then
         Verrmsg:='优待病人只能在同一个候诊段内调整';
         --Raise Ecustom;  2004.06.25不作限制
      end if;
      --同类队列秩序判定
      
      if Vsohzbr.dlxh00<Vdehzbr.dlxh00 then--往后挪
        if VYDBRSFYXTD='N' then --优待病人是否允许拖动 默认为N 省二为Y
          select count(*) into Vcounter from sf_hzbr00 
          where  ghys00=Vsohzbr.ghys00 and jzks00=Vsohzbr.jzks00 and HZZT00<>'Z' and
               hzqsrq=to_char(sysdate,'yyyymmdd') and dlxh00>0 and 
               hzqsrq||hzqssj=Vsohzbr.hzqsrq||Vsohzbr.hzqssj   and
               hzlb00=Vsohzbr.hzlb00 and dlxh00>Vsohzbr.dlxh00 and 
               dlxh00<=Vdehzbr.dlxh00; --and hzxh00>Vsohzbr.hzxh00;
          if Vcounter>0 then
             Verrmsg:='优待病人插队时必须遵循同等待遇病人的先后顺序';
             Raise Ecustom;
          end if;
        end if;
        Vlastxh:=Vsohzbr.dlxh00;
        for hzbr in C_bkhzbr loop
           Update sf_hzbr00 set dlxh00=Vlastxh where id0000=hzbr.id0000;
           Vlastxh:= hzbr.dlxh00;
        end loop;
        update sf_hzbr00 set dlxh00=Vlastxh where id0000=Vsohzbr.id0000;
      else--往前挪
        if VYDBRSFYXTD='N' then --优待病人是否允许拖动 默认为N 省二为Y
          select count(*) into Vcounter from sf_hzbr00 
          where  ghys00=Vsohzbr.ghys00 and jzks00=Vsohzbr.jzks00 and 
               hzqsrq=to_char(sysdate,'yyyymmdd') and dlxh00>0 and 
               hzqsrq||hzqssj=Vsohzbr.hzqsrq||Vsohzbr.hzqssj   and
               hzlb00=Vsohzbr.hzlb00 and dlxh00>=Vdehzbr.dlxh00 and 
               dlxh00<Vsohzbr.dlxh00 --and hzxh00<Vsohzbr.hzxh00 
               and HZZT00<>'Z';
          if Vcounter>0 then
             Verrmsg:='优待病人插队时必须遵循同等待遇病人的先后顺序';
             Raise Ecustom;
          end if;
        end if;
        
        Vlastxh:=Vsohzbr.dlxh00;
        for hzbr in C_fohzbr loop
           Update sf_hzbr00 set dlxh00=Vlastxh where id0000=hzbr.id0000;
           Vlastxh:= hzbr.dlxh00;
        end loop;
        update sf_hzbr00 set dlxh00=Vlastxh where id0000=Vsohzbr.id0000;
      end if;
    end if;
  end if;  
  update sf_hzbr00 set hzzt00='0' ,BZ0000=DECODE(Vsohzbr.HZZT00,'2','过诊','5','续诊','')
  where id0000=Vsohzbr.id0000 and hzzt00 in ('2','5');
  if pcommit='Y' then
    commit;
  end if;
exception
  when no_data_found then
    raise_application_error(-20001,substrb('数据没有找到！*'||sqlerrm||','||Vparams,1,220));
  when Ecustom       then
    raise_application_error(-20010,substrb(Verrmsg||'！*'||Vparams,1,220));
  when others        then
    raise_application_error(-20002,substrb(nvl(sqlerrm, '原因不明出错')||'！*'||Vparams,1,220));
end ;
/



-- End of DDL Script for Procedure SD_HOSPITAL.SP_YS_HZDLWH