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 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( [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-Host "Using TimeCockpit Module v$($tcModule.Version)." $sqlCmd2Module = Get-Module -ListAvailable -Name "Invoke-SqlCmd2"; if (!$sqlCmd2Module) { throw "Module 'TimeCockpit' needed. Please install executing 'Install-Module -Name Invoke-SqlCmd2' as Administrator."; } Write-Host "Using TimeCockpit Module v$($sqlCmd2Module.Version)." # settings $tcDBServer="gjmyvebfqv.database.windows.net"; $tcDBDatabase="tcedhrqnfxua"; $tcDBUser="tcreporting" $tcDBPwd=ConvertTo-SecureString "hXjl8m79dGKzD6v5CcUs" -AsPlainText -Force; #$tcUser = $env:APPSETTING_TimeCockpit_User; #$tcPassword = $env:APPSETTING_TimeCockpit_Password; $tcUser = "guido@guidnew.com" $tcPassword = "3UyXQSmLp*"; $tcDBCredentials = New-Object PSCredential($tcDBUser, $tcDBPwd); Write-Host "Update will be performed on projects having timerecords newer than ${from}."; Write-Host "Logging in to TC with User ${tcUser}..." Connect-TC $tcUser $tcPassword $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-SqlCmd2 -ServerInstance $tcDBServer -Database $tcDBDatabase -Credential $tcDBCredentials -Query $descriptionToSetQuery) Write-Host "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")) { Write-Output "XXX"; Edit-TCTimesheet -Uuid $item.TimesheetUuid -Comment $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-SqlCmd2 -ServerInstance $tcDBServer -Database $tcDBDatabase -Credential $tcDBCredentials -Query $taskToSetQuery) Write-Host "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 -Comment $newComment; } break; } } } } |