updateEffectiveHourlyRate.ps1
|
<#
.SYNOPSIS Updates the hourly rate of billable fixed price projects to the effective hourly rate. Used to keep the budget on the fixed price by reducing the hourly rate. .DESCRIPTION On open projects, if hourly rate is lower than effective hourly rate, the hourly rate is not modified. On closed projects, the hourly rate is set to the effective hourly rate. Only projects which have recently been booked are checked. .PARAMETER tcCredential Credential for Timecockpit. If not given they will be requested and stored, if used, in CredentialManager. .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 date The Timecockpit project needs to have a booking later than this date to be considered. Defaults to 7 days ago. .EXAMPLE Check from 1st of july 2019 .\updateEffectiveHourlyRate 2019-06-01 #> [CmdletBinding(SupportsShouldProcess=$true)] param( [PSCredential] $tcCredential, [PSCredential] $tcDBCredential, [Boolean] $useCredentialsManager = $True, [DateTime] $from = (Get-Date).AddDays(-7) ) # check prerequisites $tcModule = Get-Module -ListAvailable -Name "TimeCockpit"; if (!$tcModule) { throw "Module 'TimeCockpit' needed. Please install executing 'Install-Module -Name TimeCockpit' as Administrator."; } Write-Output "Using TimeCockpit Module v$($tcModule.Version)." $query = @" select Customer.APP_Code as 'Customer', Project.APP_Code as 'Project', max(Project.APP_Budget) as 'Budget', max(Project.APP_BudgetInHours) as 'BudgetInHours', sum(cast(DATEDIFF(minute, Timesheet.APP_BeginTime, Timesheet.APP_EndTime) as float)) / 60 as 'Effort', max(case when Project.APP_Billable = 1 and Project.APP_HourlyRate is null then Customer.APP_HourlyRate when Project.APP_Billable = 1 then Project.APP_HourlyRate else 0 end) as 'HourlyRate', max(Project.APP_Budget) / (sum(cast(DATEDIFF(minute, Timesheet.APP_BeginTime, Timesheet.APP_EndTime) as float)) / 60) as 'EffectiveHourlyRate', Project.APP_Closed as 'Closed' 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 where Project.APP_Billable = 1 and Project.APP_FixedPrice = 1 group by Customer.APP_Code, Project.APP_Code, Project.APP_Closed having max(Timesheet.APP_EndTime) > '$($from.ToString('yyyy-MM-dd HH:mm'))' "@ # Fetching Project data Write-Output "Update will be performed on projects having timerecords newer than ${from}."; Connect-TC -Credential $tcCredential -UseCredentialsManager $useCredentialsManager; Connect-TCDB -Credential $tcDBCredential -UseCredentialsManager $useCredentialsManager; $items = (Invoke-TCDBSqlCmd -Query $query) Write-Output "Fetched $($items.Count) records."; foreach($item in $items) { Write-Output "Updating hourly rate for Project $($item.Customer) - $($item.Project) from $($item.HourlyRate) to effective hourly rate of $([math]::Round($item.EffectiveHourlyRate, 2))..."; $effectiveHouryRate = [Math]::Round($item.EffectiveHourlyRate, 2); if($item.Closed -eq 1 -and $item.HourlyRate -ne $effectiveHouryRate -or $item.Closed -eq 0 -and $item.HourlyRate -gt $effectiveHouryRate) { $project = Get-TCProject -CustomerCode $item.Customer -Code $item.Project -Closed $Null; if($pscmdlet.ShouldProcess("$($item.Customer) - $($item.Project)", "Edit-TCProject")) { Edit-TCProject -Uuid $project.APP_ProjectUuid -HourlyRate $item.EffectiveHourlyRate; } } else { Write-Output "Unmodified hourly rate for Project $($item.Customer) - $($item.Project) of $($item.HourlyRate)."; } } |