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