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