List of usage examples for org.hibernate ScrollableResults getRowNumber
int getRowNumber();
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) {/* w ww. j a v a 2 s. 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() *///from w w w . ja v a2 s .c om @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();/* w ww . j av a 2 s . c o m*/ 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.gbif.portal.dao.occurrence.OccurrenceAssociationTraverser.java
License:Open Source License
/** * @see org.gbif.portal.dao.AssociationTraverser#batchPreprocess(int, org.hibernate.ScrollableResults, org.hibernate.Session) *///from w w w. j av a2 s .c om public void batchPreprocess(int batchSize, ScrollableResults scrollableResults, Session session) { if (!retrieveIdentifiers) return; if (logger.isDebugEnabled()) logger.debug("Current row number:" + scrollableResults.getRowNumber()); List<Long> occurrenceRecordIds = new ArrayList<Long>(); boolean eor = false; int numberScrolled = 0; for (numberScrolled = 0; numberScrolled < batchSize - 1 && !eor; numberScrolled++) { //retrieve the id Long recordId = (Long) scrollableResults.get(0); occurrenceRecordIds.add(recordId); if (scrollableResults.isLast()) { eor = true; numberScrolled--; } else { scrollableResults.next(); } } scrollableResults.scroll(-numberScrolled); if (logger.isDebugEnabled()) { logger.debug("Number scrolled through: " + numberScrolled); logger.debug("Scrolled back to: " + scrollableResults.getRowNumber()); } //retrieve image records for this batch - and process into Map - List<ORImage> orImageList = imageRecordDAO.getImageRecordsForOccurrenceRecords(occurrenceRecordIds); this.orImageUrlMap = new HashMap<Long, String>(); for (ORImage orImage : orImageList) { //only storing the first image url we find if (this.orImageUrlMap.get(orImage.getOccurrenceRecordId()) == null) { this.orImageUrlMap.put(orImage.getOccurrenceRecordId(), orImage.getUrl()); } session.evict(orImage); } if (logger.isDebugEnabled()) logger.debug("Number of images found for batch: " + this.orImageUrlMap.size()); //retrieve type status for this batch List<TypeStatus> typeStatusList = typificationRecordDAO .getTypeStatusForOccurrenceRecords(occurrenceRecordIds); this.typeStatusMap = new HashMap<Long, String>(); for (TypeStatus typeStatus : typeStatusList) { //only storing the first type status we find if (this.typeStatusMap.get(typeStatus.getOccurrenceRecordId()) == null) { this.typeStatusMap.put(typeStatus.getOccurrenceRecordId(), typeStatus.getTypeStatus()); } session.evict(typeStatus); } if (logger.isDebugEnabled()) logger.debug("Number of type status found for batch: " + this.typeStatusMap.size()); //retrieve identifiers for this batch List<IdentifierRecord> identifierList = identifierRecordDAO .getIdentifierRecordsForOccurrenceRecords(occurrenceRecordIds); this.identifiersMap = new HashMap<Long, List<IdentifierRecord>>(); for (IdentifierRecord ir : identifierList) { List<IdentifierRecord> irs = this.identifiersMap.get(ir.getOccurrenceRecordId()); if (irs == null) { irs = new ArrayList<IdentifierRecord>(); irs.add(ir); this.identifiersMap.put(ir.getOccurrenceRecordId(), irs); } else { irs.add(ir); } session.evict(ir); } if (logger.isDebugEnabled()) logger.debug("Number of identifiers found for batch: " + this.identifiersMap.size()); }
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();/*w w w .jav a2 s . co m*/ count = scroll.getRowNumber(); 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.dal.service.OBQuery.java
License:Open Source License
/** * Computes the row number of a record which has the id which is passed in as a parameter. The * rownumber computation takes into account the filter and sorting settings of the the OBQuery * object.//from ww w . j a v a 2 s . c om * * @param targetId * the record id * @return the row number or -1 if not found */ public int getRowNumber(String targetId) { String qryStr = createQueryString(); if (qryStr.toLowerCase().contains(FROM_SPACED)) { final int index = qryStr.indexOf(FROM_SPACED) + FROM_SPACED.length(); qryStr = qryStr.substring(index); } final Query qry = getSession().createQuery("select " + usedAlias + "id " + FROM_SPACED + qryStr); setParameters(qry); final ScrollableResults results = qry.scroll(ScrollMode.FORWARD_ONLY); try { while (results.next()) { final String id = results.getString(0); if (id.equals(targetId)) { return results.getRowNumber(); } } } finally { results.close(); } return -1; }
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(); }/* ww w . j a v a 2 s. c o 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); }/* w ww .j a 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); }//from w ww . ja v a2s. c o m 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); }// www . j ava 2s . co 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(); } } }