From dd2249efe0daa3fc3257029de84d212aa89a4ee9 Mon Sep 17 00:00:00 2001 From: ivan Date: Tue, 30 Mar 2010 12:16:00 +0000 Subject: [PATCH] employee commission reporting, RT#6991 --- FS/FS/Schema.pm | 3 +- FS/FS/cust_credit.pm | 2 + FS/FS/cust_event.pm | 6 +- FS/FS/cust_main.pm | 8 +- FS/FS/part_event/Action/pkg_agent_credit.pm | 7 +- FS/FS/part_event/Action/pkg_employee_credit.pm | 7 +- FS/FS/part_event/Action/pkg_referral_credit.pm | 7 +- httemplate/search/part_pkg.html | 211 ++++++++++++++++++++++ httemplate/search/report_employee_commission.html | 34 ++++ 9 files changed, 268 insertions(+), 17 deletions(-) create mode 100644 httemplate/search/part_pkg.html create mode 100644 httemplate/search/report_employee_commission.html diff --git a/FS/FS/Schema.pm b/FS/FS/Schema.pm index 6756c9c32..660a072b8 100644 --- a/FS/FS/Schema.pm +++ b/FS/FS/Schema.pm @@ -655,10 +655,11 @@ sub tables_hashref { 'addlinfo', 'text', 'NULL', '', '', '', 'closed', 'char', 'NULL', 1, '', '', 'pkgnum', 'int', 'NULL', '', '', '', #desired pkgnum for pkg-balances + 'eventnum', 'int', 'NULL', '', '', '', #triggering event for commission ], 'primary_key' => 'crednum', 'unique' => [], - 'index' => [ ['custnum'], ['_date'] ], + 'index' => [ ['custnum'], ['_date'], ['eventnum'] ], }, 'cust_credit_bill' => { diff --git a/FS/FS/cust_credit.pm b/FS/FS/cust_credit.pm index 6c3effa13..d0aa3a4b4 100644 --- a/FS/FS/cust_credit.pm +++ b/FS/FS/cust_credit.pm @@ -14,6 +14,7 @@ use FS::cust_credit_bill; use FS::part_pkg; use FS::reason_type; use FS::reason; +use FS::cust_event; @ISA = qw( FS::cust_main_Mixin FS::Record ); $me = '[ FS::cust_credit ]'; @@ -301,6 +302,7 @@ sub check { || $self->ut_textn('addlinfo') || $self->ut_enum('closed', [ '', 'Y' ]) || $self->ut_foreign_keyn('pkgnum', 'cust_pkg', 'pkgnum') + || $self->ut_foreign_keyn('eventnum', 'cust_event', 'eventnum') ; return $error if $error; diff --git a/FS/FS/cust_event.pm b/FS/FS/cust_event.pm index d2fcfc1e2..52b5911dc 100644 --- a/FS/FS/cust_event.pm +++ b/FS/FS/cust_event.pm @@ -1,18 +1,16 @@ package FS::cust_event; use strict; +use base qw( FS::cust_main_Mixin FS::Record ); use vars qw( @ISA $DEBUG $me ); use Carp qw( croak confess ); use FS::Record qw( qsearch qsearchs dbdef ); -use FS::cust_main_Mixin; use FS::part_event; #for cust_X use FS::cust_main; use FS::cust_pkg; use FS::cust_bill; -@ISA = qw(FS::cust_main_Mixin FS::Record); - $DEBUG = 0; $me = '[FS::cust_event]'; @@ -230,7 +228,7 @@ sub do_event { my $error; { local $SIG{__DIE__}; # don't want Mason __DIE__ handler active - $error = eval { $part_event->do_action($object); }; + $error = eval { $part_event->do_action($object, $self); }; } my $status = ''; diff --git a/FS/FS/cust_main.pm b/FS/FS/cust_main.pm index 5116049f3..88aceb935 100644 --- a/FS/FS/cust_main.pm +++ b/FS/FS/cust_main.pm @@ -7320,7 +7320,7 @@ sub referral_cust_main_ncancelled { Like referral_cust_main, except returns a flat list of all unsuspended (and uncancelled) packages for each customer. The number of items in this list may -be useful for comission calculations (perhaps after a Cpkgpart; grep { $_ == $pkgpart } @commission_worthy_pkgparts> } $cust_main-> ). +be useful for commission calculations (perhaps after a Cpkgpart; grep { $_ == $pkgpart } @commission_worthy_pkgparts> } $cust_main-> ). =cut @@ -7382,8 +7382,10 @@ sub credit { $cust_credit->set('reason', $reason) } - $cust_credit->addlinfo( delete $options{'addlinfo'} ) - if exists($options{'addlinfo'}); + for (qw( addlinfo eventnum )) { + $cust_credit->$_( delete $options{$_} ) + if exists($options{$_}); + } $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 250273846..4bcee983b 100644 --- a/FS/FS/part_event/Action/pkg_agent_credit.pm +++ b/FS/FS/part_event/Action/pkg_agent_credit.pm @@ -7,7 +7,7 @@ sub description { 'Credit the agent a specific amount'; } #a little false laziness w/pkg_referral_credit sub do_action { - my( $self, $cust_pkg ) = @_; + my( $self, $cust_pkg, $cust_event ) = @_; my $cust_main = $self->cust_main($cust_pkg); @@ -26,8 +26,9 @@ sub do_action { my $error = $agent_cust_main->credit( $amount, \$reasonnum, - 'addlinfo' => - 'for customer #'. $cust_main->display_custnum. ': '.$cust_main->name, + 'eventnum' => $cust_event->eventnum, + 'addlinfo' => 'for customer #'. $cust_main->display_custnum. + ': '.$cust_main->name, ); die "Error crediting customer ". $agent_cust_main->custnum. " for agent commission: $error" diff --git a/FS/FS/part_event/Action/pkg_employee_credit.pm b/FS/FS/part_event/Action/pkg_employee_credit.pm index 94fc5f3b1..e4913a21f 100644 --- a/FS/FS/part_event/Action/pkg_employee_credit.pm +++ b/FS/FS/part_event/Action/pkg_employee_credit.pm @@ -9,7 +9,7 @@ sub description { 'Credit the ordering employee a specific amount'; } #a little false laziness w/pkg_referral_credit sub do_action { - my( $self, $cust_pkg ) = @_; + my( $self, $cust_pkg, $cust_event ) = @_; my $cust_main = $self->cust_main($cust_pkg); @@ -31,8 +31,9 @@ sub do_action { my $error = $employee_cust_main->credit( $amount, \$reasonnum, - 'addlinfo' => - 'for customer #'. $cust_main->display_custnum. ': '.$cust_main->name, + 'eventnum' => $cust_event->eventnum, + 'addlinfo' => 'for customer #'. $cust_main->display_custnum. + ': '.$cust_main->name, ); die "Error crediting customer ". $employee_cust_main->custnum. " for employee commission: $error" diff --git a/FS/FS/part_event/Action/pkg_referral_credit.pm b/FS/FS/part_event/Action/pkg_referral_credit.pm index da872e7ff..e7c92d650 100644 --- a/FS/FS/part_event/Action/pkg_referral_credit.pm +++ b/FS/FS/part_event/Action/pkg_referral_credit.pm @@ -23,7 +23,7 @@ sub option_fields { } sub do_action { - my( $self, $cust_pkg ) = @_; + my( $self, $cust_pkg, $cust_event ) = @_; my $cust_main = $self->cust_main($cust_pkg); @@ -43,8 +43,9 @@ sub do_action { my $error = $referring_cust_main->credit( $amount, \$reasonnum, - 'addlinfo' => - 'for customer #'. $cust_main->display_custnum. ': '.$cust_main->name, + 'eventnum' => $cust_event->eventnum, + 'addlinfo' => 'for customer #'. $cust_main->display_custnum. + ': '.$cust_main->name, ); die "Error crediting customer ". $cust_main->referral_custnum. " for referral: $error" diff --git a/httemplate/search/part_pkg.html b/httemplate/search/part_pkg.html new file mode 100644 index 000000000..b96839161 --- /dev/null +++ b/httemplate/search/part_pkg.html @@ -0,0 +1,211 @@ +<% include( 'elements/search.html', + 'title' => $title, + 'name' => $name, + 'header' => \@header, + 'query' => { 'select' => $select, + 'table' => 'part_pkg', + 'addl_from' => $addl_from, + 'hashref' => {}, + 'extra_sql' => $extra_sql, + 'order_by' => "ORDER BY $order_by", + }, + 'count_query' => $count_query, + 'fields' => \@fields, + 'links' => \@links, + 'align' => $align, + ) +%> +<%init> + +#this is about reports about packages definitions (starting w/commission ones) +# while browse/part_pkg.cgi is config->package definitions + +my $curuser = $FS::CurrentUser::CurrentUser; +die "access denied" + unless $curuser->access_right('Financial reports'); + +my $conf = new FS::Conf; +my $money_char = $conf->config('money_char') || '$'; + +my $title = 'Package definition report'; +my $name = 'package definition'; + +my $select = ''; +my $addl_from = ''; +my @where = (); +my @order_by = (); +my @header = (); +my @fields = (); +my @links = (); +my $align = ''; + +if (1) { #commission reports + + if (1) { #employee commission reports + + $select = 'DISTINCT usernum, username, part_pkg.*'; + + $addl_from .= ' CROSS JOIN access_user '; + + if ( $cgi->param('otaker') =~ /^(\w+)$/ ) { + + #XXX in this context, agent virt for employees, not package defs + my $access_user = qsearchs('access_user', { 'username' => $1 }) + or die "unknown usernum"; + + $title = $access_user->name; + + } else { + + push @header, 'Employee'; + push @fields, sub { shift->get('username'); }; #access_user->name + push @links, ''; #link to employee edit w/ACL? + $align .= 'c'; + + push @order_by, 'otaker'; + + $title = 'Employee'; + + } + + } elsif (0) { #agent commission reports + + if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) { + + #agent virt + my $agent = qsearchs('agent', { 'agentnum' => $1 }) + or die "unknown agentnum"; + + $title = $agent->agent; + + push @header, 'Agent'; + push @fields, sub { 'XXXagent' }; + push @links, ''; #link to agent edit w/ACL? + $align .= 'c'; + + push @order_by, 'agentnum'; #join to agent? we're mostly interested in grouping rather than order + + } else { + $title = 'Agent'; + } + + } + + $title .= ' commission report'; + $name = "commissionable $name"; + + +} + +push @header, 'Package definition'; +push @fields, 'pkg_comment'; +push @links, ''; #link to pkg definition edit w/ACL? +$align .= 'l'; + +if (1) { #commission reports + + my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi); + + my $match = ''; + if (1) { #employee commission reports + $match = 'cust_pkg.otaker = access_user.username'; + } elsif (0) { #agent commission reports + $match = 'cust_main.agentnum = agent.agentnum'; + } + + my $from_cust_bill_pkg_where = "FROM cust_bill_pkg + LEFT JOIN ( cust_bill ) USING ( invnum ) + LEFT JOIN ( cust_pkg ) USING ( pkgnum ) + WHERE cust_bill_pkg.pkgnum > 0 + AND cust_bill._date >= $beginning + AND cust_bill._date <= $ending "; + my $and = " AND $match + AND cust_pkg.pkgpart = part_pkg.pkgpart"; + + push @where, "EXISTS( SELECT 1 $from_cust_bill_pkg_where $and )"; + + push @header, '#'; # of sales'; + push @links, ''; #link to detail report + $align .= 'r'; + push @fields, 'num_cust_pkg'; + $select .= ", ( SELECT COUNT(DISTINCT pkgnum) + $from_cust_bill_pkg_where $and ) + AS num_cust_pkg"; +# push @fields, sub { +# my $part_pkg = shift; +# my $sql = +# #"SELECT COUNT( SELECT DISTINCT pkgnum $from_cust_bill_pkg_where )"; +# "SELECT COUNT(DISTINCT pkgnum) $from_cust_bill_pkg_where"; +# my $sth = dbh->prepare($sql) or die dbh->errstr; +# $sth->execute or die $sth->errstr; +# $sth->fetchrow_arrayref->[0]; +# }; + + push @header, 'Sales'; + push @links, ''; #link to detail report + $align .= 'r'; +# push @fields, sub { $money_char. sprintf('%.2f', shift->get('pkg_sales')); }; +# $select .= +# ", SUM( SELECT setup+recur $from_cust_bill_pkg_where ) AS pkg_sales"; + push @fields, sub { + my $part_pkg = shift; + my $sql = "SELECT SUM(cust_bill_pkg.setup+cust_bill_pkg.recur) $from_cust_bill_pkg_where AND pkgpart = ? AND "; + my @arg = ($part_pkg->pkgpart); + if (1) { #employee commission reports + $sql .= 'otaker = ?'; + push @arg, $part_pkg->get('username'); + } elsif (0) { #agent commission reports + $match = 'cust_main.agentnum = agent.agentnum'; + } + my $sth = dbh->prepare($sql) or die dbh->errstr; + $sth->execute(@arg) or die $sth->errstr; + $money_char. sprintf('%.2f', $sth->fetchrow_arrayref->[0] ); + }; + + push @header, 'Commission'; + push @links, ''; #link to detail report + $align .= 'r'; + #push @fields, sub { $money_char. sprintf('%.2f', shift->get('pkg_commission')); }; + push @fields, sub { + my $part_pkg = shift; + my $sql = "SELECT SUM(amount) FROM cust_credit + INNER JOIN cust_event USING ( eventnum ) + LEFT JOIN part_event USING ( eventpart ) + LEFT JOIN cust_pkg ON ( cust_event.tablenum = cust_pkg.pkgnum ) + WHERE action IN ( 'pkg_employee_credit', + 'pkg_employee_credit_pkg' + ) + AND cust_credit._date >= $beginning + AND cust_credit._date <= $ending + AND pkgpart = ? + AND cust_credit.custnum = ? + "; + my @arg = ($part_pkg->pkgpart); + if (1) { #employee commission reports + + #XXX in this context, agent virt for employees, not package defs + my $access_user = qsearchs('access_user', { 'username' => $part_pkg->get('username') }) + or die "unknown usernum"; + + push @arg, $access_user->user_custnum or return 0; + + } elsif (0) { #agent commission reports + push @arg, 'XXXagent_custnum'; #$agent->agent_custnum + } + my $sth = dbh->prepare($sql) or die dbh->errstr; + $sth->execute(@arg) or die $sth->errstr; + $money_char. sprintf('%.2f', $sth->fetchrow_arrayref->[0] ); + + }; + +} + +push @order_by, 'pkgpart'; #pkg? + +$select ||= 'part_pkg.*'; +my $extra_sql = scalar(@where) ? 'WHERE ' . join(' AND ', @where) : ''; +my $order_by = join(', ', @order_by); + +my $count_query = "SELECT COUNT(*) FROM part_pkg $addl_from $extra_sql"; + + diff --git a/httemplate/search/report_employee_commission.html b/httemplate/search/report_employee_commission.html new file mode 100644 index 000000000..a79630a76 --- /dev/null +++ b/httemplate/search/report_employee_commission.html @@ -0,0 +1,34 @@ +<% include('/elements/header.html', 'Employee commission report' ) %> + +
+ + + +%# +%# <% include( '/elements/tr-select-agent.html', +%# 'curr_value' => scalar( $cgi->param('agentnum') ), +%# 'disable_empty' => 0, +%# ) +%# %> +%# + +%#2.1 +<% include( '/elements/tr-select-user.html' ) %> + +%#1.9 +%# <%include( '/elements/tr-select-otaker.html' ) %> + +<% include( '/elements/tr-input-beginning_ending.html', ) %> + +
+ +
+ + +<% include('/elements/footer.html') %> +<%init> + +die "access denied" + unless $FS::CurrentUser::CurrentUser->access_right('Financial reports'); + + -- 2.11.0