From c1c47a765702a1e1dad680f603d25aa04eeb5269 Mon Sep 17 00:00:00 2001 From: levinse Date: Wed, 22 Jun 2011 18:32:21 +0000 Subject: [PATCH] improve performance of DID provisioning status report, RT10988 --- FS/FS/Cron/set_lata_have_usage.pm | 98 +++++++++++++++++++++++++++++++++++++++ FS/FS/svc_phone.pm | 4 +- FS/bin/freeside-daily | 3 ++ bin/set-lata-have_usage | 81 ++------------------------------ 4 files changed, 108 insertions(+), 78 deletions(-) create mode 100755 FS/FS/Cron/set_lata_have_usage.pm 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 000000000..a1f5273f1 --- /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 19bef94fc..494539150 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 ac0a82391..e50d992e0 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 1efa42038..82334dfa8 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); -- 2.11.0