);
}
+=item discounted: The sum of discounts on invoices in the period.
+
+=cut
+
+sub discounted {
+ my( $self, $speriod, $eperiod, $agentnum, %opt) = @_;
+ $self->scalar_sql('SELECT SUM(cust_bill_pkg_discount.amount)
+ FROM cust_bill_pkg_discount
+ JOIN cust_bill_pkg USING ( billpkgnum )
+ JOIN cust_bill USING ( invnum )
+ JOIN cust_main USING ( custnum )
+ WHERE '. $self->in_time_period_and_agent( $speriod,
+ $eperiod,
+ $agentnum,
+ 'cust_bill._date'
+ ).
+ $self->for_opts(%opt)
+ );
+}
+
+=item gross: invoiced + discounted
+
+=cut
+
+sub gross {
+ my( $self, $speriod, $eperiod, $agentnum, %opt) = @_;
+ $self->invoiced( $speriod, $eperiod, $agentnum, %opt)
+ + $self->discounted( $speriod, $eperiod, $agentnum, %opt);
+}
+
#XXX docs
#these should be auto-generated or $AUTOLOADed or something
use FS::Record qw( qsearchs dbh );
use FS::cust_main;
use FS::access_user;
+use FS::Conf;
=item search HASHREF
(Class method)
-Returns a qsearch hash expression to search for parameters specified in HASHREF.
-In addition to all parameters accepted by search_sql_where, the following
-additional parameters valid:
+Returns a qsearch hash expression to search for parameters specified in
+HASHREF. In addition to all parameters accepted by search_sql_where, the
+following additional parameters valid:
=over 4
my $join_cust_main = FS::UI::Web::join_cust_main('cust_bill');
+ # get discounted, credited, and paid amounts here, for use in report
+ #
+ # Testing shows that this is by far the most efficient way to do the
+ # joins. In particular it's almost 100x faster to join to an aggregate
+ # query than to put the subquery in a select expression. It also makes
+ # it more convenient to do arithmetic between columns, use them as sort
+ # keys, etc.
+ #
+ # Each ends with a RIGHT JOIN cust_bill so that it includes all invnums,
+ # even if they have no discounts/credits/payments; the total amount is then
+ # coalesced to zero.
+ my $join = "$join_cust_main
+ JOIN (
+ SELECT COALESCE(SUM(cust_bill_pkg_discount.amount), 0) AS discounted,
+ invnum
+ FROM cust_bill_pkg_discount
+ JOIN cust_bill_pkg USING (billpkgnum)
+ RIGHT JOIN cust_bill USING (invnum)
+ GROUP BY invnum
+ ) AS _discount USING (invnum)
+ JOIN (
+ SELECT COALESCE(SUM(cust_credit_bill.amount), 0) AS credited, invnum
+ FROM cust_credit_bill
+ RIGHT JOIN cust_bill USING (invnum)
+ GROUP BY invnum
+ ) AS _credit USING (invnum)
+ JOIN (
+ SELECT COALESCE(SUM(cust_bill_pay.amount), 0) AS paid, invnum
+ FROM cust_bill_pay
+ RIGHT JOIN cust_bill USING (invnum)
+ GROUP BY invnum
+ ) AS _pay USING (invnum)
+ ";
+
unless ( $count_query ) {
+
+ my $money = (FS::Conf->new->config('money_char') || '$') . '%.2f';
+
$count_query = 'SELECT COUNT(*), '. join(', ',
map "SUM($_)",
- ( 'charged',
- FS::cust_bill->net_sql,
- FS::cust_bill->owed_sql,
+ ( 'charged + discounted',
+ 'discounted',
+ 'credited',
+ 'charged - credited',
+ 'charged - credited - paid',
)
);
- $count_addl = [ '$%.2f invoiced (gross)',
- '$%.2f invoiced (net)',
- '$%.2f outstanding balance',
+ $count_addl = [ "$money sales (gross)",
+ "− $money discounted",
+ "− $money credited",
+ "= $money sales (net)",
+ "$money outstanding balance",
];
}
- $count_query .= " FROM cust_bill $join_cust_main $extra_sql";
+ $count_query .= " FROM cust_bill $join $extra_sql";
#$sql_query =
+{
'table' => 'cust_bill',
- 'addl_from' => $join_cust_main,
+ 'addl_from' => $join,
'hashref' => {},
'select' => join(', ',
'cust_bill.*',
#( map "cust_main.$_", qw(custnum last first company) ),
'cust_main.custnum as cust_main_custnum',
FS::UI::Web::cust_sql_fields(),
- #$class->owed_sql. ' AS owed',
- #$class->net_sql. ' AS net',
- FS::cust_bill->owed_sql. ' AS owed',
- FS::cust_bill->net_sql. ' AS net',
+ '(charged + discounted) as gross',
+ 'discounted',
+ 'credited',
+ '(charged - credited) as net',
+ '(charged - credited - paid) as owed',
),
'extra_sql' => $extra_sql,
'order_by' => 'ORDER BY '. ( $params->{'order_by'} || 'cust_bill._date' ),
@classnum = grep /^(\d*)$/, @classnum;
if ( @classnum ) {
- push @search, '( '. join(' OR ', map {
- $_ ? "cust_main.classnum = $_"
- : "cust_main.classnum IS NULL"
- }
- @classnum
- ).
- ' )';
+ push @search, 'COALESCE(cust_main.classnum, 0) IN ('.join(',', @classnum).')';
}
}
push @search, "cust_bill.invnum <= $1";
}
+ # these are from parse_lt_gt, and should already be sanitized
#charged
if ( $param->{charged} ) {
my @charged = ref($param->{charged})
@charged;
}
- my $owed_sql = FS::cust_bill->owed_sql;
+ #my $owed_sql = FS::cust_bill->owed_sql;
+ my $owed_sql = '(cust_bill.charged - credited - paid)';
+ my $net_sql = '(cust_bill.charged - credited)';
#owed
if ( $param->{owed} ) {
my @owed = ref($param->{owed})
? @{ $param->{owed} }
: ($param->{owed});
- push @search, map { s/^owed/$owed_sql/; $_; }
- @owed;
+ push @search, map { s/^owed/$owed_sql/ } @owed;
}
#open/net flags
push @search, "0 != $owed_sql"
if $param->{'open'};
- push @search, '0 != '. FS::cust_bill->net_sql
+ push @search, "0 != $net_sql"
if $param->{'net'};
#days
}
my $referralname = $part_referral ? $part_referral->referral.' ' : '';
-
-my @items = qw( invoiced netsales
+# need to clean this up. the false symmetry of "gross" and "net" everything
+# makes it aesthetically hard to make this report more useful.
+my @items = qw( gross netsales
+ discounted
credits netcredits
payments receipts
refunds netrefunds
}
my %label = (
- 'invoiced' => 'Gross Sales',
+ 'gross' => 'Gross Sales',
'netsales' => 'Net Sales',
+ 'discounted' => 'Discounts',
'credits' => 'Gross Credits',
'netcredits' => 'Net Credits',
'payments' => 'Gross Receipts',
);
my %graph_suffix = (
- 'invoiced' => ' (invoiced)',
+ 'gross' => ' (invoiced + discounts)',
'netsales' => ' (invoiced - applied credits)',
+ 'discounted' => ' (discounts)',
'credits' => ' (credited)',
'netcredits' => ' (applied credits)',
'payments' => ' (payments)',
foreach keys %graph_label;
my %color = (
- 'invoiced' => '9999ff', #light blue
+ 'gross' => '9999ff', #light blue
'netsales' => '0000cc', #blue
'credits' => 'ff9999', #light red
'netcredits' => 'cc0000', #red
'netrefunds' => 'ff9900', #orange
'cashflow' => '99cc33', #light olive
'netcashflow' => '339900', #olive
+ 'discounted' => 'cc33cc', #purple-ish?
);
$color{$_.'_12mo'} = $color{$_}
foreach keys %color;
$ar .= ";cust_classnum=$_" foreach @classnums;
my %link = (
- 'invoiced' => "${p}search/cust_bill.html?$ar;",
+ 'gross' => "${p}search/cust_bill.html?$ar;",
'netsales' => "${p}search/cust_bill.html?$ar;net=1;",
'credits' => "${p}search/cust_credit.html?$ar;",
'netcredits' => "${p}search/cust_credit_bill.html?$ar;",
'receipts' => "${p}search/cust_bill_pay.html?$ar;",
'refunds' => "${p}search/cust_refund.html?magic=_date;$ar;",
'netrefunds' => "${p}search/cust_credit_refund.html?$ar;",
+ 'discounted' => "${p}search/cust_bill_pkg_discount.html?$ar;",
);
# XXX link 12mo?
<& elements/search.html,
- 'title' => emt('Invoice Search Results'),
- 'html_init' => $html_init,
- 'menubar' => $menubar,
- 'name' => 'invoices',
- 'query' => $sql_query,
- 'count_query' => $count_query,
- 'count_addl' => $count_addl,
- 'redirect' => $link,
- 'header' => [ emt('Invoice #'),
- emt('Balance'),
- emt('Net Amount'),
- emt('Gross Amount'),
- emt('Date'),
- FS::UI::Web::cust_header(),
- ],
- 'fields' => [
- 'display_invnum',
- sub { sprintf($money_char.'%.2f', shift->get('owed') ) },
- sub { sprintf($money_char.'%.2f', shift->get('net') ) },
- sub { sprintf($money_char.'%.2f', shift->charged ) },
- sub { time2str('%b %d %Y', shift->_date ) },
- \&FS::UI::Web::cust_fields,
- ],
- 'sort_fields' => [
- 'COALESCE( agent_invid, invnum )',
- FS::cust_bill->owed_sql,
- FS::cust_bill->net_sql,
- 'charged',
- '_date',
- ],
- 'align' => 'rrrrl'.FS::UI::Web::cust_aligns(),
- 'links' => [
- $link,
- $link,
- $link,
- $link,
- $link,
- ( map { $_ ne 'Cust. Status' ? $clink : '' }
- FS::UI::Web::cust_header()
- ),
- ],
- 'color' => [
- '',
- '',
- '',
- '',
- '',
- FS::UI::Web::cust_colors(),
- ],
- 'style' => [
- '',
- '',
- '',
- '',
- '',
- FS::UI::Web::cust_styles(),
- ],
+ 'title' => emt('Invoice Search Results'),
+ 'html_init' => $html_init,
+ 'menubar' => $menubar,
+ 'name' => 'invoices',
+ 'query' => $sql_query,
+ 'count_query' => $count_query,
+ 'count_addl' => $count_addl,
+ 'redirect' => $link,
+ 'header' => [ emt('Invoice #'),
+ emt('Gross Amount'),
+ emt('Discount'),
+ emt('Credits'),
+ emt('Net Amount'),
+ emt('Balance'),
+ emt('Date'),
+ FS::UI::Web::cust_header(),
+ ],
+ 'fields' => [
+ 'display_invnum',
+ 'gross',
+ 'discounted',
+ 'credited',
+ 'net',
+ 'owed',
+ sub { time2str('%b %d %Y', shift->_date ) },
+ \&FS::UI::Web::cust_fields,
+ ],
+ 'sort_fields' => [
+ 'COALESCE( agent_invid, invnum )',
+ 'gross',
+ 'discounted',
+ 'credited',
+ 'net',
+ 'owed',
+ '_date',
+ ],
+ 'format' => [
+ '',
+ $money_char.'%.2f',
+ $money_char.'%.2f',
+ $money_char.'%.2f',
+ $money_char.'%.2f',
+ $money_char.'%.2f',
+ '',
+ ],
+ 'align' => 'rrrrrrl'.FS::UI::Web::cust_aligns(),
+ 'links' => [
+ $link,
+ $link,
+ $link,
+ $link,
+ $link,
+ $link,
+ $link,
+ ( map { $_ ne 'Cust. Status' ? $clink : '' }
+ FS::UI::Web::cust_header()
+ ),
+ ],
+ 'color' => [
+ '',
+ '',
+ '',
+ '',
+ '',
+ '',
+ '',
+ FS::UI::Web::cust_colors(),
+ ],
+ 'style' => [
+ '',
+ '',
+ '',
+ '',
+ '',
+ '',
+ '',
+ FS::UI::Web::cust_styles(),
+ ],
&>
<%init>
% my $sizes = $opt{'size'} ? [ @{$opt{'size'}} ] : [];
% my $styles = $opt{'style'} ? [ @{$opt{'style'}} ] : [];
% my $cstyles = $opt{'cell_style'} ? [ @{$opt{'cell_style'}} ] : [];
+% my $formats = $opt{'format'} ? [ @{$opt{'format'}} ] : [];
%
% foreach my $field (
%
+% # if the value of the field is an arrayref, then construct a table in
+% # the cell.
+% # if it's a (non-empty) scalar, and a format has been specified, then
+% # format the scalar with that.
+% # otherwise, just output the value.
+% # XXX we should also do date formats like this
% map {
% if ( ref($_) eq 'ARRAY' ) {
%
% '</table>';
%
% } else {
+% if ( length($_) > 0 and my $format = shift @$formats ) {
+% $_ = sprintf($format, $_);
+% }
% $_;
% }
% }
%
+% # get the value of the field spec:
+% # - if the spec is a coderef, evaluate the coderef
+% # - if the spec is a string, call that string as a method
+% # - if the spec is an integer, get the field in that position
% map {
% if ( ref($_) eq 'CODE' ) {
% &{$_}($row);
label => mt('Customer Class'),
field => 'cust_classnum',
multiple => 1,
- 'pre_options' => [ '' => emt('(none)') ],
- 'all_selected' => 1,
+ 'pre_options' => [ 0 => emt('(none)') ],
&>
% if ( $cust_main ) {
projects / freeside.git / commitdiff