/*ALIAS>l_data*/

/*SQLStart>1028*/
DECLARE EXTERNAL FUNCTION ATRIMEX CSTRING(250) RETURNS CSTRING(250) FREE_IT ENTRY_POINT 'ATrimEx' MODULE_NAME 'strtool';
/*SQLStop*/

/*SQLStart>1029*/
ALTER TABLE USERS
  ADD USER_NAME VARCHAR(300),
  ADD USER_TITLE VARCHAR(300),
  ADD USER_POSITION VARCHAR(300),
  ADD USER_ACTIVE BOOLEAN;
/*SQLStop*/
  
/*SQLStart>1030*/
UPDATE USERS SET USER_NAME=USER_RNM,USER_ACTIVE=1;
/*SQLStop*/

/*SQLStart>1031*/
CREATE OR ALTER trigger users_biu0 for users
active before insert or update position 0
AS
begin
  if ((new.user_name is null) or (new.user_name = ''))  then
  begin
    new.user_name=new.user_rnm;
  end
  if (new.user_active is null) then
  begin
    new.user_active=1;
  end
end
/*SQLStop*/

/*SQLStart>1032*/
create or alter procedure AEUM_USER_UPDATE (
    ID integer,
    APPNAME varchar(100),
    USERNAME varchar(300),
    TITLE varchar(300),
    FIRST_NAME varchar(300),
    MIDDLE_NAME varchar(300),
    LAST_NAME varchar(300),
    PID varchar(300),
    POSITION_TEXT varchar(300))
as
declare variable RES integer;
begin
  select u1.user_id from users u1
  where u1.user_id = :id
  into :res;
  
  if (:res is Null) then
    begin
      insert into users (user_id, user_rnm, user_name, user_title, user_position)
      values (:id,atrimex(:first_name || " " || :middle_name || " " || :last_name), :username, :title, :position_text);
    end
  else
    begin
      update users u2 set
        u2.user_rnm=atrimex(:first_name || " " || :middle_name || " " || :last_name),
        u2.user_name=:username,
		u2.user_position=:position_text,
		u2.user_title=:title
      where u2.user_id = :id;
    end
end
/*SQLStop*/

/*SQLStart>1033*/
GRANT EXECUTE ON PROCEDURE AEUM_USER_UPDATE TO PUBLIC;
/*SQLStop*/

/*SQLStart>1034*/
create or alter procedure AEUM_USER_DELETE (
    ID integer,
    APPNAME varchar(100))
as
begin
  update users u1 set u1.user_active=0 where u1.user_id=:id;
end
/*SQLStop*/

/*SQLStart>1035*/
GRANT EXECUTE ON PROCEDURE AEUM_USER_DELETE TO PUBLIC;
/*SQLStop*/

/*SQLStart>1036*/
delete from system_strings where SYS_ID = 0;
/*SQLStop*/

/*SQLStart>1037*/
insert into system_strings (SYS_ID,SYS_STR) values (0,'1.5.52.1');
/*SQLStop*/
