Forecast view listings for Mac desktop
 

The [B]Forecast[/B] view on the iPad version of OmniFocus is generally found very useful, often requested for the Mac version, and doubtless planned or in progress for Mac OF 2.

In the meanwhile, as a stop-gap, here are the first rough drafts of shell scripts which can be used in NerdTool or GeekTool to get 7 day Forecast listings on the Mac Desktop, even when OmniFocus and iCal are not running.

There are three draft shell scripts:[LIST=1][*]Due in the next seven days (by day of week)[*]Calendared in the next seven days (by day of week, from iCal)[*]Starting in the next seven days (by day of week)[/LIST]
Note that in NerdTool (which I personally use because it is a bit less resource-hungry) you should set the encoding to UTF-8.

[IMG]http://forums.omnigroup.com/attachment.php?attachmentid=2500&stc=1&d=1345583166[/IMG]

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"[/CODE]

Calendared in the next seven days (by day of week, from iCal)

[CODE]#!/bin/sh
# ver 0.15
# Allows for flexible horizon - edit value of FIRSTDAY and LASTDAY in first line
# FIRSTDAY=0 starts from today (midnight last night)
# FIRSTDAY=1 is from midnight tonight, etc.

FIRSTDAY=0
LASTDAY=7
OSXVER=$(sw_vers -productVersion | cut -d . -f 2)

if [ $OSXVER -eq 7 ]; then
CALTABLE=zicselement
REMCODE=24
LOCN=zlocation1
elif [ $OSXVER -ge 8 ]; then
CALTABLE=zicselement
REMCODE=24
LOCN=zlocation
else
CALTABLE=zcalendaritem
REMCODE=9
LOCN=zlocation
fi

NOW=$(date +%s)
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")
STARTDATE="($YEARZERO + zstartdate)";
ENDDATE="($YEARZERO + zenddate)";
DAY=86400;
HORIZON=$(($NOW + ($LASTDAY * $DAY)));

TODAY=$(date -v0H -v0M -v0S +%s) #Midnight: set the time component to 00:00
START=$(($TODAY+($FIRSTDAY * $DAY)))

MATCHES="(z_ent!=$REMCODE) and ($STARTDATE >= $START) and ($STARTDATE < $HORIZON)"
TOTAL=$(sqlite3 $HOME/Library/Calendars/Calendar\ Cache "
SELECT count(*) FROM $CALTABLE WHERE $MATCHES")

if [ $FIRSTDAY -eq 0 ]; then
printf "%s\n\n" "CALENDAR ($TOTAL events in next $LASTDAY days)"
else
printf "%s\n\n" "CALENDAR ($TOTAL events between $FIRSTDAY and $LASTDAY days ahead)"
fi

# %w day of week, %m month, %d day of month, %H %M Hour Month
sqlite3 $HOME/Library/Calendars/Calendar\ Cache "
SELECT strftime('%w|%m|%d|%H:%M',$STARTDATE, 'unixepoch'),
ztitle, replace($LOCN,'
',' '), zisallday, strftime('%H:%M',$ENDDATE, 'unixepoch')
FROM $CALTABLE
WHERE $MATCHES
ORDER BY zstartdate
" | awk '
function monthname(i) {
return substr("JanFebMarAprMayJunJulAugSepOctNovDec",((i-1)*3)+1,3)
}
function dayname(i) {
return substr("SunMonTueWedThuFriSat",(i*3)+1,3)
}
BEGIN {FS="\|"; mday=0; wkday=0}
{
if (mday!=$3) {mday=$3; wkday=$1;
print dayname(wkday), monthname($2), mday
}
printf " • "; if ($7!=1) {printf $4 "-" $8 " "}; printf $5
if ($6!="") {printf " [" $6 "]"}
printf "\n"
}'[/CODE]

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)"[/CODE]

[QUOTE=RobTrew;113776]Calendared in the next seven days (by day of week, from iCal)[/QUOTE]

Fixed a bug (ver 9, above) involving multi-line location fields.

[QUOTE=RobTrew;113776]Calendared in the next seven days (by day of week, from iCal)[/QUOTE]


Edited the iCal script above to make the horizons flexible.
The integer values in the first two lines can be adjusted:

Defaults to the coming week:
[CODE]# FIRSTDAY=0 starts from today (midnight last night)
#FIRSTDAY=1 is from midnight tonight, and so on ...
FIRSTDAY=0
LASTDAY=7[/CODE]

but can be simply edited, for example for the following week:
[CODE]FIRSTDAY=8
LASTDAY=14[/CODE]

Tried this out. Cool! But I seem to be getting some unexpected items mixed into the one that shows items that are due and overdue.

Does the script filter out items in projects that have been dropped? I store templates in a dropped state, so they are out of the way.

It also seems like it pulls items underneath a project that has been marked completed. So if you marked the project completed without marking each individual item underneath it completed, then these items are displayed as overdue.

KS

It would also be great if the dates had the year as well. It's embarrassing, but some overdue items are very over due. This view is actually helping me to get rid of some of those <g>.

To many of mine are showing as imminently overdue so I'll have to leave that as an exercise for the reader, for the moment :-)

For the display of years with [I]strftime[/I]:
[URL="http://www.sqlite.org/lang_datefunc.html"]http://www.sqlite.org/lang_datefunc.html[/URL]

To eliminate uncompleted tasks of completed projects, I think you could begin by trying to expand [I]t.dateCompleted is null[/I] in MATCHES to something like [I] p.status = 'active' and t.dateCompleted is null[/I]