import customer from Excel file too

diff --git a/FS/FS/Conf.pm b/FS/FS/Conf.pm
index 3c4c6ee..728a213 100644 (file)
--- 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',
   },
 
     'type'        => 'checkbox',
   },
 

+  {
+    'key'         => 'cust_main-default_areacode',
+    'section'     => 'UI',
+    'description' => 'Default area code for customers.',
+    'type'        => 'text',
+  },
+

 );
 
 1;
 );
 
 1;

diff --git a/FS/FS/Record.pm b/FS/FS/Record.pm
index 8bd57eb..2540dd3 100644 (file)
--- 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;
     $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";
     $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 51270d6..e698bfa 100644 (file)
--- 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 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;
 use String::Approx qw(amatch);
 use Business::CreditCard 0.28;
 use Locale::Country;


@@ -6208,17 +6210,19 @@ sub append_fuzzyfiles {
 
 =cut
 
 
 =cut
 

+#some false laziness w/cdr.pm now

 sub batch_import {
   my $param = shift;
 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 $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' ) {
   my @fields;
   my $payby;
   if ( $format eq 'simple' ) {


@@ -6253,14 +6257,32 @@ sub batch_import {
     die "unknown format $format";
   }
 
     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';
   #my $columns;
 
   local $SIG{HUP} = 'IGNORE';


@@ -6274,16 +6296,35 @@ sub batch_import {
   local $FS::UID::AutoCommit = 0;
   my $dbh = dbh;
   
   local $FS::UID::AutoCommit = 0;
   my $dbh = dbh;
   

-  #while ( $columns = $csv->getline($fh) ) {

   my $line;
   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 = (
     #warn join('-',@columns);
 
     my %cust_main = (


@@ -6375,7 +6416,7 @@ sub batch_import {
 
     if ( $error ) {
       $dbh->rollback if $oldAutoCommit;
 
     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' ) {
     }
 
     if ( $format eq 'simple' ) {


@@ -6401,12 +6442,14 @@ sub batch_import {
 
     }
 
 
     }
 

-    $imported++;
+    $row++;

   }
 
   $dbh->commit or die $dbh->errstr if $oldAutoCommit;
 
   }
 
   $dbh->commit or die $dbh->errstr if $oldAutoCommit;
 

-  return "Empty file!" unless $imported;
+  unlink($spoolfile) if $spoolfile;
+
+  return "Empty file!" unless $row;

 
   ''; #no error
 
 
   ''; #no error
 

diff --git a/httemplate/elements/menu.html b/httemplate/elements/menu.html
index 5a947ea..321927e 100644 (file)
--- 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',
   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', '' ],
   '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 84da386..8e170c3 100644 (file)
--- 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') %>
 
 <% 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">
 <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>
 </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')
 %
 </TR>
 % #include('/elements/tr-select-part_referral.html')
 %


@@ -49,7 +49,7 @@ Import a CSV file containing customer records.
 </TR>
 -->
 
 </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>
 
 
 </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> -->
 
 <!-- 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>
 
 <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
 <BR><BR>
 
 <%$req%> Required fields

diff --git a/httemplate/misc/process/cust_main-import.cgi b/httemplate/misc/process/cust_main-import.cgi
index aa8cd52..2568d1c 100644 (file)
--- 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');
 
 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,
       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
       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')),
       'format'   => scalar($cgi->param('format')),

-    } )
-  : 'No file';
+    } );
+
+} else {
+  $error = 'No file';
+}

 
 </%init>
 
 </%init>