spBulkAddUpdate_BackOfficeUsers
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ClientsId | int | IN | |
| @BackOfficeSystemId | int | IN | |
| @BackOfficeSystemIdRE | int | IN | |
| @PayloadXml | ntext | IN |
Definition
Copy
CREATE procedure [dbo].[spBulkAddUpdate_BackOfficeUsers](
@ClientsId integer,
@BackOfficeSystemId integer,
@BackOfficeSystemIdRE integer,
@PayloadXml ntext
)
as
begin
set nocount on
declare @SQLError integer
declare @recsAdded integer
declare @recsUpdated integer
declare @recsErr integer
declare @MAX_PWD_LEN integer
declare @relinkRecords bit
set @MAX_PWD_LEN = 40
set @relinkRecords = 0
if not exists(select * from BackOfficeSystems where ID = @BackOfficeSystemId)
begin
raiserror ('Invalid BackOffice System Id',16,1)
return
end
declare @idoc int
declare @users table(BackOfficeId integer,
UserName nvarchar(100) COLLATE database_default,
[Password] nvarchar(100) COLLATE database_default,
FirstName nvarchar(100) COLLATE database_default,
LastName nvarchar(100) COLLATE database_default,
EMail nvarchar(200) COLLATE database_default,
RE7RecordsId integer null, -- the RE7RecordId from the BackOfficeSystem.
ClientUsersId integer null,
BackOfficeUsersId integer null,
IsNew bit not null default 1,
InsertBOSRec bit not null default 1, -- if true we need to insert a record into BackOfficeSystemPeople table
UpdateUserName bit not null default 0,
ErrMsg nvarchar(512) COLLATE database_default null ,
Added bit not null default 0,
UpdatedClientUsersID integer null, --see notes below
UpdatedBOSUID integer null --see notes below
)
exec sp_xml_preparedocument @idoc output, @PayloadXml
insert into @users(BackOfficeId, UserName, Password, FirstName, LastName, EMail, RE7RecordsId)
select BackOfficeId, UserName, Password, FirstName, LastName, EMail, RE7RecordsId
from OPENXML(@idoc, 'Payload/users/u', 1)
with (BackOfficeId integer, UserName nvarchar(100), Password nvarchar(100), FirstName nvarchar(100), LastName nvarchar(100), EMail nvarchar(200), RE7RecordsId integer)
exec sp_xml_removedocument @idoc
update @users set RE7RecordsId = null where RE7RecordsId = 0
-- remove any quarantined records
delete @users
from @users a
inner join dbo.BackOfficeUserImportErrRecords b on b.BackOfficeId = a.BackOfficeId
where b.BackOfficeSystemsId = @BackOfficeSystemId and b.Quarantine = 1
-- determine if we already have a user record via RE
update @users set IsNew = 0, ClientUsersId = c.ClientUsersID, InsertBOSRec = 1, BackOfficeUsersId=c.ID
from @users a
inner join dbo.BackOfficeSystemPeople b on b.BackOfficeRecordId = a.RE7RecordsId
inner join dbo.BackOfficeSystemUsers c on c.BackOfficePeopleId = b.ID
where b.BackOfficeSystemID = @BackOfficeSystemIdRE
-- determine if BackOffice record already exists
update @users set IsNew = 0, InsertBOSRec = 0, ClientUsersId = c.ClientUsersId, BackOfficeUsersId=c.ID
from @users a
inner join dbo.BackOfficeSystemPeople b on b.BackOfficeRecordId = a.BackOfficeId
inner join dbo.BackOfficeSystemUsers c on c.BackOfficePeopleId = b.Id
where b.BackOfficeSystemID = @BackOfficeSystemId
if (@BackOfficeSystemId <> @BackOfficeSystemIdRE)
--It is possible that a current record has since become linked with an RE record and that RE record is an existing BBNC User
--So we need to change the existing BackOfficeSystemUsers record so that it points to the ClientUser record for that RE record.
--of course, this will "de-link" the other clientuser record.
--
begin
update @users set UpdatedClientUsersID=c.clientusersID, UpdatedBOSUID=c.ID
from @users a
inner join dbo.BackOfficeSystemPeople b on b.BackOfficeRecordId = a.RE7RecordsID
inner join dbo.BackOfficeSystemUsers c on c.BackOfficePeopleId = b.Id
where InsertBOSRec=0 and IsNew=0 and c.clientusersID<>a.clientusersID and a.ClientUsersId is not null
and b.BackOfficeSystemID = @BackOfficeSystemIdRE
select @relinkRecords= count(*) from @users a where a.UpdatedClientUsersID is not null
end
-- determine if UserName is taken for records we are trying to insert
update @users set ErrMsg = 'User ID ' + a.UserName + ' already exists', ClientUsersId = b.ID
from @users a
inner join dbo.ClientUsers b on b.UserName = a.UserName
where a.IsNew = 1
-- determine of the password length is <= 40
update @users set ErrMsg = 'Password cannot be more than ' + cast(@MAX_PWD_LEN as char(2)) + ' characters'
where LEN(password) > @MAX_PWD_LEN
-- check for null usernames
update @users set ErrMsg = 'User ID is required'
where UserName is null
-- determine if there are duplicates in records we are trying to insert
update @users set ErrMsg = 'Duplicate User ID'
from @users a
inner join(
select UserName from @users
group by UserName having count(UserName) > 1
) b on b.UserName = a.UserName
where a.IsNew = 1 and a.ErrMsg is null
begin tran T1
if (@relinkRecords=1)
begin
--see notes above
update dbo.BackOfficeSystemUsers set clientusersid=a.UpdatedClientUsersID
from @users a
inner join dbo.BackOfficeSystemUsers c on a.BackOfficeUsersId=c.id
where a.UpdatedClientUsersID is not null
update dbo.ClientUsers set MembershipRefreshedOn=null from @users a inner join dbo.ClientUsers cu on a.UpdatedClientUsersID=cu.id
update dbo.ClientUsers set MembershipRefreshedOn=null from @users a inner join dbo.ClientUsers cu on a.clientusersid=cu.id where UpdatedClientUsersID is not null
update @users set clientusersid=UpdatedClientUsersID where UpdatedClientUsersID is not null
end
insert into ClientUsers(UserName, Password, Active, LastName, FirstName, EMail, InternalUser, ClientsID, IsSupervisor, IsProvisional)
select UserName, isnull(Password, ''), 1, LastName, FirstName, EMail, 0, @ClientsId, 0, 0
from @users
where ErrMsg is null and IsNew = 1
set @SQLError = @@error
if @SQLError = 0
begin
--Update our local table with the ids of those just added to NC
-- and mark them as successfully added
update @users set ClientUsersId = a.ID, added = 1
from dbo.ClientUsers a
inner join @users b on a.UserName = b.UserName
and a.Password = b.Password
and a.FirstName = b.FirstName
and a.LastName = b.LastName
and a.ClientsID = @ClientsId
and b.ErrMsg is null
and b.IsNew = 1
-- For @BackOfficeSystemId
insert into BackOfficeSystemPeople(BackOfficeSystemId, BackOfficeRecordId)
select @BackOfficeSystemId, a.BackOfficeId
from @users a
where a.ClientUsersId is not null and a.ErrMsg is null and a.InsertBOSRec = 1
and not exists(
select * from dbo.BackOfficeSystemPeople c
where c.BackOfficeSystemId = @BackOfficeSystemId
and c.BackOfficeRecordId = a.BackOfficeId
)
-- For Raiser's Edge
insert into BackOfficeSystemPeople(BackOfficeSystemId, BackOfficeRecordId)
select @BackOfficeSystemIdRE, a.RE7RecordsId
from @users a
where a.ClientUsersId is not null and a.ErrMsg is null and a.InsertBOSRec = 1 and a.RE7RecordsId is not null
and not exists(
select * from dbo.BackOfficeSystemPeople c
where c.BackOfficeSystemId = @BackOfficeSystemIdRE
and c.BackOfficeRecordId = a.RE7RecordsId
)
if (@BackOfficeSystemId <> @BackOfficeSystemIdRE)
begin
-- It is possible that a previously imported user has since been integrated with a RE record.
insert into BackOfficeSystemPeople(BackOfficeSystemId, BackOfficeRecordId)
select @BackOfficeSystemIdRE, a.RE7RecordsId
from @users a
where a.ClientUsersId is not null and a.ErrMsg is null and a.InsertBOSRec = 0 and a.RE7RecordsId is not null
and not exists(
select * from dbo.BackOfficeSystemPeople c
where c.BackOfficeSystemId = @BackOfficeSystemIdRE
and c.BackOfficeRecordId = a.RE7RecordsId
)
-- Map these new RE peeps to bosUsers
insert into BackOfficeSystemUsers(BackOfficePeopleId, ClientUsersId)
select a.Id, b.ClientUsersId
from dbo.BackOfficeSystemPeople a
inner join @users b on b.RE7RecordsId = a.BackOfficeRecordId
where b.ClientUsersId is not null and b.ErrMsg is null and
b.InsertBOSRec = 0 and a.BackOfficeSystemID= @BackOfficeSystemIdRE
and not exists(
select * from dbo.BackOfficeSystemUsers c inner join dbo.BackOfficeSystemPeople peeps on c.BackOfficePeopleId=peeps.id
where peeps.BackOfficeSystemId = @BackOfficeSystemIdRE
and peeps.BackOfficeRecordId = a.BackOfficeRecordId
)
end
-- Map ClientUsers to @BackOfficeSystemId
insert into BackOfficeSystemUsers(BackOfficePeopleId, ClientUsersId)
select a.Id, b.ClientUsersId
from dbo.BackOfficeSystemPeople a
inner join @users b on b.BackOfficeId = a.BackOfficeRecordId
where b.ClientUsersId is not null and b.ErrMsg is null and
b.InsertBOSRec = 1 and a.BackOfficeSystemID= @BackOfficeSystemId
--sterling CR327498-010810
--make sure BackofficeSystemUsers entries are flagged as Current for all successes in this run
UPDATE BackOfficeSystemUsers SET [Current] = 1
FROM BackOfficeSystemUsers bosu
INNER JOIN BackOfficeSystemPeople bosp on bosu.[Current] = 0 AND bosp.BackOfficeSystemID = @BackOfficeSystemId AND bosu.BackofficePeopleID = bosp.ID
INNER JOIN @users u on u.BackOfficeId = bosp.BackofficeRecordID
where u.ClientUsersId is not null and u.ErrMsg is null
-- Map ClientUsers to @BackOfficeSystemIdRE
insert into BackOfficeSystemUsers(BackOfficePeopleId, ClientUsersId)
select a.Id, b.ClientUsersId
from dbo.BackOfficeSystemPeople a
inner join @users b on b.RE7RecordsId = a.BackOfficeRecordId
where b.ClientUsersId is not null and b.ErrMsg is null and
b.InsertBOSRec = 1 and b.RE7RecordsId is not null and a.BackOfficeSystemID= @BackOfficeSystemIdRE
--sterling CR327498-010810
--make sure BackofficeSystemUsers entries are flagged as Current for all successes in this run
UPDATE BackOfficeSystemUsers SET [Current] = 1
FROM BackOfficeSystemUsers bosu
INNER JOIN BackOfficeSystemPeople bosp on bosu.[Current] = 0 AND bosp.BackOfficeSystemID = @BackOfficeSystemIdRE AND bosu.BackofficePeopleID = bosp.ID
INNER JOIN @users u on u.RE7RecordsId = bosp.BackofficeRecordID
where u.ClientUsersId is not null and u.ErrMsg is null
set @SQLError = @@error
if @SQLError = 0
begin
select @recsAdded = count(*) from @users a
where a.ClientUsersId is not null and a.ErrMsg is null and a.IsNew = 1
-- update existing users' FirstName, LastName, EMail which may have been updated from the backoffice
update dbo.ClientUsers
set LastName = a.LastName, FirstName = a.FirstName, EMail = a.EMail
from @users a
inner join dbo.BackOfficeSystemPeople b on b.BackOfficeRecordId = a.BackOfficeId
inner join dbo.BackOfficeSystemUsers c on c.BackOfficePeopleId = b.Id
inner join dbo.ClientUsers d on d.ID = c.ClientUsersID
where a.ErrMsg is null and a.IsNew = 0
and d.ClientsID = @ClientsId and b.BackofficeSystemID = @BackofficeSystemId
set @SQLError = @@error
if @SQLError = 0
begin
commit tran T1
select @recsUpdated = count(*) from @users a
where a.ClientUsersId is not null and a.ErrMsg is null
and a.IsNew = 0
end
else
rollback tran T1
end
else
rollback tran T1
end
else
begin
rollback tran T1
insert into BackOfficeUserImportErrRecords(BackOfficeSystemsId, BackOfficeId, RE7RecordsId, UserName, Password, FirstName,
LastName, EMail, ClientUsersId, ErrMsg)
select @BackOfficeSystemId, BackOfficeId, RE7RecordsId, UserName, Password, FirstName,
LastName, EMail, ClientUsersId, ErrMsg
from @users a
where ErrMsg is null
and IsNew = 1
and not exists(select * from dbo.BackOfficeUserImportErrRecords b
where b.BackOfficeId = a.BackOfficeId
and b.BackOfficeSystemsId = @BackOfficeSystemId)
end
-- Save the Error records from this run to BackOfficeUserImportErrRecords
update dbo.BackOfficeUserImportErrRecords
set UserName = a.UserName, Password = a.Password, FirstName = a.FirstName, LastName = a.LastName, EMail = a.EMail,
ClientUsersId = a.ClientUsersId, ErrMsg = a.ErrMsg
from @users a
inner join dbo.BackOfficeUserImportErrRecords b on a.BackOfficeId = b.BackOfficeId
where a.ErrMsg is not null and b.BackOfficeSystemsId = @BackOfficeSystemId
insert into BackOfficeUserImportErrRecords(BackOfficeSystemsId, BackOfficeId, RE7RecordsId, UserName, Password, FirstName,
LastName, EMail, ClientUsersId, ErrMsg)
select @BackOfficeSystemId, BackOfficeId, RE7RecordsId, UserName, Password, FirstName,
LastName, EMail, ClientUsersId, ErrMsg
from @users a
where ErrMsg is not null
and not exists(select * from dbo.BackOfficeUserImportErrRecords b
where b.BackOfficeId = a.BackOfficeId
and b.BackOfficeSystemsId = @BackOfficeSystemId)
INSERT INTO [userImportLog]([BackOfficeId], [UserName], [Password], [FirstName], [LastName], [EMail], [RE7RecordsId], [ClientUsersId], [BackOfficeUsersId], [IsNew], [InsertBOSRec], [UpdateUserName], [ErrMsg], [Added], [UpdatedClientUsersID], [UpdatedBOSUID])
SELECT [BackOfficeId], [UserName], [Password], [FirstName], [LastName], [EMail], [RE7RecordsId], [ClientUsersId], [BackOfficeUsersId], [IsNew], [InsertBOSRec], [UpdateUserName], [ErrMsg], [Added], [UpdatedClientUsersID], [UpdatedBOSUID] FROM @users
select @recsErr = count(*) from @users where ErrMsg is not null
-- stats
select @recsAdded as 'NumAdded', @recsUpdated as 'NumUpdated', @recsErr 'NumError'
-- resultset1 for sending email
select * from @Users where added = 1
-- resultset2 for synching logins with EE
select a.BackofficeId, a.ClientUsersId, b.UserName, b.Password
from @Users a INNER JOIN dbo.ClientUsers b on a.ClientUsersId = b.Id
where a.ClientUsersId is not null and a.ErrMsg is null
end