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:com.ut.tekir.report.ProductStatusReportBean.java
License:LGPL
public DetachedCriteria buildCriteria() { DetachedCriteria crit = DetachedCriteria.forClass(ProductTxn.class); crit.createAlias("product", "product"); crit.createAlias("warehouse", "warehouse"); //TODO: Unit Price'n farkl dvizlerde olma sorunu zle... ProjectionList pl = Projections.projectionList(); pl.add(Projections.groupProperty("product.code"), "prodcode") .add(Projections.groupProperty("product.name"), "prodname") //.add( Projections.groupProperty("action"), "action" ) //.add( Projections.groupProperty("amount.currency"), "currency" ) .add(Projections.groupProperty("product.group"), "group") .add(Projections.groupProperty("product.barcode1"), "barcode") .add(Projections.sum("quantity.value"), "quantity") .add(Projections.avg("unitPrice.value"), "unitPrice") .add(Projections.sqlGroupProjection("{alias}.UNIT as unit, " + //"sum( case {alias}.finance_action when 0 then {alias}.QUANTITY else 0 end ) as INQTY, " + //"sum( case {alias}.finance_action when 0 then 0 else {alias}.QUANTITY end ) as OUTQTY", "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 })); if (warehouseBase) { //Depo krlml pl.add(Projections.groupProperty("warehouse.code"), "warecode") .add(Projections.groupProperty("warehouse.name"), "warename"); crit.addOrder(Order.asc("warehouse.code")); }// w w w . j av a2s . com crit.setProjection(pl); crit.add(Restrictions.eq("active", true)); if (code != null && code.length() > 0) { crit.add(Restrictions.ilike("product.code", code, MatchMode.START)); } if (name != null && name.length() > 0) { crit.add(Restrictions.ilike("product.name", name, MatchMode.START)); } if (productType != null) { if (getProductType() == 1) { crit.add(Restrictions.like("this.productType", ProductType.Product)); } else if (getProductType() == 2) { crit.add(Restrictions.like("this.productType", ProductType.Service)); } } if (barcode != null && barcode.length() > 0) { Criterion criteria1 = Restrictions.like("product.barcode1", barcode, MatchMode.START); Criterion criteria2 = Restrictions.like("product.barcode2", barcode, MatchMode.START); Criterion criteria3 = Restrictions.like("product.barcode3", barcode, MatchMode.START); crit.add(Restrictions.or(criteria1, Restrictions.or(criteria2, criteria3))); } if (group != null) { crit.add(Restrictions.eq("product.group", group)); } if (warehouse != null) { crit.add(Restrictions.eq("warehouse", warehouse)); } if (beginDate != null) { crit.add(Restrictions.ge("date", beginDate)); } if (endDate != null) { crit.add(Restrictions.le("date", endDate)); } if (category != null) { crit.add(Restrictions.eq("product.category", category)); } if (docCode != null && docCode.length() > 0) { crit.add(Restrictions.ilike("code", docCode, MatchMode.START)); } if (getDocumentType() != null && getDocumentType() != DocumentType.Unknown) { crit.add(Restrictions.eq("this.documentType", getDocumentType())); } if (getWorkBunch() != null) { crit.add(Restrictions.eq("this.workBunch", getWorkBunch())); } /* crit.addOrder( Order.asc("date")); crit.addOrder( Order.asc("serial")); * */ crit.addOrder(Order.asc("product.name")); log.debug("Sonu : #0", crit); return crit; }
From source file:com.ut.tekir.stock.ProductTxnReportBean.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 . ja v a 2 s. c o m crit.add(Restrictions.eq("active", true)); 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:ke.co.mspace.nonsmppmanager.service.SMSOutServiceImpl.java
@Override public Map<String, Object> smsOutGroupBy(String user, String startDate, String endDate) { Session session = HibernateUtil.getSessionFactory().getCurrentSession(); session.getTransaction().begin();//from w w w . jav a 2 s . c om Criteria criteria = session.createCriteria(SMSOut.class); /* * This is where the report is going to come from * projectionList.add(Projections.sqlGroupProjection("YEAR(time_submitted) as yearSubmitted, MONTHNAME(STR_TO_DATE(MONTH(time_submitted), '%m')) as monthSubmitted, time_submitted as timeSubmitted", "timeSubmitted", new String[] { "monthSubmitted", "timeSubmitted", "yearSubmitted" }, new Type[] { StandardBasicTypes.STRING })); */ ProjectionList projectionList = Projections.projectionList(); projectionList.add(Projections.sqlGroupProjection( "YEAR(time_submitted) as yearSubmitted, MONTHNAME(STR_TO_DATE(MONTH(time_submitted), '%m')) as timeSubmitted", "yearSubmitted, timeSubmitted", new String[] { "yearSubmitted", "timeSubmitted" }, new Type[] { StandardBasicTypes.LONG, StandardBasicTypes.STRING })); projectionList.add(Projections.rowCount()); criteria.setProjection(projectionList); //This is added criteria.add(Restrictions.eq("user", user)); criteria.addOrder(Order.asc("timeSubmitted")); criteria.add(Restrictions.between("timeSubmitted", startDate, endDate)); List<Object[]> results = criteria.list(); for (Object[] aResult : results) { System.out.println("the Object: " + Arrays.deepToString(aResult)); System.out.println("Year : " + aResult[0] + " Month : " + aResult[1] + " No. Sent : " + aResult[2]); } Map<String, Object> mapResult = new HashMap<>(); mapResult.put("result", results); mapResult.put("noSMS", 20); session.getTransaction().commit(); return mapResult; }
From source file:ke.co.mspace.nonsmppmanager.service.SMSOutServiceImpl.java
@Override public Map<String, String> smsOutGroupByUser(String startDate, String endDate) { Session session = HibernateUtil.getSessionFactory().getCurrentSession(); session.getTransaction().begin();//from w ww .j av a 2 s. c o m Criteria criteria = session.createCriteria(SMSOut.class); Calendar startAnotherDate = null; Calendar endAnotherDate = null; try { startAnotherDate = stringToCalendar(startDate); endAnotherDate = stringToCalendar(endDate); } catch (ParseException ex) { Logger.getLogger(SMSOutServiceImpl.class.getName()).log(Level.SEVERE, null, ex); } /* * This is where the report is going to come from * projectionList.add(Projections.sqlGroupProjection("YEAR(time_submitted) as yearSubmitted, MONTHNAME(STR_TO_DATE(MONTH(time_submitted), '%m')) as monthSubmitted, time_submitted as timeSubmitted", "timeSubmitted", new String[] { "monthSubmitted", "timeSubmitted", "yearSubmitted" }, new Type[] { StandardBasicTypes.STRING })); */ ProjectionList projectionList = Projections.projectionList(); projectionList.add(Projections.sqlGroupProjection( "YEAR(time_submitted) as yearSubmitted, MONTHNAME(STR_TO_DATE(MONTH(time_submitted), '%m')) as timeSubmitted, user as userName", "yearSubmitted, timeSubmitted, userName", new String[] { "yearSubmitted", "timeSubmitted", "userName" }, new Type[] { StandardBasicTypes.LONG, StandardBasicTypes.STRING, StandardBasicTypes.STRING })); projectionList.add(Projections.rowCount()); criteria.setProjection(projectionList); criteria.addOrder(Order.asc("timeSubmitted")); criteria.add(Restrictions.between("timeSubmitted", startDate, endDate)); List<Object[]> results = criteria.list(); Map<String, Integer> json = new LinkedHashMap<>(); Set<String> months = new LinkedHashSet<>(); Set<String> users = new LinkedHashSet<>(); while (startAnotherDate.before(endAnotherDate)) { String month = startAnotherDate.getDisplayName(Calendar.MONTH, Calendar.LONG, Locale.getDefault()); int year = startAnotherDate.get(Calendar.YEAR); for (Object[] aResult : results) { json.put(year + "-" + month + "-" + aResult[2], 0); months.add(aResult[0] + "-" + aResult[1]); users.add(String.valueOf(aResult[2])); } startAnotherDate.add(Calendar.MONTH, 1); } String madeUp = null; for (String aString : json.keySet()) { for (Object[] aResult : results) { madeUp = aResult[0] + "-" + aResult[1] + "-" + aResult[2]; if (aString.equals(madeUp)) { json.put(aString, Integer.parseInt(String.valueOf(aResult[3]))); } } } StringBuilder builder = new StringBuilder(); for (String aMonth : months) { builder.append("["); builder.append('"'); builder.append(aMonth.substring(0, 8)); builder.append('"'); builder.append(','); for (String aString : json.keySet()) { if (aString.contains(aMonth)) { builder.append(json.get(aString)); builder.append(","); } } builder.append("]"); if (builder.length() > 0) { if (builder.charAt(builder.lastIndexOf("]") - 1) == ',') { builder.deleteCharAt(builder.lastIndexOf("]") - 1); } } builder.append(","); } if (builder.length() > 0) { builder.deleteCharAt(builder.lastIndexOf(",")); } StringBuilder userBuilder = new StringBuilder(); userBuilder.append("["); for (String aUser : users) { userBuilder.append('"'); userBuilder.append(aUser); userBuilder.append('"'); userBuilder.append(','); } userBuilder.append(']'); if (userBuilder.length() > 0) { if (userBuilder.charAt(userBuilder.lastIndexOf("]") - 1) == ',') { userBuilder.deleteCharAt(userBuilder.lastIndexOf("]") - 1); } } System.out.println("A new builder : " + builder.toString()); System.out.println("The Users : " + userBuilder.toString()); Map<String, String> mapResult = new HashMap<>(); mapResult.put("data", builder.toString()); mapResult.put("users", userBuilder.toString()); session.getTransaction().commit(); return mapResult; }
From source file:net.firejack.platform.core.store.registry.RegistryNodeStore.java
License:Apache License
@Override @Transactional(readOnly = true)// www.j a va 2 s .c o m public List<String> findAllDuplicateNamesByType(String _package, Class<?>... _class) { List<String> discriminators = new ArrayList<String>(_class.length); for (Class<?> _clas : _class) { DiscriminatorValue discriminator = _clas.getAnnotation(DiscriminatorValue.class); discriminators.add(discriminator.value()); } Criteria criteria = getSession().createCriteria(getClazz()); criteria.setProjection(Projections.projectionList().add(Projections.sqlGroupProjection("name as x", "x having count(x) > 1", new String[] { "x" }, new Type[] { Hibernate.STRING }))); criteria.add(Restrictions.like("lookup", _package + ".%")); if (getClazz().isAnnotationPresent(DiscriminatorColumn.class) && !discriminators.isEmpty()) { criteria.add(Restrictions.in("class", discriminators)); } return criteria.list(); }
From source file:org.candlepin.model.DetachedCandlepinQuery.java
License:Open Source License
/** * {@inheritDoc}/*ww w .j a v a2 s . c o m*/ */ @Override @SuppressWarnings({ "unchecked", "checkstyle:indentation" }) public int getRowCount() { CriteriaImpl executable = (CriteriaImpl) this.getExecutableCriteria(); // Impl note: // We're using the projection method here over using a cursor to scroll the results due to // limitations on various connectors' cursor implementations. Some don't properly support // fast-forwarding/jumping (Oracle) and others fake the whole thing by running the query // and pretending to scroll (MySQL). Until these are addressed, the hack below is going to // be far more performant and significantly safer (which makes me sad). // Remove any ordering that may be applied (since we almost certainly won't have the field // available anymore) for (Iterator iterator = executable.iterateOrderings(); iterator.hasNext();) { iterator.next(); iterator.remove(); } Projection projection = executable.getProjection(); if (projection != null && projection.isGrouped()) { // We have a projection that alters the grouping of the query. We need to rebuild the // projection such that it gets our row count and properly applies the group by // statement. // The logic for this block is largely derived from this Stack Overflow posting: // http://stackoverflow.com/ // questions/32498229/hibernate-row-count-on-criteria-with-already-set-projection // // A safer alternative may be to generate a query that uses the given criteria as a // subquery (SELECT count(*) FROM (<criteria SQL>)), but is probably less performant // than this hack. CriteriaQueryTranslator translator = new CriteriaQueryTranslator( (SessionFactoryImplementor) this.session.getSessionFactory(), executable, executable.getEntityOrClassName(), CriteriaQueryTranslator.ROOT_SQL_ALIAS); executable.setProjection( Projections.projectionList().add(Projections.rowCount()).add(Projections.sqlGroupProjection( "count(count(1))", translator.getGroupBy(), new String[] {}, new Type[] {}))); } else { executable.setProjection(Projections.rowCount()); } Long count = (Long) executable.uniqueResult(); return count != null ? count.intValue() : 0; }
From source file:org.remus.marketplace.scheduling.GenerateStatisticsJob.java
License:Open Source License
private void createClickStatistics(Integer id, File folder, Node node, Calendar instance) { Map<Date, Integer> map = new HashMap<Date, Integer>(); for (int i = 0, n = month; i < n; i++) { Calendar instance2 = Calendar.getInstance(); instance2.setTime(new Date()); instance2.add(Calendar.MONTH, i * -1); map.put(instance2.getTime(), 0); }//from w w w .j av a2 s .c om AdvancedCriteria criteria = new AdvancedCriteria() .addRestriction(Restrictions.between(Clickthrough.TIME, instance.getTime(), new Date())) .addRestriction(Restrictions.eq(Clickthrough.NODE, node)); ProjectionList projectionList = Projections.projectionList(); projectionList.add(Projections.count(Clickthrough.NODE)); projectionList .add(Projections.sqlGroupProjection("month({alias}.time) as month, year({alias}.time) as year", "month({alias}.time), year({alias}.time)", new String[] { "month", "year" }, new Type[] { Hibernate.INTEGER, Hibernate.INTEGER })); criteria.setProjection(projectionList); List<Object> query = clickthroughDao.query(criteria); for (Object object : query) { Object[] data = (Object[]) object; Integer count = (Integer) data[0]; Integer month = (Integer) data[1]; Integer year = (Integer) data[2]; Set<Date> keySet = map.keySet(); for (Date date : keySet) { Calendar instance2 = Calendar.getInstance(); instance2.setTime(date); if (instance2.get(Calendar.YEAR) == year && instance2.get(Calendar.MONTH) == month - 1) { map.put(date, count); } } } DefaultCategoryDataset data = new DefaultCategoryDataset(); List<Date> keySet = new ArrayList<Date>(map.keySet()); Collections.sort(keySet, new Comparator<Date>() { @Override public int compare(Date o1, Date o2) { return o1.compareTo(o2); } }); for (Date date : keySet) { Integer integer = map.get(date); Calendar instance2 = Calendar.getInstance(); instance2.setTime(date); int year = instance2.get(Calendar.YEAR); int month = instance2.get(Calendar.MONTH) + 1; data.addValue(integer, "Column1", month + "-" + year); } JFreeChart createBarChart = ChartFactory.createBarChart("Clicks", "Month", "", data, PlotOrientation.VERTICAL, false, false, false); File file = new File(folder, "clicks_" + id + ".png"); if (file.exists()) { file.delete(); } try { ChartUtilities.saveChartAsPNG(file, createBarChart, 500, 300); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } }
From source file:org.remus.marketplace.scheduling.GenerateStatisticsJob.java
License:Open Source License
private void createDownloadStatistics(Integer id, File folder, Node node, Calendar instance) { Map<Date, Integer> map = new HashMap<Date, Integer>(); for (int i = 0, n = month; i < n; i++) { Calendar instance2 = Calendar.getInstance(); instance2.setTime(new Date()); instance2.add(Calendar.MONTH, i * -1); map.put(instance2.getTime(), 0); }// w ww . ja v a 2s .c om AdvancedCriteria criteria = new AdvancedCriteria() .addRestriction(Restrictions.between(Download.TIME, instance.getTime(), new Date())) .addRestriction(Restrictions.eq(Download.NODE, node)); ProjectionList projectionList = Projections.projectionList(); projectionList.add(Projections.count(Download.NODE)); projectionList .add(Projections.sqlGroupProjection("month({alias}.time) as month, year({alias}.time) as year", "month({alias}.time), year({alias}.time)", new String[] { "month", "year" }, new Type[] { Hibernate.INTEGER, Hibernate.INTEGER })); criteria.setProjection(projectionList); List<Object> query = downloadDao.query(criteria); for (Object object : query) { Object[] data = (Object[]) object; Integer count = (Integer) data[0]; Integer month = (Integer) data[1]; Integer year = (Integer) data[2]; Set<Date> keySet = map.keySet(); for (Date date : keySet) { Calendar instance2 = Calendar.getInstance(); instance2.setTime(date); if (instance2.get(Calendar.YEAR) == year && instance2.get(Calendar.MONTH) == month - 1) { map.put(date, count); } } } DefaultCategoryDataset data = new DefaultCategoryDataset(); List<Date> keySet = new ArrayList<Date>(map.keySet()); Collections.sort(keySet, new Comparator<Date>() { @Override public int compare(Date o1, Date o2) { return o1.compareTo(o2); } }); for (Date date : keySet) { Integer integer = map.get(date); Calendar instance2 = Calendar.getInstance(); instance2.setTime(date); int year = instance2.get(Calendar.YEAR); int month = instance2.get(Calendar.MONTH) + 1; data.addValue(integer, "Column1", month + "-" + year); } JFreeChart createBarChart = ChartFactory.createBarChart("Downloads", "Month", "", data, PlotOrientation.VERTICAL, false, false, false); File file = new File(folder, "download_" + id + ".png"); if (file.exists()) { file.delete(); } try { ChartUtilities.saveChartAsPNG(file, createBarChart, 500, 300); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } }
From source file:org.sculptor.framework.accessimpl.jpahibernate.JpaHibFindByConditionStatAccessImpl.java
License:Apache License
private Projection makeTimeGroupByPostgreSql(ColumnStatRequest<T> column, ColumnStatType statType, Criteria criteria) {//from ww w .j a va 2 s. co m String func; if (statType.equals(GROUP_BY_DAY)) { func = "day"; } else if (statType.equals(GROUP_BY_DOW)) { func = "dow"; } else if (statType.equals(GROUP_BY_DOY)) { func = "doy"; } else if (statType.equals(GROUP_BY_HOUR)) { func = "hour"; } else if (statType.equals(GROUP_BY_MONTH)) { func = "month"; } else if (statType.equals(GROUP_BY_QUARTER)) { func = "quarter"; } else if (statType.equals(GROUP_BY_WEEK)) { func = "week"; } else if (statType.equals(GROUP_BY_YEAR)) { func = "year"; } else { func = "day"; } String colName = column.getColumn().getName(); String fldName = colName + "_" + func; String sqlFunc = "extract(" + func + " from {alias}." + colName + ")"; criteria.addOrder(Order.asc(fldName)); return Projections.alias(Projections.sqlGroupProjection(sqlFunc + " as " + fldName, fldName, new String[] { fldName }, TIME_RESULT_TYPES), fldName); }
From source file:org.sculptor.framework.accessimpl.jpahibernate.JpaHibFindByConditionStatAccessImpl.java
License:Apache License
private Projection makeTimeGroupByOracle(ColumnStatRequest<T> column, ColumnStatType statType, Criteria criteria) {// w ww .ja v a2 s .c om String func; if (statType.equals(GROUP_BY_DAY)) { func = "DD"; } else if (statType.equals(GROUP_BY_DOW)) { func = "D"; } else if (statType.equals(GROUP_BY_DOY)) { func = "DDD"; } else if (statType.equals(GROUP_BY_HOUR)) { func = "HH24"; } else if (statType.equals(GROUP_BY_MONTH)) { func = "MM"; } else if (statType.equals(GROUP_BY_QUARTER)) { func = "Q"; } else if (statType.equals(GROUP_BY_WEEK)) { func = "WW"; } else if (statType.equals(GROUP_BY_YEAR)) { func = "YYYY"; } else { func = "DD"; } String colName = column.getColumn().getName(); String fldName = colName + "_" + func; String sqlFunc = "to_char({alias}." + colName + ", '" + func + "')"; criteria.addOrder(Order.asc(fldName)); return Projections.alias(Projections.sqlGroupProjection(sqlFunc + " as " + fldName, fldName, new String[] { fldName }, TIME_RESULT_TYPES), fldName); }