Appendixes |
This appendix lists the stored procedures used to import Active Directory users from a comma-separated file.
Stored Procedure : ImportActiveDirectoryUsers
USE [MVisumAlerts] GO /****** Object: StoredProcedure [dbo].[ ImportActiveDirectoryUsers] Script Date: 06/02/2015 12:32:15 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[ImportActiveDirectoryUsers]( @bulkUserInfo VARCHAR(MAX), @ServerIP VARCHAR(50), @DomainName VARCHAR(200) ) as BEGIN DECLARE @UserPipeSaperatedStrig Varchar(MAX), @SessionID VARCHAR(200), @IsImportUser BIT, @RoleId INT, @GroupId INT, @UnitId INT, @MobileTypeID INT, @Status VARCHAR(100), @HospitalId INT, @DomainId INT, @UserName VARCHAR(200), @FirstName VARCHAR(200), --@MiddleName VARCHAR(100), @LastName VARCHAR(200), @Email VARCHAR(200), @TelephoneNumber VARCHAR(100), @GroupName VARCHAR(200), @UnitName VARCHAR(200), @Index INT DECLARE @UserList TABLE ( FirstName VARCHAR(200), --MiddleName VARCHAR(100), LastName VARCHAR(200), Email VARCHAR(200), TelephoneNumber VARCHAR(100), UserName VARCHAR(200), GroupName VARCHAR(200), UnitName VARCHAR(200) ) INSERT @UserList(FirstName,LastName,Email,TelephoneNumber,UserName,GroupName,UnitName) EXEC [dbo].[GeBulkUsersInTable] @bulkUserInfo select * from @UserList SET @SessionID=(SELECT SessionId FROM [Session] where LogoutTime is null) SET @IsImportUser=1 SET @RoleId=(SELECT RoleId FROM Roles WHERE RoleName = 'NURSE') SET @MobileTypeID=(SELECT Id FROM MobileTypes WHERE DisplayName = 'Apple-iPhone-3.0') SET @Status='ACTIVE' SET @HospitalId=(SELECT TOP 1 HospId FROM Hospitals) SET @DomainId=(SELECT ad.Id FROM ActiveDirectoryDomains ad LEFT JOIN ActiveDirectorySettings ads ON ads.Id=ad.ADId WHERE ads.ServerIP=@ServerIP AND ad.DomainName=@DomainName ) SET @UserPipeSaperatedStrig='[' SET @Index=0 DECLARE db_cursor CURSOR FOR SELECT FirstName,LastName,Email,TelephoneNumber,UserName,GroupName,UnitName from @UserList OPEN db_cursor FETCH NEXT FROM db_cursor INTO @FirstName,@LastName,@Email,@TelephoneNumber,@UserName,@GroupName,@UnitName WHILE @@FETCH_STATUS = 0 BEGIN print 'Inside Loop' SET @GroupId=(SELECT ID FROM HospitalGroups WHERE Name = @GroupName) SET @UnitId=(SELECT ID FROM HospitalUnits WHERE Name = @UnitName) IF(@Index>0) BEGIN SET @UserPipeSaperatedStrig=@UserPipeSaperatedStrig+',' END --For UserName SET @UserPipeSaperatedStrig=@UserPipeSaperatedStrig +'"'+ @UserName +'|' --For First Name SET @UserPipeSaperatedStrig=@UserPipeSaperatedStrig +''+ @FirstName +'|' --For Last Name SET @UserPipeSaperatedStrig=@UserPipeSaperatedStrig +''+ @LastName +'|' --For Description SET @UserPipeSaperatedStrig=@UserPipeSaperatedStrig +'|' --For Email SET @UserPipeSaperatedStrig=@UserPipeSaperatedStrig +''+ @Email +'|' --For Telephone Number SET @UserPipeSaperatedStrig=@UserPipeSaperatedStrig +''+ @TelephoneNumber +'|' --For StreetAddress SET @UserPipeSaperatedStrig=@UserPipeSaperatedStrig +'|' --For City SET @UserPipeSaperatedStrig=@UserPipeSaperatedStrig +'|' --For ZipPostalCode SET @UserPipeSaperatedStrig=@UserPipeSaperatedStrig +'|' --For StateProvince SET @UserPipeSaperatedStrig=@UserPipeSaperatedStrig +'|' --For CountryRegion SET @UserPipeSaperatedStrig=@UserPipeSaperatedStrig +'|' --For Role ID SET @UserPipeSaperatedStrig=@UserPipeSaperatedStrig +''+ CAST(@RoleId AS VARCHAR ) +'|' --For Unit ID SET @UserPipeSaperatedStrig=@UserPipeSaperatedStrig +''+ CAST(@UnitId AS VARCHAR ) +'|' --For Group ID SET @UserPipeSaperatedStrig=@UserPipeSaperatedStrig +''+ CAST(@GroupId AS VARCHAR ) +'|' --For MobileTYpeID SET @UserPipeSaperatedStrig=@UserPipeSaperatedStrig +''+ CAST(@MobileTypeID AS VARCHAR ) +'|' --For Status SET @UserPipeSaperatedStrig=@UserPipeSaperatedStrig +''+ @Status +'|' --For DomainID SET @UserPipeSaperatedStrig=@UserPipeSaperatedStrig +''+ CAST(@DomainId AS VARCHAR ) +'|' --For HospitalID SET @UserPipeSaperatedStrig=@UserPipeSaperatedStrig +''+ CAST(@HospitalId AS VARCHAR ) +'' SET @UserPipeSaperatedStrig=@UserPipeSaperatedStrig +'"' print @UserPipeSaperatedStrig SET @Index =@Index+1 FETCH NEXT FROM db_cursor INTO @FirstName,@LastName,@Email,@TelephoneNumber,@UserName,@GroupName,@UnitName END SET @UserPipeSaperatedStrig=@UserPipeSaperatedStrig +']' CLOSE db_cursor DEALLOCATE db_cursor print @UserPipeSaperatedStrig EXEC [dbo].[AddUpdateActiveDirectoryUsers] @UserPipeSaperatedStrig,@SessionID,@IsImportUser END
Stored Procedure : GeBulkUsersInTable
USE [MVisumAlerts] GO /****** Object: StoredProcedure [dbo].[ GeBulkUsersInTable] Script Date: 06/02/2015 14:03:08 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ======================================================================= -- Author: MVisum Inc. -- Create date: 07/16/2014 -- Description: Convert Active Directory Users Comma Saperated List to Table -- ======================================================================= CREATE PROCEDURE [dbo].[GeBulkUsersInTable] @UserListString VARCHAR(Max) AS BEGIN DECLARE @UserName VARCHAR(200) DECLARE @FirstName VARCHAR(200) --DECLARE @MiddleName VARCHAR(100) DECLARE @LastName VARCHAR(200) DECLARE @Email VARCHAR(200) DECLARE @TelephoneNumber VARCHAR(100) DECLARE @GroupName VARCHAR(200) DECLARE @UnitName VARCHAR(200) DECLARE @CommaIndex INT--,@TempEndIndex INT DECLARE @ColumnValue varchar(MAX) DECLARE @ColumnIndex INT /***Temporaty Variables***/ DECLARE @CurrentUserString VARCHAR(MAX) DECLARE @StartIndex INT,@EndIndex INT SELECT @UserName as UserName,@FirstName as FirstName,@LastName as LastName ,@Email as Email,@TelephoneNumber as TelephoneNumber,@GroupName as GroupName ,@UnitName as UnitName INTO #BulkUserInfoTable TRUNCATE TABLE #BulkUserInfoTable print @UserListString WHILE(CHARINDEX(CHAR(13)+CHAR(10),@UserListString) > 0) BEGIN Print 'INSIDE LOOP' SET @StartIndex = 0 SET @EndIndex = CHARINDEX(CHAR(13)+CHAR(10),@UserListString) SET @CurrentUserString=SUBSTRING(@UserListString,0,@EndIndex) print @CurrentUserString IF(@CurrentUserString <> '') BEGIN SET @ColumnIndex=0 Print 'INSIDE CONDITION' WHILE(CHARINDEX(',',@CurrentUserString) > 0) BEGIN SET @CommaIndex = CHARINDEX(',',@CurrentUserString) SET @ColumnValue=LTRIM(RTRIM(SUBSTRING(@CurrentUserString,0,@CommaIndex))) SET @CurrentUserString=SUBSTRING(@CurrentUserString,@CommaIndex+1,LEN(@CurrentUserString)) print @CurrentUserString print @ColumnValue IF @ColumnIndex=0 BEGIN SET @FirstName = (SELECT CASE WHEN @ColumnValue IS NOT NULL AND @ColumnValue <> '' THEN @ColumnValue ELSE NULL END) END IF @ColumnIndex=1 BEGIN SET @LastName = (SELECT CASE WHEN @ColumnValue IS NOT NULL AND @ColumnValue <> '' THEN @ColumnValue ELSE NULL END) END IF @ColumnIndex=2 BEGIN SET @Email = (SELECT CASE WHEN @ColumnValue IS NOT NULL AND @ColumnValue <> '' THEN @ColumnValue ELSE NULL END) END IF @ColumnIndex=3 BEGIN SET @TelephoneNumber =(SELECT CASE WHEN @ColumnValue IS NOT NULL AND @ColumnValue <> '' THEN @ColumnValue ELSE NULL END) END IF @ColumnIndex=4 BEGIN SET @UserName = (SELECT CASE WHEN @ColumnValue IS NOT NULL AND @ColumnValue <> '' THEN @ColumnValue ELSE NULL END) END IF @ColumnIndex=5 BEGIN SET @GroupName = (SELECT CASE WHEN @ColumnValue IS NOT NULL AND @ColumnValue <> '' THEN @ColumnValue ELSE NULL END) END IF (CHARINDEX(',',@CurrentUserString)=0) AND @ColumnIndex=5 BEGIN SET @UnitName = LTRIM(RTRIM(@CurrentUserString)) END SET @ColumnIndex=@ColumnIndex + 1 END INSERT INTO #BulkUserInfoTable( UserName, FirstName, LastName, Email, TelephoneNumber, GroupName, UnitName ) VALUES ( @UserName, @FirstName, @LastName, @Email, @TelephoneNumber, @GroupName, @UnitName ) END SET @UserListString=SUBSTRING(@UserListString,@EndIndex + 1,LEN(@UserListString)) END IF(@UserListString <> '') BEGIN SET @CurrentUserString = @UserListString SET @ColumnIndex=0 Print 'INSIDE CONDITION' WHILE(CHARINDEX(',',@CurrentUserString) > 0) BEGIN SET @CommaIndex = CHARINDEX(',',@CurrentUserString) SET @ColumnValue=LTRIM(RTRIM(SUBSTRING(@CurrentUserString,0,@CommaIndex))) SET @CurrentUserString=SUBSTRING(@CurrentUserString,@CommaIndex+1,LEN(@CurrentUserString)) print @CurrentUserString print @ColumnValue IF @ColumnIndex=0 BEGIN SET @FirstName = (SELECT CASE WHEN @ColumnValue IS NOT NULL AND @ColumnValue <> '' THEN @ColumnValue ELSE NULL END) END IF @ColumnIndex=1 BEGIN SET @LastName = (SELECT CASE WHEN @ColumnValue IS NOT NULL AND @ColumnValue <> '' THEN @ColumnValue ELSE NULL END) END IF @ColumnIndex=2 BEGIN SET @Email = (SELECT CASE WHEN @ColumnValue IS NOT NULL AND @ColumnValue <> '' THEN @ColumnValue ELSE NULL END) END IF @ColumnIndex=3 BEGIN SET @TelephoneNumber =(SELECT CASE WHEN @ColumnValue IS NOT NULL AND @ColumnValue <> '' THEN @ColumnValue ELSE NULL END) END IF @ColumnIndex=4 BEGIN SET @UserName = (SELECT CASE WHEN @ColumnValue IS NOT NULL AND @ColumnValue <> '' THEN @ColumnValue ELSE NULL END) END IF @ColumnIndex=5 BEGIN SET @GroupName = (SELECT CASE WHEN @ColumnValue IS NOT NULL AND @ColumnValue <> '' THEN @ColumnValue ELSE NULL END) END IF (CHARINDEX(',',@CurrentUserString)=0) AND @ColumnIndex=5 BEGIN SET @UnitName = LTRIM(RTRIM(@CurrentUserString)) END SET @ColumnIndex=@ColumnIndex + 1 END INSERT INTO #BulkUserInfoTable( UserName, FirstName, LastName, Email, TelephoneNumber, GroupName, UnitName ) VALUES ( @UserName, @FirstName, @LastName, @Email, @TelephoneNumber, @GroupName, @UnitName ) END SELECT FirstName, LastName, Email, TelephoneNumber, UserName, GroupName, UnitName FROM #BulkUserInfoTable drop table #BulkUserInfoTable END