Powershell help for creating accounts

Hello Experts,
I need help with the below :

#Requires -Module ActiveDirectory

# Utility functions

function Get-CommandParameter {
    # .SYNOPSIS
    #   Get a set of parameters from a specific parameter set.
    # .DESCRIPTION
    #   Get a set of parameters from a specific parameter set.
    # .INPUTS
    #   System.String
    # .OUTPUTS
    #   System.Management.Automation.ParameterMetadata
    # .EXAMPLE
    #   Get-CommandParameter Set-ADUser -ParameterSetName Identity
    
    [OutputType([System.Management.Automation.ParameterMetadata])]
    param (
        # Retrieve parameters for the specified command.
        [Parameter(Mandatory = $true)]
        [String]$CommandName,

        # Limit results to parameters from the specified parameter set.
        [String]$ParameterSetName = '__AllParameterSets',

        # Return the results as a hashtable, using the parameter name as a key.
        [Switch]$AsHashtable
    )

    try {
        $hashtable = @{}

        $command = Get-Command -Name $CommandName -ErrorAction Stop
        $command.Parameters.Values | 
            Where-Object { $_.ParameterSets.Keys -contains $ParameterSetName } |
            ForEach-Object {
                if ($AsHashtable) {
                    $hashtable.Add($_.Name, $_)
                } else {
                    $_
                }
            }

        if ($AsHashtable) {
            $hashtable
        }
    } catch {
        throw
    }
}

# Generic functions

function Invoke-SqlQuery {
    # .SYNOPSIS
    #   Invoke an SQL query.
    # .DESCRIPTION
    #   Invoke an SQL query against a server described by a connection string.
    # .INPUTS
    #   System.String
    # .OUTPUTS
    #   System.Management.Automation.PSObject
    # .EXAMPLE
    #   Invoke-SqlQuery -Query "SELECT * FROM Table" -ConnectionString "Server=server\instance;Database=db;Trusted_Connection=yes;"

    [OutputType([System.Management.Automation.PSObject])]
    param (
        # An SQL query to execute.
        [Parameter(Mandatory = $true)]
        [String]$Query,

        # The connection string to use. A connection will be created prior to executing the query, then removed afterwards.
        [Parameter(Mandatory = $true)]
        [String]$ConnectionString
    )

    # Execute the SQL query and return an object representing each row.

    try {
        $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
        $SqlConnection.ConnectionString = $ConnectionString
        $SqlConnection.Open()

        $SqlCommand = $SqlConnection.CreateCommand()
        $SqlCommand.CommandText = $Query
    
        $reader = $SqlCommand.ExecuteReader()

        if ($reader.HasRows) {
            while ($reader.Read()) {
                $psObject = New-Object PSObject
                for ($i = 0; $i -lt $reader.FieldCount; $i++) {
                    $name = $reader.GetName($i)

                    if (-not $psObject.PSObject.Properties.Item($name)) {
                        $value = $reader.GetValue($i)
                        
                        if ($value -is [DBNull]) {
                            $value = $null
                        }
                        if ($value -is [String] -and -not [String]::IsNullOrEmpty($value)) {
                            $value = $value.Trim()
                        }

                        $psObject | Add-Member $name $value
                    }
                }
                $psObject
            }
        }

        $reader.Close()
    } catch {
        throw
    } finally {
        if ($SqlConnection.State -eq 'Opened') {
            $SqlConnection.Close()
        }
    }
}

# Process functions

function NewADUser {
    # .SYNOPSIS
    #   Implements the create action.
    # .DESCRIPTION
    #   NewADUser dynamically binds parameters for the New-ADUser command based on a UserInformation object.
    # .INPUTS
    #   System.Management.Automation.PSObject
    #   System.String
    # .OUTPUTS
    #   None

    param (
        [Parameter(ValueFromPipeline = $true)]
        [PSObject]$UserInformation,

        [String]$Path = "OU=TestUser,OU=test,DC=test,DC=com"
    )

    process {
        if ($UserInformation.Action -eq 'create') {
            $params = @{
                Path            = $Path
                AccountPassword = $UserInformation.Password | ConvertTo-SecureString -AsPlainText -Force
            }
            $otherAttributes = @{}

            # Create a list of properties (from UserInformation) which will be written to the new user
            $propertiesToSet = $UserInformation.PSObject.Properties | 
                Where-Object { 
                    $_.Name -notin 'Action', 'Password', 'Status' -and 
                    $null -ne $_.Value -and
                    ($_.Value -isnot [String] -or $_.Value.Trim() -ne '')
                }

            try {
                $validParameters = Get-CommandParameter New-ADUser -AsHashtable

                # Use as many named parameters as possible
                foreach ($property in $propertiesToSet) {
                    if ($validParameters.Contains($property.Name)) {
                        $params.($property.Name) = $property.Value
                    } else {
                        $otherAttributes.Add($property.Name, $property.Value)
                    }
                }
                
                # Load everything else into OtherAttributes
                if ($otherAttributes.Count -gt 0) {
                    $params.OtherAttributes = $otherAttributes
                }
                if (-not $params.Contains('Name') -and $UserInformation.DisplayName) {
                    $params.Name = $UserInformation.DisplayName
                }

                Write-Host "Creating user with the following information"
                Write-Host ($params | ConvertTo-Json -Depth 3)

                New-ADUser @params -ErrorAction Stop
                $UserInformation.Status = 'OK'
            } catch {
                $UserInformation.Status = 'Create failed ({0})' -f $_.Exception.Message

                Write-Error -ErrorRecord $_
            }
        }
    }
}

function SetADUser {
    # .SYNOPSIS
    #   Implements the update action.
    # .DESCRIPTION
    #   SetADUser dynamically binds parameters for the Set-ADUser command based on a UserInformation object.
    # .INPUTS
    #   System.Management.Automation.PSObject
    #   System.String
    # .OUTPUTS
    #   None

    param (
        [Parameter(ValueFromPipeline = $true)]
        [PSObject]$UserInformation
    )

    process {
        $params = @{
            Identity = $_.SamAccountName
        }
        $replace = @{}

        # Create a list of properties (from UserInformation) which will be set on the existing user
        $propertiesToSet = $UserInformation.PSObject.Properties | 
            Where-Object {
                $_.Name -notin 'Action', 'Password', 'Status', 'SamAccountName' -and 
                $null -ne $_.Value -and
                ($_.Value -isnot [String] -or $_.Value.Trim() -ne '')
            }

        if ($UserInformation.Action -eq 'update') {
            $validParameters = Get-CommandParameter Set-ADUser -ParameterSetName Identity -AsHashtable

            # Use as many named parameters as possible
            foreach ($property in $propertiesToSet) {
                if ($validParameters.Contains($property.Name)) {
                    $params.($property.Name) = $property.Value
                } else {
                    $replace.Add($property.Name, $property.Value)
                }
            }
            
            # Load everything else into OtherAttributes
            if ($replace.Count -gt 0) {
                $params.Replace = $replace
            }

            Write-Host "Updating user with the following information"
            Write-Host ($params | ConvertTo-Json -Depth 3)

            try {
                Set-ADUser @params -ErrorAction Stop
                $UserInformation.Status = 'OK'
            } catch {
                $UserInformation.Status = 'Set failed ({0})' -f $_.Exception.Message

                Write-Error -ErrorRecord $_
            }
        }
    }
}

function RemoveAction {
    # .SYNOPSIS
    #   Remove the action flag.
    # .DESCRIPTION
    #   Remove the action flag for every object in UserInformation where status is set to 'OK'.
    # .INPUTS
    #   System.Management.Automation.PSObject
    # .OUTPUTS
    #   None

    param (
        [Parameter(ValueFromPipeline = $true)]
        [PSObject]$UserInformation
    )

    begin {
        $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
        $SqlConnection.ConnectionString = $ConnectionString
        $SqlConnection.Open()
    }

    process {
        if ($UserInformation.Status -eq 'OK') {
            try {
                $SqlCommand = $SqlConnection.CreateCommand()
                $SqlCommand.CommandText = "UPDATE dbo.GetActiveDirectoryUsers SET Action = ' ' WHERE SamAccountName = '{0}';" -f $UserInformation.SamAccountName
                $SqlCommand.ExecuteNonQuery()
            } catch {
                Write-Error -ErrorRecord $_
            }
        }
    }

    end {
        $SqlConnection.Close()
    }
}

function NewReport {
    # .SYNOPSIS
    #   Create a report.
    # .DESCRIPTION
    #   This step creates an HTML report based on the UserInformation collection.
    # .INPUTS
    #   System.Management.Automation.PSObject[]
    # .OUTPUTS
    #   System.String

    param (
        # The UserInformation set which holds the state of each action performed by this script.
        [Parameter(Mandatory = $true)]
        [AllowNull()]
        [PSObject[]]$UserInformation
    )

    # HTML header
    $htmlHead = "<style>
        body {
            font-family: Arial;
        }

        table {
            width: 100%;
            border-collapse: collapse;
            border: 1px solid;      
        }

        th {
            background-color: #87CEFA;
            border: 1px solid;
            padding: 1px;
        }

        td {
            border: 1px solid;
            padding: 1px;
        }

        td.Red {
            color: Red;
        }

        td.Green {
            color: Green;
        }
    </style>"

    if ($null -ne $UserInformation) {
        $UserInformation |
            Select-Object SamAccountName, DisplayName, Action, Status |
            Sort-Object {
                switch -Wildcard ($_.Status) {
                    'Failed*' { 1 }
                    'OK'      { 2 }
                    default   { 2 }
                }
            }, DisplayName |
            ConvertTo-Html -Head $htmlHead |
            ForEach-Object {
                # Colour the status cells in.
                switch -Regex ($_) {
                    '<td>Failed' { $_ -replace '<td>Failed', '<td class="Red">Failed'; break }
                    '<td>OK'     { $_ -replace '<td>OK', '<td class="Green">OK'; break }
                    default      { $_ }
                }
            } | Out-String
    }
}

function SendMailReport {
    param(
        [Parameter(Mandatory = $true)]
        [PSObject[]]$UserInformation,

        [Parameter(Mandatory = $true)]
        [String]$Body,

        [String]$To = 'someone@domain.com',

        [String]$From = 'someone@domain.com',

        [String]$Subject = ('User update: {0}' -f (Get-Date).ToShortDateString()),

        [String]$SmtpServer = 'someserver.domain.com'
    )

    # Export this to add as an attachment.
    $UserInformation | Export-Csv UserInformation.csv -NoTypeInformation

    $params = @{
        To          = $To
        From        = $From
        Subject     = $Subject
        Body        = $Body
        BodyAsHtml  = $true
        SmtpServer  = $SmtpServer
        Attachments = '.\UserInformation.csv'
    }
    Send-MailMessage @params
}

#
# Main
#

# Connection string to SQL Server database
$connectionString = "Server=WIN8\SQLEXPRESS;Database=DBA_Utilities;Trusted_Connection=yes;"

# Query
$sql = "Select FirstName as GivenName,
               LastName as sn,
               DisplayName,
               samAccountName,
               EmailAddress as mail,
               City as l,
               State as st,
               c,
               CountryCode as co,
               Department,
               StreetAddress,
               samAccountName+'@test.com' as userPrincipalName,
               PostalCode,
               Title,
               Office as physicalDeliveryOfficeName,
               OfficePhone as telephoneNumber,
               MobilePhone as mobile,
               Action,            
               AccountisEnabled as Enabled,
               '11Passw0rd' as Password
FROM GetActiveDirectoryUsers where Action = 'update' OR Action = 'create'"

# A status field is added to each entry. This is used to persistently record the result of our commands.
$usersToProcess = Invoke-SqlQuery -Query $sql -ConnectionString $connectionString |
    Select-Object *,
                  @{Name = 'PasswordNeverExpires'; Expression = { $true }},
                  @{Name = 'Status'; Expression = 'NotProcessed'}

# Update any fields which need modification
$usersToProcess | ForEach-Object {
    $_.Enabled = (Get-Variable $_.Enabled).Value
}

# Commit the changes to AD
# NewADUser internally filters usersToProcess on Action = Create
$usersToProcess | NewADUser
# SetADUser internally filters usersToProcess on Action = Update
$usersToProcess | SetADUser

# Update database
# RemoveAction internally filters usersToProcess on Status = OK
$usersToProcess | RemoveAction

# Send job notification
$report = NewReport -UserInformation $usersToProcess
if ($report) {
    SendMailReport -UserInformation $usersToProcess -Body $report
}

Select allOpen in new window

The code mentioned lookup in SQL & creates & updates Accounts in AD, which is in-house.
Based on the same information I want the script to Create, Update & Delete Accounts.
So when in SQL, the value of the field
Action = create, the account has to be created.
Action = update, the account has to be updated.

At the end of the creation there are some tasks to be performed, as O365 doesn't allow you to perform this tasks during the creation.
I have this being performed in another script, which actually is independent of SQL.
I want this all to be run as same script.
Please see below the tasks:

#		$CheckIfUserAccountExists = Get-MsolUser -UserPrincipalName $UserName -ErrorAction SilentlyContinue

		#Check If User Account Exists
		$checkmailbox = get-mailbox -identity $UserName -ErrorAction SilentlyContinue
		
		# Loop till the user account is created
		do
		{
			$checkmailbox = get-mailbox -identity $UserName -ErrorAction SilentlyContinue
			Write-Host "Checking if account has been created yet"
			Sleep 15
		}
		
		# when the user account is created
		While ($checkmailbox -eq $Null)
		
		# Set Language, DateFormat & TimeFormat
		if ($LOCATION -eq "CH") { Set-MailboxRegionalConfiguration -identity $UserName -Language de-CH -TimeZone "W. Europe Standard Time" -DateFormat "dd.MM.yyyy" -timeformat "HH:mm" -LocalizeDefaultFolderName:$true}
		elseif ($LOCATION -eq "DE") { Set-MailboxRegionalConfiguration -identity $UserName -Language de-de -TimeZone "W. Europe Standard Time" -DateFormat "dd.MM.yyyy" -timeformat "HH:mm" -LocalizeDefaultFolderName:$true }
		
		# Set Default as reviewer for calendar of the user & use Calendar as Owner
		foreach ($mbx in Get-Mailbox | Where-Object { $_.WhenCreated –ge ((Get-Date).Adddays(-1)) } | Get-Mailbox)
		{
			$mbox = $mbx.alias + ":\calendar"
			$test = Get-MailboxFolderPermission -Identity $mbox -erroraction silentlycontinue
			if ($test -ne $null)
			{
				Set-MailboxFolderPermission -Identity $mbox -User Default -AccessRights Reviewer | out-null
				Add-MailboxFolderPermission -Identity $mbox -User Calendar -AccessRights Owner | out-null
				
			}
			else
			{
				$mbox = $mbx.alias + ":\kalender"
				Set-MailboxFolderPermission -Identity $mbox -User Default -AccessRights Reviewer | out-null
				Add-MailboxFolderPermission -Identity $mbox -User Calendar -AccessRights Owner | out-null
				
			}
		}
		
		# Enable Services for User
		if ($license -eq "reseller-account:ENTERPRISEPACK") { Set-MsolUserLicense -UserprincipalName $UserName -LicenseOptions $Services}
		
		# Show all the configured parameters in Display
		Get-MsolUser -UserPrincipalName $UserName | fl DisplayName, UserprincipalName, StreetAddress, City, State, Country, MobilePhone, PhoneNumber, Office, Title, Department, Office, UsageLocation
		# Display Calendar permissions
		Write-Host "Following are the permissions for Calendar"
		Get-MailboxFolderPermission -Identity $mbox
		
	}

Select allOpen in new window

However, in case of Action = update, when the field AccountIsEnabled = False, the account has to be deleted in O365, but the impact on AD remains as in the script currently.

I want to figure out how to initiate the connection to O365 in the same script, & run the jobs?

Thanks in advance for your help
Regards,
A