3 Report listing upcoming auto-bill transactions
5 For every customer with a valid auto-bill payment method,
6 report runs bill_and_collect() for each customer, for each
7 day, from today through the report target date. After
8 recording the results, all operations are rolled back.
10 This report relies on the ability to safely run bill_and_collect(),
11 with all exports and messaging disabled, and then to roll back the
14 This report takes time. If 200 customers have automatic
15 payment methods, and requester is looking one week ahead,
16 there will be 1,400 billing and payment cycles simulated
19 <h4><% $report_subtitle %></h4>
20 <& elements/grid-report.html,
21 title => $report_title,
25 table_class => 'gridreport',
27 <style type="text/css">
28 table.gridreport { margin: .5em; border: solid 1px #aaa; }
29 th.gridreport { background-color: #ccc; }
30 tr.gridreport:nth-child(even) { background-color: #eee; }
31 tr.gridreport:nth-child(odd) { background-color: #fff; }
32 td.gridreport { margin: 0 .2em; padding: 0 .4em; }
35 suppress_header => $job ? 1 : 0,
36 suppress_footer => $job ? 1 : 0,
41 use FS::Misc::Savepoint;
42 use FS::Report::Queued::FutureAutobill;
43 use FS::UID qw( dbh );
46 unless $FS::CurrentUser::CurrentUser->access_right('Financial reports');
48 my $job = $FS::Report::Queued::FutureAutobill::job;
50 $job->update_statustext('0,Finding customers') if $job;
52 my $DEBUG = $cgi->param('DEBUG') || 0;
54 my $agentnum = $cgi->param('agentnum')
55 if $cgi->param('agentnum') =~ /^\d+/;
60 # Work with all date/time operations @ 12 noon
66 my $now_dt = DateTime->now;
67 $now_dt = DateTime->new(
68 month => $now_dt->month,
70 year => $now_dt->year,
74 # Get target date from form
75 if ($cgi->param('target_date')) {
76 # DateTime::Format::DateParse would be better
77 my ($mm, $dd, $yy) = split /[\-\/]/,$cgi->param('target_date');
78 ( $yy, $mm, $dd ) = ( $mm, $dd, $yy ) if $mm > 1900;
80 $target_dt = DateTime->new(
85 ) if $mm && $dd && $yy;
87 # Catch a date from the past: time only travels in one direction
89 unless $target_dt && $now_dt && $now_dt <= $target_dt;
92 # without a target date, default to tomorrow
94 $target_dt = $now_dt->clone->add( days => 1 );
97 my $report_title = FS::cust_payby->future_autobill_report_title;
98 my $report_subtitle = sprintf(
101 $target_dt->mdy('/'),
104 # Create a range of dates from today until the given report date
105 # (leaving the probably useless 'quick-report' mode, but disabled)
106 if ( 1 || $cgi->param('multiple_billing_dates')) {
107 my $walking_dt = DateTime->from_epoch(epoch => $now_dt->epoch);
108 until ($walking_dt->epoch > $target_dt->epoch) {
109 push @target_dates, $walking_dt->epoch;
110 $walking_dt->add(days => 1);
113 push @target_dates, $target_dt->epoch;
116 # List all customers with an auto-bill method that's not expired
117 my %cust_payby = map {$_->custnum => $_} qsearch({
118 table => 'cust_payby',
119 addl_from => 'JOIN cust_main USING (custnum)',
120 hashref => { weight => { op => '>', value => '0' }},
121 order_by => " ORDER BY weight DESC ",
124 payby IN ('CHEK','DCHK','DCHEK')
125 OR ( paydate > '".$target_dt->ymd."')
127 AND " . $FS::CurrentUser::CurrentUser->agentnums_sql
128 . ($agentnum ? "AND cust_main.agentnum = $agentnum" : ''),
131 my $completion_target = scalar(keys %cust_payby) * scalar( @target_dates );
132 my $completion_progress = 0;
134 my $fakebill_time = time();
143 # Supress COMMIT statements
144 my $oldAutoCommit = $FS::UID::AutoCommit;
145 local $FS::UID::AutoCommit = 0;
146 local $FS::UID::ForceObeyAutoCommit = 1;
148 # Suppress notices generated by billing events
149 local $FS::Misc::DISABLE_ALL_NOTICES = 1;
151 # Bypass payment processing, recording a fake payment
152 local $FS::cust_main::Billing_Realtime::BOP_TESTING = 1;
153 local $FS::cust_main::Billing_Realtime::BOP_TESTING_SUCCESS = 1;
155 my $savepoint_label = 'future_autobill';
156 savepoint_create( $savepoint_label );
158 warn sprintf "Report involves %s customers", scalar keys %cust_payby
161 # Run bill_and_collect(), for each customer with an autobill payment method,
162 # for each day represented in the report
163 for my $custnum (keys %cust_payby) {
164 my $cust_main = qsearchs('cust_main', {custnum => $custnum});
166 warn "-- Processing custnum $custnum\n"
169 # walk forward through billing dates
170 for my $query_epoch (@target_dates) {
171 $FS::cust_main::Billing_Realtime::BOP_TESTING_TIMESTAMP = $query_epoch;
172 my $return_bill = [];
174 warn "---- Set billtime to ".
175 DateTime->from_epoch( epoch => $query_epoch )."\n"
178 my $error = $cust_main->bill_and_collect(
179 time => $query_epoch,
180 return_bill => $return_bill,
185 warn "!!! $error (simulating future billing)\n" if $error;
187 my $statustext = sprintf(
188 '%s,Simulating upcoming invoices and payments',
189 int( ( ++$completion_progress / $completion_target ) * 100 )
191 $job->update_statustext( $statustext ) if $job;
192 warn "[ $completion_progress / $completion_target ] $statustext\n"
198 # Generate report rows from recorded payments in cust_pay
200 qsearch( cust_pay => {
202 _date => { op => '>=', value => $fakebill_time },
206 name => $cust_main->name,
207 _date => $cust_pay->_date,
211 { class => 'gridreport', value => $custnum },
213 # Customer name / customer link
214 { class => 'gridreport',
215 value => qq{<a href="${fsurl}view/cust_main.cgi?${custnum}">} . encode_entities( $cust_main->name ). '</a>',
220 { class => 'gridreport',
221 value => $cust_pay->paid,
226 { class => 'gridreport',
227 value => DateTime->from_epoch( epoch => $cust_pay->_date )->ymd
231 { class => 'gridreport',
232 value => encode_entities( $cust_pay->paycardtype || $cust_pay->payby ),
235 # Masked Payment Instrument
236 { class => 'gridreport',
237 value => encode_entities( $cust_pay->paymask ),
244 # Roll back database at the end of each customer
245 # Makes the report slighly slower, but ensures only one customer row
248 warn "-- custnum $custnum -- rollback()\n" if $DEBUG;
249 savepoint_rollback( $savepoint_label );
250 dbh->rollback if $oldAutoCommit;
252 } # /foreach $custnum
254 warn("future_autobill.html report generated error $@") if $@;
256 # Sort output by date, and format for output to grid-report.html
259 { class => 'gridreport', value => '#', header => 1 },
260 { class => 'gridreport', value => 'Name', header => 1 },
261 { class => 'gridreport', value => 'Amount', header => 1 },
262 { class => 'gridreport', value => 'Date', header => 1 },
263 { class => 'gridreport', value => 'Type', header => 1 },
264 { class => 'gridreport', value => 'Account', header => 1 },
268 sort { $a->{_date} <=> $b->{_date} || $a->{name} cmp $b->{name} }
271 # grid-report.html requires a parallel @rows parameter to accompany @cells
272 @rows = map { {class => 'gridreport'} } 1..scalar(@cells);