agent commission schedules for consecutive invoices, #71217

6 files changed, 470 insertions, 0 deletions

diff --git a/FS/FS/Mason.pm b/FS/FS/Mason.pm
index 1008fd5d8..245bdea88 100644
--- a/FS/FS/Mason.pm
+++ b/FS/FS/Mason.pm
@@ -413,6 +413,8 @@ if ( -e $addl_handler_use_file ) {
   use FS::olt_site;
   use FS::access_user_page_pref;
   use FS::part_svc_msgcat;
+  use FS::commission_schedule;
+  use FS::commission_rate;
   # Sammath Naur
 
   if ( $FS::Mason::addl_handler_use ) {
diff --git a/FS/FS/Schema.pm b/FS/FS/Schema.pm
index ac585108e..8661c4b97 100644
--- a/FS/FS/Schema.pm
+++ b/FS/FS/Schema.pm
@@ -1361,6 +1361,7 @@ sub tables_hashref {
         'commission_agentnum', 'int', 'NULL', '', '', '', #
         'commission_salesnum', 'int', 'NULL', '', '', '', #
         'commission_pkgnum',   'int', 'NULL', '', '', '', #
+        'commission_invnum',   'int', 'NULL', '', '', '',
         'credbatch',    'varchar', 'NULL', $char_d, '', '',
       ],
       'primary_key'  => 'crednum',
@@ -1396,6 +1397,10 @@ sub tables_hashref {
                             table      => 'cust_pkg',
                             references => [ 'pkgnum' ],
                           },
+                          { columns    => [ 'commission_invnum' ],
+                            table      => 'cust_bill',
+                            references => [ 'invnum' ],
+                          },
                         ],
     },
 
@@ -1417,6 +1422,7 @@ sub tables_hashref {
         'commission_agentnum', 'int', 'NULL', '', '', '',
         'commission_salesnum', 'int', 'NULL', '', '', '',
         'commission_pkgnum',   'int', 'NULL', '', '', '',
+        'commission_invnum',   'int', 'NULL', '', '', '',
         #void fields
         'void_date',  @date_type,                  '', '', 
         'void_reason', 'varchar', 'NULL', $char_d, '', '', 
@@ -1456,6 +1462,10 @@ sub tables_hashref {
                             table      => 'cust_pkg',
                             references => [ 'pkgnum' ],
                           },
+                          { columns    => [ 'commission_invnum' ],
+                            table      => 'cust_bill',
+                            references => [ 'invnum' ],
+                          },
                           { columns    => [ 'void_reasonnum' ],
                             table      => 'reason',
                             references => [ 'reasonnum' ],
@@ -7438,6 +7448,36 @@ sub tables_hashref {
                         ],
     },
 
+    'commission_schedule' => {
+      'columns' => [
+        'schedulenum',    'serial',     '',      '', '', '',
+        'schedulename',  'varchar',     '', $char_d, '', '',
+        'reasonnum',         'int', 'NULL',      '', '', '',
+        'basis',         'varchar', 'NULL',      32, '', '',
+      ],
+      'primary_key'  => 'schedulenum',
+      'unique'       => [],
+      'index'        => [],
+    },
+
+    'commission_rate' => {
+      'columns' => [
+        'commissionratenum', 'serial',     '',      '', '', '',
+        'schedulenum',       'int',     '',      '', '', '',
+        'cycle',             'int',     '',      '', '', '',
+        'amount',            @money_type,          '', '', 
+        'percent',           'decimal','',   '7,4', '', '',
+      ],
+      'primary_key'  => 'commissionratenum',
+      'unique'       => [ [ 'schedulenum', 'cycle', ] ],
+      'index'        => [],
+      'foreign_keys' => [
+                          { columns => [ 'schedulenum' ],
+                            table   => 'commission_schedule',
+                          },
+                        ],
+    },
+ 
     # name type nullability length default local
 
     #'new_table' => {
diff --git a/FS/FS/commission_rate.pm b/FS/FS/commission_rate.pm
new file mode 100644
index 000000000..dcb596d60
--- /dev/null
+++ b/FS/FS/commission_rate.pm
@@ -0,0 +1,116 @@
+package FS::commission_rate;
+use base qw( FS::Record );
+
+use strict;
+use FS::Record qw( qsearch qsearchs );
+
+=head1 NAME
+
+FS::commission_rate - Object methods for commission_rate records
+
+=head1 SYNOPSIS
+
+  use FS::commission_rate;
+
+  $record = new FS::commission_rate \%hash;
+  $record = new FS::commission_rate { 'column' => 'value' };
+
+  $error = $record->insert;
+
+  $error = $new_record->replace($old_record);
+
+  $error = $record->delete;
+
+  $error = $record->check;
+
+=head1 DESCRIPTION
+
+An FS::commission_rate object represents a commission rate (a percentage or a
+flat amount) that will be paid on a customer's N-th invoice. The sequence of
+commissions that will be paid on consecutive invoices is the parent object,
+L<FS::commission_schedule>.
+
+FS::commission_rate inherits from FS::Record.  The following fields are
+currently supported:
+
+=over 4
+
+=item commissionratenum - primary key
+
+=item schedulenum - L<FS::commission_schedule> foreign key
+
+=item cycle - the ordinal of the billing cycle this commission will apply
+to. cycle = 1 applies to the customer's first invoice, cycle = 2 to the
+second, etc.
+
+=item amount - the flat amount to pay per invoice in commission
+
+=item percent - the percentage of the invoice amount to pay in 
+commission
+
+=back
+
+=head1 METHODS
+
+=over 4
+
+=item new HASHREF
+
+Creates a new commission rate.  To add it to the database, see L<"insert">.
+
+=cut
+
+sub table { 'commission_rate'; }
+
+=item insert
+
+Adds this record to the database.  If there is an error, returns the error,
+otherwise returns false.
+
+=item delete
+
+Delete this record from the database.
+
+=item replace OLD_RECORD
+
+Replaces the OLD_RECORD with this one in the database.  If there is an error,
+returns the error, otherwise returns false.
+
+=item check
+
+Checks all fields to make sure this is a valid commission rate.  If there is
+an error, returns the error, otherwise returns false.  Called by the insert
+and replace methods.
+
+=cut
+
+sub check {
+  my $self = shift;
+
+  $self->set('amount', '0.00')
+    if $self->get('amount') eq '';
+  $self->set('percent', '0')
+    if $self->get('percent') eq '';
+
+  my $error = 
+    $self->ut_numbern('commissionratenum')
+    || $self->ut_number('schedulenum')
+    || $self->ut_number('cycle')
+    || $self->ut_money('amount')
+    || $self->ut_decimal('percent')
+  ;
+  return $error if $error;
+
+  $self->SUPER::check;
+}
+
+=back
+
+=head1 SEE ALSO
+
+L<FS::Record>
+
+=cut
+
+1;
+
diff --git a/FS/FS/commission_schedule.pm b/FS/FS/commission_schedule.pm
new file mode 100644
index 000000000..375386c33
--- /dev/null
+++ b/FS/FS/commission_schedule.pm
@@ -0,0 +1,235 @@
+package FS::commission_schedule;
+use base qw( FS::o2m_Common FS::Record );
+
+use strict;
+use FS::Record qw( qsearch qsearchs );
+use FS::commission_rate;
+use Tie::IxHash;
+
+tie our %basis_options, 'Tie::IxHash', (
+  setuprecur    => 'Total sales',
+  setup         => 'One-time and setup charges',
+  recur         => 'Recurring charges',
+  setup_cost    => 'Setup costs',
+  recur_cost    => 'Recurring costs',
+  setup_margin  => 'Setup charges minus costs',
+  recur_margin_permonth => 'Monthly recurring charges minus costs',
+);
+
+=head1 NAME
+
+FS::commission_schedule - Object methods for commission_schedule records
+
+=head1 SYNOPSIS
+
+  use FS::commission_schedule;
+
+  $record = new FS::commission_schedule \%hash;
+  $record = new FS::commission_schedule { 'column' => 'value' };
+
+  $error = $record->insert;
+
+  $error = $new_record->replace($old_record);
+
+  $error = $record->delete;
+
+  $error = $record->check;
+
+=head1 DESCRIPTION
+
+An FS::commission_schedule object represents a bundle of one or more
+commission rates for invoices. FS::commission_schedule inherits from
+FS::Record.  The following fields are currently supported:
+
+=over 4
+
+=item schedulenum - primary key
+
+=item schedulename - descriptive name
+
+=item reasonnum - the credit reason (L<FS::reason>) that will be assigned
+to these commission credits
+
+=item basis - for percentage credits, which component of the invoice charges
+the percentage will be calculated on:
+- setuprecur (total charges)
+- setup
+- recur
+- setup_cost
+- recur_cost
+- setup_margin (setup - setup_cost)
+- recur_margin_permonth ((recur - recur_cost) / freq)
+
+=back
+
+=head1 METHODS
+
+=over 4
+
+=item new HASHREF
+
+Creates a new commission schedule.  To add the object to the database, see
+L<"insert">.
+
+=cut
+
+sub table { 'commission_schedule'; }
+
+=item insert
+
+Adds this record to the database.  If there is an error, returns the error,
+otherwise returns false.
+
+=item delete
+
+Delete this record from the database.
+
+=cut
+
+sub delete {
+  my $self = shift;
+  # don't allow the schedule to be removed if it's still linked to events
+  if ($self->part_event) {
+    return 'This schedule is still in use.'; # UI should be smarter
+  }
+  $self->process_o2m(
+    'table'   => 'commission_rate',
+    'params'  => [],
+  ) || $self->delete;
+}
+
+=item replace OLD_RECORD
+
+Replaces the OLD_RECORD with this one in the database.  If there is an error,
+returns the error, otherwise returns false.
+
+=item check
+
+Checks all fields to make sure this is a valid record.  If there is
+an error, returns the error, otherwise returns false.  Called by the insert
+and replace methods.
+
+=cut
+
+sub check {
+  my $self = shift;
+
+  my $error = 
+    $self->ut_numbern('schedulenum')
+    || $self->ut_text('schedulename')
+    || $self->ut_number('reasonnum')
+    || $self->ut_enum('basis', [ keys %basis_options ])
+  ;
+  return $error if $error;
+
+  $self->SUPER::check;
+}
+
+=item part_event
+
+Returns a list of billing events (L<FS::part_event> objects) that pay
+commission on this schedule.
+
+=cut
+
+sub part_event {
+  my $self = shift;
+  map { $_->part_event }
+    qsearch('part_event_option', {
+      optionname  => 'schedulenum',
+      optionvalue => $self->schedulenum,
+    }
+  );
+}
+
+=item calc_credit INVOICE
+
+Takes an L<FS::cust_bill> object and calculates credit on this schedule.
+Returns the amount to credit. If there's no rate defined for this invoice,
+returns nothing.
+
+=cut
+
+# Some false laziness w/ FS::part_event::Action::Mixin::credit_bill.
+# this is a little different in that we calculate the credit on the whole
+# invoice.
+
+sub calc_credit {
+  my $self = shift;
+  my $cust_bill = shift;
+  die "cust_bill record required" if !$cust_bill or !$cust_bill->custnum;
+  # count invoices before or including this one
+  my $cycle = FS::cust_bill->count('custnum = ? AND _date <= ?',
+    $cust_bill->custnum,
+    $cust_bill->_date
+  );
+  my $rate = qsearchs('commission_rate', {
+    schedulenum => $self->schedulenum,
+    cycle       => $cycle,
+  });
+  # we might do something with a rate that applies "after the end of the
+  # schedule" (cycle = 0 or something) so that this can do commissions with
+  # no end date. add that here if there's a need.
+  return unless $rate;
+
+  my $amount;
+  if ( $rate->percent ) {
+    my $what = $self->basis;
+    my $cost = ($what =~ /_cost/ ? 1 : 0);
+    my $margin = ($what =~ /_margin/ ? 1 : 0);
+    my %part_pkg_cache;
+    foreach my $cust_bill_pkg ( $cust_bill->cust_bill_pkg ) {
+
+      my $charge = 0;
+      next if !$cust_bill_pkg->pkgnum; # exclude taxes and fees
+
+      my $cust_pkg = $cust_bill_pkg->cust_pkg;
+      if ( $margin or $cost ) {
+        # look up package costs only if we need them
+        my $pkgpart = $cust_bill_pkg->pkgpart_override || $cust_pkg->pkgpart;
+        my $part_pkg   = $part_pkg_cache{$pkgpart}
+                     ||= FS::part_pkg->by_key($pkgpart);
+
+        if ( $cost ) {
+          $charge = $part_pkg->get($what);
+        } else { # $margin
+          $charge = $part_pkg->$what($cust_pkg);
+        }
+
+        $charge = ($charge || 0) * ($cust_pkg->quantity || 1);
+
+      } else {
+
+        if ( $what eq 'setup' ) {
+          $charge = $cust_bill_pkg->get('setup');
+        } elsif ( $what eq 'recur' ) {
+          $charge = $cust_bill_pkg->get('recur');
+        } elsif ( $what eq 'setuprecur' ) {
+          $charge = $cust_bill_pkg->get('setup') +
+                    $cust_bill_pkg->get('recur');
+        }
+      }
+
+      $amount += ($charge * $rate->percent / 100);
+
+    }
+  } # if $rate->percent
+
+  if ( $rate->amount ) {
+    $amount += $rate->amount;
+  }
+
+  $amount = sprintf('%.2f', $amount + 0.005);
+  return $amount;
+}
+
+=back
+
+=head1 SEE ALSO
+
+L<FS::Record>, L<FS::part_event>, L<FS::commission_rate>
+
+=cut
+
+1;
+
diff --git a/FS/FS/cust_credit.pm b/FS/FS/cust_credit.pm
index 85463724c..e4b1fc07d 100644
--- a/FS/FS/cust_credit.pm
+++ b/FS/FS/cust_credit.pm
@@ -315,6 +315,7 @@ sub check {
     || $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')
+    || $self->ut_foreign_keyn('commission_invnum', 'cust_bill', 'invnum')
   ;
   return $error if $error;
 
diff --git a/FS/FS/part_event/Action/bill_agent_credit_schedule.pm b/FS/FS/part_event/Action/bill_agent_credit_schedule.pm
new file mode 100644
index 000000000..31189a237
--- /dev/null
+++ b/FS/FS/part_event/Action/bill_agent_credit_schedule.pm
@@ -0,0 +1,76 @@
+package FS::part_event::Action::bill_agent_credit_schedule;
+
+use base qw( FS::part_event::Action );
+use FS::Conf;
+use FS::cust_credit;
+use FS::commission_schedule;
+use Date::Format qw(time2str);
+
+use strict;
+
+sub description { 'Credit the agent based on a commission schedule' }
+
+sub option_fields {
+  'schedulenum' => { 'label'        => 'Schedule',
+                     'type'         => 'select-table',
+                     'table'        => 'commission_schedule',
+                     'name_col'     => 'schedulename',
+                     'disable_empty'=> 1,
+                   },
+}
+
+sub eventtable_hashref {
+  { 'cust_bill' => 1 };
+}
+
+our $date_format;
+
+sub do_action {
+  my( $self, $cust_bill, $cust_event ) = @_;
+
+  $date_format ||= FS::Conf->new->config('date_format') || '%x';
+
+  my $cust_main = $self->cust_main($cust_bill);
+  my $agent = $cust_main->agent;
+  return "No customer record for agent ". $agent->agent
+    unless $agent->agent_custnum;
+
+  my $agent_cust_main = $agent->agent_cust_main;
+
+  my $schedulenum = $self->option('schedulenum')
+    or return "no commission schedule selected";
+  my $schedule = FS::commission_schedule->by_key($schedulenum)
+    or return "commission schedule #$schedulenum not found";
+    # commission_schedule::delete tries to prevent this, but just in case
+
+  my $amount = $schedule->calc_credit($cust_bill)
+    or return;
+
+  my $reasonnum = $schedule->reasonnum;
+
+  #XXX shouldn't do this here, it's a localization problem.
+  # credits with commission_invnum should know how to display it as part
+  # of invoice rendering.
+  my $desc = 'from invoice #'. $cust_bill->display_invnum .
+             ' ('. time2str($date_format, $cust_bill->_date) . ')';
+             # could also show custnum and pkgnums here?
+  my $cust_credit = FS::cust_credit->new({
+    'custnum'             => $agent_cust_main->custnum,
+    'reasonnum'           => $reasonnum,
+    'amount'              => $amount,
+    'eventnum'            => $cust_event->eventnum,
+    'addlinfo'            => $desc,
+    'commission_agentnum' => $cust_main->agentnum,
+    'commission_invnum'   => $cust_bill->invnum,
+  });
+  my $error = $cust_credit->insert;
+  die "Error crediting customer ". $agent_cust_main->custnum.
+      " for agent commission: $error"
+    if $error;
+
+  #return $warning; # currently don't get warnings here
+  return;
+
+}
+
+1;