10 binmode STDOUT, ':utf8';
11 binmode STDERR, ':utf8';
15 # - detect moves between groups
16 # - message people when they sign up and we can't find them in the spreadsheet
18 require '../include/config.pm';
23 sub get_oauth_bearer_token {
26 my $jwt = JSON::XS::encode_json({
27 "iss" => $config::jwt_key->{'client_email'},
28 "scope" => "https://www.googleapis.com/auth/spreadsheets",
29 "aud" => "https://www.googleapis.com/oauth2/v4/token",
33 my $jws_token = Crypt::JWT::encode_jwt(payload=>$jwt, alg=>'RS256', key=>\$config::jwt_key->{'private_key'});
34 my $response = $ua->post('https://www.googleapis.com/oauth2/v4/token', [
35 'grant_type' => 'urn:ietf:params:oauth:grant-type:jwt-bearer',
36 'assertion' => $jws_token ]);
37 return JSON::XS::decode_json($response->decoded_content)->{'access_token'};
41 my ($ua, $userid) = @_;
42 my $req = HTTP::Request->new('GET', 'https://slack.com/api/users.info?user=' . $userid, [
43 'Authorization' => 'Bearer ' . $config::oauth_token
45 my $response = $ua->request($req);
46 die $response->status_line if !$response->is_success;
48 my $user_json = JSON::XS::decode_json($response->decoded_content);
49 die "Something went wrong: " . $response->decoded_content if (!defined($user_json) || !$user_json->{'ok'});
51 return $user_json->{'user'}{'real_name'};
54 sub get_spreadsheet_name {
56 my $name = $cell->{'userEnteredValue'}{'stringValue'};
57 return undef if (!defined($name));
58 return undef if ($name =~ /^G[1-4]\.[1-5]/);
63 $name =~ s/G\d\.\d?\??//;
71 my ($slack_name, $spreadsheet_name) = @_;
72 if (lc($slack_name) eq lc($spreadsheet_name)) {
76 my @ap = split /\s+/, $slack_name;
77 my @bp = split /\s+/, $spreadsheet_name;
78 if (scalar @ap >= 2 && scalar @bp >= 2 && lc($ap[0]) eq lc($bp[0])) {
79 # First name matches, try to match some surname
81 for my $ai (1..$#ap) {
82 for my $bi (1..$#bp) {
83 $found = 1 if (lc($ap[$ai]) eq lc($bp[$bi]));
87 skv_log("Fuzzy-matchet $slack_name -> $spreadsheet_name.");
95 sub format_cell_names_for_seen {
97 my @cells = map { chr(ord('A') + $_->[2]) . $_->[1] } @$seen;
98 return join(', ', @cells);
103 print STDERR "$msg\n";
107 sub serialize_skv_log_to_sheet {
112 userEnteredValue => { stringValue => join("\n", @log) }
115 fields => 'userEnteredValue.stringValue',
117 sheetId => $config::log_tab_id,
120 startColumnIndex => 0,
127 sub sheet_batch_update {
128 my ($ua, $token, @requests) = @_;
130 requests => \@requests
132 my $response = $ua->post(
133 'https://sheets.googleapis.com/v4/spreadsheets/' . $config::sheet_id . ':batchUpdate?key=' . $config::gsheets_api_key,
134 Content => JSON::XS::encode_json($update),
135 Content_type => 'application/json;charset=UTF-8',
136 Authorization => 'Bearer ' . $token
138 die $response->decoded_content if !$response->is_success;
141 sub get_spreadsheet_with_title {
142 my ($ua, $token, $wanted_sheet_title) = @_;
144 # See if we have any spreadsheets that match this title.
145 my $response = $ua->get('https://sheets.googleapis.com/v4/spreadsheets/' . $config::sheet_id . '?key=' . $config::gsheets_api_key . '&fields=sheets/properties',
146 Authorization => 'Bearer ' . $token
148 my $sheets_json = JSON::XS::decode_json($response->decoded_content);
149 my ($tab_name, $tab_id);
150 for my $sheet (@{$sheets_json->{'sheets'}}) {
151 my $title = $sheet->{'properties'}{'title'};
152 my $sheet_id = $sheet->{'properties'}{'sheetId'};
153 if ($title =~ /\Q$wanted_sheet_title\E/) {
154 # skv_log("Synkroniserer ($config::invitation_channel, $invitation_ts) mot arket ā$titleā (fane-ID $sheet_id).");
155 return ($title, $sheet_id);
158 return (undef, undef);
161 # Make a mapping of lowercase name -> list of [canonical name, row number, column number]
162 sub find_where_each_name_is {
166 my $rows = $json->{'sheets'}[0]{'data'}[0]{'rowData'};
168 for my $row (@$rows) {
170 for my $val (@{$row->{'values'}}) {
171 my $name = get_spreadsheet_name($val);
172 if (defined($name)) {
173 push @{$seen_names{lc $name}}, [$name, $rowno, $colno];
183 # Add the reaction log. (This only takes into account the last change
184 # for each user; earlier ones are irrelevant and don't count. But it
185 # doesn't deduplicate across reactions. Meh.)
186 sub create_reaction_log {
187 my ($dbh, $invitation_ts, $slack_userid_to_real_name, $slack_userid_to_slack_name) = @_;
189 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');
190 $q->execute($config::invitation_channel, $invitation_ts);
191 my @recent_changes = ();
192 while (my $ref = $q->fetchrow_hashref) {
193 my $msg = $ref->{'event_ts'};
194 if ($ref->{'event_type'} eq 'reaction_added') {
199 if ($ref->{'reaction'} eq 'open_mouth') {
205 if (exists($slack_userid_to_real_name->{$ref->{'userid'}})) {
206 $msg .= $slack_userid_to_real_name->{$ref->{'userid'}};
207 } elsif (exists($slack_userid_to_slack_name->{$ref->{'userid'}})) {
208 $msg .= $slack_userid_to_slack_name->{$ref->{'userid'}} . ' (fant ikke regneark-navn)';
210 # Should only happen if we didn't see the initial reaction_add, only reaction_remove.
211 $msg .= $ref->{'userid'} . ' (fant ikke Slack-navn)';
213 push @recent_changes, { values => [{ userEnteredValue => { stringValue => $msg } }] };
215 while (scalar @recent_changes < 50) {
216 push @recent_changes, { values => [{ userEnteredValue => { stringValue => '' } }] };
218 return @recent_changes;
221 # Also applies the diff to the database (a bit ugly).
223 my ($dbh, $invitation_ts, $want_names, $have_names, $seen_names) = @_;
226 for my $real_name (keys %$want_names) {
227 next if (exists($have_names->{$real_name}));
228 skv_log("Markerer at $real_name skal pƄ trening.");
240 $dbh->do('INSERT INTO applied (channel, ts, name) VALUES (?, ?, ?)', undef,
241 $config::invitation_channel, $invitation_ts, $real_name);
243 for my $real_name (keys %$have_names) {
244 next if (exists($want_names->{$real_name}));
245 if (!exists($seen_names->{lc $real_name})) {
246 # TODO: This can somehow come if we try to add someone who's not in the sheet, too?
247 skv_log("Ćnsket Ć„ fjerne at $real_name skulle pĆ„ trening, men de var ikke i regnearket lenger.");
249 skv_log("Fjerner at $real_name skal pƄ trening.");
261 $dbh->do('DELETE FROM applied WHERE channel=? AND ts=? AND name=?', undef,
262 $config::invitation_channel, $invitation_ts, $real_name);
270 skv_log("Siste sync startet: " . POSIX::ctime(time));
272 # Initialize the handles we need for communication.
273 $dbh = DBI->connect("dbi:Pg:dbname=$config::dbname;host=127.0.0.1", $config::dbuser, $config::dbpass, {RaiseError => 1})
274 or die "Could not connect to Postgres: " . DBI->errstr;
275 my $ua = LWP::UserAgent->new('SKVidarLang/1.0');
276 my $token = get_oauth_bearer_token($ua);
278 # Find the newest message, and what it is linked to.
279 # TODO: Support more than one, and test better for errors here.
280 my $q = $dbh->prepare('select * from message_sheet_link where channel=? order by ts desc limit 1');
281 $q->execute($config::invitation_channel);
282 my $linkref = $q->fetchrow_hashref;
283 my $invitation_ts = $linkref->{'ts'};
284 my $wanted_sheet_title = $linkref->{'sheet_title'};
285 die "Could not get newest sheet title" if (!defined($wanted_sheet_title));
287 my ($tab_name, $tab_id) = get_spreadsheet_with_title($ua, $token, $wanted_sheet_title);
288 if (!defined($tab_name)) {
289 skv_log("Fant ikke noen fane med ā$wanted_sheet_titleā i navnet; kan ikke synkronisere.\n");
290 sheet_batch_update($ua, $token, [ serialize_skv_log_to_sheet() ]);
294 # Find everyone who are marked as attending on Slack (via reactions).
295 $q = $dbh->prepare('SELECT DISTINCT userid FROM current_reactions WHERE channel=? AND ts=? AND reaction IN (\'heart\', \'open_mouth\')');
296 $q->execute($config::invitation_channel, $invitation_ts);
297 my @attending_userids = ();
298 while (my $ref = $q->fetchrow_hashref) {
299 push @attending_userids, $ref->{'userid'};
302 # Get the list of all people in the sheet (we're going to need them soon anyway).
303 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',
304 Authorization => 'Bearer ' . $token
306 my $main_sheet_json = JSON::XS::decode_json($response->decoded_content);
308 my %seen_names = find_where_each_name_is($main_sheet_json);
311 for my $name (sort keys %seen_names) {
312 my $seen = $seen_names{$name};
313 if (scalar @$seen >= 2) {
314 my $exemplar = $seen->[0][0];
315 skv_log("Duplikat: $exemplar (" . format_cell_names_for_seen($seen) . ")");
319 # Get our existing Slack->name mapping, from the sheets.
320 my %slack_userid_to_real_name = ();
321 my %slack_userid_to_slack_name = ();
322 my %slack_userid_to_row = ();
323 $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',
324 Authorization => 'Bearer ' . $token
326 my $mapping_sheet_json = JSON::XS::decode_json($response->decoded_content);
327 my $mapping_sheet_rows = $mapping_sheet_json->{'sheets'}[0]{'data'}[0]{'rowData'};
329 for my $row (@$mapping_sheet_rows) {
330 my $slack_id = $row->{'values'}[0]{'userEnteredValue'}{'stringValue'};
331 my $slack_name = $row->{'values'}[1]{'userEnteredValue'}{'stringValue'};
332 my $real_name = get_spreadsheet_name($row->{'values'}[2]); # TODO support more
333 $slack_userid_to_row{$slack_id} = $cur_row++;
334 next if (!defined($slack_name));
335 $slack_userid_to_slack_name{$slack_id} = $slack_name;
336 next if (!defined($real_name));
337 $slack_userid_to_real_name{$slack_id} = $real_name;
340 # See which ones we don't have a mapping for, and look them up in Slack.
341 # TODO: Use an append call instead of $cur_row?
342 my @slack_mapping_updates = ();
343 for my $userid (@attending_userids) {
344 next if (exists($slack_userid_to_real_name{$userid}));
346 # Make sure they have a row in the spreadsheet.
348 if (exists($slack_userid_to_row{$userid})) {
349 $write_row = $slack_userid_to_row{$userid};
351 $write_row = $cur_row++;
352 $slack_userid_to_row{$userid} = $write_row;
353 push @slack_mapping_updates, {
354 range => "Slack-mapping!A$write_row:A$write_row",
355 values => [ [ $userid ]]
359 # Fetch their Slack name if we don't already have it.
361 if (exists($slack_userid_to_slack_name{$userid})) {
362 $slack_name = $slack_userid_to_slack_name{$userid};
364 $slack_userid_to_slack_name{$userid} = $slack_name;
365 $slack_name = get_slack_name($ua, $userid);
366 push @slack_mapping_updates, {
367 range => "Slack-mapping!B$write_row:B$write_row",
368 values => [ [ $slack_name ]]
370 $slack_userid_to_slack_name{$userid} = $slack_name;
373 if (exists($seen_names{lc $slack_name})) {
374 # The name exists exactly, once or more, so it's a direct match and we ignore any fuzz.
375 $slack_userid_to_real_name{$userid} = $slack_name;
376 push @slack_mapping_updates, {
377 range => "Slack-mapping!C$write_row:C$write_row",
378 values => [ [ $slack_name ]]
381 # Do a search through all the available names in the sheet to find an obvious(ish) match.
383 my $main_sheet_rows = $main_sheet_json->{'sheets'}[0]{'data'}[0]{'rowData'};
384 for my $row (@$main_sheet_rows) {
385 for my $val (@{$row->{'values'}}) {
386 my $name = get_spreadsheet_name($val);
387 if (defined($name) && matches_name($slack_name, $name)) {
388 push @candidates, $name;
392 if ($#candidates == -1) {
393 skv_log("$slack_name ($userid) er pƄmeldt pƄ Slack, men fant ikke et regneark-navn for dem.");
394 } elsif ($#candidates == 0) {
395 my $name = $candidates[0];
396 $slack_userid_to_real_name{$userid} = $name;
397 push @slack_mapping_updates, {
398 range => "Slack-mapping!C$write_row:C$write_row",
399 values => [ [ $name ]]
402 skv_log("$slack_name ($userid) er pƄmeldt pƄ Slack, men hadde flere fuzzy-matcher; vet ikke hvilket regneark-navn som skal brukes.");
407 valueInputOption => 'USER_ENTERED',
408 data => \@slack_mapping_updates
410 $response = $ua->post(
411 'https://sheets.googleapis.com/v4/spreadsheets/' . $config::sheet_id . '/values:batchUpdate?key=' . $config::gsheets_api_key,
412 Content => JSON::XS::encode_json($update),
413 Content_type => 'application/json;charset=UTF-8',
414 Authorization => 'Bearer ' . $token
416 die $response->decoded_content if (!$response->is_success);
418 # Find the list of names to mark yellow.
420 my $main_sheet_rows = $main_sheet_json->{'sheets'}[0]{'data'}[0]{'rowData'};
421 for my $userid (@attending_userids) {
422 next if (!exists($slack_userid_to_real_name{$userid}));
423 my $slack_name = $slack_userid_to_slack_name{$userid};
424 my $real_name = $slack_userid_to_real_name{$userid};
426 # See if we can find them in the spreadsheet.
427 if (!exists($seen_names{lc $real_name})) {
428 # TODO: Perhaps move this logic further down, for consistency?
429 skv_log("$slack_name ($userid) er pƄmeldt pƄ Slack, og er mappet til $real_name, men var ikke i noen gruppe.");
431 my $seen = $seen_names{lc $real_name};
432 if (scalar @$seen >= 2) {
433 skv_log("$slack_name ($userid) er pƄmeldt pƄ Slack, men stƄr flere steder (se over); vet ikke hvilken celle som skal brukes.");
435 $want_names{$seen->[0][0]} = 1;
440 # Find the list of names we already marked yellow.
442 $dbh->{AutoCommit} = 0;
443 $dbh->do('SET TRANSACTION ISOLATION LEVEL SERIALIZABLE');
444 $q = $dbh->prepare('SELECT name FROM applied WHERE channel=? AND ts=?');
445 $q->execute($config::invitation_channel, $invitation_ts);
446 while (my $ref = $q->fetchrow_hashref) {
447 $have_names{$ref->{'name'}} = 1;
450 my @diffs = find_diff($dbh, $invitation_ts, \%want_names, \%have_names, \%seen_names);
452 my @yellow_updates = ();
453 if (scalar @diffs > 0) {
454 # Now fill in the actual stuff.
455 for my $diff (@diffs) {
456 my $real_name = $diff->[0];
458 # See if we can find them in the spreadsheet.
459 my $seen = $seen_names{lc $real_name};
460 die if (!defined($seen) || scalar @$seen > 1);
461 my $rowno = $seen->[0][1];
462 my $colno = $seen->[0][2];
463 push @yellow_updates, {
467 userEnteredFormat => $diff->[1]
470 fields => 'userEnteredFormat.backgroundColor',
473 startRowIndex => $rowno,
474 endRowIndex => $rowno + 1,
475 startColumnIndex => $colno,
476 endColumnIndex => $colno + 1
483 my @recent_changes = create_reaction_log($dbh, $invitation_ts, \%slack_userid_to_real_name, \%slack_userid_to_slack_name);
484 push @yellow_updates, {
486 rows => \@recent_changes,
487 fields => 'userEnteredValue.stringValue',
489 sheetId => $config::log_tab_id,
491 endRowIndex => 4 + scalar @recent_changes,
492 startColumnIndex => 0,
498 # Push the final set of updates (including the log).
500 push @yellow_updates, serialize_skv_log_to_sheet();
501 sheet_batch_update($ua, $token, \@yellow_updates);
505 if ($#ARGV >= 0 && $ARGV[0] eq '--daemon') {
507 if (!unlink("/srv/skvidar-slack.sesse.net/marker")) {
508 unless ($!{ENOENT}) {
509 warn "/srv/skvidar-slack.sesse.net/marker: $!";
518 warn "Died with: $@";