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,
38 % if ( %pmt_type_subtotal ) {
39 <table class="gridreport" style="margin-left: 2em;">
41 <th class="gridreport" colspan="2">
45 % for my $pmt_type ( sort keys %pmt_type_subtotal ) {
46 <tr class="gridreport">
47 <td class="gridreport" style="text-align: right; margin-right: 1em;">
48 <% sprintf '$%.2f', $pmt_type_subtotal{ $pmt_type } %>
50 <td class="gridreport">
55 % $pmt_type_subtotal{Total} += $_ for values %pmt_type_subtotal;
56 <tr class="gridreport" style="border-top: solid 1px #999;">
57 <td class="gridreport" style="text-align: right; margin-right: 1em; border-top: solid 1px #666;">
58 <% sprintf( '$%.2f', $pmt_type_subtotal{Total} ) %>
60 <td class="gridreport" style="border-top: solid 1px #666;">
69 use FS::Misc::Savepoint;
70 use FS::Report::Queued::FutureAutobill;
71 use FS::UID qw( dbh );
74 unless $FS::CurrentUser::CurrentUser->access_right('Financial reports');
76 my $job = $FS::Report::Queued::FutureAutobill::job;
78 $job->update_statustext('0,Finding customers') if $job;
80 my $DEBUG = $cgi->param('DEBUG') || 0;
82 my $agentnum = $cgi->param('agentnum')
83 if $cgi->param('agentnum') =~ /^\d+/;
88 # Work with all date/time operations @ 12 noon
94 my $now_dt = DateTime->now;
95 $now_dt = DateTime->new(
96 month => $now_dt->month,
98 year => $now_dt->year,
102 # Get target date from form
103 if ($cgi->param('target_date')) {
104 # DateTime::Format::DateParse would be better
105 my ($mm, $dd, $yy) = split /[\-\/]/,$cgi->param('target_date');
106 ( $yy, $mm, $dd ) = ( $mm, $dd, $yy ) if $mm > 1900;
108 $target_dt = DateTime->new(
113 ) if $mm && $dd && $yy;
115 # Catch a date from the past: time only travels in one direction
117 unless $target_dt && $now_dt && $now_dt <= $target_dt;
120 # without a target date, default to tomorrow
121 unless ($target_dt) {
122 $target_dt = $now_dt->clone->add( days => 1 );
125 my $report_title = FS::cust_payby->future_autobill_report_title;
126 my $report_subtitle = sprintf(
129 $target_dt->mdy('/'),
132 # Create a range of dates from today until the given report date
133 # (leaving the probably useless 'quick-report' mode, but disabled)
134 if ( 1 || $cgi->param('multiple_billing_dates')) {
135 my $walking_dt = DateTime->from_epoch(epoch => $now_dt->epoch);
136 until ($walking_dt->epoch > $target_dt->epoch) {
137 push @target_dates, $walking_dt->epoch;
138 $walking_dt->add(days => 1);
141 push @target_dates, $target_dt->epoch;
144 # List all customers with an auto-bill method that's not expired
145 my %cust_payby = map {$_->custnum => $_} qsearch({
146 table => 'cust_payby',
147 addl_from => 'JOIN cust_main USING (custnum)',
148 hashref => { weight => { op => '>', value => '0' }},
149 order_by => " ORDER BY weight DESC ",
152 cust_payby.payby IN ('CHEK','DCHK','DCHEK')
153 OR ( cust_payby.paydate > '".$target_dt->ymd."')
155 AND " . $FS::CurrentUser::CurrentUser->agentnums_sql
156 . ($agentnum ? "AND cust_main.agentnum = $agentnum" : ''),
159 my $completion_target = scalar(keys %cust_payby) * scalar( @target_dates );
160 my $completion_progress = 0;
162 my $fakebill_time = time();
165 my %pmt_type_subtotal;
172 # Supress COMMIT statements
173 my $oldAutoCommit = $FS::UID::AutoCommit;
174 local $FS::UID::AutoCommit = 0;
175 local $FS::UID::ForceObeyAutoCommit = 1;
177 # Suppress notices generated by billing events
178 local $FS::Misc::DISABLE_ALL_NOTICES = 1;
180 # Bypass payment processing, recording a fake payment
181 local $FS::cust_main::Billing_Realtime::BOP_TESTING = 1;
182 local $FS::cust_main::Billing_Realtime::BOP_TESTING_SUCCESS = 1;
184 my $savepoint_label = 'future_autobill';
185 savepoint_create( $savepoint_label );
187 warn sprintf "Report involves %s customers", scalar keys %cust_payby
190 # Run bill_and_collect(), for each customer with an autobill payment method,
191 # for each day represented in the report
192 for my $custnum (keys %cust_payby) {
193 my $cust_main = qsearchs('cust_main', {custnum => $custnum});
195 warn "-- Processing custnum $custnum\n"
198 # walk forward through billing dates
199 for my $query_epoch (@target_dates) {
200 $FS::cust_main::Billing_Realtime::BOP_TESTING_TIMESTAMP = $query_epoch;
201 my $return_bill = [];
203 warn "---- Set billtime to ".
204 DateTime->from_epoch( epoch => $query_epoch )."\n"
207 my $error = $cust_main->bill_and_collect(
208 time => $query_epoch,
209 return_bill => $return_bill,
214 warn "!!! $error (simulating future billing)\n" if $error;
216 my $statustext = sprintf(
217 '%s,Simulating upcoming invoices and payments',
218 int( ( ++$completion_progress / $completion_target ) * 100 )
220 $job->update_statustext( $statustext ) if $job;
221 warn "[ $completion_progress / $completion_target ] $statustext\n"
227 # Generate report rows from recorded payments in cust_pay
229 qsearch( cust_pay => {
231 _date => { op => '>=', value => $fakebill_time },
235 name => $cust_main->name,
236 _date => $cust_pay->_date,
240 { class => 'gridreport', value => $custnum },
242 # Customer name / customer link
243 { class => 'gridreport',
244 value => qq{<a href="${fsurl}view/cust_main.cgi?${custnum}">} . encode_entities( $cust_main->name ). '</a>',
249 { class => 'gridreport',
250 value => $cust_pay->paid,
255 { class => 'gridreport',
256 value => DateTime->from_epoch( epoch => $cust_pay->_date )->ymd
260 { class => 'gridreport',
261 value => encode_entities( $cust_pay->paycardtype || $cust_pay->payby ),
264 # Masked Payment Instrument
265 { class => 'gridreport',
266 value => encode_entities( $cust_pay->paymask ),
271 $pmt_type_subtotal{ $cust_pay->paycardtype || $cust_pay-> payby }
276 # Roll back database at the end of each customer
277 # Makes the report slighly slower, but ensures only one customer row
280 warn "-- custnum $custnum -- rollback()\n" if $DEBUG;
281 savepoint_rollback( $savepoint_label );
282 dbh->rollback if $oldAutoCommit;
284 } # /foreach $custnum
286 warn("future_autobill.html report generated error $@") if $@;
288 # Sort output by date, and format for output to grid-report.html
291 { class => 'gridreport', value => '#', header => 1 },
292 { class => 'gridreport', value => 'Name', header => 1 },
293 { class => 'gridreport', value => 'Amount', header => 1 },
294 { class => 'gridreport', value => 'Date', header => 1 },
295 { class => 'gridreport', value => 'Type', header => 1 },
296 { class => 'gridreport', value => 'Account', header => 1 },
300 sort { $a->{_date} <=> $b->{_date} || $a->{name} cmp $b->{name} }
303 # grid-report.html requires a parallel @rows parameter to accompany @cells
304 @rows = map { {class => 'gridreport'} } 1..scalar(@cells);