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]); +} +