package DB_DDSS
-- @E:\SVNPRIV.MK\MAIN\ČVUT\DB\Schema\pDb.sql
-- @X:\SVNPRIV.MK\MAIN\ČVUT\DB\Schema\pDb.sql
-- @X:\SVNPRIV.MK\MAIN\ČVUT\DB\Schema\pDbDdss.sql
-- drop package DB_DDSS;
as
--
-- Pouzite zkratky:
--   cl ... libovolna trida (class)
--   kt ... korenova trida
--   cs ... cast stavby
--   se ... skupina elementu
--   el ... element
--   sv ... skupina vlastnosti
--   vl ... vlastnost
--   dt ... datovy typ
--   en ... hodnota vyctu (enum)
--   un ... jednotka (unit)
--   is ... index skupiny vlastnosti
--
------------
-- kurzory --
------------
--
  cursor c_sv_vl(p_apl char, p_vl_id number) is
  -- skupiny vlastnosti, obsahujici vlastnost
    select Property_Set_ID sv_id
      from X_Property_Set_Property x
      where x.Property_ID = p_vl_id
        and x.Visible like '%'||p_apl||'%';
--
  cursor c_vl_sv(p_apl char, p_sv_id number) is
  -- vlastnosti, zarazene do skupiny vlastnosti
    select Property_ID vl_id
      from X_Property_Set_Property x
      where x.Property_Set_ID = p_sv_id
        and x.Visible like '%'||p_apl||'%';
--
  cursor c_cl_sv(p_apl char, p_sv_id number) is
  -- elementy (tridy), obsahujici skupinu vlastnosti
    select Class_ID cl_id
      from X_Property_Set_Class x
      where x.Property_Set_ID = p_sv_id
        and x.Visible like '%'||p_apl||'%';
-- skupiny vlastnosti, zarazene do elementu
  cursor c_sv_cl(p_apl char, p_cl_id number) is
    select Property_Set_id sv_id
      from X_Property_Set_Class x
      where x.Class_ID = p_cl_id
        and x.Visible like '%'||p_apl||'%';
--
------------
-- funkce --
------------
--
-- obecne
--
  function addVisible(
    p_vis varchar2, -- seznam modulu, ktere objekt vidi
    p_apl char -- pridat tento modul
    ) return varchar2;
--
  function delVisible(
    p_vis varchar2, -- seznam modulu, ktere objekt vidi
    p_apl char -- odebrat tento modul
    ) return varchar2;
--
-- tridy (korenova (kt), cast stavby (cs), skupina elementu (se), element (el)
--
  function id_cl(
  -- id tridy podle jmena a rodice
    p_apl char,
    p_col char, -- 'C'~cz, 'E'~en, 'N'~nvl
    p_cl_name varchar2,
    p_cl_pid number
    ) return number;
--
  function id_kt(
  -- id korenove tridy podle aplikace
    p_apl char
    ) return number;
--
  function id_cs(
  -- id casti stavby (tridy) podle jmena a rodice
    p_apl char,
    p_col char, -- 'C'~cz, 'E'~en, 'N'~nvl
    p_cl_name varchar2,
    p_cl_pid number
    ) return number;
--
  function id_se(
  -- id skupiny elementu (tridy) podle jmena a rodice
    p_apl char,
    p_col char, -- 'C'~cz, 'E'~en, 'N'~nvl
    p_cl_name varchar2,
    p_cl_pid number
    ) return number;
--
  function id_el(
  -- id elementu (tridy) podle jmena a rodice
    p_apl char,
    p_col char, -- 'C'~cz, 'E'~en, 'N'~nvl
    p_cl_name varchar2,
    p_cl_pid number
    ) return number;
--
-- Skupiny vlastnosti (sv)
--
  function id_sv(
  -- id skupiny vlastnosti podle jmena
    p_apl char,
    p_col char, -- 'C'~cz, 'E'~en, 'N'~nvl
    p_sv_name varchar2
    ) return number;
--
-- Vlastnosti (vl)
--
  function id_vl(
  -- id skupiny vlastnosti podle jmena
    p_apl char,
    p_col char, -- 'C'~cz, 'E'~en, 'N'~nvl
    p_vl_name varchar2
    ) return number;
--
-- Datove typy (dt)
--
  function id_dt(
  -- id datoveho typu podle ident
    p_apl char,
    p_dt_typ varchar2,
    p_dt_subtyp varchar2,
    p_dt_ident varchar2
    ) return number;
--
  function maxpos_dt(
  -- maximalni hodnota pozice v danem vyctu cehokoli (typu, jednotky, vyctove hodnoty, ...)
    p_apl char,
    p_dt_typ varchar2,
    p_dt_subtyp varchar2
    ) return number;
--
  function nxtpos_dt(
  -- prvni volna hodnota pozice v danem vyctu cehokoli (typu, jednotky, vyctove hodnoty, ...)
    p_apl char,
    p_dt_typ varchar2,
    p_dt_subtyp varchar2
    ) return number;
--
-- Index skupiny vlastnosti (is)
--
  function id_is(
  -- id skupiny vlastnosti podle jmena
    p_apl char,
    p_is_znak char
    ) return number;
--
-- Faze projektu
--
  function new_phasid(
    -- pokud byla dosud vybrana faze s ID=odlphasid, a prepina se do modulu newapl, jaka faze ma byt nastavena? Pokud mozno ta puvodni
    userlogin varchar2,
    oldphasid number,
    newapl char
    ) return number;
--
---------------
-- procedury --
---------------
--
--
end DB_DDSS; 

package body DB_DDSS
-- @E:\SVNPRIV.MK\MAIN\ČVUT\DB\Schema\packageDb.sql
-- @X:\SVNPRIV.MK\MAIN\ČVUT\DB\Schema\packageDb.sql
-- @X:\SVNPRIV.MK\MAIN\ČVUT\DB\Schema\packageDbDdss.sql
-- drop package body DB_DDSS;
as
--
------------
-- funkce --
------------
--
-- obecne
--
  function addVisible(
    p_vis varchar2, -- seznam modulu, ktere objekt vidi
    p_apl char -- pridat tento modul
    ) return varchar2
  is
  begin
    return translate(p_vis,'#'||p_apl,'#')||p_apl;
  end addVisible;
--
  function delVisible(
    p_vis varchar2, -- seznam modulu, ktere objekt vidi
    p_apl char -- odebrat tento modul
    ) return varchar2
  is
  begin
    return translate(p_vis,'#'||p_apl,'#');
  end delVisible;
--
-- tridy (korenova (kt), cast stavby (cs), skupina elementu (se), element (el)
--
  function id_cl_local(
  -- id tridy podle jmena, rodice a typu
    p_apl char,
    p_col char, -- 'C'~cz, 'E'~en, 'N'~nvl
    p_cl_name varchar2,
    p_cl_pid number,
    p_cl_typ varchar2 -- '%' ~ libovolna, '-' ~ bez konkretniho typu
    ) return number
  is
    ret number;
  begin
    if p_cl_pid is null
    then -- trida bez rodice
      begin
        if p_col='C'
        then
          select ID
            into ret
            from IFC_Class
            where coalesce(Name_CZ,'#'||ID) = p_cl_name
              and Parent_ID is null
              and nvl(ClassType,'-') like p_cl_typ;
        elsif p_col='C*'
        then
          select ID
            into ret
            from IFC_Class
            where upper(coalesce(Name_CZ,'#'||ID)) = upper(p_cl_name)
              and Parent_ID is null
              and nvl(ClassType,'-') like p_cl_typ;
        elsif p_col='E'
        then
          select ID
            into ret
            from IFC_Class
            where coalesce(Name,'#'||ID) = p_cl_name
              and Parent_ID is null
              and nvl(ClassType,'-') like p_cl_typ;
        elsif p_col='E*'
        then
          select ID
            into ret
            from IFC_Class
            where upper(coalesce(Name,'#'||ID)) = upper(p_cl_name)
              and Parent_ID is null
              and nvl(ClassType,'-') like p_cl_typ;
        elsif p_col='N*'
        then
          select ID
            into ret
            from IFC_Class
            where upper(coalesce(Name,Name_CZ)) = upper(p_cl_name)
              and Parent_ID is null
              and nvl(ClassType,'-') like p_cl_typ;
        else -- 'N'
          select ID
            into ret
            from IFC_Class
            where coalesce(Name,Name_CZ) = p_cl_name
              and Parent_ID is null
              and nvl(ClassType,'-') like p_cl_typ;
        end if;
        return ret;
      exception
        when no_data_found
          then return null;
      end;
    elsif p_cl_pid = -1
    then -- trida s libovolnym rodicem
      begin
        if p_col='C'
        then
          select ID
            into ret
            from IFC_Class
            where coalesce(Name_CZ,'#'||ID) = p_cl_name
              and nvl(ClassType,'-') like p_cl_typ;
        elsif p_col='C*'
        then
          select ID
            into ret
            from IFC_Class
            where upper(coalesce(Name_CZ,'#'||ID)) = upper(p_cl_name)
              and nvl(ClassType,'-') like p_cl_typ;
        elsif p_col='E'
        then
          select ID
            into ret
            from IFC_Class
            where coalesce(Name,'#'||ID) = p_cl_name
              and nvl(ClassType,'-') like p_cl_typ;
        elsif p_col='E*'
        then
          select ID
            into ret
            from IFC_Class
            where upper(coalesce(Name,'#'||ID)) = upper(p_cl_name)
              and nvl(ClassType,'-') like p_cl_typ;
        elsif p_col='N*'
        then
          select ID
            into ret
            from IFC_Class
            where upper(coalesce(Name,Name_CZ)) = upper(p_cl_name)
              and nvl(ClassType,'-') like p_cl_typ;
        else -- N
          select ID
            into ret
            from IFC_Class
            where coalesce(Name,Name_CZ) = p_cl_name
              and nvl(ClassType,'-') like p_cl_typ;
        end if;
        return ret;
      exception
        when no_data_found
          then return null;
        when dup_val_on_index
          then return null;
      end;
    else -- trida s konkretnim rodicem
      begin
        if p_col='C'
        then
          select ID
            into ret
            from IFC_Class
            where coalesce(Name_CZ,'#'||ID) = p_cl_name
              and Parent_ID = p_cl_pid
              and nvl(ClassType,'-') like p_cl_typ;
        elsif p_col='C*'
        then
          select ID
            into ret
            from IFC_Class
            where upper(coalesce(Name_CZ,'#'||ID)) = upper(p_cl_name)
              and Parent_ID = p_cl_pid
              and nvl(ClassType,'-') like p_cl_typ;
        elsif p_col='E'
        then
          select ID
            into ret
            from IFC_Class
            where coalesce(Name,'#'||ID) = p_cl_name
              and Parent_ID = p_cl_pid
              and nvl(ClassType,'-') like p_cl_typ;
        elsif p_col='E*'
        then
          select ID
            into ret
            from IFC_Class
            where upper(coalesce(Name,'#'||ID)) = upper(p_cl_name)
              and Parent_ID = p_cl_pid
              and nvl(ClassType,'-') like p_cl_typ;
        elsif p_col='N*'
        then
          select ID
            into ret
            from IFC_Class
            where upper(coalesce(Name,Name_CZ)) = upper(p_cl_name)
              and Parent_ID = p_cl_pid
              and nvl(ClassType,'-') like p_cl_typ;
        else -- N
          select ID
            into ret
            from IFC_Class
            where coalesce(Name,Name_CZ) = p_cl_name
              and Parent_ID = p_cl_pid
              and nvl(ClassType,'-') like p_cl_typ;
        end if;
        return ret;
      exception
        when no_data_found
          then return null;
      end;
    end if;
  end id_cl_local;
--
  function id_cl(
  -- id tridy podle jmena a rodice
    p_apl char,
    p_col char, -- 'C'~cz, 'E'~en, 'N'~nvl
    p_cl_name varchar2,
    p_cl_pid number
    ) return number
  is
  begin
    return id_cl_local(p_apl,p_col,p_cl_name,p_cl_pid,'%');
  end id_cl;
--
  function id_kt(
  -- id korenove tridy podle aplikace
    p_apl char
    ) return number
  is
    ret number;
  begin
    select ID
      into ret
      from (
        select ID
          from IFC_Class
          where Visible like '%'||p_apl||'%'
            and Parent_ID is null
          order by ID
        )
      where rownum=1;
    return ret;
  exception
    when no_data_found
      then return null;
  end id_kt;
--
  function id_cs(
  -- id casti stavby (tridy) podle jmena a rodice
    p_apl char,
    p_col char, -- 'C'~cz, 'E'~en, 'N'~nvl
    p_cl_name varchar2,
    p_cl_pid number
    ) return number
  is
  begin
    return id_cl_local(p_apl,p_col,p_cl_name,p_cl_pid,'C');
  end id_cs;
--
  function id_se(
  -- id skupiny elementu (tridy) podle jmena a rodice
    p_apl char,
    p_col char, -- 'C'~cz, 'E'~en, 'N'~nvl
    p_cl_name varchar2,
    p_cl_pid number
    ) return number
  is
  begin
    return id_cl_local(p_apl,p_col,p_cl_name,p_cl_pid,'S');
  end id_se;
--
  function id_el(
  -- id elementu (tridy) podle jmena a rodice
    p_apl char,
    p_col char, -- 'C'~cz, 'E'~en, 'N'~nvl
    p_cl_name varchar2,
    p_cl_pid number
    ) return number
  is
  begin
    return id_cl_local(p_apl,p_col,p_cl_name,p_cl_pid,'E');
  end id_el;
--
-- Skupiny vlastnosti (sv)
--
  function id_sv(
  -- id skupiny vlastnosti podle jmena
    p_apl char,
    p_col char, -- 'C'~cz, 'E'~en, 'N'~nvl
    p_sv_name varchar2
    ) return number
  is
    ret number;
  begin
    select ID
      into ret
      from IFC_Property_Set
      where coalesce(Name,p_col,Name_CZ) = p_sv_name;
    return ret;
  exception
    when no_data_found
      then return null;
  end id_sv;
--
-- Vlastnosti (vl)
--
  function id_vl(
  -- id skupiny vlastnosti podle jmena
    p_apl char,
    p_col char, -- 'C'~cz, 'E'~en, 'N'~nvl
    p_vl_name varchar2
    ) return number
  is
    ret number;
  begin
    if p_col='C'
    then
      select ID
        into ret
        from IFC_Property
        where Visible like '%'||p_apl||'%'
          and Name_CZ = p_vl_name;
    elsif p_col='E'
    then
      select ID
        into ret
        from IFC_Property
        where Visible like '%'||p_apl||'%'
          and Name = p_vl_name;
    else
      select ID
        into ret
        from IFC_Property
        where Visible like '%'||p_apl||'%'
          and coalesce(Name,Name_CZ) = p_vl_name;
    end if;
    return ret;
  exception
    when no_data_found
      then return null;
  end id_vl;
--
-- Datove typy (dt)
--
  function id_dt(
  -- id datoveho typu podle ident
    p_apl char,
    p_dt_typ varchar2,
    p_dt_subtyp varchar2,
    p_dt_ident varchar2
    ) return number
  is
    ret number;
  begin
    select ID
      into ret
      from ENUM_List
      where Type = p_dt_typ
        and SubType = p_dt_subtyp
        and Ident = p_dt_ident;
    return ret;
  exception
    when no_data_found
      then return null;
  end id_dt;
--
  function maxpos_dt(
  -- maximalni hodnota pozice v danem vyctu cehokoli (typu, jednotky, vyctove hodnoty, ...)
    p_apl char,
    p_dt_typ varchar2,
    p_dt_subtyp varchar2
    ) return number
  is
    ret number;
  begin
    select coalesce(max(Pos),0)
      into ret
      from ENUM_List
      where Type = p_dt_typ
        and SubType = p_dt_subtyp;
    return ret;
  end maxpos_dt;
--
  function nxtpos_dt(
  -- prvni volna hodnota pozice v danem vyctu cehokoli (typu, jednotky, vyctove hodnoty, ...)
    p_apl char,
    p_dt_typ varchar2,
    p_dt_subtyp varchar2
    ) return number
  is
  begin
    return maxpos_dt(p_apl,p_dt_typ,p_dt_subtyp)+1;
  end nxtpos_dt;
--
-- Index skupiny vlastnosti (is)
--
  function id_is(
  -- id skupiny vlastnosti podle jmena
    p_apl char,
    p_is_znak char
    ) return number
  is
    ret number;
  begin
    select ID
      into ret
      from STD_Property_Set_Index
      where Znak = p_is_znak;
    return ret;
  exception
    when no_data_found
      then return null;
  end id_is;
--
-- Faze projektu
--
  function new_phasid(
    -- pokud byla dosud vybrana faze s ID=odlphasid, a prepina se do modulu newapl, jaka faze ma byt nastavena? Pokud mozno ta puvodni
    userlogin varchar2,
    oldphasid number,
    newapl char
    ) return number
  is
    ret number;
  begin
    -- pokud to jde, tak tu puvodni
    begin
      select P.ID
        into ret
        from
          STD_PRJ_Phase P
        where P.ID=oldphasid
          and P.Visible like '%'||newapl||'%';
      return ret;
    exception
      when others then null;
    end;
    -- defaultni faze uzivatele
    begin
      select P.ID
        into ret
        from
          BIM_User U,
          STD_PRJ_Phase P
        where U.Login=userlogin
          and P.ID=U.DefPhase
          and P.Visible like '%'||newapl||'%';
      return ret;
    exception
      when others then null;
    end;
    -- posledni faze uzivatele
    begin
      select P.ID
        into ret
        from
          BIM_User U,
          STD_PRJ_Phase P
        where U.Login=userlogin
          and P.ID=U.LstPhase
          and P.Visible like '%'||newapl||'%';
      return ret;
    exception
      when others then null;
    end;
    -- PDPS
    begin
      select P.ID
        into ret
        from
          STD_PRJ_Phase P
        where P.Kod='PDPS'
          and P.Visible like '%'||newapl||'%';
      return ret;
    exception
      when others then null;
    end;
    return null;
  end new_phasid;
--
---------------
-- procedury --
---------------
--
--
end DB_DDSS;