forecaster/sync.rb at master · HappyFunCorp/forecaster · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
require './harvest'
require './sheets'

class Sync
  def initialize
    @harvest = Harvest.new
    @sheets = GoogleSheets.new

    load_config
    @sheet_id = @config["sheet"]

    if @sheet_id.nil?
      @sheet_id = @sheets.create_spreadsheet( "Roster", ["Dashboard", "People", "Projects", "UserAssignments", "TaskAssignments"] )
      @config["sheet"] = @sheet_id
      save_config
    end
  end

  def load_config
    @config = {}
    if File.exists? 'sheet.json'
      @config = JSON.parse( File.read( 'sheet.json' ) )
    end
  end

  def save_config
    File.open("sheet.json","w") do |f|
      f.write(JSON.pretty_generate(@config))
    end
  end

  def sync_everything
    sync_users
    sync_projects
    sync_user_assignments
    sync_task_assignments
    # TODO    sync_timesheets
    # TODO sync_forecast_projects
    # TODO sync_forecast allocations
    sync_dashboard
  end

  def sync_users
    users = @harvest.users

    letter_range = ("A".."Z").to_a[users.first.length]
    range = "People!A1:#{letter_range}"

    @sheets.update_values @sheet_id, range, users
    @config["harvest_sync_users"] = Time.now
    save_config
  end

  def sync_projects
    data= @harvest.projects

    letter_range = ("A".."Z").to_a[data.first.length]
    range = "Projects!A1:#{letter_range}"

    @sheets.update_values @sheet_id, range, data
    @config["harvest_sync_projects"] = Time.now
    save_config
  end

  def sync_user_assignments
    data = @harvest.user_assignments( { is_active: true } )
    letter_range = ("A".."Z").to_a[data.first.length]
    range = "UserAssignments!A1:#{letter_range}"

    @sheets.update_values @sheet_id, range, data
    @config["harvest_sync_assignments"] = Time.now
    save_config
  end

  def sync_task_assignments
    data = @harvest.task_assignments( { is_active: true } )
    letter_range = ("A".."Z").to_a[data.first.length]
    range = "TaskAssignments!A1:#{letter_range}"

    @sheets.update_values @sheet_id, range, data
    @config["harvest_sync_tasks"] = Time.now
    save_config
  end

  def sync_dashboard
    dashboard_values = []
    dashboard_values << [ 'Projects', "" ]
    dashboard_values << [ 'Active Projects', '=countif( Projects!D:D, "=TRUE" )' ]
    dashboard_values << [ 'Active NonBillable Projects', '=countifs( Projects!D:D, "=TRUE", Projects!E:E, "=FALSE" )', "This should similar to HFC Projects count" ]
    dashboard_values << [ 'HFC Projects', '=countifs( Projects!B:B, "=HappyFunCorp", Projects!D:D, "=TRUE" )' ]
    dashboard_values << [ 'Active No Start Date', '=countifs( Projects!D:D, "=TRUE", Projects!N:N, "<>" )' ]
    dashboard_values << [ 'Active No End Date', '=countifs( Projects!D:D, "=TRUE", Projects!O:O, "<>" )' ]

    dashboard_values << ['People', ""]

    dashboard_values << [ 'Active Harvest Users', '=countif( People!B:B, "=TRUE" )' ]
    dashboard_values << [ 'Contractors', '=countifs(People!B:B, "=TRUE", People!H:H, "=TRUE")']
    dashboard_values << [ 'Project Managers', '=countifs(People!B:B, "=TRUE", People!J:J, "=TRUE")', "Seems incorrect"]

    dashboard_values << ['Sync Times', ""]
    dashboard_values << [ 'Harvest Users', @config["harvest_sync_users"].to_s ]
    dashboard_values << [ 'Harvest Projects', @config["harvest_sync_projects"].to_s ]
    dashboard_values << [ 'Harvest User Assignments', @config["harvest_sync_assignments"].to_s ]
    dashboard_values << [ 'Harvest Task Assignments', @config["harvest_sync_tasks"].to_s ]

    @sheets.update_values @sheet_id, "Dashboard!A1:C", dashboard_values
  end
end

Sync.new.sync_everything