tool for sending file notifications
authorTony Cook <tony@develop-help.com>
Fri, 30 Oct 2009 03:16:44 +0000 (03:16 +0000)
committertony <tony@45cb6cf1-00bc-42d2-bb5a-07f51df49f94>
Fri, 30 Oct 2009 03:16:44 +0000 (03:16 +0000)
MANIFEST
schema/bse.sql
site/cgi-bin/modules/BSE/API.pm
site/cgi-bin/modules/BSE/ComposeMail.pm
site/cgi-bin/modules/BSE/NotifyFiles.pm [new file with mode: 0644]
site/cgi-bin/modules/BSE/TB/SiteUserGroups.pm
site/data/db/sql_statements.data
site/templates/user/notify_file.tmpl [new file with mode: 0644]
site/templates/user/notify_file_html.tmpl [new file with mode: 0644]
site/util/bse_notify_files.pl [new file with mode: 0644]
site/util/mysql.str

index 9c87551..ad389cc 100644 (file)
--- a/MANIFEST
+++ b/MANIFEST
@@ -93,6 +93,7 @@ site/cgi-bin/modules/BSE/Mail/SMTP.pm
 site/cgi-bin/modules/BSE/Mail/Sendmail.pm
 site/cgi-bin/modules/BSE/Message.pm
 site/cgi-bin/modules/BSE/NLFilter/SQL.pm
+site/cgi-bin/modules/BSE/NotifyFiles.pm
 site/cgi-bin/modules/BSE/Permissions.pm
 site/cgi-bin/modules/BSE/ProductImportXLS.pm
 site/cgi-bin/modules/BSE/Report.pm
@@ -595,6 +596,8 @@ site/templates/user/lostemailsent_base.tmpl
 site/templates/user/lostpassword_base.tmpl
 site/templates/user/lostpwdemail_base.tmpl
 site/templates/user/nopassword_base.tmpl
+site/templates/user/notify_file.tmpl
+site/templates/user/notify_file_html.tmpl
 site/templates/user/options_base.tmpl
 site/templates/user/options_billing_base.tmpl
 site/templates/user/options_images_base.tmpl
@@ -609,6 +612,7 @@ site/templates/user/unsubone_base.tmpl
 site/templates/user/userpage_base.tmpl
 site/templates/xbase.tmpl
 site/util/bseaddimages.pl
+site/util/bse_notify_files.pl
 site/util/bse_s3.pl
 site/util/bse_storage.pl
 site/util/bsexlsprod.pl
index 56d1df2..cfec520 100644 (file)
@@ -912,9 +912,12 @@ create table bse_file_subscriptions (
 drop table if exists bse_file_notifies;
 create table bse_file_notifies (
   id integer not null auto_increment primary key,
-  siteuser_id integer not null,
+  owner_type char not null,
+  owner_id integer not null,
   file_id integer not null,
-  index by_siteuser(siteuser_id)
+  when_at datetime not null,
+  index by_owner(owner_type, owner_id),
+  index by_time(owner_type, when_at)
 );
 
 drop table if exists bse_file_access_log;
index 337f11a..2e82f15 100644 (file)
@@ -325,7 +325,13 @@ sub bse_add_owned_file {
   $opts{filename} = $saved_name;
   
   require BSE::TB::OwnedFiles;
-  return BSE::TB::OwnedFiles->make(%opts);
+  my $result = BSE::TB::OwnedFiles->make(%opts);
+
+  if ($cfg->entry('notify_files', 'active', 0)) {
+    BSE::DB->run(bseAddOwnedFileNotification => $result->id, $owner->file_owner_type, $owner->id);
+  }
+
+  return $result;
 }
 
 sub bse_delete_owned_file {
index 7bc83ca..34ee499 100644 (file)
@@ -388,6 +388,7 @@ sub done {
     (
      %{$self->{acts}},
      resource => [ tag_resource => $self ],
+     set_subject => [ tag_set_subject => $self ],
     ); # 
 
   my $message;
@@ -513,4 +514,14 @@ sub tag_resource {
   return $url;
 }
 
+sub tag_set_subject {
+  my ($self, $args, $acts, $tag, $templater) = @_;
+
+  my @args = DevHelp::Tags->get_parms($args, $acts, $templater);
+
+  $self->{subject} = "@args";
+
+  return '';
+}
+
 1;
diff --git a/site/cgi-bin/modules/BSE/NotifyFiles.pm b/site/cgi-bin/modules/BSE/NotifyFiles.pm
new file mode 100644 (file)
index 0000000..4b0b46e
--- /dev/null
@@ -0,0 +1,199 @@
+package BSE::NotifyFiles;
+use strict;
+use BSE::ComposeMail;
+use BSE::DB;
+use BSE::TB::OwnedFiles;
+use SiteUsers;
+use BSE::Util::Tags qw(tag_hash_plain);
+use DevHelp::Tags::Iterate;
+use BSE::TB::SiteUserGroups;
+use Carp qw(confess);
+
+sub new {
+  my ($class, %opts) = @_;
+
+  $opts{cfg} and $opts{cfg}->can("entry")
+    or confess "Missing error option\n";
+
+  return bless \%opts, $class;
+}
+
+sub run {
+  my ($self) = @_;
+
+  $self->_lock
+    or die "Cannot acquire notify_files lock\n";
+
+  $self->_clear_obsolete;
+
+  $self->_expand_groups;
+
+  $self->_send_messages;
+
+  $self->_unlock;
+}
+
+sub expand_groups {
+  my ($self) = @_;
+
+  $self->lock
+    or die "Cannot acquire notify_files lock\n"
+;
+  $self->_clear_obsolete;
+
+  $self->_expand_groups;
+
+  $self->_unlock;
+}
+
+sub _clear_obsolete {
+  my ($self) = @_;
+
+  my $max_age = $self->{cfg}->entry("notify files", "oldest_notify", 7);
+  BSE::DB->run(bseClearOldFileNotifications => $max_age);
+}
+
+sub _expand_groups {
+  my ($self) = @_;
+
+  # a few at a time
+  while (my @group_entries = BSE::DB->query("bseNotifyFileGroupEntries")) {
+    for my $group_entry (@group_entries) {
+      my $file = BSE::TB::OwnedFiles->getByPkey($group_entry->{file_id});
+      if ($file) {
+       $self->_expand_group($group_entry, $file);
+      }
+      BSE::DB->run(bseDeleteFileNotification => $group_entry->{id});
+    }
+  }
+}
+
+sub _expand_group {
+  my ($self, $entry, $file) = @_;
+
+  if ($entry->{owner_id} > 0) {
+    BSE::DB->run(bseExpandGroupFileNotification => $entry->{id});
+  }
+  else {
+    my $group = BSE::TB::SiteUserGroups->getQueryGroup($self->{cfg}, $entry->{owner_id})
+      or return;
+    for my $id ($group->member_ids) {
+      BSE::DB->run(bseAddOwnedFileNotificationTime => $entry->{file_id}, "U", $id, $entry->{when_at});
+    }
+  }
+}
+
+sub _send_messages {
+  my ($self) = @_;
+
+  my @user_ids = map $_->{id}, BSE::DB->query("bseFileNotifyUsers");
+  for my $user_id (@user_ids) {
+    $self->_notify_user($user_id);
+  }
+}
+
+sub _notify_user {
+  my ($self, $user_id) = @_;
+
+  my @orig_entries = BSE::DB->query(bseFileNotifyUserEntries => $user_id);
+  if (@orig_entries) {
+    my $user = SiteUsers->getByPkey($user_id);
+    if ($user) {
+      $self->_notify_user_low($user_id, $user, \@orig_entries);
+    }
+    for my $entry (@orig_entries) {
+      BSE::DB->run(bseDeleteFileNotification => $entry->{id});
+    }
+  }
+}
+
+sub _notify_user_low {
+  my ($self, $user_id, $user, $orig_entries) = @_;
+
+  # we keep the original entry list, since we want to delete them all
+  # at the end, but we don't want to delete entries added after we
+  # started processing the user.
+  my %cats = map { $_ => 1 } $user->subscribed_file_categories;
+  my @unique_entries;
+  my %seen;
+  for my $entry (@$orig_entries) {
+    $seen{$entry->{file_id}}++ and next;
+    $entry->{file} = BSE::TB::OwnedFiles->getByPkey($entry->{file_id})
+      or next;
+    # in case the user stopped subscribing
+    $cats{$entry->{file}{category}}
+      or next;
+    push @unique_entries, $entry;
+  }
+  @unique_entries
+    or return;
+
+  my @files;
+  my %catnames = map { $_->{id} => $_->{name} }
+    BSE::TB::OwnedFiles->categories($self->{cfg});
+  for my $entry (@unique_entries) {
+    my $file = $entry->{file}->data_only;
+    $file->{catname} = $catnames{$file->{category}} || $file->{category};
+    push @files, $file;
+  }
+
+  my $it = DevHelp::Tags::Iterate->new;
+  my %acts =
+    (
+     BSE::Util::Tags->static(undef, $self->{cfg}),
+     user => [ \&tag_hash_plain, $user ],
+     $it->make
+     (
+      single => "file",
+      plural => "files",
+      data => \@files,
+     ),
+    );
+  my $mailer = BSE::ComposeMail->new(cfg => $self->{cfg});
+  $mailer->send(to => $user,
+               subject => "You have files waiting",
+               template => "user/notify_file",
+               acts => \%acts);
+}
+
+sub _lockname {
+  my ($self) = @_;
+
+  my $cfg = $self->{cfg};
+  return $cfg->entry("notify files", "lockname",
+                    $cfg->entry("site", "url") . "_notify_files");
+}
+
+sub _lock {
+  my ($self, $wait) = @_;
+
+  defined $wait or $wait = 3600;
+  my $dbh = BSE::DB->single->dbh;
+  local $dbh->{PrintWarn} = 0;
+  my $row = $dbh->selectrow_arrayref
+    ("select get_lock(?, ?)", undef, $self->_lockname, $wait)
+      or return;
+  $row->[0]
+    or return;
+  return 1;
+}
+
+sub _unlock {
+  my ($self) = @_;
+
+  my $dbh = BSE::DB->single->dbh;
+  $dbh->selectrow_arrayref("select release_lock(?)", undef, $self->_lockname)
+    or die "Cannot release lock\n";
+}
+
+# returns true if the lock is held
+sub testlock {
+  my ($self) = @_;
+
+  my $dbh = BSE::DB->single->dbh;
+  my $row = $dbh->selectrow_arrayref("select is_free_lock(?)", undef, $self->_lockname)
+    or die "Cannot retrieve lock status: ", $dbh->errstr, "\n";
+  return !$row->[0];
+}
+
+1;
index be9aff1..809e2c6 100644 (file)
@@ -17,7 +17,7 @@ sub query_groups {
   while ($name = $cfg->entry(SECT_QUERY_GROUPS, $id)) {
     my $group = $class->getQueryGroup($cfg, -$id);
     $group and push @groups, $group;
-      
+
     ++$id;
   }
 
@@ -39,11 +39,18 @@ sub getQueryGroup {
 
   my $name = $cfg->entry(SECT_QUERY_GROUPS, -$id)
     or return;
-  my $sql = $cfg->entry(SECT_QUERY_GROUP_PREFIX.$name, 'sql')
+  my $section = SECT_QUERY_GROUP_PREFIX . $name;
+  my $sql = $cfg->entry($section, 'sql')
     or return;
-
-  return bless { id => $id, name => "*$name", sql=>$sql }, 
-    "BSE::TB::SiteUserQueryGroup";
+  my $sql_all = $cfg->entry($section, 'sql_all');
+
+  return bless
+    {
+     id => $id,
+     name => "*$name",
+     sql=>$sql,
+     sql_all => $sql_all,
+    }, "BSE::TB::SiteUserQueryGroup";
 }
 
 sub getByName {
@@ -68,6 +75,7 @@ sub getByName {
 
 package BSE::TB::SiteUserQueryGroup;
 use constant OWNER_TYPE => "G";
+use Carp qw(confess);
 
 sub id { $_[0]{id} }
 
@@ -85,6 +93,22 @@ sub contains_user {
   return 0;
 }
 
+sub member_ids {
+  my ($self) = @_;
+
+  if ($self->{sql_all}) {
+    my $dbh = BSE::DB->single->dbh;
+    my $values = $dbh->selectcol_arrayref($self->{sql_all});
+    $values
+      or confess "Cannot execute $self->{sql_all}: ", $dbh->errstr, "\n";
+
+    return @$values;
+  }
+  else {
+    return grep $self->contains_user($_), SiteUsers->all_ids;
+  }
+}
+
 sub file_owner_type {
   return OWNER_TYPE;
 }
index afe31b7..393ea78 100644 (file)
@@ -123,4 +123,71 @@ name: bseDownloadLogAge
 sql_statement: <<SQL
 delete from bse_file_access_log
  where when_at < date_sub(now(), interval ? day)
-SQL
\ No newline at end of file
+SQL
+
+name: bseClearOldFileNotifications
+sql_statement: <<SQL
+delete from bse_file_notifies
+  where when_at < date_sub(now(), interval ? day)
+SQL
+
+name: bseAddOwnedFileNotification
+sql_statement: <<SQL
+insert into bse_file_notifies(file_id, owner_type, owner_id, when_at)
+  values(?, ?, ?, now())
+SQL
+
+name: bseAddOwnedFileNotificationTime
+sql_statement: <<SQL
+insert into bse_file_notifies(file_id, owner_type, owner_id, when_at)
+  values(?, ?, ?, ?)
+SQL
+
+name: baseClearOldFileNotifications
+sql_statement: <<SQL
+delete from bse_file_notifies
+ where when_at < date_sub(now(), interval ? day)
+SQL
+
+name: bseDeleteFileNotification
+sql_statement: <<SQL
+delete from bse_file_notifies where id = ?
+SQL
+
+name: bseExpandGroupFileNotification
+sql_statement: <<SQL
+insert into bse_file_notifies(file_id, owner_type, owner_id, when_at)
+select fn.file_id, 'U', sm.siteuser_id, fn.when_at
+from bse_file_notifies fn,
+     bse_siteuser_membership sm,
+     bse_owned_files fi,
+     bse_file_subscriptions fs
+where fn.id = ?
+  and fi.id = fn.file_id
+  and fn.owner_id = sm.group_id
+  and fs.siteuser_id = fs.siteuser_id
+  and fs.category = fi.category;
+SQL
+
+name: bseNotifyFileGroupEntries
+sql_statement: <<SQL
+select * from bse_file_notifies
+where owner_type = 'G'
+order by when_at
+limit 100
+SQL
+
+name: bseFileNotifyUsers
+sql_statement: <<SQL
+select distinct owner_id as id
+from bse_file_notifies
+where owner_type = 'U'
+SQL
+
+name: bseFileNotifyUserEntries
+sql_statement: <<SQL
+select *
+from bse_file_notifies
+where owner_type = 'U'
+  and owner_id = ?
+SQL
diff --git a/site/templates/user/notify_file.tmpl b/site/templates/user/notify_file.tmpl
new file mode 100644 (file)
index 0000000..b819255
--- /dev/null
@@ -0,0 +1,11 @@
+Hello <:ifUser name1:><:user name1:><:or:><:user userId:><:eif:>,
+
+New content has been uploaded to your account:
+
+<:iterator begin files:>
+<:file display_name:> (<:kb file size_in_bytes:>bytes)
+  Title: <:file title:>
+  Category: <:file catname:>
+<:iterator end files:>
+
+<:set_subject [concatenate [file_count] " new files waiting"]:>
\ No newline at end of file
diff --git a/site/templates/user/notify_file_html.tmpl b/site/templates/user/notify_file_html.tmpl
new file mode 100644 (file)
index 0000000..ea4800f
--- /dev/null
@@ -0,0 +1,14 @@
+<html><body>
+<p>Hello <:ifUser name1:><:user name1 |h:><:or:><:user userId |h:><:eif:>,</p>
+
+<p>New content has been uploaded to your account:</p>
+
+<ul>
+<:iterator begin files:>
+<li><:file display_name |h:> (<:kb file size_in_bytes:>)<br />
+  Title: <:file title |h:><br />
+  Category: <:file catname |h:></li>
+<:iterator end files:>
+
+</ul>
+</body></html>
diff --git a/site/util/bse_notify_files.pl b/site/util/bse_notify_files.pl
new file mode 100644 (file)
index 0000000..152956e
--- /dev/null
@@ -0,0 +1,27 @@
+#!perl -w
+use strict;
+use Getopt::Long;
+use FindBin;
+use lib "$FindBin::Bin/../cgi-bin/modules";
+use BSE::API qw(bse_cfg);
+use BSE::NotifyFiles;
+
+chdir "$FindBin::Bin/../cgi-bin"
+  or warn "Could not change to cgi-bin directory: $!\n";
+Getopt::Long::Configure('bundling');
+my $verbose;
+GetOptions("v", \$verbose);
+$verbose = defined $verbose;
+
+my $cfg = bse_cfg;
+
+my $notifier = BSE::NotifyFiles->new
+  (
+   verbose => $verbose,
+   output => sub { print "@_\n" },
+   error => sub { print STDERR "@_\n" },
+   cfg => $cfg,
+  );
+
+$notifier->run;
+
index c265b49..94a7538 100644 (file)
@@ -124,10 +124,13 @@ Index by_user;0;[siteuser_id;when_at]
 Index by_when_at;0;[when_at]
 Table bse_file_notifies
 Column id;int(11);NO;NULL;auto_increment
-Column siteuser_id;int(11);NO;NULL;
+Column owner_type;char(1);NO;NULL;
+Column owner_id;int(11);NO;NULL;
 Column file_id;int(11);NO;NULL;
+Column when_at;datetime;NO;NULL;
 Index PRIMARY;1;[id]
-Index by_siteuser;0;[siteuser_id]
+Index by_owner;0;[owner_type;owner_id]
+Index by_time;0;[owner_type;when_at]
 Table bse_file_subscriptions
 Column id;int(11);NO;NULL;
 Column siteuser_id;int(11);NO;NULL;