From: Mark Wells Date: Fri, 13 Mar 2015 22:24:09 +0000 (-0700) Subject: restructure agent commission reporting, #23348 X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=commitdiff_plain;h=ed741d82e6ef8b2c66985f6b54d5cb26e0a70acb restructure agent commission reporting, #23348 --- diff --git a/FS/FS/Commission_Mixin.pm b/FS/FS/Commission_Mixin.pm new file mode 100644 index 000000000..c65baa0f6 --- /dev/null +++ b/FS/FS/Commission_Mixin.pm @@ -0,0 +1,134 @@ +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) 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: limit to this package class (or null, if it's empty) +- I: 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 but then performs the search. + +=back + +=head1 SEE ALSO + +L + +=cut + +1; diff --git a/FS/FS/agent.pm b/FS/FS/agent.pm index 2c06a0576..a3489f0bc 100644 --- a/FS/FS/agent.pm +++ b/FS/FS/agent.pm @@ -731,6 +731,16 @@ sub num_sales { $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 diff --git a/FS/FS/agent_pkg_class.pm b/FS/FS/agent_pkg_class.pm index 5c5c3f7ed..2a2f5db9f 100644 --- a/FS/FS/agent_pkg_class.pm +++ b/FS/FS/agent_pkg_class.pm @@ -1,5 +1,5 @@ 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 ); @@ -105,6 +105,24 @@ sub check { $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 diff --git a/FS/FS/sales.pm b/FS/FS/sales.pm index d262051a8..31409288e 100644 --- a/FS/FS/sales.pm +++ b/FS/FS/sales.pm @@ -1,5 +1,5 @@ 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 ); @@ -153,29 +153,20 @@ package sales person will be included if this is their customer sales person. =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 @@ -189,60 +180,22 @@ sub cust_bill_pkg_search { "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 diff --git a/FS/FS/sales_pkg_class.pm b/FS/FS/sales_pkg_class.pm index b14003527..0f85ac46e 100644 --- a/FS/FS/sales_pkg_class.pm +++ b/FS/FS/sales_pkg_class.pm @@ -1,5 +1,5 @@ package FS::sales_pkg_class; -use base qw( FS::Record ); +use base qw( FS::Commission_Mixin FS::Record ); use strict; @@ -113,6 +113,18 @@ sub classname { $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 diff --git a/httemplate/elements/menu.html b/httemplate/elements/menu.html index 669f59b54..0aefcd77a 100644 --- a/httemplate/elements/menu.html +++ b/httemplate/elements/menu.html @@ -356,6 +356,7 @@ tie my %report_sales, 'Tie::IxHash', 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', '' ] diff --git a/httemplate/search/agent_commission.html b/httemplate/search/agent_commission.html index 2818d2ee5..386452aeb 100644 --- a/httemplate/search/agent_commission.html +++ b/httemplate/search/agent_commission.html @@ -1,196 +1,41 @@ -%# 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 &> -

-Download full results
-as Excel spreadsheet

-
- -<& /elements/table-grid.html &> - - Package - Sales - Percentage - Commission - -% 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; - - - <% $cust_main->display_custnum %>: <% $cust_main->name |h %> - - -% } - - <% $cust_pkg->pkg_label %> - <% sprintf('%.2f', $cust_pkg->sum_charged) %> - <% $cust_pkg->percent %>% - <% sprintf('%.2f', - $cust_pkg->sum_charged * $cust_pkg->percent / 100) %> - -% $sales += $cust_pkg->sum_charged; -% $commission += $cust_pkg->sum_charged * $cust_pkg->percent / 100; -% $row++; -% $bgcolor = 1-$bgcolor; -% $custnum = $cust_pkg->custnum; -% } - - - <% emt('[quant,_1,package] with commission', $row) %> - - <% sprintf('%.2f', $sales) %> - - <% sprintf('%.2f', $commission) %> - - -<& /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; -} diff --git a/httemplate/search/agent_commission_pkg.html b/httemplate/search/agent_commission_pkg.html new file mode 100644 index 000000000..2818d2ee5 --- /dev/null +++ b/httemplate/search/agent_commission_pkg.html @@ -0,0 +1,196 @@ +%# 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 &> +

+Download full results
+as Excel spreadsheet

+
+ +<& /elements/table-grid.html &> + + Package + Sales + Percentage + Commission + +% 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; + + + <% $cust_main->display_custnum %>: <% $cust_main->name |h %> + + +% } + + <% $cust_pkg->pkg_label %> + <% sprintf('%.2f', $cust_pkg->sum_charged) %> + <% $cust_pkg->percent %>% + <% sprintf('%.2f', + $cust_pkg->sum_charged * $cust_pkg->percent / 100) %> + +% $sales += $cust_pkg->sum_charged; +% $commission += $cust_pkg->sum_charged * $cust_pkg->percent / 100; +% $row++; +% $bgcolor = 1-$bgcolor; +% $custnum = $cust_pkg->custnum; +% } + + + <% emt('[quant,_1,package] with commission', $row) %> + + <% sprintf('%.2f', $sales) %> + + <% sprintf('%.2f', $commission) %> + + +<& /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; +} + diff --git a/httemplate/search/agent_pkg_class.html b/httemplate/search/agent_pkg_class.html new file mode 100644 index 000000000..5b8c7bfe5 --- /dev/null +++ b/httemplate/search/agent_pkg_class.html @@ -0,0 +1,45 @@ +<& 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' + ]; + + diff --git a/httemplate/search/cust_credit.html b/httemplate/search/cust_credit.html index 18908fd1f..7738494db 100755 --- a/httemplate/search/cust_credit.html +++ b/httemplate/search/cust_credit.html @@ -106,6 +106,11 @@ if ( $cgi->param('commission_salesnum') =~ /^(\d+)$/ ) { 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'; diff --git a/httemplate/search/elements/commission.html b/httemplate/search/elements/commission.html new file mode 100644 index 000000000..6f610639f --- /dev/null +++ b/httemplate/search/elements/commission.html @@ -0,0 +1,110 @@ +<& 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. + +<%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'; + + diff --git a/httemplate/search/report_agent_commission.html b/httemplate/search/report_agent_commission.html index 79f94c52e..41f40bf22 100644 --- a/httemplate/search/report_agent_commission.html +++ b/httemplate/search/report_agent_commission.html @@ -4,9 +4,15 @@ -<% 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 &>
@@ -16,7 +22,8 @@ <% 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'); diff --git a/httemplate/search/report_agent_commission_pkg.html b/httemplate/search/report_agent_commission_pkg.html new file mode 100644 index 000000000..705941c4d --- /dev/null +++ b/httemplate/search/report_agent_commission_pkg.html @@ -0,0 +1,22 @@ +<% include('/elements/header.html', 'Agent commission report' ) %> + +
+ + + +<% include( '/elements/tr-select-agent.html', disable_empty => 1 ) %> + +<% include( '/elements/tr-input-beginning_ending.html', ) %> + +
+ +
+ + +<% include('/elements/footer.html') %> +<%init> + +die "access denied" + unless $FS::CurrentUser::CurrentUser->access_right('Financial reports'); + + diff --git a/httemplate/search/sales_commission.html b/httemplate/search/sales_commission.html index 710461c40..68e030dd2 100644 --- a/httemplate/search/sales_commission.html +++ b/httemplate/search/sales_commission.html @@ -1,20 +1,18 @@ % 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> @@ -24,12 +22,6 @@ die "access denied" 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"; @@ -46,69 +38,17 @@ if ( $cgi->param('salesnum') =~ /^(\d+)$/ ) { } 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' ]; diff --git a/httemplate/search/sales_pkg_class.html b/httemplate/search/sales_pkg_class.html index 8bb6bde4c..a586fc156 100644 --- a/httemplate/search/sales_pkg_class.html +++ b/httemplate/search/sales_pkg_class.html @@ -1,20 +1,14 @@ -<& 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> @@ -23,85 +17,31 @@ die "access denied" 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' ];