first pass at VoIP rating

author: ivan <ivan> 2004-11-20 17:26:56 +0000
committer: ivan <ivan> 2004-11-20 17:26:56 +0000
commit: 48ba2845d0119c56971d5b724661aa37e73b49dd (patch)
tree: f5b2946aec0b5ee6910c97e88aa9112cec50a405 /httemplate
parent: 521e7787b230669dc10e6fbd909e70ddb8121990 (diff)
10 files changed, 459 insertions, 15 deletions
diff --git a/httemplate/browse/rate.cgi b/httemplate/browse/rate.cgi
new file mode 100644
index 000000000..c31260166
--- /dev/null
+++ b/httemplate/browse/rate.cgi
@@ -0,0 +1,34 @@
+<!-- mason kludge -->
+<%= header("Rate plan listing", menubar( 'Main Menu' => "$p#sysadmin" )) %>
+Rate plans, regions and prefixes for VoIP and call billing.<BR><BR>
+<A HREF="<%=$p%>edit/rate.cgi"><I>Add a rate plan</I></A>
+| <A HREF="<%=$p%>edit/rate_region.cgi"><I>Add a region</I></A>
+<BR><BR>
+<SCRIPT>
+function rate_areyousure(href) {
+  if (confirm("Are you sure you want to delete this rate plan?") == true)
+    window.location.href = href;
+}
+</SCRIPT>
+
+<%= table() %>
+  <TR>
+    <TH COLSPAN=2>Rate plan</TH>
+  </TR>
+
+<% foreach my $rate ( sort { 
+     $a->getfield('ratenum') <=> $b->getfield('ratenum')
+   } qsearch('rate',{}) ) {
+%>
+  <TR>
+    <TD><A HREF="<%= $p %>edit/rate.cgi?<%= $rate->ratenum %>"><%= $rate->ratenum %></A></TD>
+    <TD><A HREF="<%= $p %>edit/rate.cgi?<%= $rate->ratenum %>"><%= $rate->ratename %></A></TD>
+  </TR>
+
+<% } %>
+
+</TABLE>
+</BODY>
+</HTML>
+
+ 
diff --git a/httemplate/docs/schema.html b/httemplate/docs/schema.html
index 8523a4a79..2e78f6e9e 100644
--- a/httemplate/docs/schema.html
+++ b/httemplate/docs/schema.html
@@ -1,4 +1,4 @@
-<head>
+
   <title>Schema reference</title>
 </head>
 <body>
@@ -453,6 +453,33 @@
         <li>svcnum - <a href="#svc_acct">account</a>
         <li>groupname
       </ul>
+    <li><a name="rate" href="man/FS/rate.html">rate</a> - Call rate plans
+      <ul>
+        <li>ratenum - primary key
+        <li>ratename
+      </ul>
+    <li><a name="rate_detail" href="man/FS/rate_detail.html">rate_detail</a> - Call rate detail
+      <ul>
+        <li>ratenum - <a href="#rate">rate plan</a>
+        <li>orig_regionnum - call origination <a href="#rate_region">region</a>
+        <li>dest_regionnum - call destination <a href="#rate_region">region</a>
+        <li>min_included - included minutes
+        <li>min_charge - charge per minute
+        <li>sec_granularity - granularity in seconds, i.e. 6 or 60
+      </ul>
+    <li><a name="rate_region" href="man/FS/rate_region.html">rate_region</a> - Call rate region
+      <ul>
+        <li>regionnum - primary key
+        <li>regionname
+      </ul>
+    <li><a name="rate_prefix" href="man/FS/rate_prefix.html">rate_prefix</a> - Call rate prefix
+      <ul>
+        <li>prefixnum - primary key
+        <li>regionnum <a href="#rate_region">rate region</a>
+        <li>countrycode
+        <li>npa
+        <li>nxx
+      </ul>
     <li><a name="msgcat" href="man/FS/msgcat.html">msgcat</a> - i18n message catalog
       <ul>
         <li>msgnum - primary key
diff --git a/httemplate/docs/upgrade10.html b/httemplate/docs/upgrade10.html
index 2f2c2daa6..e17f7add3 100644
--- a/httemplate/docs/upgrade10.html
+++ b/httemplate/docs/upgrade10.html
@@ -190,6 +190,39 @@ CREATE TABLE part_pkg_option (
 CREATE INDEX part_pkg_option1 ON part_export_option ( pkgpart );
 CREATE INDEX part_pkg_option2 ON part_export_option ( optionname );
 
+CREATE TABLE rate (
+    ratenum serial NOT NULL,
+    reatename varchar(80) NOT NULL,
+    PRIMARY KEY (ratenum)
+);
+
+CREATE TABLE rate_detail (
+    ratenum int NOT NULL,
+    orig_regionnum int NULL,
+    dest_regionnum int NOT NULL,
+    min_included int NOT NULL,
+    min_charge decimal(10,2) NOT NULL,
+    sec_granularity int NOT NULL
+);
+CREATE UNIQUE INDEX rate_detail1 ON rate_detail ( ratenum, orig_regionnum, dest_regionnum );
+
+CREATE TABLE rate_region (
+    regionnum serial NOT NULL,
+    regionname varchar(80) NOT NULL,
+    PRIMARY KEY (regionnum)
+);
+
+CREATE TABLE rate_prefix (
+    prefixnum serial NOT NULL,
+    regionnum int NOT NULL, 
+    countrycode varchar(3) NOT NULL,
+    npa varchar(4) NULL,
+    nxx varchar(3) NULL,
+    PRIMARY KEY (prefixnum)
+);
+CREATE INDEX rate_prefix1 ON rate_prefix ( countrycode );
+CREATE INDEX rate_prefix2 ON rate_prefix ( regionnum );
+
 DROP INDEX cust_bill_pkg1;
 
 ALTER TABLE cust_bill_pkg ADD itemdesc varchar(80) NULL;
@@ -256,7 +289,7 @@ optionally:
 mandatory again:
 
 dbdef-create username
-create-history-tables username cust_bill_pkg_detail router part_svc_router addr_block svc_broadband acct_snarf svc_external cust_pay_refund cust_pay_void
+create-history-tables username cust_bill_pkg_detail router part_svc_router addr_block svc_broadband acct_snarf svc_external cust_pay_refund cust_pay_void part_pkg_option rate rate_detail rate_region rate_prefix
 dbdef-create username
 
 apache - fix <Files> sections to include .html also
diff --git a/httemplate/edit/part_pkg.cgi b/httemplate/edit/part_pkg.cgi
index 6a06c3525..dc2992459 100755
--- a/httemplate/edit/part_pkg.cgi
+++ b/httemplate/edit/part_pkg.cgi
@@ -295,8 +295,11 @@ my $widget = new HTML::Widgets::SelectLayers(
                      ? $plandata{$field}
                      : $href->{$field}{'default'} ).
                  qq!" onChange="fchanged(this)">!;
-      } elsif ( $href->{$field}{'type'} eq 'select_multiple' ) {
-        $html .= qq!<SELECT MULTIPLE NAME="$field" onChange="fchanged(this)">!;
+      } elsif ( $href->{$field}{'type'} =~ /^select/ ) {
+        $html .= '<SELECT';
+        $html .= ' MULTIPLE'
+          if $href->{$field}{'type'} eq 'select_multiple';
+        $html .= qq! NAME="$field" onChange="fchanged(this)">!;
         foreach my $record (
           qsearch( $href->{$field}{'select_table'},
                    $href->{$field}{'select_hash'}   )
diff --git a/httemplate/edit/process/rate.cgi b/httemplate/edit/process/rate.cgi
new file mode 100755
index 000000000..04ff5f8f7
--- /dev/null
+++ b/httemplate/edit/process/rate.cgi
@@ -0,0 +1,37 @@
+<%
+
+my $ratenum = $cgi->param('ratenum');
+
+my $old = qsearchs('rate', { 'ratenum' => $ratenum } ) if $ratenum;
+
+my @rate_detail = map {
+  my $regionnum = $_->regionnum;
+  new FS::rate_detail {
+    'dest_regionnum'  => $regionnum,
+    map { $_ => $cgi->param("$_$regionnum") }
+        qw( min_included min_charge sec_granularity )
+  };
+} qsearch('rate_region', {} );
+
+my $new = new FS::rate ( {
+  map {
+    $_, scalar($cgi->param($_));
+  } fields('rate')
+} );
+
+my $error;
+if ( $ratenum ) {
+  $error = $new->replace($old, 'rate_detail' => \@rate_detail );
+} else {
+  $error = $new->insert( 'rate_detail' => \@rate_detail );
+  $ratenum = $new->getfield('ratenum');
+}
+
+if ( $error ) {
+  $cgi->param('error', $error);
+  print $cgi->redirect(popurl(2). "rate.cgi?". $cgi->query_string );
+} else { 
+  print $cgi->redirect(popurl(3). "browse/rate.cgi");
+}
+
+%>
diff --git a/httemplate/edit/process/rate_region.cgi b/httemplate/edit/process/rate_region.cgi
new file mode 100755
index 000000000..09d3d2c42
--- /dev/null
+++ b/httemplate/edit/process/rate_region.cgi
@@ -0,0 +1,51 @@
+<%
+
+my $regionnum = $cgi->param('regionnum');
+
+my $old = qsearchs('rate_region', { 'regionnum' => $regionnum } ) if $regionnum;
+
+my $new = new FS::rate_region ( {
+  map {
+    $_, scalar($cgi->param($_));
+  } ( fields('rate_region') )
+} );
+
+my $countrycode = $cgi->param('countrycode');
+my @npa = split(/\s*,\s*/, $cgi->param('npa'));
+$npa[0] = '' unless @npa;
+my @rate_prefix = map {
+                        new FS::rate_prefix {
+                          'countrycode' => $countrycode,
+                          'npa'         => $_,
+                        }
+                      } @npa;
+
+my @dest_detail = map {
+  my $ratenum = $_->ratenum;
+  new FS::rate_detail {
+    'ratenum'  => $ratenum,
+    map { $_ => $cgi->param("$_$ratenum") }
+        qw( min_included min_charge sec_granularity )
+  };
+} qsearch('rate', {} );
+
+
+my $error;
+if ( $regionnum ) {
+  $error = $new->replace($old, 'rate_prefix' => \@rate_prefix,
+                               'dest_detail' => \@dest_detail, );
+} else {
+  $error = $new->insert( 'rate_prefix' => \@rate_prefix,
+                         'dest_detail' => \@dest_detail, );
+  $regionnum = $new->getfield('regionnum');
+}
+
+if ( $error ) {
+  $cgi->param('error', $error);
+  print $cgi->redirect(popurl(2). "rate_region.cgi?". $cgi->query_string );
+} else { 
+  #print $cgi->redirect(popurl(3). "browse/rate_region.cgi");
+  print $cgi->redirect(popurl(3). "browse/rate.cgi");
+}
+
+%>
diff --git a/httemplate/edit/rate.cgi b/httemplate/edit/rate.cgi
new file mode 100644
index 000000000..83a89c475
--- /dev/null
+++ b/httemplate/edit/rate.cgi
@@ -0,0 +1,94 @@
+<!-- mason kludge -->
+<%
+
+my $rate;
+if ( $cgi->param('error') ) {
+  $rate = new FS::rate ( {
+    map { $_, scalar($cgi->param($_)) } fields('rate')
+  } );
+} elsif ( $cgi->keywords ) {
+  my($query) = $cgi->keywords;
+  $query =~ /^(\d+)$/;
+  $rate = qsearchs( 'rate', { 'ratenum' => $1 } );
+} else { #adding
+  $rate = new FS::rate {};
+}
+my $action = $rate->ratenum ? 'Edit' : 'Add';
+
+my $p1 = popurl(1);
+
+my %granularity = (
+  '6'  => '6 second',
+  '60' => 'minute',
+);
+
+%>
+
+<%= header("$action Rate plan", menubar(
+      'Main Menu' => $p,
+      'View all rate plans' => "${p}browse/rate.cgi",
+    ))
+%>
+
+<% if ( $cgi->param('error') ) { %>
+<FONT SIZE="+1" COLOR="#ff0000">Error: <%= $cgi->param('error') %></FONT><BR>
+<% } %>
+
+<FORM ACTION="<%=$p1%>process/rate.cgi" METHOD=POST>
+
+<INPUT TYPE="hidden" NAME="ratenum" VALUE="<%= $rate->ratenum %>">
+
+Rate plan
+<INPUT TYPE="text" NAME="ratename" SIZE=32 VALUE="<%= $rate->ratename %>">
+<BR><BR>
+
+<%= table() %>
+<TR>
+  <TH>Region</TH>
+  <TH>Prefix(es)</TH>
+  <TH><FONT SIZE=-1>Included<BR>minutes</FONT></TH>
+  <TH><FONT SIZE=-1>Charge per<BR>minute</FONT></TH>
+  <TH><FONT SIZE=-1>Granularity</FONT></TH>
+</TR>
+
+<% foreach my $rate_region (
+     qsearch('rate_region', {}, '', 'ORDER BY regionname' )
+   ) {
+     my $n = $rate_region->regionnum;
+     my $rate_detail =
+       $rate->dest_detail($rate_region)
+       || new FS::rate_region { 'min_included'    => 0,
+                                'min_charge'      => 0,
+                                'sec_granularity' => '60'
+                              };
+%>
+  <TR>
+    <TD><A HREF="<%=$p%>edit/rate_region.cgi?<%= $rate_region->regionnum %>"><%= $rate_region->regionname %></A></TD>
+    <TD><%= $rate_region->prefixes_short %></TD>
+    <TD><INPUT TYPE="text" SIZE=5 NAME="min_included<%=$n%>" VALUE="<%= $cgi->param("min_included$n") || $rate_detail->min_included %>"></TD>
+    <TD>$<INPUT TYPE="text" SIZE=4 NAME="min_charge<%=$n%>" VALUE="<%= sprintf('%.2f', $cgi->param("min_charge$n") || $rate_detail->min_charge ) %>"></TD>
+    <TD>
+      <SELECT NAME="sec_granularity<%=$n%>">
+        <% foreach my $granularity ( keys %granularity ) { %>
+          <OPTION VALUE="<%=$granularity%>"<%= $granularity == ( $cgi->param("sec_granularity$n") || $rate_detail->sec_granularity ) ? ' SELECTED' : '' %>><%=$granularity{$granularity}%>
+        <% } %>
+      </SELECT>
+  </TR>
+<% } %>
+
+<TR>
+  <TD COLSPAN=5 ALIGN="center">
+    <A HREF="<%=$p%>edit/rate_region.cgi"><I>Add a region</I></A>
+  </TD>
+</TR>
+
+</TABLE>
+
+<BR><INPUT TYPE="submit" VALUE="<%= 
+  $rate->ratenum ? "Apply changes" : "Add rate plan"
+%>">
+
+    </FORM>
+  </BODY>
+</HTML>
+
diff --git a/httemplate/edit/rate_region.cgi b/httemplate/edit/rate_region.cgi
new file mode 100644
index 000000000..cc14dd37d
--- /dev/null
+++ b/httemplate/edit/rate_region.cgi
@@ -0,0 +1,114 @@
+<!-- mason kludge -->
+<%
+
+my $rate_region;
+if ( $cgi->param('error') ) {
+  $rate_region = new FS::rate_region ( {
+    map { $_, scalar($cgi->param($_)) } fields('rate_region')
+  } );
+} elsif ( $cgi->keywords ) {
+  my($query) = $cgi->keywords;
+  $query =~ /^(\d+)$/;
+  $rate_region = qsearchs( 'rate_region', { 'regionnum' => $1 } );
+} else { #adding
+  $rate_region = new FS::rate_region {};
+}
+my $action = $rate_region->regionnum ? 'Edit' : 'Add';
+
+my $p1 = popurl(1);
+
+my %granularity = (
+  '6'  => '6 second',
+  '60' => 'minute',
+);
+
+my @rate_prefix = $rate_region->rate_prefix;
+my $countrycode = '';
+if ( @rate_prefix ) {
+  $countrycode = $rate_prefix[0]->countrycode;
+  foreach my $rate_prefix ( @rate_prefix ) {
+    eidiot 'multiple country codes per region not yet supported by web UI'
+      unless $rate_prefix->countrycode eq $countrycode;
+  }
+}
+
+%>
+
+<%= header("$action Region", menubar(
+      'Main Menu' => $p,
+      #'View all regions' => "${p}browse/rate_region.cgi",
+    ))
+%>
+
+<% if ( $cgi->param('error') ) { %>
+<FONT SIZE="+1" COLOR="#ff0000">Error: <%= $cgi->param('error') %></FONT><BR>
+<% } %>
+
+<FORM ACTION="<%=$p1%>process/rate_region.cgi" METHOD=POST>
+
+<INPUT TYPE="hidden" NAME="regionnum" VALUE="<%= $rate_region->regionnum %>">
+
+<%= ntable('#cccccc') %>
+<TR>
+  <TH ALIGN="right">Region name</TH>
+  <TD><INPUT TYPE="text" NAME="regionname" SIZE=32 VALUE="<%= $rate_region->regionname %>"></TR>
+</TR>
+
+<TR>
+  <TH ALIGN="right">Country code</TH>
+  <TD><INPUT TYPE="text" NAME="countrycode" SIZE=4 MAXLENGTH=3 VALUE="<%= $countrycode %>"></TR>
+</TR>
+
+
+<TR>
+  <TH ALIGN="right">Prefixes</TH>
+  <TD>
+    <TEXTAREA NAME="npa" WRAP=SOFT><%= join(', ', map $_->npa, @rate_prefix ) %></TEXTAREA>
+  </TD>
+</TR>
+
+</TABLE>
+
+<BR>
+<%= table() %>
+<TR>
+  <TH>Rate plan</TH>
+  <TH><FONT SIZE=-1>Included<BR>minutes</FONT></TH>
+  <TH><FONT SIZE=-1>Charge per<BR>minute</FONT></TH>
+  <TH><FONT SIZE=-1>Granularity</FONT></TH>
+</TR>
+
+<% foreach my $rate ( qsearch('rate', {}) ) {
+
+  my $n = $rate->ratenum;
+  my $rate_detail = $rate->dest_detail($rate_region)
+                    || new FS::rate_region { 'min_included'    => 0,
+                                             'min_charge'      => 0,
+                                             'sec_granularity' => '60'
+                                           };
+
+%>
+  <TR>
+    <TD><A HREF="<%=$p%>edit/rate.cgi?<%= $rate->ratenum %>"><%= $rate->ratename %></TD>
+    <TD><INPUT TYPE="text" SIZE=5 NAME="min_included<%=$n%>" VALUE="<%= $cgi->param("min_included$n") || $rate_detail->min_included %>"></TD>
+    <TD>$<INPUT TYPE="text" SIZE=4 NAME="min_charge<%=$n%>" VALUE="<%= sprintf('%.2f', $cgi->param("min_charge$n") || $rate_detail->min_charge ) %>"></TD>
+    <TD>
+      <SELECT NAME="sec_granularity<%=$n%>">
+        <% foreach my $granularity ( keys %granularity ) { %>
+          <OPTION VALUE="<%=$granularity%>"<%= $granularity == ( $cgi->param("sec_granularity$n") || $rate_detail->sec_granularity ) ? ' SELECTED' : '' %>><%=$granularity{$granularity}%>
+        <% } %>
+      </SELECT>
+  </TR>
+<% } %>
+
+</TABLE>
+
+<BR><BR><INPUT TYPE="submit" VALUE="<%= 
+  $rate_region->regionnum ? "Apply changes" : "Add region"
+%>">
+
+    </FORM>
+  </BODY>
+</HTML>
+
+
diff --git a/httemplate/search/sqlradius.cgi b/httemplate/search/sqlradius.cgi
index b506ba1cb..9e4a55e62 100644
--- a/httemplate/search/sqlradius.cgi
+++ b/httemplate/search/sqlradius.cgi
@@ -51,6 +51,15 @@
     $ip = $1;
   }
 
+  my $prefix = $cgi->param('prefix');
+  $prefix =~ s/\D//g;
+  if ( $prefix =~ /^(\d+)$/ ) {
+    $prefix = $1;
+    $prefix = "011$prefix" unless $prefix =~ /^1/;
+  } else {
+    $prefix = '';
+  }
+
   ###
   # field formatting subroutines
   ###
@@ -221,11 +230,30 @@
   # and finally, display the thing
   ### 
 
-  foreach my $part_export ( map $_->rebless, 
+  foreach my $part_export (
+    #grep $_->can('usage_sessions'), qsearch( 'part_export' )
     qsearch( 'part_export', { 'exporttype' => 'sqlradius' } ),
     qsearch( 'part_export', { 'exporttype' => 'sqlradius_withdomain' } )
   ) {
     %user2svc_acct = ();
+
+    my $efields = tie my %efields, 'Tie::IxHash', %fields;
+    delete $efields{'framedipaddress'} if $part_export->option('hide_ip');
+    if ( $part_export->option('hide_data') ) {
+      delete $efields{$_} foreach qw(acctinputoctets acctoutputoctets);
+    }
+    if ( $part_export->option('show_called_station') ) {
+      $efields->Splice(1, 0,
+        'calledstationid' => {
+                               'name'   => 
+                               'attrib' => 'Called-Station-ID',
+                               'fmt'    =>
+                                 sub { length($_[0]) ? shift : '&nbsp'; },
+                               'align'  => 'left',
+                             },
+      );
+    }
+
 %>
 
 <%= $part_export->exporttype %> to <%= $part_export->machine %><BR>
@@ -239,8 +267,10 @@
   <% } %>
 </TR>
 <% foreach my $session (
-  @{ $part_export->usage_sessions( $beginning, $ending, $cgi_svc_acct, $ip ) }
-) { %>
+     @{ $part_export->usage_sessions(
+          $beginning, $ending, $cgi_svc_acct, $ip, $prefix, ) }
+   ) {
+%>
   <TR>
     <% foreach my $field ( keys %fields ) { %>
       <TD ALIGN="<%= $fields{$field}->{align} %>">
diff --git a/httemplate/search/sqlradius.html b/httemplate/search/sqlradius.html
index 48a3d8680..f33313f81 100644
--- a/httemplate/search/sqlradius.html
+++ b/httemplate/search/sqlradius.html
@@ -15,14 +15,35 @@
   <TD></TD>
   <TD><FONT SIZE="-1"><I>(leave blank to show all users)</I></FONT></TD>
 </TR>
-<TR>
-  <TD ALIGN="right">IP address: </TD>
-  <TD><INPUT TYPE="text" NAME="ip"></TD>
-</TR>
-<TR>
-  <TD></TD>
-  <TD><FONT SIZE="-1"><I>(leave blank to show all IPs)</I></FONT></TD>
-</TR>
+
+<% my @part_export = qsearch( 'part_export', { 'exporttype' => 'sqlradius' } );
+   push @part_export,
+     qsearch( 'part_export', { 'exporttype' => 'sqlradius_withdomain' } );
+%>
+
+<% if ( grep { ! $_->option('hide_ip') } @part_export ) { %>
+  <TR>
+    <TD ALIGN="right">IP address: </TD>
+    <TD><INPUT TYPE="text" NAME="ip"></TD>
+  </TR>
+  <TR>
+    <TD></TD>
+    <TD><FONT SIZE="-1"><I>(leave blank to show all IPs)</I></FONT></TD>
+  </TR>
+<% } %>
+
+<% if ( grep { $_->option('show_called_station') } @part_export ) { %>
+  <TR>
+    <TD ALIGN="right">Destination prefix:</TD>
+    <TD><INPUT TYPE="text" NAME="prefix"></TD>
+  </TR>
+  <TR>
+    <TD></TD>
+    <TD><FONT SIZE="-1"><I>(country code or country code and prefix)</I></FONT></TD>
+    <TD><FONT SIZE="-1"><I>(leave blank to show all destinations)</I></FONT></TD>
+  </TR>
+<% } %>
+
 <TR>
   <TD ALIGN="right">From: </TD>
   <TD>
author	ivan <ivan>	2004-11-20 17:26:56 +0000
committer	ivan <ivan>	2004-11-20 17:26:56 +0000
commit	48ba2845d0119c56971d5b724661aa37e73b49dd (patch)
tree	f5b2946aec0b5ee6910c97e88aa9112cec50a405 /httemplate
parent	521e7787b230669dc10e6fbd909e70ddb8121990 (diff)