$days = $1;
}
- push @where, balance($days, 0, 'no_as'=>1). " > 0";
+ push @where, balance($days, 0, 'no_as'=>1). ' > 0'; # != 0';
}
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;
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;
# '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)
#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;
}