Index: /trunk/tools/fix.ws.nightlyscience
===================================================================
--- /trunk/tools/fix.ws.nightlyscience	(revision 40466)
+++ /trunk/tools/fix.ws.nightlyscience	(revision 40466)
@@ -0,0 +1,125 @@
+#!/bin/csh -f
+
+if ($#argv != 2) then 
+  echo "USAGE: fix.ws.nightlyscience (password) (mode)"
+  echo "MODES: help, list, update, difflist, difflogs, difffix"
+  echo "give password for ippuser account"
+  exit 2
+endif
+
+set pword = $1
+set mode = $2
+
+set thisyear = "2015"
+set realyear = `date +%Y`
+
+if ($thisyear != $realyear) then
+  echo "WARNING: the hardwired year is $thisyear, but it seems we are past $thisyear.  if all $thisyear entries are done, please update the year"
+  echo "  (we have a hardwired year to avoid excessively long queries)"
+endif
+
+if ($mode == "help") then
+  echo "This script provides tools to manage the WS diffs which are stuck due to missing inputs"
+  echo "USAGE: fix.ws.nightlyscience (password) (mode)"
+  echo "There are 6 mode options:"
+  echo "  help: this help info"
+  echo "  list: display WS diff inputs which are not ready for processing"
+  echo "  update: provide chiptool and warptool commands to set the input states to update"
+  echo "    	  NOTE: the above set the label to 'ws_nightly_update', which is included by default in stdscience"
+  echo "  difflist: list outstanding diffRuns"
+  echo "  difflogs: select logs from the outstanding diffRuns and pipe through 'less'"
+  echo "  difffix:  provide difftool commands to set the fault and quality for the faulted diff runs (fault 2 -> 66, 5 -> 42)"
+  exit 1
+endif
+
+if ($mode == "list") then
+  set query = ""
+  set query = "$query select chip_id, warp_id, diff_id, diffRun.data_group, chipRun.state, warpRun.state, diffRun.state"
+  set query = "$query from diffRun"
+  set query = "$query join diffInputSkyfile using (diff_id)"
+  set query = "$query join warpRun on (warp1 = warp_id)"
+  set query = "$query join fakeRun using (fake_id)"
+  set query = "$query join camRun using (cam_id)"
+  set query = "$query join chipRun using (chip_id)"
+  set query = "$query where diffRun.state = 'new'"
+  set query = "$query and diffRun.label like '%WS.nightlyscience'"
+  set query = "$query and diffRun.data_group like '%.$thisyear%'"
+  set query = "$query group by chip_id"
+
+  mysql -h scidbm -u ippuser -p$pword gpc1 -e "$query"
+  exit 0
+endif
+
+if ($mode == "update") then
+  set query = ""
+  set query = "$query select chip_id, warp_id, diff_id, diffRun.data_group, chipRun.state, warpRun.state, diffRun.state"
+  set query = "$query from diffRun"
+  set query = "$query join diffInputSkyfile using (diff_id)"
+  set query = "$query join warpRun on (warp1 = warp_id)"
+  set query = "$query join fakeRun using (fake_id)"
+  set query = "$query join camRun using (cam_id)"
+  set query = "$query join chipRun using (chip_id)"
+  set query = "$query where diffRun.state = 'new'"
+  set query = "$query and diffRun.label like '%WS.nightlyscience'"
+  set query = "$query and diffRun.data_group like '%.$thisyear%'"
+
+  set qchip = "$query and (chipRun.state != 'full')"
+  set qwarp = "$query and (warpRun.state != 'full')"
+
+  set qchip = "$qchip group by chip_id"
+  set qwarp = "$qwarp group by chip_id"
+
+  mysql -B -h scidbm -u ippuser -p$pword gpc1 -e "$qchip" | awk '(NR > 1){printf "chiptool -dbname gpc1 -setimfiletoupdate  -set_label ws_nightly_update -chip_id %s\n", $1}'
+  mysql -B -h scidbm -u ippuser -p$pword gpc1 -e "$qwarp" | awk '(NR > 1){printf "warptool -dbname gpc1 -setskyfiletoupdate -set_label ws_nightly_update -warp_id %s\n", $2}'
+  exit 0
+endif
+
+if (($mode == "difflist") || ($mode == "diffstate")) then
+  set query = ""
+  set query = "$query SELECT diffSkyfile.diff_id, diffSkyfile.skycell_id, diffRun.label, diffRun.data_group, diffRun.state, diffSkyfile.fault"
+  set query = "$query FROM diffRun"
+  set query = "$query JOIN diffSkyfile using (diff_id)"
+  set query = "$query WHERE diffSkyfile.fault != 0"
+  set query = "$query AND diffRun.label like '%.WS.nightlyscience'"
+
+  mysql -h scidbm -u ippuser -p$pword gpc1 -e "$query"
+  exit 0
+endif
+
+if ($mode == "difflogs") then
+  set query = ""
+  set query = "$query SELECT diffSkyfile.path_base"
+  set query = "$query FROM diffRun"
+  set query = "$query JOIN diffSkyfile using (diff_id)"
+  set query = "$query WHERE diffSkyfile.fault != 0"
+  set query = "$query AND diffRun.label like '%.WS.nightlyscience'"
+
+  foreach f (`mysql -B -h scidbm -u ippuser -p$pword gpc1 -e "$query" | awk '(NR > 1){printf "%s.log\n", $1}'`)
+    set file = `neb-locate -p $f`
+    if ($status) then
+      echo "problem finding log file $f, here is neb-stat output:"
+      neb-stat $f
+      echo -----------------------------
+      echo "press cntl-D"
+      cat
+      continue
+    endif
+    less $file
+  end
+  exit 0
+endif
+
+if ($mode == "difffix") then
+  set query = ""
+  set query = "$query SELECT diffSkyfile.diff_id, diffSkyfile.skycell_id, diffRun.label, diffRun.data_group, diffRun.state, diffSkyfile.fault"
+  set query = "$query FROM diffRun"
+  set query = "$query JOIN diffSkyfile using (diff_id)"
+  set query = "$query WHERE diffSkyfile.fault != 0"
+  set query = "$query AND diffRun.label like '%.WS.nightlyscience'"
+
+  mysql -B -h scidbm -u ippuser -p$pword gpc1 -e "$query" | awk '($6 == 2){printf "difftool -dbname gpc1 -updatediffskyfile -fault 0 -set_quality 66 -diff_id %s -skycell_id %s\n", $1, $2}'
+  mysql -B -h scidbm -u ippuser -p$pword gpc1 -e "$query" | awk '($6 == 5){printf "difftool -dbname gpc1 -updatediffskyfile -fault 0 -set_quality 42 -diff_id %s -skycell_id %s\n", $1, $2}'
+  exit 0
+endif
+
+exit 2