getUnassignedBookings.ps1
|
<#
.SYNOPSIS Returns bookings which do not have an assigned project or task. .DESCRIPTION .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 from The date to start search empty booking for. Default is monday last week. .PARAMETER to The date to end search empty booking for. Default is sunday last week. .EXAMPLE Check from 1st to 7th of july 2019 .\getUnassignedBookings 2019-06-01 2019-06-07 #> param( [PSCredential] $tcDBCredential, [Boolean] $useCredentialsManager = $True, [DateTime] $from, [DateTime] $to ) # check parameters if($from -lt $to) { throw "To can't be smaller than from"; } $now = Get-Date; $now = New-Object "System.DateTime" -ArgumentList (Get-Date).Year, (Get-Date).Month, (Get-Date).Day if(!$from) { $dayOfWeek = (6+$now.dayofweek)%7 $from = $now.AddDays(-$dayOfWeek-7) } if(!$to) { $dayOfWeek = (6+$now.dayofweek)%7 $to = $now.AddDays(-$dayOfWeek).AddSeconds(-1) } # 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', Task.APP_Code as 'Task', Timesheet.APP_BeginTime as 'BeginTime', Timesheet.APP_EndTime as 'EndTime', cast(DATEDIFF(minute, Timesheet.APP_BeginTime, Timesheet.APP_EndTime) as float) / 60 as 'Duration', UserDetail.APP_Firstname + ' ' + UserDetail.APP_Lastname as 'User' from edhrqnfxua.APP_Timesheet as Timesheet left join edhrqnfxua.APP_Task as Task on Task.APP_TaskUuid = Timesheet.Relation_APP_Task left join edhrqnfxua.APP_Project as Project on Project.APP_ProjectUuid = Timesheet.Relation_APP_Project left join edhrqnfxua.APP_Customer as Customer on Customer.APP_CustomerUuid = Project.Relation_APP_Customer join edhrqnfxua.APP_UserDetail as UserDetail on UserDetail.APP_UserDetailUuid = Timesheet.Relation_APP_UserDetail where (Project.APP_Code is null or Task.APP_Code is null) and Timesheet.APP_BeginTime > '$($from.ToString('yyyy-MM-dd HH:mm'))' and Timesheet.APP_EndTime < '$($to.ToString('yyyy-MM-dd HH:mm'))' order by UserDetail.APP_Lastname, Timesheet.APP_BeginTime "@ # Fetching Project data Write-Information "Will search for empty timerecords between ${from} and ${to}."; Connect-TCDB -Credential $tcDBCredential -UseCredentialsManager $useCredentialsManager; $items = (Invoke-TCDBSqlCmd -Query $query) foreach($item in $items) { if($item.Project -eq [System.DBNull]::Value) { Write-Output "Booking for $($item.User) - $($item.BeginTime) ($($item.Duration)h) does not have a project set."; } elseif($item.Task -eq [System.DBNull]::Value) { Write-Output "Booking for $($item.User) - $($item.BeginTime) ($($item.Duration)h) - $($item.Customer) - $($item.Project) does not have a task set."; } } |