10 use DateTime::Format::DateParse;
12 use FS::cust_credit_bill;
14 use FS::Record qw( qsearch qsearchs );
15 use FS::UID qw( adminsuidsetup );
20 local $FS::UID::AutoCommit = 0;
36 'csv_dir=s' => \$csv_dir,
37 'start-date=s' => \$start_date,
38 'end-date=s' => \$end_date,
39 'taxnums=s' => \@taxnums,
40 'credit-reasonnum:s' => \$credit_reasonnum,
41 'credit-addlinfo:s' => \$credit_addlinfo,
42 'insert-credits' => \$insert_credits,
43 'apply-credits' => \$apply_credits,
50 my $dbh = adminsuidsetup( $freeside_user )
51 or die "Bad username: $freeside_user\n";
53 my $log = FS::Log->new('freeside-issue-credit-for-taxnums');
55 my @tax_rows = get_tax_rows()
56 or die "No tax rows found matching search criteria\n";
58 say sprintf 'Found %s rows from cust_bill_pkg_tax_location', scalar @tax_rows;
60 write_tax_rows_csv_file( \@tax_rows );
62 my @credits = get_cust_credit_amounts( \@tax_rows );
64 # warn Dumper({ credits => \@credits }); exit;
66 write_cust_credit_summary_csv_file( @credits );
68 unless ( $insert_credits ) {
70 Option --apply-credits was not specified, no credits written to customers
72 Please review the generated CSV files, and re-run with --apply-credits
73 to issue credit adjustments
77 apply_cust_credits( @credits );
80 $FS::UID::AutoCommit = 1;
81 say "Done - credits written to database";
85 sub apply_cust_credits {
88 my $csv_fn = "$csv_dir/cust_credit.csv";
90 open my $csv_fh, '>', $csv_fn
91 or die "Unable to write to CSV file $csv_fn: $!";
104 say $csv_fh join ',' => @csv_cols;
106 for my $credit ( @credits ) {
108 my $cust_credit = FS::cust_credit->new({
109 custnum => $credit->{custnum},
110 amount => $credit->{amount},
111 reasonnum => $credit_reasonnum,
112 addlinfo => $credit_addlinfo,
113 usernum => 6, # nobody
116 if ( my $error = $cust_credit->insert ) {
120 my $msg = sprintf 'Issued credit to custnum:%s for invnum:%s for amount %s',
127 say $csv_fh join ',' => (
128 map { $_ =~ /\D/ ? qq["$_"] : $_ } (
129 $cust_credit->crednum,
132 map { $cust_credit->$_ }
133 qw/ _date amount usernum reasonnum addlinfo/
137 if ( $apply_credits ) {
138 my $cust_credit_bill = FS::cust_credit_bill->new({
139 crednum => $cust_credit->crednum,
140 invnum => $credit->{invnum},
141 amount => $credit->{amount},
143 if ( my $error = $cust_credit_bill->insert ) {
152 say sprintf 'Wrote %s customer credits to [%s]', scalar( @credits ), $csv_fn;
155 sub write_cust_credit_summary_csv_file {
158 my $csv_fn = "$csv_dir/customer_adjustments.csv";
160 open my $csv_fh, '>', $csv_fn
161 or die "Unable to write to CSV file $csv_fn: $!";
163 say $csv_fh join ',' => qw(
175 for my $credit ( @credits ) {
176 my $cust_main = qsearchs( cust_main => { custnum => $credit->{custnum} })
177 or die "Error finding custnum($credit->{custnum}) in database!";
179 say $csv_fh join ',' => (
180 map { $_ =~ /\D/ ? qq["$_"] : $_ } (
181 sprintf('%.2f', $credit->{amount}),
186 $cust_main->ship_location->address1,
187 $cust_main->ship_location->city,
188 $cust_main->ship_location->state,
189 $cust_main->ship_location->zip
196 say sprintf 'Wrote %s customer credits to [%s]', scalar( @credits ), $csv_fn;
199 sub get_cust_credit_amounts {
200 my $tax_rows = shift;
204 for my $row (@$tax_rows ) {
206 custnum => $row->cust_bill_pkg->cust_bill->custnum,
207 invnum => $row->cust_bill_pkg->cust_bill->invnum,
208 amount => $row->amount,
215 sub write_tax_rows_csv_file {
217 my $tax_rows = shift;
219 my $csv_fn = "$csv_dir/cust_bill_pkg_tax_location.csv";
221 open my $csv_fh, '>', $csv_fn
222 or die "Unable to write to CSV file $csv_fn: $!";
225 billpkgtaxlocationnum
238 say $csv_fh join ',' => @cols;
240 for my $row ( @$tax_rows ) {
241 say $csv_fh join ',' => (
243 map { $_ =~ /\D/ ? qq["$_"] : $_ }
247 $row->cust_bill_pkg->cust_bill->custnum,
248 $row->cust_bill_pkg->invnum,
254 say sprintf 'Wrote %s matched rows into [%s]', scalar(@$tax_rows), $csv_fn;
260 DateTime::Format::DateParse
261 ->parse_datetime( $start_date )
267 DateTime::Format::DateParse
268 ->parse_datetime( $end_date )
275 table => 'cust_bill_pkg_tax_location',
277 LEFT JOIN cust_bill_pkg USING (billpkgnum)
278 LEFT JOIN cust_bill ON cust_bill_pkg.invnum = cust_bill.invnum
281 WHERE cust_bill_pkg_tax_location.taxnum IN (".join(',',@taxnums).")
282 AND taxtype = 'FS::cust_main_county'
283 AND cust_bill._date >= $start_epoch
284 AND cust_bill._date <= $end_eopch
291 $freeside_user = shift @ARGV
292 or error_and_help('freesidee_user parameter required');
294 error_and_help( '--csv_dir is required' )
296 error_and_help( '--start-date is required' )
298 error_and_help( '--end-date is required' )
300 error_and_help( '--taxnums is required' )
302 error_and_help( '--credit-reasonnum is required with --insert-credits' )
303 if $insert_credits && !$credit_reasonnum;
304 error_and_help( '--credit-addlinfo is required with --insert-credits' )
305 if $insert_credits && !$credit_addlinfo;
307 error_and_help( "csv dir ($csv_dir) is not a writable directory" )
308 unless -d $csv_dir && -r $csv_dir;
310 error_and_help( "start_date($start_date) is not a valid date string")
311 unless DateTime::Format::DateParse->parse_datetime( $start_date );
312 error_and_help( "end_date($end_date) is not a valid date string")
313 unless DateTime::Format::DateParse->parse_datetime( $end_date );
315 @taxnums = split(/,/,join(',',@taxnums));
316 error_and_help( "taxnum($_) is not a valid integer" )
317 for grep { $_ =~ /\D/ } @taxnums;
319 error_and_help( "credit-reasonnum($credit_reasonnum) is not a valid integer" )
320 if $credit_reasonnum && $credit_reasonnum =~ /\D/;
324 $Data::Dumper::Sortkeys = 1;
325 $Data::Dumper::Indent = 1;
326 $Data::Dumper::Varname = 'OPTIONS';
328 say "\nProceeding with options:\n";
331 '--csv_dir' => $csv_dir,
332 '--start-date' => $start_date,
333 '--end_date' => $end_date,
334 '--taxnums' => join(',',@taxnums),
335 '--credit-reasonnum' => $credit_reasonnum || 'undef',
336 '--credit-addlinfo' => $credit_addlinfo || 'undef',
337 '--insert-credits' => $insert_credits ? 'True' : 'False',
338 '--apply-credits' => $apply_credits ? 'True' : 'False',
341 if ( $insert_credits ) {
342 print "\nYou have chosed to write credits to the database\n"
343 . "Please review your choices\n\n"
345 my $yn = <STDIN>; chomp $yn;
346 die "ABORT!\n\n" unless lc $yn eq 'y';
352 -message => sprintf( "\n\nError:\n\t%s\n\n", shift ),
362 freeside-issue-credit-for-taxnums
366 freeside freeside-issue-credit-for-taxnums [options] [freeside_user]
374 Display help and exit
376 =item B<--csv_dir> [directory]
378 Directory to save CSV reports into
382 =item cust_bill_pkg_location.csv
384 Contains a list of all rows from cust_bill_pkg to be credited
386 =item cust_adjustments.csv
388 Contains a list of all intended customer adjustments amounts
390 =item cust_credit.csv
392 Contains all rows created in cust_credit to issue customer adjustments
396 =item B<--start-date> [yyyy-mm-dd]
398 The start of the date range to search for invoices containing taxes to credit
400 =item B<--end-date> [yyyy-mm-dd]
402 The end of the date range to search for invoices containing taxes to credit
404 =item B<--taxnums> [123,124,125,126]
406 A comma separated list, with no spaces, of taxnums to issue credits for
408 =item B<--credit-reasonnum> [22]
410 The credit reasonnum to be attached to issued credits
412 =item B<--credit-addlinfo> "[Credits happen for this reason]"
414 Comment field attached to issued credits. Enclose text within quotes.
416 =item B<--insert-credits>
418 Unless this flag is set, no changes will be written to customer accounts
420 =item B<--apply-credits>
422 If this flag is set, created credits will be applied to the original bill
423 that created the charge to be refunded. If you want the credit to be
424 created as an unapplied credit, do not set this flag
430 Tool to issue credit to customers when taxes were charged in error
432 Given a list of taxnums, and a date range, utility will compile a CSV report
433 of customer charges for those taxnums.
435 When directed, utility will issue a credit to the account of each of those
436 customers, and generate a CSV report describing those credits for reporting