update flat_introrate plan to better fit current codebase RT#4912

[freeside.git] / httemplate / search / report_receivables.cgi

diff --git a/httemplate/search/report_receivables.cgi b/httemplate/search/report_receivables.cgi
index 06aea19..63adba0 100755 (executable)
--- a/httemplate/search/report_receivables.cgi
+++ b/httemplate/search/report_receivables.cgi
@@ -71,7 +71,8 @@
 <%init>
 
 die "access denied"
-  unless $FS::CurrentUser::CurrentUser->access_right('Financial reports');
+  unless $FS::CurrentUser::CurrentUser->access_right('Receivables report')
+      or $FS::CurrentUser::CurrentUser->access_right('Financial reports');
 
 my @ranges = (
   [  0, 30 ],
@@ -107,7 +108,7 @@ unless ( $cgi->param('all_customers') ) {
     $days = $1;
   }
 
-  push @where, balance($days, 0, 'no_as'=>1). " > 0";
+  push @where, balance($days, 0, 'no_as'=>1). ' > 0'; # != 0';
 
 }
 
@@ -116,6 +117,15 @@ if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) {
   push @where, "agentnum = $agentnum";
 }
 
+#status (false laziness w/cust_main::search_sql
+
+#prospect active inactive suspended cancelled
+if ( grep { $cgi->param('status') eq $_ } FS::cust_main->statuses() ) {
+  my $method = $cgi->param('status'). '_sql';
+  #push @where, $class->$method();
+  push @where, FS::cust_main->$method();
+}
+
 #here is the agent virtualization
 push @where, $FS::CurrentUser::CurrentUser->agentnums_sql;
 
@@ -127,15 +137,19 @@ my $count_sql = "select count(*) from cust_main $where";
 my $sql_query = {
   'table'     => 'cust_main',
   'hashref'   => {},
-  'select'    => "*, $owed_cols, $packages_cols",
+  'select'    => join(',',
+                   #'cust_main.*',
+                   'custnum',
+                   $owed_cols,
+                   $packages_cols,
+                   FS::UI::Web::cust_sql_fields(),
+                 ),
   'extra_sql' => $where,
   'order_by'  => "order by coalesce(lower(company), ''), lower(last)",
 };
 
-my $join = 'LEFT JOIN cust_main USING ( custnum )';
-
-my $total_sql = "SELECT ".
-  join(',', map balance( @$_, total=>1, join=>$join, where=>\@where ), @ranges);
+my $total_sql = "SELECT ". join(',', map balance( @$_, 'sum'=>1 ), @ranges).
+                " FROM cust_main $where";
 
 my $total_sth = dbh->prepare($total_sql) or die dbh->errstr;
 $total_sth->execute or die "error executing $total_sql: ". $total_sth->errstr;
@@ -157,7 +171,9 @@ my $money_char = $conf->config('money_char') || '$';
 #   'no_as'  => 1, #set to true when using in a WHERE clause (supress AS clause)
 #                 #or 0 / omit when using in a SELECT clause as a column
 #                 #  ("AS balance_$start_$end")
-#   #options for totals
+#   'sum'    => 1, #set to true to get a SUM() of the values, for totals
+#
+#   #obsolete? options for totals (passed to cust_main::balance_date_sql)
 #   'total'  => 1, #set to true to remove all customer comparison clauses
 #   'join'   => $join,   #JOIN clause
 #   'where'  => \@where, #WHERE clause hashref (elements "AND"ed together)
@@ -170,12 +186,16 @@ sub balance {
 
   #handle start and end ranges (86400 = 24h * 60m * 60s)
   my $str2time = str2time_sql;
-  $start = $start ? "( $str2time now() ) - ".($start * 86400). ' )' : '';
-  $end   = $end   ? "( $str2time now() ) - ".($end   * 86400). ' )' : '';
+  my $closing = str2time_sql_closing;
+  $start = $start ? "( $str2time now() $closing - ".($start * 86400). ' )' : '';
+  $end   = $end   ? "( $str2time now() $closing - ".($end   * 86400). ' )' : '';
 
   $opt{'unapplied_date'} = 1;
 
-  FS::cust_main->balance_date_sql( $start, $end, %opt ). $as;
+  ( $opt{sum} ? 'SUM( ' : '' ). 
+  FS::cust_main->balance_date_sql( $start, $end, %opt ).
+  ( $opt{sum} ? ' )' : '' ). 
+  $as;
 
 }