things.rb

## things.rb
#!/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.
"