USE thejanitor
DECLARE @pID INT
DECLARE @pForename VARCHAR(100)
DECLARE @pSurname VARCHAR(100)
DECLARE @pUserName VARCHAR(100)
DECLARE @pucID INT
DECLARE @pClients VARCHAR(50)
DECLARE Users CURSOR FOR
SELECT DISTINCT id, forename , surname , username
FROM navitas.dbo.users
WHERE Salt IS NOT NULL
AND forename <> 'Deleted'
AND (forename IS NOT NULL
AND surname IS NOT NULL
AND username IS NOT NULL)
AND id NOT IN (SELECT SiRPSID 
FROM MigrationDB.dbo.MigrationLookup
WHERE SiRPSTableName = 'users')
OPEN Users
FETCH NEXT FROM Users 
INTO @pID,@pForename,@pSurname,@pUserName
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO thejanitor.dbo.users ([forename], [surname], [displayname],  
[job_title], [department], [username], [password], [salt], [default_contract], 
[active_since], [last_logon], [force_password_change], [active], [visible], 
[show_admin], [reset], [application_version])	
SELECT DISTINCT [forename], [surname], [display_name], [job_title], [department], 
[username], [password], [salt], [default_client], [active_since], [last_logon], 
[force_password_change], [active], [visible], [show_admin], [reset], 
[application_version]
FROM [navitas].[dbo].[users]
WHERE forename = @pForename 
AND surname = @pSurname
AND username = @pUsername
AND salt IS NOT NULL
AND forename <> 'deleted'
AND id = @pID
IF @@ERROR <> 0
BEGIN
INSERT INTO [MigrationDB].[dbo].[ErrorLogging] ([ErrorDetails]) 
VALUES ('error occured when inserting users. ~ SiRPS ID:'+ CONVERT(VARCHAR,@pID))
END
ELSE
BEGIN			
DECLARE @pOID INT
SET @pOID = SCOPE_IDENTITY()
EXECUTE [MigrationDB].[dbo].[AddDataTolookup] @pID,@pOID,'users'
END
DECLARE clients CURSOR FOR
SELECT id,[name] 
FROM navitas.dbo.client 
WHERE [name] 
IN (SELECT [name] 
FROM navitas.dbo.user_client
WHERE [user_id] = @pID)
OPEN clients
FETCH clients INTO @pucID,@pClients
WHILE (@@FETCH_STATUS = 0)
BEGIN
DECLARE @pContractID INT
DECLARE @pUserId INT
SELECT DISTINCT @pUserId = id
FROM thejanitor.dbo.users
WHERE forename = @pForename
AND surname = @pSurname
AND username = @pUserName
SELECT DISTINCT @pContractID = id 
FROM thejanitor.dbo.contracts
WHERE [name] = @pClients
IF (@pContractID IS NULL)
BEGIN 
PRINT @pID+ @pForename+@pSurname+@pUserName
END
ELSE
BEGIN
INSERT INTO thejanitor.dbo.user_contract ([user_id], contract_id)
VALUES (@pUserId,@pContractID)
END
FETCH clients INTO @pucID,@pClients
END
CLOSE clients
DEALLOCATE clients
FETCH NEXT 
FROM Users 
INTO @pID,@pForename,@pSurname,@pUserName
END
CLOSE Users
DEALLOCATE Users
GO
DECLARE @pMeID INT
DECLARE Client CURSOR FOR 
(SELECT JanitorID 
FROM [MigrationDB].dbo.MigrationLookup
WHERE SiRPSTableName = 'client')
OPEN Client
FETCH NEXT FROM Client 
INTO @pMeID
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO thejanitor.dbo.user_contract ([user_id],contract_id)
VALUES (94,@pMeID)
FETCH NEXT FROM Client 
INTO @pMeID
END
CLOSE Client
DEALLOCATE Client