X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=blobdiff_plain;f=bin%2Fmove-customers;h=a7ea19781c1ed96977c700f3fc591a08fb672908;hp=1da39b89e2364b9495fb21246645a5cfa2af6a54;hb=3fa5501cf8f3344360cb2a795ab58711d96540f5;hpb=426211afa7e8c85bde1473f53e15d166df60461a diff --git a/bin/move-customers b/bin/move-customers index 1da39b89e..a7ea19781 100755 --- a/bin/move-customers +++ b/bin/move-customers @@ -1,6 +1,7 @@ #!/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) @@ -15,8 +16,13 @@ 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 = 1; +my $DANGEROUS = 0; +my $DRY = 0; #ssh -p 2222 -L 1080:66.209.32.4:7219 -L 5454:localhost:5432 66.209.32.4 @@ -25,28 +31,91 @@ 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 $dest_agent_typenum = 1; #? - -my $dest_refnum = 1; #XXX - -my $dest_legacy_credit_reasontype = 4; - -my $dest_pkg_classnum = 1; +#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 ). ')' @@ -54,16 +123,6 @@ my $agent_sth = $sdbh->prepare( $agent_sth->execute or die $agent_sth->errstr; -my %map = (); - -$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; while ( my $agentrow = $agent_sth->fetchrow_hashref ) { @@ -71,7 +130,7 @@ while ( my $agentrow = $agent_sth->fetchrow_hashref ) { warn "importing customers for $src_agent\n"; - my $agent = qsearchs('agent', { 'agent' => $src_agent } ); + my $agent = qsearchs('agent', { 'agent' => $src_agent, 'disabled' => '' } ); if ( $agent ) { @@ -95,8 +154,9 @@ while ( my $agentrow = $agent_sth->fetchrow_hashref ) { cust_refund ) ) + #pkg_class, part_pkg_pop #part_pkg, pkg_svc, part_svc, part_svc_column - #pkg_class + #XXX more... does it matter? ) { #warn $statement; @@ -122,13 +182,22 @@ while ( my $agentrow = $agent_sth->fetchrow_hashref ) { } - my $customer_sth = $sdbh->prepare( - 'SELECT * FROM cust_main WHERE agentnum = '. $agentrow->{'agentnum'} - ) or die $sdbh->errstr; + $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; - $customer_sth->execute or die $customer_sth->errstr; +while ( my $customerrow = $customer_sth->fetchrow_hashref ) { - while ( my $customerrow = $customer_sth->fetchrow_hashref ) { #use Data::Dumper; # warn Dumper($customerrow); my $src_custnum = $customerrow->{'custnum'}; @@ -141,12 +210,12 @@ while ( my $agentrow = $agent_sth->fetchrow_hashref ) { 'custnum' => '', 'referral_custnum' => '', #restore afterwords? 'refnum' => $dest_refnum, - 'agentnum' => $agent->agentnum, + '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+$/; + #$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 ) { @@ -160,16 +229,26 @@ while ( my $agentrow = $agent_sth->fetchrow_hashref ) { $map{'cust_main'}->{$src_custnum} = $cust_main->custnum; - #easy direct cust_main relations: + #now import the relations, easy and hard: - #XXX ivan showing up as cust_pay otaker? just deal? + import_table( 'cust_main_note', 'custnum' => $src_custnum ); - foreach my $table ( qw( - cust_main_note - cust_pay - ) ) { - import_table( $table, '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. @@ -177,7 +256,7 @@ while ( my $agentrow = $agent_sth->fetchrow_hashref ) { 'insert_opts' => [ 'reason_type' => $dest_legacy_credit_reasontype ], 'preinsert_callback' => sub { my($row, $object) = @_; - $object->reason('(none)') if $object->get('reason') =~ /^\s*$/; + $object->set('reason', '(none)') if $object->get('reason') =~ /^\s*$/; }, ); @@ -210,7 +289,13 @@ while ( my $agentrow = $agent_sth->fetchrow_hashref ) { # 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. - # XXX cust_pay_void (something w/ paynum??? huh) or just deal? there's only 110 + # 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 ) @@ -248,6 +333,10 @@ while ( my $agentrow = $agent_sth->fetchrow_hashref ) { 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" @@ -309,6 +398,12 @@ while ( my $agentrow = $agent_sth->fetchrow_hashref ) { '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 @@ -321,6 +416,70 @@ while ( my $agentrow = $agent_sth->fetchrow_hashref ) { $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.) @@ -331,8 +490,8 @@ while ( my $agentrow = $agent_sth->fetchrow_hashref ) { $object->agent_invid( $row->{'invnum'} ); }, 'post_callback' => sub { - #my( $src_invnum, $dst_invnum ) = @_; - my $src_invnum = shift; + my( $src_invnum, $dst_invnum ) = @_; + #my $src_invnum = shift; # cust_bill_pkg ( map invnum and pkgnum... ) import_table( 'cust_bill_pkg', @@ -394,22 +553,29 @@ while ( my $agentrow = $agent_sth->fetchrow_hashref ) { ); }, ); + + #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; + + } + }, ); # --- - # XXX last of the stuff to import... - # & - # cust_pkg_reason (shit, and bring in/remap reasons) - # cust_svc - # then - # svc_acct - # radius_usergroup - # svc_domain - # (rest not in old db) - # svc_acct_pop??? looks like it - # # (not in old db: cust_pkg_detail) # (not used in old db: cust_bill_pay_batch, cust_pkg_option) @@ -419,10 +585,6 @@ while ( my $agentrow = $agent_sth->fetchrow_hashref ) { # cust_bill_pkg_tax_rate_location, cust_tax_adjustment, cust_svc_option, ) # (not used in old db: cust_tax_exempt_pkg) - #XXX then: - #need to do something about events. mark initial stuff as done or something? - # what else? that's it? - #do this last, so no notices go out import_table( 'cust_main_invoice', 'custnum' => $src_custnum ); @@ -430,8 +592,18 @@ while ( my $agentrow = $agent_sth->fetchrow_hashref ) { 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 { @@ -459,20 +631,28 @@ sub import_table { while ( my $row = $sth->fetchrow_hashref ) { #my $src_custnum = $customerrow->{'custnum'}; - my $hashref = { - %{ $row }, - $primary_key => '', - }; + 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'; + 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 $@;