List of usage examples for org.hibernate.criterion Projections sqlGroupProjection
@SuppressWarnings("UnusedDeclaration") public static Projection sqlGroupProjection(String sql, String groupBy, String[] columnAliases, Type[] types)
From source file:br.com.gerenciapessoal.repository.Lancamentos.java
@SuppressWarnings("UnusedAssignment") public Map<Date, BigDecimal> valoresTotaisPorData(Integer numeroDeDias, Conta conta) { Session session = manager.unwrap(Session.class); Calendar dataInicial = Calendar.getInstance(); dataInicial = DateUtils.truncate(dataInicial, Calendar.DAY_OF_MONTH); dataInicial.add(Calendar.DAY_OF_MONTH, numeroDeDias * -1); Map<Date, BigDecimal> resultado = criarMapaVazio(numeroDeDias, dataInicial); Criteria criteria = session.createCriteria(Lancamento.class).createAlias("conta", "c"); criteria.setProjection(Projections.projectionList() .add(Projections.sqlGroupProjection("date(data_emissao) as data", "date(data_emissao)", new String[] { "data" }, new Type[] { StandardBasicTypes.DATE })) .add(Projections.sum("valorLanca").as("valor"))) .add(Restrictions.ge("dataEmissao", dataInicial.getTime())); if (conta != null) { criteria.add(Restrictions.eq("c.id", conta.getId())); }/*from www . j a va 2s.c o m*/ List<DataValor> valoresPorData = criteria.setResultTransformer(Transformers.aliasToBean(DataValor.class)) .list(); for (DataValor dataValor : valoresPorData) { resultado.put(dataValor.getData(), dataValor.getValor()); } return resultado; }
From source file:br.ufac.sion.dao.InscricaoFacade.java
@Override public Map<Date, Long> inscricoesPorData(Concurso concurso, SituacaoInscricao situacao) { Session session = em.unwrap(Session.class); Criteria criteria = session.createCriteria(Inscricao.class); LocalDateTime dataInicial = concurso.getDataInicioInscricao(); Integer dias = Period.between(concurso.getDataInicioInscricao().toLocalDate(), concurso.getDataTerminoIncricao().toLocalDate()).getDays(); Map<Date, Long> resultado = criaMapaVazio(dias, dataInicial); criteria.createAlias("cargoConcurso", "cc") .setProjection(Projections.projectionList() .add(Projections.sqlGroupProjection("date(data_inscricao) as data", "date(data_inscricao)", new String[] { "data" }, new Type[] { StandardBasicTypes.DATE })) .add(Projections.count("id").as("quantidade"))) .add(Restrictions.ge("dataInscricao", dataInicial)).add(Restrictions.eq("cc.concurso", concurso)); if (situacao != null) { criteria.add(Restrictions.eq("status", situacao.CONFIRMADA)); }/*ww w. ja v a 2 s. com*/ List<DataQuantidade> quantidadesPorData = criteria .setResultTransformer(Transformers.aliasToBean(DataQuantidade.class)).list(); for (DataQuantidade quantidadeData : quantidadesPorData) { resultado.put(quantidadeData.getData(), quantidadeData.getQuantidade()); } return resultado; }
From source file:com.amalto.core.storage.hibernate.StandardQueryHandler.java
License:Open Source License
@Override public StorageResults visit(GroupSize groupSize) { Projection groupSizeProjection = Projections.sqlGroupProjection( "count(this_." + Storage.METADATA_TASK_ID + ") as talend_group_size", //$NON-NLS-1$ //$NON-NLS-2$ "this_." + Storage.METADATA_TASK_ID, new String[] { "talend_group_size" }, //$NON-NLS-1$ //$NON-NLS-2$ new org.hibernate.type.Type[] { new IntegerType() }); projectionList.add(groupSizeProjection); return null;/*ww w . j av a 2 s . com*/ }
From source file:com.court.controller.HomeFXMLController.java
private Map<String, Number> getLoanReleasedData() { LocalDate now = LocalDate.now(); Map<String, Number> map = new HashMap<>(); Session s = HibernateUtil.getSessionFactory().openSession(); Criteria c = s.createCriteria(MemberLoan.class); ProjectionList pList = Projections.projectionList(); ClassMetadata lpMeta = s.getSessionFactory().getClassMetadata(MemberLoan.class); pList.add(Projections.property(lpMeta.getIdentifierPropertyName())); for (String prop : lpMeta.getPropertyNames()) { pList.add(Projections.property(prop), prop); }//ww w.j a v a2s. c om c.add(Restrictions.eq("status", true)); c.add(Restrictions.between("grantedDate", FxUtilsHandler.getDateFrom(now.with(firstDayOfYear())), FxUtilsHandler.getDateFrom(now.with(lastDayOfYear())))); c.setProjection(pList .add(Projections.sqlGroupProjection("DATE_FORMAT(granted_date, '%Y-%m-01') AS groupPro", "groupPro", new String[] { "groupPro" }, new Type[] { StringType.INSTANCE })) .add(Projections.sqlProjection("SUM(loan_amount) AS lSum", new String[] { "lSum" }, new Type[] { DoubleType.INSTANCE }))); c.addOrder(Order.asc("grantedDate")); c.setResultTransformer(Transformers.aliasToBean(MemberLoan.class)); List<MemberLoan> list = (List<MemberLoan>) c.list(); for (MemberLoan ml : list) { map.put(ml.getGroupPro(), ml.getlSum()); } s.close(); return map; }
From source file:com.court.controller.HomeFXMLController.java
private Map<String, Number> getLoanCollectionData() { LocalDate now = LocalDate.now(); Map<String, Number> map = new HashMap<>(); Session s = HibernateUtil.getSessionFactory().openSession(); Criteria c = s.createCriteria(LoanPayment.class); ProjectionList pList = Projections.projectionList(); ClassMetadata lpMeta = s.getSessionFactory().getClassMetadata(LoanPayment.class); pList.add(Projections.property(lpMeta.getIdentifierPropertyName())); for (String prop : lpMeta.getPropertyNames()) { pList.add(Projections.property(prop), prop); }/*w w w . j ava 2 s .c om*/ c.add(Restrictions.between("paymentDate", FxUtilsHandler.getDateFrom(now.with(firstDayOfYear())), FxUtilsHandler.getDateFrom(now.with(lastDayOfYear())))); c.setProjection(pList .add(Projections.sqlGroupProjection("DATE_FORMAT(payment_date, '%Y-%m-01') AS groupPro", "groupPro", new String[] { "groupPro" }, new Type[] { StringType.INSTANCE })) .add(Projections.sqlProjection("SUM(paid_amt) AS lSum", new String[] { "lSum" }, new Type[] { DoubleType.INSTANCE }))); c.addOrder(Order.asc("paymentDate")); c.setResultTransformer(Transformers.aliasToBean(LoanPayment.class)); List<LoanPayment> list = (List<LoanPayment>) c.list(); for (LoanPayment lp : list) { map.put(lp.getGroupPro(), lp.getlSum()); } s.close(); return map; }
From source file:com.evolveum.midpoint.repo.sql.query.restriction.OrgRestriction.java
License:Apache License
@Override public Criterion interpret(OrgFilter filter) throws QueryException { if (filter.isRoot()) { // Criteria pCriteria = getInterpreter().getCriteria(null); DetachedCriteria dc = DetachedCriteria.forClass(ROrgClosure.class); String[] strings = new String[1]; strings[0] = "descendant.oid"; Type[] type = new Type[1]; type[0] = StringType.INSTANCE;/*from w w w . j a v a 2 s . co m*/ dc.setProjection(Projections.sqlGroupProjection("descendant_oid", "descendant_oid having count(descendant_oid)=1", strings, type)); // pCriteria.add(Subqueries.in("this.oid", dc)); return Subqueries.propertyIn("oid", dc); // Query rootOrgQuery = session.createQuery("select org from ROrg as org where org.oid in (select descendant.oid from ROrgClosure group by descendant.oid having count(descendant.oid)=1)"); } if (filter.getOrgRef() == null) { throw new QueryException("No organization reference defined in the search query."); } if (filter.getOrgRef().getOid() == null) { throw new QueryException( "No oid specified in organization reference " + filter.getOrgRef().debugDump()); } DetachedCriteria detached; switch (filter.getScope()) { case ONE_LEVEL: detached = DetachedCriteria.forClass(RParentOrgRef.class, "p"); detached.setProjection(Projections.distinct(Projections.property("p.ownerOid"))); detached.add(Restrictions.eq("p.targetOid", filter.getOrgRef().getOid())); break; case SUBTREE: default: detached = DetachedCriteria.forClass(ROrgClosure.class, "cl"); detached.setProjection(Projections.distinct(Projections.property("cl.descendantOid"))); detached.add(Restrictions.eq("cl.ancestorOid", filter.getOrgRef().getOid())); detached.add(Restrictions.ne("cl.descendantOid", filter.getOrgRef().getOid())); } String mainAlias = getContext().getAlias(null); return Subqueries.propertyIn(mainAlias + ".oid", detached); }
From source file:com.ut.tekir.invoice.yeni.LimitationChecker.java
License:LGPL
public DetachedCriteria buildCriteriaForWarehouse() { DetachedCriteria crit = DetachedCriteria.forClass(ProductTxn.class); crit.createAlias("product", "product"); crit.createAlias("warehouse", "warehouse"); ProjectionList pl = Projections.projectionList(); pl.add(Projections.groupProperty("product.code"), "prodcode") .add(Projections.groupProperty("product.name"), "prodname") .add(Projections.groupProperty("product.group"), "group") .add(Projections.groupProperty("product.barcode1"), "barcode") .add(Projections.groupProperty("warehouse.code"), "warecode") .add(Projections.groupProperty("warehouse.name"), "warename") .add(Projections.sum("quantity.value"), "quantity") .add(Projections.avg("unitPrice.value"), "unitPrice") .add(Projections.sqlGroupProjection("{alias}.UNIT as unit, " + "sum( case {alias}.trade_action when 0 then {alias}.QUANTITY else 0 end ) as INQTY, " + "sum( case {alias}.trade_action when 1 then {alias}.QUANTITY else 0 end ) as OUTQTY , " + "sum( case {alias}.trade_action when 2 then {alias}.QUANTITY else 0 end ) as BUYRETQTY, " + "sum( case {alias}.trade_action when 3 then {alias}.QUANTITY else 0 end ) as SELLRETQTY, " + "sum( case {alias}.trade_action when 6 then {alias}.QUANTITY else 0 end ) as RESQTY , " + "sum( case {alias}.trade_action when 7 then {alias}.QUANTITY else 0 end ) as DELQTY ", "UNIT", new String[] { "unit", "inqty", "outqty", "buyretqty", "sellretqty", "resqty", "delqty" }, new Type[] { Hibernate.STRING, Hibernate.DOUBLE, Hibernate.DOUBLE, Hibernate.DOUBLE, Hibernate.DOUBLE, Hibernate.DOUBLE, Hibernate.DOUBLE })); crit.setProjection(pl);/* w ww.j a v a2s . com*/ crit.add(Restrictions.eq("active", true)); //Evran kendisini toplam deerlere eklemiyoruz. if (filterModel.getDocId() != null) { crit.add(Restrictions.ne("documentId", filterModel.getDocId())); } if (filterModel.getBarcode() != null && filterModel.getBarcode().length() > 0) { Criterion criteria1 = Restrictions.eq("product.barcode1", filterModel.getBarcode()); Criterion criteria2 = Restrictions.eq("product.barcode2", filterModel.getBarcode()); Criterion criteria3 = Restrictions.eq("product.barcode3", filterModel.getBarcode()); crit.add(Restrictions.or(criteria1, Restrictions.or(criteria2, criteria3))); } if (filterModel.getProduct() != null) { crit.add(Restrictions.eq("product", filterModel.getProduct())); } crit.addOrder(Order.asc("product.name")); return crit; }
From source file:com.ut.tekir.report.AccountStatusReportBean.java
License:LGPL
public DetachedCriteria buildCriteria() { DetachedCriteria crit = DetachedCriteria.forClass(AccountTxn.class); crit.createAlias("account", "account"); crit.setProjection(Projections.projectionList() .add(Projections.groupProperty("account.code"), "accountCode") .add(Projections.groupProperty("account.name"), "accountName") .add(Projections.groupProperty("amount.currency"), "currency") .add(Projections.sum("amount.value"), "amount") .add(Projections.sum("amount.localAmount"), "localAmount") .add(Projections.property("this.processType"), "processType") .add(Projections.sqlGroupProjection( "{alias}.CCY as currency, sum( ( case {alias}.finance_action when 0 then 1 else -1 end ) * {alias}.CCYVAL ) as AMOUNT, sum( case {alias}.finance_action when 0 then {alias}.CCYVAL else 0 end ) as DEBIT, sum( case {alias}.finance_action when 0 then 0 else {alias}.CCYVAL end ) as CREDIT", "CCY", new String[] { "currency", "amount", "debit", "credit" }, new Type[] { Hibernate.STRING, Hibernate.BIG_DECIMAL, Hibernate.BIG_DECIMAL, Hibernate.BIG_DECIMAL }))); crit.add(Restrictions.eq("active", true)); if (code != null && code.length() > 0) { crit.add(Restrictions.ilike("code", code, MatchMode.START)); }//from w ww. jav a2 s .c om if (account != null) { crit.add(Restrictions.eq("account", account)); } if (beginDate != null) { crit.add(Restrictions.ge("date", beginDate)); } if (endDate != null) { crit.add(Restrictions.le("date", endDate)); } if (getDocumentType() != null && getDocumentType() != DocumentType.Unknown) { crit.add(Restrictions.eq("documentType", getDocumentType())); } if (getProcessType() != null) { crit.add(Restrictions.eq("this.processType", getProcessType())); } if (getWorkBunch() != null) { crit.add(Restrictions.eq("workBunch", getWorkBunch())); } crit.addOrder(Order.asc("account.code")); return crit; }
From source file:com.ut.tekir.report.BankStatusReportBean.java
License:LGPL
public DetachedCriteria buildCriteria() { DetachedCriteria crit = DetachedCriteria.forClass(BankTxn.class); crit.createAlias("bankAccount", "bankAccount"); crit.createAlias("bankAccount.bankBranch", "bankBranch"); crit.createAlias("bankAccount.bankBranch.bank", "bank"); crit.setProjection(Projections.projectionList() .add(Projections.groupProperty("bankAccount.accountNo"), "bankAccount") .add(Projections.groupProperty("bankBranch.name"), "bankBranchName") .add(Projections.groupProperty("bank.name"), "bankName") .add(Projections.groupProperty("amount.currency"), "currency") .add(Projections.sum("amount.value"), "amount") .add(Projections.property("processType"), "processType") .add(Projections.sum("amount.localAmount"), "localAmount") .add(Projections.sqlGroupProjection( "{alias}.CCY as currency, sum( ( case {alias}.action when 0 then 1 else -1 end ) * {alias}.CCYVAL ) as AMOUNT, sum( case {alias}.action when 0 then {alias}.CCYVAL else 0 end ) as DEBIT, sum( case {alias}.action when 0 then 0 else {alias}.CCYVAL end ) as CREDIT", "{alias}.CCY", new String[] { "currency", "amount", "debit", "credit" }, new Type[] { Hibernate.STRING, Hibernate.BIG_DECIMAL, Hibernate.BIG_DECIMAL, Hibernate.BIG_DECIMAL }))); crit.add(Restrictions.eq("active", true)); if (bankAccount != null) { crit.add(Restrictions.eq("this.bankAccount", bankAccount)); } else {//from w w w . java 2s .co m if (bankBranch != null) { crit.add(Restrictions.eq("bankAccount.bankBranch", bankBranch)); } else { if (bank != null) { crit.add(Restrictions.eq("bankBranch.bank", bank)); } } } if (code != null && code.length() > 0) { crit.add(Restrictions.ilike("code", code, MatchMode.START)); } if (beginDate != null) { crit.add(Restrictions.ge("date", beginDate)); } if (endDate != null) { crit.add(Restrictions.le("date", endDate)); } if (getWorkBunch() != null) { crit.add(Restrictions.eq("workBunch", getWorkBunch())); } if (getDocumentType() != null && getDocumentType() != DocumentType.Unknown) { crit.add(Restrictions.eq("documentType", getDocumentType())); } if (getProcessType() != null) { crit.add(Restrictions.eq("this.processType", getProcessType())); } crit.addOrder(Order.asc("bankAccount.name")); return crit; }
From source file:com.ut.tekir.report.ContactStatusReportBean.java
License:LGPL
public DetachedCriteria buildCriteria() { DetachedCriteria crit = DetachedCriteria.forClass(FinanceTxn.class); crit.createAlias("contact", "contact"); crit.setProjection(Projections.projectionList().add(Projections.groupProperty("contact.code"), "concode") .add(Projections.groupProperty("contact.fullname"), "conname") .add(Projections.groupProperty("contact.company"), "company") .add(Projections.groupProperty("contact.person"), "person") //.add( Projections.groupProperty("action"), "action" ) //.add( Projections.groupProperty("amount.currency"), "currency" ) .add(Projections.sum("amount.value"), "amount") .add(Projections.sum("amount.localAmount"), "localAmount") .add(Projections.sqlGroupProjection( "{alias}.CCY as currency, sum( case {alias}.finance_action when 0 then {alias}.CCYVAL else 0 end ) as DEBIT, sum( case {alias}.finance_action when 0 then 0 else {alias}.CCYVAL end ) as CREDIT", "{alias}.CCY", new String[] { "currency", "debit", "credit" }, new Type[] { Hibernate.STRING, Hibernate.BIG_DECIMAL, Hibernate.BIG_DECIMAL }))); if (fm.getActive() != null) { crit.add(Restrictions.eq("active", fm.getActive())); }//ww w.j a v a2 s .com if (StringUtils.isNotEmpty(fm.getCode())) { crit.add(Restrictions.ilike("contact.code", fm.getCode(), MatchMode.START)); } if (StringUtils.isNotEmpty(fm.getName())) { crit.add(Restrictions.ilike("contact.name", fm.getName(), MatchMode.START)); } if (fm.getCategory() != null) { crit.add(Restrictions.eq("contact.category", fm.getCategory())); } if (fm.getBeginDate() != null) { crit.add(Restrictions.ge("date", fm.getBeginDate())); } if (fm.getEndDate() != null) { crit.add(Restrictions.le("date", fm.getEndDate())); } if (fm.getCompanyType() != null && !fm.getCompanyType().equals("All")) { if (fm.getCompanyType().equals("Person")) { crit.add(Restrictions.eq("contact.person", Boolean.TRUE)); } else crit.add(Restrictions.eq("contact.person", Boolean.FALSE)); } if (fm.getType() != null && fm.getType() != ContactType.All) { crit.add(Restrictions.eq("contact." + fm.getType().toString().toLowerCase() + "Type", Boolean.TRUE)); } if (StringUtils.isNotEmpty(fm.getExCode1())) { crit.add(Restrictions.ilike("contact.exCode1", fm.getExCode1(), MatchMode.START)); } if (StringUtils.isNotEmpty(fm.getExCode2())) { crit.add(Restrictions.ilike("contact.exCode2", fm.getExCode2(), MatchMode.START)); } if (fm.getOrganization() != null) { crit.add(Restrictions.eq("contact.organization", fm.getOrganization())); } if (StringUtils.isNotEmpty(fm.getDocCode())) { crit.add(Restrictions.ilike("code", fm.getDocCode(), MatchMode.START)); } if (fm.getDocumentType() != null && fm.getDocumentType() != DocumentType.Unknown) { crit.add(Restrictions.eq("this.documentType", fm.getDocumentType())); } if (fm.getProcessType() != null) { crit.add(Restrictions.eq("this.processType", fm.getProcessType())); } if (fm.getWorkBunch() != null) { crit.add(Restrictions.eq("this.workBunch", fm.getWorkBunch())); } if (fm.getContact() != null) { crit.add(Restrictions.eq("contact.id", fm.getContact().getId())); } /* crit.addOrder( Order.asc("date")); crit.addOrder( Order.asc("serial")); * */ crit.addOrder(Order.asc("contact.code")); crit.addOrder(Order.asc("amount.currency")); log.debug("Sonu : #0", crit); return crit; }