my( $start, $end ) = @_;
"SQL EXPRESSION BASED ON $start AND $end";
+ # where $start and $end are unix timestamps
};
</%doc>
my $as = $opt{'no_as'} ? '' : " AS rangecol_${start}_$end";
- my $sql = &{$range_sub}( $start, $end, $opt{'offset'} ); #%opt?
+ my $offset = $opt{'offset'} || 0;
+ # Time::ParseDate for excruciating correctness
+ # Always use $offset - 1day + 1sec = the last second of that day
+ $start = parsedate('00:00 '.($start + $offset - 1).' days ago') - 1;
+ $end = $end ?
+ parsedate('00:00 '.($end + $offset - 1).' days ago') - 1 :
+ '';
+ my $cutoff = parsedate('00:00 '.($offset - 1).' days ago') - 1;
+
+ my $sql = &{$range_sub}( $start, $end, $cutoff ); #%opt?
$sql = "SUM($sql)" if $opt{'sum'};
# )
sub balance {
- my($start, $end, $offset) = @_; #, %opt ?
- #handle start and end ranges (86400 = 24h * 60m * 60s)
- my $str2time = str2time_sql;
- my $closing = str2time_sql_closing;
-
- # $end == 0 means "+infinity", while $start == 0 really means 0
- # so we should always include a start condition
- $start = "( $str2time now() $closing - ". ($start + $offset) * 86400 . ' )';
- # but only include an end condition if $end != 0
- $end = $end ?
- "( $str2time now() $closing - ". ($end + $offset) * 86400 . ' )'
- : '';
-
- #$opt{'unapplied_date'} = 1;
-
- FS::cust_main->balance_date_sql( $start, $end, 'unapplied_date'=>1,
- 'cutoff' => "( $str2time now() $closing - ".$offset * 86400 . ')' );
+ my($start, $end, $cutoff) = @_; #, %opt ?
+ FS::cust_main->balance_date_sql( $start, $end,
+ 'cutoff' => $cutoff,
+ 'unapplied_date'=>1,
+ );
}
</%once>
<%once>
sub unapplied_payments {
- my($start, $end, $offset) = @_;
-
- #handle start and end ranges (86400 = 24h * 60m * 60s)
- my $str2time = str2time_sql;
- my $closing = str2time_sql_closing;
- $start = "( $str2time now() $closing - ".($start + $offset) * 86400 . ' )';
- $end = $end ?
- "( $str2time now() $closing - ".($end + $offset) * 86400 . ' )'
- : '';
+ my($start, $end, $cutoff) = @_;
FS::cust_main->unapplied_payments_date_sql( $start, $end,
- 'cutoff' => "( $str2time now() $closing - ".$offset * 86400 . ')' );
-
+ 'cutoff' => $cutoff,
+ );
}
</%once>