-- Start of DDL script for TR_SF_SF_YSGHHB_UPDATE
-- Generated 9-六月-15  2:30:01 pm
-- from fw2-SD_HOSPITAL:1

-- Trigger TR_SF_SF_YSGHHB_UPDATE

CREATE OR REPLACE TRIGGER tr_sf_sf_ysghhb_update
BEFORE UPDATE
ON sf_ysghhb
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
declare
   E_GHXHS0  EXCEPTION;           --挂号限号数
   E_YYXHS0  EXCEPTION;           --预约限号数
   E_GXGHXH  EXCEPTION;           --更新挂号限号数
   Vtmpstr   varchar2(30);        --临时变量
   Vmaxhzh   number(10);          --最大候诊序号
   Vcount0   number(10);          --临时变量
   Vcounter  number(10);          --临时变量
   i         number(10);          --临时变量
   VJHS      varchar2(100);       --存储系统参数MZHS_HBGLJHXH的value0值
-- MODIFICATION HISTORY
-- Person      Date        Comments
-- zhr         2011.01.16  号挂满后，进行加号的，需要补增YS_HBHZXH，停止加号的，YS_HBHZXH加号记录要清理。
-- qks         2011.03.07  加号处理：默认只增加1个; 停止加号：改为删除
-- csf         2012.04.01  加号处理时候 默认增加5个号,因为病人只有转正式挂号时候，系统才会自动再加一个 MZHS-20120401-003
-- dsm         2012.04.26  加号处理时候增加20个号 for MZHS-20120423-001
-- daihq       2012.08.31  加号处理时候增加60个号,增加系统参数MZHS_HBGLJHXH  MZHS-20120829-001
begin
   select nvl(max(trim(VALUE0)),'20') into VJHS from XT_XTCS00 where NAME00='MZHS_HBGLJHXH';
   if (:NEW.SFZJGH='1') and (:NEW.GHYGHS-:NEW.GHYYHS>=:NEW.MRXHS0-:NEW.YYXHS0) then
      :NEW.GHXHS0 := :NEW.GHYGHS-:NEW.GHYYHS+:NEW.YYXHS0+1;
   end if;
   if (:NEW.SFZJGH='1' and :OLD.SFZJGH='0') or (:new.SFZJGH ='1' and :new.GHYGHS<>:old.GHYGHS) then
      --加号处理
      select count(*) into Vcount0 from YS_HBHZXH where GHHBID=:new.GHHBID and SFZJ00='1' and YXBZ00='1' and GHH000 is null;
       if Vcount0<to_number(VJHS) then --5-->20-->60
          select max(HZSJ00),max(HZDXH0),max(HZXH00) into Vtmpstr,Vcounter,Vmaxhzh from YS_HBHZXH where GHHBID=:new.GHHBID;
          if Vmaxhzh>0 then
             for i in 1..to_number(VJHS)-Vcount0 loop
                insert into YS_HBHZXH (ID0000,GHHBID,HZXH00,YYBZ00,HZSJ00,YYRQSJ,SFZJ00,SJHZXH,HZDXH0,YYID00)
                     Values(SQ_YS_HBHZXH_ID0000.nextVal,:new.GHHBID,Vmaxhzh+i,'1',Vtmpstr,null,'1',null,Vcounter,null);
             end loop;
          end if;
       end if;
--         if Vcount0<1 then
--            select max(HZSJ00),max(HZDXH0),max(HZXH00) into Vtmpstr,Vcounter,Vmaxhzh from YS_HBHZXH where GHHBID=:new.GHHBID;
--            if Vmaxhzh>0 then
--               insert into YS_HBHZXH (ID0000,GHHBID,HZXH00,YYBZ00,HZSJ00,YYRQSJ,SFZJ00,SJHZXH,HZDXH0,YYID00)
--                     Values(SQ_YS_HBHZXH_ID0000.nextVal,:new.GHHBID,Vmaxhzh+1,'1',Vtmpstr,null,'1',null,Vcounter,null);
--            end if;
--         end if;
    end if;

   IF (:NEW.SFZJGH='0' AND :OLD.SFZJGH='1') then
     --停止加号
     :NEW.GHXHS0 := GREATEST(:NEW.GHYGHS,:NEW.MRXHS0);
     --update YS_HBHZXH set YXBZ00='0' where GHHBID=:new.GHHBID and SFZJ00='1' and YXBZ00='1' and GHH000 is null;
     delete from YS_HBHZXH where GHHBID=:new.GHHBID and SFZJ00='1' and YXBZ00='1' and BRID00 is null; --GHH000 is null;
   end if;
   IF   :NEW.GHXHS0 < :NEW.GHYGHS   THEN
        RAISE E_GHXHS0;
   END IF;
   IF   :NEW.YYXHS0 < :OLD.GHYYHS   THEN
        RAISE E_YYXHS0;
   END IF;
exception
     WHEN E_GHXHS0 THEN
          RAISE_APPLICATION_ERROR(-20281, '生成日期'||:NEW.GHMZSJ||'的挂号号表失败,原因:挂号的限号数不能小于已挂的号数!*');
          ROLLBACK;
     WHEN E_YYXHS0 THEN
          RAISE_APPLICATION_ERROR(-20281, '生成日期'||:NEW.GHMZSJ||'的挂号号表失败,原因:预约的限号数不能小于已预约的号数!*');
          ROLLBACK;
     WHEN OTHERS THEN
	  RAISE_APPLICATION_ERROR(-20908,substr(nvl(sqlerrm,'出错原因不详'),1,40)||'，请记录此信息并和系统管理员联系！'||'!*');
	  ROLLBACK;
end;
/

-- End of DDL script for TR_SF_SF_YSGHHB_UPDATE