11 binmode STDOUT, ':utf8';
12 binmode STDERR, ':utf8';
16 # - detect moves between groups
18 require '../include/config.pm';
44 sub get_oauth_bearer_token {
47 my $jwt = JSON::XS::encode_json({
48 "iss" => $config::jwt_key->{'client_email'},
49 "scope" => "https://www.googleapis.com/auth/spreadsheets",
50 "aud" => "https://www.googleapis.com/oauth2/v4/token",
54 my $jws_token = Crypt::JWT::encode_jwt(payload=>$jwt, alg=>'RS256', key=>\$config::jwt_key->{'private_key'});
55 my $response = $ua->post('https://www.googleapis.com/oauth2/v4/token', [
56 'grant_type' => 'urn:ietf:params:oauth:grant-type:jwt-bearer',
57 'assertion' => $jws_token ]);
58 return JSON::XS::decode_json($response->decoded_content)->{'access_token'};
62 my ($ua, $userid) = @_;
63 my $req = HTTP::Request->new('GET', 'https://slack.com/api/users.info?user=' . $userid, [
64 'Authorization' => 'Bearer ' . $config::slack_oauth_token
66 my $response = $ua->request($req);
67 die $response->status_line if !$response->is_success;
69 my $user_json = JSON::XS::decode_json($response->decoded_content);
70 die "Something went wrong: " . $response->decoded_content if (!defined($user_json) || !$user_json->{'ok'});
72 return $user_json->{'user'}{'real_name'};
75 sub get_spreadsheet_name {
77 my $name = $cell->{'userEnteredValue'}{'stringValue'};
78 return undef if (!defined($name));
79 return undef if ($name =~ /^G[1-4]\.[1-5]/);
84 $name =~ s/G\d\.\d?\??//;
92 my ($slack_name, $spreadsheet_name) = @_;
93 if (lc($slack_name) eq lc($spreadsheet_name)) {
97 my @ap = split /\s+/, $slack_name;
98 my @bp = split /\s+/, $spreadsheet_name;
99 if (scalar @ap >= 2 && scalar @bp >= 2 && lc($ap[0]) eq lc($bp[0])) {
100 # First name matches, try to match some surname
102 for my $ai (1..$#ap) {
103 for my $bi (1..$#bp) {
104 $found = 1 if (lc($ap[$ai]) eq lc($bp[$bi]));
108 skv_log("Fuzzy-matchet $slack_name -> $spreadsheet_name.");
116 sub format_cell_names_for_seen {
118 my @cells = map { chr(ord('A') + $_->[2]) . $_->[1] } @$seen;
119 return join(', ', @cells);
124 print STDERR "$msg\n";
128 sub serialize_skv_log_to_sheet {
133 userEnteredValue => { stringValue => join("\n", @log) }
136 fields => 'userEnteredValue.stringValue',
138 sheetId => $config::log_tab_id,
141 startColumnIndex => 0,
148 sub sheet_batch_update {
149 my ($ua, $token, @requests) = @_;
151 requests => \@requests
153 my $response = $ua->post(
154 'https://sheets.googleapis.com/v4/spreadsheets/' . $config::sheet_id . ':batchUpdate?key=' . $config::gsheets_api_key,
155 Content => JSON::XS::encode_json($update),
156 Content_type => 'application/json;charset=UTF-8',
157 Authorization => 'Bearer ' . $token
159 die $response->decoded_content if !$response->is_success;
162 sub get_spreadsheet_with_title {
163 my ($ua, $token, $wanted_sheet_title) = @_;
165 # See if we have any spreadsheets that match this title.
166 my $response = $ua->get('https://sheets.googleapis.com/v4/spreadsheets/' . $config::sheet_id . '?key=' . $config::gsheets_api_key . '&fields=sheets/properties',
167 Authorization => 'Bearer ' . $token
169 my $sheets_json = JSON::XS::decode_json($response->decoded_content);
170 my ($tab_name, $tab_id);
171 for my $sheet (@{$sheets_json->{'sheets'}}) {
172 my $title = $sheet->{'properties'}{'title'};
173 my $sheet_id = $sheet->{'properties'}{'sheetId'};
174 if ($title =~ /\Q$wanted_sheet_title\E/) {
175 # skv_log("Synkroniserer ($config::invitation_channel, $invitation_ts) mot arket ā$titleā (fane-ID $sheet_id).");
176 return ($title, $sheet_id);
179 return (undef, undef);
182 # Make a mapping of lowercase name -> list of [canonical name, row number, column number]
183 sub find_where_each_name_is {
187 my $rows = $json->{'sheets'}[0]{'data'}[0]{'rowData'};
189 for my $row (@$rows) {
191 for my $val (@{$row->{'values'}}) {
192 my $name = get_spreadsheet_name($val);
193 if (defined($name)) {
194 push @{$seen_names{lc $name}}, [$name, $rowno, $colno];
204 sub best_name_for_log {
205 my ($userid, $slack_userid_to_real_name, $slack_userid_to_slack_name) = @_;
206 if (exists($slack_userid_to_real_name->{$userid})) {
207 return $slack_userid_to_real_name->{$userid};
208 } elsif (exists($slack_userid_to_slack_name->{$userid})) {
209 return $slack_userid_to_slack_name->{$userid} . ' (fant ikke regneark-navn)';
211 # Should only happen if we didn't see the initial reaction_add, only reaction_remove.
212 # (TODO: Is the comment above true anymore, now that we use this from multiple contexts?)
213 return $userid . ' (fant ikke Slack-navn)';
217 # Add the reaction log. (This only takes into account the last change
218 # for each user; earlier ones are irrelevant and don't count. But it
219 # doesn't deduplicate across reactions. Meh.)
220 sub create_reaction_log {
221 my ($dbh, $invitation_ts, $slack_userid_to_real_name, $slack_userid_to_slack_name) = @_;
223 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');
224 $q->execute($config::invitation_channel, $invitation_ts);
225 my @recent_changes = ();
226 while (my $ref = $q->fetchrow_hashref) {
227 my $msg = $ref->{'event_ts'};
228 if ($ref->{'event_type'} eq 'reaction_added') {
233 if ($ref->{'reaction'} eq 'open_mouth') {
235 } elsif ($ref->{'reaction'} eq 'blue_heart') {
241 $msg .= best_name_for_log($ref->{'userid'}, $slack_userid_to_real_name, $slack_userid_to_slack_name);
242 push @recent_changes, { values => [{ userEnteredValue => { stringValue => $msg } }] };
244 while (scalar @recent_changes < 50) {
245 push @recent_changes, { values => [{ userEnteredValue => { stringValue => '' } }] };
247 return @recent_changes;
250 # Also applies the diff to the database (a bit ugly).
252 my ($dbh, $invitation_ts, $want_colors, $have_colors, $seen_names) = @_;
255 for my $real_name (keys %$want_colors) {
256 my $wc = $want_colors->{$real_name};
257 if (exists($have_colors->{$real_name})) {
258 if ($have_colors->{$real_name} eq $wc) {
262 skv_log("Markerer at $real_name har byttet treningssted.");
264 $real_name, { backgroundColor => $rgb{$wc} }
266 $dbh->do('UPDATE applied SET color=? WHERE channel=? AND ts=? AND name=?', undef,
267 $wc, $config::invitation_channel, $invitation_ts, $real_name);
269 skv_log("Markerer at $real_name skal pƄ trening.");
271 $real_name, { backgroundColor => $rgb{$wc} }
273 $dbh->do('INSERT INTO applied (channel, ts, name, color) VALUES (?, ?, ?, ?)', undef,
274 $config::invitation_channel, $invitation_ts, $real_name, $wc);
277 for my $real_name (keys %$have_colors) {
278 next if (exists($want_colors->{$real_name}));
279 if (!exists($seen_names->{lc $real_name})) {
280 # TODO: This can somehow come if we try to add someone who's not in the sheet, too?
281 skv_log("Ćnsket Ć„ fjerne at $real_name skulle pĆ„ trening, men de var ikke i regnearket lenger.");
283 skv_log("Fjerner at $real_name skal pƄ trening.");
285 $real_name, { backgroundColor => $rgb{white} }
287 $dbh->do('DELETE FROM applied WHERE channel=? AND ts=? AND name=?', undef,
288 $config::invitation_channel, $invitation_ts, $real_name);
294 sub possibly_nag_user {
295 my ($dbh, $ua, $userid) = @_;
297 # See if we've nagged this user before.
298 my $q = $dbh->prepare('SELECT * FROM users_nagged WHERE userid=?');
299 $q->execute($userid);
300 if (defined($q->fetchrow_hashref)) {
304 skv_log("Sender melding til $userid for Ć„ spĆørre om gruppe.");
305 my $response = $ua->post(
306 'https://slack.com/api/conversations.open',
307 Content => JSON::XS::encode_json({ users => [ $userid ] }),
308 Content_type => 'application/json;charset=UTF-8',
309 Authorization => 'Bearer ' . $config::slack_oauth_token
311 die $response->status_line if !$response->is_success;
313 my $im_json = JSON::XS::decode_json($response->decoded_content);
314 die "Something went wrong: " . $response->decoded_content if (!defined($im_json) || !$im_json->{'ok'});
315 my $channel_id = $im_json->{'channel'}{'id'};
317 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!";
319 $response = $ua->post(
320 'https://slack.com/api/chat.postMessage',
321 Content => JSON::XS::encode_json({ channel => $channel_id, text => $msg }),
322 Content_type => 'application/json;charset=UTF-8',
323 Authorization => 'Bearer ' . $config::slack_oauth_token
325 my $msg_json = JSON::XS::decode_json($response->decoded_content);
326 die "Something went wrong: " . $response->decoded_content if (!defined($msg_json) || !$msg_json->{'ok'});
328 # Mark that we've sent the message, so it won't happen again.
329 $dbh->do('INSERT INTO users_nagged (userid, last_nag) VALUES (?, CURRENT_TIMESTAMP)', undef, $userid);
333 my $start = [Time::HiRes::gettimeofday];
336 skv_log("Siste sync startet: " . POSIX::ctime(time));
338 # Initialize the handles we need for communication.
339 $dbh = DBI->connect("dbi:Pg:dbname=$config::dbname;host=127.0.0.1", $config::dbuser, $config::dbpass, {RaiseError => 1})
340 or die "Could not connect to Postgres: " . DBI->errstr;
341 my $ua = LWP::UserAgent->new('SKVidarLang/1.0');
342 my $token = get_oauth_bearer_token($ua);
344 # Find the newest message, and what it is linked to.
345 # TODO: Support more than one, and test better for errors here.
346 my $q = $dbh->prepare('select * from message_sheet_link where channel=? order by ts desc limit 1');
347 $q->execute($config::invitation_channel);
348 my $linkref = $q->fetchrow_hashref;
349 my $invitation_ts = $linkref->{'ts'};
350 my $wanted_sheet_title = $linkref->{'sheet_title'};
351 die "Could not get newest sheet title" if (!defined($wanted_sheet_title));
353 my ($tab_name, $tab_id) = get_spreadsheet_with_title($ua, $token, $wanted_sheet_title);
354 if (!defined($tab_name)) {
355 skv_log("Fant ikke noen fane med ā$wanted_sheet_titleā i navnet; kan ikke synkronisere.\n");
356 sheet_batch_update($ua, $token, [ serialize_skv_log_to_sheet() ]);
360 # Find everyone who are marked as attending on Slack (via reactions).
361 $q = $dbh->prepare('SELECT DISTINCT userid,reaction FROM current_reactions WHERE channel=? AND ts=? AND reaction IN (\'heart\', \'open_mouth\', \'blue_heart\')');
362 $q->execute($config::invitation_channel, $invitation_ts);
363 my @attending_userids = ();
366 while (my $ref = $q->fetchrow_hashref) {
367 my $userid = $ref->{'userid'};
368 push @attending_userids, $userid;
369 if ($ref->{'reaction'} eq 'blue_heart') {
370 if (exists($colors{$userid}) && $colors{$userid} eq 'yellow') {
371 $double{$userid} = 1;
373 $colors{$userid} = 'blue';
375 if (exists($colors{$userid}) && $colors{$userid} eq 'blue') {
376 $double{$userid} = 1;
378 $colors{$userid} = 'yellow';
382 # Remove double-attenders (we will log them as warnings further down).
383 @attending_userids = grep { !exists($double{$_}) } @attending_userids;
384 for my $userid (keys %double) {
385 delete $colors{$userid};
388 # Get the list of all people in the sheet (we're going to need them soon anyway).
389 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',
390 Authorization => 'Bearer ' . $token
392 my $main_sheet_json = JSON::XS::decode_json($response->decoded_content);
394 my %seen_names = find_where_each_name_is($main_sheet_json);
397 for my $name (sort keys %seen_names) {
398 my $seen = $seen_names{$name};
399 if (scalar @$seen >= 2) {
400 my $exemplar = $seen->[0][0];
401 skv_log("Duplikat: $exemplar (" . format_cell_names_for_seen($seen) . ")");
405 # Get our existing Slack->name mapping, from the sheets.
406 my %slack_userid_to_real_name = ();
407 my %slack_userid_to_slack_name = ();
408 my %slack_userid_to_row = ();
409 $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',
410 Authorization => 'Bearer ' . $token
412 my $mapping_sheet_json = JSON::XS::decode_json($response->decoded_content);
413 my $mapping_sheet_rows = $mapping_sheet_json->{'sheets'}[0]{'data'}[0]{'rowData'};
415 for my $row (@$mapping_sheet_rows) {
416 my $slack_id = $row->{'values'}[0]{'userEnteredValue'}{'stringValue'};
417 my $slack_name = $row->{'values'}[1]{'userEnteredValue'}{'stringValue'};
418 my $real_name = get_spreadsheet_name($row->{'values'}[2]); # TODO support more
419 $slack_userid_to_row{$slack_id} = $cur_row++;
420 next if (!defined($slack_name));
421 $slack_userid_to_slack_name{$slack_id} = $slack_name;
422 next if (!defined($real_name));
423 $slack_userid_to_real_name{$slack_id} = $real_name;
426 # See which ones we don't have a mapping for, and look them up in Slack.
427 # TODO: Use an append call instead of $cur_row?
428 my @slack_mapping_updates = ();
429 for my $userid (@attending_userids) {
430 next if (exists($slack_userid_to_real_name{$userid}));
432 # Make sure they have a row in the spreadsheet.
434 if (exists($slack_userid_to_row{$userid})) {
435 $write_row = $slack_userid_to_row{$userid};
437 $write_row = $cur_row++;
438 $slack_userid_to_row{$userid} = $write_row;
439 push @slack_mapping_updates, {
440 range => "Slack-mapping!A$write_row:A$write_row",
441 values => [ [ $userid ]]
445 # Fetch their Slack name if we don't already have it.
447 if (exists($slack_userid_to_slack_name{$userid})) {
448 $slack_name = $slack_userid_to_slack_name{$userid};
450 $slack_userid_to_slack_name{$userid} = $slack_name;
451 $slack_name = get_slack_name($ua, $userid);
452 push @slack_mapping_updates, {
453 range => "Slack-mapping!B$write_row:B$write_row",
454 values => [ [ $slack_name ]]
456 $slack_userid_to_slack_name{$userid} = $slack_name;
459 if (exists($seen_names{lc $slack_name})) {
460 # The name exists exactly, once or more, so it's a direct match and we ignore any fuzz.
461 $slack_userid_to_real_name{$userid} = $slack_name;
462 push @slack_mapping_updates, {
463 range => "Slack-mapping!C$write_row:C$write_row",
464 values => [ [ $slack_name ]]
467 # Do a search through all the available names in the sheet to find an obvious(ish) match.
469 my $main_sheet_rows = $main_sheet_json->{'sheets'}[0]{'data'}[0]{'rowData'};
470 for my $row (@$main_sheet_rows) {
471 for my $val (@{$row->{'values'}}) {
472 my $name = get_spreadsheet_name($val);
473 if (defined($name) && matches_name($slack_name, $name)) {
474 push @candidates, $name;
478 if ($#candidates == -1) {
479 skv_log("$slack_name ($userid) er pƄmeldt pƄ Slack, men fant ikke et regneark-navn for dem.");
480 possibly_nag_user($dbh, $ua, $userid);
481 } elsif ($#candidates == 0) {
482 my $name = $candidates[0];
483 $slack_userid_to_real_name{$userid} = $name;
484 push @slack_mapping_updates, {
485 range => "Slack-mapping!C$write_row:C$write_row",
486 values => [ [ $name ]]
489 skv_log("$slack_name ($userid) er pƄmeldt pƄ Slack, men hadde flere fuzzy-matcher; vet ikke hvilket regneark-navn som skal brukes.");
494 valueInputOption => 'USER_ENTERED',
495 data => \@slack_mapping_updates
497 $response = $ua->post(
498 'https://sheets.googleapis.com/v4/spreadsheets/' . $config::sheet_id . '/values:batchUpdate?key=' . $config::gsheets_api_key,
499 Content => JSON::XS::encode_json($update),
500 Content_type => 'application/json;charset=UTF-8',
501 Authorization => 'Bearer ' . $token
503 die $response->decoded_content if (!$response->is_success);
505 # Now that we have Slack names, we can log double-reacters.
506 for my $userid (keys %double) {
507 my $name = best_name_for_log($userid, \%slack_userid_to_real_name, \%slack_userid_to_slack_name);
508 skv_log("$name er pƄmeldt flere steder pƄ Slack; vet ikke hvilken som skal brukes.");
511 # Find the list of names to mark yellow.
512 my %want_colors = ();
513 my $main_sheet_rows = $main_sheet_json->{'sheets'}[0]{'data'}[0]{'rowData'};
514 for my $userid (@attending_userids) {
515 next if (!exists($slack_userid_to_real_name{$userid}));
516 my $slack_name = $slack_userid_to_slack_name{$userid};
517 my $real_name = $slack_userid_to_real_name{$userid};
519 # See if we can find them in the spreadsheet.
520 if (!exists($seen_names{lc $real_name})) {
521 # TODO: Perhaps move this logic further down, for consistency?
522 skv_log("$slack_name ($userid) er pƄmeldt pƄ Slack, og er mappet til $real_name, men var ikke i noen gruppe.");
524 my $seen = $seen_names{lc $real_name};
525 if (scalar @$seen >= 2) {
526 skv_log("$slack_name ($userid) er pƄmeldt pƄ Slack, men stƄr flere steder (se over); vet ikke hvilken celle som skal brukes.");
528 $want_colors{$seen->[0][0]} = $colors{$userid};
533 # Find the list of names we already marked yellow.
534 my %have_colors = ();
535 $dbh->{AutoCommit} = 0;
536 $dbh->do('SET TRANSACTION ISOLATION LEVEL SERIALIZABLE');
537 $q = $dbh->prepare('SELECT name,color FROM applied WHERE channel=? AND ts=?');
538 $q->execute($config::invitation_channel, $invitation_ts);
539 while (my $ref = $q->fetchrow_hashref) {
540 $have_colors{$ref->{'name'}} = $ref->{'color'};
543 my @diffs = find_diff($dbh, $invitation_ts, \%want_colors, \%have_colors, \%seen_names);
545 my @yellow_updates = ();
546 if (scalar @diffs > 0) {
547 # Now fill in the actual stuff.
548 for my $diff (@diffs) {
549 my $real_name = $diff->[0];
551 # See if we can find them in the spreadsheet.
552 my $seen = $seen_names{lc $real_name};
553 die if (!defined($seen) || scalar @$seen > 1);
554 my $rowno = $seen->[0][1];
555 my $colno = $seen->[0][2];
556 push @yellow_updates, {
560 userEnteredFormat => $diff->[1]
563 fields => 'userEnteredFormat.backgroundColor',
566 startRowIndex => $rowno,
567 endRowIndex => $rowno + 1,
568 startColumnIndex => $colno,
569 endColumnIndex => $colno + 1
576 my @recent_changes = create_reaction_log($dbh, $invitation_ts, \%slack_userid_to_real_name, \%slack_userid_to_slack_name);
577 push @yellow_updates, {
579 rows => \@recent_changes,
580 fields => 'userEnteredValue.stringValue',
582 sheetId => $config::log_tab_id,
584 endRowIndex => 4 + scalar @recent_changes,
585 startColumnIndex => 0,
591 # Push the final set of updates (including the log).
593 push @yellow_updates, serialize_skv_log_to_sheet();
594 sheet_batch_update($ua, $token, \@yellow_updates);
597 my $elapsed = Time::HiRes::tv_interval($start);
598 printf "Tok %.0f ms.\n", 1e3 * $elapsed;
601 if ($#ARGV >= 0 && $ARGV[0] eq '--daemon') {
602 # Start with a single, forced run.
603 unlink("/srv/skvidar-slack.sesse.net/marker");
607 if (!unlink("/srv/skvidar-slack.sesse.net/marker")) {
608 unless ($!{ENOENT}) {
609 warn "/srv/skvidar-slack.sesse.net/marker: $!";
618 warn "Died with: $@";