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