Java > Open Source Codes > org > jahia > data > containers > ContainerFilterBean


1 //
2 // ____.
3 // __/\ ______| |__/\. _______
4 // __ .____| | \ | +----+ \
5 // _______| /--| | | - \ _ | : - \_________
6 // \\______: :---| : : | : | \________>
7 // |__\---\_____________:______: :____|____:_____\
8 // /_____|
9 //
10 // . . . i n j a h i a w e t r u s t . . .
11 //
12 //
13 //
14 //
15 //
16 // 27.05.2002 NK Creation
17
18
19 package org.jahia.data.containers;
20
21
22 import java.io.Serializable ;
23 import java.sql.Connection ;
24 import java.sql.PreparedStatement ;
25 import java.sql.ResultSet ;
26 import java.sql.SQLException ;
27 import java.sql.Statement ;
28 import java.util.ArrayList ;
29 import java.util.BitSet ;
30 import java.util.Calendar ;
31 import java.util.Date ;
32 import java.util.HashMap ;
33 import java.util.Iterator ;
34 import java.util.Map ;
35 import java.util.TimeZone ;
36 import java.util.Vector ;
37
38 import org.jahia.data.fields.JahiaField;
39 import org.jahia.exceptions.JahiaException;
40 import org.jahia.services.fields.ContentField;
41 import org.jahia.services.version.EntryLoadRequest;
42 import org.jahia.utils.JahiaTools;
43
44
45
46 /**
47  * Jahia Standard container filtering.
48  *
49  *
50  * @see FilterClause
51  * @see ContainerFilters
52  * @see JahiaContainerSet
53  * @author Khue Nguyen <a HREF="mailto:khue@jahia.org">khue@jahia.org</a>
54  */
55 public class ContainerFilterBean implements Serializable , ContainerFilterInterface {
56
57     private static org.apache.log4j.Logger logger =
58         org.apache.log4j.Logger.getLogger(ContainerFilterBean.class);
59
60     /**
61      * "=" Comparator
62      */
63     public static final String COMP_EQUAL = "=";
64     /**
65      * "<" Comparator
66      */
67     public static final String COMP_SMALLER = "<";
68     /**
69      * "<=" Comparator
70      */
71     public static final String COMP_SMALLER_OR_EQUAL = "<=";
72     /**
73      * ">=" Comparator
74      */
75     public static final String COMP_BIGGER_OR_EQUAL = ">=";
76     /**
77      * ">" Comparator
78      */
79     public static final String COMP_BIGGER = ">";
80
81     public static final String COMP_NOT_EQUAL = "<>";
82
83     public static final String COMP_NOTNULL = "NOTNULL";
84
85     public static final String COMP_ISNULL = "ISNULL";
86
87     public static final String COMP_STARTS_WITH = "STARTS_WITH";
88
89     private static final String CLASS_NAME = ContainerFilterBean.class.getName();
90
91
92     public static final String FIELD_ID = "b.id_jahia_fields_data";
93     public static final String FIELD_VALUE = "b.value_jahia_fields_data";
94     public static final String FIELD_VERSION_ID = "b.version_id";
95     public static final String FIELD_WORKFLOW_STATE = "b.workflow_state";
96     public static final String FIELD_LANGUAGE_CODE = "b.language_code";
97
98     protected String fieldName;
99
100     protected boolean numberFiltering = false;
101
102     protected String numberFormat = NumberFormats.LONG_FORMAT;
103
104     protected boolean multipleFieldValue = false;
105
106     /** The list of FilterClause bean **/
107     protected Vector clauses = new Vector ();
108
109     protected EntryLoadRequest entryLoadRequest = EntryLoadRequest.CURRENT;
110
111     protected ContainerFilters containerFilters = null;
112
113     //--------------------------------------------------------------------------
114 /**
115      * Constructor
116      *
117      * @param String fieldName, the field name of the field on which to apply filtering.
118      * @deprecated use ContainerFilterBean(String fieldName, EntryLoadRequest entryLoadRequest)
119      */
120     public ContainerFilterBean(String fieldName){
121         this(fieldName,null);
122     }
123
124     //--------------------------------------------------------------------------
125 /**
126      * Constructor
127      *
128      * @param String fieldName, the field name of the field on which to apply filtering.
129      * @param entryLoadRequest
130      */
131     public ContainerFilterBean(String fieldName, EntryLoadRequest entryLoadRequest){
132         this(fieldName,false,entryLoadRequest);
133     }
134
135     //--------------------------------------------------------------------------
136 /**
137      * Constructor
138      *
139      * @param String fieldName, the field name of the field on which to apply filtering.
140      * @param boolean numberFiltering, if true force to convert filed value to long representation
141      * @deprecated use ContainerFilterBean(String fieldName, EntryLoadRequest entryLoadRequest)
142      */
143     public ContainerFilterBean(String fieldName, boolean numberFiltering){
144         this(fieldName,numberFiltering,null);
145     }
146
147     //--------------------------------------------------------------------------
148 /**
149      * Constructor
150      *
151      * @param String fieldName, the field name of the field on which to apply filtering.
152      * @param boolean numberFiltering, if true force to convert filed value to long representation
153      * @param entryLoadRequest
154      */
155     public ContainerFilterBean(String fieldName, boolean numberFiltering, EntryLoadRequest entryLoadRequest){
156         this(fieldName,numberFiltering,false,entryLoadRequest);
157     }
158
159     //--------------------------------------------------------------------------
160 /**
161      * Constructor
162      *
163      * @param String fieldName, the field name of the field on which to apply filtering.
164      * @param boolean numberFiltering, if true force to convert filed value to long representation
165      * @param multipleFieldValue
166      * @param entryLoadRequest
167      */
168     public ContainerFilterBean(String fieldName, boolean numberFiltering,
169                                boolean multipleFieldValue,
170                                EntryLoadRequest entryLoadRequest){
171         this(fieldName,numberFiltering,null,multipleFieldValue,entryLoadRequest);
172     }
173
174     /**
175      *
176      * @param fieldName
177      * @param boolean numberFiltering, if true force to convert filed value to number representation
178      * @param numberFormat, only used if numberSort is true. If null, the format used is NumberFormat.LONG_FORMAT
179      * @param multipleFieldValue
180      * @param entryLoadRequest
181      */
182     public ContainerFilterBean(String fieldName, boolean numberFiltering,
183                                String numberFormat,
184                                boolean multipleFieldValue,
185                                EntryLoadRequest entryLoadRequest){
186         this.fieldName = fieldName;
187         this.numberFiltering = numberFiltering;
188         this.multipleFieldValue = multipleFieldValue;
189         if ( NumberFormats.isValidFormat(numberFormat) ){
190             this.numberFormat = numberFormat;
191         }
192         if ( entryLoadRequest != null ){
193             this.entryLoadRequest = entryLoadRequest;
194         }
195         logger.debug("Created with field name : " + fieldName);
196     }
197
198     //--------------------------------------------------------------------------
199 /**
200      * Add a simple comparison clause with a single value
201      *
202      * <pre>
203      * I.E : comparator = ContainerFilterBean.COMP_BIGGER (>)
204      * value = '1'
205      *
206      * will be used to generate the WHERE clause :
207      *
208      * WHERE (fieldvalue>'1')
209      *
210      * </pre>
211      *
212      * @param String comparator, the comparator used to compareNumber the field value.
213      * @param String value, a single value
214      */
215     public void addClause(String comparator, String value){
216
217         if ( (value == null) || !checkComparator(comparator) )
218             return;
219
220         FilterClause fClause = new FilterClause(comparator,value);
221
222         if ( fClause.isValid() ){
223             clauses.add(fClause);
224         }
225     }
226
227     //--------------------------------------------------------------------------
228 /**
229      * Add a simple comparison clause with multiple values
230      *
231      * An OR comparison is added between each clause.
232      *
233      * <pre>
234      * I.E : comparator = ContainerFilterBean.COMP_EQUAL (=)
235      * values = {'1','3','1000'}
236      *
237      * will be used to generate the WHERE clause :
238      *
239      * WHERE (fieldvalue='1' OR fieldvalue='3' OR fieldvalue='1000')
240      *
241      * </pre>
242      *
243      * @param String comparator, the comparator used to compareNumber the field value with each value of the values array.
244      * @param String[] values, an array of values as String
245      */
246     public void addClause(String comparator, String [] values){
247
248         if ( values == null || (values.length == 0) || !checkComparator(comparator) )
249             return;
250
251         FilterClause fClause = new FilterClause(comparator,values);
252
253         if ( fClause.isValid() ){
254             clauses.add(fClause);
255         }
256     }
257
258     //--------------------------------------------------------------------------
259 /**
260      * Add a simple equality comparison clause with a single value
261      *
262      * <pre>
263      * I.E :
264      * value = '1'
265      *
266      * will be used to generate the WHERE clause :
267      *
268      * WHERE (fieldvalue='1')
269      *
270      * </pre>
271      *
272      * @param String value, a single value
273      */
274     public void addEqualClause(String value){
275
276         if ( (value == null) )
277             return;
278
279         FilterClause fClause = new FilterClause(COMP_EQUAL,value);
280
281         if ( fClause.isValid() ){
282             clauses.add(fClause);
283         }
284     }
285
286     //--------------------------------------------------------------------------
287 /**
288      * Add a simple equality comparison clause with multiple value
289      *
290      * <pre>
291      * I.E :
292      * values = {'1','3','1000'}
293      *
294      * will be used to generate the WHERE clause :
295      *
296      * WHERE (fieldvalue='1' OR fieldvalue='3' OR fieldvalue='1000')
297      *
298      * </pre>
299      *
300      * @param String value, a single value
301      */
302     public void addEqualClause(String [] values){
303
304         if ( values == null || (values.length == 0) )
305             return;
306
307         FilterClause fClause = new FilterClause(COMP_EQUAL,values);
308
309         if ( fClause.isValid() ){
310             clauses.add(fClause);
311         }
312     }
313
314     //--------------------------------------------------------------------------
315 /**
316      * Constructs a range clause matching values between
317      * <code>lowerVal</code> and <code>upperVal</code>.
318      *
319      * <pre>
320      * I.E : lowerComp = ContainerFilterBean.COMP_BIGGER_OR_EQUAL (>=)
321      * upperComp = ContainerFilterBean.COMP_SMALLER (<)
322      * lowerVal = '1'
323      * upperVal = '1000'
324      *
325      * will be used to generate the WHERE clause :
326      *
327      * WHERE (fieldvalue>='1' AND fielValue<'10001')
328      *
329      * </pre>
330      *
331      * @param String lowerComp, the lower comparator
332      * @param String upperComp, the upper comparator
333      * @param String lowerVal, the lower value
334      * @param String upperVal, the upper value
335      */
336     public void addRangeClause( String lowerComp,
337                                 String upperComp,
338                                 String lowerVal,
339                                 String upperVal) {
340
341         if ( lowerVal == null
342                 || upperVal == null
343                 || !checkComparator(lowerComp)
344                 || !checkComparator(upperComp) ){
345
346             return;
347         }
348
349         FilterClause fClause = new FilterClause(lowerComp,
350                                                 upperComp,
351                                                 lowerVal,
352                                                 upperVal);
353
354         if ( fClause.isValid() ){
355             clauses.add(fClause);
356         }
357     }
358
359     //--------------------------------------------------------------------------
360 /**
361      * Constructs a range clause matching date values between
362      * <code>lower</code> and <code>upper</code>.
363      *
364      * Available only with field of type JahiaDateField ( date field ).
365      *
366      * <pre>
367      * I.E : lowerComp = ContainerFilterBean.COMP_SMALLER (<)
368      * upperComp = ContainerFilterBean.COMP_BIGGER_OR_EQUAL (>=)
369      * lowerVal = '1020038400100' ( long representation )
370      * upperVal = '1020038400000' ( long representation )
371      *
372      * will be used to generate the WHERE clause :
373      *
374      * WHERE (fieldvalue<'1020038400100' AND fielValue>='1020038400000')
375      *
376      * </pre>
377      *
378      * @param String lowerComp, the lower comparator
379      * @param String upperComp, the upper comparator
380      * @param Date lowerVal, the lower date
381      * @param Date upperVal, the upper date
382      */
383     public void addDateClause( String lowerComp,
384                                 String upperComp,
385                                 Date lowerVal,
386                                 Date upperVal) {
387
388         if ( lowerVal == null
389                 || upperVal == null
390                 || !checkComparator(lowerComp)
391                 || !checkComparator(upperComp) ){
392             return;
393         }
394
395         addRangeClause(lowerComp,
396                        upperComp,
397                        String.valueOf(lowerVal.getTime()),
398                        String.valueOf(upperVal.getTime())
399                        );
400     }
401
402     //--------------------------------------------------------------------------
403 /**
404      * Constructs a range clause matching date values between
405      * <code>lower</code> and <code>upper</code>.
406      *
407      * Available only with field of type JahiaDateField ( date field ).
408      *
409      * <pre>
410      * I.E : lowerComp = ContainerFilterBean.COMP_SMALLER (<)
411      * upperComp = ContainerFilterBean.COMP_BIGGER_OR_EQUAL (>=)
412      * lowerVal = '1020038400100' ( long representation )
413      * upperVal = '1020038400000' ( long representation )
414      *
415      * will be used to generate the WHERE clause :
416      *
417      * WHERE (fieldvalue<'1020038400100' AND fielValue>='1020038400000')
418      *
419      * </pre>
420      *
421      * @param String lowerComp, the lower comparator
422      * @param String upperComp, the upper comparator
423      * @param long lowerVal, the lower date
424      * @param long upperVal, the upper date
425      */
426     public void addDateClause( String lowerComp,
427                                 String upperComp,
428                                 long lowerVal,
429                                 long upperVal) {
430
431         addRangeClause(lowerComp,
432                        upperComp,
433                        String.valueOf(lowerVal),
434                        String.valueOf(upperVal)
435                        );
436     }
437
438     //--------------------------------------------------------------------------
439 /**
440      * Constructs a range clause matching date for X day ago.
441      *
442      * Available only with field of type JahiaDateField ( date field ).
443      *
444      * @param int nbDays
445      */
446     public void addXDayMaxDateClause(int nbDays){
447
448         // Now
449 long nowLong = System.currentTimeMillis();
450
451         addDateClause( COMP_SMALLER_OR_EQUAL,
452                        COMP_BIGGER_OR_EQUAL,
453                        nowLong,
454                        nowLong-(nbDays*24*60*60*1000)
455                        );
456     }
457
458     //--------------------------------------------------------------------------
459 /**
460      * Constructs a range clause matching date values that are in Today date.
461      *
462      * Available only with field of type JahiaDateField ( date field ).
463      *
464      */
465     public void addTodayDateClause(){
466
467         // Now
468 TimeZone tz = TimeZone.getTimeZone("UTC");
469         Calendar cal = Calendar.getInstance(tz);
470         Date nowDate = cal.getTime();
471         long nowLong = nowDate.getTime();
472
473         int year = cal.get(Calendar.YEAR);
474         int mon = cal.get(Calendar.MONTH);
475         int date = cal.get(Calendar.DAY_OF_MONTH);
476         cal.set(year,mon,date,0,0,0);
477         cal.set(Calendar.MILLISECOND,0);
478
479         Date todayDate = cal.getTime();
480         long todayLong = todayDate.getTime();
481
482         // Today
483 addDateClause( COMP_SMALLER_OR_EQUAL,
484                        COMP_BIGGER_OR_EQUAL,
485                        nowLong,
486                        todayLong
487                        );
488     }
489
490     //--------------------------------------------------------------------------
491 /**
492      * Return the field name
493      *
494      * @return String, the field name.
495      */
496     public String getFieldName()
497     {
498         return this.fieldName;
499     }
500
501     //--------------------------------------------------------------------------
502 /**
503      * You can force field values to be corverted to long representation for filtering comparison
504      * Prefer String comparison when possible, because it is faster ( only one DB query needed ).
505      *
506      * @param boolean number filtering status value.
507      */
508     public void setNumberFiltering(boolean val)
509     {
510         this.numberFiltering = val;
511     }
512
513     //--------------------------------------------------------------------------
514 /**
515      * Return the number filtering status.
516      * If true, field values are converted to long representation before filtering comparison
517      *
518      * @return boolean the number filtering status.
519      */
520     public boolean getNumberFiltering()
521     {
522         return this.numberFiltering;
523     }
524
525     //--------------------------------------------------------------------------
526 /**
527      * Return the vector of clauses.
528      *
529      * @return Vector, the list of FilterClause bean.
530      */
531     public Vector getClauses()
532     {
533         return this.clauses;
534     }
535
536     //--------------------------------------------------------------------------
537 /**
538      * Perform filtering.
539      * The expected result is a bit set of matching container ids.
540      *
541      * @param int ctnListID, the container list id
542      * @return BitSet bits, the expected result as a bit set of matching ctn ids,each bit position set to true correspond to matching ctn ids.
543      */
544     public BitSet doFilter(int ctnListID)
545     throws JahiaException
546     {
547         if ( (this.getFieldName()==null)
548               || (this.getFieldName().trim().equals(""))
549               || (this.getClauses()==null)
550               || (this.getClauses().size()==0) )
551         {
552             return null;
553         }
554
555         BitSet result = null;
556
557         if ( !this.numberFiltering )
558         {
559             if ( !this.multipleFieldValue ){
560                result = doQueryValueFiltering(ctnListID);
561             } else {
562                 result = this.doStringFiltering(ctnListID);
563             }
564         } else {
565             result = doNumberValueFiltering(ctnListID);
566         }
567         return result;
568     }
569
570     //--------------------------------------------------------------------------
571 /**
572      * Return the select statement, build with the clauses for all container list of the site.
573      *
574      * @param int ctnListID, the container list id
575      * @return String , the sql statement. Null on error
576      */
577     public String getSelect(int ctnListID)
578     {
579         if ( (this.getFieldName()==null)
580               || (this.getFieldName().trim().equals(""))
581               || (this.getClauses()==null)
582               || (this.getClauses().size()==0) )
583         {
584             return null;
585         }
586
587         StringBuffer buff = new StringBuffer ("SELECT DISTINCT id_jahia_ctn_entries,b.id_jahia_fields_data,b.workflow_state FROM jahia_ctn_entries a, jahia_fields_data b, jahia_fields_def c WHERE listid_jahia_ctn_entries=");
588         buff.append(ctnListID);
589         buff.append(" AND ( a.id_jahia_ctn_entries = b.ctnid_jahia_fields_data AND b.fielddefid_jahia_fields_data = c.id_jahia_fields_def AND c.name_jahia_fields_def='");
590         buff.append(JahiaTools.quote(this.getFieldName()));
591         buff.append("' AND (");
592
593         FilterClause fClause = null;
594         int size = this.clauses.size();
595         for ( int i=0 ; i<size ; i++ )
596         {
597             fClause = (FilterClause)this.clauses.get(i);
598             if ( fClause != null && fClause.isValid() )
599             {
600                 StringBuffer clauseBuff = new StringBuffer ("(");
601                 if ( !fClause.isRangeClause() )
602                 {
603                     String [] values = fClause.getValues();
604                     for (int j=0 ; j<values.length; j++)
605                     {
606                         // TODO : hollis
607 // need to review encode/decode special car
608 String val = values[j];
609
610                         clauseBuff.append("(");
611                         clauseBuff.append(buildMultilangAndWorlflowQuery(this.entryLoadRequest));
612                         clauseBuff.append(" AND ");
613                         //SCSE: add STARTS_WITH comparator
614 if (COMP_STARTS_WITH.equals(fClause.getComp()))
615                         {
616                           String lowerValue = val.toLowerCase();
617                           boolean considerUmlauts = lowerValue.length() == 1 && ("a".equals(lowerValue) || "o".equals(lowerValue) || "u".equals(lowerValue) || "s".equals(lowerValue));
618                           if (considerUmlauts)
619                             clauseBuff.append("(");
620                           clauseBuff.append(" lower(" + FIELD_VALUE + ") like '");
621                           clauseBuff.append(JahiaTools.quote(lowerValue));
622                           clauseBuff.append("%'");
623                           if (considerUmlauts)
624                           {
625                             clauseBuff.append(" OR lower(" + FIELD_VALUE + ") like '");
626                             switch (lowerValue.charAt(0))
627                             {
628                               case 'a':
629                                 clauseBuff.append("ä");
630                                 break;
631                               case 'o':
632                                 clauseBuff.append("ö");
633                                 break;
634                               case 'u':
635                                 clauseBuff.append("ü");
636                                 break;
637                               case 's':
638                                 clauseBuff.append("ß");
639                                 break;
640                               default:
641                                 throw new IllegalArgumentException (
642                                   "Unknown char for umlaut substitution '" + val.charAt(0)
643                                     + "'");
644                             }
645                             clauseBuff.append("%')");
646                           }
647                         }
648                         else
649                         {
650                           clauseBuff.append(FIELD_VALUE);
651                           clauseBuff.append(fClause.getComp());
652                           clauseBuff.append("'");
653                           clauseBuff.append(JahiaTools.quote(val));
654                           clauseBuff.append("'");
655                         }
656                         clauseBuff.append(")");
657                         if ( j<(values.length-1) ){
658                             clauseBuff.append(" OR ");
659                         }
660                     }
661                     clauseBuff.append(")");
662                 } else {
663
664                     clauseBuff.append(buildMultilangAndWorlflowQuery(this.entryLoadRequest));
665                     clauseBuff.append(" AND ");
666                     clauseBuff.append(FIELD_VALUE);
667                     clauseBuff.append(fClause.getLowerComp());
668                     clauseBuff.append("'");
669                     clauseBuff.append(JahiaTools.quote(fClause.getLowerValue()));
670                     clauseBuff.append("' AND ");
671                     clauseBuff.append(FIELD_VALUE);
672                     clauseBuff.append(fClause.getUpperComp());
673                     clauseBuff.append("'");
674                     clauseBuff.append(JahiaTools.quote(fClause.getUpperValue()));
675                     clauseBuff.append("')");
676                 }
677                 buff.append(clauseBuff.toString());
678                 if ( i<size-1 )
679                 {
680                     buff.append(" OR ");
681                 }
682             }
683         }
684         buff.append(")) ORDER BY ");
685         buff.append(ContainerFilterBean.FIELD_ID);
686         buff.append(",");
687         buff.append(ContainerFilterBean.FIELD_WORKFLOW_STATE);
688
689         logger.debug("field filter query : " + buff.toString());
690
691         return buff.toString();
692     }
693
694     //--------------------------------------------------------------------------
695 /**
696      * Set reference to a containerFilters
697      *
698      * @return
699      * @throws JahiaException
700      */
701     public void setContainerFilters(ContainerFilters containerFilters){
702         this.containerFilters = containerFilters;
703     }
704
705     //--------------------------------------------------------------------------
706 /**
707      * Perform filtering for TEXT like fields.
708      *
709      * The expected result is a bit set of matching container ids.
710      *
711      * @param int ctnListID, the container list id
712      * @return BitSet bits, the expected result as a bit set of matching ctn ids,each bit position set to true correspond to matching ctn ids.
713      */
714     private BitSet doQueryValueFiltering(int ctnListID)
715     throws JahiaException
716     {
717         String fieldFilterQuery = getSelect(ctnListID);
718         if ( fieldFilterQuery == null && !fieldFilterQuery.trim().equals("") ){
719             return null;
720         }
721
722         BitSet bits = new BitSet ();
723
724         Connection dbConn = null;
725         Statement stmt = null;
726         ResultSet rs = null;
727
728         ArrayList deletedCtns = getDeletedContainers(ctnListID);
729         ArrayList stagingFields = this.getStagingFields(ctnListID);
730         HashMap maps = new HashMap ();
731
732         try
733         {
734             dbConn = org.jahia.services.database.ConnectionDispenser.getConnection();
735             stmt = dbConn.createStatement();
736             rs = stmt.executeQuery( fieldFilterQuery );
737
738             while (rs.next())
739             {
740                 int ctnID = rs.getInt(1);
741                 int fieldID = rs.getInt(2);
742                 int workflowState = rs.getInt(3);
743                 if ( this.entryLoadRequest.isCurrent()
744                      || !deletedCtns.contains(new Integer (ctnID))){
745                     if ( workflowState > EntryLoadRequest.ACTIVE_WORKFLOW_STATE ){
746                         workflowState = EntryLoadRequest.STAGING_WORKFLOW_STATE;
747                     }
748                     TempField aField = new TempField(fieldID,ctnID,0,workflowState,"","");
749                     String key = fieldID + "_" + workflowState;
750                     maps.put(key,aField);
751                 }
752             }
753         }
754         catch (SQLException se)
755         {
756             logger.error("Error in doStringValueFiltering : ", se);
757         } finally {
758
759             closeStatement (stmt);
760         }
761
762         int size = maps.size();
763         Iterator iterator = maps.values().iterator();
764         while ( iterator.hasNext() ){
765             TempField aField = (TempField)iterator.next();
766             if ( this.entryLoadRequest.isCurrent() ){
767                 bits.set(aField.ctnID);
768             } else if ( this.entryLoadRequest.isStaging()
769                        && aField.workflowState > EntryLoadRequest.ACTIVE_WORKFLOW_STATE ){
770                 bits.set(aField.ctnID);
771             } else if ( aField.workflowState == EntryLoadRequest.ACTIVE_WORKFLOW_STATE
772                  && !stagingFields.contains(new Integer (aField.id))){
773                 bits.set(aField.ctnID);
774             }
775         }
776         return bits;
777     }
778
779     //--------------------------------------------------------------------------
780 /**
781      * Perform filtering for NUMBER like fields.
782      * Container field values are loaded and converted to long representation before
783      * filtering comparison.
784      *
785      * The expected result is a bit set of matching container ids.
786      *
787      * @param int ctnListID, the container list id
788      * @return BitSet bits, the expected result as a bit set of matching ctn ids,each bit position set to true correspond to matching ctn ids.
789      */
790     private BitSet doNumberValueFiltering(int ctnListID)
791     throws JahiaException
792     {
793
794         BitSet result = new BitSet ();
795
796         if ( (this.getFieldName()==null)
797               || (this.getFieldName().trim().equals(""))
798               || (this.getClauses()==null)
799               || (this.getClauses().size()==0) )
800         {
801             return null;
802         }
803
804         Map datas = getFieldValues(ctnListID, fieldName);
805
806         if ( datas == null || datas.size()==0 ){
807             return result;
808         }
809
810         FilterClause fClause = null;
811         Iterator keys = datas.keySet().iterator();
812         Integer I = null;
813         String S = null;
814
815         while ( keys.hasNext() )
816         {
817             I = (Integer )keys.next();
818             S = (String )datas.get(I);
819             boolean match = false;
820             int size = this.clauses.size();
821             int i = 0;
822             while ( (i<size) && !match )
823             {
824                 fClause = (FilterClause)this.clauses.get(i);
825                 if ( fClause != null && fClause.isValid() )
826                 {
827                     match = fClause.compareNumber(S,this.numberFormat);
828                 }
829                 i++;
830             }
831             if ( match )
832             {
833                 result.set(I.intValue());
834             }
835         }
836
837         return result;
838     }
839
840     //--------------------------------------------------------------------------
841 /**
842      * Perform filtering by loading field values in memories first.
843      *
844      * The expected result is a bit set of matching container ids.
845      *
846      * @param int ctnListID, the container list id
847      * @return BitSet bits, the expected result as a bit set of matching ctn ids,each bit position set to true correspond to matching ctn ids.
848      */
849     private BitSet doStringFiltering(int ctnListID)
850     throws JahiaException
851     {
852
853         BitSet result = new BitSet ();
854
855         if ( (this.getFieldName()==null)
856               || (this.getFieldName().trim().equals(""))
857               || (this.getClauses()==null)
858               || (this.getClauses().size()==0) )
859         {
860             return null;
861         }
862
863         Map datas = getFieldValues(ctnListID, fieldName);
864
865         if ( datas == null || datas.size()==0 ){
866             return result;
867         }
868
869         FilterClause fClause = null;
870         Iterator keys = datas.keySet().iterator();
871         Integer I = null;
872         String S = null;
873
874         while ( keys.hasNext() )
875         {
876             I = (Integer )keys.next();
877             S = (String )datas.get(I);
878             boolean match = false;
879             String val = "";
880             String [] vals = JahiaTools.getTokens(S,JahiaField.MULTIPLE_VALUES_SEP);
881             int nbVals = vals.length;
882             int i = 0;
883             while ( (i<nbVals) && !match ){
884                 val = vals[i];
885                 int size = this.clauses.size();
886                 int j = 0;
887                 while ( (j<size) && !match )
888                 {
889                     fClause = (FilterClause)this.clauses.get(j);
890                     if ( fClause != null && fClause.isValid() )
891                     {
892                         match = fClause.compare(val);
893                     }
894                     j++;
895                 }
896                 if ( match )
897                 {
898                     result.set(I.intValue());
899                 }
900                 i++;
901             }
902         }
903
904         return result;
905     }
906
907     //--------------------------------------------------------------------------
908 /**
909      * Load an hashtable of pair/value (ctnID,fieldValue) for a given ctnlist and a given fieldName.
910      *
911      *
912      * @param ctnListID the container list id
913      * @param fldName the fieldName
914      * @return map with field values
915      */
916     private Map getFieldValues(int ctnListID, String fldName)
917     throws JahiaException
918     {
919         ArrayList deletedCtns = getDeletedContainers(ctnListID);
920         ArrayList stagingFields = this.getStagingFields(ctnListID);
921
922         StringBuffer buff = new StringBuffer (256);
923         buff
924           .append(
925             "SELECT DISTINCT ctnid_jahia_fields_data,b.id_jahia_fields_data,b.value_jahia_fields_data,b.workflow_state FROM jahia_ctn_entries a, jahia_fields_data b, jahia_fields_def c WHERE listid_jahia_ctn_entries=?"
926               + " AND ( a.id_jahia_ctn_entries = b.ctnid_jahia_fields_data AND b.fielddefid_jahia_fields_data = c.id_jahia_fields_def AND c.name_jahia_fields_def=?) AND (")
927           .append(buildMultilangAndWorlflowQuery(this.entryLoadRequest)).append(
928             ") ORDER BY " + ContainerFilterBean.FIELD_ID + ","
929               + ContainerFilterBean.FIELD_WORKFLOW_STATE);
930
931         Connection dbConn = null;
932         PreparedStatement stmt = null;
933         ResultSet rs = null;
934
935         Map datas = new HashMap ();
936         HashMap maps = new HashMap ();
937         try
938         {
939             dbConn = org.jahia.services.database.ConnectionDispenser.getConnection();
940             stmt = dbConn.prepareStatement(buff.toString());
941             stmt.setInt(1, ctnListID);
942             stmt.setString(2, fldName);
943             rs = stmt.executeQuery();
944
945             while (rs.next())
946             {
947                 int ctnID = rs.getInt(1);
948                 int fieldID = rs.getInt(2);
949                 String fieldValue = rs.getString(3);
950                 int workflowState = rs.getInt(4);
951
952                 if ( this.entryLoadRequest.isCurrent()
953                      || !deletedCtns.contains(new Integer (ctnID))){
954                     if ( workflowState > EntryLoadRequest.ACTIVE_WORKFLOW_STATE ){
955                         workflowState = EntryLoadRequest.STAGING_WORKFLOW_STATE;
956                     }
957                     TempField aField = new TempField(fieldID,ctnID,0,workflowState,"",fieldValue);
958                     String key = fieldID + "_" + workflowState;
959                     maps.put(key,aField);
960                 }
961             }
962         }
963         catch (SQLException se)
964         {
965             logger.error("Error in getFieldValues : ", se);
966         } finally {
967
968             closeStatement (stmt);
969         }
970
971         Iterator iterator = maps.values().iterator();
972         while ( iterator.hasNext() ){
973             TempField aField = (TempField)iterator.next();
974             if ( this.entryLoadRequest.isCurrent() ){
975                 datas.put(new Integer (aField.ctnID),aField.value);
976             } else if ( this.entryLoadRequest.isStaging()
977                        && aField.workflowState > EntryLoadRequest.ACTIVE_WORKFLOW_STATE ){
978                 datas.put(new Integer (aField.ctnID),aField.value);
979             } else if ( aField.workflowState == EntryLoadRequest.ACTIVE_WORKFLOW_STATE
980                  && !stagingFields.contains(new Integer (aField.id))){
981                 datas.put(new Integer (aField.ctnID),aField.value);
982             }
983         }
984
985         return datas;
986     }
987
988     /**
989     *
990     * @return
991     */
992    static public String buildMultilangAndWorlflowQuery(EntryLoadRequest entryLoadRequest){
993        return buildMultilangAndWorlflowQuery(entryLoadRequest,false);
994    }
995
996    //--------------------------------------------------------------------------
997 /**
998     *
999     * @return
1000    */
1001   static public String buildMultilangAndWorlflowQuery(EntryLoadRequest entryLoadRequest,
1002                                                       boolean ignoreLang){
1003       return buildMultilangAndWorlflowQuery(entryLoadRequest,ignoreLang,false);
1004   }
1005
1006   static public String buildMultilangAndWorlflowQuery(EntryLoadRequest entryLoadRequest,
1007                                                       boolean ignoreLang,
1008                                                       boolean stagingOnly){
1009
1010       StringBuffer strBuf = new StringBuffer (96);
1011       if ( entryLoadRequest.isCurrent()){
1012           strBuf.append(FIELD_WORKFLOW_STATE);
1013           strBuf.append(COMP_EQUAL);
1014           strBuf.append(EntryLoadRequest.ACTIVE_WORKFLOW_STATE);
1015       } else if ( entryLoadRequest.isStaging() ) {
1016           strBuf.append(FIELD_WORKFLOW_STATE);
1017           strBuf.append(COMP_BIGGER);
1018           if ( stagingOnly ){
1019               strBuf.append(EntryLoadRequest.ACTIVE_WORKFLOW_STATE);
1020           } else {
1021               strBuf.append(EntryLoadRequest.VERSIONED_WORKFLOW_STATE);
1022           }
1023           strBuf.append(" AND ");
1024           strBuf.append(FIELD_VERSION_ID);
1025           strBuf.append(" <> -1 ");
1026       } else {
1027           strBuf.append(FIELD_VERSION_ID);
1028           strBuf.append("=");
1029           strBuf.append(entryLoadRequest.getVersionID());
1030       }
1031       if ( !ignoreLang ) {
1032           String languageCode = entryLoadRequest.getFirstLocale(true).
1033               toString();
1034           strBuf.append(" AND (");
1035           strBuf.append(FIELD_LANGUAGE_CODE);
1036           strBuf.append("='");
1037           strBuf.append(JahiaTools.quote(languageCode));
1038           strBuf.append("' OR ");
1039           strBuf.append(FIELD_LANGUAGE_CODE);
1040           strBuf.append("='");
1041           strBuf.append(ContentField.SHARED_LANGUAGE);
1042           strBuf.append("') ");
1043       }
1044
1045       return strBuf.toString();
1046
1047   }
1048
1049   //--------------------------------------------------------------------------
1050 /**
1051    *
1052    * @return
1053    */
1054   static public String buildMultilangAndWorlflowQuery(EntryLoadRequest entryLoadRequest,
1055                                                       String fieldWorkflowName,
1056                                                       String fieldLanguageCodeName,
1057                                                       String fieldVersionName ){
1058
1059       StringBuffer strBuf = new StringBuffer (" ");
1060       if ( entryLoadRequest.isCurrent()){
1061           strBuf.append(fieldWorkflowName);
1062           strBuf.append(COMP_EQUAL);
1063           strBuf.append(EntryLoadRequest.ACTIVE_WORKFLOW_STATE);
1064       } else {
1065           strBuf.append(fieldWorkflowName);
1066           strBuf.append(COMP_BIGGER);
1067           strBuf.append(EntryLoadRequest.VERSIONED_WORKFLOW_STATE);
1068           strBuf.append(" AND ");
1069           strBuf.append(fieldVersionName);
1070           strBuf.append(" <> -1 ");
1071       }
1072       String languageCode = entryLoadRequest.getFirstLocale(true).toString();
1073       strBuf.append(" AND (");
1074       strBuf.append(fieldLanguageCodeName);
1075       strBuf.append("='");
1076       strBuf.append(JahiaTools.quote(languageCode));
1077       strBuf.append("' OR ");
1078       strBuf.append(fieldLanguageCodeName);
1079       strBuf.append("='");
1080       strBuf.append(ContentField.SHARED_LANGUAGE);
1081       strBuf.append("') ");
1082
1083       return strBuf.toString();
1084
1085   }
1086    
1087    //--------------------------------------------------------------------------
1088 /**
1089     * Returns an array of field ids that are workflow=staging and versionid=-1 (deleted)
1090     *
1091     * @param int ctnListID, the container list id
1092     * @param String fieldName, the fieldName
1093     * @param ctnListID
1094     * @return
1095     * @throws JahiaException
1096     */
1097    static public ArrayList getDeletedFields(int ctnListID)
1098    throws JahiaException
1099    {
1100
1101        StringBuffer buff = new StringBuffer ("SELECT DISTINCT id_jahia_fields_data FROM jahia_ctn_entries a, jahia_fields_data b WHERE a.listid_jahia_ctn_entries=");
1102        buff.append(ctnListID);
1103        buff.append(" AND a.id_jahia_ctn_entries=b.ctnid_jahia_fields_data AND b.workflow_state>1 AND version_id=-1");
1104
1105        Connection dbConn = null;
1106        Statement stmt = null;
1107        ResultSet rs = null;
1108
1109        ArrayList datas = new ArrayList ();
1110
1111        try
1112        {
1113            dbConn = org.jahia.services.database.ConnectionDispenser.getConnection();
1114            stmt = dbConn.createStatement();
1115            rs = stmt.executeQuery( buff.toString() );
1116
1117            while (rs.next()) {
1118                datas.add(new Integer (rs.getInt(1)));
1119            }
1120        }
1121        catch (SQLException se)
1122        {
1123            logger.error ("Error in getDeletedFields() : ", se);
1124        } finally {
1125
1126            closeStatement (stmt);
1127        }
1128        return datas;
1129    }
1130
1131    //--------------------------------------------------------------------------
1132 /**
1133     * Returns an array of cntids that are workflow=staging and versionid=-1 (deleted)
1134     *
1135     * @param ctnListID
1136     * @return
1137     * @throws JahiaException
1138     */
1139    static public ArrayList getDeletedContainers(int ctnListID)
1140    throws JahiaException
1141    {
1142
1143        StringBuffer buff = new StringBuffer ("SELECT DISTINCT id_jahia_ctn_entries FROM jahia_ctn_entries WHERE workflow_state>1 AND version_id=-1 AND listid_jahia_ctn_entries=?");
1144        
1145        Connection dbConn = null;
1146        PreparedStatement stmt = null;
1147        ResultSet rs = null;
1148
1149        ArrayList datas = new ArrayList ();
1150
1151        try
1152        {
1153            dbConn = org.jahia.services.database.ConnectionDispenser.getConnection();
1154            stmt = dbConn.prepareStatement(buff.toString() );
1155            stmt.setInt(1, ctnListID);
1156            rs = stmt.executeQuery();
1157
1158            while (rs.next()) {
1159                datas.add(new Integer (rs.getInt(1)));
1160            }
1161        }
1162        catch (SQLException se)
1163        {
1164            logger.error("Error in getDeletedContainers() : ", se);
1165        } finally {
1166
1167            closeStatement (stmt);
1168        }
1169        return datas;
1170    }
1171
1172    //--------------------------------------------------------------------------
1173 /**
1174     * Returns an array of fields that are in staging.
1175     *
1176     * @param ctnListID
1177     * @return
1178     * @throws JahiaException
1179     */
1180    private ArrayList getStagingFields(int ctnListID)
1181    throws JahiaException
1182    {
1183        if ( this.containerFilters != null ){
1184            return this.containerFilters.getStagingFields(false,null,this.entryLoadRequest);
1185        }
1186
1187        StringBuffer buff = new StringBuffer ("SELECT DISTINCT id_jahia_fields_data FROM jahia_ctn_entries a, jahia_fields_data b WHERE a.listid_jahia_ctn_entries=");
1188        buff.append(ctnListID);
1189        buff.append(" AND a.id_jahia_ctn_entries=b.ctnid_jahia_fields_data AND b.workflow_state>1 ");
1190        buff.append(" AND ( ");
1191        buff.append(buildMultilangAndWorlflowQuery(this.entryLoadRequest,false,true));
1192        buff.append(" )");
1193
1194        Connection dbConn = null;
1195        Statement stmt = null;
1196        ResultSet rs = null;
1197
1198        ArrayList datas = new ArrayList ();
1199
1200        try
1201        {
1202            dbConn = org.jahia.services.database.ConnectionDispenser.getConnection();
1203            stmt = dbConn.createStatement();
1204            rs = stmt.executeQuery( buff.toString() );
1205
1206            while (rs.next()) {
1207                datas.add(new Integer (rs.getInt(1)));
1208            }
1209        }
1210        catch (SQLException se)
1211        {
1212            logger.error("Error in getStagingFields() : ", se);
1213        } finally {
1214
1215            closeStatement (stmt);
1216        }
1217        return datas;
1218    }
1219
1220
1221
1222
1223
1224
1225
1226
1227
1228
1229    //--------------------------------------------------------------------------
1230 /**
1231     * Perform filtering on a given site or all sites
1232     *
1233     * The expected result is a bit set of matching container ids.
1234     *
1235     * If siteId = -1 , returns results from all sites
1236     *
1237     * If the containerDefinitionName is null, return result from all containers
1238     * no regards to it definition !
1239     *
1240     * @param siteId
1241     * @param containerDefinitionName
1242     * @param listId
1243     *
1244     * @return BitSet bits, the expected result as a bit set of matching ctn ids,each bit position set to true correspond to matching ctn ids.
1245     * @throws JahiaException
1246     */
1247    public BitSet doFilterBySite(int siteId, String containerDefinitionName, int listId)
1248    throws JahiaException
1249    {
1250        if ( (this.getFieldName()==null)
1251              || (this.getFieldName().trim().equals(""))
1252              || (this.getClauses()==null)
1253              || (this.getClauses().size()==0) )
1254        {
1255            return null;
1256        }
1257
1258        BitSet result = null;
1259
1260        if ( !this.numberFiltering )
1261        {
1262            if ( !this.multipleFieldValue ){
1263                result = doQueryFilteringBySite(siteId, containerDefinitionName);
1264            } else {
1265                result = this.doStringFiltering(siteId,containerDefinitionName);
1266            }
1267        } else {
1268            result = doNumberValueFilteringBySite(siteId, containerDefinitionName);
1269        }
1270        return result;
1271    }
1272
1273    //--------------------------------------------------------------------------
1274 /**
1275     * Return the select statement, build with the clauses for a given site.
1276     * If siteId = -1 -> build query for all sites
1277     *
1278     * If the containerDefinitionName is null, return result from all containers
1279     * no regards to it definition !
1280     *
1281     * @param siteId
1282     * @param containerDefinitionName
1283     * @return
1284     */
1285    public String getSelectBySiteID(int siteId, String containerDefinitionName)
1286    {
1287
1288        if ( (this.getFieldName()==null)
1289              || (this.getFieldName().trim().equals(""))
1290              || (this.getClauses()==null)
1291              || (this.getClauses().size()==0) )
1292        {
1293            return null;
1294        }
1295
1296        StringBuffer buff = new StringBuffer ("SELECT DISTINCT id_jahia_ctn_entries,b.id_jahia_fields_data,b.workflow_state FROM jahia_ctn_entries a, jahia_fields_data b, jahia_fields_def c, jahia_ctn_def d WHERE ");
1297        if ( siteId != -1 ){
1298            buff.append(" jahiaid_jahia_ctn_entries=");
1299            buff.append(siteId);
1300            buff.append(" AND ");
1301        }
1302
1303        buff.append(" ( a.id_jahia_ctn_entries = b.ctnid_jahia_fields_data AND b.fielddefid_jahia_fields_data = c.id_jahia_fields_def AND c.name_jahia_fields_def='");
1304        buff.append(JahiaTools.quote(this.getFieldName()));
1305        buff.append("' ");
1306
1307        if ( containerDefinitionName != null &&
1308             !"".equals(containerDefinitionName.trim()) ){
1309            buff.append(" AND a.ctndefid_jahia_ctn_entries = d.id_jahia_ctn_def ");
1310            buff.append(" AND d.name_jahia_ctn_def='");
1311            buff.append(JahiaTools.quote(containerDefinitionName));
1312            buff.append("'");
1313        }
1314
1315        buff.append(" AND (");
1316
1317        FilterClause fClause = null;
1318        int size = this.clauses.size();
1319        for ( int i=0 ; i<size ; i++ )
1320        {
1321            fClause = (FilterClause)this.clauses.get(i);
1322            if ( fClause != null && fClause.isValid() )
1323            {
1324                StringBuffer clauseBuff = new StringBuffer ("(");
1325                if ( !fClause.isRangeClause() )
1326                {
1327                    String [] values = fClause.getValues();
1328                    for (int j=0 ; j<values.length; j++)
1329                    {
1330                        // TODO : hollis
1331 // need to review encode/decode special car
1332 String val = values[j];
1333
1334                        clauseBuff.append("(");
1335                        clauseBuff.append(buildMultilangAndWorlflowQuery(this.entryLoadRequest));
1336                        clauseBuff.append(" AND ");
1337                        //SCSE: add STARTS_WITH comparator
1338 if (COMP_STARTS_WITH.equals(fClause.getComp()))
1339                        {
1340                          String lowerValue = val.toLowerCase();
1341                          boolean considerUmlauts = lowerValue.length() == 1 && ("a".equals(lowerValue) || "o".equals(lowerValue) || "u".equals(lowerValue) || "s".equals(lowerValue));
1342                          if (considerUmlauts)
1343                            clauseBuff.append("(");
1344                          clauseBuff.append(" lower(" + FIELD_VALUE + ") like '");
1345                          clauseBuff.append(JahiaTools.quote(lowerValue));
1346                          clauseBuff.append("%'");
1347                          if (considerUmlauts)
1348                          {
1349                            clauseBuff.append(" OR lower(" + FIELD_VALUE + ") like '");
1350                            switch (lowerValue.charAt(0))
1351                            {
1352                              case 'a':
1353                                clauseBuff.append("ä");
1354                                break;
1355                              case 'o':
1356                                clauseBuff.append("ö");
1357                                break;
1358                              case 'u':
1359                                clauseBuff.append("ü");
1360                                break;
1361                              case 's':
1362                                clauseBuff.append("ß");
1363                                break;
1364                              default:
1365                                throw new IllegalArgumentException (
1366                                  "Unknown char for umlaut substitution '" + val.charAt(0)
1367                                    + "'");
1368                            }
1369                            clauseBuff.append("%')");
1370                          }
1371                        }
1372                        else
1373                        {
1374                          clauseBuff.append(FIELD_VALUE);
1375                          clauseBuff.append(fClause.getComp());
1376                          clauseBuff.append("'");
1377                          clauseBuff.append(JahiaTools.quote(val));
1378                          clauseBuff.append("'");
1379                        }
1380
1381                        clauseBuff.append(")");
1382                        if ( j<(values.length-1) ){
1383                            clauseBuff.append(" OR ");
1384                        }
1385                    }
1386                    clauseBuff.append(")");
1387                } else {
1388                    clauseBuff.append(buildMultilangAndWorlflowQuery(this.entryLoadRequest));
1389                    clauseBuff.append(" AND ");
1390                    clauseBuff.append(FIELD_VALUE);
1391                    clauseBuff.append(fClause.getLowerComp());
1392                    clauseBuff.append("'");
1393                    clauseBuff.append(JahiaTools.quote(fClause.getLowerValue()));
1394                    clauseBuff.append("' AND ");
1395                    clauseBuff.append(FIELD_VALUE);
1396                    clauseBuff.append(fClause.getUpperComp());
1397                    clauseBuff.append("'");
1398                    clauseBuff.append(JahiaTools.quote(fClause.getUpperValue()));
1399                    clauseBuff.append("')");
1400                }
1401                buff.append(clauseBuff.toString());
1402                if ( i<size-1 )
1403                {
1404                    buff.append(" OR ");
1405                }
1406            }
1407        }
1408        buff.append(")) ORDER BY ");
1409        buff.append(ContainerFilterBean.FIELD_ID);
1410        buff.append(",");
1411        buff.append(ContainerFilterBean.FIELD_WORKFLOW_STATE);
1412
1413        logger.debug("field filter query : " + buff.toString());
1414
1415        return buff.toString();
1416    }
1417
1418    //--------------------------------------------------------------------------
1419 /**
1420     *
1421     * The expected result is a bit set of matching container ids for a given siteId.
1422     * if siteId = -1 , return result from all sites
1423     *
1424     * If the containerDefinitionName is null, return result from all containers
1425     * no regards to it definition !
1426     *
1427     * @param siteId
1428     * @return BitSet bits, the expected result as a bit set of matching ctn ids,each bit position set to true correspond to matching ctn ids.
1429     * @throws JahiaException
1430     */
1431    protected BitSet doQueryFilteringBySite(int siteId,
1432            String containerDefinitionName)
1433    throws JahiaException
1434    {
1435        String fieldFilterQuery = this.getSelectBySiteID(siteId, containerDefinitionName);
1436        if ( fieldFilterQuery == null && !fieldFilterQuery.trim().equals("") ){
1437            return null;
1438        }
1439
1440        BitSet bits = new BitSet ();
1441
1442        Connection dbConn = null;
1443        Statement stmt = null;
1444        ResultSet rs = null;
1445
1446        ArrayList deletedCtns = getDeletedContainersBySite(siteId, containerDefinitionName);
1447        ArrayList stagingFields = getStagingFieldsBySite(siteId, containerDefinitionName);
1448        HashMap maps = new HashMap ();
1449
1450        try
1451        {
1452            dbConn = org.jahia.services.database.ConnectionDispenser.getConnection();
1453            stmt = dbConn.createStatement();
1454            rs = stmt.executeQuery( fieldFilterQuery );
1455
1456            while (rs.next())
1457            {
1458                int ctnID = rs.getInt(1);
1459                int fieldID = rs.getInt(2);
1460                int workflowState = rs.getInt(3);
1461                if ( this.entryLoadRequest.isCurrent()
1462                     || !deletedCtns.contains(new Integer (ctnID))){
1463                    if ( workflowState > EntryLoadRequest.ACTIVE_WORKFLOW_STATE ){
1464                        workflowState = EntryLoadRequest.STAGING_WORKFLOW_STATE;
1465                    }
1466                    TempField aField = new TempField(fieldID,ctnID,0,workflowState,"","");
1467                    String key = fieldID + "_" + workflowState;
1468                    maps.put(key,aField);
1469                }
1470            }
1471        }
1472        catch (SQLException se)
1473        {
1474            logger.error("Error in doStringValueFiltering : ", se);
1475        } finally {
1476
1477            closeStatement (stmt);
1478        }
1479
1480        int size = maps.size();
1481        Iterator iterator = maps.values().iterator();
1482        while ( iterator.hasNext() ){
1483            TempField aField = (TempField)iterator.next();
1484            if ( this.entryLoadRequest.isCurrent() ){
1485                bits.set(aField.ctnID);
1486            } else if ( this.entryLoadRequest.isStaging()
1487                       && aField.workflowState > EntryLoadRequest.ACTIVE_WORKFLOW_STATE ){
1488                bits.set(aField.ctnID);
1489            } else if ( aField.workflowState == EntryLoadRequest.ACTIVE_WORKFLOW_STATE
1490                 && !stagingFields.contains(new Integer (aField.id))){
1491                bits.set(aField.ctnID);
1492            }
1493        }
1494        return bits;
1495    }
1496
1497    //--------------------------------------------------------------------------
1498 /**
1499     * Perform filtering for NUMBER like fields.
1500     * Container field values are loaded and converted to long representation before
1501     * filtering comparison.
1502     *
1503     * The expected result is a bit set of matching container ids for a given siteId
1504     * If siteId = -1 , return result from all sites
1505     *
1506     * @param siteId
1507     * @return BitSet bits, the expected result as a bit set of matching ctn ids,each bit position set to true correspond to matching ctn ids.
1508     * @throws JahiaException
1509     */
1510    protected BitSet doNumberValueFilteringBySite(int siteId,
1511            String containerDefinitionName)
1512    throws JahiaException
1513    {
1514
1515        BitSet result = new BitSet ();
1516
1517        if ( (this.getFieldName()==null)
1518              || (this.getFieldName().trim().equals(""))
1519              || (this.getClauses()==null)
1520              || (this.getClauses().size()==0) )
1521        {
1522            return null;
1523        }
1524
1525        Map datas = getFieldValuesBySite(siteId, fieldName, containerDefinitionName);
1526
1527        if ( datas == null || datas.size()==0 ){
1528            return result;
1529        }
1530
1531        FilterClause fClause = null;
1532        Iterator keys = datas.keySet().iterator();
1533        Integer I = null;
1534        String S = null;
1535        String v = null;
1536
1537        while ( keys.hasNext() )
1538        {
1539            I = (Integer )keys.next();
1540            S = (String )datas.get(I);
1541            boolean match = false;
1542            String [] vals = S == null ? new String [] {null} : JahiaTools.getTokens(S,JahiaField.MULTIPLE_VALUES_SEP);
1543            int nbVals = vals.length;
1544            int i = 0;
1545            while ( (i<nbVals) && !match ){
1546                v = vals[i];
1547                int size = this.clauses.size();
1548                int j = 0;
1549                while ( (j<size) && !match )
1550                {
1551                    fClause = (FilterClause)this.clauses.get(j);
1552                    if ( fClause != null && fClause.isValid() )
1553                    {
1554                        match = fClause.compareNumber(v, this.numberFormat);
1555                    }
1556                    j++;
1557                }
1558                if ( match )
1559                {
1560                    result.set(I.intValue());
1561                }
1562                i++;
1563            }
1564        }
1565
1566        return result;
1567    }
1568
1569    //--------------------------------------------------------------------------
1570 /**
1571     * Perform filtering by loading field values in memories first.
1572     *
1573     * The expected result is a bit set of matching container ids.
1574     *
1575     * @param siteId
1576     * @param containerDefinitionName
1577     * @return BitSet bits, the expected result as a bit set of matching ctn ids,each bit position set to true correspond to matching ctn ids.
1578     */
1579    protected BitSet doStringFiltering(int siteId,
1580            String containerDefinitionName)
1581    throws JahiaException
1582    {
1583
1584        BitSet result = new BitSet ();
1585
1586        if ( (this.getFieldName()==null)
1587              || (this.getFieldName().trim().equals(""))
1588              || (this.getClauses()==null)
1589              || (this.getClauses().size()==0) )
1590        {
1591            return null;
1592        }
1593
1594        Map datas = this.getFieldValuesBySite(siteId,fieldName,containerDefinitionName);
1595
1596        if ( datas == null || datas.size()==0 ){
1597            return result;
1598        }
1599
1600        FilterClause fClause = null;
1601        Iterator keys = datas.keySet().iterator();
1602        Integer I = null;
1603        String S = null;
1604
1605        while ( keys.hasNext() )
1606        {
1607            I = (Integer )keys.next();
1608            S = (String )datas.get(I);
1609            boolean match = false;
1610            String val = "";
1611            String [] vals = JahiaTools.getTokens(S,JahiaField.MULTIPLE_VALUES_SEP);
1612            int nbVals = vals.length;
1613            int i = 0;
1614            while ( (i<nbVals) && !match ){
1615                val = vals[i];
1616                int size = this.clauses.size();
1617                int j = 0;
1618                while ( (j<size) && !match )
1619                {
1620                    fClause = (FilterClause)this.clauses.get(j);
1621                    if ( fClause != null && fClause.isValid() )
1622                    {
1623                        match = fClause.compare(val);
1624                    }
1625                    j++;
1626                }
1627                if ( match )
1628                {
1629                    result.set(I.intValue());
1630                }
1631                i++;
1632            }
1633        }
1634
1635        return result;
1636    }
1637
1638    //--------------------------------------------------------------------------
1639 /**
1640     * Returns an array of cntids that are workflow=staging and versionid=-1 (deleted)
1641     * for a given site
1642     * If siteId = -1 , return deleted containers from all sites
1643     *
1644     * @param ctnListID
1645     * @return
1646     * @throws JahiaException
1647     */
1648    static public ArrayList getDeletedContainersBySite(int siteId,
1649            String containerDefinitionName)
1650    throws JahiaException
1651    {
1652
1653        StringBuffer buff = new StringBuffer ("SELECT DISTINCT id_jahia_ctn_entries FROM jahia_ctn_entries, jahia_ctn_def WHERE workflow_state>1 AND version_id=-1 ");
1654        if ( siteId != -1 ){
1655            buff.append(" AND jahiaid_jahia_ctn_entries=");
1656            buff.append(siteId);
1657        }
1658
1659        if ( containerDefinitionName != null &&
1660             !"".equals(containerDefinitionName.trim()) ){
1661            buff.append(" AND ctndefid_jahia_ctn_entries = id_jahia_ctn_def ");
1662            buff.append(" AND name_jahia_ctn_def='");
1663            buff.append(JahiaTools.quote(containerDefinitionName));
1664            buff.append("'");
1665        }
1666
1667        Connection dbConn = null;
1668        Statement stmt = null;
1669        ResultSet rs = null;
1670
1671        ArrayList datas = new ArrayList ();
1672
1673        try
1674        {
1675            dbConn = org.jahia.services.database.ConnectionDispenser.getConnection();
1676            stmt = dbConn.createStatement();
1677            rs = stmt.executeQuery( buff.toString() );
1678
1679            while (rs.next()) {
1680                datas.add(new Integer (rs.getInt(1)));
1681            }
1682        }
1683        catch (SQLException se)
1684        {
1685            logger.error("Error in getDeletedContainers() : ", se);
1686        } finally {
1687
1688            closeStatement (stmt);
1689        }
1690        return datas;
1691    }
1692
1693    //--------------------------------------------------------------------------
1694 /**
1695     * Returns an array of fields that are in staging.
1696     *
1697     * If siteId = -1 , return result from all sites
1698     *
1699     * @param siteId
1700     * @return
1701     * @throws JahiaException
1702     */
1703    protected ArrayList getStagingFieldsBySite(int siteId,
1704            String containerDefinitionName)
1705    throws JahiaException
1706    {
1707        if ( this.containerFilters != null ){
1708            return this.containerFilters.getStagingFields(false,
1709                    containerDefinitionName,this.entryLoadRequest);
1710        }
1711
1712        StringBuffer buff = new StringBuffer ("SELECT DISTINCT id_jahia_fields_data FROM jahia_ctn_entries a, jahia_fields_data b, jahia_ctn_def c WHERE ");
1713        if ( siteId != -1 ){
1714            buff.append(" a.jahiaid_jahia_ctn_entries=");
1715            buff.append(siteId);
1716            buff.append(" AND ");
1717        }
1718
1719        if ( containerDefinitionName != null &&
1720             !"".equals(containerDefinitionName.trim()) ){
1721            buff.append(" a.ctndefid_jahia_ctn_entries = c.id_jahia_ctn_def ");
1722            buff.append(" AND c.name_jahia_ctn_def='");
1723            buff.append(JahiaTools.quote(containerDefinitionName));
1724            buff.append("'");
1725        }
1726
1727        buff.append(" a.id_jahia_ctn_entries=b.ctnid_jahia_fields_data AND b.workflow_state>1 ");
1728        buff.append(" AND ( ");
1729        buff.append(buildMultilangAndWorlflowQuery(this.entryLoadRequest,false,true));
1730        buff.append(" )");
1731
1732        Connection dbConn = null;
1733        Statement stmt = null;
1734        ResultSet rs = null;
1735
1736        ArrayList datas = new ArrayList ();
1737
1738        try
1739        {
1740            dbConn = org.jahia.services.database.ConnectionDispenser.getConnection();
1741            stmt = dbConn.createStatement();
1742            rs = stmt.executeQuery( buff.toString() );
1743
1744            while (rs.next()) {
1745                datas.add(new Integer (rs.getInt(1)));
1746            }
1747        }
1748        catch (SQLException se)
1749        {
1750            logger.error("Error in getStagingFields() : ", se);
1751        } finally {
1752
1753            closeStatement (stmt);
1754        }
1755        return datas;
1756    }
1757
1758
1759    //--------------------------------------------------------------------------
1760 /**
1761     * Load an hashtable of pair/value (ctnID,fieldValue) for a given ctnlist and a given fieldName.
1762     *
1763     * If siteId = -1 , return result from all sites
1764     *
1765     * @param siteID
1766     * @param containerDefinitionName
1767     * @return
1768     * @throws JahiaException
1769     */
1770    protected Map getFieldValuesBySite(int siteId,
1771            String fieldName,
1772            String containerDefinitionName)
1773    throws JahiaException
1774    {
1775
1776        ArrayList deletedCtns = getDeletedContainersBySite(siteId,containerDefinitionName);
1777        ArrayList stagingFields = this.getStagingFieldsBySite(siteId,containerDefinitionName);
1778
1779        StringBuffer buff = new StringBuffer ("SELECT DISTINCT ctnid_jahia_fields_data,b.id_jahia_fields_data,b.value_jahia_fields_data,b.workflow_state FROM jahia_ctn_entries a, jahia_fields_data b, jahia_fields_def c, jahia_ctn_def d WHERE ");
1780        if ( siteId != -1 ){
1781            buff.append(" jahiaid_jahia_ctn_entries=");
1782            buff.append(siteId);
1783            buff.append(" AND ");
1784        }
1785
1786        if ( containerDefinitionName != null &&
1787             !"".equals(containerDefinitionName.trim()) ){
1788            buff.append(" a.ctndefid_jahia_ctn_entries = d.id_jahia_ctn_def ");
1789            buff.append(" AND d.name_jahia_ctn_def='");
1790            buff.append(JahiaTools.quote(containerDefinitionName));
1791            buff.append("'");
1792            buff.append(" AND ");
1793        }
1794
1795        buff.append(" ( a.id_jahia_ctn_entries = b.ctnid_jahia_fields_data AND b.fielddefid_jahia_fields_data = c.id_jahia_fields_def AND c.name_jahia_fields_def='");
1796        buff.append(JahiaTools.quote(fieldName));
1797        buff.append("' ) AND (");
1798        buff.append(buildMultilangAndWorlflowQuery(this.entryLoadRequest));
1799        buff.append(") ORDER BY ");
1800        buff.append(ContainerFilterBean.FIELD_ID);
1801        buff.append(",");
1802        buff.append(ContainerFilterBean.FIELD_WORKFLOW_STATE);
1803
1804        String query = buff.toString();
1805        Connection dbConn = null;
1806        Statement stmt = null;
1807        ResultSet rs = null;
1808
1809        Map datas = new HashMap ();
1810        HashMap maps = new HashMap ();
1811        try
1812        {
1813            dbConn = org.jahia.services.database.ConnectionDispenser.getConnection();
1814            stmt = dbConn.createStatement();
1815            rs = stmt.executeQuery( buff.toString() );
1816
1817            while (rs.next())
1818            {
1819                int ctnID = rs.getInt(1);
1820                int fieldID = rs.getInt(2);
1821                String fieldValue = ""; //empty string field value: to support oracle specificity
1822 if(rs.getString(3)!=null) fieldValue=rs.getString(3);
1823                int workflowState = rs.getInt(4);
1824
1825                if ( this.entryLoadRequest.isCurrent()
1826                     || !deletedCtns.contains(new Integer (ctnID))){
1827                    if ( workflowState > EntryLoadRequest.ACTIVE_WORKFLOW_STATE ){
1828                        workflowState = EntryLoadRequest.STAGING_WORKFLOW_STATE;
1829                    }
1830                    TempField aField = new TempField(fieldID,ctnID,0,workflowState,"",fieldValue);
1831                    String key = fieldID + "_" + workflowState;
1832                    maps.put(key,aField);
1833                }
1834            }
1835        }
1836        catch (SQLException se)
1837        {
1838            logger.error("Error in getFieldValues : ", se);
1839        } finally {
1840
1841            closeStatement (stmt);
1842        }
1843
1844        Iterator iterator = maps.values().iterator();
1845        while ( iterator.hasNext() ){
1846            TempField aField = (TempField)iterator.next();
1847            if ( this.entryLoadRequest.isCurrent() ){
1848                datas.put(new Integer (aField.ctnID),aField.value);
1849            } else if ( this.entryLoadRequest.isStaging()
1850                       && aField.workflowState > EntryLoadRequest.ACTIVE_WORKFLOW_STATE ){
1851                datas.put(new Integer (aField.ctnID),aField.value);
1852            } else if ( aField.workflowState == EntryLoadRequest.ACTIVE_WORKFLOW_STATE
1853                 && !stagingFields.contains(new Integer (aField.id))){
1854                datas.put(new Integer (aField.ctnID),aField.value);
1855            }
1856        }
1857
1858        return datas;
1859    }
1860
1861
1862    //--------------------------------------------------------------------------
1863 /**
1864     * Check if a field values must be converted to long representation before comparison or not
1865     *
1866     * @param int , the container list id
1867     * @return boolean , true is a Long Value comparison is needed
1868     */
1869    private boolean needNumberValueFiltering(int ctnListID)
1870    throws JahiaException {
1871
1872        // TODO: text or number filtering should be defined by the field type.
1873 // actually, it is defined by the template designer who can force number filtering or not.
1874 return ( this.numberFiltering );
1875    }
1876
1877    //--------------------------------------------------------------------------
1878 /**
1879     * Check if the comparator is valid
1880     *
1881     * @param comparator the comparator
1882     */
1883    private boolean checkComparator(String comparator){
1884        if ( comparator == null )
1885            return false;
1886
1887        return ( comparator.equals(COMP_EQUAL)
1888                 || comparator.equals(COMP_SMALLER)
1889                 || comparator.equals(COMP_SMALLER_OR_EQUAL)
1890                 || comparator.equals(COMP_BIGGER_OR_EQUAL)
1891                 || comparator.equals(COMP_BIGGER)
1892                 || comparator.equals(COMP_NOTNULL)
1893                 || comparator.equals(COMP_ISNULL)
1894                 || comparator.equals(COMP_NOT_EQUAL)
1895                 || comparator.equals(COMP_STARTS_WITH));
1896    }
1897
1898
1899    //-------------------------------------------------------------------------
1900 static private void closeStatement (Statement statement)
1901    {
1902        // Close the opened statement
1903 try {
1904            if (statement!=null) {
1905                statement.close();
1906            }
1907        }
1908        catch (SQLException sqlEx) {
1909            // just create an exception without raising it, just to notify it
1910 // in the logs.
1911 logger.error("Error closing statement", sqlEx);
1912        }
1913    }
1914
1915
1916    private class TempField{
1917        public int id;
1918        public int ctnID;
1919        public int versionID;
1920        public int workflowState;
1921        public String languageCode;
1922        public String value;
1923
1924        public TempField(int id, int ctnID, int versionID, int workflowState,
1925                         String languageCode, String value){
1926            this.id = id;
1927            this.ctnID = ctnID;
1928            this.versionID = versionID;
1929            this.workflowState = workflowState;
1930            this.languageCode = languageCode;
1931            this.value = value;
1932        }
1933    }
1934
1935}
1936

A to Z: JavaDoc & Examples

---

Daily Java News & Articles

---

Open Source Projects

---

Open Source Codes

---

Free Computer Books

---

Remove Frame

---

Popular Tags