listFeatureCost.ps1
|
<#
.SYNOPSIS List cost and billed amount by feature .DESCRIPTION Lists all Features within a Project and summarizes the timerecord data under those features (userstories, bugs and tasks). .PARAMETER devOps The DevOps the project is in .PARAMETER devOpsProject Project to fetch data for. .PARAMETER tcProjects Projects in TC to resolve the DevOps project to. If not given the same name as the devOpsProject is used. .PARAMETER from Optional Parameter to consider costs only from the given date on. If not given, all costs in past will be taken. .PARAMETER to Optional Parameter to consider costs only up to the given date. If not given, costs until today will be taken. .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 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 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 Creates statistic and write to CSV for further usage in Excel .\listFeatureCost.ps1 -devOps Rolsped -devOpsProject TruckerApp -tcProjects "TruckerApp" -outputFile "c:\temp\features.csv" .EXAMPLE Creates statistic for DevOps project having different named and multiple TCproject .\listFeatureCost.ps1 -devOps GuidNew -devOpsProject Opticon -tcProjects "Service-App","Extension_102019" -outputFile "c:\temp\features.csv" #> [CmdletBinding(SupportsShouldProcess=$true)] param( [Parameter(Mandatory=$true)] [ValidateSet('GuidNew','Rolsped')] [string] $devOps, [Parameter(Mandatory=$true)] [string] $devOpsProject, [string[]] $tcProjects = @($devOpsProject), [DateTime] $from, [DateTime] $to, [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 "," $devOpsGetCall = "${devOpsUrl}/_apis/wit/workitems?ids=${idsList}&fields=System.Id,System.Title,System.State,System.WorkItemType,System.AreaPath,Microsoft.VSTS.Scheduling.StoryPoints&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 | Select-Object -ExpandProperty Target | Select-Object -ExpandProperty Id if($ids -eq $Null) { return @(); } $items = Get-ItemsByIds $ids return $items } # 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://dev.azure.com/guidnew" } 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))) $tcProjectsResolved = $tcProjects | foreach { "'" + $_ + "'" } $query = @" with ResolvedTimesheet as ( select Customer.APP_Code as 'CustomerCode', Project.APP_Code as 'ProjectCode', Task.APP_TaskUuid as 'TaskUuid', Task.APP_Code as 'TaskCode', CASE WHEN CHARINDEX('-', Task.APP_Code) > 0 THEN LEFT(Task.APP_Code, CHARINDEX('-', Task.APP_Code) - 1) ELSE Task.APP_Code END AS 'BacklogItemId', Task.APP_Description as 'TaskDescription', Timesheet.APP_BeginTime as 'BeginTime', Timesheet.APP_EndTime as 'EndTime', Task.APP_Closed as 'TaskClosed', case when Task.APP_NoBilling = 1 then cast(1 as bit) else Timesheet.APP_NoBilling end as 'TimesheetNoBilling', Timesheet.APP_HourlyRateBilled as 'TimesheetBilled', isnull(cast(DATEDIFF(minute, Timesheet.APP_BeginTime, Timesheet.APP_EndTime) as float), 0) as 'Duration', case when Task.APP_NoBilling = 1 or Timesheet.APP_NoBilling = 1 then 0 else isnull(cast(DATEDIFF(minute, Timesheet.APP_BeginTime, Timesheet.APP_EndTime) as float), 0) end as 'DurationBilled', case when Task.APP_NoBilling = 1 or Timesheet.APP_NoBilling = 1 then isnull(cast(DATEDIFF(minute, Timesheet.APP_BeginTime, Timesheet.APP_EndTime) as float), 0) else 0 end as 'DurationUnBilled', 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', Timesheet.USR_CostRate as 'TimesheetCostrate' 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 left join edhrqnfxua.APP_Task as Task on Task.APP_TaskUuid = Timesheet.Relation_APP_Task where Project.APP_Code IN ($($tcProjectsResolved -join ',')) and Timesheet.APP_BeginTime > '$($from.ToString('yyyy-MM-dd'))' and Timesheet.APP_EndTime <= '$($to.ToString('yyyy-MM-dd HH:mm'))' ) select CustomerCode, ProjectCode, BacklogItemId, min(BeginTime) BeginTime, max(EndTime) EndTime, sum(Duration) / 60 Duration, sum(DurationBilled) / 60 as DurationBilled, sum(DurationUnbilled) / 60 as DurationUnbilled, sum((DurationBilled / 60.0) * HourlyRate) as AmountBilled, sum((Duration / 60.0) * TimesheetCostrate) Cost, max(HourlyRate) HourlyRate from ResolvedTimesheet TS group by CustomerCode, ProjectCode, BacklogItemId order by CustomerCode desc, max(EndTime) "@ Connect-TCDB -Credential $tcDBCredential -UseCredentialsManager $useCredentialsManager; 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."; $output = @() $cleanupHints = @() # summarize unparented $duration = 0 $amountBilled = 0 $cost = 0 foreach ($orphan in $unparented.value) { $item = $items | where { $_.BacklogItemId -eq $orphan.id } $orphanTitle = $orphan.fields."System.Title" $orphanAreaPath = $child.fields."System.AreaPath" if($orphanAreaPath.Length -gt $devOpsProject.Length) { $orphanAreaPath = $orphanAreaPath.SubString($devOpsProject.Length + 1, $orphanAreaPath.Length - $devOpsProject.Length - 1) } Write-Output " $($orphan.id) (${orphanAreaPath} ${orphanTitle}) has a duration of $($item.Duration) hours, Billed Amount of $($item.AmountBilled), Cost of $($item.Cost)."; $duration += $item.Duration; $amountBilled += $item.AmountBilled; $cost += $item.Cost; $row = [pscustomobject][ordered]@{ Feature=$Null; FeatureTitle="_Unparented"; Child=$orphan.id; ChildTitle=$orphanTitle; AreaPath=$orphanAreaPath; Duration=[math]::Round($item.duration, 2); AmountBilled=[math]::Round($item.AmountBilled, 2); } if($targetGroup -eq "internal") { Add-Member -InputObject $row -NotePropertyMembers @{ Cost=[math]::Round($item.Cost, 2);} } $output += $row if($item.Duration -gt 0) { $cleanupHints += "Assign a parent feature for Backlogitem $($orphan.id)." } } Write-Output "Unparented have a duration of ${duration} hours, Billed Amount of ${amountBilled}, Cost of ${cost}."; # summarize features foreach ($feature in $features.value) { $featureTitle = $feature.fields."System.Title" $children = Get-ItemsRelation "select [System].[Id], [System.Title], [System.AreaPath] from WorkItemLinks where [Source].[System.Id] = '$($feature.id)' AND [System.Links.LinkType] = 'System.LinkTypes.Hierarchy-Forward' and [Target].[System.WorkItemType] IN ('Bug', 'User Story')" Write-Output "Fetched $($children.Count) User Stories and Bugs for feature $($feature.id) (${featureTitle})."; $duration = 0 $amountBilled = 0 $cost = 0 foreach ($child in $children.value) { $item = $items | where { $_.BacklogItemId -eq $child.id } if($child.Id -eq $feature.Id) { if($item.Duration -gt 0) { $cleanupHints += "The item $($child.Id) is a Feature, but has bookings. Please correct TC hierarchy that Stories/Bugs are rootlevel." } } $childTitle = $child.fields."System.Title" $childAreaPath = $child.fields."System.AreaPath" if($childAreaPath.Length -gt $devOpsProject.Length) { $childAreaPath = $childAreaPath.SubString($devOpsProject.Length + 1, $childAreaPath.Length - $devOpsProject.Length - 1) } Write-Output " $($child.id) (${childAreaPath} ${childTitle}) has a duration of $($item.Duration) hours, Billed Amount of $($item.AmountBilled), Cost of $($item.Cost)."; $duration += $item.Duration; $amountBilled += $item.AmountBilled; $cost += $item.Cost; $row = [pscustomobject][ordered]@{ Feature=$feature.id; FeatureTitle=$featureTitle; Child=$child.id; ChildTitle=$childTitle; AreaPath=$childAreaPath; Duration=[math]::Round($item.duration, 2); AmountBilled=[math]::Round($item.AmountBilled, 2); } if($targetGroup -eq "internal") { Add-Member -InputObject $row -NotePropertyMembers @{ Cost=[math]::Round($item.Cost, 2);} } $output += $row } Write-Output "$($feature.id) has a duration of ${duration} hours, Billed Amount of ${amountBilled}, Cost of ${cost}."; } # summarize bookings not assigned to any devops backlogitem $nonBacklogitems = $items | where {($_.BacklogItemId -eq [System.DBNull]::Value) -or ($_.BacklogItemId -match '^\D+$')} $duration = 0 $amountBilled = 0 $cost = 0 foreach ($item in $nonBacklogitems) { Write-Output " $($item.BacklogItemId) has a duration of $($item.Duration) hours, Billed Amount of $($item.AmountBilled), Cost of $($item.Cost)."; $duration += $item.Duration; $amountBilled += $item.AmountBilled; $cost += $item.Cost; if($item.BacklogItemId -eq [System.DBNull]::Value) { $featureTitle = "_MissingWorkpackage" $cleanupHints += "There are $([math]::Round($item.duration, 2)) hours booked without Task. Check TC for unassigned task to get rid of '_MissingWorkpackage'." } else { $featureTitle = "_$($item.BacklogItemId)" } $row = [pscustomobject][ordered]@{ Feature=$Null; FeatureTitle=$featureTitle; Child=$Null; ChildTitle=$item.BacklogItemId; Duration=[math]::Round($item.duration, 2); AmountBilled=[math]::Round($item.AmountBilled, 2); } if($targetGroup -eq "internal") { Add-Member -InputObject $row -NotePropertyMembers @{ Cost=[math]::Round($item.Cost, 2);} } $output += $row } Write-Output "Nonfunctional bookings have a duration of ${duration} hours, Billed Amount of ${amountBilled}, Cost of ${cost}."; if($cleanupHints.Count -gt 0) { Write-Output ""; Write-Warning "There are inconsistencies in the Report. Consider the following actions:"; $cleanupHints | fl } # write output file if($outputFile) { # 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}..."; $output | Export-Csv -Path $outputFile -NoTypeInformation } |