There was a quick stats script that used OF cache to extract the number of various types of projects and tasks (active, completed, …). I was wondering if there is a similar script for OF3?
This is the version I have:
property pstrDBPath : “$HOME/Library/Containers/com.omnigroup.OmniFocus2.MacAppStore/Data/Library/Application Support/OmniFocus/Omnifocus”
property pstrMinOSX : “10.6”
property pblnToClipboard : true
property pblnSubTreeCounts : true
property pToClipboard : “Copy list to clipboard”
– 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
tell application “Finder”
if (version < pstrMinOSX) then
display dialog “This script requires OSX " & pstrMinOSX & " or higher” buttons {“OK”} default button 1 with title pTitle & "Ver. " & pVersion
return
end if
if pstrDBPath ≠ "" then
set strCmd to "sqlite3 -separator ': ' \"" & pstrDBPath & "\" " & quoted form of ("
select \"INBOX GROUPS & ACTIONS\", 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 effectiveActive=1;
select \" Dropped folders\", count(*) from folder where effectiveActive=0;
select null;
select \"PROJECTS\", count(*) from projectInfo where containsSingletonActions=0;
select \" Active projects\", count(*) from projectInfo where (containsSingletonActions=0) and (status=\"active\");
select \" Current projects\", count(*) from projectInfo p join task t on t.projectinfo=p.pk where (p.containsSingletonActions=0) and (p.folderEffectiveActive=1) and (p.status=\"active\") and (t.dateToStart is null or t.dateToStart < (strftime('%s','now') - strftime('%s','2001-01-01')));
select \" Pending projects\", count(*) from projectInfo p join task t on t.projectinfo=p.pk where (p.containsSingletonActions=0) and (p.folderEffectiveActive=1) 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 (folderEffectiveActive=0));
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 (folderEffectiveActive=0));
select null;
select \"CONTEXTS\", count(*) from context;
select \" Active contexts\", count(*) from context where (effectiveActive=1) and (allowsNextAction=1);
select \" On-hold contexts\", count(*) from context where (effectiveActive=1) and allowsNextAction=0;
select \" Dropped contexts\", count(*) from context where effectiveActive=0;
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 (childrenCount>0);
select \" Completed action groups\", count(dateCompleted) from task where (projectinfo is null) and (childrenCount>0);
select null;
select \"ACTIONS\", count(*) from task where (projectinfo is null) and (childrenCount=0);
select \" Completed actions\", count(dateCompleted) from task where (projectinfo is null) and (childrenCount=0);
select \" Dropped project actions\", count(*) from (task t left join projectinfo p on t.containingProjectinfo=p.pk) tp where (projectinfo is null) and (childrenCount=0) and (dateCompleted is null)
and (tp.containingProjectinfo is not null and (tp.status=\"dropped\" or tp.folderEffectiveActive=0));
select \" Dropped context actions\", count(*) from (task t left join projectinfo p on t.containingProjectinfo=p.pk) tp left join context c on tp.context=c.persistentIdentifier where (projectinfo is null) and (tp.childrenCount=0) and (dateCompleted is null)
and (tp.containingProjectinfo is null or (tp.status !=\"dropped\" and tp.folderEffectiveActive=1))
and c.effectiveActive= 0;
select \" Remaining actions\", count(*) from (task t left join projectinfo p on t.containingProjectinfo=p.pk) tp left join context c on tp.context=c.persistentIdentifier where (projectinfo is null) and (tp.childrenCount=0) and (dateCompleted is null)
and (tp.containingProjectinfo is null or (tp.status !=\"dropped\" and tp.folderEffectiveActive=1))
and (tp.context is null or c.effectiveActive= 1);
select null;
select \" Actions in Projects on hold\", count(*) from (task t left join projectinfo p on t.containingProjectinfo=p.pk) tp left join context c on tp.context=c.persistentIdentifier where (projectinfo is null) and (tp.childrenCount=0) and (dateCompleted is null)
and (tp.containingProjectinfo is null or (tp.status !=\"dropped\" and tp.folderEffectiveActive=1))
and (tp.context is null or c.effectiveActive= 1)
and (tp.containingProjectInfo is not null and tp.status=\"inactive\");
select \" Actions in Contexts on hold\", count(*) from (task t left join projectinfo p on t.containingProjectinfo=p.pk) tp left join context c on tp.context=c.persistentIdentifier where (projectinfo is null) and (tp.childrenCount=0) and (dateCompleted is null)
and (tp.containingProjectinfo is null or (tp.status !=\"dropped\" and tp.folderEffectiveActive=1))
and (tp.context is null or c.effectiveActive= 1)
and (tp.containingProjectInfo is null or tp.status!=\"inactive\")
and (tp.context is not null and c.allowsNextAction=0);
select null;
select \" Blocked actions\", count(*) from (task t left join projectinfo p on t.containingProjectinfo=p.pk) tp left join context c on tp.context=c.persistentIdentifier where (projectinfo is null) and (tp.childrenCount=0) and (dateCompleted is null)
and (tp.containingProjectinfo is null or (tp.status !=\"dropped\" and tp.folderEffectiveActive=1))
and (tp.context is null or c.effectiveActive= 1)
and (tp.containingProjectInfo is null or tp.status!=\"inactive\")
and (tp.context is null or c.allowsNextAction=1)
and tp.blocked=1;
select \" Sequentially blocked\", count(*) from (task t left join projectinfo p on t.containingProjectinfo=p.pk) tp left join context c on tp.context=c.persistentIdentifier where (projectinfo is null) and (tp.childrenCount=0) and (dateCompleted is null)
and (tp.containingProjectinfo is null or (tp.status !=\"dropped\" and tp.folderEffectiveActive=1))
and (tp.context is null or c.effectiveActive= 1)
and (tp.containingProjectInfo is null or tp.status!=\"inactive\")
and (tp.context is null or c.allowsNextAction=1)
and tp.blocked=1
and tp.blockedByFutureStartDate=0;
select \" Awaiting start date\", count(*) from (task t left join projectinfo p on t.containingProjectinfo=p.pk) tp left join context c on tp.context=c.persistentIdentifier where (projectinfo is null) and (tp.childrenCount=0) and (dateCompleted is null)
and (tp.containingProjectinfo is null or (tp.status !=\"dropped\" and tp.folderEffectiveActive=1))
and (tp.context is null or c.effectiveActive= 1)
and (tp.containingProjectInfo is null or tp.status!=\"inactive\")
and (tp.context is null or c.allowsNextAction=1)
and tp.blocked=1
and tp.blockedByFutureStartDate=1;
select null;
select \" Available actions\", count(*) from (task t left join projectinfo p on t.containingProjectinfo=p.pk) tp left join context c on tp.context=c.persistentIdentifier where (projectinfo is null) and (tp.childrenCount=0) and (dateCompleted is null)
and (tp.containingProjectinfo is null or (tp.status !=\"dropped\" and tp.folderEffectiveActive=1))
and (tp.context is null or c.effectiveActive= 1)
and (tp.containingProjectInfo is null or tp.status!=\"inactive\")
and (tp.context is null or c.allowsNextAction=1)
and tp.blocked=0;
")
-- try
set strList to do shell script strCmd
-- on error
-- display dialog "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
activate
if button returned of (display dialog strList buttons {pToClipboard, "OK"} default button "OK" with title pTitle & " Ver. " & pVersion) ¬
is pToClipboard then tell application id "com.apple.finder" to set the clipboard to strList
else
display dialog "OmniFocus cache not found ..." buttons {"OK"} default button 1 with title pTitle & " Ver. " & pVersion
end if
end tell
on FileExists(strPath)
(do shell script “test -e " & strPath & " ; echo $?”) = “0”
end FileExists
on GetCachePath()
try
tell application “Finder” to tell (application file id “OFOC”) to “$HOME/Library/Caches/” & its id & “/OmniFocusDatabase2”
on error
error “OmniFocus not installed …”
end try
end GetCachePath
Thank you.
