1, 'noas'=>1). " > 0";
-select *, $owed_cols, $packages_cols from cust_main
-where 0 <
- coalesce(
- ( select $charged from cust_bill
- where cust_main.custnum = cust_bill.custnum
- )
- ,0
- )
+my $agentnum = '';
+if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) {
+ $agentnum = $1;
+ $where .= " AND agentnum = '$agentnum' ";
+}
-order by lower(company), lower(last)
+#here is the agent virtualization
+$where .= ' AND '. $FS::CurrentUser::CurrentUser->agentnums_sql;
-END
+my $count_sql = "select count(*) from cust_main $where";
- my $total_sql = "select $owed_cols";
+my $sql_query = {
+ 'table' => 'cust_main',
+ 'hashref' => {},
+ 'select' => "*, $owed_cols, $packages_cols",
+ 'extra_sql' => "$where order by coalesce(lower(company), ''), lower(last)",
+};
- my $sth = dbh->prepare($sql) or die dbh->errstr;
- $sth->execute or die $sth->errstr;
+my $total_sql = "select ".
+ join(',', map owed( @$_, 'agentnum'=>$agentnum ), @ranges );
- my $total_sth = dbh->prepare($total_sql) or die dbh->errstr;
- $total_sth->execute or die $total_sth->errstr;
+my $total_sth = dbh->prepare($total_sql) or die dbh->errstr;
+$total_sth->execute or die "error executing $total_sql: ". $total_sth->errstr;
+my $row = $total_sth->fetchrow_hashref();
-%>
-<%= 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';
- }
- }
-%>
-
- <%= $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'} ) %> |
-
-
-