closeNotBookedSince.ps1
|
<#
.SYNOPSIS Closes Tasks and Projects which have not been booked to since an amount of time. .DESCRIPTION .PARAMETER tcCredential Credential for Timecockpit. If not given they will be requested and stored in CredentialManager. .PARAMETER tcdbCredential Credential for Timecockpit DB. If not given they will be requested and stored in CredentialManager. .PARAMETER from The Task or Project needs to have not been booked to after the given date. Defaults to 180 days ago. .EXAMPLE Close projects and tasks not booked after 1st of july 2019 .\closeNotBookedSince 2019-06-01 #> [CmdletBinding(SupportsShouldProcess=$true)] param( [PSCredential] $tcCredential, [PSCredential] $tcdbCredential, [Boolean] $useCredentialsManager = $True, [DateTime] $from = (Get-Date).AddDays(-180) ) # 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)." $sqlCmd2Module = Get-Module -ListAvailable -Name "Invoke-SqlCmd2"; if (!$sqlCmd2Module) { throw "Module 'Invoke-SqlCmd2' needed. Please install executing 'Install-Module -Name Invoke-SqlCmd2' as Administrator."; } Write-Output "Using Invoke-SqlCmd2 Module v$($sqlCmd2Module.Version)." $queryTasks = @" select Customer.APP_Code as 'Customer', Project.APP_Code as 'Project', Task.APP_Code as 'Task', Task.APP_TaskUuid as 'TaskUuid', MAX(Timesheet.APP_EndTime) as 'LastTimesheetEntry' from edhrqnfxua.APP_Timesheet as Timesheet join edhrqnfxua.APP_Task as Task on Task.APP_TaskUuid = Timesheet.Relation_APP_Task 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 Task.APP_Closed = 0 and Task.Relation_APP_Project = Project.APP_ProjectUuid /* to exclude tasks which have been moved into another project and are booked there */ group by Customer.APP_Code, Project.APP_Code, Task.APP_Code, Task.APP_TaskUuid having MAX(Timesheet.APP_EndTime) < '$($from.ToString('yyyy-MM-dd HH:mm'))' order by LastTimesheetEntry "@ $queryProjects = @" select Customer.APP_Code as 'Customer', Project.APP_Code as 'Project', Project.APP_ProjectUuid as 'ProjectUuid', MAX(Timesheet.APP_EndTime) as 'LastTimesheetEntry' 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_Closed = 0 group by Customer.APP_Code, Project.APP_Code, Project.APP_ProjectUuid having MAX(Timesheet.APP_EndTime) < '$($from.ToString('yyyy-MM-dd HH:mm'))' order by LastTimesheetEntry "@ Write-Output "Closing will be performed on projects and tasks having no timerecords later than ${from}."; Connect-TC -Credential $tcCredential -UseCredentialsManager $useCredentialsManager; Connect-TCDB -Credential $tcdbCredential -UseCredentialsManager $useCredentialsManager; # Fetching Task data $items = (Invoke-TCDBSqlCmd -Query $queryTasks) Write-Output "Fetched $($items.Count) tasks."; foreach($item in $items) { if($pscmdlet.ShouldProcess("Task $($item.Customer) - $($item.Project) - $($item.Task) (last booked $($item.LastTimesheetEntry)", "Close-TCTask")) { Write-Output "Closing Task $($item.Customer) - $($item.Project) - $($item.Task) with last booking at $($item.LastTimesheetEntry)."; Close-TCTask -Uuid $item.TaskUuid; } } # Fetching Project data $items = (Invoke-TCDBSqlCmd -Query $queryProjects) Write-Output "Fetched $($items.Count) projects."; foreach($item in $items) { if($pscmdlet.ShouldProcess("Project $($item.Customer) - $($item.Project) (last booked $($item.LastTimesheetEntry))", "Close-TCProject")) { Write-Output "Closing Project $($item.Customer) - $($item.Project) with last booking at $($item.LastTimesheetEntry)."; Close-TCProject -Uuid $item.ProjectUuid; } } |