[ljcom] r9647: LJSUP-7213. e-mail subscription with sta...

A   trunk/bin/misc/smsru_stats.pl
U   trunk/bin/upgrading/update-db-local.pl
U   trunk/cgi-bin/ljcom.pl

Added: trunk/bin/misc/smsru_stats.pl
===================================================================
--- trunk/bin/misc/smsru_stats.pl	                        (rev 0)
+++ trunk/bin/misc/smsru_stats.pl	2010-10-26 03:04:46 UTC (rev 9647)
@@ -0,0 +1,101 @@
+#!/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';
+
+use strict;
+use lib "$ENV{LJHOME}/cgi-bin";
+
+require "ljlib.pl";
+use LJ::DBUtil;
+use LJ::NotificationMethod;
+
+my @stat_recipients = ('recipient@mydomain.com');
+
+my %stat_total = ();
+
+use Data::Dumper;
+
+# iterate over all clusters and query for stats
+foreach my $cid (@LJ::CLUSTERS) {
+    print "querying cluster $cid... ";
+
+    my $udb = LJ::DBUtil->connect_to_cluster($cid, 1)
+	or die "unable to contact 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)");
+    $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);
+        }
+        $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}++;
+    }
+}
+
+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");
+$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 = '';
+
+$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";
+    }
+    $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";
+    }
+    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";
+    }
+    $send_message .= "\t" x 1;
+    $send_message .= "Phones have not confirmed: " . ((scalar keys %{$total{$yearmoday}{'users_n'}}) || 0). "\n";
+}
+
+print "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,
+    });
+}
+
+print "done!\n";
+
+


Property changes on: trunk/bin/misc/smsru_stats.pl
___________________________________________________________________
Added: svn:executable
   + *

Modified: trunk/bin/upgrading/update-db-local.pl
===================================================================
--- trunk/bin/upgrading/update-db-local.pl	2010-10-26 02:54:22 UTC (rev 9646)
+++ trunk/bin/upgrading/update-db-local.pl	2010-10-26 03:04:46 UTC (rev 9647)
@@ -1557,11 +1557,11 @@
 create table `smsru` (
     smsid       INT(10) UNSIGNED PRIMARY KEY UNIQUE AUTO_INCREMENT,
     userid      INT(10) UNSIGNED,
-    to_number   INT(10),
+    to_number   VARCHAR(12),
     msg         VARCHAR(255),
     added       TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
-
-    INDEX(userid)
+    msg_type    VARCHAR(1) NOT NULL,
+    INDEX(userid), INDEX(added)
 ) Type=InnoDB
 EOC
 
@@ -2014,6 +2014,21 @@
             "ALTER TABLE smsru_phones MODIFY phone VARCHAR(12)");
     }
 
+    unless (column_type("smsru", "msg_type")) {
+        do_alter("smsru",
+            "ALTER TABLE smsru ADD msg_type VARCHAR(1) NOT NULL DEFAULT ''");
+    }
+
+    unless (index_name("smsru", "INDEX:added")) {
+        do_alter("smsru",
+                 "ALTER TABLE smsru ADD INDEX (added)");
+    }
+
+    unless (column_type("smsru_phones", "added")) {
+        do_alter("smsru_phones",
+            "ALTER TABLE smsru_phones ADD added DATETIME");
+    }
+
 });
 
 1;  # true

Modified: trunk/cgi-bin/ljcom.pl
===================================================================
--- trunk/cgi-bin/ljcom.pl	2010-10-26 02:54:22 UTC (rev 9646)
+++ trunk/cgi-bin/ljcom.pl	2010-10-26 03:04:46 UTC (rev 9647)
@@ -31,7 +31,7 @@
 
 @LJ::USER_TABLES_LOCAL = ("phonepostentry", "phoneposttrans", "vgifts",
                           "sms_quota", "sms_promo", "spinvox_msg",
-                          "jabroster2", "jabrostergroups2", "inactive_user");
+                          "jabroster2", "jabrostergroups2", "inactive_user", "smsru");
 
 LJ::ModuleLoader->require_if_exists("ljcomint.pl");
 LJ::ModuleLoader->require_if_exists("sms-local.pl");