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 % if ( keys %pmt_type_subtotal > 1 ) {
56 % $pmt_type_subtotal{Total} += $_ for values %pmt_type_subtotal;
57 <tr class="gridreport" style="border-top: solid 1px #999;">
58 <td class="gridreport" style="text-align: right; margin-right: 1em; border-top: solid 1px #666;">
59 <% sprintf( '$%.2f', $pmt_type_subtotal{Total} ) %>
61 <td class="gridreport" style="border-top: solid 1px #666;">
71 use FS::Misc::Savepoint;
72 use FS::Report::Queued::FutureAutobill;
73 use FS::UID qw( dbh );
76 unless $FS::CurrentUser::CurrentUser->access_right('Financial reports');
78 my $job = $FS::Report::Queued::FutureAutobill::job;
80 $job->update_statustext('0,Finding customers') if $job;
82 my $DEBUG = $cgi->param('DEBUG') || 0;
84 my $agentnum = $cgi->param('agentnum')
85 if $cgi->param('agentnum') =~ /^\d+/;
90 # Work with all date/time operations @ 12 noon
96 my $now_dt = DateTime->now;
97 $now_dt = DateTime->new(
98 month => $now_dt->month,
100 year => $now_dt->year,
104 # Get target date from form
105 if ($cgi->param('target_date')) {
106 # DateTime::Format::DateParse would be better
107 my ($mm, $dd, $yy) = split /[\-\/]/,$cgi->param('target_date');
108 ( $yy, $mm, $dd ) = ( $mm, $dd, $yy ) if $mm > 1900;
110 $target_dt = DateTime->new(
115 ) if $mm && $dd && $yy;
117 # Catch a date from the past: time only travels in one direction
119 unless $target_dt && $now_dt && $now_dt <= $target_dt;
122 # without a target date, default to tomorrow
123 unless ($target_dt) {
124 $target_dt = $now_dt->clone->add( days => 1 );
127 my $report_title = FS::cust_payby->future_autobill_report_title;
128 my $report_subtitle = sprintf(
131 $target_dt->mdy('/'),
134 # Create a range of dates from today until the given report date
135 # (leaving the probably useless 'quick-report' mode, but disabled)
136 if ( 1 || $cgi->param('multiple_billing_dates')) {
137 my $walking_dt = DateTime->from_epoch(epoch => $now_dt->epoch);
138 until ($walking_dt->epoch > $target_dt->epoch) {
139 push @target_dates, $walking_dt->epoch;
140 $walking_dt->add(days => 1);
143 push @target_dates, $target_dt->epoch;
146 # List all customers with an auto-bill method that's not expired
147 my %cust_payby = map {$_->custnum => $_} qsearch({
148 table => 'cust_payby',
149 addl_from => 'JOIN cust_main USING (custnum)',
150 hashref => { weight => { op => '>', value => '0' }},
151 order_by => " ORDER BY weight DESC ",
154 cust_payby.payby IN ('CHEK','DCHK','DCHEK')
155 OR ( cust_payby.paydate > '".$target_dt->ymd."')
157 AND " . $FS::CurrentUser::CurrentUser->agentnums_sql
158 . ($agentnum ? "AND cust_main.agentnum = $agentnum" : ''),
161 my $completion_target = scalar(keys %cust_payby) * scalar( @target_dates );
162 my $completion_progress = 0;
164 my $fakebill_time = time();
167 my %pmt_type_subtotal;
174 # Supress COMMIT statements
175 my $oldAutoCommit = $FS::UID::AutoCommit;
176 local $FS::UID::AutoCommit = 0;
177 local $FS::UID::ForceObeyAutoCommit = 1;
179 # Suppress notices generated by billing events
180 local $FS::Misc::DISABLE_ALL_NOTICES = 1;
182 # Bypass payment processing, recording a fake payment
183 local $FS::cust_main::Billing_Realtime::BOP_TESTING = 1;
184 local $FS::cust_main::Billing_Realtime::BOP_TESTING_SUCCESS = 1;
186 my $savepoint_label = 'future_autobill';
187 savepoint_create( $savepoint_label );
189 warn sprintf "Report involves %s customers", scalar keys %cust_payby
192 # Run bill_and_collect(), for each customer with an autobill payment method,
193 # for each day represented in the report
194 for my $custnum (keys %cust_payby) {
195 my $cust_main = qsearchs('cust_main', {custnum => $custnum});
197 warn "-- Processing custnum $custnum\n"
200 # walk forward through billing dates
201 for my $query_epoch (@target_dates) {
202 $FS::cust_main::Billing_Realtime::BOP_TESTING_TIMESTAMP = $query_epoch;
203 my $return_bill = [];
205 warn "---- Set billtime to ".
206 DateTime->from_epoch( epoch => $query_epoch )."\n"
209 my $error = $cust_main->bill_and_collect(
210 time => $query_epoch,
211 return_bill => $return_bill,
216 warn "!!! $error (simulating future billing)\n" if $error;
218 my $statustext = sprintf(
219 '%s,Simulating upcoming invoices and payments',
220 int( ( ++$completion_progress / $completion_target ) * 100 )
222 $job->update_statustext( $statustext ) if $job;
223 warn "[ $completion_progress / $completion_target ] $statustext\n"
229 # Generate report rows from recorded payments in cust_pay
231 qsearch( cust_pay => {
233 _date => { op => '>=', value => $fakebill_time },
237 name => $cust_main->name,
238 _date => $cust_pay->_date,
242 { class => 'gridreport', value => $custnum },
244 # Customer name / customer link
245 { class => 'gridreport',
246 value => qq{<a href="${fsurl}view/cust_main.cgi?${custnum}">} . encode_entities( $cust_main->name ). '</a>',
251 { class => 'gridreport',
252 value => $cust_pay->paid,
257 { class => 'gridreport',
258 value => DateTime->from_epoch( epoch => $cust_pay->_date )->ymd
262 { class => 'gridreport',
263 value => encode_entities( $cust_pay->paycardtype || $cust_pay->payby ),
266 # Masked Payment Instrument
267 { class => 'gridreport',
268 value => encode_entities( $cust_pay->paymask ),
273 $pmt_type_subtotal{ $cust_pay->paycardtype || $cust_pay-> payby }
278 # Roll back database at the end of each customer
279 # Makes the report slighly slower, but ensures only one customer row
282 warn "-- custnum $custnum -- rollback()\n" if $DEBUG;
283 savepoint_rollback( $savepoint_label );
284 dbh->rollback if $oldAutoCommit;
286 } # /foreach $custnum
288 warn("future_autobill.html report generated error $@") if $@;
290 # Sort output by date, and format for output to grid-report.html
293 { class => 'gridreport', value => '#', header => 1 },
294 { class => 'gridreport', value => 'Name', header => 1 },
295 { class => 'gridreport', value => 'Amount', header => 1 },
296 { class => 'gridreport', value => 'Date', header => 1 },
297 { class => 'gridreport', value => 'Type', header => 1 },
298 { class => 'gridreport', value => 'Account', header => 1 },
302 sort { $a->{_date} <=> $b->{_date} || $a->{name} cmp $b->{name} }
305 # grid-report.html requires a parallel @rows parameter to accompany @cells
306 @rows = map { {class => 'gridreport'} } 1..scalar(@cells);