use utf8;
# TODO:
-# - detect moves between groups
# - make the sheet message more in-your-face (ephemeral message)
require '../include/config.pm';
die $response->decoded_content if !$response->is_success;
}
+sub get_group_assignments {
+ my $json = shift;
+
+ my %assignments = ();
+ my $rows = $json->{'sheets'}[0]{'data'}[0]{'rowData'};
+ my @curr_groups = ();
+ for my $row (@$rows) {
+ my $col = 0;
+ for my $val (@{$row->{'values'}}) {
+ ++$col;
+ my $contents = $val->{'userEnteredValue'}{'stringValue'};
+ next if !defined($contents);
+ if ($contents =~ /Gruppe /) {
+ @curr_groups = ();
+ last;
+ }
+ next if $contents =~ /^VL:/;
+ next if $contents =~ /^LT\b/;
+ next if $contents =~ /^400m/;
+ next if $contents =~ /^546m/;
+ if ($contents =~ /^(G\d\.\d)/ || $contents =~ /^(Nye løpere.*)/) {
+ $curr_groups[$col] = $1;
+ } else {
+ my $name = get_spreadsheet_name($val);
+ next if (!defined($name));
+ my $group = $curr_groups[$col] // $curr_groups[$col - 1];
+ # print $group, " ", $name, "\n";
+ if (exists($assignments{$name})) {
+ $assignments{$name} = "(flere grupper)";
+ } else {
+ $assignments{$name} = $group;
+ }
+ }
+ }
+ }
+ return %assignments;
+}
+
+sub update_assignment_db {
+ my ($dbh, $channel, $ts, $assignments) = @_;
+
+ local $dbh->{AutoCommit} = 0;
+ my %db_assignments = ();
+ my $q = $dbh->prepare('SELECT name,group_name FROM current_group_membership_history WHERE channel=? AND ts=?');
+ $q->execute($channel, $ts);
+ while (my $ref = $q->fetchrow_hashref) {
+ if (defined($ref->{'group_name'})) {
+ $db_assignments{$ref->{'name'}} = $ref->{'group_name'};
+ }
+ }
+
+ $q = $dbh->prepare('INSERT INTO group_membership_history (channel, ts, name, change_seen, group_name) VALUES (?, ?, ?, CURRENT_TIMESTAMP, ?)');
+ for my $name (keys %$assignments) {
+ if (!exists($db_assignments{$name}) || $db_assignments{$name} ne $assignments->{$name}) {
+ $q->execute($channel, $ts, $name, $assignments->{$name});
+ }
+ }
+ for my $name (keys %db_assignments) {
+ if (!exists($assignments->{$name})) {
+ $q->execute($channel, $ts, $name, undef);
+ }
+ }
+ $dbh->commit;
+}
+
sub get_spreadsheet_with_title {
my ($ua, $token, $wanted_sheet_title) = @_;
return @recent_changes;
}
+sub create_move_log {
+ my ($dbh, $invitation_ts, $prev_invitation_ts) = @_;
+ my $q = $dbh->prepare(<<"EOF");
+SELECT
+ 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
+FROM ( SELECT * FROM current_group_membership_history WHERE ts=? ) g_old
+ FULL OUTER JOIN ( SELECT * FROM current_group_membership_history WHERE ts=? ) g_new USING (name)
+WHERE
+ g_new.group_name IS DISTINCT FROM g_old.group_name
+ AND g_new.group_name IS NOT NULL
+ORDER BY g_new.change_seen DESC, name
+LIMIT 50
+EOF
+ $q->execute($prev_invitation_ts, $invitation_ts);
+ my @recent_moves = ();
+ while (my $ref = $q->fetchrow_hashref) {
+ my $name = $ref->{'name'};
+ my $old_group = $ref->{'old_group'};
+ my $new_group = $ref->{'new_group'};
+
+ my $msg = $ref->{'change_seen'} . " ";
+ if (!defined($old_group)) {
+ $msg .= "$name, (ny løper) → $new_group";
+ } else {
+ $msg .= "$name, $old_group → $new_group";
+ }
+ push @recent_moves, { values => [{ userEnteredValue => { stringValue => $msg } }] };
+ }
+ while (scalar @recent_moves < 50) {
+ push @recent_moves, { values => [{ userEnteredValue => { stringValue => '' } }] };
+ }
+ return @recent_moves;
+}
+
# Also applies the diff to the database (a bit ugly).
sub find_diff {
my ($dbh, $invitation_ts, $want_colors, $have_colors, $seen_names) = @_;
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.
+ # Find the newest message, what it is linked to, and what was the one before it (for group diffing).
# 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');
+ my $q = $dbh->prepare('select * from message_sheet_link where channel=? order by ts desc limit 2');
$q->execute($config::invitation_channel);
my $linkref = $q->fetchrow_hashref;
my $invitation_ts = $linkref->{'ts'};
die;
}
+ # Store away the second-newest ID.
+ my $prev_invitation_ts = $q->fetchrow_hashref->{'ts'};
+
# 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 $main_sheet_json = JSON::XS::decode_json($response->decoded_content);
+ # Update the list of groups we've seen people in.
+ my %assignments = get_group_assignments($main_sheet_json);
+ update_assignment_db($dbh, $config::invitation_channel, $invitation_ts, \%assignments);
+
my %seen_names = find_where_each_name_is($main_sheet_json);
# Find duplicates.
}
};
+ my @recent_moves = create_move_log($dbh, $invitation_ts, $prev_invitation_ts);
+ push @yellow_updates, {
+ updateCells => {
+ rows => \@recent_moves,
+ fields => 'userEnteredValue.stringValue',
+ range => {
+ sheetId => $config::log_tab_id,
+ startRowIndex => 4,
+ endRowIndex => 4 + scalar @recent_moves,
+ startColumnIndex => 1,
+ endColumnIndex => 2
+ }
+ }
+ };
+
# Push the final set of updates (including the log).
skv_log("Ferdig.");
push @yellow_updates, serialize_skv_log_to_sheet();
PRIMARY KEY (userid)
);
+CREATE TABLE group_membership_history (
+ -- (channel, ts) says what event we saw them at (even if they were not actually attending).
+ channel VARCHAR NOT NULL,
+ ts VARCHAR NOT NULL,
+ name VARCHAR NOT NULL, -- As written in the spreadsheet.
+ change_seen TIMESTAMP WITH TIME ZONE NOT NULL,
+ group_name VARCHAR
+);
+CREATE INDEX gmh_lookup_idx ON group_membership_history (channel, ts, name, change_seen DESC);
+
CREATE VIEW current_reactions AS
SELECT userid, channel, ts, reaction
FROM ( SELECT DISTINCT ON (channel, ts, userid, reaction)
FROM reaction_log ORDER BY channel, ts, userid, reaction, event_ts DESC ) t1
WHERE event_type = 'reaction_added';
+CREATE VIEW current_group_membership_history AS
+ SELECT DISTINCT ON (channel,ts,name)
+ channel, ts, name, group_name, change_seen
+ FROM group_membership_history
+ ORDER BY channel, ts, name,change_seen DESC;
+
GRANT SELECT,UPDATE,INSERT,DELETE ON TABLE applied TO skvidarlang;
GRANT SELECT,INSERT ON TABLE reaction_log TO skvidarlang;
GRANT SELECT ON TABLE current_reactions TO skvidarlang;
GRANT SELECT,UPDATE,INSERT ON TABLE message_sheet_link TO skvidarlang;
GRANT SELECT,INSERT ON TABLE users_nagged TO skvidarlang;
+GRANT SELECT,INSERT ON TABLE group_membership_history TO skvidarlang;
+GRANT SELECT ON TABLE current_group_membership_history TO skvidarlang;
projects / skvidarsync / commitdiff