From 1cabc655d58b34f049a7cf905d5e946fbcc4f93b Mon Sep 17 00:00:00 2001 From: "Steinar H. Gunderson" Date: Fri, 27 Oct 2023 16:12:57 +0200 Subject: [PATCH] Support non-fixed sheets and messages to sync. --- bin/sync.pl | 117 ++++++++++++++++++++++++++++++---------------- include/config.pm | 4 -- www/event.pl | 2 +- 3 files changed, 78 insertions(+), 45 deletions(-) diff --git a/bin/sync.pl b/bin/sync.pl index b26d90f..8951243 100644 --- a/bin/sync.pl +++ b/bin/sync.pl @@ -103,24 +103,89 @@ sub skv_log { push @log, $msg; } +sub serialize_skv_log_to_sheet { + return { + updateCells => { + rows => [{ + values => [{ + userEnteredValue => { stringValue => join("\n", @log) } + }] + }], + fields => 'userEnteredValue.stringValue', + range => { + sheetId => $config::log_tab_id, + startRowIndex => 0, + endRowIndex => 1, + startColumnIndex => 0, + endColumnIndex => 1 + } + } + }; +} + +sub sheet_batch_update { + my ($ua, $token, @requests) = @_; + my $update = { + requests => \@requests + }; + my $response = $ua->post( + 'https://sheets.googleapis.com/v4/spreadsheets/' . $config::sheet_id . ':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; +} + 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)); + +# See if we have any spreadsheets that match this title. +my $response = $ua->get('https://sheets.googleapis.com/v4/spreadsheets/' . $config::sheet_id . '?key=' . $config::gsheets_api_key . '&fields=sheets/properties', + Authorization => 'Bearer ' . $token +); +my $sheets_json = JSON::XS::decode_json($response->decoded_content); +my ($tab_name, $tab_id); +for my $sheet (@{$sheets_json->{'sheets'}}) { + my $title = $sheet->{'properties'}{'title'}; + my $sheet_id = $sheet->{'properties'}{'sheetId'}; + if ($title =~ /\Q$wanted_sheet_title\E/) { + skv_log("Synkroniserer ($config::invitation_channel, $invitation_ts) mot arket “$title” (fane-ID $sheet_id)."); + $tab_name = $title; + $tab_id = $sheet_id; + last; + } +} +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). -my $q = $dbh->prepare('SELECT DISTINCT userid FROM current_reactions WHERE channel=? AND ts=? AND reaction IN (\'heart\', \'open_mouth\')'); -$q->execute($config::invitation_channel, $config::invitation_ts); +$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=' . $config::tab_name . '!A9:Z5000&fields=sheets/data/rowData/values/userEnteredValue', +$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); @@ -277,7 +342,7 @@ 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, $config::invitation_ts); +$q->execute($config::invitation_channel, $invitation_ts); while (my $ref = $q->fetchrow_hashref) { $have_names{$ref->{'name'}} = 1; } @@ -299,7 +364,7 @@ for my $real_name (keys %want_names) { } ]; $dbh->do('INSERT INTO applied (channel, ts, name) VALUES (?, ?, ?)', undef, - $config::invitation_channel, $config::invitation_ts, $real_name); + $config::invitation_channel, $invitation_ts, $real_name); } for my $real_name (keys %have_names) { next if (exists($want_names{$real_name})); @@ -316,7 +381,7 @@ for my $real_name (keys %have_names) { } ]; $dbh->do('DELETE FROM applied WHERE channel=? AND ts=? AND name=?', undef, - $config::invitation_channel, $config::invitation_ts, $real_name); + $config::invitation_channel, $invitation_ts, $real_name); } my @yellow_updates = (); @@ -343,7 +408,7 @@ if (scalar @diffs > 0) { }], fields => 'userEnteredFormat.backgroundColor', range => { - sheetId => $config::tab_id, + sheetId => $tab_id, startRowIndex => $rowno, endRowIndex => $rowno + 1, startColumnIndex => $colno, @@ -365,32 +430,11 @@ if (scalar @diffs > 0) { } } -skv_log("Ferdig."); - -# Add the log. -push @yellow_updates, { - updateCells => { - rows => [{ - values => [{ - userEnteredValue => { stringValue => join("\n", @log) } - }] - }], - fields => 'userEnteredValue.stringValue', - range => { - sheetId => $config::log_tab_id, - startRowIndex => 0, - endRowIndex => 1, - startColumnIndex => 0, - endColumnIndex => 1 - } - } -}; - # 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, $config::invitation_ts); +$q->execute($config::invitation_channel, $invitation_ts); my @recent_changes = (); while (my $ref = $q->fetchrow_hashref) { my $msg = $ref->{'event_ts'}; @@ -426,14 +470,7 @@ push @yellow_updates, { }; # Push the final set of updates (including the log). -$update = { - requests => \@yellow_updates -}; -$response = $ua->post( - 'https://sheets.googleapis.com/v4/spreadsheets/' . $config::sheet_id . ':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; +skv_log("Ferdig."); +push @yellow_updates, serialize_skv_log_to_sheet(); +sheet_batch_update($ua, $token, \@yellow_updates); $dbh->commit; diff --git a/include/config.pm b/include/config.pm index 4f901ec..5c7d1f4 100644 --- a/include/config.pm +++ b/include/config.pm @@ -16,9 +16,6 @@ our $jwt_key = { "client_x509_cert_url" => "https://www.googleapis.com/robot/v1/metadata/x509/ultimate-nm-2018%40solskogen-cubemap.iam.gserviceaccount.com" }; our $sheet_id = '1-alcvuB3184rw3pWLcvYjc2d6CnVGpMuvZYMYxn7Cgw'; -#my $tab_id = '1739147411'; -our $tab_id = '0'; -our $tab_name = 'Neste'; our $log_tab_id = '253633356'; our $gsheets_api_key; our $dbname = 'skvidarlang'; @@ -26,7 +23,6 @@ our $dbuser = 'skvidarlang'; our $dbpass; our $oauth_token; our $invitation_channel = 'C062GSYQ3C6'; -our $invitation_ts = '1698239546.063769'; # Read in a local configuration file if it exists. eval { diff --git a/www/event.pl b/www/event.pl index 04e7cda..c99da21 100755 --- a/www/event.pl +++ b/www/event.pl @@ -51,7 +51,7 @@ if (exists($json->{'event'}) && exists($json->{'event'}{'type'})) { # TODO: What if edits happen out-of-order? my $date = $1; my $channel = $json->{'event'}{'channel'}; - my $ts = $json->{'event'}{'ts'}; + my $ts = $json->{'event'}{'message'}{'ts'}; print "Matching message {$channel, $ts} to date $date\n"; $dbh->do('INSERT INTO message_sheet_link (channel, ts, sheet_title) VALUES (?,?,?)', undef, $channel, $ts, $date); -- 2.39.2