From 5778beb6849b2383068aa7c627e6968fe92419eb Mon Sep 17 00:00:00 2001 From: Mark Wells Date: Wed, 19 Jun 2013 14:46:24 -0700 Subject: [PATCH] improve accuracy of historical package counts, #8461 --- FS/FS/h_cust_pkg.pm | 73 +++++++++ httemplate/search/cust_pkg_summary.cgi | 174 ++++++++++---------- httemplate/search/elements/search-html.html | 8 +- httemplate/search/elements/search-xls.html | 5 +- httemplate/search/elements/search.html | 2 + httemplate/search/h_cust_pkg.html | 239 ++++++++++++++++++++++++++++ 6 files changed, 411 insertions(+), 90 deletions(-) create mode 100755 httemplate/search/h_cust_pkg.html diff --git a/FS/FS/h_cust_pkg.pm b/FS/FS/h_cust_pkg.pm index e796f4145..99037c22f 100644 --- a/FS/FS/h_cust_pkg.pm +++ b/FS/FS/h_cust_pkg.pm @@ -20,6 +20,79 @@ FS::h_cust_pkg - Historical record of customer package changes An FS::h_cust_pkg object represents historical changes to packages. FS::h_cust_pkg inherits from FS::h_Common and FS::cust_pkg. +=head1 CLASS METHODS + +=over 4 + +=item search HASHREF + +Like L, but adapted for searching historical records. +Takes the additional parameter "date", which is the timestamp to perform +the search "as of" (i.e. search the most recent insert or replace_new record +for each pkgnum that is not later than that date). + +=cut + +sub search { + my ($class, $params) = @_; + my $date = delete $params->{'date'}; + $date =~ /^\d*$/ or die "invalid search date '$date'\n"; + + my $query = FS::cust_pkg->search($params); + + # allow multiple status criteria + # this might be useful in the base cust_pkg search, but I haven't + # tested it there yet + my $status = delete $params->{'status'}; + if( $status ) { + my @status_where; + foreach ( split(',', $status) ) { + if ( /^active$/ ) { + push @status_where, $class->active_sql(); + } elsif ( /^not[ _]yet[ _]billed$/ ) { + push @status_where, $class->not_yet_billed_sql(); + } elsif ( /^(one-time charge|inactive)$/ ) { + push @status_where, $class->inactive_sql(); + } elsif ( /^suspended$/ ) { + push @status_where, $class->suspended_sql(); + } elsif ( /^cancell?ed$/ ) { + push @status_where, $class->cancelled_sql(); + } + } + if ( @status_where ) { + $query->{'extra_sql'} .= ' AND ('.join(' OR ', @status_where).')'; + $query->{'count_query'} .= ' AND ('.join(' OR ', @status_where).')'; + } + } + + # make some adjustments + $query->{'table'} = 'h_cust_pkg'; + foreach (qw(select addl_from extra_sql count_query)) { + $query->{$_} =~ s/cust_pkg\b/h_cust_pkg/g; + $query->{$_} =~ s/cust_main\b/h_cust_main/g; + } + + my $and_where = " AND h_cust_pkg.historynum = + (SELECT historynum FROM h_cust_pkg AS mostrecent + WHERE mostrecent.pkgnum = h_cust_pkg.pkgnum + AND mostrecent.history_date <= $date + AND mostrecent.history_action IN ('insert', 'replace_new') + ORDER BY history_date DESC,historynum DESC LIMIT 1 + ) AND h_cust_main.historynum = + (SELECT historynum FROM h_cust_main AS mostrecent + WHERE mostrecent.custnum = h_cust_main.custnum + AND mostrecent.history_date <= h_cust_pkg.history_date + AND mostrecent.history_action IN ('insert', 'replace_new') + ORDER BY history_date DESC,historynum DESC LIMIT 1 + )"; + + $query->{'extra_sql'} .= $and_where; + $query->{'count_query'} .= $and_where; + + $query; +} + + =head1 BUGS =head1 SEE ALSO diff --git a/httemplate/search/cust_pkg_summary.cgi b/httemplate/search/cust_pkg_summary.cgi index d3274894f..c0eb69920 100644 --- a/httemplate/search/cust_pkg_summary.cgi +++ b/httemplate/search/cust_pkg_summary.cgi @@ -1,25 +1,14 @@ -<% include('/elements/header.html', $title) %> -<% include('/elements/table-grid.html') %> - -% foreach (@head) { - <% $_ %> -% } - -% my $r=0; -% foreach my $row (@rows) { - -% foreach (@$row) { - <% $_ %> -% } - -% $r++; -% } - -% foreach (@totals) { - <% $_ %> -% } - - +<& elements/search.html, + 'title' => $title, + 'name' => 'package types', + 'query' => $query, + 'count_query' => $count_query, + 'header' => \@head, + 'fields' => \@fields, + 'links' => \@links, + 'align' => 'clrrrrr', + 'footer_data' => $totals, +&> <%init> my $curuser = $FS::CurrentUser::CurrentUser; @@ -34,83 +23,92 @@ if($begin > 0) { $cgi->param('beginning').' - '.$cgi->param('ending').')'; } -my @h_sql = FS::h_cust_pkg->sql_h_search($end); - -my ($end_sql, $addl_from) = @h_sql[1,3]; -$end_sql =~ s/ORDER BY.*//; # breaks aggregate queries - -my $begin_sql = $end_sql; -$begin_sql =~ s/$end/$begin/g; - -my $active_sql = FS::cust_pkg->active_sql; -my $suspended_sql = FS::cust_pkg->suspended_sql; -my @conds = ( - # SQL WHERE clauses for each column of the table. - " $begin_sql AND ($active_sql OR $suspended_sql)", - '', - " $end_sql AND ($active_sql OR $suspended_sql)", - " $end_sql AND $active_sql", - " $end_sql AND $suspended_sql", - ); - -$_ =~ s/\bcust_pkg/maintable/g foreach @conds; - -my @head = ('Package', 'Before Period', 'Sales', 'Total', 'Active', 'Suspended'); -my @rows = (); -my @totals = ('Total', 0, 0, 0, 0, 0); - -if( !$begin ) { - splice @conds, 1, 1; - splice @head, 1, 1; -} - my $agentnums_sql = $curuser->agentnums_sql( 'null' => 1, - 'table' => 'part_pkg', + 'table' => 'main', ); -my $extra_sql = " WHERE $agentnums_sql"; +my $extra_sql = " freq != '0' AND $agentnums_sql"; #tiny bit of false laziness w/cust_pkg.pm::search if ( grep { $_ eq 'classnum' } $cgi->param ) { if ( $cgi->param('classnum') eq '' ) { - $extra_sql .= ' AND part_pkg.classnum IS NULL'; + $extra_sql .= ' AND main.classnum IS NULL'; } elsif ( $cgi->param('classnum') =~ /^(\d+)$/ && $1 ne '0' ) { - $extra_sql .= " AND part_pkg.classnum = $1 "; + $extra_sql .= " AND main.classnum = $1 "; } } -foreach my $part_pkg (qsearch({ 'table' => 'part_pkg', - 'hashref' => {}, - 'extra_sql' => $extra_sql, - }) - ) -{ - my @row = (); - next if !$part_pkg->freq; # exclude one-time packages - push @row, $part_pkg->pkg; - my $i=1; - foreach my $cond (@conds) { - if($cond) { - my $result = qsearchs({ - 'table' => 'h_cust_pkg', - 'addl_from' => $addl_from. - ' LEFT JOIN cust_main USING ( custnum )', - - 'hashref' => {}, - 'select' => 'count(*)', - 'extra_sql' => 'WHERE pkgpart = '.$part_pkg->pkgpart.$cond. - ' AND '. $curuser->agentnums_sql( - 'table' => 'cust_main', - ), - }); - $row[$i] = $result->getfield('count'); - $totals[$i] += $row[$i]; - } - $i++; - } - $row[2] = $row[3]-$row[1]; - $totals[2] += $row[2]; - push @rows, \@row; +my $active_sql = 'setup IS NOT NULL AND susp IS NULL AND cancel IS NULL'; +my $suspended_sql = 'setup IS NOT NULL AND susp IS NOT NULL AND cancel IS NULL'; +my $active_or_suspended_sql = 'setup IS NOT NULL AND cancel IS NULL'; +my %conds; + +$conds{'before'} = { 'date' => $begin, 'status' => 'active,suspended' }; +$conds{'after'} = { 'date' => $end, 'status' => 'active,suspended' }; +$conds{'active'} = { 'date' => $end, 'status' => 'active' }; +$conds{'suspended'} = { 'date' => $end, 'status' => 'suspended' }; + +my @select; +my $totals = FS::part_pkg->new({pkg => 'Total'}); +foreach my $column (keys %conds) { + my $h_search = FS::h_cust_pkg->search($conds{$column}); + my $count_query = $h_search->{count_query}; + + # push a select expression for the total packages with pkgpart=main.pkgpart + push @select, "($count_query AND h_cust_pkg.pkgpart = main.pkgpart) AS $column"; + + # and query the total packages with pkgpart=any of the main.pkgparts + my $total = FS::Record->scalar_sql($count_query . + " AND h_cust_pkg.pkgpart IN(SELECT pkgpart FROM part_pkg AS main WHERE $extra_sql)" + ); + $totals->set($column => $total); +} + +my $query = { + 'table' => 'part_pkg', + 'addl_from' => 'AS main', + 'select' => join(', ', 'main.*', @select), + 'extra_sql' => "WHERE $extra_sql", +}; + +my $count_query = "SELECT COUNT(*) FROM part_pkg AS main WHERE $extra_sql"; + +my $baselink = "h_cust_pkg.html?"; +if ( $cgi->param('classnum') =~ /^\d*$/ ) { + $baselink .= "classnum=".$cgi->param('classnum').';'; +} +my @links = ( #arguments to h_cust_pkg.html, except for pkgpart + '', + '', + [ $baselink . "status=active,suspended;date=$begin;pkgpart=", 'pkgpart' ], + '', + [ $baselink . "status=active,suspended;date=$end;pkgpart=", 'pkgpart' ], + [ $baselink . "status=active;date=$end;pkgpart=", 'pkgpart' ], + [ $baselink . "status=suspended;date=$end;pkgpart=", 'pkgpart' ], +); + +my @head = ('#', + 'Package', + 'Before Period', + 'Sales', + 'Total', + 'Active', + 'Suspended'); + +my @fields = ( + 'pkgpart', + 'pkg', + 'before', + sub { $_[0]->after - $_[0]->before }, + 'after', + 'active', + 'suspended', + ); + +if ( !$begin ) { + # remove the irrelevant 'before' column + splice(@$_,2,1) foreach \@head, \@fields, \@links; } + diff --git a/httemplate/search/elements/search-html.html b/httemplate/search/elements/search-html.html index e760bc546..bee33cfe8 100644 --- a/httemplate/search/elements/search-html.html +++ b/httemplate/search/elements/search-html.html @@ -253,6 +253,12 @@ % $bgcolor = $bgcolor1; % } +% my $rowstyle = ''; +% if ( $row eq $opt{'footer_data'} ) { +% $rowstyle = ' STYLE="border-top: dashed 1px black; font-style: italic"'; +% $bgcolor = '#dddddd'; +% } + % my $trid = ''; % if ( $opt{'link_field' } ) { % my $link_field = $opt{'link_field'}; @@ -262,7 +268,7 @@ % $trid = $row->$link_field(); % } % } - + > % if ( $opt{'fields'} ) { diff --git a/httemplate/search/elements/search-xls.html b/httemplate/search/elements/search-xls.html index bc844a579..8334497d2 100644 --- a/httemplate/search/elements/search-xls.html +++ b/httemplate/search/elements/search-xls.html @@ -32,7 +32,10 @@ my $XLS = new IO::Scalar \$data; my $workbook = $format->{class}->new($XLS) or die "Error opening Excel file: $!"; -my $worksheet = $workbook->add_worksheet(substr($opt{'title'},0,31)); +my $title = $opt{'title'}; +$title =~ s/[\[\]\:\*\?\/\/]//g; +$title = substr($title, 0, 31); +my $worksheet = $workbook->add_worksheet($title); $worksheet->protect(); diff --git a/httemplate/search/elements/search.html b/httemplate/search/elements/search.html index d44b45465..8f6272030 100644 --- a/httemplate/search/elements/search.html +++ b/httemplate/search/elements/search.html @@ -453,4 +453,6 @@ if ( ref($opt{query}) ) { $header ||= $sth->{NAME}; } +push @$rows, $opt{'footer_data'} if $opt{'footer_data'}; + diff --git a/httemplate/search/h_cust_pkg.html b/httemplate/search/h_cust_pkg.html new file mode 100755 index 000000000..b23a57be6 --- /dev/null +++ b/httemplate/search/h_cust_pkg.html @@ -0,0 +1,239 @@ +<& elements/search.html, + 'html_init' => $html_init, + 'title' => $title, + 'name' => 'packages', + 'query' => $query, + 'count_query' => $count_query, + 'header' => [ emt('#'), + emt('Quan.'), + emt('Package'), + emt('Class'), + emt('Status'), + emt('Ordered by'), + emt('Setup'), + emt('Base Recur'), + emt('Freq.'), + emt('Setup'), + emt('Last bill'), + emt('Next bill'), + emt('Adjourn'), + emt('Susp.'), + emt('Susp. delay'), + emt('Expire'), + emt('Contract end'), + emt('Changed'), + emt('Cancel'), + emt('Reason'), + FS::UI::Web::cust_header( + $cgi->param('cust_fields') + ), + emt('As of'), + ], + 'fields' => [ + 'pkgnum', + 'quantity', + 'pkg', + 'classname', + sub { ucfirst(shift->status); }, + 'otaker', + sub { sprintf( $money_char.'%.2f', + shift->part_pkg->option('setup_fee'), + ); + }, + sub { my $c = shift; + sprintf( $money_char.'%.2f', + $c->part_pkg->base_recur($c) + ); + }, + sub { FS::part_pkg::freq_pretty(shift); }, + + ( map { time_or_blank($_) } + qw( setup last_bill bill adjourn susp dundate expire contract_end change_date cancel ) ), + + sub { my $self = shift; + my $return = ''; + foreach my $action ( qw ( cancel susp ) ) { + my $reason = $self->last_reason($action); + $return = $reason->reason if $reason; + last if $return; + } + $return; + }, + + \&FS::UI::Web::cust_fields, + # in cust_pkg.cgi, service labels would go here + time_or_blank('history_date'), + ], + 'color' => [ + '', + '', + '', + '', + sub { shift->statuscolor; }, + '', + '', + '', + '', + '', + '', + '', + '', + '', + '', + '', + '', + '', + '', + '', + FS::UI::Web::cust_colors(), + '', + ], + 'style' => [ '', '', '', '', 'b', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', + FS::UI::Web::cust_styles() ], + 'size' => [ '', '', '', '', '-1' ], + 'align' => 'rrlcccrrlrrrrrrrrrrl'. FS::UI::Web::cust_aligns(). 'r', + 'links' => [ + $link, + $link, + $link, + '', + '', + '', + '', + '', + '', + '', + '', + '', + '', + '', + '', + '', + '', # link to changed-from package? + '', + '', + '', + ( map { $_ ne 'Cust. Status' ? $clink : '' } + FS::UI::Web::cust_header( + $cgi->param('cust_fields') + ) + ), + '', + ], +&> +<%init> + +# shamelessly cloned from cust_pkg.cgi, with minimal changes to make it work + +my $curuser = $FS::CurrentUser::CurrentUser; + +die "access denied" + unless $curuser->access_right('List packages'); + +my $conf = new FS::Conf; +my $money_char = $conf->config('money_char') || '$'; + +my %search_hash = (); + +#some false laziness w/misc/bulk_change_pkg.cgi + +$search_hash{'query'} = $cgi->keywords; + +#scalars +for (qw( agentnum custnum magic status custom cust_fields pkgbatch )) { + $search_hash{$_} = $cgi->param($_) if $cgi->param($_); +} + +#arrays +for my $param (qw( pkgpart classnum )) { + $search_hash{$param} = [ $cgi->param($param) ] + if grep { $_ eq $param } $cgi->param; +} + +#scalars that need to be passed if empty +for my $param (qw( censustract censustract2 )) { + $search_hash{$param} = $cgi->param($param) || '' + if grep { $_ eq $param } $cgi->param; +} + +my $report_option = $cgi->param('report_option'); +$search_hash{report_option} = $report_option if $report_option; + +for my $param (grep /^report_option_any/, $cgi->param) { + $search_hash{$param} = $cgi->param($param); +} + +### +# parse dates +### + +#false laziness w/report_cust_pkg.html +my %disable = ( + 'all' => {}, + 'one-time charge' => { 'last_bill'=>1, 'bill'=>1, 'adjourn'=>1, 'susp'=>1, 'expire'=>1, 'cancel'=>1, 'contract_end'=>1, 'dundate'=>1, }, + 'active' => { 'susp'=>1, 'cancel'=>1 }, + 'suspended' => { 'cancel' =>1, 'dundate'=>1, }, + 'cancelled' => {}, + '' => {}, +); + +foreach my $field (qw( setup last_bill bill adjourn susp expire contract_end change_date cancel active )) { + + my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi, $field); + + next if $beginning == 0 && $ending == 4294967295 + or $disable{$cgi->param('status')}->{$field}; + + $search_hash{$field} = [ $beginning, $ending ]; + +} + +my $date; +if ( $cgi->param('date') =~ /^(\d+)$/ ) { + $date = $1; + $search_hash{'date'} = $date; +} + +my $query = FS::h_cust_pkg->search(\%search_hash); +my $count_query = delete($query->{'count_query'}); + +my $show = $curuser->default_customer_view =~ /^(jumbo|packages)$/ + ? '' + : ';show=packages'; + +my $link = sub { + my $self = shift; + my $frag = 'cust_pkg'. $self->pkgnum; #hack for IE ignoring real #fragment + [ "${p}view/cust_main.cgi?custnum=".$self->custnum. + "$show;fragment=$frag#cust_pkg", + 'pkgnum' + ]; +}; + +my $clink = sub { + my $cust_pkg = shift; + $cust_pkg->cust_main_custnum + ? [ "${p}view/cust_main.cgi?", 'custnum' ] + : ''; +}; + +sub time_or_blank { + my $column = shift; + return sub { + my $record = shift; + my $value = $record->get($column); #mmm closures + $value ? time2str('%b %d %Y', $value ) : ''; + }; +} + +my $html_init = ''; + +my $title = 'Historical Package View - '; +if ( $date == 0 ) { + $title .= 'start'; +} elsif ( $date == 4294967295 ) { + $title .= 'present'; +} else { + $title .= time2str('%h %o %Y', $date); +} + -- 2.11.0