Patch to obtain ISDN usage for billing in Freeside 1.5.7 - There are better ways to do this.

1 files changed, 269 insertions, 0 deletions

diff --git a/install/rpm/freeside-1.5.7.nasport.patch b/install/rpm/freeside-1.5.7.nasport.patch
new file mode 100644
index 0000000..64f645b
--- /dev/null
+++ b/install/rpm/freeside-1.5.7.nasport.patch
@@ -0,0 +1,269 @@
+diff -Naur freeside-1.5.7.orig/FS/bin/freeside-sqlradius-seconds freeside-1.5.7/FS/bin/freeside-sqlradius-seconds
+--- freeside-1.5.7.orig/FS/bin/freeside-sqlradius-seconds	2002-12-17 05:42:26.000000000 -0500
++++ freeside-1.5.7/FS/bin/freeside-sqlradius-seconds	2005-09-09 10:45:39.010386201 -0400
+@@ -17,7 +17,12 @@
+ my $svc_acct = qsearchs( 'svc_acct', { 'username' => $target_user } );
+ die "username $target_user not found\n" unless $svc_acct;
+ 
+-print $svc_acct->seconds_since_sqlradacct( $start, $stop ). "\n";
++my %usage = $svc_acct->seconds_since_sqlradacct( $start, $stop );
++my $seconds = 0;
++foreach (keys %usage) {
++  $seconds += $usage{$_};
++}
++print $seconds . "\n";
+ 
+ sub usage {
+   die "Usage:\n\n  freeside-sqlradius-seconds freeside_username target_username start_date stop_date\n";
+diff -Naur freeside-1.5.7.orig/FS/FS/cust_pkg.pm freeside-1.5.7/FS/FS/cust_pkg.pm
+--- freeside-1.5.7.orig/FS/FS/cust_pkg.pm	2005-03-21 17:13:36.000000000 -0500
++++ freeside-1.5.7/FS/FS/cust_pkg.pm	2005-09-08 16:10:06.000000000 -0400
+@@ -857,7 +857,7 @@
+ sub seconds_since_sqlradacct {
+   my($self, $start, $end) = @_;
+ 
+-  my $seconds = 0;
++  my %seconds = ();
+ 
+   foreach my $cust_svc (
+     grep {
+@@ -866,10 +866,13 @@
+         && scalar($part_svc->part_export('sqlradius'));
+     } $self->cust_svc
+   ) {
+-    $seconds += $cust_svc->seconds_since_sqlradacct($start, $end);
++    my %result = $cust_svc->seconds_since_sqlradacct($start, $end);
++    foreach (keys %result) {
++      $seconds{$_} += $result{$_};
++    }
+   }
+ 
+-  $seconds;
++  %seconds;
+ 
+ }
+ 
+diff -Naur freeside-1.5.7.orig/FS/FS/cust_svc.pm freeside-1.5.7/FS/FS/cust_svc.pm
+--- freeside-1.5.7.orig/FS/FS/cust_svc.pm	2005-06-09 16:16:58.000000000 -0400
++++ freeside-1.5.7/FS/FS/cust_svc.pm	2005-09-08 17:12:12.000000000 -0400
+@@ -390,7 +390,7 @@
+     unless @part_export;
+     #or return undef;
+ 
+-  my $seconds = 0;
++  my %seconds = ();
+   foreach my $part_export ( @part_export ) {
+ 
+     next if $part_export->option('ignore_accounting');
+@@ -423,65 +423,82 @@
+     my $query;
+   
+     #find closed sessions completely within the given range
+-    my $sth = $dbh->prepare("SELECT SUM(acctsessiontime)
++    my $sth = $dbh->prepare("SELECT NASPortType, SUM(acctsessiontime)
+                                FROM radacct
+                                WHERE UserName = ?
+                                  AND $str2time AcctStartTime) >= ?
+                                  AND $str2time AcctStopTime ) <  ?
+                                  AND $str2time AcctStopTime ) > 0
+-                                 AND AcctStopTime IS NOT NULL"
++                                 AND AcctStopTime IS NOT NULL
++                                 AND NASPortType IS NOT NULL
++                                 GROUP BY NASPortType"
+     ) or die $dbh->errstr;
+     $sth->execute($username, $start, $end) or die $sth->errstr;
+-    my $regular = $sth->fetchrow_arrayref->[0];
++    my %regular = ();
++    while (my $h = $sth->fetchrow_arrayref()) {
++      $seconds{$h->[0]} = $h->[1];
++    }
+   
+     #find open sessions which start in the range, count session start->range end
+-    $query = "SELECT SUM( ? - $str2time AcctStartTime ) )
++    $query = "SELECT NASPortType, SUM( ? - $str2time AcctStartTime ) )
+                 FROM radacct
+                 WHERE UserName = ?
+                   AND $str2time AcctStartTime ) >= ?
+                   AND $str2time AcctStartTime ) <  ?
+                   AND ( ? - $str2time AcctStartTime ) ) < 86400
+                   AND (    $str2time AcctStopTime ) = 0
+-                                    OR AcctStopTime IS NULL )";
++                                    OR AcctStopTime IS NULL )
++                  AND NASPortType IS NOT NULL
++                  GROUP BY NASPortType";
+     $sth = $dbh->prepare($query) or die $dbh->errstr;
+     $sth->execute($end, $username, $start, $end, $end)
+       or die $sth->errstr. " executing query $query";
+-    my $start_during = $sth->fetchrow_arrayref->[0];
++    while (my $h = $sth->fetchrow_arrayref()) {
++#      $seconds{$h->[0]} += $h->[1];
++    }
+   
+     #find closed sessions which start before the range but stop during,
+     #count range start->session end
+-    $sth = $dbh->prepare("SELECT SUM( $str2time AcctStopTime ) - ? ) 
++    $sth = $dbh->prepare("SELECT NASPortType, SUM( $str2time AcctStopTime ) - ? ) 
+                             FROM radacct
+                             WHERE UserName = ?
+                               AND $str2time AcctStartTime ) < ?
+                               AND $str2time AcctStopTime  ) >= ?
+                               AND $str2time AcctStopTime  ) <  ?
+                               AND $str2time AcctStopTime ) > 0
+-                              AND AcctStopTime IS NOT NULL"
++                              AND AcctStopTime IS NOT NULL
++                              AND AcctStartTime > 0
++                              AND NASPortType IS NOT NULL
++                              GROUP BY NASPortType"
+     ) or die $dbh->errstr;
+     $sth->execute($start, $username, $start, $start, $end ) or die $sth->errstr;
+-    my $end_during = $sth->fetchrow_arrayref->[0];
++    while (my $h = $sth->fetchrow_arrayref()) {
++#      $seconds{$h->[0]} += $h->[1];
++    }
+   
+     #find closed (not anymore - or open) sessions which start before the range
+     # but stop after, or are still open, count range start->range end
+     # don't count open sessions (probably missing stop record)
+-    $sth = $dbh->prepare("SELECT COUNT(*)
++    $sth = $dbh->prepare("SELECT NASPortType, COUNT(*)
+                             FROM radacct
+                             WHERE UserName = ?
+                               AND $str2time AcctStartTime ) < ?
+-                              AND ( $str2time AcctStopTime ) >= ?
+-                                                                  )"
++                              AND $str2time AcctStopTime ) >= ?
++                              AND AcctStartTime > 0
++                              AND NASPortType IS NOT NULL
++                              GROUP BY NASPortType"
+                               #      OR AcctStopTime =  0
+                               #      OR AcctStopTime IS NULL       )"
+     ) or die $dbh->errstr;
+     $sth->execute($username, $start, $end ) or die $sth->errstr;
+-    my $entire_range = ($end-$start) * $sth->fetchrow_arrayref->[0];
+-
+-    $seconds += $regular + $end_during + $start_during + $entire_range;
++    while (my $h = $sth->fetchrow_arrayref()) {
++#      my $entire_range = ($end-$start) * $h->[1];
++#      $seconds{$h->[0]} += $entire_range;
++    }
+ 
+   }
+ 
+-  $seconds;
++  %seconds;
+ 
+ }
+ 
+diff -Naur freeside-1.5.7.orig/FS/FS/part_pkg/sqlradacct_hour.pm freeside-1.5.7/FS/FS/part_pkg/sqlradacct_hour.pm
+--- freeside-1.5.7.orig/FS/FS/part_pkg/sqlradacct_hour.pm	2005-07-09 06:36:43.000000000 -0400
++++ freeside-1.5.7/FS/FS/part_pkg/sqlradacct_hour.pm	2005-09-08 16:10:06.000000000 -0400
+@@ -26,6 +26,10 @@
+     'recur_hourly_charge' => { 'name' => 'Additional charge per hour',
+                                'default' => 0,
+                              },
++    'NASPortTypes' => { 'name' => 'NAS Port Types',
++				'default' => 'Async ISDN Virtual',
++                #                'type' => 'select',
++                             },
+     'recur_included_input' => { 'name' => 'Upload megabytes included',
+                                 'default' => 0,
+                               },
+@@ -49,7 +53,7 @@
+                               'default' => 0,
+                             },
+   },
+-  'fieldorder' => [qw( setup_fee recur_flat unused_credit recur_included_hours recur_hourly_charge recur_included_input recur_input_charge recur_included_output recur_output_charge recur_included_total recur_total_charge )],
++  'fieldorder' => [qw( setup_fee recur_flat unused_credit recur_included_hours recur_hourly_charge NASPortTypes recur_included_input recur_input_charge recur_included_output recur_output_charge recur_included_total recur_total_charge )],
+   #'setup' => 'what.setup_fee.value',
+   #'recur' => '\'my $last_bill = $cust_pkg->last_bill; my $hours = $cust_pkg->seconds_since_sqlradacct($last_bill, $sdate ) / 3600 - \' + what.recur_included_hours.value + \'; $hours = 0 if $hours < 0; my $input = $cust_pkg->attribute_since_sqlradacct($last_bill, $sdate, \"AcctInputOctets\" ) / 1048576; my $output = $cust_pkg->attribute_since_sqlradacct($last_bill, $sdate, \"AcctOutputOctets\" ) / 1048576; my $total = $input + $output - \' + what.recur_included_total.value + \'; $total = 0 if $total < 0; my $input = $input - \' + what.recur_included_input.value + \'; $input = 0 if $input < 0; my $output = $output - \' + what.recur_included_output.value + \'; $output = 0 if $output < 0; my $totalcharge = sprintf(\"%.2f\", \' + what.recur_total_charge.value + \' * $total); my $inputcharge = sprintf(\"%.2f\", \' + what.recur_input_charge.value + \' * $input); my $outputcharge = sprintf(\"%.2f\", \' + what.recur_output_charge.value + \' * $output); my $hourscharge = sprintf(\"%.2f\", \' + what.recur_hourly_charge.value + \' * $hours); if ( \' + what.recur_total_charge.value + \' > 0 ) { push @details, \"Last month\\\'s data \". sprintf(\"%.1f\", $total). \" megs: \\\$$totalcharge\" } if ( \' + what.recur_input_charge.value + \' > 0 ) { push @details, \"Last month\\\'s download \". sprintf(\"%.1f\", $input). \" megs: \\\$$inputcharge\" } if ( \' + what.recur_output_charge.value + \' > 0 ) { push @details, \"Last month\\\'s upload \". sprintf(\"%.1f\", $output). \" megs: \\\$$outputcharge\" } if ( \' + what.recur_hourly_charge.value + \' > 0 ) { push @details, \"Last month\\\'s time \". sprintf(\"%.1f\", $hours). \" hours: \\\$$hourscharge\"; } \' + what.recur_flat.value + \' + $hourscharge + $inputcharge + $outputcharge + $totalcharge ;\'',
+   'weight' => 40,
+@@ -59,7 +63,12 @@
+   my($self, $cust_pkg, $sdate, $details ) = @_;
+ 
+   my $last_bill = $cust_pkg->last_bill;
+-  my $hours = $cust_pkg->seconds_since_sqlradacct($last_bill, $$sdate ) / 3600;
++  my %result = $cust_pkg->seconds_since_sqlradacct($last_bill, $$sdate );
++  my $seconds = 0;
++  foreach (split '\s+', $self->option('NASPortTypes')) {
++    $seconds += $result{$_};
++  }
++  my $hours = $seconds / 3600;
+   $hours -= $self->option('recur_included_hours');
+   $hours = 0 if $hours < 0;
+ 
+@@ -81,30 +90,37 @@
+   $output = 0 if $output < 0;
+ 
+   my $totalcharge =
+-    $total  * sprintf('%.2f', $self->option('recur_total_charge'));
++    sprintf('%.2f', $total  * $self->option('recur_total_charge'));
+   my $inputcharge =
+-    $input  * sprintf('%.2f', $self->option('recur_input_charge'));
++    sprintf('%.2f', $input  * $self->option('recur_input_charge'));
+   my $outputcharge = 
+-    $output * sprintf('%.2f', $self->option('recur_output_charge'));
++    sprintf('%.2f', $output * $self->option('recur_output_charge'));
+ 
+   my $hourscharge =
+-    $hours * sprintf('%.2f', $self->option('recur_hourly_charge'));
++    sprintf('%.2f', $hours * $self->option('recur_hourly_charge'));
++
++#  my $money_char = $conf->config('money_char') || '$';
++  my $money_char = '$';
++
++  if ( $self->option('recur_flat') > 0 ) {
++    push @$details, "Base charge: $money_char" . sprintf('%.2f', $self->option('recur_flat'));
++  }
+ 
+   if ( $self->option('recur_total_charge') > 0 ) {
+-    push @$details, "Last month's data ".
+-                    sprintf('%.1f', $total). " megs: $totalcharge";
++    push @$details, "Excess data ".
++                    sprintf('%.1f', $total). " megs: $money_char$totalcharge";
+   }
+   if ( $self->option('recur_input_charge') > 0 ) {
+-    push @$details, "Last month's download ".
+-                   sprintf('%.1f', $input). " megs: $inputcharge";
++    push @$details, "Excess download ".
++                   sprintf('%.1f', $input). " megs: $money_char$inputcharge";
+   }
+   if ( $self->option('recur_output_charge') > 0 ) {
+-    push @$details, "Last month's upload ".
+-                   sprintf('%.1f', $output). " megs: $outputcharge";
++    push @$details, "Excess upload ".
++                   sprintf('%.1f', $output). " megs: $money_char$outputcharge";
+   }
+   if ( $self->option('recur_hourly_charge')  > 0 ) {
+-    push @$details, "Last month\'s time ".
+-                   sprintf('%.1f', $hours). " hours: $hourscharge";
++    push @$details, "Excess time ".
++                   sprintf('%.1f', $hours). " hours: $money_char$hourscharge";
+   }
+ 
+   $self->option('recur_flat')
+diff -Naur freeside-1.5.7.orig/httemplate/view/svc_acct.cgi freeside-1.5.7/httemplate/view/svc_acct.cgi
+--- freeside-1.5.7.orig/httemplate/view/svc_acct.cgi	2005-06-08 05:03:06.000000000 -0400
++++ freeside-1.5.7/httemplate/view/svc_acct.cgi	2005-09-08 16:10:06.000000000 -0400
+@@ -74,7 +74,11 @@
+     %plandata = ();
+   }
+ 
+-  my $seconds = $svc_acct->seconds_since_sqlradacct( $last_bill, time );
++  my %usage = $svc_acct->seconds_since_sqlradacct( $last_bill, time );
++  my $seconds = 0;
++  foreach (keys %usage) {
++    $seconds += $usage{$_};
++  }
+   my $hour = int($seconds/3600);
+   my $min = int( ($seconds%3600) / 60 );
+   my $sec = $seconds%60;
+@@ -108,6 +112,9 @@
+     (no billing cycle available for unaudited account)<BR>
+   <% } %>
+ 
++  <% foreach (sort keys %usage) { %>
++     <%= $_ %>: <B><%= int($usage{$_}/3600) %></B>h <B><%= int(($usage{$_} % 3600) / 60) %></B>m <B><%= $usage{$_} % 60 %></B>s<BR>
++  <% } %>
+   Upload: <B><%= sprintf("%.3f", $input) %></B> megabytes<BR>
+   Download: <B><%= sprintf("%.3f", $output) %></B> megabytes<BR>
+