From ae7a7aedd844e581638d435e432e08a76f8097d1 Mon Sep 17 00:00:00 2001 From: ivan Date: Sun, 15 May 2005 13:01:00 +0000 Subject: [PATCH] move receivables report to search template --- FS/FS/cust_main.pm | 9 ++ httemplate/search/elements/search.html | 42 +++++++- httemplate/search/report_receivables.cgi | 171 +++++++++++++++++++------------ 3 files changed, 155 insertions(+), 67 deletions(-) diff --git a/FS/FS/cust_main.pm b/FS/FS/cust_main.pm index c03480cde..1edd319cb 100644 --- a/FS/FS/cust_main.pm +++ b/FS/FS/cust_main.pm @@ -3066,9 +3066,15 @@ Returns an SQL expression identifying active cust_main records. =cut +my $recurring_sql = " + '0' != ( select freq from part_pkg + where cust_pkg.pkgpart = part_pkg.pkgpart ) +"; + sub active_sql { " 0 < ( SELECT COUNT(*) FROM cust_pkg WHERE cust_pkg.custnum = cust_main.custnum + AND $recurring_sql AND ( cust_pkg.cancel IS NULL OR cust_pkg.cancel = 0 ) AND ( cust_pkg.susp IS NULL OR cust_pkg.susp = 0 ) ) @@ -3085,10 +3091,12 @@ sub suspended_sql { susp_sql(@_); } sub susp_sql { " 0 < ( SELECT COUNT(*) FROM cust_pkg WHERE cust_pkg.custnum = cust_main.custnum + AND $recurring_sql AND ( cust_pkg.cancel IS NULL OR cust_pkg.cancel = 0 ) ) AND 0 = ( SELECT COUNT(*) FROM cust_pkg WHERE cust_pkg.custnum = cust_main.custnum + AND $recurring_sql AND ( cust_pkg.susp IS NULL OR cust_pkg.susp = 0 ) AND ( cust_pkg.cancel IS NULL OR cust_pkg.cancel = 0 ) ) @@ -3108,6 +3116,7 @@ sub cancel_sql { " ) AND 0 = ( SELECT COUNT(*) FROM cust_pkg WHERE cust_pkg.custnum = cust_main.custnum + AND $recurring_sql AND ( cust_pkg.cancel IS NULL OR cust_pkg.cancel = 0 ) ) "; } diff --git a/httemplate/search/elements/search.html b/httemplate/search/elements/search.html index 92889c5f7..529f48f20 100644 --- a/httemplate/search/elements/search.html +++ b/httemplate/search/elements/search.html @@ -245,11 +245,18 @@ %> <% if ( $opt{'fields'} ) { - my $links = $opt{'links'} ? [ @{$opt{'links'}} ] : ''; + + my $links = $opt{'links'} ? [ @{$opt{'links'}} ] : ''; my $aligns = $opt{'align'} ? [ @{$opt{'align'}} ] : ''; + my $colors = $opt{'color'} ? [ @{$opt{'color'}} ] : []; + my $sizes = $opt{'size'} ? [ @{$opt{'size'}} ] : []; + my $styles = $opt{'style'} ? [ @{$opt{'style'}} ] : []; + foreach my $field ( @{$opt{'fields'}} ) { + my $align = $aligns ? shift @$aligns : ''; $align = " ALIGN=$align" if $align; + my $a = ''; if ( $links ) { my $link = shift @$links; @@ -264,11 +271,32 @@ $a = qq(); } } + + my $font = ''; + my $color = shift @$colors; + $color = &{$color}($row) if ref($color) eq 'CODE'; + my $size = shift @$sizes; + $size = &{$size}($row) if ref($size) eq 'CODE'; + if ( $color || $size ) { + $font = ''; + } + + my($s, $es) = ( '', '' ); + my $style = shift @$styles; + $style = &{$style}($row) if ref($style) eq 'CODE'; + if ( $style ) { + $s = join( '', map "<$_>", split('', $style) ); + $es = join( '', map "", split('', $style) ); + } + %> <% if ( ref($field) eq 'CODE' ) { %> - ><%= $a %><%= &{$field}($row) %><%= $a ? '' : '' %> + ><%= $font %><%= $a %><%= $s %><%= &{$field}($row) %><%= $es %><%= $a ? '' : '' %><%= $font ? '' : '' %> <% } else { %> - ><%= $a %><%= $row->$field() %><%= $a ? '' : '' %> + ><%= $font %><%= $a %><%= $s %><%= $row->$field() %><%= $es %><%= $a ? '' : '' %><%= $font ? '' : '' %> <% } %> <% } %> <% } else { %> @@ -278,6 +306,14 @@ <% } %> <% } %> + + <% if ( $opt{'footer'} ) { %> + + <% foreach my $footer ( @{ $opt{'footer'} } ) { %> + <%= $footer %> + <% } %> + + <% } %> <%= $pager %> diff --git a/httemplate/search/report_receivables.cgi b/httemplate/search/report_receivables.cgi index 0e95ad73c..6a2dff3e1 100755 --- a/httemplate/search/report_receivables.cgi +++ b/httemplate/search/report_receivables.cgi @@ -1,4 +1,3 @@ - <% my $charged = <prepare($sql) or die dbh->errstr; - $sth->execute or die $sth->errstr; + my $sql_query = { + 'table' => 'cust_main', + 'hashref' => {}, + 'select' => "*, $owed_cols, $packages_cols", + 'extra_sql' => "$where order by coalesce(lower(company), ''), lower(last)", + }; + my $total_sql = "select $owed_cols"; my $total_sth = dbh->prepare($total_sql) or die dbh->errstr; $total_sth->execute or die $total_sth->errstr; - -%> -<%= header('Accounts Receivable Aging Summary', menubar( 'Main Menu'=>$p, ) ) %> -<%= table() %> - - Customer - Status - 0-30 - 30-60 - 60-90 - 90+ - Total - -<% while ( my $row = $sth->fetchrow_hashref() ) { - my $status = 'Cancelled'; - my $statuscol = 'FF0000'; - if ( $row->{uncancelled_pkgs} ) { - $status = 'Suspended'; - $statuscol = 'FF9900'; - if ( $row->{active_pkgs} ) { - $status = 'Active'; - $statuscol = '00CC00'; - } - } + my $row = $total_sth->fetchrow_hashref(); + + my $conf = new FS::Conf; + my $money_char = $conf->config('money_char') || '$'; + +%><%= include( 'elements/search.html', + 'title' => 'Accounts Receivable Aging Summary', + 'name' => 'customers', + 'query' => $sql_query, + 'count_query' => $count_sql, + 'header' => [ + '#', + 'Customer', + 'Status (me)', + 'Status (cust_main)', + '0-30', + '30-60', + '60-90', + '90+', + 'Total', + ], + 'footer' => [ + '', + 'Total', + '', + '', + sprintf( $money_char.'%.2f', + $row->{'owed_0_30'} ), + sprintf( $money_char.'%.2f', + $row->{'owed_30_60'} ), + sprintf( $money_char.'%.2f', + $row->{'owed_60_90'} ), + sprintf( $money_char.'%.2f', + $row->{'owed_90_pl'} ), + sprintf( ''. $money_char.'%.2f'. '', + $row->{'owed_total'} ), + ], + 'align' => 'rlccrrrrr', + 'size' => [ '', '', '-1', '-1', '', '', '', '', '', ], + 'style' => [ '', '', 'b', 'b', '', '', '', '', 'b', ], + 'color' => [ + '', + '', + sub { + my $row = shift; + my $status = 'Cancelled'; + my $statuscol = 'FF0000'; + if ( $row->uncancelled_pkgs ) { + $status = 'Suspended'; + $statuscol = 'FF9900'; + if ( $row->active_pkgs ) { + $status = 'Active'; + $statuscol = '00CC00'; + } + } + $statuscol; + }, + sub { shift->statuscolor; }, + '', + '', + '', + '', + '', + ], + 'fields' => [ + 'custnum', + 'name', + sub { + my $row = shift; + my $status = 'Cancelled'; + my $statuscol = 'FF0000'; + if ( $row->uncancelled_pkgs ) { + $status = 'Suspended'; + $statuscol = 'FF9900'; + if ( $row->active_pkgs ) { + $status = 'Active'; + $statuscol = '00CC00'; + } + } + $status; + }, + sub { ucfirst(shift->status) }, + 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_pl') ) }, + sub { sprintf( $money_char.'%.2f', + shift->get('owed_total') ) }, + ], + ) %> - - <%= $row->{'custnum'} %>: - <%= $row->{'company'} ? $row->{'company'}. ' (' : '' %><%= $row->{'last'}. ', '. $row->{'first'} %><%= $row->{'company'} ? ')' : '' %> - - <%= $status %> - $<%= sprintf("%.2f", $row->{'owed_0_30'} ) %> - $<%= sprintf("%.2f", $row->{'owed_30_60'} ) %> - $<%= sprintf("%.2f", $row->{'owed_60_90'} ) %> - $<%= sprintf("%.2f", $row->{'owed_90_plus'} ) %> - $<%= sprintf("%.2f", $row->{'owed_total'} ) %> - -<% } %> -<% my $row = $total_sth->fetchrow_hashref(); %> - -   - - - Total - $<%= sprintf("%.2f", $row->{'owed_0_30'} ) %> - $<%= sprintf("%.2f", $row->{'owed_30_60'} ) %> - $<%= sprintf("%.2f", $row->{'owed_60_90'} ) %> - $<%= sprintf("%.2f", $row->{'owed_90_plus'} ) %> - $<%= sprintf("%.2f", $row->{'owed_total'} ) %> - - - - + -- 2.11.0