FS/FS/msa.pm

7 files changed, 268 insertions, 24 deletions

diff --git a/FS/FS/Schema.pm b/FS/FS/Schema.pm
index c9775fca8..37660f20b 100644
--- a/FS/FS/Schema.pm
+++ b/FS/FS/Schema.pm
@@ -3125,6 +3125,26 @@ sub tables_hashref {
       'index'  => [],
     },
     
+    'msa' => {
+      'columns' => [
+        'msanum',    'int',      '',      '', '', '', 
+        'description',   'varchar',    '',      $char_d, '', '', 
+      ],
+      'primary_key' => 'msanum',
+      'unique' => [],
+      'index'  => [],
+    },
+    
+    'rate_center' => {
+      'columns' => [
+        'ratecenternum',    'serial',      '',      '', '', '', 
+        'description',   'varchar',    '',      $char_d, '', '', 
+      ],
+      'primary_key' => 'ratecenternum',
+      'unique' => [],
+      'index'  => [],
+    },
+
     'did_vendor' => {
       'columns' => [
         'vendornum',    'serial',      '',      '', '', '', 
@@ -3139,10 +3159,10 @@ sub tables_hashref {
       'columns' => [
         'orderitemnum',    'serial',      '',      '', '', '', 
         'ordernum',    'int',      '',      '', '', '', 
-        'msa',        'varchar', 'NULL', $char_d, '', '',
+        'msanum',      'int',     'NULL',      '', '', '',
         'npa',      'int',     'NULL',      '', '', '',
         'latanum',      'int',     'NULL',      '', '', '',
-        'ratecenter',        'varchar', 'NULL', $char_d, '', '',
+        'ratecenternum',      'int',     'NULL',      '', '', '',
         'state',       'char',    'NULL',       2, '', '', 
         'quantity',      'int',     '',      '', '', '',
       ],
diff --git a/FS/FS/did_order.pm b/FS/FS/did_order.pm
index adc965f91..b138192f8 100644
--- a/FS/FS/did_order.pm
+++ b/FS/FS/did_order.pm
@@ -2,7 +2,7 @@ package FS::did_order;
 
 use strict;
 use base qw( FS::o2m_Common FS::Record );
-use FS::Record qw( qsearch qsearchs );
+use FS::Record qw( qsearch qsearchs dbh );
 
 =head1 NAME
 
@@ -89,7 +89,43 @@ Delete this record from the database.
 
 =cut
 
-# the delete method can be inherited from FS::Record
+sub delete {
+  my $self = shift;
+
+  return "Can't delete a DID order which has DIDs received"
+    if qsearch( 'phone_avail', { 'ordernum' => $self->ordernum } );
+
+  local $SIG{HUP} = 'IGNORE';
+  local $SIG{INT} = 'IGNORE';
+  local $SIG{QUIT} = 'IGNORE';
+  local $SIG{TERM} = 'IGNORE';
+  local $SIG{TSTP} = 'IGNORE';
+  local $SIG{PIPE} = 'IGNORE';
+
+  my $oldAutoCommit = $FS::UID::AutoCommit;
+  local $FS::UID::AutoCommit = 0;
+  my $dbh = dbh;
+
+  my @did_order_item = $self->did_order_item;
+
+  foreach my $did_order_item ( @did_order_item ) {
+    my $error = $did_order_item->delete;
+    if ( $error ) {
+      $dbh->rollback if $oldAutoCommit;
+      return "can't delete DID order item "
+                                . $did_order_item->orderitemnum . ": $error";
+    }
+  }
+
+  my $error = $self->SUPER::delete(@_);
+  if ( $error ) {
+    $dbh->rollback if $oldAutoCommit;
+    return $error;
+  }
+
+  $dbh->commit or die $dbh->errstr if $oldAutoCommit;
+}
+
 
 =item replace OLD_RECORD
 
diff --git a/FS/FS/did_order_item.pm b/FS/FS/did_order_item.pm
index 4408c50f5..c2d657ad8 100644
--- a/FS/FS/did_order_item.pm
+++ b/FS/FS/did_order_item.pm
@@ -37,33 +37,18 @@ primary key
 
 =item ordernum
 
-ordernum
-
-=item msa
-
-msa
+=item msanum - foreign key to msa table
 
 =item npa
 
-npa
-
-=item latanum
-
-latanum
+=item latanum - foreign key to lata table
 
-=item rate_center
-
-rate_center
+=item ratecenternum - foreign key to rate_center table
 
 =item state
 
-state
-
 =item quantity
 
-quantity
-
-
 =back
 
 =head1 METHODS
@@ -126,10 +111,10 @@ sub check {
   my $error = 
     $self->ut_numbern('orderitemnum')
     || $self->ut_number('ordernum')
-    || $self->ut_textn('msa')
+    || $self->ut_foreign_keyn('msanum', 'msa', 'msanum')
     || $self->ut_numbern('npa')
     || $self->ut_foreign_keyn('latanum', 'lata', 'latanum')
-    || $self->ut_textn('rate_center')
+    || $self->ut_foreign_keyn('ratecenternum', 'rate_center', 'ratecenternum')
     || $self->ut_textn('state')
     || $self->ut_number('quantity')
   ;
diff --git a/FS/FS/rate_center.pm b/FS/FS/rate_center.pm
new file mode 100644
index 000000000..d566b63a4
--- /dev/null
+++ b/FS/FS/rate_center.pm
@@ -0,0 +1,119 @@
+package FS::rate_center;
+
+use strict;
+use base qw( FS::Record );
+use FS::Record qw( qsearch qsearchs );
+
+=head1 NAME
+
+FS::rate_center - Object methods for rate_center records
+
+=head1 SYNOPSIS
+
+  use FS::rate_center;
+
+  $record = new FS::rate_center \%hash;
+  $record = new FS::rate_center { 'column' => 'value' };
+
+  $error = $record->insert;
+
+  $error = $new_record->replace($old_record);
+
+  $error = $record->delete;
+
+  $error = $record->check;
+
+=head1 DESCRIPTION
+
+An FS::rate_center object represents an rate center.  FS::rate_center inherits from
+FS::Record.  The following fields are currently supported:
+
+=over 4
+
+=item ratecenternum
+
+primary key
+
+=item description
+
+description
+
+
+=back
+
+=head1 METHODS
+
+=over 4
+
+=item new HASHREF
+
+Creates a new rate center.  To add the rate center to the database, see L<"insert">.
+
+Note that this stores the hash reference, not a distinct copy of the hash it
+points to.  You can ask the object for a copy with the I<hash> method.
+
+=cut
+
+# the new method can be inherited from FS::Record, if a table method is defined
+
+sub table { 'rate_center'; }
+
+=item insert
+
+Adds this record to the database.  If there is an error, returns the error,
+otherwise returns false.
+
+=cut
+
+# the insert method can be inherited from FS::Record
+
+=item delete
+
+Delete this record from the database.
+
+=cut
+
+# the delete method can be inherited from FS::Record
+
+=item replace OLD_RECORD
+
+Replaces the OLD_RECORD with this one in the database.  If there is an error,
+returns the error, otherwise returns false.
+
+=cut
+
+# the replace method can be inherited from FS::Record
+
+=item check
+
+Checks all fields to make sure this is a valid rate center.  If there is
+an error, returns the error, otherwise returns false.  Called by the insert
+and replace methods.
+
+=cut
+
+# the check method should currently be supplied - FS::Record contains some
+# data checking routines
+
+sub check {
+  my $self = shift;
+
+  my $error = 
+    $self->ut_numbern('ratecenternum')
+    || $self->ut_text('description')
+  ;
+  return $error if $error;
+
+  $self->SUPER::check;
+}
+
+=back
+
+=head1 SEE ALSO
+
+L<FS::Record>, schema.html from the base documentation.
+
+=cut
+
+1;
+
diff --git a/FS/bin/freeside-msa-import b/FS/bin/freeside-msa-import
new file mode 100755
index 000000000..ade3efab9
--- /dev/null
+++ b/FS/bin/freeside-msa-import
@@ -0,0 +1,74 @@
+#!/usr/bin/perl -w
+
+use strict;
+use Getopt::Std;
+use FS::UID qw(adminsuidsetup);
+use FS::Conf;
+use FS::Record qw(qsearch qsearchs dbh);
+use LWP::Simple;
+use Data::Dumper;
+
+&untaint_argv;	#what it sounds like  (eww)
+use vars qw(%opt);
+
+my $user = shift or die &usage;
+my $dbh = adminsuidsetup $user;
+
+my $content = get("http://www.census.gov/population/www/metroareas/lists/2009/List1.txt");
+my @content = split(/\n/,$content);
+
+my $sql = 'insert into msa (msanum, description) values ';
+my @sql;
+foreach my $row ( @content ) {
+    next unless $row =~ /^([0-9]{5})\s+([A-Za-z, \-]{5,80}) .{3}ropolitan Statistical Area/;
+    push @sql, "( $1, '$2')";
+}
+$sql .= join(',',@sql);
+
+my $sth = $dbh->prepare('delete from msa');
+$sth->execute or die $sth->errstr;
+
+$sth = $dbh->prepare($sql);
+$sth->execute or die $sth->errstr;
+
+$dbh->commit;
+
+###
+# subroutines
+###
+
+sub untaint_argv {
+  foreach $_ ( $[ .. $#ARGV ) { #untaint @ARGV
+    #$ARGV[$_] =~ /^([\w\-\/]*)$/ || die "Illegal arguement \"$ARGV[$_]\"";
+    # Date::Parse
+    $ARGV[$_] =~ /^(.*)$/ || die "Illegal arguement \"$ARGV[$_]\"";
+    $ARGV[$_]=$1;
+  }
+}
+
+sub usage {
+  die "Usage:\n  freeside-msa-import user \n";
+}
+
+###
+# documentation
+###
+
+=head1 NAME
+
+freeside-msa-import - Pull MSA data from census.gov and insert into MSA table
+
+=head1 SYNOPSIS
+
+  freeside-msa-import user
+
+=head1 DESCRIPTION
+
+user - name of an internal Freeside user
+
+=head1 SEE ALSO
+
+L<FS::msa>
+
+=cut
+
diff --git a/FS/t/msa.t b/FS/t/msa.t
new file mode 100644
index 000000000..84b603300
--- /dev/null
+++ b/FS/t/msa.t
@@ -0,0 +1,5 @@
+BEGIN { $| = 1; print "1..1\n" }
+END {print "not ok 1\n" unless $loaded;}
+use FS::msa;
+$loaded=1;
+print "ok 1\n";
diff --git a/FS/t/rate_center.t b/FS/t/rate_center.t
new file mode 100644
index 000000000..8b272d43f
--- /dev/null
+++ b/FS/t/rate_center.t
@@ -0,0 +1,5 @@
+BEGIN { $| = 1; print "1..1\n" }
+END {print "not ok 1\n" unless $loaded;}
+use FS::rate_center;
+$loaded=1;
+print "ok 1\n";