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