Ext xref

View Javadoc

1   package org.opensync.engine.xslt.extensions;
2   
3   import java.sql.Connection;
4   import java.sql.PreparedStatement;
5   import java.sql.ResultSet;
6   import java.sql.SQLException;
7   import java.text.ParseException;
8   import java.text.SimpleDateFormat;
9   import java.util.ArrayList;
10  import java.util.Calendar;
11  import java.util.Date;
12  import java.util.Hashtable;
13  
14  import net.sf.saxon.expr.XPathContext;
15  import net.sf.saxon.om.SequenceIterator;
16  import net.sf.saxon.xpath.XPathException;
17  
18  import org.opensync.tools.Utils;
19  
20  
21  /***
22   * @author	warzee, chapuis
23   */
24  public class Ext extends XSLTExtensionFunction {
25  
26    private static byte[] lock = new byte[0];
27  
28    /***
29     * This function returns the current date of the system. <br/> Function compliant with Saxon extension API.
30     * @param context : xpath context
31     * @param nsv : empty
32     * @return String
33     * @throws XPathException
34     */
35    public static String date_system( XPathContext context, SequenceIterator nsv) throws XPathException {
36      return date_system(getNodeList(context, nsv) );
37    }
38  
39    public static String date_system(org.w3c.dom.NodeList args) {
40      // Get the first arg: formatDate
41      String formatDate = args.item(0).getNodeValue();
42  
43      //System.out.println("My function date_system(String formatDate) called with........"+formatDate);
44      Calendar calendar = Calendar.getInstance();
45      SimpleDateFormat dateFormat = new SimpleDateFormat(formatDate);
46      Date date = calendar.getTime();
47      return dateFormat.format(calendar.getTime());
48    }
49  
50    /***
51     * This function returns a date with the specified format <br/> Function compliant with Saxon extension API.
52     * @param context : xpath context
53     * @param nsv : dataDate : date to format, formatDateIn : the current format of the date in input, formatDateOut : the specified format to return the date
54     * @return String
55     * @throws XPathException
56     */
57    public static String formatDate( XPathContext context, SequenceIterator nsv) throws XPathException {
58      return formatDate(getNodeList(context, nsv) );
59    }
60  
61    public static String formatDate(org.w3c.dom.NodeList args) {
62  
63    if (args.getLength() != 3) return "";
64  
65    String dataDate = args.item(0).getNodeValue();
66    String formatDateIn = args.item(1).getNodeValue();
67    String formatDateOut = args.item(2).getNodeValue();
68  
69    //System.out.println("myFormatDate(String dataDate,String formatDateIn,String formatDateOut) called with........"+dataDate+","+formatDateIn+","+formatDateOut);
70    SimpleDateFormat dateFormatIn,dateFormatOut;
71    Calendar calendar = Calendar.getInstance();
72    dateFormatIn = new SimpleDateFormat(formatDateIn);
73    if (dataDate.trim().length() < 8)
74      return "";
75  
76    try {
77      calendar.setTime(dateFormatIn.parse(dataDate));
78    } catch (ParseException p) {
79      return "";
80    }
81    dateFormatOut = new SimpleDateFormat(formatDateOut);
82    return dateFormatOut.format(calendar.getTime());
83    }
84  
85    /***
86     * Function compliant with Saxon extension API
87     * spaceLength
88     * @param context
89     * @param nsv
90     * @return
91     * @throws XPathException
92     */
93    public static String spaceLength( XPathContext context, SequenceIterator nsv) throws XPathException {
94      return spaceLength(getNodeList(context, nsv) );
95    }
96  
97    public static String spaceLength(org.w3c.dom.NodeList args) {
98      String length = args.item(0).getNodeValue();
99      int _length;
100     StringBuffer str;
101     //System.out.println("spaceLength(String length) called with........"+length);
102     _length = (int)Integer.parseInt(length);
103     str = new StringBuffer("");
104     for (int i=0;i<_length;i++) str.append(" ");
105     return str.toString();
106   }
107 
108   /***
109    * truncateString. <br/> Function compliant with Saxon extension API
110    * Actual args:
111    * data: string to truncate
112    * length: length of the returned string
113    * @param context
114    * @param nsv
115    * @return
116    * @throws XPathException
117    */
118   public static String truncateString( XPathContext context, SequenceIterator nsv) throws XPathException {
119     return truncateString(getNodeList(context, nsv) );
120   }
121 
122   public static String truncateString(org.w3c.dom.NodeList args) {
123     String data = args.item(0).getNodeValue();
124     String length = args.item(1).getNodeValue();
125 
126     int _length;
127     String _data;
128 
129     _data = data.trim();
130     _length = (int)Integer.parseInt(length);
131     if (_data.length()==0) return "";
132     if (_data.length()>_length)  _data = _data.substring(0,_length-1);
133     return _data;
134   }
135 
136   /***
137    * subStringWithFactor. <br/> Function compliant with Saxon extension API
138    * @param context
139    * @param nsv: data, length, sens, charRemplacement, facteurX
140    * @return
141    * @throws XPathException
142    * @throws NumberFormatException
143    */
144   public static String subStringWithFactor( XPathContext context, SequenceIterator nsv) throws XPathException, NumberFormatException {
145     return subStringWithFactor(getNodeList(context, nsv) );
146   }
147 
148   public static String subStringWithFactor(org.w3c.dom.NodeList args) throws NumberFormatException {
149     String data = args.item(0).getNodeValue();
150     String length = args.item(1).getNodeValue();
151     String sens = args.item(2).getNodeValue();
152     String charRemplacement = args.item(3).getNodeValue();
153     String facteurX = args.item(4).getNodeValue();
154 
155     long value;
156     double dataNumeric;
157     int diff,_length,_facteurX;
158     String _data,dataReturn="";
159     StringBuffer str;
160 
161     /*
162     System.out.println("***************SubString avec 5 Arguments **************");
163     System.out.println("data : "+data);
164     System.out.println("length : "+length);
165     System.out.println("sens : "+sens);
166     System.out.println("charRemplacement : "+charRemplacement);
167     System.out.println("facteurX : "+facteurX);
168     */
169     _length = Integer.parseInt(length);
170     _facteurX = Integer.parseInt(facteurX);
171 
172     if (data.trim().equals("")) dataNumeric = 0;
173     else {
174       try {
175         dataNumeric = Double.parseDouble(data);
176       } catch (NumberFormatException nfe) {
177 	throw nfe;
178       }
179     }
180 
181     value = (long)(dataNumeric*_facteurX);
182     _data = new Long(value).toString();
183     diff = _length-_data.trim().length();
184     str = new StringBuffer("");
185     for (int i=0;i<diff;i++) str.append(charRemplacement);
186 
187     // troquer chaine si dépassement
188     if (diff<0) {
189       dataReturn = _data.trim().substring(0,_length);
190       return dataReturn;
191     }
192     // compléter à gauche ou à droite la chaine vide
193     if (sens.equals("RIGHT")) dataReturn =  str.toString()+_data.trim();
194     else dataReturn =  _data.trim()+str.toString();
195 
196     return dataReturn;
197   }
198 
199 
200   /***
201    * lastChar. <br/> Function compliant with Saxon extension API
202    * @param context
203    * @param nsv: data, length, sens, charRemplacement,
204    * @return String
205    * @throws XPathException
206    * @throws NumberFormatException
207    */
208   public static String lastChar( XPathContext context, SequenceIterator nsv) throws XPathException, NumberFormatException {
209     return lastChar(getNodeList(context, nsv) );
210   }
211 
212   public static String lastChar(org.w3c.dom.NodeList args) throws NumberFormatException {
213 
214     String data = args.item(0).getNodeValue();
215 
216     int length = data.length();
217 
218     return data.substring(length - 2, length - 1);
219   }
220 
221   /***
222    * removeLastChar. <br/> Function compliant with Saxon extension API
223    * @param context
224    * @param nsv: data, length, sens, charRemplacement,
225    * @return String
226    * @throws XPathException
227    * @throws NumberFormatException
228    */
229   public static String removeLastChar( XPathContext context, SequenceIterator nsv) throws XPathException, NumberFormatException {
230     return removeLastChar(getNodeList(context, nsv) );
231   }
232 
233   public static String removeLastChar(org.w3c.dom.NodeList args) throws NumberFormatException {
234 
235     String data = args.item(0).getNodeValue();
236 
237     int length = data.length();
238 
239     return data.substring(0, length - 2);
240   }
241 
242   /***
243    * subString. <br/> Function compliant with Saxon extension API
244    * @param context
245    * @param nsv: data, length, sens, charRemplacement,
246    * @return String
247    * @throws XPathException
248    * @throws NumberFormatException
249    */
250   public static String subString( XPathContext context, SequenceIterator nsv) throws XPathException, NumberFormatException {
251     return subString(getNodeList(context, nsv) );
252   }
253 
254   public static String subString(org.w3c.dom.NodeList args) throws NumberFormatException {
255 
256     if (args.getLength() != 4) {
257       System.out.println("Call to XSLTExtensionFunction::subString usage: data, length, sens ('RIGHT', 'LEFT'), charRemplacement");
258       return "";
259     }
260     String data = args.item(0).getNodeValue();
261     String length = args.item(1).getNodeValue();
262     String sens = args.item(2).getNodeValue();
263     String charRemplacement = args.item(3).getNodeValue();
264 
265     int diff,_length;
266     String dataReturn="";
267     StringBuffer str;
268 
269     /*
270     System.out.println("***************SubString avec 4 Arguments **************");
271     System.out.println("data : "+data);
272     System.out.println("length : "+length);
273     System.out.println("sens : "+sens);
274     System.out.println("charRemplacement : "+charRemplacement);
275     */
276     _length = Integer.parseInt(length);
277     diff = _length-data.trim().length();
278     str = new StringBuffer("");
279     for (int i=0;i<diff;i++) str.append(charRemplacement);
280     // troquer chaine si dépassement
281     if (diff<0) {
282       dataReturn = data.trim().substring(0,_length);
283       return dataReturn;
284     }
285     // compléter à gauche ou à droite la chaine vide
286     if (sens.equals("RIGHT")) dataReturn =  str.toString()+data.trim();
287     else dataReturn =  data.trim()+str.toString();
288 
289     return dataReturn;
290     }
291 
292     /***
293      * query_id returns the ID of a record based on the given input fields if it exists
294      * or an empty string if it doesn't exist.
295      * <br/>Function compliant with Saxon extension API
296      * @param context : XPathContext
297      * @param nsv: String TargetSourceName, String currentTable, String pk1Name, String[] pkeys
298      * @return String
299      * @throws XPathException
300      * @throws SQLException
301      */
302     public static String query_id( XPathContext context, SequenceIterator nsv) throws XPathException, SQLException {
303       return query_id(getNodeList(context, nsv) );
304     }
305 
306     /***
307      * query_id returns the ID of a record based on the given input fields if it exists
308      * or an empty string if it doesn't exist.
309      * The actual arguments are:
310      * String TargetSourceName, String currentTable, String pk1Name, String[] pkeys
311      * @param args : org.w3c.dom.NodeList
312      * @return String
313      * @throws SQLException
314      */
315     public static String query_id(  org.w3c.dom.NodeList args
316 				    ) throws SQLException {
317 
318       synchronized(lock) {
319 	String TargetSourceName = args.item(0).getNodeValue();
320 	String currentTable = args.item(1).getNodeValue();
321 	String pk1Name = args.item(2).getNodeValue();
322 	Connection conn = null;
323 	PreparedStatement stmnt = null;
324 	ResultSet rs = null;
325 	try {
326 	  conn = getConnection(TargetSourceName);
327 	  // Create the PreparedStatement
328 	  String statement = "SELECT " +pk1Name;
329 	  for (int i =3; i < args.getLength(); i=i+2) {
330 	    String pkName = args.item(i).getNodeValue();
331 	    if (pkName != "") statement += " , " + pkName;
332 	  }
333 	  statement += " FROM " + currentTable.toUpperCase() + " WHERE ";
334 	  for (int i =3; i < args.getLength(); i=i+2) {
335 	    String pkName = args.item(i).getNodeValue();
336 	    String pkValue = args.item(i+1).getNodeValue();
337 	    if (pkName != "") statement += buildCondition(pkName, pkValue);
338 	    if (i+2 < args.getLength()) statement += " AND ";
339 	  }
340 
341 	  stmnt = conn.prepareStatement(statement);
342 	  int index = 1;
343 	  for (int i = 3; i < args.getLength(); i=i+2) {
344 	    String pkName = args.item(i).getNodeValue();
345 	    String pkValue = args.item(i+1).getNodeValue();
346 	    if (pkName != "") stmnt.setString(index++, pkValue.trim().toUpperCase());
347 	  }
348 	  rs = stmnt.executeQuery();
349 	  String res = "";
350 
351 	  if (rs.next()) {
352 	    res = rs.getString(1);
353 	    return res;
354 	  } else {
355 	    return "";
356 	  }
357 	}
358 	finally {
359 	  if (rs != null) rs.close();
360 	  if (stmnt != null) stmnt.close();
361 	  if (conn != null) {
362 	    freeConnection(TargetSourceName, conn);
363 	  }
364 	}
365       }
366     }
367 
368     /***
369      * query_autoid
370      * <br/>Function compliant with Saxon extension API
371      * @param context
372      * @param nsv: TargetSourceName, currentTable, pk1Name, pkKeysList
373      * @return
374      * @throws XPathException
375      * @throws SQLException
376      */
377     public static String query_autoid( XPathContext context, SequenceIterator nsv) throws XPathException, SQLException {
378 
379       return query_autoid(getNodeList(context, nsv) );
380 
381     }
382 
383     public static String query_autoid(  org.w3c.dom.NodeList args
384 					) throws SQLException {
385 
386       synchronized(lock) {
387 	String TargetSourceName = args.item(0).getNodeValue();
388 	String currentTable = args.item(1).getNodeValue();
389 	String pk1Name = args.item(2).getNodeValue();
390 	Connection conn = null;
391 	PreparedStatement stmnt = null;
392 	ResultSet rs = null;
393 	try {
394 	  conn = getConnection(TargetSourceName);
395 	  String statement = "SELECT ";
396 	  // First case: if one of the field is also the id field:
397 	  // Try first to know if it exists in the database:
398 	  boolean found = false;
399 	  for (int i =3; i < args.getLength(); i=i+2) {
400 	    String pkName = args.item(i).getNodeValue();
401 	    if (pkName.equals(pk1Name)) {
402 	      statement += pkName;
403 	      statement += " FROM " + currentTable.toUpperCase() + " WHERE ";
404 	      String pkValue = args.item(i+1).getNodeValue();
405 	      statement += buildCondition(pkName, pkValue);
406 	      stmnt = conn.prepareStatement(statement);
407 	      stmnt.setString(1, pkValue.trim().toUpperCase());
408 	      found = true;
409 	    }
410 	  }
411 	  if (!found) {
412 	    // Create the PreparedStatement
413 	    statement = "SELECT " +pk1Name;
414 	    for (int i =3; i < args.getLength(); i=i+2) {
415 	      String pkName = args.item(i).getNodeValue();
416 	      if (pkName != "") statement += " , " + pkName;
417 	    }
418 	    statement += " FROM " + currentTable.toUpperCase() + " WHERE ";
419 	    for (int i =3; i < args.getLength(); i=i+2) {
420 	      String pkName = args.item(i).getNodeValue();
421 	      String pkValue = args.item(i+1).getNodeValue();
422 	      if (pkName != "" && pkValue.trim() != "") statement += buildCondition(pkName, pkValue);
423 	      if (i+2 < args.getLength()) statement += " AND ";
424 	    }
425 	    stmnt = conn.prepareStatement(statement);
426 
427 	    int index = 1;
428 	    for (int i = 3; i < args.getLength(); i=i+2) {
429 	      String pkName = args.item(i).getNodeValue();
430 	      String pkValue = args.item(i+1).getNodeValue();
431 	      if (pkName != "") stmnt.setString(index++, pkValue.trim().toUpperCase());
432 	    }
433 	  }
434 
435 	  rs = stmnt.executeQuery();
436 	  String res = "";
437 
438 	  if (rs.next()) {
439 	    res = rs.getString(1);
440 	  } else {
441 	    res = query_autoid(TargetSourceName, currentTable, conn);
442 	  }
443 	  return res;
444 	}
445 	finally {
446 	  if (rs != null) rs.close();
447 	  if (stmnt != null) stmnt.close();
448 	  if (conn != null) freeConnection(TargetSourceName, conn);
449 	}
450       }
451     }
452 
453     /***
454      * query_autoid
455      * <br/>Function compliant with Saxon extension API
456      * @param context
457      * @param nsv: TargetSourceName, currentTable, pk1Name, pkKeysList
458      * @return
459      * @throws XPathException
460      * @throws SQLException
461      */
462     public static String query_autoid_check( XPathContext context, SequenceIterator nsv) throws XPathException, SQLException {
463 
464       return query_autoid_check(getNodeList(context, nsv) );
465 
466     }
467 
468     public static String query_autoid_check(  org.w3c.dom.NodeList args
469 					) throws SQLException {
470 
471       int i=0;
472       String TargetSourceName = args.item(i++).getNodeValue();
473       String currentTable = args.item(i++).getNodeValue();
474       String pk1Name = args.item(i++).getNodeValue();
475       ArrayList targetPKs = new ArrayList();
476       if (args.item(i++).getNodeValue().equalsIgnoreCase("beginlist")) {
477 	while (!args.item(i).getNodeValue().equalsIgnoreCase("endlist")) {
478 	  targetPKs.add(args.item(i).getNodeValue());
479 	  targetPKs.add(args.item(i+1).getNodeValue());
480 	  i+=2;
481 	}
482       }
483       i++;
484       String origSourceName = args.item(i++).getNodeValue();
485       String origCurrentTable = args.item(i++).getNodeValue();
486       String origPk1Name = args.item(i++).getNodeValue();
487       String origPk1Value = args.item(i++).getNodeValue();
488       ArrayList originPKs = new ArrayList();
489       if (args.item(i++).getNodeValue().equalsIgnoreCase("beginlist")) {
490 	while (!args.item(i).getNodeValue().equalsIgnoreCase("endlist")) {
491 	  originPKs.add(args.item(i).getNodeValue());
492 	  originPKs.add(args.item(i+1).getNodeValue());
493 	  i+=2;
494 	}
495       }
496       Connection conn = null;
497       Connection origConn = null;
498       PreparedStatement stmnt = null;
499       ResultSet rs = null;
500 
501       try {
502 	conn = getConnection(TargetSourceName);
503 
504 	// Create the PreparedStatement
505 	String statement = "SELECT " +pk1Name;
506 	String pkName = null;
507 	String pkValue = null;
508 	int targetPKsSize = targetPKs.size();
509 
510 	for (i = 0; i < targetPKsSize; i+=2) {
511 	  pkName = (String)targetPKs.get(i);
512 	  if (pkName != "") statement += " , " + pkName;
513 	}
514 	statement += " FROM " + currentTable.toUpperCase() + " WHERE ";
515 
516 	for (i = 0; i < targetPKsSize; i+=2) {
517 	  pkName = (String)targetPKs.get(i);
518 	  pkValue = (String)targetPKs.get(i+1);
519 	  if (pkName != "") statement += buildCondition(pkName, pkValue);
520 	  if (i+2 < targetPKsSize) statement += " AND ";
521 	}
522 	stmnt = conn.prepareStatement(statement);
523 
524 	for ( i = 0; i < targetPKsSize; i+=2) {
525 	  pkName = (String)targetPKs.get(i);
526 	  pkValue = (String)targetPKs.get(i+1);
527 	  if (pkName != "") stmnt.setString(i+1, pkValue.trim().toUpperCase());
528 	}
529 
530 	rs = stmnt.executeQuery();
531 	String res = "";
532 
533 	if (rs.next()) {
534 	  res = rs.getString(1);
535 	  rs.close();
536 	  stmnt.close();
537 	} else {
538 	  res = query_autoid(TargetSourceName, currentTable, conn);
539 	}
540 	if (!res.equalsIgnoreCase(origPk1Value)) {
541 	    // The origin ID value is different from the target ID value
542 	    // Update the origin ID value with the target ID value:
543 	    // The database of the Network Discovery engine is in charge
544 	    // to keep the Sophie (target) ID value
545 	    origConn = getConnection(origSourceName);
546 	    // UPDATE origCurrentTable SET origPk1Name = res WHERE pkName = pkValue
547 	    statement = "UPDATE " +  origCurrentTable + " SET " + origPk1Name + " = '" + res + "' WHERE ";
548 	    int originPKsSize = originPKs.size();
549 	    for ( i = 0; i < originPKsSize; i=i+2) {
550 	      pkName = (String)originPKs.get(i);
551 	      pkValue = (String)originPKs.get(i+1);
552 	      if (pkName != "") statement += buildCondition(pkName, pkValue);
553 	      if (i+2 < originPKsSize) statement += " AND ";
554 	    }
555 	    stmnt = origConn.prepareStatement(statement);
556 	    for ( i = 0; i < originPKsSize; i=i+2) {
557 	      pkName = (String)originPKs.get(i);
558 	      pkValue = (String)originPKs.get(i+1);
559 	      if (pkName != "") stmnt.setString(i+1, pkValue.trim().toUpperCase());
560 	    }
561 
562 	    if (Utils.debug) System.out.println("Update query: " + statement);
563 
564 	    System.out.println("Update query: " + stmnt.toString());
565 
566 	    int n = stmnt.executeUpdate();
567 	    stmnt.close();
568 
569 	    if (Utils.debug) System.out.println(n + " rows were updated.");
570 	  }
571 	  return res;
572       }
573       finally {
574 	if (rs != null) rs.close();
575 	if (stmnt != null) stmnt.close();
576 	if (conn != null) {
577 	  freeConnection(TargetSourceName, conn);
578 	  conn = null;
579 	}
580 	if (origConn != null) {
581 	  freeConnection(origSourceName, origConn);
582 	  origConn = null;
583 	}
584       }
585     }
586 
587     /***
588      * query_autoid : private method to generate auto ID
589      * @param TargetSourceName
590      * @param currentTable
591      * @return
592      * @throws SQLException
593      */
594     private static String query_autoid(String TargetSourceName, String currentTable, Connection conn) throws SQLException {
595 
596       // select NUMERO from PARANUM where NOMTAB='EN_COURS'"
597       // update from PARANUM values NOMTAB='EN_COURS', NUMERO='+1'"
598       int nu_int;
599       String nu_int_result = "";
600       String nu_int_st = "";
601 
602       PreparedStatement stmnt = null;
603       ResultSet rs = null;
604       try {
605 	// Create the PreparedStatement
606 	stmnt = conn.prepareStatement("SELECT NUMERO FROM PARANUM WHERE NOMTAB='"+currentTable.toUpperCase()+"'");
607 	// Execute the query to obtain the ResultSet
608 	rs = stmnt.executeQuery();
609 	while (rs.next()) {
610 	  nu_int = rs.getInt("NUMERO");
611 	  nu_int_result = rs.getString("NUMERO");
612 	  nu_int_st = String.valueOf(nu_int + 1);
613 	  stmnt = conn.prepareStatement("UPDATE PARANUM SET NUMERO = ? WHERE NOMTAB='"+currentTable.toUpperCase()+"'");
614 	  stmnt.setString(1, nu_int_st);
615 	  stmnt.executeUpdate();
616 	}
617       } finally {
618 	if (rs != null) rs.close();
619 	if (stmnt != null) stmnt.close();
620 	if (conn != null) {
621 	  freeConnection(TargetSourceName, conn);
622 	}
623       }
624       return nu_int_st;
625     }
626 
627     /***
628      * textWithoutLineBreak.
629      * <br/>Function compliant with Saxon extension API
630      * @param context
631      * @param nsv
632      * @return
633      * @throws XPathException
634      * @throws SQLException
635      */
636     public static String textWithoutLineBreak( XPathContext context, SequenceIterator nsv) throws XPathException, SQLException {
637       return textWithoutLineBreak(getNodeList(context, nsv) );
638     }
639 
640     public static String textWithoutLineBreak(  org.w3c.dom.NodeList args
641 	) throws SQLException {
642 
643       String textString = args.item(0).getNodeValue();
644 
645       if ( textString==null || textString.trim().length()==0 ) return "";
646 
647       StringBuffer out;
648       char s1;
649       int i,j;
650 
651       // traiter &
652       j = textString.trim().length();
653       out = new StringBuffer();
654       for (i=0;i<j;i++)  {
655 	s1 = textString.charAt(i);
656 	Character s2 = new Character(s1);
657 	//System.out.println(" Char Numeric : "+s2.getNumericValue(s1)+ " de "+s1);
658 	if ( s2.isISOControl(s1) ) out.append(".");
659 	else out.append(s1);
660       }
661       //System.out.println("character Special : "+in);
662       return out.toString();
663     }
664 
665     /***
666      * getCodeString
667      * @param source
668      * @param table
669      * @param fieldIDName
670      * @param fieldName
671      * @param fieldValue
672      * @return
673      * @throws SQLException
674      */
675     public static String getCodeString(String source, String table, String fieldIDName, String fieldName, String fieldValue) throws SQLException{
676 
677       Connection conn  = null;
678       PreparedStatement stmnt = null;
679       ResultSet rs = null;
680 
681       try {
682 	conn = getConnection(source);
683 	// Create the PreparedStatement
684 	stmnt = conn.prepareStatement("SELECT "+fieldIDName+" FROM "+table+" WHERE "+fieldName+"=?");
685 	stmnt.setString(1,fieldValue.trim().toUpperCase());
686 	// Execute the query to obtain the ResultSet
687 	rs = stmnt.executeQuery();
688 
689 	String result = "";
690 	if (rs.next()) {
691 	  result = rs.getString(1);
692 	} else result = "";
693 	return result;
694       }
695       finally {
696 	if (rs != null) rs.close();
697 	if (stmnt != null) stmnt.close();
698 	if (conn != null) freeConnection(source, conn);
699       }
700     }
701 
702 
703     public static String getCodeNumeric( XPathContext context, SequenceIterator nsv) throws XPathException, SQLException {
704       return getCodeNumeric(getNodeList(context, nsv) );
705     }
706 
707     public static String getCodeNumeric(  org.w3c.dom.NodeList args
708 	) throws SQLException {
709 
710       int i = 0;
711       String source = args.item(i++).getNodeValue();
712       String table = args.item(i++).getNodeValue();
713       String fieldIDName = args.item(i++).getNodeValue();
714       String fieldName = args.item(i++).getNodeValue();
715       String fieldValue = args.item(i++).getNodeValue();
716 
717       Connection conn = null;
718       PreparedStatement stmnt = null;
719       ResultSet rs = null;
720 
721       try {
722 	conn = getConnection(source);
723 	// Create the PreparedStatement
724 	stmnt = conn.prepareStatement("SELECT "+fieldIDName+" FROM "+table+" WHERE "+fieldName+"=?");
725 	stmnt.setString(1,fieldValue.trim().toUpperCase());
726 	// Execute the query to obtain the ResultSet
727 	rs = stmnt.executeQuery();
728 
729 	String result = "";
730 	if (rs.next()) {
731 	  result = rs.getString(1);
732 	} else result = "0";
733 	return result;
734       }
735       finally {
736 	if (rs != null) rs.close();
737 	if (stmnt != null) stmnt.close();
738 	if (conn != null) freeConnection(source, conn);
739       }
740     }
741 
742 
743     public static String updateND_Link_EF_Eqt( XPathContext context, SequenceIterator nsv) throws XPathException, SQLException {
744           return updateND_Link_EF_Eqt(getNodeList(context, nsv) );
745         }
746 
747 
748    /* NetworkDiscovery methods */
749 
750    /***
751     * Update the links between PCs and their components (UC, Printers, Screens, etc).
752     * The Pytheas database defines a father link between PCs and their components.
753     * @param args
754     * @return
755     * @throws SQLException
756     */
757    public static String updateND_Link_EF_Eqt(  org.w3c.dom.NodeList args
758                                                ) throws SQLException {
759 
760       int i = 0;
761       String sourceAP = args.item(i++).getNodeValue();
762       String sourceND = args.item(i++).getNodeValue();
763       String table = "otObject";
764       String oidObject = args.item(i++).getNodeValue();
765       // EF number
766       String n_ef = args.item(i++).getNodeValue();
767 
768       Connection connND = null;
769       Connection connAP = null;
770       PreparedStatement stmnt = null;
771       ResultSet rs = null;
772       String nom_ef = null;
773       try {
774         // Get the hostname from the n_ef
775         connAP = getConnection(sourceAP);
776         // Create the PreparedStatement
777         stmnt = connAP.prepareStatement("SELECT NOM FROM B_SF1996 where N_EF=?");
778         stmnt.setString(1, n_ef.toUpperCase());
779         // Execute the query to obtain the ResultSet
780         rs = stmnt.executeQuery();
781 
782         if (rs.next()) {
783           nom_ef = rs.getString(1);
784           // Get the oidObject of the Pc in Pytheas
785           connND = getConnection(sourceND);
786           // Create the PreparedStatement
787           stmnt = connND.prepareStatement("SELECT oidObject FROM otObject where oidObjectType In (408, 422, 423, 424, 425, 426, 427, 430, 437, 438) and otModel=?");
788           stmnt.setString(1, nom_ef);
789           // Execute the query to obtain the ResultSet
790           rs = stmnt.executeQuery();
791 
792           if (rs.next()) {
793             int oidObject_PC = rs.getInt(1);
794             stmnt = connND.prepareStatement(
795                 "UPDATE otObject set oidObjectFather=? WHERE oidObject=?");
796             stmnt.setInt(1, oidObject_PC);
797             stmnt.setInt(2, Integer.parseInt(oidObject));
798             int numberofUpdates = stmnt.executeUpdate();
799           }
800         }
801         return "UPDATE_DONE";
802       }
803       catch (Exception ex) {
804           return "UPDATE_FAILED";
805       }
806       finally {
807         if (rs != null) rs.close();
808         if (stmnt != null) stmnt.close();
809         if (connND != null) freeConnection(sourceND, connND);
810         if (connAP != null) freeConnection(sourceAP, connAP);
811       }
812     }
813 
814 
815     public static String updateND_Link_EFandEqt_User( XPathContext context, SequenceIterator nsv) throws XPathException, SQLException {
816       return updateND_Link_EFandEqt_User(getNodeList(context, nsv) );
817     }
818 
819     /***
820      * Update the links between PCs and users.
821      * The Pytheas database defines a relation between PCs and users.
822      * @param args
823      * @return
824      * @throws SQLException
825      */
826     public static String updateND_Link_EFandEqt_User (  org.w3c.dom.NodeList args) throws SQLException {
827 
828       org.opensync.engine.server.OpenSync.getInstance().getLog().debug(
829         org.opensync.engine.server.Log.ROOT,
830         "xslt extension function called: updateND_Link_EFandEqt_User");
831 
832       int i = 0;
833       String sourceAP = args.item(i++).getNodeValue();
834       String sourceND = args.item(i++).getNodeValue();
835       String table = "otObject";
836       String oidObject = args.item(i++).getNodeValue();
837       // EF number
838       String user_login_name = args.item(i++).getNodeValue();
839       int id_it_user = -1;
840       String n_imma = args.item(i++).getNodeValue();
841       String n_ef = args.item(i++).getNodeValue();
842 
843       Connection connND = null;
844       Connection connAP = null;
845       PreparedStatement stmnt = null;
846       ResultSet rs = null;
847       int oidObject_User = 0;
848       int numberofUpdates = 0;
849       try {
850         connAP = getConnection(sourceAP);
851         // Get the ID_IT_USER
852         stmnt = connAP.prepareStatement("select ID_IT_USER from IT_USERS where LOGIN_NAME=?");
853         stmnt.setString(1, user_login_name);
854         rs = stmnt.executeQuery();
855         if (rs.next()) {
856           id_it_user = rs.getInt(1);
857 
858           // Check if all the equipments belonging to the same FunctionGroup (EF)
859           // are linked to the same user since we can't change the user of one
860           // equipement in the Pytheas database.
861 
862           // Create the PreparedStatement
863           stmnt = connAP.prepareStatement("select N_IMMA from B_EQ1996 where V_FONC='IT' and N_EF=? and ID_IT_USER != ?");
864           stmnt.setString(1, n_ef);
865           stmnt.setInt(2, id_it_user);
866           rs = stmnt.executeQuery();
867 
868           if (!rs.next()) {
869             // All the equipments belong to the same user !
870             // Now get the father of the current equipment to update
871             // Get the oidObject from the user_login_name
872             connND = getConnection(sourceND);
873             // Create the PreparedStatement
874             stmnt = connND.prepareStatement(
875               "SELECT oidObject FROM otUser where otLoginName=?");
876             stmnt.setString(1, user_login_name);
877             // Execute the query to obtain the ResultSet
878             rs = stmnt.executeQuery();
879 
880             if (rs.next()) {
881               oidObject_User = rs.getInt(1);
882               stmnt = connND.prepareStatement(
883                 "UPDATE otObjectRelation set oidObjectRelation=?  where ( oidObject=? or  (oidObject in (select oidObjectFather from otObject where oidObject=?))) and oidObjectRelationType=304");
884               stmnt.setInt(1, oidObject_User);
885               stmnt.setInt(2, Integer.parseInt(oidObject));
886               stmnt.setInt(3, Integer.parseInt(oidObject));
887               numberofUpdates = stmnt.executeUpdate();
888             }
889           }
890         }
891         return "UPDATE_DONE";
892       }
893       catch (Exception ex) {
894         return "UPDATE_FAILED";
895       }
896       finally {
897         if (rs != null)
898           rs.close();
899         if (stmnt != null)
900           stmnt.close();
901         if (connND != null)
902           freeConnection(sourceND, connND);
903         if (connAP != null)
904           freeConnection(sourceAP, connAP);
905       }
906     }
907 
908 
909     public static String updateND_Link_EFandEqt_Dept( XPathContext context, SequenceIterator nsv) throws XPathException, SQLException {
910       return updateND_Link_EFandEqt_Dept(getNodeList(context, nsv) );
911     }
912 
913     /***
914      * Update the links between PCs and users.
915      * The Pytheas database defines a relation between PCs and users.
916      * @param args
917      * @return
918      * @throws SQLException
919      */
920     public static String updateND_Link_EFandEqt_Dept (  org.w3c.dom.NodeList args) throws SQLException {
921 
922       org.opensync.engine.server.OpenSync.getInstance().getLog().debug(
923           org.opensync.engine.server.Log.ROOT,
924           "xslt extension function called: updateND_Link_EFandEqt_Dept");
925 
926       int i = 0;
927       String sourceAP = args.item(i++).getNodeValue();
928       String sourceND = args.item(i++).getNodeValue();
929       String table = "otObject";
930       String oidObject = args.item(i++).getNodeValue();
931       // EF number
932       String user_login_name = args.item(i++).getNodeValue();
933       int id_it_user = -1;
934       String n_imma = args.item(i++).getNodeValue();
935       String n_ef = args.item(i++).getNodeValue();
936       String n_uf = args.item(i++).getNodeValue();
937 
938       Connection connND = null;
939       Connection connAP = null;
940       PreparedStatement stmnt = null;
941       ResultSet rs = null;
942       String nom_department = "";
943       int oidObject_Dept = 0;
944       int oidObject_User = 0;
945       int numberofUpdates = 0;
946       try {
947         connAP = getConnection(sourceAP);
948         // Check if all the equipments belonging to the same FunctionnalGroup (EnsFonctionnel)
949 
950          stmnt = connAP.prepareStatement(
951             "select N_IMMA from B_EQ1996 where N_EF= ? and N_UF!=?");
952         stmnt.setString(1, n_ef);
953         stmnt.setString(2, n_uf);
954         rs = stmnt.executeQuery();
955 
956         // To continue: the query must not return results !
957         if (!rs.next()) {
958         //if (true) {
959           // Get the name of the department
960           stmnt = connAP.prepareStatement("select N_SERVI from UNITES where N_UF=?");
961           stmnt.setString(1, n_uf);
962           rs = stmnt.executeQuery();
963           if (rs.next()) {
964             nom_department = rs.getString(1);
965 
966             // Get the oidObject from the user_login_name
967             connND = getConnection(sourceND);
968             // Create the PreparedStatement
969             stmnt = connND.prepareStatement(
970                 "SELECT oidObject FROM otUser where otLoginName=?");
971             stmnt.setString(1, user_login_name);
972             // Execute the query to obtain the ResultSet
973             rs = stmnt.executeQuery();
974 
975             if (rs.next()) {
976               oidObject_User = rs.getInt(1);
977 
978               // Get the oidObject from the department/service
979               // Create the PreparedStatement
980               stmnt = connND.prepareStatement(
981                   "SELECT oidObject FROM otObject where otModel=?");
982               stmnt.setString(1, nom_department);
983               // Execute the query to obtain the ResultSet
984               rs = stmnt.executeQuery();
985 
986               if (rs.next()) {
987                 oidObject_Dept = rs.getInt(1);
988                 stmnt = connND.prepareStatement(
989                     "UPDATE otObject set oidObjectFather=?  where oidObject=?");
990                 stmnt.setInt(1, oidObject_Dept);
991                 stmnt.setInt(2, oidObject_User);
992                 numberofUpdates = stmnt.executeUpdate();
993               }
994             }
995           }
996         }
997         return "UPDATE_DONE";
998       }
999       catch (Exception ex) {
1000         return "UPDATE_FAILED";
1001       }
1002       finally {
1003         if (rs != null)
1004           rs.close();
1005         if (stmnt != null)
1006           stmnt.close();
1007         if (connND != null)
1008           freeConnection(sourceND, connND);
1009         if (connAP != null)
1010           freeConnection(sourceAP, connAP);
1011       }
1012     }
1013 
1014     public static String updateNetworkDiscovery( XPathContext context, SequenceIterator nsv) throws XPathException, SQLException {
1015       return updateNetworkDiscovery(getNodeList(context, nsv) );
1016     }
1017 
1018     public static String updateNetworkDiscovery(  org.w3c.dom.NodeList args
1019         ) throws SQLException {
1020 
1021       int i = 0;
1022       String source = args.item(i++).getNodeValue();
1023       String table = args.item(i++).getNodeValue();
1024       String fieldIDName = args.item(i++).getNodeValue();
1025       String fieldIDValue = args.item(i++).getNodeValue();
1026       Connection conn = null;
1027       PreparedStatement stmnt = null;
1028       ResultSet rs = null;
1029       try {
1030         if (fieldIDValue != null && !fieldIDValue.trim().equals("")) {
1031           String fieldName1 = args.item(i++).getNodeValue();
1032           String fieldValue1 = args.item(i++).getNodeValue();
1033 
1034           conn = getConnection(source);
1035           // Create the PreparedStatement
1036           stmnt = conn.prepareStatement(
1037               "UPDATE " + table + " set " + fieldName1 + "=? " + " WHERE " + fieldIDName + "=?");
1038           stmnt.setInt(1, Integer.parseInt(fieldValue1.trim()));
1039           stmnt.setInt(2, Integer.parseInt(fieldIDValue.trim()));
1040           // Execute the query to obtain the ResultSet
1041           int numberOfRowsUpdated = stmnt.executeUpdate();
1042           return "UPDATE_DONE";
1043         } else
1044           return "NOTHING_DONE";
1045       }
1046       catch (Exception ex) {
1047           return "UPDATE_FAILED";
1048       }
1049       finally {
1050         if (rs != null) rs.close();
1051         if (stmnt != null) stmnt.close();
1052         if (conn != null) freeConnection(source, conn);
1053       }
1054     }
1055 
1056 
1057     public static String getDeviceID( XPathContext context, SequenceIterator nsv) throws XPathException, SQLException {
1058       return getDeviceID(getNodeList(context, nsv) );
1059     }
1060 
1061     public static String getDeviceID(  org.w3c.dom.NodeList args
1062 	) throws SQLException {
1063 
1064       int i = 0;
1065       String typ_model = args.item(i++).getNodeValue();
1066 
1067       Hashtable codes = new Hashtable();
1068 
1069       //codes.put("BIOMED",1);
1070       codes.put("CARTE","2");
1071       codes.put("CD-ROM","3");
1072       codes.put("COMPOSANT","4");
1073       codes.put("LECTEUR DE DISQUETTE","5");
1074       codes.put("DISQUE DUR","6");
1075       codes.put("PROCESSEUR","7");
1076       codes.put("LOGICIEL","8");
1077 
1078       String id = (String)codes.get(typ_model.trim().toUpperCase());
1079 
1080       if (id == null) return "1";
1081       return id;
1082     }
1083     /***
1084      * buildCondition : helper function.
1085      * @param field
1086      * @param fieldValue
1087      * @return
1088      */
1089     static private String buildCondition(String field, String fieldValue) {
1090 
1091       if (fieldValue == null || fieldValue.trim().length()==0)
1092 	return "( ''=? OR "+field+" is NULL OR "+field+"='')";
1093       else
1094 	return "("+field+"=?)";
1095     }
1096 
1097     public static String getETAT_LIG( XPathContext context, SequenceIterator nsv) throws XPathException, SQLException {
1098       return getETAT_LIG(getNodeList(context, nsv) );
1099     }
1100 
1101     public static String getETAT_LIG(  org.w3c.dom.NodeList args
1102 	) throws SQLException {
1103 
1104       int i = 0;
1105       String source = args.item(i++).getNodeValue();
1106       String table = args.item(i++).getNodeValue();
1107       String fieldName = args.item(i++).getNodeValue();
1108       String fieldValue = args.item(i++).getNodeValue();
1109 
1110       Connection conn = null;
1111       PreparedStatement stmnt = null;
1112       ResultSet rs = null;
1113 
1114       try {
1115 	conn = getConnection(source);
1116 	// Create the PreparedStatement
1117 	stmnt = conn.prepareStatement("SELECT "+fieldName+" FROM "+table+" WHERE "+fieldName+"=?");
1118 	stmnt.setString(1,fieldValue.trim().toUpperCase());
1119 	// Execute the query to obtain the ResultSet
1120 	rs = stmnt.executeQuery();
1121 
1122 	String result = "";
1123 	if (rs.next()) {
1124 	  result = "N";
1125 	} else result = "O";
1126 	return result;
1127       }
1128       finally {
1129 	if (rs != null) rs.close();
1130 	if (stmnt != null) stmnt.close();
1131 	if (conn != null) freeConnection(source, conn);
1132 	conn = null;
1133       }
1134     }
1135 
1136     public static String getNUMCOMM( XPathContext context, SequenceIterator nsv) throws XPathException, SQLException {
1137       return getNUMCOMM(getNodeList(context, nsv) );
1138     }
1139 
1140     public static String getNUMCOMM(  org.w3c.dom.NodeList args
1141 	) throws SQLException {
1142 
1143       int i = 0;
1144       String source = args.item(i++).getNodeValue();
1145       String table = args.item(i++).getNodeValue();
1146       String fieldName = args.item(i++).getNodeValue();
1147       String fieldValue = args.item(i++).getNodeValue();
1148       String numComm = args.item(i++).getNodeValue();
1149 
1150       Connection conn = null;
1151       PreparedStatement stmnt = null;
1152       ResultSet rs = null;
1153 
1154       try {
1155 	conn = getConnection(source);
1156 	// Create the PreparedStatement
1157 	stmnt = conn.prepareStatement("SELECT "+fieldName+" FROM "+table+" WHERE "+fieldName+"=?");
1158 	stmnt.setString(1,fieldValue.trim().toUpperCase());
1159 	// Execute the query to obtain the ResultSet
1160 	rs = stmnt.executeQuery();
1161 
1162 	String result = "";
1163 	if (rs.next()) {
1164 	  result = numComm;
1165 	} else result = "";
1166 	return result;
1167       }
1168       finally {
1169 	if (rs != null) rs.close();
1170 	if (stmnt != null) stmnt.close();
1171 	if (conn != null) freeConnection(source, conn);
1172       }
1173     }
1174 
1175 
1176 }