1 %# still not a good way to do rows grouped by some field in a search.html
3 <& /elements/header.html, $title &>
5 <STYLE TYPE="text/css">
11 background-color: #ffffff;
13 td.money { text-align: right; }
14 td.money:before { content: '<% $money_char %>'; }
15 .row0 { background-color: #eeeeee; }
16 .row1 { background-color: #ffffff; }
18 <& /elements/table-grid.html &>
19 <TR STYLE="background-color: #cccccc">
20 <TH CLASS="grid">Package</TH>
21 <TH CLASS="grid">Sales</TH>
22 <TH CLASS="grid">Percentage</TH>
23 <TH CLASS="grid">Commission</TH>
25 % my ($custnum, $sales, $commission, $row, $bgcolor) = (0, 0, 0, 0);
26 % foreach my $cust_pkg ( @cust_pkg ) {
27 % if ( $custnum ne $cust_pkg->custnum ) {
28 % # start of a new customer section
29 % my $cust_main = $cust_pkg->cust_main;
30 % my $label = $cust_main->custnum . ': '. $cust_main->name;
33 <TD COLSPAN=4 CLASS="cust_head">
34 <A HREF="<%$p%>view/cust_main.cgi?<%$cust_main->custnum%>"><% $label %></A>
38 <TR CLASS="row<% $bgcolor %>">
39 <TD CLASS="grid"><% $cust_pkg->pkg_label %></TD>
40 <TD CLASS="money"><% sprintf('%.2f', $cust_pkg->sum_charged) %></TD>
41 <TD ALIGN="right"><% $cust_pkg->percent %>%</TD>
42 <TD CLASS="money"><% sprintf('%.2f',
43 $cust_pkg->sum_charged * $cust_pkg->percent / 100) %></TD>
45 % $sales += $cust_pkg->sum_charged;
46 % $commission += $cust_pkg->sum_charged * $cust_pkg->percent / 100;
48 % $bgcolor = 1-$bgcolor;
49 % $custnum = $cust_pkg->custnum;
51 <TR STYLE="background-color: #f5f6be">
53 <% emt('[quant,_1,package] with commission', $row) %>
55 <TD CLASS="money"><% sprintf('%.2f', $sales) %></TD>
57 <TD CLASS="money"><% sprintf('%.2f', $commission) %></TD>
60 <& /elements/footer.html &>
63 unless $FS::CurrentUser::CurrentUser->access_right('Financial reports');
65 my ($begin, $end) = FS::UI::Web::parse_beginning_ending($cgi);
66 $cgi->param('agentnum') =~ /^(\d+)$/ or die "bad agentnum";
68 my $agent = FS::agent->by_key($agentnum);
70 my $title = $agent->agent . ' commissions';
73 '(SELECT SUM(setup + recur) FROM cust_bill_pkg JOIN cust_bill USING (invnum)'.
74 'WHERE cust_bill_pkg.pkgnum = cust_pkg.pkgnum AND '.
75 "cust_bill._date >= $begin AND cust_bill._date < $end)";
79 'agent_pkg_class.commission_percent AS percent',
80 "$sum_charged AS sum_charged",
84 'table' => 'cust_pkg',
85 'select' => join(',', @select),
86 'addl_from' => 'JOIN cust_main USING (custnum) '.
87 'JOIN part_pkg USING (pkgpart) '.
88 'JOIN agent_pkg_class ON ( '.
89 'cust_main.agentnum = agent_pkg_class.agentnum AND '.
90 '( agent_pkg_class.classnum = part_pkg.classnum OR '.
91 '(agent_pkg_class IS NULL AND part_pkg.classnum IS NULL)'.
93 'extra_sql' => "WHERE cust_main.agentnum = $agentnum AND ".
94 'agent_pkg_class.commission_percent > 0 AND '.
96 'order_by' => 'ORDER BY cust_pkg.custnum ASC',
99 my @cust_pkg = qsearch($query);
101 my $money_char = FS::Conf->new->config('money_char') || '$';
104 # 'SELECT COUNT(*) FROM cust_pkg '.$query->{'addl_from'}.$query->{'extra_sql'}.
105 # ' AND EXISTS(SELECT 1 FROM cust_bill_pkg JOIN cust_bill USING (invnum) '.
106 # ' WHERE cust_bill_pkg.pkgnum = cust_pkg.pkgnum AND '.
107 # "cust_bill._date >= $begin AND cust_bill._date < $end".