GeneralUtilities.java

package ProdDatabase;
import java.util.*;
import java.sql.*;
import java.util.zip.*;
import java.util.regex.*;
import java.io.*;
import Preferences.*;

public class GeneralUtilities implements SCTDBInfo {
    public static Hashtable generalHash;
    public static Vector testNumberList;

// *****************************************************************************************************************
    public static void itemExists(String sn1,String sn2,boolean resetHash) throws Exception {
      if(resetHash || generalHash==null) generalHash = new Hashtable();
      Vector itemList = new Vector();
      StringBuffer sqlStat = new StringBuffer("SELECT ");
      sqlStat.append("ser_no,ctype,locn_name FROM items WHERE ser_no >="+sn1+" AND ser_no <="+sn2);
      sqlStat.append(" ORDER BY ser_no");
//      System.out.println(sqlStat.toString());

      SCTDBInterface db = SCTDBInterface.getInstance();

      Statement statement = db.connection.createStatement();
      ResultSet resultSet = statement.executeQuery(sqlStat.toString());
      int recordCount=0;
      for(boolean n = resultSet.next() ; n==true ; n=resultSet.next() ){
         recordCount++;
         String serno = resultSet.getString(1);
          String ctype = resultSet.getString(2);
          String location = resultSet.getString(3);
          generalHash.put((String)serno,(String)ctype);
          System.out.println(serno+"("+ctype+") already exists, located at "+location);
          }
      statement.close();
      return;
      }
  public static boolean itemExists(String sn1) throws Exception {
       return itemExists(sn1,true);
       }
//************************************************************
  public static boolean itemExists(String sn1, boolean showDialog) throws Exception {
     boolean itExists=false;
     String cType=null;
     StringBuffer sqlStat = new StringBuffer("SELECT ctype FROM items WHERE ser_no="+sn1);
      Statement statement = SCTDBInterface.getInstance().connection.createStatement();
      ResultSet resultSet = statement.executeQuery(sqlStat.toString());
      for(boolean n = resultSet.next() ; n==true ; n=resultSet.next() ){
         cType=resultSet.getString(1);
         if(cType.indexOf("Module")!=-1) itExists=true;
         }
      statement.close();
      if(!itExists && showDialog) {
         if(cType==null) javax.swing.JOptionPane.showMessageDialog(null,sn1+" does not exist in the database!","No such device",javax.swing.JOptionPane.INFORMATION_MESSAGE);
         else  javax.swing.JOptionPane.showMessageDialog(null,sn1+" is a "+cType+" (not a module)","Not a module",javax.swing.JOptionPane.INFORMATION_MESSAGE);
         }
      return itExists;
      }
         

// *****************************************************************************************************************
    public static String validateLocation(String theLocation) throws Exception {
// get location name where argument = ulocation name OR institute username OR institute user number
      String validatedLocation="unknown";
      boolean isNumber = true;
      try {
         int locationNumber = Integer.parseInt(theLocation);
      }catch(Exception nException) {isNumber=false;}

      StringBuffer sqlStat = new StringBuffer("SELECT locn_name FROM LOCNS");
      if(isNumber) sqlStat.append(" WHERE locn="+theLocation);
      else sqlStat.append(" WHERE (locn_name='"+theLocation+"' OR username='"+theLocation+"')");

      SCTDBInterface db = SCTDBInterface.getInstance();
      Statement statement = db.connection.createStatement();
      ResultSet resultSet = statement.executeQuery(sqlStat.toString());
      int recordCount=0;
      for(boolean n = resultSet.next() ; n==true ; n=resultSet.next() ){
           validatedLocation = resultSet.getString(1);
            }
//      System.out.println("Retrieved "+recordCount+" records in total.");
      statement.close();
      return validatedLocation;
      }
// *****************************************************************************************************************
    public static String validatePersonInitial(String theUser) throws Exception {

      String person="unknown";
      StringBuffer sqlStat = new StringBuffer("SELECT initls FROM persons WHERE initls='"+theUser+"'");

      SCTDBInterface db = SCTDBInterface.getInstance();
      Statement statement = db.connection.createStatement();
      ResultSet resultSet = statement.executeQuery(sqlStat.toString());
      resultSet = statement.executeQuery(sqlStat.toString());
      int recordCount=0;
      for(boolean n = resultSet.next() ; n==true ; n=resultSet.next() ){
           person = resultSet.getString(1);
            }
//      System.out.println("Retrieved "+recordCount+" records in total.");
      statement.close();
      return person;
      }

// ******************************************************************************************************************
    public static Vector getNameList(String location) throws Exception {
     StringBuffer sqlStat = new StringBuffer("SELECT initls FROM persons");
     sqlStat.append(" WHERE persons.locn_name LIKE '"+location+"'");

      SCTDBInterface db = SCTDBInterface.getInstance();
      Statement statement = db.connection.createStatement();
      ResultSet resultSet = statement.executeQuery(sqlStat.toString());

     int recordCount=0;
     Vector itemList = new Vector();
     for(boolean n = resultSet.next() ; n==true ; n=resultSet.next() ){
          recordCount++;
          itemList.addElement(resultSet.getString(1));  
            }
//      System.out.println("Retrieved "+recordCount+" names in total.");

      statement.close();

      if(itemList.size()>0) return itemList;
// no users at this location? If so, return all users
//      System.out.println("As there are no users defined at "+location+" I will return all users in the database.");
     sqlStat = new StringBuffer("SELECT initls FROM persons ORDER BY initls");

     statement = db.connection.createStatement();
     resultSet = statement.executeQuery(sqlStat.toString());
     recordCount=0;
     for(boolean n = resultSet.next() ; n==true ; n=resultSet.next() ){
          recordCount++;
          itemList.addElement(resultSet.getString(1));   // serial no
            }
      statement.close();
//      System.out.println("Retrieved "+recordCount+" names in total.");

      return itemList;
     }


//****************************************************************************
public static double get500VData(String testno) throws Exception {
     java.util.regex.Pattern i500Pattern = java.util.regex.Pattern.compile(".*500[\\.\\d]*\\s*([\\.\\d]+).*");
     java.util.regex.Pattern lastCharPattern = java.util.regex.Pattern.compile("[0-9\\.]");

     Vector thisData = getRawData(testno);
     if(thisData.size()==0 || !isSctDaqData(testno)) return 0.;
     String[] theDataString = ((String)thisData.elementAt(1)).split("[\\r\\n]");
     for(int i=theDataString.length-1;i>=0;i--) {
         java.util.regex.Matcher matcher = i500Pattern.matcher(theDataString[i]);
         if(matcher.matches()) {
            String data500 = theDataString[i].substring(matcher.start(1),matcher.end(1));
            return Double.parseDouble(data500);
            }
         }
      return 0.;
}
//*********************************************************************
    public static boolean isSctDaqData(String testno) throws Exception {
     Statement statement = SCTDBInterface.getInstance().connection.createStatement();
     ResultSet resultSet = statement.executeQuery("SELECT version FROM SCT_TSTDAQINFO where test_no="+testno);
     String version=null;
     for(boolean n = resultSet.next() ; n==true ; n=resultSet.next() ){
          version = resultSet.getString(1);
            }
     statement.close();
     if(version==null) return false;
     return version.matches("\\d+\\.\\d+");
     }

//**********************************************************************
    public static Vector getLocationList() throws Exception {
     System.out.println("Retrieving list of valid institute names from database ...");
     Statement statement = SCTDBInterface.getInstance().connection.createStatement();
     ResultSet resultSet = statement.executeQuery("SELECT locn_name FROM locns ORDER BY locn_name");
     Vector itemList = new Vector();
     for(boolean n = resultSet.next() ; n==true ; n=resultSet.next() ){
          itemList.addElement(resultSet.getString(1));   // serial no
            }
//      System.out.println("Retrieved "+recordCount+" names in total.");
      statement.close();
      return itemList;
     }
//*************************************************************************
      public static void downloadPSFile(javax.swing.JFrame parent, java.net.URL theURL) {

      File savedFile;
      String theFileName;
      String baseName=null;
      String tryAgainURL=null;

        theFileName = theURL.getFile();
        baseName = theURL.toString();
        System.out.println("Downloading "+baseName);
        baseName = baseName.substring(0,baseName.indexOf(theFileName));
//        System.out.println("Downloading "+baseName);

        String theQuery = theURL.getQuery();
//        if(theQuery!=null) System.out.println("theQuery: "+theQuery);
        theFileName = (theQuery!=null) ? theQuery : theURL.getFile();
        int lastEquals = theFileName.lastIndexOf("=");
        if(lastEquals!=-1) theFileName = theFileName.substring(lastEquals+1);
        int lastSlash = theFileName.lastIndexOf("/");
        if(lastSlash!=-1) theFileName = theFileName.substring(lastSlash+1);

        if(!theFileName.endsWith(".ps")) theFileName+=".ps";
        savedFile = new File(PreferencesInterface.getInstance().getPreference(PreferencesInterface.SCRATCH_DIR),theFileName);
// execute query here
       try {
        downloadURL(savedFile,theURL,theFileName);
        }catch(Exception e1) {
            String eString = e1.toString();
            int nprot = eString.indexOf("no protocol: ");
            if(nprot==-1) {
                   System.out.println("Error downloading from URL: "+eString);
                   return;
                   }
            String theSubURL = eString.substring(nprot+13);
            tryAgainURL=baseName+theSubURL;
        }
     try {
     if(tryAgainURL!=null) {
        System.out.println("Invalid URL - maybe relative pathname - reconstructing new URL from relative pathname...");
        theURL = new java.net.URL(tryAgainURL);
        theFileName = theURL.getFile();
        lastSlash = theFileName.lastIndexOf("/");
        if(lastSlash!=-1) theFileName = theFileName.substring(lastSlash+1);
        int gzIndex = theFileName.indexOf(".gz");
        if(gzIndex!=-1) theFileName=theFileName.substring(0,gzIndex);
        savedFile = new File(PreferencesInterface.getInstance().getPreference(PreferencesInterface.SCRATCH_DIR),theFileName);
          

        downloadURL(savedFile,theURL,theFileName);
        }
      }catch(Exception e2) {
         System.out.println("Exception downloading from relative pathname in URL: "+e2.toString());
         return;
         }
//     theFrame.psFileList.addElement(savedFile);   //save it for deletion when application closes


     String psExecutable = PreferencesInterface.getInstance().getPreference(PreferencesInterface.PSVIEWER);
     if(psExecutable.equals("Not defined")) {
                   Preference preference = new Preference(parent,PreferencesInterface.PSVIEWER);
                   psExecutable = PreferencesInterface.getInstance().getPreference(PreferencesInterface.PSVIEWER);
                   }
     if(psExecutable.equals("Not defined")) return; // fluffed the second chance!         

     try {
         File psExec = new File(psExecutable);
         if(!psExec.exists()) return;
         String command = psExecutable+" "+savedFile.getAbsolutePath();
         System.out.println(command);
         Process p = Runtime.getRuntime().exec( command );
     } catch(Exception e2) {System.out.println("Exception invoking ps viewer: "+e2.toString());}
   }
//********************
      public static void downloadURL(File savedFile, java.net.URL theURL, String theFileName) throws Exception {
      InputStream in;              
      byte[] buffer = new byte[128000]; // max size is 128kByte per image (I think!)
      FileOutputStream fos;
      int nByteCount=0;

       fos = new FileOutputStream(savedFile);       // open the file output stream
       in  =  theURL.openStream();
// read the data - will stop when reach end of stream or error, hopefully!
//       System.out.println("Downloading postscript file "+theFileName+" Please be patient.");
       for(int nbytes=in.read(buffer); nbytes>0; nbytes=in.read(buffer)){
           nByteCount += nbytes;
           fos.write(buffer,0,nbytes);
            }
       fos.close();
       in.close();
       System.out.println("Wrote "+nByteCount+" bytes to "+savedFile.getAbsolutePath());
       guiUtilities.HTMLViewer.getInstance().addImageFile(savedFile.getAbsolutePath());
} 
//************************************************
       public static Hashtable getSignoffDataTestNo(String sn, String location, String signoffType) throws Exception {
         Hashtable thisHash = new Hashtable();
         StringBuffer sqlStat = new StringBuffer("SELECT test_no FROM tests");
         sqlStat.append(" WHERE ser_no="+sn+" AND run_no='"+signoffType+"' AND test_name='bmMOD_RData'");
         if(!location.equals("Any")) sqlStat.append(" AND locn_name='"+location+"'");
         sqlStat.append(" ORDER BY test_date DESC, test_no DESC");
         Statement statement = SCTDBInterface.getInstance().connection.createStatement();
         ResultSet resultSet = statement.executeQuery(sqlStat.toString());
         for(boolean n = resultSet.next() ; n==true ; n=resultSet.next() ){
            if(thisHash.containsKey(location)) continue;
            thisHash.put(location,resultSet.getString(1));
            break;
            }
//      System.out.println("Retrieved "+recordCount+" names in total.");
      statement.close();
      return thisHash;
      }
//************************************************
       public static Hashtable getSignoffDataTestNo(String sn, String signoffType) throws Exception {
// return vector of test numbers
         Hashtable locationHash = new Hashtable();
         StringBuffer sqlStat = new StringBuffer("SELECT locn_name,test_no FROM tests");
         sqlStat.append(" WHERE ser_no="+sn+" AND run_no='"+signoffType+"' AND test_name='bmMOD_RData'");
         sqlStat.append(" ORDER BY test_date DESC, test_no DESC");
         Statement statement = SCTDBInterface.getInstance().connection.createStatement();
         ResultSet resultSet = statement.executeQuery(sqlStat.toString());
         for(boolean n = resultSet.next() ; n==true ; n=resultSet.next() ){
            String location = resultSet.getString(1);
            String testno = resultSet.getString(2);
            if(locationHash.containsKey(location)) continue;
            locationHash.put(location,testno);
            if(signoffType.equals("FINALSIGNOFF")) break;
            }
//      System.out.println("Retrieved "+recordCount+" names in total.");
      statement.close();
      return locationHash;
      }
//************************************************
       public static Vector getSignoffDataTestNos(String sn1, String sn2, String currLocn, String institute, String signoffType, boolean latestOnly) throws Exception {
// return vector of test numbers
         Set snHash = new HashSet();
         Vector snList = new Vector();
         StringBuffer sqlStat = new StringBuffer("SELECT ser_no, test_no, locn_name, run_no FROM tests");
 //        if(!currLocn.equals("Anywhere")) sqlStat.append(",items");
         sqlStat.append(" WHERE ser_no>="+sn1+" AND ser_no<="+sn2+" AND (run_no='SIGNOFF' OR run_no='FINALSIGNOFF') AND test_name='bmMOD_RData'");
         if(!institute.equals("Anywhere")) sqlStat.append(" AND locn_name='"+institute+"'");
         if(!currLocn.equals("Anywhere")) sqlStat.append(" AND tests.ser_no IN (SELECT items.ser_no FROM items WHERE items.locn_name='"+currLocn+"')");
         String ordering = latestOnly ? "DESC" : "";
         sqlStat.append(" ORDER BY ser_no, test_date "+ordering+", test_no "+ordering);
//         System.out.println(sqlStat.toString());
         Statement statement = SCTDBInterface.getInstance().connection.createStatement();
         ResultSet resultSet = statement.executeQuery(sqlStat.toString());
         int recordCount=0;
         for(boolean n = resultSet.next() ; n==true ; n=resultSet.next() ){
            String sn = resultSet.getString(1);
            String testno = resultSet.getString(2);
            String locn = resultSet.getString(3);
            String runno = resultSet.getString(4);
            boolean isUKUpload = (locn.equals("Cambridge") || locn.equals("RAL") || locn.equals("Birmingham"));
            if(!isUKUpload && !signoffType.equals(runno)) continue;   // non-UK uploads both SIGNOFF and FINALSIGNOFF
            if(isUKUpload && runno.equals("SIGNOFF")) continue; // UK uploads FINALSIGNOFF only
            if(latestOnly && snHash.contains(sn)) continue;
            snList.addElement(testno);
            snHash.add(sn);
            recordCount++;
            if(recordCount%100==0) System.out.println("Retrieved "+recordCount+" tests so far...");
            }
      System.out.println("Retrieved "+recordCount+" tests in total, now downloading and extracting raw data");
      statement.close();
      return snList;
      }
//*************************************************
    public static String getUploaderInfo(String testno) throws Exception {
         StringBuffer sqlStat = new StringBuffer("SELECT locn_name,test_date,initls FROM tests");
         sqlStat.append(" WHERE test_no="+testno);
         Statement statement = SCTDBInterface.getInstance().connection.createStatement();
         ResultSet resultSet = statement.executeQuery(sqlStat.toString());
         String returnString=null;
         for(boolean n = resultSet.next() ; n==true ; n=resultSet.next() ){
            String location = resultSet.getString(1);
            String testdate = guiUtilities.DaveUtils.extractDate(resultSet.getString(2));
            String initials = resultSet.getString(3);
            returnString = "Uploaded by "+initials+" from "+location+" on "+testdate;
            }
      statement.close();
      return returnString;
      }
//**********************************************************
  public static Hashtable testComments(String testno_subQuery) throws Exception {
     return testComments(testno_subQuery,false);
     }
   public static Hashtable testComments(String testno_subQuery, boolean isHTML) throws Exception {
      String nl = isHTML ? "<br>" : " ";
      Hashtable commentHash = new Hashtable();
      StringBuffer sqlStat = new StringBuffer("SELECT test_no,cmnt_text FROM test_cmnts WHERE test_no IN (SELECT tests.test_no FROM tests ");
      sqlStat.append(testno_subQuery+")");
//      System.out.println(sqlStat.toString());
     Statement statement = SCTDBInterface.getInstance().connection.createStatement();
     ResultSet resultSet = statement.executeQuery(sqlStat.toString());
     int ncomments=0;
     for(boolean n = resultSet.next() ; n==true ; n=resultSet.next() ){
          ncomments++;
          if(ncomments%100 ==0) System.out.println("Retrieved "+ncomments+" comments so far...");
          String testno = resultSet.getString(1);
          String comment = resultSet.getString(2);
          // beware of more than one comment in a given testno !
          String newComment = (commentHash.containsKey(testno)) ? (String)commentHash.get(testno)+nl+comment : comment;
          commentHash.put(resultSet.getString(1),newComment);
            }
      statement.close();
      return commentHash;
      }




   public static Hashtable testImages(String testno_subQuery) throws Exception {
// has contains a vector, and the vector contains title, then url, then title, then url etc
      Hashtable testImageHash = new Hashtable();
//      System.out.println("Checking for test images...");
      StringBuffer sqlStat = new StringBuffer("SELECT test_no,title,name FROM test_images WHERE test_no IN (SELECT tests.test_no FROM tests ");      
      sqlStat.append(testno_subQuery+")");

     Statement statement = SCTDBInterface.getInstance().connection.createStatement();
     ResultSet resultSet = statement.executeQuery(sqlStat.toString());
     int ncomments=0;
     Vector imageList;
     for(boolean n = resultSet.next() ; n==true ; n=resultSet.next() ){
          ncomments++;
          if(ncomments%100 ==0) System.out.println("Retrieved "+ncomments+" test images so far...");
          String testno = resultSet.getString(1);
          String title = resultSet.getString(2);
          String name = resultSet.getString(3);
          // beware of more than one image in a given testno !
          if(testImageHash.containsKey(testno)) imageList = (Vector)testImageHash.get(testno);
          else imageList = new Vector();
          imageList.addElement(title);
          imageList.addElement(name);
          testImageHash.put(testno,imageList);
            }
//      System.out.println("Retrieved "+ncomments+" test images in total.");
      statement.close();
      return testImageHash;
      }

//***********************************************************************************************
   public static void saveImage(String testno, String fileName, File theFile) throws Exception {
      InputStream in;   
      FileOutputStream fos = new FileOutputStream(theFile);               
      byte[] buffer = new byte[128000]; // max size is 128kByte per image (I think!)

     String sqlQuery = "SELECT data FROM test_images WHERE test_no="+testno+" AND name='"+fileName+"'";
     Statement statement = SCTDBInterface.getInstance().connection.createStatement();
     ResultSet resultSet = statement.executeQuery(sqlQuery);

// execute query here
      System.out.println("Retrieving "+fileName+" ...");
     int nByteCount=0;

     for(boolean n = resultSet.next() ; n==true ; n=resultSet.next() ){
         in  = resultSet.getBinaryStream(1);           // open the data stream

// read the data - will stop when reach end of stream or error, hopefully!
         for(int nbytes=in.read(buffer); nbytes>0; nbytes=in.read(buffer)){

            // System.out.print("Nbytes read : " + nbytes+". ");
            nByteCount += nbytes;
            fos.write(buffer,0,nbytes);  // write the data to the file output
            }
         fos.close();  // close the file output
         System.out.println("Saved image "+fileName+" with size "+nByteCount+" bytes...");
         guiUtilities.HTMLViewer.getInstance().addImageFile(theFile.getAbsolutePath());

         }
     statement.close();
     return;
}
// *****************************************************************************************************************
    public static Vector getItemVector(String serialNo) throws Exception {
      Vector itemList = new Vector();
      StringBuffer sqlStat = new StringBuffer("SELECT ");
      sqlStat.append("mfr,mfr_ser_no,ctype,locn_name,assembled,trashed FROM items WHERE ser_no="+serialNo);
      sqlStat.append(" ORDER BY ser_no");

     Statement statement = SCTDBInterface.getInstance().connection.createStatement();
//      System.out.println("Interrogating items table ...");
      ResultSet resultSet = statement.executeQuery(sqlStat.toString());
      for(boolean n = resultSet.next() ; n==true ; n=resultSet.next() ){
          itemList.addElement(resultSet.getString(1));   // mfr name
          itemList.addElement(resultSet.getString(2));   // mfr serial no
          itemList.addElement(resultSet.getString(3));   // type
          itemList.addElement(resultSet.getString(4));   // location name
          itemList.addElement(resultSet.getString(5));   // assembled flag
          itemList.addElement(resultSet.getString(6));   // trashed flag
            }
      statement.close();
      return itemList;
      }

// ******************************************************************************************************
    public static Vector getVisualTestList(String ser_no1, String ser_no2, String location, String currLocn,String orderString) throws Exception {
     StringBuffer sqlStat = new StringBuffer("SELECT ");
     sqlStat.append("tests.test_no,tests.ser_no,tests.test_date,tests.locn_name,tests.problem,tests.pass");
     sqlStat.append(" FROM tests");
     sqlStat.append(" WHERE tests.ser_no >= "+ser_no1+" AND tests.ser_no <= "+ser_no2);
     if(!location.equals("Anywhere")) sqlStat.append(" AND tests.locn_name='"+location+"'");
     if(!currLocn.equals("Anywhere")) sqlStat.append(" AND tests.ser_no IN (SELECT items.ser_no FROM items WHERE items.locn_name='"+currLocn+"')");
     sqlStat.append(" AND tests.test_name='Visual_Inspection'");
     sqlStat.append(" ORDER BY "+orderString);
     return createVisualTestTable(sqlStat.toString());
     }
// ******************************************************************************************************
    public static Vector getVisualTestList(String ser_no1, String ser_no2, String location, String currLocn) throws Exception {
      return getVisualTestList(ser_no1,ser_no2,location,currLocn,"tests.ser_no,tests.test_date,tests.test_no");
     }
//***************************************************************8
    public static Vector createVisualTestTable(String theQuery) throws Exception {
      Hashtable testnoHash = new Hashtable();
      Vector testnoList = new Vector();
      Vector itemList = new Vector();
      Vector theLine = new Vector();
      String snKey="SNKEY";
      String dateKey="DATEKEY";
      String locationKey="LOCATIONKEY";
      String flagKey="FLAGKEY";

     Statement statement = SCTDBInterface.getInstance().connection.createStatement();

     System.out.println("Interrogating tests and tstdetivs tables ...");
     ResultSet resultSet = statement.executeQuery(theQuery);
     int recordCount=0;
     testNumberList = new Vector();
     for(boolean n = resultSet.next() ; n==true ; n=resultSet.next() ){
          Hashtable thisHash = new Hashtable();
          recordCount++;
          if(recordCount%100 == 0) System.out.println("Retrieved "+recordCount+" records so far...");
          String testno = resultSet.getString(1);
          thisHash.put(snKey,resultSet.getString(2));
          String date = resultSet.getString(3);
          thisHash.put(dateKey,guiUtilities.DaveUtils.extractDate(date));
          thisHash.put(locationKey,resultSet.getString(4));
          String problemFlag = resultSet.getString(5);
          String passFlag = resultSet.getString(6);
          String statusFlag; 
          if(passFlag.equals("YES")) {
             statusFlag = (problemFlag.equals("YES")) ? "PROBLEM" : "Ok";
             }
          else statusFlag = "FAIL";
          thisHash.put(flagKey,statusFlag);
          testnoHash.put(testno,thisHash);   
          testnoList.addElement(testno);  

            }
      System.out.println("Retrieved "+recordCount+" records in total.");
      statement.close();
      if(recordCount==0) return itemList;

// now get the comments, if any
      String subQuery = theQuery;
      subQuery = subQuery.substring(subQuery.indexOf("WHERE"),subQuery.indexOf("ORDER")-1);
      Hashtable commentsHash = testComments(subQuery);
      Hashtable testImagesHash = testImages(subQuery);

      theLine.addElement("Serial Number");
      theLine.addElement("Date");
      theLine.addElement("Location");
      theLine.addElement("Status");
      theLine.addElement("Remarks");
      theLine.addElement("Pictures");
      itemList.addElement(theLine);

      for(int i=0;i<testnoList.size();i++) {
            theLine = new Vector();
            String testno = (String)testnoList.elementAt(i); // this contains the testno's, ordered by serial no
            Hashtable tempHash = (Hashtable)testnoHash.get(testno);
            theLine.addElement(tempHash.get(snKey));                                      // first column is serial number

            theLine.addElement(tempHash.get(dateKey));                                    // 2nd column is the date
            theLine.addElement(tempHash.get(locationKey));              
            theLine.addElement(tempHash.get(flagKey));
            if(commentsHash.containsKey(testno)) theLine.addElement(commentsHash.get(testno));
            else theLine.addElement("");
            if(testImagesHash.containsKey(testno)) {
               Vector imageList = (Vector)testImagesHash.get(testno);

               for(int j=0;j<imageList.size()/2; j++) {
                   if(j==0) {

                              theLine.addElement((String)imageList.elementAt(0));
                              testNumberList.addElement(testno);
                              itemList.addElement(theLine);
                              }
                   else {

                              theLine = new Vector();
                              theLine.addElement(tempHash.get(snKey));
                              theLine.addElement("  ''");
                              theLine.addElement("  ''");
                              theLine.addElement("  ''");
                              theLine.addElement("  ''");
                              theLine.addElement((String)imageList.elementAt(2*j));  
                              testNumberList.addElement(testno);
                              itemList.addElement(theLine);                     
                          }

                      }
            }
            else {theLine.addElement(""); testNumberList.addElement(testno);itemList.addElement(theLine);}
            }
      return itemList;


      }
//
//***********************************************************************************************
   public static Vector getImages(String testno,String headerTitle) throws Exception {
      InputStream in;   
 //     FileOutputStream fos;               
      byte[] buffer = new byte[128000]; // max size is 128kByte per image (I think!)

     Vector imageList = new Vector();

     String sqlQuery = "SELECT data,title,name FROM test_images WHERE test_no="+testno;
     Statement statement = SCTDBInterface.getInstance().connection.createStatement();
     ResultSet resultSet = statement.executeQuery(sqlQuery);

// execute query here
      System.out.println("Interrogating test_images table for images ...");
     int nByteCount=0;
     int noImages=0;

     for(boolean n = resultSet.next() ; n==true ; n=resultSet.next() ){
         noImages++;
         in  = resultSet.getBinaryStream(1);           // open the data stream

// read the data - will stop when reach end of stream or error, hopefully!
         for(int nbytes=in.read(buffer); nbytes>0; nbytes=in.read(buffer)){
            nByteCount += nbytes;
            }
         javax.swing.ImageIcon thisImage;
         try {
           thisImage = new javax.swing.ImageIcon(buffer);
         } catch (Exception e) {thisImage=null;} // just in case 
         String title = resultSet.getString(2);
         String name = resultSet.getString(3);
         System.out.println("Retrieved image "+name+" with size "+nByteCount+" bytes...");
         imageList.addElement(new guiUtilities.Photo(thisImage,title,name,headerTitle,testno));
         }
     System.out.println("Retrieved "+noImages+" images in total for test number "+testno);
     statement.close();
//     storedImages.put(testno,imageList); // store the image list vector, in case we want to look again
     return imageList;
}

//***************************************************************************************************
 public static Map getRawDataLists(String sn1, String sn2, String location, int testType, int temperature, String runNo, int testRequest) throws Exception {
      Map m = new HashMap();
      StringBuffer sqlStat = new StringBuffer("SELECT tests.test_no,tests.ser_no,tests.test_date,tests.locn_name,");
      if(testType>=0) sqlStat.append("test_rawdata.filename,");
      sqlStat.append("SCT_TSTDCSINFO.T0 FROM tests,");
      if(testType>=0) sqlStat.append("test_rawdata,");
      sqlStat.append("sct_tstdaqinfo,SCT_TSTDCSINFO ");

      if(testRequest>=0) sqlStat.append("WHERE tests.test_name='"+sctdaqDBTestNames[testRequest]+"'");
      else {
        switch(testType) {
         case SCTDBInfo.SCTDB_TEST_TIMEWALK: 
// xml data
      sqlStat.append(" WHERE ( (tests.test_name = 'HybTWalk' AND tests.locn_name !='Oxford') OR (tests.test_name = 'HybNoise' AND tests.locn_name = 'Oxford'))");
          break;
         default:
          sqlStat.append("WHERE tests.test_name='"+sctdaqDBTestNames[testType]+"'");
          }
       }
     sqlStat.append(" AND tests.ser_no >= "+sn1+" AND tests.ser_no <= "+sn2);
     if(runNo!=null) sqlStat.append(" AND tests.run_no LIKE '"+runNo+"'");
     if(testType>=0)sqlStat.append(" AND tests.test_no=test_rawdata.test_no");
     sqlStat.append(" AND SCT_TSTDCSINFO.test_no=tests.test_no");
     if(!location.equals("Anywhere")) sqlStat.append(" AND tests.locn_name='"+location+"'");
     sqlStat.append(" AND sct_tstdaqinfo.version NOT LIKE 'SctRodDaq%' AND sct_tstdaqinfo.test_no=tests.test_no"); // REQUIRE sctdaq data only, not SctRodDaq
     switch(temperature) {
       case 1:
         sqlStat.append(" AND SCT_TSTDCSINFO.T0<=10");
         break;
       case 2:
         sqlStat.append(" AND SCT_TSTDCSINFO.T0>10");
         break;
       default:
       }
     sqlStat.append(" ORDER BY tests.ser_no,tests.test_date,tests.test_no");

//     System.out.println(sqlStat.toString());
     Statement statement = SCTDBInterface.getInstance().connection.createStatement();
     ResultSet resultSet = statement.executeQuery(sqlStat.toString());

     for(boolean n = resultSet.next() ; n==true ; n=resultSet.next() ){

           SCTDBTestInfoHolder testInfo = new SCTDBTestInfoHolder();
           testInfo.put(SCTDBTestInfoHolder.TESTNUMBER,resultSet.getString(1));
           Long sn = new Long(resultSet.getLong(2));
           testInfo.put(SCTDBTestInfoHolder.SN,sn.toString());
           testInfo.put(SCTDBTestInfoHolder.DATE,guiUtilities.DaveUtils.extractDate(resultSet.getString(3)));
           testInfo.put(SCTDBTestInfoHolder.LOCATION,resultSet.getString(4));
           int lastIndex = (testType>=0) ? 6 : 5;
           testInfo.put(SCTDBTestInfoHolder.TEMPERATURE,resultSet.getString(lastIndex));
           m.put(sn,testInfo);
       }
     statement.close();
     return m;
     }
   public static Vector getRawData(String testno) throws Exception {
     return getRawData(testno,PreferencesInterface.getInstance().getPreference(PreferencesInterface.SCRATCH_DIR));
     }

// ****************************************************************************************************************
   public static Vector getRawData(String testno, String saveDir) throws Exception {
// get the raw data - if its a zip file, save it to disk with same filename
// otherwise just download as a string
      Vector returnData = new Vector();

      // first get filename of the raw data file of this testno

     String sqlQuery = "SELECT filename from test_rawdata WHERE test_no="+testno;
     Statement statement = SCTDBInterface.getInstance().connection.createStatement();
     ResultSet resultSet = statement.executeQuery(sqlQuery);
     String thisFname = null;
     for(boolean n = resultSet.next() ; n==true ; n=resultSet.next() ){
          thisFname = resultSet.getString(1);
          }
     statement.close();
     if(thisFname==null) {
          return returnData;
          }
// now download that file
     File savedFile = new File(saveDir,thisFname);
     if(downloadFile(testno,savedFile)==0) return returnData;
// now read in the downloaded file - firat assume it is zipped...
     try {
       returnData = unZipFile(savedFile);
       }
     catch(Exception ee) {
// not zipped, so assume its just text
       returnData = getFileContent(savedFile);
       File tempFile = new File(thisFname); // get just the last part, not a pathname
       returnData.insertElementAt((String)tempFile.getName(),0);
       }
     boolean deleted = savedFile.delete(); // delete the downloaded file
// check if its an .xml file, if so, assume it is an sctdaq xml config file
// in which case, we want to lookup and susbtitute the calibrate correction factors
     returnData = insertCalFactors(returnData);
// index0 : filename
// index1 : content as a String
// ..etc for index2 and 3 etc
     return returnData;
     }
//***********************************************************************8
public static Vector getFileContent(File theFile) throws Exception {
    // get file content as a String
    Vector returnData = new Vector();
    BufferedReader in = new BufferedReader(new FileReader(theFile));
    String line;
    StringBuffer stringBuffer = new StringBuffer();
    boolean firstLine=true;
    while((line=in.readLine())!=null) {
      if(!firstLine) stringBuffer.append("\n");
      stringBuffer.append(line);
      firstLine=false;
      }
    in.close();
    returnData.addElement(stringBuffer.toString());
    return returnData;
    }

//********************************************
public static int downloadFile(String testno, File savedFile) throws Exception {

      InputStream in;         
      String theData = "";      
      byte[] buffer = new byte[128000]; // max size is 128kByte per image (I think!)
    FileOutputStream fos;
// execute query here
     String sqlQuery = "SELECT raw_data FROM test_rawdata WHERE test_no="+testno; 
     Statement statement = SCTDBInterface.getInstance().connection.createStatement();
     ResultSet resultSet = statement.executeQuery(sqlQuery);

     int nByteCount=0;
     fos = new FileOutputStream(savedFile);       // open the file output stream
     for(boolean n = resultSet.next() ; n==true ; n=resultSet.next() ){
         in  = resultSet.getBinaryStream(1);           // open the data stream

// read the data - will stop when reach end of stream or error, hopefully!
         for(int nbytes=in.read(buffer); nbytes>0; nbytes=in.read(buffer)){
           nByteCount += nbytes;
           fos.write(buffer,0,nbytes);
            }
         }
     fos.close();
     statement.close();
     if(nByteCount==0) System.out.println("No raw data available for test "+testno);
//     else System.out.println("Downloaded "+savedFile.getName()+", size "+nByteCount+" bytes.");
     return nByteCount;
}
//*************************************************************************
public static Vector unZipFile(File savedFile) throws Exception{
      Vector returnData = new Vector();
      byte[] buffer = new byte[128000]; // max size is 128kByte per image (I think!)

// now extract the zip file...
     final int BUFFER=2048;
     ZipFile zFile = new ZipFile(savedFile);
     FileInputStream fis = new FileInputStream(savedFile);
     ZipInputStream zis = new ZipInputStream(new BufferedInputStream(fis));
     ZipEntry entry;
     while((entry = zis.getNextEntry()) != null) {
         returnData.addElement((String)entry.getName());   // first the data filename
         int count;
         byte[] data = new byte[BUFFER];
         StringBuffer thisData = new StringBuffer();
         while((count=zis.read(data,0,BUFFER)) != -1) {
            thisData.append(new String(data,0,count)); 
            }
         returnData.addElement((String)thisData.toString());    // then the filename content as a string
        }
     zis.close();
     return returnData;
}
public static Vector insertCalFactors(Vector rawdata) throws Exception {
    String lineTerminator = "\\s*\\n";
    Pattern modulePattern = Pattern.compile("<module>");
    Pattern locationPattern = Pattern.compile(".*<location>(.*)</location>.*");
    Pattern snPattern = Pattern.compile("\\s*<sn>.*(\\d{14}).*</sn>");
    Pattern chipIDPattern = Pattern.compile("\\s*<chip id=\"(\\d{2})\".*");
    Pattern cFactorPattern = Pattern.compile("(\\s*)<c_factor>.*</c_factor>");
    Pattern runNoPattern = Pattern.compile(".*<run>(\\d+)</run><scan>(\\d+)</scan>.*");
    Pattern rcFitPattern = Pattern.compile("(.*)<rc_function.*</rc_function>");
    String moduleSerialNo = null;
    String location=null;
    String thisChipPosition=null;
    Hashtable chipPositionHash = new Hashtable();
    Hashtable rcFitHash = null;

    boolean xmlfile=false;
    for(int i=0;i<rawdata.size();i++) {

        if(i%2==0) {
            if (((String)rawdata.elementAt(i)).indexOf(".xml")!=-1) xmlfile=true;
            else xmlfile=false;
            continue;
            }

        if(!xmlfile) continue;
        String[] lines = ((String)rawdata.elementAt(i)).split(lineTerminator);

        Matcher matcher = modulePattern.matcher(lines[0]);
        if(!matcher.matches()) return rawdata;     // first line of xml file is expected to be <module>

        StringBuffer newString = new StringBuffer("<module>");
        for(int line=1;line<lines.length;line++) {
            String thisLine = lines[line];
//            System.out.println("Checking line "+line+": "+thisLine);
            matcher = snPattern.matcher(thisLine);
            if(matcher.matches()) {
                 moduleSerialNo = thisLine.substring(matcher.start(1),matcher.end(1));
                 chipPositionHash = getChipPositionHash(moduleSerialNo);               
                 }
            matcher = locationPattern.matcher(thisLine);
            if(matcher.matches()) location = thisLine.substring(matcher.start(1),matcher.end(1));

            matcher = runNoPattern.matcher(thisLine);
            if(matcher.matches()) {
                   rcFitHash = getRCFits(moduleSerialNo,location,thisLine.substring(matcher.start(1),matcher.end(1)),thisLine.substring(matcher.start(2),matcher.end(2)));
                   if(rcFitHash.size()!=12) System.err.println("Failed to extract RC fit parameters for module config file");
                   }

            matcher = rcFitPattern.matcher(thisLine);
            if(matcher.matches() && thisChipPosition!=null && rcFitHash!=null) {
              String padding = thisLine.substring(matcher.start(1),matcher.end(1));
              if(rcFitHash.containsKey(thisChipPosition)) {
                 lines[line] = padding+(String)rcFitHash.get(thisChipPosition);
                 }
              }

            matcher = chipIDPattern.matcher(thisLine);
            if(matcher.matches()) {
                 thisChipPosition = thisLine.substring(matcher.start(1),matcher.end(1));
//                 System.out.println("Found chip position "+thisChipPosition);
                 }
            matcher = cFactorPattern.matcher(thisLine);
            if(matcher.matches() && thisChipPosition!=null) {
                 String spaces = thisLine.substring(matcher.start(1),matcher.end(1));
                 if(chipPositionHash.containsKey(thisChipPosition)) lines[line] = spaces+"<c_factor>"+(String)chipPositionHash.get(thisChipPosition)+"</c_factor>";
                 else System.err.println("Cal Correction factors are not available for "+moduleSerialNo);
                }
            newString.append("\n"+lines[line]);

            }
         rawdata.setElementAt((String)newString.toString(),i);

        }

   return rawdata;
}
public static Hashtable getChipPositionHash(String moduleSN) throws Exception {
   Pattern lotNoPattern = Pattern.compile("(Z\\d+-W\\d+).*"); // miss out the non-LOT info, eg Z39993-W12 X1 Y2
   Hashtable thisHash = new Hashtable();
   String sn=null;
// first get the hybrid serial no
     StringBuffer sqlStat = new StringBuffer("SELECT ");
     sqlStat.append("ser_no from ASSM_ITEMS WHERE assm_ser_no="+moduleSN);
     sqlStat.append(" AND (ctype LIKE '%Hybrid%' OR ctype LIKE 'bmHASIC')");
//     System.out.println("get hybrid serial no ...");
//     System.out.println(sqlStat.toString());
     Statement statement;
     statement = SCTDBInterface.getInstance().connection.createStatement();

     ResultSet resultSet = statement.executeQuery(sqlStat.toString());

     for(boolean n = resultSet.next() ; n==true ; n=resultSet.next() ){
          sn = resultSet.getString(1);
          }
     statement.close();
     if(sn==null) return thisHash;  // no entry in assembly table - return empty hash
//     else System.out.println("Hybrid is "+sn);

// now get the chips in the assembly table
// construct the hash with position as Key and LotNumber as value
     sqlStat = new StringBuffer("SELECT ");
     sqlStat.append("ASSM_ITEMS.posn,items.mfr_ser_no from ASSM_ITEMS,items WHERE assm_ser_no="+sn);
     sqlStat.append(" AND items.ser_no=assm_items.ser_no AND assm_items.ctype = 'chABCD3T'");

//     System.out.println("now get chips ...");
//     System.out.println(sqlStat.toString());
     statement = SCTDBInterface.getInstance().connection.createStatement();
     resultSet = statement.executeQuery(sqlStat.toString());

     int chipno=0;
     for(boolean n = resultSet.next() ; n==true ; n=resultSet.next() ){
          String posn = resultSet.getString(1);
          String mfr_sn = resultSet.getString(2);
          Matcher matcher = lotNoPattern.matcher(mfr_sn);
          int thePosition = Integer.parseInt(posn);
          thePosition--;
          posn = Integer.toString(thePosition);
//          System.out.println("chipno="+chipno+", posn="+posn+", mfr_sn="+mfr_sn);
          if(matcher.matches()) {
            mfr_sn = mfr_sn.substring(matcher.start(1),matcher.end(1));
            if(posn.length()<2) posn="0"+posn;
            thisHash.put(posn,mfr_sn);
//            System.out.println("chipno="+chipno+", posn="+posn+", mfr_sn="+mfr_sn);
            }
          chipno++;
          }
      statement.close();
     if(thisHash.size()!=12) {
        System.out.println("No asic assembly data for hybrid "+sn);
        return new Hashtable();
        }
// ok, now go through the chips to get the cal correction factors
     Hashtable correctionHash = new Hashtable();
     int ifactors=0;
     for (Enumeration e = thisHash.keys() ; e.hasMoreElements() ;) {
         String thisPosn = (String)e.nextElement();
         String thisLotNo = (String)thisHash.get(thisPosn);
         if(!correctionHash.containsKey(thisLotNo)) {
              String factor = WaferUtilities.getCalCorrFactor(thisLotNo); 
              if(!factor.equals("")) correctionHash.put(thisLotNo,factor);
              else {
                  System.out.println("WARNING: CalCorr factors not available for wafer "+thisLotNo);
                  correctionHash.put(thisLotNo,"1.0");
                  }
              }
         if(correctionHash.containsKey(thisLotNo)) {
              String thisFactor = (String)correctionHash.get(thisLotNo);
              thisHash.put(thisPosn,thisFactor);
//              System.out.println("Putting "+thisFactor+" into chip position "+thisPosn);
              ifactors++;
              }
         }
      ifactors = 12 - ifactors;
      if(ifactors>0) System.out.println("WARNING: CalCorr factors not found for "+ifactors+" chips");
    
      return thisHash;    

}
   public static Hashtable getRCFits(String sn, String location, String runNo, String scanNo) throws Exception {

      Hashtable rcFitHash = new Hashtable();
      if(sn==null) {
         System.err.println("Null serialno in getRCFits");
         return rcFitHash;
         }
      if(location==null) {
         System.err.println("Null location in getRCFits");
         return rcFitHash;
         }

      int scanNumber= Integer.parseInt(scanNo);
// the run-scan of the ResponseCurve is either 10 or 12 scans before the run-scan in the module config file
// depending on whether it was uploaded with the TimeWalk or Noise test
      String rsString1 = runNo+"-"+Integer.toString(scanNumber-10);
      String rsString2 = runNo+"-"+Integer.toString(scanNumber-21);

// first get the hybrid serial no
     StringBuffer sqlStat = new StringBuffer("SELECT tests.test_no,tests.run_no");

     for(int i=0;i<12;i++) {
        for (int y=9;y<=12;y++) sqlStat.append(","+chipDBnames[i]+DBChipParameterNames[SCTDB_TEST_NPTGAIN][y]);
        }
     sqlStat.append(" FROM SCT_TSTHYBRC,tests WHERE SCT_TSTHYBRC.test_no=tests.test_no");
     sqlStat.append(" AND (tests.run_no='"+rsString1+"' OR tests.run_no='"+rsString2+"')");
     sqlStat.append(" AND tests.ser_no="+sn+" AND tests.locn_name='"+location+"'");

//     System.out.println(sqlStat.toString());

     Statement statement = SCTDBInterface.getInstance().connection.createStatement();
     ResultSet resultSet = statement.executeQuery(sqlStat.toString());

     int oKey=0;
     java.util.regex.Pattern rPattern = java.util.regex.Pattern.compile("(\\d+)-(\\d+)");
     java.util.regex.Matcher matcher;
     for(boolean n = resultSet.next() ; n==true ; n=resultSet.next() ){
          int arg=1;
          String testno = resultSet.getString(arg++);
//        System.out.println("testno="+testno);

          // want the most recent run and scan only, if by chance nboth 3PtGain and NPtGain are found
          String runno = resultSet.getString(arg++);
          matcher = rPattern.matcher(runno);
          if(matcher.matches()) {
             int run = Integer.valueOf(runno.substring(matcher.start(1),matcher.end(1))).intValue();
             int scan = Integer.valueOf(runno.substring(matcher.start(2),matcher.end(2))).intValue();
             int thisKey = run*100000 + scan;
             if(oKey>0) System.out.println("found more than one 3PtGain/NPtGain for "+sn+" from "+location+". Using latest run/scan only....");
             if(thisKey<oKey) continue;
             oKey=thisKey;
             }
          for(int chipno=0;chipno<12;chipno++) {
              String line = "<rc_function type=\""+resultSet.getString(arg++)+"\">p0 "+resultSet.getString(arg++)+" p1 "+resultSet.getString(arg++)+" p2 "+resultSet.getString(arg++)+"</rc_function>";
              String posn = Integer.toString(chipno);
              if(posn.length()<2) posn="0"+posn;
              rcFitHash.put(posn,line);
              }
          }
      statement.close();
      return rcFitHash;
      }



     


// ******************************************************************************************************************
    public static boolean confirmLocation(String location) throws Exception {
     boolean foundit=false;
     String sqlStat = new String("SELECT locn_name FROM locns WHERE locn_name='"+location+"'");
     Statement statement = SCTDBInterface.getInstance().connection.createStatement();
     ResultSet resultSet = statement.executeQuery(sqlStat);
     for(boolean n = resultSet.next() ; n==true ; n=resultSet.next() ){
           return true;
            }
      statement.close();
      return foundit;
     }
//**************************************************************
     public static String getTestString(int testIndex,String testno) {
         Hashtable snHash = new Hashtable();
         Hashtable testnoHash = new Hashtable();

         StringBuffer result = new StringBuffer();

         StringBuffer sqlStat = new StringBuffer();
            sqlStat = new StringBuffer("SELECT tests.ser_no,tests.pass,tests.problem,tests.TEST_no,tests.locn_name,tests.TEST_date,tests.run_no,tests.test_name,SCT_TSTDCSINFO.T0");
            sqlStat.append(",SCT_TSTDAQINFO.VERSION,SCT_TSTDAQINFO.TEST_TIME");
            switch(testIndex) {
                    case SCTDB_TEST_RESET:
                    case SCTDB_TEST_REDUNDANCY:
                    case SCTDB_TEST_LONGTERM:
                    case SCTDB_TEST_IV:
                        break;
                    default:
                        for(int y=0;y<chipDBnames.length;y++) {
                           for(int paramIndex=0;paramIndex<DBChipParameterNames[testIndex].length;paramIndex++) {
                                  sqlStat.append(","+chipDBnames[y]+DBChipParameterNames[testIndex][paramIndex]);
                                  }
                          }

                     }
         sqlStat.append(" FROM "+sctdaqDBTableNames[testIndex]+",tests,SCT_TSTDCSINFO,SCT_TSTDAQINFO");
         sqlStat.append(" WHERE tests.test_no = "+testno);
         sqlStat.append(" AND tests.TEST_no = "+sctdaqDBTableNames[testIndex]+".TEST_no");
         sqlStat.append(" AND tests.TEST_no = SCT_TSTDCSINFO.TEST_no");
         sqlStat.append(" AND tests.TEST_no = SCT_TSTDAQINFO.TEST_no");
         sqlStat.append(" ORDER BY tests.TEST_date DESC,tests.TEST_no DESC");

         try {

         Statement statement = SCTDBInterface.getInstance().connection.createStatement();
//         System.out.println(sqlStat.toString());
         ResultSet resultSet = statement.executeQuery(sqlStat.toString());
         for(boolean n = resultSet.next() ; n==true ; n=resultSet.next() ){
            int rc=1;

            String sn = resultSet.getString(rc++);
            result.append("SERIALNO="+sn+"\n");
            if(snHash.containsKey(sn)) continue;
            int status = (resultSet.getString(rc++).equals("YES")) ? 0 : 2;
            if(resultSet.getString(rc++).equals("YES") && status==0) status = 1;



            result.append(sctdaqNormalTestNames[testIndex]+"\n");
            switch(status) {
              case 0:
                 result.append("PASS\n");
                 break;
              case 1:
                 result.append("PROBLEM\n");
                 break;
              default:
                 result.append("FAIL\n");
                 }
            result.append("TESTNO="+resultSet.getString(rc++)+"\n");  // testno
            result.append("LOCATION="+resultSet.getString(rc++)+"\n");  //date
            result.append("DATE="+guiUtilities.DaveUtils.extractDate(resultSet.getString(rc++))+"\n");
            result.append("RUN/SCAN="+resultSet.getString(rc++)+"\n");
            result.append("TESTNAME="+resultSet.getString(rc++)+"\n");
            result.append("TEMPERATURE="+resultSet.getString(rc++)+"\n");
           result.append("SCTDAQ_VERSION="+resultSet.getString(rc++)+"\n");
           result.append("TIME="+resultSet.getString(rc++)+"\n");

            result.append("Chip\t");
            for(int i=0;i<DBChipParameterNames[testIndex].length;i++) result.append(DBChipParameterNames[testIndex][i]+"\t");
            result.append("\n");

            for(int chip=0;chip<12;chip++) {
              result.append(chipDBnames[chip].substring(0,2)+"\t");
              for(int r=0;r<DBChipParameterNames[testIndex].length;r++) {
                 if(r>0) result.append("\t");
                 result.append(resultSet.getString(rc++));
                 }
              result.append("\n");
              }

            }
        statement.close();

        sqlStat = new StringBuffer();
        sqlStat.append("SELECT defects.defect_name,defects.chan_1st,defects.chan_last");
        sqlStat.append(" FROM defects");
        sqlStat.append(" WHERE defects.TEST_no = "+testno);


        statement = SCTDBInterface.getInstance().connection.createStatement();
//         System.out.println(sqlStat.toString());
        resultSet = statement.executeQuery(sqlStat.toString());
        int ndefects=0;
        result.append("Defects:\n");
        for(boolean n = resultSet.next() ; n==true ; n=resultSet.next() ){ 
            ndefects++;
            String defectName = resultSet.getString(1);
            String chan1 = resultSet.getString(2);
            String chanLast = resultSet.getString(3);
            result.append(chan1+"-"+chanLast+" : "+defectName+"\n");
            }
        statement.close();
        result.append("defectCount="+Integer.toString(ndefects)+"\n");

        }catch(Exception e) {System.out.println("Failed to publish SCTDB Data: "+e.toString());}

        return result.toString();
}

//****************************************************************************************8
     public static void removePreviousUploads(String testname, String runno, String locn, Map itemList) throws Exception {

     StringBuffer sqlStat = new StringBuffer("SELECT tests.ser_no,test_name,run_no,test_date,sct_tstdaqinfo.test_time FROM tests,sct_tstdaqinfo");
     sqlStat.append(" WHERE tests.test_name='"+testname+"' AND tests.locn_name='"+locn+"' AND tests.run_no='"+runno+"' AND sct_tstdaqinfo.test_no=tests.test_no");
     sqlStat.append(" AND sct_tstdaqinfo.version LIKE 'SctRodDaq%'");
     Statement statement = SCTDBInterface.getInstance().connection.createStatement();
//     System.out.println(sqlStat.toString());
     ResultSet resultSet = statement.executeQuery(sqlStat.toString());
     for(boolean n = resultSet.next() ; n==true ; n=resultSet.next() ){
          StringBuffer thisSig = new StringBuffer(resultSet.getString(1));
          thisSig.append("_");
          thisSig.append(resultSet.getString(2));   // testname
          thisSig.append("_");
          thisSig.append(resultSet.getString(3));   // run_no
          thisSig.append("_");
          thisSig.append(guiUtilities.DaveUtils.extractSCTDAQDate(resultSet.getString(4)));   // date
          thisSig.append("_");
          thisSig.append(resultSet.getString(5));   // time
          String signature = thisSig.toString();
          if(itemList.containsKey(signature)) {
              System.out.println("Data for "+signature.substring(0,14)+" is already uploaded!");
              itemList.remove(signature);
              }
            }
      statement.close();
     }

//*************************************************************
     public static String getUserName(String locn) throws Exception {
     Statement statement = SCTDBInterface.getInstance().connection.createStatement();
//     System.out.println(sqlStat.toString());
     ResultSet resultSet = statement.executeQuery("SELECT username FROM locns WHERE locn_name='"+locn+"'");
     String username = null;
     for(boolean n = resultSet.next() ; n==true ; n=resultSet.next() ){
          username = resultSet.getString(1);
          }
     statement.close();
     return username;
     }
//*************************************************************
     public static String getSerialNo(String testno) throws Exception {
     Statement statement = SCTDBInterface.getInstance().connection.createStatement();
//     System.out.println(sqlStat.toString());
     ResultSet resultSet = statement.executeQuery("SELECT ser_no FROM tests WHERE test_no='"+testno+"'");
     String sn = null;
     for(boolean n = resultSet.next() ; n==true ; n=resultSet.next() ){
          sn = resultSet.getString(1);
          }
     statement.close();
     return sn;
     }
//*************************************************************
     public static String getCType(String serno) throws Exception {
     Statement statement = SCTDBInterface.getInstance().connection.createStatement();
//     System.out.println(sqlStat.toString());
     ResultSet resultSet = statement.executeQuery("SELECT ctype FROM items WHERE ser_no='"+serno+"'");
     String ctype = null;
     for(boolean n = resultSet.next() ; n==true ; n=resultSet.next() ){
          ctype = resultSet.getString(1);
          }
     statement.close();
     return ctype;
     }
//**********************************************************
    public static List getTestList(int testIndex,String locn, String testMenuName) throws Exception {
     java.util.regex.Pattern runPattern = java.util.regex.Pattern.compile("(\\d+)-(\\d+)");
     Statement statement = SCTDBInterface.getInstance().connection.createStatement();
     StringBuffer sqlStat = new StringBuffer("SELECT DISTINCT test_date,locn_name,run_no,tests.test_no FROM tests,sct_tstdaqinfo WHERE locn_name='"+locn+"' AND test_name='"+sctdaqDBTestNames[testIndex]+"'");
     sqlStat.append(" AND tests.test_no=sct_tstdaqinfo.test_no AND sct_tstdaqinfo.version LIKE 'SctRodDaq%'");
     sqlStat.append(" ORDER BY tests.test_date");
//     System.out.println(sqlStat.toString());
     ResultSet resultSet = statement.executeQuery(sqlStat.toString());
     List itemList = new ArrayList();
     SortedMap runMap = new TreeMap();
     for(boolean n = resultSet.next() ; n==true ; n=resultSet.next() ){
          List row = new ArrayList();
          row.add(guiUtilities.DaveUtils.extractDate(resultSet.getString(1)));
          row.add(testMenuName);
          row.add(resultSet.getString(2));
          String runno = resultSet.getString(3);
          java.util.regex.Matcher matcher = runPattern.matcher(runno);
          if(!matcher.matches()) {
            System.err.println("Unrecognised runno "+runno);
            continue;
             }
          String run = runno.substring(matcher.start(1),matcher.end(1));
          String scan = runno.substring(matcher.start(2),matcher.end(2));
          int key = Integer.valueOf(run).intValue()*1000000 + Integer.valueOf(scan).intValue();
          row.add(runno.substring(matcher.start(1),matcher.end(1)));
          row.add(runno.substring(matcher.start(2),matcher.end(2)));
          runMap.put(new Integer(key),row);

          }
     statement.close();
//     for(boolean n = resultSet.next() ; n==true ; n=resultSet.next() ){
//          List row = new ArrayList();
//          row.add(guiUtilities.DaveUtils.extractDate(resultSet.getString(1)));
//          row.add(testMenuName);
//        row.add(resultSet.getString(2));
//          String runno = resultSet.getString(3);
//          java.util.regex.Matcher matcher = runPattern.matcher(runno);
//          if(!matcher.matches()) {
//            System.err.println("Unrecognised runno "+runno);
//            continue;
//             }
//
//          row.add(runno.substring(matcher.start(1),matcher.end(1)));
//          row.add(runno.substring(matcher.start(2),matcher.end(2)));
//          itemList.add(row);
//
//          }
     return new ArrayList(runMap.values());
     }

//******************************************************
   public static SCTDBTestInfoHolder getTestInfo(String test_no) throws Exception {

      SCTDBTestInfoHolder testInfo= new SCTDBTestInfoHolder();

      StringBuffer genTable = new StringBuffer();
      StringBuffer sqlStat = new StringBuffer("SELECT tests.test_name,tests.ser_no,tests.test_date,tests.locn_name,items.ctype FROM tests,items WHERE tests.test_no="+test_no);
      sqlStat.append(" AND items.ser_no=tests.ser_no");

// submit query
     Statement statement = SCTDBInterface.getInstance().connection.createStatement();
     ResultSet resultSet = statement.executeQuery(sqlStat.toString());
   
     for(boolean n = resultSet.next() ; n==true ; n=resultSet.next() ){
          testInfo.put(SCTDBTestInfoHolder.TEST_NAME,resultSet.getString(1));
          testInfo.put(SCTDBTestInfoHolder.SN,resultSet.getString(2));
          testInfo.put(SCTDBTestInfoHolder.DATE,guiUtilities.DaveUtils.extractDate(resultSet.getString(3)));
          testInfo.put(SCTDBTestInfoHolder.LOCATION,resultSet.getString(4));
          testInfo.put(SCTDBTestInfoHolder.CTYPE,resultSet.getString(5));
          }
     testInfo.put(SCTDBTestInfoHolder.TESTNUMBER,test_no);
     statement.close();
     if(testInfo.isValid()) return testInfo;
     return null;
     }

// **********************************************************************************************
    public static Vector getTestHistory(String serialNo) throws Exception {
     StringBuffer sqlStat = new StringBuffer("SELECT ");
     sqlStat.append("tests.ser_no,tests.locn_name,tests.test_date,tests.test_name,tests.problem,tests.pass");
     sqlStat.append(" FROM tests");
     sqlStat.append(" WHERE tests.ser_no = "+serialNo);
     sqlStat.append(" ORDER BY tests.test_date,tests.test_no");
     

      Statement statement = SCTDBInterface.getInstance().connection.createStatement();
      ResultSet resultSet = statement.executeQuery(sqlStat.toString());
      Hashtable serNoHash = new Hashtable();
      Hashtable thisHash;
      Vector serNoList = new Vector();
      Vector theLine = new Vector();
      Vector itemList = new Vector();
      int recordCount=0;
      theLine.addElement("Serial Number");
      theLine.addElement("Test Location");
      theLine.addElement("Date");
      theLine.addElement("Test Name");
      theLine.addElement("Test Status");
      itemList.addElement(theLine);
      for(boolean n = resultSet.next() ; n==true ; n=resultSet.next() ){
          theLine = new Vector();
          theLine.addElement(resultSet.getString(1));
          theLine.addElement(resultSet.getString(2));
          theLine.addElement(guiUtilities.DaveUtils.extractDate(resultSet.getString(3)));
          theLine.addElement(resultSet.getString(4));

          String problemFlag = resultSet.getString(5);
          String passFlag = resultSet.getString(6);
          String statusFlag; 
          if(passFlag.equals("YES")) {
             statusFlag = (problemFlag.equals("YES")) ? "Problem" : "Pass";
             }
          else statusFlag="FAIL";
          theLine.addElement(statusFlag);
          itemList.addElement(theLine);
          recordCount++;
          if(recordCount%100 ==0) System.out.println("Retrieved "+recordCount+" records ...");
            }
       System.out.println("Retrieved "+recordCount+" records in total.");

       statement.close();
       return itemList;

     }

   public static Vector getShipmentHistory(String serialNo) throws Exception {
      Vector theLine = new Vector();
      Vector itemList = new Vector();
      StringBuffer sqlStat = new StringBuffer("SELECT ship_items.ser_no,ship_items.ship_no,ship.locn_name,ship.dest_locn_name,ship.ship_date,ship_items.recvd FROM ship,ship_items ");
      sqlStat.append("WHERE ship_items.ser_no="+serialNo+" AND ship_items.ship_no=ship.ship_no ");
      sqlStat.append("ORDER BY ship.ship_date,ship.ship_no"); 
 
      theLine.addElement("Serial Number");
      theLine.addElement("Shipment No");
      theLine.addElement("Sent from");
      theLine.addElement("Received By");
      theLine.addElement("Send Date");
      theLine.addElement("Received?");
      itemList.addElement(theLine);
      Statement statement = SCTDBInterface.getInstance().connection.createStatement();
      ResultSet resultSet = statement.executeQuery(sqlStat.toString());
      
      int recordCount=0;
      for(boolean n = resultSet.next() ; n==true ; n=resultSet.next() ){
          theLine = new Vector();
          theLine.addElement(resultSet.getString(1));
          theLine.addElement(resultSet.getString(2));
          theLine.addElement(resultSet.getString(3));
          theLine.addElement(resultSet.getString(4));
          theLine.addElement(guiUtilities.DaveUtils.extractDate(resultSet.getString(5)));
          theLine.addElement(new Boolean(resultSet.getString(6).equals("YES")));
          itemList.addElement(theLine);

          recordCount++;
          if(recordCount%100 ==0) System.out.println("Retrieved "+recordCount+" shipment records ...");
            }
       System.out.println("Retrieved "+recordCount+" shipment records in total.");
       statement.close();
       return itemList;
       }


    public static List getRodList(String sn) throws Exception {

      StringBuffer sqlStat = new StringBuffer("SELECT ");
      sqlStat.append(" tests.test_no,tests.ser_no,tests.locn_name,tests.test_date,sct_tstdcsinfo.t0 FROM tests,test_rawdata,sct_tstdcsinfo");
      sqlStat.append(" WHERE tests.ser_no="+sn);
      sqlStat.append(" AND ( (tests.test_name = 'HybTWalk' AND tests.locn_name !='Oxford') OR (tests.test_name = 'HybNoise' AND tests.locn_name = 'Oxford'))");

      sqlStat.append(" AND tests.test_no = sct_tstdcsinfo.test_no AND tests.test_no=test_rawdata.test_no");
      sqlStat.append(" ORDER BY tests.ser_no,tests.test_date,tests.test_no");

     List itemList = new ArrayList();

     Statement statement = SCTDBInterface.getInstance().connection.createStatement();
     ResultSet resultSet = statement.executeQuery(sqlStat.toString());
     testNumberList = new Vector();
     for(boolean n = resultSet.next() ; n==true ; n=resultSet.next() ){
          testNumberList.addElement(resultSet.getString(1));
          List thisLine = new ArrayList();
          thisLine.add(resultSet.getString(2));  //sn
          thisLine.add(resultSet.getString(3)); // location
          thisLine.add(guiUtilities.DaveUtils.extractDate(resultSet.getString(4)));
          thisLine.add(resultSet.getString(5));
          itemList.add(thisLine);
         }

      statement.close();

      return itemList;

      }

        
     


}

---