HelperFunctions.psm1

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
}

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-Verbose -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-Verbose -Message "$($property.Name): Supported datatype <$($dataType)>"
                        }

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

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

                        # add column to DataTable
                        $dataTable.Columns.Add($dataColumn)
                        Write-Verbose -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-Verbose -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-Verbose -Message 'Value added to row as XML'
                        }

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

                # add DataRow to DataTable
                $dataTable.Rows.Add($dataRow)
                Write-Verbose -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)) 
    }
}

#region Logging

# Purpose : Write error messages to log files
# Requirements : Write permission to log directory

function Write-Log
{ 
    
    [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'  
                 
            } 
        
            'Warn'
            { 

                $LevelText = 'WARNING' 
               
            } 
            'Info'
            { 
             
                $LevelText = 'INFO' 

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

    } 
    End 
    {
        
        # Write-Verbose "$LevelText log written to $Path"

    } 

}

# Purpose : Searches a log file written using the Write-Log function, and returns entries from a specified number of days ago. Returns records from a single day only.
# Requirements : Write-Log function

function Get-PowerShellLogsFromPreviousDays
{ 

<#
  .SYNOPSIS
    Returns log entries written by Write-Log from a specified number of days ago.
  .DESCRIPTION
    Searches a log file written using the Write-Log 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 $Path

    }
    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

# Purpose : Allows user to select a folder/file using the GUI, and returns the folder/file path.

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|All files|*.*'

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

    return $FilePath
}

#endregion Windows

#region SQL

# Purpose : Bulk copies a datatable into a SQL table.
# Requirements:
# - Write-Log -Cmdlet $Cmdlet function
# - SQLPS module (Install-Module SqlServer)
# - SQL Server login credentials
# - Get-SQLDataType

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-Log -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
            Write-Log -Cmdlet $Cmdlet -Message $Message -Level Info

            try
            {

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

            }
            catch
            {

                $Message = "$($PSItem.Exception.Message)"
                Write-Error $Message
                Write-Log -Cmdlet $Cmdlet -Message $Message -Level Error
                
                $Message = "Error truncating table."
                Write-Error $Message
                Write-Log -Cmdlet $Cmdlet -Message $Message -Level Error
                
                return; 

            }
            
            $Message = "Successfully truncated table."
            Write-Verbose $Message
            Write-Log -Cmdlet $Cmdlet -Message $Message -Level Info    

        }

        #endregion Truncate Table
        
        #region Define SQL Objects

        ### Initiate Bulk Copy object

        $Message = "Initiating SQL Bulk Copy object."
        Write-Verbose $Message
        Write-Log -Cmdlet $Cmdlet -Message $Message -Level Info

        try
        {

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

        }
        catch
        {

            $Message = "$($PSItem.Exception.Message)"
            Write-Error $Message
            Write-Log -Cmdlet $Cmdlet -Message $Message -Level Error
            
            $Message = "Error initiating SQL Bulk Copy object."
            Write-Error $Message
            Write-Log -Cmdlet $Cmdlet -Message $Message -Level Error
            
            return; 

        }
        
        $Message = "Successfully initiated SQL Bulk Copy object."
        Write-Verbose $Message
        Write-Log -Cmdlet $Cmdlet -Message $Message -Level Info

        #endregion Define SQL Objects

    } 
    End 
    {
        
        #region Bulk Copy table

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

        $Message = "Copying $($Datatable.Rows.Count) rows..."
        Write-Verbose $Message
        Write-Log -Cmdlet $Cmdlet -Message $Message -Level Info

        try
        {

            $SQLBulkCopy.WriteToServer($Datatable)

        }
        catch
        {
            
            $Message = "$($PSItem.Exception.Message)"
            Write-Error $Message
            Write-Log -Cmdlet $Cmdlet -Message $Message -Level Error
            
            $Message = "Error bulk copying table."
            Write-Error $Message
            Write-Log -Cmdlet $Cmdlet -Message $Message -Level Error
            
            return; 

        }

        $Message = "Successfully performed bulk copy."
        Write-Verbose $Message
        Write-Log -Cmdlet $Cmdlet -Message $Message -Level Info

        #endregion Bulk Copy table

    } 

}

# Purpose : Returns databases in a SQL instance
# Requirements:
# - Write-Log -Cmdlet $Cmdlet function
# - SQLPS module (Install-Module SqlServer)
# - SQL Server login credentials

function Get-SQLDatabaseFunction
{ 

<#
  .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-SQLDatabaseFunction -SQLConnection $SQLConnection
  .EXAMPLE
    This example will return the database object for DATABASEONE from the SQL instance SQLONE.
    $SQLConnection = New-SQLConnection -SQLServer 'SQLONE' -WindowsAuthentication
        Get-SQLDatabaseFunction -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-SQLDatabaseFunction'

        #endregion Startup

    } 
    Process 
    { 
        
        #region Define SQL Objects

        ### Initiate SMO

        $Message = "Initiating SQL Server Management object."
        Write-Verbose $Message
        Write-Log -Cmdlet $Cmdlet -Message $Message -Level Info

        try
        {

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

        }
        catch
        {

            $Message = "$($PSItem.Exception.Message)"
            Write-Error $Message
            Write-Log -Cmdlet $Cmdlet -Message $Message -Level Error
            
            $Message = "Error initiating SQL Server object."
            Write-Error $Message
            Write-Log -Cmdlet $Cmdlet -Message $Message -Level Error
            
            return; 

        }
        
        $Message = "Successfully initiated SQL Server Management object."
        Write-Verbose $Message
        Write-Log -Cmdlet $Cmdlet -Message $Message -Level Info

        #endregion Define SQL Objects
        
        #region Database Check

        ### Check if database exists

        if ($Database)
        {

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

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

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

            }
            else
            {

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

            }
        
        }

        #endregion Database Check

    } 
    End 
    {
        
        #region Return Database

        try
        {
            
            if ($Database)
            {

                $Message = "Returning $Database..."
                Write-Verbose $Message
                Write-Log -Cmdlet $Cmdlet -Message $Message -Level Info
                
                Write-Output $SQLSMO.Databases[$Database]

            }
            else
            {

                $Message = "Returning databases..."
                Write-Verbose $Message
                Write-Log -Cmdlet $Cmdlet -Message $Message -Level Info
                
                Write-Output $SQLSMO.Databases

            }

        }
        catch
        {
            
            $Message = "Error returning database. Aborting script..."
            Write-Error $Message
            Write-Log -Cmdlet $Cmdlet -Message $Message -Level Error
            
            return; 

        }

        #endregion Return Database

    } 

}

# Purpose : Converts data types to SQL data types
# Requirements:
# - Write-Log -Cmdlet $Cmdlet function
# - SQLPS module (Install-Module SqlServer)


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
        Write-Log -Cmdlet $Cmdlet -Message $Message -Level Info

        $Message = "Input data type is '$DataType'."
        Write-Verbose $Message
        Write-Log -Cmdlet $Cmdlet -Message $Message -Level Info

        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
        Write-Log -Cmdlet $Cmdlet -Message $Message -Level Info

        #endregion Convert Data Type

    } 
    End 
    {
        
        #region Return Data Type

        if ($SQLDataType)
        {

            $Message = "Data type conversion successful."
            Write-Verbose $Message
            Write-Log -Cmdlet $Cmdlet -Message $Message -Level Info

            Write-Output -InputObject $SQLDataType

        }
        else
        {

            $Message = "Failed to covert data type."
            Write-Error $Message
            Write-Log -Cmdlet $Cmdlet -Message $Message -Level Error
            
            break;

        }

        #endregion Return Data Type

    } 

}

# Purpose : Returns tables in a specified SQL database
#
# Requirements:
# - Write-Log -Cmdlet $Cmdlet function
# - SQLPS module (Install-Module SqlServer)

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-Log -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
        Write-Log -Cmdlet $Cmdlet -Message $Message -Level Info

        try
        {
            
            if ($Database)
            {

                $SQLDBO = Get-SQLDatabaseFunction -SQLConnection $SQLConnection -Database $Database

            }
            else
            {
                
                $Database = $SQLConnection.DatabaseName

                $SQLDBO = Get-SQLDatabaseFunction -SQLConnection $SQLConnection -Database $Database

            }
            

        }
        catch
        {

            $Message = "$($PSItem.Exception.Message)"
            Write-Error $Message
            Write-Log -Cmdlet $Cmdlet -Message $Message -Level Error
            
            $Message = "Error returning SQL Database object."
            Write-Error $Message
            Write-Log -Cmdlet $Cmdlet -Message $Message -Level Error
            
            return; 

        }
        
        $Message = "Successfully returned SQL Database object."
        Write-Verbose $Message
        Write-Log -Cmdlet $Cmdlet -Message $Message -Level Info  

        #endregion Define SQL Objects
        
        #region Check Table

        if ($TableName)
        {

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

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

                $Message = "Found table '$TableName' in database '$Database'."
                Write-Verbose $Message
                Write-Log -Cmdlet $Cmdlet -Message $Message -Level Info

            }
            else
            {

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

            }
        
        }

        #endregion Check table

    } 
    End 
    {
        
        #region Return Table

        if ($TableName)
        {

            try
            {

                $Message = "Returning table '$TableName'."
                Write-Verbose $Message
                Write-Log -Cmdlet $Cmdlet -Message $Message -Level Info
                
                Write-Output $SQLDBO.Tables[$TableName] 

            }
            catch
            {
                
                $Message = "Error returning table."
                Write-Error $Message
                Write-Log -Cmdlet $Cmdlet -Message $Message -Level Error
                
                return; 

            }
        
        }
        else
        {

            try
            {

                $Message = "Returning tables in '$Database'."
                Write-Verbose $Message
                Write-Log -Cmdlet $Cmdlet -Message $Message -Level Info
                
                Write-Output $SQLDBO.Tables

            }
            catch
            {
                
                $Message = "Error returning tables."
                Write-Error $Message
                Write-Log -Cmdlet $Cmdlet -Message $Message -Level Error
                
                return; 

            }

        }

        #endregion Return Table

    } 

}

# Purpose : Opens a connection to a specified SQL server
# Requirements:
# - Write-Log -Cmdlet $Cmdlet function
# - SQLPS module (Install-Module SqlServer)
# - SQL Server login credentials

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
        Write-Log -Cmdlet $Cmdlet -Message $Message -Level Info

        try
        {
   
            if (!(Get-Module -Name SqlServer))
            {
            
                $Message = "Importing SqlServer module..."
                Write-Verbose $Message
                Write-Log -Cmdlet $Cmdlet -Message $Message -Level Info

                Import-Module -Name SqlServer -ErrorAction Stop

            }

        }
        catch
        {

            $Message = "$($PSItem.Exception.Message)"
            Write-Error $Message
            Write-Log -Cmdlet $Cmdlet -Message $Message -Level Error
            
            $Message = "Error importing SqlServer Module."
            Write-Error $Message
            Write-Log -Cmdlet $Cmdlet -Message $Message -Level Error
            
            break;  

        }

        #endregion Requirements

        #region Parameter Checks

        if ($WindowsAuthentication)
        {

            $Message = "Authentication mode: Windows."
            Write-Verbose $Message
            Write-Log -Cmdlet $Cmdlet -Message $Message -Level Info

        }
        else
        {

            $Message = "Authentication mode: SQL Server."
            Write-Verbose $Message
            Write-Log -Cmdlet $Cmdlet -Message $Message -Level Info

            ### No password provided

            if (!($Password))
            {

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

            }

            ### No username provided

            if (!($Username))
            {

                $Message = "No username provided for SQL Server authentication."
                Write-Error $Message
                Write-Log -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
            Write-Log -Cmdlet $Cmdlet -Message $Message -Level Info

            try
            {

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

            }
            catch
            {

                $Message = "$($PSItem.Exception.Message)"
                Write-Error $Message
                Write-Log -Cmdlet $Cmdlet -Message $Message -Level Error
            
                $Message = "Error establishing connection to '$SQLServer'."
                Write-Error $Message
                Write-Log -Cmdlet $Cmdlet -Message $Message -Level Error
            
                break;  

            }

        }
        else
        {

            $Message = "Establishing connection to '$SQLServer' as '$Username' using SQL Server authentication."
            Write-Verbose $Message
            Write-Log -Cmdlet $Cmdlet -Message $Message -Level Info

            try
            {

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

            }
            catch
            {

                $Message = "$($PSItem.Exception.Message)"
                Write-Error $Message
                Write-Log -Cmdlet $Cmdlet -Message $Message -Level Error
            
                $Message = "Error establishing connection to '$SQLServer' as '$Username'."
                Write-Error $Message
                Write-Log -Cmdlet $Cmdlet -Message $Message -Level Error
            
                break;  

            }

        }
        
        $Message = "Successfully established connection to '$SQLServer'."
        Write-Verbose $Message
        Write-Log -Cmdlet $Cmdlet -Message $Message -Level Info

        #endregion Establish Connection

        #region Target Database

        if ($Database)
        {

            $Message = "Setting target database to '$Database'."
            Write-Verbose $Message
            Write-Log -Cmdlet $Cmdlet -Message $Message -Level Info

            try
            {

                $SQLServerConnection.DatabaseName = $Database

            }
            catch
            {
                
                $Message = "Error setting target database."
                Write-Error $Message
                Write-Log -Cmdlet $Cmdlet -Message $Message -Level Error
                
                break; 

            }

        }

        #endregion Target Database

        #region Open Connection

        $Message = "Opening connection..."
        Write-Verbose $Message
        Write-Log -Cmdlet $Cmdlet -Message $Message -Level Info

        try
        {
        
            $SQLServerConnection.Connect()

        }
        catch
        {
            
            $Message = "Error opening connection. Check login credentials and inputs. Aborting script..."
            Write-Error $Message
            Write-Log -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
            Write-Log -Cmdlet $Cmdlet -Message $Message -Level Info

            Write-Output -InputObject $SQLServerConnection

        }
        else
        {

            $Message = "Failed to open SQL connection."
            Write-Error $Message
            Write-Log -Cmdlet $Cmdlet -Message $Message -Level Error
            
            break;

        }

        #endregion Return Connection

    } 

}

# Purpose : Creates a table in a specified database
# Requirements:
# - Write-Log -Cmdlet $Cmdlet function
# - SQLPS module (Install-Module SqlServer)
# - SQL Server login credentials

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-Log -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
        Write-Log -Cmdlet $Cmdlet -Message $Message -Level Info

        try
        {

            if ($Database)
            {

                $SQLDBO = Get-SQLDatabaseFunction -SQLConnection $SQLConnection -Database $Database

            }
            else
            {

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

            }

        }
        catch
        {

            $Message = "$($PSItem.Exception.Message)"
            Write-Error $Message
            Write-Log -Cmdlet $Cmdlet -Message $Message -Level Error
            
            $Message = "Error returning SQL Database object."
            Write-Error $Message
            Write-Log -Cmdlet $Cmdlet -Message $Message -Level Error
            
            return; 

        }
        
        $Message = "Successfully returned $($SQLDBO.Name)."
        Write-Verbose $Message
        Write-Log -Cmdlet $Cmdlet -Message $Message -Level Info   

        #endregion Define SQL Objects
        
        #region Define Table

        ### Define table object

        $Message = "Defining SQL Table object..."
        Write-Verbose $Message
        Write-Log -Cmdlet $Cmdlet -Message $Message -Level Info

        try
        {

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

        }
        catch
        {

            $Message = "$($PSItem.Exception.Message)"
            Write-Error $Message
            Write-Log -Cmdlet $Cmdlet -Message $Message -Level Error
        
            $Message = "Error defining SQL Table object."
            Write-Error $Message
            Write-Log -Cmdlet $Cmdlet -Message $Message -Level Error
        
            return;  

        }

        $Message = "Successfully defined table '$($SQLTable.Name)'."
        Write-Verbose $Message
        Write-Log -Cmdlet $Cmdlet -Message $Message -Level Info

        ### Add columns to table

        $Message = "Preparing to add $($DataTable.Columns.Count) columns to '$($SQLTable.Name)'..."
        Write-Verbose $Message
        Write-Log -Cmdlet $Cmdlet -Message $Message -Level Info

        foreach ($Column in $Datatable.Columns)
        {
            
            ### Convert data type

            try
            {

                $Message = "Converting data type for column '$($Column.ColumnName)'."
                Write-Verbose $Message
                Write-Log -Cmdlet $Cmdlet -Message $Message -Level Info

                $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-Log -Cmdlet $Cmdlet -Message $Message -Level Error
            
                $Message = "Error converting data type."
                Write-Error $Message
                Write-Log -Cmdlet $Cmdlet -Message $Message -Level Error
            
                return;  

            }
            
            ### Define column

            try
            {

                $Message = "Defining column object '$($Column.ColumnName)'."
                Write-Verbose $Message
                Write-Log -Cmdlet $Cmdlet -Message $Message -Level Info

                $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-Log -Cmdlet $Cmdlet -Message $Message -Level Error
            
                $Message = "Error defining column object."
                Write-Error $Message
                Write-Log -Cmdlet $Cmdlet -Message $Message -Level Error
            
                return; 

            }            
        
            
            ### Add column

            try
            {

                $Message = "Adding column object '$($Column.ColumnName)' to table object '$($SQLTable.Name)'."
                Write-Verbose $Message
                Write-Log -Cmdlet $Cmdlet -Message $Message -Level Info

                $SQLTable.Columns.Add($SQLColumn)

            }
            catch
            {

                $Message = "$($PSItem.Exception.Message)"
                Write-Error $Message
                Write-Log -Cmdlet $Cmdlet -Message $Message -Level Error
            
                $Message = "Error adding column object."
                Write-Error $Message
                Write-Log -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
            Write-Log -Cmdlet $Cmdlet -Message $Message -Level Info
            
            $SQLTable.Create()  

        }
        catch
        {

            $Message = "$($PSItem.Exception.Message)"
            Write-Error $Message
            Write-Log -Cmdlet $Cmdlet -Message $Message -Level Error
            
            $Message = "Error adding table object."
            Write-Error $Message
            Write-Log -Cmdlet $Cmdlet -Message $Message -Level Error
            
            return; 

        }

        #endregion Create Table

    } 

}

# Purpose : Truncates a specified SQL table.
# Requirements:
# - Write-Log -Cmdlet $Cmdlet function
# - SQLPS module (Install-Module SqlServer)
# - SQL Server login credentials

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-Log -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
        Write-Log -Cmdlet $Cmdlet -Message $Message -Level Info

        try
        {

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

        }
        catch
        {

            $Message = "$($PSItem.Exception.Message)"
            Write-Error $Message
            Write-Log -Cmdlet $Cmdlet -Message $Message -Level Error
            
            $Message = "Error returning table object."
            Write-Error $Message
            Write-Log -Cmdlet $Cmdlet -Message $Message -Level Error
            
            return; 

        }
        
        $Message = "Successfully returned table object."
        Write-Verbose $Message
        Write-Log -Cmdlet $Cmdlet -Message $Message -Level Info

        #endregion Return SQL Table

    } 
    End 
    {
        
        #region Truncate table

        $Message = "Performing truncate on table '$TableName'..."
        Write-Verbose $Message
        Write-Log -Cmdlet $Cmdlet -Message $Message -Level Info

        try
        {

            $SQLTable.TruncateData()

        }
        catch
        {
            
            $Message = "$($PSItem.Exception.Message)"
            Write-Error $Message
            Write-Log -Cmdlet $Cmdlet -Message $Message -Level Error
            
            $Message = "Error truncating table."
            Write-Error $Message
            Write-Log -Cmdlet $Cmdlet -Message $Message -Level Error
            
            return; 

        }

        $Message = "Successfully truncated table."
        Write-Verbose $Message
        Write-Log -Cmdlet $Cmdlet -Message $Message -Level Info

        #endregion Truncate

    } 

}

#endregion SQL

#region Reporting

# Purpose : Returns audit trail records from previous days
# Requirements:
# - Write-Log function

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
        Write-Log -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-Log -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
        Write-Log -Cmdlet $Cmdlet -Level Info -Message $Message

        if ($IncludeIntermediateDays)
        {

            $Message = "Include intermediate days switch activated!"
            Write-Verbose $Message
            Write-Log -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
            Write-Log -Cmdlet $Cmdlet -Level Info -Message $Message

        }
        
        #endregion Checks

    } 
    Process 
    { 
        
        #region Return Records

        $Message = "Returning audit trail records..."
        Write-Verbose $Message
        Write-Log -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-Log -Cmdlet $Cmdlet -Level Error -Message $Message
            break; 

        }
        
        $Message = "Returned $AuditTrailRecordsCount audit trail records."
        Write-Verbose $Message
        Write-Log -Cmdlet $Cmdlet -Level Info -Message $Message

        #endregion Return Records

        #region Add Properties

        if ($AddDatasourceInformation)
        {
            
            $Message = "Adding properties to audit trail records..."
            Write-Verbose $Message
            Write-Log -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
                    Write-Log -Cmdlet $Cmdlet -Level Info -Message $Message
            
                }
                catch
                {

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

                    break;

                }

            }
            elseif ($AuditTrailRecordsCount -eq 0)
            {
            
                $Message = "No audit trail records returned."
                Write-Warning $Message
                Write-Log -Cmdlet $Cmdlet -Level Warn -Message $Message
            
            }
            else
            {
            
                Write-Log -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
        Write-Log -Cmdlet $Cmdlet -Level Info -Message $Message

        if ($AuditTrailRecordsCount -gt 0)
        {

            Write-Output $AuditTrailRecords

        }

        #endregion Write Output

    } 

}

#endregion Reporting

#region SharePoint

# Purpose : Copies a document from ProjectWise to SharePoint
# Requirements:
# - Write-Log function
# - SharePointPnPPowerShell* module (Install-Module SharePointPnPPowerShell2013 or Install-Module SharePointPnPPowerShell2016 or Install-Module SharePointPnPPowerShellOnline)

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
        Write-Log -Cmdlet $Cmdlet -Level Info -Message $Message

        ## Working Directory

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

            $Message = "Found working directory."
            Write-Verbose $Message
            Write-Log -Cmdlet $Cmdlet -Level Info -Message $Message

        }
        else
        {

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

            break;

        }

        ## Number of attempts

        $Message = "Number of attempts set to $NumberOfAttempts."
        Write-Verbose $Message
        Write-Log -Cmdlet $Cmdlet -Level Info -Message $Message

        ## SharePoint Metadata

        if ($SharePointMetadata)
        {

            $Message = "SharePoint metadata supplied."
            Write-Verbose $Message
            Write-Log -Cmdlet $Cmdlet -Level Info -Message $Message

        }
        else
        {

            $Message = "No SharePoint metadata supplied."
            Write-Verbose $Message
            Write-Log -Cmdlet $Cmdlet -Level Info -Message $Message

        }

        ## Check SharePoint

        if ($CheckSharePointBeforeCopy)
        {

            $Message = "Check SharePoint switch activated."
            Write-Verbose $Message
            Write-Log -Cmdlet $Cmdlet -Level Info -Message $Message

        }
        else
        {

            $Message = "Check SharePoint switch not activated. SharePoint will not be checked before copy."
            Write-Verbose $Message
            Write-Log -Cmdlet $Cmdlet -Level Info -Message $Message

        }

        #endregion Parameter Checks

    } 
    Process 
    { 
        
        #region Export Document From ProjectWise

        $Message = "Exporting '$($ProjectWiseDocument.Name)' to '$WorkingDirectory'..."
        Write-Verbose $Message
        Write-Log -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-Log -Cmdlet $Cmdlet -Level Error -Message $Message
            Write-Log -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
        Write-Log -Cmdlet $Cmdlet -Level Info -Message $Message
        
        if ((Test-Path -LiteralPath $ExportedFilePath))
        {
        
            $Message = "Successfully exported '$($ProjectWiseDocument.Name)' to '$WorkingDirectory'."
            Write-Verbose $Message
            Write-Log -Cmdlet $Cmdlet -Level Info -Message $Message
        
        }
        else
        {
        
            $Message = "'$($ProjectWiseDocument.Name)' could not be found in '$WorkingDirectory' after export!"
            Write-Error $Message
            Write-Log -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
            Write-Log -Cmdlet $Cmdlet -Level Info -Message $Message
            
            $ModifiedFileName = "$SharePointDocumentName$ExportedFileExtension"
            
            $Message = "Target FileName: '$ModifiedFileName'."
            Write-Verbose $Message
            Write-Log -Cmdlet $Cmdlet -Level Info -Message $Message
            
            $ModifiedFilePath = "$WorkingDirectory\$ModifiedFileName"
            
            $Message = "Target FilePath: '$ModifiedFilePath'."
            Write-Verbose $Message
            Write-Log -Cmdlet $Cmdlet -Level Info -Message $Message

        }
        else
        {

            $Message = "Modifying exported file name to specified name..."
            Write-Verbose $Message
            Write-Log -Cmdlet $Cmdlet -Level Info -Message $Message
            
            $ModifiedFileName = "$SharePointDocumentName$ExportedFileExtension"
            
            $Message = "Modified FileName: '$ModifiedFileName'."
            Write-Verbose $Message
            Write-Log -Cmdlet $Cmdlet -Level Info -Message $Message
            
            $ModifiedFilePath = "$WorkingDirectory\$ModifiedFileName"
            
            $Message = "Modified FilePath: '$ModifiedFilePath'."
            Write-Verbose $Message
            Write-Log -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-Log -Cmdlet $Cmdlet -Level Warn -Message $Message
                    
                
                    $Message = "Overwriting existing file with latest version..."
                    Write-Warning $Message
                    Write-Log -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-Log -Cmdlet $Cmdlet -Level Error -Message $Message
                Write-Log -Cmdlet $Cmdlet -Level Error -Message $_.Exception.Message
                
                Break;
            
            }
            
            $Message = "Exported file successfully renamed to '$ModifiedFileName'."
            Write-Verbose $Message
            Write-Log -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
            Write-Log -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-Log -Cmdlet $Cmdlet -Level Warn -Message $Message
                    Write-Log -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
                Write-Log -Cmdlet $Cmdlet -Level Info -Message $Message  
            
            }
            else
            {
                
                $Message = "'$ModifiedFileName' does not exist in SharePoint. Creating new file..."
                Write-Verbose $Message
                Write-Log -Cmdlet $Cmdlet -Level Info -Message $Message
            
            }
        
        }

        #endregion Check SharePoint For File

        #region Upload File To SharePoint

        $Message = "Uploading file to SharePoint..."
        Write-Verbose $Message
        Write-Log -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-Log -Cmdlet $Cmdlet -Level Warn -Message $Message
                Write-Log -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
            Write-Log -Cmdlet $Cmdlet -Level Info -Message $Message
        
        }
        else
        {
        
            $Message = "Failed to upload $($ProjectWiseDocument.Name) to SharePoint after $NumberOfAttempts attempts."
            Write-Error $Message
            Write-Log -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
        Write-Log -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-Log -Cmdlet $Cmdlet -Level Error -Message $Message
            Write-Log -Cmdlet $Cmdlet -Level Error -Message $_.Exception.Message
        
            break;
        
        }
        
        $Message = "Successfully removed temporary file from working directory."
        Write-Verbose $Message
        Write-Log -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

    } 

}

# Purpose : Returns items from a given SharePoint list
# Requirements:
# - Write-Log function
# - SharePointPnPPowerShell* module (Install-Module SharePointPnPPowerShell2013 or Install-Module SharePointPnPPowerShell2016 or Install-Module SharePointPnPPowerShellOnline)

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
        Write-Log -Cmdlet $Cmdlet -Level Info -Message $Message

        ## Number of attempts

        $Message = "Number of attempts set to $NumberOfAttempts."
        Write-Verbose $Message
        Write-Log -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
        Write-Log -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-Log -Cmdlet $Cmdlet -Level Warn -Message $Message
                Write-Log -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 
            Write-Log -Cmdlet $Cmdlet -Level Info -Message $Message
               
        
        }
        else
        {
        
            $Message = "Failed to return SharePoint list items after $NumberOfAttempts attempts!"
            Write-Error $Message
            Write-Log -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

    } 

}

# Purpose : Opens a connection to a specified SharePoint server
# Requirements:
# - Write-Log function
# - SharePointPnPPowerShell* module (Install-Module SharePointPnPPowerShell2013 or Install-Module SharePointPnPPowerShell2016 or Install-Module SharePointPnPPowerShellOnline)

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-SharePointConnection -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
                Write-Log -Cmdlet $Cmdlet -Message $Message -Level Info
                
                try
                {
   
                    if (!($Module = Get-Module -Name SharePointPnPPowerShell2013))
                    {
                    
                        $Message = "Importing SharePointPnPPowerShell2013 module..."
                        Write-Verbose $Message
                        Write-Log -Cmdlet $Cmdlet -Message $Message -Level Info

                        Import-Module -Name SharePointPnPPowerShell2013 -ErrorAction Stop

                        $Module = Get-Module -Name SharePointPnPPowerShell2013

                    }

                }
                catch
                {

                    $Message = "$($PSItem.Exception.Message)"
                    Write-Error $Message
                    Write-Log -Cmdlet $Cmdlet -Message $Message -Level Error
                    
                    $Message = "Error importing SharePointPnPPowerShell2013 Module."
                    Write-Error $Message
                    Write-Log -Cmdlet $Cmdlet -Message $Message -Level Error
                    
                    break;  

                }
            
            }
            "2016"
            {
            
                $Message = "Checking for SharePointPnPPowerShell2016 module..."
                Write-Verbose $Message
                Write-Log -Cmdlet $Cmdlet -Message $Message -Level Info
                
                try
                {
   
                    if (!($Module = Get-Module -Name SharePointPnPPowerShell2016))
                    {
                    
                        $Message = "Importing SharePointPnPPowerShell2016 module..."
                        Write-Verbose $Message
                        Write-Log -Cmdlet $Cmdlet -Message $Message -Level Info

                        Import-Module -Name SharePointPnPPowerShell2016 -ErrorAction Stop

                        $Module = Get-Module -Name SharePointPnPPowerShell2016

                    }

                }
                catch
                {

                    $Message = "$($PSItem.Exception.Message)"
                    Write-Error $Message
                    Write-Log -Cmdlet $Cmdlet -Message $Message -Level Error
                    
                    $Message = "Error importing SharePointPnPPowerShell2016 Module."
                    Write-Error $Message
                    Write-Log -Cmdlet $Cmdlet -Message $Message -Level Error
                    
                    break;  

                }
            
            }
            "365"
            {
            
                $Message = "Checking for SharePointPnPPowerShellOnline module..."
                Write-Verbose $Message
                Write-Log -Cmdlet $Cmdlet -Message $Message -Level Info
                
                try
                {
   
                    if (!($Module = Get-Module -Name SharePointPnPPowerShellOnline))
                    {
                    
                        $Message = "Importing SharePointPnPPowerShellOnline module..."
                        Write-Verbose $Message
                        Write-Log -Cmdlet $Cmdlet -Message $Message -Level Info

                        Import-Module -Name SharePointPnPPowerShellOnline -ErrorAction Stop

                        $Module = Get-Module -Name SharePointPnPPowerShellOnline

                    }

                }
                catch
                {

                    $Message = "$($PSItem.Exception.Message)"
                    Write-Error $Message
                    Write-Log -Cmdlet $Cmdlet -Message $Message -Level Error
                    
                    $Message = "Error importing SharePointPnPPowerShellOnline Module."
                    Write-Error $Message
                    Write-Log -Cmdlet $Cmdlet -Message $Message -Level Error
                    
                    break;  

                }


            }
        }

        if (!($Module))
        {

            break;

        }

        #endregion Requirements

        #region Parameter Checks

        ## SharePointServer

        $Message = "SharePoint server is $SharePointURL."
        Write-Verbose $Message
        Write-Log -Cmdlet $Cmdlet -Level Info -Message $Message

        ## Number of attempts

        $Message = "Number of attempts set to $NumberOfAttempts."
        Write-Verbose $Message
        Write-Log -Cmdlet $Cmdlet -Level Info -Message $Message

        #endregion Parameter Checks

    } 
    Process 
    { 
        
        #region Login to SharePoint

        $Message = "Logging into SharePoint..."
        Write-Log -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-Log -Cmdlet $Cmdlet -Level Warn -Message $Message
                Write-Log -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-Log -Cmdlet $Cmdlet -Level Error -Message $Message
            break;
        
        }
        else
        {
        
            $Message = "Successfully opened SharePoint connection to $($SharePointConnection.URL) on attempt $i."
            Write-Log -Cmdlet $Cmdlet -Level Info -Message $Message
            Write-Verbose $Message

            Write-Output $SharePointConnection
        
        }

        #endregion Return Connection

    } 

}

#endregion SharePoint