diff options
Diffstat (limited to 'bin/fs-migrate-cust_tax_exempt')
| -rwxr-xr-x | bin/fs-migrate-cust_tax_exempt | 323 |
1 files changed, 0 insertions, 323 deletions
diff --git a/bin/fs-migrate-cust_tax_exempt b/bin/fs-migrate-cust_tax_exempt deleted file mode 100755 index 35c74ff..0000000 --- a/bin/fs-migrate-cust_tax_exempt +++ /dev/null @@ -1,323 +0,0 @@ -#!/usr/bin/perl -w - -use strict; -use Time::Local; -use Date::Format; -use Time::Duration; -use FS::UID qw(adminsuidsetup); -use FS::Record qw( qsearch dbh ); -use FS::cust_tax_exempt; -#use FS::cust_bill; -use FS::h_cust_bill; -use FS::h_cust_tax_exempt; -use FS::cust_bill_pkg; -use FS::cust_tax_exempt_pkg; -#use Data::Dumper; - -my $start = time; - -adminsuidsetup shift; - -my $fuz = 7; #seconds - - #site-specific rewrites -my %rewrite = ( - #cust_tax_exempt.exemptnum => { 'field' => 'newvalue', ... }, -# '23' => { month=>10, year=>2005, invnum=>1640 }, - - #etc. -); - -my @cust_tax_exempt = qsearch('cust_tax_exempt', {} ); -my $num_cust_tax_exempt = scalar(@cust_tax_exempt); -my $num_cust_tax_exempt_migrated = 0; -my $total_cust_tax_exempt_migrated = 0; -my $num_cust_tax_exempt_pkg_migrated = 0; -my $total_cust_tax_exempt_pkg_migrated = 0; - -$FS::UID::AutoCommit = 0; - -foreach my $cust_tax_exempt ( @cust_tax_exempt ) { - - if ( exists $rewrite{ $cust_tax_exempt->exemptnum } ) { - my $hashref = $rewrite{ $cust_tax_exempt->exemptnum }; - $cust_tax_exempt->setfield($_, $hashref->{$_}) - foreach keys %$hashref; - } - - if ( $cust_tax_exempt->year < 1990 ) { - warn "exemption year is ". $cust_tax_exempt->year. - "; not migrating exemption ". $cust_tax_exempt->exemptnum. - ' for custnum '. $cust_tax_exempt->custnum. "\n\n"; - next; - } - - # also make sure cust_bill_pkg record dates contain the month/year -# my $mon = $cust_tax_exempt->month; -# my $year = $cust_tax_exempt->year; -# $mon--; -# my $edate_after = timelocal(0,0,0,1,$mon,$year); -# $mon++; -# if ( $mon >= 12 ) { $mon-=12; $year++ }; -# my $sdate_before = timelocal(0,0,0,1,$mon,$year); - - my $mon = $cust_tax_exempt->month; - my $year = $cust_tax_exempt->year; - if ( $mon >= 12 ) { $mon-=12; $year++ }; - my $sdate_before = timelocal(0,0,0,1,$mon,$year); - #$mon++; - #if ( $mon >= 12 ) { $mon-=12; $year++ }; - my $edate_after = timelocal(0,0,0,1,$mon,$year); - - # !! start a transaction? (yes, its started) - - my @h_cust_tax_exempt = qsearch({ - 'table' => 'h_cust_tax_exempt', - 'hashref' => { 'exemptnum' => $cust_tax_exempt->exemptnum }, - 'extra_sql' => " AND ( history_action = 'insert' - OR history_action = 'replace_new' ) - ORDER BY history_date ASC - ", - }); - - my $amount_so_far = 0; - my $num_cust_tax_exempt_pkg = 0; - my $total_cust_tax_exempt_pkg = 0; - H_CUST_TAX_EXEMPT: foreach my $h_cust_tax_exempt ( @h_cust_tax_exempt ) { - - my $amount = sprintf('%.2f', $h_cust_tax_exempt->amount - $amount_so_far ); - $amount_so_far += $amount; - -# print Dumper($h_cust_tax_exempt), "\n"; - - #find a matching cust_bill record - # (print time differences and choose a meaningful threshold, should work) - - my @h_cust_bill = (); - if ( $cust_tax_exempt->invnum ) { - #warn "following invnum ". $cust_tax_exempt->invnum. - # " kludge for cust_tax_exempt ". $cust_tax_exempt->exemptnum. "\n"; - - @h_cust_bill = qsearch({ - #'table' => 'cust_bill', - 'table' => 'h_cust_bill', - 'hashref' => { 'custnum' => $h_cust_tax_exempt->custnum, - 'invnum' => $cust_tax_exempt->invnum, - 'history_action' => 'insert', - }, - #'extra_sql' => - # ' AND history_date <= '. ( $h_cust_tax_exempt->history_date + $fuz ). - # ' AND history_date > '. ( $h_cust_tax_exempt->history_date - $fuz ), - }); - - } else { - - @h_cust_bill = qsearch({ - #'table' => 'cust_bill', - 'table' => 'h_cust_bill', - 'hashref' => { 'custnum' => $h_cust_tax_exempt->custnum, - 'history_action' => 'insert', - }, - 'extra_sql' => - ' AND history_date <= '. ( $h_cust_tax_exempt->history_date + $fuz ). - ' AND history_date > '. ( $h_cust_tax_exempt->history_date - $fuz ), - }); - - } - - if ( scalar(@h_cust_bill) != 1 ) { - warn ' '. scalar(@h_cust_bill). ' h_cust_bill records matching '. - 'h_cust_tax_exempt.historynum '. $h_cust_tax_exempt->historynum. - "; not migrating (adjust fuz factor?)\n"; - next; - } - - my $h_cust_bill = $h_cust_bill[0]; - -# print Dumper(@cust_bill), "\n\n"; - - # then find a matching cust_bill_pkg record with part_pkg.taxclass record - # that matches the one pointed to by cust_tax_exempt.taxnum - # (hopefully just one, see how many we can match automatically) - - my $cust_main_county = $cust_tax_exempt->cust_main_county; - my $taxclass = $cust_main_county->taxclass; - - my $hashref = { - 'custnum' => $cust_tax_exempt->custnum, - 'invnum' => $h_cust_bill->invnum, - 'pkgnum' => { op=>'>', value=>0, }, - }; - unless ( $cust_tax_exempt->invnum ) { - # also make sure cust_bill_pkg record dates contain the month/year - - #$hashref->{'sdate'} = { op=>'<', value=>$sdate_before }; - $hashref->{'sdate'} = { op=>'<=', value=>$sdate_before }; - - #$hashref->{'edate'} = { op=>'>', value=>$edate_after }; - $hashref->{'edate'} = { op=>'>=', value=>$edate_after }; - } - - if ( $cust_tax_exempt->billpkgnum ) { - $hashref->{'billpkgnum'} = $cust_tax_exempt->billpkgnum; - } - - my $extra_sql = 'ORDER BY billpkgnum'; - - $extra_sql = "AND taxclass = '$taxclass' $extra_sql" - unless $cust_tax_exempt->ignore_current_taxclass; - - my @cust_bill_pkg = qsearch({ - 'select' => 'cust_bill_pkg.*, part_pkg.freq', - 'table' => 'cust_bill_pkg', - 'addl_from' => 'LEFT JOIN cust_pkg using ( pkgnum ) '. - 'LEFT JOIN part_pkg using ( pkgpart ) ', - 'hashref' => $hashref, - 'extra_sql' => $extra_sql, - }); - - foreach my $cust_bill_pkg ( @cust_bill_pkg ) { - $cust_bill_pkg->exemptable_per_month( - sprintf('%.2f', - ( $cust_bill_pkg->setup + $cust_bill_pkg->recur ) - / - ( $cust_bill_pkg[0]->freq || 1 ) - ) - ); - } - - my(@cust_tax_exempt_pkg) = (); - if ( scalar(@cust_bill_pkg) == 1 - && $cust_bill_pkg[0]->exemptable_per_month >= $amount - ) - { - - my $cust_bill_pkg = $cust_bill_pkg[0]; - - # finally, create an appropriate cust_tax_exempt_pkg record - - push @cust_tax_exempt_pkg, new FS::cust_tax_exempt_pkg { - 'billpkgnum' => $cust_bill_pkg->billpkgnum, - 'taxnum' => $cust_tax_exempt->taxnum, - 'year' => $cust_tax_exempt->year, - 'month' => $cust_tax_exempt->month, - 'amount' => $amount, - }; - - } else { - -# warn ' '. scalar(@cust_bill_pkg). ' cust_bill_pkg records for invoice '. -# $h_cust_bill->invnum. -# "; not migrating h_cust_tax_exempt historynum ". -# $h_cust_tax_exempt->historynum. " for \$$amount\n"; -# warn " *** DIFFERENT DATES ***\n" -# if grep { $_->sdate != $cust_bill_pkg[0]->sdate -# || $_->edate != $cust_bill_pkg[0]->edate -# } @cust_bill_pkg; -# foreach ( @cust_bill_pkg ) { -# warn ' '. $_->billpkgnum. ': '. $_->setup. 's/'. $_->recur.'r'. -# ' '. time2str('%D', $_->sdate). '-'. time2str('%D', $_->edate). -# "\n"; -# } -# -# next; - - my $remaining = $amount; - foreach my $cust_bill_pkg ( @cust_bill_pkg ) { - last unless $remaining; - my $this_amount =sprintf('%.2f', - $remaining <= $cust_bill_pkg->exemptable_per_month - ? $remaining - : $cust_bill_pkg->exemptable_per_month - );; - - push @cust_tax_exempt_pkg, new FS::cust_tax_exempt_pkg { - 'billpkgnum' => $cust_bill_pkg->billpkgnum, - 'taxnum' => $cust_tax_exempt->taxnum, - 'year' => $cust_tax_exempt->year, - 'month' => $cust_tax_exempt->month, - 'amount' => $this_amount, - }; - - $remaining -= $this_amount; - - } - - } - - foreach my $cust_tax_exempt_pkg ( @cust_tax_exempt_pkg ) { - my $error = $cust_tax_exempt_pkg->insert; - #my $error = $cust_tax_exempt_pkg->check; - if ( $error ) { - warn "*** error inserting cust_tax_exempt_pkg record: $error\n"; - next; #not necessary.. H_CUST_TAX_EXEMPT; - - #not necessary, incorrect $total_cust_tax_exempt_pkg will error it out - # roll back at least the entire cust_tax_exempt transaction - # next CUST_TAX_EXEMPT; - } - - $num_cust_tax_exempt_pkg++; - - $total_cust_tax_exempt_pkg += $cust_tax_exempt_pkg->amount; - - } - - } - - $total_cust_tax_exempt_pkg = sprintf('%.2f', $total_cust_tax_exempt_pkg ); - - unless ( $total_cust_tax_exempt_pkg == $cust_tax_exempt->amount ) { - warn "total h_ amount $total_cust_tax_exempt_pkg != cust_tax_exempt.amount ". - $cust_tax_exempt->amount. - ";\n not migrating exemption ". $cust_tax_exempt->exemptnum. " for ". - $cust_tax_exempt->month. '/'. $cust_tax_exempt->year. - ' (custnum '. $cust_tax_exempt->custnum. ") ". - #"\n (sdate < ". time2str('%D', $sdate_before ). - "\n (sdate <= ". time2str('%D', $sdate_before ). " [$sdate_before]". - #' / edate > '. time2str('%D', $edate_after ). ')'. - ' / edate >= '. time2str('%D', $edate_after ). " [$edate_after])". - "\n\n"; - - # roll back at least the entire cust_tax_exempt transaction - dbh->rollback; - - # next CUST_TAX_EXEMPT; - next; - } - - # remove the cust_tax_exempt record - my $error = $cust_tax_exempt->delete; - if ( $error ) { - #roll back at least the entire cust_tax_exempt transaction - dbh->rollback; - - #next CUST_TAX_EXEMPT; - next; - } - - $num_cust_tax_exempt_migrated++; - $total_cust_tax_exempt_migrated += $cust_tax_exempt->amount; - - $num_cust_tax_exempt_pkg_migrated += $num_cust_tax_exempt_pkg; - $total_cust_tax_exempt_pkg_migrated += $total_cust_tax_exempt_pkg; - - # commit the transaction - dbh->commit; - -} - -$total_cust_tax_exempt_migrated = - sprintf('%.2f', $total_cust_tax_exempt_migrated ); -$total_cust_tax_exempt_pkg_migrated = - sprintf('%.2f', $total_cust_tax_exempt_pkg_migrated ); - -warn - "$num_cust_tax_exempt_migrated / $num_cust_tax_exempt (". - sprintf('%.2f', 100 * $num_cust_tax_exempt_migrated / $num_cust_tax_exempt). - '%) cust_tax_exempt records migrated ($'. $total_cust_tax_exempt_migrated. - ")\n to $num_cust_tax_exempt_pkg_migrated cust_tax_exempt_pkg records". - ' ($'. $total_cust_tax_exempt_pkg_migrated. ')'. - "\n in ". duration(time-$start). "\n" -; - |