1   package ch.ige.edossier.web.server.dao;
2   
3   import java.sql.*;
4   import java.util.*;
5   import ch.ige.edossier.util.DBHelper;
6   import ch.ige.edossier.util.ServerProperties;
7   import ch.ige.edossier.web.vo.InterceptionVO;
8   import ch.ige.edossier.web.vo.NewsletterVO;
9   import oracle.jdbc.OracleResultSet;
10  import oracle.sql.BLOB;
11  import oracle.sql.CLOB;
12  
13  /**
14   * B32.03 - eDossier-Interceptions - Diplomarbeit an der Software-Schule Schweiz<br>
15   * Data Access Objekt für die Tabelle INTERCEPTION
16   * <p>
17   * Copyright (c) 2004, Eidgenössisches Institut für Geistiges Eigentum
18   * @author    Anita Rueegsegger, Marc Bouquet
19   * @version   $Id: InterceptionDAO.java,v 1.19 2004/11/03 14:46:37 bouquet Exp $
20   */
21  public class InterceptionDAO
22  {
23    /**
24     * Fügt einen Datensatz in die Tabelle INTERCEPTION ein
25     * @param interceptionVO InterceptionVO Value-Object
26     * @exception Exception Unerwarteter SQL Fehler oder Konvertierungsfehler
27     */
28    public void insert( InterceptionVO interceptionVO ) throws Exception
29    {
30      Connection con = null;
31      PreparedStatement ps = null;
32      PreparedStatement ps1 = null;
33      PreparedStatement ps2 = null;
34      ResultSet rs = null;
35      BLOB blob = null;
36  
37      try
38      {
39        con = DBHelper.getInstance().getEsolutionConnection();
40        // Auto commit must be turned off.
41        con.setAutoCommit( false );
42        // Insert Values and preparing blob-column with empty-blob
43        ps = con.prepareStatement(
44            "INSERT INTO edossier.interception(" +
45            "interception_id, dossier_id, state_id, titel, text, pdf, letter_dat, answer_dat, expire_dat, event_idat, ma_name, ma_vname, ma_email, ma_tel ) " +
46            "VALUES(?,?,?,?,?,EMPTY_BLOB(),?,?,?,?,?,?,?,?)"
47            );
48  
49        ps.setInt( 1, interceptionVO.getInterceptionId() );
50        ps.setInt( 2, interceptionVO.getDossierId() );
51        ps.setInt( 3, interceptionVO.getStateId() );
52        ps.setString( 4, interceptionVO.getTitel() );
53        ps.setString( 5, interceptionVO.getText() );
54        ps.setDate( 6, interceptionVO.getLetterDat() );
55        ps.setDate( 7, interceptionVO.getAnswerDat() );
56        ps.setDate( 8, interceptionVO.getExpireDat() );
57        ps.setInt( 9, interceptionVO.getEventIdat() );
58        ps.setString( 10, interceptionVO.getMaName() );
59        ps.setString( 11, interceptionVO.getMaVname() );
60        ps.setString( 12, interceptionVO.getMaEmail() );
61        ps.setString( 13, interceptionVO.getMaTel() );
62  
63        if( ps.executeUpdate() != 1 )
64        {
65          throw new SQLException();
66        }
67  
68        // Select the blob column for update.
69        // An OracleResultSet will be used.
70        ps2 = con.prepareStatement( "SELECT interception_id, pdf FROM interception WHERE interception_id = ? FOR UPDATE" );
71        ps2.setInt( 1, interceptionVO.getInterceptionId() );
72  
73        rs = ( OracleResultSet )ps2.executeQuery();
74        // Throw an exception if the blob reference was not retrieved.
75        if( !rs.next() )
76        {
77          throw new SQLException( "Unable to retrieve BLOB reference." );
78        }
79        blob = ( oracle.sql.BLOB )rs.getBlob( "pdf" );
80  
81        // update blob
82        ps1 = con.prepareStatement( "UPDATE interception set pdf = ? WHERE interception_id = ? " );
83        ps1.setInt( 2, interceptionVO.getInterceptionId() );
84        if( interceptionVO.getPdf() == null )
85        {
86          ps1.setNull( 1, java.sql.Types.BLOB );
87        }
88        else
89        {
90          blob.putBytes( 1, interceptionVO.getPdf() );
91          ps1.setBlob( 1, blob );
92        }
93        con.commit();
94        ps1.executeUpdate();
95      }
96      finally
97      {
98        if( rs != null )
99        {
100         rs.close();
101       }
102       if( ps1 != null )
103       {
104         ps1.close();
105       }
106       if( ps2 != null )
107       {
108         ps2.close();
109       }
110       if( con != null && ps != null )
111       {
112         DBHelper.getInstance().close( con, ps );
113       }
114     }
115   }
116 
117   /**
118    * Selektiert einen Datensatz der Tabelle INTERCEPTION
119    * @param interceptionId int Key zum selektieren des Datensatzes.
120    * @return InterceptionVO Value-Object
121    * @throws Exception Unerwarteter Fehler
122    */
123   public InterceptionVO select( int interceptionId ) throws Exception
124   {
125     Connection con = null;
126     PreparedStatement ps = null;
127     ResultSet rs = null;
128     InterceptionVO vo = null;
129 
130     try
131     {
132       con = DBHelper.getInstance().getEsolutionConnection();
133       ps = con.prepareStatement( getSelectStatement() + "where interception_id = ?" );
134 
135       ps.setInt( 1, interceptionId );
136 
137       rs = ps.executeQuery();
138       if( rs.next() )
139       {
140         vo = fillVO( rs );
141       }
142 
143       return vo;
144     }
145     finally
146     {
147       DBHelper.getInstance().close( con, ps );
148     }
149   }
150 
151   /**
152    * Selektierte alle Einträge eines Dossiers der Tablle INTERCEPTION
153    * @param dossierId int Nummer zum selektieren der Datensätze
154    * @return List mit Value-Objects InterceptionVO
155    * @throws Exception Unerwarteter SQL-Fehler oder I/O-Fehler(Stream)
156    */
157   public List selectByDossier( int dossierId ) throws Exception
158   {
159     Connection con = null;
160     PreparedStatement ps = null;
161     ResultSet rs = null;
162     List list = new ArrayList();
163     InterceptionVO vo = null;
164 
165     try
166     {
167       con = DBHelper.getInstance().getEsolutionConnection();
168 
169       ps = con.prepareStatement( getSelectStatement() + "where dossier_id = ? order by expire_dat, state_id" );
170       ps.setInt( 1, dossierId );
171       rs = ps.executeQuery();
172 
173       while( rs.next() )
174       {
175         vo = this.fillVO( rs );
176         list.add( vo );
177       }
178     }
179     finally
180     {
181       DBHelper.getInstance().close( con, ps );
182     }
183     return list;
184   }
185 
186   /**
187    * Diese Methode gibt eine Liste mit Newsletter-Value-Objects zurück, bei denen da Firstdatum in den
188    * nächsten 14 Tagen abläuft.
189    * @throws Exception Unerwarteter Fehler
190    * @return List Liste mit Newsletter-Value-Objects
191    */
192   public List endOfExpireDat() throws Exception
193   {
194     Connection con = null;
195     PreparedStatement ps = null;
196     ResultSet rs = null;
197     List list = new ArrayList();
198     NewsletterVO vo = null;
199     CLOB clob = null;
200     char[] buf = null;
201 
202     try
203     {
204       con = DBHelper.getInstance().getEsolutionConnection();
205       ps = con.prepareStatement( "SELECT a.interception_id, a.dossier_id, a.titel, b.description, b.guard_nr, b.reference, b.language_cd, a.expire_dat, a.state_id, c.email " +
206                                  "FROM edossier.interception a, edossier.dossier b, edossier.account c " +
207                                  "WHERE a.expire_dat < sysdate + 14 " +
208                                  "AND a.dossier_id = b.dossier_id " +
209                                  "AND b.account_id = c.account_id" );
210       rs = ps.executeQuery();
211 
212       while( rs.next() )
213       {
214         vo = new NewsletterVO();
215         vo.setInterceptionId( rs.getInt( "interception_id" ) );
216         vo.setDossierId( rs.getInt( "dossier_id" ) );
217 
218         clob = ( CLOB ) ( ( OracleResultSet )rs ).getClob( "titel" );
219         if( clob != null )
220         {
221           buf = new char[ ( int )clob.length() ];
222 
223           clob.getCharacterStream().read( buf );
224           vo.setTitel( new String( buf ).trim() );
225         }
226         vo.setDescription( rs.getString( "description" ) );
227         vo.setGuard_nr( rs.getString( "guard_nr" ) );
228         vo.setReference( rs.getString( "reference" ) );
229         vo.setLanguageCd( getLanguageText( rs.getInt( "language_cd" ) ) );
230         vo.setExpireDat( rs.getDate( "expire_dat" ) );
231         vo.setStateId( rs.getInt( "state_id" ) );
232         vo.setEmail( rs.getString( "email" ) );
233         list.add( vo );
234       }
235     }
236     finally
237     {
238       DBHelper.getInstance().close( con, ps );
239     }
240 
241     return list;
242   }
243 
244   /**
245    * Diese Methode liest alle neuen Interceptions des heutigen Tages aus.
246    * @param today String Datum des heutigen Tages
247    * @throws Exception Unerwarteter Fehler
248    * @return Collection mit Newsletter-Value-Objects
249    */
250   public Collection newInterception( String today ) throws Exception
251   {
252     Connection con = null;
253     PreparedStatement ps = null;
254     ResultSet rs = null;
255     Map map = new HashMap();
256     NewsletterVO vo = null;
257     CLOB clob = null;
258     char[] buf = null;
259 
260     try
261     {
262       con = DBHelper.getInstance().getEsolutionConnection();
263       ps = con.prepareStatement( "SELECT a.interception_id, a.dossier_id, a.titel, b.description, b.guard_nr, b.reference, b.language_cd, a.expire_dat, a.state_id, c.email " +
264                                  "FROM edossier.interception a, edossier.dossier b, edossier.account c " +
265                                  "WHERE a.created_dat > to_date(?, 'yyyy-MM-dd') " +
266                                  "AND a.state_id = 1 " +
267                                  "AND a.dossier_id = b.dossier_id " +
268                                  "AND b.account_id = c.account_id " +
269                                  "ORDER BY c.email" );
270       ps.setString( 1, today );
271       rs = ps.executeQuery();
272 
273       while( rs.next() )
274       {
275         String email = rs.getString( "email" );
276         if( map.containsKey( email ) )
277         {
278           ( ( NewsletterVO )map.get( email ) ).appendDetailMsg( getMessageDetail( vo.getLanguageCd(), vo.getTitel(), rs.getString( "description" ), rs.getString( "guard_nr" ), rs.getString( "reference" ), rs.getInt( "state_id" ) ) );
279         }
280         else
281         {
282           vo = new NewsletterVO();
283           vo.setInterceptionId( rs.getInt( "interception_id" ) );
284           vo.setDossierId( rs.getInt( "dossier_id" ) );
285 
286           clob = ( CLOB ) ( ( OracleResultSet )rs ).getClob( "titel" );
287           if( clob != null )
288           {
289             buf = new char[ ( int )clob.length() ];
290 
291             clob.getCharacterStream().read( buf );
292             vo.setTitel( new String( buf ).trim() );
293           }
294           vo.setLanguageCd( getLanguageText( rs.getInt( "language_cd" ) ) );
295 
296           vo.setDetailMsg( getMessageDetail( vo.getLanguageCd(), vo.getTitel(), rs.getString( "description" ), rs.getString( "guard_nr" ), rs.getString( "reference" ), rs.getInt( "state_id" ) ) );
297 
298           vo.setEmail( rs.getString( "email" ) );
299           map.put( vo.getEmail(), vo );
300         }
301       }
302     }
303     finally
304     {
305       DBHelper.getInstance().close( con, ps );
306     }
307     return map.values();
308   }
309 
310   /**
311    * Diese Methode aktualisiert den Status einer Beanstandung wenn der Status kleiner ist als der
312    * übergebene Status. Ist der Status gleich wird die State_Id nicht verändert.
313    * @param interceptionId int Nummer zum selektieren des Datensatzes
314    * @param state int Endwert des Status nach dem Update
315    * @throws SQLException Unerwarteter SQL-Fehler
316    * @return boolean true = Status wurde aktualisiert, false = Status konnte nicht aktualisiert werden
317    */
318   public boolean update( int interceptionId, int state ) throws SQLException
319   {
320     Connection con = null;
321     PreparedStatement ps = null;
322     String query = null;
323     boolean modified = false;
324 
325     try
326     {
327       // Stati sind: 1 = Neu, 2 = Gelesen, 3 = Geantwortet, 4 = Abgeschlossen
328       query = "UPDATE edossier.interception SET state_id = state_id + 1, changed_dat = sysdate " +
329           "WHERE interception_id = ? " +
330           "AND state_id < ? " +
331           "AND state_id != 4";
332 
333       con = DBHelper.getInstance().getEsolutionConnection();
334       ps = con.prepareStatement( query );
335       ps.setInt( 1, interceptionId );
336       ps.setInt( 2, state );
337       ps.executeUpdate();
338       modified = true;
339     }
340     finally
341     {
342       DBHelper.getInstance().close( con, ps );
343     }
344     return modified;
345   }
346 
347   /**
348    * Löscht einen Datensatz aus der Tabelle INTERCEPTION
349    * @param interceptionId int PK FehlerId
350    * @return int Anzahl gelöschter Datensätze
351    * @exception SQLException Unerwarteter SQL-Fehler
352    */
353   public int delete( int interceptionId ) throws SQLException
354   {
355     Connection con = null;
356     PreparedStatement ps = null;
357 
358     try
359     {
360       con = DBHelper.getInstance().getEsolutionConnection();
361 
362       ps = con.prepareStatement(
363           "DELETE FROM edossier.interception where interception_id = ? "
364           );
365       ps.setInt( 1, interceptionId );
366 
367       return ps.executeUpdate();
368     }
369     finally
370     {
371       DBHelper.getInstance().close( con, ps );
372     }
373   }
374 
375   //---------------------------------------------------------------------------
376   // private methods
377   //---------------------------------------------------------------------------
378 
379   /**
380    * Gibt das SQL-Statement für den select aller Attribute ohne where-Bedingngung zurück
381    * @return String Select-Statement
382    */
383   private String getSelectStatement()
384   {
385     return
386         ( "select interception_id, dossier_id, state_id, titel, text, pdf, letter_dat, " +
387           "answer_dat, expire_dat, event_idat, ma_name, ma_vname, ma_email, ma_tel, " +
388           "created_dat, changed_dat " +
389           "from edossier.interception " );
390   }
391 
392   /**
393    * Füllt einen Datensatz anhand des ResultSet
394    * @param rs ResultSet Gültiges resultSet
395    * @return DossierVO Value-Object
396    * @exception Exception Unerwarteter SQL-Fehler oder Konvertierungsfehler
397    */
398   private InterceptionVO fillVO( ResultSet rs ) throws Exception
399   {
400     InterceptionVO interceptionVO = new InterceptionVO();
401     Blob pdfBlob = null;
402     CLOB clob = null;
403     char[] buf = null;
404 
405     interceptionVO.setInterceptionId( rs.getInt( "interception_id" ) );
406     interceptionVO.setDossierId( rs.getInt( "dossier_id" ) );
407     interceptionVO.setStateId( rs.getInt( "state_id" ) );
408 
409     clob = ( CLOB ) ( ( OracleResultSet )rs ).getClob( "titel" );
410     if( clob != null )
411     {
412       buf = new char[ ( int )clob.length() ];
413 
414       clob.getCharacterStream().read( buf );
415       interceptionVO.setTitel( new String( buf ).trim() );
416     }
417 
418     clob = ( CLOB ) ( ( OracleResultSet )rs ).getClob( "text" );
419     if( clob != null )
420     {
421       buf = new char[ ( int )clob.length() ];
422 
423       clob.getCharacterStream().read( buf );
424       interceptionVO.setText( new String( buf ).trim() );
425     }
426 
427     interceptionVO.setText( interceptionVO.getText().replaceAll( "http://wdl.ige.ch", "<a href=\"http://wdl.ige.ch\" target=\"_blank\">http://wdl.ige.ch</a>" ) );
428 
429     Object obj = rs.getObject( "pdf" );
430     if( obj == null )
431     {
432       interceptionVO.setPdf( null );
433     }
434     else
435     {
436       // pdf-Blob lesen
437       pdfBlob = ( java.sql.Blob )rs.getBlob( "pdf" );
438       interceptionVO.setPdf( pdfBlob.getBytes( new Long( "1" ).longValue(), new Long( pdfBlob.length() ).intValue() ) );
439     }
440 
441     interceptionVO.setLetterDat( rs.getDate( "letter_dat" ) );
442     interceptionVO.setAnswerDat( rs.getDate( "answer_dat" ) );
443     interceptionVO.setExpireDat( rs.getDate( "expire_dat" ) );
444     interceptionVO.setEventIdat( rs.getInt( "event_idat" ) );
445     interceptionVO.setMaName( rs.getString( "ma_name" ) );
446     interceptionVO.setMaVname( rs.getString( "ma_vname" ) );
447     interceptionVO.setMaEmail( rs.getString( "ma_email" ) );
448     interceptionVO.setMaTel( rs.getString( "ma_tel" ) );
449     interceptionVO.setCreatedDat( rs.getDate( "created_dat" ) );
450     interceptionVO.setChangedDat( rs.getDate( "changed_dat" ) );
451 
452     return interceptionVO;
453   }
454 
455   /**
456    * Diese Methode verarbeitet die Informationen im Newsletter-Value-Object zu einem String der zurückgegeben wird.
457    * @param languageCd String Sprachcode
458    * @param titel String Titel
459    * @param description String Beschreibung
460    * @param guardNr String Gesuchs-Nummer
461    * @param reference String Anmelde-Referenz
462    * @param stateId int Status-ID
463    * @throws Exception Unerwarteter Fehler
464    * @return String Detail-Informationen
465    */
466   private String getMessageDetail( String languageCd, String titel, String description, String guardNr, String reference, int stateId ) throws Exception
467   {
468     Properties webProps = ServerProperties.loadWebProperties( languageCd );
469     StringBuffer sbuf = new StringBuffer();
470 
471     sbuf.append( webProps.getProperty( "mail.newsletter.body.title" ) );
472     sbuf.append( titel.replaceAll( "<br>", " " ) );
473     sbuf.append( webProps.getProperty( "mail.newsletter.body.description" ) );
474     sbuf.append( description );
475     sbuf.append( webProps.getProperty( "mail.newsletter.body.guardNr" ) );
476     sbuf.append( guardNr );
477     sbuf.append( webProps.getProperty( "mail.newsletter.body.reference" ) );
478     sbuf.append( reference );
479     sbuf.append( webProps.getProperty( "mail.newsletter.body.state" ) );
480     sbuf.append( getStateText( stateId, webProps ) );
481 
482     return sbuf.toString();
483   }
484 
485   /**
486    * Diese Methode gibt den Status in Textform zurück.
487    * @param state_id int Status-Nummer
488    * @param webProps Properties Referenz auf Resourcen-Datei
489    * @throws Exception Unerwarteter Fehler
490    * @return String Text des Status
491    */
492   private String getStateText( int state_id, Properties webProps ) throws Exception
493   {
494     switch( state_id )
495     {
496       case 2:
497         return webProps.getProperty( "table.interception.stateid.2" ).toUpperCase();
498       case 3:
499         return webProps.getProperty( "table.interception.stateid.3" ).toUpperCase();
500       case 4:
501         return webProps.getProperty( "table.interception.stateid.4" ).toUpperCase();
502       default:
503         return webProps.getProperty( "table.interception.stateid.1" ).toUpperCase();
504     }
505   }
506 
507   /**
508    * Diese Methode macht das Mapping für den Sprachcode und gibt den jeweiligen Text zurück.
509    * @param languageCd int Sprach-Code
510    * @return String Text des Sprach-Codes
511    */
512   private String getLanguageText( int languageCd )
513   {
514     switch( languageCd )
515     {
516       case 2:
517         return "fr";
518         /** @todo Sprach-Code die noch kein Resourcen-Datei haben */
519 //      case 3:
520 //        return "it";
521 //      case 4:
522 //        return "en";
523       default:
524         return "de";
525     }
526   }
527 
528   /**
529    * Diese Methode wird verwendet um nachträglich PDF-Dateien in der Datenbank zu speichern.
530    * @param interceptionVO InterceptionVO Value-Object
531    * @throws SQLException Unerwartete SQL-Exception
532    */
533   public void insertPdf( InterceptionVO interceptionVO ) throws SQLException
534   {
535     Connection con = null;
536     PreparedStatement ps = null;
537     ResultSet rs;
538     String queryBlobSelect = null;
539     String queryBlobUpdate = null;
540 
541     try
542     {
543       queryBlobSelect = "SELECT pdf " +
544           "FROM edossier.interception " +
545           "WHERE interception_id = ? " +
546           "FOR UPDATE";
547 
548       con = DBHelper.getInstance().getEsolutionConnection();
549       ps = con.prepareStatement( queryBlobSelect );
550       con.setAutoCommit( false );
551       ps.setInt( 1, interceptionVO.getInterceptionId() );
552       rs = ps.executeQuery();
553       rs.next();
554 
555       oracle.sql.BLOB dbBlob = ( oracle.sql.BLOB )rs.getBlob( 1 );
556       // update blob
557 
558       ps.close(); // Statement schliessen, da mehrere ausgeführt werden.
559 
560       queryBlobUpdate = "UPDATE edossier.interception " +
561           "SET pdf = ? " +
562           "WHERE interception_id = ?";
563 
564       ps = con.prepareStatement( queryBlobUpdate );
565 
566       dbBlob.putBytes( 1, interceptionVO.getPdf() );
567       ps.setInt( 2, interceptionVO.getInterceptionId() );
568 
569       ps.setBlob( 1, dbBlob );
570       con.commit();
571       ps.executeUpdate();
572       ps.close(); // Statement schliessen, da mehrere ausgeführt werden.
573     }
574     finally
575     {
576       DBHelper.getInstance().close( con, ps );
577     }
578   }
579 }
580