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 | |
parent | ef3c3ca13b2b93df158e01d4fc0713a3e8191a8c (diff) |
payables summary by vendor
-rw-r--r-- | FS/FS/vend_main.pm | 60 | ||||
-rw-r--r-- | httemplate/elements/menu.html | 3 | ||||
-rw-r--r-- | httemplate/search/report_vend_main.html | 48 | ||||
-rw-r--r-- | httemplate/search/vend_main.html | 40 |
4 files changed, 150 insertions, 1 deletions
diff --git a/FS/FS/vend_main.pm b/FS/FS/vend_main.pm index 2e5e150..22ac0bf 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 diff --git a/httemplate/elements/menu.html b/httemplate/elements/menu.html index 7df2448..fdd4962 100644 --- a/httemplate/elements/menu.html +++ b/httemplate/elements/menu.html @@ -377,7 +377,8 @@ if( $curuser->access_right('Financial reports') ) { } # else $report_financial contains nothing. tie my %report_payable, 'Tie::IxHash', - 'Payables' => [ $fsurl. 'search/report_vend_bill.html' ], + 'Payables summary' => [ $fsurl. 'search/report_vend_main.html', 'Payables summary by vendor' ], + 'Payables detail' => [ $fsurl. 'search/report_vend_bill.html' ], ; tie my %report_logs, 'Tie::IxHash'; diff --git a/httemplate/search/report_vend_main.html b/httemplate/search/report_vend_main.html new file mode 100644 index 0000000..c7fbb2b --- /dev/null +++ b/httemplate/search/report_vend_main.html @@ -0,0 +1,48 @@ +<& /elements/header.html, mt('Payables summary by vendor') &> + +<FORM ACTION="vend_main.html" METHOD="GET"> + + <TABLE BGCOLOR="#cccccc" CELLSPACING=0> + + <TR> + <TD ALIGN="right" VALIGN="center"><% mt('Date') |h %></TD> + <TD> + <TABLE> + <& /elements/tr-input-beginning_ending.html, + prefix => '_date', + layout => 'horiz', + &> + </TABLE> + </TD> + </TR> + + <TR> + <TD ALIGN="right" VALIGN="center"><% mt('Payment date') |h %></TD> + <TD> + <TABLE> + <& /elements/tr-input-beginning_ending.html, + prefix => 'payment_date', + layout => 'horiz', + &> + </TABLE> + </TD> + </TR> + + <& /elements/tr-select-vend_class.html, + &> + + </TABLE> + +<BR> +<INPUT TYPE="submit" VALUE="<% mt('Get Report') |h %>"> + +</FORM> + +<& /elements/footer.html &> +<%init> + +die "access denied" + unless $FS::CurrentUser::CurrentUser->access_right('Configuration'); + +</%init> + diff --git a/httemplate/search/vend_main.html b/httemplate/search/vend_main.html new file mode 100644 index 0000000..41917a5 --- /dev/null +++ b/httemplate/search/vend_main.html @@ -0,0 +1,40 @@ +<& elements/search.html, + 'title' => 'Payables summary by vendor', + 'name' => 'vendors', + 'query' => $query, + 'count_query' => $count_query, + 'count_addl' => [ '$%.2f total', ], + 'header' => [ + 'Vendor', + 'Class', + 'Amount', + '', + ], + 'fields' => [ + sub { shift->vendname }, + sub { shift->vend_class->classname }, + 'sum_charged', + ], + +&> +<%init> + +die "access denied" + unless $FS::CurrentUser::CurrentUser->access_right('Configuration'); + +my %search = (); + +# begin/end/beginning/ending +my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi, '_date'); +$search{'_date'} = [ $beginning, $ending ]; + +($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi, 'payment_date'); +$search{'payment_date'} = [ $beginning, $ending ]; + +$search{'classnum'} = $cgi->param('classnum'); + +my $query = FS::vend_main->search( \%search ); +my $count_query = delete( $query->{'count_query'} ); + +</%init> + |