[ljcom] r9706: LJSUP-7213: E-mail subscription with sta...

U   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&not_subscribed"}->{$userid}++;
+        $stats_users->{"S=verified&P=$provider_name&not_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=$_&not_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>&nbsp;</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]);
+}
+