Java tutorial
/* * To change this license header, choose License Headers in Project Properties. * To change this template file, choose Tools | Templates * and open the template in the editor. */ package com.bookselling.dao; import com.bookselling.domain.SellingPost; import com.bookselling.domain.Subject; import com.bookselling.domain.User; import com.bookselling.domain.statistic.GenericChartUnit; import com.bookselling.domain.statistic.StatisticData; import com.bookselling.domain.status.SellingPostStatus; import com.bookselling.form.filter.SellingPostFilterForm; import com.bookselling.form.searchby.QuatityFilterType; import com.bookselling.form.searchby.SellingPostFilterType; import com.bookselling.form.orderby.SellingPostOrderType; import com.bookselling.form.orderby.SortType; import com.bookselling.util.PaginationData; import java.io.Serializable; import java.math.BigDecimal; import java.math.BigInteger; import java.util.ArrayList; import java.util.Arrays; import java.util.Date; import java.util.HashSet; import java.util.Iterator; import java.util.LinkedHashSet; import java.util.List; import java.util.Set; import org.hibernate.Criteria; import org.hibernate.SQLQuery; import org.hibernate.criterion.Order; import org.hibernate.criterion.Projections; import org.hibernate.criterion.Restrictions; import org.springframework.stereotype.Repository; /** * * @author Phan Phat */ @Repository public class SellingPostDaoImpl extends GenericDao<SellingPost> implements SellingPostDao { private double timeToDeny = 0.1; @Override public Class<SellingPost> registeredClass() { return SellingPost.class; } private Object[] genericFilter(SellingPostFilterForm criteriaForm, int first, int items, int id) { Criteria criteria = getSession().createCriteria(SellingPost.class); //parse form SellingPostFilterForm form = criteriaForm; //get form data String keyword = form.getKeyword(); Double minPrice = form.getMinPrice(); Double maxPrice = form.getMaxPrice(); QuatityFilterType quatityStatus = form.getQuatityStatus(); SellingPostFilterType searchBy = form.getSearchBy(); SellingPostStatus sellingPostStatus[] = form.getSellingPostStatus(); Set<Subject> subjects = form.getSubjects(); Integer subjectIds[] = new Integer[subjects.size()]; Subject subjectsArray[] = subjects.toArray(new Subject[subjectIds.length]); for (int i = 0; i < subjects.size(); i++) subjectIds[i] = subjectsArray[i].getId(); //create criteria criteria.createAlias("purchasingSellingEntity", "bk").createAlias("bk.subjects", "sbj") .createAlias("bk.publisher", "pub").createAlias("seller", "sl").createAlias("sl.account", "acc"); //search by keyword if (keyword != null && !keyword.isEmpty()) { keyword = "%" + keyword.trim() + "%"; if (searchBy == SellingPostFilterType.HEADER) criteria.add(Restrictions.like("header", keyword)); else if (searchBy == SellingPostFilterType.NAME) { criteria.add(Restrictions.like("bk.name", keyword)); //product } else if (searchBy == SellingPostFilterType.PUBLISHER) { criteria.add(Restrictions.like("pub.name", keyword)); } } //search with price range criteria.add(Restrictions.between("bk.sellingPrice", minPrice == null ? 0 : minPrice, maxPrice == null ? Integer.MAX_VALUE : maxPrice)); //search with quatity status if (quatityStatus == QuatityFilterType.AVAILABLE) criteria.add(Restrictions.gt("bk.quatity", 0)); else if (quatityStatus == QuatityFilterType.OUTOFSTOCK) criteria.add(Restrictions.eq("bk.quatity", 0)); //search with selling post status if (sellingPostStatus.length != 0) criteria.add(Restrictions.in("status", sellingPostStatus)); //search with subjects if (subjectIds.length != 0) criteria.add(Restrictions.in("sbj.id", subjectIds)); //get data from form SortType sortType = form.getSortType(); SellingPostOrderType sortByProperty = form.getSortByProperty(); //Set up criteria String propertyName = null; if (sortByProperty == SellingPostOrderType.HEADER) propertyName = "header"; else if (sortByProperty == SellingPostOrderType.NAME) propertyName = "bk.name"; else if (sortByProperty == SellingPostOrderType.PUBLISHER) propertyName = "pub.name"; else if (sortByProperty == SellingPostOrderType.CREATEDDATE) propertyName = "createdDate"; else if (sortByProperty == SellingPostOrderType.PRICE) propertyName = "bk.sellingPrice"; if (id != -1) criteria.add(Restrictions.eq("sl.id", id)); //Ly s dng long rowCount = (long) criteria.setProjection(Projections.countDistinct("id")).uniqueResult(); //Ly id criteria.setProjection(null).setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY) .setProjection(Projections.distinct(Projections.id())).setFirstResult(first).setMaxResults(items) .addOrder(sortType == SortType.ASC ? Order.asc(propertyName) : Order.desc(propertyName)); List<Integer> ids = new ArrayList<>(); for (Iterator<Integer> temp = criteria.list().iterator(); temp.hasNext();) ids.add(temp.next()); //Criteria ph Criteria subCriteria = getSession().createCriteria(SellingPost.class); subCriteria.createAlias("purchasingSellingEntity", "bk").createAlias("bk.subjects", "sbj") .createAlias("bk.publisher", "pub").createAlias("seller", "sl").createAlias("sl.account", "acc") .add(Restrictions.in("id", ids.size() > 0 ? ids : Arrays.asList(-1))) .addOrder(sortType == SortType.ASC ? Order.asc(propertyName) : Order.desc(propertyName)); return new Object[] { subCriteria, rowCount }; } @Override public PaginationData<SellingPost> filterByUser(User user, SellingPostFilterForm form, int first, int items) { Object[] data = genericFilter(form, first, items, user.getId()); Criteria criteria = (Criteria) data[0]; Set<SellingPost> posts = new LinkedHashSet<>(criteria.list()); HibernateInitSupport.setCls(SellingPost.class); for (SellingPost post : posts) HibernateInitSupport.initDomain(post); //pagination data PaginationData paginationData = new PaginationData((long) data[1], items, first, posts); return paginationData; } @Override public PaginationData<SellingPost> filter(SellingPostFilterForm criteriaForm, int first, int items) { Object[] data = genericFilter(criteriaForm, first, items, -1); Criteria criteria = (Criteria) data[0]; //get list Set<SellingPost> sellingPosts = new LinkedHashSet<>(criteria.list()); HibernateInitSupport.setCls(SellingPost.class); for (SellingPost post : sellingPosts) HibernateInitSupport.initDomain(post); //pagination data PaginationData paginationData = new PaginationData((long) data[1], items, first, sellingPosts); return paginationData; } @Override public PaginationData<SellingPost> clientFilter(SellingPostFilterForm form, int first, int items) { Object[] data = genericFilter(form, first, items, -1); Criteria criteria = (Criteria) data[0]; criteria.add(Restrictions.eq("status", SellingPostStatus.CONFIRM)); //Ly d liu Set<SellingPost> posts = new LinkedHashSet<>(criteria.list()); HibernateInitSupport.setCls(SellingPost.class); for (SellingPost post : posts) HibernateInitSupport.initDomain(post); PaginationData paginationData = new PaginationData((long) data[1], items, first, posts); return paginationData; } @Override public PaginationData<SellingPost> get(User user, int first, int items) { Criteria criteria = getSession().createCriteria(SellingPost.class); criteria.createAlias("seller", "sl").add(Restrictions.eq("sl.id", user.getId())); criteria.setFirstResult(first); criteria.setMaxResults(items); List<SellingPost> posts = criteria.list(); HibernateInitSupport.setCls(SellingPost.class); for (SellingPost post : posts) HibernateInitSupport.initDomain(post); PaginationData paginationData = new PaginationData( (long) criteria.setProjection(Projections.countDistinct("id")).uniqueResult(), items, first, posts); return paginationData; } @Override public SellingPost get(User user, Serializable id) { Criteria criteria = getSession().createCriteria(SellingPost.class); criteria.createAlias("seller", "sl").add(Restrictions.eq("id", id)) .add(Restrictions.eq("sl.id", user.getId())); List<SellingPost> posts = criteria.list(); HibernateInitSupport.setCls(SellingPost.class); for (SellingPost post : posts) HibernateInitSupport.initDomain(post); return posts.iterator().next(); } @Override public PaginationData<SellingPost> getConfirmedPost(int first, int items) { SQLQuery sqlQuery = getSession().createSQLQuery(""); Criteria criteria = getSession().createCriteria(SellingPost.class) .add(Restrictions.eq("status", SellingPostStatus.CONFIRM)); //Ly s dng long rowCount = (long) criteria.setProjection(Projections.countDistinct("id")).uniqueResult(); criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY) .setProjection(Projections.distinct(Projections.id())).addOrder(Order.desc("id")) .setFirstResult(first).setMaxResults(items); List<Integer> ids = new ArrayList<>(); for (Iterator<Integer> temp = criteria.list().iterator(); temp.hasNext();) ids.add(temp.next()); //Criteria ph Criteria subCriteria = getSession().createCriteria(SellingPost.class); subCriteria.createAlias("purchasingSellingEntity", "bk").createAlias("bk.subjects", "sbj") .createAlias("bk.publisher", "pub").createAlias("seller", "sl").createAlias("sl.account", "acc") .add(Restrictions.in("id", ids.size() > 0 ? ids : Arrays.asList(-1))).addOrder(Order.desc("id")); Set<SellingPost> posts = new HashSet(subCriteria.list()); HibernateInitSupport.setCls(SellingPost.class); for (SellingPost post : posts) HibernateInitSupport.initDomain(post); PaginationData paginationData = new PaginationData(rowCount, items, first, posts); return paginationData; } @Override public PaginationData<SellingPost> getBestSelling(int first, int items) { SQLQuery sqlQuery = getSession().createSQLQuery("select boughtEntityId, sum(quatity) " + "from tradedetail trd " + "group by boughtEntityId " + "order by sum(quatity) desc"); sqlQuery.setFirstResult(first).setMaxResults(items); List result = sqlQuery.list(); Integer boughtEntityIds[] = new Integer[result.size()]; int index = 0; for (Object rows : boughtEntityIds) { Object[] row = (Object[]) rows; boughtEntityIds[index++] = Integer.valueOf((String) row[0]); } Criteria criteria = getSession().createCriteria(SellingPost.class); criteria.add(Restrictions.eq("status", SellingPostStatus.CONFIRM)) .add(Restrictions.in("id", boughtEntityIds)); Set<SellingPost> posts = new HashSet<>(criteria.list()); HibernateInitSupport.setCls(SellingPost.class); for (SellingPost post : posts) HibernateInitSupport.initDomain(post); PaginationData paginationData = new PaginationData( (long) criteria.setProjection(Projections.rowCount()).uniqueResult(), items, first, posts); return paginationData; } @Override public PaginationData<SellingPost> getBySubject(int first, int items, Subject subject) { Integer subjectId = subject.getId(); Criteria criteria = getSession().createCriteria(SellingPost.class); criteria.createAlias("purchasingSellingEntity", "slen").createAlias("slen.subjects", "sbj") .add(Restrictions.eq("status", SellingPostStatus.CONFIRM)) .add(Restrictions.eq("sbj.id", subjectId)); //Ly s dng long rowCount = (long) criteria.setProjection(Projections.countDistinct("id")).uniqueResult(); criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY) .setProjection(Projections.distinct(Projections.id())).addOrder(Order.desc("id")) .setFirstResult(first).setMaxResults(items); List<Integer> ids = new ArrayList<>(); for (Iterator<Integer> temp = criteria.list().iterator(); temp.hasNext();) ids.add(temp.next()); //Criteria ph Criteria subCriteria = getSession().createCriteria(SellingPost.class); subCriteria.createAlias("purchasingSellingEntity", "bk").createAlias("bk.subjects", "sbj") .createAlias("bk.publisher", "pub").createAlias("seller", "sl").createAlias("sl.account", "acc") .add(Restrictions.in("id", ids.size() > 0 ? ids : Arrays.asList(-1))).addOrder(Order.desc("id")); Set<SellingPost> posts = new HashSet<>(subCriteria.list()); HibernateInitSupport.setCls(SellingPost.class); for (SellingPost post : posts) HibernateInitSupport.initDomain(post); PaginationData paginationData = new PaginationData(rowCount, items, first, posts); return paginationData; } @Override public SellingPost simpleDetail(Serializable id) { return this.get(id); } @Override public int countStoredItem(User user) { SQLQuery sqlQuery = getSession() .createSQLQuery("select SUM(quatity) " + "from storeablegood gd, sellingpost slp " + "where gd.id = slp.sellingEntityId and " + " slp.userId = :id "); BigDecimal figure = (BigDecimal) sqlQuery.setInteger("id", user.getId()).uniqueResult(); return figure != null ? figure.intValue() : 0; } @Override public Set<SellingPost> bestSellingItem(User user) { SQLQuery sqlQuery = getSession().createSQLQuery("select slid.soldEntityId itemId, MAX(quatity) iquatity " + "from sellerinvoice sli, sellerinvoicedetail slid " + "where sli.id = slid.sellerInvoiceId and " + " sli.seller_id = :id " + "group by itemId " + "having iquatity > 0 " + "order by iquatity asc " + "limit 5 "); Set<Object[]> queryResult = new HashSet<>(sqlQuery.setInteger("id", user.getId()).list()); Set<Integer> ids = new HashSet<>(); for (Object[] row : queryResult) { ids.add((int) row[0]); System.out.println((int) row[0]); } Criteria criteria = getSession().createCriteria(SellingPost.class); if (ids.size() != 0) criteria.add(Restrictions.in("id", ids)); else return new HashSet<>(); Set<SellingPost> data = new HashSet<SellingPost>(criteria.list()); HibernateInitSupport.setCls(SellingPost.class); for (SellingPost post : data) HibernateInitSupport.initDomain(post); return data; } @Override public StatisticData<GenericChartUnit<Object>> countPost(Date fromDate, Date toDate) { StatisticData<GenericChartUnit<Object>> statisticData = new StatisticData<>(new HashSet<>()); statisticData.setLabel("Lt ng bi"); SQLQuery sqlQuery = getSession().createSQLQuery("select DATE(createdDate) date, count(*) " + "from post " + "where createdDate between :fromDate and :toDate " + "group by date " + "order by date "); sqlQuery.setDate("fromDate", fromDate).setDate("toDate", toDate); List<Object[]> rows = sqlQuery.list(); for (Object[] row : rows) { GenericChartUnit<Object> unit = new GenericChartUnit<>(((Date) row[0]).getTime(), new ArrayList<Object>()); unit.getFigures().add(row[1]); statisticData.getData().add(unit); } return statisticData; } @Override public int totalPost() { SQLQuery sqlQuery = getSession().createSQLQuery("select COUNT(*) " + "from post"); BigInteger figure = (BigInteger) sqlQuery.uniqueResult(); return figure != null ? figure.intValue() : 0; } @Override public void autoDeny() { SQLQuery sqlQuery = getSession().createSQLQuery("update sellingpost as slp" + " join post as p on slp.id = p.id " + "set status = 'DENY' " + "where TIMESTAMPDIFF(MINUTE, createdDate, NOW()) >= :time*60 and " + " slp.status = 'WAIT'"); sqlQuery.setDouble("time", timeToDeny).executeUpdate(); } }