;
}
-=item credited
+=item unapplied
-Returns the amount of this credit that is still outstanding; which is
+Returns the amount of this credit that is still unapplied/outstanding;
amount minus all refund applications (see L<FS::cust_credit_refund>) and
applications to invoices (see L<FS::cust_credit_bill>).
=cut
-sub credited {
+sub unapplied {
my $self = shift;
my $amount = $self->amount;
$amount -= $_->amount foreach ( $self->cust_credit_refund );
sprintf( "%.2f", $amount );
}
+=item credited
+
+Deprecated name for the unapplied method.
+
+=cut
+
+sub credited {
+ my $self = shift;
+ #carp "cust_credit->credited deprecated; use ->unapplied";
+ $self->unapplied(@_);
+}
+
=item cust_main
Returns the customer (see L<FS::cust_main>) for this credit.
=over 4
-=item credited_sql
+=item unapplied_sql
Returns an SQL fragment to retreive the unapplied amount.
=cut
-sub credited_sql {
+sub unapplied_sql {
#my $class = shift;
"amount
}
+=item credited_sql
+
+Deprecated name for the unapplied_sql method.
+
+=cut
+
+sub credited_sql {
+ #my $class = shift;
+
+ #carp "cust_credit->credited_sql deprecated; use ->unapplied_sql";
+
+ #$class->unapplied_sql(@_);
+ unapplied_sql();
+}
+
=back
=head1 BUGS
The delete method. The replace method.
-B<credited> and B<credited_sql> should probably be called B<unapplied> and
-B<unapplied_sql>.
+B<credited> and B<credited_sql> are now called B<unapplied> and
+B<unapplied_sql>. The old method names should start to give warnings.
=head1 SEE ALSO
=cut
sub balance_sql { "
- COALESCE( ( SELECT SUM(charged) FROM cust_bill
- WHERE cust_bill.custnum = cust_main.custnum ), 0)
- - COALESCE( ( SELECT SUM(paid) FROM cust_pay
- WHERE cust_pay.custnum = cust_main.custnum ), 0)
- - COALESCE( ( SELECT SUM(amount) FROM cust_credit
- WHERE cust_credit.custnum = cust_main.custnum ), 0)
- + COALESCE( ( SELECT SUM(refund) FROM cust_refund
- WHERE cust_refund.custnum = cust_main.custnum ), 0)
+ ( SELECT COALESCE( SUM(charged), 0 ) FROM cust_bill
+ WHERE cust_bill.custnum = cust_main.custnum )
+ - ( SELECT COALESCE( SUM(paid), 0 ) FROM cust_pay
+ WHERE cust_pay.custnum = cust_main.custnum )
+ - ( SELECT COALESCE( SUM(amount), 0 ) FROM cust_credit
+ WHERE cust_credit.custnum = cust_main.custnum )
+ + ( SELECT COALESCE( SUM(refund), 0 ) FROM cust_refund
+ WHERE cust_refund.custnum = cust_main.custnum )
"; }
-=item balance_date_sql TIME
+=item balance_date_sql START_TIME [ END_TIME [ OPTION => VALUE ... ] ]
Returns an SQL fragment to retreive the balance for this customer, only
-considering invoices with date earlier than TIME. (total_owed_date minus total_credited minus
-total_unapplied_payments). TIME is specified as an SQL fragment or a numeric
-UNIX timestamp; see L<perlfunc/"time">). Also see L<Time::Local> and
-L<Date::Parse> for conversion functions.
+considering invoices with date earlier than START_TIME, and optionally not
+later than END_TIME (total_owed_date minus total_credited minus
+total_unapplied_payments).
+
+Times are specified as SQL fragments or numeric
+UNIX timestamps; see L<perlfunc/"time">). Also see L<Time::Local> and
+L<Date::Parse> for conversion functions. The empty string can be passed
+to disable that time constraint completely.
+
+Available options are:
+
+=over 4
+
+=item unapplied_date - set to true to disregard unapplied credits, payments and refunds outside the specified time period - by default the time period restriction only applies to invoices (useful for reporting, probably a bad idea for event triggering)
+
+=item total - set to true to remove all customer comparison clauses, for totals
+
+=item where - WHERE clause hashref (elements "AND"ed together) (typically used with the total option)
+
+=item join - JOIN clause (typically used with the total option)
+
+=item
+
+=back
=cut
sub balance_date_sql {
- my( $class, $time ) = @_;
+ my( $class, $start, $end, %opt ) = @_;
- my $owed_sql = FS::cust_bill->owed_sql;
- my $unapp_refund_sql = FS::cust_refund->unapplied_sql;
- #my $unapp_credit_sql = FS::cust_credit->unapplied_sql;
- my $unapp_credit_sql = FS::cust_credit->credited_sql;
- my $unapp_pay_sql = FS::cust_pay->unapplied_sql;
+ my $owed = FS::cust_bill->owed_sql;
+ my $unapp_refund = FS::cust_refund->unapplied_sql;
+ my $unapp_credit = FS::cust_credit->unapplied_sql;
+ my $unapp_pay = FS::cust_pay->unapplied_sql;
- "
- COALESCE( ( SELECT SUM($owed_sql) FROM cust_bill
- WHERE cust_bill.custnum = cust_main.custnum
- AND cust_bill._date <= $time )
- ,0
- )
- + COALESCE( ( SELECT SUM($unapp_refund_sql) FROM cust_refund
- WHERE cust_refund.custnum = cust_main.custnum )
- ,0
- )
- - COALESCE( ( SELECT SUM($unapp_credit_sql) FROM cust_credit
- WHERE cust_credit.custnum = cust_main.custnum )
- ,0
- )
- - COALESCE( ( SELECT SUM($unapp_pay_sql) FROM cust_pay
- WHERE cust_pay.custnum = cust_main.custnum )
- ,0
- )
+ my $j = $opt{'join'} || '';
+ my $owed_wh = $class->_money_table_where( 'cust_bill', $start,$end,%opt );
+ my $refund_wh = $class->_money_table_where( 'cust_refund', $start,$end,%opt );
+ my $credit_wh = $class->_money_table_where( 'cust_credit', $start,$end,%opt );
+ my $pay_wh = $class->_money_table_where( 'cust_pay', $start,$end,%opt );
+
+ " ( SELECT COALESCE(SUM($owed), 0) FROM cust_bill $j $owed_wh )
+ + ( SELECT COALESCE(SUM($unapp_refund), 0) FROM cust_refund $j $refund_wh )
+ - ( SELECT COALESCE(SUM($unapp_credit), 0) FROM cust_credit $j $credit_wh )
+ - ( SELECT COALESCE(SUM($unapp_pay), 0) FROM cust_pay $j $pay_wh )
";
}
+=item _money_table_where TABLE START_TIME [ END_TIME [ OPTION => VALUE ... ] ]
+
+Helper method for balance_date_sql; name (and usage) subject to change
+(suggestions welcome).
+
+Returns a WHERE clause for the specified monetary TABLE (cust_bill,
+cust_refund, cust_credit or cust_pay).
+
+If TABLE is "cust_bill" or the unapplied_date option is true, only
+considers records with date earlier than START_TIME, and optionally not
+later than END_TIME .
+
+=cut
+
+sub _money_table_where {
+ my( $class, $table, $start, $end, %opt ) = @_;
+
+ my @where = ();
+ push @where, "cust_main.custnum = $table.custnum" unless $opt{'total'};
+ if ( $table eq 'cust_bill' || $opt{'unapplied_date'} ) {
+ push @where, "$table._date <= $start" if length($start);
+ push @where, "$table._date > $end" if length($end);
+ }
+ push @where, @{$opt{'where'}} if $opt{'where'};
+ my $where = scalar(@where) ? 'WHERE '. join(' AND ', @where ) : '';
+
+ $where;
+
+}
+
=item fuzzy_search FUZZY_HASHREF [ HASHREF, SELECT, EXTRA_SQL, CACHE_OBJ ]
Performs a fuzzy (approximate) search and returns the matching FS::cust_main
)
),
sprintf( $money_char.'%.2f',
- $row->{'owed_0_30'} ),
+ $row->{'balance_0_30'} ),
sprintf( $money_char.'%.2f',
- $row->{'owed_30_60'} ),
+ $row->{'balance_30_60'} ),
sprintf( $money_char.'%.2f',
- $row->{'owed_60_90'} ),
+ $row->{'balance_60_90'} ),
sprintf( $money_char.'%.2f',
- $row->{'owed_90_0'} ),
+ $row->{'balance_90_0'} ),
sprintf( '<b>'. $money_char.'%.2f'. '</b>',
- $row->{'owed_0_0'} ),
+ $row->{'balance_0_0'} ),
],
'fields' => [
\&FS::UI::Web::cust_fields,
- sub { sprintf( $money_char.'%.2f',
- shift->get('owed_0_30') ) },
- sub { sprintf( $money_char.'%.2f',
- shift->get('owed_30_60') ) },
- sub { sprintf( $money_char.'%.2f',
- shift->get('owed_60_90') ) },
- sub { sprintf( $money_char.'%.2f',
- shift->get('owed_90_0') ) },
- sub { sprintf( $money_char.'%.2f',
- shift->get('owed_0_0') ) },
+ format_balance('0_30'),
+ format_balance('30_60'),
+ format_balance('60_90'),
+ format_balance('90_0'),
+ format_balance('0_0'),
],
'links' => [
( map { $_ ne 'Cust. Status' ? $clink : '' }
[ 0, 0 ],
);
-my $owed_cols = join(',', map owed( @$_, 'cust'=>1 ), @ranges );
+my $owed_cols = join(',', map balance( @$_ ), @ranges );
my $select_count_pkgs = FS::cust_main->select_count_pkgs_sql;
$days = $1;
}
- push @where, owed($days, 0, 'cust'=>1, 'noas'=>1). " > 0";
+ push @where, balance($days, 0, 'no_as'=>1). " > 0";
}
'table' => 'cust_main',
'hashref' => {},
'select' => "*, $owed_cols, $packages_cols",
- 'extra_sql' => "$where order by coalesce(lower(company), ''), lower(last)",
+ 'extra_sql' => $where,
+ 'order_by' => "order by coalesce(lower(company), ''), lower(last)",
};
my $join = 'LEFT JOIN cust_main USING ( custnum )';
-my $total_sql = "select ".
- join(',', map owed( @$_, join=>$join, where=>\@where ), @ranges);
+my $total_sql = "SELECT ".
+ join(',', map balance( @$_, total=>1, join=>$join, where=>\@where ), @ranges);
my $total_sth = dbh->prepare($total_sql) or die dbh->errstr;
$total_sth->execute or die "error executing $total_sql: ". $total_sth->errstr;
my $row = $total_sth->fetchrow_hashref();
-my $conf = new FS::Conf;
-my $money_char = $conf->config('money_char') || '$';
-
my $clink = [ "${p}view/cust_main.cgi?", 'custnum' ];
</%init>
<%once>
-sub owed {
- my($start, $end, %opt) = @_;
-
- my @where = ();
-
- #handle start and end ranges
-
- my $str2time = str2time_sql;
-
- #24h * 60m * 60s
- push @where, "cust_bill._date <= $str2time now() ) - ". ($start * 86400)
- if $start;
-
- push @where, "cust_bill._date > $str2time now() ) - ". ($end * 86400)
- if $end;
-
- #handle 'cust' option
- push @where, "cust_main.custnum = cust_bill.custnum"
- if $opt{'cust'};
+my $conf = new FS::Conf;
- #handle 'join' option
- my $join = $opt{'join'} || '';
+my $money_char = $conf->config('money_char') || '$';
- #handle 'where' option
- push @where, @{ $opt{'where'} } if $opt{'where'};
+#Example:
+#
+# my $balance = balance(
+# $start, $end,
+# 'no_as' => 1, #set to true when using in a WHERE clause (supress AS clause)
+# #or 0 / omit when using in a SELECT clause as a column
+# # ("AS balance_$start_$end")
+# #options for totals
+# 'total' => 1, #set to true to remove all customer comparison clauses
+# 'join' => $join, #JOIN clause
+# 'where' => \@where, #WHERE clause hashref (elements "AND"ed together)
+# )
+
+sub balance {
+ my($start, $end, %opt) = @_;
- my $where = scalar(@where) ? 'WHERE '.join(' AND ', @where) : '';
+ my $as = $opt{'no_as'} ? '' : " AS balance_${start}_$end";
- my $as = $opt{'noas'} ? '' : "as owed_${start}_$end";
+ #handle start and end ranges (86400 = 24h * 60m * 60s)
+ my $str2time = str2time_sql;
+ $start = $start ? "( $str2time now() ) - ".($start * 86400). ' )' : '';
+ $end = $end ? "( $str2time now() ) - ".($end * 86400). ' )' : '';
- my $charged = <<END;
-sum( charged
- - coalesce(
- ( select sum(amount) from cust_bill_pay
- where cust_bill.invnum = cust_bill_pay.invnum )
- ,0
- )
- - coalesce(
- ( select sum(amount) from cust_credit_bill
- where cust_bill.invnum = cust_credit_bill.invnum )
- ,0
- )
+ $opt{'unapplied_date'} = 1;
- )
-END
+ FS::cust_main->balance_date_sql( $start, $end, %opt ). $as;
- "coalesce( ( select $charged from cust_bill $join $where ) ,0 ) $as";
+}
+sub format_balance { #closures help alot
+ my $range = shift;
+ sub { sprintf( $money_char.'%.2f', shift->get("balance_$range") ) };
}
</%once>
projects / freeside.git / commitdiff