3 Report listing upcoming auto-bill transactions
5 Spec requested the ability to run this report with a longer date range,
6 and see which charges will process on which day. Checkbox multiple_billing_dates
7 enables this functionality.
10 This is a dynamically generated report. The time this report takes to run
11 will depends on the number of customers. Installations with a high number
12 of auto-bill customers may find themselves unable to run this report
13 because of browser timeout. Report could be implemented as a queued job if
14 necessary, to solve the performance problem.
17 <& elements/grid-report.html,
18 title => 'Upcoming auto-bill transactions',
22 table_class => 'gridreport',
24 <style type="text/css">
25 table.gridreport { margin: .5em; border: solid 1px #aaa; }
26 th.gridreport { background-color: #ccc; }
27 tr.gridreport:nth-child(even) { background-color: #eee; }
28 tr.gridreport:nth-child(odd) { background-color: #fff; }
29 td.gridreport { margin: 0 .2em; padding: 0 .4em; }
36 use FS::UID qw( dbh myconnect );
39 unless $FS::CurrentUser::CurrentUser->access_right('Financial reports');
44 # Work with all date/time operations @ 12 noon
51 my $now_dt = DateTime->now;
52 $now_dt = DateTime->new(
53 month => $now_dt->month,
55 year => $now_dt->year,
59 # Get target date from form
60 if ($cgi->param('target_date')) {
61 my ($mm, $dd, $yy) = split /[\-\/]/,$cgi->param('target_date');
62 $target_dt = DateTime->new(
67 ) if $mm && $dd & $yy;
69 # Catch a date from the past: time only travels in one direction
70 $target_dt = undef if $target_dt->epoch < $now_dt->epoch;
73 # without a target date, default to tomorrow
75 $target_dt = DateTime->from_epoch( epoch => time() + 86400) ;
76 $target_dt = DateTime->new(
77 month => $target_dt->month,
78 day => $target_dt->day,
79 year => $target_dt->year,
84 # If multiple_billing_dates checkbox selected, create a range of dates
85 # from today until the given report date. Otherwise, use target date only.
86 if ($cgi->param('multiple_billing_dates')) {
87 my $walking_dt = DateTime->from_epoch(epoch => $now_dt->epoch);
88 until ($walking_dt->epoch > $target_dt->epoch) {
89 push @target_dates, $walking_dt->epoch;
90 $walking_dt->add(days => 1);
93 push @target_dates, $target_dt->epoch;
96 # List all customers with an auto-bill method
98 # my %cust_payby = map {$_->custnum => $_} qsearch({
99 # table => 'cust_payby',
101 # weight => { op => '>', value => '0' },
102 # paydate => { op => '>', value => $target_dt->ymd },
104 # order_by => " ORDER BY weight DESC ",
107 # List all customers with an auto-bill method that's not expired
108 my %cust_payby = map {$_->custnum => $_} qsearch({
109 table => 'cust_payby',
111 weight => { op => '>', value => '0' },
113 order_by => " ORDER BY weight DESC ",
114 extra_sql => " AND ( payby = 'CHEK' OR ( paydate > '".$target_dt->ymd."')) ",
122 my $temp_dbh = myconnect();
123 eval { # Creating sandbox dbh where all connections are to be rolled back
124 local $FS::UID::dbh = $temp_dbh;
125 local $FS::UID::AutoCommit = 0;
127 # Generate report data into @rows
128 for my $custnum (keys %cust_payby) {
129 my $cust_main = qsearchs('cust_main', {custnum => $custnum});
131 # walk forward through billing dates
132 for my $query_epoch (@target_dates) {
133 my $return_bill = [];
135 eval { # Don't let an error on one customer crash the report
136 my $error = $cust_main->bill(
137 time => $query_epoch,
138 return_bill => $return_bill,
141 die "$error (simulating future billing)" if $error;
143 warn ("$@: (future_autobill custnum:$custnum)");
145 if (@{$return_bill}) {
146 my $inv = $return_bill->[0];
148 name => $cust_main->name,
149 _date => $inv->_date,
151 { class => 'gridreport', value => $custnum },
152 { class => 'gridreport',
153 value => '<a href="/view/cust_main.cgi?"'.$custnum.'">'.$cust_main->name.'</a>',
156 { class => 'gridreport', value => $inv->charged, format => 'money' },
157 { class => 'gridreport', value => DateTime->from_epoch(epoch=>$inv->_date)->ymd },
158 { class => 'gridreport', value => ($cust_payby{$custnum}->payby || $cust_payby{$custnum}->paytype) },
159 { class => 'gridreport', value => $cust_payby{$custnum}->paymask },
166 } # /foreach $custnum
171 # Sort output by date, and format for output to grid-report.html
174 { class => 'gridreport', value => '#', header => 1 },
175 { class => 'gridreport', value => 'Name', header => 1 },
176 { class => 'gridreport', value => 'Amount', header => 1 },
177 { class => 'gridreport', value => 'Date', header => 1 },
178 { class => 'gridreport', value => 'Type', header => 1 },
179 { class => 'gridreport', value => 'Account', header => 1 },
183 sort { $a->{_date} <=> $b->{_date} || $a->{name} cmp $b->{name} }
186 # grid-report.html requires a parallel @rows parameter to accompany @cells
187 @rows = map { {class => 'gridreport'} } 1..scalar(@cells);