IF (EXISTS(Select * from sys.sysprocedure where LCase(proc_name) = LCase('GetRegistry')))
THEN Drop procedure GetRegistry
END IF

GO

create function
DBA.GetRegistry(in @RegName char(120),in @Owner char(60),in @UseDefaultOwner integer default 0)
returns varchar(200)
begin
  --------------------------------------------------------------
  -- Reads a registry value. If it does not exist returns NULL
  --------------------------------------------------------------
  declare R varchar(200);
  -- --------------------
  set R=(select MIN(Registry.Value) from Registers,Registry where
      Registers.Name = @RegName and Registers.N = Registry.Register and Registry.Owner = @Owner);
  if(R is null) and(@Owner <> '_') and(@UseDefaultOwner <> 0) then
    set R=(select MIN(Registry.Value) from Registers,Registry where
        Registers.Name = @RegName and Registers.N = Registry.Register and Registry.Owner = '_')
  end if;
  return(R)
end
-- [20.Feb.2008] - kopirana ot Riscont

GO

IF (EXISTS(Select * from sys.sysprocedure where LCase(proc_name) = LCase('GetPropertyValues')))
THEN Drop procedure GetPropertyValues
END IF

GO

create procedure
DBA.GetPropertyValues(in @ASvoiTable varchar(50),in @glava integer)
result(Svoistvo integer,SvoistvoIme varchar(40),Stoinost varchar(100))
on exception resume
begin
  declare local temporary table pom(
    Svoistvo integer null,
    SvoistvoIme varchar(40) null,
    Stoinost varchar(100) null,
    ) on commit preserve rows;
  //  execute immediate 'Insert into pom (Svoistvo,SvoistvoIme,Stoinost) Select Distinct S.N as Svoistvo ,S.Ime as SvoistvoIme ,Stoinost=GetPropertyValue('''||@ASvoiTable||''',@glava,S.N) from "'||@ASvoiTable||'" as S ,"'||@ASvoiTable||'Stn" as St Where S.N=ST.Svoistvo And ST.Glava='||cast(@glava as varchar(12));
  execute immediate 'Insert into pom (Svoistvo,SvoistvoIme,Stoinost) Select Distinct S.N as Svoistvo ,S.Ime as SvoistvoIme ,Stoinost=GetPropertyValue(''' || @ASvoiTable || ''',@glava,S.N) from "' || @ASvoiTable || '" as S left outer join "' || @ASvoiTable || 'Stn" as St on S.N=ST.Svoistvo And ST.Glava=' || cast(@glava as varchar(12)) || ' Where isnull(GetValue,'''')<>'''' or st.stoinost is not null' || ' and s.active=1';
  select * from pom
end
-- [05 Aug 2003] - peter - modified - 'left outer join ...' added
-- [04.Mar.2008] - kopirana ot Riscont

GO

IF (EXISTS(Select * from sys.sysprocedure where LCase(proc_name) = LCase('GetPropertyValue')))
THEN Drop procedure GetPropertyValue
END IF

GO

create function
DBA.GetPropertyValue(in @ASvoiTable varchar(50),in @glava integer,in @property integer)
returns varchar(100)
begin
  declare Res varchar(100);
  declare GetValue_ varchar(255);
  execute immediate 'Select GetValue into GetValue_ from "' || @ASvoiTable || '" as S Where S.N=' || @property;
  if Isnull(GetValue_,'') <> '' then
    set GetValue_='Select (' || GetValue_ || ') into res ';
    execute immediate GetValue_
  end if;
  if Isnull(GetValue_,'') = '' then
    execute immediate 'Select (If (Trim(Isnull(S.LookUpTable,''''))='''')  then ST.Stoinost else GetLookUpValue(S.LookUpTable,S.LookUpKeyField,S.LookUpResultField,ST.Stoinost) endif)  into res from "' || @ASvoiTable || '" as S ,"' || @ASvoiTable || 'Stn" as St Where S.N=ST.Svoistvo And ST.Glava=' || cast(@glava as varchar(12)) || ' And S.N=' || @property
  end if;
  return res
end
-- [04.Mar.2008] - kopirana ot Riscont

GO

IF (EXISTS(Select * from sys.sysprocedure where LCase(proc_name) = LCase('GetPropertyValueByName')))
THEN Drop procedure GetPropertyValueByName
END IF

GO

create function
DBA.GetPropertyValueByName(in @ASvoiTable varchar(50),in @glava integer,in @propertyname varchar(50))
returns varchar(100)
begin
  declare prop integer;
  execute immediate 'Select N into prop from ' || @ASvoiTable || ' Where Ime=''' || @propertyname || '''';
  return GetPropertyValue(@ASvoiTable,@glava,prop)
end
-- [04.Mar.2008] - kopirana ot Riscont

GO

IF (EXISTS(Select * from sys.sysprocedure where LCase(proc_name) = LCase('GetLookUpValue')))
THEN Drop procedure GetLookUpValue
END IF

GO

create function
DBA.GetLookUpValue(in LookUpTable_ varchar(50),in LookUpKeyField_ varchar(50),in LookUpResultField_ varchar(50),in Stoinost_ varchar(100))
returns varchar(100)
begin
  declare res varchar(100);
  execute immediate 'Select Cast("' || LookUpResultField_ || '" as varchar(100)) into res From "' || LookUpTable_ || '" Where Cast("' || LookUpKeyField_ || '" as VarChar(100)) = ''' || Stoinost_ || '''';
  return res
end
-- [04.Mar.2008] - kopirana ot Riscont

GO

IF (EXISTS(Select * from sys.sysprocedure where LCase(proc_name) = LCase('ChangePriority')))
THEN Drop procedure ChangePriority
END IF

GO

create procedure DBA.ChangePriority(in @o_priority integer,in @o_hid integer)
begin
  for f as curs scroll cursor for
    select id as @id,priority as @priority from ExportType where
      Priority > @o_priority and HandleID = @o_hid do
    update ExportType set Priority = (@priority-1) where id = @id end for
end
//v1 05.2008 created - gnikolov

GO

IF (EXISTS(Select * from sys.sysprocedure where LCase(proc_name) = LCase('GetCounter')))
THEN Drop procedure GetCounter
END IF

GO

create function DBA.GetCounter(in @cnt_id integer)
returns integer
not deterministic
begin
  declare cnt integer;
  update Counters set Counter = Counter+1 where id = @cnt_id;
  set cnt=(select Counter from Counters where id = @cnt_id);
  return cnt
end
//05.2008 gnikolov - created

GO
