Index: trunk/tools/wiki_nightly_stacks_table.pl
===================================================================
--- trunk/tools/wiki_nightly_stacks_table.pl	(revision 26620)
+++ trunk/tools/wiki_nightly_stacks_table.pl	(revision 26620)
@@ -0,0 +1,284 @@
+#! /usr/local/bin/perl -w
+
+use DBI;
+use Getopt::Std;
+
+getopts('P',\%opt);
+
+$dbname = 'gpc1';
+sub init_gpc_db {
+    use constant DB_SOCKET => '/var/run/mysqld/mysqld.sock';
+    my $dbserver = 'ippdb01';
+    my $dbuser = 'ippuser';
+    my $dbpass = 'ippuser';
+    $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";
+    return($db);
+}
+
+$db = init_gpc_db();
+
+@targets = ('M31','MD01','MD02','MD03','MD04','MD05','MD06','MD07','MD08','MD09','MD10');
+
+# potential targets
+if (exists($opt{P})) {
+    foreach $t (@targets) {
+	$sth = "select filter,DATE_FORMAT(rawExp.dateobs,'%Y-%m-%d') AS day,count(filter) FROM rawExp JOIN newExp using(exp_id) WHERE rawExp.fault = 0 AND exp_type = 'OBJECT' AND comment LIKE '${t}%' AND dateobs >= '2009-12-08T00:00:00' GROUP BY day,filter";
+	$data_ref = $db->selectall_arrayref( $sth );
+	foreach $rr (@{ $data_ref }) {
+	    ($filter,$date,$N) = @{ $rr };
+	    $filter =~ s/.00000//;
+	    $potential{$date}{$t}{$filter} = $N;
+	}
+    }
+    print "|| '''Potential (Nexp):'''\n||\n";
+    print '|| Date ||';
+    foreach $t (@targets) {
+	print "$t ||";
+    }
+    print "\n";
+    
+    foreach $d (sort (keys %potential)) {
+	print '||' . $d ;
+	foreach $t (@targets) {
+	    print '|| ' ;
+	    $i = 0;
+	    foreach $f (sort (keys %{ $potential{$d}{$t} })) {
+		if ($i == 0) {
+		    print "${f}($potential{$d}{$t}{$f})";
+		    $i++;
+		}
+		else {
+		    print ",${f}($potential{$d}{$t}{$f})";
+		}
+	    }
+	}
+	print '||' . "\n";
+    }
+    print "||\n";
+}
+#queued in chip
+
+$sth = "select rawExp.dateobs,chipRun.state,rawExp.filter,count(chipRun.state),chipRun.data_group,chipRun.dist_group from chipRun JOIN rawExp using (exp_id)  where chipRun.label LIKE '%nightlystack' GROUP BY data_group,filter,state ORDER BY dateobs";
+
+$data_ref = $db->selectall_arrayref( $sth );
+
+foreach $rr (@{ $data_ref }) {
+    ($date,$state,$filter,$N,$data_group,$dist_group) = @{ $rr };
+    if ($state eq 'full') {
+	($date,$trash) = split /\s+/,$date;
+	$filter =~ s/.00000//;
+	$chips{$date}{$dist_group}{$filter} = $N;
+    }
+    if ($state eq 'new') {
+	($date,$trash) = split /\s+/,$date;
+	$filter =~ s/.00000//;
+	$queued{$date}{$dist_group}{$filter} = $N;
+    }
+
+}
+print "|| '''Queued for Chip (Nexp):'''\n||\n";
+print '|| Date ||';
+foreach $t (@targets) {
+    print "$t ||";
+}
+print "\n";
+
+foreach $d (sort (keys %queued)) {
+    print '||' . $d ;
+    foreach $t (@targets) {
+	print '|| ' ;
+	$i = 0;
+	foreach $f (sort (keys %{ $queued{$d}{$t} })) {
+	    if ($i == 0) {
+		print "${f}($queued{$d}{$t}{$f})";
+		$i++;
+	    }
+	    else {
+		print ",${f}($queued{$d}{$t}{$f})";
+	    }
+	}
+    }
+    print '||' . "\n";
+}
+print "||\n";
+print "|| '''Through Chip (Nexp):'''\n||\n";
+print '|| Date ||';
+foreach $t (@targets) {
+    print "$t ||";
+}
+print "\n";
+
+foreach $d (sort (keys %chips)) {
+    print '||' . $d ;
+    foreach $t (@targets) {
+	print '|| ' ;
+	$i = 0;
+	foreach $f (sort (keys %{ $chips{$d}{$t} })) {
+	    if ($i == 0) {
+		print "${f}($chips{$d}{$t}{$f})";
+		$i++;
+	    }
+	    else {
+		print ",${f}($chips{$d}{$t}{$f})";
+	    }
+	}
+    }
+    print '||' . "\n";
+}
+print "||\n";
+
+#warps
+$sth = "select rawExp.dateobs,warpRun.state,rawExp.filter,count(warpRun.state),warpRun.data_group,warpRun.dist_group from warpRun JOIN fakeRun using (fake_id) JOIN camRun using (cam_id) JOIN chipRun using (chip_id) JOIN rawExp using (exp_id)  where warpRun.label LIKE '%nightlystack' GROUP BY data_group,filter ORDER BY data_group,filter"; 
+$data_ref = $db->selectall_arrayref( $sth );
+
+foreach $rr (@{ $data_ref }) {
+    ($date,$state,$filter,$N,$data_group,$dist_group) = @{ $rr };
+    if ($state eq 'full') {
+	($date,$trash) = split /\s+/,$date;
+	$filter =~ s/.00000//;
+	$data{$date}{$dist_group}{$filter} = $N;
+    }
+}
+
+print "|| '''Through Warp (Nexp):'''\n||\n";
+
+print '|| Date ||';
+foreach $t (@targets) {
+    print "$t ||";
+}
+print "\n";
+
+foreach $d (sort (keys %data)) {
+    print '||' . $d ;
+    foreach $t (@targets) {
+	print '|| ' ;
+	$i = 0;
+	foreach $f (sort (keys %{ $data{$d}{$t} })) {
+	    if ($i == 0) {
+		print "${f}($data{$d}{$t}{$f})";
+		$i++;
+	    }
+	    else {
+		print ",${f}($data{$d}{$t}{$f})";
+	    }
+	}
+    }
+    print '||' . "\n";
+}
+print "||\n";
+
+# stacks	    
+
+
+$sth = "select state,count(skycell_id),data_group,dist_group,filter from stackRun where label LIKE '%nightlystack' group by data_group,filter,state";
+$data_ref = $db->selectall_arrayref( $sth );
+
+foreach $rr (@{ $data_ref }) {
+    ($state,$N,$data_group,$dist_group,$filter) = @{ $rr };
+    $filter =~ s/.00000//;
+    if ($state eq 'full') {
+	($trash,$date_str) = split /\./, $data_group;
+	$date = sprintf("%d%d%d%d-%d%d-%d%d",(split //,$date_str));
+	$stacks{$date}{$dist_group}{$filter} = $N;
+    }
+    if ($state eq 'new') {
+	($trash,$date_str) = split /\./, $data_group;
+	$date = sprintf("%d%d%d%d-%d%d-%d%d",(split //,$date_str));
+	$queuestacks{$date}{$dist_group}{$filter} = $N;
+    }	
+}
+print "|| '''Queued Stack (Nskycells):'''\n||\n";
+print '|| Date ||';
+foreach $t (@targets) {
+    print "$t ||";
+}
+print "\n";
+
+foreach $d (sort (keys %queuestacks)) {
+    print '||' . $d ;
+    foreach $t (@targets) {
+	print '|| ' ;
+	$i = 0;
+	foreach $f (sort (keys %{ $queuestacks{$d}{$t} })) {
+	    if ($i == 0) {
+		print "${f}($queuestacks{$d}{$t}{$f})";
+		$i++;
+	    }
+	    else {
+		print ",${f}($queuestacks{$d}{$t}{$f})";
+	    }
+	}
+    }
+    print '||' . "\n";
+}
+print "||\n";
+print "|| '''Through Stack (Nskycells):'''\n||\n";
+print '|| Date ||';
+foreach $t (@targets) {
+    print "$t ||";
+}
+print "\n";
+
+foreach $d (sort (keys %stacks)) {
+    print '||' . $d ;
+    foreach $t (@targets) {
+	print '|| ' ;
+	$i = 0;
+	foreach $f (sort (keys %{ $stacks{$d}{$t} })) {
+	    if ($i == 0) {
+		print "${f}($stacks{$d}{$t}{$f})";
+		$i++;
+	    }
+	    else {
+		print ",${f}($stacks{$d}{$t}{$f})";
+	    }
+	}
+    }
+    print '||' . "\n";
+}
+print "||\n";
+
+# distribution
+
+$sth = "select distRun.data_group,dist_group,filter,count(stage_id) from distRun JOIN stackRun ON (stage_id = stack_id) where distRun.label LIKE '%.nightlystack' AND distRun.state = 'full' GROUP BY distRun.data_group,filter";
+
+$data_ref = $db->selectall_arrayref( $sth );
+
+foreach $rr (@{ $data_ref }) {
+    ($data_group,$dist_group,$filter,$N) = @{ $rr };
+    $filter =~ s/.00000//;
+    ($trash,$date_str) = split /\./, $data_group;
+    $date = sprintf("%d%d%d%d-%d%d-%d%d",(split //,$date_str));
+    
+    $diststacks{$date}{$dist_group}{$filter} = $N;
+}
+print "|| '''Distributed (Nstack_ids):'''\n||\n";
+print '|| Date ||';
+foreach $t (@targets) {
+    print "$t ||";
+}
+print "\n";
+
+foreach $d (sort (keys %diststacks)) {
+    print '||' . $d ;
+    foreach $t (@targets) {
+	print '|| ' ;
+	$i = 0;
+	foreach $f (sort (keys %{ $diststacks{$d}{$t} })) {
+	    if ($i == 0) {
+		print "${f}($diststacks{$d}{$t}{$f})";
+		$i++;
+	    }
+	    else {
+		print ",${f}($diststacks{$d}{$t}{$f})";
+	    }
+	}
+    }
+    print '||' . "\n";
+}
+print "\n\n";
+