11 binmode STDOUT, ':utf8';
12 binmode STDERR, ':utf8';
15 require '../include/config.pm';
42 my ($start, $msg) = @_;
43 my $elapsed = Time::HiRes::tv_interval($start);
44 printf "%s: %.0f ms.\n", $msg, 1e3 * $elapsed;
47 sub get_oauth_bearer_token {
51 # See if the database already has a token we could use, that doesn't expire in a while.
52 my $ref = $dbh->selectrow_hashref('SELECT token FROM oauth_tokens WHERE expiry - (TIMESTAMPTZ \'1970-01-01\' + ? * INTERVAL \'1 second\') > INTERVAL \'1 minute\' ORDER BY expiry DESC LIMIT 1', undef, $now);
53 if (defined($ref->{'token'})) {
54 return $ref->{'token'};
57 my $jwt = JSON::XS::encode_json({
58 "iss" => $config::jwt_key->{'client_email'},
59 "scope" => "https://www.googleapis.com/auth/spreadsheets",
60 "aud" => "https://www.googleapis.com/oauth2/v4/token",
64 my $jws_token = Crypt::JWT::encode_jwt(payload=>$jwt, alg=>'RS256', key=>\$config::jwt_key->{'private_key'});
65 my $start = [Time::HiRes::gettimeofday];
66 my $response = $ua->post('https://www.googleapis.com/oauth2/v4/token', [
67 'grant_type' => 'urn:ietf:params:oauth:grant-type:jwt-bearer',
68 'assertion' => $jws_token ]);
69 log_timing($start, '/oauth2/v4/token');
70 my $token = JSON::XS::decode_json($response->decoded_content)->{'access_token'};
71 $dbh->do('INSERT INTO oauth_tokens (token, acquired, expiry) VALUES (?, TIMESTAMPTZ \'1970-01-01\' + ? * INTERVAL \'1 second\', TIMESTAMPTZ \'1970-01-01\' + ? * INTERVAL \'1 second\')',
72 undef, $token, $now, $now + 1800);
77 my ($ua, $userid) = @_;
78 my $req = HTTP::Request->new('GET', 'https://slack.com/api/users.info?user=' . $userid, [
79 'Authorization' => 'Bearer ' . $config::slack_oauth_token
81 my $start = [Time::HiRes::gettimeofday];
82 my $response = $ua->request($req);
83 log_timing($start, '/users.info');
84 die $response->status_line if !$response->is_success;
86 my $user_json = JSON::XS::decode_json($response->decoded_content);
87 die "Something went wrong: " . $response->decoded_content if (!defined($user_json) || !$user_json->{'ok'});
89 return $user_json->{'user'}{'real_name'};
92 sub get_spreadsheet_name {
94 my $name = $cell->{'userEnteredValue'}{'stringValue'};
95 return undef if (!defined($name));
96 return undef if ($name =~ /^G[1-4]\.[1-5]/);
101 $name =~ s/G\d\.\d?\??//;
109 my ($slack_name, $spreadsheet_name) = @_;
110 if (lc($slack_name) eq lc($spreadsheet_name)) {
114 my @ap = split /\s+/, $slack_name;
115 my @bp = split /\s+/, $spreadsheet_name;
116 if (scalar @ap >= 2 && scalar @bp >= 2 && lc($ap[0]) eq lc($bp[0])) {
117 # First name matches, try to match some surname
119 for my $ai (1..$#ap) {
120 for my $bi (1..$#bp) {
121 $found = 1 if (lc($ap[$ai]) eq lc($bp[$bi]));
125 skv_log("Fuzzy-matchet $slack_name -> $spreadsheet_name.");
133 sub format_cell_names_for_seen {
135 my @cells = map { chr(ord('A') + $_->[2]) . ($_->[1] + 1) } @$seen;
136 return join(', ', @cells);
141 print STDERR "$msg\n";
145 sub serialize_skv_log_to_sheet {
150 userEnteredValue => { stringValue => join("\n", @log) }
153 fields => 'userEnteredValue.stringValue',
155 sheetId => $config::log_tab_id,
158 startColumnIndex => 0,
165 sub sheet_batch_update {
166 my ($ua, $token, @requests) = @_;
168 requests => \@requests
170 my $start = [Time::HiRes::gettimeofday];
171 my $response = $ua->post(
172 'https://sheets.googleapis.com/v4/spreadsheets/' . $config::sheet_id . ':batchUpdate?key=' . $config::gsheets_api_key,
173 Content => JSON::XS::encode_json($update),
174 Content_type => 'application/json;charset=UTF-8',
175 Authorization => 'Bearer ' . $token
177 log_timing($start, '/spreadsheets/values:batchUpdate');
178 die $response->decoded_content if !$response->is_success;
181 sub get_group_assignments {
184 my %assignments = ();
185 my $rows = $json->{'sheets'}[0]{'data'}[0]{'rowData'};
186 my @curr_groups = ();
187 for my $row (@$rows) {
189 for my $val (@{$row->{'values'}}) {
191 my $contents = $val->{'userEnteredValue'}{'stringValue'};
192 next if !defined($contents);
193 if ($contents =~ /Gruppe /) {
197 next if $contents =~ /^VL:/;
198 next if $contents =~ /^LT\b/;
199 next if $contents =~ /^400m/;
200 next if $contents =~ /^546m/;
201 if ($contents =~ /^(G\d\.\d)/ || $contents =~ /^(Nye lĆøpere.*)/) {
202 $curr_groups[$col] = $1;
204 my $name = get_spreadsheet_name($val);
205 next if (!defined($name));
206 my $group = $curr_groups[$col] // $curr_groups[$col - 1];
207 # print $group, " ", $name, "\n";
208 if (exists($assignments{$name})) {
209 $assignments{$name} = "(flere grupper)";
211 $assignments{$name} = $group;
219 sub update_assignment_db {
220 my ($dbh, $channel, $ts, $assignments) = @_;
222 local $dbh->{AutoCommit} = 0;
223 my %db_assignments = ();
224 my $q = $dbh->prepare('SELECT name,group_name FROM current_group_membership_history WHERE channel=? AND ts=?');
225 $q->execute($channel, $ts);
226 while (my $ref = $q->fetchrow_hashref) {
227 if (defined($ref->{'group_name'})) {
228 $db_assignments{$ref->{'name'}} = $ref->{'group_name'};
232 $q = $dbh->prepare('INSERT INTO group_membership_history (channel, ts, name, change_seen, group_name) VALUES (?, ?, ?, CURRENT_TIMESTAMP, ?)');
233 for my $name (keys %$assignments) {
234 if (!exists($db_assignments{$name}) || $db_assignments{$name} ne $assignments->{$name}) {
235 $q->execute($channel, $ts, $name, $assignments->{$name});
238 for my $name (keys %db_assignments) {
239 if (!exists($assignments->{$name})) {
240 $q->execute($channel, $ts, $name, undef);
246 sub get_spreadsheet_with_title {
247 my ($ua, $token, $wanted_sheet_title) = @_;
249 # See if we have any spreadsheets that match this title.
250 my $start = [Time::HiRes::gettimeofday];
251 my $response = $ua->get('https://sheets.googleapis.com/v4/spreadsheets/' . $config::sheet_id . '?key=' . $config::gsheets_api_key . '&fields=sheets/properties',
252 Authorization => 'Bearer ' . $token
254 log_timing($start, '/v4/spreadsheets/properties');
255 my $sheets_json = JSON::XS::decode_json($response->decoded_content);
256 my ($tab_name, $tab_id);
257 for my $sheet (@{$sheets_json->{'sheets'}}) {
258 my $title = $sheet->{'properties'}{'title'};
259 my $sheet_id = $sheet->{'properties'}{'sheetId'};
260 if ($title =~ /\Q$wanted_sheet_title\E/) {
261 # skv_log("Synkroniserer ($config::invitation_channel, $invitation_ts) mot arket ā$titleā (fane-ID $sheet_id).");
262 return ($title, $sheet_id);
265 return (undef, undef);
268 # Make a mapping of lowercase name -> list of [canonical name, row number, column number]
269 sub find_where_each_name_is {
273 my $rows = $json->{'sheets'}[0]{'data'}[0]{'rowData'};
275 for my $row (@$rows) {
277 for my $val (@{$row->{'values'}}) {
278 my $name = get_spreadsheet_name($val);
279 if (defined($name)) {
280 push @{$seen_names{lc $name}}, [$name, $rowno, $colno];
290 sub best_name_for_log {
291 my ($userid, $slack_userid_to_real_name, $slack_userid_to_slack_name) = @_;
292 if (exists($slack_userid_to_real_name->{$userid})) {
293 return $slack_userid_to_real_name->{$userid};
294 } elsif (exists($slack_userid_to_slack_name->{$userid})) {
295 return $slack_userid_to_slack_name->{$userid} . ' (fant ikke regneark-navn)';
297 # Should only happen if we didn't see the initial reaction_add, only reaction_remove.
298 # (TODO: Is the comment above true anymore, now that we use this from multiple contexts?)
299 return $userid . ' (fant ikke Slack-navn)';
303 # Add the reaction log. (This only takes into account the last change
304 # for each user; earlier ones are irrelevant and don't count. But it
305 # doesn't deduplicate across reactions. Meh.)
306 sub create_reaction_log {
307 my ($dbh, $invitation_ts, $slack_userid_to_real_name, $slack_userid_to_slack_name) = @_;
309 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');
310 $q->execute($config::invitation_channel, $invitation_ts);
311 my @recent_changes = ();
312 while (my $ref = $q->fetchrow_hashref) {
313 my $msg = $ref->{'event_ts'};
314 if ($ref->{'event_type'} eq 'reaction_added') {
319 if ($ref->{'reaction'} eq 'open_mouth') {
321 } elsif ($ref->{'reaction'} eq 'blue_heart') {
327 $msg .= best_name_for_log($ref->{'userid'}, $slack_userid_to_real_name, $slack_userid_to_slack_name);
328 push @recent_changes, { values => [{ userEnteredValue => { stringValue => $msg } }] };
330 while (scalar @recent_changes < 50) {
331 push @recent_changes, { values => [{ userEnteredValue => { stringValue => '' } }] };
333 return @recent_changes;
336 sub create_move_log {
337 my ($dbh, $invitation_ts, $prev_invitation_ts) = @_;
338 my $q = $dbh->prepare(<<"EOF");
340 name, g_old.group_name as old_group, g_new.group_name as new_group, TO_CHAR(g_new.change_seen, \'YYYY-mm-dd HH24:MI\') AS change_seen
341 FROM ( SELECT * FROM current_group_membership_history WHERE ts=? ) g_old
342 FULL OUTER JOIN ( SELECT * FROM current_group_membership_history WHERE ts=? ) g_new USING (name)
344 g_new.group_name IS DISTINCT FROM g_old.group_name
345 AND g_new.group_name IS NOT NULL
346 ORDER BY g_new.change_seen DESC, name
349 $q->execute($prev_invitation_ts, $invitation_ts);
350 my @recent_moves = ();
351 while (my $ref = $q->fetchrow_hashref) {
352 my $name = $ref->{'name'};
353 my $old_group = $ref->{'old_group'};
354 my $new_group = $ref->{'new_group'};
356 my $msg = $ref->{'change_seen'} . " ";
357 if (!defined($old_group)) {
358 $msg .= "$name, (ny lĆøper) ā $new_group";
360 $msg .= "$name, $old_group ā $new_group";
362 push @recent_moves, { values => [{ userEnteredValue => { stringValue => $msg } }] };
364 while (scalar @recent_moves < 50) {
365 push @recent_moves, { values => [{ userEnteredValue => { stringValue => '' } }] };
367 return @recent_moves;
370 # Also applies the diff to the database (a bit ugly).
372 my ($dbh, $invitation_ts, $want_colors, $have_colors, $seen_names) = @_;
375 for my $real_name (keys %$want_colors) {
376 my $wc = $want_colors->{$real_name};
377 if (exists($have_colors->{$real_name})) {
378 if ($have_colors->{$real_name} eq $wc) {
382 skv_log("Markerer at $real_name har byttet treningssted.");
384 $real_name, { backgroundColor => $rgb{$wc} }
386 $dbh->do('UPDATE applied SET color=? WHERE channel=? AND ts=? AND name=?', undef,
387 $wc, $config::invitation_channel, $invitation_ts, $real_name);
389 skv_log("Markerer at $real_name skal pƄ trening.");
391 $real_name, { backgroundColor => $rgb{$wc} }
393 $dbh->do('INSERT INTO applied (channel, ts, name, color) VALUES (?, ?, ?, ?)', undef,
394 $config::invitation_channel, $invitation_ts, $real_name, $wc);
397 for my $real_name (keys %$have_colors) {
398 next if (exists($want_colors->{$real_name}));
399 if (!exists($seen_names->{lc $real_name})) {
400 # TODO: This can somehow come if we try to add someone who's not in the sheet, too?
401 skv_log("Ćnsket Ć„ fjerne at $real_name skulle pĆ„ trening, men de var ikke i regnearket lenger.");
402 } elsif (scalar @{$seen_names->{lc $real_name}} > 1) {
405 skv_log("Fjerner at $real_name skal pƄ trening.");
407 $real_name, { backgroundColor => $rgb{white} }
409 $dbh->do('DELETE FROM applied WHERE channel=? AND ts=? AND name=?', undef,
410 $config::invitation_channel, $invitation_ts, $real_name);
416 sub possibly_nag_user {
417 my ($dbh, $ua, $userid) = @_;
419 # See if we've nagged this user before.
420 my $q = $dbh->prepare('SELECT * FROM users_nagged WHERE userid=?');
421 $q->execute($userid);
422 if (defined($q->fetchrow_hashref)) {
426 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!";
428 skv_log("Sender melding til $userid for Ć„ spĆørre om gruppe.");
430 channel => $config::invitation_channel,
434 my $start = [Time::HiRes::gettimeofday];
435 my $response = $ua->post(
436 'https://slack.com/api/chat.postEphemeral',
437 Content => JSON::XS::encode_json($content),
438 Content_type => 'application/json;charset=UTF-8',
439 Authorization => 'Bearer ' . $config::slack_oauth_token
441 log_timing($start, 'chat.postEphemeral');
442 die $response->status_line if !$response->is_success;
443 my $msg_json = JSON::XS::decode_json($response->decoded_content);
444 die "Something went wrong: " . $response->decoded_content if (!defined($msg_json) || !$msg_json->{'ok'});
446 # Mark that we've sent the message, so it won't happen again.
447 $dbh->do('INSERT INTO users_nagged (userid, last_nag) VALUES (?, CURRENT_TIMESTAMP)', undef, $userid);
451 my $total_start = [Time::HiRes::gettimeofday];
454 skv_log("Siste sync startet: " . POSIX::ctime(time));
456 # Initialize the handles we need for communication.
457 $dbh = DBI->connect("dbi:Pg:dbname=$config::dbname;host=127.0.0.1", $config::dbuser, $config::dbpass, {RaiseError => 1})
458 or die "Could not connect to Postgres: " . DBI->errstr;
459 my $ua = LWP::UserAgent->new('SKVidarLang/1.0');
460 my $token = get_oauth_bearer_token($dbh, $ua);
462 # Find the newest message, what it is linked to, and what was the one before it (for group diffing).
463 # TODO: Support more than one, and test better for errors here.
464 my $q = $dbh->prepare('select * from message_sheet_link where channel=? order by ts desc limit 2');
465 $q->execute($config::invitation_channel);
466 my $linkref = $q->fetchrow_hashref;
467 my $invitation_ts = $linkref->{'ts'};
468 my $wanted_sheet_title = $linkref->{'sheet_title'};
469 die "Could not get newest sheet title" if (!defined($wanted_sheet_title));
471 my ($tab_name, $tab_id) = get_spreadsheet_with_title($ua, $token, $wanted_sheet_title);
472 if (!defined($tab_name)) {
473 skv_log("Fant ikke noen fane med Ā«$wanted_sheet_titleĀ» i navnet; kan ikke synkronisere.\n");
474 sheet_batch_update($ua, $token, [ serialize_skv_log_to_sheet() ]);
478 # Store away the second-newest ID.
479 my $prev_invitation_ts = $q->fetchrow_hashref->{'ts'};
481 # Find everyone who are marked as attending on Slack (via reactions).
482 $q = $dbh->prepare('SELECT DISTINCT userid,reaction FROM current_reactions WHERE channel=? AND ts=? AND reaction IN (\'heart\', \'open_mouth\', \'blue_heart\')');
483 $q->execute($config::invitation_channel, $invitation_ts);
484 my @attending_userids = ();
487 while (my $ref = $q->fetchrow_hashref) {
488 my $userid = $ref->{'userid'};
489 push @attending_userids, $userid;
490 if ($ref->{'reaction'} eq 'blue_heart') {
491 if (exists($colors{$userid}) && $colors{$userid} eq 'yellow') {
492 $double{$userid} = 1;
494 $colors{$userid} = 'blue';
496 if (exists($colors{$userid}) && $colors{$userid} eq 'blue') {
497 $double{$userid} = 1;
499 $colors{$userid} = 'yellow';
503 # Remove double-attenders (we will log them as warnings further down).
504 @attending_userids = grep { !exists($double{$_}) } @attending_userids;
505 for my $userid (keys %double) {
506 delete $colors{$userid};
509 # Get the list of all people in the sheet (we're going to need them soon anyway).
510 my $start = [Time::HiRes::gettimeofday];
511 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',
512 Authorization => 'Bearer ' . $token
514 log_timing($start, "/spreadsheets/$tab_name");
515 my $main_sheet_json = JSON::XS::decode_json($response->decoded_content);
517 # Update the list of groups we've seen people in.
518 my %assignments = get_group_assignments($main_sheet_json);
519 update_assignment_db($dbh, $config::invitation_channel, $invitation_ts, \%assignments);
521 my %seen_names = find_where_each_name_is($main_sheet_json);
524 for my $name (sort keys %seen_names) {
525 my $seen = $seen_names{$name};
526 if (scalar @$seen >= 2) {
527 my $exemplar = $seen->[0][0];
528 skv_log("Duplikat: $exemplar (" . format_cell_names_for_seen($seen) . ")");
532 # Get our existing Slack->name mapping, from the sheets.
533 my %slack_userid_to_real_name = ();
534 my %slack_userid_to_slack_name = ();
535 my %slack_userid_to_row = ();
537 $start = [Time::HiRes::gettimeofday];
538 $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',
539 Authorization => 'Bearer ' . $token
541 log_timing($start, "/spreadsheets/Slack-mapping");
542 my $mapping_sheet_json = JSON::XS::decode_json($response->decoded_content);
543 my $mapping_sheet_rows = $mapping_sheet_json->{'sheets'}[0]{'data'}[0]{'rowData'};
545 for my $row (@$mapping_sheet_rows) {
546 my $slack_id = $row->{'values'}[0]{'userEnteredValue'}{'stringValue'};
547 my $slack_name = $row->{'values'}[1]{'userEnteredValue'}{'stringValue'};
548 my $real_name = get_spreadsheet_name($row->{'values'}[2]); # TODO support more
549 $slack_userid_to_row{$slack_id} = $cur_row++;
550 next if (!defined($slack_name));
551 $slack_userid_to_slack_name{$slack_id} = $slack_name;
552 next if (!defined($real_name));
553 $slack_userid_to_real_name{$slack_id} = $real_name;
556 # See which ones we don't have a mapping for, and look them up in Slack.
557 # TODO: Use an append call instead of $cur_row?
558 my @slack_mapping_updates = ();
559 for my $userid (@attending_userids) {
560 next if (exists($slack_userid_to_real_name{$userid}));
562 # Make sure they have a row in the spreadsheet.
564 if (exists($slack_userid_to_row{$userid})) {
565 $write_row = $slack_userid_to_row{$userid};
567 $write_row = $cur_row++;
568 $slack_userid_to_row{$userid} = $write_row;
569 push @slack_mapping_updates, {
570 range => "Slack-mapping!A$write_row:A$write_row",
571 values => [ [ $userid ]]
575 # Fetch their Slack name if we don't already have it.
577 if (exists($slack_userid_to_slack_name{$userid})) {
578 $slack_name = $slack_userid_to_slack_name{$userid};
580 $slack_userid_to_slack_name{$userid} = $slack_name;
581 $slack_name = get_slack_name($ua, $userid);
582 push @slack_mapping_updates, {
583 range => "Slack-mapping!B$write_row:B$write_row",
584 values => [ [ $slack_name ]]
586 $slack_userid_to_slack_name{$userid} = $slack_name;
589 if (exists($seen_names{lc $slack_name})) {
590 # The name exists exactly, once or more, so it's a direct match and we ignore any fuzz.
591 $slack_userid_to_real_name{$userid} = $slack_name;
592 push @slack_mapping_updates, {
593 range => "Slack-mapping!C$write_row:C$write_row",
594 values => [ [ $slack_name ]]
597 # Do a search through all the available names in the sheet to find an obvious(ish) match.
599 my $main_sheet_rows = $main_sheet_json->{'sheets'}[0]{'data'}[0]{'rowData'};
600 for my $row (@$main_sheet_rows) {
601 for my $val (@{$row->{'values'}}) {
602 my $name = get_spreadsheet_name($val);
603 if (defined($name) && matches_name($slack_name, $name)) {
604 push @candidates, $name;
608 if ($#candidates == -1) {
609 skv_log("$slack_name ($userid) er pƄmeldt pƄ Slack, men fant ikke et regneark-navn for dem.");
610 possibly_nag_user($dbh, $ua, $userid);
611 } elsif ($#candidates == 0) {
612 my $name = $candidates[0];
613 $slack_userid_to_real_name{$userid} = $name;
614 push @slack_mapping_updates, {
615 range => "Slack-mapping!C$write_row:C$write_row",
616 values => [ [ $name ]]
619 skv_log("$slack_name ($userid) er pƄmeldt pƄ Slack, men hadde flere fuzzy-matcher; vet ikke hvilket regneark-navn som skal brukes.");
623 if (scalar @slack_mapping_updates > 0) {
625 valueInputOption => 'USER_ENTERED',
626 data => \@slack_mapping_updates
628 $start = [Time::HiRes::gettimeofday];
629 $response = $ua->post(
630 'https://sheets.googleapis.com/v4/spreadsheets/' . $config::sheet_id . '/values:batchUpdate?key=' . $config::gsheets_api_key,
631 Content => JSON::XS::encode_json($update),
632 Content_type => 'application/json;charset=UTF-8',
633 Authorization => 'Bearer ' . $token
635 log_timing($start, "/spreadsheets/values:batchUpdate");
636 die $response->decoded_content if (!$response->is_success);
639 # Now that we have Slack names, we can log double-reacters.
640 for my $userid (keys %double) {
641 my $name = best_name_for_log($userid, \%slack_userid_to_real_name, \%slack_userid_to_slack_name);
642 skv_log("$name er pƄmeldt flere steder pƄ Slack; vet ikke hvilken som skal brukes.");
645 # Find the list of names to mark yellow.
646 my %want_colors = ();
647 my $main_sheet_rows = $main_sheet_json->{'sheets'}[0]{'data'}[0]{'rowData'};
648 for my $userid (@attending_userids) {
649 next if (!exists($slack_userid_to_real_name{$userid}));
650 my $slack_name = $slack_userid_to_slack_name{$userid};
651 my $real_name = $slack_userid_to_real_name{$userid};
653 # See if we can find them in the spreadsheet.
654 if (!exists($seen_names{lc $real_name})) {
655 # TODO: Perhaps move this logic further down, for consistency?
656 skv_log("$slack_name ($userid) er pƄmeldt pƄ Slack, og er mappet til $real_name, men var ikke i noen gruppe.");
658 my $seen = $seen_names{lc $real_name};
659 if (scalar @$seen >= 2) {
660 skv_log("$slack_name ($userid) er pƄmeldt pƄ Slack, men stƄr flere steder (se over); vet ikke hvilken celle som skal brukes.");
662 $want_colors{$seen->[0][0]} = $colors{$userid};
667 # Find the list of names we already marked yellow.
668 my %have_colors = ();
669 $dbh->{AutoCommit} = 0;
670 $dbh->do('SET TRANSACTION ISOLATION LEVEL SERIALIZABLE');
671 $q = $dbh->prepare('SELECT name,color FROM applied WHERE channel=? AND ts=?');
672 $q->execute($config::invitation_channel, $invitation_ts);
673 while (my $ref = $q->fetchrow_hashref) {
674 $have_colors{$ref->{'name'}} = $ref->{'color'};
677 my @diffs = find_diff($dbh, $invitation_ts, \%want_colors, \%have_colors, \%seen_names);
679 my @yellow_updates = ();
680 if (scalar @diffs > 0) {
681 # Now fill in the actual stuff.
682 for my $diff (@diffs) {
683 my $real_name = $diff->[0];
685 my $seen = $seen_names{lc $real_name};
687 # We've already complained about these earlier, so just skip them silently.
688 next if (scalar @$seen > 1);
690 # See if we can find them in the spreadsheet.
691 die "Could not find $real_name" if (!defined($seen));
692 my $rowno = $seen->[0][1];
693 my $colno = $seen->[0][2];
694 push @yellow_updates, {
698 userEnteredFormat => $diff->[1]
701 fields => 'userEnteredFormat.backgroundColor',
704 startRowIndex => $rowno,
705 endRowIndex => $rowno + 1,
706 startColumnIndex => $colno,
707 endColumnIndex => $colno + 1
714 my @recent_changes = create_reaction_log($dbh, $invitation_ts, \%slack_userid_to_real_name, \%slack_userid_to_slack_name);
715 push @yellow_updates, {
717 rows => \@recent_changes,
718 fields => 'userEnteredValue.stringValue',
720 sheetId => $config::log_tab_id,
722 endRowIndex => 4 + scalar @recent_changes,
723 startColumnIndex => 0,
729 my @recent_moves = create_move_log($dbh, $invitation_ts, $prev_invitation_ts);
730 push @yellow_updates, {
732 rows => \@recent_moves,
733 fields => 'userEnteredValue.stringValue',
735 sheetId => $config::log_tab_id,
737 endRowIndex => 4 + scalar @recent_moves,
738 startColumnIndex => 1,
744 # Push the final set of updates (including the log).
746 push @yellow_updates, serialize_skv_log_to_sheet();
747 sheet_batch_update($ua, $token, \@yellow_updates);
750 my $elapsed = Time::HiRes::tv_interval($total_start);
751 printf "Tok %.0f ms.\n", 1e3 * $elapsed;
754 if ($#ARGV >= 0 && $ARGV[0] eq '--daemon') {
755 # Start with a single, forced run.
756 unlink("/srv/skvidar-slack.sesse.net/marker");
760 if (!unlink("/srv/skvidar-slack.sesse.net/marker")) {
761 unless ($!{ENOENT}) {
762 warn "/srv/skvidar-slack.sesse.net/marker: $!";
771 warn "Died with: $@";