1 package FS::cust_main::Import;
4 use vars qw( $DEBUG $conf );
9 use File::Slurp qw( slurp );
10 use FS::UID qw( dbh );
18 install_callback FS::UID sub {
24 FS::cust_main::Import - Batch customer importing
28 use FS::cust_main::Import;
31 FS::cust_main::Import::batch_import( {
32 file => $file, #filename
33 type => $type, #csv or xls
34 format => $format, #extended, extended-plus_company, svc_external,
35 # or svc_external_svc_phone
36 agentnum => $agentnum,
39 job => $job, #optional job queue job, for progressbar updates
40 custbatch => $custbatch, #optional batch unique identifier
45 use FS::UI::Web::JSRPC;
47 new FS::UI::Web::JSRPC 'FS::cust_main::Import::process_batch_import', $cgi;
48 print $server->process;
52 Batch customer importing.
56 =item process_batch_import
58 Load a batch import as a queued JSRPC job
62 sub process_batch_import {
65 my $param = thaw(decode_base64(shift));
66 warn Dumper($param) if $DEBUG;
68 my $files = $param->{'uploaded_files'}
69 or die "No files provided.\n";
71 my (%files) = map { /^(\w+):([\.\w]+)$/ ? ($1,$2):() } split /,/, $files;
73 my $dir = '%%%FREESIDE_CACHE%%%/cache.'. $FS::UID::datasrc. '/';
74 my $file = $dir. $files{'file'};
77 if ( $file =~ /\.(\w+)$/i ) {
81 warn "can't parse file type from filename $file; defaulting to CSV";
86 FS::cust_main::Import::batch_import( {
90 custbatch => $param->{custbatch},
91 agentnum => $param->{'agentnum'},
92 refnum => $param->{'refnum'},
93 pkgpart => $param->{'pkgpart'},
94 #'fields' => [qw( cust_pkg.setup dayphone first last address1 address2
95 # city state zip comments )],
96 'format' => $param->{'format'},
101 die "$error\n" if $error;
110 #some false laziness w/cdr.pm now
114 my $job = $param->{job};
116 my $filename = $param->{file};
117 my $type = $param->{type} || 'csv';
119 my $custbatch = $param->{custbatch};
121 my $agentnum = $param->{agentnum};
122 my $refnum = $param->{refnum};
123 my $pkgpart = $param->{pkgpart};
125 my $format = $param->{'format'};
129 if ( $format eq 'simple' ) {
130 @fields = qw( cust_pkg.setup dayphone first last
131 address1 address2 city state zip comments );
133 } elsif ( $format eq 'extended' ) {
134 @fields = qw( agent_custid refnum
135 last first address1 address2 city state zip country
137 ship_last ship_first ship_address1 ship_address2
138 ship_city ship_state ship_zip ship_country
139 payinfo paycvv paydate
142 svc_acct.username svc_acct._password
145 } elsif ( $format eq 'extended-plus_company' ) {
146 @fields = qw( agent_custid refnum
147 last first company address1 address2 city state zip country
149 ship_last ship_first ship_company ship_address1 ship_address2
150 ship_city ship_state ship_zip ship_country
151 payinfo paycvv paydate
154 svc_acct.username svc_acct._password
157 } elsif ( $format =~ /^svc_external/ ) {
158 @fields = qw( agent_custid refnum
159 last first company address1 address2 city state zip country
161 ship_last ship_first ship_company ship_address1 ship_address2
162 ship_city ship_state ship_zip ship_country
163 payinfo paycvv paydate
165 cust_pkg.pkgpart cust_pkg.bill
166 svc_external.id svc_external.title
168 push @fields, map "svc_phone.$_", qw( countrycode phonenum sip_password pin)
169 if $format eq 'svc_external_svc_phone';
172 die "unknown format $format";
178 if ( $type eq 'csv' ) {
180 eval "use Text::CSV_XS;";
183 $parser = new Text::CSV_XS;
185 @buffer = split(/\r?\n/, slurp($filename) );
186 $count = scalar(@buffer);
188 } elsif ( $type eq 'xls' ) {
190 eval "use Spreadsheet::ParseExcel;";
193 my $excel = new Spreadsheet::ParseExcel::Workbook->Parse($filename);
194 $parser = $excel->{Worksheet}[0]; #first sheet
196 $count = $parser->{MaxRow} || $parser->{MinRow};
200 die "Unknown file type $type\n";
205 local $SIG{HUP} = 'IGNORE';
206 local $SIG{INT} = 'IGNORE';
207 local $SIG{QUIT} = 'IGNORE';
208 local $SIG{TERM} = 'IGNORE';
209 local $SIG{TSTP} = 'IGNORE';
210 local $SIG{PIPE} = 'IGNORE';
212 my $oldAutoCommit = $FS::UID::AutoCommit;
213 local $FS::UID::AutoCommit = 0;
218 my( $last, $min_sec ) = ( time, 5 ); #progressbar foo
222 if ( $type eq 'csv' ) {
224 last unless scalar(@buffer);
225 $line = shift(@buffer);
227 $parser->parse($line) or do {
228 $dbh->rollback if $oldAutoCommit;
229 return "can't parse: ". $parser->error_input();
231 @columns = $parser->fields();
233 } elsif ( $type eq 'xls' ) {
235 last if $row > ($parser->{MaxRow} || $parser->{MinRow});
237 my @row = @{ $parser->{Cells}[$row] };
238 @columns = map $_->{Val}, @row;
241 #warn $z++. ": $_\n" for @columns;
244 die "Unknown file type $type\n";
247 #warn join('-',@columns);
250 custbatch => $custbatch,
251 agentnum => $agentnum,
253 country => $conf->config('countrydefault') || 'US',
254 payby => $payby, #default
255 paydate => '12/2037', #default
258 my %cust_pkg = ( pkgpart => $pkgpart );
260 foreach my $field ( @fields ) {
262 if ( $field =~ /^cust_pkg\.(pkgpart|setup|bill|susp|adjourn|expire|cancel)$/ ) {
264 #$cust_pkg{$1} = str2time( shift @$columns );
265 if ( $1 eq 'pkgpart' ) {
266 $cust_pkg{$1} = shift @columns;
267 } elsif ( $1 eq 'setup' ) {
268 $billtime = str2time(shift @columns);
270 $cust_pkg{$1} = str2time( shift @columns );
273 } elsif ( $field =~ /^svc_acct\.(username|_password)$/ ) {
275 $svc_x{$1} = shift @columns;
277 } elsif ( $field =~ /^svc_external\.(id|title)$/ ) {
279 $svc_x{$1} = shift @columns;
281 } elsif ( $field =~ /^svc_phone\.(countrycode|phonenum|sip_password|pin)$/ ) {
282 $svc_x{$1} = shift @columns;
287 if ( $field eq 'refnum' && $columns[0] !~ /^\s*(\d+)\s*$/ ) {
289 my $referral = $columns[0];
290 my %hash = ( 'referral' => $referral,
291 'agentnum' => $agentnum,
295 my $part_referral = qsearchs('part_referral', \%hash )
296 || new FS::part_referral \%hash;
298 unless ( $part_referral->refnum ) {
299 my $error = $part_referral->insert;
301 $dbh->rollback if $oldAutoCommit;
302 return "can't auto-insert advertising source: $referral: $error";
306 $columns[0] = $part_referral->refnum;
309 my $value = shift @columns;
310 $cust_main{$field} = $value if length($value);
314 $cust_main{'payby'} = 'CARD'
315 if defined $cust_main{'payinfo'}
316 && length $cust_main{'payinfo'};
318 my $invoicing_list = $cust_main{'invoicing_list'}
319 ? [ delete $cust_main{'invoicing_list'} ]
322 my $cust_main = new FS::cust_main ( \%cust_main );
325 tie my %hash, 'Tie::RefHash'; #this part is important
327 if ( $cust_pkg{'pkgpart'} ) {
328 my $cust_pkg = new FS::cust_pkg ( \%cust_pkg );
332 if ( $svc_x{'username'} ) {
334 } elsif ( $svc_x{'id'} || $svc_x{'title'} ) {
335 $svcdb = 'svc_external';
339 if ( $svc_x{'countrycode'} || $svc_x{'phonenum'} ) {
340 $svc_phone = FS::svc_phone->new( {
341 map { $_ => delete($svc_x{$_}) }
342 qw( countrycode phonenum sip_password pin)
346 if ( $svcdb || $svc_phone ) {
347 my $part_pkg = $cust_pkg->part_pkg;
348 unless ( $part_pkg ) {
349 $dbh->rollback if $oldAutoCommit;
350 return "unknown pkgpart: ". $cust_pkg{'pkgpart'};
353 $svc_x{svcpart} = $part_pkg->svcpart_unique_svcdb( $svcdb );
354 my $class = "FS::$svcdb";
355 push @svc_x, $class->new( \%svc_x );
358 $svc_phone->svcpart( $part_pkg->svcpart_unique_svcdb('svc_phone') );
359 push @svc_x, $svc_phone;
363 $hash{$cust_pkg} = \@svc_x;
366 my $error = $cust_main->insert( \%hash, $invoicing_list );
369 $dbh->rollback if $oldAutoCommit;
370 return "can't insert customer". ( $line ? " for $line" : '' ). ": $error";
373 if ( $format eq 'simple' ) {
375 #false laziness w/bill.cgi
376 $error = $cust_main->bill( 'time' => $billtime );
378 $dbh->rollback if $oldAutoCommit;
379 return "can't bill customer for $line: $error";
382 $error = $cust_main->apply_payments_and_credits;
384 $dbh->rollback if $oldAutoCommit;
385 return "can't bill customer for $line: $error";
388 $error = $cust_main->collect();
390 $dbh->rollback if $oldAutoCommit;
391 return "can't collect customer for $line: $error";
398 if ( $job && time - $min_sec > $last ) { #progress bar
399 $job->update_statustext( int(100 * $row / $count) );
405 $dbh->commit or die $dbh->errstr if $oldAutoCommit;;
407 return "Empty file!" unless $row;
415 Not enough documentation.
419 L<FS::cust_main>, L<FS::cust_pkg>,
420 L<FS::svc_acct>, L<FS::svc_external>, L<FS::svc_phone>