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