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 );
36 unless $FS::CurrentUser::CurrentUser->access_right('Financial reports');
38 my $DEBUG = $cgi->param('DEBUG') || 0;
40 my $report_title = FS::cust_payby->future_autobill_report_title;
41 my $agentnum = $cgi->param('agentnum')
42 if $cgi->param('agentnum') =~ /^\d+/;
47 # Work with all date/time operations @ 12 noon
53 my $now_dt = DateTime->now;
54 $now_dt = DateTime->new(
55 month => $now_dt->month,
57 year => $now_dt->year,
61 # Get target date from form
62 if ($cgi->param('target_date')) {
63 my ($mm, $dd, $yy) = split /[\-\/]/,$cgi->param('target_date');
64 $target_dt = DateTime->new(
69 ) if $mm && $dd & $yy;
71 # Catch a date from the past: time only travels in one direction
72 $target_dt = undef if $target_dt->epoch < $now_dt->epoch;
75 # without a target date, default to tomorrow
77 $target_dt = $now_dt->clone->add( days => 1 );
80 # Create a range of dates from today until the given report date
81 # (leaving the probably useless 'quick-report' mode, but disabled)
82 if ( 1 || $cgi->param('multiple_billing_dates')) {
83 my $walking_dt = DateTime->from_epoch(epoch => $now_dt->epoch);
84 until ($walking_dt->epoch > $target_dt->epoch) {
85 push @target_dates, $walking_dt->epoch;
86 $walking_dt->add(days => 1);
89 push @target_dates, $target_dt->epoch;
92 # List all customers with an auto-bill method that's not expired
93 my %cust_payby = map {$_->custnum => $_} qsearch({
94 table => 'cust_payby',
95 addl_from => 'JOIN cust_main USING (custnum)',
96 hashref => { weight => { op => '>', value => '0' }},
97 order_by => " ORDER BY weight DESC ",
100 payby IN ('CHEK','DCHK','DCHEK')
101 OR ( paydate > '".$target_dt->ymd."')
103 AND " . $FS::CurrentUser::CurrentUser->agentnums_sql
104 . ($agentnum ? "AND cust_main.agentnum = $agentnum" : ''),
107 my $fakebill_time = time();
116 # Supress COMMIT statements
117 my $oldAutoCommit = $FS::UID::AutoCommit;
118 local $FS::UID::AutoCommit = 0;
119 local $FS::UID::ForceObeyAutoCommit = 1;
121 # Suppress notices generated by billing events
122 local $FS::Misc::DISABLE_ALL_NOTICES = 1;
124 # Bypass payment processing, recording a fake payment
125 local $FS::cust_main::Billing_Realtime::BOP_TESTING = 1;
126 local $FS::cust_main::Billing_Realtime::BOP_TESTING_SUCCESS = 1;
128 warn sprintf "Report involves %s customers", scalar keys %cust_payby
131 # Run bill_and_collect(), for each customer with an autobill payment method,
132 # for each day represented in the report
133 for my $custnum (keys %cust_payby) {
134 my $cust_main = qsearchs('cust_main', {custnum => $custnum});
136 warn "-- Processing custnum $custnum\n"
139 # walk forward through billing dates
140 for my $query_epoch (@target_dates) {
141 $FS::cust_main::Billing_Realtime::BOP_TESTING_TIMESTAMP = $query_epoch;
142 my $return_bill = [];
144 warn "---- Set billtime to ".
145 DateTime->from_epoch( epoch => $query_epoch )."\n"
148 my $error = $cust_main->bill_and_collect(
149 time => $query_epoch,
150 return_bill => $return_bill,
155 warn "!!! $error (simulating future billing)\n" if $error;
158 # Generate report rows from recorded payments in cust_pay
160 qsearch( cust_pay => {
162 _date => { op => '>=', value => $fakebill_time },
166 name => $cust_main->name,
167 _date => $cust_pay->_date,
171 { class => 'gridreport', value => $custnum },
173 # Customer name / customer link
174 { class => 'gridreport',
175 value => qq{<a href="${fsurl}view/cust_main.cgi?${custnum}">} . encode_entities( $cust_main->name ). '</a>',
180 { class => 'gridreport',
181 value => $cust_pay->paid,
186 { class => 'gridreport',
187 value => DateTime->from_epoch( epoch => $cust_pay->_date )->ymd
191 { class => 'gridreport',
192 value => encode_entities( $cust_pay->paycardtype || $cust_pay->payby ),
195 # Masked Payment Instrument
196 { class => 'gridreport',
197 value => encode_entities( $cust_pay->paymask ),
204 # Roll back database at the end of each customer
205 # Makes the report slighly slower, but ensures only one customer row
208 warn "-- custnum $custnum -- rollback()\n" if $DEBUG;
209 dbh->rollback if $oldAutoCommit;
211 } # /foreach $custnum
213 warn("future_autobill.html report generated error $@") if $@;
215 # Sort output by date, and format for output to grid-report.html
218 { class => 'gridreport', value => '#', header => 1 },
219 { class => 'gridreport', value => 'Name', header => 1 },
220 { class => 'gridreport', value => 'Amount', header => 1 },
221 { class => 'gridreport', value => 'Date', header => 1 },
222 { class => 'gridreport', value => 'Type', header => 1 },
223 { class => 'gridreport', value => 'Account', header => 1 },
227 sort { $a->{_date} <=> $b->{_date} || $a->{name} cmp $b->{name} }
230 # grid-report.html requires a parallel @rows parameter to accompany @cells
231 @rows = map { {class => 'gridreport'} } 1..scalar(@cells);