From 0677db2866105b5a37e2a3b426999b84ab35f4b7 Mon Sep 17 00:00:00 2001 From: ivan Date: Wed, 14 May 2008 16:52:07 +0000 Subject: [PATCH] *** empty log message *** --- bin/import-optigold.pl | 723 +++++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 723 insertions(+) create mode 100755 bin/import-optigold.pl diff --git a/bin/import-optigold.pl b/bin/import-optigold.pl new file mode 100755 index 000000000..1284d8599 --- /dev/null +++ b/bin/import-optigold.pl @@ -0,0 +1,723 @@ +#!/usr/bin/perl -Tw + +use strict; +use DBI; +use HTML::TableParser; +use Date::Parse; +use Data::Dumper; +use FS::UID qw(adminsuidsetup); +use FS::cust_credit; +use FS::cust_main; +use FS::cust_pkg; +use FS::cust_svc; +use FS::svc_acct; +use FS::part_referral; +use FS::part_pkg; +use FS::Record qw(qsearch qsearchs); + +my $DEBUG = 0; + +my $dry_run = '0'; + +my $s_dbname = 'DBI:Pg:dbname=optigoldimport'; +my $s_dbuser = 'freeside'; +my $s_dbpass = ''; +my $extension = '.htm'; + +#my $d_dbuser = 'freeside'; +my $d_dbuser = 'enet'; +#my $d_dbuser = 'ivan'; + +#my $agentnum = 1; +my $agentnum = 13; +my $legacy_domain_svcnum = 1; +my $legacy_ppp_svcnum = 2; +my $legacy_email_svcnum = 3; +#my $legacy_broadband_svcnum = 4; +my $legacy_broadband_svcnum = 14; +#my $previous_credit_reasonnum = 1; +my $previous_credit_reasonnum = 1220; + + + +my $state = ''; #statemachine-ish +my $sourcefile; +my $s_dbh; +my $columncount; +my $rowcount; + +my @args = ( + { + id => 1, + hdr => \&header, + row => \&row, + start => \&start, + end => \&end, + }, + ); + + +$s_dbh = DBI->connect($s_dbname, $s_dbuser, $s_dbpass, + { 'AutoCommit' => 0, + 'ChopBlanks' => 1, + 'ShowErrorStatement' => 1 + } + ); + +foreach ( qw ( billcycle cust email product ) ) { + $sourcefile = $_; + + print "parsing $sourcefile\n"; + + die "bad file name" unless $sourcefile =~ /^\w+$/; + + $columncount = 0; + $rowcount = 0; + + my $c_sth = ''; + if ( $c_sth = $s_dbh->prepare("SELECT COUNT(*) FROM $sourcefile") ) { + if ( $c_sth->execute ) { + if ( $c_sth->fetchrow_arrayref->[0] ) { + warn "already have data in $sourcefile table; skipping"; + next; + } + } + } + + my $tp = new HTML::TableParser( \@args, { Decode => 1, Trim => 1, Chomp => 1 }); + $tp->parse_file($sourcefile.$extension) or die "failed"; + $s_dbh->commit or die $s_dbh->errstr; +# $s_dbh->disconnect; +} + + +sub start { + warn "start\n" if $DEBUG; + my $table_id = shift; + die "unexpected state change" unless $state eq ''; + die "unexpected table" unless $table_id eq '1'; + $state = 'table'; +} + +sub end { + warn "end\n" if $DEBUG; + my ($tbl_id, $line, $udata) = @_; + die "unexpected state change in header" unless $state eq 'rows'; + die "unexpected table" unless $tbl_id eq '1'; + $state = ''; +} + +sub header { + warn "header\n" if $DEBUG; + my ($tbl_id, $line, $cols, $udata) = @_; + die "unexpected state change in header" unless $state eq 'table'; + die "unexpected table" unless $tbl_id eq '1'; + $state = 'rows'; + + die "invalid column ". join (', ', grep { !/^[ \w\r]+$/ } @$cols) + if scalar(grep { !/^[ \w\r]+$/ } @$cols); + + my $sql = "CREATE TABLE $sourcefile ( ". + join(', ', map { s/[ \r]/_/g; "$_ varchar NULL" } @$cols). " )"; + $s_dbh->do($sql) or die "create table failed: ". $s_dbh->errstr; + $columncount = scalar( @$cols ); +} + +sub row { + warn "row\n" if $DEBUG; + my ($tbl_id, $line, $cols, $udata) = @_; + die "unexpected state change in row" unless $state eq 'rows'; + die "unexpected table" unless $tbl_id eq '1'; + + die "invalid number of columns: ". join(', ', @$cols) + unless (scalar(@$cols) == $columncount); + + my $sql = "INSERT INTO $sourcefile VALUES(". + join(', ', map { $s_dbh->quote($_) } @$cols). ")"; + $s_dbh->do($sql) or die "insert failed: ". $s_dbh->errstr; + $rowcount++; + warn "row $rowcount\n" unless ($rowcount % 1000); +} + + + +$FS::cust_main::import=1; +$FS::cust_pkg::disable_agentcheck = 1; + +my (%part_pkg_map) = (); +my (%referrals) = (); +my (%custid) = (); +my (%cancel) = (); +my (%susp) = (); +my (%adjo) = (); +my (%bill) = (); +my (%cust_pkg_map) = (); +my (%object_map) = (); +my (%package_cache) = (); +my $count; + +sub pkg_freq { + my ( $href ) = ( shift ); + $href->{'one_type_item'} + ? 0 + : int(eval "$href->{'months_credit'} + 0"); +} + +sub b_or { + my ( $field, $hash ) = ( shift, shift ); + $field = 'bill_'. $field + if $hash->{'billing_use'} eq 'Billing Address'; + $hash->{$field}; +} + +sub p_or { + my ( $field, $hash ) = ( shift, shift ); + $field = 'bill_'. $field + if $hash->{'billing_use'} eq 'Billing Address'; + my $ac = ( $hash->{$field. '_area_code'} + && $hash->{$field. '_area_code'} =~ /^\d{3}$/ ) + ? $hash->{$field. '_area_code'}. '-' + : '903-' # wtf? + ; + ( $hash->{$field} && $hash->{$field} =~ /^\d{3}-\d{4}$/) + ? $ac. $hash->{$field} + : ''; +} + +sub or_b { + my ( $field, $hash ) = ( shift, shift ); + $hash->{'billing_use'} eq 'Billing Address' ? $hash->{$field} : ''; +} + +sub or_p { + my ( $field, $hash ) = ( shift, shift ); + $hash->{'billing_use'} eq 'Billing Address' && $hash->{$field} =~ /^\d{3}-\d{4}$/ + ? ( $hash->{$field. '_area_code'} =~ /^\d{3}$/ + ? $hash->{$field. '_area_code'}. '-' + : '903-' # wtf? + ). $hash->{$field} + : ''; +} + +my %payby_map = ( '' => 'BILL', + 'None' => 'BILL', + 'Credit Card' => 'CARD', + 'Bank Debit' => 'CHEK', + 'Virtual Check' => 'CHEK', +); +sub payby { + $payby_map{ shift->{billing_type} }; +} + +sub ut_name_fixup { + my ($object, $field) = (shift, shift); + my $value = $object->getfield($field); + $value =~ s/[^\w \,\.\-\']/ /g; + $object->setfield($field, $value); +} + +sub ut_text_fixup { + my ($object, $field) = (shift, shift); + my $value = $object->getfield($field); + $value =~ s/[^\w \!\@\#\$\%\&\(\)\-\+\;\:\'\"\,\.\?\/\=\[\]]/ /g; + $object->setfield($field, $value); +} + +sub ut_state_fixup { + my ($object, $field) = (shift, shift); + my $value = $object->getfield($field); + $value = 'TX' if $value eq 'TTX'; + $object->setfield($field, $value); +} + +sub ut_zip_fixup { + my ($object, $field) = (shift, shift); + my $value = $object->getfield($field); + $value =~ s/[^-\d]//g; + $object->setfield($field, $value); +} + +my @tables = ( +part_pkg => { 'stable' => 'product', + 'mapping' => + { 'pkg' => sub { my $href = shift; + $href->{'description'} + ? $href->{'description'} + : $href->{'product_id'}; + }, + 'comment' => 'product_id', + 'freq' => sub { pkg_freq(shift) }, + 'recur' => sub { my $href = shift; + my $price = ( pkg_freq($href) + ? $href->{'unit_price'} + : 0 + ); + $price =~ s/[^\d.]//g; + $price = 0 unless $price; + sprintf("%.2f", $price); + }, + 'setuptax' => sub { my $href = shift; + $href->{'taxable'} ? '' : 'Y'; + }, + 'recurtax' => sub { my $href = shift; + $href->{'taxable'} ? '' : 'Y'; + }, + 'plan' => sub { 'flat' }, + 'disabled' => sub { 'Y' }, + 'pkg_svc' => sub { my $href = shift; + my $result = {}; + if (pkg_freq($href)){ + $result->{$legacy_ppp_svcnum} = 1; + $result->{$legacy_email_svcnum} = + $href->{emails_allowed} + if $href->{emails_allowed}; + } + }, + 'primary_svc'=> sub { pkg_freq(shift) + ? $legacy_ppp_svcnum + : '' + ; + }, + }, + 'fixup' => sub { my $part_pkg = shift; + my $row = shift; + return 1 unless $part_pkg->comment; + $package_cache{$part_pkg->comment} = $part_pkg; + 1; + }, + 'wrapup' => sub { foreach (keys %package_cache) { + my $part_pkg = $package_cache{$_}; + my $options = + { map { my $v = $part_pkg->$_; + $part_pkg->$_(''); + ($_ => $v); + } + qw (setup recur) + }; + my $error = + $part_pkg->insert(options=>$options); + die "Error inserting referral: $error" + if $error; + $count++ unless $error; + } + }, + }, +part_referral => { 'stable' => 'cust', + 'mapping' => + { 'agentnum' => sub { $agentnum }, + 'referral' => sub { my $r = shift->{'referred_from'}; + $referrals{$r} = 1; + }, + }, + 'fixup' => sub { 1 }, + 'wrapup' => sub { foreach (keys %referrals) { + my $part_referral = + new FS::part_referral( { + 'agentnum' => $agentnum, + 'referral' => $referrals{$_}, + } ); + my $error = $part_referral->insert; + die "Error inserting referral: $error" + if $error; + $count++ unless $error; + $referrals{$_} = $part_referral->refnum; + } + }, + }, +svc_acct => { 'stable' => 'cust', + 'mapping' => + { 'username' => 'login', + '_password' => 'password', + 'svcpart' => sub{ $legacy_ppp_svcnum }, + 'domsvc' => sub{ $legacy_domain_svcnum }, + 'status' => 'status', + }, + 'fixup' => sub { my $svc_acct = shift; + my $row = shift; + my $id = $row->{'master_account'} + ? 'slave:'. $row->{'customer_id'} + : $row->{'login'}; + my $status = $svc_acct->status; + if ( $status ne 'Current' + && $status ne 'On Hold' ) + { + $cancel{$id} = + str2time($row->{termination_date}); + warn "not creating (cancelled) svc_acct for " . + $svc_acct->username. "\n"; + return 1 + } + $susp{$id} = str2time($row->{hold_date}) + if $status eq 'On Hold'; + $adjo{$id} = str2time($row->{hold_date}) + if ( $status eq 'Current' && + $row->{hold_date} ); + $bill{$id} = + str2time($row->{expiration_date}); + ''; + }, + 'skey' => sub { my $svc_acct = shift; + my $row = shift; + my $id = $row->{'master_account'} + ? 'slave:'. $row->{'customer_id'} + : $row->{'login'}; + }, + }, +cust_main => { 'stable' => 'cust', + 'mapping' => + { 'agentnum' => sub { $agentnum }, + 'agent_custid' => sub { my $id = shift->{'customer_number'}; + if (exists($custid{$id})) { + $custid{$id}++; + $id. chr(64 + $custid{$id}); + }else{ + $custid{$id} = 0; + $id; + } + }, + 'last' => sub { b_or('last_name', shift) || ' ' }, + 'first' => sub { b_or('first_name', shift) || ' ' }, + 'stateid' => 'drivers_license_number', + 'signupdate' => sub { str2time(shift->{'creation_date'}) }, + 'company' => sub { b_or('company_name', shift) }, + 'address1' => sub { b_or('address', shift) || ' ' }, + 'city' => sub { b_or('city', shift) || 'Paris' }, + 'state' => sub { uc(b_or('state', shift)) || 'TX' }, + 'zip' => sub { b_or('zip_code', shift) || '75460' }, + 'country' => sub { 'US' }, + 'daytime' => sub { p_or('phone', shift) }, + 'night' => sub { p_or('phone_alternate_1', shift) }, + 'fax' => sub { p_or('fax', shift) }, + 'ship_last' => sub { or_b('last_name', shift) }, + 'ship_first' => sub { or_b('first_name', shift) }, + 'ship_company' => sub { or_b('company_name', shift) }, + 'ship_address1'=> sub { or_b('address', shift) }, + 'ship_city' => sub { or_b('city', shift) }, + 'ship_state' => sub { uc(or_b('state', shift)) }, + 'ship_zip' => sub { or_b('zip_code', shift) }, + 'ship_daytime' => sub { or_p('phone', shift) }, + 'ship_fax' => sub { or_p('fax', shift) }, + 'payby' => \&payby, + 'payinfo' => sub { my $hash = shift; + my $payby = payby($hash); + if ($payby eq 'CARD') { + $hash->{'credit_card_number_1'}. + $hash->{'credit_card_number_2'}. + $hash->{'credit_card_number_3'}. + $hash->{'credit_card_number_4'}; + }elsif ($payby eq 'CHEK') { + $hash->{'bank_account_number'}. + '@'. + $hash->{'bank_transit_number'}; + }elsif ($payby eq 'BILL') { + $hash->{'blanket_purchase_order_number'}; + }else{ + die "unexpected payby"; + } + }, + 'paycvv' => sub { my $hash = shift; + my $payby = payby($hash); + if ($payby eq 'CARD') { + $hash->{'credit_card_cvv_number'}; + }else{ + ''; + } + }, + 'paydate' => sub { my $hash = shift; + my $payby = payby($hash); + if ($payby eq 'CARD') { + '20'. + $hash->{'credit_card_exp_date_2'}. + '-'. + substr( + $hash->{'credit_card_exp_date_1'}, + 0, + 2, + ). + '-01'; + }else{ + '2037-12-01'; + } + }, + 'payname' => sub { my $hash = shift; + my $payby = payby($hash); + if ($payby eq 'CARD') { + $hash->{'credit_card_name'}; + }elsif ($payby eq 'CHEK') { + $hash->{'bank_name'}; + }else{ + ''; + } + }, + 'paytype' => sub { my $hash = shift; + my $payby = payby($hash); + if ($payby eq 'CHEK') { + $hash->{'bank_account_to_debit'} + ? 'Personal '. + $hash->{bank_account_to_debit} + : ''; + }else{ + ''; + } + }, + 'tax' => sub { shift->{taxable} eq '' ? 'Y' : '' }, + 'refnum' => sub { $referrals{shift->{'referred_from'}} + || 1 + }, + }, + 'fixup' => sub { my $cust_main = shift; + my $row = shift; + return 1 if $row->{master_account}; + $cust_main->ship_country('US') + if $cust_main->has_ship_address; + ut_name_fixup($cust_main, 'first'); + ut_name_fixup($cust_main, 'company'); + ut_name_fixup($cust_main, 'last'); + $cust_main->payby('BILL') + if ($cust_main->payby eq 'CHEK' && + $cust_main->payinfo !~ /^\d+\@\d{9}$/); + $cust_main->payby('BILL') + if ($cust_main->payby eq 'CARD' && + $cust_main->payinfo =~ /^\s*$/); + $cust_main->paydate('2037-12-01') + if ($cust_main->payby eq 'BILL'); + ut_text_fixup($cust_main, 'address1'); + ut_state_fixup($cust_main, 'state'); + ut_zip_fixup($cust_main, 'zip'); + ''; + }, + 'skey' => sub { my $object = shift; + my $href = shift; + my $balance = sprintf("%.2f", + $href->{balance_due}); + if ($balance < 0) { + my $cust_credit = new FS::cust_credit({ + 'custnum' => $object->custnum, + 'amount' => sprintf("%.2f", -$balance), + 'reasonnum' => $previous_credit_reasonnum, + }); + my $error = $cust_credit->insert; + warn "Error inserting credit for ", + $href->{'login'}, " : $error\n" + if $error; + + }elsif($balance > 0) { + my $error = $object->charge( + $balance, "Prior balance", + ); + warn "Error inserting balance charge for ", + $href->{'login'}, " : $error\n" + if $error; + + } + $href->{'login'}; + }, + }, +#cust_main => { 'stable' => 'cust', +# 'mapping' => +# { 'referred_by' => sub { my $href = shift; +# my $u = shift->{'login'}; +# my $cn = $href->{'customer_number'}; +# +# my $c = qsearch( 'cust_main', +# { 'custnum' => $cn } +# ) or die "can't fine customer $cn"; +# +# my $s = qsearch( 'svc_acct', +# { 'username' => $u } +# ) or return ''; +# +# my $n = $s->cust_svc +# ->cust_pkg +# ->cust_main +# ->custnum; +# +# $c->referral_custnum($n); +# my $error = $c->replace; +# die "error setting referral: $error" +# if $error; +# ''; +# }, +# }; +# 'fixup' => sub { 1 }, +# }, +cust_pkg => { 'stable' => 'billcycle', + 'mapping' => + { 'custnum' => sub { my $l = shift->{cbilling_cycle_login}; + $object_map{'cust_main'}{$l}; + }, + 'pkgpart' => sub { my $p = shift->{product_id}; + $package_cache{$p} + ? $package_cache{$p}->pkgpart + : ''; + }, + 'setup' => sub { str2time(shift->{creation_date}) }, + 'bill' => sub { my $href = shift; + my $id = $href->{'slave_account_id'} + ? 'slave:'. $href->{'slave_account_id'} + : $href->{'cbilling_cycle_login'}; + $bill{$id}; + }, + 'susp' => sub { my $href = shift; + my $id = $href->{'slave_account_id'} + ? 'slave:'. $href->{'slave_account_id'} + : $href->{'cbilling_cycle_login'}; + $susp{$id}; + }, + 'adjo' => sub { my $href = shift; + my $id = $href->{'slave_account_id'} + ? 'slave:'. $href->{'slave_account_id'} + : $href->{'cbilling_cycle_login'}; + $adjo{$id}; + }, + 'cancel' => sub { my $href = shift; + my $id = $href->{'slave_account_id'} + ? 'slave:'. $href->{'slave_account_id'} + : $href->{'cbilling_cycle_login'}; + $cancel{$id}; + }, + }, + 'skey' => sub { my $object = shift; + my $href = shift; + if ($href->{'slave_account_id'}) { + 'slave:'. $href->{'slave_account_id'}; + }else{ + my $id = $href->{'billing_cycle_item_id'}; + $cust_pkg_map{$id} = $object->pkgnum; + $href->{'cbilling_cycle_login'}; + } + }, + 'wrapup' => sub { for my $id (keys %{$object_map{'cust_pkg'}}){ + my $cust_svc = + qsearchs( 'cust_svc', { 'svcnum' => + $object_map{'svc_acct'}{$id} } + ); + unless ($cust_svc) { + warn "can't find legacy ppp $id\n"; + next; + } + $cust_svc-> + pkgnum($object_map{'cust_pkg'}{$id}); + my $error = $cust_svc->replace; + warn "error linking legacy ppp $id: $error\n"; + } + }, + }, +svc_acct => { 'stable' => 'email', + 'mapping' => + { 'username' => 'email_name', + '_password' => 'password', + 'svcpart' => sub{ $legacy_email_svcnum }, + 'domsvc' => sub{ $legacy_domain_svcnum }, + }, + 'fixup' => sub { my $object = shift; + my ($sd,$sm,$sy) = split '/', + $object->{shut_off_date} + if $object->{shut_off_date}; + if ($sd && $sm && $sy) { + my ($cd, $cm, $cy) = (localtime)[3,4,5]; + $cy += 1900; $cm++; + return 1 if $sy < $cy; + return 1 if ($sy == $cy && $sm < $cm); + return 1 if ($sy == $cy && $sm == $cm && $sd <= $cd); + } + return 1 if $object_map{'cust_main'}{$object->username}; + ''; + }, + 'skey' => sub { my $object = shift; + my $href = shift; + 'email:'. $href->{'email_customer_id'}; + }, + 'wrapup' => sub { for my $id (keys %{$object_map{'cust_pkg'}}){ + next unless $id =~ /^email:(\d+)/; + my $custid = $1; + my $cust_svc = + qsearchs( 'cust_svc', { 'svcnum' => + $object_map{'svc_acct'}{$id} } + ); + unless ($cust_svc) { + warn "can't find legacy ppp $id\n"; + next; + } + + $cust_svc-> + pkgnum($cust_pkg_map{$custid}); + my $error = $cust_svc->replace; + warn "error linking legacy ppp $id: $error\n"; + } + }, + }, +); + +#my $s_dbh = DBI->connect($s_datasrc, $s_dbuser, $s_dbpass) or die $DBI::errstr; +my $d_dbh = adminsuidsetup $d_dbuser; + +local $FS::UID::AutoCommit = 0; + +while ( @tables ) { + my ($table, $href) = (shift @tables, shift @tables); + my $stable = $href->{'stable'} or die "No source table"; # good enough for now + my (%mapping) = %{$href->{'mapping'}}; + my ($fixup) = $href->{'fixup'}; + my ($wrapup) = $href->{'wrapup'}; + my ($id) = $href->{'id'}; + my ($skey) = $href->{'skey'}; + + $d_dbh->do("delete from $table"); #XXX FIXME! + + my $s_sth = $s_dbh->prepare("select count(*) from $stable"); + $s_sth->execute or die $s_sth->errstr; + my $rowcount = $s_sth->fetchrow_arrayref->[0]; + + $s_sth = $s_dbh->prepare("select * from $stable"); + $s_sth->execute or die $s_sth->errstr; + + my $row; + $count = 0; + while ( $row = $s_sth->fetchrow_hashref ) { + my $class = "FS::$table"; + + warn sprintf("%.2f", 100*$count/$rowcount). "% of $table processed\n" + unless( !$count || $count % 100 ); + + my $object = new $class ( { + map { $_ => ( ref($mapping{$_}) eq 'CODE' + ? &{$mapping{$_}}($row) + : $row->{$mapping{$_}} + ) + } + keys(%mapping) + } ); + my $skip = &{$fixup}($object, $row) + if $fixup; + + unless ($skip) { + my $error = $object->insert; + if ($error) { + warn "Error inserting $table ". + join(", ", map{"$_ => ". $object->get($_)} fields $object). + ": $error\n"; + next; + } + if ($skey) { + my $key = (ref($skey) eq 'CODE') ? &{$skey}($object, $row) + : $row->{$skey}; + $object_map{$table}{$key} = $object->get($object->primary_key) + } + $count++; + } + } + + &{$wrapup}() + if $wrapup; + + print "$count/$rowcount of $table SUCCESSFULLY processed\n"; + +} + +if ($dry_run) { + $d_dbh->rollback; +}else{ + $d_dbh->commit or die $d_dbh->errstr; +} + -- 2.11.0