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