pwps_cdot

1.0.2.3

Scripts/WorkAreaUpdates/CDOT_UpdatePWProjects_v02.ps1

#####################################################################################################################################################################################################################
##############################| |####################################################################################################################################################################################
##############################| |#
############## ########| |# Name : PWPS_CDOT_UpdatePWProjects_v02
########### #####| |# Type : Script
######### ####### ####| |# Purpose : Update Project Information in Cloud Datasource
######## ############ ####| |# Author : Adrian J. Crowfoot
####### ##### ##### ####| |# Creation Date : 08/01/2019
############## ##### #####| |# Modified By : Adrian J. Crowfoot
############# ### ######| |# Modified Date : 12/03/2019
########## #######| |# Script Version : 1.3
######## ##########| |# PowerShell Version : 5.1.16299.1146
######## ##############| |# ProjectWise Version : 10.00.03.167
######### #########| |# PWPS Module Version : 10.0.2.2
######## ###### ########| |# PWPS_DAB Module Version : 1.16.3.0
####### ######### #######| |# PWPS_CDOT Module Version : 1.0.2.3
####### ########## #######| |#
###### ######### ########| |# 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 20190917] Added in routine to update project properties only if SAP Changes have been made since last run
## - [AJC 20190917] If Project Number property is blank, it is filled in with the first five characters of the project's folder name
## - [AJC 20191127] Reworked script to require an active PW Login and to simplify logging.
## - [AJC 20191203] Line 207 - $dtPWRichProject is now filtered down to only the parent rich project
## - [AJC 20200925] Line 201 - Needed to push output from Get-PWFolders over pipeline to Get-PWFolderPathAndProperties to really get Rich Project Properties
## - [AJC 20200925] Line 267 - Now skips Update date and user if no update is needed
## - [AJC 20200928] Line 218 - New switch parameter -Full will update any property that changed regardless of SAP change date
##
#####################################################################################################################################################################################################################
##
## 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 2.6
 
.GUID a3474eff-53fd-47b3-a11c-b5c6efabfe9f
 
.AUTHOR adrian.crowfoot@state.co.us
 
.COMPANYNAME Colorado DOT
 
.TAGS ProjectWise
 
.EXTERNALMODULEDEPENDENCIES
 PWPS_CDOT
 PWPS_DAB
 
.RELEASENOTES
 
#>

<#
 
.DESCRIPTION
 Update Rich Project Information in Datasource for a given Rich Project Type from a SQL Database
 
#> 

[CmdletBinding()]
param(
    ###########################################################################################
    # Parameters for Rich Project Comparison Parameters
    ###########################################################################################
    [Parameter(Mandatory =$true)]
    [System.Data.DataTable] $dtPWRichProjects, # Input Rich Project Datatable
    [Parameter( Mandatory = $false )]
    [string] $PWProjectType = 'CDOT_INFO',
    [Parameter( Mandatory = $false )]
    [string] $PWProjectNumberProperty = 'PROJECT_Project_Code',
    [Parameter( Mandatory = $false )]
    [string] $PWProjectTimeStampUpdateProperty = 'PROJECT_SAP_Change_Date',
    
    ###########################################################################################
    # 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",

    # Add the WHERE part of the SELECT Statement here - use to switch based on parameter later
    # Full Processing
    [Parameter(
            HelpMessage = "(optional) Where clause to append to the SQL SELECT Statement",
            Mandatory = $false )]
    [string] $SqlWhereCondition = "",

    ###########################################################################################
    # Script and Log File Information
    ###########################################################################################
    [switch] $Full,
    [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 Rich Projects 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 Database results
        # 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

        try {
            $dtCDOTDATA = Select-CDOTSQLDataTable -SQLConnectionString $SQLConnectionString -SqlQuery $SqlQuery -TableName $PWProjectType -InformationAction Stop -InformationVariable info

            if($dtCDOTDATA.Rows.Count -eq 0) {
                throw "No Rows in Data table"
            }

            Write-PWPSLog -Message "[BEGIN] $($dtCDOTDATA.Rows.Count) Rows 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
            break
        }
        
    #endregion Check Database results
    
} # end BEGIN

PROCESS {
    Write-PWPSLog -Message "[PROCESS] Continuing with processing script." -LogPath $LogFilePathName -Level Info -Cmdlet $CmdletName

##################################################################################################################################
#
# EDIT THIS SECTION [20191205 AJC] - CYCLE THROUGH INPUT PROJECTS TABLE AND UPDATE PROPS
# 1. If project number is null or 0, write error folder path not processed, then 'break' loop
# 2. Get rows from SQL Datatable for project number
# 3. Get folder object for project
# 4. Compare rich project props to SQL dt row values - log differences discreetly
# 5. Update Rich Project Props and folder if values are different
#
##################################################################################################################################
    # Process each row in Rich Project Datatable to find which ones need SAP Update
    foreach ($row in $dtPWRichProjects.Rows){
        
        # Get Project Number and retrieve data row(s) with Project Number
        $projectNumber = $row.$PWProjectNumberProperty

        # Filter table to rows containing asset number
        $drFilteredRows = $dtCDOTDATA.Select("$PWProjectNumberProperty = '$projectNumber'")

        if ($drFilteredRows.Count -gt 0) {
            # Project Code exists in SAP
            #region update rich project if needed
                # get this from current row in rich project list
                $richProjectFolder = Get-PWFolders -FolderPath $row.ExistingProjectPath -JustOne | Get-PWFolderPathAndProperties

                $PWRichProjectKeys = $richProjectFolder.ProjectProperties.Keys -like "PROJECT_*"


                # Get update date in CDOTDATA and rich project
                try {$databasePropertyValue = $($drFilteredRows[0].$PWProjectTimeStampUpdateProperty).ToString(“yyyy-MM-dd") }
                catch { $databasePropertyValue =  ""}

                try { $PWPropertyValue = $([datetime]$richProjectFolder.ProjectProperties.$PWProjectTimeStampUpdateProperty).ToString(“yyyy-MM-dd") }
                catch { $PWPropertyValue =  "" }


                # If the values are different OR if Full Run, update the project props - make hash table first
                if($databasePropertyValue -ne $PWPropertyValue -or $Full) {
                    Write-PWPSLog -Message "[PROCESS] UPDATING $($richProjectFolder.FullPath)" -Path $LogFilePathName -Level Info -Cmdlet $CmdletName

                    # Reset HashTable for PW Rich Project Properties
                    $PWRichProject_Properties = @{}

                    foreach ($key in $PWRichProjectKeys){
                        if ($drFilteredRows[0].$key) {
                            # Reset compare values
                            $dbCompareValue = ''
                            $PWCompareValue = ''

            
                            try { $dbCompareValue = $($drFilteredRows[0].$key).ToString(“yyyy-MM-dd hh:mm:ss") }
                            catch{
                
                                try {
                                    $dbCompareValue = $drFilteredRows[0].$key
                                    $dbCompareValue = $dbCompareValue.Trim()
                                }
                                catch { $dbCompareValue = '' }
                            }

                            try {$PWCompareValue = ([datetime]$richProjectFolder.ProjectProperties.$key).ToString(“yyyy-MM-dd hh:mm:ss")}
                            catch{$PWCompareValue = $richProjectFolder.ProjectProperties.$key}
            
                            if ($dbCompareValue -ne $PWCompareValue) {
                                $PWRichProject_Properties.$key = $dbCompareValue
                                Write-PWPSLog -Message "[PROCESS] $projectNumber`: $key changed from $PWCompareValue to $dbCompareValue." -Path $LogFilePathName -Level Info -Cmdlet $CmdletName
                            }
                        }
                    }

                    # Count Updated properties. Skip update if no changes are found
                    if ($PWRichProject_Properties.Count -gt 0) {
                        # Try update
                        # adjust the update date and user
                        $PWRichProject_Properties.PROJECT_PWZ_Project_Update_User = $PWUserName
                        $PWRichProject_Properties.PROJECT_PWZ_Project_Update_Date = (Get-Date).ToString(“yyyy-MM-dd hh:mm:ss")

                        if( -not (Update-PWRichProjectProperties -InputFolder $richProjectFolder -ProjectProperties $PWRichProject_Properties -Verbose)){
                            Write-PWPSLog -Message "[PROCESS] $projectNumber`: Failed to update properties for $($richProjectFolder.FullPath)." -Path $LogFilePathName -Level Warn -Cmdlet $CmdletName
                        }
                        else {
                            Write-PWPSLog -Message "[PROCESS] $projectNumber`: $($richProjectFolder.FullPath) was changed." -Path $LogFilePathName -Level Info -Cmdlet $CmdletName
                        }
                    }
                    else {
                        # Log NO UPDATE NEEDED
                        Write-PWPSLog -Message "[PROCESS] $projectNumber`: No changes needed for $($richProjectFolder.FullPath)." -Path $LogFilePathName -Level Info -Cmdlet $CmdletName
                    }

    
                }
                else {
                    Write-PWPSLog -Message "[PROCESS] $projectNumber`: NO CHANGES ($($richProjectFolder.FullPath))." -Path $LogFilePathName -Level Info -Cmdlet $CmdletName
                }
            #endregion update rich project if needed
        }
        else {
            # Project Code does not exist in SAP
            Write-PWPSLog -Message "[PROCESS] '$projectNumber'`: Does not exist in SAP ($($row.ExistingProjectPath))." -Path $LogFilePathName -Level Info -Cmdlet $CmdletName
        }

            
    } # end foreach $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