Scripts/WorkAreaUpdates/CDOT_UpdatePWStructures_v01.ps1
|
##################################################################################################################################################################################################################### ##############################| |#################################################################################################################################################################################### ##############################| |# ############## ########| |# Name : CDOT ProjectWise Project Structures Document Update Tool ########### #####| |# Type : Script ######### ####### ####| |# Purpose : Update ProjectWise Documents with Project Info from CDOTDATA Database ######## ############ ####| |# Author : Adrian Crowfoot ####### ##### ##### ####| |# Creation Date : 01/23/2020 ############## ##### #####| |# Modified By : ############# ### ######| |# Modified Date : ########## #######| |# Script Version : 1.1 ######## ##########| |# 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 20200123] Initial creation ## - [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.1 .GUID .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, # = 'PROJ_STRUCTURES', # 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, # = "ST", [Parameter( HelpMessage = "PW Path to store asset documents", Mandatory = $true )] [string]$PWFolderRootPath, # = "04 Admin Configuration\Project Structures\", ########################################################################################### # Script and Log File Information ########################################################################################### [ValidateNotNullOrEmpty()] [ValidateScript( { Test-Path -Path $_ -PathType Container })] [Parameter( HelpMessage = "Location of script file.", 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 Project Structures 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" } 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" } 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 ######################################################################################### ######################################################################################### # FINISH PROCESS TOMORROW - 01/24/2020 # We have $SQLConnectionString and $projectList[] from BEGIN block # Cycle through each project in array foreach ($projectCode in $projectList) { # We have the following to build the table # $SQLConnectionString # $inputTableName = 'PROJ_STRUCTURES' # $assetPWEnvironmentName = 'CDOT Data - Project Structures' # $assetIDColumnName = 'assetID' # $assetNameColumnName = 'assetCode' # Select all structure rows from SQL table for project (don't add assetID yet) $sqlAssetStatement = "SELECT PSPID $projectIDColumnName, 'ST' assetType, STRUCTUREID $assetNameColumnName, MANDT, SEQNR, IMTYPE, PROGCODE, ROUTE, REFPT, STRTYPE, RUN_LEN_REF_POINT, USEKEY, COUNTY, CON_DT, RDCS FROM $inputTableName WHERE PSPID = '$projectCode'" $dtProjectAssetRows = Select-CDOTSQLDataTable -SQLConnectionString $SQLConnectionString -SqlQuery $sqlAssetStatement -TableName $inputTableName # Select all structure documents from PW for project $arrProjectAssetDocs = Get-PWDocumentsBySearch -FolderPath $PWFolderRootPath -Attributes @{$projectIDColumnName = $projectCode} $NumberOfProjectAssetRows = $dtProjectAssetRows.Rows.Count $NumberOfProjectAssetDocs = $arrProjectAssetDocs.Count if ($NumberOfProjectAssetRows -ge 1) { Write-PWPSLog -Message "[PROCESS] $projectCode - Found $($dtProjectAssetRows.Rows.Count) Structures in Input Table ($inputTableName)" -Path $LogFilePathName -Level Info -Cmdlet $CmdletName # Add column to table for Asset ID $dtProjectAssetRows.Columns.Add($assetIDColumnName,'int') foreach ($assetRow in $dtProjectAssetRows.Rows) { # Set projectId, assetID and assetName $projectID = $assetRow.$projectIDColumnName $assetID = $dtProjectAssetRows.Rows.IndexOf($assetRow) + 1 $assetName = $assetRow.$assetNameColumnName # Assign assetID to assetRow as row number $assetRow.$assetIDColumnName = $dtProjectAssetRows.Rows.IndexOf($assetRow)+1 $assetLookupAttributes = @{ $projectIDColumnName = $assetRow.$projectIDColumnName $assetIDColumnName = $assetRow.$assetIDColumnName } 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 = "$projectCode/$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] [$projectCode/$assetID] ($assetName) ROUTE WAS ADDED" -Path $LogFilePathName -Level Info -Cmdlet $CmdletName } # end else to create new asset document } # next $assetRow } # end if ($NumberOfProjectAssetRows -ge 1) else { Write-PWPSLog -Message "[PROCESS] $projectCode - No Structures Found in Input Table ($inputTableName)" -Path $LogFilePathName -Level Info -Cmdlet $CmdletName } } # Next projectCode # Get Table from CDODATA # Cycle through each line in table # If document exists, check attribute values # Create new hash table to store updated values # If attribute value differs, # Add to hash table # Else # Don't add to Hash Table # If hash table has keys, update attributes to document # Else, create new document and add attributes ######################################################################################### ######################################################################################### } # 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 |