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