From de55504121071ada6dd7252a5bbc2ae8b3317636 Mon Sep 17 00:00:00 2001 From: rsiddall Date: Thu, 28 Jun 2007 02:34:40 +0000 Subject: [PATCH] Patch to obtain ISDN usage for billing in Freeside 1.5.7 - There are better ways to do this. --- install/rpm/freeside-1.5.7.nasport.patch | 269 +++++++++++++++++++++++++++++++ 1 file changed, 269 insertions(+) create mode 100644 install/rpm/freeside-1.5.7.nasport.patch 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 000000000..64f645b66 --- /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)
+ <% } %> + ++ <% foreach (sort keys %usage) { %> ++ <%= $_ %>: <%= int($usage{$_}/3600) %>h <%= int(($usage{$_} % 3600) / 60) %>m <%= $usage{$_} % 60 %>s
++ <% } %> + Upload: <%= sprintf("%.3f", $input) %> megabytes
+ Download: <%= sprintf("%.3f", $output) %> megabytes
+ -- 2.11.0