001 /** =====================================================================
002 *
003 * File Name : $Id: FaultServiceImpl.java,v 1.47 2008/01/15 11:08:14 cb Exp $
004 *
005 * Description
006 * -----------
007 *
008 * See javadoc comment
009 *
010 * =====================================================================
011 *
012 * @Author : Craige Bevil
013 * Control Software Group
014 * Isaac Newton Group of Telescopes
015 *
016 * =====================================================================
017 *
018 * Modification Log
019 *
020 * Vers Date Author Reason
021 * ---- ---- ------ ------
022 * 1 9 May 2007 C.Bevil First Release
023 *
024 * Commissioning Notes
025 * -------------------
026 *
027 * None
028 *
029 * =====================================================================
030 *
031 * @Version : $Id: FaultServiceImpl.java,v 1.47 2008/01/15 11:08:14 cb Exp $
032 *
033 * @Author : $Author: cb $
034 *
035 * Header : $Header: /opt/INGsrc/src/CVS/softproj/FaultDatabase/src/FaultDatabase/FaultDatabase/src/GWTApplication/server/FaultServiceImpl.java,v 1.47 2008/01/15 11:08:14 cb Exp $
036 *
037 * Log : $Log: FaultServiceImpl.java,v $
038 * Log : Revision 1.47 2008/01/15 11:08:14 cb
039 * Log : Ran through PMD and sorted out the javadoc so that we could export the
040 * Log : javadoc to the javadoc repository.
041 * Log :
042 * Log : Revision 1.46 2008/01/14 17:18:15 cb
043 * Log : Updated to include a mailshot which will mail the details of the
044 * Log : faults which are in the pending state to opshead and the person which
045 * Log : is assigned the fault.
046 * Log :
047 * Log : Revision 1.45 2007/12/17 12:30:47 cb
048 * Log : Typo fixed
049 * Log :
050 * Log : Revision 1.44 2007/12/17 11:30:45 cb
051 * Log : Revised the list of fault listeners which are returned to the client
052 * Log : so only those faults which are not closed are returned just to keep
053 * Log : the list down.
054 * Log :
055 * Log : Modified the formatting of the HTML when preparing it to be printed
056 * Log : into the PDF file to ensure that the <br .....> tag is maintained
057 * Log : correctly.
058 * Log :
059 * Log : Revision 1.43 2007/12/17 10:36:58 cb
060 * Log : Corrected a problem with printing out the faults from the previous 24
061 * Log : hours (see DR 17947)
062 * Log :
063 * Log : Revision 1.42 2007/12/14 12:33:55 cb
064 * Log : Removed dead code
065 * Log :
066 * Log : Revision 1.41 2007/12/14 07:52:27 cb
067 * Log : Update the task which sends out mailshots so that it sends out the
068 * Log : details of the faults which are in the new state for more than 48
069 * Log : hours.
070 * Log :
071 * Log : Revision 1.40 2007/12/12 15:29:21 cb
072 * Log : Included the code which will print out the faults on the ops
073 * Log : printer. Corrected some SQL and added some debug
074 * Log :
075 * Log : Revision 1.39 2007/12/12 12:03:14 cb
076 * Log : Fixed a bug in the reporting of new faults to teh user
077 * Log :
078 * Log : Revision 1.38 2007/10/18 13:18:34 cb
079 * Log : Updated the name of the user associated with the emails which are sent
080 * Log : by the system.
081 * Log :
082 * Log : Revision 1.37 2007/10/18 08:43:49 cb
083 * Log : Support for decommissioned instruments has been added to the system
084 * Log :
085 * Log : Revision 1.36 2007/10/18 07:58:02 cb
086 * Log : Changed the person from whom the emails are sent from to
087 * Log : faultdbadmin.
088 * Log :
089 * Log : Revision 1.35 2007/10/08 13:08:29 cb
090 * Log : Modified the packFault method so that it can pack fault data from
091 * Log : either a fault view or a fault table.
092 * Log :
093 * Log : Revision 1.34 2007/10/03 00:00:34 cb
094 * Log : Modified the margin at the top of the PDF documents. Now prints off the
095 * Log : documents at the ops_laser.
096 * Log :
097 * Log : Revision 1.33 2007/10/02 13:08:09 cb
098 * Log : Send the mail shot at 7 AM only
099 * Log :
100 * Log : Revision 1.32 2007/09/26 15:20:14 cb
101 * Log : Corrected some spellings of the word occurred
102 * Log :
103 * Log : Revision 1.31 2007/09/26 09:42:52 cb
104 * Log : Added a new timer task which will inform management when a fault has
105 * Log : been in the NEW state for more than 48 hours.
106 * Log :
107 * Log : Revision 1.30 2007/09/26 08:14:55 cb
108 * Log : New faults are printed to wht_laser at 0830 every morning
109 * Log :
110 * Log : Revision 1.29 2007/09/25 11:05:13 cb
111 * Log : Corrected a problem when printing as we were printing on the default
112 * Log : printer only and essentially ignoring the printer which was specified
113 * Log : by the user.
114 * Log :
115 * Log : Revision 1.28 2007/09/06 14:07:02 cb
116 * Log : Reduced the number of items returned in a search to 250
117 * Log :
118 * Log : Revision 1.27 2007/09/06 12:57:32 cb
119 * Log : Updated a comment
120 * Log :
121 * Log : Revision 1.26 2007/09/06 12:57:07 cb
122 * Log : Added some extra debug
123 * Log :
124 * Log : Revision 1.25 2007/09/06 12:54:51 cb
125 * Log : Reverted the method of checking for fault updates.
126 * Log :
127 * Log : Use log4j for logging
128 * Log :
129 * Log : Revision 1.23 2007/09/03 13:21:17 cb
130 * Log : Modified so that if the start and the end dates which have been
131 * Log : specified in the advanced and the basic search are entered with the
132 * Log : start date greater than the end date, the software swaps them around.
133 * Log :
134 * Log : Revision 1.22 2007/09/03 11:21:43 cb
135 * Log : Sorted out the problem of the fault not being printed correctly on an
136 * Log : A4 piece of paper
137 * Log :
138 * Log : Revision 1.21 2007/08/22 11:15:41 cb
139 * Log : Update the agorithm which strips HTML from text before inserting it
140 * Log : into PDF files.
141 * Log :
142 * Log : Revision 1.20 2007/08/21 13:39:02 cb
143 * Log : Rewrote the advanced search algorithm taking into account the
144 * Log : conjugated text field which was introduced.
145 * Log :
146 * Log : Revision 1.19 2007/08/21 09:21:01 cb
147 * Log : Modified so that in the advanced search we can specifiy either of the
148 * Log : start or end date
149 * Log :
150 * Log : Revision 1.18 2007/08/20 14:03:49 cb
151 * Log : Update the assigneddate if the fault has been assigned to somebody.
152 * Log :
153 * Log : Revision 1.17 2007/08/20 11:02:11 cb
154 * Log : Added an option which allow the usr to specify an boolean OR on the
155 * Log : search terms in the keywords
156 * Log :
157 * Log : Revision 1.16 2007/08/17 14:28:17 cb
158 * Log : Do not return the state RELEASED as part of the state list. Fixed a
159 * Log : bug when updating the workarounds. Revised the manner in which the
160 * Log : application informs listeners.
161 * Log :
162 * Log : Revision 1.15 2007/08/16 14:55:06 cb
163 * Log : Added a link to the fault details which are mailed to the users.
164 * Log :
165 * Log : Revision 1.14 2007/08/16 14:35:15 cb
166 * Log : Modified to use the new timelost_interval field in teh fault view in
167 * Log : order to perform searches on the time lost attribute.
168 * Log :
169 * Log : Revision 1.13 2007/08/16 13:31:44 cb
170 * Log : Updated the search of the simple search to allow for the terms
171 * Log : which are added to the description and the title fields.
172 * Log :
173 * Log : Updated the advanced search to allow for textual entry of the priority
174 * Log : field so that the user can specify <>
175 * Log :
176 * Log : Revision 1.12 2007/08/15 11:52:55 cb
177 * Log : Fixed up the advanced search
178 * Log :
179 * Log : Revision 1.11 2007/08/15 09:51:52 cb
180 * Log : Fixed a serious bug in inserting the solution fields.
181 * Log :
182 * Log : Revision 1.10 2007/08/14 09:54:15 cb
183 * Log : Before modifying the method through which the faults are updated
184 * Log :
185 * Log : Revision 1.9 2007/08/13 13:57:45 cb
186 * Log : Modified the SQL which looks up fault links to optimize it was too
187 * Log : slow.
188 * Log :
189 * Log : Revision 1.8 2007/08/02 12:22:54 cb
190 * Log : Modified to support the extra tabs in the quick view tab panel to
191 * Log : include the different groups of outstanding faults.
192 * Log :
193 * Log : Revision 1.7 2007/08/01 13:00:02 cb
194 * Log : First prototype after import
195 * Log :
196 * Log : Revision 1.6 2007/07/26 13:35:24 cb
197 * Log : Updated the HTML report generation
198 * Log :
199 * Log : Revision 1.5 2007/07/19 11:37:07 cb
200 * Log : Added the lastmodified field to the fault table and views which is
201 * Log : updated when anything is changed in the fault.
202 * Log :
203 * Log : Added a new method which can be used to close down the fault.
204 * Log :
205 * Log : Revision 1.4 2007/07/13 10:54:00 cb
206 * Log : Complete interface prototype
207 * Log :
208 * Log : Revision 1.3 2007/07/03 10:05:46 cb
209 * Log : The email feature has now been completed.
210 * Log :
211 * Log : Revision 1.2 2007/07/02 16:21:15 cb
212 * Log : Before changing the email to send HTML messages
213 * Log :
214 * Log : Revision 1.1.1.1 2007/06/01 08:33:25 cb
215 * Log : Imported using TkCVS
216 * Log :
217 *
218 * =====================================================================*/
219
220 package GWTApplication.server;
221
222 import GWTApplication.client.*;
223 import com.google.gwt.user.server.rpc.*;
224 import java.util.*;
225 import java.text.*;
226
227 import javax.sql.DataSource;
228 import java.sql.*;
229 import java.io.*;
230
231 // SNMP packages
232
233 import org.snmp4j.*;
234 import org.snmp4j.security.*;
235 import org.snmp4j.transport.*;
236 import org.snmp4j.mp.*;
237 import org.snmp4j.smi.*;
238 import org.snmp4j.event.*;
239
240 // Used for emailing clients about updates to faults.
241
242 import javax.mail.*;
243 import javax.mail.internet.*;
244
245 // Used for printing
246
247 import javax.print.*;
248 import javax.print.attribute.*;
249 import javax.print.attribute.standard.*;
250 import javax.print.event.*;
251
252 // Used for creating PDF documents (this stuff is so so cool)
253
254 import com.lowagie.text.*;
255 import com.lowagie.text.pdf.*;
256 import com.lowagie.text.html.*;
257
258 // log4j
259
260 import org.apache.log4j.Logger;
261 import org.apache.log4j.Level;
262 import org.apache.log4j.PropertyConfigurator;
263
264 import java.util.Date;
265
266 /**
267 * This class implments all of the services which will be used by the
268 * fault database application to support the client AJAX/GWT
269 * componentry which runs on the client browser. This class is
270 * responsible for performing the business logic associated with the fault
271 * management system.
272 * <P>
273 * This class makes use of the SNMP4J classes which are use to allow
274 * the class to send SNMP TRAPS to the SNMP manager to inform it that
275 * new faults have been raised.
276 * <P>
277 * The class uses a database pool which is set up by the tomcat
278 * container which will permit the class rapid access to the
279 * database. The configuration of the database pool is part of the
280 * tomcat configuration and <b>not</b> the configuraiton of the fault
281 * management system.
282 * <P>
283 * Aside from SNMP4J API JavaDoc, one should look at the source code
284 * for the class SnmpRequest which is a console based class which can
285 * be used to create a SNMP manager/agent and is the best place to
286 * look if you want to change the SNMP code in this class as there is
287 * precious little documentation elsewhere.
288 *<P>
289 * @author Craige Bevil
290 * @version $Id: FaultServiceImpl.java,v 1.47 2008/01/15 11:08:14 cb Exp $
291 */
292
293 public class FaultServiceImpl extends RemoteServiceServlet implements FaultService {
294
295 enum AdvancedDataType {STRING,NONSTRING};
296
297 /**
298 * This is a timer task which will be used to print the new faults
299 * everyday in the ops room in the morning
300 */
301
302 private final PrintNewFaultsInLastTwentyFourHours printNewFaultsInLastTwentyFourHrsTimerTask = new PrintNewFaultsInLastTwentyFourHours();
303
304 /**
305 * A timer task which is used to inform the relevant people when a
306 * fault has been left in the new state for more than 48 hours
307 */
308
309 private final MailManagementUnattendedFaults MailManagementUntendedFaultsTimerTask = new MailManagementUnattendedFaults();
310
311 /**
312 * This is a timer task which will report faults which have been
313 * state pending for more than seven days.
314 */
315
316 private final NotifyPendingFaults NotifyPendingFaultsTimerTask = new NotifyPendingFaults();
317
318 /**
319 * This is a timer object which will be used for scheduling the
320 * printing of the new faults to the ops room printer
321 */
322
323 private final Timer PrintNewFaultsTimer = new Timer ();
324
325 /**
326 * This is a timer object which will be used for scheduling the
327 * report of unattended faults in the system to management.
328 */
329
330 private final Timer ProduceUnattendedFaultsMailShotTimer = new Timer ();
331
332 /**
333 * This is a timer object which will be used for scheduling the
334 * report of pending faults in the system to the assigned.
335 */
336
337 private final Timer ProduceOutstandingPendingFaultsMailShotTimer = new Timer ();
338
339 /**
340 * Maximum number of items returned by database in one search
341 */
342
343 private final static int MAXNUMBEROFRESULTS = 250;
344
345 /**
346 * Used for logging
347 */
348
349 private Logger logger;
350
351 /**
352 * An enumeration of the list of fields in the fault table which
353 * contain text which is suitable to be searched as such. DO NOT
354 * CHANGE the order of these enums, especially the last
355 * two. The TITLE and the DESCRIPTION enums should <b> always</b>
356 * remain at the end.
357 */
358
359 enum TextFieldsToSearch {
360 OBSERVER,
361 TO_NAME,
362 TO_SURNAME,
363 TO_EMAIL,
364 SA_NAME,
365 SA_SURNAME,
366 SA_EMAIL,
367 FAULTTYPE,
368 ASSIGNEDTO_NAME,
369 ASSIGNEDTO_SURNAME,
370 ASSIGNEDTO_EMAIL,
371 ENTEREDBY_NAME,
372 ENTEREDBY_SURNAME,
373 ENTEREDBY_EMAIL,
374 STATE,
375 SYSTEM,
376 SITE,
377 SEVERITY,
378 INSTRUMENT,
379 DUTYTECHNICIAN_NAME,
380 DUTYTECHNICIAN_SURNAME,
381 DUTYTECHNICIAN_EMAIL,
382 CONJUGATEDTEXTFIELD
383 };
384
385 /**
386 * This is the host which the SMNP TRAP fault alerts will be sent
387 * to
388 */
389
390 org.snmp4j.smi.Address targetAddress;
391
392 /**
393 * This is the transport protocol which we will use which in our
394 * case will be UDP.
395 */
396
397 TransportMapping transport;
398
399 /**
400 * This is the SNMP class which will be used to communicate with
401 * the SNMP manager
402 */
403
404 Snmp snmp;
405
406 /**
407 * The user based security model which will be used to when
408 * sending messages to the SNMP server
409 */
410
411 USM usm;
412
413 /**
414 * Used to modify the global properties of the application
415 */
416
417 private Properties props;
418
419 /**
420 * This is a data resource from the servlet container which
421 * contains a postgres data source
422 */
423
424 private DataSource dataSource;
425
426 private static final long sealVersionUID= -1502084255979334403L;
427
428 /**
429 * Used to set the data source for this module
430 * @param dataSource The new data source
431 */
432
433 public void set_dataSource (DataSource dataSource) {
434 this.dataSource = dataSource;
435 }
436
437 /**
438 * This is a timer task which will mail the person assigned a
439 * fault if the fault is in a <b>pending</b> state for more than 7
440 * days. This task is run every day at 0700.
441 */
442
443 class NotifyPendingFaults extends TimerTask {
444
445 public void run () {
446
447 Connection connection = null;
448 Statement stmt = null;
449 ResultSet rs = null;
450
451 final ArrayList<Fault> pendingFaults = new ArrayList<Fault>();
452
453 logger.info("Checking to see if there are pending faults over one week old in the database to be reported.");
454
455 if (dataSource == null) {
456 dataSource = (DataSource) getServletContext().getAttribute("DBCPool");
457 }
458
459 try {
460
461 connection = dataSource.getConnection();
462 stmt = connection.createStatement();
463
464 // Find all pending faults which are greater than 7
465 // days old. Nice use of intervals here I say,
466 // databases are lovely.
467
468 rs = stmt.executeQuery("select * from faultview_en where timeentered <= cast(cast('now' as date) - cast ('7 day' as interval) as date) and state = 'Pending' order by id desc");
469
470 while (rs.next()) {
471 Fault faultData = packFault(rs,true);
472 pendingFaults.add(faultData);
473 }
474
475 // Ensure that we clean up the database connection
476 // correctly so that the connection is returned to the
477 // database connection pool
478
479 rs.close();
480 connection.close();
481 stmt.close();
482
483 rs = null;
484 connection = null;
485 stmt = null;
486
487 } catch (SQLException e) {
488 e.printStackTrace();
489 } finally {
490
491 // Always make sure result sets and statements are closed,
492 // and the connection is returned to the pool
493
494 closeDatabaseConnections(connection,stmt,rs);
495 }
496
497 // Now we need to go through all of the new faults which
498 // have been found and then create an HTML report which we
499 // will send to the list of recipients
500
501 StringBuffer pendingFaultReport;
502
503 for (Fault currentFault : pendingFaults) {
504
505 pendingFaultReport = new StringBuffer();
506
507 try {
508
509 pendingFaultReport.append(fetchHTMLFaultReport(currentFault.id,true));
510 pendingFaultReport.append("<P>");
511
512 } catch (UnknownFaultSpecifiedException e) {
513 logger.error("Unable to find fault " + currentFault.id + " " + e.getMessage());
514 return;
515 }
516
517 // Now for each of the managers and the person who is
518 // assigned the fault, they should be sent an email
519 // which notifies them that this fault has been in a
520 // pending state for more than 7 days and that they
521 // should do something about it.
522
523 try {
524
525 final javax.mail.Session session = javax.mail.Session.getInstance(props, null);
526
527 final Message msg = new MimeMessage(session);
528
529 msg.setFrom(new InternetAddress("faultdbadmin@ing.iac.es","Fault Database Admin"));
530
531 msg.setRecipients(Message.RecipientType.TO, InternetAddress.parse("opshead@ing.iac.es", false));
532
533 // If there is a person which is assigned the
534 // fault then he should be sent the email as well.
535
536 if (!currentFault.assignedToEmail.equals("UNKNOWN")) {
537 msg.addRecipients(Message.RecipientType.TO, InternetAddress.parse(currentFault.assignedToEmail, false));
538 } else {
539 logger.error("There is no person which has been assigned fault " + currentFault.id + " to inform ");
540 }
541
542 // Now set the content of the email to show the
543 // details of the fault
544
545 msg.setSubject("Pending faults in fault database / Defectos pendientes en el base de datos de defectos");
546
547 msg.setContent("You have been sent this email by the <b>Fault Management System</B> to inform you that the following fault has been in the state <b>pending</b> for more than seven days and it's status should now be updated.<P>" +
548 "<P> El sistema de defectos te ha mandando este correo para informarte que el defecto siguiente ha sido en el estado pendiente durante mas de siete dias. DeberÃa actualizar el estado de este defecto. <P><P> " +
549 pendingFaultReport.toString(), "text/html");
550
551 msg.setHeader("X-Mailer", "Fault Management System");
552
553 msg.setSentDate(new java.util.Date());
554
555 logger.info("Sending email for fault " + currentFault.id + " as it has been in state pending for more than 7 days");
556
557 // Now send the message to the email to the
558 // current recipient
559
560 Transport.send(msg);
561
562 } catch (Exception e) {
563 logger.error("Unable to send pending faults report " + e.getMessage());
564 }
565 }
566
567 logger.info("Pending faults mailshot has been sent");
568 }
569 }
570
571 /**
572 * This is a timer task which will be used to mail a list of
573 * recipients the details of all faults which have remained in the
574 * <b>new</b> state for more than 48 hours.
575 */
576
577 class MailManagementUnattendedFaults extends TimerTask {
578
579 /**
580 * Called when the timertask is scheduled to run. It finds all
581 * of the faults which have been in the new state for more
582 * than 48 hours and then mails the details of them to
583 * management. This thread is run twice a day at 0700 and
584 * 1900.
585 * <P>
586 * Currently the people who are informed are Juerg and Michiel.
587 */
588
589 public void run () {
590
591 // A list of people who are to be informed of unattended
592 // faults.
593
594 final String[] MANAGEMENTMAILRECIPIENTS = {
595 "opshead@ing.iac.es",
596 "cb@ing.iac.es"
597 };
598
599 Connection connection = null;
600 Statement stmt = null;
601 ResultSet rs = null;
602
603 final ArrayList<Fault> newFaults = new ArrayList<Fault>();
604
605 logger.info("Checking to see if there are unattended faults to be reported");
606
607 if (dataSource == null) {
608 dataSource = (DataSource) getServletContext().getAttribute("DBCPool");
609 }
610
611 try {
612
613 connection = dataSource.getConnection();
614 stmt = connection.createStatement();
615
616 // Find all faults which have been entered in the past
617 // 24 hours, nice use of intervals here I say.
618
619 rs = stmt.executeQuery("select * from faultview_en where timeentered <= cast(cast('now' as date) - cast ('2 day' as interval) as date) and state = 'New' order by id desc");
620
621 while (rs.next()) {
622 Fault faultData = packFault(rs,true);
623 newFaults.add(faultData);
624 }
625
626 // Ensure that we clean up the database connection
627 // correctly so that the connection is returned to the
628 // database connection pool
629
630 rs.close();
631 connection.close();
632 stmt.close();
633
634 rs = null;
635 connection = null;
636 stmt = null;
637
638 } catch (SQLException e) {
639 e.printStackTrace();
640 } finally {
641
642 // Always make sure result sets and statements are closed,
643 // and the connection is returned to the pool
644
645 closeDatabaseConnections(connection,stmt,rs);
646 }
647
648 // Now we need to go through all of the new faults which
649 // have been found and then create an HTML report which we
650 // will send to the list of recipients
651
652 StringBuffer newFaultsUnattended = new StringBuffer();
653
654 boolean faultsFound = false;
655
656 for (Fault currentFault : newFaults) {
657
658 faultsFound = true;
659
660 try {
661
662 newFaultsUnattended.append(fetchHTMLFaultReport(currentFault.id,true));
663 newFaultsUnattended.append("<P>");
664
665 } catch (UnknownFaultSpecifiedException e) {
666 logger.error("Unable to find fault " + currentFault.id + " " + e.getMessage());
667 return;
668 }
669 }
670
671 // If no faults where found then we have nothing to do here.
672
673 if (!faultsFound) {
674 logger.info("No unattended faults were found, no report sent to opshead");
675 return;
676 }
677
678 // Now for each of the managers which are to be informed,
679 // let them know of the unattended faults.
680
681 for (String emailAddress : MANAGEMENTMAILRECIPIENTS) {
682
683 try {
684
685 final javax.mail.Session session = javax.mail.Session.getInstance(props, null);
686
687 final Message msg = new MimeMessage(session);
688
689 msg.setFrom(new InternetAddress("faultdbadmin@ing.iac.es","Fault Database Admin"));
690
691 msg.setRecipients(Message.RecipientType.TO, InternetAddress.parse(emailAddress, false));
692
693 msg.setSubject("Unattended faults in the Fault Management System have been found");
694
695 msg.setContent("You have been sent this email by the <b>Fault Management System</B> to inform you of the following faults which have been found in the database which have been in the state <b>NEW</B> for more than 48 hours now. <P><P> " + newFaultsUnattended.toString(), "text/html");
696
697 msg.setHeader("X-Mailer", "Fault Management System");
698
699 msg.setSentDate(new java.util.Date());
700
701 // Now send the message to the email to the current
702 // recipient
703
704 Transport.send(msg);
705
706 } catch (Exception e) {
707 logger.error("Unable to send unattended faults report to " + emailAddress + " " + e.getMessage());
708 }
709 }
710
711 logger.info("Unattended faults mailshot has been sent");
712 }
713 }
714
715 /**
716 * This is a timertask which will be used to print all <b>new</B>
717 * faults in the previous 24 hours to the printer in the
718 * operations room.
719 */
720
721 class PrintNewFaultsInLastTwentyFourHours extends TimerTask {
722
723 /**
724 * Called when the timertask is scheduled to run. It finds all
725 * of the faults which have occurred in the last 24 hours and
726 * then prints them to the printer in the operations
727 * room. This should take place at 0830 every morning.
728 */
729
730 public void run () {
731
732 // This is the default destination for faults which are to
733 // be printed
734
735 final String OPSPRINTER = "ops_laser";
736
737 Connection connection = null;
738 Statement stmt = null;
739 ResultSet rs = null;
740
741 final ArrayList<Fault> newFaults = new ArrayList<Fault>();
742
743 logger.info("Printing faults from the last 24 hours to ops printer");
744
745 if (dataSource == null) {
746 dataSource = (DataSource) getServletContext().getAttribute("DBCPool");
747 }
748
749 try {
750
751 connection = dataSource.getConnection();
752 stmt = connection.createStatement();
753
754 // Find all faults which have been entered in the past
755 // 24 hours, nice use of intervals here I say. See
756 // http://www.postgresql.org/docs/techdocs.13 for more
757 // information about intervals, timestamps etc.
758
759 // The following line has been changed after DR17947
760
761 rs = stmt.executeQuery("select * from faultview_en where timeentered >= cast('now' as timestamp) - cast ('1 day' as interval) order by id desc");
762
763 while (rs.next()) {
764
765 Fault faultData = packFault(rs,true);
766 newFaults.add(faultData);
767 }
768
769 // Ensure that we clean up the database connection
770 // correctly so that the connection is returned to the
771 // database connection pool
772
773 rs.close();
774 connection.close();
775 stmt.close();
776
777 rs = null;
778 connection = null;
779 stmt = null;
780
781 } catch (SQLException e) {
782 e.printStackTrace();
783 } finally {
784
785 // Always make sure result sets and statements are closed,
786 // and the connection is returned to the pool
787
788 closeDatabaseConnections(connection,stmt,rs);
789 }
790
791 // Now we need to go through all of the faults which have
792 // been found and then print them out
793
794 for (Fault currentFault : newFaults) {
795
796 try {
797 printFault(currentFault.id,OPSPRINTER,true);
798 } catch (UnknownPrinterSpecifiedException e) {
799 logger.error("Unable to print to printer " + OPSPRINTER + " : " + e.getMessage());
800 return;
801 }
802 }
803
804 logger.info("New faults printed for operations on " + OPSPRINTER);
805 }
806 }
807
808 /**
809 * Constructor. This initialises the log4j and SMTP handling. It
810 * also starts timer threads which will be used to print the
811 * details of the new faults to the printer in the ops room and
812 * also remind management when there is a fault which has been in
813 * the <b>NEW</b> state for more than 48 hours.
814 */
815
816 public void init () {
817
818 // Initialise log4j
819
820 String config = getServletContext().getRealPath("/") + getInitParameter("setup");
821 PropertyConfigurator.configure(config);
822
823 logger = Logger.getLogger("GWTApplication.server.FaultServiceImpl");
824
825 logger.info("Initialising the Fault Management Servlet Vers $Id: FaultServiceImpl.java,v 1.47 2008/01/15 11:08:14 cb Exp $");
826
827 // Now sort out the java mail initialisation so that we send
828 // email
829
830 props = System.getProperties();
831 props.put("mail.smtp.host","smtphost.ing.iac.es");
832
833 // Now we need to initialise the SNMP handling so that we can
834 // send alerts when a fault is raised.
835
836 try {
837
838 targetAddress = GenericAddress.parse("udp:snmppc.ing.iac.es/162");
839 transport = new DefaultUdpTransportMapping();
840 snmp = new Snmp(transport);
841 usm = new USM(SecurityProtocols.getInstance(),new OctetString(MPv3.createLocalEngineID()), 0);
842
843 SecurityModels.getInstance().addSecurityModel(usm);
844 transport.listen();
845
846 } catch (IOException e) {
847 logger.error("Unable to initialise snmp " + e);
848 }
849
850 // Now start a timer task which will be used to print out the
851 // details of all of the new faults in the previous 24 hours
852 // to the ops room printer at 0830 every morning
853
854 GregorianCalendar calendar = new GregorianCalendar();
855
856 // Set the time of the calender to 0830
857
858 calendar.set(Calendar.HOUR_OF_DAY,8);
859 calendar.set(Calendar.MINUTE,30);
860
861 // Add one day to get the time at 0830 tomorrow morning
862
863 calendar.add(Calendar.DAY_OF_YEAR,1);
864
865 final Date eightThirtyTomorrowMorning = calendar.getTime();
866
867 // Now schedule printing of all new faults at 0830 the next
868 // morning and then subsequently all following mornings at the
869 // same time
870
871 PrintNewFaultsTimer.scheduleAtFixedRate(printNewFaultsInLastTwentyFourHrsTimerTask,eightThirtyTomorrowMorning,24 * 60 * 60 * 1000);
872
873 // Schedule the generation of reports of faults which have
874 // been left unattended for more than 48 hours to be sent to
875 // various users
876
877 calendar = new GregorianCalendar();
878
879 // Set the time of the calender to 0700
880
881 calendar.set(Calendar.HOUR_OF_DAY,7);
882 calendar.set(Calendar.MINUTE,0);
883
884 // Add one day to get the time in millisecs at 0700 tomorrow
885 // morning.
886
887 calendar.add(Calendar.DAY_OF_YEAR,1);
888
889 final Date sevenAMTomorrowMorning = calendar.getTime();
890
891 ProduceUnattendedFaultsMailShotTimer.scheduleAtFixedRate(MailManagementUntendedFaultsTimerTask,sevenAMTomorrowMorning,24 * 60 * 60 * 1000);
892
893 // Now create the timer task which send emails to the person
894 // assigned to a fault which has been in the pending state for
895 // more than 7 days. We do this every seven days.
896
897 ProduceOutstandingPendingFaultsMailShotTimer.scheduleAtFixedRate(NotifyPendingFaultsTimerTask,sevenAMTomorrowMorning, 7 * 24 * 60 * 60 * 1000);
898 }
899
900 /**
901 * Get the list of people as specified by the caller. It takes as
902 * an input parameter the name of database table which contains
903 * the data.
904 * <P>
905 * @param DatabaseView The name of the database view which
906 * contains the details of the group of people to return.
907 * @param Exclude the people which are in this table. If this
908 * value is null then we do not exclude anything.
909 * @param IncludeStaffWhichHaveLeft Include staff which have left the organisation.
910 * @throws UnableToGetListException If there is a problem getting
911 * the list back from the database
912 * @return ArrayList<Person> An ArrayList of People.
913 * @gwt.typeArgs <GWTApplication.client.Person>
914 */
915
916 private ArrayList<Person> getPersonDetails (final String DatabaseView,
917 final String ExclusionTable,
918 final boolean IncludeStaffWhichHaveLeft) throws UnableToGetListException {
919
920 final ArrayList<Person> People = new ArrayList<Person>();
921
922 Connection connection = null;
923 Statement stmt = null;
924 ResultSet rs = null;
925
926 if (dataSource == null) {
927 dataSource = (DataSource) getServletContext().getAttribute("DBCPool");
928 }
929
930 // Now get a database connection from the connection pool if
931 // we can
932
933 try {
934
935 Person newPerson;
936
937 connection = dataSource.getConnection();
938 stmt = connection.createStatement();
939
940 if (ExclusionTable == null) {
941
942 // Do we include the members of staff which are not with us any longer?
943
944 if (IncludeStaffWhichHaveLeft) {
945 rs = stmt.executeQuery("select * from " + DatabaseView + " order by surname");
946 } else {
947 rs = stmt.executeQuery("select * from " + DatabaseView + " where stillwithing = 'TRUE' order by surname");
948 }
949
950 } else {
951
952 // Do we include the members of staff which are not with us any longer?
953
954 if (IncludeStaffWhichHaveLeft) {
955
956 rs = stmt.executeQuery("select * from " + DatabaseView + " where " + DatabaseView + ".email not in (select email from " + ExclusionTable + ") and staffmember.username not in ('observer','unknown') order by surname");
957 } else {
958 rs = stmt.executeQuery("select * from " + DatabaseView + " where stillwithing = 'true' and " + DatabaseView + ".email not in (select email from " + ExclusionTable + ") and staffmember.username not in ('observer','unknown') order by surname");
959 }
960
961 logger.debug("select * from " + DatabaseView + " where " + DatabaseView + ".email not in (select email from " + ExclusionTable + ") and staffmember.username not in ('observer','unknown') order by surname") ;
962 }
963
964 while (rs.next()) {
965
966 newPerson = new Person();
967
968 newPerson.setSurname(rs.getString("surname"));
969 newPerson.setName(rs.getString("name"));
970 newPerson.setEmailAddress(rs.getString("email"));
971
972 People.add(newPerson);
973 }
974
975 // Ensure that we clean up the database connection
976 // correctly so that the connection is returned to the
977 // database connection pool
978
979 rs.close();
980 connection.close();
981 stmt.close();
982
983 rs = null;
984 connection = null;
985 stmt = null;
986
987 } catch (SQLException e) {
988 e.printStackTrace();
989 throw new UnableToGetListException("Unable to get list of people : " + e.getMessage());
990 } finally {
991
992 // Always make sure result sets and statements are closed,
993 // and the connection is returned to the pool
994
995 closeDatabaseConnections(connection,stmt,rs);
996 }
997
998 // Now return the details of the People to the caller
999
1000 return People;
1001
1002 }
1003
1004 /**
1005 * Return the names of all of the telescope operators to the
1006 * caller which are recalled from the fault database table
1007 * <b>TELESCOPEOPERATORVIEW</b>.
1008 * <P>
1009 *
1010 * @return ArrayList<Person> A array which contains objects of type
1011 * <b>Person</b>.
1012 * @throws UnableToGetListException If there is a problem getting
1013 * the list back from the database
1014 * @gwt.typeArgs <GWTApplication.client.Person>
1015 */
1016
1017 public ArrayList<Person> getTelescopeOperators(final boolean getOldStaff) throws UnableToGetListException {
1018 return getPersonDetails("TELESCOPEOPERATORVIEW",null,getOldStaff);
1019 }
1020
1021 /**
1022 * Return the names of all of the duty technicians to the
1023 * caller which are recalled from the fault database table
1024 * <b>DUTYTECHNICIANVIEW</b>.
1025 * <P>
1026 * @throws UnableToGetListException If there is a problem getting
1027 * the list back from the database
1028 * @gwt.typeArgs <GWTApplication.client.Person>
1029 * @return ArrayList<Person> A array which contains objects of type
1030 * <b>Person</b>.
1031 */
1032
1033 public ArrayList<Person> getDutyTechnicians(final boolean getOldStaff) throws UnableToGetListException {
1034 return getPersonDetails("DUTYTECHNICIANVIEW",null,getOldStaff);
1035 }
1036
1037 /**
1038 * Return the names of all of the support astronomers to the
1039 * caller which are recalled from the fault database table.
1040 * <b>SUPPORTASTRONOMERVIEW</b>.
1041 * @throws UnableToGetListException If there is a problem getting
1042 * the list back from the database
1043 * <P>
1044 *
1045 * @return ArrayList<Person> A array which contains objects of type
1046 * <b>Person</b>.
1047 * @gwt.typeArgs <GWTApplication.client.Person>
1048 */
1049
1050 public ArrayList<Person> getSupportAstronomers(final boolean getOldStaff) throws UnableToGetListException {
1051 return getPersonDetails("SUPPORTASTRONOMERVIEW",null,getOldStaff);
1052 }
1053
1054 /**
1055 * Return the names of all of the staff members to the
1056 * caller which are recalled from the fault database table.
1057 * <b>STAFFMEMBER</b>.
1058 * @throws UnableToGetListException If there is a problem getting
1059 * the list back from the database
1060 * <P>
1061 *
1062 * @return ArrayList<Person> A array which contains objects of type
1063 * <b>Person</b>.
1064 * @gwt.typeArgs <GWTApplication.client.Person>
1065 */
1066
1067 public ArrayList<Person> getStaffMembers(final boolean getOldStaff) throws UnableToGetListException {
1068 return getPersonDetails("STAFFMEMBER",null,getOldStaff);
1069 }
1070
1071 /**
1072 * Return the names of all of the sites to the
1073 * caller which are recalled from the fault database table.
1074 * <b>SITES</b>.
1075 * <P>
1076 * @throws UnableToGetListException If there is a problem getting
1077 * the list back from the database
1078 * @return ArrayList<Site> A array which contains objects of type
1079 * <b>Site</b>.
1080 * @gwt.typeArgs <GWTApplication.client.Site>
1081 */
1082
1083 public ArrayList<Site> getSites () throws UnableToGetListException {
1084
1085 final ArrayList<Site> Sites = new ArrayList<Site>();
1086
1087 Connection connection = null;
1088 Statement stmt = null;
1089 ResultSet rs = null;
1090
1091 if (dataSource == null) {
1092 dataSource = (DataSource) getServletContext().getAttribute("DBCPool");
1093 }
1094
1095 // Now get a database connection from the connection pool if
1096 // we can
1097
1098 try {
1099
1100 Site newSite;
1101
1102 connection = dataSource.getConnection();
1103 stmt = connection.createStatement();
1104
1105 rs = stmt.executeQuery("select * from site order by id");
1106
1107 while (rs.next()) {
1108
1109 newSite = new Site();
1110
1111 newSite.setEn_description(rs.getString("en_description"));
1112 newSite.setSp_description(rs.getString("sp_description"));
1113 newSite.setId(rs.getString("id"));
1114
1115 Sites.add(newSite);
1116 }
1117
1118 // Ensure that we clean up the database connection
1119 // correctly so that the connection is returned to the
1120 // database connection pool
1121
1122 rs.close();
1123 connection.close();
1124 stmt.close();
1125
1126 rs = null;
1127 connection = null;
1128 stmt = null;
1129
1130 } catch (SQLException e) {
1131 e.printStackTrace();
1132 throw new UnableToGetListException("Unable to get list of sites : " + e.getMessage());
1133 } finally {
1134
1135 // Always make sure result sets and statements are closed,
1136 // and the connection is returned to the pool
1137
1138 closeDatabaseConnections(connection,stmt,rs);
1139 }
1140
1141 // Now return the details of the Sites to the caller
1142
1143 return Sites;
1144 }
1145
1146 /**
1147 * Return the names of all of the system to the
1148 * caller which are recalled from the fault database table.
1149 * <b>SITES</b>.
1150 * <P>
1151 * @throws UnableToGetListException If there is a problem getting
1152 * the list back from the database
1153 * @return ArrayList<FDBSystem> A array which contains objects of type
1154 * <b>System</b>.
1155 * @gwt.typeArgs <GWTApplication.client.FDBSystem>
1156 */
1157
1158 public ArrayList<FDBSystem> getSystems () throws UnableToGetListException {
1159
1160 final ArrayList<FDBSystem> Systems = new ArrayList<FDBSystem>();
1161
1162 Connection connection = null;
1163 Statement stmt = null;
1164 ResultSet rs = null;
1165
1166 if (dataSource == null) {
1167 dataSource = (DataSource) getServletContext().getAttribute("DBCPool");
1168 }
1169
1170 // Now get a database connection from the connection pool if
1171 // we can
1172
1173 try {
1174
1175 FDBSystem newSystem;
1176
1177 connection = dataSource.getConnection();
1178 stmt = connection.createStatement();
1179
1180 rs = stmt.executeQuery("select * from system order by id");
1181
1182 while (rs.next()) {
1183
1184 newSystem = new FDBSystem();
1185
1186 newSystem.setEn_description(rs.getString("en_description"));
1187 newSystem.setSp_description(rs.getString("sp_description"));
1188 newSystem.setId(rs.getString("id"));
1189
1190 Systems.add(newSystem);
1191 }
1192
1193 // Ensure that we clean up the database connection
1194 // correctly so that the connection is returned to the
1195 // database connection pool
1196
1197 rs.close();
1198 connection.close();
1199 stmt.close();
1200
1201 rs = null;
1202 connection = null;
1203 stmt = null;
1204
1205 } catch (SQLException e) {
1206 e.printStackTrace();
1207 throw new UnableToGetListException("Unable to get list of systems : " + e.getMessage());
1208 } finally {
1209
1210 // Always make sure result sets and statements are closed,
1211 // and the connection is returned to the pool
1212
1213 closeDatabaseConnections(connection,stmt,rs);
1214 }
1215
1216 // Now return the details of the Systems to the caller
1217
1218 return Systems;
1219 }
1220
1221
1222 /**
1223 * Return the names of all of the severity to the
1224 * caller which are recalled from the fault database table.
1225 * <b>SEVERITY</b>.
1226 * <P>
1227 * @throws UnableToGetListException If there is a problem getting
1228 * the list back from the database
1229 * @return ArrayList<GWTApplication.client.Severity> A array which contains objects of type
1230 * <b>Severity</b>.
1231 * @gwt.typeArgs <GWTApplication.client.Severity>
1232 */
1233
1234 public ArrayList<GWTApplication.client.Severity> getSeverity () throws UnableToGetListException {
1235
1236 ArrayList<GWTApplication.client.Severity> Severity = new ArrayList<GWTApplication.client.Severity>();
1237
1238 Connection connection = null;
1239 Statement stmt = null;
1240 ResultSet rs = null;
1241
1242 if (dataSource == null) {
1243 dataSource = (DataSource) getServletContext().getAttribute("DBCPool");
1244 }
1245
1246 // Now get a database connection from the connection pool if
1247 // we can
1248
1249 try {
1250
1251 GWTApplication.client.Severity newSeverity;
1252
1253 connection = dataSource.getConnection();
1254 stmt = connection.createStatement();
1255
1256 rs = stmt.executeQuery("select * from severity order by id");
1257
1258 while (rs.next()) {
1259
1260 newSeverity = new GWTApplication.client.Severity();
1261
1262 newSeverity.setEn_description(rs.getString("en_description"));
1263 newSeverity.setSp_description(rs.getString("sp_description"));
1264 newSeverity.setId(rs.getString("id"));
1265
1266 Severity.add(newSeverity);
1267 }
1268
1269 // Ensure that we clean up the database connection
1270 // correctly so that the connection is returned to the
1271 // database connection pool
1272
1273 rs.close();
1274 connection.close();
1275 stmt.close();
1276
1277 rs = null;
1278 connection = null;
1279 stmt = null;
1280
1281 } catch (SQLException e) {
1282 e.printStackTrace();
1283 throw new UnableToGetListException("Unable to get list of severities : " + e.getMessage());
1284 } finally {
1285
1286 // Always make sure result sets and statements are closed,
1287 // and the connection is returned to the pool
1288
1289 closeDatabaseConnections(connection,stmt,rs);
1290 }
1291
1292 // Now return the details of the Severity to the caller
1293
1294 return Severity;
1295 }
1296
1297
1298 /**
1299 * Return the names of all of the instrument to the
1300 * caller which are recalled from the fault database table.
1301 * <b>INSTRUMENT</b>.
1302 * <P>
1303 * @param allInstruments Whether to get all instruments or just
1304 * those which are not decommissioned.
1305 * @throws UnableToGetListException If there is a problem getting
1306 * the list back from the database
1307 * @return ArrayList<Instrument> A array which contains objects of type
1308 * <b>Instrument</b>.
1309 * @gwt.typeArgs <GWTApplication.client.Instrument>
1310 */
1311
1312 public ArrayList<Instrument> getInstruments (final boolean allInstruments) throws UnableToGetListException {
1313
1314 final ArrayList<Instrument> Instrument = new ArrayList<Instrument>();
1315
1316 Connection connection = null;
1317 Statement stmt = null;
1318 ResultSet rs = null;
1319
1320 if (dataSource == null) {
1321 dataSource = (DataSource) getServletContext().getAttribute("DBCPool");
1322 }
1323
1324 // Now get a database connection from the connection pool if
1325 // we can
1326
1327 try {
1328
1329 Instrument newInstrument;
1330
1331 connection = dataSource.getConnection();
1332 stmt = connection.createStatement();
1333
1334 if (allInstruments) {
1335 rs = stmt.executeQuery("select * from instrument order by id");
1336 } else {
1337 rs = stmt.executeQuery("select * from instrument where decommissioned = 'false' order by id");
1338 }
1339
1340 while (rs.next()) {
1341
1342 newInstrument = new Instrument();
1343
1344 newInstrument.setEn_description(rs.getString("en_description"));
1345 newInstrument.setSp_description(rs.getString("sp_description"));
1346 newInstrument.setId(rs.getString("id"));
1347
1348 Instrument.add(newInstrument);
1349 }
1350
1351 // Ensure that we clean up the database connection
1352 // correctly so that the connection is returned to the
1353 // database connection pool
1354
1355 rs.close();
1356 connection.close();
1357 stmt.close();
1358
1359 rs = null;
1360 connection = null;
1361 stmt = null;
1362
1363 } catch (SQLException e) {
1364
1365 e.printStackTrace();
1366
1367 // Always make sure result sets and statements are closed,
1368 // and the connection is returned to the pool
1369
1370 throw new UnableToGetListException("Unable to get list of instruments : " + e.getMessage());
1371
1372 } finally {
1373
1374 closeDatabaseConnections(connection,stmt,rs);
1375
1376 }
1377
1378 // Now return the details of the Instrument to the caller
1379
1380 return Instrument;
1381 }
1382
1383 /**
1384 * Used to update the details of fault which has been modified by
1385 * the user. The details should have had their format verified by
1386 * the client and should be ready for simply updating the contents
1387 * in the database now.
1388 * @param FaultUpdateDetails This contains the details of the
1389 * changes that were made to the fault which need to be
1390 * re-inserted into the database.
1391 * @param locale This is the locale in which we are operating within
1392 * @throws UnableToUpdateFaultException If the fault could not be
1393 * updated for whatever reason
1394 */
1395
1396 public boolean updateFault(final Fault FaultUpdateDetails,final String locale) throws UnableToUpdateFaultException {
1397
1398 logger.debug("Updating fault " + FaultUpdateDetails.id);
1399
1400 Connection connection = null;
1401 Statement stmt = null;
1402 ResultSet rs = null;
1403
1404 // Now we need to build an SQL statement which can be used to
1405 // put the data into the fault table in the database
1406
1407 final String updateStatement = "update fault set description = '" + makeTextFieldDatabaseCompatible(FaultUpdateDetails.description) + "',title = '" + makeTextFieldDatabaseCompatible(FaultUpdateDetails.title) + "', timeoccured = '" + FaultUpdateDetails.dateOccured + " " + FaultUpdateDetails.timeOccured + "',timelost = '" + FaultUpdateDetails.timeLost + "',telescopeoperator = '" + FaultUpdateDetails.telescopeOperatorEmail + "',site = '" + FaultUpdateDetails.site_id + "', observer = '" + FaultUpdateDetails.observer + "', system = '" + FaultUpdateDetails.system_id + "', instrument = '" + FaultUpdateDetails.instrument_id + "', dutytechnician = '" + FaultUpdateDetails.dutyTechnicianEmail + "', supportastronomer = '" + FaultUpdateDetails.supportAstronomerEmail + "',severity = '" + FaultUpdateDetails.severity_id + "', state = '" + FaultUpdateDetails.state_id + "', priority = " + FaultUpdateDetails.priority + ", faulttype = '" + FaultUpdateDetails.faultType_id + "', assignedto = '" + FaultUpdateDetails.assignedToEmail + "', lastmodified = 'NOW' where id = " + FaultUpdateDetails.id;
1408
1409 logger.debug( "Fault update statement >> " + updateStatement);
1410
1411 if (dataSource == null) {
1412 dataSource = (DataSource) getServletContext().getAttribute("DBCPool");
1413 }
1414
1415 // Now get a database connection from the connection pool if
1416 // we can to do the update
1417
1418 try {
1419
1420 connection = dataSource.getConnection();
1421 stmt = connection.createStatement();
1422
1423 // If the person to which this fault is assigned is
1424 // specified then we need update the date when the fault
1425 // was assigned if it has not been set already. We only
1426 // update the assigned date once.
1427
1428 if (!FaultUpdateDetails.assignedToEmail.equals("UNKNOWN")) {
1429 stmt.execute("update fault set assigneddate = 'now' where id = " + FaultUpdateDetails.id + " and assignedto = 'UNKNOWN'");
1430 }
1431
1432 // Get the time of the last update for this fault and
1433 // ensure that we are updating the fault with out of date
1434 // information (i.e. somebody in the background as not
1435 // come along and updated the fault whilst we have been
1436 // updating the fault.
1437
1438 rs = stmt.executeQuery("select lastmodified from fault where id = " + FaultUpdateDetails.id);
1439
1440 if (rs.next()) {
1441
1442 final Timestamp currentTimeStamp = rs.getTimestamp("lastmodified");
1443
1444 final Timestamp lastModifiedTime = new Timestamp(FaultUpdateDetails.lastModified);
1445
1446 if (currentTimeStamp.getTime() != lastModifiedTime.getTime()) {
1447 logger.info("Fault " + FaultUpdateDetails.id + " has been changed by another user before we had the chance to save the update");
1448 throw new UnableToUpdateFaultException("Fault has been updated/closed since you started editing the fault");
1449 }
1450
1451 } else {
1452 throw new UnableToUpdateFaultException("Unable to find the details of fault " + FaultUpdateDetails.id);
1453 }
1454
1455 rs.close();
1456
1457 // Now update the fault if the fault has not already been updated in the background
1458
1459 stmt.execute(updateStatement);
1460 stmt.close();
1461
1462 connection.close();
1463 stmt.close();
1464
1465 connection = null;
1466 stmt = null;
1467
1468 } catch (SQLException e) {
1469 e.printStackTrace();
1470 throw new UnableToUpdateFaultException("Unable to update the fault " + e.getMessage());
1471 } finally {
1472
1473 // Always make sure result sets and statements are closed,
1474 // and the connection is returned to the pool
1475
1476 closeDatabaseConnections(connection,stmt,rs);
1477 }
1478
1479 return true;
1480 }
1481
1482 /**
1483 * This method will be called to create a new fault in the fault
1484 * database from the data which should have been passed into the
1485 * method by AJAX client which should be running on the clients
1486 * web browser. All of the parameters should have been verified by the
1487 * client before they are sent down to this method so we will
1488 * not verify them again.
1489 *
1490 * @param newFault This structure will contain the details which
1491 * are to be entered into the database when the fault is created.
1492 * @throws UnableToCreateFaultException When the servlet is unable
1493 * to create the fault in the database it throws this exception.
1494 */
1495
1496 public Integer createFault (final Fault newFault) throws UnableToCreateFaultException {
1497
1498 Connection connection = null;
1499 Statement stmt = null;
1500 ResultSet rs = null;
1501
1502 int defectNumber = -9999;
1503
1504 // Now we need to build an SQL statement which can be used to
1505 // put the data into the fault table in the database
1506
1507 final String insertStatement = "insert into fault (description,title,timeoccured,timelost,telescopeoperator,site,observer,system,instrument,dutytechnician,supportastronomer,enteredby,severity,state,faultopen,lastmodified) values (" +
1508 "'" + makeTextFieldDatabaseCompatible(newFault.description) + "'," +
1509 "'" + makeTextFieldDatabaseCompatible(newFault.title) + "'," +
1510 "'" + newFault.dateOccured + " " + newFault.timeOccured + "'," +
1511 "'" + newFault.timeLost + "'," +
1512 "'" + newFault.telescopeOperatorEmail + "'," +
1513 "'" + newFault.site + "'," +
1514 "'" + newFault.observer + "'," +
1515 "'" + newFault.system + "'," +
1516 "'" + newFault.instrument + "'," +
1517 "'" + newFault.dutyTechnicianEmail + "'," +
1518 "'" + newFault.supportAstronomerEmail + "'," +
1519 "'" + newFault.enteredByEmail + "'," +
1520 "'" + newFault.severity + "'," +
1521 "'" + "NEW" + "'," +
1522 "'true','NOW')";
1523
1524 logger.debug( "Fault insertion statement >> " + insertStatement);
1525
1526 if (dataSource == null) {
1527 dataSource = (DataSource) getServletContext().getAttribute("DBCPool");
1528 }
1529
1530 // Now get a database connection from the connection pool if
1531 // we can
1532
1533 try {
1534
1535 connection = dataSource.getConnection();
1536 stmt = connection.createStatement();
1537
1538 stmt.execute(insertStatement);
1539 stmt.close();
1540
1541 // Now get defect number which was given to the fault so
1542 // that we can return it to the caller
1543
1544 stmt = connection.createStatement();
1545
1546 rs = stmt.executeQuery("select max(id) from fault");
1547
1548 rs.next();
1549
1550 defectNumber = rs.getInt(1);
1551
1552 logger.debug("Defect number is " + defectNumber);
1553
1554 // Now if the user has requested that he should be
1555 // informed when there is a change to the fault we make an
1556 // entry into the fault listener table. Note that we can
1557 // only do this if the user has authenticated himself with
1558 // his real user, not as a casual user such as observer.
1559
1560 if (newFault.userWantsEmailUpdates) {
1561
1562 logger.debug(newFault.enteredByEmail);
1563
1564 if (!newFault.enteredByEmail.equalsIgnoreCase("UNKNOWN") &&
1565 !newFault.enteredByEmail.equalsIgnoreCase(FaultDatabaseConstants.GUEST) &&
1566 !newFault.enteredByEmail.equalsIgnoreCase("OBSERVER")) {
1567
1568 // Now make an entry into the fault listener table
1569
1570 stmt = connection.createStatement();
1571
1572 stmt.execute("insert into faultlistener(email,id) values ('" + newFault.enteredByEmail + "'," + defectNumber + ")");
1573 stmt.close();
1574 }
1575 }
1576
1577 // Ensure that we clean up the database connection
1578 // correctly so that the connection is returned to the
1579 // database connection pool
1580
1581 rs.close();
1582 connection.close();
1583 stmt.close();
1584
1585 rs = null;
1586 connection = null;
1587 stmt = null;
1588
1589 } catch (SQLException e) {
1590 e.printStackTrace();
1591
1592 throw new UnableToCreateFaultException("Unable to create fault : " + e.getMessage());
1593
1594 } finally {
1595
1596 // Always make sure result sets and statements are closed,
1597 // and the connection is returned to the pool
1598
1599 closeDatabaseConnections(connection,stmt,rs);
1600 }
1601
1602 // Now we need to send a SNMP message to indicate a new fault
1603 // has been raised by the system
1604
1605 raiseSNMPAlert(defectNumber,newFault);
1606
1607 return new Integer(defectNumber);
1608 }
1609
1610 /**
1611 * This is user to raise an SMNP alert when a fault has been
1612 * raised in the system.
1613 * <P>
1614 * In order to test this facility out one must use the SmnpRequest
1615 * class which comes the snmp4j package and set up an smtp
1616 * manager. This can be done with something like the following :
1617 * <P>
1618 * <pre>java -cp /<Java path>/SNMP4J.jar org.snmp4j.tools.console.SnmpRequest -u aSecurityName -Ol 0.0.0.0/<port no>
1619 * </pre>
1620 * The port number is the port number which the SNMP manager is to
1621 * listen on. You will need to set the port in the init() method as
1622 * well to match the port which the manager is using.
1623 * <P>
1624 * @param defectNumber This is the number of the defect which has been raised
1625 * @param fault The details of the fault which is to be entered.
1626 */
1627
1628 void raiseSNMPAlert(final int defectNumber,final Fault fault) {
1629
1630 // Setting up target
1631
1632 CommunityTarget target = new CommunityTarget();
1633 target.setCommunity(new OctetString("public"));
1634 target.setAddress(targetAddress);
1635 target.setRetries(2);
1636 target.setTimeout(1500);
1637 target.setVersion(SnmpConstants.version2c);
1638
1639 // Create the data packet which contains the details of the
1640 // alert to send to the SNMP manager
1641
1642 PDU pdu = new PDU();
1643 pdu.add(new VariableBinding(new OID("1.3.6.1.4.777"), new OctetString("Fault " + Integer.valueOf(defectNumber) + " <" + fault.title + ">" + " at the " + fault.site + " (Time Lost " + fault.timeLost + " (HH:MM))" )));
1644 pdu.add(new VariableBinding(new OID("1.3.6.1.4.777"), new OctetString("Fault " + Integer.valueOf(defectNumber) + " <" + fault.title + ">" + " at the " + fault.site + " (Time Lost " + fault.timeLost + " (HH:MM))" )));
1645 pdu.add(new VariableBinding(new OID("1.3.6.1.4.777"), new OctetString("Fault " + Integer.valueOf(defectNumber) + " <" + fault.title + ">" + " at the " + fault.site + " (Time Lost " + fault.timeLost + " (HH:MM))" )));
1646
1647 pdu.setType(PDU.TRAP);
1648
1649 // Now send the TRAP to the SNMP manager to inform him that a
1650 // new fault has been created.
1651
1652 try {
1653 snmp.send(pdu, target);
1654 } catch (IOException e) {
1655 logger.error("Unable to send SNMP trap" + e.getMessage());
1656 }
1657 }
1658
1659 /**
1660 * Get all faults which go back to a specific date which has been
1661 * specified by the caller.
1662 * <P>
1663 * @param SearchType This is the type of search which is to be performed
1664 * @param locale This is the locale which has been chosen by the user
1665 * @return ArrayList<FaultType> This is an array of type fault which will
1666 * contain the details of all of the faults which were found as
1667 * part of the search.
1668 */
1669
1670 public ArrayList<Fault> getQuickViewFaultData (final int SearchType,final String locale) {
1671
1672 logger.debug("Getting quick view information from the database");
1673
1674 // First we need to create the SQL which we will use to get
1675 // the fault information back from the database
1676
1677 StringBuffer selectString = new StringBuffer();
1678
1679 String sectionAllocatedTo = null;
1680
1681 String view = null;
1682 String whereClause = " where ";
1683 boolean searchOutstandingFaults = false;
1684
1685 final ArrayList<Fault> faultsFound = new ArrayList<Fault>();
1686
1687 java.util.GregorianCalendar dateToSearchFrom = new java.util.GregorianCalendar();
1688
1689 // Set the time to the current time
1690
1691 dateToSearchFrom.setTime(new java.util.Date());
1692
1693 switch (SearchType) {
1694
1695 case QuickViewReport.TWENTYFOURHOURS :
1696 dateToSearchFrom.add(Calendar.DAY_OF_MONTH,-1);
1697 break;
1698
1699 case QuickViewReport.SEVENTYTWOHOURS :
1700 dateToSearchFrom.add(Calendar.DAY_OF_MONTH,-3);
1701 break;
1702
1703 case QuickViewReport.ONEWEEK :
1704 dateToSearchFrom.add(Calendar.DAY_OF_MONTH,-7);
1705 break;
1706
1707 case QuickViewReport.ONEMONTH :
1708 dateToSearchFrom.add(Calendar.MONTH,-1);
1709 break;
1710
1711 case QuickViewReport.OUTSTANDING :
1712 dateToSearchFrom = null;
1713 searchOutstandingFaults = true;
1714 break;
1715
1716 case QuickViewReport.TI_OUTSTANDING :
1717 dateToSearchFrom = null;
1718 searchOutstandingFaults = true;
1719 sectionAllocatedTo = "T&I";
1720 break;
1721
1722 case QuickViewReport.COMPUTING_OUTSTANDING :
1723 dateToSearchFrom = null;
1724 searchOutstandingFaults = true;
1725 sectionAllocatedTo = "COMPUTING";
1726 break;
1727
1728 case QuickViewReport.OPERATIONS_OUTSTANDING :
1729 dateToSearchFrom = null;
1730 searchOutstandingFaults = true;
1731 sectionAllocatedTo = "OPERATIONS";
1732 break;
1733
1734 case QuickViewReport.ASTRONOMY_OUTSTANDING :
1735 dateToSearchFrom = null;
1736 searchOutstandingFaults = true;
1737 sectionAllocatedTo = "ASTRONOMY";
1738 break;
1739
1740 }
1741
1742 // Depending on whether we are in spanish or english we search
1743 // across different database views.
1744
1745 if (locale.equalsIgnoreCase("en")) {
1746 view = "faultview_en";
1747 } else {
1748 view = "faultview_sp";
1749 }
1750
1751 selectString.append("select * from " + view);
1752
1753 // Now put in the date filter into the select statement
1754
1755 final SimpleDateFormat dateFormatter = new SimpleDateFormat("yyyy-MM-dd HH:mm");
1756
1757 if (dateToSearchFrom != null) {
1758 selectString.append(" " + whereClause + " timeentered > '" + dateFormatter.format(dateToSearchFrom.getTime()) + "'");
1759 whereClause = " and ";
1760 }
1761
1762 // If we have been requested to display only oustanding faults
1763 // then we should only display faults which are new,pending or
1764 // inhand.
1765
1766 if (searchOutstandingFaults) {
1767
1768 selectString.append(" " + whereClause + " state_id in ('NEW','PENDING','INHAND')");
1769
1770 if (sectionAllocatedTo != null) {
1771 selectString.append(" and faulttype_id = '" + sectionAllocatedTo + "'");
1772 }
1773
1774 whereClause = " and ";
1775 }
1776
1777 selectString.append(" order by id desc limit " + MAXNUMBEROFRESULTS);
1778
1779 // Now get the data back from the database
1780
1781 Connection connection = null;
1782 Statement stmt = null;
1783 ResultSet rs = null;
1784
1785 // Now get a database connection from the connection pool if
1786 // we can
1787
1788 if (dataSource == null) {
1789 dataSource = (DataSource) getServletContext().getAttribute("DBCPool");
1790 }
1791
1792 try {
1793
1794 logger.debug("quick view select string is " + selectString.toString());
1795
1796 connection = dataSource.getConnection();
1797 stmt = connection.createStatement();
1798 rs = stmt.executeQuery(selectString.toString());
1799
1800 while (rs.next()) {
1801 faultsFound.add(packFault(rs,true));
1802 }
1803
1804 // Ensure that we clean up the database connection
1805 // correctly so that the connection is returned to the
1806 // database connection pool
1807
1808 rs.close();
1809 connection.close();
1810 stmt.close();
1811
1812 rs = null;
1813 connection = null;
1814 stmt = null;
1815
1816 } catch (SQLException e) {
1817 e.printStackTrace();
1818 } finally {
1819
1820 // Always make sure result sets and statements are closed,
1821 // and the connection is returned to the pool
1822
1823 closeDatabaseConnections(connection,stmt,rs);
1824 }
1825
1826 return faultsFound;
1827 }
1828
1829
1830 /**
1831 * This will be simply used to pack the details of a fault as read
1832 * out of the database into the fault class. It can be configured
1833 * to work on either a result set from a fault view or the fault
1834 * table.
1835 * @throws SQLException If there is a problem reading the data from the database
1836 * @param rs This is a result set which can be used to read the details of the fault which is to be packed.
1837 * @param includeFullViewInformation Assumes that the result set
1838 * belongs to a view and that we should pack all of that
1839 * information as well.
1840 * @return Fault This is a instance of the fault object which contains the details of the fault.
1841 * @gwt.typeArgs <GWTApplication.client.Fault>
1842 */
1843
1844 public Fault packFault (final ResultSet rs,final boolean includeFullViewInformation) throws SQLException {
1845
1846 Fault fault = new Fault();
1847
1848 Timestamp temp = rs.getTimestamp("lastmodified");
1849
1850 fault.lastModified = temp.getTime();
1851
1852 fault.id = rs.getInt("id");
1853 fault.description = rs.getString("description");
1854 fault.title = rs.getString("title");
1855 fault.timeLost = rs.getString("timelost");
1856 fault.observer = rs.getString("observer");
1857 fault.site = rs.getString("site");
1858 fault.severity = rs.getString("severity");
1859
1860 fault.faultType = rs.getString("faulttype");
1861
1862 fault.instrument = rs.getString("instrument");
1863
1864 fault.priority = rs.getInt("priority");
1865
1866 fault.state = rs.getString("state");
1867 fault.faultOpen = rs.getBoolean("faultopen");
1868 fault.system = rs.getString("system");
1869
1870 // If we have been requested to include all of the data from
1871 // the view then we should otherwise we only include the data
1872 // from the base table
1873
1874 if (includeFullViewInformation) {
1875
1876 fault.assignedToEmail = rs.getString("assignedto_email");
1877 fault.enteredByEmail = rs.getString("enteredby_email");
1878 fault.dutyTechnicianEmail = rs.getString("dutytechnician_email");
1879 fault.supportAstronomerEmail = rs.getString("sa_email");
1880 fault.telescopeOperatorEmail = rs.getString("to_email");
1881
1882 fault.severity_id = rs.getString("severity_id");
1883 fault.faultType_id = rs.getString("faulttype_id");
1884 fault.instrument_id = rs.getString("instrument_id");
1885 fault.site_id = rs.getString("site_id");
1886 fault.state_id = rs.getString("state_id");
1887 fault.system_id = rs.getString("system_id");
1888
1889 fault.assignedToSurname = rs.getString("assignedto_surname");
1890 fault.assignedToName = rs.getString("assignedto_name");
1891
1892 fault.enteredBySurname = rs.getString("enteredby_surname");
1893 fault.enteredByName = rs.getString("enteredby_name");
1894
1895 fault.dutyTechnicianSurname = rs.getString("dutytechnician_surname");
1896 fault.dutyTechnicianName = rs.getString("dutytechnician_name");
1897
1898 fault.supportAstronomerSurname = rs.getString("sa_surname");
1899 fault.supportAstronomerName = rs.getString("sa_name");
1900
1901 fault.telescopeOperatorSurname = rs.getString("to_surname");
1902 fault.telescopeOperatorName = rs.getString("to_name");
1903
1904 } else {
1905
1906 // The fields in the fault resultset are slightly
1907 // different to those of a view
1908
1909 fault.assignedToEmail = rs.getString("assignedto");
1910 fault.enteredByEmail = rs.getString("enteredby");
1911 fault.dutyTechnicianEmail = rs.getString("dutytechnician");
1912 fault.supportAstronomerEmail = rs.getString("supportastronomer");
1913 fault.telescopeOperatorEmail = rs.getString("telescopeoperator");
1914
1915 }
1916
1917 // Now get the various dates & times and translate them back
1918 // to something that can be displayed by the web browser.
1919
1920 java.sql.Timestamp assignedTime, timeOccuredTime, enteredTime;
1921
1922 final SimpleDateFormat timeFormatter = new SimpleDateFormat("HH:mm");
1923 final SimpleDateFormat dateFormatter = new SimpleDateFormat("dd/MM/yyyy");
1924
1925 enteredTime = rs.getTimestamp("timeentered");
1926 timeOccuredTime = rs.getTimestamp("timeoccured");
1927 assignedTime = rs.getTimestamp("assigneddate");
1928
1929 // If the entered time is specified then strip out the time
1930 // and the date
1931
1932 if (enteredTime != null) {
1933 fault.timeEntered = timeFormatter.format(enteredTime);
1934 fault.dateEntered = dateFormatter.format(enteredTime);
1935 }
1936
1937 if (timeOccuredTime != null) {
1938 fault.timeOccured = timeFormatter.format(timeOccuredTime);
1939 fault.dateOccured = dateFormatter.format(timeOccuredTime);
1940 }
1941
1942 if (assignedTime != null) {
1943 fault.assignedDate = dateFormatter.format(assignedTime);
1944 }
1945
1946 // Get the total amount spent on this fault
1947
1948 fault.totalTimeSpentOnFault = calculateTotalAmountOfTimeSpentOnFault(fault.id);
1949
1950 return fault;
1951 }
1952
1953 /**
1954 * This will be simply used to pack the details of a linked fault
1955 * into a fault structure. This is <b> not </b> the same as the
1956 * packFault method as we are packing the information from the
1957 * fault tabl and not a fault view. This is for reasons of
1958 * performance as the SQL to get the details of the links from the
1959 * database which was working across the views was taking 3
1960 * seconds to perform which was just unacceptable to such a
1961 * critical section of code.
1962 * @throws SQLException If there is a problem reading the data from the database
1963 * @param rs This is a result set which can be used to read the details of the fault which is to be packed.
1964 * @return Fault This is a instance of the fault object which contains the details of the fault.
1965 * @gwt.typeArgs <GWTApplication.client.Fault>
1966 */
1967
1968 private Fault packLink (final ResultSet rs) throws SQLException {
1969
1970 Fault fault = new Fault();
1971
1972 Timestamp temp = rs.getTimestamp("lastmodified");
1973
1974 fault.lastModified = temp.getTime();
1975
1976 fault.id = rs.getInt("id");
1977 fault.description = rs.getString("description");
1978 fault.title = rs.getString("title");
1979 fault.timeLost = rs.getString("timelost");
1980 fault.observer = rs.getString("observer");
1981 fault.site_id = rs.getString("site");
1982 fault.severity_id = rs.getString("severity");
1983 fault.faultType_id = rs.getString("faulttype");
1984 fault.instrument_id = rs.getString("instrument");
1985 fault.priority = rs.getInt("priority");
1986 fault.state_id = rs.getString("state");
1987 fault.faultOpen = rs.getBoolean("faultopen");
1988 fault.system_id = rs.getString("system");
1989 fault.assignedToEmail = rs.getString("assignedto");
1990 fault.enteredByEmail = rs.getString("enteredby");
1991 fault.dutyTechnicianEmail = rs.getString("dutytechnician");
1992 fault.supportAstronomerEmail = rs.getString("supportastronomer");
1993 fault.telescopeOperatorEmail = rs.getString("telescopeoperator");
1994
1995 // Now get the various dates & times and translate them back
1996 // to something that can be displayed by the web browser.
1997
1998 java.sql.Timestamp assignedTime, timeOccuredTime, enteredTime;
1999
2000 final SimpleDateFormat timeFormatter = new SimpleDateFormat("HH:mm");
2001 final SimpleDateFormat dateFormatter = new SimpleDateFormat("dd/MM/yyyy");
2002
2003 enteredTime = rs.getTimestamp("timeentered");
2004 timeOccuredTime = rs.getTimestamp("timeoccured");
2005 assignedTime = rs.getTimestamp("assigneddate");
2006
2007 // If the entered time is specified then strip out the time
2008 // and the date
2009
2010 if (enteredTime != null) {
2011 fault.timeEntered = timeFormatter.format(enteredTime);
2012 fault.dateEntered = dateFormatter.format(enteredTime);
2013 }
2014
2015 if (timeOccuredTime != null) {
2016 fault.timeOccured = timeFormatter.format(timeOccuredTime);
2017 fault.dateOccured = dateFormatter.format(timeOccuredTime);
2018 }
2019
2020 if (assignedTime != null) {
2021 fault.assignedDate = dateFormatter.format(assignedTime);
2022 }
2023
2024 // Get the total amount spent on this fault
2025
2026 fault.totalTimeSpentOnFault = calculateTotalAmountOfTimeSpentOnFault(fault.id);
2027
2028 return fault;
2029 }
2030
2031 /**
2032 * Return the names of all of the FaultType to the
2033 * caller which are recalled from the fault database table.
2034 * <b>FAULTTYPE</b>.
2035 * <P>
2036 * @throws UnableToGetListException If there is a problem getting
2037 * the list back from the database
2038 * @return ArrayList<FaultType> A array which contains objects of type
2039 * <b>FaultType</b>.
2040 * @gwt.typeArgs <GWTApplication.client.FaultType>
2041 */
2042
2043 public ArrayList<FaultType> getFaultType () throws UnableToGetListException {
2044
2045 ArrayList<FaultType> FaultType = new ArrayList<FaultType>();
2046
2047 Connection connection = null;
2048 Statement stmt = null;
2049 ResultSet rs = null;
2050
2051 if (dataSource == null) {
2052 dataSource = (DataSource) getServletContext().getAttribute("DBCPool");
2053 }
2054
2055 // Now get a database connection from the connection pool if
2056 // we can
2057
2058 try {
2059
2060 FaultType newFaultType;
2061
2062 connection = dataSource.getConnection();
2063 stmt = connection.createStatement();
2064
2065 rs = stmt.executeQuery("select * from FaultType order by id");
2066
2067 while (rs.next()) {
2068
2069 newFaultType = new FaultType();
2070
2071 newFaultType.setEn_description(rs.getString("en_description"));
2072 newFaultType.setSp_description(rs.getString("sp_description"));
2073 newFaultType.setId(rs.getString("id"));
2074
2075 FaultType.add(newFaultType);
2076 }
2077
2078 // Ensure that we clean up the database connection
2079 // correctly so that the connection is returned to the
2080 // database connection pool
2081
2082 rs.close();
2083 connection.close();
2084 stmt.close();
2085
2086 rs = null;
2087 connection = null;
2088 stmt = null;
2089
2090 } catch (SQLException e) {
2091 e.printStackTrace();
2092 throw new UnableToGetListException("Unable to get list of fault types : " + e.getMessage());
2093
2094 } finally {
2095
2096 // Always make sure result sets and statements are closed,
2097 // and the connection is returned to the pool
2098
2099 closeDatabaseConnections(connection,stmt,rs);
2100
2101 }
2102
2103 // Now return the details of the FaultType to the caller
2104
2105 return FaultType;
2106 }
2107
2108 /**
2109 * Return the names of all of the states to the
2110 * caller which are recalled from the fault database table.
2111 * <b>STATES</b>.
2112 * <P>
2113 * @throws UnableToGetListException If there is a problem getting
2114 * the list back from the database
2115 * @return ArrayList<State> A array which contains objects of type
2116 * <b>State</b>.
2117 * @gwt.typeArgs <GWTApplication.client.State>
2118 */
2119
2120 public ArrayList<State> getStates () throws UnableToGetListException {
2121
2122 ArrayList<State> States = new ArrayList<State>();
2123
2124 Connection connection = null;
2125 Statement stmt = null;
2126 ResultSet rs = null;
2127
2128 if (dataSource == null) {
2129 dataSource = (DataSource) getServletContext().getAttribute("DBCPool");
2130 }
2131
2132 // Now get a database connection from the connection pool if
2133 // we can
2134
2135 try {
2136
2137 State newState;
2138
2139 connection = dataSource.getConnection();
2140 stmt = connection.createStatement();
2141
2142 rs = stmt.executeQuery("select * from state where id <> 'RELEASED' order by id");
2143
2144 while (rs.next()) {
2145
2146 newState = new State();
2147
2148 newState.setEn_description(rs.getString("en_description"));
2149 newState.setSp_description(rs.getString("sp_description"));
2150 newState.setId(rs.getString("id"));
2151
2152 States.add(newState);
2153 }
2154
2155 // Ensure that we clean up the database connection
2156 // correctly so that the connection is returned to the
2157 // database connection pool
2158
2159 rs.close();
2160 connection.close();
2161 stmt.close();
2162
2163 rs = null;
2164 connection = null;
2165 stmt = null;
2166
2167 } catch (SQLException e) {
2168 e.printStackTrace();
2169 throw new UnableToGetListException("Unable to get list of : " + e.getMessage());
2170 } finally {
2171
2172 // Always make sure result sets and statements are closed,
2173 // and the connection is returned to the pool
2174
2175 closeDatabaseConnections(connection,stmt,rs);
2176 }
2177
2178 // Now return the details of the States to the caller
2179
2180 return States;
2181 }
2182
2183 /**
2184 * Will be used to massage a text field into the correct format
2185 * for insertion into the database
2186 *
2187 * @param InputString This is the string which is to be converted
2188 */
2189
2190 private String makeTextFieldDatabaseCompatible (final String InputString) {
2191
2192 String processedString;
2193
2194 processedString = InputString.replaceAll("'","`");
2195
2196 return processedString;
2197 }
2198
2199
2200 /**
2201 * Pack the details of a solution result set object into an
2202 * <b>Solution</b> object.
2203 * @param rs This is a object which contains a database row which
2204 * contains the details of a solution.
2205 */
2206
2207 private Solution packSolution (final ResultSet rs) throws SQLException {
2208
2209 Solution solution = new Solution();
2210
2211 // Now get the various dates & times and translate them back
2212 // to something that can be displayed by the web browser.
2213
2214 java.util.Date timeOfInitialEntry;
2215
2216 final SimpleDateFormat timeFormatter = new SimpleDateFormat("HH:mm");
2217 final SimpleDateFormat dateFormatter = new SimpleDateFormat("dd/MM/yyyy");
2218
2219 solution.enteredByName = rs.getString("name");
2220 solution.enteredBySurname = rs.getString("surname");
2221 solution.timeSpent = rs.getString("timespent");
2222 solution.enteredByEmail = rs.getString("email");
2223 solution.id = rs.getInt("id");
2224 solution.description = rs.getString("description");
2225
2226 timeOfInitialEntry = rs.getDate("timeentered");
2227
2228 // Convert the time of entry into a human readable form
2229
2230 if (timeOfInitialEntry != null) {
2231 solution.timeEntered = timeFormatter.format(timeOfInitialEntry);
2232 solution.dateEntered = dateFormatter.format(timeOfInitialEntry);
2233 }
2234
2235 return solution;
2236 }
2237
2238 /**
2239 * This method is called to update the solution which is
2240 * associated with the fault in the database
2241 */
2242
2243 public void updateSolutionDetails (final Solution NewSolutionDetails) {
2244
2245 // First we need to know if the solution already exists in the
2246 // database. If it does not exist then we need to perform an
2247 // insert otherwise we will need to perform an update
2248
2249 Connection connection = null;
2250 Statement stmt = null;
2251 ResultSet rs = null;
2252
2253 // Now get a database connection from the connection pool if
2254 // we can
2255
2256 if (dataSource == null) {
2257 dataSource = (DataSource) getServletContext().getAttribute("DBCPool");
2258 }
2259
2260 try {
2261
2262 boolean performUpdate;
2263
2264 // Establish if there is already a solution in the
2265 // database and if there is then we need to update the
2266 // details of the solution
2267
2268 connection = dataSource.getConnection();
2269 stmt = connection.createStatement();
2270 rs = stmt.executeQuery("select * from solutionview where id = " + NewSolutionDetails.id);
2271
2272 if (rs.next()) {
2273 performUpdate = true;
2274 } else {
2275 performUpdate = false;
2276 }
2277
2278 // Now create the update/insert statement to update the
2279 // details of solution in the database
2280
2281 final SimpleDateFormat dateFormatter = new SimpleDateFormat("yyyy-MM-dd HH:mm");
2282
2283 String sqlStatement;
2284
2285 if (performUpdate) {
2286 sqlStatement = "update solution set enteredby = '" + NewSolutionDetails.enteredByEmail + "',description = '" + makeTextFieldDatabaseCompatible(NewSolutionDetails.description) + "', timeentered = '" + dateFormatter.format(new java.util.Date()) + "',timespent = '" + NewSolutionDetails.timeSpent + "' where id = " + NewSolutionDetails.id;
2287 } else {
2288 sqlStatement = "insert into solution (enteredby,description,id,timespent) values ('" + NewSolutionDetails.enteredByEmail + "','" + makeTextFieldDatabaseCompatible(NewSolutionDetails.description) + "'," + NewSolutionDetails.id + ",'" + NewSolutionDetails.timeSpent + "')";
2289 }
2290
2291 logger.debug("Solution sql statement is " + sqlStatement);
2292
2293 // Now update the database with the details of the solution
2294
2295 stmt.execute(sqlStatement);
2296
2297 // Now update the time that the fault was last modified
2298
2299 stmt.execute("update fault set lastmodified = 'NOW' where id = " + NewSolutionDetails.id);
2300
2301 // Ensure that we clean up the database connection
2302 // correctly so that the connection is returned to the
2303 // database connection pool
2304
2305 rs.close();
2306 connection.close();
2307 stmt.close();
2308
2309 rs = null;
2310 connection = null;
2311 stmt = null;
2312
2313 } catch (SQLException e) {
2314 e.printStackTrace();
2315 } finally {
2316
2317 // Always make sure result sets and statements are closed,
2318 // and the connection is returned to the pool
2319
2320 closeDatabaseConnections(connection,stmt,rs);
2321 }
2322 }
2323
2324 /**
2325 * Get the details of a solution from the database which is
2326 * associated with the fault number which was specified by the
2327 * caller
2328 * <P>
2329 * @param DefectNumber This is the number of the fault which is
2330 * associated with the solution which we are to return to the
2331 * caller
2332 * @return Solution This structure contains the details of the solution if there is one
2333 */
2334
2335 public Solution getSolutionDetails (final Integer DefectNumber) {
2336
2337 Solution solution = null;
2338
2339 // Now get the data back from the database
2340
2341 Connection connection = null;
2342 Statement stmt = null;
2343 ResultSet rs = null;
2344
2345 // Now get a database connection from the connection pool if
2346 // we can
2347
2348 if (dataSource == null) {
2349 dataSource = (DataSource) getServletContext().getAttribute("DBCPool");
2350 }
2351
2352 try {
2353
2354 connection = dataSource.getConnection();
2355 stmt = connection.createStatement();
2356 rs = stmt.executeQuery("select * from solutionview where id = " + DefectNumber.toString());
2357
2358 if (rs.next()) {
2359 solution = packSolution(rs);
2360 }
2361
2362 //log("select * from solution view where id = " + DefectNumber.toString());
2363
2364 // Ensure that we clean up the database connection
2365 // correctly so that the connection is returned to the
2366 // database connection pool
2367
2368 rs.close();
2369 connection.close();
2370 stmt.close();
2371
2372 rs = null;
2373 connection = null;
2374 stmt = null;
2375
2376 } catch (SQLException e) {
2377 e.printStackTrace();
2378 } finally {
2379
2380 // Always make sure result sets and statements are closed,
2381 // and the connection is returned to the pool
2382
2383 closeDatabaseConnections(connection,stmt,rs);
2384 }
2385
2386 return solution;
2387 }
2388
2389 /**
2390 * This method is called to update the workaround which is
2391 * associated with the fault in the database
2392 */
2393
2394 public void updateWorkaroundDetails (final Workaround NewWorkaroundDetails) {
2395
2396 // First we need to know if the workaround already exists in the
2397 // database. If it does not exist then we need to perform an
2398 // insert otherwise we will need to perform an update
2399
2400 Connection connection = null;
2401 Statement stmt = null;
2402 ResultSet rs = null;
2403
2404 // Now get a database connection from the connection pool if
2405 // we can
2406
2407 if (dataSource == null) {
2408 dataSource = (DataSource) getServletContext().getAttribute("DBCPool");
2409 }
2410
2411 try {
2412
2413 boolean performUpdate;
2414
2415 // Establish if there is already a workaround in the
2416 // database and if there is then we need to update the
2417 // details of the workaround
2418
2419 connection = dataSource.getConnection();
2420 stmt = connection.createStatement();
2421 rs = stmt.executeQuery("select * from workaround where id = " + NewWorkaroundDetails.id);
2422
2423 if (rs.next()) {
2424 performUpdate = true;
2425 } else {
2426 performUpdate = false;
2427 }
2428
2429 // Now create the update/insert statement to update the
2430 // details of workaround in the database
2431
2432 final SimpleDateFormat dateFormatter = new SimpleDateFormat("yyyy-MM-dd HH:mm");
2433
2434 String sqlStatement;
2435
2436 if (performUpdate) {
2437 sqlStatement = "update workaround set enteredby = '" + NewWorkaroundDetails.enteredByEmail + "',description = '" + makeTextFieldDatabaseCompatible(NewWorkaroundDetails.description) + "', timeentered = '" + dateFormatter.format(new java.util.Date()) + "',timespent = '" + NewWorkaroundDetails.timeSpent + "' where id = " + NewWorkaroundDetails.id;
2438 } else {
2439 sqlStatement = "insert into workaround (enteredby,description,id,timespent) values ('" + NewWorkaroundDetails.enteredByEmail + "','" + makeTextFieldDatabaseCompatible(NewWorkaroundDetails.description) + "'," + NewWorkaroundDetails.id + ",'" + NewWorkaroundDetails.timeSpent + "')";
2440 }
2441
2442 logger.debug("Workaround sql statement is " + sqlStatement);
2443
2444 // Now update the database with the details of the workaround
2445
2446 stmt.execute(sqlStatement);
2447
2448 stmt.execute("update fault set lastmodified = 'NOW' where id = " + NewWorkaroundDetails.id);
2449
2450 // Ensure that we clean up the database connection
2451 // correctly so that the connection is returned to the
2452 // database connection pool
2453
2454 rs.close();
2455 connection.close();
2456 stmt.close();
2457
2458 rs = null;
2459 connection = null;
2460 stmt = null;
2461
2462 } catch (SQLException e) {
2463 e.printStackTrace();
2464 } finally {
2465
2466 // Always make sure result sets and statements are closed,
2467 // and the connection is returned to the pool
2468
2469 closeDatabaseConnections(connection,stmt,rs);
2470 }
2471 }
2472
2473
2474 /**
2475 * Get the details of a workaround from the database which is
2476 * associated with the fault number which was specified by the
2477 * caller
2478 * <P>
2479 * @param DefectNumber This is the number of the fault which is
2480 * associated with the workaround which we are to return to the
2481 * caller
2482 * @return Workaround This structure contains the details of the workaround if there is one
2483 */
2484
2485 public Workaround getWorkaroundDetails (final Integer DefectNumber) {
2486
2487 Workaround workaround = null;
2488
2489 // Now get the data back from the database
2490
2491 Connection connection = null;
2492 Statement stmt = null;
2493 ResultSet rs = null;
2494
2495 // Now get a database connection from the connection pool if
2496 // we can
2497
2498 if (dataSource == null) {
2499 dataSource = (DataSource) getServletContext().getAttribute("DBCPool");
2500 }
2501
2502 try {
2503
2504 connection = dataSource.getConnection();
2505 stmt = connection.createStatement();
2506 rs = stmt.executeQuery("select * from workaroundview where id = " + DefectNumber.toString());
2507
2508 if (rs.next()) {
2509 workaround = packWorkaround(rs);
2510 }
2511
2512 // Ensure that we clean up the database connection
2513 // correctly so that the connection is returned to the
2514 // database connection pool
2515
2516 rs.close();
2517 connection.close();
2518 stmt.close();
2519
2520 rs = null;
2521 connection = null;
2522 stmt = null;
2523
2524 } catch (SQLException e) {
2525 e.printStackTrace();
2526 } finally {
2527
2528 // Always make sure result sets and statements are closed,
2529 // and the connection is returned to the pool
2530
2531 closeDatabaseConnections(connection,stmt,rs);
2532
2533 }
2534
2535 return workaround;
2536 }
2537
2538 /**
2539 * Pack the details of a workaround result set object into an
2540 * <b>Workaround</b> object.
2541 * @param rs This is a object which contains a database row which
2542 * contains the details of a workaround.
2543 */
2544
2545 private Workaround packWorkaround (final ResultSet rs) throws SQLException {
2546
2547 Workaround workaround = new Workaround();
2548
2549 // Now get the various dates & times and translate them back
2550 // to something that can be displayed by the web browser.
2551
2552 java.util.Date timeOfInitialEntry;
2553
2554 final SimpleDateFormat timeFormatter = new SimpleDateFormat("HH:mm");
2555 final SimpleDateFormat dateFormatter = new SimpleDateFormat("dd/MM/yyyy");
2556
2557 workaround.enteredByName = rs.getString("name");
2558 workaround.enteredBySurname = rs.getString("surname");
2559 workaround.timeSpent = rs.getString("timespent");
2560 workaround.enteredByEmail = rs.getString("enteredby");
2561 workaround.id = rs.getInt("id");
2562 workaround.description = rs.getString("description");
2563
2564 timeOfInitialEntry = rs.getDate("timeentered");
2565
2566 // Convert the time of entry into a human readable form
2567
2568 if (timeOfInitialEntry != null) {
2569 workaround.timeEntered = timeFormatter.format(timeOfInitialEntry);
2570 workaround.dateEntered = dateFormatter.format(timeOfInitialEntry);
2571 }
2572
2573 return workaround;
2574 }
2575
2576 /**
2577 * This method is called to update the comment which is associated
2578 * with the fault in the database
2579 * @param NewCommentDetails The details of the new comment.
2580 */
2581
2582 public void updateCommentDetails (final Comment NewCommentDetails) {
2583
2584 // First we need to know if the comment already exists in the
2585 // database. If it does not exist then we need to perform an
2586 // insert otherwise we will need to perform an update
2587
2588 Connection connection = null;
2589 Statement stmt = null;
2590
2591 // Now get a database connection from the connection pool if
2592 // we cang
2593
2594 if (dataSource == null) {
2595 dataSource = (DataSource) getServletContext().getAttribute("DBCPool");
2596 }
2597
2598 try {
2599
2600 // Establish if there is already a comment in the
2601 // database and if there is then we need to update the
2602 // details of the comment
2603
2604 connection = dataSource.getConnection();
2605 stmt = connection.createStatement();
2606
2607 // Now create the update/insert statement to update the
2608 // details of comment in the database
2609
2610 final SimpleDateFormat dateFormatter = new SimpleDateFormat("yyyy-MM-dd HH:mm");
2611
2612 String sqlStatement;
2613
2614 sqlStatement = "insert into comment (enteredby,description,id,timespent) values ('" + NewCommentDetails.enteredByEmail + "','" + makeTextFieldDatabaseCompatible(NewCommentDetails.description) + "'," + NewCommentDetails.id + ",'" + NewCommentDetails.timeSpent + "')";
2615
2616 logger.debug("Comment sql statement is " + sqlStatement);
2617
2618 // Now update the database with the details of the comment
2619
2620 stmt.execute(sqlStatement);
2621
2622 stmt.execute("update fault set lastmodified = 'NOW' where id = " + NewCommentDetails.id);
2623
2624 // Ensure that we clean up the database connection
2625 // correctly so that the connection is returned to the
2626 // database connection pool
2627
2628 connection.close();
2629 stmt.close();
2630
2631 connection = null;
2632 stmt = null;
2633
2634 } catch (SQLException e) {
2635 e.printStackTrace();
2636 } finally {
2637
2638 // Always make sure result sets and statements are closed,
2639 // and the connection is returned to the pool
2640
2641 closeDatabaseConnections(connection,stmt,null);
2642 }
2643
2644 calculateTotalAmountOfTimeSpentOnFault(NewCommentDetails.id);
2645 }
2646
2647 /**
2648 * Get the details of a comment from the database which is
2649 * associated with the fault number which was specified by the
2650 * caller
2651 * <P>
2652 * @param DefectNumber This is the number of the fault which is
2653 * associated with the comment which we are to return to the
2654 * caller
2655 * @return ArrayList<Comment> This structure contains the details of all of
2656 * the comments which are associated with the fault
2657 * @gwt.typeArgs <GWTApplication.client.Comment>
2658 */
2659
2660 public ArrayList<Comment> getCommentDetails (final Integer DefectNumber) {
2661
2662 ArrayList<Comment> comment = new ArrayList<Comment>();
2663
2664 // Now get the data back from the database
2665
2666 Connection connection = null;
2667 Statement stmt = null;
2668 ResultSet rs = null;
2669
2670 // Now get a database connection from the connection pool if
2671 // we can
2672
2673 if (dataSource == null) {
2674 dataSource = (DataSource) getServletContext().getAttribute("DBCPool");
2675 }
2676
2677 try {
2678
2679 connection = dataSource.getConnection();
2680 stmt = connection.createStatement();
2681 rs = stmt.executeQuery("select * from commentview where id = " + DefectNumber.toString() + " order by timeentered");
2682
2683 while (rs.next()) {
2684 comment.add(packComment(rs));
2685 }
2686
2687 // Ensure that we clean up the database connection
2688 // correctly so that the connection is returned to the
2689 // database connection pool
2690
2691 rs.close();
2692 connection.close();
2693 stmt.close();
2694
2695 rs = null;
2696 connection = null;
2697 stmt = null;
2698
2699 } catch (SQLException e) {
2700 e.printStackTrace();
2701 } finally {
2702
2703 // Always make sure result sets and statements are closed,
2704 // and the connection is returned to the pool
2705
2706 closeDatabaseConnections(connection,stmt,rs);
2707 }
2708
2709 return comment;
2710 }
2711
2712 /**
2713 * Pack the details of a comment result set object into an
2714 * <b>Comment</b> object.
2715 * @param rs This is a object which contains a database row which
2716 * contains the details of a comment.
2717 */
2718
2719 private Comment packComment (final ResultSet rs) throws SQLException {
2720
2721 Comment comment = new Comment();
2722
2723 // Now get the various dates & times and translate them back
2724 // to something that can be displayed by the web browser.
2725
2726 java.util.Date timeOfInitialEntry;
2727
2728 final SimpleDateFormat timeFormatter = new SimpleDateFormat("HH:mm");
2729 final SimpleDateFormat dateFormatter = new SimpleDateFormat("dd/MM/yyyy");
2730
2731 comment.enteredByName = rs.getString("name");
2732 comment.enteredBySurname = rs.getString("surname");
2733 comment.timeSpent = rs.getString("timespent");
2734 comment.enteredByEmail = rs.getString("enteredby");
2735 comment.id = rs.getInt("id");
2736 comment.description = rs.getString("description");
2737
2738 timeOfInitialEntry = rs.getDate("timeentered");
2739
2740 // Convert the time of entry into a human readable form
2741
2742 if (timeOfInitialEntry != null) {
2743 comment.timeEntered = timeFormatter.format(timeOfInitialEntry);
2744 comment.dateEntered = dateFormatter.format(timeOfInitialEntry);
2745 }
2746
2747 return comment;
2748 }
2749
2750 /**
2751 * This operation will be used to authenticate the user. If
2752 * authentication is successful then then level of priviledge will be
2753 * returned to the caller otherwise an exception will be raised.
2754 * @param UserIdentifier This is the user identifier of the user which is essentially his email address
2755 * @param Password This is the password of the user
2756 * @return AuthenticationDetails The level of priviledge and other
2757 * data relating to the user which was logged on
2758 * @throws UserAuthenticationFailedException Unable to authenticate the user.
2759 */
2760
2761 public AuthenticationDetails authenticateUser (String UserIdentifier, String Password) throws UserAuthenticationFailedException {
2762
2763 // This will be used to hold the details
2764
2765 final AuthenticationDetails authenticationDetails = new AuthenticationDetails();
2766
2767 // Now we need to look in the database and find out if the
2768 // user exists in the database and if so what is his level of
2769 // priviledge.
2770
2771 logger.debug("Authenticating user " + UserIdentifier);
2772
2773 Connection connection = null;
2774 Statement stmt = null;
2775 ResultSet rs = null;
2776
2777 // Check to see if the user is attempting to log on without
2778 // credentials and if so give him the basic functionality, we
2779 // use the default password to log onto the system though
2780
2781 if (UserIdentifier.equalsIgnoreCase(FaultDatabaseConstants.GUEST)) {
2782 Password = "blackadder";
2783 UserIdentifier = FaultDatabaseConstants.GUEST;
2784 }
2785
2786 // Now get a database connection from the connection pool if
2787 // we can
2788
2789 if (dataSource == null) {
2790 dataSource = (DataSource) getServletContext().getAttribute("DBCPool");
2791 }
2792
2793 boolean authenticated = false;
2794
2795 try {
2796
2797 connection = dataSource.getConnection();
2798 stmt = connection.createStatement();
2799
2800 rs = stmt.executeQuery("select * from staffmember where username = '" + UserIdentifier + "' and password ='" + Password + "' and stillwithing = true");
2801
2802 // If there is a row in the table for that user and
2803 // password then we pack the details of the authentication
2804 // into the return value
2805
2806 if (rs.next()) {
2807
2808 authenticated = true;
2809
2810 authenticationDetails.UserEmailAddress = rs.getString("email");
2811 authenticationDetails.UserId = rs.getString("username");
2812 authenticationDetails.Name = rs.getString("name");
2813 authenticationDetails.Surname = rs.getString("surname");
2814 }
2815
2816 rs.close();
2817
2818 // Now get the levels of the priviledge which are
2819 // associated with this user from the database
2820
2821 rs = stmt.executeQuery("select * from roles where username = '" + UserIdentifier + "'");
2822
2823 logger.debug("select * from roles where username = '" + UserIdentifier + "'");
2824
2825 while (rs.next()) {
2826 authenticationDetails.PriviledgeLevels.add(rs.getString("rolename"));
2827 logger.debug("user " + UserIdentifier + " has the role " + rs.getString("rolename"));
2828 }
2829
2830 // Ensure that we clean up the database connection
2831 // correctly so that the connection is returned to the
2832 // database connection pool
2833
2834 rs.close();
2835 connection.close();
2836 stmt.close();
2837
2838 rs = null;
2839 connection = null;
2840 stmt = null;
2841
2842 } catch (SQLException e) {
2843 e.printStackTrace();
2844 } finally {
2845
2846 // Always make sure result sets and statements are closed,
2847 // and the connection is returned to the pool
2848
2849 closeDatabaseConnections(connection,stmt,rs);
2850 }
2851
2852 // If we were unable to authenticate the user then throw an exception
2853
2854 if (!authenticated) {
2855 throw new UserAuthenticationFailedException("Unable to authenticate user " + UserIdentifier);
2856 }
2857
2858 logger.debug("User " + UserIdentifier + " has been authenticated");
2859
2860 return authenticationDetails;
2861 }
2862
2863 /**
2864 * Send an email to all of the people that have registered an
2865 * interest in this fault. The details of the fault are recalled
2866 * from the database and then sent out as a HTML or a text based
2867 * message depending on the user preferences.
2868 * @param ContextMessage This abstract text which is associated with the fault
2869 * @param FaultNumber This is the identifier of the fault which
2870 * needs to be sent out to the user.
2871 */
2872
2873
2874 public void informEmailListenersFaultHasChanged (final String ContextMessage, final int FaultNumber) {
2875
2876 Connection connection = null;
2877 Statement stmt = null;
2878 ResultSet rs = null;
2879
2880 // Now get a database connection from the connection pool if
2881 // we can
2882
2883 if (dataSource == null) {
2884 dataSource = (DataSource) getServletContext().getAttribute("DBCPool");
2885 }
2886
2887 // Now get a list of all of the listeners of this fault which
2888 // we are to send the details of the fault to
2889
2890 ArrayList<String> faultListeners = new ArrayList<String>();
2891
2892 try {
2893
2894 connection = dataSource.getConnection();
2895 stmt = connection.createStatement();
2896
2897 rs = stmt.executeQuery("select * from faultlistener where id = " + Integer.toString(FaultNumber));
2898
2899 while (rs.next()) {
2900 faultListeners.add(rs.getString("email"));
2901 }
2902
2903 rs.close();
2904 connection.close();
2905 stmt.close();
2906
2907 rs = null;
2908 connection = null;
2909 stmt = null;
2910
2911 } catch (SQLException e) {
2912 e.printStackTrace();
2913 } finally {
2914
2915 // Always make sure result sets and statements are closed,
2916 // and the connection is returned to the pool
2917
2918 closeDatabaseConnections(connection,stmt,rs);
2919
2920 }
2921
2922 // Get a report of the fault
2923
2924 String faultReport = "Unknown";
2925
2926 try {
2927 faultReport = fetchHTMLFaultReport(FaultNumber,false);
2928 } catch (UnknownFaultSpecifiedException e) {
2929 logger.error("Unable to find fault " + FaultNumber + " in the database " + e.getMessage());
2930 }
2931
2932 // Now for each of listeners, tell them about the change to
2933 // the fault
2934
2935 for (int i=0; i < faultListeners.size(); i++) {
2936
2937 String emailAddress = (String)faultListeners.get(i);
2938
2939 try {
2940
2941 javax.mail.Session session = javax.mail.Session.getInstance(props, null);
2942
2943 Message msg = new MimeMessage(session);
2944
2945 msg.setFrom(new InternetAddress("faultdbadmin@ing.iac.es","Fault Database"));
2946
2947 msg.setRecipients(Message.RecipientType.TO, InternetAddress.parse(emailAddress, false));
2948
2949 msg.setSubject("Fault " + FaultNumber + " has been updated ");
2950
2951 msg.setContent("You have been sent this email by the fault management system because you registered an interest in this fault, please do not reply to this message.<P>" +
2952 "The reason you have been sent this email is because : <b>" + ContextMessage +
2953 "</b><p>Click <b><a href=\"http://www.ing.iac.es:8081/faultdb/?EditFault=" + FaultNumber + "\">here</a></b> if you wish to update the fault.<P> " +
2954 faultReport, "text/html");
2955
2956 msg.setHeader("X-Mailer", "Fault Management System");
2957
2958 msg.setSentDate(new java.util.Date());
2959
2960 // Now send the message to the email address which was
2961 // specified and, alas poor email, off it goes to the
2962 // spam folder.
2963
2964 Transport.send(msg);
2965
2966 } catch (Exception e) {
2967 logger.error("Unable to send fault listener update message to " + emailAddress + " " + e.getMessage());
2968 }
2969 }
2970 }
2971
2972 /**
2973 * Get a list of staff members which are not in the group people
2974 * which have been specified by the caller from the database. this
2975 * will be used for instance to get a list of all of the staff
2976 * members except those in the TO group for instance.
2977 *<P>
2978 * @param GroupToExclude This is the group of people which are to
2979 * be exceluded from the list of staff returned to the
2980 * caller. This parameter should be one of
2981 * <ul>
2982 * <li> FaultDatabaseConstants.TELESCOPEOPERATOR
2983 * <li> FaultDatabaseConstants.DUTYENGINEERS
2984 * <li> FaultDatabaseConstants.SUPPORTASTRONOMERS
2985 * </ul>
2986 * <P>
2987 * @return ArrayList<Person> An array of class Person of the people in the
2988 * staff member list which are not in the group which was
2989 * specified.
2990 * @throws UnableToGetListException In the event that there
2991 * is a problem getting the list of people.
2992 * @gwt.typeArgs <GWTApplication.client.Person>
2993 */
2994
2995 public ArrayList<Person> getMembersOfStaffNotInGroup (final Integer GroupToExclude) throws UnableToGetListException {
2996
2997 ArrayList<Person> PersonList = null;
2998
2999 switch (GroupToExclude.intValue()) {
3000
3001 case FaultDatabaseConstants.TELESCOPEOPERATOR :
3002 PersonList = getPersonDetails("STAFFMEMBER","TELESCOPEOPERATORVIEW",false);
3003 break;
3004
3005 case FaultDatabaseConstants.DUTYENGINEERS :
3006 PersonList = getPersonDetails("STAFFMEMBER","DUTYTECHNICIANVIEW",false);
3007 break;
3008
3009 case FaultDatabaseConstants.SUPPORTASTRONOMERS :
3010 PersonList = getPersonDetails("STAFFMEMBER","SUPPORTASTRONOMERVIEW",false);
3011 break;
3012
3013 default :
3014 logger.error("Group to exclude has been incorrectly specified" + GroupToExclude);
3015 }
3016
3017 return PersonList;
3018 }
3019
3020 /**
3021 * Will be used to add a new instrument to the database.
3022 * @param newInstrument The name of the new instrument
3023 * @throws UnableToGetPeopleListException If there was a problem
3024 * adding the new instrument to the list
3025 */
3026
3027 public void addNewInstrumentToSystem (final String newInstrument) throws UnableToAddInstrumentException {
3028
3029 Connection connection = null;
3030 Statement stmt = null;
3031 ResultSet rs = null;
3032
3033 // Now we need to build an SQL statement which can be used to
3034 // put the data into the fault table in the database
3035
3036 final String insertStatement = "insert into instrument (id,sp_description,en_description) values ('" + newInstrument + "','" + newInstrument + "','" + newInstrument + "')";
3037
3038 logger.debug( "Instrument insertion statement >> " + insertStatement);
3039
3040 // Now get a database connection from the connection pool if
3041 // we can
3042
3043 if (dataSource == null) {
3044 dataSource = (DataSource) getServletContext().getAttribute("DBCPool");
3045 }
3046
3047 try {
3048
3049 connection = dataSource.getConnection();
3050 stmt = connection.createStatement();
3051
3052 stmt.execute(insertStatement);
3053
3054 connection.close();
3055 stmt.close();
3056
3057 connection = null;
3058 stmt = null;
3059
3060 } catch (SQLException e) {
3061 e.printStackTrace();
3062 throw new UnableToAddInstrumentException("Database error : " + e.getMessage());
3063 } finally {
3064
3065 // Always make sure result sets and statements are closed,
3066 // and the connection is returned to the pool
3067
3068 closeDatabaseConnections(connection,stmt,null);
3069 }
3070
3071 return;
3072 }
3073
3074 /**
3075 * Will be used to add a new system to the system.
3076 * @param newSystem The name of the new system
3077 * @throws UnableToGetPeopleListException If there was a problem
3078 * adding the new system to the list
3079 */
3080
3081 public void addNewSystemToSystem (String newSystem) throws UnableToAddSystemException {
3082
3083 Connection connection = null;
3084 Statement stmt = null;
3085
3086 // Now we need to build an SQL statement which can be used to
3087 // put the data into the fault table in the database
3088
3089 final String insertStatement = "insert into system (id,sp_description,en_description) values ('" + newSystem + "','" + newSystem + "','" + newSystem + "')";
3090
3091 logger.debug( "System insertion statement >> " + insertStatement);
3092
3093 // Now get a database connection from the connection pool if
3094 // we can
3095
3096 if (dataSource == null) {
3097 dataSource = (DataSource) getServletContext().getAttribute("DBCPool");
3098 }
3099
3100 try {
3101
3102 connection = dataSource.getConnection();
3103 stmt = connection.createStatement();
3104
3105 stmt.execute(insertStatement);
3106
3107 connection.close();
3108 stmt.close();
3109
3110 connection = null;
3111 stmt = null;
3112
3113 } catch (SQLException e) {
3114 e.printStackTrace();
3115 throw new UnableToAddSystemException("Database error : " + e.getMessage());
3116 } finally {
3117
3118 // Always make sure result sets and statements are closed,
3119 // and the connection is returned to the pool
3120
3121 closeDatabaseConnections(connection,stmt,null);
3122 }
3123 }
3124
3125 /**
3126 * Will be used to add a new user to the role which is
3127 * specified. For example the caller will be able to add a user to
3128 * the list of telescope operators
3129 * @param emailAddress This is the email address of the person who is to be added to the people in the role which is specified
3130 * @param Role This is the role into which the user will be added. Will be one of
3131 * <ul>
3132 * <li> TELESCOPEOPERATOR
3133 * <li> DUTYENGINEER
3134 * <li> SUPPORTASTRONOMER
3135 * </ul>
3136 * @throws UnableToAddNewRoleException If there is a problem adding the user to the role which was specified
3137 */
3138
3139 public void addNewRole (final String emailAddress,final Integer Role) throws UnableToAddNewRoleException {
3140
3141 Connection connection = null;
3142 Statement stmt = null;
3143
3144 String databaseTable;
3145
3146 // Now we need to build an SQL statement which can be used to
3147 // put the data into the fault table in the database
3148
3149 switch (Role.intValue()) {
3150
3151 case FaultDatabaseConstants.TELESCOPEOPERATOR :
3152 databaseTable = "TELESCOPEOPERATOR";
3153 break;
3154
3155 case FaultDatabaseConstants.DUTYENGINEERS :
3156 databaseTable = "DUTYTECHNICIAN";
3157 break;
3158
3159 case FaultDatabaseConstants.SUPPORTASTRONOMERS :
3160 databaseTable = "SUPPORTASTRONOMER";
3161 break;
3162
3163 default :
3164 logger.error("Unknown role has been specified " + Role);
3165 throw new UnableToAddNewRoleException("Unknown role specified " + Role);
3166 }
3167
3168 final String insertStatement = "insert into " + databaseTable + "(email) values ('" + emailAddress + "')";
3169
3170 logger.debug( "System insertion statement >> " + insertStatement);
3171
3172 // Now get a database connection from the connection pool if
3173 // we can
3174
3175 if (dataSource == null) {
3176 dataSource = (DataSource) getServletContext().getAttribute("DBCPool");
3177 }
3178
3179 try {
3180
3181 connection = dataSource.getConnection();
3182 stmt = connection.createStatement();
3183
3184 stmt.execute(insertStatement);
3185
3186 stmt.close();
3187 connection.close();
3188
3189 connection = null;
3190 stmt = null;
3191
3192 } catch (SQLException e) {
3193 e.printStackTrace();
3194 throw new UnableToAddNewRoleException();
3195
3196 } finally {
3197
3198 // Always make sure result sets and statements are closed,
3199 // and the connection is returned to the pool
3200
3201 closeDatabaseConnections(connection,stmt,null);
3202 }
3203 }
3204
3205 /**
3206 * Will add the details of a new user to the system.
3207 * @param Name This is the christian name of the user
3208 * @param Surname This is the surname of the user
3209 * @param EmailAddress This is the email address of the user
3210 * @throws UnableToAddNewUserException If there was a problem
3211 * adding the details of the new user to the system
3212 */
3213
3214 public void addNewUser(final String Name, final String Surname, final String EmailAddress) throws UnableToAddNewUserException {
3215
3216 Connection connection = null;
3217 Statement stmt = null;
3218
3219 // We need to get the user name of the user which should be
3220 // established by stripping the userid from the email address
3221
3222 final String[] emailAddressComponents = EmailAddress.split("@");
3223
3224 // Check that we were able to find a @ in the email address
3225
3226 if (emailAddressComponents.length != 2) {
3227 throw new UnableToAddNewUserException();
3228 }
3229
3230 // Now we need to create the SQL statement which we will use
3231 // to insert the data into the database
3232
3233 String insertStatement = "insert into staffmember (surname,name,stillwithing,email,username) values ('" + makeTextFieldDatabaseCompatible(Surname) + "','" + Name +
3234 "',true,'" + makeTextFieldDatabaseCompatible(EmailAddress) + "','" + makeTextFieldDatabaseCompatible(emailAddressComponents[0]) + "')";
3235
3236 logger.debug(insertStatement);
3237
3238 // Now get a database connection from the connection pool if
3239 // we can
3240
3241 if (dataSource == null) {
3242 dataSource = (DataSource) getServletContext().getAttribute("DBCPool");
3243 }
3244
3245 try {
3246
3247 connection = dataSource.getConnection();
3248 stmt = connection.createStatement();
3249
3250 stmt.execute(insertStatement);
3251
3252 // Now we need to add a new role into the role table for the new user
3253
3254 stmt.execute("insert into roles (rolename,username) values ('ing','" + makeTextFieldDatabaseCompatible(emailAddressComponents[0]) + "')");
3255
3256 stmt.close();
3257 connection.close();
3258
3259 connection = null;
3260 stmt = null;
3261
3262 } catch (SQLException e) {
3263 e.printStackTrace();
3264 throw new UnableToAddNewUserException("Database error : " + e.getMessage());
3265 } finally {
3266
3267 // Always make sure result sets and statements are closed,
3268 // and the connection is returned to the pool
3269
3270 closeDatabaseConnections(connection,stmt,null);
3271
3272 }
3273
3274 return;
3275 }
3276
3277 /**
3278 * Allow the password associated with the user which is provided
3279 * to be changed to the demand password
3280 * @param emailAddress This is the email address of the user to have his password changed
3281 * @param confirmedPassword This is the new password of the user
3282 * @throws UnableToChangePasswordException When unable to update the password of the user
3283 */
3284
3285 public void resetPassword (final String emailAddress, final String confirmedPassword) throws UnableToChangePasswordException {
3286
3287 Connection connection = null;
3288 Statement stmt = null;
3289
3290 String databaseTable;
3291
3292 final String updateStatement = "update staffmember set password = '" + makeTextFieldDatabaseCompatible(confirmedPassword) + "' where email = '" + makeTextFieldDatabaseCompatible(emailAddress) + "'";
3293
3294 logger.debug( "password update statement >> " + updateStatement);
3295
3296 // Now get a database connection from the connection pool if
3297 // we can
3298
3299 if (dataSource == null) {
3300 dataSource = (DataSource) getServletContext().getAttribute("DBCPool");
3301 }
3302
3303 try {
3304
3305 connection = dataSource.getConnection();
3306 stmt = connection.createStatement();
3307
3308 stmt.execute(updateStatement);
3309
3310 stmt.close();
3311 connection.close();
3312
3313 connection = null;
3314 stmt = null;
3315
3316 } catch (SQLException e) {
3317 e.printStackTrace();
3318 throw new UnableToChangePasswordException("Database error : " + e.getMessage());
3319
3320 } finally {
3321
3322 // Always make sure result sets and statements are closed,
3323 // and the connection is returned to the pool
3324
3325 closeDatabaseConnections(connection,stmt,null);
3326 }
3327
3328 }
3329
3330 /**
3331 * Will be used to execute a basic search across the database
3332 * using the input data which has been provided. The user will
3333 * simply specify a string of search terms possibly using
3334 * wildcards and then this routine will attempt to match any
3335 * faults which may contain any of the search terms. The method
3336 * will search through <b>all</b> of the text fields as well as
3337 * the description and the comment fields in the fault.
3338 * <p>
3339 * @param DefectNumber This is the defect number for which we
3340 * should return the fault information.
3341 * @param SearchTerms A list of search terms which will be used to
3342 * index the fault information.
3343 * @param DefectToSearchFor This is the defect number to search for if specified.
3344 * @param StartDate This is the start date from when to look
3345 * from. This could be left blank.
3346 * @param EndDate This is the end date from when to stop looking
3347 * for faults.
3348 * @gwt.typeArgs <GWTApplication.client.FaultType>
3349 */
3350
3351 public ArrayList<Fault> performBasicSearch (final int DefectNumber,
3352 final String SearchTerms,
3353 final String DefectToSearchFor,
3354 String StartDate,
3355 String EndDate,
3356 final String locale) throws UnableToPerformSearchException {
3357
3358 String searchTerm = SearchTerms.trim();
3359
3360 // Convert all of the * characters in the search term to %
3361 // symbols to be SQL compatible
3362
3363 searchTerm = searchTerm.replace('*','%');
3364 searchTerm = searchTerm.replace("_","\\_");
3365 searchTerm = searchTerm.replace("'","\\'");
3366
3367 // Now break up the search string based on spaces and spaces
3368 // between quoted text.
3369
3370 final String[] searchItems = searchTerm.split("[ ]|\"[ ]?\"",0);
3371
3372 // Now create a SQL statement which can be used to go through
3373 // the faults
3374
3375 String view;
3376
3377 // Now depending on the locale we search different views
3378
3379 if (locale.equalsIgnoreCase("en")) {
3380 view = "faultview_en";
3381 } else {
3382 view = "faultview_sp";
3383 }
3384
3385 // For each text field in the view we now create a search
3386 // across the fields.
3387
3388 StringBuilder sqlClause = new StringBuilder("select * from " + view + " where ");
3389
3390 // If the user has specified the defect number then we simply
3391 // look for that defect number and ignore the rest of the
3392 // search terms.
3393
3394 boolean firstClauseInWhereStatement = true;
3395
3396 if (!DefectToSearchFor.equals("")) {
3397 sqlClause.append(" id = " + DefectToSearchFor);
3398
3399 } else {
3400
3401 if (!searchTerm.equals("")) {
3402
3403 for (TextFieldsToSearch i : TextFieldsToSearch.values()) {
3404
3405 String textFieldBooleanOperator;
3406
3407 boolean firstSubClause = true;
3408
3409 // Whether to bracket the expression. If we start
3410 // using the AND operator then we have to bracket the
3411 // search otherwise the semantics are incorrect
3412 // i.e. a + b + c . e . f should be a + b + (c . e . f)
3413
3414 boolean bracketSubExpression = false;
3415
3416 // If the fields that we are searching is a pure text
3417 // field such as the description or title field then
3418 // we should perform an AND on these terms
3419
3420 if (i.equals(TextFieldsToSearch.CONJUGATEDTEXTFIELD)) {
3421 textFieldBooleanOperator = " and ";
3422 bracketSubExpression = true;
3423 } else {
3424 textFieldBooleanOperator = " or ";
3425 bracketSubExpression = false;
3426 }
3427
3428 for (int j=0; j < searchItems.length; j++) {
3429
3430 if (!firstClauseInWhereStatement) {
3431
3432 // In the description and title field, if we
3433 // have more than one search term, we perform
3434 // an AND. e.g if the user specifies "petals
3435 // closing", then the search engine will match
3436 // descriptions which have the words petals
3437 // and closing in them.
3438
3439 if (firstSubClause) {
3440 sqlClause.append(" or ");
3441 } else {
3442 sqlClause.append(textFieldBooleanOperator);
3443 }
3444
3445 } else {
3446 sqlClause.append("(");
3447 }
3448
3449 // If we are using AND with the text terms in the
3450 // description and the title fields we should
3451 // bracket the entire subexpression.
3452
3453 if (firstSubClause && bracketSubExpression) {
3454 sqlClause.append("(");
3455 firstSubClause = false;
3456 }
3457
3458 sqlClause.append("lower(" + i.toString() + ") LIKE '%" + searchItems[j].toLowerCase() + "%'");
3459
3460 // Establish if the user has entered an integer value for the
3461 // search term and if he has then we have to try and match
3462 // that value against a fault identifier
3463
3464 try {
3465 Integer.valueOf(searchTerm);
3466
3467 // We managed to parse the integer so now add an SQL term
3468
3469 sqlClause.append(" or id = " + searchTerm);
3470
3471 } catch (NumberFormatException e) {
3472 // Do nothing if the search term is not an integer.
3473 }
3474
3475 firstClauseInWhereStatement = false;
3476
3477 }
3478
3479 if (!firstSubClause && bracketSubExpression) {
3480 sqlClause.append(")");
3481 }
3482 }
3483
3484 if (!firstClauseInWhereStatement) {
3485 sqlClause.append(")");
3486 }
3487 }
3488
3489 // If the start date is before the end date then swap them
3490 // around
3491
3492 if (!StartDate.equals("") && !EndDate.equals("")) {
3493
3494 // If the user has specified both dates we need to ensure
3495 // that the start date is before the end date and if not
3496 // swap them around.
3497
3498 final String [] endDateInFields = EndDate.split("/");
3499
3500 final String [] startDateInFields = StartDate.split("/");
3501
3502 final GregorianCalendar startDate,endDate;
3503
3504 try {
3505 startDate = new GregorianCalendar(Integer.parseInt(startDateInFields[2]),Integer.parseInt(startDateInFields[1]),Integer.parseInt(startDateInFields[0]));
3506 endDate = new GregorianCalendar(Integer.parseInt(endDateInFields[2]),Integer.parseInt(endDateInFields[1]),Integer.parseInt(endDateInFields[0]));
3507 } catch(NumberFormatException e) {
3508 throw new UnableToPerformSearchException("Problem formatting date fields");
3509 }
3510
3511 // If the start date is after the end date then swap them around
3512
3513 if (startDate.compareTo(endDate) == 1) {
3514
3515 final String temp = StartDate;
3516
3517 StartDate = EndDate;
3518 EndDate = temp;
3519 }
3520 }
3521
3522 // If the user has specified a time window then we should add
3523 // that into the SQL as well. Note that we need to add one
3524 // day to the end date to ensure that we do an inclusive
3525 // search on the end date
3526
3527 if (!StartDate.equals("")) {
3528
3529 if (!firstClauseInWhereStatement) {
3530 sqlClause.append(" and ");
3531 }
3532
3533 firstClauseInWhereStatement = false;
3534
3535 sqlClause.append(" timeentered >= '" + StartDate + "' ");
3536 }
3537
3538 if (!EndDate.equals("")) {
3539
3540 if (!firstClauseInWhereStatement) {
3541 sqlClause.append(" and ");
3542 }
3543
3544 firstClauseInWhereStatement = false;
3545
3546 sqlClause.append(" timeentered <= cast(cast ('" + EndDate + "' as date) + cast ('1 day' as interval) as date) ");
3547 }
3548 }
3549
3550 sqlClause.append(" order by id desc limit " + MAXNUMBEROFRESULTS);
3551
3552 logger.debug("Search string is : " + sqlClause);
3553
3554 // Now get the fault data back from the database
3555
3556 Connection connection = null;
3557 Statement stmt = null;
3558 ResultSet rs = null;
3559
3560 ArrayList<Fault> matchingFaults = new ArrayList<Fault>();
3561
3562 // Now get a database connection from the connection pool if
3563 // we can
3564
3565 if (dataSource == null) {
3566 dataSource = (DataSource) getServletContext().getAttribute("DBCPool");
3567 }
3568
3569 try {
3570
3571 connection = dataSource.getConnection();
3572 stmt = connection.createStatement();
3573 rs = stmt.executeQuery(sqlClause.toString());
3574
3575 while (rs.next()) {
3576
3577 Fault faultData = packFault(rs,true);
3578 matchingFaults.add(faultData);
3579 }
3580
3581 // Ensure that we clean up the database connection
3582 // correctly so that the connection is returned to the
3583 // database connection pool
3584
3585 rs.close();
3586 connection.close();
3587 stmt.close();
3588
3589 rs = null;
3590 connection = null;
3591 stmt = null;
3592
3593 } catch (SQLException e) {
3594 e.printStackTrace();
3595 throw new UnableToPerformSearchException("Database error : " + e.getMessage());
3596 } finally {
3597
3598 // Always make sure result sets and statements are closed,
3599 // and the connection is returned to the pool
3600
3601 closeDatabaseConnections(connection,stmt,rs);
3602 }
3603
3604 logger.debug("Simple Search completed");
3605
3606 return matchingFaults;
3607 }
3608
3609 /**
3610 * Will be used to link a fault which is specified by the user to another fault
3611 * @param parentFaultId This is the parent fault which the child fault will be linked to
3612 * @param childFaultId This the fault which is to be linked to the parent fault
3613 * @throws UnknownFaultSpecifiedException This exception is thrown if the fault which is specified by the user does not exist.
3614 */
3615
3616 public void linkFault(final int parentFaultId, final int childFaultId) throws UnknownFaultSpecifiedException,Exception {
3617
3618 logger.debug("Attempting to link " + childFaultId + " to " + parentFaultId);
3619
3620 boolean unknownChildFaultIdSpecified = false;
3621
3622 // Now find out if the fault which was specified by the user
3623 // actually exists in the database and otherwise we create an
3624 // error
3625
3626 Connection connection = null;
3627 Statement stmt = null;
3628 ResultSet rs = null;
3629
3630 if (dataSource == null) {
3631 dataSource = (DataSource) getServletContext().getAttribute("DBCPool");
3632 }
3633
3634 // Now get a database connection from the connection pool if
3635 // we can
3636
3637 try {
3638
3639 connection = dataSource.getConnection();
3640 stmt = connection.createStatement();
3641
3642 rs = stmt.executeQuery("select count(*) from fault where id = " + childFaultId);
3643
3644 if (rs.next()) {
3645
3646 // There should be at least one fault which matches
3647 // the fault id the user entered
3648
3649 if (rs.getInt(1) != 1) {
3650 unknownChildFaultIdSpecified = true;
3651 }
3652 }
3653
3654 // Now we need to insert a new entry into the fault link
3655 // table now.
3656
3657 stmt.execute("insert into linkfault (childfault,parentfault) values (" + childFaultId + "," + parentFaultId + ")");
3658
3659 // Now update the last modified time for the faults.
3660
3661 stmt.execute("update fault set lastmodified = 'NOW' where id = " + childFaultId + " or id = " + parentFaultId);
3662
3663 stmt.close();
3664
3665 // Ensure that we clean up the database connection
3666 // correctly so that the connection is returned to the
3667 // database connection pool
3668
3669 rs.close();
3670 connection.close();
3671 stmt.close();
3672
3673 rs = null;
3674 connection = null;
3675 stmt = null;
3676
3677 } catch (SQLException e) {
3678 e.printStackTrace();
3679 throw new Exception("Unable to save fault link : " + e.getMessage());
3680 } finally {
3681
3682 // Always make sure result sets and statements are closed,
3683 // and the connection is returned to the pool
3684
3685 closeDatabaseConnections(connection,stmt,rs);
3686 }
3687
3688 // If the user specified an unknown child fault id then throw an error
3689
3690 if (unknownChildFaultIdSpecified) {
3691 throw new UnknownFaultSpecifiedException();
3692 }
3693
3694 return;
3695 }
3696
3697
3698 /**
3699 * Gets all of the faults which are linked to the fault specified
3700 * <P>
3701 * @param DefectNumber This is the number of the fault which we are to return to the caller
3702 * @return ArrayList<Fault> Array of all of the faults which are linked
3703 */
3704
3705 public ArrayList<Fault> getLinkedFaults (final int DefectNumber) {
3706
3707 //logger.debug("Getting linked faults to fault " + DefectNumber + " from the database");
3708
3709 String view = null;
3710
3711 ArrayList<Fault> linkedFaults = new ArrayList<Fault>();
3712
3713 // Now get the data back from the database
3714
3715 Connection connection = null;
3716 Statement stmt = null;
3717 ResultSet rs = null;
3718
3719 // Now get a database connection from the connection pool if
3720 // we can
3721
3722 if (dataSource == null) {
3723 dataSource = (DataSource) getServletContext().getAttribute("DBCPool");
3724 }
3725
3726 try {
3727
3728 connection = dataSource.getConnection();
3729 stmt = connection.createStatement();
3730
3731 final String sqlStatement = "select distinct * from fault where id <> " + DefectNumber + " and id in (select childfault from linkfault where parentfault = " + DefectNumber + " union select parentfault from linkfault where childfault = " + DefectNumber + ")";
3732
3733 rs = stmt.executeQuery(sqlStatement);
3734
3735 while (rs.next()) {
3736 linkedFaults.add(packLink(rs));
3737 }
3738
3739 // Ensure that we clean up the database connection
3740 // correctly so that the connection is returned to the
3741 // database connection pool
3742
3743 rs.close();
3744 connection.close();
3745 stmt.close();
3746
3747 rs = null;
3748 connection = null;
3749 stmt = null;
3750
3751 } catch (SQLException e) {
3752 e.printStackTrace();
3753 } finally {
3754
3755 // Always make sure result sets and statements are closed,
3756 // and the connection is returned to the pool
3757
3758 closeDatabaseConnections(connection,stmt,rs);
3759 }
3760
3761 return linkedFaults;
3762 }
3763 /**
3764 * Get the details of the fault from the database which was specified by the caller
3765 * <P>
3766 * @param DefectNumber This is the number of the fault which we are to return to the caller
3767 * @return Fault This structure contains the details of the fault
3768 */
3769
3770 public Fault getFaultDetails (final Integer DefectNumber,final String locale) {
3771
3772 String view = null;
3773
3774 Fault faultData = null;
3775
3776 // Depending on whether we are in spanish or english we search
3777 // across different database views.
3778
3779 if (locale.equalsIgnoreCase("en")) {
3780 view = "faultview_en";
3781 } else {
3782 view = "faultview_sp";
3783 }
3784
3785 // Now get the data back from the database
3786
3787 Connection connection = null;
3788 Statement stmt = null;
3789 ResultSet rs = null;
3790
3791 // Now get a database connection from the connection pool if
3792 // we can
3793
3794 if (dataSource == null) {
3795 dataSource = (DataSource) getServletContext().getAttribute("DBCPool");
3796 }
3797
3798 try {
3799
3800 connection = dataSource.getConnection();
3801 stmt = connection.createStatement();
3802 rs = stmt.executeQuery("select * from " + view + " where id = " + DefectNumber.toString());
3803
3804 if (rs.next()) {
3805 faultData = packFault(rs,true);
3806 }
3807
3808 //logger.debug(">>select * from " + view + " where id = " + DefectNumber.toString());
3809
3810 // Ensure that we clean up the database connection
3811 // correctly so that the connection is returned to the
3812 // database connection pool
3813
3814 rs.close();
3815 connection.close();
3816 stmt.close();
3817
3818 rs = null;
3819 connection = null;
3820 stmt = null;
3821
3822 } catch (SQLException e) {
3823 e.printStackTrace();
3824 } finally {
3825
3826 // Always make sure result sets and statements are closed,
3827 // and the connection is returned to the pool
3828
3829 closeDatabaseConnections(connection,stmt,rs);
3830 }
3831
3832 return faultData;
3833 }
3834
3835 /**
3836 * Get the total amount of time which has been spent on a
3837 * fault. This will sum the total amount of time which has been
3838 * allocated to comments, solutions and work arounds if they
3839 * exist
3840 * @param FaultNumber This is the fault number for which we
3841 * have to calculate the total time spent.
3842 * @return String The total amount of time spent in hours and minutes in the format HH:MM
3843 */
3844
3845 public String calculateTotalAmountOfTimeSpentOnFault (final int FaultNumber) {
3846
3847 int totalTimeSpentMins = 0;
3848
3849 Connection connection = null;
3850 Statement stmt = null;
3851 ResultSet rs = null;
3852
3853 // Now get a database connection from the connection pool if
3854 // we can
3855
3856 if (dataSource == null) {
3857 dataSource = (DataSource) getServletContext().getAttribute("DBCPool");
3858 }
3859
3860 try {
3861
3862 connection = dataSource.getConnection();
3863
3864 stmt = connection.createStatement();
3865 rs = stmt.executeQuery("select coalesce(to_char(sum(timespent), 'HH24:MI'),'00:00') from comment where id = " + FaultNumber);
3866 rs.next();
3867 totalTimeSpentMins += convertTimeToMinutes(rs.getString(1));
3868
3869 rs.close();
3870 stmt.close();
3871
3872 // Now get the amount of time which has been spent on the solution
3873
3874 stmt = connection.createStatement();
3875 rs = stmt.executeQuery("select coalesce(to_char(sum(timespent), 'HH24:MI'),'00:00') from solution where id = " + FaultNumber);
3876 rs.next();
3877 totalTimeSpentMins += convertTimeToMinutes(rs.getString(1));
3878
3879 rs.close();
3880 stmt.close();
3881
3882 // Now get the amount of time which has been spent on the work around
3883
3884 stmt = connection.createStatement();
3885 rs = stmt.executeQuery("select coalesce(to_char(sum(timespent), 'HH24:MI'),'00:00') from workaround where id = " + FaultNumber);
3886 rs.next();
3887 totalTimeSpentMins += convertTimeToMinutes(rs.getString(1));
3888
3889 rs.close();
3890 stmt.close();
3891
3892 // Ensure that we clean up the database connection
3893 // correctly so that the connection is returned to the
3894 // database connection pool
3895
3896 rs.close();
3897 connection.close();
3898 stmt.close();
3899
3900 rs = null;
3901 connection = null;
3902 stmt = null;
3903
3904 } catch (SQLException e) {
3905 e.printStackTrace();
3906 } finally {
3907
3908 // Always make sure result sets and statements are closed,
3909 // and the connection is returned to the pool
3910
3911 closeDatabaseConnections(connection,stmt,rs);
3912
3913 }
3914
3915 // Now convert the number of minutes to HH:MM
3916
3917 return String.format("%1$02d:%2$02d",totalTimeSpentMins/60, totalTimeSpentMins % 60);
3918 }
3919
3920 /**
3921 * Fetch from the database the details of the fault and all of
3922 * it's comments and prepare an HTML report which can be returned
3923 * to the user
3924 * @param faultId This is the identifier of the fault which is to be printed
3925 * @param addLinks Add the links which will allow user to log into the system and modify the fault
3926 * @return String A string which contains a HTML based fault report
3927 * @throws UnknownFaultSpecifiedException Unknown fault exception.
3928 */
3929
3930 public String fetchHTMLFaultReport (final int faultId, final boolean addLinks) throws UnknownFaultSpecifiedException {
3931
3932 // Get the details of the fault from the database
3933
3934 final Fault faultDetails = getFaultDetails (faultId,"en");
3935
3936 if (faultDetails == null) {
3937 throw new UnknownFaultSpecifiedException("Fault " + faultId + " not found in the database");
3938 }
3939
3940 final Solution solution = getSolutionDetails(faultId);
3941
3942 final Workaround workAround = getWorkaroundDetails(faultId);
3943
3944 // Now get all of the comments associated with the fault
3945
3946 final ArrayList<Comment> faultComments = getCommentDetails(faultId);
3947
3948 final StringBuffer HTMLReport = new StringBuffer();
3949
3950 HTMLReport.append
3951 ("<table cellpadding=3 cellspacing=1 width=\"95%\" border=\"Y\">" +
3952 "<tr><th bgcolor=\"#68A7C0\" colspan=6><a name=\"" + faultId + "\"> Fault " + faultId + "</a></th>");
3953
3954 // Now we need to add the details of the links if specified
3955 // which will be used to allow the user to log onto the system
3956 // and modify the fault should it be required.
3957
3958 HTMLReport.append("<tr><td bgcolor=\"#FFFFC9\" colspan=6><b>" + faultDetails.title + "</b>");
3959
3960 if (addLinks) {
3961
3962 HTMLReport.append("<tr><th bgcolor=\"#E6EEFF\" align=left colspan=6><b>[<a href=\"http://www.ing.iac.es:8081/faultdb?EditFault=" + faultId + "\">Update</a></b>|");
3963 HTMLReport.append("<b><a href=\"#top\">Top</a></b>");
3964
3965 // If the person assigned is known then put in a link
3966 // which will allow the user to email them
3967
3968 if (!faultDetails.assignedToEmail.equalsIgnoreCase("UNKNOWN") && !faultDetails.assignedToEmail.equalsIgnoreCase(FaultDatabaseConstants.GUEST)) {
3969 HTMLReport.append("|<b><a href=\"mailto:" + faultDetails.assignedToEmail + "?Subject=Fault Report " + faultId + "\">Contact Assignee</a></b>");
3970 }
3971
3972 HTMLReport.append("]");
3973 }
3974
3975 HTMLReport.append(
3976 "<tr><td><b>Time Occurred</b><td>" + faultDetails.timeOccured + "<td><b> Date Occurred</b><td>" + faultDetails.dateOccured + "<td><b>Entered By</b><td>" + faultDetails.enteredByName + " " + faultDetails.enteredBySurname +
3977 "<tr><td><b>Assigned to</b> <td>" + faultDetails.assignedToName + " " + faultDetails.assignedToSurname + "<td><b> Severity</b><td>" + faultDetails.severity + "<td><b>Site</b> <td>" + faultDetails.site +
3978 "<tr><td><b>Instrument </b><td>" + faultDetails.instrument + "<td><b> State</b><td>" + faultDetails.state + "<td><b>Type </b> <td>" + faultDetails.faultType +
3979 "<tr><th bgcolor=\"#E6EEFF\" colspan=6>Fault Description" +
3980 "<tr><td colspan=6>" + faultDetails.description);
3981
3982 // Now for each of the comment which have been appended to the
3983 // fault add them into the report
3984
3985 for (int i=0; i < faultComments.size();i++) {
3986
3987 final Comment comment = faultComments.get(i);
3988
3989 HTMLReport.append
3990 ("<tr><th bgcolor=\"#E6EEFF\" colspan=6>Comment" +
3991 "<tr><td><b>Time entered</b><td>" + comment.timeEntered + "<td><b>Date Entered</b><td>" + comment.dateEntered + "<td><b>Entered By</b><td>" + comment.enteredByName + " " + comment.enteredBySurname +
3992 "<tr><td colspan=6>" + comment.description);
3993
3994 }
3995
3996 // If there is a solution then we add in the details of the solution
3997
3998 if (workAround != null) {
3999 HTMLReport.append
4000 ("<tr><th bgcolor=\"#E6EEFF\" colspan=6>Workaround" +
4001 "<tr><td><b>Time entered</b><td>" + workAround.timeEntered + "<td><b>Date Entered</b><td>" + workAround.dateEntered + "<td><b>Entered By</b><td>" + workAround.enteredByName + " " + workAround.enteredBySurname +
4002 "<tr><td colspan=6>" + workAround.description);
4003
4004 }
4005
4006 // If there is a solution then we add in the details of the solution
4007
4008 if (solution != null) {
4009 HTMLReport.append
4010 ("<tr><th bgcolor=\"#E6EEFF\" colspan=6>Solution" +
4011 "<tr><td><b>Time entered</b><td>" + solution.timeEntered + "<td><b>Date Entered</b><td>" + solution.dateEntered + "<td><b>Entered By</b><td>" + solution.enteredByName + " " + solution.enteredBySurname +
4012 "<tr><td colspan=6>" + solution.description);
4013
4014 }
4015
4016 HTMLReport.append("</table>");
4017
4018 return HTMLReport.toString();
4019
4020 }
4021
4022 /**
4023 * Used to convert the HH:MM field into minutes expressed as
4024 * an integer.
4025 * @param TimeAmount Time expressed in Hours:Minutes
4026 * @return int The amount expressed in minutes.
4027 */
4028
4029 private int convertTimeToMinutes(final String TimeAmount) {
4030
4031 final String[] timeFields = TimeAmount.split(":");
4032
4033 return (Integer.parseInt(timeFields[0]) * 60) + Integer.parseInt(timeFields[1]);
4034 }
4035
4036
4037 /**
4038 * Used to record in the database that the user would like to be
4039 * informed of updates on the fault
4040 * @param emailAddress this is the email address of the person that wants to recieve updates
4041 * @param faultId This is the fault identifier of the fault that the wants to get updates for
4042 */
4043
4044 public void registerForFaultUpdates (final String emailAddress,final int faultId) {
4045
4046 Connection connection = null;
4047 Statement stmt = null;
4048
4049 // Now get a database connection from the connection pool if
4050 // we can
4051
4052 if (dataSource == null) {
4053 dataSource = (DataSource) getServletContext().getAttribute("DBCPool");
4054 }
4055
4056 try {
4057
4058 connection = dataSource.getConnection();
4059
4060 stmt = connection.createStatement();
4061 stmt.execute("insert into faultlistener(email,id) values ('" + emailAddress + "'," + faultId + ")");
4062 stmt.close();
4063
4064 connection.close();
4065 stmt.close();
4066
4067 connection = null;
4068 stmt = null;
4069
4070 } catch (SQLException e) {
4071 e.printStackTrace();
4072 } finally {
4073
4074 // Always make sure result sets and statements are closed,
4075 // and the connection is returned to the pool
4076
4077 closeDatabaseConnections(connection,stmt,null);
4078 }
4079 }
4080
4081 /**
4082 * Will be used to print the fault specified on the printer which
4083 * has been specified. The method uses an external agent
4084 * <b>pdf2ps</b> to convert the PDF which is created into
4085 * postscript before it is printed. It is therefore important to
4086 * ensure that this process exists and is configured on the
4087 * machine which is hosting the fault database.
4088 * <P>
4089 * @param faultId This is the identifier of the fault to be printed
4090 * @param printer This is the name of the printer which is to be used to print fault
4091 * @param landscape True if the document is to be printed in landscape mode
4092 * @throws UnknownPrinterSpecifiedException If the printer that
4093 * was specified by the user does not exist
4094 * @return boolean True if we managed to print out the fault
4095 */
4096
4097 public boolean printFault (final int faultId,final String printer,boolean landscape) throws UnknownPrinterSpecifiedException {
4098
4099 // These the temporary files which will be used by the system
4100
4101 final String PSTEMPFILE = "/tmp/faultDBtempFile.ps";
4102 final String PDFTEMPFILE = "/tmp/PDFTEMPFILE.pdf";
4103
4104 final Document PDFDocument;
4105
4106 // Create the PDF document with the margins included. Check to
4107 // see if the user has requested the document in landscape or
4108 // portrait
4109
4110 if (landscape) {
4111 PDFDocument = new Document(PageSize.A4.rotate(),5.0f,5.0f,90,40);
4112 } else {
4113 PDFDocument = new Document(PageSize.A4,5.0f,5.0f,90,40);
4114 }
4115
4116 // Verify that the printer that the user has specified
4117 // actually exists and if not then throw an error
4118
4119 final ArrayList<String> printerList = getPostscriptPrinters();
4120
4121 boolean printerFound = false;
4122
4123 for (int i=0; i < printerList.size();i++) {
4124
4125 final String currentPrinter = printerList.get(i);
4126
4127 if (currentPrinter.equals(printer)) {
4128 printerFound = true;
4129 break;
4130 }
4131 }
4132
4133 // If we could not find the printer that was specifed by the
4134 // user then we throw an error
4135
4136 if (!printerFound) {
4137
4138 logger.error("User has specified an unknown printer for this machine " + printer);
4139
4140 throw new UnknownPrinterSpecifiedException("User has specified an unknown printer " + printer);
4141 }
4142
4143 try {
4144
4145 PdfWriter.getInstance (PDFDocument, new FileOutputStream (PDFTEMPFILE));
4146
4147 PDFDocument.open();
4148
4149 // Now add the fault to the PDF document which we are
4150 // creating
4151
4152 createFaultReportAsPDF(faultId,PDFDocument);
4153
4154 } catch (UnknownFaultSpecifiedException e) {
4155 logger.error("Unable to find fault " + faultId + " in the database");
4156 return false;
4157 } catch (Exception e) {
4158 logger.error(e.getMessage());
4159 return false;
4160 } finally {
4161 if (PDFDocument != null) {
4162 PDFDocument.close();
4163 }
4164 }
4165
4166 // Now convert the PDF file to postscript and then print it out ta-da!
4167
4168 final Runtime pdf2psConvertor = Runtime.getRuntime();
4169
4170 try {
4171
4172 final String[] cmd = {
4173 "/usr/bin/pdf2ps",
4174 PDFTEMPFILE,
4175 PSTEMPFILE
4176 };
4177
4178 final Process proc = pdf2psConvertor.exec(cmd);
4179
4180 // Wait for the process to complete
4181 proc.waitFor();
4182
4183 if (proc.exitValue() != 0) {
4184 logger.error("Unable to convert the PDF to postscript");
4185 return false;
4186 }
4187
4188 } catch (Exception e) {
4189 logger.error("Unable to convert the PDF to postscript : " + e.getMessage());
4190 return false;
4191 }
4192
4193 try {
4194
4195 // Find the all of the print services which can print
4196 // postscript and then try and find the one that the user
4197 // specified.
4198
4199 PrintService printServiceToUse = null;
4200
4201 for (PrintService i : PrintServiceLookup.lookupPrintServices(DocFlavor.INPUT_STREAM.POSTSCRIPT,null)) {
4202
4203 if (i.getName().equals(printer)) {
4204 logger.info("Printing " + faultId + " on printer " + i.getName());
4205 printServiceToUse = i;
4206 break;
4207 }
4208 }
4209
4210 // If we could not find the name of the printer that the
4211 // user has specified then complain
4212
4213 if (printServiceToUse == null) {
4214 throw new UnknownPrinterSpecifiedException("User has specified an unknown printer " + printer);
4215 }
4216
4217 // Create the print job
4218
4219 final File psFile = new File(PSTEMPFILE);
4220 final DocPrintJob job = printServiceToUse.createPrintJob();
4221
4222 final Doc doc = new SimpleDoc(psFile.toURL(),DocFlavor.URL.AUTOSENSE,null);
4223
4224 // Now print out the document on the printer which was
4225 // specified by the caller
4226
4227 job.print(doc,null);
4228
4229 } catch (Exception e) {
4230 e.printStackTrace();
4231 return false;
4232 }
4233
4234 return true;
4235 }
4236
4237 /**
4238 * Add the full details of the fault history into the PDF document
4239 * ready for printing out.
4240 * @param faultId This is the identifier of the fault
4241 * @param PDFDocument This is the PDF document to which the fault details are to be added
4242 * @throws UnknownFaultSpecifiedException If the fault specified cannot be found.
4243 */
4244
4245 void createFaultReportAsPDF (final int faultId,final Document PDFDocument) throws UnknownFaultSpecifiedException {
4246
4247 PdfPCell cell;
4248
4249 // This is the font which will be used for the headers
4250
4251 final Font headerFont = new Font(Font.HELVETICA, 10,Font.BOLD);
4252
4253 // This is the fault which will be used for the body of the
4254 // text
4255
4256 final Font bodyFont = new Font(Font.HELVETICA,8);
4257
4258 // Create a 4 column table into which we will drop the
4259 // details of the fault
4260
4261 PdfPTable table = new PdfPTable (new float [] {
4262 1.0f,
4263 1.0f,
4264 1.0f,
4265 1.0f,
4266 });
4267
4268 // Get the details of the fault from the database
4269
4270 final Fault faultDetails = getFaultDetails (faultId,"en");
4271
4272 if (faultDetails == null) {
4273 throw new UnknownFaultSpecifiedException("The fault " + faultId + " does not exist in the database");
4274 }
4275
4276 final Solution solution = getSolutionDetails(faultId);
4277
4278 final Workaround workAround = getWorkaroundDetails(faultId);
4279
4280 // Now get all of the comments associated with the fault
4281
4282 final ArrayList<Comment> faultComments = getCommentDetails(faultId);
4283
4284 // Now we need to prepare the PDF document for display
4285
4286 table.setWidthPercentage(90.0f);
4287
4288 // Now the title of the report
4289
4290 cell = new PdfPCell(new Paragraph("Defect Report " + faultId,headerFont));
4291
4292 cell.setColspan(4);
4293 cell.setHorizontalAlignment (Element.ALIGN_CENTER);
4294 cell.setPadding (10.0f);
4295 table.addCell (cell);
4296
4297 // Now add the text of the title
4298
4299 cell = new PdfPCell(new Paragraph(removeHTMLMarkUp(faultDetails.title),headerFont));
4300 cell.setColspan(4);
4301 cell.setHorizontalAlignment (Element.ALIGN_LEFT);
4302 cell.setPadding (10.0f);
4303 table.addCell (cell);
4304
4305 // Now for the fault description banner
4306
4307 cell = new PdfPCell(new Paragraph("Description",headerFont));
4308 cell.setColspan(4);
4309 cell.setHorizontalAlignment (Element.ALIGN_LEFT);
4310 cell.setPadding (10.0f);
4311 table.addCell (cell);
4312
4313 // Now the description of the fault
4314
4315 cell = new PdfPCell(new Paragraph(removeHTMLMarkUp(faultDetails.description),bodyFont));
4316
4317 cell.setColspan(4);
4318 cell.setHorizontalAlignment (Element.ALIGN_LEFT);
4319 cell.setPadding (10.0f);
4320 table.addCell (cell);
4321
4322 // Now for the first row of the table
4323
4324 cell = new PdfPCell(new Paragraph("Time Occurred",bodyFont));
4325 cell.setPadding(5.0f);
4326 table.addCell (cell);
4327
4328 cell = new PdfPCell(new Paragraph(faultDetails.timeOccured,bodyFont));
4329 cell.setPadding(5.0f);
4330 table.addCell (cell);
4331
4332
4333 cell = new PdfPCell(new Paragraph("Date Occurred",bodyFont));
4334 cell.setPadding(5.0f);
4335 table.addCell (cell);
4336
4337 cell = new PdfPCell(new Paragraph(faultDetails.dateOccured,bodyFont));
4338 cell.setPadding(5.0f);
4339 table.addCell (cell);
4340
4341 cell = new PdfPCell(new Paragraph("Entered By",bodyFont));
4342 cell.setPadding(5.0f);
4343 table.addCell (cell);
4344
4345 cell = new PdfPCell(new Paragraph(faultDetails.enteredByName + " " + faultDetails.enteredBySurname,bodyFont));
4346 cell.setPadding(5.0f);
4347 table.addCell (cell);
4348
4349 cell = new PdfPCell(new Paragraph("Assigned to",bodyFont));
4350 cell.setPadding(5.0f);
4351 table.addCell (cell);
4352
4353 // Make sure that the name of the assigned person is correctly
4354 // formatted if the name of the person is unknown.
4355
4356 String assignedName;
4357
4358 if (faultDetails.assignedToSurname.equals("Unknown") || faultDetails.assignedToSurname.equals(FaultDatabaseConstants.GUEST)) {
4359 assignedName = faultDetails.assignedToSurname;
4360 } else {
4361 assignedName = faultDetails.assignedToName + " " + faultDetails.assignedToSurname;
4362 }
4363
4364 cell = new PdfPCell(new Paragraph(assignedName,bodyFont));
4365 cell.setPadding(5.0f);
4366 table.addCell (cell);
4367
4368 cell = new PdfPCell(new Paragraph("Severity",bodyFont));
4369 cell.setPadding(5.0f);
4370 table.addCell (cell);
4371
4372 cell = new PdfPCell(new Paragraph(faultDetails.severity,bodyFont));
4373 cell.setPadding(5.0f);
4374 table.addCell (cell);
4375
4376 cell = new PdfPCell(new Paragraph("Site",bodyFont));
4377 cell.setPadding(5.0f);
4378 table.addCell (cell);
4379
4380 cell = new PdfPCell(new Paragraph(faultDetails.site,bodyFont));
4381 cell.setPadding(5.0f);
4382 table.addCell (cell);
4383
4384 cell = new PdfPCell(new Paragraph("Instrument",bodyFont));
4385 cell.setPadding(5.0f);
4386 table.addCell (cell);
4387
4388 cell = new PdfPCell(new Paragraph(faultDetails.instrument,bodyFont));
4389 cell.setPadding(5.0f);
4390 table.addCell (cell);
4391
4392 cell = new PdfPCell(new Paragraph("State",bodyFont));
4393 cell.setPadding(5.0f);
4394 table.addCell (cell);
4395
4396 cell = new PdfPCell(new Paragraph(faultDetails.state,bodyFont));
4397 cell.setPadding(5.0f);
4398 table.addCell (cell);
4399
4400 cell = new PdfPCell(new Paragraph("Type",bodyFont));
4401 cell.setPadding(5.0f);
4402 table.addCell (cell);
4403
4404 cell = new PdfPCell(new Paragraph(faultDetails.faultType,bodyFont));
4405 cell.setPadding(5.0f);
4406 table.addCell (cell);
4407
4408 cell = new PdfPCell(new Paragraph("Time Entered",bodyFont));
4409 cell.setPadding(5.0f);
4410 table.addCell (cell);
4411
4412 cell = new PdfPCell(new Paragraph(faultDetails.timeEntered,bodyFont));
4413 cell.setPadding(5.0f);
4414 table.addCell (cell);
4415
4416 cell = new PdfPCell(new Paragraph("Date Entered",bodyFont));
4417 cell.setPadding(5.0f);
4418 table.addCell (cell);
4419
4420 cell = new PdfPCell(new Paragraph(faultDetails.dateEntered,bodyFont));
4421 cell.setPadding(5.0f);
4422 table.addCell (cell);
4423
4424 cell = new PdfPCell(new Paragraph("Time Lost",bodyFont));
4425 cell.setPadding(5.0f);
4426 table.addCell (cell);
4427
4428 cell = new PdfPCell(new Paragraph(faultDetails.timeLost,bodyFont));
4429 cell.setPadding(5.0f);
4430 table.addCell (cell);
4431
4432 cell = new PdfPCell(new Paragraph("Observer",bodyFont));
4433 cell.setPadding(5.0f);
4434 table.addCell (cell);
4435
4436 String tmp = faultDetails.observer;
4437
4438 if (faultDetails.observer.equals("")) {
4439 tmp = "Unknown";
4440 }
4441
4442 cell = new PdfPCell(new Paragraph(tmp,bodyFont));
4443 cell.setPadding(5.0f);
4444 table.addCell (cell);
4445
4446 cell = new PdfPCell(new Paragraph("Time Spent",bodyFont));
4447 cell.setPadding(5.0f);
4448 table.addCell (cell);
4449
4450 cell = new PdfPCell(new Paragraph(faultDetails.totalTimeSpentOnFault,bodyFont));
4451 cell.setPadding(5.0f);
4452 table.addCell (cell);
4453
4454 // Add the table to the document
4455
4456 try {
4457 PDFDocument.add(table);
4458 } catch (DocumentException e) {
4459 logger.error("Unable to add the fault table to the document " + e.getMessage());
4460 }
4461
4462 // Now for all of the comments associated with the fault we
4463 // should print them out
4464
4465 for (int i=0; i < faultComments.size(); i++) {
4466
4467 final Comment comment = faultComments.get(i);
4468
4469 logger.debug("Adding comment to report for fault");
4470
4471 // Now add the details of the comment to the report
4472
4473 table = new PdfPTable (new float [] {
4474 1.0f,
4475 });
4476
4477 table.setWidthPercentage(90.0f);
4478
4479 // Now the title banner of the comment
4480
4481 cell = new PdfPCell(new Paragraph("Comment",headerFont));
4482 cell.setPadding (10.0f);
4483 table.addCell(cell);
4484
4485 cell = new PdfPCell(new Paragraph("By " + comment.enteredByName + " " + comment.enteredBySurname + " on the " + comment.dateEntered + " who spent " + comment.timeSpent + " (HH:MM)",bodyFont));
4486 cell.setPadding (5.0f);
4487
4488 cell.setHorizontalAlignment (Element.ALIGN_LEFT);
4489 table.addCell(cell);
4490
4491 // Add the description of the comment to the document
4492
4493 cell = new PdfPCell(new Paragraph(removeHTMLMarkUp(comment.description),bodyFont));
4494
4495 cell.setPadding (10.0f);
4496
4497 table.addCell (cell);
4498
4499 try {
4500 PDFDocument.add(table);
4501 } catch (DocumentException e) {
4502 logger.error("Unable to add the comment to the document " + e.getMessage());
4503 }
4504 }
4505
4506 // Now report any workaround which has been entered
4507
4508 if (workAround != null) {
4509
4510 logger.debug("Adding workaround to report for fault");
4511
4512 // Now add the details of the workaround to the report
4513
4514 table = new PdfPTable (new float [] {
4515 1.0f,
4516 });
4517
4518 table.setWidthPercentage(90.0f);
4519
4520 // Now the title banner of the workaround
4521
4522 cell = new PdfPCell(new Paragraph("Workaround",headerFont));
4523 cell.setPadding (10.0f);
4524 table.addCell(cell);
4525
4526 cell = new PdfPCell(new Paragraph("By " + workAround.enteredByName + " " + workAround.enteredBySurname + " on the " + workAround.dateEntered + " who spent " + workAround.timeSpent + " (HH:MM)",bodyFont));
4527 cell.setPadding (5.0f);
4528
4529 cell.setHorizontalAlignment (Element.ALIGN_LEFT);
4530 table.addCell(cell);
4531
4532 cell = new PdfPCell(new Paragraph(removeHTMLMarkUp(workAround.description),bodyFont));
4533 cell.setPadding (10.0f);
4534
4535 table.addCell (cell);
4536
4537 try {
4538 PDFDocument.add(table);
4539 } catch (DocumentException e) {
4540 logger.error("Unable to add the workaround to the document " + e.getMessage());
4541 }
4542
4543 }
4544
4545 // Now report any solution which has been entered
4546
4547 if (solution != null) {
4548
4549 // Now add the details of the solution to the report
4550
4551 table = new PdfPTable (new float [] {
4552 1.0f,
4553 });
4554
4555 table.setWidthPercentage(90.0f);
4556
4557 // Now the title banner of the solution
4558
4559 cell = new PdfPCell(new Paragraph("Solution",headerFont));
4560 cell.setPadding (10.0f);
4561 table.addCell(cell);
4562
4563 cell = new PdfPCell(new Paragraph("By " + solution.enteredByName + " " + solution.enteredBySurname + " on the " + solution.dateEntered + " who spent " + solution.timeSpent + " (HH:MM)",bodyFont));
4564 cell.setPadding (5.0f);
4565
4566 cell.setHorizontalAlignment (Element.ALIGN_LEFT);
4567 table.addCell(cell);
4568
4569 cell = new PdfPCell(new Paragraph(removeHTMLMarkUp(solution.description),bodyFont));
4570 cell.setPadding (10.0f);
4571
4572 table.addCell (cell);
4573
4574 try {
4575 PDFDocument.add(table);
4576 } catch (DocumentException e) {
4577 logger.error("Unable to add the solution to the document " + e.getMessage());
4578 }
4579 }
4580 }
4581
4582 /**
4583 * Return a list of all of the printers which are currently
4584 * accesible by this machine
4585 * @return ArrayList<String> A list of the printers which can print
4586 * postscript from this machine
4587 * @gwt.typeArgs <java.lang.String>
4588 */
4589
4590 public ArrayList<String> getPostscriptPrinters () {
4591
4592 ArrayList<String> printerList = new ArrayList<String>();
4593
4594 for (PrintService i : PrintServiceLookup.lookupPrintServices(DocFlavor.INPUT_STREAM.POSTSCRIPT,null)) {
4595 printerList.add(i.getName());
4596 }
4597
4598 logger.debug("Found " + printerList.size() + " printers on the host computer");
4599
4600 return printerList;
4601 }
4602
4603 /**
4604 * Used to remove all HTML up characters in a string so that the
4605 * text can be printed in a PDF document.
4606 * @param inputString The string to be converted.
4607 * @return The converted string without the markup characters.
4608 */
4609
4610 private String removeHTMLMarkUp (final String inputString) {
4611
4612 String processedString = inputString;
4613
4614 // By default if the user has used the tags H1-6 through the
4615 // use of the editor widget then we need to augment the final
4616 // tag with a new line as that is not added.
4617
4618 processedString = processedString.replaceAll("</h[1-6]+>","\n");
4619 processedString = processedString.replaceAll("<p>","\n\n");
4620 processedString = processedString.replaceAll(" "," ");
4621 processedString = processedString.replaceAll(">",">");
4622 processedString = processedString.replaceAll("<",">");
4623 processedString = processedString.replaceAll("&","&");
4624 processedString = processedString.replaceAll(""","'");
4625
4626
4627 // Try and preserve the structure of lists
4628
4629 processedString = processedString.replaceAll("<br></li>","</li>");
4630 processedString = processedString.replaceAll("<li>","\to ");
4631 processedString = processedString.replaceAll("</li>","\n");
4632 processedString = processedString.replaceAll("<ul>","\n");
4633 processedString = processedString.replaceAll("<ol>","\n");
4634
4635 processedString = processedString.replaceAll("<br.*?>","\n");
4636 processedString = processedString.replaceAll("\n{3,}?","\n\n");
4637
4638 return processedString.replaceAll("<.*?>","");
4639 }
4640
4641 /**
4642 * Will be used to execute an advanced search across the database
4643 * using the input data which has been provided.
4644 * <p>
4645 * @param SearchParameters These are the search parameters which
4646 * will be used to search across the database.
4647 * @param locale This is the locale which we are operating in
4648 * @param StartDate This is the start date from when to look
4649 * from. This could be left blank.
4650 * @param EndDate This is the end date from when to stop looking
4651 * for faults.
4652 * @throws UnableToPerformSearchException If we were unable to
4653 * perform the search for whatever reason.
4654 * @gwt.typeArgs <GWTApplication.client.Fault>
4655 */
4656
4657 public ArrayList<Fault> performAdvancedSearch (final HashMap SearchParameters,
4658 String StartDate,
4659 String EndDate,
4660 final String locale) throws UnableToPerformSearchException {
4661
4662
4663 /**
4664 * A entity class which is used to hold the data associated
4665 * with the fields in the advanced search
4666 */
4667
4668 class AdvancedSearchParameter {
4669
4670 /**
4671 * This is name of the index into the hashmap which is
4672 * passed down by the client for this parameter
4673 */
4674
4675 String index;
4676
4677 /**
4678 * This is the data type of the attribute in the table.
4679 */
4680
4681 AdvancedDataType dataType;
4682
4683 /**
4684 * This is the name of the attribute in the database table
4685 * which we need to search over
4686 */
4687
4688 String tableAttribute;
4689
4690 /**
4691 * Constructor
4692 */
4693
4694 AdvancedSearchParameter(final String index, final AdvancedDataType dataType, final String tableAttribute) {
4695 this.index = index;
4696 this.dataType = dataType;
4697 this.tableAttribute = tableAttribute;
4698 }
4699 }
4700
4701 // This data structure will be used to help construct the
4702 // advanced search query
4703
4704 final AdvancedSearchParameter advancedSearchParameters[] = {
4705 new AdvancedSearchParameter(SearchForm.OBSERVER,AdvancedDataType.STRING,"OBSERVER"),
4706 new AdvancedSearchParameter(SearchForm.SEVERITY,AdvancedDataType.STRING,"SEVERITY"),
4707 new AdvancedSearchParameter(SearchForm.INSTRUMENT,AdvancedDataType.STRING,"INSTRUMENT"),
4708 new AdvancedSearchParameter(SearchForm.SITE,AdvancedDataType.STRING,"SITE_ID"),
4709 new AdvancedSearchParameter(SearchForm.SYSTEM,AdvancedDataType.STRING,"SYSTEM_ID"),
4710 new AdvancedSearchParameter(SearchForm.ORIGINATOR,AdvancedDataType.STRING,"ENTEREDBY_EMAIL"),
4711 new AdvancedSearchParameter(SearchForm.TO,AdvancedDataType.STRING,"TO_EMAIL"),
4712 new AdvancedSearchParameter(SearchForm.DUTYENGINEER,AdvancedDataType.STRING,"DUTYTECHNICIAN_EMAIL"),
4713 new AdvancedSearchParameter(SearchForm.SA,AdvancedDataType.STRING,"SA_EMAIL"),
4714 new AdvancedSearchParameter(SearchForm.ASSIGNEDTO,AdvancedDataType.STRING,"ASSIGNEDTO_EMAIL"),
4715 new AdvancedSearchParameter(SearchForm.FAULTTYPE,AdvancedDataType.STRING,"FAULTTYPE_ID"),
4716 new AdvancedSearchParameter(SearchForm.STATUSTYPE,AdvancedDataType.STRING,"STATE_ID"),
4717 };
4718
4719 // The results of the search
4720
4721 final ArrayList<Fault> matchingFaults = new ArrayList<Fault>();
4722
4723 // The free format text fields which have been specified by
4724 // the user in the keywords box in the search form
4725
4726 String searchTerm = (String)SearchParameters.get(SearchForm.KEYWORDS);
4727
4728 String[] searchItems = null;
4729
4730 // This will be used for joining all of the various search terms
4731
4732 String searchTermsBooleanOperator,keywordsBooleanOperator;
4733
4734 // This is the boolean operator which will be used for
4735 // conjugating the keyword search terms
4736
4737 if (((String)SearchParameters.get(SearchForm.USEORKEYWORDS)).equals(SearchForm.CHECKED)) {
4738 keywordsBooleanOperator = " or ";
4739 } else {
4740 keywordsBooleanOperator = " and ";
4741 }
4742
4743 // Identify if the user has indicated that he would like the
4744 // search terms conjugated using OR instead of AND
4745
4746 if (((String)SearchParameters.get(SearchForm.USEOR)).equals(SearchForm.CHECKED)) {
4747 searchTermsBooleanOperator = " or ";
4748 } else {
4749 searchTermsBooleanOperator = " and ";
4750 }
4751
4752 // Convert all of the * characters in the search term to %
4753 // symbols to be SQL compatible
4754
4755 if (searchTerm != null) {
4756
4757 searchTerm = searchTerm.trim();
4758
4759 searchTerm = searchTerm.replace('*','%');
4760 searchTerm = searchTerm.replace("_","\\_");
4761 searchTerm = searchTerm.replace("'","\\'");
4762
4763 // Now break up the search string based on spaces and spaces
4764 // between quoted text.
4765
4766 searchItems = searchTerm.split("[ ]|\"[ ]?\"",0);
4767 }
4768
4769 // Now create a SQL statement which can be used to go through
4770 // the faults
4771
4772 String view;
4773
4774 // Now depending on the locale we search different views
4775
4776 if (locale.equalsIgnoreCase("en")) {
4777 view = "faultview_en";
4778 } else {
4779 view = "faultview_sp";
4780 }
4781
4782 // For each text field in the view we now create a search
4783 // across the fields.
4784
4785 final StringBuilder sqlClause = new StringBuilder("select * from " + view + " where ");
4786
4787 boolean firstClauseInWhereStatement = true;
4788
4789 // If the user specified anything in the keywords field then
4790 // we should add these search terms into the SQL statement
4791
4792 if (searchTerm != null) {
4793
4794 for (int j=0; j < searchItems.length; j++) {
4795
4796 if (firstClauseInWhereStatement) {
4797
4798 sqlClause.append("(");
4799 firstClauseInWhereStatement = false;
4800
4801 } else {
4802
4803 // In the description and title field, if we
4804 // have more than one search term, by default
4805 // we perform an AND on the search terms e.g
4806 // if the user specifies "petals closing",
4807 // then the search engine will match
4808 // descriptions which have the words petals
4809 // and closing in them. However, the user may
4810 // modify this behaviour by clicking upon the
4811 // checkbox labelled Use Or in Keywords ......
4812
4813 sqlClause.append(keywordsBooleanOperator);
4814 }
4815
4816 sqlClause.append("lower(CONJUGATEDTEXTFIELD) LIKE '%" + searchItems[j].toLowerCase() + "%'");
4817
4818 // Establish if the user has entered an integer value for the
4819 // search term and if he has then we have to try and match
4820 // that value against a fault identifier
4821
4822 try {
4823
4824 Integer.valueOf(searchTerm);
4825
4826 // We managed to parse the integer so now add an SQL term
4827
4828 sqlClause.append(" or id = " + searchTerm);
4829
4830 } catch (NumberFormatException e) {
4831 // Do nothing if the search term is not an integer.
4832 }
4833
4834 firstClauseInWhereStatement = false;
4835 }
4836 }
4837
4838 // Now we need to go through the rest of the terms which the
4839 // user may have entered into the form and then add them into
4840 // the search query
4841
4842 if (!firstClauseInWhereStatement) {
4843 sqlClause.append(")");
4844 }
4845
4846 // Now we need to add in all of the additional clauses that
4847 // the user may have specified
4848
4849 // Add in the DATEOCCURED clause if the user specified it. Note
4850 // that the user can enter a > or < operator which should be
4851 // respected.
4852
4853 String dateOccured = (String)SearchParameters.get(SearchForm.DATEOCCURED);
4854
4855 // If this is the first clause after the keywords, we need to
4856 // AND the result of the keyword search clause with the result
4857 // of the search across all of the specific fields.
4858
4859 final StringBuilder specificTermSubClause = new StringBuilder();
4860
4861 boolean specificSearchTermsFound = false;
4862
4863 // Whether this is the first term in the subclause which
4864 // contains the list terms which have been additionally
4865 // selected by the user in the advanced search panel
4866
4867 boolean firstSpecificTermInSubClause = true;
4868
4869 if (dateOccured != null) {
4870
4871 final String operator = firstSpecificTermInSubClause?"":searchTermsBooleanOperator;
4872
4873 dateOccured = dateOccured.replaceAll(" ","");
4874 dateOccured = dateOccured.replaceAll(">",">'");
4875 dateOccured = dateOccured.replaceAll("<","<'");
4876
4877 if (dateOccured.contains(">") || dateOccured.contains("<")) {
4878 specificTermSubClause.append(operator + "TIMEOCCURED" + dateOccured + "'");
4879 } else {
4880 specificTermSubClause.append(operator + "TIMEOCCURED = '" + dateOccured + "'");
4881 }
4882
4883 firstSpecificTermInSubClause = false;
4884 specificSearchTermsFound = true;
4885 }
4886
4887 // Add in the PRIORITY if the user specified it. Note
4888 // that the user can enter a > or < operator which should be
4889 // respected.
4890
4891 String priority = (String)SearchParameters.get(SearchForm.PRIORITY);
4892
4893 if (priority != null) {
4894
4895 final String operator = firstSpecificTermInSubClause?"":searchTermsBooleanOperator;
4896
4897 if (priority.contains(">") || priority.contains("<")) {
4898 specificTermSubClause.append(operator + "PRIORITY" + priority);
4899 } else {
4900 specificTermSubClause.append(operator + "PRIORITY = " + priority);
4901 }
4902
4903 firstSpecificTermInSubClause = false;
4904 specificSearchTermsFound = true;
4905 }
4906
4907 // Add in the TIMELOST clause if the user specified it. Note
4908 // that the user can enter a > or < operator which should be
4909 // respected.
4910
4911 String timeLost = (String)SearchParameters.get(SearchForm.TIMELOST);
4912
4913 if (timeLost != null) {
4914
4915 final String operator = firstSpecificTermInSubClause?"":searchTermsBooleanOperator;
4916
4917 timeLost = timeLost.replaceAll(" ","");
4918 timeLost = timeLost.replaceAll(">",">'");
4919 timeLost = timeLost.replaceAll("<","<'");
4920
4921 if (timeLost.contains(">") || timeLost.contains("<")) {
4922 specificTermSubClause.append(operator + " TIMELOST_INTERVAL " + timeLost + "'");
4923 } else {
4924 specificTermSubClause.append(operator + " TIMELOST_INTERVAL = '" + timeLost + "'");
4925 }
4926
4927 firstSpecificTermInSubClause = false;
4928 specificSearchTermsFound = true;
4929 }
4930
4931 // Add in the defect number clause if the user specified it.
4932
4933 final String defectNumber = (String)SearchParameters.get(SearchForm.DEFECTNUMBER);
4934
4935 if (defectNumber != null) {
4936
4937 final String operator = firstSpecificTermInSubClause?"":searchTermsBooleanOperator;
4938
4939 if (defectNumber.contains(">") || defectNumber.contains("<")) {
4940 specificTermSubClause.append(operator + "ID" + defectNumber);
4941 } else {
4942 specificTermSubClause.append(operator + "ID = " + defectNumber + " ");
4943 }
4944
4945 firstSpecificTermInSubClause = false;
4946 specificSearchTermsFound = true;
4947
4948 }
4949
4950 // Now add in any of the advanced fields into the search
4951 // query
4952
4953 for (int i=0; i < advancedSearchParameters.length ;i++) {
4954
4955 String operator = firstSpecificTermInSubClause?"":searchTermsBooleanOperator;
4956
4957 final AdvancedSearchParameter searchParameter = advancedSearchParameters[i];
4958
4959 String searchKey;
4960
4961 searchKey = (String)SearchParameters.get(searchParameter.index);
4962
4963 if (searchKey != null) {
4964
4965 searchKey = searchKey.toLowerCase();
4966
4967 switch (searchParameter.dataType) {
4968
4969 case STRING :
4970 specificTermSubClause.append(operator + "lower(" + searchParameter.tableAttribute + ") ='" + searchKey + "'");
4971 firstSpecificTermInSubClause = false;
4972 break;
4973
4974 case NONSTRING :
4975 specificTermSubClause.append(operator + "lower(" + searchParameter.tableAttribute + ")=" + searchKey);
4976 firstSpecificTermInSubClause = false;
4977 break;
4978 }
4979
4980 specificSearchTermsFound = true;
4981 }
4982 }
4983
4984 // If we added a specific search term, then close the
4985 // bracketing
4986
4987 if (specificSearchTermsFound) {
4988
4989 if (!firstClauseInWhereStatement) {
4990 sqlClause.append(" and (" + specificTermSubClause + ")");
4991 } else {
4992 sqlClause.append(" (" + specificTermSubClause + ")");
4993 firstClauseInWhereStatement = false;
4994 }
4995 }
4996
4997 // If the user has specified a time window then we should add
4998 // that into the SQL as well
4999
5000 if (!EndDate.equals("") && !StartDate.equals("")) {
5001
5002 // If the user has specified both dates we need to ensure
5003 // that the start date is before the end date and if not
5004 // swap them around.
5005
5006 final String[] endDateInFields = EndDate.split("/");
5007
5008 final String[] startDateInFields = StartDate.split("/");
5009
5010 final GregorianCalendar startDate,endDate;
5011
5012 try {
5013 startDate = new GregorianCalendar(Integer.parseInt(startDateInFields[2]),Integer.parseInt(startDateInFields[1]),Integer.parseInt(startDateInFields[0]));
5014 endDate = new GregorianCalendar(Integer.parseInt(endDateInFields[2]),Integer.parseInt(endDateInFields[1]),Integer.parseInt(endDateInFields[0]));
5015 } catch(NumberFormatException e) {
5016 throw new UnableToPerformSearchException("Problem formatting date fields");
5017 }
5018
5019 // If the start date is after the end date then swap them around
5020
5021 if (startDate.compareTo(endDate) == 1) {
5022
5023 String temp = StartDate;
5024
5025 StartDate = EndDate;
5026 EndDate = temp;
5027 }
5028
5029 // If there are additional clauses in the search then add
5030 // a boolean operator
5031
5032 if (!firstClauseInWhereStatement) {
5033 sqlClause.append(" and ");
5034 }
5035
5036 // Add a day onto the end date to make sure that the time
5037 // window is inclusive of the final day
5038
5039 sqlClause.append(" timeentered >= '" + StartDate + "' and timeentered <= cast(cast ('" + EndDate + "' as date) + cast ('1 day' as interval) as date) ");
5040
5041 } else if (!EndDate.equals("")) {
5042
5043 // If there are additional clauses in the search then add
5044 // a boolean operator
5045
5046 if (!firstClauseInWhereStatement) {
5047 sqlClause.append(" and ");
5048 }
5049
5050 // Add a day onto the end date to make sure that the time
5051 // window is inclusive of the final day
5052
5053 sqlClause.append(" timeentered <= cast(cast ('" + EndDate + "' as date) + cast ('1 day' as interval) as date) ");
5054
5055 } else if (!StartDate.equals("")) {
5056
5057 // If there are additional clauses in the search then add
5058 // a boolean operator
5059
5060 if (!firstClauseInWhereStatement) {
5061 sqlClause.append(" and ");
5062 }
5063
5064 // Add a day onto the end date to make sure that the time
5065 // window is inclusive of the final day
5066
5067 sqlClause.append(" timeentered >= '" + StartDate + "'");
5068 }
5069
5070 sqlClause.append(" order by id desc limit " + MAXNUMBEROFRESULTS);
5071
5072 logger.debug("Search string is : " + sqlClause);
5073
5074 // Now get the fault data back from the database
5075
5076 Connection connection = null;
5077 Statement stmt = null;
5078 ResultSet rs = null;
5079
5080 // Now get a database connection from the connection pool if
5081 // we can
5082
5083 if (dataSource == null) {
5084 dataSource = (DataSource) getServletContext().getAttribute("DBCPool");
5085 }
5086
5087 try {
5088
5089 connection = dataSource.getConnection();
5090 stmt = connection.createStatement();
5091 rs = stmt.executeQuery(sqlClause.toString());
5092
5093 while (rs.next()) {
5094
5095 Fault faultData = packFault(rs,true);
5096 matchingFaults.add(faultData);
5097 }
5098
5099 // Ensure that we clean up the database connection
5100 // correctly so that the connection is returned to the
5101 // database connection pool
5102
5103 rs.close();
5104 connection.close();
5105 stmt.close();
5106
5107 rs = null;
5108 connection = null;
5109 stmt = null;
5110
5111 } catch (SQLException e) {
5112 e.printStackTrace();
5113 throw new UnableToPerformSearchException("Database error : " + e.getMessage());
5114 } finally {
5115
5116 // Always make sure result sets and statements are closed,
5117 // and the connection is returned to the pool
5118
5119 closeDatabaseConnections(connection,stmt,rs);
5120
5121 }
5122
5123 return matchingFaults;
5124 }
5125
5126 /**
5127 * Called to close a fault down by a user with administration
5128 * priviledges.
5129 * @param faultId This is is the number of the fault which is
5130 * to be closed down.
5131 * @throws UnableToCloseFaultException Thrown when the method is
5132 * unable to close down the fault for whatever reason.
5133 */
5134
5135 public void closeFault (final int faultId) throws UnableToCloseFaultException {
5136
5137 // Now set the state of the fault in the database accordingly
5138
5139 Connection connection = null;
5140 Statement stmt = null;
5141
5142 // Now get a database connection from the connection pool if
5143 // we can
5144
5145 if (dataSource == null) {
5146 dataSource = (DataSource) getServletContext().getAttribute("DBCPool");
5147 }
5148
5149 try {
5150
5151 connection = dataSource.getConnection();
5152 stmt = connection.createStatement();
5153
5154 // If a user closes a fault down we move it to state
5155 // RELEASED.
5156
5157 stmt.execute("update fault set faultopen = 'false',state = 'RELEASED', lastmodified = 'NOW' where id = " + faultId);
5158
5159 connection.close();
5160 stmt.close();
5161
5162 connection = null;
5163 stmt = null;
5164
5165 } catch (SQLException e) {
5166 e.printStackTrace();
5167 throw new UnableToCloseFaultException ("Database error : " + e.getMessage());
5168 } finally {
5169
5170 // Always make sure result sets and statements are closed,
5171 // and the connection is returned to the pool
5172
5173 closeDatabaseConnections(connection,stmt,null);
5174 }
5175
5176 // Inform all email listeners that the fault has now been closed down
5177
5178 informEmailListenersFaultHasChanged("This fault has now been closed down", faultId);
5179 }
5180
5181 /**
5182 * Get a list of the faults from the database which have been
5183 * updated since the last time that this client checked the
5184 * database. We return to the caller an array of the full history
5185 * of the faults which have been changed.
5186 * @param timeOfLastUpdateCheck This is the time that we last
5187 * checked to see if the faults we are interested in were updated
5188 * @param locale This is the locale which we are operating in
5189 * @return FaultUpdateSnapshot The details of all of the faults
5190 * which have been updated since the time specified
5191 */
5192
5193 public FaultUpdateSnapshot getFaultsUpdated (Date timeOfLastUpdateCheck, final String locale) {
5194
5195 final FaultUpdateSnapshot updatedFaults = new FaultUpdateSnapshot();
5196
5197 String view;
5198
5199 // This is the time from which we need to search from when
5200 // comparing against the lastmodified attribute of the fault
5201 // table.
5202
5203 java.util.GregorianCalendar dateToSearchFrom = new java.util.GregorianCalendar();
5204
5205 if (timeOfLastUpdateCheck == null) {
5206
5207 Date currentTime = new Date();
5208
5209 timeOfLastUpdateCheck = new Date(currentTime.getTime() - 20000L);
5210 }
5211
5212 dateToSearchFrom.setTime(timeOfLastUpdateCheck);
5213
5214 // Now we need to convert the time which has been sent to us
5215 // from the client into a time which we can use to compare
5216 // against the lastmodified timestamp in the fault table
5217
5218 final SimpleDateFormat dateFormatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SS");
5219
5220 // Execute the SQL statement and return the details to the caller
5221
5222 Connection connection = null;
5223 Statement stmt = null;
5224 ResultSet rs = null;
5225
5226 // Now get a database connection from the connection pool if
5227 // we can
5228
5229 if (dataSource == null) {
5230 dataSource = (DataSource) getServletContext().getAttribute("DBCPool");
5231 }
5232
5233 try {
5234
5235 connection = dataSource.getConnection();
5236 stmt = connection.createStatement();
5237
5238 rs = stmt.executeQuery("select id from fault where lastmodified > '" + dateFormatter.format(dateToSearchFrom.getTime()) + "'");
5239
5240 //logger.debug("select id from fault where lastmodified > '" + dateFormatter.format(dateToSearchFrom.getTime()) + "'");
5241
5242 while (rs.next()) {
5243
5244 // Add the details of the update fault to the list of
5245 // faults which will be returned.
5246
5247 updatedFaults.faultsUpdated.add(getFullFaultHistory(rs.getInt("id"),locale));
5248 }
5249
5250 // Ensure that we clean up the database connection
5251 // correctly so that the connection is returned to the
5252 // database connection pool
5253
5254 rs.close();
5255 connection.close();
5256 stmt.close();
5257
5258 rs = null;
5259 connection = null;
5260 stmt = null;
5261
5262 } catch (SQLException e) {
5263 e.printStackTrace();
5264 } finally {
5265
5266 // Always make sure result sets and statements are closed,
5267 // and the connection is returned to the pool
5268
5269 closeDatabaseConnections(connection,stmt,rs);
5270 }
5271
5272 //logger.debug("There are " + updatedFaults.faultsUpdated.size() + " faults which have been changed");
5273
5274 return updatedFaults;
5275 }
5276
5277
5278 /**
5279 * Used by the client to establish if the fault actually exists in the database
5280 * @param faultId The defect number of the fault to be checked
5281 * @param isEditable Check that the fault is editable
5282 * @return boolean True if the fault exists
5283 */
5284
5285 public Boolean faultExists(final int faultId,final boolean isEditable) {
5286
5287 Connection connection = null;
5288 Statement stmt = null;
5289 ResultSet rs = null;
5290
5291 Boolean faultFound = false;
5292
5293 // Now get a database connection from the connection pool if
5294 // we can
5295
5296 if (dataSource == null) {
5297 dataSource = (DataSource) getServletContext().getAttribute("DBCPool");
5298 }
5299
5300 try {
5301
5302 final StringBuffer SQLStatement = new StringBuffer("select id from fault where id = " + faultId);
5303
5304 // If the caller has requested to check that the fault can
5305 // be edited as well then add in a check into the SQL
5306 // statement
5307
5308 if (isEditable) {
5309 SQLStatement.append(" and faultopen = true");
5310 }
5311
5312 connection = dataSource.getConnection();
5313 stmt = connection.createStatement();
5314 rs = stmt.executeQuery(SQLStatement.toString());
5315
5316 if (rs.next()) {
5317 faultFound = true;
5318 }
5319
5320 // Ensure that we clean up the database connection
5321 // correctly so that the connection is returned to the
5322 // database connection pool
5323
5324 rs.close();
5325 connection.close();
5326 stmt.close();
5327
5328 rs = null;
5329 connection = null;
5330 stmt = null;
5331
5332 } catch (SQLException e) {
5333 e.printStackTrace();
5334 } finally {
5335 closeDatabaseConnections(connection,stmt,rs);
5336 }
5337
5338 return faultFound;
5339 }
5340
5341 /**
5342 * Used for closing down database connections
5343 * @param connection Connection to the database to be closed.
5344 * @param stmt Statement to be closed.
5345 * @param rs Result set to be closed.
5346 */
5347
5348 public void closeDatabaseConnections (Connection connection,Statement stmt, ResultSet rs) {
5349
5350 if (rs != null) {
5351 try {
5352 rs.close();
5353 } catch (SQLException e) { ; }
5354 }
5355
5356 if (stmt != null) {
5357 try {
5358 stmt.close();
5359 } catch (SQLException e) { ; }
5360 }
5361
5362 if (connection != null) {
5363 try {
5364 connection.close();
5365 } catch (SQLException e) { ; }
5366 }
5367 }
5368
5369 /**
5370 * This will be used to return the full history of a fault. This
5371 * includes the fault itself, any workaround, any solution, any
5372 * comments and any links.
5373 * @param defectNumber The fault number for the fault
5374 * @param locale This is the locale to use
5375 * @return FaultEntryDetails The full history of the fault
5376 */
5377
5378 public FaultEntryDetails getFullFaultHistory (final int defectNumber,final String locale) {
5379
5380 final FaultEntryDetails faultEntryDetails = new FaultEntryDetails();
5381
5382 faultEntryDetails.fault = getFaultDetails(defectNumber,locale);
5383 faultEntryDetails.solution = getSolutionDetails(defectNumber);
5384 faultEntryDetails.workAround = getWorkaroundDetails(defectNumber);
5385 faultEntryDetails.comments = getCommentDetails(defectNumber);
5386 faultEntryDetails.links = getLinkedFaults(defectNumber);
5387
5388 return faultEntryDetails;
5389 }
5390
5391 /**
5392 * This is used to get a list of the faults which the user specified is subscribed to
5393 * @param authenticationDetails The details of the person that has logged into the system.
5394 * @param locale The locale which the user has selected
5395 * @return ArrayList This is a list of faults which the user is subscribed to
5396 */
5397
5398 public ArrayList getSubscribedFaults(final AuthenticationDetails authenticationDetails,final String locale) {
5399
5400 final ArrayList<Fault> searchResults = new ArrayList();
5401
5402 Connection connection = null;
5403 Statement stmt = null;
5404 ResultSet rs = null;
5405
5406 logger.debug("Getting list of subscribed faults for " + authenticationDetails.UserEmailAddress);
5407
5408 // Now get a database connection from the connection pool if
5409 // we can
5410
5411 if (dataSource == null) {
5412 dataSource = (DataSource) getServletContext().getAttribute("DBCPool");
5413 }
5414
5415 try {
5416
5417 final StringBuffer SQLStatement = new StringBuffer("select * from fault where faultopen = 'true' and id in (select id from faultlistener where email = '" + authenticationDetails.UserEmailAddress + "') order by id desc");
5418
5419 connection = dataSource.getConnection();
5420 stmt = connection.createStatement();
5421 rs = stmt.executeQuery(SQLStatement.toString());
5422
5423 int count = 0;
5424
5425 while (rs.next()) {
5426 Fault faultData = packFault(rs,false);
5427 searchResults.add(faultData);
5428 count++;
5429 }
5430
5431 logger.info("Found " + count + " faults which have been subscribed to by user " + authenticationDetails.UserEmailAddress);
5432
5433 } catch (SQLException e) {
5434 e.printStackTrace();
5435 } finally {
5436 closeDatabaseConnections(connection,stmt,rs);
5437 }
5438
5439 return searchResults;
5440 }
5441
5442 /**
5443 * Will update the list of faults which a user is subscribed to.
5444 * @param authenticationDetails Details of the user
5445 * @param newSubscriptionStatuses This is a hashmap of the subscription statues of faults
5446 */
5447
5448 public void updateFaultSubscriptionList (final AuthenticationDetails authenticationDetails, final HashMap newSubscriptionStatuses) {
5449
5450 if (authenticationDetails == null || newSubscriptionStatuses == null) {
5451 logger.error("Input parameters not specified correctly");
5452 return;
5453 }
5454
5455 // Get the email address of the person
5456
5457 final String userEmailAddress = authenticationDetails.UserEmailAddress;
5458
5459 Iterator iterator = newSubscriptionStatuses.keySet().iterator();
5460
5461 Connection connection = null;
5462 PreparedStatement preparedStatement = null;
5463
5464 // Now update the database table which is holding the
5465 // status of the updates.
5466
5467
5468 if (dataSource == null) {
5469 dataSource = (DataSource) getServletContext().getAttribute("DBCPool");
5470 }
5471
5472 try {
5473
5474 connection = dataSource.getConnection();
5475
5476 preparedStatement = connection.prepareStatement("delete from faultlistener where email = '" + userEmailAddress + "' and id = ?");
5477
5478 while (iterator.hasNext()) {
5479
5480 final Integer defectNo = (Integer)iterator.next();
5481
5482 final Boolean listenToFault = (Boolean) newSubscriptionStatuses.get(defectNo);
5483
5484 // If the user does not want to listen to a fault then
5485 // delete the subscription
5486
5487 if (!listenToFault) {
5488 preparedStatement.setInt(1,defectNo);
5489 preparedStatement.executeUpdate();
5490 }
5491 }
5492
5493 preparedStatement.close();
5494
5495 } catch (SQLException e) {
5496 logger.error("Unable to update the faultlistener table for " + userEmailAddress + " " + e.getMessage());
5497 } finally {
5498
5499 try {
5500
5501 if (preparedStatement != null) {
5502 preparedStatement.close();
5503 }
5504
5505 if (connection != null) {
5506 connection.close();
5507 }
5508
5509 } catch (SQLException e){
5510 // Do nothing here
5511 }
5512 }
5513 }
5514 }