USP_CREATE_FAF_LOGINUSERID_FROMPRIMARYEMAIL

CREATE procedure dbo.USP_CREATE_FAF_LOGINUSERID_FROMPRIMARYEMAIL
As

set nocount on

-- check if FAF is installed

if not exists(select ID from dbo.INSTALLEDPRODUCTLIST where ID ='a919502c-a2f6-4a56-9183-28e3f667916e')
begin
   return 1
end

DECLARE @ACTIVECLIENTUSERS TABLE
  (
       ID             int IDENTITY(1,1),
       ClIENTUSERID   int,
       CLIENTSID      int
  )

 Insert into @ACTIVECLIENTUSERS(ClIENTUSERID, CLIENTSID)
    Select ID, CLIENTSID from dbo.ClientUsers
        where Deleted = 0
        order by CreateDate desc


 -- insert user data

 declare @count  int
 set @count = 1

 declare @numUsers   int
 select @numUsers = MAX(ID) from @ACTIVECLIENTUSERS

 declare @curPrimaryEmail   varchar(200),
         @curClientUserID   int,
         @curClientsID      int,
         @newClientUserID   int

 -- start loop transaction for clientusers

 while @count <= @numUsers
 begin
     Select @curPrimaryEmail = ea.EMAILADDRESS,
            @curClientUserID = acu.ClIENTUSERID,
            @curClientsID = acu.CLIENTSID
          From @ACTIVECLIENTUSERS acu 
            inner join dbo.BackOfficeSystemUsers bosu (nolock)  
              on acu.ClIENTUSERID = bosu.ClientUsersID AND bosu.[current] = 1   
            inner join dbo.BackOfficeSystemPeople bosp (nolock)    
              on bosp.ID = bosu.BackofficePeopleID AND bosp.BackOfficeSystemID = 0    
            inner join dbo.CONSTITUENT C (nolock)    
              on bosp.BackofficeRecordID = C.SEQUENCEID
            inner join dbo.EMAILADDRESS ea (nolock)   
              on ea.CONSTITUENTID = c.ID and ea.ISPRIMARY = 1
            Where acu.ID = @count

     -- create login user

     if @curPrimaryEmail is not null and len(@curPrimaryEmail) <= 50 and
             not exists(Select ID from dbo.ClientUsers (nolock)   
                     where ClientsID = @curClientsID and UserName = @curPrimaryEmail)
     begin try
         insert into dbo.ClientUsers      
            ([Password], UserName, EMail, Active,
             LastName, FirstName, MiddleInitial, InternalUser,
             ClientsID, RegistrationNumber, ReminderPhrase, IsSupervisor,
             IsProvisional, TimeZoneID)
         Select 
             [Password], @curPrimaryEmail, EMail, Active,
             LastName, FirstName, MiddleInitial, InternalUser,
             ClientsID, RegistrationNumber, ReminderPhrase, IsSupervisor,
             IsProvisional, TimeZoneID 
         from dbo.ClientUsers (nolock)   
         where ClientUsers.ID = @curClientUserID 

         select @newClientUserID = ID from dbo.ClientUsers (nolock) 
           where ClientsID = @curClientsID and UserName = @curPrimaryEmail

         -- add backoffice id

         insert into dbo.BackOfficeSystemUsers
            (BackofficePeopleID, ClientUsersID, [Current])
         select BackofficePeopleID, @newClientUserID, [Current]
             from dbo.BackOfficeSystemUsers
         where ClientUsersID = @curClientUserID 


         -- copy user role

         insert into dbo.UserRoles(ClientUsersID, ClientRolesID, ManuallyAdded)
             select @newClientUserID, ClientRolesID, ManuallyAdded
               from dbo.UserRoles (nolock)   
               where ClientUsersID = @curClientUserID 

     end try

     -- track error

     begin catch
       exec dbo.USP_RAISE_ERROR;    
       return 1
     end catch

     -- add loop count

     set @count = @count + 1

 end -- end of loop