diff options
| -rw-r--r-- | conf/invoice_html | 8 | ||||
| -rw-r--r-- | conf/invoice_latex | 7 | ||||
| -rw-r--r-- | httemplate/search/agent_commission.html | 100 |
3 files changed, 102 insertions, 13 deletions
diff --git a/conf/invoice_html b/conf/invoice_html index 567385b06..cd348274f 100644 --- a/conf/invoice_html +++ b/conf/invoice_html @@ -132,8 +132,8 @@ $OUT .= '<th align="center">' . emt('Ref') . '</th>'. '<th align="left">' . emt('Description') . '</th>'. ( $unitprices - ? '<th align="left">' . emt('Unit Price') . '</th>'. - '<th align="left">' . emt('Quantity') . '</th>' + ? '<th align="right">' . emt('Unit Price') . '</th>'. + '<th align="right">' . emt('Quantity') . '</th>' : '' ). '<th align="right">' . emt('Amount') . '</th>'; } @@ -158,8 +158,8 @@ ( $line->{'ref'} ne $lastref ? $line->{'ref'} : '' ). '</td>'. '<td align="left">'. $line->{'description'}. '</td>'. ( $unitprices - ? '<td align="left">'. $line->{'unit_amount'}. '</td>'. - '<td align="left">'. $line->{'quantity'}. '</td>' + ? '<td align="right">'. $line->{'unit_amount'}. '</td>'. + '<td align="right">'. $line->{'quantity'}. '</td>' : '' ). diff --git a/conf/invoice_latex b/conf/invoice_latex index d56a7fbdc..533e8340d 100644 --- a/conf/invoice_latex +++ b/conf/invoice_latex @@ -164,8 +164,9 @@ \newcommand{\FSdescriptionlength} { [@-- $unitprices ? '8.2cm' : '12.8cm' --@] }
\newcommand{\FSdescriptioncolumncount} { [@-- $unitprices ? '4' : '6' --@] }
\newcommand{\FSunitcolumns}{ [@--
- $unitprices
- ? '\makebox[2.5cm][l]{\textbf{~~'.emt('Unit Price').'}}&\makebox[1.4cm]{\textbf{~'.emt('Quantity').'}}&'
+ $unitprices
+ ? '\makebox[2.5cm][r]{\textbf{~~' . emt('Unit Price') . '}} &' .
+ '\makebox[1.4cm]{\textbf{~' . emt('Quantity') . '}} & '
: '' --@] }
\newcommand{\FShead}{
@@ -182,7 +183,7 @@ \newcommand{\FSdesc}[5]{
\multicolumn{1}{c}{\rule{0pt}{2.5ex}\textbf{#1}} &
\multicolumn{[@-- $unitprices ? '4' : '6' --@]}{l}{\textbf{#2}} &
-[@-- $unitprices ? ' \multicolumn{1}{l}{\textbf{#3}} &'."\n".
+[@-- $unitprices ? ' \multicolumn{1}{r}{\textbf{\dollar #3}} &'."\n".
' \multicolumn{1}{r}{\textbf{#4}} &'."\n"
: ''
--@]
diff --git a/httemplate/search/agent_commission.html b/httemplate/search/agent_commission.html index b8fbe200f..b94ae9f6e 100644 --- a/httemplate/search/agent_commission.html +++ b/httemplate/search/agent_commission.html @@ -1,6 +1,12 @@ %# 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 &> +<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 { @@ -58,6 +64,7 @@ td.money:before { content: '<% $money_char %>'; } </TR> </TABLE> <& /elements/footer.html &> +% } <%init> die "access denied" unless $FS::CurrentUser::CurrentUser->access_right('Financial reports'); @@ -100,10 +107,91 @@ my @cust_pkg = qsearch($query); my $money_char = FS::Conf->new->config('money_char') || '$'; -#my $count_query = -# 'SELECT COUNT(*) FROM cust_pkg '.$query->{'addl_from'}.$query->{'extra_sql'}. -# ' AND EXISTS(SELECT 1 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 $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; +} </%init> |