diff options
Diffstat (limited to 'bin/move-customers')
| -rwxr-xr-x | bin/move-customers | 678 |
1 files changed, 0 insertions, 678 deletions
diff --git a/bin/move-customers b/bin/move-customers deleted file mode 100755 index a7ea197..0000000 --- a/bin/move-customers +++ /dev/null @@ -1,678 +0,0 @@ -#!/usr/bin/perl -w - -#script to move customers from one installation to another -# source is remote, destination is local -# script is kinda-specific to a somewhat old source installation (1.7? older?) -# target installation has to be 1.9 (after 9/2009) - -use strict; -use vars qw( $sdbh ); -use DBI; -use FS::UID qw( adminsuidsetup dbh ); -use FS::Schema qw( dbdef ); -use FS::Record qw( qsearchs ); -use FS::agent; -use FS::cust_main; -use FS::part_pkg; -use FS::part_svc; -use FS::cust_bill_ApplicationCommon; -use FS::svc_Common; -use FS::cust_event; -use FS::svc_domain; -use FS::cust_pkg; - -my $DANGEROUS = 0; -my $DRY = 0; - -#ssh -p 2222 -L 1080:66.209.32.4:7219 -L 5454:localhost:5432 66.209.32.4 - -#my $source_datasrc = 'DBI:Pg:host=66.209.32.4;dbname=freeside;sslmode=require'; -my $source_datasrc = 'DBI:Pg:host=localhost;port=5454;dbname=freeside'; -my $source_user = 'readonly'; -my $source_pw = ''; - -#my @source_agents = ( 2, 7, 3, 4, 5, 1 ); -my @source_agents = ( 1, 2, 3, 4, 5, 7 ); - -my $dest_agent_typenum = 12; - -my $dest_refnum = 60; - -my $dest_legacy_credit_reasontype = 5; - -my $dest_pkg_classnum = 6; - -my %domsvc_map = ( - 1 => 20450, - 3653 => 20162, - 7634 => 20451, -); - -#testing -#my %eventparts = ( -# 'CARD' => [ 1, ], -# 'CHEK' => [], -# 'BILL' => [], -# 'DCHK' => [], -# 'DCRD' => [], -# 'COMP' => [], -#); -#production -my %eventparts = ( - 'CARD' => [ 1, ], - 'CHEK' => [ 2, ], - 'BILL' => [ 5, ], - 'DCHK' => [ 12, ], - 'DCRD' => [ 15, ], - 'COMP' => [], -); - -#-- - -# target(local) setup - -my $user = shift - or die "Usage:\n (edit variables at top of script and then)\n". - " move-customers user\n"; -adminsuidsetup $user; - -$FS::cust_main::ignore_expired_card = 1; -$FS::cust_main::ignore_expired_card = 1; -$FS::part_pkg::skip_pkg_svc_hack = 1; -$FS::part_pkg::skip_pkg_svc_hack = 1; -$FS::cust_bill_ApplicationCommon::skip_apply_to_lineitems_hack = 1; -$FS::cust_bill_ApplicationCommon::skip_apply_to_lineitems_hack = 1; -$FS::svc_Common::noexport_hack = 1; -$FS::svc_Common::noexport_hack = 1; -$FS::svc_domain::whois_hack = 1; -$FS::svc_domain::whois_hack = 1; -$FS::cust_pkg::disable_agentcheck = 1; -$FS::cust_pkg::disable_agentcheck = 1; - -my $void_paynum = 2147483646; #top of int range - -# -- - -# source(remote) setup - -$sdbh = DBI->connect($source_datasrc, $source_user, $source_pw) - or die $DBI::errstr; - -$sdbh->{ChopBlanks} = 1; - -# -- - -my %map = (); -$map{'_DOMSVC'} = \%domsvc_map; - -import_table('pkg_class', 'nomap' => 1); -import_table('svc_acct_pop', 'nomap' => 1); - -#XXX -#import_table('reason_type', 'nomap' => 1); -#foreach my $src_typenum ( keys %{ $map{'reason_type'} } ) { -# import_table('reason', 'reason_type' => $src_typenum, -# 'search' => 'reason_type', -# 'map' => 'reason_type', -# ); -#} - -my $agent_sth = $sdbh->prepare( - 'SELECT * FROM agent WHERE agentnum IN ( '. join(',', @source_agents ). ')' -) or die $sdbh->errstr; - -$agent_sth->execute or die $agent_sth->errstr; - - -while ( my $agentrow = $agent_sth->fetchrow_hashref ) { - - my $src_agent = $agentrow->{'agent'}; - - warn "importing customers for $src_agent\n"; - - my $agent = qsearchs('agent', { 'agent' => $src_agent, 'disabled' => '' } ); - - if ( $agent ) { - - warn " using existing agentnum ". $agent->agentnum. "\n"; - - if ( $DANGEROUS ) { - warn "DELETING ALL CUSTOMERS OF $src_agent locally \n"; - - foreach my $statement ( - 'DELETE FROM cust_main WHERE agentnum = '. $agent->agentnum, - ( map { "DELETE FROM $_ - WHERE 0 = ( SELECT COUNT(*) FROM cust_main - WHERE cust_main.custnum = $_.custnum ) - " - } - qw( - cust_credit - cust_main_invoice - cust_main_note - cust_pay - cust_refund - ) - ) - #pkg_class, part_pkg_pop - #part_pkg, pkg_svc, part_svc, part_svc_column - #XXX more... does it matter? - ) { - - #warn $statement; - my $sth = dbh->prepare($statement) or die dbh->errstr; - $sth->execute or die $sth->errstr; - - } - - dbh->commit or die dbh->errstr; - - } - - } else { - - warn " creating new agent...\n"; - - $agent = new FS::agent { 'agent' => $src_agent, - 'typenum' => $dest_agent_typenum }; - my $error = $agent->insert; - die $error if $error; - - warn " agentnum ". $agent->agentnum. "\n"; - - } - - $map{'agent'}->{ $agentrow->{'agentnum'} } = $agent->agentnum; - -} - - #my $customer_sth = $sdbh->prepare( - # 'SELECT * FROM cust_main WHERE agentnum = '. $agentrow->{'agentnum'} - #) or die $sdbh->errstr; -my $customer_sth = $sdbh->prepare( - 'SELECT * FROM cust_main WHERE agentnum IN ( '. join(',', @source_agents ). ') - ORDER BY custnum' -) or die $sdbh->errstr; - -$customer_sth->execute or die $customer_sth->errstr; - -while ( my $customerrow = $customer_sth->fetchrow_hashref ) { - - #use Data::Dumper; - # warn Dumper($customerrow); - my $src_custnum = $customerrow->{'custnum'}; - - warn " $src_custnum has referral_custnum ". $customerrow->{'referral_custnum'} - if $customerrow->{'referral_custnum'}; - - my $cust_main = new FS::cust_main { - %{ $customerrow }, - 'custnum' => '', - 'referral_custnum' => '', #restore afterwords? - 'refnum' => $dest_refnum, - 'agentnum' => $map{'agent'}->{ $customerrow->{'agentnum'} }, - 'agent_custid' => $src_custnum, - }; - - #$cust_main->ship_country('') if $cust_main->ship_country eq ' '; - #$cust_main->tax('') if $cust_main->tax =~ /^\s+$/; - - my $error = $cust_main->insert; - if ( $error ) { - warn "*** WARNING: error importing customer src custnum $src_custnum: $error"; - use Data::Dumper; - warn Dumper($cust_main) if $src_custnum == 6854; - next; - } - - warn "inserting dest customer ". $cust_main->custnum. " for $src_custnum\n"; - - $map{'cust_main'}->{$src_custnum} = $cust_main->custnum; - - #now import the relations, easy and hard: - - import_table( 'cust_main_note', 'custnum' => $src_custnum ); - - import_table( 'cust_pay', 'custnum' => $src_custnum, - #ivan showing up as cust_pay otaker - # old db doesn't have cust_pay.otaker, pull it from history - 'preinsert_callback' => sub { - my($row, $cust_pay) = @_; - - my $sth = $sdbh->prepare( - "SELECT history_user FROM h_cust_pay WHERE history_action = 'insert' - AND paynum = ". $row->{'paynum'} - ) or die $sdbh->errstr; - $sth->execute or die $sth->errstr; - my $otaker = $sth->fetchrow_arrayref->[0]; - - $cust_pay->otaker($otaker); - }, - ); - - # crap, cust_credit.reason is text in old db -#*** WARNING: error importing cust_credit src crednum 2200: failed to set reason for [ FS::cust_credit ]: at ./move-customers line 232. - import_table( 'cust_credit', 'custnum' => $src_custnum, - 'insert_opts' => [ 'reason_type' => $dest_legacy_credit_reasontype ], - 'preinsert_callback' => sub { - my($row, $object) = @_; - $object->set('reason', '(none)') if $object->get('reason') =~ /^\s*$/; - }, - ); - - import_table( 'cust_refund', 'custnum' => $src_custnum, - 'post_callback' => sub { - #my( $src_refundnum, $dst_refundnum ) = @_; - my $src_refundnum = shift; - - # cust_credit_refund (map refundnum and crednum...) - import_table( 'cust_credit_refund', - 'refundnum' => $src_refundnum, - 'search' => 'refundnum', - 'map' => 'cust_refund', - 'map2' => 'cust_credit', - 'map2key' => 'crednum', - ); - - # cust_pay_refund (map refundnum and paynum...) - import_table( 'cust_pay_refund', - 'refundnum' => $src_refundnum, - 'search' => 'refundnum', - 'map' => 'cust_refund', - 'map2' => 'cust_pay', - 'map2key' => 'paynum', - ); - - }, - ); - - # dunno what's up with this (ship_country ' ', fixed) -#*** WARNING: error importing customer src custnum 6854: Illegal (name) (error code illegal_name) ship_last: at ./move-customers line 129. - - # cust_pay_void - import_table( 'cust_pay_void', 'custnum' => $src_custnum, - 'preinsert_callback' => sub { - my($row, $object) = @_; - $object->paynum( $void_paynum-- ); - }, - ); - - # (not in old db: cust_attachment, cust_statement, cust_location, - # cust_main_exemption, cust_pay_pending ) - # (not used in old db: cust_pay_batch, cust_tax_exempt) - # (not useful to migrate: queue) - - #werid direct cust_main relations: - - # cust_pkg (part_pkg, part_svc, etc.) - import_table( 'cust_pkg', 'custnum' => $src_custnum, - 'preinsert_callback' => sub { - my($row, $object) = @_; - my $src_pkgpart = $row->{'pkgpart'} or die "wtf"; - my $dest_pkgpart = $map{'part_pkg'}->{$src_pkgpart}; - if ( $dest_pkgpart ) { - $object->pkgpart($dest_pkgpart); - return; - } - - my $sth = $sdbh->prepare( - "SELECT * FROM part_pkg WHERE pkgpart = $src_pkgpart" - ) or die $sdbh->errstr; - - $sth->execute or die $sth->errstr; - - my $part_pkg_row = $sth->fetchrow_hashref - or die "cust_pkg.pkgpart missing in part_pkg?!"; - - my $hashref = { - %{ $part_pkg_row }, - 'pkgpart' => '', - }; - my $src_classnum = $part_pkg_row->{'classnum'}; - $hashref->{'classnum'} = $map{'pkg_class'}->{ $src_classnum } - if $src_classnum; - - my $part_pkg = new FS::part_pkg $hashref; - - #$part_pkg->setuptax('') if $part_pkg->setuptax =~ /^\s+$/; - #$part_pkg->recurtax('') if $part_pkg->recurtax =~ /^\s+$/; - - my $error = $part_pkg->insert( 'options' => {} ); - die "*** FATAL: error importing part_pkg src pkgpart $src_pkgpart ". - ": $error" - if $error; - - $map{ 'part_pkg' }->{ $part_pkg_row->{'pkgpart'} } = $part_pkg->pkgpart; - - # part_pkg_option - import_table( 'part_pkg_option', - 'pkgpart' => $src_pkgpart, - 'search' => 'pkgpart', - 'map' => 'part_pkg', - ); - - my $osth = $sdbh->prepare( - "SELECT * FROM part_pkg_option WHERE pkgpart = $src_pkgpart" - ) or die $sdbh->errstr; - - # pkg_svc, part_svc, part_svc_column - import_table( 'pkg_svc', - 'pkgpart' => $src_pkgpart, - 'search' => 'pkgpart', - 'map' => 'part_pkg', - 'preinsert_callback' => sub { - - my($row, $object) = @_; - my $src_svcpart = $row->{'svcpart'} or die "wtf2"; - my $dest_svcpart = $map{'part_svc'}->{$src_svcpart}; - if ( $dest_svcpart ) { - $object->svcpart($dest_svcpart); - return; - } - - my $sth = $sdbh->prepare( - "SELECT * FROM part_svc WHERE svcpart = $src_svcpart" - ) or die $sdbh->errstr; - - $sth->execute or die $sth->errstr; - - my $part_svc_row = $sth->fetchrow_hashref - or die "svcpart missing in part_svc?!"; - - my $hashref = { - %{ $part_svc_row }, - 'svcpart' => '', - }; - - my $part_svc = new FS::part_svc $hashref; - $part_svc->disabled('') if $part_svc->disabled =~ /^\s+$/; - my $error = $part_svc->insert; - die "*** FATAL: error importing part_svc src svcpart $src_svcpart ". - ": $error" - if $error; - - $map{ 'part_svc' }->{ $part_svc_row->{'svcpart'} } = $part_svc->svcpart; - - # part_svc_column - import_table( 'part_svc_column', - 'svcpart' => $src_svcpart, - 'search' => 'svcpart', - 'map' => 'part_svc', - 'preinsert_callback' => sub { - my($row, $object) = @_; - if ( $object->columnname eq 'domsvc' ) { - $object->columnvalue( $map{'_DOMSVC'}->{ $object->columnvalue } ); - } - }, - ); - - #what we came here for in the first place - $object->svcpart( $part_svc->svcpart ); - - } - ); - - #what we came here for in the first place - $object->pkgpart( $part_pkg->pkgpart ); - - }, - - 'post_callback' => sub { - #my( $src_pkgnum, $dst_pkgnum ) = @_; - my $src_pkgnum = shift; - - #XXX grr... action makes this very hard... - ## cust_pkg_reason (shit, and bring in/remap reasons) - #import_table( 'cust_pkg_reason', - # 'pkgnum' => $src_pkgnum, - # 'search' => 'pkgnum', - # 'map' => 'cust_pkg', - # 'map2' => 'reason', - # 'map2key' => 'reasonnum', - # ); - - #cust_svc - import_table( 'cust_svc', - 'pkgnum' => $src_pkgnum, - 'search' => 'pkgnum', - 'map' => 'cust_pkg', - 'map2' => 'part_svc', - 'map2key' => 'svcpart', - 'post_callback' => sub { - #my( $src_svcnum, $dst_svcnum ) = @_; - my $src_svcnum = shift; - - #svc_domain - import_table( 'svc_domain', - 'svcnum' => $src_svcnum, - 'search' => 'svcnum', - 'map' => 'cust_svc', - 'noblank_primary' => 1, - ); - - #svc_acct - import_table( 'svc_acct', - 'svcnum' => $src_svcnum, - 'search' => 'svcnum', - 'map' => 'cust_svc', - 'noblank_primary' => 1, - 'map2' => 'svc_acct_pop', - 'map2key' => 'popnum', - #'map3' => 'svc_domain', - 'map3' => '_DOMSVC', - 'map3key' => 'domsvc', - ); - - #radius_usergroup - import_table( 'radius_usergroup', - 'svcnum' => $src_svcnum, - 'search' => 'svcnum', - 'map' => 'cust_svc', - ); - - #other svc_ tables not in old db - - }, - ); - - }, - - - - - ); - # end of cust_pkg (part_pkg, part_svc, etc.) - - # cust_bill (invnum move) - import_table( 'cust_bill', 'custnum' => $src_custnum, - 'preinsert_callback' => sub { - my($row, $object) = @_; - $object->agent_invid( $row->{'invnum'} ); - }, - 'post_callback' => sub { - my( $src_invnum, $dst_invnum ) = @_; - #my $src_invnum = shift; - - # cust_bill_pkg ( map invnum and pkgnum... ) - import_table( 'cust_bill_pkg', - 'invnum' => $src_invnum, - 'search' => 'invnum', - 'map' => 'cust_bill', - 'map2' => 'cust_pkg', - 'map2key' => 'pkgnum', - 'post_callback' => sub { - my $src_billpkgnum = shift; - - import_table( 'cust_bill_pkg_detail', - 'billpkgnum' => $src_billpkgnum, - 'search' => 'billpkgnum', - 'map' => 'cust_bill_pkg', - 'addl_from' => 'left join cust_bill_pkg using ( invnum, pkgnum )', - ); - - }, - ); - - # cust_credit_bill (map invnum and crednum... ) - import_table( 'cust_credit_bill', - 'invnum' => $src_invnum, - 'search' => 'invnum', - 'map' => 'cust_bill', - 'map2' => 'cust_credit', - 'map2key' => 'crednum', - 'post_callback' => sub { - my $src_creditbillnum = shift; - #map creditbillnum and billpkgnum - import_table( 'cust_credit_bill_pkg', - 'creditbillnum' => $src_creditbillnum, - 'search' => 'creditbillnum', - 'map' => 'cust_credit_bill', - 'map2' => 'cust_bill_pkg', - 'map2key' => 'billpkgnum', - ); - - }, - ); - - # cust_bill_pay (map invnum and paynum...) - import_table( 'cust_bill_pay', - 'invnum' => $src_invnum, - 'search' => 'invnum', - 'map' => 'cust_bill', - 'map2' => 'cust_pay', - 'map2key' => 'paynum', - 'post_callback' => sub { - my $src_billpaynum = shift; - #map billpaynum and billpkgnum - import_table( 'cust_bill_pay_pkg', - 'billpaynum' => $src_billpaynum, - 'search' => 'billpaynum', - 'map' => 'cust_bill_pay', - 'map2' => 'cust_bill_pkg', - 'map2key' => 'billpkgnum', - ); - }, - ); - - #need to do something about events. mark initial stuff as done - foreach my $eventpart ( @{ $eventparts{$cust_main->payby} } ) { - - my $cust_event = new FS::cust_event { - 'eventpart' => $eventpart, - 'tablenum' => $dst_invnum, - '_date' => time, # XXX something? probably not - 'status' => 'done', - }; - - my $error = $cust_event->insert; - die "*** FATAL: error inserting cust_event for eventpart $eventpart,". - " tablenum (invnum) $dst_invnum: $error" - if $error; - - } - - }, - ); - - # --- - - # (not in old db: cust_pkg_detail) - # (not used in old db: cust_bill_pay_batch, cust_pkg_option) - - # --- - - # (not in old db: cust_bill_pkg_display, cust_bill_pkg_tax_location, - # cust_bill_pkg_tax_rate_location, cust_tax_adjustment, cust_svc_option, ) - # (not used in old db: cust_tax_exempt_pkg) - - #do this last, so no notices go out - import_table( 'cust_main_invoice', 'custnum' => $src_custnum ); - - #dbh->commit or die dbh->errstr; - warn "customer ". $cust_main->custnum. " inserted\n"; - #exit; - -} - - -warn "import successful!\n"; -if ( $DRY ) { - warn "rolling back (dry run)\n"; - dbh->rollback or die dbh->errstr; - warn "rolled back\n" -} else { - warn "commiting\n"; - dbh->commit or die dbh->errstr; - warn "committed\n"; -} - -sub import_table { - my( $table, %opt ) = @_; - - eval "use FS::$table;"; - die $@ if $@; - - my $map = $opt{'map'} || 'cust_main'; - my $search = $opt{'search'} || 'custnum'; - - $opt{'insert_opts'} ||= []; - - my $primary_key = dbdef->table($table)->primary_key; - - my $addl_from = defined($opt{'addl_from'}) ? $opt{'addl_from'} : ''; - - my $sth = $sdbh->prepare( - "SELECT * FROM $table $addl_from ". - ( $opt{'nomap'} ? '' : " WHERE $search = ". $opt{$search} ) - ) or die $sdbh->errstr; - - $sth->execute or die "(searching $table): ". $sth->errstr; - - while ( my $row = $sth->fetchrow_hashref ) { - #my $src_custnum = $customerrow->{'custnum'}; - - my $hashref = { %$row }; - $hashref->{$primary_key} = '' - unless $opt{'noblank_primary'}; - $hashref->{ $search } = $map{$map}->{ $row->{$search} } - unless $opt{'nomap'}; - - if ( $opt{'map2'} ) { - my $key2 = $opt{'map2key'}; - $hashref->{$key2} = $map{ $opt{'map2'} }->{ $row->{$key2} } - unless $opt{map2key} eq 'pkgnum' && ( $row->{$key2} eq '0' - || $row->{$key2} eq '-1' - ) - or ! defined($row->{$key2}) - or $row->{$key2} eq ''; - #warn "map $opt{map2}.$opt{map2key}: ". $row->{$key2}. " to ". $map{ $opt{'map2'} }->{ $row->{$key2} }; - } - - if ( $opt{'map3'} ) { - my $key3 = $opt{'map3key'}; - $hashref->{$key3} = $map{ $opt{'map3'} }->{ $row->{$key3} }; - } - - my $object = eval "new FS::$table \$hashref;"; - die $@ if $@; - - &{ $opt{preinsert_callback} }( $row, $object ) - if $opt{preinsert_callback}; - - my $error = $object->insert( @{ $opt{'insert_opts'} } ); - if ( $error ) { - warn "*** WARNING: error importing $table src $primary_key ". $row->{$primary_key}. ": $error"; - next; - } - - $map{ $table }->{ $row->{$primary_key} } = $object->get($primary_key); - - &{ $opt{post_callback} }( $row->{$primary_key}, $object->get($primary_key) ) - if $opt{post_callback}; - - } - -} - -1; - |