pwps_dab

2.1.12.0

HelperFunctions.psm1

#region Security

function Save-ClearTextToEncryptedFile ($Password, $FileName)
{
    $secureStringPwd = $Password | ConvertTo-SecureString -AsPlainText -Force
    #$secureStringPwd = New-Object PSCredential ("Dummy User", $Password) | Select-Object -ExpandProperty Password
    $secureStringText = $secureStringPwd | ConvertFrom-SecureString
    Set-Content $FileName $secureStringText
}

function Save-SecureStringToEncryptedFile ($FileName, $Prompt)
{
    if ($Prompt -eq $null) {$Prompt = "Enter Password:"}
    $secureStringPwd = Read-Host -Prompt $Prompt -AsSecureString
    $secureStringText = $secureStringPwd | ConvertFrom-SecureString
    Set-Content $FileName $secureStringText
}

function Get-SecureStringFromEncryptedFile ($FileName)
{
    $pwdTxt = Get-Content $FileName
    $securePwd = $pwdTxt | ConvertTo-SecureString
    Write-Output $securePwd
}

function Get-ClearTextFromEncryptedFile ($FileName)
{
    $pwdTxt = Get-Content $FileName
    $securePwd = $pwdTxt | ConvertTo-SecureString
    $BSTR = [System.Runtime.InteropServices.Marshal]::SecureStringToBSTR($securePwd)
    $clearText = [System.Runtime.InteropServices.Marshal]::PtrToStringAuto($BSTR)
    Write-Output $clearText
}
#endregion

#region Conversion
function ConvertTo-DataTable
{
    <#
            .SYNOPSIS
            Convert regular PowerShell objects to a DataTable object.
            .DESCRIPTION
            Convert regular PowerShell objects to a DataTable object.
            .EXAMPLE
            $myDataTable = $myObject | ConvertTo-DataTable
 
            # using the SqlServer PowerShell module to connect to SQL Server and query for and return data
            # returns data as an array of DataRow objects
            $drs=Invoke-Sqlcmd -ServerInstance "ServerName" -Database Databasename -Username UserName -Password Password -Query "SELECT * FROM [dbo].[DrawingValidation] where Owner='None' and UpToDate=1 order by stamptime desc"
 
            # use this function to Convert the DataRow array to a DataTable
            $dt=ConvertTo-DataTable $drs
 
            # use PWPS_DAB cmdlet to output the DataTable into a spreadsheet
            New-XLSXWorkbook -InputTables $dt -OutputFileName c:\temp\Output.xlsx
 
    #>
    [CmdletBinding()]
    param (
        # The object to convert to a DataTable
        [Parameter(ValueFromPipeline = $true)]
        [PSObject[]] $InputObject,

        # Override the default type.
        [Parameter()]
        [string] $DefaultType = 'System.String'
    )

    begin {

        # create an empty datatable
        try {
            $dataTable = New-Object -TypeName 'System.Data.DataTable'
            Write-Verbose -Message 'Empty DataTable created'
        }

        catch {
            Write-Warning -Message $_.Exception.Message
            break
        }

        # define a boolean to keep track of the first datarow
        $first = $true

        # define array of supported .NET types
        $types = @(
            'System.String',
            'System.Boolean',
            'System.Byte[]',
            'System.Byte',
            'System.Char',
            'System.DateTime',
            'System.Decimal',
            'System.Double',
            'System.Guid',
            'System.Int16',
            'System.Int32',
            'System.Int64',
            'System.Single',
            'System.UInt16',
            'System.UInt32',
            'System.UInt64'
        )
    }

    process {

        # iterate through each input object
        foreach ($object in $InputObject) {

            try {

                # create a new datarow
                $dataRow = $dataTable.NewRow()
                Write-Debug -Message 'New DataRow created'

                # iterate through each object property
                foreach ($property in $object.PSObject.get_properties()) {

                    # check if we are dealing with the first row or not
                    if ($first) {

                        # handle data types
                        if ($types -contains $property.TypeNameOfValue) {
                            $dataType = $property.TypeNameOfValue
                            Write-Debug -Message "$($property.Name): Supported datatype <$($dataType)>"
                        }

                        else {
                            $dataType = $DefaultType
                            Write-Debug -Message "$($property.Name): Unsupported datatype ($($property.TypeNameOfValue)), using default <$($DefaultType)>"
                        }

                        # create a new datacolumn
                        $dataColumn = New-Object 'System.Data.DataColumn' $property.Name, $dataType
                        Write-Debug -Message 'Created new DataColumn'

                        # add column to DataTable
                        $dataTable.Columns.Add($dataColumn)
                        Write-Debug -Message 'DataColumn added to DataTable'
                    }

                    # add values to column
                    if ($property.Value -ne $null) {

                        # handle data types
                        if ($types -contains $property.TypeNameOfValue) {
                            $dataType = $property.TypeNameOfValue
                            Write-Debug -Message "$($property.Name): Supported datatype <$($dataType)>"
                        }

                        # if array or collection, add as XML
                        if (($property.Value.GetType().IsArray) -or ($property.TypeNameOfValue -like '*collection*')) {
                            $dataRow.Item($property.Name) = $property.Value | ConvertTo-Xml -As 'String' -NoTypeInformation -Depth 1
                            Write-Debug -Message 'Value added to row as XML'
                        }

                        else{
                            $dataRow.Item($property.Name) = $property.Value -as $dataType
                            Write-Debug -Message "Value ($($property.Value)) added to row as $($dataType)"
                        }
                    }
                }

                # add DataRow to DataTable
                $dataTable.Rows.Add($dataRow)
                Write-Debug -Message 'DataRow added to DataTable'
            }

            catch {
                Write-Warning -Message $_.Exception.Message
            }

            $first = $false
        }
    }

    end {
        #"properties" that aren't really columns when this is passed an array of or DataRows
        if ($dataTable.Columns.Contains("RowError")) { $dataTable.Columns.Remove("RowError")}
        if ($dataTable.Columns.Contains("RowState")) { $dataTable.Columns.Remove("RowState")}
        if ($dataTable.Columns.Contains("Table")) { $dataTable.Columns.Remove("Table")}
        if ($dataTable.Columns.Contains("ItemArray")) { $dataTable.Columns.Remove("ItemArray")}
        if ($dataTable.Columns.Contains("HasErrors")) { $dataTable.Columns.Remove("HasErrors")}

        Write-Output (,($dataTable))
    }
}
#endregion

#region Utility

FUNCTION New-PWScanForReferences
{
    <#
            .SYNOPSIS
            Runs the ScanRef.exe with provided parameter values.
            .DESCRIPTION
            The function runs a scan for reference job using the parameter values passed. MUST be run in a 32Bit session of PowerShell.
            .INPUTS
            None
            .OUTPUTS
            None
            .EXAMPLE
            There are many options and configurations for this function. Use the following as a template to setup your reference scanning.
            $NewScanRef = @{
            DataSourceName = '';
            UserName = '';
            Password = '';
            ScanMode = 'references';
            MasterDocuments = '';
            MasterFolders = '';
            Priority = '';
            Proximity = 'r:1';
            Order = 'priority;proximity';
            Applications = '';
            LogFilePath = '';
            }
            New-PWScanForReferences @NewScanRef -RecurseMasterFolders -RecursePriority -OpenLogFile -Verbose
    #>
    [CMDLETBINDING()]
    PARAM(
        [Parameter(
                Position=0,
                Mandatory=$true,
            HelpMessage = "ProjectWise Datasource to log into.")]
        [string]$DataSourceName,

        [PARAMETER(
                Position=1,
                Mandatory=$false,
            HelpMessage = "If this option is absent, SSO login will be attempted.")]
        [string]$UserName,

        [PARAMETER(
                Position=2,
                Mandatory=$false,
            HelpMessage = "If this option is absent, password prompt will be shown. When username is supplied.")]
        [string]$Password,

        [PARAMETER(
                Position=3,
                Mandatory=$false,
            HelpMessage = "Specifies a list of scanning modes to use. If not included, defaults to references;linksets.")]
        [ValidateSet("references", "linksets", "references;linksets")]
        [string[]]$ScanMode = "references;linksets",

        [PARAMETER(
                Mandatory=$false,
            HelpMessage = "A list of documents to scan for references and/or linksets.")]
        [string[]]$MasterDocuments,

        [PARAMETER(
                Mandatory=$false,
            HelpMessage = "A list of folders to scan for references and/or linksets.")]
        [string[]]$MasterFolders,

        [PARAMETER(
                Mandatory=$false,
            HelpMessage = "When included, all sub-folders within the supplied folder path will be scanned for references and/or linksets.")]
        [switch]$RecurseMasterFolders,

        [PARAMETER(
                Mandatory=$false,
            HelpMessage = "Enable priority search for reference files in the specified folders. To recurse folders, prefix with r:.")]
        [string[]]$Priority,

        [PARAMETER(
                Mandatory=$false,
            HelpMessage = "When included, all sub-folders within the supplied folder path will be scanned for references and/or linksets.")]
        [switch]$RecursePriority,

        [PARAMETER(
                Mandatory=$false,
            HelpMessage = "Enable proximity search for reference files <number> levels above the master file's folder. To recurse folders, prefix with r:.")]
        [ValidateSet("0", "1", "r:1")]
        [string[]]$Proximity,

        [PARAMETER(
                Mandatory=$false,
            HelpMessage = "Order in which the proximity and priority searches will be done (if both are enabled). If this parameter is not specified, proximity search will take precedence.")]
        [ValidateSet("proximity", "priority", "priority;proximity", "proximity;priority")]
        [string]$Order = "proximity;priority",

        [PARAMETER(
                Mandatory=$false,
            HelpMessage = "Application filter for the lists of documents to scan - only the documents with matching application names will be scanned.")]
        [string[]]$Applications,

        [PARAMETER(
                Mandatory=$false,
            HelpMessage = "Log file path.")]
        [string]$LogFilePath,

        [PARAMETER(
                Mandatory=$false,
            HelpMessage = "When included, the log file will be open when process is complete.")]
        [switch]$OpenLogFile
    )

    BEGIN {
        $Continue = $true
        # This function will only run within a 32Bit session of PowerShell.
        if($env:PROCESSOR_ARCHITECTURE -ne 'x86') {
            Write-Warning -Message "This New-PWScanForReferences function will ONLY run in a 32Bit session of PowerShell."
            $Continue = $false
        }
    }

    PROCESS {
        if($Continue = $false) {
            return
        }
        
        # Variables:
        #$DataSourceName = $datasource #'BMF_W2K12R2:PSTraining'
        #$UserName = $user #'admin'
        ## Entered in clear text. This can be modified to encrypt or lookup password.
        #$Password = $password # 'admin'

        # The following retrieves the ProjectWise path information from the registry.
        #$execpath = # Get-PWScanRefsPath
        #
        ## Stops processing if the path information is not found.
        #if([string]::IsNullOrEmpty($ExecPath)) {
        # Write-Error("Failed to get ProjectWise path.")
        # return;
        #}

        # Verify the scanrefs executable exists on the current machine. Stops processing if not found.
        $executable = "C:\Program Files (x86)\Bentley\ProjectWise\bin\scanrefs.exe" #(Get-PWScanRefsPath) + "\scanrefs.exe"
        if(-not (Test-Path -Path $executable))
        {
            Write-Error ("Could not find scanrefs.exe file.")
            return;
        } else {
            "Found scanrefs.exe file."
        } # end if/else

        # -d datasource Datasource name to connect to.
        $dsname = $DataSourceName
        Write-Verbose "DSName = $dsname"
        # -u username Username for the datasource connection.
        # If this option is absent, SSO login will be attempted.
        $user = $UserName
        Write-Verbose "UserName = $user"
        # -p password Password of the datasource user.
        # If this option is absent, password prompt will be shown.
        $pw = $Password
        Write-Verbose "PW = $pw"

        # -mode scanmode Specifies a list of scanning modes to use.
        # If this option is absent, the tool will operate in "references;linksets" mode.
        #
        # Possible modes:
        # references - scan for references.
        # linksets - scan DGN documents for link sets.
        # urfcs - update references from current set - no attempt will be made at creating new sets,
        # only existing reference sets will be updated.
        # If this mode is enabled, priority or proximity reference search paths will be ignored.
        # This option is intended to be used after upgrading a datasource from a pre-V8.1 version.
        # Note that this mode disables other modes.
        # Modes can be combined:
        # references;linksets - scan for reference documents and for DGN link sets.
        # NOTE: Do not add spaces between mode labels. This will cause an error (Unrecognized scanning mode:)
        $scanmode1 = $ScanMode # 'references'
        Write-Verbose "ScanMode = $scanmode1"

        # -masters documentlist A list of documents to scan for references and/or linksets.
        if([string]::IsNullOrEmpty($MasterDocuments)) {
            $masterdocuments1 = -1
        } else {
            $masterdocuments1 = $MasterDocuments
            Write-Verbose "MasterDocuments = $masterdocuments1"
        } # end if/else

        # -masterfolders folderlist A list of folders to scan for references and/or linksets.
        # If RecurseMasterFolders is included, prefix master folders with r:.
        if([string]::IsNullOrEmpty($MasterFolders)) {
            $masterfolders1 = -1
        } else {
            $masterfolders1 = $MasterFolders #"r:Documents\Projects\NewFolder\BSI900 - Adelaide Tower"
            if($RecurseMasterFolders){
                $masterfolders1 = "r:$MasterFolders"
            }
        } # end if/else
        Write-Verbose "MasterFolders = $masterfolders1"

        # -priority folderlist Enable priority search for reference files in the specified folders.
        # Prefix folder path with "r:" to recurse through the folder and sub-folders.
        # NOTE: Ensure the folder path does NOT end with a backslash "\". This will cause an error.
        # NOTE: Do not add spaces between folder paths. This will cause an error.
        if([string]::IsNullOrEmpty($Priority)) {
            $priority1 = -1
        } else {
            $priority1 = $Priority #"r:Documents\BSI900 - Adelaide Tower\05-Incoming;r:Documents\BSI900 - Adelaide Tower\03-Published"
            if($RecursePriority){
                $priority1 = "r:$Priority"
            }
            Write-Verbose "Priority = $priority1"
        } # end if/else

        ### Proximity is required when priority is not set.
        #
        # -proximity [r:]number Enable proximity search for reference files <number> levels above the master file's folder.
        # r: switch enables recursive search (includes subfolders).
        # Examples:
        # -proximity 0 - look for references in the master's folder.
        # -proximity 1 - look for references in the parent folder of the master.
        # -proximity r:1 - look for references in the master's folder's parent folder and its subfolders.
         if([string]::IsNullOrEmpty($Proximity)) {
            if($priority1 -eq -1) {
                Write-Verbose "Setting proximity to 0 due to priority not being included."
                $proximity1 = 0
            } else {
                $proximity1 = -1
            } # end if/else
         } else {
            $proximity1 = $Proximity #"r:1"
            Write-Verbose "Proximity = $proximity1"
        } # end if/else

        # -order orderlist Order in which the proximity and priority searches will be done (if both are enabled).
        # If this parameter is not specified, proximity search will take precedence.
        # Examples:
        # -order proximity;priority - proximity first.
        # -order priority;proximity - priority first.
        # NOTE: Do not add spaces between order label. This will cause an error (Input error: Unknown search type:).
        $order1 = $Order #"priority;proximity"
        Write-Verbose "Order = $order1"

        # -af applicationlist Application filter for the lists of documents to scan - only the documents with matching application names will be scanned.
        # Example:
        # -af "MicroStation;AutoCad;Bentley Navigator"
        # NOTE: This is case sensative, so ensure the names are listed exactly as they are in ProjectWise Administrator.
        # NOTE: Do not add spaces between application names. This will cause an error (Collecting data... Failure).
        if([string]::IsNullOrEmpty($Applications)) {
            $applications1 = -1
        } else {
            $applications1 = $Applications #"AutoCAD;MicroStation"
            Write-Verbose "Applications = $applications1"
        } # end if/else

        # -l logfile Log file path.
        # If logfile path and name is not included, set default to 'c:\temp\'.
        if([string]::IsNullOrEmpty($LogFilePath)) {
            $logfilePath1 = 'c:\temp\'
        } else {
            $logfilePath1 = $LogFilePath
        } # end if/else

        # Test to determine if the log folder exists. If not, create.
        if(-not (Test-Path -Path $logfilePath1)) {
            New-Item -Path $logfilePath1 -ItemType "directory"
        }

        $logfile = "$logfilePath1\ScanRef.log"
        # -lv Use verbose logging - write more details to the log file.


        # masterfolders
        # masterdocuments
        # applications
        # priority
        # If priority is enabled, proximity is not required. If priority is not included, proximity is required.


        # Master Folders ONLY
        if(($masterfolders1 -ne -1) -and ($masterdocuments1 -eq -1)) {
            if($applications1 -eq -1) {
                # Masterfolders1, no applications, no priority, proximity
                # Masterfolders1, no applications, priority, no proximity
                # Masterfolders1, no applications, priority, proximity
                if($priority1 -eq -1) {
                    Write-Verbose "Only master folders will be scanned. Applications NOT included. Priority NOT included. Proximity included."
                    & $executable -d $dsname -u $user -p $pw -mode $scanmode1 -masterfolders $masterfolders1 -proximity $proximity1 -order $order1 -l $logfile -lv
                } elseif ($proximity1 -eq -1) {
                    Write-Verbose "Only master folders will be scanned. Applications NOT included. Priority included. Proximity NOT included."
                    & $executable -d $dsname -u $user -p $pw -mode $scanmode1 -masterfolders $masterfolders1 -priority $priority1 -order $order1 -l $logfile -lv
                } else {
                    Write-Verbose "Only master folders will be scanned. Applications NOT included. Priority included. Proximity included."
                    & $executable -d $dsname -u $user -p $pw -mode $scanmode1 -masterfolders $masterfolders1 -priority $priority1 -proximity $proximity1 -order $order1 -l $logfile -lv
                } # end if/elseif/else

            } else {
                # Masterfolders1, applications, no priority, proximity
                # Masterfolders1, applications, priority, no proximity
                # Masterfolders1, applications, priority, proximity
                if($priority1 -eq -1) {
                    Write-Verbose "Only master folders will be scanned. Applications included. Priority NOT included. Proximity included."
                    & $executable -d $dsname -u $user -p $pw -mode $scanmode1 -masterfolders $masterfolders1 -af $applications1 -proximity $proximity1 -order $order1 -l $logfile -lv
                } elseif ($proximity1 -eq -1) {
                    Write-Verbose "Only master folders will be scanned. Applications included. Priority included. Proximity NOT included."
                    & $executable -d $dsname -u $user -p $pw -mode $scanmode1 -masterfolders $masterfolders1 -af $applications1 -priority $priority1 -order $order1 -l $logfile -lv
                } else {
                    Write-Verbose "Only master folders will be scanned. Applications included. Priority included. Proximity included."
                    & $executable -d $dsname -u $user -p $pw -mode $scanmode1 -masterfolders $masterfolders1 -af $applications1 -priority $priority1 -proximity $proximity1 -order $order1 -l $logfile -lv
                } # end if/elseif/else
            } # end if/else
        } elseif (($masterfolders1 -eq -1) -and ($masterdocuments1 -ne -1)) {
            if($applications1 -eq -1) {
                # masterdocuments1, no applications, no priority, proximity
                # masterdocuments1, no applications, priority, no proximity
                # masterdocuments1, no applications, priority, proximity
                if($priority1 -eq -1) {
                    Write-Verbose "Only master documents will be scanned. Applications NOT included. Priority NOT included. Proximity included."
                    & $executable -d $dsname -u $user -p $pw -mode $scanmode1 -masters $masterdocuments1 -proximity $proximity1 -order $order1 -l $logfile -lv
                } elseif ($proximity1 -eq -1) {
                    Write-Verbose "Only master documents will be scanned. Applications NOT included. Priority included. Proximity NOT included."
                    & $executable -d $dsname -u $user -p $pw -mode $scanmode1 -masters $masterdocuments1 -priority $priority1 -order $order1 -l $logfile -lv
                } else {
                    Write-Verbose "Only master documents will be scanned. Applications NOT included. Priority included. Proximity included."
                    & $executable -d $dsname -u $user -p $pw -mode $scanmode1 -masters $masterdocuments1 -priority $priority1 -proximity $proximity1 -order $order1 -l $logfile -lv
                } # end if/elseif/else

            } else {
                # masterdocuments1, applications, no priority, proximity
                # masterdocuments1, applications, priority, no proximity
                # masterdocuments1, applications, priority, proximity
                if($priority1 -eq -1) {
                    Write-Verbose "Only master documents will be scanned. Applications included. Priority NOT included. Proximity included."
                    & $executable -d $dsname -u $user -p $pw -mode $scanmode1 -masters $masterdocuments1 -af $applications1 -proximity $proximity1 -order $order1 -l $logfile -lv
                } elseif ($proximity1 -eq -1) {
                    Write-Verbose "Only master documents will be scanned. Applications included. Priority included. Proximity NOT included."
                    & $executable -d $dsname -u $user -p $pw -mode $scanmode1 -masters $masterdocuments1 -af $applications1 -priority $priority1 -order $order1 -l $logfile -lv
                } else {
                    Write-Verbose "Only master documents will be scanned. Applications included. Priority included. Proximity included."
                    & $executable -d $dsname -u $user -p $pw -mode $scanmode1 -masters $masterdocuments1 -af $applications1 -priority $priority1 -proximity $proximity1 -order $order1 -l $logfile -lv
                } # end if/elseif/else
            } # end if/else
        } elseif (($masterfolders1 -ne -1) -and ($masterdocuments1 -ne -1)) {
            if($applications1 -eq -1) {
                # Masterfolders1, masterdocuments1, no applications, no priority, proximity
                # Masterfolders1, masterdocuments1, no applications, priority, no proximity
                # Masterfolders1, masterdocuments1, no applications, priority, proximity
                if($priority1 -eq -1) {
                    Write-Verbose "Both master folders and documents will be scanned. Applications NOT included. Priority NOT included. Proximity included."
                    & $executable -d $dsname -u $user -p $pw -mode $scanmode1 -masterfolders $masterfolders1 -masters $masterdocuments1 -proximity $proximity1 -order $order1 -l $logfile -lv
                } elseif ($proximity1 -eq -1) {
                    Write-Verbose "Both master folders and documents will be scanned. Applications NOT included. Priority included. Proximity NOT included."
                    & $executable -d $dsname -u $user -p $pw -mode $scanmode1 -masterfolders $masterfolders1 -masters $masterdocuments1 -priority $priority1 -order $order1 -l $logfile -lv
                } else {
                    Write-Verbose "Both master folders and documents will be scanned. Applications NOT included. Priority included. Proximity included."
                    & $executable -d $dsname -u $user -p $pw -mode $scanmode1 -masterfolders $masterfolders1 -masters $masterdocuments1 -priority $priority1 -proximity $proximity1 -order $order1 -l $logfile -lv
                } # end if/elseif/else

            } else {
                # Masterfolders1, masterdocuments1, applications, no priority, proximity
                # Masterfolders1, masterdocuments1, applications, priority, no proximity
                # Masterfolders1, masterdocuments1, applications, priority, proximity
                if($priority1 -eq -1) {
                    Write-Verbose "Both master folders and documents will be scanned. Applications included. Priority NOT included. Proximity included."
                    & $executable -d $dsname -u $user -p $pw -mode $scanmode1 -masterfolders $masterfolders1 -masters $masterdocuments1 -af $applications1 -proximity $proximity1 -order $order1 -l $logfile -lv
                } elseif ($proximity1 -eq -1) {
                    Write-Verbose "Both master folders and documents will be scanned. Applications included. Priority included. Proximity NOT included."
                    & $executable -d $dsname -u $user -p $pw -mode $scanmode1 -masterfolders $masterfolders1 -masters $masterdocuments1 -af $applications1 -priority $priority1 -order $order1 -l $logfile -lv
                } else {
                    Write-Verbose "Both master folders and documents will be scanned. Applications included. Priority included. Proximity included."
                    & $executable -d $dsname -u $user -p $pw -mode $scanmode1 -masterfolders $masterfolders1 -masters $masterdocuments1 -af $applications1 -priority $priority1 -proximity $proximity1 -order $order1 -l $logfile -lv
                } # end if/elseif/else
            } # end if/else
        } # end if/elseif/else
    } # end process

    END {
        # Opens log file
        if($OpenLogFile) {
            if(Test-Path $logfile) {
                explorer $logfile
            }
        } # end if
    } # end
}
#Export-ModuleMember -Function New-PWScanForReferences

FUNCTION New-PWEnumFolders
{
    <#
            .SYNOPSIS
            Runs the EnumFolders.exe with provided parameter values.
            .DESCRIPTION
            The function runs the enumfolders.exe using the parameter values passed.
            .INPUTS
            None
            .OUTPUTS
            None
            .EXAMPLE
            There are many options and configurations for this function. Use the following as a template to setup your reference scanning.
            $NewEnum = @{
            DataSourceName = '';
            UserName = '';
            Password = '';
            PWFolder = '';
            OutputFilePath = '';
            }
            New-PWEnumFolders @NewEnum -IncludeDocuments -RecurseFolders-OpenLogFile -Verbose
    #>
    [CMDLETBINDING()]
    PARAM(
        [Parameter(
                Position=0,
                Mandatory=$true,
            HelpMessage = "ProjectWise Datasource to log into.")]
        [string]$DataSourceName,

        [PARAMETER(
                Position=1,
                Mandatory=$false,
            HelpMessage = "If this option is absent, SSO login will be attempted.")]
        [string]$UserName,

        [PARAMETER(
                Position=2,
                Mandatory=$false,
            HelpMessage = "If this option is absent, password prompt will be shown. When username is supplied.")]
        [string]$Password,

        [PARAMETER(
                Position=3,
                Mandatory=$false,
            HelpMessage = "Specifies a folder ID to enumerate. If not included, returns information for the entire datasource.")]
        [int]$PWFolderID = 0,

        [PARAMETER(
                Mandatory=$false,
            HelpMessage = "When included, all sub-folders are enumerated.")]
        [switch]$RecurseFolders,

        [PARAMETER(
                Mandatory=$false,
            HelpMessage = "Include documents within the enumeration process.")]
        [switch]$IncludeDocuments,

        [PARAMETER(
                Mandatory=$false,
            HelpMessage = "Include deleted objects within the enumeration process.")]
        [switch]$IncludeDeletedObjects,

        [PARAMETER(
                Mandatory=$false,
            HelpMessage = "Uses verbose operation. (Does not appear to do anything.)")]
        [switch]$VerboseOperation,

        [PARAMETER(
                Mandatory=$false,
            HelpMessage = "Output file path and name.")]
        [string]$OutputFilePathName = "c:\temp\"  + (Get-Date).Year + (Get-Date).Month + (Get-Date).Day + "_enumfolders.txt",

        [PARAMETER(
                Mandatory=$false,
            HelpMessage = "When included, the output file will be open when process is complete.")]
        [switch]$OpenOutputFile
    )

    BEGIN {

    }

    PROCESS {
        <#
                Enumerates the objects in a folder hierarchy.
                Options:
                -d datasource hostname:datasource.
                -u user ProjectWise username (omit for domain auth).
                -p password ProjectWise password (omit for domain auth).
                -f folder # Folder to enumerate (GUID or integer folder id)
                -v Verbose operation.
                -D Include documents in object list
                -l Include deleted objects in object list
                -r Recursive enumeration operation. All subfolders are
                                    enumerated, along with any objects in those subfolders.
                -o filename Save results to the specified file.
        #>

        # Verify the enumfolders.exe executable exists on the current machine. Stops processing if not found.
        $executable = "C:\Program Files (x86)\Bentley\ProjectWise\bin\enumfolders.exe"
        if(-not (Test-Path -Path $executable))
        {
            Write-Error "Could not find enumfolders.exe file."
            return;
        } else {
            Write-Verbose "Found enumfolders.exe file." -Verbose
        } # end if/else

        # -d datasource Datasource name to connect to.
        $dsname = $DataSourceName
        Write-Verbose "DSName = $dsname"
        # -u username Username for the datasource connection.
        # If this option is absent, SSO login will be attempted.
        $user = $UserName
        Write-Verbose "UserName = $user"
        # -p password Password of the datasource user.
        # If this option is absent, password prompt will be shown.
        $pw = $Password
        Write-Verbose "PW = $pw"

        Write-Verbose "FolderID: $PWFolderID" -Verbose
        Write-Verbose "Recurse: $RecurseFolders" -Verbose
        Write-Verbose "IncludeDocuments: $IncludeDocuments" -Verbose
        Write-Verbose "IncludeDeletedObjects: $IncludeDeletedObjects" -Verbose
        Write-Verbose "VerboseOperation: $VerboseOperation" -Verbose


        # RecurseFolders
        # If RecurseFolders ONLY
        if($RecurseFolders -and (-not($IncludeDocuments)) -and (-not($IncludeDeletedObjects)) -and (-not($VerboseOperation))) {
            Write-Verbose "Recurse through all folders ONLY. Do not include documents." -Verbose
            Write-Verbose "IncludeDocuments NOT included. IncludeDeletedObjects NOT included. VerboseOperation NOT included." -Verbose
            & $executable -d $dsname -u $user -p $pw -f $PWFolderID -r -o $OutputFilePathName
        } elseif($RecurseFolders -and $IncludeDocuments -and (-not($IncludeDeletedObjects)) -and (-not($VerboseOperation))) {
            Write-Verbose "Recurse through all folders and documents." -Verbose
            Write-Verbose "IncludeDocuments IS included. IncludeDeletedObjects NOT included. VerboseOperation NOT included." -Verbose
            & $executable -d $dsname -u $user -p $pw -f $PWFolderID -r -D -o $OutputFilePathName
        } elseif($RecurseFolders -and (-not($IncludeDocuments)) -and $IncludeDeletedObjects -and (-not($VerboseOperation))) {
            Write-Verbose "Recurse through all folders. Do not include documents." -Verbose
            Write-Verbose "IncludeDocuments NOT included. IncludeDeletedObjects IS included. VerboseOperation NOT included." -Verbose
            & $executable -d $dsname -u $user -p $pw -f $PWFolderID -r -l -o $OutputFilePathName
        } elseif($RecurseFolders -and (-not($IncludeDocuments)) -and (-not($IncludeDeletedObjects)) -and $VerboseOperation) {
            Write-Verbose "Recurse through all folders and documents." -Verbose
            Write-Verbose "IncludeDocuments NOT included. IncludeDeletedObjects NOT included. VerboseOperation IS included." -Verbose
            & $executable -d $dsname -u $user -p $pw -f $PWFolderID -r -v -o $OutputFilePathName
        } elseif($RecurseFolders -and $IncludeDocuments -and $IncludeDeletedObjects -and (-not($VerboseOperation))) {
            Write-Verbose "Recurse through all folders and documents." -Verbose
            Write-Verbose "IncludeDocuments IS included. IncludeDeletedObjects IS included. VerboseOperation NOT included." -Verbose
            & $executable -d $dsname -u $user -p $pw -f $PWFolderID -r -D -l -o $OutputFilePathName
        } elseif($RecurseFolders -and $IncludeDocuments -and $IncludeDeletedObjects -and $VerboseOperation) {
            Write-Verbose "Recurse through all folders and documents." -Verbose
            Write-Verbose "IncludeDocuments IS included. IncludeDeletedObjects IS included. VerboseOperation IS included." -Verbose
            & $executable -d $dsname -u $user -p $pw -f $PWFolderID -r -D -l -v -o $OutputFilePathName
        } elseif($RecurseFolders -and (-not($IncludeDocuments)) -and $IncludeDeletedObjects -and $VerboseOperation) {
            Write-Verbose "Recurse through all folders and documents." -Verbose
            Write-Verbose "IncludeDocuments NOT included. IncludeDeletedObjects IS included. VerboseOperation IS included." -Verbose
            & $executable -d $dsname -u $user -p $pw -f $PWFolderID -r -l -v -o $OutputFilePathName
        }

        # Without RecurseFolders
        if((-not ($RecurseFolders)) -and (-not($IncludeDocuments)) -and (-not($IncludeDeletedObjects)) -and (-not($VerboseOperation))) {
            Write-Verbose "Current folder ONLY. Do not include documents." -Verbose
            Write-Verbose "IncludeDocuments NOT included. IncludeDeletedObjects NOT included. VerboseOperation NOT included." -Verbose
            & $executable -d $dsname -u $user -p $pw -f $PWFolderID -o $OutputFilePathName
        } elseif((-not ($RecurseFolders)) -and $IncludeDocuments -and (-not($IncludeDeletedObjects)) -and (-not($VerboseOperation))) {
            Write-Verbose "Current folder and documents." -Verbose
            Write-Verbose "IncludeDocuments IS included. IncludeDeletedObjects NOT included. VerboseOperation NOT included." -Verbose
            & $executable -d $dsname -u $user -p $pw -f $PWFolderID -D -o $OutputFilePathName
        } elseif((-not ($RecurseFolders)) -and (-not($IncludeDocuments)) -and $IncludeDeletedObjects -and (-not($VerboseOperation))) {
            Write-Verbose "Recurse through all folders. Do not include documents." -Verbose
            Write-Verbose "IncludeDocuments NOT included. IncludeDeletedObjects IS included. VerboseOperation NOT included." -Verbose
            & $executable -d $dsname -u $user -p $pw -f $PWFolderID -l -o $OutputFilePathName
        } elseif((-not ($RecurseFolders))  -and (-not($IncludeDocuments)) -and (-not($IncludeDeletedObjects)) -and $VerboseOperation) {
            Write-Verbose "Recurse through all folders and documents." -Verbose
            Write-Verbose "IncludeDocuments NOT included. IncludeDeletedObjects NOT included. VerboseOperation IS included." -Verbose
            & $executable -d $dsname -u $user -p $pw -f $PWFolderID -v -o $OutputFilePathName
        } elseif((-not ($RecurseFolders)) -and $IncludeDocuments -and $IncludeDeletedObjects -and (-not($VerboseOperation))) {
            Write-Verbose "Recurse through all folders and documents." -Verbose
            Write-Verbose "IncludeDocuments IS included. IncludeDeletedObjects IS included. VerboseOperation NOT included." -Verbose
            & $executable -d $dsname -u $user -p $pw -f $PWFolderID -D -l -o $OutputFilePathName
        } elseif((-not ($RecurseFolders)) -and $IncludeDocuments -and $IncludeDeletedObjects -and $VerboseOperation) {
            Write-Verbose "Recurse through all folders and documents." -Verbose
            Write-Verbose "IncludeDocuments IS included. IncludeDeletedObjects IS included. VerboseOperation IS included." -Verbose
            & $executable -d $dsname -u $user -p $pw -f $PWFolderID -D -l -v -o $OutputFilePathName
        } elseif((-not ($RecurseFolders)) -and (-not($IncludeDocuments)) -and $IncludeDeletedObjects -and $VerboseOperation) {
            Write-Verbose "Recurse through all folders and documents." -Verbose
            Write-Verbose "IncludeDocuments NOT included. IncludeDeletedObjects IS included. VerboseOperation IS included." -Verbose
            & $executable -d $dsname -u $user -p $pw -f $PWFolderID -l -v -o $OutputFilePathName
        }
    } # end process

    END {
        # Opens log file
        if($OpenOutputFile) {
            if(Test-Path $OutputFilePathName) {
                explorer $OutputFilePathName
            }
        } # end if
    } # end
}
#Export-ModuleMember -Function New-PWEnumFolders

#endregion
#region Logging

function Write-PWPSLog
{

    <#
            .SYNOPSIS
            Writes and appends logs to a log file.
            .DESCRIPTION
            Writes and appends logs to a log file. Format for each row is "DateTime [Level] Cmdlet - Message". Max file size for a single log is 10MB, and a maximum of four log files will be created. The older file will rollover if four files exist and the current file size exceeds the size limit.
            .PARAMETER Message
            Log message to write.
            .PARAMETER Path
            Path to target log file. File will be created if it doesn't exist. Default path is "C:\users\<username>\AppData\Local\Bentley\Logs\PowerShellLogging.log".
            .PARAMETER Level
            Severity level for the target log line. Accepts 'Error', 'Warn' and 'Info'.
            .PARAMETER Cmdlet
            Name of the cmdlet/function/script writing the log line. This is for sorting purposes when analysing logs, so source function/cmdlet/script can be easily identified.
            .PARAMETER NoClobber
            Switch to enagle NoClobber. If enabled and the target log file already exists, the log will not be written and the file cannot be overwritten.
            .EXAMPLE
            This example will write an info level log with function name Test-Logging to the default log location, with the message 'We are testing the logging'.
            Write-PWPSLog -Cmdlet 'Test-Logging' -Level Info -Message 'We are testing the logging'.
            .EXAMPLE
            This example will write an error level log with function name Test-Logging to C:\temp\log.log, with the message 'There was an error!'.
            Write-PWPSLog -Cmdlet 'Test-Logging' -Level Error -Message 'There was an error!'.
 
    #>

    [CmdletBinding()]

    Param
    (

        [Parameter(Mandatory=$true, ValueFromPipelineByPropertyName=$true)]
        [ValidateNotNullOrEmpty()]
        [Alias("LogContent")]
        [string]$Message,

        [Parameter(Mandatory=$false)]
        [Alias('LogPath')]
        [string]$Path="$env:LOCALAPPDATA" + "\Bentley\Logs\PowerShellLogging.log",

        [Parameter(Mandatory=$false)]
        [ValidateSet("Error","Warn","Info")]
        [string]$Level="Info",

        [Parameter(Mandatory=$true)]
        [string]$Cmdlet,

        [Parameter(Mandatory=$false)]
        [switch]$NoClobber

    )

    Begin
    {

        # Set VerbosePreference to Continue so that verbose messages are displayed.

        #$VerbosePreference = 'Continue'

        #
        # Rollover logs if size is exceeded
        #

        if ((Get-Item -LiteralPath $Path -ErrorAction SilentlyContinue))
        {


            if ((Get-Item -LiteralPath $Path).Length -gt 9999999)
            {

                if ((Test-Path -LiteralPath "$Path.3"))
                {

                    Remove-Item -LiteralPath "$Path.3" -Force

                }

                if ((Test-Path -LiteralPath "$Path.2"))
                {

                    Rename-Item -LiteralPath "$Path.2" -NewName "$Path.3" -Force

                }

                if ((Test-Path -LiteralPath "$Path.1"))
                {

                    Rename-Item -LiteralPath "$Path.1" -NewName "$Path.2" -Force

                }

                Rename-Item -LiteralPath $Path -NewName "$Path.1" -Force

                New-Item $Path -Force -ItemType File | Out-Null

            }

        }


    }
    Process
    {

        # If the file already exists and NoClobber was specified, do not write to the log.

        if ((Test-Path $Path) -AND $NoClobber)
        {

            Write-Error "Log file $Path already exists, and you specified NoClobber. Either delete the file or specify a different name."
            Return

        }

        # If attempting to write to a log file in a folder/path that doesn't exist create the file including the path.

        elseif (!(Test-Path $Path))
        {

            Write-Verbose "Creating $Path."
            $NewLogFile = New-Item $Path -Force -ItemType File

        }

        else {
            # Nothing to see here yet.
            }


        # Format Date for our Log File
        $FormattedDate = Get-Date -Format "yyyy/MM/dd HH:mm:ss"

        # Write message to error, warning, or verbose pipeline and specify $LevelText
        switch ($Level)
        {
            'Error'
            {

                $LevelText = 'ERROR'
                Write-Error $Message

            }

            'Warn'
            {

                $LevelText = 'WARNING'
                Write-Warning $Message

            }
            'Info'
            {

                $LevelText = 'INFO'
                Write-Verbose $Message

            }

        }

        # Write log entry to $Path
        "$FormattedDate [$LevelText] $Cmdlet - $Message" | Out-File -FilePath $Path -Append

    }
    End
    {

        # Nothing

    }

}

function Get-PWPSLogsFromPreviousDays
{

    <#
            .SYNOPSIS
            Returns log entries written by Write-PWPSLog from a specified number of days ago.
            .DESCRIPTION
            Searches a log file written using the Write-PWPSLog function, and returns entries from a specified number of days ago. Returns records from a single day only. There is no logging for this cmdlet by design.
            .PARAMETER LogFilePath
            Target log file path to search. Default is 'C:\users\<username>\AppData\Local\Bentley\Logs\PowerShellLogging.log'.
            .PARAMETER DaysAgo
            Integer value to specify the desired number of days ago to return logs for. A value of '1' is equal to yesterday.
            .PARAMETER IncludeIntermediateDays
            Switch to include intermediate results. If days is '5' and this switch is activated, log entries from 5 days ago up until now will be returned, as opposed to just the log entries from 5 days ago if this switch is not activated.
            .PARAMETER Level
            Target level to return. Default is set to return all levels. Takes multiple inputs. Acceptable input values are 'INFO','WARN' and 'ERROR'.
            .EXAMPLE
            This example will return all log entries from 10 days ago.
            Get-PowerShellLogsFromPreviousDays -DaysAgo 10 -Verbose
            .EXAMPLE
            This example will return all log entries from the last 10 days.
            Get-PowerShellLogsFromPreviousDays -DaysAgo 10 -IncludeIntermediateDays -Verbose
            .EXAMPLE
            This example will return info and warn log entries from 10 days ago.
            Get-PowerShellLogsFromPreviousDays -DaysAgo 10 -Level INFO,WARN -Verbose
            .EXAMPLE
            This example will return error log entries from the last 10 days.
            Get-PowerShellLogsFromPreviousDays -DaysAgo 10 -IncludeIntermediateDays -Level ERROR -Verbose
 
    #>

    [CmdletBinding()]

    Param
    (

        [Parameter(Mandatory=$false)]
        [string]$LogFilePath="$env:LOCALAPPDATA" + "\Bentley\Logs\PowerShellLogging.log",

        [Parameter(Mandatory=$true)]
        [ValidateNotNullOrEmpty()]
        [int]$DaysAgo,

        [Parameter(Mandatory=$false)]
        [switch]$IncludeIntermediateDays,

        [Parameter(Mandatory=$false)]
        [ValidateNotNullOrEmpty()]
        [ValidateSet("INFO","WARN","ERROR")]
        [string[]]$Level

    )

    Begin
    {

        ## Check log file exists

        if (!(Test-Path -LiteralPath $LogFilePath))
        {

            Write-Error "Log file not found! ($LogFilePath)"
            break;

        }

    }
    Process
    {

        #region Get Content

        try
        {

            $RawLogContent = Get-Content -LiteralPath $LogFilePath

        }
        catch
        {

            Write-Error "Failed to retrieve log content!"
            Write-Error $_.Exception.Message
            break;

        }

        #endregion Get Content

        #region Sort Log Content

        [System.Collections.ArrayList]$TargetLogEntries = @()

        if ($IncludeIntermediateDays)
        {

            While ($DaysAgo -ge 0)
            {

                foreach ($row in $RawLogContent)
                {

                    try
                    {

                        $RawDate = $row.Split(' ')[0]
                        $CalculatedDate = (Get-Date -Date $row.Split(' ')[0]).Date
                        $TargetDate = (Get-Date).AddDays(-$DaysAgo).Date

                        if ($CalculatedDate -eq $TargetDate)
                        {

                            $TargetLogEntries.Add($row) | Out-Null

                        }

                    }
                    Catch
                    {

                        Write-Warning "Could not read row!"
                        Write-Warning "Raw row content: $row"

                    }


                }

                $DaysAgo--

            }

        }
        else
        {

            foreach ($row in $RawLogContent)
            {

                try
                {

                    $RawDate = $row.Split(' ')[0]
                    $CalculatedDate = (Get-Date -Date $row.Split(' ')[0]).Date
                    $TargetDate = (Get-Date).AddDays(-$DaysAgo).Date

                    if ($CalculatedDate -eq $TargetDate)
                    {

                        $TargetLogEntries.Add($row) | Out-Null

                    }

                }
                Catch
                {

                    Write-Warning "Could not read row!"
                    Write-Warning "Raw row content: $row"

                }

            }

        }

        $TargetLogEntriesCount = ($TargetLogEntries | Measure-Object).Count

        Write-Verbose "Returned $TargetLogEntriesCount target log entries."

        #endregion Sort Log Content

        #region Filter Log Content

        if ($Level)
        {

            [System.Collections.ArrayList]$FilteredLogEntries = @()

            foreach ($row in $TargetLogEntries)
            {

                try
                {

                    $CalculatedLevel = $row.Split(' ')[2].TrimStart('[').TrimEnd(']')

                    if ($CalculatedLevel -in $Level)
                    {

                        $FilteredLogEntries.Add($row) | Out-Null

                    }

                }
                Catch
                {

                    Write-Warning "Could not read row!"
                    Write-Warning "Raw row content: $row"

                }

            }

            $FilteredLogEntriesCount = ($FilteredLogEntries | Measure-Object).Count

            Write-Verbose "Returned $FilteredLogEntriesCount filtered log entries."

        }

        #endregion Filter Log Content

    }
    End
    {


        if ($Level -and ($FilteredLogEntriesCount -gt 0))
        {

            Write-Output $FilteredLogEntries

        }
        elseif ($Level -and ($FilteredLogEntriesCount -eq 0))
        {

            Write-Warning "No log entries found for specified dates and levels."

        }
        elseif (!($Level) -and ($TargetLogEntriesCount -gt 0))
        {

            Write-Output $TargetLogEntries

        }
        elseif (!($Level) -and ($TargetLogEntriesCount -eq 0))
        {

            Write-Warning "No log entries found for specified dates."

        }
        else
        {

            Write-Error "Unable to output log entries!"

        }

    }

}

#endregion Logging

#region Windows

Function Get-WindowsFolderPath($InitialDirectory)
{
    [System.Reflection.Assembly]::LoadWithPartialName("System.windows.forms")|Out-Null

    $FolderBrowser = New-Object System.Windows.Forms.FolderBrowserDialog
    $FolderBrowser.Description = "Select a folder"
    $FolderBrowser.rootfolder = "MyComputer"

    if($FolderBrowser.ShowDialog() -eq "OK")
    {
        $FolderPath += $FolderBrowser.SelectedPath
    }

    return $FolderPath
}

Function Get-WindowsFilePath($InitialDirectory)
{
    [System.Reflection.Assembly]::LoadWithPartialName("System.windows.forms")|Out-Null

    $FileBrowser = New-Object System.Windows.Forms.OpenFileDialog
    $FileBrowser.Multiselect = $false
    $FileBrowser.Filter = 'Excel Workbooks (*.xls, *.xlsx)|*.xls;*.xlsx'

    if($FileBrowser.ShowDialog() -eq "OK")
    {
        $FilePath += $FileBrowser.FileName
    }

    return $FilePath
}

#endregion Windows

#region SQL

function BulkCopy-SQLTable
{

    <#
            .SYNOPSIS
            Bulk copies a datatable into a SQL table.
            .DESCRIPTION
            Copies the contents of an input datatable to a target table in the database. The target table can be truncated before bulk copy is executed. Database is specified in New-SQLConnection.
            .PARAMETER SQLConnection
            SQL Server connection generated using New-SQLConnection. Database must be specified in New-SQLConnection to use this BulkCopy-SQLTable.
            .PARAMETER Datatable
            Datatable containing the records to be bulk copied to the target SQL table. Must have matching schema with target table.
            .PARAMETER TruncateBeforeCopy
            If this switch parameter is activated, the target SQL table will be truncated before records from the input datatable are bulk copied.
            .EXAMPLE
            This example will bulk copy all records from the dms_audt datatable to the dms_audt SQL table in the database specified during SQL Connection.
            $SQLConnection = New-SQLConnection -SQLServer 'SQLONE' -Database 'DATABASEONE' -WindowsAuthentication
            $DataTable = Select-PWSQLDataTable -SQLSelectStatement "SELECT * FROM dms_audt"
            $DataTable.TableName = "dms_audt"
            BulkCopy-SQLTable -SQLConnection $SQLConnection -DataTable $DataTable
            .EXAMPLE
            This example will bulk copy all records from the dms_audt datatable to the dms_audt SQL table in the database specified during SQL Connection, truncating the SQL table before bulk copying.
            $SQLConnection = New-SQLConnection -SQLServer 'SQLONE' -Database 'DATABASEONE' -WindowsAuthentication
            $DataTable = Select-PWSQLDataTable -SQLSelectStatement "SELECT * FROM dms_audt"
            $DataTable.TableName = "dms_audt"
            BulkCopy-SQLTable -SQLConnection $SQLConnection -DataTable $DataTable -TruncateBeforeCopy
    #>

    [CmdletBinding()]

    Param
    (

        [Parameter(Mandatory=$true)]
        [ValidateNotNullOrEmpty()]
        [Microsoft.SqlServer.Management.Common.ConnectionManager]$SQLConnection,

        [Parameter(Mandatory=$true)]
        [ValidateNotNullOrEmpty()]
        [System.Data.DataTable]$DataTable,

        [Parameter(Mandatory=$false)]
        [switch]$TruncateBeforeCopy

    )

    Begin
    {

        #region Startup

        $Cmdlet = 'BulkCopy-SQLTable'

        #endregion Startup

        #region Parameter Checks

        ### Check database is specified in SQL Connection object

        if (!($SQLConnection.DatabaseName))
        {

            $Message = "No database specified in SQL Connection. Create new SQL Connection using New-SQLConnection, making sure to specify the database parameter."
            Write-Error $Message
            Write-PWPSLog -Cmdlet $Cmdlet -Message $Message -Level Error

            return;

        }

        #endregion Parameter Checks

    }
    Process
    {

        #region Truncate Table

        if ($TruncateBeforeCopy)
        {

            $Message = "Truncate switch activated. Truncating table '$($DataTable.TableName)' in database '$($SQLConnection.DatabaseName)'..."
            Write-Verbose $Message

            if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent)
            {

                Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message

            }

            try
            {

                Truncate-SQLTable -SQLConnection $SQLConnection -TableName $DataTable.TableName -ErrorAction Stop

            }
            catch
            {

                $Message = "$($PSItem.Exception.Message)"
                Write-Error $Message
                Write-PWPSLog -Cmdlet $Cmdlet -Message $Message -Level Error

                $Message = "Error truncating table."
                Write-Error $Message
                Write-PWPSLog -Cmdlet $Cmdlet -Message $Message -Level Error

                return;

            }

            $Message = "Successfully truncated table."
            Write-Verbose $Message

            if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent)
            {

                Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message

            }

        }

        #endregion Truncate Table

        #region Define SQL Objects

        ### Initiate Bulk Copy object

        $Message = "Initiating SQL Bulk Copy object."
        Write-Verbose $Message

        if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent)
        {

            Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message

        }

        try
        {

            $SQLBulkCopy = New-Object Data.SqlClient.SqlBulkCopy $SQLConnection
            $SQLBulkCopy.DestinationTableName = $Datatable.TableName

        }
        catch
        {

            $Message = "$($PSItem.Exception.Message)"
            Write-Error $Message
            Write-PWPSLog -Cmdlet $Cmdlet -Message $Message -Level Error

            $Message = "Error initiating SQL Bulk Copy object."
            Write-Error $Message
            Write-PWPSLog -Cmdlet $Cmdlet -Message $Message -Level Error

            return;

        }

        $Message = "Successfully initiated SQL Bulk Copy object."
        Write-Verbose $Message

        if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent)
        {

            Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message

        }

        #endregion Define SQL Objects

    }
    End
    {

        #region Bulk Copy table

        $Message = "Performing bulk copy of '$($Datatable.TableName)' to database '$($SQLConnection.DatabaseName)'..."
        Write-Verbose $Message

        if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent)
        {

            Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message

        }

        $Message = "Copying $($Datatable.Rows.Count) rows..."
        Write-Verbose $Message

        if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent)
        {

            Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message

        }

        try
        {

            $SQLBulkCopy.WriteToServer($Datatable)

        }
        catch
        {

            $Message = "$($PSItem.Exception.Message)"
            Write-Error $Message
            Write-PWPSLog -Cmdlet $Cmdlet -Message $Message -Level Error

            $Message = "Error bulk copying table."
            Write-Error $Message
            Write-PWPSLog -Cmdlet $Cmdlet -Message $Message -Level Error

            return;

        }

        $Message = "Successfully performed bulk copy."
        Write-Verbose $Message

        if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent)
        {

            Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message

        }

        #endregion Bulk Copy table

    }

}

function Get-SQLDatabase
{

    <#
            .SYNOPSIS
            Returns databases in a SQL instance.
            .DESCRIPTION
            Returns either all databases or a specified database from the SQL Server specified in New-SQLConnection.
            .PARAMETER SQLConnection
            SQL Server connection generated using New-SQLConnection.
            .PARAMETER Database
            Optional parameter to specify the name of database within the SQL Server instance to return. If not specified, all databases within the SQL Server instance will be returned.
            .EXAMPLE
            This example will return all databases in the SQL instance SQLONE.
            $SQLConnection = New-SQLConnection -SQLServer 'SQLONE' -WindowsAuthentication
            Get-SQLDatabase -SQLConnection $SQLConnection
            .EXAMPLE
            This example will return the database object for DATABASEONE from the SQL instance SQLONE.
            $SQLConnection = New-SQLConnection -SQLServer 'SQLONE' -WindowsAuthentication
            Get-SQLDatabase -SQLConnection $SQLConnection -Database "DATABASEONE"
    #>

    [CmdletBinding()]

    Param
    (

        [Parameter(Mandatory=$true)]
        [ValidateNotNullOrEmpty()]
        [Microsoft.SqlServer.Management.Common.ConnectionManager]$SQLConnection,

        [Parameter(Mandatory=$false)]
        [ValidateNotNullOrEmpty()]
        [string]$Database

    )

    Begin
    {

        #region Startup

        $Cmdlet = 'Get-SQLDatabase'

        #endregion Startup

    }
    Process
    {

        #region Define SQL Objects

        ### Initiate SMO

        $Message = "Initiating SQL Server Management object."
        Write-Verbose $Message

        if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent)
        {

            Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message

        }

        try
        {

            $SQLSMO = New-Object Microsoft.SqlServer.Management.Smo.Server $SQLConnection -ErrorAction Stop

        }
        catch
        {

            $Message = "$($PSItem.Exception.Message)"
            Write-Error $Message
            Write-PWPSLog -Cmdlet $Cmdlet -Message $Message -Level Error

            $Message = "Error initiating SQL Server object."
            Write-Error $Message
            Write-PWPSLog -Cmdlet $Cmdlet -Message $Message -Level Error

            return;

        }

        $Message = "Successfully initiated SQL Server Management object."
        Write-Verbose $Message

        if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent)
        {

            Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message

        }

        #endregion Define SQL Objects

        #region Database Check

        ### Check if database exists

        if ($Database)
        {

            $Message = "Checking if '$Database' exists in '$($SQLConnection.ServerInstance)'..."
            Write-Verbose $Message

            if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent)
            {

                Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message

            }

            if ($Database -in $SQLSMO.Databases.Name)
            {

                $Message = "Found '$Database' in '$($SQLConnection.ServerInstance)'."
                Write-Verbose $Message

                if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent)
                {

                    Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message

                }

            }
            else
            {

                $Message = "'$Database' does not exist in '$($SQLConnection.ServerInstance)'."
                Write-Error $Message
                Write-PWPSLog -Cmdlet $Cmdlet -Message $Message -Level Error

                return;

            }

        }

        #endregion Database Check

    }
    End
    {

        #region Return Database

        try
        {

            if ($Database)
            {

                $Message = "Returning $Database..."
                Write-Verbose $Message

                if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent)
                {

                    Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message

                }

                Write-Output $SQLSMO.Databases[$Database]

            }
            else
            {

                $Message = "Returning databases..."
                Write-Verbose $Message

                if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent)
                {

                    Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message

                }

                Write-Output $SQLSMO.Databases

            }

        }
        catch
        {

            $Message = "Error returning database. Aborting script..."
            Write-Error $Message
            Write-PWPSLog -Cmdlet $Cmdlet -Message $Message -Level Error

            return;

        }

        #endregion Return Database

    }

}

function Get-SQLDataType
{

    <#
            .SYNOPSIS
            Converts data types to SQL data types.
            .DESCRIPTION
            Takes an input data type, and returns the corresponding SQL data type.
            .PARAMETER DataType
            Input data type.
            .EXAMPLE
            This example will return the SQL data type for input type 'string'.
            Get-SQLDataType -DataType 'String'
            .EXAMPLE
            This example will return the SQL data type for input type 'int32'.
            Get-SQLDataType -DataType 'int32'
    #>

    [CmdletBinding()]

    Param
    (

        [Parameter(Mandatory=$true)]
        [string]$DataType

    )

    Begin
    {

        #region Startup

        $Cmdlet = 'Get-SQLDataType'

        #endregion Startup

    }
    Process
    {

        #region Convert Data Type

        $Message = "Beginning conversion."
        Write-Verbose $Message

        if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent)
        {

            Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message

        }

        $Message = "Input data type is '$DataType'."
        Write-Verbose $Message

        if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent)
        {

            Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message

        }

        switch ($DataType)
        {

            'Boolean'
            {$SQLDataType = [Microsoft.SqlServer.Management.Smo.SqlDataType]::Bit }
            'Byte[]'
            {$SQLDataType = [Microsoft.SqlServer.Management.Smo.SqlDataType]::VarBinary}
            'Byte'
            {$SQLDataType = [Microsoft.SqlServer.Management.Smo.SqlDataType]::VarBinary}
            'Datetime'
            {$SQLDataType = [Microsoft.SqlServer.Management.Smo.SqlDataType]::DateTime}
            #'Datetime'
            #{$SQLDataType = [Microsoft.SqlServer.Management.Smo.SqlDataType]::DateTime2}
            'Decimal'
            {$SQLDataType = [Microsoft.SqlServer.Management.Smo.SqlDataType]::Decimal}
            'Double'
            {$SQLDataType = [Microsoft.SqlServer.Management.Smo.SqlDataType]::Float}
            'Guid'
            {$SQLDataType = [Microsoft.SqlServer.Management.Smo.SqlDataType]::UniqueIdentifier}
            'Int16'
            {$SQLDataType = [Microsoft.SqlServer.Management.Smo.SqlDataType]::SmallInt}
            'Int32'
            {$SQLDataType = [Microsoft.SqlServer.Management.Smo.SqlDataType]::Int}
            #'Int32'
            #{$SQLDataType = [Microsoft.SqlServer.Management.Smo.SqlDataType]::Numeric}
            'Int64'
            {$SQLDataType = [Microsoft.SqlServer.Management.Smo.SqlDataType]::BigInt}
            #'Int64'
            #{$SQLDataType = [Microsoft.SqlServer.Management.Smo.SqlDataType]::Numeric}
            'UInt16'
            {$SQLDataType = [Microsoft.SqlServer.Management.Smo.SqlDataType]::SmallInt}
            'UInt32'
            {$SQLDataType = [Microsoft.SqlServer.Management.Smo.SqlDataType]::Int}
            'UInt64'
            {$SQLDataType = [Microsoft.SqlServer.Management.Smo.SqlDataType]::BigInt}
            'Single'
            {$SQLDataType = [Microsoft.SqlServer.Management.Smo.SqlDataType]::Decimal}
            default
            {$SQLDataType = [Microsoft.SqlServer.Management.Smo.SqlDataType]::VarChar}

        }

        $Message = "Output data type is '$SQLDataType'."
        Write-Verbose $Message

        if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent)
        {

            Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message

        }

        #endregion Convert Data Type

    }
    End
    {

        #region Return Data Type

        if ($SQLDataType)
        {

            $Message = "Data type conversion successful."
            Write-Verbose $Message

            if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent)
            {

                Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message

            }

            Write-Output -InputObject $SQLDataType

        }
        else
        {

            $Message = "Failed to covert data type."
            Write-Error $Message
            Write-PWPSLog -Cmdlet $Cmdlet -Message $Message -Level Error

            break;

        }

        #endregion Return Data Type

    }

}

function Get-SQLTable
{

    <#
            .SYNOPSIS
            Returns tables in a specified SQL database.
            .DESCRIPTION
            Uses a database specified in this cmdlet or in New-SQLConnection, and returns either all or a specified table from the target database.
            .PARAMETER SQLConnection
            SQL Server connection generated using New-SQLConnection.
            .PARAMETER Database
            Optional parameter to specify the name of database within the SQL Server instance. Only required if the -database paramater was not used when generating the SQL Server connection.
            .PARAMETER TableName
            Optional parameter to return a target table only. If not specified, all tables in the target database will be returned.
            .EXAMPLE
            This example will return the dms_audt table in the database specified during SQL Connection.
            $SQLConnection = New-SQLConnection -SQLServer 'SQLONE' -Database 'DATABASEONE' -WindowsAuthentication
            Get-SQLTable -SQLConnection $SQLConnection -TableName "dms_audt"
            .EXAMPLE
            This example will return all tables in the database specified during SQL Connection.
            $SQLConnection = New-SQLConnection -SQLServer 'SQLONE' -Database 'DATABASEONE' -WindowsAuthentication
            Get-SQLTable -SQLConnection $SQLConnection
            .EXAMPLE
            This example will return the dms_audt table in the database specified during table return.
            $SQLConnection = New-SQLConnection -SQLServer 'SQLONE' -WindowsAuthentication
            Get-SQLTable -SQLConnection $SQLConnection -Database 'DATABASEONE' -TableName "dms_audt"
            .EXAMPLE
            This example will return all tables in the database specified during table return.
            $SQLConnection = New-SQLConnection -SQLServer 'SQLONE' -WindowsAuthentication
            Get-SQLTable -SQLConnection $SQLConnection -Database 'DATABASEONE'
    #>

    [CmdletBinding()]

    Param
    (

        [Parameter(Mandatory=$true)]
        [ValidateNotNullOrEmpty()]
        [Microsoft.SqlServer.Management.Common.ConnectionManager]$SQLConnection,

        [Parameter(Mandatory=$false)]
        [ValidateNotNullOrEmpty()]
        [string]$Database,

        [Parameter(Mandatory=$false)]
        [ValidateNotNullOrEmpty()]
        [string]$TableName

    )

    Begin
    {

        #region Startup

        $Cmdlet = 'Get-SQLTable'

        #endregion Startup

        #region Parameter Checks

        if (!($SQLConnection.DatabaseName) -and !($Database))
        {

            $Message = "No database parameter specified, and no database specified in SQL Connection. Either specify the database, or create new SQL Connection using New-SQLConnection, making sure to specify the database parameter."
            Write-Error $Message
            Write-PWPSLog -Cmdlet $Cmdlet -Message $Message -Level Error

            return;

        }

        #endregion Parameter Checks

    }
    Process
    {

        #region Define SQL Objects

        ### Return DBO

        $Message = "Returning SQL Database object."
        Write-Verbose $Message
        if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent)
        {

            Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message

        }

        try
        {

            if ($Database)
            {

                $SQLDBO = Get-SQLDatabase -SQLConnection $SQLConnection -Database $Database

            }
            else
            {

                $Database = $SQLConnection.DatabaseName

                $SQLDBO = Get-SQLDatabase -SQLConnection $SQLConnection -Database $Database

            }


        }
        catch
        {

            $Message = "$($PSItem.Exception.Message)"
            Write-Error $Message
            Write-PWPSLog -Cmdlet $Cmdlet -Message $Message -Level Error

            $Message = "Error returning SQL Database object."
            Write-Error $Message
            Write-PWPSLog -Cmdlet $Cmdlet -Message $Message -Level Error

            return;

        }

        $Message = "Successfully returned SQL Database object."
        Write-Verbose $Message
        if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent)
        {

            Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message

        }

        #endregion Define SQL Objects

        #region Check Table

        if ($TableName)
        {

            $Message = "Checking if table '$TableName' exists in '$($SQLConnection.ServerInstance)\$Database'..."
            Write-Verbose $Message

            if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent)
            {

                Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message

            }

            if ($TableName -in $SQLDBO.Tables.Name)
            {

                $Message = "Found table '$TableName' in database '$Database'."
                Write-Verbose $Message
                if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent)
                {

                    Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message

                }

            }
            else
            {

                $Message = "Could not find table '$TableName' in database '$Database'."
                Write-Error $Message
                Write-PWPSLog -Cmdlet $Cmdlet -Message $Message -Level Error

                return;

            }

        }

        #endregion Check table

    }
    End
    {

        #region Return Table

        if ($TableName)
        {

            try
            {

                $Message = "Returning table '$TableName'."
                Write-Verbose $Message
                if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent)
                {

                    Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message

                }

                Write-Output $SQLDBO.Tables[$TableName]

            }
            catch
            {

                $Message = "Error returning table."
                Write-Error $Message
                Write-PWPSLog -Cmdlet $Cmdlet -Message $Message -Level Error

                return;

            }

        }
        else
        {

            try
            {

                $Message = "Returning tables in '$Database'."
                Write-Verbose $Message

                if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent)
                {

                    Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message

                }

                Write-Output $SQLDBO.Tables

            }
            catch
            {

                $Message = "Error returning tables."
                Write-Error $Message
                Write-PWPSLog -Cmdlet $Cmdlet -Message $Message -Level Error

                return;

            }

        }

        #endregion Return Table

    }

}

function New-SQLConnection
{

    <#
            .SYNOPSIS
            Opens a connection to a specified SQL server.
            .DESCRIPTION
            Uses the assemblies delivered with the SQLServer module to open a connection to the specified SQL Server.
            .PARAMETER SQLServer
            The name of the SQL Server. If connecting to a named instance, use the format SQLServerName\InstanceName.
            .PARAMETER Database
            The name of database within the SQL Server instance to which you would like to connect.
            .PARAMETER Username
            Username to connect with if using SQL Server Authentication.
            .PARAMETER Password
            Password to connect with if using SQL Server Authentication.
            .PARAMETER WindowsAuthentication
            Activate this switch to connect using Windows Authentication.
            .EXAMPLE
            This example will open a general connection to the SQL Server 'SQLONE' using Windows authentication.
            New-SQLConnection -SQLServer 'SQLONE' -WindowsAuthentication
            .EXAMPLE
            This example will open a connection to the database 'DATABASEONE' in the SQL Server 'SQLONE' using Windows authentication.
            New-SQLConnection -SQLServer 'SQLONE' -Database 'DATABASEONE' -WindowsAuthentication
            .EXAMPLE
            This example will open a connection to the database 'DATABASEONE' in the SQL Server 'SQLONE' using SQL Server authentication.
            New-SQLConnection -SQLServer 'SQLONE' -Database 'DATABASEONE' -Username 'sa' -Password 'sa'
 
    #>

    [CmdletBinding()]

    Param
    (

        [Parameter(Mandatory=$true)]
        [ValidateNotNullOrEmpty()]
        [string]$SQLServer,

        [Parameter(Mandatory=$false)]
        [ValidateNotNullOrEmpty()]
        [string]$Database,

        [Parameter(Mandatory=$false)]
        [string]$Username,

        [Parameter(Mandatory=$false)]
        [string]$Password,

        [Parameter(Mandatory=$false)]
        [switch]$WindowsAuthentication

    )

    Begin
    {

        #region Startup

        $Cmdlet = 'New-SQLConnection'

        #endregion Startup

        #region Requirements

        ### Import SqlServer module to load required assemblies
        # Assemblies can be loaded manually, instructions here: https://docs.microsoft.com/en-us/sql/powershell/load-the-smo-assemblies-in-windows-powershell?view=sql-server-2017

        $Message = "Checking for SqlServer module..."
        Write-Verbose $Message
        if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent)
        {

            Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message

        }

        try
        {

            if (!(Get-Module -Name SqlServer))
            {

                $Message = "Importing SqlServer module..."
                Write-Verbose $Message
                if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent)
                {

                    Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message

                }

                Import-Module -Name SqlServer -ErrorAction Stop

            }

        }
        catch
        {

            $Message = "$($PSItem.Exception.Message)"
            Write-Error $Message
            Write-PWPSLog -Cmdlet $Cmdlet -Message $Message -Level Error

            $Message = "Error importing SqlServer Module."
            Write-Error $Message
            Write-PWPSLog -Cmdlet $Cmdlet -Message $Message -Level Error

            break;

        }

        #endregion Requirements

        #region Parameter Checks

        if ($WindowsAuthentication)
        {

            $Message = "Authentication mode: Windows."
            Write-Verbose $Message

            if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent)
            {

                Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message

            }

        }
        else
        {

            $Message = "Authentication mode: SQL Server."
            Write-Verbose $Message
            if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent)
            {

                Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message

            }

            ### No password provided

            if (!($Password))
            {

                $Message = "No password provided for SQL Server authentication."
                Write-Error $Message
                Write-PWPSLog -Cmdlet $Cmdlet -Message $Message -Level Error

                break;

            }

            ### No username provided

            if (!($Username))
            {

                $Message = "No username provided for SQL Server authentication."
                Write-Error $Message
                Write-PWPSLog -Cmdlet $Cmdlet -Message $Message -Level Error

                break;

            }

        }

        #endregion Parameter Checks

    }
    Process
    {

        #region Establish Connection

        if ($WindowsAuthentication)
        {

            $Message = "Establishing connection to '$SQLServer' using Windows authentication."
            Write-Verbose $Message
            if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent)
            {

                Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message

            }

            try
            {

                $SQLServerConnection = New-Object Microsoft.SqlServer.Management.Common.ServerConnection ($SQLServer) -ErrorAction Stop

            }
            catch
            {

                $Message = "$($PSItem.Exception.Message)"
                Write-Error $Message
                Write-PWPSLog -Cmdlet $Cmdlet -Message $Message -Level Error

                $Message = "Error establishing connection to '$SQLServer'."
                Write-Error $Message
                Write-PWPSLog -Cmdlet $Cmdlet -Message $Message -Level Error

                break;

            }

        }
        else
        {

            $Message = "Establishing connection to '$SQLServer' as '$Username' using SQL Server authentication."
            Write-Verbose $Message
            if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent)
            {

                Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message

            }

            try
            {

                $SQLServerConnection = New-Object Microsoft.SqlServer.Management.Common.ServerConnection ($SQLServer, $Username, $Password) -ErrorAction Stop

            }
            catch
            {

                $Message = "$($PSItem.Exception.Message)"
                Write-Error $Message
                Write-PWPSLog -Cmdlet $Cmdlet -Message $Message -Level Error

                $Message = "Error establishing connection to '$SQLServer' as '$Username'."
                Write-Error $Message
                Write-PWPSLog -Cmdlet $Cmdlet -Message $Message -Level Error

                break;

            }

        }

        $Message = "Successfully established connection to '$SQLServer'."
        Write-Verbose $Message
        if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent)
        {

            Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message

        }

        #endregion Establish Connection

        #region Target Database

        if ($Database)
        {

            $Message = "Setting target database to '$Database'."
            Write-Verbose $Message
            if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent)
            {

                Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message

            }

            try
            {

                $SQLServerConnection.DatabaseName = $Database

            }
            catch
            {

                $Message = "Error setting target database."
                Write-Error $Message
                Write-PWPSLog -Cmdlet $Cmdlet -Message $Message -Level Error

                break;

            }

        }

        #endregion Target Database

        #region Open Connection

        $Message = "Opening connection..."
        Write-Verbose $Message
        if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent)
        {

            Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message

        }

        try
        {

            $SQLServerConnection.Connect()

        }
        catch
        {

            $Message = "Error opening connection. Check login credentials and inputs. Aborting script..."
            Write-Error $Message
            Write-PWPSLog -Cmdlet $Cmdlet -Message $Message -Level Error

            break;

        }

        #endregion Open Connection

    }
    End
    {

        #region Return Connection

        if ($($SQLServerConnection.IsOpen) -eq $True)
        {

            $Message = "Successfully opened connection to '$SQLServer'."
            Write-Verbose $Message
            if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent)
            {

                Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message

            }

            Write-Output -InputObject $SQLServerConnection

        }
        else
        {

            $Message = "Failed to open SQL connection."
            Write-Error $Message
            Write-PWPSLog -Cmdlet $Cmdlet -Message $Message -Level Error

            break;

        }

        #endregion Return Connection

    }

}

function New-SQLTable
{

    <#
            .SYNOPSIS
            Creates a table in a specified database.
            .DESCRIPTION
            Uses a specified datatable for the schema template and table namme, then creates an empty table with the same schema in a target database.
            .PARAMETER SQLConnection
            SQL Server connection generated using New-SQLConnection.
            .PARAMETER Database
            Optional parameter to specify the name of database within the SQL Server instance. Only required if the -database paramater was not used when generating the SQL Server connection.
            .PARAMETER Datatable
            Datatable to use as the template schema and table name.
            .EXAMPLE
            This example will create an empty replica table for dms_audt in the database specified during SQL Connection.
            $SQLConnection = New-SQLConnection -SQLServer 'SQLONE' -Database 'DATABASEONE' -WindowsAuthentication
            $DataTable = Select-PWSQLDataTable -SQLSelectStatement "SELECT * FROM dms_audt"
            $DataTable.TableName = "dms_audt"
            New-SQLTable -SQLConnection $SQLConnection -DataTable $DataTable
            .EXAMPLE
            This example will create an empty replica table for dms_audt in the database specified during table creation.
            $SQLConnection = New-SQLConnection -SQLServer 'SQLONE' -WindowsAuthentication
            $DataTable = Select-PWSQLDataTable -SQLSelectStatement "SELECT * FROM dms_audt"
            $DataTable.TableName = "dms_audt"
            New-SQLTable -SQLConnection $SQLConnection -Database 'DATABASEONE' -DataTable $DataTable
    #>

    [CmdletBinding()]

    Param
    (

        [Parameter(Mandatory=$true)]
        [ValidateNotNullOrEmpty()]
        [Microsoft.SqlServer.Management.Common.ConnectionManager]$SQLConnection,

        [Parameter(Mandatory=$false)]
        [ValidateNotNullOrEmpty()]
        [string]$Database,

        [Parameter(Mandatory=$true)]
        [ValidateNotNullOrEmpty()]
        [System.Data.DataTable]$DataTable

    )

    Begin
    {

        #region Startup

        $Cmdlet = 'New-SQLTable'

        #endregion Startup

        #region Parameter Checks

        if (!($SQLConnection.DatabaseName) -and !($Database))
        {

            $Message = "No database parameter specified, and no database specified in SQL Connection. Either specify the database, or create new SQL Connection using New-SQLConnection, making sure to specify the database parameter."
            Write-Error $Message
            Write-PWPSLog -Cmdlet $Cmdlet -Message $Message -Level Error

            return;

        }

        #endregion Parameter Checks

    }
    Process
    {

        #region Define SQL Objects

        ### Return DBO

        $Message = "Returning SQL Database object."
        Write-Verbose $Message
        if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent)
        {

            Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message

        }

        try
        {

            if ($Database)
            {

                $SQLDBO = Get-SQLDatabase -SQLConnection $SQLConnection -Database $Database

            }
            else
            {

                $SQLDBO = Get-SQLDatabase -SQLConnection $SQLConnection -Database $SQLConnection.DatabaseName

            }

        }
        catch
        {

            $Message = "$($PSItem.Exception.Message)"
            Write-Error $Message
            Write-PWPSLog -Cmdlet $Cmdlet -Message $Message -Level Error

            $Message = "Error returning SQL Database object."
            Write-Error $Message
            Write-PWPSLog -Cmdlet $Cmdlet -Message $Message -Level Error

            return;

        }

        $Message = "Successfully returned $($SQLDBO.Name)."
        Write-Verbose $Message
        if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent)
        {

            Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message

        }

        #endregion Define SQL Objects

        #region Define Table

        ### Define table object

        $Message = "Defining SQL Table object..."
        Write-Verbose $Message
        if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent)
        {

            Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message

        }

        try
        {

            $SQLTable = New-Object Microsoft.SqlServer.Management.Smo.Table ($SQLDBO, $DataTable.TableName) -ErrorAction Stop

        }
        catch
        {

            $Message = "$($PSItem.Exception.Message)"
            Write-Error $Message
            Write-PWPSLog -Cmdlet $Cmdlet -Message $Message -Level Error

            $Message = "Error defining SQL Table object."
            Write-Error $Message
            Write-PWPSLog -Cmdlet $Cmdlet -Message $Message -Level Error

            return;

        }

        $Message = "Successfully defined table '$($SQLTable.Name)'."
        Write-Verbose $Message
        if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent)
        {

            Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message

        }

        ### Add columns to table

        $Message = "Preparing to add $($DataTable.Columns.Count) columns to '$($SQLTable.Name)'..."
        Write-Verbose $Message
        if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent)
        {

            Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message

        }

        foreach ($Column in $Datatable.Columns)
        {

            ### Convert data type

            try
            {

                $Message = "Converting data type for column '$($Column.ColumnName)'."
                Write-Verbose $Message
                if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent)
                {

                    Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message

                }

                $SQLDatabaseType = Get-SQLDataType -DataType $Column.DataType.Name

                if ($SQLDatabaseType -eq 'VarBinary' -or $SQLDatabaseType -eq 'VarChar')
                {

                    $SQLDataType = New-Object Microsoft.SqlServer.Management.Smo.DataType ("$($SQLDatabaseType)Max") -ErrorAction Stop

                }
                else
                {

                    $SQLDataType = New-Object Microsoft.SqlServer.Management.Smo.DataType ($SQLDatabaseType) -ErrorAction Stop

                }



            }
            catch
            {

                $Message = "$($PSItem.Exception.Message)"
                Write-Error $Message
                Write-PWPSLog -Cmdlet $Cmdlet -Message $Message -Level Error

                $Message = "Error converting data type."
                Write-Error $Message
                Write-PWPSLog -Cmdlet $Cmdlet -Message $Message -Level Error

                return;

            }

            ### Define column

            try
            {

                $Message = "Defining column object '$($Column.ColumnName)'."
                Write-Verbose $Message
                if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent)
                {

                    Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message

                }

                $SQLColumn = New-Object Microsoft.SqlServer.Management.Smo.Column ($SQLTable, $Column.ColumnName, $SQLDataType) -ErrorAction Stop
                $SQLColumn.Nullable = $Column.AllowDBNull

            }
            catch
            {

                $Message = "$($PSItem.Exception.Message)"
                Write-Error $Message
                Write-PWPSLog -Cmdlet $Cmdlet -Message $Message -Level Error

                $Message = "Error defining column object."
                Write-Error $Message
                Write-PWPSLog -Cmdlet $Cmdlet -Message $Message -Level Error

                return;

            }


            ### Add column

            try
            {

                $Message = "Adding column object '$($Column.ColumnName)' to table object '$($SQLTable.Name)'."
                Write-Verbose $Message
                if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent)
                {

                    Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message

                }

                $SQLTable.Columns.Add($SQLColumn)

            }
            catch
            {

                $Message = "$($PSItem.Exception.Message)"
                Write-Error $Message
                Write-PWPSLog -Cmdlet $Cmdlet -Message $Message -Level Error

                $Message = "Error adding column object."
                Write-Error $Message
                Write-PWPSLog -Cmdlet $Cmdlet -Message $Message -Level Error

                return;

            }


        }

        #endregion Define Table

    }
    End
    {

        #region Create Table

        try
        {

            $Message = "Adding table object '$($SQLTable.Name)' to database '$($SQLDBO.Name)'."
            Write-Verbose $Message
            if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent)
            {

                Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message

            }

            $SQLTable.Create()

        }
        catch
        {

            $Message = "$($PSItem.Exception.Message)"
            Write-Error $Message
            Write-PWPSLog -Cmdlet $Cmdlet -Message $Message -Level Error

            $Message = "Error adding table object."
            Write-Error $Message
            Write-PWPSLog -Cmdlet $Cmdlet -Message $Message -Level Error

            return;

        }

        #endregion Create Table

    }

}

function Truncate-SQLTable
{

    <#
            .SYNOPSIS
            Truncates a specified SQL table.
            .DESCRIPTION
            Targets a database specified in New-SQLConnection, and truncates the specified table.
            .PARAMETER SQLConnection
            SQL Server connection generated using New-SQLConnection. Connection must created specifying the -database parameter.
            .PARAMETER TableName
            Name of the SQL table to truncate.
            .EXAMPLE
            This example will truncate the table "dms_audt" in the database specified during SQL connection.
            $SQLConnection = New-SQLConnection -SQLServer 'SQLONE' -Database 'DATABASEONE' -WindowsAuthentication
            Truncate-SQLTable -SQLConnection $SQLConnection -TableName "dms_audt"
    #>

    [CmdletBinding()]

    Param
    (

        [Parameter(Mandatory=$true)]
        [ValidateNotNullOrEmpty()]
        [Microsoft.SqlServer.Management.Common.ConnectionManager]$SQLConnection,

        [Parameter(Mandatory=$true)]
        [ValidateNotNullOrEmpty()]
        [string]$TableName

    )

    Begin
    {

        #region Startup

        $Cmdlet = 'Truncate-SQLTable'

        #endregion Startup

        #region Parameter Checks

        ### Check database is specified in SQL Connection object

        if (!($SQLConnection.DatabaseName))
        {

            $Message = "No database specified in SQL Connection. Create new SQL Connection using New-SQLConnection, making sure to specify the database parameter."
            Write-Error $Message
            Write-PWPSLog -Cmdlet $Cmdlet -Message $Message -Level Error

            return;

        }

        #endregion Parameter Checks

    }
    Process
    {

        #region Define SQL Objects

        ### Return SQL Table

        $Message = "Returning table '$TableName'."
        Write-Verbose $Message
        if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent)
        {

            Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message

        }

        try
        {


            $SQLTable = Get-SQLTable -SQLConnection $SQLConnection -TableName $TableName


        }
        catch
        {

            $Message = "$($PSItem.Exception.Message)"
            Write-Error $Message
            Write-PWPSLog -Cmdlet $Cmdlet -Message $Message -Level Error

            $Message = "Error returning table object."
            Write-Error $Message
            Write-PWPSLog -Cmdlet $Cmdlet -Message $Message -Level Error

            return;

        }

        $Message = "Successfully returned table object."
        Write-Verbose $Message
        if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent)
        {

            Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message

        }

        #endregion Return SQL Table

    }
    End
    {

        #region Truncate table

        $Message = "Performing truncate on table '$TableName'..."
        Write-Verbose $Message
        if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent)
        {

            Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message

        }

        try
        {

            $SQLTable.TruncateData()

        }
        catch
        {

            $Message = "$($PSItem.Exception.Message)"
            Write-Error $Message
            Write-PWPSLog -Cmdlet $Cmdlet -Message $Message -Level Error

            $Message = "Error truncating table."
            Write-Error $Message
            Write-PWPSLog -Cmdlet $Cmdlet -Message $Message -Level Error

            return;

        }

        $Message = "Successfully truncated table."
        Write-Verbose $Message
        if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent)
        {

            Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message

        }

        #endregion Truncate

    }

}

#endregion SQL

#region Reporting

function Get-PWAuditTrailRecordsFromPreviousDays
{

    <#
            .SYNOPSIS
            Returns audit trail records from previous days.
            .DESCRIPTION
            Uses a SQL query to return audit trail records from previous days.
            .PARAMETER DaysAgo
            Integer value to specify the desired number of days ago to return records for. A value of '1' is equal to yesterday.
            .PARAMETER IncludeIntermediateDays
            Switch to include records from intermediate results. If days ago is '5' and this switch is activated, audit trail records from 5 days ago up until now will be returned, as opposed to just the audit trail records from 5 days ago if this switch is not activated.
            .PARAMETER AddDatasourceInformation
            Switch to add datasource information (DatasourceString = 'servername:datasourcename';DatasourceName = 'datasourcename'; ServerName = 'servername') to the returned DataTable. Useful when reporting against multiple servers and datasources.
            .EXAMPLE
            This example will return audit trail records from 10 days ago.
            Get-PWAuditTrailRecordsFromPreviousDays -DaysAgo 10 -Verbose
            .EXAMPLE
            This example will return audit trail records from 10 days ago, and add datasource information to the output DataTable.
            Get-PWAuditTrailRecordsFromPreviousDays -DaysAgo 10 -AddDatasourceInformation -Verbose
            .EXAMPLE
            This example will return audit trail records from the last 10 days up until this moment.
            Get-PWAuditTrailRecordsFromPreviousDays -DaysAgo 10 -IncludeIntermediateDays -Verbose
 
    #>

    [CmdletBinding()]

    Param
    (

        [Parameter(Mandatory=$true)]
        [ValidateNotNullOrEmpty()]
        [int]$DaysAgo,

        [Parameter(Mandatory=$false)]
        [switch]$IncludeIntermediateDays,

        [Parameter(Mandatory=$false)]
        [switch]$AddDatasourceInformation

    )

    Begin
    {

        #region Startup

        $Cmdlet = 'Get-PWAuditTrailRecordsFromPreviousDays'

        #endregion Startup

        #region Checks

        # Check ProjectWise connection

        $Message = "Checking for ProjectWise connection..."
        Write-Verbose $Message

        if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent)
        {

            Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message

        }


        if(!(Get-PWCurrentDatasource))
        {

            $Message = "Get-PWAuditTrailRacordsFromPreviousDays requires an active PW connection. Please open a connection using New-PWLogin."
            Write-Error $Message
            Write-PWPSLog -Cmdlet $Cmdlet -Level Error -Message $Message
            break;

        }
        else
        {

            $Datasource = Get-PWCurrentDatasource
            $DatasourceName = $Datasource.Split(':')[1]
            $ServerName = $Datasource.Split(':')[0]

        }

        $Message = "Connected to '$Datasource'."
        Write-Verbose $Message

        if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent)
        {

            Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message

        }

        if ($IncludeIntermediateDays)
        {

            $Message = "Include intermediate days switch activated!"
            Write-Verbose $Message

            if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent)
            {

                Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message

            }

        }
        else
        {

            $Message = "Include intermediate days switch not activated! Only records from the target day will be returned."
            Write-Verbose $Message

            if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent)
            {

                Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message

            }

        }

        #endregion Checks

    }
    Process
    {

        #region Return Records

        $Message = "Returning audit trail records..."
        Write-Verbose $Message

        if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent)
        {

            Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message

        }

        if ($IncludeIntermediateDays)
        {

            $SQLQuery = "SELECT * from dms_audt WHERE o_acttime >= dateadd(day,datediff(day,$DaysAgo,GETDATE()),0) AND o_acttime < dateadd(day,datediff(day,0,GETDATE()),0)"

        }
        else
        {

            $DaysAgoMinusOne = ($DaysAgo -1)

            $SQLQuery = "SELECT * from dms_audt WHERE o_acttime >= dateadd(day,datediff(day,$DaysAgo,GETDATE()),0) AND o_acttime < dateadd(day,datediff(day,$DaysAgoMinusOne,GETDATE()),0)"

        }

        try
        {

            $AuditTrailRecords = Select-PWSQL -SQLSelectStatement $SQLQuery -Verbose
            $AuditTrailRecordsCount = ($AuditTrailRecords | Measure-Object).Count

        }
        catch
        {

            $Message = "Failed to return audit trail records!"
            Write-Error $Message
            Write-PWPSLog -Cmdlet $Cmdlet -Level Error -Message $Message
            break;

        }

        $Message = "Returned $AuditTrailRecordsCount audit trail records."
        Write-Verbose $Message

        if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent)
        {

            Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message

        }

        #endregion Return Records

        #region Add Properties

        if ($AddDatasourceInformation)
        {

            $Message = "Adding properties to audit trail records..."
            Write-Verbose $Message

            if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent)
            {

                Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message

            }

            if ($AuditTrailRecordsCount -gt 0)
            {

                try
                {

                    # Properties

                    $AuditTrailRecords | Add-Member -MemberType NoteProperty -Name DatasourceString -Value $Datasource
                    $AuditTrailRecords | Add-Member -MemberType NoteProperty -Name DatasourceName -Value $DatasourceName
                    $AuditTrailRecords | Add-Member -MemberType NoteProperty -Name ServerName -Value $ServerName

                    $Message = "Finished adding properties to audit trail records."
                    Write-Verbose $Message

                    if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent)
                    {

                        Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message

                    }

                }
                catch
                {

                    $Message = "Failed to add properties to audit trail records!"
                    Write-Error $Message
                    Write-PWPSLog -Cmdlet $Cmdlet -Level Error -Message $Message

                    break;

                }

            }
            elseif ($AuditTrailRecordsCount -eq 0)
            {

                $Message = "No audit trail records returned."
                Write-Warning $Message
                Write-PWPSLog -Cmdlet $Cmdlet -Level Warn -Message $Message

            }
            else
            {

                Write-PWPSLog -Cmdlet $Cmdlet -Level Error -Message "This should never happen! Something has gone wrong..."

                break;

            }

        }

        #endregion Add Properties

    }
    End
    {

        #region Write Output

        $Message = "Writing output..."
        Write-Verbose $Message

        if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent)
        {

            Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message

        }

        if ($AuditTrailRecordsCount -gt 0)
        {

            Write-Output $AuditTrailRecords

        }

        #endregion Write Output

    }

}

FUNCTION Get-PWPerformanceReportDataV1 {
    <#
            .SYNOPSIS
            Returns a dataset containing ProjectWise performance metrics.
            .DESCRIPTION
            Performs and times the execution of various tasks in ProjectWise, the results of which are output to a dataset containing four tables. All test documents/folders are cleaned up after the test has completed.
            .PARAMETER Connection
            Used to specify the connection type for later sorting - e.g. "Home Wifi", "Bentley LAN", "4G Dongle" etc.
            .PARAMETER Location
            Used to specify the location from where the test is being run - e.g. "Melbourne, Victoria" etc.
            .PARAMETER ConnectingViaCache
            Used to specify whether or not the client is connecting via a caching server - accepts "True" or "False".
            .PARAMETER Datasource
            Used to specify the datasource to run the test - server:datasource.
            .PARAMETER ProjectWiseUserName
            Used to specify the username for the ProjectWise account running the test.
            .PARAMETER ProjectWisePassword
            Used to specify the password for the ProjectWise account running the test.
            .PARAMETER TestParentPath
            Used to specify a parent path under which all test operations will be completed. If not specified, test operations are completed at root level in the folder tree. All test documents/folders are cleaned up after the test has completed.
            .PARAMETER TestFileSizeInMB
            Used to specify the size of the files in MB used for testing, default value is 10MB.
            .PARAMETER EnvironmentName
            Used to specify the name of the environment to create for testing - the environment will have a single attribute used for testing attribute updates. Default value is "PWPerfReport"
            .PARAMETER EnvironmentTableName
            Used to specify the table name for the test environment. Default value is "PWPerfReport"
            .PARAMETER EnvironmentColumnName
            Used to specify the column/attribure name for the test environment table. Default value is "Test_Attribute"
            .PARAMETER OutputType
            Used to specify output data type as either PSObject or DataTable. Default value is PSObject
            .EXAMPLE
            This example will return a performance metric dataset for a home connection, not using a caching server, and output a PSObject.
            $PWPerformanceReportVariables = @{
            Connection = "Home Wifi";
            Location = "Melbourne, Victoria";
            ConnectingViaCache = "False"
            Datasource = "decide-pwce-aus.bentley.com:decide-pwce-aus-010"
            ProjectWiseUserName = "PWPerfReportUser"
            ProjectWisePassword = (Get-SecureStringFromEncryptedFile -FileName "C:\temp\ProjectWisePassword.txt")
            OutputType = PSObject
            }
 
            $PerformanceData = Get-PWPerformanceReportData @PWPerformanceReportVariables -Verbose
            .EXAMPLE
            This example will return a performance metric dataset for an office connection, using a caching server, and output a DataTable.
            $PWPerformanceReportVariables = @{
            Connection = "Bentley Office LAN";
            Location = "Melbourne, Victoria";
            ConnectingViaCache = "True"
            Datasource = "decide-pwce-aus.bentley.com:decide-pwce-aus-010"
            ProjectWiseUserName = "PWPerfReportUser"
            ProjectWisePassword = (Get-SecureStringFromEncryptedFile -FileName "C:\temp\ProjectWisePassword.txt")
            OutputType = DataTable
            }
 
            $PerformanceData = Get-PWPerformanceReportData @PWPerformanceReportVariables -Verbose
    #>

    [CmdletBinding()]
    param (

        [Parameter(Mandatory=$false)]
        [ValidateNotNullOrEmpty()]
        [string]$Connection = "Unknown",

        [Parameter(Mandatory=$false)]
        [ValidateNotNullOrEmpty()]
        [string]$Location = "Unknown",

        [Parameter(Mandatory=$false)]
        [ValidateSet("True", "False")]
        [ValidateNotNullOrEmpty()]
        [string]$ConnectingViaCache,

        [Parameter(Mandatory=$true)]
        [ValidateNotNullOrEmpty()]
        [string]$Datasource,

        [Parameter(Mandatory=$true,
            ParameterSetName = 'PWLogin')]
        [ValidateNotNullOrEmpty()]
        [string]$ProjectWiseUserName,

        [Parameter(Mandatory=$true,
            ParameterSetName = 'PWLogin')]
        [ValidateNotNullOrEmpty()]
        [System.Security.SecureString]$ProjectWisePassword,

        [Parameter(Mandatory=$true,
            ParameterSetName = 'BentleyIMS')]
        [switch]$UseBentleyIMS,

        [Parameter(Mandatory=$false)]
        [ValidateNotNullOrEmpty()]
        [string]$TestParentPath,

        [Parameter(Mandatory=$false)]
        [ValidateNotNullOrEmpty()]
        [int]$TestFileSizeInMB = 10,

        [Parameter(Mandatory=$false)]
        [ValidateNotNullOrEmpty()]
        [string]$EnvironmentName = "PWPerfReport",

        [Parameter(Mandatory=$false)]
        [ValidateNotNullOrEmpty()]
        [string]$EnvironmentTableName = "PWPerfReport",

        [Parameter(Mandatory=$false)]
        [ValidateNotNullOrEmpty()]
        [string]$EnvironmentColumnName = "Test_Attribute",

        [Parameter(Mandatory=$false)]
        [ValidateSet("PSObject", "DataTable")]
        [ValidateNotNullOrEmpty()]
        [string]$OutputType = "PSObject"
    ) # end param...

    BEGIN {

        #region Startup

        $Cmdlet = 'Get-PWPerformanceReportData'
        $MachineName = $env:COMPUTERNAME

        #endregion Startup

        #region Checks

        if ((Get-PWCurrentDatasource)) {
            Write-PWPSLog -Message "ProjectWise connection already open. Logout and rerun script!" -Level Error -Cmdlet $Cmdlet
            break
        }

        if (!($ConnectingViaCache)) {
            $ConnectingViaCache = "Unknown"
        }

        #endregion Checks

    } # end BEGIN...
    
    PROCESS {

        #region Create Arrays

        if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent) { Write-PWPSLog -Message "Creating arrays..." -Level Info -Cmdlet $Cmdlet }

        $MetricsGeneral = @{}
        $MetricsUser = @{}
        $MetricsDocument = @{}
        $MetricsFolder = @{}

        if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent) { Write-PWPSLog -Message "Generating test GUID..." -Level Info -Cmdlet $Cmdlet }

        $TestGUID = New-Guid

        $MetricsGeneral.Add("TestGUID", $TestGUID)
        $MetricsUser.Add("TestGUID", $TestGUID)
        $MetricsDocument.Add("TestGUID", $TestGUID)
        $MetricsFolder.Add("TestGUID", $TestGUID)

        #endregion Create Arrays

        #region Add General Properties

        if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent) { Write-PWPSLog -Message "Adding general properties..." -Level Info -Cmdlet $Cmdlet }

        $MetricsGeneral.Add("MachineName", $MachineName) # Machine name
        $MetricsGeneral.Add("Location", $Location) # Source location
        $MetricsGeneral.Add("Datasource", $Datasource) # Datasource
        $MetricsGeneral.Add("ConnectionType", $Connection) # Connection type
        $MetricsGeneral.Add("ConnectingViaCache", $ConnectingViaCache) # Connecting via cache check
        $MetricsGeneral.Add("TestFileSize", $TestFileSizeInMB) # Test file size
        $MetricsGeneral.Add("TimeZone", (Get-TimeZone).ID) # Time zone
        $MetricsGeneral.Add("ReportStartLocalTime", (Get-Date)) # Test start local time
        $MetricsGeneral.Add("ReportStartUniversalTime",$MetricsGeneral.ReportStartLocalTime.ToUniversalTime()) # Test start UTC

        if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent) {

            Write-PWPSLog -Message "Test GUID: $TestGUID" -Level Info -Cmdlet $Cmdlet
            Write-PWPSLog -Message "MachineName: $MachineName" -Level Info -Cmdlet $Cmdlet
            Write-PWPSLog -Message "Location: $Location" -Level Info -Cmdlet $Cmdlet
            Write-PWPSLog -Message "ConnectionType: $Connection" -Level Info -Cmdlet $Cmdlet
            Write-PWPSLog -Message "ConnectingViaCache: $ConnectingViaCache" -Level Info -Cmdlet $Cmdlet
            Write-PWPSLog -Message "TestFileSize: $TestFileSizeInMB MB" -Level Info -Cmdlet $Cmdlet
            Write-PWPSLog -Message "TimeZone: $($MetricsGeneral.TimeZone)" -Level Info -Cmdlet $Cmdlet
            Write-PWPSLog -Message "ReportStartLocalTime: $($MetricsGeneral.ReportStartLocalTime)" -Level Info -Cmdlet $Cmdlet
            Write-PWPSLog -Message "ReportStartUniversalTime: $($MetricsGeneral.ReportStartUniversalTime)" -Level Info -Cmdlet $Cmdlet

        }

        #endregion Add General Properties

        #region Add User Login

        if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent) { Write-PWPSLog -Message "Logging into '$Datasource' as '$ProjectWiseUserName'..." -Level Info -Cmdlet $Cmdlet }
        
        if($UseBentleyIMS) { 
            $MetricsUser.Add("Login", (Measure-Command {New-PWLogin -DatasourceName $Datasource -BentleyIMS -Verbose}).TotalSeconds) # ProjectWise login
        } else { 
            $MetricsUser.Add("Login", (Measure-Command {New-PWLogin -DatasourceName $Datasource -UserName $ProjectWiseUserName -Password $ProjectWisePassword -Verbose}).TotalSeconds) # ProjectWise login
        }

        $MetricsUser.Add("CurrentlyConnectedUsers", (Get-PWUsersByMatch | Where-Object {$PSItem.IsConnected -eq "True"}).Count) # Currently connected users

        if (!(Get-PWCurrentDatasource))
        {
            Write-PWPSLog -Message "Failed to login. Aborting!" -Level Error -Cmdlet $Cmdlet
            break
        }

        #endregion Add User Login

        #region Build Test Environment

        ### Local

        $WorkingDirectory = Get-PWUserWorkingDirectory -UserId ((Get-PWCurrentUser).ID) -Verbose

        $WorkingFileNameOne = "$(Get-RandomString -Length 10).dgn"
        $WorkingFileNameTwo = "$(Get-RandomString -Length 10).dgn"

        $TempWorkingFileOne = New-Object System.IO.FileStream "$WorkingDirectory\$WorkingFileNameOne", Create, ReadWrite
        $TempWorkingFileOne.SetLength($TestFileSizeInMB * 1048576)
        $TempWorkingFileOne.Close()

        $TempWorkingFileTwo = New-Object System.IO.FileStream "$WorkingDirectory\$WorkingFileNameTwo", Create, ReadWrite
        $TempWorkingFileTwo.SetLength($TestFileSizeInMB * 1048576)
        $TempWorkingFileTwo.Close()

        if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent) {

            Write-PWPSLog -Message "WorkingDirectory: $WorkingDirectory" -Level Info -Cmdlet $Cmdlet
            Write-PWPSLog -Message "WorkingFileOne: $WorkingFileNameOne" -Level Info -Cmdlet $Cmdlet
            Write-PWPSLog -Message "WorkingFileTwo: $WorkingFileNameTwo" -Level Info -Cmdlet $Cmdlet

        }

        ### ProjectWise

        $EnvironmentDetailsQuery = "SELECT e.o_envname, t.o_tabname, c.column_name from dms_env e LEFT JOIN dms_tabs t on e.o_tabno = t.o_tabno LEFT JOIN INFORMATION_SCHEMA.COLUMNS c on t.o_tabname = c.TABLE_NAME WHERE e.o_envname = '$EnvironmentName' AND t.o_tabname = '$EnvironmentTableName' and c.column_name = '$EnvironmentColumnName'"
        $EnvironmentDetails = Select-PWSQL -SQLSelectStatement $EnvironmentDetailsQuery

        if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent) { Write-PWPSLog -Message "Checking for environment :$EnvironmentName'..." -Level Info -Cmdlet $Cmdlet }

        if (($EnvironmentName -ne $EnvironmentDetails.o_envname) -or ($EnvironmentTableName -ne $EnvironmentDetails.o_tabname) -or ($EnvironmentColumnName -ne $EnvironmentDetails.column_name)) {
            Write-PWPSLog -Message "Environment not found! Creating environment..." -Level Warn -Cmdlet $Cmdlet

            $NewPWEnvironment = @{
                EnvironmentName = $EnvironmentName
                TableName = $EnvironmentTableName
                ColumnName = $EnvironmentColumnName
            }

            New-PWEnvironment @NewPWEnvironment -Verbose | Out-Null
        }

        $TestFolderName = [string]::Format("{0}_{1}",(Get-RandomString -Length 10), $MachineName)

        if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent) { 
            Write-PWPSLog -Message "TestFolderName:$TestFolderName'" -Level Info -Cmdlet $Cmdlet 
        }

        if ($TestParentPath) {

            if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent) {
                Write-PWPSLog -Message "Parent path specified:$TestParentPath'" -Level Info -Cmdlet $Cmdlet
            }

            if (!(Get-PWFolders -FolderPath $TestParentPath -JustOne)) {
                Write-PWPSLog -Message "Parent path not found! Creating parent path..." -Level Warn -Cmdlet $Cmdlet

                New-PWFolder -FolderPath $TestParentPath -Environment $EnvironmentName
            }

            $TestFolderPath = "$TestParentPath\$TestFolderName"
            $TestFolderDestinationPath = "$TestParentPath\$TestFolderName\Destination"
        } else {
            $TestFolderPath = $TestFolderName
            $TestFolderDestinationPath = "$TestFolderName\Destination"
        } # end if ($TestParentPath.../else...

        if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent) {
            Write-PWPSLog -Message "TestFolderPath:$TestFolderPath'" -Level Info -Cmdlet $Cmdlet
            Write-PWPSLog -Message "TestFolderDestinationPath:$TestFolderDestinationPath'" -Level Info -Cmdlet $Cmdlet
        }

        #endregion Build Test Environment

        #region Folders Create, Modify

        if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent){ Write-PWPSLog -Message "Gathering folder metrics..." -Level Info -Cmdlet $Cmdlet }

        $TestFolder = @{
            FolderPath = $TestFolderPath
            Description = (Get-RandomString -Length 10)
            Environment = $EnvironmentName
        }
        $MetricsFolder.Add("FolderCreate", (Measure-Command {New-PWFolder @TestFolder | Out-Null}).TotalSeconds) # Create folder

        $UpdateTestFolder = @{
            FolderPath = $TestFolderPath;
            NewDescription = (Get-RandomString -Length 10)
        }
        $MetricsFolder.Add("FolderPropertyUpdate", (Measure-Command {Update-PWFolderNameProps @UpdateTestFolder}).TotalSeconds) # Update folder properties

        $TestFolderDestination = @{
            FolderPath = $TestFolderDestinationPath
            Description = (Get-RandomString -Length 10)
            Environment = $EnvironmentName
        }
        New-PWFolder @TestFolderDestination | Out-Null

        #endregion Folders Create, Modify

        #region Documents Create, Copy, Modify, Remove

        if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent){ Write-PWPSLog -Message "Gathering document metrics..." -Level Info -Cmdlet $Cmdlet }

        ## Create document

        $NewPWDocument = @{
            FolderPath = $TestFolderPath
            FilePath = $TempWorkingFileOne.Name
        }
        $MetricsDocument.Add("CreateDocument", (Measure-Command {New-PWDocument @NewPWDocument | Out-Null}).TotalSeconds) # Create document for general testing

        ## Return document

        $MetricsDocument.Add("DocumentSearch", (Measure-Command {$TargetDocument = Get-PWDocumentsBySearch -FolderPath $TestFolderPath -FileName ($TempWorkingFileOne.Name | Split-Path -Leaf) -JustThisFolder}).TotalSeconds) # Return document

        ## Check out document

        $MetricsDocument.Add("DocumentCheckOut", (Measure-Command {CheckOut-PWDocuments -InputDocument $TargetDocument}).TotalSeconds) # Check-out document

        ## Check in document

        $MetricsDocument.Add("DocumentCheckIn", (Measure-Command {CheckIn-PWDocumentsOrFree -InputDocument $TargetDocument}).TotalSeconds) # Check-in document

        ## First copy out document

        $NewPWDocumentForCopyOut = @{
            FolderPath = $TestFolderPath
            FilePath = $TempWorkingFileTwo.Name
        }

        New-PWDocument @NewPWDocumentForCopyOut | Out-Null # Create document for copy out test
        $TargetCopyDocument = Get-PWDocumentsBySearch -FolderPath $TestFolderPath -FileName ($TempWorkingFileTwo.Name | Split-Path -Leaf) -JustThisFolder # Return document
        $MetricsDocument.Add("FirstCopyOut",(Measure-Command {CheckOut-PWDocuments -InputDocument $TargetCopyDocument -CopyOut}).TotalSeconds) # First copy out

        Get-ChildItem -LiteralPath $WorkingDirectory -Recurse | Remove-Item -Recurse -Force # Clear working directory

        ## Second copy out document

        $MetricsDocument.Add("SecondCopyOut",(Measure-Command {CheckOut-PWDocuments -InputDocument $TargetCopyDocument -CopyOut}).TotalSeconds) # Second copy out

        ## Third copy out document

        $MetricsDocument.Add("ThirdCopyOut",(Measure-Command {CheckOut-PWDocuments -InputDocument $TargetCopyDocument -CopyOut}).TotalSeconds) # Third copy out

        ## Copy out variance

        $MetricsDocument.Add("CacheCopyOutVariance", ($MetricsDocument.FirstCopyOut - $MetricsDocument.SecondCopyOut))
        $MetricsDocument.Add("LocalCopyOutVariance", ($MetricsDocument.FirstCopyOut - $MetricsDocument.ThirdCopyOut))

        ## Update document attributes

        $UpdatePWDocumentAttributes = @{
            InputDocument = $TargetDocument
            Attributes = @{$EnvironmentColumnName = (Get-RandomString -Length 10)}
        }
        $MetricsDocument.Add("DocumentAttributeUpdate", (Measure-Command {Update-PWDocumentAttributes @UpdatePWDocumentAttributes}).TotalSeconds) # Update attribute

        ## Update document properties

        $TargetDocument.Description = (Get-RandomString -Length 10) # Define property update
        $MetricsDocument.Add("DocumentPropertyUpdate", (Measure-Command {Update-PWDocumentProperties -InputDocument $TargetDocument}).TotalSeconds) # Update property

        ## Copy document between folders

        $MetricsDocument.Add("CopyDocument", (Measure-Command {$TargetDocument | Copy-PWDocumentsToFolder -TargetFolderPath $TestFolderDestinationPath}).TotalSeconds) # Copy document between folders

        ## Remove document

        $RemoveDocument = Get-PWDocumentsBySearch -FolderPath $TestFolderDestinationPath -JustThisFolder
        $MetricsDocument.Add("RemoveDocument", (Measure-Command {$RemoveDocument | Remove-PWDocuments}).TotalSeconds) # Remove document

        #endregion Documents Create, Copy, Modify, Remove

        #region Workflow

        #endregion Workflow

        #region Deliverables

        #endregion Deliverables

        #region General Continued

        $MetricsGeneral.Add("PWVersion", (Get-PWVersion))

        #endregion General Continued

        #region Folder Remove

        if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent){ Write-PWPSLog -Message "Removing test folders..." -Level Info -Cmdlet $Cmdlet }

        $MetricsFolder.Add("FolderRemove", (Measure-Command {Remove-PWFolder -FolderPath $TestFolderDestinationPath -RemoveDocuments -RemoveFolders -ProceedWithDelete}).TotalSeconds) # Remove Folder
        Remove-PWFolder -FolderPath $TestFolderPath -RemoveDocuments -RemoveFolders -ProceedWithDelete | Out-Null

        #endregion Folder Remove

        #region Users Logout

        if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent){ Write-PWPSLog -Message "Logging out..." -Level Info -Cmdlet $Cmdlet }

        $MetricsUser.Add("Logout", (Measure-Command {Undo-PWLogin | Out-Null}).TotalSeconds) # Logout

        #endregion Users Logout

        #region Totals

        if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent){ Write-PWPSLog -Message "Clearing working directory..." -Level Info -Cmdlet $Cmdlet }

        Get-ChildItem -LiteralPath $WorkingDirectory -Recurse | Remove-Item -Recurse -Force # Clear working directory

        $MetricsFolder.Add("FolderTotal", ($MetricsFolder.FolderCreate + $MetricsFolder.FolderPropertyUpdate + $MetricsFolder.FolderRemove))
        $MetricsDocument.Add("DocumentTotal", ($MetricsDocument.CreateDocument + $MetricsDocument.DocumentSearch + $MetricsDocument.ThirdCopyOut + $MetricsDocument.CopyDocument + $MetricsDocument.SecondCopyOut + $MetricsDocument.RemoveDocument + $MetricsDocument.DocumentCheckIn + $MetricsDocument.DocumentPropertyUpdate + $MetricsDocument.FirstCopyOut + $MetricsDocument.DocumentCheckOut + $MetricsDocument.DocumentAttributeUpdate))
        $MetricsUser.Add("UserTotal", ($MetricsUser.Login + $MetricsUser.Logout))
        $MetricsGeneral.Add("ReportTotal", ($MetricsFolder.FolderTotal + $MetricsDocument.DocumentTotal + $MetricsUser.UserTotal))

        #endregion Totals

        #region Create Dataset

        if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent){ Write-PWPSLog -Message "Creating PS Objects..." -Level Info -Cmdlet $Cmdlet }

        $GeneralObject = New-Object -TypeName PSObject -Property $MetricsGeneral
        $DocumentObject = New-Object -TypeName PSObject -Property $MetricsDocument
        $FolderObject = New-Object -TypeName PSObject -Property $MetricsFolder
        $UserObject = New-Object -TypeName PSObject -Property $MetricsUser

        if ($OutputType -eq "PSObject") {
            if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent){Write-PWPSLog -Message "Creating PS Object DataSet..." -Level Info -Cmdlet $Cmdlet}

            $AllMetrics = @{}
            $AllMetrics.Add("PWPerfGeneral", $MetricsGeneral)
            $AllMetrics.Add("PWPerfDocument", $MetricsDocument)
            $AllMetrics.Add("PWPerfFolder", $MetricsFolder)
            $AllMetrics.Add("PWPerfUser", $MetricsUser)

            $Dataset = New-Object -TypeName PSObject -Property $AllMetrics

        } elseif ($OutputType -eq "DataTable") {
            if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent){Write-PWPSLog -Message "Creating Datatables..." -Level Info -Cmdlet $Cmdlet}

            $GeneralDT  = ConvertTo-DataTable -InputObject $GeneralObject
            $DocumentDT  = ConvertTo-DataTable -InputObject $DocumentObject
            $FolderDT  = ConvertTo-DataTable -InputObject $FolderObject
            $UserDT  = ConvertTo-DataTable -InputObject $UserObject

            $GeneralDT.TableName = "PWPerfGeneral"
            $DocumentDT.TableName = "PWPerfDocument"
            $FolderDT.TableName = "PWPerfFolder"
            $UserDT.TableName = "PWPerfUser"

            if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent){Write-PWPSLog -Message "Adding datatables to dataset..." -Level Info -Cmdlet $Cmdlet}

            [System.Data.DataSet]$Dataset = New-Object System.Data.DataSet
            $Dataset.Tables.Add($GeneralDT)
            $Dataset.Tables.Add($DocumentDT)
            $Dataset.Tables.Add($FolderDT)
            $Dataset.Tables.Add($UserDT)

        } # end if ($OutputType -eq "PSObject"...
        
        #endregion Create Dataset
        
    } # end PROCESS...
    
    END {
        Write-Output $Dataset
    } # end END...
} # end FUNCTION Get-myPWPerformanceData...

FUNCTION Get-PWPerformanceReportData { 
<#
  .SYNOPSIS
    Returns a dataset containing ProjectWise performance metrics.
  .DESCRIPTION
    Performs and times the execution of various tasks in ProjectWise, the results of which are output to a dataset containing four tables. All test documents/folders are cleaned up after the test has completed.
  .PARAMETER Connection
    Used to specify the connection type for later sorting - e.g. "Home Wifi", "Bentley LAN", "4G Dongle" etc.
  .PARAMETER Location
    Used to specify the location from where the test is being run - e.g. "Melbourne, Victoria" etc.
  .PARAMETER ConnectingViaCache
    Used to specify whether or not the client is connecting via a caching server - accepts "True" or "False".
  .PARAMETER Datasource
    Used to specify the datasource to run the test - server:datasource.
  .PARAMETER ProjectWiseUserName
    Used to specify the username for the ProjectWise account running the test.
  .PARAMETER ProjectWisePassword
    Used to specify the password for the ProjectWise account running the test.
  .PARAMETER TestParentPath
    Used to specify a parent path under which all test operations will be completed. If not specified, test operations are completed at root level in the folder tree. All test documents/folders are cleaned up after the test has completed.
  .PARAMETER TestFileSizeInMB
    Used to specify the size of the files in MB used for testing, default value is 10MB.
  .PARAMETER EnvironmentName
    Used to specify the name of the environment to create for testing - the environment will have a single attribute used for testing attribute updates. Default value is "PWPerfReport"
  .PARAMETER EnvironmentTableName
    Used to specify the table name for the test environment. Default value is "PWPerfReport"
  .PARAMETER EnvironmentColumnName
    Used to specify the column/attribure name for the test environment table. Default value is "Test_Attribute"
  .PARAMETER IncludeWSGData
    Switch parameter used to activate the collection of WSG data. If activated, WSGServerURL must be specified.
  .PARAMETER WSGServerURL
    Used to specify the WSG server URL when retrieving WSG data, e.g. "https://decide-pwce-aus-ws.bentley.com/ws"
  .PARAMETER OutputType
    Used to specify output data type as either PSObject or DataTable. Default value is PSObject
  .EXAMPLE
    This example will return a performance metric dataset for a home connection, not using a caching server, and output a PSObject.
    $PWPerformanceReportVariables = @{
            Connection = "Home Wifi";
            Location = "Melbourne, Victoria";
            ConnectingViaCache = "False"
            Datasource = "decide-pwce-aus.bentley.com:decide-pwce-aus-010"
            ProjectWiseUserName = "PWPerfReportUser"
            ProjectWisePassword = (Get-SecureStringFromEncryptedFile -FileName "C:\temp\ProjectWisePassword.txt")
            OutputType = PSObject
        }
         
        $PerformanceData = Get-PWPerformanceReportData @PWPerformanceReportVariables -Verbose
  .EXAMPLE
    This example will return a performance metric dataset for an office connection, using a caching server, and output a DataTable.
    $PWPerformanceReportVariables = @{
            Connection = "Bentley Office LAN";
            Location = "Melbourne, Victoria";
            ConnectingViaCache = "True"
            Datasource = "decide-pwce-aus.bentley.com:decide-pwce-aus-010"
            ProjectWiseUserName = "PWPerfReportUser"
            ProjectWisePassword = (Get-SecureStringFromEncryptedFile -FileName "C:\temp\ProjectWisePassword.txt")
            OutputType = DataTable
        }
         
        $PerformanceData = Get-PWPerformanceReportData @PWPerformanceReportVariables -Verbose
  .EXAMPLE
    This example will return a performance metric dataset for a home connection, not using a caching server, for a 1MB test file size, including WSG data, and output as a PSObject.
    $PWPerformanceReportVariables = @{
        Connection = "Home Wifi";
        Location = "Melbourne, Victoria";
        ConnectingViaCache = "False"
        Datasource = "decide-pwce-aus.bentley.com:decide-pwce-aus-010"
        ProjectWiseUserName = "PWPerfReportUser"
        ProjectWisePassword = (ConvertTo-SecureString -String PWPerfReportUser -AsPlainText -Force)
        OutputType = "PSObject"
        TestParentPath = "PWPerfReport"
        TestFileSizeInMB = 1
        WSGServerURL = "https://decide-pwce-aus-ws.bentley.com/ws"
    }
    $PerformanceData = Get-PWPerformanceReportDataTest @PWPerformanceReportVariables -IncludeWSGData -Verbose
#>

    [CmdletBinding()]

    Param 
    (
        
        [Parameter(Mandatory=$false)]
        [ValidateNotNullOrEmpty()]
        [string]$Connection = "Unknown",

        [Parameter(Mandatory=$false)]
        [ValidateNotNullOrEmpty()]
        [string]$Location = "Unknown",

        [Parameter(Mandatory=$false)]
        [ValidateSet("True", "False")]
        [ValidateNotNullOrEmpty()]
        [string]$ConnectingViaCache,

        [Parameter(Mandatory=$true)]
        [ValidateNotNullOrEmpty()]
        [string]$Datasource,

        [Parameter(Mandatory=$true,
            ParameterSetName = 'PWLogin')]
        [ValidateNotNullOrEmpty()]
        [string]$ProjectWiseUserName,

        [Parameter(Mandatory=$true,
            ParameterSetName = 'PWLogin')]
        [ValidateNotNullOrEmpty()]
        [System.Security.SecureString]$ProjectWisePassword,

        [Parameter(Mandatory=$true,
            ParameterSetName = 'BentleyIMS')]
        [switch]$UseBentleyIMS,

        [Parameter(Mandatory=$false)]
        [ValidateNotNullOrEmpty()]
        [string]$TestParentPath,

        [Parameter(Mandatory=$false)]
        [ValidateNotNullOrEmpty()]
        [int]$TestFileSizeInMB = 10,

        [Parameter(Mandatory=$false)]
        [ValidateNotNullOrEmpty()]
        [string]$EnvironmentName = "PWPerfReport",

        [Parameter(Mandatory=$false)]
        [ValidateNotNullOrEmpty()]
        [string]$EnvironmentTableName = "PWPerfReport",

        [Parameter(Mandatory=$false)]
        [ValidateNotNullOrEmpty()]
        [string]$EnvironmentColumnName = "Test_Attribute",

        [Parameter(Mandatory=$false)]
        [switch]$IncludeWSGData,

        [Parameter(Mandatory=$false)]
        [ValidateNotNullOrEmpty()]
        [string]$WSGServerURL,

        [Parameter(Mandatory=$false)]
        [ValidateSet("PSObject", "DataTable")]
        [ValidateNotNullOrEmpty()]
        [string]$OutputType = "PSObject"

    ) 
 
    Begin 
    {
        
        #region Startup

        $Cmdlet = 'Get-PWPerformanceReportData'
        $MachineName = $env:COMPUTERNAME

        #endregion

        #region Checks
        
        if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent){Write-PWPSLog -Message "Performing startup checks" -Level Info -Cmdlet $Cmdlet}

        if ((Get-PWCurrentDatasource))
        {
            Write-PWPSLog -Message "ProjectWise connection already open. Logout and rerun script!" -Level Error -Cmdlet $Cmdlet
            break;
        }

        if (!($ConnectingViaCache))
        {

            $ConnectingViaCache = "Unknown"

        }

        if ($IncludeWSGData)
        {
            if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent){Write-PWPSLog -Message "Including WSG data" -Level Info -Cmdlet $Cmdlet}

            try
            {
                Import-Module PWPS_WSG -ErrorAction Stop
            }
            catch
            {
                Write-PWPSLog -Message "To gather WSG data the PWPS_WSG module is required. Please install PWPS_WSG. (Install-Module PWPS_WSG)" -Level Warn -Cmdlet $Cmdlet
                Write-PWPSLog -Message "Could not import PWPS_WSG module." -Level Error -Cmdlet $Cmdlet
                break;
            }
        }

        if ($IncludeWSGData -and !($WSGServerURL))
        {
            Write-PWPSLog -Message "The IncludeWSGData switch has been activated, but no WSGServerURL has been specified!" -Level Error -Cmdlet $Cmdlet
            Write-PWPSLog -Message "Please specify WSGServerURL!" -Level Error -Cmdlet $Cmdlet
            break;
        }

        if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent){Write-PWPSLog -Message "Finished startup checks" -Level Info -Cmdlet $Cmdlet}
        #endregion Checks

    } 
    Process 
    { 
        
        #region Create Arrays

        if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent)
        {
        
            Write-PWPSLog -Message "Creating arrays..." -Level Info -Cmdlet $Cmdlet

        }

        $MetricsGeneral = @{}
        $MetricsUser = @{}
        $MetricsDocument = @{}
        $MetricsFolder = @{}
        $MetricsEnvironment = @{}

        if ($IncludeWSGData)
        {
            $MetricsWSG = @{}
        }
        
        if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent)
        {
        
            Write-PWPSLog -Message "Generating test GUID..." -Level Info -Cmdlet $Cmdlet

        }

        $TestGUID = New-Guid
        
        $MetricsGeneral.Add("TestGUID", $TestGUID)
        $MetricsUser.Add("TestGUID", $TestGUID)
        $MetricsDocument.Add("TestGUID", $TestGUID) 
        $MetricsFolder.Add("TestGUID", $TestGUID)
        $MetricsEnvironment.Add("TestGUID", $TestGUID)

        if ($IncludeWSGData)
        {
            $MetricsWSG.Add("TestGUID", $TestGUID)
        }
        
        #endregion

        #region Add General Properties

        if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent)
        {
        
            Write-PWPSLog -Message "Adding general properties..." -Level Info -Cmdlet $Cmdlet

        }

        $MetricsGeneral.Add("MachineName", $MachineName) # Machine name
        $MetricsGeneral.Add("Location", $Location) # Source location
        $MetricsGeneral.Add("Datasource", $Datasource) # Datasource
        $MetricsGeneral.Add("ConnectionType", $Connection) # Connection type
        $MetricsGeneral.Add("ConnectingViaCache", $ConnectingViaCache) # Connecting via cache check
        $MetricsGeneral.Add("TestFileSize", $TestFileSizeInMB) # Test file size
        $MetricsGeneral.Add("TimeZone", (Get-TimeZone).ID) # Time zone
        $MetricsGeneral.Add("ReportStartLocalTime", (Get-Date)) # Test start local time
        $MetricsGeneral.Add("ReportStartUniversalTime",$MetricsGeneral.ReportStartLocalTime.ToUniversalTime()) # Test start UTC
        
        if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent)
        {

            Write-PWPSLog -Message "Test GUID: $TestGUID" -Level Info -Cmdlet $Cmdlet
            Write-PWPSLog -Message "MachineName: $MachineName" -Level Info -Cmdlet $Cmdlet
            Write-PWPSLog -Message "Location: $Location" -Level Info -Cmdlet $Cmdlet
            Write-PWPSLog -Message "ConnectionType: $Connection" -Level Info -Cmdlet $Cmdlet
            Write-PWPSLog -Message "ConnectingViaCache: $ConnectingViaCache" -Level Info -Cmdlet $Cmdlet
            Write-PWPSLog -Message "TestFileSize: $TestFileSizeInMB MB" -Level Info -Cmdlet $Cmdlet
            Write-PWPSLog -Message "TimeZone: $($MetricsGeneral.TimeZone)" -Level Info -Cmdlet $Cmdlet
            Write-PWPSLog -Message "ReportStartLocalTime: $($MetricsGeneral.ReportStartLocalTime)" -Level Info -Cmdlet $Cmdlet
            Write-PWPSLog -Message "ReportStartUniversalTime: $($MetricsGeneral.ReportStartUniversalTime)" -Level Info -Cmdlet $Cmdlet


        }

        #endregion

        #region Add User Login

        if($UseBentleyIMS)
        {
            if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent)
            {        
                Write-PWPSLog -Message "Logging into '$Datasource' using Bentley IMS..." -Level Info -Cmdlet $Cmdlet
            }
            
            $MetricsUser.Add("Login", (Measure-Command {New-PWLogin -DatasourceName $Datasource -BentleyIMS -Verbose}).TotalSeconds) # ProjectWise login
            
            # Generate WSG Headers

            if ($IncludeWSGData)
            {
                if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent){Write-PWPSLog -Message "Building WSG client context" -Level Info -Cmdlet $Cmdlet}

                $Repositories = Get-WSGRepositories -WsgURL $WSGServerURL
                $DisplayLabel = ($Repositories | Where-Object {$PSItem.Location -eq $Datasource}).DisplayLabel

                $WSGClientContext = New-WSGClientContext -UserName "dummy" -Password (ConvertTo-SecureString -String "dummy" -AsPlainText -Force) -DisplayLabel $DisplayLabel -WsgURL $WSGServerURL
                
                $Token = Get-PWConnectionClientToken

                $WSGClientContext.AuthorisationHeader = "TOKEN $Token"

            }
        }
        else
        {
            if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent)
            {        
                Write-PWPSLog -Message "Logging into '$Datasource' as '$ProjectWiseUserName'..." -Level Info -Cmdlet $Cmdlet
            }
            
            $MetricsUser.Add("Login", (Measure-Command {New-PWLogin -DatasourceName $Datasource -UserName $ProjectWiseUserName -Password $ProjectWisePassword -Verbose}).TotalSeconds) # ProjectWise login
            
            # Generate WSG Headers

            if ($IncludeWSGData)
            {
                
                if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent){Write-PWPSLog -Message "Building WSG client context" -Level Info -Cmdlet $Cmdlet}

                $Repositories = Get-WSGRepositories -WsgURL $WSGServerURL
                $DisplayLabel = ($Repositories | Where-Object {$PSItem.Location -eq $Datasource}).DisplayLabel

                $WSGClientContext = New-WSGClientContext -UserName $ProjectWiseUserName -Password $ProjectWisePassword -DisplayLabel $DisplayLabel -WsgURL $WSGServerURL

            }
        }

        if (!(Get-PWCurrentDatasource))
        {
            Write-PWPSLog -Message "Failed to login. Aborting!" -Level Error -Cmdlet $Cmdlet
            break;
        }
        
        if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent)
        {
        
            Write-PWPSLog -Message "Getting currently connected users..." -Level Info -Cmdlet $Cmdlet

        }

        $MetricsUser.Add("CurrentlyConnectedUsers", (Get-PWUsersLoggedIn | Measure-Object).Count) # Currently connected users

        #endregion

        #region Build Test Environment

        if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent)
        {
        
            Write-PWPSLog -Message "Building local test environment..." -Level Info -Cmdlet $Cmdlet
            Write-PWPSLog -Message "Creating test files..." -Level Info -Cmdlet $Cmdlet

        }

        ### Local

        $WorkingDirectory = Get-PWUserWorkingDirectory -UserId ((Get-PWCurrentUser).ID) -Verbose

        $WorkingFileNameOne = "$(Get-RandomString -Length 10).dgn"
        $WorkingFileNameTwo = "$(Get-RandomString -Length 10).dgn"
        
        $TempWorkingFileOne = New-Object System.IO.FileStream "$WorkingDirectory\$WorkingFileNameOne", Create, ReadWrite
        $TempWorkingFileOne.SetLength($TestFileSizeInMB * 1048576)
        $TempWorkingFileOne.Close()

        $TempWorkingFileTwo = New-Object System.IO.FileStream "$WorkingDirectory\$WorkingFileNameTwo", Create, ReadWrite
        $TempWorkingFileTwo.SetLength($TestFileSizeInMB * 1048576)
        $TempWorkingFileTwo.Close()

        if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent)
        {
        
            Write-PWPSLog -Message "WorkingDirectory: $WorkingDirectory" -Level Info -Cmdlet $Cmdlet
            Write-PWPSLog -Message "WorkingFileOne: $WorkingFileNameOne" -Level Info -Cmdlet $Cmdlet
            Write-PWPSLog -Message "WorkingFileTwo: $WorkingFileNameTwo" -Level Info -Cmdlet $Cmdlet

        }

        ### ProjectWise
        
        if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent)
        {
        
            Write-PWPSLog -Message "Building ProjectWise test environment..." -Level Info -Cmdlet $Cmdlet
            Write-PWPSLog -Message "Environment Name: $EnvironmentName" -Level Info -Cmdlet $Cmdlet
            Write-PWPSLog -Message "Environment Table Name: $EnvironmentTableName" -Level Info -Cmdlet $Cmdlet
            Write-PWPSLog -Message "Environment Column Name: $EnvironmentColumnName" -Level Info -Cmdlet $Cmdlet

        }

        $EnvironmentDetailsQuery = "SELECT e.o_envname, t.o_tabname, c.column_name from dms_env e LEFT JOIN dms_tabs t on e.o_tabno = t.o_tabno LEFT JOIN INFORMATION_SCHEMA.COLUMNS c on t.o_tabname = c.TABLE_NAME WHERE e.o_envname = '$EnvironmentName' AND t.o_tabname = '$EnvironmentTableName' and c.column_name = '$EnvironmentColumnName'"
        $EnvironmentDetails = Select-PWSQL -SQLSelectStatement $EnvironmentDetailsQuery
        
        if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent)
        {
        
            Write-PWPSLog -Message "Checking for environment :$EnvironmentName'..." -Level Info -Cmdlet $Cmdlet

        }

        if (($EnvironmentName -ne $EnvironmentDetails.o_envname) -or ($EnvironmentTableName -ne $EnvironmentDetails.o_tabname) -or ($EnvironmentColumnName -ne $EnvironmentDetails.column_name))
        {


            Write-PWPSLog -Message "Specified environment not found! Creating environment..." -Level Warn -Cmdlet $Cmdlet


            $NewPWEnvironment = @{
                EnvironmentName = $EnvironmentName;
                TableName = $EnvironmentTableName;
                ColumnName = $EnvironmentColumnName;
            }

            New-PWEnvironment @NewPWEnvironment -Verbose | Out-Null 
        }

        $TestFolderName = [string]::Format(“{0}_{1}”,(Get-RandomString -Length 10), $MachineName)

        if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent)
        {
        
            Write-PWPSLog -Message "TestFolderName:$TestFolderName'" -Level Info -Cmdlet $Cmdlet

        }

        if ($TestParentPath)
        {

            if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent)
            {
            
                Write-PWPSLog -Message "Parent path specified:$TestParentPath'" -Level Info -Cmdlet $Cmdlet

            }

            if (!(Get-PWFolders -FolderPath $TestParentPath -JustOne))
            {
                
                Write-PWPSLog -Message "Parent path not found! Creating parent path..." -Level Warn -Cmdlet $Cmdlet


                New-PWFolder -FolderPath $TestParentPath -Environment $EnvironmentName
            }

            $TestFolderPath = "$TestParentPath\$TestFolderName"
            $TestFolderDestinationPath = "$TestParentPath\$TestFolderName\Destination"

            if ($IncludeWSGData)
            {
                $TestFolderWSGPath = "$TestFolderPath\WSG"
                $TestFolderWSGDestinationPath = "$TestFolderPath\WSG\Destination"
            }
        }
        else
        {
            $TestFolderPath = $TestFolderName
            $TestFolderDestinationPath = "$TestFolderName\Destination"

            if ($IncludeWSGData)
            {
                $TestFolderWSGPath = "$TestFolderPath\WSG"
                $TestFolderWSGDestinationPath = "$TestFolderPath\WSG\Destination"
            }
        }

        if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent)
        {
        
            Write-PWPSLog -Message "TestFolderPath: '$TestFolderPath'" -Level Info -Cmdlet $Cmdlet
            Write-PWPSLog -Message "TestFolderDestinationPath: '$TestFolderDestinationPath'" -Level Info -Cmdlet $Cmdlet
            
            if ($IncludeWSGData)
            {
                Write-PWPSLog -Message "TestFolderWSGPath: '$TestFolderWSGPath'" -Level Info -Cmdlet $Cmdlet
            }

        }

        ### Write data

        $EnvironmentDocumentCountQuery = "SELECT COUNT(*) FROM $EnvironmentTableName"
        $EnvironmentDocumentCount = (Select-PWSQL -SQLSelectStatement $EnvironmentDocumentCountQuery).Column1

        $EnvironmentColumnsCountQuery = "SELECT TOP (1) * FROM PWPerfReport"
        $EnvironmentColumns = Select-PWSQL -SQLSelectStatement $EnvironmentColumnsCountQuery
        $EnvironmentColumnsTotal = ($EnvironmentColumns.Columns | Measure-Object).Count
        $EnvironmentColumnsSystem = 8
        $EnvironmentColumnsUser = $EnvironmentColumnsTotal - $EnvironmentColumnsSystem

        $MetricsEnvironment.Add("EnvironmentName", $EnvironmentName)
        $MetricsEnvironment.Add("EnvironmentTableName", $EnvironmentTableName)
        $MetricsEnvironment.Add("EnvironmentColumnName", $EnvironmentColumnName)
        $MetricsEnvironment.Add("DocumentsInEnvironment", $EnvironmentDocumentCount)
        $MetricsEnvironment.Add("TotalAttributes", $EnvironmentColumnsTotal)
        $MetricsEnvironment.Add("SystemAttributes", $EnvironmentColumnsSystem)
        $MetricsEnvironment.Add("UserAttributes", $EnvironmentColumnsUser)

        #endregion

        #region Folders Create, Modify

        if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent)
        {
            Write-PWPSLog -Message "Gathering folder metrics..." -Level Info -Cmdlet $Cmdlet
        }

        if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent){Write-PWPSLog -Message "Creating test folder..." -Level Info -Cmdlet $Cmdlet}
        $TestFolder = @{
            FolderPath = $TestFolderPath;
            Description = (Get-RandomString -Length 10);
            Environment = $EnvironmentName;
        }
        $MetricsFolder.Add("FolderCreate", (Measure-Command {$TestFolderCreation = New-PWFolder @TestFolder}).TotalSeconds) # Create folder

        if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent){Write-PWPSLog -Message "Updating folder properties" -Level Info -Cmdlet $Cmdlet}
        $UpdateTestFolder = @{
            FolderPath = $TestFolderPath;
            NewDescription = (Get-RandomString -Length 10);
        }
        $MetricsFolder.Add("FolderPropertyUpdate", (Measure-Command {Update-PWFolderNameProps @UpdateTestFolder}).TotalSeconds) # Update folder properties

        if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent){Write-PWPSLog -Message "Adding destination folder" -Level Info -Cmdlet $Cmdlet}
        $TestFolderDestination = @{
            FolderPath = $TestFolderDestinationPath;
            Description = (Get-RandomString -Length 10);
            Environment = $EnvironmentName;
        }
        New-PWFolder @TestFolderDestination | Out-Null

        if ($IncludeWSGData)
        {
            # Create folder

            if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent){Write-PWPSLog -Message "Creating WSG folder" -Level Info -Cmdlet $Cmdlet}

            $Environments = Get-PWEnvironments
            $EnvironmentId = ($Environments | Where-Object {$PSItem.TableName -eq $EnvironmentTableName}).ID

            $MetricsWSG.Add("WSGFolderCreate",(Measure-Command {$WSGFolder = New-WSGProject -ProjectName "WSG" -ParentProjectGuid $TestFolderCreation.ProjectGUIDString -ProjectDescription "BaseProjectDescription" -EnvironmentId $EnvironmentId -WSGClientContext $WSGClientContext -ReturnProject}).TotalSeconds) # WSG create folder
            
            if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent){Write-PWPSLog -Message "Adding WSG destination folder" -Level Info -Cmdlet $Cmdlet}

            $WSGDestinationFolder = New-WSGProject -ProjectName "Destination" -ParentProjectGuid $WSGFolder.instanceId -ProjectDescription "BaseProjectDescription" -EnvironmentId $EnvironmentId -WSGClientContext $WSGClientContext -ReturnProject


            # Update folder properties
            if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent){Write-PWPSLog -Message "Updating WSG folder properties" -Level Info -Cmdlet $Cmdlet}

            $MetricsWSG.Add("WSGFolderPropertyUpdate",(Measure-Command {Update-WSGProjectProperties -ProjectGuid $WSGFolder.instanceId -NewProjectDescription (Get-RandomString -Length 10) -WSGClientContext $WSGClientContext}).TotalSeconds) # WSG update folder properties
        
        }
        
        #endregion

        #region Documents Create, Copy, Modify, Remove

        if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent){Write-PWPSLog -Message "Gathering document metrics..." -Level Info -Cmdlet $Cmdlet}

        ## Create document

        if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent){Write-PWPSLog -Message "Creating document" -Level Info -Cmdlet $Cmdlet}
        $NewPWDocument = @{
            FolderPath = $TestFolderPath;
            FilePath = $TempWorkingFileOne.Name;   
        }
        $MetricsDocument.Add("CreateDocument", (Measure-Command {New-PWDocument @NewPWDocument | Out-Null}).TotalSeconds) # Create document for general testing

        
        if ($IncludeWSGData)
        {
            if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent){Write-PWPSLog -Message "Creating WSG document" -Level Info -Cmdlet $Cmdlet}
            $MetricsWSG.Add("WSGCreateDocument", (Measure-Command {$NewWSGDoc = New-WSGDocument -DocumentName $WorkingFileNameOne.Split('.')[0] -FilePath "$WorkingDirectory\$WorkingFileNameOne" -ParentProjectGuid $WSGFolder.instanceId -DocumentDescription "BaseDescription" -WSGClientContext $WSGClientContext -ReturnDocument}).TotalSeconds)

        }

        ## Return document
        if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent){Write-PWPSLog -Message "Returning document" -Level Info -Cmdlet $Cmdlet}
        $MetricsDocument.Add("DocumentSearch", (Measure-Command {$TargetDocument = Get-PWDocumentsBySearch -FolderPath $TestFolderPath -FileName ($TempWorkingFileOne.Name | Split-Path -Leaf) -JustThisFolder}).TotalSeconds) # Return document
        
        if ($IncludeWSGData)
        {
            if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent){Write-PWPSLog -Message "Returning WSG document" -Level Info -Cmdlet $Cmdlet}
            $MetricsWSG.Add("WSGDocumentSearch", (Measure-Command {Get-WSGDocumentByGuid -DocumentGuid $NewWSGDoc.instanceId -WSGClientContext $WSGClientContext}).TotalSeconds) # WSG return document
        }

        ## Check out document

        if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent){Write-PWPSLog -Message "Checking out document" -Level Info -Cmdlet $Cmdlet}
        $MetricsDocument.Add("DocumentCheckOut", (Measure-Command {CheckOut-PWDocuments -InputDocument $TargetDocument}).TotalSeconds) # Check-out document
        
        if ($IncludeWSGData)
        {
            if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent){Write-PWPSLog -Message "Checking out WSG document" -Level Info -Cmdlet $Cmdlet}
            $MetricsWSG.Add("WSGDocumentCheckOut", (Measure-Command {Invoke-WSGDocumentCheckOut -DocumentGuid $NewWSGDoc.instanceId -CheckOutDirectory $WorkingDirectory -FileName "$($WorkingFileNameOne.Split('.')[0])_WSGCheckOut.dgn" -WSGClientContext $WSGClientContext}).TotalSeconds) # TODO: Change this to be a WSG call
        }

        ## Check in document

        if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent){Write-PWPSLog -Message "Checking in document" -Level Info -Cmdlet $Cmdlet}
        $MetricsDocument.Add("DocumentCheckIn", (Measure-Command {CheckIn-PWDocumentsOrFree -InputDocument $TargetDocument}).TotalSeconds) # Check-in document
        
        if ($IncludeWSGData)
        {
            if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent){Write-PWPSLog -Message "Checking in WSG document" -Level Info -Cmdlet $Cmdlet}
            $MetricsWSG.Add("WSGDocumentCheckIn", (Measure-Command {Invoke-WSGDocumentCheckIn -DocumentGuid $NewWSGDoc.instanceId -FilePath "$WorkingDirectory\$($WorkingFileNameOne.Split('.')[0])_WSGCheckOut.dgn" -Comment "CheckedInByPWPerf" -WSGClientContext $WSGClientContext}).TotalSeconds) # TODO: Change this to be a WSG call
        }

        ## First copy out document

        $NewPWDocumentForCopyOut = @{
            FolderPath = $TestFolderPath;
            FilePath = $TempWorkingFileTwo.Name;   
        }

        if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent){Write-PWPSLog -Message "Copying out document (1)" -Level Info -Cmdlet $Cmdlet}
        New-PWDocument @NewPWDocumentForCopyOut | Out-Null # Create document for copy out test

        $TargetCopyDocument = Get-PWDocumentsBySearch -FolderPath $TestFolderPath -FileName ($TempWorkingFileTwo.Name | Split-Path -Leaf) -JustThisFolder # Return document
        $MetricsDocument.Add("FirstCopyOut",(Measure-Command {CheckOut-PWDocuments -InputDocument $TargetCopyDocument -CopyOut}).TotalSeconds) # First copy out

        if ($IncludeWSGData)
        {
            if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent){Write-PWPSLog -Message "Copying out WSG document (1)" -Level Info -Cmdlet $Cmdlet}
            $MetricsWSG.Add("WSGFirstCopyOut", (Measure-Command {Invoke-WSGDocumentCopyOut -DocumentGuid $NewWSGDoc.instanceId -CopyOutDirectory $WorkingDirectory -FileName "$($WorkingFileNameOne.Split('.')[0])_WSGCopyOut.dgn" -WSGClientContext $WSGClientContext}).TotalSeconds) # WSG first copy out
        }

        # Clear working directory

        if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent){Write-PWPSLog -Message "Clearing temporary files from working directory" -Level Info -Cmdlet $Cmdlet}

        Get-Item -LiteralPath "$WorkingDirectory\$WorkingFileNameOne" | Remove-Item -Force # Remove working file one
        Get-Item -LiteralPath "$WorkingDirectory\$WorkingFileNameTwo" | Remove-Item -Force # Remove working file two

        if ($IncludeWSGData)
        {
            Get-Item -LiteralPath "$WorkingDirectory\$($WorkingFileNameOne.Split('.')[0])_WSGCheckOut.dgn" | Remove-Item -Force # Remove wsg check out file
            Get-Item -LiteralPath "$WorkingDirectory\$($WorkingFileNameOne.Split('.')[0])_WSGCopyOut.dgn" | Remove-Item -Force # Remove wsg copy out file
        }
        
        if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent){Write-PWPSLog -Message "Clearing dms folders related to perf testing from working directory" -Level Info -Cmdlet $Cmdlet}
        $AllTestFolders = Get-PWFolders -FolderPath $TestFolderPath

        foreach ($Folder in $AllTestFolders)
        {
            if (Test-Path "$WorkingDirectory\$($Folder.Code)")
            {
                Get-Item -LiteralPath "$WorkingDirectory\$($Folder.Code)" | Remove-Item -Recurse -Force # Remove local folder for each test folder
            }
        }
        
        ## Second copy out document

        if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent){Write-PWPSLog -Message "Copying out document (2)" -Level Info -Cmdlet $Cmdlet}
        $MetricsDocument.Add("SecondCopyOut",(Measure-Command {CheckOut-PWDocuments -InputDocument $TargetCopyDocument -CopyOut}).TotalSeconds) # Second copy out
        
        if ($IncludeWSGData)
        {
            if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent){Write-PWPSLog -Message "Copying out WSG document (2)" -Level Info -Cmdlet $Cmdlet}
            $MetricsWSG.Add("WSGSecondCopyOut", (Measure-Command {Invoke-WSGDocumentCopyOut -DocumentGuid $NewWSGDoc.instanceId -CopyOutDirectory $WorkingDirectory -FileName "$($WorkingFileNameOne.Split('.')[0])_WSGCopyOut.dgn" -WSGClientContext $WSGClientContext}).TotalSeconds) # WSG second copy out
        }

        ## Third copy out document
        
        if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent){Write-PWPSLog -Message "Copying out document (3)" -Level Info -Cmdlet $Cmdlet}
        $MetricsDocument.Add("ThirdCopyOut",(Measure-Command {CheckOut-PWDocuments -InputDocument $TargetCopyDocument -CopyOut}).TotalSeconds) # Third copy out

        if ($IncludeWSGData)
        {
            if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent){Write-PWPSLog -Message "Copying out WSG document (3)" -Level Info -Cmdlet $Cmdlet}
            $MetricsWSG.Add("WSGThirdCopyOut", (Measure-Command {Invoke-WSGDocumentCopyOut -DocumentGuid $NewWSGDoc.instanceId -CopyOutDirectory $WorkingDirectory -FileName "$($WorkingFileNameOne.Split('.')[0])_WSGCopyOut.dgn" -WSGClientContext $WSGClientContext}).TotalSeconds) # WSG third copy out
        }

        ## Copy out variance

        $MetricsDocument.Add("CacheCopyOutVariance", ($MetricsDocument.FirstCopyOut - $MetricsDocument.SecondCopyOut))
        $MetricsDocument.Add("LocalCopyOutVariance", ($MetricsDocument.FirstCopyOut - $MetricsDocument.ThirdCopyOut))

        ## Update document attributes

        if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent){Write-PWPSLog -Message "Updating document attributes" -Level Info -Cmdlet $Cmdlet}
        $UpdatePWDocumentAttributes = @{
            InputDocument = $TargetDocument;
            Attributes = @{$EnvironmentColumnName = (Get-RandomString -Length 10)};
        }
        $MetricsDocument.Add("DocumentAttributeUpdate", (Measure-Command {Update-PWDocumentAttributes @UpdatePWDocumentAttributes}).TotalSeconds) # Update attribute

        if ($IncludeWSGData)
        {
            if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent){Write-PWPSLog -Message "Updating WSG document attributes" -Level Info -Cmdlet $Cmdlet}
            $WSGDocEnvClassName = [string]::Format("Env_{0}_{1}", $EnvironmentId, $EnvironmentName)

            $MetricsWSG.Add("WSGDocumentAttributeUpdate", (Measure-Command {Update-WSGDocumentAttributes -DocumentGuid $NewWSGDoc.instanceId -ParentProjectGuid $WSGFolder.instanceId -EnvironmentClassName $WSGDocEnvClassName -Attributes @{$EnvironmentColumnName = (Get-RandomString -Length 10).ToString()} -WSGClientContext $WSGClientContext}).TotalSeconds) # WSG attribute update
        }

        ## Update document properties

        
        if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent){Write-PWPSLog -Message "Updating document properties" -Level Info -Cmdlet $Cmdlet}
        $TargetDocument.Description = (Get-RandomString -Length 10) # Define property update
        $MetricsDocument.Add("DocumentPropertyUpdate", (Measure-Command {Update-PWDocumentProperties -InputDocument $TargetDocument}).TotalSeconds) # Update property
        
        if ($IncludeWSGData)
        {
            if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent){Write-PWPSLog -Message "Updating WSG document properties" -Level Info -Cmdlet $Cmdlet}
            $MetricsWSG.Add("WSGDocumentPropertyUpdate", (Measure-Command {Update-WSGDocumentProperties -DocumentGuid $NewWSGDoc.instanceId -NewDocumentDescription (Get-RandomString -Length 10) -WSGClientContext $WSGClientContext}).TotalSeconds) # WSG Update property
        }

        ## Copy document between folders
        
        if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent){Write-PWPSLog -Message "Copying document between folders" -Level Info -Cmdlet $Cmdlet}
        $MetricsDocument.Add("CopyDocument", (Measure-Command {$TargetDocument | Copy-PWDocumentsToFolder -TargetFolderPath $TestFolderDestinationPath}).TotalSeconds) # Copy document between folders
        
        if ($IncludeWSGData)
        {
            if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent){Write-PWPSLog -Message "Copying WSG document between folders" -Level Info -Cmdlet $Cmdlet}
            $MetricsWSG.Add("WSGCopyDocument", (Measure-Command {$TargetWSGDocument = Copy-WSGDocumentBetweenProjects -DocumentGuid $NewWSGDoc.instanceId -DestinationProjectGuid $WSGDestinationFolder.instanceId -WSGClientContext $WSGClientContext -ReturnDocument}).TotalSeconds) # WSG Copy between folders
        }

        ## Remove document

        if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent){Write-PWPSLog -Message "Removing document" -Level Info -Cmdlet $Cmdlet}
        $RemoveDocument = Get-PWDocumentsBySearch -FolderPath $TestFolderDestinationPath -JustThisFolder
        $MetricsDocument.Add("RemoveDocument", (Measure-Command {$RemoveDocument | Remove-PWDocuments}).TotalSeconds) # Remove document
        
        if ($IncludeWSGData)
        {
            if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent){Write-PWPSLog -Message "Removing WSG document" -Level Info -Cmdlet $Cmdlet}
            $MetricsWSG.Add("WSGRemoveDocument", (Measure-Command {Remove-WSGDocument -DocumentGuid $TargetWSGDocument.instanceId -WSGClientContext $WSGClientContext}).TotalSeconds) # WSG remove document
        }

        #endregion

        #region Workflow
        
        #endregion Workflow
        
        #region Deliverables
        
        #endregion Deliverables

        #region General Continued

        $MetricsGeneral.Add("PWVersion", (Get-PWVersion))
        
        #endregion
        
        #region Folder Remove

        if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent){Write-PWPSLog -Message "Removing folder" -Level Info -Cmdlet $Cmdlet}
        
        $MetricsFolder.Add("FolderRemove", (Measure-Command {Remove-PWFolder -FolderPath $TestFolderDestinationPath -RemoveDocuments -RemoveFolders -ProceedWithDelete}).TotalSeconds) # Remove Folder

        if ($IncludeWSGData)
        {
            if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent){Write-PWPSLog -Message "Removing WSG folder" -Level Info -Cmdlet $Cmdlet}
            $MetricsWSG.Add("WSGFolderRemove", (Measure-Command {Remove-WSGProject -ProjectGuid $WSGDestinationFolder.instanceId -WSGClientContext $WSGClientContext}).TotalSeconds) # WSG remove folder
        }

        if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent){Write-PWPSLog -Message "Cleaning up ProjectWise" -Level Info -Cmdlet $Cmdlet}

        Remove-PWFolder -FolderPath $TestFolderPath -RemoveDocuments -RemoveFolders -ProceedWithDelete | Out-Null

        
        
        #endregion
        
        #region Users Logout

        if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent){Write-PWPSLog -Message "Logging out..." -Level Info -Cmdlet $Cmdlet}
        
        $MetricsUser.Add("Logout", (Measure-Command {Undo-PWLogin | Out-Null}).TotalSeconds) # Logout
        
        #endregion
        
        #region Totals

        if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent){Write-PWPSLog -Message "Clearing working directory..." -Level Info -Cmdlet $Cmdlet}
        
        if ($IncludeWSGData)
        {
            Get-Item -LiteralPath "$WorkingDirectory\$($WorkingFileNameOne.Split('.')[0])_WSGCopyOut.dgn" | Remove-Item -Force # Remove wsg copy out file
        }

        foreach ($Folder in $AllTestFolders)
        {
            if (Test-Path "$WorkingDirectory\$($Folder.Code)")
            {
                Get-Item -LiteralPath "$WorkingDirectory\$($Folder.Code)" | Remove-Item -Recurse -Force # Remove local folder for each test folder
            }
        }

        $MetricsFolder.Add("FolderTotal", ($MetricsFolder.FolderCreate + $MetricsFolder.FolderPropertyUpdate + $MetricsFolder.FolderRemove))
        $MetricsDocument.Add("DocumentTotal", ($MetricsDocument.CreateDocument + $MetricsDocument.DocumentSearch + $MetricsDocument.ThirdCopyOut + $MetricsDocument.CopyDocument + $MetricsDocument.SecondCopyOut + $MetricsDocument.RemoveDocument + $MetricsDocument.DocumentCheckIn + $MetricsDocument.DocumentPropertyUpdate + $MetricsDocument.FirstCopyOut + $MetricsDocument.DocumentCheckOut + $MetricsDocument.DocumentAttributeUpdate))
        $MetricsUser.Add("UserTotal", ($MetricsUser.Login + $MetricsUser.Logout))
        
        if ($IncludeWSGData)
        {
            $MetricsWSG.Add("WSGFolderTotal", ($MetricsWSG.WSGFolderCreate + $MetricsWSG.WSGFolderPropertyUpdate + $MetricsWSG.WSGFolderRemove))
            $MetricsWSG.Add("WSGDocumentTotal", ($MetricsWSG.WSGCreateDocument + $MetricsWSG.WSGDocumentSearch + $MetricsWSG.WSGThirdCopyOut + $MetricsWSG.WSGCopyDocument + $MetricsWSG.WSGSecondCopyOut + $MetricsWSG.WSGRemoveDocument + $MetricsWSG.WSGDocumentCheckIn + $MetricsWSG.WSGDocumentPropertyUpdate + $MetricsWSG.WSGFirstCopyOut + $MetricsWSG.WSGDocumentCheckOut + $MetricsWSG.WSGDocumentAttributeUpdate))
            $MetricsWSG.Add("WSGTotal", ($MetricsWSG.WSGFolderTotal + $MetricsWSG.WSGDocumentTotal))
            
            $MetricsGeneral.Add("ReportTotal", ($MetricsFolder.FolderTotal + $MetricsDocument.DocumentTotal + $MetricsUser.UserTotal + $MetricsWSG.WSGTotal))
        }
        else
        {
            $MetricsGeneral.Add("ReportTotal", ($MetricsFolder.FolderTotal + $MetricsDocument.DocumentTotal + $MetricsUser.UserTotal))
        }

        #endregion

        #region Create Dataset

        if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent){Write-PWPSLog -Message "Creating PS Objects..." -Level Info -Cmdlet $Cmdlet}
        
        $GeneralObject = New-Object -TypeName PSObject -Property $MetricsGeneral
        $DocumentObject = New-Object -TypeName PSObject -Property $MetricsDocument
        $FolderObject = New-Object -TypeName PSObject -Property $MetricsFolder
        $UserObject = New-Object -TypeName PSObject -Property $MetricsUser
        $EnvironmentObject = New-Object -TypeName PSObject -Property $MetricsEnvironment

        if ($IncludeWSGData)
        {
            $WSGObject = New-Object -TypeName PSObject -Property $MetricsWSG
        }

        if ($OutputType -eq "PSObject")
        {
            if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent){Write-PWPSLog -Message "Creating PS Object DataSet..." -Level Info -Cmdlet $Cmdlet}

            $AllMetrics = @{}
            $AllMetrics.Add("PWPerfGeneral", $MetricsGeneral)
            $AllMetrics.Add("PWPerfDocument", $MetricsDocument)
            $AllMetrics.Add("PWPerfFolder", $MetricsFolder)
            $AllMetrics.Add("PWPerfUser", $MetricsUser)
            $AllMetrics.Add("PWPerfEnvironment", $MetricsEnvironment)

            if ($IncludeWSGData)
            {
                $AllMetrics.Add("PWPerfWSG", $MetricsWSG)
            }

            $Dataset = New-Object -TypeName PSObject -Property $AllMetrics

        }
        elseif ($OutputType -eq "DataTable")
        {
            if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent){Write-PWPSLog -Message "Creating Datatables..." -Level Info -Cmdlet $Cmdlet}
            
            $GeneralDT  = ConvertTo-DataTable -InputObject $GeneralObject
            $DocumentDT  = ConvertTo-DataTable -InputObject $DocumentObject
            $FolderDT  = ConvertTo-DataTable -InputObject $FolderObject
            $UserDT  = ConvertTo-DataTable -InputObject $UserObject
            $EnvironmentDT  = ConvertTo-DataTable -InputObject $EnvironmentObject
            
            $GeneralDT.TableName = "PWPerfGeneral"
            $DocumentDT.TableName = "PWPerfDocument"
            $FolderDT.TableName = "PWPerfFolder"
            $UserDT.TableName = "PWPerfUser"
            $EnvironmentDT.TableName = "PWPerfEnvironment"

            if ($IncludeWSGData)
            {
                $WSGDT = ConvertTo-DataTable -InputObject $WSGObject
                $WSGDT.TableName = "PWPerfWSG"
            }

            if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent){Write-PWPSLog -Message "Adding datatables to dataset..." -Level Info -Cmdlet $Cmdlet}
        
            [System.Data.DataSet]$Dataset = New-Object System.Data.DataSet
            $Dataset.Tables.Add($GeneralDT)
            $Dataset.Tables.Add($DocumentDT)
            $Dataset.Tables.Add($FolderDT)
            $Dataset.Tables.Add($UserDT)
            $Dataset.Tables.Add($EnvironmentDT)

            if ($IncludeWSGData)
            {
                $Dataset.Tables.Add($WSGDT)
            }

        }

        #endregion

    } 
    End 
    {
        
        Write-Output $Dataset

    } 
}

#endregion Reporting

#region SharePoint

 function Copy-FileToSharePoint
{

    <#
            .SYNOPSIS
            Copies a file from Windows to SharePoint.
            .DESCRIPTION
            Takes a single file as input and uploads to specified location in SharePoint.
            .PARAMETER FilePath
            Windows filepath to target file.
            .PARAMETER SharePointConnection
            SharePoint connection object. (Generated using New-SharePointConnection)
            .PARAMETER SharePointFolderPath
            URL path to the target SharePoint folder, minus the server name. (e.g. the folder path input for 'https://my-sharepoint-server.com/TargetFolderPath' would be 'TargetFolderPath')
            .PARAMETER CheckSharePointBeforeCopy
            Switch to check whether the file already exists in SharePoint. The file will be updated with the latest version if it exists.
            .PARAMETER NumberOfAttemps
            Integer value for number of attempts before returning a failure. Default number is 20.
            .EXAMPLE
            This example will copy the input file to /TargetFolder, and check if the document exists before copying.
            $Connection = New-SharePointConnection -SharePointURL 'https://my-sharepoint-server.com/Transmittals' -Credentials $Credentials -SharePointVersion 2013 -Verbose
            Copy-FileToSharePoint -FilePath C:\Path\To\File.pdf -SharePointConnection $Connection -SharePointFolderPath 'TargetFolder' -CheckSharePointBeforeCopy
 
    #>

    [CmdletBinding()]

    Param
    (

        [Parameter(Mandatory=$true)]
        [ValidateNotNullOrEmpty()]
        [string]$FilePath,

        [Parameter(Mandatory=$true)]
        [ValidateNotNullOrEmpty()]
        [SharePointPnP.PowerShell.Commands.Base.SPOnlineConnection]$SharePointConnection,

        [Parameter(Mandatory=$true)]
        [ValidateNotNullOrEmpty()]
        [string]$SharePointFolderPath,

        [Parameter(Mandatory=$false)]
        [switch]$CheckSharePointBeforeCopy,

        [Parameter(Mandatory=$false)]
        [int]$NumberOfAttempts = 20

    )

    Begin
    {

        #region Startup

        $Cmdlet = 'Copy-FileToSharePoint'

        $FileName = $FilePath | Split-Path -Leaf

        $FileNameNoExtension = $FileName.Substring(0, $FileName.lastIndexOf('.'))

        #endregion Startup

        #region Parameter Checks

        ## SharePoint URL

        $Message = "SharePoint URL is '$($SharePointConnection.URL)/$SharePointFolderPath'."
        Write-Verbose $Message
        if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent)
        {

            Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message

        }

        ## Input File

        if ((Test-Path -LiteralPath $FilePath))
        {

            $Message = "Found target file."
            Write-Verbose $Message
            if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent)
            {

                Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message

            }

        }
        else
        {

            $Message = "Target file does not exist!"
            Write-Error $Message
            Write-PWPSLog -Cmdlet $Cmdlet -Level Error -Message $Message

            break;

        }

        ## Number of attempts

        $Message = "Number of attempts set to $NumberOfAttempts."
        Write-Verbose $Message
        if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent)
        {

            Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message

        }

        ## File name

        $Message = "File name is '$FileName'."
        Write-Verbose $Message
        if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent)
        {

            Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message

        }

        #endregion Parameter Checks

    }
    Process
    {

        #region Check SharePoint For File

        if ($CheckSharePointBeforeCopy)
        {

            $Message = "Checking if file exists in SharePoint..."
            Write-Verbose $Message
            if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent)
            {

                Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message

            }

            $i = 0
            do
            {

                try
                {

                    $i++
                    $ExistingFile = Get-PnPFile -Url "$SharePointFolderPath/$FileNameNoExtension" -Connection $SharePointConnection -ErrorAction Stop

                }
                catch
                {

                    $Message = "Failed to find '$FileName' in SharePoint on attempt $i."
                    Write-Warning $Message
                    Write-PWPSLog -Cmdlet $Cmdlet -Level Warn -Message $Message
                    Write-PWPSLog -Cmdlet $Cmdlet -Level Warn -Message $_.Exception.Message


                }

                Start-Sleep -Seconds 3

            }
            while (!($ExistingFile) -and $i -lt 3)


            if ($ExistingFile)
            {

                $Message = "'$FileName' already exists in SharePoint. Updating with latest version..."
                Write-Verbose $Message
                if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent)
                {

                    Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message

                }

            }
            else
            {

                $Message = "'$FileName' does not exist in SharePoint. Creating new file..."
                Write-Verbose $Message
                if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent)
                {

                    Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message

                }

            }

        }

        #endregion Check SharePoint For File

        #region Upload File To SharePoint

        $Message = "Uploading '$FileName' to SharePoint..."
        Write-Verbose $Message
        if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent)
        {

            Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message

        }

        $i = 0
        do
        {

            try
            {

                $i++

                $SharePointFile = Add-PnPFile -Path $FilePath -Folder $SharePointFolderPath -Connection $SharePointConnection -ErrorAction Stop

            }
            catch
            {

                $Message = "Failed to upload file to SharePoint on attempt $i."
                Write-Warning $Message
                Write-PWPSLog -Cmdlet $Cmdlet -Level Warn -Message $Message
                Write-PWPSLog -Cmdlet $Cmdlet -Level Warn -Message $_.Exception.Message


            }

            Start-Sleep -Seconds 3

        }
        while (!($SharePointFile) -and $i -lt $NumberOfAttempts)


        if ($SharePointFile)
        {

            $Message = "Successfully uploaded file to SharePoint on attempt $i."
            Write-Verbose $Message
            if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent)
            {

                Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message

            }

        }
        else
        {

            $Message = "Failed to upload '$FileName' to SharePoint after $NumberOfAttempts attempts."
            Write-Error $Message
            Write-PWPSLog -Cmdlet $Cmdlet -Level Error -Message $Message

        }

        #endregion Upload File To SharePoint

    }
    End
    {

        #region Return SharePoint File

        if ($SharePointFile)
        {

            $Properties = @{

                Name = $SharePointFile.Name;
                URL = "$($SharePointConnection.Url)/$SharePointFolderPath/$($SharePointFile.Name)"

            }

            $OutputObject = New-Object -TypeName PSObject -Property $Properties

            Write-Output $OutputObject

        }

        #endregion Return SharePoint File

    }

}

function Copy-PWDocumentToSharePoint
{

    <#
            .SYNOPSIS
            Copies a document from ProjectWise to SharePoint.
            .DESCRIPTION
            Takes a single ProjectWise document as input, copies it to a local working directory, then uploads to specified location in SharePoint.
            .PARAMETER ProjectWiseDocument
            Input ProjectWise document.
            .PARAMETER WorkingDirectory
            Path to local working directory.
            .PARAMETER SharePointConnection
            SharePoint connection object. (Generated using New-SharePointConnection)
            .PARAMETER SharePointDocumentName
            Name to use for document in SharePoint.
            .PARAMETER SharePointFolderPath
            URL path to the target SharePoint folder, minus the server name. (e.g. the folder path input for 'https://my-sharepoint-server.com/TargetFolderPath' would be 'TargetFolderPath')
            .PARAMETER SharePointMetadata
            Hashtable of metadata to apply to the SharePoint document. Note, this must use the correct field names as defined in SharePoint, or document upload will fail.
            .PARAMETER CheckSharePointBeforeCopy
            Switch to check whether the document already exists in SharePoint. The document will be updated with the latest version if it exists.
            .PARAMETER NumberOfAttemps
            Integer value for number of attempts before returning a failure. Default number is 20.
            .EXAMPLE
            This example will copy the input ProjectWise document and metadata to /Transmittals/Drawings, and check if the document exists before copying.
            $Connection = New-SharePointConnection -SharePointURL 'https://my-sharepoint-server.com/Transmittals' -Credentials $Credentials -SharePointVersion 2013 -Verbose
            $SharePointMetadata = @{
            Title = "Test Title";
            Revision = "1";
            Description = "Test Description";
            Design_Package = "Design Package One";
            Created = $ProjectWiseDocument.DocumentUpdateDate;
            }
            Copy-PWDocumentToSharePoint -ProjectWiseDocument $ProjectWiseDocument -WorkingDirectory "C:\temp" -SharePointConnection $Connection -SharePointDocumentName "ModifiedNameTest" -SharePointFolderPath '/Drawings' -CheckSharePointBeforeCopy -SharePointMetadata $SharePointMetadata -Verbose
 
    #>

    [CmdletBinding()]

    Param
    (

        [Parameter(Mandatory=$true)]
        [ValidateNotNullOrEmpty()]
        [PWPS_DAB.CommonTypes+ProjectWiseDocument]$ProjectWiseDocument,

        [Parameter(Mandatory=$true)]
        [ValidateNotNullOrEmpty()]
        [string]$WorkingDirectory,

        [Parameter(Mandatory=$true)]
        [ValidateNotNullOrEmpty()]
        [SharePointPnP.PowerShell.Commands.Base.SPOnlineConnection]$SharePointConnection,

        [Parameter(Mandatory=$true)]
        [ValidateNotNullOrEmpty()]
        [string]$SharePointDocumentName,

        [Parameter(Mandatory=$true)]
        [ValidateNotNullOrEmpty()]
        [string]$SharePointFolderPath,

        [Parameter(Mandatory=$false)]
        [ValidateNotNullOrEmpty()]
        [System.Collections.HashTable]$SharePointMetadata,

        [Parameter(Mandatory=$false)]
        [switch]$CheckSharePointBeforeCopy,

        [Parameter(Mandatory=$false)]
        [int]$NumberOfAttempts = 20

    )

    Begin
    {

        #region Startup

        $Cmdlet = 'Copy-PWDocumentToSharePoint'

        #endregion Startup

        #region Parameter Checks

        ## SharePoint URL

        $Message = "SharePoint URL is '$($SharePointConnection.URL)/$SharePointFolderPath'."
        Write-Verbose $Message
        if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent)
        {

            Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message

        }

        ## Working Directory

        if ((Test-Path -LiteralPath $WorkingDirectory))
        {

            $Message = "Found working directory."
            Write-Verbose $Message
            if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent)
            {

                Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message

            }

        }
        else
        {

            $Message = "Working directory does not exist!"
            Write-Error $Message
            Write-PWPSLog -Cmdlet $Cmdlet -Level Error -Message $Message

            break;

        }

        ## Number of attempts

        $Message = "Number of attempts set to $NumberOfAttempts."
        Write-Verbose $Message
        if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent)
        {

            Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message

        }

        ## SharePoint Metadata

        if ($SharePointMetadata)
        {

            $Message = "SharePoint metadata supplied."
            Write-Verbose $Message
            if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent)
            {

                Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message

            }

        }
        else
        {

            $Message = "No SharePoint metadata supplied."
            Write-Verbose $Message
            if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent)
            {

                Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message

            }

        }

        ## Check SharePoint

        if ($CheckSharePointBeforeCopy)
        {

            $Message = "Check SharePoint switch activated."
            Write-Verbose $Message
            if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent)
            {

                Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message

            }

        }
        else
        {

            $Message = "Check SharePoint switch not activated. SharePoint will not be checked before copy."
            Write-Verbose $Message
            if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent)
            {

                Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message

            }

        }

        #endregion Parameter Checks

    }
    Process
    {

        #region Export Document From ProjectWise

        $Message = "Exporting '$($ProjectWiseDocument.Name)' to '$WorkingDirectory'..."
        Write-Verbose $Message
        if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent)
        {

            Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message

        }

        try
        {

            CheckOut-PWDocuments -InputDocument $ProjectWiseDocument -CopyOut -ExportFolder $WorkingDirectory -NoReferences -ErrorAction Stop | Out-Null

        }
        catch
        {

            $Message = "Failed to export '$($DocumentToCopy.Name)' to '$WorkingDirectory'!"
            Write-Error $Message
            Write-PWPSLog -Cmdlet $Cmdlet -Level Error -Message $Message
            Write-PWPSLog -Cmdlet $Cmdlet -Level Error -Message $_.Exception.Message

            Break;

        }

        $ExportedFilePath = "$WorkingDirectory\$($ProjectWiseDocument.FileName)"
        $ExportedFileExtension = ".$($ProjectWiseDocument.FileName.Split('.')[$ProjectWiseDocument.FileName.Split('.').Length -1])"

        $Message = "Exported FilePath: '$ExportedFilePath'."
        Write-Verbose $Message
        if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent)
        {

            Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message

        }

        if ((Test-Path -LiteralPath $ExportedFilePath))
        {

            $Message = "Successfully exported '$($ProjectWiseDocument.Name)' to '$WorkingDirectory'."
            Write-Verbose $Message
            if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent)
            {

                Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message

            }

        }
        else
        {

            $Message = "'$($ProjectWiseDocument.Name)' could not be found in '$WorkingDirectory' after export!"
            Write-Error $Message
            Write-PWPSLog -Cmdlet $Cmdlet -Level Error -Message $Message

            Break;

        }

        #endregion Export Document From ProjectWise

        #region Modify Exported File

        if ($SharePointDocumentName -eq $ProjectWiseDocument.FileName.Split('.')[0])
        {

            $Message = "Target SharePoint document name matches ProjectWise file name. No modification required."
            Write-Verbose $Message
            if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent)
            {

                Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message

            }

            $ModifiedFileName = "$SharePointDocumentName$ExportedFileExtension"

            $Message = "Target FileName: '$ModifiedFileName'."
            Write-Verbose $Message
            if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent)
            {

                Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message

            }

            $ModifiedFilePath = "$WorkingDirectory\$ModifiedFileName"

            $Message = "Target FilePath: '$ModifiedFilePath'."
            Write-Verbose $Message
            if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent)
            {

                Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message

            }

        }
        else
        {

            $Message = "Modifying exported file name to specified name..."
            Write-Verbose $Message
            if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent)
            {

                Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message

            }

            $ModifiedFileName = "$SharePointDocumentName$ExportedFileExtension"

            $Message = "Modified FileName: '$ModifiedFileName'."
            Write-Verbose $Message
            if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent)
            {

                Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message

            }

            $ModifiedFilePath = "$WorkingDirectory\$ModifiedFileName"

            $Message = "Modified FilePath: '$ModifiedFilePath'."
            Write-Verbose $Message
            if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent)
            {

                Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message

            }

            try
            {

                if ((Test-Path -LiteralPath $ModifiedFilePath))
                {

                    $Message = "File name matching specified name ($ModifiedFileName) already exists in '$WorkingDirectory'."
                    Write-Warning $Message
                    Write-PWPSLog -Cmdlet $Cmdlet -Level Warn -Message $Message


                    $Message = "Overwriting existing file with latest version..."
                    Write-Warning $Message
                    Write-PWPSLog -Cmdlet $Cmdlet -Level Warn -Message $Message

                    Remove-Item -LiteralPath $ModifiedFilePath -Force

                    Rename-Item -LiteralPath $ExportedFilePath -NewName $ModifiedFileName

                }
                else
                {

                    Rename-Item -LiteralPath $ExportedFilePath -NewName $ModifiedFileName

                }

            }
            catch
            {

                $Message = "Failed to rename file!"
                Write-Error $Message
                Write-PWPSLog -Cmdlet $Cmdlet -Level Error -Message $Message
                Write-PWPSLog -Cmdlet $Cmdlet -Level Error -Message $_.Exception.Message

                Break;

            }

            $Message = "Exported file successfully renamed to '$ModifiedFileName'."
            Write-Verbose $Message
            if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent)
            {

                Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message

            }

        }

        #endregion Modify Exported File

        #region Check SharePoint For File

        if ($CheckSharePointBeforeCopy)
        {

            $Message = "Checking if file exists in SharePoint..."
            Write-Verbose $Message
            if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent)
            {

                Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message

            }

            $i = 0
            do
            {

                try
                {

                    $i++
                    $ExistingFile = Get-PnPFile -Url "$SharePointFolderPath/$ModifiedFileName" -Connection $SharePointConnection -ErrorAction Stop

                }
                catch
                {

                    $Message = "Failed to find $($ProjectWiseDocument.Name) in SharePoint on attempt $i."
                    Write-Warning $Message
                    Write-PWPSLog -Cmdlet $Cmdlet -Level Warn -Message $Message
                    Write-PWPSLog -Cmdlet $Cmdlet -Level Warn -Message $_.Exception.Message


                }

                Start-Sleep -Seconds 3

            }
            while (!($ExistingFile) -and $i -lt 3)


            if ($ExistingFile)
            {

                $Message = "'$ModifiedFileName' already exists in SharePoint. Updating with latest version..."
                Write-Verbose $Message
                if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent)
                {

                    Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message

                }

            }
            else
            {

                $Message = "'$ModifiedFileName' does not exist in SharePoint. Creating new file..."
                Write-Verbose $Message
                if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent)
                {

                    Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message

                }

            }

        }

        #endregion Check SharePoint For File

        #region Upload File To SharePoint

        $Message = "Uploading file to SharePoint..."
        Write-Verbose $Message
        if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent)
        {

            Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message

        }

        $i = 0
        do
        {

            try
            {

                $i++

                if ($SharePointMetadata)
                {

                    $SharePointFile = Add-PnPFile -Path $ModifiedFilePath -Folder $SharePointFolderPath -Connection $SharePointConnection -Values $SharePointMetadata -ErrorAction Stop

                }
                else
                {

                    $SharePointFile = Add-PnPFile -Path $ModifiedFilePath -Folder $SharePointFolderPath -Connection $SharePointConnection -ErrorAction Stop

                }

            }
            catch
            {

                $Message = "Failed to upload $($ProjectWiseDocument.Name) to SharePoint on attempt $i."
                Write-Warning $Message
                Write-PWPSLog -Cmdlet $Cmdlet -Level Warn -Message $Message
                Write-PWPSLog -Cmdlet $Cmdlet -Level Warn -Message $_.Exception.Message


            }

            Start-Sleep -Seconds 3

        }
        while (!($SharePointFile) -and $i -lt $NumberOfAttempts)


        if ($SharePointFile)
        {

            $Message = "Successfully uploaded file to SharePoint on attempt $i."
            Write-Verbose $Message
            if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent)
            {

                Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message

            }

        }
        else
        {

            $Message = "Failed to upload $($ProjectWiseDocument.Name) to SharePoint after $NumberOfAttempts attempts."
            Write-Error $Message
            Write-PWPSLog -Cmdlet $Cmdlet -Level Error -Message $Message

        }

        #endregion Upload File To SharePoint

        #region Remove Temporary File

        $Message = "Removing temporary file '$ModifiedFileName' from '$WorkingDirectory'..."
        Write-Verbose $Message
        if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent)
        {

            Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message

        }

        try
        {

            Remove-Item -LiteralPath $ModifiedFilePath -Force

        }
        catch
        {


            $Message = "Failed to remove temporary file from working directory!"
            Write-Error $Message
            Write-PWPSLog -Cmdlet $Cmdlet -Level Error -Message $Message
            Write-PWPSLog -Cmdlet $Cmdlet -Level Error -Message $_.Exception.Message

            break;

        }

        $Message = "Successfully removed temporary file from working directory."
        Write-Verbose $Message
        if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent)
        {

            Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message

        }

        #endregion Remove Temporary File

    }
    End
    {

        #region Return SharePoint File

        if ($SharePointFile)
        {

            $Properties = @{

                Name = $SharePointFile.Name;
                URL = "$($SharePointConnection.Url)/$SharePointFolderPath/$($SharePointFile.Name)"

            }

            $OutputObject = New-Object -TypeName PSObject -Property $Properties

            Write-Output $OutputObject

        }

        #endregion Return SharePoint File

    }

}

function Get-SharePointListItem
{

    <#
            .SYNOPSIS
            Opens a connection to a specified SharePoint2013 server.
            .DESCRIPTION
            This function is a simplified wrapper for the login cmdlet in the SharePointPnPPowerShell2013 module. It does not provide the same ability to change granular settings. The login is placed in a loop, as the SharePoint module often fails on the first few attempts, but succeeds after - this pattern is common to all the SharePoint2013 wrapper functions.
            .PARAMETER SharePointConnection
            SharePoint connection object. (Generated using New-SharePointConnection)
            .PARAMETER SharePointList
            Name of target SharePoint list
            .PARAMETER NumberOfAttemps
            Integer value for number of attempts before returning a failure.
            .EXAMPLE
            This example will return the list items from the SharePoint list 'Target List'.
            Get-SharePointListItem -SharePointConnection $SharePointConnection -SharePointList 'Target List' -NumberOfAttempts 10 -Verbose
 
    #>

    [CmdletBinding()]

    Param
    (

        [Parameter(Mandatory=$true)]
        [ValidateNotNullOrEmpty()]
        [SharePointPnP.PowerShell.Commands.Base.SPOnlineConnection]$SharePointConnection,

        [Parameter(Mandatory=$true)]
        [ValidateNotNullOrEmpty()]
        [string]$SharePointList,

        [Parameter(Mandatory=$false)]
        [int]$NumberOfAttempts = 20

    )

    Begin
    {

        #region Startup

        $Cmdlet = 'Get-SharePointListItem'

        #endregion Startup

        #region Parameter Checks

        ## SharePoint URL

        $Message = "SharePoint URL is '$($SharePointConnection.URL)'."
        Write-Verbose $Message
        if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent)
        {

            Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message

        }

        ## Number of attempts

        $Message = "Number of attempts set to $NumberOfAttempts."
        Write-Verbose $Message
        if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent)
        {

            Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message

        }

        #endregion Parameter Checks

    }
    Process
    {

        #region Return SharePoint List Items

        $Message = "Returning items in SharePoint list '$SharePointList'..."
        Write-Verbose $Message
        if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent)
        {

            Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message

        }

        $i = 0
        do
        {

            try
            {

                $i++
                $SharePointListItems = Get-PnPListItem -List $SharePointList -Connection $SharePointConnection -ErrorAction Stop

            }
            catch
            {

                $Message = "Failed to return SharePoint list items on attempt $i."
                Write-Warning $Message
                Write-PWPSLog -Cmdlet $Cmdlet -Level Warn -Message $Message
                Write-PWPSLog -Cmdlet $Cmdlet -Level Warn -Message $_.Exception.Message


            }

            Start-Sleep -Seconds 3

        }
        while (!($SharePointListItems) -and $i -lt $NumberOfAttempts)


        if ($SharePointListItems)
        {

            $Message = "Successfully returned SharePoint list items on attempt $i."
            Write-Verbose $Message
            if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent)
            {

                Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message

            }

        }
        else
        {

            $Message = "Failed to return SharePoint list items after $NumberOfAttempts attempts!"
            Write-Error $Message
            Write-PWPSLog -Cmdlet $Cmdlet -Level Error -Message $Message

            break;

        }

        #endregion Return SharePoint List Items

    }
    End
    {

        #region Return SharePoint List Items

        if ($SharePointListItems)
        {

            Write-Output $SharePointListItems

        }

        #endregion Return SharePoint List Items

    }

}

function New-SharePointConnection
{

    <#
            .SYNOPSIS
            Opens a connection to a specified SharePoint server.
            .DESCRIPTION
            This function is a simplified wrapper for the login cmdlet in the SharePointPnPPowerShell* module. It does not provide the same ability to change granular settings. The login is placed in a loop, as the SharePoint module often fails on the first few attempts, but succeeds after - this pattern is common to all the SharePoint2013 wrapper functions.
            .PARAMETER SharePointURL
            The URL of the SharePoint server. ('https://my-sharepoint-server.com')
            .PARAMETER Credentials
            PSCredential object containing the username and login for the SharePoint server. $Credentials = New-Object -TypeName PSCredential -ArgumentList ('UserName',(Read-Host -Prompt "Enter password" -AsSecureString))
            .PARAMETER SharePointVersion
            Version of SharePoint being used. (2013/2016/365)
            .PARAMETER NumberOfAttemps
            Integer value for number of attempts before returning a failure. Default number is 20.
            .EXAMPLE
            This example will open a connection to the specified SharePoint2013 server.
            New-SharePoint2013Connection -SharePointURL 'https://my-sharepoint-server.com' -Credentials $Credentials -SharePointVersion 2013 -Verbose
 
    #>

    [CmdletBinding()]

    Param
    (

        [Parameter(Mandatory=$true)]
        [ValidateNotNullOrEmpty()]
        [string]$SharePointURL,

        [Parameter(Mandatory=$true)]
        [ValidateNotNullOrEmpty()]
        [System.Management.Automation.PSCredential]$Credentials,

        [Parameter(Mandatory=$true)]
        [ValidateNotNullOrEmpty()]
        [ValidateSet("2013","2016","365")]
        [string]$SharePointVersion,

        [Parameter(Mandatory=$false)]
        [int]$NumberOfAttempts = 20

    )

    Begin
    {

        #region Startup

        $Cmdlet = 'New-SharePointConnection'

        #endregion Startup

        #region Requirements

        ## Import SharePointPnPPowerShell

        switch ($SharePointVersion)
        {
            "2013"
            {

                $Message = "Checking for SharePointPnPPowerShell2013 module..."
                Write-Verbose $Message
                if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent)
                {

                    Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message

                }

                try
                {

                    if (!($Module = Get-Module -Name SharePointPnPPowerShell2013))
                    {

                        $Message = "Importing SharePointPnPPowerShell2013 module..."
                        Write-Verbose $Message
                        if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent)
                        {

                            Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message

                        }

                        Import-Module -Name SharePointPnPPowerShell2013 -ErrorAction Stop

                        $Module = Get-Module -Name SharePointPnPPowerShell2013

                    }

                }
                catch
                {

                    $Message = "$($PSItem.Exception.Message)"
                    Write-Error $Message
                    Write-PWPSLog -Cmdlet $Cmdlet -Message $Message -Level Error

                    $Message = "Error importing SharePointPnPPowerShell2013 Module."
                    Write-Error $Message
                    Write-PWPSLog -Cmdlet $Cmdlet -Message $Message -Level Error

                    break;

                }

            }
            "2016"
            {

                $Message = "Checking for SharePointPnPPowerShell2016 module..."
                Write-Verbose $Message
                if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent)
                {

                    Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message

                }

                try
                {

                    if (!($Module = Get-Module -Name SharePointPnPPowerShell2016))
                    {

                        $Message = "Importing SharePointPnPPowerShell2016 module..."
                        Write-Verbose $Message
                        if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent)
                        {

                            Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message

                        }

                        Import-Module -Name SharePointPnPPowerShell2016 -ErrorAction Stop

                        $Module = Get-Module -Name SharePointPnPPowerShell2016

                    }

                }
                catch
                {

                    $Message = "$($PSItem.Exception.Message)"
                    Write-Error $Message
                    Write-PWPSLog -Cmdlet $Cmdlet -Message $Message -Level Error

                    $Message = "Error importing SharePointPnPPowerShell2016 Module."
                    Write-Error $Message
                    Write-PWPSLog -Cmdlet $Cmdlet -Message $Message -Level Error

                    break;

                }

            }
            "365"
            {

                $Message = "Checking for SharePointPnPPowerShellOnline module..."
                Write-Verbose $Message
                if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent)
                {

                    Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message

                }

                try
                {

                    if (!($Module = Get-Module -Name SharePointPnPPowerShellOnline))
                    {

                        $Message = "Importing SharePointPnPPowerShellOnline module..."
                        Write-Verbose $Message
                        if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent)
                        {

                            Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message

                        }

                        Import-Module -Name SharePointPnPPowerShellOnline -ErrorAction Stop

                        $Module = Get-Module -Name SharePointPnPPowerShellOnline

                    }

                }
                catch
                {

                    $Message = "$($PSItem.Exception.Message)"
                    Write-Error $Message
                    Write-PWPSLog -Cmdlet $Cmdlet -Message $Message -Level Error

                    $Message = "Error importing SharePointPnPPowerShellOnline Module."
                    Write-Error $Message
                    Write-PWPSLog -Cmdlet $Cmdlet -Message $Message -Level Error

                    break;

                }


            }
        }

        if (!($Module))
        {

            break;

        }

        #endregion Requirements

        #region Parameter Checks

        ## SharePointServer

        $Message = "SharePoint server is $SharePointURL."
        Write-Verbose $Message
        if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent)
        {

            Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message

        }

        ## Number of attempts

        $Message = "Number of attempts set to $NumberOfAttempts."
        Write-Verbose $Message
        if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent)
        {

            Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message

        }

        #endregion Parameter Checks

    }
    Process
    {

        #region Login to SharePoint

        $Message = "Logging into SharePoint..."
        if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent)
        {

            Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message

        }
        Write-Verbose $Message

        $i = 0
        do
        {

            try
            {

                $i++
                $SharePointConnection = Connect-PnPOnline -Url $SharePointURL -Credentials $Credentials -ReturnConnection -ErrorAction Stop

            }
            catch
            {

                $Message = "Failed to open SharePoint connection on attempt $i."
                Write-Warning $Message
                Write-PWPSLog -Cmdlet $Cmdlet -Level Warn -Message $Message
                Write-PWPSLog -Cmdlet $Cmdlet -Level Warn -Message $_.Exception.Message

            }

            Start-Sleep -Seconds 3

        }
        while (!($SharePointConnection) -and $i -lt $NumberOfAttempts)

        #endregion Login to SharePoint

    }
    End
    {

        #region Return Connection

        if (!($SharePointConnection))
        {

            $Message = "Failed to open SharePoint connection after $NumberOfAttempts attempts!"
            Write-Error $Message
            Write-PWPSLog -Cmdlet $Cmdlet -Level Error -Message $Message
            break;

        }
        else
        {

            $Message = "Successfully opened SharePoint connection to $($SharePointConnection.URL) on attempt $i."
            if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent)
            {

                Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message

            }

            Write-Verbose $Message

            Write-Output $SharePointConnection

        }

        #endregion Return Connection

    }

}

#endregion SharePoint


# SIG # Begin signature block
# MIIkoQYJKoZIhvcNAQcCoIIkkjCCJI4CAQExDzANBglghkgBZQMEAgEFADB5Bgor
# BgEEAYI3AgEEoGswaTA0BgorBgEEAYI3AgEeMCYCAwEAAAQQH8w7YFlLCE63JNLG
# KX7zUQIBAAIBAAIBAAIBAAIBADAxMA0GCWCGSAFlAwQCAQUABCDnXjGr7erAHnnq
# 89ZXTO6Sxr4D3Clb8xg15yDazHMcvaCCCn8wggUwMIIEGKADAgECAhAECRgbX9W7
# ZnVTQ7VvlVAIMA0GCSqGSIb3DQEBCwUAMGUxCzAJBgNVBAYTAlVTMRUwEwYDVQQK
# EwxEaWdpQ2VydCBJbmMxGTAXBgNVBAsTEHd3dy5kaWdpY2VydC5jb20xJDAiBgNV
# BAMTG0RpZ2lDZXJ0IEFzc3VyZWQgSUQgUm9vdCBDQTAeFw0xMzEwMjIxMjAwMDBa
# Fw0yODEwMjIxMjAwMDBaMHIxCzAJBgNVBAYTAlVTMRUwEwYDVQQKEwxEaWdpQ2Vy
# dCBJbmMxGTAXBgNVBAsTEHd3dy5kaWdpY2VydC5jb20xMTAvBgNVBAMTKERpZ2lD
# ZXJ0IFNIQTIgQXNzdXJlZCBJRCBDb2RlIFNpZ25pbmcgQ0EwggEiMA0GCSqGSIb3
# DQEBAQUAA4IBDwAwggEKAoIBAQD407Mcfw4Rr2d3B9MLMUkZz9D7RZmxOttE9X/l
# qJ3bMtdx6nadBS63j/qSQ8Cl+YnUNxnXtqrwnIal2CWsDnkoOn7p0WfTxvspJ8fT
# eyOU5JEjlpB3gvmhhCNmElQzUHSxKCa7JGnCwlLyFGeKiUXULaGj6YgsIJWuHEqH
# CN8M9eJNYBi+qsSyrnAxZjNxPqxwoqvOf+l8y5Kh5TsxHM/q8grkV7tKtel05iv+
# bMt+dDk2DZDv5LVOpKnqagqrhPOsZ061xPeM0SAlI+sIZD5SlsHyDxL0xY4PwaLo
# LFH3c7y9hbFig3NBggfkOItqcyDQD2RzPJ6fpjOp/RnfJZPRAgMBAAGjggHNMIIB
# yTASBgNVHRMBAf8ECDAGAQH/AgEAMA4GA1UdDwEB/wQEAwIBhjATBgNVHSUEDDAK
# BggrBgEFBQcDAzB5BggrBgEFBQcBAQRtMGswJAYIKwYBBQUHMAGGGGh0dHA6Ly9v
# Y3NwLmRpZ2ljZXJ0LmNvbTBDBggrBgEFBQcwAoY3aHR0cDovL2NhY2VydHMuZGln
# aWNlcnQuY29tL0RpZ2lDZXJ0QXNzdXJlZElEUm9vdENBLmNydDCBgQYDVR0fBHow
# eDA6oDigNoY0aHR0cDovL2NybDQuZGlnaWNlcnQuY29tL0RpZ2lDZXJ0QXNzdXJl
# ZElEUm9vdENBLmNybDA6oDigNoY0aHR0cDovL2NybDMuZGlnaWNlcnQuY29tL0Rp
# Z2lDZXJ0QXNzdXJlZElEUm9vdENBLmNybDBPBgNVHSAESDBGMDgGCmCGSAGG/WwA
# AgQwKjAoBggrBgEFBQcCARYcaHR0cHM6Ly93d3cuZGlnaWNlcnQuY29tL0NQUzAK
# BghghkgBhv1sAzAdBgNVHQ4EFgQUWsS5eyoKo6XqcQPAYPkt9mV1DlgwHwYDVR0j
# BBgwFoAUReuir/SSy4IxLVGLp6chnfNtyA8wDQYJKoZIhvcNAQELBQADggEBAD7s
# DVoks/Mi0RXILHwlKXaoHV0cLToaxO8wYdd+C2D9wz0PxK+L/e8q3yBVN7Dh9tGS
# dQ9RtG6ljlriXiSBThCk7j9xjmMOE0ut119EefM2FAaK95xGTlz/kLEbBw6RFfu6
# r7VRwo0kriTGxycqoSkoGjpxKAI8LpGjwCUR4pwUR6F6aGivm6dcIFzZcbEMj7uo
# +MUSaJ/PQMtARKUT8OZkDCUIQjKyNookAv4vcn4c10lFluhZHen6dGRrsutmQ9qz
# sIzV6Q3d9gEgzpkxYz0IGhizgZtPxpMQBvwHgfqL2vmCSfdibqFT+hKUGIUukpHq
# aGxEMrJmoecYpJpkUe8wggVHMIIEL6ADAgECAhAPKH/gevzFDApWjQefHyxKMA0G
# CSqGSIb3DQEBCwUAMHIxCzAJBgNVBAYTAlVTMRUwEwYDVQQKEwxEaWdpQ2VydCBJ
# bmMxGTAXBgNVBAsTEHd3dy5kaWdpY2VydC5jb20xMTAvBgNVBAMTKERpZ2lDZXJ0
# IFNIQTIgQXNzdXJlZCBJRCBDb2RlIFNpZ25pbmcgQ0EwHhcNMjEwNDE1MDAwMDAw
# WhcNMjQwNzEyMjM1OTU5WjCBhDELMAkGA1UEBhMCVVMxFTATBgNVBAgTDFBlbm5z
# eWx2YW5pYTEOMAwGA1UEBxMFRXh0b24xJjAkBgNVBAoTHUJlbnRsZXkgU3lzdGVt
# cywgSW5jb3Jwb3JhdGVkMSYwJAYDVQQDEx1CZW50bGV5IFN5c3RlbXMsIEluY29y
# cG9yYXRlZDCCASIwDQYJKoZIhvcNAQEBBQADggEPADCCAQoCggEBALtslQRBHGgs
# /1qa7w9RtQ0aEfJni5M3GsoKm/0KZd4Blapo9YzqFS0GwI77XpZ4/5Xqw68ufUCR
# gswv/8Dsxz/lqHEARqjOeYDWxl8N+I8OSeEMsHs4/sYpwBnG2jVWk+XaapF7cITd
# kaT9CpLY59OuHBOoJ1znag71J1LCyzPZJocLtO1dg7IA2xdpL01iKbdz7YRi3BGs
# uFw8zua70nLziy2rNS4OlTNkj0Y2GJcbk3XJY+WlU5/Hh+AtqQaCAnGftL+YRAOX
# rekEVRqGMWSxwBhLdnptEm3SJ4WOsD43Xk/Im+yNShaqwEr3q4N90dDce7GH18++
# O5sxhkY1Px0CAwEAAaOCAcQwggHAMB8GA1UdIwQYMBaAFFrEuXsqCqOl6nEDwGD5
# LfZldQ5YMB0GA1UdDgQWBBRMYZ/p25NaSIh+FsgvOSh3KuFP2TAOBgNVHQ8BAf8E
# BAMCB4AwEwYDVR0lBAwwCgYIKwYBBQUHAwMwdwYDVR0fBHAwbjA1oDOgMYYvaHR0
# cDovL2NybDMuZGlnaWNlcnQuY29tL3NoYTItYXNzdXJlZC1jcy1nMS5jcmwwNaAz
# oDGGL2h0dHA6Ly9jcmw0LmRpZ2ljZXJ0LmNvbS9zaGEyLWFzc3VyZWQtY3MtZzEu
# Y3JsMEsGA1UdIAREMEIwNgYJYIZIAYb9bAMBMCkwJwYIKwYBBQUHAgEWG2h0dHA6
# Ly93d3cuZGlnaWNlcnQuY29tL0NQUzAIBgZngQwBBAEwgYQGCCsGAQUFBwEBBHgw
# djAkBggrBgEFBQcwAYYYaHR0cDovL29jc3AuZGlnaWNlcnQuY29tME4GCCsGAQUF
# BzAChkJodHRwOi8vY2FjZXJ0cy5kaWdpY2VydC5jb20vRGlnaUNlcnRTSEEyQXNz
# dXJlZElEQ29kZVNpZ25pbmdDQS5jcnQwDAYDVR0TAQH/BAIwADANBgkqhkiG9w0B
# AQsFAAOCAQEA6W4LCYiD/nimog7II0cwEXvPoIculG+aV4QKlwjJE6uXs76fL2dV
# NlG4BpW0sT8uPtcDSew8x83aaKIn/VNxkp8V2nS82WKNtip7SCcKiFm/uDWNvy7O
# 03DxTT+qLi+kbFJ0S+zf8xSymEaGAyD0MqjbNr63mxmXHDNy+k+agPLVdiwNUIbU
# AXVcWTSJRyPcdz06ZMzAcBO+dVG0Vnvw7BN8+5s9Uh5Hfya5EnAdNvKpBe7qz+7Q
# Rb6YEKVDxs5KIAEwgqNjhcrT6d1IAn/SrPwdpG+S5wicbunhonn8rsLyRee2FFSF
# gzkoazE7CnK74WzN9eiGk7sfe84FfaPyKTGCGXgwghl0AgEBMIGGMHIxCzAJBgNV
# BAYTAlVTMRUwEwYDVQQKEwxEaWdpQ2VydCBJbmMxGTAXBgNVBAsTEHd3dy5kaWdp
# Y2VydC5jb20xMTAvBgNVBAMTKERpZ2lDZXJ0IFNIQTIgQXNzdXJlZCBJRCBDb2Rl
# IFNpZ25pbmcgQ0ECEA8of+B6/MUMClaNB58fLEowDQYJYIZIAWUDBAIBBQCgfDAQ
# BgorBgEEAYI3AgEMMQIwADAZBgkqhkiG9w0BCQMxDAYKKwYBBAGCNwIBBDAcBgor
# BgEEAYI3AgELMQ4wDAYKKwYBBAGCNwIBFTAvBgkqhkiG9w0BCQQxIgQgHWXpa8tj
# KHoRJUTvjVH8AautZh07Hudnewgsgm7wDpQwDQYJKoZIhvcNAQEBBQAEggEAE2fF
# t8ph5mGviY0PFxy2lAYSe5JuOVaTA+LpXuJe0BgRlaEBxZobyVRvOYZoJDGnaZAy
# lULv6bEEzgx2oQl1A5vsi7bH/bau335L+5PiZda6rt5fm7VDSa1q3c0Yb6ZXfnWL
# DplgrhD1tOF+BjwIewsVOmVJaQpmgS6b6912xJWCd/oQ9rT+JCACis7Y0Wqid2Ez
# lQ3u1A9TjYgb2YxyO/S8RhpW+YLsexAaiXPS08NueUSuIEHEk/nWetq9pu2AHbhF
# MW8CF/VUClY2IdDpSOwAfp9O6JJeedp5klavko9Xl0OsMO1v/iNx2WJXkzP9yaoz
# U67YAQsnuS9P42vryaGCF0QwghdABgorBgEEAYI3AwMBMYIXMDCCFywGCSqGSIb3
# DQEHAqCCFx0wghcZAgEDMQ8wDQYJYIZIAWUDBAIBBQAweAYLKoZIhvcNAQkQAQSg
# aQRnMGUCAQEGCWCGSAGG/WwHATAxMA0GCWCGSAFlAwQCAQUABCDJ5N33KJf3cing
# fOVZZ99DFoO/Jxg1nQqIJWddcHudEgIRAIzsSUx2GvC2szdVzeUnS1QYDzIwMjIw
# ODI5MTY0MTAzWqCCEw0wggbGMIIErqADAgECAhAKekqInsmZQpAGYzhNhpedMA0G
# CSqGSIb3DQEBCwUAMGMxCzAJBgNVBAYTAlVTMRcwFQYDVQQKEw5EaWdpQ2VydCwg
# SW5jLjE7MDkGA1UEAxMyRGlnaUNlcnQgVHJ1c3RlZCBHNCBSU0E0MDk2IFNIQTI1
# NiBUaW1lU3RhbXBpbmcgQ0EwHhcNMjIwMzI5MDAwMDAwWhcNMzMwMzE0MjM1OTU5
# WjBMMQswCQYDVQQGEwJVUzEXMBUGA1UEChMORGlnaUNlcnQsIEluYy4xJDAiBgNV
# BAMTG0RpZ2lDZXJ0IFRpbWVzdGFtcCAyMDIyIC0gMjCCAiIwDQYJKoZIhvcNAQEB
# BQADggIPADCCAgoCggIBALkqliOmXLxf1knwFYIY9DPuzFxs4+AlLtIx5DxArvur
# xON4XX5cNur1JY1Do4HrOGP5PIhp3jzSMFENMQe6Rm7po0tI6IlBfw2y1vmE8Zg+
# C78KhBJxbKFiJgHTzsNs/aw7ftwqHKm9MMYW2Nq867Lxg9GfzQnFuUFqRUIjQVr4
# YNNlLD5+Xr2Wp/D8sfT0KM9CeR87x5MHaGjlRDRSXw9Q3tRZLER0wDJHGVvimC6P
# 0Mo//8ZnzzyTlU6E6XYYmJkRFMUrDKAz200kheiClOEvA+5/hQLJhuHVGBS3BEXz
# 4Di9or16cZjsFef9LuzSmwCKrB2NO4Bo/tBZmCbO4O2ufyguwp7gC0vICNEyu4P6
# IzzZ/9KMu/dDI9/nw1oFYn5wLOUrsj1j6siugSBrQ4nIfl+wGt0ZvZ90QQqvuY4J
# 03ShL7BUdsGQT5TshmH/2xEvkgMwzjC3iw9dRLNDHSNQzZHXL537/M2xwafEDsTv
# QD4ZOgLUMalpoEn5deGb6GjkagyP6+SxIXuGZ1h+fx/oK+QUshbWgaHK2jCQa+5v
# dcCwNiayCDv/vb5/bBMY38ZtpHlJrYt/YYcFaPfUcONCleieu5tLsuK2QT3nr6ca
# KMmtYbCgQRgZTu1Hm2GV7T4LYVrqPnqYklHNP8lE54CLKUJy93my3YTqJ+7+fXpr
# AgMBAAGjggGLMIIBhzAOBgNVHQ8BAf8EBAMCB4AwDAYDVR0TAQH/BAIwADAWBgNV
# HSUBAf8EDDAKBggrBgEFBQcDCDAgBgNVHSAEGTAXMAgGBmeBDAEEAjALBglghkgB
# hv1sBwEwHwYDVR0jBBgwFoAUuhbZbU2FL3MpdpovdYxqII+eyG8wHQYDVR0OBBYE
# FI1kt4kh/lZYRIRhp+pvHDaP3a8NMFoGA1UdHwRTMFEwT6BNoEuGSWh0dHA6Ly9j
# cmwzLmRpZ2ljZXJ0LmNvbS9EaWdpQ2VydFRydXN0ZWRHNFJTQTQwOTZTSEEyNTZU
# aW1lU3RhbXBpbmdDQS5jcmwwgZAGCCsGAQUFBwEBBIGDMIGAMCQGCCsGAQUFBzAB
# hhhodHRwOi8vb2NzcC5kaWdpY2VydC5jb20wWAYIKwYBBQUHMAKGTGh0dHA6Ly9j
# YWNlcnRzLmRpZ2ljZXJ0LmNvbS9EaWdpQ2VydFRydXN0ZWRHNFJTQTQwOTZTSEEy
# NTZUaW1lU3RhbXBpbmdDQS5jcnQwDQYJKoZIhvcNAQELBQADggIBAA0tI3Sm0fX4
# 6kuZPwHk9gzkrxad2bOMl4IpnENvAS2rOLVwEb+EGYs/XeWGT76TOt4qOVo5TtiE
# WaW8G5iq6Gzv0UhpGThbz4k5HXBw2U7fIyJs1d/2WcuhwupMdsqh3KErlribVaka
# a33R9QIJT4LWpXOIxJiA3+5JlbezzMWn7g7h7x44ip/vEckxSli23zh8y/pc9+RT
# v24KfH7X3pjVKWWJD6KcwGX0ASJlx+pedKZbNZJQfPQXpodkTz5GiRZjIGvL8nvQ
# NeNKcEiptucdYL0EIhUlcAZyqUQ7aUcR0+7px6A+TxC5MDbk86ppCaiLfmSiZZQR
# +24y8fW7OK3NwJMR1TJ4Sks3KkzzXNy2hcC7cDBVeNaY/lRtf3GpSBp43UZ3Lht6
# wDOK+EoojBKoc88t+dMj8p4Z4A2UKKDr2xpRoJWCjihrpM6ddt6pc6pIallDrl/q
# +A8GQp3fBmiW/iqgdFtjZt5rLLh4qk1wbfAs8QcVfjW05rUMopml1xVrNQ6F1uAs
# zOAMJLh8UgsemXzvyMjFjFhpr6s94c/MfRWuFL+Kcd/Kl7HYR+ocheBFThIcFClY
# zG/Tf8u+wQ5KbyCcrtlzMlkI5y2SoRoR/jKYpl0rl+CL05zMbbUNrkdjOEcXW28T
# 2moQbh9Jt0RbtAgKh1pZBHYRoad3AhMcMIIGrjCCBJagAwIBAgIQBzY3tyRUfNhH
# rP0oZipeWzANBgkqhkiG9w0BAQsFADBiMQswCQYDVQQGEwJVUzEVMBMGA1UEChMM
# RGlnaUNlcnQgSW5jMRkwFwYDVQQLExB3d3cuZGlnaWNlcnQuY29tMSEwHwYDVQQD
# ExhEaWdpQ2VydCBUcnVzdGVkIFJvb3QgRzQwHhcNMjIwMzIzMDAwMDAwWhcNMzcw
# MzIyMjM1OTU5WjBjMQswCQYDVQQGEwJVUzEXMBUGA1UEChMORGlnaUNlcnQsIElu
# Yy4xOzA5BgNVBAMTMkRpZ2lDZXJ0IFRydXN0ZWQgRzQgUlNBNDA5NiBTSEEyNTYg
# VGltZVN0YW1waW5nIENBMIICIjANBgkqhkiG9w0BAQEFAAOCAg8AMIICCgKCAgEA
# xoY1BkmzwT1ySVFVxyUDxPKRN6mXUaHW0oPRnkyibaCwzIP5WvYRoUQVQl+kiPNo
# +n3znIkLf50fng8zH1ATCyZzlm34V6gCff1DtITaEfFzsbPuK4CEiiIY3+vaPcQX
# f6sZKz5C3GeO6lE98NZW1OcoLevTsbV15x8GZY2UKdPZ7Gnf2ZCHRgB720RBidx8
# ald68Dd5n12sy+iEZLRS8nZH92GDGd1ftFQLIWhuNyG7QKxfst5Kfc71ORJn7w6l
# Y2zkpsUdzTYNXNXmG6jBZHRAp8ByxbpOH7G1WE15/tePc5OsLDnipUjW8LAxE6lX
# KZYnLvWHpo9OdhVVJnCYJn+gGkcgQ+NDY4B7dW4nJZCYOjgRs/b2nuY7W+yB3iIU
# 2YIqx5K/oN7jPqJz+ucfWmyU8lKVEStYdEAoq3NDzt9KoRxrOMUp88qqlnNCaJ+2
# RrOdOqPVA+C/8KI8ykLcGEh/FDTP0kyr75s9/g64ZCr6dSgkQe1CvwWcZklSUPRR
# 8zZJTYsg0ixXNXkrqPNFYLwjjVj33GHek/45wPmyMKVM1+mYSlg+0wOI/rOP015L
# dhJRk8mMDDtbiiKowSYI+RQQEgN9XyO7ZONj4KbhPvbCdLI/Hgl27KtdRnXiYKNY
# CQEoAA6EVO7O6V3IXjASvUaetdN2udIOa5kM0jO0zbECAwEAAaOCAV0wggFZMBIG
# A1UdEwEB/wQIMAYBAf8CAQAwHQYDVR0OBBYEFLoW2W1NhS9zKXaaL3WMaiCPnshv
# MB8GA1UdIwQYMBaAFOzX44LScV1kTN8uZz/nupiuHA9PMA4GA1UdDwEB/wQEAwIB
# hjATBgNVHSUEDDAKBggrBgEFBQcDCDB3BggrBgEFBQcBAQRrMGkwJAYIKwYBBQUH
# MAGGGGh0dHA6Ly9vY3NwLmRpZ2ljZXJ0LmNvbTBBBggrBgEFBQcwAoY1aHR0cDov
# L2NhY2VydHMuZGlnaWNlcnQuY29tL0RpZ2lDZXJ0VHJ1c3RlZFJvb3RHNC5jcnQw
# QwYDVR0fBDwwOjA4oDagNIYyaHR0cDovL2NybDMuZGlnaWNlcnQuY29tL0RpZ2lD
# ZXJ0VHJ1c3RlZFJvb3RHNC5jcmwwIAYDVR0gBBkwFzAIBgZngQwBBAIwCwYJYIZI
# AYb9bAcBMA0GCSqGSIb3DQEBCwUAA4ICAQB9WY7Ak7ZvmKlEIgF+ZtbYIULhsBgu
# EE0TzzBTzr8Y+8dQXeJLKftwig2qKWn8acHPHQfpPmDI2AvlXFvXbYf6hCAlNDFn
# zbYSlm/EUExiHQwIgqgWvalWzxVzjQEiJc6VaT9Hd/tydBTX/6tPiix6q4XNQ1/t
# YLaqT5Fmniye4Iqs5f2MvGQmh2ySvZ180HAKfO+ovHVPulr3qRCyXen/KFSJ8NWK
# cXZl2szwcqMj+sAngkSumScbqyQeJsG33irr9p6xeZmBo1aGqwpFyd/EjaDnmPv7
# pp1yr8THwcFqcdnGE4AJxLafzYeHJLtPo0m5d2aR8XKc6UsCUqc3fpNTrDsdCEkP
# lM05et3/JWOZJyw9P2un8WbDQc1PtkCbISFA0LcTJM3cHXg65J6t5TRxktcma+Q4
# c6umAU+9Pzt4rUyt+8SVe+0KXzM5h0F4ejjpnOHdI/0dKNPH+ejxmF/7K9h+8kad
# dSweJywm228Vex4Ziza4k9Tm8heZWcpw8De/mADfIBZPJ/tgZxahZrrdVcA6KYaw
# mKAr7ZVBtzrVFZgxtGIJDwq9gdkT/r+k0fNX2bwE+oLeMt8EifAAzV3C+dAjfwAL
# 5HYCJtnwZXZCpimHCUcr5n8apIUP/JiW9lVUKx+A+sDyDivl1vupL0QVSucTDh3b
# NzgaoSv27dZ8/DCCBY0wggR1oAMCAQICEA6bGI750C3n79tQ4ghAGFowDQYJKoZI
# hvcNAQEMBQAwZTELMAkGA1UEBhMCVVMxFTATBgNVBAoTDERpZ2lDZXJ0IEluYzEZ
# MBcGA1UECxMQd3d3LmRpZ2ljZXJ0LmNvbTEkMCIGA1UEAxMbRGlnaUNlcnQgQXNz
# dXJlZCBJRCBSb290IENBMB4XDTIyMDgwMTAwMDAwMFoXDTMxMTEwOTIzNTk1OVow
# YjELMAkGA1UEBhMCVVMxFTATBgNVBAoTDERpZ2lDZXJ0IEluYzEZMBcGA1UECxMQ
# d3d3LmRpZ2ljZXJ0LmNvbTEhMB8GA1UEAxMYRGlnaUNlcnQgVHJ1c3RlZCBSb290
# IEc0MIICIjANBgkqhkiG9w0BAQEFAAOCAg8AMIICCgKCAgEAv+aQc2jeu+RdSjww
# IjBpM+zCpyUuySE98orYWcLhKac9WKt2ms2uexuEDcQwH/MbpDgW61bGl20dq7J5
# 8soR0uRf1gU8Ug9SH8aeFaV+vp+pVxZZVXKvaJNwwrK6dZlqczKU0RBEEC7fgvMH
# hOZ0O21x4i0MG+4g1ckgHWMpLc7sXk7Ik/ghYZs06wXGXuxbGrzryc/NrDRAX7F6
# Zu53yEioZldXn1RYjgwrt0+nMNlW7sp7XeOtyU9e5TXnMcvak17cjo+A2raRmECQ
# ecN4x7axxLVqGDgDEI3Y1DekLgV9iPWCPhCRcKtVgkEy19sEcypukQF8IUzUvK4b
# A3VdeGbZOjFEmjNAvwjXWkmkwuapoGfdpCe8oU85tRFYF/ckXEaPZPfBaYh2mHY9
# WV1CdoeJl2l6SPDgohIbZpp0yt5LHucOY67m1O+SkjqePdwA5EUlibaaRBkrfsCU
# tNJhbesz2cXfSwQAzH0clcOP9yGyshG3u3/y1YxwLEFgqrFjGESVGnZifvaAsPvo
# ZKYz0YkH4b235kOkGLimdwHhD5QMIR2yVCkliWzlDlJRR3S+Jqy2QXXeeqxfjT/J
# vNNBERJb5RBQ6zHFynIWIgnffEx1P2PsIV/EIFFrb7GrhotPwtZFX50g/KEexcCP
# orF+CiaZ9eRpL5gdLfXZqbId5RsCAwEAAaOCATowggE2MA8GA1UdEwEB/wQFMAMB
# Af8wHQYDVR0OBBYEFOzX44LScV1kTN8uZz/nupiuHA9PMB8GA1UdIwQYMBaAFEXr
# oq/0ksuCMS1Ri6enIZ3zbcgPMA4GA1UdDwEB/wQEAwIBhjB5BggrBgEFBQcBAQRt
# MGswJAYIKwYBBQUHMAGGGGh0dHA6Ly9vY3NwLmRpZ2ljZXJ0LmNvbTBDBggrBgEF
# BQcwAoY3aHR0cDovL2NhY2VydHMuZGlnaWNlcnQuY29tL0RpZ2lDZXJ0QXNzdXJl
# ZElEUm9vdENBLmNydDBFBgNVHR8EPjA8MDqgOKA2hjRodHRwOi8vY3JsMy5kaWdp
# Y2VydC5jb20vRGlnaUNlcnRBc3N1cmVkSURSb290Q0EuY3JsMBEGA1UdIAQKMAgw
# BgYEVR0gADANBgkqhkiG9w0BAQwFAAOCAQEAcKC/Q1xV5zhfoKN0Gz22Ftf3v1cH
# vZqsoYcs7IVeqRq7IviHGmlUIu2kiHdtvRoU9BNKei8ttzjv9P+Aufih9/Jy3iS8
# UgPITtAq3votVs/59PesMHqai7Je1M/RQ0SbQyHrlnKhSLSZy51PpwYDE3cnRNTn
# f+hZqPC/Lwum6fI0POz3A8eHqNJMQBk1RmppVLC4oVaO7KTVPeix3P0c2PR3WlxU
# jG/voVA9/HYJaISfb8rbII01YBwCA8sgsKxYoA5AY8WYIsGyWfVVa88nq2x2zm8j
# LfR+cWojayL/ErhULSd+2DrZ8LaHlv1b0VysGMNNn3O3AamfV6peKOK5lDGCA3Yw
# ggNyAgEBMHcwYzELMAkGA1UEBhMCVVMxFzAVBgNVBAoTDkRpZ2lDZXJ0LCBJbmMu
# MTswOQYDVQQDEzJEaWdpQ2VydCBUcnVzdGVkIEc0IFJTQTQwOTYgU0hBMjU2IFRp
# bWVTdGFtcGluZyBDQQIQCnpKiJ7JmUKQBmM4TYaXnTANBglghkgBZQMEAgEFAKCB
# 0TAaBgkqhkiG9w0BCQMxDQYLKoZIhvcNAQkQAQQwHAYJKoZIhvcNAQkFMQ8XDTIy
# MDgyOTE2NDEwM1owKwYLKoZIhvcNAQkQAgwxHDAaMBgwFgQUhQjzhlFcs9MHfba0
# t8B/G0peQd4wLwYJKoZIhvcNAQkEMSIEIEBk6RDGBBrwKeHpRdpdvwaGEeWwq65v
# NH7woabFthGzMDcGCyqGSIb3DQEJEAIvMSgwJjAkMCIEIJ2mkBXDScbBiXhFujWC
# rXDIj6QpO9tqvpwr0lOSeeY7MA0GCSqGSIb3DQEBAQUABIICAGuOKQHjWcHZM2LW
# L9mkFU+zV8V/5T/LI1q5UDk/0skSqTQa/k78Kas6VUWh9zU8H8xiFwWPwXFVdXiK
# /x01GqJQi4OQijMekExUNtsqYTanWMfnkdAiPH6Ugy6mpg3KKGPB4YEE0svWhKQn
# i3vk0Eew/pWAGh9FgeunQlrja1yzAAYO8+q860JWw+yztrXnBqEBv/RvCvS7+vRf
# R47rSNCNns4H1fCcxK6falk2aT9uynPpR7+BuBDIeiQpWQnmky/r+Q/Rw25O3R2I
# z3dVfIekOwQxP4BJaqaDyn3Ly2KeAtjL1IfUM5HDxExLVAst7iClVNi+dHePwmAJ
# rSaXnKh8jYbg/HFAylG4aWnaBIhPTK8iF7LTZRfdTUVqC/m633mGp+6T0Kk3hfsE
# CADFkB1gB+21R3WxjcnI5btbL+1GfnehawIjDv/Zv9RHrVo8Sy1u3qayJSGIzwtS
# pK5VjRqTXy5UpYXfSaWeIaFXWBDAYCqlpySI/fptHjeiFhONpZwcUOAbf6bmB7Vw
# BTbGzsYV/Q5h+YhkLWoyVhn2tCnfzbt+zSlKbhoRqRiH3B0n98DpA9QOvu3zFpvf
# mgajh4MGvp7aN+ltdwFpxQdoiYz6fn5/mI7Stnjb0pQcqphH++5QMJ0CLXiKYTxI
# YJ20Kz8M7U/aLET3BFnFMYbDPibN
# SIG # End signature block