00001 package ProdDatabase;
00002
00003
00004
00005 import java.sql.*;
00006 import java.io.*;
00007 import java.util.*;
00008
00009 public class ModuleIVInfo implements SCTDBInfo {
00010 Statement statement;
00011 ResultSet resultSet;
00012 StringBuffer sqlStat;
00013 SCTDBInterface db;
00014
00015 private Vector sensors,modIVs;
00016 String moduleSerialNo;
00017 Hashtable i150Hash, i350Hash, temperatureHash, testnoHash;
00018 Hashtable moduleTestNoHash;
00019 String dataOrigin;
00020 private double tMin,tMax, i150Total, i350Total, i500Total;
00021 java.text.DecimalFormat ivFormatter = new java.text.DecimalFormat("#.###");
00022 java.text.DecimalFormat temperatureFormatter = new java.text.DecimalFormat("#.#");
00023
00024 boolean DEBUG=false;
00025
00026 public ModuleIVInfo(String moduleSerialNo) {
00027 this.moduleSerialNo=moduleSerialNo;
00028 sensors=new Vector();
00029 initVariables();
00030 }
00031
00032 public ModuleIVInfo(String moduleSerialNo, Vector sensors) {
00033 this.moduleSerialNo=moduleSerialNo;
00034 this.sensors=sensors;
00035 initVariables();
00036 }
00037 private void initVariables() {
00038 modIVs = new Vector();
00039 i150Hash = new Hashtable();
00040 i350Hash = new Hashtable();
00041 temperatureHash = new Hashtable();
00042 testnoHash = new Hashtable();
00043 moduleTestNoHash = new Hashtable();
00044 dataOrigin = new String(" - ");
00045 tMin=100.;
00046 tMax=0.;
00047 i150Total=0.;
00048 i350Total=0.;
00049 i500Total=0.;
00050
00051 db = SCTDBInterface.getInstance();
00052 try {
00053 if(sensors.size()>0) {
00054 getSensorIVInfo();
00055 if(i150Hash.size()!=sensors.size()) getMfrIVInfo();
00056 if(i150Hash.size()==sensors.size()) getSensorSums();
00057 }
00058 getModuleIVs();
00059 getModule500VData();
00060 getModuleIVPlots();
00061 System.out.println("done");
00062 }catch(Exception e){System.err.println(e.toString());}
00063
00064 }
00065
00066 public double geti150SensorCurrent() {
00067 return i150Total;
00068 }
00069 public double geti350SensorCurrent() {
00070 return i350Total;
00071 }
00072 public double geti500SensorCurrent() {
00073 return i500Total;
00074 }
00075 public String getTemperatureRange() {
00076 if(tMax==0.) return " - ";
00077 return temperatureFormatter.format(tMin)+"-"+temperatureFormatter.format(tMax);
00078 }
00079 public String getOrigin() {
00080 return dataOrigin;
00081 }
00082 public int noModuleIVs() {
00083 return modIVs.size();
00084 }
00085 public ModuleIV getModuleIV(int index) {
00086 String testno = (String)modIVs.elementAt(index);
00087 return (ModuleIV)moduleTestNoHash.get(testno);
00088 }
00089 public ModuleIV getLastModuleIV() {
00090 String testno = (String)modIVs.lastElement();
00091 return (ModuleIV)moduleTestNoHash.get(testno);
00092 }
00093
00094
00095
00096 private void getSensorIVInfo() throws Exception {
00097
00098 if(sensors.size()==0) return;
00099 StringBuffer sensorList = new StringBuffer();
00100 for(int i=0;i<sensors.size();i++) {
00101 if(i>0) sensorList.append(",");
00102 Hashtable tempHash = (Hashtable)sensors.elementAt(i);
00103 sensorList.append((String)tempHash.get("SERIALNO"));
00104 }
00105
00106 if(!db.isConnected()) throw new Exception("No database connection");
00107 StringBuffer sqlStat = new StringBuffer("SELECT ");
00108 sqlStat.append("tests.ser_no,tstdetivs.i_leak_150,tstdetivs.i_leak_350,tstdetivs.temperature, tests.locn_name,tests.test_no");
00109 sqlStat.append(" FROM tests,tstdetivs");
00110 sqlStat.append(" WHERE tests.ser_no IN ("+sensorList.toString()+")");
00111 sqlStat.append(" AND tests.test_name='DetIVscan'");
00112 sqlStat.append(" AND tstdetivs.test_no=tests.test_no AND tstdetivs.temperature>0");
00113 sqlStat.append(" ORDER BY tests.test_date,tests.test_no");
00114
00115 statement = db.connection.createStatement();
00116 if(DEBUG) System.out.println(sqlStat.toString());
00117 resultSet = statement.executeQuery(sqlStat.toString());
00118
00119 for(boolean n = resultSet.next() ; n==true ; n=resultSet.next() ){
00120 String sn = resultSet.getString(1);
00121 i150Hash.put(sn,resultSet.getString(2));
00122 i350Hash.put(sn,resultSet.getString(3));
00123 temperatureHash.put(sn,resultSet.getString(4));
00124 dataOrigin=resultSet.getString(5);
00125 testnoHash.put(sn,resultSet.getString(6));
00126 }
00127 statement.close();
00128
00129
00130 }
00131
00132 private void getMfrIVInfo() throws Exception {
00133 if(!db.isConnected()) throw new Exception("No database connection");
00134 StringBuffer sensorList = new StringBuffer();
00135 for(int i=0;i<sensors.size();i++) {
00136 if(i>0) sensorList.append(",");
00137 Hashtable tempHash = (Hashtable)sensors.elementAt(i);
00138 sensorList.append((String)tempHash.get("SERIALNO"));
00139 }
00140 StringBuffer sqlStat = new StringBuffer("SELECT tests.ser_no,i_leak_150,i_leak_350,temperature,tests.locn_name");
00141 sqlStat.append(" FROM tests,tstdetmfrs WHERE tstdetmfrs.test_no=tests.test_no");
00142 sqlStat.append(" AND tests.ser_no IN ("+sensorList.toString()+")");
00143 sqlStat.append(" AND tests.test_name='DetMfrSheet'");
00144 sqlStat.append(" ORDER BY tests.test_date,tests.test_no");
00145
00146 statement = db.connection.createStatement();
00147 if(DEBUG) System.out.println(sqlStat.toString());
00148 resultSet = statement.executeQuery(sqlStat.toString());
00149
00150 for(boolean n = resultSet.next() ; n==true ; n=resultSet.next() ){
00151 String sn = resultSet.getString(1);
00152 i150Hash.put(sn,resultSet.getString(2));
00153 i350Hash.put(sn,resultSet.getString(3));
00154 temperatureHash.put(sn,resultSet.getString(4));
00155 dataOrigin=resultSet.getString(5);
00156 }
00157 statement.close();
00158 }
00159
00160 private void getSensorSums() {
00161 int i500Count=0;
00162 for(Enumeration e = i150Hash.keys(); e.hasMoreElements();) {
00163 String sn = (String)e.nextElement();
00164 String testno = (String)testnoHash.get(sn);
00165 String i150 = (String)i150Hash.get(sn);
00166 String i350 = (String)i350Hash.get(sn);
00167 double temperature = Double.parseDouble((String)temperatureHash.get(sn));
00168 try {
00169 double i500 = GeneralUtilities.get500VData(testno);
00170 if(i500>0.) i500Count++;
00171 i500Total+=guiUtilities.DaveUtils.getCorrectedCurrent(i500, temperature);
00172 }catch(Exception e2){System.err.println(e2.toString());}
00173 i150Total+= guiUtilities.DaveUtils.getCorrectedCurrent(Double.parseDouble(i150), temperature);
00174 i350Total+= guiUtilities.DaveUtils.getCorrectedCurrent(Double.parseDouble(i350), temperature);
00175 if(temperature>tMax) tMax=temperature;
00176 if(temperature<tMin) tMin=temperature;
00177 }
00178 if(i500Count!=i150Hash.size()) i500Total=0.;
00179 }
00180
00181 private void getModuleIVs() throws Exception {
00182
00183 StringBuffer sqlStat = new StringBuffer("SELECT ");
00184 sqlStat.append("tests.ser_no,tests.test_date,tests.locn_name,tests.problem,tests.pass,tstdetivs.i_leak_150,tstdetivs.i_leak_350,tstdetivs.temperature,tests.test_no");
00185 sqlStat.append(" FROM tests,tstdetivs");
00186 sqlStat.append(" WHERE tests.ser_no="+moduleSerialNo+" AND tests.test_name='DetModIV'");
00187 sqlStat.append(" AND tstdetivs.test_no=tests.test_no AND tstdetivs.temperature>0");
00188 sqlStat.append(" ORDER BY tests.test_date,tests.test_no");
00189
00190 statement = db.connection.createStatement();
00191 if(DEBUG) System.out.println(sqlStat.toString());
00192 resultSet = statement.executeQuery(sqlStat.toString());
00193
00194 for(boolean n = resultSet.next() ; n==true ; n=resultSet.next() ){
00195 String sn = resultSet.getString(1);
00196 String testDate = guiUtilities.DaveUtils.extractDate(resultSet.getString(2));
00197 String locn = resultSet.getString(3);
00198 String problem = resultSet.getString(4);
00199 String pass = resultSet.getString(5);
00200 String i150 = resultSet.getString(6);
00201 String i350 = resultSet.getString(7);
00202 String temperature = resultSet.getString(8);
00203 String testno = resultSet.getString(9);
00204 String status = "FAIL";
00205 if(pass.equals("YES")) {
00206 status = (problem.equals("YES")) ? "Problem" : "Ok";
00207 }
00208 double ii150 = guiUtilities.DaveUtils.getCorrectedCurrent(Double.parseDouble(i150), Double.parseDouble(temperature));
00209 double ii350 = guiUtilities.DaveUtils.getCorrectedCurrent(Double.parseDouble(i350), Double.parseDouble(temperature));
00210 ModuleIV iv = new ModuleIV(sn,testDate,status,temperature,locn,testno,ii150,ii350);
00211 moduleTestNoHash.put(testno,iv);
00212 modIVs.add(testno);
00213 }
00214 statement.close();
00215 }
00216
00217 private void getModule500VData() {
00218 for(int i=0;i<modIVs.size();i++) {
00219 String testno = (String)modIVs.elementAt(i);
00220 ModuleIV iv = (ModuleIV)moduleTestNoHash.get(testno);
00221 String temperature = iv.getTemperature();
00222 try {
00223 double i500 = GeneralUtilities.get500VData(testno);
00224 i500 = guiUtilities.DaveUtils.getCorrectedCurrent(i500, Double.parseDouble(temperature));
00225 iv.add500VData(i500);
00226 moduleTestNoHash.put(testno,iv);
00227 }catch(Exception e2){System.err.println(e2.toString());}
00228 }
00229 }
00230
00231 private void getModuleIVPlots() throws Exception {
00232 if(moduleTestNoHash.size()==0) return;
00233 int i=0;
00234 StringBuffer testnos = new StringBuffer();
00235 for(Enumeration e = moduleTestNoHash.keys(); e.hasMoreElements();) {
00236 if(i>0) testnos.append(",");
00237 i++;
00238 testnos.append((String)e.nextElement());
00239 }
00240 StringBuffer sqlStat = new StringBuffer("SELECT ");
00241 sqlStat.append("test_no,url FROM web_links");
00242 sqlStat.append(" WHERE test_no IN ("+testnos.toString()+")");
00243
00244 statement = db.connection.createStatement();
00245 if(DEBUG) System.out.println(sqlStat.toString());
00246 resultSet = statement.executeQuery(sqlStat.toString());
00247
00248 for(boolean n = resultSet.next() ; n==true ; n=resultSet.next() ){
00249 String testno = resultSet.getString(1);
00250 String url = resultSet.getString(2);
00251 ModuleIV iv = (ModuleIV)moduleTestNoHash.get(testno);
00252 iv.addLink(url);
00253 moduleTestNoHash.put(testno,iv);
00254 }
00255 statement.close();
00256
00257 }
00258
00259
00260 }
