From a58bd9386e1e5fa1cdd16936fc29093ed67714db Mon Sep 17 00:00:00 2001 From: ivan Date: Mon, 24 Aug 2009 06:39:58 +0000 Subject: [PATCH] (start of) customer move script, RT#5351 --- FS/FS/cust_bill_ApplicationCommon.pm | 6 +- FS/FS/part_pkg.pm | 45 ++-- bin/move-customers | 497 +++++++++++++++++++++++++++++++++++ 3 files changed, 527 insertions(+), 21 deletions(-) create mode 100755 bin/move-customers diff --git a/FS/FS/cust_bill_ApplicationCommon.pm b/FS/FS/cust_bill_ApplicationCommon.pm index ec694ca58..d4627c0b4 100644 --- a/FS/FS/cust_bill_ApplicationCommon.pm +++ b/FS/FS/cust_bill_ApplicationCommon.pm @@ -1,7 +1,7 @@ package FS::cust_bill_ApplicationCommon; use strict; -use vars qw( @ISA $DEBUG $me ); +use vars qw( @ISA $DEBUG $me $skip_apply_to_lineitems_hack ); use List::Util qw(min); use FS::Schema qw( dbdef ); use FS::Record qw( qsearch qsearchs dbh ); @@ -11,6 +11,8 @@ use FS::Record qw( qsearch qsearchs dbh ); $DEBUG = 0; $me = '[FS::cust_bill_ApplicationCommon]'; +$skip_apply_to_lineitems_hack = 0; + =head1 NAME FS::cust_bill_ApplicationCommon - Base class for bill application classes @@ -113,6 +115,8 @@ Auto-applies this invoice application to specific line items, if possible. sub apply_to_lineitems { my $self = shift; + return '' if $skip_apply_to_lineitems_hack; + my @apply = (); my $conf = new FS::Conf; diff --git a/FS/FS/part_pkg.pm b/FS/FS/part_pkg.pm index 3ee9e7f01..287453f3c 100644 --- a/FS/FS/part_pkg.pm +++ b/FS/FS/part_pkg.pm @@ -1,7 +1,7 @@ package FS::part_pkg; use strict; -use vars qw( @ISA %plans $DEBUG $setup_hack ); +use vars qw( @ISA %plans $DEBUG $setup_hack $skip_pkg_svc_hack ); use Carp qw(carp cluck confess); use Scalar::Util qw( blessed ); use Time::Local qw( timelocal_nocheck ); @@ -23,6 +23,7 @@ use FS::part_pkg_link; @ISA = qw( FS::m2m_Common FS::option_Common ); $DEBUG = 0; $setup_hack = 0; +$skip_pkg_svc_hack = 0; =head1 NAME @@ -217,26 +218,30 @@ sub insert { } } - warn " inserting pkg_svc records" if $DEBUG; - my $pkg_svc = $options{'pkg_svc'} || {}; - foreach my $part_svc ( qsearch('part_svc', {} ) ) { - my $quantity = $pkg_svc->{$part_svc->svcpart} || 0; - my $primary_svc = - ( $options{'primary_svc'} && $options{'primary_svc'}==$part_svc->svcpart ) - ? 'Y' - : ''; - - my $pkg_svc = new FS::pkg_svc( { - 'pkgpart' => $self->pkgpart, - 'svcpart' => $part_svc->svcpart, - 'quantity' => $quantity, - 'primary_svc' => $primary_svc, - } ); - my $error = $pkg_svc->insert; - if ( $error ) { - $dbh->rollback if $oldAutoCommit; - return $error; + unless ( $skip_pkg_svc_hack ) { + + warn " inserting pkg_svc records" if $DEBUG; + my $pkg_svc = $options{'pkg_svc'} || {}; + foreach my $part_svc ( qsearch('part_svc', {} ) ) { + my $quantity = $pkg_svc->{$part_svc->svcpart} || 0; + my $primary_svc = + ( $options{'primary_svc'} && $options{'primary_svc'}==$part_svc->svcpart ) + ? 'Y' + : ''; + + my $pkg_svc = new FS::pkg_svc( { + 'pkgpart' => $self->pkgpart, + 'svcpart' => $part_svc->svcpart, + 'quantity' => $quantity, + 'primary_svc' => $primary_svc, + } ); + my $error = $pkg_svc->insert; + if ( $error ) { + $dbh->rollback if $oldAutoCommit; + return $error; + } } + } if ( $options{'cust_pkg'} ) { diff --git a/bin/move-customers b/bin/move-customers new file mode 100755 index 000000000..40219551e --- /dev/null +++ b/bin/move-customers @@ -0,0 +1,497 @@ +#!/usr/bin/perl -w + +#script to move customers from one installation to another +# 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; + +my $DANGEROUS = 1; + +#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 $dest_agent_typenum = 1; #? + +my $dest_refnum = 1; #XXX + +my $dest_legacy_credit_reasontype = 4; + +my $dest_pkg_classnum = 1; + +#-- + +my $user = shift + or die "Usage:\n (edit variables at top of script and then)\n". + " move-customers user\n"; +adminsuidsetup $user; + +$sdbh = DBI->connect($source_datasrc, $source_user, $source_pw) + or die $DBI::errstr; + +import_table('pkg_class', 'nomap' => 1); + +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; + +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 ) { + + my $src_agent = $agentrow->{'agent'}; + + warn "importing customers for $src_agent\n"; + + my $agent = qsearchs('agent', { 'agent' => $src_agent } ); + + 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 + ) + ) + #part_pkg, pkg_svc, part_svc, part_svc_column + #pkg_class + ) { + + #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"; + + } + + my $customer_sth = $sdbh->prepare( + 'SELECT * FROM cust_main WHERE agentnum = '. $agentrow->{'agentnum'} + ) 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' => $agent->agentnum, + 'agent_custid' => $src_custnum, + }; + + $cust_main->ship_country('') if $cust_main->ship_country eq ' '; + + 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; + + #easy direct cust_main relations: + + #XXX ivan showing up as cust_pay otaker? just deal? + + foreach my $table ( qw( + cust_main_note + cust_pay + ) ) { + import_table( $table, 'custnum' => $src_custnum ); + } + + # 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->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' => 'refundum', + 'map' => 'cust_refund', + 'map2' => 'cust_credit', + 'map2key' => 'crednum', + ); + + # cust_pay_refund (map refundnum and paynum...) + import_table( 'cust_pay_refund', + 'refundnum' => $src_refundnum, + 'search' => 'refundum', + '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. + + # XXX cust_pay_void (something w/ paynum??? huh) or just deal? there's only 110 + + # (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; + 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', + ); + + #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 ); + + }, + ); + # 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', + ); + }, + ); + }, + ); + + # --- + + # 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) + + # --- + + # (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) + + #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 ); + + #dbh->commit or die dbh->errstr; + warn "customer ". $cust_main->custnum. " inserted\n"; + #exit; + + } + +} + +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 }, + $primary_key => '', + }; + $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'; + #warn "map $opt{map2}.$opt{map2key}: ". $row->{$key2}. " to ". $map{ $opt{'map2'} }->{ $row->{$key2} }; + } + + 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; + -- 2.11.0