10 binmode STDOUT, ':utf8';
11 binmode STDERR, ':utf8';
15 # - detect moves between groups
17 require '../include/config.pm';
43 sub get_oauth_bearer_token {
46 my $jwt = JSON::XS::encode_json({
47 "iss" => $config::jwt_key->{'client_email'},
48 "scope" => "https://www.googleapis.com/auth/spreadsheets",
49 "aud" => "https://www.googleapis.com/oauth2/v4/token",
53 my $jws_token = Crypt::JWT::encode_jwt(payload=>$jwt, alg=>'RS256', key=>\$config::jwt_key->{'private_key'});
54 my $response = $ua->post('https://www.googleapis.com/oauth2/v4/token', [
55 'grant_type' => 'urn:ietf:params:oauth:grant-type:jwt-bearer',
56 'assertion' => $jws_token ]);
57 return JSON::XS::decode_json($response->decoded_content)->{'access_token'};
61 my ($ua, $userid) = @_;
62 my $req = HTTP::Request->new('GET', 'https://slack.com/api/users.info?user=' . $userid, [
63 'Authorization' => 'Bearer ' . $config::slack_oauth_token
65 my $response = $ua->request($req);
66 die $response->status_line if !$response->is_success;
68 my $user_json = JSON::XS::decode_json($response->decoded_content);
69 die "Something went wrong: " . $response->decoded_content if (!defined($user_json) || !$user_json->{'ok'});
71 return $user_json->{'user'}{'real_name'};
74 sub get_spreadsheet_name {
76 my $name = $cell->{'userEnteredValue'}{'stringValue'};
77 return undef if (!defined($name));
78 return undef if ($name =~ /^G[1-4]\.[1-5]/);
83 $name =~ s/G\d\.\d?\??//;
91 my ($slack_name, $spreadsheet_name) = @_;
92 if (lc($slack_name) eq lc($spreadsheet_name)) {
96 my @ap = split /\s+/, $slack_name;
97 my @bp = split /\s+/, $spreadsheet_name;
98 if (scalar @ap >= 2 && scalar @bp >= 2 && lc($ap[0]) eq lc($bp[0])) {
99 # First name matches, try to match some surname
101 for my $ai (1..$#ap) {
102 for my $bi (1..$#bp) {
103 $found = 1 if (lc($ap[$ai]) eq lc($bp[$bi]));
107 skv_log("Fuzzy-matchet $slack_name -> $spreadsheet_name.");
115 sub format_cell_names_for_seen {
117 my @cells = map { chr(ord('A') + $_->[2]) . $_->[1] } @$seen;
118 return join(', ', @cells);
123 print STDERR "$msg\n";
127 sub serialize_skv_log_to_sheet {
132 userEnteredValue => { stringValue => join("\n", @log) }
135 fields => 'userEnteredValue.stringValue',
137 sheetId => $config::log_tab_id,
140 startColumnIndex => 0,
147 sub sheet_batch_update {
148 my ($ua, $token, @requests) = @_;
150 requests => \@requests
152 my $response = $ua->post(
153 'https://sheets.googleapis.com/v4/spreadsheets/' . $config::sheet_id . ':batchUpdate?key=' . $config::gsheets_api_key,
154 Content => JSON::XS::encode_json($update),
155 Content_type => 'application/json;charset=UTF-8',
156 Authorization => 'Bearer ' . $token
158 die $response->decoded_content if !$response->is_success;
161 sub get_spreadsheet_with_title {
162 my ($ua, $token, $wanted_sheet_title) = @_;
164 # See if we have any spreadsheets that match this title.
165 my $response = $ua->get('https://sheets.googleapis.com/v4/spreadsheets/' . $config::sheet_id . '?key=' . $config::gsheets_api_key . '&fields=sheets/properties',
166 Authorization => 'Bearer ' . $token
168 my $sheets_json = JSON::XS::decode_json($response->decoded_content);
169 my ($tab_name, $tab_id);
170 for my $sheet (@{$sheets_json->{'sheets'}}) {
171 my $title = $sheet->{'properties'}{'title'};
172 my $sheet_id = $sheet->{'properties'}{'sheetId'};
173 if ($title =~ /\Q$wanted_sheet_title\E/) {
174 # skv_log("Synkroniserer ($config::invitation_channel, $invitation_ts) mot arket ā$titleā (fane-ID $sheet_id).");
175 return ($title, $sheet_id);
178 return (undef, undef);
181 # Make a mapping of lowercase name -> list of [canonical name, row number, column number]
182 sub find_where_each_name_is {
186 my $rows = $json->{'sheets'}[0]{'data'}[0]{'rowData'};
188 for my $row (@$rows) {
190 for my $val (@{$row->{'values'}}) {
191 my $name = get_spreadsheet_name($val);
192 if (defined($name)) {
193 push @{$seen_names{lc $name}}, [$name, $rowno, $colno];
203 sub best_name_for_log {
204 my ($userid, $slack_userid_to_real_name, $slack_userid_to_slack_name) = @_;
205 if (exists($slack_userid_to_real_name->{$userid})) {
206 return $slack_userid_to_real_name->{$userid};
207 } elsif (exists($slack_userid_to_slack_name->{$userid})) {
208 return $slack_userid_to_slack_name->{$userid} . ' (fant ikke regneark-navn)';
210 # Should only happen if we didn't see the initial reaction_add, only reaction_remove.
211 # (TODO: Is the comment above true anymore, now that we use this from multiple contexts?)
212 return $userid . ' (fant ikke Slack-navn)';
216 # Add the reaction log. (This only takes into account the last change
217 # for each user; earlier ones are irrelevant and don't count. But it
218 # doesn't deduplicate across reactions. Meh.)
219 sub create_reaction_log {
220 my ($dbh, $invitation_ts, $slack_userid_to_real_name, $slack_userid_to_slack_name) = @_;
222 my $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\',\'blue_heart\') 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');
223 $q->execute($config::invitation_channel, $invitation_ts);
224 my @recent_changes = ();
225 while (my $ref = $q->fetchrow_hashref) {
226 my $msg = $ref->{'event_ts'};
227 if ($ref->{'event_type'} eq 'reaction_added') {
232 if ($ref->{'reaction'} eq 'open_mouth') {
234 } elsif ($ref->{'reaction'} eq 'blue_heart') {
240 $msg .= best_name_for_log($ref->{'userid'}, $slack_userid_to_real_name, $slack_userid_to_slack_name);
241 push @recent_changes, { values => [{ userEnteredValue => { stringValue => $msg } }] };
243 while (scalar @recent_changes < 50) {
244 push @recent_changes, { values => [{ userEnteredValue => { stringValue => '' } }] };
246 return @recent_changes;
249 # Also applies the diff to the database (a bit ugly).
251 my ($dbh, $invitation_ts, $want_colors, $have_colors, $seen_names) = @_;
254 for my $real_name (keys %$want_colors) {
255 my $wc = $want_colors->{$real_name};
256 if (exists($have_colors->{$real_name})) {
257 if ($have_colors->{$real_name} eq $wc) {
261 skv_log("Markerer at $real_name har byttet treningssted.");
263 $real_name, { backgroundColor => $rgb{$wc} }
265 $dbh->do('UPDATE applied SET color=? WHERE channel=? AND ts=? AND name=?', undef,
266 $wc, $config::invitation_channel, $invitation_ts, $real_name);
268 skv_log("Markerer at $real_name skal pƄ trening.");
270 $real_name, { backgroundColor => $rgb{$wc} }
272 $dbh->do('INSERT INTO applied (channel, ts, name, color) VALUES (?, ?, ?, ?)', undef,
273 $config::invitation_channel, $invitation_ts, $real_name, $wc);
276 for my $real_name (keys %$have_colors) {
277 next if (exists($want_colors->{$real_name}));
278 if (!exists($seen_names->{lc $real_name})) {
279 # TODO: This can somehow come if we try to add someone who's not in the sheet, too?
280 skv_log("Ćnsket Ć„ fjerne at $real_name skulle pĆ„ trening, men de var ikke i regnearket lenger.");
282 skv_log("Fjerner at $real_name skal pƄ trening.");
284 $real_name, { backgroundColor => $rgb{white} }
286 $dbh->do('DELETE FROM applied WHERE channel=? AND ts=? AND name=?', undef,
287 $config::invitation_channel, $invitation_ts, $real_name);
293 sub possibly_nag_user {
294 my ($dbh, $ua, $userid) = @_;
296 # See if we've nagged this user before.
297 my $q = $dbh->prepare('SELECT * FROM users_nagged WHERE userid=?');
298 $q->execute($userid);
299 if (defined($q->fetchrow_hashref)) {
303 skv_log("Sender melding til $userid for Ć„ spĆørre om gruppe.");
304 my $response = $ua->post(
305 'https://slack.com/api/conversations.open',
306 Content => JSON::XS::encode_json({ users => [ $userid ] }),
307 Content_type => 'application/json;charset=UTF-8',
308 Authorization => 'Bearer ' . $config::slack_oauth_token
310 die $response->status_line if !$response->is_success;
312 my $im_json = JSON::XS::decode_json($response->decoded_content);
313 die "Something went wrong: " . $response->decoded_content if (!defined($im_json) || !$im_json->{'ok'});
314 my $channel_id = $im_json->{'channel'}{'id'};
316 my $msg = "Hei! Du meldte deg akkurat pĆ„ trening, men vi klarer ikke Ć„ finne deg i en gruppe i regnearket. For at det skal vƦre enklere for trenerne, Ćønsker vi gjerne at du gĆ„r inn pĆ„ https://regneark.skvidar.run/ og skriver deg inn der med samme navn som du bruker pĆ„ Slack. Om du er usikker pĆ„ hvilken gruppe som passer for deg, ta gjerne kontakt med en trener. Velkommen pĆ„ trening og til klubben!";
318 $response = $ua->post(
319 'https://slack.com/api/chat.postMessage',
320 Content => JSON::XS::encode_json({ channel => $channel_id, text => $msg }),
321 Content_type => 'application/json;charset=UTF-8',
322 Authorization => 'Bearer ' . $config::slack_oauth_token
324 my $msg_json = JSON::XS::decode_json($response->decoded_content);
325 die "Something went wrong: " . $response->decoded_content if (!defined($msg_json) || !$msg_json->{'ok'});
327 # Mark that we've sent the message, so it won't happen again.
328 $dbh->do('INSERT INTO users_nagged (userid, last_nag) VALUES (?, CURRENT_TIMESTAMP)', undef, $userid);
333 skv_log("Siste sync startet: " . POSIX::ctime(time));
335 # Initialize the handles we need for communication.
336 $dbh = DBI->connect("dbi:Pg:dbname=$config::dbname;host=127.0.0.1", $config::dbuser, $config::dbpass, {RaiseError => 1})
337 or die "Could not connect to Postgres: " . DBI->errstr;
338 my $ua = LWP::UserAgent->new('SKVidarLang/1.0');
339 my $token = get_oauth_bearer_token($ua);
341 # Find the newest message, and what it is linked to.
342 # TODO: Support more than one, and test better for errors here.
343 my $q = $dbh->prepare('select * from message_sheet_link where channel=? order by ts desc limit 1');
344 $q->execute($config::invitation_channel);
345 my $linkref = $q->fetchrow_hashref;
346 my $invitation_ts = $linkref->{'ts'};
347 my $wanted_sheet_title = $linkref->{'sheet_title'};
348 die "Could not get newest sheet title" if (!defined($wanted_sheet_title));
350 my ($tab_name, $tab_id) = get_spreadsheet_with_title($ua, $token, $wanted_sheet_title);
351 if (!defined($tab_name)) {
352 skv_log("Fant ikke noen fane med ā$wanted_sheet_titleā i navnet; kan ikke synkronisere.\n");
353 sheet_batch_update($ua, $token, [ serialize_skv_log_to_sheet() ]);
357 # Find everyone who are marked as attending on Slack (via reactions).
358 $q = $dbh->prepare('SELECT DISTINCT userid,reaction FROM current_reactions WHERE channel=? AND ts=? AND reaction IN (\'heart\', \'open_mouth\', \'blue_heart\')');
359 $q->execute($config::invitation_channel, $invitation_ts);
360 my @attending_userids = ();
363 while (my $ref = $q->fetchrow_hashref) {
364 my $userid = $ref->{'userid'};
365 push @attending_userids, $userid;
366 if ($ref->{'reaction'} eq 'blue_heart') {
367 if (exists($colors{$userid}) && $colors{$userid} eq 'yellow') {
368 $double{$userid} = 1;
370 $colors{$userid} = 'blue';
372 if (exists($colors{$userid}) && $colors{$userid} eq 'blue') {
373 $double{$userid} = 1;
375 $colors{$userid} = 'yellow';
379 # Remove double-attenders (we will log them as warnings further down).
380 @attending_userids = grep { !exists($double{$_}) } @attending_userids;
381 for my $userid (keys %double) {
382 delete $colors{$userid};
385 # Get the list of all people in the sheet (we're going to need them soon anyway).
386 my $response = $ua->get('https://sheets.googleapis.com/v4/spreadsheets/' . $config::sheet_id . '?key=' . $config::gsheets_api_key . '&ranges=' . $tab_name . '!A4:Z5000&fields=sheets/data/rowData/values/userEnteredValue',
387 Authorization => 'Bearer ' . $token
389 my $main_sheet_json = JSON::XS::decode_json($response->decoded_content);
391 my %seen_names = find_where_each_name_is($main_sheet_json);
394 for my $name (sort keys %seen_names) {
395 my $seen = $seen_names{$name};
396 if (scalar @$seen >= 2) {
397 my $exemplar = $seen->[0][0];
398 skv_log("Duplikat: $exemplar (" . format_cell_names_for_seen($seen) . ")");
402 # Get our existing Slack->name mapping, from the sheets.
403 my %slack_userid_to_real_name = ();
404 my %slack_userid_to_slack_name = ();
405 my %slack_userid_to_row = ();
406 $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',
407 Authorization => 'Bearer ' . $token
409 my $mapping_sheet_json = JSON::XS::decode_json($response->decoded_content);
410 my $mapping_sheet_rows = $mapping_sheet_json->{'sheets'}[0]{'data'}[0]{'rowData'};
412 for my $row (@$mapping_sheet_rows) {
413 my $slack_id = $row->{'values'}[0]{'userEnteredValue'}{'stringValue'};
414 my $slack_name = $row->{'values'}[1]{'userEnteredValue'}{'stringValue'};
415 my $real_name = get_spreadsheet_name($row->{'values'}[2]); # TODO support more
416 $slack_userid_to_row{$slack_id} = $cur_row++;
417 next if (!defined($slack_name));
418 $slack_userid_to_slack_name{$slack_id} = $slack_name;
419 next if (!defined($real_name));
420 $slack_userid_to_real_name{$slack_id} = $real_name;
423 # See which ones we don't have a mapping for, and look them up in Slack.
424 # TODO: Use an append call instead of $cur_row?
425 my @slack_mapping_updates = ();
426 for my $userid (@attending_userids) {
427 next if (exists($slack_userid_to_real_name{$userid}));
429 # Make sure they have a row in the spreadsheet.
431 if (exists($slack_userid_to_row{$userid})) {
432 $write_row = $slack_userid_to_row{$userid};
434 $write_row = $cur_row++;
435 $slack_userid_to_row{$userid} = $write_row;
436 push @slack_mapping_updates, {
437 range => "Slack-mapping!A$write_row:A$write_row",
438 values => [ [ $userid ]]
442 # Fetch their Slack name if we don't already have it.
444 if (exists($slack_userid_to_slack_name{$userid})) {
445 $slack_name = $slack_userid_to_slack_name{$userid};
447 $slack_userid_to_slack_name{$userid} = $slack_name;
448 $slack_name = get_slack_name($ua, $userid);
449 push @slack_mapping_updates, {
450 range => "Slack-mapping!B$write_row:B$write_row",
451 values => [ [ $slack_name ]]
453 $slack_userid_to_slack_name{$userid} = $slack_name;
456 if (exists($seen_names{lc $slack_name})) {
457 # The name exists exactly, once or more, so it's a direct match and we ignore any fuzz.
458 $slack_userid_to_real_name{$userid} = $slack_name;
459 push @slack_mapping_updates, {
460 range => "Slack-mapping!C$write_row:C$write_row",
461 values => [ [ $slack_name ]]
464 # Do a search through all the available names in the sheet to find an obvious(ish) match.
466 my $main_sheet_rows = $main_sheet_json->{'sheets'}[0]{'data'}[0]{'rowData'};
467 for my $row (@$main_sheet_rows) {
468 for my $val (@{$row->{'values'}}) {
469 my $name = get_spreadsheet_name($val);
470 if (defined($name) && matches_name($slack_name, $name)) {
471 push @candidates, $name;
475 if ($#candidates == -1) {
476 skv_log("$slack_name ($userid) er pƄmeldt pƄ Slack, men fant ikke et regneark-navn for dem.");
477 possibly_nag_user($dbh, $ua, $userid);
478 } elsif ($#candidates == 0) {
479 my $name = $candidates[0];
480 $slack_userid_to_real_name{$userid} = $name;
481 push @slack_mapping_updates, {
482 range => "Slack-mapping!C$write_row:C$write_row",
483 values => [ [ $name ]]
486 skv_log("$slack_name ($userid) er pƄmeldt pƄ Slack, men hadde flere fuzzy-matcher; vet ikke hvilket regneark-navn som skal brukes.");
491 valueInputOption => 'USER_ENTERED',
492 data => \@slack_mapping_updates
494 $response = $ua->post(
495 'https://sheets.googleapis.com/v4/spreadsheets/' . $config::sheet_id . '/values:batchUpdate?key=' . $config::gsheets_api_key,
496 Content => JSON::XS::encode_json($update),
497 Content_type => 'application/json;charset=UTF-8',
498 Authorization => 'Bearer ' . $token
500 die $response->decoded_content if (!$response->is_success);
502 # Now that we have Slack names, we can log double-reacters.
503 for my $userid (keys %double) {
504 my $name = best_name_for_log($userid, \%slack_userid_to_real_name, \%slack_userid_to_slack_name);
505 skv_log("$name er pƄmeldt flere steder pƄ Slack; vet ikke hvilken som skal brukes.");
508 # Find the list of names to mark yellow.
509 my %want_colors = ();
510 my $main_sheet_rows = $main_sheet_json->{'sheets'}[0]{'data'}[0]{'rowData'};
511 for my $userid (@attending_userids) {
512 next if (!exists($slack_userid_to_real_name{$userid}));
513 my $slack_name = $slack_userid_to_slack_name{$userid};
514 my $real_name = $slack_userid_to_real_name{$userid};
516 # See if we can find them in the spreadsheet.
517 if (!exists($seen_names{lc $real_name})) {
518 # TODO: Perhaps move this logic further down, for consistency?
519 skv_log("$slack_name ($userid) er pƄmeldt pƄ Slack, og er mappet til $real_name, men var ikke i noen gruppe.");
521 my $seen = $seen_names{lc $real_name};
522 if (scalar @$seen >= 2) {
523 skv_log("$slack_name ($userid) er pƄmeldt pƄ Slack, men stƄr flere steder (se over); vet ikke hvilken celle som skal brukes.");
525 $want_colors{$seen->[0][0]} = $colors{$userid};
530 # Find the list of names we already marked yellow.
531 my %have_colors = ();
532 $dbh->{AutoCommit} = 0;
533 $dbh->do('SET TRANSACTION ISOLATION LEVEL SERIALIZABLE');
534 $q = $dbh->prepare('SELECT name,color FROM applied WHERE channel=? AND ts=?');
535 $q->execute($config::invitation_channel, $invitation_ts);
536 while (my $ref = $q->fetchrow_hashref) {
537 $have_colors{$ref->{'name'}} = $ref->{'color'};
540 my @diffs = find_diff($dbh, $invitation_ts, \%want_colors, \%have_colors, \%seen_names);
542 my @yellow_updates = ();
543 if (scalar @diffs > 0) {
544 # Now fill in the actual stuff.
545 for my $diff (@diffs) {
546 my $real_name = $diff->[0];
548 # See if we can find them in the spreadsheet.
549 my $seen = $seen_names{lc $real_name};
550 die if (!defined($seen) || scalar @$seen > 1);
551 my $rowno = $seen->[0][1];
552 my $colno = $seen->[0][2];
553 push @yellow_updates, {
557 userEnteredFormat => $diff->[1]
560 fields => 'userEnteredFormat.backgroundColor',
563 startRowIndex => $rowno,
564 endRowIndex => $rowno + 1,
565 startColumnIndex => $colno,
566 endColumnIndex => $colno + 1
573 my @recent_changes = create_reaction_log($dbh, $invitation_ts, \%slack_userid_to_real_name, \%slack_userid_to_slack_name);
574 push @yellow_updates, {
576 rows => \@recent_changes,
577 fields => 'userEnteredValue.stringValue',
579 sheetId => $config::log_tab_id,
581 endRowIndex => 4 + scalar @recent_changes,
582 startColumnIndex => 0,
588 # Push the final set of updates (including the log).
590 push @yellow_updates, serialize_skv_log_to_sheet();
591 sheet_batch_update($ua, $token, \@yellow_updates);
595 if ($#ARGV >= 0 && $ARGV[0] eq '--daemon') {
597 if (!unlink("/srv/skvidar-slack.sesse.net/marker")) {
598 unless ($!{ENOENT}) {
599 warn "/srv/skvidar-slack.sesse.net/marker: $!";
608 warn "Died with: $@";