This code does the task of outlook time reporting sheet with all the categories and time of the day and the summary for a week. Hope it will help someone out there to import their time into SAP. This is all made easy with powershell’ here is the code:

#

# OutlookTimeReport.ps1

# 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 “$HOMEdesktopOutlookTimeReport.csv”

 Invoke-Item “$HOMEdesktopOutlookTimeReport.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 “$HOMEdesktopOutlookTimeReport.html”

 Invoke-Item “$HOMEdesktopOutlookTimeReport.html”

}

Leave a Reply

You must be logged in to post a comment.