11 binmode STDOUT, ':utf8';
12 binmode STDERR, ':utf8';
16 # - detect moves between groups
17 # - make the sheet message more in-your-face
19 require '../include/config.pm';
45 sub get_oauth_bearer_token {
48 my $jwt = JSON::XS::encode_json({
49 "iss" => $config::jwt_key->{'client_email'},
50 "scope" => "https://www.googleapis.com/auth/spreadsheets",
51 "aud" => "https://www.googleapis.com/oauth2/v4/token",
55 my $jws_token = Crypt::JWT::encode_jwt(payload=>$jwt, alg=>'RS256', key=>\$config::jwt_key->{'private_key'});
56 my $response = $ua->post('https://www.googleapis.com/oauth2/v4/token', [
57 'grant_type' => 'urn:ietf:params:oauth:grant-type:jwt-bearer',
58 'assertion' => $jws_token ]);
59 return JSON::XS::decode_json($response->decoded_content)->{'access_token'};
63 my ($ua, $userid) = @_;
64 my $req = HTTP::Request->new('GET', 'https://slack.com/api/users.info?user=' . $userid, [
65 'Authorization' => 'Bearer ' . $config::slack_oauth_token
67 my $response = $ua->request($req);
68 die $response->status_line if !$response->is_success;
70 my $user_json = JSON::XS::decode_json($response->decoded_content);
71 die "Something went wrong: " . $response->decoded_content if (!defined($user_json) || !$user_json->{'ok'});
73 return $user_json->{'user'}{'real_name'};
76 sub get_spreadsheet_name {
78 my $name = $cell->{'userEnteredValue'}{'stringValue'};
79 return undef if (!defined($name));
80 return undef if ($name =~ /^G[1-4]\.[1-5]/);
85 $name =~ s/G\d\.\d?\??//;
93 my ($slack_name, $spreadsheet_name) = @_;
94 if (lc($slack_name) eq lc($spreadsheet_name)) {
98 my @ap = split /\s+/, $slack_name;
99 my @bp = split /\s+/, $spreadsheet_name;
100 if (scalar @ap >= 2 && scalar @bp >= 2 && lc($ap[0]) eq lc($bp[0])) {
101 # First name matches, try to match some surname
103 for my $ai (1..$#ap) {
104 for my $bi (1..$#bp) {
105 $found = 1 if (lc($ap[$ai]) eq lc($bp[$bi]));
109 skv_log("Fuzzy-matchet $slack_name -> $spreadsheet_name.");
117 sub format_cell_names_for_seen {
119 my @cells = map { chr(ord('A') + $_->[2]) . $_->[1] } @$seen;
120 return join(', ', @cells);
125 print STDERR "$msg\n";
129 sub serialize_skv_log_to_sheet {
134 userEnteredValue => { stringValue => join("\n", @log) }
137 fields => 'userEnteredValue.stringValue',
139 sheetId => $config::log_tab_id,
142 startColumnIndex => 0,
149 sub sheet_batch_update {
150 my ($ua, $token, @requests) = @_;
152 requests => \@requests
154 my $response = $ua->post(
155 'https://sheets.googleapis.com/v4/spreadsheets/' . $config::sheet_id . ':batchUpdate?key=' . $config::gsheets_api_key,
156 Content => JSON::XS::encode_json($update),
157 Content_type => 'application/json;charset=UTF-8',
158 Authorization => 'Bearer ' . $token
160 die $response->decoded_content if !$response->is_success;
163 sub get_spreadsheet_with_title {
164 my ($ua, $token, $wanted_sheet_title) = @_;
166 # See if we have any spreadsheets that match this title.
167 my $response = $ua->get('https://sheets.googleapis.com/v4/spreadsheets/' . $config::sheet_id . '?key=' . $config::gsheets_api_key . '&fields=sheets/properties',
168 Authorization => 'Bearer ' . $token
170 my $sheets_json = JSON::XS::decode_json($response->decoded_content);
171 my ($tab_name, $tab_id);
172 for my $sheet (@{$sheets_json->{'sheets'}}) {
173 my $title = $sheet->{'properties'}{'title'};
174 my $sheet_id = $sheet->{'properties'}{'sheetId'};
175 if ($title =~ /\Q$wanted_sheet_title\E/) {
176 # skv_log("Synkroniserer ($config::invitation_channel, $invitation_ts) mot arket ā$titleā (fane-ID $sheet_id).");
177 return ($title, $sheet_id);
180 return (undef, undef);
183 # Make a mapping of lowercase name -> list of [canonical name, row number, column number]
184 sub find_where_each_name_is {
188 my $rows = $json->{'sheets'}[0]{'data'}[0]{'rowData'};
190 for my $row (@$rows) {
192 for my $val (@{$row->{'values'}}) {
193 my $name = get_spreadsheet_name($val);
194 if (defined($name)) {
195 push @{$seen_names{lc $name}}, [$name, $rowno, $colno];
205 sub best_name_for_log {
206 my ($userid, $slack_userid_to_real_name, $slack_userid_to_slack_name) = @_;
207 if (exists($slack_userid_to_real_name->{$userid})) {
208 return $slack_userid_to_real_name->{$userid};
209 } elsif (exists($slack_userid_to_slack_name->{$userid})) {
210 return $slack_userid_to_slack_name->{$userid} . ' (fant ikke regneark-navn)';
212 # Should only happen if we didn't see the initial reaction_add, only reaction_remove.
213 # (TODO: Is the comment above true anymore, now that we use this from multiple contexts?)
214 return $userid . ' (fant ikke Slack-navn)';
218 # Add the reaction log. (This only takes into account the last change
219 # for each user; earlier ones are irrelevant and don't count. But it
220 # doesn't deduplicate across reactions. Meh.)
221 sub create_reaction_log {
222 my ($dbh, $invitation_ts, $slack_userid_to_real_name, $slack_userid_to_slack_name) = @_;
224 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');
225 $q->execute($config::invitation_channel, $invitation_ts);
226 my @recent_changes = ();
227 while (my $ref = $q->fetchrow_hashref) {
228 my $msg = $ref->{'event_ts'};
229 if ($ref->{'event_type'} eq 'reaction_added') {
234 if ($ref->{'reaction'} eq 'open_mouth') {
236 } elsif ($ref->{'reaction'} eq 'blue_heart') {
242 $msg .= best_name_for_log($ref->{'userid'}, $slack_userid_to_real_name, $slack_userid_to_slack_name);
243 push @recent_changes, { values => [{ userEnteredValue => { stringValue => $msg } }] };
245 while (scalar @recent_changes < 50) {
246 push @recent_changes, { values => [{ userEnteredValue => { stringValue => '' } }] };
248 return @recent_changes;
251 # Also applies the diff to the database (a bit ugly).
253 my ($dbh, $invitation_ts, $want_colors, $have_colors, $seen_names) = @_;
256 for my $real_name (keys %$want_colors) {
257 my $wc = $want_colors->{$real_name};
258 if (exists($have_colors->{$real_name})) {
259 if ($have_colors->{$real_name} eq $wc) {
263 skv_log("Markerer at $real_name har byttet treningssted.");
265 $real_name, { backgroundColor => $rgb{$wc} }
267 $dbh->do('UPDATE applied SET color=? WHERE channel=? AND ts=? AND name=?', undef,
268 $wc, $config::invitation_channel, $invitation_ts, $real_name);
270 skv_log("Markerer at $real_name skal pƄ trening.");
272 $real_name, { backgroundColor => $rgb{$wc} }
274 $dbh->do('INSERT INTO applied (channel, ts, name, color) VALUES (?, ?, ?, ?)', undef,
275 $config::invitation_channel, $invitation_ts, $real_name, $wc);
278 for my $real_name (keys %$have_colors) {
279 next if (exists($want_colors->{$real_name}));
280 if (!exists($seen_names->{lc $real_name})) {
281 # TODO: This can somehow come if we try to add someone who's not in the sheet, too?
282 skv_log("Ćnsket Ć„ fjerne at $real_name skulle pĆ„ trening, men de var ikke i regnearket lenger.");
284 skv_log("Fjerner at $real_name skal pƄ trening.");
286 $real_name, { backgroundColor => $rgb{white} }
288 $dbh->do('DELETE FROM applied WHERE channel=? AND ts=? AND name=?', undef,
289 $config::invitation_channel, $invitation_ts, $real_name);
295 sub possibly_nag_user {
296 my ($dbh, $ua, $userid) = @_;
298 # See if we've nagged this user before.
299 my $q = $dbh->prepare('SELECT * FROM users_nagged WHERE userid=?');
300 $q->execute($userid);
301 if (defined($q->fetchrow_hashref)) {
305 skv_log("Sender melding til $userid for Ć„ spĆørre om gruppe.");
306 my $response = $ua->post(
307 'https://slack.com/api/conversations.open',
308 Content => JSON::XS::encode_json({ users => [ $userid ] }),
309 Content_type => 'application/json;charset=UTF-8',
310 Authorization => 'Bearer ' . $config::slack_oauth_token
312 die $response->status_line if !$response->is_success;
314 my $im_json = JSON::XS::decode_json($response->decoded_content);
315 die "Something went wrong: " . $response->decoded_content if (!defined($im_json) || !$im_json->{'ok'});
316 my $channel_id = $im_json->{'channel'}{'id'};
318 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!";
320 $response = $ua->post(
321 'https://slack.com/api/chat.postMessage',
322 Content => JSON::XS::encode_json({ channel => $channel_id, text => $msg }),
323 Content_type => 'application/json;charset=UTF-8',
324 Authorization => 'Bearer ' . $config::slack_oauth_token
326 my $msg_json = JSON::XS::decode_json($response->decoded_content);
327 die "Something went wrong: " . $response->decoded_content if (!defined($msg_json) || !$msg_json->{'ok'});
329 # Mark that we've sent the message, so it won't happen again.
330 $dbh->do('INSERT INTO users_nagged (userid, last_nag) VALUES (?, CURRENT_TIMESTAMP)', undef, $userid);
334 my $start = [Time::HiRes::gettimeofday];
337 skv_log("Siste sync startet: " . POSIX::ctime(time));
339 # Initialize the handles we need for communication.
340 $dbh = DBI->connect("dbi:Pg:dbname=$config::dbname;host=127.0.0.1", $config::dbuser, $config::dbpass, {RaiseError => 1})
341 or die "Could not connect to Postgres: " . DBI->errstr;
342 my $ua = LWP::UserAgent->new('SKVidarLang/1.0');
343 my $token = get_oauth_bearer_token($ua);
345 # Find the newest message, and what it is linked to.
346 # TODO: Support more than one, and test better for errors here.
347 my $q = $dbh->prepare('select * from message_sheet_link where channel=? order by ts desc limit 1');
348 $q->execute($config::invitation_channel);
349 my $linkref = $q->fetchrow_hashref;
350 my $invitation_ts = $linkref->{'ts'};
351 my $wanted_sheet_title = $linkref->{'sheet_title'};
352 die "Could not get newest sheet title" if (!defined($wanted_sheet_title));
354 my ($tab_name, $tab_id) = get_spreadsheet_with_title($ua, $token, $wanted_sheet_title);
355 if (!defined($tab_name)) {
356 skv_log("Fant ikke noen fane med ā$wanted_sheet_titleā i navnet; kan ikke synkronisere.\n");
357 sheet_batch_update($ua, $token, [ serialize_skv_log_to_sheet() ]);
361 # Find everyone who are marked as attending on Slack (via reactions).
362 $q = $dbh->prepare('SELECT DISTINCT userid,reaction FROM current_reactions WHERE channel=? AND ts=? AND reaction IN (\'heart\', \'open_mouth\', \'blue_heart\')');
363 $q->execute($config::invitation_channel, $invitation_ts);
364 my @attending_userids = ();
367 while (my $ref = $q->fetchrow_hashref) {
368 my $userid = $ref->{'userid'};
369 push @attending_userids, $userid;
370 if ($ref->{'reaction'} eq 'blue_heart') {
371 if (exists($colors{$userid}) && $colors{$userid} eq 'yellow') {
372 $double{$userid} = 1;
374 $colors{$userid} = 'blue';
376 if (exists($colors{$userid}) && $colors{$userid} eq 'blue') {
377 $double{$userid} = 1;
379 $colors{$userid} = 'yellow';
383 # Remove double-attenders (we will log them as warnings further down).
384 @attending_userids = grep { !exists($double{$_}) } @attending_userids;
385 for my $userid (keys %double) {
386 delete $colors{$userid};
389 # Get the list of all people in the sheet (we're going to need them soon anyway).
390 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',
391 Authorization => 'Bearer ' . $token
393 my $main_sheet_json = JSON::XS::decode_json($response->decoded_content);
395 my %seen_names = find_where_each_name_is($main_sheet_json);
398 for my $name (sort keys %seen_names) {
399 my $seen = $seen_names{$name};
400 if (scalar @$seen >= 2) {
401 my $exemplar = $seen->[0][0];
402 skv_log("Duplikat: $exemplar (" . format_cell_names_for_seen($seen) . ")");
406 # Get our existing Slack->name mapping, from the sheets.
407 my %slack_userid_to_real_name = ();
408 my %slack_userid_to_slack_name = ();
409 my %slack_userid_to_row = ();
410 $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',
411 Authorization => 'Bearer ' . $token
413 my $mapping_sheet_json = JSON::XS::decode_json($response->decoded_content);
414 my $mapping_sheet_rows = $mapping_sheet_json->{'sheets'}[0]{'data'}[0]{'rowData'};
416 for my $row (@$mapping_sheet_rows) {
417 my $slack_id = $row->{'values'}[0]{'userEnteredValue'}{'stringValue'};
418 my $slack_name = $row->{'values'}[1]{'userEnteredValue'}{'stringValue'};
419 my $real_name = get_spreadsheet_name($row->{'values'}[2]); # TODO support more
420 $slack_userid_to_row{$slack_id} = $cur_row++;
421 next if (!defined($slack_name));
422 $slack_userid_to_slack_name{$slack_id} = $slack_name;
423 next if (!defined($real_name));
424 $slack_userid_to_real_name{$slack_id} = $real_name;
427 # See which ones we don't have a mapping for, and look them up in Slack.
428 # TODO: Use an append call instead of $cur_row?
429 my @slack_mapping_updates = ();
430 for my $userid (@attending_userids) {
431 next if (exists($slack_userid_to_real_name{$userid}));
433 # Make sure they have a row in the spreadsheet.
435 if (exists($slack_userid_to_row{$userid})) {
436 $write_row = $slack_userid_to_row{$userid};
438 $write_row = $cur_row++;
439 $slack_userid_to_row{$userid} = $write_row;
440 push @slack_mapping_updates, {
441 range => "Slack-mapping!A$write_row:A$write_row",
442 values => [ [ $userid ]]
446 # Fetch their Slack name if we don't already have it.
448 if (exists($slack_userid_to_slack_name{$userid})) {
449 $slack_name = $slack_userid_to_slack_name{$userid};
451 $slack_userid_to_slack_name{$userid} = $slack_name;
452 $slack_name = get_slack_name($ua, $userid);
453 push @slack_mapping_updates, {
454 range => "Slack-mapping!B$write_row:B$write_row",
455 values => [ [ $slack_name ]]
457 $slack_userid_to_slack_name{$userid} = $slack_name;
460 if (exists($seen_names{lc $slack_name})) {
461 # The name exists exactly, once or more, so it's a direct match and we ignore any fuzz.
462 $slack_userid_to_real_name{$userid} = $slack_name;
463 push @slack_mapping_updates, {
464 range => "Slack-mapping!C$write_row:C$write_row",
465 values => [ [ $slack_name ]]
468 # Do a search through all the available names in the sheet to find an obvious(ish) match.
470 my $main_sheet_rows = $main_sheet_json->{'sheets'}[0]{'data'}[0]{'rowData'};
471 for my $row (@$main_sheet_rows) {
472 for my $val (@{$row->{'values'}}) {
473 my $name = get_spreadsheet_name($val);
474 if (defined($name) && matches_name($slack_name, $name)) {
475 push @candidates, $name;
479 if ($#candidates == -1) {
480 skv_log("$slack_name ($userid) er pƄmeldt pƄ Slack, men fant ikke et regneark-navn for dem.");
481 possibly_nag_user($dbh, $ua, $userid);
482 } elsif ($#candidates == 0) {
483 my $name = $candidates[0];
484 $slack_userid_to_real_name{$userid} = $name;
485 push @slack_mapping_updates, {
486 range => "Slack-mapping!C$write_row:C$write_row",
487 values => [ [ $name ]]
490 skv_log("$slack_name ($userid) er pƄmeldt pƄ Slack, men hadde flere fuzzy-matcher; vet ikke hvilket regneark-navn som skal brukes.");
495 valueInputOption => 'USER_ENTERED',
496 data => \@slack_mapping_updates
498 $response = $ua->post(
499 'https://sheets.googleapis.com/v4/spreadsheets/' . $config::sheet_id . '/values:batchUpdate?key=' . $config::gsheets_api_key,
500 Content => JSON::XS::encode_json($update),
501 Content_type => 'application/json;charset=UTF-8',
502 Authorization => 'Bearer ' . $token
504 die $response->decoded_content if (!$response->is_success);
506 # Now that we have Slack names, we can log double-reacters.
507 for my $userid (keys %double) {
508 my $name = best_name_for_log($userid, \%slack_userid_to_real_name, \%slack_userid_to_slack_name);
509 skv_log("$name er pƄmeldt flere steder pƄ Slack; vet ikke hvilken som skal brukes.");
512 # Find the list of names to mark yellow.
513 my %want_colors = ();
514 my $main_sheet_rows = $main_sheet_json->{'sheets'}[0]{'data'}[0]{'rowData'};
515 for my $userid (@attending_userids) {
516 next if (!exists($slack_userid_to_real_name{$userid}));
517 my $slack_name = $slack_userid_to_slack_name{$userid};
518 my $real_name = $slack_userid_to_real_name{$userid};
520 # See if we can find them in the spreadsheet.
521 if (!exists($seen_names{lc $real_name})) {
522 # TODO: Perhaps move this logic further down, for consistency?
523 skv_log("$slack_name ($userid) er pƄmeldt pƄ Slack, og er mappet til $real_name, men var ikke i noen gruppe.");
525 my $seen = $seen_names{lc $real_name};
526 if (scalar @$seen >= 2) {
527 skv_log("$slack_name ($userid) er pƄmeldt pƄ Slack, men stƄr flere steder (se over); vet ikke hvilken celle som skal brukes.");
529 $want_colors{$seen->[0][0]} = $colors{$userid};
534 # Find the list of names we already marked yellow.
535 my %have_colors = ();
536 $dbh->{AutoCommit} = 0;
537 $dbh->do('SET TRANSACTION ISOLATION LEVEL SERIALIZABLE');
538 $q = $dbh->prepare('SELECT name,color FROM applied WHERE channel=? AND ts=?');
539 $q->execute($config::invitation_channel, $invitation_ts);
540 while (my $ref = $q->fetchrow_hashref) {
541 $have_colors{$ref->{'name'}} = $ref->{'color'};
544 my @diffs = find_diff($dbh, $invitation_ts, \%want_colors, \%have_colors, \%seen_names);
546 my @yellow_updates = ();
547 if (scalar @diffs > 0) {
548 # Now fill in the actual stuff.
549 for my $diff (@diffs) {
550 my $real_name = $diff->[0];
552 # See if we can find them in the spreadsheet.
553 my $seen = $seen_names{lc $real_name};
554 die if (!defined($seen) || scalar @$seen > 1);
555 my $rowno = $seen->[0][1];
556 my $colno = $seen->[0][2];
557 push @yellow_updates, {
561 userEnteredFormat => $diff->[1]
564 fields => 'userEnteredFormat.backgroundColor',
567 startRowIndex => $rowno,
568 endRowIndex => $rowno + 1,
569 startColumnIndex => $colno,
570 endColumnIndex => $colno + 1
577 my @recent_changes = create_reaction_log($dbh, $invitation_ts, \%slack_userid_to_real_name, \%slack_userid_to_slack_name);
578 push @yellow_updates, {
580 rows => \@recent_changes,
581 fields => 'userEnteredValue.stringValue',
583 sheetId => $config::log_tab_id,
585 endRowIndex => 4 + scalar @recent_changes,
586 startColumnIndex => 0,
592 # Push the final set of updates (including the log).
594 push @yellow_updates, serialize_skv_log_to_sheet();
595 sheet_batch_update($ua, $token, \@yellow_updates);
598 my $elapsed = Time::HiRes::tv_interval($start);
599 printf "Tok %.0f ms.\n", 1e3 * $elapsed;
602 if ($#ARGV >= 0 && $ARGV[0] eq '--daemon') {
603 # Start with a single, forced run.
604 unlink("/srv/skvidar-slack.sesse.net/marker");
608 if (!unlink("/srv/skvidar-slack.sesse.net/marker")) {
609 unless ($!{ENOENT}) {
610 warn "/srv/skvidar-slack.sesse.net/marker: $!";
619 warn "Died with: $@";