sp_UserImportValidate

CREATE Procedure [dbo].[sp_UserImportValidate]
        (
            @IMPORTID int,
            @UNIQUELOGIN bit,
            @BACKOFFICESYSTEM int
        )
        AS
        set nocount on

        DECLARE @tempStatus tinyint
        DECLARE @Continue int
        DECLARE @Incrementor int
        DECLARE @RecordCount int
        DECLARE @RecsProcessed int
        DECLARE @tempTable table
        (
        UIMLID int,
        UserName nvarchar(50) collate database_default,
        TestUserName nvarchar(50) collate database_default,
        [Status] tinyint
        )

        --Clear error conditions from a previous validation
        update dbo.UserImportMasterList set [Status]=0 where [Status] in (4,5) and [UserImportID]=@IMPORTID 

        --Already linked to a user
        update dbo.UserImportMasterList
        set [Status]=4, [StatusTxt] = 'This record is already linked to a website user', [ClientUsersID]=bosu.ClientUsersID
        from backofficesystemusers bosu 
            inner join backofficesystempeople bosp on bosu.BackOfficePeopleID=bosp.id and bosp.BackOfficeSystemId=0 
            inner join UserImportMasterList uiml on uiml.BackofficeID=bosp.backofficeRecordID
        where [Status] =0 and [UserImportID]=@IMPORTID 

        --Duplicate backofficeID specified in the import
        update dbo.UserImportMasterList
        set [Status]=4, [StatusTxt] = 'This record is already included in this import'
        where id <> (select top 1 id from UserImportMasterList uiml2 
                where uiml2.[Status]=0 and uiml2.[UserImportID]=@IMPORTID and uiml2.BackofficeID=UserImportMasterList.BackofficeID and uiml2.BackOfficeSystemID=UserImportMasterList.BackOfficeSystemID)
        and [Status]=0 and [UserImportID]=@IMPORTID 

        --Check for organization constituents
        update dbo.UserImportMasterList
        set [Status]=4, [StatusTxt] = 'Website users may not be linked to organization records'
        from BackOfficeSystemPeople bosp
            inner join UserImportMasterList uiml on uiml.BackofficeID = bosp.BackofficeRecordID
            inner join Constituent C on bosp.BackofficeRecordID = C.SEQUENCEID
        where C.ISORGANIZATION = 1
        and [Status]=0 and [UserImportID]=@IMPORTID 


        if (@UNIQUELOGIN=1)
            set @tempStatus=3
        else    
            set @tempStatus=5

        --Look for usernames in use
        update dbo.UserImportMasterList
        set [Status]=@tempStatus, [StatusTxt] = 'This login is in use'
        from clientUsers cu 
            inner join UserImportMasterList uiml on uiml.UserName=cu.username
        where [Status] =0 and [UserImportID]=@IMPORTID 

        --Duplicate username specified in the import
        update dbo.UserImportMasterList
        set [Status]=@tempStatus, [StatusTxt] = 'This username is already included in this import'
        where id <> (select top 1 (id) from UserImportMasterList uiml2 where uiml2.[Status]=0 and uiml2.[UserImportID]=@IMPORTID and  uiml2.[Username]=UserImportMasterList.[Username])
        and [Status]=0 and [UserImportID]=@IMPORTID 


        --For any that are in use we need to append a # until it is unique
        if (@UNIQUELOGIN=1) BEGIN
            set @Incrementor=1

            --build a temptable of all the records w/duplicate usernames
            INSERT INTO @tempTable ([UIMLID],[Username],[Status])
                SELECT ID, baseUserName, @tempStatus     
                FROM [dbo].[UserImportMasterList]
                where [Status] =@tempStatus and [UserImportID]=@IMPORTID

            select @Continue=count(*) from  @tempTable where [Status] =@tempStatus

            WHILE (@Continue>0) BEGIN

                --Add a number to the username
                UPDATE @tempTable set [TestUserName]=[UserName]+convert(nvarchar(10),@Incrementor) where [Status] =@tempStatus

                --If not in use, then clear the status flag
                UPDATE @tempTable  set [Status]=0 
                    FROM @tempTable t
                    where t.status=@tempstatus AND not exists(select id from clientusers cu where cu.username=t.[TestUserName] and t.[Status]=@tempStatus)

                --Check the temp table for any new duplicates
                UPDATE @tempTable set [Status]=@tempStatus
                    FROM @tempTable t
                    where (t.[Status] =0) and t.UIMLID <> (select top 1 UIMLID from @tempTable it where it.[TestUserName]=t.[TestUserName] and it.[Status]=0 )


                --Check the import file and see if there are any that are now duplicated
                UPDATE @tempTable set [Status]=@tempStatus 
                    FROM @tempTable t
                    where t.[status]=0 and exists(select id from UserImportMasterList where UserImportMasterList.[UserImportID]=@IMPORTID and 
                                        UserImportMasterList.[username]=t.[TestUserName] and t.[Status]=0 and UserImportMasterList.[Status]=0)


                set @Incrementor=@Incrementor+1            
                select @Continue=count(*) from  @tempTable where [Status] =@tempStatus
            END

            UPDATE dbo.UserImportMasterList
                SET [Status]=0, [StatusTxt]=NULL, [Username]=T.TestUserName 
                FROM [dbo].[UserImportMasterList] 
                inner join @tempTable T on T.UIMLID=UserImportMasterList.ID


        END

        -- Create a string pattern containing the bad ascii chars that will cause Email processing code to fail
        -- If using a local variable for a pattern, it MUST be declared with a length
        declare @invalidChars nvarchar(36) = '%['
        declare @i int = 0
        while @i < 32
        begin
            select @invalidChars = @invalidChars + char(@i),
                @i = @i+1
        end
        select @invalidChars = @invalidChars + ']%'


        -- Check for invalid characters in a row
        update dbo.UserImportMasterList
        set [Status]=6, [StatusTxt] = 'This record contains invalid characters'
        where [Status]=0 and [UserImportID]=@IMPORTID 
        and (PATINDEX(@invalidChars, FirstName) > 0
            or PATINDEX(@invalidChars, LastName) > 0)

        SELECT @RecordCount=count(*) from UserImportMasterList where [UserImportID]=@IMPORTID 
        SELECT @RecsProcessed =count(*) from UserImportMasterList where [Status] =0 and [UserImportID]=@IMPORTID 

        UPDATE dbo.UserImport set [NeedToProcessList]=0, [RecordCount]=@RecordCount, [RecordsProcessed]=@RecsProcessed where ID=@IMPORTID