if (select count(*) from sys.systable where table_name='BulSystAccess')=0
  then
     CREATE TABLE "DBA"."BulSystAccess"
       (
	"RoomN"        		integer NOT NULL,
	"Concentrator" 		integer NULL,
	"Reader"   		integer NULL,
	PRIMARY KEY ("RoomN")
        )
end if;

GO

delete from BulSystAccess

GO

insert into BulSystAccess(RoomN,Concentrator,Reader) select RoomNum,0,0 from Rooms order by RoomNum

GO

IF (EXISTS (select proc_name from sysprocedure where proc_name='GetRoomID')) THEN
  Drop function GetRoomID;
END IF;

GO

create function "DBA"."GetRoomID"(in @RoomNo integer)
returns varchar(7)
// Vryshta koncentrator i chetec za Bulsyst card readers
begin
  declare @Concentrator integer;
  declare @Reader integer;
  declare @ConcentratorStr varchar(7);
  declare @ReaderStr varchar(7);
  declare res varchar(7);
  select Concentrator,Reader into @Concentrator,@Reader from BulSystAccess
  where RoomN=@RoomNo;
  set @ConcentratorStr=cast(@Concentrator as varchar);
  set @ReaderStr=cast(@Reader as varchar);
  message length(@ConcentratorStr);
  message length(@ReaderStr);
  set @ConcentratorStr=substr('000',1,3-length(@ConcentratorStr))||@ConcentratorStr;
  set @ReaderStr=substr('000',1,3-length(@ReaderStr))||@ReaderStr;
  set Res=@ConcentratorStr||':'||@ReaderStr;
  return(Res)
end
// [v 25.11.2005] Jorko new function