1   /**
2    * Copyright (c) 2000-2007 Liferay, Inc. All rights reserved.
3    *
4    * Permission is hereby granted, free of charge, to any person obtaining a copy
5    * of this software and associated documentation files (the "Software"), to deal
6    * in the Software without restriction, including without limitation the rights
7    * to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
8    * copies of the Software, and to permit persons to whom the Software is
9    * furnished to do so, subject to the following conditions:
10   *
11   * The above copyright notice and this permission notice shall be included in
12   * all copies or substantial portions of the Software.
13   *
14   * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
15   * IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
16   * FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
17   * AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
18   * LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
19   * OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
20   * SOFTWARE.
21   */
22  
23  package com.liferay.portlet.shopping.service.persistence;
24  
25  import com.liferay.portal.SystemException;
26  import com.liferay.portal.kernel.util.StringMaker;
27  import com.liferay.portal.kernel.util.StringUtil;
28  import com.liferay.portal.spring.hibernate.CustomSQLUtil;
29  import com.liferay.portal.spring.hibernate.HibernateUtil;
30  import com.liferay.portlet.shopping.model.impl.ShoppingItemImpl;
31  import com.liferay.util.dao.hibernate.QueryPos;
32  import com.liferay.util.dao.hibernate.QueryUtil;
33  
34  import java.util.Iterator;
35  import java.util.List;
36  
37  import org.hibernate.Hibernate;
38  import org.hibernate.SQLQuery;
39  import org.hibernate.Session;
40  
41  /**
42   * <a href="ShoppingItemFinder.java.html"><b><i>View Source</i></b></a>
43   *
44   * @author Brian Wing Shun Chan
45   *
46   */
47  public class ShoppingItemFinder {
48  
49      public static String COUNT_BY_CATEGORY_IDS =
50          ShoppingItemFinder.class.getName() + ".countByCategoryIds";
51  
52      public static int countByCategoryIds(List categoryIds)
53          throws SystemException {
54  
55          Session session = null;
56  
57          try {
58              session = HibernateUtil.openSession();
59  
60              String sql = CustomSQLUtil.get(COUNT_BY_CATEGORY_IDS);
61  
62              sql = StringUtil.replace(
63                  sql, "[$CATEGORY_ID$]", _getCategoryIds(categoryIds));
64  
65              SQLQuery q = session.createSQLQuery(sql);
66  
67              q.addScalar(HibernateUtil.getCountColumnName(), Hibernate.LONG);
68  
69              QueryPos qPos = QueryPos.getInstance(q);
70  
71              for (int i = 0; i < categoryIds.size(); i++) {
72                  Long categoryId = (Long)categoryIds.get(i);
73  
74                  qPos.add(categoryId);
75              }
76  
77              Iterator itr = q.list().iterator();
78  
79              if (itr.hasNext()) {
80                  Long count = (Long)itr.next();
81  
82                  if (count != null) {
83                      return count.intValue();
84                  }
85              }
86  
87              return 0;
88          }
89          catch (Exception e) {
90              throw new SystemException(e);
91          }
92          finally {
93              HibernateUtil.closeSession(session);
94          }
95      }
96  
97      public static int countByFeatured(long groupId, long[] categoryIds)
98          throws SystemException {
99  
100         Session session = null;
101 
102         try {
103             session = HibernateUtil.openSession();
104 
105             StringMaker query = new StringMaker();
106 
107             query.append("SELECT COUNT(*) AS COUNT_VALUE FROM ShoppingItem ");
108             query.append("INNER JOIN ShoppingCategory ON ");
109             query.append("ShoppingCategory.categoryId = ");
110             query.append("ShoppingItem.categoryId ");
111             query.append("WHERE ");
112             query.append("ShoppingCategory.groupId = ? AND (");
113 
114             if ((categoryIds != null) && (categoryIds.length > 0)) {
115                 query.append("(");
116 
117                 for (int i = 0; i < categoryIds.length; i++) {
118                     query.append("ShoppingItem.categoryId = ? ");
119 
120                     if (i + 1 < categoryIds.length) {
121                         query.append("OR ");
122                     }
123                 }
124 
125                 query.append(") AND ");
126             }
127 
128             query.append("ShoppingItem.featured = ? AND ");
129             query.append("ShoppingItem.smallImage = ?");
130 
131             SQLQuery q = session.createSQLQuery(query.toString());
132 
133             q.addScalar(HibernateUtil.getCountColumnName(), Hibernate.LONG);
134 
135             QueryPos qPos = QueryPos.getInstance(q);
136 
137             qPos.add(groupId);
138 
139             for (int i = 0; i < categoryIds.length; i++) {
140                 qPos.add(categoryIds[i]);
141             }
142 
143             qPos.add(true);
144             qPos.add(true);
145 
146             Iterator itr = q.list().iterator();
147 
148             if (itr.hasNext()) {
149                 Long count = (Long)itr.next();
150 
151                 if (count != null) {
152                     return count.intValue();
153                 }
154             }
155 
156             return 0;
157         }
158         catch (Exception e) {
159             throw new SystemException(e);
160         }
161         finally {
162             HibernateUtil.closeSession(session);
163         }
164     }
165 
166     public static int countByKeywords(
167             long groupId, long[] categoryIds, String keywords)
168         throws SystemException {
169 
170         Session session = null;
171 
172         try {
173             session = HibernateUtil.openSession();
174 
175             StringMaker query = new StringMaker();
176 
177             query.append("SELECT COUNT(*) AS COUNT_VALUE FROM ShoppingItem ");
178             query.append("INNER JOIN ShoppingCategory ON ");
179             query.append("ShoppingCategory.categoryId = ");
180             query.append("ShoppingItem.categoryId ");
181             query.append("WHERE ");
182             query.append("ShoppingCategory.groupId = ? AND (");
183 
184             if ((categoryIds != null) && (categoryIds.length > 0)) {
185                 query.append("(");
186 
187                 for (int i = 0; i < categoryIds.length; i++) {
188                     query.append("ShoppingItem.categoryId = ? ");
189 
190                     if (i + 1 < categoryIds.length) {
191                         query.append("OR ");
192                     }
193                 }
194 
195                 query.append(") AND ");
196             }
197 
198             query.append("(ShoppingItem.name LIKE ? OR ");
199             query.append("ShoppingItem.description LIKE ? OR ");
200             query.append("ShoppingItem.properties LIKE ?))");
201 
202             keywords = '%' + keywords + '%';
203 
204             SQLQuery q = session.createSQLQuery(query.toString());
205 
206             q.addScalar(HibernateUtil.getCountColumnName(), Hibernate.LONG);
207 
208             QueryPos qPos = QueryPos.getInstance(q);
209 
210             qPos.add(groupId);
211 
212             for (int i = 0; i < categoryIds.length; i++) {
213                 qPos.add(categoryIds[i]);
214             }
215 
216             qPos.add(keywords);
217             qPos.add(keywords);
218             qPos.add(keywords);
219 
220             Iterator itr = q.list().iterator();
221 
222             if (itr.hasNext()) {
223                 Long count = (Long)itr.next();
224 
225                 if (count != null) {
226                     return count.intValue();
227                 }
228             }
229 
230             return 0;
231         }
232         catch (Exception e) {
233             throw new SystemException(e);
234         }
235         finally {
236             HibernateUtil.closeSession(session);
237         }
238     }
239 
240     public static int countBySale(long groupId, long[] categoryIds)
241         throws SystemException {
242 
243         Session session = null;
244 
245         try {
246             session = HibernateUtil.openSession();
247 
248             StringMaker query = new StringMaker();
249 
250             query.append("SELECT COUNT(*) AS COUNT_VALUE FROM ShoppingItem ");
251             query.append("INNER JOIN ShoppingCategory ON ");
252             query.append("ShoppingCategory.categoryId = ");
253             query.append("ShoppingItem.categoryId ");
254             query.append("WHERE ");
255             query.append("ShoppingCategory.groupId = ? AND (");
256 
257             if ((categoryIds != null) && (categoryIds.length > 0)) {
258                 query.append("(");
259 
260                 for (int i = 0; i < categoryIds.length; i++) {
261                     query.append("ShoppingItem.categoryId = ? ");
262 
263                     if (i + 1 < categoryIds.length) {
264                         query.append("OR ");
265                     }
266                 }
267 
268                 query.append(") AND ");
269             }
270 
271             query.append("ShoppingItem.sale = ? AND ");
272             query.append("ShoppingItem.smallImage = ?");
273 
274             SQLQuery q = session.createSQLQuery(query.toString());
275 
276             q.addScalar(HibernateUtil.getCountColumnName(), Hibernate.LONG);
277 
278             QueryPos qPos = QueryPos.getInstance(q);
279 
280             qPos.add(groupId);
281 
282             for (int i = 0; i < categoryIds.length; i++) {
283                 qPos.add(categoryIds[i]);
284             }
285 
286             qPos.add(true);
287             qPos.add(true);
288 
289             Iterator itr = q.list().iterator();
290 
291             if (itr.hasNext()) {
292                 Long count = (Long)itr.next();
293 
294                 if (count != null) {
295                     return count.intValue();
296                 }
297             }
298 
299             return 0;
300         }
301         catch (Exception e) {
302             throw new SystemException(e);
303         }
304         finally {
305             HibernateUtil.closeSession(session);
306         }
307     }
308 
309     public static List findByFeatured(
310             long groupId, long[] categoryIds, int numOfItems)
311         throws SystemException {
312 
313         int countByFeatured = countByFeatured(groupId, categoryIds);
314 
315         Session session = null;
316 
317         try {
318             session = HibernateUtil.openSession();
319 
320             StringMaker query = new StringMaker();
321 
322             query.append("SELECT {ShoppingItem.*} FROM ShoppingItem ");
323             query.append("INNER JOIN ShoppingCategory ON ");
324             query.append("ShoppingCategory.categoryId = ");
325             query.append("ShoppingItem.categoryId ");
326             query.append("WHERE ");
327             query.append("ShoppingCategory.groupId = ? AND (");
328 
329             if ((categoryIds != null) && (categoryIds.length > 0)) {
330                 query.append("(");
331 
332                 for (int i = 0; i < categoryIds.length; i++) {
333                     query.append("ShoppingItem.categoryId = ? ");
334 
335                     if (i + 1 < categoryIds.length) {
336                         query.append("OR ");
337                     }
338                 }
339 
340                 query.append(") AND ");
341             }
342 
343             query.append("ShoppingItem.featured = ? AND ");
344             query.append("ShoppingItem.smallImage = ?");
345 
346             SQLQuery q = session.createSQLQuery(query.toString());
347 
348             q.addEntity("ShoppingItem", ShoppingItemImpl.class);
349 
350             QueryPos qPos = QueryPos.getInstance(q);
351 
352             qPos.add(groupId);
353 
354             for (int i = 0; i < categoryIds.length; i++) {
355                 qPos.add(categoryIds[i]);
356             }
357 
358             qPos.add(true);
359             qPos.add(true);
360 
361             return QueryUtil.randomList(
362                 q, HibernateUtil.getDialect(), countByFeatured, numOfItems);
363         }
364         catch (Exception e) {
365             throw new SystemException(e);
366         }
367         finally {
368             HibernateUtil.closeSession(session);
369         }
370     }
371 
372     public static List findByKeywords(
373             long groupId, long[] categoryIds, String keywords, int begin,
374             int end)
375         throws SystemException {
376 
377         Session session = null;
378 
379         try {
380             session = HibernateUtil.openSession();
381 
382             StringMaker query = new StringMaker();
383 
384             query.append("SELECT {ShoppingItem.*} FROM ShoppingItem ");
385             query.append("INNER JOIN ShoppingCategory ON ");
386             query.append("ShoppingCategory.categoryId = ");
387             query.append("ShoppingItem.categoryId ");
388             query.append("WHERE ");
389             query.append("ShoppingCategory.groupId = ? AND (");
390 
391             if ((categoryIds != null) && (categoryIds.length > 0)) {
392                 query.append("(");
393 
394                 for (int i = 0; i < categoryIds.length; i++) {
395                     query.append("ShoppingItem.categoryId = ? ");
396 
397                     if (i + 1 < categoryIds.length) {
398                         query.append("OR ");
399                     }
400                 }
401 
402                 query.append(") AND ");
403             }
404 
405             query.append("(ShoppingItem.name LIKE ? OR ");
406             query.append("ShoppingItem.description LIKE ? OR ");
407             query.append("ShoppingItem.properties LIKE ?))");
408 
409             keywords = '%' + keywords + '%';
410 
411             SQLQuery q = session.createSQLQuery(query.toString());
412 
413             q.addEntity("ShoppingItem", ShoppingItemImpl.class);
414 
415             QueryPos qPos = QueryPos.getInstance(q);
416 
417             qPos.add(groupId);
418 
419             for (int i = 0; i < categoryIds.length; i++) {
420                 qPos.add(categoryIds[i]);
421             }
422 
423             qPos.add(keywords);
424             qPos.add(keywords);
425             qPos.add(keywords);
426 
427             return QueryUtil.list(q, HibernateUtil.getDialect(), begin, end);
428         }
429         catch (Exception e) {
430             throw new SystemException(e);
431         }
432         finally {
433             HibernateUtil.closeSession(session);
434         }
435     }
436 
437     public static List findBySale(
438             long groupId, long[] categoryIds, int numOfItems)
439         throws SystemException {
440 
441         int countBySale = countBySale(groupId, categoryIds);
442 
443         Session session = null;
444 
445         try {
446             session = HibernateUtil.openSession();
447 
448             StringMaker query = new StringMaker();
449 
450             query.append("SELECT {ShoppingItem.*} FROM ShoppingItem ");
451             query.append("INNER JOIN ShoppingCategory ON ");
452             query.append("ShoppingCategory.categoryId = ");
453             query.append("ShoppingItem.categoryId ");
454             query.append("WHERE ");
455             query.append("ShoppingCategory.groupId = ? AND (");
456 
457             if ((categoryIds != null) && (categoryIds.length > 0)) {
458                 query.append("(");
459 
460                 for (int i = 0; i < categoryIds.length; i++) {
461                     query.append("ShoppingItem.categoryId = ? ");
462 
463                     if (i + 1 < categoryIds.length) {
464                         query.append("OR ");
465                     }
466                 }
467 
468                 query.append(") AND ");
469             }
470 
471             query.append("ShoppingItem.sale = ? AND ");
472             query.append("ShoppingItem.smallImage = ?");
473 
474             SQLQuery q = session.createSQLQuery(query.toString());
475 
476             q.addEntity("ShoppingItem", ShoppingItemImpl.class);
477 
478             QueryPos qPos = QueryPos.getInstance(q);
479 
480             qPos.add(groupId);
481 
482             for (int i = 0; i < categoryIds.length; i++) {
483                 qPos.add(categoryIds[i]);
484             }
485 
486             qPos.add(true);
487             qPos.add(true);
488 
489             return QueryUtil.randomList(
490                 q, HibernateUtil.getDialect(), countBySale, numOfItems);
491         }
492         catch (Exception e) {
493             throw new SystemException(e);
494         }
495         finally {
496             HibernateUtil.closeSession(session);
497         }
498     }
499 
500     private static String _getCategoryIds(List categoryIds) {
501         StringMaker sm = new StringMaker();
502 
503         for (int i = 0; i < categoryIds.size(); i++) {
504             sm.append("categoryId = ? ");
505 
506             if ((i + 1) != categoryIds.size()) {
507                 sm.append("OR ");
508             }
509         }
510 
511         return sm.toString();
512     }
513 
514 }