3 #script to move customers from one installation to another
4 # source is remote, destination is local
5 # script is kinda-specific to a somewhat old source installation (1.7? older?)
6 # target installation has to be 1.9 (after 9/2009)
11 use FS::UID qw( adminsuidsetup dbh );
12 use FS::Schema qw( dbdef );
13 use FS::Record qw( qsearchs );
18 use FS::cust_bill_ApplicationCommon;
26 #ssh -p 2222 -L 1080:66.209.32.4:7219 -L 5454:localhost:5432 66.209.32.4
28 #my $source_datasrc = 'DBI:Pg:host=66.209.32.4;dbname=freeside;sslmode=require';
29 my $source_datasrc = 'DBI:Pg:host=localhost;port=5454;dbname=freeside';
30 my $source_user = 'readonly';
33 #my @source_agents = ( 2, 7, 3, 4, 5, 1 );
34 my @source_agents = ( 1, 2, 3, 4, 5, 7 );
36 my $dest_agent_typenum = 12;
40 my $dest_legacy_credit_reasontype = 5;
42 my $dest_pkg_classnum = 6;
74 or die "Usage:\n (edit variables at top of script and then)\n".
75 " move-customers user\n";
78 $FS::cust_main::ignore_expired_card = 1;
79 $FS::cust_main::ignore_expired_card = 1;
80 $FS::part_pkg::skip_pkg_svc_hack = 1;
81 $FS::part_pkg::skip_pkg_svc_hack = 1;
82 $FS::cust_bill_ApplicationCommon::skip_apply_to_lineitems_hack = 1;
83 $FS::cust_bill_ApplicationCommon::skip_apply_to_lineitems_hack = 1;
84 $FS::svc_Common::noexport_hack = 1;
85 $FS::svc_Common::noexport_hack = 1;
86 $FS::svc_domain::whois_hack = 1;
87 $FS::svc_domain::whois_hack = 1;
89 my $void_paynum = 2147483646; #top of int range
93 # source(remote) setup
95 $sdbh = DBI->connect($source_datasrc, $source_user, $source_pw)
98 $sdbh->{ChopBlanks} = 1;
103 $map{'_DOMSVC'} = \%domsvc_map;
105 import_table('pkg_class', 'nomap' => 1);
106 import_table('svc_acct_pop', 'nomap' => 1);
109 #import_table('reason_type', 'nomap' => 1);
110 #foreach my $src_typenum ( keys %{ $map{'reason_type'} } ) {
111 # import_table('reason', 'reason_type' => $src_typenum,
112 # 'search' => 'reason_type',
113 # 'map' => 'reason_type',
117 my $agent_sth = $sdbh->prepare(
118 'SELECT * FROM agent WHERE agentnum IN ( '. join(',', @source_agents ). ')'
119 ) or die $sdbh->errstr;
121 $agent_sth->execute or die $agent_sth->errstr;
124 while ( my $agentrow = $agent_sth->fetchrow_hashref ) {
126 my $src_agent = $agentrow->{'agent'};
128 warn "importing customers for $src_agent\n";
130 my $agent = qsearchs('agent', { 'agent' => $src_agent } );
134 warn " using existing agentnum ". $agent->agentnum. "\n";
137 warn "DELETING ALL CUSTOMERS OF $src_agent locally \n";
139 foreach my $statement (
140 'DELETE FROM cust_main WHERE agentnum = '. $agent->agentnum,
141 ( map { "DELETE FROM $_
142 WHERE 0 = ( SELECT COUNT(*) FROM cust_main
143 WHERE cust_main.custnum = $_.custnum )
154 #pkg_class, part_pkg_pop
155 #part_pkg, pkg_svc, part_svc, part_svc_column
156 #XXX more... does it matter?
160 my $sth = dbh->prepare($statement) or die dbh->errstr;
161 $sth->execute or die $sth->errstr;
165 dbh->commit or die dbh->errstr;
171 warn " creating new agent...\n";
173 $agent = new FS::agent { 'agent' => $src_agent,
174 'typenum' => $dest_agent_typenum };
175 my $error = $agent->insert;
176 die $error if $error;
178 warn " agentnum ". $agent->agentnum. "\n";
182 $map{'agent'}->{ $agentrow->{'agentnum'} } = $agent->agentnum;
186 #my $customer_sth = $sdbh->prepare(
187 # 'SELECT * FROM cust_main WHERE agentnum = '. $agentrow->{'agentnum'}
188 #) or die $sdbh->errstr;
189 my $customer_sth = $sdbh->prepare(
190 'SELECT * FROM cust_main WHERE agentnum IN ( '. join(',', @source_agents ). ')
192 ) or die $sdbh->errstr;
194 $customer_sth->execute or die $customer_sth->errstr;
196 while ( my $customerrow = $customer_sth->fetchrow_hashref ) {
199 # warn Dumper($customerrow);
200 my $src_custnum = $customerrow->{'custnum'};
202 warn " $src_custnum has referral_custnum ". $customerrow->{'referral_custnum'}
203 if $customerrow->{'referral_custnum'};
205 my $cust_main = new FS::cust_main {
208 'referral_custnum' => '', #restore afterwords?
209 'refnum' => $dest_refnum,
210 'agentnum' => $map{'agent'}->{ $customerrow->{'agentnum'} },
211 'agent_custid' => $src_custnum,
214 #$cust_main->ship_country('') if $cust_main->ship_country eq ' ';
215 #$cust_main->tax('') if $cust_main->tax =~ /^\s+$/;
217 my $error = $cust_main->insert;
219 warn "*** WARNING: error importing customer src custnum $src_custnum: $error";
221 warn Dumper($cust_main) if $src_custnum == 6854;
225 warn "inserting dest customer ". $cust_main->custnum. " for $src_custnum\n";
227 $map{'cust_main'}->{$src_custnum} = $cust_main->custnum;
229 #now import the relations, easy and hard:
231 import_table( 'cust_main_note', 'custnum' => $src_custnum );
233 import_table( 'cust_pay', 'custnum' => $src_custnum,
234 #ivan showing up as cust_pay otaker
235 # old db doesn't have cust_pay.otaker, pull it from history
236 'preinsert_callback' => sub {
237 my($row, $cust_pay) = @_;
239 my $sth = $sdbh->prepare(
240 "SELECT history_user FROM h_cust_pay WHERE history_action = 'insert'
241 AND paynum = ". $row->{'paynum'}
242 ) or die $sdbh->errstr;
243 $sth->execute or die $sth->errstr;
244 my $otaker = $sth->fetchrow_arrayref->[0];
246 $cust_pay->otaker($otaker);
250 # crap, cust_credit.reason is text in old db
251 #*** WARNING: error importing cust_credit src crednum 2200: failed to set reason for [ FS::cust_credit ]: at ./move-customers line 232.
252 import_table( 'cust_credit', 'custnum' => $src_custnum,
253 'insert_opts' => [ 'reason_type' => $dest_legacy_credit_reasontype ],
254 'preinsert_callback' => sub {
255 my($row, $object) = @_;
256 $object->set('reason', '(none)') if $object->get('reason') =~ /^\s*$/;
260 import_table( 'cust_refund', 'custnum' => $src_custnum,
261 'post_callback' => sub {
262 #my( $src_refundnum, $dst_refundnum ) = @_;
263 my $src_refundnum = shift;
265 # cust_credit_refund (map refundnum and crednum...)
266 import_table( 'cust_credit_refund',
267 'refundnum' => $src_refundnum,
268 'search' => 'refundnum',
269 'map' => 'cust_refund',
270 'map2' => 'cust_credit',
271 'map2key' => 'crednum',
274 # cust_pay_refund (map refundnum and paynum...)
275 import_table( 'cust_pay_refund',
276 'refundnum' => $src_refundnum,
277 'search' => 'refundnum',
278 'map' => 'cust_refund',
279 'map2' => 'cust_pay',
280 'map2key' => 'paynum',
286 # dunno what's up with this (ship_country ' ', fixed)
287 #*** WARNING: error importing customer src custnum 6854: Illegal (name) (error code illegal_name) ship_last: at ./move-customers line 129.
290 import_table( 'cust_pay_void', 'custnum' => $src_custnum,
291 'preinsert_callback' => sub {
292 my($row, $object) = @_;
293 $object->paynum( $void_paynum-- );
297 # (not in old db: cust_attachment, cust_statement, cust_location,
298 # cust_main_exemption, cust_pay_pending )
299 # (not used in old db: cust_pay_batch, cust_tax_exempt)
300 # (not useful to migrate: queue)
302 #werid direct cust_main relations:
304 # cust_pkg (part_pkg, part_svc, etc.)
305 import_table( 'cust_pkg', 'custnum' => $src_custnum,
306 'preinsert_callback' => sub {
307 my($row, $object) = @_;
308 my $src_pkgpart = $row->{'pkgpart'} or die "wtf";
309 my $dest_pkgpart = $map{'part_pkg'}->{$src_pkgpart};
310 if ( $dest_pkgpart ) {
311 $object->pkgpart($dest_pkgpart);
315 my $sth = $sdbh->prepare(
316 "SELECT * FROM part_pkg WHERE pkgpart = $src_pkgpart"
317 ) or die $sdbh->errstr;
319 $sth->execute or die $sth->errstr;
321 my $part_pkg_row = $sth->fetchrow_hashref
322 or die "cust_pkg.pkgpart missing in part_pkg?!";
328 my $src_classnum = $part_pkg_row->{'classnum'};
329 $hashref->{'classnum'} = $map{'pkg_class'}->{ $src_classnum }
332 my $part_pkg = new FS::part_pkg $hashref;
334 #$part_pkg->setuptax('') if $part_pkg->setuptax =~ /^\s+$/;
335 #$part_pkg->recurtax('') if $part_pkg->recurtax =~ /^\s+$/;
337 my $error = $part_pkg->insert( 'options' => {} );
338 die "*** FATAL: error importing part_pkg src pkgpart $src_pkgpart ".
342 $map{ 'part_pkg' }->{ $part_pkg_row->{'pkgpart'} } = $part_pkg->pkgpart;
345 import_table( 'part_pkg_option',
346 'pkgpart' => $src_pkgpart,
347 'search' => 'pkgpart',
351 my $osth = $sdbh->prepare(
352 "SELECT * FROM part_pkg_option WHERE pkgpart = $src_pkgpart"
353 ) or die $sdbh->errstr;
355 # pkg_svc, part_svc, part_svc_column
356 import_table( 'pkg_svc',
357 'pkgpart' => $src_pkgpart,
358 'search' => 'pkgpart',
360 'preinsert_callback' => sub {
362 my($row, $object) = @_;
363 my $src_svcpart = $row->{'svcpart'} or die "wtf2";
364 my $dest_svcpart = $map{'part_svc'}->{$src_svcpart};
365 if ( $dest_svcpart ) {
366 $object->svcpart($dest_svcpart);
370 my $sth = $sdbh->prepare(
371 "SELECT * FROM part_svc WHERE svcpart = $src_svcpart"
372 ) or die $sdbh->errstr;
374 $sth->execute or die $sth->errstr;
376 my $part_svc_row = $sth->fetchrow_hashref
377 or die "svcpart missing in part_svc?!";
384 my $part_svc = new FS::part_svc $hashref;
385 $part_svc->disabled('') if $part_svc->disabled =~ /^\s+$/;
386 my $error = $part_svc->insert;
387 die "*** FATAL: error importing part_svc src svcpart $src_svcpart ".
391 $map{ 'part_svc' }->{ $part_svc_row->{'svcpart'} } = $part_svc->svcpart;
394 import_table( 'part_svc_column',
395 'svcpart' => $src_svcpart,
396 'search' => 'svcpart',
398 'preinsert_callback' => sub {
399 my($row, $object) = @_;
400 if ( $object->columnname eq 'domsvc' ) {
401 $object->columnvalue( $map{'_DOMSVC'}->{ $object->columnvalue } );
406 #what we came here for in the first place
407 $object->svcpart( $part_svc->svcpart );
412 #what we came here for in the first place
413 $object->pkgpart( $part_pkg->pkgpart );
417 'post_callback' => sub {
418 #my( $src_pkgnum, $dst_pkgnum ) = @_;
419 my $src_pkgnum = shift;
421 #XXX grr... action makes this very hard...
422 ## cust_pkg_reason (shit, and bring in/remap reasons)
423 #import_table( 'cust_pkg_reason',
424 # 'pkgnum' => $src_pkgnum,
425 # 'search' => 'pkgnum',
426 # 'map' => 'cust_pkg',
427 # 'map2' => 'reason',
428 # 'map2key' => 'reasonnum',
432 import_table( 'cust_svc',
433 'pkgnum' => $src_pkgnum,
434 'search' => 'pkgnum',
436 'map2' => 'part_svc',
437 'map2key' => 'svcpart',
438 'post_callback' => sub {
439 #my( $src_svcnum, $dst_svcnum ) = @_;
440 my $src_svcnum = shift;
443 import_table( 'svc_domain',
444 'svcnum' => $src_svcnum,
445 'search' => 'svcnum',
447 'noblank_primary' => 1,
451 import_table( 'svc_acct',
452 'svcnum' => $src_svcnum,
453 'search' => 'svcnum',
455 'noblank_primary' => 1,
456 'map2' => 'svc_acct_pop',
457 'map2key' => 'popnum',
458 #'map3' => 'svc_domain',
460 'map3key' => 'domsvc',
464 import_table( 'radius_usergroup',
465 'svcnum' => $src_svcnum,
466 'search' => 'svcnum',
470 #other svc_ tables not in old db
481 # end of cust_pkg (part_pkg, part_svc, etc.)
483 # cust_bill (invnum move)
484 import_table( 'cust_bill', 'custnum' => $src_custnum,
485 'preinsert_callback' => sub {
486 my($row, $object) = @_;
487 $object->agent_invid( $row->{'invnum'} );
489 'post_callback' => sub {
490 my( $src_invnum, $dst_invnum ) = @_;
491 #my $src_invnum = shift;
493 # cust_bill_pkg ( map invnum and pkgnum... )
494 import_table( 'cust_bill_pkg',
495 'invnum' => $src_invnum,
496 'search' => 'invnum',
497 'map' => 'cust_bill',
498 'map2' => 'cust_pkg',
499 'map2key' => 'pkgnum',
500 'post_callback' => sub {
501 my $src_billpkgnum = shift;
503 import_table( 'cust_bill_pkg_detail',
504 'billpkgnum' => $src_billpkgnum,
505 'search' => 'billpkgnum',
506 'map' => 'cust_bill_pkg',
507 'addl_from' => 'left join cust_bill_pkg using ( invnum, pkgnum )',
513 # cust_credit_bill (map invnum and crednum... )
514 import_table( 'cust_credit_bill',
515 'invnum' => $src_invnum,
516 'search' => 'invnum',
517 'map' => 'cust_bill',
518 'map2' => 'cust_credit',
519 'map2key' => 'crednum',
520 'post_callback' => sub {
521 my $src_creditbillnum = shift;
522 #map creditbillnum and billpkgnum
523 import_table( 'cust_credit_bill_pkg',
524 'creditbillnum' => $src_creditbillnum,
525 'search' => 'creditbillnum',
526 'map' => 'cust_credit_bill',
527 'map2' => 'cust_bill_pkg',
528 'map2key' => 'billpkgnum',
534 # cust_bill_pay (map invnum and paynum...)
535 import_table( 'cust_bill_pay',
536 'invnum' => $src_invnum,
537 'search' => 'invnum',
538 'map' => 'cust_bill',
539 'map2' => 'cust_pay',
540 'map2key' => 'paynum',
541 'post_callback' => sub {
542 my $src_billpaynum = shift;
543 #map billpaynum and billpkgnum
544 import_table( 'cust_bill_pay_pkg',
545 'billpaynum' => $src_billpaynum,
546 'search' => 'billpaynum',
547 'map' => 'cust_bill_pay',
548 'map2' => 'cust_bill_pkg',
549 'map2key' => 'billpkgnum',
554 #need to do something about events. mark initial stuff as done
555 foreach my $eventpart ( @{ $eventparts{$cust_main->payby} } ) {
557 my $cust_event = new FS::cust_event {
558 'eventpart' => $eventpart,
559 'tablenum' => $dst_invnum,
560 '_date' => time, # XXX something? probably not
564 my $error = $cust_event->insert;
565 die "*** FATAL: error inserting cust_event for eventpart $eventpart,".
566 " tablenum (invnum) $dst_invnum: $error"
576 # (not in old db: cust_pkg_detail)
577 # (not used in old db: cust_bill_pay_batch, cust_pkg_option)
581 # (not in old db: cust_bill_pkg_display, cust_bill_pkg_tax_location,
582 # cust_bill_pkg_tax_rate_location, cust_tax_adjustment, cust_svc_option, )
583 # (not used in old db: cust_tax_exempt_pkg)
585 #do this last, so no notices go out
586 import_table( 'cust_main_invoice', 'custnum' => $src_custnum );
588 #dbh->commit or die dbh->errstr;
589 warn "customer ". $cust_main->custnum. " inserted\n";
595 warn "import successful!\n";
597 warn "rolling back (dry run)\n";
598 dbh->rollback or die dbh->errstr;
602 dbh->commit or die dbh->errstr;
607 my( $table, %opt ) = @_;
609 eval "use FS::$table;";
612 my $map = $opt{'map'} || 'cust_main';
613 my $search = $opt{'search'} || 'custnum';
615 $opt{'insert_opts'} ||= [];
617 my $primary_key = dbdef->table($table)->primary_key;
619 my $addl_from = defined($opt{'addl_from'}) ? $opt{'addl_from'} : '';
621 my $sth = $sdbh->prepare(
622 "SELECT * FROM $table $addl_from ".
623 ( $opt{'nomap'} ? '' : " WHERE $search = ". $opt{$search} )
624 ) or die $sdbh->errstr;
626 $sth->execute or die "(searching $table): ". $sth->errstr;
628 while ( my $row = $sth->fetchrow_hashref ) {
629 #my $src_custnum = $customerrow->{'custnum'};
631 my $hashref = { %$row };
632 $hashref->{$primary_key} = ''
633 unless $opt{'noblank_primary'};
634 $hashref->{ $search } = $map{$map}->{ $row->{$search} }
635 unless $opt{'nomap'};
637 if ( $opt{'map2'} ) {
638 my $key2 = $opt{'map2key'};
639 $hashref->{$key2} = $map{ $opt{'map2'} }->{ $row->{$key2} }
640 unless $opt{map2key} eq 'pkgnum' && ( $row->{$key2} eq '0'
641 || $row->{$key2} eq '-1'
643 or ! defined($row->{$key2})
644 or $row->{$key2} eq '';
645 #warn "map $opt{map2}.$opt{map2key}: ". $row->{$key2}. " to ". $map{ $opt{'map2'} }->{ $row->{$key2} };
648 if ( $opt{'map3'} ) {
649 my $key3 = $opt{'map3key'};
650 $hashref->{$key3} = $map{ $opt{'map3'} }->{ $row->{$key3} };
653 my $object = eval "new FS::$table \$hashref;";
656 &{ $opt{preinsert_callback} }( $row, $object )
657 if $opt{preinsert_callback};
659 my $error = $object->insert( @{ $opt{'insert_opts'} } );
661 warn "*** WARNING: error importing $table src $primary_key ". $row->{$primary_key}. ": $error";
665 $map{ $table }->{ $row->{$primary_key} } = $object->get($primary_key);
667 &{ $opt{post_callback} }( $row->{$primary_key}, $object->get($primary_key) )
668 if $opt{post_callback};