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