createSupportReport.ps1
|
<#
.SYNOPSIS Creates invoices based on timecockpit bookings on support projects .DESCRIPTION Workpackages are created as invoice position, which are closed, billable and not billed. The workpackage have no bookings later than the given date (to avoid having closed booking from the next month already in the invoice). .PARAMETER tcCustomerNames Customer to create output for. If no customer is passed, then all customers are returned. .PARAMETER from Bookings before this date will not be considered. If not given (which is the default behaviour) all bookings into the past are considered as long as they are not marked as billed. .PARAMETER to Bookings after this date will not be considered. Defaults to last day of previous month .PARAMETER includeBilled If also Bookings which are already billed should be included. Defaults to $False .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. .EXAMPLE Creates invoices up to january .\createSupportInvoices 2019-01-31 #> [CmdletBinding(SupportsShouldProcess=$true)] param( [string[]] $tcCustomerNames, [DateTime] $from, [DateTime] $to, [Boolean] $includeBilled = $False, [PSCredential] $tcDBCredential, [Boolean] $useCredentialsManager = $True ) # 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)." $fdModule = Get-Module -ListAvailable -Name "Freshdesk"; if (!$fdModule) { throw "Module 'Freshdesk' needed. Please install executing 'Install-Module -Name Freshdesk' as Administrator."; } if(!$from) { $from = [datetime]::MinValue } if(!$to) { $now = Get-Date $to = Get-Date -Year $now.Year -Month $now.Month -Day 1 } else { $to = Get-Date -Year $to.Year -Month $to.Month -Day $to.Day -Hour 23 -Minute 59 } if($tcCustomerNames) { $tcCustomerNamesResolved = $tcCustomerNames | foreach { "'" + $_ + "'" } $tcCustomerNamesQuery = "and Customer.APP_Code in ($($tcCustomerNamesResolved -join ','))" } if($includeBilled) { $tcIncludeBilledQuery = "" } else { $tcIncludeBilledQuery = "and Timesheet.APP_HourlyRateBilled is null" } if($from -eq [datetime]::MinValue) { Write-Information "Will create Report for not billed timerecords older than $($to.ToString('yyyy-MM-dd'))..."; } else { Write-Information "Will create Report for not billed timerecords ($($from.ToString('yyyy-MM-dd')) - $($to.ToString('yyyy-MM-dd')))..."; } $timesheetQuery = @" select Customer.APP_Code as 'CustomerCode', Project.APP_Code as 'ProjectCode', Task.APP_TaskUuid as 'TaskUuid', Task.APP_Code as 'TaskCode', Task.APP_Description as 'TaskDescription', Timesheet.APP_Description as 'Description', Timesheet.APP_BeginTime as 'BeginTime', Timesheet.APP_EndTime as 'EndTime', UserDetail.APP_Username as 'User', 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 join edhrqnfxua.APP_Task as Task on Task.APP_TaskUuid = Timesheet.Relation_APP_Task join edhrqnfxua.APP_UserDetail as UserDetail on UserDetail.APP_UserDetailUuid = Timesheet.Relation_APP_UserDetail where Project.APP_Code like 'Support%' and Customer.APP_Code not in ('GuidNew') ${tcCustomerNamesQuery} and Project.APP_Billable = 1 and Task.APP_NoBilling = 0 and Timesheet.APP_NoBilling = 0 ${tcIncludeBilledQuery} and Timesheet.APP_BeginTime >= '$($from.ToString('yyyy-MM-dd'))' and Timesheet.APP_EndTime <= '$($to.ToString('yyyy-MM-dd HH:mm'))' "@ $query = @" with ResolvedTimesheet as ( ${timesheetQuery} ) select CustomerCode, ProjectCode, TaskCode, TaskDescription, TaskClosed, 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(TimesheetCostrate) TimesheetCostrate, max(HourlyRate) HourlyRate from ResolvedTimesheet TS group by CustomerCode, ProjectCode, TaskUuid, TaskCode, TaskDescription, TaskClosed order by CustomerCode, TaskClosed desc, max(EndTime) "@ Connect-TCDB -Credential $tcDBCredential -UseCredentialsManager $useCredentialsManager; Write-Information "Fetching Tasks from DB..."; $tasks = (Invoke-TCDBSqlCmd -Query $query) Write-Information "Fetched $($tasks.Count) records."; Write-Information "Fetching Bookings from DB..."; $bookings = (Invoke-TCDBSqlCmd -Query $timesheetQuery) Write-Information "Fetched $($bookings.Count) records."; $resultRaw = @(); foreach($item in $tasks) { $resultRow = @{} $resultRow.Add("customerCode", $item.CustomerCode) $resultRow.Add("taskCode", $item.TaskCode) $resultRow.Add("taskDescription", $item.TaskDescription) $resultRow.Add("beginDate", $item.BeginTime.ToString('yyyy-MM-dd')) $resultRow.Add("endDate", $item.EndTime.ToString('yyyy-MM-dd')) $resultRow.Add("isClosed", $item.TaskClosed) $resultRow.Add("durationBilled", $item.DurationBilled) $resultRow.Add("durationUnbilled", $item.DurationUnbilled) $resultRow.Add("hourlyRate", $item.HourlyRate) $resultRow.Add("amountBilled", $item.AmountBilled) if($item.TaskCode -match '^\d+$' -and [int]$item.TaskCode -lt 5000) { try { $ticket = Get-FDTicket $item.TaskCode } catch { throw "Failed to get Ticket having Id '$($item.TaskCode)'" } $resultRow.Add("type", "Ticket") $resultRow.Add("subType", $ticket.type) switch($ticket.priority) { 1 { $category = "Inquiry" } 2 { $category = "Minor" } 3 { $category = "Major" } 4 { $category = "Blocker" } } $resultRow.Add("categoryNumber", $ticket.priority) $resultRow.Add("categoryName", $category) $resultRow.Add("firstResponseSLAViolation", $ticket.fr_escalated) $resultRow.Add("resolutionSLAViolation", $ticket.is_escalated) } else { $resultRow.Add("type", "Other") } # append bookings $taskBookings = $bookings | Where-Object { $_.TaskCode -eq $item.TaskCode } $resultTasks = @() foreach($taskBooking in $taskBookings) { $taskRow = [PSCustomObject]@{ beginTime = $taskBooking.BeginTime.ToString('yyyy-MM-dd HH:mm') endTime = $taskBooking.EndTime.ToString('yyyy-MM-dd HH:mm') description = $taskBooking.Description user = $taskBooking.User } $resultTasks += $taskRow } $resultRow.Add("bookings", $resultTasks) $resultRaw += $resultRow; } $resultRaw | ConvertTo-Json -Depth 16 #$resultRaw | ConvertTo-Json | ConvertFrom-Json #$new = $raw.values | Where-Object { $_.type -eq "Ticket" } | Group-Object -Property subType #$new[2].Group | Meassure-Object -Property durationBilled -Sum |