create or replace function getdeviceidbytype(devicetype in number) return number
is
  xdevid number(30);
begin
  xdevid := 0;
  
  select nvl(xdev_id, 0)
  into xdevid
  from xdev
  where xdev_type = devicetype and rownum = 1;
  
  return xdevid;  

  exception
     when no_data_found then
       return 0;

end;
/


CREATE OR REPLACE PROCEDURE V8_UPD_UPDATEDEVICESTABLE
as
 wpcaid varchar2(60);
 xdevid number(30);
begin
  --check if fujitsu configured on some terminal
  begin
    select nvl(wpca_id, 0) 
    into wpcaid 
    from wpca 
    where nvl(wpca_passscanner, 0) = 1 and rownum = 1;

    exception
      when no_data_found then
        wpcaid := 0;
  end;
  if (wpcaid > 0) then
    --check if fujitsu already exists in devices
    xdevid := getdeviceidbytype(0);
    --fujitsu defined on terminal but not in table
    if (xdevid = 0) then
      insert into xdev(xdev_id, xdev_type) values(seq_xdev.nextval, 0);
      xdevid := getdeviceidbytype(0);
      --fujitsu exists update it's values;
      update xdev
      set 
        xdev_enablefastscanning = (select to_number(wuss_value) from wuss where wuss_name='EnablePassportScannerFastScan' and wuss_xcms_id=0 and rownum = 1),
        xdev_xpdg_id = (select to_number(wuss_value) from wuss where wuss_name='PassScannerDefGroupForIDs' and wuss_xcms_id=0 and rownum = 1),
        --xdev_xpdg_id = (select to_number(wuss_value) from wuss where wuss_name='PassScannerDefGroupForPassports' and wuss_xcms_id=0 and rownum = 1),
        xdev_autoattachpicture = (select to_number(wuss_value) from wuss where wuss_name='EnablePassScannerSavePicture' and wuss_xcms_id=0 and rownum = 1),
        xdev_picture_wdcg_id = (select to_number(wuss_value) from wuss where wuss_name='PassScannerPictureGroup' and wuss_xcms_id=0 and rownum = 1),
        xdev_promptwhenmissingfields = (select to_number(wuss_value) from wuss where wuss_name='PassScannerWarningMissingFields' and wuss_xcms_id=0 and rownum = 1),
        xdev_questionifsecondside = (select to_number(wuss_value) from wuss where wuss_name='QuestionForSecondSideScanning' and wuss_xcms_id=0 and rownum = 1),
        xdev_autoattachfullpicture = (select to_number(wuss_value) from wuss where wuss_name='EnablePassScannerSaveFullPicture' and wuss_xcms_id=0 and rownum = 1),
        xdev_fullpicture_wdcg_id = (select to_number(wuss_value) from wuss where wuss_name='PassScannerFullPictureGroup' and wuss_xcms_id=0 and rownum = 1),
        xdev_usewatermark = (select to_number(wuss_value) from wuss where wuss_name='EnablePassportScannerWaterMark' and wuss_xcms_id=0 and rownum = 1),
        xdev_watermark = (select wuss_value from wuss where wuss_name='PassportScannerWaterMarkPath' and wuss_xcms_id=0 and rownum = 1),
        xdev_usescanningcenter = (select wuss_value from wuss where wuss_name='UseScanningCenterMenu' and wuss_xcms_id=0 and rownum = 1)
      where xdev_id = xdevid;
    end if;
    --fujitsu defined on terminals and also in devices table; make links if not existing
    insert into xdlt(xdlt_id, xdlt_wpca_id, xdlt_xdev_id)
    select seq_xdlt.nextval, wpca_id, xdevid
    from wpca
    where nvl(wpca_passscanner, 0) = 1 and
      wpca_id not in (select xdlt_wpca_id from xdlt where xdlt_xdev_id=xdevid);
    --delete links that exists and terminal no more have the device
    delete from xdlt
    where xdlt_xdev_id=xdevid and xdlt_wpca_id not in
      (select wpca_id from wpca where nvl(wpca_passscanner, 0) = 1 );
  end if;
   
  --check if belgian eid reader configured on some terminal
  begin
    select nvl(wpca_id, 0) 
    into wpcaid 
    from wpca 
    where nvl(wpca_haseidreader, 0) = 1 and rownum = 1;

    exception
      when no_data_found then
        wpcaid := 0;
  end;       
  if (wpcaid > 0) then
    --check if belgian eid reader already exists in devices
    xdevid := getdeviceidbytype(1);
    --belgian eid reader defined on terminal but not in table
    if (xdevid = 0) then
      insert into xdev(xdev_id, xdev_type) values(seq_xdev.nextval, 1);
      xdevid := getdeviceidbytype(1);
      --belgian eid reader exists update it's values;
      update xdev
      set 
        xdev_autoattachpicture = (select to_number(wuss_value) from wuss where wuss_name='EnableBeIDSavePicture' and wuss_xcms_id=0 and rownum = 1),
        xdev_picture_wdcg_id = (select to_number(wuss_value) from wuss where wuss_name='BeIDPictureGroup' and wuss_xcms_id=0 and rownum = 1)
      where xdev_id = xdevid;
    end if;  
    --belgian eid reader defined on terminals and also in devices table; make links if not existing
    insert into xdlt(xdlt_id, xdlt_wpca_id, xdlt_xdev_id)
    select seq_xdlt.nextval, wpca_id, xdevid
    from wpca
    where nvl(wpca_haseidreader, 0) = 1 and
      wpca_id not in (select xdlt_wpca_id from xdlt where xdlt_xdev_id=xdevid);
    --delete links that exists and terminal no more have the device
    delete from xdlt
    where xdlt_xdev_id=xdevid and xdlt_wpca_id not in
      (select wpca_id from wpca where nvl(wpca_haseidreader, 0) = 1 );
  end if;
   
end;
/


CREATE OR REPLACE PROCEDURE V8_UPD_ZCNV_UPDATE
IS
   CURSOR fillmmod
   IS
        SELECT mmod_id, mmod_vendor_id, mmod_vendor_desc
          FROM mmod
      ORDER BY mmod_id;

   fill   fillmmod%ROWTYPE;
BEGIN
   FOR fill IN fillmmod
   LOOP
      UPDATE ZCNV
         SET zcnv_shortdesc = fill.mmod_vendor_id,
             zcnv_longdesc = fill.mmod_vendor_desc
       WHERE zcnv_interfaceid = fill.mmod_id;
   END LOOP;
END V8_UPD_ZCNV_UPDATE;
/


CREATE OR REPLACE PROCEDURE V8_UPD_ROOMRACKPOPUP IS
  cursor OOO is
    select distinct wuss_xcms_id from wuss where wuss_xcms_id <> 0;
  cooo OOO%rowtype;

begin

DELETE from wuss where wuss_name like 'RoomRackCalOnPopup';

  for cooo in ooo loop
    insert into wuss values (seq_wuss.nextval, cooo.wuss_xcms_id, 'RoomRackCalOnPopup', 1); 
  end loop;

  exception
    when no_data_found then
      raise; --null;
    when others then
      raise; --null;  
END V8_UPD_ROOMRACKPOPUP;
/


CREATE OR REPLACE PROCEDURE V8_UPD_UPDATEYBEFOFYEMI
as
  cursor yemicursor is
    select yemi.*, yevm.*, ybom.* from ybom, yevm, yemn, yemi where ybom_id=yevm_ybom_id and yevm_id=yemn_yevm_id and yemn_id=yemi_yemn_id and yemn_servestart >=(select wgbs_datevalue from fideliodate) and nvl(yemn_pkgincl, 0) = 1 and nvl(yemi_includedinthemenu, 0) = 0
      and nvl(yemi_price, 0) >0;
    
  yemirow yemicursor%rowtype;
  ybefid number;
  
begin

  for yemirow in yemicursor loop
    ybefid := 0;
    begin
      select nvl(ybef_id, 0)
      into ybefid
      from ybef
      where ybef_yevm_id=yemirow.yevm_id and ybef_zdco_id=yemirow.yemi_zdco_id and nvl(ybef_ypkd_id, 0)=0 and rownum=1;
      
      exception
        when no_data_found then
          ybefid :=0;
    end;
    if nvl(ybefid, 0) = 0 then --just insert the other one need a summarization which seems a complex thing
      insert into ybef(ybef_id, ybef_yevm_id, ybef_per_person, ybef_flat_amount, ybef_zcur_id, ybef_rev_amount, ybef_ypkd_id, ybef_yety_id, ybef_zdco_id, ybef_price_net, ybef_price_vat) 
      values(seq_ybef.nextval, yemirow.yevm_id, 0, 0, yemirow.ybom_base_zcur_id, yemirow.yemi_quantity * yemirow.yemi_price, 0, yemirow.yevm_yety_id, yemirow.yemi_zdco_id, yemirow.yemi_quantity * yemirow.yemi_price_net, yemirow.yemi_quantity * yemirow.yemi_price_vat);
    end if;   
  end loop;
      
end;
/
