4 use vars qw( $opt_d $opt_f $opt_s $opt_p $opt_m $opt_c $opt_e $opt_r $opt_k );
6 use Date::Parse qw(str2time);
7 use FS::UID qw( dbh adminsuidsetup );
8 use FS::Record qw( qsearch qsearchs );
9 use FS::cust_main_county;
13 use FS::cust_credit_bill;
15 getopts('d:f:s:pm:c:erk');
17 my $user = shift or &usage;
20 #i should be an option
21 my $taxname = 'HST, ON';
24 my $oldAutoCommit = $FS::UID::AutoCommit;
25 local $FS::UID::AutoCommit = 0;
29 # find tax classes with double taxes
32 #my $sql = "select distinct taxclass from cust_main_county where taxname = '$taxname';";
33 #my $sth = dbh->prepare($sql) or die dbh->errstr;
34 #$sth->execute or die $sth->errstr;
35 #my @taxclasses = map $_->[0], @{ $sth->fetchall_arrayref() };
39 # my @cust_main_county = qsearch('cust_main_county', {
40 # 'taxclass' => $taxclass,
41 # 'taxname' => $taxname,
42 # 'tax' => { op=>'>', value=>'0' },
45 # $taxclass => ( scalar(@cust_main_county) > 1 );
49 my %taxclass = map { $_ => 1 } (
51 'ComputerSetup-Remote',
52 'Config File- Static IPs',
60 'InternetService_WEx',
61 'LondDistanceFixedRate',
62 'LondDistanceVariableRate',
64 'SARA-ONLY-GSTPayable',
68 'SoftwareLicenseToUse',
71 'WarrantyCoverageHardware',
72 'WarrantyCoverageSoftware',
76 'LongDistanceFixedRate_WEx',
77 'LongDistanceVariableRate_WEx',
81 $FS::Record::nowarn_classload = 1;
82 $FS::Record::nowarn_classload = 1;
88 #my $extra_sql = $opt_s ? " AND cust_main.state = '$opt_s' " : '';
90 my $start_date = $opt_d ? str2time($opt_d) : 0;
91 my $end_date = $opt_f ? str2time($opt_f) - 1 : 1375471643;
93 my @cust_bill = qsearch({
94 'select' => 'cust_bill.*',
95 'table' => 'cust_bill',
96 'addl_from' => 'LEFT JOIN cust_main USING ( custnum )',
97 'hashref' => { '_date' => { op=>'>', value=>$start_date } },
98 'extra_sql' => " AND _date < $end_date ",
99 # 'extra_sql' => $extra_sql,
102 my( @billpkgnums, @setuprecurs, @amounts );
104 foreach my $cust_bill ( @cust_bill ) {
106 my $tax = $cust_bill->tax;
108 my $cust_main = $cust_bill->cust_main;
110 next if $cust_bill->charged == 0
112 or $cust_main->tax eq 'Y';
120 foreach my $cust_bill_pkg ( $cust_bill->cust_bill_pkg ) {
121 my $cust_pkg = $cust_bill_pkg->cust_pkg or next;
123 # #my $loc = $cust_pkg->cust_location_or_main;
124 # #next if $opt_s && $loc->state ne $opt_s;
125 # next if $cust_pkg->cust_location; #we did these already
126 # next if $opt_s && $cust_main->ship_state ne $opt_s;
127 # next if $opt_s && $cust_main->state eq $opt_s; #we did these already too
130 if ( my $loc = $cust_pkg->cust_location ) {
131 next if $loc->state ne $opt_s;
133 next if ($cust_main->ship_state || $cust_main->state) ne $opt_s;
137 my $part_pkg = $cust_pkg->part_pkg;
139 #these were changed already
140 # next unless $taxclass{ $part_pkg->taxclass };
142 my $h = $h{$part_pkg->pkgpart} ||= qsearchs({
143 'table' => 'h_part_pkg',
144 'hashref' => { 'pkgpart' => $part_pkg->pkgpart,
145 'history_date' => { op=>'<', value=>$cust_bill->_date },
147 'extra_sql' => "AND history_action IN ( 'insert', 'replace_new' )",
148 'order_by' => 'ORDER BY HISTORYNUM DESC LIMIT 1',
151 warn "can't find history record for pkgpart ". $part_pkg->pkgpart.
152 " (invoice ". $cust_bill->invnum. ")\n";
153 #well, we have no idea what the mystery package was, so, we have no idea
154 # if we need to credit it or not.
155 next; #assuming, not...
157 next unless $taxclass{ $h->taxclass }
158 || ( $h->taxclass eq 'AllTaxesApply' && $cust_bill->_date < 1371750732 ) ;
160 #my $amount = $cust_bill_pkg->setup + $cust_bill_pkg->recur;
161 #$credit += $rate * $amount / 100;
163 if ( $cust_bill_pkg->setup ) {
164 my $scredit = sprintf('%.2f', ($rate * $cust_bill_pkg->setup / 100) + 0.00000001);
166 push @setuprecurs, 'setup';
167 push @billpkgnums, $cust_bill_pkg->billpkgnum;
168 push @amounts, $scredit;
171 if ( $cust_bill_pkg->recur ) {
172 my $rcredit = sprintf('%.2f', ($rate * $cust_bill_pkg->recur / 100) + 0.00000001);
174 push @setuprecurs, 'recur';
175 push @billpkgnums, $cust_bill_pkg->billpkgnum;
176 push @amounts, $rcredit;
181 $credit = sprintf('%.2f', $credit + 0.00000001);
183 next if $credit == 0;
185 #$credit = sprintf('%.2f', $credit + 0.00000001);
187 warn "invoice ". $cust_bill->invnum. ": credit of $credit is more than orignally taxed ($tax)\n" if $credit > $tax;
189 warn "invoice ". $cust_bill->invnum. ": credit of $credit is more than 50% of originally taxed ($tax)\n" if $credit-0.01 > $tax/2;
191 #my $cr_percent = sprintf('%.1f', 100 * $credit / $tax);
194 $cur_cr += $_->amount foreach $cust_bill->cust_credited;
195 $cur_cr = '' if $cur_cr == 0;
197 next if $cur_cr > 0 && $opt_k;
200 #print $cust_bill->invnum. ','. $cust_bill->custnum. ",$tax,$credit,$cr_percent%\n";
201 # print $cust_bill->invnum. ','. $cust_bill->custnum. ',"'.
202 # $cust_bill->cust_main->name. '",'. "$tax,$credit,$cur_cr\n";
203 print $cust_bill->invnum. ','. $cust_bill->custnum. ',"'.
204 $cust_bill->cust_main->name. '",'. "$tax,$credit\n";
207 # if ( $opt_m && ! $opt_r ) {
209 # my $msg_template = qsearchs('msg_template', { 'msgnum' => $opt_m } )
210 # or die "Template $opt_m not found";
211 # my $error = $msg_template->send(
212 # 'cust_main' => $cust_main,
213 # 'object' => $cust_main,
216 # warn "error sending email for invnum ". $cust_bill->invnum. ','.
217 # " custnum ". $cust_bill->custnum. ": $error\n";
222 my $cust_credit = new FS::cust_credit {
223 'custnum' => $cust_main->custnum,
225 'reasonnum' => $opt_c,
227 my $error = $cust_credit->insert;
229 warn "error inserting credit: $error\n";
231 my $cust_credit_bill = new FS::cust_credit_bill {
232 'crednum' => $cust_credit->crednum,
233 'invnum' => $cust_bill->invnum,
236 my $aerror = $cust_credit_bill->insert;
238 warn "error applying credit to invnum ". $cust_bill->invnum. ": $aerror\n";
242 # if ( $opt_e && ! $opt_r ) {
243 # eval { $cust_bill->email };
245 # warn "error sending invnum ". $cust_bill->invnum. ','.
246 # " custnum ". $cust_bill->custnum. ": $@\n";
253 $dbh->rollback or die $dbh->errstr; #if $oldAutoCommit;
255 $dbh->commit or die $dbh->errstr; #if $oldAutoCommit;
259 die "usage: cust_bill-credit [ -d date ] [ -s state ] [ -p ] [ -m templatenum ] [ -c reasonnum ] [ -e ] [ -r ] employee_username\n";
268 cust_bill-credit [ -d date ] [ -s state ] [ -p ] [ -m templatenum ] [ -c reasonnum ] [ -e ] employee_username
272 Command-line tool to search for and credit invoices.
274 -d: Search for invoices starting from this date
276 -f: Search for invoice finishing on this date
278 -s: Search for invoices for packages within this state
280 -p: Print an informational line for each invoice with invnum, custnum, original tax amount, calculate credit, and credit percentage of original.
282 -m: Send an email to the customer with this message template.
284 -c: Credit the invoice for one-half of the taxation amount, using this reason.
286 -k: But, don't credit if the customer already has a credit applied against this invoice