syncCommentAndTasks.ps1
|
<#
.SYNOPSIS Synchronizes the comment and the task of a timebooking .DESCRIPTION When the description is a part of an existing taskcode of the project and only one matching task is found, then the task of the timebooking is set to the found task and the description is updated with code and description of the task. When the description of the task has a length of < 1(so any single character), then the description is upated with the code and description of the task. .PARAMETER tcCredential Credential for Timecockpit. If not given they will be requested and stored, if used, in CredentialManager. .PARAMETER tcDBCredential Credential for Timecockpit DB. If not given they will be requested and stored, if used, in CredentialManager. .PARAMETER useCredentialsManager If credentialmanager should be used. Default is $True. .PARAMETER date The timebooking needs to be later than this date to be considered. Defaults to 7 days ago. .EXAMPLE Check and update from 1st of july 2019 .\syncCommentAndTasks 2019-06-01 #> [CmdletBinding(SupportsShouldProcess=$true)] param( [PSCredential] $tcCredential, [PSCredential] $tcDBCredential, [Boolean] $useCredentialsManager = $True, [DateTime] $from = (Get-Date).AddDays(-7) ) # check prerequisites $tcModule = Get-Module -ListAvailable -Name "TimeCockpit"; if (!$tcModule) { throw "Module 'TimeCockpit' needed. Please install executing 'Install-Module -Name TimeCockpit' as Administrator."; } Write-Output "Using TimeCockpit Module v$($tcModule.Version)." Write-Output "Update will be performed on projects having timerecords newer than ${from}."; Connect-TC -Credential $tcCredential -UseCredentialsManager $useCredentialsManager; Connect-TCDB -Credential $tcDBCredential -UseCredentialsManager $useCredentialsManager; $descriptionToSetQuery = @" select Customer.APP_Code as 'Customer', Project.APP_Code as 'Project', Task.APP_Code as 'TaskCode', Task.APP_Description as 'TaskDescription', Timesheet.APP_TimesheetUuid as 'TimesheetUuid', Timesheet.APP_Description as 'Comment', Timesheet.APP_BeginTime as 'BeginTime', UserDetail.APP_Username as 'Username' from edhrqnfxua.APP_Timesheet as Timesheet join edhrqnfxua.APP_Task as Task on Task.APP_TaskUuid = Timesheet.Relation_APP_Task join edhrqnfxua.APP_Project as Project on Project.APP_ProjectUuid = Timesheet.Relation_APP_Project join edhrqnfxua.APP_Customer as Customer on Customer.APP_CustomerUuid = Project.Relation_APP_Customer join edhrqnfxua.APP_UserDetail as UserDetail on UserDetail.APP_UserDetailUuid = Timesheet.Relation_APP_UserDetail where LEN(Timesheet.APP_Description) <= 1 and Timesheet.APP_EndTime > '$($from.ToString('yyyy-MM-dd HH:mm'))' "@ $items = (Invoke-TCDBSqlCmd -Query $descriptionToSetQuery) Write-Output "Fetched $($items.Count) candidates for comment update."; foreach($item in $items) { $project = Get-TCProject -CustomerCode $item.Customer -Code $item.Project -Closed $Null; $task = Get-TCTask -ProjectUuid $project.APP_ProjectUuid -Code $item.TaskCode; $newComment = $task.APP_Code + " | " + $task.APP_Description; Write-Output "Updating Comment of Timesheet entry for $($item.Customer) - $($item.Project) - $($item.Username) - $($item.BeginTime) to '${newComment}'"; if($pscmdlet.ShouldProcess("$($item.Customer) - $($item.Project) - $($item.Username) - $($item.BeginTime)", "Edit-TCTimesheet")) { Edit-TCTimesheet -Uuid $item.TimesheetUuid -Description $newComment; } } $taskToSetQuery = @" select Customer.APP_Code as 'Customer', Project.APP_Code as 'Project', Task.APP_Code as 'TaskCode', Task.APP_Description as 'TaskDescription', Timesheet.APP_TimesheetUuid as 'TimesheetUuid', Timesheet.APP_Description as 'Comment', Timesheet.APP_BeginTime as 'BeginTime', UserDetail.APP_Username as 'Username' from edhrqnfxua.APP_Timesheet as Timesheet left join edhrqnfxua.APP_Task as Task on Task.APP_TaskUuid = Timesheet.Relation_APP_Task join edhrqnfxua.APP_Project as Project on Project.APP_ProjectUuid = Timesheet.Relation_APP_Project join edhrqnfxua.APP_Customer as Customer on Customer.APP_CustomerUuid = Project.Relation_APP_Customer join edhrqnfxua.APP_UserDetail as UserDetail on UserDetail.APP_UserDetailUuid = Timesheet.Relation_APP_UserDetail where Project.APP_Code is not null and Task.APP_Code is null and Timesheet.APP_EndTime > '$($from.ToString('yyyy-MM-dd HH:mm'))' "@ $items = (Invoke-TCDBSqlCmd -Query $taskToSetQuery) Write-Output "Fetched $($items.Count) candidates for task update."; foreach($item in $items) { $project = Get-TCProject -CustomerCode $item.Customer -Code $item.Project -Closed $Null; $tasks = Get-TCTask -ProjectUuid $project.APP_ProjectUuid; foreach($task in $tasks) { $fittingTask = $Null; if($task.APP_Code -match "(?<ParentId>^\d+)-(?<TaskId>\d+)" -or $task.APP_Code -match "(?<ParentId>^\d+)") { if($Matches.ParentId -eq $item.Comment -or $Matches.TaskId -eq $item.Comment) { $newComment = $task.APP_Code + " | " + $task.APP_Description; Write-Output "Updating task for Timesheet entry $($item.Customer) - $($item.Project) - $($item.Username) - $($item.BeginTime) to $($task.APP_Code) and comment to '$($newComment)'"; if($pscmdlet.ShouldProcess("$($item.Customer) - $($item.Project) - $($item.Username) - $($item.BeginTime)", "Edit-TCTimesheet")) { Edit-TCTimesheet -Uuid $item.TimesheetUuid -Task $task -Description $newComment; } break; } } } } |