restructure agent commission reporting, #23348

5 files changed, 204 insertions, 72 deletions

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<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;
diff --git a/FS/FS/agent.pm b/FS/FS/agent.pm
index e7fb3fba2..d6171c672 100644
--- a/FS/FS/agent.pm
+++ b/FS/FS/agent.pm
@@ -1,7 +1,7 @@
 package FS::agent;
 
 use strict;
-use vars qw( @ISA );
+use base qw( FS::Commission_Mixin FS::m2m_Common FS::Record );
 #use Crypt::YAPassGen;
 use Business::CreditCard 0.28;
 use FS::Record qw( dbh qsearch qsearchs );
@@ -12,8 +12,6 @@ use FS::reg_code;
 use FS::TicketSystem;
 use FS::Conf;
 
-@ISA = qw( FS::m2m_Common FS::Record );
-
 =head1 NAME
 
 FS::agent - Object methods for agent records
@@ -722,6 +720,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..6b748972b 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,31 @@ sub check {
   $self->SUPER::check;
 }
 
+sub cust_credit_search {
+  my $self = shift;
+  my $agent = FS::agent->by_key($self->agentnum);
+  $agent->cust_credit_search(@_, commission_classnum => $self->classnum);
+}
+
+sub cust_bill_pkg_search {
+  my $self = shift;
+  my $agent = FS::agent->by_key($self->agentnum);
+  $agent->cust_bill_pkg_search(@_, classnum => $self->classnum);
+}
+
+#3.x stub
+sub pkg_class {
+  my $self = shift;
+  my $classnum = $self->get('classnum');
+  $classnum ? FS::pkg_class->by_key($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 f1b406da7..d1f473175 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 );
@@ -146,29 +146,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 
@@ -182,60 +173,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 90867a828..84ba683a1 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;
 use FS::Record qw( qsearchs ); # qsearch qsearchs );
@@ -121,6 +121,18 @@ sub classname {
   $pkg_class ? $pkg_class->classname : '(no package class)';
 }
 
+sub cust_credit_search {
+  my $self = shift;
+  my $sales = FS::sales->by_key($self->salesnum);
+  $sales->cust_credit_search(@_, commission_classnum => $self->classnum);
+}
+
+sub cust_bill_pkg_search {
+  my $self = shift;
+  my $sales = FS::sales->by_key($self->salesnum);
+  $sales->cust_bill_pkg_search(@_, classnum => $self->classnum);
+}
+
 =back
 
 =head1 BUGS