Scripts/WorkAreaUpdates/CDOT_UpdatePWProjectCatalogDocs_v01.ps1
|
##################################################################################################################################################################################################################### ##############################| |#################################################################################################################################################################################### ##############################| |# ############## ########| |# Name : CDOT_UpdatePWAssetCatalogDocs_v01 ########### #####| |# Type : Script ######### ####### ####| |# Purpose : Update ProjectWise Documents with Asset Info from a SQL Query ######## ############ ####| |# Author : Adrian J. Crowfoot ####### ##### ##### ####| |# Creation Date : 10/23/2019 ############## ##### #####| |# Modified By : Adrian J. Crowfoot ############# ### ######| |# Modified Date : 12/04/2019 ########## #######| |# Script Version : 1.2 ######## ##########| |# 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 20191203] Finished BEGIN block ## - [AJC 20191204] Finished PROCESS Block to create and/or update Project Catalog documents ## - [AJC 20200127] Reworked to accept two parameter sets: DataTable ($dtPWRichProjects, $PWProjectNumberProperty) or ArrayList ($ProjectCodes) ## ##################################################################################################################################################################################################################### ## ## 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.3 .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 CDOT_UpdatePWProjects_v02 CDOT_UpdatePWProjectCatalogDocs_v01 .EXTERNALSCRIPTDEPENDENCIES .RELEASENOTES #> <# .DESCRIPTION Update ProjectWise Documents with Asset Info from a SQL Query #> [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 ########################################################################################### # Parameters for Comparing Asset Data in CDOTDATA vs. ProjectWise Environment ########################################################################################### [Parameter( HelpMessage = "Name of Environment that contains Asset Information", Mandatory =$true)] [string] $PWEnvironmentName, # = 'CDOT Data - Project Catalog', [Parameter( HelpMessage = "Project Number Attribue Name", Mandatory =$true)] [string] $PWProjectNumberAttribute, # = 'projCode', [Parameter( HelpMessage = "Name of attribute to compare", Mandatory =$true)] [string] $PWProjectTimeStampUpdateAttribute, # = 'projChangeDate', [Parameter( Mandatory = $false )] [string] $PWFolderRootPath = "04 Admin Configuration\Project Catalog", [Parameter( Mandatory = $false )] [string] $PWAssetDocumentPrefix = "PRJ", ########################################################################################### # 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', # Edit the SQL Query file to get the correct columns from CDOTDATA [Parameter( HelpMessage = "SQL Query file that returns the input datatable", Mandatory = $true )] [string] $SqlQueryFile, # = "$(Get-CDOTModuleLocation)\CDOTDATA_ProjectInfo.sql", ########################################################################################### # 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 ProjCatalog 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" } $SqlWhereCondition = "WHERE $PWProjectNumberAttribute IN (" + "'$($projectList -join "','")'" + ")" Write-PWPSLog -Message "[BEGIN] $($projectList.Count) distinct $PWProjectNumberAttribute 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" } $SqlWhereCondition = "WHERE $PWProjectNumberAttribute IN (" + "'$($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" # A. Build the SQL Query from the SQL Query File and the SQL Where Condition $SqlQuery = $(Get-Content $SqlQueryFile -Raw) + $SqlWhereCondition # B. Create Data Table using Select-SQLDataTable function in PWPS_CDOT $dtCDOTDATA = Select-CDOTSQLDataTable -SQLConnectionString $SQLConnectionString -SqlQuery $SqlQuery -TableName 'Properties' if($dtCDOTDATA.Rows.Count -eq 0) { throw "No Rows in Data table" } Write-PWPSLog -Message "[BEGIN] $($dtCDOTDATA.Rows.Count) Distinct Projects in Data table" -Path $LogFilePathName -Level Info -Cmdlet $CmdletName } 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 #region Check ProjectWise Environment and Column Name try { if ($PWEnvironmentName -notin $(Get-PWEnvironments).Name) { throw "$PWEnvironmentName NOT found. Please add it to $PWDatasource." } Write-PWPSLog -Message "[BEGIN] $PWEnvironmentName found in ProjectWise datasource" -Path $LogFilePathName -Level Info -Cmdlet $CmdletName # Find $PWProjectNumberAttribute in list of $PWEnvironmentName columns. If not, exit script. $PWEnvironmentAttributeNames = $(Get-PWEnvironmentColumns -EnvironmentName $PWEnvironmentName).Name if($PWProjectNumberAttribute -notin $($PWEnvironmentAttributeNames)) { throw "$PWProjectNumberAttribute NOT found in $PWEnvironmentName. Please add it to $PWEnvironmentName." } Write-PWPSLog -Message "[BEGIN] $PWProjectNumberAttribute found in $PWEnvironmentName." -Path $LogFilePathName -Level Info -Cmdlet $CmdletName } 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 ProjectWise Environment and Column Name } # end BEGIN PROCESS { # ALL INFORMATION IS LOADED IN $dtCDOTDATA but does not include rogue project numbers # $projectList contains all unique project codes (including bogus ones) - only use real ones # Process each Project Code in Project Array to find which ones need SAP Update foreach ($row in $dtCDOTDATA.Rows) { $projectCode = $row.$PWProjectNumberAttribute # Create new document if not exists - store document in $docToUpdate if ( -not ($docToUpdate = Get-PWDocumentsBySearch -Environment $PWEnvironmentName -Attributes @{$PWProjectNumberAttribute = $projectCode } -FolderPath $PWFolderRootPath -GetAttributes)) { $Splat_NewDoc = @{ InputFolders = (Get-PWFolders -FolderPath $PWFolderRootPath -JustOne) DocumentName = "$PWAssetDocumentPrefix$projectCode" Description = "$PWAssetDocumentPrefix$projectCode" Count = 1 DoNotAddSuffix = $true } $docToUpdate = New-PWDocumentAbstract @Splat_NewDoc -Verbose } if ($docToUpdate) { # Get update date in CDOTDATA $databaseAttributeValue = $row.$PWProjectTimeStampUpdateAttribute $PWAttributeValue = $docToUpdate.CustomAttributes.$PWProjectTimeStampUpdateAttribute if ($databaseAttributeValue -ne $PWAttributeValue) { # Reset HashTable for PW Document Attributes $PWDoc_Attributes = @{} # Extract one Row from Datatable $drProject = $dtCDOTDATA.Select("$PWProjectNumberAttribute = $projectCode") foreach ($attribute in $PWEnvironmentAttributeNames){ if ($drProject.$attribute) { $PWDoc_Attributes.$attribute = $drProject.$attribute } } if( -not (Update-PWDocumentAttributes -Attributes $PWDoc_Attributes -InputDocuments $docToUpdate -ReturnBoolean)){ Write-PWPSLog -Message "[PROCESS] Failed to update attributes for $($docToUpdate.Name)." -Path $LogFilePathName -Level Warn -Cmdlet $CmdletName } else { Write-PWPSLog -Message "[PROCESS] $($docToUpdate.Name) was changed." -Path $LogFilePathName -Level Info -Cmdlet $CmdletName } } else { # DOCUMENT DOES NOT NEED TO BE UPDATED Write-PWPSLog -Message "[PROCESS] No changes for $($docToUpdate.Name)." -Path $LogFilePathName -Level Info -Cmdlet $CmdletName } } # end if $docToUpdate exists else { # Document was not found and not created properly Write-PWPSLog -Message "[PROCESS] Failed to find or create document for $projectCode." -Path $LogFilePathName -Level Warn -Cmdlet $CmdletName } } # end for $row } # 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 |