10 binmode STDOUT, ':utf8';
11 binmode STDERR, ':utf8';
15 # - detect moves between groups
17 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::slack_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-5]/);
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 sub serialize_skv_log_to_sheet {
111 userEnteredValue => { stringValue => join("\n", @log) }
114 fields => 'userEnteredValue.stringValue',
116 sheetId => $config::log_tab_id,
119 startColumnIndex => 0,
126 sub sheet_batch_update {
127 my ($ua, $token, @requests) = @_;
129 requests => \@requests
131 my $response = $ua->post(
132 'https://sheets.googleapis.com/v4/spreadsheets/' . $config::sheet_id . ':batchUpdate?key=' . $config::gsheets_api_key,
133 Content => JSON::XS::encode_json($update),
134 Content_type => 'application/json;charset=UTF-8',
135 Authorization => 'Bearer ' . $token
137 die $response->decoded_content if !$response->is_success;
140 sub get_spreadsheet_with_title {
141 my ($ua, $token, $wanted_sheet_title) = @_;
143 # See if we have any spreadsheets that match this title.
144 my $response = $ua->get('https://sheets.googleapis.com/v4/spreadsheets/' . $config::sheet_id . '?key=' . $config::gsheets_api_key . '&fields=sheets/properties',
145 Authorization => 'Bearer ' . $token
147 my $sheets_json = JSON::XS::decode_json($response->decoded_content);
148 my ($tab_name, $tab_id);
149 for my $sheet (@{$sheets_json->{'sheets'}}) {
150 my $title = $sheet->{'properties'}{'title'};
151 my $sheet_id = $sheet->{'properties'}{'sheetId'};
152 if ($title =~ /\Q$wanted_sheet_title\E/) {
153 # skv_log("Synkroniserer ($config::invitation_channel, $invitation_ts) mot arket ā$titleā (fane-ID $sheet_id).");
154 return ($title, $sheet_id);
157 return (undef, undef);
160 # Make a mapping of lowercase name -> list of [canonical name, row number, column number]
161 sub find_where_each_name_is {
165 my $rows = $json->{'sheets'}[0]{'data'}[0]{'rowData'};
167 for my $row (@$rows) {
169 for my $val (@{$row->{'values'}}) {
170 my $name = get_spreadsheet_name($val);
171 if (defined($name)) {
172 push @{$seen_names{lc $name}}, [$name, $rowno, $colno];
182 sub best_name_for_log {
183 my ($userid, $slack_userid_to_real_name, $slack_userid_to_slack_name) = @_;
184 if (exists($slack_userid_to_real_name->{$userid})) {
185 return $slack_userid_to_real_name->{$userid};
186 } elsif (exists($slack_userid_to_slack_name->{$userid})) {
187 return $slack_userid_to_slack_name->{$userid} . ' (fant ikke regneark-navn)';
189 # Should only happen if we didn't see the initial reaction_add, only reaction_remove.
190 # (TODO: Is the comment above true anymore, now that we use this from multiple contexts?)
191 return $userid . ' (fant ikke Slack-navn)';
195 # Add the reaction log. (This only takes into account the last change
196 # for each user; earlier ones are irrelevant and don't count. But it
197 # doesn't deduplicate across reactions. Meh.)
198 sub create_reaction_log {
199 my ($dbh, $invitation_ts, $slack_userid_to_real_name, $slack_userid_to_slack_name) = @_;
201 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\') 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');
202 $q->execute($config::invitation_channel, $invitation_ts);
203 my @recent_changes = ();
204 while (my $ref = $q->fetchrow_hashref) {
205 my $msg = $ref->{'event_ts'};
206 if ($ref->{'event_type'} eq 'reaction_added') {
211 if ($ref->{'reaction'} eq 'open_mouth') {
217 $msg .= best_name_for_log($ref->{'userid'}, $slack_userid_to_real_name, $slack_userid_to_slack_name);
218 push @recent_changes, { values => [{ userEnteredValue => { stringValue => $msg } }] };
220 while (scalar @recent_changes < 50) {
221 push @recent_changes, { values => [{ userEnteredValue => { stringValue => '' } }] };
223 return @recent_changes;
226 # Also applies the diff to the database (a bit ugly).
228 my ($dbh, $invitation_ts, $want_names, $have_names, $seen_names) = @_;
231 for my $real_name (keys %$want_names) {
232 next if (exists($have_names->{$real_name}));
233 skv_log("Markerer at $real_name skal pƄ trening.");
245 $dbh->do('INSERT INTO applied (channel, ts, name) VALUES (?, ?, ?)', undef,
246 $config::invitation_channel, $invitation_ts, $real_name);
248 for my $real_name (keys %$have_names) {
249 next if (exists($want_names->{$real_name}));
250 if (!exists($seen_names->{lc $real_name})) {
251 # TODO: This can somehow come if we try to add someone who's not in the sheet, too?
252 skv_log("Ćnsket Ć„ fjerne at $real_name skulle pĆ„ trening, men de var ikke i regnearket lenger.");
254 skv_log("Fjerner at $real_name skal pƄ trening.");
266 $dbh->do('DELETE FROM applied WHERE channel=? AND ts=? AND name=?', undef,
267 $config::invitation_channel, $invitation_ts, $real_name);
273 sub possibly_nag_user {
274 my ($dbh, $ua, $userid) = @_;
276 # See if we've nagged this user before.
277 my $q = $dbh->prepare('SELECT * FROM users_nagged WHERE userid=?');
278 $q->execute($userid);
279 if (defined($q->fetchrow_hashref)) {
283 skv_log("Sender melding til $userid for Ć„ spĆørre om gruppe.");
284 my $response = $ua->post(
285 'https://slack.com/api/conversations.open',
286 Content => JSON::XS::encode_json({ users => [ $userid ] }),
287 Content_type => 'application/json;charset=UTF-8',
288 Authorization => 'Bearer ' . $config::slack_oauth_token
290 die $response->status_line if !$response->is_success;
292 my $im_json = JSON::XS::decode_json($response->decoded_content);
293 die "Something went wrong: " . $response->decoded_content if (!defined($im_json) || !$im_json->{'ok'});
294 my $channel_id = $im_json->{'channel'}{'id'};
296 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!";
298 $response = $ua->post(
299 'https://slack.com/api/chat.postMessage',
300 Content => JSON::XS::encode_json({ channel => $channel_id, text => $msg }),
301 Content_type => 'application/json;charset=UTF-8',
302 Authorization => 'Bearer ' . $config::slack_oauth_token
304 my $msg_json = JSON::XS::decode_json($response->decoded_content);
305 die "Something went wrong: " . $response->decoded_content if (!defined($msg_json) || !$msg_json->{'ok'});
307 # Mark that we've sent the message, so it won't happen again.
308 $dbh->do('INSERT INTO users_nagged (userid, last_nag) VALUES (?, CURRENT_TIMESTAMP)', undef, $userid);
313 skv_log("Siste sync startet: " . POSIX::ctime(time));
315 # Initialize the handles we need for communication.
316 $dbh = DBI->connect("dbi:Pg:dbname=$config::dbname;host=127.0.0.1", $config::dbuser, $config::dbpass, {RaiseError => 1})
317 or die "Could not connect to Postgres: " . DBI->errstr;
318 my $ua = LWP::UserAgent->new('SKVidarLang/1.0');
319 my $token = get_oauth_bearer_token($ua);
321 # Find the newest message, and what it is linked to.
322 # TODO: Support more than one, and test better for errors here.
323 my $q = $dbh->prepare('select * from message_sheet_link where channel=? order by ts desc limit 1');
324 $q->execute($config::invitation_channel);
325 my $linkref = $q->fetchrow_hashref;
326 my $invitation_ts = $linkref->{'ts'};
327 my $wanted_sheet_title = $linkref->{'sheet_title'};
328 die "Could not get newest sheet title" if (!defined($wanted_sheet_title));
330 my ($tab_name, $tab_id) = get_spreadsheet_with_title($ua, $token, $wanted_sheet_title);
331 if (!defined($tab_name)) {
332 skv_log("Fant ikke noen fane med ā$wanted_sheet_titleā i navnet; kan ikke synkronisere.\n");
333 sheet_batch_update($ua, $token, [ serialize_skv_log_to_sheet() ]);
337 # Find everyone who are marked as attending on Slack (via reactions).
338 $q = $dbh->prepare('SELECT DISTINCT userid FROM current_reactions WHERE channel=? AND ts=? AND reaction IN (\'heart\', \'open_mouth\')');
339 $q->execute($config::invitation_channel, $invitation_ts);
340 my @attending_userids = ();
341 while (my $ref = $q->fetchrow_hashref) {
342 push @attending_userids, $ref->{'userid'};
345 # Get the list of all people in the sheet (we're going to need them soon anyway).
346 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',
347 Authorization => 'Bearer ' . $token
349 my $main_sheet_json = JSON::XS::decode_json($response->decoded_content);
351 my %seen_names = find_where_each_name_is($main_sheet_json);
354 for my $name (sort keys %seen_names) {
355 my $seen = $seen_names{$name};
356 if (scalar @$seen >= 2) {
357 my $exemplar = $seen->[0][0];
358 skv_log("Duplikat: $exemplar (" . format_cell_names_for_seen($seen) . ")");
362 # Get our existing Slack->name mapping, from the sheets.
363 my %slack_userid_to_real_name = ();
364 my %slack_userid_to_slack_name = ();
365 my %slack_userid_to_row = ();
366 $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',
367 Authorization => 'Bearer ' . $token
369 my $mapping_sheet_json = JSON::XS::decode_json($response->decoded_content);
370 my $mapping_sheet_rows = $mapping_sheet_json->{'sheets'}[0]{'data'}[0]{'rowData'};
372 for my $row (@$mapping_sheet_rows) {
373 my $slack_id = $row->{'values'}[0]{'userEnteredValue'}{'stringValue'};
374 my $slack_name = $row->{'values'}[1]{'userEnteredValue'}{'stringValue'};
375 my $real_name = get_spreadsheet_name($row->{'values'}[2]); # TODO support more
376 $slack_userid_to_row{$slack_id} = $cur_row++;
377 next if (!defined($slack_name));
378 $slack_userid_to_slack_name{$slack_id} = $slack_name;
379 next if (!defined($real_name));
380 $slack_userid_to_real_name{$slack_id} = $real_name;
383 # See which ones we don't have a mapping for, and look them up in Slack.
384 # TODO: Use an append call instead of $cur_row?
385 my @slack_mapping_updates = ();
386 for my $userid (@attending_userids) {
387 next if (exists($slack_userid_to_real_name{$userid}));
389 # Make sure they have a row in the spreadsheet.
391 if (exists($slack_userid_to_row{$userid})) {
392 $write_row = $slack_userid_to_row{$userid};
394 $write_row = $cur_row++;
395 $slack_userid_to_row{$userid} = $write_row;
396 push @slack_mapping_updates, {
397 range => "Slack-mapping!A$write_row:A$write_row",
398 values => [ [ $userid ]]
402 # Fetch their Slack name if we don't already have it.
404 if (exists($slack_userid_to_slack_name{$userid})) {
405 $slack_name = $slack_userid_to_slack_name{$userid};
407 $slack_userid_to_slack_name{$userid} = $slack_name;
408 $slack_name = get_slack_name($ua, $userid);
409 push @slack_mapping_updates, {
410 range => "Slack-mapping!B$write_row:B$write_row",
411 values => [ [ $slack_name ]]
413 $slack_userid_to_slack_name{$userid} = $slack_name;
416 if (exists($seen_names{lc $slack_name})) {
417 # The name exists exactly, once or more, so it's a direct match and we ignore any fuzz.
418 $slack_userid_to_real_name{$userid} = $slack_name;
419 push @slack_mapping_updates, {
420 range => "Slack-mapping!C$write_row:C$write_row",
421 values => [ [ $slack_name ]]
424 # Do a search through all the available names in the sheet to find an obvious(ish) match.
426 my $main_sheet_rows = $main_sheet_json->{'sheets'}[0]{'data'}[0]{'rowData'};
427 for my $row (@$main_sheet_rows) {
428 for my $val (@{$row->{'values'}}) {
429 my $name = get_spreadsheet_name($val);
430 if (defined($name) && matches_name($slack_name, $name)) {
431 push @candidates, $name;
435 if ($#candidates == -1) {
436 skv_log("$slack_name ($userid) er pƄmeldt pƄ Slack, men fant ikke et regneark-navn for dem.");
437 possibly_nag_user($dbh, $ua, $userid);
438 } elsif ($#candidates == 0) {
439 my $name = $candidates[0];
440 $slack_userid_to_real_name{$userid} = $name;
441 push @slack_mapping_updates, {
442 range => "Slack-mapping!C$write_row:C$write_row",
443 values => [ [ $name ]]
446 skv_log("$slack_name ($userid) er pƄmeldt pƄ Slack, men hadde flere fuzzy-matcher; vet ikke hvilket regneark-navn som skal brukes.");
451 valueInputOption => 'USER_ENTERED',
452 data => \@slack_mapping_updates
454 $response = $ua->post(
455 'https://sheets.googleapis.com/v4/spreadsheets/' . $config::sheet_id . '/values:batchUpdate?key=' . $config::gsheets_api_key,
456 Content => JSON::XS::encode_json($update),
457 Content_type => 'application/json;charset=UTF-8',
458 Authorization => 'Bearer ' . $token
460 die $response->decoded_content if (!$response->is_success);
462 # Find the list of names to mark yellow.
464 my $main_sheet_rows = $main_sheet_json->{'sheets'}[0]{'data'}[0]{'rowData'};
465 for my $userid (@attending_userids) {
466 next if (!exists($slack_userid_to_real_name{$userid}));
467 my $slack_name = $slack_userid_to_slack_name{$userid};
468 my $real_name = $slack_userid_to_real_name{$userid};
470 # See if we can find them in the spreadsheet.
471 if (!exists($seen_names{lc $real_name})) {
472 # TODO: Perhaps move this logic further down, for consistency?
473 skv_log("$slack_name ($userid) er pƄmeldt pƄ Slack, og er mappet til $real_name, men var ikke i noen gruppe.");
475 my $seen = $seen_names{lc $real_name};
476 if (scalar @$seen >= 2) {
477 skv_log("$slack_name ($userid) er pƄmeldt pƄ Slack, men stƄr flere steder (se over); vet ikke hvilken celle som skal brukes.");
479 $want_names{$seen->[0][0]} = 1;
484 # Find the list of names we already marked yellow.
486 $dbh->{AutoCommit} = 0;
487 $dbh->do('SET TRANSACTION ISOLATION LEVEL SERIALIZABLE');
488 $q = $dbh->prepare('SELECT name FROM applied WHERE channel=? AND ts=?');
489 $q->execute($config::invitation_channel, $invitation_ts);
490 while (my $ref = $q->fetchrow_hashref) {
491 $have_names{$ref->{'name'}} = 1;
494 my @diffs = find_diff($dbh, $invitation_ts, \%want_names, \%have_names, \%seen_names);
496 my @yellow_updates = ();
497 if (scalar @diffs > 0) {
498 # Now fill in the actual stuff.
499 for my $diff (@diffs) {
500 my $real_name = $diff->[0];
502 # See if we can find them in the spreadsheet.
503 my $seen = $seen_names{lc $real_name};
504 die if (!defined($seen) || scalar @$seen > 1);
505 my $rowno = $seen->[0][1];
506 my $colno = $seen->[0][2];
507 push @yellow_updates, {
511 userEnteredFormat => $diff->[1]
514 fields => 'userEnteredFormat.backgroundColor',
517 startRowIndex => $rowno,
518 endRowIndex => $rowno + 1,
519 startColumnIndex => $colno,
520 endColumnIndex => $colno + 1
527 my @recent_changes = create_reaction_log($dbh, $invitation_ts, \%slack_userid_to_real_name, \%slack_userid_to_slack_name);
528 push @yellow_updates, {
530 rows => \@recent_changes,
531 fields => 'userEnteredValue.stringValue',
533 sheetId => $config::log_tab_id,
535 endRowIndex => 4 + scalar @recent_changes,
536 startColumnIndex => 0,
542 # Push the final set of updates (including the log).
544 push @yellow_updates, serialize_skv_log_to_sheet();
545 sheet_batch_update($ua, $token, \@yellow_updates);
549 if ($#ARGV >= 0 && $ARGV[0] eq '--daemon') {
551 if (!unlink("/srv/skvidar-slack.sesse.net/marker")) {
552 unless ($!{ENOENT}) {
553 warn "/srv/skvidar-slack.sesse.net/marker: $!";
562 warn "Died with: $@";