3 #script to move customers from one installation to another
4 # source is remote, destination is local
9 use FS::UID qw( adminsuidsetup dbh );
10 use FS::Schema qw( dbdef );
11 use FS::Record qw( qsearchs );
16 use FS::cust_bill_ApplicationCommon;
25 my $source_datasrc = 'dbi:Pg:dbname=benson';
27 my $source_user = 'freeside';
30 my $dest_agentnum = 3;
39 'CARD' => [ 13, 14, 15 ],
52 or die "Usage:\n (edit variables at top of script and then)\n".
53 " b-move-customers user\n";
56 $FS::cust_main::ignore_expired_card = 1;
57 $FS::cust_main::ignore_expired_card = 1;
58 $FS::part_pkg::skip_pkg_svc_hack = 1;
59 $FS::part_pkg::skip_pkg_svc_hack = 1;
60 $FS::cust_bill_ApplicationCommon::skip_apply_to_lineitems_hack = 1;
61 $FS::cust_bill_ApplicationCommon::skip_apply_to_lineitems_hack = 1;
62 $FS::svc_Common::noexport_hack = 1;
63 $FS::svc_Common::noexport_hack = 1;
64 $FS::svc_domain::whois_hack = 1;
65 $FS::svc_domain::whois_hack = 1;
66 $FS::cust_pkg::disable_agentcheck = 1;
67 $FS::cust_pkg::disable_agentcheck = 1;
69 my $void_paynum = 2147483646; #top of int range
73 # source(remote) setup
75 $sdbh = DBI->connect($source_datasrc, $source_user, $source_pw)
78 $sdbh->{ChopBlanks} = 1;
83 $map{'_DOMSVC'} = \%domsvc_map;
85 import_table('pkg_category', 'nomap' => 1);
86 import_table('pkg_class', 'nomap' => 1,
87 'preinsert_callback' => sub {
88 my($row, $object) = @_;
89 my $src_categorynum = $row->{'categorynum'};
90 my $dest_categorynum = $map{'pkg_category'}->{$src_categorynum};
91 if ( $dest_categorynum ) {
92 $object->categorynum($dest_categorynum);
97 import_table('reason_type', 'nomap' => 1);
98 foreach my $src_typenum ( keys %{ $map{'reason_type'} } ) {
99 import_table('reason', 'reason_type' => $src_typenum,
100 'search' => 'reason_type',
101 'map' => 'reason_type',
105 my $customer_sth = $sdbh->prepare(
106 "SELECT * FROM cust_main WHERE agentnum = $src_agentnum ORDER BY custnum"
107 ) or die $sdbh->errstr;
109 $customer_sth->execute or die $customer_sth->errstr;
113 while ( my $customerrow = $customer_sth->fetchrow_hashref ) {
115 my $src_custnum = $customerrow->{'custnum'};
117 if ( $customerrow->{'referral_custnum'} ) {
118 warn " $src_custnum has referral_custnum ". $customerrow->{'referral_custnum'};
119 $referrals{$src_custnum} = $customerrow->{'referral_custnum'};
122 my $cust_main = new FS::cust_main {
125 'referral_custnum' => '',
126 'refnum' => $dest_refnum,
127 'agentnum' => $dest_agentnum,
128 'agent_custid' => $src_custnum,
131 my $error = $cust_main->insert;
133 warn "*** WARNING: error importing customer src custnum $src_custnum: $error";
137 warn "inserting dest customer ". $cust_main->custnum. " for $src_custnum\n";
139 $map{'cust_main'}->{$src_custnum} = $cust_main->custnum;
141 #now import the relations, easy and hard:
143 import_table( 'cust_location', 'custnum' => $src_custnum );
145 import_table( 'cust_main_note', 'custnum' => $src_custnum );
147 import_table( 'cust_pay', 'custnum' => $src_custnum );
149 import_table( 'cust_credit', 'custnum' => $src_custnum,
150 'preinsert_callback' => sub {
151 my($row, $object) = @_;
152 my $src_reasonnum = $row->{'reasonnum'};
153 my $dest_reasonnum = $map{'reason'}->{$src_reasonnum};
154 if ( $dest_reasonnum ) {
155 $object->reasonnum($dest_reasonnum);
160 import_table( 'cust_refund', 'custnum' => $src_custnum,
161 'post_callback' => sub {
162 #my( $src_refundnum, $dst_refundnum ) = @_;
163 my $src_refundnum = shift;
165 # cust_pay_refund (map refundnum and paynum...)
166 import_table( 'cust_pay_refund',
167 'refundnum' => $src_refundnum,
168 'search' => 'refundnum',
169 'map' => 'cust_refund',
170 'map2' => 'cust_pay',
171 'map2key' => 'paynum',
178 import_table( 'cust_pay_void', 'custnum' => $src_custnum,
179 'preinsert_callback' => sub {
180 my($row, $object) = @_;
181 $object->paynum( $void_paynum-- );
185 # no data in old db for:
186 # cust_attachment, cust_statement, cdr, cdr_*, cust_bill_event,
187 # cust_main_exemption, cust_pay_batch, cust_tax_*, cust_recon,
188 # inventory_item, part_bill_event, part_device, part_export,
189 # part_pop_local, part_virtual_field, pay_batch, phone_*,
190 # payment_gateway_*, prepay_credit, port, radius_usergroup,
191 # rate_*, reg_code, reg_code_pkg, registrar, router,
192 # svc_acct, svc_acct_pop, svc_broadband, svc_external,
193 # svc_forward, svc_phone, svc_www, tax_*, usage_class, virtual_field
194 # appears to be unused in old db: inventory_class
197 #werid direct cust_main relations:
199 warn " inserting cust_pkg for src cust $src_custnum\n";
200 # cust_pkg (part_pkg, part_svc, etc.)
201 import_table( 'cust_pkg', 'custnum' => $src_custnum,
202 'preinsert_callback' => sub {
203 my($row, $object) = @_;
205 $object->start_date(''); #bogus start dates on all packages
207 my $src_pkgpart = $row->{'pkgpart'} or die "wtf";
208 my $dest_pkgpart = $map{'part_pkg'}->{$src_pkgpart};
209 if ( $dest_pkgpart ) {
210 $object->pkgpart($dest_pkgpart);
214 my $sth = $sdbh->prepare(
215 "SELECT * FROM part_pkg WHERE pkgpart = $src_pkgpart"
216 ) or die $sdbh->errstr;
218 $sth->execute or die $sth->errstr;
220 my $part_pkg_row = $sth->fetchrow_hashref
221 or die "cust_pkg.pkgpart missing in part_pkg?!";
227 my $src_classnum = $part_pkg_row->{'classnum'};
228 $hashref->{'classnum'} = $map{'pkg_class'}->{ $src_classnum }
231 my $part_pkg = new FS::part_pkg $hashref;
233 #$part_pkg->setuptax('') if $part_pkg->setuptax =~ /^\s+$/;
234 #$part_pkg->recurtax('') if $part_pkg->recurtax =~ /^\s+$/;
236 my $error = $part_pkg->insert( 'options' => {} );
237 die "*** FATAL: error importing part_pkg src pkgpart $src_pkgpart ".
241 $map{ 'part_pkg' }->{ $part_pkg_row->{'pkgpart'} } = $part_pkg->pkgpart;
244 import_table( 'part_pkg_option',
245 'pkgpart' => $src_pkgpart,
246 'search' => 'pkgpart',
250 my $osth = $sdbh->prepare(
251 "SELECT * FROM part_pkg_option WHERE pkgpart = $src_pkgpart"
252 ) or die $sdbh->errstr;
254 # pkg_svc, part_svc, part_svc_column
255 import_table( 'pkg_svc',
256 'pkgpart' => $src_pkgpart,
257 'search' => 'pkgpart',
259 'preinsert_callback' => sub {
261 my($row, $object) = @_;
262 my $src_svcpart = $row->{'svcpart'} or die "wtf2";
263 my $dest_svcpart = $map{'part_svc'}->{$src_svcpart};
264 if ( $dest_svcpart ) {
265 $object->svcpart($dest_svcpart);
269 my $sth = $sdbh->prepare(
270 "SELECT * FROM part_svc WHERE svcpart = $src_svcpart"
271 ) or die $sdbh->errstr;
273 $sth->execute or die $sth->errstr;
275 my $part_svc_row = $sth->fetchrow_hashref
276 or die "svcpart missing in part_svc?!";
283 my $part_svc = new FS::part_svc $hashref;
284 $part_svc->disabled('') if $part_svc->disabled =~ /^\s+$/;
285 my $error = $part_svc->insert;
286 die "*** FATAL: error importing part_svc src svcpart $src_svcpart ".
290 $map{ 'part_svc' }->{ $part_svc_row->{'svcpart'} } = $part_svc->svcpart;
293 import_table( 'part_svc_column',
294 'svcpart' => $src_svcpart,
295 'search' => 'svcpart',
297 'preinsert_callback' => sub {
298 my($row, $object) = @_;
299 if ( $object->columnname eq 'domsvc' ) {
300 $object->columnvalue( $map{'_DOMSVC'}->{ $object->columnvalue } );
305 #what we came here for in the first place
306 $object->svcpart( $part_svc->svcpart );
311 #what we came here for in the first place
312 $object->pkgpart( $part_pkg->pkgpart );
316 'post_callback' => sub {
317 #my( $src_pkgnum, $dst_pkgnum ) = @_;
318 my $src_pkgnum = shift;
320 #XXX grr... action makes this very hard...
321 ## cust_pkg_reason (shit, and bring in/remap reasons)
322 #import_table( 'cust_pkg_reason',
323 # 'pkgnum' => $src_pkgnum,
324 # 'search' => 'pkgnum',
325 # 'map' => 'cust_pkg',
326 # 'map2' => 'reason',
327 # 'map2key' => 'reasonnum',
331 import_table( 'cust_svc',
332 'pkgnum' => $src_pkgnum,
333 'search' => 'pkgnum',
335 'map2' => 'part_svc',
336 'map2key' => 'svcpart',
337 'post_callback' => sub {
338 #my( $src_svcnum, $dst_svcnum ) = @_;
339 my $src_svcnum = shift;
342 import_table( 'svc_domain',
343 'svcnum' => $src_svcnum,
344 'search' => 'svcnum',
346 'noblank_primary' => 1,
352 import_table('cust_pkg_detail',
353 'pkgnum' => $src_pkgnum,
354 'search' => 'pkgnum',
361 # end of cust_pkg (part_pkg, part_svc, etc.)
363 warn " inserting cust_bill for src cust $src_custnum\n";
364 # cust_bill (invnum move)
365 import_table( 'cust_bill', 'custnum' => $src_custnum,
366 'preinsert_callback' => sub {
367 my($row, $object) = @_;
368 $object->agent_invid( $row->{'invnum'} );
370 'post_callback' => sub {
371 my( $src_invnum, $dst_invnum ) = @_;
372 #my $src_invnum = shift;
374 # cust_bill_pkg ( map invnum and pkgnum... )
375 import_table( 'cust_bill_pkg',
376 'invnum' => $src_invnum,
377 'search' => 'invnum',
378 'map' => 'cust_bill',
379 'map2' => 'cust_pkg',
380 'map2key' => 'pkgnum',
381 'post_callback' => sub {
382 my $src_billpkgnum = shift;
384 import_table( 'cust_bill_pkg_detail',
385 'cust_bill_pkg.billpkgnum' => $src_billpkgnum,
386 'search' => 'cust_bill_pkg.billpkgnum',
387 'map' => 'cust_bill_pkg',
388 'addl_from' => 'left join cust_bill_pkg using ( invnum, pkgnum )',
394 # cust_credit_bill (map invnum and crednum... )
395 import_table( 'cust_credit_bill',
396 'invnum' => $src_invnum,
397 'search' => 'invnum',
398 'map' => 'cust_bill',
399 'map2' => 'cust_credit',
400 'map2key' => 'crednum',
401 'post_callback' => sub {
402 my $src_creditbillnum = shift;
403 #map creditbillnum and billpkgnum
404 import_table( 'cust_credit_bill_pkg',
405 'creditbillnum' => $src_creditbillnum,
406 'search' => 'creditbillnum',
407 'map' => 'cust_credit_bill',
408 'map2' => 'cust_bill_pkg',
409 'map2key' => 'billpkgnum',
415 # cust_bill_pay (map invnum and paynum...)
416 import_table( 'cust_bill_pay',
417 'invnum' => $src_invnum,
418 'search' => 'invnum',
419 'map' => 'cust_bill',
420 'map2' => 'cust_pay',
421 'map2key' => 'paynum',
422 'post_callback' => sub {
423 my $src_billpaynum = shift;
424 #map billpaynum and billpkgnum
425 import_table( 'cust_bill_pay_pkg',
426 'billpaynum' => $src_billpaynum,
427 'search' => 'billpaynum',
428 'map' => 'cust_bill_pay',
429 'map2' => 'cust_bill_pkg',
430 'map2key' => 'billpkgnum',
435 #need to do something about events. mark initial stuff as done
436 foreach my $eventpart ( @{ $eventparts{$cust_main->payby} } ) {
438 my $cust_event = new FS::cust_event {
439 'eventpart' => $eventpart,
440 'tablenum' => $dst_invnum,
441 '_date' => time, # XXX something? probably not
445 my $error = $cust_event->insert;
446 die "*** FATAL: error inserting cust_event for eventpart $eventpart,".
447 " tablenum (invnum) $dst_invnum: $error"
457 # (not used in old db: cust_bill_pay_batch, cust_pkg_option)
461 # (not in old db: cust_bill_pkg_display, cust_bill_pkg_tax_location,
462 # cust_bill_pkg_tax_rate_location, cust_tax_adjustment, cust_svc_option, )
463 # (not used in old db: cust_tax_exempt_pkg)
465 #do this last, so no notices go out
466 import_table( 'cust_main_invoice', 'custnum' => $src_custnum );
468 #dbh->commit or die dbh->errstr;
469 warn "customer ". $cust_main->custnum. " inserted\n";
474 foreach my $agent_custid ( keys %referrals ) {
475 my $referred_cust = qsearchs('cust_main',
476 { 'agentnum' => $dest_agentnum,
477 'agent_custid' => $agent_custid,
480 $referred_cust->referral_custnum($map{'cust_main'}->{$referrals{$agent_custid}});
481 $referred_cust->replace;
485 warn "import successful!\n";
487 warn "rolling back (dry run)\n";
488 dbh->rollback or die dbh->errstr;
492 dbh->commit or die dbh->errstr;
497 my( $table, %opt ) = @_;
499 eval "use FS::$table;";
502 my $map = $opt{'map'} || 'cust_main';
503 my $search = $opt{'search'} || 'custnum';
505 $opt{'insert_opts'} ||= [];
507 my $primary_key = dbdef->table($table)->primary_key;
509 my $addl_from = defined($opt{'addl_from'}) ? $opt{'addl_from'} : '';
511 my $sth = $sdbh->prepare(
512 "SELECT * FROM $table $addl_from ".
513 ( $opt{'nomap'} ? '' : " WHERE $search = ". $opt{$search} )
514 ) or die $sdbh->errstr;
516 $sth->execute or die "(searching $table): ". $sth->errstr;
518 while ( my $row = $sth->fetchrow_hashref ) {
519 #my $src_custnum = $customerrow->{'custnum'};
521 my $hashref = { %$row };
522 $hashref->{$primary_key} = ''
523 unless $opt{'noblank_primary'};
524 $hashref->{ $search } = $map{$map}->{ $row->{$search} }
525 unless $opt{'nomap'};
527 if ( $opt{'map2'} ) {
528 my $key2 = $opt{'map2key'};
529 $hashref->{$key2} = $map{ $opt{'map2'} }->{ $row->{$key2} }
530 unless $opt{map2key} eq 'pkgnum' && ( $row->{$key2} eq '0'
531 || $row->{$key2} eq '-1'
533 or ! defined($row->{$key2})
534 or $row->{$key2} eq '';
535 #warn "map $opt{map2}.$opt{map2key}: ". $row->{$key2}. " to ". $map{ $opt{'map2'} }->{ $row->{$key2} };
538 if ( $opt{'map3'} ) {
539 my $key3 = $opt{'map3key'};
540 $hashref->{$key3} = $map{ $opt{'map3'} }->{ $row->{$key3} };
543 my $object = eval "new FS::$table \$hashref;";
546 &{ $opt{preinsert_callback} }( $row, $object )
547 if $opt{preinsert_callback};
549 my $error = $object->insert( @{ $opt{'insert_opts'} } );
551 warn "*** WARNING: error importing $table src $primary_key ". $row->{$primary_key}. ": $error";
555 $map{ $table }->{ $row->{$primary_key} } = $object->get($primary_key);
557 &{ $opt{post_callback} }( $row->{$primary_key}, $object->get($primary_key) )
558 if $opt{post_callback};