#!/usr/bin/perl -w
use DBI;
use constant DB_SOCKET => '/var/run/mysqld/mysqld.sock'; # Socket for mysql
use Getopt::Long qw( GetOptions :config auto_help auto_version gnu_getopt );

GetOptions('label=s' => \$label,) or die "unable to parse arguments.\n";
die "Unknown option: @ARGV\n" if @ARGV;
die "required options: --label \n"
	   unless defined $label;


my $dbname = 'gpc1';
my $dbserver = 'ippdb01';
my $dbuser = 'ippuser';
my $dbpass = 'ippuser';
my $db = DBI->connect(
                      "DBI:mysql:database=${dbname};host=${dbserver};mysql_socket=" .
                      DB_SOCKET(),${dbuser},${dbpass}, 
                      {
                          RaiseError => 1, AutoCommit => 1}
                      ) or die "Unable to connect to database $DBI::errstr\n";
my $qr = "SELECT label,state,count(state) as n FROM chipRun WHERE label like '$label' group by state;";

my $query = $db->selectall_arrayref( $qr ) ||
    die "Unable to execute SQL: $DBI::errstr\n";
print "chip stage \n";

foreach my $item (@{$query}) {
    print "${$item}[0] ${$item}[1] ${$item}[2] \n";
}
print "\ncam stage\n";
 $qr = "SELECT label,state,count(state) as n FROM camRun WHERE label like '$label' group by state;";

 $query = $db->selectall_arrayref( $qr ) ||
    die "Unable to execute SQL: $DBI::errstr\n";


foreach  $item (@{$query}) {
    print "${$item}[0] ${$item}[1] ${$item}[2] \n";
}
print "\nfake stage\n";
 $qr = "SELECT label,state,count(state) as n FROM fakeRun WHERE label like '$label' group by state;";

 $query = $db->selectall_arrayref( $qr ) ||
    die "Unable to execute SQL: $DBI::errstr\n";

#rint $qr;
foreach  $item (@{$query}) {
    print "${$item}[0] ${$item}[1] ${$item}[2] \n";
}
print "\nwarp stage\n";
 $qr = "SELECT label,state,count(state) as n FROM warpRun WHERE label like '$label' group by state;";

 $query = $db->selectall_arrayref( $qr ) ||
    die "Unable to execute SQL: $DBI::errstr\n";


foreach  $item (@{$query}) {
    print "${$item}[0] ${$item}[1] ${$item}[2] \n";
}

print "\nstack stage\n";
 $qr = "SELECT label,state,count(state) as n FROM stackRun WHERE label like '$label' group by state;";

 $query = $db->selectall_arrayref( $qr ) ||
    die "Unable to execute SQL: $DBI::errstr\n";


foreach  $item (@{$query}) {
    print "${$item}[0] ${$item}[1] ${$item}[2] \n";
}
print "\ndiff stage\n";
 $qr = "SELECT label,state,count(state) as n FROM diffRun WHERE label like '$label' group by state;";

 $query = $db->selectall_arrayref( $qr ) ||
    die "Unable to execute SQL: $DBI::errstr\n";


foreach  $item (@{$query}) {
    print "${$item}[0] ${$item}[1] ${$item}[2] \n";
}
print "\nmagic stage\n";
 $qr = "SELECT label,state,count(state) as n FROM magicRun WHERE label like '$label' group by state;";

 $query = $db->selectall_arrayref( $qr ) ||
    die "Unable to execute SQL: $DBI::errstr\n";


foreach  $item (@{$query}) {
    print "${$item}[0] ${$item}[1] ${$item}[2] \n";
}
print "\nmagicDS stage\n";
 $qr = "SELECT label,state,count(state) as n FROM magicDSRun WHERE label like '$label' group by state;";

 $query = $db->selectall_arrayref( $qr ) ||
    die "Unable to execute SQL: $DBI::errstr\n";


foreach  $item (@{$query}) {
    print "${$item}[0] ${$item}[1] ${$item}[2] \n";
}

print "\ndist stage\n";
 $qr = "SELECT label,state,count(state) as n FROM distRun WHERE label like '$label' group by state;";

 $query = $db->selectall_arrayref( $qr ) ||
    die "Unable to execute SQL: $DBI::errstr\n";


foreach  $item (@{$query}) {
    print "${$item}[0] ${$item}[1] ${$item}[2] \n";
}

print "\n-------------------\n";
print "faults and log files\n\n";

$qr = "select chip_id,class_id,state, fault, path_base from chipRun join chipProcessedImfile using(chip_id) where   label ='$label' and (fault > 0) order by fault desc;";

 $query = $db->selectall_arrayref( $qr ) ||
    die "Unable to execute SQL: $DBI::errstr\n";


foreach  $item (@{$query}) {
    print "chip ${$item}[2] ${$item}[3] ${$item}[4].${$item}[1].log\n";
}
 

$qr = "select cam_id,state, fault, path_base from camRun join camProcessedExp using (cam_id) where label ='$label' and (fault > 0) order by fault desc;";

 $query = $db->selectall_arrayref( $qr ) ||
    die "Unable to execute SQL: $DBI::errstr\n";


foreach  $item (@{$query}) {
    print "cam  ${$item}[1] ${$item}[2] ${$item}[3].log\n";
}

$qr = "select fake_id,state, fault, path_base from fakeRun join fakeProcessedImfile using(fake_id) where label ='$label' and (fault > 0) order by fault desc;";

 $query = $db->selectall_arrayref( $qr ) ||
    die "Unable to execute SQL: $DBI::errstr\n";


foreach  $item (@{$query}) {
    print "fake ${$item}[1] ${$item}[2] ${$item}[3].log\n";
}

$qr = "select warp_id,skycell_id,state, fault, path_base from warpRun join warpSkyfile using(warp_id) where label ='$label' and (fault > 0) order by fault desc;";  

 $query = $db->selectall_arrayref( $qr ) ||
    die "Unable to execute SQL: $DBI::errstr\n";


foreach  $item (@{$query}) {
    print "warp ${$item}[2] ${$item}[3] ${$item}[4].log\n";
} 

$qr = "select warp_id,skycell_id,state, fault, workdir from warpRun join warpSkyCellMap using(warp_id) where label ='$label' and (fault > 0) order by fault desc;";  

 $query = $db->selectall_arrayref( $qr ) ||
    die "Unable to execute SQL: $DBI::errstr\n";


foreach  $item (@{$query}) {
    print "warp ${$item}[2] ${$item}[3] ${$item}[4]\n";
} 






$qr = "select stack_id,skycell_id,state, fault, path_base from stackRun join stackSumSkyfile using(stack_id) where label ='$label' and (fault > 0) order by fault desc;";

 $query = $db->selectall_arrayref( $qr ) ||
    die "Unable to execute SQL: $DBI::errstr\n";


foreach  $item (@{$query}) {
    print "stck ${$item}[2] ${$item}[3] ${$item}[4].log \n";
} 


$qr = "select diff_id,skycell_id,state, fault, path_base from diffRun join diffSkyfile using(diff_id) where label ='$label' and (fault > 0) order by fault desc;";  

 $query = $db->selectall_arrayref( $qr ) ||
    die "Unable to execute SQL: $DBI::errstr\n";


foreach  $item (@{$query}) {
    print "diff ${$item}[2] ${$item}[3] ${$item}[4].log  \n";
} 
 

$qr = "select magic_id, magicRun.state, magicNodeResult.fault, path_base, workdir from magicRun join magicNodeResult using (magic_id) where label ='$label' and (magicNodeResult.fault > 0) order by magicNodeResult.fault desc;"; 

 $query = $db->selectall_arrayref( $qr ) ||
    die "Unable to execute SQL: $DBI::errstr\n";
foreach  $item (@{$query}) {
if (!defined( ${$item}[3])) { ${$item}[3]=${$item}[4];}
    print "magic ${$item}[1] ${$item}[2] ${$item}[3]\n";
}

$qr = "select magic_id, state, magicDSFile.fault, outroot from magicDSRun join magicDSFile using (magic_ds_id) where label ='$label' and (magicDSFile.fault > 0) order by magicDSFile.fault desc;"; 

 $query = $db->selectall_arrayref( $qr ) ||
    die "Unable to execute SQL: $DBI::errstr\n";



foreach  $item (@{$query}) {
    print "destreak ${$item}[1] ${$item}[2]  ${$item}[3] \n";
}



$qr = "select  distRun.state, distComponent.fault, distComponent.outdir, outroot from distRun join distComponent using (dist_id) where label ='$label' and (distComponent.fault > 0 ) order by distComponent.fault desc;"; 

 $query = $db->selectall_arrayref( $qr ) ||
    die "Unable to execute SQL: $DBI::errstr\n";


foreach  $item (@{$query}) {
    print "distComp ${$item}[0] ${$item}[1] ${$item}[2]\n";
}

$qr = "select  distRun.state, distRun.fault, outroot from distRun where label ='$label' and (distRun.fault > 0 ) order by distRun.fault desc;"; 

 $query = $db->selectall_arrayref( $qr ) ||
    die "Unable to execute SQL: $DBI::errstr\n";


foreach  $item (@{$query}) {
    print "distRun ${$item}[0] ${$item}[1] ${$item}[2]\n";
}

$db->disconnect();
