ModuleInfo.java

package ProdDatabase;
/*
 * @author robinson
 */
import java.sql.*;
import java.io.*;
import java.util.*;

public class ModuleInfo implements SCTDBInfo, barrelMetrologyData, endcapMetrologyData {
   Statement           statement;
   ResultSet           resultSet;
   StringBuffer        sqlStat;

   private Vector sensorList, asicList, metrologyList;
   private Hashtable testDataHash, asicMfrHash, correctionHash;
   private Hashtable metrologyHash;
   private Hashtable snHash;
   int recordCount;
   String moduleSerialNo,hybridSerialNo;
   int deviceType;
   boolean DEBUG=false;

   java.util.regex.Pattern lotNoPattern = java.util.regex.Pattern.compile("(Z\\d+-W\\d+).*"); // miss out the non-LOT info, eg Z39993-W12 X1 Y2

   public ModuleInfo(String moduleSerialNo)  {
       this.moduleSerialNo=moduleSerialNo;
       metrologyList = new Vector();
       sensorList = new Vector();          // list of sensors, in order of position
       asicList = new Vector();            // list of asics, in order of position
       snHash = new Hashtable();           // maps component name to serial no
       testDataHash = new Hashtable();     // maps datasheet testname to DB testno
       asicMfrHash = new Hashtable();      // maps sn to mfr_sn for asics
       correctionHash = new Hashtable();   // maps lot no to cal corr factor for asics
       metrologyHash = new Hashtable();
       try {
        getDeviceType();
        if(!isModule()) return;
        fillSNHash();
        getDataSheetInfo();
        if(isBarrelModule()) getBarrelMetrologyData();
        if(isForwardModule()) getEndCapMetrologyData();
       }catch(Exception e){System.err.println(e.toString());}
               
   }

   public String getSerialNo(int component) {
      if(snHash.containsKey(moduleParts[component])) return (String)snHash.get(moduleParts[component]);
      else return "Not Uploaded.";
      }
   public String getHybridSerialNo() {
      return hybridSerialNo;
      }
   public Vector getSensorList() {
       return sensorList;
       }
   public Vector getAsicList() {
       return asicList;
       }
   public int noMetrologyTests() {
       return metrologyList.size();
       }
   public MetrologyTest getMetrologyTest(int index) {
       String testno = (String)metrologyList.elementAt(index);
       return (MetrologyTest)metrologyHash.get(testno);
       }

   public String getDataSheetTestNo(int component) {
       if(testDataHash.containsKey(dataSheetTestDBNames[component])) return (String)testDataHash.get(dataSheetTestDBNames[component]);
       else return null;
       }

   public String getASICInfo(String sn) {
       if(!asicMfrHash.containsKey(sn)) return null;
       else return (String)asicMfrHash.get(sn);
       }
   public String getASICcorrFactor(String sn) {
       String mfrNo;
       if(!asicMfrHash.containsKey(sn)) return null;
       mfrNo = new String((String)asicMfrHash.get(sn));     
       java.util.regex.Matcher matcher = lotNoPattern.matcher(mfrNo);       
       if(matcher.matches()) {
            String lotNo = mfrNo.substring(matcher.start(1),matcher.end(1));
            if(correctionHash.containsKey(lotNo))  return (String)correctionHash.get(lotNo);
            else return null;
            }
       return null;
       }
   private void fillSNHash() {
       String bmHASICserialNo;
       snHash.put(moduleParts[bmMODULE],moduleSerialNo);
       try {
         hybridSerialNo = getHybridSN(moduleSerialNo);
         switch(deviceType) {
           case 1: //endcap
             sensorList = getSensorList(moduleSerialNo);
             if(hybridSerialNo!=null) {
                getChipInfo(hybridSerialNo);   
                snHash.put(moduleParts[bmHASIC],hybridSerialNo);   
                }      
             break;
           default: // barrel
             if(hybridSerialNo!=null) {
               snHash.put(moduleParts[bmHASIC],hybridSerialNo);
               snHash.put(moduleParts[bmHPC],moduleSerialNo.substring(0,7)+"7"+moduleSerialNo.substring(8));
               getChipInfo(hybridSerialNo);
               }
             String bmSBsn;
             if((bmSBsn=getSensorBaseboardSN(moduleSerialNo))!=null) {
                sensorList = getSensorList(bmSBsn);
                snHash.put(moduleParts[bmSB],bmSBsn);
                snHash.put(moduleParts[bmBB],bmSBsn.substring(0,7)+"8"+bmSBsn.substring(8));
                }
           }
       }catch(Exception e1){System.err.println(e1.toString());}

   }    

//****************************************************************************8
public void getDeviceType() throws Exception {
     sqlStat = new StringBuffer("SELECT ctype from ITEMS WHERE ser_no="+moduleSerialNo);
     statement = SCTDBInterface.getInstance().connection.createStatement();
     if(DEBUG) System.out.println(sqlStat.toString());
     resultSet = statement.executeQuery(sqlStat.toString());
     deviceType=-1;
     String ctype=null;
     for(boolean n = resultSet.next() ; n==true ; n=resultSet.next() ){
          ctype=resultSet.getString(1);
          }
     statement.close();
     if(ctype==null) return;
     if(ctype.matches("bmModule")) deviceType=0;
     else if(ctype.matches(".*fmModule.*")) deviceType=1;
     }
//********************************************
public boolean isModule() {
     return (deviceType>=0);
     } 
public boolean isBarrelModule() {
     return (deviceType==0);
     }
public boolean isForwardModule() {
     return (deviceType==1);
     }

//***************************************************************************************************
public String getHybridSN(String moduleSerialNo) throws Exception {
     sqlStat = new StringBuffer("SELECT ");
     sqlStat.append("ser_no, posn from ASSM_ITEMS WHERE assm_ser_no="+moduleSerialNo+" AND (ctype LIKE 'fhHybrid%' OR ctype LIKE 'bmHASIC') ORDER BY posn");
     statement = SCTDBInterface.getInstance().connection.createStatement();
     if(DEBUG) System.out.println(sqlStat.toString());
     resultSet = statement.executeQuery(sqlStat.toString());
     recordCount=0;
     String sn = null;

     for(boolean n = resultSet.next() ; n==true ; n=resultSet.next() ){
          sn = resultSet.getString(1);
          }
     statement.close();
     return sn;
     }
//*****************************************************************************************************
public String getSensorBaseboardSN(String moduleSerialNo) throws Exception {
     // first get the bmSB
     sqlStat = new StringBuffer("SELECT ");
     sqlStat.append("ser_no, posn from ASSM_ITEMS WHERE assm_ser_no="+moduleSerialNo+" AND ctype LIKE 'bmSB' ORDER BY posn");
     statement = SCTDBInterface.getInstance().connection.createStatement();
     if(DEBUG) System.out.println(sqlStat.toString());
     resultSet = statement.executeQuery(sqlStat.toString());
     recordCount=0;
     String sn = null;

     for(boolean n = resultSet.next() ; n==true ; n=resultSet.next() ){
          sn = resultSet.getString(1);
          }

     statement.close();
     return sn;
     }
//*************************************************************************8
public Vector getSensorList(String serialNo) throws Exception {
// supplied serialNo is moduleSerialNo for endcap, bmSB serial number of barrels
     // first get the bmSB
     Vector sensorList = new Vector();
     sqlStat = new StringBuffer("SELECT ");
     sqlStat.append("ser_no, posn from ASSM_ITEMS WHERE assm_ser_no="+serialNo+" AND ctype LIKE '%SiDetector%' ORDER BY posn");
     statement = SCTDBInterface.getInstance().connection.createStatement();
     if(DEBUG) System.out.println(sqlStat.toString());
     resultSet = statement.executeQuery(sqlStat.toString());
     recordCount=0;
     String sn;

     for(boolean n = resultSet.next() ; n==true ; n=resultSet.next() ){
          Hashtable tempHash = new Hashtable();
          tempHash.put("SERIALNO",resultSet.getString(1));
          tempHash.put("POSITION",resultSet.getString(2));
          sensorList.addElement(tempHash);
          }

     statement.close();
     return sensorList;
     }
//*****************************************************************************
public void getChipInfo(String hybridSN) throws Exception {

   Hashtable thisHash = new Hashtable();

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

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

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

}
//***********************************************************************************************
    public void getDataSheetInfo() {
      String bmSBserialNo = (snHash.containsKey(moduleParts[bmSB])) ? (String)snHash.get(moduleParts[bmSB]) : null;
      try {
          testDataHash = BarrelUtilities.getDataSheetStatus(moduleSerialNo,bmSBserialNo);
      }catch(Exception e){System.err.println(e.toString());}
      }
//*************************************************************************************************
    public void getBarrelMetrologyData() throws Exception {
      SCTDBInterface db = SCTDBInterface.getInstance();
      StringBuffer sqlStat = new StringBuffer("SELECT tests.test_no,tests.LOCN_NAME,tests.test_date,SCT_TSTBMSURVEYXY.event,tests.pass,tests.problem");
      for(int i=0;i<xyMetrologyParameters.length-2;i++) sqlStat.append(","+xyMetrologyParameters[i]);
      sqlStat.append(" FROM tests,SCT_TSTBMSURVEYXY WHERE tests.ser_no ="+moduleSerialNo+" AND SCT_TSTBMSURVEYXY.test_no=tests.test_no");
      sqlStat.append(" ORDER BY tests.test_date,tests.test_no");
     if(DEBUG) System.out.println(sqlStat.toString());
      statement = db.connection.createStatement();
      resultSet = statement.executeQuery(sqlStat.toString());
      for(boolean n = resultSet.next() ; n==true ; n=resultSet.next() ){
         String testno = resultSet.getString(1);
         String location = resultSet.getString(2);
         String date = guiUtilities.DaveUtils.extractDate(resultSet.getString(3));
         String event = resultSet.getString(4);
         String pass = resultSet.getString(5);
         String problem = resultSet.getString(6);

         int ncat = event.equals("SB") ? 13 : xyMetrologyParameters.length-2;
         int[] icategory= new int[ncat];
         for(int i=0;i<icategory.length;i++) {
             icategory[i] = BarrelUtilities.getXYStatus(i,resultSet.getString(7+i));
             }
         int category=0;
// exclusivity of midyf and stereo for categories PASS2 and SPARE
// if one is SPARE, then the other must be equal or less to next tightest cut, ie PASS2
// otherwise FAIL
        if(icategory[7]>=2 && icategory[12]>=2) {
              if(icategory[7]>icategory[12]) category=icategory[7];
              else if(icategory[7]<icategory[12]) category=icategory[12];
              else category=4;
              }
         for(int i=0;i<icategory.length;i++) {if(icategory[i]>category) category=icategory[i];}

         String cColor;
         switch(category) {
           case 1:
           case 2:
             cColor="purple";
             break;
           case 3:
           case 4:
             cColor="red";
             break;
           default:
             cColor="green";
             }


         MetrologyTest met = new MetrologyTest(testno, location, date, event, "XY", "<font color="+cColor+">"+categories[category]+"</font>");
         metrologyHash.put(testno,met);
         metrologyList.addElement(testno);
         }
      statement.close();


      sqlStat = new StringBuffer("SELECT tests.test_no,tests.LOCN_NAME,tests.test_date,SCT_TSTBMSURVEYZ.event,tests.pass,tests.problem");
      for(int i=0;i<zBarrelDBParameters.length-2;i++) sqlStat.append(","+zBarrelDBParameters[i]);
      sqlStat.append(" FROM tests,SCT_TSTBMSURVEYZ WHERE tests.ser_no ="+moduleSerialNo+" AND SCT_TSTBMSURVEYZ.test_no=tests.test_no");
      sqlStat.append(" ORDER BY tests.test_date,tests.test_no");
      if(DEBUG) System.out.println(sqlStat.toString());
      statement = SCTDBInterface.getInstance().connection.createStatement();
      resultSet = statement.executeQuery(sqlStat.toString());
      for(boolean n = resultSet.next() ; n==true ; n=resultSet.next() ){
         String testno = resultSet.getString(1);
         String location = resultSet.getString(2);
         String date = guiUtilities.DaveUtils.extractDate(resultSet.getString(3));
         String event = resultSet.getString(4);
         String pass = resultSet.getString(5);
         String problem = resultSet.getString(6);

         int ncat = zMetrologyParameters.length-2;
         int[] icategory= new int[ncat];
         for(int i=0;i<icategory.length;i++) {
             icategory[i] = BarrelUtilities.getZStatus(i,resultSet.getString(7+i));
             }
         int category=0;
         for(int i=0;i<icategory.length;i++) {if(icategory[i]>category) category=icategory[i];}

         String cColor;
         switch(category) {
           case 1:
           case 2:
             cColor="purple";
             break;
           case 3:
           case 4:
             cColor="red";
             break;
           default:
             cColor="green";
             }

         MetrologyTest met = new MetrologyTest(testno, location, date, event, "Z", "<font color="+cColor+">"+categories[category]+"</font>");
         metrologyHash.put(testno,met);
         metrologyList.addElement(testno);
         }
      statement.close();
      }

//*************************************************************************************************
    public void getEndCapMetrologyData() throws Exception {
      SCTDBInterface db = SCTDBInterface.getInstance();
      StringBuffer sqlStat = new StringBuffer("SELECT tests.test_no,tests.LOCN_NAME,tests.test_date,tests.pass,tests.problem");
      sqlStat.append(" FROM tests,TSTMODXY WHERE tests.ser_no ="+moduleSerialNo+" AND TSTMODXY.test_no=tests.test_no");
      sqlStat.append(" ORDER BY tests.test_date,tests.test_no");
     if(DEBUG) System.out.println(sqlStat.toString());
      statement = db.connection.createStatement();
      resultSet = statement.executeQuery(sqlStat.toString());
      for(boolean n = resultSet.next() ; n==true ; n=resultSet.next() ){
         String testno = resultSet.getString(1);
         String location = resultSet.getString(2);
         String date = guiUtilities.DaveUtils.extractDate(resultSet.getString(3));
         String pass = resultSet.getString(4);
         String problem = resultSet.getString(5);

         int category = (pass.equals("NO")) ? 2 : ( (problem.equals("NO"))? 0 : 1);

         String cColor;
         switch(category) {
           case 1:
             cColor="purple";
             break;
           case 2:
             cColor="red";
             break;
           default:
             cColor="green";
             }
         MetrologyTest met = new MetrologyTest(testno, location, date, "n/a", "XY", "<font color="+cColor+">"+endCapStatus[category]+"</font>");
         metrologyHash.put(testno,met);
         metrologyList.addElement(testno);
         }
      statement.close();


      sqlStat = new StringBuffer("SELECT tests.test_no,tests.LOCN_NAME,tests.test_date,tests.pass,tests.problem");
      sqlStat.append(" FROM tests,TSTMODZ WHERE tests.ser_no ="+moduleSerialNo+" AND TSTMODZ.test_no=tests.test_no");
      sqlStat.append(" ORDER BY tests.test_date,tests.test_no");
     if(DEBUG) System.out.println(sqlStat.toString());
      statement = SCTDBInterface.getInstance().connection.createStatement();
      resultSet = statement.executeQuery(sqlStat.toString());
      for(boolean n = resultSet.next() ; n==true ; n=resultSet.next() ){
         String testno = resultSet.getString(1);
         String location = resultSet.getString(2);
         String date = guiUtilities.DaveUtils.extractDate(resultSet.getString(3));
         String pass = resultSet.getString(4);
         String problem = resultSet.getString(5);

         int category = (pass.equals("NO")) ? 2 : ( (problem.equals("NO"))? 0 : 1);

         String cColor;
         switch(category) {
           case 1:
             cColor="purple";
             break;
           case 2:
             cColor="red";
             break;
           default:
             cColor="green";
             }


         MetrologyTest met = new MetrologyTest(testno, location, date, "n/a", "Z", "<font color="+cColor+">"+endCapStatus[category]+"</font>");
         metrologyHash.put(testno,met);
         metrologyList.addElement(testno);
         }
      statement.close();
      }

}

---