listBookings.ps1
|
<#
.SYNOPSIS Returns timebookings enriched by information on DevOps Backlogitems .DESCRIPTION .PARAMETER devOps The DevOps the bookings are in .PARAMETER tcCustomer The code of the customer to filter bookings for. If not present, all customers will be returned .PARAMETER tcProject The code of the project to filter bookings for. Only valid if tcCustomer is given. If not present, all projects matching other criteria will be returned. .PARAMETER from Date and time from which on to return bookings. If not present, all past bookings will be returned. .PARAMETER to Date and time until which on to return bookings. If not present, bookings with no to-limit will be returned. .PARAMETER targetGroup If report contains internal data (cost column and non-billable bookings). Can be set to internal and external. Defaults to external If set to "internal" then a "confidential" suffix is added to the outputFile filename. .PARAMETER outputFile Optional file to write results as CSV to If targetGroup is set to "internal" then a "confidential" suffix is added to the provided filename. .PARAMETER tcDBCredential Credential for Timecockpit DB. If not given they will be requested and stored, if used, in CredentialManager. .PARAMETER devOpsCredential Credential for DevOps. If not given they will be requested and stored, if used, in CredentialManager. .PARAMETER useCredentialsManager If credentialmanager should be used. Default is $True. .EXAMPLE .\listBookings.ps1 -devOps Rolsped -outputFile bookings.csv -tcCustomer Rolsped -tcProject TruckerApp -from 2020-01-01 -to 2020-01-31 #> [CmdletBinding(SupportsShouldProcess=$true)] param( [Parameter(Mandatory=$true)] [ValidateSet('GuidNew','Rolsped')] [string] $devOps, [string] $tcCustomer, [string] $tcProject, [DateTime] $from, [DateTime] $to, [Parameter(Mandatory=$true)] [System.IO.FileInfo] $outputFile, [ValidateSet('internal','external')] [string] $targetGroup = "external", [PSCredential] $tcDBCredential, [PSCredential] $devOpsCredential, [Boolean] $useCredentialsManager = $True ) function Get-PAT { Param( [Parameter(Mandatory=$true)] [ValidateSet('GuidNew','Rolsped')] [string] $devOps, [PSCredential]$Credential, [Boolean]$UseCredentialsManager = $False ) #end param # check module prerequisites if($UseCredentialsManager) { $module = Get-Module -ListAvailable -Name "CredentialsManager"; if (!$module) { throw "Module 'CredentialsManager' needed. Please install executing 'Install-Module -Name CredentialsManager' as Administrator."; } } if($UseCredentialsManager -and $Credential -eq $Null) { $Credential = Read-Credential -ListAvailable | Where { $_.Environment -eq "DevOps.${devOps}" } } if(!$Credential) { $Credential = Get-Credential -Message "Please enter Credentials for ${devOps} DevOps."; } if($UseCredentialsManager) { Write-Credential "DevOps.${devOps}" -Credential $Credential; } return $Credential; } function Get-ItemsByIds([int[]] $ids) { if($ids.Count -eq 0) { return @() } $allItems= @() for($i = 0; $i -lt $ids.Count;$i += 200) { $idsList = $ids[$i..($i+199)] -join "," # TODO: can we collect the errors and print them as cleanuphints? $devOpsGetCall = "${devOpsUrl}/_apis/wit/workitems?ids=${idsList}&fields=System.Id,System.Title,System.WorkItemType,System.AreaPath,Microsoft.VSTS.Scheduling.StoryPoints&errorPolicy=omit&api-version=5.1"; $items = Invoke-RestMethod -Uri $devOpsGetCall -Method Get -ContentType "application/json" -Headers @{Authorization=("Basic {0}" -f $base64AuthInfo)} $allItems += $items } return $allItems } function Get-Items([string] $wiql) { $devOpsCall = "${devOpsUrl}/_apis/wit/wiql?api-version=5.1" $content = ConvertTo-JSON( @{ query=$wiql; }); $returned = Invoke-RestMethod -Uri $devOpsCall -Method Post -Body $content -ContentType "application/json" -Headers @{Authorization=("Basic {0}" -f $base64AuthInfo)} $ids = $returned.workItems | Select-Object -ExpandProperty Id if($ids -eq $Null) { return @(); } $items = Get-ItemsByIds $ids return $items } function Get-ItemsRelation([string] $wiql) { $devOpsCall = "${devOpsUrl}/_apis/wit/wiql?api-version=5.1" $content = ConvertTo-JSON( @{ query=$wiql; }); $returned = Invoke-RestMethod -Uri $devOpsCall -Method Post -Body $content -ContentType "application/json" -Headers @{Authorization=("Basic {0}" -f $base64AuthInfo)} $ids = $returned.workItemRelations | Where-Object { $_.rel } | Select-Object -ExpandProperty Target | Select-Object -ExpandProperty Id if($ids -eq $Null) { return @(); } $items = Get-ItemsByIds $ids return $items } Function IIf($If, $IfTrue, $IfFalse) { If ($If) {If ($IfTrue -is "ScriptBlock") {&$IfTrue} Else {$IfTrue}} Else {If ($IfFalse -is "ScriptBlock") {&$IfFalse} Else {$IfFalse}} } # check prerequisites $tcModule = Get-Module -ListAvailable -Name "TimeCockpit"; if (!$tcModule) { throw "Module 'TimeCockpit' needed. Please install executing 'Install-Module -Name TimeCockpit' as Administrator."; } Write-Information "Using TimeCockpit Module v$($tcModule.Version)." # process parameters if($devOps -eq "GuidNew") { $devOpsUrl = "https://guidnew.visualstudio.com" } if($devOps -eq "Rolsped") { $devOpsUrl = "https://dev.azure.com/rolsped" } if(-not $from) { $from = [DateTime]::MinValue } if(-not $to) { $to = [DateTime]::MaxValue } else { # set time of to to end of day $to = $to.Date.AddDays(1).AddSeconds(-1) } # Authentication Token for DevOps $devOpsCredential = Get-PAT $devOps $devOpsCredential $useCredentialsManager $base64AuthInfo = [Convert]::ToBase64String([Text.Encoding]::ASCII.GetBytes(("{0}:{1}" -f $devOpsCredential.UserName, $devOpsCredential.GetNetworkCredential().password))) ################################################### #### Fetch Bookings ############################### ################################################### $queryFilters = @() if($tcCustomer) { $queryFilters += "Customer.APP_Code IN ('${tcCustomer}')" } if($tcProject) { $queryFilters += "Project.APP_Code IN ('${tcProject}')" } $query = @" select UserDetail.APP_Lastname + ', ' + UserDetail.APP_Firstname as 'User', Customer.APP_Code as 'Customer', Project.APP_Code as 'Project', Task.APP_Code as 'Task', CASE WHEN CHARINDEX('-', Task.APP_Code) > 0 THEN RIGHT(Task.APP_Code, CHARINDEX('-', Task.APP_Code) - 1) ELSE IIF(isnumeric(Task.APP_Code) = 1, Task.APP_Code, NULL) END AS 'BacklogItemId', CASE WHEN CHARINDEX('-', Task.APP_Code) > 0 THEN LEFT(Task.APP_Code, CHARINDEX('-', Task.APP_Code) - 1) ELSE NULL END AS 'ParentBacklogItemId', Task.APP_Description as 'TaskDescription', Timesheet.APP_BeginTime as 'BeginTime', Timesheet.APP_EndTime as 'EndTime', Timesheet.APP_Description as 'Description', cast(DATEDIFF(minute, Timesheet.APP_BeginTime, Timesheet.APP_EndTime) as float) / 60 as 'Duration', case when Timesheet.APP_HourlyRate is null and Task.APP_HourlyRate is null and Project.APP_HourlyRate is null then Customer.APP_HourlyRate when Timesheet.APP_HourlyRate is null and Task.APP_HourlyRate is null then Project.APP_HourlyRate when Timesheet.APP_HourlyRate is null then Task.APP_HourlyRate else Timesheet.APP_HourlyRate end as 'HourlyRate', case when Project.APP_Billable = 0 or Task.APP_NoBilling = 1 or Timesheet.APP_NoBilling = 1 then 0 else 1 end as 'Billable', COALESCE(Timesheet.APP_HourlyRateBilled, 0) as 'Billed', Timesheet.USR_CostRate as 'Cost' from edhrqnfxua.APP_Timesheet as Timesheet 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 left join edhrqnfxua.APP_Task as Task on Task.APP_TaskUuid = Timesheet.Relation_APP_Task where $($queryFilters -join ' and ') and Timesheet.APP_BeginTime > '$($from.ToString('yyyy-MM-dd'))' and Timesheet.APP_EndTime <= '$($to.ToString('yyyy-MM-dd HH:mm'))' "@ Write-Output "Fetching Timerecords from DB..."; $items = (Invoke-TCDBSqlCmd -Query $query) Write-Output "Fetched $($items.Count) records."; #Write-Output "Fetching Features from DevOps..."; #$features = Get-Items "Select [System.Id], [System.Title], [System.State] From WorkItems Where [System.WorkItemType] = 'Feature' and [System.TeamProject] = '${devOpsProject}' " #Write-Output "Fetched $($features.Count) features."; #$unparented = Get-Items "select [System].[Id], [System.Title], [System.AreaPath] from WorkItemLinks where [Source].[System.TeamProject] = '${devOpsProject}' AND [Source].[System.WorkItemType] IN ('User Story', 'Bug') AND [System.Links.LinkType] = 'System.LinkTypes.Hierarchy-Reverse' and [Target].[System.WorkItemType] IN ('Feature') mode (DoesNotContain)" #Write-Output "Fetched $($unparented.Count) unparented User Stories and Bugs."; # fetch items and parentitems from DevOps for value lookups $devOpsItemIds = $items | Where-Object { $_.BacklogItemId -ne [System.DBNull]::Value } | Select-Object -ExpandProperty "BacklogItemId" -Unique $devOpsParentItemIds = $items | Where-Object { $_.ParentBacklogItemId -ne [System.DBNull]::Value } | Select-Object -ExpandProperty "ParentBacklogItemId" -Unique $devOpsAllItemIds = $devOpsItemIds + $devOpsParentItemIds | Get-Unique $devOpsItems = Get-ItemsByIds $devOpsAllItemIds Write-Output "Fetched $($devOpsItems.Count) Backlogitems."; $output = @() foreach($item in $items) { $area = $storyId = $storyTitle = $featureId = $featureTitle = $Null if($item.BacklogItemId -ne [System.DBNull]::Value) { # resolve additional fields from DevOps Items $devOpsData = $devOpsItems.value | Where-Object { $_.id -eq $item.BacklogItemId } | Select-Object -ExpandProperty fields $backlogItemType = $devOpsData | Select-Object -ExpandProperty "System.WorkItemType" if($backlogItemType -eq "Task") { $parentDevOpsData = $devOpsItems.value | Where-Object { $_.id -eq $item.ParentBacklogItemId } | Select-Object -ExpandProperty fields $area = $parentDevOpsData | Select-Object -ExpandProperty "System.AreaPath" $storyTitle = $parentDevOpsData | Select-Object -ExpandProperty "System.Title" $storyId = $item.ParentBacklogItemId } if($backlogItemType -eq "User Story" -or $backlogItemType -eq "Bug" ) { $devOpsData = $devOpsItems.value | Where-Object { $_.id -eq $item.BacklogItemId } | Select-Object -ExpandProperty fields $area = $devOpsData | Select-Object -ExpandProperty "System.AreaPath" $storyTitle = $devOpsData | Select-Object -ExpandProperty "System.Title" $storyId = $item.BacklogItemId } } # fetch the feature $grantparent = Get-ItemsRelation "select [System].[Id], [System.Title] from WorkItemLinks where [Source].[System.Id] = '${storyId}' AND [System.Links.LinkType] = 'System.LinkTypes.Hierarchy-Reverse' and [Target].[System.WorkItemType] IN ('Feature')" # resolve additional Grandparent fields from fetched grandparent if($grantparent) { $featureId = $grantparent.value.id $featureTitle = $grantparent.value.fields."System.Title" } if($targetGroup -eq "internal" -or ($targetGroup -eq "external" -and $item.Billable -eq 1)) { Write-Output "Adding booking $($item.Task) $($item.BeginTime)" $row = [pscustomobject][ordered]@{ User = $item.User; Customer = $item.Customer; Project = $item.Project; Task = $item.Task; BacklogItemId = $item.BacklogItemId; Area = $area; StoryId = $storyId; StoryTitle = $storyTitle; FeatureId = $featureId; FeatureTitle = $featureTitle; TaskDescription = $item.TaskDescription; BeginTime = $item.BeginTime; EndTime = $item.EndTime; Description = $item.Description; Duration = $item.Duration; HourlyRate = $item.HourlyRate; Amount = $item.HourlyRate * $item.Duration; BilledAmount = $item.Billed * $item.Duration; } if($targetGroup -eq "internal") { Add-Member -InputObject $row -NotePropertyMembers @{ Billable = $item.Billable; } Add-Member -InputObject $row -NotePropertyMembers @{ Cost = $row.Duration * [math]::Round($item.Cost, 2); } } $output += $row } } # amment confidential note in case of internal if($targetGroup -eq "internal") { $outputFile = $outputFile.DirectoryName + [System.IO.Path]::DirectorySeparatorChar + $outputFile.BaseName + "_confidential" + $outputFile.Extension } Write-Output "Writing Results into ${outputFile}..."; # write output file $output | Export-Csv -Path $outputFile -NoTypeInformation |