add internal did database & ability to query for availability, plus upload tool

4 files changed, 360 insertions, 1 deletions

diff --git a/FS/FS/Record.pm b/FS/FS/Record.pm
index 7dea7cb..022f7cd 100644
--- a/FS/FS/Record.pm
+++ b/FS/FS/Record.pm
@@ -11,6 +11,8 @@ use Carp qw(carp cluck croak confess);
 use Scalar::Util qw( blessed );
 use File::CounterFile;
 use Locale::Country;
+use Text::CSV_XS;
+use File::Slurp qw( slurp );
 use DBI qw(:sql_types);
 use DBIx::DBSchema 0.33;
 use FS::UID qw(dbh getotaker datasrc driver_name);
@@ -1294,6 +1296,194 @@ sub check {
   '';
 }
 
+=item batch_import PARAM_HASHREF
+
+Class method for batch imports.  Available params:
+
+=over 4
+
+=item job
+
+FS::queue object, will be updated with progress
+
+=back
+
+=cut
+
+use Storable qw(thaw);
+use Data::Dumper;
+use MIME::Base64;
+sub process_batch_import {
+  my($job, $opt) = ( shift, shift );
+
+  my $table = $opt->{table};
+  my @pass_params = @{ $opt->{params} };
+  my %formats = %{ $opt->{formats} };
+
+  my $param = thaw(decode_base64(shift));
+  warn Dumper($param) if $DEBUG;
+  
+  my $files = $param->{'uploaded_files'}
+    or die "No files provided.\n";
+
+  my (%files) = map { /^(\w+):([\.\w]+)$/ ? ($1,$2):() } split /,/, $files;
+
+  my $dir = '%%%FREESIDE_CACHE%%%/cache.'. $FS::UID::datasrc. '/';
+  my $file = $dir. $files{'file'};
+
+  my $type;
+  if ( $file =~ /\.(\w+)$/i ) {
+    $type = lc($1);
+  } else {
+    #or error out???
+    warn "can't parse file type from filename $file; defaulting to CSV";
+    $type = 'csv';
+  }
+
+  my $error =
+    FS::Record::batch_import( {
+      table     => $table,
+      formats   => \%formats,
+      job       => $job,
+      file      => $file,
+      type      => $type,
+      format    => $param->{format},
+      params    => { map { $_ => $param->{$_} } @pass_params },
+    } );
+
+  unlink $file;
+
+  die "$error\n" if $error;
+}
+
+sub batch_import {
+  my $param = shift;
+
+  my $table     = $param->{table};
+  my $formats   = $param->{formats};
+  my $params    = $param->{params};
+
+  my $job       = $param->{job};
+
+  my $filename  = $param->{file};
+  my $type      = $param->{type} || 'csv';
+
+  my $format = $param->{'format'};
+
+  die "unknown format $format" unless exists $formats->{ $format };
+  my @fields    = @{ $formats->{ $format } };
+
+  my $count;
+  my $parser;
+  my @buffer = ();
+  if ( $type eq 'csv' ) {
+
+    $parser = new Text::CSV_XS;
+
+    @buffer = split(/\r?\n/, slurp($filename) );
+    $count = scalar(@buffer);
+
+  } elsif ( $type eq 'xls' ) {
+
+    eval "use Spreadsheet::ParseExcel;";
+    die $@ if $@;
+
+    my $excel = new Spreadsheet::ParseExcel::Workbook->Parse($filename);
+    $parser = $excel->{Worksheet}[0]; #first sheet
+
+    $count = $parser->{MaxRow} || $parser->{MinRow};
+    $count++;
+
+  } else {
+    die "Unknown file type $type\n";
+  }
+
+  #my $columns;
+
+  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 $line;
+  my $row = 0;
+  my( $last, $min_sec ) = ( time, 5 ); #progressbar foo
+  while (1) {
+
+    my @columns = ();
+    if ( $type eq 'csv' ) {
+
+      last unless scalar(@buffer);
+      $line = shift(@buffer);
+
+      $parser->parse($line) or do {
+        $dbh->rollback if $oldAutoCommit;
+        return "can't parse: ". $parser->error_input();
+      };
+      @columns = $parser->fields();
+
+    } elsif ( $type eq 'xls' ) {
+
+      last if $row > ($parser->{MaxRow} || $parser->{MinRow});
+
+      my @row = @{ $parser->{Cells}[$row] };
+      @columns = map $_->{Val}, @row;
+
+      #my $z = 'A';
+      #warn $z++. ": $_\n" for @columns;
+
+    } else {
+      die "Unknown file type $type\n";
+    }
+
+    my %hash = %$params;
+
+    foreach my $field ( @fields ) {
+
+      my $value = shift @columns;
+     
+      if ( ref($field) eq 'CODE' ) {
+        &{$field}(\%hash, $value);
+      } else {
+        $hash{$field} = $value if length($value);
+      }
+
+    }
+
+    my $class = "FS::$table";
+
+    my $record = $class->new( \%hash );
+
+    my $error = $record->insert;
+
+    if ( $error ) {
+      $dbh->rollback if $oldAutoCommit;
+      return "can't insert record". ( $line ? " for $line" : '' ). ": $error";
+    }
+
+    $row++;
+
+    if ( $job && time - $min_sec > $last ) { #progress bar
+      $job->update_statustext( int(100 * $row / $count) );
+      $last = time;
+    }
+
+  }
+
+  $dbh->commit or die $dbh->errstr if $oldAutoCommit;;
+
+  return "Empty file!" unless $row;
+
+  ''; #no error
+
+}
+
 sub _h_statement {
   my( $self, $action, $time ) = @_;
 
diff --git a/FS/FS/Schema.pm b/FS/FS/Schema.pm
index c0fd466..22bd19e 100644
--- a/FS/FS/Schema.pm
+++ b/FS/FS/Schema.pm
@@ -2112,10 +2112,19 @@ sub tables_hashref {
         'state',       'char', 'NULL',  2, '', '', 
         'npa',         'char',     '',  3, '', '', 
         'nxx',         'char', 'NULL',  3, '', '', 
+        'station',     'char', 'NULL',  4, '', '',
+        'svcnum',      'int',     'NULL',      '', '', '',
+        'availbatch', 'varchar', 'NULL', $char_d, '', '',
       ],
       'primary_key' => 'availnum',
       'unique' => [],
-      'index'  => [ [ 'exportnum', 'countrycode', 'state' ] ],
+      'index'  => [ [ 'exportnum', 'countrycode', 'state' ],     #npa search
+                    [ 'exportnum', 'countrycode', 'npa' ],       #nxx search
+                    [ 'exportnum', 'countrycode', 'npa', 'nxx' ],#station search
+                    [ 'exportnum', 'countrycode', 'npa', 'nxx', 'station' ], # #
+                    [ 'svcnum' ],
+                    [ 'availbatch' ],
+                  ],
     },
 
     'reason_type' => {
diff --git a/FS/FS/part_export/internal_diddb.pm b/FS/FS/part_export/internal_diddb.pm
new file mode 100644
index 0000000..5e14873
--- /dev/null
+++ b/FS/FS/part_export/internal_diddb.pm
@@ -0,0 +1,126 @@
+package FS::part_export::internal_diddb;
+
+use vars qw(@ISA %info);
+#use Tie::IxHash;
+use FS::Record qw(qsearch qsearchs);
+use FS::part_export;
+use FS::phone_avail;
+
+@ISA = qw(FS::part_export);
+
+%info = (
+  'svc'   => 'svc_phone',
+  'desc'  => 'Provision phone numbers from the internal DID database',
+  'notes' => 'After adding the export, DIDs may be imported under Tools -> Importing -> Import phone numbers (DIDs)',
+);
+
+sub rebless { shift; }
+
+sub get_dids {
+  my $self = shift;
+  my %opt = ref($_[0]) ? %{$_[0]} : @_;
+
+  my %hash = ( 'countrycode' => 1, #XXX make an option or something
+               'exportnum'   => $self->exportnum,
+               'svcnum'      => '',
+             );
+
+  if ( $opt{'areacode'} && $opt{'exchange'} ) { #return numbers
+
+    $hash{npa} = $opt{areacode};
+    $hash{nxx} = $opt{exchange};
+
+    return [ map { $_->npa. '-'. $_->nxx. '-'. $_->station }
+                 qsearch({ 'table'    => 'phone_avail',
+                           'hashref'  => \%hash,
+                           'order_by' => 'ORDER BY station',
+                        })
+           ];
+
+  } elsif ( $opt{'areacode'} ) { #return city (npa-nxx-XXXX)
+
+    $hash{npa} = $opt{areacode};
+
+    return [ map { '('. $_->npa. '-'. $_->nxx. '-XXXX)' }
+                 qsearch({ 'select'   => 'DISTINCT npa, nxx',
+                           'table'    => 'phone_avail',
+                           'hashref'  => \%hash,
+                           'order_by' => 'ORDER BY nxx',
+                        })
+           ];
+
+  } elsif ( $opt{'state'} ) { #return aracodes
+
+    $hash{state} = $opt{state};
+
+    return [ map { $_->npa }
+                 qsearch({ 'select'   => 'DISTINCT npa',
+                           'table'    => 'phone_avail',
+                           'hashref'  => \%hash,
+                           'order_by' => 'ORDER BY npa',
+                        })
+           ];
+
+  } else { 
+    die "FS::part_export::internal_diddb::get_dids called without options\n";
+  }
+
+}
+
+sub _export_insert   { #link phone_avail to svcnum
+  my( $self, $svc_phone ) = (shift, shift);
+
+  $svc_phone =~ /^(\d{3})(\d{3})(\d+)$/
+    or return "unparsable phone number: ". $svc_phone->phonenum;
+  my( $npa, $nxx, $station ) = ($1, $2, $3);
+
+  my $phone_avail = qsearchs('phone_avail', {
+    'countrycode' => 1, #XXX make an option or something
+    'exportnum'   => $self->exportnum,
+    'svcnum'      => '',
+    'npa'         => $npa,
+    'nxx'         => $nxx,
+    'station'     => $station,
+  });
+
+  return "number not available: ". $svc_phone->phonenum
+    unless $phone_avail;
+
+  $phone_avail->svcnum($svc_phone->svcnum);
+
+  $phone_avail->replace;
+
+}
+
+sub _export_delete   { #unlink phone_avail from svcnum
+  my( $self, $svc_phone ) = (shift, shift);
+
+  $svc_phone =~ /^(\d{3})(\d{3})(\d+)$/
+    or return "unparsable phone number: ". $svc_phone->phonenum;
+  my( $npa, $nxx, $station ) = ($1, $2, $3);
+
+  my $phone_avail = qsearchs('phone_avail', {
+    'countrycode' => 1, #XXX make an option or something
+    'exportnum'   => $self->exportnum,
+    'svcnum'      => $svc_phone->svcnum,
+    #these too?
+    'npa'         => $npa,
+    'nxx'         => $nxx,
+    'station'     => $station,
+  });
+
+  return "can't find number to return to availability: ". $svc_phone->phonenum
+    unless $phone_avail;
+
+  $phone_avail->svcnum('');
+
+  $phone_avail->replace;
+
+}
+
+sub _export_replace  { ''; }
+sub _export_suspend  { ''; }
+sub _export_unsuspend  { ''; }
+
+1;
+
diff --git a/FS/FS/phone_avail.pm b/FS/FS/phone_avail.pm
index 68aeca1..136fc24 100644
--- a/FS/FS/phone_avail.pm
+++ b/FS/FS/phone_avail.pm
@@ -57,6 +57,18 @@ npa
 
 nxx
 
+=item station
+
+station
+
+=item svcnum
+
+svcnum
+
+=item availbatch
+
+availbatch
+
 =back
 
 =head1 METHODS
@@ -123,12 +135,34 @@ sub check {
     || $self->ut_alphan('state')
     || $self->ut_number('npa')
     || $self->ut_numbern('nxx')
+    || $self->ut_numbern('station')
+    || $self->ut_foreign_keyn('svcnum', 'cust_svc', 'svcnum' )
+    || $self->ut_textn('availbatch')
   ;
   return $error if $error;
 
   $self->SUPER::check;
 }
 
+sub process_batch_import {
+  my $job = shift;
+
+  my $numsub = sub {
+    my( $hash, $value ) = @_;
+    $value =~ s/\D//g;
+    $value =~ /^(\d{3})(\d{3})(\d+)$/ or die "unparsable number $value\n";
+    ( $hash->{npa}, $hash->{nxx}, $hash->{station} ) = ( $1, $2, $3 );
+  };
+
+  my $opt = { 'table'   => 'phone_avail',
+              'params'  => [ 'availbatch', 'exportnum', 'countrycode' ],
+              'formats' => { 'default' => [ 'state', $numsub ] },
+            };
+
+  FS::Record::process_batch_import( $job, $opt, @_ );
+
+}
+
 =back
 
 =head1 BUGS