From f1ce861d8f7a6431934b4bf5b69f5e7573043818 Mon Sep 17 00:00:00 2001 From: Jonathan Prykop Date: Mon, 25 Jan 2016 22:59:47 -0600 Subject: [PATCH] RT#39638: VoIP Usage cost reporting --- FS/FS/Report/Table.pm | 42 +++- FS/FS/Schema.pm | 1 + FS/FS/cdr.pm | 8 +- httemplate/search/customer_usage_profit.html | 252 +++++++++++++++++++++ .../search/report_customer_usage_profit.html | 29 +++ 5 files changed, 325 insertions(+), 7 deletions(-) create mode 100644 httemplate/search/customer_usage_profit.html create mode 100755 httemplate/search/report_customer_usage_profit.html diff --git a/FS/FS/Report/Table.pm b/FS/FS/Report/Table.pm index eeb99bac5..5fb56404d 100644 --- a/FS/FS/Report/Table.pm +++ b/FS/FS/Report/Table.pm @@ -599,6 +599,10 @@ sub _cust_bill_pkg_recurring { $self->in_time_period_and_agent($speriod, $eperiod, $agentnum, $_date); } + if ( $opt{'custnum'} =~ /^(\d+)$/ ) { + push @where, "(cust_main.custnum = $1)"; + } + return " FROM $cust_bill_pkg $cust_bill_pkg_join @@ -606,6 +610,16 @@ sub _cust_bill_pkg_recurring { } +=item cust_bill_pkg_recur: the total recur charges + +Most arguments as for C, plus: + +'custnum': limit to this customer + +'cost': if true, return total recur costs instead + +=cut + sub cust_bill_pkg_recur { my $self = shift; my ($speriod, $eperiod, $agentnum, %opt) = @_; @@ -632,9 +646,11 @@ sub cust_bill_pkg_recur { ($cust_bill_pkg.edate - $cust_bill_pkg.sdate)"; } - my $total_sql = - "SELECT COALESCE(SUM(($cust_bill_pkg.recur - $item_usage) $recur_fraction),0)" . - $self->_cust_bill_pkg_recurring(@_); + my $total_sql = $opt{'cost'} + ? "SELECT SUM(part_pkg.recur_cost)" + : "SELECT COALESCE(SUM(($cust_bill_pkg.recur - $item_usage) $recur_fraction),0)"; + + $total_sql .= $self->_cust_bill_pkg_recurring(@_); $self->scalar_sql($total_sql); } @@ -650,10 +666,14 @@ sub cust_bill_pkg_count_pkgnum { =item cust_bill_pkg_detail: the total usage charges in detail lines. -Arguments as for C, plus: +Most arguments as for C, plus: 'usageclass': limit to this usage class number. +'custnum': limit to this customer + +'cost': if true, return total usage costs instead + =cut sub cust_bill_pkg_detail { @@ -686,7 +706,16 @@ sub cust_bill_pkg_detail { ); } + if ( $opt{'custnum'} =~ /^(\d+)$/ ) { + push @where, "(cust_main.custnum = $1)"; + } + my $total_sql = " SELECT SUM(cust_bill_pkg_detail.amount) "; + my $extra_join = ''; + if ($opt{'cost'}) { + $extra_join = " JOIN cdr USING ( detailnum ) "; + $total_sql = " SELECT SUM(cdr.rated_cost) "; + } $total_sql .= " FROM cust_bill_pkg_detail @@ -696,8 +725,9 @@ sub cust_bill_pkg_detail { LEFT JOIN cust_pkg ON cust_bill_pkg.pkgnum = cust_pkg.pkgnum LEFT JOIN part_pkg USING ( pkgpart ) LEFT JOIN part_pkg AS override ON pkgpart_override = override.pkgpart - LEFT JOIN part_fee USING ( feepart ) - WHERE ".join( ' AND ', grep $_, @where ); + LEFT JOIN part_fee USING ( feepart ) + ".$extra_join. + " WHERE ".join( ' AND ', grep $_, @where ); $self->scalar_sql($total_sql); diff --git a/FS/FS/Schema.pm b/FS/FS/Schema.pm index c855b1fba..cd05be36f 100644 --- a/FS/FS/Schema.pm +++ b/FS/FS/Schema.pm @@ -3819,6 +3819,7 @@ sub tables_hashref { 'rated_ratedetailnum', 'int', 'NULL', '', '', '', 'rated_classnum', 'int', 'NULL', '', '', '', 'rated_ratename', 'varchar', 'NULL', $char_d, '', '', + 'rated_cost', 'decimal', 'NULL', '10,4', '', '', 'carrierid', 'bigint', 'NULL', '', '', '', diff --git a/FS/FS/cdr.pm b/FS/FS/cdr.pm index cdca6fc75..f8fea35d7 100644 --- a/FS/FS/cdr.pm +++ b/FS/FS/cdr.pm @@ -214,6 +214,7 @@ sub table_info { #'upstream_rateplanid' => '', #'ratedetailnum' => '', 'rated_price' => 'Rated price', + 'rated_cost' => 'Rated cost', #'distance' => '', #'islocal' => '', #'calltypenum' => '', @@ -463,7 +464,9 @@ Sets the status and rated price. Available options are: inbound, rated_pretty_dst, rated_regionname, rated_seconds, rated_minutes, rated_granularity, rated_ratedetailnum, -rated_classnum, rated_ratename. +rated_classnum, rated_ratename, and set_rate_cost (if true, will set +a recalculated L in the rated_cost field after the other +fields are set; does not work with inbound.) If there is an error, returns the error, otherwise returns false. @@ -501,6 +504,8 @@ sub set_status_and_rated_price { qw( pretty_dst regionname seconds minutes granularity ratedetailnum classnum ratename ); $self->svcnum($svcnum) if $svcnum; + $self->rated_cost($self->rate_cost) if $opt{'set_rate_cost'}; + return $self->replace(); } @@ -1000,6 +1005,7 @@ sub rate_prefix { 'rated_ratedetailnum' => $rate_detail->ratedetailnum, 'rated_classnum' => $rate_detail->classnum, #rated_ratedetailnum? 'rated_ratename' => $ratename, #not rate_detail - Intrastate/Interstate + 'set_rate_cost' => 1, ); } diff --git a/httemplate/search/customer_usage_profit.html b/httemplate/search/customer_usage_profit.html new file mode 100644 index 000000000..9fcc92259 --- /dev/null +++ b/httemplate/search/customer_usage_profit.html @@ -0,0 +1,252 @@ +% if ( $cgi->param('_type') =~ /(xls)$/ ) { +<%perl> + # egregious false laziness w/ search/report_tax-xls.cgi + my $format = $FS::CurrentUser::CurrentUser->spreadsheet_format; + my $filename = $cgi->url(-relative => 1); + $filename =~ s/\.html$//; + $filename .= $format->{extension}; + http_header('Content-Type' => $format->{mime_type}); + http_header('Content-Disposition' => qq!attachment;filename="$filename"!); + + my $output = ''; + my $XLS = IO::String->new($output); + my $workbook = $format->{class}->new($XLS) + or die "Error opening .xls file: $!"; + + my $worksheet = $workbook->add_worksheet('Summary'); + + my %format = ( + header => { + size => 11, + bold => 1, + align => 'center', + valign => 'vcenter', + text_wrap => 1, + }, + money => { + size => 11, + align => 'right', + valign => 'bottom', + num_format=> 8, + }, + '' => {}, + ); + my %default = ( + font => 'Calibri', + border => 1, + ); + foreach (keys %format) { + my %f = (%default, %{$format{$_}}); + $format{$_} = $workbook->add_format(%f); + $format{"m_$_"} = $workbook->add_format(%f); + } + + my ($r, $c) = (0, 0); + for my $row (@rows) { + $c = 0; + my $thisrow = shift @cells; + for my $cell (@$thisrow) { + if (!ref($cell)) { + # placeholder, so increment $c so that we write to the correct place + $c++; + next; + } + # format name + my $f = ''; + $f = 'header' if $row->{header} or $cell->{header}; + $f = 'money' if $cell->{format} eq 'money'; + if ( $cell->{rowspan} > 1 or $cell->{colspan} > 1 ) { + my $range = xl_range_formula( + 'Summary', + $r, $r - 1 + ($cell->{rowspan} || 1), + $c, $c - 1 + ($cell->{colspan} || 1) + ); + #warn "merging $range\n"; + $worksheet->merge_range($range, $cell->{value}, $format{"m_$f"}); + } else { + #warn "writing ".xl_rowcol_to_cell($r, $c)."\n"; + $worksheet->write( $r, $c, $cell->{value}, $format{$f} ); + } + $c++; + } #$cell + $r++; + } #$row + $workbook->close; + + http_header('Content-Length' => length($output)); + $m->print($output); + +% } else { +<& /elements/header.html, $title &> +% my $myself = $cgi->self_url; +

+Download full reports
+as ">Excel spreadsheet +

+ + +% foreach my $rowinfo (@rows) { + {class} ? ' class="'.$rowinfo->{class}.'"' : ''%>> +% my $thisrow = shift @cells; +% foreach my $cell (@$thisrow) { +% next if !ref($cell); # placeholders +% my $td = $cell->{header} ? 'th' : 'td'; +% my $style = ''; +% $style .= ' class="'.$cell->{class}.'"' if $cell->{class}; +% $style .= " rowspan=".$cell->{rowspan} if $cell->{rowspan} > 1; +% $style .= " colspan=".$cell->{colspan} if $cell->{colspan} > 1; +% $style .= ' style="color: red"' if $cell->{value} < 0; + <<%$td%><%$style%>><% $cell->{value} |h %>> +% } + +% } +
+ +<& /elements/footer.html &> +% } +<%init> + +die "access denied" + unless $FS::CurrentUser::CurrentUser->access_right('Financial reports'); + +my ($agentnum,$sel_agent); +if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) { + $agentnum = $1; + $sel_agent = qsearchs('agent', { 'agentnum' => $agentnum } ); + die "agentnum $agentnum not found!" unless $sel_agent; +} +my $title = $sel_agent ? $sel_agent->agent.' ' : ''; + +$title .= 'Customer Usage Profit/Loss Report'; + +my @items = ('cust_bill_pkg_recur', 'cust_bill_pkg_recur', 'cust_bill_pkg_detail', 'cust_bill_pkg_detail' ); +my @params = ( [], [ 'cost' => 1 ], [], [ 'cost' => 1 ] ); + +my @labels = (); +my @cross_params = (); + +my %search_hash; +foreach (qw(agentnum)) { + if ( defined $cgi->param($_) ) { + $search_hash{$_} = $cgi->param($_); + } +} + +my $query = FS::cust_main::Search->search(\%search_hash); +my @cust_main = qsearch($query); + +foreach my $cust_main (@cust_main) { + push @cross_params, [ ('custnum' => $cust_main->custnum) ]; +} + +my %opt = ( + items => \@items, + params => \@params, + cross_params => \@cross_params, + agentnum => $agentnum, +); +for ( qw(start_month start_year end_month end_year) ) { + if ( $cgi->param($_) =~ /^(\d+)$/ ) { + $opt{$_} = $1; + } +} + +my $report = FS::Report::Table::Monthly->new(%opt); +my $data = $report->data; + +### False laziness with customer_accounting_summary.html +my @total; + +my @rows; # hashes of row info +my @cells; # arrayrefs of cell info +# We use Excel currency format, but not Excel dates, because +# these are whole months and there's no nice way to express that. +# This is the historical behavior for monthly reports. + +# header row +$rows[0] = {}; +$cells[0] = [ + { header => 1, rowspan => 2 }, + map { + { header => 1, colspan => 5, value => time2str('%b %Y', $_) } + } @{ $data->{speriod} } +]; +my $ncols = scalar(@{ $data->{speriod} }); + +$rows[1] = {}; +$cells[1] = [ '', + map { + ( + { header => 1, value => mt('Recur Fee') }, + { header => 1, value => mt('Recur Cost') }, + { header => 1, value => mt('Usage Fee') }, + { header => 1, value => mt('Usage Cost') }, + { header => 1, value => mt('Profit'), class => 'shaded' }, + ) } (1..$ncols) +]; + +my $row = 0; +foreach my $cust_main (@cust_main) { # correspond to cross_params + my $skip = 1; # skip the customer iff ALL of their values are zero + push @rows, {}; + my @thisrow; + # customer name + push @thisrow, + { value => $cust_main->name, + header => 1 + }; + for my $col (0..$ncols-1) { # the month + my $profit = 0; + for my $item (0..3) { # recur/recur_cost/usage/usage_cost + my $value = $data->{data}[$item][$col][$row]; + $skip = 0 if abs($value) > 0.005; + push @thisrow, { value => sprintf('%0.2f', $value), format => 'money' }; + $total[$col * 5 + $item] += $value; + $profit += (($item % 2) ? -1 : 1) * $value; + } #item + push @thisrow, { + value => sprintf('%0.2f', $profit), + format => 'money', + class => 'shaded', + }; + $total[$col * 5 + 4] += $profit; + } #month + push @cells, \@thisrow; + + if ( $skip ) { + # all values are zero--remove the rows we just added + pop @rows; + pop @cells; + } + $row++; +} + +push @rows, { class => 'total' }; +my @thisrow; +push @thisrow, + { value => mt('Total'), + header => 1 + }; +for my $col (0..($ncols * 5)-1) { # month and recur/recur_cost/usage/usage_cost/profit + my $value = $total[$col]; + push @thisrow, { + value => sprintf('%0.2f', $value), + format => 'money', + class => ($col % 5 == 4) ? 'totalshaded' : 'total', + }; +} +push @cells, \@thisrow; + + diff --git a/httemplate/search/report_customer_usage_profit.html b/httemplate/search/report_customer_usage_profit.html new file mode 100755 index 000000000..f16489b99 --- /dev/null +++ b/httemplate/search/report_customer_usage_profit.html @@ -0,0 +1,29 @@ +<% include('/elements/header.html', 'Customer Usage Profit/Loss Report' ) %> + +
+ + + + <% include( '/elements/tr-select-agent.html', + 'curr_value' => scalar( $cgi->param('agentnum') ), + 'label' => 'Agent ', + 'disable_empty' => 0, + ) + %> + + <% include('/elements/tr-select-from_to.html' ) %> + +
+ +
+ + +
+ +<% include('/elements/footer.html') %> +<%init> + +die "access denied" + unless $FS::CurrentUser::CurrentUser->access_right('Financial reports'); + + -- 2.11.0