From 4b2b65e322f09922513d6d23fc50bfe3b69af36d Mon Sep 17 00:00:00 2001 From: Ivan Kohler Date: Wed, 2 Mar 2016 15:03:27 -0800 Subject: [PATCH] page large customer package lists, RT#39822 --- FS/FS/cust_main/Packages.pm | 42 ++++++-- httemplate/view/cust_main/packages.html | 164 ++++++++++++++------------------ 2 files changed, 108 insertions(+), 98 deletions(-) diff --git a/FS/FS/cust_main/Packages.pm b/FS/FS/cust_main/Packages.pm index ead97f2c3..6a69517d5 100644 --- a/FS/FS/cust_main/Packages.pm +++ b/FS/FS/cust_main/Packages.pm @@ -629,29 +629,55 @@ customer. =cut sub num_cancelled_pkgs { - shift->num_pkgs("cust_pkg.cancel IS NOT NULL AND cust_pkg.cancel != 0"); + my $self = shift; + my $opt = shift || {}; + $opt->{extra_sql} .= ' AND ' if $opt->{extra_sql}; + $opt->{extra_sql} .= "cust_pkg.cancel IS NOT NULL AND cust_pkg.cancel != 0"; + $self->num_pkgs($opt); } sub num_ncancelled_pkgs { - shift->num_pkgs("( cust_pkg.cancel IS NULL OR cust_pkg.cancel = 0 )"); + my $self = shift; + my $opt = shift || {}; + $opt->{extra_sql} .= ' AND ' if $opt->{extra_sql}; + $opt->{extra_sql} .= "( cust_pkg.cancel IS NULL OR cust_pkg.cancel = 0 )"; + $self->num_pkgs($opt); } sub num_suspended_pkgs { - shift->num_pkgs(" ( cust_pkg.cancel IS NULL OR cust_pkg.cancel = 0 ) - AND cust_pkg.susp IS NOT NULL AND cust_pkg.susp != 0 "); + my $self = shift; + my $opt = shift || {}; + $opt->{extra_sql} .= ' AND ' if $opt->{extra_sql}; + $opt->{extra_sql} .= " ( cust_pkg.cancel IS NULL OR cust_pkg.cancel = 0 ) + AND cust_pkg.susp IS NOT NULL AND cust_pkg.susp != 0 "; + $self->num_pkgs($opt); } sub num_unsuspended_pkgs { - shift->num_pkgs(" ( cust_pkg.cancel IS NULL OR cust_pkg.cancel = 0 ) - AND ( cust_pkg.susp IS NULL OR cust_pkg.susp = 0 ) "); + my $self = shift; + my $opt = shift || {}; + $opt->{extra_sql} .= ' AND ' if $opt->{extra_sql}; + $opt->{extra_sql} .= " ( cust_pkg.cancel IS NULL OR cust_pkg.cancel = 0 ) + AND ( cust_pkg.susp IS NULL OR cust_pkg.susp = 0 )"; + $self->num_pkgs($opt); } sub num_pkgs { my( $self ) = shift; - my $sql = scalar(@_) ? shift : ''; + my $addl_from = ''; + my $sql = ''; + if ( @_ ) { + if ( ref($_[0]) ) { + my $opt = shift; + $sql = $opt->{extra_sql} if exists($opt->{extra_sql}); + $addl_from = $opt->{addl_from} if exists($opt->{addl_from}); + } else { + $sql = shift; + } + } $sql = "AND $sql" if $sql && $sql !~ /^\s*$/ && $sql !~ /^\s*AND/i; my $sth = dbh->prepare( - "SELECT COUNT(*) FROM cust_pkg WHERE custnum = ? $sql" + "SELECT COUNT(*) FROM cust_pkg $addl_from WHERE custnum = ? $sql" ) or die dbh->errstr; $sth->execute($self->custnum) or die $sth->errstr; $sth->fetchrow_arrayref->[0]; diff --git a/httemplate/view/cust_main/packages.html b/httemplate/view/cust_main/packages.html index 0c67843d4..475e18949 100755 --- a/httemplate/view/cust_main/packages.html +++ b/httemplate/view/cust_main/packages.html @@ -102,18 +102,35 @@ if ( el ) el.scrollIntoView(true); <% mt('cancelled packages') |h %> ) % } -% if ( $num_old_packages ) { + +% if ( $cgi->param('showoldpackages') ) { +% $cgi->param('showoldpackages', 0); + ( <% mt('hide old packages') |h %> ) +% $cgi->param('showoldpackages', 1); +% } else { % $cgi->param('showoldpackages', 1); ( <% mt('show old packages') |h %> ) -% } elsif ( $cgi->param('showoldpackages') ) { % $cgi->param('showoldpackages', 0); - ( <% mt('hide old packages') |h %> ) % } +% my $pager = include('/elements/pager.html', +% offset => $offset, +% num_rows => scalar(@packages), +% total => $num_pkgs, +% maxrecords => $maxrecords, +% ); +% if ( $num_pkgs > $maxrecords ) { + + + <% $pager %> + + +% } + @@ -131,6 +148,14 @@ if ( el ) el.scrollIntoView(true); +% if ( $num_pkgs > $maxrecords ) { + + + <% $pager %> + + +% } + <%init> @@ -167,7 +192,31 @@ my $group_by = my $num_svcs = '( SELECT COUNT(*) FROM cust_svc '. ' WHERE cust_svc.pkgnum = cust_pkg.pkgnum ) AS num_svcs'; -# don't exclude cancelled packages at this stage +my $extra_sql = + ' AND main_pkgnum IS NULL '. # supplemental package of something else + ' AND change_to_pkgnum IS NULL '. # ordered, not-yet-active change target + ' AND change_pkgnum IS NULL '; # canceled package changed into another + +unless ( $cgi->param('showoldpackages') ) { + my $years = $conf->config('cust_main-packages-years') || 2; + my $then = time - $years * 31556926; #60*60*24*365.2422 is close enough + + $extra_sql .= " AND ( + ( part_pkg.freq = '0' AND ( setup IS NULL OR setup > $then ) ) + OR ( part_pkg.freq != '0' AND ( cancel IS NULL OR cancel > $then ) ) + OR EXISTS ( SELECT 1 FROM cust_svc WHERE cust_svc.pkgnum = cust_pkg.pkgnum ) + )"; +} + +my $num_method = $hide_cancelled ? 'ncancelled_pkgs' : 'all_pkgs'; +my $num_pkgs = $cust_main->$num_method({ + 'addl_from' => 'LEFT JOIN part_pkg USING ( pkgpart )', + 'extra_sql' => $extra_sql, +}); + +my $maxrecords = 10; +my $offset = $cgi->param('offset') =~ /^(\d+)$/ ? $1 : 0; + my @packages = $cust_main->all_pkgs( { 'select' => "$cust_pkg_fields, $part_pkg_fields, $num_svcs", 'addl_from' => qq{ @@ -179,105 +228,40 @@ my @packages = $cust_main->all_pkgs( { ON ( cust_pkg.pkgpart = recur_option.pkgpart AND recur_option.optionname = 'recur_fee' ) }, + 'extra_sql' => $extra_sql, + 'order_by' => "ORDER BY pkgnum ASC LIMIT $maxrecords OFFSET $offset", } ); -my $is_anything_hidden = 0; # optimization - -my %change_to_from; # target pkgnum => current cust_pkg, for future changes -my %changed_from; # old pkgnum => new cust_pkg, for past changes -my %supplementals_of; # main pkgnum => arrayref of supplementals - foreach my $cust_pkg ( @packages ) { my %hash = $cust_pkg->hash; my %part_pkg = map { /^part_pkg_(.+)$/ or die; ( $1 => $hash{$_} ); } grep { /^part_pkg_/ } keys %hash; $cust_pkg->{'_pkgpart'} = new FS::part_pkg \%part_pkg; - if ( $cust_pkg->change_to_pkgnum ) { - $change_to_from{$cust_pkg->change_to_pkgnum} = $cust_pkg; - $is_anything_hidden = 1; - } - if ( $cust_pkg->change_pkgnum ) { - $changed_from{$cust_pkg->change_pkgnum} = $cust_pkg; - $is_anything_hidden = 1; - } - if ( $cust_pkg->main_pkgnum ) { - $supplementals_of{$cust_pkg->main_pkgnum} ||= []; - push @{ $supplementals_of{$cust_pkg->main_pkgnum} }, $cust_pkg; - $is_anything_hidden = 1; - } -} - -# filter out hidden package changes -if ( $is_anything_hidden ) { - my @displayable_packages; - foreach my $cust_pkg (@packages) { - - # if this package has any supplemental packages, it should remember them - $cust_pkg->set('_supplemental', $supplementals_of{$cust_pkg->pkgnum}); - - if ( $cust_pkg->main_pkgnum ) { - - # it's a supplemental package of something else, and shouldn't be on the - # root list - - } elsif ( exists( $change_to_from{$cust_pkg->pkgnum} ) ) { - - # $cust_pkg is an ordered, not-yet-active package change target - my $change_from = $change_to_from{ $cust_pkg->pkgnum }; - $cust_pkg->set('change_from_pkg', $change_from); - $change_from->set('change_to_pkg', $cust_pkg); - - } elsif ( exists( $changed_from{$cust_pkg->pkgnum} ) ) { - - # $cust_pkg is a canceled package changed into another packge - # hide it under the destination package's history - my $changed_to = $changed_from{$cust_pkg->pkgnum}; - $cust_pkg->set('changed_to_pkg', $changed_to); - $changed_to->set('changed_from_pkg', $cust_pkg); - - } else { - - push @displayable_packages, $cust_pkg; - - } + #arrayref of supplementals + $cust_pkg->set('_supplemental', [ + qsearch('cust_pkg', { main_pkgnum=>$cust_pkg->pkgnum }) + ] ); + + #for future changes + my $change_to = + qsearchs('cust_pkg', { change_to_pkgnum=>$cust_pkg->pkgnum }); + if ( $change_to ) { + $change_to->set('change_from_pkg', $cust_pkg); + $cust_pkg->set('change_to_pkg', $change_to); } - @packages = @displayable_packages; -} -# filter all cancelled packages if the user wants -if ( $hide_cancelled ) { - @packages = grep { !$_->get('cancel') } @packages; -} - -# filter out 'old' packages -my $num_old_packages = scalar(@packages); - -unless ( $cgi->param('showoldpackages') ) { - my $years = $conf->config('cust_main-packages-years') || 2; - my $then = time - $years * 31556926; #60*60*24*365.2422 is close enough - - my %hide = ( 'cancelled' => 'cancel', - 'one-time charge' => 'setup', - ); - - @packages = - grep { !exists($hide{$_->status}) or $_->get($hide{$_->status}) > $then - or $_->num_svcs #don't hide packages w/services - } - @packages; -} - -$num_old_packages -= scalar(@packages); - -# don't include supplemental packages in this list; they'll be found from -# their main packages -# (as will change-target packages) -####@packages = grep !$_->main_pkgnum, @packages; + #for past changes + my $changed_from = + qsearchs('cust_pkg', { change_pkgnum=>$cust_pkg->pkgnum }); + if ( $changed_from ) { + $changed_from->set('changed_to_pkg', $cust_pkg); + $cust_pkg->set('changed_from_pkg', $changed_from); + } -foreach my $cust_pkg ( @packages ) { $cust_pkg->{'_cust_pkg_discount_active'} = [ $cust_pkg->cust_pkg_discount_active ]; + } -- 2.11.0