<& elements/search.html,
     'title'         => 'Time worked per-customer summary',
     'name_singular' => 'customer',
     'query'       => $sql_query,
     'count_query' => $count_query,
     'header'      => [ FS::UI::Web::cust_header(
                          $cgi->param('cust_fields')
                        ),
                        @extra_headers,
                        'Support time',
                        #'Development time',
                        'Unclassified time',
                      ],
     'fields'      => [
       \&FS::UI::Web::cust_fields,
       @extra_fields,
       $support_time_sub,
       $unclass_time_sub,
     ],
     'color'       => [ FS::UI::Web::cust_colors(),
                        map '', @extra_fields
                      ],
     'style'       => [ FS::UI::Web::cust_styles(),
                        map '', @extra_fields
                      ],
     'align'       => [ FS::UI::Web::cust_aligns(),
                        map '', @extra_fields
                      ],
     'links'       => [ ( map { $_ ne 'Cust. Status' ? $link : '' }
                              FS::UI::Web::cust_header(
                                         $cgi->param('cust_fields')
                                                      )
                        ),
                        map '', @extra_fields
                      ],

&>
<%init>

die "access denied"
  unless $FS::CurrentUser::CurrentUser->access_right('List rating data')
;

#false laziness w/cust_main.html (we're really only filtering on status for now)

my %search_hash = ();

#$search_hash{'query'} = $cgi->keywords;

#scalars
my @scalars = qw (
  agentnum salesnum status address city county state zip country                
  invoice_terms                                                                 
  no_censustract with_geocode with_email tax no_tax POST no_POST                
  custbatch usernum                                                             
  cancelled_pkgs                                                                
  cust_fields flattened_pkgs                                                    
  all_tags                                                                      
  all_pkg_classnums                                                             
  any_pkg_status                                                                
);                                                                              
                                                                                
for my $param ( @scalars ) {                                                    
  $search_hash{$param} = scalar( $cgi->param($param) )                          
    if length($cgi->param($param));                                             
}                                                                               
                                                                                
#lists                                                                          
for my $param (qw( classnum refnum tagnum pkg_classnum )) {                     
  $search_hash{$param} = [ $cgi->param($param) ];                               
}

###
# etc
###

my $sql_query = FS::cust_main::Search->search(\%search_hash);
my $count_query   = delete($sql_query->{'count_query'});
my @extra_headers = @{ delete($sql_query->{'extra_headers'}) };
my @extra_fields  = @{ delete($sql_query->{'extra_fields'})  };

my $link = [ "${p}view/cust_main.cgi?", 'custnum' ];

#eofalse (cust_main.html)

#false laziness / cribbed from search/rt_ticket.html

my $twhere = "
  WHERE Transactions.ObjectType = 'RT::Ticket'
"; #AND Transactions.ObjectId = Tickets.Id

my $transaction_time = "
CASE transactions.type when 'Set'
  THEN (to_number(newvalue,'999999')-to_number(oldvalue, '999999')) * 60
  ELSE timetaken*60
END";

$twhere .= "
  AND (    ( Transactions.Type = 'Set'
             AND Transactions.Field = 'TimeWorked'
             AND Transactions.NewValue != Transactions.OldValue )
        OR ( Transactions.Type IN ( 'Create', 'Comment', 'Correspond', 'Touch' )
             AND Transactions.TimeTaken > 0
           )
      )";

my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi);
# TIMESTAMP is Pg-specific... ?
if ( $beginning > 0 ) {
  $beginning = "TIMESTAMP '". time2str('%Y-%m-%d %X', $beginning). "'";
  $twhere .= " AND Transactions.Created >= $beginning ";
}
if ( $ending < 4294967295 ) {
  $ending =    "TIMESTAMP '". time2str('%Y-%m-%d %X', $ending).    "'";
  $twhere .= " AND Transactions.Created <= $ending    ";
}

my $transactions = "FROM Transactions $twhere";

#eofalse (rt_ticket.html)

my $support_time_sub = sub {
  my $cust_main = shift;
  my $sec = 0;
  foreach my $ticket ($cust_main->tickets) {

    my $TimeType = FS::Record->scalar_sql(
      "SELECT Content FROM ObjectCustomFieldValues
                      JOIN CustomFields
                      ON (ObjectCustomFieldValues.CustomField = CustomFields.Id)
         WHERE CustomFields.Name = 'TimeType'
           AND ObjectCustomFieldValues.ObjectType = 'RT::Ticket'
           AND ObjectCustomFieldValues.Disabled = 0
           AND ObjectId = ". $ticket->{id}
    );
    next unless $TimeType eq 'support';

    $sec += FS::Record->scalar_sql(
      "SELECT SUM($transaction_time) $transactions ".
      " AND Transactions.ObjectId = ". $ticket->{id}
    );
  }

  (($sec < 0) ? '-' : '' ). int(abs($sec)/3600)."h".sprintf("%02d",(abs($sec)%3600)/60)."m";

};

my $unclass_time_sub = sub {
  my $cust_main = shift;
  my $sec = 0;
  foreach my $ticket ($cust_main->tickets) {

    my $TimeType = FS::Record->scalar_sql(
      "SELECT Content FROM ObjectCustomFieldValues
                      JOIN CustomFields
                      ON (ObjectCustomFieldValues.CustomField = CustomFields.Id)
         WHERE CustomFields.Name = 'TimeType'
           AND ObjectCustomFieldValues.ObjectType = 'RT::Ticket'
           AND ObjectCustomFieldValues.Disabled = 0
           AND ObjectId = ". $ticket->{id}
    );
    next unless $TimeType eq '';

    $sec += FS::Record->scalar_sql(
      "SELECT SUM($transaction_time) $transactions ".
      " AND Transactions.ObjectId = ". $ticket->{id}
    );
  }

  (($sec < 0) ? '-' : '' ). int(abs($sec)/3600)."h".sprintf("%02d",(abs($sec)%3600)/60)."m";

};

</%init>