GeekAfterFive

Infrastructure as Code

home

Powershell Outlook Time Reporting by Category

07 Jun 2012

By request, I have revised the code from my original Outlook Time Reporting to report on Category and day of week with summary totals. A true time sheet really. In fact, I believe someone is using this to import their time into SAP...:D This was a significant change made completely easy with Powershell. The HTML and CSV output look like this: [caption id="attachment_606" align="alignnone" width="446"]a picture Sample HTML report[/caption]   Here's the new code, but please leave me a comment if you use this! I'd love to hear about it!
#
# OutlookTimeReport.ps1
# Jake Robinson
# v1.2
#
#
#
#

param ( [DateTime] $rangeStart = (get-date -hour 0 -minute 0 -second 0).AddDays(-(get-date).DayOfWeek.value__),
 [DateTime] $rangeEnd = (get-date -hour 23 -minute 59 -second 59).AddDays(7-(get-date).DayOfWeek.value__),
 [String] $categories,
 [String] $calendar,
 [switch] $csv,
 [switch] $pickdates)

function pick-date()
{
 $objForm = New-Object Windows.Forms.Form

$objForm.Text = "Select a Date"
 $objForm.Size = New-Object Drawing.Size @(190,190)
 $objForm.StartPosition = "CenterScreen"

$objForm.KeyPreview = $True

$objForm.Add_KeyDown({
 if ($_.KeyCode -eq "Enter")
 {
 $dtmDate=$objCalendar.SelectionStart
 $objForm.Close()
 }
 })

$objForm.Add_KeyDown({
 if ($_.KeyCode -eq "Escape")
 {
 $objForm.Close()
 }
 })

$objCalendar = New-Object System.Windows.Forms.MonthCalendar
 $objCalendar.ShowTodayCircle = $False
 $objCalendar.MaxSelectionCount = 1
 $objForm.Controls.Add($objCalendar)

$objForm.Topmost = $True

$objForm.Add_Shown({$objForm.Activate()})
 [void] $objForm.ShowDialog()

if ($dtmDate)
 {
 return $dtmDate
 }
}

if ($pickdates)
{
 [void] [System.Reflection.Assembly]::LoadWithPartialName("System.Windows.Forms")
 [void] [System.Reflection.Assembly]::LoadWithPartialName("System.Drawing")

 [DateTime] $pickedStart = pick-date
 [DateTime] $pickedEnd = pick-date

 If ($pickedStart -and $pickedEnd)
 {
 $rangeStart = $pickedStart
 $rangeEnd = $pickedEnd
 }
}

Add-Type -AssemblyName Microsoft.Office.Interop.Outlook

$class = @
using Microsoft.Office.Interop.Outlook;public class MyOL
{
 public MAPIFolder GetCalendar(string userName)
 {
 Application oOutlook = new Application();
 NameSpace oNs = oOutlook.GetNamespace("MAPI");
 Recipient oRep = oNs.CreateRecipient(userName);
 MAPIFolder calendar = oNs.GetSharedDefaultFolder(oRep, OlDefaultFolders.olFolderCalendar);
 return calendar;
 }
}
@

Add-Type $class -ReferencedAssemblies Microsoft.Office.Interop.Outlook

$outlook = new-object MyOL
$restriction = "[End] >= '{0}' AND [Start] <= '{1}'" -f $rangeStart.ToString("g"), $rangeEnd.ToString("g")
$seArray = @()
$categoryMatch = [string]$categories -replace ' ','|'
$SECalendar = $outlook.GetCalendar($calendar)
$appointments = $SECalendar.items
$appointments.Sort("[Start]")
$appointments.IncludeRecurrences = $true
$SEappts = $appointments.Restrict($restriction) | where {$_.categories -match $categoryMatch}

# Group the appointments by category
$grouped = $SEappts | Group-Object categories

# This is our table array
$table = @()

# Days of the week. Are these language sensitive?
$dow = @("Monday","Tuesday","Wednesday","Thursday","Friday")
# Change hash values to local language if required.
$dowHash = @{"Monday"="Monday"; "Tuesday"="Tuesday"; "Wednesday"="Wednesday"; "Thursday"="Thursday"; "Friday"="Friday"}

# For each category...
foreach ($group in $grouped)
{
 # Start a new row...
 $row = New-Object PSObject

 # Add the category name to the start of our table...
 Add-Member -MemberType NoteProperty -Name "Category" -value $group.name -InputObject $row

# For each work day
 foreach ($day in $dow)
 {
 # Sum up the hours for that category.
 $hours = ($group.group | where {$_.start.dayofweek -eq $day} | Measure-Object -Sum -Property duration).sum /60

# Then add it to our table row.
 Add-Member -MemberType NoteProperty -Name $dowHash.($day) -Value $hours -InputObject $row
 }
 $table += $row
}


# Summary at the end
$summary = New-Object PSObject
Add-Member -MemberType NoteProperty -Name "Category" -value "SUMMARY" -InputObject $summary
foreach ($day in $dow)
 {
 # Sum up the hours for that category.
 $hours = ($SEappts | where {$_.start.dayofweek -eq $day} | Measure-Object -Sum -Property duration).sum /60

# Then add it.
 Add-Member -MemberType NoteProperty -Name $dowHash.($day) -Value $hours -InputObject $summary
 }
$table += $summary

# Get TOTAL hours for final HTML report
$totalHours = ($SEappts | Measure-Object -Sum -Property duration).sum /60


if ($csv)
{
 $table | Export-Csv -NoTypeInformation -Path "$HOME\desktop\OutlookTimeReport.csv"
 Invoke-Item "$HOME\desktop\OutlookTimeReport.csv"
}
else
{
 $totalpost = "<table><tr><td><b>TOTAL HOURS:</b></td><td><b>$($totalHours)</b></td></tr><table>"
 $seHTML = $table | ConvertTo-Html -PostContent $totalpost | Out-File "$HOME\desktop\OutlookTimeReport.html"
 Invoke-Item "$HOME\desktop\OutlookTimeReport.html"
}
comments powered by Disqus