diff options
author | Ivan Kohler <ivan@freeside.biz> | 2014-05-28 22:58:04 -0700 |
---|---|---|
committer | Ivan Kohler <ivan@freeside.biz> | 2014-05-28 22:58:04 -0700 |
commit | 5700d83bcd475134cff8afb28307174c09116924 (patch) | |
tree | 1980bc74fb5b35ce0fe0cdd5fc51d39b9ffda72f /FS/FS/vend_main.pm | |
parent | ef3c3ca13b2b93df158e01d4fc0713a3e8191a8c (diff) |
payables summary by vendor
Diffstat (limited to 'FS/FS/vend_main.pm')
-rw-r--r-- | FS/FS/vend_main.pm | 60 |
1 files changed, 60 insertions, 0 deletions
diff --git a/FS/FS/vend_main.pm b/FS/FS/vend_main.pm index 2e5e1505a..22ac0bf70 100644 --- a/FS/FS/vend_main.pm +++ b/FS/FS/vend_main.pm @@ -101,6 +101,66 @@ sub check { =item vend_class +=item search + +=cut + +sub search { + my ($class, $param) = @_; + + my @where = (); + my $addl_from = ''; + + #_date + if ( $param->{_date} ) { + my($beginning, $ending) = @{$param->{_date}}; + + push @where, "vend_bill._date >= $beginning", + "vend_bill._date < $ending"; + } + + #payment_date + if ( $param->{payment_date} ) { + my($beginning, $ending) = @{$param->{payment_date}}; + + push @where, "vend_pay._date >= $beginning", + "vend_pay._date < $ending"; + } + + if ( $param->{'classnum'} =~ /^(\d+)$/ ) { + push @where, "vend_main.classnum = $1"; + } + + my $extra_sql = scalar(@where) ? ' WHERE '. join(' AND ', @where) : ''; + + my $group_by = ' GROUP BY vend_main.vendnum '; + + my $addl_from_vend_bill = ' LEFT JOIN vend_bill_pay USING (vendbillnum) '. + ' LEFT JOIN vend_pay USING (vendpaynum) '; + + $addl_from .= " LEFT JOIN vend_bill USING ( vendnum ) $addl_from_vend_bill"; + + #simplistic, but how we are for now + + my $count_query = " + SELECT COUNT(*), + ( SELECT SUM(charged) from vend_bill $addl_from_vend_bill $extra_sql + ) AS sum_charged + FROM vend_main "; #XXX classnum, sum_charged > 0 + + +{ + 'table' => 'vend_main', + 'select' => 'vend_main.*, sum(vend_bill.charged) as sum_charged', + 'addl_from' => $addl_from, + 'hashref' => {}, + 'extra_sql' => "$extra_sql $group_by", + 'order_by' => 'ORDER BY sum_charged desc', + 'count_query' => $count_query, + #'extra_headers' => \@extra_headers, + #'extra_fields' => \@extra_fields, + }; +} + =back =head1 BUGS |