Billomat

0.1.0

createSupportInvoices.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 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.
 
.PARAMETER to
    If task has bookings later than this date, it will be omited.
    Defaults to last day of previous month
 
.EXAMPLE
    Creates invoices up to january
     
    .\createSupportInvoices 2019-01-31
#>
[CmdletBinding(SupportsShouldProcess=$true)]
param(
    [PSCredential] $tcDBCredential,
    [Boolean] $useCredentialsManager = $True,
    [DateTime] $to
)

# 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)."

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
}

Write-Output "Will create Invoices for closed, billable and not billed timerecords having all booking before $($to.ToString('yyyy-MM-dd'))...";

$query = @"
select
    Customer.APP_Code as 'CustomerCode',
    Project.APP_Code as 'ProjectCode',
    Task.APP_Code as 'TaskCode',
    Task.APP_TaskUuid as 'TaskUuid',
    min(Timesheet.APP_BeginTime) as 'Start',
    max(Timesheet.APP_EndTime) as 'End',
    Task.APP_Description as 'TaskDescription',
    Task.APP_Closed as 'TaskClosed',
    isnull(
        (select sum(cast(DATEDIFF(minute, Timesheet.APP_BeginTime, Timesheet.APP_EndTime) as float)) / 60
         from edhrqnfxua.APP_Timesheet as Timesheet
            join edhrqnfxua.APP_Task as TaskInner on TaskInner.APP_TaskUuid = Timesheet.Relation_APP_Task
         where TaskInner.APP_TaskUuid = Task.APP_TaskUuid
            and TaskInner.APP_NoBilling = 0
            and Timesheet.APP_NoBilling = 0
            and (Timesheet.APP_HourlyRate is null or Timesheet.APP_HourlyRate > 0)
            )
    , 0) as 'DurationBilled',
    isnull(
        (select sum(cast(DATEDIFF(minute, Timesheet.APP_BeginTime, Timesheet.APP_EndTime) as float)) / 60
         from edhrqnfxua.APP_Timesheet as Timesheet
             join edhrqnfxua.APP_Task as TaskInner on TaskInner.APP_TaskUuid = Timesheet.Relation_APP_Task
         where TaskInner.APP_TaskUuid = Task.APP_TaskUuid
            and (TaskInner.APP_NoBilling = 1 or Timesheet.APP_NoBilling = 1 or Timesheet.APP_HourlyRate = 0)
        )
    , 0) as 'DurationUnbilled',
    max(case
        when Task.APP_HourlyRate is null and Project.APP_HourlyRate is null then Customer.APP_HourlyRate
        when Task.APP_HourlyRate is null then Project.APP_HourlyRate
        else Task.APP_HourlyRate
    end) as 'HourlyRate'
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
where Project.APP_Code= 'Support' and Customer.APP_Code not in ('GuidNew')
    and Project.APP_Billable = 1
    and Task.APP_NoBilling = 0
    and Timesheet.APP_NoBilling = 0
    and Timesheet.APP_HourlyRateBilled is null
group by Customer.APP_Code, Project.APP_Code, Task.APP_Code, Task.APP_TaskUuid, Task.APP_Description, Task.APP_Closed
having max(Timesheet.APP_EndTime) < '$($to.ToString('yyyy-MM-dd'))'
order by Customer.APP_Code, Task.APP_Closed desc, max(Timesheet.APP_EndTime)
"@


Connect-TCDB -Credential $tcDBCredential -UseCredentialsManager $useCredentialsManager;

Write-Information "Fetching Timerecords from DB...";
$items = (Invoke-TCDBSqlCmd -Query $query)
Write-Information "Fetched $($items.Count) records.";

$customerCode = $Null;
foreach($item in $items) 
{
    if($customerCode -ne $item.CustomerCode)
    {
        $customerCode = $item.CustomerCode;
        Write-Output "Details zu $($item.CustomerCode)";
    }
    $lineAmount = [math]::Round($item.DurationBilled * $item.HourlyRate, 2);
    
    $duration = $Null;
    if($item.Start.Date -eq $item.End.Date) { $duration = "am $($item.Start.ToString('dd.MM.yyyy'))"; }
    else { $duration = "im Zeitraum $($item.Start.ToString('dd.MM.yyyy'))-$($item.End.ToString('dd.MM.yyyy'))"; }
    
    if($item.TaskClosed) 
    {
        if($item.DurationBilled -gt 0)
        {
            $unbilledInfo = ""
            if($item.DurationUnbilled -gt 0) { $unbilledInfo = "(+$($item.DurationUnbilled)h nicht verrechnet) " }
            Write-Output " geschlossen : $($item.TaskCode) ($($item.TaskDescription)) - $duration $($item.DurationBilled)h ${unbilledInfo}zu Euro $($item.HourlyRate)/h = Euro $lineAmount";
        }
        else
        {
            Write-Output " geschlossen : $($item.TaskCode) ($($item.TaskDescription)) - $duration $($item.DurationUnbilled)h (nicht verrechnet) = Euro 0.00";
        }
    }
    else
    {
        Write-Output " noch offen : $($item.TaskCode) ($($item.TaskDescription)) - $duration $($item.DurationBilled)h zu Euro $($item.HourlyRate)/h = Euro $lineAmount";
    }
}