Scripts/WorkAreaUpdates/CDOT_GetPWRichProjectDataTable_v01.ps1
|
##################################################################################################################################################################################################################### ##############################| |#################################################################################################################################################################################### ##############################| |# ############## ########| |# Name : CDOT_GetPWRichProjectDataTable_v01 ########### #####| |# Type : Script ######### ####### ####| |# Purpose : Output Data Table of Folder Paths and IDs for a given Rich Project Type ######## ############ ####| |# Author : Adrian J. Crowfoot ####### ##### ##### ####| |# Creation Date : 09/18/2019 ############## ##### #####| |# Modified By : Adrian J. Crowfoot ############# ### ######| |# Modified Date : 11/22/2019 ########## #######| |# Script Version : 1.3 ######## ##########| |# 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.2 ####### ########## #######| |# ###### ######### ########| |# Requirements: ###### #### ########| |# - Ability to log into a ProjectWise Datasource with Administrative privileges. ###### ##########| |# - Logged into a ProjectWise datasource. ######### #############| |# - FUNCTIONS_Archiving.psm1 ##############################| |# - ##############################| |# ##################################################################################################################################################################################################################### ## ## Description/Notes: ## - ## - [AJC 20191122] Reworked script to require an active PW Login and to simplify logging. ## - [AJC 20200925] BUG: Line 135 - Rich Project Properties were not being captured ## - ##################################################################################################################################################################################################################### ## ## 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.12.3.0'} #Requires -Modules @{ModuleName="PWPS_CDOT";ModuleVersion='1.0.0.1'} ##Requires -RunAsAdministrator <#PSScriptInfo .VERSION 0.1 .GUID 06dfb5cb-ad18-4cbc-af6b-0c1e505f0e0f .AUTHOR adrian.crowfoot@state.co.us .COMPANYNAME Colorado DOT .TAGS ProjectWise .EXTERNALMODULEDEPENDENCIES FUNCTIONS_Archiving PWPS_DAB .RELEASENOTES #> <# .DESCRIPTION Output Data Table of Folder Paths and IDs for a given Rich Project Type #> [CmdletBinding()] param( ########################################################################################### # Parameters for Rich Project Comparison Parameters ########################################################################################### [Parameter( Mandatory = $false )] [string] $PWProjectType = 'CDOT_INFO', [Parameter( Mandatory = $false )] [string] $PWProjectNumberProperty = 'PROJECT_Project_Code', [Parameter( Mandatory = $false )] [string] $PWProjectTimeStampUpdateProperty = 'PROJECT_SAP_Change_Date', ########################################################################################### # 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 Get PW Rich Project Datatable script." -Path $LogFilePathName -Level Info -Cmdlet $CmdletName $ScriptStartTime = Get-Date Write-PWPSLog -Message "[BEGIN] Start time: $ScriptStartTime" -Path $LogFilePathName -Level Info -Cmdlet $CmdletName } # end BEGIN PROCESS { try { Write-PWPSLog -Message "[PROCESS] Continuing with processing script." -LogPath $LogFilePathName -Level Info -Cmdlet $CmdletName if ($PWProjectTypeClass = Get-PWRichProjectTypes | Where-Object {$_.ClassName -eq $PWProjectType}) { $sqlPWProjectClassStatement = "SELECT o_projguid FROM dms_proj WHERE o_classid = $($PWProjectTypeClass.ClassID)" $dtFoldersToProcess = Select-PWSQL -SQLSelectStatement $sqlPWProjectClassStatement # Add ExistingProjectPath and $PWProjectNumberProperty Columns $dtFoldersToProcess.Columns.Add("ExistingProjectPath") | Out-Null $dtFoldersToProcess.Columns.Add("$PWProjectNumberProperty") | Out-Null $dtFoldersToProcess.Columns.Add("$PWProjectTimeStampUpdateProperty") | Out-Null Write-PWPSLog -Message "[PROCESS] Datasource houses $($dtFoldersToProcess.Rows.Count) PW Work Areas with $PWProjectType Work Area Type." -LogPath $LogFilePathName -Level Info -Cmdlet $CmdletName # Cycle through these Project GUIDs to add ExistingProjectPath and $PWProjectNumberProperty (uses access control) foreach ($f in $dtFoldersToProcess){ # Get Folder Path then Project Number if($PWProject = Get-PWFoldersByGUIDs -FolderGUIDs $f.o_projguid | Get-PWFolderPathAndProperties) { Write-PWPSLog -Message "[PROCESS] GETTING PW INFO: $($PWProject.FullPath)" -LogPath $LogFilePathName -Level Info -Cmdlet $CmdletName $f.ExistingProjectPath = $PWProject.FullPath $f.$PWProjectNumberProperty = $($PWProject.ProjectProperties.$PWProjectNumberProperty) $f.$PWProjectTimeStampUpdateProperty = $($PWProject.ProjectProperties.$PWProjectTimeStampUpdateProperty) } } # Remove rows that have no ExistingProjectPath - user does not have access to these $dataRowsToRemove = $dtFoldersToProcess.Select("ExistingProjectPath IS NULL") foreach ($r in $dataRowsToRemove){ $dtFoldersToProcess.Rows.Remove($r) } # Remove o_projectno Column $dtFoldersToProcess.Columns.Remove("o_projguid") $dtFoldersToProcess.TableName = $PWProjectType } else { Write-Error "'$PWProjectType' is not a vald Work Area Type" $Line = $($Error[0].InvocationInfo.ScriptLineNumber) $ErrorMessage = $($Error[0].Exception.Message) Write-PWPSLog -Message "[PROCESS] Error occurred while attemnpting to get datatable. Occurred on line: $Line. $ErrorMessage" -Path $LogFilePathName -Level Error -Cmdlet $CmdletName return } } # end if($Continue... catch { $Line = $($Error[0].InvocationInfo.ScriptLineNumber) $ErrorMessage = $($Error[0].Exception.Message) Write-PWPSLog -Message "[PROCESS] Error occurred while attemnpting to get datatable. Occurred on line: $Line. $ErrorMessage" -Path $LogFilePathName -Level Warn -Cmdlet $CmdletName break } } # 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 Write-Output @(,($dtFoldersToProcess)) # Return the Data Table #Return $ProjectWiseProjectsDataTable #Write-Output @(,($ProjectWiseProjectsDataTable)) } # end END |