X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=blobdiff_plain;f=bin%2Fimport-optigold.pl;h=d32a2a129b12d7164b53c08a4dcb32a1fc68ba54;hp=03efe519243325faca939103455c68af1f2cfd90;hb=949a80c148a8bbeeeec54c5a0be5d73b292423a5;hpb=5b6136aa27cdab3d87544df665a9db08db04498f diff --git a/bin/import-optigold.pl b/bin/import-optigold.pl index 03efe5192..d32a2a129 100755 --- a/bin/import-optigold.pl +++ b/bin/import-optigold.pl @@ -4,8 +4,8 @@ use strict; use DBI; use HTML::TableParser; use Date::Parse; -use Data::Dumper; -use FS::UID qw(adminsuidsetup); +use Text::CSV_XS; +use FS::Record qw(qsearch qsearchs); use FS::cust_credit; use FS::cust_main; use FS::cust_pkg; @@ -13,7 +13,7 @@ use FS::cust_svc; use FS::svc_acct; use FS::part_referral; use FS::part_pkg; -use FS::Record qw(qsearch qsearchs); +use FS::UID qw(adminsuidsetup); my $DEBUG = 0; @@ -27,19 +27,22 @@ my $extension = '.htm'; #my $d_dbuser = 'freeside'; my $d_dbuser = 'enet'; #my $d_dbuser = 'ivan'; +#my $d_dbuser = 'freesideimport'; + +my $radius_file = 'radius.csv'; +my $email_file = 'email.csv'; #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 $legacy_ppp_svcpart = 2; +my $legacy_email_svcpart = 3; +#my $legacy_broadband_svcpart = 4; +#my $legacy_broadband_svcpart = 14; #my $previous_credit_reasonnum = 1; my $previous_credit_reasonnum = 1220; - my $state = ''; #statemachine-ish my $sourcefile; my $s_dbh; @@ -133,18 +136,19 @@ sub row { unless (scalar(@$cols) == $columncount); my $sql = "INSERT INTO $sourcefile VALUES(". - join(', ', map { $s_dbh->quote($_) } @$cols). ")"; + join(', ', map { s/\s*(\S[\S ]*?)\s*$/$1/; $s_dbh->quote($_) } @$cols). ")"; $s_dbh->do($sql) or die "insert failed: ". $s_dbh->errstr; $rowcount++; warn "row $rowcount\n" unless ($rowcount % 1000); } - +## now svc_acct from CSV files $FS::cust_main::import=1; $FS::cust_pkg::disable_agentcheck = 1; +$FS::cust_svc::ignore_quantity = 1; -my (%part_pkg_map) = (); +my (%master_map) = (); my (%referrals) = (); my (%custid) = (); my (%cancel) = (); @@ -154,25 +158,131 @@ my (%bill) = (); my (%cust_pkg_map) = (); my (%object_map) = (); my (%package_cache) = (); -my $count; +my $count = 0; + +my $d_dbh = adminsuidsetup $d_dbuser; +local $FS::UID::AutoCommit = 0; + +my @import = ( { 'file' => $radius_file, + 'sep_char' => ';', + 'fields' => [ qw( garbage1 username garbage2 garbage3 _password ) ], + 'fixup' => sub { + my $hash = shift; + delete $hash->{$_} + foreach qw (garbage1 garbage2 garbage3); + $hash->{'svcpart'} = $legacy_ppp_svcpart; + $hash->{'domsvc'} = $legacy_domain_svcnum; + ''; + }, + 'mapkey' => 'legacy_ppp', + 'skey' => 'username', + }, + { 'file' => $email_file, + 'sep_char' => ';', + 'fields' => [ qw( username null finger _password status garbage ) ], + 'fixup' => sub { + my $hash = shift; + #return 1 + # if $object_map{'legacy_ppp'}{$hash->{'username'}}; + delete $hash->{$_} + foreach qw (null status garbage); + $hash->{'svcpart'} = $legacy_email_svcpart; + $hash->{'domsvc'} = $legacy_domain_svcnum; + ''; + }, + 'mapkey' => 'legacy_email', + 'skey' => 'username', + }, +); + +while ( @import ) { + my $href = shift @import; + my $file = $href->{'file'} or die "No file specified"; + my (@fields) = @{$href->{'fields'}}; + my ($sep_char) = $href->{'sep_char'} || ';'; + my ($fixup) = $href->{'fixup'}; + my ($mapkey) = $href->{'mapkey'}; + my ($skey) = $href->{'skey'}; + my $line; + + my $csv = new Text::CSV_XS({'sep_char' => $sep_char}); + open(FH, $file) or die "cannot open $file: $!"; + $count = 0; + + while ( defined($line=) ) { + chomp $line; + + $line &= "\177" x length($line); # i hope this isn't really necessary + $csv->parse($line) + or die "cannot parse: " . $csv->error_input(); + + my @values = $csv->fields(); + my %hash; + foreach my $field (@fields) { + $hash{$field} = shift @values; + } + + if (@values) { + warn "skipping malformed line: $line\n"; + next; + } + + my $skip = &{$fixup}(\%hash) + if $fixup; + + unless ($skip) { + my $svc_acct = new FS::svc_acct { %hash }; + my $error = $svc_acct->insert; + if ($error) { + warn $error; + next; + } + + if ($skey && $mapkey) { + my $key = (ref($skey) eq 'CODE') ? &{$skey}($svc_acct) : $hash{$skey}; + $object_map{$mapkey}{$key} = $svc_acct->svcnum; + } + + $count++ + } + } + print "Imported $count service records\n"; + +} + + sub pkg_freq { my ( $href ) = ( shift ); - $href->{'one_type_item'} + my $once; + $href->{'one_time_list'} =~ /^\s*(\S[\S ]*?)\s*$/ && ($once = $1); + $once ? 0 : int(eval "$href->{'months_credit'} + 0"); +# int(eval "$href->{'month_credit'} + 0"); +} + +sub account_id { + my $href = shift; + if ($href->{'slave_account_id'} =~ /^\s*(\S[\S ]*?)\s*$/) { + "slave:$1"; + }else{ + my $l = $href->{cbilling_cycle_login}; + $l =~ /^\s*(\S[\S ]*?)\s*$/ && ($l = $1); + $l; + } } sub b_or { my ( $field, $hash ) = ( shift, shift ); - $field = 'bill_'. $field + $field = 'billing_'. $field if $hash->{'billing_use'} eq 'Billing Address'; $hash->{$field}; } sub p_or { my ( $field, $hash ) = ( shift, shift ); - $field = 'bill_'. $field + $field = 'billing_'. $field if $hash->{'billing_use'} eq 'Billing Address'; my $ac = ( $hash->{$field. '_area_code'} && $hash->{$field. '_area_code'} =~ /^\d{3}$/ ) @@ -209,6 +319,41 @@ sub payby { $payby_map{ shift->{billing_type} }; } +sub payinfo { + my $hash = shift; + my $payby = payby($hash); + my $info; + my $cc = + $hash->{'credit_card_number_1'}. + $hash->{'credit_card_number_2'}. + $hash->{'credit_card_number_3'}. + $hash->{'credit_card_number_4'}; + my $bank = + $hash->{'bank_account_number'}. + '@'. + $hash->{'bank_transit_number'}; + if ($payby eq 'CARD') { + $info = $cc; + }elsif ($payby eq 'CHEK') { + $info = $bank; + }elsif ($payby eq 'BILL') { + $info = $hash->{'blanket_purchase_order_number'}; + $bank =~ s/[^\d\@]//g; + $cc =~ s/\D//g; + if ( $bank =~ /^\d+\@\d{9}/) { + $info = $bank; + $payby = 'DCHK'; + } + if ( $cc =~ /^\d{13,16}/ ) { + $info = $cc; + $payby = 'DCRD'; + } + }else{ + die "unexpected payby"; + } + ($info, $payby); +} + sub ut_name_fixup { my ($object, $field) = (shift, shift); my $value = $object->getfield($field); @@ -239,6 +384,7 @@ sub ut_zip_fixup { my @tables = ( part_pkg => { 'stable' => 'product', +#part_pkg => { 'stable' => 'billcycle', 'mapping' => { 'pkg' => sub { my $href = shift; $href->{'description'} @@ -247,7 +393,7 @@ part_pkg => { 'stable' => 'product', }, 'comment' => 'product_id', 'freq' => sub { pkg_freq(shift) }, - 'recur' => sub { my $href = shift; + 'recur_fee'=> sub { my $href = shift; my $price = ( pkg_freq($href) ? $href->{'unit_price'} : 0 @@ -267,22 +413,32 @@ part_pkg => { 'stable' => 'product', 'pkg_svc' => sub { my $href = shift; my $result = {}; if (pkg_freq($href)){ - $result->{$legacy_ppp_svcnum} = 1; - $result->{$legacy_email_svcnum} = + $result->{$legacy_ppp_svcpart} = 1; + $result->{$legacy_email_svcpart} = $href->{emails_allowed} if $href->{emails_allowed}; } }, 'primary_svc'=> sub { pkg_freq(shift) - ? $legacy_ppp_svcnum + ? $legacy_ppp_svcpart : '' ; }, }, 'fixup' => sub { my $part_pkg = shift; my $row = shift; - return 1 unless $part_pkg->comment; - $package_cache{$part_pkg->comment} = $part_pkg; + unless ($part_pkg->pkg =~ /^\s*(\S[\S ]*?)\s*$/) { + warn "no pkg: ". $part_pkg->pkg. " for ". $row->{product_id}; + return 1; + } + + unless ($part_pkg->comment =~ /^\s*(\S[\S ]*?)\s*$/) { + warn "no comment: ". $part_pkg->comment. " for ". $row->{product_id}; + return 1; + } + + return 1 if exists($package_cache{$1}); + $package_cache{$1} = $part_pkg; 1; }, 'wrapup' => sub { foreach (keys %package_cache) { @@ -292,11 +448,11 @@ part_pkg => { 'stable' => 'product', $part_pkg->$_(''); ($_ => $v); } - qw (setup recur) + qw (setup_fee recur_fee) }; my $error = $part_pkg->insert(options=>$options); - die "Error inserting referral: $error" + die "Error inserting package: $error" if $error; $count++ unless $error; } @@ -324,45 +480,45 @@ part_referral => { 'stable' => 'cust', } }, }, -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'}; - }, - }, +#svc_acct => { 'stable' => 'cust', +# 'mapping' => +# { 'username' => 'login', +# '_password' => 'password', +# 'svcpart' => sub{ $legacy_ppp_svcpart }, +# '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 }, @@ -397,69 +553,6 @@ cust_main => { 'stable' => 'cust', '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 @@ -467,12 +560,108 @@ cust_main => { 'stable' => 'cust', }, 'fixup' => sub { my $cust_main = shift; my $row = shift; - return 1 if $row->{master_account}; + + my ($master_account, $customer_id, $login) = + ('', '', ''); + $row->{'master_account'} =~ /^\s*(\S[\S ]*?)\s*$/ + && ($master_account = $1); + $row->{'customer_id'} =~ /^\s*(\S[\S ]*?)\s*$/ + && ($customer_id = $1); + $row->{'login'} =~ /^\s*(\S[\S ]*?)\s*$/ + && ($login = $1); + + my ($first, $last, $company) = + ('', '', ''); + $cust_main->first =~ /^\s*(\S[\S ]*?)\s*$/ + && ($first = $1); + $cust_main->last =~ /^\s*(\S[\S ]*?)\s*$/ + && ($last = $1); + $cust_main->company =~ /^\s*(\S[\S ]*?)\s*$/ + && ($company = $1); + + unless ($first || $last || $company) { + warn "bogus entry: ". $row->{'login'}; + return 1; + } + + my $id = $master_account + ? 'slave:'. $customer_id + : $login; + #my $id = $login; + my $status = $row->{status}; + + my $cancelled = 0; + if ( $status ne 'Current' + && $status ne 'current' + && $status ne 'On Hold' ) + { + $cancelled = 1; + $cancel{$login} = + str2time($row->{termination_date}); + } + $susp{$id} = str2time($row->{hold_date}) + if ($status eq 'On Hold' && !$cancelled); + $adjo{$id} = str2time($row->{hold_date}) + if ( $status eq 'Current' && !$cancelled && + $row->{hold_date} ); + $bill{$id} = + str2time($row->{expiration_date}) + if (!$cancelled); + + my $svcnum = + $object_map{legacy_ppp}{$row->{'login'} }; + unless( $cancelled || $svcnum || $status eq 'Pn Hold' ) { + warn "can't find svc_acct for legacy ppp ". + $row->{'login'}, "\n"; + } + + $object_map{svc_acct}{$id} = $svcnum + unless $cancelled; + + $master_map{$login} = $master_account + if $master_account; + return 1 if $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'); + + my ($info, $payby) = payinfo($row); + $cust_main->payby($payby); + $cust_main->payinfo($info); + + $cust_main->paycvv( + $row->{'credit_card_cvv_number'} + ) + if ($payby eq 'CARD' or $payby eq 'DCRD'); + + $cust_main->paydate('20'. + $row->{'credit_card_exp_date_2'}. '-'. + substr( + $row->{'credit_card_exp_date_1'}, + 0, + 2, + ). + '-01' + ) + if ($payby eq 'CARD' or $payby eq 'DCRD'); + + my $payname = ''; + $payname = $row->{'credit_card_name'} + if ($payby eq 'CARD' or $payby eq 'DCRD'); + $payname = $row->{'bank_name'} + if ($payby eq 'CHEK' or $payby eq 'DCHK'); + $cust_main->payname($payname); + + $cust_main->paytype( + $row->{'bank_account_to_debit'} + ? 'Personal '. + $row->{bank_account_to_debit} + : '' + ) + if ($payby eq 'CHEK' or $payby eq 'DCHK'); + $cust_main->payby('BILL') if ($cust_main->payby eq 'CHEK' && $cust_main->payinfo !~ /^\d+\@\d{9}$/); @@ -484,6 +673,8 @@ cust_main => { 'stable' => 'cust', ut_text_fixup($cust_main, 'address1'); ut_state_fixup($cust_main, 'state'); ut_zip_fixup($cust_main, 'zip'); + + ''; }, 'skey' => sub { my $object = shift; @@ -544,48 +735,139 @@ cust_main => { 'stable' => 'cust', cust_pkg => { 'stable' => 'billcycle', 'mapping' => { 'custnum' => sub { my $l = shift->{cbilling_cycle_login}; - $object_map{'cust_main'}{$l}; + $l =~ /^\s*(\S[\S ]*?)\s*$/ && ($l = $1); + my $r = $object_map{'cust_main'}{$l}; + unless ($r) { + my $m = $master_map{$l}; + $r = $object_map{'cust_main'}{$m} + if $m; + } + $r; }, - 'pkgpart' => sub { my $p = shift->{product_id}; - $package_cache{$p} - ? $package_cache{$p}->pkgpart - : ''; + 'pkgpart' => sub { my $href = shift; + my $p = $href->{product_id}; + $p =~ /^\s*(\S[\S ]*?)\s*$/ && ($p = $1); + my $pkg = $package_cache{$p} + if $package_cache{$p}; + + my $month = ''; + $href->{month_credit} =~ /\s*(\S[\S ]*?)\s*$/ && ($month = $1); + $month = int(eval "$month + 0"); + + my $price = 0; + $href->{unit_price} =~ /\s*(\S[\S ]*?)\s*$/ && ($price = $1); + $price = eval "$price + 0"; + + if ($pkg) { + $pkg = '' + unless $pkg->freq + 0 == $month; + + if ($pkg && ($pkg->freq + 0)) { + my $recur = 0; + $pkg->recur_fee =~ /\s*(\S[\S ]*?)\s*$/ && ($recur = $1); + $recur = eval "$recur + 0"; + $pkg = '' + unless $recur == $price; + } + + if ($pkg) { + $pkg = '' + unless $pkg->setuptax + eq ($href->{taxable} ? '' : 'Y'); + } + + } + + unless ($pkg) { + my $pkghref = { 'pkg' => ($href->{description} ? $href->{description} : $href->{product_id} ), + 'comment' => $href->{product_id}, + 'freq' => $month, + 'setuptax' => ($href->{'taxable'} ? '' : 'Y'), + 'recurtax' => ($href->{'taxable'} ? '' : 'Y'), + 'plan' => 'flat', + 'disabled' => 'Y', + }; + + my @pkgs = qsearch('part_pkg', $pkghref); + my $recur = sprintf("%.2f", ($month ? $price : 0)); + for (@pkgs) { + my %options = $_->options; + if ($options{recur_fee} eq $recur) { + $pkg = $_; + last; + } + } + + $pkghref->{recur_fee} = $recur + unless $pkg; + + my $pkg_svc = {}; + + if ($month){ + $pkg_svc->{$legacy_ppp_svcpart} = 1; + $pkg_svc->{$legacy_email_svcpart} = + $href->{emails_allowed} + if $href->{emails_allowed}; + } + $pkghref->{pkg_svc} = $pkg_svc; + $pkghref->{primary_svc} + = ( $month + ? $legacy_ppp_svcpart + : ''); + unless ($pkg) { + $pkg = new FS::part_pkg $pkghref; + my $options = + { map { my $v = $pkg->$_; + $pkg->$_(''); + ($_ => $v); + } + qw (setup_fee recur_fee) + }; + my $error = + $pkg->insert(options=>$options); + if ($error) { + warn "Error inserting pkg ". + join(", ", map{"$_ => ". $pkg->get($_)} fields $pkg). + ": $error\n"; + $pkg = ''; + } + } + } + $pkg ? $pkg->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}; + 'bill' => sub { $bill{account_id(shift)} + #$bill{$href->{cbilling_cycle_login}}; }, - 'susp' => sub { my $href = shift; - my $id = $href->{'slave_account_id'} - ? 'slave:'. $href->{'slave_account_id'} - : $href->{'cbilling_cycle_login'}; - $susp{$id}; + 'susp' => sub { $susp{account_id(shift)} + #$susp{$href->{cbilling_cycle_login}}; }, - 'adjo' => sub { my $href = shift; - my $id = $href->{'slave_account_id'} - ? 'slave:'. $href->{'slave_account_id'} - : $href->{'cbilling_cycle_login'}; - $adjo{$id}; + 'adjo' => sub { $adjo{account_id(shift)} + #$adjo{$href->{cbilling_cycle_login}}; }, - 'cancel' => sub { my $href = shift; - my $id = $href->{'slave_account_id'} - ? 'slave:'. $href->{'slave_account_id'} - : $href->{'cbilling_cycle_login'}; - $cancel{$id}; + 'cancel' => sub { $cancel{account_id(shift)} + #$cancel{$href->{cbilling_cycle_login}}; }, }, + 'fixup' => sub { my ($object, $row) = (shift,shift); + unless ($object->custnum) { + warn "can't find customer for ". + $row->{cbilling_cycle_login}. "\n"; + return 1; + } + unless ($object->pkgpart) { + warn "can't find package for ". + $row->{product_id}. "\n"; + return 1; + } + ''; + }, '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'}; - } + my $id = $href->{'billing_cycle_item_id'}; + $id =~ /^\s*(\S[\S ]*?)\s*$/ && ($id = $1); + $cust_pkg_map{$id} = $object->pkgnum; + account_id($href); }, 'wrapup' => sub { for my $id (keys %{$object_map{'cust_pkg'}}){ my $cust_svc = @@ -599,7 +881,8 @@ cust_pkg => { 'stable' => 'billcycle', $cust_svc-> pkgnum($object_map{'cust_pkg'}{$id}); my $error = $cust_svc->replace; - warn "error linking legacy ppp $id: $error\n"; + warn "error linking legacy ppp $id: $error\n" + if $error; } }, }, @@ -607,13 +890,27 @@ svc_acct => { 'stable' => 'email', 'mapping' => { 'username' => 'email_name', '_password' => 'password', - 'svcpart' => sub{ $legacy_email_svcnum }, + 'svcpart' => sub{ $legacy_email_svcpart }, 'domsvc' => sub{ $legacy_domain_svcnum }, }, - 'fixup' => sub { my $object = shift; +# 'fixup' => sub { my ($object, $row) = (shift,shift); +# my ($sd,$sm,$sy) = split '/', +# $row->{shut_off_date} +# if $row->{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}; +# ''; +# }, + 'fixup' => sub { my ($object, $row) = (shift,shift); my ($sd,$sm,$sy) = split '/', - $object->{shut_off_date} - if $object->{shut_off_date}; + $row->{shut_off_date} + if $row->{shut_off_date}; if ($sd && $sm && $sy) { my ($cd, $cm, $cy) = (localtime)[3,4,5]; $cy += 1900; $cm++; @@ -621,14 +918,29 @@ svc_acct => { 'stable' => 'email', 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'}; + #return 1 if $object_map{'cust_main'}{$object->username}; + + my $email_name; + $row->{email_name} =~ /^\s*(\S[\S ]*?)\s*$/ + && ($email_name = $1); + + my $svcnum = + $object_map{legacy_email}{$email_name} + if $email_name; + unless( $svcnum ) { + warn "can't find svc_acct for legacy email ". + $row->{'email_name'}, "\n"; + return 1; + } + + $object_map{svc_acct}{'email:'.$row->{'email_customer_id'}} = $svcnum; + return 1; }, - 'wrapup' => sub { for my $id (keys %{$object_map{'cust_pkg'}}){ +# 'skey' => sub { my $object = shift; +# my $href = shift; +# 'email:'. $href->{'email_customer_id'}; +# }, + 'wrapup' => sub { for my $id (keys %{$object_map{'svc_acct'}}){ next unless $id =~ /^email:(\d+)/; my $custid = $1; my $cust_svc = @@ -636,23 +948,30 @@ svc_acct => { 'stable' => 'email', $object_map{'svc_acct'}{$id} } ); unless ($cust_svc) { - warn "can't find legacy ppp $id\n"; + warn "can't find legacy email $id\n"; + next; + } + + if ($cust_svc->pkgnum) { + warn "service already linked for $id\n"; next; } $cust_svc-> pkgnum($cust_pkg_map{$custid}); - my $error = $cust_svc->replace; - warn "error linking legacy ppp $id: $error\n"; + if ($cust_svc->pkgnum){ + my $error = $cust_svc->replace; + warn "error linking legacy email $id: $error\n" + if $error; + }else{ + warn "can't find package for $id\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); @@ -715,6 +1034,41 @@ while ( @tables ) { } +# link to any uncancelled package on customer +foreach my $username ( keys %{$object_map{'legacy_email'}} ) { + my $cust_svc = qsearchs( 'cust_svc', + { 'svcnum' => $object_map{legacy_email}{$username} } + ); + next unless $cust_svc; + next if $cust_svc->pkgnum; + + my $custnum = $object_map{cust_main}{$username}; + unless ($custnum) { + my $master = $master_map{$username}; + $custnum = $object_map{'cust_main'}{$master} + if $master; + next unless $custnum; + } + + #my $extra_sql = ' AND 0 != (select freq from part_pkg where '. + # 'cust_pkg.pkgpart = part_pkg.pkgpart )'; + my $extra_sql = " AND 'Prior balance' != (select pkg from part_pkg where ". + "cust_pkg.pkgpart = part_pkg.pkgpart )"; + + my @cust_pkg = qsearch( { + 'table' => 'cust_pkg', + 'hashref' => { 'custnum' => $custnum, + 'cancel' => '', + }, + 'extra_sql' => $extra_sql, + } ); + next unless scalar(@cust_pkg); + + $cust_svc->pkgnum($cust_pkg[0]->pkgnum); + $cust_svc->replace; +} + + if ($dry_run) { $d_dbh->rollback; }else{