property pstrSubject : "TPS Report"
property pstrGreeting : "Boss Name,"
property pstrThisWeek : "Here's what I did this week:"
property pstrNextWeek : "Here's what I plan on doing next week (with due dates):"
property pstrRecpName : "Boss"
property pstrRecpAddr : "the_boss@megacorp.com"

property pDateFormat : "%m/%d/%Y"

-- OF SQL TIME BEGINS AT MIDNIGHT AT THE START OF 2001-01-01
property pDateZero : "strftime('%s','2001-01-01')"
property pLastWeek : "(strftime('%s','now','-7 days') - " & pDateZero & ")"
property pNextWeek : "(strftime('%s','now','+7 days') - " & pDateZero & ")"

on OFDate(strFieldName)
	"strftime('" & pDateFormat & "', " & strFieldName & " + " & pDateZero & ", 'unixepoch')"
end OFDate

set text item delimiters to "|"
set lstFields to text items of (do shell script "sqlite3 ~/Library/Caches/com.omnigroup.OmniFocus/OmniFocusDatabase2 " & quoted form of ("
SELECT \"" & pstrGreeting & "\"; select null; 

SELECT \"" & pstrThisWeek & "\"; select null;
SELECT " & OFDate("dateModified") & ", name 
FROM task WHERE (inInbox=0) and (dateModified > " & pLastWeek & ")
ORDER BY dateModified; select null;

SELECT \"" & pstrNextWeek & "\"; select null;
SELECT " & OFDate("dateDue") & ", name 
FROM task WHERE (inInbox=0) and (dateCompleted is null) and (dateModified < " & pLastWeek & ") 
	and (dateDue > " & pLastWeek & ") and (dateDue < " & pNextWeek & ") 
ORDER BY dateDue;"))

set text item delimiters to space
tell application id "com.apple.mail"
	tell (make new outgoing message with properties {visible:true, subject:pstrSubject, content:lstFields as string})
		make new to recipient at end of to recipients with properties {name:pstrRecpName, address:pstrRecpAddr}
	end tell
	activate
end tell