1
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
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 }