From 511df0f6b2b42849d3174fa6c8267a9e36191baf Mon Sep 17 00:00:00 2001 From: Mark Wells Date: Wed, 16 Oct 2013 17:49:20 -0700 Subject: [PATCH] sales commission report improvements, #25256 --- FS/FS/sales.pm | 81 ++++++++++++++++++++------ httemplate/search/cust_bill_pkg.cgi | 41 +++++++------ httemplate/search/report_sales_commission.html | 16 +++-- httemplate/search/sales_commission.html | 12 ++-- httemplate/search/sales_pkg_class.html | 27 ++++++--- 5 files changed, 124 insertions(+), 53 deletions(-) diff --git a/FS/FS/sales.pm b/FS/FS/sales.pm index 82c875a36..bdeaf1b68 100644 --- a/FS/FS/sales.pm +++ b/FS/FS/sales.pm @@ -131,35 +131,78 @@ sub sales_cust_main { qsearchs( 'cust_main', { 'custnum' => $self->sales_custnum } ); } -sub cust_bill_pkg { +=item cust_bill_pkg START END OPTIONS + +Returns the package line items (see L) for which this +sales person could receive commission. + +START and END are an optional date range to limit the results. + +OPTIONS may contain: +- I: if this is a true value, sales of packages that have no +package sales person will be included if this is their customer sales person. +- I: limit to this package classnum. +- I: limit to sales that have no unpaid balance. + +=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 '; + my $salesnum = $self->salesnum; + die "bad salesnum" unless $salesnum =~ /^(\d+)$/; + 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'}; - $classnum_sql = " AND part_pkg.classnum ". ( $classnum ? " = $classnum " - : ' IS NULL ' ); + my $classnum = $search{'classnum'} || ''; + die "bad classnum" unless $classnum =~ /^(\d*)$/; + + push @where, + "part_pkg.classnum ". ( $classnum ? " = $classnum " : ' IS NULL ' ); } - qsearch({ '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' => ( keys %{ $search{'hashref'} } - ? ' AND ' : 'WHERE ' - ). - " cust_bill._date >= $sdate ". - " AND cust_bill._date < $edate ". - " AND $cmp_salesnum = ". $self->salesnum. - $classnum_sql, - #%search, - }); + # sales_pkg_class number-of-months limit, grr + # (we should be able to just check for the cust_event record from the + # commission credit, but the report is supposed to act as a check on that) + # + # Pg-specific, of course + my $setup_date = 'TO_TIMESTAMP( cust_pkg.setup )'; + my $interval = "(sales_pkg_class.commission_duration || ' months')::interval"; + my $charge_date = 'TO_TIMESTAMP( cust_bill._date )'; + push @where, "CASE WHEN sales_pkg_class.commission_duration IS NOT NULL ". + "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, + }; +} + +sub cust_bill_pkg { + my $self = shift; + qsearch( $self->cust_bill_pkg_search(@_) ) } sub cust_credit { diff --git a/httemplate/search/cust_bill_pkg.cgi b/httemplate/search/cust_bill_pkg.cgi index 4c5e90f6a..fc74b542e 100644 --- a/httemplate/search/cust_bill_pkg.cgi +++ b/httemplate/search/cust_bill_pkg.cgi @@ -283,24 +283,7 @@ if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) { push @where, "cust_main.agentnum = $1"; } -# salesnum -if ( $cgi->param('salesnum') =~ /^(\d+)$/ ) { - - my $salesnum = $1; - - my $cmp_salesnum = $cgi->param('cust_main_sales') - ? ' COALESCE( cust_pkg.salesnum, cust_main.salesnum )' - : ' cust_pkg.salesnum '; - - push @where, "$cmp_salesnum = $salesnum"; - - #because currently we're called from sales_pkg_class.html for a specific - # class (or empty class) but not for all classes - #will have to do something to distinguish if someone wants the sales report - # (report_cust_bill_pkg.html) to have a sales person dropdown - $cgi->param('classnum', 0) unless $cgi->param('classnum'); -} - +# salesnum--see below # refnum if ( $cgi->param('refnum') =~ /^(\d+)$/ ) { push @where, "cust_main.refnum = $1"; @@ -704,6 +687,28 @@ if ( $cgi->param('credit') ) { push @select, 'cust_main.custnum', FS::UI::Web::cust_sql_fields(); +#salesnum +if ( $cgi->param('salesnum') =~ /^(\d+)$/ ) { + + my $salesnum = $1; + my $sales = FS::sales->by_key($salesnum) + or die "salesnum $salesnum not found"; + + my $subsearch = $sales->cust_bill_pkg_search('', '', + 'cust_main_sales' => ($cgi->param('cust_main_sales') ? 1 : 0), + 'paid' => ($cgi->param('paid') ? 1 : 0), + 'classnum' => scalar($cgi->param('classnum')) + ); + $join_pkg .= " JOIN sales_pkg_class ON ( COALESCE(sales_pkg_class.classnum, 0) = COALESCE( part_pkg.classnum, 0) )"; + + my $extra_sql = $subsearch->{extra_sql}; + $extra_sql =~ s/^WHERE//; + push @where, $extra_sql; + + $cgi->param('classnum', 0) unless $cgi->param('classnum'); +} + + my $where = join(' AND ', @where); $where &&= "WHERE $where"; diff --git a/httemplate/search/report_sales_commission.html b/httemplate/search/report_sales_commission.html index cc17e6bed..792c3353e 100644 --- a/httemplate/search/report_sales_commission.html +++ b/httemplate/search/report_sales_commission.html @@ -20,14 +20,22 @@ -<& /elements/tr-select-sales.html &> +<& /elements/tr-select-sales.html, + 'empty_label' => 'all', +&> <& /elements/tr-checkbox.html, - 'label' => 'Customer sales person if there is no package sales person', - 'field' => 'cust_main_sales', - 'value' => 'Y', + 'label' => 'Customer sales person if there is no package sales person', + 'field' => 'cust_main_sales', + 'value' => 'Y', &> +<& /elements/tr-checkbox.html, + 'label' => 'Show paid sales only', + 'field' => 'paid', + 'value' => 'Y', +&> + <& /elements/tr-input-beginning_ending.html &> diff --git a/httemplate/search/sales_commission.html b/httemplate/search/sales_commission.html index b4d40aeb2..e74f3792e 100644 --- a/httemplate/search/sales_commission.html +++ b/httemplate/search/sales_commission.html @@ -36,6 +36,8 @@ my $count_query = "SELECT COUNT(*) FROM sales"; my $salesnum; if ( $cgi->param('salesnum') =~ /^(\d+)$/ ) { $salesnum = $1; +} else { + $cgi->delete('salesnum'); } my $title = 'Sales person commission'; @@ -43,13 +45,14 @@ $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' : ''; my $sales_link = [ 'sales_pkg_class.html?'. - "begin=$beginning;". - "end=$ending;". - "cust_main_sales=$cust_main_sales;". - "salesnum=", + # pass all of our parameters along + $cgi->query_string. ';salesnum=', 'salesnum' ]; @@ -64,6 +67,7 @@ my $sales_sub_maker = sub { $beginning, $ending, 'cust_main_sales' => $cust_main_sales, + 'paid' => $paid, ); $total += $_->get($field) foreach @cust_bill_pkg; diff --git a/httemplate/search/sales_pkg_class.html b/httemplate/search/sales_pkg_class.html index da5d512d5..8bb6bde4c 100644 --- a/httemplate/search/sales_pkg_class.html +++ b/httemplate/search/sales_pkg_class.html @@ -9,7 +9,7 @@ $sales_sub_maker->('setup'), $sales_sub_maker->('recur'), $commission_sub, ], - 'links' => [ '', '', '', $commission_link ], + 'links' => [ '', $sales_link, $sales_link, $commission_link ], 'align' => 'lrrr', 'query' => { 'table' => 'sales_pkg_class', 'hashref' => { 'salesnum' => $salesnum }, @@ -40,6 +40,9 @@ $title .= ': '. time2str($date_format, $beginning). ' to '. if $beginning; 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;". @@ -55,18 +58,17 @@ my $sales_sub_maker = sub { my $field = shift; sub { my $sales_pkg_class = shift; - - #efficiency improvement: ask the db for a sum instead of all the records - my $total = 0; - my @cust_bill_pkg = $sales->cust_bill_pkg( + # 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, ); - $total += $_->get($field) foreach @cust_bill_pkg; - - $money_char. sprintf('%.2f', $total); + $search->{'select'} = "SUM(cust_bill_pkg.$field) AS total"; + my $result = qsearchs($search); + $money_char. sprintf('%.2f', $result ? $result->get('total') : 0); }; }; @@ -85,6 +87,15 @@ my $commission_sub = sub { $money_char. sprintf('%.2f', $total_credit); }; +my $sales_link = [ 'cust_bill_pkg.cgi?'. + "begin=$beginning;". + "end=$ending;". + "cust_main_sales=$cust_main_sales;". + "salesnum=$salesnum;". + "classnum=", + 'classnum' + ]; + my $commission_link = [ 'cust_credit.html?'. "begin=$beginning;". "end=$ending;". -- 2.11.0