historic/mailusers/mailusers.pl

   1 #!/usr/bin/perl
   2
   3 require "/home/lj/cgi-bin/ljlib.pl";
   4
   5 $newsnum = $ARGV[0];
   6
   7 unless ($newsnum =~ /^\d+$/) {
   8     die "Need argv[0] = newsnum!\n";
   9 }
  10
  11 $base = "";
  12 while (<STDIN>) {
  13     $base .= $_;
  14 }
  15
  16 #$where = "u.allow_getljnews='Y' AND u.status='A' AND u.statusvis='V'";
  17
  18 $where = "u.paidfeatures IN ('early', 'off') AND u.status='A' AND u.statusvis='V' AND u.timeupdate > DATE_SUB(NOW(), INTERVAL 14 DAY)";
  19
  20 #$where = "u.status='A' AND u.statusvis='V' AND u.country='US' and u.state IN ('OR', 'WA', 'ID') AND u.timeupdate > DATE_SUB(NOW(), INTERVAL 21 DAY)";
  21
  22 #$where = "u.userid=1";
  23 #$where = "u.status='A' AND u.timeupdate > DATE_SUB(NOW(), INTERVAL 7 DAY)";
  24 #$where = "u.status='A' AND (u.allow_getljnews='Y' OR u.lastn_style NOT IN (1, 2, 3, 4, 5, 20, 6) OR u.friends_style NOT IN (1, 2, 3, 4, 5, 20, 6))";
  25
  26 my $ct = 0;
  27 &connect_db;
  28 $sth = $dbh->prepare("SELECT u.user FROM user u WHERE $where");
  29 $sth->execute;
  30 if ($dbh->err) { print $dbh->errstr; }
  31
  32 my @users;
  33 push @users, $_->{'user'} while ($_ = $sth->fetchrow_hashref);
  34
  35 my ($count) = scalar(@users);
  36 print "Count: $count\n";
  37
  38 foreach my $user (@users)
  39 {
  40     $ct++;
  41     print "$ct/$count: ";
  42
  43     my $quser = $dbh->quote($user);
  44     $sth = $dbh->prepare("SELECT COUNT(*) AS 'sent' FROM news_sent WHERE user=$quser AND newsnum=$newsnum");
  45     $sth->execute;
  46     my ($sent) = $sth->fetchrow_array;
  47     if ($sent) {
  48         print "$user: skipping.\n";
  49         next;
  50     } else {
  51         $sth = $dbh->prepare("SELECT userid, user, email, name, timeupdate FROM user WHERE user=$quser");
  52         $sth->execute;
  53         $c = $sth->fetchrow_hashref;
  54     }
  55
  56 #    my $aa = &register_authaction($c->{'userid'}, "nonews");
  57 #    next unless $aa;
  58 #    $c->{'authid'} = $aa->{'aaid'};
  59 #    $c->{'authcode'} = $aa->{'authcode'};
  60
  61     $msg = $base;
  62     $msg =~ s/\[(\w+?)\]/$c->{$1}/g;
  63     open (MAIL, "|$SENDMAIL");
  64     print MAIL $msg;
  65     close MAIL;
  66
  67     ### log the spam
  68     my $quser = $dbh->quote($c->{'user'});
  69     my $qemail = $dbh->quote($c->{'email'});
  70     $dbh->do("INSERT INTO news_sent (newsnum, user, datesent, email) VALUES ($newsnum, $quser, NOW(), $qemail)");
  71     if ($dbh->err) { die $dbh->errstr; }
  72
  73     print "mailed $user ($c->{'email'})...\n";
  74 }
  75
  76 print "Done.\n";