christiangenco · July 13, 2023 14:47 · Jul 13, 2023 · Jul 13, 2023
diff --git a/snapThings.rb b/snapThings.rb
@@ -0,0 +1,152 @@
+#!/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
No results found