Convert Things database into csv and json

snapThings.rb

#!/usr/bin/env ruby

# require 'sqlite3'
require 'shellwords'
require 'json'
require 'csv'
# require 'pry'

def tasks_to_csv(sqlite3_path, dest_path)
  query = 'select TASK.*, AREA.title as areaTitle, PROJECT.title as projectTitle, HEADING.title as headingTitle'
  # query = "select *,"
  # query += " strftime('%Y', TASK.stopDate) as stopDateX"
  query += ' from TMTask as TASK'
  query += ' LEFT OUTER JOIN TMTask PROJECT ON TASK.project = PROJECT.uuid'
  query += ' LEFT OUTER JOIN TMArea AREA ON TASK.area = AREA.uuid'
  query += ' LEFT OUTER JOIN TMTask HEADING ON TASK.actionGroup = HEADING.uuid'
  `sqlite3 -header -csv #{sqlite3_path.shellescape} "#{query}" > #{dest_path.shellescape}`
end

BASE_DIR = File.expand_path("~/Library/Containers/com.culturedcode.ThingsMac/Data/Library/Application\ Support/Cultured\ Code/Things/")

puts "translating all the sqlite3 databases into csv snapshots"
backups_glob_path = File.join(BASE_DIR, 'Backups/*.sqlite3')
dest_dir = File.join(BASE_DIR, 'csvs')
# generate from backup any days we're missing
Dir.glob(backups_glob_path).each do |backup_path|
  backup_path =~ /ThingsBackup (\d{4}\-\d{2}\-\d{2})/
  date = Regexp.last_match(1)
  dest = File.join(dest_dir, date + '.csv')
  `mkdir -p #{File.dirname(dest).shellescape}`
  next if File.exist?(dest)

  tasks_to_csv(backup_path, dest)
end

today_dest = File.join(dest_dir, Time.now.strftime("%Y-%m-%d") + '.csv')
tasks_to_csv(File.join(BASE_DIR, "Things.sqlite3"), today_dest) # unless File.exist?(today_dest)
# exit
# THEN: compute task_ids_scheduled and task_ids_completed

# # csv_filename = File.join(dest_dir, "2018-10-27.csv")

def read_tasks_csv(csv_filename)
  CSV.new(File.read(csv_filename), headers: true).to_a.map{|task|
    task.to_hash.map{|k, v|
      if(k.include?("Date"))
        # dynamically cast all Date fields to actual dates
        [k, Time.at(v.to_f)]
      else
        [k, v]
      end
    }.to_h
  }
end

def today(tasks)
  tasks.select do |t|
    t['trashed'] == '0' && # TASK.$ISNOTTRASHED
    t['status'] == '0' && # TASK.$ISOPEN
    t['type'] == '0' && # TASK.$ISTASK
    t['start'] == '1' && # TASK.$ISSTARTED
    t['startDate'].to_i != 0 && # TASK.startdate is NOT NULL
    true
  end
end

def completed(tasks)
  tasks.select do |t|
    t['trashed'] == '0' && # TASK.$ISNOTTRASHED
    t['type'] == '0' && # TASK.$ISTASK
    t['status'] == '3' && # TASK.$ISCOMPLETED
    true
  end
end

def finished_on(tasks, date)
  date = Date.parse(date) if date.class == String
  tasks.select do |t|
    t["stopDate"].to_date == date
  end
end

json_path = File.join(BASE_DIR, "things.json")
json = File.exists?(json_path) ? JSON.parse(File.read(json_path)) : {}
tasks_scheduled_completed = json["tasksScheduledCompleted"] || {}

csvs_glob_path = File.join(BASE_DIR, 'csvs/*.csv')
Dir.glob(csvs_glob_path).each{|csv_filename|
  puts "processing #{csv_filename}"
  tasks = read_tasks_csv(csv_filename)

  today_date = Date.parse(File.basename(csv_filename, ".csv"))

  today_tasks = today(tasks)
  completed_tasks = completed(tasks)
  completed_today = finished_on(completed_tasks, today_date)

  today_iso = today_date.strftime("%Y-%m-%d")
  tasks_scheduled_completed[today_iso] ||= {};

  tasks_scheduled_completed[today_iso]["taskIdsScheduled"] = (today_tasks.map{|t| t["uuid"]} + completed_today.map{|t| t["uuid"]}).uniq
}

puts "work with the most #recent data to define task_ids_completed"
tasks = read_tasks_csv(Dir.glob(csvs_glob_path).sort.last)
completed(tasks).each{|task|
  date_iso = task["stopDate"].strftime("%Y-%m-%d")
  tasks_scheduled_completed[date_iso] ||= {}
  (tasks_scheduled_completed[date_iso]["taskIdsCompleted"] ||= []) << task["uuid"]
}

scores = json["scores"] || {}
tasks_scheduled_completed.each{|date_iso, task_ids|
  task_ids_scheduled = task_ids["taskIdsScheduled"] || []
  task_ids_completed = task_ids["taskIdsCompleted"] || []

  monkey_score = task_ids_completed.length
  if task_ids_scheduled.length.to_f == 0
    # managers don't get points if they don't schedule anything
    manager_score = 0
  else
    manager_score = (task_ids_scheduled.length - (task_ids_scheduled - task_ids_completed).length).to_f / task_ids_scheduled.length.to_f
  end

  scores[date_iso] = {
    monkey: monkey_score,
    manager: manager_score,
  }
  # binding.pry
}

json["tasksScheduledCompleted"] = tasks_scheduled_completed
json["scores"] = scores

# sanity check
if json && json["scores"] && json["tasksScheduledCompleted"]
  File.open(json_path, 'w'){|f| f.puts JSON.pretty_generate(json)}
end

puts json_path
today_iso = Date.today.strftime("%Y-%m-%d")
tasks_scheduled_today = json["tasksScheduledCompleted"][today_iso]["taskIdsScheduled"]
puts "#{tasks_scheduled_today.length} tasks scheduled today"
p scores[today_iso]

# db_path = File.expand_path("~/Library/Containers/com.culturedcode.ThingsMac/Data/Library/Application\ Support/Cultured\ Code/Things/Things.sqlite3")
# db = SQLite3::Database.new(db_path)
#
# db.execute( "select * from TMTask" ) do |row|
#   p row
#   binding.pry
# end