List of usage examples for org.hibernate.criterion Projections sum
public static AggregateProjection sum(String propertyName)
From source file:com.smartitengineering.dao.impl.hibernate.AbstractDAO.java
License:Open Source License
@SuppressWarnings("unchecked") private void processCriteria(Criteria criteria, String element, QueryParameter parameter) { switch (parameter.getParameterType()) { case PARAMETER_TYPE_PROPERTY: { criteria.add(getCriterion(element, parameter)); return;/*from ww w .j a v a 2 s . c o m*/ } case PARAMETER_TYPE_ORDER_BY: { final Order order; SimpleNameValueQueryParameter<com.smartitengineering.dao.common.queryparam.Order> queryParameter = QueryParameterCastHelper.SIMPLE_PARAM_HELPER .cast(parameter); com.smartitengineering.dao.common.queryparam.Order requestedOrder = queryParameter.getValue(); switch (requestedOrder) { case ASC: { order = Order.asc(element); break; } case DESC: { order = Order.desc(element); break; } default: { order = null; break; } } if (order != null) { criteria.addOrder(order); } return; } case PARAMETER_TYPE_MAX_RESULT: { ValueOnlyQueryParameter<Integer> queryParameter = QueryParameterCastHelper.VALUE_PARAM_HELPER .cast(parameter); criteria.setMaxResults(queryParameter.getValue()); return; } case PARAMETER_TYPE_FIRST_RESULT: { ValueOnlyQueryParameter<Integer> queryParameter = QueryParameterCastHelper.VALUE_PARAM_HELPER .cast(parameter); criteria.setFirstResult(queryParameter.getValue()); return; } case PARAMETER_TYPE_DISJUNCTION: { processDisjunction(criteria, parameter); return; } case PARAMETER_TYPE_CONJUNCTION: { processConjunction(criteria, parameter); return; } case PARAMETER_TYPE_NESTED_PROPERTY: { processNestedParameter(criteria, element, parameter); return; } case PARAMETER_TYPE_COUNT: { final Projection countProjection = Projections.count(element); setProjection(criteria, countProjection); return; } case PARAMETER_TYPE_ROW_COUNT: { final Projection rowCount = Projections.rowCount(); setProjection(criteria, rowCount); return; } case PARAMETER_TYPE_SUM: { final AggregateProjection sum = Projections.sum(element); setProjection(criteria, sum); return; } case PARAMETER_TYPE_MAX: { final AggregateProjection max = Projections.max(element); setProjection(criteria, max); return; } case PARAMETER_TYPE_MIN: { final AggregateProjection min = Projections.min(element); setProjection(criteria, min); return; } case PARAMETER_TYPE_AVG: { final AggregateProjection avg = Projections.avg(element); setProjection(criteria, avg); return; } case PARAMETER_TYPE_GROUP_BY: { final PropertyProjection groupProperty = Projections.groupProperty(element); setProjection(criteria, groupProperty); return; } case PARAMETER_TYPE_COUNT_DISTINCT: { final CountProjection countDistinct = Projections.countDistinct(element); setProjection(criteria, countDistinct); return; } case PARAMETER_TYPE_DISTINCT_PROP: { final Projection distinct = Projections.distinct(Projections.property(element)); setProjection(criteria, distinct); return; } case PARAMETER_TYPE_UNIT_PROP: { final PropertyProjection property = Projections.property(element); setProjection(criteria, property); return; } } }
From source file:com.tysanclan.site.projectewok.entities.dao.hibernate.MembershipStatusChangeDAOImpl.java
License:Open Source License
@Override public SortedMap<Date, Long> getMutationsByDate(Date start, Date end) { Criteria criteria = getSession().createCriteria(MembershipStatusChange.class); if (start != null) criteria.add(Restrictions.ge("changeTime", start)); if (end != null) criteria.add(Restrictions.le("changeTime", end)); criteria.setProjection(Projections.projectionList().add(Projections.property("changeTime")) .add(Projections.sum("memberSizeMutation"))); List<Object[]> results = listOf(criteria); SortedMap<Date, Long> map = new TreeMap<Date, Long>(new Comparator<Date>() { @Override/* w w w . j a va2s . co m*/ public int compare(Date d1, Date d2) { return -d1.compareTo(d2); } }); for (Object[] res : results) { Date date = (Date) res[0]; Long sum = (Long) res[1]; map.put(date, sum); } return map; }
From source file:com.uniminuto.ejercicio2.dao.EmployeeDaoImpl.java
public int sumaSalarios() { SessionFactory sf = HibernateUtil.getSessionFactory(); Session session = sf.openSession();//w w w. ja v a2 s . c o m Criteria cr = session.createCriteria(Employee.class); cr.setProjection(Projections.sum("salario")); List totalSalary = cr.list(); int suma = totalSalary.get(0).hashCode(); return suma; }
From source file:com.ut.tekir.contact.ContactStatusBean.java
License:LGPL
/** * nce devir hesaplayalm.../*from ww w . j a va 2 s .c o m*/ * */ @SuppressWarnings("unchecked") protected void calculateOffset() { HibernateSessionProxy session = (HibernateSessionProxy) entityManager.getDelegate(); Calendar cal = Calendar.getInstance(); cal.set(getYear(), 0, 1); Date beginDate = cal.getTime(); Criteria crit = session.createCriteria(FinanceTxn.class); crit.add(Restrictions.eq("contact", contact)).add(Restrictions.lt("date", beginDate)) .add(Restrictions.eq("active", true)); //Tarih kontrol yaplacak crit.setProjection(Projections.projectionList() .add(Projections.groupProperty("amount.currency"), "amountCurrency") .add(Projections.groupProperty("action"), "action").add(Projections.sum("amount.value"), "amount") .add(Projections.sum("localAmount.value"), "localAmount")); List ls = crit.list(); for (Iterator it = ls.iterator(); it.hasNext();) { Object obj[] = (Object[]) it.next(); dataTable.addRow("OPEN", (String) obj[0], (FinanceAction) obj[1], (Double) obj[2], (Double) obj[3]); } }
From source file:com.ut.tekir.contact.ContactStatusBean.java
License:LGPL
@SuppressWarnings("unchecked") private void calculateMonth(Integer month) { String rowKey = "MONTH" + (month < 10 ? "0" + month : month); Calendar cal = Calendar.getInstance(); cal.set(getYear(), month, 1);//w w w . j a v a 2 s. c o m Date beginDate = cal.getTime(); if (month == 12) { cal.set(getYear() + 1, 1, 1); } else { cal.set(getYear(), month + 1, 1); } Date endDate = cal.getTime(); HibernateSessionProxy session = (HibernateSessionProxy) entityManager.getDelegate(); Criteria crit = session.createCriteria(FinanceTxn.class); crit.add(Restrictions.eq("contact", contact)).add(Restrictions.ge("date", beginDate)) .add(Restrictions.lt("date", endDate)).add(Restrictions.eq("active", true)); //Tarih kontrol yaplacak crit.setProjection(Projections.projectionList() .add(Projections.groupProperty("amount.currency"), "amountCurrency") .add(Projections.groupProperty("action"), "action").add(Projections.sum("amount.value"), "amount") .add(Projections.sum("localAmount.value"), "localAmount")); List ls = crit.list(); for (Iterator it = ls.iterator(); it.hasNext();) { Object obj[] = (Object[]) it.next(); dataTable.addRow(rowKey, (String) obj[0], (FinanceAction) obj[1], (Double) obj[2], (Double) obj[3]); } }
From source file:com.ut.tekir.finance.FinanceTxnReportBean.java
License:LGPL
@Override public DetachedCriteria buildCriteria() { DetachedCriteria crit = DetachedCriteria.forClass(FinanceTxn.class); crit.createAlias("contact", "contact"); if (isNotEmpty(filterModel.getSerial())) { crit.add(Restrictions.ilike("this.serial", filterModel.getSerial(), MatchMode.START)); }/* ww w . ja v a 2 s . c o m*/ if (isNotEmpty(filterModel.getReference())) { crit.add(Restrictions.ilike("this.reference", filterModel.getReference(), MatchMode.START)); } if (isNotEmpty(filterModel.getCode())) { crit.add(Restrictions.ilike("this.code", filterModel.getCode(), MatchMode.START)); } if (filterModel.getBeginDate() != null) { crit.add(Restrictions.ge("this.date", filterModel.getBeginDate())); } if (filterModel.getEndDate() != null) { crit.add(Restrictions.le("this.date", filterModel.getEndDate())); } if (isNotEmpty(filterModel.getContactName())) { crit.add(Restrictions.ilike("contact.fullname", filterModel.getContactName(), MatchMode.START)); } if (isNotEmpty(filterModel.getContactCode())) { crit.add(Restrictions.ilike("contact.code", filterModel.getContactCode(), MatchMode.START)); } if (filterModel.getCompanyType() != null && !filterModel.getCompanyType().equals("All")) { if (filterModel.getCompanyType().equals("Person")) { crit.add(Restrictions.eq("contact.person", Boolean.TRUE)); } else crit.add(Restrictions.eq("contact.person", Boolean.FALSE)); } if (filterModel.getType() != null && filterModel.getType() != ContactType.All) { crit.add(Restrictions.eq("contact." + filterModel.getType().toString().toLowerCase() + "Type", Boolean.TRUE)); } if (filterModel.getCategory() != null) { crit.add(Restrictions.eq("contact.category", filterModel.getCategory())); } if (filterModel.getExCode1() != null && filterModel.getExCode1().length() > 0) { crit.add(Restrictions.ilike("contact.exCode1", filterModel.getExCode1(), MatchMode.START)); } if (filterModel.getExCode2() != null && filterModel.getExCode2().length() > 0) { crit.add(Restrictions.ilike("contact.exCode2", filterModel.getExCode2(), MatchMode.START)); } if (filterModel.getOrganization() != null) { crit.add(Restrictions.eq("contact.organization", filterModel.getOrganization())); } if (filterModel.getActive() != null) { crit.add(Restrictions.eq("this.active", filterModel.getActive())); } if (filterModel.getDocumentType() != null && filterModel.getDocumentType() != DocumentType.Unknown) { crit.add(Restrictions.eq("this.documentType", filterModel.getDocumentType())); } if (filterModel.getContact() != null) { crit.add(Restrictions.eq("contact.id", filterModel.getContact().getId())); } if (filterModel.getWorkBunch() != null) { crit.add(Restrictions.eq("this.workBunch", filterModel.getWorkBunch())); } if (filterModel.getCurrency() != null) { crit.add(Restrictions.eq("this.amount.currency", filterModel.getCurrency())); } if (filterModel.getLocalCurrencyOnly()) { crit.setProjection(Projections.projectionList() .add(Projections.groupProperty("documentType"), "documentType") .add(Projections.groupProperty("documentId"), "documentId") .add(Projections.groupProperty("serial"), "serial") .add(Projections.groupProperty("reference"), "reference") .add(Projections.groupProperty("date"), "date") .add(Projections.groupProperty("contact.fullname"), "contactName") .add(Projections.groupProperty("contact.code"), "contactCode") .add(Projections.groupProperty("code"), "code").add(Projections.groupProperty("info"), "info") .add(Projections.groupProperty("action"), "action") .add(Projections.sum("amount.localAmount"), "localAmount") .add(Projections.sum("processType"), "processType") .add(Projections.sum("amount.currency"), "currency") .add(Projections.groupProperty("workBunch"), "workBunch")); log.info("yerel secili : #0", crit); } if (filterModel.getProcessType() != null) { crit.add(Restrictions.eq("this.processType", filterModel.getProcessType())); } crit.addOrder(Order.desc("this.date")); crit.addOrder(Order.desc("this.id")); //crit.addOrder(Order.desc("serial")); //crit.addOrder(Order.asc("contact.name")); log.debug("Sonu : #0", crit); return crit; }
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);//from w w w .j a va 2s . c om 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 w w. j av a2s. c o m*/ 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 www . ja v a2 s . c o 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 a 2 s. c om*/ 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; }