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 {
50 my $jwt = JSON::XS::encode_json({
51 "iss" => $config::jwt_key->{'client_email'},
52 "scope" => "https://www.googleapis.com/auth/spreadsheets",
53 "aud" => "https://www.googleapis.com/oauth2/v4/token",
57 my $jws_token = Crypt::JWT::encode_jwt(payload=>$jwt, alg=>'RS256', key=>\$config::jwt_key->{'private_key'});
58 my $start = [Time::HiRes::gettimeofday];
59 my $response = $ua->post('https://www.googleapis.com/oauth2/v4/token', [
60 'grant_type' => 'urn:ietf:params:oauth:grant-type:jwt-bearer',
61 'assertion' => $jws_token ]);
62 log_timing($start, '/oauth2/v4/token');
63 return JSON::XS::decode_json($response->decoded_content)->{'access_token'};
67 my ($ua, $userid) = @_;
68 my $req = HTTP::Request->new('GET', 'https://slack.com/api/users.info?user=' . $userid, [
69 'Authorization' => 'Bearer ' . $config::slack_oauth_token
71 my $start = [Time::HiRes::gettimeofday];
72 my $response = $ua->request($req);
73 log_timing($start, '/users.info');
74 die $response->status_line if !$response->is_success;
76 my $user_json = JSON::XS::decode_json($response->decoded_content);
77 die "Something went wrong: " . $response->decoded_content if (!defined($user_json) || !$user_json->{'ok'});
79 return $user_json->{'user'}{'real_name'};
82 sub get_spreadsheet_name {
84 my $name = $cell->{'userEnteredValue'}{'stringValue'};
85 return undef if (!defined($name));
86 return undef if ($name =~ /^G[1-4]\.[1-5]/);
91 $name =~ s/G\d\.\d?\??//;
99 my ($slack_name, $spreadsheet_name) = @_;
100 if (lc($slack_name) eq lc($spreadsheet_name)) {
104 my @ap = split /\s+/, $slack_name;
105 my @bp = split /\s+/, $spreadsheet_name;
106 if (scalar @ap >= 2 && scalar @bp >= 2 && lc($ap[0]) eq lc($bp[0])) {
107 # First name matches, try to match some surname
109 for my $ai (1..$#ap) {
110 for my $bi (1..$#bp) {
111 $found = 1 if (lc($ap[$ai]) eq lc($bp[$bi]));
115 skv_log("Fuzzy-matchet $slack_name -> $spreadsheet_name.");
123 sub format_cell_names_for_seen {
125 my @cells = map { chr(ord('A') + $_->[2]) . ($_->[1] + 1) } @$seen;
126 return join(', ', @cells);
131 print STDERR "$msg\n";
135 sub serialize_skv_log_to_sheet {
140 userEnteredValue => { stringValue => join("\n", @log) }
143 fields => 'userEnteredValue.stringValue',
145 sheetId => $config::log_tab_id,
148 startColumnIndex => 0,
155 sub sheet_batch_update {
156 my ($ua, $token, @requests) = @_;
158 requests => \@requests
160 my $start = [Time::HiRes::gettimeofday];
161 my $response = $ua->post(
162 'https://sheets.googleapis.com/v4/spreadsheets/' . $config::sheet_id . ':batchUpdate?key=' . $config::gsheets_api_key,
163 Content => JSON::XS::encode_json($update),
164 Content_type => 'application/json;charset=UTF-8',
165 Authorization => 'Bearer ' . $token
167 log_timing($start, '/spreadsheets/values:batchUpdate');
168 die $response->decoded_content if !$response->is_success;
171 sub get_group_assignments {
174 my %assignments = ();
175 my $rows = $json->{'sheets'}[0]{'data'}[0]{'rowData'};
176 my @curr_groups = ();
177 for my $row (@$rows) {
179 for my $val (@{$row->{'values'}}) {
181 my $contents = $val->{'userEnteredValue'}{'stringValue'};
182 next if !defined($contents);
183 if ($contents =~ /Gruppe /) {
187 next if $contents =~ /^VL:/;
188 next if $contents =~ /^LT\b/;
189 next if $contents =~ /^400m/;
190 next if $contents =~ /^546m/;
191 if ($contents =~ /^(G\d\.\d)/ || $contents =~ /^(Nye lĆøpere.*)/) {
192 $curr_groups[$col] = $1;
194 my $name = get_spreadsheet_name($val);
195 next if (!defined($name));
196 my $group = $curr_groups[$col] // $curr_groups[$col - 1];
197 # print $group, " ", $name, "\n";
198 if (exists($assignments{$name})) {
199 $assignments{$name} = "(flere grupper)";
201 $assignments{$name} = $group;
209 sub update_assignment_db {
210 my ($dbh, $channel, $ts, $assignments) = @_;
212 local $dbh->{AutoCommit} = 0;
213 my %db_assignments = ();
214 my $q = $dbh->prepare('SELECT name,group_name FROM current_group_membership_history WHERE channel=? AND ts=?');
215 $q->execute($channel, $ts);
216 while (my $ref = $q->fetchrow_hashref) {
217 if (defined($ref->{'group_name'})) {
218 $db_assignments{$ref->{'name'}} = $ref->{'group_name'};
222 $q = $dbh->prepare('INSERT INTO group_membership_history (channel, ts, name, change_seen, group_name) VALUES (?, ?, ?, CURRENT_TIMESTAMP, ?)');
223 for my $name (keys %$assignments) {
224 if (!exists($db_assignments{$name}) || $db_assignments{$name} ne $assignments->{$name}) {
225 $q->execute($channel, $ts, $name, $assignments->{$name});
228 for my $name (keys %db_assignments) {
229 if (!exists($assignments->{$name})) {
230 $q->execute($channel, $ts, $name, undef);
236 sub get_spreadsheet_with_title {
237 my ($ua, $token, $wanted_sheet_title) = @_;
239 # See if we have any spreadsheets that match this title.
240 my $start = [Time::HiRes::gettimeofday];
241 my $response = $ua->get('https://sheets.googleapis.com/v4/spreadsheets/' . $config::sheet_id . '?key=' . $config::gsheets_api_key . '&fields=sheets/properties',
242 Authorization => 'Bearer ' . $token
244 log_timing($start, '/v4/spreadsheets/properties');
245 my $sheets_json = JSON::XS::decode_json($response->decoded_content);
246 my ($tab_name, $tab_id);
247 for my $sheet (@{$sheets_json->{'sheets'}}) {
248 my $title = $sheet->{'properties'}{'title'};
249 my $sheet_id = $sheet->{'properties'}{'sheetId'};
250 if ($title =~ /\Q$wanted_sheet_title\E/) {
251 # skv_log("Synkroniserer ($config::invitation_channel, $invitation_ts) mot arket ā$titleā (fane-ID $sheet_id).");
252 return ($title, $sheet_id);
255 return (undef, undef);
258 # Make a mapping of lowercase name -> list of [canonical name, row number, column number]
259 sub find_where_each_name_is {
263 my $rows = $json->{'sheets'}[0]{'data'}[0]{'rowData'};
265 for my $row (@$rows) {
267 for my $val (@{$row->{'values'}}) {
268 my $name = get_spreadsheet_name($val);
269 if (defined($name)) {
270 push @{$seen_names{lc $name}}, [$name, $rowno, $colno];
280 sub best_name_for_log {
281 my ($userid, $slack_userid_to_real_name, $slack_userid_to_slack_name) = @_;
282 if (exists($slack_userid_to_real_name->{$userid})) {
283 return $slack_userid_to_real_name->{$userid};
284 } elsif (exists($slack_userid_to_slack_name->{$userid})) {
285 return $slack_userid_to_slack_name->{$userid} . ' (fant ikke regneark-navn)';
287 # Should only happen if we didn't see the initial reaction_add, only reaction_remove.
288 # (TODO: Is the comment above true anymore, now that we use this from multiple contexts?)
289 return $userid . ' (fant ikke Slack-navn)';
293 # Add the reaction log. (This only takes into account the last change
294 # for each user; earlier ones are irrelevant and don't count. But it
295 # doesn't deduplicate across reactions. Meh.)
296 sub create_reaction_log {
297 my ($dbh, $invitation_ts, $slack_userid_to_real_name, $slack_userid_to_slack_name) = @_;
299 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');
300 $q->execute($config::invitation_channel, $invitation_ts);
301 my @recent_changes = ();
302 while (my $ref = $q->fetchrow_hashref) {
303 my $msg = $ref->{'event_ts'};
304 if ($ref->{'event_type'} eq 'reaction_added') {
309 if ($ref->{'reaction'} eq 'open_mouth') {
311 } elsif ($ref->{'reaction'} eq 'blue_heart') {
317 $msg .= best_name_for_log($ref->{'userid'}, $slack_userid_to_real_name, $slack_userid_to_slack_name);
318 push @recent_changes, { values => [{ userEnteredValue => { stringValue => $msg } }] };
320 while (scalar @recent_changes < 50) {
321 push @recent_changes, { values => [{ userEnteredValue => { stringValue => '' } }] };
323 return @recent_changes;
326 sub create_move_log {
327 my ($dbh, $invitation_ts, $prev_invitation_ts) = @_;
328 my $q = $dbh->prepare(<<"EOF");
330 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
331 FROM ( SELECT * FROM current_group_membership_history WHERE ts=? ) g_old
332 FULL OUTER JOIN ( SELECT * FROM current_group_membership_history WHERE ts=? ) g_new USING (name)
334 g_new.group_name IS DISTINCT FROM g_old.group_name
335 AND g_new.group_name IS NOT NULL
336 ORDER BY g_new.change_seen DESC, name
339 $q->execute($prev_invitation_ts, $invitation_ts);
340 my @recent_moves = ();
341 while (my $ref = $q->fetchrow_hashref) {
342 my $name = $ref->{'name'};
343 my $old_group = $ref->{'old_group'};
344 my $new_group = $ref->{'new_group'};
346 my $msg = $ref->{'change_seen'} . " ";
347 if (!defined($old_group)) {
348 $msg .= "$name, (ny lĆøper) ā $new_group";
350 $msg .= "$name, $old_group ā $new_group";
352 push @recent_moves, { values => [{ userEnteredValue => { stringValue => $msg } }] };
354 while (scalar @recent_moves < 50) {
355 push @recent_moves, { values => [{ userEnteredValue => { stringValue => '' } }] };
357 return @recent_moves;
360 # Also applies the diff to the database (a bit ugly).
362 my ($dbh, $invitation_ts, $want_colors, $have_colors, $seen_names) = @_;
365 for my $real_name (keys %$want_colors) {
366 my $wc = $want_colors->{$real_name};
367 if (exists($have_colors->{$real_name})) {
368 if ($have_colors->{$real_name} eq $wc) {
372 skv_log("Markerer at $real_name har byttet treningssted.");
374 $real_name, { backgroundColor => $rgb{$wc} }
376 $dbh->do('UPDATE applied SET color=? WHERE channel=? AND ts=? AND name=?', undef,
377 $wc, $config::invitation_channel, $invitation_ts, $real_name);
379 skv_log("Markerer at $real_name skal pƄ trening.");
381 $real_name, { backgroundColor => $rgb{$wc} }
383 $dbh->do('INSERT INTO applied (channel, ts, name, color) VALUES (?, ?, ?, ?)', undef,
384 $config::invitation_channel, $invitation_ts, $real_name, $wc);
387 for my $real_name (keys %$have_colors) {
388 next if (exists($want_colors->{$real_name}));
389 if (!exists($seen_names->{lc $real_name})) {
390 # TODO: This can somehow come if we try to add someone who's not in the sheet, too?
391 skv_log("Ćnsket Ć„ fjerne at $real_name skulle pĆ„ trening, men de var ikke i regnearket lenger.");
392 } elsif (scalar @{$seen_names->{lc $real_name}} > 1) {
395 skv_log("Fjerner at $real_name skal pƄ trening.");
397 $real_name, { backgroundColor => $rgb{white} }
399 $dbh->do('DELETE FROM applied WHERE channel=? AND ts=? AND name=?', undef,
400 $config::invitation_channel, $invitation_ts, $real_name);
406 sub possibly_nag_user {
407 my ($dbh, $ua, $userid) = @_;
409 # See if we've nagged this user before.
410 my $q = $dbh->prepare('SELECT * FROM users_nagged WHERE userid=?');
411 $q->execute($userid);
412 if (defined($q->fetchrow_hashref)) {
416 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!";
418 skv_log("Sender melding til $userid for Ć„ spĆørre om gruppe.");
420 channel => $config::invitation_channel,
424 my $start = [Time::HiRes::gettimeofday];
425 my $response = $ua->post(
426 'https://slack.com/api/chat.postEphemeral',
427 Content => JSON::XS::encode_json($content),
428 Content_type => 'application/json;charset=UTF-8',
429 Authorization => 'Bearer ' . $config::slack_oauth_token
431 log_timing($start, 'chat.postEphemeral');
432 die $response->status_line if !$response->is_success;
433 my $msg_json = JSON::XS::decode_json($response->decoded_content);
434 die "Something went wrong: " . $response->decoded_content if (!defined($msg_json) || !$msg_json->{'ok'});
436 # Mark that we've sent the message, so it won't happen again.
437 $dbh->do('INSERT INTO users_nagged (userid, last_nag) VALUES (?, CURRENT_TIMESTAMP)', undef, $userid);
441 my $total_start = [Time::HiRes::gettimeofday];
444 skv_log("Siste sync startet: " . POSIX::ctime(time));
446 # Initialize the handles we need for communication.
447 $dbh = DBI->connect("dbi:Pg:dbname=$config::dbname;host=127.0.0.1", $config::dbuser, $config::dbpass, {RaiseError => 1})
448 or die "Could not connect to Postgres: " . DBI->errstr;
449 my $ua = LWP::UserAgent->new('SKVidarLang/1.0');
450 my $token = get_oauth_bearer_token($ua);
452 # Find the newest message, what it is linked to, and what was the one before it (for group diffing).
453 # TODO: Support more than one, and test better for errors here.
454 my $q = $dbh->prepare('select * from message_sheet_link where channel=? order by ts desc limit 2');
455 $q->execute($config::invitation_channel);
456 my $linkref = $q->fetchrow_hashref;
457 my $invitation_ts = $linkref->{'ts'};
458 my $wanted_sheet_title = $linkref->{'sheet_title'};
459 die "Could not get newest sheet title" if (!defined($wanted_sheet_title));
461 my ($tab_name, $tab_id) = get_spreadsheet_with_title($ua, $token, $wanted_sheet_title);
462 if (!defined($tab_name)) {
463 skv_log("Fant ikke noen fane med Ā«$wanted_sheet_titleĀ» i navnet; kan ikke synkronisere.\n");
464 sheet_batch_update($ua, $token, [ serialize_skv_log_to_sheet() ]);
468 # Store away the second-newest ID.
469 my $prev_invitation_ts = $q->fetchrow_hashref->{'ts'};
471 # Find everyone who are marked as attending on Slack (via reactions).
472 $q = $dbh->prepare('SELECT DISTINCT userid,reaction FROM current_reactions WHERE channel=? AND ts=? AND reaction IN (\'heart\', \'open_mouth\', \'blue_heart\')');
473 $q->execute($config::invitation_channel, $invitation_ts);
474 my @attending_userids = ();
477 while (my $ref = $q->fetchrow_hashref) {
478 my $userid = $ref->{'userid'};
479 push @attending_userids, $userid;
480 if ($ref->{'reaction'} eq 'blue_heart') {
481 if (exists($colors{$userid}) && $colors{$userid} eq 'yellow') {
482 $double{$userid} = 1;
484 $colors{$userid} = 'blue';
486 if (exists($colors{$userid}) && $colors{$userid} eq 'blue') {
487 $double{$userid} = 1;
489 $colors{$userid} = 'yellow';
493 # Remove double-attenders (we will log them as warnings further down).
494 @attending_userids = grep { !exists($double{$_}) } @attending_userids;
495 for my $userid (keys %double) {
496 delete $colors{$userid};
499 # Get the list of all people in the sheet (we're going to need them soon anyway).
500 my $start = [Time::HiRes::gettimeofday];
501 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',
502 Authorization => 'Bearer ' . $token
504 log_timing($start, "/spreadsheets/$tab_name");
505 my $main_sheet_json = JSON::XS::decode_json($response->decoded_content);
507 # Update the list of groups we've seen people in.
508 my %assignments = get_group_assignments($main_sheet_json);
509 update_assignment_db($dbh, $config::invitation_channel, $invitation_ts, \%assignments);
511 my %seen_names = find_where_each_name_is($main_sheet_json);
514 for my $name (sort keys %seen_names) {
515 my $seen = $seen_names{$name};
516 if (scalar @$seen >= 2) {
517 my $exemplar = $seen->[0][0];
518 skv_log("Duplikat: $exemplar (" . format_cell_names_for_seen($seen) . ")");
522 # Get our existing Slack->name mapping, from the sheets.
523 my %slack_userid_to_real_name = ();
524 my %slack_userid_to_slack_name = ();
525 my %slack_userid_to_row = ();
527 $start = [Time::HiRes::gettimeofday];
528 $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',
529 Authorization => 'Bearer ' . $token
531 log_timing($start, "/spreadsheets/Slack-mapping");
532 my $mapping_sheet_json = JSON::XS::decode_json($response->decoded_content);
533 my $mapping_sheet_rows = $mapping_sheet_json->{'sheets'}[0]{'data'}[0]{'rowData'};
535 for my $row (@$mapping_sheet_rows) {
536 my $slack_id = $row->{'values'}[0]{'userEnteredValue'}{'stringValue'};
537 my $slack_name = $row->{'values'}[1]{'userEnteredValue'}{'stringValue'};
538 my $real_name = get_spreadsheet_name($row->{'values'}[2]); # TODO support more
539 $slack_userid_to_row{$slack_id} = $cur_row++;
540 next if (!defined($slack_name));
541 $slack_userid_to_slack_name{$slack_id} = $slack_name;
542 next if (!defined($real_name));
543 $slack_userid_to_real_name{$slack_id} = $real_name;
546 # See which ones we don't have a mapping for, and look them up in Slack.
547 # TODO: Use an append call instead of $cur_row?
548 my @slack_mapping_updates = ();
549 for my $userid (@attending_userids) {
550 next if (exists($slack_userid_to_real_name{$userid}));
552 # Make sure they have a row in the spreadsheet.
554 if (exists($slack_userid_to_row{$userid})) {
555 $write_row = $slack_userid_to_row{$userid};
557 $write_row = $cur_row++;
558 $slack_userid_to_row{$userid} = $write_row;
559 push @slack_mapping_updates, {
560 range => "Slack-mapping!A$write_row:A$write_row",
561 values => [ [ $userid ]]
565 # Fetch their Slack name if we don't already have it.
567 if (exists($slack_userid_to_slack_name{$userid})) {
568 $slack_name = $slack_userid_to_slack_name{$userid};
570 $slack_userid_to_slack_name{$userid} = $slack_name;
571 $slack_name = get_slack_name($ua, $userid);
572 push @slack_mapping_updates, {
573 range => "Slack-mapping!B$write_row:B$write_row",
574 values => [ [ $slack_name ]]
576 $slack_userid_to_slack_name{$userid} = $slack_name;
579 if (exists($seen_names{lc $slack_name})) {
580 # The name exists exactly, once or more, so it's a direct match and we ignore any fuzz.
581 $slack_userid_to_real_name{$userid} = $slack_name;
582 push @slack_mapping_updates, {
583 range => "Slack-mapping!C$write_row:C$write_row",
584 values => [ [ $slack_name ]]
587 # Do a search through all the available names in the sheet to find an obvious(ish) match.
589 my $main_sheet_rows = $main_sheet_json->{'sheets'}[0]{'data'}[0]{'rowData'};
590 for my $row (@$main_sheet_rows) {
591 for my $val (@{$row->{'values'}}) {
592 my $name = get_spreadsheet_name($val);
593 if (defined($name) && matches_name($slack_name, $name)) {
594 push @candidates, $name;
598 if ($#candidates == -1) {
599 skv_log("$slack_name ($userid) er pƄmeldt pƄ Slack, men fant ikke et regneark-navn for dem.");
600 possibly_nag_user($dbh, $ua, $userid);
601 } elsif ($#candidates == 0) {
602 my $name = $candidates[0];
603 $slack_userid_to_real_name{$userid} = $name;
604 push @slack_mapping_updates, {
605 range => "Slack-mapping!C$write_row:C$write_row",
606 values => [ [ $name ]]
609 skv_log("$slack_name ($userid) er pƄmeldt pƄ Slack, men hadde flere fuzzy-matcher; vet ikke hvilket regneark-navn som skal brukes.");
613 if (scalar @slack_mapping_updates > 0) {
615 valueInputOption => 'USER_ENTERED',
616 data => \@slack_mapping_updates
618 $start = [Time::HiRes::gettimeofday];
619 $response = $ua->post(
620 'https://sheets.googleapis.com/v4/spreadsheets/' . $config::sheet_id . '/values:batchUpdate?key=' . $config::gsheets_api_key,
621 Content => JSON::XS::encode_json($update),
622 Content_type => 'application/json;charset=UTF-8',
623 Authorization => 'Bearer ' . $token
625 log_timing($start, "/spreadsheets/values:batchUpdate");
626 die $response->decoded_content if (!$response->is_success);
629 # Now that we have Slack names, we can log double-reacters.
630 for my $userid (keys %double) {
631 my $name = best_name_for_log($userid, \%slack_userid_to_real_name, \%slack_userid_to_slack_name);
632 skv_log("$name er pƄmeldt flere steder pƄ Slack; vet ikke hvilken som skal brukes.");
635 # Find the list of names to mark yellow.
636 my %want_colors = ();
637 my $main_sheet_rows = $main_sheet_json->{'sheets'}[0]{'data'}[0]{'rowData'};
638 for my $userid (@attending_userids) {
639 next if (!exists($slack_userid_to_real_name{$userid}));
640 my $slack_name = $slack_userid_to_slack_name{$userid};
641 my $real_name = $slack_userid_to_real_name{$userid};
643 # See if we can find them in the spreadsheet.
644 if (!exists($seen_names{lc $real_name})) {
645 # TODO: Perhaps move this logic further down, for consistency?
646 skv_log("$slack_name ($userid) er pƄmeldt pƄ Slack, og er mappet til $real_name, men var ikke i noen gruppe.");
648 my $seen = $seen_names{lc $real_name};
649 if (scalar @$seen >= 2) {
650 skv_log("$slack_name ($userid) er pƄmeldt pƄ Slack, men stƄr flere steder (se over); vet ikke hvilken celle som skal brukes.");
652 $want_colors{$seen->[0][0]} = $colors{$userid};
657 # Find the list of names we already marked yellow.
658 my %have_colors = ();
659 $dbh->{AutoCommit} = 0;
660 $dbh->do('SET TRANSACTION ISOLATION LEVEL SERIALIZABLE');
661 $q = $dbh->prepare('SELECT name,color FROM applied WHERE channel=? AND ts=?');
662 $q->execute($config::invitation_channel, $invitation_ts);
663 while (my $ref = $q->fetchrow_hashref) {
664 $have_colors{$ref->{'name'}} = $ref->{'color'};
667 my @diffs = find_diff($dbh, $invitation_ts, \%want_colors, \%have_colors, \%seen_names);
669 my @yellow_updates = ();
670 if (scalar @diffs > 0) {
671 # Now fill in the actual stuff.
672 for my $diff (@diffs) {
673 my $real_name = $diff->[0];
675 my $seen = $seen_names{lc $real_name};
677 # We've already complained about these earlier, so just skip them silently.
678 next if (scalar @$seen > 1);
680 # See if we can find them in the spreadsheet.
681 die "Could not find $real_name" if (!defined($seen));
682 my $rowno = $seen->[0][1];
683 my $colno = $seen->[0][2];
684 push @yellow_updates, {
688 userEnteredFormat => $diff->[1]
691 fields => 'userEnteredFormat.backgroundColor',
694 startRowIndex => $rowno,
695 endRowIndex => $rowno + 1,
696 startColumnIndex => $colno,
697 endColumnIndex => $colno + 1
704 my @recent_changes = create_reaction_log($dbh, $invitation_ts, \%slack_userid_to_real_name, \%slack_userid_to_slack_name);
705 push @yellow_updates, {
707 rows => \@recent_changes,
708 fields => 'userEnteredValue.stringValue',
710 sheetId => $config::log_tab_id,
712 endRowIndex => 4 + scalar @recent_changes,
713 startColumnIndex => 0,
719 my @recent_moves = create_move_log($dbh, $invitation_ts, $prev_invitation_ts);
720 push @yellow_updates, {
722 rows => \@recent_moves,
723 fields => 'userEnteredValue.stringValue',
725 sheetId => $config::log_tab_id,
727 endRowIndex => 4 + scalar @recent_moves,
728 startColumnIndex => 1,
734 # Push the final set of updates (including the log).
736 push @yellow_updates, serialize_skv_log_to_sheet();
737 sheet_batch_update($ua, $token, \@yellow_updates);
740 my $elapsed = Time::HiRes::tv_interval($total_start);
741 printf "Tok %.0f ms.\n", 1e3 * $elapsed;
744 if ($#ARGV >= 0 && $ARGV[0] eq '--daemon') {
745 # Start with a single, forced run.
746 unlink("/srv/skvidar-slack.sesse.net/marker");
750 if (!unlink("/srv/skvidar-slack.sesse.net/marker")) {
751 unless ($!{ENOENT}) {
752 warn "/srv/skvidar-slack.sesse.net/marker: $!";
761 warn "Died with: $@";