diff options
author | ivan <ivan> | 2005-05-15 13:01:00 +0000 |
---|---|---|
committer | ivan <ivan> | 2005-05-15 13:01:00 +0000 |
commit | ae7a7aedd844e581638d435e432e08a76f8097d1 (patch) | |
tree | fcbf4da8a741ddfe9cbda13b1f5a97064de60d0f /httemplate/search | |
parent | ccf0fd3e5cbdb4b3d88b11b7ab290d9a280a4c5c (diff) |
move receivables report to search template
Diffstat (limited to 'httemplate/search')
-rw-r--r-- | httemplate/search/elements/search.html | 42 | ||||
-rwxr-xr-x | httemplate/search/report_receivables.cgi | 171 |
2 files changed, 146 insertions, 67 deletions
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 @@ %> <TR> <% 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(<A HREF="$a">); } } + + 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 = '<FONT '. + ( $color ? "COLOR=#$color " : '' ). + ( $size ? qq(SIZE="$size" ) : '' ). + '>'; + } + + 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' ) { %> - <TD CLASS="grid" BGCOLOR="<%= $bgcolor %>"<%= $align %>><%= $a %><%= &{$field}($row) %><%= $a ? '</A>' : '' %></TD> + <TD CLASS="grid" BGCOLOR="<%= $bgcolor %>"<%= $align %>><%= $font %><%= $a %><%= $s %><%= &{$field}($row) %><%= $es %><%= $a ? '</A>' : '' %><%= $font ? '</FONT>' : '' %></TD> <% } else { %> - <TD CLASS="grid" BGCOLOR="<%= $bgcolor %>"<%= $align %>><%= $a %><%= $row->$field() %><%= $a ? '</A>' : '' %></TD> + <TD CLASS="grid" BGCOLOR="<%= $bgcolor %>"<%= $align %>><%= $font %><%= $a %><%= $s %><%= $row->$field() %><%= $es %><%= $a ? '</A>' : '' %><%= $font ? '</FONT>' : '' %></TD> <% } %> <% } %> <% } else { %> @@ -278,6 +306,14 @@ <% } %> </TR> <% } %> + + <% if ( $opt{'footer'} ) { %> + <TR> + <% foreach my $footer ( @{ $opt{'footer'} } ) { %> + <TD CLASS="grid" BGCOLOR="#dddddd" STYLE="border-top: dashed 1px black;"><i><%= $footer %></i></TH> + <% } %> + </TR> + <% } %> </TABLE> <%= $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 @@ -<!-- mason kludge --> <% my $charged = <<END; @@ -50,7 +49,7 @@ END and cust_main.custnum = cust_bill.custnum ) ,0 - ) as owed_90_plus, + ) as owed_90_pl, coalesce( ( select $charged from cust_bill @@ -61,8 +60,8 @@ END END my $recurring = <<END; - 0 < ( select freq from part_pkg - where cust_pkg.pkgpart = part_pkg.pkgpart ) + '0' != ( select freq from part_pkg + where cust_pkg.pkgpart = part_pkg.pkgpart ) END my $packages_cols = <<END; @@ -82,9 +81,7 @@ END END - my $sql = <<END; - -select *, $owed_cols, $packages_cols from cust_main + my $where = <<END; where 0 < coalesce( ( select $charged from cust_bill @@ -92,67 +89,113 @@ where 0 < ) ,0 ) - -order by coalesce(lower(company), ''), lower(last) - END - my $total_sql = "select $owed_cols"; + my $count_sql = "select count(*) from cust_main $where"; - my $sth = dbh->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() %> - <TR> - <TH>Customer</TH> - <TH>Status</TH> - <TH>0-30</TH> - <TH>30-60</TH> - <TH>60-90</TH> - <TH>90+</TH> - <TH>Total</TH> - </TR> -<% 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( '<b>'. $money_char.'%.2f'. '</b>', + $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') ) }, + ], + ) %> - <TR> - <TD><A HREF="<%= $p %>view/cust_main.cgi?<%= $row->{'custnum'} %>"><%= $row->{'custnum'} %>: - <%= $row->{'company'} ? $row->{'company'}. ' (' : '' %><%= $row->{'last'}. ', '. $row->{'first'} %><%= $row->{'company'} ? ')' : '' %></A> - </TD> - <TD><B><FONT SIZE=-1 COLOR="#<%= $statuscol %>"><%= $status %></FONT></B></TD> - <TD ALIGN="right">$<%= sprintf("%.2f", $row->{'owed_0_30'} ) %></TD> - <TD ALIGN="right">$<%= sprintf("%.2f", $row->{'owed_30_60'} ) %></TD> - <TD ALIGN="right">$<%= sprintf("%.2f", $row->{'owed_60_90'} ) %></TD> - <TD ALIGN="right">$<%= sprintf("%.2f", $row->{'owed_90_plus'} ) %></TD> - <TD ALIGN="right"><B>$<%= sprintf("%.2f", $row->{'owed_total'} ) %></B></TD> - </TR> -<% } %> -<% my $row = $total_sth->fetchrow_hashref(); %> - <TR> - <TD COLSPAN=6> </TD> - </TR> - <TR> - <TD COLSPAN=2><I>Total</I></TD> - <TD ALIGN="right"><I>$<%= sprintf("%.2f", $row->{'owed_0_30'} ) %></TD> - <TD ALIGN="right"><I>$<%= sprintf("%.2f", $row->{'owed_30_60'} ) %></TD> - <TD ALIGN="right"><I>$<%= sprintf("%.2f", $row->{'owed_60_90'} ) %></TD> - <TD ALIGN="right"><I>$<%= sprintf("%.2f", $row->{'owed_90_plus'} ) %></TD> - <TD ALIGN="right"><I><B>$<%= sprintf("%.2f", $row->{'owed_total'} ) %></B></I></TD> - </TR> -</TABLE> -</BODY> -</HTML> + |