Scripts/WorkAreaUpdates/CDOT_UpdatePWHighways_v01.ps1

#####################################################################################################################################################################################################################
##############################| |####################################################################################################################################################################################
##############################| |#
############## ########| |# Name : CDOT ProjectWise Project Highways Document Update Tool
########### #####| |# Type : Script
######### ####### ####| |# Purpose : Update ProjectWise Documents with Project Info from CDOTDATA Database
######## ############ ####| |# Author : Adrian Crowfoot
####### ##### ##### ####| |# Creation Date : 04/02/2019
############## ##### #####| |# Modified By : Adrian J. Crowfoot
############# ### ######| |# Modified Date : 01/23/2020
########## #######| |# Script Version : 1.0
######## ##########| |# PowerShell Version : 5.1.16299.1146
######## ##############| |# ProjectWise Version : 10.00.03.167
######### #########| |# PWPS Module Version : 10.0.2.1
######## ###### ########| |# PWPS_DAB Module Version : 1.16.3.0
####### ######### #######| |# PWPS_CDOT Module Version : 1.0.1.0
####### ########## #######| |#
###### ######### ########| |# Requirements:
###### #### ########| |# - Ability to log into a ProjectWise Datasource with Administrative privileges.
###### ##########| |# - Data Table using Get-PWRichProjectReport and other parameters
######### #############| |# - Logged into a ProjectWise datasource.
##############################| |# -
##############################| |#
#####################################################################################################################################################################################################################
##
## Description/Notes:
## - [AJC 20191122] Reworked script to require an active PW Login and to simplify logging.
## - [AJC 20200122] Reworked to accept two parameter sets: DataTable ($dtPWRichProjects, $PWProjectNumberProperty) or ArrayList ($ProjectCodes)
## - [AJC 20200123] Updated to account for column renames and new column in CDOT Data - Project Highways environment
## - [AJC 20200127] Updated to leverage two new functions in PWPS_CDOT: New-CDOTPWAssetDocument and Update-CDOTPWAssetDocument
##
#####################################################################################################################################################################################################################
##
## This script is provided for example purposes only. Bentley makes no warranty as to the effectiveness of this script in a specific situation or for a specific user.
## Use of this script or a similar script in a production environment is entirely at the risk of the user. Bentley suggests extensive testing before deployment.
##
#####################################################################################################################################################################################################################

#Requires -Version 5.0
#Requires -Modules @{ModuleName="PWPS_DAB";ModuleVersion='1.16.0.0'}
#Requires -Modules @{ModuleName="PWPS_CDOT";ModuleVersion='1.0.1.0'}
##Requires -RunAsAdministrator

<#PSScriptInfo
 
.VERSION 1.0
 
.GUID 10ec7184-50aa-4e4f-87b4-9d571a2782fd
 
.AUTHOR adrian.crowfoot@state.co.us
 
.COMPANYNAME Colorado DOT
 
.TAGS ProjectWise
 
.EXTERNALMODULEDEPENDENCIES
 PWPS_DAB
 PWPS_CDOT
 
.REQUIREDSCRIPTS
 CDOT_GetPWRichProjectDataTable_v01
 
.EXTERNALSCRIPTDEPENDENCIES
 
.RELEASENOTES
 
#>



[CmdletBinding()]
param(

    #############################################################################################
    # Parameters to select pertinent data from a Data Table (DataTable Parameter Set)
    #############################################################################################
    [Parameter(
            HelpMessage = 'Datatable listing input project paths and project numbers',
            Mandatory =$true,
            ParameterSetName ='DataTable')]
    [System.Data.DataTable] $dtPWRichProjects, # Input Rich Project Datatable
    [Parameter(
            HelpMessage = 'Work Area Properties Type',
            Mandatory =$true,
            ParameterSetName ='DataTable')]
    [String]$PWProjectNumberProperty,

    #############################################################################################
    # Parameters to select pertinent data from a list of projects (ArrayList Parameter Set)
    #############################################################################################
    [Parameter(
            HelpMessage = 'Array listing input project numbers',
            Mandatory =$true,
            ParameterSetName ='ArrayList')]
    [String[]] $ProjectCodes, # Input Rich Project Datatable
    
    ###########################################################################################
    # Define SQL Parameters
    ###########################################################################################

    # SQL Server Information (uses Windows Authentication)
    [Parameter(
            HelpMessage = "SQL Server that houses input database and table",
            Mandatory = $true )]
    [string] $SQLServer, # = 'IntProdListener',
    [Parameter(
            HelpMessage = "SQL Server Instance that houses input database and table",
            Mandatory = $true )]
    [string] $SQLInstanceName, # = 'Default',
    [Parameter(
            HelpMessage = "SQL database that contains input table(s)",
            Mandatory = $true )]
    [string] $SQLDatabase, # = 'CDOTDATA',

    ###########################################################################################
    # Define Parameters for the Input Table
    ###########################################################################################

    # Define table to read
    [Parameter(
            HelpMessage = "Name of Table that contains the assets",
            Mandatory = $true )]
    [string] $inputTableName, # = 'ProjHwy',

    # Define project and asset ID columns and additional column names
    # NOTE: For now, the column names in the input datable need to match the column names in the PW Environment
    [Parameter(
            HelpMessage = "Project Code Column Name (must match column name in PW Environment)",
            Mandatory = $true )]
    [string] $projectIDColumnName, # = 'projCode',
    [Parameter(
            HelpMessage = "Asset Code Column Name (must match column name in PW Environment)",
            Mandatory = $true )]
    [string] $assetIDColumnName, # = 'assetID',
    [Parameter(
            HelpMessage = "Asset Name Column Name (must match column name in PW Environment)",
            Mandatory = $true )]
    [string] $assetNameColumnName, # = 'assetCode',

    ###########################################################################################
    # Define Parameters for the Environment that tracks the Assets
    ###########################################################################################
    [Parameter(
            HelpMessage = "PW Environment that tracks the Assets",
            Mandatory = $true )]
    [string] $assetPWEnvironmentName, # = 'CDOT Data - Project Highways',
    [Parameter(
            HelpMessage = "Prefix for naming assets",
            Mandatory = $true )]
    [string] $PWDocumentNamePrefix, # = "HW",
    [Parameter(
            HelpMessage = "PW Path to store asset documents",
            Mandatory = $true )]
    [string] $PWFolderRootPath, # = "04 Admin Configuration\Project Highways\",

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

)

BEGIN {
    $CmdletName = $MyInvocation.MyCommand.Name
    
    Write-PWPSLog -Message " - " -Path $LogFilePathName -Level Info -Cmdlet $CmdletName
    Write-PWPSLog -Message "[BEGIN] Entering Update ProjHwy Documents script." -Path $LogFilePathName -Level Info -Cmdlet $CmdletName
    
    $ScriptStartTime = Get-Date
    Write-PWPSLog -Message "[BEGIN] Start time: $ScriptStartTime" -Path $LogFilePathName -Level Info -Cmdlet $CmdletName
    
    #region Check Datatable for Unique Values and rows
        try {
            if ($dtPWRichProjects) { # Process datatable
                $projectList = $dtPWRichProjects.Rows.$PWProjectNumberProperty | Sort-Object -Unique | Where-Object $PWProjectNumberProperty -ne 0 | Where-Object $PWProjectNumberProperty -ne ''
                if($projectList.Count -eq 0) {
                    throw "No Projects to Process"
                }
                $strAllProjects = "'$($projectList -join "','")'"
                Write-PWPSLog -Message "[BEGIN] $($projectList.Count) distinct $($projectList.Count) values in Data table" -Path $LogFilePathName -Level Info -Cmdlet $CmdletName
            }
            else { # Process array
                $projectList = $ProjectCodes | Sort-Object -Unique
                if($projectList.Count -eq 0) {
                    throw "No Projects to Process"
                }
                $strAllProjects = "'$($projectList -join "','")'"
                Write-PWPSLog -Message "[BEGIN] $($projectList.Count) distinct Project Codes in Array" -Path $LogFilePathName -Level Info -Cmdlet $CmdletName
            }
            

            # SQL Connection string to load from correct instance of CDOTDATA
            $SQLConnectionString = "Server=$SQLServer; Database=$SQLDatabase; Integrated Security=true;Connect Timeout=30"

        }
        catch {
            $Line = $($Error[0].InvocationInfo.ScriptLineNumber)
            $ErrorMessage = $($Error[0].Exception.Message)      
            Write-PWPSLog -Message "[BEGIN] Occurred on line: $Line. $ErrorMessage" -Path $LogFilePathName -Level Warn -Cmdlet $CmdletName
            Write-PWPSLog -Message "[BEGIN] Exiting Script." -Path $LogFilePathName -Level Warn -Cmdlet $CmdletName
            break
        } # End Try Catch
        
    #endregion Check Datatable for Unique Values and rows

} #End BEGIN

PROCESS {
    # Build Datatable with all Asset Information for Projects stored in ProjectWise

    $routes = 1..10

    foreach ($route in $routes){
    
        # Build Query columns here

        Write-PWPSLog -Message "[PROCESS] Begin Processing ROUTE $($route.ToString("00")) for ALL Projects" -Path $LogFilePathName -Level Info -Cmdlet $CmdletName

        # All cases
        $routeInfo = "$projectIDColumnName, 'HW' assetType, $route assetID, route$route assetCode, refPt$route refPt, endRefPt$route endRefPt"
        $joinStatement = ""

        # Case ($route -le 5): tack on county[n] as county
        if ($route -le 5) {
            $routeInfo = $routeInfo + ", county$route county_code, rtrim(b.Name) county_name"
        
            # Make this LEFT join to accomodate empty values of County
            $joinStatement = "LEFT JOIN County b on county$route = b.ColoID"
        }

        # Case ($route -le 3): tack on GUID[n] as GUID, latitude[n] as latitude, longitude[n] as longitude
        if ($route -le 3) {
            $routeInfo = $routeInfo + ", GUID$route GUID, latitude$route latitude, longitude$route longitude"
        }

        # Case ($route -le 2): tack on ROWrefPt[n] as ROWendRefPt, ROWendRefPt[n] as ROWendRefPt
        if ($route -le 2) {
            $routeInfo = $routeInfo + ", ROWrefPt$route ROWRefPt, ROWendRefPt$route ROWendRefPt"
        }

        # Case ($route -eq 1): tack on consult geoLoc, lenType, LRSDate, projGeoUpdateDate, projGeoWKT, resOrgn, terrainType
        if ($route -eq 1) {
            $routeInfo = $routeInfo + ", geoLoc, lenType, LRSDate, projGeoUpdateDate, projGeoWKT, resOrgn, terrainType"
        }


        # Assemble SQL Query with the correct Route Information
        $SqlAssetQuery = "SELECT $routeInfo FROM $inputTableName $joinStatement WHERE route$route IS NOT NULL AND $projectIDColumnName IN ($strAllProjects)"

        # Create datatable for all assets that meet the query
        $dtAssetsInRange = Select-CDOTSQLDataTable -SQLConnectionString $SQLConnectionString -SqlQuery $SqlAssetQuery -TableName $inputTableName

    
    
        if ($route -eq 1) {
            # Initiate Master Asset Table
            $dtAllAssets = $dtAssetsInRange
        }
        else {
            # Add to Master Asset Table
            $dtAllAssets.Merge($dtAssetsInRange)
        }
    } # end ForEach $route
    
    Write-PWPSLog -Message "[PROCESS] $($dtAllAssets.Rows.Count) Routes Found in Input Table" -Path $LogFilePathName -Level Info -Cmdlet $CmdletName
    
    # Cycle through rows
    foreach ($assetRow in $dtAllAssets.Rows) {
        
        $projectID = $assetRow.$projectIDColumnName
        $assetID = $assetRow.$assetIDColumnName
        $assetName = $assetRow.$assetNameColumnName

        $assetLookupAttributes = @{
            $projectIDColumnName = $projectID
            $assetIDColumnName = $assetID
        }

        # Try to get document using $asetIDColumnName and $projectIDColumnName
        if($assetDocument = Get-PWDocumentsBySearch -Attributes $assetLookupAttributes -Environment $assetPWEnvironmentName -GetAttributes){
    
                    # Update EXISTING Asset document using Update-CDOTPWAssetDocument Function in PWPS_CDOT
                    $Splat_UpdateAssetDocument = @{
                        assetDocument = $assetDocument
                        assetRow = $assetRow
                        projectIDColumnName = $projectIDColumnName
                        assetIDColumnName = $assetIDColumnName
                        assetNameColumnName = $assetNameColumnName
                        Path = $Path
                        LogFilePathName = $LogFilePathName
                        CmdletName = $CmdletName
                    }                    
                    Update-CDOTPWAssetDocument @Splat_UpdateAssetDocument 
                    $msg = "$projectID/$assetID ($assetName) EXISTS - FINISHED PROCESSING"
                    Write-PWPSLog -Message "[PROCESS] $msg" -Path $LogFilePathName -Level Info -Cmdlet $CmdletName

                } # end if document exists
        else {
                    # Create NEW Asset document using New-CDOTPWAssetDocument Function in PWPS_CDOT
                    $Splat_NewAssetDocument = @{
                        assetRow = $assetRow
                        projectIDColumnName = $projectIDColumnName
                        assetIDColumnName = $assetIDColumnName
                        PWDocumentNamePrefix = $PWDocumentNamePrefix
                        PWFolderRootPath = $PWFolderRootPath
                        Path = $Path
                        LogFilePathName = $LogFilePathName
                        CmdletName = $CmdletName
                    }
                    New-CDOTPWAssetDocument @Splat_NewAssetDocument
                    Write-PWPSLog -Message "[PROCESS] [$projectID/$assetID] ($assetName) ROUTE WAS ADDED" -Path $LogFilePathName -Level Info -Cmdlet $CmdletName

                } # end else to create new asset document


    } #END For Each $assetRow

    # Search for all documents in $assetPWEnvironmentName that are NOT in list of GUIDs | Remove-PWDocuments
    
} # end PROCESS

END {
    $ScriptEndTime = Get-Date
    Write-PWPSLog -Message "[END] It took $([Math]::Round($ScriptEndTime.Subtract($ScriptStartTime).TotalMinutes, 2)) minutes to complete the process." -Path $LogFilePathName -Level Info -Cmdlet $CmdletName

    Write-PWPSLog -Message "[END] Leaving script." -Path $LogFilePathName -Level Info -Cmdlet $CmdletName
    Write-PWPSLog -Message " - " -Path $LogFilePathName -Level Info -Cmdlet $CmdletName
     
} # end END