Active Directory Import Procedures

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