Index: /branches/czw_branch/20170908/tools/hsc/check_completion.pl
===================================================================
--- /branches/czw_branch/20170908/tools/hsc/check_completion.pl	(revision 40446)
+++ /branches/czw_branch/20170908/tools/hsc/check_completion.pl	(revision 40446)
@@ -0,0 +1,144 @@
+#! /usr/bin/env perl
+
+use Carp;
+use warnings;
+use strict;
+use IPC::Cmd 0.36 qw( can_run run );
+use PS::IPP::Metadata::List qw( parse_md_list );
+use PS::IPP::Config 1.01 qw( :standard );
+use Getopt::Long qw( GetOptions :config auto_help auto_version gnu_getopt );
+use Pod::Usage qw( pod2usage );
+use DBI;
+
+my $dbname = 'hsc';
+my $dbhost = 'hsc00';
+my $ipprc  = PS::IPP::Config->new();
+my $siteConfig = $ipprc->{_siteConfig};
+
+
+my $db = init_hsc_db();
+
+my $label = shift(@ARGV);
+
+my %results = ();
+my %unexpected_state = ();
+
+## Find counts
+
+my $q = "select count(*),state,data_group,0 FROM chipRun WHERE label = '${label}' GROUP BY state,data_group";
+my $r = $db->selectall_arrayref( $q );
+foreach my $rr (@{ $r }) {
+    add_it("CHIP",@{ $rr });
+}
+
+$q = "select count(*),state,data_group,quality FROM camRun LEFT JOIN camProcessedExp USING(cam_id) WHERE label = '${label}' GROUP BY state,data_group,quality";
+$r = $db->selectall_arrayref( $q );
+foreach my $rr (@{ $r }) {
+    add_it("CAM",@{ $rr });
+}
+
+$q = "select count(*),state,data_group,0 FROM fakeRun WHERE label = '${label}' GROUP BY state,data_group";
+$r = $db->selectall_arrayref( $q );
+foreach my $rr (@{ $r }) {
+    add_it("FAKE",@{ $rr });
+}
+
+$q = "select count(*),state,data_group,0 FROM warpRun WHERE label = '${label}' GROUP BY state,data_group";
+$r = $db->selectall_arrayref( $q );
+foreach my $rr (@{ $r }) {
+    add_it("WARP",@{ $rr });
+}
+
+$q = "select count(*),state,data_group,quality FROM stackRun LEFT JOIN stackSumSkyfile USING(stack_id) WHERE label = '${label}' GROUP BY state,data_group,quality";
+$r = $db->selectall_arrayref( $q );
+foreach my $rr (@{ $r }) {
+    add_it("STACK",@{ $rr });
+}
+
+$q = "select count(*),state,data_group,quality FROM staticskyRun LEFT JOIN staticskyResult USING(sky_id) WHERE label = '${label}' GROUP BY state,data_group,quality";
+$r = $db->selectall_arrayref( $q );
+foreach my $rr (@{ $r }) {
+    add_it("STATIC",@{ $rr });
+}
+
+$q = "select count(*),state,data_group,quality FROM skycalRun LEFT JOIN skycalResult USING(skycal_id) WHERE label = '${label}' GROUP BY state,data_group,quality";
+$r = $db->selectall_arrayref( $q );
+foreach my $rr (@{ $r }) {
+    add_it("SKYCAL",@{ $rr });
+}
+
+
+## Print results
+print "$label\n";
+printf("%-10s   %-20s  %8s  %8s  %8s\n","stage","data group","state","good","bad");
+print_it("CHIP","chip");
+print_it("CAM","camera");
+print_it("FAKE","fake");
+print_it("WARP","warp");
+print_it("STACK","stack");
+print_it("STATIC","static sky");
+print_it("SKYCAL","sky cal");
+
+
+## Subroutines for clarity
+
+
+sub add_it {
+    my $stage = shift(@_);
+    my $count = shift(@_);
+    my $state = shift(@_);
+    my $data_group = shift(@_);
+    my $quality = shift(@_);
+
+#    print "$stage $count $state $data_group $quality\n";
+
+    if ($quality != 0) { $quality = 1; }
+    if (($state ne 'full')&&($state ne 'new')) { $unexpected_state{$state} = 1; }
+
+    for (my $q = 0; $q <= 1; $q++) {
+	unless (exists( $results{$stage}{$data_group}{$state}{$q} )) {
+	    $results{$stage}{$data_group}{$state}{$q} = 0;
+	}
+    }
+    unless (exists( $results{$stage}{$data_group}{$state}{$quality} )) {
+	$results{$stage}{$data_group}{$state}{$quality} = 0;
+    }
+    $results{$stage}{$data_group}{$state}{$quality} += $count;
+}
+
+sub print_it {
+    my $stage = shift;
+    my $stage_name = shift;
+    my $dg;
+
+    foreach $dg (keys %{ $results{$stage} }) {
+	if (exists($results{$stage}{$dg}{"new"})) {
+	    printf("%-10s   %-20s  %8s  %8d  %8d\n",$stage_name,$dg,"new",$results{$stage}{$dg}{"new"}{0}, $results{$stage}{$dg}{"new"}{1});
+	}
+	if (exists($results{$stage}{$dg}{"full"})) {
+	    printf("%-10s   %-20s  %8s  %8d  %8d\n",$stage_name,$dg,"full",$results{$stage}{$dg}{"full"}{0}, $results{$stage}{$dg}{"full"}{1});
+	}
+	foreach my $ustate (keys %unexpected_state) {
+	    if (exists($results{$stage}{$dg}{$ustate})) {
+		printf("%-10s  %-20s !%8s! %8d  %8d\n",$stage_name,$dg,$ustate,$results{$stage}{$dg}{$ustate}{0},$results{$stage}{$dg}{$ustate}{1});
+	    }
+	}
+    }
+}
+
+sub init_hsc_db {
+    ## change to use the siteConfig setting, while readonly probably do not want to use a readonly replicated DB incase it gets behind
+    my $dbserver = $dbhost;
+    my $dbuser = metadataLookupStr($ipprc->{_siteConfig}, "RO_DBUSER");
+    my $dbpass = metadataLookupStr($ipprc->{_siteConfig}, "RO_DBPASSWORD");
+    die "database configuration not set up" unless defined($dbserver);
+    die "database configuration not set up" unless defined($dbuser);
+    die "database configuration not set up" unless defined($dbpass);
+    use constant DB_SOCKET => '/var/run/mysqld/mysqld.sock';
+    $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);
+}