1   /**
2    * Copyright (c) 2000-2009 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   * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
12   * IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
13   * FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
14   * AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
15   * LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
16   * OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
17   * SOFTWARE.
18   */
19  
20  package com.liferay.portlet.shopping.service.persistence;
21  
22  import com.liferay.portal.SystemException;
23  import com.liferay.portal.kernel.dao.orm.QueryPos;
24  import com.liferay.portal.kernel.dao.orm.QueryUtil;
25  import com.liferay.portal.kernel.dao.orm.SQLQuery;
26  import com.liferay.portal.kernel.dao.orm.Session;
27  import com.liferay.portal.kernel.dao.orm.Type;
28  import com.liferay.portal.kernel.util.StringUtil;
29  import com.liferay.portal.service.persistence.impl.BasePersistenceImpl;
30  import com.liferay.portlet.shopping.model.ShoppingItem;
31  import com.liferay.portlet.shopping.model.impl.ShoppingItemImpl;
32  import com.liferay.util.dao.orm.CustomSQLUtil;
33  
34  import java.util.Iterator;
35  import java.util.List;
36  
37  /**
38   * <a href="ShoppingItemFinderImpl.java.html"><b><i>View Source</i></b></a>
39   *
40   * @author Brian Wing Shun Chan
41   *
42   */
43  public class ShoppingItemFinderImpl
44      extends BasePersistenceImpl implements ShoppingItemFinder {
45  
46      public static String COUNT_BY_CATEGORY_IDS =
47          ShoppingItemFinder.class.getName() + ".countByCategoryIds";
48  
49      public int countByCategoryIds(List<Long> categoryIds)
50          throws SystemException {
51  
52          Session session = null;
53  
54          try {
55              session = openSession();
56  
57              String sql = CustomSQLUtil.get(COUNT_BY_CATEGORY_IDS);
58  
59              sql = StringUtil.replace(
60                  sql, "[$CATEGORY_ID$]", getCategoryIds(categoryIds));
61  
62              SQLQuery q = session.createSQLQuery(sql);
63  
64              q.addScalar(COUNT_COLUMN_NAME, Type.LONG);
65  
66              QueryPos qPos = QueryPos.getInstance(q);
67  
68              for (int i = 0; i < categoryIds.size(); i++) {
69                  Long categoryId = categoryIds.get(i);
70  
71                  qPos.add(categoryId);
72              }
73  
74              Iterator<Long> itr = q.list().iterator();
75  
76              if (itr.hasNext()) {
77                  Long count = itr.next();
78  
79                  if (count != null) {
80                      return count.intValue();
81                  }
82              }
83  
84              return 0;
85          }
86          catch (Exception e) {
87              throw new SystemException(e);
88          }
89          finally {
90              closeSession(session);
91          }
92      }
93  
94      public int countByFeatured(long groupId, long[] categoryIds)
95          throws SystemException {
96  
97          Session session = null;
98  
99          try {
100             session = openSession();
101 
102             StringBuilder query = new StringBuilder();
103 
104             query.append("SELECT COUNT(*) AS COUNT_VALUE FROM ShoppingItem ");
105             query.append("INNER JOIN ShoppingCategory ON ");
106             query.append("ShoppingCategory.categoryId = ");
107             query.append("ShoppingItem.categoryId ");
108             query.append("WHERE ");
109             query.append("ShoppingCategory.groupId = ? AND (");
110 
111             if ((categoryIds != null) && (categoryIds.length > 0)) {
112                 query.append("(");
113 
114                 for (int i = 0; i < categoryIds.length; i++) {
115                     query.append("ShoppingItem.categoryId = ? ");
116 
117                     if (i + 1 < categoryIds.length) {
118                         query.append("OR ");
119                     }
120                 }
121 
122                 query.append(") AND ");
123             }
124 
125             query.append("ShoppingItem.featured = ? AND ");
126             query.append("ShoppingItem.smallImage = ?");
127 
128             SQLQuery q = session.createSQLQuery(query.toString());
129 
130             q.addScalar(COUNT_COLUMN_NAME, Type.LONG);
131 
132             QueryPos qPos = QueryPos.getInstance(q);
133 
134             qPos.add(groupId);
135 
136             for (int i = 0; i < categoryIds.length; i++) {
137                 qPos.add(categoryIds[i]);
138             }
139 
140             qPos.add(true);
141             qPos.add(true);
142 
143             Iterator<Long> itr = q.list().iterator();
144 
145             if (itr.hasNext()) {
146                 Long count = itr.next();
147 
148                 if (count != null) {
149                     return count.intValue();
150                 }
151             }
152 
153             return 0;
154         }
155         catch (Exception e) {
156             throw new SystemException(e);
157         }
158         finally {
159             closeSession(session);
160         }
161     }
162 
163     public int countByKeywords(
164             long groupId, long[] categoryIds, String keywords)
165         throws SystemException {
166 
167         Session session = null;
168 
169         try {
170             session = openSession();
171 
172             StringBuilder query = new StringBuilder();
173 
174             query.append("SELECT COUNT(*) AS COUNT_VALUE FROM ShoppingItem ");
175             query.append("INNER JOIN ShoppingCategory ON ");
176             query.append("ShoppingCategory.categoryId = ");
177             query.append("ShoppingItem.categoryId ");
178             query.append("WHERE ");
179             query.append("ShoppingCategory.groupId = ? AND (");
180 
181             if ((categoryIds != null) && (categoryIds.length > 0)) {
182                 query.append("(");
183 
184                 for (int i = 0; i < categoryIds.length; i++) {
185                     query.append("ShoppingItem.categoryId = ? ");
186 
187                     if (i + 1 < categoryIds.length) {
188                         query.append("OR ");
189                     }
190                 }
191 
192                 query.append(") AND ");
193             }
194 
195             query.append("(ShoppingItem.name LIKE ? OR ");
196             query.append("ShoppingItem.description LIKE ? OR ");
197             query.append("ShoppingItem.properties LIKE ?))");
198 
199             keywords = '%' + keywords + '%';
200 
201             SQLQuery q = session.createSQLQuery(query.toString());
202 
203             q.addScalar(COUNT_COLUMN_NAME, Type.LONG);
204 
205             QueryPos qPos = QueryPos.getInstance(q);
206 
207             qPos.add(groupId);
208 
209             for (int i = 0; i < categoryIds.length; i++) {
210                 qPos.add(categoryIds[i]);
211             }
212 
213             qPos.add(keywords);
214             qPos.add(keywords);
215             qPos.add(keywords);
216 
217             Iterator<Long> itr = q.list().iterator();
218 
219             if (itr.hasNext()) {
220                 Long count = itr.next();
221 
222                 if (count != null) {
223                     return count.intValue();
224                 }
225             }
226 
227             return 0;
228         }
229         catch (Exception e) {
230             throw new SystemException(e);
231         }
232         finally {
233             closeSession(session);
234         }
235     }
236 
237     public int countBySale(long groupId, long[] categoryIds)
238         throws SystemException {
239 
240         Session session = null;
241 
242         try {
243             session = openSession();
244 
245             StringBuilder query = new StringBuilder();
246 
247             query.append("SELECT COUNT(*) AS COUNT_VALUE FROM ShoppingItem ");
248             query.append("INNER JOIN ShoppingCategory ON ");
249             query.append("ShoppingCategory.categoryId = ");
250             query.append("ShoppingItem.categoryId ");
251             query.append("WHERE ");
252             query.append("ShoppingCategory.groupId = ? AND (");
253 
254             if ((categoryIds != null) && (categoryIds.length > 0)) {
255                 query.append("(");
256 
257                 for (int i = 0; i < categoryIds.length; i++) {
258                     query.append("ShoppingItem.categoryId = ? ");
259 
260                     if (i + 1 < categoryIds.length) {
261                         query.append("OR ");
262                     }
263                 }
264 
265                 query.append(") AND ");
266             }
267 
268             query.append("ShoppingItem.sale = ? AND ");
269             query.append("ShoppingItem.smallImage = ?");
270 
271             SQLQuery q = session.createSQLQuery(query.toString());
272 
273             q.addScalar(COUNT_COLUMN_NAME, Type.LONG);
274 
275             QueryPos qPos = QueryPos.getInstance(q);
276 
277             qPos.add(groupId);
278 
279             for (int i = 0; i < categoryIds.length; i++) {
280                 qPos.add(categoryIds[i]);
281             }
282 
283             qPos.add(true);
284             qPos.add(true);
285 
286             Iterator<Long> itr = q.list().iterator();
287 
288             if (itr.hasNext()) {
289                 Long count = itr.next();
290 
291                 if (count != null) {
292                     return count.intValue();
293                 }
294             }
295 
296             return 0;
297         }
298         catch (Exception e) {
299             throw new SystemException(e);
300         }
301         finally {
302             closeSession(session);
303         }
304     }
305 
306     public List<ShoppingItem> findByFeatured(
307             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 = openSession();
316 
317             StringBuilder query = new StringBuilder();
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 (List<ShoppingItem>)QueryUtil.randomList(
359                 q, getDialect(), countByFeatured, numOfItems);
360         }
361         catch (Exception e) {
362             throw new SystemException(e);
363         }
364         finally {
365             closeSession(session);
366         }
367     }
368 
369     public List<ShoppingItem> findByKeywords(
370             long groupId, long[] categoryIds, String keywords, int start,
371             int end)
372         throws SystemException {
373 
374         Session session = null;
375 
376         try {
377             session = openSession();
378 
379             StringBuilder query = new StringBuilder();
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 (List<ShoppingItem>)QueryUtil.list(
425                 q, getDialect(), start, end);
426         }
427         catch (Exception e) {
428             throw new SystemException(e);
429         }
430         finally {
431             closeSession(session);
432         }
433     }
434 
435     public List<ShoppingItem> findBySale(
436             long groupId, long[] categoryIds, int numOfItems)
437         throws SystemException {
438 
439         int countBySale = countBySale(groupId, categoryIds);
440 
441         Session session = null;
442 
443         try {
444             session = openSession();
445 
446             StringBuilder query = new StringBuilder();
447 
448             query.append("SELECT {ShoppingItem.*} FROM ShoppingItem ");
449             query.append("INNER JOIN ShoppingCategory ON ");
450             query.append("ShoppingCategory.categoryId = ");
451             query.append("ShoppingItem.categoryId ");
452             query.append("WHERE ");
453             query.append("ShoppingCategory.groupId = ? AND (");
454 
455             if ((categoryIds != null) && (categoryIds.length > 0)) {
456                 query.append("(");
457 
458                 for (int i = 0; i < categoryIds.length; i++) {
459                     query.append("ShoppingItem.categoryId = ? ");
460 
461                     if (i + 1 < categoryIds.length) {
462                         query.append("OR ");
463                     }
464                 }
465 
466                 query.append(") AND ");
467             }
468 
469             query.append("ShoppingItem.sale = ? AND ");
470             query.append("ShoppingItem.smallImage = ?");
471 
472             SQLQuery q = session.createSQLQuery(query.toString());
473 
474             q.addEntity("ShoppingItem", ShoppingItemImpl.class);
475 
476             QueryPos qPos = QueryPos.getInstance(q);
477 
478             qPos.add(groupId);
479 
480             for (int i = 0; i < categoryIds.length; i++) {
481                 qPos.add(categoryIds[i]);
482             }
483 
484             qPos.add(true);
485             qPos.add(true);
486 
487             return (List<ShoppingItem>)QueryUtil.randomList(
488                 q, getDialect(), countBySale, numOfItems);
489         }
490         catch (Exception e) {
491             throw new SystemException(e);
492         }
493         finally {
494             closeSession(session);
495         }
496     }
497 
498     protected String getCategoryIds(List<Long> categoryIds) {
499         StringBuilder sb = new StringBuilder();
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 }