'type' => 'checkbox',
},
+ {
+ 'key' => 'cust_main-default_areacode',
+ 'section' => 'UI',
+ 'description' => 'Default area code for customers.',
+ 'type' => 'text',
+ },
+
);
1;
$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";
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;
=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' ) {
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';
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 = (
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' ) {
}
- $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
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', '' ],
<% 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">
</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>
-->
-<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>
<!-- 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
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>