move receivables report to search template

diff --git a/FS/FS/cust_main.pm b/FS/FS/cust_main.pm
index c03480c..1edd319 100644 (file)
--- 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 92889c5..529f48f 100644 (file)
--- 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 0e95ad7..6a2dff3 100755 (executable)
--- 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>&nbsp;</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>
+