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
15 <& elements/grid-report.html,
16 title => $report_title,
20 table_class => 'gridreport',
22 <style type="text/css">
23 table.gridreport { margin: .5em; border: solid 1px #aaa; }
24 th.gridreport { background-color: #ccc; }
25 tr.gridreport:nth-child(even) { background-color: #eee; }
26 tr.gridreport:nth-child(odd) { background-color: #fff; }
27 td.gridreport { margin: 0 .2em; padding: 0 .4em; }
33 use FS::UID qw( dbh myconnect );
36 unless $FS::CurrentUser::CurrentUser->access_right('Financial reports');
38 my $DEBUG = $cgi->param('DEBUG') || 0;
43 # Work with all date/time operations @ 12 noon
49 my $now_dt = DateTime->now;
50 $now_dt = DateTime->new(
51 month => $now_dt->month,
53 year => $now_dt->year,
57 # Get target date from form
58 if ($cgi->param('target_date')) {
59 my ($mm, $dd, $yy) = split /[\-\/]/,$cgi->param('target_date');
60 $target_dt = DateTime->new(
65 ) if $mm && $dd & $yy;
67 # Catch a date from the past: time only travels in one direction
68 $target_dt = undef if $target_dt->epoch < $now_dt->epoch;
71 # without a target date, default to tomorrow
73 $target_dt = $now_dt->clone->add( days => 1 );
76 # Create a range of dates from today until the given report date
77 # (leaving the probably useless 'quick-report' mode, but disabled)
78 if ( 1 || $cgi->param('multiple_billing_dates')) {
79 my $walking_dt = DateTime->from_epoch(epoch => $now_dt->epoch);
80 until ($walking_dt->epoch > $target_dt->epoch) {
81 push @target_dates, $walking_dt->epoch;
82 $walking_dt->add(days => 1);
85 push @target_dates, $target_dt->epoch;
88 # List all customers with an auto-bill method that's not expired
89 my %cust_payby = map {$_->custnum => $_} qsearch({
90 table => 'cust_payby',
92 weight => { op => '>', value => '0' },
94 order_by => " ORDER BY weight DESC ",
97 payby IN ('CHEK','DCHK')
98 OR ( paydate > '".$target_dt->ymd."')
103 my $fakebill_time = time();
112 # Create new database handle and supress all COMMIT statements
113 my $oldAutoCommit = $FS::UID::AutoCommit;
114 local $FS::UID::AutoCommit = 0;
115 local $FS::UID::ForceObeyAutoCommit = 1;
117 # Suppress notices generated by billing events
118 local $FS::Misc::DISABLE_ALL_NOTICES = 1;
120 # Bypass payment processing, recording a fake payment
121 local $FS::cust_main::Billing_Realtime::BOP_TESTING = 1;
122 local $FS::cust_main::Billing_Realtime::BOP_TESTING_SUCCESS = 1;
124 warn sprintf "Report involves %s customers", scalar keys %cust_payby
127 # Run bill_and_collect(), for each customer with an autobill payment method,
128 # for each day represented in the report
129 for my $custnum (keys %cust_payby) {
130 my $cust_main = qsearchs('cust_main', {custnum => $custnum});
132 warn "-- Processing custnum $custnum\n"
135 # walk forward through billing dates
136 for my $query_epoch (@target_dates) {
137 $FS::cust_main::Billing_Realtime::BOP_TESTING_TIMESTAMP = $query_epoch;
138 my $return_bill = [];
140 warn "---- Set billtime to ".
141 DateTime->from_epoch( epoch => $query_epoch )."\n"
144 my $error = $cust_main->bill_and_collect(
145 time => $query_epoch,
146 return_bill => $return_bill,
151 warn "!!! $error (simulating future billing)\n" if $error;
154 # Generate report rows from recorded payments in cust_pay
156 qsearch( cust_pay => {
158 _date => { op => '>=', value => $fakebill_time },
162 name => $cust_main->name,
163 _date => $cust_pay->_date,
167 { class => 'gridreport', value => $custnum },
169 # Customer name / customer link
170 { class => 'gridreport',
171 value => qq{<a href="${fsurl}view/cust_main.cgi?${custnum}">} . encode_entities( $cust_main->name ). '</a>',
176 { class => 'gridreport',
177 value => $cust_pay->paid,
182 { class => 'gridreport',
183 value => DateTime->from_epoch( epoch => $cust_pay->_date )->ymd
187 { class => 'gridreport',
188 value => encode_entities( $cust_pay->paycardtype || $cust_pay->payby ),
191 # Masked Payment Instrument
192 { class => 'gridreport',
193 value => encode_entities( $cust_pay->paymask ),
200 # Roll back database at the end of each customer
201 # Makes the report slighly slower, but ensures only one customer row
204 warn "-- custnum $custnum -- rollback()\n";
205 dbh->rollback if $oldAutoCommit;
207 } # /foreach $custnum
209 warn("future_autobill.html report generated error $@") if $@;
211 # Sort output by date, and format for output to grid-report.html
214 { class => 'gridreport', value => '#', header => 1 },
215 { class => 'gridreport', value => 'Name', header => 1 },
216 { class => 'gridreport', value => 'Amount', header => 1 },
217 { class => 'gridreport', value => 'Date', header => 1 },
218 { class => 'gridreport', value => 'Type', header => 1 },
219 { class => 'gridreport', value => 'Account', header => 1 },
223 sort { $a->{_date} <=> $b->{_date} || $a->{name} cmp $b->{name} }
226 # grid-report.html requires a parallel @rows parameter to accompany @cells
227 @rows = map { {class => 'gridreport'} } 1..scalar(@cells);
229 # Dynamic report title
230 my $title_types = '';
231 my $card_count = FS::cust_payby->count_autobill_cards;
232 my $check_count = FS::cust_payby->count_autobill_checks;
233 if ( $card_count && $check_count ) {
234 $title_types = 'Card and Check';
235 } elsif ( $card_count ) {
236 $title_types = 'Card';
237 } elsif ( $check_count ) {
238 $title_types = 'Check';
241 my $report_title = sprintf(
242 'Upcoming Auto Bill %s Transactions',