10 binmode STDOUT, ':utf8';
11 binmode STDERR, ':utf8';
15 # - support non-fixed sheets
16 # - detect moves between groups
18 require '../include/config.pm';
22 sub get_oauth_bearer_token {
25 my $jwt = JSON::XS::encode_json({
26 "iss" => $config::jwt_key->{'client_email'},
27 "scope" => "https://www.googleapis.com/auth/spreadsheets",
28 "aud" => "https://www.googleapis.com/oauth2/v4/token",
32 my $jws_token = Crypt::JWT::encode_jwt(payload=>$jwt, alg=>'RS256', key=>\$config::jwt_key->{'private_key'});
33 my $response = $ua->post('https://www.googleapis.com/oauth2/v4/token', [
34 'grant_type' => 'urn:ietf:params:oauth:grant-type:jwt-bearer',
35 'assertion' => $jws_token ]);
36 return JSON::XS::decode_json($response->decoded_content)->{'access_token'};
40 my ($ua, $userid) = @_;
41 my $req = HTTP::Request->new('GET', 'https://slack.com/api/users.info?user=' . $userid, [
42 'Authorization' => 'Bearer ' . $config::oauth_token
44 my $response = $ua->request($req);
45 die $response->status_line if !$response->is_success;
47 my $user_json = JSON::XS::decode_json($response->decoded_content);
48 die "Something went wrong: " . $response->decoded_content if (!defined($user_json) || !$user_json->{'ok'});
50 return $user_json->{'user'}{'real_name'};
53 sub get_spreadsheet_name {
55 my $name = $cell->{'userEnteredValue'}{'stringValue'};
56 return undef if (!defined($name));
57 return undef if ($name =~ /^G[1-4]\.[1-4]/);
62 $name =~ s/G\d\.\d?\??//;
70 my ($slack_name, $spreadsheet_name) = @_;
71 if (lc($slack_name) eq lc($spreadsheet_name)) {
75 my @ap = split /\s+/, $slack_name;
76 my @bp = split /\s+/, $spreadsheet_name;
77 if (scalar @ap >= 2 && scalar @bp >= 2 && lc($ap[0]) eq lc($bp[0])) {
78 # First name matches, try to match some surname
80 for my $ai (1..$#ap) {
81 for my $bi (1..$#bp) {
82 $found = 1 if (lc($ap[$ai]) eq lc($bp[$bi]));
86 skv_log("Fuzzy-matchet $slack_name -> $spreadsheet_name.");
94 sub format_cell_names_for_seen {
96 my @cells = map { chr(ord('A') + $_->[2]) . $_->[1] } @$seen;
97 return join(', ', @cells);
102 print STDERR "$msg\n";
106 skv_log("Siste sync startet: " . POSIX::ctime(time));
108 my $dbh = DBI->connect("dbi:Pg:dbname=$config::dbname;host=127.0.0.1", $config::dbuser, $config::dbpass, {RaiseError => 1})
109 or die "Could not connect to Postgres: " . DBI->errstr;
111 my $ua = LWP::UserAgent->new('SKVidarLang/1.0');
112 my $token = get_oauth_bearer_token($ua);
114 # Find everyone who are marked as attending on Slack (via reactions).
115 my $q = $dbh->prepare('SELECT DISTINCT userid FROM current_reactions WHERE channel=? AND ts=? AND reaction IN (\'heart\', \'open_mouth\')');
116 $q->execute($config::invitation_channel, $config::invitation_ts);
117 my @attending_userids = ();
118 while (my $ref = $q->fetchrow_hashref) {
119 push @attending_userids, $ref->{'userid'};
122 # Get the list of all people in the sheet (we're going to need them soon anyway).
123 my $response = $ua->get('https://sheets.googleapis.com/v4/spreadsheets/' . $config::sheet_id . '?key=' . $config::gsheets_api_key . '&ranges=' . $config::tab_name . '!A9:Z5000&fields=sheets/data/rowData/values/userEnteredValue',
124 Authorization => 'Bearer ' . $token
126 my $main_sheet_json = JSON::XS::decode_json($response->decoded_content);
128 # Duplicate detection
131 my $main_sheet_rows = $main_sheet_json->{'sheets'}[0]{'data'}[0]{'rowData'};
133 for my $row (@$main_sheet_rows) {
135 for my $val (@{$row->{'values'}}) {
136 my $name = get_spreadsheet_name($val);
137 if (defined($name)) {
138 push @{$seen_names{lc $name}}, [$name, $rowno, $colno];
144 for my $name (sort keys %seen_names) {
145 my $seen = $seen_names{$name};
146 if (scalar @$seen >= 2) {
147 my $exemplar = $seen->[0][0];
148 skv_log("Duplikat: $exemplar (" . format_cell_names_for_seen($seen) . ")");
153 # Get our existing Slack->name mapping, from the sheets.
154 my %slack_userid_to_real_name = ();
155 my %slack_userid_to_slack_name = ();
156 my %slack_userid_to_row = ();
157 my %real_name_to_slack_userid = ();
158 $response = $ua->get('https://sheets.googleapis.com/v4/spreadsheets/' . $config::sheet_id . '?key=' . $config::gsheets_api_key . '&ranges=Slack-mapping!A5:C5000&fields=sheets/data/rowData/values/userEnteredValue',
159 Authorization => 'Bearer ' . $token
161 my $mapping_sheet_json = JSON::XS::decode_json($response->decoded_content);
162 my $mapping_sheet_rows = $mapping_sheet_json->{'sheets'}[0]{'data'}[0]{'rowData'};
164 for my $row (@$mapping_sheet_rows) {
165 my $slack_id = $row->{'values'}[0]{'userEnteredValue'}{'stringValue'};
166 my $slack_name = $row->{'values'}[1]{'userEnteredValue'}{'stringValue'};
167 my $real_name = get_spreadsheet_name($row->{'values'}[2]); # TODO support more
168 $slack_userid_to_row{$slack_id} = $cur_row++;
169 next if (!defined($slack_name));
170 $slack_userid_to_slack_name{$slack_id} = $slack_name;
171 next if (!defined($real_name));
172 $slack_userid_to_real_name{$slack_id} = $real_name;
173 $real_name_to_slack_userid{$real_name} = $slack_id;
176 # See which ones we don't have a mapping for, and look them up in Slack.
177 # TODO: Use an append call instead of $cur_row?
178 my @slack_mapping_updates = ();
179 for my $userid (@attending_userids) {
180 next if (exists($slack_userid_to_real_name{$userid}));
182 # Make sure they have a row in the spreadsheet.
184 if (exists($slack_userid_to_row{$userid})) {
185 $write_row = $slack_userid_to_row{$userid};
187 $write_row = $cur_row++;
188 $slack_userid_to_row{$userid} = $write_row;
189 push @slack_mapping_updates, {
190 range => "Slack-mapping!A$write_row:A$write_row",
191 values => [ [ $userid ]]
195 # Fetch their Slack name if we don't already have it.
197 if (exists($slack_userid_to_slack_name{$userid})) {
198 $slack_name = $slack_userid_to_slack_name{$userid};
200 $slack_userid_to_slack_name{$userid} = $slack_name;
201 $slack_name = get_slack_name($ua, $userid);
202 push @slack_mapping_updates, {
203 range => "Slack-mapping!B$write_row:B$write_row",
204 values => [ [ $slack_name ]]
206 $slack_userid_to_slack_name{$userid} = $slack_name;
209 if (exists($seen_names{lc $slack_name})) {
210 # The name exists exactly, once or more, so it's a direct match and we ignore any fuzz.
211 $slack_userid_to_real_name{$userid} = $slack_name;
212 push @slack_mapping_updates, {
213 range => "Slack-mapping!C$write_row:C$write_row",
214 values => [ [ $slack_name ]]
217 # Do a search through all the available names in the sheet to find an obvious(ish) match.
219 my $main_sheet_rows = $main_sheet_json->{'sheets'}[0]{'data'}[0]{'rowData'};
220 for my $row (@$main_sheet_rows) {
221 for my $val (@{$row->{'values'}}) {
222 my $name = get_spreadsheet_name($val);
223 if (defined($name) && matches_name($slack_name, $name)) {
224 push @candidates, $name;
228 if ($#candidates == -1) {
229 skv_log("$slack_name ($userid) er pƄmeldt pƄ Slack, men fant ikke et regneark-navn for dem.");
230 } elsif ($#candidates == 0) {
231 my $name = $candidates[0];
232 $slack_userid_to_real_name{$userid} = $name;
233 push @slack_mapping_updates, {
234 range => "Slack-mapping!C$write_row:C$write_row",
235 values => [ [ $name ]]
238 skv_log("$slack_name ($userid) er pƄmeldt pƄ Slack, men hadde flere fuzzy-matcher; vet ikke hvilket regneark-navn som skal brukes.");
243 valueInputOption => 'USER_ENTERED',
244 data => \@slack_mapping_updates
246 $response = $ua->post(
247 'https://sheets.googleapis.com/v4/spreadsheets/' . $config::sheet_id . '/values:batchUpdate?key=' . $config::gsheets_api_key,
248 Content => JSON::XS::encode_json($update),
249 Content_type => 'application/json;charset=UTF-8',
250 Authorization => 'Bearer ' . $token
252 die $response->decoded_content if (!$response->is_success);
254 # Find the list of names to mark yellow.
256 my $main_sheet_rows = $main_sheet_json->{'sheets'}[0]{'data'}[0]{'rowData'};
257 for my $userid (@attending_userids) {
258 next if (!exists($slack_userid_to_real_name{$userid}));
259 my $slack_name = $slack_userid_to_slack_name{$userid};
260 my $real_name = $slack_userid_to_real_name{$userid};
262 # See if we can find them in the spreadsheet.
263 if (!exists($seen_names{lc $real_name})) {
264 skv_log("$slack_name ($userid) er pƄmeldt pƄ Slack, og er mappet til $real_name, men var ikke i noen gruppe.");
266 my $seen = $seen_names{lc $real_name};
267 if (scalar @$seen >= 2) {
268 skv_log("$slack_name ($userid) er pƄmeldt pƄ Slack, men stƄr flere steder (se over); vet ikke hvilken celle som skal brukes.");
270 $want_names{$seen->[0][0]} = 1;
275 # Find the list of names we already marked yellow.
277 $dbh->{AutoCommit} = 0;
278 $dbh->do('SET TRANSACTION ISOLATION LEVEL SERIALIZABLE');
279 $q = $dbh->prepare('SELECT name FROM applied WHERE channel=? AND ts=?');
280 $q->execute($config::invitation_channel, $config::invitation_ts);
281 while (my $ref = $q->fetchrow_hashref) {
282 $have_names{$ref->{'name'}} = 1;
285 # Find the diff between the two.
287 for my $real_name (keys %want_names) {
288 next if (exists($have_names{$real_name}));
289 skv_log("Markerer at $real_name skal pƄ trening.");
301 $dbh->do('INSERT INTO applied (channel, ts, name) VALUES (?, ?, ?)', undef,
302 $config::invitation_channel, $config::invitation_ts, $real_name);
304 for my $real_name (keys %have_names) {
305 next if (exists($want_names{$real_name}));
306 skv_log("Fjerner at $real_name skal pƄ trening.");
318 $dbh->do('DELETE FROM applied WHERE channel=? AND ts=? AND name=?', undef,
319 $config::invitation_channel, $config::invitation_ts, $real_name);
322 my @yellow_updates = ();
323 if (scalar @diffs > 0) {
324 # Now fill in the actual stuff.
325 for my $diff (@diffs) {
326 my $real_name = $diff->[0];
328 # See if we can find them in the spreadsheet.
329 # FIXME use seen_names
330 my $rowno = 8; # zero-indexed
332 for my $row (@$main_sheet_rows) {
334 for my $val (@{$row->{'values'}}) {
335 my $name = get_spreadsheet_name($val);
336 if (defined($name) && $real_name eq $name) {
337 push @yellow_updates, {
341 userEnteredFormat => $diff->[1]
344 fields => 'userEnteredFormat.backgroundColor',
346 sheetId => $config::tab_id,
347 startRowIndex => $rowno,
348 endRowIndex => $rowno + 1,
349 startColumnIndex => $colno,
350 endColumnIndex => $colno + 1
363 skv_log("Ćnsket Ć„ fjerne at $real_name skulle pĆ„ trening, men de var ikke i regnearket lenger.");
371 push @yellow_updates, {
375 userEnteredValue => { stringValue => join("\n", @log) }
378 fields => 'userEnteredValue.stringValue',
380 sheetId => $config::log_tab_id,
383 startColumnIndex => 0,
389 # Add the reaction log. (This only takes into account the last change
390 # for each user; earlier ones are irrelevant and don't count. But it
391 # doesn't deduplicate across reactions. Meh.)
392 $q = $dbh->prepare('select userid,event_type,reaction,to_char(event_ts,\'YYYY-mm-dd HH24:MI\') as event_ts from ( select distinct on (channel,ts,userid,reaction) userid,event_type,reaction,timestamptz \'1970-01-01 utc\' + event_ts::float * interval \'1 second\' as event_ts from reaction_log where channel=? and ts=? and reaction in (\'heart\',\'open_mouth\') order by channel,ts,userid,reaction,event_ts desc ) t1 where event_ts > current_timestamp - interval \'8 hours\' order by event_ts desc limit 50');
393 $q->execute($config::invitation_channel, $config::invitation_ts);
394 my @recent_changes = ();
395 while (my $ref = $q->fetchrow_hashref) {
396 my $msg = $ref->{'event_ts'};
397 if ($ref->{'event_type'} eq 'reaction_added') {
402 if ($ref->{'reaction'} eq 'open_mouth') {
408 $msg .= $slack_userid_to_real_name{$ref->{'userid'}} // ($slack_userid_to_slack_name{$ref->{'userid'}} . ' (fant ikke regneark-navn)');
409 push @recent_changes, { values => [{ userEnteredValue => { stringValue => $msg } }] };
411 while (scalar @recent_changes < 50) {
412 push @recent_changes, { values => [{ userEnteredValue => { stringValue => '' } }] };
414 push @yellow_updates, {
416 rows => \@recent_changes,
417 fields => 'userEnteredValue.stringValue',
419 sheetId => $config::log_tab_id,
421 endRowIndex => 4 + scalar @recent_changes,
422 startColumnIndex => 0,
428 # Push the final set of updates (including the log).
430 requests => \@yellow_updates
432 $response = $ua->post(
433 'https://sheets.googleapis.com/v4/spreadsheets/' . $config::sheet_id . ':batchUpdate?key=' . $config::gsheets_api_key,
434 Content => JSON::XS::encode_json($update),
435 Content_type => 'application/json;charset=UTF-8',
436 Authorization => 'Bearer ' . $token
438 die $response->decoded_content if !$response->is_success;