pwps_cdot

1.0.2.3

PWPS_CDOT.psm1

#####################################################################################################################################################################################################################
##############################| |####################################################################################################################################################################################
############## ########| |#
########### #####| |# Name : PWPS_CDOT Script Module
######### ####### ####| |# Type : Script Module
######## ############ ####| |# Purpose : Provide Functions for CDOT PS Scripts that interact with ProjectWise
####### ##### ##### ####| |# Author : Bentley Systems, Inc.
############## ##### #####| |# Date : 09/25/2020
############# ### ######| |# Script Version : 1.0.2.3
########## #######| |# PowerShell Version : 5.0 and above
######## ##########| |# ProjectWise Version : Not needed
######## ##############| |#
######### #########| |# Requirements:
######## ###### ########| |# - PWPS_DAB Module Version 1.16.0.0 or above (Tested on Version 01.22.2.0)
####### ######### #######| |# -
####### ########## #######| |# -
###### ######### ########| |# -
###### #### ########| |# -
###### ##########| |# -
######### #############| |# -
##############################| |#
#####################################################################################################################################################################################################################
##
## Function List:
## < Get-CDOTModuleLocation : Returns the location of the PWPS_CDOT Module
## < Get-CDOTProjectNumberFromProjectNameReturns : Project Number based on first five characters of folder name
## < Out-CDOTLogFile : Logs the time and a message in a log file (Default is "$env:LOCALAPPDATA\Bentley\Logs\CDOTProjectUpdate.log")
## < Remove-DataTableDuplicateRows : Removes Duplicate Rows from a Data Table based on a Primary Key
## < Remove-DataTableRowsByCriteria : Removes Rows from a Data Table with a given criteria string
## < Select-CDOTSQLDataTable : Creates a new PowerShell Data Table from a SQL Connection and SQL Statement
## < Update-CDOTPWProjectCodes : Attempts to add a project code to items that do not have a project code in a PW Rich Projects Data Table
## < Update-CDOTPWProjectInfoFromMasterTable : Updates a PW Rich Project Report Data Table with updated Project Properties
## < Update-CDOTPWProjectFolderNameProps : Updates folder name and description based on three project properties
## < Update-CDOTPWProjectsByPath : Updates project properties for a given project type in a given path
## < Compare-CDOTStartEndDates : Used to compare date and time values to determine if a process should continue or not.
## < New-CDOTPWLogin : Attempts a PW Login and writes result to a log file
## < New-CDOTPWAssetDocument : Creates a new abstract PW Document using a datarow from a datatable
## < Update-CDOTPWAssetDocument : Updates an existing abstract PW Document using a datarow from a datatable
##
## The Following Functions were inserted from a blog by Chad Miller and are listed at the end of this module
## (see https://gallery.technet.microsoft.com/scriptcenter/4208a159-a52e-4b99-83d4-8048468d29dd)
## < Get-Type : Returns Type for a given PowerShell object
## < Out-DataTable : Creates a DataTable for an object
##
##
## Description/Notes:
## - Version 01.00.00.01
## - Writes log information to a custom log file for the following functions
## < Update-CDOTPWProjectFolderNameProps
## < Update-CDOTPWProjectsByPath
##
## - Version 1.0.1.0
## - Added the following functions - all new functions use Write-PWPSLog in PWPS_DAB
## < Compare-CDOTStartEndDates
## < New-CDOTPWLogin
##
## - Version 1.0.2.0
## - Added the following functions - all new functions use Write-PWPSLog in PWPS_DAB
## < New-CDOTPWAssetDocument
## < Update-CDOTPWAssetDocument
##
## - Version 1.0.2.1
## - Repaired call to fixed SQL Server when calling CDOT_UpdatePWStructures_v01.ps1 from CDOT_UpdatePWProjects_CallingScript_v03.ps1
##
## - Version 1.0.2.2
## - Updated CDOT_GetPWRichProjectDataTable_v01 at line 135. Project Properties were not being captured.
##
## - Version 1.0.2.3
## - PWPS_CDOT.psm1:1155 - Fixed bug that tried to make a decimal out of a date: if ($($assetRow.$columnName) -match "^[-]?[0-9.]+$")
## - CDOT_UpdatePWProjects_CallingScript_v03.ps1: 227 - Now logs if $dtProjectList cannot be populated because no applicable work areas are found in the ProjectWise datasource
##
#####################################################################################################################################################################################################################
#####################################################################################################################################################################################################################

Function Get-CDOTModuleLocation {
    <#
    .SYNOPSIS
      Returns the location of the PWPS_CDOT Module
    .INPUTS
      None
    .OUTPUTS
      Location of PWPS_CDOT Module
    .EXAMPLE
        Get-CDOTModuleLocation
        Returns the location of the PWPS_CDOT Module
    .EXAMPLE
        $CDOTModuleLocation = Get-CDOTModuleLocation
        Returns the location of the PWPS_CDOT Module as a variable
    #>
    [CMDLETBINDING()]
     $PWPS_CDOTModuleLocation = $PSScriptRoot
    return $PWPS_CDOTModuleLocation
}
Export-ModuleMember Get-CDOTModuleLocation

FUNCTION Out-CDOTLogFile {
    <#
    .SYNOPSIS
      Logs the time and a message in a log file
    .DESCRIPTION
      The function uses two parameters: a message and a log file.
      The default log file is "$env:LOCALAPPDATA\Bentley\Logs\CDOTProjectUpdate.log"
    .PARAMETER Message
      The message to write to the log file (accepts string from pipeline)
    .PARAMETER logFile
      The file to write to
    .INPUTS
      None
    .OUTPUTS
      None
    .EXAMPLE
        Out-CDOTLogFile -Message "A new message"
        <Writes to the default log file>
    .EXAMPLE
        "A new message" | Out-CDOTLogFile
        <Writes to the default log file using the pipeline>
    #>
    [CMDLETBINDING()]
    PARAM([Parameter(Position=0, Mandatory=$true, ValueFromPipeline = $true)] [string]$Message, 
        [PARAMETER(Position=1, Mandatory=$false)] [string]$logFile="$env:LOCALAPPDATA\Bentley\Logs\CDOTProjectUpdate.log"
    )
    "$(Get-Date) $Message" | Out-File -FilePath $logFile -Append -Force
}
Export-ModuleMember -Function Out-CDOTLogFile

FUNCTION Remove-DataTableDuplicateRows {
    <#
    .SYNOPSIS
      Removes Duplicate Rows from a Data Table based on a Primary Key
    .DESCRIPTION
      The function uses two parameters: a select statement and a data table.
      If select statement produces a result, then the corresponding rows are deleted from the table
    .PARAMETER dt
      The data table that is processed and returned (accepts datatable from pipeline)
    .PARAMETER PrimaryKeyName
      The column name to test for duplicates
    .INPUTS
      None
    .OUTPUTS
      None
    .EXAMPLE
        Remove-DataTableDuplicateRows -dt $dtPWProjects -PrimaryKeyName "PROJECT_Project_Code"
        # Returns the first instance of each project number in $dtPWProjects
    #>
    [CMDLETBINDING()]
    PARAM([Parameter(Position=0, Mandatory=$true, ValueFromPipeline = $true)] $dt, 
        [PARAMETER(Position=1, Mandatory=$true)] [string]$PrimaryKeyName
    )
   
    $_item1 = "";

    for ($m = 0; $m -lt $dt.Rows.Count; $m++)
    {
        $_rCount = 0;
        $_item1 = $dt.Rows[$m]["$PrimaryKeyName"].ToString().Trim();

        for ($k = 0; $k -lt $dt.Rows.Count; $k++)
        {
            if ($_item1 -eq $dt.Rows[$k]["$PrimaryKeyName"].ToString().Trim())
            {
                if ($_rCount -ne 0)
                {
                    $strProjectPath = "$($dt.Rows[$k].ExistingProjectPath)"
                    $strProjectPrimaryKey = "$($dt.Rows[$k].$PrimaryKeyName)"
                    if ($strProjectPrimaryKey.Length -gt 1) {
                        $strMessage = "'$strProjectPath' uses a duplicate $PrimaryKeyName ($strProjectPrimaryKey) and will not be updated."
                        Write-Warning $strMessage
                        Out-CDOTLogFile $strMessage
                    }
                    $dt.Rows.RemoveAt($k);                       

                }
                $_rCount++;

            }
        }
    }
        
    return $dt;
}
Export-ModuleMember Remove-DataTableDuplicateRows

FUNCTION Remove-DataTableRowsByCriteria 
{ 

    <#
      .SYNOPSIS
        Removes rows in a data table by a condition
      .DESCRIPTION
        Removes rows in a data table by a condition
      .PARAMETER dt
        Data table to process (accepts pipeline object)
      .PARAMETER rowCriteria
        Criteria to use in a dt.Select statement
      .INTPUTS
        dt
      .OUTPUTS
        dt
      .EXAMPLE
        Remove-DataTableRowsByCriteria -dt $dtPWProjects -rowCriteria "FolderName IS NULL"
        # Remove rows in a data table whose value is NULL in the FolderName column
      #>

    [CmdletBinding()] 
    param([Parameter(Position=0, Mandatory=$true, ValueFromPipeline = $true)] $dt, 
          [Parameter(Position=1, Mandatory=$true,HelpMessage="Enter the row criteria")] [string]$rowCriteria)
 
    Begin 
    { 
    } 
    Process 
    { 
        $rowsToDelete = $dt.Select("$rowCriteria")
        foreach ($row in $rowsToDelete) { $row.Delete() }
        $dt.AcceptChanges()
    }  
      
    End 
    { 
        return $dt;
    } 

} #Remove-DataTableRowsByCriteria
Export-ModuleMember -Function Remove-DataTableRowsByCriteria


FUNCTION Update-CDOTPWProjectFolderNameProps {

    <#
      .SYNOPSIS
        Updates the folder name and description for a CDOT_INFO Project
      .DESCRIPTION
         Folder name consists of two project properties and a separator.
         Folder description is the Project Number.
         The function also removes illegal characters in the folder name based on entries in a CSV File named PWPS_CDOT_Illegal_Characters.csv.
      .PARAMETER ProjectFolderPath
        Path to the project
      .PARAMETER PWFolderNameProjectProperty01
        Project Property to begin folder name
      .PARAMETER PWProjectFolderSeparator
        Characters to separate first and second part of folder name
      .PARAMETER PWFolderNameProjectProperty02
        Project Property to end folder name
      .PARAMETER PWFolderDescriptionProjectProperty
        Project property that will be the description of the folder
      .PARAMETER illegalCharacterMapFile
        CSV File that contains two columns: IllegalCharacter and SubstitutionString
      .OUTPUTS
        None
      .EXAMPLE
        Update-CDOTPWProjectFolderNameProps
         
        Description
        -----------
        Update a Project based on its path
      #>

    [CMDLETBINDING()]
    PARAM([PARAMETER(Position=0,Mandatory=$true,HelpMessage="Enter path to project", ValueFromPipeline = $true)] [string]$ProjectFolderPath,
          [PARAMETER(Position=1,Mandatory=$false)] [string]$PWFolderNameProjectProperty01 = 'PROJECT_Project_Code',
          [PARAMETER(Position=2,Mandatory=$false)] [string]$PWProjectFolderSeparator = ' - ',
          [PARAMETER(Position=3,Mandatory=$false)] [string]$PWFolderNameProjectProperty02 = 'PROJECT_Project_Description',
          [PARAMETER(Position=4,Mandatory=$false)] [string]$PWFolderDescriptionProjectProperty = 'PROJECT_Project_Number',
          [PARAMETER(Position=5,Mandatory=$false)] [string]$illegalCharacterMapFile = "$(Get-CDOTModuleLocation)\PWPS_CDOT_Illegal_Characters.csv",
          [PARAMETER()] $logFile
    )

    BEGIN{
        if (!(Get-PWCurrentDatasource)) {Throw "A ProjectWise session is required to update Projects"}
        $illegalCharacterMap = Import-Csv($illegalCharacterMapFile)
    }
    PROCESS{
        
        try {
            
            # Get the Rich Project and its properties by folder path
            $PWProject = Get-PWRichProjects -FolderPath $ProjectFolderPath -JustOne

            # Validate Project Property Names
            $PWPropKeyNames = $PWProject.ProjectProperties.Keys

            # Ensure that the First Project Property and Second Project Property Exist
            if($($PWPropKeyNames) -match $PWFolderNameProjectProperty01 -and $($PWPropKeyNames) -match $PWFolderNameProjectProperty02)
            {
    
                # Read Existing Folder Name and Description
                $existFolderName = $PWProject.Name
                $existFolderDescription = $PWProject.Description
        
                # Create parts of folder name
                $newFolderNamePart01 = $PWProject.ProjectProperties[$PWFolderNameProjectProperty01]
                $newFolderNamePart02 = $PWProject.ProjectProperties[$PWFolderNameProjectProperty02]
        
                # Create new folder name
                $newFolderName = "$($newFolderNamePart01)$($PWProjectFolderSeparator)$($newFolderNamePart02)"

                # Replace illegal characters in folder name
                foreach ($row in $illegalCharacterMap) { $newFolderName = $newFolderName.Replace($row.IllegalCharacter,$row.SubstitutionString) }
                
                # Make folder name lower case
                $newFolderName = $newFolderName.ToLower()

                # Create new folder description
                $newFolderDescripion = $PWProject.ProjectProperties[$PWFolderDescriptionProjectProperty]

                # Update folder name if it is different than the current name
                if ($newFolderName -ne $existFolderName -or $newFolderDescripion -ne $existFolderDescription) {
                    # Update Folder Name and Description if name or description is different
                    Update-PWFolderNameProps -FolderPath $ProjectFolderPath -NewName $newFolderName -NewDescription $newFolderDescripion
                    
                    if ($newFolderName -ne $existFolderName) {
                        $strMessage = "Name for $ProjectFolderPath was changed to $newFolderName"
                        Out-CDOTLogFile $strMessage -logFile $logFile
                    }
                    
                    if ($newFolderDescripion -ne $existFolderDescription) {
                        $strMessage = "Description for $ProjectFolderPath was changed to $newFolderDescripion"
                        Out-CDOTLogFile $strMessage -logFile $logFile
                    }
                }
                else {
                    # Write-Host "FOLDER NAME NOT CHANGED!"
                }
                                        
            }
            else {
                "$PWFolderNameProjectProperty01 OR $PWFolderNameProjectProperty02 NOT FOUND..."
            }
            
        }
        catch [system.exception]
        {
            Write-Host $_
        }
        finally {
             [void]$PWProject.Dispose
             [void]$PWPropKeyNames.Dispose
             [void]$existFolderName.Dispose
             [void]$existFolderDescription.Dispose
             [void]$newFolderNamePart01.Dispose
             [void]$newFolderNamePart02.Dispose
             [void]$newFolderName.Dispose
             [void]$newFolderDescripion.Dispose
        }

        
    }
    END{
    }

}
Export-ModuleMember -Function Update-CDOTPWProjectFolderNameProps

FUNCTION Update-CDOTPWProjectsByPath {
    <#
    .SYNOPSIS
      Updates project properties for a given project type in a given path
    .DESCRIPTION
      The function finds all projects in a ProjectWise Path and updates
      the Project Propeties based using the following parameters:
       
      Datasource Definition, Master Table data table object, ProjectWise Path,
       
    .PARAMETER ProjectWisePath
      The path to process
    .PARAMETER ProjectWiseProjectType
      The Project Type to Update. Default is 'CDOT_INFO'
    .PARAMETER MasterProjectsDataTable
      The data table that contains the new values for project properties. Default is $dtCDOTDATA
    .PARAMETER PrimaryKeyName
      The column name in both tables that will be used for the merge. Default is 'PROJECT_Project_Code'
    .PARAMETER UpdateFolderNames
      When set, folder names will be updated using the project code, a separator and the project description
    .INPUTS
      None
    .OUTPUTS
      None
    .EXAMPLE
        Update-CDOTPWProjectsByPath -ProjectWisePath 'Projects\'
        # Update all projects in \Projects
    .EXAMPLE
        PS C:\>$InvalidFolderNames = "Engineering_Estimate", "Final_Engineers", "DELETE*", "*Backup"
        PS C:\>Update-CDOTPWProjectsByPath -ProjectWisePath 'Projects\' -InvalidFolderNames $InvalidFolderNames
         
        Description
        -----------
        Update all projects in \Projects except for ones with a certain folder name pattern
    #>
    [CMDLETBINDING()]
    PARAM(
        [PARAMETER(Position=0, Mandatory=$true)] [string]$ProjectWisePath,
        [PARAMETER(Position=1, Mandatory=$false)] [string]$ProjectWiseProjectType="CDOT_INFO",
        [PARAMETER(Position=2, Mandatory=$true)] $MasterProjectsDataTable,
        [PARAMETER(Position=3, Mandatory=$false)] [string]$PrimaryKeyName="PROJECT_Project_Code",
        [PARAMETER(Position=4, Mandatory=$false)] [switch]$UpdateFolderNames,
        [PARAMETER()] $logFile
    )

    BEGIN {
        if (!(Get-PWCurrentDatasource)) {Throw "A ProjectWise session is required to update Projects"}
        # Create array to hold paths that are not updated
        $foldersNotIncluded = @()
    }
    PROCESS {
        # 1. Get Projects in $PWPath
        $dtPWProjects = (Get-PWRichProjectReport -FolderPath $ProjectWisePath -ProjectType $ProjectWiseProjectType -OutputDataTables)
        if ($dtPWProjects.Rows.Count -gt 0) {
            # Process the projects
            # 2. Clean up the ProjectWise Projects Data Table
        
                # A. Select Projects with no Primary Key value and update
                Update-CDOTPWProjectCodes -projectCodeColumnName $PrimaryKeyName -dt $dtPWProjects

                # D. Remove duplicates
                Remove-DataTableDuplicateRows -dt $dtPWProjects -PrimaryKeyName $PrimaryKeyName
   
            try {
                # 3. Update Project Properties for in the data table (PW Projects with Duplicate Project Numbers will be removed)
                Update-CDOTPWProjectInfoFromMasterTable -ProjectWiseProjectsDataTable $dtPWProjects -MasterProjectsDataTable $MasterProjectsDataTable -PrimaryKeyName $PWProjectNumberProperty

                # 4. Update the ProjectWise Projects using the ProjectWise datatable
                Update-PWRichProjectsFromDataTables -Data $dtPWProjects

                # 5. Write message to log file here
                foreach ($item in $dtPWProjects) {
                    Write-Verbose "$($item.FolderName) was updated"
                    Out-CDOTLogFile "$($item.FolderName) was updated" -logFile $logFile 
                }

            }
            catch {
                $strMessage = ($dtPWProjects | Select-Object -Property ExistingProjectPath, PROJECT_Project_Code | Out-String)
                Write-Warning "Some of the following folders have duplicate Project IDs:`n$strMessage"
                Undo-PWLogin
                Throw "Exiting...'$PrimaryKeyName' does not have unique values in ProjectWise Projects Data Table"
            }
            # 5. Update the folder names and descriptions (if set)
            if ($UpdateFolderNames)
            {
                foreach ($project in $dtPWProjects) { Update-CDOTPWProjectFolderNameProps -ProjectFolderPath $project.ExistingProjectPath }
            }
        }
        else {Write-Warning "$ProjectWisePath Contains no Projects"}
    }
    END {
        if ($foldersNotIncluded.Count -gt 0) {
            # 5. Output the folders that were not updated
            $strMessage = ($foldersNotIncluded | Out-String).Replace("$PWPath"," $PWPath")
            Write-Warning "The following folders were not updated:`n$strMessage"
        }
    }
}
Export-ModuleMember Update-CDOTPWProjectsByPath

FUNCTION Update-CDOTPWProjectInfoFromMasterTable {
    <#
    .SYNOPSIS
      Updates a PW Rich Project Report Data Table with updated Project Properties
    .DESCRIPTION
      The function uses three parameters: ProjectWiseProjectsDataTable, MasterProjectsDataTable, PrimaryKeyName
      The ProjectWiseProjectsDataTable is updated with information from the MastetProejctsDataTable
    .PARAMETER ProjectWiseProjectsDataTable
      ProjectWise Rich Project Data Table for Input
    .PARAMETER MasterProjectsDataTable
      The data table that contains the new values for project properties
    .PARAMETER PrimaryKeyName
      The column name in both tables that will be used for the merge
    .INPUTS
      None
    .OUTPUTS
      None
    .EXAMPLE
        Update-CDOTPWProjectInfoFromMasterTable -MasterProjectsDataTable $dtPWProjects -PrimaryKeyName PROJECT_Project_Code -ProjectWiseProjectsDataTable $dtCDOTDATA
        # Updates the PROJECT Columns in the $dtPWProjects data table from the values in the $dtCDOTDATA data table
    .EXAMPLE
        $dtPWProjects | Update-CDOTPWProjectInfoFromMasterTable -PrimaryKeyName PROJECT_Project_Code -ProjectWiseProjectsDataTable $dtCDOTDATA
         
        Description
        -----------
        Updates the PROJECT Columns in the $dtPWProjects data table (from the pipeline) from the values in the $dtCDOTDATA data table
    #>
    [CMDLETBINDING()]
    PARAM(
        [PARAMETER(Position=0, Mandatory=$true, ValueFromPipeline = $true)] $ProjectWiseProjectsDataTable,
        [PARAMETER(Position=1, Mandatory=$true)] $MasterProjectsDataTable,
        [PARAMETER(Position=2, Mandatory=$true)] [string]$PrimaryKeyName
    )

    BEGIN
    {
        # Verify that the input objects are data tables
        if (($ProjectWiseProjectsDataTable.GetType().Name -eq 'DataTable') -eq $false) {
            Throw "PW Project Input object is not a datatable!"
        }
        if (($MasterProjectsDataTable.GetType().Name -eq 'DataTable') -eq $false) {
            Throw "Master Projects object is not a datatable!"
        }

        # Verify that both data tables use the same table name
        if (!($ProjectWiseProjectsDataTable.TableName -eq $MasterProjectsDataTable.TableName)) {
            Throw "Table Name must match in both tables"
        }

        # Try to set a primary key for both tables - remove dups in ProectWise if found
        try { $ProjectWiseProjectsDataTable.PrimaryKey = $ProjectWiseProjectsDataTable.Columns[$PrimaryKeyName] }
        catch {
            Write-Warning "$PrimaryKeyName' does not have unique values in ProjectWise Projects Data Table...removing duplicates..."
            Remove-DataTableDuplicateRows -dt $ProjectWiseProjectsDataTable -PrimaryKeyName $PrimaryKeyName
            $ProjectWiseProjectsDataTable.PrimaryKey = $ProjectWiseProjectsDataTable.Columns[$PrimaryKeyName]
        }
        try { $MasterProjectsDataTable.PrimaryKey = $MasterProjectsDataTable.Columns[$PrimaryKeyName] }
        catch {
            Write-Warning "$PrimaryKeyName' does not have unique values in Master Projects Data Table...removing duplicates..."
            Remove-DataTableDuplicateRows -dt $MasterProjectsDataTable -PrimaryKeyName $PrimaryKeyName
            $MasterProjectsDataTable.PrimaryKey = $MasterProjectsDataTable.Columns[$PrimaryKeyName]
        }
        
    }
    PROCESS
    {
        # 0. Obtain Column Names for the Project Table
        $AllColumnNames = ($ProjectWiseProjectsDataTable.Columns).ColumnName
        $ProjectPropertyColumnNames = $AllColumnNames -like 'PROJECT_*' -ne $PrimaryKeyName
        
        # 1. Remove PROJECT Columns from $dtPWProjects so the values from the input table can be added
        foreach ($colName in $ProjectPropertyColumnNames) { $ProjectWiseProjectsDataTable.Columns.Remove($colName) }
       
        # 2. Merge Columns from Master Table into Project Table
        $ProjectWiseProjectsDataTable.Merge($MasterProjectsDataTable)

        # 3. Remove all records where FolderName is Null, PROJECT_Project_Code = 0, PROJECT_Project_Status is NULL
        Remove-DataTableRowsByCriteria -dt $ProjectWiseProjectsDataTable -rowCriteria "FolderName IS NULL"
        Remove-DataTableRowsByCriteria -dt $ProjectWiseProjectsDataTable -rowCriteria "$PrimaryKeyName = 0"
        Remove-DataTableRowsByCriteria -dt $ProjectWiseProjectsDataTable -rowCriteria "PROJECT_Project_Status is NULL"
        

        # 4. Cleanup Columns that are not in the original Project Data Table
        $NewColumnNames = ($ProjectWiseProjectsDataTable.Columns).ColumnName
        foreach ($colName in $NewColumnNames) { 
            if ($colName -notin $AllColumnNames) {$ProjectWiseProjectsDataTable.Columns.Remove($colName)}
        }

        $ProjectWiseProjectsDataTable.AcceptChanges()
    }
    END
    {
        # Return the Data Table
        #Return $ProjectWiseProjectsDataTable
        #Write-Output @(,($ProjectWiseProjectsDataTable))
    }


}
Export-ModuleMember -Function Update-CDOTPWProjectInfoFromMasterTable

FUNCTION Get-CDOTProjectNumberFromProjectName ([string]$PWFolderName){
   try{
    return [int]$PWFolderName.Substring(0,5)
   }
   catch{
    return 0
   }
}
Export-ModuleMember -Function Get-CDOTProjectNumberFromProjectName

FUNCTION Update-CDOTPWProjectCodes {
    <#
    .SYNOPSIS
      Attempts to add a project code to items that do not have a project code
      in a PW Rich Projects Data Table
    .DESCRIPTION
      The function finds all records in the data table that do not have a
      project code.
       
      Once the records are found, the function attempts to ascertain the
      project code based on the first five characters of the folder name
      using the folders DMS number (o_projectno)
    .PARAMETER projectCodeColumnName
      The name of the column to update in the data table
    .PARAMETER dt
      The data table that is processed
    .INPUTS
      None
    .OUTPUTS
      None
    .EXAMPLE
        Remove-CDOTInvalidPWFolders -projectCodeColumnName PROJECT_Project_Code -dt $dtPWProjects
         
        Description
        -----------
        Update all rows from $dtPWProjects whose value for PROJECT_Project_Code is blank
    #>
    [CMDLETBINDING()]
    PARAM(
        [PARAMETER(Position=0, Mandatory=$true)] [string]$projectCodeColumnName,
        [PARAMETER(Position=1, Mandatory=$true)] $dt
    )

    $dtProjectsToUpdate = $dt.Select("$projectCodeColumnName = ''") | Out-DataTable

    try {

        if ($dtProjectsToUpdate.Rows.Count -gt 0) {
            $col = $dt.Columns[$projectCodeColumnName];
            foreach($row in $dt.Rows) {
                $row[$col] = Get-CDOTProjectNumberFromProjectName -PWFolderName $row.FolderName
            }
            Write-Host $dtProjectsToUpdate.Count "Project Codes Updated"
        }
        else {
            Write-Host "All Projects have a project Code"
        }
        
        
    }
    catch {
        $ex = $_.Exception 
        Write-Error "$ex.Message" 
        continue 
    }
}
Export-ModuleMember -Function Update-CDOTPWProjectCodes


FUNCTION Select-CDOTSQLDataTable {

    <#
      .SYNOPSIS
        Creates a new PowerShell Data Table from a SQL Connection and SQL Statement
      .DESCRIPTION
         Creates a new PowerShell Data Table from a SQL Connection and SQL Statement
      .OUTPUTS
        System.Data.DataTable
      .EXAMPLE
        Select-SQLDataTable -SQLConnectionString "Database=master; Integrated Security=true" -SqlQuery "SELECT * FROM spt_monitor"
         
        Description
        -----------
        Runs a SELECT statement on the Master database on the local server
      #>

    [CMDLETBINDING()]
    PARAM([PARAMETER(Position=0,Mandatory=$true,HelpMessage="Enter Connection String to SQL Database")] [string]$SQLConnectionString,
          [PARAMETER(Position=1,Mandatory=$true,HelpMessage="Enter SELECT Statement to generate the data table")] [string]$SqlQuery,
          [PARAMETER(Position=2,Mandatory=$true,HelpMessage="Enter the table name for the data table")] [string]$TableName
    )

    BEGIN{
        $SQLConnection = New-Object   System.Data.SqlClient.SqlConnection
        $SQLCommand = New-Object System.Data.SqlClient.SqlCommand
        $dataAdaptor = New-Object System.Data.SqlClient.SqlDataAdapter
        $dataTable = New-Object System.Data.DataTable($TableName)
    }
    PROCESS{
        
        
        
        try {
            
            $SQLConnection.ConnectionString = $SQLConnectionString
            $SQLCommand.Connection = $SQLConnection
            $SQLCommand.CommandText = $SqlQuery

            [void]$SQLConnection.Open

            $dataAdaptor.SelectCommand = $SQLCommand

            $null = $dataAdaptor.Fill($dataTable)

            [void]$SQLConnection.Close
        }
        catch [system.exception]
        {
            Write-Host $_
        }
        finally {
             [void]$dataAdaptor.Dispose
             [void]$SQLCommand.Dispose
             [void]$SQLConnection.Dispose
        }

        
    }
    END{
        # Return the Data Table
        #Return $dataTable
        Write-Output @(,($datatable)) 
    }

}
Export-ModuleMember -Function Select-CDOTSQLDataTable



############################################################################################
# Following Functions are from Chad Miller
# https://gallery.technet.microsoft.com/scriptcenter/4208a159-a52e-4b99-83d4-8048468d29dd
############################################################################################

#######################
function Get-Type 
{ 
    param($type) 
 
$types = @( 
'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') 
 
    if ( $types -contains $type ) { 
        Write-Output "$type" 
    } 
    else { 
        Write-Output 'System.String' 
         
    } 
} #Get-Type
Export-ModuleMember -Function Get-Type

#######################
 
function Out-DataTable { 
<#
.SYNOPSIS
Creates a DataTable for an object
.DESCRIPTION
Creates a DataTable based on an objects properties.
.INPUTS
Object
    Any object can be piped to Out-DataTable
.OUTPUTS
   System.Data.DataTable
.EXAMPLE
$dt = Get-psdrive | Out-DataTable
This example creates a DataTable from the properties of Get-psdrive and assigns output to $dt variable
.NOTES
Adapted from script by Marc van Orsouw see link
Version History
v1.0 - Chad Miller - Initial Release
v1.1 - Chad Miller - Fixed Issue with Properties
v1.2 - Chad Miller - Added setting column datatype by property as suggested by emp0
v1.3 - Chad Miller - Corrected issue with setting datatype on empty properties
v1.4 - Chad Miller - Corrected issue with DBNull
v1.5 - Chad Miller - Updated example
v1.6 - Chad Miller - Added column datatype logic with default to string
v1.7 - Chad Miller - Fixed issue with IsArray
v1.8 (CDOT) - Adrian Crowfoot - Added optional parameter to add Table Name
.LINK
https://gallery.technet.microsoft.com/scriptcenter/4208a159-a52e-4b99-83d4-8048468d29dd
#> 
    [CmdletBinding()] 
    param([Parameter(Position=0, Mandatory=$true, ValueFromPipeline = $true)] [PSObject[]]$InputObject, 
          [Parameter(Position=1, Mandatory=$false,HelpMessage="Enter the table name for the data table")] [string]$TableName="Table1")
 
    Begin 
    { 
        $dt = new-object Data.datatable   
        $First = $true  
    } 
    Process 
    { 
        foreach ($object in $InputObject) 
        { 
            $DR = $DT.NewRow()   
            foreach($property in $object.PsObject.get_properties()) 
            {   
                if ($first) 
                {   
                    $Col =  new-object Data.DataColumn   
                    $Col.ColumnName = $property.Name.ToString()   
                    if ($property.value) 
                    { 
                        if ($property.value -isnot [System.DBNull]) { 
                            $Col.DataType = [System.Type]::GetType("$(Get-Type $property.TypeNameOfValue)") 
                         } 
                    } 
                    $DT.Columns.Add($Col) 
                }   
                if ($property.Gettype().IsArray) { 
                    $DR.Item($property.Name) =$property.value | ConvertTo-XML -AS String -NoTypeInformation -Depth 1 
                }   
               else { 
                    $DR.Item($property.Name) = $property.value 
                } 
            }   
            $DT.Rows.Add($DR)   
            $First = $false 
        } 
    }  
      
    End 
    { 
        $dt.TableName = $TableName
        Write-Output @(,($dt)) 
    } 
 
} #Out-DataTable
Export-ModuleMember -Function Out-DataTable


FUNCTION Compare-CDOTStartEndDates {
    <#
            .SYNOPSIS
            Used to compare date and time.
            .DESCRIPTION
            Used to compare date and time values to determine if a process should continue or not.
            Returns true or false.
            .EXAMPLE
            $ReturnValue = Compare-CDOTStartEndDates -StartTime 04:00 -EndTime 20:00
    #>
    
    [CmdletBinding()]
    param(
        [ValidateNotNullOrEmpty()]
        [Parameter(
                HelpMessage = "Start time to compare.",
                Mandatory = $true,
        Position = 0)]
        [DateTime] $StartTime,

        [ValidateNotNullOrEmpty()] 
        [Parameter(
                HelpMessage = "End time to compare.",
                Mandatory = $true,
        Position = 1)]
        [DateTime] $EndTime,
        
        [ValidateNotNullOrEmpty()]        
        [Parameter(
                HelpMessage = "Log file to write messages to.",
                Mandatory = $true,
        Position = 2)]
        [String] $LogFile
    ) # end param...

    BEGIN {
        $CmdletName = $MyInvocation.MyCommand.Name
        
        $Splat_Log = @{
            Path = $LogFile
            Cmdlet = $CmdletName
        }
        
        Write-PWPSLog @Splat_Log -Message "[BEGIN] $(Get-Date) - Entering '$CmdletName' Function..." -Level Info
    } # end BEGIN...
    
    PROCESS { 
        
        [bool] $Return = $false
        
        
        $CurrentDateTime = Get-Date
        # The current time must be after the Start time.
        <#
            No conversions are needed. Full datetimes with proper hours must be supplied.
            I think we really want this always set to tomorrow at least now so the Sat to Sun run is multiday.....
        $StartTimeMilitary = Get-Date $StartTime
        # The current time must be before the End time.
        $EndTimeMilitary = Get-Date $EndTime
         
        if($EndTimeMilitary -lt $StartTimeMilitary) {
 
            #This was the only active line until removed due to it being moved out to the file that kicks it off with a splat. $EndTimeMilitary = $EndTimeMilitary.AddDays(1)
        #}
        #>
               #Write-PWPSLog @Splat_Log -Message "cur: $CurrentDateTime start: $StartTimeMilitary end: $EndTimeMilitary" -Level Info

        if(( $CurrentDateTime -gt $StartTime ) -and ( $CurrentDateTime -lt $EndTime )) {
            $Return = $true
        }   
 
    } # end PROCESS...
    
    END {
        Write-PWPSLog -Message "[END] $(Get-Date) - Exiting '$CmdletName' Function..." -Path $LogFile -Level Info -Cmdlet $CmdletName
        Write-Output $Return
    } # end END...
} # end FUNCTION Compare-CDOTStartEndDates...
Export-ModuleMember -Function Compare-CDOTStartEndDates

FUNCTION New-CDOTPWLogin {
    [CmdletBinding()]
    param( 
        [ValidateNotNullOrEmpty()]
        [Parameter(
            HelpMessage = 'ProjectWise Datasource to log into.',
            Position = 0,
            Mandatory = $true)]
        [string] $DatasourceName,

        [ValidateNotNullOrEmpty()]
        [Parameter(
            HelpMessage = 'ProjectWise User Name.',
            Position = 0,
            Mandatory = $true)]
        [string] $UserName,

        [ValidateNotNullOrEmpty()]
        [ValidateScript( { Get-ChildItem -Path $_ -File } )]
        [Parameter(
            HelpMessage = 'Text file to get password from.',
            Position = 1,
            Mandatory = $true)]
        [string] $PWFilePathName,

        [ValidateNotNullOrEmpty()]
        [Parameter(
            HelpMessage = 'Log file path and name.',
            Position = 2,
            Mandatory = $true)]
        [string] $LogFilePathName
    ) # end param...

    BEGIN {
        $CmdletName = $MyInvocation.MyCommand.Name
        
        $Splat_Log = @{
            Path = $LogFilePathName
            Cmdlet = $CmdletName
        }
        
        Write-PWPSLog @Splat_Log -Message "[BEGIN] $(Get-Date) - Entering '$CmdletName' Function..." -Level Info
    } #end BEGIN...

    PROCESS {
        [bool] $Return = $true
        # Log into Datasource
        Write-PWPSLog @Splat_Log -Message '[PROCESS] Logging into ProjectWise' -Level Info

        # Get secure password from provided file for use with logging into ProjectWise source datasource.
        $Password = Get-SecureStringFromEncryptedFile -FileName $PWFilePathName
        if( -not (New-PWLogin -DatasourceName $DatasourceName -UserName $UserName -Password $Password)) {
            # failed to log into source datasource. Throw an error message.
            $Return = $false
        } else {
            Write-PWPSLog @Splat_Log -Message '[PROCESS] Successfully logged into ProjectWise datasource.' -Level Info
            
            # Verify in datasource
            Write-PWPSLog @Splat_Log -Message "[PROCESS] Current datasource is '$(Get-PWCurrentDatasource)'." -Level Info
            
            # Verify current user's working directory exists.
            $pwUserWorkingDirectory = Get-PWUserWorkingDirectory -UserName $UserName
            
            if( -not (Test-Path -Path $pwUserWorkingDirectory -PathType Container )) {
                Write-PWPSLog @Splat_Log -Message "[PROCESS] Working directory does not exist for current user '$((Get-PWCurrentUser).Name)'. Attempting to create." -Level Warn
                New-Item -Path $pwUserWorkingDirectory -ItemType Directory -Force
            }
        }
    } # end PROCESS...

    END {
        Write-PWPSLog @Splat_Log -Message "[END] $(Get-Date) - Exiting '$CmdletName' Function..." -Level Info
        Write-Output $Return
    } # end END...
} #end FUNCTION New-CDOTPWLogin...
Export-ModuleMember -Function New-CDOTPWLogin

function New-CDOTPWAssetDocument
{
  <#
      .SYNOPSIS
      Creates a new abstract PW Document using a datarow from a datatable
      .DESCRIPTION
      Creates a new abstract PW Document with attributes from a datarow that represents an asset
      .EXAMPLE
      New-PWAssetDocument
      explains how to use the command
      can be multiple lines
      .EXAMPLE
      New-PWAssetDocument
      another example
      can have as many examples as you like
  #>

  param
  (
    [Parameter(
        HelpMessage = 'Datarow that includes project number, asset number and asset attributes',
    Mandatory=$true, Position=0)]
    [Data.DataRow] $assetRow, # = ,
    
    [Parameter(
        HelpMessage = 'Column Name in DataRow and PW Asset Environment for the Project ID',
    Mandatory =$true, Position=1)]
    [String]$projectIDColumnName,
    
    [Parameter(
        HelpMessage = 'Column Name in DataRow and PW Asset Environment for the Asset ID',
    Mandatory =$true, Position=2)]
    [String] $assetIDColumnName,
    
    [Parameter(
        HelpMessage = 'Prefix to name each document',
        Mandatory = $true,
    Position = 3 )]
    [string]$PWDocumentNamePrefix, # ='ST',

    [Parameter(
        HelpMessage = 'PW Folder Path to place asset document',
        Mandatory = $true,
    Position = 4 )]
    [string]$PWFolderRootPath , # ='AdminConfig\Assets\',

    ###########################################################################################
    # Script and Log File Information
    ###########################################################################################
    [ValidateNotNullOrEmpty()]
    [ValidateScript( { Test-Path -Path $_ -PathType Container })]
    [Parameter( 
        HelpMessage = 'Location of script file and log folder.',
        Mandatory = $true,
    Position = 5 )]
    [string] $Path,    
    [ValidateNotNullOrEmpty()]
    [Parameter(
        HelpMessage = 'Log file to be generated for each run of the script.',
        Mandatory = $true,
    Position = 6 )]
    [string] $LogFilePathName,
    [Parameter(
        HelpMessage = 'Name of cmdlet to place in log file',
        Mandatory = $true,
    Position = 7 )]
    [string] $CmdletName
            
  )
  

  # Set Project Code, Asset ID, and Asset Name to check in document
  $projectID = $assetRow.$projectIDColumnName
  $assetID = $assetRow.$assetIDColumnName
  
  # Convert Asset ID to String if it is an integer ("00")
  if ($assetID.GetTypeCode() -eq 'Int32'){$assetID = $assetID.ToString('00')}
  $assetDocName = $projectID + '_' + $PWDocumentNamePrefix + $assetID
  
  # Create New Document
  $Splat_NewDocument = @{
    FolderPath = $PWFolderRootPath
    Count = 1
    Description = $assetDocName
    DocumentName = $assetDocName
  }
  $newAssetDocument = New-PWDocumentAbstract @Splat_NewDocument -DoNotAddSuffix
  
  
  # Create Attribute Hash table
  $htUpdatedAttributes = @{}
  foreach ($columnName in $assetRow.Table.Columns.ColumnName){
    $htUpdatedAttributes.$columnName = $assetRow.$columnName
  }
  
  #update the attribute with that value so we can see it
  Update-PWDocumentAttributes -InputDocuments $newAssetDocument -Attributes $htUpdatedAttributes -ReturnBoolean # | Out-Null
}
Export-ModuleMember -Function New-CDOTPWAssetDocument


function Update-CDOTPWAssetDocument
{
  <#
      .SYNOPSIS
      Updates an existing abstract PW Document using a datarow from a datatable
      .DESCRIPTION
      Updates an existing abstract PW Document with attributes from a datarow that represents an asset
      .EXAMPLE
      Update-PWAssetDocument
      explains how to use the command
      can be multiple lines
  #>
  param
  (
    [Parameter(
        HelpMessage = 'Existing PW Document with Asset info',
    Mandatory=$true, Position=0)]
    [PWPS_DAB.CommonTypes+ProjectWiseDocument]$assetDocument,
    [Parameter(
        HelpMessage = 'Datarow that includes project number, asset number and asset attributes',
    Mandatory=$true, Position=1)]
    [Data.DataRow] $assetRow, # = ,
    
    [Parameter(
        HelpMessage = 'Column Name in DataRow and PW Asset Environment for the Project ID',
    Mandatory =$true, Position=2)]
    [String]$projectIDColumnName,
    
    [Parameter(
        HelpMessage = 'Column Name in DataRow and PW Asset Environment for the Asset ID',
    Mandatory =$true, Position=3)]
    [String] $assetIDColumnName,
    
    [Parameter(
        HelpMessage = 'Column Name in DataRow and PW Asset Environment for the Asset Name',
    Mandatory =$true, Position=4)]
    [String] $assetNameColumnName,

    ###########################################################################################
    # Script and Log File Information
    ###########################################################################################
    [ValidateNotNullOrEmpty()]
    [ValidateScript( { Test-Path -Path $_ -PathType Container })]
    [Parameter( 
        HelpMessage = 'Location of script file and log folder.',
        Mandatory = $true,
    Position = 5 )]
    [string] $Path,    
    [ValidateNotNullOrEmpty()]
    [Parameter(
        HelpMessage = 'Log file to be generated for each run of the script.',
        Mandatory = $true,
    Position = 6 )]
    [string] $LogFilePathName,
    [Parameter(
        HelpMessage = 'Name of cmdlet to place in log file',
        Mandatory = $true,
    Position = 7 )]
    [string] $CmdletName
            
  )
  
  # Set Project Code, Asset ID, and Asset Name to check in document
  $projectID = $assetRow.$projectIDColumnName
  $assetID = $assetRow.$assetIDColumnName
  $assetName = $assetRow.$assetNameColumnName
  
  # Compare assetRow to assetDocument attributes
  $htAssetDocumentAttributes = $assetDocument.CustomAttributes
            
  # Reset $htUpdatedAttributes
  $htUpdatedAttributes = @{}
  
  
  # Compare values in each $assetRow.Column to the corresponding key in $htAssetDocumentAttributes
  foreach ($columnName in $assetRow.Table.Columns.ColumnName) {
    # Process only if NOT projectID or assetID
    if ($columnName -notin $projectIDColumnName, $assetIDColumnName) {
      
      # Reset $ValueSource and $ValueTarget
      if ($valueSource) {Remove-Variable -Name valueSource}
      if ($valueTarget) {Remove-Variable -Name valueTarget}
      
      # Determine if row column is a Value or String
      if ($($assetRow.$columnName) -match "^[-]?[0-9.]+$") {
        $valueSource = $assetRow.$columnName
        $valueTarget = [decimal]$htAssetDocumentAttributes.$columnName
      }
      elseif ($($assetRow.$columnName).GetType().Name -eq 'DBNULL'){
        # Check if Target Value is 0
        try {
          if ([int]$htAssetDocumentAttributes.$columnName -eq 0) {
            # Set Source and Target to 0
            $valueSource = 0
            $valueTarget = 0
          }
          else {
            $valueSource = 0
            $valueTarget = $htAssetDocumentAttributes.$columnName
          }
        }
        catch {
          # Use as null and null
          $valueSource = ''
          $valueTarget = ''
        }
      }
      else {
        # Treat Source value as string
        $valueSource = [string]$assetRow.$columnName
        $valueTarget = [string]$htAssetDocumentAttributes.$columnName
      }
      
      if ($valueSource -ne $valueTarget) {
        $htUpdatedAttributes.$columnName = $valueSource
      }
    }
  } # END For Each Attribute
  
  # Check $htUpdatedAttributes count
  # Update Document ONLY if Attributes were updated (ie.$htUpdatedAttributes.Count -gt 0)
  if ($htUpdatedAttributes.Count -gt 0) {
    Update-PWDocumentAttributes -InputDocuments $assetDocument -Attributes $htUpdatedAttributes | Out-Null
    $msg = ('{0}/{1} ({2}) EXISTS - The following attributes were updated:' -f $projectID, $assetID, $assetName)
    Write-PWPSLog -Message ('[PROCESS] {0}' -f $msg) -Path $LogFilePathName -Level Info -Cmdlet $CmdletName
    $msg = ("{0}/{1} ({2})'{3}'" -f $projectID, $assetID, $assetName, ($htUpdatedAttributes.Keys -join "', '"))
    Write-PWPSLog -Message ('[PROCESS] {0}' -f $msg) -Path $LogFilePathName -Level Info -Cmdlet $CmdletName
  }
  else {
    $msg = ('{0}/{1} ({2}) EXISTS - NO CHANGES MADE' -f $projectID, $assetID, $assetName)
    Write-PWPSLog -Message ('[PROCESS] {0}' -f $msg) -Path $LogFilePathName -Level Info -Cmdlet $CmdletName
  }
}
Export-ModuleMember -Function Update-CDOTPWAssetDocument