<% include( 'elements/search.html',
              'title'         => 'LATA Search Results',
              'name_singular' => 'LATA',
              'query'         => {
                                   'table'   => 'phone_avail',
                                   'hashref' => {},
                                   'select'  => 'distinct latanum',
                                 },
              'count_query'   => 'SELECT COUNT(distinct latanum) FROM phone_avail',
              'header'        => [ 'LATA',
				   'Available',
				   'Provisioned',
				   'Have Usage',
                                 ],
              'fields'        => [
        sub { # LATA
            my $did_order = shift;
            my $lata = $did_order->lata;
            $lata = $lata ? $lata->description : '';
            $did_order->latanum . " - " . $lata;
        },
		sub { # Available
		    my $latanum = shift->latanum;
		    my @dids = qsearch('phone_avail',
                                    { 'svcnum' => '',
                                      'latanum' => $latanum,
                                    }
                          );
		    return scalar(@dids);
		},
		sub { # Provisioned
		    my $latanum = shift->latanum;
		    my @dids = provisioned_dids($latanum);
		    return scalar(@dids);
		},
		sub { # Have Usage
            my $did_order = shift;
		    my @dids = provisioned_dids($did_order->latanum);

		    my $count = 0;
		    foreach my $did ( @dids ) {
                my $cust_svc = $did->cust_svc;
                next unless $cust_svc;

                my $svcnum = $cust_svc->svcnum;
                my $sql = "select count(1) from cdr where src = 
                    (select phonenum from svc_phone where svcnum = $svcnum)
                    or charged_party = (select phonenum from svc_phone where
                        svcnum = $svcnum)";
                my $c = $did_order->scalar_sql($sql);
                $count++ if $c > 0;
		    }
		    $count;
		},
              ],
              'align'         => 'lccc',
              'links'         => [
                '',
		'',
		'',
		'',
              ],
              'color' => [ 
                           '',
			   '',
			   '',
			   '',
                         ],
              'style' => [ 
                           '',
			   '',
			   '',
			   '',
                         ],
      )
%>
<%init>

warn "started @ ".time;

die "access denied"
  unless ( $FS::CurrentUser::CurrentUser->access_right('List inventory')
	 && $FS::CurrentUser::CurrentUser->access_right('List services')
	 );

# XXX: agent virtualize

sub provisioned_dids {
    my $latanum = shift;
    qsearch({   'table' => 'phone_avail',
	            'hashref' => { 'latanum' => $latanum, },
	            'extra_sql' => ' and svcnum is not null ',
	        });
}

</%init>