From: ivan <ivan>
Date: Thu, 14 Aug 2008 01:58:31 +0000 (+0000)
Subject: import customer from Excel file too
X-Git-Tag: root_of_webpay_support~437
X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=commitdiff_plain;h=ec5603ae351d4ed8e4873dcd20bf71f8a4d549bb

import customer from Excel file too
---

diff --git a/FS/FS/Conf.pm b/FS/FS/Conf.pm
index 3c4c6eebe..728a2136b 100644
--- a/FS/FS/Conf.pm
+++ b/FS/FS/Conf.pm
@@ -2350,6 +2350,13 @@ worry that config_items is freeside-specific and icky.
     'type'        => 'checkbox',
   },
 
+  {
+    'key'         => 'cust_main-default_areacode',
+    'section'     => 'UI',
+    'description' => 'Default area code for customers.',
+    'type'        => 'text',
+  },
+
 );
 
 1;
diff --git a/FS/FS/Record.pm b/FS/FS/Record.pm
index 8bd57ebff..2540dd399 100644
--- a/FS/FS/Record.pm
+++ b/FS/FS/Record.pm
@@ -1614,6 +1614,8 @@ sub ut_phonen {
     $self->setfield($field,'');
   } elsif ( $country eq 'US' || $country eq 'CA' ) {
     $phonen =~ s/\D//g;
+    $phonen = $conf->config('cust_main-default_areacode').$phonen
+      if length($phonen)==7 && $conf->config('cust_main-default_areacode');
     $phonen =~ /^(\d{3})(\d{3})(\d{4})(\d*)$/
       or return gettext('illegal_phone'). " $field: ". $self->getfield($field);
     $phonen = "$1-$2-$3";
diff --git a/FS/FS/cust_main.pm b/FS/FS/cust_main.pm
index 51270d6dd..e698bfa68 100644
--- a/FS/FS/cust_main.pm
+++ b/FS/FS/cust_main.pm
@@ -16,6 +16,8 @@ use Digest::MD5 qw(md5_base64);
 use Date::Format;
 use Date::Parse;
 #use Date::Manip;
+use File::Slurp qw( slurp );
+use File::Temp qw( tempfile );
 use String::Approx qw(amatch);
 use Business::CreditCard 0.28;
 use Locale::Country;
@@ -6208,17 +6210,19 @@ sub append_fuzzyfiles {
 
 =cut
 
+#some false laziness w/cdr.pm now
 sub batch_import {
   my $param = shift;
-  #warn join('-',keys %$param);
-  my $fh = $param->{filehandle};
+
+  my $fh       = $param->{filehandle};
+  my $type     = $param->{type} || 'csv';
+
   my $agentnum = $param->{agentnum};
+  my $refnum   = $param->{refnum};
+  my $pkgpart  = $param->{pkgpart};
 
-  my $refnum = $param->{refnum};
-  my $pkgpart = $param->{pkgpart};
+  my $format   = $param->{'format'};
 
-  #my @fields = @{$param->{fields}};
-  my $format = $param->{'format'};
   my @fields;
   my $payby;
   if ( $format eq 'simple' ) {
@@ -6253,14 +6257,32 @@ sub batch_import {
     die "unknown format $format";
   }
 
-  eval "use Text::CSV_XS;";
-  die $@ if $@;
+  my $parser;
+  my $spoolfile = '';
+  if ( $type eq 'csv' ) {
+    eval "use Text::CSV_XS;";
+    die $@ if $@;
+    $parser = new Text::CSV_XS;
+  } elsif ( $type eq 'xls' ) {
 
-  my $csv = new Text::CSV_XS;
-  #warn $csv;
-  #warn $fh;
+    eval "use Spreadsheet::ParseExcel;";
+    die $@ if $@;
+
+    ( my $spool_fh, $spoolfile ) =
+      tempfile('cust_main-batch_import-XXXXXXXXXXXX',
+                 DIR    => '%%%FREESIDE_CACHE%%%',
+                 SUFFIX => '.xls',
+              );
+    print $spool_fh slurp($fh);
+    close $spool_fh or die $!;
+
+    my $excel = new Spreadsheet::ParseExcel::Workbook->Parse($spoolfile);
+    $parser = $excel->{Worksheet}[0]; #first sheet
+
+  } else {
+    die "Unknown file type $type\n";
+  }
 
-  my $imported = 0;
   #my $columns;
 
   local $SIG{HUP} = 'IGNORE';
@@ -6274,16 +6296,35 @@ sub batch_import {
   local $FS::UID::AutoCommit = 0;
   my $dbh = dbh;
   
-  #while ( $columns = $csv->getline($fh) ) {
   my $line;
-  while ( defined($line=<$fh>) ) {
+  my $row = 0;
+  while (1) {
 
-    $csv->parse($line) or do {
-      $dbh->rollback if $oldAutoCommit;
-      return "can't parse: ". $csv->error_input();
-    };
+    my @columns = ();
+    if ( $type eq 'csv' ) {
+
+      last unless defined($line=<$fh>);
+
+      $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 @columns = $csv->fields();
     #warn join('-',@columns);
 
     my %cust_main = (
@@ -6375,7 +6416,7 @@ sub batch_import {
 
     if ( $error ) {
       $dbh->rollback if $oldAutoCommit;
-      return "can't insert customer for $line: $error";
+      return "can't insert customer ". ( $line ? "for $line" : '' ). ": $error";
     }
 
     if ( $format eq 'simple' ) {
@@ -6401,12 +6442,14 @@ sub batch_import {
 
     }
 
-    $imported++;
+    $row++;
   }
 
   $dbh->commit or die $dbh->errstr if $oldAutoCommit;
 
-  return "Empty file!" unless $imported;
+  unlink($spoolfile) if $spoolfile;
+
+  return "Empty file!" unless $row;
 
   ''; #no error
 
diff --git a/httemplate/elements/menu.html b/httemplate/elements/menu.html
index 5a947ea88..321927ec4 100644
--- a/httemplate/elements/menu.html
+++ b/httemplate/elements/menu.html
@@ -217,7 +217,7 @@ $report_menu{'SQL Query'}  = [ $fsurl.'search/report_sql.html', 'SQL Query' ]
   if $curuser->access_right('Raw SQL');
 
 tie my %tools_importing, 'Tie::IxHash',
-  'Import customers from CSV file' => [ $fsurl.'misc/cust_main-import.cgi', '' ],
+  'Import customers' => [ $fsurl.'misc/cust_main-import.cgi', '' ],
   'Import customer comments from CSV file' => [ $fsurl.'misc/cust_main_note-import.html', '' ],
   'Import one-time charges from CSV file' => [ $fsurl.'misc/cust_main-import_charges.cgi', '' ],
   'Import payments from CSV file' => [ $fsurl.'misc/cust_pay-import.cgi', '' ],
diff --git a/httemplate/misc/cust_main-import.cgi b/httemplate/misc/cust_main-import.cgi
index 84da38611..8e170c3c6 100644
--- a/httemplate/misc/cust_main-import.cgi
+++ b/httemplate/misc/cust_main-import.cgi
@@ -1,6 +1,6 @@
 <% include("/elements/header.html",'Batch Customer Import') %>
 
-Import a CSV file containing customer records.
+Import a file containing customer records.
 <BR><BR>
 
 <FORM ACTION="process/cust_main-import.cgi" METHOD="post" ENCTYPE="multipart/form-data">
@@ -26,8 +26,8 @@ Import a CSV file containing customer records.
 </TR>
 
 <TR>
-  <TH ALIGN="right">CSV filename</TH>
-  <TD><INPUT TYPE="file" NAME="csvfile"></TD>
+  <TH ALIGN="right">Filename</TH>
+  <TD><INPUT TYPE="file" NAME="file"></TD>
 </TR>
 % #include('/elements/tr-select-part_referral.html')
 %
@@ -49,7 +49,7 @@ Import a CSV file containing customer records.
 </TR>
 -->
 
-<TR><TD COLSPAN=2 ALIGN="center" STYLE="padding-top:6px"><INPUT TYPE="submit" VALUE="Import CSV file"></TD></TR>
+<TR><TD COLSPAN=2 ALIGN="center" STYLE="padding-top:6px"><INPUT TYPE="submit" VALUE="Import file"></TD></TR>
 
 </TABLE>
 
@@ -60,10 +60,13 @@ Import a CSV file containing customer records.
 <!-- Simple file format is CSV, with the following field order: <i>cust_pkg.setup, dayphone, first, last, address1, address2, city, state, zip, comments</i>
 <BR><BR> -->
 
-<b>Extended</b> file format is CSV, with the following field order: <i>agent_custid, refnum<%$req%>, last<%$req%>, first<%$req%>, address1<%$req%>, address2, city<%$req%>, state<%$req%>, zip<%$req%>, country, daytime, night, ship_last, ship_first, ship_address1, ship_address2, ship_city, ship_state, ship_zip, ship_country, payinfo<%$req%>, paycvv, paydate<%$req%>, invoicing_list, pkgpart, username, _password</i>
+Uploaded files can be CSV (comma-separated value) files or Excel spreadsheets.  The file should have a .CSV or .XLS extension.
 <BR><BR>
 
-<b>Extended plus company</b> file format is CSV, with the following field order: <i>agent_custid, refnum<%$req%>, last<%$req%>, first<%$req%>, company, address1<%$req%>, address2, city<%$req%>, state<%$req%>, zip<%$req%>, country, daytime, night, ship_last, ship_first, ship_company, ship_address1, ship_address2, ship_city, ship_state, ship_zip, ship_country, payinfo<%$req%>, paycvv, paydate<%$req%>, invoicing_list, pkgpart, username, _password</i>
+<b>Extended</b> format has the following field order: <i>agent_custid, refnum<%$req%>, last<%$req%>, first<%$req%>, address1<%$req%>, address2, city<%$req%>, state<%$req%>, zip<%$req%>, country, daytime, night, ship_last, ship_first, ship_address1, ship_address2, ship_city, ship_state, ship_zip, ship_country, payinfo, paycvv, paydate, invoicing_list, pkgpart, username, _password</i>
+<BR><BR>
+
+<b>Extended plus company</b> format has the following field order: <i>agent_custid, refnum<%$req%>, last<%$req%>, first<%$req%>, company, address1<%$req%>, address2, city<%$req%>, state<%$req%>, zip<%$req%>, country, daytime, night, ship_last, ship_first, ship_company, ship_address1, ship_address2, ship_city, ship_state, ship_zip, ship_country, payinfo, paycvv, paydate, invoicing_list, pkgpart, username, _password</i>
 <BR><BR>
 
 <%$req%> Required fields
diff --git a/httemplate/misc/process/cust_main-import.cgi b/httemplate/misc/process/cust_main-import.cgi
index aa8cd5298..2568d1c7b 100644
--- a/httemplate/misc/process/cust_main-import.cgi
+++ b/httemplate/misc/process/cust_main-import.cgi
@@ -9,20 +9,34 @@
 die "access denied"
   unless $FS::CurrentUser::CurrentUser->access_right('Import');
 
-my $fh = $cgi->upload('csvfile');
-#warn $cgi;
-#warn $fh;
+my $fh = $cgi->upload('file');
+my $error = '';
+if ( defined($fh) ) {
 
-my $error = defined($fh)
-  ? FS::cust_main::batch_import( {
+  my $type;
+  if ( $cgi->param('file') =~ /\.(\w+)$/i ) {
+    $type = lc($1);
+  } else {
+    #or error out???
+    warn "can't parse file type from filename ". $cgi->param('file').
+         '; defaulting to CSV';
+    $type = 'csv';
+  }
+
+  $error =
+    FS::cust_main::batch_import( {
       filehandle => $fh,
+      type       => $type,
       agentnum   => scalar($cgi->param('agentnum')),
       refnum     => scalar($cgi->param('refnum')),
       pkgpart    => scalar($cgi->param('pkgpart')),
       #'fields'    => [qw( cust_pkg.setup dayphone first last address1 address2
-      #                   city state zip comments                          )],
+      #                    city state zip comments                          )],
       'format'   => scalar($cgi->param('format')),
-    } )
-  : 'No file';
+    } );
+
+} else {
+  $error = 'No file';
+}
 
 </%init>