rapport.sql

DECLARE
  @UsernameLogin bit,
  @AlterInlog bit,
  @execute varchar(max)
  

set @UsernameLogin = (select isnull((select VALUE_BOOLEAN from ZSET_Settings where SETTINGKEY = 'Logon.EnableUsernameLogin'), 0))
set @AlterInlog = (select isnull((select 1 from syscolumns where name = '_INLOGNAAM' and id = (select id from sysobjects where name = 'PERS_Personen')), 0))

IF @UsernameLogin = 0
BEGIN

set @execute = 'insert into ZWUM_WebUserMemberships (USER_ID, REL_NR, PERS_NR, UserId)
 select zwur.[user_id], rlat.rel_nr, pers.pers_nr, users.UserId
 from users
 join pers_personen pers on pers.email_128 = users.UserName
 join rlat_relatie rlat on rlat.pers_nr_1 = pers.pers_nr
 join zwur_WebUsers zwur on zwur.rel_nr = rlat.rel_nr
 where rlat.rltp_id = -1 and pers_nr not in (select pers_nr from ZWUM_WebUserMemberships)'
  
END
ELSE
BEGIN

set @execute = 'insert into ZWUM_WebUserMemberships (USER_ID, REL_NR, PERS_NR, UserId)
 select zwur.[user_id], rlat.rel_nr, pers.pers_nr, users.UserId
 from users
 join pers_personen pers on pers._INLOGNAAM = users.UserName
 join rlat_relatie rlat on rlat.pers_nr_1 = pers.pers_nr
 join zwur_WebUsers zwur on zwur.rel_nr = rlat.rel_nr
 where rlat.rltp_id = -1 and pers_nr not in (select pers_nr from ZWUM_WebUserMemberships)'

END

IF @AlterInlog = 1
BEGIN

exec (@execute)

create table [dbo]._IMLO_inlognaam(pers_nr int, _INLOGNAAM varchar(100))

set @execute = 'INSERT INTO _IMLO_inlognaam(pers_nr, _INLOGNAAM) select pers_nr, _INLOGNAAM from PERS_PERSONEN'
exec(@execute)

set @execute = 'ALTER TABLE PERS_Personen DROP COLUMN [_INLOGNAAM]'
exec(@execute)

END

delete from ZSET_Settings where SETTINGKEY = 'Logon.EnableUsernameLogin'