Seems that the modified Rob Trew OFStats script is reporting wildly inaccurate numbers. I can run the script and according to it I’ve got 189 active projects but if I actually count them there are less than 80.
This is the version I have and it’s supposed to be working with OF3 but it seems that sometime about February it started reporting inaccurate results. I haven’t really had time to deal with it but I’m finding I really miss the feedback of seeing the numbers change after my weekly review. Does anyone have any suggestions on how to fix it?
(*
Ver 0.8 adds clipboard option to dialogue
Ver 0.9 gives an error message if the cache schema has changed, leading to an SQL error in the script
Ver 1.0 slight simplification of the code
Ver 1.1 added count of Pending projects
Ver 1.2 added a count of available actions
Ver 1.3 added a break-down of unavailable actions
Ver 1.4 added count of Current projects to complement Pending projects
Ver 1.5 replaced Applescript time function with SQL time expression
Ver 1.7 reorganizes menu, and attempts to enable access for .macappstore installations of OF
Ver 1.8 adjusts handling of variant bundle identifiers generally
Ver 2.00 redraft of task breakdown, with progressive narrowing of criteria …
Ver 2.01 change for OF2. pstrDBPath reset
Ver 3.0 reworked for OF3:
- Updated query to match schema changes for tags
- Automatically checks both Omni Store and MAS containers for SQL database
- dialog presented when there is a SQL error now includes the specific SQL error message returned
- Reorganized the code, mostly separating out the query string as a property
Ver 3.1 adjusts for schema change from dropped task feature added in OF v3.4
*)
property pTitle : “OmniFocus: Quick Stats”
property pVersion : “3.1”
property pstrOmniStoreDBPath : “$HOME/Library/Containers/com.omnigroup.OmniFocus3/Data/Library/Application Support/OmniFocus/OmniFocus Caches/OmniFocusDatabase”
property pstrMacAppStoreDBPath : “$HOME/Library/Containers/com.omndigroup.OmniFocus3.MacAppStore/Data/Library/Application Support/OmniFocus/OmniFocus Caches/OmniFocusDatabase”
property pblnToClipboard : true
property pblnSubTreeCounts : true
property pToClipboard : “Copy list to clipboard”
property enableDebug : true
property pstrQuery : ¬
" SELECT “INBOX ITEMS”, COUNT() FROM task
WHERE (inInbox=1);
SELECT " Inbox action groups", COUNT() FROM task
WHERE (inInbox=1) AND (childrenCount>0);
SELECT \" Inbox actions\", COUNT(*) FROM task
WHERE (inInbox=1) AND (childrenCount=0);
SELECT null;
SELECT \"FOLDERS\", COUNT(*) FROM folder;
SELECT \" Active folders\", COUNT(*) FROM folder
WHERE effectiveDateHidden is null;
SELECT \" Dropped folders\", COUNT(*) FROM folder
WHERE effectiveDateHidden is not null;
SELECT null;
SELECT \"PROJECTS\", COUNT(*) FROM projectInfo
WHERE containsSingletonActions=0;
SELECT \" Active projects\", COUNT(*) FROM projectInfo
WHERE (containsSingletonActions=0) AND (status=\"active\");
SELECT \" Current (available) projects\", COUNT(*) FROM projectInfo p join task t on t.projectinfo=p.pk
WHERE (p.containsSingletonActions=0) AND (p.folderEffectiveDateHidden is null) AND (p.status=\"active\")
AND (t.dateToStart is null OR t.dateToStart < (strftime('%s','now') - strftime('%s','2001-01-01')));
SELECT \" Pending (deferred) projects\", COUNT(*) FROM projectInfo p join task t on t.projectinfo=p.pk
WHERE (p.containsSingletonActions=0) AND (p.folderEffectiveDateHidden is null) AND (p.status=\"active\")
AND (t.dateToStart > (strftime('%s','now') - strftime('%s','2001-01-01')));
SELECT \" On Hold projects\", COUNT(*) FROM projectInfo
WHERE (containsSingletonActions=0) AND (status=\"inactive\");
SELECT \" Completed projects\", COUNT(*) FROM projectInfo
WHERE (containsSingletonActions=0) AND (status=\"done\");
SELECT \" Dropped projects\", COUNT(*) FROM projectInfo
WHERE (containsSingletonActions=0) AND (( status=\"dropped\") OR (folderEffectiveDateHidden is not null));
SELECT null;
SELECT \"SINGLE ACTION LISTS\", COUNT(*) FROM projectInfo
WHERE containsSingletonActions=1;
SELECT \" Active single action lists\", COUNT(*) FROM projectInfo
WHERE (containsSingletonActions=1) AND (status=\"active\");
SELECT \" On Hold single action lists\", COUNT(*) FROM projectInfo
WHERE (containsSingletonActions=1) AND (status=\"inactive\");
SELECT \" Completed single action lists\", COUNT(*) FROM projectInfo
WHERE (containsSingletonActions=1) AND (status=\"done\");
SELECT \" Dropped single action lists\", COUNT(*) FROM projectInfo
WHERE (containsSingletonActions=1) AND ((status=\"dropped\") OR (folderEffectiveDateHidden is not null));
SELECT null;
SELECT \"TAGS\", COUNT(*) FROM context;
SELECT \" Active tags\", COUNT(*) FROM context
WHERE (effectiveDateHidden is null) AND (allowsNextAction=1);
SELECT \" On Hold tags\", COUNT(*) FROM context
WHERE (effectiveDateHidden is null) AND (allowsNextAction=0);
SELECT \" Dropped tags\", COUNT(*) FROM context
WHERE effectiveDateHidden is not null;
SELECT null;
SELECT \"ACTION GROUPS\", COUNT(*) FROM task
WHERE (projectinfo is null) AND (childrenCount>0);
SELECT \" Remaining action groups\", COUNT(*) FROM task
WHERE (projectinfo is null) AND (dateCompleted is null) AND (effectiveDateHidden is null) AND (childrenCount>0);
SELECT \" Completed action groups\", COUNT(*) FROM task
WHERE (projectinfo is null) AND (dateCompleted is not null) AND (childrenCount>0);
SELECT \" Dropped action groups\", COUNT(*) FROM task
WHERE (projectinfo is null) AND (effectiveDateHidden is not null) AND (childrenCount>0);
SELECT null;
SELECT \"ACTIONS\", COUNT(*) FROM task
WHERE (projectinfo is null) AND (childrenCount=0);
SELECT \" Remaining actions\", COUNT(*) FROM task t
LEFT JOIN projectinfo p on t.containingProjectinfo=p.pk
LEFT JOIN tasktotag tg on t.persistentIdentifier=tg.task
LEFT JOIN context c on tg.tag=c.persistentIdentifier
WHERE (t.projectinfo is null) AND (t.childrenCount=0) AND (t.dateCompleted is null)
AND (t.containingProjectinfo is null OR (p.status !=\"dropped\" AND p.folderEffectiveDateHidden is null))
AND (t.tagged=0 OR c.effectiveDateHidden is null);
SELECT \" Available actions\", COUNT(*) FROM task t
LEFT JOIN projectinfo p on t.containingProjectinfo=p.pk
LEFT JOIN tasktotag tg on t.persistentIdentifier=tg.task
LEFT JOIN context c on tg.tag=c.persistentIdentifier
WHERE (t.projectinfo is null) AND (t.childrenCount=0) AND (t.dateCompleted is null)
AND (t.containingProjectinfo is null OR (p.status !=\"dropped\" AND p.folderEffectiveDateHidden is null))
AND (t.tagged=0 OR c.effectiveDateHidden is null) AND (t.containingProjectInfo is null OR p.status!=\"inactive\")
AND (t.tagged=0 OR c.allowsNextAction=1) AND t.blocked=0;
SELECT \" Actions in On Hold projects\", COUNT(*) FROM task t
LEFT JOIN projectinfo p on t.containingProjectinfo=p.pk
LEFT JOIN tasktotag tg on t.persistentIdentifier=tg.task
LEFT JOIN context c on tg.tag=c.persistentIdentifier
WHERE (t.projectinfo is null) AND (t.childrenCount=0) AND (t.dateCompleted is null)
AND (t.containingProjectinfo is null OR (p.status !=\"dropped\" AND p.folderEffectiveDateHidden is null))
AND (t.tagged=0 OR c.effectiveDateHidden is null) AND (t.containingProjectInfo is not null AND p.status=\"inactive\");
SELECT \" Actions with On Hold tags\", COUNT(*) FROM task t
LEFT JOIN projectinfo p on t.containingProjectinfo=p.pk
LEFT JOIN tasktotag tg on t.persistentIdentifier=tg.task
LEFT JOIN context c on tg.tag=c.persistentIdentifier
WHERE (t.projectinfo is null) AND (t.childrenCount=0) AND (t.dateCompleted is null)
AND (t.containingProjectinfo is null OR (p.status !=\"dropped\" AND p.folderEffectiveDateHidden is null))
AND (t.tagged=0 OR c.effectiveDateHidden is null) AND (t.containingProjectInfo is null OR p.status=\"inactive\")
AND (t.tagged=1 AND c.allowsNextAction=0);
SELECT \" Blocked actions\", COUNT(*) FROM task t
LEFT JOIN projectinfo p on t.containingProjectinfo=p.pk
LEFT JOIN tasktotag tg on t.persistentIdentifier=tg.task
LEFT JOIN context c on tg.tag=c.persistentIdentifier
WHERE (t.projectinfo is null) AND (t.childrenCount=0) AND (t.dateCompleted is null)
AND (t.containingProjectinfo is null OR (p.status !=\"dropped\" AND p.folderEffectiveDateHidden is null))
AND (t.tagged=0 OR c.effectiveDateHidden is null) AND (t.containingProjectInfo is null OR p.status!=\"inactive\")
AND (t.tagged=0 OR c.allowsNextAction=1) AND t.blocked=1;
SELECT \" Blocked by future defer date\", COUNT(*) FROM task t
LEFT JOIN projectinfo p on t.containingProjectinfo=p.pk
LEFT JOIN tasktotag tg on t.persistentIdentifier=tg.task
LEFT JOIN context c on tg.tag=c.persistentIdentifier
WHERE (t.projectinfo is null) AND (t.childrenCount=0) AND (t.dateCompleted is null)
AND (t.containingProjectinfo is null OR (p.status !=\"dropped\" AND p.folderEffectiveDateHidden is null))
AND (t.tagged=0 OR c.effectiveDateHidden is null) AND (t.containingProjectInfo is null AND p.status!=\"inactive\")
AND (t.tagged=0 OR c.allowsNextAction=1) AND t.blocked=1 AND t.blockedByFutureStartDate=1;
SELECT \" Sequentially blocked\", COUNT(*) FROM task t
LEFT JOIN projectinfo p on t.containingProjectinfo=p.pk
LEFT JOIN tasktotag tg on t.persistentIdentifier=tg.task
LEFT JOIN context c on tg.tag=c.persistentIdentifier
WHERE (t.projectinfo is null) AND (t.childrenCount=0) AND (t.dateCompleted is null)
AND (t.containingProjectinfo is null OR (p.status !=\"dropped\" AND p.folderEffectiveDateHidden is null))
AND (t.tagged=0 OR c.effectiveDateHidden is null) AND (t.containingProjectInfo is null OR p.status!=\"inactive\")
AND (t.tagged=0 OR c.allowsNextAction=1) AND t.blocked=1 AND t.blockedByFutureStartDate=0;
SELECT \" Completed actions\", COUNT(dateCompleted) FROM task
WHERE (projectinfo is null) AND (childrenCount=0);
SELECT \" Dropped actions\", COUNT (*) FROM task t
LEFT JOIN projectinfo p on t.containingProjectinfo=p.pk
LEFT JOIN tasktotag tg on t.persistentIdentifier=tg.task
LEFT JOIN context c on tg.tag=c.persistentIdentifier
WHERE (t.projectinfo is null AND t.childrenCount=0 AND t.dateCompleted is null)
AND ((t.effectiveDateHidden is not null) OR ((t.containingProjectinfo is not null)
AND (p.status=\"dropped\" OR p.folderEffectiveDateHidden is not null)) OR ((t.containingProjectinfo is null)
OR ((p.status !=\"dropped\") AND (p.folderEffectiveDateHidden is null)) AND (c.effectiveDateHidden is not null)));
SELECT \" Dropped directly\", COUNT(*) FROM task
WHERE (projectinfo is null) AND (childrenCount=0) AND (dateCompleted is null) AND (effectiveDateHidden is not null);
SELECT \" Dropped by project\", COUNT(*) FROM task t
LEFT JOIN projectinfo p on t.containingProjectinfo=p.pk
WHERE (t.projectinfo is null) AND (childrenCount=0) AND (t.dateCompleted is null)
AND (t.containingProjectinfo is not null AND (p.status=\"dropped\" OR p.folderEffectiveDateHidden is not null));
SELECT \" Dropped by tag\", COUNT(*) FROM task t
LEFT JOIN projectinfo p on t.containingProjectinfo=p.pk
LEFT JOIN tasktotag tg on t.persistentIdentifier=tg.task
LEFT JOIN context c on tg.tag=c.persistentIdentifier
WHERE (t.projectinfo is null) AND (t.childrenCount=0) AND (t.dateCompleted is null)
AND (t.containingProjectinfo is null OR (p.status !=\"dropped\" AND p.folderEffectiveDateHidden is null))
AND (t.tagged=1 AND c.effectiveDateHidden is not null);
"
on run
-- Check to see if there is a OmniFocus SQL database cache
set omniStorPathExists to checkPathExists(pstrOmniStoreDBPath)
set macAppStorePathExists to checkPathExists(pstrMacAppStoreDBPath)
if omniStorPathExists then
set strDBPath to pstrOmniStoreDBPath
else if macAppStorePathExists then
set strDBPath to pstrMacAppStoreDBPath
else
display dialog "OmniFocus cache (SQL database) not found ..." buttons {"OK"} default button 1 ¬
with title pTitle & " Ver. " & pVersion
return
end if
try
-- Run query to get list of stats
set strStatsList to do shell script "sqlite3 -separator ': ' \"" & strDBPath & "\" " & quoted form of pstrQuery
on error errMsg
display dialog ¬
errMsg & ¬
return & return & ¬
"The SQL schema for the OmniFocus cache may have changed in a recent update of OF." & ¬
return & return & ¬
"Look on the OmniFocus user forums for an updated version of this script." buttons {"OK"} ¬
with title pTitle & "Ver. " & pVersion
return
end try
--Show the results
activate
set statsWindow to display dialog strStatsList buttons {pToClipboard, "OK"} default button ¬
"OK" with title pTitle & " Ver. " & pVersion
if button returned of statsWindow is pToClipboard then set the clipboard to strStatsList
end run
on checkPathExists(strDBPath)
set pathExists to do shell script “test -f “” & strDBPath & “” && echo ‘true’ || echo ‘false’”
if pathExists is "true" then
return true
else
return false
end if
end checkPathExists