TimeCockpit

0.8.0

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 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
#>
param(
    [DateTime] $from = (Get-Date).AddDays(-7)
)

function Invoke-Sql {
    param(
        [string] $dataSource,
        [string] $database,
        [string] $query,
        [string] $username,
        [string] $password
    )

    $connectionString = "Provider=sqloledb; " + 
        "Data Source=$dataSource; " + 
        "Initial Catalog=$database; " + 
        "uid=$username; " +
        "pwd=$password; "

    ## Connect to the data source and open it
    $connection = New-Object System.Data.OleDb.OleDbConnection $connectionString
    $command = New-Object System.Data.OleDb.OleDbCommand $query, $connection
    $connection.Open()

    ## Fetch the results, and close the connection
    $adapter = New-Object System.Data.OleDb.OleDbDataAdapter $command
    $dataset = New-Object System.Data.DataSet
    [void] $adapter.Fill($dataSet)
    $connection.Close()

    ## Return all of the rows from their query
    $dataSet.Tables | Select-Object -Expand Rows
}

############# Main Script ###################

$tcDBServer="gjmyvebfqv.database.windows.net";
$tcDBDatabase="tcedhrqnfxua";
$tcDBUser="tcreporting"
$tcDBPwd="hXjl8m79dGKzD6v5CcUs";


$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-Host "Update will be performed on projects having timerecords newer than ${from}.";
$items = (Invoke-Sql -dataSource $tcDBServer -database $tcDBDatabase -Username $tcDBUser -Password $tcDBPwd -Query $query)
Write-Host "Fetched $($items.Count) records.";

foreach($item in $items) 
{
    if($item.Closed -eq 1) 
    {
        Write-Output "Updating Closed Project $($item.Customer) - $($item.Project) to effective hourly rate of $($item.EffectiveHourlyRate).";
    }
    else 
    {
        if($item.HourlyRate -gt $item.EffectiveHourlyRate) 
        {
            Write-Output "Updating Project $($item.Customer) - $($item.Project) to effective hourly rate of $($item.EffectiveHourlyRate).";
        }
    }
}