-- Sorts Flagged and Due Soon tasks by Due Date
-- and lists tasks, with due dates, under project headings

property pstrDBPath : "~/Library/Caches/com.omnigroup.OmniFocus/OmniFocusDatabase2"
property pDelim : "~|~"

on run
	set lstLines to paragraphs of (do shell script "sqlite3 -separator " & quoted form of pDelim & space & pstrDBPath & space & quoted form of ¬
		"SELECT proj.name, tsk.name, tsk.effectiveFlagged, strftime('%m/%d/%Y', tsk.effectivedateDue + strftime('%s','2001-01-01'), 'unixepoch')
		FROM (task t left join projectinfo p on t.containingprojectinfo=p.pk) tsk join task proj on proj.projectinfo=tsk.pk
		WHERE (folderEffectiveActive is null or folderEffectiveActive = 1)  and (status is null or status = \"active\")  
			and tsk.dateCompleted is null and tsk.blocked=0 and (tsk.isDueSoon=1 or tsk.effectiveFlagged=1)
		ORDER BY tsk.effectiveFlagged DESC, tsk.effectivedateDue, proj.name")
	
	set {lstTasks, strLastProj} to {{}, ""}
	set {strDlm, text item delimiters} to {text item delimiters, pDelim}
	repeat with oLine in lstLines
		set {strProj, strTask, strFlagged, strDue} to text items of oLine
		
		if strProj ≠ strLastProj then
			set end of lstTasks to do shell script "echo " & quoted form of strProj & " | tr '[:lower:]' '[:upper:]'"
			set strLastProj to strProj
		end if
		
		if strFlagged ≠ "0" then
			set strFlag to "• [!!] "
		else
			set strFlag to "• "
		end if
		
		if strDue ≠ "" then set strDue to "[" & strDue & "] "
		
		set end of lstTasks to strFlag & strDue & strTask
	end repeat
	set text item delimiters to strDlm
	return lstTasks
end run