public MemberOrderDTO getReconciliationData(Specification<MemberOrder> spec){
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<MemberOrderDTO> cq = cb
.createQuery(MemberOrderDTO.class);
Root<MemberOrder> customerRoot = cq.from(MemberOrder.class);
cq.multiselect(
cb.countDistinct(customerRoot.get("username")).alias("userNum"),
cb.sum(customerRoot.<BigDecimal>get("price")).alias("sumMoney"),
cb.sum(customerRoot.<BigDecimal>get("extraPayAmount")).alias("extraMoney"),
cb.count(customerRoot.<Long>get("id")).alias("orderNum"),
cb.sum(customerRoot.<BigDecimal>get("price")).alias("orderMoney"),
//https://stackoverflow.com/questions/16858855/having-clause-along-with-case-when-in-criteriabuilder
cb.sum(cb.<Integer>selectCase().when(cb.equal(customerRoot.get("extraPayFlag"), true), 1).otherwise(0))
);
javax.persistence.criteria.Predicate predicate = spec.toPredicate(customerRoot,cq,cb);
cq.where(predicate);
TypedQuery<MemberOrderDTO> query = em.createQuery(cq);
MemberOrderDTO dto = query.getSingleResult();
return dto;
}
