%# still not a good way to do rows grouped by some field in a search.html %# report % if ( $type eq 'xls' ) { <% $data %>\ % } else { <& /elements/header.html, $title &>

Download full results
as Excel spreadsheet


<& /elements/table-grid.html &> Package Sales Percentage Commission % my ($custnum, $sales, $commission, $row, $bgcolor) = (0, 0, 0, 0); % foreach my $cust_pkg ( @cust_pkg ) { % if ( $custnum ne $cust_pkg->custnum ) { % # start of a new customer section % my $cust_main = $cust_pkg->cust_main; % $bgcolor = 0; <% $cust_main->display_custnum %>: <% $cust_main->name |h %> % } <% $cust_pkg->pkg_label %> <% sprintf('%.2f', $cust_pkg->sum_charged) %> <% $cust_pkg->percent %>% <% sprintf('%.2f', $cust_pkg->sum_charged * $cust_pkg->percent / 100) %> % $sales += $cust_pkg->sum_charged; % $commission += $cust_pkg->sum_charged * $cust_pkg->percent / 100; % $row++; % $bgcolor = 1-$bgcolor; % $custnum = $cust_pkg->custnum; % } <% emt('[quant,_1,package] with commission', $row) %> <% sprintf('%.2f', $sales) %> <% sprintf('%.2f', $commission) %> <& /elements/footer.html &> % } <%init> die "access denied" unless $FS::CurrentUser::CurrentUser->access_right('Financial reports'); my ($begin, $end) = FS::UI::Web::parse_beginning_ending($cgi); $cgi->param('agentnum') =~ /^(\d+)$/ or die "bad agentnum"; my $agentnum = $1; my $agent = FS::agent->by_key($agentnum); my $title = $agent->agent . ' commissions'; my $sum_charged = '(SELECT SUM(setup + recur) FROM cust_bill_pkg JOIN cust_bill USING (invnum)'. 'WHERE cust_bill_pkg.pkgnum = cust_pkg.pkgnum AND '. "cust_bill._date >= $begin AND cust_bill._date < $end)"; my @select = ( 'cust_pkg.*', 'agent_pkg_class.commission_percent AS percent', "$sum_charged AS sum_charged", ); my $query = { 'table' => 'cust_pkg', 'select' => join(',', @select), 'addl_from' => 'JOIN cust_main USING (custnum) '. 'JOIN part_pkg USING (pkgpart) '. 'JOIN agent_pkg_class ON ( '. 'cust_main.agentnum = agent_pkg_class.agentnum AND '. '( agent_pkg_class.classnum = part_pkg.classnum OR '. '(agent_pkg_class IS NULL AND part_pkg.classnum IS NULL)'. ' ) ) ', 'extra_sql' => "WHERE cust_main.agentnum = $agentnum AND ". 'agent_pkg_class.commission_percent > 0 AND '. "$sum_charged > 0", 'order_by' => 'ORDER BY cust_pkg.custnum ASC', }; my @cust_pkg = qsearch($query); my $money_char = FS::Conf->new->config('money_char') || '$'; my $data = ''; my $type = $cgi->param('_type'); if ( $type eq 'xls') { # some false laziness with the above... my $format = $FS::CurrentUser::CurrentUser->spreadsheet_format; my $filename = 'agent_commission' . $format->{extension}; http_header('Content-Type' => $format->{mime_type}); http_header('Content-Disposition' => qq!attachment;filename="$filename"!); my $XLS = IO::Scalar->new(\$data); my $workbook = $format->{class}->new($XLS); my $worksheet = $workbook->add_worksheet(substr($title, 0, 31)); my $cust_head_format = $workbook->add_format( bold => 1, underline => 1, text_wrap => 0, bg_color => 'white', ); my $col_head_format = $workbook->add_format( bold => 1, align => 'center', bg_color => 'silver' ); my @format; foreach (0, 1) { my %bg = (bg_color => $_ ? 'white' : 'silver'); $format[$_] = { 'text' => $workbook->add_format(%bg), 'money' => $workbook->add_format(%bg, num_format => $money_char.'#0.00'), 'percent' => $workbook->add_format(%bg, num_format => '0.00%'), }; } my $total_format = $workbook->add_format( bg_color => 'yellow', num_format => $money_char.'#0.00', top => 1 ); my ($r, $c) = (0, 0); foreach (qw(Package Sales Percentage Commission)) { $worksheet->write($r, $c++, $_, $col_head_format); } $r++; my ($custnum, $sales, $commission, $row, $bgcolor) = (0, 0, 0, 0); my $label_length = 0; foreach my $cust_pkg ( @cust_pkg ) { if ( $custnum ne $cust_pkg->custnum ) { # start of a new customer section my $cust_main = $cust_pkg->cust_main; my $label = $cust_main->custnum . ': '. $cust_main->name; $bgcolor = 0; $worksheet->set_row($r, 20); $worksheet->merge_range($r, 0, $r, 3, $label, $cust_head_format); $r++; } $c = 0; my $percent = $cust_pkg->percent / 100; $worksheet->write($r, $c++, $cust_pkg->pkg_label, $format[$bgcolor]{text}); $worksheet->write($r, $c++, $cust_pkg->sum_charged, $format[$bgcolor]{money}); $worksheet->write($r, $c++, $percent, $format[$bgcolor]{percent}); $worksheet->write($r, $c++, ($cust_pkg->sum_charged * $percent), $format[$bgcolor]{money}); $label_length = max($label_length, length($cust_pkg->pkg_label)); $sales += $cust_pkg->sum_charged; $commission += $cust_pkg->sum_charged * $cust_pkg->percent / 100; $row++; $bgcolor = 1-$bgcolor; $custnum = $cust_pkg->custnum; $r++; } $c = 0; $label_length = max($label_length, 20); $worksheet->set_column($c, $c, $label_length); $worksheet->write($r, $c++, mt('[quant,_1,package] with commission', $row), $total_format); $worksheet->set_column($c, $c + 2, 11); $worksheet->write($r, $c++, $sales, $total_format); $worksheet->write($r, $c++, '', $total_format); $worksheet->write($r, $c++, $commission, $total_format); $workbook->close; }