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 |