1 package FS::cust_main::Import;
4 use vars qw( $DEBUG $conf );
8 use File::Slurp qw( slurp );
9 use FS::Misc::DateTime qw( parse_datetime );
10 use FS::UID qw( dbh );
11 use FS::Record qw( qsearchs );
14 use FS::svc_broadband;
18 use FS::part_referral;
22 install_callback FS::UID sub {
26 my %is_location = map { $_ => 1 } FS::cust_main::Location->location_fields;
30 FS::cust_main::Import - Batch customer importing
34 use FS::cust_main::Import;
37 FS::cust_main::Import::batch_import( {
38 file => $file, #filename
39 type => $type, #csv or xls
40 format => $format, #extended, extended-plus_company, svc_external,
41 #extended-plus_company_and_options
42 #extended-plus_options, or svc_external_svc_phone
43 agentnum => $agentnum,
46 job => $job, #optional job queue job, for progressbar updates
47 custbatch => $custbatch, #optional batch unique identifier
52 use FS::UI::Web::JSRPC;
54 new FS::UI::Web::JSRPC 'FS::cust_main::Import::process_batch_import', $cgi;
55 print $server->process;
59 Batch customer importing.
63 =item process_batch_import
65 Load a batch import as a queued JSRPC job
69 sub process_batch_import {
72 my $param = thaw(decode_base64(shift));
73 warn Dumper($param) if $DEBUG;
75 my $files = $param->{'uploaded_files'}
76 or die "No files provided.\n";
78 my (%files) = map { /^(\w+):([\.\w]+)$/ ? ($1,$2):() } split /,/, $files;
80 my $dir = '%%%FREESIDE_CACHE%%%/cache.'. $FS::UID::datasrc. '/';
81 my $file = $dir. $files{'file'};
84 if ( $file =~ /\.(\w+)$/i ) {
88 warn "can't parse file type from filename $file; defaulting to CSV";
93 FS::cust_main::Import::batch_import( {
97 custbatch => $param->{custbatch},
98 agentnum => $param->{'agentnum'},
99 refnum => $param->{'refnum'},
100 pkgpart => $param->{'pkgpart'},
101 #'fields' => [qw( cust_pkg.setup dayphone first last address1 address2
102 # city state zip comments )],
103 'format' => $param->{'format'},
108 die "$error\n" if $error;
117 #some false laziness w/cdr.pm now
121 my $job = $param->{job};
123 my $filename = $param->{file};
124 my $type = $param->{type} || 'csv';
126 my $custbatch = $param->{custbatch};
128 my $agentnum = $param->{agentnum};
129 my $refnum = $param->{refnum};
130 my $pkgpart = $param->{pkgpart};
132 my $format = $param->{'format'};
136 if ( $format eq 'simple' ) {
137 @fields = qw( cust_pkg.setup dayphone first last
138 address1 address2 city state zip comments );
140 } elsif ( $format eq 'extended' ) {
141 @fields = qw( agent_custid refnum
142 last first address1 address2 city state zip country
144 ship_last ship_first ship_address1 ship_address2
145 ship_city ship_state ship_zip ship_country
146 payinfo paycvv paydate
149 svc_acct.username svc_acct._password
152 } elsif ( $format eq 'extended-plus_options' ) {
153 @fields = qw( agent_custid refnum
154 last first address1 address2 city state zip country
156 ship_last ship_first ship_address1 ship_address2
157 ship_city ship_state ship_zip ship_country
158 payinfo paycvv paydate
161 svc_acct.username svc_acct._password
165 } elsif ( $format eq 'extended-plus_company' ) {
166 @fields = qw( agent_custid refnum
167 last first company address1 address2 city state zip country
169 ship_last ship_first ship_company ship_address1 ship_address2
170 ship_city ship_state ship_zip ship_country
171 payinfo paycvv paydate
174 svc_acct.username svc_acct._password
177 } elsif ( $format eq 'extended-plus_company_and_options' ) {
178 @fields = qw( agent_custid refnum
179 last first company address1 address2 city state zip country
181 ship_last ship_first ship_company ship_address1 ship_address2
182 ship_city ship_state ship_zip ship_country
183 payinfo paycvv paydate
186 svc_acct.username svc_acct._password
190 } elsif ( $format =~ /^svc_broadband/ ) {
191 @fields = qw( agent_custid refnum
192 last first company address1 address2 city state zip country
194 ship_last ship_first ship_company ship_address1 ship_address2
195 ship_city ship_state ship_zip ship_country
196 payinfo paycvv paydate
198 cust_pkg.pkgpart cust_pkg.bill
200 push @fields, map "svc_broadband.$_", qw( ip_addr mac_addr sectornum );
202 } elsif ( $format =~ /^svc_external/ ) {
203 @fields = qw( agent_custid refnum
204 last first company address1 address2 city state zip country
206 ship_last ship_first ship_company ship_address1 ship_address2
207 ship_city ship_state ship_zip ship_country
208 payinfo paycvv paydate
210 cust_pkg.pkgpart cust_pkg.bill
211 svc_external.id svc_external.title
213 push @fields, map "svc_phone.$_", qw( countrycode phonenum sip_password pin)
214 if $format eq 'svc_external_svc_phone';
216 } elsif ( $format eq 'birthdates-acct_phone_hardware') {
217 @fields = qw( agent_custid refnum
218 last first company address1 address2 city state zip country
220 ship_last ship_first ship_company ship_address1 ship_address2
221 ship_city ship_state ship_zip ship_country
222 birthdate spouse_birthdate
223 payinfo paycvv paydate
225 cust_pkg.pkgpart cust_pkg.bill
226 svc_acct.username svc_acct._password
228 push @fields, map "svc_phone.$_", qw(countrycode phonenum sip_password pin);
229 push @fields, map "svc_hardware.$_", qw(typenum ip_addr hw_addr serial);
232 } elsif ( $format eq 'national_id-acct_phone') {
233 @fields = qw( agent_custid refnum
234 last first company address1 address2 city state zip country
236 ship_last ship_first ship_company ship_address1 ship_address2
237 ship_city ship_state ship_zip ship_country
239 payinfo paycvv paydate
241 cust_pkg.pkgpart cust_pkg.bill
242 svc_acct.username svc_acct._password svc_acct.slipip
244 push @fields, map "svc_phone.$_", qw(countrycode phonenum sip_password pin);
248 die "unknown format $format";
254 if ( $type eq 'csv' ) {
256 eval "use Text::CSV_XS;";
259 $parser = new Text::CSV_XS;
261 @buffer = split(/\r?\n/, slurp($filename) );
262 $count = scalar(@buffer);
264 } elsif ( $type eq 'xls' ) {
266 eval "use Spreadsheet::ParseExcel;";
269 my $excel = Spreadsheet::ParseExcel::Workbook->new->Parse($filename);
270 $parser = $excel->{Worksheet}[0]; #first sheet
272 $count = $parser->{MaxRow} || $parser->{MinRow};
276 die "Unknown file type $type\n";
281 local $SIG{HUP} = 'IGNORE';
282 local $SIG{INT} = 'IGNORE';
283 local $SIG{QUIT} = 'IGNORE';
284 local $SIG{TERM} = 'IGNORE';
285 local $SIG{TSTP} = 'IGNORE';
286 local $SIG{PIPE} = 'IGNORE';
288 my $oldAutoCommit = $FS::UID::AutoCommit;
289 local $FS::UID::AutoCommit = 0;
292 #implies ignore_expired_card
293 local($FS::cust_main::import) = 1;
294 local($FS::cust_main::import) = 1;
298 my( $last, $min_sec ) = ( time, 5 ); #progressbar foo
302 if ( $type eq 'csv' ) {
304 last unless scalar(@buffer);
305 $line = shift(@buffer);
307 $parser->parse($line) or do {
308 $dbh->rollback if $oldAutoCommit;
309 return "can't parse: ". $parser->error_input();
311 @columns = $parser->fields();
313 } elsif ( $type eq 'xls' ) {
315 last if $row > ($parser->{MaxRow} || $parser->{MinRow})
316 || ! $parser->{Cells}[$row];
318 my @row = @{ $parser->{Cells}[$row] };
319 @columns = map $_->{Val}, @row;
322 #warn $z++. ": $_\n" for @columns;
325 die "Unknown file type $type\n";
328 #warn join('-',@columns);
331 custbatch => $custbatch,
332 agentnum => $agentnum,
334 payby => $payby, #default
335 paydate => '12/2037', #default
338 my %cust_pkg = ( pkgpart => $pkgpart );
340 my %bill_location = ();
341 my %ship_location = ();
342 foreach my $field ( @fields ) {
344 if ( $field =~ /^cust_pkg\.(pkgpart|setup|bill|susp|adjourn|expire|cancel)$/ ) {
346 #$cust_pkg{$1} = parse_datetime( shift @$columns );
347 if ( $1 eq 'pkgpart' ) {
348 $cust_pkg{$1} = shift @columns;
349 } elsif ( $1 eq 'setup' ) {
350 $billtime = parse_datetime(shift @columns);
352 $cust_pkg{$1} = parse_datetime( shift @columns );
355 } elsif ( $field =~ /^svc_acct\.(username|_password|slipip)$/ ) {
357 $svc_x{$1} = shift @columns;
359 } elsif ( $field =~ /^svc_broadband\.(ip_addr|mac_addr|sectornum)$/ ) {
361 $svc_x{$1} = shift @columns;
363 } elsif ( $field =~ /^svc_external\.(id|title)$/ ) {
365 $svc_x{$1} = shift @columns;
367 } elsif ( $field =~ /^svc_phone\.(countrycode|phonenum|sip_password|pin)$/ ) {
368 $svc_x{$1} = shift @columns;
370 } elsif ( $field =~ /^svc_hardware\.(typenum|ip_addr|hw_addr|serial)$/ ) {
372 $svc_x{$1} = shift @columns;
374 } elsif ( $is_location{$field} ) {
376 $bill_location{$field} = shift @columns;
378 } elsif ( $field =~ /^ship_(.*)$/ and $is_location{$1} ) {
380 $ship_location{$1} = shift @columns;
385 if ( $field eq 'refnum' && $columns[0] !~ /^\s*(\d+)\s*$/ ) {
387 my $referral = $columns[0];
388 my %hash = ( 'referral' => $referral,
389 'agentnum' => $agentnum,
393 my $part_referral = qsearchs('part_referral', \%hash )
394 || new FS::part_referral \%hash;
396 unless ( $part_referral->refnum ) {
397 my $error = $part_referral->insert;
399 $dbh->rollback if $oldAutoCommit;
400 return "can't auto-insert advertising source: $referral: $error";
404 $columns[0] = $part_referral->refnum;
407 my $value = shift @columns;
408 $cust_main{$field} = $value if length($value);
410 } # foreach my $field
411 # finished importing columns
413 $bill_location{'country'} ||= $conf->config('countrydefault') || 'US';
414 $cust_main{'bill_location'} = FS::cust_location->new(\%bill_location);
415 if ( grep $_, values(%ship_location) ) {
416 $ship_location{'country'} ||= $conf->config('countrydefault') || 'US';
417 $cust_main{'ship_location'} = FS::cust_location->new(\%ship_location);
419 $cust_main{'ship_location'} = $cust_main{'bill_location'};
422 if ( defined $cust_main{'payinfo'} && length $cust_main{'payinfo'} ) {
424 if ( $cust_main{'payinfo'} =~ /^\s*(\d+\@[\d\.]+)\s*$/ ) {
426 $cust_main{'payby'} = 'CHEK';
427 $cust_main{'payinfo'} = $1;
431 $cust_main{'payby'} = 'CARD';
433 if ($cust_main{'payinfo'} =~ /^\s*([AD]?)(.*)\s*$/) {
434 $cust_main{'payby'} = 'DCRD' if $1 eq 'D';
435 $cust_main{'payinfo'} = $2;
442 $cust_main{$_} = parse_datetime($cust_main{$_})
443 foreach grep $cust_main{$_},
444 qw( birthdate spouse_birthdate anniversary_date );
446 my $invoicing_list = $cust_main{'invoicing_list'}
447 ? [ delete $cust_main{'invoicing_list'} ]
450 my $customer_options = delete $cust_main{customer_options};
451 $cust_main{tax} = 'Y' if $customer_options =~ /taxexempt/i;
452 push @$invoicing_list, 'POST' if $customer_options =~ /postalinvoice/i;
454 my $cust_main = new FS::cust_main ( \%cust_main );
457 tie my %hash, 'Tie::RefHash'; #this part is important
459 if ( $cust_pkg{'pkgpart'} ) {
461 unless ( $cust_pkg{'pkgpart'} =~ /^\d+$/ ) {
462 $dbh->rollback if $oldAutoCommit;
463 return 'illegal pkgpart: '. $cust_pkg{'pkgpart'};
466 my $cust_pkg = new FS::cust_pkg ( \%cust_pkg );
470 if ( $svc_x{'username'} ) {
472 } elsif ( $svc_x{'id'} || $svc_x{'title'} ) {
473 $svcdb = 'svc_external';
474 } elsif ( $svc_x{ip_addr} || $svc_x{mac_addr} ) {
475 $svcdb = 'svc_broadband';
479 if ( $svc_x{'countrycode'} || $svc_x{'phonenum'} ) {
480 $svc_phone = FS::svc_phone->new( {
481 map { $_ => delete($svc_x{$_}) }
482 qw( countrycode phonenum sip_password pin )
486 my $svc_hardware = '';
487 if ( $svc_x{'typenum'} ) {
488 $svc_hardware = FS::svc_hardware->new( {
489 map { $_ => delete($svc_x{$_}) }
490 qw( typenum ip_addr hw_addr serial )
494 if ( $svcdb || $svc_phone || $svc_hardware ) {
495 my $part_pkg = $cust_pkg->part_pkg;
496 unless ( $part_pkg ) {
497 $dbh->rollback if $oldAutoCommit;
498 return "unknown pkgpart: ". $cust_pkg{'pkgpart'};
501 $svc_x{svcpart} = $part_pkg->svcpart_unique_svcdb( $svcdb );
502 my $class = "FS::$svcdb";
503 push @svc_x, $class->new( \%svc_x );
506 $svc_phone->svcpart( $part_pkg->svcpart_unique_svcdb('svc_phone') );
507 push @svc_x, $svc_phone;
509 if ( $svc_hardware ) {
510 $svc_hardware->svcpart( $part_pkg->svcpart_unique_svcdb('svc_hardware') );
511 push @svc_x, $svc_hardware;
516 $hash{$cust_pkg} = \@svc_x;
519 my $error = $cust_main->insert( \%hash, $invoicing_list );
522 $dbh->rollback if $oldAutoCommit;
523 return "can't insert customer". ( $line ? " for $line" : '' ). ": $error";
526 if ( $format eq 'simple' ) {
528 #false laziness w/bill.cgi
529 $error = $cust_main->bill( 'time' => $billtime );
531 $dbh->rollback if $oldAutoCommit;
532 return "can't bill customer for $line: $error";
535 $error = $cust_main->apply_payments_and_credits;
537 $dbh->rollback if $oldAutoCommit;
538 return "can't bill customer for $line: $error";
541 $error = $cust_main->collect();
543 $dbh->rollback if $oldAutoCommit;
544 return "can't collect customer for $line: $error";
551 if ( $job && time - $min_sec > $last ) { #progress bar
552 $job->update_statustext( int(100 * $row / $count) );
558 $dbh->commit or die $dbh->errstr if $oldAutoCommit;;
560 return "Empty file!" unless $row;
568 Not enough documentation.
572 L<FS::cust_main>, L<FS::cust_pkg>,
573 L<FS::svc_acct>, L<FS::svc_external>, L<FS::svc_phone>