7 use FS::UID qw(adminsuidsetup);
8 use FS::Record qw( qsearch dbh );
9 use FS::cust_tax_exempt;
12 use FS::h_cust_tax_exempt;
13 use FS::cust_bill_pkg;
14 use FS::cust_tax_exempt_pkg;
23 #site-specific rewrites
25 #cust_tax_exempt.exemptnum => { 'field' => 'newvalue', ... },
26 '23' => { month=>10, year=>2005, invnum=>1640 },
31 my @cust_tax_exempt = qsearch('cust_tax_exempt', {} );
32 my $num_cust_tax_exempt = scalar(@cust_tax_exempt);
33 my $num_cust_tax_exempt_migrated = 0;
34 my $total_cust_tax_exempt_migrated = 0;
35 my $num_cust_tax_exempt_pkg_migrated = 0;
36 my $total_cust_tax_exempt_pkg_migrated = 0;
38 $FS::UID::AutoCommit = 0;
40 foreach my $cust_tax_exempt ( @cust_tax_exempt ) {
42 if ( exists $rewrite{ $cust_tax_exempt->exemptnum } ) {
43 my $hashref = $rewrite{ $cust_tax_exempt->exemptnum };
44 $cust_tax_exempt->setfield($_, $hashref->{$_})
45 foreach keys %$hashref;
48 if ( $cust_tax_exempt->year < 1990 ) {
49 warn "exemption year is ". $cust_tax_exempt->year.
50 "; not migrating exemption ". $cust_tax_exempt->exemptnum.
51 ' for custnum '. $cust_tax_exempt->custnum. "\n\n";
55 # also make sure cust_bill_pkg record dates contain the month/year
56 # my $mon = $cust_tax_exempt->month;
57 # my $year = $cust_tax_exempt->year;
59 # my $edate_after = timelocal(0,0,0,1,$mon,$year);
61 # if ( $mon >= 12 ) { $mon-=12; $year++ };
62 # my $sdate_before = timelocal(0,0,0,1,$mon,$year);
64 my $mon = $cust_tax_exempt->month;
65 my $year = $cust_tax_exempt->year;
66 if ( $mon >= 12 ) { $mon-=12; $year++ };
67 my $sdate_before = timelocal(0,0,0,1,$mon,$year);
69 #if ( $mon >= 12 ) { $mon-=12; $year++ };
70 my $edate_after = timelocal(0,0,0,1,$mon,$year);
72 # !! start a transaction? (yes, its started)
74 my @h_cust_tax_exempt = qsearch({
75 'table' => 'h_cust_tax_exempt',
76 'hashref' => { 'exemptnum' => $cust_tax_exempt->exemptnum },
77 'extra_sql' => " AND ( history_action = 'insert'
78 OR history_action = 'replace_new' )
79 ORDER BY history_date ASC
83 my $amount_so_far = 0;
84 my $num_cust_tax_exempt_pkg = 0;
85 my $total_cust_tax_exempt_pkg = 0;
86 H_CUST_TAX_EXEMPT: foreach my $h_cust_tax_exempt ( @h_cust_tax_exempt ) {
88 my $amount = sprintf('%.2f', $h_cust_tax_exempt->amount - $amount_so_far );
89 $amount_so_far += $amount;
91 # print Dumper($h_cust_tax_exempt), "\n";
93 #find a matching cust_bill record
94 # (print time differences and choose a meaningful threshold, should work)
97 if ( $cust_tax_exempt->invnum ) {
98 #warn "following invnum ". $cust_tax_exempt->invnum.
99 # " kludge for cust_tax_exempt ". $cust_tax_exempt->exemptnum. "\n";
101 @h_cust_bill = qsearch({
102 #'table' => 'cust_bill',
103 'table' => 'h_cust_bill',
104 'hashref' => { 'custnum' => $h_cust_tax_exempt->custnum,
105 'invnum' => $cust_tax_exempt->invnum,
106 'history_action' => 'insert',
109 # ' AND history_date <= '. ( $h_cust_tax_exempt->history_date + $fuz ).
110 # ' AND history_date > '. ( $h_cust_tax_exempt->history_date - $fuz ),
115 @h_cust_bill = qsearch({
116 #'table' => 'cust_bill',
117 'table' => 'h_cust_bill',
118 'hashref' => { 'custnum' => $h_cust_tax_exempt->custnum,
119 'history_action' => 'insert',
122 ' AND history_date <= '. ( $h_cust_tax_exempt->history_date + $fuz ).
123 ' AND history_date > '. ( $h_cust_tax_exempt->history_date - $fuz ),
128 if ( scalar(@h_cust_bill) != 1 ) {
129 warn ' '. scalar(@h_cust_bill). ' h_cust_bill records matching '.
130 'h_cust_tax_exempt.historynum '. $h_cust_tax_exempt->historynum.
131 "; not migrating (adjust fuz factor?)\n";
135 my $h_cust_bill = $h_cust_bill[0];
137 # print Dumper(@cust_bill), "\n\n";
139 # then find a matching cust_bill_pkg record with part_pkg.taxclass record
140 # that matches the one pointed to by cust_tax_exempt.taxnum
141 # (hopefully just one, see how many we can match automatically)
143 my $cust_main_county = $cust_tax_exempt->cust_main_county;
144 my $taxclass = $cust_main_county->taxclass;
147 'custnum' => $cust_tax_exempt->custnum,
148 'invnum' => $h_cust_bill->invnum,
149 'pkgnum' => { op=>'>', value=>0, },
151 unless ( $cust_tax_exempt->invnum ) {
152 # also make sure cust_bill_pkg record dates contain the month/year
154 #$hashref->{'sdate'} = { op=>'<', value=>$sdate_before };
155 $hashref->{'sdate'} = { op=>'<=', value=>$sdate_before };
157 #$hashref->{'edate'} = { op=>'>', value=>$edate_after };
158 $hashref->{'edate'} = { op=>'>=', value=>$edate_after };
161 if ( $cust_tax_exempt->billpkgnum ) {
162 $hashref->{'billpkgnum'} = $cust_tax_exempt->billpkgnum;
165 my $extra_sql = 'ORDER BY billpkgnum';
167 $extra_sql = "AND taxclass = '$taxclass' $extra_sql"
168 unless $cust_tax_exempt->ignore_current_taxclass;
170 my @cust_bill_pkg = qsearch({
171 'select' => 'cust_bill_pkg.*, part_pkg.freq',
172 'table' => 'cust_bill_pkg',
173 'addl_from' => 'LEFT JOIN cust_pkg using ( pkgnum ) '.
174 'LEFT JOIN part_pkg using ( pkgpart ) ',
175 'hashref' => $hashref,
176 'extra_sql' => $extra_sql,
179 foreach my $cust_bill_pkg ( @cust_bill_pkg ) {
180 $cust_bill_pkg->exemptable_per_month(
182 ( $cust_bill_pkg->setup + $cust_bill_pkg->recur )
184 ( $cust_bill_pkg[0]->freq || 1 )
189 my(@cust_tax_exempt_pkg) = ();
190 if ( scalar(@cust_bill_pkg) == 1
191 && $cust_bill_pkg[0]->exemptable_per_month >= $amount
195 my $cust_bill_pkg = $cust_bill_pkg[0];
197 # finally, create an appropriate cust_tax_exempt_pkg record
199 push @cust_tax_exempt_pkg, new FS::cust_tax_exempt_pkg {
200 'billpkgnum' => $cust_bill_pkg->billpkgnum,
201 'taxnum' => $cust_tax_exempt->taxnum,
202 'year' => $cust_tax_exempt->year,
203 'month' => $cust_tax_exempt->month,
209 # warn ' '. scalar(@cust_bill_pkg). ' cust_bill_pkg records for invoice '.
210 # $h_cust_bill->invnum.
211 # "; not migrating h_cust_tax_exempt historynum ".
212 # $h_cust_tax_exempt->historynum. " for \$$amount\n";
213 # warn " *** DIFFERENT DATES ***\n"
214 # if grep { $_->sdate != $cust_bill_pkg[0]->sdate
215 # || $_->edate != $cust_bill_pkg[0]->edate
217 # foreach ( @cust_bill_pkg ) {
218 # warn ' '. $_->billpkgnum. ': '. $_->setup. 's/'. $_->recur.'r'.
219 # ' '. time2str('%D', $_->sdate). '-'. time2str('%D', $_->edate).
225 my $remaining = $amount;
226 foreach my $cust_bill_pkg ( @cust_bill_pkg ) {
227 last unless $remaining;
228 my $this_amount =sprintf('%.2f',
229 $remaining <= $cust_bill_pkg->exemptable_per_month
231 : $cust_bill_pkg->exemptable_per_month
234 push @cust_tax_exempt_pkg, new FS::cust_tax_exempt_pkg {
235 'billpkgnum' => $cust_bill_pkg->billpkgnum,
236 'taxnum' => $cust_tax_exempt->taxnum,
237 'year' => $cust_tax_exempt->year,
238 'month' => $cust_tax_exempt->month,
239 'amount' => $this_amount,
242 $remaining -= $this_amount;
248 foreach my $cust_tax_exempt_pkg ( @cust_tax_exempt_pkg ) {
249 my $error = $cust_tax_exempt_pkg->insert;
250 #my $error = $cust_tax_exempt_pkg->check;
252 warn "*** error inserting cust_tax_exempt_pkg record: $error\n";
253 next; #not necessary.. H_CUST_TAX_EXEMPT;
255 #not necessary, incorrect $total_cust_tax_exempt_pkg will error it out
256 # roll back at least the entire cust_tax_exempt transaction
257 # next CUST_TAX_EXEMPT;
260 $num_cust_tax_exempt_pkg++;
262 $total_cust_tax_exempt_pkg += $cust_tax_exempt_pkg->amount;
268 $total_cust_tax_exempt_pkg = sprintf('%.2f', $total_cust_tax_exempt_pkg );
270 unless ( $total_cust_tax_exempt_pkg == $cust_tax_exempt->amount ) {
271 warn "total h_ amount $total_cust_tax_exempt_pkg != cust_tax_exempt.amount ".
272 $cust_tax_exempt->amount.
273 ";\n not migrating exemption ". $cust_tax_exempt->exemptnum. " for ".
274 $cust_tax_exempt->month. '/'. $cust_tax_exempt->year.
275 ' (custnum '. $cust_tax_exempt->custnum. ") ".
276 #"\n (sdate < ". time2str('%D', $sdate_before ).
277 "\n (sdate <= ". time2str('%D', $sdate_before ). " [$sdate_before]".
278 #' / edate > '. time2str('%D', $edate_after ). ')'.
279 ' / edate >= '. time2str('%D', $edate_after ). " [$edate_after])".
282 # roll back at least the entire cust_tax_exempt transaction
285 # next CUST_TAX_EXEMPT;
289 # remove the cust_tax_exempt record
290 my $error = $cust_tax_exempt->delete;
292 #roll back at least the entire cust_tax_exempt transaction
295 #next CUST_TAX_EXEMPT;
299 $num_cust_tax_exempt_migrated++;
300 $total_cust_tax_exempt_migrated += $cust_tax_exempt->amount;
302 $num_cust_tax_exempt_pkg_migrated += $num_cust_tax_exempt_pkg;
303 $total_cust_tax_exempt_pkg_migrated += $total_cust_tax_exempt_pkg;
305 # commit the transaction
310 $total_cust_tax_exempt_migrated =
311 sprintf('%.2f', $total_cust_tax_exempt_migrated );
312 $total_cust_tax_exempt_pkg_migrated =
313 sprintf('%.2f', $total_cust_tax_exempt_pkg_migrated );
316 "$num_cust_tax_exempt_migrated / $num_cust_tax_exempt (".
317 sprintf('%.2f', 100 * $num_cust_tax_exempt_migrated / $num_cust_tax_exempt).
318 '%) cust_tax_exempt records migrated ($'. $total_cust_tax_exempt_migrated.
319 ")\n to $num_cust_tax_exempt_pkg_migrated cust_tax_exempt_pkg records".
320 ' ($'. $total_cust_tax_exempt_pkg_migrated. ')'.
321 "\n in ". duration(time-$start). "\n"