From eedfe58ba50912dbfb490aa47930bbc0ab9e3ac9 Mon Sep 17 00:00:00 2001 From: Ivan Kohler Date: Mon, 26 Aug 2013 01:09:20 -0700 Subject: [PATCH] continue sales person work: customer and package selection, commissions, reporting. RT#23402 --- FS/FS/Schema.pm | 8 ++- FS/FS/cust_credit.pm | 5 ++ FS/FS/cust_main.pm | 10 +-- FS/FS/part_event/Action/pkg_agent_credit.pm | 9 +-- FS/FS/part_event/Action/pkg_sales_credit.pm | 11 ++-- FS/FS/part_event/Condition/pkg_age_before.pm | 3 +- FS/FS/part_event/Condition/pkg_age_before_sales.pm | 2 +- FS/FS/sales.pm | 58 ++++++++++++++++- httemplate/search/cust_bill_pkg.cgi | 14 ++++ httemplate/search/cust_credit.html | 25 +++++++- httemplate/search/report_sales_commission.html | 26 +++++++- httemplate/search/sales_commission.html | 64 +++++++++++++++++-- httemplate/search/sales_pkg_class.html | 74 ++++++++++++++++++++-- httemplate/view/cust_main/packages/package.html | 2 +- 14 files changed, 277 insertions(+), 34 deletions(-) diff --git a/FS/FS/Schema.pm b/FS/FS/Schema.pm index 818b43741..340b678e9 100644 --- a/FS/FS/Schema.pm +++ b/FS/FS/Schema.pm @@ -1004,11 +1004,15 @@ sub tables_hashref { 'closed', 'char', 'NULL', 1, '', '', 'pkgnum', 'int', 'NULL', '', '','',#desired pkgnum for pkg-balances 'eventnum', 'int', 'NULL', '', '','',#triggering event for commission - #'commission_agentnum', 'int', 'NULL', '', '', '', # + 'commission_agentnum', 'int', 'NULL', '', '', '', # + 'commission_salesnum', 'int', 'NULL', '', '', '', # + 'commission_pkgnum', 'int', 'NULL', '', '', '', # ], 'primary_key' => 'crednum', 'unique' => [], - 'index' => [ ['custnum'], ['_date'], ['usernum'], ['eventnum'] ], + 'index' => [ ['custnum'], ['_date'], ['usernum'], ['eventnum'], + [ 'commission_salesnum' ], + ], }, 'cust_credit_bill' => { diff --git a/FS/FS/cust_credit.pm b/FS/FS/cust_credit.pm index 0376f1dc4..bd92bdc75 100644 --- a/FS/FS/cust_credit.pm +++ b/FS/FS/cust_credit.pm @@ -19,6 +19,8 @@ use FS::part_pkg; use FS::reason_type; use FS::reason; use FS::cust_event; +use FS::agent; +use FS::sales; $me = '[ FS::cust_credit ]'; $DEBUG = 0; @@ -311,6 +313,9 @@ sub check { || $self->ut_enum('closed', [ '', 'Y' ]) || $self->ut_foreign_keyn('pkgnum', 'cust_pkg', 'pkgnum') || $self->ut_foreign_keyn('eventnum', 'cust_event', 'eventnum') + || $self->ut_foreign_keyn('commission_agentnum', 'agent', 'agentnum') + || $self->ut_foreign_keyn('commission_salesnum', 'sales', 'salesnum') + || $self->ut_foreign_keyn('commission_pkgnum', 'cust_pkg', 'pkgnum') ; return $error if $error; diff --git a/FS/FS/cust_main.pm b/FS/FS/cust_main.pm index ef6628ffd..13fd4056e 100644 --- a/FS/FS/cust_main.pm +++ b/FS/FS/cust_main.pm @@ -3300,6 +3300,8 @@ reason, and a 'reason_type' option must be passed to indicate the FS::reason_type for the new reason. An I option may be passed to set the credit's I field. +Likewise for I, I, I and +I. Any other options are passed to FS::cust_credit::insert. @@ -3325,10 +3327,10 @@ sub credit { $cust_credit->set('reason', $reason) } - for (qw( addlinfo eventnum )) { - $cust_credit->$_( delete $options{$_} ) - if exists($options{$_}); - } + $cust_credit->$_( delete $options{$_} ) + foreach grep exists($options{$_}), + qw( addlinfo eventnum ), + map "commission_$_", qw( agentnum salesnum pkgnum ); $cust_credit->insert(%options); diff --git a/FS/FS/part_event/Action/pkg_agent_credit.pm b/FS/FS/part_event/Action/pkg_agent_credit.pm index e1c77be07..494c40e3f 100644 --- a/FS/FS/part_event/Action/pkg_agent_credit.pm +++ b/FS/FS/part_event/Action/pkg_agent_credit.pm @@ -26,10 +26,11 @@ sub do_action { my $error = $agent_cust_main->credit( $amount, \$reasonnum, - 'eventnum' => $cust_event->eventnum, - 'addlinfo' => 'for customer #'. $cust_main->display_custnum. - ': '.$cust_main->name, - #'commission_agentnum' => $agent->agentnum, + 'eventnum' => $cust_event->eventnum, + 'addlinfo' => 'for customer #'. $cust_main->display_custnum. + ': '.$cust_main->name, + 'commission_agentnum' => $agent->agentnum, + 'commission_pkgnum' => $cust_pkg->pkgnum, ); die "Error crediting customer ". $agent_cust_main->custnum. " for agent commission: $error" diff --git a/FS/FS/part_event/Action/pkg_sales_credit.pm b/FS/FS/part_event/Action/pkg_sales_credit.pm index 8e04a3bd5..caa25cc80 100644 --- a/FS/FS/part_event/Action/pkg_sales_credit.pm +++ b/FS/FS/part_event/Action/pkg_sales_credit.pm @@ -26,11 +26,12 @@ sub do_action { my $error = $sales_cust_main->credit( $amount, \$reasonnum, - 'eventnum' => $cust_event->eventnum, - 'addlinfo' => 'for customer #'. $cust_main->display_custnum. - ': '.$cust_main->name. - ', package #'. $cust_pkg->pkgnum, - #'commission_salesnum' => $sales->salesnum, + 'eventnum' => $cust_event->eventnum, + 'addlinfo' => 'for customer #'. $cust_main->display_custnum. + ': '.$cust_main->name. + ', package #'. $cust_pkg->pkgnum, + 'commission_salesnum' => $sales->salesnum, + 'commission_pkgnum' => $cust_pkg->pkgnum, ); die "Error crediting customer ". $sales_cust_main->custnum. " for sales commission: $error" diff --git a/FS/FS/part_event/Condition/pkg_age_before.pm b/FS/FS/part_event/Condition/pkg_age_before.pm index c778265f2..89389ad71 100644 --- a/FS/FS/part_event/Condition/pkg_age_before.pm +++ b/FS/FS/part_event/Condition/pkg_age_before.pm @@ -10,7 +10,8 @@ sub pkg_age_operator { '>'; } sub pkg_age_label { 'Package date age younger than'; } sub pkg_age_compare { - my( $pkg_date, $age ) = @_; + my( $self, $pkg_date, $age ) = @_; + $pkg_date > $age; } diff --git a/FS/FS/part_event/Condition/pkg_age_before_sales.pm b/FS/FS/part_event/Condition/pkg_age_before_sales.pm index 4ad56fa10..32369c05b 100644 --- a/FS/FS/part_event/Condition/pkg_age_before_sales.pm +++ b/FS/FS/part_event/Condition/pkg_age_before_sales.pm @@ -24,7 +24,7 @@ sub option_fields { } sub pkg_age_age { - my( $self, $cust_pkg, %opt ); + my( $self, $cust_pkg, %opt ) = @_; my $salesnum = $cust_pkg->salesnum; $salesnum ||= $self->cust_main($cust_pkg)->salesnum diff --git a/FS/FS/sales.pm b/FS/FS/sales.pm index 00f45c0e4..c8604abce 100644 --- a/FS/FS/sales.pm +++ b/FS/FS/sales.pm @@ -2,9 +2,11 @@ package FS::sales; use base qw( FS::Agent_Mixin FS::Record ); use strict; -use FS::Record qw( qsearchs ); #qsearch qsearchs ); +use FS::Record qw( qsearch qsearchs ); use FS::agent; use FS::cust_main; +use FS::cust_bill_pkg; +use FS::cust_credit; =head1 NAME @@ -129,6 +131,60 @@ sub sales_cust_main { qsearchs( 'cust_main', { 'custnum' => $self->sales_custnum } ); } +sub cust_bill_pkg { + my( $self, $sdate, $edate, %search ) = @_; + + my $cmp_salesnum = delete $search{'cust_main_sales'} + ? ' COALESCE( cust_pkg.salesnum, cust_main.salesnum )' + : ' cust_pkg.salesnum '; + + my $classnum_sql = ''; + if ( exists( $search{'classnum'} ) ) { + my $classnum = $search{'classnum'}; + $classnum_sql = " AND part_pkg.classnum ". ( $classnum ? " = $classnum " + : ' IS NULL ' ); + } + + qsearch({ 'table' => '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, + }); +} + +sub cust_credit { + my( $self, $sdate, $edate, %search ) = @_; + + $search{'hashref'}->{'commission_salesnum'} = $self->salesnum; + + my $classnum_sql = ''; + if ( exists($search{'commission_classnum'}) ) { + my $classnum = delete($search{'commission_classnum'}); + $classnum_sql = " AND 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 ) '; + } + + qsearch({ 'table' => 'cust_credit', + 'extra_sql' => " AND cust_credit._date >= $sdate ". + " AND cust_credit._date < $edate ". + $classnum_sql, + %search, + }); +} + =back =head1 BUGS diff --git a/httemplate/search/cust_bill_pkg.cgi b/httemplate/search/cust_bill_pkg.cgi index bf73d74bd..744286a68 100644 --- a/httemplate/search/cust_bill_pkg.cgi +++ b/httemplate/search/cust_bill_pkg.cgi @@ -283,6 +283,20 @@ 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"; + + $cgi->param('classnum', 0) unless $cgi->param('classnum'); +} + # refnum if ( $cgi->param('refnum') =~ /^(\d+)$/ ) { push @where, "cust_main.refnum = $1"; diff --git a/httemplate/search/cust_credit.html b/httemplate/search/cust_credit.html index cabf8c002..fb6ef834b 100755 --- a/httemplate/search/cust_credit.html +++ b/httemplate/search/cust_credit.html @@ -84,6 +84,7 @@ push @style, '', ; my @search = (); +my $addl_from = ''; if ( $cgi->param('usernum') =~ /^(\d+)$/ ) { push @search, "cust_credit.usernum = $1"; @@ -96,6 +97,23 @@ if ( $cgi->param('agentnum') && $cgi->param('agentnum') =~ /^(\d+)$/ ) { $title = $agent->agent. " $title"; } +# commission_salesnum +if ( $cgi->param('commission_salesnum') =~ /^(\d+)$/ ) { + push @search, "commission_salesnum = $1"; +} + +# commission_classnum +if ( grep { $_ eq 'commission_classnum' } $cgi->param ) { + $cgi->param('commission_classnum') =~ /^(\d*)$/ or die 'guru meditation #13'; + my $classnum = $1; + push @search, "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 ) '; +} + if ( $cgi->param('refnum') && $cgi->param('refnum') =~ /^(\d+)$/ ) { push @search, "refnum = $1"; my $part_referral = qsearchs('part_referral', { 'refnum' => $1 } ); @@ -124,7 +142,7 @@ push @search, "_date >= $beginning ", push @search, FS::UI::Web::parse_lt_gt($cgi, 'amount' ); #here is the agent virtualization -push @search, $FS::CurrentUser::CurrentUser->agentnums_sql; +push @search, $FS::CurrentUser::CurrentUser->agentnums_sql(table=>'cust_main'); my @select = ( 'cust_credit.*', @@ -141,7 +159,8 @@ my $where = 'WHERE '. join(' AND ', @search); my $count_query = 'SELECT COUNT(*), SUM(amount) '; $count_query .= ', SUM(' . FS::cust_credit->unapplied_sql . ') ' if $unapplied; -$count_query .= 'FROM cust_credit'. FS::UI::Web::join_cust_main('cust_credit'). +$count_query .= 'FROM cust_credit'. + $addl_from. FS::UI::Web::join_cust_main('cust_credit'). $where; my @count_addl = ( $money_char.'%.2f total credited (gross)' ); @@ -152,7 +171,7 @@ my $sql_query = { 'select' => join(', ',@select), 'hashref' => {}, 'extra_sql' => $where, - 'addl_from' => FS::UI::Web::join_cust_main('cust_credit') + 'addl_from' => $addl_from. FS::UI::Web::join_cust_main('cust_credit') }; diff --git a/httemplate/search/report_sales_commission.html b/httemplate/search/report_sales_commission.html index 03527f6bd..cc17e6bed 100644 --- a/httemplate/search/report_sales_commission.html +++ b/httemplate/search/report_sales_commission.html @@ -4,11 +4,31 @@ -<% include( '/elements/tr-select-agent.html' ) %> +<& /elements/tr-select-agent.html, + 'onchange' => 'agent_changed(this)', +&> -<% include( '/elements/tr-select-sales.html' ) %> + + +<& /elements/tr-select-sales.html &> + +<& /elements/tr-checkbox.html, + 'label' => 'Customer sales person if there is no package sales person', + 'field' => 'cust_main_sales', + 'value' => 'Y', +&> + +<& /elements/tr-input-beginning_ending.html &>
diff --git a/httemplate/search/sales_commission.html b/httemplate/search/sales_commission.html index 19e11889b..d7b7a88ad 100644 --- a/httemplate/search/sales_commission.html +++ b/httemplate/search/sales_commission.html @@ -1,12 +1,13 @@ <& elements/search.html, - 'title' => 'Sales person commission report', + 'title' => $title, 'name_singular' => 'sales person', # 'redirect' => sub { #my( $sales, $cgi ) = @); # $saleslink; # }, 'header' => [ 'Sales person', 'Sales', 'Commission', ], - 'fields' => [ 'salesperson', sub{'XXX';}, sub{'XXX';}, ], - 'links' => [ '', $saleslink, $commissionlink ], + 'fields' => [ 'salesperson', $sales_sub, $commission_sub, ], + 'links' => [ '', $sales_link, $commission_link ], + 'align' => 'lrr', 'query' => { 'table' => 'sales', }, 'count_query' => 'SELECT COUNT(*) FROM sales', 'disableable' => 1, @@ -16,8 +17,61 @@ die "access denied" unless $FS::CurrentUser::CurrentUser->access_right('Financial reports'); -my $saleslink = [ 'sales_pkg_class.html?salesnum=', 'salesnum' ]; +my $conf = new FS::Conf; -my $commissionlink = ''; #XXX +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 $title = 'Sales person commission'; +$title .= ': '. time2str($date_format, $beginning). ' to '. + time2str($date_format, $ending) + if $beginning; + +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=", + 'salesnum' + ]; + +my $sales_sub = sub { + my $sales = shift; + + #efficiency improvement: ask the db for a sum instead of all the records + my $total_recur = 0; + my @cust_bill_pkg = $sales->cust_bill_pkg( + $beginning, + $ending, + 'cust_main_sales' => $cust_main_sales, + ); + $total_recur += $_->recur foreach @cust_bill_pkg; + + $money_char. sprintf('%.2f', $total_recur); +}; + +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; + + $money_char. sprintf('%.2f', $total_credit); +}; + +my $commission_link = [ 'cust_credit.html?'. + "begin=$beginning;". + "end=$ending;". + "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 bb67c70a6..c57aae66d 100644 --- a/httemplate/search/sales_pkg_class.html +++ b/httemplate/search/sales_pkg_class.html @@ -1,22 +1,88 @@ <& elements/search.html, - 'title' => $sales->salesperson. ' commission package classes', + 'title' => $title, 'name_singular' => 'package class', 'header' => [ 'Package class', 'Sales', 'Commission', ], - 'fields' => [ 'classname', sub{'XXX';}, sub{'XXX';}, ], - #'links' + 'fields' => [ 'classname', $sales_sub, $commission_sub, ], + 'links' => [ '', $sales_link, $commission_link ], + 'align' => 'lrr', 'query' => { 'table' => 'sales_pkg_class', 'hashref' => { 'salesnum' => $salesnum }, }, - 'count_query' => "SELECT COUNT(*) FROM sales_pkg_class WHERE salesnum = $salesnum", + 'count_query' => "SELECT COUNT(*) FROM sales_pkg_class WHERE salesnum = $salesnum", #show some totals? &> <%init> die "access denied" unless $FS::CurrentUser::CurrentUser->access_right('Financial reports'); +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 $date_format = $conf->config('date_format') || '%m/%d/%Y'; + +my $title = $sales->salesperson. ' commission'; +$title .= ': '. time2str($date_format, $beginning). ' to '. + time2str($date_format, $ending) + if $beginning; + +my $cust_main_sales = $cgi->param('cust_main_sales') eq 'Y' ? 'Y' : ''; + +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 = sub { + my $sales_pkg_class = shift; + + #efficiency improvement: ask the db for a sum instead of all the records + my $total_recur = 0; + my @cust_bill_pkg = $sales->cust_bill_pkg( + $beginning, + $ending, + 'cust_main_sales' => $cust_main_sales, + 'classnum' => $sales_pkg_class->classnum, + ); + $total_recur += $_->recur foreach @cust_bill_pkg; + + $money_char. sprintf('%.2f', $total_recur); +}; + +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); +}; + +my $commission_link = [ 'cust_credit.html?'. + "begin=$beginning;". + "end=$ending;". + "cust_main_sales=$cust_main_sales;". + 'commission_salesnum='. $sales->salesnum. ';'. + 'commission_classnum=', + 'classnum' + ]; + diff --git a/httemplate/view/cust_main/packages/package.html b/httemplate/view/cust_main/packages/package.html index f0ece9884..1c8db15f4 100644 --- a/httemplate/view/cust_main/packages/package.html +++ b/httemplate/view/cust_main/packages/package.html @@ -107,7 +107,7 @@ % if ( $cust_pkg->salesnum ) { % $change_sales_label = 'change';       <% mt('Sales Person:') |h %> - <% $cust_pkg->salesperson %> + <% $cust_pkg->salesperson |h %> % } % if ( $curuser->access_right('Change customer package') -- 2.11.0