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