Main Page | Packages | Class Hierarchy | Alphabetical List | Class List | File List | Class Members | Related Pages

GeneralUtilities.java

00001 package ProdDatabase;
00002 import java.util.*;
00003 import java.sql.*;
00004 import java.util.zip.*;
00005 import java.util.regex.*;
00006 import java.io.*;
00007 import Preferences.*;
00008 
00009 public class GeneralUtilities implements SCTDBInfo {
00010     public static Hashtable generalHash;
00011     public static Vector testNumberList;
00012 
00013 // *****************************************************************************************************************
00014     public static void itemExists(String sn1,String sn2,boolean resetHash) throws Exception {
00015       if(resetHash || generalHash==null) generalHash = new Hashtable();
00016       Vector itemList = new Vector();
00017       StringBuffer sqlStat = new StringBuffer("SELECT ");
00018       sqlStat.append("ser_no,ctype,locn_name FROM items WHERE ser_no >="+sn1+" AND ser_no <="+sn2);
00019       sqlStat.append(" ORDER BY ser_no");
00020 //      System.out.println(sqlStat.toString());
00021 
00022       SCTDBInterface db = SCTDBInterface.getInstance();
00023 
00024       Statement statement = db.connection.createStatement();
00025       ResultSet resultSet = statement.executeQuery(sqlStat.toString());
00026       int recordCount=0;
00027       for(boolean n = resultSet.next() ; n==true ; n=resultSet.next() ){
00028          recordCount++;
00029          String serno = resultSet.getString(1);
00030           String ctype = resultSet.getString(2);
00031           String location = resultSet.getString(3);
00032           generalHash.put((String)serno,(String)ctype);
00033           System.out.println(serno+"("+ctype+") already exists, located at "+location);
00034           }
00035       statement.close();
00036       return;
00037       }
00038   public static boolean itemExists(String sn1) throws Exception {
00039        return itemExists(sn1,true);
00040        }
00041 //************************************************************
00042   public static boolean itemExists(String sn1, boolean showDialog) throws Exception {
00043      boolean itExists=false;
00044      String cType=null;
00045      StringBuffer sqlStat = new StringBuffer("SELECT ctype FROM items WHERE ser_no="+sn1);
00046       Statement statement = SCTDBInterface.getInstance().connection.createStatement();
00047       ResultSet resultSet = statement.executeQuery(sqlStat.toString());
00048       for(boolean n = resultSet.next() ; n==true ; n=resultSet.next() ){
00049          cType=resultSet.getString(1);
00050          if(cType.indexOf("Module")!=-1) itExists=true;
00051          }
00052       statement.close();
00053       if(!itExists && showDialog) {
00054          if(cType==null) javax.swing.JOptionPane.showMessageDialog(null,sn1+" does not exist in the database!","No such device",javax.swing.JOptionPane.INFORMATION_MESSAGE);
00055          else  javax.swing.JOptionPane.showMessageDialog(null,sn1+" is a "+cType+" (not a module)","Not a module",javax.swing.JOptionPane.INFORMATION_MESSAGE);
00056          }
00057       return itExists;
00058       }
00059          
00060 
00061 // *****************************************************************************************************************
00062     public static String validateLocation(String theLocation) throws Exception {
00063 // get location name where argument = ulocation name OR institute username OR institute user number
00064       String validatedLocation="unknown";
00065       boolean isNumber = true;
00066       try {
00067          int locationNumber = Integer.parseInt(theLocation);
00068       }catch(Exception nException) {isNumber=false;}
00069 
00070       StringBuffer sqlStat = new StringBuffer("SELECT locn_name FROM LOCNS");
00071       if(isNumber) sqlStat.append(" WHERE locn="+theLocation);
00072       else sqlStat.append(" WHERE (locn_name='"+theLocation+"' OR username='"+theLocation+"')");
00073 
00074       SCTDBInterface db = SCTDBInterface.getInstance();
00075       Statement statement = db.connection.createStatement();
00076       ResultSet resultSet = statement.executeQuery(sqlStat.toString());
00077       int recordCount=0;
00078       for(boolean n = resultSet.next() ; n==true ; n=resultSet.next() ){
00079            validatedLocation = resultSet.getString(1);
00080             }
00081 //      System.out.println("Retrieved "+recordCount+" records in total.");
00082       statement.close();
00083       return validatedLocation;
00084       }
00085 // *****************************************************************************************************************
00086     public static String validatePersonInitial(String theUser) throws Exception {
00087 
00088       String person="unknown";
00089       StringBuffer sqlStat = new StringBuffer("SELECT initls FROM persons WHERE initls='"+theUser+"'");
00090 
00091       SCTDBInterface db = SCTDBInterface.getInstance();
00092       Statement statement = db.connection.createStatement();
00093       ResultSet resultSet = statement.executeQuery(sqlStat.toString());
00094       resultSet = statement.executeQuery(sqlStat.toString());
00095       int recordCount=0;
00096       for(boolean n = resultSet.next() ; n==true ; n=resultSet.next() ){
00097            person = resultSet.getString(1);
00098             }
00099 //      System.out.println("Retrieved "+recordCount+" records in total.");
00100       statement.close();
00101       return person;
00102       }
00103 
00104 // ******************************************************************************************************************
00105     public static Vector getNameList(String location) throws Exception {
00106      StringBuffer sqlStat = new StringBuffer("SELECT initls FROM persons");
00107      sqlStat.append(" WHERE persons.locn_name LIKE '"+location+"'");
00108 
00109       SCTDBInterface db = SCTDBInterface.getInstance();
00110       Statement statement = db.connection.createStatement();
00111       ResultSet resultSet = statement.executeQuery(sqlStat.toString());
00112 
00113      int recordCount=0;
00114      Vector itemList = new Vector();
00115      for(boolean n = resultSet.next() ; n==true ; n=resultSet.next() ){
00116           recordCount++;
00117           itemList.addElement(resultSet.getString(1));  
00118             }
00119 //      System.out.println("Retrieved "+recordCount+" names in total.");
00120 
00121       statement.close();
00122 
00123       if(itemList.size()>0) return itemList;
00124 // no users at this location? If so, return all users
00125 //      System.out.println("As there are no users defined at "+location+" I will return all users in the database.");
00126      sqlStat = new StringBuffer("SELECT initls FROM persons ORDER BY initls");
00127 
00128      statement = db.connection.createStatement();
00129      resultSet = statement.executeQuery(sqlStat.toString());
00130      recordCount=0;
00131      for(boolean n = resultSet.next() ; n==true ; n=resultSet.next() ){
00132           recordCount++;
00133           itemList.addElement(resultSet.getString(1));   // serial no
00134             }
00135       statement.close();
00136 //      System.out.println("Retrieved "+recordCount+" names in total.");
00137 
00138       return itemList;
00139      }
00140 
00141 
00142 //****************************************************************************
00143 public static double get500VData(String testno) throws Exception {
00144      java.util.regex.Pattern i500Pattern = java.util.regex.Pattern.compile(".*500[\\.\\d]*\\s*([\\.\\d]+).*");
00145      java.util.regex.Pattern lastCharPattern = java.util.regex.Pattern.compile("[0-9\\.]");
00146 
00147      Vector thisData = getRawData(testno);
00148      if(thisData.size()==0 || !isSctDaqData(testno)) return 0.;
00149      String[] theDataString = ((String)thisData.elementAt(1)).split("[\\r\\n]");
00150      for(int i=theDataString.length-1;i>=0;i--) {
00151          java.util.regex.Matcher matcher = i500Pattern.matcher(theDataString[i]);
00152          if(matcher.matches()) {
00153             String data500 = theDataString[i].substring(matcher.start(1),matcher.end(1));
00154             return Double.parseDouble(data500);
00155             }
00156          }
00157       return 0.;
00158 }
00159 //*********************************************************************
00160     public static boolean isSctDaqData(String testno) throws Exception {
00161      Statement statement = SCTDBInterface.getInstance().connection.createStatement();
00162      ResultSet resultSet = statement.executeQuery("SELECT version FROM SCT_TSTDAQINFO where test_no="+testno);
00163      String version=null;
00164      for(boolean n = resultSet.next() ; n==true ; n=resultSet.next() ){
00165           version = resultSet.getString(1);
00166             }
00167      statement.close();
00168      if(version==null) return false;
00169      return version.matches("\\d+\\.\\d+");
00170      }
00171 
00172 //**********************************************************************
00173     public static Vector getLocationList() throws Exception {
00174      System.out.println("Retrieving list of valid institute names from database ...");
00175      Statement statement = SCTDBInterface.getInstance().connection.createStatement();
00176      ResultSet resultSet = statement.executeQuery("SELECT locn_name FROM locns ORDER BY locn_name");
00177      Vector itemList = new Vector();
00178      for(boolean n = resultSet.next() ; n==true ; n=resultSet.next() ){
00179           itemList.addElement(resultSet.getString(1));   // serial no
00180             }
00181 //      System.out.println("Retrieved "+recordCount+" names in total.");
00182       statement.close();
00183       return itemList;
00184      }
00185 //*************************************************************************
00186       public static void downloadPSFile(javax.swing.JFrame parent, java.net.URL theURL) {
00187 
00188       File savedFile;
00189       String theFileName;
00190       String baseName=null;
00191       String tryAgainURL=null;
00192 
00193         theFileName = theURL.getFile();
00194         baseName = theURL.toString();
00195         System.out.println("Downloading "+baseName);
00196         baseName = baseName.substring(0,baseName.indexOf(theFileName));
00197 //        System.out.println("Downloading "+baseName);
00198 
00199         String theQuery = theURL.getQuery();
00200 //        if(theQuery!=null) System.out.println("theQuery: "+theQuery);
00201         theFileName = (theQuery!=null) ? theQuery : theURL.getFile();
00202         int lastEquals = theFileName.lastIndexOf("=");
00203         if(lastEquals!=-1) theFileName = theFileName.substring(lastEquals+1);
00204         int lastSlash = theFileName.lastIndexOf("/");
00205         if(lastSlash!=-1) theFileName = theFileName.substring(lastSlash+1);
00206 
00207         if(!theFileName.endsWith(".ps")) theFileName+=".ps";
00208         savedFile = new File(PreferencesInterface.getInstance().getPreference(PreferencesInterface.SCRATCH_DIR),theFileName);
00209 // execute query here
00210        try {
00211         downloadURL(savedFile,theURL,theFileName);
00212         }catch(Exception e1) {
00213             String eString = e1.toString();
00214             int nprot = eString.indexOf("no protocol: ");
00215             if(nprot==-1) {
00216                    System.out.println("Error downloading from URL: "+eString);
00217                    return;
00218                    }
00219             String theSubURL = eString.substring(nprot+13);
00220             tryAgainURL=baseName+theSubURL;
00221         }
00222      try {
00223      if(tryAgainURL!=null) {
00224         System.out.println("Invalid URL - maybe relative pathname - reconstructing new URL from relative pathname...");
00225         theURL = new java.net.URL(tryAgainURL);
00226         theFileName = theURL.getFile();
00227         lastSlash = theFileName.lastIndexOf("/");
00228         if(lastSlash!=-1) theFileName = theFileName.substring(lastSlash+1);
00229         int gzIndex = theFileName.indexOf(".gz");
00230         if(gzIndex!=-1) theFileName=theFileName.substring(0,gzIndex);
00231         savedFile = new File(PreferencesInterface.getInstance().getPreference(PreferencesInterface.SCRATCH_DIR),theFileName);
00232           
00233 
00234         downloadURL(savedFile,theURL,theFileName);
00235         }
00236       }catch(Exception e2) {
00237          System.out.println("Exception downloading from relative pathname in URL: "+e2.toString());
00238          return;
00239          }
00240 //     theFrame.psFileList.addElement(savedFile);   //save it for deletion when application closes
00241 
00242 
00243      String psExecutable = PreferencesInterface.getInstance().getPreference(PreferencesInterface.PSVIEWER);
00244      if(psExecutable.equals("Not defined")) {
00245                    Preference preference = new Preference(parent,PreferencesInterface.PSVIEWER);
00246                    psExecutable = PreferencesInterface.getInstance().getPreference(PreferencesInterface.PSVIEWER);
00247                    }
00248      if(psExecutable.equals("Not defined")) return; // fluffed the second chance!         
00249 
00250      try {
00251          File psExec = new File(psExecutable);
00252          if(!psExec.exists()) return;
00253          String command = psExecutable+" "+savedFile.getAbsolutePath();
00254          System.out.println(command);
00255          Process p = Runtime.getRuntime().exec( command );
00256      } catch(Exception e2) {System.out.println("Exception invoking ps viewer: "+e2.toString());}
00257    }
00258 //********************
00259       public static void downloadURL(File savedFile, java.net.URL theURL, String theFileName) throws Exception {
00260       InputStream in;              
00261       byte[] buffer = new byte[128000]; // max size is 128kByte per image (I think!)
00262       FileOutputStream fos;
00263       int nByteCount=0;
00264 
00265        fos = new FileOutputStream(savedFile);       // open the file output stream
00266        in  =  theURL.openStream();
00267 // read the data - will stop when reach end of stream or error, hopefully!
00268 //       System.out.println("Downloading postscript file "+theFileName+" Please be patient.");
00269        for(int nbytes=in.read(buffer); nbytes>0; nbytes=in.read(buffer)){
00270            nByteCount += nbytes;
00271            fos.write(buffer,0,nbytes);
00272             }
00273        fos.close();
00274        in.close();
00275        System.out.println("Wrote "+nByteCount+" bytes to "+savedFile.getAbsolutePath());
00276        guiUtilities.HTMLViewer.getInstance().addImageFile(savedFile.getAbsolutePath());
00277 } 
00278 //************************************************
00279        public static Hashtable getSignoffDataTestNo(String sn, String location, String signoffType) throws Exception {
00280          Hashtable thisHash = new Hashtable();
00281          StringBuffer sqlStat = new StringBuffer("SELECT test_no FROM tests");
00282          sqlStat.append(" WHERE ser_no="+sn+" AND run_no='"+signoffType+"' AND test_name='bmMOD_RData'");
00283          if(!location.equals("Any")) sqlStat.append(" AND locn_name='"+location+"'");
00284          sqlStat.append(" ORDER BY test_date DESC, test_no DESC");
00285          Statement statement = SCTDBInterface.getInstance().connection.createStatement();
00286          ResultSet resultSet = statement.executeQuery(sqlStat.toString());
00287          for(boolean n = resultSet.next() ; n==true ; n=resultSet.next() ){
00288             if(thisHash.containsKey(location)) continue;
00289             thisHash.put(location,resultSet.getString(1));
00290             break;
00291             }
00292 //      System.out.println("Retrieved "+recordCount+" names in total.");
00293       statement.close();
00294       return thisHash;
00295       }
00296 //************************************************
00297        public static Hashtable getSignoffDataTestNo(String sn, String signoffType) throws Exception {
00298 // return vector of test numbers
00299          Hashtable locationHash = new Hashtable();
00300          StringBuffer sqlStat = new StringBuffer("SELECT locn_name,test_no FROM tests");
00301          sqlStat.append(" WHERE ser_no="+sn+" AND run_no='"+signoffType+"' AND test_name='bmMOD_RData'");
00302          sqlStat.append(" ORDER BY test_date DESC, test_no DESC");
00303          Statement statement = SCTDBInterface.getInstance().connection.createStatement();
00304          ResultSet resultSet = statement.executeQuery(sqlStat.toString());
00305          for(boolean n = resultSet.next() ; n==true ; n=resultSet.next() ){
00306             String location = resultSet.getString(1);
00307             String testno = resultSet.getString(2);
00308             if(locationHash.containsKey(location)) continue;
00309             locationHash.put(location,testno);
00310             if(signoffType.equals("FINALSIGNOFF")) break;
00311             }
00312 //      System.out.println("Retrieved "+recordCount+" names in total.");
00313       statement.close();
00314       return locationHash;
00315       }
00316 //************************************************
00317        public static Vector getSignoffDataTestNos(String sn1, String sn2, String currLocn, String institute, String signoffType) throws Exception {
00318 // return vector of test numbers
00319          Set snHash = new HashSet();
00320          Vector snList = new Vector();
00321          StringBuffer sqlStat = new StringBuffer("SELECT ser_no, test_no FROM tests");
00322          if(!currLocn.equals("Anywhere")) sqlStat.append(",items");
00323          sqlStat.append(" WHERE ser_no>="+sn1+" AND ser_no<="+sn2+" AND run_no='"+signoffType+"' AND test_name='bmMOD_RData'");
00324          if(!institute.equals("Anywhere")) sqlStat.append(" AND locn_name='"+institute+"'");
00325          if(!currLocn.equals("Anywhere")) sqlStat.append(" AND tests.ser_no IN (SELECT items.ser_no FROM items WHERE items.locn_name='"+currLocn+"')");
00326          String ordering = (signoffType.equals("FINALSIGNOFF")) ? "DESC" : "";
00327          sqlStat.append(" ORDER BY ser_no, test_date "+ordering+", test_no "+ordering);
00328          Statement statement = SCTDBInterface.getInstance().connection.createStatement();
00329          ResultSet resultSet = statement.executeQuery(sqlStat.toString());
00330          int recordCount=0;
00331          for(boolean n = resultSet.next() ; n==true ; n=resultSet.next() ){
00332             String sn = resultSet.getString(1);
00333             String testno = resultSet.getString(2);
00334             if(signoffType.equals("FINALSIGNOFF") && snHash.contains(sn)) continue;
00335             snList.addElement(testno);
00336             snHash.add(sn);
00337             recordCount++;
00338             if(recordCount%100==0) System.out.println("Retrieved "+recordCount+" tests so far...");
00339             }
00340       System.out.println("Retrieved "+recordCount+" tests in total, now downloading and extracting raw data");
00341       statement.close();
00342       return snList;
00343       }
00344 //*************************************************
00345     public static String getUploaderInfo(String testno) throws Exception {
00346          StringBuffer sqlStat = new StringBuffer("SELECT locn_name,test_date,initls FROM tests");
00347          sqlStat.append(" WHERE test_no="+testno);
00348          Statement statement = SCTDBInterface.getInstance().connection.createStatement();
00349          ResultSet resultSet = statement.executeQuery(sqlStat.toString());
00350          String returnString=null;
00351          for(boolean n = resultSet.next() ; n==true ; n=resultSet.next() ){
00352             String location = resultSet.getString(1);
00353             String testdate = guiUtilities.DaveUtils.extractDate(resultSet.getString(2));
00354             String initials = resultSet.getString(3);
00355             returnString = "Uploaded by "+initials+" from "+location+" on "+testdate;
00356             }
00357       statement.close();
00358       return returnString;
00359       }
00360 //**********************************************************
00361    public static Hashtable testComments(String testno_subQuery) throws Exception {
00362 
00363       Hashtable commentHash = new Hashtable();
00364       StringBuffer sqlStat = new StringBuffer("SELECT test_no,cmnt_text FROM test_cmnts WHERE test_no IN (SELECT tests.test_no FROM tests ");      
00365       sqlStat.append(testno_subQuery+")");
00366 //      System.out.println(sqlStat.toString());
00367      Statement statement = SCTDBInterface.getInstance().connection.createStatement();
00368      ResultSet resultSet = statement.executeQuery(sqlStat.toString());
00369      int ncomments=0;
00370      for(boolean n = resultSet.next() ; n==true ; n=resultSet.next() ){
00371           ncomments++;
00372           if(ncomments%100 ==0) System.out.println("Retrieved "+ncomments+" comments so far...");
00373           String testno = resultSet.getString(1);
00374           String comment = resultSet.getString(2);
00375           // beware of more than one comment in a given testno !
00376           String newComment = (commentHash.containsKey(testno)) ? (String)commentHash.get(testno)+" "+comment : comment;
00377           commentHash.put(resultSet.getString(1),newComment);
00378             }
00379       statement.close();
00380       return commentHash;
00381       }
00382 
00383 
00384 
00385 
00386    public static Hashtable testImages(String testno_subQuery) throws Exception {
00387 // has contains a vector, and the vector contains title, then url, then title, then url etc
00388       Hashtable testImageHash = new Hashtable();
00389 //      System.out.println("Checking for test images...");
00390       StringBuffer sqlStat = new StringBuffer("SELECT test_no,title,name FROM test_images WHERE test_no IN (SELECT tests.test_no FROM tests ");      
00391       sqlStat.append(testno_subQuery+")");
00392 
00393      Statement statement = SCTDBInterface.getInstance().connection.createStatement();
00394      ResultSet resultSet = statement.executeQuery(sqlStat.toString());
00395      int ncomments=0;
00396      Vector imageList;
00397      for(boolean n = resultSet.next() ; n==true ; n=resultSet.next() ){
00398           ncomments++;
00399           if(ncomments%100 ==0) System.out.println("Retrieved "+ncomments+" test images so far...");
00400           String testno = resultSet.getString(1);
00401           String title = resultSet.getString(2);
00402           String name = resultSet.getString(3);
00403           // beware of more than one image in a given testno !
00404           if(testImageHash.containsKey(testno)) imageList = (Vector)testImageHash.get(testno);
00405           else imageList = new Vector();
00406           imageList.addElement(title);
00407           imageList.addElement(name);
00408           testImageHash.put(testno,imageList);
00409             }
00410 //      System.out.println("Retrieved "+ncomments+" test images in total.");
00411       statement.close();
00412       return testImageHash;
00413       }
00414 
00415 //***********************************************************************************************
00416    public static void saveImage(String testno, String fileName, File theFile) throws Exception {
00417       InputStream in;   
00418       FileOutputStream fos = new FileOutputStream(theFile);               
00419       byte[] buffer = new byte[128000]; // max size is 128kByte per image (I think!)
00420 
00421      String sqlQuery = "SELECT data FROM test_images WHERE test_no="+testno+" AND name='"+fileName+"'";
00422      Statement statement = SCTDBInterface.getInstance().connection.createStatement();
00423      ResultSet resultSet = statement.executeQuery(sqlQuery);
00424 
00425 // execute query here
00426       System.out.println("Retrieving "+fileName+" ...");
00427      int nByteCount=0;
00428 
00429      for(boolean n = resultSet.next() ; n==true ; n=resultSet.next() ){
00430          in  = resultSet.getBinaryStream(1);           // open the data stream
00431 
00432 // read the data - will stop when reach end of stream or error, hopefully!
00433          for(int nbytes=in.read(buffer); nbytes>0; nbytes=in.read(buffer)){
00434 
00435             // System.out.print("Nbytes read : " + nbytes+". ");
00436             nByteCount += nbytes;
00437             fos.write(buffer,0,nbytes);  // write the data to the file output
00438             }
00439          fos.close();  // close the file output
00440          System.out.println("Saved image "+fileName+" with size "+nByteCount+" bytes...");
00441          guiUtilities.HTMLViewer.getInstance().addImageFile(theFile.getAbsolutePath());
00442 
00443          }
00444      statement.close();
00445      return;
00446 }
00447 // *****************************************************************************************************************
00448     public static Vector getItemVector(String serialNo) throws Exception {
00449       Vector itemList = new Vector();
00450       StringBuffer sqlStat = new StringBuffer("SELECT ");
00451       sqlStat.append("mfr,mfr_ser_no,ctype,locn_name,assembled,trashed FROM items WHERE ser_no="+serialNo);
00452       sqlStat.append(" ORDER BY ser_no");
00453 
00454      Statement statement = SCTDBInterface.getInstance().connection.createStatement();
00455 //      System.out.println("Interrogating items table ...");
00456       ResultSet resultSet = statement.executeQuery(sqlStat.toString());
00457       for(boolean n = resultSet.next() ; n==true ; n=resultSet.next() ){
00458           itemList.addElement(resultSet.getString(1));   // mfr name
00459           itemList.addElement(resultSet.getString(2));   // mfr serial no
00460           itemList.addElement(resultSet.getString(3));   // type
00461           itemList.addElement(resultSet.getString(4));   // location name
00462           itemList.addElement(resultSet.getString(5));   // assembled flag
00463           itemList.addElement(resultSet.getString(6));   // trashed flag
00464             }
00465       statement.close();
00466       return itemList;
00467       }
00468 
00469 // ******************************************************************************************************
00470     public static Vector getVisualTestList(String ser_no1, String ser_no2, String location, String currLocn,String orderString) throws Exception {
00471      StringBuffer sqlStat = new StringBuffer("SELECT ");
00472      sqlStat.append("tests.test_no,tests.ser_no,tests.test_date,tests.locn_name,tests.problem,tests.pass");
00473      sqlStat.append(" FROM tests");
00474      sqlStat.append(" WHERE tests.ser_no >= "+ser_no1+" AND tests.ser_no <= "+ser_no2);
00475      if(!location.equals("Anywhere")) sqlStat.append(" AND tests.locn_name='"+location+"'");
00476      if(!currLocn.equals("Anywhere")) sqlStat.append(" AND tests.ser_no IN (SELECT items.ser_no FROM items WHERE items.locn_name='"+currLocn+"')");
00477      sqlStat.append(" AND tests.test_name='Visual_Inspection'");
00478      sqlStat.append(" ORDER BY "+orderString);
00479      return createVisualTestTable(sqlStat.toString());
00480      }
00481 // ******************************************************************************************************
00482     public static Vector getVisualTestList(String ser_no1, String ser_no2, String location, String currLocn) throws Exception {
00483       return getVisualTestList(ser_no1,ser_no2,location,currLocn,"tests.ser_no,tests.test_date,tests.test_no");
00484      }
00485 //***************************************************************8
00486     public static Vector createVisualTestTable(String theQuery) throws Exception {
00487       Hashtable testnoHash = new Hashtable();
00488       Vector testnoList = new Vector();
00489       Vector itemList = new Vector();
00490       Vector theLine = new Vector();
00491       String snKey="SNKEY";
00492       String dateKey="DATEKEY";
00493       String locationKey="LOCATIONKEY";
00494       String flagKey="FLAGKEY";
00495 
00496      Statement statement = SCTDBInterface.getInstance().connection.createStatement();
00497 
00498      System.out.println("Interrogating tests and tstdetivs tables ...");
00499      ResultSet resultSet = statement.executeQuery(theQuery);
00500      int recordCount=0;
00501      testNumberList = new Vector();
00502      for(boolean n = resultSet.next() ; n==true ; n=resultSet.next() ){
00503           Hashtable thisHash = new Hashtable();
00504           recordCount++;
00505           if(recordCount%100 == 0) System.out.println("Retrieved "+recordCount+" records so far...");
00506           String testno = resultSet.getString(1);
00507           thisHash.put(snKey,resultSet.getString(2));
00508           String date = resultSet.getString(3);
00509           thisHash.put(dateKey,guiUtilities.DaveUtils.extractDate(date));
00510           thisHash.put(locationKey,resultSet.getString(4));
00511           String problemFlag = resultSet.getString(5);
00512           String passFlag = resultSet.getString(6);
00513           String statusFlag; 
00514           if(passFlag.equals("YES")) {
00515              statusFlag = (problemFlag.equals("YES")) ? "PROBLEM" : "Ok";
00516              }
00517           else statusFlag = "FAIL";
00518           thisHash.put(flagKey,statusFlag);
00519           testnoHash.put(testno,thisHash);   
00520           testnoList.addElement(testno);  
00521 
00522             }
00523       System.out.println("Retrieved "+recordCount+" records in total.");
00524       statement.close();
00525       if(recordCount==0) return itemList;
00526 
00527 // now get the comments, if any
00528       String subQuery = theQuery;
00529       subQuery = subQuery.substring(subQuery.indexOf("WHERE"),subQuery.indexOf("ORDER")-1);
00530       Hashtable commentsHash = testComments(subQuery);
00531       Hashtable testImagesHash = testImages(subQuery);
00532 
00533       theLine.addElement("Serial Number");
00534       theLine.addElement("Date");
00535       theLine.addElement("Location");
00536       theLine.addElement("Status");
00537       theLine.addElement("Remarks");
00538       theLine.addElement("Pictures");
00539       itemList.addElement(theLine);
00540 
00541       for(int i=0;i<testnoList.size();i++) {
00542             theLine = new Vector();
00543             String testno = (String)testnoList.elementAt(i); // this contains the testno's, ordered by serial no
00544             Hashtable tempHash = (Hashtable)testnoHash.get(testno);
00545             theLine.addElement(tempHash.get(snKey));                                      // first column is serial number
00546 
00547             theLine.addElement(tempHash.get(dateKey));                                    // 2nd column is the date
00548             theLine.addElement(tempHash.get(locationKey));              
00549             theLine.addElement(tempHash.get(flagKey));
00550             if(commentsHash.containsKey(testno)) theLine.addElement(commentsHash.get(testno));
00551             else theLine.addElement("");
00552             if(testImagesHash.containsKey(testno)) {
00553                Vector imageList = (Vector)testImagesHash.get(testno);
00554 
00555                for(int j=0;j<imageList.size()/2; j++) {
00556                    if(j==0) {
00557 
00558                               theLine.addElement((String)imageList.elementAt(0));
00559                               testNumberList.addElement(testno);
00560                               itemList.addElement(theLine);
00561                               }
00562                    else {
00563 
00564                               theLine = new Vector();
00565                               theLine.addElement(tempHash.get(snKey));
00566                               theLine.addElement("  ''");
00567                               theLine.addElement("  ''");
00568                               theLine.addElement("  ''");
00569                               theLine.addElement("  ''");
00570                               theLine.addElement((String)imageList.elementAt(2*j));  
00571                               testNumberList.addElement(testno);
00572                               itemList.addElement(theLine);                     
00573                           }
00574 
00575                       }
00576             }
00577             else {theLine.addElement(""); testNumberList.addElement(testno);itemList.addElement(theLine);}
00578             }
00579       return itemList;
00580 
00581 
00582       }
00583 //
00584 //***********************************************************************************************
00585    public static Vector getImages(String testno,String headerTitle) throws Exception {
00586       InputStream in;   
00587  //     FileOutputStream fos;               
00588       byte[] buffer = new byte[128000]; // max size is 128kByte per image (I think!)
00589 
00590      Vector imageList = new Vector();
00591 
00592      String sqlQuery = "SELECT data,title,name FROM test_images WHERE test_no="+testno;
00593      Statement statement = SCTDBInterface.getInstance().connection.createStatement();
00594      ResultSet resultSet = statement.executeQuery(sqlQuery);
00595 
00596 // execute query here
00597       System.out.println("Interrogating test_images table for images ...");
00598      int nByteCount=0;
00599      int noImages=0;
00600 
00601      for(boolean n = resultSet.next() ; n==true ; n=resultSet.next() ){
00602          noImages++;
00603          in  = resultSet.getBinaryStream(1);           // open the data stream
00604 
00605 // read the data - will stop when reach end of stream or error, hopefully!
00606          for(int nbytes=in.read(buffer); nbytes>0; nbytes=in.read(buffer)){
00607             nByteCount += nbytes;
00608             }
00609          javax.swing.ImageIcon thisImage;
00610          try {
00611            thisImage = new javax.swing.ImageIcon(buffer);
00612          } catch (Exception e) {thisImage=null;} // just in case 
00613          String title = resultSet.getString(2);
00614          String name = resultSet.getString(3);
00615          System.out.println("Retrieved image "+name+" with size "+nByteCount+" bytes...");
00616          imageList.addElement(new guiUtilities.Photo(thisImage,title,name,headerTitle,testno));
00617          }
00618      System.out.println("Retrieved "+noImages+" images in total for test number "+testno);
00619      statement.close();
00620 //     storedImages.put(testno,imageList); // store the image list vector, in case we want to look again
00621      return imageList;
00622 }
00623 
00624 //***************************************************************************************************
00625  public static Map getRawDataLists(String sn1, String sn2, String location, int testType, int temperature, String runNo, int testRequest) throws Exception {
00626       Map m = new HashMap();
00627       StringBuffer sqlStat = new StringBuffer("SELECT tests.test_no,tests.ser_no,tests.test_date,tests.locn_name,");
00628       if(testType>=0) sqlStat.append("test_rawdata.filename,");
00629       sqlStat.append("SCT_TSTDCSINFO.T0 FROM tests,");
00630       if(testType>=0) sqlStat.append("test_rawdata,");
00631       sqlStat.append("SCT_TSTDCSINFO ");
00632 
00633       if(testRequest>=0) sqlStat.append("WHERE tests.test_name='"+sctdaqDBTestNames[testRequest]+"'");
00634       else {
00635         switch(testType) {
00636          case SCTDBInfo.SCTDB_TEST_TIMEWALK: 
00637 // xml data
00638       sqlStat.append(" WHERE ( (tests.test_name = 'HybTWalk' AND tests.locn_name !='Oxford') OR (tests.test_name = 'HybNoise' AND tests.locn_name = 'Oxford'))");
00639           break;
00640          default:
00641           sqlStat.append("WHERE tests.test_name='"+sctdaqDBTestNames[testType]+"'");
00642           }
00643        }
00644      sqlStat.append(" AND tests.ser_no >= "+sn1+" AND tests.ser_no <= "+sn2);
00645      if(runNo!=null) sqlStat.append(" AND tests.run_no LIKE '"+runNo+"'");
00646      if(testType>=0)sqlStat.append(" AND tests.test_no=test_rawdata.test_no");
00647      sqlStat.append(" AND SCT_TSTDCSINFO.test_no=tests.test_no");
00648      if(!location.equals("Anywhere")) sqlStat.append(" AND tests.locn_name='"+location+"'");
00649      switch(temperature) {
00650        case 1:
00651          sqlStat.append(" AND SCT_TSTDCSINFO.T0<=10");
00652          break;
00653        case 2:
00654          sqlStat.append(" AND SCT_TSTDCSINFO.T0>10");
00655          break;
00656        default:
00657        }
00658      sqlStat.append(" ORDER BY tests.ser_no,tests.test_date,tests.test_no");
00659 
00660 //     System.out.println(sqlStat.toString());
00661      Statement statement = SCTDBInterface.getInstance().connection.createStatement();
00662      ResultSet resultSet = statement.executeQuery(sqlStat.toString());
00663 
00664      for(boolean n = resultSet.next() ; n==true ; n=resultSet.next() ){
00665 
00666            SCTDBTestInfoHolder testInfo = new SCTDBTestInfoHolder();
00667            testInfo.put(SCTDBTestInfoHolder.TESTNUMBER,resultSet.getString(1));
00668            Long sn = new Long(resultSet.getLong(2));
00669            testInfo.put(SCTDBTestInfoHolder.SN,sn.toString());
00670            testInfo.put(SCTDBTestInfoHolder.DATE,guiUtilities.DaveUtils.extractDate(resultSet.getString(3)));
00671            testInfo.put(SCTDBTestInfoHolder.LOCATION,resultSet.getString(4));
00672            int lastIndex = (testType>=0) ? 6 : 5;
00673            testInfo.put(SCTDBTestInfoHolder.TEMPERATURE,resultSet.getString(lastIndex));
00674            m.put(sn,testInfo);
00675        }
00676      statement.close();
00677      return m;
00678      }
00679    public static Vector getRawData(String testno) throws Exception {
00680      return getRawData(testno,PreferencesInterface.getInstance().getPreference(PreferencesInterface.SCRATCH_DIR));
00681      }
00682 
00683 // ****************************************************************************************************************
00684    public static Vector getRawData(String testno, String saveDir) throws Exception {
00685 // get the raw data - if its a zip file, save it to disk with same filename
00686 // otherwise just download as a string
00687       Vector returnData = new Vector();
00688 
00689       // first get filename of the raw data file of this testno
00690 
00691      String sqlQuery = "SELECT filename from test_rawdata WHERE test_no="+testno;
00692      Statement statement = SCTDBInterface.getInstance().connection.createStatement();
00693      ResultSet resultSet = statement.executeQuery(sqlQuery);
00694      String thisFname = null;
00695      for(boolean n = resultSet.next() ; n==true ; n=resultSet.next() ){
00696           thisFname = resultSet.getString(1);
00697           }
00698      if(thisFname==null) {
00699           return returnData;
00700           }
00701 // now download that file
00702      File savedFile = new File(saveDir,thisFname);
00703      if(downloadFile(testno,savedFile)==0) return returnData;
00704 // now read in the downloaded file - firat assume it is zipped...
00705      try {
00706        returnData = unZipFile(savedFile);
00707        }
00708      catch(Exception ee) {
00709 // not zipped, so assume its just text
00710        returnData = getFileContent(savedFile);
00711        File tempFile = new File(thisFname); // get just the last part, not a pathname
00712        returnData.insertElementAt((String)tempFile.getName(),0);
00713        }
00714      boolean deleted = savedFile.delete(); // delete the downloaded file
00715 // check if its an .xml file, if so, assume it is an sctdaq xml config file
00716 // in which case, we want to lookup and susbtitute the calibrate correction factors
00717      returnData = insertCalFactors(returnData);
00718 // index0 : filename
00719 // index1 : content as a String
00720 // ..etc for index2 and 3 etc
00721      return returnData;
00722      }
00723 //***********************************************************************8
00724 public static Vector getFileContent(File theFile) throws Exception {
00725     // get file content as a String
00726     Vector returnData = new Vector();
00727     BufferedReader in = new BufferedReader(new FileReader(theFile));
00728     String line;
00729     StringBuffer stringBuffer = new StringBuffer();
00730     boolean firstLine=true;
00731     while((line=in.readLine())!=null) {
00732       if(!firstLine) stringBuffer.append("\n");
00733       stringBuffer.append(line);
00734       firstLine=false;
00735       }
00736     in.close();
00737     returnData.addElement(stringBuffer.toString());
00738     return returnData;
00739     }
00740 
00741 //********************************************
00742 public static int downloadFile(String testno, File savedFile) throws Exception {
00743 
00744       InputStream in;         
00745       String theData = "";      
00746       byte[] buffer = new byte[128000]; // max size is 128kByte per image (I think!)
00747     FileOutputStream fos;
00748 // execute query here
00749      String sqlQuery = "SELECT raw_data FROM test_rawdata WHERE test_no="+testno; 
00750      Statement statement = SCTDBInterface.getInstance().connection.createStatement();
00751      ResultSet resultSet = statement.executeQuery(sqlQuery);
00752 
00753      int nByteCount=0;
00754      fos = new FileOutputStream(savedFile);       // open the file output stream
00755      for(boolean n = resultSet.next() ; n==true ; n=resultSet.next() ){
00756          in  = resultSet.getBinaryStream(1);           // open the data stream
00757 
00758 // read the data - will stop when reach end of stream or error, hopefully!
00759          for(int nbytes=in.read(buffer); nbytes>0; nbytes=in.read(buffer)){
00760            nByteCount += nbytes;
00761            fos.write(buffer,0,nbytes);
00762             }
00763          }
00764      fos.close();
00765      statement.close();
00766      if(nByteCount==0) System.out.println("No raw data available for test "+testno);
00767 //     else System.out.println("Downloaded "+savedFile.getName()+", size "+nByteCount+" bytes.");
00768      return nByteCount;
00769 }
00770 //*************************************************************************
00771 public static Vector unZipFile(File savedFile) throws Exception{
00772       Vector returnData = new Vector();
00773       byte[] buffer = new byte[128000]; // max size is 128kByte per image (I think!)
00774 
00775 // now extract the zip file...
00776      final int BUFFER=2048;
00777      ZipFile zFile = new ZipFile(savedFile);
00778      FileInputStream fis = new FileInputStream(savedFile);
00779      ZipInputStream zis = new ZipInputStream(new BufferedInputStream(fis));
00780      ZipEntry entry;
00781      while((entry = zis.getNextEntry()) != null) {
00782          returnData.addElement((String)entry.getName());   // first the data filename
00783          int count;
00784          byte[] data = new byte[BUFFER];
00785          StringBuffer thisData = new StringBuffer();
00786          while((count=zis.read(data,0,BUFFER)) != -1) {
00787             thisData.append(new String(data,0,count)); 
00788             }
00789          returnData.addElement((String)thisData.toString());    // then the filename content as a string
00790         }
00791      zis.close();
00792      return returnData;
00793 }
00794 public static Vector insertCalFactors(Vector rawdata) throws Exception {
00795     String lineTerminator = "\\s*\\n";
00796     Pattern modulePattern = Pattern.compile("<module>");
00797     Pattern locationPattern = Pattern.compile(".*<location>(.*)</location>.*");
00798     Pattern snPattern = Pattern.compile("\\s*<sn>(\\d{14})</sn>");
00799     Pattern chipIDPattern = Pattern.compile("\\s*<chip id=\"(\\d{2})\".*");
00800     Pattern cFactorPattern = Pattern.compile("(\\s*)<c_factor>.*</c_factor>");
00801     Pattern runNoPattern = Pattern.compile(".*<run>(\\d+)</run><scan>(\\d+)</scan>.*");
00802     Pattern rcFitPattern = Pattern.compile("(.*)<rc_function.*</rc_function>");
00803     String moduleSerialNo = null;
00804     String location=null;
00805     String thisChipPosition=null;
00806     Hashtable chipPositionHash = new Hashtable();
00807     Hashtable rcFitHash = null;
00808 
00809     boolean xmlfile=false;
00810     for(int i=0;i<rawdata.size();i++) {
00811 
00812         if(i%2==0) {
00813             if (((String)rawdata.elementAt(i)).indexOf(".xml")!=-1) xmlfile=true;
00814             else xmlfile=false;
00815             continue;
00816             }
00817 
00818         if(!xmlfile) continue;
00819         String[] lines = ((String)rawdata.elementAt(i)).split(lineTerminator);
00820 
00821         Matcher matcher = modulePattern.matcher(lines[0]);
00822         if(!matcher.matches()) return rawdata;     // first line of xml file is expected to be <module>
00823 
00824         StringBuffer newString = new StringBuffer("<module>");
00825         for(int line=1;line<lines.length;line++) {
00826             String thisLine = lines[line];
00827 //            System.out.println("Checking line "+line+": "+thisLine);
00828             matcher = snPattern.matcher(thisLine);
00829             if(matcher.matches()) {
00830                  moduleSerialNo = thisLine.substring(matcher.start(1),matcher.end(1));
00831                  chipPositionHash = getChipPositionHash(moduleSerialNo);               
00832                  }
00833             matcher = locationPattern.matcher(thisLine);
00834             if(matcher.matches()) location = thisLine.substring(matcher.start(1),matcher.end(1));
00835 
00836             matcher = runNoPattern.matcher(thisLine);
00837             if(matcher.matches()) {
00838                    rcFitHash = getRCFits(moduleSerialNo,location,thisLine.substring(matcher.start(1),matcher.end(1)),thisLine.substring(matcher.start(2),matcher.end(2)));
00839                    if(rcFitHash.size()!=12) System.err.println("Failed to extract RC fit parameters for module config file");
00840                    }
00841 
00842             matcher = rcFitPattern.matcher(thisLine);
00843             if(matcher.matches() && thisChipPosition!=null && rcFitHash!=null) {
00844               String padding = thisLine.substring(matcher.start(1),matcher.end(1));
00845               if(rcFitHash.containsKey(thisChipPosition)) {
00846                  lines[line] = padding+(String)rcFitHash.get(thisChipPosition);
00847                  }
00848               }
00849 
00850             matcher = chipIDPattern.matcher(thisLine);
00851             if(matcher.matches()) {
00852                  thisChipPosition = thisLine.substring(matcher.start(1),matcher.end(1));
00853 //                 System.out.println("Found chip position "+thisChipPosition);
00854                  }
00855             matcher = cFactorPattern.matcher(thisLine);
00856             if(matcher.matches() && thisChipPosition!=null) {
00857                  String spaces = thisLine.substring(matcher.start(1),matcher.end(1));
00858                  if(chipPositionHash.containsKey(thisChipPosition)) lines[line] = spaces+"<c_factor>"+(String)chipPositionHash.get(thisChipPosition)+"</c_factor>";
00859                  else System.err.println("Cal Correction factors are not available for "+moduleSerialNo);
00860                 }
00861             newString.append("\n"+lines[line]);
00862 
00863             }
00864          rawdata.setElementAt((String)newString.toString(),i);
00865 
00866         }
00867 
00868    return rawdata;
00869 }
00870 public static Hashtable getChipPositionHash(String moduleSN) throws Exception {
00871    Pattern lotNoPattern = Pattern.compile("(Z\\d+-W\\d+).*"); // miss out the non-LOT info, eg Z39993-W12 X1 Y2
00872    Hashtable thisHash = new Hashtable();
00873    String sn=null;
00874 // first get the hybrid serial no
00875      StringBuffer sqlStat = new StringBuffer("SELECT ");
00876      sqlStat.append("ser_no from ASSM_ITEMS WHERE assm_ser_no="+moduleSN);
00877      sqlStat.append(" AND (ctype LIKE '%Hybrid%' OR ctype LIKE 'bmHASIC')");
00878 //     System.out.println("get hybrid serial no ...");
00879 //     System.out.println(sqlStat.toString());
00880      Statement statement;
00881      statement = SCTDBInterface.getInstance().connection.createStatement();
00882 
00883      ResultSet resultSet = statement.executeQuery(sqlStat.toString());
00884 
00885      for(boolean n = resultSet.next() ; n==true ; n=resultSet.next() ){
00886           sn = resultSet.getString(1);
00887           }
00888      statement.close();
00889      if(sn==null) return thisHash;  // no entry in assembly table - return empty hash
00890 //     else System.out.println("Hybrid is "+sn);
00891 
00892 // now get the chips in the assembly table
00893 // construct the hash with position as Key and LotNumber as value
00894      sqlStat = new StringBuffer("SELECT ");
00895      sqlStat.append("ASSM_ITEMS.posn,items.mfr_ser_no from ASSM_ITEMS,items WHERE assm_ser_no="+sn);
00896      sqlStat.append(" AND items.ser_no=assm_items.ser_no AND assm_items.ctype = 'chABCD3T'");
00897 
00898 //     System.out.println("now get chips ...");
00899 //     System.out.println(sqlStat.toString());
00900      statement = SCTDBInterface.getInstance().connection.createStatement();
00901      resultSet = statement.executeQuery(sqlStat.toString());
00902 
00903      int chipno=0;
00904      for(boolean n = resultSet.next() ; n==true ; n=resultSet.next() ){
00905           String posn = resultSet.getString(1);
00906           String mfr_sn = resultSet.getString(2);
00907           Matcher matcher = lotNoPattern.matcher(mfr_sn);
00908           int thePosition = Integer.parseInt(posn);
00909           thePosition--;
00910           posn = Integer.toString(thePosition);
00911 //          System.out.println("chipno="+chipno+", posn="+posn+", mfr_sn="+mfr_sn);
00912           if(matcher.matches()) {
00913             mfr_sn = mfr_sn.substring(matcher.start(1),matcher.end(1));
00914             if(posn.length()<2) posn="0"+posn;
00915             thisHash.put(posn,mfr_sn);
00916 //            System.out.println("chipno="+chipno+", posn="+posn+", mfr_sn="+mfr_sn);
00917             }
00918           chipno++;
00919           }
00920       statement.close();
00921      if(thisHash.size()!=12) {
00922         System.out.println("No asic assembly data for hybrid "+sn);
00923         return new Hashtable();
00924         }
00925 // ok, now go through the chips to get the cal correction factors
00926      Hashtable correctionHash = new Hashtable();
00927      int ifactors=0;
00928      for (Enumeration e = thisHash.keys() ; e.hasMoreElements() ;) {
00929          String thisPosn = (String)e.nextElement();
00930          String thisLotNo = (String)thisHash.get(thisPosn);
00931          if(!correctionHash.containsKey(thisLotNo)) {
00932               String factor = WaferUtilities.getCalCorrFactor(thisLotNo); 
00933               if(!factor.equals("")) correctionHash.put(thisLotNo,factor);
00934               }
00935          if(correctionHash.containsKey(thisLotNo)) {
00936               String thisFactor = (String)correctionHash.get(thisLotNo);
00937               thisHash.put(thisPosn,thisFactor);
00938 //              System.out.println("Putting "+thisFactor+" into chip position "+thisPosn);
00939               ifactors++;
00940               }
00941          }
00942       ifactors = 12 - ifactors;
00943       if(ifactors>0) System.out.println("WARNING: CalCorr factors not found for "+ifactors+" chips");
00944     
00945       return thisHash;    
00946 
00947 }
00948    public static Hashtable getRCFits(String sn, String location, String runNo, String scanNo) throws Exception {
00949 
00950       Hashtable rcFitHash = new Hashtable();
00951       if(sn==null) {
00952          System.err.println("Null serialno in getRCFits");
00953          return rcFitHash;
00954          }
00955       if(location==null) {
00956          System.err.println("Null location in getRCFits");
00957          return rcFitHash;
00958          }
00959 
00960       int scanNumber= Integer.parseInt(scanNo);
00961 // the run-scan of the ResponseCurve is either 10 or 12 scans before the run-scan in the module config file
00962 // depending on whether it was uploaded with the TimeWalk or Noise test
00963       String rsString1 = runNo+"-"+Integer.toString(scanNumber-10);
00964       String rsString2 = runNo+"-"+Integer.toString(scanNumber-21);
00965 
00966 // first get the hybrid serial no
00967      StringBuffer sqlStat = new StringBuffer("SELECT tests.test_no");
00968 
00969      for(int i=0;i<12;i++) {
00970         for (int y=9;y<=12;y++) sqlStat.append(","+chipDBnames[i]+DBChipParameterNames[SCTDB_TEST_NPTGAIN][y]);
00971         }
00972      sqlStat.append(" FROM SCT_TSTHYBRC,tests WHERE SCT_TSTHYBRC.test_no=tests.test_no");
00973      sqlStat.append(" AND (tests.run_no='"+rsString1+"' OR tests.run_no='"+rsString2+"')");
00974      sqlStat.append(" AND tests.ser_no="+sn+" AND tests.locn_name='"+location+"'");
00975 
00976 //     System.out.println(sqlStat.toString());
00977 
00978      Statement statement = SCTDBInterface.getInstance().connection.createStatement();
00979      ResultSet resultSet = statement.executeQuery(sqlStat.toString());
00980 
00981      int arg=1;
00982      for(boolean n = resultSet.next() ; n==true ; n=resultSet.next() ){
00983           String testno = resultSet.getString(arg++);
00984           for(int chipno=0;chipno<12;chipno++) {
00985               String line = "<rc_function type=\""+resultSet.getString(arg++)+"\">p0 "+resultSet.getString(arg++)+" p1 "+resultSet.getString(arg++)+" p2 "+resultSet.getString(arg++)+"</rc_function>";
00986               String posn = Integer.toString(chipno);
00987               if(posn.length()<2) posn="0"+posn;
00988               rcFitHash.put(posn,line);
00989               }
00990           }
00991       statement.close();
00992       return rcFitHash;
00993       }
00994 
00995 
00996 
00997      
00998 
00999 
01000 // ******************************************************************************************************************
01001     public static boolean confirmLocation(String location) throws Exception {
01002      boolean foundit=false;
01003      String sqlStat = new String("SELECT locn_name FROM locns WHERE locn_name='"+location+"'");
01004      Statement statement = SCTDBInterface.getInstance().connection.createStatement();
01005      ResultSet resultSet = statement.executeQuery(sqlStat);
01006      for(boolean n = resultSet.next() ; n==true ; n=resultSet.next() ){
01007            return true;
01008             }
01009       statement.close();
01010       return foundit;
01011      }
01012 //**************************************************************
01013      public static String getTestString(int testIndex,String testno) {
01014          Hashtable snHash = new Hashtable();
01015          Hashtable testnoHash = new Hashtable();
01016 
01017          StringBuffer result = new StringBuffer();
01018 
01019          StringBuffer sqlStat = new StringBuffer();
01020             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");
01021             sqlStat.append(",SCT_TSTDAQINFO.VERSION,SCT_TSTDAQINFO.TEST_TIME");
01022             switch(testIndex) {
01023                     case SCTDB_TEST_RESET:
01024                     case SCTDB_TEST_REDUNDANCY:
01025                     case SCTDB_TEST_LONGTERM:
01026                     case SCTDB_TEST_IV:
01027                         break;
01028                     default:
01029                         for(int y=0;y<chipDBnames.length;y++) {
01030                            for(int paramIndex=0;paramIndex<DBChipParameterNames[testIndex].length;paramIndex++) {
01031                                   sqlStat.append(","+chipDBnames[y]+DBChipParameterNames[testIndex][paramIndex]);
01032                                   }
01033                           }
01034 
01035                      }
01036          sqlStat.append(" FROM "+sctdaqDBTableNames[testIndex]+",tests,SCT_TSTDCSINFO,SCT_TSTDAQINFO");
01037          sqlStat.append(" WHERE tests.test_no = "+testno);
01038          sqlStat.append(" AND tests.TEST_no = "+sctdaqDBTableNames[testIndex]+".TEST_no");
01039          sqlStat.append(" AND tests.TEST_no = SCT_TSTDCSINFO.TEST_no");
01040          sqlStat.append(" AND tests.TEST_no = SCT_TSTDAQINFO.TEST_no");
01041          sqlStat.append(" ORDER BY tests.TEST_date DESC,tests.TEST_no DESC");
01042 
01043          try {
01044 
01045          Statement statement = SCTDBInterface.getInstance().connection.createStatement();
01046 //         System.out.println(sqlStat.toString());
01047          ResultSet resultSet = statement.executeQuery(sqlStat.toString());
01048          for(boolean n = resultSet.next() ; n==true ; n=resultSet.next() ){
01049             int rc=1;
01050 
01051             String sn = resultSet.getString(rc++);
01052             result.append("SERIALNO="+sn+"\n");
01053             if(snHash.containsKey(sn)) continue;
01054             int status = (resultSet.getString(rc++).equals("YES")) ? 0 : 2;
01055             if(resultSet.getString(rc++).equals("YES") && status==0) status = 1;
01056 
01057 
01058 
01059             result.append(sctdaqNormalTestNames[testIndex]+"\n");
01060             switch(status) {
01061               case 0:
01062                  result.append("PASS\n");
01063                  break;
01064               case 1:
01065                  result.append("PROBLEM\n");
01066                  break;
01067               default:
01068                  result.append("FAIL\n");
01069                  }
01070             result.append("TESTNO="+resultSet.getString(rc++)+"\n");  // testno
01071             result.append("LOCATION="+resultSet.getString(rc++)+"\n");  //date
01072             result.append("DATE="+guiUtilities.DaveUtils.extractDate(resultSet.getString(rc++))+"\n");
01073             result.append("RUN/SCAN="+resultSet.getString(rc++)+"\n");
01074             result.append("TESTNAME="+resultSet.getString(rc++)+"\n");
01075             result.append("TEMPERATURE="+resultSet.getString(rc++)+"\n");
01076            result.append("SCTDAQ_VERSION="+resultSet.getString(rc++)+"\n");
01077            result.append("TIME="+resultSet.getString(rc++)+"\n");
01078 
01079             result.append("Chip\t");
01080             for(int i=0;i<DBChipParameterNames[testIndex].length;i++) result.append(DBChipParameterNames[testIndex][i]+"\t");
01081             result.append("\n");
01082 
01083             for(int chip=0;chip<12;chip++) {
01084               result.append(chipDBnames[chip].substring(0,2)+"\t");
01085               for(int r=0;r<DBChipParameterNames[testIndex].length;r++) {
01086                  if(r>0) result.append("\t");
01087                  result.append(resultSet.getString(rc++));
01088                  }
01089               result.append("\n");
01090               }
01091 
01092             }
01093         statement.close();
01094 
01095         sqlStat = new StringBuffer();
01096         sqlStat.append("SELECT defects.defect_name,defects.chan_1st,defects.chan_last");
01097         sqlStat.append(" FROM defects");
01098         sqlStat.append(" WHERE defects.TEST_no = "+testno);
01099 
01100 
01101         statement = SCTDBInterface.getInstance().connection.createStatement();
01102 //         System.out.println(sqlStat.toString());
01103         resultSet = statement.executeQuery(sqlStat.toString());
01104         int ndefects=0;
01105         result.append("Defects:\n");
01106         for(boolean n = resultSet.next() ; n==true ; n=resultSet.next() ){ 
01107             ndefects++;
01108             String defectName = resultSet.getString(1);
01109             String chan1 = resultSet.getString(2);
01110             String chanLast = resultSet.getString(3);
01111             result.append(chan1+"-"+chanLast+" : "+defectName+"\n");
01112             }
01113         statement.close();
01114         result.append("defectCount="+Integer.toString(ndefects)+"\n");
01115 
01116         }catch(Exception e) {System.out.println("Failed to publish SCTDB Data: "+e.toString());}
01117 
01118         return result.toString();
01119 }
01120 
01121 //****************************************************************************************8
01122      public static void removePreviousUploads(String testname, String runno, String locn, Map itemList) throws Exception {
01123 
01124      StringBuffer sqlStat = new StringBuffer("SELECT tests.ser_no,test_name,run_no,test_date,sct_tstdaqinfo.test_time FROM tests,sct_tstdaqinfo");
01125      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");
01126      sqlStat.append(" AND sct_tstdaqinfo.version LIKE 'SctRodDaq%'");
01127      Statement statement = SCTDBInterface.getInstance().connection.createStatement();
01128 //     System.out.println(sqlStat.toString());
01129      ResultSet resultSet = statement.executeQuery(sqlStat.toString());
01130      for(boolean n = resultSet.next() ; n==true ; n=resultSet.next() ){
01131           StringBuffer thisSig = new StringBuffer(resultSet.getString(1));
01132           thisSig.append("_");
01133           thisSig.append(resultSet.getString(2));   // testname
01134           thisSig.append("_");
01135           thisSig.append(resultSet.getString(3));   // run_no
01136           thisSig.append("_");
01137           thisSig.append(guiUtilities.DaveUtils.extractSCTDAQDate(resultSet.getString(4)));   // date
01138           thisSig.append("_");
01139           thisSig.append(resultSet.getString(5));   // time
01140           String signature = thisSig.toString();
01141           if(itemList.containsKey(signature)) {
01142               System.out.println("Data for "+signature.substring(0,14)+" is already uploaded!");
01143               itemList.remove(signature);
01144               }
01145             }
01146       statement.close();
01147      }
01148 
01149 //*************************************************************
01150      public static String getUserName(String locn) throws Exception {
01151      Statement statement = SCTDBInterface.getInstance().connection.createStatement();
01152 //     System.out.println(sqlStat.toString());
01153      ResultSet resultSet = statement.executeQuery("SELECT username FROM locns WHERE locn_name='"+locn+"'");
01154      String username = null;
01155      for(boolean n = resultSet.next() ; n==true ; n=resultSet.next() ){
01156           username = resultSet.getString(1);
01157           }
01158      statement.close();
01159      return username;
01160      }
01161 //*************************************************************
01162      public static String getSerialNo(String testno) throws Exception {
01163      Statement statement = SCTDBInterface.getInstance().connection.createStatement();
01164 //     System.out.println(sqlStat.toString());
01165      ResultSet resultSet = statement.executeQuery("SELECT ser_no FROM tests WHERE test_no='"+testno+"'");
01166      String sn = null;
01167      for(boolean n = resultSet.next() ; n==true ; n=resultSet.next() ){
01168           sn = resultSet.getString(1);
01169           }
01170      statement.close();
01171      return sn;
01172      }
01173 //*************************************************************
01174      public static String getCType(String serno) throws Exception {
01175      Statement statement = SCTDBInterface.getInstance().connection.createStatement();
01176 //     System.out.println(sqlStat.toString());
01177      ResultSet resultSet = statement.executeQuery("SELECT ctype FROM items WHERE ser_no='"+serno+"'");
01178      String ctype = null;
01179      for(boolean n = resultSet.next() ; n==true ; n=resultSet.next() ){
01180           ctype = resultSet.getString(1);
01181           }
01182      statement.close();
01183      return ctype;
01184      }
01185 //**********************************************************
01186     public static List getTestList(int testIndex,String locn, String testMenuName) throws Exception {
01187      java.util.regex.Pattern runPattern = java.util.regex.Pattern.compile("(\\d+)-(\\d+)");
01188      Statement statement = SCTDBInterface.getInstance().connection.createStatement();
01189      StringBuffer sqlStat = new StringBuffer("SELECT DISTINCT test_date,run_no FROM tests,sct_tstdaqinfo WHERE locn_name='"+locn+"' AND test_name='"+sctdaqDBTestNames[testIndex]+"'");
01190      sqlStat.append(" AND tests.test_no=sct_tstdaqinfo.test_no AND sct_tstdaqinfo.version LIKE 'SctRodDaq%'");
01191      sqlStat.append(" ORDER BY tests.test_date DESC");
01192 //     System.out.println(sqlStat.toString());
01193      ResultSet resultSet = statement.executeQuery(sqlStat.toString());
01194      List itemList = new ArrayList();
01195      for(boolean n = resultSet.next() ; n==true ; n=resultSet.next() ){
01196           List row = new ArrayList();
01197           row.add(guiUtilities.DaveUtils.extractDate(resultSet.getString(1)));
01198           row.add(testMenuName);
01199           String runno = resultSet.getString(2);
01200           java.util.regex.Matcher matcher = runPattern.matcher(runno);
01201           if(!matcher.matches()) {
01202             System.err.println("Unrecognised runno "+runno);
01203             continue;
01204              }
01205           
01206           row.add(runno.substring(matcher.start(1),matcher.end(1)));
01207           row.add(runno.substring(matcher.start(2),matcher.end(2)));
01208           itemList.add(row);
01209 
01210           }
01211      statement.close();
01212      return itemList;
01213      }
01214 
01215 //******************************************************
01216    public static SCTDBTestInfoHolder getTestInfo(String test_no) throws Exception {
01217 
01218       SCTDBTestInfoHolder testInfo= new SCTDBTestInfoHolder();
01219 
01220       StringBuffer genTable = new StringBuffer();
01221       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);
01222       sqlStat.append(" AND items.ser_no=tests.ser_no");
01223 
01224 // submit query
01225      Statement statement = SCTDBInterface.getInstance().connection.createStatement();
01226      ResultSet resultSet = statement.executeQuery(sqlStat.toString());
01227    
01228      for(boolean n = resultSet.next() ; n==true ; n=resultSet.next() ){
01229           testInfo.put(SCTDBTestInfoHolder.TEST_NAME,resultSet.getString(1));
01230           testInfo.put(SCTDBTestInfoHolder.SN,resultSet.getString(2));
01231           testInfo.put(SCTDBTestInfoHolder.DATE,guiUtilities.DaveUtils.extractDate(resultSet.getString(3)));
01232           testInfo.put(SCTDBTestInfoHolder.LOCATION,resultSet.getString(4));
01233           testInfo.put(SCTDBTestInfoHolder.CTYPE,resultSet.getString(5));
01234           }
01235      testInfo.put(SCTDBTestInfoHolder.TESTNUMBER,test_no);
01236      statement.close();
01237      if(testInfo.isValid()) return testInfo;
01238      return null;
01239      }
01240 
01241 // **********************************************************************************************
01242     public static Vector getTestHistory(String serialNo) throws Exception {
01243      StringBuffer sqlStat = new StringBuffer("SELECT ");
01244      sqlStat.append("tests.ser_no,tests.locn_name,tests.test_date,tests.test_name,tests.problem,tests.pass");
01245      sqlStat.append(" FROM tests");
01246      sqlStat.append(" WHERE tests.ser_no = "+serialNo);
01247      sqlStat.append(" ORDER BY tests.test_date,tests.test_no");
01248      
01249 
01250       Statement statement = SCTDBInterface.getInstance().connection.createStatement();
01251       ResultSet resultSet = statement.executeQuery(sqlStat.toString());
01252       Hashtable serNoHash = new Hashtable();
01253       Hashtable thisHash;
01254       Vector serNoList = new Vector();
01255       Vector theLine = new Vector();
01256       Vector itemList = new Vector();
01257       int recordCount=0;
01258       theLine.addElement("Serial Number");
01259       theLine.addElement("Test Location");
01260       theLine.addElement("Date");
01261       theLine.addElement("Test Name");
01262       theLine.addElement("Test Status");
01263       itemList.addElement(theLine);
01264       for(boolean n = resultSet.next() ; n==true ; n=resultSet.next() ){
01265           theLine = new Vector();
01266           theLine.addElement(resultSet.getString(1));
01267           theLine.addElement(resultSet.getString(2));
01268           theLine.addElement(guiUtilities.DaveUtils.extractDate(resultSet.getString(3)));
01269           theLine.addElement(resultSet.getString(4));
01270 
01271           String problemFlag = resultSet.getString(5);
01272           String passFlag = resultSet.getString(6);
01273           String statusFlag; 
01274           if(passFlag.equals("YES")) {
01275              statusFlag = (problemFlag.equals("YES")) ? "Problem" : "Pass";
01276              }
01277           else statusFlag="FAIL";
01278           theLine.addElement(statusFlag);
01279           itemList.addElement(theLine);
01280           recordCount++;
01281           if(recordCount%100 ==0) System.out.println("Retrieved "+recordCount+" records ...");
01282             }
01283        System.out.println("Retrieved "+recordCount+" records in total.");
01284 
01285        statement.close();
01286        return itemList;
01287 
01288      }
01289 
01290    public static Vector getShipmentHistory(String serialNo) throws Exception {
01291       Vector theLine = new Vector();
01292       Vector itemList = new Vector();
01293       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 ");
01294       sqlStat.append("WHERE ship_items.ser_no="+serialNo+" AND ship_items.ship_no=ship.ship_no ");
01295       sqlStat.append("ORDER BY ship.ship_date,ship.ship_no"); 
01296  
01297       theLine.addElement("Serial Number");
01298       theLine.addElement("Shipment No");
01299       theLine.addElement("Sent from");
01300       theLine.addElement("Received By");
01301       theLine.addElement("Send Date");
01302       theLine.addElement("Received?");
01303       itemList.addElement(theLine);
01304       Statement statement = SCTDBInterface.getInstance().connection.createStatement();
01305       ResultSet resultSet = statement.executeQuery(sqlStat.toString());
01306       
01307       int recordCount=0;
01308       for(boolean n = resultSet.next() ; n==true ; n=resultSet.next() ){
01309           theLine = new Vector();
01310           theLine.addElement(resultSet.getString(1));
01311           theLine.addElement(resultSet.getString(2));
01312           theLine.addElement(resultSet.getString(3));
01313           theLine.addElement(resultSet.getString(4));
01314           theLine.addElement(guiUtilities.DaveUtils.extractDate(resultSet.getString(5)));
01315           theLine.addElement(new Boolean(resultSet.getString(6).equals("YES")));
01316           itemList.addElement(theLine);
01317 
01318           recordCount++;
01319           if(recordCount%100 ==0) System.out.println("Retrieved "+recordCount+" shipment records ...");
01320             }
01321        System.out.println("Retrieved "+recordCount+" shipment records in total.");
01322        statement.close();
01323        return itemList;
01324        }
01325 
01326 
01327     public static List getRodList(String sn) throws Exception {
01328 
01329       StringBuffer sqlStat = new StringBuffer("SELECT ");
01330       sqlStat.append(" tests.test_no,tests.ser_no,tests.locn_name,tests.test_date,sct_tstdcsinfo.t0 FROM tests,test_rawdata,sct_tstdcsinfo");
01331       sqlStat.append(" WHERE tests.ser_no="+sn);
01332       sqlStat.append(" AND ( (tests.test_name = 'HybTWalk' AND tests.locn_name !='Oxford') OR (tests.test_name = 'HybNoise' AND tests.locn_name = 'Oxford'))");
01333 
01334       sqlStat.append(" AND tests.test_no = sct_tstdcsinfo.test_no AND tests.test_no=test_rawdata.test_no");
01335       sqlStat.append(" ORDER BY tests.ser_no,tests.test_date,tests.test_no");
01336 
01337      List itemList = new ArrayList();
01338 
01339      Statement statement = SCTDBInterface.getInstance().connection.createStatement();
01340      ResultSet resultSet = statement.executeQuery(sqlStat.toString());
01341      testNumberList = new Vector();
01342      for(boolean n = resultSet.next() ; n==true ; n=resultSet.next() ){
01343           testNumberList.addElement(resultSet.getString(1));
01344           List thisLine = new ArrayList();
01345           thisLine.add(resultSet.getString(2));  //sn
01346           thisLine.add(resultSet.getString(3)); // location
01347           thisLine.add(guiUtilities.DaveUtils.extractDate(resultSet.getString(4)));
01348           thisLine.add(resultSet.getString(5));
01349           itemList.add(thisLine);
01350          }
01351 
01352       statement.close();
01353 
01354       return itemList;
01355 
01356       }
01357 
01358         
01359      
01360 
01361 
01362 }

Generated on Thu Jul 15 09:55:42 2004 for SCT DAQ/DCS Software - Java by doxygen 1.3.5