use strict; use warnings; 
use DBI; 
use JSON 'encode_json';
use cheap::mysqls qw/sql_connect/; 

  my ($DBH,$rc) = sql_connect(id=>'finddata',dbi=>'mysql');


  $DBH->do('DROP view IF EXISTS datas ');
  $DBH->do('create  
            view datas as
                 select queue_name as name, 
                       jobs_pend   as data1, 
                       jobs_run    as data2
                   from queues
           '); 

{
my @col1; 
my @col2; 
my @oth1; 
my @oth2; 

	my $stmt="select name,data1,data2 from((select name,data1,data2 from datas order by data1 +data2 desc limit 5  )union all(select 'added' as name ,sum(data1)  as data1 ,sum(data2) as data2 from (select data1,data2 from datas order by data2+data1 desc limit  18446744073709551615 offset 5 ) new)) new;";
	
	my $sth = $DBH->prepare( $stmt );
	$sth->execute() or die $sth->errstr;
	
	my $tmp = 0;
	while(my @row_array=$sth->fetchrow_array)
	{
          if ($row_array[0] ne 'added') { 
             push @col1,[$row_array[0],$row_array[1]]; 
             push @col2,[$row_array[0],$row_array[2]]; 
             } 
          else { 
             push @oth1,[$row_array[0],$row_array[1]]; 
             push @oth2,[$row_array[0],$row_array[2]]; 
             } 
	}
	$sth->finish;

my @sort1=((sort {$b->[1] <=> $a->[1]} @col1),@oth1); 
my $json1 = encode_json( \@sort1 );
print $json1."\n"; 

my @sort2=((sort {$b->[1] <=> $a->[1]} @col2),@oth2); 
my $json2 = encode_json( \@sort2 );
print $json2."\n"; 

}
  $DBH->do('DROP view IF EXISTS datas ');
  $DBH->disconnect();