List of usage examples for org.hibernate ScrollableResults last
boolean last();
From source file:net.firejack.platform.core.store.BaseStore.java
License:Apache License
@SuppressWarnings("unchecked") @Transactional(readOnly = true)/*ww w . jav a 2 s. com*/ public int count(final String queryName, final boolean useScroll, final Object... params) throws DataAccessException { return (Integer) getHibernateTemplate().execute(new HibernateCallback() { public Object doInHibernate(Session session) throws HibernateException, SQLException { Query query = session.getNamedQuery(queryName); setQueryParams(query, params); Integer count; if (useScroll) { ScrollableResults scroll = query.scroll(); count = scroll.last() ? scroll.getRowNumber() + 1 : 0; } else { count = ((Number) query.uniqueResult()).intValue(); } return count; } }); }
From source file:net.mlw.vlh.adapter.hibernate3.HibernateAdapter.java
License:Open Source License
/** * @see net.mlw.vlh.ValueListAdapter#getValueList(java.lang.String, * net.mlw.vlh.ValueListInfo)/*from w w w .ja va 2s. c om*/ */ public ValueList getValueList(String name, ValueListInfo info) { LOGGER.debug("getValueList(String, ValueListInfo) - start"); if (info.getSortingColumn() == null) { info.setPrimarySortColumn(getDefaultSortColumn()); info.setPrimarySortDirection(getDefaultSortDirectionInteger()); if (LOGGER.isDebugEnabled()) { LOGGER.debug("The default sort column '" + getDefaultSortColumn() + "' with direction '" + getDefaultSortDirectionInteger() + "' was set."); } } int numberPerPage = info.getPagingNumberPer(); if (numberPerPage == Integer.MAX_VALUE) { numberPerPage = getDefaultNumberPerPage(); info.setPagingNumberPer(numberPerPage); if (LOGGER.isDebugEnabled()) { LOGGER.debug("The paging number per page '" + numberPerPage + "' was set."); } } Session session = SessionFactoryUtils.getSession(getSessionFactory(), allowCreate); try { Query query; boolean doFocus = ((getAdapterType() & DO_FOCUS) == 0) && info.isFocusEnabled() && info.isDoFocus() && (namedQuery == null); if (doFocus) { if (LOGGER.isDebugEnabled()) { LOGGER.debug( "Start to focusing adapterName '" + name + "', ValueListInfo info = " + info + "'"); } ScrollableResults results = getScrollableResults(getQueryForFocus(info, session), info); results.beforeFirst(); doFocusFor(info, results); if (LOGGER.isDebugEnabled()) { LOGGER.debug( "Focusing finished for adapterName '" + name + "', ValueListInfo info '" + info + "'"); } } query = getQuery(info, session); boolean doPaging = ((getAdapterType() & DO_PAGE) == 0); List list; if (doPaging) { if (LOGGER.isDebugEnabled()) { LOGGER.debug("getValueList(String adapterName = " + name + ", ValueListInfo info = " + info + ") - Start to paging result set"); } list = new ArrayList(numberPerPage); ScrollableResults results = getScrollableResults(query, info); results.last(); int lastRowNumber = results.getRowNumber(); info.setTotalNumberOfEntries(lastRowNumber + 1); if (numberPerPage == 0) { numberPerPage = getDefaultNumberPerPage(); } int pageNumber = info.getPagingPage(); boolean isResult; if (pageNumber > 1) { if ((pageNumber - 1) * numberPerPage > lastRowNumber) { pageNumber = (lastRowNumber / numberPerPage) + 1; info.setPagingPage(pageNumber); } } // fixed by liujuan 2008.6.5 isResult = results.first(); if (pageNumber > 1) { // isResult = results.scroll((pageNumber - 1) * numberPerPage - lastRowNumber); isResult = results.scroll((pageNumber - 1) * numberPerPage); } /*else { isResult = results.first(); }*/ for (int i = 0; i < numberPerPage && isResult; i++) { list.add(results.get(0)); isResult = results.next(); } LOGGER.debug("Sorting finished."); } else { LOGGER.debug("Retrieving a list directly from the query."); list = query.list(); info.setTotalNumberOfEntries(list.size()); } ValueList returnValueList = getListBackedValueList(info, list); if (LOGGER.isDebugEnabled()) { LOGGER.debug("Retrieved list was wrapped in valuelist, info=" + info); } return returnValueList; } catch (HibernateException e) { LOGGER.error("Error getting data in adapater '" + name + "' with info = '" + info + "'", e); throw SessionFactoryUtils.convertHibernateAccessException(e); } catch (Exception e) { LOGGER.fatal("Fatal error getting data in adapater '" + name + "' with info = '" + info + "'", e); return null; } finally { SessionFactoryUtils.releaseSession(session, getSessionFactory()); } }
From source file:net.sf.oreka.services.RecSegmentServiceHbn.java
License:Open Source License
public int getResults(RecSegmentFilter filter, int offset, int number, String orderBy, boolean ascending, List results) {//ww w . j av a 2s. c o m firstCriterium = true; int numResults = 0; logger.log(Level.DEBUG, "Entering getResults"); //logger.log(Level.INFO, System.getProperty("java.class.path")); //RecSegment seg1 = new RecSegment(); //RecSegment seg2 = new RecSegment(); //RecTape tape1 = new RecTape(); //RecTape tape2 = new RecTape(); /* RecSegmentResult result1 = new RecSegmentResult(); RecSegmentResult result2 = new RecSegmentResult(); result1.getRecSegment().setDuration(10); result1.getRecSegment().setLocalParty("01223"); results.add(result1); result2.getRecSegment().setDuration(11); result2.getRecSegment().setLocalParty("01440"); results.add(result2); */ /* for (int i=0; i<number; i++) { RecSegmentResult result = new RecSegmentResult(); result.getRecSegment().setDuration(offset + i); result.getRecSegment().setLocalParty(orderBy); result.getRecTape().setId(ascending ? 0:1); results.add(result); } numResults = 502; */ Transaction tx = null; Session session = null; try { session = OrkWeb.hibernateManager.getSession(); StringBuffer queryString = new StringBuffer( "from OrkSegment as seg left join seg.tape as tape left join tape.service as srv "); //StringBuffer queryString = new StringBuffer("from RecSegment as seg "); //boolean firstCriterium = false; if (filter.getStartDate() != null && filter.getEndDate() != null) queryString.append(" where seg.timestamp between :startDate and :endDate "); else if (filter.getStartDate() != null) queryString.append(" where seg.timestamp > :startDate "); else if (filter.getEndDate() != null) queryString.append(" where seg.timestamp < :endDate "); if (filter.getLocalParty().length() > 0) { queryString.append(" and seg.localParty=:localParty "); } if (filter.getRemoteParty().length() > 0) { queryString.append(" and seg.remoteParty=:remoteParty "); } if (filter.getMinDuration().length() > 0) { queryString.append(" and seg.duration>:minDuration "); } if (filter.getMaxDuration().length() > 0) { queryString.append(" and seg.duration<:maxDuration "); } if (filter.getDirection() != Direction.ALL) { queryString.append(" and seg.direction=:direction "); } if (orderBy.length() == 0) { orderBy = "seg.timestamp"; } queryString.append(" order by "); queryString.append(orderBy); if (ascending) { queryString.append(" asc"); } else { queryString.append(" desc"); } Query query = session.createQuery(queryString.toString()); SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss"); if (filter.getStartDate() != null) { logger.debug("Filter start date:" + dateFormat.format(filter.getStartDate())); query.setTimestamp("startDate", filter.getStartDate()); } if (filter.getEndDate() != null) { logger.debug("Filter end date:" + dateFormat.format(filter.getEndDate())); query.setTimestamp("endDate", filter.getEndDate()); } if (filter.getLocalParty().length() > 0) { query.setString("localParty", filter.getLocalParty()); } if (filter.getRemoteParty().length() > 0) { query.setString("remoteParty", filter.getRemoteParty()); } if (filter.getMinDuration().length() > 0) { query.setString("minDuration", filter.getMinDuration()); } if (filter.getMaxDuration().length() > 0) { query.setString("maxDuration", filter.getMaxDuration()); } if (filter.getDirection() != Direction.ALL) { query.setParameter("direction", filter.getDirection().ordinal()); //query.setParameter( "direction", filter.getDirection().name() ); } // Criteria crit = session.createCriteria(RecSegment.class); // //crit.setFetchMode("RecTape",FetchMode.EAGER); // crit.setFetchMode(null, FetchMode.LAZY); ScrollableResults scrollDocs = query.scroll(); if (scrollDocs.last()) { numResults = scrollDocs.getRowNumber() + 1; logger.debug("Num res:" + numResults); } //scrollDocs.beforeFirst(); scrollDocs.setRowNumber(offset); int rowsSoFar = 0; while (scrollDocs.get() != null && rowsSoFar < number) { rowsSoFar++; OrkSegment seg = (OrkSegment) scrollDocs.get(0); //logger.log(Level.ERROR, seg.getRecTape().getUrl()); //RecTape tape = (RecTape)scrollDocs.get(1); //RecTape tape = new RecTape(); RecSegmentResult res = new RecSegmentResult(); res.setRecSegment(seg); //res.setRecTape(tape); results.add(res); scrollDocs.next(); } } catch (HibernateException he) { if (tx != null) tx.rollback(); logger.log(Level.ERROR, he.toString()); he.printStackTrace(); } catch (Exception e) { logger.error(e); e.printStackTrace(); } finally { session.close(); } return numResults; }
From source file:org.esupportail.commons.dao.AbstractHibernateQueryPaginator.java
License:Apache License
/** * @see org.esupportail.commons.web.beans.AbstractPaginator#loadItemsInternal() */// w w w . ja va2 s .co m @Override @SuppressWarnings("unchecked") protected void loadItemsInternal() { String queryString = getQueryString(); if (queryString == null) { setVisibleItems(new ArrayList<E>()); setCurrentPageInternal(0); setTotalItemsCount(0); return; } Query query = getDaoService().getQuery(queryString); ScrollableResults scrollableResults = query.scroll(); /* * We set the max results to one more than the specfied pageSize to * determine if any more results exist (i.e. if there is a next page * to display). The result set is trimmed down to just the pageSize * before being displayed later (in getList()). */ if (logger.isDebugEnabled()) { logger.debug("executing " + query.getQueryString() + "..."); } query.setFirstResult(getCurrentPageInternal() * getPageSize()); query.setMaxResults(getPageSize()); setVisibleItems(query.list()); // the total number of results is computed here since scrolling is not allowed when rendering scrollableResults.last(); setTotalItemsCount(scrollableResults.getRowNumber() + 1); if (logger.isDebugEnabled()) { logger.debug("done."); } if (getVisibleItemsCountInternal() == 0 && getTotalItemsCountInternal() != 0) { setCurrentPageInternal(getLastPageNumberInternal()); loadItemsInternal(); } updateLoadTime(); }
From source file:org.eurocarbdb.action.core.SearchGlycanSequence.java
License:Open Source License
public List<GlycanSequence> getQueryResults() { Criteria query = createCriteria();/*from w w w . j av a2 s. com*/ log.info("Performing query: " + query.toString()); setMessage(query.toString()); ScrollableResults scroll = null; try { scroll = query.scroll(); scroll.last(); setTotalResults(scroll.getRowNumber() + 1); int count = getTotalResults(); int first = getOffset(); int max = getMaxResults(); if (first > 0) { query.setFirstResult(first); } if (max > 0) { query.setMaxResults(max); } List<GlycanSequence> ret = (List<GlycanSequence>) query.list(); log.debug("query executed ok, results count=" + ret.size()); return ret; } catch (HibernateException e) { log.warn("Caught " + e.getClass().getName() + " performing query:", e); return Collections.emptyList(); } finally { if (scroll != null) scroll.close(); } }
From source file:org.glite.security.voms.admin.persistence.tools.DeleteRecordsCommand.java
License:Apache License
@Override public void execute(CommandLine line) { AuditSearchDAO dao = DAOFactory.instance().getAuditSearchDAO(); AuditLogSearchParams params = addConstraintsFromCommandLine(line); ScrollableAuditLogSearchResults results = dao.scrollEventsMatchingParams(params, ScrollMode.SCROLL_INSENSITIVE); int count = 0; ScrollableResults scroll = results.getResults(); scroll.last(); count = scroll.getRowNumber();//ww w . j a v a 2 s . c o m if (count + 1 == 0) { System.out.println("No events found"); return; } System.out.format("%d records will be deleted. Type 'yes' if you want to continue\n", count + 1); try { if (!getConfirmationFromUser()) { System.out.println("Aborting on user's request."); return; } } catch (IOException e) { throw new AuditLogCommandError(e); } scroll.beforeFirst(); System.out.println("Deleting events:"); count = 0; while (scroll.next()) { count++; AuditEvent event = (AuditEvent) scroll.get()[0]; printAuditLogEvent(event); dao.makeTransient(event); dao.flush(); HibernateFactory.getSession().evict(event); } System.out.format("%d events deleted.\n", count); }
From source file:org.openbravo.service.datasource.HQLDataSourceService.java
License:Open Source License
protected int getGroupedCount(Query countQuery) { int nRows = -1; ScrollableResults scrollableResults = countQuery.scroll(); if (scrollableResults.last()) { nRows = scrollableResults.getRowNumber(); }/*from w w w .j a v a 2 s . co m*/ scrollableResults.close(); return nRows + 1; }
From source file:org.opentaps.common.domain.order.PurchaseOrderLookupRepository.java
License:Open Source License
/** {@inheritDoc} */ public List<OrderViewForListing> findOrders() throws RepositoryException { // convert fromDateStr / thruDateStr into Timestamps if the string versions were given if (UtilValidate.isNotEmpty(fromDateStr)) { fromDate = UtilDate.toTimestamp(fromDateStr, timeZone, locale); }//from w w w. ja va 2s . c om if (UtilValidate.isNotEmpty(thruDateStr)) { thruDate = UtilDate.toTimestamp(thruDateStr, timeZone, locale); } Session session = null; try { // get a hibernate session session = getInfrastructure().getSession(); Criteria criteria = session.createCriteria(OrderHeader.class); // always filter by the current organization criteria.add(Restrictions.eq(OrderHeader.Fields.billToPartyId.name(), organizationPartyId)); // filters by order type, we only want purchase order criteria.add(Restrictions.eq(OrderHeader.Fields.orderTypeId.name(), OrderTypeConstants.PURCHASE_ORDER)); // set the from/thru date filter if they were given if (fromDate != null) { criteria.add(Restrictions.ge(OrderHeader.Fields.orderDate.name(), fromDate)); } if (thruDate != null) { criteria.add(Restrictions.le(OrderHeader.Fields.orderDate.name(), thruDate)); } // filter the role assoc, there is only one supplier role per order Criteria roleCriteria = criteria.createAlias("orderRoles", "or"); roleCriteria.add(Restrictions.eq("or.id." + OrderRole.Fields.roleTypeId.name(), RoleTypeConstants.BILL_FROM_VENDOR)); // filter by order status if (findDesiredOnly) { List<String> statuses = UtilMisc.toList(StatusItemConstants.OrderStatus.ORDER_APPROVED, StatusItemConstants.OrderStatus.ORDER_CREATED, StatusItemConstants.OrderStatus.ORDER_HOLD); criteria.add(Restrictions.in(OrderHeader.Fields.statusId.name(), statuses)); } // filter by the given orderId string if (UtilValidate.isNotEmpty(orderId)) { criteria.add(Restrictions.ilike(OrderHeader.Fields.orderId.name(), orderId, MatchMode.START)); } // filter by exact matching status, if a statusId was given if (UtilValidate.isNotEmpty(statusId)) { criteria.add(Restrictions.eq(OrderHeader.Fields.statusId.name(), statusId)); } // filter by the user who created the order if given if (UtilValidate.isNotEmpty(createdBy)) { criteria.add(Restrictions.eq(OrderHeader.Fields.createdBy.name(), createdBy)); } // filter by the given orderName string if (UtilValidate.isNotEmpty(orderName)) { criteria.add(Restrictions.ilike(OrderHeader.Fields.orderName.name(), orderName, MatchMode.START)); } // filter by the given supplierPartyId string, from the OrderRole entity if (UtilValidate.isNotEmpty(supplierPartyId)) { roleCriteria.add(Restrictions.ilike("or.id." + OrderRole.Fields.partyId.name(), supplierPartyId, MatchMode.START)); } // filter by product, if given criteria.createAlias("orderItems", "oi"); if (UtilValidate.isNotEmpty(productPattern)) { try { // try to get product by using productPattern as productId Product product = getProductRepository().getProductById(productPattern); criteria.add( Restrictions.eq("oi." + OrderItem.Fields.productId.name(), product.getProductId())); } catch (EntityNotFoundException e) { // could not get the product by using productPattern as productId // find all the products that may match String likePattern = "%" + productPattern + "%"; EntityCondition conditionList = EntityCondition.makeCondition(EntityOperator.OR, EntityCondition.makeCondition(ProductAndGoodIdentification.Fields.productId.getName(), EntityOperator.LIKE, likePattern), EntityCondition.makeCondition( ProductAndGoodIdentification.Fields.internalName.getName(), EntityOperator.LIKE, likePattern), EntityCondition.makeCondition(ProductAndGoodIdentification.Fields.productName.getName(), EntityOperator.LIKE, likePattern), EntityCondition.makeCondition(ProductAndGoodIdentification.Fields.comments.getName(), EntityOperator.LIKE, likePattern), EntityCondition.makeCondition(ProductAndGoodIdentification.Fields.description.getName(), EntityOperator.LIKE, likePattern), EntityCondition.makeCondition( ProductAndGoodIdentification.Fields.longDescription.getName(), EntityOperator.LIKE, likePattern), EntityCondition.makeCondition(ProductAndGoodIdentification.Fields.idValue.getName(), EntityOperator.LIKE, likePattern)); List<ProductAndGoodIdentification> products = findList(ProductAndGoodIdentification.class, conditionList); if (products.size() > 0) { criteria.add(Restrictions.in("oi." + OrderItem.Fields.productId.name(), Entity .getDistinctFieldValues(products, ProductAndGoodIdentification.Fields.productId))); } } } // specify the fields to return criteria.setProjection(Projections.projectionList() .add(Projections.distinct(Projections.property(OrderHeader.Fields.orderId.name()))) .add(Projections.property(OrderHeader.Fields.orderName.name())) .add(Projections.property(OrderHeader.Fields.statusId.name())) .add(Projections.property(OrderHeader.Fields.grandTotal.name())) .add(Projections.property(OrderHeader.Fields.orderDate.name())) .add(Projections.property(OrderHeader.Fields.currencyUom.name())) .add(Projections.property("or.id." + OrderRole.Fields.partyId.name()))); // set the order by if (orderBy == null) { orderBy = Arrays.asList(OrderHeader.Fields.orderDate.desc()); } // some substitution is needed to fit the hibernate field names // this also maps the calculated fields and indicates the non sortable fields Map<String, String> subs = new HashMap<String, String>(); subs.put("partyId", "or.id.partyId"); subs.put("partyName", "or.id.partyId"); subs.put("orderDateString", "orderDate"); subs.put("orderNameId", "orderId"); subs.put("statusDescription", "statusId"); HibernateUtil.setCriteriaOrder(criteria, orderBy, subs); ScrollableResults results = null; List<OrderViewForListing> results2 = new ArrayList<OrderViewForListing>(); try { // fetch the paginated results results = criteria.scroll(ScrollMode.SCROLL_INSENSITIVE); if (usePagination()) { results.setRowNumber(getPageStart()); } else { results.first(); } // convert them into OrderViewForListing objects which will also calculate or format some fields for display Object[] o = results.get(); int n = 0; // number of results actually read while (o != null) { OrderViewForListing r = new OrderViewForListing(); r.initRepository(this); int i = 0; r.setOrderId((String) o[i++]); r.setOrderName((String) o[i++]); r.setStatusId((String) o[i++]); r.setGrandTotal((BigDecimal) o[i++]); r.setOrderDate((Timestamp) o[i++]); r.setCurrencyUom((String) o[i++]); r.setPartyId((String) o[i++]); r.calculateExtraFields(getDelegator(), timeZone, locale); results2.add(r); n++; if (!results.next()) { break; } if (usePagination() && n >= getPageSize()) { break; } o = results.get(); } results.last(); // note: row number starts at 0 setResultSize(results.getRowNumber() + 1); } finally { results.close(); } return results2; } catch (InfrastructureException e) { throw new RepositoryException(e); } finally { if (session != null) { session.close(); } } }
From source file:org.opentaps.common.domain.order.SalesOrderLookupRepository.java
License:Open Source License
/** {@inheritDoc} */ public List<OrderViewForListing> findOrders() throws RepositoryException { // convert fromDateStr / thruDateStr into Timestamps if the string versions were given if (UtilValidate.isNotEmpty(fromDateStr)) { fromDate = UtilDate.toTimestamp(fromDateStr, timeZone, locale); }/*w w w.j av a 2 s .c om*/ if (UtilValidate.isNotEmpty(thruDateStr)) { thruDate = UtilDate.toTimestamp(thruDateStr, timeZone, locale); } Session session = null; try { // get a hibernate session session = getInfrastructure().getSession(); Criteria criteria = session.createCriteria(OrderHeader.class); // always filter by the current organization criteria.add(Restrictions.eq(OrderHeader.Fields.billFromPartyId.name(), organizationPartyId)); // filters by order type, we only want sales order criteria.add(Restrictions.eq(OrderHeader.Fields.orderTypeId.name(), OrderTypeConstants.SALES_ORDER)); // set the from/thru date filter if they were given if (fromDate != null) { criteria.add(Restrictions.ge(OrderHeader.Fields.orderDate.name(), fromDate)); } if (thruDate != null) { criteria.add(Restrictions.le(OrderHeader.Fields.orderDate.name(), thruDate)); } // filter the role assoc, there is only one customer role per order Criteria roleCriteria = criteria.createAlias("orderRoles", "or"); roleCriteria.add(Restrictions.eq("or.id." + OrderRole.Fields.roleTypeId.name(), RoleTypeConstants.BILL_TO_CUSTOMER)); // filter orders created by the given user (TODO: what use is viewPref as a string here, should be a boolean flag instead ?) if (UtilValidate.isNotEmpty(viewPref)) { criteria.add(Restrictions.eq(OrderHeader.Fields.createdBy.name(), userLoginId)); } // filter by order status if (findActiveOnly || findDesiredOnly) { List<String> statuses = UtilMisc.toList(StatusItemConstants.OrderStatus.ORDER_APPROVED, StatusItemConstants.OrderStatus.ORDER_CREATED, StatusItemConstants.OrderStatus.ORDER_HOLD); if (findActiveOnly) { statuses.add(StatusItemConstants.OrderStatus.ORDER_PROCESSING); } criteria.add(Restrictions.in(OrderHeader.Fields.statusId.name(), statuses)); } // filter by the given orderId string if (UtilValidate.isNotEmpty(orderId)) { criteria.add(Restrictions.ilike(OrderHeader.Fields.orderId.name(), orderId, MatchMode.START)); } // filter by the given externalOrderId string if (UtilValidate.isNotEmpty(externalOrderId)) { criteria.add( Restrictions.ilike(OrderHeader.Fields.externalId.name(), externalOrderId, MatchMode.START)); } // filter by exact matching status, if a statusId was given if (UtilValidate.isNotEmpty(statusId)) { criteria.add(Restrictions.eq(OrderHeader.Fields.statusId.name(), statusId)); } // filter by product store if given if (UtilValidate.isNotEmpty(productStoreId)) { criteria.add(Restrictions.eq(OrderHeader.Fields.productStoreId.name(), productStoreId)); } // filter by the user who created the order if given if (UtilValidate.isNotEmpty(createdBy)) { criteria.add(Restrictions.eq(OrderHeader.Fields.createdBy.name(), createdBy)); } // filter by the given orderName string if (UtilValidate.isNotEmpty(orderName)) { criteria.add(Restrictions.ilike(OrderHeader.Fields.orderName.name(), orderName, MatchMode.START)); } // filter by the given customerPartyId string, from the OrderRole entity if (UtilValidate.isNotEmpty(customerPartyId)) { roleCriteria.add(Restrictions.ilike("or.id." + OrderRole.Fields.partyId.name(), customerPartyId, MatchMode.START)); } // filter by the given purchaseOrderId string, from the OrderItem entity criteria.createAlias("orderItems", "oi"); if (UtilValidate.isNotEmpty(purchaseOrderId)) { criteria.add(Restrictions.ilike("oi." + OrderItem.Fields.correspondingPoId.name(), purchaseOrderId, MatchMode.START)); } // filter by the given productId string, from the OrderItem entity if (UtilValidate.isNotEmpty(productId)) { criteria.add( Restrictions.ilike("oi." + OrderItem.Fields.productId.name(), productId, MatchMode.START)); } // filter by the given shippingAddress string, from the OrderItemShipGroup entity criteria.createAlias("orderItemShipGroups", "oisg"); Criteria address = criteria.createCriteria("oisg.postalAddress"); if (UtilValidate.isNotEmpty(shippingAddress)) { address.add(Restrictions.ilike(PostalAddress.Fields.address1.name(), shippingAddress, MatchMode.ANYWHERE)); } if (UtilValidate.isNotEmpty(shippingCountry)) { address.add(Restrictions.ilike(PostalAddress.Fields.countryGeoId.name(), shippingCountry, MatchMode.EXACT)); } if (UtilValidate.isNotEmpty(shippingStateProvince)) { address.add(Restrictions.ilike(PostalAddress.Fields.stateProvinceGeoId.name(), shippingStateProvince, MatchMode.EXACT)); } if (UtilValidate.isNotEmpty(shippingCity)) { address.add(Restrictions.ilike(PostalAddress.Fields.city.name(), shippingCity, MatchMode.START)); } if (UtilValidate.isNotEmpty(shippingPostalCode)) { address.add(Restrictions.ilike(PostalAddress.Fields.postalCode.name(), shippingPostalCode, MatchMode.START)); } if (UtilValidate.isNotEmpty(shippingToName)) { address.add( Restrictions.ilike(PostalAddress.Fields.toName.name(), shippingToName, MatchMode.START)); } if (UtilValidate.isNotEmpty(shippingAttnName)) { address.add(Restrictions.ilike(PostalAddress.Fields.attnName.name(), shippingAttnName, MatchMode.START)); } // filter by the given lotId and serialNumber, which may come either from // OrderItemShipGrpInvRes -> InventoryItem // or // ItemIssuance -> InventoryItem criteria.createCriteria("orderItemShipGrpInvReses", Criteria.LEFT_JOIN).createCriteria("inventoryItem", "rii", Criteria.LEFT_JOIN); criteria.createCriteria("itemIssuances", Criteria.LEFT_JOIN).createCriteria("inventoryItem", "iii", Criteria.LEFT_JOIN); if (UtilValidate.isNotEmpty(lotId)) { criteria.add(Restrictions.or( Restrictions.ilike("rii." + InventoryItem.Fields.lotId.name(), lotId, MatchMode.START), Restrictions.ilike("iii." + InventoryItem.Fields.lotId.name(), lotId, MatchMode.START))); } if (UtilValidate.isNotEmpty(serialNumber)) { criteria.add(Restrictions.or( Restrictions.ilike("rii." + InventoryItem.Fields.serialNumber.name(), serialNumber, MatchMode.START), Restrictions.ilike("iii." + InventoryItem.Fields.serialNumber.name(), serialNumber, MatchMode.START))); } criteria.createCriteria("trackingCodeOrders", "tco", Criteria.LEFT_JOIN); // specify the fields to return criteria.setProjection(Projections.projectionList() .add(Projections.distinct(Projections.property(OrderHeader.Fields.orderId.name()))) .add(Projections.property(OrderHeader.Fields.orderName.name())) .add(Projections.property(OrderHeader.Fields.statusId.name())) .add(Projections.property(OrderHeader.Fields.grandTotal.name())) .add(Projections.property(OrderHeader.Fields.orderDate.name())) .add(Projections.property(OrderHeader.Fields.currencyUom.name())) .add(Projections.property("or.id." + OrderRole.Fields.partyId.name())) .add(Projections.property("oi." + OrderItem.Fields.correspondingPoId.name())) .add(Projections.property("tco." + TrackingCodeOrder.Fields.trackingCodeId.name()))); Debug.logInfo("criteria.toString() : " + criteria.toString(), MODULE); // set the order by if (orderBy == null) { orderBy = Arrays.asList(OrderHeader.Fields.orderDate.desc()); } // some substitution is needed to fit the hibernate field names // this also maps the calculated fields and indicates the non sortable fields Map<String, String> subs = new HashMap<String, String>(); subs.put("partyId", "or.id.partyId"); subs.put("partyName", "or.id.partyId"); subs.put("orderDateString", "orderDate"); subs.put("shipByDateString", null); subs.put("orderNameId", "orderId"); subs.put("statusDescription", "statusId"); subs.put("correspondingPoId", "oi.correspondingPoId"); subs.put("trackingCodeId", "tco.trackingCodeId"); HibernateUtil.setCriteriaOrder(criteria, orderBy, subs); ScrollableResults results = null; List<OrderViewForListing> results2 = new ArrayList<OrderViewForListing>(); try { // fetch the paginated results results = criteria.scroll(ScrollMode.SCROLL_INSENSITIVE); if (usePagination()) { results.setRowNumber(getPageStart()); } else { results.first(); } // convert them into OrderViewForListing objects which will also calculate or format some fields for display Object[] o = results.get(); int n = 0; // number of results actually read while (o != null) { OrderViewForListing r = new OrderViewForListing(); r.initRepository(this); int i = 0; r.setOrderId((String) o[i++]); r.setOrderName((String) o[i++]); r.setStatusId((String) o[i++]); r.setGrandTotal((BigDecimal) o[i++]); r.setOrderDate((Timestamp) o[i++]); r.setCurrencyUom((String) o[i++]); r.setPartyId((String) o[i++]); r.setCorrespondingPoId((String) o[i++]); r.setTrackingCodeId((String) o[i++]); r.calculateExtraFields(getDelegator(), timeZone, locale); results2.add(r); n++; if (!results.next()) { break; } if (usePagination() && n >= getPageSize()) { break; } o = results.get(); } results.last(); // note: row number starts at 0 setResultSize(results.getRowNumber() + 1); } finally { if (results != null) { results.close(); } } return results2; } catch (InfrastructureException e) { Debug.logError(e, MODULE); throw new RepositoryException(e); } finally { if (session != null) { session.close(); } } }
From source file:org.opentaps.financials.domain.billing.invoice.InvoiceLookupRepository.java
License:Open Source License
/** {@inheritDoc} */ public List<InvoiceViewForListing> findInvoices() throws RepositoryException { // convert from / thru from String into Timestamps if the string versions were given if (UtilValidate.isNotEmpty(fromInvoiceDateString)) { fromInvoiceDate = UtilDate.toTimestamp(fromInvoiceDateString, timeZone, locale); }//from ww w . j a v a2 s.c o m if (UtilValidate.isNotEmpty(thruInvoiceDateString)) { thruInvoiceDate = UtilDate.toTimestamp(thruInvoiceDateString, timeZone, locale); } if (UtilValidate.isNotEmpty(fromDueDateString)) { fromDueDate = UtilDate.toTimestamp(fromDueDateString, timeZone, locale); } if (UtilValidate.isNotEmpty(thruDueDateString)) { thruDueDate = UtilDate.toTimestamp(thruDueDateString, timeZone, locale); } if (UtilValidate.isNotEmpty(fromPaidDateString)) { fromPaidDate = UtilDate.toTimestamp(fromPaidDateString, timeZone, locale); } if (UtilValidate.isNotEmpty(thruPaidDateString)) { thruPaidDate = UtilDate.toTimestamp(thruPaidDateString, timeZone, locale); } Session session = null; try { // get a hibernate session session = getInfrastructure().getSession(); Criteria criteria = session.createCriteria(Invoice.class); // always filter by invoice type criteria.add(Restrictions.eq(Invoice.Fields.invoiceTypeId.name(), invoiceTypeId)); // some id filters if (UtilValidate.isNotEmpty(partyId)) { criteria.add(Restrictions.eq(Invoice.Fields.partyId.name(), partyId)); } if (UtilValidate.isNotEmpty(partyIdFrom)) { criteria.add(Restrictions.eq(Invoice.Fields.partyIdFrom.name(), partyIdFrom)); } if (UtilValidate.isNotEmpty(invoiceId)) { criteria.add(Restrictions.eq(Invoice.Fields.invoiceId.name(), invoiceId)); } if (UtilValidate.isNotEmpty(statusId)) { criteria.add(Restrictions.eq(Invoice.Fields.statusId.name(), statusId)); } if (UtilValidate.isNotEmpty(processingStatusId)) { // this is a special case where we want an empty status if ("_NA_".equals(processingStatusId)) { criteria.add(Restrictions.eq(Invoice.Fields.processingStatusId.name(), null)); } else { criteria.add(Restrictions.eq(Invoice.Fields.processingStatusId.name(), processingStatusId)); } } // set the from/thru date filter if they were given if (fromInvoiceDate != null) { criteria.add(Restrictions.ge(Invoice.Fields.invoiceDate.name(), fromInvoiceDate)); } if (thruInvoiceDate != null) { criteria.add(Restrictions.le(Invoice.Fields.invoiceDate.name(), thruInvoiceDate)); } if (fromDueDate != null) { criteria.add(Restrictions.ge(Invoice.Fields.dueDate.name(), fromDueDate)); } if (thruDueDate != null) { criteria.add(Restrictions.le(Invoice.Fields.dueDate.name(), thruDueDate)); } if (fromPaidDate != null) { criteria.add(Restrictions.ge(Invoice.Fields.paidDate.name(), fromPaidDate)); } if (thruPaidDate != null) { criteria.add(Restrictions.le(Invoice.Fields.paidDate.name(), thruPaidDate)); } // set the from/thru amount filter if they were given if (fromAmount != null) { criteria.add(Restrictions.ge(Invoice.Fields.invoiceTotal.name(), fromAmount)); } if (thruAmount != null) { criteria.add(Restrictions.le(Invoice.Fields.invoiceTotal.name(), thruAmount)); } if (fromOpenAmount != null) { criteria.add(Restrictions.ge(Invoice.Fields.openAmount.name(), fromOpenAmount)); } if (thruOpenAmount != null) { criteria.add(Restrictions.le(Invoice.Fields.openAmount.name(), thruOpenAmount)); } // set the other like filters if they were given if (UtilValidate.isNotEmpty(referenceNumber)) { criteria.add(Restrictions.ilike(Invoice.Fields.referenceNumber.name(), referenceNumber, MatchMode.ANYWHERE)); } if (UtilValidate.isNotEmpty(message)) { criteria.add(Restrictions.ilike(Invoice.Fields.invoiceMessage.name(), message, MatchMode.ANYWHERE)); } // order Id search needs a join with OrderItemBilling if (UtilValidate.isNotEmpty(orderId)) { criteria.createAlias("orderItemBillings", "oib"); criteria.add(Restrictions.eq("oib." + OrderItemBilling.Fields.orderId.name(), orderId)); } // item description search needs a join with InvoiceItem if (UtilValidate.isNotEmpty(itemDescription)) { criteria.createAlias("invoiceItems", "ii"); criteria.add(Restrictions.ilike("ii." + InvoiceItem.Fields.description.name(), itemDescription, MatchMode.ANYWHERE)); } // TODO: accounting tags // specify the fields to return criteria.setProjection(Projections.projectionList() .add(Projections.distinct(Projections.property(Invoice.Fields.invoiceId.name()))) .add(Projections.property(Invoice.Fields.partyId.name())) .add(Projections.property(Invoice.Fields.partyIdFrom.name())) .add(Projections.property(Invoice.Fields.statusId.name())) .add(Projections.property(Invoice.Fields.processingStatusId.name())) .add(Projections.property(Invoice.Fields.invoiceDate.name())) .add(Projections.property(Invoice.Fields.dueDate.name())) .add(Projections.property(Invoice.Fields.currencyUomId.name())) .add(Projections.property(Invoice.Fields.invoiceTotal.name())) .add(Projections.property(Invoice.Fields.openAmount.name())) .add(Projections.property(Invoice.Fields.referenceNumber.name()))); // set the order by if (orderBy == null) { orderBy = Arrays.asList(Invoice.Fields.invoiceDate.desc()); } // some substitution is needed to fit the hibernate field names // this also maps the calculated fields and indicates the non sortable fields Map<String, String> subs = new HashMap<String, String>(); subs.put("partyName", "partyId"); subs.put("partyNameFrom", "partyIdFrom"); subs.put("invoiceDateString", "invoiceDate"); subs.put("dueDateString", "dueDate"); subs.put("statusDescription", "statusId"); subs.put("processingStatusDescription", "processingStatusId"); HibernateUtil.setCriteriaOrder(criteria, orderBy, subs); ScrollableResults results = null; List<InvoiceViewForListing> results2 = new ArrayList<InvoiceViewForListing>(); try { // fetch the paginated results results = criteria.scroll(ScrollMode.SCROLL_INSENSITIVE); if (usePagination()) { results.setRowNumber(getPageStart()); } // convert them into InvoiceViewForListing objects which will also calculate or format some fields for display Object[] o = results.get(); int n = 0; // number of results actually read while (o != null) { InvoiceViewForListing r = new InvoiceViewForListing(); r.initRepository(this); int i = 0; r.setInvoiceId((String) o[i++]); r.setPartyId((String) o[i++]); r.setPartyIdFrom((String) o[i++]); r.setStatusId((String) o[i++]); r.setProcessingStatusId((String) o[i++]); r.setInvoiceDate((Timestamp) o[i++]); r.setDueDate((Timestamp) o[i++]); r.setCurrencyUomId((String) o[i++]); r.setInvoiceTotal((BigDecimal) o[i++]); r.setOpenAmount((BigDecimal) o[i++]); r.setReferenceNumber((String) o[i++]); r.calculateExtraFields(getDelegator(), timeZone, locale); results2.add(r); n++; if (!results.next()) { break; } if (usePagination() && n >= getPageSize()) { break; } o = results.get(); } results.last(); // note: row number starts at 0 setResultSize(results.getRowNumber() + 1); } finally { results.close(); } return results2; } catch (InfrastructureException e) { throw new RepositoryException(e); } finally { if (session != null) { session.close(); } } }