SCT DAQ/DCS Software - Java: generalScripts.java Source File

00001 package webscripts;
00002 // getDBfiles.java
00003 // @Author Dave Robinson
00004 //
00005 // 21052003 Introduce controlFlag - bit 1 set means create upload files but don't upload
00006 //                                  bit 2 set means don't show gui and immediately create upload files
00007 
00008 
00009 import java.io.*;
00010 import java.util.*;
00011 import java.sql.*;
00012 import ProdDatabase.*;
00013 import ProdDatabase.SCTDBInfo;
00014 import DisplayGUI.*;
00015 
00016 public class generalScripts {
00017 
00018 
00019 
00020 public static Vector getPositionMap(int i) throws Exception {
00021 
00022        Vector list = new Vector();
00023        StringBuffer sqlStat = new StringBuffer("SELECT ser_no,posn FROM assm_items WHERE");
00024        sqlStat.append(" assm_ser_no="+DisplayGUI.SCTInfo.SCTSerialNumbers[i]);
00025        sqlStat.append(" ORDER BY ser_no");
00026 
00027 //      System.out.println(sqlStat.toString());
00028        Statement statement = SCTDBInterface.getInstance().connection.createStatement();
00029        ResultSet resultSet = statement.executeQuery(sqlStat.toString());
00030        int recordCount=0;
00031 
00032        for(boolean n = resultSet.next() ; n==true ; n=resultSet.next() ){
00033           String sn = resultSet.getString(1);
00034           String posn = resultSet.getString(2);
00035           DisplayGUI.SCTDBAssemblyInfo a = new DisplayGUI.SCTDBAssemblyInfo(posn);
00036           list.addElement(sn+"="+DisplayGUI.SCTInfo.SCTViewNames[i]+", Row "+a.getRow()+", Position "+a.getPos());
00037           }
00038        statement.close();
00039        return list;
00040        }
00041 
00042        
00043 public static void makeDataReferenceFile(String targetDir, int testIndex, boolean isWarm) throws Exception {
00044 
00045        Set s = new HashSet();
00046        StringBuffer sqlStat = new StringBuffer("SELECT tests.ser_no,tests.TEST_no,tests.locn_name,tests.test_date");
00047        switch(testIndex) {
00048          case SCTDBInfo.SCTDB_TEST_3PTGAIN:
00049             for(int y=0;y<SCTDBInfo.chipDBnames.length;y++) sqlStat.append(","+SCTDBInfo.chipDBnames[y]+"RC_MN_ENC,"+SCTDBInfo.chipDBnames[y]+"RC_MN_GAIN");
00050             break;
00051          case SCTDBInfo.SCTDB_TEST_STROBEDELAY:
00052             for(int y=0;y<SCTDBInfo.chipDBnames.length;y++) sqlStat.append(","+SCTDBInfo.chipDBnames[y]+"DELAY");
00053             break;
00054          case SCTDBInfo.SCTDB_TEST_NOISE:
00055             for(int y=0;y<SCTDBInfo.chipDBnames.length;y++) sqlStat.append(","+SCTDBInfo.chipDBnames[y]+"NO_NSE,"+SCTDBInfo.chipDBnames[y]+"NO_MN_OCC");
00056          default:
00057          }
00058 
00059        sqlStat.append(" FROM "+SCTDBInfo.sctdaqDBTableNames[testIndex]+",tests,sct_tstdcsinfo,SCT_TSTDAQINFO");
00060        sqlStat.append(" WHERE tests.TEST_name = '"+SCTDBInfo.sctdaqDBTestNames[testIndex]+"'");
00061        sqlStat.append(" AND tests.ser_no IN (SELECT ser_no FROM items WHERE locn_name='Oxford')");
00062        sqlStat.append(" AND tests.TEST_no = "+SCTDBInfo.sctdaqDBTableNames[testIndex]+".TEST_no");
00063        sqlStat.append(" AND SCT_TSTDAQINFO.version NOT LIKE 'SctRodDaq%' AND SCT_TSTDAQINFO.test_no=tests.TEST_no");  // SCTDAQ data only
00064        if(isWarm) sqlStat.append(" AND sct_tstdcsinfo.t0>10 AND sct_tstdcsinfo.TEST_no=tests.TEST_no");
00065        else  sqlStat.append(" AND sct_tstdcsinfo.t0<=10 AND sct_tstdcsinfo.TEST_no=tests.TEST_no");
00066        sqlStat.append(" ORDER BY tests.ser_no,tests.TEST_date DESC,tests.TEST_no DESC");
00067 
00068        BufferedWriter out = null;
00069        String endtab = isWarm ? "_Reference_warm.txt" : "_Reference_cold.txt";
00070        File oFile = new File(targetDir,SCTDBInfo.sctdaqDBTestNames[testIndex]+endtab);
00071 
00072        System.out.println("Downloading reference "+SCTDBInfo.sctdaqNormalTestNames[testIndex]+" data to "+oFile.getAbsolutePath());
00073 
00074        Statement statement = SCTDBInterface.getInstance().connection.createStatement();
00075        System.out.println(sqlStat.toString());
00076        ResultSet resultSet = statement.executeQuery(sqlStat.toString());
00077        for(boolean n = resultSet.next() ; n==true ; n=resultSet.next() ){
00078             int rc=1;
00079 
00080             String sn = resultSet.getString(rc++);
00081             if(s.contains(sn)) continue;
00082             s.add(sn);
00083 
00084             StringBuffer theLine = new StringBuffer("SN="+sn);
00085             theLine.append("TESTNO="+resultSet.getString(rc++));
00086             System.out.println("sn="+sn+", location="+resultSet.getString(rc++)+" date="+guiUtilities.DaveUtils.extractDate(resultSet.getString(rc++)));
00087             theLine.append("DATA=");
00088             for(int j=0;j<12;j++) {
00089               if(j>0) theLine.append(";");
00090               theLine.append(resultSet.getString(rc++));
00091               if(testIndex!=SCTDBInfo.SCTDB_TEST_STROBEDELAY) {
00092                 theLine.append("*");
00093                 theLine.append(resultSet.getString(rc++));
00094                 }
00095               }
00096             if(out==null) out = new BufferedWriter(new FileWriter(oFile));
00097             out.write(theLine.toString());
00098             out.newLine();
00099             }
00100          if(out!=null) out.close();
00101          statement.close();
00102          }
00103 
00104 public static void makeDataReferenceFile2(String targetDir, int testIndex, boolean isWarm) throws Exception {
00105 
00106        java.util.regex.Pattern runPattern = java.util.regex.Pattern.compile("(\\d+)-(\\d+)");
00107        java.util.regex.Pattern linePattern = java.util.regex.Pattern.compile("(.*)DEF=XX(CFACTORS=.*TESTNO=)(\\d+)(LOC=.*)");
00108        java.text.DecimalFormat nf = new java.text.DecimalFormat("#.##");
00109 
00110        String[] testfnames={"","","FullBypass","Pipeline","StrobeDelay","3PtGain","TrimRange","NPtGain","NoiseOccupancy","TimeWalk","",""};
00111        for(int barrelType=0;barrelType<4;barrelType++) {
00112        Set s = new HashSet();
00113        Vector slist = new Vector();
00114 
00115        String temp = isWarm ? "warm" : "cold";
00116 
00117        StringBuffer sqlStat = new StringBuffer("SELECT tests.ser_no,tests.TEST_no,tests.locn_name,tests.test_date,tests.run_no");
00118        sqlStat.append(",sct_tstdcsinfo.T0,sct_tstdcsinfo.T1,sct_tstdcsinfo.Vdet,sct_tstdcsinfo.Idet,sct_tstdcsinfo.Vcc,sct_tstdcsinfo.Icc,sct_tstdcsinfo.Vdd,sct_tstdcsinfo.Idd");
00119        for(int y=0;y<SCTDBInfo.chipDBnames.length;y++) {
00120            for(int d=0;d<SCTDBInfo.DBChipParameterNames[testIndex].length;d++) {
00121              if(testIndex==SCTDBInfo.SCTDB_TEST_FULLBYPASS && d>=2) continue;
00122              if(testIndex==SCTDBInfo.SCTDB_TEST_3PTGAIN && d>=9) continue;
00123              if(testIndex==SCTDBInfo.SCTDB_TEST_NPTGAIN && d>=9) continue;
00124              sqlStat.append(","+SCTDBInfo.chipDBnames[y]+SCTDBInfo.DBChipParameterNames[testIndex][d]);
00125              }
00126          }
00127        sqlStat.append(" FROM "+SCTDBInfo.sctdaqDBTableNames[testIndex]+",tests,sct_tstdcsinfo,SCT_TSTDAQINFO");
00128        sqlStat.append(" WHERE tests.TEST_name = '"+SCTDBInfo.sctdaqDBTestNames[testIndex]+"'");
00129        sqlStat.append(" AND tests.ser_no IN (SELECT ser_no FROM assm_items WHERE assm_ser_no="+DisplayGUI.SCTInfo.SCTSerialNumbers[barrelType]+")");
00130        sqlStat.append(" AND tests.TEST_no = "+SCTDBInfo.sctdaqDBTableNames[testIndex]+".TEST_no");
00131        sqlStat.append(" AND SCT_TSTDAQINFO.version NOT LIKE 'SctRodDaq%' AND SCT_TSTDAQINFO.test_no=tests.TEST_no");  // SCTDAQ data only
00132        if(isWarm) sqlStat.append(" AND sct_tstdcsinfo.t0>10 AND sct_tstdcsinfo.TEST_no=tests.TEST_no");
00133        else  sqlStat.append(" AND sct_tstdcsinfo.t0<=10 AND sct_tstdcsinfo.TEST_no=tests.TEST_no");
00134        sqlStat.append(" ORDER BY tests.ser_no,tests.TEST_date DESC,tests.TEST_no DESC");
00135 
00136        BufferedWriter out = null;
00137        int bnum = barrelType+3;
00138        String barreltab = "_Barrel"+Integer.toString(bnum);
00139        String endtab = isWarm ? "_Reference_warm.txt" : "_Reference_cold.txt";
00140        File oFile = new File(targetDir,testfnames[testIndex]+barreltab+endtab);
00141 
00142        System.out.println("Downloading "+temp+" reference "+SCTDBInfo.sctdaqNormalTestNames[testIndex]+" data from Barrel "+Integer.toString(bnum)+" to "+oFile.getAbsolutePath());
00143 
00144        Statement statement = SCTDBInterface.getInstance().connection.createStatement();
00145 //       System.out.println(sqlStat.toString());
00146        ResultSet resultSet = statement.executeQuery(sqlStat.toString());
00147 
00148        StringBuffer testnoString = new StringBuffer();
00149 
00150        for(boolean n = resultSet.next() ; n==true ; n=resultSet.next() ){
00151             int rc=1;
00152 
00153             String sn = resultSet.getString(rc++);
00154             if(s.contains(sn)) continue;
00155             s.add(sn);
00156 
00157             String testno = resultSet.getString(rc++);
00158             String location = resultSet.getString(rc++);
00159             String date = guiUtilities.DaveUtils.extractDate(resultSet.getString(rc++));
00160             String runscan = resultSet.getString(rc++);
00161             String run="-1";
00162             String scan="-1";
00163             java.util.regex.Matcher matcher = runPattern.matcher(runscan);
00164             if(matcher.matches()) {
00165                run = runscan.substring(matcher.start(1),matcher.end(1));
00166                scan = runscan.substring(matcher.start(2),matcher.end(2));
00167                }
00168             StringBuffer DCSline = new StringBuffer("DCS=");
00169             String t0 = resultSet.getString(rc++);
00170             String t1 = resultSet.getString(rc++);
00171             double t0d = Double.valueOf(t0).doubleValue();
00172             double t1d = Double.valueOf(t1).doubleValue();
00173             String tdiff = nf.format(Math.abs(t0d-t1d));
00174             DCSline.append(t0+"*"+t1+"*"+tdiff);
00175             for(int d=0;d<6;d++) {
00176                DCSline.append("*");
00177                if(d==1) {  // IDet convert uA to nA
00178                   String idet = resultSet.getString(rc++);
00179                   double idetd = Double.valueOf(idet).doubleValue();
00180                   idetd *= 1000.;
00181                   idet = nf.format(idetd);
00182                   DCSline.append(idet);
00183                   }
00184                else DCSline.append(resultSet.getString(rc++));
00185                }
00186 
00187             StringBuffer dataline = new StringBuffer("DATA=");
00188             for(int j=0;j<SCTDBInfo.chipDBnames.length;j++) {
00189                 dataline.append(SCTDBInfo.chipDBnames[j].substring(0,2)+":");
00190                 for(int d=0;d<SCTDBInfo.DBChipParameterNames[testIndex].length;d++) {
00191                    if(testIndex==SCTDBInfo.SCTDB_TEST_FULLBYPASS && d>=2) continue;
00192                    if(testIndex==SCTDBInfo.SCTDB_TEST_3PTGAIN && d>=9) continue;
00193                    if(testIndex==SCTDBInfo.SCTDB_TEST_NPTGAIN && d>=9) continue;
00194                    if(d>0) dataline.append("*");
00195                    dataline.append(resultSet.getString(rc++));
00196                    }
00197                    // now we need to add blank entries for the sctroddaq-only params
00198                   if(testIndex==SCTDBInfo.SCTDB_TEST_3PTGAIN || testIndex==SCTDBInfo.SCTDB_TEST_NPTGAIN) {
00199                    for(int d=0;d<3;d++) dataline.append("*.");  // 3 types of slope params
00200                    }
00201                   if(testIndex==SCTDBInfo.SCTDB_TEST_NOISE) {
00202                    for(int d=0;d<2;d++) dataline.append("*.");  // badness params
00203                    }
00204                   dataline.append(";");
00205                 }
00206 
00207 //            RUN=${runno}SCAN=${scanno}SN=${sn}${DATAline}${DCSline}DEF=${defectcount}*${cCount}${CONFline}
00208             String theLine = "RUN="+run+"SCAN="+scan+"SN="+sn+dataline+DCSline+"DEF=XXCFACTORS=1.00*1.00*1.00*1.00*1.00*1.00*1.00*1.00*1.00*1.00*1.00*1.00CONF=TESTNO="+testno+"LOC="+location+"DATE="+date;
00209             slist.addElement(theLine);
00210             if(testnoString.length()!=0) testnoString.append(",");
00211             testnoString.append(testno);
00212 
00213 
00214             }
00215         statement.close();
00216 
00217         Map defectCount = new HashMap();
00218         Map chanCount = new HashMap();
00219 
00220         if(slist.size()>0) {
00221 
00222         System.out.println("Get defects for "+testfnames[testIndex]+"...");
00223         sqlStat = new StringBuffer();
00224         sqlStat.append("SELECT defects.defect_name,defects.chan_1st,defects.chan_last,defects.test_no");
00225         sqlStat.append(" FROM defects");
00226         sqlStat.append(" WHERE defects.TEST_no IN ("+testnoString.toString()+")");
00227         statement = SCTDBInterface.getInstance().connection.createStatement();
00228 //        System.out.println(sqlStat.toString());
00229         resultSet = statement.executeQuery(sqlStat.toString());
00230 
00231        for(boolean n = resultSet.next() ; n==true ; n=resultSet.next() ){
00232          String name = resultSet.getString(1);
00233          String chan1 = resultSet.getString(2);
00234          String chan2 = resultSet.getString(3);
00235          String testno = resultSet.getString(4);
00236          if(defectCount.containsKey(testno)) {
00237             int i = ((Integer)defectCount.get(testno)).intValue();
00238             i++;
00239             defectCount.put(testno,new Integer(i));
00240             }
00241          else defectCount.put(testno,new Integer(1));
00242          int i1 = Integer.valueOf(chan1).intValue();
00243          int i2 = Integer.valueOf(chan2).intValue();
00244          Map tmpMap;
00245          if(chanCount.containsKey(testno)) tmpMap = (Map)chanCount.get(testno);
00246          else tmpMap = new HashMap();
00247          for (int x=i1;x<=i2;x++) {
00248                Integer xi = new Integer(x);
00249                if(!tmpMap.containsKey(xi)) tmpMap.put(xi,"1");
00250                }
00251          chanCount.put(testno,tmpMap);
00252          }
00253          statement.close();
00254 
00255          }
00256 
00257          for(int i=0;i<slist.size();i++) {
00258            String line = (String)slist.elementAt(i);
00259            java.util.regex.Matcher matcher = linePattern.matcher(line);
00260            //("(.*)DEF=0*0(CFACTORS=.*TESTNO=)(.*)(LOC=.*)");
00261            if(matcher.matches()) {
00262               String b1 = line.substring(matcher.start(1),matcher.end(1));
00263               String b2 = line.substring(matcher.start(2),matcher.end(2));
00264               String testno = line.substring(matcher.start(3),matcher.end(3));
00265               String b3 = line.substring(matcher.start(4),matcher.end(4));
00266               String defC="0";
00267               String chanC="0";
00268               if(defectCount.containsKey(testno)) defC = ((Integer)defectCount.get(testno)).toString();
00269               if(chanCount.containsKey(testno)) {
00270                  Map tmpMap = (Map)chanCount.get(testno);
00271                  int size = tmpMap.keySet().size();
00272                  chanC = Integer.toString(size);
00273                  }
00274               line = b1+"DEF="+defC+"*"+chanC+b2+testno+b3;
00275               }
00276            else System.out.println("**ERROR cant match line "+line);
00277            // write file
00278            if(out==null) {
00279                System.out.println("opening "+oFile.getAbsolutePath());
00280                out = new BufferedWriter(new FileWriter(oFile));
00281                }
00282             out.write(line);
00283             out.newLine();
00284             }
00285 
00286          if(out!=null) out.close();
00287 
00288          }
00289          }
00290 
00291 
00292 
00293 
00294 }