1   /**
2    * Copyright (c) 2000-2009 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.dao.orm.QueryPos;
27  import com.liferay.portal.kernel.dao.orm.QueryUtil;
28  import com.liferay.portal.kernel.dao.orm.SQLQuery;
29  import com.liferay.portal.kernel.dao.orm.Session;
30  import com.liferay.portal.kernel.dao.orm.Type;
31  import com.liferay.portal.kernel.util.StringUtil;
32  import com.liferay.portal.service.persistence.impl.BasePersistenceImpl;
33  import com.liferay.portlet.shopping.model.ShoppingItem;
34  import com.liferay.portlet.shopping.model.impl.ShoppingItemImpl;
35  import com.liferay.util.dao.orm.CustomSQLUtil;
36  
37  import java.util.Iterator;
38  import java.util.List;
39  
40  /**
41   * <a href="ShoppingItemFinderImpl.java.html"><b><i>View Source</i></b></a>
42   *
43   * @author Brian Wing Shun Chan
44   *
45   */
46  public class ShoppingItemFinderImpl
47      extends BasePersistenceImpl implements ShoppingItemFinder {
48  
49      public static String COUNT_BY_CATEGORY_IDS =
50          ShoppingItemFinder.class.getName() + ".countByCategoryIds";
51  
52      public int countByCategoryIds(List<Long> categoryIds)
53          throws SystemException {
54  
55          Session session = null;
56  
57          try {
58              session = 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(COUNT_COLUMN_NAME, Type.LONG);
68  
69              QueryPos qPos = QueryPos.getInstance(q);
70  
71              for (int i = 0; i < categoryIds.size(); i++) {
72                  Long categoryId = categoryIds.get(i);
73  
74                  qPos.add(categoryId);
75              }
76  
77              Iterator<Long> itr = q.list().iterator();
78  
79              if (itr.hasNext()) {
80                  Long count = 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              closeSession(session);
94          }
95      }
96  
97      public int countByFeatured(long groupId, long[] categoryIds)
98          throws SystemException {
99  
100         Session session = null;
101 
102         try {
103             session = openSession();
104 
105             StringBuilder query = new StringBuilder();
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(COUNT_COLUMN_NAME, Type.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<Long> itr = q.list().iterator();
147 
148             if (itr.hasNext()) {
149                 Long count = 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             closeSession(session);
163         }
164     }
165 
166     public int countByKeywords(
167             long groupId, long[] categoryIds, String keywords)
168         throws SystemException {
169 
170         Session session = null;
171 
172         try {
173             session = openSession();
174 
175             StringBuilder query = new StringBuilder();
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(COUNT_COLUMN_NAME, Type.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<Long> itr = q.list().iterator();
221 
222             if (itr.hasNext()) {
223                 Long count = 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             closeSession(session);
237         }
238     }
239 
240     public int countBySale(long groupId, long[] categoryIds)
241         throws SystemException {
242 
243         Session session = null;
244 
245         try {
246             session = openSession();
247 
248             StringBuilder query = new StringBuilder();
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(COUNT_COLUMN_NAME, Type.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<Long> itr = q.list().iterator();
290 
291             if (itr.hasNext()) {
292                 Long count = 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             closeSession(session);
306         }
307     }
308 
309     public List<ShoppingItem> 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 = openSession();
319 
320             StringBuilder query = new StringBuilder();
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 (List<ShoppingItem>)QueryUtil.randomList(
362                 q, getDialect(), countByFeatured, numOfItems);
363         }
364         catch (Exception e) {
365             throw new SystemException(e);
366         }
367         finally {
368             closeSession(session);
369         }
370     }
371 
372     public List<ShoppingItem> findByKeywords(
373             long groupId, long[] categoryIds, String keywords, int start,
374             int end)
375         throws SystemException {
376 
377         Session session = null;
378 
379         try {
380             session = openSession();
381 
382             StringBuilder query = new StringBuilder();
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 (List<ShoppingItem>)QueryUtil.list(
428                 q, getDialect(), start, end);
429         }
430         catch (Exception e) {
431             throw new SystemException(e);
432         }
433         finally {
434             closeSession(session);
435         }
436     }
437 
438     public List<ShoppingItem> findBySale(
439             long groupId, long[] categoryIds, int numOfItems)
440         throws SystemException {
441 
442         int countBySale = countBySale(groupId, categoryIds);
443 
444         Session session = null;
445 
446         try {
447             session = openSession();
448 
449             StringBuilder query = new StringBuilder();
450 
451             query.append("SELECT {ShoppingItem.*} FROM ShoppingItem ");
452             query.append("INNER JOIN ShoppingCategory ON ");
453             query.append("ShoppingCategory.categoryId = ");
454             query.append("ShoppingItem.categoryId ");
455             query.append("WHERE ");
456             query.append("ShoppingCategory.groupId = ? AND (");
457 
458             if ((categoryIds != null) && (categoryIds.length > 0)) {
459                 query.append("(");
460 
461                 for (int i = 0; i < categoryIds.length; i++) {
462                     query.append("ShoppingItem.categoryId = ? ");
463 
464                     if (i + 1 < categoryIds.length) {
465                         query.append("OR ");
466                     }
467                 }
468 
469                 query.append(") AND ");
470             }
471 
472             query.append("ShoppingItem.sale = ? AND ");
473             query.append("ShoppingItem.smallImage = ?");
474 
475             SQLQuery q = session.createSQLQuery(query.toString());
476 
477             q.addEntity("ShoppingItem", ShoppingItemImpl.class);
478 
479             QueryPos qPos = QueryPos.getInstance(q);
480 
481             qPos.add(groupId);
482 
483             for (int i = 0; i < categoryIds.length; i++) {
484                 qPos.add(categoryIds[i]);
485             }
486 
487             qPos.add(true);
488             qPos.add(true);
489 
490             return (List<ShoppingItem>)QueryUtil.randomList(
491                 q, getDialect(), countBySale, numOfItems);
492         }
493         catch (Exception e) {
494             throw new SystemException(e);
495         }
496         finally {
497             closeSession(session);
498         }
499     }
500 
501     protected String getCategoryIds(List<Long> categoryIds) {
502         StringBuilder sb = new StringBuilder();
503 
504         for (int i = 0; i < categoryIds.size(); i++) {
505             sb.append("categoryId = ? ");
506 
507             if ((i + 1) != categoryIds.size()) {
508                 sb.append("OR ");
509             }
510         }
511 
512         return sb.toString();
513     }
514 
515 }