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
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
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
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;
$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 {
(
%{$self->{acts}},
resource => [ tag_resource => $self ],
+ set_subject => [ tag_set_subject => $self ],
); #
my $message;
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;
--- /dev/null
+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;
while ($name = $cfg->entry(SECT_QUERY_GROUPS, $id)) {
my $group = $class->getQueryGroup($cfg, -$id);
$group and push @groups, $group;
-
+
++$id;
}
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 {
package BSE::TB::SiteUserQueryGroup;
use constant OWNER_TYPE => "G";
+use Carp qw(confess);
sub id { $_[0]{id} }
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;
}
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
--- /dev/null
+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
--- /dev/null
+<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>
--- /dev/null
+#!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 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;
projects / bse.git / commitdiff