use LWP::UserAgent;
use DBI;
use POSIX;
+use Time::HiRes;
binmode STDOUT, ':utf8';
binmode STDERR, ':utf8';
use utf8;
# TODO:
# - detect moves between groups
+# - 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;
my %seen_names = ();
my $rows = $json->{'sheets'}[0]{'data'}[0]{'rowData'};
- my $rowno = 4;
+ my $rowno = 3;
for my $row (@$rows) {
my $colno = 0;
for my $val (@{$row->{'values'}}) {
return %seen_names;
}
+
+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 {
+ # 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)';
+ }
+}
+
# 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\') 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');
+ 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) {
}
if ($ref->{'reaction'} eq 'open_mouth') {
$msg .= 'š®';
+ } elsif ($ref->{'reaction'} eq 'blue_heart') {
+ $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)';
- }
+ $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) {
# Also applies the diff to the database (a bit ugly).
sub find_diff {
- my ($dbh, $invitation_ts, $want_names, $have_names, $seen_names) = @_;
+ my ($dbh, $invitation_ts, $want_colors, $have_colors, $seen_names) = @_;
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
- }
+ 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;
}
- ];
- $dbh->do('INSERT INTO applied (channel, ts, name) VALUES (?, ?, ?)', undef,
- $config::invitation_channel, $invitation_ts, $real_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("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);
+ }
}
- for my $real_name (keys %$have_names) {
- next if (exists($want_names->{$real_name}));
+ 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 {
skv_log("Fjerner at $real_name skal pƄ trening.");
push @diffs, [
- $real_name,
- {
- backgroundColor => {
- red => 1,
- green => 1,
- blue => 1,
- alpha => 0
- }
- }
+ $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;
}
+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);
+}
+
sub run {
+ my $start = [Time::HiRes::gettimeofday];
+
@log = ();
skv_log("Siste sync startet: " . POSIX::ctime(time));
}
# 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 = $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) {
- push @attending_userids, $ref->{'userid'};
+ 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;
+ }
+ $colors{$userid} = 'blue';
+ } else {
+ if (exists($colors{$userid}) && $colors{$userid} eq 'blue') {
+ $double{$userid} = 1;
+ }
+ $colors{$userid} = 'yellow';
+ }
+ }
+
+ # 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};
}
# Get the list of all people in the sheet (we're going to need them soon anyway).
}
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;
);
die $response->decoded_content if (!$response->is_success);
+ # 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_names = ();
+ 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}));
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_names{$seen->[0][0]} = 1;
+ $want_colors{$seen->[0][0]} = $colors{$userid};
}
}
}
# Find the list of names we already marked yellow.
- my %have_names = ();
+ my %have_colors = ();
$dbh->{AutoCommit} = 0;
$dbh->do('SET TRANSACTION ISOLATION LEVEL SERIALIZABLE');
- $q = $dbh->prepare('SELECT name FROM applied WHERE channel=? AND ts=?');
+ $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_names{$ref->{'name'}} = 1;
+ $have_colors{$ref->{'name'}} = $ref->{'color'};
}
- my @diffs = find_diff($dbh, $invitation_ts, \%want_names, \%have_names, \%seen_names);
+ my @diffs = find_diff($dbh, $invitation_ts, \%want_colors, \%have_colors, \%seen_names);
my @yellow_updates = ();
if (scalar @diffs > 0) {
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;
}
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}) {
projects / skvidarsync / blobdiff