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