Due in the next seven days (by day of week)
Code:
#!/bin/sh
# Ver 0.05
# Lists the specifics of overdue items
# Changes the handling of time zones
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")
DUE="($YEARZERO + t.effectiveDateDue)";
DAY=86400
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 $DUE < ($NOW + (7 * $DAY))) and ($DUE >= $TODAY)"
OVERMATCHES="status='active' and (t.dateCompleted is null) and ($DUE < $TODAY)"
NEXTMATCHES="(status='active') and ($DUE < ($NOW + (14 * $DAY))) and ($DUE > ($NOW + (7 * $DAY)))"
read overTOTAL weekTOTAL foreTOTAL <<< $($OFQUERY "
SELECT count(*) FROM $JOIN WHERE $OVERMATCHES;
SELECT count(*) FROM $JOIN WHERE $MATCHES;
SELECT count(*) FROM $JOIN WHERE $NEXTMATCHES;
")
printf "DUE (%s in coming week)\n\n" "$weekTOTAL"
$OFQUERY "
SELECT strftime('%w|%m|%d|%Y|%H:%M',$DUE, 'unixepoch'), p.name, t.name
FROM $JOIN
WHERE $MATCHES
ORDER BY t.effectiveDateDue, 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="\|"; day=0; year=0; prj=0}
{
if (day!=$3) {day=$3;
print dayname($1), monthname($2), $3, $4
}
if (prj!=$6) {prj=$6;
if (prj!="") {print " " prj ":"}
}
print " •", $5, $7
}'
printf "\nOVERDUE (%s tasks)\n\n" "$overTOTAL"
$OFQUERY "
SELECT strftime('%w|%m|%d|%Y|%H:%M',$DUE, 'unixepoch'), p.name, t.name
FROM $JOIN
WHERE $OVERMATCHES
ORDER BY t.effectiveDateDue, 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="\|"; day=0; year=0; prj=0}
{
if ($7 != "") {
if (day!=$3 || year!=$4) {day=$3; year=$4;
print "Was due " dayname($1), monthname($2), $3, $4
}
if (prj!=$6) {prj=$6;
if (prj!="") {print " " prj ":"}
}
print " •", $5, $7
}
}'
printf "\n(%s due in following week)\n" "$foreTOTAL"