#!/bin/sh
#Ver 0.04
# Time-zone handling adjusted 
# Works with Vanilla and AppStore OF
# Exclusion of completed tasks corrected
OFOC="com.omnigroup.OmniFocus"
if [ ! -d "$HOME/Library/Caches/$OFOC" ]; then OFOC=$OFOC.MacAppStore; fi
OFQUERY="sqlite3 $HOME/Library/Caches/$OFOC/OmniFocusDatabase2"

NOW=$(date +%s)  # the date command automatically allows for daylight savings like BST in the UK
#TODAY=$(date -v0H -v0M -v0S +%s) #Midnight at the start of today: set the time component to 00:00

ZONERESET=$(date +%z | awk '
{if (substr($1,1,1)!="+") {printf "+"} else {printf "-"} print substr($1,2,4)}') 
YEARZERO=$(date -j -f "%Y-%m-%d %H:%M:%S %z" "2001-01-01 0:0:0 $ZONERESET" "+%s")
DUE="($YEARZERO + t.effectiveDateDue)";
DAY=86400
DAYS_LEFT="round((($DUE-$NOW)*1.0)/$DAY,1)"
DAYS_OVERDUE="($NOW-$DUE)/$DAY"

JOIN="(((task tt left join projectinfo pi on tt.containingprojectinfo=pi.pk) t
left join task p on t.task=p.persistentIdentifier)
left join context c on t.context = c.persistentIdentifier)
left join folder f on t.folder=f.persistentIdentifier"

MATCHES="status='active' and t.dateCompleted is null and t.isDueSoon=1 and $DAYS_OVERDUE < 14"
TOTAL=$($OFQUERY "SELECT count(*) FROM $JOIN WHERE $MATCHES")

echo "DUE SOON OR IN LAST FORTNIGHT ($TOTAL tasks)"
$OFQUERY "
SELECT f.name, p.name, c.name, t.name, $DAYS_LEFT, strftime('%m-%d %H:%M',$DUE, 'unixepoch')
FROM $JOIN
WHERE $MATCHES
ORDER BY f.name, p.name, c.name, t.effectiveDateDue
" | awk '
function relativedays(days) {
	if (days<0) {return "("(-days) " days ago)"}
	else {return "("days " days left)"}
}
BEGIN {FS="\|"; fld=0; prj=0; ctx=0;}
{
	# Whenever the value of col. 1 changes, write it out as a FOLDER header,
	if ( fld!=$1) { fld=$1; if (length( fld) < 1) {print "\n(No folder)"}
	else {print "\n(Folder: " toupper( fld) ")"}};
		
	# and whenever the value of col. 2 changes, write it out as a PROJECT header.
	if (prj!=$2) {prj=$2; if (length(prj) < 1) {print "\nInbox:"}
	else {print "\n" prj ":"}};

	# Whenever the value of col. 3 changes, write it out as a [CONTEXT] sub-header.
	if (ctx!=$3) {ctx=$3; if (length(ctx) > 0) {print "[" ctx "]"}};

	# and whenever col 4. contains sth other than the project name, write it out as a • TASK.
	if (length($4) > 0 && $4!=$2) {print "• " $4, relativedays($5), $6}
}'