connection fee for initial N seconds support, RT#7018

9 files changed, 109 insertions, 23 deletions

diff --git a/FS/FS/Schema.pm b/FS/FS/Schema.pm
index ecb3a749d..d0a61aa8d 100644
--- a/FS/FS/Schema.pm
+++ b/FS/FS/Schema.pm
@@ -2126,16 +2126,17 @@ sub tables_hashref {
 
     'rate_detail' => {
       'columns' => [
-        'ratedetailnum',   'serial', '', '', '', '', 
-        'ratenum',         'int',     '', '', '', '', 
-        'orig_regionnum',  'int', 'NULL', '', '', '', 
-        'dest_regionnum',  'int',     '', '', '', '', 
-        'min_included',    'int',     '', '', '', '', 
-        #'min_charge',      @money_type, '', '', 
-        'min_charge',      'decimal', '', '10,5', '', '', 
-        'sec_granularity', 'int',     '', '', '', '', 
+        'ratedetailnum',   'serial',  '',     '', '', '', 
+        'ratenum',         'int',     '',     '', '', '', 
+        'orig_regionnum',  'int', 'NULL',     '', '', '', 
+        'dest_regionnum',  'int',     '',     '', '', '', 
+        'min_included',    'int',     '',     '', '', '', 
+        'conn_charge',     @money_type, '0', '', #'decimal','','10,5','0','',
+        'conn_sec',        'int',     '',     '', '0', '',
+        'min_charge',      'decimal', '', '10,5', '', '', #@money_type, '', '', 
+        'sec_granularity', 'int',     '',     '', '', '', 
         #time period (link to table of periods)?
-        'classnum',        'int',     'NULL', '', '', '', 
+        'classnum',        'int', 'NULL',     '', '', '', 
       ],
       'primary_key' => 'ratedetailnum',
       'unique'      => [ [ 'ratenum', 'orig_regionnum', 'dest_regionnum' ] ],
diff --git a/FS/FS/part_pkg/voip_cdr.pm b/FS/FS/part_pkg/voip_cdr.pm
index 0c87581ed..38e5941a9 100644
--- a/FS/FS/part_pkg/voip_cdr.pm
+++ b/FS/FS/part_pkg/voip_cdr.pm
@@ -535,6 +535,9 @@ sub calc_usage {
                       # length($cdr->billsec) ? $cdr->billsec : $cdr->duration;
           $seconds = $use_duration ? $cdr->duration : $cdr->billsec;
 
+          $seconds -= $rate_detail->conn_sec;
+          $seconds = 0 if $seconds < 0;
+
           $seconds += $granularity - ( $seconds % $granularity )
             if $seconds      # don't granular-ize 0 billsec calls (bills them)
             && $granularity; # 0 is per call
@@ -546,12 +549,15 @@ sub calc_usage {
 
           $included_min{$regionnum} -= $minutes;
 
+          $charge = sprintf('%.2f', $rate_detail->conn_charge);
+
           if ( $included_min{$regionnum} < 0 ) {
             my $charge_min = 0 - $included_min{$regionnum}; #XXX should preserve
                                                             #(display?) this
             $included_min{$regionnum} = 0;
-            $charge = sprintf('%.2f', ( $rate_detail->min_charge * $charge_min )
-                                      + 0.00000001 ); #so 1.005 rounds to 1.01
+            $charge += sprintf('%.2f', ($rate_detail->min_charge * $charge_min)
+                                       + 0.00000001 ); #so 1.005 rounds to 1.01
+            $charge = sprintf('%.2f', $charge);
             $charges += $charge;
           }
 
diff --git a/FS/FS/rate_detail.pm b/FS/FS/rate_detail.pm
index b7b23babe..f6cdedf6e 100644
--- a/FS/FS/rate_detail.pm
+++ b/FS/FS/rate_detail.pm
@@ -232,6 +232,31 @@ sub granularities {
   %granularities;
 }
 
+=item conn_secs
+
+  Returns an (ordered) hash of conn_sec => name pairs
+
+=cut
+
+tie my %conn_secs, 'Tie::IxHash',
+    '0' => 'connection',
+    '1' => 'first second',
+    '6' => 'first 6 seconds',
+   '30' => 'first 30 seconds', # '1/2 minute',
+   '60' => 'first minute',
+  '120' => 'first 2 minutes',
+  '180' => 'first 3 minutes',
+  '300' => 'first 5 minutes',
+;
+
+sub conn_secs {
+  %conn_secs;
+}
+
+=item process_edit_import
+
+=cut
+
 use Storable qw(thaw);
 use Data::Dumper;
 use MIME::Base64;
@@ -311,6 +336,10 @@ sub process_edit_import {
 
 }
 
+=item edit_import
+
+=cut
+
 #false laziness w/ #FS::Record::batch_import, grep "edit_import" for differences
 #could be turned into callbacks or something
 use Text::CSV_XS;
@@ -569,8 +598,6 @@ sub edit_import {
 
 }
 
-
-
 =back
 
 =head1 BUGS
diff --git a/httemplate/browse/rate_detail.html b/httemplate/browse/rate_detail.html
index 23bc23ff8..3371926b4 100644
--- a/httemplate/browse/rate_detail.html
+++ b/httemplate/browse/rate_detail.html
@@ -15,6 +15,7 @@
                            'Region',
                            'Prefix(es)',
                            'Included<BR>minutes',
+                           'Connection<BR>charge',
                            'Charge per<BR>minute',
                            'Granularity',
                            'Usage class',
@@ -22,12 +23,9 @@
      'fields'         => [
                            'regionname',
                            sub { shift->dest_region->prefixes_short },
-                           sub { shift->min_included.
-                                 '&nbsp;<FONT SIZE="-1">(edit)</FONT>';
-                               },
-                           sub { $money_char. shift->min_charge.
-                                 '&nbsp;<FONT SIZE="-1">(edit)</FONT>';
-                               },
+                           sub { shift->min_included. $edit_hint },
+                           $conn_charge_sub,
+                           sub { $money_char. shift->min_charge. $edit_hint },
                            sub { $granularity{ shift->sec_granularity } },
                            'classname',
                          ],
@@ -39,6 +37,7 @@
 <%once>
 
 tie my %granularity, 'Tie::IxHash', FS::rate_detail::granularities();
+tie my %conn_secs,   'Tie::IxHash', FS::rate_detail::conn_secs();
 
 my $conf = new FS::Conf;
 my $money_char = $conf->config('money_char') || '$';
@@ -59,6 +58,15 @@ my $edit_onclick = sub {
              #default# 'color'       => '#333399',
          );
 };
+my $edit_hint = '&nbsp;<FONT SIZE="-1">(edit)</FONT>';
+
+my $conn_charge_sub = sub {
+   my $rate_detail = shift;
+   #return '' unless $rate_detail->conn_charge > 0 || $rate_detail->conn_sec;
+   $money_char. $rate_detail->conn_charge.
+     ($rate_detail->conn_sec ? ' for '.$conn_secs{$rate_detail->conn_sec} : '').
+     $edit_hint;
+};
 
 </%once>
 <%init>
diff --git a/httemplate/edit/process/rate_region.cgi b/httemplate/edit/process/rate_region.cgi
index 882991e9d..8036f7388 100755
--- a/httemplate/edit/process/rate_region.cgi
+++ b/httemplate/edit/process/rate_region.cgi
@@ -39,7 +39,7 @@ my @dest_detail = map {
   new FS::rate_detail {
     'ratenum'  => $ratenum,
     map { $_ => $cgi->param("$_$ratenum") }
-        qw( min_included min_charge sec_granularity classnum )
+        qw( min_included conn_charge conn_sec min_charge sec_granularity classnum )
   };
 } qsearch('rate', {} );
 
diff --git a/httemplate/edit/rate_detail.html b/httemplate/edit/rate_detail.html
index dd8c3f6b3..869ace8d4 100644
--- a/httemplate/edit/rate_detail.html
+++ b/httemplate/edit/rate_detail.html
@@ -6,6 +6,8 @@
                    'dest_regionname'     => 'Region',
                    'dest_prefixes_short' => 'Prefix(es)',
                    'min_included'        => 'Included minutes/calls',
+                   'conn_charge'         => 'Connection charge',
+                   'conn_sec'            => 'For',
                    'min_charge'          => 'Charge per minute/call',
                    'sec_granularity'     => 'Granularity',
                    'classnum'            => 'Usage class',
@@ -17,6 +19,13 @@
                    { field=>'dest_regionname',     type=>'fixed',  },
                    { field=>'dest_prefixes_short', type=>'fixed',  },
                    { field=>'min_included',        type=>'text',  size=>5 },
+                   { field=>'conn_charge',         type=>'money', size=>4 },
+                   { field          =>'conn_sec',
+                      type          =>'select',
+                      options       => [ keys %conn_secs ],
+                      labels        => \%conn_secs,
+                      disable_empty => 1,
+                   },
                    { field=>'min_charge',          type=>'money', size=>4 },
                    { field         =>'sec_granularity',
                      type          =>'select',
@@ -38,9 +47,9 @@
 <%once>
 
 tie my %granularity, 'Tie::IxHash', FS::rate_detail::granularities();
+tie my %conn_secs,   'Tie::IxHash', FS::rate_detail::conn_secs();
 
 </%once>
-
 <%init>
 
 my $conf = new FS::Conf;
diff --git a/httemplate/edit/rate_region.cgi b/httemplate/edit/rate_region.cgi
index 9ca3a3569..f77c0dbe7 100644
--- a/httemplate/edit/rate_region.cgi
+++ b/httemplate/edit/rate_region.cgi
@@ -52,6 +52,12 @@
       <FONT SIZE=-1>Included<BR>minutes/calls</FONT>
     </TH>
     <TH CLASS="grid" BGCOLOR="#cccccc">
+      <FONT SIZE=-1>Connection<BR>charge</FONT>
+    </TH>
+    <TH CLASS="grid" BGCOLOR="#cccccc">
+      <FONT SIZE=-1>Connection<BR>charge for</FONT>
+    </TH>
+    <TH CLASS="grid" BGCOLOR="#cccccc">
       <FONT SIZE=-1>Charge per<BR>minute/call</FONT>
     </TH>
     <TH CLASS="grid" BGCOLOR="#cccccc">
@@ -88,7 +94,20 @@
     </TD>
 
     <TD CLASS="grid" BGCOLOR="<% $bgcolor %>">
-      $<INPUT TYPE="text" SIZE=6 NAME="min_charge<%$n%>" VALUE="<% $cgi->param("min_charge$n") || $rate_detail->min_charge |h %>">
+      <%$money_char%><INPUT TYPE="text" SIZE=9 NAME="conn_charge<%$n%>" VALUE="<% $cgi->param("conn_charge$n") || $rate_detail->conn_charge |h %>">
+    </TD>
+
+    <TD CLASS="grid" BGCOLOR="<% $bgcolor %>">
+      <SELECT NAME="conn_sec<%$n%>">
+%       foreach my $conn_sec ( keys %conn_secs ) {
+%         my $curr_value = $cgi->param("conn_sec$n") || $rate_detail->conn_sec;
+%         my $selected = ($conn_sec==$curr_value) ? ' SELECTED' : '';
+          <OPTION VALUE="<% $conn_sec %>" <%$selected%>><% $conn_secs{$conn_sec} %></OPTION>
+%       }
+    </TD>
+
+    <TD CLASS="grid" BGCOLOR="<% $bgcolor %>">
+      <%$money_char%><INPUT TYPE="text" SIZE=6 NAME="min_charge<%$n%>" VALUE="<% $cgi->param("min_charge$n") || $rate_detail->min_charge |h %>">
     </TD>
 
     <TD CLASS="grid" BGCOLOR="<% $bgcolor %>">
@@ -125,12 +144,19 @@
 </FORM>
 
 <% include('/elements/footer.html') %>
+<%once>
+
+tie my %conn_secs,   'Tie::IxHash', FS::rate_detail::conn_secs();
 
+</%once>
 <%init>
 
 die "access denied"
   unless $FS::CurrentUser::CurrentUser->access_right('Configuration');
 
+my $conf = new FS::Conf;
+my $money_char = $conf->config('money_char') || '$';
+
 my $rate_region;
 if ( $cgi->param('error') ) {
   $rate_region = new FS::rate_region ( {
diff --git a/httemplate/misc/process/copy-rate_detail.html b/httemplate/misc/process/copy-rate_detail.html
index 87a674566..60b2aebee 100644
--- a/httemplate/misc/process/copy-rate_detail.html
+++ b/httemplate/misc/process/copy-rate_detail.html
@@ -47,7 +47,7 @@ foreach my $countrycode ( @countrycodes ) {
                           || new FS::rate_detail   \%hash;
 
     $dst_rate_detail->$_( $src_rate_detail->get($_) )
-      foreach qw( min_included min_charge sec_granularity classnum );
+      foreach qw( min_included conn_charge conn_sec min_charge sec_granularity classnum );
 
     my $method = $dst_rate_detail->ratedetailnum ? 'replace' : 'insert';
 
diff --git a/httemplate/misc/rate_edit_excel.html b/httemplate/misc/rate_edit_excel.html
index e73133c05..442d83aca 100644
--- a/httemplate/misc/rate_edit_excel.html
+++ b/httemplate/misc/rate_edit_excel.html
@@ -1,5 +1,9 @@
 <% include('/elements/header.html', 'Edit rates with Excel' ) %>
 
+% if ( $have_conn ) { 
+  <FONT COLOR="#FF0000">WARNING: This functionality does not yet preserve connection charges.</FONT><BR><BR>
+% }
+
 <% include( '/elements/form-file_upload.html',
               'name'      => 'RateImportForm',
               'action'    => 'process/rate_edit_excel.html',
@@ -58,4 +62,9 @@
 die "access denied"
   unless $FS::CurrentUser::CurrentUser->access_right('Configuration');
 
+my $sth = dbh->prepare('SELECT COUNT(*) FROM rate_detail WHERE conn_charge > 0 OR conn_sec > 0 LIMIT 1')
+  or die dbh->errstr;
+$sth->execute or die $sth->errstr;
+my $have_conn = $sth->fetchrow_arrayref->[0];
+
 </%init>