improve performance of DID provisioning status report, RT10988

4 files changed, 108 insertions, 78 deletions

diff --git a/FS/FS/Cron/set_lata_have_usage.pm b/FS/FS/Cron/set_lata_have_usage.pm
new file mode 100755
index 0000000..a1f5273
--- /dev/null
+++ b/FS/FS/Cron/set_lata_have_usage.pm
@@ -0,0 +1,98 @@
+package FS::Cron::set_lata_have_usage;
+
+use strict;
+use warnings;
+use vars qw( @ISA @EXPORT_OK $me $DEBUG );
+use Exporter;
+use FS::UID qw(adminsuidsetup);
+use FS::Record qw(qsearch qsearchs dbh);
+use FS::lata;
+use FS::phone_avail;
+use FS::svc_phone;
+use Data::Dumper;
+
+@ISA = qw( Exporter );
+@EXPORT_OK = qw ( set_lata_have_usage );
+$DEBUG = 0;
+$me = '[FS::Cron::set_lata_have_usage]';
+
+sub set_lata_have_usage {
+    my %opt = @_;
+    
+    my $debug = 0;
+    $debug = 1 if $opt{'v'};
+    $debug = $opt{'l'} if $opt{'l'};
+  
+    local $DEBUG = $debug if $debug;
+  
+    warn "$me set_lata_have_usage called time=".time."\n" if $DEBUG;
+
+    local $SIG{HUP} = 'IGNORE';
+    local $SIG{INT} = 'IGNORE';
+    local $SIG{QUIT} = 'IGNORE';
+    local $SIG{TERM} = 'IGNORE';
+    local $SIG{TSTP} = 'IGNORE';
+    local $SIG{PIPE} = 'IGNORE';
+
+    my $oldAutoCommit = $FS::UID::AutoCommit;
+    local $FS::UID::AutoCommit = 0;
+    my $dbh = dbh;
+
+    my %latas = map { $_->latanum => $_ } qsearch('lata', {});
+
+    foreach my $lata ( keys %latas ) {
+            next unless $latas{$lata}->have_usage > 0;
+            $latas{$lata}->have_usage(0);
+            my $error = $latas{$lata}->replace;
+            if ( $error ) {
+                $dbh->rollback if $oldAutoCommit;
+                die "error replacing LATA $lata: $error";
+            }
+    }
+    warn "$me cleared have_usage for all LATAs time=".time."\n" if $DEBUG;
+
+    my @dids = qsearch({     'table'     => 'svc_phone',
+                                    'hashref'   => 
+                                        { 'latanum' =>
+                                            { 'op'      => '>',
+                                              'value'   => '0',
+                                            },
+                                        },
+                                    'addl_from' => 'join phone_avail using (svcnum)',
+                                      });
+    warn "$me DID query finished time=".time."\n" if $DEBUG;
+
+    my $count = 0;
+    foreach my $did ( @dids ) {
+        warn "$me count=$count time=".time."\n" if $DEBUG && ($count % 1000 == 0);
+        my @cdrs = $did->get_cdrs;
+        my $lata = $latas{$did->latanum};
+        $count++;
+        if ( scalar(@cdrs) ) {
+            if ( !$lata->have_usage ) {
+                $lata->have_usage(1);
+            }
+            else {
+                $lata->have_usage($lata->have_usage+1);
+            }
+        }
+    }
+
+    warn "$me Set have_usage finished time=".time."\n" if $DEBUG;
+
+    foreach my $lata ( keys %latas ) {
+        if ( $latas{$lata}->modified ) {
+            print "$lata ".$latas{$lata}->have_usage."\n";
+            my $error = $latas{$lata}->replace;
+            if ( $error ) {
+                $dbh->rollback if $oldAutoCommit;
+                die "error replacing LATA $lata: $error";
+            }
+        }
+    }
+
+    $dbh->commit or die $dbh->errstr if $oldAutoCommit;
+    warn "$me done time=".time."\n" if $DEBUG;
+}
+
+1;
diff --git a/FS/FS/svc_phone.pm b/FS/FS/svc_phone.pm
index 19bef94..4945391 100644
--- a/FS/FS/svc_phone.pm
+++ b/FS/FS/svc_phone.pm
@@ -706,8 +706,8 @@ sub get_cdrs {
 
   my @orwhere =  map " $_ = '$number'        ", @fields;
   push @orwhere, map " $_ = '$prefix$number' ", @fields
-    if length($prefix);
-  if ( $prefix =~ /^\+(\d+)$/ ) {
+    if defined($prefix) && length($prefix);
+  if ( $prefix && $prefix =~ /^\+(\d+)$/ ) {
     push @orwhere, map " $_ = '$1$number' ", @fields
   }
 
diff --git a/FS/bin/freeside-daily b/FS/bin/freeside-daily
index ac0a823..e50d992 100755
--- a/FS/bin/freeside-daily
+++ b/FS/bin/freeside-daily
@@ -28,6 +28,9 @@ reconcile_breakage(%opt);
 use FS::Cron::upload qw(upload);
 upload(%opt);
 
+use FS::Cron::set_lata_have_usage qw(set_lata_have_usage);
+set_lata_have_usage(%opt);
+
 # Send alerts about upcoming credit card expiration.
 use FS::Cron::alert_expiration qw(alert_expiration);
 my $conf = new FS::Conf;
diff --git a/bin/set-lata-have_usage b/bin/set-lata-have_usage
index 1efa420..82334df 100755
--- a/bin/set-lata-have_usage
+++ b/bin/set-lata-have_usage
@@ -1,81 +1,10 @@
-#!/usr/bin/perl
+#!/usr/bin/perl -w
 use strict;
-use warnings;
 use FS::UID qw(adminsuidsetup);
-use FS::Record qw(qsearch qsearchs dbh);
-use FS::lata;
-use FS::phone_avail;
-use FS::svc_phone;
-use Data::Dumper;
+use FS::Cron::set_lata_have_usage qw(set_lata_have_usage);
+use FS::Conf;
 
-print "started time=".time."\n";
-
-my $user = shift;
+my $user = shift or die "set-lata-have_usage username";
 adminsuidsetup $user;
 
-local $SIG{HUP} = 'IGNORE';
-local $SIG{INT} = 'IGNORE';
-local $SIG{QUIT} = 'IGNORE';
-local $SIG{TERM} = 'IGNORE';
-local $SIG{TSTP} = 'IGNORE';
-local $SIG{PIPE} = 'IGNORE';
-
-my $oldAutoCommit = $FS::UID::AutoCommit;
-local $FS::UID::AutoCommit = 0;
-my $dbh = dbh;
-
-my %latas = map { $_->latanum => $_ } qsearch('lata', {});
-
-foreach my $lata ( keys %latas ) {
-        next unless $latas{$lata}->have_usage > 0;
-        $latas{$lata}->have_usage(0);
-        my $error = $latas{$lata}->replace;
-        if ( $error ) {
-            $dbh->rollback if $oldAutoCommit;
-            die "error replacing LATA $lata: $error";
-        }
-}
-print "cleared have_usage for all LATAs time=".time."\n";
-
-my @dids = qsearch({     'table'     => 'svc_phone',
-                                'hashref'   => 
-                                    { 'latanum' =>
-                                        { 'op'      => '>',
-                                          'value'   => '0',
-                                        },
-                                    },
-                                'addl_from' => 'join phone_avail using (svcnum)',
-                                  });
-print "DID query finished time=".time."\n";
-
-my $count = 0;
-foreach my $did ( @dids ) {
-    print "count=$count time=".time."\n" if $count % 1000 == 0;
-    my @cdrs = $did->get_cdrs;
-    my $lata = $latas{$did->latanum};
-    $count++;
-    if ( scalar(@cdrs) ) {
-        if ( !$lata->have_usage ) {
-            $lata->have_usage(1);
-        }
-        else {
-            $lata->have_usage($lata->have_usage+1);
-        }
-    }
-}
-
-print "Set have_usage finished time=".time."\n";
-
-foreach my $lata ( keys %latas ) {
-    if ( $latas{$lata}->modified ) {
-        print "$lata ".$latas{$lata}->have_usage."\n";
-        my $error = $latas{$lata}->replace;
-        if ( $error ) {
-            $dbh->rollback if $oldAutoCommit;
-            die "error replacing LATA $lata: $error";
-        }
-    }
-}
-
-$dbh->commit or die $dbh->errstr if $oldAutoCommit;
-print "done time=".time."\n";
+set_lata_have_usage('v' => 1);