1 %# still not a good way to do rows grouped by some field in a search.html
3 %# (there is now, but we're not yet sponsored to switch this over to it)
4 % if ( $type eq 'xls' ) {
7 % if ( $type eq 'html-print' ) {
8 <& /elements/header-popup.html, $title &>
10 <& /elements/header.html, $title &>
11 <P ALIGN="right" CLASS="noprint">
12 Download full results<BR>
13 as <A HREF="<% $cgi->self_url %>;_type=xls">Excel spreadsheet</A></P>
16 <STYLE TYPE="text/css">
22 background-color: #ffffff;
24 td.money { text-align: right; }
25 td.money:before { content: '<% $money_char %>'; }
26 .row0 { background-color: #eeeeee; }
27 .row1 { background-color: #ffffff; }
29 <& /elements/table-grid.html &>
31 <TR STYLE="background-color: #cccccc">
32 <TH CLASS="grid">Package</TH>
33 <TH CLASS="grid">Sales</TH>
34 <TH CLASS="grid">Percentage</TH>
35 <TH CLASS="grid">Commission</TH>
38 % my ($custnum, $sales, $commission, $row, $bgcolor) = (0, 0, 0, 0);
39 % foreach my $cust_pkg ( @cust_pkg ) {
40 % if ( $custnum ne $cust_pkg->custnum ) {
41 % # start of a new customer section
42 % my $cust_main = $cust_pkg->cust_main;
45 <TD COLSPAN=4 CLASS="cust_head">
46 <A HREF="<%$p%>view/cust_main.cgi?<%$cust_main->custnum%>"><% $cust_main->display_custnum %>: <% $cust_main->name |h %></A>
50 <TR CLASS="row<% $bgcolor %>">
51 <TD CLASS="grid"><% $cust_pkg->pkg_label %></TD>
52 <TD CLASS="money"><% sprintf('%.2f', $cust_pkg->sum_charged) %></TD>
53 <TD ALIGN="right"><% $cust_pkg->percent %>%</TD>
54 <TD CLASS="money"><% sprintf('%.2f',
55 $cust_pkg->sum_charged * $cust_pkg->percent / 100) %></TD>
57 % $sales += $cust_pkg->sum_charged;
58 % $commission += $cust_pkg->sum_charged * $cust_pkg->percent / 100;
60 % $bgcolor = 1-$bgcolor;
61 % $custnum = $cust_pkg->custnum;
63 <TR STYLE="background-color: #f5f6be">
65 <% emt('[quant,_1,package] with commission', $row) %>
67 <TD CLASS="money"><% sprintf('%.2f', $sales) %></TD>
69 <TD CLASS="money"><% sprintf('%.2f', $commission) %></TD>
72 <& /elements/footer.html &>
76 #pretty bad false laziness w/agent_commission.html, lots of s/agent/sales/ :/
77 # everything above is completely untouched
80 unless $FS::CurrentUser::CurrentUser->access_right('Financial reports');
82 my ($begin, $end) = FS::UI::Web::parse_beginning_ending($cgi);
84 $cgi->param('salesnum') =~ /^(\d+)$/ or die "No sales person selected"; #better error handling of this case (or, better javascript that doesn't let you submit)
86 my $sales = FS::sales->by_key($salesnum);
88 my $title = $sales->salesperson . ' commissions';
91 '(SELECT SUM(setup + recur) FROM cust_bill_pkg JOIN cust_bill USING (invnum)'.
92 'WHERE cust_bill_pkg.pkgnum = cust_pkg.pkgnum AND '.
93 "cust_bill._date >= $begin AND cust_bill._date < $end)";
97 'sales_pkg_class.commission_percent AS percent',
98 "$sum_charged AS sum_charged",
102 'table' => 'cust_pkg',
103 'select' => join(',', @select),
105 JOIN cust_main USING (custnum)
106 JOIN part_pkg USING (pkgpart)
107 JOIN sales_pkg_class ON (
108 COALESCE(cust_pkg.salesnum,cust_main.salesnum) = sales_pkg_class.salesnum
110 ( sales_pkg_class.classnum = part_pkg.classnum
111 OR (sales_pkg_class IS NULL AND part_pkg.classnum IS NULL)
116 WHERE COALESCE(cust_pkg.salesnum,cust_main.salesnum) = $salesnum
117 AND sales_pkg_class.commission_percent > 0
120 'order_by' => 'ORDER BY cust_pkg.custnum ASC',
123 my @cust_pkg = qsearch($query);
125 my $money_char = FS::Conf->new->config('money_char') || '$';
128 my $type = $cgi->param('_type');
129 if ( $type eq 'xls') {
130 # some false laziness with the above...
131 my $format = $FS::CurrentUser::CurrentUser->spreadsheet_format;
132 my $filename = 'sales_commission_pkg' . $format->{extension};
133 http_header('Content-Type' => $format->{mime_type});
134 http_header('Content-Disposition' => qq!attachment;filename="$filename"!);
135 my $XLS = IO::Scalar->new(\$data);
136 my $workbook = $format->{class}->new($XLS);
137 my $worksheet = $workbook->add_worksheet(substr($title, 0, 31));
139 my $cust_head_format = $workbook->add_format(
146 my $col_head_format = $workbook->add_format(
154 my %bg = (bg_color => $_ ? 'white' : 'silver');
156 'text' => $workbook->add_format(%bg),
157 'money' => $workbook->add_format(%bg, num_format => $money_char.'#0.00'),
158 'percent' => $workbook->add_format(%bg, num_format => '0.00%'),
161 my $total_format = $workbook->add_format(
162 bg_color => 'yellow',
163 num_format => $money_char.'#0.00',
167 my ($r, $c) = (0, 0);
168 foreach (qw(Package Sales Percentage Commission)) {
169 $worksheet->write($r, $c++, $_, $col_head_format);
173 my ($custnum, $sales, $commission, $row, $bgcolor) = (0, 0, 0, 0);
174 my $label_length = 0;
175 foreach my $cust_pkg ( @cust_pkg ) {
176 if ( $custnum ne $cust_pkg->custnum ) {
177 # start of a new customer section
178 my $cust_main = $cust_pkg->cust_main;
179 my $label = $cust_main->custnum . ': '. $cust_main->name;
181 $worksheet->set_row($r, 20);
182 $worksheet->merge_range($r, 0, $r, 3, $label, $cust_head_format);
186 my $percent = $cust_pkg->percent / 100;
187 $worksheet->write($r, $c++, $cust_pkg->pkg_label, $format[$bgcolor]{text});
188 $worksheet->write($r, $c++, $cust_pkg->sum_charged, $format[$bgcolor]{money});
189 $worksheet->write($r, $c++, $percent, $format[$bgcolor]{percent});
190 $worksheet->write($r, $c++, ($cust_pkg->sum_charged * $percent),
191 $format[$bgcolor]{money});
193 $label_length = max($label_length, length($cust_pkg->pkg_label));
194 $sales += $cust_pkg->sum_charged;
195 $commission += $cust_pkg->sum_charged * $cust_pkg->percent / 100;
197 $bgcolor = 1-$bgcolor;
198 $custnum = $cust_pkg->custnum;
203 $label_length = max($label_length, 20);
204 $worksheet->set_column($c, $c, $label_length);
205 $worksheet->write($r, $c++, mt('[quant,_1,package] with commission', $row),
207 $worksheet->set_column($c, $c + 2, 11);
208 $worksheet->write($r, $c++, $sales, $total_format);
209 $worksheet->write($r, $c++, '', $total_format);
210 $worksheet->write($r, $c++, $commission, $total_format);