diff options
| -rw-r--r-- | FS/FS/cust_bill_pkg.pm | 80 | ||||
| -rw-r--r-- | httemplate/search/cust_bill_pkg.cgi | 107 | ||||
| -rw-r--r-- | httemplate/search/prepaid_income.html | 56 | ||||
| -rw-r--r-- | httemplate/search/report_prepaid_income.html | 2 |
4 files changed, 154 insertions, 91 deletions
diff --git a/FS/FS/cust_bill_pkg.pm b/FS/FS/cust_bill_pkg.pm index 1ee5c0943..f196a0a22 100644 --- a/FS/FS/cust_bill_pkg.pm +++ b/FS/FS/cust_bill_pkg.pm @@ -1067,6 +1067,86 @@ sub _X_show_zero { =back +=head1 CLASS METHODS + +=over 4 + +=item owed_sql [ BEFORE, AFTER, OPTIONS ] + +Returns an SQL expression for the amount owed. BEFORE and AFTER specify +a date window. OPTIONS may include 'no_usage' (excludes usage charges) +and 'setuprecur' (set to "setup" or "recur" to limit to one or the other). + +=cut + +sub owed_sql { + my ($class, $start, $end, %opt) = @_; + my $charged = + $opt{setuprecur} =~ /^s/ ? 'setup' : + $opt{setuprecur} =~ /^r/ ? 'recur' : + 'setup + recur'; + + if ( $opt{no_usage} ) { + $charged .= ' - ' . $class->usage_sql; + } + + '(' . $charged . + ' - ' . $class->paid_sql($start, $end, %opt) . + ' - ' . $class->credited_sql($start, $end, %opt) . ')' +} + +=item usage_sql + +Returns an SQL expression for the total usage charges in details on +an item. + +=cut + +sub usage_sql { + my $class = shift; + "(SELECT COALESCE(SUM(cust_bill_pkg_detail.amount),0) + FROM cust_bill_pkg_detail + WHERE cust_bill_pkg_detail.billpkgnum = cust_bill_pkg.billpkgnum)" +} + +=item paid_sql [ BEFORE, AFTER, OPTIONS ] + +Returns an SQL expression for the sum of payments applied to this item. + +=cut + +sub paid_sql { + my ($class, $start, $end, %opt) = @_; + $start = $start ? "AND cust_bill_pay._date <= $start" : ''; + $end = $end ? "AND cust_bill_pay._date > $end" : ''; + my $setuprecur = + $opt{setuprecur} =~ /^s/ ? 'setup' : + $opt{setuprecur} =~ /^r/ ? 'recur' : + ''; + $setuprecur &&= "AND setuprecur = '$setuprecur'"; + "( SELECT COALESCE(SUM(cust_bill_pay_pkg.amount),0) + FROM cust_bill_pay_pkg JOIN cust_bill_pay USING (billpaynum) + WHERE cust_bill_pay_pkg.billpkgnum = cust_bill_pkg.billpkgnum + $start $end $setuprecur )"; +} + +sub credited_sql { + my ($class, $start, $end, %opt) = @_; + $start = $start ? "AND cust_credit_bill._date <= $start" : ''; + $end = $end ? "AND cust_credit_bill._date > $end" : ''; + my $setuprecur = + $opt{setuprecur} =~ /^s/ ? 'setup' : + $opt{setuprecur} =~ /^r/ ? 'recur' : + ''; + $setuprecur &&= "AND setuprecur = '$setuprecur'"; + "( SELECT COALESCE(SUM(cust_credit_bill_pkg.amount),0) + FROM cust_credit_bill_pkg JOIN cust_credit_bill USING (creditbillnum) + WHERE cust_credit_bill_pkg.billpkgnum = cust_bill_pkg.billpkgnum + $start $end $setuprecur )"; +} + +=back + =head1 BUGS setup and recur shouldn't be separate fields. There should be one "amount" diff --git a/httemplate/search/cust_bill_pkg.cgi b/httemplate/search/cust_bill_pkg.cgi index 820b17864..fd215969a 100644 --- a/httemplate/search/cust_bill_pkg.cgi +++ b/httemplate/search/cust_bill_pkg.cgi @@ -35,16 +35,9 @@ # they're not applicable to pkg_tax search sub { my $cust_bill_pkg = shift; if ( $unearned ) { - my $period = - $cust_bill_pkg->edate - $cust_bill_pkg->sdate; - my $elapsed = $unearned - $cust_bill_pkg->sdate; - $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->$base ); + sprintf($money_char.'%.2f', + $cust_bill_pkg->unearned_revenue) } else { sprintf($money_char.'%.2f', $cust_bill_pkg->setup ); @@ -56,7 +49,7 @@ ), sub { my $row = shift; my $value = 0; - if ( $use_usage eq 'recurring' ) { + if ( $use_usage eq 'recurring' or $unearned ) { $value = $row->recur - $row->usage; } elsif ( $use_usage eq 'usage' ) { $value = $row->usage; @@ -67,7 +60,10 @@ }, ( $unearned ? ( sub { time2str('%b %d %Y', shift->sdate ) }, - sub { time2str('%b %d %Y', shift->edate ) }, + # shift edate back a day + # 82799 = 3600*23 - 1 + # (to avoid skipping a day during DST) + sub { time2str('%b %d %Y', shift->edate - 82799 ) }, ) : () ), @@ -79,9 +75,11 @@ '', 'setup', #broken in $unearned case i guess ( $unearned ? ('', '') : () ), - ( $use_usage eq 'recurring' ? 'recur - usage' : - $use_usage eq 'usage' ? 'usage' - : 'recur' + ( $use_usage eq 'recurring' or $unearned + ? 'recur - usage' : + $use_usage eq 'usage' + ? 'usage' + : 'recur' ), ( $unearned ? ('sdate', 'edate') : () ), 'invnum', @@ -142,9 +140,9 @@ my $conf = new FS::Conf; my $unearned = ''; my $unearned_mode = ''; my $unearned_base = ''; +my $unearned_sql = ''; -my @select = ( 'cust_bill_pkg.*', - 'cust_bill._date', ); +my @select = ( 'cust_bill_pkg.*', 'cust_bill._date' ); my ($join_cust, $join_pkg ) = ('', ''); #here is the agent virtualization @@ -346,7 +344,10 @@ if ( $cgi->param('out') ) { qw( district city county state locationtaxid ) ); -} elsif ( $cgi->param('unearned_now') =~ /^(\d+)$/ ) { +} + +# unearned revenue mode +if ( $cgi->param('unearned_now') =~ /^(\d+)$/ ) { $unearned = $1; $unearned_mode = $cgi->param('mode'); @@ -364,25 +365,32 @@ if ( $cgi->param('out') ) { "part_pkg.freq NOT LIKE '%w'"; } - if ( !$unearned_mode or $unearned_mode eq 'billed' ) { - $unearned_base = 'cust_bill_pkg.recur'; + my $usage_sql = FS::cust_bill_pkg->usage_sql; + push @select, "($usage_sql) AS usage"; # we need this + my $owed_sql = FS::cust_bill_pkg->owed_sql($unearned, '', + setuprecur => 'recur', no_usage => 1); + push @select, "($owed_sql) AS owed"; # also this + + $unearned_base = "(cust_bill_pkg.recur - $usage_sql)"; + my $period = "CAST(cust_bill_pkg.edate - cust_bill_pkg.sdate AS REAL)"; + my $elapsed = "GREATEST( $unearned - cust_bill_pkg.sdate, 0 )"; + my $remaining = "(1 - $elapsed/$period)"; + + $unearned_sql = "CAST( + GREATEST( ( $unearned_base * $remaining ) - $owed_sql, 0 ) + AS DECIMAL(10,2) + )"; + push @select, "$unearned_sql AS unearned_revenue"; + + if ( $unearned_mode eq 'billed' ) { + # include only rows that have some unearned portion + push @where, "$unearned_base > 0"; } 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'"; + # include only those that have some _paid_ unearned portion + push @where, "$unearned_sql > 0"; } + } if ( $cgi->param('itemdesc') ) { @@ -500,12 +508,12 @@ if ( $cgi->param('pkg_tax') ) { $count_query = "SELECT COUNT(DISTINCT billpkgnum), "; } - if ( $use_usage eq 'recurring' ) { + if ( $unearned ) { + $count_query .= "SUM( $unearned_base ), SUM( $unearned_sql )"; + } elsif ( $use_usage eq 'recurring' ) { $count_query .= "SUM(setup + recur - usage)"; } elsif ( $use_usage eq 'usage' ) { $count_query .= "SUM(usage)"; - } elsif ( $unearned ) { - $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') { @@ -514,29 +522,10 @@ if ( $cgi->param('pkg_tax') ) { $count_query .= "SUM(cust_bill_pkg.setup + cust_bill_pkg.recur)"; } - if ( $unearned ) { - - #false laziness w/report_prepaid_income.cgi - - my $float = 'REAL'; #'DOUBLE PRECISION'; - - my $period = "CAST(cust_bill_pkg.edate - cust_bill_pkg.sdate AS $float)"; - my $elapsed = "(CASE WHEN cust_bill_pkg.sdate > $unearned - THEN 0 - ELSE ($unearned - cust_bill_pkg.sdate) - END)"; - #my $elapsed = "CAST($unearned - cust_bill_pkg.sdate AS $float)"; - - my $remaining = "(1 - $elapsed/$period)"; - - $count_query .= ", SUM($remaining * $unearned_base)"; - - } - } -$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 ) '; if ( $cgi->param('nottax') ) { @@ -613,7 +602,7 @@ my $query = { 'table' => 'cust_bill_pkg', 'addl_from' => "$join_cust $join_pkg", 'hashref' => {}, - 'select' => join(', ', @select ), + 'select' => join(",\n", @select ), 'extra_sql' => $where, 'order_by' => 'ORDER BY cust_bill._date, billpkgnum', }; @@ -625,7 +614,7 @@ my $conf = new FS::Conf; my $money_char = $conf->config('money_char') || '$'; my $owed_sub = sub { - $money_char . shift->owed_recur; + $money_char . shift->get('owed') # owed_recur is not correct here }; my $payment_date_sub = sub { #my $cust_bill_pkg = shift; diff --git a/httemplate/search/prepaid_income.html b/httemplate/search/prepaid_income.html index d224af9d7..deed33a33 100644 --- a/httemplate/search/prepaid_income.html +++ b/httemplate/search/prepaid_income.html @@ -92,6 +92,12 @@ my $now = $cgi->param('date') && parse_datetime($cgi->param('date')) || $time; $now =~ /^(\d+)$/ or die "unparsable date?"; $now = $1; +my $dt = DateTime->from_epoch(epoch => $now, time_zone => 'local'); +$dt->truncate(to => 'day'); # local midnight on the report day +$dt->add(days => 1); # the day after that +$dt->subtract(seconds => 1); # the last second of the report day +$now = $dt->epoch; + my $link = "cust_bill_pkg.cgi?nottax=1;unearned_now=$now;mode=$mode"; if ( $cgi->param('include_monthly') ) { @@ -132,37 +138,23 @@ foreach my $agentnum (@agentnums) { my( $total, $total_legacy ) = ( 0, 0 ); - my $float = 'REAL'; #'DOUBLE PRECISION'; - - my $period = "CAST(cust_bill_pkg.edate - cust_bill_pkg.sdate AS $float)"; - my $elapsed = "(CASE WHEN cust_bill_pkg.sdate > $now - THEN 0 - ELSE ($now - cust_bill_pkg.sdate) - END)"; - #my $elapsed = "CAST($unearned - cust_bill_pkg.sdate AS $float)"; - + # false laziness + my $usage_sql = FS::cust_bill_pkg->usage_sql; + my $owed_sql = FS::cust_bill_pkg->owed_sql($now, '', + setuprecur => 'recur', no_usage => 1); + my $unearned_base = "(cust_bill_pkg.recur - $usage_sql)"; + my $period = "CAST(cust_bill_pkg.edate - cust_bill_pkg.sdate AS REAL)"; + my $elapsed = "GREATEST( $now - cust_bill_pkg.sdate, 0 )"; my $remaining = "(1 - $elapsed/$period)"; - 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)"; + my $unearned_sql = "CAST( + GREATEST( $unearned_base * $remaining - $owed_sql, 0 ) + AS DECIMAL(10,2) + )"; + + my $float = 'REAL'; #'DOUBLE PRECISION'; + + my $select = "SUM( $unearned_sql )"; if ( !$cgi->param('include_monthly') ) { # all except freq != 0; one-time charges should never be included @@ -173,8 +165,11 @@ foreach my $agentnum (@agentnums) { AND part_pkg.freq NOT LIKE '%w'"; } + # $mode actually doesn't matter here, since unpaid invoices have zero + # unearned revenue + my $sql = - "SELECT $select FROM $from + "SELECT $select FROM cust_bill_pkg 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) @@ -185,7 +180,6 @@ foreach my $agentnum (@agentnums) { AND part_pkg.freq != '0' $where "; - my $sth = dbh->prepare($sql) or die dbh->errstr; $sth->execute or die $sth->errstr; my $total = $sth->fetchrow_arrayref->[0]; diff --git a/httemplate/search/report_prepaid_income.html b/httemplate/search/report_prepaid_income.html index 90b72f60b..4743e2d21 100644 --- a/httemplate/search/report_prepaid_income.html +++ b/httemplate/search/report_prepaid_income.html @@ -41,7 +41,7 @@ <TD ALIGN="right"> <INPUT TYPE="checkbox" NAME="include_monthly" VALUE=1> </TD> - <TD ALIGN="left"><% emt('Include packages with period <= 1 month') %> + <TD ALIGN="left"><% mt('Include packages with period ≤ 1 month') %> </TD> </TR> <TR> |