# only for figuring next bill date, nothing else, so, reset $sdate again
# here
$sdate = $cust_pkg->bill || $cust_pkg->setup || $time;
- $cust_pkg->last_bill($sdate)
- if $cust_pkg->dbdef_table->column('last_bill');
+ $cust_pkg->last_bill($sdate);
if ( $part_pkg->freq =~ /^\d+$/ ) {
$mon += $part_pkg->freq;
'gatewaynum' => ( $payment_gateway ? $payment_gateway->gatewaynum : '' ),
};
$cust_pay_pending->payunique( $options{payunique} )
- if length($options{payunique});
+ if defined($options{payunique}) && length($options{payunique});
my $cpp_new_err = $cust_pay_pending->insert; #mutex lost when this is inserted
return $cpp_new_err if $cpp_new_err;
'paydate' => $paydate,
} );
#doesn't hurt to know, even though the dup check is in cust_pay_pending now
- $cust_pay->payunique( $options{payunique} ) if length($options{payunique});
+ $cust_pay->payunique( $options{payunique} )
+ if defined($options{payunique}) && length($options{payunique});
my $oldAutoCommit = $FS::UID::AutoCommit;
local $FS::UID::AutoCommit = 0;
sub charge {
my $self = shift;
- my ( $amount, $pkg, $comment, $taxclass, $additional );
+ my ( $amount, $pkg, $comment, $taxclass, $additional, $classnum );
if ( ref( $_[0] ) ) {
$amount = $_[0]->{amount};
$pkg = exists($_[0]->{pkg}) ? $_[0]->{pkg} : 'One-time charge';
$comment = exists($_[0]->{comment}) ? $_[0]->{comment}
: '$'. sprintf("%.2f",$amount);
$taxclass = exists($_[0]->{taxclass}) ? $_[0]->{taxclass} : '';
+ $classnum = exists($_[0]->{classnum}) ? $_[0]->{classnum} : '';
$additional = $_[0]->{additional};
}else{
$amount = shift;
'plan' => 'flat',
'freq' => 0,
'disabled' => 'Y',
+ 'classnum' => $classnum ? $classnum : '',
'taxclass' => $taxclass,
} );
=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 defined($start) && length($start);
+ push @where, "$table._date > $end" if defined($end) && 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
}
- } elsif ( $search =~ /^\s*(\d+)\s*$/ ) { # customer # search
- # (also try agent_custid)
- # (regex needs tweaking if your
- # legacy cust numbers have letters)
+ # custnum search (also try agent_custid), with some tweaking options if your
+ # legacy cust "numbers" have letters
+ } elsif ( $search =~ /^\s*(\d+)\s*$/
+ || ( $conf->config('cust_main-agent_custid-format') eq 'ww?d+'
+ && $search =~ /^\s*(\w\w?\d+)\s*$/
+ )
+ )
+ {
push @cust_main, qsearch( {
'table' => 'cust_main',