View Single Post
Starting in the next seven days (by day of week)

Code:
#!/bin/sh
# Ver 0.2
OFOC="com.omnigroup.OmniFocus"
if [ ! -d "$HOME/Library/Caches/$OFOC" ]; then OFOC=$OFOC.MacAppStore; fi
OFQUERY="sqlite3 $HOME/Library/Caches/$OFOC/OmniFocusDatabase2"

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

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")
STARTS="($YEARZERO + t.effectiveDateToStart)";

MATCHES="($STARTS < ($NOW + (7 * $DAY))) and ($STARTS >= $TODAY)"
#TOTAL=$($OFQUERY "SELECT count(*) FROM task t WHERE $MATCHES")
read weekTOTAL foreTOTAL  <<< $($OFQUERY "
	SELECT count(*) FROM task t WHERE $MATCHES;
	SELECT count(*) FROM task t 
		WHERE ($STARTS < ($NOW + (14 * $DAY))) and ($STARTS > ($NOW + (7 * $DAY)));
")

echo "STARTING ($weekTOTAL in coming week)
"
$OFQUERY "
SELECT strftime('%w|%m|%d|%H:%M',$STARTS, 'unixepoch'), p.name, t.name
FROM (((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
WHERE status='active' and $MATCHES
ORDER BY t.effectiveDateToStart, f.name, p.name, c.name
" | awk '
function monthname(i) {
	return substr("JanFebMarAprMayJunJulAugSepOctNovDec",((i-1)*3)+1,3)
}
function dayname(i) {
	return substr("SunMonTueWedThuFriSat",(i*3)+1,3)
}
BEGIN {FS="\|"; wkday=0; prj=0}
{
	if (wkday!=$1) {wkday=$1;
		print "Starting " dayname(wkday), monthname($2), $3
	}
	if (prj!=$5) {prj=$5;
		if (prj!="") {print "    " prj ":"}
	}
	printf "     "
	if ($4!="00:00") {printf "at " $4 " "}; print $6
}'
echo "
($foreTOTAL starting in following week)"

Last edited by RobTrew; 2012-10-16 at 04:43 AM.. Reason: Edited to work with both versions of OF - vanilla and AppStore