use LWP::UserAgent;
use DBI;
use POSIX;
+use Time::HiRes;
binmode STDOUT, ':utf8';
binmode STDERR, ':utf8';
use utf8;
# TODO:
# - detect moves between groups
-# - better daemon behavior
+# - make the sheet message more in-your-face
require '../include/config.pm';
+my $dbh;
my @log = ();
+my %rgb = (
+ yellow => {
+ red => 1,
+ green => 1,
+ blue => 0,
+ alpha => 1
+ },
+ blue => {
+ red => 0,
+ green => 1,
+ blue => 1,
+ alpha => 1
+ },
+ white => {
+ red => 1,
+ green => 1,
+ blue => 1,
+ alpha => 0
+ }
+);
+
sub get_oauth_bearer_token {
my $ua = shift;
my $now = time();
sub get_slack_name {
my ($ua, $userid) = @_;
my $req = HTTP::Request->new('GET', 'https://slack.com/api/users.info?user=' . $userid, [
- 'Authorization' => 'Bearer ' . $config::oauth_token
+ 'Authorization' => 'Bearer ' . $config::slack_oauth_token
]);
my $response = $ua->request($req);
die $response->status_line if !$response->is_success;
return (undef, undef);
}
-skv_log("Siste sync startet: " . POSIX::ctime(time));
-
-# Initialize the handles we need for communication.
-my $dbh = DBI->connect("dbi:Pg:dbname=$config::dbname;host=127.0.0.1", $config::dbuser, $config::dbpass, {RaiseError => 1})
- or die "Could not connect to Postgres: " . DBI->errstr;
-my $ua = LWP::UserAgent->new('SKVidarLang/1.0');
-my $token = get_oauth_bearer_token($ua);
-
-# Find the newest message, and what it is linked to.
-# TODO: Support more than one, and test better for errors here.
-my $q = $dbh->prepare('select * from message_sheet_link where channel=? order by ts desc limit 1');
-$q->execute($config::invitation_channel);
-my $linkref = $q->fetchrow_hashref;
-my $invitation_ts = $linkref->{'ts'};
-my $wanted_sheet_title = $linkref->{'sheet_title'};
-die "Could not get newest sheet title" if (!defined($wanted_sheet_title));
-
-my ($tab_name, $tab_id) = get_spreadsheet_with_title($ua, $token, $wanted_sheet_title);
-if (!defined($tab_name)) {
- skv_log("Fant ikke noen fane med “$wanted_sheet_title” i navnet; kan ikke synkronisere.\n");
- sheet_batch_update($ua, $token, [ serialize_skv_log_to_sheet() ]);
- die;
-}
-
-# Find everyone who are marked as attending on Slack (via reactions).
-$q = $dbh->prepare('SELECT DISTINCT userid FROM current_reactions WHERE channel=? AND ts=? AND reaction IN (\'heart\', \'open_mouth\')');
-$q->execute($config::invitation_channel, $invitation_ts);
-my @attending_userids = ();
-while (my $ref = $q->fetchrow_hashref) {
- push @attending_userids, $ref->{'userid'};
-}
-
-# Get the list of all people in the sheet (we're going to need them soon anyway).
-my $response = $ua->get('https://sheets.googleapis.com/v4/spreadsheets/' . $config::sheet_id . '?key=' . $config::gsheets_api_key . '&ranges=' . $tab_name . '!A9:Z5000&fields=sheets/data/rowData/values/userEnteredValue',
- Authorization => 'Bearer ' . $token
-);
-my $main_sheet_json = JSON::XS::decode_json($response->decoded_content);
+# Make a mapping of lowercase name -> list of [canonical name, row number, column number]
+sub find_where_each_name_is {
+ my $json = shift;
-# Duplicate detection
-my %seen_names = ();
-{
- my $main_sheet_rows = $main_sheet_json->{'sheets'}[0]{'data'}[0]{'rowData'};
- my $rowno = 9;
- for my $row (@$main_sheet_rows) {
+ my %seen_names = ();
+ my $rows = $json->{'sheets'}[0]{'data'}[0]{'rowData'};
+ my $rowno = 3;
+ for my $row (@$rows) {
my $colno = 0;
for my $val (@{$row->{'values'}}) {
my $name = get_spreadsheet_name($val);
}
++$rowno;
}
- for my $name (sort keys %seen_names) {
- my $seen = $seen_names{$name};
- if (scalar @$seen >= 2) {
- my $exemplar = $seen->[0][0];
- skv_log("Duplikat: $exemplar (" . format_cell_names_for_seen($seen) . ")");
- }
- }
-}
-# Get our existing Slack->name mapping, from the sheets.
-my %slack_userid_to_real_name = ();
-my %slack_userid_to_slack_name = ();
-my %slack_userid_to_row = ();
-my %real_name_to_slack_userid = ();
-$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',
- Authorization => 'Bearer ' . $token
-);
-my $mapping_sheet_json = JSON::XS::decode_json($response->decoded_content);
-my $mapping_sheet_rows = $mapping_sheet_json->{'sheets'}[0]{'data'}[0]{'rowData'};
-my $cur_row = 5;
-for my $row (@$mapping_sheet_rows) {
- my $slack_id = $row->{'values'}[0]{'userEnteredValue'}{'stringValue'};
- my $slack_name = $row->{'values'}[1]{'userEnteredValue'}{'stringValue'};
- my $real_name = get_spreadsheet_name($row->{'values'}[2]); # TODO support more
- $slack_userid_to_row{$slack_id} = $cur_row++;
- next if (!defined($slack_name));
- $slack_userid_to_slack_name{$slack_id} = $slack_name;
- next if (!defined($real_name));
- $slack_userid_to_real_name{$slack_id} = $real_name;
- $real_name_to_slack_userid{$real_name} = $slack_id;
+ return %seen_names;
}
-# See which ones we don't have a mapping for, and look them up in Slack.
-# TODO: Use an append call instead of $cur_row?
-my @slack_mapping_updates = ();
-for my $userid (@attending_userids) {
- next if (exists($slack_userid_to_real_name{$userid}));
-
- # Make sure they have a row in the spreadsheet.
- my $write_row;
- if (exists($slack_userid_to_row{$userid})) {
- $write_row = $slack_userid_to_row{$userid};
+sub best_name_for_log {
+ my ($userid, $slack_userid_to_real_name, $slack_userid_to_slack_name) = @_;
+ if (exists($slack_userid_to_real_name->{$userid})) {
+ return $slack_userid_to_real_name->{$userid};
+ } elsif (exists($slack_userid_to_slack_name->{$userid})) {
+ return $slack_userid_to_slack_name->{$userid} . ' (fant ikke regneark-navn)';
} else {
- $write_row = $cur_row++;
- $slack_userid_to_row{$userid} = $write_row;
- push @slack_mapping_updates, {
- range => "Slack-mapping!A$write_row:A$write_row",
- values => [ [ $userid ]]
- };
+ # Should only happen if we didn't see the initial reaction_add, only reaction_remove.
+ # (TODO: Is the comment above true anymore, now that we use this from multiple contexts?)
+ return $userid . ' (fant ikke Slack-navn)';
}
+}
- # Fetch their Slack name if we don't already have it.
- my $slack_name;
- if (exists($slack_userid_to_slack_name{$userid})) {
- $slack_name = $slack_userid_to_slack_name{$userid};
- } else {
- $slack_userid_to_slack_name{$userid} = $slack_name;
- $slack_name = get_slack_name($ua, $userid);
- push @slack_mapping_updates, {
- range => "Slack-mapping!B$write_row:B$write_row",
- values => [ [ $slack_name ]]
- };
- $slack_userid_to_slack_name{$userid} = $slack_name;
+# Add the reaction log. (This only takes into account the last change
+# for each user; earlier ones are irrelevant and don't count. But it
+# doesn't deduplicate across reactions. Meh.)
+sub create_reaction_log {
+ my ($dbh, $invitation_ts, $slack_userid_to_real_name, $slack_userid_to_slack_name) = @_;
+
+ 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');
+ $q->execute($config::invitation_channel, $invitation_ts);
+ my @recent_changes = ();
+ while (my $ref = $q->fetchrow_hashref) {
+ my $msg = $ref->{'event_ts'};
+ if ($ref->{'event_type'} eq 'reaction_added') {
+ $msg .= ' +';
+ } else {
+ $msg .= ' –';
+ }
+ if ($ref->{'reaction'} eq 'open_mouth') {
+ $msg .= '😮';
+ } elsif ($ref->{'reaction'} eq 'blue_heart') {
+ $msg .= '💙';
+ } else {
+ $msg .= '❤️';
+ }
+ $msg .= ' ';
+ $msg .= best_name_for_log($ref->{'userid'}, $slack_userid_to_real_name, $slack_userid_to_slack_name);
+ push @recent_changes, { values => [{ userEnteredValue => { stringValue => $msg } }] };
}
+ while (scalar @recent_changes < 50) {
+ push @recent_changes, { values => [{ userEnteredValue => { stringValue => '' } }] };
+ }
+ return @recent_changes;
+}
- if (exists($seen_names{lc $slack_name})) {
- # The name exists exactly, once or more, so it's a direct match and we ignore any fuzz.
- $slack_userid_to_real_name{$userid} = $slack_name;
- push @slack_mapping_updates, {
- range => "Slack-mapping!C$write_row:C$write_row",
- values => [ [ $slack_name ]]
- };
- } else {
- # Do a search through all the available names in the sheet to find an obvious(ish) match.
- my @candidates = ();
- my $main_sheet_rows = $main_sheet_json->{'sheets'}[0]{'data'}[0]{'rowData'};
- for my $row (@$main_sheet_rows) {
- for my $val (@{$row->{'values'}}) {
- my $name = get_spreadsheet_name($val);
- if (defined($name) && matches_name($slack_name, $name)) {
- push @candidates, $name;
- }
+# Also applies the diff to the database (a bit ugly).
+sub find_diff {
+ my ($dbh, $invitation_ts, $want_colors, $have_colors, $seen_names) = @_;
+
+ my @diffs = ();
+ for my $real_name (keys %$want_colors) {
+ my $wc = $want_colors->{$real_name};
+ if (exists($have_colors->{$real_name})) {
+ if ($have_colors->{$real_name} eq $wc) {
+ # Already good.
+ next;
}
- }
- if ($#candidates == -1) {
- skv_log("$slack_name ($userid) er påmeldt på Slack, men fant ikke et regneark-navn for dem.");
- } elsif ($#candidates == 0) {
- my $name = $candidates[0];
- $slack_userid_to_real_name{$userid} = $name;
- push @slack_mapping_updates, {
- range => "Slack-mapping!C$write_row:C$write_row",
- values => [ [ $name ]]
- };
+ skv_log("Markerer at $real_name har byttet treningssted.");
+ push @diffs, [
+ $real_name, { backgroundColor => $rgb{$wc} }
+ ];
+ $dbh->do('UPDATE applied SET color=? WHERE channel=? AND ts=? AND name=?', undef,
+ $wc, $config::invitation_channel, $invitation_ts, $real_name);
} else {
- skv_log("$slack_name ($userid) er påmeldt på Slack, men hadde flere fuzzy-matcher; vet ikke hvilket regneark-navn som skal brukes.");
+ skv_log("Markerer at $real_name skal på trening.");
+ push @diffs, [
+ $real_name, { backgroundColor => $rgb{$wc} }
+ ];
+ $dbh->do('INSERT INTO applied (channel, ts, name, color) VALUES (?, ?, ?, ?)', undef,
+ $config::invitation_channel, $invitation_ts, $real_name, $wc);
}
}
-}
-my $update = {
- valueInputOption => 'USER_ENTERED',
- data => \@slack_mapping_updates
-};
-$response = $ua->post(
- 'https://sheets.googleapis.com/v4/spreadsheets/' . $config::sheet_id . '/values:batchUpdate?key=' . $config::gsheets_api_key,
- Content => JSON::XS::encode_json($update),
- Content_type => 'application/json;charset=UTF-8',
- Authorization => 'Bearer ' . $token
-);
-die $response->decoded_content if (!$response->is_success);
-
-# Find the list of names to mark yellow.
-my %want_names = ();
-my $main_sheet_rows = $main_sheet_json->{'sheets'}[0]{'data'}[0]{'rowData'};
-for my $userid (@attending_userids) {
- next if (!exists($slack_userid_to_real_name{$userid}));
- my $slack_name = $slack_userid_to_slack_name{$userid};
- my $real_name = $slack_userid_to_real_name{$userid};
-
- # See if we can find them in the spreadsheet.
- if (!exists($seen_names{lc $real_name})) {
- skv_log("$slack_name ($userid) er påmeldt på Slack, og er mappet til $real_name, men var ikke i noen gruppe.");
- } else {
- my $seen = $seen_names{lc $real_name};
- if (scalar @$seen >= 2) {
- skv_log("$slack_name ($userid) er påmeldt på Slack, men står flere steder (se over); vet ikke hvilken celle som skal brukes.");
+ for my $real_name (keys %$have_colors) {
+ next if (exists($want_colors->{$real_name}));
+ if (!exists($seen_names->{lc $real_name})) {
+ # TODO: This can somehow come if we try to add someone who's not in the sheet, too?
+ skv_log("Ønsket å fjerne at $real_name skulle på trening, men de var ikke i regnearket lenger.");
} else {
- $want_names{$seen->[0][0]} = 1;
+ skv_log("Fjerner at $real_name skal på trening.");
+ push @diffs, [
+ $real_name, { backgroundColor => $rgb{white} }
+ ];
+ $dbh->do('DELETE FROM applied WHERE channel=? AND ts=? AND name=?', undef,
+ $config::invitation_channel, $invitation_ts, $real_name);
}
}
+ return @diffs;
}
-# Find the list of names we already marked yellow.
-my %have_names = ();
-$dbh->{AutoCommit} = 0;
-$dbh->do('SET TRANSACTION ISOLATION LEVEL SERIALIZABLE');
-$q = $dbh->prepare('SELECT name FROM applied WHERE channel=? AND ts=?');
-$q->execute($config::invitation_channel, $invitation_ts);
-while (my $ref = $q->fetchrow_hashref) {
- $have_names{$ref->{'name'}} = 1;
+sub possibly_nag_user {
+ my ($dbh, $ua, $userid) = @_;
+
+ # See if we've nagged this user before.
+ my $q = $dbh->prepare('SELECT * FROM users_nagged WHERE userid=?');
+ $q->execute($userid);
+ if (defined($q->fetchrow_hashref)) {
+ return;
+ }
+
+ skv_log("Sender melding til $userid for å spørre om gruppe.");
+ my $response = $ua->post(
+ 'https://slack.com/api/conversations.open',
+ Content => JSON::XS::encode_json({ users => [ $userid ] }),
+ Content_type => 'application/json;charset=UTF-8',
+ Authorization => 'Bearer ' . $config::slack_oauth_token
+ );
+ die $response->status_line if !$response->is_success;
+
+ my $im_json = JSON::XS::decode_json($response->decoded_content);
+ die "Something went wrong: " . $response->decoded_content if (!defined($im_json) || !$im_json->{'ok'});
+ my $channel_id = $im_json->{'channel'}{'id'};
+
+ 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!";
+
+ $response = $ua->post(
+ 'https://slack.com/api/chat.postMessage',
+ Content => JSON::XS::encode_json({ channel => $channel_id, text => $msg }),
+ Content_type => 'application/json;charset=UTF-8',
+ Authorization => 'Bearer ' . $config::slack_oauth_token
+ );
+ my $msg_json = JSON::XS::decode_json($response->decoded_content);
+ die "Something went wrong: " . $response->decoded_content if (!defined($msg_json) || !$msg_json->{'ok'});
+
+ # Mark that we've sent the message, so it won't happen again.
+ $dbh->do('INSERT INTO users_nagged (userid, last_nag) VALUES (?, CURRENT_TIMESTAMP)', undef, $userid);
}
-# Find the diff between the two.
-my @diffs = ();
-for my $real_name (keys %want_names) {
- next if (exists($have_names{$real_name}));
- skv_log("Markerer at $real_name skal på trening.");
- push @diffs, [
- $real_name,
- {
- backgroundColor => {
- red => 1,
- green => 1,
- blue => 0,
- alpha => 1
+sub run {
+ my $start = [Time::HiRes::gettimeofday];
+
+ @log = ();
+ skv_log("Siste sync startet: " . POSIX::ctime(time));
+
+ # Initialize the handles we need for communication.
+ $dbh = DBI->connect("dbi:Pg:dbname=$config::dbname;host=127.0.0.1", $config::dbuser, $config::dbpass, {RaiseError => 1})
+ or die "Could not connect to Postgres: " . DBI->errstr;
+ my $ua = LWP::UserAgent->new('SKVidarLang/1.0');
+ my $token = get_oauth_bearer_token($ua);
+
+ # Find the newest message, and what it is linked to.
+ # TODO: Support more than one, and test better for errors here.
+ my $q = $dbh->prepare('select * from message_sheet_link where channel=? order by ts desc limit 1');
+ $q->execute($config::invitation_channel);
+ my $linkref = $q->fetchrow_hashref;
+ my $invitation_ts = $linkref->{'ts'};
+ my $wanted_sheet_title = $linkref->{'sheet_title'};
+ die "Could not get newest sheet title" if (!defined($wanted_sheet_title));
+
+ my ($tab_name, $tab_id) = get_spreadsheet_with_title($ua, $token, $wanted_sheet_title);
+ if (!defined($tab_name)) {
+ skv_log("Fant ikke noen fane med “$wanted_sheet_title” i navnet; kan ikke synkronisere.\n");
+ sheet_batch_update($ua, $token, [ serialize_skv_log_to_sheet() ]);
+ die;
+ }
+
+ # Find everyone who are marked as attending on Slack (via reactions).
+ $q = $dbh->prepare('SELECT DISTINCT userid,reaction FROM current_reactions WHERE channel=? AND ts=? AND reaction IN (\'heart\', \'open_mouth\', \'blue_heart\')');
+ $q->execute($config::invitation_channel, $invitation_ts);
+ my @attending_userids = ();
+ my %colors = ();
+ my %double = ();
+ while (my $ref = $q->fetchrow_hashref) {
+ my $userid = $ref->{'userid'};
+ push @attending_userids, $userid;
+ if ($ref->{'reaction'} eq 'blue_heart') {
+ if (exists($colors{$userid}) && $colors{$userid} eq 'yellow') {
+ $double{$userid} = 1;
}
- }
- ];
- $dbh->do('INSERT INTO applied (channel, ts, name) VALUES (?, ?, ?)', undef,
- $config::invitation_channel, $invitation_ts, $real_name);
-}
-for my $real_name (keys %have_names) {
- next if (exists($want_names{$real_name}));
- skv_log("Fjerner at $real_name skal på trening.");
- push @diffs, [
- $real_name,
- {
- backgroundColor => {
- red => 1,
- green => 1,
- blue => 1,
- alpha => 0
+ $colors{$userid} = 'blue';
+ } else {
+ if (exists($colors{$userid}) && $colors{$userid} eq 'blue') {
+ $double{$userid} = 1;
}
+ $colors{$userid} = 'yellow';
}
- ];
- $dbh->do('DELETE FROM applied WHERE channel=? AND ts=? AND name=?', undef,
- $config::invitation_channel, $invitation_ts, $real_name);
-}
+ }
-my @yellow_updates = ();
-if (scalar @diffs > 0) {
- # Now fill in the actual stuff.
- for my $diff (@diffs) {
- my $real_name = $diff->[0];
+ # Remove double-attenders (we will log them as warnings further down).
+ @attending_userids = grep { !exists($double{$_}) } @attending_userids;
+ for my $userid (keys %double) {
+ delete $colors{$userid};
+ }
- # See if we can find them in the spreadsheet.
- # FIXME use seen_names
- my $rowno = 8; # zero-indexed
- my $found = 0;
- for my $row (@$main_sheet_rows) {
- my $colno = 0;
- for my $val (@{$row->{'values'}}) {
- my $name = get_spreadsheet_name($val);
- if (defined($name) && $real_name eq $name) {
- push @yellow_updates, {
- updateCells => {
- rows => [{
- values => [{
- userEnteredFormat => $diff->[1]
- }]
- }],
- fields => 'userEnteredFormat.backgroundColor',
- range => {
- sheetId => $tab_id,
- startRowIndex => $rowno,
- endRowIndex => $rowno + 1,
- startColumnIndex => $colno,
- endColumnIndex => $colno + 1
- }
- }
- };
- $found = 1;
+ # Get the list of all people in the sheet (we're going to need them soon anyway).
+ 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',
+ Authorization => 'Bearer ' . $token
+ );
+ my $main_sheet_json = JSON::XS::decode_json($response->decoded_content);
+
+ my %seen_names = find_where_each_name_is($main_sheet_json);
+
+ # Find duplicates.
+ for my $name (sort keys %seen_names) {
+ my $seen = $seen_names{$name};
+ if (scalar @$seen >= 2) {
+ my $exemplar = $seen->[0][0];
+ skv_log("Duplikat: $exemplar (" . format_cell_names_for_seen($seen) . ")");
+ }
+ }
+
+ # Get our existing Slack->name mapping, from the sheets.
+ my %slack_userid_to_real_name = ();
+ my %slack_userid_to_slack_name = ();
+ my %slack_userid_to_row = ();
+ $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',
+ Authorization => 'Bearer ' . $token
+ );
+ my $mapping_sheet_json = JSON::XS::decode_json($response->decoded_content);
+ my $mapping_sheet_rows = $mapping_sheet_json->{'sheets'}[0]{'data'}[0]{'rowData'};
+ my $cur_row = 5;
+ for my $row (@$mapping_sheet_rows) {
+ my $slack_id = $row->{'values'}[0]{'userEnteredValue'}{'stringValue'};
+ my $slack_name = $row->{'values'}[1]{'userEnteredValue'}{'stringValue'};
+ my $real_name = get_spreadsheet_name($row->{'values'}[2]); # TODO support more
+ $slack_userid_to_row{$slack_id} = $cur_row++;
+ next if (!defined($slack_name));
+ $slack_userid_to_slack_name{$slack_id} = $slack_name;
+ next if (!defined($real_name));
+ $slack_userid_to_real_name{$slack_id} = $real_name;
+ }
+
+ # See which ones we don't have a mapping for, and look them up in Slack.
+ # TODO: Use an append call instead of $cur_row?
+ my @slack_mapping_updates = ();
+ for my $userid (@attending_userids) {
+ next if (exists($slack_userid_to_real_name{$userid}));
+
+ # Make sure they have a row in the spreadsheet.
+ my $write_row;
+ if (exists($slack_userid_to_row{$userid})) {
+ $write_row = $slack_userid_to_row{$userid};
+ } else {
+ $write_row = $cur_row++;
+ $slack_userid_to_row{$userid} = $write_row;
+ push @slack_mapping_updates, {
+ range => "Slack-mapping!A$write_row:A$write_row",
+ values => [ [ $userid ]]
+ };
+ }
+
+ # Fetch their Slack name if we don't already have it.
+ my $slack_name;
+ if (exists($slack_userid_to_slack_name{$userid})) {
+ $slack_name = $slack_userid_to_slack_name{$userid};
+ } else {
+ $slack_userid_to_slack_name{$userid} = $slack_name;
+ $slack_name = get_slack_name($ua, $userid);
+ push @slack_mapping_updates, {
+ range => "Slack-mapping!B$write_row:B$write_row",
+ values => [ [ $slack_name ]]
+ };
+ $slack_userid_to_slack_name{$userid} = $slack_name;
+ }
+
+ if (exists($seen_names{lc $slack_name})) {
+ # The name exists exactly, once or more, so it's a direct match and we ignore any fuzz.
+ $slack_userid_to_real_name{$userid} = $slack_name;
+ push @slack_mapping_updates, {
+ range => "Slack-mapping!C$write_row:C$write_row",
+ values => [ [ $slack_name ]]
+ };
+ } else {
+ # Do a search through all the available names in the sheet to find an obvious(ish) match.
+ my @candidates = ();
+ my $main_sheet_rows = $main_sheet_json->{'sheets'}[0]{'data'}[0]{'rowData'};
+ for my $row (@$main_sheet_rows) {
+ for my $val (@{$row->{'values'}}) {
+ my $name = get_spreadsheet_name($val);
+ if (defined($name) && matches_name($slack_name, $name)) {
+ push @candidates, $name;
+ }
}
- ++$colno;
}
- ++$rowno;
+ if ($#candidates == -1) {
+ skv_log("$slack_name ($userid) er påmeldt på Slack, men fant ikke et regneark-navn for dem.");
+ possibly_nag_user($dbh, $ua, $userid);
+ } elsif ($#candidates == 0) {
+ my $name = $candidates[0];
+ $slack_userid_to_real_name{$userid} = $name;
+ push @slack_mapping_updates, {
+ range => "Slack-mapping!C$write_row:C$write_row",
+ values => [ [ $name ]]
+ };
+ } else {
+ skv_log("$slack_name ($userid) er påmeldt på Slack, men hadde flere fuzzy-matcher; vet ikke hvilket regneark-navn som skal brukes.");
+ }
}
+ }
+ my $update = {
+ valueInputOption => 'USER_ENTERED',
+ data => \@slack_mapping_updates
+ };
+ $response = $ua->post(
+ 'https://sheets.googleapis.com/v4/spreadsheets/' . $config::sheet_id . '/values:batchUpdate?key=' . $config::gsheets_api_key,
+ Content => JSON::XS::encode_json($update),
+ Content_type => 'application/json;charset=UTF-8',
+ Authorization => 'Bearer ' . $token
+ );
+ die $response->decoded_content if (!$response->is_success);
- if ($found) {
+ # Now that we have Slack names, we can log double-reacters.
+ for my $userid (keys %double) {
+ my $name = best_name_for_log($userid, \%slack_userid_to_real_name, \%slack_userid_to_slack_name);
+ skv_log("$name er påmeldt flere steder på Slack; vet ikke hvilken som skal brukes.");
+ }
+
+ # Find the list of names to mark yellow.
+ my %want_colors = ();
+ my $main_sheet_rows = $main_sheet_json->{'sheets'}[0]{'data'}[0]{'rowData'};
+ for my $userid (@attending_userids) {
+ next if (!exists($slack_userid_to_real_name{$userid}));
+ my $slack_name = $slack_userid_to_slack_name{$userid};
+ my $real_name = $slack_userid_to_real_name{$userid};
+
+ # See if we can find them in the spreadsheet.
+ if (!exists($seen_names{lc $real_name})) {
+ # TODO: Perhaps move this logic further down, for consistency?
+ skv_log("$slack_name ($userid) er påmeldt på Slack, og er mappet til $real_name, men var ikke i noen gruppe.");
} else {
- skv_log("Ønsket å fjerne at $real_name skulle på trening, men de var ikke i regnearket lenger.");
+ my $seen = $seen_names{lc $real_name};
+ if (scalar @$seen >= 2) {
+ skv_log("$slack_name ($userid) er påmeldt på Slack, men står flere steder (se over); vet ikke hvilken celle som skal brukes.");
+ } else {
+ $want_colors{$seen->[0][0]} = $colors{$userid};
+ }
}
}
-}
-# Add the reaction log. (This only takes into account the last change
-# for each user; earlier ones are irrelevant and don't count. But it
-# doesn't deduplicate across reactions. Meh.)
-$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');
-$q->execute($config::invitation_channel, $invitation_ts);
-my @recent_changes = ();
-while (my $ref = $q->fetchrow_hashref) {
- my $msg = $ref->{'event_ts'};
- if ($ref->{'event_type'} eq 'reaction_added') {
- $msg .= ' +';
- } else {
- $msg .= ' –';
+ # Find the list of names we already marked yellow.
+ my %have_colors = ();
+ $dbh->{AutoCommit} = 0;
+ $dbh->do('SET TRANSACTION ISOLATION LEVEL SERIALIZABLE');
+ $q = $dbh->prepare('SELECT name,color FROM applied WHERE channel=? AND ts=?');
+ $q->execute($config::invitation_channel, $invitation_ts);
+ while (my $ref = $q->fetchrow_hashref) {
+ $have_colors{$ref->{'name'}} = $ref->{'color'};
}
- if ($ref->{'reaction'} eq 'open_mouth') {
- $msg .= '😮';
- } else {
- $msg .= '❤️';
- }
- $msg .= ' ';
- if (exists($slack_userid_to_real_name{$ref->{'userid'}})) {
- $msg .= $slack_userid_to_real_name{$ref->{'userid'}};
- } elsif (exists($slack_userid_to_slack_name{$ref->{'userid'}})) {
- $msg .= $slack_userid_to_slack_name{$ref->{'userid'}} . ' (fant ikke regneark-navn)';
- } else {
- # Should only happen if we didn't see the initial reaction_add, only reaction_remove.
- $msg .= $ref->{'userid'} . ' (fant ikke Slack-navn)';
+
+ my @diffs = find_diff($dbh, $invitation_ts, \%want_colors, \%have_colors, \%seen_names);
+
+ my @yellow_updates = ();
+ if (scalar @diffs > 0) {
+ # Now fill in the actual stuff.
+ for my $diff (@diffs) {
+ my $real_name = $diff->[0];
+
+ # See if we can find them in the spreadsheet.
+ my $seen = $seen_names{lc $real_name};
+ die if (!defined($seen) || scalar @$seen > 1);
+ my $rowno = $seen->[0][1];
+ my $colno = $seen->[0][2];
+ push @yellow_updates, {
+ updateCells => {
+ rows => [{
+ values => [{
+ userEnteredFormat => $diff->[1]
+ }]
+ }],
+ fields => 'userEnteredFormat.backgroundColor',
+ range => {
+ sheetId => $tab_id,
+ startRowIndex => $rowno,
+ endRowIndex => $rowno + 1,
+ startColumnIndex => $colno,
+ endColumnIndex => $colno + 1
+ }
+ }
+ };
+ }
}
- push @recent_changes, { values => [{ userEnteredValue => { stringValue => $msg } }] };
-}
-while (scalar @recent_changes < 50) {
- push @recent_changes, { values => [{ userEnteredValue => { stringValue => '' } }] };
+
+ my @recent_changes = create_reaction_log($dbh, $invitation_ts, \%slack_userid_to_real_name, \%slack_userid_to_slack_name);
+ push @yellow_updates, {
+ updateCells => {
+ rows => \@recent_changes,
+ fields => 'userEnteredValue.stringValue',
+ range => {
+ sheetId => $config::log_tab_id,
+ startRowIndex => 4,
+ endRowIndex => 4 + scalar @recent_changes,
+ startColumnIndex => 0,
+ endColumnIndex => 1
+ }
+ }
+ };
+
+ # Push the final set of updates (including the log).
+ skv_log("Ferdig.");
+ push @yellow_updates, serialize_skv_log_to_sheet();
+ sheet_batch_update($ua, $token, \@yellow_updates);
+ $dbh->commit;
+
+ my $elapsed = Time::HiRes::tv_interval($start);
+ printf "Tok %.0f ms.\n", 1e3 * $elapsed;
}
-push @yellow_updates, {
- updateCells => {
- rows => \@recent_changes,
- fields => 'userEnteredValue.stringValue',
- range => {
- sheetId => $config::log_tab_id,
- startRowIndex => 4,
- endRowIndex => 4 + scalar @recent_changes,
- startColumnIndex => 0,
- endColumnIndex => 1
+
+if ($#ARGV >= 0 && $ARGV[0] eq '--daemon') {
+ # Start with a single, forced run.
+ unlink("/srv/skvidar-slack.sesse.net/marker");
+ run();
+
+ while (1) {
+ if (!unlink("/srv/skvidar-slack.sesse.net/marker")) {
+ unless ($!{ENOENT}) {
+ warn "/srv/skvidar-slack.sesse.net/marker: $!";
+ }
+ sleep 1;
+ next;
}
+ eval {
+ run();
+ };
+ if ($@) {
+ warn "Died with: $@";
+ }
+ $dbh->disconnect;
}
-};
-
-# Push the final set of updates (including the log).
-skv_log("Ferdig.");
-push @yellow_updates, serialize_skv_log_to_sheet();
-sheet_batch_update($ua, $token, \@yellow_updates);
-$dbh->commit;
+} else {
+ run();
+}
projects / skvidarsync / blobdiff