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