a script to analyze your Things app

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: 2015-12-13 Weekly Performance
layout: post
guid: #{SecureRandom.hex}
date: #{Time.now.to_s(:db)}
tags:
  - 
---

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. 每个 Tag 下任务的完成情况\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. 每日循环任务的完成情况\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. 每个 tag 的任务明细"

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
  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)"