From d54109cc3035ce63ab0f6c2ec94317e378887d49 Mon Sep 17 00:00:00 2001 From: Mark Wells Date: Fri, 27 Apr 2012 12:07:32 -0700 Subject: [PATCH] improvements to prepaid income report, #13289 --- httemplate/search/cust_bill_pkg.cgi | 73 +++++++++++----- ...port_prepaid_income.cgi => prepaid_income.html} | 98 ++++++++++++---------- httemplate/search/report_prepaid_income.html | 19 ++++- 3 files changed, 120 insertions(+), 70 deletions(-) rename httemplate/search/{report_prepaid_income.cgi => prepaid_income.html} (75%) diff --git a/httemplate/search/cust_bill_pkg.cgi b/httemplate/search/cust_bill_pkg.cgi index b08024cb0..820b17864 100644 --- a/httemplate/search/cust_bill_pkg.cgi +++ b/httemplate/search/cust_bill_pkg.cgi @@ -9,7 +9,9 @@ 'header' => [ emt('Description'), ( $unearned - ? ( emt('Unearned'), emt('Owed'), emt('Payment date') ) + ? ( emt('Unearned'), + emt('Owed'), # useful in 'paid' mode? + emt('Payment date') ) : ( emt('Setup charge') ) ), ( $use_usage eq 'usage' @@ -39,9 +41,10 @@ $elapsed = 0 if $elapsed < 0; my $remaining = 1 - $elapsed/$period; + my $base = ($unearned_mode eq 'paid' ? 'total_paid' : 'recur'); sprintf($money_char. '%.2f', - $remaining * $cust_bill_pkg->recur ); + $remaining * $cust_bill_pkg->$base ); } else { sprintf($money_char.'%.2f', $cust_bill_pkg->setup ); @@ -137,6 +140,12 @@ die "access denied" my $conf = new FS::Conf; my $unearned = ''; +my $unearned_mode = ''; +my $unearned_base = ''; + +my @select = ( 'cust_bill_pkg.*', + 'cust_bill._date', ); +my ($join_cust, $join_pkg ) = ('', ''); #here is the agent virtualization my $agentnums_sql = @@ -146,14 +155,18 @@ my @where = ( $agentnums_sql ); my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi); +if ( $cgi->param('status') =~ /^([a-z]+)$/ ) { + push @where, FS::cust_main->cust_status_sql . " = '$1'"; +} + if ( $cgi->param('distribute') == 1 ) { push @where, "sdate <= $ending", "edate > $beginning", ; } else { - push @where, "_date >= $beginning", - "_date <= $ending"; + push @where, "cust_bill._date >= $beginning", + "cust_bill._date <= $ending"; } if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) { @@ -336,16 +349,40 @@ if ( $cgi->param('out') ) { } elsif ( $cgi->param('unearned_now') =~ /^(\d+)$/ ) { $unearned = $1; + $unearned_mode = $cgi->param('mode'); push @where, "cust_bill_pkg.sdate < $unearned", "cust_bill_pkg.edate > $unearned", "cust_bill_pkg.recur != 0", - "part_pkg.freq != '0'", + "part_pkg.freq != '0'"; + + if ( !$cgi->param('include_monthly') ) { + push @where, "part_pkg.freq != '1'", "part_pkg.freq NOT LIKE '%h'", "part_pkg.freq NOT LIKE '%d'", "part_pkg.freq NOT LIKE '%w'"; + } + if ( !$unearned_mode or $unearned_mode eq 'billed' ) { + $unearned_base = 'cust_bill_pkg.recur'; + } + elsif ( $unearned_mode eq 'paid' ) { + $join_pkg .= "JOIN ( + SELECT billpkgnum, SUM(cust_bill_pay_pkg.amount) AS total_paid + FROM cust_bill_pay_pkg + JOIN cust_bill_pay USING (billpaynum) + JOIN cust_pay USING (paynum) + WHERE cust_bill_pay_pkg.setuprecur = 'recur' + AND cust_pay._date <= $unearned + GROUP BY billpkgnum + ) AS cust_bill_pkg_paid USING (billpkgnum)"; + $unearned_base = 'total_paid'; + push @select, 'total_paid'; + } + else { + die "invalid mode '$unearned_mode'"; + } } if ( $cgi->param('itemdesc') ) { @@ -468,7 +505,7 @@ if ( $cgi->param('pkg_tax') ) { } elsif ( $use_usage eq 'usage' ) { $count_query .= "SUM(usage)"; } elsif ( $unearned ) { - $count_query .= "SUM(cust_bill_pkg.recur)"; + $count_query .= "SUM($unearned_base)"; } elsif ( scalar( grep( /locationtaxid/, $cgi->param ) ) ) { $count_query .= "SUM( COALESCE(cust_bill_pkg_tax_rate_location.amount, cust_bill_pkg.setup + cust_bill_pkg.recur))"; } elsif ( $cgi->param('iscredit') eq 'rate') { @@ -492,23 +529,21 @@ if ( $cgi->param('pkg_tax') ) { my $remaining = "(1 - $elapsed/$period)"; - $count_query .= ", SUM($remaining * cust_bill_pkg.recur)"; + $count_query .= ", SUM($remaining * $unearned_base)"; } } -my $join_cust = ' JOIN cust_bill USING ( invnum ) - LEFT JOIN cust_main USING ( custnum ) '; +$join_cust = ' JOIN cust_bill USING ( invnum ) + LEFT JOIN cust_main USING ( custnum ) '; - -my $join_pkg; if ( $cgi->param('nottax') ) { - $join_pkg = ' LEFT JOIN cust_pkg USING ( pkgnum ) - LEFT JOIN part_pkg USING ( pkgpart ) - LEFT JOIN part_pkg AS override - ON pkgpart_override = override.pkgpart '; + $join_pkg .= ' LEFT JOIN cust_pkg USING ( pkgnum ) + LEFT JOIN part_pkg USING ( pkgpart ) + LEFT JOIN part_pkg AS override + ON pkgpart_override = override.pkgpart '; $join_pkg .= ' LEFT JOIN cust_location USING ( locationnum ) ' if $conf->exists('tax-pkg_address'); @@ -567,9 +602,6 @@ if ($use_usage) { $count_query .= " FROM cust_bill_pkg $join_cust $join_pkg $where"; } -my @select = ( 'cust_bill_pkg.*', - 'cust_bill._date', ); - push @select, 'part_pkg.pkg', 'part_pkg.freq', unless $cgi->param('istax'); @@ -583,7 +615,7 @@ my $query = { 'hashref' => {}, 'select' => join(', ', @select ), 'extra_sql' => $where, - 'order_by' => 'ORDER BY _date, billpkgnum', + 'order_by' => 'ORDER BY cust_bill._date, billpkgnum', }; my $ilink = [ "${p}view/cust_bill.cgi?", 'invnum' ]; @@ -593,9 +625,8 @@ my $conf = new FS::Conf; my $money_char = $conf->config('money_char') || '$'; my $owed_sub = sub { - $money_char. shift->owed_recur; #_recur :/ + $money_char . shift->owed_recur; }; - my $payment_date_sub = sub { #my $cust_bill_pkg = shift; my @cust_pay = sort { $a->_date <=> $b->_date } diff --git a/httemplate/search/report_prepaid_income.cgi b/httemplate/search/prepaid_income.html similarity index 75% rename from httemplate/search/report_prepaid_income.cgi rename to httemplate/search/prepaid_income.html index 2fe5b6f10..d224af9d7 100644 --- a/httemplate/search/report_prepaid_income.cgi +++ b/httemplate/search/prepaid_income.html @@ -58,7 +58,9 @@ <% $actual_label %><% $actual_label ? 'u' : 'U' %>nearned revenue is the amount of unearned revenue <% $actual_label ? 'Freeside has actually' : '' %> -invoiced for packages with longer-than monthly terms. +invoiced for packages with +<% $cgi->param('include_monthly') ? 'terms extending into the future.' + : 'longer-than monthly terms.' %> % if ( $legacy ) {

@@ -82,13 +84,19 @@ my $actual_label = $legacy ? 'Actual ' : ''; #doesn't yet deal with daily/weekly packages +my $mode = $cgi->param('mode'); + my $time = time; my $now = $cgi->param('date') && parse_datetime($cgi->param('date')) || $time; $now =~ /^(\d+)$/ or die "unparsable date?"; $now = $1; -my $link = "cust_bill_pkg.cgi?nottax=1;unearned_now=$now"; +my $link = "cust_bill_pkg.cgi?nottax=1;unearned_now=$now;mode=$mode"; + +if ( $cgi->param('include_monthly') ) { + $link .= ';include_monthly=1'; +} my $curuser = $FS::CurrentUser::CurrentUser; @@ -108,6 +116,13 @@ my @where = (); #here is the agent virtualization push @where, $curuser->agentnums_sql( 'table'=>'cust_main' ); +my $status = ''; +if ( $cgi->param('status') =~ /^([a-z]+)$/ ) { + $status = $1; + $link .= ";status=$status"; + push @where, FS::cust_main->cust_status_sql . " = '$status'"; +} + my %total = (); my %total_legacy = (); foreach my $agentnum (@agentnums) { @@ -117,38 +132,6 @@ foreach my $agentnum (@agentnums) { my( $total, $total_legacy ) = ( 0, 0 ); - # my @cust_bill_pkg = - # grep { $_->cust_pkg && $_->cust_pkg->part_pkg->freq !~ /^([01]|\d+[hdw])$/ } - # qsearch({ - # 'select' => 'cust_bill_pkg.*', - # 'table' => 'cust_bill_pkg', - # 'addl_from' => ' LEFT JOIN cust_bill USING ( invnum ) '. - # ' LEFT JOIN cust_main USING ( custnum ) ', - # 'hashref' => { - # 'recur' => { op=>'!=', value=>0 }, - # 'sdate' => { op=>'<', value=>$now }, - # 'edate' => { op=>'>', value=>$now }, - # }, - # 'extra_sql' => $where, - # }); - # - # foreach my $cust_bill_pkg ( @cust_bill_pkg) { - # my $period = $cust_bill_pkg->edate - $cust_bill_pkg->sdate; - # - # my $elapsed = $now - $cust_bill_pkg->sdate; - # $elapsed = 0 if $elapsed < 0; - # - # my $remaining = 1 - $elapsed/$period; - # - # my $unearned = $remaining * $cust_bill_pkg->recur; - # $total += $unearned; - # - # } - - #re-written in sql: - - #false laziness w/cust_bill_pkg.cgi - my $float = 'REAL'; #'DOUBLE PRECISION'; my $period = "CAST(cust_bill_pkg.edate - cust_bill_pkg.sdate AS $float)"; @@ -160,23 +143,46 @@ foreach my $agentnum (@agentnums) { my $remaining = "(1 - $elapsed/$period)"; - my $select = "SUM($remaining * cust_bill_pkg.recur)"; + my $base; + my $from = ''; + if ( $mode eq 'billed' ) { + $base = 'cust_bill_pkg.recur'; + $from = 'cust_bill_pkg'; + } + elsif ( $mode eq 'paid' ) { + $base = 'cust_bill_pay_pkg.amount'; + $from = 'cust_bill_pay_pkg + LEFT JOIN cust_bill_pkg USING ( billpkgnum ) + LEFT JOIN cust_bill_pay USING ( billpaynum ) + LEFT JOIN cust_pay USING ( paynum )'; + $where .= ' AND ' if $where; + $where .= "cust_bill_pay_pkg.setuprecur = 'recur' + AND cust_pay._date <= $now"; + } + else { + die "invalid mode '$mode'"; + } + my $select = "SUM($remaining * $base)"; - #[...] + if ( !$cgi->param('include_monthly') ) { + # all except freq != 0; one-time charges should never be included + $where .= " + AND part_pkg.freq != '1' + AND part_pkg.freq NOT LIKE '%h' + AND part_pkg.freq NOT LIKE '%d' + AND part_pkg.freq NOT LIKE '%w'"; + } - my $sql = "SELECT $select FROM cust_bill_pkg - LEFT JOIN cust_pkg USING ( pkgnum ) - LEFT JOIN part_pkg USING ( pkgpart ) - LEFT JOIN cust_main USING ( custnum ) + my $sql = + "SELECT $select FROM $from + LEFT JOIN cust_pkg ON (cust_bill_pkg.pkgnum = cust_pkg.pkgnum) + LEFT JOIN part_pkg USING ( pkgpart ) + LEFT JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum) WHERE pkgpart > 0 - AND sdate < $now - AND edate > $now + AND cust_bill_pkg.sdate < $now + AND cust_bill_pkg.edate > $now AND cust_bill_pkg.recur != 0 AND part_pkg.freq != '0' - AND part_pkg.freq != '1' - AND part_pkg.freq NOT LIKE '%h' - AND part_pkg.freq NOT LIKE '%d' - AND part_pkg.freq NOT LIKE '%w' $where "; diff --git a/httemplate/search/report_prepaid_income.html b/httemplate/search/report_prepaid_income.html index 061b24c68..90b72f60b 100644 --- a/httemplate/search/report_prepaid_income.html +++ b/httemplate/search/report_prepaid_income.html @@ -2,7 +2,7 @@ <% include('/elements/init_calendar.html') %> -
+ @@ -13,7 +13,7 @@ - + <% include( '/elements/tr-select-agent.html', 'disable_empty'=>0 ) %> - + + <& /elements/tr-select-cust_main-status.html, + label => mt('Customer Status') &> + <& /elements/tr-select.html, + label => 'Invoice Status', + field => 'mode', + options => [ qw(billed paid) ] &> + + + + -- 2.11.0
As of As of @@ -30,7 +30,20 @@
+ + <% emt('Include packages with period <= 1 month') %> +