Support non-fixed sheets and messages to sync.

diff --git a/bin/sync.pl b/bin/sync.pl
index b26d90fd82bac12afa8c8fcdc6add728fa9eed08..89512436e4eddfdab0c4aac1f0a8e44727ed605a 100644 (file)
--- 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 4f901ec750e1f922537fab1bad4959f68c85b5ae..5c7d1f4cb39f3c720fb0016500c0e769a8508682 100644 (file)
--- 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 04e7cdaa5ab11fb252bd3f92bcc86d3270469582..c99da216af9df343724de87f9a5a023d1b3ee62b 100755 (executable)
--- 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);