SpeakeasyPSModule.psm1

#Region './Private/Format-BooleanValue.ps1' 0
function Format-BooleanValue([string]$ValueToFormat)
{
  if($ValueToFormat -eq "Yes") {return 1} else { return 0}
}

#EndRegion './Private/Format-BooleanValue.ps1' 6
#Region './Private/Format-DateValue.ps1' 0
function Format-DateValue([string]$ValueToFormat)
{
  [datetime] $dt = $ValueToFormat
  return "'" + $dt.ToString("yyyyMMdd HH:MM:ss") + "'"
}
#EndRegion './Private/Format-DateValue.ps1' 6
#Region './Private/Format-LookupValue.ps1' 0
function Format-LookupValue($ValueToFormat)
{
     $LookupValue = [string]::join("; ",( $ValueToFormat | Select-Object -expandproperty LookupValue))
     $LookupValue = $LookupValue -replace "'", "''"
     return "'" + $LookupValue + "'"
}
#EndRegion './Private/Format-LookupValue.ps1' 7
#Region './Private/Format-MMSValue.ps1' 0
function Format-MMSValue([Object]$ValueToFormat)
{
  return "'" + $ValueToFormat.Label + "'"
}
#EndRegion './Private/Format-MMSValue.ps1' 5
#Region './Private/Format-StringValue.ps1' 0
function Format-StringValue([object]$ValueToFormat)
{
  [string]$result = $ValueToFormat -replace "'", "''"
  return "'" + $result + "'"
}
#EndRegion './Private/Format-StringValue.ps1' 6
#Region './Private/Format-UserValue.ps1' 0
function Format-UserValue([object] $ValueToFormat)
{
     $Users = [string]::join("; ",( $ValueToFormat | Select -expandproperty LookupValue))
     $Users = $Users -replace "'", "''"
     return "'" + $Users + "'"
}
#EndRegion './Private/Format-UserValue.ps1' 7
#Region './Private/Get-AzureCertificateProperties.ps1' 0
function Get-AzureCertificateProperties
{
    if ($null -eq $Script:azureCertificateProperties)
    {
        throw "Azure properties are null - Set-AzureCertificateProperties must be invoked to set these"
    }
    return $Script:azureCertificateProperties;
}
#EndRegion './Private/Get-AzureCertificateProperties.ps1' 9
#Region './Private/Get-AzureProperties.ps1' 0
function Get-AzureProperties
{
    if ($null -eq $Script:azureProperties)
    {
        throw "Azure properties are null - Set-AzureProperties must be invoked to set these"
    }
    return $Script:azureProperties;
}
#EndRegion './Private/Get-AzureProperties.ps1' 9
#Region './Private/Get-ColumnDefinition.ps1' 0
#Get SQL column Definition for SharePoint List Field
function Get-ColumnDefinition($field)
{
    $ColumnDefinition=[string]::Empty
    $fieldTitle = $Field.Title.replace(' ','') # remove whitespace so that column names have no spaces
    # use field title instead of internal name as the internal name may be somehting like "field_1" where title would be "surname"
    Switch($Field.TypeAsString)
    {
      "Boolean" { $ColumnDefinition = '['+ $fieldTitle +'] [bit] NULL '}
      "Choice" { $ColumnDefinition = '['+ $fieldTitle +'] [nvarchar](MAX) NULL '}
      "Currency" { $ColumnDefinition = '['+ $fieldTitle +'] [decimal](18, 2) NULL '}
      "DateTime" { $ColumnDefinition = '['+ $fieldTitle +'] [datetime] NULL '}
      "Guid" { $ColumnDefinition = '['+ $fieldTitle +'] [uniqueidentifier] NULL '}
      "Integer" { $ColumnDefinition = '['+ $fieldTitle +'] [int] NULL '}
      "Lookup" { if ($fieldTitle.ToUpper().EndsWith("ID")) {$ColumnDefinition = '['+ $fieldTitle +'] [int] NULL '} else {$ColumnDefinition = '['+ $fieldTitle +'] [nvarchar] (500) NULL '}}
      "MultiChoice" { $ColumnDefinition = '['+ $fieldTitle +'] [nText] NULL '}
      #"MultiChoice" { $ColumnDefinition = '['+ $fieldTitle +'] [nText] (MAX) NULL '}
      "Note" { $ColumnDefinition = '['+ $fieldTitle +'] [nText] NULL '}
      "Number" { $ColumnDefinition = '['+ $fieldTitle +'] [decimal](18, 2) NULL '}
      "Text" { $ColumnDefinition = '['+ $fieldTitle +'] [nVarchar] (MAX) NULL '}
      "URL" { $ColumnDefinition = '['+ $fieldTitle +'] [nvarchar] (500) NULL '}
      "User" { $ColumnDefinition = '['+ $fieldTitle +'] [nvarchar] (255) NULL '}     
      default { $ColumnDefinition = '['+ $fieldTitle +'] [nvarchar] (MAX) NULL '}
  }
  return $ColumnDefinition
}
#EndRegion './Private/Get-ColumnDefinition.ps1' 27
#Region './Private/Get-ColumnValue.ps1' 0
#function to get the value of a given field based on its data type
function Get-ColumnValue($field, $fieldValue)
{
    #Check for NULL
    if([string]::IsNullOrEmpty($fieldValue)) { return 'NULL'}
     
    $formattedValue = [string]::Empty
     
    Switch($field.TypeAsString)
    {
    "Boolean"  {$formattedValue =  Format-BooleanValue($fieldValue)}
    "Choice"  {$formattedValue = Format-StringValue($fieldValue)}
    "Currency"  {$formattedValue = $fieldValue}
    "DateTime"  {$formattedValue = Format-DateValue($fieldValue)}
    "Guid" { $formattedValue = Format-StringValue($fieldValue)}
    "Integer"  {$formattedValue = $fieldValue}
    "Lookup"  {$formattedValue = Format-LookupValue($fieldValue) }
    "MultiChoice" {$formattedValue = Format-StringValue($fieldValue)}
    "Note"  {$formattedValue = Format-StringValue($fieldValue)}
    "Number"    {$formattedValue = $fieldValue}
    "Text"  {$formattedValue = Format-StringValue($fieldValue)}
    "URL"  {$formattedValue =  Format-StringValue($fieldValue)}
    "User"  {$formattedValue = Format-UserValue($fieldValue) }
     #Check MMS Field
     "Invalid" { if($field.TypeDisplayName -eq "Managed Metadata") { $formattedValue = Format-MMSValue($fieldValue) } else { $formattedValue =Format-StringValue($fieldValue)}  }
    default  {$formattedValue = Format-StringValue($fieldValue)}
  }
  Return $formattedValue
}
#EndRegion './Private/Get-ColumnValue.ps1' 30
#Region './Private/Get-ConnectedSharepointSiteProperties.ps1' 0

# retrieve the name of the last connected SharePoint site
function Get-ConnectedSharePointSiteProperties
{
    return $Script:connectedSharePointSiteProperties
}
#EndRegion './Private/Get-ConnectedSharepointSiteProperties.ps1' 7
#Region './Private/Save-ConnectedSharepointSiteProperties.ps1' 0
$Script:connectedSharePointSite = @{
    SharePointSiteName = $null
    SharePointOrgRootUrl = $null
}

function Save-ConnectedSharePointSiteProperties
{
    param(
        [Parameter(Mandatory)] [string]$SharePointSiteName,
        [Parameter(Mandatory)] [string]$SharePointOrgRootUrl
        )
    
    $Script:connectedSharePointSiteProperties = @{
        SharePointSiteName = $SharePointSiteName
        SharePointOrgRootUrl = $SharePointOrgRootUrl
    }
}
#EndRegion './Private/Save-ConnectedSharepointSiteProperties.ps1' 18
#Region './Public/Add-DataFromSharepointToSQL.ps1' 0
#Insert Data from SharePoint List to SQL Table
function Add-DataFromSharePointToSQL($logFile,$dbConnection,$fields,$listItems)
{
    #Progress bar counter
    $counter=0
    $listItemCount=$listItems.Count

    Write-Output "Total SharePoint List Items to Copy: $($listItemCount)"
    foreach ($item in $listItems)
    {
 
        Write-Progress -Activity "Copying SharePoint list items. Please wait...`n`n" -status "Processing list item: $($item['ID'])" -percentComplete ($counter/$ListItemCount*100)
 
        $sql = new-object System.Text.StringBuilder
        [void]$sql.Append("INSERT INTO [dbo].[$($tableName)] ( [ID] ")
        $vals = new-object System.Text.StringBuilder
        [void]$vals.Append("VALUES ("+ $item["ID"])
         
        foreach ($field in $fields)
        {
            $fieldInternalName = $field.InternalName
            $fieldTitle = $Field.Title.replace(' ','') # remove whitespace so that column names have no spaces
            [void]$sql.Append(",[$($fieldTitle)]")
            $fieldValue = $item[$fieldInternalName]
            $columnValue =  Get-ColumnValue $field $fieldValue
            [void]$vals.Append( ","+ $columnValue)
        }

        
        [void]$sql.Append(") ")
        [void]$vals.Append(") ")
     
        #Combine Field and Values
        $SQLStatement = $sql.ToString() + $vals.ToString()
     
        #Run the Query
        Invoke-SQLQuery -logFile $logFile -dbConnection $dbConnection -Query $SQLStatement | Out-Null
     
        $Counter=$Counter+1; 
    }
    "Total SharePoint List Items Copied: $($listItemCount)" >> $logFile
}
#EndRegion './Public/Add-DataFromSharepointToSQL.ps1' 43
#Region './Public/Add-ForeignKeyConstraints.ps1' 0
#function to create all foreign key constraints from the database
function Add-ForeignKeyConstraints($logFile,$dbConnection)
{
  $Query = "exec dbo.AddAllForeignKeyConstraints"
    
  #Run the Query
  # Write-Output $Query
  Invoke-SQLQuery -logFile $logFile -dbConnection $dbConnection -Query $Query | Out-Null
}
#EndRegion './Public/Add-ForeignKeyConstraints.ps1' 10
#Region './Public/Add-SQLTable.ps1' 0
#Create SQL Server table for SharePoint List
function Add-SQLTable($logFile,$dbConnection,$listFields)
{
    #Check if the table exists already
    $Query="IF (OBJECT_ID('[dbo].[$($TableName)]','U') IS NULL) CREATE TABLE [dbo].[$($TableName)]([ID] [int] NOT NULL PRIMARY KEY, "
    foreach ($field in $listFields)
    {
        $Query += Get-ColumnDefinition -Field $field
        $Query += ","
    }
    $Query += ")"
 
    #Run the Query
    Invoke-SQLQuery -logFile $logFile -dbConnection $dbConnection -Query $Query | Out-Null
}
#EndRegion './Public/Add-SQLTable.ps1' 16
#Region './Public/Confirm-ElapsedTimeHasExpired.ps1' 0
# retrieves a time value stored in an Azure Automation Value and returns true if that value is
# older than the days/hours/minutes values provided
# e.g. Confirm-ElapsedTimeHasExpired -varName $varName -days 0 -hours 0 -minutes 1 will return true
# if the value stored in $varName is older than 1 minute.
# If the time has elapsed, the current date is stored in the Azure Automation Value and the function
# will return false until another 1 minute has elapsed. This function is typically used to limit how
# often error exception emails are sent so that we don't exceed our limit in Twilio. If we have a
# problem with a runbook and that runbook executes every 30 minutes we can easily end up with 48 emails
# (or more over a weekend!) before the problem is resolved.
function Confirm-ElapsedTimeHasExpired([string]$varName,[int]$days,[int]$hours,[int]$minutes)
{
    $timeHasExpired = $false

    [dateTime]$dateNow = Get-Date
    $dateNow = $dateNow.ToUniversalTime()
    $savedVar = Get-AutomationVariableValue $varName
    if ($null -ne $savedVar)
    {
        [dateTime]$savedDate = $savedVar
        $savedDate = $savedDate.ToUniversalTime()
    }

    if ($null -eq $savedDate)
    {
        $ignore = newAutomationVariable -Name $varName -Value $utcValue
    }
    else
    {
        $expiryDate = $savedDate
        $expiryDate = $expiryDate.AddDays($days)
        $expiryDate = $expiryDate.AddHours($hours) 
        $expiryDate = $expiryDate.AddMinutes($minutes) 
        #$timeBeforeNextExpiry = New-TimeSpan -Start $dateNow -End $expiryDate
    }

    #Write-Output "savedDate = $($savedDate) (UTC)"
    #Write-Output "expiryDate = $($expiryDate) (UTC)"
    #Write-Output "dateNow = $($dateNow) (UTC)"
    #Write-Output "timeBeforeNextExpiry = $($timeBeforeNextExpiry.Hours):$($timeBeforeNextExpiry.Minutes):$($timeBeforeNextExpiry.Seconds)"

    if (($null-eq $savedDate) -or ($dateNow -ge $expiryDate))
    {
        $timeHasExpired = $true
        Set-AutomationVariableValue -Name $varName -Value $dateNow
    }
    return $timeHasExpired
}
#EndRegion './Public/Confirm-ElapsedTimeHasExpired.ps1' 48
#Region './Public/Confirm-ScriptIsRunningInAzure.ps1' 0
# identifies whether the script is running on a user#s desktop computer on in Azure
function Confirm-ScriptIsRunningInAzure
{
    $runningInAzure = $false
    if ($env:AZUREPS_HOST_ENVIRONMENT -or $PSPrivateMetadata.JobId) 
    {
        # Not local
        $runningInAzure = $true
    }
    return $runningInAzure
}
#EndRegion './Public/Confirm-ScriptIsRunningInAzure.ps1' 12
#Region './Public/Connect-Azure.ps1' 0
# connect to Azure if not already connected (or if connected user is not in the required domain
function Connect-Azure([Parameter(Mandatory)] [string]$requiredUserDomain)  
{  
    if (Confirm-ScriptIsRunningInAzure -eq $true)
    {
        # no need to do this if running as a runbook in Azure
    }
    else
    {
        $context = Get-AzContext  
  
        if (!$context -or !($context.Account.Id -like "*$($requiredUserDomain)"))   
        {  
            $context = Connect-AzAccount 
            if ($null -eq $context)
            {
                throw "Failed to establish an Azure connection"
            }
            else
            {
                $context = Get-AzContext  
                if (!($context.Account.Id -like "*$($requiredUserDomain)"))
                {
                    throw "Connected user must be in the $($requiredUserDomain) domain"
                }
                else
                {
                    Write-Output "Azure connection successfully established"  
                }
            }
        }   
        else   
        {  
            Write-Output "Azure connection already established"  
        }  
    }
} 
#EndRegion './Public/Connect-Azure.ps1' 38
#Region './Public/Connect-Exchange.ps1' 0
# establishes a connection to Exchange 365
function Connect-Exchange
{
    $azureProperties = Get-AzureProperties
    $azureCertificateProperties = Get-AzureCertificateProperties
    Connect-ExchangeOnline `
        -CertificateThumbPrint $azureCertificateProperties.thumbPrint `
        -AppID $azureCertificateProperties.appId `
        -Organization $azureProperties.organisationDomain


    Write-Output "Connection to Exchange was successful"
}
#EndRegion './Public/Connect-Exchange.ps1' 14
#Region './Public/Connect-SharepointSite.ps1' 0
# establishes a connection to a particular SharePoint site. The site will correspond to one of the teams
# configured in Microsoft Teams
function Connect-SharePointSite
{
    param(
        [Parameter(Mandatory)] [string]$SharePointOrgRootUrl,
        [Parameter(Mandatory)] [string]$SharePointSiteName
    )

    $azureProperties = Get-AzureProperties
    $azureCertificateProperties = Get-AzureCertificateProperties

    $SharePointSite = "$($SharePointOrgRootUrl)/$($SharePointSiteName)/"
    Connect-PnpOnline `
                    -Url $SharePointSite `
                    -Tenant $azureProperties.organisationDomain `
                    -ClientId $azureCertificateProperties.appId `
                    -ThumbPrint $azureCertificateProperties.thumbPrint

    # save the site name so we can reconnect if we lose connection context, e.g. whilst retrieving items from a SharePoint list
    Save-ConnectedSharePointSiteProperties -SharePointSiteName $SharePointSiteName -SharePointOrgRootUrl $SharePointOrgRootUrl

    # TODO: move Output line to calling script
    # Write-Output "Connection to SharePoint site $($SharePointSiteName) was successful"
}
#EndRegion './Public/Connect-SharepointSite.ps1' 26
#Region './Public/Convert-HashMapToStringValuePairs.ps1' 0
# converts a map into a string of key value pairs - which makes it easier to save to an Azure automation variable
function Convert-HashMapToStringValuePairs
{
    param($hashMap)
    $hashMapStringData = [string]($hashMap.GetEnumerator()|%{"$($_.Key)=$($_.Value);"})

    return $hashMapStringData
}
#EndRegion './Public/Convert-HashMapToStringValuePairs.ps1' 9
#Region './Public/Disconnect-Exchange.ps1' 0
# disconnects any existing connection to Exchange 365
function Disconnect-Exchange
{
    Disconnect-ExchangeOnline -Confirm:$false
}
#EndRegion './Public/Disconnect-Exchange.ps1' 6
#Region './Public/Disconnect-SharepointSite.ps1' 0
# disconnects any existing connection to SharePoint 365
function Disconnect-SharePointSite
{
    Disconnect-PnpOnline
}
#EndRegion './Public/Disconnect-SharepointSite.ps1' 6
#Region './Public/Get-AutomationVariableValue.ps1' 0
# retrieve the value of an Azure automation variable - the function encapsulates the different mechanisms based upon whether the script
# is running in Azure or on a user's desktop
function Get-AutomationVariableValue
{
    param($Name)

    $variableValue = $null

    if (Confirm-ScriptIsRunningInAzure -eq $true)
    {
        $variableValue = Get-AutomationVariable  -Name $Name
    }
    else
    {
        try
        {
            $azureProperties = Get-AzureProperties
            $variableObj = Get-AzAutomationVariable -Name $Name -ResourceGroupName $azureProperties.resourceGroupName -AutomationAccountName $azureProperties.automationAccountName
            if ($null -ne $variableObj)
            {
                $variableValue = $variableObj.Value
            }
        }
        catch
        {
            # ignore
        }
    }
    return $variableValue
}
#EndRegion './Public/Get-AutomationVariableValue.ps1' 31
#Region './Public/Get-DbConnection.ps1' 0
# function to encapsulate connecting to a server instance or an Azure instance of a database
function Get-DbConnection
{
    param ($azureSqlServer,$applicationDatabase)

    $numTries = 2
    $retryCount = 0
    do
    {
        if ($azureSqlServer -like "*.database.windows.net") # i.e. the server is an azure SQL database
        {
            if (Confirm-ScriptIsRunningInAzure -eq $true)
            {
                # this uses the example from: https://jaliyaudagedara.blogspot.com/2022/01/azure-automation-runbook-execute-sql.html

                # Getting AccessToken for System assigned Managed Identity
                $Resource = "https://database.windows.net/"
                $QueryParameter = "?resource=$Resource"
                $Url = $env:IDENTITY_ENDPOINT + $QueryParameter
                $Headers = New-Object "System.Collections.Generic.Dictionary[[String],[String]]" 
                $Headers.Add("X-IDENTITY-HEADER", $env:IDENTITY_HEADER) 
                $Headers.Add("Metadata", "True") 
                $Content =[System.Text.Encoding]::Default.GetString((Invoke-WebRequest `
                    -UseBasicParsing `
                    -Uri $Url `
                    -Method 'GET' `
                    -Headers $Headers).RawContentStream.ToArray()) | ConvertFrom-Json 
                $AccessToken = $Content.access_token 
        
                # PowerShell/ADO.NET Connected Architecture
                $SqlConnection = New-Object System.Data.SqlClient.SQLConnection  
                $SqlConnection.ConnectionString = "Server=$azureSqlServer;Initial Catalog=$applicationDatabase;Connect Timeout=30" 
                $SqlConnection.AccessToken = $AccessToken 
            }
            else
            {
                $accessToken = (Get-AzAccessToken -ResourceUrl https://database.windows.net).Token

                $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
                $SqlConnection.ConnectionString = "Server=$azureSqlServer;Initial Catalog=$applicationDatabase;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30"
                $SqlConnection.AccessToken = $accessToken
            }
        }
        else
        {
            if (Confirm-ScriptIsRunningInAzure -eq $true)
            {
                throw "can't connect to local db $($azureSqlServer) from Azure RunBook"
                # there is no point in retrying to open a database in this case
                $retryCount = $numTries
            }
            else
            {
                $SqlConnection = New-Object System.Data.SqlClient.SqlConnection("Server=$azureSqlServer;Initial Catalog=$applicationDatabase;Integrated Security=True;")
            }
        }
        try
        {
            $SqlConnection.Open()
            $retryCount = $numTries
        }
        catch
        {
            $retryCount++
            if ($retryCount -lt $numTries)
            {
                Start-Sleep -Seconds 10
            }
            else
            {
                throw $_
            }        
        }
    } while ($retryCount -lt $numTries)

    return $SqlConnection
}
#EndRegion './Public/Get-DbConnection.ps1' 78
#Region './Public/Get-HashMapFromStringValuePairs.ps1' 0
# converts a string of key value pairs back to a hash map
function Get-HashMapFromStringValuePairs
{
    param($stringValuePairs)

    $hashMap = @{}
    $listOfStringValuePairs = $stringValuePairs.Split(";")
    foreach($stringValuePair in $listOfStringValuePairs)
    {
        if ($null -ne $stringValuePair)
        {
            $values = $stringValuePair.Split("=")
            if ($null -ne $values)
            {
                $keyName = $values[0]
                $value = $values[1]
                if ($null -ne $value)
                {
                    $hashMap[$keyName.Trim()] = $value.Trim()
                }
            }
        }
    }
    return $hashMap
}
#EndRegion './Public/Get-HashMapFromStringValuePairs.ps1' 26
#Region './Public/Get-TwilioApiKey.ps1' 0
# get the API key for Twilio SendGrid to be able to send emails
function Get-TwilioApiKey
{
    if ($null -eq $Script:twilioSendGridApiKey)
    {
        throw "Twilio api key is null - Set-TwilioApiKey must be invoked to set this"
    }

     return $Script:twilioSendGridApiKey
}
#EndRegion './Public/Get-TwilioApiKey.ps1' 11
#Region './Public/Get-XeroTenantId.ps1' 0
# obtains the tenantId from Xero - this needs to be passed to Xero with the access token when making API requests
function Get-XeroTenantId
{
    param($tenantName,$accessToken)

    $headers = @{
        "Authorization"="Bearer $($accessToken)"
    }
    $tenantId = $null

    $connectionsUrl = "https://api.xero.com/connections"
    # Note: this would need to change if there were more than one tenant associated with the account
    $response = Invoke-RestMethod -Method GET -Uri $connectionsUrl -Headers $headers
    if ($response.tenantName -eq $tenantName)
    {
        $tenantName = $response.tenantName
        $tenantId = $response.tenantId
    }
    else
    {
        Write-Output "Error - can't find Xero connection called $($tenantName)"
    }

    return $tenantId
}
#EndRegion './Public/Get-XeroTenantId.ps1' 26
#Region './Public/Invoke-ExportSharepointListToSQLTable.ps1' 0
function Invoke-ExportSharePointListToSQLTable($logFile,$dbConnection,$listName)
{
    $tableName = $listName.replace(' ','')
    $allFields = Get-PnpField -List $ListName

    #include Created and Modified fields
    #$fields = $allFields | Where { ($_.Hidden -ne $true ) -and ($_.ReadOnlyField -ne $true ) -and ($_.InternalName -ne "Attachments") -and ($_.InternalName -ne "ContentType") }
    $fields = $allFields | Where-Object { ($_.Hidden -ne $true ) -and (($_.ReadOnlyField -ne $true) -or (($_.ReadOnlyField -eq $true)-and (($_.Title -eq "Created") -or ($_.Title -eq "Modified"))) ) -and ($_.InternalName -ne "Attachments") -and ($_.InternalName -ne "ContentType") }


    $fieldNameList = New-Object System.Collections.ArrayList
    foreach ($field in $fields)
    {
        $fieldNameList.Add($field.InternalName) | Out-Null
    }

    $listItems = Read-SharePointListItems -SharePointListName $listName -fieldsToReturn $fieldNameList

    if ($null -ne $listItems)
    {
        #Call functions to export-import SharePoint list to SQL table

        try
        {
            Write-Output "Beginning export of $($listName) SharePoint data to SQL table $($tableName)..."
            Remove-SQLTable $logFile $dbConnection $tableName
            Add-SQLTable -logFile $logFile -dbConnection $dbConnection -listFields $fields
            Add-DataFromSharePointToSQL -logFile $logFile -dbConnection $dbConnection -listFields $fields -listItems $listItems
            Write-Output "Export of $($listName) SharePoint data to SQL table $($tableName) is complete"
        }
        catch  
        { 
            Write-Output "Exception during SQL operation: $($_.Exception.Message)" >> $logFile 
            Write-Output "Exception during SQL operation: $($_.Exception.Message)"
        }
    }
    else
    {
        Write-Output "Ignoring export of $($listName) as there are no items in the list"
    }
}
#EndRegion './Public/Invoke-ExportSharepointListToSQLTable.ps1' 42
#Region './Public/Invoke-ReportExceptionViaEmail.ps1' 0
# and use some accessor functions instead - and check that accessors
# have been populated at start of every runbook script
function Invoke-ReportExceptionViaEmail
{
    param($fromAddress,$toAddress,$scriptName,$exceptionDetails,$fileName,$attachment,$attachmentType)

    # Retrieve the Xero client id from an Azure automation variable
    $twilioSendGridApiKey = Get-TwilioApiKey

    $varName = "TimeOfLastErrorEmail_$($scriptName)"
    $hasExpired = Confirm-ElapsedTimeHasExpired -varName $varName -days 1
    if ($hasExpired -eq $false)
    {
        Write-Output "The time since the last error email has not expired - forced into test mode so email is NOT sent, to limit the load on Twilio"
        $testMode = $true
    }
    elseif (Confirm-ScriptIsRunningInAzure -eq $true)
    {
        # script is running in Azure
        $TestMode = $false
    }
    else
    {
        $TestMode = $true
    }

    $Parameters = @{
            FromAddress = $fromAddress
            ToAddress   = $toAddress
            Subject     = "Azure Runbook script exception: $scriptName"
            Body        = "An error was found with the Azure Runbook ""$($scriptName)"". Error details are given below:<BR>$($exceptionDetails)<BR>$($exceptionDetails.ScriptStackTrace)"
            APIKey       = $twilioSendGridApiKey
            FileName    = $fileName
            FileNameWithFilePath = $attachment
            AttachmentType = $attachmentType
            TestMode = $TestMode
    }

    Invoke-SendGridMailWithAttachment @Parameters
}
#EndRegion './Public/Invoke-ReportExceptionViaEmail.ps1' 41
#Region './Public/Invoke-SendGridMailWithAttachment.ps1' 0
# TODO: make sure this is consistent with all references in all current scripts - there may be small differences
# use Twilio SendGrid to send an email with an optional file attachment
function Invoke-SendGridMailWithAttachment 
{
    param (
        [cmdletbinding()]
        [parameter()]
        [string]$ToAddress,
        [parameter()]
        [string]$FromAddress,
        [parameter()]
        [string]$Subject,
        [parameter()]
        [string]$Body,
        [parameter()]
        [string]$APIKey,
        [parameter()]
        [string]$FileName,
        [parameter()]
        [string]$FileNameWithFilePath,
        [parameter()]
        [string]$AttachmentType,
        [parameter()]
        [bool]$TestMode
    )

    if ($testMode -eq $true)
    {
        Write-Output "Simulating the sending of an email (testMode is true: the actual email will NOT be sent)"
    }
    else
    {
        Write-Output "Sending email..."
    }
    Write-Output "From: $($FromAddress)"
    Write-Output "To: $($ToAddress)"
    Write-Output "Subject: $($Subject)"
    Write-Output "Body: $($Body)"
    if (($FileNameWithFilePath -ne $null) -and ($FileNameWithFilePath.Length -gt 0))
    {
        Write-Output "Attachment: $FileName"
        if ($testMode -eq $true)
        {
            Write-Output "Last 10 lines of attachment:"
            Get-Content $FileNameWithFilePath -Tail 10
        }
    }
    else
    {
        Write-Output "Attachment: None"
    }
    
    if ($testMode -eq $true)
    {
        return
    }

    #Convert File to Base64
    #Note: the attachment here is UTF format, so we need to take a different approach to reading the file
    # than that for the executive report generator script
    if ($FileNameWithFilePath.Length -gt 0)
    {
            $FileContent = get-content -Path $FileNameWithFilePath -Encoding Byte
            $EncodedFile = [System.Convert]::ToBase64String($FileContent)
<#
        This used to be the technique used - since Sept 2033, the above technique works when running
        in or out of Azure
        if (Confirm-ScriptIsRunningInAzure -eq $true)
        {
            $FileContent = get-content $FileNameWithFilePath -Raw
            $ConvertToBytes = [System.Text.Encoding]::Unicode.GetBytes($FileContent)
            $EncodedFile = [System.Convert]::ToBase64String($ConvertToBytes)
        }
        else
        {
            $FileContent = get-content -Path $FileNameWithFilePath -Encoding Byte
            $EncodedFile = [System.Convert]::ToBase64String($FileContent)
        }
#>

    }

    # this can be used to test if we can write the file back to its original format
    #$bytes = [Convert]::FromBase64String($EncodedFile)
    #[system.io.file]::WriteAllBytes('test.pdf',$bytes)
    
    # Body with attachment for SendGrid
    if ($FileNameWithFilePath.Length -gt 0)
    {
        $SendGridBody = @{
            "personalizations" = @(
                @{
                    "to"= @(
                                @{
                                    "email" = $ToAddress
                                }
                     )
                    "subject" = $Subject
                }
            )
            "content"= @(
                    @{
                        "type" = "text/html"
                        "value" = $Body
                    }
            )
            "from"  = @{
                        "email" = $FromAddress
                        }

            "attachments" = @(
                                @{
                                    "content"=$EncodedFile
                                    "filename"=$FileName
                                    "type"= $AttachmentType
                                    "disposition"="attachment"
                                    }
            )
        }
    }
    else
    {
        $SendGridBody = @{
            "personalizations" = @(
                @{
                    "to"= @(
                                @{
                                    "email" = $ToAddress
                                }
                     )
                    "subject" = $Subject
                }
            )
            "content"= @(
                    @{
                        "type" = "text/html"
                        "value" = $Body
                    }
            )
            "from"  = @{
                        "email" = $FromAddress
                        }

        }
    }

    $BodyJson = $SendGridBody | ConvertTo-Json -Depth 4
    #Header for SendGrid API
    $Header = @{
        "authorization" = "Bearer $APIKey"
    }
    #Send the email through SendGrid API
    $Parameters = @{
        Method      = "POST"
        Uri         = "https://api.sendgrid.com/v3/mail/send"
        Headers     = $Header
        ContentType = "application/json"
        Body        = $BodyJson
    }
    Invoke-RestMethod @Parameters
    Write-Output "Done sending email"
}
#EndRegion './Public/Invoke-SendGridMailWithAttachment.ps1' 162
#Region './Public/Invoke-SQLProcedure.ps1' 0
#function to Execute a SQL Stored procedure
function Invoke-SQLProcedure($logFile,$dbConnection,[string]$Query)
{
    #Write-Output "Executing SQL: ", $Query
    $Query >> $logFile
    $cmd = new-object System.Data.SqlClient.SqlCommand ($Query, $dbCnnection)
    $adapter = New-Object System.Data.sqlclient.sqlDataAdapter $cmd
    $dataset = New-Object System.Data.DataSet
    $adapter.Fill($dataSet) | Out-Null

    return $dataset
}
#EndRegion './Public/Invoke-SQLProcedure.ps1' 13
#Region './Public/Invoke-SQLQuery.ps1' 0
#function to Execute given SQL Query
function Invoke-SQLQuery($logFile,$dbConnection,[string]$Query)
{
    #Write-Output "Executing SQL: ", $Query
    $Query >> $logFile
    $cmd = new-object System.Data.SqlClient.SqlCommand ($Query, $dbConnection)
    $ReturnValue = $cmd.ExecuteNonQuery()

    return $ReturnValue
}

#EndRegion './Public/Invoke-SQLQuery.ps1' 12
#Region './Public/Invoke-StripCRLF.ps1' 0
# strip any carriage return and line feed characters from a string
function Invoke-StripCRLF
{
    param($stringToStrip)

    if ($null -ne $stringToStrip)
    {
        $stringToStrip = $stringToStrip.Replace("`r","")
        $stringToStrip = $stringToStrip.Replace("`n","")
    }

    return $stringToStrip
}
#EndRegion './Public/Invoke-StripCRLF.ps1' 14
#Region './Public/New-AutomationVariable.ps1' 0
# creates a new Azure automation variable - the function encapsulates the different mechanisms based upon whether the script
# is running in Azure or on a user's desktop
function New-AutomationVariable
{
    param($Name,$Value)

    if (Confirm-ScriptIsRunningInAzure -eq $true)
    {
        # can't create in Azure automation
        throw "Automation variable $($Name) does not exist - it needs to be created by first running this runbook script on a desktop computer"
    }
    else
    {
        $azureProperties = Get-AzureProperties
        $variableValue = New-AzAutomationVariable -encrypted $false -Name $Name -Value $Value -ResourceGroupName $azureProperties.resourceGroupName -AutomationAccountName $azureProperties.automationAccountName
    }
    return $variableValue
}
#EndRegion './Public/New-AutomationVariable.ps1' 19
#Region './Public/Read-SharepointListItems.ps1' 0
# TODO: make sure that all references to this use same code - there may be multiple variants in scripts
function Read-SharePointListItems
{
    param(
        [Parameter(Mandatory)][string]$SharePointListName,
        [Parameter(Mandatory)][object]$fieldsToReturn,
        [object]$queryDateFrom = $null,
        [object]$queryDateTo = $null,
        [string]$queryTimeColumnName = $null,
        [bool]$includeTimeValue = $true
    )

    if ($includeTimeValue -eq $true)
    {
        $includeTimeValueStr = "TRUE"
    }
    else
    {
        $includeTimeValueStr = "FALSE"
    }

    $viewFields = "";
    foreach ($fieldName in $fieldsToReturn)
    {
        $viewFields += "<FieldRef Name='$($fieldName)'/>"
    }

    if ($null -eq $queryTimeColumnName -or $queryTimeColumnName.Length -eq 0)
    {
        $queryTimeColumnName = "Modified"
    }

    if ($null -eq $queryDateFrom)
    {
        $queryDateFromString = "1970-01-01:00:00:00Z"
    }
    else
    {
        $queryDateFromString = $queryDateFrom.GetDateTimeFormats("s")
    }

    if ($null -eq $queryDateTo)
    {
        $queryDateToString = (Get-Date).GetDateTimeFormats("s")
    }
    else
    {
        $queryDateToString = $queryDateTo.GetDateTimeFormats("s")
    }

    $dateFilter = " `
          <Where> `
            <And> `
              <Geq> `
                <FieldRef Name='$($queryTimeColumnName)' /> `
                  <Value IncludeTimeValue='$($includeTimeValueStr)' Type='DateTime'>$queryDateFromString</Value> `
              </Geq> `
              <Leq> `
                <FieldRef Name='$($queryTimeColumnName)' /> `
                <Value IncludeTimeValue='$($includeTimeValueStr)' Type='DateTime'>$queryDateToString</Value> `
              </Leq> `
            </And> `
          </Where>"


    $query = "<View><Query>$dateFilter</Query><ViewFields>$viewFields</ViewFields></View>"

    $numTries = 0
    $maxTries = 2
    do
    {
        try
        {
            $numTries++
            $results = Get-PnPListItem -Connection $global:SharePointConnection -List $SharePointListName -Query $query | ForEach-Object { $_.FieldValues }
            $numTries = $maxTries
        }
        catch
        {
            if ($_.Exception.Message -like "*Connection holds no SharePoint context*")
            {
                $lastConnectedSharePointSiteProperties = Get-ConnectedSharePointSiteProperties
                                # try to reconnect to the SharePoint site
                connectToSharePointSite `
                    -SharePointSiteName $lastConnectedSharePointSiteProperties.SharePointSiteName `
                    -SharePointOrgRootUrl $lastConnectedSharePointSiteProperties.SharePointOrgRootUrl
                    }
            else
            {
                throw $_
            }
        }   
    }   while ($numTries -lt $maxTries)

    return $results
}
#EndRegion './Public/Read-SharepointListItems.ps1' 96
#Region './Public/Remove-ForeignKeyConstraints.ps1' 0
#function to drop all foreign key constraints from the database
function Remove-ForeignKeyConstraints($logFile,$dbConnection)
{
  $Query = "exec dbo.DeleteAllForeignKeyConstraints"
   
  #Run the Query
  # Write-Output $Query
  Invoke-SQLQuery -logFile $logFile -dbConnection $dbConnection -Query $Query | Out-Null
}
#EndRegion './Public/Remove-ForeignKeyConstraints.ps1' 10
#Region './Public/Remove-SQLTable.ps1' 0
#function to Drop Table, if exists!
function Remove-SQLTable($logFile,$dbConnection,[string]$TableName)
{
  $Query = "IF (OBJECT_ID('[dbo].[$($TableName)]','U') IS NOT NULL) DROP TABLE [dbo].[$($TableName)]"
   
  #Run the Query
  # Write-Output $Query
  Invoke-SQLQuery -logFile $logFile -dbConnection $dbConnection -Query $Query | Out-Null
}
#EndRegion './Public/Remove-SQLTable.ps1' 10
#Region './Public/Set-AutomationVariableValue.ps1' 0
# sets the value of an Azure automation variable - the function encapsulates the different mechanisms based upon whether the script
# is running in Azure or on a user's desktop
function Set-AutomationVariableValue
{
    param([Parameter(Mandatory)][string]$Name,[string]$Value,[bool]$Encrypted = $false)

    if (Confirm-ScriptIsRunningInAzure -eq $true)
    {
        $variableValue = Set-AutomationVariable -Name $Name -Value $Value 
    }
    else
    {
        $azureProperties = Get-AzureProperties
        $variableObj = Set-AzAutomationVariable -Encrypted $Encrypted -Name $Name -Value $Value -ResourceGroupName $azureProperties.resourceGroupName -AutomationAccountName $azureProperties.automationAccountName
    }
}
#EndRegion './Public/Set-AutomationVariableValue.ps1' 17
#Region './Public/Set-AzureCertificateProperties.ps1' 0
$Script:azureCertificateProperties = @{
    appId = $null
    thumbprint = $null
}

function Set-AzureCertificateProperties
{
    param(
        [Parameter(Mandatory)] [string]$appId,
        [Parameter(Mandatory)] [string]$certThumbprint
    )

    $Script:azureCertificateProperties = @{
        appId = $appId
        thumbprint = $certThumbPrint
    }
}
#EndRegion './Public/Set-AzureCertificateProperties.ps1' 18
#Region './Public/Set-AzureProperties.ps1' 0
$Script:azureProperties = @{
    resourceGroupName = $null
    automationAccountName = $null
    organisationDomain = $null
}

function Set-AzureProperties
{
    param(
        [Parameter(Mandatory)] [string]$resourceGroupName,
        [Parameter(Mandatory)] [string]$automationAccountName,
        [Parameter(Mandatory)] [string]$organisationDomain
    )

    $Script:azureProperties = @{
        resourceGroupName = $resourceGroupName
        automationAccountName = $automationAccountName
        organisationDomain = $organisationDomain
    }
}


#TODO: do we put variables in a variables section of this Visual Studio project?
#EndRegion './Public/Set-AzureProperties.ps1' 24
#Region './Public/Set-TwilioApiKey.ps1' 0
$Script:twilioSendGridApiKey = $null

# set an API key so that Twilio SendGrid can be used to send emails
function Set-TwilioApiKey([Parameter(Mandatory)][string]$apiKey)
{
     $Script:twilioSendGridApiKey = $apiKey
}
#EndRegion './Public/Set-TwilioApiKey.ps1' 8