From: Jonathan Prykop Date: Thu, 28 Jan 2016 21:42:43 +0000 (-0600) Subject: RT#39638: VoIP Usage cost reporting [bug fixes/cleanup] X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=commitdiff_plain;h=488d3c15c9aaea2a17826600ee332f0af9b0634d RT#39638: VoIP Usage cost reporting [bug fixes/cleanup] --- diff --git a/FS/FS/cdr.pm b/FS/FS/cdr.pm index f8fea35d7..5a1d8ea23 100644 --- a/FS/FS/cdr.pm +++ b/FS/FS/cdr.pm @@ -464,9 +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, 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.) +rated_classnum, rated_ratename. If rated_ratedetailnum is provided, +will also 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. @@ -504,7 +504,7 @@ 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'}; + $self->rated_cost($self->rate_cost) if $opt{'rated_ratedetailnum'}; return $self->replace(); @@ -1005,7 +1005,6 @@ 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/elements/menu.html b/httemplate/elements/menu.html index 1d5789d04..13478e979 100644 --- a/httemplate/elements/menu.html +++ b/httemplate/elements/menu.html @@ -290,6 +290,8 @@ $report_rating{'Call Detail Records (CDRs)'} = [ $fsurl.'search/report_cdr.html' if $curuser->access_right("Usage: Call Detail Records (CDRs)"); $report_rating{'Unrateable CDRs'} = [ $fsurl.'search/cdr.html?freesidestatus=failed;cdrbatchnum=_ALL_' ] if $curuser->access_right("Usage: Unrateable CDRs"); +$report_rating{'Customer CDRs Profit/Loss'} = [ $fsurl.'search/report_customer_cdr_profit.html', 'Profit/loss from customer CDRs' ] + if $curuser->access_right('Financial reports'); if ( $curuser->access_right("Usage: Time worked") ) { $report_rating{'Time worked'} = [ $fsurl.'search/report_rt_transaction.html', '' ]; $report_rating{'Time worked summary per ticket'} = [ $fsurl.'search/report_rt_ticket.html', '' ]; @@ -398,8 +400,6 @@ if( $curuser->access_right('Financial reports') ) { $report_financial{'Customer Accounting Summary'} = [ $fsurl.'search/report_customer_accounting_summary.html', 'Customer accounting summary report' ]; - $report_financial{'Customer Usage Profit/Loss'} = [ $fsurl.'search/report_customer_usage_profit.html', 'Customer usage profit/loss' ]; - } elsif($curuser->access_right('Receivables report')) { $report_financial{'A/R Aging'} = [ $fsurl.'search/report_receivables.html', 'Accounts Receivable Aging report' ]; diff --git a/httemplate/search/customer_cdr_profit.html b/httemplate/search/customer_cdr_profit.html new file mode 100644 index 000000000..8dc06636a --- /dev/null +++ b/httemplate/search/customer_cdr_profit.html @@ -0,0 +1,253 @@ +% 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->{colspan} || 1; + } #$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') + && $FS::CurrentUser::CurrentUser->access_right('List rating data'); + +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 CDRs 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/customer_usage_profit.html b/httemplate/search/customer_usage_profit.html deleted file mode 100644 index 9fcc92259..000000000 --- a/httemplate/search/customer_usage_profit.html +++ /dev/null @@ -1,252 +0,0 @@ -% 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_cdr_profit.html b/httemplate/search/report_customer_cdr_profit.html new file mode 100755 index 000000000..2a5efc3eb --- /dev/null +++ b/httemplate/search/report_customer_cdr_profit.html @@ -0,0 +1,30 @@ +<% include('/elements/header.html', 'Customer CDRs 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') + && $FS::CurrentUser::CurrentUser->access_right('List rating data'); + + diff --git a/httemplate/search/report_customer_usage_profit.html b/httemplate/search/report_customer_usage_profit.html deleted file mode 100755 index f16489b99..000000000 --- a/httemplate/search/report_customer_usage_profit.html +++ /dev/null @@ -1,29 +0,0 @@ -<% 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'); - -