--- /dev/null
+package FS::Commission_Mixin;
+
+use strict;
+use FS::Record 'qsearch';
+
+=head1 NAME
+
+FS::Commission_Mixin - Common interface for entities that can receive
+sales commissions.
+
+=head1 INTERFACE
+
+=over 4
+
+=item commission_where
+
+Returns an SQL WHERE fragment to search for commission credits belonging
+to this entity.
+
+=item sales_where
+
+Returns an SQL WHERE fragment to search for sales records
+(L<FS::cust_bill_pkg>) that would be assigned to this entity for commission.
+
+=cut
+
+sub commission_where { ... }
+
+=head1 METHODS
+
+=over 4
+
+=item cust_credit_search START, END, OPTIONS
+
+Returns a qsearch hashref for the commission credits given to this entity.
+START and END are a date range.
+
+OPTIONS may optionally contain "commission_classnum", a package classnum to
+limit the commission packages.
+
+=cut
+
+sub cust_credit_search {
+ my( $self, $sdate, $edate, %search ) = @_;
+
+ my @where = ( $self->commission_where );
+ push @where, "cust_credit._date >= $sdate" if $sdate;
+ push @where, "cust_credit._date < $edate" if $edate;
+
+ my $classnum_sql = '';
+ my $addl_from = '';
+ if ( exists($search{'commission_classnum'}) ) {
+ my $classnum = delete($search{'commission_classnum'});
+ push @where, 'part_pkg.classnum '. ( $classnum ? " = $classnum"
+ : " IS NULL " );
+
+ $addl_from =
+ ' LEFT JOIN cust_pkg ON ( commission_pkgnum = cust_pkg.pkgnum ) '.
+ ' LEFT JOIN part_pkg USING ( pkgpart ) ';
+ }
+
+ my $extra_sql = 'WHERE ' . join(' AND ', map {"( $_ )"} @where);
+
+ { 'table' => 'cust_credit',
+ 'addl_from' => $addl_from,
+ 'extra_sql' => $extra_sql,
+ };
+}
+
+=item cust_credit START, END, OPTIONS
+
+Takes the same options as cust_credit_search, and performs the search.
+
+=cut
+
+sub cust_credit {
+ my $self = shift;
+ qsearch( $self->cust_credit_search(@_) );
+}
+
+=item cust_bill_pkg_search START, END, OPTIONS
+
+Returns a qsearch hashref for the sales for which this entity could receive
+commission. START and END are a date range; OPTIONS may contain:
+- I<classnum>: limit to this package class (or null, if it's empty)
+- I<paid>: limit to sales that have no unpaid balance (as of now)
+
+=cut
+
+sub cust_bill_pkg_search {
+ my( $self, $sdate, $edate, %search ) = @_;
+
+ my @where = $self->sales_where(%search);
+ push @where, "cust_bill._date >= $sdate" if $sdate;
+ push @where, "cust_bill._date < $edate" if $edate;
+
+ my $classnum_sql = '';
+ if ( exists( $search{'classnum'} ) ) {
+ my $classnum = $search{'classnum'} || '';
+ die "bad classnum" unless $classnum =~ /^(\d*)$/;
+
+ push @where,
+ "part_pkg.classnum ". ( $classnum ? " = $classnum " : ' IS NULL ' );
+ }
+
+ if ( $search{'paid'} ) {
+ push @where, FS::cust_bill_pkg->owed_sql . ' <= 0.005';
+ }
+
+ my $extra_sql = "WHERE ".join(' AND ', map {"( $_ )"} @where);
+
+ { 'table' => 'cust_bill_pkg',
+ 'select' => 'cust_bill_pkg.*',
+ 'addl_from' => ' LEFT JOIN cust_bill USING ( invnum ) '.
+ ' LEFT JOIN cust_pkg USING ( pkgnum ) '.
+ ' LEFT JOIN part_pkg USING ( pkgpart ) '.
+ ' LEFT JOIN cust_main ON ( cust_pkg.custnum = cust_main.custnum )',
+ 'extra_sql' => $extra_sql,
+ };
+}
+
+=item cust_bill_pkg START, END, OPTIONS
+
+Same as L</cust_bill_pkg_search> but then performs the search.
+
+=back
+
+=head1 SEE ALSO
+
+L<FS::cust_credit>
+
+=cut
+
+1;
$sth->fetchrow_arrayref->[0];
}
+sub commission_where {
+ my $self = shift;
+ 'cust_credit.commission_agentnum = ' . $self->agentnum;
+}
+
+sub sales_where {
+ my $self = shift;
+ 'cust_main.agentnum = ' . $self->agentnum;
+}
+
=back
=head1 BUGS
package FS::agent_pkg_class;
-use base qw( FS::Record );
+use base qw( FS::Commission_Mixin FS::Record );
use strict;
#use FS::Record qw( qsearch qsearchs );
$self->SUPER::check;
}
+sub cust_credit_search {
+ my $self = shift;
+ my $agent = $self->agent;
+ $agent->cust_credit_search(@_, commission_classnum => $self->classnum);
+}
+
+sub cust_bill_pkg_search {
+ my $self = shift;
+ my $agent = $self->agent;
+ $agent->cust_bill_pkg_search(@_, classnum => $self->classnum);
+}
+
+sub classname {
+ my $self = shift;
+ my $pkg_class = $self->pkg_class;
+ $pkg_class ? $pkg_class->classname : '(no package class)';
+}
+
=back
=head1 BUGS
package FS::sales;
-use base qw( FS::Agent_Mixin FS::Record );
+use base qw( FS::Commission_Mixin FS::Agent_Mixin FS::Record );
use strict;
use FS::Record qw( qsearch qsearchs );
=cut
-sub cust_bill_pkg_search {
- my( $self, $sdate, $edate, %search ) = @_;
-
- my $cmp_salesnum = delete $search{'cust_main_sales'}
- ? ' COALESCE( cust_pkg.salesnum, cust_main.salesnum )'
- : ' cust_pkg.salesnum ';
-
+sub sales_where {
+ my $self = shift;
my $salesnum = $self->salesnum;
die "bad salesnum" unless $salesnum =~ /^(\d+)$/;
+ my %opt = @_;
+
+ my $cmp_salesnum = 'cust_pkg.salesnum';
+ if ($opt{cust_main_sales}) {
+ $cmp_salesnum = 'COALESCE(cust_pkg.salesnum, cust_main.salesnum)';
+ }
+
my @where = ( "$cmp_salesnum = $salesnum",
"sales_pkg_class.salesnum = $salesnum"
);
- push @where, "cust_bill._date >= $sdate" if $sdate;
- push @where, "cust_bill._date < $edate" if $edate;
-
- my $classnum_sql = '';
- if ( exists( $search{'classnum'} ) ) {
- my $classnum = $search{'classnum'} || '';
- die "bad classnum" unless $classnum =~ /^(\d*)$/;
-
- push @where,
- "part_pkg.classnum ". ( $classnum ? " = $classnum " : ' IS NULL ' );
- }
# sales_pkg_class number-of-months limit, grr
# (we should be able to just check for the cust_event record from the
"THEN $charge_date < $setup_date + $interval ".
"ELSE TRUE END";
- if ( $search{'paid'} ) {
- push @where, FS::cust_bill_pkg->owed_sql . ' <= 0.005';
- }
-
- my $extra_sql = "WHERE ".join(' AND ', map {"( $_ )"} @where);
-
- { 'table' => 'cust_bill_pkg',
- 'select' => 'cust_bill_pkg.*',
- 'addl_from' => ' LEFT JOIN cust_bill USING ( invnum ) '.
- ' LEFT JOIN cust_pkg USING ( pkgnum ) '.
- ' LEFT JOIN part_pkg USING ( pkgpart ) '.
- ' LEFT JOIN cust_main ON ( cust_pkg.custnum = cust_main.custnum )'.
- ' JOIN sales_pkg_class ON ( '.
- ' COALESCE( sales_pkg_class.classnum, 0) = COALESCE( part_pkg.classnum, 0) )',
- 'extra_sql' => $extra_sql,
- };
+ @where;
}
-sub cust_bill_pkg {
+sub commission_where {
my $self = shift;
- qsearch( $self->cust_bill_pkg_search(@_) )
+ 'cust_credit.commission_salesnum = ' . $self->salesnum;
}
-sub cust_credit_search {
- my( $self, $sdate, $edate, %search ) = @_;
-
- $search{'hashref'}->{'commission_salesnum'} = $self->salesnum;
-
- my @where = ();
- push @where, "cust_credit._date >= $sdate" if $sdate;
- push @where, "cust_credit._date < $edate" if $edate;
-
- my $classnum_sql = '';
- if ( exists($search{'commission_classnum'}) ) {
- my $classnum = delete($search{'commission_classnum'});
- push @where, 'part_pkg.classnum '. ( $classnum ? " = $classnum"
- : " IS NULL " );
-
- $search{'addl_from'} .=
- ' LEFT JOIN cust_pkg ON ( commission_pkgnum = cust_pkg.pkgnum ) '.
- ' LEFT JOIN part_pkg USING ( pkgpart ) ';
- }
-
- my $extra_sql = "AND ".join(' AND ', map {"( $_ )"} @where);
-
- { 'table' => 'cust_credit',
- 'extra_sql' => $extra_sql,
- %search,
- };
-}
-
-sub cust_credit {
+# slightly modify it
+sub cust_bill_pkg_search {
my $self = shift;
- qsearch( $self->cust_credit_search(@_) )
+ my $search = $self->SUPER::cust_bill_pkg_search(@_);
+ $search->{addl_from} .= '
+ JOIN sales_pkg_class ON( COALESCE(sales_pkg_class.classnum, 0) = COALESCE(part_pkg.classnum, 0) )';
+
+ return $search;
}
=back
package FS::sales_pkg_class;
-use base qw( FS::Record );
+use base qw( FS::Commission_Mixin FS::Record );
use strict;
$pkg_class ? $pkg_class->classname : '(no package class)';
}
+sub cust_credit_search {
+ my $self = shift;
+ my $sales = $self->sales;
+ $sales->cust_credit_search(@_, commission_classnum => $self->classnum);
+}
+
+sub cust_bill_pkg_search {
+ my $self = shift;
+ my $sales = $self->sales;
+ $sales->cust_bill_pkg_search(@_, classnum => $self->classnum);
+}
+
=back
=head1 BUGS
tie my %report_commissions, 'Tie::IxHash',
'Agent' => [ $fsurl.'search/report_agent_commission.html' ],
+ 'Agent per package' => [ $fsurl.'search/report_agent_commission_pkg.html' ],
'Sales Person' => [ $fsurl.'search/report_sales_commission.html' ],
'Sales Person per package' => [ $fsurl.'search/report_sales_commission_pkg.html' ],
'Employee' => [ $fsurl.'search/report_employee_commission.html', '' ]
-%# still not a good way to do rows grouped by some field in a search.html
-%# report
-% if ( $type eq 'xls' ) {
-<% $data %>\
+% if ( $agentnum ) {
+<% $cgi->redirect($sales_link->[0] . $agentnum) %>
% } 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;
-% $bgcolor = 0;
- <TR>
- <TD COLSPAN=4 CLASS="cust_head">
- <A HREF="<%$p%>view/cust_main.cgi?<%$cust_main->custnum%>"><% $cust_main->display_custnum %>: <% $cust_main->name |h %></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>
--- /dev/null
+%# 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 {
+ 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;
+% $bgcolor = 0;
+ <TR>
+ <TD COLSPAN=4 CLASS="cust_head">
+ <A HREF="<%$p%>view/cust_main.cgi?<%$cust_main->custnum%>"><% $cust_main->display_custnum %>: <% $cust_main->name |h %></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 &>
+% }
+<%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;
+}
+</%init>
--- /dev/null
+<& elements/commission.html,
+ 'title' => $title,
+ 'name_singular' => 'package class',
+ 'header' => [ 'Package class' ],
+ 'fields' => [ 'classname' ],
+ 'links' => [ '' ],
+ 'align' => 'l',
+ 'query' => \%query,
+ 'count_query' => $count_query,
+ 'sales_detail_link' => $sales_link,
+ 'credit_detail_link' => $commission_link,
+&>
+<%init>
+
+die "access denied"
+ unless $FS::CurrentUser::CurrentUser->access_right('Financial reports');
+
+my $conf = new FS::Conf;
+
+$cgi->param('agentnum') =~ /^(\d+)$/ or die 'illegal agentnum';
+my $agentnum = $1;
+my $agent = FS::agent->by_key($agentnum);
+
+my $title = $agent->agent . ' commission';
+
+my %query = ( 'table' => 'agent_pkg_class',
+ 'addl_from' => ' LEFT JOIN pkg_class USING (classnum)',
+ 'hashref' => { 'agentnum' => $agentnum },
+ );
+my $count_query = "SELECT COUNT(*) FROM agent_pkg_class WHERE agentnum = $agentnum";
+
+# cust_bill_pkg.cgi expects "classnum=0" for null classnum
+my $sales_link = [ 'cust_bill_pkg.cgi?nottax=1;'.
+ "agentnum=$agentnum;" .
+ 'classnum=',
+ sub { shift->classnum || 0 },
+ ];
+
+my $commission_link = [ 'cust_credit.html?'.
+ "commission_agentnum=$agentnum;" .
+ 'classnum=',
+ 'classnum'
+ ];
+
+</%init>
push @search, "commission_salesnum = $1";
}
+# commission agentnum
+if ( $cgi->param('commission_agentnum') =~ /^(\d+)$/ ) {
+ push @search, "commission_agentnum = $1";
+}
+
# commission_classnum
if ( grep { $_ eq 'commission_classnum' } $cgi->param ) {
$cgi->param('commission_classnum') =~ /^(\d*)$/ or die 'guru meditation #13';
--- /dev/null
+<& search.html, %opt &>
+<%doc>
+<& elements/commission.html,
+ name_singular => 'sales person', # or 'agent', 'employee', etc.
+ header => [ 'Sales person' ], # 'One-Time Sales', 'Recurring Sales',
+ # 'Commission' will be appended
+ fields => [ 'salesperson' ], # ditto
+ links => [ [ '/view/sales.html?', 'salesnum' ] ], # usual conventions
+ sales_detail_link => [ 'sales_commission_pkg.html?', 'salesnum' ],
+ credit_detail_link => [ 'cust_credit.html?commission_salesnum=', 'salesnum' ],
+ align => 'l',
+ query => { table => 'sales', # must be a Commission_Mixin
+ #other params as appropriate
+ },
+ count_query => 'SELECT COUNT(*) FROM sales ...',
+
+ # all other elements/search.html stuff will be passed through
+&>
+
+The hash passed as 'query' will be passed through to the cust_bill_pkg_search
+and cust_credit_search methods, and so can contain type-specific options.
+</%doc>
+<%init>
+die "access denied"
+ unless $FS::CurrentUser::CurrentUser->access_right('Financial reports');
+
+my %opt = @_;
+my $conf = new FS::Conf;
+
+my $money_char = $conf->config('money_char') || '$';
+
+my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi, '');
+
+my $date_format = $conf->config('date_format') || '%m/%d/%Y';
+
+my $query = $opt{'query'};
+
+my $paid = $cgi->param('paid') ? 1 : 0;
+if ($beginning) {
+ $opt{'title'} .= ': ' . time2str($date_format, $beginning) . ' to ' .
+ time2str($date_format, $ending);
+}
+if ($paid) {
+ $opt{'title'} .= ' - paid sales only';
+}
+
+my $sales_sub_maker = sub {
+ my $field = shift;
+ sub {
+ my $object = shift;
+ my $search = $object->cust_bill_pkg_search(
+ $beginning,
+ $ending,
+ 'paid' => $paid,
+ %$query,
+ );
+ $search->{select} = "SUM(cust_bill_pkg.$field) AS total_amount";
+ my $result = qsearchs($search);
+ my $total = $result->get('total_amount') || 0;
+
+ return $money_char. sprintf('%.2f', $total);
+ };
+};
+
+my $commission_sub = sub {
+ my $object = shift;
+
+ my $search = $object->cust_credit_search(
+ $beginning,
+ $ending,
+ %$query
+ );
+ $search->{select} = 'SUM(cust_credit.amount) AS total_amount';
+ my $result = qsearchs($search);
+ my $total = $result->get('total_amount') || 0;
+
+ return $money_char. sprintf('%.2f', $total);
+};
+
+my $sales_link = $opt{'sales_detail_link'};
+if ($sales_link) {
+ my ($pre, $post) = split('\?', $sales_link->[0], 2);
+ $sales_link->[0] = $pre . "?begin=$beginning;end=$ending;" . $post;
+}
+
+my $commission_link = $opt{'credit_detail_link'};
+if ($commission_link) {
+ my ($pre, $post) = split('\?', $commission_link->[0], 2);
+ $commission_link->[0] = $pre . "?begin=$beginning;end=$ending;" . $post;
+}
+
+# merge our new stuff into %opt
+my $header = $opt{'header'};
+push @$header,
+ 'One-time sales',
+ 'Recurring sales',
+ 'Commission'
+;
+
+my $fields = $opt{'fields'};
+push @$fields,
+ $sales_sub_maker->('setup'),
+ $sales_sub_maker->('recur'),
+ $commission_sub
+;
+
+push @{$opt{'links'}}, $sales_link, $sales_link, $commission_link;
+$opt{'align'} .= 'rrr';
+
+</%init>
<TABLE BGCOLOR="#cccccc" CELLSPACING=0>
-<% include( '/elements/tr-select-agent.html', disable_empty => 1 ) %>
+<& /elements/tr-select-agent.html &>
-<% include( '/elements/tr-input-beginning_ending.html', ) %>
+<& /elements/tr-checkbox.html,
+ 'label' => 'Show paid sales only',
+ 'field' => 'paid',
+ 'value' => 'Y',
+&>
+
+<& /elements/tr-input-beginning_ending.html &>
</TABLE>
<% include('/elements/footer.html') %>
<%init>
-die "access denied"
- unless $FS::CurrentUser::CurrentUser->access_right('Financial reports');
+my $curuser = $FS::CurrentUser::CurrentUser;
+
+die "access denied" unless $curuser->access_right('Financial reports');
</%init>
--- /dev/null
+<% include('/elements/header.html', 'Agent commission report' ) %>
+
+<FORM ACTION="agent_commission_pkg.html">
+
+<TABLE BGCOLOR="#cccccc" CELLSPACING=0>
+
+<% include( '/elements/tr-select-agent.html', disable_empty => 1 ) %>
+
+<% include( '/elements/tr-input-beginning_ending.html', ) %>
+
+</TABLE>
+
+<BR>
+<INPUT TYPE="submit" VALUE="Get Report">
+
+<% include('/elements/footer.html') %>
+<%init>
+
+die "access denied"
+ unless $FS::CurrentUser::CurrentUser->access_right('Financial reports');
+
+</%init>
% if ( $salesnum ) {
<% $cgi->redirect($sales_link->[0] . $salesnum) %>
% } else {
-<& elements/search.html,
- 'title' => $title,
- 'name_singular' => 'sales person',
- 'header' => [ 'Sales person', 'One-Time Sales', 'Recurring Sales', 'Commission', ],
- 'fields' => [ 'salesperson',
- $sales_sub_maker->('setup'),
- $sales_sub_maker->('recur'),
- $commission_sub,
- ],
- 'links' => [ '', $sales_link, $sales_link, $commission_link ],
- 'align' => 'lrrr',
- 'query' => \%query,
- 'count_query' => $count_query,
- 'disableable' => 1,
+<& elements/commission.html,
+ 'title' => $title,
+ 'name_singular' => 'sales person',
+ 'header' => [ 'Sales person' ],
+ 'fields' => [ 'salesperson' ],
+ 'links' => [ '' ],
+ 'align' => 'l',
+ 'query' => \%query,
+ 'count_query' => $count_query,
+ 'disableable' => 1,
+ 'sales_detail_link' => $sales_link,
+ 'credit_detail_link' => $commission_link,
&>
% }
<%init>
my $conf = new FS::Conf;
-my $money_char = $conf->config('money_char') || '$';
-
-my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi, '');
-
-my $date_format = $conf->config('date_format') || '%m/%d/%Y';
-
my %query = ( 'table' => 'sales' );
my $count_query = "SELECT COUNT(*) FROM sales";
}
my $title = 'Sales person commission';
-$title .= ': '. time2str($date_format, $beginning). ' to '.
- time2str($date_format, $ending)
- if $beginning;
-
-my $paid = $cgi->param('paid') ? 1 : 0;
-$title .= ' - paid sales only' if $paid;
my $cust_main_sales = $cgi->param('cust_main_sales') eq 'Y' ? 'Y' : '';
+$query{'cust_main_sales'} = $cust_main_sales;
my $sales_link = [ 'sales_pkg_class.html?'.
- # pass all of our parameters along
- $cgi->query_string. ';salesnum=',
+ "cust_main_sales=$cust_main_sales;salesnum=",
'salesnum'
];
-my $sales_sub_maker = sub {
- my $field = shift;
- sub {
- my $sales = shift;
-
- my $search = $sales->cust_bill_pkg_search(
- $beginning,
- $ending,
- 'cust_main_sales' => $cust_main_sales,
- 'paid' => $paid,
- );
- die 'cust_bill_pkg_search hashref not yet handled' if $search->{hashref};
-
- my $total = FS::Record->scalar_sql(
- "SELECT SUM(cust_bill_pkg.$field) FROM cust_bill_pkg ". #$search->{table}
- $search->{addl_from}. ' '. $search->{extra_sql}
- );
-
- return $money_char. sprintf('%.2f', $total);
- };
-};
-
-my $commission_sub = sub {
- my $sales = shift;
-
- #efficiency improvement: ask the db for a sum instead of all the records
- #my $total_credit = 0;
- #my @cust_credit = $sales->cust_credit( $beginning, $ending );
- #$total_credit += $_->amount foreach @cust_credit;
-
- my $search = $sales->cust_credit_search( $beginning, $ending );
-
- my $sql =
- "SELECT SUM(cust_credit.amount) FROM cust_credit ". #$search->{table}
- $search->{addl_from}. ' '.
- ' WHERE commission_salesnum = ? '. #$search->{hashref}
- $search->{extra_sql};
-
- my $total = FS::Record->scalar_sql($sql, $sales->salesnum);
-
- $money_char. sprintf('%.2f', $total);
-};
-
my $commission_link = [ 'cust_credit.html?'.
- "begin=$beginning;".
- "end=$ending;".
- "cust_main_sales=$cust_main_sales;".
- 'commission_salesnum=',
+ "cust_main_sales=$cust_main_sales;commission_salesnum=",
'salesnum'
];
-<& elements/search.html,
- 'title' => $title,
- 'name_singular' => 'package class',
- 'header' => [ 'Package class',
- 'One-Time Sales',
- 'Recurring Sales',
- 'Commission', ],
- 'fields' => [ 'classname',
- $sales_sub_maker->('setup'),
- $sales_sub_maker->('recur'),
- $commission_sub, ],
- 'links' => [ '', $sales_link, $sales_link, $commission_link ],
- 'align' => 'lrrr',
- 'query' => { 'table' => 'sales_pkg_class',
- 'hashref' => { 'salesnum' => $salesnum },
- },
- 'count_query' => "SELECT COUNT(*) FROM sales_pkg_class WHERE salesnum = $salesnum", #show some totals?
+<& elements/commission.html,
+ 'title' => $title,
+ 'name_singular' => 'package class',
+ 'header' => [ 'Package class' ],
+ 'fields' => [ 'classname' ],
+ 'links' => [ '' ],
+ 'align' => 'l',
+ 'query' => \%query,
+ 'count_query' => $count_query,
+ 'sales_detail_link' => $sales_link,
+ 'credit_detail_link' => $commission_link,
&>
<%init>
my $conf = new FS::Conf;
-my $money_char = $conf->config('money_char') || '$';
-
$cgi->param('salesnum') =~ /^(\d+)$/ or die 'illegal salesnum';
my $salesnum = $1;
-my $sales = qsearchs('sales', { 'salesnum'=>$salesnum } )
- or die 'unknown salesnum';
-
-my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi, '');
+my $sales = FS::sales->by_key($salesnum);
-my $date_format = $conf->config('date_format') || '%m/%d/%Y';
+my $title = $sales->salesperson . ' commission';
-my $title = $sales->salesperson. ' commission';
-$title .= ': '. time2str($date_format, $beginning). ' to '.
- time2str($date_format, $ending)
- if $beginning;
+my %query = ( 'table' => 'sales_pkg_class',
+ 'addl_from' => ' LEFT JOIN pkg_class USING (classnum)',
+ 'hashref' => { 'salesnum' => $salesnum },
+ );
+my $count_query = "SELECT COUNT(*) FROM sales_pkg_class WHERE salesnum = $salesnum";
my $cust_main_sales = $cgi->param('cust_main_sales') eq 'Y' ? 'Y' : '';
-my $paid = $cgi->param('paid') ? 1 : 0;
-
-$title .= " - paid sales only" if $paid;
-
-my $sales_link = [ 'cust_bill_pkg.cgi?'.
- "begin=$beginning;".
- "end=$ending;".
- "cust_main_sales=$cust_main_sales;".
- 'salesnum='. $sales->salesnum. ';'.
- 'nottax=1;'.
- 'classnum=',
- 'classnum'
- ];
-
-my $sales_sub_maker = sub {
- my $field = shift;
- sub {
- my $sales_pkg_class = shift;
- # could be even more efficient but this is pretty good
- my $search = $sales->cust_bill_pkg_search(
- $beginning,
- $ending,
- 'cust_main_sales' => $cust_main_sales,
- 'classnum' => $sales_pkg_class->classnum,
- 'paid' => $paid,
- );
- $search->{'select'} = "SUM(cust_bill_pkg.$field) AS total";
- my $result = qsearchs($search);
- $money_char. sprintf('%.2f', $result ? $result->get('total') : 0);
- };
-};
-
-my $commission_sub = sub {
- my $sales_pkg_class = shift;
-
- #efficiency improvement: ask the db for a sum instead of all the records
- my $total_credit = 0;
- my @cust_credit = $sales->cust_credit(
- $beginning,
- $ending,
- 'commission_classnum' => $sales_pkg_class->classnum,
- );
- $total_credit += $_->amount foreach @cust_credit;
-
- $money_char. sprintf('%.2f', $total_credit);
-};
+$query{'cust_main_sales'} = $cust_main_sales;
-my $sales_link = [ 'cust_bill_pkg.cgi?'.
- "begin=$beginning;".
- "end=$ending;".
- "cust_main_sales=$cust_main_sales;".
- "salesnum=$salesnum;".
- "classnum=",
- 'classnum'
+my $sales_link = [ 'cust_bill_pkg.cgi?nottax=1;'.
+ "cust_main_sales=$cust_main_sales;salesnum=$salesnum;" .
+ 'classnum=',
+ sub { shift->classnum || 0 },
];
my $commission_link = [ 'cust_credit.html?'.
- "begin=$beginning;".
- "end=$ending;".
- "cust_main_sales=$cust_main_sales;".
- 'commission_salesnum='. $sales->salesnum. ';'.
- 'commission_classnum=',
+ "cust_main_sales=$cust_main_sales;" .
+ "commission_salesnum=$salesnum;" .
+ 'classnum=',
'classnum'
];
projects / freeside.git / commitdiff