a script to analyze your Things app
#!/usr/bin/env ruby | |
# Usuage | |
# things 'thisweek' | |
# things 'lastweek' | |
# things 'thismonth' | |
require 'fileutils' | |
require 'date' | |
# please install the following 5 gems. | |
require 'sqlite3' | |
require 'active_record' | |
require 'hirb' | |
require 'hirb-unicode' | |
require 'rainbow' | |
require 'securerandom' | |
# copy sqlite db file to tmp | |
src = "/Users/i319092/Library/Containers/com.culturedcode.things/Data/Library/Application Support/Cultured Code/Things/ThingsLibrary.db" | |
dest = '/tmp' | |
FileUtils.cp_r src, dest, remove_destination: true | |
# connect database | |
ActiveRecord::Base.logger = nil | |
ActiveRecord::Base.establish_connection( | |
"adapter" => "sqlite3", | |
"database" => "/tmp/ThingsLibrary.db" | |
) | |
now = Date.today | |
# this week's report | |
now = Date.today | |
this_monday = now - (now.wday - 1) % 7 | |
this_sunday = this_monday + 6 | |
# last week's report | |
last_monday = this_monday - 7 | |
last_sunday = last_monday + 6 | |
# this_month | |
month_start = Date.today.beginning_of_month | |
month_end = Date.today.end_of_month | |
case ARGV[0] | |
when 'lastweek' | |
# last week's report | |
start_date = last_monday | |
end_date = last_sunday | |
when 'thismonth' | |
# this month's report | |
start_date = month_start | |
end_date = mon | |
else | |
# this week's report | |
start_date = this_monday | |
end_date = this_sunday | |
end | |
puts "--- | |
title: #{end_date} Weekly Review | |
layout: post | |
guid: #{SecureRandom.hex} | |
date: #{Time.now.to_s(:db)} | |
tags: | |
- | |
--- | |
h1. Table of Contents | |
1. Aggregation results for GTD Areas | |
2. Aggregation results for schedule tasks | |
3. Task list of every GTD Area | |
* 3.1 God | |
* 3.2 Myself | |
* 3.3 Family | |
* 3.4 Learning | |
* 3.5 Work | |
* 3.6 Church | |
* 3.7 Others | |
--- | |
h1. Review | |
Start: #{start_date} Monday | |
End: #{end_date} Sunday | |
Wrap-up: | |
" | |
# seperate models | |
setup = [] | |
setup << "CREATE TABLE `tags` as select Z_PK as id, ZTITLE as title from ZTHING where ZTYPE1=0" | |
setup << "CREATE TABLE `task_tags` as select Z_12NOTES as task_id, Z_14TAGS as tag_id from Z_12TAGS" | |
# convert dateime format | |
setup << "update ZTHING set | |
ZCREATIONDATE = datetime(ZCREATIONDATE, 'unixepoch', '+31 years', 'localtime','+1 day'), | |
ZSTOPPEDDATE = datetime(ZSTOPPEDDATE, 'unixepoch', '+31 years', 'localtime','+1 day'), | |
ZTICKLEDATE = datetime(ZTICKLEDATE, 'unixepoch', '+31 years', 'localtime','+1 day'), | |
ZDELEGATEDDATE = datetime(ZDELEGATEDDATE, 'unixepoch', '+31 years', 'localtime','+1 day'), | |
ZINSTANCECREATIONSTARTDATE = datetime(ZINSTANCECREATIONSTARTDATE, 'unixepoch', '+31 years', 'localtime','+1 day'), | |
ZNOTIFIEDDATE = datetime(ZNOTIFIEDDATE, 'unixepoch', '+31 years', 'localtime','+1 day'), | |
ZRECEIVEDDATE = datetime(ZRECEIVEDDATE, 'unixepoch', '+31 years', 'localtime','+1 day'), | |
ZUSEDDATE = datetime(ZUSEDDATE, 'unixepoch', '+31 years', 'localtime','+1 day'), | |
ZUSERMODIFICATIONDATE = datetime(ZUSERMODIFICATIONDATE, 'unixepoch', '+31 years', 'localtime','+1 day'), | |
ZDUEDATE = date(ZDUEDATE, 'unixepoch', '+31 years', 'localtime', '+1 day'), | |
ZSTARTDATE = date(ZSTARTDATE, 'unixepoch', '+31 years', 'localtime','+1 day'), | |
ZDUEDATEWITHOFFSET = date(ZDUEDATEWITHOFFSET, 'unixepoch', '+31 years', 'localtime','+1 day') | |
" | |
setup.each do |sql| | |
ActiveRecord::Base.connection.select_all(sql) | |
end | |
# 1. Tag summary | |
# | Tag | Success | Failed | Total | | |
# |---------- | --------- | -------- | -------| | |
# | Myself | 37 | 25 | 62 | | |
# | Learning | 20 | 8 | 29 | | |
# | Family | 17 | 2 | 19 | | |
# | God | 11 | 4 | 15 | | |
# | Church | 5 | 1 | 6 | | |
# | Work | 2 | 0 | 2 | | |
tag_summary = "select | |
tags.title as Tag, | |
count(case when tasks.ZSTATUS = '3' then 1 else null end) as Success, | |
count(case when tasks.ZSTATUS = '2' then 1 else null end) as Failed, | |
count(*) as Total, | |
(count(case when ZSTATUS = '3' then 1 else null end)*100/count(*)) as Score | |
from ZTHING as tasks | |
JOIN task_tags | |
ON tasks.Z_PK = task_tags.task_id | |
JOIN tags | |
on task_tags.tag_id = tags.id | |
where | |
tasks.ZSTARTDATE >= '#{start_date}' and | |
tasks.ZSTARTDATE <= '#{end_date}' | |
GROUP BY task_tags.tag_id | |
ORDER BY Success DESC" | |
puts "\n" * 3 | |
puts "## 1. Aggregation results for GTD Areas\n\n" | |
puts "Comments: " | |
results = ActiveRecord::Base.connection.select_all(tag_summary) | |
puts Hirb::Helpers::Table.render(results.rows, :change_fields => results.columns, :markdown => true).gsub("Yes", Rainbow("Yes").green).gsub("No", Rainbow("No").red) | |
# 2. Repeated tasks report | |
# | Routine | Success | Failed | Total | | |
# |----------------------------------------------------------------- | --------- | -------- | -------| | |
# | [Night] Love actions | prepare breakfast | prepare dinner | 7 | 0 | 7 | | |
# | Drink a cup of water | 7 | 0 | 7 | | |
# | Drink a cup of water | 7 | 0 | 7 | | |
# | Pray | 6 | 1 | 7 | | |
# | Anki: Review quiet | 6 | 1 | 7 | | |
# | [Night] Love words | 6 | 1 | 7 | | |
repeated_tasks = "select | |
ZTITLE as 'Routine', | |
count(case when ZSTATUS = '3' then 1 else null end) as Success, | |
count(case when ZSTATUS = '2' then 1 else null end) as Failed, | |
count(*) as Total, | |
(count(case when ZSTATUS = '3' then 1 else null end)*100/count(*)) as Score | |
from ZTHING as tasks | |
where | |
ZREPEATINGTEMPLATE is not null and | |
ZSTARTDATE >= '#{start_date}' and | |
ZSTARTDATE <= '#{end_date}' | |
GROUP BY ZREPEATINGTEMPLATE | |
ORDER BY Success DESC" | |
puts "\n" * 3 | |
puts "## 2. Aggregation results for schedule tasks\n\n" | |
puts "Comments: " | |
results = ActiveRecord::Base.connection.select_all(repeated_tasks) | |
puts Hirb::Helpers::Table.render(results.rows, :change_fields => results.columns, :markdown => true).gsub("Yes", Rainbow("Yes").green).gsub("No", Rainbow("No").red) | |
# 3. Every Tag's list | |
# | |
# God | |
# | Title | Start | End | Completed | Tag | | |
# |------------------------------------------ | ------------ | ------------ | ----------- | -----| | |
# | Pray | 2015-11-22 | 2015-11-22 | Yes | God | | |
# | Study Bible or Related book. | 2015-11-22 | 2015-11-22 | Yes | God | | |
# | Study Bible or Related book. | 2015-11-21 | 2015-11-21 | Yes | God | | |
# | Pray | 2015-11-21 | 2015-11-21 | Yes | God | | |
# | Study Bible or Related book. | 2015-11-20 | 2015-11-20 | Yes | God | | |
# | Pray | 2015-11-20 | 2015-11-20 | Yes | God | | |
# | Pray | 2015-11-19 | 2015-11-19 | Yes | God | | |
# | Collect others pray Items to my Evernote | 2015-11-19 | 2015-11-19 | No | God | | |
# | Study Bible or Related book. | 2015-11-19 | 2015-11-20 | Yes | God | | |
# | Study Bible or Related book. | 2015-11-18 | 2015-11-19 | No | God | | |
# | Pray | 2015-11-18 | 2015-11-19 | No | God | | |
# | Pray | 2015-11-17 | 2015-11-17 | Yes | God | | |
# | Study Bible or Related book. | 2015-11-17 | 2015-11-17 | Yes | God | | |
# | Study Bible or Related book. | 2015-11-16 | 2015-11-17 | No | God | | |
# | Pray | 2015-11-16 | 2015-11-16 | Yes | God | | |
puts "\n" * 3 + "## 3. Task list of every GTD Area" | |
tags = %w(God Myself Family Learning Work Church Others) | |
tags.each_with_index do |tag, index| | |
sql = "select | |
tasks.ZTITLE as Title, | |
tasks.ZSTARTDATE as Start, | |
date(tasks.ZSTOPPEDDATE) as End, | |
(case when tasks.ZSTATUS = '3' then 'Yes' when tasks.ZSTATUS = '2' then 'No' else null end) as Completed | |
from ZTHING as tasks | |
JOIN task_tags | |
ON tasks.Z_PK = task_tags.task_id | |
JOIN tags | |
on task_tags.tag_id = tags.id | |
where | |
tasks.ZSTARTDATE >= '#{start_date}' and | |
tasks.ZSTARTDATE <= '#{end_date}' and | |
tags.title = '#{tag}' | |
ORDER BY Completed ASC, Title ASC, ZSTARTDATE ASC" | |
puts "\n" * 3 + "### 3.#{index+1} " + tag + " Area" | |
if tag == "Work" | |
puts "\nComments: 工作内容保密" | |
else | |
puts "\nComments: \n\n" | |
end | |
results = ActiveRecord::Base.connection.select_all(sql) | |
puts Hirb::Helpers::Table.render(results.rows, :change_fields => results.columns, :markdown => true).gsub("Yes", Rainbow("Yes").green).gsub("No", Rainbow("No").red) | |
end | |
puts " | |
## Reference | |
1. 该报告由脚本自动生成,详细教程请参考 [GTD Review: 如何使用脚本自动生成 Things 的每周报表?](http://mednoter.com/how-to-dump-things-db-and-generate-weekly-report.html) | |
2. [Anki](http://ankisrs.net) 是间隔记忆工具,可以大幅缩短记忆知识的时间。 Anki is a program which makes remembering things easy. Because it's a lot more efficient than traditional study methods, you can either greatly decrease your time spent studying, or greatly increase the amount you learn. | |
3. *The Five Love Languages* 中文书名为 [《爱的五种语言》](http://book.douban.com/subject/1444900/),是符合圣经教导的婚姻指导书籍。里面将爱的付出划分为:肯定的言语、精心的时刻、接受礼物、服务的行动、身体的接触。 | |
4. Evernote 是笔记管理工具,用于梳理知识。有价值的信息消化完毕后制作成 Flashcard 纳入到间隔记忆工具,然后归档;没价值的信息直接删除。 | |
5. [GTD: What is area of responsibility?](http://www.challies.com/articles/how-to-get-things-done-define-your-areas-of-responsibility) | |
" | |
puts " | |
---- | |
h1. Plan | |
1. | |
2. | |
3. | |
4. | |
" |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
This comment has been minimized.
example of this script
Part 1 重复任务的完成度
Comments: 重复任务的完成度良好,上周的两个问题明显解决了。
本周的2个问题:
晚上的 quick exercise 持续几周不达标。
解决方法,每天晚上回家后立即做完。
每天记录自己的分析次数,连续几周不达标。
解决方法,这个任务优先级不高,再放几周看看。
Part 2 每个 Tag 下任务的完成度
Comments:
这周工作和学习效率不错,赞!
Part 3 每个 Tag 的流水
3.1 God Tag
3.2 Myself Tag
3.3 Family Tag
Comments:
这周没怎么夸媳妇,下周要继续鼓励她去健身,把肚子减下去。
3.4 Learning Tag
Comments:
这周搞明白了 Memcached 的内存分配机制,很有成就感。下周彻底搞清楚 HTTP conditional get。
3.5 Work Tag
内容保密,所以跳过
3.6 Church Tag
Comments:
不要总是做事情,定睛在神要我做的事情上。
3.7 Others Tag