1 diff -Naur freeside-1.5.7.orig/FS/bin/freeside-sqlradius-seconds freeside-1.5.7/FS/bin/freeside-sqlradius-seconds
2 --- freeside-1.5.7.orig/FS/bin/freeside-sqlradius-seconds 2002-12-17 05:42:26.000000000 -0500
3 +++ freeside-1.5.7/FS/bin/freeside-sqlradius-seconds 2005-09-09 10:45:39.010386201 -0400
5 my $svc_acct = qsearchs( 'svc_acct', { 'username' => $target_user } );
6 die "username $target_user not found\n" unless $svc_acct;
8 -print $svc_acct->seconds_since_sqlradacct( $start, $stop ). "\n";
9 +my %usage = $svc_acct->seconds_since_sqlradacct( $start, $stop );
11 +foreach (keys %usage) {
12 + $seconds += $usage{$_};
14 +print $seconds . "\n";
17 die "Usage:\n\n freeside-sqlradius-seconds freeside_username target_username start_date stop_date\n";
18 diff -Naur freeside-1.5.7.orig/FS/FS/cust_pkg.pm freeside-1.5.7/FS/FS/cust_pkg.pm
19 --- freeside-1.5.7.orig/FS/FS/cust_pkg.pm 2005-03-21 17:13:36.000000000 -0500
20 +++ freeside-1.5.7/FS/FS/cust_pkg.pm 2005-09-08 16:10:06.000000000 -0400
22 sub seconds_since_sqlradacct {
23 my($self, $start, $end) = @_;
28 foreach my $cust_svc (
31 && scalar($part_svc->part_export('sqlradius'));
34 - $seconds += $cust_svc->seconds_since_sqlradacct($start, $end);
35 + my %result = $cust_svc->seconds_since_sqlradacct($start, $end);
36 + foreach (keys %result) {
37 + $seconds{$_} += $result{$_};
46 diff -Naur freeside-1.5.7.orig/FS/FS/cust_svc.pm freeside-1.5.7/FS/FS/cust_svc.pm
47 --- freeside-1.5.7.orig/FS/FS/cust_svc.pm 2005-06-09 16:16:58.000000000 -0400
48 +++ freeside-1.5.7/FS/FS/cust_svc.pm 2005-09-08 17:12:12.000000000 -0400
55 foreach my $part_export ( @part_export ) {
57 next if $part_export->option('ignore_accounting');
61 #find closed sessions completely within the given range
62 - my $sth = $dbh->prepare("SELECT SUM(acctsessiontime)
63 + my $sth = $dbh->prepare("SELECT NASPortType, SUM(acctsessiontime)
66 AND $str2time AcctStartTime) >= ?
67 AND $str2time AcctStopTime ) < ?
68 AND $str2time AcctStopTime ) > 0
69 - AND AcctStopTime IS NOT NULL"
70 + AND AcctStopTime IS NOT NULL
71 + AND NASPortType IS NOT NULL
72 + GROUP BY NASPortType"
73 ) or die $dbh->errstr;
74 $sth->execute($username, $start, $end) or die $sth->errstr;
75 - my $regular = $sth->fetchrow_arrayref->[0];
77 + while (my $h = $sth->fetchrow_arrayref()) {
78 + $seconds{$h->[0]} = $h->[1];
81 #find open sessions which start in the range, count session start->range end
82 - $query = "SELECT SUM( ? - $str2time AcctStartTime ) )
83 + $query = "SELECT NASPortType, SUM( ? - $str2time AcctStartTime ) )
86 AND $str2time AcctStartTime ) >= ?
87 AND $str2time AcctStartTime ) < ?
88 AND ( ? - $str2time AcctStartTime ) ) < 86400
89 AND ( $str2time AcctStopTime ) = 0
90 - OR AcctStopTime IS NULL )";
91 + OR AcctStopTime IS NULL )
92 + AND NASPortType IS NOT NULL
93 + GROUP BY NASPortType";
94 $sth = $dbh->prepare($query) or die $dbh->errstr;
95 $sth->execute($end, $username, $start, $end, $end)
96 or die $sth->errstr. " executing query $query";
97 - my $start_during = $sth->fetchrow_arrayref->[0];
98 + while (my $h = $sth->fetchrow_arrayref()) {
99 +# $seconds{$h->[0]} += $h->[1];
102 #find closed sessions which start before the range but stop during,
103 #count range start->session end
104 - $sth = $dbh->prepare("SELECT SUM( $str2time AcctStopTime ) - ? )
105 + $sth = $dbh->prepare("SELECT NASPortType, SUM( $str2time AcctStopTime ) - ? )
108 AND $str2time AcctStartTime ) < ?
109 AND $str2time AcctStopTime ) >= ?
110 AND $str2time AcctStopTime ) < ?
111 AND $str2time AcctStopTime ) > 0
112 - AND AcctStopTime IS NOT NULL"
113 + AND AcctStopTime IS NOT NULL
114 + AND AcctStartTime > 0
115 + AND NASPortType IS NOT NULL
116 + GROUP BY NASPortType"
117 ) or die $dbh->errstr;
118 $sth->execute($start, $username, $start, $start, $end ) or die $sth->errstr;
119 - my $end_during = $sth->fetchrow_arrayref->[0];
120 + while (my $h = $sth->fetchrow_arrayref()) {
121 +# $seconds{$h->[0]} += $h->[1];
124 #find closed (not anymore - or open) sessions which start before the range
125 # but stop after, or are still open, count range start->range end
126 # don't count open sessions (probably missing stop record)
127 - $sth = $dbh->prepare("SELECT COUNT(*)
128 + $sth = $dbh->prepare("SELECT NASPortType, COUNT(*)
131 AND $str2time AcctStartTime ) < ?
132 - AND ( $str2time AcctStopTime ) >= ?
134 + AND $str2time AcctStopTime ) >= ?
135 + AND AcctStartTime > 0
136 + AND NASPortType IS NOT NULL
137 + GROUP BY NASPortType"
138 # OR AcctStopTime = 0
139 # OR AcctStopTime IS NULL )"
140 ) or die $dbh->errstr;
141 $sth->execute($username, $start, $end ) or die $sth->errstr;
142 - my $entire_range = ($end-$start) * $sth->fetchrow_arrayref->[0];
144 - $seconds += $regular + $end_during + $start_during + $entire_range;
145 + while (my $h = $sth->fetchrow_arrayref()) {
146 +# my $entire_range = ($end-$start) * $h->[1];
147 +# $seconds{$h->[0]} += $entire_range;
157 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
158 --- freeside-1.5.7.orig/FS/FS/part_pkg/sqlradacct_hour.pm 2005-07-09 06:36:43.000000000 -0400
159 +++ freeside-1.5.7/FS/FS/part_pkg/sqlradacct_hour.pm 2005-09-08 16:10:06.000000000 -0400
161 'recur_hourly_charge' => { 'name' => 'Additional charge per hour',
164 + 'NASPortTypes' => { 'name' => 'NAS Port Types',
165 + 'default' => 'Async ISDN Virtual',
166 + # 'type' => 'select',
168 'recur_included_input' => { 'name' => 'Upload megabytes included',
175 - '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 )],
176 + '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 )],
177 #'setup' => 'what.setup_fee.value',
178 #'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 ;\'',
181 my($self, $cust_pkg, $sdate, $details ) = @_;
183 my $last_bill = $cust_pkg->last_bill;
184 - my $hours = $cust_pkg->seconds_since_sqlradacct($last_bill, $$sdate ) / 3600;
185 + my %result = $cust_pkg->seconds_since_sqlradacct($last_bill, $$sdate );
187 + foreach (split '\s+', $self->option('NASPortTypes')) {
188 + $seconds += $result{$_};
190 + my $hours = $seconds / 3600;
191 $hours -= $self->option('recur_included_hours');
192 $hours = 0 if $hours < 0;
195 $output = 0 if $output < 0;
198 - $total * sprintf('%.2f', $self->option('recur_total_charge'));
199 + sprintf('%.2f', $total * $self->option('recur_total_charge'));
201 - $input * sprintf('%.2f', $self->option('recur_input_charge'));
202 + sprintf('%.2f', $input * $self->option('recur_input_charge'));
204 - $output * sprintf('%.2f', $self->option('recur_output_charge'));
205 + sprintf('%.2f', $output * $self->option('recur_output_charge'));
208 - $hours * sprintf('%.2f', $self->option('recur_hourly_charge'));
209 + sprintf('%.2f', $hours * $self->option('recur_hourly_charge'));
211 +# my $money_char = $conf->config('money_char') || '$';
212 + my $money_char = '$';
214 + if ( $self->option('recur_flat') > 0 ) {
215 + push @$details, "Base charge: $money_char" . sprintf('%.2f', $self->option('recur_flat'));
218 if ( $self->option('recur_total_charge') > 0 ) {
219 - push @$details, "Last month's data ".
220 - sprintf('%.1f', $total). " megs: $totalcharge";
221 + push @$details, "Excess data ".
222 + sprintf('%.1f', $total). " megs: $money_char$totalcharge";
224 if ( $self->option('recur_input_charge') > 0 ) {
225 - push @$details, "Last month's download ".
226 - sprintf('%.1f', $input). " megs: $inputcharge";
227 + push @$details, "Excess download ".
228 + sprintf('%.1f', $input). " megs: $money_char$inputcharge";
230 if ( $self->option('recur_output_charge') > 0 ) {
231 - push @$details, "Last month's upload ".
232 - sprintf('%.1f', $output). " megs: $outputcharge";
233 + push @$details, "Excess upload ".
234 + sprintf('%.1f', $output). " megs: $money_char$outputcharge";
236 if ( $self->option('recur_hourly_charge') > 0 ) {
237 - push @$details, "Last month\'s time ".
238 - sprintf('%.1f', $hours). " hours: $hourscharge";
239 + push @$details, "Excess time ".
240 + sprintf('%.1f', $hours). " hours: $money_char$hourscharge";
243 $self->option('recur_flat')
244 diff -Naur freeside-1.5.7.orig/httemplate/view/svc_acct.cgi freeside-1.5.7/httemplate/view/svc_acct.cgi
245 --- freeside-1.5.7.orig/httemplate/view/svc_acct.cgi 2005-06-08 05:03:06.000000000 -0400
246 +++ freeside-1.5.7/httemplate/view/svc_acct.cgi 2005-09-08 16:10:06.000000000 -0400
251 - my $seconds = $svc_acct->seconds_since_sqlradacct( $last_bill, time );
252 + my %usage = $svc_acct->seconds_since_sqlradacct( $last_bill, time );
254 + foreach (keys %usage) {
255 + $seconds += $usage{$_};
257 my $hour = int($seconds/3600);
258 my $min = int( ($seconds%3600) / 60 );
259 my $sec = $seconds%60;
261 (no billing cycle available for unaudited account)<BR>
264 + <% foreach (sort keys %usage) { %>
265 + <%= $_ %>: <B><%= int($usage{$_}/3600) %></B>h <B><%= int(($usage{$_} % 3600) / 60) %></B>m <B><%= $usage{$_} % 60 %></B>s<BR>
267 Upload: <B><%= sprintf("%.3f", $input) %></B> megabytes<BR>
268 Download: <B><%= sprintf("%.3f", $output) %></B> megabytes<BR>