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
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
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
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
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
00120
00121 statement.close();
00122
00123 if(itemList.size()>0) return itemList;
00124
00125
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));
00134 }
00135 statement.close();
00136
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));
00180 }
00181
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
00198
00199 String theQuery = theURL.getQuery();
00200
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
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
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;
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];
00262 FileOutputStream fos;
00263 int nByteCount=0;
00264
00265 fos = new FileOutputStream(savedFile);
00266 in = theURL.openStream();
00267
00268
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
00293 statement.close();
00294 return thisHash;
00295 }
00296
00297 public static Hashtable getSignoffDataTestNo(String sn, String signoffType) throws Exception {
00298
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
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
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
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
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
00388 Hashtable testImageHash = new Hashtable();
00389
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
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
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];
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
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);
00431
00432
00433 for(int nbytes=in.read(buffer); nbytes>0; nbytes=in.read(buffer)){
00434
00435
00436 nByteCount += nbytes;
00437 fos.write(buffer,0,nbytes);
00438 }
00439 fos.close();
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
00456 ResultSet resultSet = statement.executeQuery(sqlStat.toString());
00457 for(boolean n = resultSet.next() ; n==true ; n=resultSet.next() ){
00458 itemList.addElement(resultSet.getString(1));
00459 itemList.addElement(resultSet.getString(2));
00460 itemList.addElement(resultSet.getString(3));
00461 itemList.addElement(resultSet.getString(4));
00462 itemList.addElement(resultSet.getString(5));
00463 itemList.addElement(resultSet.getString(6));
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
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
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);
00544 Hashtable tempHash = (Hashtable)testnoHash.get(testno);
00545 theLine.addElement(tempHash.get(snKey));
00546
00547 theLine.addElement(tempHash.get(dateKey));
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
00588 byte[] buffer = new byte[128000];
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
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);
00604
00605
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;}
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
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
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
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
00686
00687 Vector returnData = new Vector();
00688
00689
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
00702 File savedFile = new File(saveDir,thisFname);
00703 if(downloadFile(testno,savedFile)==0) return returnData;
00704
00705 try {
00706 returnData = unZipFile(savedFile);
00707 }
00708 catch(Exception ee) {
00709
00710 returnData = getFileContent(savedFile);
00711 File tempFile = new File(thisFname);
00712 returnData.insertElementAt((String)tempFile.getName(),0);
00713 }
00714 boolean deleted = savedFile.delete();
00715
00716
00717 returnData = insertCalFactors(returnData);
00718
00719
00720
00721 return returnData;
00722 }
00723
00724 public static Vector getFileContent(File theFile) throws Exception {
00725
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];
00747 FileOutputStream fos;
00748
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);
00755 for(boolean n = resultSet.next() ; n==true ; n=resultSet.next() ){
00756 in = resultSet.getBinaryStream(1);
00757
00758
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
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];
00774
00775
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());
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());
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;
00823
00824 StringBuffer newString = new StringBuffer("<module>");
00825 for(int line=1;line<lines.length;line++) {
00826 String thisLine = lines[line];
00827
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
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+).*");
00872 Hashtable thisHash = new Hashtable();
00873 String sn=null;
00874
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
00879
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;
00890
00891
00892
00893
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
00899
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
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
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
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
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
00962
00963 String rsString1 = runNo+"-"+Integer.toString(scanNumber-10);
00964 String rsString2 = runNo+"-"+Integer.toString(scanNumber-21);
00965
00966
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
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
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");
01071 result.append("LOCATION="+resultSet.getString(rc++)+"\n");
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
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
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
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));
01134 thisSig.append("_");
01135 thisSig.append(resultSet.getString(3));
01136 thisSig.append("_");
01137 thisSig.append(guiUtilities.DaveUtils.extractSCTDAQDate(resultSet.getString(4)));
01138 thisSig.append("_");
01139 thisSig.append(resultSet.getString(5));
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
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
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
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
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
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));
01346 thisLine.add(resultSet.getString(3));
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 }