11 binmode STDOUT, ':utf8';
12 binmode STDERR, ':utf8';
16 # - detect moves between groups
17 # - make the sheet message more in-your-face (ephemeral message)
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.");
283 } elsif (scalar @{$seen_names->{lc $real_name}} > 1) {
286 skv_log("Fjerner at $real_name skal pƄ trening.");
288 $real_name, { backgroundColor => $rgb{white} }
290 $dbh->do('DELETE FROM applied WHERE channel=? AND ts=? AND name=?', undef,
291 $config::invitation_channel, $invitation_ts, $real_name);
297 sub possibly_nag_user {
298 my ($dbh, $ua, $userid) = @_;
300 # See if we've nagged this user before.
301 my $q = $dbh->prepare('SELECT * FROM users_nagged WHERE userid=?');
302 $q->execute($userid);
303 if (defined($q->fetchrow_hashref)) {
307 skv_log("Sender melding til $userid for Ć„ spĆørre om gruppe.");
308 my $response = $ua->post(
309 'https://slack.com/api/conversations.open',
310 Content => JSON::XS::encode_json({ users => [ $userid ] }),
311 Content_type => 'application/json;charset=UTF-8',
312 Authorization => 'Bearer ' . $config::slack_oauth_token
314 die $response->status_line if !$response->is_success;
316 my $im_json = JSON::XS::decode_json($response->decoded_content);
317 die "Something went wrong: " . $response->decoded_content if (!defined($im_json) || !$im_json->{'ok'});
318 my $channel_id = $im_json->{'channel'}{'id'};
320 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!";
322 $response = $ua->post(
323 'https://slack.com/api/chat.postMessage',
324 Content => JSON::XS::encode_json({ channel => $channel_id, text => $msg }),
325 Content_type => 'application/json;charset=UTF-8',
326 Authorization => 'Bearer ' . $config::slack_oauth_token
328 my $msg_json = JSON::XS::decode_json($response->decoded_content);
329 die "Something went wrong: " . $response->decoded_content if (!defined($msg_json) || !$msg_json->{'ok'});
331 # Mark that we've sent the message, so it won't happen again.
332 $dbh->do('INSERT INTO users_nagged (userid, last_nag) VALUES (?, CURRENT_TIMESTAMP)', undef, $userid);
336 my $start = [Time::HiRes::gettimeofday];
339 skv_log("Siste sync startet: " . POSIX::ctime(time));
341 # Initialize the handles we need for communication.
342 $dbh = DBI->connect("dbi:Pg:dbname=$config::dbname;host=127.0.0.1", $config::dbuser, $config::dbpass, {RaiseError => 1})
343 or die "Could not connect to Postgres: " . DBI->errstr;
344 my $ua = LWP::UserAgent->new('SKVidarLang/1.0');
345 my $token = get_oauth_bearer_token($ua);
347 # Find the newest message, and what it is linked to.
348 # TODO: Support more than one, and test better for errors here.
349 my $q = $dbh->prepare('select * from message_sheet_link where channel=? order by ts desc limit 1');
350 $q->execute($config::invitation_channel);
351 my $linkref = $q->fetchrow_hashref;
352 my $invitation_ts = $linkref->{'ts'};
353 my $wanted_sheet_title = $linkref->{'sheet_title'};
354 die "Could not get newest sheet title" if (!defined($wanted_sheet_title));
356 my ($tab_name, $tab_id) = get_spreadsheet_with_title($ua, $token, $wanted_sheet_title);
357 if (!defined($tab_name)) {
358 skv_log("Fant ikke noen fane med Ā«$wanted_sheet_titleĀ» i navnet; kan ikke synkronisere.\n");
359 sheet_batch_update($ua, $token, [ serialize_skv_log_to_sheet() ]);
363 # Find everyone who are marked as attending on Slack (via reactions).
364 $q = $dbh->prepare('SELECT DISTINCT userid,reaction FROM current_reactions WHERE channel=? AND ts=? AND reaction IN (\'heart\', \'open_mouth\', \'blue_heart\')');
365 $q->execute($config::invitation_channel, $invitation_ts);
366 my @attending_userids = ();
369 while (my $ref = $q->fetchrow_hashref) {
370 my $userid = $ref->{'userid'};
371 push @attending_userids, $userid;
372 if ($ref->{'reaction'} eq 'blue_heart') {
373 if (exists($colors{$userid}) && $colors{$userid} eq 'yellow') {
374 $double{$userid} = 1;
376 $colors{$userid} = 'blue';
378 if (exists($colors{$userid}) && $colors{$userid} eq 'blue') {
379 $double{$userid} = 1;
381 $colors{$userid} = 'yellow';
385 # Remove double-attenders (we will log them as warnings further down).
386 @attending_userids = grep { !exists($double{$_}) } @attending_userids;
387 for my $userid (keys %double) {
388 delete $colors{$userid};
391 # Get the list of all people in the sheet (we're going to need them soon anyway).
392 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',
393 Authorization => 'Bearer ' . $token
395 my $main_sheet_json = JSON::XS::decode_json($response->decoded_content);
397 my %seen_names = find_where_each_name_is($main_sheet_json);
400 for my $name (sort keys %seen_names) {
401 my $seen = $seen_names{$name};
402 if (scalar @$seen >= 2) {
403 my $exemplar = $seen->[0][0];
404 skv_log("Duplikat: $exemplar (" . format_cell_names_for_seen($seen) . ")");
408 # Get our existing Slack->name mapping, from the sheets.
409 my %slack_userid_to_real_name = ();
410 my %slack_userid_to_slack_name = ();
411 my %slack_userid_to_row = ();
412 $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',
413 Authorization => 'Bearer ' . $token
415 my $mapping_sheet_json = JSON::XS::decode_json($response->decoded_content);
416 my $mapping_sheet_rows = $mapping_sheet_json->{'sheets'}[0]{'data'}[0]{'rowData'};
418 for my $row (@$mapping_sheet_rows) {
419 my $slack_id = $row->{'values'}[0]{'userEnteredValue'}{'stringValue'};
420 my $slack_name = $row->{'values'}[1]{'userEnteredValue'}{'stringValue'};
421 my $real_name = get_spreadsheet_name($row->{'values'}[2]); # TODO support more
422 $slack_userid_to_row{$slack_id} = $cur_row++;
423 next if (!defined($slack_name));
424 $slack_userid_to_slack_name{$slack_id} = $slack_name;
425 next if (!defined($real_name));
426 $slack_userid_to_real_name{$slack_id} = $real_name;
429 # See which ones we don't have a mapping for, and look them up in Slack.
430 # TODO: Use an append call instead of $cur_row?
431 my @slack_mapping_updates = ();
432 for my $userid (@attending_userids) {
433 next if (exists($slack_userid_to_real_name{$userid}));
435 # Make sure they have a row in the spreadsheet.
437 if (exists($slack_userid_to_row{$userid})) {
438 $write_row = $slack_userid_to_row{$userid};
440 $write_row = $cur_row++;
441 $slack_userid_to_row{$userid} = $write_row;
442 push @slack_mapping_updates, {
443 range => "Slack-mapping!A$write_row:A$write_row",
444 values => [ [ $userid ]]
448 # Fetch their Slack name if we don't already have it.
450 if (exists($slack_userid_to_slack_name{$userid})) {
451 $slack_name = $slack_userid_to_slack_name{$userid};
453 $slack_userid_to_slack_name{$userid} = $slack_name;
454 $slack_name = get_slack_name($ua, $userid);
455 push @slack_mapping_updates, {
456 range => "Slack-mapping!B$write_row:B$write_row",
457 values => [ [ $slack_name ]]
459 $slack_userid_to_slack_name{$userid} = $slack_name;
462 if (exists($seen_names{lc $slack_name})) {
463 # The name exists exactly, once or more, so it's a direct match and we ignore any fuzz.
464 $slack_userid_to_real_name{$userid} = $slack_name;
465 push @slack_mapping_updates, {
466 range => "Slack-mapping!C$write_row:C$write_row",
467 values => [ [ $slack_name ]]
470 # Do a search through all the available names in the sheet to find an obvious(ish) match.
472 my $main_sheet_rows = $main_sheet_json->{'sheets'}[0]{'data'}[0]{'rowData'};
473 for my $row (@$main_sheet_rows) {
474 for my $val (@{$row->{'values'}}) {
475 my $name = get_spreadsheet_name($val);
476 if (defined($name) && matches_name($slack_name, $name)) {
477 push @candidates, $name;
481 if ($#candidates == -1) {
482 skv_log("$slack_name ($userid) er pƄmeldt pƄ Slack, men fant ikke et regneark-navn for dem.");
483 possibly_nag_user($dbh, $ua, $userid);
484 } elsif ($#candidates == 0) {
485 my $name = $candidates[0];
486 $slack_userid_to_real_name{$userid} = $name;
487 push @slack_mapping_updates, {
488 range => "Slack-mapping!C$write_row:C$write_row",
489 values => [ [ $name ]]
492 skv_log("$slack_name ($userid) er pƄmeldt pƄ Slack, men hadde flere fuzzy-matcher; vet ikke hvilket regneark-navn som skal brukes.");
497 valueInputOption => 'USER_ENTERED',
498 data => \@slack_mapping_updates
500 $response = $ua->post(
501 'https://sheets.googleapis.com/v4/spreadsheets/' . $config::sheet_id . '/values:batchUpdate?key=' . $config::gsheets_api_key,
502 Content => JSON::XS::encode_json($update),
503 Content_type => 'application/json;charset=UTF-8',
504 Authorization => 'Bearer ' . $token
506 die $response->decoded_content if (!$response->is_success);
508 # Now that we have Slack names, we can log double-reacters.
509 for my $userid (keys %double) {
510 my $name = best_name_for_log($userid, \%slack_userid_to_real_name, \%slack_userid_to_slack_name);
511 skv_log("$name er pƄmeldt flere steder pƄ Slack; vet ikke hvilken som skal brukes.");
514 # Find the list of names to mark yellow.
515 my %want_colors = ();
516 my $main_sheet_rows = $main_sheet_json->{'sheets'}[0]{'data'}[0]{'rowData'};
517 for my $userid (@attending_userids) {
518 next if (!exists($slack_userid_to_real_name{$userid}));
519 my $slack_name = $slack_userid_to_slack_name{$userid};
520 my $real_name = $slack_userid_to_real_name{$userid};
522 # See if we can find them in the spreadsheet.
523 if (!exists($seen_names{lc $real_name})) {
524 # TODO: Perhaps move this logic further down, for consistency?
525 skv_log("$slack_name ($userid) er pƄmeldt pƄ Slack, og er mappet til $real_name, men var ikke i noen gruppe.");
527 my $seen = $seen_names{lc $real_name};
528 if (scalar @$seen >= 2) {
529 skv_log("$slack_name ($userid) er pƄmeldt pƄ Slack, men stƄr flere steder (se over); vet ikke hvilken celle som skal brukes.");
531 $want_colors{$seen->[0][0]} = $colors{$userid};
536 # Find the list of names we already marked yellow.
537 my %have_colors = ();
538 $dbh->{AutoCommit} = 0;
539 $dbh->do('SET TRANSACTION ISOLATION LEVEL SERIALIZABLE');
540 $q = $dbh->prepare('SELECT name,color FROM applied WHERE channel=? AND ts=?');
541 $q->execute($config::invitation_channel, $invitation_ts);
542 while (my $ref = $q->fetchrow_hashref) {
543 $have_colors{$ref->{'name'}} = $ref->{'color'};
546 my @diffs = find_diff($dbh, $invitation_ts, \%want_colors, \%have_colors, \%seen_names);
548 my @yellow_updates = ();
549 if (scalar @diffs > 0) {
550 # Now fill in the actual stuff.
551 for my $diff (@diffs) {
552 my $real_name = $diff->[0];
554 my $seen = $seen_names{lc $real_name};
556 # We've already complained about these earlier, so just skip them silently.
557 next if (scalar @$seen > 1);
559 # See if we can find them in the spreadsheet.
560 die "Could not find $real_name" if (!defined($seen));
561 my $rowno = $seen->[0][1];
562 my $colno = $seen->[0][2];
563 push @yellow_updates, {
567 userEnteredFormat => $diff->[1]
570 fields => 'userEnteredFormat.backgroundColor',
573 startRowIndex => $rowno,
574 endRowIndex => $rowno + 1,
575 startColumnIndex => $colno,
576 endColumnIndex => $colno + 1
583 my @recent_changes = create_reaction_log($dbh, $invitation_ts, \%slack_userid_to_real_name, \%slack_userid_to_slack_name);
584 push @yellow_updates, {
586 rows => \@recent_changes,
587 fields => 'userEnteredValue.stringValue',
589 sheetId => $config::log_tab_id,
591 endRowIndex => 4 + scalar @recent_changes,
592 startColumnIndex => 0,
598 # Push the final set of updates (including the log).
600 push @yellow_updates, serialize_skv_log_to_sheet();
601 sheet_batch_update($ua, $token, \@yellow_updates);
604 my $elapsed = Time::HiRes::tv_interval($start);
605 printf "Tok %.0f ms.\n", 1e3 * $elapsed;
608 if ($#ARGV >= 0 && $ARGV[0] eq '--daemon') {
609 # Start with a single, forced run.
610 unlink("/srv/skvidar-slack.sesse.net/marker");
614 if (!unlink("/srv/skvidar-slack.sesse.net/marker")) {
615 unless ($!{ENOENT}) {
616 warn "/srv/skvidar-slack.sesse.net/marker: $!";
625 warn "Died with: $@";