[ljcom] r9706: LJSUP-7213: E-mail subscription with sta...
Committer: dnikolaev
LJSUP-7213: E-mail subscription with stats data of ru sms notificationsU trunk/bin/misc/smsru_stats.pl
Modified: trunk/bin/misc/smsru_stats.pl
===================================================================
--- trunk/bin/misc/smsru_stats.pl 2010-11-13 10:56:10 UTC (rev 9705)
+++ trunk/bin/misc/smsru_stats.pl 2010-11-15 04:08:30 UTC (rev 9706)
@@ -1,7 +1,7 @@
#!/usr/bin/perl
# This script will query each cluser to pull time, count, and security information for
-# phoneposts. The aggregated stats are output to 'phonepost_stats.txt';
+# phoneposts. The aggregated stats are output to 'phonepost_stats.txt';
use strict;
use lib "$ENV{LJHOME}/cgi-bin";
@@ -10,92 +10,253 @@
use LJ::DBUtil;
use LJ::NotificationMethod;
-my @stat_recipients = ('recipient@mydomain.com');
+my $SECONDS_IN_DAY = 86400;
+my $DAYS_IN_PERIOD = 7;
+my @OPERATORS = qw(beeline megafon mts total);
-my %stat_total = ();
+my @stat_recipients = ('dnikolaev@sup.com', 'lj_rusms_notifications_stats@sup.com');
-use Data::Dumper;
+my $now = time();
+my $time_from = $now - $SECONDS_IN_DAY * $DAYS_IN_PERIOD;
+my $date_from = to_date($time_from);
+my $date_to = to_date($now); # not including
+my %USERS;
+my %PHONES;
+
+my $stats_sms = {};
+my $stats_users = {};
+my $stats_phones = {};
+
# iterate over all clusters and query for stats
foreach my $cid (@LJ::CLUSTERS) {
- print "querying cluster $cid... ";
+ print STDERR "Querying cluster $cid...\n";
my $udb = LJ::DBUtil->connect_to_cluster($cid, 1)
- or die "unable to contact database for cluster: $cid\n";
+ or die "Unable to connect database for cluster: $cid\n";
- my $sth = $udb->prepare("SELECT userid, to_number, msg, DATE_FORMAT(added, '\%Y-\%m-\%d') as added, msg_type FROM smsru WHERE added > DATE_SUB(NOW(), INTERVAL 1 WEEK)");
+ my $sth = $udb->prepare(
+ "SELECT userid, to_number, msg, DATE_FORMAT(added, '\%Y-\%m-\%d') as added, msg_type " .
+ "FROM smsru " .
+ "WHERE added >= '$date_from' AND added < '$date_to';"
+ );
$sth->execute;
while (my ($userid, $to_number, $msg, $added, $msg_type) = $sth->fetchrow_array) {
+
next unless $msg_type =~ /[VI]/;
- my $u = LJ::load_userid($userid);
- my $is_subscribed = 0;
- foreach my $event (qw/CommentReply CommunityEntryReply JournalNewComment/) {
- my %params = (event => $event, ntypeid => LJ::NotificationMethod->method_to_ntypeid ('SMSru'));
- $is_subscribed = 1 if $u->has_subscription(%params);
+
+ my $provider_name = get_provider_name($to_number) || '';
+
+ my $stats_day = $stats_sms->{$added};
+ unless ($stats_day) {
+ $stats_day = {};
+ $stats_sms->{$added} = $stats_day;
}
- $stat_total{$added}{'subs'}{$userid}++ unless $is_subscribed;
- $stat_total{$added}{'V'}++ if $msg_type eq 'V';
- $stat_total{$added}{'I'}++ if $msg_type eq 'I';
- $stat_total{$added}{'users'}{$userid}++;
- $stat_total{$added}{'phones'}{$to_number}++;
- $stat_total{$added}{'count'}++;
- my ($def) = $to_number =~ /^7(\d\d\d)\d{7}$/;
- my $op = $LJ::SMSRU{supported_DEF}->{$def};
- $stat_total{$added}{'OPS'}{$op}++;
+
+ # Total number of all SMS
+ $stats_day->{"P=total"}++;
+ $stats_day->{"P=$provider_name"}++;
+
+ # Number of SMS by different types ('V' or 'I')
+ $stats_day->{"T=$msg_type&P=total"}++;
+ $stats_day->{"T=$msg_type&P=$provider_name"}++;
}
}
-my %total = ();
my $dbh = LJ::get_db_reader();
-my $sth = $dbh->prepare("SELECT userid, phone, status, DATE_FORMAT(added, '\%Y-\%m-\%d') as added FROM smsru_phones WHERE DATE_SUB(added, INTERVAL 1 WEEK) AND added IS NOT NULL");
+
+my $sth = $dbh->prepare(
+ "SELECT userid, phone, status, DATE_FORMAT(added, '\%Y-\%m-\%d') as added " .
+ "FROM smsru_phones;"
+);
$sth->execute;
+
while (my ($userid, $phone, $status, $added) = $sth->fetchrow_array) {
- $total{$added}{'users'}{$userid}++ if $status eq 'verified';
- $total{$added}{'users_n'}{$phone}++ if $status ne 'verified';
-}
-my $send_message = '';
+ my $is_subscribed = is_user_subscribed($userid);
+ my $provider_name = get_provider_name($phone) || '';
+ my $is_verified = ($status eq 'verified' ? 1 : 0);
-$send_message .= "DAYLY:\n";
-foreach my $yearmoday (sort (keys %total, keys %stat_total)) {
- $send_message .= " $yearmoday:\n";
- $send_message .= "\t" x 1;
- my @users = keys %{$total{$yearmoday}{'users'}};
- $send_message .= "Unique users with verified phone number: " . (@users || 0) . "\n";
- unless (scalar keys %{$stat_total{$yearmoday}{'subs'}} == 0) {
- $send_message .= "\t" x 1;
- $send_message .= "Percentage of unique users with verified number, but who not subscribed to any one notice: " . ((scalar @users / scalar keys %{$stat_total{$yearmoday}{'subs'}} ) || 0) . "\n";
+ $stats_users->{"S=$status&P=total"}->{$userid}++;
+ $stats_users->{"S=$status&P=$provider_name"}->{$userid}++;
+
+ if ($status eq 'verified' && !$is_subscribed) {
+ $stats_users->{"S=verified&P=total¬_subscribed"}->{$userid}++;
+ $stats_users->{"S=verified&P=$provider_name¬_subscribed"}->{$userid}++;
}
- $send_message .= "\t" x 1;
- $send_message .= "Unique users of Beeline with a verified phone number: " . ($stat_total{$yearmoday}{'OPS'}{'BE'} || 0). "\n";
- $send_message .= "\t" x 1;
- $send_message .= "Unique users of Megafon with a verified phone number: " . ($stat_total{$yearmoday}{'OPS'}{'MF'} || 0). "\n";
- unless ($stat_total{$yearmoday}{'I'} == 0) {
- $send_message .= "\t" x 1;
- $send_message .= "All SMS / only comments events (over the day): " . ($stat_total{$yearmoday}{'count'} . " / " . $stat_total{$yearmoday}{'I'} || 0). "\n";
+
+ if ($status eq 'verification_asked' ||
+ $status eq 'verification_outdated' ||
+ $status eq 'verification_sms_sended') {
+ $stats_users->{"S=wait_verification&P=total"}->{$userid}++;
+ $stats_users->{"S=wait_verification&P=$provider_name"}->{$userid}++;
+ $stats_phones->{"S=wait_verification&P=total"}->{$phone}++;
+ $stats_phones->{"S=wait_verification&P=$provider_name"}->{$phone}++;
}
- my @stat_users = keys %{$stat_total{$yearmoday}{'users'}};
- unless (scalar @stat_users == 0) {
- $send_message .= "\t" x 1;
- $send_message .= "Average number of all SMS / only comments events to one unique user (over the day): " . (($stat_total{$yearmoday}{'count'} / scalar @stat_users) . " / " . ($stat_total{$yearmoday}{'I'} / scalar @stat_users) || 0). "\n";
+}
+
+foreach (values %$stats_users) {
+ $_ = scalar keys %$_;
+}
+
+foreach (values %$stats_phones) {
+ $_ = scalar keys %$_;
+}
+
+# use Data::Dumper;
+# print STDERR Dumper($stats_sms), "\n";
+# print STDERR Dumper($stats_users), "\n";
+# print STDERR Dumper($stats_phones), "\n";
+
+my $operators_count = scalar @OPERATORS;
+
+my $columns1 = [
+ ( map { "P=$_" } @OPERATORS ),
+ ( map { "T=I&P=$_" } @OPERATORS ),
+];
+
+my $message = <<'EOT';
+<html>
+ <head>
+ <style type="text/css">
+ body { color: red; }
+ table { border: solid; border-width: 1; }
+ tr { text-align: center; }
+ td { text-align: center; }
+ </style>
+ </head>
+<body>
+EOT
+
+$message .= "<strong>SMS statistics</strong>\n";
+$message .= "<table border=1>\n";
+$message .= " <tr>";
+$message .= "<td rowspan=2>Date</td>";
+$message .= "<td colspan=$operators_count>All SMS</td>";
+$message .= "<td colspan=$operators_count>Event SMS</td>";
+$message .= "</tr>\n";
+$message .= " <tr>";
+$message .= join '', map { "<td align=center>$_</td>" } @OPERATORS foreach (1..2);
+$message .= "</tr>\n";
+
+for (my $time = $time_from; $time < $now; $time += $SECONDS_IN_DAY) {
+ my $date = to_date($time);
+ $message .= " <tr><td>$date</td>";
+
+ my $sms = $stats_sms->{$date};
+
+ foreach (@$columns1) {
+ $message .= "<td align=center>" . ($sms->{$_} || 0) . "</td>";
}
- $send_message .= "\t" x 1;
- $send_message .= "Phones have not confirmed: " . ((scalar keys %{$total{$yearmoday}{'users_n'}}) || 0). "\n";
+
+ $message .= "</tr>\n";
}
+$message .= "</table><br>\n";
-print "Sending email(s)\n";
+my $table2 = [
+ [
+ "Unique users verified phones:",
+ map { $stats_users->{"S=verified&P=$_"} || 0 } @OPERATORS
+ ],
+ [
+ "Unique users verified phones, but not subscribed:",
+ map { $stats_users->{"S=verified&P=$_¬_subscribed"} || 0 } @OPERATORS
+ ],
+ [
+ "Unique users waiting verification:",
+ map { $stats_users->{"S=wait_verification&P=$_"} || 0 } @OPERATORS
+ ],
+];
+$message .= "<strong>User Phones statistics $date_to</strong>\n";
+$message .= "<table border=1>\n";
+$message .= " <tr><td> </td>" . join('', map { "<td>$_</td>" } @OPERATORS ) . "</tr>\n";
+foreach my $row (@$table2) {
+ $message .= " <tr>";
+ my $is_header = 1;
+ foreach my $col (@$row) {
+ $message .= $is_header ? "<td>$col</td>" : "<td align=center>$col</td>";
+ $is_header = 0;
+ }
+ $message .= "</tr>\n";
+}
+$message .= "</table><br>\n";
+
+$message .= "</body></html>\n";
+
+print STDERR "Sending email(s)\n";
+
foreach my $email (@stat_recipients) {
LJ::send_mail({
to => $email,
from => $LJ::DONOTREPLY_EMAIL,
charset => 'utf-8',
subject => 'SMS RU Stats for week',
- body => $send_message,
+ html => $message,
});
}
-print "done!\n";
+print STDERR "done!\n";
+sub is_user_subscribed {
+ my $userid = shift;
+
+ my $user = $USERS{$userid};
+ unless ($user) {
+ $user = {};
+ $USERS{$userid} = $user;
+ }
+
+ return undef if $user->{not_found};
+
+ my $u = LJ::load_userid($userid);
+ unless ($u) {
+ $user->{not_found} = 1;
+ return undef;
+ }
+
+ return $user->{is_subscribed} if exists $user->{is_subscribed};
+
+ my $is_subscribed = 0;
+ foreach my $event (qw/CommentReply CommunityEntryReply JournalNewComment/) {
+ $is_subscribed = 1 if $u->has_subscription(
+ event => $event,
+ ntypeid => LJ::NotificationMethod->method_to_ntypeid ('SMSru')
+ );
+ }
+ $user->{is_subscribed} = $is_subscribed;
+
+ return $user->{is_subscribed};
+}
+
+sub get_provider_name {
+ my $number = shift;
+
+ return $PHONES{$number} if exists $PHONES{$number};
+
+ my ($def) = $number =~ /^7(\d\d\d)\d{7}$/;
+ unless ($def) {
+ warn "Invalid phone number: '$number'\n";
+ $PHONES{$number} = undef;
+ return undef;
+ }
+
+ my $provider_name = $LJ::SMSRU{supported_DEF}->{$def};
+ unless ($provider_name) {
+ warn "Invalid phone DEF code: '$number'\n";
+ $PHONES{$number} = undef;
+ return undef;
+ }
+
+ $PHONES{$number} = $provider_name;
+ return $provider_name;
+}
+
+sub to_date {
+ my $time_stamp = shift;
+ my @t = gmtime($time_stamp);
+ return sprintf('%04d-%02d-%02d', $t[5] + 1900, $t[4] + 1, $t[3]);
+}
+