private void exportData(ArrayList<ArrayList<String>> list)
throws java.sql.SQLException, ClassNotSupportedException {
c.disconnect();
if (DetIDGenerator.mtcc) {
for (String[] s : TOBMTCC) {
ArrayList<String> n = new ArrayList<String>();
n.add(s[0]);
n.add(s[1]);
n.add(s[2]);
n.add(s[3]);
list.add(n);
}
}
if (DetIDGenerator.export) {
configureExportDatabaseConnection();
c.connect();
} else {
System.out.print(
"<?xml version=\"1.0\"?>\n<ROWSET xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:noNamespaceSchemaLocation='http://cmsdoc.cern.ch/cms/cmt/System_aspects/FecControl/binaries/misc/conversionSchema.xsd'>\n");
}
for (int i = 0; i < list.size(); i++) {
ArrayList<String> v = list.get(i);
int dcuID = Integer.parseInt(v.get(0));
int detID = Integer.parseInt(v.get(1));
float length = new java.lang.Float(v.get(2));
int apvNumber = Integer.parseInt(v.get(3));
if (DetIDGenerator.export) {
int res =
((OracleConnection) c)
.callFunction("PkgDcuInfo.setValues", dcuID, detID, length, apvNumber);
// ArrayList<ArrayList<String>> count = c.selectQuery("select count(*) from dcuInfo");
// if(count.size()!=0){
// int nbOk = Integer.parseInt(count.get(0).get(0));
// System.out.println(v);
// System.out.println("Trace : Nb in db : "+nbOk+"\nInsertion Nb : "+i+"\nPLSQL result
// :"+res+"\n");
// }
} else {
System.out.println(
"<DCUINFO dcuHardId=\""
+ dcuID
+ "\" detId=\""
+ detID
+ "\" fibreLength=\""
+ length
+ "\" apvNumber=\""
+ apvNumber
+ "\" />");
}
if (DetIDGenerator.verbose) System.out.print(((i * 100) / list.size()) + " %\r");
}
if (!DetIDGenerator.export) {
System.out.println("</ROWSET>");
}
}
private void configureExportDatabaseConnection() throws java.sql.SQLException {
String dbString = System.getProperty("CONFDB");
if (dbString == null
|| dbString.equals("")
|| dbString.indexOf('/') == -1
|| dbString.indexOf('@') == -1)
throw new java.sql.SQLException("No valid $CONFDB variable found : can not connect!");
String user = dbString.substring(0, dbString.indexOf('/'));
String password = dbString.substring(dbString.indexOf('/') + 1, dbString.indexOf('@'));
String url = dbString.substring(dbString.indexOf('@') + 1, dbString.length());
url = "jdbc:oracle:thin:@" + url;
c.setUser(user);
c.setUrl(url);
c.setPassword(password);
}
/** Default Constructor */
public DetIDGenerator() {
query = new String();
try {
c = CDBConnection.getConnection();
if (DetIDGenerator.export || DetIDGenerator.updateCB) {
/*
Just to check that we can connect to the export database
Better to see it now rather than after all the computing...
*/
configureExportDatabaseConnection();
c.connect();
c.disconnect();
// Ok it's working, let's go!
}
c.setUser("prod_consult");
c.setUrl(
"jdbc:oracle:thin:@(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = ccdbcl01.in2p3.fr)(PORT = 1521))(ADDRESS = (PROTOCOL = TCP)(HOST = ccdbcl02.in2p3.fr)(PORT = 1521))(LOAD_BALANCE = yes)(CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = cccmstrktaf.in2p3.fr)(FAILOVER_MODE =(TYPE = SELECT)(METHOD = BASIC)(RETRIES = 180)(DELAY = 5))))");
c.setPassword("am8bilo8gy");
c.connect();
} catch (java.sql.SQLException e) {
Error("SQL Error : \n" + query + "\n" + e.getMessage());
} catch (java.lang.ClassNotFoundException e) {
Error("Can not find Oracle driver");
}
}
/**
* Perform the treatment :
*
* <ul>
* <li>Get the Det IDs for TEC and TOB
* <li>Get the DCU IDs associated to the module IDs
* <li>Get the fiber's length
* <li>Get the number of APVs
* <li>Export the data to a DB or print them on the screen
* </ul>
*/
public void go() {
try {
ArrayList<ArrayList<String>> list = new ArrayList<ArrayList<String>>();
IDetIdGenerator tec = new TECAnalyzer();
IDetIdGenerator tob = new TOBAnalyzer();
list.addAll(tec.getDetIds());
list.addAll(tob.getDetIds());
if (DetIDGenerator.verbose) System.out.println(list.size() + " modules found");
if (!DetIDGenerator.updateCB) {
if (DetIDGenerator.verbose) System.out.println("Retrieving the fibers length...");
getFiberLength(list);
}
if (DetIDGenerator.verbose) System.out.println("Converting DetIds to 32 bits...");
compactDetIds(list);
if (!DetIDGenerator.updateCB) {
if (DetIDGenerator.verbose) System.out.println("Retrieving the number of APVs...");
getApvNumber(list);
}
if (DetIDGenerator.verbose) System.out.println("Searching the DCU ids...");
getDCU(list);
if (DetIDGenerator.verbose) System.out.println("Reversing the DCU ids...");
reverseDcuIds(list);
if (!DetIDGenerator.updateCB) {
if (DetIDGenerator.verbose) System.out.println("Exporting...");
exportData(list);
} else {
System.out.println("updating the construction DB...");
updateConstructionDB(list);
}
c.disconnect();
} catch (java.sql.SQLException e) {
Error("SQL Error : \n" + query + "\n" + e.getMessage());
} catch (ClassNotSupportedException e) {
Error("ClassNotSupportedException :\n" + e.getMessage());
} catch (java.lang.ClassNotFoundException e) {
Error("Can not find Oracle driver");
} catch (Exception e) {
Error("Error : \n" + e.getMessage());
}
}
private void getApvNumber(ArrayList<ArrayList<String>> list) throws java.sql.SQLException {
PreparedStatement psType =
c.createPreparedStatement(
"select OD.type_description from "
+ "cmstrkdb.object_assembly OA, "
+ "cmstrkdb.object_description OD WHERE"
+ " OA.object=OD.object AND OA.type=OD.type AND "
+ "OA.version=OD.version AND OA.object='HYB' AND "
+ "OA.container_id=?");
for (int i = 0; i < list.size(); i++) {
ArrayList<String> v = list.get(i);
ArrayList<ArrayList<String>> res = c.preparedSelectQuery(psType, v.get(0));
if (res.size() > 0) {
String type = (res.get(0)).get(0);
type = type.substring(type.indexOf('.') + 1, type.length() - 1);
v.add(type);
} else {
v.add("4");
Error("Type of hybrid contained in module " + v.get(0) + " unknown!!");
}
if (DetIDGenerator.verbose) System.out.print(((i * 100) / list.size()) + " %\r");
}
psType.close();
}
/**
* Retrieve the det_id from a dcuHardId
*
* @param dcuId The dcuHard Id
* @return The det_id corresponding to the dcu_id
*/
public int getDetId(int dcuId) throws java.sql.SQLException {
ArrayList<ArrayList<String>> res =
c.selectQuery("select detid from dcuInfo where dcuhardid=" + dcuId);
if (res.size() == 0) throw new java.sql.SQLException("No detId found for DcuId " + dcuId);
else return Integer.parseInt((res.get(0)).get(0));
}
private void updateConstructionDB(ArrayList<ArrayList<String>> list)
throws java.sql.SQLException, ClassNotSupportedException, java.lang.Exception {
if (DetIDGenerator.updateCB) {
c.disconnect();
configureExportDatabaseConnection();
c.connect();
c.beginTransaction();
c.executeQuery("delete tec_detid");
c.executeQuery("delete tob_detid");
for (ArrayList<String> record : list) {
int dcuID = Integer.parseInt(record.get(0));
int detID = Integer.parseInt(record.get(1));
DetIdConverter det = new DetIdConverter(detID);
if (det.getSubDetector() == 6) { // TEC
TECDetIdConverter d = new TECDetIdConverter(detID);
d.compact();
String query =
"insert into tec_detid (DETECTOR,DISK,SECTOR,FRONT_BACK,RING,POSITION,STEREO,DCUID,DETID) values (\'TEC"
+ (d.getTEC() == 1 ? "-" : "+")
+ "\',"
+ d.getWheel()
+ ","
+ d.getPetal()
+ ",'"
+ (d.getFrontBack() == 1 ? "F" : "B")
+ "',"
+ d.getRing()
+ ","
+ d.getModNumber()
+ ",'"
+ ((d.getStereo() == 1) ? "S" : (d.getStereo() == 0 ? "G" : "M"))
+ "',"
+ dcuID
+ ","
+ detID
+ ")";
System.out.println(query);
c.executeQuery(query);
}
if (det.getSubDetector() == 5) { // TOB
TOBDetIdConverter d = new TOBDetIdConverter(detID);
d.compact();
String query =
"insert into tob_detid (LAYER,ROD,FRONT_BACK,POSITION,STEREO,DCUID,DETID) values ("
+ d.getLayer()
+ ","
+ d.getRod()
+ ",'"
+ (d.getFrontBack() == 1 ? "F" : "B")
+ "',"
+ d.getModNumber()
+ ",'"
+ ((d.getStereo() == 1) ? "S" : (d.getStereo() == 0 ? "U" : "R"))
+ "',"
+ dcuID
+ ","
+ detID
+ ")";
System.out.println(query);
c.executeQuery(query);
// System.out.println(dcuID+","+detID+",TOB,"+d.getLayer()+","+d.getRod()+","+d.getFrontBack()+","+d.getModNumber()+","+d.getStereo());
}
}
c.commit();
}
}
private void getDCU(ArrayList<ArrayList<String>> list) throws java.sql.SQLException {
// 1 method for TOB dcus
PreparedStatement tob =
c.createPreparedStatement(
"select MB.dcuhardid from "
+ "cmstrkdb.TOBTESTINGMODULEBASIC_1_MOD_ MB "
+ "where MB.status='reference' AND MB.object_id=?");
// 4 different methods for TEC (trye the first, if fails try the second ...)
PreparedStatement psTec1 =
c.createPreparedStatement(
"select MB.dcuid from cmstrkdb.modvalidation_2_mod_ MV, "
+ "cmstrkdb.modulbasic_2_mod_ MB "
+ "where MB.test_id=MV.modulbasic_2_mod_ AND "
+ "MV.status='reference' AND MV.object_id=?");
PreparedStatement psTec2 =
c.createPreparedStatement(
"select distinct FP.dcu_id from "
+ "cmstrkdb.hybproducer_1_hyb_ HP, "
+ "cmstrkdb.object_assembly OA, "
+ "cmstrkdb.fhitproduction_1_hyb_ FP where "
+ "HP.fhitproduction_1_hyb_=FP.test_id AND "
+ "OA.object_id=HP.object_id AND OA.object='HYB'"
+ " AND OA.container_id=?");
PreparedStatement psTec3 =
c.createPreparedStatement(
"select distinct FR.dcu_id from "
+ "cmstrkdb.hybmeasurements_2_hyb_ HM, "
+ "cmstrkdb.object_assembly OA, "
+ "cmstrkdb.fhitreception_1_hyb_ FR where "
+ "HM.fhitreception_1_hyb_=FR.test_id AND "
+ "OA.object_id=HM.object_id AND OA.object='HYB' "
+ "AND OA.container_id=?");
PreparedStatement psTec4 =
c.createPreparedStatement(
"select distinct FP.dcu_id from "
+ "cmstrkdb.object_assembly OA, "
+ "cmstrkdb.fhitproduction_1_hyb_ FP where "
+ "OA.object_id=FP.object_id AND OA.object='HYB' "
+ "AND OA.container_id=?");
for (int i = 0; i < list.size(); i++) {
ArrayList<String> v = list.get(i);
DetIdConverter det = new DetIdConverter(Integer.parseInt(v.get(1)));
if (det.getSubDetector() == 6) { // TEC
ArrayList<ArrayList<String>> res = c.preparedSelectQuery(psTec1, v.get(0));
if (res.size() == 1 && (res.get(0)).get(0) != null && !(res.get(0)).get(0).equals("0")) {
ArrayList<String> detail = res.get(0);
v.set(0, detail.get(0));
} else {
res = c.preparedSelectQuery(psTec2, v.get(0));
if (res.size() == 1 && (res.get(0)).get(0) != null && !(res.get(0)).get(0).equals("0")) {
ArrayList<String> detail = res.get(0);
v.set(0, detail.get(0));
} else {
res = c.preparedSelectQuery(psTec3, v.get(0));
if (res.size() == 1
&& (res.get(0)).get(0) != null
&& !(res.get(0)).get(0).equals("0")) {
ArrayList<String> detail = res.get(0);
v.set(0, detail.get(0));
} else {
res = c.preparedSelectQuery(psTec4, v.get(0));
if (res.size() == 1
&& (res.get(0)).get(0) != null
&& !(res.get(0)).get(0).equals("0")) {
ArrayList<String> detail = res.get(0);
v.set(0, detail.get(0));
} else {
Error("DCU_ID of module " + v.get(0) + " unknown!!");
}
}
}
}
}
if (det.getSubDetector() == 5) { // TOB
ArrayList<ArrayList<String>> res = c.preparedSelectQuery(tob, v.get(0));
if (res.size() == 1 && (res.get(0)).get(0) != null && !(res.get(0)).get(0).equals("0")) {
ArrayList<String> detail = res.get(0);
int dcuId = reverseDcuId(Integer.parseInt(detail.get(0)));
v.set(0, dcuId + "");
} else {
Error("DCU_ID of module " + v.get(0) + " unknown!!");
v.set(0, "0");
}
}
if (DetIDGenerator.verbose) System.out.print(((i * 100) / list.size()) + " %\r");
}
tob.close();
psTec1.close();
psTec2.close();
psTec3.close();
psTec4.close();
}
private void getFiberLength(ArrayList<ArrayList<String>> list) throws java.sql.SQLException {
PreparedStatement modules =
c.createPreparedStatement(
"select OA2.object_id, OA2.number_in_container from cmstrkdb.object_assembly OA, cmstrkdb.object_assembly OA2 WHERE OA2.object='AOH' AND OA2.number_in_container=OA.number_in_container AND OA2.container_id=OA.container_id AND OA.object_id=?");
PreparedStatement aoh =
c.createPreparedStatement(
"select G.fiber_length from cmstrkdb.aohgeneral_1_aoh_ G, cmstrkdb.aohcomp_1_aoh_ C where C.aohgeneral_1_aoh_=G.test_id AND C.object_id=?");
PreparedStatement length =
c.createPreparedStatement(
"select distinct D.fanout_length from cmstrkdb.manufacture_1_optfanout_ M, cmstrkdb.diamond_1_optfanout_ D, cmstrkdb.object_assembly FANOUT, cmstrkdb.object_assembly AOH, cmstrkdb.object_assembly LASTRANS, cmstrkdb.link L WHERE M.diamond_1_optfanout_=D.test_id AND M.object_id=FANOUT.object_id AND L.object_id_b=FANOUT.object_id AND L.object_id_a=LASTRANS.object_id AND LASTRANS.container_id=AOH.object_id AND AOH.object_id=?");
for (int i = 0; i < list.size(); i++) {
try {
ArrayList<String> v = list.get(i);
String cDetID = new String(v.get(1));
String[] det_id = cDetID.split("\\.");
int tec = Integer.parseInt(det_id[2]);
int disk = Integer.parseInt(det_id[3]);
int front = Integer.parseInt(det_id[4]);
int sector = Integer.parseInt(det_id[5]);
// System.out.println(tec+" "+disk+" "+front+" "+sector);
ArrayList<ArrayList<String>> res = c.preparedSelectQuery(modules, v.get(0));
if (res.size() > 0) {
String aoh_id = (res.get(0)).get(0);
int aoh_position = Integer.parseInt((res.get(0)).get(1));
ArrayList<ArrayList<String>> lengthRes = c.preparedSelectQuery(aoh, aoh_id);
if (lengthRes.size() > 0) {
int aoh_length = Integer.parseInt((lengthRes.get(0)).get(0));
ArrayList<ArrayList<String>> lengthRes2 = c.preparedSelectQuery(length, aoh_id);
if (lengthRes2.size() == 1) {
float total_length = aoh_length + Integer.parseInt((lengthRes2.get(0)).get(0));
v.add((total_length / 10) + ""); // The size should be in centimeters
} else {
v.add("1");
if (lengthRes2.size() > 1)
throw new Exception("Can not find unique length for the aoh " + aoh_id);
else throw new Exception("Data are missing for this AOH : " + aoh_id);
}
} else {
v.add("1");
throw new Exception("Can not find the length of the aoh " + aoh_id);
}
} else {
v.add("1");
throw new Exception("Can not find the AOH corresponding to module " + v.get(0));
}
if (DetIDGenerator.verbose) System.out.print(((i * 100) / list.size()) + " %\r");
} catch (java.sql.SQLException e) {
throw e;
} catch (Exception e) {
Error(e.getMessage());
}
}
// Close the statements
modules.close();
aoh.close();
length.close();
}
