-%# still not a good way to do rows grouped by some field in a search.html
-%# report
-% if ( $type eq 'xls' ) {
-<% $data %>\
+% if ( $agentnum ) {
+% my $url = 'agent_pkg_class.html?' . $cgi->query_string;
+<% $cgi->redirect($url) %>
% } else {
-<& /elements/header.html, $title &>
-<P ALIGN="right" CLASS="noprint">
-Download full results<BR>
-as <A HREF="<% $cgi->self_url %>;_type=xls">Excel spreadsheet</A></P>
-<BR>
-<STYLE TYPE="text/css">
-td.cust_head {
- border-left: none;
- border-right: none;
- padding-top: 0.5em;
- font-weight: bold;
- background-color: #ffffff;
-}
-td.money { text-align: right; }
-td.money:before { content: '<% $money_char %>'; }
-.row0 { background-color: #eeeeee; }
-.row1 { background-color: #ffffff; }
-</STYLE>
-<& /elements/table-grid.html &>
- <TR STYLE="background-color: #cccccc">
- <TH CLASS="grid">Package</TH>
- <TH CLASS="grid">Sales</TH>
- <TH CLASS="grid">Percentage</TH>
- <TH CLASS="grid">Commission</TH>
- </TR>
-% 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;
-% my $label = $cust_main->custnum . ': '. $cust_main->name;
-% $bgcolor = 0;
- <TR>
- <TD COLSPAN=4 CLASS="cust_head">
- <A HREF="<%$p%>view/cust_main.cgi?<%$cust_main->custnum%>"><% $label %></A>
- </TD>
- </TR>
-% }
- <TR CLASS="row<% $bgcolor %>">
- <TD CLASS="grid"><% $cust_pkg->pkg_label %></TD>
- <TD CLASS="money"><% sprintf('%.2f', $cust_pkg->sum_charged) %></TD>
- <TD ALIGN="right"><% $cust_pkg->percent %>%</TD>
- <TD CLASS="money"><% sprintf('%.2f',
- $cust_pkg->sum_charged * $cust_pkg->percent / 100) %></TD>
- </TR>
-% $sales += $cust_pkg->sum_charged;
-% $commission += $cust_pkg->sum_charged * $cust_pkg->percent / 100;
-% $row++;
-% $bgcolor = 1-$bgcolor;
-% $custnum = $cust_pkg->custnum;
-% }
- <TR STYLE="background-color: #f5f6be">
- <TD CLASS="grid">
- <% emt('[quant,_1,package] with commission', $row) %>
- </TD>
- <TD CLASS="money"><% sprintf('%.2f', $sales) %></TD>
- <TD></TD>
- <TD CLASS="money"><% sprintf('%.2f', $commission) %></TD>
- </TR>
-</TABLE>
-<& /elements/footer.html &>
+<& elements/commission.html,
+ 'title' => $title,
+ 'name_singular' => 'agent',
+ 'header' => [ 'Agent' ],
+ 'fields' => [ 'agent' ],
+ 'links' => [ '' ],
+ 'align' => 'l',
+ 'query' => \%query,
+ 'count_query' => $count_query,
+ 'disableable' => 1,
+ 'sales_detail_link' => $sales_link,
+ 'credit_detail_link' => $commission_link,
+&>
% }
<%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',
- );
+die "access denied"
+ unless $FS::CurrentUser::CurrentUser->access_right('Financial reports');
- my $col_head_format = $workbook->add_format(
- bold => 1,
- align => 'center',
- bg_color => 'silver'
- );
+my $conf = new FS::Conf;
- 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 %query = ( 'table' => 'agent' );
+my $count_query = "SELECT COUNT(*) FROM agent";
- my ($r, $c) = (0, 0);
- foreach (qw(Package Sales Percentage Commission)) {
- $worksheet->write($r, $c++, $_, $col_head_format);
- }
- $r++;
+my $agentnum = '';
+if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) {
+ $agentnum = $1;
+} else {
+ $cgi->delete('agentnum');
+}
- 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});
+my $title = 'Agent commission';
- $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++;
- }
+my $sales_link = [ 'agent_pkg_class.html?agentnum=', 'agentnum' ];
- $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);
+my $commission_link = [ 'cust_credit.html?commission_agentnum=', 'agentnum' ];
- $workbook->close;
-}
</%init>
projects / freeside.git / blobdiff