3 #script to move customers from one installation to another
4 # script is kinda-specific to a somewhat old source installation (1.7? older?)
5 # target installation has to be 1.9 (after 9/2009)
10 use FS::UID qw( adminsuidsetup dbh );
11 use FS::Schema qw( dbdef );
12 use FS::Record qw( qsearchs );
17 use FS::cust_bill_ApplicationCommon;
22 #ssh -p 2222 -L 1080:66.209.32.4:7219 -L 5454:localhost:5432 66.209.32.4
24 #my $source_datasrc = 'DBI:Pg:host=66.209.32.4;dbname=freeside;sslmode=require';
25 my $source_datasrc = 'DBI:Pg:host=localhost;port=5454;dbname=freeside';
26 my $source_user = 'readonly';
29 my @source_agents = ( 2, 7, 3, 4, 5, 1 );
32 my $dest_agent_typenum = 1; #?
34 my $dest_refnum = 1; #XXX
36 my $dest_legacy_credit_reasontype = 4;
38 my $dest_pkg_classnum = 1;
43 or die "Usage:\n (edit variables at top of script and then)\n".
44 " move-customers user\n";
47 $sdbh = DBI->connect($source_datasrc, $source_user, $source_pw)
50 $sdbh->{ChopBlanks} = 1;
52 import_table('pkg_class', 'nomap' => 1);
54 my $agent_sth = $sdbh->prepare(
55 'SELECT * FROM agent WHERE agentnum IN ( '. join(',', @source_agents ). ')'
56 ) or die $sdbh->errstr;
58 $agent_sth->execute or die $agent_sth->errstr;
62 $FS::cust_main::ignore_expired_card = 1;
63 $FS::cust_main::ignore_expired_card = 1;
65 $FS::part_pkg::skip_pkg_svc_hack = 1;
66 $FS::part_pkg::skip_pkg_svc_hack = 1;
68 $FS::cust_bill_ApplicationCommon::skip_apply_to_lineitems_hack = 1;
69 $FS::cust_bill_ApplicationCommon::skip_apply_to_lineitems_hack = 1;
71 while ( my $agentrow = $agent_sth->fetchrow_hashref ) {
73 my $src_agent = $agentrow->{'agent'};
75 warn "importing customers for $src_agent\n";
77 my $agent = qsearchs('agent', { 'agent' => $src_agent } );
81 warn " using existing agentnum ". $agent->agentnum. "\n";
84 warn "DELETING ALL CUSTOMERS OF $src_agent locally \n";
86 foreach my $statement (
87 'DELETE FROM cust_main WHERE agentnum = '. $agent->agentnum,
88 ( map { "DELETE FROM $_
89 WHERE 0 = ( SELECT COUNT(*) FROM cust_main
90 WHERE cust_main.custnum = $_.custnum )
101 #part_pkg, pkg_svc, part_svc, part_svc_column
106 my $sth = dbh->prepare($statement) or die dbh->errstr;
107 $sth->execute or die $sth->errstr;
111 dbh->commit or die dbh->errstr;
117 warn " creating new agent...\n";
119 $agent = new FS::agent { 'agent' => $src_agent,
120 'typenum' => $dest_agent_typenum };
121 my $error = $agent->insert;
122 die $error if $error;
124 warn " agentnum ". $agent->agentnum. "\n";
128 my $customer_sth = $sdbh->prepare(
129 'SELECT * FROM cust_main WHERE agentnum = '. $agentrow->{'agentnum'}
130 ) or die $sdbh->errstr;
132 $customer_sth->execute or die $customer_sth->errstr;
134 while ( my $customerrow = $customer_sth->fetchrow_hashref ) {
136 # warn Dumper($customerrow);
137 my $src_custnum = $customerrow->{'custnum'};
139 warn " $src_custnum has referral_custnum ". $customerrow->{'referral_custnum'}
140 if $customerrow->{'referral_custnum'};
142 my $cust_main = new FS::cust_main {
145 'referral_custnum' => '', #restore afterwords?
146 'refnum' => $dest_refnum,
147 'agentnum' => $agent->agentnum,
148 'agent_custid' => $src_custnum,
151 #$cust_main->ship_country('') if $cust_main->ship_country eq ' ';
152 #$cust_main->tax('') if $cust_main->tax =~ /^\s+$/;
154 my $error = $cust_main->insert;
156 warn "*** WARNING: error importing customer src custnum $src_custnum: $error";
158 warn Dumper($cust_main) if $src_custnum == 6854;
162 warn "inserting dest customer ". $cust_main->custnum. " for $src_custnum\n";
164 $map{'cust_main'}->{$src_custnum} = $cust_main->custnum;
166 #easy direct cust_main relations:
168 #XXX ivan showing up as cust_pay otaker? just deal?
170 foreach my $table ( qw(
174 import_table( $table, 'custnum' => $src_custnum );
177 # crap, cust_credit.reason is text in old db
178 #*** WARNING: error importing cust_credit src crednum 2200: failed to set reason for [ FS::cust_credit ]: at ./move-customers line 232.
179 import_table( 'cust_credit', 'custnum' => $src_custnum,
180 'insert_opts' => [ 'reason_type' => $dest_legacy_credit_reasontype ],
181 'preinsert_callback' => sub {
182 my($row, $object) = @_;
183 $object->reason('(none)') if $object->get('reason') =~ /^\s*$/;
187 import_table( 'cust_refund', 'custnum' => $src_custnum,
188 'post_callback' => sub {
189 #my( $src_refundnum, $dst_refundnum ) = @_;
190 my $src_refundnum = shift;
192 # cust_credit_refund (map refundnum and crednum...)
193 import_table( 'cust_credit_refund',
194 'refundnum' => $src_refundnum,
195 'search' => 'refundnum',
196 'map' => 'cust_refund',
197 'map2' => 'cust_credit',
198 'map2key' => 'crednum',
201 # cust_pay_refund (map refundnum and paynum...)
202 import_table( 'cust_pay_refund',
203 'refundnum' => $src_refundnum,
204 'search' => 'refundnum',
205 'map' => 'cust_refund',
206 'map2' => 'cust_pay',
207 'map2key' => 'paynum',
213 # dunno what's up with this (ship_country ' ', fixed)
214 #*** WARNING: error importing customer src custnum 6854: Illegal (name) (error code illegal_name) ship_last: at ./move-customers line 129.
216 # XXX cust_pay_void (something w/ paynum??? huh) or just deal? there's only 110
218 # (not in old db: cust_attachment, cust_statement, cust_location,
219 # cust_main_exemption, cust_pay_pending )
220 # (not used in old db: cust_pay_batch, cust_tax_exempt)
221 # (not useful to migrate: queue)
223 #werid direct cust_main relations:
225 # cust_pkg (part_pkg, part_svc, etc.)
226 import_table( 'cust_pkg', 'custnum' => $src_custnum,
227 'preinsert_callback' => sub {
228 my($row, $object) = @_;
229 my $src_pkgpart = $row->{'pkgpart'} or die "wtf";
230 my $dest_pkgpart = $map{'part_pkg'}->{$src_pkgpart};
231 if ( $dest_pkgpart ) {
232 $object->pkgpart($dest_pkgpart);
236 my $sth = $sdbh->prepare(
237 "SELECT * FROM part_pkg WHERE pkgpart = $src_pkgpart"
238 ) or die $sdbh->errstr;
240 $sth->execute or die $sth->errstr;
242 my $part_pkg_row = $sth->fetchrow_hashref
243 or die "cust_pkg.pkgpart missing in part_pkg?!";
249 my $src_classnum = $part_pkg_row->{'classnum'};
250 $hashref->{'classnum'} = $map{'pkg_class'}->{ $src_classnum }
253 my $part_pkg = new FS::part_pkg $hashref;
255 #$part_pkg->setuptax('') if $part_pkg->setuptax =~ /^\s+$/;
256 #$part_pkg->recurtax('') if $part_pkg->recurtax =~ /^\s+$/;
258 my $error = $part_pkg->insert( 'options' => {} );
259 die "*** FATAL: error importing part_pkg src pkgpart $src_pkgpart ".
263 $map{ 'part_pkg' }->{ $part_pkg_row->{'pkgpart'} } = $part_pkg->pkgpart;
266 import_table( 'part_pkg_option',
267 'pkgpart' => $src_pkgpart,
268 'search' => 'pkgpart',
272 my $osth = $sdbh->prepare(
273 "SELECT * FROM part_pkg_option WHERE pkgpart = $src_pkgpart"
274 ) or die $sdbh->errstr;
276 # pkg_svc, part_svc, part_svc_column
277 import_table( 'pkg_svc',
278 'pkgpart' => $src_pkgpart,
279 'search' => 'pkgpart',
281 'preinsert_callback' => sub {
283 my($row, $object) = @_;
284 my $src_svcpart = $row->{'svcpart'} or die "wtf2";
285 my $dest_svcpart = $map{'part_svc'}->{$src_svcpart};
286 if ( $dest_svcpart ) {
287 $object->svcpart($dest_svcpart);
291 my $sth = $sdbh->prepare(
292 "SELECT * FROM part_svc WHERE svcpart = $src_svcpart"
293 ) or die $sdbh->errstr;
295 $sth->execute or die $sth->errstr;
297 my $part_svc_row = $sth->fetchrow_hashref
298 or die "svcpart missing in part_svc?!";
305 my $part_svc = new FS::part_svc $hashref;
306 $part_svc->disabled('') if $part_svc->disabled =~ /^\s+$/;
307 my $error = $part_svc->insert;
308 die "*** FATAL: error importing part_svc src svcpart $src_svcpart ".
312 $map{ 'part_svc' }->{ $part_svc_row->{'svcpart'} } = $part_svc->svcpart;
315 import_table( 'part_svc_column',
316 'svcpart' => $src_svcpart,
317 'search' => 'svcpart',
321 #what we came here for in the first place
322 $object->svcpart( $part_svc->svcpart );
327 #what we came here for in the first place
328 $object->pkgpart( $part_pkg->pkgpart );
332 # end of cust_pkg (part_pkg, part_svc, etc.)
334 # cust_bill (invnum move)
335 import_table( 'cust_bill', 'custnum' => $src_custnum,
336 'preinsert_callback' => sub {
337 my($row, $object) = @_;
338 $object->agent_invid( $row->{'invnum'} );
340 'post_callback' => sub {
341 #my( $src_invnum, $dst_invnum ) = @_;
342 my $src_invnum = shift;
344 # cust_bill_pkg ( map invnum and pkgnum... )
345 import_table( 'cust_bill_pkg',
346 'invnum' => $src_invnum,
347 'search' => 'invnum',
348 'map' => 'cust_bill',
349 'map2' => 'cust_pkg',
350 'map2key' => 'pkgnum',
351 'post_callback' => sub {
352 my $src_billpkgnum = shift;
354 import_table( 'cust_bill_pkg_detail',
355 'billpkgnum' => $src_billpkgnum,
356 'search' => 'billpkgnum',
357 'map' => 'cust_bill_pkg',
358 'addl_from' => 'left join cust_bill_pkg using ( invnum, pkgnum )',
364 # cust_credit_bill (map invnum and crednum... )
365 import_table( 'cust_credit_bill',
366 'invnum' => $src_invnum,
367 'search' => 'invnum',
368 'map' => 'cust_bill',
369 'map2' => 'cust_credit',
370 'map2key' => 'crednum',
371 'post_callback' => sub {
372 my $src_creditbillnum = shift;
373 #map creditbillnum and billpkgnum
374 import_table( 'cust_credit_bill_pkg',
375 'creditbillnum' => $src_creditbillnum,
376 'search' => 'creditbillnum',
377 'map' => 'cust_credit_bill',
378 'map2' => 'cust_bill_pkg',
379 'map2key' => 'billpkgnum',
385 # cust_bill_pay (map invnum and paynum...)
386 import_table( 'cust_bill_pay',
387 'invnum' => $src_invnum,
388 'search' => 'invnum',
389 'map' => 'cust_bill',
390 'map2' => 'cust_pay',
391 'map2key' => 'paynum',
392 'post_callback' => sub {
393 my $src_billpaynum = shift;
394 #map billpaynum and billpkgnum
395 import_table( 'cust_bill_pay_pkg',
396 'billpaynum' => $src_billpaynum,
397 'search' => 'billpaynum',
398 'map' => 'cust_bill_pay',
399 'map2' => 'cust_bill_pkg',
400 'map2key' => 'billpkgnum',
409 # XXX last of the stuff to import...
411 # cust_pkg_reason (shit, and bring in/remap reasons)
417 # (rest not in old db)
418 # svc_acct_pop??? looks like it
420 # (not in old db: cust_pkg_detail)
421 # (not used in old db: cust_bill_pay_batch, cust_pkg_option)
425 # (not in old db: cust_bill_pkg_display, cust_bill_pkg_tax_location,
426 # cust_bill_pkg_tax_rate_location, cust_tax_adjustment, cust_svc_option, )
427 # (not used in old db: cust_tax_exempt_pkg)
430 #need to do something about events. mark initial stuff as done or something?
431 # what else? that's it?
433 #do this last, so no notices go out
434 import_table( 'cust_main_invoice', 'custnum' => $src_custnum );
436 #dbh->commit or die dbh->errstr;
437 warn "customer ". $cust_main->custnum. " inserted\n";
445 warn "import successful!\n"
447 warn "rolling back (dry run)\n";
448 dbh->rollback or die dbh->errstr;
452 dbh->commit or die dbh->errstr;
457 my( $table, %opt ) = @_;
459 eval "use FS::$table;";
462 my $map = $opt{'map'} || 'cust_main';
463 my $search = $opt{'search'} || 'custnum';
465 $opt{'insert_opts'} ||= [];
467 my $primary_key = dbdef->table($table)->primary_key;
469 my $addl_from = defined($opt{'addl_from'}) ? $opt{'addl_from'} : '';
471 my $sth = $sdbh->prepare(
472 "SELECT * FROM $table $addl_from ".
473 ( $opt{'nomap'} ? '' : " WHERE $search = ". $opt{$search} )
474 ) or die $sdbh->errstr;
476 $sth->execute or die "(searching $table): ". $sth->errstr;
478 while ( my $row = $sth->fetchrow_hashref ) {
479 #my $src_custnum = $customerrow->{'custnum'};
485 $hashref->{ $search } = $map{$map}->{ $row->{$search} }
486 unless $opt{'nomap'};
488 if ( $opt{'map2'} ) {
489 my $key2 = $opt{'map2key'};
490 $hashref->{$key2} = $map{ $opt{'map2'} }->{ $row->{$key2} }
491 unless $opt{map2key} eq 'pkgnum' && $row->{$key2} eq '0';
492 #warn "map $opt{map2}.$opt{map2key}: ". $row->{$key2}. " to ". $map{ $opt{'map2'} }->{ $row->{$key2} };
495 my $object = eval "new FS::$table \$hashref;";
498 &{ $opt{preinsert_callback} }( $row, $object )
499 if $opt{preinsert_callback};
501 my $error = $object->insert( @{ $opt{'insert_opts'} } );
503 warn "*** WARNING: error importing $table src $primary_key ". $row->{$primary_key}. ": $error";
507 $map{ $table }->{ $row->{$primary_key} } = $object->get($primary_key);
509 &{ $opt{post_callback} }( $row->{$primary_key}, $object->get($primary_key) )
510 if $opt{post_callback};