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::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 # Add the reaction log. (This only takes into account the last change
183 # for each user; earlier ones are irrelevant and don't count. But it
184 # doesn't deduplicate across reactions. Meh.)
185 sub create_reaction_log {
186 my ($dbh, $invitation_ts, $slack_userid_to_real_name, $slack_userid_to_slack_name) = @_;
188 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');
189 $q->execute($config::invitation_channel, $invitation_ts);
190 my @recent_changes = ();
191 while (my $ref = $q->fetchrow_hashref) {
192 my $msg = $ref->{'event_ts'};
193 if ($ref->{'event_type'} eq 'reaction_added') {
198 if ($ref->{'reaction'} eq 'open_mouth') {
204 if (exists($slack_userid_to_real_name->{$ref->{'userid'}})) {
205 $msg .= $slack_userid_to_real_name->{$ref->{'userid'}};
206 } elsif (exists($slack_userid_to_slack_name->{$ref->{'userid'}})) {
207 $msg .= $slack_userid_to_slack_name->{$ref->{'userid'}} . ' (fant ikke regneark-navn)';
209 # Should only happen if we didn't see the initial reaction_add, only reaction_remove.
210 $msg .= $ref->{'userid'} . ' (fant ikke Slack-navn)';
212 push @recent_changes, { values => [{ userEnteredValue => { stringValue => $msg } }] };
214 while (scalar @recent_changes < 50) {
215 push @recent_changes, { values => [{ userEnteredValue => { stringValue => '' } }] };
217 return @recent_changes;
220 # Also applies the diff to the database (a bit ugly).
222 my ($dbh, $invitation_ts, $want_names, $have_names, $seen_names) = @_;
225 for my $real_name (keys %$want_names) {
226 next if (exists($have_names->{$real_name}));
227 skv_log("Markerer at $real_name skal pƄ trening.");
239 $dbh->do('INSERT INTO applied (channel, ts, name) VALUES (?, ?, ?)', undef,
240 $config::invitation_channel, $invitation_ts, $real_name);
242 for my $real_name (keys %$have_names) {
243 next if (exists($want_names->{$real_name}));
244 if (!exists($seen_names->{lc $real_name})) {
245 # TODO: This can somehow come if we try to add someone who's not in the sheet, too?
246 skv_log("Ćnsket Ć„ fjerne at $real_name skulle pĆ„ trening, men de var ikke i regnearket lenger.");
248 skv_log("Fjerner at $real_name skal pƄ trening.");
260 $dbh->do('DELETE FROM applied WHERE channel=? AND ts=? AND name=?', undef,
261 $config::invitation_channel, $invitation_ts, $real_name);
267 sub possibly_nag_user {
268 my ($dbh, $ua, $userid) = @_;
270 # See if we've nagged this user before.
271 my $q = $dbh->prepare('SELECT * FROM users_nagged WHERE userid=?');
272 $q->execute($userid);
273 if (defined($q->fetchrow_hashref)) {
277 skv_log("Sender melding til $userid for Ć„ spĆørre om gruppe.");
278 my $response = $ua->post(
279 'https://slack.com/api/conversations.open',
280 Content => JSON::XS::encode_json({ users => [ $userid ] }),
281 Content_type => 'application/json;charset=UTF-8',
282 Authorization => 'Bearer ' . $config::oauth_token
284 die $response->status_line if !$response->is_success;
286 my $im_json = JSON::XS::decode_json($response->decoded_content);
287 die "Something went wrong: " . $response->decoded_content if (!defined($im_json) || !$im_json->{'ok'});
288 my $channel_id = $im_json->{'channel'}{'id'};
290 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!";
292 $response = $ua->post(
293 'https://slack.com/api/chat.postMessage',
294 Content => JSON::XS::encode_json({ channel => $channel_id, text => $msg }),
295 Content_type => 'application/json;charset=UTF-8',
296 Authorization => 'Bearer ' . $config::oauth_token
298 my $msg_json = JSON::XS::decode_json($response->decoded_content);
299 die "Something went wrong: " . $response->decoded_content if (!defined($msg_json) || !$msg_json->{'ok'});
301 # Mark that we've sent the message, so it won't happen again.
302 $dbh->do('INSERT INTO users_nagged (userid, last_nag) VALUES (?, CURRENT_TIMESTAMP)', undef, $userid);
307 skv_log("Siste sync startet: " . POSIX::ctime(time));
309 # Initialize the handles we need for communication.
310 $dbh = DBI->connect("dbi:Pg:dbname=$config::dbname;host=127.0.0.1", $config::dbuser, $config::dbpass, {RaiseError => 1})
311 or die "Could not connect to Postgres: " . DBI->errstr;
312 my $ua = LWP::UserAgent->new('SKVidarLang/1.0');
313 my $token = get_oauth_bearer_token($ua);
315 # Find the newest message, and what it is linked to.
316 # TODO: Support more than one, and test better for errors here.
317 my $q = $dbh->prepare('select * from message_sheet_link where channel=? order by ts desc limit 1');
318 $q->execute($config::invitation_channel);
319 my $linkref = $q->fetchrow_hashref;
320 my $invitation_ts = $linkref->{'ts'};
321 my $wanted_sheet_title = $linkref->{'sheet_title'};
322 die "Could not get newest sheet title" if (!defined($wanted_sheet_title));
324 my ($tab_name, $tab_id) = get_spreadsheet_with_title($ua, $token, $wanted_sheet_title);
325 if (!defined($tab_name)) {
326 skv_log("Fant ikke noen fane med ā$wanted_sheet_titleā i navnet; kan ikke synkronisere.\n");
327 sheet_batch_update($ua, $token, [ serialize_skv_log_to_sheet() ]);
331 # Find everyone who are marked as attending on Slack (via reactions).
332 $q = $dbh->prepare('SELECT DISTINCT userid FROM current_reactions WHERE channel=? AND ts=? AND reaction IN (\'heart\', \'open_mouth\')');
333 $q->execute($config::invitation_channel, $invitation_ts);
334 my @attending_userids = ();
335 while (my $ref = $q->fetchrow_hashref) {
336 push @attending_userids, $ref->{'userid'};
339 # Get the list of all people in the sheet (we're going to need them soon anyway).
340 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',
341 Authorization => 'Bearer ' . $token
343 my $main_sheet_json = JSON::XS::decode_json($response->decoded_content);
345 my %seen_names = find_where_each_name_is($main_sheet_json);
348 for my $name (sort keys %seen_names) {
349 my $seen = $seen_names{$name};
350 if (scalar @$seen >= 2) {
351 my $exemplar = $seen->[0][0];
352 skv_log("Duplikat: $exemplar (" . format_cell_names_for_seen($seen) . ")");
356 # Get our existing Slack->name mapping, from the sheets.
357 my %slack_userid_to_real_name = ();
358 my %slack_userid_to_slack_name = ();
359 my %slack_userid_to_row = ();
360 $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',
361 Authorization => 'Bearer ' . $token
363 my $mapping_sheet_json = JSON::XS::decode_json($response->decoded_content);
364 my $mapping_sheet_rows = $mapping_sheet_json->{'sheets'}[0]{'data'}[0]{'rowData'};
366 for my $row (@$mapping_sheet_rows) {
367 my $slack_id = $row->{'values'}[0]{'userEnteredValue'}{'stringValue'};
368 my $slack_name = $row->{'values'}[1]{'userEnteredValue'}{'stringValue'};
369 my $real_name = get_spreadsheet_name($row->{'values'}[2]); # TODO support more
370 $slack_userid_to_row{$slack_id} = $cur_row++;
371 next if (!defined($slack_name));
372 $slack_userid_to_slack_name{$slack_id} = $slack_name;
373 next if (!defined($real_name));
374 $slack_userid_to_real_name{$slack_id} = $real_name;
377 # See which ones we don't have a mapping for, and look them up in Slack.
378 # TODO: Use an append call instead of $cur_row?
379 my @slack_mapping_updates = ();
380 for my $userid (@attending_userids) {
381 next if (exists($slack_userid_to_real_name{$userid}));
383 # Make sure they have a row in the spreadsheet.
385 if (exists($slack_userid_to_row{$userid})) {
386 $write_row = $slack_userid_to_row{$userid};
388 $write_row = $cur_row++;
389 $slack_userid_to_row{$userid} = $write_row;
390 push @slack_mapping_updates, {
391 range => "Slack-mapping!A$write_row:A$write_row",
392 values => [ [ $userid ]]
396 # Fetch their Slack name if we don't already have it.
398 if (exists($slack_userid_to_slack_name{$userid})) {
399 $slack_name = $slack_userid_to_slack_name{$userid};
401 $slack_userid_to_slack_name{$userid} = $slack_name;
402 $slack_name = get_slack_name($ua, $userid);
403 push @slack_mapping_updates, {
404 range => "Slack-mapping!B$write_row:B$write_row",
405 values => [ [ $slack_name ]]
407 $slack_userid_to_slack_name{$userid} = $slack_name;
410 if (exists($seen_names{lc $slack_name})) {
411 # The name exists exactly, once or more, so it's a direct match and we ignore any fuzz.
412 $slack_userid_to_real_name{$userid} = $slack_name;
413 push @slack_mapping_updates, {
414 range => "Slack-mapping!C$write_row:C$write_row",
415 values => [ [ $slack_name ]]
418 # Do a search through all the available names in the sheet to find an obvious(ish) match.
420 my $main_sheet_rows = $main_sheet_json->{'sheets'}[0]{'data'}[0]{'rowData'};
421 for my $row (@$main_sheet_rows) {
422 for my $val (@{$row->{'values'}}) {
423 my $name = get_spreadsheet_name($val);
424 if (defined($name) && matches_name($slack_name, $name)) {
425 push @candidates, $name;
429 if ($#candidates == -1) {
430 skv_log("$slack_name ($userid) er pƄmeldt pƄ Slack, men fant ikke et regneark-navn for dem.");
431 possibly_nag_user($dbh, $ua, $userid);
432 } elsif ($#candidates == 0) {
433 my $name = $candidates[0];
434 $slack_userid_to_real_name{$userid} = $name;
435 push @slack_mapping_updates, {
436 range => "Slack-mapping!C$write_row:C$write_row",
437 values => [ [ $name ]]
440 skv_log("$slack_name ($userid) er pƄmeldt pƄ Slack, men hadde flere fuzzy-matcher; vet ikke hvilket regneark-navn som skal brukes.");
445 valueInputOption => 'USER_ENTERED',
446 data => \@slack_mapping_updates
448 $response = $ua->post(
449 'https://sheets.googleapis.com/v4/spreadsheets/' . $config::sheet_id . '/values:batchUpdate?key=' . $config::gsheets_api_key,
450 Content => JSON::XS::encode_json($update),
451 Content_type => 'application/json;charset=UTF-8',
452 Authorization => 'Bearer ' . $token
454 die $response->decoded_content if (!$response->is_success);
456 # Find the list of names to mark yellow.
458 my $main_sheet_rows = $main_sheet_json->{'sheets'}[0]{'data'}[0]{'rowData'};
459 for my $userid (@attending_userids) {
460 next if (!exists($slack_userid_to_real_name{$userid}));
461 my $slack_name = $slack_userid_to_slack_name{$userid};
462 my $real_name = $slack_userid_to_real_name{$userid};
464 # See if we can find them in the spreadsheet.
465 if (!exists($seen_names{lc $real_name})) {
466 # TODO: Perhaps move this logic further down, for consistency?
467 skv_log("$slack_name ($userid) er pƄmeldt pƄ Slack, og er mappet til $real_name, men var ikke i noen gruppe.");
469 my $seen = $seen_names{lc $real_name};
470 if (scalar @$seen >= 2) {
471 skv_log("$slack_name ($userid) er pƄmeldt pƄ Slack, men stƄr flere steder (se over); vet ikke hvilken celle som skal brukes.");
473 $want_names{$seen->[0][0]} = 1;
478 # Find the list of names we already marked yellow.
480 $dbh->{AutoCommit} = 0;
481 $dbh->do('SET TRANSACTION ISOLATION LEVEL SERIALIZABLE');
482 $q = $dbh->prepare('SELECT name FROM applied WHERE channel=? AND ts=?');
483 $q->execute($config::invitation_channel, $invitation_ts);
484 while (my $ref = $q->fetchrow_hashref) {
485 $have_names{$ref->{'name'}} = 1;
488 my @diffs = find_diff($dbh, $invitation_ts, \%want_names, \%have_names, \%seen_names);
490 my @yellow_updates = ();
491 if (scalar @diffs > 0) {
492 # Now fill in the actual stuff.
493 for my $diff (@diffs) {
494 my $real_name = $diff->[0];
496 # See if we can find them in the spreadsheet.
497 my $seen = $seen_names{lc $real_name};
498 die if (!defined($seen) || scalar @$seen > 1);
499 my $rowno = $seen->[0][1];
500 my $colno = $seen->[0][2];
501 push @yellow_updates, {
505 userEnteredFormat => $diff->[1]
508 fields => 'userEnteredFormat.backgroundColor',
511 startRowIndex => $rowno,
512 endRowIndex => $rowno + 1,
513 startColumnIndex => $colno,
514 endColumnIndex => $colno + 1
521 my @recent_changes = create_reaction_log($dbh, $invitation_ts, \%slack_userid_to_real_name, \%slack_userid_to_slack_name);
522 push @yellow_updates, {
524 rows => \@recent_changes,
525 fields => 'userEnteredValue.stringValue',
527 sheetId => $config::log_tab_id,
529 endRowIndex => 4 + scalar @recent_changes,
530 startColumnIndex => 0,
536 # Push the final set of updates (including the log).
538 push @yellow_updates, serialize_skv_log_to_sheet();
539 sheet_batch_update($ua, $token, \@yellow_updates);
543 if ($#ARGV >= 0 && $ARGV[0] eq '--daemon') {
545 if (!unlink("/srv/skvidar-slack.sesse.net/marker")) {
546 unless ($!{ENOENT}) {
547 warn "/srv/skvidar-slack.sesse.net/marker: $!";
556 warn "Died with: $@";