Scripts/WorkAreaUpdates/CDOT_UpdatePWProjects_CallingScript_v03.ps1
|
##################################################################################################################################################################################################################### ## Requirements: ## - Ability to log into a ProjectWise Datasource with Administrative privileges. ## - Data Table using Get-PWRichProjectReport and other parameters ## - Logged into a ProjectWise datasource. ## - FUNCTIONS_Archiving.psm1 ## ##################################################################################################################################################################################################################### ## ## Description/Notes: ## - [AJC 20191127] Finished Version 2 - adds logging and continuous PW session for whole script ## - [AJC 20191203] Updated CDOT_UpdatePWProjects_v02 ## - [AJC 20200122] Finished CDOT_UpdatePWHighways_v01 ## - [AJC 20200127] Updated CDOT_UpdatePWHighways_v01 and Finished CDOT_UpdatePWStructures_v01.ps1 ## - [AJC 20200902] Fixed callout to Finished CDOT_UpdatePWStructures_v01.ps1 to use SQLServer and SQLInstance params ## - [AJC 20201002] Now logs if $dtProjectList cannot be populated because not applicable work areas are found in the ProjectWise datasource ## ##################################################################################################################################################################################################################### ## ## Roadmap: ## - [V04.0] Add environment for project roles such as Project Manager, Resident Engineer, Construction Manager and Project Engineer ## - [V04.0] Add Spatial Updater ## - ## ##################################################################################################################################################################################################################### <#PSScriptInfo .VERSION 3.2 .GUID 06dfb5cb-ad18-4cbc-af6b-0c1e505f0e0f .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 CDOT_UpdatePWHighways_v01 CDOT_UpdatePWStructures_v01 .EXTERNALSCRIPTDEPENDENCIES .RELEASENOTES #> <# .DESCRIPTION Queries a SQL database for project information then updates Rich Projects in a ProjectWise Datasource #> [CmdletBinding()] param( #region PROJECTWISE LOGIN Parameters [ValidateNotNullOrEmpty()] [Parameter( HelpMessage = "ProjectWise datasource definition.", Mandatory = $true)] $PWDataSource, # = 'cdot-pw.bentley.com:cdot-pw-04T', [ValidateNotNullOrEmpty()] [Parameter( HelpMessage = "ProjectWise user account to log into the datasource with.", Mandatory = $true )] $PWUserName, # = '_powershelluser_90159', [ValidateNotNullOrEmpty()] [Parameter( HelpMessage = "File on local machine containing the secure password for the datasource user. (Must be specific for user running the script on a given machine)", Mandatory = $true)] $SecurePasswordFile, # = "$Path\securepassword.txt", #endregion PROJECTWISE LOGIN Parameters #region SQL Parameters [ValidateNotNullOrEmpty()] [Parameter( HelpMessage = "Name of SQL Server which contains the database with SAP data", Mandatory = $true )] $SQLServer = 'IntProdListener', [ValidateNotNullOrEmpty()] [Parameter( HelpMessage = "Name of Instance on SQL Server which contains the source database", DontShow )] $SQLInstanceName = 'Default', [ValidateNotNullOrEmpty()] [Parameter( HelpMessage = "Name of the database that houses output from SAP", Mandatory = $true )] $SQLDatabase = 'CDOTDATA', #endregion SQL Parameters #region OTHER PROJECTWISE Parameters [Parameter(DontShow)] $PWProjectType = 'CDOT_INFO', [Parameter(DontShow)] $PWProjectNumberProperty = 'PROJECT_Project_Code', [Parameter(DontShow)] $PWProjectTimeStampUpdateProperty = 'PROJECT_SAP_Change_Date', [Parameter(DontShow)] $PWProjectInfoEnvironmentName = 'CDOT Data - Project Catalog', #endregion OTHER PROJECTWISE Parameters #region COMMON SCRIPT Parameters # Provides mechanism to force FULL Update and not just based on SAP Update attribute [switch] $Full, # Setting the $Path value to equal the global variable to eliminate the need to update each entry of the $Path variable. [ValidateNotNullOrEmpty()] [ValidateScript({ Test-Path -Path $_ -PathType Container })] [Parameter( HelpMessage = "Local folder to work in.", Mandatory = $false)] [string] $Path = "$(Get-CDOTModuleLocation)\Scripts\WorkAreaUpdates", [ValidateNotNullOrEmpty()] [Parameter(DontShow)] [string] $DateTime = (Get-Date -Format yyyyMMdd_hhmm), [ValidateNotNullOrEmpty()] [Parameter()] [string] $LogFilePathName = (Join-Path -Path $env:ProgramData -ChildPath "Bentley\Logs\CDOT_UpdatePWProjects_$DateTime.log"), [ValidateNotNullOrEmpty()] [Parameter()] [DateTime] $ProcessStartTime = '06:00', [ValidateNotNullOrEmpty()] [Parameter()] [DateTime] $ProcessEndTime = '20:30' #endregion COMMON SCRIPT Parameters ) BEGIN { # Test to see if the provided path exists. If not, exit. if(-not(Test-Path -Path $Path -PathType Container -Verbose -ErrorAction Stop)) { Write-Warning -Message "[BEGIN] Folder '$Path' not found. Update path variable value and try again." break } $CmdletName = 'CDOT PW Project Update Calling Script' $Splat_Log = @{ Path = $LogFilePathName Cmdlet = $CmdletName } $Continue = $true $StartTime = Get-Date Write-PWPSLog @Splat_Log -Message "[BEGIN] Start time: $StartTime" -Level Info #region DateTime Comparison <# Add datetime comparison to determine if the function is being called between the times specified in the StartTime and EndTime variables. If not, exit function. #> if( -not (Compare-CDOTStartEndDates -StartTime $ProcessStartTime -EndTime $ProcessEndTime -LogFile $LogFilePathName)) { $Message = "[BEGIN] Current time '$($StartTime.TimeOfDay)' is not between $($ProcessStartTime.TimeOfDay) and $($ProcessEndTime.TimeOfDay). Exiting function." Write-PWPSLog @Splat_Log -Message $Message -Level Warn $Continue = $false } } # end BEGIN PROCESS { if( -not ($Continue)) { Write-Warning -Message "[PROCESS] Exiting script." } else { Write-PWPSLog @Splat_Log -Message "[PROCESS] Continuing with processing script." -Level Info #region LOG INTO DATASOURCE try { # Log into Source Datasource using Single SignOn $Splat_Login = @{ DatasourceName = $PWDataSource UserName = $PWUserName PWFilePathName = $SecurePasswordFile LogFilePathName = $LogFilePathName } New-CDOTPWLogin @Splat_Login } catch { Write-PWPSLog @Splat_Log -Message "[PROCESS] $PWUserName failed to log into ProjectWise datasource '$PWDataSource'." -Level Warn return } #endregion LOG INTO DATASOURCE #region SCRIPT 01 - Get a Rich Project data table with $PWProjectType try { $Splat_GetPWProjectList = @{ PWProjectType = $PWProjectType PWProjectNumberProperty = $PWProjectNumberProperty PWProjectTimeStampUpdateProperty = $PWProjectTimeStampUpdateProperty Path = $Path LogFilePathName = $LogFilePathName } $dtProjectList = . $Path\CDOT_GetPWRichProjectDataTable_v01.ps1 @Splat_GetPWProjectList # -Verbose } catch { Write-PWPSLog @Splat_Log -Message "[PROCESS] No '$PWProjectType' Work Areas found ProjectWise datasource '$PWDataSource'." -Level Warn return } #endregion SCRIPT 01 - Get a Rich Project data table with $PWProjectType #region SCRIPT 02 - Update Work Area Props $Splat_PWProjectUpdate = @{ dtPWRichProjects = $dtProjectList PWProjectType = $PWProjectType PWProjectNumberProperty = $PWProjectNumberProperty PWProjectTimeStampUpdateProperty = $PWProjectTimeStampUpdateProperty SQLInstanceName = $SQLInstanceName SQLDatabase = $SQLDatabase SQLServer = $SQLServer SqlQueryFile = "$Path\CDOTDATA_ProjectInfo_v02.sql" Path = $Path LogFilePathName = $LogFilePathName Full = $Full } . $Path\CDOT_UpdatePWProjects_v02.ps1 @Splat_PWProjectUpdate # -Verbose #endregion SCRIPT 02 - Update Work Area Props #region SCRIPT 03 - Do ProjCatalog, Project Highway and Project Structures Lookup environment Updates Here # Get distinct $PWProjectNumberProperty in datasource of $PWProjectType as a string separated by commas # example: "'22776','22831','22901','22983'" $sqlStatement = "SELECT DISTINCT $PWProjectNumberProperty FROM I_$PWProjectType WHERE $PWProjectNumberProperty <> '0' AND $PWProjectNumberProperty <> ''" $dtProjectList = Select-PWSQLDataTable -SQLSelectStatement $sqlStatement # Update Project Catalog $Splat_PWProjCatalogUpdate = @{ dtPWRichProjects = $dtProjectList PWProjectNumberProperty = $PWProjectNumberProperty PWEnvironmentName = $PWProjectInfoEnvironmentName PWProjectNumberAttribute = 'projCode' PWProjectTimeStampUpdateAttribute = 'projChangeDate' PWFolderRootPath = "04 Admin Configuration\Project Catalog" SQLServer = $SQLServer SQLInstanceName = $SQLInstanceName SQLDatabase = $SQLDatabase SqlQueryFile = "$Path\CDOTDATA_AllProjectInfo_v01.sql" Path = $Path LogFilePathName = $LogFilePathName PWAssetDocumentPrefix = "PRJ" } . $Path\CDOT_UpdatePWProjectCatalogDocs_v01.ps1 @Splat_PWProjCatalogUpdate #-Verbose # Update Project Highways $Splat_UpdatePWHighwaysWithDataTable = @{ dtPWRichProjects = $dtProjectList PWProjectNumberProperty = $PWProjectNumberProperty projectIDColumnName = 'projCode' inputTableName = 'ProjHwy' assetIDColumnName = 'assetID' assetNameColumnName = 'assetCode' PWDocumentNamePrefix = 'HW' PWFolderRootPath = '04 Admin Configuration\Project Highways\' assetPWEnvironmentName = 'CDOT Data - Project Highways' SQLServer = $SQLServer SQLInstanceName = $SQLInstanceName SQLDatabase = $SQLDatabase Path = $Path LogFilePathName = $LogFilePathName } . $Path\CDOT_UpdatePWHighways_v01.ps1 @Splat_UpdatePWHighwaysWithDataTable #-Verbose #region Updated Structures with Datatable $sqlStatement = "SELECT PROJECT_Project_Code FROM I_CDOT_INFO WHERE PROJECT_Project_Code <> '0' AND PROJECT_Project_Code <> ''" $dtProjectList = Select-PWSQLDataTable -SQLSelectStatement $sqlStatement $Splat_UpdatePWStructuresWithStringDataTable = @{ dtPWRichProjects = $dtProjectList PWProjectNumberProperty = 'PROJECT_Project_Code' inputTableName = 'PROJ_STRUCTURES' projectIDColumnName = 'projCode' assetPWEnvironmentName = 'CDOT Data - Project Structures' assetIDColumnName = 'assetID' assetNameColumnName = 'assetCode' PWDocumentNamePrefix = 'ST' PWFolderRootPath = '04 Admin Configuration\Project Structures\' SQLServer = $SQLServer SQLInstanceName = $SQLInstanceName SQLDatabase = $SQLDatabase Path = $Path LogFilePathName = $LogFilePathName } . $Path\CDOT_UpdatePWStructures_v01.ps1 @Splat_UpdatePWStructuresWithStringDataTable #-Verbose #endregion Updated Structures with Datatable #endregion SCRIPT 03 #region SCRIPT 04 --> Do Spatial Location Updates here #endregion SCRIPT 04 #region LOG OUT OF SOURCE DATASOURCE try { Undo-PWLogin Write-PWPSLog @Splat_Log -Message "[END] Logging out of ProjectWise Datasource '$(Get-PWCurrentDatasource)'." -Level Info #endregion LOG OUT OF SOURCE DATASOURCE } catch { $Line = $error[0].InvocationInfo.ScriptLineNumber $ErrorMessage = $Error[0].Exception.Message Write-PWPSLog @Splat_Log -Message "[PROCESS] Error occurred while attempting to process Projects. Occurred on line: $Line. $ErrorMessage" -Level 'Warn' } # end try / catch... #endregion LOG OUT OF SOURCE DATASOURCE } # end if($Continue... } # end PROCESS END { ############################################################################################################################### # NOTE - ADD ONE LOG OUT HERE LATER ############################################################################################################################### $EndTime = Get-Date Write-PWPSLog @Splat_Log -Message "[END] It took $([Math]::Round($EndTime.Subtract($StartTime).TotalMinutes, 2)) minutes to complete process." -Level Info #Undo-PWLogin } # end END |