001
014
015 package com.liferay.portlet.shopping.service.persistence;
016
017 import com.liferay.portal.kernel.dao.orm.QueryPos;
018 import com.liferay.portal.kernel.dao.orm.QueryUtil;
019 import com.liferay.portal.kernel.dao.orm.SQLQuery;
020 import com.liferay.portal.kernel.dao.orm.Session;
021 import com.liferay.portal.kernel.dao.orm.Type;
022 import com.liferay.portal.kernel.exception.SystemException;
023 import com.liferay.portal.kernel.util.StringBundler;
024 import com.liferay.portal.kernel.util.StringPool;
025 import com.liferay.portal.kernel.util.StringUtil;
026 import com.liferay.portal.security.permission.InlineSQLHelperUtil;
027 import com.liferay.portal.service.persistence.impl.BasePersistenceImpl;
028 import com.liferay.portlet.shopping.model.ShoppingItem;
029 import com.liferay.portlet.shopping.model.impl.ShoppingItemImpl;
030 import com.liferay.util.dao.orm.CustomSQLUtil;
031
032 import java.util.Iterator;
033 import java.util.List;
034
035
038 public class ShoppingItemFinderImpl
039 extends BasePersistenceImpl<ShoppingItem> implements ShoppingItemFinder {
040
041 public static final String COUNT_BY_G_C =
042 ShoppingItemFinder.class.getName() + ".countByG_C";
043
044 public int countByG_C(long groupId, List<Long> categoryIds)
045 throws SystemException {
046
047 return doCountByG_C(groupId, categoryIds, false);
048 }
049
050 public int countByFeatured(long groupId, long[] categoryIds)
051 throws SystemException {
052
053 Session session = null;
054
055 try {
056 session = openSession();
057
058 StringBundler query = new StringBundler();
059
060 query.append("SELECT COUNT(*) AS COUNT_VALUE FROM ShoppingItem ");
061 query.append("WHERE ");
062 query.append("ShoppingItem.groupId = ? AND (");
063
064 if ((categoryIds != null) && (categoryIds.length > 0)) {
065 query.append("(");
066
067 for (int i = 0; i < categoryIds.length; i++) {
068 query.append("ShoppingItem.categoryId = ? ");
069
070 if (i + 1 < categoryIds.length) {
071 query.append("OR ");
072 }
073 }
074
075 query.append(") AND ");
076 }
077
078 query.append("ShoppingItem.featured = ? AND ");
079 query.append("ShoppingItem.smallImage = ?");
080
081 SQLQuery q = session.createSQLQuery(query.toString());
082
083 q.addScalar(COUNT_COLUMN_NAME, Type.LONG);
084
085 QueryPos qPos = QueryPos.getInstance(q);
086
087 qPos.add(groupId);
088
089 for (long categoryId : categoryIds) {
090 qPos.add(categoryId);
091 }
092
093 qPos.add(true);
094 qPos.add(true);
095
096 Iterator<Long> itr = q.iterate();
097
098 if (itr.hasNext()) {
099 Long count = itr.next();
100
101 if (count != null) {
102 return count.intValue();
103 }
104 }
105
106 return 0;
107 }
108 catch (Exception e) {
109 throw new SystemException(e);
110 }
111 finally {
112 closeSession(session);
113 }
114 }
115
116 public int countByKeywords(
117 long groupId, long[] categoryIds, String keywords)
118 throws SystemException {
119
120 Session session = null;
121
122 try {
123 session = openSession();
124
125 StringBundler query = new StringBundler();
126
127 query.append("SELECT COUNT(*) AS COUNT_VALUE FROM ShoppingItem ");
128 query.append("WHERE ");
129 query.append("ShoppingItem.groupId = ? AND (");
130
131 if ((categoryIds != null) && (categoryIds.length > 0)) {
132 query.append("(");
133
134 for (int i = 0; i < categoryIds.length; i++) {
135 query.append("ShoppingItem.categoryId = ? ");
136
137 if (i + 1 < categoryIds.length) {
138 query.append("OR ");
139 }
140 }
141
142 query.append(") AND ");
143 }
144
145 query.append("(ShoppingItem.name LIKE ? OR ");
146 query.append("ShoppingItem.description LIKE ? OR ");
147 query.append("ShoppingItem.properties LIKE ?))");
148
149 keywords = '%' + keywords + '%';
150
151 SQLQuery q = session.createSQLQuery(query.toString());
152
153 q.addScalar(COUNT_COLUMN_NAME, Type.LONG);
154
155 QueryPos qPos = QueryPos.getInstance(q);
156
157 qPos.add(groupId);
158
159 for (long categoryId : categoryIds) {
160 qPos.add(categoryId);
161 }
162
163 qPos.add(keywords);
164 qPos.add(keywords);
165 qPos.add(keywords);
166
167 Iterator<Long> itr = q.iterate();
168
169 if (itr.hasNext()) {
170 Long count = itr.next();
171
172 if (count != null) {
173 return count.intValue();
174 }
175 }
176
177 return 0;
178 }
179 catch (Exception e) {
180 throw new SystemException(e);
181 }
182 finally {
183 closeSession(session);
184 }
185 }
186
187 public int countBySale(long groupId, long[] categoryIds)
188 throws SystemException {
189
190 Session session = null;
191
192 try {
193 session = openSession();
194
195 StringBundler query = new StringBundler();
196
197 query.append("SELECT COUNT(*) AS COUNT_VALUE FROM ShoppingItem ");
198 query.append("WHERE ");
199 query.append("ShoppingItem.groupId = ? AND (");
200
201 if ((categoryIds != null) && (categoryIds.length > 0)) {
202 query.append("(");
203
204 for (int i = 0; i < categoryIds.length; i++) {
205 query.append("ShoppingItem.categoryId = ? ");
206
207 if (i + 1 < categoryIds.length) {
208 query.append("OR ");
209 }
210 }
211
212 query.append(") AND ");
213 }
214
215 query.append("ShoppingItem.sale = ? AND ");
216 query.append("ShoppingItem.smallImage = ?");
217
218 SQLQuery q = session.createSQLQuery(query.toString());
219
220 q.addScalar(COUNT_COLUMN_NAME, Type.LONG);
221
222 QueryPos qPos = QueryPos.getInstance(q);
223
224 qPos.add(groupId);
225
226 for (long categoryId : categoryIds) {
227 qPos.add(categoryId);
228 }
229
230 qPos.add(true);
231 qPos.add(true);
232
233 Iterator<Long> itr = q.iterate();
234
235 if (itr.hasNext()) {
236 Long count = itr.next();
237
238 if (count != null) {
239 return count.intValue();
240 }
241 }
242
243 return 0;
244 }
245 catch (Exception e) {
246 throw new SystemException(e);
247 }
248 finally {
249 closeSession(session);
250 }
251 }
252
253 public int filterCountByG_C(long groupId, List<Long> categoryIds)
254 throws SystemException {
255
256 return doCountByG_C(groupId, categoryIds, true);
257 }
258
259 public List<ShoppingItem> findByFeatured(
260 long groupId, long[] categoryIds, int numOfItems)
261 throws SystemException {
262
263 int countByFeatured = countByFeatured(groupId, categoryIds);
264
265 Session session = null;
266
267 try {
268 session = openSession();
269
270 StringBundler query = new StringBundler();
271
272 query.append("SELECT {ShoppingItem.*} FROM ShoppingItem ");
273 query.append("WHERE ");
274 query.append("ShoppingItem.groupId = ? AND (");
275
276 if ((categoryIds != null) && (categoryIds.length > 0)) {
277 query.append("(");
278
279 for (int i = 0; i < categoryIds.length; i++) {
280 query.append("ShoppingItem.categoryId = ? ");
281
282 if (i + 1 < categoryIds.length) {
283 query.append("OR ");
284 }
285 }
286
287 query.append(") AND ");
288 }
289
290 query.append("ShoppingItem.featured = ? AND ");
291 query.append("ShoppingItem.smallImage = ?");
292
293 SQLQuery q = session.createSQLQuery(query.toString());
294
295 q.addEntity("ShoppingItem", ShoppingItemImpl.class);
296
297 QueryPos qPos = QueryPos.getInstance(q);
298
299 qPos.add(groupId);
300
301 for (long categoryId : categoryIds) {
302 qPos.add(categoryId);
303 }
304
305 qPos.add(true);
306 qPos.add(true);
307
308 return (List<ShoppingItem>)QueryUtil.randomList(
309 q, getDialect(), countByFeatured, numOfItems);
310 }
311 catch (Exception e) {
312 throw new SystemException(e);
313 }
314 finally {
315 closeSession(session);
316 }
317 }
318
319 public List<ShoppingItem> findByKeywords(
320 long groupId, long[] categoryIds, String keywords, int start,
321 int end)
322 throws SystemException {
323
324 Session session = null;
325
326 try {
327 session = openSession();
328
329 StringBundler query = new StringBundler();
330
331 query.append("SELECT {ShoppingItem.*} FROM ShoppingItem ");
332 query.append("WHERE ");
333 query.append("ShoppingItem.groupId = ? AND (");
334
335 if ((categoryIds != null) && (categoryIds.length > 0)) {
336 query.append("(");
337
338 for (int i = 0; i < categoryIds.length; i++) {
339 query.append("ShoppingItem.categoryId = ? ");
340
341 if (i + 1 < categoryIds.length) {
342 query.append("OR ");
343 }
344 }
345
346 query.append(") AND ");
347 }
348
349 query.append("(ShoppingItem.name LIKE ? OR ");
350 query.append("ShoppingItem.description LIKE ? OR ");
351 query.append("ShoppingItem.properties LIKE ?))");
352
353 keywords = '%' + keywords + '%';
354
355 SQLQuery q = session.createSQLQuery(query.toString());
356
357 q.addEntity("ShoppingItem", ShoppingItemImpl.class);
358
359 QueryPos qPos = QueryPos.getInstance(q);
360
361 qPos.add(groupId);
362
363 for (long categoryId : categoryIds) {
364 qPos.add(categoryId);
365 }
366
367 qPos.add(keywords);
368 qPos.add(keywords);
369 qPos.add(keywords);
370
371 return (List<ShoppingItem>)QueryUtil.list(
372 q, getDialect(), start, end);
373 }
374 catch (Exception e) {
375 throw new SystemException(e);
376 }
377 finally {
378 closeSession(session);
379 }
380 }
381
382 public List<ShoppingItem> findBySale(
383 long groupId, long[] categoryIds, int numOfItems)
384 throws SystemException {
385
386 int countBySale = countBySale(groupId, categoryIds);
387
388 Session session = null;
389
390 try {
391 session = openSession();
392
393 StringBundler query = new StringBundler();
394
395 query.append("SELECT {ShoppingItem.*} FROM ShoppingItem ");
396 query.append("WHERE ");
397 query.append("ShoppingItem.groupId = ? AND (");
398
399 if ((categoryIds != null) && (categoryIds.length > 0)) {
400 query.append("(");
401
402 for (int i = 0; i < categoryIds.length; i++) {
403 query.append("ShoppingItem.categoryId = ? ");
404
405 if (i + 1 < categoryIds.length) {
406 query.append("OR ");
407 }
408 }
409
410 query.append(") AND ");
411 }
412
413 query.append("ShoppingItem.sale = ? AND ");
414 query.append("ShoppingItem.smallImage = ?");
415
416 SQLQuery q = session.createSQLQuery(query.toString());
417
418 q.addEntity("ShoppingItem", ShoppingItemImpl.class);
419
420 QueryPos qPos = QueryPos.getInstance(q);
421
422 qPos.add(groupId);
423
424 for (long categoryId : categoryIds) {
425 qPos.add(categoryId);
426 }
427
428 qPos.add(true);
429 qPos.add(true);
430
431 return (List<ShoppingItem>)QueryUtil.randomList(
432 q, getDialect(), countBySale, numOfItems);
433 }
434 catch (Exception e) {
435 throw new SystemException(e);
436 }
437 finally {
438 closeSession(session);
439 }
440 }
441
442 protected int doCountByG_C(
443 long groupId, List<Long> categoryIds, boolean inlineSQLHelper)
444 throws SystemException {
445
446 Session session = null;
447
448 try {
449 session = openSession();
450
451 String sql = CustomSQLUtil.get(COUNT_BY_G_C);
452
453 if (inlineSQLHelper) {
454 sql = InlineSQLHelperUtil.replacePermissionCheck(
455 sql, ShoppingItem.class.getName(), "ShoppingItem.itemId",
456 groupId);
457 }
458
459 sql = StringUtil.replace(
460 sql, "[$CATEGORY_ID$]", getCategoryIds(categoryIds));
461
462 SQLQuery q = session.createSQLQuery(sql);
463
464 q.addScalar(COUNT_COLUMN_NAME, Type.LONG);
465
466 QueryPos qPos = QueryPos.getInstance(q);
467
468 qPos.add(groupId);
469
470 for (int i = 0; i < categoryIds.size(); i++) {
471 Long categoryId = categoryIds.get(i);
472
473 qPos.add(categoryId);
474 }
475
476 Iterator<Long> itr = q.iterate();
477
478 if (itr.hasNext()) {
479 Long count = itr.next();
480
481 if (count != null) {
482 return count.intValue();
483 }
484 }
485
486 return 0;
487 }
488 catch (Exception e) {
489 throw new SystemException(e);
490 }
491 finally {
492 closeSession(session);
493 }
494 }
495
496 protected String getCategoryIds(List<Long> categoryIds) {
497 if (categoryIds.isEmpty()) {
498 return StringPool.BLANK;
499 }
500
501 StringBundler sb = new StringBundler(categoryIds.size() * 2 - 1);
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 }