--FUNCTIONS AND PROCEDURE TO CREATE MISSING FUNCTION SPACE ID'S IN YEBD + YSCP FOR COMBINATION ROOMS THAT HAVE BEEN ENTERED AFTER THE GENERAL CONFIGURATION (TT12028 + TT15788)

CREATE OR REPLACE function hascapacityfunctionspace(yfscid number, starttime date, endtime date, yebsid number, shareable number) return boolean
is
  cursor fsmaxcapacity(yfscid number)
  is
    select yfsc_minimumcapacity, yfsc_maximumcapacity from yfsc where yfsc_id=yfscid;
  rec_fsmaxcapacity fsmaxcapacity%ROWTYPE;
  
  cursor capacitybooked(yfscid number, starttime date, endtime date, yebsid number)
  is
    select sum(yevm_noofattendees) Booked 
    from yevm, yebs, yest 
    where yevm_id=yebs_yevm_id 
      and yebs_starttime-(1/1440)*yebs_setuptime<endtime 
      and yebs_endtime+(1/1440)*yebs_setdowntime>starttime 
      and yebs_yfsc_id=yfscid 
      and yevm_yest_id=yest_id 
      and yest_allowwaitlist<>1 
      and yest_cancelstatus<>1 
      and yest_deductinventory=1
      and (yebsid=0 or yebsid>0 and yebs_id<>yebsid); 
  rec_capacitybooked capacitybooked%ROWTYPE;
  --
  functionspacecapacity number;
  hascapacity boolean;
begin
  hascapacity:=true;
  open fsmaxcapacity(yfscid);
  fetch fsmaxcapacity into rec_fsmaxcapacity;
  functionspacecapacity:=rec_fsmaxcapacity.yfsc_maximumcapacity;
  close fsmaxcapacity;
  
  if shareable=1 then
    open capacitybooked(yfscid, starttime, endtime, yebsid);
    fetch capacitybooked into rec_capacitybooked;
    hascapacity:=(rec_capacitybooked.Booked>=functionspacecapacity);
    close capacitybooked;  
  end if;
  
  return hascapacity;
end;
/

CREATE OR REPLACE function checkfunctionspaceavailability(yebsid number, starttime date, endtime date, yfscid number, shareable number) return boolean
as
  cursor closedperiods(starttime date, endtime date, yfscid number) is
    select yscp_yfsc_id from yscp 
    where yscp_from<=endtime and yscp_to>=starttime and yscp_yfsc_id=yfscid;
  rec_closedperiods closedperiods%ROWTYPE;
  
  cursor bookedfs(starttime date, endtime date, yfscid number, yebsid number) is
    select yebs_id, yebs_shareable 
    from yebs,yevm, yest 
    where yebs_starttime-(1/1440)*yebs_setuptime<endtime
      and yebs_endtime+(1/1440)*yebs_setdowntime>starttime
      and yebs_yfsc_id=yfscid 
      and (yebsid=0 or yebsid>0 and yebs_id<>yebsid)
      and yebs_yevm_id=yevm_id and yevm_yest_id=yest_id and yest_allowwaitlist<>1 and yest_cancelstatus<>1 and yest_deductinventory=1 
    union 
    select yebs_id, yebs_shareable 
    from yebs,yevm, yest, yebd 
    where yebs_starttime-(1/1440)*yebs_setuptime<endtime
      and yebs_endtime+(1/1440)*yebs_setdowntime>starttime
      and yebd_yfsc_id=yfscid 
      and (yebsid=0 or yebsid>0 and yebs_id<>yebsid)
      and yebs_yevm_id=yevm_id and yevm_yest_id=yest_id and yest_allowwaitlist<>1 and yest_cancelstatus<>1 and yest_deductinventory=1 
      and yebs_id=yebd_yebs_id;
  rec_bookedfs bookedfs%ROWTYPE;
  
  isavailable boolean;
begin
  open closedperiods(starttime, endtime, yfscid);
  fetch closedperiods into rec_closedperiods;
  isavailable:=(closedperiods%ROWCOUNT=0);
  close closedperiods;
  if isavailable then 
    open bookedfs(starttime, endtime, yfscid, yebsid);
    fetch bookedfs into rec_bookedfs;
    if (shareable=0) or (shareable=2) then --try to book not shareable
      isavailable:=(bookedfs%ROWCOUNT=0);
      DBMS_OUTPUT.PUT_LINE('booked same period ='||to_char(bookedfs%ROWCOUNT));
    else --try to book in shareable mode
      loop
        fetch bookedfs into rec_bookedfs;
        exit when bookedfs%NOTFOUND or not isavailable;
        isavailable:=(rec_bookedfs.yebs_shareable=1);
      end loop;  
      if isavailable then --all existing booked fs are shareable
        isavailable:=hascapacityfunctionspace(yfscid, starttime, endtime, yebsid, shareable);
      end if;  
    end if;
    close bookedfs;
  end if;
  
  if isavailable then DBMS_OUTPUT.PUT_LINE('Available '||to_char(yebsid));
  else DBMS_OUTPUT.PUT_LINE('Not Available '||to_char(yebsid));
  end if;

  return isavailable;
end;
/

CREATE OR REPLACE procedure checkglobalavailability is
  --exception declaration
  NOWAITLISTSTATUSFOUND EXCEPTION;
  NOSYSTEMDATEFOUND EXCEPTION;
  --booked and closed fs
  cursor bookedandclosedperiods(fromdate Date) is
    select v.* 
    from( 
    --only yebs headers of what it is booked as combined owner or child
    select yebs_id IId, yebs_starttime Starttime, yebs_endtime EndTime, yebs_yfsc_id yfsc_id, 1 FS, 0 CP, ' ' Reason, yebs_shareable Shareable, yebs_yevm_id yevm_id 
    from yebs 
    where yebs_endtime>=fromdate 
    union all 
    --only yscp where yscp_original=1
    select yscp_id, yscp_from, yscp_to, yscp_yfsc_id, 0 FS, 1 CP, yscp_reason Reason, 0 Shareable, 0 yevm_id 
    from yscp 
    where yscp_to>=fromdate and nvl(yscp_original, 0)=1 
    ) v 
    where v.yfsc_id in 
    ( 
      select distinct ysco_owner_yfsc_id from ysco 
      union 
      select distinct ysco_child_yfsc_id from ysco 
    ) 
    order by v.starttime desc;
  rec_bookedandclosedperiods bookedandclosedperiods%ROWTYPE;
  
  --booked fs
  cursor bookedfs(iid number, fromdate date, todate date, ascp number)
  is
    select yscp_yfsc_id yfsc_id 
    from yscp 
    where (ascp=1) and yscp_from=fromdate and yscp_to=todate and nvl(yscp_original, 0)=0
    union
    select yebd_yfsc_id yfsc_id 
    from yebd 
    where (ascp=0) and yebd_yebs_id=iid;
  rec_bookedfs bookedfs%ROWTYPE;   
  
  --config for 1 fs
  cursor configfs(yfscid number) 
  is
    select distinct v.yfsc_id, 
    (select yfsc_longdesc from yfsc where yfsc_id=v.yfsc_id) 
    from( 
    --childs of the owner
    select ysco_child_yfsc_id yfsc_id from ysco where ysco_owner_yfsc_id=yfscid 
    --owner of the childs of current owner
    union all 
    select ysco_owner_yfsc_id from ysco, 
    (select ysco_child_yfsc_id yfsc_id from ysco where ysco_owner_yfsc_id=yfscid) childs 
    where ysco_child_yfsc_id=childs.yfsc_id 
    union all 
    --owner of the childs
    select ysco_owner_yfsc_id from ysco where ysco_child_yfsc_id=yfscid 
    ) v 
    where v.yfsc_id<>yfscid;
  rec_configfs configfs%ROWTYPE;  
  --event info
  cursor eventstatusestypes(yevmid number) is
    select yest_allowwaitlist, yest_deductinventory, 
    (select ybom_con_yest_id from ybom where ybom_id=yevm_ybom_id) bookingyest_id 
    from yevm, yest 
    where yevm_yest_id=yest_id and yevm_id=yevmid;
  rec_eventstatusestypes eventstatusestypes%ROWTYPE; 
  --wmlg cursor
  cursor wmlgrecord(tbl varchar2, iid number) is
    select * from wmlg where wmlg_tablename=tbl and wmlg_table_id=iid;
  rec_wmlgrecord wmlgrecord%ROWTYPE;  
  --
  waitlist_yest_id number;
  systemdate date;
  recfound boolean;
  isavailable boolean;
  iseventwaitlist boolean;
  iseventdeductinventory boolean;
  bookingyest_id number;
  eventyest_id number;
  yscpid number;
begin
  --check waitlist status existence
  select yest_id
  into waitlist_yest_id
  from yest
  where nvl(yest_allowwaitlist, 0)=1;
  if nvl(waitlist_yest_id, 0)=0 then
    raise NOWAITLISTSTATUSFOUND;
  end if;
  --check system date
  select wgbs_datevalue
  into systemdate
  from wgbs;
  if systemdate is null then
    raise NOSYSTEMDATEFOUND;
  end if;  
  
  --fix inconsistencies yebs+yebs using yfsc+ysco 
  open bookedandclosedperiods(systemdate);
  loop
    fetch bookedandclosedperiods into rec_bookedandclosedperiods;
    exit when bookedandclosedperiods%NOTFOUND;
    
    DBMS_OUTPUT.PUT_LINE(
      to_char(rec_bookedandclosedperiods.iid)||' - '||to_char(rec_bookedandclosedperiods.starttime)
      ||' - '||to_char(rec_bookedandclosedperiods.cp));

    --check bookedfs vs configfs
    open bookedfs(rec_bookedandclosedperiods.iid, rec_bookedandclosedperiods.starttime, rec_bookedandclosedperiods.endtime, rec_bookedandclosedperiods.cp);
    loop
      fetch bookedfs into rec_bookedfs;
      exit when bookedfs%NOTFOUND;

      open configfs(rec_bookedandclosedperiods.yfsc_id);
      recfound:=false;
      loop
        fetch configfs into rec_configfs;
        exit when configfs%NOTFOUND or recfound;
        if rec_bookedfs.yfsc_id=rec_configfs.yfsc_id then
          recfound:=true;
        end if;
      end loop;
      if not recfound then
        if rec_bookedandclosedperiods.fs=1 then
          DBMS_OUTPUT.PUT_LINE('delete booked yebs_id='||to_char(rec_bookedandclosedperiods.iid)||' from_yfsc_id='||to_char(rec_bookedandclosedperiods.yfsc_id)||' yfsc_id='||to_char(rec_bookedfs.yfsc_id));
          delete from yebd where yebd_yebs_id=rec_bookedandclosedperiods.iid and yebd_yfsc_id=rec_bookedfs.yfsc_id;
        else
          DBMS_OUTPUT.PUT_LINE('delete closed from='||to_char(rec_bookedandclosedperiods.starttime)||' from_yfsc_id='||to_char(rec_bookedandclosedperiods.yfsc_id)||' yfsc_id'||to_char(rec_bookedfs.yfsc_id));
          delete from yscp where yscp_from=rec_bookedandclosedperiods.starttime and yscp_to=rec_bookedandclosedperiods.endtime and yscp_yfsc_id=rec_bookedfs.yfsc_id;
        end if;
      end if;
      close configfs;
    end loop;
    close bookedfs;
     
    --check configfs vs bookedfs
    open configfs(rec_bookedandclosedperiods.yfsc_id);
    loop
      fetch configfs into rec_configfs;
      exit when configfs%NOTFOUND;
      open bookedfs(rec_bookedandclosedperiods.iid, rec_bookedandclosedperiods.starttime, rec_bookedandclosedperiods.endtime, rec_bookedandclosedperiods.cp);
      recfound:=false;
      loop
        fetch bookedfs into rec_bookedfs;
        exit when bookedfs%NOTFOUND or recfound;
        if rec_configfs.yfsc_id=rec_bookedfs.yfsc_id then
          recfound:=true;
        end if;
      end loop;
      if not recfound then
        if rec_bookedandclosedperiods.fs=1 then
          DBMS_OUTPUT.PUT_LINE('insert booked yebs_id='||to_char(rec_bookedandclosedperiods.iid)||' yfsc_id='||to_char(rec_configfs.yfsc_id));
          insert into yebd(yebd_id, yebd_yebs_id, yebd_yfsc_id) 
          values(seq_yebd.nextval, rec_bookedandclosedperiods.iid, rec_configfs.yfsc_id);
        else
          DBMS_OUTPUT.PUT_LINE('insert closed from='||to_char(rec_bookedandclosedperiods.starttime)||' yfsc_id'||to_char(rec_configfs.yfsc_id));
          select seq_yscp.nextval 
          into yscpid
          from dual;
          
          insert into yscp(yscp_id, yscp_yfsc_id, yscp_from, yscp_to, yscp_reason, yscp_original) 
          values(yscpid, rec_configfs.yfsc_id, rec_bookedandclosedperiods.starttime, rec_bookedandclosedperiods.endtime, rec_bookedandclosedperiods.reason, 0);
          --create wmlg record 
          open wmlgrecord('YSCP', rec_bookedandclosedperiods.iid);
          fetch wmlgrecord into rec_wmlgrecord;
          insert into wmlg(wmlg_id, wmlg_tablename, wmlg_table_id, wmlg_xcms_creation, wmlg_time_creation, wmlg_fideliodate, wmlg_time_update, wmlg_xcms_update, wmlg_creaprgversion, wmlg_creadbversion) 
                   values (seq_wmlg.nextval, 'YSCP', yscpid, rec_wmlgrecord.wmlg_xcms_creation, rec_wmlgrecord.wmlg_time_creation, rec_wmlgrecord.wmlg_fideliodate, rec_wmlgrecord.wmlg_time_update, rec_wmlgrecord.wmlg_xcms_update, rec_wmlgrecord.wmlg_creaprgversion, rec_wmlgrecord.wmlg_creadbversion );
          close wmlgrecord; 
        end if;
      end if;
      close bookedfs;
    end loop;
    close configfs;
  end loop;   
  close bookedandclosedperiods;
  commit;
  
  --che4ck availability
  open bookedandclosedperiods(systemdate);
  loop
    fetch bookedandclosedperiods into rec_bookedandclosedperiods;
    exit when bookedandclosedperiods%NOTFOUND;
    if rec_bookedandclosedperiods.fs=1 then
      isavailable:=checkfunctionspaceavailability(rec_bookedandclosedperiods.iid, rec_bookedandclosedperiods.starttime, rec_bookedandclosedperiods.endtime, rec_bookedandclosedperiods.yfsc_id, rec_bookedandclosedperiods.shareable);
      
      open eventstatusestypes(rec_bookedandclosedperiods.yevm_id);
      fetch eventstatusestypes into rec_eventstatusestypes;
      iseventwaitlist:=(rec_eventstatusestypes.yest_allowwaitlist=1);
      iseventdeductinventory:=(rec_eventstatusestypes.yest_deductinventory=1);
      bookingyest_id:=rec_eventstatusestypes.bookingyest_id;
      close eventstatusestypes;

      if not isavailable and iseventdeductinventory or isavailable and iseventwaitlist then
        eventyest_id:=0;
        if not isavailable and iseventdeductinventory then 
          eventyest_id:=waitlist_yest_id;
        end if;  
        if isavailable and iseventwaitlist then 
          eventyest_id:=bookingyest_id;
        end if;  
        update yevm set yevm_yest_id=eventyest_id where yevm_id=rec_bookedandclosedperiods.yevm_id;
      end if;
      
    end if;
  end loop;
  close bookedandclosedperiods;
  
  EXCEPTION
    when NOWAITLISTSTATUSFOUND then
      DBMS_OUTPUT.PUT_LINE('No waitlist status configured found');
    when NOSYSTEMDATEFOUND then
      DBMS_OUTPUT.PUT_LINE('No system date found');
    when OTHERS then  
      DBMS_OUTPUT.PUT_LINE('Exception unidentified');
end;
/
